SQLite 全文検索（FTS5）入門：高速なインデックス作成と検索

はじめに

この実験（Lab）では、SQLite の FTS5 拡張機能を使用して、効率的な全文検索を行う方法を学びます。FTS5 テーブルを作成し、データを挿入し、検索を実行し、MATCH 句を使用してクエリを最適化します。この実験（Lab）は、SQLite における全文インデックス作成の実践的な入門となります。

Skills Graph

%%%%{init: {'theme':'neutral'}}%%%% flowchart RL sqlite(("SQLite")) -.-> sqlite/SQLiteGroup(["SQLite"]) sqlite/SQLiteGroup -.-> sqlite/make_table("Create New Table") sqlite/SQLiteGroup -.-> sqlite/add_rows("Insert Multiple Rows") sqlite/SQLiteGroup -.-> sqlite/get_all("Select All Rows") sqlite/SQLiteGroup -.-> sqlite/query_where("Filter With WHERE") sqlite/SQLiteGroup -.-> sqlite/verify_table("Check Table Existence") sqlite/SQLiteGroup -.-> sqlite/end_db("Close Database Connection") subgraph Lab Skills sqlite/make_table -.-> lab-552551{{"SQLite 全文インデックス"}} sqlite/add_rows -.-> lab-552551{{"SQLite 全文インデックス"}} sqlite/get_all -.-> lab-552551{{"SQLite 全文インデックス"}} sqlite/query_where -.-> lab-552551{{"SQLite 全文インデックス"}} sqlite/verify_table -.-> lab-552551{{"SQLite 全文インデックス"}} sqlite/end_db -.-> lab-552551{{"SQLite 全文インデックス"}} end

SQLite データベースの作成と FTS5 の有効化

このステップでは、SQLite データベースを作成し、FTS5 拡張機能を有効にします。FTS5 は、全文検索機能を提供するモジュールです。

まず、LabEx VM でターミナルを開きます。デフォルトのパスは /home/labex/project です。

SQLite データベースの作成：

books.db という名前のデータベースファイルを作成し、sqlite3 コマンドを使用して接続します。
```
sqlite3 books.db
```
このコマンドは SQLite シェルを起動し、SQL コマンドを実行できます。
FTS5 拡張機能の有効化：

FTS5 を使用する前に、有効にする必要があります。次の SQL コマンドを実行します。
```
CREATE VIRTUAL TABLE book_search USING fts5();
```
Error: no such module: fts5 のようなエラーが表示された場合、FTS5 拡張機能が利用できないことを意味します。最新の SQLite バージョンでは、FTS5 はデフォルトで含まれています。このエラーが発生した場合は、SQLite のバージョンが最新であることを確認してください。エラーなくコマンドが実行された場合、FTS5 拡張機能は有効になっています。

このコマンドは、fts5 モジュールを使用して book_search という名前の仮想テーブルを作成します。仮想テーブルは SQLite の機能であり、カスタム機能でデータベースを拡張できます。
テーブルスキーマの定義：

次に、book_search テーブルの列を定義しましょう。title（タイトル）、author（著者）、および content（コンテンツ）の列を含めます。次の SQL コマンドを実行します。
```
CREATE VIRTUAL TABLE book_search USING fts5(title, author, content);
```
このコマンドは、指定された列を持つ book_search テーブルを作成します。title、author、および content 列は FTS5 によってインデックスが作成され、それらに対して全文検索を実行できます。
トークナイザーの設定 (オプション):

トークナイザー（tokenizer）を指定することで、FTS5 がテキストをトークン化する方法をカスタマイズできます。unicode61 トークナイザーは、優れた Unicode サポートを提供します。これを使用し、ダイアクリティカルマーク（発音区別符号）を削除するには、次のコマンドでテーブルを再作成します。
```
DROP TABLE IF EXISTS book_search;
CREATE VIRTUAL TABLE book_search USING fts5(title, author, content, tokenize="unicode61 remove_diacritics 1");
```
このコマンドは、最初に既存の book_search テーブルを削除し（存在する場合）、次に unicode61 トークナイザーを使用して再作成します。remove_diacritics 1 オプションは、インデックス作成中にテキストからダイアクリティカルマークを削除するように FTS5 に指示します。

FTS5 テーブルへのデータの挿入

このステップでは、book_search テーブルにデータを挿入します。このデータは、後のステップで全文検索に使用されます。

書籍データの挿入：

次の SQL コマンドを実行して、サンプル書籍データを book_search テーブルに挿入します。

INSERT INTO book_search (title, author, content) VALUES (
    'The Lord of the Rings',
    'J.R.R. Tolkien',
    'A fantasy epic about hobbits, elves, and the battle against Sauron.'
);

INSERT INTO book_search (title, author, content) VALUES (
    'Pride and Prejudice',
    'Jane Austen',
    'A classic novel about love, class, and society in 19th-century England.'
);

INSERT INTO book_search (title, author, content) VALUES (
    'The Hitchhiker''s Guide to the Galaxy',
    'Douglas Adams',
    'A comedic science fiction series following the misadventures of Arthur Dent.'
);

INSERT INTO book_search (title, author, content) VALUES (
    'To Kill a Mockingbird',
    'Harper Lee',
    'A powerful story about racial injustice in the American South.'
);

INSERT INTO book_search (title, author, content) VALUES (
    '1984',
    'George Orwell',
    'A dystopian novel about totalitarianism and surveillance.'
);

