Home » Database » MDB2_Schema » Manual

Introduction

Introduction – Introduction to MDB2_Schema

Introduction to MDB2_Schema

MDB2_Schema builds upon MDB2 to provide tools to manage your database schema using XML which is both platform- and database-independent.

The XML format is inherited from the Metabase package and was improved to be able to represent new entities such as Data Manipulation Instructions and Foreign Keys.

It enables users to maintain RDBMS independent schema files in XML that can be used to create, alter and drop database entities (also called as DDL: Data Definition Language) and insert data (also called as DML: Data Manipulation Language) into a database. Reverse engineering database schemas from existing databases is also supported. It also features the hability to parse database schemas and database data in separated files. However, in this document the term "schema file" will be used to designate any XML supported by MDB2_Schema, no matter its nature.

Reading of schema files is handled by MDB2_Schema_Parser and writing to them by MDB2_Schema_Writer. There is also the MDB2_Schema_Validate class which is called after something is parsed. It is supposed to check the logical integrity of the schema file, for instance whether you are trying to declare a index field that does not exist or even when you are trying to insert a float in a date field.

Currently two parsers are available, one based on the package XML_Parser (default) and another based on XML_Unserializer. As MDB2_Schema is still beta, both parsers have its own limitation. The default one can't parse more than 2 nested expressions. The later doesn't respect DML instructions in the order they are specified in the schema file.

Although MDB2_Schema API supports input from physical databases and schema files, internally it always operates using a database schema array that will be described later in the documentation.

Installation and Usage Example

Installation and Usage Example – Installation and example for the usage of MDB2_Schema

Installation

First you need MDB2 installed:

You should install a driver for each database you are working with. For MySQL it would be:

For some hints refers to MDB2 documentation or try in a UNIX-like system:

MDB2_Schema is a separate package, and can also be installed using the PEAR installer:

now you should be ready :)

Usage Example

To create an instance of the MDB2_Schema class you can use the factory(), which accepts a $dsn or an array. The factory method also accepts an existing MDB2 object. In the example below, we will just use a $dsn.

<?php
require_once 'MDB2/Schema.php';

$options = array(
    'log_line_break' => '<br>',
    'idxname_format' => '%s',
    'debug' => true,
    'quote_identifier' => true,
    'force_defaults' => false,
    'portability' => false
);
$dsn = 'mysql://root:@localhost/MDB2Example';

$schema =& MDB2_Schema::factory($dsn, $options);

if (PEAR::isError($schema)) {
    $error = $schema->getMessage();
}

if (isset($error)) {
    var_dump($error);
}

$schema->disconnect();
?>

Get Database Definition

Get Database Definition – Generating the definition array of a physical database or MDB2 XML

Introduction

All the internal work of MDB2_Schema is done over array structures, which we will call "definition array". Many methods, such as createDatabase, requires a definition array as input parameter, instead of a filename. Others take care of the conversion automatically, accepting both inputs.

For the cases when you need a definition array, there are two avaliable methods to manually generate the definition array, one for each schema source - either a database or a schema file. Obviously you can also write it by hand, it is not intended to be done this way though.

Get Database Definition

You can use getDefinitionFromDatabase() to get the definition array from an existing database.

<?php
require_once 'MDB2/Schema.php';

$options = array(
    'log_line_break' => '<br>',
    'idxname_format' => '%s',
    'debug' => true,
    'quote_identifier' => true,
    'force_defaults' => false,
    'portability' => false
);
$dsn = 'mysql://root:@localhost/MDB2Example';

$schema =& MDB2_Schema::factory($dsn, $options);

if (PEAR::isError($schema)) {
    $error = $schema->getMessage();
} else {
    // this method _attempts_ to get the defintition from the database
    // make sure you have tested it with your database to see if it
    // returns what you expect
    $definition = $schema->getDefinitionFromDatabase();

    if (PEAR::isError($definition)) {
      $error = $definition->getMessage();
    }
}

if (isset($error)) {
  var_dump($error);
}

$schema->disconnect();
?>

Though you have to use the method with caution, if you use the method on a database created by hand. Some of the fields might be slightly different, but once you create your database using MDB2_Schema it is reliable and will return the same $definition every time.

Parse Database Definition

You can use parseDatabaseDefinitionFile() to get the definition array from a schema file.

<?php
require_once 'MDB2/Schema.php';

$options = array(
    'log_line_break' => '<br>',
    'idxname_format' => '%s',
    'debug' => true,
    'quote_identifier' => true,
    'force_defaults' => false,
    'portability' => false
);
$dsn = 'mysql://root:@localhost/MDB2Example';

$schema =& MDB2_Schema::factory($dsn, $options);

if (PEAR::isError($schema)) {
    $error = $schema->getMessage();
} else {
    $definition = $schema->parseDatabaseDefinitionFile('schema.xml');

    if (PEAR::isError($definition)) {
      $error = $definition->getMessage();
    }
}

