SQLiteをPHPのSQLite3 classで使う

programming
目次

PHPでSQLite3データベースを扱う方法がいくつかありますが、SQLite3 classを使用して簡単なデータベース操作をしてみました。

SQLite3を使用する環境

SQLite3はプログラムに埋め込んで簡単に使えるデータベースです。ファイルだけで動作するので配布も簡単にすることが出来ます。

今回の作業環境は「Ubuntu 20.04.2 LTS」上のDockerコンテナで動かしています。 使用したイメージは"php:7.4.21-apache"です。

  • PHP Version 7.4.21
  • SQLite Library 3.27.2
  • Apache Version Apache/2.4.38 (Debian)

PHPのDockerコンテナはコマンドラインで以下のように実行しています。

$ docker run -dit --name コンテナ名 -p 8080:80 -v "$PWD":/var/www/html/ php:7.4.21-apache

ディレクトリ構成は以下のようにし、“sqlite3c.php"ファイルをブラウザから呼び出して実行しました。

sqlite3class
├ db/
│ └ database.db ◀ PHPで作成するので初期状態ではファイルは存在しません
└ web/
   └ sqlite3c.php

データベースを作成する

SQLite3クラスのコンストラクタにデータベースファイルを指定することでSQLite3オブジェクトを作成し、SQLite3データベースをオープンします。 デフォルトではSQLite3データベースファイルがない場合、新規作成されます。

// SQLite3クラスコンストラクタ
public SQLite3::__construct(string $filename,
                            int $flags = SQLITE3_OPEN_READWRITE | SQLITE3_OPEN_CREATE,
                            string $encryptionKey = "")

// SQLite3データベースファイル名
$dbname = '../db/database.db';
try {
    // データベースを開く
    // データベースファイルが存在しない場合は新規に作成される
    $db = new SQLite3($dbname);
} catch (Exception $e) {
    echo $e->getMessage();
}

ブラウザのアドレスバーに"http://localhost:8080/sqlite3class/web/sqlite3c.php"を入力するとPHPプログラムが実行され、データベースファイルが作成されます。

sqlite3class
├ db/
│ └ database.db ◀ 作成されたファイル
└ web/
   └ sqlite3c.php

0バイトの"database.db"ファイルが作成されました。

テーブルを作成する

テーブルを作成するにはSQLを使用します。

結果を返さないクエリ(SQL)を実行します。

public SQLite3::exec(string $query): bool

・・・略・・・
    // テーブルを作成するSQLを変数定義
    $createTable = 'CREATE TABLE TEST_TABLE (
        ID INTEGER PRIMARY KEY,
        NAME VARCHAR(255) NOT NULL,
        MEMO TEXT,
        CREATED TEXT NOT NULL,
        UPDATED TEXT NOT NULL
        )';
    // SQLを実行する
    $ret = $db->exec($createTable);
    if (!$ret) {
        // SQLの実行に失敗した場合
        // 直近で失敗したリクエストの説明表示
        $error = $db->lastErrorMsg();
    }
・・・略・・・

データを追加する

テーブル作成のときのように、直接SQLのINSERT文を実行できます。 しかし、設定項目をパラメーター化したプリペアドステートメントの方が使いやすいです。

実行するSQL文を設定し、SQLite3Stmtオブジェクトを受け取ります。

public SQLite3::prepare(string $query): SQLite3Stmt|false

SQLite3Stmtオブジェクトでパラメータの値を変数にバインドします。

public SQLite3Stmt::bindValue(string|int $param, mixed $value, int $type = SQLITE3_TEXT): bool

変数をバインドする場合。

public SQLite3Stmt::bindParam(string|int $param, mixed &$var, int $type = SQLITE3_TEXT): bool

プリペアドステートメントを実行し、結果を受け取ります。

public SQLite3Stmt::execute(): SQLite3Result|false

・・・略・・・
    // 直接SQLを実行する場合
    $insSQL1 = "INSERT INTO test_table(id, name, memo, created_at, updated_at) VALUES (1, 'Apple', 'りんご、赤いりんご🍎とgreen apple🍏', DATETIME('now'), DATETIME('now'));";
    $insRet1 = $db->exec($insSQL1);

    // プリペアドステートメントを使用する場合
    $insSQL2 = "INSERT INTO test_table(id, name, memo, created_at, updated_at) VALUES (:id, :name, :memo, DATETIME('now'), DATETIME('now'));";
    // 実行するSQL文をセット
    if ($insStmt = $db->prepare($insSQL2)) {
        // SQLパラメータに変数をバインド
        $insStmt->bindParam(':id', $id, SQLITE3_INTEGER);
        $insStmt->bindParam(':name', $name);
        $insStmt->bindParam(':memo', $memo);
        // 変数に登録値をセット
        $id = 2;
        $name = 'Banana';
        $memo = 'バナナ、<b>🍌</b>';
        // SQL実行
        $insRet2 = $insStmt->execute();
        // 各変数に別の値をセットして同じSQLを実行
        $id = 3;
        $name = 'Cherry';
        $memo = 'さくらんぼ、<i>🍒</i>';
        $insRet3 = $insStmt->execute();
    }
