One box for every script
Dad's corpus never stays in one writing system. A Spanish sign sits next to its Korean translation, a Japanese station name next to a hanja root, a pronunciation in Latin letters under all of it. A search that splits text on spaces fails most of that. Japanese and Chinese put no spaces between words. Korean glues particles onto nouns, so 화장실 (restroom) arrives as 화장실이 or 화장실에서, and a word index stores those as different words.
Spark answers with one table, the first code block: an FTS5 virtual table using the trigram tokenizer. The comment above it states the whole contract. Every text the corpus holds is derived into it, trigrams match inside words in every script, and a query under three characters falls back to LIKE.
What a trigram index sees
The trigram tokenizer indexes every run of three consecutive characters. Farmacia becomes far, arm, rma, mac, aci, cia. A query of three or more characters is cut the same way, and a phrase matches where its trigrams appear in sequence. So macia finds Farmacia de guardia, 화장실 finds 화장실이 어디예요?, and 出口は finds a Japanese sign. No dictionary, no word boundaries, no language detection.
| Question | Default word tokenizer (unicode61) | Trigram tokenizer |
|---|---|---|
| What is a token? | A word between separators | Every three-character run |
| Does 화장실 find 화장실이 어디예요? | No: 화장실이 is one token | Yes |
| Does 出口 or 出口は find 出口はこちら | No: the whole line is one token | Yes for 出口は; 出口 is too short |
| What does it cost? | A small index | A larger index, and no match under three characters |
I ran each row of that table against SQLite before writing it down. Two more behaviours are worth knowing. Matching ignores case by default, beyond ASCII too: água finds ÁGUA. Accents do count: donde does not find ¿Dónde está el baño?. The tokenizer has a remove_diacritics option that would fold them, and Spark leaves it at the default.
What gets indexed
The second code block writes the rows. Each record gets exactly one row, keyed by ref. A result's row joins the non-empty values of ten fields with newlines: source, translation, pronunciation, note, use, comment, quote, root, gloss and expression. A moment's own row holds its text, its place name and its situation, with the kind moment. Before inserting, _index deletes any earlier row for the same ref, so an edited moment is indexed again instead of twice. One edge is worth knowing if you copy it: when every field is empty, the function returns before that delete, so an edit that clears a moment's last words leaves its old row searchable.
The field list decides what Dad can find. A phrase is found by its Korean translation as easily as by its source, a sign by its pronunciation, a high-quality version by a cold reviewer's comment, a root by its gloss. And because search is one of the mirror's derived tables, it is dropped and rebuilt from the log like the rest, so a new field in the list reaches every old moment on the replay that a schema version bump triggers.
Three characters, or LIKE
The third code block is the query. An empty needle returns nothing. A needle of three or more characters is wrapped in double quotes, with any quote inside it doubled, and sent to MATCH. That one line turns whatever Dad typed into a single FTS5 phrase. Words like OR and NOT, a trailing * or a column: prefix become plain text to find. Without the quoting, a search for guardia OR is a syntax error; I checked that too.
Under three characters, a trigram query cannot match at all; the SQLite documentation says so plainly. So the engine switches to LIKE '%needle%'. This branch matters most for the scripts the index was chosen for: 出口, 출구 and 물 are whole words of one or two characters. It has three costs. A pattern that short gives FTS5 nothing to use, so it scans the table, which a single person's corpus affords. SQLite's LIKE folds case only for ASCII, which never matters for Korean, Japanese or Chinese and only rarely for a two-letter Latin search. And this branch is not literal. The needle goes into the pattern unescaped, so a % or _ Dad types is a wildcard here: a search for _ alone matches every row. The quoting that makes the long branch literal has no twin on this one. Escaping both characters and adding an ESCAPE clause would give it one.
Both branches join the hit to its moment, skip deleted moments, and sort newest capture first, with the index's own row order breaking ties. The limit (60 by default, at most 500) counts hits.
Grouped by moment
Corpus.search folds the hits under their moments. Each group carries the moment's capture truth (kind, time, date, situation, place, scene language, trip and text) and its hits, and a result hit carries the whole result. The web's search page shows the groups; the phone's Library search shows up to three hits per moment as source and translation. The same answer is what cwkPippa's reader receives, which is the next lesson.
The engine's test reads like the contract. A word in a moment's text finds that moment and skips a deleted moment holding the same word. A three-character Korean query finds a phrase through its translation. A one-character Korean query still finds it, through LIKE. A place name comes back as a moment hit. An unrelated phrase finds nothing. SQLite Quest's FTS5 lesson covers the virtual tables, ranking and tokenizer options in depth.