Manager Module
Description
The Manager module provides methods for managing database structure. The methods can be grouped based on their responsibility: create, edit (alter or update), list or delete (drop) database elements. The following document lists the available methods, providing examples of their use. The following tables will be created, altered and finally dropped, in a database named "events_db":
events(id, name, datetime);
people(id, name);
event_participants(event_id, person_id);
To include the Manager module functionality, you need to load it first.
Loading the Manager module
<?php
require_once 'MDB2.php';
$dsn = 'pgsql://someuser:apasswd@somehost';
$mdb2 =& MDB2::factory($dsn);
if (PEAR::isError($mdb2)) {
die($mdb2->getMessage());
}
// loading the Manager module
$mdb2->loadModule('Manager');
?>
After including the module, you can access its methods like this:
Creating a database
<?php
// PHP5
$mdb2->createDatabase('events_db');
// PHP4 and PHP5
$mdb2->manager->createDatabase('events_db');
?>
Further in the document the PHP5-compatible way will be used.
Creating database elements
These are methods to create new databases, tables, indices, constraints and sequences.
Creating a database
Creating and setting an events_db database
<?php
// MDB2 setup
require_once 'MDB2.php';
$dsn = 'mysql://root:test@localhost'; // note that DB name is omitted
$mdb2 =& MDB2::factory($dsn);
// loading the Manager module
$mdb2->loadModule('Manager');
// create the database
$mdb2->createDatabase('events_db');
// set the new database to work with it
$mdb2->setDatabase('events_db');
// the next time the DSN could be like
// mysql://root:test@localhost/events_db
?>
Creating tables
Now that the database is created, we can proceed with adding some tables. The method createTable() takes three parameters: the table name, an array of field definition and some extra options (optional and RDBMS-specific).
Creating the events table
<?php
$definition = array (
'id' => array (
'type' => 'integer',
'unsigned' => 1,
'notnull' => 1,
'default' => 0,
),
'name' => array (
'type' => 'text',
'length' => 255
),
'datetime' => array (
'type' => 'timestamp'
)
);
$mdb2->createTable('events', $definition);
?>
The keys of the definition array are the names of the fields in the table. The values are arrays containing the required key 'type' as well as other keys, depending on the value of 'type'. The values for the 'type' key are the same as the possible MDB2 datatypes. Depending on the datatype, the other options may vary.
| Datatype | length | default | not null | unsigned |
|---|---|---|---|---|
| text | x | x | x | |
| boolean | x | x | ||
| integer | x | x | x | |
| decimal | x | x | ||
| float | x | x | ||
| timestamp | x | x | ||
| time | x | x | ||
| date | x | x | ||
| clob | x | x | ||
| blob | x | x |
The third parameter to createTable() contains extra options for the table, such as the charset, collation, and other DBMS-specific properties, like MySQL's table engine. Here's an example for MySQL.
Creating the people table
<?php
$table_options = array(
'comment' => 'Repository of people',
'charset' => 'utf8',
'collate' => 'utf8_unicode_ci',
'type' => 'innodb',
);
$definition = array (
'id' => array (
'type' => 'integer',
'unsigned' => 1,
'notnull' => 1,
'default' => 0,
),
'name' => array (
'type' => 'text',
'length' => 255
)
);
$mdb2->createTable('people', $definition, $table_options);
?>
To round up the example database, here's the event_participants table creation code.
Creating the event_participants table
<?php
$definition = array (
'event_id' => array (
'type' => 'integer',
'unsigned' => 1,
'notnull' => 1,
'default' => 0,
),
'person_id' => array (
'type' => 'integer',
'unsigned' => 1,
'notnull' => 1,
'default' => 0,
),
);
$mdb2->createTable('event_participants', $definition, $table_options);
?>
Creating constraints
In the example events table, the id should be a primary key. Creating a primary key is a task done by the createConstraint() method. It takes three parameters: the table name, the constraint name and the definition array.
The full structure of the definition array looks like this
(in this case, it's representing a FOREIGN KEY constraint):
<?php
$definition = array (
'primary' => false,
'unique' => false,
'foreign' => true,
'check' => false,
'fields' => array (
'field1name' => array(), // one entry per each field covered
'field2name' => array(), // by the index
'field3name' => array(
'sorting' => ascending|descending,
'position' => 3,
),
)
'references' => array(
'table' => name,
'fields' => array(
'field1name' => array( //one entry per each referenced field
'position' => 1,
),
)
'deferrable' => false,
'initiallydeferred' => false,
'onupdate' => CASCADE|RESTRICT|SET NULL|SET DEFAULT|NO ACTION,
'ondelete' => CASCADE|RESTRICT|SET NULL|SET DEFAULT|NO ACTION,
'match' => SIMPLE|PARTIAL|FULL,
);
?>
Creating primary keys in the events and people tables
<?php
$definition = array (
'primary' => true,
'fields' => array (
'id' => array()
)
);
$mdb2->createConstraint('events', 'keyname', $definition);
$mdb2->createConstraint('people', 'keyname', $definition);
?>
Note: Some RDBMS may choose to ignore the name of the constraint, for example
MySQL will not use the value keyname provided in the
method call, but will use PRIMARY when a primary key is
created, or [tablename]_ibfk_[n] when a foreign key is
created.
In the definition array, you specify which fields will be included in the
constraint, using the fields key. The other possible
keys in the definition array are primary and
unique, which have boolean values.
Let's create another key in the event_participants, where each row has to be
unique, meaning that one person can appear only once for a specific event.
The definitions array will have both fields in the fields
key and the unique key will be set to true.
Creating a unique constraint
<?php
$definition = array (
'unique' => true,
'fields' => array (
'event_id' => array(),
'person_id' => array(),
)
);
$mdb2->createConstraint('event_participants', 'unique_participant', $definition);
?>
Creating indices
To create an index, the method createIndex() is used,
which has similar signature as createConstraint(), so it
takes table name, index name and a definition array. The definition array has
one key fields with a value which is another associative
array containing fields that will be a part of the index. The fields are
defined as arrays with possible keys:
-
sorting, with valuesascendinganddescending -
length, integer value
Not all RDBMS will support index sorting or length, in these cases the drivers will ignore them. In the test events database, we can assume that our application will show events occuring in a specific timeframe, so the selects will use the datetime field in WHERE conditions. It will help if there is an index on this field.
Creating an index
<?php
$definition = array(
'fields' => array(
'datetime' => array()
)
);
$mdb2->createIndex('events', 'event_timestamp', $definition);
?>
Creating sequences
The way MDB2 handles sequences is described here. For the events table in our example database, we'll need to have the 'id' auto-incrementing. For this purpose the method nextId() is used to give the next value. nextId() will create the sequence table if it doesn't exist, but we can create if beforehand with createSequence(). It takes a sequence name and an optional start value for the sequence.
Creating sequences
<?php
$mdb2->createSequence('events');
$mdb2->createSequence('people', 1);
?>
In the default MDB2 setup the example above will create
two tables: events_seq and people_seq, each with one field called 'sequence',
but the field name and the '_seq' postfix are configurable via the MDB2 options
seqname_format and seqcol_name.
Altering database tables
Once a database table is created you can rename it or add, remove, rename and alter fields, using the alterTable() method. alterTable() takes three parameters: the table name, the definition of changes and a boolean "check-only" flag. If true, no changes will be made, but only a check if the proposed changes are feasible for the specific table and RDBMS. The second parameter (definition of changes) is an array with these keys:
-
name: new name for the table. This is the only key related to the table itself, the other keys contain field definitions -
add: fields to be added -
remove: fields to be removed -
change: fields to be changed -
rename: fields to be renamed
The values for add/remove/change/rename keys have slightly different structures, but they all contain field definitions. You can check the API docs for more information and an examples.
Listing database elements
To see what's in the database, you can use the list*() family of functions, namely:
- listDatabases()
- listFunctions()
- listSequences(): takes optional database name as a parameter. If not supplied, the currently selected database is assumed.
- listTableConstraints(): takes a table name
- listTableFields(): takes a table name
- listTableIndexes(): takes a table name
- listTables(): takes an optional database name
- listTableTriggers(): takes a table name
- listTableViews(): takes a table name
- listUsers()
- listViews(): takes an optional database name
Listing database elements
<?php
$dbs = $mdb2->listDatabases();
print_r($dbs);
/*
prints:
Array
(
[0] => information_schema
[1] => events_db
[2] => mysql
[3] => test
[4] => test_db
[5] => test_db_explain
[6] => test_mdb2
)
*/
$seqs = $mdb2->listSequences('events_db');
print_r($seqs);
/*
prints:
Array
(
[0] => events
[1] => people
)
*/
$cons = $mdb2->listTableConstraints('event_participants');
print_r($cons);
/*
prints:
Array
(
[0] => unique_participant
)
*/
$fields = $mdb2->listTableFields('events');
print_r($fields);
/*
prints:
Array
(
[0] => id
[1] => name
[2] => datetime
)
*/
$idx = $mdb2->listTableIndexes('events');
print_r($idx);
/*
prints:
Array
(
[0] => event_timestamp
)
*/
$tables = $mdb2->listTables();
print_r($tables);
/*
prints:
Array
(
[0] => event_participants
[1] => events
[2] => people
)
*/
// currently there is no method to create a view,
// so let's do it "manually"
$sql = "CREATE VIEW names_only AS SELECT name FROM people";
$mdb2->exec($sql);
$sql = "CREATE VIEW last_ten_events AS SELECT * FROM events ORDER BY id DESC LIMIT 0,10";
$mdb2->exec($sql);
// list views
$views = $mdb2->listViews();
print_r($views);
/*
prints:
Array
(
[0] => last_ten_events
[1] => names_only
)
*/
/*
Not implemented in the MySQL driver
listTableTriggers()
listTableViews()
listFunctions()
*/
?>
Deleting database elements
For every create*() method as shown above, there is a corresponding drop*() method to delete a database, a table, field, index or constraint. The drop*() methods do not check if the item to be deleted exists, so it's the developer's responsibility to check for PEAR errors.
Drop database elements
<?php
// let's say our normal setup is to die on PEAR errors
PEAR::setErrorHandling(PEAR_ERROR_DIE);
// for the next statements we'll temporarily
// change the error handling
PEAR::pushErrorHandling(PEAR_ERROR_RETURN);
// drop a sequence
$result = $mdb2->dropSequence('nonexisting');
if (PEAR::isError($result)) {
echo 'The sequence does not exist';
} else {
echo 'Sequence dropped';
}
// another sequence
$result = $mdb2->dropSequence('people');
// drop a constraint
$mdb2->dropConstraint('events', 'PRIMARY', true);
// note: the third parameter gives a hint
// that this is a primary key constraint
$mdb2->dropConstraint('event_participants', 'unique_participant');
// drop an index
$mdb2->dropIndex('events', 'event_timestamp');
// drop a table
$mdb2->dropTable('events');
// drop the database already!
$mdb2->dropDatabase('events_db');
// revert to the usual error handling
PEAR::popErrorHandling();
?>