PHPマイグレーションツール lulco/phoenix と MySQL に関するメモ

  • 投稿日
  • 著者 アルム＝バンド
  • カテゴリー MySQL
  • カテゴリー PHP

一つ一つは比較的小さな Tips の内容ですが、今回改めて結構がっつりと lulco/phoenix を弄ることになったため、まとめて一つの記事としてメモを残しておきます。

1. lulco/phoenix のテーブルの依存関係と実行順序

経緯

以前 (PHP) Phoenix でデータ関係が依存しているDBをマイグレーションする の記事で lulco/phoenix のマイグレーションでデータベースに依存関係がある場合について記事を書きましたが、今回同じようなことをしようとして上手く行かなかったため改めて記事にします。

設定としては上述の記事の通り、 phoenix.php で migration_dirs キーにネストで first, second と指定をしていきました。

    'migration_dirs' => [
        'first'  => __DIR__ . DIRECTORY_SEPARATOR . '_migrations' . DIRECTORY_SEPARATOR . 'first',
        'second' => __DIR__ . DIRECTORY_SEPARATOR . '_migrations' . DIRECTORY_SEPARATOR . 'second',
    ],

しかし、今回は second のディレクトリのコードが先に実行されてしまい、依存関係で first でマイグレーションするつもりだった DBテーブル を select しようとして「テーブルがない」と怒られてしまいました。

なお、実行環境は以下の通りです。

  • PHP: XAMPP PHP 7.4.3
  • lulco/phoenix: 2.6.0

今回はどうも上手く行かず、以下の事項を対処として試しましたが、いずれか、または全てを実行しても改善しませんでした。

  • first, second のディレクトリの階層(_migrations)に他のファイル・ディレクトリを配置しない。
    • 今回はテキストファイルからレコードを挿入するためにファイル読み込みや、データテキストの行を正規表現でパースするためのヘルパークラスのファイルを用意していました。これは first, second どちらの工程でも使用するため、first, second, helpers.php や first, second, helspers/helpers.php のように同じ階層にファイル、ディレクトリを配置していました。
      • これを migrate/first, migrate/second と一つ階層を深く掘り下げて、 _migrations の下は migrate/first, migrate/second, helspers/helpers.php というようにマイグレーションファイルのディレクトリ(_migrations/migrate)に first, second 以外何も置かないようにしました。
      • 併せて migration_dirs の指定も以下のように書き換えました。
    'migration_dirs' => [
        'first'  => __DIR__ . DIRECTORY_SEPARATOR . '_migrations' . DIRECTORY_SEPARATOR . 'migrate' . DIRECTORY_SEPARATOR . 'first',
        'second' => __DIR__ . DIRECTORY_SEPARATOR . '_migrations' . DIRECTORY_SEPARATOR . 'migrate' . DIRECTORY_SEPARATOR . 'second',
    ],
  • first, second を別の記述 (1st, 2nd) にしたり、キーを数字として配列にしたりした。
    • 配列にした場合は以下のような設定になりました。
    'migration_dirs' => [
        __DIR__ . DIRECTORY_SEPARATOR . '_migrations' . DIRECTORY_SEPARATOR . 'migrate' . DIRECTORY_SEPARATOR . 'first',
        __DIR__ . DIRECTORY_SEPARATOR . '_migrations' . DIRECTORY_SEPARATOR . 'migrate' . DIRECTORY_SEPARATOR . 'second',
    ],
  • DIRECTORY_SEPARATOR を普通に文字列スラッシュ(/)にした。
  • 名前空間を切る。
    • namespace Migrations; と宣言していたのを、 namespace FirstDir;, namespace SecondDir; 等とディレクトリの階層に合わせた名前に変更した。
  • 各ディレクトリのマイグレーションファイルを1個ずつにした。

これらを試行錯誤しましたが、結局解決には至りませんでした。

対処

いくつかの実行結果から、マイグレーションが途中まで走る際にどうやらファイル名で実行順番が決まるのではないか、と推測しました。

  • 前回の記事を書いたときは、記事中では hoge.php と fuga.php としていますが実際には first/post_data.php と second/taxonomy_master.php のようなファイル名で、依存関係として post_data → taxonomy_master の順番でした。
    • 単純にファイル名であればアルファベット順で p → t なので「たまたま」上手く動いたのではないか。
  • 今回のケースでは first/raw_data.php → second/empty_data.php のようなファイル名であり、 e → r なのでアルファベット順と本来実行してほしい順番が逆転していました。
    • その上で、上述のような対処をいくら行っても、 second/empty_data.php の方が先に実行されてしまう現象に遭遇して嵌まりました。

そこで、以下のようにファイル名の先頭に番号を付けました。

  • migrate/1_raw_data.php
  • migrate/2_empty_data.php

しかも、記載の通りディレクトリを同じディレクトリで1つに統合してしまいました。

これで実行した結果……上手く動きました。

この結果から、サンプルで記載されているような first や second の指定は関係なく、ディレクトリディレクトリ構造はもはや関係なく、単純にマイグレーションファイル名を昇順で並ぶように記述すれば上手く動くようだ、ということが分かりました。

なお、この場合は当然ですが migrate ディレクトリ以下に冒頭のようなヘルパーのクラスとかを置いていると巻き込まれるので分離しておく必要はあります。

イマイチ釈然としませんが……(もしくは PHP 8.x 系の環境ならば挙動が変わるのかは未調査。ただ、 PHP や lulco/phoenix のバージョンで挙動がそんなに大きく変化するのであればそれはそれでいかがなものかと……(ドキュメントにもそういった情報はなさそうでしたし))。

2. 型の指定方法、対応、代入

lulco/phoenix での型の指定の仕方がイマイチ分からなかったのでメモ。

$this->table('raw_data')
     ->addColumn('created_at', 'datetime')
     ->addColumn('name', 'string')
     ->addColumn('caetgory_id', 'tinyinteger')
     ->addColumn('tag_id', 'tinyinteger')
     ->addColumn('msg', 'string', ['length' => 8000])
     ->addIndex('id', Index::TYPE_UNIQUE)
     ->addIndex('caetgory_id', Index::TYPE_NORMAL)
     ->addIndex('tag_id', Index::TYPE_NORMAL)
     ->create();
  • TINYINT: tinyinteger
  • VARCHAR: string で第三引数に ['length' => 8000] のように長さを指定する。
  • 真偽値: lulco/phoenix では boolean の指定ができますが、 MySQL 側には Boolean はないので実際は 0 or 1 が入る。
    • lulco/phoenix で値を代入する際は true or false で良い。

地味に引っかかったのでメモ。

3. ERROR 1118 (42000): Row size too large.

これも MySQL 側の話になりますが、最初以下のような設計をしました。

$this->table('raw_data')
     ->addColumn('created_at', 'datetime')
     ->addColumn('name', 'string')
     ->addColumn('caetgory_id', 'tinyinteger')
     ->addColumn('tag_id', 'tinyinteger')
     ->addColumn('msg', 'string', ['length' => 10000])
     ->addColumn('msg2', 'string', ['length' => 10000])
     ->addIndex('id', Index::TYPE_UNIQUE)
     ->addIndex('caetgory_id', Index::TYPE_NORMAL)
     ->addIndex('tag_id', Index::TYPE_NORMAL)
     ->create();

すると、以下のようなエラーが発生しました。

ERROR 1118 (42000): Row size too large.

MySQL では行ごとの最大サイズに制約があるようです。

MySQL テーブルの内部表現の最大行サイズは 65,535 バイトです。

MySQL :: MySQL 8.0 リファレンスマニュアル :: 8.4.7 テーブルカラム数と行サイズの制限

そもそもそれだけの長さが必要なのか、という問題はありますが今回は削れませんでした。

$this->table('raw_data')
     ->addColumn('created_at', 'datetime')
     ->addColumn('name', 'string')
     ->addColumn('caetgory_id', 'tinyinteger')
     ->addColumn('tag_id', 'tinyinteger')
     ->addColumn('msg', 'string', ['length' => 8000])
     ->addColumn('msg2', 'string', ['length' => 8000])
     ->addIndex('id', Index::TYPE_UNIQUE)
     ->addIndex('caetgory_id', Index::TYPE_NORMAL)
     ->addIndex('tag_id', Index::TYPE_NORMAL)
     ->create();

対処としては各列の長さを短くすることでギリギリ収めました。

4. 主キー(primary key)とインデックス

  • 主キー(primary key): あるレコードをそのテーブル内で一意と識別できる値。
  • インデックス: 検索処理を高速化するためのインデックスでしかない。
    • 主キーのように「あるレコードをそのテーブル内で一意と識別できる」というような制約はこれ単体では持たない。

というところは MySQL の話ですが、冒頭の過去記事でもちらっと触れていますがドキュメントがないため、 lulco/phoenix での普通のインデックスの作り方がパッと見では分かりませんでした。

そこで本体のコードを見て判断。 Index::TYPE_NORMAL のプロパティを付けることでできることが分かりました。

