m_shige1979のときどきITブログ

プログラムの勉強をしながら学習したことや経験したことをぼそぼそと書いていきます

Github（変なおっさんの顔でるので気をつけてね）

https://github.com/mshige1979

cakephp3のmigrations3

php cakephp phinx マイグレーション

テーブル追加などのいくつかの機能

なんか必要そうな記法を調査

テーブル

テーブル作成
<?php

use Phinx\Migration\AbstractMigration;

class Initial extends AbstractMigration
{
    /**
     * Migrate Up.
     */
    public function up()
    {
        $sample1 = $this->table("sample1");
        $sample1
            ->create()
            ;
    }

    /**
     * Migrate Down.
     */
    public function down()
    {

    }
}

↓

CREATE TABLE `sample1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

※テーブルに何も指定しない場合はidが主キーで設定される

idを主キーにしない
<?php

use Phinx\Migration\AbstractMigration;

class Initial extends AbstractMigration
{
    /**
     * Migrate Up.
     */
    public function up()
    {
        $this->dropTable("sample1");
        $sample1 = $this->table("sample1"
            , array(
                "id" => false
            )
        );
        $sample1
            ->addColumn("shohin_cd", "string", array("limit" => 10))
            ->addColumn("shohin_name", "string", array("limit" => 256))
            ->create()
            ;
    }

    /**
     * Migrate Down.
     */
    public function down()
    {

    }
}

↓

CREATE TABLE `sample1` (
  `shohin_cd` varchar(10) NOT NULL,
  `shohin_name` varchar(256) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

※idを無効にしたら主キーは任意で設定しないといけない

プライマリキーを設定
<?php

use Phinx\Migration\AbstractMigration;

class Initial extends AbstractMigration
{
    /**
     * Migrate Up.
     */
    public function up()
    {
        $this->dropTable("sample1");
        $sample1 = $this->table("sample1"
            , array(
                "id" => false,
                "primary_key" => array("shohin_cd")
            )
        );
        $sample1
            ->addColumn("shohin_cd", "string", array("limit" => 10))
            ->addColumn("shohin_name", "string", array("limit" => 256))
            ->create()
            ;
    }

    /**
     * Migrate Down.
     */
    public function down()
    {

    }
}

↓

CREATE TABLE `sample1` (
  `shohin_cd` varchar(10) NOT NULL,
  `shohin_name` varchar(256) NOT NULL,
  PRIMARY KEY (`shohin_cd`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

※テーブル設定時にプライマリキーを指定する場合

テーブル名変更
<?php

use Phinx\Migration\AbstractMigration;

class Initial extends AbstractMigration
{
    /**
     * Migrate Up.
     */
    public function up()
    {
        $this->table("sample1")
            ->rename("hoge11111");
    }

    /**
     * Migrate Down.
     */
    public function down()
    {

    }
}

※tableで取得したあとにrenameメソッドで変更

テーブル削除
<?php

use Phinx\Migration\AbstractMigration;

class Initial extends AbstractMigration
{
    /**
     * Migrate Up.
     */
    public function up()
    {
        $this->droptable("hoge11111");
    }

    /**
     * Migrate Down.
     */
    public function down()
    {

    }
}

※dropTableメソッドで削除

項目のデータ型

string
<?php

use Phinx\Migration\AbstractMigration;

class Initial extends AbstractMigration
{
    /**
     * Migrate Up.
     */
    public function up()
    {
        if($this->hasTable("sample1")){
            $this->dropTable("sample1");
        }
        $sample1 = $this->table("sample1"
            , array(
                "id" => false
            )
        );
        $sample1
            ->addColumn("data1", "string")
            ->addColumn("data2", "string", array("limit" => 100))
            ->create()
        ;
    }

    /**
     * Migrate Down.
     */
    public function down()
    {

    }
}

↓

CREATE TABLE `sample1` (
  `data1` varchar(255) NOT NULL,
  `data2` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

※varcharで未指定の場合は255桁数となる

integer、biginteger
<?php

use Phinx\Migration\AbstractMigration;

class Initial extends AbstractMigration
{
    /**
     * Migrate Up.
     */
    public function up()
    {
        if($this->hasTable("sample1")){
            $this->dropTable("sample1");
        }
        $sample1 = $this->table("sample1"
            , array(
                "id" => false
            )
        );
        $sample1
            ->addColumn("data1", "integer")
            ->addColumn("data2", "integer", array("limit" => 10))
            ->addColumn("data3", "biginteger")
            ->addColumn("data4", "biginteger", array("limit" => 10))
            ->create()
        ;
    }

    /**
     * Migrate Down.
     */
    public function down()
    {

    }
}

↓

CREATE TABLE `sample1` (
  `data1` int(11) NOT NULL,
  `data2` int(10) NOT NULL,
  `data3` bigint(20) NOT NULL,
  `data4` bigint(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

※integerやbigintegerの場合

decimal
<?php

use Phinx\Migration\AbstractMigration;

class Initial extends AbstractMigration
{
    /**
     * Migrate Up.
     */
    public function up()
    {
        if($this->hasTable("sample1")){
            $this->dropTable("sample1");
        }
        $sample1 = $this->table("sample1"
            , array(
                "id" => false
            )
        );
        $sample1
            ->addColumn("data1", "decimal")
            ->addColumn("data2", "decimal", array("limit" => "5,2"))
            // ->addColumn("data2", "decimal", array("precision" => 5, "scale" => 2))
            ->create()
        ;
    }

    /**
     * Migrate Down.
     */
    public function down()
    {

    }
}

↓

CREATE TABLE `sample1` (
  `data1` decimal(10,0) NOT NULL,
  `data2` decimal(5,2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

※データの桁数が小数部も指定する場合は”n，n”のように指定すればできるよう

text
<?php

use Phinx\Migration\AbstractMigration;

class Initial extends AbstractMigration
{
    /**
     * Migrate Up.
     */
    public function up()
    {
        if($this->hasTable("sample1")){
            $this->dropTable("sample1");
        }
        $sample1 = $this->table("sample1"
            , array(
                "id" => false
            )
        );
        $sample1
            ->addColumn("data1", "text")
            ->create()
        ;
    }

    /**
     * Migrate Down.
     */
    public function down()
    {

    }
}

↓

CREATE TABLE `sample1` (
  `data1` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
date、time、datetime
<?php

use Phinx\Migration\AbstractMigration;

class Initial extends AbstractMigration
{
    /**
     * Migrate Up.
     */
    public function up()
    {
        if($this->hasTable("sample1")){
            $this->dropTable("sample1");
        }
        $sample1 = $this->table("sample1"
            , array(
                "id" => false
            )
        );
        $sample1
            ->addColumn("data1", "date")
            ->addColumn("data2", "time")
            ->addColumn("data3", "datetime")
            ->addColumn("data4", "timestamp")
            ->create()
        ;
    }

    /**
     * Migrate Down.
     */
    public function down()
    {

    }
}

↓

CREATE TABLE `sample1` (
  `data1` date NOT NULL,
  `data2` time NOT NULL,
  `data3` datetime NOT NULL,
  `data4` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
その他
<?php

use Phinx\Migration\AbstractMigration;

class Initial extends AbstractMigration
{
    /**
     * Migrate Up.
     */
    public function up()
    {
        if($this->hasTable("sample1")){
            $this->dropTable("sample1");
        }
        $sample1 = $this->table("sample1"
            , array(
                "id" => false
            )
        );
        $sample1
            ->addColumn("data1", "binary")
            ->addColumn("data2", "boolean")
            ->create()
        ;
    }

    /**
     * Migrate Down.
     */
    public function down()
    {

    }
}

↓

CREATE TABLE `sample1` (
  `data1` blob NOT NULL,
  `data2` tinyint(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

追加位置

予めテーブルを作成
<?php

use Phinx\Migration\AbstractMigration;

class Initial extends AbstractMigration
{
    /**
     * Migrate Up.
     */
    public function up()
    {
        if($this->hasTable("sample1")){
            $this->dropTable("sample1");
        }
        $sample1 = $this->table("sample1"
            , array(
                "id" => false
            )
        );
        $sample1
            ->addColumn("data1", "string")
            ->addColumn("data2", "string")
            ->create()
        ;
    }

    /**
     * Migrate Down.
     */
    public function down()
    {

    }
}
data1の後に追加
<?php

use Phinx\Migration\AbstractMigration;

class Sample1 extends AbstractMigration
{
    /**
     * Change Method.
     *
     * More information on this method is available here:
     * http://docs.phinx.org/en/latest/migrations.html#the-change-method
     *
     * Uncomment this method if you would like to use it.
     *
    public function change()
    {
    }
    */
    
    /**
     * Migrate Up.
     */
    public function up()
    {
        $sample1 = $this->table("sample1");
        $sample1
            ->addColumn("hoge1", "integer", array("after" => "data1"))
            ->addColumn("hoge2", "char", array("after" => "hoge1"))
            ->save();
    }

    /**
     * Migrate Down.
     */
    public function down()
    {

    }
}

※afterに設定

↓

CREATE TABLE `sample1` (
  `data1` varchar(255) NOT NULL,
  `hoge1` int(11) NOT NULL,
  `hoge2` char(255) NOT NULL,
  `data2` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

所感

全部のデータ型を保証しているわけではない感じ。
一部は対応できるけど、できない部分はSQLで対応するしかないかも…