Upgrade search to use sqlite FTS

[ckolderup]

@cadeef writes:

It may be worth investigating SQLite's FTS as an alternative to this approach. FTS provides full text search of specified columns via a virtual table, is quite performant, and has been shipped with SQLite's default build since 2015.

Glitch's container has a very old version of sqlite, so we are blocked on that for now, but leaving this here to revisit once the upgrades start coming in. Glitch's container has sqlite3 v3.11.0 as pointed out below, so we're good to go on this without any need to have to support both methods or anything like that.

[cadeef]

I was curious, 2.8.17 seemed exceptionally old for the age of Glitch, and spun up a bare node instance. The container environment appears to have SQLite 2 & 3.

$ sqlite3 -version
3.11.0 2016-02-15 17:29:24 3d862f207e3adc00f78066799ac5a8c282430a5f

Looks like postmarks is using sqlite3 for all of its database interaction based on my cursory grok of the code.

[ckolderup]

you are right-- I was in a rush, forgot that sqlite is different from sqlite3, and my brain jumped immediately to "glitch containers have old stuff in them". So we can do this! I still appreciate Steve's spike since the day I announced this project, I had people already asking if they could search, but I guess I'll rephrase the issue and remove the blocked label. Thanks for the poke.

[steve-bate]

I can take a look at this. I haven't used FTS (in sqlite3) but I'm interested in learning more about it. My understanding is that FTS is a sqlite3 extension and there are multiple versions of it (FTS1 - FTS5). Do you know which versions the Glitch sqlite3 supports (assuming the extension is included)? After implementing this, we should document the sqlite3 extension requirements for developers who self-host on other platforms than Glitch (like me).

[ckolderup]

looks like FTS3 & FTS4 are supported? (and according to this they're always packaged together, so that's a good sign)

from a Glitch container:

sqlite> PRAGMA compile_options;
ENABLE_COLUMN_METADATA
ENABLE_DBSTAT_VTAB
ENABLE_FTS3
ENABLE_FTS3_PARENTHESIS
ENABLE_FTS4
ENABLE_JSON1
ENABLE_LOAD_EXTENSION
ENABLE_RTREE
ENABLE_UNLOCK_NOTIFY
ENABLE_UPDATE_DELETE_LIMIT
HAVE_ISNAN
LIKE_DOESNT_MATCH_BLOBS
MAX_SCHEMA_RETRY=25
OMIT_LOOKASIDE
SECURE_DELETE
SOUNDEX
SYSTEM_MALLOC
THREADSAFE=1