Full Text Search and Full Text Index in SQLite

[i-h8-github]

Hello,

Is full text indexing and searching only supported in Postgres? Or is there a way to utilize it with other DBs such as SQLite?

Thank you in advance for your time and attention.

[jeremyevans]

Dataset#full_text_search is currently supported on PostgreSQL and MySQL. The issue with SQLite is that there are multiple full text search options (fts3, fts4, fts5), and none is supported by default (e.g. you have to build with --enable-fts5 to enable fts5). So supporting in by default is probably not going to happen unless SQLite commits to a full text search option and enables it by default. It would be fairly simple to add an extension for it, but it's not necessary with fts5, since you can use an equals operator or select from a function and you do not need special syntax. SQLite fts5 supports all of the following syntax (all equivalent):

SELECT * FROM email WHERE email MATCH 'fts5';
SELECT * FROM email WHERE email = 'fts5';
SELECT * FROM email('fts5');

Only the first query in requires special support in Sequel. The other two are already supported without any changes needed:

DB[:email].where(email: 'fts5')
DB.from{email('fts5')}

[jeremyevans]

For FTS5, the full text index is implicit in the virtual table, it's not a separate index like it is for PostgreSQL/MySQL (didn't look into FTS3/FTS4). Sequel doesn't support the CREATE VIRTUAL TABLE syntax on SQLite, so you would have to execute raw SQL currently for that. I'm not sure if it's possible to check for FTS5 support, without trying to create a FTS5 virtual table.

[flavorjones]

👋 one of the sqlite3-ruby gem maintainers here.

none is supported by default

FTS5 is enabled by default in the amalgamation configure script, which is what the sqlite3-ruby gem uses as well as (I think) most package managers and distros. The gem turns on fts5 support explicitly in case the default ever changes. That is only relevant if folks are using the vendored sqlite3, but that is again the default.

FTS5 would only be unavailable to a user these days if they are building the gem from source AND have opted out of using the vendored version of sqlite AND are using a system library that has opted out of fts5 support.

I'm not sure if it's possible to check for FTS5 support, without trying to create a FTS5 virtual table

It is! You can use PRAGMA compile_options to see what's turned on:

SQLite3::Database.new(":memory:")
  .execute("PRAGMA compile_options").flatten.grep(/ENABLE/)
# => ["ENABLE_FTS3", "ENABLE_FTS4", "ENABLE_FTS5", "ENABLE_GEOPOLY", "ENABLE_MATH_FUNCTIONS", "ENABLE_RTREE"]

If I can help with sqlite3 support, please let me know!

[jeremyevans]

@flavorjones Thanks for the information. It should be possible to detect if FTS5 is supported and support full_text_search in that case. As mentioned earlier in the thread, that doesn't actually get you anything other than API compatibility with PostgreSQL and MySQL, though.

[jeremyevans]

I looked into this today and determined that SQLite does not support what is necessary for the Sequel full_text_search/full_text_index API. Here's how full_text_search works on MySQL and PostgresSQL:

DB.create_table(:posts){text :title; text :body; full_text_index :title}
# insert into posts

DB[:posts].full_text_search(:title, 'test')

SQLite does not appear to support this. SQLite's full text support relies on creating virtual tables, populating them separately, and then searching those virtual tables:

DB.create_table(:posts){text :title; text :body}
DB.run("CREATE VIRTUAL TABLE posts_search USING fts5(title)") 

# insert into posts
# also insert into posts_search

DB[:posts_search].where(posts_search: 'test')

This is a pretty significant impendence mismatch. In order to support Sequel's full_text_search API with compatibility for PostgreSQL and MySQL, Sequel would need to locate the appropriate FTS5 table related to the current table and columns being queried. I don't think that's possible, as the virtual tables are in no way tied to the underlying tables. The virtual tables are populated separately, and they could be populated with data from multiple tables or no tables at all.

Since running a full text search in SQLite does not require any special Sequel syntax (normal hash syntax works), it doesn't seem worthwhile to add full_text_search on SQLite. Making full_text_index on SQLite create the virtual table also doesn't make sense, because unlike on PostgreSQL and MySQL, adding information to the main table does not automatically update the virtual table.

I think the only change to Sequel that would be helpful is allowing something like:

DB.create_table(:posts_search, using: 'fts5(title)')
# CREATE VIRTUAL TABLE posts_search USING fts5(title)

That way you wouldn't need to write as much raw SQL.

[jeremyevans]

I added support for the create_table :using option in 5c0e37b