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


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

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




Full Text Search: The Key to Better Natural Language Queries for NoSQL in Node.js

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

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.

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