SQLiteとPHPで軽快なWebアプリケーションを作る

はじめに

　ここ最近のうちにオープンソースデータベース開発を行った人ならば、アプリケーションで使うデータベースを選ぼうとすると選択肢が山ほどあるということをご存知でしょう。現在では、Microsoft SQL ServerやOracleなどの商用製品だけでなく、MySQLやPostgreSQLのように、同等の機能を何分の一かのコストで提供するオープンソース製品も実用的な代替手段として利用できます。

　ただ、これらの製品は多数の堅牢な機能を備え、コストの削減にも貢献しますが、小さくはありません。PostgreSQLおよびMySQLの最新のダウンロード版は、それぞれ12MBと57MBもあります。最低限のデータベース機能だけを必要とする小規模のアプリケーションでは、機能豊富な大型データベース製品を使用するのは不釣合いであり、多くの場合、軽量データベースの方が適しています。軽量データベースはトリガ、ビュー、ストアドプロシージャなどの洗練された機能を備えていないかもしれませんが、その代わり、最小限のリソースとディスク領域で済むというメリットがあります。

　では、実際にどのような軽量データベースがあり、プロジェクト内でどのように利用できるのでしょうか？本稿では、この質問に答えるために、いくつかの選択肢を紹介し、サンプルアプリケーションを作成してみます。本稿で紹介するサンプルWebアプリケーションは個人用の仕事リスト（TODOリスト）で、個人ユーザが自分の「するべき仕事」をデータベースに記録するためのものです。ブラウザベースのインターフェイスで、仕事リストの項目を追加、編集、削除し、完了項目および保留項目のリストを表示することができます。

必要なもの

　このサンプルアプリケーションを実行するには、Apache WebサーバーでPHPを実行できる環境が必要です。どちらもオープンソースプロジェクトであり、無料でダウンロードして開発環境にインストールできます。

データベースの選択

　ここで、現在利用できる6種類の軽量データベースを簡単に紹介します。いずれも小規模から中規模のアプリケーションに適した軽量オープンソース製品です。

Apache Derby

Apache Derbyは、おそらく最も有名な軽量Javaデータベースエンジンです。オープンソースプロジェクトであるため無料でダウンロードでき、Apache Licenseの下で個人プロジェクトまたは商用プロジェクトに利用できます。DerbyはACID完全対応で、ANSI SQL標準に準拠し、Javaアプリケーションへの直接埋め込みを前提として設計されています。このデータベースはソースアプリケーションと同じJVM上で動作し、データベース通信には埋め込みJDBCドライバを使用します。複数の同時実行ユーザに対応し（埋め込みモードでも可）、EclipseやNetBeansなどの統合開発環境ならびにTomcatやWebSphereなどのサーバー環境にスムーズに統合でき、データベース操作のためのJavaベースの対話型コマンドラインツールを数多く含んでいます。

H2は、もう1つの軽量・高速のJavaベースのデータベースエンジンであり、JDBCとODBCの両APIを使って利用できます。埋め込みモードでもサーバーモードでも利用でき、トリガ、結合、ビュー、ストアドプロシージャ、暗号化に対応しています。H2は同時実行ユーザをサポートし、さらに簡単なクラスタリングメカニズムをサポートしているので、厳しいアップタイム要件が求められる基幹アプリケーションにも適しています。H2はMozilla Public Licenseの下、オンラインで無料で入手できます。

Ocelot

Ocelotは、SQL-92およびSQL-99に完全に準拠するWindows用データベースエンジンです。32ビットWindows DLLとしてパッケージ化されており、Windowsアプリケーションに簡単に統合でき、シングルユーザーモードでもマルチユーザーモードでも標準のODBC APIを通じてアクセスできます。Ocelotはトリガ、ストアドプロシージャ、ビューを完全サポートしており、データベースの保守とクエリ作成を行うためのグラフィカルな管理ツールを備えています。

Firebird

Firebirdは、Windowsおよび*NIXプラットフォームで利用できる高機能のクライアント/サーバーRDBMSです。Borlandのオープンソース版InterBaseデータベースをベースにしています。Firebirdでは、シングルユーザーデータベースエンジンを、WindowsまたはLinuxアプリケーションに直接統合できるライブラリファイルとして実現することができます（Linux版では一部制約があります）。この埋め込みエンジンはSQL-92に完全準拠し、SQL-99にほぼ準拠しています。ACID対応のトランザクション、シーケンス、トリガ、サブセレクト、参照整合性制約をサポートし、データベース操作を行うための各種コマンドラインツールを含んでいます。埋め込みFirebirdエンジンは、Mozilla Public Licenseと同等のライセンスの下で利用できます。

One$DB

One$DBは、DaffodilDBという商用Java RDBMSのオープンソース版であり、あらゆるJavaアプリケーションに埋め込み可能です。SQL-99に準拠し、JDBCを通じてアクセスできます（PHP拡張も利用できます）。暗号化テーブル、トリガ、ビュー、ストアドプロシージャをサポートしており、LGPLの下で個人目的にも商用目的にも利用できます。

SQLite

SQLiteは、スタンドアロンのCライブラリとして実装されている無料の埋め込み可能なシングルユーザーデータベースエンジンです。1つのデータベースにつき1つのディスクファイルを使用します。ドキュメントでは「SQL-92にほぼ準拠」とされていますが、外部キー制約、トリガ、ストアドプロシージャのサポートは含みません。また、限定バージョンのALTER TABLEコマンドと、フィールドデータ型のごく一部だけをサポートしています。しかし、SQLiteはここで紹介した中で最も小さく軽いデータベースエンジンであり、SQLite固有のドライバとPDOデータ抽象化レイヤの両方を通じてPHP 5.xでネイティブにサポートされています。

