I l @ ve RuBoard |
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. Design GoalsMetabase was designed to accomplish two main goals:
Metabase accomplishes the first goal using just PHP, and the second using a combination of PHP and XML. Application ComponentsMetabase consists of the following components, implemented as PHP classes:
Usage ExamplesBefore 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<?php // require Metabase public interface files require("metabase_interface.php"); require("metabase_database.php"); // configure Metabase with database type and connection parameters $error = MetabaseSetupDatabase(array("Type" => "mysql", "User" => "john", "Password" => "doe"), $db); // select a database MetabaseSetDatabase($db, "db127"); // 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); MetabaseFreeResult($db, $result); exit; } // get number of rows in result set $rows = MetabaseNumberOfRows($db, $result); // if no rows available if(!$rows) { echo "No data available."; } // print data in each row else { echo "<table><tr><td>name</td><td>email</td></tr>"; for($row=0; $row<$rows; $row++) { echo "<tr><td>", MetabaseFetchResult($db, $result, $row, "name"),"</td>"; echo "<td>",MetabaseFetchResult($db, $result, $row, "email"),"</td></tr>"; } echo "</table>"; } ?> 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 above ”the call to MetabaseSetupDatabase() ”and everything else would continue to work as before.
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 )<?xml version="1.0"?> <database> <!-- database name --> <name>db1097</name> <!-- table definition --> <table> <name>addressbook</name> <!-- field declarations --> <declaration> <field> <name>id</name> <type>integer</type> <notnull>1</notnull> <default>1</default> </field> <field> <name>name</name> <type>text</type> <length>255</length> </field> <field> <name>address</name> <type>text</type> <length>150</length> </field> <field> <name>tel</name> <type>integer</type> </field> <field> <name>fax</name> <type>integer</type> </field> </declaration> </table> </database> By using simple XML structures to express database schema semantics, Metabase allows developers ”even those who are not familiar with the intricacies of database creation ”to 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<?php // include all required files require("xml_parser.php"); require("metabase_parser.php"); require("metabase_interface.php"); require("metabase_database.php"); require("metabase_manager.php"); // 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); ?>
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. Implementation OverviewNow 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:
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"), $db); 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 )<?php // some parts of these functions // have been deleted for greater readability Function MetabaseSetupDatabase($arguments,&$database) { global $metabase_databases; $database=count($metabase_databases)+1; if(strcmp($error=MetabaseSetupInterface($arguments,$metabase_databases [$database]), "")) { Unset($metabase_databases[$database]); $database=0; } else $metabase_databases[$database]->database=$database; return($error); } Function MetabaseSetupInterface(&$arguments,&$db) { switch(IsSet($arguments["Type"]) ? $arguments["Type"] : "") { case "msql"; $include="metabase_msql.php"; $class_name="metabase_msql_class"; $included="METABASE_MSQL_INCLUDED"; break; case "mssql"; $include="metabase_mssql.php"; $class_name="metabase_mssql_class"; $included="METABASE_MSSQL_INCLUDED"; break; case "mysql"; $include="metabase_mysql.php"; $class_name="metabase_mysql_class"; $included="METABASE_MYSQL_INCLUDED"; break; case "pgsql"; $include="metabase_pgsql.php"; $class_name="metabase_pgsql_class"; $included="METABASE_PGSQL_INCLUDED"; break; // and so on... include($include_path.$include); } $db=new $class_name; if(IsSet($arguments["Host"])) $db->host=$arguments["Host"]; if(IsSet($arguments["User"])) $db->user=$arguments["User"]; if(IsSet($arguments["Password"])) $db->password=$arguments["Password"]; return($db->Setup()); } ?> 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 )<?php Function MetabaseNumberOfRows($database,$result) { global $metabase_databases; return($metabase_databases[$database]->NumberOfRows($result)); } ?> 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 )<?php Function NumberOfRows($result) { return(mysql_num_rows($result)); } ?> 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 )<?php Function NumberOfRows($result) { return(pg_numrows($result)); } ?> 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<?php // include XML parser require("xml_parser.php"); // create a simple XML document $xml_str = <<< END <?xml version="1.0"?> <scream>Mommy, Mommy, the <martians>little green men</martians> are back!</scream> END; // instantiate an XML parser object $xml_parser=new xml_parser_class; // uncomment the next line to store element positions // as part of the structure // $xml_parser->store_positions = 1; // parse the XML document $xml_parser->Parse($xml_str, 1); // uncomment the next line to see the resulting structure // print_r($xml_parser); ?> 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 Parserxml_parser_class Object ( [xml_parser] => 0 [error] => [error_number] => 0 [error_line] => 0 [error_column] => 0 [error_byte_index] => 0 [error_code] => 0 [stream_buffer_size] => 4096 [structure] => Array ( [0] => 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 schema ”for 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 )<?php // some parts of this function // have been deleted for greater readability Function Parse($data,$end_of_data) { // snip if(strcmp($this->xml_parser->structure["0"]["Tag"],"database")) return($this->SetParserError("0","it was not defined a valid database definition")); if(!IsSet($database_tags["name"])) return($this->SetParserError("0","it was not defined the database name property")); if(!strcmp($database_values["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.
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 database ”which 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 works ”a 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. Concluding RemarksBy 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. |
I l @ ve RuBoard |