Reverse Module
Description
The Reverse module is an extension of the Common Module driver, and is made up in the following structure:
- Common driver file that encapsulates an API that can be used to execute the reverse driver queries.
- A set of DBMS specific drivers that are specifically written against the supported database back end (e.g. mysql.php for MySQL, and pgsql.php for PostgreSQL)
Usage
In order to use the MDB2 Reverse drivers, it is necessary to first load the Reverse driver into your MDB2 instance. Let's start a MDB2 instance and set up our connection:
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:
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:
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:
<?php
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:
- getTableIndexDefinition(): - requires a table name and an index name to return information about the table index as an array
- getTableConstraintDefinition(): requires a table name and a constraint to query against. This will return any constraint definitions that exist on the table. A constraint as is defined here is usually a Primary Key, an Unique Key or a Foreign Key. The returned array has the same structure as the one used in the Manager module with createConstraint().
- getSequenceDefinition(): requires a sequence name. It will return information about the existing table sequence. The method will return an array on success or an MDB2 error on failure.
- getTriggerDefinition(): takes a trigger name as an argument, and will return information as an array on the trigger queried.
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.
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 default
output) as the values.
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 RDBMS's due to the way each RDBMS works:
primary_keyunique_keymultiple_keynot_nullunsigned
Most RDBMS'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 field and table names;
this is the default setting.