Laboratório local com SQLite FTS5

Objetivos

Nesta aula você vai:

• Criar FTS5 com content table e triggers de sincronização
• Executar MATCH (termo, frase, prefixo, AND/OR) com BM25 e snippet
• Ler EXPLAIN QUERY PLAN e corrigir erros comuns do lab local

Introdução

Com o seed em products, o passo local é montar o índice invertido sem duplicar o texto canônico: virtual table FTS5 com content='products', triggers e um conjunto de consultas que você repetirá no D1.

Use o sqlite3 CLI (ou qualquer cliente) apontando para um arquivo .db de laboratório.

Conteúdo

1. Virtual table FTS5 (external content)

CREATE VIRTUAL TABLE IF NOT EXISTS products_fts USING fts5(
  name,
  description,
  category,
  tags,
  content='products',
  content_rowid='id',
  tokenize='unicode61 remove_diacritics 2'
);

• content / content_rowid: o índice aponta para a PK inteira de products.
• tokenize='unicode61 remove_diacritics 2': favorece busca sem acento no português.
• Declare fts5 em minúsculas (obrigatório também no D1).

2. Triggers de sincronização

CREATE TRIGGER products_ai AFTER INSERT ON products BEGIN
  INSERT INTO products_fts(rowid, name, description, category, tags)
  VALUES (new.id, new.name, new.description, new.category, new.tags);
END;

CREATE TRIGGER products_ad AFTER DELETE ON products BEGIN
  INSERT INTO products_fts(products_fts, rowid, name, description, category, tags)
  VALUES ('delete', old.id, old.name, old.description, old.category, old.tags);
END;

CREATE TRIGGER products_au AFTER UPDATE ON products BEGIN
  INSERT INTO products_fts(products_fts, rowid, name, description, category, tags)
  VALUES ('delete', old.id, old.name, old.description, old.category, old.tags);
  INSERT INTO products_fts(rowid, name, description, category, tags)
  VALUES (new.id, new.name, new.description, new.category, new.tags);
END;

3. Backfill do seed já existente

Se os INSERT do seed rodaram antes dos triggers:

INSERT INTO products_fts(rowid, name, description, category, tags)
SELECT id, name, description, category, tags FROM products;

Confirme:

SELECT COUNT(*) FROM products;
SELECT COUNT(*) FROM products_fts;

Os totais devem coincidir.

4. Consultas MATCH

Termo simples:

SELECT p.id, p.name, bm25(products_fts) AS score
FROM products_fts
JOIN products p ON p.id = products_fts.rowid
WHERE products_fts MATCH 'furadeira'
ORDER BY bm25(products_fts)
LIMIT 10;

Frase:

SELECT p.id, p.name
FROM products_fts
JOIN products p ON p.id = products_fts.rowid
WHERE products_fts MATCH '"chave de fenda"';

Prefixo (autocomplete):

SELECT p.id, p.name
FROM products_fts
JOIN products p ON p.id = products_fts.rowid
WHERE products_fts MATCH 'tecla*'
ORDER BY bm25(products_fts)
LIMIT 5;

AND / OR:

-- ambos os termos
SELECT p.id, p.name
FROM products_fts
JOIN products p ON p.id = products_fts.rowid
WHERE products_fts MATCH 'usb AND hub';

-- qualquer um
SELECT p.id, p.name
FROM products_fts
JOIN products p ON p.id = products_fts.rowid
WHERE products_fts MATCH 'laser OR nivel';

No FTS5, espaços entre termos costumam implicar AND; OR e frases com aspas são explícitos. Prefira documentar a política no endpoint (próxima aula).

5. BM25, highlight e snippet

Scores menores (mais negativos) com bm25() são melhores — daí ORDER BY bm25(products_fts).

SELECT
  p.id,
  p.name,
  bm25(products_fts) AS score,
  highlight(products_fts, 0, '<b>', '</b>') AS name_hl,
  snippet(products_fts, 1, '<mark>', '</mark>', '…', 24) AS desc_snip
FROM products_fts
JOIN products p ON p.id = products_fts.rowid
WHERE products_fts MATCH 'bluetooth OR fone'
ORDER BY score
LIMIT 10;

• highlight(fts, col_index, open, close) — índice 0 = name, 1 = description, …
• snippet(...) — trecho curto para UI de resultados.

Pesos de coluna (título/nome mais importante):

SELECT p.id, p.name, bm25(products_fts, 10.0, 1.0, 2.0, 3.0) AS score
FROM products_fts
JOIN products p ON p.id = products_fts.rowid
WHERE products_fts MATCH 'sql'
ORDER BY score
LIMIT 10;

Ordem dos pesos: name, description, category, tags.

6. Performance com EXPLAIN QUERY PLAN

EXPLAIN QUERY PLAN
SELECT p.id, p.name
FROM products_fts
JOIN products p ON p.id = products_fts.rowid
WHERE products_fts MATCH 'webcam'
ORDER BY bm25(products_fts)
LIMIT 10;

Espere menção a uso da virtual table / índice FTS — não um scan cego só em products com filtro textual.

Compare com o antipadrão:

EXPLAIN QUERY PLAN
SELECT id, name FROM products
WHERE name LIKE '%webcam%' OR description LIKE '%webcam%';

No seed pequeno a diferença de tempo é sutil; o plano já mostra o contraste que importa em catálogos grandes.

Medição simples no CLI:

.timer on
SELECT ... WHERE products_fts MATCH 'hub';
SELECT ... WHERE name LIKE '%hub%' OR description LIKE '%hub%';

Exemplos práticos

Roteiro sugerido (15–20 min):

1. Criar products_fts + triggers + backfill.
2. Rodar termo, frase, tecla*, usb AND hub.
3. Exibir snippet e bm25.
4. EXPLAIN QUERY PLAN em FTS vs LIKE.
5. Atualizar um name e confirmar que o MATCH reflete o novo texto (trigger UPDATE).

Problemas e como resolver

Rebuild de emergência:

INSERT INTO products_fts(products_fts) VALUES('rebuild');

Resumo

No lab local você fecha o ciclo FTS5: virtual table com content externo, triggers, backfill, consultas (termo, frase, prefixo, AND/OR), ranking BM25 e snippets, mais leitura de plano de execução. Na próxima aula, o mesmo schema migra para Cloudflare D1 e vira GET /search no Worker.