he MDB2_Schema library from the PHP Extension and Application Repository (PEAR) is a powerful solution for preserving and using database schemas for different kinds of Relational Database Management Systems (RDBMSs). Because MDB2_Schema stores database schemas in XML format, it’s independent of any particular RDBMS. You can execute basic SQL statements such as CREATE, ALTER, DROP, and INSERT directly through MDB2_Schema. MDB2_Schema supports reverse engineering and the format is compatible with both Microsoft Access (.mdb) and Internet Information Server’s Metabase files.

If you’re not already familiar with MDB2_Schema, to fully understand this article, I recommend that you consult the MDB2 XML Schema documentation whenever you’re not familiar with the terminology or concepts used here. Also, a full description of the Metabase schema itself is beyond the article scope, but you can find it here.

Installing MDB2_Schema
You install the PEAR package like this (version 0.8.2 is a beta version):

   pear install --alldeps channel:/pear.php.net/XML_Tree-2.0.0RC2   pear install --alldeps channel:/pear.php.net/XML_DTD-0.4.2   pear install --alldeps channel:/pear.php.net/XML_Serializer-0.18.0   pear install --alldeps channel:/pear.php.net/MDB2_Schema-0.8.2   pear install --alldeps channel:/pear.php.net/MDB2_Driver_mysql-1.4.1   pear install --alldeps channel:/pear.php.net/MDB2_2.4.1

As you can see, the MDB2_Schema PEAR requires other PEARs to operate properly. At minimum, you will need to install the MDB2 PEAR and at least one database driver. The links to the database drivers supported by the MDB2_Schema PEAR package are listed below (the last portion of each name shows you the database targeted by each driver):

• MDB2_Driver_fbsql
• MDB2_Driver_ibase
• MDB2_Driver_mssql
• MDB2_Driver_mysql
• MDB2_Driver_mysqli
• MDB2_Driver_oci8
• MDB2_Driver_pgsql
• MDB2_Driver_querysim
• MDB2_Driver_sqlite

Connecting to the Database
To connect to a specified database you first need to set up a DSN (Data Source Name), which can be a string or an array that defines the parameters for the connection: the RDBMS type, the protocol, the host specification, the username, the password, and the database name.

The complete DSN syntax is:

   phptype(dbsyntax)://username:password@protocol+      hostspec/database?option=8&another=true

You rarely need the complete form; instead, you usually use one of these simpler common forms derived from the complete form:

• phptype://username:password@hostspec/database_name
• phptype://username:password@hostspec
• phptype://username@hostspec
• phptype://hostspec/database
• phptype://hostspec
• phptype(dbsyntax)
• phptype

Here are two examples of a DSN, the first defined as an array and the second as a string:

   $dsn = array ( 'phptype' => 'mysql','hostspec' =>    'localhost:3306','username' => 'root',    'password' => '', 'database' => 'bookstore' );      $dsn = 'mysql://root@localhost/bookstore';

After creating a DSN, you can use either of the following two methods to open a connection to an RDBMS:

1. Create a new MDB2 connection object and connect to the specified database using the DSN represented by the &$db argument. The optional $options argument represents an associative array of option names and their values:

      bool|MDB2_Error connect( string|array|MDB2_Driver_Common &$db,       [array $options = array()]) :  

2. Create a new MDB2 object for the specified database type using the DSN represented by the &$db argument. Again, the optional $options argument represents an associative array of option names and their values:

      bool|MDB2_Error &factory( string|array|MDB2_Driver_Common &$db,       [array $options = array()]) : 

   For this method, the $options array looks like this:

      $options = array(           'fail_on_invalid_names' => true,           'dtd_file' => false,           'valid_types' => array(),'force_defaults'=>true,           'parser'=>'MDB2_Schema_Parser',           'writer'=>'MDB2_Schema_Writer',           'validate'=>'MDB2_Schema_Validate')

You can set those options when you initially connect to the database using the connect and factory methods or you can set them (one option at a time), using the setOption method which has the following prototype:

   bool|MDB2_Error setOption( string $option, mixed $value)

The $option argument in the preceding code specifies the option you want to set, while the $value argument holds the value you want to apply.

Dump a Database
In this section you will see how to extract the structure, the content, and the entire database into three different files using the current database definition. To do that, you’ll use two methods: the dumpDatabase() and getDefinitionFromDatabase() methods. Here’s the dumpDatabase() prototype:

   bool|MDB2_Error dumpDatabase( array $database_definition, array $arguments,       [int $dump = MDB2_SCHEMA_DUMP_ALL])

The dumpDatabase method writes a previously parsed database structure to a file in the Metabase schema XML-based format. The $database_definition argument represents the current database definition, and the $arguments parameter represents an array that defines various options, as shown in Table 1:

The $dump argument specifies what data to dump, and takes one of the values: MDB2_SCHEMA_DUMP_ALL (dump the entire database), MDB2_SCHEMA_DUMP_STRUCTURE (dump only the structure of the database), or MDB2_SCHEMA_DUMP_CONTENT (dump only the content of the database).

The other function you’ll need in this section is getDefinitionFromDatabase(), defined below:

array|MDB2_Error getDefinitionFromDatabase( )

You use this method to reverse-engineer a schema structure from an existing MDB2 if an XML schema file exists.