・・・略・・・

データを取得する

取得結果が1つの場合は簡単に実行することが出来ます。

 public SQLite3::querySingle(string $query, bool $entireRow = false): mixed

SQLクエリを実行し、SQLite3Resultオブジェクトで結果を受け取ります。

public SQLite3::query(string $query): SQLite3Result|false

結果のSQLite3Resultオブジェクトはarrayで処理することが出来ます。

public SQLite3Result::fetchArray(int $mode = SQLITE3_BOTH): array|false

・・・略・・・
    // 取得結果が1つの場合
    // デフォルトではクエリが返す結果の最初のカラムの値が帰ります
    // $countRet 変数にはtest_tableのレコード数である int(3) がセットされます
    $countRet = $db->querySingle('SELECT count(id) FROM test_table');

    // 複数件のデータを取得する場合
    $selSql = 'SELECT id, name, memo, created_at, updated_at FROM test_table';
    // SQLを実行して結果をSQLite3Resultオブジェクトで受け取ります
    $selRet = $db->query($selSql);
    $sel1List = array();
    if ($selRet != false) {
        // 結果を行毎に処理
        $selRow = '<tr>';
        while ($row = $selRet->fetchArray(SQLITE3_ASSOC)) {
            $selRow .= '<td>'.htmlspecialchars($row['id']).'</td>';
            $selRow .= '<td>'.htmlspecialchars($row['name']).'</td>';
            $selRow .= '<td>'.htmlspecialchars($row['memo']).'</td>';
            $selRow .= '<td>'.htmlspecialchars($row['created_at']).'</td>';
            $selRow .= '<td>'.htmlspecialchars($row['updated_at']).'</td></tr>';
        }
        $sel1List[] = $selRow;
    }

    // プリペアドステートメントを使用する場合
    if($selStmt = $db->prepare('SELECT id, name FROM test_table WHERE id = :id'))
    {
        // 検索条件の変数:idに値をバインド
        $selStmt->bindValue(':id', 3, SQLITE3_INTEGER);
        // プリペアドステートメントを実行し、結果をSQLite3Resultオブジェクトで受け取ります
        $sel2Ret = $selStmt->execute();
        $sel2Names = array();
        while ($arr = $sel2Ret->fetchArray(SQLITE3_ASSOC)) {
            $sel2Names[$arr['id']] = $arr['name'];
        }
    }
・・・略・・・

データベースから切断する

データベースとの接続を閉じます。

 public SQLite3::close(): bool

・・・略・・・
    if (!$db->close()) {
        echo '切断に失敗しました。';
    }
・・・略・・・

例外処理の有効化

エラー発生時に例外を投げるか設定します。

public SQLite3::enableExceptions(bool $enable = false): bool

<!DOCTYPE html>
<html>
<head>
    <title>SQLite3の操作</title>
</head>
<body>
    <h1>SQLite3 例外</h1>

    <h2>例外を無効にした場合</h2>
    <?php
        $edb = new SQLite3(':memory:');
        try {
            // 例外のスローを無効にする
            $edb->enableExceptions(false);
            // エラーのあるSQLを実行する
            $edb->exec('CREATE TABLE disable');
        } catch(Exception $e) {
            // 例外内容の表示
            echo $e->getMessage();
        } finally {
            // データベースを閉じる
            $edb->close();
        }
    ?>

    <h2>例外を有効にした場合</h2>
    <?php
        $tdb = new SQLite3(':memory:');
        try {
            // 例外のスローを有効にする
            $tdb->enableExceptions(true);
            // エラーのあるSQLを実行する
            $tdb->exec('CREATE TABLE enable');
        } catch(Exception $e) {
            // 例外内容の表示
            echo $e->getMessage();
        } finally {
            // データベースを閉じる
            $tdb->close();
        }
    ?>
</body>
</html>

上の例をブラウザから実行した結果は以下のようになります。

SQLite3クラスの例外

SQLite3クラスの例外

トランザクション

SQLiteのSQLBEGIN、COMMIT、ROLLBACKを使用してトランザクション処理を行います。

Transaction

    try {
        $tdb = new SQLite3('../db/transaction.db');
    } catch(Exception $e) {
        echo $e->getMessage();
        goto owari;
    }
    // トランザクションの開始
    $tdb->exec('BEGIN');
    try {
        // テーブル作成
        $tdb->exec('CREATE TABLE temptb (name TEXT)');
        // データ追加
        $stmt=$tdb->prepare('INSERT INTO temptb(name) VALUES (:name)');
        $stmt->bindValue(':name', 'firstname', SQLITE3_TEXT);
        $stmt->execute();
        $stmt->bindValue(':name', 'secondname', SQLITE3_TEXT);
        $stmt->execute();
        // コミット　処理の確定
        $tdb->exec('COMMIT');
    } catch(Exception $e) {
        // ロールバック　処理の取り消し
        $tdb->exec('ROLLBACK');
        // 例外内容の表示
        echo '<pre>';
        print_r($e->getErrors());
        echo '</pre>';
    } finally {
        // データベースを閉じる
        $tdb->close();
    }
    owari: