python sqlite3 with fts4

nivieru · 06-01-2017, 02:08 PM

Hi all,

The sqlite3 that's come bundled with python in slackware-current doesn't have fts, I get "no such module: fts4" when trying to run a script that needs it.

I don't know much about python, so I'm not sure how to tackle this.
Do I need to recompile the python package? or just the bundled sqlite somehow?

Just to be clear, the "system" sqlite3 does have fts compiled in, but python uses it's own version of sqlite3.

Any help appreciated,
Niv

audriusk · 06-01-2017, 02:51 PM

Quote:

Originally Posted by nivieru

The sqlite3 that's come bundled with python in slackware-current <...>

<...> python uses it's own version of sqlite3.

Doesn't seem to be true:

Code:

$ ldd /usr/lib64/python2.7/lib-dynload/_sqlite3.so | grep sqlite
        libsqlite3.so.0 => /usr/lib64/libsqlite3.so.0 (0x00007efed7854000)

And version is the same as what's provided by sqlite package in Slackware (sqlite-3.19.0-x86_64-1).

Code:

$ python -c 'import sqlite3; print sqlite3.sqlite_version'
3.19.0

But there's definitely a difference when sqlite is invoked from CLI (highlighted the lines that were shown only in one output, but not the other):

Code:

sqlite> pragma compile_options;
COMPILER=gcc-7.1.0
DEFAULT_SYNCHRONOUS=2
DEFAULT_WAL_SYNCHRONOUS=2
DISABLE_DIRSYNC
ENABLE_COLUMN_METADATA
ENABLE_DBSTAT_VTAB
ENABLE_FTS3
ENABLE_FTS3_PARENTHESIS
ENABLE_FTS4
ENABLE_ICU
ENABLE_JSON1
ENABLE_STAT4
ENABLE_UNLOCK_NOTIFY
HAVE_ISNAN
SECURE_DELETE
SYSTEM_MALLOC
THREADSAFE=1

and when it's used in Python:

Code:

>>> import sqlite3
>>> conn = sqlite3.connect(':memory:')
>>> cur = conn.cursor()
>>> for (val,) in cur.execute('pragma compile_options'): print val
... 
COMPILER=gcc-7.1.0
DEFAULT_SYNCHRONOUS=2
DEFAULT_WAL_SYNCHRONOUS=2
DISABLE_DIRSYNC
ENABLE_COLUMN_METADATA
ENABLE_DBSTAT_VTAB
ENABLE_FTS3_PARENTHESIS
ENABLE_ICU
ENABLE_STAT4
ENABLE_UNLOCK_NOTIFY
HAVE_ISNAN
SECURE_DELETE
SYSTEM_MALLOC
TEMP_STORE=2
THREADSAFE=1

Don't know why it's like this and how to enable FTS.

audriusk · 06-01-2017, 03:10 PM

I think I found the fix:

Quote:

The sqlite3 module is not built with loadable extension support by default, because some platforms (notably Mac OS X) have SQLite libraries which are compiled without this feature. To get loadable extension support, you must modify setup.py and remove the line that sets SQLITE_OMIT_LOAD_EXTENSION.

So adding the following line to python.SlackBuild before ./configure part should do the trick:

Code:

sed -i '/SQLITE_OMIT_LOAD_EXTENSION/d' setup.py

Haven't tested it myself, it's late here, will try to build Python and see if it works tomorrow.

nivieru · 06-02-2017, 03:53 AM

Thanks audriusk, these compile_options got me thinking it's a different sqlite lib.

I tried your suggestion, but it doesn't work

Code:

>>> import sqlite3
>>> conn = sqlite3.connect(':memory:')
>>> conn.enable_load_extension(True)
>>> conn.load_extension('fts3')
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
sqlite3.OperationalError: fts3.so: cannot open shared object file: No such file or directory

I think sqlite3 is compiled with this option built in so there isn't any fts?.so file to load.
why python can't use this feature is a mystery.

audriusk · 06-02-2017, 04:47 AM

You don't need to load FTS extensions manually, just rebuild python package after applying the following patch:

Code:

--- python.SlackBuild.orig      2016-09-07 02:20:52.000000000 +0300
+++ python.SlackBuild   2017-06-02 12:50:24.068193249 +0300
@@ -86,6 +86,9 @@
  \( -perm 666 -o -perm 664 -o -perm 600 -o -perm 444 -o -perm 440 -o -perm 400 \) \
  -exec chmod 644 {} \;
 
+# Enable loadable sqlite extensions.
+sed -i '/SQLITE_OMIT_LOAD_EXTENSION/d' setup.py
+
 ./configure \
   --prefix=/usr \
   --libdir=/usr/lib${LIBDIRSUFFIX} \

I just tried it using python source from -current on Slackware64-14.2 VM (currently at work and don't have access to my -current machine) and after rebuilding Python compile_options now shows ENABLE_FTS3, I also was able to create FTS3 table as explained in SQLite docs. I see no reason why this patch shouldn't work on -current.

nivieru · 06-02-2017, 05:00 AM

Huh, strange, that's what I did.
I'll try again, maybe I missed something.

Thanks

ppr:kut · 06-02-2017, 05:45 AM

You didn't do anything wrong, it's just that you need FTS4 not FTS3.
FTS4 is currently not built for sqlite because of a bug in sqlite's configure script.

You'd need to add this to sqlite.SlackBuild before ./configure is called:

Quote:

sed -i "s|\$(OPT_FEATURE_FLAGS)|\${OPT_FEATURE_FLAGS}|g" ./configure

and then rebuild sqlite as well.

I'd expect fixes for both of these issues to make it into -current soon though.

nivieru · 06-02-2017, 06:38 AM

Thanks ppr:kut! It works!

ppr:kut · 06-02-2017, 06:48 AM

Glad to hear it