database-command Follow @yiiframework

Yii command to create database migrations from existing schema

database-command

Yii command to create database migrations from existing schema. Migration classes are created in application runtime folder.

Features ¶

primary key generation
foreign key generation (skipped for SQLite)
complete data and schmema dump

Download ¶

Get it from github and place it into your application.

Usage ¶

Run the command...

./yiic database

... to show the help page

dump [<name>] [--prefix=<table_prefix,...>] [--dbConnection=<db>]
    [--createSchema=<1|0>] [--insertData=<1|0>] [--foreignKeyChecks=<1|0>] 
    [--truncateTable=<0|1>]

Param	Default	Info
name	dump	migration class name
--prefix		dump only tables with given prefix (specify multiple prefixes separated by commas)
--createSchema	1	wheter to create tables
--insertData	1	wheter to create insert statements
--foreignKeyChecks	1	set to `0` to disable foreign key checks
--truncateTable	0	wheter to remove all records from the table first
--dbConnection	db	application component to use

Example

To create a migration from an existing application scheme, define an alternative database component in your application, e.g. db-production.

The following command dumps all tables starting with p3_media and omits the schema create statements:

./yiic database dump p3media-no-schema-production --prefix=p3_media --createSchema=0 --dbConnection=db-production

This example shows data dumping, removes all data (truncate tables) and omits foreign key checks:

 ./yiic database dump p3_replace_data --prefix=Auth,Rights,usr,p3 --createSchema=0 --foreignKeyChecks=0 --truncateTable=1

Separate schema and data:

 ./yiic database dump my_schema --insertData=0
 ./yiic database dump my_data --createSchema=0

Requirements ¶

Yii 1.1.*

Configuration ¶

config/console.php

'commandMap' => array(
    'database' => array(
        'class' => 'vendor.schmunk42.database-command.EDatabaseCommand',
    ),
)

Resources ¶

Availble via Phundament 3 Composer Package Repository http://packages.phundament.com
Fork on github
CHANGELOG
View at Yii Extensions

Total 6 comments

#11525

schmunk at 2013/01/18 09:08am

git issues

I know :)

I've added the two open points to github as issue 5 and 6

#11524

le_top at 2013/01/18 08:45am

Pull request ...

Feel free to create a pull request ;)

I understand the message, and I know what its like - I have an opensource of my own: BT747 - unfortunately we are too often on our own. When I can, I provide code (see the ireport extension for instance), however I have no time for this one (and no time to dig into GIT for the moment).

Regarding the use of the model table names: there may be cases where migrations run into issues but I do not expect somebody to use this extension to create a migration, but more often to use it to do the initial setup. Personnally, I keep some initialisation code ready for the test setup and initialisation, and use migrations for live databases.

I'ld also like to see comments be recovered.

#11511

schmunk at 2013/01/17 06:05pm

@Alan and @le_top

Thanks for your code and suggestions I've included most of them in the just released version 0.8.6

About these two points:

A small suggestion for the generator: I prefer to get the table name form the Model, like this: $this->createTable(ActionHistory::model()->tableName()

I think this will create additional dependencies from migrations to models and you may run into problems, when you need a migration which changes your table name.

When the primary key is composed of two columns, the command generation is also incorrect:

Feel free to create a pull request ;)

#11253

le_top at 2013/01/01 02:24pm

Use dynamic table names, other improvements.

A small suggestion for the generator: I prefer to get the table name form the Model, like this:

$this->createTable(ActionHistory::model()->tableName()

As foreign keys depend on multiple tables, they should be added only after all tables have been created.
When the primary key is composed of two columns, the command generation is also incorrect:

CREATE TABLE IF NOT EXISTS `alert_listener` (
  `alert_listener_id` int(11) NOT NULL AUTO_INCREMENT,
  `listener` varchar(250) DEFAULT NULL COMMENT 'Describes the listner (email address, phone number, ...).',
  `listener_type` int(11) DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT NULL,
  `entity_alert_id` int(11) NOT NULL,
  PRIMARY KEY (`alert_listener_id`,`entity_alert_id`),
  KEY `fk_alert_listener_device_alert1` (`entity_alert_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=815 ;

gives

$this->createTable(AlertListener::model()->tableName(),
                array(
                        "alert_listener_id"=>"pk",
                        "listener"=>"varchar(250)",
                        "listener_type"=>"int(11)",
                        "is_active"=>"tinyint(1)",
                        "entity_alert_id"=>"int(11) NOT NULL",
                        "PRIMARY KEY (entity_alert_id)"
                ),
                $options);

#11205

Alan Lobo at 2012/12/27 10:28am

Diff for unsigned primary keys / unique name for keys

I had to make small changes because the foreign keys generated were not compatible with the primary key generation (mainly due to 'unsigned')

private function resolveColumnType($col)
    {
//removed
//        if ($col->isPrimaryKey && $col->autoIncrement) {
//            return "pk";
//        }
 
        $result = $col->dbType;
 
        if (!$col->allowNull) {
            $result .= ' NOT NULL';
        }
        if ($col->defaultValue != null) {
            $result .= " DEFAULT '{$col->defaultValue}'";
        }
//added
        if ($col->isPrimaryKey) {
            $result .= " PRIMARY KEY";
        }
 
        if ($col->autoIncrement) {
            $result .= " AUTO_INCREMENT";
        }
//end
        return $result;
    }

and

private function generateForeignKeys($table, $schema)
    {
        if (count($table->foreignKeys) == 0)
            return "";
        $code = "\n\n\n" . $this->indent(2) . "// Foreign Keys for table '" . $table->name . "'\n";
        $code .= $this->indent(2) . "if ((Yii::app()->db->schema instanceof CSqliteSchema) == false):";
        foreach ($table->foreignKeys as $name => $foreignKey) {
//            $code .= "\n" . $this->indent(3) . "\$this->addForeignKey('fk_{$foreignKey[0]}_{$name}', '{$table->name}', '{$name}', '{$foreignKey[0]}', '{$foreignKey[1]}', null, null); // FIX RELATIONS \n";
//added the table name to the key name to make it unique
            $code .= "\n" . $this->indent(3) . "\$this->addForeignKey('fk_{$table->name}_{$foreignKey[0]}_{$name}', '{$table->name}', '{$name}', '{$foreignKey[0]}', '{$foreignKey[1]}', null, null); // FIX RELATIONS \n";        }
        $code .= "\n" . $this->indent(2) . "endif;\n";
        $this->_displayFkWarning = TRUE;
        return $code;
    }

#10234

yiqing95 at 2012/10/13 01:12am

run it from web

refer here Run Yiic directly from your app without a shell

i just simple test it ,and it works :

public function actionEDataBaseCommand(){
        $commandPath = Yii::app()->getBasePath() . DIRECTORY_SEPARATOR . 'commands';
        $runner = new CConsoleCommandRunner();
        $runner->addCommands($commandPath);
        // below it is dir path where you put this extension  
        $commandPath =  Yii::getPathOfAlias('application.vendors.schmunk42-database-command');
 
        $runner->addCommands($commandPath);
        $args = array('yiic', 'edatabase', 'dump');
        ob_start();
        $runner->run($args);
        echo htmlentities(ob_get_clean(), null, Yii::app()->charset);
    }

Please login to leave your comment.