Manager Module

Creating a database

Example 39-3. 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).

Example 39-4. 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.

<?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);
?>

<?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, ); ?>

Example 39-7. 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.

Example 39-8. 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 values ascending and descending

• length, integer value

<?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.

Example 39-10. 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.