Planet CakePHP

As promised, the following is a tutorial on how to use and take advantage of Migrations in your CakePHP development. I suppose you could also call it a basic manual.

This article assumes that you already have an installation of CakePHP which is already configured and ready to go. It also assumes that you know what migrations are and why you need them. For more information on DB Migrations, please see here.
Getting started
Once you have installed and configured CakePHP, you should download the Migrations package from HomepageUniverse here. Unzip the archive into your vendors directory that is above your app directory.

Remember that you should place the migrations package in your top level vendors directory, not the vendors directory within your app directory.

Within the migrations directory are a number of files and two more directories. I will explain each here:

• migrate.php [file] - This is the main Migrations script.
• MDB2.php [file] - This is Pear's MDB2 database abstraction library.
• Spyc.php [file] - The Spyc YAML library (just in case you don't have the PHP Syck extension installed)
• MDB2 [directory] - The included MDB2 library files.
• Examples [directory] - A few example YAML migration files

The main file you should be interested in, is the migrate.php file. This is the script that you need to run on your command line, and helps you generate, run and manage your migrations.

To use it, simply run the following from within your cake root directory and follow the on screen prompts:
php vendors/migrations/migrate.php

Read on for more detailed description of its usage.
Migration Basics
I will quickly run through what migrations do and how they do it. This should help you understand how and why you should use them.

Database Support
All migrations let you do is run database queries in a structured and simple manner. Pear's MDB2 library is used to interface with your database, so you can use any database engine that MDB2 supports. Why do I use MDB2 when Cake has a very good database? Because Cake's DB classes do not support the creation of tables, fields, indexes, etc.

Migration Files
Migrations use a selection of migration files that are used to manage your database. You can roll forward and backwards through each file, just as you would with any version control system, such as subversion. The migration files are written in a very simple, human readable language called YAML, and do not need to contain any SQL queries at all. This way, migrations are 100% portable and can be used with almost any database engine. You can find some example migration files in the Examples directory of the download.

When you run a migration, the script simply analyses each migration file and converts the YAML into database queries and performs the actions specified in the file. The best way to demonstrate this would be to actually do it. So on we go to the meat of this tutorial.
The Migration Script
Just like Cake's Bake.php script, the migrate.php script must be run via the command line. You can do three things with the migrate.php script:

1. Generate Migration Files
2. Run Migrations
3. Reset your Migrations

We will go through each one in turn in the next section.

The migrate.php script also takes some optional command line arguments, but I will not go into them too much here. Just run the following for more help:
php vendors/migrations/migrate.php -h

The Generation Game
Before you can do anything else, you need to generate your migration files. You can of course create these manually yourself, but it's much easier and less error prone if you use migrate.php. So go ahead and run the migrate script;
php vendors/migrations/migrate.php

Assuming everything is good, you should see a lovely little 'CAKEPHP MIGRATIONS' banner in your command line window.

If you have not already configured your Cake database config, it will help you with that in the same way that bake.php does. So go ahead and follow the prompts if you need to do that.

After that, the script will then check that the 'schema_info' table exists in your database, and creates it if it isn't there. This table is used to keep a record of what migration version your app is currently at. So it is very important and should not be tampered with.

Once all that is done, you should see a few lines of data. The first line tells you which application [dir] you are using. Then it tells you your current migration version, along with the number of migration files previously generated.

Now choose 'Generate' from the three Options menu. Then just follow the prompts and your very first migration file will be created for you and placed in a migrations directory in your app's config directory. Take a look and you will see that the script has numbered your file. It will do this for every file generated and simply increment each time. This numerical value is used to determine what order your migrations should be run. It is basically the version number.
YAML, YAML, YAML
Before we get started on editing your migration file, I strongly recommend that you get acquainted with YAML. You should read a brilliant little tutorial called YAML in Five Minutes. And of course you can check out the official YAML site

I am going to try and explain a migration file as best I can. So please bear with me, if I splutter.

A migration file consists of two main parts; an UP and a DOWN. The UP section contains the commands that you would like performed when migrating up to this file. And the contents of the DOWN section are run when you rollback from or through this file to a previous version. So usually, the DOWN section contains an exact opposite of what is in the UP section, as you would usually want it to reverse the changes made in the UP section.

Lets start by creating a table. Enter the following into your migration file;
#
# migration YAML file
#
UP:
create_table:
users:
first_name:
type: text
index: true
last_name:
type: text
email:
type: text
unique: true
age:
type: integer
length: 3
notnull: false
DOWN:
drop_table: users

Directly under the UP section name, is the action that should be performed. We are creating a table, so we use 'create_table'. Then you tell the migrate script what we want to call this table. After which we create the fields by specifying each field name and its properties. All properties are optional, apart from the 'type', which is required.

Available field types are:

• text
• integer
• blob
• boolean
• float
• date
• time
• timestamp (datetime)

You can then set the following optional properties:

• length
• notnull
• default
• index
• unique
• primary

If you notice in the example above, I have not specified a primary key on any of the fields, and there is no ID field. That is because the migrate.php script will create an ID field for you and set it as the primary key. You can overide that if you wish. Just add a line directly under the UP line like this:
UP:
create_table:
users:
no_id: true
first_name:
type: text
index: true
last_name:
type: text

....

If you do use the 'no_id' line, you will need to set the primary key and ID field yourself.

Now we need to define the DOWN section. All we want to do is to reverse the actions of the UP section. So we simply say:
DOWN:
drop_table: users

In both sections, you can create/drop more than one table. To create another table, just create another create_table section starting from the table name. To drop more than one table, do this:
DOWN:
drop_table: [users, table2, another_table]

And that my friends, is your very first migration file. I won't go into the details of all the actions that migrations can do as the Examples are pretty good at doing that, and this tutorial is already pretty long.

Other actions available:

• alter_field
• add_field
• drop_field
• query

Pretty self explanatory really, but the last one; 'query', lets you run a raw SQL query. So you can use it to insert/update data. Something like:
query: INSERT INTO users SET first_name='Joel'

Run it!
Now we get to the exciting part. We run the migration.

Start the script again and accept the default option: "Migrate". It will then give you a list of migration files along with their version number. You only have one file, so type the number of that file, which should be '1'.

Ta Da!! migrate.php will then fetch and parse the file and run the commands on your database. Now check your database and you should see a spangly new table ready for your app to use.

But what happens if you don't like that table and want to change it. You got two options; roll back the version or generate and run a new migration file with the changes.

To rollback the changes, just run migrate.php, accept the default option and enter the number '0'. This happens to be the version prior to the migration you just made, and is also the base. So choosing that will rollback all migrations back to the beginning. Try it now. You should now notice that your table has been dropped.
Is that good or wot?
That is pretty much it. The last option: 'Reset', will do exactly that. It will reset all your migrations and optionally delete your migrations files. So be careful when using that.

Play around with it and create a forum post here if you have any questions, bug reports or suggestions.

Thanks again and look out for more [shorter] tutorials and maybe a few screencasts in the near future.