SQLite FTS5 Extension

2025-05-26

FTS5の主な特徴と仕組み

仮想テーブル (Virtual Table): FTS5は、通常のテーブルとは異なり「仮想テーブル」として機能します。これは、実データを直接格納するのではなく、検索のためのインデックスデータを持つ特殊なテーブルです。実際のテキストデータは別の通常テーブルに格納し、FTS5仮想テーブルはそれに紐づく全文検索インデックスとして利用されるのが一般的です。
転置インデックス (Inverted Index): トークナイザーによって分解された各トークンに対して、それがどのドキュメント（行）のどこに出現するかを示す「転置インデックス」が作成されます。これにより、検索時にデータベース全体をスキャンすることなく、高速に該当するドキュメントを特定できます。
クエリ構文: FTS5は、MATCH演算子や=演算子を使って全文検索クエリを実行します。
- SELECT * FROM your_fts_table WHERE your_fts_table MATCH '検索語';
- SELECT * FROM your_fts_table WHERE your_fts_table = '検索語'; また、以下のような高度な検索もサポートしています。
- AND/OR/NOT: 複数の検索語を結合したり除外したりします。
- フレーズ検索: 複数の単語が特定の順序で連続して出現するものを検索します（例: "full text search"）。
- 近接検索 (NEAR): 複数の単語が指定された距離内に出現するものを検索します（例: word1 NEAR word2）。
- プレフィックス検索: 単語の先頭部分で検索します（例: appl* で "apple", "application" など）。
関連度ランキング (Relevance Ranking): FTS5は、rankカラムやbm25()関数などの補助関数を使って、検索結果の関連度を評価し、関連性の高い順にソートする機能を提供します。

FTS5の利用例 (SQLiteシェル)

-- FTS5仮想テーブルの作成
-- 'content' カラムにテキストデータを格納すると仮定
CREATE VIRTUAL TABLE documents USING fts5(title, body);

-- データの挿入
INSERT INTO documents (title, body) VALUES
('SQLite FTS5の紹介', 'SQLiteの全文検索機能であるFTS5は、高速なテキスト検索を可能にします。'),
('データベース入門', 'データベースの基本的な概念とSQLについて学びます。'),
('FTS5のカスタマイズ', 'FTS5のトークナイザーをカスタムする方法について説明します。');

-- 全文検索の実行 (基本的な検索)
SELECT * FROM documents WHERE documents MATCH 'FTS5';

-- AND演算子を使った検索
SELECT * FROM documents WHERE documents MATCH 'SQLite AND 検索';

-- フレーズ検索
SELECT * FROM documents WHERE documents MATCH '"データベース入門"';

-- プレフィックス検索
SELECT * FROM documents WHERE documents MATCH 'カスタマイズ*';

-- 関連度順にソート
SELECT * FROM documents WHERE documents MATCH 'FTS5' ORDER BY rank;

-- 日本語対応の考慮事項（例: trigramトークナイザーの使用）
-- CREATE VIRTUAL TABLE documents_ja USING fts5(title, body, tokenize='trigram');
-- ただし、より高度な日本語検索には、外部の形態素解析器を組み込んだカスタムトークナイザーが推奨されます。

組み込み機能: SQLiteの標準的な拡張機能として提供されているため、追加のサーバーや複雑な設定が不要です。
関連度ランキング: 検索結果を関連度の高い順に並べ替えることができ、ユーザーエクスペリエンスを向上させます。
柔軟なクエリ: AND/OR/NOT、フレーズ検索、近接検索、プレフィックス検索など、多様な検索要件に対応します。
高速な検索: 転置インデックスにより、大量のテキストデータから効率的に検索を実行できます。

FTS5モジュールが利用できない/ロードできないエラー

エラーメッセージの例

Error: cannot open shared library: libfts5.so (Linuxの場合)
Error: cannot open shared library: fts5.dll (Windowsの場合)
Error: no such module: fts5

原因

ロード可能な拡張機能としてFTS5を使用する場合、共有ライブラリ（fts5.dllやlibfts5.soなど）が適切に配置されていないか、SQLiteがそのライブラリを見つけられないパスに存在しない。
FTS5はSQLiteの標準的な機能ですが、コンパイル時にFTS5拡張機能が有効になっていない場合があります。特に、自分でSQLiteをコンパイルする場合や、配布されているSQLiteライブラリが最小限の機能しか含まれていない場合に発生しやすいです。