　これから開発するサンプルアプリケーションに最も適しているのはどのデータベースでしょうか？その答えは、各自のニーズによって異なります。オープンソースのLAMPスタックは最もコストが安く、コストの安さを優先するならば、Windows専用のOcelotはすぐに選択肢から除外されます。残る選択肢の中から、私は今回のアプリケーションのためにSQLiteを選びました。これにはいくつか理由があります。第一に、SQLiteはPHPでネイティブにサポートされているため、PHP開発者ならば特別な設定やインストールを行わなくてもすぐに開発に利用できます。第二に、SQLiteは他のデータベースとは異なりトリガ、ストアドプロシージャ、外部キー制約などの洗練された機能を備えていませんが、軽量でリソース効率が良く、高いパフォーマンスを期待できます。第三に、個人的な理由ですが、私はあまりJavaに詳しくないので、JavaベースのRDBMSエンジンよりもSQLiteの方が使いやすいと思ったからです。

データベースのインストール

　データベースとしてSQLiteを選択したら、まずはインストールする必要があります。クライアント/サーバ方式のMySQLやPostgreSQLとは異なり、SQLiteでは、コマンドラインプログラムを使用してディスクベースのデータベースファイルを直接操作します。このプログラムの各プラットフォーム用のコンパイル済みバイナリは、こちらのサイトからダウンロードできます。現時点では、WindowsおよびLinuxのコンパイル済みバイナリと、Linux、Windows、Mac OS Xシステムに対応するクロスプラットフォームのスターキット（starkit）が用意されています。

　WindowsシステムでもLinuxシステムでも、SQLiteのインストールは非常に簡単です。必要な作業は、ダウンロードしたアーカイブ（単体のバイナリファイル）の内容を、自分の選んだディレクトリ（原則的にはデフォルトのシステムパスに指定済みのもの）に展開することだけです。これにより、このバイナリを使ってSQLiteデータベースを作成および操作できます。

　このプログラムをカスタマイズしたい、あるいはお使いのプラットフォーム用のコンパイル済みバイナリがないなどの理由でプログラムをソースコードからコンパイルしたい場合は、ソースコードアーカイブをシステムにダウンロードし、ビルド領域を作成し、次に示す標準的なconfigure/make/make installの手順を使ってコードをコンパイルします。

root@thor:/tmp# tar -xzvf sqlite-3.3.17.tar.gz
root@thor:/tmp# mkdir build
root@thor:/tmp# cd build
root@thor:/tmp# ../sqlite-3.3.17/configure
root@thor:/tmp# make
root@thor:/tmp# make install

　デフォルトでは、コンパイルしたSQLiteバイナリが/usr/local/binにインストールされます。

　SQLiteバイナリをインストールしたら、本稿の作業リストアプリケーションで使用するtodo.db3という名前の空のデータベースを初期化します。このデータベースは次のようにして定義します。

Windowsの場合

C:ApacheData> sqlite3.exe todo.db3
SQLite version 3.3.17
Enter ".help" for instructions
sqlite>

Linuxの場合

root@thor:/usr/local/apache/data# sqlite3 todo.db3
SQLite version 3.3.17
Enter ".help" for instructions
sqlite>

　ここでSQLiteのコマンドプロンプトが表示されます。コマンドを入力するときは、先頭にピリオドを指定します。たとえば、ヘルプを表示するには.helpというコマンドを入力します。このコマンドにより、次のようなコマンド一覧が表示されます。

sqlite> .help
.bail ON|OFF           Stop after hitting an error. Default is OFF
.databases             List names and files of attached databases
.dump ?TABLE? ...      Dump the database in an SQL text format
.echo ON|OFF           Turn command echo on or off
.exit                  Exit this program
.explain ON|OFF        Turn output mode for EXPLAIN on or off.
.header(s) ON|OFF      Turn display of headers on or off
.help                  Show this message
.import FILE TABLE     Import data from FILE into TABLE
.indices TABLE         Show names of all indices on TABLE
...

　SQLiteのコマンドラインプログラムを終了するには、.quitと入力します。SQLiteのコマンドプロンプトからOSのコマンドプロンプトに戻ります。

sqlite> .quit
C:ApacheData>

　セキュリティのために、todo.db3データベースファイルはWebサーバーのルート以外の場所に保存するとよいでしょう。そうすれば、リモートユーザーがこのファイルをHTTP経由でダウンロードできる可能性はなくなります。

アプリケーション要件の定義

　ここで、今回のサンプルアプリケーションで実装すべき機能を簡単にまとめておきます。このリストは、後でデータベース構造を作成するときのガイドラインになります。

項目の入力 ―― ユーザーが新しい作業項目を入力できるようにします。各項目には、説明、期限、優先度を含めます。
項目の修正 ―― 入力済みの項目をユーザーが編集または削除できるようにします。
項目の状態の変更 ―― ユーザーが項目を「完了済み」としてマークできるようにします。
項目リストの表示 ―― ユーザーがすべての完了項目と保留項目のリストを日付順および優先度順に表示できるようにします。

　ここに示した機能はごく基本的なものであり、必要に応じてもっと複雑なアプリケーションにすることもできます。

　このサンプルアプリケーションでは、これらの機能を実装するために、データベースにアクセスしてitemsテーブル内のレコードを操作するさまざまなPHPスクリプトを使用します。ここで、スクリプトの内容を簡単に紹介しておきます。

　まずアプリケーションのインデックスページ（図1）を見てみましょう。このページは、保留項目と完了項目の最新の要約を日付順に並べて表示するindex.phpというスクリプトに基づきます。index.phpはSELECTクエリでデータベース内のレコードを読み取り、出力をHTMLページとして整形します。

図1　作業リストアプリケーションのインデックスページ。このリストでは作業項目を日付の降順で表示している。項目の追加または変更を行うためのリンクも用意されている。

　データベースへの新規項目の追加は、2つのPHPスクリプトを通じて行います。form.phpはデータ入力用のHTMLフォーム（図2）を生成し、save.phpではユーザーが入力した情報をデータベースに保存します。

