DBVersion

Category:Libraries | Category:Libraries::External | Category:Libraries::Database

DBVersion is a database versioning tool, used to keep track of database changes. It can also be used to make sure your development and production database are synchronized!

Installation instructions

1. Download DBVersion here: File:dbversionci-0.96.zip, unzip contents to the application folder.

2. Dump your current database structure and relevant data to a file, it will be version 0 of the DB.

3. Create a XML file that will be used to store the changes you make. Here's a template file with full syntax:

<?xml version="1.0" encoding="UTF-8"?>
<dbversion>
    <version id="1">
        &lt;!-- Actions --&gt;
        <createColumn name="customer.personalnr" type="varchar(20)" default="123" after="column" />
        <createIndex name="table.indexname" columns="column1,column2" primary="true" unique="true" />
        <dropColumn name="table.column" />
        <dropIndex name="table.indexname" />
        <dropTable name="table" />
        <alterColumn name="table.column" renameto="table.newcolumn" type="varchar(10)" />
        <alterTable name="table" renameto="nytabell" />
        <execute>&lt;![CDATA[
            INSERT INTO table (col1,col2) VALUES ...
        ]]></execute>
    </version>
    
    <version id="2">
        <dropColumn name="table.column" />
    </version>
</dbversion>

4. Save the XML file together with the sql dump.

5. Check out the configuration file for the CIDBVersion library, point it to the XML file and make sure you have a DB user that can ALTER, CREATE and DROP.

6. Execute a Dry run of the library, testing what the sql output looks like. Here's an example (from a controller) how to run it:

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

The default DB adapter will highlight the SQL when a column, table or index doesn't exist. Some warnings can be ignored if many versions are executed and the later versions operates on structures that doesn't exist yet.

7. If the xml validates and the output looks good, get ready to update! It's very simple as well, here's a code snippet:

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

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

As you see, the LatestDBVersion() method can be used to test if the DB is up-to-date.

8. For every new version you make on your database, update the XML file with a new version element, and corresponding actions. Also, update the dbversion table with a new version field.

9. All you need to do to synchronize another database with the DBVersion library installed is just to send the updated XML file to that server, and run Synchronize() there.

Forum discussion

The forum thread for DBVersion is here:http://codeigniter.com/forums/viewthread/69303/, please post your comments, ideas or other things. Thank you!

Category:Contributions::Libraries::Database