PHPでSQLite3

PHPに標準で有効になっているSQLite拡張モジュールを利用します。サンプルとして利用した環境はDebian11ですが、Windows11環境にも触れながら進めていきます。

初期設定

Debian11においてはAPTで「php7.4-sqlite3」モジュールのインストール、Windows11の場合はphp.iniでSqlite3拡張を有効化する必要があります。

またPHPはバージョンを8を想定環境にしています。バージョンにより挙動が違うことがあります。

php.ini(Windows)

...
;extension=pdo_pgsql
#　コメントアウトを解除します。
extension=pdo_sqlite
;extension=pgsql
...

詳しくはSQLiteの公式ドキュメントを確認してもらえればと思いますが、SQLiteにおいての列のタイプは次のようになります。

INTEGER
符号付の最大8バイトの整数値です。サイズは値によって自動で変わります。
REAL
8バイトの浮動小数点数です。
TEXT
テキストです。対応するエンコーディングはUTF-8、UTF-16BE、UTF-16LEとなります。
BLOB
バイナリデータを保持します。
NULL

SQLite3インスタンスの生成とデータベースオープン

SQLite3はオブジェクト志向型のライブラリとなっています。次のようにしてインスタンスを生成します。

インスタンスからopenメソッドを使うことで、SQLite3データベースをオープンします。

# インスタンスの生成とデータベースオープン
$sqlite3 = new SQLite3('./sqlite3.db',SQLITE3_OPEN_READWRITE | SQLITE3_OPEN_CREATE,"");

openメソッドの第1引数にはデータベースファイルへのパスを渡します。第2引数、第3引数はオプションで、フラグ、暗号化キーとなります。

フラグには次の種類があります。

SQLITE3_OPEN_READWRITE
データベースファイルを読み書き用でオープンします
SQLITE3_OPEN_READONLY
データベースを読み込み専用でオープンします
SQLITE3_OPEN_CREATE
第1引数で指定したファイルが存在しない場合は生成します

