PEAR is archived and read-only

This mirror preserves historical PEAR package releases and metadata so existing references remain available.

Home » Database » DB_Table » Manual

An object-oriented interface to a database

Introduction

Introduction – Overview of the DB_Table package.

Description

The DB_Table package provides an object oriented interface to a database. It contains three main classes:

The package grew around the DB_Table class, which was written by Paul M. Jones. The Database and Generator class were added in releases 1.5.0RC1 and 1.5.0RC2, respectively. These manual pages document release 1.5.0.

The properties of the core DB_Table class contain the schema for a table, defined using portable data types. The class methods provides a convenient API for constructing and submitting INSERT, UPDATE, DELETE, and SELECT SQL commands, and for creation of a RDBMS database table from the defined schema. Simple data type validation is provided for data to be inserted or updated. The class provides methods to automatically generate HTML_QuickForm input forms that match the column definitions.

Each instance of DB_Table_Database contains a model of relationships between tables in a database, in which each table is represented by an instance of DB_Table. The autoJoin() method can automatically construct join conditions for queries that join any number of tables. The class also provides optional PHP validation of foreign key validity, and optional PHP emulation of actions triggered on delete or update of referenced rows, such as cascading deletes.

The DB_Table_Generator class may be used to automatically generate the PHP code necessary to use the DB_Table package as an interface to an existing database. It is used (if at all) only during set-up of the interface for a database.

Each of these three classes is discussed in a separate tutorial in the following manual pages.

DB_Table Class Tutorial

DB_Table Class Tutorial – Interface to a single table

Description

This tutorial uses an extended example to introduce the use of the DB_Table class as an interface to a single database table. This class provides:

This class tutorial contains two manual pages: This page documents all of the features of DB_Table except those involving HTML_Quick form generation, which are discussed in the next page. The tutorial is based closely upon the original external documentation for DB_Table, by Paul M. Jones, which is still available here. The original documentation includes some examples of how to customize a DB_Table subclass definition that are not included here.

The DB_Table and DB_Table_Database classes both extend an abstract base class named DB_Table_Base. Methods and properties that are inherited from DB_Table_Base are thus available via either a DB_Table or a DB_Table_Database object, with the same interface. These shared methods and properties will be identified as such in these manual pages.

Extending DB_Table

You generally do not usually create instances of DB_Table directly. Instead, for each table in a database, you define a a subclass of DB_Table, and instantiate one object of that subclass. The table schema is embedded in the properties of that object: column definitions are declared in the $col property array and indices in the $idx property array. Normally, the values of these property arrays are defined as part of the subclass definition, which thus serves as a record of the table schema.

One advantage of any database gateway that associates a class with each RDBMS table is that the subclass definition provides a natural place to specify properties and behaviors that are specific to that table. Custom behaviors and validations may be implemented by overriding the class methods, or by defining new methods. DB_Table also allows commonly used or baseline SELECT queries to be stored in the $sql property array.

The package provides two ways to create both a RDBMS table and a corresponding DB_Table subclass, without writing redundant specifications:

In this tutorial, we demonstrate the former method, which requires us to write subclass definitions manually. A discussion of the latter method is given in the DB_Table_Generator class tutorial.

Defining Columns

The $col property is an associative array in which each key is a column name, and each value is an associative array containing a column definition. The value of the required 'type' element of a column definition must be the name of one of the DB_Table data types, e.g., 'integer', 'decimal', 'boolean', etc. The 'char' and 'varchar' string types, and the 'decimal' numerical type, all require a 'size' element, for which the value is an integer number of characters or digits. The 'decimal' type also requires a 'scope' element, which is the number of digits after the decimal point. A 'require' element with a boolean true value is equivalent to NOT NULL in SQL, and indicates that NULL values are not allowed in that column.

As an example, let's say we're going to create a GuestBook table. This table will store the first and last name of visitors, their email address, and the date and time they signed the guestbook. In addition, we're going to want a unique ID for each row. The columns in our table will be:

These column definitions must be declared in the $col property array. Shown below is the required declaration of $col for a subclass of DB_Table, named GuestBook_Table, that is associated with a database table named GuestBook:

GuestBook column definitions

<?php

class GuestBook_Table extends DB_Table
{

    var $col = array(
             
        // unique row ID
        'id' => array(
            'type'    => 'integer',
            'require' => true
        ),
                                                                     
        // first name
        'fname' => array( 
            'type' => 'varchar', 
            'size' => 32
        ),

        // last name 
        'lname' => array( 
            'type' => 'varchar', 
            'size' => 64
        ),

        // email address 
        'email' => array( 
            'type' => 'varchar', 
            'size' => 128, 
            'require' => true
        ),

        // date signed 
        'signdate' => array( 
            'type' => 'date', 
            'require' => true)
     );
?>

Defining Indices

The $idx property array is used to declare indices. Each index can be declared to be of type 'primary', 'unique', or 'normal'. Primary and unique indices both define constraints that require that each key value be unique within the table. Normal indices are introduced purely for efficiency. Both single- and multi-column indices may be defined.

In our GuestBook example, we want two single column indices: First, we will define a primary index for the "id" identifier column. Second, for purposes of the example, we'll define a normal index on the "signdate" column, because we expect to search for visitors by date and time. The required $idx property declaration is given below:

Index Definitions for GuestBook

<?php

class GuestBook_Table extends DB_Table
{
    
    // Column definitions - see above 
    // var $col = array(...);
       
