Cacher is the code snippet organizer for pro developers

COLUMN TYPES
(you can also use literal column declarations but they might be specific to db type)
pk: an auto-incremental primary key type, will be converted into “int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY”
string: string type, will be converted into “varchar(255)”
text: a long string type, will be converted into “text”
integer: integer type, will be converted into “int(11)”
boolean: boolean type, will be converted into “tinyint(1)”
float: float number type, will be converted into “float”
decimal: decimal number type, will be converted into “decimal”
datetime: datetime type, will be converted into “datetime”
timestamp: timestamp type, will be converted into “timestamp”
time: time type, will be converted into “time”
date: date type, will be converted into “date”
binary: binary data type, will be converted into “blob”

MIGRATON METHODS
up() method: gets called when we migrate up
down() method: gets called when we migrate down
safeUp() method: same as up() but uses transactions
safeDown() method: same as down() but uses transactions

CREATE MIGRATION
cd to protected and type:
./yiic migrate create my_new_migration
If this doesn't work check the path to framework in yiic.php and that you are in the protected directory.

APPLY MIGRATIONS
./yiic migrate (apply ALL migrations)
./yiic migrate new (shows migrations that haven't been applied)
./yiic migrate up 3 (apply next 3 migrations)
./yiic migrate down 3 (revert last 3 migrations)
./yiic migrate history (show migration history)
./yiic migrate mark 101129_185401 (modifies history to version 101129_185401 - does NOT apply migrations)

CREATE TABLE
$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: supports foreign keys and utf-8 by default (MySQL only):
$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'); 

DROP TABLE
$this->dropTable(string $table)

ADD COLUMN
$this->addColumn(string $table, string $column, string $type);

DROP COLUMN
$this->dropColumn(string $table, string $column)

INSERT RECORD
$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'),
));

DELETE RECORD
$this->delete('users',"id = '1'");

ADD FOREIGN KEY
$this->addForeignKey('fk1', 'table1', 'foreign_id', 'table2', 'id','CASCADE','CASCADE');
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"

STRING AS PRIMARY KEY (MYSQL ONLY)
Migrations don't work if you write string pk as description for a column (I think because PK tries
to apply AUTO_INCREMENT) but this works:
$this->createTable('foo_bar_baz', array(
  'name'=>'string NOT NULL',
  'PRIMARY KEY (name)'
),'ENGINE=InnoDB CHARSET=utf8');

COMPOSITE PRIMARY KEY
This works for MySQL. Not sure about other DBMS.
$this->createTable('bibliographic_item_checkout', array(
 'bibliographic_item_id'=>'int NOT NULL',
 'checkout_id'=>'int NOT NULL',
 'PRIMARY KEY (bibliographic_item_id,checkout_id)'
),'engine InnoDB');
Where bibliographic_item_checkout is a table to simulate an N to N relationship between
bibliographic_item and checkout.

ADOPTED AND EXPANDED FROM
http://queirozf.com/reminders/yii-migrations-cheatsheet-and-examples
http://channaly.wordpress.com/2012/02/02/list-of-column-type-in-yii-migration/
http://queirozf.com/reminders/adding-a-composite-primary-key-for-mysql-using-yii-migrations