Enough background. The examples use a bookstore database and a table called books created using this SQL command:

   create table books (id int not null       auto_increment primary key, title varchar(50),       author varchar(50),      yearofpublication int,       publisher varchar(50),      price int);

To get some data to work with, the following code inserts three records into the books table:

   insert into books values(      1,"Annabel Lee","Edgar Allan Poe",      1849,"The Literature Page",26);   insert into books values(      2,"The Ballad of Reading Gaol","Oscar Wilde",      1898,"The Literature Page",45);   insert into books values(3,"The Sonnets","Edgar Allan Poe",      1602,"The Literature Page",34);

Listing 1 shows a PHP application that extracts the entire database, the structure, and the content. It outputs three schema documents—all.xml, structure.xml, and content.xml:

When you execute Listing 1, you will see the following messages:

   Database structure and contents successfully dumped to all.xml.   Database structure successfully dumped to structure.xml.   Database contents successfully dumped to content.xml.

The three XML schema documents resulted from the above application are in Listing 2 (all.xml), Listing 3 (structure.xml), and Listing 4 (content.xml), respectively:

Reverse-Engineer a Database
You’ve seen how to dump a database. The reverse is also possible—creating a database using the dumped schema document structure.xml. To do that, you use the parseDatabaseDefinitionFile and the createDatabase methods.

You parse a database definition file by creating a schema format parser object and passing the file contents to the parser as an input data stream. Here’s the function prototype:

   void parseDatabaseDefinitionFile(string $input_file,       [array $variables = array()],       [bool $fail_on_invalid_names = true],       [array $structure = false])

The $input_file arguments represents the database schema file. The $variables argument represents an associative array that defines text string values that will replace the variables used in the schema description. The $fail_on_invalid_names argument causes the function to fail if the parser finds invalid names, and the $structure argument represent the database structure definition.

After parsing the schema file, you call the createDatabase method to actually create the database:

   bool|MDB2_Error createDatabase(      array $database_definition,       [array $options = array()]):

The createDatabase method creates the database as well as any objects defined in the schema such as tables, indexes, and sequences. The $database_definition argument represents a multi-dimensional array that contains the current definition of the database, and the $options argument represents an array of options to pass to the database-specific driver version of MDB2_Driver_Manager_Common::createTable().

As an example, the next application creates a bookstore2 database containing a books2 table by parsing a slightly modified version of the structure.xml schema document, where the database and table name were replaced to reflect the new names.

   parseDatabaseDefinitionFile(      'structure.xml');      //Create/import database   $ret = $schema->createDatabase($data);      if ($ret instanceof MDB2_Error){       die ($ret->getMessage());   } else {      echo 'Database schema successfully imported. 
';   }   ?>

Running the preceding code parses the input structure.xml file, and creates the bookstore2 database containing the books2 table. This new database will have the same structure as the bookstore database, but the books2 table will contain no records.

Create a Database Using an Array
If you don’t have an existing schema file, the MDB2 PEAR provides a createDatabase method to create a new database. To do that, you create a multi-dimensional array, setting parameters such as the database name, properties, table, and field names (see Listing 5 for a generic example).

After setting up the multi-dimensional array that defines the database, you create a DSN, and call the createDatabase method. The application shown in Listing 6 uses a multi-dimensional array similar in structure to that in Listing 5 to create a bookstore3 database containing books3 and books4 tables:

When you run the code in Listing 6, it creates a new database called bookstore3 which has two tables: books3 with id, title and author fields, and books4 with id, yearofpublication, and publisher fields.

Updating a Database
You can use your schema to update or delete content from a database using the update and delete table initialization methods. As you would expect, these methods replace the field values of rows or remove rows that match a condition in the target table when creating a database for the first time.

The next example performs an update to the bookstore database, using the updateDatabase method described below:

   bool|MDB2_Error updateDatabase(      string|array $current_schema,       [string|array $previous_schema = false],       [array $variables = array()],       [bool $disable_query = false],       [ $overwrite_old_schema_file = false]) 

This method compares two versions of a database schema definition: the installed database and the one you’re using to update the previous database definition. If the definition file of the previous database does not exist, then the database will be created using the definition specified in the current schema file. The $current_schema argument represents the filename (or array) of the updated database schema definition, the $previous_schema argument represent the filename or array that defines the previously installed database schema. The $variables argument represents an associative array passed to the $variables argument of the parseDatabaseDefinitionFile function. The $disable_query argument determines whether to set the disable_query option to true for the alterDatabase() or createDatabase() methods (the default value is false).

The following application updates the bookstore database by modifying the id field for all records and deleting the last record. It uses a modified version of the all.xml document generated in the section “Dump a Database.” The revised schema document is in Listing 7. Here’s the code:

   updateDatabase(      allnew.xml',all.xml');      if ($operation instanceof MDB2_Error){      die ($operation->getMessage());   } else {      echo 'Database successfully updated. 
';   }   ?>

Using the examples in this article, you have the basics for using a tool specially designed to work with database schemas. As you probably noticed it’s very easy to use, and it offers a high degree of flexibility, especially considering that this is only a beta version. It will be interesting to monitor the evolution of this PEAR and to see what the first stable release brings.