トラブルシューティング

パスの確認
共有ライブラリのパスが正しいか、実行環境からアクセス可能かを確認してください。システムパスに追加するか、フルパスで指定します。

拡張機能のロード
ロード可能な拡張機能としてFTS5を使用する場合は、SQLiteシェルやアプリケーションから明示的にロードする必要があります。 SQLiteシェルでの例：

.load ./fts5
-- または、フルパスで指定
.load C:/path/to/fts5.dll
.load /usr/local/lib/libfts5.so

アプリケーション（例: Pythonのsqlite3モジュール）での例：

import sqlite3

conn = sqlite3.connect(':memory:')
# 拡張機能のロードを許可
conn.enable_load_extension(True)
try:
    conn.load_extension('./fts5.dll') # または 'libfts5.so'
except sqlite3.OperationalError as e:
    print(f"拡張機能のロードに失敗しました: {e}")
conn.enable_load_extension(False) # セキュリティのため無効に戻す

FTS5が組み込まれているか確認
SQLiteコマンドラインツールで以下のコマンドを実行します。
```
PRAGMA compile_options;
```
出力に SQLITE_ENABLE_FTS5 が含まれていれば、FTS5は組み込まれています。含まれていない場合は、FTS5が有効なSQLiteを再コンパイルするか、FTS5が組み込まれたSQLiteバイナリを入手する必要があります。

原因

トラブルシューティング

クエリ構文の誤り

エラーメッセージの例

Error: near "AND": syntax error
Error: malformed MATCH expression

原因

二重引用符の閉じ忘れ、演算子の誤った使い方などが原因で発生します。
FTS5のMATCH句の構文は、通常のSQLのWHERE句とは異なります。特に、スペース、句読点、予約語などの扱いには注意が必要です。

トラブルシューティング

エスケープ処理
検索したい文字列にFTS5の特殊文字（例: -、"、*など）が含まれる場合は、適切にエスケープする必要があります。通常は二重引用符で囲むことでリテラルとして扱われます。
```
-- "C++" を検索したい場合
SELECT * FROM my_fts_table WHERE my_fts_table MATCH '"C++"';
```
公式ドキュメントの確認
FTS5のクエリ構文は、SQLiteの公式ドキュメントで詳細に説明されています。特に、フレーズ検索（"単語単語"）、プレフィックス検索（単語*）、近接検索（単語1 NEAR 単語2）、ブール演算子（AND, OR, NOT）の使い方を確認してください。

仮想テーブルに関するエラー

エラーメッセージの例

Error: CREATE VIRTUAL TABLE cannot have column constraints
Error: CREATE VIRTUAL TABLE cannot have a PRIMARY KEY constraint

原因

FTS5仮想テーブルは、通常のテーブルとは異なり、PRIMARY KEY、NOT NULL、DEFAULTなどの列制約や型の指定（TEXT, INTEGERなど）をCREATE VIRTUAL TABLE文で直接定義することはできません。FTS5仮想テーブルは、内部的にrowidを持つため、明示的なPRIMARY KEYは不要です。

トラブルシューティング

原因

非効率なクエリ
非常に一般的な単語（例: 英語の "the", "a"）を検索すると、インデックスが大きくなり、検索が遅くなる可能性があります。
大量の書き込み
FTS5は、データの挿入/更新/削除時にインデックスを再構築するため、大量の書き込みが発生するとパフォーマンスが低下することがあります。
インデックスの欠如
FTS5仮想テーブルを使用していない、またはFTS5仮想テーブルにデータが適切に挿入されていない。

ストップワード（Stopwords）の考慮
非常に頻繁に出現する単語は、インデックスの肥大化と検索性能の低下を招くことがあります。これらの単語を「ストップワード」として扱い、インデックスから除外することでパフォーマンスを改善できます。FTS5にはストップワードのリストをカスタムするオプションはありませんが、カスタムトークナイザーを使用すれば、この機能を実装できます。
大量書き込み時の最適化
- トランザクションの使用
  複数のレコードを挿入する場合は、単一のトランザクション内で実行することで、インデックス構築のオーバーヘッドを削減できます。
