JAdVA PHP Library

Jadva_Installer_Database

Introduction

The Jadva_Installer_Database helps you keep your database changes in order, by storing them in files, which the update script then can execute on the various database servers. It keeps a record of which scripts have been installed, and which haven't. It also creates a restore point before making any changes to the database, and restores the database to that point when anything goes wrong.

Getting started

You will need the JAdVA PHP Library. Download it, and unzip it ito a directory. The directory with the library we'll call jadva, make sure the jadva directory contains the library directory from the zip file. You'll also need a database to work on. In our example we'll work with a MySQL database. In theory it's possible for this installer to work on other databases, but those aren't in the library yet.

You'll also want to create a separate directory where you can put the files from this tutorial.

Setting up

To use the installer, you need to instantiate an instance of the Jadva_Installer_Database_Mysqli class, configure it, and then run the install function. You can easily store this in a separate PHP script for this purpose as follows.

update.php

Create the update.php file, and put the following contents in there:

<?php

if( empty($argv[1]) ) {
	die('Missing argument: installation scripts directory');
}

$directory = realpath($argv[1]);
if( !$directory ) {
	die('Could not interpret "' . $argv[1] . '" as a proper directory');
}

//Open the configuration file
$config = require_once './config.inc.php';

//Set up a standard TXT output formatter unless the configuration file says different
if( empty($config['outputFormatter']) ) {
	require_once 'Jadva/Installer/OutputFormatter/Txt.php';

	$config['outputFormatter'] = new Jadva_Installer_OutputFormatter_Txt;
}

//Instantiate the installer, and run it
require_once 'Jadva/Installer/Database/Mysqli.php';

$installer = new Jadva_Installer_Database_Mysqli($config);
$installer->addDirectory($directory)
	->install();

config.inc.php

As you can see, the update.php file refers to the config.inc.php file. Create it as follows:

<?php

set_include_path(
	'../../../../../library' . PATH_SEPARATOR
	. get_include_path()
);

return array(
	'credentials' => array(
		'username' => '',
		'password' => '',
		'host'     => '',
	),
	'restoreDirectory' => 'restore',
	'databaseName'     => '',
);

You'll need to fill in the database credentials for your own database server, including the database name. You'll also need to add the library directory from the zip file to the path.

The restore directory

Before each update, the installer makes a backup (a "restore point"). The config file above says to put those restore points in the restore subdirectory. Make sure you create that directory.

The scripts directory

You can add several directories to the installer, even though our script above only allows one directory. We'll assume you put all the scripts in the scripts subdirectory. You can run the update script as follows:

php update.php scripts

This will then update the database with the scripts.

The installation scripts

In order for this installer to work, there are a few requirements that are placed on the installation scripts.

The filename

The filename consists of three parts, separated by dots. The first part is the name of the script. It's usually my convention to name the script after the table it will install or update. It's then followed by a version number, and the database type it is for. In our case, the database is "MySQL", so the last part (or "file extension") would be "mysql".

Versioning

I mentioned that each filename contains a version number. This is to easily group updates for a single item together. These will always be executed in order. The first version of the script must have as version number 1, and each next script must increase the version number by one.

Requires

You may have scripts that depends on other scripts, especially if you use foreign keys. The database installer need to make sure that those scripts are executed before the scripts that depend on them are executed. You can do so by adding a small annotation to the script:

-- REQUIRES: users,1

In the above example, the script would require that the first version of the users script is executed before this script is.

Sometimes you have circular requirements, for example a mutual foreign key. In that case, you must delay adding one of those foreign keys to the second version of your script.

Putting it all together

We'll create two tables, a users table, and a user_logs table. The logs table will link with a foreign key to the users table. Create the following two files in the scripts directory:

users.1.mysql

CREATE TABLE users (
	id    INTEGER  UNSIGNED  NOT NULL  auto_increment,
	
	name  VARCHAR(32)  CHARSET  utf8    NOT NULL,
	pass  CHAR(32)     CHARSET  latin1  NOT NULL,
	
	PRIMARY KEY(id)
) ENGINE=InnoDB;

user_logs.1.mysql

