Yii Framework: Example database migration.

gistfile1.aw

<?php

class m111228_064525_add_user_profile_hoge_table extends CDbMigration
{
	protected $options = 'ENGINE=InnoDB DEFAULT CHARSET=utf8';
	
	public function safeUp()
	{
		// user
		$this->createTable('user', 
			array(
				'id' => 'pk',
				'username' => 'varchar(32) NOT NULL',
				'email' => 'varchar(128) NOT NULL',
				'password' => 'varchar(64) NOT NULL',
			),
			$this->options
		);
		$this->createIndex('user_username', 'user', 'username', true);
		$this->createIndex('user_email', 'user', 'email', true);
		
		// profile
		$this->createTable('profile',
			array(
				'userid' => 'pk',
				'age' => 'integer NOT NULL',
				'location' => 'varchar(64) NOT NULL',
				'body' => 'text',
			),
			$this->options
		);
		$this->addForeignKey('fk_profile_userid', 'profile', 'userid', 'user', 'id', 'CASCADE', 'RESTRICT');
		
		// hoge
		$this->createTable('hoge',
			array(
				'id' => 'pk',
				'userid' => 'integer NOT NULL',
				'fuga' => 'varchar(64) NOT NULL',
				'piyo' => 'varchar(64) NOT NULL',
			),
			$this->options
		);
		$this->addForeignKey('fk_hoge_userid', 'hoge', 'userid', 'user', 'id', 'CASCADE', 'CASCADE');
		$this->createIndex('hoge_userid_fuga', 'hoge', 'userid,fuga', false);
	}

	public function safeDown()
	{
		$this->dropTable('user');
		$this->dropTable('profile');
		$this->dropTable('hoge');
	}
}