Main Page

From DBUpgrade

[edit] Open Source

Code repository is now hosted on Github: http://github.com/sergeychernyshev/DBUpgrade

Old Subversion repository on Google Code: http://code.google.com/p/dbupgrade/

[edit] Issue tracker

Please feel free to submit issues and take on them in the issue tracker: https://github.com/sergeychernyshev/DBUpgrade/issues

Also check out old issue tracker (the goal is to migrate it all to one on Github)

[edit] Installation

Use git to get the code:

git clone git@github.com:sergeychernyshev/DBUpgrade.git dbupgrade

Or you can still use Subversion:

svn checkout http://svn.github.com/sergeychernyshev/DBUpgrade.git dbupgrade

[edit] Usage

Copy dbupgrade/dbupgrade.php to your application folder and add your migrations to $versions array.

Then update DB credentials at the bottom of the file.

$dbupgrade = new DBUpgrade(
        new mysqli( 'localhost', '...user...', '...pass...', '...dbname...'),
        $versions
);

Use optional third parameter to define namespace for your structure in case your code uses multiple projects that utilize DBUpgrade. This will allow them all co-exist in the same database as schema versions will be stored in separate tables.

$dbupgrade = new DBUpgrade(
        new mysqli( 'localhost', '...user...', '...pass...', '...dbname...'),
        $versions,
        'myapp'
);

[edit] Command line

If you noticided, dbupgrade.php boilerplate includes dbupgrade/client.php to add simple command line and web interface functionality so now all you need to do to upgrade your database to the latest version is to run

php dbupgrade.php

If you'd like to downgrade to previous version, you can also run

php dbupgrade.php down

Note: downgrading doesn't work if you run the script using web browser because we don't want anyone to be able to downgrade your database structure.

[edit] In your code

dbup() function can be used as part of your application to automatically upgrade your schema when new migrations are added.

In this case, you don't need config.php as all it's doing is setting up MySQLi database object and migrations array

Currently, functions spit out status messages, but they will be off by default in the future versions.

[edit] Migrations

Migrations are defined as array of versions with array index corresponding to schema version.

Each entry in the array has two keys - 'up' and 'down' representing upgrade steps and downgrade steps. These keys map to SQL statements (either a single string or an array of strings) that comprise the migration steps.

Downgrade steps should be a reverse of upgrade steps in order for DBUpgrade to seamlessly downgrade back to the previous version of data.

[edit] Example

Single string SQL statements:

$versions[1] = array(
        'up' => "CREATE TABLE `table1` (
                 `id` bigint(20) unsigned NOT NULL auto_increment,
                 `field1` VARCHAR(25) NOT NULL,
                  PRIMARY KEY (`id`)
                 )",
        'down' => 'DROP TABLE table1'
);

Arrays of SQL strings:

$versions[2]['up'][] = 'ALTER TABLE table1 ADD `field2` VARCHAR(25) NOT NULL';
$versions[2]['up'][] = 'ALTER TABLE table1 ADD `field3` VARCHAR(25) NOT NULL';

$versions[2]['down'][] = 'ALTER TABLE table1 DROP `field2`';
$versions[2]['down'][] = 'ALTER TABLE table1 DROP `field3`';

[edit] Updates

Run svn update from time to time for periodic updates and follow @DBUpgrade on twitter.

You can also subscribe to the blog: http://www.sergeychernyshev.com/blog/category/dbupgrade/

[edit] Roadmap

This section will contain some sort of roadmap with links to bug tracker.

When naming milestones, have the name that voices a purpose of that milestone. It will make it harder to add features that do not belong in the milestone, improving agility of the project.

[edit] v0.1 "Atomicity"

Goal for this version is to allow rollback even on broken migrations to help reduce problems with rolling back half-way upgrades during development (or production for that matter).

The solution is simple - to make changes atomic (introducing change number in changeset) so atomic upgrade is paired with atomic downgrade and successful increment is tracked in addition to the changeset version number (currently the only number tracked).

This milestone is tracked in issue tracker under the label: v0.1Atomicity

[edit] v0.2 "Downgrade"

Goal for this version is to be able to downgrade the database without having all the changesets in the code.

This is a common problem if you roll back the code from code repository, it doesn't have the changes needed to downgrade (they were not developed at that moment). This makes it impossible to downgrade without having latest code (containing all downgrade queries in changesets).

The solution is simple - store changesets in the database itself, this way database will store all information that is required to downgrade it back to the latest level that code can work with.

This can be done by simply inserting queries into the table as schema-only dumps don't have this problem (if no data is stored, it can be recreated by upgrading from empty database).

This milestone is tracked in issue tracker under the label: v0.2Downgrade

Main Page

Contents