FTS5 Contentless vs. External Content Tables and Trigram Tokenizer Issues

Differences Between FTS5 Contentless and External Content Tables

The core issue revolves around understanding the differences between FTS5 (Full-Text Search version 5) contentless and external content tables, particularly when using the trigram tokenizer. The discussion highlights several key points of confusion, including the size implications of each table type, the functionality trade-offs, and the challenges of using the trigram tokenizer with contentless and external content tables. Additionally, there are specific issues with querying these tables when they are stored in attached databases.

Challenges with Trigram Tokenizer and Attached Databases

The discussion reveals a critical problem when using the trigram tokenizer with contentless and external content tables, especially when these tables are stored in attached databases. The user encountered two main issues:

1. No Results from Contentless Trigram Tables: When querying a contentless FTS5 table using the trigram tokenizer, no results were returned, even though the same query worked on a standard FTS5 table with content.

2. Runtime Errors with External Content Trigram Tables: When querying an external content FTS5 table using the trigram tokenizer, a runtime error occurred, indicating that the referenced table (fts3.t) did not exist. This error persisted even when the content table was explicitly referenced with the schema name (main.t).

These issues suggest that there may be limitations or bugs in how SQLite handles contentless and external content FTS5 tables with the trigram tokenizer, particularly when these tables are stored in attached databases.

Troubleshooting Contentless and External Content FTS5 Tables

To address the issues outlined above, we need to explore the following areas in detail:

1. Understanding the Differences Between Contentless and External Content Tables: This involves examining the structural and functional differences between these two table types, including their size implications and the trade-offs in functionality.

2. Investigating Trigram Tokenizer Compatibility: This involves testing and validating the behavior of the trigram tokenizer with contentless and external content tables, particularly in scenarios where the FTS5 tables are stored in attached databases.

3. Resolving Runtime Errors and Query Issues: This involves identifying the root cause of the runtime errors and no-result scenarios, and proposing potential fixes or workarounds.

Understanding the Differences Between Contentless and External Content Tables

Structural Differences

Both contentless and external content FTS5 tables are designed to avoid duplicating the indexed content, which can significantly reduce the size of the FTS5 index. However, they achieve this in different ways:

• Contentless Tables: These tables do not store any content at all. Instead, they only store the tokens and their associated metadata (rowid, column number, and position). This means that any auxiliary function that requires access to the original content (e.g., snippet or highlight) will return null.

• External Content Tables: These tables also do not store the content directly but instead reference an external table that contains the original content. This allows auxiliary functions to access the original content by querying the external table.

Size Implications

In the discussion, the user observed that both contentless and external content tables were the same size (5.4G), which is approximately twice the size of the source table (2.8G). This suggests that the size reduction comes from not duplicating the content, rather than from any difference in how the tokens are stored.

However, the user also noted that a standard FTS5 table (with content) was significantly larger (9.9G), which is approximately three times the size of the source table. This highlights the space savings achieved by using contentless or external content tables.

Functional Trade-offs

The primary functional difference between contentless and external content tables is the ability to access the original content. Contentless tables cannot provide the original content to auxiliary functions, which limits their usefulness in scenarios where functions like snippet or highlight are required.

External content tables, on the other hand, can provide the original content by querying the external table, making them more versatile. However, this comes with the added complexity of managing the relationship between the FTS5 table and the external content table.

Investigating Trigram Tokenizer Compatibility

Trigram Tokenizer Overview

The trigram tokenizer is a specialized tokenizer that breaks text into overlapping sequences of three characters (trigrams). This is particularly useful for substring searches and autocomplete functionality. However, the trigram tokenizer can significantly increase the size of the FTS5 index because it generates a large number of tokens for even short pieces of text.

Compatibility with Contentless and External Content Tables

The discussion suggests that the trigram tokenizer is compatible with both contentless and external content tables, as the user was able to create these tables without any errors. However, the user encountered issues when querying these tables:

1. No Results from Contentless Trigram Tables: The user observed that queries against a contentless trigram table returned no results, even though the same query worked on a standard FTS5 table with content. This suggests that the trigram tokenizer may not be functioning correctly with contentless tables, or that there may be an issue with how the tokens are being stored or queried.

2. Runtime Errors with External Content Trigram Tables: The user encountered a runtime error when querying an external content trigram table, indicating that the referenced table (fts3.t) did not exist. This error suggests that SQLite is having trouble resolving the reference to the external content table, particularly when the FTS5 table is stored in an attached database.

Potential Causes of Query Issues

The issues with querying contentless and external content trigram tables may be due to the following factors:

1. Token Storage and Retrieval: The trigram tokenizer generates a large number of tokens, which may be stored differently in contentless and external content tables. If the tokens are not being stored or retrieved correctly, this could explain why no results are returned from contentless tables.

2. Schema Resolution in Attached Databases: When using attached databases, SQLite may have difficulty resolving references to external content tables, particularly if the schema name is not explicitly specified. This could explain the runtime error encountered when querying the external content trigram table.

Resolving Runtime Errors and Query Issues

Addressing No Results from Contentless Trigram Tables

To resolve the issue of no results being returned from contentless trigram tables, the following steps can be taken:

1. Verify Tokenizer Configuration: Ensure that the trigram tokenizer is correctly configured and that the tokens are being generated and stored as expected. This can be done by inspecting the FTS5 table’s internal structures using SQLite’s diagnostic tools.

2. Check Query Syntax: Ensure that the query syntax is correct and that the search terms are being interpreted as expected by the trigram tokenizer. For example, the LIKE operator may not be the most appropriate for trigram searches, and the MATCH operator should be used instead.

3. Test with Standard FTS5 Tables: Compare the behavior of the contentless trigram table with a standard FTS5 table that uses the trigram tokenizer. This can help identify any differences in how the tokens are being stored or queried.

Addressing Runtime Errors with External Content Trigram Tables

To resolve the runtime error encountered when querying external content trigram tables, the following steps can be taken:

1. Explicitly Specify Schema Names: When creating the external content FTS5 table, explicitly specify the schema name for the content table. For example, use content='main.t' instead of content='t'. This ensures that SQLite can correctly resolve the reference to the external content table.

2. Check Attached Database Configuration: Ensure that the attached database is correctly configured and that the external content table is accessible from the FTS5 table. This can be done by running a simple query against the external content table to verify that it exists and is accessible.

3. Use Triggers for Data Synchronization: If the external content table is being updated independently of the FTS5 table, consider using triggers to ensure that the FTS5 index is kept up to date. This can help prevent issues where the FTS5 table references content that no longer exists in the external content table.

Conclusion

The issues discussed highlight the complexities of using FTS5 contentless and external content tables, particularly when combined with the trigram tokenizer and attached databases. While both table types offer significant space savings compared to standard FTS5 tables, they come with trade-offs in functionality and potential challenges in querying and schema resolution.

To address these issues, it is important to carefully configure the FTS5 tables, explicitly specify schema names, and use appropriate query syntax. Additionally, thorough testing and validation are essential to ensure that the trigram tokenizer is functioning correctly and that the FTS5 index is being maintained as expected.

By following the troubleshooting steps outlined above, users can overcome the challenges associated with contentless and external content FTS5 tables and leverage the full power of SQLite’s full-text search capabilities.