Adodb-xmlschema, or AXMLS, is a set of classes that extends the ADOdb data dictionary by processing XML formatted files and transforming them into ADOdb portable schema management commands. In simple terms:

• An XML file contains all of the definitions required to create a table, the associated columns, indexes and constraints.
• The table can be created by processing the XML file with the parseSchema method, which converts the XML into an array of SQL statements. These statements can then be executed using the executeSchema method.
• That XML file can be used unmodified against any database supported by ADOdb.
• Any modifications to the table can be enabled by simply modifying the XML file, and re-executing the XML load command. The ADOdb Meta Functions take care of any processing required to apply those changes to the table.
• In limited circumstances, the XML file may also contain data rows to load into the table.

Richard Tango-Lowy
Dan Cech 

This feature is included in the standard release.

There are two steps involved in using AXMLS in your application: first, you must create a schema, or XML representation of your database, and second, you must create the PHP code that will parse and execute the schema.

Let's begin with a schema that describes a typical, if simplistic user management table for an application.

<?xml version="1.0"?>
<schema version="0.3">
  <table name="users">
    <descr>A typical users table for our application.</descr>
    <field name="userId" type="I">
      <descr>A unique ID assigned to each user.</descr>
      <KEY/>
      <AUTOINCREMENT/>
    </field> 
    <field name="userName" type="C" size="16"><NOTNULL/></field>
    <index name="userName">
      <descr>Put a unique index on the user name</descr>
      <col>userName</col>
      <UNIQUE/>
    </index>
  </table>
  <sql>
    <descr>Insert some data into the users table.</descr>
    <query>insert into users (userName) values ( 'admin' )</query>
    <query>insert into users (userName) values ( 'Joe' )</query>
  </sql>
</schema>			

Let's take a detailed look at this schema. The opening <?xml version=“1.0”?> tag is required by XML. The <schema> tag tells the parser that the enclosed markup defines an XML schema. The version=“0.3” attribute sets the version of the AXMLS DTD used by the XML schema. The current schema version is “0.3”.

<?xml version="1.0"?>
<schema version="0.3">
  ...
</schema>

Next we define one or more tables. A table consists of a fields (and other objects) enclosed by <table> tags. The name=“” attribute specifies the name of the table that will be created in the database.

<table name="users">
    <descr>A typical users table for our application.</descr>
    <field name="userId" type="I">
      <descr>A unique ID assigned to each user.</descr>
      <KEY/>
      <AUTOINCREMENT/>
    </field>
    <field name="userName" type="C" size="16"><NOTNULL/></field>
</table>

This table is called “users” and has a description and two fields:

• The description is optional, and is currently only for your own information; it is not (yet) applied to the database.
• The first <field> tag will create a field named “userId” of type “I”, or integer. (See the ADOdb Data Dictionary documentation for a list of valid types.) This <field> tag encloses two special field options: <KEY/>, which specifies this field as a primary key, and <AUTOINCREMENT/>, which specifies that the database engine should automatically fill this field with the next available value when a new row is inserted.
• The second <field> tag will create a field named “userName” of type “C”, or character, and of length 16 characters. The <NOTNULL/> option specifies that this field does not allow NULLs.

There are two ways to add indexes to a table. The simplest is to mark a field with the <KEY/> option as described above; a primary key is a unique index. The second and more powerful method uses the <index> tags.

<table name="users">
  ...
  <index name="userName">
    <descr>Put a unique index on the user name</descr>
    <col>userName</col>
    <UNIQUE/>
  </index> 
</table>

The <index> tag specifies that an index should be created on the enclosing table. The name=“” attribute provides the name of the index that will be created in the database. The description, as above, is for your information only. The <col> tags list each column that will be included in the index. Finally, the <UNIQUE/> tag specifies that this will be created as a unique index.

Finally, AXMLS allows you to include arbitrary SQL that will be applied to the database when the schema is executed.

<sql>
  <descr>Insert some data into the users table.</descr>
  <query>insert into users (userName) values ('admin')</query>
  <query>insert into users (userName) values ('Joe')</query>
</sql>

The <sql> tag encloses any number of SQL queries that you define for your own use.

Now that we've defined an XML schema, you need to know how to apply it to your database. Here's a simple PHP script that shows how to load the schema.

