Spiga

MySQL Database versioning strategy

by Gabi Solomon

IF you just got into versioning your code then you are probably really happy with your acomplishment.
But do you have your database versioned ? This is the questioned asked in this here by Jeff Atwood and also asked by a lot of users on stackoverflow.

After reading a lot on this topic i decided on a strategy on how to keep my database under version control.
As a warning it isn’t the most easy strategy and it really takes some discipline from all the developers.

The strategy

The main ideea is to have a folder with this structure in your project base path
/__DB
—-/changesets
——–/1123
—-/data
—-/tables

Now who the whole thing works is that you have 3 folders:
Tables
Holds the table create query. I recommend using the naming “table_name.sql”.

Data
Holds the table insert data query. I recommend using the same naming “table_name.sql”.
Note: Not all tables need a data file, you would only add the ones that need this initial data on project install.

Changesets
This is the main folder you will work with.
This holds the change sets made to the initial structure. This holds actually folders with changesets.
For example i added a folder 1123 wich will contain the modifications made in revision 1123 ( the number is from your code source control ) and may contain one or more sql files.

I like to add them grouped into tables with the naming xx_tablename.sql – the xx is a number that tells the order they need to be runned, since sometimes you need the modification runned in a certain order.

Note:
When you modify a table, you also add those modifications to table and data files … since those are the file s that will be used to do a fresh install.

This is the main ideea.

Improvements you can do

This is the basic ideea of how i version my database structure. I am sure this is not the perfect way of versioning your database but this is the own that works for me.
If you want you can modify the structure and add 2 subfolder for each changeset : up & down … and this way you could have a way of upgrading and downgrading your database.

Automation

Since running those queries manually isn’t so enjoyable i wrote a small class to automate the process a little.
The class is posted on phpclasses.org and is available for download here.
Warning: The class is still in beta, so be careful when first using it. and always backup :) )

The main idea is that the class saves the last changeset runned in mysql and always checks if there are any new change sets. When you see any commits with database change sets you access /__DB/index.php and the class does the upgrade for you.

I await your comments
Cheers

  • http://codeutopia.net/ Jani Hartikainen

    Nice idea. Personally I’d prefer if the 1123 numering scheme would be more automatic, because it’s a bit tedious in my opinion to maintain such directory structure. Not sure how you could achieve that though.

    Also, rather than using your own script, how about using Phing for deploying the changes? Phing has a DbDeploy task type which could possibly be used for something like that. (see http://phing.info/docs/guide/current/ Appendix B)

    ps. I wanted to take a look at your script, but phpclasses wanted me to log in. I don’t have a phpclasses account and 99% of stuff from there isn’t worth it, so I kinda don’t want to make one… =)

  • http://www.gsdesign.ro/ Gabi Solomon

    @Jani
    I dont know how you can make that more automatic either.
    And i access the script to update my working copy .. .for stanging and production its automated. ( post-commit script )

    I will look into posting my classes elsewhere since there are a lot of people bothered ny the login … but dont expect to much of the class … it just reads the files and do a mysql querry :)

  • Gabriel

    Hi,

    I’ve been looking at this recently and have something similar but the changeset data is in a database table of the form:

    CREATE TABLE `system_upgrade` (
    `id` int(11),
    `from` decimal(5,2),
    `to` decimal(5,2),
    `type` enum(‘db’, ‘php’),
    `upgrade` text ,
    `comments` varchar(255),
    PRIMARY KEY (`id`)
    ) TYPE=MyISAM;

    Every time we make a change we put the SQL in this table and then use a web interface to upgrade other sites. Its in its infancy but works well.

  • http://www.gsdesign.ro/ Gabi Solomon

    but what do you do on production websites ? … do you still have that table

  • Gabriel

    At the moment the table exists in every installation.

    To do an update we dump the latest data from our development system and insert it into the table on the live installations.

    This is fine as the data is only ever created by the development team and will never be editable by anyone else.

    I am looking at improving things though by having a central database that all installations “phone home” to so we only have the data in one place.

  • http://pulse.yahoo.com/_N5JS3A4CLT6NA2JTZQ5NJSS5HE Syed

    how to check mysql version no http://www.geeksww.com/tutoria

  • Harish220886

    any one know correct code  foe converting doc file to pdf format