I was talking to the guys in the Kohana forum and the subject of “why should I use timestamped-migrations instead of minion’s migrations” came up. Why indeed. So I decided to up the game a bit. Doing funky stuff like reading the changes from the local database and then syncing those back to production is a bit too fragile for my tastes, and at least with RDBMS i want to be really sure what’s going to happen and what SQL queries will run, so I’m trying to reduce the “magic” as much as possible. There is however a lot of room for improvement, so what I ended up implementing was rails-style parsing of the filename to generate.

Migration Name Parsing

So imagine you want to make a migration like this:

1

php kohana db:generate add_created_at_and_title_to_users

Now, the tool should be savvy enough to read this filename and at least guess what you want to accomplish. And now it is. Behold:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

<?php
class Add_Created_At_And_Title_To_Users extends Migration
{
  public function up()
  {
      $this->add_column('users', 'created_at', 'string[255]');
      $this->add_column('users', 'title', 'string[255]');
  }
  
  public function down()
  {
      $this->remove_column('users', 'title');
      $this->remove_column('users', 'created_at');
  }
}
?>

It’s not perfect, and you still have to tweak it a bit (change the type of created_at to a timestamp maybe) but it’s a whole lot easier than having a blank migration file.

But where this functionality really shines is when you want to drop some tables / columns.

1

php kohana db:generate drop_table_roles

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

<?php
class Drop_Table_Roles extends Migration
{
  public function up()
  {
      $this->drop_table('roles');
  }
  
  public function down()
  {
      $this->create_table('roles', array(
          'name' => array( 'string[32]', "null" => false),
          'description' => array( 'string[255]', "null" => false)
      ), array( 'engine' => 'InnoDB' ));
  }
}
?>

You no longer have to go through the current database to write your own down statement, this data is accessible to the tool, so it does the work for you. Of course if the table does not exist it behaves like normal and you still have to write your own down statement.

So Available patterns right now are:

• create_table_{table}
• drop_table_{table}
• add_{columns}_to_{table}
• remove_{columns}_from_{table}
• change_{columns}_in_{table}
• rename_table_{old_name}_to_{new_name}
• rename_{old_name}_to_{new_name}_in_{table_name}

And you can write several {columns} if you separate them with _and_ and you can also write several patterns, separated with _also_ :

1

php kohana db:generate drop_table_roles_also_add_name_and_title_to_authors

It will order the statements in the up and down methods of the migration correctly too.

Dry Runs

There’s now the ability to run the migrations and see what they do, without actually doing anything. Just add a –dry-run:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

php kohana db:migrate --dry-run
1321124936 Add_Created_At_And_Title_To_Users : migrating up -- Dry Run
-- [dry-run]add_column( users, created_at )
   --> 0.0000s
-- [dry-run]add_column( users, title )
   --> 0.0000s
1321124936 Add_Created_At_And_Title_To_Users : migrated (0.0001s)
1321124960 Drop_Table_Roles_Also_Add_Name_And_Title_To_Authors : migrating up -- Dry Run
-- [dry-run]drop_table( roles )
   --> 0.0000s
-- [dry-run]add_column( authors, name )
   --> 0.0000s
-- [dry-run]add_column( authors, title )
   --> 0.0000s
1321124960 Drop_Table_Roles_Also_Add_Name_And_Title_To_Authors : migrated (0.0001s)

So no worries.

Execute SQL

After adding the dry run ability the need for a dedicated method to execute arbitrary SQL arose - so it will not execute on the dry run. You can now do:

1

$this->execute( "INSERT INTO `roles` (`id`, `name`, `description`) VALUES(1, 'login', 'Login privileges, granted after account confirmation')");

And when you make a dry run this will not execute.

Bug Fixes

A nasty bug was fixed when when you specified already executed migration with –version it would execute all the others. Anyway, you should sleep happy now.

Check out the code: timestamped-migrations.