SQLite3 (PDO) を使った全文検索 (FTS) 入門

SQLite3の全文検索(FTS:Full Text Search) 機能を使ったプログラムを作成してみて、使い方や注意点などがある程度わかったので入門用の記事として紹介します

SQLiteは、パブリックドメインで提供されてる SQL92準拠の豊富な機能を持った中小規模用のデータベースです。データベースサーバーが不要で、1つのデータベースを1つのファイルとして作成するので、手軽に扱うことが出来ます。

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
WordPress プラグインは現在30000個以上もあるようです。その中から使用するプラグイン選ぶ時の
参考となるように人気のプラグインを紹介いたします。 日本製プラグインも応援してます (^^)…

ちょっと試して頂ければ分かりますが、SQLiteのFTSは、十分高速で実用的です
(これで、プラグインデータが23000件以上、作者データ12000件以上登録されています)

Web系でよく使われている全文検索(FTS) は、Mroonga という MySQL/MariaDB の全文検索用拡張機能があるようですが、なんとなくハードルが高い気がしてもっと小規模でいろんな環境で手軽に扱え、将来的にもいろいろ応用が効くと思い Sqlite3のFTSを使ってみました (^^)

 

各データベース毎に得手不得手がありますが、SQLiteは、今回の様なデータが10万件程度で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)対応コンパイル手順
PHP の sqlite3, pdo_sqlite モジュールの全文検索(FTS)機能を有効化してコンパイルする手順を紹介いたします (^^)

※他には、以前使用していた xampp 3.1.0 では FTS 機能が有効になっていました(最新の xampp がどうなっているかわかりません)

公開サーバー

残念ながら多くのレンタルサーバーでは、pdo_sqlite モジュールは FTS が有効化されていないことが多いです

サーバー毎に状況は異なると思いますが、私の使用しているエックスサーバーでも FTS は有効化されていませんでしたので、エックスサーバーでFTSを使う方法を紹介しておきます

エックスサーバーのPHPモジュール sqlite3, pdo_sqlite を全文検索(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等のリレーショナルデータベースのようなカラムに対して厳密に型付けるわけではなく動的な型として扱われます

データベースに保存されるデータタイプは以下の5種類となります

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からの秒数

これらのいずれかの形式で日付と時刻を保存し、使用することが出来ます

時系列でソートする等を行う場合は、unix 時間(Integer)として扱ったほうが若干高速だと思われますが、日付時刻にフォーマットした文字列 YYYY-MM-DD HH:MM:SS で揃えておけば日付の大小判定も問題ないし、SQL 文での確認動作を行う場合も扱いやすいのでお勧めです

値のデータ型とカラムのデータ型

先に保存するデータ自体の値のデータ型を紹介しましたが、データを格納する器側であるカラム(フィールド)の型について紹介します

MySQL等のリレーショナル・データベースは、通常保存する値とそのカラムのデータ型が一致していないと保存できないように厳密に型付けされています。
一方、SQLiteでは値とカラムのデータ型は厳格な型付をされているわけでなくデータ型は動的に決めています。カラムのデータ型は必須条件でなく(未指定も可)推奨という意味合いで、任意のタイプのデータが保存できるゆるくて疎な型付けとなっています

どういうことかと言うと、実際にデータを保存するときに挿入されたデータをカラムに対して指定されているデータ型にキャストして保存するのですが、キャスト出来なかった場合はそのまま保存する様になっています

SQLiteのカラムには、以下の5種類のデータ型を割り当てることができます

TEXT テキスト文字列(UTF-8/UTF-16BE/UTF-16LE)

  • NULL,TEXT,BLOBはそのまま保存
  • 数値データをTEXTカラムに挿入された場合は、TEXT形式に変換
NUMERIC 整数/浮動小数値 ※NULL,BLOBはそのまま保存

  • TEXTがNUMERICカラムに挿入された場合は、INTEGERまたはREALに変換
  • 変換出来ない場合はTEXT形式で保存
INTEGER 符号付き整数値 ※NULL,BLOBはそのまま保存

  • TEXTがINTEGERカラムに挿入された場合は、INTEGERに変換
  • 変換出来ない場合はTEXT形式で保存
REAL 浮動小数点値 ※NULL,BLOBはそのまま保存

  • TEXTがREALカラムに挿入された場合は、REALに変換、
  • 変換出来ない場合はTEXT形式で保存
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
上記1-4以外の場合 NUMERIC

参照 https://www.sqlite.org/datatype3.html

SQLiteにおけるデータ型の扱いをわかったうえで、後は目的毎に、データ型をきちっと意識したデータ処理が必要なのか、全てがテキストとしてして処理出来れば良いのかなど柔軟に対応できるようになっています

ちなみに FTS データを扱うには、専用のFTSテーブルを作成する必要があり、カラムのデータ型は、基本的にテキスト型のみに制限されます

この辺りの特徴をわかっていてデータ型を指定しないのと、わからずにデータ型を指定しないのでは大きな違いがありますので、ここはきちっと押さえておきましょう

次のページに続きます (^^)


まとめ記事紹介

search star user home refresh tag chevron-left chevron-right exclamation-triangle calendar comment folder thumb-tack navicon angle-double-up angle-double-down angle-up angle-down quote-left googleplus facebook instagram twitter rss