Skip to content

Instantly share code, notes, and snippets.

@bmarston
Created May 8, 2013 16:23
Show Gist options
  • Star 26 You must be signed in to star a gist
  • Fork 14 You must be signed in to fork a gist
  • Save bmarston/5541632 to your computer and use it in GitHub Desktop.
Save bmarston/5541632 to your computer and use it in GitHub Desktop.
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;
}
}
@linkdesu
Copy link

@nkm @bmarston Thanks a lot !

@asimzeeshan
Copy link

@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