Using PHP's MDB2_Schema, an XML-Based Database Schema Manager : Page 3

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).

Author's Note: The multi-dimensional array definition for the database and table shown in Listing 5 was extracted from the MDB2_Driver_mysql-1.4.1\MDB2\Driver\Manager\mysql.php file.

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).

Author's Note: The alterDatabase() method works exactly like the SQL ALTER DATABASE statement.

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:

   <?php
    
   // Include class
   include_once 'MDB2/Schema.php';
   
   //initialize an MDB connection to the database 
   $schema = MDB2_Schema::factory(
      MDB2::factory('mysql://root@localhost/bookstore'));
   

   // Update database
   $operation = $schema->updateDatabase(
      allnew.xml',all.xml');
   
   if ($operation instanceof MDB2_Error){
      die ($operation->getMessage());
   } else {
      echo 'Database successfully updated. <br />';
   }
   ?>

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.

Previous Page