- optimizeコマンド
  FTS5は、インデックスを効率的に管理するために内部的にマージ処理を行いますが、手動で最適化を実行することも可能です。
```
INSERT INTO my_fts_table(my_fts_table) VALUES('optimize');
```

FTS5仮想テーブルの利用とデータ投入の確認
テキストデータがFTS5仮想テーブルに正しく挿入されていることを確認してください。通常、元のデータテーブルとFTS5仮想テーブルを同期させるためにトリガーを使用します。

-- 元のテーブル
CREATE TABLE articles (
    id INTEGER PRIMARY KEY,
    title TEXT,
    body TEXT
);

-- FTS5仮想テーブル
CREATE VIRTUAL TABLE articles_fts USING fts5(title, body, content='articles', content_rowid='id');

-- トリガーでFTS5テーブルを更新
CREATE TRIGGER articles_ai AFTER INSERT ON articles BEGIN
  INSERT INTO articles_fts(rowid, title, body) VALUES (new.id, new.title, new.body);
END;

CREATE TRIGGER articles_ad AFTER DELETE ON articles BEGIN
  INSERT INTO articles_fts(articles_fts, rowid, title, body) VALUES ('delete', old.id, old.title, old.body);
END;

CREATE TRIGGER articles_au AFTER UPDATE ON articles BEGIN
  INSERT INTO articles_fts(articles_fts, rowid, title, body) VALUES ('delete', old.id, old.title, old.body);
  INSERT INTO articles_fts(rowid, title, body) VALUES (new.id, new.title, new.body);
END;

外部コンテンツモード (External Content Mode)
FTS5は、実際のテキストデータを別のテーブルに格納し、FTS5仮想テーブルはインデックスとしてのみ機能させる「外部コンテンツモード」をサポートしています。これは、データ重複を避け、管理を容易にするために推奨される方法です。上記のトリガーの例はこのモードを利用しています。
部分一致と全文一致
FTS5はあくまで「単語」をベースにした全文検索です。SQLのLIKE '%部分文字列%'のような「任意の場所での部分一致」とは異なります。厳密な部分文字列検索が必要な場合は、trigramトークナイザーを検討するか、データベースレベルでの別の対策（例: LIKE演算子の併用）が必要です。
大文字・小文字の区別
FTS5はデフォルトで大文字・小文字を区別しません（Case-Insensitive）。

FTS5仮想テーブルの作成とデータ投入 (SQL & Python)

FTS5仮想テーブルは、元のデータとは別に全文検索用のインデックスとして機能します。ここでは、元の記事データとFTS5インデックスを同期させる一般的な方法を示します。

シナリオ
記事のタイトルと本文を検索したい

import sqlite3

# データベースに接続（メモリ上に作成）
# 永続化する場合は 'articles.db' などのファイル名を指定
conn = sqlite3.connect(':memory:')
cursor = conn.cursor()

# -----------------------------------------------------
# 1. 元のデータテーブル (articles) の作成
# -----------------------------------------------------
cursor.execute("""
CREATE TABLE articles (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    body TEXT
);
""")

# -----------------------------------------------------
# 2. FTS5仮想テーブル (articles_fts) の作成
#    - content='articles': articlesテーブルのデータをインデックス化することを示す
#    - content_rowid='id': articlesテーブルのどのカラムがrowidに対応するかを示す
#    - tokenize='unicode61': デフォルトのトークナイザーを使用（日本語には不向きな場合あり）
# -----------------------------------------------------
cursor.execute("""
CREATE VIRTUAL TABLE articles_fts USING fts5(
    title,
    body,
    content='articles',
    content_rowid='id'
);
""")

# -----------------------------------------------------
# 3. トリガーの作成 (データ同期のため)
#    - INSERT, UPDATE, DELETE 時に FTS5 テーブルも自動的に更新されるようにする
# -----------------------------------------------------

# INSERT トリガー
cursor.execute("""
CREATE TRIGGER articles_ai AFTER INSERT ON articles BEGIN
  INSERT INTO articles_fts(rowid, title, body) VALUES (new.id, new.title, new.body);
END;
""")

# DELETE トリガー
cursor.execute("""
CREATE TRIGGER articles_ad AFTER DELETE ON articles BEGIN
  INSERT INTO articles_fts(articles_fts, rowid, title, body) VALUES ('delete', old.id, old.title, old.body);
END;
""")

