※この記事は専門学校の講義用に作成されたものです
前回に引き続きPHPからMySQLを操作します。今回のテーマは「トランザクション」への対応です。トランザクションをマスターすれば、2つ以上のSQLを実行した際にいずれかの実行が失敗した場合でも、そのすべてを無かったことにできちゃいます。
データベースは堅牢に作ってあるとは言え、いつ障害など不測の事態が発生するか分かりません。データベースはユーザーの個人情報やお金など非常に重要なデータを扱うこともありますからうまく動作しなかった場合には元の健全な状態に戻して上げる必要があるというわけです。つまり、めちゃめちゃ重要なのです←語彙ｗ
今回作るもの
前回はソーシャルゲームのユーザーを管理するテーブルをイメージして作成しました。今回はその続きとして「ガチャ」を引くRESTful APIの作成をゴールとしたいと思います。
処理の流れはざっくりと次の通りです。
データベースの準備
ER図
まずは今回作成するテーブルを定義したのが以下の図です。このようにテーブルの関係性を示したこの図を「ER図(Entity-relationship Diagram)」と呼びます。日本語でいうと「実体関連図」となります。
ER図でもう一つ注目すべきはテーブル間を結んでいる線の両端にある記号です。
向かって左側にあるマークがある方が「1」、向かって右側にあるマークが「多(n)」を表しています。例えばUserテーブルとUserCharaテーブルで言えば、今回は1人が複数のキャラクターを所持できるわけですから、Userテーブルが1レコードあった際にそれと関連するUserCharaテーブルのレコードは1つ以上存在する可能性があるということになります。
「UserテーブルとUserCharaテーブルは、1対多(1:n)の関係である」という具合に会話の中で使います。
テーブルを追加
では先ほどER図で示したテーブルを作成しましょう。
前回作成したソーシャルゲーム用のデータベースにsrpgにCharaとUserCharaテーブルを追加で作成します。SQLは以下です。これを「sgrpg2.sql」という名前で保存してます。
/*---------------------------*/ /* データベースを新規作成 */ /*---------------------------*/ CREATE DATABASE IF NOT EXISTS sgrpg; USE sgrpg; /*---------------------------*/ /* テーブルを作成 */ /*---------------------------*/ -- キャラクター マスター CREATE TABLE Chara( id integer AUTO_INCREMENT, name varchar(64), PRIMARY KEY(id) ); -- ユーザーが所持しているキャラクター CREATE TABLE UserChara( id integer AUTO_INCREMENT, user_id integer, chara_id integer, PRIMARY KEY(id) );
SQLをTerminalから実行
MySQLのクライアント経由で先ほどのSQLを実行します。ここではMySQLにログインするためのユーザーIDはsenpaiとしています。エラーメッセージなどが表示されなければ正常に終了しています。
$ mysql -u senpai -p < sgrpg2.sql Enter password:
SQLが正常に実行されているか確認
ではMySQLにログインし本当に作成が成功しているか確認します。いつものようにshow databasesでデータベースの一覧を表示します。ここはまぁ変化はないハズです。
$ mysql -u senpai -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 28 Server version: 5.7.27-log MySQL Community Server (GPL) (中略) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sgrpg | | sys | +--------------------+ 5 rows in set (0.00 sec)
続けてテーブルの一覧をshow tablesで確認します。Chara, User, UserCharaの3つが存在していれば成功です。
mysql> use sgrpg; Database changed mysql> show tables; +-----------------+ | Tables_in_sgrpg | +-----------------+ | Chara | | User | | UserChara | +-----------------+ 3 rows in set (0.01 sec)
それぞれdesc (テーブル名)でテーブルの構造を確認します。
mysql> desc User; +-------+---------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | lv | int(11) | YES | | NULL | | | exp | int(11) | YES | | NULL | | | money | int(11) | YES | | NULL | | +-------+---------+------+-----+---------+----------------+ 4 rows in set (0.00 sec) mysql> desc Chara; +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(64) | YES | | NULL | | +-------+-------------+------+-----+---------+----------------+ 2 rows in set (0.01 sec) mysql> desc UserChara; +----------+---------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+---------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | user_id | int(11) | YES | | NULL | | | chara_id | int(11) | YES | | NULL | | +----------+---------+------+-----+---------+----------------+ 3 rows in set (0.00 sec)
Userテーブルの初期値
また初期値としてUserテーブルには次のレコードが挿入されています。
mysql> select * from User; +----+------+----------+--------+ | id | lv | exp | money | +----+------+----------+--------+ | 1 | 1 | 1 | 3000 | | 2 | 20 | 23456 | 80000 | | 3 | 99 | 99999999 | 120000 | +----+------+----------+--------+ 3 rows in set (0.01 sec)
初期データを挿入
キャラクターのマスターデータを挿入します。Chara.idはAUTO_INCREMENTにしているので指定しません。
INSERT INTO Chara(name) VALUES ("メジェド"), ("ファーブニル"), ("トール"), ("イエティ"), ("エルルーン"), ("ユグドラ"), ("オーディン"), ("クレイゴーレム"), ("ルトラ"), ("アーサー");
INSERTが成功したか必ず確認しておきます。
mysql> select * from Chara; +----+-----------------------+ | id | name | +----+-----------------------+ | 1 | メジェド | | 2 | ファーブニル | | 3 | トール | | 4 | イエティ | | 5 | エルルーン | | 6 | ユグドラ | | 7 | オーディン | | 8 | クレイゴーレム | | 9 | ルトラ | | 10 | アーサー | +----+-----------------------+ 10 rows in set (0.00 sec)
PDOでトランザクション
データベースの準備が終わったら、APIを作り始める前にトランザクションについて脳内を整理しておきます。
トランザクションとは？
例えば以下のようにクマさんの銀行口座にある100万円をペンギンさんの口座に振り込むとします。クマさんの口座から100万円を減算する処理は成功したのですが、ペンギンさんの口座に100万円を加算していた際に障害が発生してしました。このまま処理を終えてしまうと100万円がどこかに消えて無くなってしまいます。地獄！
そこで利用するのがトランザクション。データを更新するSQLを実行する前にSTART TRANSACTIONしておきます。これはゲームで言うならセーブポイント。もし先ほどと同じように障害が発生したらこのセーブポイントまでデータの状態を戻すことができるのです。戻すときにはROLLBACKと哀愁を漂わせながら叫びます。
注意点としてはトランザクションを張った際には、成功した場合にも叫ぶ必要があります。これを忘れてプログラムを終了するとトランザクション中の処理が設定によってはすべて無かったことになる場合があります。無事に終了した際に叫ぶ必殺技の名前はCOMMITです。決めポーズを取りながら実行しましょう。
PDOでトランザクションを使うには？
ここまでの説明は長かったのですが、実際に使うのは非常に簡単です。
try{
// 接続
$dbh = new PDO($dsn, $user, $pw);
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// トランザクション開始
$dbh->beginTransaction();
// SQL実行
$sth = $dbh->prepare($sql); // SQL準備
$sth->execute();
// コミット
$dbh->commit();
}
catch( PDOException $e ) {
// ロールバック
$dbh->rollBack();
printf("[Error] %s¥n", $e->getMessage());
}
$dbh->beginTransaction()でトランザクション開始、SQLの実行が成功したら$dbh->commit()で確定します。もし失敗したら例外が発生しますのでcatchで$dbh->rollBack()すればトランザクションを開始したところまで巻き戻すことができます。
ガチャAPIを作成する
では冒頭の予告通りガチャAPIを作成します。以下のソースコードをWebサーバから見える場所(ドキュメントルート)に保存します。
ソースコード (PHP)
<?php /** * ガチャAPI * */ // 以下のコメントを外すと実行時エラーが発生した際にエラー内容が表示される // ini_set('display_errors', 'On'); // ini_set('error_reporting', E_ALL); //------------------------------------------------- // 定数 //------------------------------------------------- // キャラクター数 define('MAX_CHARA', 10); // ガチゃ1回の価格 define('GACHA_PRICE', 300); //------------------------------------------------- // 引数を受け取る //------------------------------------------------- // ユーザーIDを受け取る $uid = isset($_GET['uid'])? $_GET['uid']:null; // Validation if( ($uid === null) || (!is_numeric($uid)) ){ sendResponse(false, 'Invalid uid'); exit(1); } //------------------------------------------------- // 準備 //------------------------------------------------- $dsn = 'mysql:dbname=sgrpg;host=127.0.0.1'; // 接続先を定義 $user = 'senpai'; // MySQLのユーザーID $pw = 'indocurry'; // MySQLのパスワード //--------------------------- // 実行したいSQL //--------------------------- // Userテーブルから所持金を取得 $sql1 = 'SELECT money FROM User WHERE id=:userid'; // Userテーブルの所持金を減産 $sql2 = 'UPDATE User SET money=money-:price WHERE id=:userid'; // UserCharaテーブルにキャラクターを追加 $sql3 = 'INSERT INTO UserChara(user_id, chara_id) VALUES(:userid,:charaid)'; // Charaテーブルから1レコード取得 $sql4 = 'SELECT * FROM Chara WHERE id=:charaid'; //------------------------------------------------- // SQLを実行 //------------------------------------------------- try{ $dbh = new PDO($dsn, $user, $pw); // 接続 $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // エラーモード // トランザクション開始 $dbh->beginTransaction(); //--------------------------- // 所持金の残高を取得 //--------------------------- $sth = $dbh->prepare($sql1); $sth->bindValue(':userid', $uid, PDO::PARAM_INT); $sth->execute(); $buff = $sth->fetch(PDO::FETCH_ASSOC); // ユーザーが存在しているかチェック if( $buff === false ){ sendResponse(false, 'Not Found User'); exit(1); } // 残高が足りているかチェック if( $buff['money'] < GACHA_PRICE ){ sendResponse(false, 'The balance is not enough'); exit(1); } //--------------------------- // 残高を減らす //--------------------------- $sth = $dbh->prepare($sql2); $sth->bindValue(':price', GACHA_PRICE, PDO::PARAM_INT); $sth->bindValue(':userid', $uid, PDO::PARAM_INT); $sth->execute(); //--------------------------- // キャラクターを抽選 //--------------------------- $charaid = random_int(1, MAX_CHARA); //--------------------------- // キャラクターを所有 //--------------------------- $sth = $dbh->prepare($sql3); $sth->bindValue(':userid', $uid, PDO::PARAM_INT); $sth->bindValue(':charaid', $charaid, PDO::PARAM_INT); $sth->execute(); //--------------------------- // キャラクター情報を取得 //--------------------------- $sth = $dbh->prepare($sql4); $sth->bindValue(':charaid', $charaid, PDO::PARAM_INT); $sth->execute(); $chara = $sth->fetch(PDO::FETCH_ASSOC); //--------------------------- // トランザクション確定 //--------------------------- $dbh->commit(); } catch( PDOException $e ) { // ロールバック $dbh->rollBack(); sendResponse(false, 'Database error: '.$e->getMessage()); // 本来エラーメッセージはサーバ内のログへ保存する(悪意のある人間にヒントを与えない) exit(1); } //------------------------------------------------- // 実行結果を返却 //------------------------------------------------- // データが0件 if( $buff === false ){ sendResponse(false, 'System Error'); } // データを正常に取得 else{ sendResponse(true, $chara); } /** * 実行結果をJSON形式で返却する * * @param boolean $status * @param array $value * @return void */ function sendResponse($status, $value=[]){ header('Content-type: application/json'); echo json_encode([ 'status' => $status, 'result' => $value ]); }
実行する
ブラウザからアクセスしてみましょう。
正常動作
次のようにCharaテーブルからランダムに1行返ってくればとりあえず動いていることが確認できますね
肝心なのはデータベースが更新されているかですね。 Userテーブルの残高が300減っているか確認してみます。最初のテーブルが実行前、次のテーブルが実行後です。idが1のユーザーのmoneyが無事に減っているのが確認できました。
mysql> select * from User; +----+------+----------+--------+ | id | lv | exp | money | +----+------+----------+--------+ | 1 | 1 | 1 | 3000 | | 2 | 20 | 23456 | 80000 | | 3 | 99 | 99999999 | 120000 | +----+------+----------+--------+ 3 rows in set (0.00 sec) mysql> select * from User; +----+------+----------+--------+ | id | lv | exp | money | +----+------+----------+--------+ | 1 | 1 | 1 | 2700 | | 2 | 20 | 23456 | 79700 | | 3 | 99 | 99999999 | 119700 | +----+------+----------+--------+ 3 rows in set (0.00 sec)
次にUserCharaにデータが追加されているかもチェックします。前者がAPI実行前、後者が実行後です。こちらも最初は空だったテーブルがAPIを実行することで1レコード追加されているのがわかります。
mysql> select * from UserChara; Empty set (0.00 sec) mysql> select * from UserChara; +----+---------+----------+ | id | user_id | chara_id | +----+---------+----------+ | 1 | 1 | 2 | +----+---------+----------+ 1 row in set (0.00 sec)
異常動作
せっかくなのでロールバックを発動してみましょう。
ここではSQLの構文エラーを利用します。処理の一番最後に実行するSQLを適当に変更します。以下ではテーブル名を存在しない名称にしました。
$sql4 = 'SELECT * FROM Chara11111 WHERE id=:charaid';
これをブラウザから実行すると正常に？実行時エラーを発生させることができました。
データベースの内容も見てみましょう。UserとUserCharaが更新されず最後に実行された状態のままであることが確認できたでしょうか。
mysql> select * from User; +----+------+----------+--------+ | id | lv | exp | money | +----+------+----------+--------+ | 1 | 1 | 1 | 2700 | | 2 | 20 | 23456 | 79700 | | 3 | 99 | 99999999 | 119700 | +----+------+----------+--------+ 3 rows in set (0.00 sec) mysql> select * from UserChara; +----+---------+----------+ | id | user_id | chara_id | +----+---------+----------+ | 1 | 1 | 2 | +----+---------+----------+ 1 row in set (0.00 sec)
試しに$dbh->rollBack();をコメントアウトし、データベースが巻き戻らなかったりデータの残骸が残ることも確認しておきましょう。
