A quite popular feature in Rails are migrations, a kind of version control system for database schemes. Inspired by this feature, Joel Moss started a while ago the CakePHP migrations project to bring this feature to the CakePHP world.

In the meantime there is also a migrations-like feature in the Cake core, even though it seems to be a bit half-baked (or maybe I’m too stupid to understand it *g*).

Anyway, let’s have a look at it by using the traditional blog example.

First, we create a file which will contain the definitions for our schema. We name the file blog.php and place it in app/config/sql. In this file we create a class BlogSchema:

class BlogSchema extends CakeSchema {
}

Each property of a schema class represents a table, i.e. the property name is the table name, and the property value contains the table definition as an array. The definition for our posts table looks like:

class BlogSchema extends CakeSchema {
    public $posts = array(
        'id' => array('type' => 'integer', 'null' => false, 'default' => NULL, 'key' => 'primary'),
        'title' => array('type' => 'string', 'null' => false, 'length' => 100),
        'content' => array('type' => 'text', 'null' => false),
        'indexes' => array('PRIMARY' => array('column' => 'id', 'unique' => 1))
    );
}

Now, we can switch to the command line and make a test run to verify the schema script will execute the correct SQL statements:

cd cake/console
./cake schema run create blog -dry

---------------------------------------------------------------
Cake Schema Shell
---------------------------------------------------------------
Performing a dry run.

The following tables will drop.
posts

Are you sure you want to drop the tables? (y/n)
[n] > y
Dropping tables.
Dry run for posts :
DROP TABLE IF EXISTS `posts`;

The following tables will create.
posts

Are you sure you want to create the tables? (y/n)
[y] > y
Creating tables.
Dry run for posts :
CREATE TABLE `posts` (
        `id` int(11) NOT NULL AUTO_INCREMENT,
        `title` varchar(100) NOT NULL,
        `content` text NOT NULL,
        PRIMARY KEY  (`id`)
);

End create.

Everything looks fine, so we can run the same command again, this time without the -dry parameter, and it should create the posts table.

Ok, now let’s add a new column “created” to our schema:

class BlogSchema extends CakeSchema {
    public $posts = array(
        'id' => array('type' => 'integer', 'null' => false, 'default' => NULL, 'key' => 'primary'),
        'title' => array('type' => 'string', 'null' => false, 'length' => 100),
        'content' => array('type' => 'text', 'null' => false),
        'created' => array('type' => 'datetime'),
        'indexes' => array('PRIMARY' => array('column' => 'id', 'unique' => 1))
    );
}

To introduce the new column we could use the command from above, which drops the table and recreates it. Or we could use the update command, which performs an “ALTER TABLE” statement to synchronize the posts table in the database with the posts table from the schema file:

./cake schema run update blog

---------------------------------------------------------------
Cake Schema Shell
---------------------------------------------------------------
Comparing Database to Schema...

The following statements will run.
ALTER TABLE `posts`
        ADD `created` datetime DEFAULT NULL;

Are you sure you want to alter the tables? (y/n)
[n] > y

Updating Database…
posts updated.
End update.

With that, our blog database is up-to-date.

Unfortunately, the update command doesn’t work if you add a new table to the schema file, it doesn’t create this new table in the database. And so I think the migration process is currently not that useful in practice…

I used a question mark in the title of this article, as I am not sure if what I described here is really the correct way to use the schema feature. Please correct me, if I am wrong :)