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 8 comments

#17747 report it
schmunk at 2014/07/18 01:50pm
@gain

copy the directory in protected/commands folder, no database command from yiic

That should work, but the command should appear as 'edatabase', could you post your yiic output with the above configuration?

Please see also the README how to configure the command via the commandMap property of the console application.

#17743 report it
gain at 2014/07/18 09:05am
Installation hints

"Get it from github and place it into your application." is not enough for a noob like me. Cloning it I have a database-command directory with all the files, I've tried to:

  • copy the directory in protected/extensions folder, no database command from yiic

  • copy the directory in protected/commands folder, no database command from yiic

  • copy the database-command directory in protected/commands renaming it in EDatabaseCommand, no database command from yiic

  • copy EDatabaseCommand.php in protected/commands and view/migration.php in protected/views/migration, I have the edatabase command from yiic but when it try to create the file it can't find migration.php

  • copy EDatabaseCommand.php in protected/commands and view/migration.php in protected/commands/views, I have the edatabase command from yiic and seems to work correctly, but I think that's not the correct installation method 'cause I have the edatabase command instead of database

Any hint on how to install it correctly? TIA

#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