/* You must tell the script where to find the ADOdb and
 * the AXMLS libraries.
 */
require "path_to_adodb/adodb.inc.php";
require "path_to_adodb/adodb-xmlschema03.inc.php";
 
/* Configuration information. Define the schema filename,
 * RDBMS platform (see the ADODB documentation for valid
 * platform names), and database connection information here.
 */
$schemaFile = 'example.xml';
$platform = 'mysql';
$dbHost = 'localhost';
$dbName = 'database';
$dbUser = 'username';
$dbPassword = 'password';
 
/* Start by creating a normal ADODB connection.
 */
$db = ADONewConnection($platform);
$db->connect($dbHost, $dbUser, $dbPassword, $dbName);
 
/* Use the database connection to create a new adoSchema object.
 */
$schema = new adoSchema($db);
 
/* Call parseSchema() to build SQL from the XML schema file.
 * Then call executeSchema() to apply the resulting SQL to 
 * the database.
 */
$sql = $schema->parseSchema($schemaFile);
$result = $schema->executeSchema();

Let's look at each part of the example in turn. After you manually create the database, there are three steps required to load (or upgrade) your schema. First, create a normal ADOdb connection. The variables and values here should be those required to connect to your database.

$db = ADONewConnection($platform);
$db->connect($dbHost, $dbUser, $dbPassword, $dbName);

Second, create the adoSchema object that load and manipulate your schema. You must pass an ADOdb database connection object in order to create the adoSchema object.

$schema = new adoSchema($db);

Third, call parseSchema() to parse the schema and then executeSchema() to apply it to the database. You must pass parseSchema() the path and filename of your schema file.

$schema->parseSchema($schemaFile); 
$schema->executeSchema(); 

Execute the above code and then log into your database. If you've done all this right, you should see your tables, indexes, and SQL.

You can find the source files for this tutorial in the examples directory as tutorial_schema.xml and tutorial.php. See the class documentation for a more detailed description of the adoSchema methods, including methods and schema elements that are not described in this tutorial.

AXMLS consists of two parts: the schema description, or DTD, and the PHP class that manipulates and parses the schema. This document introduces the reader to many of the tools AXMLS provides to manage and manipulate a schema.

AXMLS provides two different methods for applying the SQL resulting from a parsed schema to the database: inline execution and post execution. Use the continueOnError() method to specify whether or not database execution should attempt to continue if an error occurs.

Inline Execution applies each schema entity to the database immediately after that entity is parsed. I.e., the first table is read from the schema and applied to the database, then the second table, etc.

$schema = new adoSchema($this->dbconn);
 
// Inline Execution
$schema->continueOnError(true);
$schema->executeInline(true);
$schema->parseSchemaFile('schema.xml');

Post Execution parses the entire database and then applies all of the resulting SQL statements to the database in order.

$schema = new adoSchema($this->dbconn);
 
// Post Execution (default)
$schema->executeInline(false);
$schema->parseSchemaFile('schema.xml');
$schema->executeSchema();

If successful, either method will build or upgrade the database.

Upgrading a database is as simple as creating one. To upgrade an existing database to a new version of a schema, simply parse and execute the schema as above. AXMLS will automatically upgrade all tables and indices to match those provided in the schema.

In a world of pluggable and reusable code, multiple applications often coexist in a single database. Object prefixes allow you to create a namespace for your application. For example, an application called Tackle might want to prefix all its tables and objects with tackle_ so they'll be less likely to conflict with other applications that share the database. AXMLS's setPrefix() method provides this capability.

$schema = new adoSchema($this->dbconn);
 
// Set the prefix for database objects (before parsing)
$schema->setPrefix('tackle');

AXMLS provides several tools for accessing the SQL created by the schema parser. Calling printSql returns the parsed SQL in HTML, text, or PHP array format. Calling saveSql saves the parsed SQL to the specified file.

$schema->parseSchemaFile('schema.xml');
$schema->executeSchema();
 
print $schema->printSQL('TEXT'); // Display SQL as text
print $schema->printSQL('HTML'); // Display SQL as HTML
$sql = $schema->printSQL();	// Fetch SQL as array
 
// Save SQL to file
$schema->saveSQL('schema.sql');

As always, see the Class Documentation for detailed information about the methods.