Tuesday, March 11, 2014

Setting the CDbConnection for Active Records within a Yii Migration

I use migrations whenever possible within Yii applications to help ensure consistency between implementation. I have multiple development environments, a staging environment and a production environment. I really don't want to have to manually issue a lot of sql queries or yii commands if I don't have to. Migrations make that possible and can be integrated automatically in the build process for deployment.

Occasionally, I need to convert some old data into a new structure, and I can do this via the migrations as well!

Create a basic migration to translate data:

For example, to query a set of data from an old table, and create a new set of records from it (that may have additional logical processes added beforeSave etc.) I could start off by doing the following in my migration:

public function up()
{
  // Query for the existing record data
  $results = Yii::app()->db->createCommand()
    ->select('*')
    ->from('tbl_old')
    ->queryAll();

  // Ensure that if we fail, we start with a fresh slate
  try {
    foreach( $results as $row )
    { 
       $m = new NewModel();
       // assign attributes from $row[] that should be
       // mapped to the new model applying any new
       // logical manipulations required
       if (!$m->save())
       {
         throw new Exception(
            CVarDumper::dumpAsString( $m->errors )
         );
       }
    }
  } catch ( Exception $e )
  {
     echo $e->getMessage(), PHP_EOL;
     $this->truncate('tbl_new');
     // indicate migration failure
     return false;
  }

  // acknowledge success
  return true;
}


If you only have one db connection in your application, this will work fine. But, if you also have a test database that you need to migrate, it will quickly fail due to duplicate primary keys, or write double the data in your primary database, rather than your test database.

Update the migration to handle alternate connection components:

To update the migration to work with a different connection, we simply need to add a few lines that specify which connection to use, as follows:

public function up()
{
  // Query for the existing record data 
  // Use migration's db connection rather than Yii::app()->db
  $results = $this->getDbConnection()->createCommand()
    ->select('*')
    ->from('tbl_old')
    ->queryAll();

  // Set the static connection property for active records to the migration's 
  // connnection
  CActiveRecord::$db = $this->getDbConnection();
 
  try {
  //  ... the rest remains the same
  }
}


Now, I can run my migration on the alternate connection with no problems, and no pulling of hair!

./yiic migrate --connectionID=db_test


On the Yii2 front, I was side tracked by the fact that I can't find an equivalent to the CViewAction (which I use extensively), and so much of the main layout involves new widgets in static method form, but I will get back to it soon!

No comments:

Post a Comment