PEAR Manual
PrevNext

Reverse Module

Reverse Module -- Module for managing database structure

Description

The Reverse module is an extension of the Common Module driver, and is made up in the following structure:

Usage

In order to use the MDB2 Reverse drivers, it is necessary to first load the Reverse driver into your MDB2 instance. Lets start a MDB2 instance and set up our connection:

Example 36-1. Loading the Reverse module

<?php
// Load up the MDB2 Base class
require 'MDB2.php';

// define a DSN to connect to our database
$dsn = array(
	'phptype' => 'mysql',
	'username' => 'someuser',
	'password' => 'somepass',
	'hostspec' => 'somehost',
	'database' => 'somedb',
);

// create MDB2 instance
// MDB2 has a number of connection options - singleton(), factory() and connect()
$mdb2 = &MDB2::connect($dsn);
if(PEAR::isError($mdb2)) {
	// Die with the user info message on failure for any reason
	die($mdb2->getuserinfo());
}
// We have a valid connection, so let's do some magic!
// Load the Reverse Module using MDB2's loadModule method
$mdb2->loadModule('Reverse', null, true);
?>
In the above example, we have created a valid connection to a database that does exist, using a valid user and password for our database server. We now have access to all of the MDB2 Reverse functionality within our example application. For the purposes of this document, we will set up an example table as such:

Example 36-2. Create a table

<?php
// Define a table name that we will be working with
$fields = array(
	'id' => array(
		'type' => 'integer',
		'unsigned' => true,
		'autoincrement' => true,
	),
	'somename' => array(
		'type' => 'text',
		'length' => 12,
	),
	'somedate' => array(
		'type' => 'date',
	),
);
$table = 'sometable';
// Create the table
//Load the Manager Module
$mdb2->loadModule('Manager', null, true);
//create the table with the manager module
$mdb2->manager->createTable($table, $fields);
?>

getTableFieldDefinition() Method

The getTableFieldDefinition() Method exists primarily to get an array that defines a table field. This array can then be used to re-create the table elsewhere, or for any other purpose that may be necessary. Using the MDB2 instance defined above, we will connect to a database, define a table that we want to work with, and reverse engineer a specific field that we are interested in. First, we need to define the table and field that we want to work with; then, it is as easy as a single line of code to get the table definition back as an array of mixed values, then using var_dump to view the results:

Example 36-3. Get the table definition

<?php
// Set the field that we would like to reverse engineer (get definition of)
$field = 'somedate';

// Here we get the definition of the table field and return it to a variable
// The return will either be a mixed array on success, or an MDB2 error on failure,
//so we don't need additional checks
$def = $mdb2->getTableFieldDefinition($table, $field);
// finally dumping the variable to screen
var_dump($def);
?>
The return will look something like the following, depending on your field definition: 
array(1) {
  [0] => array(5) {
    ['notnull'] => bool(false)
    ['nativetype'] => string(4) "date"
    ['default'] => NULL
    ['type'] => string(4) "date"
    ['mdb2type'] => string(4) "date"
  }
}

Additional table information

A number of other methods exist to gain information about a selected table. You may use any of the following methods according to the information needed:

tableInfo() Method

This method will return a lot of information regarding a table, and can be used in a number of different ways. The information that it returns will differ slightly across different RDBM's and may give some variance in results. This method can be used to query either a table definition, or a resultset, which makes it great for creating optimized tables. The tableInfo() method allows you to pass a parameter for the mode that you would like to see the results presented as. In order to demonstrate the results more effectively, we will use a series of examples to do some queries and return the results in different modes. NOTE: Either a table OR a resultset can be passed as the first parameter to get information on the table. In these examples, we will be using the table that we defined above.

Example 36-4. tableinfo() usage 1

<?php
// Default Mode - NULL
$tableInfo = $mdb2->tableInfo($table, NULL);
var_dump($tableInfo);

