The XML Database Schema Installer

The XML database schema installer / updater / removal class

One of the most important operations in managing a component is being able to install, update and remove its database schema. Normally you do that using the relevant sections in your extension's manifest file. The big problem with this approach is that you are using the short-sighted code in Joomla!'s own extensions installer. The Achilles' heel in Joomla!'s code is that database installation and updates depend solely on the contents of the #__schemas table. If no version is recorded there, Joomla! is using your installation SQL file. If it contains a version it will try to run all SQL scripts with a name which indicates a version later than the one recorded in the schemas table. This leaves a lot of room for spectacular failures in the following cases, which happen far more frequently than you'd imagine:

• The #__schemas entry is empty but (some of) the database tables already exist, for example is the user is transferring tables manually, upgrading from an earlier Joomla! version using a third party tool, a previous installation has gone wrong etc

• The #__schemas entry is out of date, e.g. the installer timed out before updating it

• A previous installation resulted in a SQL error

In all of the above cases Joomla! will throw a SQL error and your clients will think that you are a moron who can't even install their own component's database properly. Well, it's time to claim your power over Joomla! thanks to FOF!

FOF comes with the FOF30\Database\Installer class. Unlike Joomla!, it doesn't base its actions on unreliable version numbers. Instead, it will run SQL commands based on whether tables / fields exist, the reported type of fields or the result of custom SQL commands. It goes even further than that by letting you, the developer, to choose if the failure of executing a particular SQL command should raise a blocking SQL error or be silently ignored. This is database schema management on steroids.

The schema files are provided in XML format. You only need one XML file per database technology. By default these files are stored in the back-end of your component, under the sql/xml directory. This is something you can override through the $directory parameter you pass to the class constructor. The constructor parameters are:

db The database object used to execute the SQL commands. If unsure use JFactory::getDbo();.

directory The absolute filesystem path where the XML schema files are located.

The class provides two methods you can use anywhere in your component:

public function updateSchema()

It creates or updates the tables of your component in the database. You are suggested to call this in two places: in your installation script and in the main back-end page of your component. The latter place is not very obvious, but it allows your component to update its tables even without going through the installer. You will thank yourself for doing that when an installation fails due to a timeout error (resulting in an inconsistency between schema and code) and when you pull a Git repository on a test server. No need to run SQL commands manually, just visit your component's back-end page and presto! The database schema is updated. You're welcome.

public function removeSchema()

It removes (drops) the tables of your component. You are recommended to only use it in the uninstallation script of your component. Beware! It really does what it says on the tin. It will remove all database tables you mention in your XML schema file without any further warning. One line and the data dies, permanently.

Format of the XML schema files

<?xml version="1.0" encoding="UTF-8"?>
<schema>
    <!-- Metadata -->
    <meta>
        <!-- Supported driver types -->
        <drivers>
            <driver>mysql</driver>
            <driver>mysqli</driver>
        </drivers>
    </meta>

    <!-- SQL commands to run on installation and update -->
    <sql>
        <!-- Create the #__foobar_profiles table if it's missing -->
        <action table="#__foobar_profiles" canfail="0">
            <condition type="missing" value="" />
            <query><![CDATA[
CREATE TABLE `#__foobar_profiles` (
`foobar_profile_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`description` varchar(255) NOT NULL,
`params` longtext,
PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8;
            ]]></query>
        </action>
        <action table="#__foobar_profiles" canfail="0">
            <condition type="missing" value="params" />
            <query><![CDATA[
ALTER TABLE `#__foobar_profiles` ADD `params` longtext AFTER `description`;
            ]]></query>
        </action>
    </sql>
</schema>

As you can see the file has the root element <schema> and consists of two sections.

The <meta> section

This section has a lone element, <drivers>, which tells FOF which database technologies this XML schema file applies to. Put one driver name in each child element. The driver names recognised are those that Joomla! supports. By the latest count they are: mysql, mysqli, postgresql, sqlsrv, sqlazure.

Warning Joomla! seems to only be tested properly against the mysql and mysqli drivers. We have been observing inconsistent behaviour with the other drivers. You are advised against supporting these database technologies unless you enjoy receiving bug reports which you can't fix because the problem is in Joomla!, not your code.

The <sql> section

This is where all the action takes place. And that's why you have one or more <action> tags. Each <action> tag defines SQL commands which should run when one or more conditions are met. Each tag has the following attributes:

table REQUIRED. The table this action operates on, e.g. #__foobar_categories

canfail (optional) If it's set to 0 (default) if any SQL command in this action tag fails the error will bubble up and throw a system error, preventing further execution. If it's set to 1 all SQL errors are ignored. This setting can be overridden in each <query> tag.

Let's start with the conditions. They are defined by one or more <condition> tags. All <condition> tags accept the operator attribute which can have one of the following values:

and (default) This condition must be true

not This condition must be false

or Either this condition or its previous conditions must be true

nor Either this condition must be false or its previous conditions must be true

xor Either this condition or its previous conditions must be true, but not both

maybe Equivalent to "or"

Any other value is treated as "and". As you may have noticed this is straightforward Boolean algebra.

The kind of check performed by a <condition> tag is determined by its type attribute:

missing Checks if a table or a field of the table is missing. This depends on the value of the value attribute of the tag:

If it's empty FOF will check if the table defined by the table attribute of the <action> tag exists.

If it's not empty FOF will check if the field whose name you've put in the value attribute exists in the table defined by the table attribute of the <action> tag.

This is useful to install tables if they are missing or add missing fields to tables.

type FOF checks if the type of a database field matches what you expect. The field will be searched in the table attribute of the <action> tag. It requires two attributes:

• value. The name of the field.

• coltype. The expected field type (case insensitive). Please note that this expects the full field type definition, e.g. VARCHAR(255) NULL and NOT just VARCHAR. This is a limitation of Joomla!'s database drivers.

equals FOF checks the result of a SQL query against the value attribute. In this case the content of the <condition> tag gives the SQL query to execute. For example:

        <action table="#__admintools_profiles" canfail="1">
            <condition type="equals" operator="not" value="1"><![CDATA[
SELECT COUNT(*) FROM `#__admintools_profiles` WHERE `id` = 1;
            ]]></condition>

            <query canfail="1"><![CDATA[
INSERT IGNORE INTO `#__admintools_profiles`
(`id`,`description`, `configuration`, `filters`) VALUES
(1,'Default PHP Change Scanner Profile','','');
            ]]></query>
        </action>

In this case we are checking if the COUNT query returns "1". If not (due to the "not" operator) we will run the INSERT IGNORE query.

true Always returns true. Useful to run index creation as indices cannot be reliably detected across all database types (again, a limitation of Joomla!'s database drivers…).

Next up you have one or more <query> tags. When the conditions are met they will all execute. The only attribute you can have is canfail and it's optional. If you skip it FOF will use the canfail attribute of the <action> tag.

As you see this feature is extremely simple and extremely powerful. If you use it wisely it will make your component's database installation and maintenance seem to take place magically!