cvs: peardoc /ja/package/database/db-table intro-database.xml

http://cvs.php.net/viewvc.cgi/peardoc/ja/package/database/db-table/intro-database.xml?view=markup&rev=1.1
Index: peardoc/ja/package/database/db-table/intro-database.xml
+++ peardoc/ja/package/database/db-table/intro-database.xml
<?xml version="1.0" encoding="ISO-8859-1" ?>
<!-- $Revision: 1.1 $ -->

<refentry id="package.database.db-table.intro-database">
 <refnamediv>
   <refname><classname>DB_Table_Database</classname> Class Tutorial</refname>
   <refpurpose>
   Interface to a relational database
   </refpurpose>
 </refnamediv>

<refsect1 id="package.database.db-table.intro-database.desc">
&title.desc;

<para>
<classname>DB_Table_Database</classname> is an database abstraction 
class for a relational database. It is a layer built on top of the 
<classname>DB_Table</classname> class: 
Each table in a <classname>DB_Table_Database</classname> object is 
represented by a <classname>DB_Table</classname> object. 
The most important difference between a 
<classname>DB_Table_Database</classname> object and 
a collection of <classname>DB_Table</classname> objects is that the 
properties of a parent <classname>DB_Table_Database</classname> object 
contain a model of the entire database, including relationships between
tables.
</para><para>
<classname>DB_Table_Database</classname> provides:
<itemizedlist>
<listitem>
  <simpara>
  An object-oriented representation of a relations between tables in a 
  relational database, including linking/association tables that create
  many-to-many relationships.
  </simpara>
</listitem>

<listitem>
  <simpara>
  A simplified API for INSERT, UPDATE, DELETE, and SELECT commands, 
  with an interface very similar to that of the 
  <classname>DB_Table</classname> class.
  </simpara>
</listitem>

<listitem>
  <simpara>
  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.
  </simpara>
</listitem>

<listitem>
  <simpara>
  Optional checking of the validity of foreign key values by the PHP 
  layer upon insertion or updating.
  </simpara>
</listitem>

<listitem>
  <simpara>
  Optional PHP emulation of SQL ON DELETE and ON UPDATE referentially 
  triggered actions.
  </simpara>
</listitem>

<listitem>
  <simpara>
  PHP serialization to (and unserialization from) a string that contains 
  the entire database schema.
  </simpara>
</listitem>

<listitem>
  <simpara>
  Serialization to (and unserialization) from XML, using an extension 
  of the MDB2 XML schema. (Unserialization from XML requires PHP 5).
  </simpara>
</listitem>

<listitem>
  <simpara>
  Methods to set various properties of all the child 
  <classname>DB_Table</classname> objects to a common value.
  </simpara>
</listitem>

<listitem>
  <simpara>
  Various utility methods that aid the construction of SQL queries.
  </simpara>
</listitem>

</itemizedlist>
</para>

<para>
Like <classname>DB_Table</classname>, <classname>DB_Table_Database</classname> 
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 
<link linkend="package.database.db-table.intro-database.xml">
<function>fromXML</function> 
</link> 
method, which creates a <classname>DB_Table_Database</classname> object 
from an XML database schema, requires PHP 5. 
</para>

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

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

<itemizedlist>
  <title>Contents</title>
  <listitem>
     <simpara><link linkend="package.database.db-table.intro-database.example">Example Database</link></simpara>
  </listitem>
  <listitem>
     <simpara><link
linkend="package.database.db-table.intro-database.constructor">Constructor</link></simpara>
  </listitem>
  <listitem>
     <simpara>
     <link linkend="package.database.db-table.intro-database.model">Building a Model</link>
     </simpara>
     <itemizedlist>
       <listitem>
          <simpara>
          <link linkend="package.database.db-table.intro-database.model.addTable">
          <function>addTable</function></link> - Adding tables
          </simpara>
       </listitem>
       <listitem>
          <simpara><link linkend="package.database.db-table.intro-database.model.addRef">
       <function>addRef</function></link> - Adding references</simpara>
       </listitem>
       <listitem>
          <simpara><link
          linkend="package.database.db-table.intro-database.model.addLink">
          <function>addLink</function></link> - Declaring linking tables</simpara>
       </listitem>
       <listitem>
          <simpara>
            <link linkend="package.database.db-table.intro-database.model.delete">
            Deleting Tables, References, or Links
            </link>
          </simpara>
       </listitem>
     </itemizedlist>
  </listitem>
  <listitem>
     <simpara>
       <link linkend="package.database.db-table.intro-database.refAction">
       Referentially Triggered Actions
       </link>
     </simpara>
     <itemizedlist>
        <listitem>
        <simpara>
          <link linkend="package.database.db-table.intro-database.refAction.addRef">
          Declaring actions with <function>addRef</function>
          </link>
        </simpara>
        </listitem>
        <listitem>
        <simpara>
        <link linkend="package.database.db-table.intro-database.refAction.modifyRefOptions">
          <function>setOnDelete</function> and <function>setOnUpdate</function>
        </link>
        </simpara>
        </listitem>
        <listitem>
          <simpara>
            <link linkend="package.database.db-table.intro-database.refAction.setActOn">
            <function>setActOnDelete</function> and 
            <function>setActOnUpdate</function>
            </link>
          </simpara>
        </listitem>
     </itemizedlist>
  </listitem>
  <listitem>
     <simpara>
        <link linkend="package.database.db-table.intro-database.FKeyCheck">
        Foreign Key Validation</link>
     </simpara>
  </listitem>
  <listitem>
     <simpara>
     <link linkend="package.database.db-table.intro-database.select">
     Data Selection</link>
     </simpara>
     <itemizedlist>
        <listitem>
           <simpara>
           <link linkend="package.database.db-table.intro-database.select.query">
             Query Arrays
           </link>
           </simpara>
        </listitem>
        <listitem>
           <simpara>
           <link linkend="package.database.db-table.intro-database.select.methods">
             <function>select</function>, <function>selectResult</function>, and
             <function>selectCount</function>
           </link> 
           (from <classname>DB_Table_Base</classname>)
           </simpara>
        </listitem>
        <listitem>
           <simpara>
           <link linkend="package.database.db-table.intro-database.select.autoJoin">
           <function>autoJoin</function></link>
           </simpara>
        </listitem>
     </itemizedlist>
  </listitem>
  <listitem>
     <simpara>
     <link linkend="package.database.db-table.intro-database.util">
     SQL Utilities</link>
     </simpara>
     <itemizedlist>
        <listitem>
           <simpara>
              <link linkend="package.database.db-table.intro-database.util.quote">
              <function>quote</function></link>
           </simpara>
        </listitem>
        <listitem>
           <simpara>
              <link linkend="package.database.db-table.intro-database.util.buildFilter">
              <function>buildFilter</function></link>
              (from <classname>DB_Table_Base</classname>)
           </simpara>
        </listitem>
        <listitem>
           <simpara>
              <link linkend="package.database.db-table.intro-database.util.buildSQL">
              <function>buildSQL</function></link>
              (from <classname>DB_Table_Base</classname>)
           </simpara>
        </listitem>
        <listitem>
           <simpara>
              <link linkend="package.database.db-table.intro-database.util.validCol">
              <function>validCol</function></link>
           </simpara>
        </listitem>
     </itemizedlist>
  </listitem>
  <listitem>
     <simpara>
        <link linkend="package.database.db-table.intro-database.modify">
        Data Modification: 
        <function>insert</function>, <function>update</function>, and 
        <function>delete</function></link>
     </simpara>
  </listitem>
  <listitem>
     <simpara>
        <link linkend="package.database.db-table.intro-database.serialize">
        PHP Serialization
        </link>
     </simpara>
     <itemizedlist>
        <listitem>
           <simpara>
              <link linkend="package.database.db-table.intro-database.serialize.setTableSubclassPath">
              <function>setTableSubclassPath</function>
              </link>
           </simpara>
        </listitem>
     </itemizedlist>
  </listitem>
  <listitem>
     <simpara>
        <link linkend="package.database.db-table.intro-database.xml">XML Serialization</link>
     </simpara>
  </listitem>
  <listitem>
     <simpara>
        <link linkend="package.database.db-table.intro-database.DBTableProps">
        Setting <classname>DB_Table</classname> Properties
        </link>
     </simpara>
  </listitem>
  <listitem>
     <simpara>
     <link linkend="package.database.db-table.intro-database.get">Get* Methods</link>
     </simpara>
  </listitem>
</itemizedlist>
</refsect1>

<refsect1 id="package.database.db-table.intro-database.example">
<title>Example Database</title>
<para>
Throughout this tutorial, our examples will refer to a 
<classname>DB_Table_Database</classname> 
object for an example database named TestDB, which is described below. 
The child <classname>DB_Table</classname> objects that are associated 
with RDBMS tables must all be instantiated first, and then added to 
(i.e., linked with) a parent <classname>DB_Table_Database</classname> 
object. 
</para>
<para>
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.
</para> 

<para>
DB_Table objects must be instantiated before they can be added to 
a parent <classname>DB_Table_Database</classname> instance. The usual 
way of creating a <classname>DB_Table</classname> object (as discussed 
in the tutorial for that class) is to create one subclass of 
<classname>DB_Table</classname> for each table, and create one instance 
of each such subclass. In this tutorial, we use a convention in which 
the subclass of <classname>DB_Table</classname> 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 <classname>DB_Table_Generator</classname> class.
</para> <para>
The following code defines a subclass <classname>Person_Table</classname> 
that represents a database table Person:
<programlisting role="php">
<![CDATA[
<?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';

}
?>]]>
</programlisting>
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). 
</para><para>
Note the assignment of a value for the $auto_inc_col property, which 
is a recent addition to <classname>DB_Table</classname>: 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 <classname>DB_Table</classname> using DB or MDB2 sequences.
</para><para>
The following code uses the same method to create subclasses of 
<classname>DB_Table</classname> associated with the remaining Phone, 
Address, and PersonPhone tables of database TestDB:
<programlisting role="php">
<![CDATA[<?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';
}
?>]]>
</programlisting>
<programlisting role="php">
<![CDATA[<?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';
}
?>]]>
</programlisting>
<programlisting role="php">
<![CDATA[<?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')
    );
}
?>
]]>
</programlisting>
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'.
</para><para>
The following code instantiates one object of each <classname>DB_Table</classname> 
subclass, which is associated with the corresponding table:
<programlisting role="php">
<![CDATA[<?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');
?>]]>
</programlisting>
Here, because we have used the value 'safe' for the optional third 
parameter of the <classname>DB_Table</classname> 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.
</para>

<para>
It is recommended that constructor statements be placed in a separate 
file from any of the <classname>DB_Table</classname> subclass definitions. 
Doing so makes it easier to 
<link linkend="package.database.db-table.intro-database.serialize">
serialize and unserialize</link> the 
<classname>DB_Table</classname> and <classname>DB_Table_Database</classname> 
objects, because a php file in which an instance of a 
<classname>DB_Table</classname> subclass is unserialized must have access 
to the subclass definition, but should not include the constructor statements. 
Putting each <classname>DB_Table</classname> 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 
<link linkend="package.database.db-table.intro-database.serialize">below</link>.
</para>

<para>
An alternative way to create a <classname>DB_Table</classname> object is to 
create an instance of <classname>DB_Table</classname> itself, rather than 
of a subclass of <classname>DB_Table</classname>.  In this method, one first 
instantiates a generic <classname>DB_Table</classname> 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:
<programlisting role="php">
<![CDATA[<?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';
?>
]]>
</programlisting>
This method is valid only in recent versions of the 
<classname>DB_Table</classname> package (1.5.0RC1 and greater) that contain 
the <classname>DB_Table_Database</classname> class. Earlier versions of 
<classname>DB_Table</classname> required that <classname>DB_Table</classname> 
always be extended.  The only real disadvantage of using such generic 
<classname>DB_Table</classname> objects is that it makes it impossible to 
override the methods of <classname>DB_Table</classname> to, for example, 
customize the insert or update method so as to implement business rules 
for a table. Generic <classname>DB_Table</classname> objects are used by 
the <function>fromXML</function> method, which takes an XML description of 
a database schema as a parameter, and returns a 
<classname>DB_Table_Database</classname> object in which each of the child 
tables is represented by an instance of <classname>DB_Table</classname>. 
</para>
</refsect1>

<refsect1 id="package.database.db-table.intro-database.constructor">
<title>Constructor</title>
<para>
A <classname>DB_Table_Database</classname> object is instantiated as an 
empty shell, to which tables, foreign key references, and links are then 
added. The constructor interface is
<programlisting>
<![CDATA[

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

]]>
</programlisting>
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):
<programlisting role="php">
<![CDATA[<?php
require_once 'DB/Table/Database.php'

$conn = DB::connect("mysqli://$user:$password@$host");
$db = new DB_Table_Database($conn, 'TestDB');
?>]]>
</programlisting>
where the values of $user, $password, and $host are the user name,
database password, and host machine, respectively.
</para>
</refsect1>

<refsect1 id="package.database.db-table.intro-database.model">
<title>Building a Model</title>
<para>
To construct a model of a relational database, after instantiating a 
set of <classname>DB_Table</classname> objects and a 
<classname>DB_Table_Database</classname> 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.
</para>

<refsect2 id="package.database.db-table.intro-database.model.addTable">
<title>Adding Tables</title>
<para>
After a <classname>DB_Table</classname> object is instantiated, it 
can be added to the parent database with the 
<function>DB_Table_Database::addTable</function> method. The interface 
for this method is
<programlisting>
<![CDATA[

true|PEAR_Error addTable(object &$Table)

]]>
</programlisting>
where $Table is a <classname>DB_Table</classname> object that is passed 
by reference. The method returns boolean true on normal completion, and 
a <classname>PEAR_Error</classname> object on failure.
</para><para>
The following code adds the four tables of our example database to 
the $db <classname>DB_Table_Database</classname> object:
<programlisting role="php">
<![CDATA[
<?php
$db->addTable($Person);
$db->addTable($Address);
$db->addTable($Phone);
$db->addTable($PersonPhone);
?>
]]>
</programlisting>
In this and all subseqent examples, we omit the error handling code
that should be added to production code. 
</para>
</refsect2>

<refsect2 id="package.database.db-table.intro-database.model.addRef">
<title>Adding Foreign Key References</title>
<para>
After tables have been added to a database, we can use the 
<function>addRef</function> method to add references between pairs 
of tables. 
</para><para>
Synopsis (simplified):
<programlisting>
<![CDATA[

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

]]>
</programlisting>
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 
<classname>DB_Table</classname>: 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 <classname>PEAR_Error</classname> 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 
<link linkend="package.database.db-table.intro-database.refAction.addRef">
below</link>.

For example, the command:
<programlisting role="php">
<![CDATA[
<?php
$db->addRef('Person', 'AddressID', 'Address', 'AddressID');
?>
]]>
</programlisting>
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:
<programlisting role="php">
<![CDATA[<?php
$db->addRef('Person', 'AddressID', 'Address');
?>]]>
</programlisting>
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.
</para><para>
A reference between two tables can only be added after both the referencing 
and referenced <classname>DB_Table</classname> objects have been instantiated 
and added to the parent <classname>DB_Table_Datbase</classname> instance. 
</para>
</refsect2>

<refsect2 id="package.database.db-table.intro-database.model.addLink">
<title>Adding Links</title>
<para>
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. 
</para><para>
Method <function>addLink</function> declares a table to be a 
linking or association table for two others.
</para><para>
Synopsis:
<programlisting>
<![CDATA[

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

]]>
</programlisting>
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 <function>PEAR_Error</function> if an error 
is detected. 
</para>
<para>
For example, the command:
<programlisting role="php">
<![CDATA[
<?php
$db->addLink('Person', 'Phone, 'PersonPhone')
?>
]]>
</programlisting>
declares PersonPhone to be a linking table that links tables Person
and Phone.
</para><para>
The <function>addLink</function> 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 <function>autoJoin</function> 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. 
</para><para>
The command
<programlisting role="php">
<![CDATA[<?php
$db->addAllLinks()
?>
]]>
</programlisting>
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 
<link linkend="package.database.db-table.intro-database.model.delete">
<function>deleteLink</function></link> method to delete those that 
are not desired. 
</para>
<para>
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. 
</para>
</refsect2>

<refsect2 id="package.database.db-table.intro-database.model.example">
<title>Example - Putting it Together</title>
<para>
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 
<classname>DB_Table</classname> 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 <classname>DB_Table_Database</classname> object. This file 
structure is used by the <classname>DB_Table_Generator</classname> 
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:
</para>

<example>
<title>Database.php File</title>
<programlisting role="php">
<![CDATA[
<?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();

?>
]]>
</programlisting>
</example>

<para>
This example file is very similar to the skeleton file that would 
be created by <classname>DB_Table_Generator</classname> 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 (<emphasis>e.g.</emphasis>,
the lines that define the database DSN). In this example, the call
to <function>addAllLinks</function> 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.
</para>
</refsect2>

<refsect2 id="package.database.db-table.intro-database.model.delete">
<title>Deleting Tables, References, and Links</title>
<para>
The 
<function>deleteTable</function>, 
<function>deleteRef</function>, and 
<function>deleteLinks</function>
methods can be used to delete 
tables, and foreign key references, and linking table declarations, 
respectively, from the <classname>DB_Table_Database</classname> model.
</para>

<para id="package.database.db-table.intro-database.model.delete.table">
<function>deleteTable</function> - deletes a table from the database model
</para>

<para>
Synopsis:
<programlisting>
<![CDATA[

void deleteTable(string $table)

]]>
</programlisting>
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.
</para>

<para id="package.database.db-table.intro-database.model.delete.ref">
<function>deleteRef</function> - deletes a reference from the database model
</para>
<para>
Synopsis:
<programlisting>
<![CDATA[

void deleteRef(string $ftable, string $rtable)

]]>
</programlisting>
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.
</para>
<para id="package.database.db-table.intro-database.model.delete.Link">
<function>deleteLink</function> - deletes a linking table declaration 
</para>
<para>
Synopsis:
<programlisting>
<![CDATA[

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

]]>
</programlisting>
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 <function>deleteLinks</function> method may be used 
after the <function>addAllLinks</function> to prune the resulting set 
of linking table declarations.
</para>
</refsect2>
</refsect1>

<refsect1 id="package.database.db-table.intro-database.refAction">
<title>On Delete and On Update Actions</title>
<para>
<classname>DB_Table_Database</classname> 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).  <classname>DB_Table_Database</classname> 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 
<link linkend="package.database.db-table.intro-database.FKeyCheck">below</link>)
</para>
<para>
The ON DELETE and ON UPDATE actions associated with a reference 
(if any) may be declared either as additional parameters to 
<link linkend="package.database.db-table.intro-database.refAction.addRef">
<function>addRef</function></link>, or by using 
<link
linkend="package.database.db-table.intro-database.refAction.modifyRefOptions">
<function>setOnDelete</function> and <function>setOnUpdate</function></link>.
</para>

<refsect2 id="package.database.db-table.intro-database.refAction.addRef">
<title>Declaring actions in <function>addRef</function></title>
<para>
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 <function>addRef</function> method. The following 
example shows the extended form of <function>addRef</function> 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:
<programlisting role="php">
<![CDATA[<?php
$db->addRef('PersonPhone', 'PersonID', 'Person', null, 'cascade', 'restrict');
?>]]>
</programlisting>
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.
</para>
<para>
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.
</para>

<para>
The full interface of the <function>addRef</function> method is:
<programlisting>
<![CDATA[

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

]]>
</programlisting>
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
<programlisting>
'cascade' | 'restrict' | 'set null' | 'set default' 
</programlisting> 
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.)
</para>
<para>
The following example declares all of the foreign key references needed 
in our example database, with appropriate referentially triggered actions:
<programlisting role="php">
<![CDATA[<?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');
?>]]>
</programlisting>
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. 
</para>
</refsect2>

<refsect2 id="package.database.db-table.intro-database.refAction.modifyRefOptions">
<title>
  <function>setOnDelete</function> and <function>setOnUpdate</function>
</title>
<para>
The referentially triggered actions associated with a foreign key 
reference may also be changed, or turned off, with the 
<function>setOnDelete</function> and <function>setOnUpdate</function> 
methods.
</para><para>
Synopses:
<programlisting>
<![CDATA[

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

]]>
</programlisting>
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. 
</para>
<para>
For example, the following code would change the 'on_update' action 
associated with the the reference from 'PersonPhone' to 'Person' to 
a 'restrict' action:
<programlisting role="php">
<![CDATA[<?php
$db->setOnUpdate('PersonPhone', 'Person', 'restrict');
?>]]>
</programlisting>
The effect this is to prohibit updates of the primary key value in 
rows of Person that are referenced by rows of PersonPhone.
</para>
</refsect2>

<refsect2 id="package.database.db-table.intro-database.refAction.setActOn">
<title>
<function>setActOnDelete</function> and <function>setActOnUpdate</function>
</title>
<para>
PHP emulation of referentially triggered actions may be turned on or 
off for the entire database by the 
<function>setActOnDelete</function> and 
<function>setActOnUpdate</function> methods.
</para>
<para>
Synopses:
<programlisting>
<![CDATA[

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

]]>
</programlisting>
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 
<function>DB_Table_Database::delete</function> and 
<function>DB_Table_Database::update</function> methods.
</para>
</refsect2>
</refsect1>

<refsect1 id="package.database.db-table.intro-database.FKeyCheck">
<title>Foreign Key Validation</title>
<para>
By default, <classname>DB_Table_Database</classname> 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 <function>insert</function> and <function>update</function>
methods of <classname>DB_Table_Database</classname> 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. 
</para>
<para id="package.database.db-table.intro-database.FKeyCheck.setCheckFKey">
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 
<function>setCheckFKey</function> method. The interface of this method is:
<programlisting>
<![CDATA[

void setCheckFKey(bool $flag)

]]>
</programlisting>
Passing a true value of $flag turns on checking of foreign keys (the
default), while a false value turns checking off. 
</para>
</refsect1>

<refsect1 id="package.database.db-table.intro-database.select">
<title>Data Selection</title>
<para>
<classname>DB_Table_Database</classname> provides an object-oriented 
interface for SQL select statements that is almost identical to that of 
<classname>DB_Table</classname>.
</para>

<refsect2 id="package.database.db-table.intro-database.select.query">
<title>Query Arrays</title>
<para>
As in <classname>DB_Table</classname>, queries are represented in 
<classname>DB_Table_Database</classname> 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 
<programlisting>
<![CDATA[
<?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' );
?>]]>
</programlisting>
The 
<link linkend="package.database.db-table.intro-database.util.buildSQL">
<function>buildSQL</function></link> method accepts such a query array as 
a parameter and returns the corresponding SQL command string. For example
<programlisting role="php">
<![CDATA[
<?php
echo $db->buildSQL($oak);
?>]]>
</programlisting>
yields the output
<programlisting>
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
</programlisting>
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.
</para><para>
As in <classname>DB_Table</classname>, such query arrays can be stored 
in the public $sql property array:
<programlisting role="php">
<![CDATA[
<?php
$db->sql['oak'] = $oak
?>]]>
</programlisting>
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.
</para>
</refsect2>

<refsect2 id="package.database.db-table.intro-database.select.methods">
<title>Select* Methods: 
  <function>select</function>, <function>selectResult</function>, and
  <function>selectCount</function>
</title>
<para>
The <function>select*</function> methods are inherited by both the 
<classname>DB_Table_Database</classname>  and <classname>DB_Table</classname>
classes from the <classname>DB_Table_Base</classname> 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
<programlisting role="php">
<![CDATA[<?php
$result = $db->select('oak')
?>]]>
</programlisting>
or the corresponding array value as a parameter, as in
<programlisting role="php">
<![CDATA[<?php
$result = $db->select($oak)
?>]]>
</programlisting>
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 $fetch_mode property of 
the <classname>DB_Table_Database</classname> object or (if this is null) 
the fetchmode of the underlying DB or MDB2 object. 
</para> 

<para>
The common interface of three select* methods
<function>select</function>, 
<function>selectCount</function>, and 
<function>selectResult</function> is:
<programlisting>
<![CDATA[

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

]]>
</programlisting>
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. 
</para>
</refsect2>

<refsect2 id="package.database.db-table.intro-database.select.autoJoin">
<title><function>autoJoin</function></title>
<para>
<function>autoJoin</function> - accepts an array paramater 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.
</para>
<para>
Synopsis:
<programlisting role="php">
<![CDATA[

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

]]>
</programlisting>
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. 
</para>
<para>
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. 
<programlisting role="php">
<![CDATA[
<?php
$cols = array('FirstName', 'LastName', 'PhoneNumber', 'Building', 'Street', 'City')
$report = $db->autoJoin($cols, "Phone.PhoneType = 'HOME'");
$result = $db->select($report);
?>
]]>
</programlisting>
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 
<link linkend="package.database.db-table.intro-database.util.validCol">
<function>validCol</function></link> 
method to validate and disambiguate all qualified and unqualified 
column names.
</para>
<para>
The SQL command corresponding to such a query array may be obtained
using 
<link linkend="package.database.db-table.intro-database.util.buildSQL">
<function>buildSQL</function></link>. In this example,the command
<programlisting role="php">
<![CDATA[
<?php
echo $db->buildSQL($report);
?>
]]>
</programlisting>
yields
<programlisting>
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
</programlisting>
If <function>autoJoin</function> 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.
</para><para>
In the following example, the $cols parameter is null, but the names 
of the tables to be joined are specified in the $tables parameter:
<programlisting role="php">
<![CDATA[
<?php
$tables = array('Person', 'Address', 'Phone');
$report = $db->autoJoin(null,$tables);
$result = $db->select($report);
?>
]]>
</programlisting>
The corresponding SQL command is
<programlisting>
SELECT *
FROM Person, Phone, Address, PersonPhone
WHERE PersonPhone.PhoneID = Phone.PhoneID
  AND PersonPhone.PersonID = Person.PersonID
  AND Person.AddressID = Address.AddressID
</programlisting>
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. 
</para>

<para>
Algorithm: 
The algorithm used by <function>autoJoin</function> 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. 
</para>
</refsect2>
</refsect1>

<refsect1 id="package.database.db-table.intro-database.util">
<title>SQL Utilities</title>

<refsect2 id="package.database.db-table.intro-database.util.quote">
<title><function>quote</function></title>
<para>
The quote method returns an SQL literal string representation of the 
parameter $value.
</para><para>
Synposis:
<programlisting>
<![CDATA[

string quote(mixed $value)

]]>
</programlisting>
The <function>DB_Table_Database::quote</function> method calls either the 
<function>DB::quoteSmart</function> or <function>MDB2::quote</function> 
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 <classname>DB_Table</classname> abstract 
data type). If $value is null, it returns the unquoted string NULL.
</para>
</refsect2>
<refsect2 id="package.database.db-table.intro-database.util.buildFilter">
<title><function>buildFilter</function></title>
<para>
<function>buildFilter</function> 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. 
</para><para>
The following example uses the buildFilter method to construct a filter 
for addresses on Pine St. in Peoria:
<programlisting role="php">
<![CDATA[
<?php
$data = array('Street' => 'Pine St', 'City' => 'Peoria');
$filter = $db->buildFilter($data);
?>
]]>
</programlisting>
Printing $filter then yields the SQL snippet:
<programlisting>
Street = 'Pine St' AND City = 'Peoria'
</programlisting>
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.
</para>
</refsect2>

<refsect2 id="package.database.db-table.intro-database.util.buildSQL">
<title><function>buildSQL</function></title>
<para>
<function>buildSQL</function> - 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 <function>select*</function> methods. 
Both <function>buildSQL</function> and the <function>select*</function> 
methods are inherited from <classname>DB_Table_Base</classname>.
</para>
<para>
Synopsis: The interface is similar to that of the <function>select*</function> 
methods:
<programlisting>
<![CDATA[

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

]]>
</programlisting>
As in the <function>select*</function> 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. 
</para>
</refsect2>

<refsect2 id="package.database.db-table.intro-database.util.validCol">
<title><function>validCol</function></title>
<para>
<function>validCol</function> - validates and (if necessary) disambiguates 
column names. 
</para>
<para>
Synopsis:
<programlisting>
<![CDATA[

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

]]>
</programlisting>
The required parameter $col is a column name. The optional $from parameter 
is a sequential array of table names.
</para>
<para>
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. 
</para>
<para>
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.
</para>
</refsect2>
</refsect1>

<refsect1 id="package.database.db-table.intro-database.modify"> 
<title>Data Modification: <function>insert</function>, 
       <function>update</function>, and <function>delete</function> 
</title>
<para>
The <function>insert</function>, <function>delete</function>, and 
<function>update</function> methods of <classname>DB_Table_Database</classname> 
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 
<classname>DB_Table_Database</classname> 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. 
</para>

<para>
Synopses:
<programlisting>
<![CDATA[

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

]]>
</programlisting>
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.
</para>

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

<para>
The <classname>DB_Table_Database</classname> data 
<function>insert</function> and <function>update</function> 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 <classname>DB_Table</classname> will take 
identical actions if the <classname>DB_Table</classname> has been added to 
a parent <classname>DB_Table_Database</classname> object (i.e., if it 
contains a reference to a parent object), and if these actions are enabled 
in the parent <classname>DB_Table_Database</classname> 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.  
</para>

<para>
The <function>insert</function> and <function>update</function> methods return 
a <classname>PEAR_Error</classname> object if foreign key validation fails, or if 
an error occurs during any database command. A <classname>PEAR_Error</classname> 
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.
</para>

</refsect1>

<refsect1 id="package.database.db-table.intro-database.serialize">
<title>PHP Serialization</title>
<para>
One way to maintain the state of <classname>DB_Table_Database</classname> 
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 
<classname>DB_Table_Database</classname> 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:
<programlisting role="php">
<![CDATA[
<?php
$db_serial = serialize($db);
?>
]]>
</programlisting>
The following two commands are necessary to unserialize and restore 
the state of a <classname>DB_Table_Database</classname> object:
<programlisting role="php">
<![CDATA[
<?php
$db = unserialize($db_serial);
$db->setDBconnection($DB_object);
?>
]]>
</programlisting>
where $DB_object is a DB or MDB2 connection object. 
The setDBconnection method sets the same database connection for the
parent <classname>DB_Table_Database</classname> object and all of 
the child <classname>DB_Table</classname> objects. 
</para>
<para>
When a <classname>DB_Table_Database</classname> object is unserialized, 
each child <classname>DB_Table</classname> 
object is unserialized in turn by the 
<function>DB_Table_Database::__wakeup</function> 
method.  If the <classname>DB_Table</classname> objects are instances 
of subclasses of <classname>DB_Table</classname>, 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.  
</para>
<para>
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 
<classname>DB_Table</classname> named "classname" is found to not exist 
in memory when needed during unserialization, the 
<function>__wakeup</function> method tries to 
include a file named "classname.php" in this directory. 
</para><para>
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. 
</para>
<refsect2 id="package.database.db-table.intro-database.serialize.setTableSubclassPath">
<title><function>setTableSubclassPath</function></title>
<para>
<function>setTableSubclassPath</function> - sets the path to the default 
directory for <classname>DB_Table</classname> subclass definitions.
</para> <para>
Synopsis:
<programlisting>
<![CDATA[

void setTableSubclassPath(string $path)

]]>
</programlisting>
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.
</para>
</refsect2>

</refsect1>

<refsect1 id="package.database.db-table.intro-database.xml">
<title>XML Serialization</title>
<para>
The <function>toXML</function> and <function>fromXML</function> 
methods may be used to serialize a database schema to, and unserialize 
it from, an XML string, respectively. The <function>fromXML</function> 
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). 
</para><para>
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. 
</para><para>
The <function>toXML</function> method returns an XML string for the 
entire database,
including all of its tables and foreign key references, like so:
<programlisting role="php">
<![CDATA[
<?php
$xml_string = $db->toXML();
?>
]]>
</programlisting>
The <function>DB_Table_Database::fromXML</function> method is a static method 
that takes an MDB2 XML database schema string as its only parameter and returns 
a <classname>DB_Table_Database</classname> object containing all the tables 
and references in the database. The following pair of commands is necessary 
to create a <classname>DB_Table_Database</classname> object and connect it to 
a RDBMS
<programlisting role="php">
<![CDATA[<?php
$db = DB_Table_Database::fromXML($xml_string);
$db->setDBconnection($DB_object);
?>]]>
</programlisting> 
Here, as for unserialization, the <function>setDBconnection</function> 
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 <classname>DB_Table_Database</classname> object that 
is returned by <function>fromXML</function> are all instances of 
<classname>DB_Table</classname> itself, rather than of custom subclasses 
of <classname>DB_Table</classname>.  <function>fromXML</function> returns 
a <classname>PEAR_Error</classname> if the XML string cannot be parsed, 
if an error is thrown during instantiation of either the 
<classname>DB_Table_Database</classname> object or any of the child 
<classname>DB_Table</classname> objects, or if called with a PHP 4 
interpreter (it requires PHP 5).  
</para>
</refsect1>

<refsect1 id="package.database.db-table.intro-database.DBTableProps">
<title>Setting <classname>DB_Table</classname> properties</title>
<para>
Several methods of <classname>DB_Table_Database</classname> are used 
to set a common value of a <classname>DB_Table</classname> 
property for every child table in the database. These
methods, which have the same names and interfaces as the 
corresponding <classname>DB_Table</classname> methods, are:
<programlisting>
<![CDATA[

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

]]>
</programlisting>
Each has a boolean argument that turns on (true) or off (false) one 
of the features of <classname>DB_Table</classname>. All of the relevant 
features affect data insertion and updating, and are implemented within 
the <classname>DB_Table</classname>
<function>insert</function> and <function>update</function> methods.  
The <classname>DB_Table_Database</classname> <function>insert</function> 
and <function>update</function> methods simply call the corresponding
<classname>DB_Table</classname> methods, so changes in these properties 
also change the behavior of the <classname>DB_Table_Database</classname> 
methods. 
</para>
<para>
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.
</para>
</refsect1>

<refsect1 id="package.database.db-table.intro-database.get">
<title>Get* Methods</title>
<para>
Most of the properties of <classname>DB_Table_Database</classname> 
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.
</para>
</refsect1>

</refentry>

<!-- Keep this comment at the end of the file
Local variables:
mode: sgml
sgml-omittag:t
sgml-shorttag:t
sgml-minimize-attributes:nil
sgml-always-quote-attributes:t
sgml-indent-step:1
sgml-indent-data:t
sgml-parent-document:nil
sgml-default-dtd-file:"../../../../manual.ced"
sgml-exposed-tags:nil
sgml-local-catalogs:nil
sgml-local-ecat-files:nil
End:
vim600: syn=xml fen fdm=syntax fdl=2 si
vim: et tw=78 syn=sgml
vi: ts=1 sw=1
-->