図2　ユーザーが新しい作業項目を追加するときに使う項目入力フォーム。

　既存の作業項目を編集するときにも、同じフォームを使用します。form.phpはインテリジェントな判断を行い、編集モードで呼び出されたときにはフォームに既存レコードの内容を自動的に埋め込み、追加モードで呼び出されたときには空のフォームを生成します。

　項目をデータベースに保存した後は、その項目についてさまざまな操作を実行できます。保留項目に対しては削除または編集のほか、完了済みとしてマークすることができますが、完了項目に対しては削除しかできません。これらの操作はそれぞれ異なるスクリプトによって実現されます。delete.phpでは、DELETEクエリを通じて名前付きレコードを削除します。done.phpでは、UPDATEクエリを通じてデータベース内の作業項目の状態を変更します。

　このアプリケーションで使用するPHP定数とCSSスタイル規則は、それぞれinclude.phpとmain.cssに記述します。

　index.phpとform.phpはさまざまなSELECTクエリでデータベースのレコードを読み取るだけの「読み取り用」スクリプトであり、save.php、delete.php、done.phpはデータベースの内容を実際に変更する「書き込み用」スクリプトであるという点に注目してください。

データベースの定義

　実装のロードマップが明らかになったので、実際にアプリケーションの作成に進みましょう。まず、データベースファイルの名前を指定してSQLiteプログラムを呼び出し、新しいSQLiteデータベースを作成します。

root@thor:/usr/local/apache/data# sqlite3 todo.db3
SQLite version 3.3.17
Enter ".help" for instructions
sqlite>

　次のCREATE TABLEコマンドを使用してテーブルを追加します。

sqlite> CREATE TABLE items (
   ...> id INTEGER NOT NULL PRIMARY KEY,
   ...> name TEXT NOT NULL,
   ...> due INTEGER NOT NULL,
   ...> complete INTEGER NULL,
   ...> priority INTEGER NOT NULL,
   ...> status INTEGER NOT NULL
   ...> );

　表1に、このテーブル定義の各フィールドの簡単な説明を示します。

表1　サンプルアプリケーションのitemsテーブル内のデータベースフィールド

フィールド	説明
Id	レコードID
Name	作業項目の説明
Due	作業項目の実施期限（日）
Complete	作業項目の実際の完了日
Priority	作業項目の優先度： 1（優先度低）～5（優先度高い）
Status	作業項目の状態： 0（完了）または1（保留）

　テーブルが作成されたかどうかを確認するには、.tablesコマンドを使用します。このコマンドにより、現在のデータベースに含まれている全テーブルの一覧が表示されます。テーブルが正しく作成されている場合、.tablesコマンドを実行するとitemsというテーブル名が1つ返されます。

sqlite> .tables
items

　次に、サンプル実行に必要なダミーレコードを挿入します。

sqlite> INSERT INTO "items" VALUES(1,
   'Buy anniversary present',1184437800,NULL,4,1);
sqlite> INSERT INTO "items" VALUES(2,
   'Send invoices',1180549800,NULL,5,1);
sqlite> INSERT INTO "items" VALUES(3,
   'Finish homework',1180549800, 1180290600,1,0);
sqlite> INSERT INTO "items" VALUES(4,
   'Visit Jane',1180290600,NULL,2,1);
sqlite> INSERT INTO "items" VALUES(5,
   'Finalize hotel reservations',1181737000,NULL,5,1);
sqlite> INSERT INTO "items" VALUES(6,
   "Angie's birthday",1182537000,NULL,4,1);
sqlite> INSERT INTO "items" VALUES(7,
   'Weed the lawn',1182237000,NULL,1,1);

　dueフィールドに挿入する値が、日付らしく見えないので戸惑う人もいるかもしれません。これは日付を表すUNIXタイムスタンプです。このタイムスタンプはPHPのmktime()関数を使って簡単に生成できます（詳しくは後述）。

SQLiteのデータ型について補足

　SQLiteには、NULL、INTEGER、REAL、TEXT、BLOBという5つの基本データ型があります。ただし、SQLiteデータベースにデータを挿入するときには、挿入する各値のデータ型の方が挿入先フィールドのデータ型よりも優先されます。これはSQLite特有の「明確な型付け（manifest typing）」と呼ばれる機能で、この機能により、たとえばINTEGER型の値をTEXT型のフィールドに挿入したり、その逆を行ったりできます。ただし、自動増分数値フィールドを作成するINTEGER PRIMARY KEY型のフィールドだけは例外です（このフィールド型は前述のスキーマでも使われています）。

　明確な型付けを歓迎しない人もいるかもしれませんが、心配はいりません。SQLiteは厳密な類似性モード（Strict Affinity Mode）で実行することができ、その場合、不正な型の値をフィールドに挿入しようとするとエラーが返されます。明確な型付けの詳細についてはこちらを参照してください。また、SQL-92のうちSQLiteで省略されている機能（またはサポートされていない機能）を一度確認しておくとよいでしょう。

作業リストのメインページの作成

　次は、前節で作成したデータベースを操作するためのコードに取り掛かります。まずサンプルアプリケーションのインデックスページから始めます。これは保留項目と完了項目の要約を表示するページです。移植性を高めるために、以降のすべてのコードリストではPHPのPDO拡張とPDO/SQLiteドライバを使用します（どちらもPHPバージョン5.1以降でデフォルトでインストールされます）。インデックスページ全体のコードについては、リスト1を参照してください。

