Friction River Software - CakePHP5入門【WebAPI編⑫】データベース操作

Ａ子

ねぇ、管理ページ上での話なんだけどさ

データベースの「バックアップ」や「リストア」を手動で行えるようにしたいんだよね

Ｃ菜

「バックアップ」については、クーロンを使って自動的に行えば良いんじゃないでしょうか～？

まぁ、「リストア」については、それなりの知識（コマンドの知識）が無いとできませんけど～

Ａ子

そう、そこなのよ

誰でもブラウザ上で「リストア」できれば便利じゃない？
（「バックアップ」は、ついでだけどね）

Ｂ美

あー、管理ページを階層化して、さらに複数のページを作るの？
（面倒くさい…）

Ｃ菜

トップページでやったようにタブ切り替えにしたらどうでしょう～？

Ａ子

お、それ良いね

んじゃ、「Excelインポート」「データベースバックアップ」「データベースリストア」の三つのタブを作ろう

Ｃ菜

ちゃちゃっと作ってみました～

こんな感じでいかがでしょうか～？

Ｂ美

さすがはＣ菜ね
（HTMLとCSSの知識については、私よりも上じゃないかしら…）

Ｃ菜

問題はここからです～

「バックアップ」はボタンだけあれば良いんですよね～？
「リストア」については、（「Excelインポート」と同様に）ファイル選択のフォームを作る形ですか～？

Ｂ美

そうね

「バックアップ」のほうはフォーム不要よ
（リンクをボタンとして表示するだけで良いかな）

「リストア」についてもＣ菜の考えでOKだから…

Ａ子

よし！
それじゃ、やってみよう

まずは「バックアップ」からね

Ｃ菜

タブ切り替えの箇所は省略するとして～
ボタンについては、こんな感じで記述してみました～

<?= $this->Html->link(__('バックアップ実行'), ['action' => 'backup'], ['class' => 'button is-link', 'style' => 'margin: 20px;']) ?>

Ａ子

ついでに「リストア」の画面も作ったよ
（「Excelインポート」の記述をコピペするだけだし…）

<?= $this->Form->create(null, ['id' => 'restore_form', 'type' => 'file', 'url' => ['action' => 'restore']]) ?>
<?= $this->Form->file('upload2', ['id' => 'upload2', 'label' => false, 'div' => false, 'accept' => '.sql']) ?>
<?= $this->Form->button(__('リストア実行'), ['type' => 'submit', 'id' => 'submit_btn2', 'class' => 'button is-link']) ?>
<?= $this->Form->end() ?>

Ｃ菜

「src/Controller/AdminController.php」の中に「backup」メソッドと「restore」メソッドを作りますね～

Ｂ美

さて、あなたたち

データベースのバックアップコマンドって憶えてる？

Ａ子

「mysqldump」だよね？

Ｃ菜

もっと正確に言えば、こうです～

mysqldump -u root -p （データベース名） > （ファイル名）[Enter]

（【CakePHP5応用編⑩】を参照）

Ａ子

もしかしてこれをプログラムで実行するの？

あれ？
でもこのあとパスワード入力があるよね？

Ｂ美

実はコマンドの中に「パスワード」を含めることもできるのよ

mysqldump -u root -p（パスワード）（データベース名） > （ファイル名）[Enter]

Ａ子

それってさー
もしもパスワードが「abc123」でデータベース名が「numbersdb」、ファイル名を「numbers_backup.sql」としたとき

mysqldump -u root -p abc123 numbersdb > numbers_backup.sql

…ってことで良いの？

Ｂ美

それではダメよ

Ｃ菜

あっ、もしかしてこうですか～？

mysqldump -u root -pabc123 numbersdb > numbers_backup.sql

Ｂ美

さすがはＣ菜ね

それが正解よ
（「-p」のあとに空白文字を入れちゃいけないの）

なお、プログラム中に記述する場合、こう書くことが多いかな
（さっきのは省略形で、こっちのほうが正式な書き方なんだけどね…ダブルクォートは省略可）

mysqldump --user="root" --password="abc123" numbersdb

