Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Let's say you're starting a new Yii project and you've already created the database schema for it using something like phpMyAdmin or MySQL Workbench. Now you want to create an initial database migration so you can put the schema under version control, but you don't want to manually write the Yii code to create the tables, indexes, and foreign ke…
<?php
class InitialDbMigrationCommand extends CConsoleCommand
{
public function run($args) {
$schema = $args[0];
$tables = Yii::app()->db->schema->getTables($schema);
$addForeignKeys = '';
$dropForeignKeys = '';
$result = "public function up()\n{\n";
foreach ($tables as $table) {
$compositePrimaryKeyCols = array();
// Create table
$result .= ' $this->createTable(\'' . $table->name . '\', array(' . "\n";
foreach ($table->columns as $col) {
$result .= ' \'' . $col->name . '\'=>\'' . $this->getColType($col) . '\',' . "\n";
if ($col->isPrimaryKey && !$col->autoIncrement) {
// Add column to composite primary key array
$compositePrimaryKeyCols[] = $col->name;
}
}
$result .= ' ), \'\');' . "\n\n";
// Add foreign key(s) and create indexes
foreach ($table->foreignKeys as $col => $fk) {
// Foreign key naming convention: fk_table_foreignTable_col (max 64 characters)
$fkName = substr('fk_' . $table->name . '_' . $fk[0] . '_' . $col, 0 , 64);
$addForeignKeys .= ' $this->addForeignKey(' . "'$fkName', '$table->name', '$col', '$fk[0]', '$fk[1]', 'NO ACTION', 'NO ACTION');\n\n";
$dropForeignKeys .= ' $this->dropForeignKey(' . "'$fkName', '$table->name');\n\n";
// Index naming convention: idx_col
$result .= ' $this->createIndex(\'idx_' . $col . "', '$table->name', '$col', FALSE);\n\n";
}
// Add composite primary key for join tables
if ($compositePrimaryKeyCols) {
$result .= ' $this->addPrimaryKey(\'pk_' . $table->name . "', '$table->name', '" . implode(',', $compositePrimaryKeyCols) . "');\n\n";
}
}
$result .= $addForeignKeys; // This needs to come after all of the tables have been created.
$result .= "}\n\n\n";
$result .= "public function down()\n{\n";
$result .= $dropForeignKeys; // This needs to come before the tables are dropped.
foreach ($tables as $table) {
$result .= ' $this->dropTable(\'' . $table->name . '\');' . "\n";
}
$result .= "}\n";
echo $result;
}
public function getColType($col) {
if ($col->isPrimaryKey && $col->autoIncrement) {
return "pk";
}
$result = $col->dbType;
if (!$col->allowNull) {
$result .= ' NOT NULL';
}
if ($col->defaultValue != null) {
$result .= " DEFAULT '{$col->defaultValue}'";
} elseif ($col->allowNull) {
$result .= ' DEFAULT NULL';
}
return $result;
}
}
@omartech

This comment has been minimized.

Copy link

omartech commented Sep 10, 2013

I noticed a small flaw.. if there are default values, it doesn't escape the quotes for them

so you end up with something like '...... default 'defaultval''

@selorm

This comment has been minimized.

Copy link

selorm commented Oct 4, 2013

I also realised the same problem with respect to DEFAULT values...i think it has to do with line 69

@jzfgo

This comment has been minimized.

Copy link

jzfgo commented Oct 21, 2013

@ line 73 replace:

return $result;

with:

return addslashes($result);

Profit.

@bmarston Thank you so much for this. Huge time saver.

@firecentaur

This comment has been minimized.

Copy link

firecentaur commented May 9, 2014

I seam to be getting an error: .. and its behaviors do not have a method or closure named "getColType".

@pedroponte

This comment has been minimized.

Copy link

pedroponte commented Sep 2, 2014

many thanks for this,

I've created this project for Yii automated generation of migration files, inspired by your code: https://code.google.com/p/yii-automatically-generated-migration-files/

Summary
This allows a distributed team to easily update the db locally and then distribute it's updates with thee other developers automatically with the rest of the code via a versioning control system (I used git).

Further reading
This tool exports and generates the full db in xml format and migration code (safeUp/safeDown) for:

  • initial full db;
  • added/dropped tables, columns, and foreign keys (and fks related indexes);
  • updated column attributes
    the following modified column attributes are detected and exported:
  • type, length, zerofill, allow null, default value
    Please note:
  • indexes are not automatically exported;
  • new/dropped foreign keys generate/remove linked indexes automatically in the migration file
  • columns renamed are considered drop columns and add addd new columns;
  • column unsigned and comments are not automatically exported;
  • the foreign key name exported is not a match with the one from the db, but based on the namming convention
    Final notes:
  • see the Wiki for run instructions
  • to automate the migration after a git pull, use this code in your .git/hooks folder. Don't forget to read the README file
@linkdesu

This comment has been minimized.

Copy link

linkdesu commented Jan 20, 2015

@nkm @bmarston Thanks a lot !

@asimzeeshan

This comment has been minimized.

Copy link

asimzeeshan commented Oct 28, 2015

@pedroponte any chances you can migrate it to GITHUB?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.