in Tutorial minutes

Get Started With CakePHP 3 – Database Migrations

CakePHP 3 (and later versions of CakePHP 2) have added command line tools to create database migrations. What are migrations? Basically it’s just a program that creates your SQL tables for you and it’s a whole lot less verbose than regular SQL. Some of the other advantages are: it’s regular PHP code, so you’re not looking up SQL syntax or clicking though PHPMyAdmin, plus it can be version-ed with the rest of your app – good if another developer need to re-build your app on their machine.

The official docs are here: http://book.cakephp.org/3.0/en/migrations.html As a side-note, CakePHP’s migrations wrap the excellent Phinx.org library, and throws in some of the Cake-specific defaults.

First check if it’s already installed (CakePHP 3.1 seem to have it installed by default). If you run cake bake you should see a list of available commands, if you don’t see migrations in there, follow the instructions on cakephp.org to install.

Let’s start by creating a migration:

cake bake migration CreateFilmVotes film:string vote_1:integer vote_2:integer vote_3:integer vote_4:integer vote_5:integer created modified

Basically we’re creating a new table (film_votes) with a the name of the film and columns with the number of audience members who gave the film a rating of either 1,2,3,4 or 5; the created and modified are the regular CakePHP date fields.

Tip: You can find a list of valid column types here : http://book.cakephp.org/2.0/en/console-and-shells/schema-management-and-migrations.html

When you run this command, a new file will be created in config/Migrations folder.

Now one thing I realized is the name field is too large, I haven’t found a CLI command to alter existing fields yet, so I’ll just go into the code and change limit to 50 character like so:

public function change()
    {
        $table = $this->table('film_votes');
        $table->addColumn('film', 'string', [
            'default' => null,
            'limit' => 50,
            'null' => false,
        ]);
...

You can see the full code over at GitHub

Also, lets add a description too: cake bake migration AlterFilmVotes description:text

We’re going to throw this next part out later, but it’s worth knowing it can be done.

As you’d expect, running that line will create a new migration file that is adding a new column to the existing table:

public function change()
    {
        $table = $this->table('film_votes');
        $table->addColumn('description', 'text', [
            'default' => null,
            'null' => false,
        ]);
        $table->update();
    }

Now, let’s actually make a database table, with this: cake migrations migrate

You should see something like:

using migration path C:\xampp3\htdocs\cpt-filmrating\config\Migrations
using environment default
using adapter mysql
using database cpt-filmrating

 == 20151025050608 CreateFilmVotes: migrating
 == 20151025050608 CreateFilmVotes: migrated 0.1510s

 == 20151026015610 AlterFilmVotes: migrating
 == 20151026015610 AlterFilmVotes: migrated 0.1610s

All Done. Took 0.4010s

And if you check your database, you’ll see something like this: mysql-1

Now this isn’t exactly what you want, since the description is stuck on the end, so lets run cake migrations rollback to undo the whole step.

Tip: if you’re wondering how the migrations is being kept track of, it’s in that table called phinxlog. If it seems like migrations aren’t happening, like if you’ve been deleting migration files a lot, you can always just empty out that table to reset things.

Since this the migrations are just code, we can just move the description into the original migration (when we used Create) and put the description right after the film name and delete thje second migration script (…alter_film_votes.php); run the migration again and you should see the description after the name. Lets also do some clean-up, like add full-text search to the description field, in case we want to search it at some point down the road (note, MYSQL’s fulltext fine for a few hundred films, but maybe not if we get into hundreds of thousands). While we’re on that, lets make the vote fields a bit shorter – a Small Int is probably fine and should be unsigned and the column should have a default of 0.

Here’s the MySQL code:

DROP TABLE IF EXISTS `film_votes`;
CREATE TABLE IF NOT EXISTS `film_votes` (
`id` int(11) NOT NULL,
  `film` varchar(50) NOT NULL,
  `description` text NOT NULL,
  `vote_1` smallint(6) unsigned NOT NULL DEFAULT '0',
  `vote_2` smallint(6) unsigned NOT NULL DEFAULT '0',
  `vote_3` smallint(6) unsigned NOT NULL DEFAULT '0',
  `vote_4` smallint(6) unsigned NOT NULL DEFAULT '0',
  `vote_5` smallint(6) unsigned NOT NULL DEFAULT '0',
  `created` datetime NOT NULL,
  `modified` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `film_votes`
 ADD PRIMARY KEY (`id`), ADD FULLTEXT KEY `description` (`description`);

Lets reverse engineer this, run cake bake migration_snapshot filmvotes and you’ll see a new file within the migrations folder. Open it up and you’ll see the familiar code that defines the database structure. This command is really handy if already have the database.

It’s worth noting, at some point migrations did get confused – the message missing table showed up, so I ended up having to clear phinxlog and run the generate command again.

Lets see what the SQL code looks like now, might be something missing:

DROP TABLE IF EXISTS `film_votes`;
CREATE TABLE IF NOT EXISTS `film_votes` (
`id` int(11) NOT NULL,
  `film` varchar(50) NOT NULL,
  `description` text NOT NULL,
  `vote_1` int(6) NOT NULL DEFAULT '0',
  `vote_2` int(6) NOT NULL DEFAULT '0',
  `vote_3` int(6) NOT NULL DEFAULT '0',
  `vote_4` int(6) NOT NULL DEFAULT '0',
  `vote_5` int(6) NOT NULL DEFAULT '0',
  `created` datetime NOT NULL,
  `modified` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `film_votes`
 ADD PRIMARY KEY (`id`);

ALTER TABLE `film_votes`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

We’re missing our full text search and the vote fields are not unsigned anymore. Not really a problem as not going to use the full text in this project, and since we’re expecting no more than about 500 votes, even with signed fields that still gives us room to store numbers up to about 32,000 , versus about 64,000 (in case you ever wonder where the Commodore 64 got it’s name, that it – and 65,535 might not have fit on the case…and since we’re on a tangent here, there actually was a Commodore 65 but that’s a whole other story that has nothing to do with CakePHP, so anyways…)

But lets say we want to add a signed, that’s easy: just add a signed key and set to false like:

->addColumn('vote_1', 'integer', [
                'default' => 0,
                'limit' => 6,
                'null' => false,
                'signed' => false
            ])

Rollback the migration and re-run, and the column is not signed. Maybe this can be done from the Cake’s CLI, but I haven’t found out how yet.

We’re still missing the full text index, and from what I’ve read, it’s not supported yet. If and when it’s supported, it would be something like: $table->addIndex($cls, [‘type’=>’fulltext’]).

That should give a bit of an introduction to Cake’s migration tools. Honestly I’m probably going to use the basic add command, then go into the generated file and set up the columns myself. Migrations is a really good idea, especially if you’re working in teams, but even for solo projects, it’s a set up from fiddling with raw SQL (and we haven’t even touched on moving from one flavour of SQL to another)

Next time we’ll look at populating that database…