Yii Migrations: cheatsheet and examples

• up() method: gets called when we migrate up (or just call yiic migrate)

• down() method: gets called when we migrate down

• to create a migration:

  cd to protected folder and type:

  ./yiic migrate create my_new_migration
  

• to create a table using migrations:

  <?php
  public function up()
  {
     $this->createTable('users', array(
          'id'=>'pk',
          'username'=>'string NOT NULL',
          'password'=>'string NOT NULL',
          'created_at'=>'DATETIME NOT NULL',
          'last_login_at'=>'DATETIME NOT NULL',
      )); 
  }
  

• slightly better table creation: supporting foreign keys and utf-8 by default:(MySQL only):

  <?php
  public function up()
  {
     $this->createTable('users', array(
          'id'=>'pk',
          'username'=>'string NOT NULL',
          'password'=>'string NOT NULL',
          'created_at'=>'DATETIME NOT NULL',
          'last_login_at'=>'DATETIME NOT NULL',
      ),  'ENGINE=InnoDB CHARSET=utf8'); 
  }
  

• inserting a record into a table:

  <?php
  public function up()
  {
      $this->insert('users', array(
          "id" => "1",
          "username" => "admin",
          "password" => sha1("admin"),
          "created_at"=>date('Y-m-d H:i:s'),
          "last_login_at"=>date('Y-m-d H:i:s'),
      ));
  }
  

• deleting a record from a table: (this was added in the down() method just because we tend to delete stuff when we are migrating down or undoing a migration.)

  <?php
  public function down()
  {
      $this->delete(
          'users',"id = '1'"
          );
  }
  

• creating an INT attribute:

  <?php
  public function up()
  {
      $this->createTable('foo_bar', array(
          //more attributes....
         'type'=>'integer NOT NULL',
      ));
  }
  

• creating a composite primary key: (Yii is able to derive a MANY_MANY relation form this) see here: adding-a-composite-primary-key-for-mysql-using-yii-migrations

• setting up a string as primary key (on MySQL). For some reason migrations don't work if you write string pk as description for a column, but this works:

  <?php
  $this->createTable('foo_bar_baz', array(
          'name'=>'string NOT NULL',
          'PRIMARY KEY (name)'
      ),'ENGINE=InnoDB CHARSET=utf8'); 
  

  • creating a foreign key: this will bind table1 to table2 in order to tell the database that the attribute foreign_id of table1 is actually attribute id on table2. The next two parameters tell the database what it should do to table1 in case the id attribute of table2 gets deleted and updated, respectively. Possible values for those parameters are: "CASCADE","NO ACTION","RESTRICT" and "SET NULL"

  <?php
  $this->addForeignKey('fk1', 'table1', 'foreign_id', 'table2', 'id','CASCADE','CASCADE');
  

This is a w.i.p.(work in progress)