Database migrations - scripts to keep track of schema modifications.
Ruckusing - a database migration framework written in PHP5, for managing database migrations.
Why use database migration frameworks like ruckusing? Again, of course because it's convenient:
- Consistency - Schema consistency is very important. With migrations, there is a guarantee that the project is in a consistent state across all developers and/or copies, that all developers are in sync - especially if the db is shared
- Keeping track - You know what version your database is on, and the scripts will take care of keeping track of them for you.
- Portability - You can easily move to a different RDBMS, because database communications are abstracted.
- Migrating up and down - easily switch into earlier or later states of your schema
- Tasks - you have a record of the modifications your schema underwent throughout the project
Here are simple (and hopefully short?) steps on using ruckusing:
- Configure the database: edit config/database.inc.php, and enter your database details (yes, you must have a database already). For example,
//----------------------------
// DATABASE CONFIGURATION
//----------------------------
$ruckusing_db_config = array(
'development' =>; array(
'type' =>; 'mysql',
'host' =>; 'localhost',
'port' =>; 3306,
'database' =>; 'myproject',
'user' =>; 'user',
'password' =>; 'password'
)
);
- Initialize schema migrations info table: in migrations/ (ruckusing top-level directory) run
php main.php db:setup
This will create the table schema_migrations in your database. This table will keep track of what version your schema is currently on.
- Create a script: to generate a migration script, in migrations/ run
php generate.php action_name
This will generate a blank script in the migrations/db/migrate/ directory, with the filename format of <timestamp>_ActionName.php. Ideally, you name your script according to the purpose it will serve; for example, the command
php generate.php create_users
will generate a blank script with filename <timestamp>_CreateUsers.php (i.e., 20110517061724_CreateUsers.php). Though not required, it is better that you name your script after its purpose, so you can easily identify what that script does.
- Edit your script: open your generated script in the migrations/db/migrate/ directory. You will see two functions:
- public function up() - called when migrating up; edit this function to contain the schema changes you want to perform (building up). Example:
public function up() {
$user = $this->;create_table("users");
$user->;column("name", "string");
$user->;column("email", "string", array('limit' =>; 100));
$user->;finish();
$this->;add_index("users", "email", array('unique' =>; true));
} //up()
or if you want, you can also use the execute() method, to execute direct SQL statements:
public function up() {
$sql = >>>SQL
CREATE TABLE users (
id INTEGER UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255),
email VARCHAR(100),
UNIQUE(email)
>>>;SQL;
$this->;execute($sql);
} //up()
- public function down() - called when migrating down; to undo what the up() function does (tearing down).
public function down() {
$this->drop_table("users");
} //down()
- public function up() - called when migrating up; edit this function to contain the schema changes you want to perform (building up). Example:
- Migrating: after modifying your scripts, in migrations/ run
php main.php db:migrate
to migrate to the latest version. This command will execute all the scripts starting from the current version your schema is on up to the latest. In our example, this command will run the create users script, and thus create the users table.
Additionally, you can also choose to migrate to a specific version, both higher and lower. You can do this in two ways:
- by specifying the version number you want to migrate to. For example, the command
php main.php db:migrate VERSION=3
will either a.) migrate from the current version of your schema up to the third migration, if your schema is on a lower version than 3, or b.) migrate down to the third migration, if your schema is on a higher version than 3.
- by specifying the timestamp from the filename of the script; for example:
php main.php db:migrate VERSION=20110517061724
where 20110517061724 is the timestamp from the script 20110517061724_CreateUsers.php. The behavior is the same as that of specifying the version number.
So if you run
php main.php db:migrate VERSION=0
the framework will migrate all the way down to zero - all the down() functions of all the migration scripts are executed, and all the schema modifications that were done from the first to the latest version will be reverted.
- by specifying the version number you want to migrate to. For example, the command
- Other useful stuff: here are some of the other stuff you can do with ruckusing:
- php main.php db:version - returns the current version of your schema
- php main.php db:schema - dumps the current schema
Simple, right? Much easier than managing all the sql files you've created for all your schema modifications, syncing databases accross machines, keeping track of what schema version you are on, etc. For more details, visit http://code.google.com/p/ruckusing/ and https://github.com/ruckus/ruckusing-migrations.
:D
