Concrete5

Attention PHP developers: are you using Doctrine DBAL to access your databases? DBAL is a nice, database-agnostic wrapper around PDO that provides a lot of niceties, including support for migrations, the backbone for the popular Doctine ORM project, a robust query builder, and more.

The Problem

One thing that Doctrine DBAL doesn't have, however, is a simple XML-based declarative language for table schemas. Consider this:

create table Foo ( 
id int unsigned not null default 0; 
type varchar(255) null);

In your PHP code using Doctrine DBAL, you might execute this table creation statement like this:

$db->Execute('create table Foo ( 
id int unsigned not null default 0; 
type varchar(255) null)');

This isn't portable, and isn't that easily modifiable. Yes, you could use Doctrine's schema objects, but if your app has hundreds of tables to create, you might be dealing with some beefy PHP classes. Plus, Schema objects in PHP can't easily be generated from an existing database.

The Solution

Store your schema definitions using Doctrine XML. Doctrine XML is an XML-based format that we're using in Concrete5 to store our table data. A simple PHP parser can take a Doctrine XML file and transform it into Doctrine Schema objects, and into direct database CREATE TABLE and ALTER TABLE statements. Benefits of Doctrine XML include.

1. Portable. Just include XML files in your project.
2. State-independent. Doctrine XML doesn't care what the current state of your table is. Just add or rename columns within the XML, and reparse it using the PHP classes, and your table will be updated automatically.
3. Uses Doctrine's existing nomenclature. Types are the same, etc... If you've registered custom Doctrine data types, Doctrine XML will support them as well.

Example

Here's how our Concrete5's Users table looks in Doctrine XML:

<schema
  xmlns="http://www.concrete5.org/doctrine-xml/0.5"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.concrete5.org/doctrine-xml/0.5 http://concrete5.github.io/doctrine-xml/doctrine-xml-0.5.xsd">

    <table name="Users">
<field name="uID" type="integer" size="10">
  <unsigned/>
  <autoincrement/>
  <key/>
</field>
<field name="uName" type="string" size="64">
  <notnull/>
</field>
<field name="uEmail" type="string" size="254">
  <notnull/>
</field>
<field name="uPassword" type="string" size="255">
  <notnull/>
</field>
<field name="uIsActive" type="string" size="1">
  <default value="0"/>
  <notnull/>
</field>
<field name="uIsValidated" type="boolean">
  <default value="-1"/>
  <notnull/>
</field>
<field name="uIsFullRecord" type="boolean">
  <default value="1"/>
  <notnull/>
</field>
<field name="uDateAdded" type="datetime">
  <default value="0000-00-00 00:00:00"/>
  <notnull/>
</field>
<field name="uLastPasswordChange" type="datetime">
  <default value="0000-00-00 00:00:00"/>
  <notnull/>
</field>
<field name="uHasAvatar" type="boolean">
  <default value="0"/>
  <notnull/>
</field>
<field name="uLastOnline" type="integer" size="10">
  <unsigned/>
  <default value="0"/>
  <notnull/>
</field>
<field name="uLastLogin" type="integer" size="10">
  <unsigned/>
  <default value="0"/>
  <notnull/>
</field>
<field name="uLastIP" type="blob" size="32"/>
<field name="uPreviousLogin" type="integer" size="10">
  <unsigned/>
  <default value="0"/>
</field>
<field name="uNumLogins" type="integer" size="10">
  <unsigned/>
  <default value="0"/>
  <notnull/>
</field>
<field name="uLastAuthTypeID" type="integer" size="10">
  <unsigned/>
  <default value="0"/>
  <notnull/>
</field>
<field name="uTimezone" type="string" size="255"/>
<field name="uDefaultLanguage" type="string" size="32"/>
<index name="uName">
  <unique/>
  <col>uName</col>
</index>
<index name="uEmail">
  <col>uEmail</col>
</index>
</table>
</schema>

That's it!

Including in Projects

Doctrine XML is included in Concrete5 5.7.5 and above (which, as of this writing, is still in development.) If you'd like to use Doctrine XML in your own custom PHP project, you can! Just include it as a composer dependency:

composer require concrete5/doctrine-xml

Usage

Once you have a Doctrine XML file, making use of it is easy. This code snippet assumes $db already exists as your Doctrine DBAL instance.

<?php
$platform = $db->getDatabasePlatform();
$schema = \DoctrineXml\Parser::fromFile('/path/to/file.xml', $platform);
?>

$schema is an instance of the \Doctrine\DBAL\Schema\Schema object. From there, it's trivial to operate on it, using standard Doctrine DBAL methods:

$db->beginTransaction();
$existingSchema = $db->getSchemaManager()->createSchema();
$comparator = new \Doctrine\DBAL\Schema\Comparator();
$schemaDiff = $comparator->compare($existingSchema, $schema);
$queries = $schemaDiff->toSaveSql($db->getDatabasePlatform());
foreach ($saveQueries as $query) {
    $db->query($query);
}
$db->commit();

Hope this helps someone! Thanks to mlocati in the Concrete5 community for helping really get this up and running.