Sonntag, 27. Januar 2008

MDB2 Modules

When looking at some of the examples earlier in this article, you've already seen how the idea of modularity is built into MDB2. The main purpose is to keep the base functionality lightweight and then include more functionality on demand, using the loadModule() method.

Earlier in the tutorial, the Extended module was loaded like this:

$mdb2->loadModule('Extended');

After this call you have access to all the methods that the Extended module provides, such as all the get*() methods. The methods are accessible through the extended property of the $mdb2 instance:

$mdb2->extended->getAssoc($sql);

In addition to that, in PHP5, due to the object overloading functionality, you can access the methods directly as methods of the $mdb2 instance:

$mdb2->getAssoc($sql);

In this tutorial PHP5 was assumed, so all the calls to the module methods benefit from object overloading and are called using this short notation.

Yet another way to access the module's methods is by prefixing them with the short name of the module ex (for "Extended"). This is also PHP5-only.

$mdb2->exGetAssoc($sql);

And finally, you can specify a custom property name to load the module into (works in both PHP 4 and 5):

$mdb2->loadModule('Extended', 'mine');
$mdb2->mine->getAssoc($sql);

The full list of currently available MDB2 modules is as follows (short access names given in brackets):

  • Extended (ex)': You already have an idea of some of the methods available in the Extended module. This module is the only one unrelated to the different database drivers and its definition file (Extended.php) lies in the root MDB2 directory, not in the Drivers directory. This module is defined in the MDB2_Extended class, which inherits the MDB2_Module_Common class.
  • Datatype (dt)': Contains methods for manipulating and converting MDB2 data types and mapping them to types that are native to the underlying database.
  • Manager (mg)': Contains methods for managing the database structure (schema), like creating, listing, or dropping databases, tables, indices, etc.
  • Reverse (rv)': Methods for reverse engineering a database structure.
  • Native (na)': Any methods that are native to the underlying database are placed here.
  • Function (fc)': Contains wrappers for useful functions that are implemented differently in the different databases.

Let's see a few examples that use some of the modules.

Manager Module

Using the Manager module you have access to methods for managing your database schema. Let's see some of its methods in action.

Create a Database

Here's an example that will create a new blank database:

$mdb2->loadModule('Manager');
$mdb2->createDatabase('test_db');
Create a Table

You can use the Manager module to recreate the table people that was used in the earlier examples in this article. This table had four fields:

  • id: An unsigned integer primary key that cannot be null
  • name: A text field, like VARCHAR(255) in MySQL
  • family: Same type as name
  • birth_date: A date field

To create this table you use the createTable() method, to which you pass the table name and an array containing the table definition.

$definition = array ( 'id' => array ( 'type' => 'integer',
'unsigned' => 1,
'notnull' => 1,
'default' => 0,
),
'name' => array ( 'type' => 'text',
'length' => 255
),
'family' => array ( 'type' => 'text',
'length' => 255
),
'birth_date' => array ( 'type' => 'date'
)
);
$mdb2->createTable('people', $definition);
Alter Table

Let's say that after the table was created, you decide that 255 characters are too much for one name. In this case you'll need to set up a new definition array and call alterTable(). The new definition array used for modifications is broken down into the following keys:

  • name: New name for the table
  • add: New fields to be added
  • remove: Fields to be dropped
  • rename: Fields to rename
  • change: Fields to modify

Here's how to modify the name field to store only a hundred characters:

$definition = array(
'change' => array(
'name' => array(
'definition' => array(
'length' => 100,
'type' => 'text',
)
),
)
);
$mdb2->alterTable('people', $definition, false);

If you set the third parameter of alterTable() to true, MDB2 will not execute the changes, but will only check if they are supported by your DBMS.

Constraints

The id field was meant to be the primary key, but so far it isn't. For this purpose you can use the createConstraint() method, which accepts the table name, the name we chose for the constraint, and the array containing the constraint definition.

$definition = array ( 'primary' => true,
'fields' => array ( 'id' => array())
);
$mdb2->createConstraint('people', 'myprimekey', $definition);
Note. Note that MySQL will ignore the myprimekey name of the constraint, because it requires the primary key to always be named PRIMARY.

Now we can specify that the name plus the family name should be unique:

$definition = array('unique' => true,
'fields' => array('name' => array(),
'family' => array(),
)
);
$mdb2->createConstraint('people', 'unique_people', $definition);

On second thoughts, different people sometimes have the same names, so let's drop this constraint:

$mdb2->dropConstraint('people', 'unique_people');
Indices

If there will be a lot of SELECTs on the birth_date field, you can speed them up by creating index on this field.

$definition = array('fields' => array('birth_date' => array(),) );
$mdb2->createIndex('people', 'dates', $definition);

Note that by default MDB2 will add a _idx suffix to all your indices and constraints. If you want to modify this behavior, set the idxname_format option:

$mdb2->setOption('idxname_format', '%s'); // no suffix
Listings

In the Manager module you have also a lot of methods to get information about a database, such as listDatabases(), listUsers(), listTables(), listTableViews(), and listTableFields().

Function Module

The Function module contains some methods to access common database functions, such as referring to the current timestamp, and concatenating or getting partial strings. If you want to access the current timestamp in your statements, you can use the now() method and it will return you the means to get the timestamp in a way that is native for the currently underlying database system.

$mdb2->loadModule('Function');
echo $mdb2->now();

This will output CURRENT_TIMESTAMP when using MySQL and datetime('now') when using SQLite.

now() accepts a string parameter (with values date, time, and timestamp) that specifies if you want the current date, time, or both.

If you wish to concatenate strings in your statements in a database-agnostic way, you can use the concat() method and pass an unlimited number of string parameters to it. For extracting substrings, you have the substring() method. Here's an example that uses both methods:

$mdb2->loadModule('Function');
$sql = 'SELECT %s FROM people';
$first_initial = $mdb2->substring('name', 1, 1);
$dot = $mdb2->quote('.');
$all = $mdb2->concat($first_initial, $dot, 'family');
$sql = sprintf($sql, $all);
$data = $mdb2->queryCol($sql);

echo $sql;
print_r($data);

The print_r() from this code will produce:

Array (
[0] => E.Vedder
[1] => M.McCready
[2] => S.Gossard
...
)

The echo $sql; line will print a different result, depending on the database driver you use. For MySQL it would be:

SELECT CONCAT(SUBSTRING(name FROM 1 FOR 1), '.', family) FROM people

Using the Oracle (oci8) driver, you'll get:

SELECT (SUBSTR(name, 1, 1) || '.' || family) FROM people

In this example only the first character from the value in the name field was extracted. Then it was concatenated with the dot symbol and the full value of the family field. Note that it was necessary to quote the dot in order for it to be treated as a string and not a field name.

Reverse Module

If you want to get information about the table people that was used in the examples in this article, you can call the tableInfo() method:

$mdb2->loadModule('Reverse');
$data = $mdb2->tableInfo('people');

If you print_r() the result, you'll get something like:

Array( [0] => Array ( [table] => people
[name] => id
[type] => int
[length] => 11
[flags] => not_null primary_key
[mdb2type] => integer
)
[1] => Array ( [table] => people
[name] => name
[type] => char
[length] => 100
[flags] =>
[mdb2type] => text
)
...
)

The real magic about the tableInfo() method is that it can return information on the fields based on a query result, not just a table. Let's say you have one more table phones in your database that stores phone numbers of the people from the people table and it looks like this:

id
person_id
Phone
1
1
555-666-7777
2
1
555-666-7788

You can execute a query that joins the two tables and when you get the result, you can pass it to tableInfo():

$mdb2->loadModule('Reverse');

$sql = 'SELECT phones.id, people.name, phones.phone ';
$sql.= ' FROM people ';
$sql.= ' LEFT JOIN phones ';
$sql.= ' ON people.id = phones.person_id ';

$result = $mdb2->query($sql);
$data = $mdb2->tableInfo($result);

Now if you print_r() what tableInfo() returns, you'll get an array that describes the three fields selected in the JOIN statementid, name, and phone:

Array (
[0] => Array ( [table] => phones
[name] => id
[type] => int
[length] => 11
[flags] => primary_key
[mdb2type] => integer
)
[1] => Array ( [table] => people
[name] => name
[type] => char
[length] => 100
[flags] =>
[mdb2type] => text
)
[2] => Array ( [table] => phones
[name] => phone
[type] => char
[length] => 20
[flags] =>
[mdb2type] => text
)
)

1 Kommentar:

Unknown hat gesagt…

Hi, your posts are very informative and helpful. I am having a problem with executeStoredProc and inout parameters. Could you post an example on that?