Using a PHP Shell to Simplify Database Migration

During a development cycle, as I make changes to the database, I log those changes in tweaks.sql. Some of these changes might be structural (adding a country column) and some of them might be data related (replacing all occurrences of USA with US.) When it comes time to do a deployment, I just need to walk through tweaks.sql and execute the various statements that are present. When I'm done, the database is essentially migrated for the release.

Simple enough, right?

This morning though, I ran into an annoying piece of database migration. I wanted to set a particular user property on every user that didn't already have one set. The tricky part comes into play that properties are stored as individual rows of a user_properties table, which has multiple rows relating back to a single users table.

I could have done some funky stored procedure work to detect if the property was set, and if not, do an insert of a row, but I thought there had to be a better way. After all, while this is clunky SQL to write, it's trivial PHP to write.

Which got me thinking: if I can sit at a MySQL prompt and execute statements to migrate the database, why can't I do the same thing at a PHP prompt? Surely someone has developed a PHP based shell, right?

After a few moments of searching, I learned that if you kick off PHP with -a you automagically get a shell. Unfortunately, my system didn't have PHP compiled with readline (or perhaps some other dependency was missing), so the built in version of PHP shell was pretty useless. Not to worry, another moment of poking around and I learned about phpsh.

After a couple minutes of fiddling with install, I was able to type:

 phpsh lib/config.php

And Bam! I had an interactive shell, or REPL, that made every function of my system available to me. This is amazing. How have I gone this long without investigating this option?!

Anyway, with phpsh running, I was trivially able to write some code that does the migration that I need. It's something like:

  foreach(all_users() as $u) {
    if(user_prop($u, 'editor.type')) {
      continue; // Don't set it, they've got a value
    }
    set_user_prop($u, 'editor.type', 'emacs');
  }

I dropped this code into tweaks.sql and I'm good to go. Next time I do the migration, when I get to this block, I'll know that I have to kick off phpsh and enter the code there instead of the MySQL prompt. Should be a piece of cake.

Personally, I prefer to manually execute the statements in tweaks.sql to be sure that the database is migrated properly. So this whole mixing of SQL and PHP works for me. If I had automated the execution of tweaks.sql, it would be fairly straightforward to put some tags around the PHP block, and have the migration script send this code to phpsh rather than mysql. Though, for my purposes, that be overkill.

Now that I've got my shiny new shell/REPL, I wonder how else I can put it to use? If there's one thing I'm sure of, it's that REPLs Rock!