あと、出力先であるファイル名はコマンド内には書かず、header関数を使って指定するの

Ａ子

「MATE端末」上で手入力するならいつものやり方（省略形）のほうが良いけど、分かりやすいのはあとのほうだね

Ｂ美

私も同意見よ

さて、PHPプログラムの中からシェルコマンドを実行するには二つの方法があるの
一つが「shell_exec」、もう一つが「exec」関数よ

Ｃ菜

どちらを使えば良いんでしょうか～？

Ｂ美

違いはこんな感じね

関数	引数	戻り値	用途
shell_exec	１個（コマンド文字列のみ）	コマンドの実行結果（末尾に必ず改行が入る）（エラーの場合は「null」）	簡単なコマンド実行
exec	１～３個・コマンド文字列・戻り値を格納する配列・ステータスコードを格納する変数（配列と変数はオプション）	コマンド文字列のみを渡した場合は実行結果（エラーの場合は「空文字列」）	実行結果の詳細な制御・配列には複数行の結果が一行ずつ入る・変数にはステータスコードが入る

まぁ、基本的には「shell_exec」関数を使えば良いってこと
（簡単だからね）

Ａ子

なるほどね

あ、「ユーザ名」「パスワード」「データベース名」なんかは定数として定義しておくの？

Ｂ美

もちろんそれでも良いんだけど、「ConnectionManager」を使えば「データベース接続情報」の取得もできるわよ
（開発環境と本番環境でデータベースのパスワードが異なる場合もあるから、こっちのほうが便利かもね）

use Cake\Datasource\ConnectionManager;

のあと

$connection = ConnectionManager::get('default');
$dbconfig = $connection->config();
$host = $dbconfig['host'];
$user = $dbconfig['username'];
$password = $dbconfig['password'];
$db = $dbconfig['database'];
$charset = $dbconfig['encoding'];
$cmd = $dump." --host=".$host." --user=".$user." --password=".$password." --default-character-set=".$charset." ".$db;

…って感じかな
（「ConnectionManager」のuse文はすでにあるけどね…「import」メソッドで使ったから）

Ｃ菜

「host」や「encoding」の指定は必要なんでしょうか～？

「encoding」って文字コードのことですよね～？

Ｂ美

「--host=""」の指定って、省略すると「localhost」…つまり自分自身のことだから別に要らないかな

「--default-character-set=""」も私たちの環境では不要よ
（だって、文字コードは「UTF-8」で統一してるからね）

あえてここに記載した理由は、昔は「シフトJIS」や「EUC-JP」といった文字コードを使うことが多かったからね
（現在は、ほとんどのWebアプリが「UTF-8」になってるけど…）

Ａ子

あれ？
Windowsって「シフトJIS」じゃなかったっけ？

Ｂ美

正解！

だからWindows上で開発してる人は未だに「シフトJIS」を使っているかもしれない
（私たちはLinux上で開発してるから、デフォルトの文字コードが「UTF-8」になってるってわけ）

Ｃ菜

なるほどですねぇ

これで必要な情報は全て出そろった感じですか～？

Ｂ美

いいえ、あと一つあるわ

さっきのコードの中に「$dump」という変数があったわよね
あれって、次のコードを実行して取得しないといけないの

$dump = trim(exec("which mysqldump"));

Ｃ菜

あ～、分かりました～
「mysqldump」のフルパスですね～？

でも、なぜ「shell_exec」ではなく「exec」なんですか～？
あと、「trim」って～？

Ｂ美

これはまぁ、よくある落とし穴と言うか何というか…（苦笑）

「shell_exec」の戻り値って、末尾に必ず改行コードが入っちゃうのよね
（「exec」も環境によっては入る可能性があるから、念のためにtrimで削ってるの）

まぁ、実行環境が決まっている場合は、「which」せずに決め打ちしちゃっても良いわよ（苦笑）
（本番サーバの引っ越しによって他のディストリビューションに移行した場合、「mysqldump」の場所が変わる可能性もあるからこうしているだけ）

Ａ子

なるほどねぇ

