Keeping your database up to date throughout development

A major development issue in any computer project that requires a database is keeping your database structure keep to date with the different versions of the code. Keep database up to date is always a difficult because you cannot simply put it under version (thou technically you can because you can create an sql statement that represents the entire database). But Xataface provides an interesting way to handle this through the use of an Installer.php (make sure it's capital I) class in the conf folder.

The idea behind this is your application will be associated with a version number which you specify in the database. And then Xataface will run the Installer.php file which checks which version is in the database, and then it will call a bunch of functions that update the database table. The functions are named after the version number they should be applied to. So for example:

<?php
class conf_Installer {
function update_582(){
$sql[] = 'ALTER TABLE `webpage_sites` ADD `dynamic` TINYINT(1) AFTER `require_approval`';
$sql[] = 'ALTER TABLE `webpage_sites` ADD `irrelevant_get_params` TEXT NULL AFTER `aliases` , ADD `relevant_get_params` TEXT NULL AFTER `irrelevant_get_params`';
foreach ($sql as $q){
mysql_query($q, df_db());
}
}
?>

What happens is it will see if the database version is 582 or above and then it will apply this change to the database. The this general function format can be used for any version numbers.

To actually setup the database version, we don't actually need to setup a separate database table to handle this. We make a version.txt file in the application and xataface will create a version table in the database for us. The contents of the file will look like this:

0.1 680

The 0.1 really means nothing. It is simply used to note which major release we are on. It is the second number that means something. That is the exact version number that gets stored into the database.

And by setting up these two things, it becomes extremely easy to manage the database in xataface!