Metabase is a database abstraction layer for SQL-compliant databases. Developed by Manuel Lemos as an open-source software project, Metabase is available under the BSD license, and can be downloaded from http://www.phpclasses.org/browse.html/package/20.html.
Metabase was designed to accomplish two main goals:
Provide a database-independent API for PHP applications
Provide a database-independent mechanism for maintaining and updating database schema
Metabase accomplishes the first goal using just PHP, and the second using a combination of PHP and XML.
Metabase consists of the following components, implemented as PHP classes:
A database-independent public interface, or API, that developers can use in their application
Database-specific "drivers" that implement public interface methods for each database type
A manager to handle database schema comparison, installation, and upgrade
A Metabase-specific parser, which verifies the syntax and structure of an XML-encoded database schema
A generic XML parser, which is used to convert any XML document into a native PHP structure
If you've worked with databases before, you already know that PHP comes with a different set of database-manipulation functions for each database type. For example, opening a connection to a MySQL database is accomplished via the mysql_connect() function, whereas opening an equivalent connection to an Oracle database is accomplished via the OCILogon() function.
With this in mind, it follows that any change to the database server used in a PHP application immediately implies a change to the application codespecifically, to the native PHP functions used to manipulate the database. This is both tedious (a developer needs to manually inspect the code and alter it to use the functions specific to the new database) and time-consuming (the entire application needs to be retested in order to verify that things still work as advertised).
That's where a database abstraction layer comes in. A database abstraction layer provides an interface that can be used with any database type, thereby providing the developer with a uniform API to develop applications that are portable across databases. This interface (which may be represented as public functions or object methods) is internally mapped to the corresponding native PHP functions for each database type, with the abstraction layer possessing the intelligence necessary to decide which native function to call when the corresponding public function or object method is invoked. For example, an abstraction layer might expose a generic connect() function, which internally invokes either mysql_connect(), OCILogon(), or odbc_connect(), depending on the database type it has been configured for.
Thus, by providing a public interface that is independent of the database being used, a database abstraction layer provides a simple solution to the problem of developing portable database-independent PHP applications.
So that's the good news. Now, here's the bad news: In the real world, database abstraction layers are usually slower than native functions, take advantage of fewer database features, and are slower to catch up with the native API.
Before getting into an analysis of the code, it's instructive to look at a couple of usage examples in order to see how Metabase fulfills the design goals stated previously. Consider Listing 9.1, which shows the abstraction layer in action:
Listing 9.1 Retrieving Data from a MySQL Database with the Metabase API
// require Metabase public interface files
// configure Metabase with database type and connection parameters
$error = MetabaseSetupDatabase(array("Type" => "mysql", "User" => "john", "Password" =>
// select a database
// check for errors
if($error != ")
die("Database setup error: $error\n");
// generate and execute query
$query = "SELECT * FROM addressbook";
$result = MetabaseQuery($db, $query);
// if an error occurred while executing the query
// report the error and exit
if(!$result != 0)
echo "The following error occurred: " . MetabaseError($db);
// get number of rows in result set
$rows = MetabaseNumberOfRows($db, $result);
// if no rows available
echo "No data available.";
// print data in each row
for($row=0; $row<$rows; $row++)
echo "<tr><td>", MetabaseFetchResult($db, $result, $row, "name"),"</td>";
echo "<td>",MetabaseFetchResult($db, $result, $row, "email"),"</td></tr>";
In this case, although I'm using a MySQL database, I'm not accessing the data within it using PHP's native MySQL functions. Rather, I'm using the Metabase API, which provides equivalent functionality with the additional benefit of portability across databases. If, for example, I decided to move to a PostgreSQL database, I would only need to alter one line in the preceding script abovethe call to MetabaseSetupDatabase() and everything else would continue to work as before.
Fans of object-oriented programming will be glad to hear that Metabase supports an alternative object-based interface to database manipulation. So, although you can certainly do this:
Metabase also allows you to do this:
In this case, $db is an object created via a call to the MetabaseSetupDatabaseObject() function.
Take a look at the Metabase manual for more information on this feature.
In addition to the database-independent API demonstrated in Listing 9.1, Metabase also comes with a fairly powerful module for working with database schema. This module uses a combination of PHP and XML to simplify the task of schema creation and maintenance, and is the primary focus of the case study. In order to illustrate how it works, consider the following simple table definition:
Table : addressbook
| Field | Type | Null | Key | Default | Extra |
| id | int(11) | | |1 | |
| name | char(255) | YES | |NULL | |
| address | char(150) | YES | |NULL | |
| tel | int(11) | YES | |NULL | |
| fax | int(11) | YES | |NULL | |
Now, consider Listing 9.2, which creates a Metabase-compliant representation of this table definition in well-formed XML.
Listing 9.2 A Database Table Definition in XML (addressbook.xml)
<!-- database name -->
<!-- table definition -->
<!-- field declarations -->
By using simple XML structures to express database schema semantics, Metabase allows developerseven those who are not familiar with the intricacies of database creationto easily create and maintain database schema definitions, and to use these definitions in their development efforts.
Metabase can accept an XML document in this format, parse it, and create a corresponding database table. Listing 9.3 contains a PHP script that uses the Metabase public interface to do just this.
Listing 9.3 Creating a Table Using a Metabase-Compliant XML Table Definition
// include all required files
// where is the schema
$schema = "addressbook.xml";
// set up variable interpolation array
$variables = array();
// set up Metabase configuration
$arguments = array("Type" => "mysql", "User" => "john", "Password" => "doe");
// instantiate the Metabase manager
$manager = new metabase_manager_class;
// set up database
$result = $manager->UpdateDatabase($schema, $schema . ".old", $arguments, $variables);
Not My Type
It's sad but true: Different database management systems are not always consistent in their support for basic data types. And this lack of consistency can cause serious difficulties when porting data over from one database system to another.
As a database abstraction layer, Metabase has an interesting solution to this problem. It defines a set of "base data types"http://www.phpclasses.org/browse.html/package/20.html or http://phpclasses.org/goto/browse.html/file/60.html
In the event of any change to the database schema, Metabase also allows you to update the XML document with the new schema; it then takes care of altering the database to conform to the new schema, and of porting existing records to this altered database. As demonstrated in Listing 9.1 and Listing 9.3, all these tasks are accomplished via the database-independent Metabase API, which currently supports a number of different databases (including Oracle, MySQL, PostgreSQL, mSQL, and ODBC), and is therefore a viable real-world alternative for developers looking to create portable database-driven web applications.
Now that you've seen how Metabase works, let's briefly focus on what happens behind the scenes (you might find it helpful to refer to the source code of the application while reading this section).
Metabase is implemented as a collection of different PHP classes, each one performing a clearly defined set of functions. These classes are the following:
A class that provides base functionality to driver classes, and that may be reused or overridden in the actual driver classes
A database-specific class that implements Metabase public interface functions for a particular database type
A class that processes database schema documents, compares different schema documents to build a list of differences, and makes the database changes necessary to install or upgrade to the new schema
A class that validates the XML-encoded database schema
A generic XML parser for parsing any XML document
Putting the Pieces Together
It should be noted that the generic XML parser used by Metabase, the xml_parser_class class, is not included in the standard Metabase distribution. You can download it separately from http://phpclasses.upperdesign.com/browse.html/package/4.
As Listing 9.1 demonstrated, the first order of business is to configure Metabase by providing it with information on the database type, username, and password:
MetabaseSetupDatabase(array("Type" => "mysql", "User" => "john", "Password" => "doe"),
This allows the Metabase public interface (defined in the file metabase_interface.php) to select the appropriate database driver for subsequent queries. Listing 9.4 demonstrates this with a snippet of the function code.
Listing 9.4 The Functions for Configuration of the Metabase Public Interface (from metabase_interface.php)
// some parts of these functions
// have been deleted for greater readability
switch(IsSet($arguments["Type"]) ? $arguments["Type"] : ")
// and so on...
As Listing 9.4 demonstrates, the Metabase API function MetabaseSetupDatabase() internally invokes MetabaseSetupInterface(), which uses the database type to include the appropriate database driver file.
Now, when a Metabase API function is called by the application, the Metabase interface layer diverts the request to the appropriate database driver, which uses PHP's native function(s) for that database to perform the selected action. The result of the function call(s) is then sent back to the application from the database driver via the Metabase interface layer.
This hierarchical flow can be better represented by Figure 9.1.
Figure 9.1. Interaction between the Metabase public API and the database-specific driver.
So, for example, a call to the Metabase function MetabaseNumberOfRows() in a MySQL environment would first get intercepted by the Metabase interface layer and then diverted to the MySQL database driver, which would execute the function using PHP's native MySQL functions.
This can be clearly seen from the following code snippets. Listing 9.5 contains the definition for the MetabaseNumberOfRows() function as it appears in the Metabase interface layer.
Listing 9.5 The Definition for the MetabaseNumberOfRows() API Function (from metabase_interface.php)
As you can see, this function definition is merely a stub pointing to the NumberOfRows() function, which is individually defined for each database driver. Listing 9.6 demonstrates what the one for MySQL looks like.
Listing 9.6 The Definition for the MySQL-Specific NumberOfRows() Function (from metabase_mysql.php)
And Listing 9.7 demonstrates what the one for PostgreSQL looks like.
Listing 9.7 The definition for the PostgreSQL-specific NumberOfRows() function (from metabase_pgsql.php)
Thus, the two-tiered approach illustrated in Figure 9.1 makes it possible to create database-specific drivers while still exposing a uniform interface to developers.
In the event that the API function called involves the creation or alteration of a database (remember the second design goal?), the two-tier hierarchy illustrated in Figure 9.1 gets modified to include a couple of additional layers. Figure 9.2 illustrates the change.
Figure 9.2. Interaction between the Metabase manager, XML parser, and public interface.
In this case, Metabase first creates an instance of the XML parser class (defined in the file xml_parser.php) to parse the XML-compliant schema definition, and to convert this XML document into a native PHP object.
The XML parser used by Metabase converts the marked-up database schema definition into a single PHP associative array. The indices of this array are the path of the tag or data elements, and the corresponding values are the actual tag or data element values.
This can be better understood with an example. Consider Listing 9.8, which uses this XML parser to parse a simple XML document.
Listing 9.8 Using Metabase's XML Parser to Convert an XML Document into a Structured PHP Object
// include XML parser
// create a simple XML document
$xml_str = <<< END
<scream>Mommy, Mommy, the <martians>little green men</martians> are back!</scream>
// instantiate an XML parser object
// uncomment the next line to store element positions
// as part of the structure
// $xml_parser->store_positions = 1;
// parse the XML document
// uncomment the next line to see the resulting structure
When the resulting structure is dissected with print_r(), it looks like Listing 9.9.
Listing 9.9 The Object Created after Parsing an XML Document with Metabase's XML Parser
[xml_parser] => 0
[error_number] => 0
[error_line] => 0
[error_column] => 0
[error_byte_index] => 0
[error_code] => 0
[stream_buffer_size] => 4096
[structure] => Array
 => Array
[Tag] => scream
[Elements] => 3
[Attributes] => Array
[0,0] => Mommy, Mommy, the
[0,1] => Array
[Tag] => martians
[Elements] => 1
[Attributes] => Array
[0,1,0] => little green men
[0,2] => are back!
[positions] => Array
[store_positions] => 0
[case_folding] => 0
[target_encoding] => ISO-8859-1
[simplified_xml] => 0
[fail_on_non_simplified_xml] => 0
This PHP structure is then passed on to the Metabase parser class (defined in the file metabase_parser.php), which performs Metabase-specific error checks, and tests on the schemafor example, verifying that every <database> element has a corresponding <name> element under it, or ensuring that <field> elements contain appropriate values.
Listing 9.10 demonstrates this by reproducing some snippets culled directly from the class code.
Listing 9.10 Some of the Error Checks Performed by the Metabase Parser Class (from metabase_parser.php)
// some parts of this function
// have been deleted for greater readability
return($this->SetParserError("0","it was not defined a valid database
return($this->SetParserError("0","it was not defined the database name
return($this->SetParserError($database_tags["name"],"It was not defined a
valid database name"));
// and so on...
These error checks, together with others in the script, perform the very important function of verifying that the schema is valid before using it to create one or more database tables.
Tying Up Loose Ends
If you actually peek into the internals of the Metabase parser class (defined in the file metabase_parser.php), you'll notice that a good portion of the code consists of error checks. In case you're wondering whether this is really necessary, you might want to keep in mind that PHP's SAX parser is a non-validating XML parser, and consequently does not support using a DTD to verify the integrity of the marked-up schema definition. Therefore, the only way to validate the XML data is to manually parse it and aggressively check the elements and data within it for structural or type errors.
After all the error checks have been completed successfully, the next step is to compare the current definition with the previous one (if it exists), and build a list of changes between the two versions. This list of changes (again structured as an array of arrays) is then passed on to the Metabase interface layer (specifically to the MetabaseAlterTable() function), and then to the driver for that specific databasewhich takes care of performing the actual table modification, dropping, renaming, and adding columns to the table.
Obviously, this is a broad overview of how Metabase worksa line-by-line explanation of the code is beyond the scope of this chapter, especially when you consider that the application consists of well over eight thousand lines of code. That said, if you have the time (and patience), it's instructive to read through the source code of the application, if only to increase your familiarity with code modularization, error handling, and object-oriented programming techniques.
By providing developers with a uniform, database-independent mechanism for executing SQL queries, Metabase makes it possible to write web applications that can be ported across databases with minimal difficulty, time, and cost. It's also a good example of the type of applications that the XML/PHP combination makes possible, illustrating clearly how the simplicity of XML can be combined with the power of PHP's XML parser to create a robust and useful real-world solution.