peewee.OperationalError: unknown tokenizer: mozporter when opening Thunderbirds global-messages.sqlite about sqlite-web HOT 1 CLOSED

Well without the full traceback this was really not helpful as to debugging where exactly sqlite-web is running into problems with the missing tokenizer. Even if sqlite-browser can open the database file, I'm dubious whether you can actually query the full-text index table without the custom tokenizer.

In order to query the full-text search tables, you'll need Mozilla's custom mozporter shared library. It doesn't come offered as a standalone extension, but it was fairly easy to compile one from the available sources: https://github.com/mozilla/releases-comm-central/tree/master/mailnews/extensions/fts3

To install the tokenizer automatically in a loadable extension, you'll want to modify the module registration function to register the tokenizer directly, e.g.

    /* no error checking, just an example */
    sqlite3_stmt *pStmt;
    const char zSql[] = "SELECT fts3_tokenizer(?, ?)";

    sqlite3_prepare_v2(db, zSql, -1, &pStmt, 0);

    const static sqlite3_tokenizer_module* module;
    sqlite3Fts3PorterTokenizerModule(&module);
    sqlite3_bind_text(pStmt, 1, "mozporter", -1, SQLITE_STATIC);
    sqlite3_bind_blob(pStmt, 2, &module, sizeof(module), SQLITE_STATIC);
    sqlite3_step(pStmt);
    sqlite3_finalize(pStmt);

I built such an extension locally and it works just fine:

sqlite> .load ./libmozporter
sqlite> CREATE VIRTUAL TABLE documents USING fts3(id, title, content, tokenize=mozporter);
sqlite> INSERT INTO documents ("title", "content") VALUES ('The title', 'This is the content of the message');
sqlite> SELECT title, rank(matchinfo(documents)) from documents where documents match 'contents';
The title|1.0

In order to get it working with the loadable extension support in sqlite-web I did need to make a modification to ensure extensions are loaded before introspection occurs (see: ed80c34).

Once that was done, though, I had no problem running sqlite-web with the loadable extension, and viewing a FTS table using the mozporter tokenizer:

from sqlite-web.