    // Index definitions             
    var $idx = array( 
    
        'id' => array( 
            'type' => 'primary', 
            'cols' => 'id'
        ), 
        
        'signdate' => array( 
            'type' => 'normal', 
            'cols' => 'signdate'
        )
    ); 
} 
?>

The key of each element in the $idx array is an index name, and the value is generally an array containing a definition for the index. An index definition array must contain a 'type' element, for which the value must be 'primary', 'unique' or 'normal', and 'cols' element. The value of 'cols' element must be either the name of a single column, for a single-column array, or a sequential array of column names, for a multi-column index. There is also an alternative shorthand syntax for single-column indices, which is discussed below.

As an example of a multi-column index, here is the definition for a multi-column index named "namefl" on the fname and lname columns.

Defining A Multi-Column Index

<?php

class GuestBook_Table extends DB_Table
{
    // Column definitions
    // var $col = array( ... )

    // Index definitions
    var $idx = array(

            // above single-column indices, plus

            'namefl' = array(
                'type' => 'normal',
                'cols' => array('fname', 'lname')
            )
        );
}
?>

There is also a shorthand way to declare a single-column index: If you want single-column index for which the index name is the same as the column name, use the column name as the key of $idx, and the index type string (rather than an index definition array) as the value.

Shorthand for Single-Column Indices:

<?php

class GuestBook_Table extends DB_Table
{
    
    // Column definitions 
    // var $col = array(...);
       
    // Index definitions             
    var $idx = array( 
   
        // primary index called 'id' based on the 'id' column 
        'id' => 'primary',
        
        // normal index called 'signdate' based on the 'signdate' column 
        'signdate' => 'normal'

    ); 
} 
?>

Declaring an Auto-Increment Column

One integer column of each table may be declared to be an auto-increment column. This column is normally a primary key identifier. The only effect of this declaration is to change the behavior of the insert() method: If the insert() method is used to insert a row in which the value of an auto-increment column is not set or NULL, then an auto-incremented integer will be inserted automatically.

A column is declared to be auto-increment by setting the value of the $auto_inc_col property to the column name. In the following example, we declare the "id" primary key column of the GuestBook table to be auto-increment:

An Auto-Incrementing Identifier

<?php

class GuestBook_Table extends DB_Table
{
    
    // Column definitions 
    // var $col = array(...);
       
    // Index definitions             
    var $idx = array(...);

    // Auto-increment declaration
    var $auto_inc_col = 'id';

}

?>

DB_Table Constructor

Each DB_Table object wraps a DB or MDB2 database connection object. This object is passed to the DB_Table constructor as its first parameter. A single DB/MDB2 object may be shared by any number of DB_Table objects. The second parameter of the constructor is the name of the associated RDBMS table. The optional third parameter is a string $create that can be used to specify whether the table should be created by the constructor if it does not already exist, or whether its structure should be verified if it does (as discussed in more detail below).

In the example shown below, an instance of the GuestBook subclass is created, which binds to the GuestBook table. The use of a value $auto_create = 'safe' specifies that the GuestBook table should be created if a table of that name does not already exist, but not otherwise.

Constructing the GuestBook_Table Object

<?php

// Include basic classes
require_once 'MDB2.php';
require_once 'DB/Table.php';
require_once 'Guestboook_Table.php';

// create a PEAR MDB2 (or DB) object
$dsn = "phptype://username:password@localhost/database";
$conn = MDB2::connect($dsn);

// set up for the GuestBook and create it
$table  = 'GuestBook';
$create = 'safe';
$GuestBook =& new GuestBook_Table($conn, $table, $create);

// print out results
if ($GuestBook->error) {
     echo "Failure!  Try again.";
     print_r($GuestBook->error);
} else {
     echo "Success!";
     print_r($GuestBook);
}

?>

The $conn connection object may be either a DB or MDB2 object, and is passed by reference.

The allowed values of the $create argument are:

Modifying Data: Insert, Update, and Delete

The DB_Table insert(), update(), and delete() methods provide a convenient interface for inserting, updating, and deleting rows of data. A row of data to be inserted or updated is passed to the insert or update method as an associative array in which the keys are column names.

Inserting Rows

To insert a row into the GuestBook table, you use the insert() method. The only parameter is an associative array in which the keys are column names, and the values are column values to be inserted.

Inserting a Row

<?php

// [snip] create the $GuestBook object

// assign fields and values
$row = array(
    'fname'    => 'Thomas',
    'lname'    => 'Anderson',
    'signdate' => '2003-10-12',
    'email'    => 'neo@matrix.net'
);

// insert into the table and print results
$result = $GuestBook->insert($row);
if (PEAR::isError($result)) {
    // Error handling code
}

?>

By default, the insert method will automatically validate that the data you are inserting is of the expected type, and that values have been provided for all required columns other than auto-increment columns. If either of these validations fails, the method returns a PEAR Error, and does not attempt to insert a row. In the above example, no value has been provided for the required 'id' column because this is an auto-increment column, as discussed below.

Auto-Increment Columns and Sequences

DB_Table can use sequences created by the underlying DB or MDB2 layer to generate auto-increment integer identifiers. If the value of a column that has been declared to be auto-increment is not set or set to PHP NULL in the array of values that is passed to the insert method, then an auto-incremented sequence value will be generated and inserted for that column. In the above example, a sequence value is generated and inserted for the 'id' column. If an integer value is set for such a column, however, the provided value will instead be inserted.

The sequence generation features of DB or MDB2 may also be accessed explictly via the DB_Table::nextID() method. This method is simply a wrapper that calls the corresponding DB or MDB2 method internally. The following example shows how to use nextID() to explicitly generate and insert an auto-incremented value for an identifier.

Using nextID() to access a sequence

<?php

// [snip] create the $GuestBook object

// get the next ID in a sequence associated with the table
$id = $GuestBook->nextID();

// assign fields and values
$row = array(
    'id'       => $id,
    'fname'    => 'Thomas',
    'lname'    => 'Anderson',
    'signdate' => '2003-10-12',
    'email'    => 'neo@matrix.net'
);

// insert into the table and print results
$result = $GuestBook->insert($row);
if (PEAR::isError($result)) {
    // ... error handling code ...
}

?>

Because the 'id' column has been declared to be auto-increment, this code snippet is functionally equivalent to that given in the preceding example.

Updating Rows

The update() method is used to update the data in a row or a set of rows. The method takes two parameters. Its first parameter is an associative array in which keys are names of columns to be updated, and values are new data values. The second is the text of the WHERE clause of the corresponding SQL UPDATE statement, excluding the WHERE keyword.

For example to change all the rows with the last name "Smith" to "Jones":

Updating a Set of Rows

<?php

// [snip] create the $GuestBook object

$values = array(
    'lname' => 'Jones'
    );

// assign the WHERE clause
$where = "lname = 'Smith'";

// attempt the update 
$result = $GuestBook->update($values, $where);
if (PEAR::isError($result)) {
    // ... error handling code ...
}

?>

As for insertion, if you attempt to update with values that do not validate against the declared column types, then the update will fail, and the method will return a PEAR Error.

Deleting Rows

The delete() method is used to delete a row or set of rows specified by a logical condition. Its only parameter is a string containing the logical condition of the WHERE clause needed to identify which rows to delete, excluding the WHERE keyword.

For example, to delete all rows that were entered before today:

Deleting a Set of Rows

<?php

// [snip] create the $GuestBook object

// a WHERE clause
$today = date('Y-m-d'); // formatted as yyyy-mm-dd
$where = "signdate < '$today'";

// attempt the update and print the results
$result = $GuestBook->delete($where);
if (PEAR::isError($result)) {
    // ... error handling code ...
}

?>

Selecting Data

DB_Table uses an array syntax for constructing SQL SELECT statements, in which each clause of an SQL SELECT statement (e.g., SELECT, FROM, WHERE clauses) is stored in a different element. This representation makes it relatively easy to modify a query by, e.g., adding additional conditions to the WHERE clause to further limit the returned set of rows. Query arrays may be stored in the $sql property array.

Queries may be submitted to the database using either the select() method, which generally returns the result set as an array, or the selectResult() method, which returns a DB_Result or MDB2_Result_Common result set object. These three methods are all inherited from the DB_Table_Base base class, and thus are also available as methods of a DB_Table_Database object.

Query Arrays

Each SQL select statement is defined by a "query array". For most of the allowed elements of a query array, the key is a lower case version of the keyword that begins a clause in the SELECT statement (e.g., 'select', 'from', 'where', etc.), and the value is the text of the remainder of that clause, excluding the keyword. The allowed keys of a query array that define clauses of the SELECT command are:

The values of the 'select', 'from', 'where', 'group', 'having', and 'order' elements are strings that contain the text of corresponding clause of the desired SQL statement, excluding the keywords SELECT, FROM, WHERE, GROUP BY, HAVING, or ORDER, respectively. The value of the 'join' element, if present, is simply concatenated onto the end of the 'FROM' element, and should include the keywords, e.g., 'JOIN', 'INNER JOIN', or 'LEFT JOIN'.

The other allowed keys of a query array may be used to specify how the results of query should be returned by the select() method. These are:

These three elements effect only the behavior of the select() method, and have no effect upon the behavior of the selectResult() method. The allowed values of the 'get' element are:

Each allowed value of the 'get' element corresponds to the name of the get* method of DB and MDB2 that is actually called internally by DB_Table::select(): If the value of the 'get' element is 'all', the query is submitted by calling the DB/MDB2::getAll() method, using an SQL query constructed from the query array, and DB_Table::select() simply returns the return value of getAll(). Similarly, a 'get' element value of 'assoc' causes a call to getAssoc(), 'col' calls getCol(), 'one' calls getOne(), and 'row' calls getRow().

When the 'get' element is set to 'all', explicitly or by default, each row in the array returned by the DB_Table::select() method returned as an associative array in which keys are column names, as a sequential array, or as an object in which column names map to property names. The choice of data structure for each row may be controlled by the 'fetchmode' element of the query or, if this is not is set, by the $fetchmode property of the DB_Table object. Similarly, when rows are returned as objects, the name of the object class may be specified by the 'fetchmode_object_class' element of the query or, if this is not set, by the $fetchmode_object_class property of the DB_Table object.

The values of the 'fetchmode' and 'fetchmode_object_class' elements of the query or the $fetchmode and $fetchmode_object_class properties of DB_Table are used to temporarily reset the values of the 'fetchmode' and 'fetchmode_object_class' properties (for DB) or options (for MDB2) of the underlying DB or MDB2 object. These values should be set equal to the desired DB_FETCHMODE_* or MDB2_FETCHMODE_* constant appropriate for the underlying backend. The original values of the DB/MDB2 object properties or options are restored before the select() method returns.

Storing Queries: $sql property array

Commonly used queries, and "baseline" queries that are useful as a starting point for construction of more complicated ones, may be stored in the $sql public property array. The $sql property is an associative array in which keys are query names, and values are query arrays. Stored queries can be submitted by passing the name key for the query as an argument to any of the select*() methods.

The $sql property is inherited from the DB_Table_Base class. Queries may thus be stored either the $sql property of a DB_Table that provides an interface to a specific table, or in the $sql property of a DB_Table_Database object that provides an interface to the entire database. For applications that involve only one table, or queries that involve only one table, it may be convenient to use the $sql property of the DB_Table object, as in the above example, and to then use the select*() methods of that object to access these queries by name. For more complicated queries and applications, however, it may be more convenient to store all queries in the $sql property of a DB_Table_Database object, and submit them by name via the select*() methods of that object.

Defining Baseline Queries in a DB_Table Subclass Definition

For our GuestBook table, let's say we define three stored SELECT statements, which may be used as a basis of more specific queries:

In the following program snippet, we declare these queries as elements of the $sql property within the GuestBook_Table class definition.

<?php

class GuestBook_Table extends DB_Table
{
 
    // [snip] var $col = array( ... );
    // [snip] var $idx = array( ... );
         
    var $sql = array( 
    
        // multiple rows for a list 
        'list' => array( 
            'select' => "id, signdate, CONCAT(fname, ' ', lname) AS fullname", 
            'order'  => 'signdate DESC'
        ), 

        // one row for an item detail
        'item' => array( 
            'select' => 'id, fname, lname, email, signdate', 
            'get' => 'row'
        ),

        // email => fullname (unique rows only) 
        'emails' => array( 
            'select' => "DISTINCT email, CONCAT(fname, ' ', lname) AS fullname", 
            'order' => 'lname, fname', 
            'get' => 'assoc'
        )           
    );

}

?>

Submitting Queries: select*() Methods

The select() method submits a query and returns the result set as an array. The selectResult() method returns the result as a DB_Result/MDB2_Result_Common object. The selectCount() method returns an integer equal to the number of rows that would be returned by a query, without returning the actual result set.

The interface is the same for all three of these methods. In each, the first parameter, which is required, can be either a query array or a key of the $sql property array, for which the corresponding value is a stored query array. The remaining parameters, which are all optional, may be used to modify the query before submission to the database. The second parameter, $filter, is a string that contains an SQL logical expression that will be ANDed with the WHERE clause of the query before the SELECT command is submitted. The third parameter, $order, if present, is used to construct the 'ORDER BY' clause, and may be used to override the 'order' element of the query. The fourth and fifth parameters, $start and $count, are integers that may be used to specify the first row of the result set that should be returned, and the maximum number of rows to be returned.

Submitting a Stored Query

<?php

// [snip] create a DB/MDB2 object and connect to the database
// [snip] create the GuestBook_Table object $GuestBook

// Submit the stored 'list' query, which returns an array of rows
$rows = $GuestBook->select('list');

// Print the result set
print_r($rows);

?>

The next example shows how to modify a stored query with the filter, order, start and count parameters:

Using Filter, Order, and Limit Parameters

<?php

$filter = "signdate = '2003-08-14'";
$order = 'lname, fname';
$start = 7;
$count = 12;

// Return results as an array of rows
$rows = $GuestBook->select($view, $filter, $order, $start, $count);
print_r($rows);

// Return results as a DB_Result/MDB2_Result_Common object
$result = $GuestBook->selectResult($view, $filter, $order, $start, $count);
print_r($result);

?>

To have select() return rows as associative arrays, we must set either the 'fetchmode' element of a specific query array or the $fetchmode property of the DB_Table object to the DB_FETCHMODE_ASSOC or MDB2_FETCHMODE_ASSOC constant values, as appropriate:

Setting Fetchmode to Return Rows as Associative Arrays

<?php

// [snip] create the $GuestBook GuestBook_Table object

// set the fetch mode to "associative"
$GuestBook->fetchmode = DB_FETCHMODE_ASSOC;

// now all rows arrays will be returned by DB_Table::select() as associative
// arrays with the column names as the array keys
?>

To have select() return rows as objects, with properties that correspond to column names, we must set either the 'fetchmode' element of the query array or the $fetchmode property of the DB_Table object to the DB_FETCHMODE_OBJECT or MDB2_FETCHMODE_OBJECT constant. If no user-defined class is specified, all rows are returned as instances of stdClass, as in the following example:

Returning Rows as stdClass Objects

<?php

// [snip] create the $GuestBook GuestBook_Table object

// set the fetch mode to the "object" constant for DB or MDB2
$GuestBook->fetchmode = DB_FETCHMODE_OBJECT;

// now each row in the results array will be a stdClass object,
// and the object properties will be named for the columns

// now all column arrays will come with the column names as the array keys
?>

To specify a class to be used to encapsulate rows of a return set, set the value of the 'fetchmode_object_class' element of the query array or the $fetchmode_object_class property to the name of the desired class.

Returning Rows as Objects of a User-Defined Class

<?php

// [snip] create the $GuestBook GuestBook_Table object

// set the fetch mode to "object"
$GuestBook->fetchmode = DB_FETCHMODE_OBJECT;

// set the fetched row class to "myRowClass" ...
// of course, you will need to include the "myRowClass" file
// before this.
$GuestBook->fetchmode_object_class = 'myRowClass';

// now each row in the results array will be a myRowClass object,
// and the object properties will be named for the columns

?>

Data Type Validation

DB_Table can automatically validate that the format of data to be inserted or updated is consistent with the declared types for the corresponding columns. Validation of the data type of both inserted and updated data is on by default. Validation upon insertion and updating can be turned on or off with the autoValidInsert() and autoValidUpdate() methods, respectively. Each of these methods takes a single boolean parameter, and turns validation on if its parameter is true and off if it is false.

Data type validation for a row of data to be inserted or updated is actually carried out by the validInsert() or validUpdate() method, respectively. Each of these methods takes one parameter, which is an associative array of data in which keys are column names. Each returns boolean true on success or a PEAR_Error object on failure. These methods are called internally by the insert() and update() methods, respectively, when auto-validation is enabled. Customized automatic validations may thus be implemented by overriding one or both of these methods.

Validation of the type of a single column value is carried out by the isValid() method. This method takes the value to be validated as its first parameter, and the name of the required DB_Table data type as its second parameter. This method is called internally by the two row validation methods.

Miscellaneous Utility Methods

Miscellaneous Methods
Method Description
quote() Enquotes and escapes a value in a form appropriate for inclusion in an SQL query. A simple wrapper for the DB::smartQuote() or MDB2::quote() method, which it calls internally.
recast() Takes an associative array of data (keys are column names and values are column values) and re-casts each value to the proper format for its column.
getBlankRow() Returns an associative array with column name keys and a blank value for each column. Each value will be in the proper format for the associated column type.

DB_Table Forms Tutorial

DB_Table Forms Tutorial – Creating HTML_QuickForm forms

Description

This page documents the use of the DB_Table class to create HTML_QuickForm form elements appropriate for the columns of a table, and to create entire data entry forms. DB_Table can use the column definitions to automatically create default form elements for you. Although your plain column definitions will do fine at first, you will want to customize the labels and options for your form elements. You can do so by adding 'qf_*' keys to your column definitions

Note: The underlying HTML_QuickForm package is a very powerful library with many options. While DB_Table automates away much of the complexity for simple forms, the full power of HTML_QuickForm can only be realized by combining the automatic element creation from DB_Table with custom-created HTML_QuickForm objects. DB_Table makes it easy to get started with HTML_QuickForm, but does not replace it.

Simple Form with Default Elements

Once you have defined your columns and instantiated an object, you can generate a complete HTML_QuickForm object using the getForm() method. If getForm() is called with no parameters, it will return a form with all of the $GuestBook object columns as input fields.

A default form with all columns

<?php
// [snip] create the GuestBook_Table object ($GuestBook)

// create the <classname>HTML_QuickForm</classname> object
$form =& $GuestBook->getForm(); // note the "=&" -- very important

// display the form
$form->display();
?>

Specifying Columns

To limit the fields you want to display, or to change the order, pass a sequential array of column names as the first parameter to getForm()

Specifying column names in getForm()

<?php

// [snip] create the Guesbook object ($GuestBook)

// only show the first name, last name, and email in the form
$cols = array('fname', 'lname', 'email');

// create the <classname>HTML_QuickForm</classname> object
$form =& $GuestBook->getForm($cols);

// display the form
$form->display();

?>

Add Buttons

Unfortunately, the form only has the input fields; it does not yet have "Submit" or "Reset" buttons, which are not automatically generated.

Adding "Submit" and "Reset" buttons with getForm() 

<?php

// [snip] create the $GuestBook GuestBook_Table object

// create the HMTL_QuickForm object
$form =& $GuestBook->getForm($cols);

// add a "submit" button named "op" that says "Go!"
$form->addElement('submit', 'op', 'Go!');

// add a reset button
$form->addElement('reset');

// display the form
$form->display();

?>

Simple Form with Custom Elements

The basic form is nice, but it's very generic. It doesn't have descriptive labels for the input fields, all the fields are text boxes, and so on. This section shows you how to include form element properties along with your column definitions so that your forms are customized in a somewhat consistent fashion.

In every case you will modify the $col property by adding 'qf_*' keys and values.

Element Label

When a column appears in a form, you can make sure the same label is always applied to it. Let's say we have a column called "other" defined as a 32-character string. We always want it to be labelled as "Other Information" in our forms; use 'qf_label' key like this:

Setting 'qf_label'

<?php

class GuestBook_Table extends DB_Table
{
 
