how to create a table using createCommand in Yii? I’ve followed the instructions in the http://www.yiiframework.com/doc/api/1.1/CDbCommand but I am still confused. Can you give an example of creating a table using createCommand in yii. thank you
how to create a table using createCommand in Yii? I’ve followed the instructions in the http://www.yiiframework.com/doc/api/1.1/CDbCommand but I am still confused. Can you give an example of creating a table using createCommand in yii. thank you
Here is an example inside a migration.
class m130605_082835_create_message_queue extends CDbMigration
{
public function dbType()
{
list($type) = explode(':',Yii::app()->db->connectionString);
return $type;[/size]
}
public function comment($comment) {
if($this->dbType() === "mysql")
return " COMMENT ".Yii::app()->db->quoteValue($comment);
}
const MESSAGE_QUEUE_TABLE = "message_queue";
const ALERTS_MESSAGE_LINK = "alert_messages_link";
public function up()
{
$this->createTable(
self::MESSAGE_QUEUE_TABLE,
array(
"message_queue_id"=>"pk",
"method"=>"int(4)".$this->comment("Defines method: 0 - web, 1 - mail, 2 - sms, 3 - push"),
"attempts"=>"int(4)".$this->comment("Number of attempts made"),
"max_attempts"=>"int(4)".$this->comment("Maximum number of attempts"),
"last_attempt"=>"datetime".$this->comment("Last attempt at sending"),
"date_sent"=>"datetime".$this->comment("When effectively sent"),
"state"=>"tinyint(4)".$this->comment("0 - in queue, 1 - in network, 2 - sent,"),
"date_add"=>"timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP".$this->comment("When request was added"),
"from_name"=>"varchar(64)".$this->comment("Name to appear as sender"),
"from_address"=>"varchar(128)".$this->comment("Source email, phone number, ..."),
"to_address"=>"varchar(512)".$this->comment("Destination email(s),phone number(s), ..."),
"subject"=>"varchar(255)".$this->comment("Subject of the message"),
"message"=>"text".$this->comment("Message content"),
"errormsg"=>"text".$this->comment("Error message"),
)
);
$this->createIndex("to_address_idx",self::MESSAGE_QUEUE_TABLE,"to_address");
$this->createIndex("state_idx",self::MESSAGE_QUEUE_TABLE,"state");
$this->createTable(
self::ALERTS_MESSAGE_LINK,
array(
self::ALERTS_MESSAGE_LINK."_id"=>"pk",
AlertHistory::ALERT_HISTORY_ID=>"int(11)".$this->comment("Alert to which the message applies"),
"message_queue_id"=>"int(11)".$this->comment("Reference to the message"),
)
);
$this->addForeignKey("fk_alert_history", self::ALERTS_MESSAGE_LINK, AlertHistory::ALERT_HISTORY_ID, AlertHistory::model()->tableName(), AlertHistory::ALERT_HISTORY_ID);
$this->addForeignKey("fk_message_queue", self::ALERTS_MESSAGE_LINK, "message_queue_id", self::MESSAGE_QUEUE_TABLE, "message_queue_id");
}
public function down()
{
$this->dropIndex("to_address_idx", self::MESSAGE_QUEUE_TABLE);
$this->dropIndex("state_idx", self::MESSAGE_QUEUE_TABLE);
$this->dropForeignKey("fk_message_queue",self::ALERTS_MESSAGE_LINK);
$this->dropForeignKey("fk_alert_history",self::ALERTS_MESSAGE_LINK);
$this->dropTable(self::MESSAGE_QUEUE_TABLE);
$this->dropTable(self::ALERTS_MESSAGE_LINK);
return true;
}
/*
// Use safeUp/safeDown to do migration with transaction
public function safeUp()
{
}
public function safeDown()
{
}
*/}