SQLite３の全文検索(FTS:Full Text Search) 機能を使ったプログラムを作成してみて、使い方や注意点などがある程度わかったので入門用の記事として紹介します
SQLiteは、パブリックドメインで提供されてる SQL92準拠の豊富な機能を持った中小規模用のデータベースです。データベースサーバーが不要で、１つのデータベースを１つのファイルとして作成するので、手軽に扱うことが出来ます。
SQLiteは今も進化しているのでもっと最新の情報が紹介されていてもいいと思うのですが、残念ながら日本語でのSQLiteに関する最近のまとまった情報はあまりないようです
特にFTSに関する情報は殆どありません。
SQLiteの情報は本家サイト（英語） https://www.sqlite.org/docs.html が充実しています
FTSに関しても公式サイトの情報 https://www.sqlite.org/fts3.html が唯一のまとまった情報源ですがなかなか難しくて詳細までは理解出来ていない所も多いです ^^;
SQLiteの日本語情報が増えるように、この記事が少しでもお役に立てば良いのですが (^^)
デモサイト
WordPress のプラグイン検索ページを以前 MySQL を使って構築していたのですが、検索条件 AND, OR, NOT 等を使って検索結果を絞り込めるようにしたいと思いデータベースを SQLite3 に置き換えてFTS を使って構築しなおしました
プラグイン名、タグ、作者名は、英語ベースで既に単語に分割されているデータなので、FTSの利用形態としては、全文検索と言うよりは便利で柔軟な複合インデックス的な感じの利用です
WordPressプラグインアンテナ – Yet Another Plugin Directory
参考となるように人気のプラグインを紹介いたします。 日本製プラグインも応援してます (^^)
セルティスラボちょっと試して頂ければ分かりますが、SQLiteのFTSは、十分高速で実用的です
（これで、プラグインデータが23000件以上、作者データ12000件以上登録されています）
Web系でよく使われている全文検索(FTS) は、Mroonga という MySQL/MariaDB の全文検索用拡張機能があるようですが、なんとなくハードルが高い気がしてもっと小規模でいろんな環境で手軽に扱え、将来的にもいろいろ応用が効くと思い Sqlite3のFTSを使ってみました (^^)
各データベース毎に得手不得手がありますが、SQLiteは、今回の様なデータが１０万件程度でINSERT/UPDATE頻度がそれほど高くなく、 AND, OR, NOT を使った複合条件による検索を高速に行いたい場合に とても向いているし、手軽に扱えるのでお勧めです (^^)
※日本語全文検索として様々な文章から検索する場合（例えばTwitter データ等）の解析を行う場合には、日本語形態素解析 (MeCab等)が必要となってきます
SQLite3 (PDO)入門
準備
PHP で SQLite の FTS を使うための準備を行います
サーバー上で動作させるには、多くの場合 SQLite FTS 機能が有効になっていませんので、FTSを使うにはちょっと作業が必要となります
サーバー上での動作試す場合には Vagrant等の仮想環境を、パソコン上で SQL 動作を試すだけなら SqliteBrowser や SQLiteSpy 等のツールを用意してください。
ローカルサーバー
Vagrantを使ってもPHPのSQLiteでFTSが有効化されていない場合が多いと思いますが、ここでは、以前紹介した Vagrant VAW をお勧めしておきます
Vagrant VAW で SQLiteのFTSを有効にする手順を紹介しています
sqlite3, pdo_sqlite モジュールの全文検索（FTS）対応コンパイル手順
セルティスラボ※他には、以前使用していた xampp 3.1.0 では FTS 機能が有効になっていました（最新の xampp がどうなっているかわかりません）
公開サーバー
残念ながら多くのレンタルサーバーでは、pdo_sqlite モジュールは FTS が有効化されていないことが多いです
サーバー毎に状況は異なると思いますが、私の使用しているエックスサーバーでも FTS は有効化されていませんでしたので、エックスサーバーでFTSを使う方法を紹介しておきます
エックスサーバーのPHPモジュール sqlite3, pdo_sqlite を全文検索（FTS）対応に置き換えてみる
セルティスラボサーバー以外で動作させたい場合
この記事は、サーバー上でPDO使ったFTSの入門記事ですが、SQL文に関してはPDOを使わない他の環境での動作でも同じですので、Windowsなら SqliteBrowser や SQLiteSpy 等のツールを使ってもほとんどの SQL の動作を試すことは可能です（FTS対応のツールが必要）。
SQL文の動作をちょっと試してみたいという場合には、SQLiteSpy を使えば、SQL文の実行と確認表示を簡単に行うことが出来ます。データも高速で見やすく表示してくれます
以降、ローカル環境でSQL文の動作確認は SQLiteSpy 使って行いました
SQLiteデータベースの特徴
SQLiteは、データベースファイルを操作するためのライブラリとして提供されていて、様々な環境やプログラム言語からも使用出来るので組み込みシステムやブラウザ、メールクライアント等でも利用されています
PHPでSQLiteを使うには、sqlite3 と pdo_sqlite の拡張モジュールがあり、どちらのモジュールを使用しても SQLite3 の FTS を扱うことは可能です。
違いは、sqlite3 モジュールは、データベースを操作するための記述が、sqlite3 固有の関数を直接使用するのに対して、pdo_sqlite モジュールでは、PHPからデータベースを扱う共通のライブラリ PDO を使用することです
主なSQL操作について紹介する前に大きな特徴であるデータ型について先に紹介します
SQLite を使っている方にはいまさらな情報かも知れませんが、MySQL等のデータベースを使っていた方は、これから紹介するデータ型について一読されることをお薦めします
SQLite のデータ型
SQLiteは、型がないなどと言われていますが、MySQL等のリレーショナルデータベースのようなカラムに対して厳密に型付けるわけではなく動的な型として扱われます
データベースに保存されるデータタイプは以下の５種類となります
| NULL | NULL値 |
| INTEGER | 符号付き整数値（値の大きさに応じて1、2、3、4、6、8バイト単位） |
| REAL | 浮動小数点値（浮動小数点数8バイトIEEE） |
| TEXT | テキスト文字列（UTF-8/UTF-16BE/UTF-16LE） |
| BLOB | Binary Large Object 任意の形式のバイナリデータ |
これだけじゃデータ型足りないんじゃないかと思うかも知れませんが、真偽値や、日付時刻等も、特定のルール（フォーマット）を守れば、問題なく扱えるようになっています
ブール型（真偽値）
ブール値は整数0（偽）と1（真）として保存されます
日付時刻型
SQLiteのサポートしている日付と時刻の関数によりTEXT、REAL、またはINTEGER値として日付と時刻を保存できます
• TEXT ISO8601 (“YYYY-MM-DD HH:MM:SS.SSS”) 等の数パターンのフォーマット文字列
• REALユリウス日番号など、先発グレゴリオ暦によると11月24日にグリニッジの正午、4714 BCからの日数
• INTEGER Unixの時間、1970-01-01 00:00:00からの秒数
これらのいずれかの形式で日付と時刻を保存し、使用することが出来ます
値のデータ型とカラムのデータ型
先に保存するデータ自体の値のデータ型を紹介しましたが、データを格納する器側であるカラム（フィールド）の型について紹介します
MySQL等のリレーショナル・データベースは、通常保存する値とそのカラムのデータ型が一致していないと保存できないように厳密に型付けされています。
一方、SQLiteでは値とカラムのデータ型は厳格な型付をされているわけでなくデータ型は動的に決めています。カラムのデータ型は必須条件でなく（未指定も可）推奨という意味合いで、任意のタイプのデータが保存できるゆるくて疎な型付けとなっています
どういうことかと言うと、実際にデータを保存するときに挿入されたデータをカラムに対して指定されているデータ型にキャストして保存するのですが、キャスト出来なかった場合はそのまま保存する様になっています
SQLiteのカラムには、以下の５種類のデータ型を割り当てることができます
| TEXT | テキスト文字列（UTF-8/UTF-16BE/UTF-16LE）
|
| NUMERIC | 整数/浮動小数値 ※NULL,BLOBはそのまま保存
|
| INTEGER | 符号付き整数値 ※NULL,BLOBはそのまま保存
|
| REAL | 浮動小数点値 ※NULL,BLOBはそのまま保存
|
| BLOB | Binary Large Object 任意の形式のバイナリデータ
|
このよう仕様でデータが保存されるので、データを保存する側がきちんとデータ型を意識しておかないと意図していない間違ったデータ型で保存されることがあります
例えば、INTEGER型のカラムに対してINTEGER（数値）やTEXT（文字列）の異なるデータ型が混在することがありえるので、間違って保存されると正しくソート出来なかったり、正しい値を取得出来なかったりすることがあるのでデータ型を混在させないように気をつける必要もあります
他データベースとの互換性
このデータ値とカラムのゆるい関係は、SQLiteと他のデータベースエンジン間の互換性の為の機能（型親和性）に一役買っていて、MySQL等の他のデータベースから移行する場合にそこで使っていたデータ型は、自動的に下記ルールで変換して処理するので改めてSQLiteのデータ型を宣言しなくともそのまま使えるようになっています
| 優先度 | 他データベースで使われている定義 | 条件 | カラムデータ型 |
| 1 | INT INTEGER TINYINT SMALLINT MEDIUMINT BIGINT UNSIGNED BIG INT INT2 INT8 |
宣言された型に “INT”文字列が含まれている場合 | INTEGER |
| 2 | CHARACTER(20) VARCHAR(255) VARYINGCHARACTER(255) NCHAR(55) NATIVE CHARACTER(70) NVARCHAR(100) TEXT CLOB |
宣言された型に “CHAR”、”CLOB”、”TEXT” のいずれかの文字列が含まれている場合 | TEXT |
| 3 | BLOB データ型指定なし |
宣言された型に “BLOB”文字列が含まれているか、型宣言がされていない場合 | BLOB |
| 4 | REAL DOUBLE DOUBLE PRECISION FLOAT |
宣言された型に “REAL”、”FLOA”、”DOUB” のいずれかの文字列が含まれている場合 | REAL |
| 5 | NUMERIC DECIMAL(10,5) BOOLEAN DATE DATETIME |
上記１－４以外の場合 | NUMERIC |
参照 https://www.sqlite.org/datatype3.html
SQLiteにおけるデータ型の扱いをわかったうえで、後は目的毎に、データ型をきちっと意識したデータ処理が必要なのか、全てがテキストとしてして処理出来れば良いのかなど柔軟に対応できるようになっています
ちなみに FTS データを扱うには、専用のFTSテーブルを作成する必要があり、カラムのデータ型は、基本的にテキスト型のみに制限されます
この辺りの特徴をわかっていてデータ型を指定しないのと、わからずにデータ型を指定しないのでは大きな違いがありますので、ここはきちっと押さえておきましょう
次のページに続きます (^^)
