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! [h2]Installation instructions[/h2] [b]1.[/b] Download DBVersion here: File:dbversionci-0.96.zip, unzip contents to the application folder.

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

[b]3.[/b] 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>

[b]4.[/b] Save the XML file together with the sql dump.

[b]5.[/b] 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.

[b]6.[/b] 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.

[b]7.[/b] 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.

[b]8.[/b] 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.

[b]9.[/b] 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. [h3]Forum discussion[/h3] The forum thread for DBVersion is here:[url=http://codeigniter.com/forums/viewthread/69303/]http://codeigniter.com/forums/viewthread/69303/[/url], please post your comments, ideas or other things. Thank you!

Category:Contributions::Libraries::Database