Open2

cloudflare D1で fts5 モジュールを使って全文検索してみる

PolonityPolonity

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});
});