# UPDATE トリガー (DELETE と INSERT の組み合わせで実現)
cursor.execute("""
CREATE TRIGGER articles_au AFTER UPDATE ON articles BEGIN
  INSERT INTO articles_fts(articles_fts, rowid, title, body) VALUES ('delete', old.id, old.title, old.body);
  INSERT INTO articles_fts(rowid, title, body) VALUES (new.id, new.title, new.body);
END;
""")

# -----------------------------------------------------
# 4. データの挿入 (元のテーブルに挿入すればFTS5も更新される)
# -----------------------------------------------------
articles_data = [
    ("SQLite FTS5の概要", "SQLiteの全文検索機能であるFTS5は、高速なテキスト検索を可能にします。"),
    ("Pythonとデータベース", "Pythonのsqlite3モジュールを使って、SQLiteデータベースを操作する方法を学びます。"),
    ("日本語全文検索の課題", "日本語のようなスペースで単語が区切られない言語では、FTS5のデフォルト設定では検索精度が低くなることがあります。"),
    ("FTS5の高度な利用", "FTS5の関連度ランキングやカスタムトークナイザーについて解説します。")
]

cursor.executemany("INSERT INTO articles (title, body) VALUES (?, ?)", articles_data)
conn.commit()

print("FTS5テーブルとデータ投入の準備ができました。")

# 接続を閉じる
conn.close()

解説

トリガー
articlesテーブルへのINSERT, UPDATE, DELETE操作時に、自動的にarticles_ftsテーブルも同期するように設定します。
- INSERT INTO articles_fts(rowid, title, body) ...: 新しいレコードが挿入されたときに、FTS5インデックスに追加します。
- INSERT INTO articles_fts(articles_fts, rowid, title, body) VALUES ('delete', old.id, old.title, old.body);: articles_ftsテーブルの最初の引数に'delete'を指定することで、指定したrowid（old.id）のインデックスを削除します。UPDATEでは古いインデックスを削除し、新しいインデックスを追加します。
articles_fts仮想テーブル
CREATE VIRTUAL TABLE ... USING fts5(...) で作成します。
- title, body: FTS5がインデックス化するカラムを指定します。これらのカラム名は何でも構いませんが、元のテーブルのカラム名と合わせるのが一般的です。
- content='articles': FTS5がインデックスを構築する対象の元のテーブル名を指定します。
- content_rowid='id': articlesテーブルのどのカラムがFTS5の内部rowidに対応するかを指定します。これにより、FTS5検索結果から元のテーブルのレコードを効率的に参照できます。
articlesテーブル
実際の記事データを格納する通常のテーブルです。idはプライマリキー。

FTS5はMATCH演算子を使用して全文検索を行います。

import sqlite3

conn = sqlite3.connect(':memory:')
cursor = conn.cursor()

# 上記のFTS5テーブル作成とデータ投入のコードをここに含めるか、
# 永続化したDBファイルを開く場合は以下のようにします。
# conn = sqlite3.connect('articles.db')

# 簡略化のため、ここでは再度テーブルとデータを設定
# 実際のアプリケーションでは、DBファイルを開き、必要なテーブルが存在することを確認します
cursor.execute("""
CREATE TABLE articles (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    body TEXT
);
""")
cursor.execute("""
CREATE VIRTUAL TABLE articles_fts USING fts5(
    title,
    body,
    content='articles',
    content_rowid='id'
);
""")
cursor.execute("""
CREATE TRIGGER articles_ai AFTER INSERT ON articles BEGIN
  INSERT INTO articles_fts(rowid, title, body) VALUES (new.id, new.title, new.body);
END;
""")
articles_data = [
    ("SQLite FTS5の概要", "SQLiteの全文検索機能であるFTS5は、高速なテキスト検索を可能にします。"),
    ("Pythonとデータベース", "Pythonのsqlite3モジュールを使って、SQLiteデータベースを操作する方法を学びます。"),
    ("日本語全文検索の課題", "日本語のようなスペースで単語が区切られない言語では、FTS5のデフォルト設定では検索精度が低くなることがあります。"),
    ("FTS5の高度な利用", "FTS5の関連度ランキングやカスタムトークナイザーについて解説します。")
]
cursor.executemany("INSERT INTO articles (title, body) VALUES (?, ?)", articles_data)
conn.commit()