-- REQUIRES: users,1
CREATE TABLE user_logs (
	id      INTEGER  UNSIGNED  NOT NULL  auto_increment,

	user    INTEGER  UNSIGNED  NOT NULL,

	action  TEXT     CHARSET utf8  NOT NULL,

	PRIMARY KEY (id),

	FOREIGN KEY (user) REFERENCES users (id)
		ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB;

As you can see, we've told the installer to install the users table before the user logs table by adding a comment.

Running the installer

As you run the update.php script, it'll output something like this:

[2011-11-19 11:03:33][info   ] Doing pre-install system check
[2011-11-19 11:03:33][info   ] Reading the directories
[2011-11-19 11:03:33][info   ] Retrieving the list of files
[2011-11-19 11:03:33][info   ] Checking for content
[2011-11-19 11:03:33][success] File list updated and sorted
[2011-11-19 11:03:33][info   ] Connecting to the database
[2011-11-19 11:03:38][info   ] Locking the database
[2011-11-19 11:03:38][notice ] MySQL cannot lock databases; proceed with care.
[2011-11-19 11:03:38][info   ] Creating a restore point
[2011-11-19 11:03:44][success] Restore point created, and stored in "restore\tut_jadva_dbins_mysql_20111119110338.mysql".
[2011-11-19 11:03:44][info   ] Checking version table
[2011-11-19 11:03:44][info   ] Retrieving current versions
[2011-11-19 11:03:44][success] Retrieved information about the current versions.
[2011-11-19 11:03:44][info   ] Updating the database
[2011-11-19 11:03:44][info   ] Updating script "users" to version 1
[2011-11-19 11:03:44][success] Update successful
[2011-11-19 11:03:44][info   ] Updating script "user_logs" to version 1
[2011-11-19 11:03:44][success] Update successful
[2011-11-19 11:03:44][success] Updated 2 scripts
[2011-11-19 11:03:44][info   ] Stored information about the new versions
[2011-11-19 11:03:44][success] Done.

As you can see, it creates a restore point, and updates the database.

Making changes

Let's say we want to store the timestamp of the last action of a user, and their email address. The first version of the users script is already installed, so we can't change it anymore. We'll have to create a second file. Create the file users.2.mysql, and fill it with the following contents:

ALTER TABLE users
	ADD COLUMN  email  VARCHAR(255)  NULL  AFTER pass;

ALTER TABLE users
	ADD COLUMN  last_action  DATEETIME  NULL  AFTER email;

Since the file is called users.2.mysql, we don't need to add a note to depend on the version version, since that happens automatically. Of course, if we'd depend on a different script (for example a foreign key to an addresses table), then we'd need to add a REQUIRES comment.

Let's run the updater:

Messages from the installer
[2011-11-19 11:08:27][info   ] Doing pre-install system check
[2011-11-19 11:08:27][info   ] Reading the directories
[2011-11-19 11:08:27][info   ] Retrieving the list of files
[2011-11-19 11:08:27][info   ] Checking for content
[2011-11-19 11:08:27][success] File list updated and sorted
[2011-11-19 11:08:27][info   ] Connecting to the database
[2011-11-19 11:08:32][info   ] Locking the database
[2011-11-19 11:08:32][notice ] MySQL cannot lock databases; proceed with care.
[2011-11-19 11:08:32][info   ] Creating a restore point
[2011-11-19 11:08:37][success] Restore point created, and stored in "restore\tut_jadva_dbins_mysql_20111119110832.mysql".
[2011-11-19 11:08:37][info   ] Checking version table
[2011-11-19 11:08:37][info   ] Retrieving current versions
[2011-11-19 11:08:37][success] Retrieved information about the current versions.
[2011-11-19 11:08:37][info   ] Updating the database
[2011-11-19 11:08:37][info   ] Updating script "users" to version 2
[2011-11-19 11:08:37][err    ] MySQLi error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DATEETIME  NULL  AFTER email' at line 2
[2011-11-19 11:08:37][info   ] Query was:
<pre>
ALTER TABLE users
        ADD COLUMN  last_action  DATEETIME  NULL  AFTER email</pre>
[2011-11-19 11:08:37][err    ] An error has occurred: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DATEETIME  NULL  AFTER email' at line 2
[2011-11-19 11:08:37][info   ] Restoring restore point
[2011-11-19 11:08:42][success] Restorepoint restored

Looks like we made a typo in our second query. Luckily, the installer made a restore point, and restored the database. You can check this by doing a DESCRIBE on the users. You'll see that the email column that was added successfully in the update isn't there.

Let's fix our mistake, and run it again. Replace "DATEETIME" by "DATETIME", and re-run the installer:

Messages from the installer
[2011-11-19 11:13:03][info   ] Doing pre-install system check
[2011-11-19 11:13:03][info   ] Reading the directories
[2011-11-19 11:13:03][info   ] Retrieving the list of files
[2011-11-19 11:13:03][info   ] Checking for content
[2011-11-19 11:13:03][success] File list updated and sorted
[2011-11-19 11:13:03][info   ] Connecting to the database
[2011-11-19 11:13:08][info   ] Locking the database
[2011-11-19 11:13:08][notice ] MySQL cannot lock databases; proceed with care.
[2011-11-19 11:13:08][info   ] Creating a restore point
[2011-11-19 11:13:13][success] Restore point created, and stored in "restore\tut_jadva_dbins_mysql_20111119111308.mysql".
[2011-11-19 11:13:13][info   ] Checking version table
[2011-11-19 11:13:13][info   ] Retrieving current versions
[2011-11-19 11:13:13][success] Retrieved information about the current versions.
[2011-11-19 11:13:13][info   ] Updating the database
[2011-11-19 11:13:13][info   ] Updating script "users" to version 2
[2011-11-19 11:13:13][success] Update successful
[2011-11-19 11:13:13][success] Updated 1 scripts
[2011-11-19 11:13:13][info   ] Stored information about the new versions
[2011-11-19 11:13:13][success] Done.

As you can see, the first version of the users and user_logs scripts aren't executed again, since they were already installed. The installer keeps a list of the installed scripts in the jadva_installer_database_table_versions table. And this time, no error occurred.

Last thoughts

We used the installer in a separate script, but you can just as easily integrate it into your application.

You can't change scripts that are already executed. You'll need to keep in mind which scripts you've already released into production, and which are still open for changes. You could move the scripts that are already released into a separate folder, for example.

Notes