あ、ファイル名ってどうする？
さっき例で出した「numbers_backup.sql」にする？

Ｃ菜

バージョン管理したいですね～

「年月日時分秒」をファイル名に含めるのはどうですか～？

Ａ子

お、良いね
んじゃ、こうかな？

$dumpfile = "numbers_backup_".date("Ymd_His").".sql";

Ｂ美

ふむ、問題ないわね
そのファイル名をダウンロード時のデフォルトとするためにはheader関数内にこう書くの

header ("Content-disposition: attachment; filename=" . $dumpfile);
header ("Content-type: application/octet-stream; name=" . $dumpfile);

んで最後に、「mysqldump」の実行結果をレスポンスするってわけ

$res = shell_exec($cmd);
return $this->response->withStringBody($res);

あ、ビューファイルへの移行はしないから「$this->autoRender = false;」を忘れないようにね

Ｃ菜

まとめてみました～

//データベースのバックアップ
public function backup()
{
    //mysqldumpのフルパスを取得
    $dump = trim(exec("which mysqldump"));

    if ($dump != "") {
        //データベース接続情報を取得
        $connection = ConnectionManager::get('default');
        $dbconfig = $connection->config();
        $host = $dbconfig['host'];
        $user = $dbconfig['username'];
        $password = $dbconfig['password'];
        $db = $dbconfig['database'];
        $charset = $dbconfig['encoding'];

        //コマンド文字列の生成と実行
        $cmd = $dump." --host=".$host." --user=".$user." --password=".$password." --default-character-set=".$charset." ".$db;
        $res = shell_exec($cmd);

        if (!is_null($res)) {
            //成功
            $this->autoRender = false;

            //ファイル名
            $dumpfile = "numbers_backup_".date("Ymd_His").".sql";

            header ("Content-disposition: attachment; filename=" . $dumpfile);
            header ("Content-type: application/octet-stream; name=" . $dumpfile);

            return $this->response->withStringBody($res);
        } else {
            //失敗
            $this->Flash->error('mysqldumpの実行時にエラーが発生しました。');
            return $this->redirect(['action' => 'index']);
        }
    } else {
        $this->Flash->error('mysqldumpのフルパスを取得できませんでした。');
        return $this->redirect(['action' => 'index']);
    }
}

Ｂ美

ふむ、ざっと見た感じ、問題は無いかな

それじゃ、ブラウザから実行確認してみなさい

Ａ子

やってみよう

・・・

うん！
ばっちり成功したよ
（ダウンロードしたファイルの中身も確認してみたけど、問題なかった）

Ｂ美

次は「リストア」ね

「MATE端末」でのやり方は憶えてる？

Ｃ菜

当然です～

mysql -u root -p < （バックアップファイル名）[Enter]

ですよね～？
（【CakePHP5応用編⑩】を参照）

Ｂ美

正解！
だったらもう分かるでしょ？

Ａ子

手順としてはこうかな？

１．アップロードされたファイルをどこかに保存
２．そのファイルを使って「mysql」コマンドでリストア
３．保存したファイルを削除

…で、どう？

Ｂ美

もちろん、それでもOKよ

でもね
ファイルの保存場所（ディレクトリ）のパーミッションとか設定しなきゃいけないし、面倒くさいのよ

なので、これを使います

$upload = $this->request->getData('upload2');

・・・

$tmpPath = $upload->getStream()->getMetadata('uri');

上記の「$tmpPath」という変数がサーバにアップロードされたファイルと考えて良いわ

Ｃ菜

あ～、それってExcelファイルのインポート時にも出てきましたね～
（【WebAPI編④】を参照）

Ａ子

あ、ほんとだ（苦笑）
（「import」メソッドで使ってた…）

だったら、こんな感じかな

