How do you write DB migrations that are meant to work on different databases? You know, sometimes they just need different dialects…
For example, I had to create a table with a MEDIUMTEXT column and liked to index that column. So, for mysql I needed something like INDEX( column(length) ), because mysql will only create an index for a prefix of the text. SQLite doesn’t allow INDEX statements in the CREATE TABLE statement itself. Instead, you need a separate CREATE INDEX. And they know nothing about index prefix lengths…
What I actually ended up doing was something like this in my migration:
class SomeMigration extends CDbMigration
{
public function safeUp()
{
$driver = $this->dbConnection->driverName;
switch ($driver)
{
case 'sqlite':
$this->_safeUp_sqlite();
break;
case 'mysql':
$this->_safeUp_mysql();
break;
default:
throw ...
}
}
private function _safeUp_sqlite()
{
$this->createTable( 'table', array(
'id' => 'pk',
'textCol' => 'MEDIUMTEXT NOT NULL',
));
$this->createIndex( 'idx-table-textCol', 'table', 'textCol' );
}
private function _safeUp_mysql()
{
$this->createTable( 'table', array(
'id' => 'pk',
'textCol' => 'MEDIUMTEXT NOT NULL',
'INDEX( textCol(255) )',
));
}
}
Can anyone think of another approach? Or are you doing it the same way?
I saw that you visited my profile, so I visited yours and found your post without a reply.
The way I do it is similar, but my migrations depend on this base class directly:
<?php
class MyDbMigration extends CDbMigration {
protected $MySqlOptions = 'ENGINE=InnoDB CHARSET=utf8';
protected $MySqliteOptions = '';
protected $dbOptions = '';
public function dbType()
{
list($type) = explode(':',Yii::app()->db->connectionString);
return $type;
}
private function initDb() {
switch($this->dbType()) {
case 'mysql':
$this->dbOptions=$this->MySqlOptions;
break;
case 'sqlite':
$this->dbOptions=$this->MySqliteOptions;
break;
}
}
public function comment($comment) {
if($this->dbType() === "mysql")
return " COMMENT ".Yii::app()->db->quoteValue($comment);
}
public function __construct() {
$this->initDb();
}
public function createTable($table, $columns,$options="mydboptions") {
if($options==="mydboptions"||strpos($options,' COMMENT')==0) {
[size=2] $tOptions=$this->dbOptions;[/size]
[size=2] if(strpos($options,' COMMENT')===0) {[/size]
$tOptions.=$options;
}
parent::createTable($table, $columns,$tOptions);
} else {
[size=2] parent::createTable($table, $columns,$options);[/size]
}
}
}
And I use it like this (notice the use of $this->comment()):
<?php
class m130427_195619_add_initial_credit_info extends MyDbMigration
{
public function up()
{
$this->addColumn(SubscriptionCredit::model()->tableName(), 'end_date_initial', 'datetime'.$this->comment('Initial end date when the credit was added.'));
Yii::app()->db->schema->refresh();
SubscriptionCredit::model()->refreshMetaData();
SubscriptionCredit::model()->updateAll(array('end_date_initial'=>new CDbExpression('end_date')));
}
public function down()
{
$this->dropColumn(SubscriptionCredit::model()->tableName(), 'end_date_initial');
return true;
}
/*
// Use safeUp/safeDown to do migration with transaction
public function safeUp()
{
}
public function safeDown()
{
}
*/
}
The idea is that this class gets extended as needs evolve - for instance your index issue. I would try to identify a reuseable way to do that, like I did for the comments.
I also forgot to mention that my ‘commands/templates/migrate.php’ has this content so that I do not have to update the class manually when I create a migration:
<?php
class {ClassName} extends MyDbMigration
{
public function up()
{
}
public function down()
{
echo "{ClassName} does not support migration down.\\n";
return false;
}
/*
// Use safeUp/safeDown to do migration with transaction
public function safeUp()
{
}
public function safeDown()
{
}
*/
}
Thanks mate! Wouldn’t have expected someone will be sharing their thoughts on this after the first few days passed without response.
I agree, instead of writing that migrations several times (once for each target DB), I should try to collect functionality so it can be reused. Maybe a "createPartialIndex" method in my case, that falls back to normal createIndex() for sqlite, but does some custom work for mysql.
And also thanks for that hint about the template. Somehow, I never noticed MigrateCommand::$templateFile, but it definitely comes in handy. Seems like the whole system.cli package is missing in the online API.
