DB_Table_Database Class Tutorial

Description

DB_Table_Database is an database abstraction class for a relational database. It is a layer built on top of the DB_Table class: Each table in a DB_Table_Database object is represented by a DB_Table object. The most important difference between a DB_Table_Database object and a collection of DB_Table objects is that the properties of a parent DB_Table_Database object contain a model of the entire database, including relationships between tables.

DB_Table_Database provides:

• An object-oriented representation of a relations between tables in a relational database, including linking/association tables that create many-to-many relationships.

• A simplified API for INSERT, UPDATE, DELETE, and SELECT commands, with an interface very similar to that of the DB_Table class.

• Automated construction of join conditions for inner joins of any number of tables, based on a list of table names and/or a list of desired column names.

• Optional checking of the validity of foreign key values by the PHP layer upon insertion or updating.

• Optional PHP emulation of SQL ON DELETE and ON UPDATE referentially triggered actions.

• PHP serialization to (and unserialization from) a string that contains the entire database schema.

• Serialization to (and unserialization) from XML, using an extension of the MDB2 XML schema. (Unserialization from XML requires PHP 5).

• Methods to set various properties of all the child DB_Table objects to a common value.

• Various utility methods that aid the construction of SQL queries.

Like DB_Table, DB_Table_Database wraps a DB or MDB2 database connection object. The class is compatible with both PHP 4 and PHP 5, with the exception of one non-essential method: The fromXML() method, which creates a DB_Table_Database object from an XML database schema, requires PHP 5.

Class DB_Table_Database extends abstract base class DB_Table_Base. Methods or properties that are inherited from DB_Table_Base are noted as such, and are indicated in the table of contents of this page with the notation "(from DB_Table_Base)".

This tutorial uses an extended example to introduce the use of the DB_Table_Database class to create a model of an interface to a relational database.

Contents

• Example Database

• Constructor

• Building a Model

  • addTable() - Adding tables

  • addRef() - Adding references

  • addLink() - Declaring linking tables

  • Deleting Tables, References, or Links

• Referentially Triggered Actions

  • Declaring actions with addRef()

  • setOnDelete() and setOnUpdate()

  • setActOnDelete() and setActOnUpdate()

• Foreign Key Validation

• Data Selection

  • Query Arrays

  • select(), selectResult(), and selectCount() (from DB_Table_Base)

  • autoJoin()

• SQL Utilities

  • quote()

  • buildFilter() (from DB_Table_Base)

  • buildSQL() (from DB_Table_Base)

  • validCol()

• Data Modification: insert(), update(), and delete()

• PHP Serialization

  • setTableSubclassPath()

• XML Serialization

• Setting DB_Table Properties

• Get* Methods

Example Database

Throughout this tutorial, our examples will refer to a DB_Table_Database object for an example database named TestDB, which is described below. The child DB_Table objects that are associated with RDBMS tables must all be instantiated first, and then added to (i.e., linked with) a parent DB_Table_Database object.

The example database TestDB stores names, numbers, and addresses for a set of people, and contains 4 tables. Peoples names, phone numbers, and addresses are stored in three tables named Person, Phone, and Address, respectively. To allow for the fact that several people may share a phone number, and that a person may have more than one phone number, the database allows the creation of a many-to-many relationship between Person and Phone. This relationships are established by an additional linking table, named PersonPhone, which contains foreign key references to Person and Phone.

DB_Table objects must be instantiated before they can be added to a parent DB_Table_Database instance. The usual way of creating a DB_Table object (as discussed in the tutorial for that class) is to create one subclass of DB_Table for each table, and create one instance of each such subclass. In this tutorial, we use a convention in which the subclass of DB_Table associated with a database table named "Entity" is Entity_Table, and in which the single object of this class is $Entity. This is also the convention used in code generated by the DB_Table_Generator class.

The following code defines a subclass Person_Table that represents a database table Person:

<?php
require_once 'DB/Table.php'

class Person_Table extends DB_Table
{
    // Define columns
    $col = array (
        'PersonID' => array('type' => 'integer', 'require' => true),
        'FirstName' => array('type' => 'char', 'size' => 32, 'require' => true),
        'MiddleName' => array('type' => 'char', 'size' => 32),
        'LastName' => array('type' => 'char', 'size' => 64, 'require' => true),
        'NameSuffix' => array('type' => 'char', 'size' => 16),
        'AddressID' => array('type' => 'integer')
    );

    // Define indices. PersonID is declared to be the primary index
    $idx = array(
        'PersonID' => array('cols' => 'PersonID',  'type' => 'primary'),
        'AddressID' => array('cols' => 'AddressID', 'type' => 'normal')
    );

    // Declare 'PersonID' to be an auto-increment column
    $auto_inc_col = 'PersonID';

}
?>

Note the assignment of a value for the $auto_inc_col property, which is a recent addition to DB_Table: The value of $auto_inc_col is the name of a column that is declared to be 'auto increment'. Auto incrementing of this column is now implemented in the insert method of DB_Table using DB or MDB2 sequences.

The following code uses the same method to create subclasses of DB_Table associated with the remaining Phone, Address, and PersonPhone tables of database TestDB:

<?php
class Address_Table extends DB_Table
{
    $col = array(
        'AddressID' => array('type' => 'integer', 'require' => true),
        'Building' => array('type' => 'char', 'size' =>16),
        'Street' => array('type' => 'char', 'size' => 64),
        'UnitType' => array('type' => 'char', 'size' => 16),
        'Unit' => array('type' => 'char', 'size' => 16),
        'City' => array('type' => 'char', 'size' => 64),
        'StateAbb' => array('type' => 'char', 'size' => 2),
        'ZipCode' => array('type' => 'char', 'size' => 16)
    );
    $idx = array(
        'AddressID' => array('cols' => 'AddressID', 'type' => 'primary'),
    );
    $auto_inc_col = 'AddressID';
}
?>

<?php
class Phone_Table extends DB_Table
{
    $col = array(
        'PhoneID' => array('type' => 'integer', 'require' => true),
        'PhoneNumber' => array('type' => 'char', 'size' => 16, 'require' => true),
        'PhoneType'   => array('type' => 'char', 'size' => 4)
    );
    $idx = array(
        'PhoneID' => array('cols' => 'PhoneID', 'type' => 'primary')
    );
    $auto_inc_col = 'PhoneID';
}
?>

<?php
class PersonPhone_Table extends DB_Table
{
    $col = array(
        'PersonID' => array('type' => 'integer', 'require' => true),
        'PhoneID'  => array('type' => 'integer', 'require' => true)
    );

    $idx = array(
        'PersonID' => array('cols' => 'PersonID', 'type' => 'normal'),
        'PhoneID' => array('cols' => 'PhoneID', 'type' => 'normal')
    );
}
?>

The following code instantiates one object of each DB_Table subclass, which is associated with the corresponding table:

<?php
$Person = new Person_Table($conn, 'Person', 'safe');
$Address = new Address_Table($conn, 'Address', 'safe');
$Phone = new Phone_Table($conn, 'Phone', 'safe');
$PersonPhone = new PersonPhone_Table($conn, 'PersonPhone', 'safe');
?>

It is recommended that constructor statements be placed in a separate file from any of the DB_Table subclass definitions. Doing so makes it easier to serialize and unserialize the DB_Table and DB_Table_Database objects, because a php file in which an instance of a DB_Table subclass is unserialized must have access to the subclass definition, but should not include the constructor statements. Putting each DB_Table subclass definition in a separate file, with a name that is the subclass name with a .php extension, also allows the subclass definitions to be autoloaded when an object is serialized, as discussed below.

An alternative way to create a DB_Table object is to create an instance of DB_Table itself, rather than of a subclass of DB_Table. In this method, one first instantiates a generic DB_Table object, which initially contains no information about the table schema, and then sets the values of the public $col and $idx properties needed to define a table schema. As an example, the following code constructs an instance of DB_Table that represents the Person table:

<?php
$Person = new DB_Table($conn, 'Person');

$Person->col['PersonID'] = array('type' => 'integer', 'require' => true);
$Person->col['FirstName'] = array('type' => 'char', 'size' => 32, 'require' => true);
$Person->col['MiddleName'] = array('type' => 'char', 'size' => 32);
$Person->col['LastName'] = array('type' => 'char', 'size' => 64, 'require' => true);
$Person->col['NameSuffix'] = array('type' => 'char', 'size' => 16);
$Person->col['AddressID'] = array('type' => 'integer');

$Person->idx['PersonID'] = array('cols' => 'PersonID', 'type' => 'primary');
$Person->idx['PersonID'] = array('cols' => 'PersonID', 'type' => 'normal');

$Person->auto_inc_col = 'PersonID';
?>

Constructor

A DB_Table_Database object is instantiated as an empty shell, to which tables, foreign key references, and links are then added. The constructor interface is

void DB_Table_Database(DB/MDB2 object $conn, string $name)

<?php
require_once 'DB/Table/Database.php'

$conn = DB::connect("mysqli://$user:$password@$host");
$db = new DB_Table_Database($conn, 'TestDB');
?>

Building a Model

To construct a model of a relational database, after instantiating a set of DB_Table objects and a DB_Table_Database object, we must add the table objects to the database object, add declarations of foreign key references, and declare many-to-many relationships that involve linking tables, in that order.

true|PEAR_Error addTable(object &$Table)

<?php
$db->addTable($Person);
$db->addTable($Address);
$db->addTable($Phone);
$db->addTable($PersonPhone);
?>

true|PEAR_Error addRef(string $ftable, string|array $fkey, 
                       string $rtable, [string|array $rkey] )

<?php
$db->addRef('Person', 'AddressID', 'Address', 'AddressID');
?>

<?php
$db->addRef('Person', 'AddressID', 'Address');
?>

true|PEAR_Error addLink(string $table1, string $table2, string $link)

