SQLite3とphpのPDOを使用した、DBテーブルの自動作成

構築中のWebサイトに、SQLデータベースを導入することにしました。

今回のWebサイトは友人と分業して作っているため、Ubuntuで開発したあと、ソースツリーだけコピーしてxampp環境でテストできなければなりません。
「できるだけ導入作業がいらない」(というか事前準備がない)のが大事、ということになります。
つまり、DB側であらかじめテーブルなどを作成する必要がないように作りたいのです。

普通、商業サイトなどを作る際は、DB環境構築は一回きりなので、自動化などがされていない場合もあるようです。

データベースの自動セットアップ

PHPコード側は、「PHPプログラミングの教科書」様のデータベースを自動セットアップ（テーブルを自動作成）するには様の通りにすればよいです。

PHPからSQLite接続してテーブルを作成したら、.sqliteファイルが作成されます。

データベース操作関数にPDOを使うようにしておけば、mysqlなどへの移行も比較的簡単になるそうですが、いずれ移行することをねらうなら、実際にMySQLで試したほうがよいと思います。

一番下にPHP(PDO)によるサンプルソースがあります。

SQLiteの導入

Ubuntu

主にWorld Wide Windblue様のページを参考に。必要パッケージを導入します。
sudo apt-get install sqlite3 php5-sqlite
「2.適当なディレクトリにDBを作成してみる」に書いてあったDBファイルの作成手順ですが、私の環境と違うのか、ただSQLiteに接続して終了するだけでは、.sqliteファイルは作成されないようです。

結果的に事前に.sqliteファイルの作成は不要だったので、問題なしです。

ただし、.sqliteファイルを作成するディレクトリの権限を変更する必要があります。
chmod a+w indbfile/

PHPコードからsqliteにアクセスして、DB作成したら.sqliteファイルも問題なく作ることができました。

xampp

xampp v3.1.0にて、特に設定することなくsqliteを使うことができました。
これはかなり良いです。

TIPS:「カラムを書き込もうとしたらSQLiteのDBからテーブルが消えていた」場合

SQLiteはいろいろ気が利いている、と聞いていたのですが、それが原因で悩むことに。
CREATE TABLE したら、PDOを閉じないうちにカラムをINSERT しないと、空のtableが自動で削除されるようです。
.sqliteファイルのサイズがゼロだったので気づきました。

TIPS：SQLite環境でカラムを日付データ順にソートする

timestamp型への挿入はYYYYMMDDHHMMSS(PHPのdateで'YmdHis')だけかと思っていたのですが、'Y-m-d H:i:s'も使えるようです。
最終的に、date (DATE_RFC822) に落ち着きました。
でもSQLiteだからかもしれません。MySQLなどへの移行の際に苦労しそうで怖いです。

※(2013/07/04追記)
日付情報によるSQLカラムの並び替えに失敗していることが判明しました。
日付情報をRFC822形式から data('Y-m-d H:i:s') に変更しました。
SQLiteには日付型が存在しないことが、ソート失敗の原因と思われます。
日付によってSQLカラムを並び替える方法、そして任意の順位のカラムを取り出す方法は、リンク先の記事をどうぞ。
この方法で上手く行かない場合や、他のDBMS移行後に問題が発生したら、あらためて追記します。

その他のTIPS

INSERT動作の確認は、
select * from テーブル名
コマンドで行えば、表示部を作る前に書き込みができているかチェック可能です。


エラー表示については、PDOがPDOExceptionを投げてくれない件様を参考に追記する必要がありました。

ソース

テーブルを作成し、PDOを閉じる前に最初のカラムを書き込むサンプルです。

//DBに画像を追加
function addImgDB ($imgOrgName, $imgFileName, $username, $passwd){
    $uptime   = date (DATE_RFC822);
    $askcount = 1;
     try {
   
        $dsn = "sqlite:imgfile.sqlite";
        $conn = new PDO($dsn);
               
        $sql = "CREATE TABLE IF NOT EXISTS imgfile(
                        id INTEGER PRIMARY KEY AUTOINCREMENT,
                        origname   char(128) not null,
                        filename   char(128) not null,
                        uptime     timestanp not null,
                        askcount   integer   not null,
                        username   char(64),
                        passwd     char(64)
                     
                 )";
        $stmt = $conn->prepare($sql);
        $stmt->execute();
               
               
         // データ格納
        $sql = "INSERT INTO
                imgfile(origname, filename, uptime, askcount, username, passwd)
                VALUES (:origname, :filename, :uptime, :askcount, :username, :passwd)";
        print "sql:". $sql."<br>";
              
        $args = array(':origname' => $imgOrgName, ':filename' => $imgFileName, ':uptime' => $uptime,
                ':askcount' => $askcount, ':username' => $username, ':passwd' => $passwd);
        $stmt = $conn->prepare($sql);
        $stmt->execute($args);
           
    } catch (PDOException $e){
        echo "失敗しました\n";
        var_dump($e->getMessage());
    }
           
    return true;
}