# -----------------------------------------------------
# 検索例1: 基本的な単語検索
# -----------------------------------------------------
search_term1 = "検索"
print(f"\n--- 検索語: '{search_term1}' ---")
cursor.execute(f"SELECT articles.id, articles.title FROM articles JOIN articles_fts ON articles.id = articles_fts.rowid WHERE articles_fts MATCH '{search_term1}'")
results = cursor.fetchall()
for row in results:
    print(f"ID: {row[0]}, Title: {row[1]}")

# -----------------------------------------------------
# 検索例2: AND 演算子を使った検索
# -----------------------------------------------------
search_term2 = "Python AND データベース"
print(f"\n--- 検索語: '{search_term2}' ---")
cursor.execute(f"SELECT articles.id, articles.title FROM articles JOIN articles_fts ON articles.id = articles_fts.rowid WHERE articles_fts MATCH '{search_term2}'")
results = cursor.fetchall()
for row in results:
    print(f"ID: {row[0]}, Title: {row[1]}")

# -----------------------------------------------------
# 検索例3: フレーズ検索 (厳密な並び順で検索)
# -----------------------------------------------------
search_term3 = '"全文検索機能"'
print(f"\n--- 検索語: '{search_term3}' ---")
cursor.execute(f"SELECT articles.id, articles.title FROM articles JOIN articles_fts ON articles.id = articles_fts.rowid WHERE articles_fts MATCH '{search_term3}'")
results = cursor.fetchall()
for row in results:
    print(f"ID: {row[0]}, Title: {row[1]}")

# -----------------------------------------------------
# 検索例4: プレフィックス検索 (単語の冒頭一致)
# -----------------------------------------------------
search_term4 = "日本語*" # "日本語"で始まる単語を検索
print(f"\n--- 検索語: '{search_term4}' ---")
cursor.execute(f"SELECT articles.id, articles.title FROM articles JOIN articles_fts ON articles.id = articles_fts.rowid WHERE articles_fts MATCH '{search_term4}'")
results = cursor.fetchall()
for row in results:
    print(f"ID: {row[0]}, Title: {row[1]}")

# -----------------------------------------------------
# 検索例5: 関連度ランキング (rank) との組み合わせ
# -----------------------------------------------------
search_term5 = "FTS5"
print(f"\n--- 検索語: '{search_term5}' (関連度順) ---")
# articles_fts.rank は FTS5仮想テーブルが提供する隠しカラム
cursor.execute(f"SELECT articles.id, articles.title, articles_fts.rank FROM articles JOIN articles_fts ON articles.id = articles_fts.rowid WHERE articles_fts MATCH '{search_term5}' ORDER BY articles_fts.rank")
results = cursor.fetchall()
for row in results:
    print(f"ID: {row[0]}, Title: {row[1]}, Rank: {row[2]}")

conn.close()

解説

ORDER BY articles_fts.rank: FTS5は、検索結果の関連度を計算し、rankという擬似カラムで提供します。このカラムでソートすることで、関連性の高い順に結果を表示できます。rankの値が小さいほど関連度が高いことを示します。
WHERE articles_fts MATCH '検索語': これがFTS5の全文検索の核となる部分です。MATCH演算子と検索したい文字列を指定します。
- '検索語': 単語検索
- '単語1 AND 単語2': 両方の単語を含むレコード
- '"フレーズ検索"': 厳密なフレーズ一致
- 'プレフィックス*' : プレフィックス一致
SELECT articles.id, articles.title FROM articles JOIN articles_fts ON articles.id = articles_fts.rowid: FTS5はインデックスのみを提供するため、検索結果のrowidを使って元のarticlesテーブルとJOINし、必要なカラム（titleなど）を取得します。

import sqlite3

conn = sqlite3.connect(':memory:')
cursor = conn.cursor()

# -----------------------------------------------------
# FTS5仮想テーブル (trigram_articles_fts) の作成 (tokenize='trigram' を指定)
# -----------------------------------------------------
cursor.execute("""
CREATE VIRTUAL TABLE trigram_articles_fts USING fts5(
    title,
    body,
    content='articles',
    content_rowid='id',
    tokenize='trigram' -- ここで trigram トークナイザーを指定
);
""")

