HAL v1.2.0 から、データベースを簡単に操作するためのライブラリ「DataMapper Lite(bata版)」が搭載されました。

DataMapper Lite を利用すれば、難しい SQL 文をほとんど覚える必要なく、簡単にデータの出し入れができるようになります。

※この記事で紹介しているソケットサーバー『HAL』は、諸般の事情により現在、公開と販売を中止しています。この製品へのお問い合わせは画面上部の『お問い合わせ』メニューよりご連絡ください。

※この記事は執筆・公開から3年以上経過しています。記事の情報が古くなっている場合がありますのでご注意ください。

公開日時:2016/07/10 05:45
最終更新:2019/12/10 05:13

DBアクセスライブラリ「DataMapper Lite」

HAL v1.2.0 から、データベースを簡単に操作するためのライブラリ「DataMapper Lite(RC版)」が搭載されました。

DataMapper Lite を利用すれば、難しい SQL 文をほとんど覚える必要なく、簡単にデータの出し入れができるようになります。

DB 設定の定義

データベースの利用にあたっては、データベースの選択と導入を参考にデータベースの選定を行ってください。DataMapper Lite は、MySQL、PostgreSQL、SQLite の3データベースに対応しており、基本的にはどのデータベースでも殆ど同じプログラムコードが動作します。

データベースの選定が終わったら、ユーザーアカウントの作成と、利用するスキーマ(データベース)を作成して下さい。

アカウントとスキーマの作成が終わったら、

/HAL/setting/DBSetting.php

に、情報を記述して下さい。DBSetting.php には各データベースの設定の雛形が既に記述してありますので、getDB()メソッド内の必要な情報を書き換えて下さい。

class DBSetting
{
    public static function getDB()
    {
        $db = self::getDBMySQL();       // MySQLを使う場合
        //$db = self::getDBPgSQL();     // PostgreSQLを使う場合
        //$db = self::getDBSQLite();    // SQLiteを使う場合

        return $db;
    }
    …
}

上記のように、getDB() メソッドで利用するデータベースのコメントをアンコメントして下さい。

なお、PostgreSQL を選択された方は、データベース内に log スキーマを作成して下さい。postgres ユーザーでログインし、データベースを選択してからスキーマを作成、ユーザーに権限を与えます。

su - postgres

\c feijoa

CREATE SCHEMA log;

ALTER SCHEMA log OWNER TO feijoa;

bakepi コマンドの作成

テーブルの作成は bakepi.php を用いて行います。まず、bakepi コマンドを作成するため、sh ファイルを作成しましょう。場所は $PATH に示されているディレクトリであればどこでも構いませんが、

/usr/local/bin

ディレクトリ内を推奨しています。次のコマンドで、新規ファイルを作成します。

sudo vi /usr/local/bin/bakepi

以下のように、HAL の bakepi.php を実行するスクリプトを書きます。

#!/bin/sh

sudo php -q /var/www/HAL/system/bakepi.php $1 $2 $3

ファイルを保存したら、パーミッションを管理者のみ実行できるように設定しましょう。

sudo chmod 700 /usr/local/bin/bakepi

これで、bakepi コマンドが使えるようになりました。

テーブル定義の記述

テーブルを作成するにはまず、テーブルの定義を記述します。

/HAL/db/tabledefinitions ディレクトリ内にある Test.yml を見てみましょう。

## YAML Template.
---
columns:
    id:
        type:       "INT"
        collation: "utf8mb4_general_ci"
        allownull:  false
        default:    null
        ai:         true
        extra:      null
        other:      null
        index:      null
        comment:    "ID"
    idx:
   :(ここから idx カラムの定義は省略)

primary:    null
unique:
    -
        - "col1"
        - "col2"
index:
    - "name"
    - "col3"
fulltext:   null
check:      null
foreign:    null
table_comment:  "テストテーブル"

このような定義が行われています。これは YAML という形式の定義ファイルです。

参考)YAMLとは何か? - いつもRailsの設定ファイルで出てくるやつの正体

上記 id のようなカラム設定は

プロパティ名: 値

のように1対1で指定しますが、primary、unique、index、fulltext については1対多になるケースがあります。これらは制約と呼ばれる、テーブル利用にあたってのルールです。