<?php
$db->addLink('Person', 'Phone, 'PersonPhone')
?>

<?php
$db->addAllLinks()
?>

<?php
require_once 'MDB2.php';
require_once 'DB/Table/Database.php';
require_once 'Person_Table.php';
require_once 'Address_Table.php';
require_once 'Phone_Table.php';
require_once 'PersonPhoneAssoc_Table.php';

// NOTE: User must uncomment & edit code to create $dsn
$phptype  = 'mysqli';
$username = 'root';
$password = 'password';
$hostname = 'localhost';
$dsn = "$phptype://$username:$password@$hostname";

// Instantiate DB/MDB2 connection object $conn
$conn =& MDB2::connect($dsn);
if (PEAR::isError($conn)) {
    print "Error connecting to database server\n";
    print $conn->getMessage();
    die;
}

// Create one instance of each DB_Table subclass
$Person = new Person_Table($conn, 'Person');
$Address = new Address_Table($conn, 'Address');
$Phone = new Phone_Table($conn, 'Phone');
$PersonPhoneAssoc = new PersonPhoneAssoc_Table($conn, 'PersonPhoneAssoc');

// Instantiate a parent DB_Table_Database object $db
$db = new DB_Table_Database($conn, '42A');

// Add DB_Table objects to parent DB_Table_Database object
$db->addTable($Person);
$db->addTable($Address);
$db->addTable($Phone);
$db->addTable($PersonPhoneAssoc);

// Add foreign references
$db->addRef('PersonPhoneAssoc', 'PersonID', 'Person');
$db->addRef('PersonPhoneAssoc', 'PhoneID', 'Phone');
$db->addRef('Person', 'AddressID', 'Address');

// Add all possible linking tables 
$db->addAllLinks();

?>

void deleteTable(string $table)

void deleteRef(string $ftable, string $rtable)

void deleteLink(string $table1, string $table2, [string $link])

On Delete and On Update Actions

DB_Table_Database optionally provides actions designed to enforce referential integrity that are provided by ANSI SQL, but that are not provided by some popular databases (e.g., SQLite and the default MySQL engine). DB_Table_Database offers optional PHP emulation of referentially triggered ON DELETE and ON UPDATE actions, such as cascading deletes (discussed here), and also optionally checks the validity of foreign key values before insertion or updating (discussed below)

The ON DELETE and ON UPDATE actions associated with a reference (if any) may be declared either as additional parameters to addRef(), or by using setOnDelete() and setOnUpdate().

<?php
$db->addRef('PersonPhone', 'PersonID', 'Person', null, 'cascade', 'restrict');
?>

true|PEAR_Error addRef(string $ftable, mixed $fkey, string $rtable, [mixed $rkey], 
                       [string|null $on_delete], [string|null $on_update])

'cascade' | 'restrict' | 'set null' | 'set default'

<?php
$db->addRef('Person', 'AddressID', 'Address', null, 'set null', 'cascade');
$db->addRef('PersonPhone', 'PersonID', 'Person', null, 'cascade', 'cascade');
$db->addRef('PersonPhone', 'PhoneID', 'Phone', null, 'cascade', 'cascade');
?>

void setOnDelete(string $ftable, string $rtable, string|null $action)
void setOnUpdate(string $ftable, string $rtable, string|null $action)

<?php
$db->setOnUpdate('PersonPhone', 'Person', 'restrict');
?>

void setActOnDelete(bool $flag)
void setActOnUpdate(bool $flag)

Foreign Key Validation

By default, DB_Table_Database checks the validity of foreign key values before inserting or updating data in a table with foreign keys. That is, before inserting a row, or updating any foreign key column values, the insert() and update() methods of DB_Table_Database actually submit a query to confirm that the inserted or updated foreign key column values correspond to values of the referenced columns of an existing row in the referenced table. By default, both methods throw an error, and do not modify the data, if this check fails.

This checking of foreign key validity by the PHP layer may be turned on or off, for insertion or updating of any table in database, with the setCheckFKey() method. The interface of this method is:

void setCheckFKey(bool $flag)

Data Selection

DB_Table_Database provides an object-oriented interface for SQL select statements that is almost identical to that of DB_Table.

<?php
$oak = array(
   'select'  => 'Person.FirstName, Person.LastName, Address.Building',
   'from'    => 'Person, Address',
   'where'   => "Person.AddressID = Address.AddressID\n" 
              . "  AND Address.Street = 'Oak Street'\n"
              . "  AND Address.City = 'AnyTown'",
   'order'   => 'Address.Building' );
?>

<?php
echo $db->buildSQL($oak);
?>

SELECT Person.FirstName, Person.LastName, Address.Building
FROM Person, Address
WHERE Person.AddressID = Address.AddressID
  AND Address.Street = 'Oak Street'
  AND Address.City = 'AnyTown'
ORDER BY Address.Building

<?php
$db->sql['oak'] = $oak
?>

<?php
$result = $db->select('oak')
?>

<?php
$result = $db->select($oak)
?>

mixed select*( array|string $sql_key, [string $filter], [string $order], 
               [int $start], [int $count], [array $params])

array|PEAR_Error autoJoin([array $cols], [array $tables], [string $filter])

<?php
$cols = array('FirstName', 'LastName', 'PhoneNumber', 'Building', 'Street', 'City')
$report = $db->autoJoin($cols, "Phone.PhoneType = 'HOME'");
$result = $db->select($report);
?>

<?php
echo $db->buildSQL($report);
?>

SELECT Person.FirstName, Person.LastName, Phone.PhoneNumber, Address.Building, Address.Street, Address.City
FROM Person, Phone, Address, PersonPhone
WHERE PersonPhone.PhoneID = Phone.PhoneID
  AND PersonPhone.PersonID = Person.PersonID
  AND Person.AddressID = Address.AddressID

<?php
$tables = array('Person', 'Address', 'Phone');
$report = $db->autoJoin(null,$tables);
$result = $db->select($report);
?>

SELECT *
FROM Person, Phone, Address, PersonPhone
WHERE PersonPhone.PhoneID = Phone.PhoneID
  AND PersonPhone.PersonID = Person.PersonID
  AND Person.AddressID = Address.AddressID

SQL Utilities

string quote(mixed $value)

<?php
$data = array('Street' => 'Pine St', 'City' => 'Peoria');
$filter = $db->buildFilter($data);
?>

Street = 'Pine St' AND City = 'Peoria'

string|PEAR_Error buildSQL(array|string $query, [string $filter], [string $order], 
                           [int $start], [int $count])

array|PEAR_Error validCol(string $col, [array $from])

Data Modification: insert(), update(), and delete()

The insert(), delete(), and update() methods of DB_Table_Database have interfaces and behaviors similiar to those of the corresponding methods of DB_Table. The only differences in the interfaces are that each of these DB_Table_Database method requires an additional first parameter whose value is the name of the table to which the SQL insert, update, or delete command should be applied.

Synopses:

true|PEAR_Error insert(string $table, array $data)
true|PEAR_Error delete(string $table, [string $where])
true|PEAR_Error update(string $table, array $data, [string $where])

These DB_Table_Database methods are simple wrappers that call the corresponding methods DB_Table methods internally. As one result, overriding any of these methods in a subclass of DB_Table in order to customize the behavior of a specific table will automatically modify the behavior of the DB_Table_Database method.

The DB_Table_Database data insert() and update() methods can validate foreign key values before actually modifying data in the database, and can emulate referentially triggered actions such cascading deletes, if foreign key validation and these referentially triggered actions are enabled. The corresponding methods of DB_Table will take identical actions if the DB_Table has been added to a parent DB_Table_Database object (i.e., if it contains a reference to a parent object), and if these actions are enabled in the parent DB_Table_Database object. Foreign key validation is disabled by default. Referentially triggered actions are enabled by default, for any such action that is declared in the database model.

The insert() and update() methods return a PEAR_Error object if foreign key validation fails, or if an error occurs during any database command. A PEAR_Error is also returned if a 'restrict' ON DELETE or ON UPDATE action is declared, when such actions are enabled, if an attempt is made to delete or update any row that is referenced by a foreign key of one or more rows of another table.

PHP Serialization

One way to maintain the state of DB_Table_Database between web pages is to serialize the entire database as one string, and save it in a session variable, a file, or a database. A serialized DB_Table_Database object contains serialized versions of all of its tables, and thus contains the information necessary to reconstruct the database. Serialization is accomplished by the PHP serialize function:

<?php
$db_serial = serialize($db);
?>

<?php
$db = unserialize($db_serial);
$db->setDBconnection($DB_object);
?>

When a DB_Table_Database object is unserialized, each child DB_Table object is unserialized in turn by the DB_Table_Database::__wakeup() method. If the DB_Table objects are instances of subclasses of DB_Table, this requires that the definitions of these subclasses exist in memory prior to unserialization of the table. This can be accomplished by explicitly including the file or files containing the required class definitions in the file containing the unserialize command, or by taking advantage of an auto-load mechanism that is built into the wake-up method.

In order for autoloading of subclass definitions to work, each of the subclasses must be defined in a separate file in a default directory, with a filename that is given by the class name with an added '.php' extension. If the definition of a required subclass of DB_Table named "classname" is found to not exist in memory when needed during unserialization, the __wakeup() method tries to include a file named "classname.php" in this directory.

For autoloading to work, the base of each such filename must be the class name obtained by applying the built-in get_class function to the object. This yields a lower case class name PHP 4 and preserves the capitalization used in the class definition in PHP 5.

void setTableSubclassPath(string $path)

XML Serialization

The toXML() and fromXML() methods may be used to serialize a database schema to, and unserialize it from, an XML string, respectively. The fromXML() method uses simpleXML to parse the XML string, and so requires PHP 5. (This is the only method in the class that is not compatible with PHP 4).

The XML schema used by these methods is an extension of the current MDB2_Schema DTD, extended so as to allow specification of foreign key references. This extension for foreign keys has been agreed upon for adoption in a future release of MDB2_Schema.

The toXML() method returns an XML string for the entire database, including all of its tables and foreign key references, like so:

<?php
$xml_string = $db->toXML();
?>

<?php
$db = DB_Table_Database::fromXML($xml_string);
$db->setDBconnection($DB_object);
?>

Setting DB_Table properties

Several methods of DB_Table_Database are used to set a common value of a DB_Table property for every child table in the database. These methods, which have the same names and interfaces as the corresponding DB_Table methods, are:

void autoValidInsert(bool $flag);
void autoValidUpdate(bool $flag);
void autoRecast(bool $flag);
void autoInc(bool $flag);

The autoValidInsert and autoValidUpdate methods turn on or off the automatic validation that data is of the expected type prior to insertion or updating of the data. The autoRecast method turns on or off the attempted recasting of data to the expected data type, if necessary, prior to insertion or updating. autoInc turns on or off the PHP implementation of auto-incrementation of the value of the $auto_inc_col column (if any) upon insertion. Note that, when the feature is on, this column is still auto-incremented only if its value is left null in the data to be inserted.

Get* Methods

Most of the properties of DB_Table_Database are private. A get* method is defined for each private property. Please see the API documentation for a discussion of all of properties and associated get* methods.