デフォルトでは「SQLITE3_OPEN_READWRITE | SQLITE3_OPEN_CREATE」(読み書き・未存在時生成）となっています。

第3引数の暗号化キーは文字列で渡します。通常暗号化モジュールが存在しない為パラメータは無視されます。

openメソッドと同じパラメータをコンストラクタに渡すことで、オープン済みのSQLite3データベースのインスタンスを取得することもできます。

SQLite3インスタンスのメソッド

SQLの実行はSQLite3のインスタンスから実行します。たとえば結果(表)を返さないSQLを実行するにはexecメソッドを使います。

$sqlite3->exec('CREATE TABLE sample (column1 TEXT)');

SQLite3クラスが持つそのほかの主なメソッドは次の通りです。

prepare($query)
プリペアドステートメントを準備します。後で紹介するステートメントオブジェクトが変えるので、以降はそちらから操作します。

プレースホルダは「:」または「@」の後に任意の名前を付けます。公式ページには掲載されていませんでしたが、?の記法を使うこともできるようです。
```
$ps = $sqlite3->prepare("INSERT INTO users_table VALUES(:id,:name)");
$ps->bindValue(':no',0,SQLITE3_INTEGER);
$ps->bindValue(':name','User Name',SQLITE3_TEXT);
```
```
$ps = $sqlite3->prepare("INSERT INTO users_table VALUES(@id,@name)");
$ps->bindValue('@no',0,SQLITE3_INTEGER);
$ps->bindValue('@name','User Name',SQLITE3_TEXT);
```
```
$ps = $sqlite3->prepare("INSERT INTO users_table VALUES(?,?)");
$ps->bindValue(1,0,SQLITE3_INTEGER);
$ps->bindValue(2,'User Name',SQLITE3_TEXT);
```
数値での指定は、?を使った場合だけでなく:や@を使うときでも可能です。この時数値は1から始まります。
query($query)
値(表)を返すSQLを実行します。戻り値は後で紹介するリザルトクラスで返ります。以降はそちから操作をします。
close()
データベース接続を切断します。

リザルト

SQLiteインスタンスや、ステートメントからはSQLite3Resultクラスで結果が返ります。

ここからFetchArrayを実行することで行や列の値を取得することができます。

$res = $sqlite3->query("SELECT * FROM users_table");

$arr = $res->fetchArray();

// 行が無い場合はfalseが返ります
if ($arr !== false) {
  // カラム名か、0から始まるインデックスで列を取得します
  echo $arr[0];
}

// 結果を閉じます
$res->finalize();

各メソッドの詳細は次のようになっています。

columnName($列番号)
0から始まる列番号を指定して列名を取得します。ASで別名を付けた場合はその名前が戻ります。
columnType($列番号)
0から始まる列番号を指定して列のタイプが返ります。成功時の戻り値はSQLITE3_INTEGER、SQLITE3_FLOAT、 SQLITE3_TEXT、SQLITE3_BLOB、SQLITE3_NULL のいずれか、失敗時はFalseになります。
fetchArray($mode)
結果の行を取得します。modeにSQLITE3_ASSOCを指定すると連想配列、SQLITE3_NUMを指定すると数値添字配列で返ります。SQLITE3_BOTHを指定するとその両方を取得します。

行が無い場合はfalseを返します。
numColumns()
結果の行のカラム数を取得します。カラム数をfetchArrayで得られる配列からcountで取得しようとすると、SQLITE3_BOTHを指定時には倍になってしまうのでこのメソッドを利用する方が確実です。
finalize()
結果セットを閉じます。戻り値は常にTrueです。
reset()
結果セットを先頭行に戻します。

ステートメント

ステートメントはSQLite3オブジェクトのprepareメソッドの戻り値として受け取ることが多いオブジェクトです。

# ステートメントオブジェクトの生成
$stmt = $sqlite3->prepare("SELECT * FROM users_table WHERE id=:id");
# 値のバインド
$stmt->bindValue(':id',10,SQLITE3_INTEGER);
# 実行すると先のリザルトが返ります。クエリが値(表)を返さない場合も同様です。
$res = $stmt->execute();

if($res != false) {
  while($arr = $res->fetchArray()) {
    echo $arr['id'].$arr['name'];
  }
  $res->finalize();
}

ステートメントが持つメソッドは次の通りです。

bindParam($バインド位置, $値, $type);
文字列または1からはじまる数値で示されるバインド位置に、値をバインドします。

typeにはセットする値に適合するタイプを、SQLITE3_INTEGER、SQLITE3_FLOAT、 SQLITE3_TEXT、SQLITE3_BLOB、SQLITE3_NULLのいずれかから選択します。ただし、値がNULLだった場合はSQLITE3_NULLとなります。

省略した場合は値から自動判別されます。
bindValue($バインド位置, $値, $type);
パラメーターに変数の値をバインドします。bindParamとの違いはbindParamが値を参照渡しするのに対し、bindVlueは値渡しとなります。
clear()
バインドされているすべてのパラメータをクリアします。
reset()
プリペアドステートメントを実行前の状態に戻しますが、バインドした内容はそのままです。
close()
プリペアドステートメントを閉じます。
getSQL($expand)
ステートメントのSQLを取得します。$expandにTrueを設定すると、バインドした箇所はその値、バインドされていない箇所はNULLがセットされた状態のSQLが返ります。$expandの値をFalseにすると、SQLite3インスタンスのprepareに渡したSQLがそのまま返ります。
execute()
バインドした値でプリペアドステートメントを実行します。戻り値はリザルトクラスが返ります。同じステートメントクラスから複数回executeを呼んだ際、得られるリザルトは独立していないため1回ごとにリザルトをfinalize()することが推奨されています。

トランザクション

多くのDBMSがそうなようにSQLiteも暗黙のCommitをします。そのため大量のデータを取り扱う場合はトランザクションを発行すると処理が早いです。ただPHPのライブラリにはトランザクション専用のメソッドは用意されていないため、execメソッドで実行します。

begin transaction
トランザクションを開始します。

$sqlite3->exec("begin transaction");
rollback transaction
ロールバックします。

$sqlite3->exec("rollback transaction");
commit transaction
コミットします。

$sqlite3->exec("commit transaction");

投稿:2022/09/27 - 更新: