DB_Table_Database Class Tutorial

DB_Table_Database Class Tutorial – Interface to a relational database

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.

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';

}
?>

Here, 'PersonID' is the primary index of the Person table. Column AddressID is a foreign key that references the primary key of the Address table (defined below).

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

In ths example, the PhoneType column of table Phone is used to distinguish home, work, and cell phones, and so must have one of the 4 character values 'HOME', 'WORK' or 'CELL'.

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

Here, because we have used the value 'safe' for the optional third parameter of the DB_Table constructor in each statement, each table will be created in the RDBMS only if and only if a table of that name does not already exist in the database.

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

This method is valid only in recent versions of the DB_Table package (1.5.0RC1 and greater) that contain the DB_Table_Database class. Earlier versions of DB_Table required that DB_Table always be extended. The only real disadvantage of using such generic DB_Table objects is that it makes it impossible to override the methods of DB_Table to, for example, customize the insert or update method so as to implement business rules for a table. Generic DB_Table objects are used by the fromXML() method, which takes an XML description of a database schema as a parameter, and returns a DB_Table_Database object in which each of the child tables is represented by an instance of DB_Table.

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)

The parameter $conn must be either a DB or MDB2 object, which establishes a connection to a RDBMS. The $name parameter is the name of the database. To instantiate an object that represents a database named TestDB with a DB connection to a MySQL database, we might thus use (with no error checking):

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

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

where the values of $user, $password, and $host are the user name, database password, and host machine, respectively.

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.

Adding Tables

After a DB_Table object is instantiated, it can be added to the parent database with the DB_Table_Database::addTable() method. The interface for this method is



true|PEAR_Error addTable(object &$Table)

where $Table is a DB_Table object that is passed by reference. The method returns boolean true on normal completion, and a PEAR_Error object on failure.

The following code adds the four tables of our example database to the $db DB_Table_Database object:

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

In this and all subseqent examples, we omit the error handling code that should be added to production code.

Adding Foreign Key References

After tables have been added to a database, we can use the addRef() method to add references between pairs of tables.

Synopsis (simplified):



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

Here $ftable is the name of a referencing (or foreign key) table, $fkey is the foreign key, $rtable is the name of the referenced table, and $rkey is the (optional) referenced key. If the optional $rkey parameter is absent or null, the referenced key is taken by default to be the primary key of the referenced table. The foreign and referenced key values are specified using the same syntax as that used to define indices in DB_Table: Each key may be either a column name string, for a single-column key, or a sequential array of column names, for a multi-column key. The method returns true on normal completion, and a PEAR_Error on failure. The simplified synopsis shown here does not include two more optional parameters (parameters 5 and 6) that can be used to specify 'on delete' and 'on update' actions. The full interface is presented below. For example, the command:

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

adds a reference from foreign key Person.AddressID of referencing table Person to the primary key Address.AddressID of referenced table Address. Because the referenced key 'AddressID' is also the primary key of table 'Address' this could also be written as:

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

When the referenced key is explicitly specified, as in the first example, it should always be either a primary key or a key for which a unique index is defined, as required by standard SQL.

A reference between two tables can only be added after both the referencing and referenced DB_Table objects have been instantiated and added to the parent DB_Table_Datbase instance.

Example - Putting it Together

For our example, let us create a directory in which to put all of the code required as an interface to a database. We will put each DB_Table subclass definition in a separate file in this directory, in which each file name is simply the class name with a '.php' extension. In addition, it is convenient to create a single file, which we will call 'Database.php', in which we create a DB or MDB2 connection, create one object per table, and construct a parent DB_Table_Database object. This file structure is used by the DB_Table_Generator class for code that is auto-generated for an existing database. Below is a listing of the minimal 'Database.php' file required for our example database:

Database.php File

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

?>

This example file is very similar to the skeleton file that would be created by DB_Table_Generator for an existing database with this structure. The main differences are that some lines in the auto-generated file would have to be uncommented or edited to produce the above (e.g., the lines that define the database DSN). In this example, the call to addAllLinks() method would correctly identify 'PersonPhoneAssoc' as a table that links 'Person' and 'Phone'. This example does not include any referentially triggered 'ON DELETE' or 'ON UPDATE' actions, discussed below, which could be added to the end of the same file.

Deleting Tables, References, and Links

The deleteTable(), deleteRef(), and deleteLinks() methods can be used to delete tables, and foreign key references, and linking table declarations, respectively, from the DB_Table_Database model.

deleteTable() - deletes a table from the database model

Synopsis:



void deleteTable(string $table)

Parameter $table is the name of the table to be deleted. Deletion of a table causes deletion of the table and all other entities of the model that depend on the existence of that table, including foreign key references to or from that table, and linking relationships that depend upon the existence of those foreign key references.

deleteRef() - deletes a reference from the database model

Synopsis:



void deleteRef(string $ftable, string $rtable)

where $ftable and $rtable are the names of the referencing and referenced tables, respectively. Deletion of a foreign key reference causes deletion of any links that rely on the existence of that reference, i.e., links in which $ftable is the linking table and $rtable is one of the linked tables.

Synopsis:



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

Here $table1 and $table2 are names of the linked tables, and the optional parameter $link is the name of the linking table. If $link is null or absent, all declarations of linking tables between $table1 and $table2 are deleted. If $link is present, only the declaration of $link as a linking table between $table1 and $table2 is deleted (if one exists). The deleteLinks() method may be used after the addAllLinks() to prune the resulting set of linking table declarations.

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().

Declaring actions in addRef()

Actions to be taken on deletion or updating of a referenced row may may be declared when a reference is added to the model using two optional parameters of the addRef() method. The following example shows the extended form of addRef() needed to add a reference from PersonPhone to Person (as above), while also declaring a cascade action on delete of a referenced row of Person, and a restrict action on update of such a row:

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

Here, a null value of the fourth parameter is used to indicate that the referenced key should be taken, by default, to be primary key of referenced table Person. The values of the fifth and sixth parameters represent actions to be taken upon delete ('cascade') and upon update ('restrict'), respectively. A null or absent value for either of these parameters indicates that no referentially triggered action should be taken on delete or on update.

The effect of the 'cascade' values of the fifth parameter in the above example is to declare that that all referencing rows of PersonPhone should be deleted upon deletion of a corresponding referenced row of Person (a cascading delete). The 'restrict' value of the sixth parameter declares that updating of the primary key PersonID of Person should be prevented (the 'restrict' on update action), and an error should be thrown by the update method, in rows of Person that are referenced by rows of PersonPhone.

The full interface of the addRef() method is:



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

Here, $ftable is the referencing table, $fkey is the foreign key, $rtable is referenced table, and $rkey is the referenced key. The $fkey and $rkey parameters may be column name strings or arrays of column names, or $rkey may be null. An absent or null value of $rkey indicates a reference to the primary key of the referenced table. The $on_delete and $on_update parameters indicate actions to be taken on deletion or updating of a referenced row. The only allowed values of $on_delete and $on_update are the string literals

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

or PHP null, which is the default value for both parameters. Each of the allowed action strings is the lower case form of a standard SQL action, and turns on PHP emulation of the corresponding action. An absent or null value for either action indicates that no action should be taken at the PHP layer upon delete or update of a referenced row. (Note that a PHP null value is different from the 'set null' action string.)

The following example declares all of the foreign key references needed in our example database, with appropriate referentially triggered actions:

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

As a result of these declarations, rows in the linking table PersonPhone will be deleted when corresponding rows of either Person or Phone are deleted, and updated if the primary keys of reference rows Person or Phone are modified. The foreign key AddressID of a row in table Person will be set to null if the corresponding referenced row of Address is deleted (to indicate that no address is known), and updated if the primary key of that row in Address is modified.

setOnDelete() and setOnUpdate()

The referentially triggered actions associated with a foreign key reference may also be changed, or turned off, with the setOnDelete() and setOnUpdate() methods.

Synopses:



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

Here, $ftable and $rtable are the names of referencing (foreign key) and referenced table, respectively, for an existing reference. The $action parameter is the value for the on_delete or on_update action for that reference, i.e., either an action strings or null. A null parameter is used to indicate that no action on delete or update of rows of table $rtable.