これらのコマンドは、book_search テーブルに 5 つの行を挿入し、それぞれが書籍を表します。各書籍に対して、title（タイトル）、author（著者）、および content（コンテンツ）の値が提供されます。'The Hitchhiker''s Guide to the Galaxy' のエスケープされたシングルクォートに注意してください。文字列内のシングルクォートは、2 倍にすることでエスケープする必要があります。

データ挿入の確認：

データが正しく追加されたことを確認するには、次のコマンドを実行します。

SELECT * FROM book_search;

このコマンドは、book_search テーブルからすべての行と列を取得します。挿入したばかりのデータが表示されるはずです。

期待される出力：

The Lord of the Rings|J.R.R. Tolkien|A fantasy epic about hobbits, elves, and the battle against Sauron.
Pride and Prejudice|Jane Austen|A classic novel about love, class, and society in 19th-century England.
The Hitchhiker's Guide to the Galaxy|Douglas Adams|A comedic science fiction series following the misadventures of Arthur Dent.
To Kill a Mockingbird|Harper Lee|A powerful story about racial injustice in the American South.
1984|George Orwell|A dystopian novel about totalitarianism and surveillance.

基本的な全文検索の実行

このステップでは、MATCH 演算子を使用して基本的な全文検索を実行します。

単一の用語の検索：

"fantasy" という単語を含む書籍を検索するには、次の SQL コマンドを実行します。
```
SELECT * FROM book_search WHERE book_search MATCH 'fantasy';
```
このコマンドは、book_search テーブルのすべての列から "fantasy" という用語を検索します。MATCH 演算子は、全文検索を実行するために使用されます。

期待される出力：
```
The Lord of the Rings|J.R.R. Tolkien|A fantasy epic about hobbits, elves, and the battle against Sauron.
```
複数の用語の検索：

スペースで区切ることで、複数の用語を検索できます。"love" と "society" の両方を含む書籍を検索するには、次の SQL コマンドを実行します。
```
SELECT * FROM book_search WHERE book_search MATCH 'love society';
```
このコマンドは、インデックスが作成された列のいずれかに "love" と "society" の両方を含む行を検索します。

期待される出力：
```
Pride and Prejudice|Jane Austen|A classic novel about love, class, and society in 19th-century England.
```
特定の列内の検索：

特定の列内を検索するには、MATCH 演算子の前に列名を指定します。たとえば、author 列に "Orwell" が含まれる書籍を検索するには、次の SQL コマンドを実行します。
```
SELECT * FROM book_search WHERE author MATCH 'Orwell';
```
このコマンドは、author 列のみから "Orwell" という用語を検索します。

期待される出力：
```
1984|George Orwell|A dystopian novel about totalitarianism and surveillance.
```

高度な MATCH 機能の使用

このステップでは、プレフィックス検索やブール演算子など、MATCH 演算子の高度な機能を探索します。

プレフィックス検索 (Prefix Search):

* ワイルドカードを使用して、プレフィックス検索を実行します。"soci" で始まる単語を含む書籍を検索するには、次の SQL コマンドを実行します。
```
SELECT * FROM book_search WHERE book_search MATCH 'soci*';
```
このコマンドは、"society" のように "soci" で始まる用語を検索します。

期待される出力：
```
Pride and Prejudice|Jane Austen|A classic novel about love, class, and society in 19th-century England.
```

ブール演算子 (Boolean Operators):

FTS5 は、AND、OR、NOT などのブール演算子をサポートしており、より複雑な検索クエリを作成できます。

AND: 両方の用語を含むドキュメントを検索します。用語がスペースで区切られている場合のデフォルトの動作は AND です。
```
SELECT * FROM book_search WHERE book_search MATCH 'love AND society';
```
これは SELECT * FROM book_search WHERE book_search MATCH 'love society'; と同等です。

期待される出力：
```
Pride and Prejudice|Jane Austen|A classic novel about love, class, and society in 19th-century England.
```

OR: いずれかの用語を含むドキュメントを検索します。

SELECT * FROM book_search WHERE book_search MATCH 'love OR injustice';

このコマンドは、"love" または "injustice" (あるいはその両方) を含む書籍を検索します。

期待される出力：

Pride and Prejudice|Jane Austen|A classic novel about love, class, and society in 19th-century England.
To Kill a Mockingbird|Harper Lee|A powerful story about racial injustice in the American South.

NOT: 特定の用語を含むドキュメントを除外します。

SELECT * FROM book_search WHERE book_search MATCH 'NOT fantasy';

このコマンドは、"fantasy" という単語を含まない書籍を検索します。

期待される出力：

Pride and Prejudice|Jane Austen|A classic novel about love, class, and society in 19th-century England.
The Hitchhiker's Guide to the Galaxy|Douglas Adams|A comedic science fiction series following the misadventures of Arthur Dent.
To Kill a Mockingbird|Harper Lee|A powerful story about racial injustice in the American South.
1984|George Orwell|A dystopian novel about totalitarianism and surveillance.

まとめ

この実験（Lab）では、SQLite の FTS5 拡張機能を使用して全文検索を行う方法を学びました。FTS5 テーブルを作成し、データを挿入し、MATCH 演算子を使用して基本的な検索を実行し、プレフィックス検索やブール演算子などの高度な機能を探索しました。これらのスキルは、SQLite アプリケーションに強力な検索機能を構築するための基盤となります。