This tutorial will demonstrate how to use the DooManageDb and DooUpdateDb classes to manage your projects database.

These classes have been developed to allow your application(s) to be independent of the database your users choose to use by allowing standard database definition to be used to define your database which is then translated into the SQL needed for the database engine the user is using. It also supports easy upgrading (and downgrading) of the database to allow for better version control support and rolling out of updates and unit testing.

In order to follow this tutorial you will need to use the latest version of DooPHP avaliable form the SVN repository (or version 1.3 or above – not out at the time of writing). The current implementation only supports MySQL and PgSQL at the moment so you will also need one of these 2 databases in order to follow the guide.

The first thing we need to do is to create a simple controller to run our tests from. Therefore create a new controller in your protected/controller/ folder. In this example I will use the controller “DatabaseController”. Now enter the following into the controller:

<?php
class DatabaseController extends DooController {

	public function updateDatabase() {

		echo '<h1>Update Database to Head</h1>' . PHP_EOL;

		/* DB Updater Code will go here later */

		echo '<p>Done Updating the Database</p>';
		exit;
	}
}
?>

For this demo I am not going to be using the templating engine as I want to keep it focused on the database management classes.

Now we have a controller we will add a route to it to our protected/config/routes.conf.php file so we can run the update function. Therefore open up the routes configuration file and add the following route

$route['*']['/db/update'] = array('DatabaseController', 'updateDatabase');

You should now be able to access this route and test it out. On my system this would be “http://localhost/db/update/”. If you do not see the Update Database To Head message you need to go back and ensure your controller is working before continuing.

Assuming your route and controller are working we can setup our database updater. The DooDbUpdater class is an abstract class because you MUST extend it before you can use it. Therefore we will create a new class MyAppDbUpdater in protected/class/MyAppDbUpdater.php which will contain the following code.

<?php

// Load the Required Classes
Doo::loadCore('db/manage/DooDbUpdater');
Doo::loadCore('db/manage/DooManageDb');

class MyAppDbUpdater extends DooDbUpdater {

	/*
	 * Set allows us to pickup the lastest update avalaible.
	 * Eg If you have 10 update functions defined this should be 10
	 */
	protected $latestVersion = 1;

	protected function upgrade_1() {
		$this->db->createTable('user', array(
	  								'id' => array(
	  									'type'    => DooManageDb::COL_TYPE_INT,
	  									'require' => true,
	  									'autoinc' => true,
	  									'primary' => true
	       							),
									'username' => array(
										'type' => DooManageDb::COL_TYPE_VARCHAR,
										'size' => 20,
										'require' => true,
									),
									'password' => array(
										'type' => DooManageDb::COL_TYPE_CHAR,
										'size' => 32,
										'require' => true,
									)
								)
							);
	}

	/* Undoes actions carried out in upgrade_1 */
	protected function downgrade_1() {
		$this->db->dropTable('user');
	}
}
?>

The first thing we need to do is define the number of update_* functions their are. So each time you add an upgrade function you should set this to reflect the new number of upgrade functions.

The next thing we do is define our first upgrade rule. The upgrade rules can contain any php code you want to run but will normally be one of the $this->db->* functions which are createTable(tableName, columnDefinitions), dropTable(tableName), addColumn(tableName, columnName, columnDefintion) and dropColumn(tableName, columnName). In this case we create a new users table which has the fields id, username and password. In the column definitions we specify that the id column is an auto incrementing number and should also be a primary key. We also specify that all 3 fields are required.

The last part of the class is the downgrade function. The downgrade function should contain to opposite command to the upgrade function so if the upgrade function creates a table the downgrade function should drop this table. If it had added a column then this should drop it so that the database will be back to the state it was in before the upgrade was called. So here we drop the users table.

It is also worthwhile only carrying out 1 database operation in each upgrade / downgrade function. This prevents the database getting out of sync if one update or down grade should fail 1/2 through. Ie. If you create 2 tables and the first works and the second does not you can not run the upgrade again because the first table now exists. Also the downgrade will fail as not both tables will exist.

The upgrader does log which version upgrade / downgrade last executed successfully so when it runs a series of updates and one fails it can continue from where it happened to fail.

The create table function requires you to provide it with the name of the table to be created and then the column defenitions. The column defenitions are formatted as:

array(
	'COLUMN_ID' => array(
		'type'    => DooManageDb::COL_TYPE_*
		'size'    => (int) size limitation for char|varchar|numeric
		'scope'   => (int) decimal places for numeric
		'require' => (bool) is the value required ie. NOT NULL
		'default' => (mixed) default value for this column
		'autoinc' => (bool) is this value to be auto incremented?
		'primary' => (bool) is this a primary column?
	),
	'Column_Id_2' => array( ... );
)

The column types supported are

COL_TYPE_BOOL       : A true or false boolean
COL_TYPE_SMALLINT   : 2-byte integer (-32,767 to 32,768)
COL_TYPE_INT        : 4-byte integer (-2,147,483,648 to 2,147,483,647)
COL_TYPE_BIGINT     : 8-byte integer (about -9,000 trilllion to 9,000 trillion)
COL_TYPE_DECIMAL    : Fixed point decimal of specific size (total digits) and scope (num digits after decimal point)
COL_TYPE_FLOAT      : A double-percision floating point decimal number
COL_TYPE_CHAR       : A fixed length string of 1-255 characters
COL_TYPE_VARCHAR    : A variable length string of 1-255 characters
COL_TYPE_CLOB       : A large character object of up to about 2Gb
COL_TYPE_DATE       : an ISO 8601 date eg. 2009-09-27
COL_TYPE_TIME       : an ISO 8601 time eg. 18:38:49
COL_TYPE_TIMESTAMP  : an ISO 8601 timestamp without a timezone eg. 2009-09-27 18:38:49

The addColumn function is very much the same but you specify the table name, column id and then the column definitions. This should look something like this:

$this->db->addColumn('user', 'lastloggedin', array(
	'type' => DooManageDb::COL_TYPE_TIMESTAMP,
	'require' => true,
	'default' => '2009-09-27 00:00:00'
));

Drop Table and Drop Column are as follows:

$this->db->dropColumn('user', 'lastloggedin');
$this->db->dropTable('user');

The $this->db is also NOT the same as the Doo::db() instance and is instead an instance of DooManageDb.

Now we have out database update class defined we need to make use of it. Therefore go back to the DatabaseContoller we defined earlier and update the updateDatabase function as follows

<?php
class DatabaseController extends DooController {

	public function updateDatabase() {

		echo '<h1>Update Database to Head</h1>' . PHP_EOL;

		$dbUpdater = null;

		Doo::loadClass('MyAppDbUpdater');

		// Create an instance of the updater. Pass in the following
		// Location to write the db version files, the db configuration and the app_mode
		$dbUpdater = new MyAppDbUpdater(Doo::conf()->SITE_PATH . 'protected/config/dbversioning/',
										Doo::db()->getDefaultDbConfig(),
										Doo::conf()->APP_MODE);

		// Use Debugging?
		if (Doo::conf()->APP_MODE == 'dev') {
			$dbUpdater->enableDebugging(true);
		}

		try {
			$dbUpdater->updateToLatestVersion();
			//$dbUpdater->updateToVersion(1);
			echo "<p>Update Completed. Database Version Now : $dbUpdater->getCurrentDbVersion()</p>";

		} catch (Exception $ex) {
			echo "<p>Error Updating<br />$ex->getMessage()</p>";
		}

		if (Doo::conf()->APP_MODE == 'dev') {
			echo '<pre>';
			echo implode("\n\n", $dbUpdater->db->get_sql_history());
			echo '</pre>';
		}

		exit;
	}
}
?>

If you now visit the webpage http://localhost/db/update/ then you should have the new table successfully added to your database. Please note you should use this on an empty database and have conigured your database settings in db.conf.php before running.

You can also run function on the db udpater to downgrade the database. These include:

• updateToLatestVersion(); – Update to the latest version as determined by $latestVersion
• revertToStart(); – Downgrade back to version 0 (an empty database)
• updateToVersion($version) – Update to the specified version from current version
• downgradeToVersion($version) – Downgrade to the specified version from current version
• getCurrentDbVersion() – The current database version

For a full list of supported options please check out the API Documentation.

As the updater accepts the application mode it can be used to manage both a development and a production database at different versions

If you have any questions please use the forums to ask any questions rather than commenting directly on this article.