//will produce:
array(3) {
  [0] => array(11) {
    ['notnull'] => bool(true)
    ['nativetype'] => string(3) "int"
    ['length'] => int(4)
    ['unsigned'] => int(1)
    ['default'] => string(0) ""
    ['autoincrement'] => bool(true)
    ['type'] => string(3) "int"
    ['mdb2type'] => string(7) "integer"
    ['name'] => string(2) "id"
    ['table'] => string(9) "sometable"
    ['flags'] => string(29) "primary_key not_null unsigned"
  }
  [1] => array(10) {
    ['notnull'] => bool(false)
    ['nativetype'] => string(7) "varchar"
    ['length'] => string(2) "12"
    ['fixed'] => bool(false)
    ['default'] => NULL
    ['type'] => string(7) "varchar"
    ['mdb2type'] => string(4) "text"
    ['name'] => string(8) "somename"
    ['table'] => string(9) "sometable"
    ['flags'] => string(0) ""
  }
  [2] => array(8) {
    ['notnull'] => bool(false)
    ['nativetype'] => string(4) "date"
    ['default'] => NULL
    ['type'] => string(4) "date"
    ['mdb2type'] => string(4) "date"
    ['name'] => string(8) "somedate"
    ['table'] => string(9) "sometable"
    ['flags'] => string(0) ""
  }
}
?>
In the subsequent examples, we will only include the first table field definition, as it effectively illustrates the differences in modes. Now, let's change the mode to MDB2_TABLEINFO_ORDER. In addition to the information found in the default output, a notation of the number of columns is provided by the num_fields element while the order element provides an array with the column names as the keys and their location index number (corresponding to the keys in the the default output) as the values.

Example 36-5. tableinfo() usage 2

<?php
$tableInfo = $mdb2->tableInfo($table, MDB2_TABLEINFO_ORDER);
var_dump($tableInfo);

array(5) {
  ['num_fields'] => int(3)
  [0] => array(11) {
    ['notnull'] => bool(true)
    ['nativetype'] => string(3) "int"
    ['length'] => int(4)
    ['unsigned'] => int(1)
    ['default'] => string(0) ""
    ['autoincrement'] => bool(true)
    ['type'] => string(3) "int"
    ['mdb2type'] => string(7) "integer"
    ['name'] => string(2) "id"
    ['table'] => string(9) "sometable"
    ['flags'] => string(29) "primary_key not_null unsigned"
  }
  [1] => array(10) {
    ['notnull'] => bool(false)
    ['nativetype'] => string(7) "varchar"
    ['length'] => string(2) "12"
    ['fixed'] => bool(false)
    ['default'] => NULL
    ['type'] => string(7) "varchar"
    ['mdb2type'] => string(4) "text"
    ['name'] => string(8) "somename"
    ['table'] => string(9) "sometable"
    ['flags'] => string(0) ""
  }
  [2] => array(8) {
    ['notnull'] => bool(false)
    ['nativetype'] => string(4) "date"
    ['default'] => NULL
    ['type'] => string(4) "date"
    ['mdb2type'] => string(4) "date"
    ['name'] => string(8) "somedate"
    ['table'] => string(9) "sometable"
    ['flags'] => string(0) ""
  }
  ['order'] => array(3) {
    ['id'] => int(0)
    ['somename'] => int(1)
    ['somedate'] => int(2)
  }
}
?>
Changing the mode to MDB2_TABLEINFO_ORDERTABLE will give us some additional information by adding additional dimensions to the array in which the table names are keys, and the field names are sub keys. This type of query can be useful when querying complex joins, where some field names may be the same.

NOTE: The flags element will contain a space-separated list of additional information about the field. This data is inconsistent between DBMS's due to the way each DBMS works:

Most DBMS's only provide the table and flags elements if the result is a table name. If the portability option is set to MDB2_PORTABILITY_FIX_CASE, the names of tables and fields will be lower- or upper-cased. If the case is set to CASE_UPPER all tables and fields will be uppercased, as is seen in Oracle and Firebird/Interbase, while CASE_LOWER will lower case all fields and tables ? this is the default setting.

PrevHomeNext
Manager ModuleUpautoPrepare & autoExecute