ENABLE_FTS4 missing from compile_options, but FTS4 tables work?!

(1) By BohwaZ (bohwaz) on 2023-06-12 07:13:32 [link] [source]

I've got a user on a shared hosting service (running SQLite 3.39.4) where PRAGMA compile_options doesn't show ENABLE_FTS4:

    ATOMIC_INTRINSICS=1
    COMPILER=gcc-4.8.5 20150623 (Red Hat 4.8.5-44)
    DEFAULT_AUTOVACUUM
    DEFAULT_CACHE_SIZE=-2000
    DEFAULT_FILE_FORMAT=4
    DEFAULT_JOURNAL_SIZE_LIMIT=-1
    DEFAULT_MMAP_SIZE=0
    DEFAULT_PAGE_SIZE=4096
    DEFAULT_PCACHE_INITSZ=20
    DEFAULT_RECURSIVE_TRIGGERS
    DEFAULT_SECTOR_SIZE=4096
    DEFAULT_SYNCHRONOUS=2
    DEFAULT_WAL_AUTOCHECKPOINT=1000
    DEFAULT_WAL_SYNCHRONOUS=2
    DEFAULT_WORKER_THREADS=0
    DISABLE_DIRSYNC
    ENABLE_COLUMN_METADATA
    ENABLE_FTS3
    ENABLE_MATH_FUNCTIONS
    ENABLE_RTREE
    ENABLE_UNLOCK_NOTIFY
    HAVE_ISNAN
    MALLOC_SOFT_LIMIT=1024
    MAX_ATTACHED=10
    MAX_COLUMN=2000
    MAX_COMPOUND_SELECT=500
    MAX_DEFAULT_PAGE_SIZE=8192
    MAX_EXPR_DEPTH=1000
    MAX_FUNCTION_ARG=127
    MAX_LENGTH=1000000000
    MAX_LIKE_PATTERN_LENGTH=50000
    MAX_MMAP_SIZE=0x7fff0000
    MAX_PAGE_COUNT=1073741823
    MAX_PAGE_SIZE=65536
    MAX_SQL_LENGTH=1000000000
    MAX_TRIGGER_DEPTH=1000
    MAX_VARIABLE_NUMBER=32766
    MAX_VDBE_OP=250000000
    MAX_WORKER_THREADS=8
    MUTEX_PTHREADS
    SECURE_DELETE
    SYSTEM_MALLOC
    TEMP_STORE=1
    THREADSAFE=1

But creating a FTS4 table and querying it works:

$db->exec('
  CREATE VIRTUAL TABLE IF NOT EXISTS test USING fts4 (tokenize=unicode61, a TEXT);
  INSERT INTO test (a) VALUES ("coucou test");
');

var_dump($db->querySingle('SELECT * FROM test WHERE test MATCH \'coucou\';', true));

This returns correctly:

    array(1) {
       ["a"]=>  
       string(11) "coucou test"
    }

So I'm left wondering: how can I detect that FTS4 is available if it doesn't show up in compile_options? (apart from trying to create a table, see if it fails, and store this somewhere)

Or maybe it's a magic fallback to FTS3?

Thanks :)

(2) By Phil G (phil_g) on 2023-06-12 09:23:54 in reply to 1 [source]

According to the FTS 3/4 docs:

Note that enabling FTS3 also makes FTS4 available. There is not a separate SQLITE_ENABLE_FTS4 compile-time option. A build of SQLite either supports both FTS3 and FTS4 or it supports neither.

The second sentence there is not entirely true, since there is an SQLITE_ENABLE_FTS4 option, but the code appears to treat it as simply an alias for SQLITE_ENABLE_FTS3.

Also, in the list of compile-time options, the notes for SQLITE_ENABLE_FTS3 doesn't mention that version 4 will also be enabled. There is a separate entry on that page for SQLITE_ENABLE_FTS4 (which claims it enables both versions 3 and 4).

Possibly there's a minor doc-improvement opportunity here?

(3) By BohwaZ (bohwaz) on 2023-06-12 23:26:09 in reply to 2 [link] [source]

Ah! Thank you, because all other hosting servers do have a ENABLE_FTS4 compile option, I didn't know it was just an alias.

Awesome find, thanks!

(4) By Larry Brasfield (larrybr) on 2023-06-13 18:58:21 in reply to 2 [link] [source]

Possibly there's a minor doc-improvement opportunity here?

Yes, there was.