　リスト1の処理で重要なのは最初の数行です。ここでSQLiteデータベースへの接続を確立し、このデータベースからレコードを取得します。まず、新しいPDOオブジェクトを初期化し、SQLiteデータベースファイルの所在を示すDSN文字列をPDOオブジェクトに渡します。実際のDSN文字列は、接続しようとするデータベースによって異なります（詳細については、「補足説明：移植性」を参照してください）。次に、このスクリプトはPDOオブジェクトのquery()メソッドを呼び出して2つのSELECTクエリを実行します。1つ目のクエリでは保留項目の一覧を取得し、2つ目のクエリでは完了項目の一覧を取得します。どちらの場合も、以降の処理を単純化するために、fetchAll()メソッドを使用して結果セットを配列の入れ子として整形しています。

リスト1　index.php

<?php
require 'include.php';
try {
   // connect to database
   $dbh = new PDO('sqlite:../../db/todo.db3');
    
   // query and retrieve pending items
   $sth = $dbh->query("SELECT * FROM items WHERE status = '1' 
      ORDER BY due DESC, priority DESC");
   $pending = $sth->fetchAll();
   $sth = null;
    
   // query and retrieve completed items
   $sth = $dbh->query("SELECT * FROM items WHERE status = '0' 
      ORDER BY due DESC, priority DESC");
   $complete = $sth->fetchAll();
   unset($dbh);
} catch (PDOException $e) {
   die('Error: ' . $e->getMessage());
}
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01//EN">
<html>
<head>
  <title>To-Do List</title>
  <link rel="stylesheet" href="main.css" />
</head>

<body>
  <h2>To-Do List</h2>
  <div class="category">Pending Items</div>
  
  <!-- generate listing of pending items -->    
  <?php if (sizeof($pending) > 0) {  ?>
  
  <table>
    <tr>
      <td class="head"></td>
      <td class="head">Due date</td>
      <td class="head">Priority</td>
    </tr>
    
  <?php foreach ($pending as $p) { ?>
    <tr>
      <td><?php echo $p['name']; ?></td>
      <td><?php echo date('d M Y', $p['due']); ?></td>
      <td><?php echo $priorities[$p['priority']]; ?></td>
    </tr>
    
  <?php } ?>
  
  </table>
  
  <?php } else { ?>
    <p>None</p>
  <?php } ?>
  
  <!-- link to add a new to-do item -->    
  <p><a href="form.php">Add a new entry</a></p>
  
  <!-- generate listing of completed items -->    
  <div class="category">Completed Items</div>
  
  <?php if (sizeof($complete) > 0) {  ?>
  
  <table>
    <tr>
      <td class="head"></td>
      <td class="head">Due date</td>
      <td class="head">Completion date</td>
    </tr>
    
  <?php foreach ($complete as $c) { ?>
  
    <tr>
      <td><?php echo $c['name']; ?></td>
      <td><?php echo date('d M Y', $c['due']); ?></td>
      <td><?php echo date('d M Y', $c['complete']); ?></td>
    </tr>
    
  <?php } ?>
  
  </table>
  
  <?php } else { ?>
    <p>None</p>
  <?php } ?>
  
 </body></html>

補足説明: 移植性

　本稿のコードリストでは、PHP 5.1以降の新機能であるPHPのPDO（PHP Data Objects）拡張を使用しています。この拡張はデータベース抽象化レイヤのような働きをし、この拡張を利用すると、移植可能なデータベース非依存のコードを簡単に記述でき、新しいデータベースに切り換えるときにも、PDOコンストラクタに渡すDSN文字列を編集するだけで済みます。
　たとえばMySQLからSQLiteに切り換える場合、必要な作業はDSN文字列を次のように変更することだけです。

<?php
$dbh = new PDO('mysql:host=localhost;port=3307;dbname=testdb', 
  'user, 'pass);
?>

　ただし、PDOなどのデータベース抽象化レイヤを使用すると、移植性は高まりますが、アプリケーションのパフォーマンスに影響が出ることがあるので注意してください。移植性を重視しない場合は、PHPのネイティブSQLiteサポートを使用した方が高いパフォーマンスを得られます。

　必要なデータをすべて取得したら、単純なHTMLページを生成し、foreach()ループを使用して、fetchAll()から返された配列を処理します。1つ1つのレコードについて、説明、実施期限、完了日（データがある場合）、優先度を表形式でページに出力します（図3）。ここでPHPのdate()メソッドを使用して、dueおよびcompleteフィールドで返されたUNIXタイムスタンプを人間にわかりやすい表記に変換していることに注目してください。

図3　index.phpの出力結果。データベースから保留項目と完了項目を取得し、表形式で表示する。

　さらに、リスト1で2つの外部ファイルをインクルードしている点に注意してください。1つ目はinclude.phpで、このファイルには複数のコードリストで使われる定数（優先度レベルの英文表記や月名など）が定義されています。

<?php
// define labels for priority values
$priorities = array (
    '1' => 'Very Low',
    '2' => 'Low',
    '3' => 'Medium',
    '4' => 'High',
    '5' => 'Very High',
);

// define array of month names
$months = array(
    '1' => 'Jan',
    '2' => 'Feb',
    '3' => 'Mar',
    '4' => 'Apr',
    '5' => 'May',
    '6' => 'Jun',
    '7' => 'Jul',
    '8' => 'Aug',
    '9' => 'Sep',
    '10' => 'Oct',
    '11' => 'Nov',
    '12' => 'Dec',
);
?>

　2つ目はmain.css（リスト2を参照）で、このファイルにはサンプルアプリケーションで生成される各種のHTMLドキュメントの書式設定に使われるCSSスタイルシートが含まれています。

リスト2　main.css

h2 {
  font: 125% Verdana, sans-serif;
}

input.txt {
  color: #00008B;
  background-color: #E3F2F7;
  border: 1px inset #00008B;
  width: 200px;
}

input.btn {
  color: #00008B;
  font: bold Verdana;
  background-color: #ADD8E6;
  border: 1px outset #00008B;
}

select.txt {
  color: #00008B;
  background-color: #E3F2F7;
  border: 1px inset #00008B;    
}

div.category {
  font: bold 80% Verdana, sans-serif;
  background-color: #ADD8E6;
  padding-bottom: 5px;
  margin-top: 8px;
  width: 600px;
}

td.head {
  font: bold 80% Verdana, sans-serif;    
}

td {
  padding: 3px;    
  font: 80% Verdana, sans-serif;  
}

p {
  font: 80% Verdana, sans-serif;   
  padding-left: 5px;         
}

.late {
  color: red;    
}

.ontime {
  color: green;    
}

a:link, a:visited {
  color: blue;    
}

リスト項目の追加

　リスト1には、form.php（リスト3を参照）へのリンクも含まれています。これは、HTMLフォームを使用して新しい作業項目をデータベースに追加するスクリプトです。

　一見したところ、このリスト3は、図2の画面（項目の説明を示すテキストフィールドと実施期限および優先度のドロップダウンリストを含んだ画面）を作成するための単純なフォームです。しかし、このフォームは後でもっと複雑になってくるので、現時点ではこのくらい単純なものにしておきます。

リスト3　form.php

<?php
require 'include.php';
// get current year
$year = date('Y');
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01//EN">
<html>
<head>
  <title>To-Do Form</title>
  <link rel="stylesheet" href="main.css" />
</head>

<body>
  <h2>Enter To-Do Item</h2>
  <form method="post" action="save.php">
    <table>
      <tr>
        <td><label>Title:</label></td>
        <td><input type="text" name="name" 
          class="txt" value=""></td>
      </tr>

      <tr>
        <td><label>Due Date:</label></td>
        <td>
          <select name="due_dd" class="txt">
          
          <?php for ($x=1; $x<=31; $x++) { 
          ?>                
            <option value="<?php echo $x; ?>">
              <?php echo $x; ?></option>
          <?php } ?>
          
          </select>
          
          <select name="due_mm" class="txt">
          
          <?php for ($x=1; $x<=12; $x++) { 
          ?>                
            <option value="<?php echo $x; ?>">
              <?php echo $months[$x]; ?></option>
          <?php } ?>
          
          </select>
          
          <select name="due_yy" class="txt">
          
          <?php for ($x=$year; $x<=($year+5); $x++) { ?>                
            <option value="<?php echo $x; ?>">
              <?php echo $x; ?></option>
          <?php } ?>
          
          </select>
        </td>
      </tr>
      
      <tr>
        <td><label>Priority:</label></td>
        <td>
          <select name="priority" class="txt">
          
          <?php for ($x=1; $x<=5; $x++) {              
          ?>                
            <option value="<?php echo $x; ?>">
              <?php echo $priorities[$x]; ?></option>
          <?php } ?>
          
          </select>
        </td>
      </tr>
    </table>
    
    <p><input type="submit" name="submit" 
         value="Submit" class="btn"></p>
  </form>
</body>
</html>

　ユーザーがこのフォームを送信すると、save.php（リスト4を参照）が呼び出されます。このスクリプトは、ユーザー入力を検証し、それをデータベースに保存するために必要なSQLを生成するという重要な処理を行います。

　リスト1ではSELECTクエリを使ってSQLiteデータベースからレコードを取得する方法を示しましたが、リスト4ではデータベースに新しいレコードを追加する方法を示しています。リスト4の前半部分ではもっぱらユーザー入力の検証を行っています。ここでは、正規表現を使ってnameフィールドに不正な文字が含まれていないかを確認し、さらにcheckdate()関数を使って実施期限が有効な日付であるかどうかを確認します。

　入力が有効な場合には、リスト1と同じ方法で新しいPDOインスタンスを初期化し、PDOのquote()メソッドを使ってフォームの送信データ中の特殊文字をエスケープします。その後、これらの値をSQLのINSERTクエリ文字列に組み込みます。最後に、PDOのexec()メソッドを使ってクエリを実行し、レコードをデータベースに保存します。exec()メソッドは、そのクエリの結果として変更された行の数を返します。クエリが完了したら、PDOインスタンスを破棄し、クライアントブラウザをインデックスページ（リスト1を参照）にリダイレクトします。

リスト4　save.php

<?php
if (isset($_POST['submit'])) {
     
    // perform input validation
    // add errors to $errors array
    $errors = array();
    
    // check name
    $name = stripslashes($_POST['name']);
    if(!isset($_POST['name']) || !preg_match(
       "/^[a-z0-9][a-z0-9¥'¥.[:space:]]+$/i", $name)) {
        $errors[] = 'Invalid title';
    }
             
    // check priority
    if (!isset($_POST['priority']) || 
        !is_numeric($_POST['priority'])) {
        $errors[] = 'Invalid priority';
    }
    
    // check date validity
    if (!checkdate($_POST['due_mm'], 
        $_POST['due_dd'], $_POST['due_yy'])) {
        $errors[] = 'Invalid date';
    }
    
    // print errors and exit
    // if no errors, proceed to save the record to the database
    if (sizeof($errors) > 0) {
        die('Error: <br/>' . implode($errors, '<br/>'));
    } else {
        
        try {
            // connect to database
            $dbh = new PDO('sqlite:../../db/todo.db3');
            $dbh->setAttribute(
               PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);
        
            // quote all input characters
            $name = $dbh->quote($name);        
            $priority = (int) $_POST['priority'];
            // generate UNIX timestamp for due date
            $due = $dbh->quote(mktime(
              0,0,0,$_POST['due_mm'], 
              $_POST['due_dd'],$_POST['due_yy']));
            
            // generate INSERT query
            $sql = "INSERT INTO items (
              name, due, priority, status) 
              VALUES ($name, $due, '$priority', '1')";

            // execute query
            $dbh->exec($sql);
            unset($dbh);
            
            // redirect to index page
            header('Location: index.php');
            exit();        
        } catch (PDOException $e) {
           die('Error: ' . $e->getMessage());
        }    
    }    
} else {
    die('Error in form submission');    
}
?>

リスト項目の編集

　リスト3およびリスト4で示したフォームとフォーム処理コードに少し手を加えれば、既存レコードの編集機能を実現できます。

　しかしその前に、インデックスページを少し手直しして、各レコードの横に「Change」リンクを追加しましょう。そのためには、保留項目の表を作成するときに、「Change」リンクを含んだ<td>タグを新たに追加します。このリンクには、各項目のIDを含んだURLをリンク先として指定する必要があります。次のコードリストでは、この部分に関係するコードを太字で示しています。

...
  <table>
    <tr>
      <td class="head"></td>
      <td class="head">Due date</td>
      <td class="head">Priority</td>
      <td></td>
    </tr>
       
  <?php foreach ($pending as $p) {
    $class = (mktime() > $p['due']) ? 'late' : 'ontime';
  ?>
    <tr>
      <td class="<?php echo $class; ?>">
        <?php echo $p['name']; ?></td>
      <td class="<?php echo $class; ?>">
        <?php echo date('d M Y', $p['due']); ?></td>
      <td class="<?php echo $class; ?>">
        <?php echo $priorities[$p['priority']]; ?></td>
      <td><a href="form.php?id=
        <?php echo (int) $p['id']; ?>">Change</a></td>
    </tr>
       
  <?php } ?>
     
  </table>
...

　次に、form.phpを修正して、レコードIDを指定してこのスクリプトを呼び出したときに、対応するレコードがデータベースから自動的に取得され、フォームに埋め込まれるようにします。修正版のフォームはリスト5のようになります。

　リスト5の第一の変更点は、スクリプトの最初の部分にさまざまなPDO呼び出しを追加していることです。このスクリプトは、レコードIDを指定して呼び出されると、新しいPDO接続を初期化し、SELECTクエリを使用して、該当レコードをフィールド-値のペアの配列という形で取得します。この部分では、dueフィールドのUNIXタイムスタンプ値を日、月、年の数値に分けるという処理も行っています。

　さらに、フォーム部分のさまざまな入力フィールドのコードにも手を加え、query()およびfetch()で生成したレコードセット配列から適切な値を取得してフォームに埋め込むようにしています。入力フィールドがドロップダウンリストの場合は、条件テストを使用して、あらかじめ適切なオプションが自動的に選択されるようにします。またこの修正版スクリプトでは、取得するレコードのIDを格納するためのidという隠しフィールドを追加しています（このフィールドはリスト6で使われます）。

リスト5　修正版のform.php

<?php
require 'include.php';
// get current year
$year = date('Y');
$record = null;
if (isset($_GET['id']) && is_numeric($_GET['id'])) {
    $id = (int) $_GET['id'];
    try {
        // connect to database
        $dbh = new PDO('sqlite:../../db/todo.db3');
        
        // retrieve item record
        $sth = $dbh->query("SELECT * FROM items WHERE id = '$id'");
        $record = $sth->fetch();
        
        // break due date timestamp into constituent parts
        $date = getdate($record['due']);
        $record['due_mm'] = $date['mon'];
        $record['due_yy'] = $date['year'];
        $record['due_dd'] = $date['mday'];        
        unset($dbh);        
    } catch (PDOException $e) {
       die('Error: ' . $e->getMessage());
    }            
}        
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01//EN">
<html>
<head>
  <title>To-Do Form</title>
  <link rel="stylesheet" href="main.css" />
</head>

<body>
  <h2>Enter To-Do Item</h2>
  <form method="post" action="save.php">
    <table>
      <tr>
        <td><label>Title:</label></td>
        <td><input type="text" name="name" class="txt" value="
           <?php echo $record['name']; ?>"></td>
      </tr>

      <tr>
        <td><label>Due Date:</label></td>
        <td>
          <select name="due_dd" class="txt">
          
          <?php for ($x=1; $x<=31; $x++) { 
            $selected = ($record['due_dd'] == $x) ? 'selected' : '';    
          ?>                
            <option value="<?php echo $x; ?>" 
              <?php echo $selected; ?>><?php echo $x; ?></option>
          <?php } ?>
          
          </select>
          
          <select name="due_mm" class="txt">
          
          <?php for ($x=1; $x<=12; $x++) { 
            $selected = ($record['due_mm'] == $x) ? 'selected' : '';    
          ?>                
            <option value="<?php echo $x; ?>" 
              <?php echo $selected; ?>><?php echo $months[$x]; ?>
            </option>
          <?php } ?>
          
          </select>
          
          <select name="due_yy" class="txt">
          
          <?php for ($x=$year; $x<=($year+5); $x++) { 
              $selected = ($record['due_yy'] == $x) ? 'selected' : '';
           ?>                
            <option value="<?php echo $x; ?>" 
              <?php echo $selected; ?>><?php echo $x; ?></option>
          <?php } ?>
          
          </select>
        </td>
      </tr>
      
      <tr>
        <td><label>Priority:</label></td>
        <td>
          <select name="priority" class="txt">
          
          <?php for ($x=1; $x<=5; $x++) {
             $selected = ($record['priority'] == $x) ? 'selected' : '';
          ?>                
            <option value="<?php echo $x; ?>" 
              <?php echo $selected; ?>>
              <?php echo $priorities[$x]; ?></option>
          <?php } ?>
          
          </select>
        </td>
      </tr>
    </table>
    
    <input type="hidden" name="id" 
           value="<?php echo $record['id']; ?>">    
    
    <p>
      <input type="submit" name="submit" value="Submit" class="btn">
    </p>
  </form>
</body>
</html>

　form.phpに上記の変更を加えたことにより、save.phpスクリプトにも変更の必要性が出てきます。ユーザーからフォームが送信されたときに、送信されたデータが既存レコードのものなのか新規レコードなのかを判断し、状況に応じて適切なUPDATEクエリまたはINSERTクエリを生成しなければならないのです。そこで、save.phpにも変更を加えます。修正版のsave.phpはリスト6のようになります。

　リスト6の変更点はそれほど多くありません。修正版スクリプトでは、idという隠しフィールドをフラグとして利用し、UPDATEクエリとINSERTクエリのどちらを生成するかを判断しています。PDOのexec()メソッドを前述のように使用して、データベースに対してクエリを実行します。

リスト6　修正版のsave.php

<?php
if (isset($_POST['submit'])) {
    
    // check if record ID exists in the form submission
    if (isset($_POST['id']) && is_numeric(trim($_POST['id']))) {
        $id = (int) $_POST['id'];
    }
         
    // perform input validation
    // add errors to $errors array
    $errors = array();
    
    // check name
    $name = stripslashes($_POST['name']);
    if (!isset($_POST['name']) || !preg_match(
       "/^[a-z0-9][a-z0-9¥'¥.[:space:]]+$/i", $name)) {
       $errors[] = 'Invalid title';
    }
             
    // check priority
    if (!isset($_POST['priority']) || 
       !is_numeric($_POST['priority'])) {
       $errors[] = 'Invalid priority';
    }
    
    // check date validity
    if (!checkdate($_POST['due_mm'], $_POST['due_dd'], 
       $_POST['due_yy'])) {
       $errors[] = 'Invalid date';
    }
    
    // print errors and exit
    // if no errors, proceed to save the record to the database
    if (sizeof($errors) > 0) {
       die('Error: <br/>' . implode($errors, '<br/>'));
    } else {
        
        try {
            // connect to database
            $dbh = new PDO('sqlite:../../db/todo.db3');
            $dbh->setAttribute(
              PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);
        
            // quote all input characters
            $name = $dbh->quote($name);        
            $priority = (int) $_POST['priority'];
            // generate UNIX timestamp for due date
            $due = $dbh->quote(mktime(
               0,0,0,$_POST['due_mm'],$_POST['due_dd'],
               $_POST['due_yy']));
            
            // generate UPDATE or INSERT query
            if (isset($id)) {
                $sql = "UPDATE items SET name=$name, 
                  due=$due, priority='$priority',status='1' 
                  WHERE id='$id'";
            } else {
                $sql = "INSERT INTO items (
                name, due, priority, status) VALUES (
                $name, $due, '$priority', '1')";
            }
            
            // execute query
            $dbh->exec($sql);
            unset($dbh);
            
            // redirect to index page
            header('Location: index.php');
            exit();        
        } catch (PDOException $e) {
           die('Error: ' . $e->getMessage());
        }    
    }    
} else {
    die('Error in form submission');    
}
?>

リスト項目のマーキングと削除

　ここまでの段階で、PHP Webアプリケーションから軽量のSQLiteデータベースに対してSELECT、INSERT、UPDATEクエリを実行できるようになりました。あと必要なのは、DELETEコマンドでレコードを削除する機能と、UPDATEコマンドでレコードを完了済みとしてマークする機能です。

　どちらの機能も、実装は特に難しくありません。項目を完了済みとしてマークするには、項目のstatusフィールドを1（保留）から0（完了）に変更します。この処理はdone.phpで行います。

<?php
// check for record ID
if (isset($_GET['id']) && is_numeric($_GET['id'])) {
    $id = (int) $_GET['id'];
    try {
        // connect to database
        $dbh = new PDO('sqlite:../../db/todo.db3');
        
        // update item record
        // change status field
        $now = mktime();
        $dbh->exec("UPDATE items SET status = '0', 
           complete = '$now' WHERE id = '$id'");
        
        // redirect to index page
        header('Location: index.php');
        exit();
    } catch (PDOException $e) {
       die('Error: ' . $e->getMessage());
    }    
} else {
    die('Error in form submission');    
}
?>

　このスクリプトでは、UPDATEクエリを使用して、選択したレコードのstatusフィールドを0に変更します（クエリの実行にはやはりPDOのexec()メソッドを使用します）。また、現在の日付（完了日）をUNIXタイムスタンプ形式で保存していることに注意してください。

　項目を削除するには、対象項目のレコードIDを使用してDELETEクエリを発行します。この処理はdelete.phpで行います。

<?php
// check for record ID
if (isset($_GET['id']) && is_numeric($_GET['id'])) {
    $id = (int) $_GET['id'];
    try {
        // connect to database
        $dbh = new PDO('sqlite:../../db/todo.db3');
        
        // delete item
        $dbh->exec("DELETE FROM items WHERE id = '$id'");
        
        // redirect to index page
        header('Location: index.php');
        exit();
    } catch (PDOException $e) {
       die('Error: ' . $e->getMessage());
    }    
} else {
    die('Error in form submission');    
}
?>

　この時点で、今度はアプリケーションのインデックスページを修正し、各レコードの隣に新しい機能のリンクを追加する必要があります。修正版のインデックスページはリスト7のようになります。

　この変更により、すべての保留項目に「Mark as Done」、「Change」、「Remove」というリンクが表示され、すべての完了項目に「Remove」リンクが表示されるようになります。さらに、修正版スクリプトではもう1つ機能を追加し、現在の日付（保留項目の場合）または完了日（完了項目の場合）を項目の実施期限と比較して、期限を過ぎている項目を赤で表示するようにしています。前掲の図1は、上記すべての機能を実装した状態のインデックスページです。

リスト7　修正版のindex.php

<?php
require 'include.php';
try {
    // connect to database
    $dbh = new PDO('sqlite:../../db/todo.db3');
    
    // query and retrieve pending items
    $sth = $dbh->query("SELECT * FROM items WHERE status = '1' 
       ORDER BY due DESC, priority DESC");
    $pending = $sth->fetchAll();
    $sth = null;
    
    // query and retrieve completed items
    $sth = $dbh->query("SELECT * FROM items WHERE status = '0' 
       ORDER BY due DESC, priority DESC");
    $complete = $sth->fetchAll();
    unset($dbh);
} catch (PDOException $e) {
   die('Error: ' . $e->getMessage());
}
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01//EN">
<html>
<head>
  <title>To-Do List</title>
  <link rel="stylesheet" href="main.css" />
</head>

<body>
  <h2>To-Do List</h2>
  <div class="category">Pending Items</div>
  
  <!-- generate listing of pending items -->    
  <?php if (sizeof($pending) > 0) {  ?>
  
  <table>
    <tr>
      <td class="head"></td>
      <td class="head">Due date</td>
      <td class="head">Priority</td>
      <td></td>
      <td></td>
      <td></td>
    </tr>
    
  <?php foreach ($pending as $p) {
    $class = (mktime() > $p['due']) ? 'late' : 'ontime';
  ?>
    <tr>
      <td class="<?php echo $class; ?>">
         <?php echo $p['name']; ?></td>
      <td class="<?php echo $class; ?>">
         <?php echo date('d M Y', $p['due']); ?></td>
      <td class="<?php echo $class; ?>">
         <?php echo $priorities[$p['priority']]; ?></td>
      <td><a href="done.php?id=
         <?php echo (int) $p['id']; ?>">Mark as done</a> | </td>
      <td><a href="form.php?id=
        <?php echo (int) $p['id']; ?>">Change</a> | </td>
      <td><a href="delete.php?id=
         <?php echo (int) $p['id']; ?>">Remove</a></td>
    </tr>
    
  <?php } ?>
  
  </table>
  
  <?php } else { ?>
    <p>None</p>
  <?php } ?>
  
  <!-- link to add a new to-do item -->    
  <p><a href="form.php">Add a new entry</a></p>
  
  <!-- generate listing of completed items -->    
  <div class="category">Completed Items</div>
  
  <?php if (sizeof($complete) > 0) {  ?>
  
  <table>
    <tr>
      <td class="head"></td>
      <td class="head">Due date</td>
      <td class="head">Completion date</td>
      <td></td>
    </tr>
    
  <?php foreach ($complete as $c) { 
    $class = ($c['complete'] > $c['due']) ? 'late' : 'ontime';
  ?>
  
    <tr>
      <td class="<?php echo $class; ?>">
         <?php echo $c['name']; ?></td>
      <td class="<?php echo $class; ?>">
         <?php echo date('d M Y', $c['due']); ?></td>
      <td class="<?php echo $class; ?>">
         <?php echo date('d M Y', $c['complete']); ?></td>
      <td><a href="delete.php?id=
         <?php echo (int) $c['id']; ?>">Remove</a></td>
    </tr>
    
  <?php } ?>
  
  </table>
  
  <?php } else { ?>
    <p>None</p>
  <?php } ?>
  
  
</body>
</html>

データベースのバックアップと復元

　これで作業リストアプリケーションは完成です。このアプリケーションを使用して作業項目を保存し、優先順位を指定または変更し、完了済みとしてマークし、完了した項目を削除することができます。しかし本稿を終える前に、このアプリケーションを運用するうえで重要な意味を持つ機能について触れておきたいと思います。その機能とはデータベースのバックアップです。

　SQLiteデータベースをバックアップするのは簡単です。このデータベースは1つのディスクファイルに格納されているからです。最も簡単なバックアップ方法は、このディスクファイルをバックアップメディアにコピーして、安全な場所に保管することです。しかしその他に、SQLiteの組み込みの.dumpコマンドを使用するという方法もあります。このコマンドを実行すると、対象データベースを一から再生成するのに必要なCREATE TABLEコマンドとINSERTコマンドを含んだファイルを生成できます。.dumpコマンドを単独で実行した場合の結果は次のとおりです。

sqlite> .dump
BEGIN TRANSACTION;
CREATE TABLE items (
  id integer not null primary key,
  name text not null,
  due integer not null,
  complete integer null,
  priority integer not null,
  status integer not null
);
INSERT INTO "items" VALUES(1,
  'Buy anniversary present',1184437800,NULL,4,1);
INSERT INTO "items" VALUES(2,
  'Send invoices',1180549800,NULL,5,1);
INSERT INTO "items" VALUES(3,
  'Finish homework',1180549800,NULL,1,0);
INSERT INTO "items" VALUES(4,
  'Visit Jane',1180290600,NULL,2,1);
INSERT INTO "items" VALUES(5,
  'Finalize hotel reservations',1181737000,NULL,5,1);
INSERT INTO "items" VALUES(6,
  'Angie''s birthday',1182537000,NULL,4,1);
INSERT INTO "items" VALUES(7,
  'Weed the lawn',1182237000,NULL,1,1);
COMMIT;

　このようにダンプ結果を画面に出力してもあまり意味がないので、ダンプ結果をファイルに出力するようにします。そのためには、.dumpコマンドの前に、.outputコマンドにファイル名を指定して実行します。

sqlite> .output todo.sql
sqlite> .dump
sqlite> .output stdout

　このバックアップファイルからデータベースを復元することは簡単にできます。SQLiteの.readコマンドにバックアップファイルの名前を渡して実行すればよいのです。これにより、ファイル内のSQLが自動的に読み込まれて実行され、データベースが再作成されます。

sqlite> .read todo.sql

　ここまで見てきたように、軽量データベースはWebアプリケーションで十分に利用できる段階まで来ており、各種のプラットフォーム、言語、機能セットをサポートする軽量データベースも登場しています。今度、データベース機能が必要だが大規模なデータベースを導入するほどのコストやリソースはないという状況に遭遇したら、ぜひ本稿で紹介したいずれかの軽量データベースを試してみてください。

著者紹介

Vikram Vaswani（Vikram Vaswani）