Login | Register   
RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX

By submitting your information, you agree that devx.com may send you DevX offers via email, phone and text message, as well as email offers about other products and services that DevX believes may be of interest to you. DevX will process your information in accordance with the Quinstreet Privacy Policy.


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

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




Application Security Testing: An Integral Part of DevOps

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:

Table 1. Options Array: These are the options to the dumpDatabase method.
Option Type Description
'output_mode' String 'file': Dump the output to a specified file. If omitted, dumpDatabase dumps the data using a function.
'output' String The file name output by the function (depends on the output_mode parameter value.
'end_of_line' String The end-of-line delimiter to use for the output. The default is "\n."

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:

</b>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.

<?php // Include class include_once 'MDB2/Schema.php'; // Initialize an MDB connection to the database $schema = MDB2_Schema::factory(MDB2::factory( 'mysql://root@localhost/bookstore2')); // Parse a database definition file $data = $schema->parseDatabaseDefinitionFile( 'structure.xml'); //Create/import database $ret = $schema->createDatabase($data); if ($ret instanceof MDB2_Error){ die ($ret->getMessage()); } else { echo 'Database schema successfully imported. <br />'; } ?>

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.

Comment and Contribute






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



Thanks for your registration, follow us on our social networks to keep up-to-date