# 既存のarticlesテーブルとトリガーは省略（上記コードと同じ）
cursor.execute("""
CREATE TABLE articles (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    body TEXT
);
""")
cursor.execute("""
CREATE TRIGGER articles_ai AFTER INSERT ON articles BEGIN
  INSERT INTO trigram_articles_fts(rowid, title, body) VALUES (new.id, new.title, new.body);
END;
""")

articles_data = [
    ("日本語の全文検索", "FTS5で日本語を正確に検索するには、特別な考慮が必要です。"),
    ("形態素解析の重要性", "形態素解析は、日本語の単語を適切に区切るために不可欠です。"),
    ("PyMeCabの利用", "PythonでMeCabを利用して、形態素解析を行うことができます。")
]
cursor.executemany("INSERT INTO articles (title, body) VALUES (?, ?)", articles_data)
conn.commit()

# -----------------------------------------------------
# trigram トークナイザーでの検索例
# -----------------------------------------------------
search_term_ja1 = "日本語" # 単語の一部でもヒットしやすい
print(f"\n--- trigram検索語: '{search_term_ja1}' ---")
cursor.execute(f"SELECT articles.id, articles.title FROM articles JOIN trigram_articles_fts ON articles.id = trigram_articles_fts.rowid WHERE trigram_articles_fts MATCH '{search_term_ja1}'")
results = cursor.fetchall()
for row in results:
    print(f"ID: {row[0]}, Title: {row[1]}")

search_term_ja2 = "解析" # 「形態素解析」の中の「解析」でヒット
print(f"\n--- trigram検索語: '{search_term_ja2}' ---")
cursor.execute(f"SELECT articles.id, articles.title FROM articles JOIN trigram_articles_fts ON articles.id = trigram_articles_fts.rowid WHERE trigram_articles_fts MATCH '{search_term_ja2}'")
results = cursor.fetchall()
for row in results:
    print(f"ID: {row[0]}, Title: {row[1]}")

conn.close()

trigramは、テキストを3文字のN-gramに分割するため、部分一致の網羅性は高まりますが、正確な単語の区切りを認識しているわけではありません。

LIKE演算子とGLOB演算子

最もシンプルで基本的な検索方法です。

GLOB
シェルのパターンマッチングに似た機能 (* や ?) を使った検索。大文字・小文字を区別します。
LIKE
ワイルドカード (% や _) を使ったパターンマッチング検索。大文字・小文字を区別しない検索が可能です（データベースの照合順序による）。

メリット

非常にシンプルで実装が容易。
FTS5のような特別な設定や仮想テーブルは不要。

デメリット

機能の制限
関連度ランキング、近接検索、複雑なブール演算子（AND/OR/NOT）は利用できません。
パフォーマンス
大量のテキストデータや、文字列の途中にワイルドカードを使用する場合（例: '%検索語%'）、インデックスがほとんど効かず、テーブル全体をスキャンするため非常に遅くなります。

コード例 (Python)

import sqlite3

conn = sqlite3.connect(':memory:')
cursor = conn.cursor()

cursor.execute("""
CREATE TABLE documents (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    content TEXT
);
""")

documents_data = [
    ("これは全文検索のテストです。",),
    ("SQLiteは軽量なデータベースです。",),
    ("PythonとSQLiteの連携について。",),
    ("全文検索とインデックスについて学びます。",)
]
cursor.executemany("INSERT INTO documents (content) VALUES (?)", documents_data)
conn.commit()

# LIKE 検索
print("--- LIKE 検索 ---")
search_term_like = '%全文検索%'
cursor.execute("SELECT * FROM documents WHERE content LIKE ?", (search_term_like,))
results = cursor.fetchall()
for row in results:
    print(row)

# GLOB 検索 (大文字・小文字を区別)
print("\n--- GLOB 検索 ---")
search_term_glob = '*Python*'
cursor.execute("SELECT * FROM documents WHERE content GLOB ?", (search_term_glob,))
results = cursor.fetchall()
for row in results:
    print(row)

conn.close()

アプリケーション層での全文検索

データベースに全文検索機能を任せず、アプリケーション側でテキストデータを処理し、検索ロジックを実装する方法です。

