Yii supports database migration via the yiic migrate command line tool. This tool supports creating new migrations, applying/reverting/redoing migrations, and showing migration history and new migrations.In this entry, we will know more about the data column types that is supported in Yii, and have some tips on defining some columns types.

Basic column type which is defined from Yii

From CDbSchema schema abstract class which is the base class for retrieving metadata information. It contains a method called getColumnType($type) which get available column type that is defined for Yii migration to support creating database schema.
For MySQL database, we have a class called CMysqlSchema which extends CDbSchema base class, defined column types array that Yii is supporting for MySQL. It's defined as below: 
 public $columnTypes=array(
  'pk' => 'int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY',
  'string' => 'varchar(255)',
  'text' => 'text',
  'integer' => 'int(11)',
  'float' => 'float',
  'decimal' => 'decimal',
  'datetime' => 'datetime',
  'timestamp' => 'timestamp',
  'time' => 'time',
  'date' => 'date',
  'binary' => 'blob',
  'boolean' => 'tinyint(1)',
  'money' => 'decimal(19,4)',
 );
  • 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)"
  • 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"
  • boolean: boolean type, will be converted into "tinyint(1)"
  • binary: binary data type, will be converted into "blob"
  • money: will be converted into decimal(19,4)

Using Yii migration to create new table

As you know, to create a table in database by using Yii migration, we have to config db component in /config/console.php, run yiic migrate create command on console, fill out up(), down() method in new file that is generated by Yii migration. In this entry, I will not explain how to use Yii migration step by step. I just want to focus on some tips to define a table in mysql database in Yii migration file. For example, we have to create a table which have the structure like the following: 
  CREATE TABLE IF NOT EXISTS `user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(150) NOT NULL,
  `password` varchar(32) NOT NULL,
  `email` varchar(255) NOT NULL,
  `description` text NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` timestamp DEFAULT CURRENT_TIMESTAMP,
  `isvalid` boolean DEFAULT 1,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
We will try to use Yii columns types to define first 
class m130827_034420_create_user extends CDbMigration
{
 public function up()
 {
  $this -> createTable ('user', array(
    'id' => 'pk',
    'name' => 'string NOT NULL',
    'password'=> 'string NOT NULL',
    'email' =>'string NOT NULL',
    'description' =>'text  NOT NULL',
    'created_at' => 'datetime NOT NULL',
    'updated_at'=> 'timestamp DEFAULT CURRENT_TIMESTAMP',
    'isvalid' => 'boolean DEFAULT 1'
  ),'ENGINE=InnoDB DEFAULT CHARSET=utf8');
  
 }
 public function down()
 {
  $this->dropTable('user');
 }

}
and here is the result:

There are two problems we need to resolve:
  • The primary key type is int(11), not bigint(20)
  • The length of name and password is 255

Defining Big integer primary key

just change 
  'id' => 'pk',
become 
   'id' => 'bigint(20) AUTO_INCREMENT PRIMARY KEY',
because we just have pk column type which is defined in Yii. It's will become "int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY" as mentioned before in CMysqlSchema class. We do not have bigpk to become "bigint(20) NOT NULL AUTO_INCREMENT PRIMARY KEY " , so we have to define it directly in colums array.

Specifying The Length Of String In Yii Migration


  'name' => 'string NOT NULL',
become 
   'name' => 'varchar(150) NOT NULL',
and do it again with what varchar field you want to specific length, because string type is converted into varchar(255) in Yii. Now, we have new m130827_034420_create_user class: 
 
 class m130827_034420_create_user extends CDbMigration
{
 public function up()
 {
  $this -> createTable ('user', array(
    'id' => 'bigint(20) AUTO_INCREMENT PRIMARY KEY',
    'name' => 'varchar(150) NOT NULL',
    'password'=> 'varchar(32) NOT NULL',
    'email' =>'string NOT NULL',
    'description' =>'text  NOT NULL',
    'created_at' => 'datetime NOT NULL',
    'updated_at'=> 'timestamp DEFAULT CURRENT_TIMESTAMP',
    'isvalid' => 'boolean DEFAULT 1'
  ),'ENGINE=InnoDB DEFAULT CHARSET=utf8');
  
 }
 public function down()
 {
  $this->dropTable('user');
 }

}
which will create the table like the followings: