FTS5 Extension Activation and Load Issues in SQLite on Windows x64

FTS5 Module Not Found Despite Correct PRAGMA Settings

The core issue revolves around the inability to activate and load the FTS5 extension in SQLite when using a Pascal compiler on a Windows x64 system. The user has confirmed that the SQLite3.dll they are using supports FTS5, as evidenced by successful operations in SQLite Manager with the same DLL. However, when attempting to use FTS5 within their Pascal application, the module fails to load, resulting in the error message: "No such module: FTS5". This occurs despite setting various PRAGMA configurations and attempting to load the extension explicitly using SELECT load_extension("SQLite.dll", "sqlite3_fts5_init");.

The user has tried multiple approaches to enable FTS5, including setting PRAGMA directives such as PRAGMA enable_fts5;, PRAGMA enable_fts5 = ON;, and PRAGMA enable_fts5 = TRUE;. None of these attempts resulted in runtime errors, but the FTS5 module remains inaccessible. The critical failure point is the load_extension command, which causes the application to break. This suggests that while the SQLite3.dll file itself supports FTS5, there is a disconnect between the Pascal environment and the SQLite extension loading mechanism.

Misconfigured Extension Loading and Compile-Time Flags

The root cause of this issue can be traced to two primary factors: misconfigured extension loading mechanisms and the absence of necessary compile-time flags during the SQLite library build.

First, the load_extension function in SQLite requires explicit enabling via the ENABLE_LOAD_EXTENSION compile-time flag. If this flag is not set during the compilation of the SQLite library, the load_extension function will not work, regardless of the PRAGMA settings. The user’s SQLite3.dll might have been compiled without this flag, or the Pascal wrapper might not be properly configured to handle extension loading.

Second, the FTS5 module itself must be enabled at compile time using the -DSQLITE_ENABLE_FTS5 flag. If this flag is missing, the FTS5 module will not be included in the SQLite binary, even if the load_extension function is available. The user’s SQLite3.dll might have been compiled without this flag, leading to the "No such module: FTS5" error.

Additionally, the user’s Pascal environment might not be correctly interfacing with the SQLite API for extension loading. The Pascal wrapper might lack the necessary bindings or configurations to support the load_extension function, or it might be incorrectly passing parameters to the SQLite library. This could explain why the load_extension command causes the application to break.

Verifying Compile-Time Flags and Correctly Loading FTS5

To resolve this issue, the following troubleshooting steps and solutions are recommended:

Step 1: Verify Compile-Time Flags

The first step is to verify whether the SQLite3.dll being used was compiled with the necessary flags for FTS5 and extension loading. This can be done by executing the following SQL command after opening a connection to the database:

PRAGMA compile_options;

This command returns a list of compile-time options that were used to build the SQLite library. Look for the following options in the output:

• ENABLE_FTS5: Indicates that the FTS5 module was included in the build.
• ENABLE_LOAD_EXTENSION: Indicates that the load_extension function is enabled.

If either of these options is missing, the SQLite3.dll must be recompiled with the appropriate flags. For example, using the following commands with a compatible compiler:

cl /Ox /MT /LD -DSQLITE_ENABLE_FTS5 -DSQLITE_ENABLE_LOAD_EXTENSION sqlite3.c fts5.c

Step 2: Ensure Proper Extension Loading

If the compile-time flags are correct, the next step is to ensure that the extension loading mechanism is properly configured. The load_extension function must be called with the correct parameters. The syntax for loading the FTS5 extension is:

SELECT load_extension('SQLite.dll', 'sqlite3_fts5_init');

However, this command will fail if the ENABLE_LOAD_EXTENSION flag is not set or if the Pascal wrapper does not support extension loading. To address this, ensure that the Pascal wrapper is configured to enable extension loading. This might involve setting specific options or using a different wrapper that supports this functionality.

Step 3: Use a Precompiled FTS5 DLL

If recompiling SQLite is not feasible, consider using a precompiled FTS5.dll. A precompiled DLL can be obtained from trusted sources or built using the instructions provided in the forum discussion. For example, the following link provides a precompiled FTS5.dll:
http://www.dessus.com/files/fts5.dll

To use this DLL, place it in the same directory as the SQLite3.dll and modify the load_extension command to reference the correct path:

SELECT load_extension('fts5.dll', 'sqlite3_fts5_init');

Step 4: Validate Pascal Wrapper Configuration

Ensure that the Pascal wrapper is correctly configured to support SQLite extensions. This might involve setting specific options in the Pascal code, such as:

SpecificOptions.Add('EnableLoadExtension = True');
SpecificOptions.Add('Direct = True');

Additionally, verify that the wrapper supports the load_extension function and that it is correctly passing parameters to the SQLite library. If the wrapper does not support extension loading, consider using a different wrapper or modifying the existing one to add this functionality.

Step 5: Test with Minimal Configuration

To isolate the issue, create a minimal test case with the following configuration:

1. Use a fresh SQLite3.dll compiled with ENABLE_FTS5 and ENABLE_LOAD_EXTENSION.
2. Place the FTS5.dll in the same directory as the SQLite3.dll.
3. Set the necessary PRAGMA options in the Pascal code:

ExecSQL('PRAGMA enable_fts5;');
ExecSQL('PRAGMA cache_size = 400000;');
ExecSQL('PRAGMA synchronous = OFF;');
ExecSQL('PRAGMA journal_mode = OFF;');
ExecSQL('PRAGMA locking_mode = EXCLUSIVE;');
ExecSQL('PRAGMA temp_store = MEMORY;');

4. Execute the load_extension command:

ExecSQL('SELECT load_extension("fts5.dll", "sqlite3_fts5_init");');

5. Test the FTS5 functionality by creating and querying an FTS5 table:

ExecSQL('CREATE VIRTUAL TABLE test USING fts5(content);');
ExecSQL('INSERT INTO test VALUES ("This is a test");');

By following these steps, the issue should be resolved, and the FTS5 module should be successfully activated and loaded in the Pascal application. If the problem persists, consider reaching out to the maintainers of the Pascal wrapper or the SQLite community for further assistance.