方法

メモリ上での検索
比較的小規模なデータセットであれば、データベースから全データを読み込み、Pythonなどの言語で文字列操作（in, find, 正規表現など）を使って検索します。

メリット

スケーラビリティ (外部エンジン)
Elasticsearchのような外部エンジンは、大量のデータや高負荷な検索にも対応できます。

デメリット

同期の問題
データベースのデータと全文検索エンジンのインデックスを常に最新に保つための同期ロジックが必要です。
リソース消費
メモリ上で全データを扱う場合、メモリ消費が大きくなる可能性があります。外部エンジンは、別途サーバーリソースが必要になります。
実装の複雑さ
全文検索エンジンやライブラリの選定、設定、インデックスの構築、同期、クエリの構築など、FTS5よりも多くの実装作業と学習コストがかかります。

コード例 (Python - Whoosh)

pip install Whoosh でインストールします。

import os
import shutil
from whoosh.index import create_in, open_dir
from whoosh.fields import Schema, TEXT, ID
from whoosh.qparser import QueryParser

# インデックスの保存ディレクトリ
INDEX_DIR = "whoosh_index"

# 既存のインデックスディレクトリがあれば削除
if os.path.exists(INDEX_DIR):
    shutil.rmtree(INDEX_DIR)

# スキーマの定義 (インデックス化するフィールド)
schema = Schema(id=ID(stored=True), title=TEXT(stored=True), body=TEXT(stored=True))

# インデックスの作成
ix = create_in(INDEX_DIR, schema)
writer = ix.writer()

# データの追加
articles_data = [
    {"id": "1", "title": "SQLite FTS5の概要", "body": "SQLiteの全文検索機能であるFTS5は、高速なテキスト検索を可能にします。"},
    {"id": "2", "title": "Pythonとデータベース", "body": "Pythonのsqlite3モジュールを使って、SQLiteデータベースを操作する方法を学びます。"},
    {"id": "3", "title": "日本語全文検索の課題", "body": "日本語のようなスペースで単語が区切られない言語では、FTS5のデフォルト設定では検索精度が低くなることがあります。"},
    {"id": "4", "title": "FTS5の高度な利用", "body": "FTS5の関連度ランキングやカスタムトークナイザーについて解説します。"}
]

for doc in articles_data:
    writer.add_document(**doc)
writer.commit()

# インデックスを開いて検索
ix = open_dir(INDEX_DIR)
searcher = ix.searcher()

# 検索クエリの作成
parser = QueryParser("body", ix.schema) # bodyフィールドをデフォルトの検索対象とする

# -----------------------------------------------------
# 検索例1: 単語検索
# -----------------------------------------------------
query1 = parser.parse("全文検索")
results1 = searcher.search(query1)
print(f"\n--- Whoosh 検索語: '全文検索' ---")
for hit in results1:
    print(f"ID: {hit['id']}, Title: {hit['title']}")

# -----------------------------------------------------
# 検索例2: AND 検索
# -----------------------------------------------------
query2 = parser.parse("Python AND データベース")
results2 = searcher.search(query2)
print(f"\n--- Whoosh 検索語: 'Python AND データベース' ---")
for hit in results2:
    print(f"ID: {hit['id']}, Title: {hit['title']}")

# -----------------------------------------------------
# 検索例3: 特定のフィールドを指定した検索
# -----------------------------------------------------
query3 = parser.parse("title:FTS5") # titleフィールドでFTS5を検索
results3 = searcher.search(query3)
print(f"\n--- Whoosh 検索語: 'title:FTS5' ---")
for hit in results3:
    print(f"ID: {hit['id']}, Title: {hit['title']}")

searcher.close()

FTS5のtrigramトークナイザーのように、テキストを固定長のN-gram（文字の連続）に分割し、それらを通常のSQLiteテーブルにインデックスとして保存し、JOINやLIKEで検索する方法です。FTS5の機能が限定的な場合や、より細かい制御が必要な場合に検討されます。

メリット

部分文字列検索に比較的強い。
N-gramの長さを自由に調整できる（バイグラム、トライグラムなど）。
FTS5に依存しないため、任意のSQLite環境で動作する。

デメリット