    var $col = array( 
        // ...  
        'example' => array( 
            // table column definition 
            'type' => 'varchar', 
            'size' => 32, 
             
            // form element definition 
            'qf_label' => 'Other Information'
        )
    ); 
} 
?>

Element Type

By default, DB_Table will try to figure out what kind of form field input type to use for your column. Most column types translate into text fields, but there are some exceptions.

To explicitly set the HTML_QuickForm element type for a column, rather than relying on the above defaults, you add a 'qf_type' element to your column definition. You can tell DB_Table to create any of several supported HTML_QuickForm element types by setting the 'qf_type' element to one of these string values:

If you use any other element name, DB_Table will attempt to map to the proper HTML_QuickForm element (thanks to Moritz Heidkamp for the patch), but it's not guaranteed to work.

In this example, we'll make the 'example' column a text field:

Setting an element type

<?php

class GuestBook_Table extends DB_Table
{
 
    var $col = array( 
        // ...  
        'example' => array( 
            // table column definition 
            'type' => 'varchar', 
            'size' => 32,
            // form element definition 
            'qf_label' => 'Other Information',
            'qf_type'  => 'text'
        )
    );
}
?>

Element Values

If your column form element is 'checkbox', 'radio', or 'select', you can set the values of the available choices for the form element. You do so via an 'sq_vals' element in the corresponding column definition.

Setting CheckBox Values

For a checkbox, use a sequential array of two elements: the first is the value if the box is not checked, and the second is the value if it is checked.

In this example, the checkbox values are 0 if not checked,and 1 if checked; these are the values that will be stored in the column.

<?php

class GuestBook_Table extends DB_Table
{
 
    var $col = array( 
        // ...  
        'example' => array( 
            // table column definition 
            'type' => 'varchar', 
            'size' => 32,
            // form element definition 
            'qf_label' => 'Other Information',
            'qf_type'  => 'text',
            'qf_vals'  => array(0, 1)
        )
    );
}
?>

Radio and Select Values

For radio buttons and select menus, use an associative array. Each array key is the value that will be stored in the column, and each array value is the text that will be displayed to the user.

This example creates a select menu with three possible values ('a', 'b', and 'c') and the corresponding labels:

<?php

class GuestBook_Table extends DB_Table
{
 
    var $col = array( 
        // ...  
        'example' => array( 
            // table column definition 
            'type' => 'varchar', 
            'size' => 32,
            // form element definition 
            'qf_label' => 'Other Information',
            'qf_type'  => 'text',
            'qf_vals'  => array(
                'a' => 'This is the letter "a"',
                'b' => 'I choose "b"',
                'c' => 'No, "c" is always the right answer'
            )
        )
    );
}
?>

Element HTML Attributes

If you like, you can add HTML attributes to the form element using the 'qf_attrs' keys. Attributes are assigned as key-value pairs in an associative array; the keys is the attribute name, and the value is the attribute value. For example, to set the number of rows and columns for a textarea element:

Setting rows and column number for a textarea

<?php

class GuestBook_Table extends DB_Table
{
 
    var $col = array( 

        // ...  

        'example' => array( 
            // table column definition 
            'type' => 'varchar', 
            'size' => 32,

            // form element definition 
            'qf_label' => 'Other Information',
            'qf_type'  => 'textarea',
            'qf_attrs' => array(
                'rows' => 24,
                'cols' => 80
            )
        )
    );
}
?>

DB_Table automatically adds a "maxlength" attribute to text and password elements based on the column size, so that users cannot type in values longer than the column allows.

Element Rules

HTML_QuickForm allows you to add validation rules to the input form. These rules are not the same as the DB_Table automated validations; the HTML_QuickForm rules apply only to the values as they relate to the form itself, not the values related to the table proper. This is an important distinction that will become apparent as you use HTML_QuickForm.

The available rules are:

DB_Table will automatically add HTML_QuickForm rules for you in most cases.

If you want to use a specific HTML_QuickForm rule for a column when that column appears in a form, set 'qf_rules' key to the name of the rule and set the value for that rule; in most cases, the value is an error message, but in some cases the value is a sequential array. You can add as many different rules as you like, but you can add only one of each type.

QuickForm Rules

<?php

class GuestBook_Table extends DB_Table
{

    var $col = array( 
     
        // ...  
         
        'example1' => array( 
            // table column definition 
            'type' => 'integer', 
            
            // form element definition 
            'qf_label' => 'Information', 
            'qf_type' => 'text', 
            
            // a set of QuickForm rules 
            'qf_rules' => array( 
                'required' => 'This field is required.', 
                'numeric' => 'Please use only numbers, no letters.'
            )
        ), 
         
        'example2' => array( 
        
            // table column definition 
            'type' => 'varchar', 
            'size' => 10, 

            // form element definition 
            'qf_label' => 'Something:', 
            'qf_type' => 'text', 

            // a set of QuickForm rules 
            'qf_rules' => array( 
                'minlength' => array('Minimum length is 6 characters.', 6), 
                'maxlength' => array('Maximum length is 10 characters.', 10), 
                'regex' => array( 
                    'Must be only upper-case letters and underscores.', 
                    '/^[A-Z_]+$/'
                )   
            ); 
        )
    ) 
}
?>

HTML_QuickForm supports rules for groups and entire forms, but DB_Table does not automate these; you will need to add them to the form object yourself.

Setting Default Values

Sometimes you will want to populate your input form with default values, such as the current values from the database. Doing so is easy.

When you call getForm(), instead of passing a sequential array of column names, pass an associative array in which the key is the column name and the value is the column value.

Setting Default Values for Form Fields

<?php

// [snip] create the GuestBook_Table object ($GuestBook)

// Show only the first name, last name, and email in the form
// Provide default values for the form elements
$cols = array(
    'fname' => 'Thomas', 
    'lname' => 'Anderson', 
    'email' => 'neo@matrix.net'
);

// create the HTML_QuickForm object
$form =& $GuestBook->getForm($cols);

// display the form
$form->display();
?>

Element Names as Array Keys

By default, when you call getForm(), the form elements are named for their columns. For example, the column 'fname' is called 'fname' in the form.

<!-- [snip] the beginning of the form -->

<input type="text" name="fname" ... />

<!-- [snip] the end of the form -->

However, often you will want the form elements to be keys in any array instead of their own separate variables. This is particularly useful when you are going to use the values in a DB_Table insert() or update() call.

To do so, pass the name of an array as the second argument to getForm() (after the list of columns to use in the form). For example, if you want the column names to be keys in an array called 'new_row', do this:

Returning form values in an array

<?php

// [snip] create the $GuestBook object of class GuestBook_Table

// choose which columns to display in the form
$cols = array('fname', 'lname', 'email');

// create the HTML_QuickForm object, with elements
// named as part of an array called 'new_row'
$form = $GuestBook->getForm($cols, 'new_row');

// display the form
$form->display()

?>

Custom HTML_QuickForm Objects

Although the getForm() method supports all HTML_QuickForm parameters (such as the name of the form, the method, the action, and so on), you don't need to use DB_Table to create the entire form. If you like, you can create your own HTML_QuickForm object, and add DB_Table columns to it one-by-one or in groups.

DB_Table provides these methods to add automatically-defined elements to pre-existing HTML_QuickForm objects:

Here are example uses of each:

Custom HTML Element

<?php

// [snip] Create the GuestBook_Table object $GuestBook

// load the class file and create a QuickForm object
require_once 'HTML/QuickForm.php';
$form =& new HTML_QuickForm();

// add one or more elements from the GuestBook
// object to the QuickForm object, along with
// the rules for those elements
$cols = array('email', 'signdate');
$GuestBook->addFormElements($form, $cols);

// add an element group of GuestBook columns
// to the QuickForm object (does not add rules)
$cols = array('fname', 'lname');
$group =& $GuestBook->getFormGroup($cols);
$form->addGroup($group);

// get back a single form element object with a
// custom name, then add it to the form
$col = 'id';
$name = 'new_row[id]';
$element =& $GuestBook->getFormElement($col, $name);
$form->addElement($element);

?>

DB_Table_Database Class Tutorial

DB_Table_Database Class Tutorial – Interface to a relational database

Description

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

DB_Table_Database provides:

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

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

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

Example Database

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

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

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

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

<?php
require_once 'DB/Table.php'

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

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

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

}
?>

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Constructor

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



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

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

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

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

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

