[PHP]SQLiteが好きになる！PDO接続してのあれこれレシピサンプル

SQLiteは、無償で利用できる軽量のデータベース(RDBMS)です。
単一のファイルにデータを格納するタイプなので非常に扱いやすいです。

SQLiteについては、別エントリーに少し書いているので確認してみてください。

[SQLite]起動時に読み込まれる設定ファイル(~/.sqliterc)おすすめ設定

こんにちは。 BIG1等を当てたいサラリーマンです。 なかなかBIG1等が当たらないので、今回はSQLite起動時の読み込みファ...

big1to.com

今回は、LinuxにインストールしてPDOを使って接続してみます。

こちらで構築したローカル開発環境にインストールします。

[ローカル開発環境 #01]ローカルサーバーを導入しよう

ローカル開発環境の構築：もくじ ローカルサーバーを導入しよう 前置き VirtualBoxの導入 Vagrantの導入 V...

big1to.com

目次

• インストール
• コマンド操作
• PDO接続
• SQL実行
• プリペアードステトメント
• トランザクション
• まとめ

インストール

yumでパッケージをインストールします。

sudo yum -y install sqlite

コマンド操作

コマンドでDBを作ってデータを投入してみます。

SQLiteはSELECTの結果表示が見づらいです。こちら見やすくする設定をエントリーしています。

[SQLite]起動時に読み込まれる設定ファイル(~/.sqliterc)おすすめ設定

こんにちは。 BIG1等を当てたいサラリーマンです。 なかなかBIG1等が当たらないので、今回はSQLite起動時の読み込みファ...

big1to.com

SQLite実行

SQLiteはDB名を指定して実行します。
DBがない場合は、カレントディレクトリ(現在のディレクトリ)に作成します。

DB名、test.dbで実行します。

sqlite3 test.db;
SQLite version 3.6.20
Enter ".help" for instructions
Enter SQL statements terminated with a ";"

テーブル作成

create table macs (id, name);

データ挿入

insert into macs values(1, 'MacBook');
insert into macs values(2, 'MacBook Pro');
insert into macs values(3, 'iMac');

データSELECT

select * from macs;
1|MacBook
2|MacBook Pro
3|iMac

終了

.q

PDO利用

こちらのエントリーで作成した、example.comのDocumentRootにpdo.phpファイルを配置します。

[ローカル開発環境 #04]Web開発環境を実用的にしよう

ローカル開発環境の構築：もくじ ローカルサーバーを導入しよう ローカルサーバーの設定をしよう Web開発環境を構築しよう Web...

big1to.com

コマンドで行った処理を同じようにPDOで行います。

MySQLで利用している、コードを再利用しています。
PHP Data Objects(PDO)の利点も再確認してみたいと思います。

PHPの公式サイトでは、以下のように説明されています。

使用しているデータベースが何であるかにかかわらず、同じ 関数を使用してクエリの発行やデータの取得が行えるということです。 PDO は、データベースの抽象化を行うのでは ありません。つまり、SQL を書き直したり 存在しない機能をエミュレートしたりはしないということです。

PHP: 導入 – Manual

DB接続

try {
    $options = array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION);
    $dbh = new PDO('sqlite:test.db','' ,'' ,$options);
} catch (PDOException $e) {
    echo 'Connection failed: ' . $e->getMessage();
    exit;
}

SQLiteには、ユーザ、パスワードの概念はありません。
第4引数を省略する場合は、第2引数と第3引数も省略可能です。

PDOのエラーは明確に明示しなければなりません。
PDO::ERRMODE_EXCEPTIONで、接続時以外のエラーもtry catchでcatchします。

参考ページ
PHP: エラーおよびエラー処理 – Manual

テーブル作成

try {
    $sql = 'create table macs (
                id integer primary key autoincrement, 
                name text
                );';
    $dbh->query($sql);
} catch (PDOException $e) {
    echo 'Error: ' . $e->getMessage();
    exit;
}

データ挿入

$names = array('MacBook', 'MacBook Pro', 'iMac');

try {
    $sql = 'insert into macs values(null, :name);';
    $stmt = $dbh->prepare($sql);

    foreach ($names as $name) {
        $ret = $stmt->execute(array(':name'=>$name));
    }
} catch (PDOException $e) {
    echo 'Error: ' . $e->getMessage();
    exit;
}

トランザクション

トランザクションも確認します。
強制的に、Syntaxエラーを発生させてロールバックさせます。

$names = array('MacBook Air', 'Mac mini', 'Mac Pro');

try {
    $sql = 'insert into macs values(null, :name);';
    $stmt = $dbh->prepare($sql);

    $dbh->beginTransaction();
    foreach ($names as $name) {
        if ($name == 'Mac mini') {
            $ret = $stmt->execute(array(':n'=>$name));
        } else {
            $ret = $stmt->execute(array(':name'=>$name));
        }
    }
    $dbh->commit();
} catch (PDOException $e) {
    $dbh->rollback();
    echo 'Error: ' . $e->getMessage();
    // exit;
}

データSELECT

try {
    $sql = 'select *, typeof(id) from macs;';
    $stmt = $dbh->query($sql);

    $rows = array();
    foreach ($stmt as $row) {
        $rows[] = $row;
    }
    $stmt->closeCursor();
} catch (PDOException $e) {
    echo 'Error: ' . $e->getMessage();
    exit;
}
var_dump($rows);

結果を、ブラウザのソースで見てみます。

きちんとロールバックはされています。
idは数値型で格納されていますが、文字列型でSELECTされています。
必要な場合は、ソースで吸収するようにしなければなりません。

まとめ

SQLiteは、扱いやすいRDBMSです。
PDOを利用すればMySQLのソースも、ほぼ再利用可能でした。

厳格なデータ型指定が必要な場合は、ソースでキャストする必要があります。