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

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:

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

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

Synopsis (simplified):

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:

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:

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.

A table may be declared to be a "linking" table that establishes a many-to-may relationship between two others. The only effect of such a declaration is to change the action of the autoJoin method: If a table named $link is declared to be a linking table that creates a many-to-many relationship between tables named $table1 and $table2, then the autoJoin method may use the linking table to join $table1 and $table2, if necessary.

Method addLink() declares a table to be a linking or association table for two others.

Synopsis:

Here, $link is the name of a linking table that links tables named $table1 and $table2. All three parameters are required, and all must be valid table name strings. It does not matter which of the two linked tables, $table1 and $table2, is listed first and which second in the function call. A table that links $table1 and $table2 must have foreign keys that references to both of the linked tables. A link can only be added to the model after the references from the linking table to both of the linked tables have been added. The method returns true on normal completion and a PEAR_Error() if an error is detected.

For example, the command:

declares PersonPhone to be a linking table that links tables Person and Phone.

The addLink() method will not prevent one from declarating more than one linking table for the same two linked tables. Doing so would make the link declaration useless, however, because the autoJoin() method will fail if it needs to use a linking table to join a pair of tables, but finds that more than one of linking table is declared for those two tables.

The command

adds all possible linking tables to the database. In this method, any table that has foreign keys that reference tables $table1 and $table2 is declared to be a link between $table1 and $table2. In some databases, the easiest way to declare links may be by using the addAllLinks method to create all possible links and then using the deleteLink() method to delete those that are not desired.

The database model is complete when all of the tables have been added, all of the references have been added, and all the links have been added.

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:

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.

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:

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:

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.

deleteLink() - deletes a linking table declaration

Synopsis:

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.

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:

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:

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:

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.

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

Synopses:

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:

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

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

Synopses:

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.

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

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

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:

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.

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

or the corresponding array value as a parameter, as in

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:

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

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.

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

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:

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.

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

Synposis:

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

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

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() - validates and (if necessary) disambiguates column names.

Synopsis:

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.

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

Synopsis:

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.