Open2
cloudflare D1で fts5 モジュールを使って全文検索してみる
![Polonity](https://res.cloudinary.com/zenn/image/fetch/s--8TBXNLk7--/c_limit%2Cf_auto%2Cfl_progressive%2Cq_auto%2Cw_70/https://storage.googleapis.com/zenn-user-upload/avatar/2f63025cff.jpeg)
alpha 版からの移行
私の場合、 D1 を alpha 版で使用していたので本チャン版に移行する。
![Polonity](https://res.cloudinary.com/zenn/image/fetch/s--8TBXNLk7--/c_limit%2Cf_auto%2Cfl_progressive%2Cq_auto%2Cw_70/https://storage.googleapis.com/zenn-user-upload/avatar/2f63025cff.jpeg)
fts の仮想テーブルを追加
本チャン移行が完了したら本題。
※移行中に fts5 を追加してもよいが、安全のためステップバイステップで作業した。
再び db.sql をダンプして作成し、 fts を追加したいテーブル(今回は myTableとする)用に
以下の通り fts5 の仮想テーブルと各トリガを追加する。
CREATE VIRTUAL TABLE IF NOT EXISTS myTableFTS USING fts5(
title,
memo,
content='myTable',
tokenize='trigram'
);
-- Insertトリガー
CREATE TRIGGER IF NOT EXISTS fts_insert AFTER INSERT ON myTable
BEGIN
INSERT INTO myTableFTS (title, memo) VALUES (new.title, new.memo);
END;
-- Updateトリガー
CREATE TRIGGER IF NOT EXISTS fts_update AFTER UPDATE ON myTable
BEGIN
UPDATE myTableFTS SET title = new.title, memo = new.memo WHERE rowid = old.rowid;
END;
-- Deleteトリガー
CREATE TRIGGER IF NOT EXISTS fts_delete AFTER DELETE ON myTable
BEGIN
DELETE FROM myTableFTS WHERE rowid = old.rowid;
END;
そして、上記db.sql を流し込むのみ。
一旦ローカル環境でやってみる。
wrangler d1 execute my-table --file=.\db.sql --local
npx wrangler d1 execute my-table --command="SELECT * FROM myTableFTS WHERE myTableFTS MATCH 'ほげほげ AND ふがふが'" --local
期待した結果が得られたら本番環境に適用する。
wrangler d1 execute myTable --file=.\db.sql
これで cloudflare workers に API を書いて実行できるようになる。
app.get("/blog/search", async c => {
const keyword = c.req.query('keyword');
const limit = c.req.query('limit');
const limitAsNumber = (limit !== undefined)? Number(limit) : 100;
const offset = c.req.query('offset');
const offsetAsNumber = (offset !== undefined)? Number(offset) : 0;
// check parameter
if(keyword === undefined) {
return new Response('Bad Request', {status: 400});
}
// +を ' AND ' に変換
const keyword_literal = keyword.replace(/\+/g, ' AND ');
// ====================================================================================
const activities = await c.env.DB.prepare(
`SELECT myTableFTS.id, myTable.*
FROM myTableFTS
JOIN myTable ON myTableFTS.id = myTable.id
WHERE myTableFTS
MATCH '${keyword_literal}'
LIMIT ${limitAsNumber}
OFFSET ${offsetAsNumber}`
).all();
return new Response(JSON.stringify(activities.results), {status: 200});
});