-
-
Save bmarston/5541632 to your computer and use it in GitHub Desktop.
<?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; | |
} | |
} |
I also realised the same problem with respect to DEFAULT values...i think it has to do with line 69
@ line 73 replace:
return $result;
with:
return addslashes($result);
Profit.
@bmarston Thank you so much for this. Huge time saver.
I seam to be getting an error: .. and its behaviors do not have a method or closure named "getColType".
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
@pedroponte any chances you can migrate it to GITHUB?
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''