Login | Register   
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


advertisement
 

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

MDB2_Schema lets you create and alter database schemas in an RDBMS-independent manner.


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





Octavia Andreea Anghel is a senior PHP developer currently working as a primary trainer for programming teams that participate at national and international software-development contests. She consults on developing educational projects at a national level. She is a coauthor of the book "XML Technologies—XML in Java" (Albastra, ISBN 978-973-650-210-1), for which she wrote the XML portions. In addition to PHP and XML, she's interested in software architecture, web services, UML, and high-performance unit tests.
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap