ビューで FTS を操る！SQLite の全文検索を拡張する魔法のテクニック

SQLite FTS テーブルをビューでサポートできるのか？

FTS (Full Text Search) テーブルは、SQLite における全文検索機能を提供する拡張機能です。一方、ビューは、既存のテーブルからデータを定義する仮想テーブルの一種です。

FTS テーブルは、専用の内部構造を持つため、通常のテーブルとは異なり、ビューで直接定義することはできません。しかし、外部コンテンツテーブルと呼ばれる機能を利用することで、ビューを介して FTS テーブルと間接的に連携させることは可能です。

外部コンテンツテーブルは、FTS5 以降で導入された機能で、FTS テーブルのコンテンツを別のテーブルから取得することができます。この機能を利用することで、ビューを介して FTS テーブルにデータを提供することができます。

外部コンテンツテーブルを作成するには、CREATE FTS5 TABLE ステートメントの content オプションにビューの名前を指定します。FTS テーブルがクエリを実行すると、content オプションで指定されたビューがクエリされ、その結果が FTS テーブルのコンテンツとして使用されます。

ビューで FTS テーブルをサポートする場合の注意点

• ビューには、rowid という名前の列が必ず含まれている必要があります。この列は、FTS テーブルの内部的なドキュメントIDに対応します。
• ビューの列式は、FTS テーブルの列式と一致する必要があります。
• ビューは、常に最新のデータを提供する必要があります。FTS テーブルはビューのキャッシュを保持しないため、ビューのデータに変更があった場合は、FTS テーブルを再構築する必要があります。

FTS テーブルとビューを組み合わせる場合は、上記の点に注意する必要があります。

例

以下の例は、articles テーブルと authors テーブルを使用して、articles テーブルの全文検索を可能にする FTS テーブルを作成する方法を示しています。

CREATE TABLE articles (
  id INTEGER PRIMARY KEY,
  title TEXT,
  body TEXT,
  author_id INTEGER,
  FOREIGN KEY (author_id) REFERENCES authors(id)
);

CREATE TABLE authors (
  id INTEGER PRIMARY KEY,
  name TEXT
);

CREATE VIEW author_name_view AS
SELECT articles.id, articles.title, articles.body, authors.name AS author_name
FROM articles
JOIN authors ON articles.author_id = authors.id;

CREATE FTS5 TABLE articles_fts5 (
  id INTEGER PRIMARY KEY,
  title TEXT,
  body TEXT,
  author_name TEXT,
  content = 'author_name_view'
);

この例では、author_name_view ビューは、articles テーブルと authors テーブルを結合して、各記事に著者の名前を追加します。articles_fts5 FTS テーブルは、author_name_view ビューの内容を基にして作成され、title、body、および author_name 列の全文検索をサポートします。

サンプルコード：SQLite FTS テーブルとビュー

データベーススキーマ

この例では、以下の3つのテーブルを使用します。

• articles: 記事のタイトル、本文、公開日などを格納します。
• authors: 記事の著者情報を格納します。
• articles_fts5: FTS テーブルで、記事のタイトル、本文、および著者名に対する全文検索をサポートします。

テーブル定義

CREATE TABLE articles (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  body TEXT NOT NULL,
  author_id INTEGER NOT NULL,
  published_at DATETIME NOT NULL,
  FOREIGN KEY (author_id) REFERENCES authors(id)
);

CREATE TABLE authors (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE VIEW author_name_view AS
SELECT articles.id, articles.title, articles.body, authors.name AS author_name
FROM articles
JOIN authors ON articles.author_id = authors.id;

CREATE FTS5 TABLE articles_fts5 (
  id INTEGER PRIMARY KEY,
  title TEXT,
  body TEXT,
  author_name TEXT,
  content = 'author_name_view'
);

データ挿入

INSERT INTO authors (name) VALUES ('John Doe');
INSERT INTO authors (name) VALUES ('Jane Doe');

INSERT INTO articles (title, body, author_id, published_at)
VALUES ('My First Blog Post', 'This is my first blog post.', 1, '2024-06-24');

INSERT INTO articles (title, body, author_id, published_at)
VALUES ('My Second Blog Post', 'This is my second blog post.', 2, '2024-06-23');

全文検索

FTS テーブルを使用して、記事のタイトル、本文、または著者名で全文検索を実行できます。

SELECT * FROM articles_fts5
WHERE title MATCH 'blog' OR body MATCH 'blog' OR author_name MATCH 'blog';

このクエリは、blog というキーワードを含むタイトル、本文、または著者名の記事をすべて返します。

ビューの利点

ビューを使用することで、FTS テーブルを介してデータをクエリする際に、柔軟性と抽象化のレベルを向上させることができます。

• ビューを使用すると、FTS テーブルの構造を変更せずに、検索条件を変更できます。
• ビューを使用すると、複数の FTS テーブルを単一のクエリで結合できます。
• ビューを使用すると、セキュリティ上の理由から、FTS テーブルの基盤となるテーブルへの直接アクセスを制限できます。

注意事項

• FTS テーブルは、ビューに格納されたデータに基づいてのみ全文検索を実行できます。ビューのデータが最新でない場合、検索結果が不正確になる可能性があります。
• FTS テーブルは、ビューの変更を自動的に検知しません。ビューを変更した場合は、FTS テーブルを再構築する必要があります。

このサンプルコードは、SQLite FTS テーブルとビューを使用して、基本的な全文検索機能を実装する方法を示しています。より複雑な検索要件については、SQLite FTS5 のドキュメントを参照してください。

SQLite FTS テーブルとビュー以外のアプローチ

カスタムトークナイザと全文インデックス

FTS テーブルを使用せずに、カスタムトークナイザと全文インデックスを使用して、独自の全文検索ソリューションを構築できます。この方法は、よりきめ細かな検索制御と、特定のアプリケーションのニーズに合わせたカスタマイズ性を提供します。

サードパーティの全文検索ライブラリ

SQLite には、全文検索機能 が組み込まれていますが、他にも 全文検索機能を提供するサードパーティ製ライブラリがいくつかあります。これらのライブラリは、FTS5 にない追加機能や、特定のユースケースに最適化されたパフォーマンスを提供する場合があります。

外部検索エンジン

場合によっては、Elasticsearch や Algolia などの外部検索エンジンを使用する方が、より適切な場合があります。これらの検索エンジンは、スケーラビリティ、冗長性、高度な分析機能など、エンタープライズレベルの機能を提供します。

最適なアプローチは、特定の要件と制約条件によって異なります。

• データ量と検索ボリューム: データ量と検索ボリュームが多い場合は、FTS テーブルまたはサードパーティの全文検索ライブラリが適切な場合があります。
• カスタマイズの必要性: 検索ロジックを高度にカスタマイズする必要がある場合は、カスタムトークナイザと全文インデックスを使用する方がよい場合があります。
• スキルとリソース: 独自の全文検索ソリューションを構築するには、開発スキルとリソースが必要となります。
• コスト: サードパーティの検索エンジンは、通常、サブスクリプション費用がかかります。

各オプションの詳細

以下に、各オプションの詳細な説明と、それぞれの長所と短所をまとめました。

長所:

• 高度なカスタマイズ性
• 特定のアプリケーションのニーズに合わせた調整が可能
• オープンソースソリューションが多い

• 開発とメンテナンスに時間がかかる
• 専門知識が必要
• パフォーマンスが FTS テーブルほど優れていない場合がある

• FTS5 にない追加機能を提供するものがある
• 特定のユースケースに最適化されたパフォーマンスを提供するものがある

• ライセンス費用がかかる場合がある
• SQLite との統合が難しい場合がある

• スケーラビリティ
• 冗長性
• 高度な分析機能
• 専門知識がなくても利用可能

• サブスクリプション費用がかかる
• データを外部に保存する必要がある

SQLite FTS テーブルとビューは、シンプルな全文検索ソリューションを提供しますが、より複雑な要件の場合は、他のオプションを検討する必要があります。最適なアプローチは、特定の要件と制約条件によって異なります。