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…

InitialDbMigrationCommand.php

<?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 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

@nkm @bmarston Thanks a lot !

@pedroponte any chances you can migrate it to GITHUB?