Skip to content

Instantly share code, notes, and snippets.

@jzfgo

jzfgo/InitialDbMigrationCommand.php

Forked from bmarston/InitialDbMigrationCommand.php
Created October 21, 2013 21:34
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save jzfgo/7091407 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save jzfgo/7091407 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. @nkm nkm revised this gist Oct 21, 2013. 1 changed file with 16 additions and 4 deletions.
    20 changes: 16 additions & 4 deletions InitialDbMigrationCommand.php
    View file
    Original file line number Diff line number Diff line change
    @@ -1,7 +1,7 @@
    <?php

    class InitialDbMigrationCommand extends CConsoleCommand
    {

    public function run($args) {
    $schema = $args[0];
    $tables = Yii::app()->db->schema->getTables($schema);
    @@ -13,17 +13,29 @@ public function run($args) {
    foreach ($tables as $table) {
    $compositePrimaryKeyCols = array();

    $engine = Yii::app()->db->createCommand()
    ->select('ENGINE')
    ->from('information_schema.TABLES')
    ->where('TABLE_SCHEMA = :schema AND TABLE_NAME = :table', array(':schema' => $schema, ':table' => $table->name))
    ->queryScalar();

    $charset = Yii::app()->db->createCommand()
    ->select('ccsa.character_set_name')
    ->from('information_schema.TABLES t, information_schema.COLLATION_CHARACTER_SET_APPLICABILITY ccsa')
    ->where('ccsa.collation_name = t.table_collation AND t.TABLE_SCHEMA = :schema AND t.TABLE_NAME = :table', array(':schema' => $schema, ':table' => $table->name))
    ->queryScalar();

    // Create table
    $result .= ' $this->createTable(\'' . $table->name . '\', array(' . "\n";
    foreach ($table->columns as $col) {
    $result .= ' \'' . $col->name . '\'=>\'' . $this->getColType($col) . '\',' . "\n";
    $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";
    $result .= ' ), \'ENGINE=' . $engine . ' DEFAULT CHARSET=' . $charset . '\');' . "\n\n";

    // Add foreign key(s) and create indexes
    foreach ($table->foreignKeys as $col => $fk) {
    @@ -70,6 +82,6 @@ public function getColType($col) {
    } elseif ($col->allowNull) {
    $result .= ' DEFAULT NULL';
    }
    return $result;
    return addslashes($result);
    }
    }
  2. @bmarston bmarston created this gist May 8, 2013.
    75 changes: 75 additions & 0 deletions InitialDbMigrationCommand.php
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,75 @@
    <?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;
    }
    }