ホーム › ホームページを作る › プログラミング › データベース › SQLite3 (PDO) を使った全文検索 (FTS) 入門

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

テーブルへのデータ登録、変更、削除操作は INSERT、UPDATE、DELETEコマンドを使用します

FTSテーブルに対しても同様に INSERT、UPDATE、DELETEコマンドで行いますが、書き込まれるデータはトークナイザーで処理されたトークン毎にスペース区切りでなったデータです。英語ならトークナイザーによってトークンが抽出されて登録されますが、日本語だとトークン毎にスペースで区切ったデータを用意して書き込む必要があります

※日本語の文章から全文検索するには、別途、書き込む前に何らかの方法でスペース区切りのトークンに分割する必要があります。日本語形態要素解析（MEcab等が有名）してトークンに区切りるか、文章をN-gram変換等で単純に規則的に分割する等の方法があります

以下、SQL文を網羅的に紹介するものではありませんが、今回作成したプログラムで使ったものを中心に限定的な使い方ですがサンプルと共に紹介していきます

データ登録

INSERT

外部からのデータを登録するには、PDOの prepare メソッドを使い、バインドでプレースホルダとデータを指定すれば良いのですが、直接PDOを使うと細かく様々なパターンに合わせて記述できるぶんコード量も増えてかえって分かり難くなりがちなので、その処理をシンプル化した Ext_PDO の sql_prepare_exec メソッドを使った例で紹介します

INSERT文にテーブルのカラム名に対応する数のプレースホルダ ? をセットしたSQL文とそれに順番に対応するデータ変数を配列で渡して実行します

$db->sql_prepare_exec("INSERT INTO plugins_author (profile, author, location, updated) VALUES ( ?, ?, ?, ?)",  array($author_profile, $author, $location, $now->format('Y-m-d H:i:s')));

FTSテーブルに対する INSERT でも同じように実行することが出来ます

$db->sql_prepare_exec("INSERT INTO plugins_fts (docid, names, tags, authors ) VALUES ( ?, ?, ?, ?)", array((int)$rowid, $names, $tags, $authors ));

sql_prepare_exec メソッドの処理は下記のような内容です

/**
 * プリペアドステートメント(セキュリティ対策)
 * @param string $sql      例. "INSERT INTO table (id, title, author, date) VALUES ( ?, ?, ?, ?)"
 * @param array  $ar_param 例. array($id, $title, $author, $now->format('Y-m-d H:i:s')
 * @param $output
 * @return 
 */
// データ取得のないSQL文の実行
public function sql_prepare_exec($sql, $ar_param) {
    try {
        $stmt = $this->prepare( $sql );
        $stmt = self::_stmt_bind( $stmt, $ar_param );
        return self::_stmt_execute( $stmt );
    } catch (PDOException $e) {
        if(PDO::inTransaction()){
            throw $e;
        } else {
            self::display_error($e);
            return false;
        }
    }
}
テーブル間のカラム（フィールド）を関連付ける

データを複数のテーブルに分けて管理する場合に、テーブル間を関連付けるためにデータ登録した時のレコードIDを取得してそれを別のテーブルに登録したい場合があります

そのような時に改めてそのデータのIDをSELECT文で取得しなくとも、直前に挿入したID（ROWID）がPDO関数を使って簡単に取得出来ます

$lastid = $db->lastInsertId();

FTS用テーブルの場合は、カラムデータは全てテキストですので DOCID （ROWIDの別名）という UNIQUE な ID が特別に明示的に指定出来ますのでこれを利用します

通常のテーブルのプラグインデータを１対１で関連付けしたい場合には、プラグインデータのレコードIDをFTSデータのDOCIDとして明示的に指定することで関連付けが行えます

※ 1.3. Populating FTS Tables – FTSテーブルには DOCID （ROWIDの別名）という UNIQUE な ID で管理され、明示的に指定されない場合には自動的に設定されます

 

データ更新

UPDATE

INSERTと同様に UPDATE文にテーブルのカラム名に対応する数のプレースホルダ ? をセットしたSQL文とそれに順番に対応するデータ変数を配列で渡して実行します

Ext_PDO の sql_prepare_exec メソッドを使った例で紹介します

例えば、plugins_author テーブルの ID 123 レコードの (profile, author, location, updated) カラムから location データを ‘Japan’ に変更する場合を考えてみます

sql_prepare_exec("UPDATE plugins_author SET location = ?, updated =? WHERE id = '123' ",  array( 'Japan', $now->format('Y-m-d H:i:s')));

このように直接 sql_prepare_exec を記述してもよいのですが、条件により更新するデータのカラムが不定の場合には、更新前のデータから変更のあるカラムデータだけを対象にデータを更新します

