Metabase

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 Goals

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.

Application Components

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

Abstract Thinking

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 code ” specifically , 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.

Usage Examples

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
 <?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" => graphics/ccc.gif "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.

OOPs!

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:

 MetabaseSetDatabase($db, "db127"); 

Metabase also allows you to do this:

 $result=$db->SetDatabase("db127"); 

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 )
 <?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);  ?> 

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" [1] that are independent of the database system, and which may be used transparently by developers within their applications. These base data types include date, time, numeric (integer, float, and decimal), string, and Boolean types.

When it comes to actually inserting the data into the database, the Metabase API internally does the hard work of translating the base data type into a native data type supported by the underlying database system. For applications that must support multiple database backends , this feature is extremely useful because it allows developers to work with a uniform set of data types without worrying about compatibility issues between different database systems.

[1] Lemos, Manuel. Class: Metabase ”Database Independent Access and Management . PHP Classes Repository. Available from the Internet: 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.

Implementation Overview

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:

  • metabase_database_class : A class that provides base functionality to driver classes, and that may be reused or overridden in the actual driver classes

  • metabase_ dbtype _class : A database-specific class that implements Metabase public interface functions for a particular database type

  • metabase_manager_class : 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

  • metabase_parser_class : A class that validates the XML-encoded database schema

  • xml_parser_class : 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"), graphics/ccc.gif $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]), graphics/ccc.gif ""))        {             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.

graphics/09fig01.gif

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.

graphics/09fig02.gif

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 Parser
 xml_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 graphics/ccc.gif definition"));        if(!IsSet($database_tags["name"]))              return($this->SetParserError("0","it was not defined the database name graphics/ccc.gif property"));        if(!strcmp($database_values["name"],""))              return($this->SetParserError($database_tags["name"],"It was not defined a graphics/ccc.gif 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 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 Remarks

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.

I l @ ve RuBoard


XML and PHP
XML and PHP
ISBN: 0735712271
EAN: 2147483647
Year: 2002
Pages: 84

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net