Search code examples
phpsqlitecentosfull-text-search

Enable sqlite3 FTS5 for PHP

I'm trying to use SQLite3 FTS5 (full-text search) with PHP on CentOS.. but I couldn't get it successfully after so much time trying.

I'm building a loadable FTS5 Extension for SQlite and tried the following steps (from docs)

in the server terminal (SSH) :

$ yum install libsqlite3x-devel
$ wget -c http://www.sqlite.org/src/tarball/SQLite-trunk.tgz?uuid=trunk -O SQLite-trunk.tgz
$ tar -xzf SQLite-trunk.tgz
$ cd SQLite-trunk
$ ./configure && make fts5.c
$ gcc -g -fPIC -shared fts5.c -o fts5.so

But I'm having the following error in the last step:

fts5_index.c:732:11: error: ‘SQLITE_PREPARE_PERSISTENT’ undeclared (first use in this function)
fts5_index.c:732:37: error: ‘SQLITE_PREPARE_NO_VTAB’ undeclared (first use in this function)
fts5_main.c:888:29: error: ‘SQLITE_PREPARE_PERSISTENT’ undeclared (first use in this function)
fts5_main.c:1029:31: error: ‘SQLITE_PREPARE_PERSISTENT’ undeclared (first use in this function)
fts5_storage.c:139:15: error: ‘SQLITE_PREPARE_PERSISTENT’ undeclared (first use in this function)
fts5_storage.c:140:41: error: ‘SQLITE_PREPARE_NO_VTAB’ undeclared (first use in this function)

The file fts5.so is not produced so I can't use it in the server

So do you have any idea how to fix this error and produce fts5.so file successfully

Solution

  • I found that I have to generate header files before the last step

    so the full steps looks like this:

    $ yum install libsqlite3x-devel
$ wget -c http://www.sqlite.org/src/tarball/SQLite-trunk.tgz?uuid=trunk -O SQLite-trunk.tgz
$ tar -xzf SQLite-trunk.tgz
$ cd SQLite-trunk
$ ./configure
$ make fts5.c sqlite3.h sqlite3ext.h
$ gcc -g -fPIC -shared fts5.c -o fts5.so

    Then I had to load the fts5.so as loadable extension for sqlite

    1- Copy the fts5.so file to a new folder on server /sqlite_ext

    $ mkdir /sqlite_ext
$ cp fts5.so /sqlite_ext

    2- Edit sqlite3.extension_dir in php.ini to point to the same folder like this

    sqlite3.extension_dir = "/sqlite_ext"

    3- Then in my php file, load the extension :

    $db->loadExtension('fts5.so');

    Update :

    It's better to update server's SQLite as a whole with --enable-fts5 option

    $ wget -c https://www.sqlite.org/2019/sqlite-autoconf-3280000.tar.gz
$ tar -xzf sqlite-autoconf-3280000.tar.gz
$ cd sqlite-autoconf-3280000
$ ./configure --enable-fts5 --prefix=/usr --disable-static CFLAGS="-g -O2 -DSQLITE_ENABLE_FTS3=1 -DSQLITE_ENABLE_COLUMN_METADATA=1 -DSQLITE_ENABLE_UNLOCK_NOTIFY=1 -DSQLITE_SECURE_DELETE=1 -DSQLITE_SOUNDEX"
$ make
$ make install

    Now the latest SQLite is installed for the server but not for PHP, let's do it for PHP

    $ mv aclocal.m4 config.m4
$ phpize

    Check for your SQLite version in server using $ sqlite3 --version and in PHP using phpinfo();

    *notice: the link mentioned in the first step is for the latest sqlite-autoconf amalgamation at the time of this answer. there maybe a more recent version for you . check here

    enter image description here