sfPropelMigrationsLightPlugin
Easily change the database structure without losing any data.
What it is
Migrations are a cool thing that I first saw in [RoR]. They allow you to change the database structure between application revisions without losing data, in essence "a system that automates the process of keeping the schema just as flexible as the code basis as soon as your application enters maintanance or production". Have a look at their http://media.rubyonrails.org/video/migrations.mov screencast to see what it is all about.
This plugins is called "light", because it doesn't do any RDBMS-abstraction. So you'll have to manually write SQL code. It currently works quite alright for me, though. And you don't have to write all the SQL code manually of course. Actually you'll just make a diff of the schema.sql file created by propel and copy'n'paste the right parts of it in a new migration file.
I think something more general is planned for Propel 2.0.
Installation
To install just use the symfony task:
symfony plugin-install http://plugins.symfony-project.com/sfPropelMigrationsLightPlugin
You also can install manually by downloading the attached file or checking out from the SVN repository.
Usage
step one -- create migration
The plugin provides an init-migration task. So run php symfony init-migration migration_name. This will generate a new migration class stub in the migrations directory. The migration name is purely informational. I doesn't have to be unique.
Then put code into the up() and down() methods.
The up() code should bring the DB structure to the new version. The down() method should revert what was done in up().
To do that you can use the parent class' (sfMigration) methods, e.g. executeSQL(). Have a look at the source code to see what else there is.
This might look like that in a simple case:
<?php
class Migration001 extends sfMigration
{
public function up()
{
$this->executeSQL("ALTER TABLE foo ADD COLUMN `bar` INTEGER default 0 NOT NULL");
}
public function down()
{
$this->executeSQL("ALTER TABLE foo DROP COLUMN `bar`");
}
}
step two -- run the migration
To actually update the database you have to run the migrate task.
That will execute all the migrations from the current DB version up to the latest existing migration. (A automatically created table "schema_info" saves the current DB version in the DB itself.)
You have to pass the migrate task an application parameter. Otherwise it won't be able to find the database configuration.
To upgrade or downgrade to a specific DB version call the migrate script with the version as a parameter, e.g. php symfony migrate frontend 42
You'll have to run that task on your production server after every release that contains database structure changes.
(So put a call to it in your upgrade script if you have one.)
There's no harm done by calling the migrate task even if no migrations have been added. It just won't do anything.
More Information
Migrations are wrapped in transactions. So if one command in a migration fails, all the other stuff should theoretically be rolled back.
Unfortunately this doesn't work with DDL at least in MySQL!
You can use Propel objects in your migrations. But generally you should avoid doing that! They always expect the database to have the latest schema version. That means if you e.g. add a column in a later migration the migration code using Propel objects won't work any more.
There is a sfMigration::diag() method you can use in migrations to output messages to the user. Currently it's practically just a wrapper for echo. But you should use it nonetheless as it might be integrated with the task system or logging at a later time.
Fixture Loading
There is some basic support for fixture loading. This brings some problems as fixture loading uses Propel objects. So use it carefully.
Fixture loading works like that:
You add a folder "fixtures" to your migrations directory. In there you can put subdirectories named after the migration number, e.g. "042".
Then, in Migration042::up() you'd write $this->loadFixtures(); at some point and it will load all the YAML files in the "042" directory.
Changelog
2008-05-23 | 1.0.1
- develop7: fixed syntax error in sfMigration::addColumn()
2007-10-12 | 0.5.0 Beta
Finally released as a plugin. This is my first plugin, hope everything works...
Comments welcome. :)
2007-09-07
- Migrations are now wrapped in transactions
- Basic support for fixture loading
- Some coding style corrections
- A new diag() function in the migration base class to output information to the command line.
