One of the more annoying challenges involved with managing production web applications is keeping the development and production versions in sync. Verson control systems such as Subversion and CVS make this task trivial for source code and file system changes, but changes to the database schema between versions still need to be handled with care, as these changes fall outside the watch of any version control system.

For example, suppose I am running an application that stores user profile information, and I want to add a column to the “users” table to store the user’s postal code. I add the column to my development database but I don’t want to add it to the production database until I am finished with the rest of my changes.

The old way: Copy & Paste – text files

The old way managing these changes was to make the change in the development database, then copy and paste the SQL query that I used to perform the update into a text file. I would repeat this process for each change that I made. When it came time to move the changes to the production application, I would just execute these statements manually one by one on the production server.

The down-side of this approach is that it didn’t scale very well. It works OK if I only have one production installation and one development server. But what if I have dozens of production servers all running the same application, and perhaps running different versions. It would become cumbersome if not impossible to keep track of all of these changes and manually apply them across all installations.

The new way: Xataface Application Versioning

Xataface allows you to track the version of your application with a text file named version.txt stored in your application’s directory. This file should contain one line like with two numbers separated by a space:

1.0b1 345


This example means that the application version is 1.0b1, and that the build version is 345. The build version must be an integer that is incremented every time there is a change to the source code. It is used by Xataface to figure out whether the file system version matches the database version. A good practice is to just use the SVN revision number for the build version.

On every page request, Xataface checks the version.txt file to see what version of application is currently in the file system. It compares this with the version of the database. If the database version is lower, it will execute the necessary queries to update the database to the current version.

The conf/Installer.php file

Xataface looks for a class named conf_Installer located in your application’s conf/Installer.php file to find out what it needs to do to update between versions. You can define methods in this class of the form:


function update_##(){}


Where ## is the build number of the update.

Xataface will execute all functions update_XX() to update_YY() in your conf_Installer class automatically if it finds that the database version is XX and the filesystem version is YY. This is where you can place your database updates that need to be performed between versions.

For example, suppose the production server is running build version 345. That means that the version.txt file in your production server might look something like:

0.5.1 345


Now you want to add a postal_code column to the users table in the development version, so you’ll increment the version number on the development server:


0.5.2 346


And add a method to your conf/Installer.php file to perform the database change:

<?php
class conf_Installer {
  function update_346(){
    $sql[] = 'ALTER TABLE `users` ADD `postal_code` VARCHAR(32) AFTER `phone_number`';
    foreach ($sql as $q){
      mysql_query($q, df_db());
    }
  }
}


Then you can just update the source files to the production server using subversion. The first time you run the production app after updating the source files you’ll get a message saying that the application has been updated to version 346.

That’s all it takes. You just keep on adding these methods for each update. Then even if you have an instance that is a couple of versions behind, all you need to do is update to the latest source revisions, and it will automatically update the database to the correct version.