下記の様なコードを使い、連想配列 $data にカラム名とデータをセットすることで柔軟に  sql_prepare_exec メソッドに渡すSQL文とデータ配列が作成出来ます

//更新するデータを連想配列＄dataにセット
$data['location'] = 'Japan';
$data['updated'] = $now->format('Y-m-d H:i:s');
    
//連想配列からキー（カラム名）とデータを取り出してSQL生成
$sql = "UPDATE plugins_author SET ";
$prm = array();
$idx = count($data);
foreach ($data as $key => $val) {
    $sql .= "$key = ?";
    $sql .= ($idx > 1)? ', ' : ' ';
    $prm[] = $val;
    $idx--;
}
$sql .= "WHERE id = '$lastid'";                   
$res = $db->sql_prepare_exec($sql, $prm);

データ削除

DELETE

指定した条件を満たすレコードを削除します

データ登録や更新のように、外部から入力されたデータにより削除する場合は PDOの prepare メソッドを使うべきですが、今回作成したプログラムでは外部からの削除は無く、内部処理として２年以上更新のないデータを削除するというケースで使用したので、この例では sql_exec メソッドで実行しています

$ckdate = $now->modify("-730 day")->format('Y-m-d H:i:s');
$db->sql_exec( "DELETE FROM plugins_base WHERE last_updated < '$ckdate'" );
参照されなくなったレコードの削除

複数のテーブルを使ってデータを管理している場合、上記のようにプラグインのデータを削除すると、その作者データがテーブルに残り、どこからも参照されないデータとなってしまう場合があります

以前MySQLを使っていた時は、下記のようなSQLでデータを削除していました
プラグインデータと作者テーブルを結合して author_id が NULL となるデータを参照されない作者データとして削除します

DELATE plugins_author FROM plugins_author LEFT JOIN plugins_base ON plugins_author.id = plugins_base.author_id WHERE plugins_base.author_id IS NULL

ところが SQLite では、このSQL文はエラーとなってしまいました
調べたところ SELECT 部をサブクエリーにすることで実行出来ました

DELETE FROM plugins_author WHERE id IN (SELECT a.id FROM plugins_author AS a LEFT JOIN plugins_base As p ON a.id = p.author_id WHERE p.author_id IS NULL)

参照　http://stackoverflow.com/questions/4967135/deleting-rows-from-sqlite-table-when-no-match-exists-in-another-table

トランザクション

単体のSQLコマンドとして INSERT / UPDATE /DELETE を紹介してきましたが、実際に書き込み系のコマンドを使用する場合にはトランザクションを使用することを推奨します

複数のテーブルを使って、関連付けられたデータを管理している場合にはトランザクションを使っていないとデータに不整合が起きる場合があるので注意です

また別の理由として、SQLiteのデータベースは、１つのファイルから構成されているのでファイル書き込み時のデータI/Oの能力に大きく影響を受けるので書き込み時の速度は遅めです。そのため１件ずつデータを書き込むよりも、トランザクションを活用してある程度まとめて（例えば１００件）データを書き込むことでデータI/O頻度を下げることでトータルな書き込み時間を短縮することができます

参照　INSERT is really slow – I can only do few dozen INSERTs per second

トランザクションの詳細は下記サイトを参考にしていただければと思います
atomic commit in SQLite

日本語だと少し古い情報ですが下記サイトが参考になります
http://www.antun.net/tips/api/sqlite.html

トランザクションのデフォルト動作は、当然ながら高速性よりもデータの安全性を優先したモードになっています
但し、そのへんは利用環境により変わってきますので、組み込みシステムやローカル環境、サーバー環等、アクセス頻度やは書き込みと読み込みの割合などの違いによって動作を選択出来るようになっています

トランザクションで処理する注意点

  1. 動作環境や使用状況に合わせて最適なトランザクションモードを設定すること
  2. トランザクションはある程度まとめたデータで処理することが望ましい
  3. トランザクション中は必要最小限の処理に限定（時間のかかる処理が入っているとロックエラーの原因となる）

トランザクション時の動作モード

今回はほとんどがデータ取得系で書き込み頻度も低く、サーバー上での動作なので、突然サーバーが落ちてしまうような可能性は低いと判断し、スピード重視の設定にしてみました

$db->exec("PRAGMA synchronous = 0");
$db->exec("PRAGMA journal_mode = PERSIST");

詳細は下記を参照して下さい
journal_mode
synchronous

※試していませんが書き込みが多い場合はジャーナルモードを WAL モードにすると良いそうです

次は、データ取得についてです　(^^)

データベース, , , , ,

まとめ記事紹介

コメントを残す

メールアドレスが公開されることはありません。 ※ が付いている欄は必須項目です

go-to-top