Main Page
From DBUpgrade
DBUpgrade is a simple open source tool for MySQL schema upgrade management.
DBUpgrade is written in PHP and uses MySQLi library (not compiled in by default)
Contents |
[edit] Open Source
Code repository and issue tracker are hosted on Google Code: http://code.google.com/p/dbupgrade/
[edit] Installation
Use Subversion to checkout the latest code like this:
svn co svn checkout http://dbupgrade.googlecode.com/svn/trunk/ dbupgrade
[edit] Usage
[edit] Command line
Copy config.sample.php to config.php and update database connection string.
Use up.php and down.php to upgrade and downgrade the database.
Example:
php dbupgrade/up.php
[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 ($versions array)
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] = array(
'up' => array(
'ALTER TABLE table1 ADD `field2` VARCHAR(25) NOT NULL',
'ALTER TABLE table1 ADD `field3` VARCHAR(25) NOT NULL'
),
'down' => array(
'ALTER TABLE table1 DROP `field2`',
'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