Building a Model

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

Adding Tables

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



true|PEAR_Error addTable(object &$Table)

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

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

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

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

Adding Foreign Key References

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

Synopsis (simplified):



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

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

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

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

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

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

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

Adding Links

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:



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

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:

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

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

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

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.

Example - Putting it Together

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

Database.php File

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

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

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

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

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

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

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

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

?>

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

Deleting Tables, References, and Links

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

deleteTable() - deletes a table from the database model

Synopsis:



void deleteTable(string $table)

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

deleteRef() - deletes a reference from the database model

Synopsis:



void deleteRef(string $ftable, string $rtable)

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

deleteLink() - deletes a linking table declaration

Synopsis:



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

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

On Delete and On Update Actions

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

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

Declaring actions in addRef()

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

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

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

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

The full interface of the addRef() method is:



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

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

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

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

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

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

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

setOnDelete() and setOnUpdate()

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

Synopses:



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

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

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

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

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

setActOnDelete() and setActOnUpdate()

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

Synopses:



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

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

Foreign Key Validation

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

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



void setCheckFKey(bool $flag)

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

Data Selection

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

Query Arrays

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


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

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

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

yields the output

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

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

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

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

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

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

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

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

or the corresponding array value as a parameter, as in

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

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

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



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

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

autoJoin()

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

Synopsis:

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

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

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

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

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

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

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

yields

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

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

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

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

The corresponding SQL command is

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

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

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

SQL Utilities

quote()

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

Synposis:



string quote(mixed $value)

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

buildFilter()

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

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

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

Printing $filter then yields the SQL snippet:

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

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

buildSQL()

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

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



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

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

validCol()

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

Synopsis:



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

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

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

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

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

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

Synopses:



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

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

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

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

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

PHP Serialization

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

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

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

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

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

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

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

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

setTableSubclassPath()

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

Synopsis:



void setTableSubclassPath(string $path)

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

XML Serialization

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

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

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

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

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

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

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

Setting DB_Table properties

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



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

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

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

Get* Methods

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

DB_Table_Generator Class Tutorial

DB_Table_Generator Class Tutorial – Code generation for an existing database

Description

The DB_Table_Generator class can generate the PHP code necessary to use the DB_Table package to interact with an existing database. It generates skeleton DB_Table subclass definitions for every table in the database, using table schemas that are obtained by querying the database. It can also generate a file containing the code required to connect to the database, and to create a parent DB_Table_Database object.

Name Conventions and File Structure

All code generated by a DB_Table_Generator object is written to a directory whose path is given by the $class_write_path property of that object. By default, this is the current directory. By default, the name of the class constructed for a table named 'thing' is Thing_Table. That is, the class name is the table name, with the first letter upper case, with an added suffix '_Table'. This suffix can be changed by setting the $class_suffix property. The name of the file containing a subclass definition is the subclass name with a PHP extension, e.g., 'Thing_Table.php'. The name of the object instantiated from that subclass is the same as the table name, with no suffix, e.g., 'thing'.

Code Generation Instructions

To generate the code for all of the tables in a database named $database, instantiate a DB or MDB2 object named $conn that connects to the database of interest, and execute the following code:

Code Generation for an Entire Database

<?php
require_once 'DB/Table/Generator.php';

// [snip] Instantiate DB or MDB2 object $conn

// Instantiate a Generator object 
$generator = new DB_Table_Generator($conn, $database);

// Choose a directory for the generated code
$generator->class_write_path = '/var/www/html/app1/db_table' ;

// Generate DB_Table subclass definition files 
$generator->generateTableClassFiles();

// Generate the 'Database.php' file
$generator->generateDatabaseFile();

?>

In the above example, '/var/www/html/app1/db_table' is the path (without a trailing directory separator) to a directory in which all of the code should be written. If this directory does not exist, it will be created (if possible). If the directory does already exist, existing files will not be overwritten. If $class_write_path is not set (i.e., if this line from the example is ommitted) all the code will be written to the current directory.

The generateTableClassFiles() method generates skeleton subclass definitions for a set of tables, with different subclass definitions in different files. If it is called with no argument, as above, it generates subclass definitions for all of the tables in the current database.

The generateDatabaseFile() method generates a file, named 'Database.php' by default, that contains all of the additional code necessary to connect to a database and to create a parent DB_Table_Database object. If the generateDatabaseFile() method is called, as in the above example, it must be called after the generateTableClassFiles(). The code in the 'Database.php' file includes (using the "require_once" command) each of the table subclass definition files, instantiates one object of each DB_Table subclass (creating one object per table), instantiates a parent DB_Table_Database object, adds all the tables to that parent, and attempts to guess foreign key relationships between tables based on the column names. This file will generally need to be edited so as to include information about the data source name (DSN), and any foreign key relationships that could not be guessed by the generator.

