Detecting FTS4 Availability When ENABLE_FTS4 Is Missing from SQLite Compile Options

Understanding Discrepancies Between FTS4 Functionality and Compile Option Visibility

Issue Overview: FTS4 Tables Function Despite Absence of ENABLE_FTS4 in Compile Options

The core issue arises when a SQLite database instance exhibits functional Full-Text Search Version 4 (FTS4) capabilities despite the absence of the ENABLE_FTS4 flag in the output of PRAGMA compile_options. This behavior contradicts expectations, as SQLite’s compile-time options typically dictate which features are available at runtime. For example, a user reported that their shared hosting environment (running SQLite 3.39.4) successfully created and queried an FTS4 virtual table even though ENABLE_FTS4 was missing from the compile options.

Key Observations:

1. Compile Options vs. Runtime Behavior: The PRAGMA compile_options output listed ENABLE_FTS3 but not ENABLE_FTS4. However, creating an FTS4 table and querying it worked without errors.
2. Documentation Ambiguity: SQLite’s official documentation initially suggested that enabling FTS3 automatically enables FTS4, but later sections implied that ENABLE_FTS4 is a distinct compile-time option.
3. Detection Challenges: Users seeking to programmatically verify FTS4 availability may rely on compile_options, but this method fails when ENABLE_FTS4 is omitted despite FTS4 being functional.

Underlying Mechanics of FTS3/FTS4 in SQLite:
FTS3 and FTS4 are closely related modules for full-text search. While FTS4 introduces performance optimizations and additional features (e.g., merge commands, language-specific tokenizers), it shares foundational code with FTS3. Historically, enabling FTS3 at compile time allowed limited use of FTS4 syntax, but this behavior evolved over SQLite versions. The confusion stems from the fact that modern SQLite builds often treat ENABLE_FTS3 and ENABLE_FTS4 as interdependent flags rather than entirely separate options.

Why This Matters:
Accurate detection of FTS4 support is critical for applications that depend on its advanced features. Misinterpreting compile options could lead to false assumptions about compatibility, especially in environments where SQLite is precompiled (e.g., shared hosting, embedded systems).

Possible Causes: Compile-Time Flag Aliasing and Documentation Misalignment

1. Compile-Time Flag Aliasing Between FTS3 and FTS4

SQLite’s build system treats SQLITE_ENABLE_FTS3 and SQLITE_ENABLE_FTS4 as overlapping flags. The source code reveals that enabling SQLITE_ENABLE_FTS3 automatically activates a subset of FTS4 functionality. Specifically:

• The ENABLE_FTS4 compile option is an alias for ENABLE_FTS3 in certain SQLite versions.
• The build process may omit ENABLE_FTS4 from compile_options even when FTS4 features are available, depending on how the codebase is configured.

Code Analysis:
In SQLite’s source tree, the fts3Int.h header file defines conditional compilation blocks:

#if defined(SQLITE_ENABLE_FTS3) && !defined(SQLITE_ENABLE_FTS4)
# define SQLITE_ENABLE_FTS4 0
#elif !defined(SQLITE_ENABLE_FTS3) && defined(SQLITE_ENABLE_FTS4)
# define SQLITE_ENABLE_FTS3 1
#endif

This logic ensures that FTS3 and FTS4 are either both enabled or both disabled. However, the PRAGMA compile_options output may only list the explicitly defined flag (e.g., ENABLE_FTS3), even though FTS4 is functionally present.

2. Documentation Inconsistencies

The SQLite documentation historically contained conflicting statements:

• The FTS3/FTS4 documentation states, “Note that enabling FTS3 also makes FTS4 available. There is not a separate SQLITE_ENABLE_FTS4 compile-time option.”
• Conversely, the compile options documentation lists SQLITE_ENABLE_FTS4 as a distinct flag, claiming it enables both FTS3 and FTS4.

This discrepancy creates confusion about whether FTS4 requires a separate flag or is implicitly enabled with FTS3.

3. Build-Specific Customizations

Third-party SQLite builds (e.g., those provided by Linux distributions or hosting providers) may apply patches or custom configurations that alter the relationship between FTS3 and FTS4 flags. For example:

• A build script might enable FTS3 and manually include FTS4 code without setting the ENABLE_FTS4 flag.
• Legacy systems might backport FTS4 features into FTS3-enabled builds for compatibility.

Troubleshooting Steps and Solutions: Ensuring Accurate Detection of FTS4 Support

1. Validate FTS4 Availability via Runtime Checks

Since compile options may not reliably reflect FTS4 support, perform runtime tests:

Step 1: Attempt to Create an FTS4 Table
Execute a CREATE VIRTUAL TABLE statement with USING fts4 and check for errors:

CREATE VIRTUAL TABLE IF NOT EXISTS test_fts4 USING fts4(content);

If the command succeeds, FTS4 is available.

Step 2: Query the sqlite_master Table
Verify that the FTS4 module is registered:

SELECT sql FROM sqlite_master WHERE name = 'test_fts4';

The output should include USING fts4.

Step 3: Test FTS4-Specific Features
Use an FTS4-exclusive feature like the merge command:

INSERT INTO test_fts4(test_fts4) VALUES('merge=50');

If no error occurs, FTS4 is fully operational.

2. Interpret Compile Options Correctly

When ENABLE_FTS3 is present in PRAGMA compile_options, assume FTS4 is available unless proven otherwise.

Example Workflow:

import sqlite3

conn = sqlite3.connect(':memory:')
cursor = conn.cursor()
cursor.execute('PRAGMA compile_options')
compile_options = [row[0] for row in cursor.fetchall()]

fts4_enabled = 'ENABLE_FTS3' in compile_options
if fts4_enabled:
    print("FTS4 is likely available (via ENABLE_FTS3).")
else:
    print("FTS4 is not enabled.")

3. Consult the SQLite Version and Documentation

Newer SQLite versions (3.9.0+) unify FTS3/FTS4 enablement under ENABLE_FTS3. Cross-reference the version with official docs:

SELECT sqlite_version();

• For SQLite ≥ 3.9.0, ENABLE_FTS3 implies FTS4 support.
• For older versions, check for ENABLE_FTS4 explicitly.

4. Advocate for Documentation Clarity

Submit corrections to the SQLite documentation to resolve ambiguities. For example:

• Update the compile options page to clarify that ENABLE_FTS3 enables both FTS3 and FTS4.
• Remove outdated references to ENABLE_FTS4 as a standalone option.

5. Handle Edge Cases in Custom Builds

If working with a non-standard SQLite build:

• Contact the provider to confirm FTS4 status.
• Use a combination of compile options and runtime checks.

Final Recommendations:

1. Prefer Runtime Checks Over Compile Options: Directly testing FTS4 functionality avoids reliance on potentially misleading flags.
2. Update Detection Logic: Treat ENABLE_FTS3 as a sufficient (but not necessary) indicator of FTS4 support.
3. Document Assumptions: Clearly state in your codebase that FTS4 may be available even if ENABLE_FTS4 is absent.

By addressing compile-time aliasing, clarifying documentation, and implementing robust runtime checks, developers can reliably detect FTS4 support across diverse SQLite environments.