Yii migrations are an interesting way to keep track of your database changes. I was originally looking for a tool that would abstract the schema SQL — with something like the Yii ActiveRecord — so I wouldn’t have to write duplicate code for Oracle and MySQL. I mean, that’s kind of the point of going with the PDO abstraction.
So more than a few forum question/answers led me to Yii migration:
http://www.yiiframework.com/doc/guide/1.1/en/database.migration
I seriously have that page open 3 times in my tabs and I have read it top to bottom probably 20 times. I keep going back to it over and over hoping it will have more information. It’s just a little short on answers to all of my questions.
As I said, all I wanted was a way to abstract the schema, so the original premise of “migrations” was a little different than what I was looking for, but it being the only non-custom option, I went forward with it. The point of migrations is to make sure the state of your database matches the revision of your code. So it’s sort of like its own hokey versioning system. As such, it doesn’t function as simply as I might like it. It’s not easy to focus on one migration if it’s not the last migration. Because the point of migrations is that you’re moving forward and editing a past migration would be like editing revision X without it effecting all of the future revisions. So it makes sense for what it is, it’s just awkward. For one the file structure HAS to include the timestamp. And it has to include that timestamp at the front of the filename, so autocompleting these migration filenames is a pain in the ass.
One important thing they don’t talk about in the documentation is that the list of what’s been migrated is added automatically to your database via the “tbl_migrations” — and please remember the “s as it will insert it as lowercase, which could be annoying for Oracle users, though Yii seems to handle it “appropriately”.
The only major hurdle here is dealing with autoincrement. The way this was dealt with was I created an Autoincrement class that I plopped in the migrations directory:
protected/migrations/Autoincrement.php
This meant that I could just create the tables like so:
class m120402_194059_Quizes extends CDbMigration
{
public function up()
{
$this->createTable('QUIZES', array(
'ID' => 'pk',
'COLLECTION_ID' => 'integer NOT NULL',
'TITLE' => 'string NOT NULL',
'DESCRIPTION' => 'string',
'VISIBILITY' => "integer NOT NULL",
'STATE' => 'string',
'SHOW_FEEDBACK' => 'integer',
'START_DATE' => 'datetime',
'END_DATE' => 'datetime',
'DATE_MODIFIED' => 'datetime',
'DELETED' => "integer NOT NULL",
));
Autoincrement::up('QUIZES', Yii::app()->db->driverName);
}
public function down()
{
Autoincrement::down('QUIZES', Yii::app()->db->driverName);
$this->dropTable('QUIZES');
}