By default, generateTableClassFiles() and generateDatabaseFiles() generate code for all of the tables in the current database. To generate code for a specified list of tables, set the value of the public $tables property to a sequential list of table names before calling either of these methods. For example, code can be generated for three tables named 'table1', 'table2', and 'table3' as follows:

Code Generation for a Specified Set of Tables

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

// [snip] Instantiate DB or MDB2 object $conn

// Instantiate a Generator object 
$generator = new DB_Table_Generator($conn, $database);

// Choose a directory for the generated code
$generator->class_write_path = '/var/www/html/app1/db_table' ;

// Define the set of tables
$generator->tables = array('table1', 'table2', 'table3');

// Generate DB_Table subclass definition files 
$generator->generateTableClassFiles();

// Generate the 'Database.php' file
$generator->generateDatabaseFile();

?>

If the $tables property of the DB_Table_Generator object is not set to an array value before generateTableClassFiles() is called, then, by default, the generateTableClassFiles() method queries the database for an array of all table names (by calling the getTableNames() method internally), and the $table property is set equal to the resulting array of table names.

DB_Table Data Types

DB_Table Data Types – Abstract data types used in DB_Table column definitions

Description

These are the supported data types in DB_Table:

DB_Table abstracts data types for you, so your data is always stored the same way, regardless of the database backend. In some cases, particularly with date, time, and timestamp, the native database format is ignored completely and data is stored as a fixed-length character string.

DB_Table does no support binary large objects (BLOBs), but character large objects (CLOBS) are available.

Integers

There are three sizes of integer columns:

The $col definition, you need only specify the column type; no size or scope is needed.

Integer column declaration

<?php
var $col = array(
    // unique row ID
    'fieldname' => array(
        'type'    => 'integer'
    )
);                                                                 
?>

Fixed-Point Decimal Numbers

To define a fixed-point column in DB_Table, use the 'decimal' datatype, and indicate both the 'size' (width) and 'scope' (number of decimal places). For example, to define a 5-digit number that has 2 decimal places:

Decimal column declaration

<?php
var $col = array(
    // unique row ID
    'fieldname' => array(
        'type'  => 'decimal',
        'size'  => 5,
        'scope' => 2
    ),
);                                                                 
?>

For the above example, standard SQL requires that the column be able to store any value with 5 digits and 2 decimals. In this case, therefore, the range of values that can be stored in the column is from -999.99 to 999.99. DB_Table attempts to enforce this behavior regardless of the RDBMS backend behavior.

Floating-Point Numbers

To define a floating-point column in DB_Table, use the 'single' or 'double' datatype.

You need only specify the column type; no size or scope is needed.

Floating-point column declaration

<?php
var $col = array(
    // unique row ID
    'fieldname' => array(
        'type'  => 'double'
    ),
);                                                                 
?>

Boolean

A boolean value is a true/false (1 or 0) value. You need only specify the column type; no size or scope is needed.

Boolean column declaration

<?php
var $col = array(
    // unique row ID
    'fieldname' => array(
        'type'  => 'boolean'
    )
);                                                                 
?>

Boolean values are stored as fixed-point decimals of size 1, scope 0.

If the column is not required, a NULL value stored therein may be treated as a third value, which allows the boolean column to be treated as a ternary value instead of a binary value (i.e., NULL|0|1 instead of 0|1).

Strings

To define a fixed-length character string column, use the 'char' datatype, and indicate the exact size of the string.

To define a variable-length character string column, use the 'varchar' datatype, and indicate the maximum size of the string.

For example, to define a 64-character variable-length string:

Variable length string column declaration

<?php
var $col = array(
    // unique row ID
    'fieldname' => array(
        'type'  => 'varchar',
        'size'  => 64
    )
);                                                                 
?>

You must specify a 'size' element, but no scope is needed. The maximum size is 255 characters.

Date

To define an ISO-standard date column, use the 'date' datatype.

Date column declaration

<?php
var $col = array(
    // unique row ID
    'fieldname' => array(
        'type'  => 'date'
    )
);                                                                 
?>

You need only specify the column type; no size or scope is needed.

Values for 'date' are always stored as 10-character strings, in the format "yyyy-mm-dd".

Time

To define an ISO standard time with hour, minutes, and seconds, use the 'time' data type.

Time column declaration

<?php
var $col = array(
    // unique row ID
    'fieldname' => array(
        'type'  => 'time'
    )
);                                                                 
?>

You need only specify the column type; no size or scope is needed.

A 'time' value is always stored as an 8-character string, in the format "hh:ii:ss".

Timestamp

To define an ISO standard date-and-time column, use the 'timestamp' data type.

Timestamp column declaration

<?php
var $col = array(
    // unique row ID
    'fieldname' => array(
        'type'  => 'timestamp'
    )
);                                                                 
?>

You need only specify the column type; no size or scope is needed.

Values for 'timestamp' are always stored as an 19-character strings, in the format "yyyy-mm-dd hh:ii:ss" (24-hour clock).

Note: If you want to store a Unix timestamp, use the 'integer' datatype; Unix timestampes are 4-byte integers, which maps perfectly to the DB_Table 'integer' datatype.

Character Large Object (CLOB)

CLOB Column Declaration

<?php
var $col = array(
    // unique row ID
    'fieldname' => array(
        'type'  => 'clob'
    )
);                                                                 
?>

You need only specify the column type; no size or scope is needed.

Values for 'clob' are always stored as the large possible native text type (e.g., LONGTEXT) or native CLOB type.