[memovie] データベース設計 その2

[memovie] データベース設計 その2

http://farm1.static.flickr.com/8/12286703_f6de414bf6.jpg
写真で見る「いき」 on Flickr - Photo Sharing!

少し間が空いてしまいましたが、今回もデータベース周りをいじっていきます。

主キー

まずは主キーについて。

create table movie (
id integer primary key,
title text,
director text,
actor text
);

primary key = 主キー、です。
個々のレコードを一意に特定するために使います。そのため重複のない数（あるいは文字列）を各レコードに割り当てる必要があります。主キーがなくても動きますが、たいていはあった方が高速にレコードを参照できて便利でしょう。

SQLiteにPHPからつなぐ

PHP5を使います。
データベースへの接続は、SQLiteやMySQLに依存しない（抽象度の高い）コードにした方が楽です。そのために、PDOなどを使って接続します。PEARのDBクラスもかつてはよく使われました。

PDOは十分に高速ですし機能も揃っていますが、若干難しい点があるので今回はあえてSQLite専用のコードを書いてみます。（具体的には、PDOはtry/catchの利用が必須でこれを使いこなすには長い説明が多くなってしまいそうなのでやめました）

さて、SQLiteのマニュアルは以下です。
PHP: SQLite - Manual

sqlite_openにあるサンプルが短くてわかりやすかったので拝借＆改良しました。

<?php
$db = sqlite_open('mysqlitedb', 0666, $sqliteerror);
if (!$db) {
die($sqliteerror);
}
sqlite_query($db, 'CREATE TABLE foo (bar varchar(10))');
sqlite_query($db, "INSERT INTO foo VALUES ('fnord')");
$result = sqlite_query($db, "SELECT bar FROM foo");
print_r(sqlite_fetch_array($result));

実行すると何か表示されるでしょうか。
2回目に実行すると

Warning: sqlite_query(): table foo already exists

と怒られると思います。
テーブルは一度作るとそのまま存在し続けるのでエラーになるわけです。

CREATE TABLEはPHPの中ではなく、前回やったようにコマンドライン上で実行しておくのが普通です。

SQLの一部が大文字で書かれていますが、これは見やすくするため。小文字でも動きます。

fopenなどと同じようにsqlite_openでDB（1つのファイルになっています）を開いて、sqlite_queryでSQLを実行。sqlite_fetch_arrayを使ってSELECTした結果を配列で取得しています。

1つ、PHP上でSQLを実行するときの特徴として、行末にセミコロンが不要、というのがあります。これはPHPからは1行分のSQLしか送信されない、という前提があるためです。試しにセミコロンで区切って2行分実行しようとしても最初の1つしか実行されません。

sqlite_query($db, "insert into foo values ('fnord'); insert into foo values('hogehoge');");

また、注意点としてPHPに組み込まれているSQLiteとコマンドラインで実行しようとしているSQLiteのバージョンが2と3で違うと

SQL error: file is encrypted or is not a database

といったエラーが出る場合があります。その場合はsqlite_libversion()で確認しつつ、コマンドライン上のSQLiteのバージョンを下げるのが手っ取り早いと思います。

主キーのありがたみ

先ほどのcreate table movieを実行したmemovieというDBがあるとします。

C:\sqlite>sqlite3.exe movies
sqlite> create table movie (
id integer primary key,
title text,
director text,
actor text
);

これに大量にデータを入れてみます。

<?php
$db = sqlite_open('memovie', 0666, $sqliteerror);
if (!$db) {
die($sqliteerror);
}
$max = 1000;
for ($i = 0; $i < $max; ++$i) {
sqlite_query($db, sprintf("insert into movie (id, title, director, actor) values (%d, '%s', '%s', '%s')", $i, uniqid(), uniqid(), uniqid()));
}
$result = sqlite_query($db, 'select count(id) from movie');
print_r(sqlite_fetch_array($result));

uniqid()はランダムな文字列を出力してくれます。

続いてランダムに主キーが0〜999までのレコードを取得してみます。SELECTの後ろにWHEREを付けると条件になります。

<?php
$db = sqlite_open('memovie', 0666, $sqliteerror);
if (!$db) {
die($sqliteerror);
}
$max = 1000;
$result = sqlite_query($db, sprintf("select * from movie where id = %d", rand(0, $max-1)));
print_r(sqlite_fetch_array($result));

一瞬で出力されると思います。
試しに$maxを10000にしてやってみましたが、この程度だとCREATE TABLE時にprimary keyを指定していてもいなくても、変わりがありませんでした。。もっと数が増えれば差が出てくるとは思います。

なお、正確には主キーに指定することでデータベースが内部で「インデックス」を張っているためWHEREで指定されたときに高速に参照できるようになっています。

まとめ

若干、ややこしくなってきていると思いますが、次回もデータベース関係で基本的なところを書いてみます。