$this->table('raw_data')
     ->addColumn('created_at', 'datetime')
     ->addColumn('name', 'string')
     ->addColumn('caetgory_id', 'tinyinteger')
     ->addColumn('tag_id', 'tinyinteger')
     ->addColumn('msg', 'string', ['length' => 8000])
     ->addIndex('id', Index::TYPE_UNIQUE)
     ->addIndex('caetgory_id', Index::TYPE_NORMAL)
     ->addIndex('tag_id', Index::TYPE_NORMAL)
     ->create();

例えばこのような感じですね。

Index::TYPE_UNIQUE は主キー、 Index::TYPE_NORMAL は普通のインデックス、という認識です。

5. foreign key

今回はデータベースの依存関係だけでなく、 raw_data の ID を empty_data にも紐づける、というようなことをしたいと考えました。

詳述すると、「empty_data は raw_data の子集合で、 empty_data の任意のレコード A は raw_data の一意のレコード B に1対1の対応が付く」という関係性です。

これを表すことができる foreign key を lulco/phoenix でも作成できるようです。

しかし、これも例によって作り方が分からなかったので本体のコードを参照しました。

$this->table('raw_data')
     ->addColumn('created_at', 'datetime')
     ->addColumn('name', 'string')
     ->addColumn('caetgory_id', 'tinyinteger')
     ->addColumn('tag_id', 'tinyinteger')
     ->addColumn('msg', 'string', ['length' => 8000])
     ->addIndex('id', Index::TYPE_UNIQUE)
     ->addIndex('caetgory_id', Index::TYPE_NORMAL)
     ->addIndex('tag_id', Index::TYPE_NORMAL)
     ->create();

raw_data がこのように定義されたとして……。

$this->table('empty_data')
     ->addColumn('created_at', 'datetime')
     ->addColumn('raw_id', 'integer')
     ->addColumn('params1', 'tinyinteger')
     ->addColumn('params2', 'tinyinteger')
     ->addColumn('params3', 'integer')
     ->addIndex('id', Index::TYPE_UNIQUE)
     ->addIndex('send_datetime', Index::TYPE_NORMAL)
     ->addIndex('severity', Index::TYPE_NORMAL)
     ->addForeignKey('raw_id', 'raw_data')
     ->create();

このような記述。最後の方の ->addForeignKey('raw_id', 'raw_data') のメソッドがそれですね。第一引数がこのテーブルでの列名、第二引数が依存しているテーブルです (デフォルトでは第二引数のテーブルの id が何も記述せずとも foreign key として認識されます)。

6. ERROR 1553(HY000):Cannot drop index ‘INDEX名’: needed in a foreign key constraint

これは MySQL の話になりますが、 3. で foreign key を作りましたが、この状態でいきなり raw_data テーブルを吹き飛ばそうとすると以下のエラーが発生します。

ERROR 1553(HY000):Cannot drop index ‘INDEX名’: needed in a foreign key constraint

foreign key で依存しているのでその依存関係をなくしてから (この場合は empty_data テーブルを削除してから) でないと raw_data テーブルは削除できない、ということのようです。

しかも今回はマイグレーションの処理を作りこんでいる途中でトライアンドエラーを繰り返していたので、中途半端な状態のテーブルができてしまったりで手動で削除するのが大変でした。

7. cleanup

トライアンドエラーの最中は使えませんでしたが、全部のマイグレーションが正常に完了した状態であれば、 foreign key も含めて php vendor/bin/phoenix cleanup で綺麗に削除(マイグレーションの順番の逆向きに削除を実行している模様)できます。

そのため、 composer.json に例えば以下のようにコマンドを入れておくと良さそうです。

    "scripts": {
        "db:migrate": "php vendor/bin/phoenix migrate",
        "db:rollback": "php vendor/bin/phoenix rollback",
        "db:cleanup": "php vendor/bin/phoenix cleanup",
        "db:status": "php vendor/bin/phoenix status",
        "db:test": "php vendor/bin/phoenix test"
    }

参考

lulco/phoenix 全般

2. 型の指定方法、対応、代入

3. ERROR 1118 (42000): Row size too large.

4. 主キー(primary key)とインデックス

5. foreign key

6. ERROR 1553(HY000):Cannot drop index ‘INDEX名’: needed in a foreign key constraint

ERROR 1553(HY000):Cannot drop index ‘INDEX名’: needed in a foreign key constraint

Phoenix マイグレーション

この記事を書いた人

アルム＝バンド

フロントエンド・バックエンド・サーバエンジニア。LAMPやNodeからWP、Gulpを使ってejs,Scss,JSのコーディングまで一通り。たまにRasPiで遊んだり、趣味で開発したり。

記事一覧