Database version control / synchronization plugin

[eluser]ciscoheat[/eluser]
Hi guys, again I'm looking for public interest in a library. This time I'm tackling what I think is one of the hardest developing problems, database version control. I'm sure you know how tricky it is. Synchronizing a version control system like SVN with a database structure (and data), how tricky isn't that? Having sql statements in a text file feels complicated and a bit awkward. And there is no way to automatically update it. I've searched, but found no better automated solution. So my solution is this:

Create a XML file with version elements, containing the modifications. Like this:

Code:

<?xml version="1.0" encoding="UTF-8"?>
<dbversion>
    <version id="1">
        <alter column="customer.mail" renameto="customer.email" type="varchar(100)" />      
        <delete column="customer.registrationdate" />
    </version>
    
    <version id="2">
        <create index="customer.emailIndex" columns="email" unique="true" />

        &lt;!-- Create table must be done by execute so far --&gt;
        <execute>&lt;![CDATA[
            CREATE TABLE `new_table` (
             ...
            ) ENGINE=MyISAM;
        ]]></execute>
    </version>
</dbversion>

Using the DBVersion library I just made, then you can synchronize the database by simply doing this in a controller:

Code:

$this->load->library('CIDBVersion');

if(!$this->cidbversion->LatestDBVersion())
{
  $this->cidbversion->Synchronize();
}

...or if you want to just check what modifications will be made in the update:

Code:

$this->load->library('CIDBVersion');
$this->cidbversion->DryRun();

Right now, the library works fine using the mysql driver (others are untested), and it requires PHP 5.1.3 or above thanks to some reflection usage. I'm using abstract classes with quite simple method signatures, so extending to other drivers is easy.

So what do you say, is it interesting? If so, I'll work some more on XML structure testing (I'll probably add a schema if the validation works fine in PHP), and aim for a release in the very near future. Please post comments and questions!


/Andreas

[eluser]mironcho[/eluser]
Hi ciscoheat,
In my opinion such a library is going to be very useful and popular. Database VC and synchronization is very common problem which soon or later concerns every web developer.

So, I am looking forward to test first version

[eluser]ciscoheat[/eluser]
Hello mironcho, glad to hear that! I'll try to fix an xml schema, then it should be ready for a release. The hardest part is always the documentation... I hope you can cope with examples instead of a user guide.

[eluser]adamp1[/eluser]
This seems a really good idea. I know I have been looking at moving projects over to SVN and was thinking about the DB version control side of things. I think this could be a very good thing. I don't quite get the version elements inside an xml file. What I mean is if this is to be used with SVN then the SVN will do the versioning. The xml should first have a command to wipe all the tables and then re-add. Maybe. The issue with having double versioning is it might get complicated.

I don't know how that idea would work with upgrades since its not very good if it deletes table data. I suppose it could check the structure you have with the current structure and only add features & requests confirmation to remove/change tables/rows.

Will have to give this a go when its released

[eluser]ciscoheat[/eluser]
[quote author="adamp1" date="1200806039"]I don't quite get the version elements inside an xml file. What I mean is if this is to be used with SVN then the SVN will do the versioning. The xml should first have a command to wipe all the tables and then re-add.[/quote]

Hello! I understand what you mean with the version elements looking like "versioning within versioning", but I think they are needed, since you always need some kind of tracker which version the DB is currently at, which must check the xml file where to continue executing.

And as you say, deleting data is not always what you want. So I'm going for smaller alterations of the DB instead, using simple xml elements. Of course, the <execute> element is available if you're going for some heavy stuff.

I'm just finished with the schema, unfortunately I hit a limitation in the schema definition itself. I was aiming for a structure kind of like this:

Code:

<create index="tabell.indexname" columns="column1,column2" primary="true" />

But it turns out having different attribute groups is not supported, so I had to change the action elements to the slightly uglier (imo)

Code:

<createIndex name="tabell.indexname" columns="column1,column2" primary="true" />

Well, gotta go do some more work! Thanks for the feedback.

[eluser]ciscoheat[/eluser]
All right, DBVersion 0.9 is downloadable at http://codeigniter.com/wiki/DBVersion/
Get it while it's hot!

[eluser]ciscoheat[/eluser]
Updated with two new versions. Changelog:

v0.91
- XML schema now accepts underscores in table and column names.
- The DBVersion table for mysql won't be created if it already exists.
Also added index for the 'version' column.

v0.95
- The main DB adapter (dbversionci_pi.php) now highlights the SQL when a
column, table or index doesn't exist, and issues a warning when making
a dry run. This warning can be ignored if many versions are executed
and the later versions operates on structures that doesn't exist yet.

Update by overwriting the files in the "plugins" folder.

As usual, grab it from here: http://codeigniter.com/wiki/DBVersion/

[eluser]ciscoheat[/eluser]
A new version of DBVersion is released. Changelog:

v0.96
- The <createColumn> element now takes a "default" parameter, to specify default value for the column.

Download and installation instructions: http://codeigniter.com/wiki/DBVersion

[eluser]philpalmieri[/eluser]
thanks ciscoheat, this looks really promising - iim trying it out now for a new project, and will give you some feedback as we use it.

Phil

[eluser]philpalmieri[/eluser]
Also, maybe i just don't see it... is there no createTable declaration? If starting from scratch, how do i create a new table? just use execute?

Thanks,
Phil