//データベースのリストア
public function restore()
{
    if ($this->request->is('post')) {
        $upload = $this->request->getData('upload2');

        //オリジナルのファイル名を取得
        $original_filename = $upload->getClientFilename();
        if ($original_filename == '') {
            $this->Flash->error('ファイルが選択されていません。');
            return $this->redirect(['action' => 'index']);
        }
        $extension = mb_strtolower(pathinfo($original_filename, PATHINFO_EXTENSION)); //拡張子

        if ($extension == 'sql') {
            //拡張子チェックOK
            $tmpPath = $upload->getStream()->getMetadata('uri');

            //mysqlのフルパスを取得
            $mysql = trim(exec("which mysql"));

            if ($mysql != "") {
                //データベース接続情報を取得
                $connection = ConnectionManager::get('default');
                $dbconfig = $connection->config();
                $host = $dbconfig['host'];
                $user = $dbconfig['username'];
                $password = $dbconfig['password'];
                $db = $dbconfig['database'];
                $charset = $dbconfig['encoding'];

                //コマンド文字列の生成と実行
                $cmd = $mysql." --host=".$host." --user=".$user." --password=".$password." --default-character-set=".$charset." ".$db." < ".$tmpPath;
                $ret = shell_exec($cmd);
                if (!is_null($ret)) {
                    //成功
                    $this->Flash->success(__('データベースの復元に成功しました。'));
                } else {
                    //失敗
                    $this->Flash->error(__('データベースの復元に失敗しました。'));
                }
            } else {
                $this->Flash->error(__('MySQLのクライアントが見つかりません。'));
            }
        } else {
            $this->Flash->error(__('バックアップファイルではない可能性があります。'));
        }

        return $this->redirect(['action' => 'index']);
    }
}

どうよ
完璧でしょ？

Ｂ美

うーん、ほとんど大丈夫なんだけど、一点だけ惜しい！

「mysql」コマンドの実行結果は「shell_exec」関数じゃ取得できないわよ

Ａ子

いやいや、成功なら何らかの実行結果で、失敗なら「null」が返るって、さっき言ってたじゃん

Ｂ美

「MATE端末」上で「mysql」コマンドを使ってリストアしたときって、何か返ってきたかしら？

Ｃ菜

あ～
何も返ってこなかったです～

つまり「null」判定になっちゃうってことですか～

Ｂ美

そういうこと
そして「mysql」コマンドの実行結果（成功か失敗か）については、以下の呼び出し方法で取得できるの

exec($cmd, $output, $ret);

この第三引数である「$ret」という変数の値が「0」だったら成功、「0以外」だったら失敗ってことよ

Ａ子

なるほどねぇ
ならば、こうよ

//データベースのリストア
public function restore()
{
    if ($this->request->is('post')) {
        $upload = $this->request->getData('upload2');

        //オリジナルのファイル名を取得
        $original_filename = $upload->getClientFilename();
        if ($original_filename == '') {
            $this->Flash->error('ファイルが選択されていません。');
            return $this->redirect(['action' => 'index']);
        }
        $extension = mb_strtolower(pathinfo($original_filename, PATHINFO_EXTENSION)); //拡張子

        if ($extension == 'sql') {
            //拡張子チェックOK
            $tmpPath = $upload->getStream()->getMetadata('uri');

            //mysqlのフルパスを取得
            $mysql = trim(exec("which mysql"));

            if ($mysql != "") {
                //データベース接続情報を取得
                $connection = ConnectionManager::get('default');
                $dbconfig = $connection->config();
                $host = $dbconfig['host'];
                $user = $dbconfig['username'];
                $password = $dbconfig['password'];
                $db = $dbconfig['database'];
                $charset = $dbconfig['encoding'];

                //コマンド文字列の生成と実行
                $cmd = $mysql." --host=".$host." --user=".$user." --password=".$password." --default-character-set=".$charset." ".$db." < ".$tmpPath;
                exec($cmd, $output, $ret);
                if ($ret == 0) {
                    //成功
                    $this->Flash->success(__('データベースの復元に成功しました。'));
                } else {
                    //失敗
                    $this->Flash->error(__('データベースの復元に失敗しました。'));
                }
            } else {
                $this->Flash->error(__('MySQLのクライアントが見つかりません。'));
            }
        } else {
            $this->Flash->error(__('バックアップファイルではない可能性があります。'));
        }

        return $this->redirect(['action' => 'index']);
    }
}