if (isset($error)) {
  var_dump($error);
}

$schema->disconnect();
?>

Although the method accepts more parameters, only the first one is required.

Dump Database

Dump Database – Dumping a database to MDB2 XML

Dump Database

You can use dumpDatabase() to copy your database to a schema file. dumpDatabase() accepts a database definition array, for instance:

<?php
require_once 'MDB2/Schema.php';

$options = array(
    'log_line_break' => '<br>',
    'idxname_format' => '%s',
    'debug' => true,
    'quote_identifier' => true,
    'force_defaults' => false,
    'portability' => false
);
$dsn = 'mysql://root:@localhost/MDB2Example';

$schema =& MDB2_Schema::factory($dsn, $options);

if (PEAR::isError($schema)) {
    $error = $schema->getMessage();
} else {
    $dump_options = array(
      'output_mode' => 'file',
      'output' => 'schema.xml',
      'end_of_line' => "\n"
    );

    $definition = $schema->getDefinitionFromDatabase();
    if (PEAR::isError($definition)) {
      $error = $definition->getMessage();
    } else {
      $op = $schema->dumpDatabase($definition, $dump_options, MDB2_SCHEMA_DUMP_ALL);

      if (PEAR::isError($op)) {
          $error = $op->getMessage();
      }
  }
}

if (isset($error)) {
    var_dump($error);
}

$schema->disconnect();
?>

The first parameter is just the database definition array. The second parameter is the options where we choose to output to a file. The third option tells dumpDatabase() what to be dumped - either the structure, the data in the tables, or both. This is defined using the constants MDB2_SCHEMA_DUMP_STRUCTURE, MDB2_SCHEMA_DUMP_CONTENT and MDB2_SCHEMA_DUMP_ALL.

Some databases don't accept a text field with a default value. Given that, notice that $options['force_defaults'] has to be set to false when you want to create a field with the type text, as it is true by default.

Create Database

Create Database – Restoring a database from MDB2 XML

Create Database

When having a schema file, it is a breeze to create a database. Simply do the following:

<?php
require_once 'MDB2/Schema.php';

$options = array(
    'log_line_break' => '<br>',
    'idxname_format' => '%s',
    'debug' => true,
    'quote_identifier' => true,
    'force_defaults' => false,
    'portability' => false
);
$dsn = 'mysql://root:@localhost/MDB2Example';

$schema =& MDB2_Schema::factory($dsn, $options);

if (PEAR::isError($schema)) {
    $error = $schema->getMessage();
} else {
    // first run with queries disabled to make sure everything is allright
    $disable_query = true;

    $definition = $schema->parseDatabaseDefinitionFile('example.xml');
    if (PEAR::isError($definition)) {
      $error = $definition->getMessage();
    } else {
      $op = $schema->createDatabase($definition, array(), $disable_query);

      if (PEAR::isError($op)) {
        $error = $op->getMessage();
      }
    }
}

if (isset($error)) {
    var_dump($error);
}

$schema->disconnect();
?>

Update Database

Update Database – Updating a database against a new schema

Update Database

Having MDB2_Schema to update your database, when its schema changes is also really easy. You can use the getDefinitionFromDatabase() method to determine the current database schema, and then just use updateDatabase() to do the actual update. However, you have to make sure, that getDefinitionFromDatabase() returns what you expect before you rely on it. See the respective chapter for more info.

<?php
require_once 'MDB2/Schema.php';

$options = array(
    'log_line_break' => '<br>',
    'idxname_format' => '%s',
    'debug' => true,
    'quote_identifier' => true,
    'force_defaults' => false,
    'portability' => false
);
$dsn = 'mysql://root:@localhost/MDB2Example';

$schema =& MDB2_Schema::factory($dsn, $options);

if (PEAR::isError($schema)) {
    $error = $schema->getMessage();
} else {
    // first run with queries disabled to make sure everything is allright
    $disable_query = true;

    $previous_schema = $schema->getDefinitionFromDatabase();
    $op = $schema->updateDatabase('schema.xml', $previous_schema, array(), $disable_query);

    if (PEAR::isError($op)) {
        $error = $op->getMessage();
    }
}

if (isset($error)) {
    var_dump($error);
}

$schema->disconnect();
?>

The method accepts both, a filename or a definition array, as the first two parameters. Note how we mixed them in the example above. You may want to backup the current schema using dumpDatabase() for the case something goes wrong.

When updating database schemas we can run into data persistence issues. This can be addressed with data manipulation ability, that will be documented later in this manual.

XML Schema

XML Schema – Description of the XML dialect used to define database schemas and data on MDB2

Availability

The MDB2 XML Schema documentaion is available online at the package repository and refers to the latest development version.