パフォーマンス
複雑なクエリや大量のデータでは、FTS5ほど高速ではない可能性があります。特に、JOIN操作がボトルネックになることがあります。
インデックスサイズ
N-gramインデックスは非常に大きくなる傾向があります。
実装の手間
N-gramの生成、インデックステーブルへの挿入、検索クエリの構築など、すべて自前で実装する必要があります。

import sqlite3

def generate_ngrams(text, n):
    """テキストからN-gramを生成するジェネレータ"""
    text = text.replace(' ', '') # スペースは無視する
    for i in range(len(text) - n + 1):
        yield text[i:i+n]

conn = sqlite3.connect(':memory:')
cursor = conn.cursor()

# -----------------------------------------------------
# 1. 元のデータテーブル (documents) の作成
# -----------------------------------------------------
cursor.execute("""
CREATE TABLE documents (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    content TEXT
);
""")

# -----------------------------------------------------
# 2. N-gramインデックステーブルの作成
#    - gram: N-gram文字列
#    - doc_id: 元のドキュメントのID
# -----------------------------------------------------
cursor.execute("""
CREATE TABLE ngrams (
    gram TEXT NOT NULL,
    doc_id INTEGER NOT NULL,
    PRIMARY KEY (gram, doc_id), -- 複合プライマリキーで高速化
    FOREIGN KEY (doc_id) REFERENCES documents(id)
);
""")

# -----------------------------------------------------
# 3. データの挿入とN-gramインデックスの生成
# -----------------------------------------------------
documents_data = [
    ("これは日本語の全文検索のテストです。",),
    ("形態素解析の重要性について。",),
    ("SQLiteデータベースの利用。",)
]

# N-gramの長さ (例: 2-gram)
N_GRAM_LENGTH = 2

for doc_content in documents_data:
    content = doc_content[0]
    cursor.execute("INSERT INTO documents (content) VALUES (?)", (content,))
    doc_id = cursor.lastrowid

    # N-gramを生成してインデックステーブルに挿入
    for gram in generate_ngrams(content, N_GRAM_LENGTH):
        try:
            cursor.execute("INSERT INTO ngrams (gram, doc_id) VALUES (?, ?)", (gram, doc_id))
        except sqlite3.IntegrityError:
            # 既に同じgramとdoc_idの組み合わせがある場合はスキップ (PRIMARY KEY制約)
            pass
conn.commit()

# -----------------------------------------------------
# 4. N-gramインデックスを使った検索
# -----------------------------------------------------
def search_by_ngram(search_term, n_gram_len):
    grams_to_search = list(generate_ngrams(search_term, n_gram_len))
    if not grams_to_search:
        return []

    # 複数のN-gramすべてを含むドキュメントを検索
    # サブクエリで各N-gramを含むdoc_idを取得し、グループ化してカウントする
    # COUNT(*) が検索語のN-gram数と一致すれば、すべてのN-gramが含まれている
    placeholders = ','.join('?' * len(grams_to_search))
    query = f"""
    SELECT
        d.id,
        d.content
    FROM
        documents d
    JOIN
        ngrams n ON d.id = n.doc_id
    WHERE
        n.gram IN ({placeholders})
    GROUP BY
        d.id
    HAVING
        COUNT(DISTINCT n.gram) = ?
    """
    
    # 検索語のN-gramリストと、そのN-gramの数をパラメータとして渡す
    params = grams_to_search + [len(grams_to_search)]
    
    cursor.execute(query, params)
    return cursor.fetchall()

print("\n--- N-gram 検索 ---")
search_term_ngram = "日本語" # 例：「日本」「本語」の2-gramが生成される
results_ngram = search_by_ngram(search_term_ngram, N_GRAM_LENGTH)
for row in results_ngram:
    print(f"ID: {row[0]}, Content: {row[1]}")

search_term_ngram_2 = "解析" # 例：「解析」
results_ngram_2 = search_by_ngram(search_term_ngram_2, N_GRAM_LENGTH)
for row in results_ngram_2:
    print(f"ID: {row[0]}, Content: {row[1]}")

conn.close()

FTS5を使わず、より細かい制御で部分文字列検索が必要
N-gramインデックスの自作
大規模なデータ、分散環境、高い負荷
Elasticsearch / Solr
簡単な部分一致
LIKE / GLOB (%検索語%など)