Yii 1.1: database-command

Yii command to create database migrations from existing schema
27 followers

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

Total 6 comments

#11525 report it
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 report it
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 report it
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 report it
le_top at 2013/01/01 02:24pm
Use dynamic table names, other improvements.

Hi

  • 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 report it
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 report it
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);
    }

Leave a comment

Please to leave your comment.

Create extension
Downloads
No downloadable files yet