DB_Table Class Tutorial
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:
- Column and index definitions embedded in the object properties, using portable data types.
- Creation or verification of the table from the declared schema.
- Simplified API for SELECT, INSERT, UPDATE, and DELETE commands.
- Array syntax for SELECT commands, and stored baseline queries.
- Data type validation for inserted/updated column values
- Auto-increment emulation
- Automated creation of HTML_QuickForm elements from the column definitions.
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:
- Programmatic table creation: Write a DB_Table subclass definition for each table, and use an instance of that subclass to create the actual database table.
- Reflection and code generation: Use the DB_Table_Generator class to automatically generate skeleton DB_Table subclass definitions, and related glue code, for an existing database.
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:
- id -- a sequential integer that is unique for every row (required; i.e., no nulls allowed)
- fname -- a string of up to 32 characters, the first name of the visitor
- lname -- a string of up to 64 characters, the last name of the visitor
- email -- a string of up to 255 characters, the visitor's email address (required; i.e., no nulls allowed)
- signdate -- a date for when the visitor signed the guestbook (required; i.e., no nulls allowed)
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:
- boolean false to not attempt creation (default)
- 'drop' to drop any existing table with the same name and re-create it
- 'safe' to create the table if no such table exist, and do nothing if it does.
- 'verify' to check whether the table exists, verify the schema. It checks whether all the columns exist, whether the columns have the right type, and whether the indexes exist and have the right type
- 'alter' does the same as 'safe' if the table does not exist; if it does exist, the schema is verified, and the table is altered if needed.
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:
- 'select' - the SELECT clause (defaults to '*')
- 'from' - the table(s) to select from. (Defaults to the name of this table, $this->table)
- 'join' - any join clauses that should be added to the FROM clause.
- 'where' - the WHERE clause
- 'group' - the GROUP BY clause
- 'having' - the HAVING clause
- 'order' - the ORDER BY clause
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:
- 'get' - determines how the select() method will return results. See below for a list of allowed values. (Defaults to 'all').
- 'fetchmode' - determines how rows of a result set will be returned by select() if 'get' is 'all' or not set.
- 'fetchmode_object_class' - The name of the class of the objects that should be used to encapsulate rows if rows are returned as objects.
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:
- 'all' - return all rows of a return set as a sequential array of rows (this is the default). Each row can be returned as either an associative array in which keys are column names (the default) or a sequential array.
- 'assoc' - returns an associative array in which the key is the first column of the return set and the value is the second column.
- 'col' - return only the first column as a sequential array
- 'row' - return only the first row as an associative array in which the keys are column names and the values are the column values.
- 'one' - return only the value of the first column in the first row.
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:
- One that returns a list of guestbook rows ordered from most-recent to least-recent, showing the full name (first+last) and the date signed.
- One that returns a single guestbook row, showing all fields.
- One that returns a list of all unique email + fullname rows, ordered by last name and first name.
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
| 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. |