For example, the following code would change the 'on_update' action associated with the reference from 'PersonPhone' to 'Person' to a 'restrict' action:

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

The effect this is to prohibit updates of the primary key value in rows of Person that are referenced by rows of PersonPhone.

setActOnDelete() and setActOnUpdate()

PHP emulation of referentially triggered actions may be turned on or off for the entire database by the setActOnDelete() and setActOnUpdate() methods.

Synopses:



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

Passing a true value to either method activates PHP emulation of all of the declared ON DELETE or ON UPDATE actions, respectively, while a false value turns off PHP emulation of the corresponding action. By default, PHP emulation of both ON DELETE and ON UPDATE actions is on. Calling either of these methods with a false value does not modify the values of the instance property (the $_ref property) that records the on delete or on update actions associated with each reference: It merely prevents PHP emulation of these actions by the DB_Table_Database::delete() and DB_Table_Database::update() methods.

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)

Passing a true value of $flag turns on checking of foreign keys (the default), while a false value turns checking off.

Data Selection

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

Query Arrays

As in DB_Table, queries are represented in DB_Table_Database as arrays, in which array elements represents clauses of a corresponding SQL select statement. For example, a query for names of all people that live on Oak Street in Anytown in our example database might be


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

The buildSQL() method accepts such a query array as a parameter and returns the corresponding SQL command string. For example

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

yields the output

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

The string values of most values in this array are passed to the RDBMS unmodified, prefixed by the keywords 'SELECT', 'FROM', etc. Column names that appear in only one table often do not need to be qualified by table names, as they are in the above example.

As in DB_Table, such query arrays can be stored in the public $sql property array:

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

Representing queries as arrays, rather than strings, makes it easier for baseline queries to be modified by, for example, adding additional limitations to the end of the 'where' clause string.

Select* Methods: select(), selectResult(), and selectCount()

The select*() methods are inherited by both the DB_Table_Database and DB_Table classes from the DB_Table_Base class, and thus share the same interface and behavior. The interface is also the same for all three methods. The required first parameter can be either the key for a previously stored query array, as in

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

or the corresponding array value as a parameter, as in

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

The select method returns a result set as a numerically indexed array of rows. Each row can represented as be either an associative or numerical array, or an object, depending on the value of the $fetchmode property of the DB_Table_Database object or (if this is null) the fetchmode of the underlying DB or MDB2 object.

The common interface of three select* methods select(), selectCount(), and selectResult() is:



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

As discussed above, $sql_key is either a query array, or the key of a baseline query array that has been stored in the $sql property. The $filter parameter is an SQL logical expression string that limits the result set. This condition is added (i.e., ANDed) to the end of the 'where' element of the $sql_key query array. The $order parameter is an ORDER BY clause (without the ORDER BY prefix) that can be used to override any 'order' element of the $sql_key array. Integer parameters $start is which the position within the full result set of the first row that should be included in the return value, while $count is the maximum number of rows desired within the return value. If present, $params is an array in which the values are parameters for placeholder substitution in a prepared query.

autoJoin()

autoJoin() - accepts an array parameter containing the names of desired columns and/or an array of tables names, and returns a query array containing a WHERE clause with automatically generated join conditions.

Synopsis:

<?php
array|PEAR_Error autoJoin([array $cols], [array $tables], [string $filter])
?>

Here, $cols is a sequential array of the names of the desired columns, $tables is a sequential array of names of tables to be joined, and $filter is an SQL logical statement that may be used to limit the results. The $filter clause is added (i.e., ANDed) to the end of the the 'where' element, after the automatically generated join conditions. Both the $col and $tables parameter are optional, but at least one of them must be supplied. The query returned by autoJoin is an inner join of a set of tables containing all of those listed in the $tables parameter, all of the tables containing the columns listed in the $cols parameter, and any linking tables required to join these tables.

The following example generates and submits query that selects a result set in which each row contains a person's name, home phone number and address, based on knowledge of the names of the desired columns. In our example database, this requires that all four tables be joined.

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

Note that the column names in the $cols parameter do not need to be qualified by table names if they are unambiguous -- the autoJoin method internally uses the validCol() method to validate and disambiguate all qualified and unqualified column names.

The SQL command corresponding to such a query array may be obtained using buildSQL(). In this example,the command

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

yields

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

If autoJoin() is passed only a set of column names, as in the above example, it identifies the set of tables that contain those columns, and joins those tables, plus any linking tables needed to create many-to-many relationships.

In the following example, the $cols parameter is null, but the names of the tables to be joined are specified in the $tables parameter:

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

The corresponding SQL command is

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

When the first argument of autoJoin is null, as in this example, the SELECT clause is taken to be 'SELECT * by default. Note that the FROM and WHERE clauses join a linking table PersonPhone that was not explicitly specified, because this table was necessary to join two of the tables containing the desired data.

Algorithm: The algorithm used by autoJoin() is designed to find appropriate join conditions if these exist and are unambiguous, and to return a PEAR Error if the structure of references and linking tables either yields a multiply connected network of joins, or if it cannot construct an appropriate set of join conditions. The method first examines the $col and $table property to identify the list of tables that must be joined. It then creates a network of joined tables (the joined set) by starting with one table and sequentially adding tables to the joined set from the set of tables have not yet been joined (the unjoined set). The process starts by taking the first required table as a nucleus of the joined set, and then iterating through the unjoined set in search of a table that can be joined to the first. During this and each subsequent stage of addition, the method iterates through the unjoined set in search of a table that can be joined to any table in the joined set (i.e., that either references or is referenced by one of the tables in the joined set.) If it finds an unjoined table that can be joined to exactly one table in the joined set, that table is added to the joined set, and the search for another table to join begins. If the search encounters a table in the unjoined set that can be joined to two or more tables in the joined set, the method returns an error indicating that the join conditions are ambiguous -- the method will only return a set of joins that correspond to a tree graph (where tables are nodes and joins are bonds) and will reject any multiply connected set of joins. If it is found that none of the tables in the unjoined set can be directly joined to any table in the joined set, the method then cycles through the unjoined set again in search of a table that can be joined to exactly one table in the joined set through a linking tables. If it finds a table that is connected via linking tables to two or more tables in the joined set, it will also return an error. If the search does not identify any unjoined table that can be joined to a table in the joined set either through a direct reference or a linking table, the method returns an error indicating that the required set of tables can not be joined.

SQL Utilities

quote()

The quote method returns an SQL literal string representation of the parameter $value.

Synposis:



string quote(mixed $value)

The DB_Table_Database::quote() method calls either the DB::quoteSmart() or MDB2::quote() method internally. The return value is always a string, with a return value whose format depends upon the PHP type of $value: If $value is a string, the method returns a string that is properly quoted and escaped for the underlying RDBMS. If $value is an integer or float, it returns an unquoted string representation of the number. If $value is boolean, it returns '1' for true, or '0' for false (consistent with the representation of booleans as integers used by the DB_Table abstract data type). If $value is null, it returns the unquoted string NULL.

buildFilter()

buildFilter() returns a SQL logical expression that is true if the values of a specified set of database columns are equal to a corresponding set of SQL literal values. It must be passed an array parameter in which the array keys are column names and the array values are the required values.

The following example uses the buildFilter method to construct a filter for addresses on Pine St. in Peoria:

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

Printing $filter then yields the SQL snippet:

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

In this example, the resulting SQL string is admittedly longer than the code required to create it. The function becomes more useful when the column names and/or values are variables representing data of various types, rather than string literals, or strings that may require escaping. The buildFilter method uses the quote method internally to construct SQL literal string representations of values.

buildSQL()

buildSQL() - takes a query array of the form used by the select* methods, and returns a corresponding SQL command string. It is called internally by the select*() methods. Both buildSQL() and the select*() methods are inherited from DB_Table_Base.

Synopsis: The interface is similar to that of the select*() methods:



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

As in the select*() methods, $query is a query array or a key for a query array stored in the $sql property, $filter is an SQL logical condition that is added to the 'where' element of the query array, $order is an ORDER BY clause that overrides the 'order' element of the query array when it is present, and $start and $count are the first row in the result set that should be returned, and the maximum number of rows that should be returned. Only the $query argument is required.

validCol()

validCol() - validates and (if necessary) disambiguates column names.

Synopsis:



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

The required parameter $col is a column name. The optional $from parameter is a sequential array of table names.

The $col parameter may either be a column name qualified by a table name, using the SQL syntax table.column, or a column name that is not qualified by a table name, if the identification of the column with a table is unambiguous. The return value of validCol, upon success, is a sequential array in which the second element is the unqualified column name string, and the first element is either a table name (if $col is qualified by a table name or a unique table can be identified) or a sequential array of possible column names (if $col is an unqualified column name that could refer to columns in two or more different tables). If no column with the specified name exists in the database, a PEAR error is returned.

The optional $from parameter is used only when $col is not explicitly qualified by a table name. When it is present, $from is a sequential list of tables that should be searched for a column of the specified name (as in the from clause of an SQL select statement). In this case, validCol first searches the tables in $from, and returns a table name if this yields a unique result. If a set of or more tables in $from are found to contain a column with the specified name, the return value is that set, or a subset thereof. If none of the table in $from contain a columns with the specified name, the search is instead broadened to all tables in the database. If two or more choices still remain at this point (either more than one tables in from, or more than one tables in the rest of the database) the method tries excluding tables in which the specified column is a foreign key column, if this still leaves one or more tables in which the column is not a foreign key column.

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])

In all three functions $table_name is the name of the table to which the operation should be applied. In the insert and update methods, $data is an associative array of data to be inserted or updated, in which the keys are column name strings and the values are the value to be inserted or updated in the database. In the delete and update methods, the optional $where parameter is a string containing an SQL logical condition that is used to select the rows that should be deleted or updated, respectively. That is, the $where parameter should contain the contents of the WHERE clause of the corresponding SQL command, without the 'WHERE ' prefix. Each method returns true on normal completion, and a PEAR Error if an error is encountered.

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

The following two commands are necessary to unserialize and restore the state of a DB_Table_Database object:

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

where $DB_object is a DB or MDB2 connection object. The setDBconnection method sets the same database connection for the parent DB_Table_Database object and all of the child DB_Table objects.

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.

setTableSubclassPath()

setTableSubclassPath() - sets the path to the default directory for DB_Table subclass definitions.

Synopsis:



void setTableSubclassPath(string $path)

Parameter $path is the path to the desired directory, without a trailing directory separator. The path must be specified in the form required by a 'require_once" statement, with the current PHP settings.

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

The DB_Table_Database::fromXML() method is a static method that takes an MDB2 XML database schema string as its only parameter and returns a DB_Table_Database object containing all the tables and references in the database. The following pair of commands is necessary to create a DB_Table_Database object and connect it to a RDBMS

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

Here, as for unserialization, the setDBconnection() method is used to establish a connection be the new object and a database server, where $DB_object is a DB or MDB2 connection object. The tables of the DB_Table_Database object that is returned by fromXML() are all instances of DB_Table itself, rather than of custom subclasses of DB_Table. fromXML() returns a PEAR_Error if the XML string cannot be parsed, if an error is thrown during instantiation of either the DB_Table_Database object or any of the child DB_Table objects, or if called with a PHP 4 interpreter (it requires PHP 5).

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

Each has a boolean argument that turns on (true) or off (false) one of the features of DB_Table. All of the relevant features affect data insertion and updating, and are implemented within the DB_Table insert() and update() methods. The DB_Table_Database insert() and update() methods simply call the corresponding DB_Table methods, so changes in these properties also change the behavior of the DB_Table_Database methods.

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.

Creating HTML_QuickForm forms (Previous) Code generation for an existing database (Next)
Last updated: Thu, 02 Oct 2014 — Download Documentation
Do you think that something on this page is wrong? Please file a bug report or add a note.
View this page in:

User Notes:

There are no user contributed notes for this page.