参考)主キー 【 primary key 】 プライマリキー / PK

例えば、id と idx のセットで複合プライマリキーを設定したい場合の記述は次のようになります。

primary:
    - id
    - idx

これは、YAML における配列の表記です。なお、カラムの ai: を ture にすると、そのカラムは AUTO INCREMENT な PRIMARY KEY となります。

また、unique 制約を複数設定したい時は

unique:
    -
        - col1
        - col2
    -
        - col3

のようにすると、 [col1, col2] [col3] という2つの unique 制約を設定できます。PRIMARY KEY についても同様に複数の複合キーを記述できますが、設定されるのは最初の1つだけです。このため、最初に設定された ai: true なカラムが PRIMARY KEY として設定され、primary: の設定は無視される事になります。

なお、これら制約のキー名はそれぞれ、p、u、i、t、c、f を冠し、テーブル名と連番をアンダースコアで繋いだ名前が自動で割り振られます。例えば、u_tests_1、u_tests_2 といった具合です。(ただし、現在の bakepi.php では CHECK 制約と FOREIGN KEY 制約には対応していません。これらはそれぞれのデータベースにログインし、手動で設定して下さい)

YAML ファイルの名前は、これから作成するテーブルに入れるデータを示した物で、アッパーキャメルケースを用いた単数形にしてください。

例えば ユーザー情報のテーブルである場合は、User.yml 等となります。この YAML を用いて自動生成されるテーブル名は users となります。UserAccount.yml の場合は user_accounts となります。このように、bakepi では複数形のテーブル名を自動生成します。Diary.yml の場合は、diaries テーブルとなります。

ただし、現在のバージョンでは単複同形の単語なども機械的に複数形で作成します。例えば fish は単複同形ですが、Fish.yml から作成されるテーブル名は fishs です。

テーブルとデータモデルの自動生成

テーブル定義の YAML ファイルを作成したら bakepi コマンドで生成を行います。

まず、PHP で YAML を扱えるように apt-get でライブラリを導入します。

sudo apt-get install php-pear libyaml-dev

sudo pecl install yaml

恐らく、「php.ini に変更が加えられている」という警告が出ると思いますので、変更を保持するを選んで下さい。ライブラリのインストールが終わったら、.ini ファイルを作成し、CLI と Apache モジュール用にシンボリックリンクを生成します。

sudo sh -c "echo 'extension=yaml.so' >> /etc/php5/mods-available/yaml.ini"

sudo php5enmod yaml

最後に、変更を適用するため apache を再起動して下さい。

sudo service apache2 restart

参考)Install PHP Extension YAML

これで、PHP でも YAML を扱えるようになりました。

では、bakepi コマンドを実行してテーブルを生成してみましょう。例えばサンプルの Test.yml からテーブルを作成する場合は、

sudo bakepi -d Test true

です。オプションの -d は現在必須です。Test を指定した場合に作成されるテーブル名は tests ですが、最後の引数に true を指定すると、既に tests テーブルが存在した場合、既存テーブルを削除(DROP TABLE)してから新たにテーブルを作成します。この引数は省略可能で、デフォルトは false なため、既存テーブルが存在した場合にはエラーとなって処理を停止します。

また、テーブル作成と同時にデータモデル(DataModel)も生成します。場所は、/HAL/db/datamodels 内に、指定した YAML のファイル名.php として作成されます。中身は YAML のファイル名 のクラスです。

一括処理

なお、

sudo bakepi -d all true

のように、全て小文字の all を指定すると、/HAL/db/tabledefinitions ディレクトリ内にある全ての YAML ファイルが処理されます。もし、All という名の DataModel を作成したい時は All のようにアッパー・キャメル・ケース を用いれば All.yml だけが処理されます。

bakepi コマンドは、全て小文字でもアッパー・キャメル・ケースに自動変換して処理を行うので、-d オプションで指定する yml ファイル名は大文字で始まらなくてもかまいませんが、普段からアッパー・キャメル・ケースで記述するクセをつけておいたほうが良いかと思います。

レコードの INSERT

DataMapper Lite での INSERT はとても簡単です。ここでは、Test.yml から生成された tests テーブルと Test データモデルクラス(Test.php)を例に説明します。

まず、Test クラスのインスタンスを生成し、登録したいレコードの情報を定義します。

$dm = new Test();
$dm->idx = 1;
$dm->col1 = "テストレコード1";
$dm->col2 = "テストレコード2";

テーブルのカラムに NOT NULL 制約が設定されていない限りは、必ずしも全ての項目を設定する必要はありません。ただし、PRIMARY KEY のカラムは AUTO INCREMENT でないかぎり、当然、必ず指定が必要です。

このようにデータモデルのインスタンスに値を設定したら、

DataMapper::getInstance()->setUp("Test")->insert($dm)->execute();

で、レコードが登録されます。最初の

DataMapper::getInstance()

は、DataMapper のインスタンスを取得しています。DataMapper はシングルトンモデルになっていて、何度インスタンスを取得しても生成されるのは最初の1回だけで、以降は同じインスタンスが使いまわされます。

setUp("データモデル名")

で、使用するデータモデルとテーブル名を設定しています。

insert($dm)

で、登録する情報を設定します。なお、$dm は単一のデータモデルでも良いですし、複数のデータモデルを格納した単純配列でも構いません。ただし、配列で渡す場合のデータモデルへの値の設定は、全て同じカラムに対して行って下さい。これは、配列の最初のデータモデルを検査して、それに合った SQL 文を DataMapper が準備(prepare)し、配列の全てのデータモデルインスタンスについてループ処理するからです。

最後に execute() を実行して下さい。execute() を実行するまではクエリは実行されず、SQL がビルドされ続けます。

このため、execute() を実行する前に getQuery() メソッドを実行すると、生成されている SQL クエリの内容を確認できます。

echo DataMapper::getInstance()->setUp("Test")->insert($dm)->getQuery();

これは、デバッグの際に参考になるでしょう。

execute() が正常終了した場合、DataMapper内に生成されていたクエリは破棄されて様々な情報がクリーンナップされます。

ただし、execute() を実行し正常終了した後で、getLastQuery() を実行することで、直前に実行された SQL クエリを確認することができます。

echo DataMapper::getInstance()->getLastQuery();

SELECT

DataMapper Lite での SELECT は少し不便です。なぜなら、Lite 版 では、直接 SQL 文を書かなければいけないからです。ここでは、Test.yml から生成された tests テーブルと Test クラス(Test.php)を例に説明します。

$qry = <<< EOL
    SELECT
        *
    FROM
        tests
    WHERE
        idx = :idx
    ;
EOL;

$params = array("idx" => 1);
$result = DataMapper::getInstance()->setUp("Test")->query($qry)->bind($params)->execute();

このようになります。:idx は名前付きプレースホルダです。bind() に渡す配列で、実際にどのような値を SQL クエリに当てはめるのかを指定します。DataMapper Lite では、全ての SQL クエリについて、名前付きプレースホルダを使ったプリペアが行われます。SQL インジェクションを特別意識すること無く回避するための機能であり、これは、INSERT や UPDATE、DELETE でも同様です。

ですから、query() を実行する場合は必ず名前付きプレースホルダを用い、bind() を行って下さい。

なお、プレースホルダの数とバインドするパラメターの数は同じでなければなりません。例えば、同じ値を設定するからといって

$qry = "SELECT * FROM tests WHERE col1 = :val AND col2 = :val;";
$params = array("val" => "test");

のようにすると、数が不一致の為エラーになります。必ず以下のように数を合わせてください。

$qry = "SELECT * FROM tests WHERE col1 = :val1 AND col2 = :val2;";
$params = array("val1" => "test", "val2" => "test");

SELECT 文の結果は、setUp() で指定したデータモデルに fetch されます。DataMapper Lite のデータモデルは型付きであり、DataModel で定義されている型にパースされます。どのような型が利用できるかは、

参考)定義済み定数|PDO

を参照して下さい。

ただし、loose() を実行すると、データモデルではなく PHP の stdClass オブジェクトとして fetch されます。この場合、全ての値は string 型となります。

$result = DataMapper::getInstance()->setUp("Test")->loose()->query($qry)->bind($params)->execute();

なお、いずれの場合でも SQL クエリが正常実行された場合には、SELECT 結果(上記の場合 $result)は配列として返ります。SELECT 結果が1件だけだとしても、データモデルまたは stdClass オブジェクト1つだけを含んだ配列となります。

UPDATE と DELETE

DataMapper Lite での UPDATE と DELETE はとても簡単です。ここでは、Test.yml から生成された tests テーブルと Test クラス(Test.php)を例に説明します。

まず、Test クラスのインスタンスを生成し、更新したいレコードの情報を定義します。

$dm = new Test();
$dm->id = $result[0]->id;
$dm->col1 = "修正テストレコード1";
$dm->update_time = $result[0]->update_time;
DataMapper::getInstance()->setUp("Test")->update($dm)->execute();
$dm = new Test();
$dm->id = $result[0]->id;
$dm->update_time = $result[0]->update_time;
DataMapper::getInstance()->setUp("Test")->delete($dm, true)->execute();

update() と delete() メソッドにおいて、PRIMARY KEY のカラムは必須です。また、この2つのメソッドでは楽観排他が強制されています。ですから、基準カラムである update_time は必ず設定が必要です。

参考)楽観的並行性制御

delete() メソッドの第二引数の boolean 値は、物理削除か論理削除かのフラグです。true を指定した場合、レコードは物理削除されテーブルから無くなります。false を指定した場合は lg_del カラムが 1 に設定されます。これは delete() でありながら、内部的に update() が実行されています。この第二引数のデフォルトは false(論理削除)で、この値は省略できます。

update() および delete() の第一引数に指定する $dm は単一のデータモデルでも良いですし、複数のデータモデルを格納した単純配列でも構いません。ただし、配列で渡す場合のデータモデルへの値の設定は、全て同じカラムに対して行って下さい。これは、配列の最初のデータモデルを検査して、それに合った SQL 文を DataMapper が準備(prepare)するからです。

WHERE を使った UPDATE と DELETE

DataMapper Lite では、WHERE句による UPDATE と DELETE も可能です。ここでは、Test.yml から生成された tests テーブルと Test クラス(Test.php)を例に説明します。

まず、Test クラスのインスタンスを生成し、更新したいレコードの情報を定義し、Where オブジェクトで条件を指定します。どのような条件が使えるかは、当サイト製品である Feijoa Prototype Framework の Whereオブジェクトを参考にして下さい。ただし、本製品は Lite 版であるため、Select クラスを含んでいません。このため、EXISTS や SELECT 結果を使った IN などは利用できません。今後リリースされる予定の正規版である DataMapper Pro をご利用ください。

$dm = new Test();
$dm->col1 = "修正テストレコード1";

$where = new Where(["idx" => 1]);

DataMapper::getInstance()->setUp("Test")->updateByWhere($dm, $where)->execute();
$where = new Where(["idx" => 1]);

DataMapper::getInstance()->setUp("Test")->deleteByWhere($where, true)->execute();

updateByWhere() および deleteByWhere() においては PRIMARY KEY は必ずしも必須ではありませんし、楽観排他も行われません。このため、簡単に更新や削除ができますが、WHERE句に該当する全てのレコードに影響が出るので、本当に実行して良いのかをよく確認してください。

なお、updateByWhere() には、データモデルの配列は渡せません。かならず単一のデータモデルにしてください。

レコード更新時のロギング

DataMapper Lite ではデフォルトで UPDATE と DELETE のログを記録します(ByWhere の場合も含む)。PostgreSQL では、デフォルトでは通常テーブルを public スキーマに、ログテーブルを log スキーマに作成します。MySQL と SQLite ではスキーマ=データベースですので、同じスキーマ内に通常テーブルと zzz_ というプレフィックスを持ったログテーブルが作成されます。

ロギングを行わない場合は、

/HAL/setting/DBSetting.php

の使用するデータベース定義メソッドの中で、

$db->logging = false;

を指定して下さい。

補足事項

DataMapper Lite の導入にあたり、

に修正が加えられ

が追加されました。

HAL v1.1 以前をご利用の方は修正内容を確認して下さい。


【改訂第3版】 SQLポケットリファレンス (POCKET REFERENCE) (技術評論社)

記事リンク