Migration the database schema to another one

You are viewing revision #3 of this wiki article.
This is the latest version of this article.
You may want to see the changes made in this revision.

« previous (#2)

In this wiki I will show how to copy a schema from a database to another one

Note that there are compatible issues depended of the source and the destination database, so I preferred to keep the migration as simple as possible

In this wiki we will use as source the mysql database and for destination an sqlite database

1) Add in protected/config/main.php both of two database connection in the component section

'dblite' => array(
            'class'=>'CDbConnection',
            'connectionString' => 'sqlite:' . dirname(__FILE__) . '/../data/sqlite.db',
 ),

'db' => array(
    'connectionString' => 'mysql:host=localhost;dbname=mydatabase',
    'username' => 'root',
    'password' => '',
    'emulatePrepare' => true,
    'charset' => 'utf8',
),

2) Make a php file (for example Myglobals.php) In your components

class Myglobals {

  public static function MigrateMsqlToSqlite() {

        $path = Yii::getPathOfAlias('application.data');
       

        $del = (@unlink($path . '/thedatabase.db'));
        if (!$del) {
            throw new CHttpException(403,'Access file permission denied!');
        }

        touch($path . '/thedatabase.db');

        $cmd = Yii::app()->dblite->createCommand();

        $connection = Yii::app()->db;
        $dbSchema = $connection->schema;
        $tables = $dbSchema->getTables();
        foreach ($tables as $tbl) {
            $cols = [];
            foreach ($tbl->columns as $c)
                    $cols[$c->name] = $c->type;
            
            $cmd->createTable($tbl->name, $cols);
        }
    }   
}

3) Call in your Controller/action the above function like that Myglobals::MigrateMsqlToSqlite();

Now the sqlite.db conteains the database schema of the mysql