Packages:
default
System
System.Caching
System.Collections
System.Data
System.Data.ActiveRecord
System.Data.ActiveRecord.Relations
System.Data.ActiveRecord.Scaffold
System.Data.ActiveReecord.Scaffold.InputBuilder
System.Data.Commom.Sqlite
System.Data.Common
System.Data.Common.Mssql
System.Data.Common.Mysql
System.Data.Common.Oracle
System.Data.Common.Pgsql
System.Data.Common.Sqlite
System.Data.DataGateway
System.Data.SqlMap
System.Data.SqlMap.Configuration
System.Data.SqlMap.Statements
System.Exceptions
System.I18N
System.IO
System.Security
System.Util
System.Web
System.Web.Services
System.Web.UI
System.Web.UI.ActiveControls
System.Web.UI.WebControls
System.Web.UI.WebControls.assets
System.Xml


Classes:
Keyword

Class TTableGateway

TComponent
   |
   --TTableGateway

TTableGateway class provides several find methods to get data from the database and update, insert, and delete methods.

Each method maps the input parameters into a SQL call and executes the SQL against a database connection. The TTableGateway is stateless (with respect to the data and data objects), as its role is to push data back and forth.

Example usage:

  1. //create a connection
  2. $dsn = 'pgsql:host=localhost;dbname=test';
  3. $conn = new TDbConnection($dsn, 'dbuser','dbpass');
  4.  
  5. //create a table gateway for table/view named 'address'
  6. $table = new TTableGateway('address', $conn);
  7.  
  8. //insert a new row, returns last insert id (if applicable)
  9. $id = $table->insert(array('name'=>'wei', 'phone'=>'111111'));
  10.  
  11. $record1 = $table->findByPk($id); //find inserted record
  12.  
  13. //finds all records, returns an iterator
  14. $records = $table->findAll();
  15. print_r($records->readAll());
  16.  
  17. //update the row
  18. $table->updateByPk($record1, $id);

All methods that may return more than one row of data will return an TDbDataReader iterator.

The OnCreateCommand event is raised when a command is prepared and parameter binding is completed. The parameter object is a TDataGatewayEventParameter of which the TDataGatewayEventParameter::getCommand property can be inspected to obtain the sql query to be executed.

The OnExecuteCommand event is raised when a command is executed and the result from the database was returned. The parameter object is a TDataGatewayResultEventParameter of which the TDataGatewayEventParameter::getResult property contains the data return from the database. The data returned can be changed by setting the TDataGatewayEventParameter::setResult property.

  1. $table->OnCreateCommand[] = 'log_it'; //any valid PHP callback statement
  2. $table->OnExecuteCommand[] = array($obj, 'method_name'); // calls 'method_name' on $obj
  3.  
  4. function log_it($sender, $param)
  5. {
  6. var_dump($param); //TDataGatewayEventParameter object.
  7. }

Since: 3.1
Author: Wei Zhuo <weizho[at]gmail[dot]com>

Constructor Summary
public
__construct Array
Creates a new generic table gateway for a given table or view name and a database connection.

Method Summary
int
count ( string|TSqlCriteria $criteria, mixed $parameters)
Find the number of records.
integer
deleteAll ( string $criteria, array $parameters)
Delete records from the table with condition given by $where and binding values specified by $parameter argument.
void
deleteAllByPks ( mixed $keys)
Alias for deleteByPk()
int
deleteByPk ( mixed $keys)
Delete records by primary key. Usage:
array
find ( string|TSqlCriteria $criteria, mixed $parameters)
Find one single record that matches the criteria.
TDbDataReader
findAll ( string|TSqlCriteria $criteria, mixed $parameters)
Accepts same parameters as find(), but returns TDbDataReader instead.
TDbDataReader
findAllByPks ( mixed $keys)
Similar to findByPk(), but returns TDbDataReader instead.
TDbDataReader
findAllBySql ( string $sql, array $parameters)
Execute arbituary sql command with binding parameters.
array
findByPk ( mixed $keys)
Find one record using only the primary key or composite primary keys. Usage:
array
findBySql ( string $sql, array $parameters)
Execute arbituary sql command with binding parameters.
protected  TDataGatewayCommand
protected  TSqlCriteria
getCriteria ( string|TSqlCriteria $criteria, mixed $parameters, array $args)
Create a new TSqlCriteria object from a string $criteria. The $args are additional parameters and are used in place of the $parameters if $parameters is not an array and $args is an arrary.
TDbConnection
mixed
void
void
protected  void
mixed
insert ( array $data)
Inserts a new record into the table. Each array key must correspond to a column name in the table unless a null value is permitted.
void
Raised when a command is prepared and parameter binding is completed.
void
Raised when a command is executed and the result from the database was returned.
protected  void
setTableInfo ( TDbTableInfo $tableInfo)
protected  void
setTableName ( string $tableName)
Sets up the command builder for the given table.
integer
update ( array $data, string $criteria, array $parameters)
Updates the table with new name-value pair $data. Each array key must correspond to a column name in the table. The update condition is specified by the $where argument and additional binding values can be specified using the $parameter argument.
mixed
__call ( mixed $method, mixed $args)
Dynamic find method using parts of method name as search criteria.
Methods Inherited From TComponent
TComponent::addParsedObject(), TComponent::attachEventHandler(), TComponent::canGetProperty(), TComponent::canSetProperty(), TComponent::createdOnTemplate(), TComponent::detachEventHandler(), TComponent::evaluateExpression(), TComponent::evaluateStatements(), TComponent::getEventHandlers(), TComponent::getSubProperty(), TComponent::hasEvent(), TComponent::hasEventHandler(), TComponent::hasProperty(), TComponent::raiseEvent(), TComponent::setSubProperty(), TComponent::__get(), TComponent::__set()

Constructor Details

__construct

public __construct Array

Creates a new generic table gateway for a given table or view name and a database connection.


Method Details

count

public int count (string|TSqlCriteria $criteria , mixed $parameters )

Find the number of records.

Input
string|TSqlCriteria$criteriaSQL condition or criteria object.
mixed$parametersparameter values.
Output
int number of records.
Exception

deleteAll

public integer deleteAll (string $criteria , array $parameters )

Delete records from the table with condition given by $where and binding values specified by $parameter argument.

This method uses additional arguments as $parameters. E.g.

  1. $table->delete('age > ? AND location = ?', $age, $location);

Input
string$criteriadelete condition.
array$parameterscondition parameters.
Output
integer number of records deleted.
Exception

deleteAllByPks

public void deleteAllByPks (mixed $keys )

Alias for deleteByPk()

Input
mixed$keys
Output
Exception

deleteByPk

public int deleteByPk (mixed $keys )

Delete records by primary key. Usage:

  1. $table->deleteByPk($primaryKey); //delete 1 record
  2. $table->deleteByPk($key1,$key2,...); //delete multiple records
  3. $table->deleteByPk(array($key1,$key2,...)); //delete multiple records

For composite primary keys (determined from the table definitions):

  1. $table->deleteByPk(array($key1,$key2)); //delete 1 record
  2.  
  3. //delete multiple records
  4. $table->deleteByPk(array($key1,$key2), array($key3,$key4),...);
  5.  
  6. //delete multiple records
  7. $table->deleteByPk(array( array($key1,$key2), array($key3,$key4), .. ));

Input
mixed$keysprimary key values.
Output
int number of records deleted.
Exception

find

public array find (string|TSqlCriteria $criteria , mixed $parameters )

Find one single record that matches the criteria.

Usage:

  1. $table->find('username = :name AND password = :pass',
  2. array(':name'=>$name, ':pass'=>$pass));
  3. $table->find('username = ? AND password = ?', array($name, $pass));
  4. $table->find('username = ? AND password = ?', $name, $pass);
  5. //$criteria is of TSqlCriteria
  6. $table->find($criteria); //the 2nd parameter for find() is ignored.

Input
string|TSqlCriteria$criteriaSQL condition or criteria object.
mixed$parametersparameter values.
Output
array matching record object.
Exception

findAll

public TDbDataReader findAll (string|TSqlCriteria $criteria , mixed $parameters )

Accepts same parameters as find(), but returns TDbDataReader instead.

Input
string|TSqlCriteria$criteriaSQL condition or criteria object.
mixed$parametersparameter values.
Output
TDbDataReader matching records.
Exception

findAllByPks

public TDbDataReader findAllByPks (mixed $keys )

Similar to findByPk(), but returns TDbDataReader instead.

For scalar primary keys:

  1. $table->findAllByPk($key1, $key2, ...);
  2. $table->findAllByPk(array($key1, $key2, ...));

For composite keys:

  1. $table->findAllByPk(array($key1, $key2), array($key3, $key4), ...);
  2. $table->findAllByPk(array(array($key1, $key2), array($key3, $key4), ...));

Input
mixed$keysprimary keys
Output
TDbDataReader data reader.
Exception

findAllBySql

public TDbDataReader findAllBySql (string $sql , array $parameters )

Execute arbituary sql command with binding parameters.

Input
string$sqlSQL query string.
array$parametersbinding parameters, positional or named.
Output
TDbDataReader query results.
Exception

findByPk

public array findByPk (mixed $keys )

Find one record using only the primary key or composite primary keys. Usage:

  1. $table->findByPk($primaryKey);
  2. $table->findByPk($key1, $key2, ...);
  3. $table->findByPk(array($key1,$key2,...));

Input
mixed$keysprimary keys
Output
array matching record.
Exception

findBySql

public array findBySql (string $sql , array $parameters )

Execute arbituary sql command with binding parameters.

Input
string$sqlSQL query string.
array$parametersbinding parameters, positional or named.
Output
array query results.
Exception

getCommand

protected TDataGatewayCommand getCommand ()

Output
TDataGatewayCommand command builder and executor.
Exception

getCriteria

protected TSqlCriteria getCriteria (string|TSqlCriteria $criteria , mixed $parameters , array $args )

Create a new TSqlCriteria object from a string $criteria. The $args are additional parameters and are used in place of the $parameters if $parameters is not an array and $args is an arrary.

Input
string|TSqlCriteria$criteriasql criteria
mixed$parametersparameters passed by the user.
array$argsadditional parameters obtained from function_get_args().
Output
TSqlCriteria criteria object.
Exception

getDbConnection

public TDbConnection getDbConnection ()

Output
TDbConnection database connection.
Exception

getLastInsertId

public mixed getLastInsertId ()

Output
mixed last insert id, null if none is found.
Exception

getTableInfo

public void getTableInfo ()

Output
Exception

getTableName

public void getTableName ()

Output
Exception

initCommandBuilder

protected void initCommandBuilder (TDbCommandBuilder $builder )

Input
TDbCommandBuilder$builderdatabase specific command builder.
Output
Exception

insert

public mixed insert (array $data )

Inserts a new record into the table. Each array key must correspond to a column name in the table unless a null value is permitted.

Input
array$datanew record data.
Output
mixed last insert id if one column contains a serial or sequence, otherwise true if command executes successfully and affected 1 or more rows.
Exception

onCreateCommand

public void onCreateCommand (TDataGatewayCommand $sender , TDataGatewayEventParameter $param )

Raised when a command is prepared and parameter binding is completed.

The parameter object is TDataGatewayEventParameter of which the TDataGatewayEventParameter::getCommand property can be inspected to obtain the sql query to be executed.

Input
TDataGatewayCommand$senderoriginator $sender
TDataGatewayEventParameter$param
Output
Exception

onExecuteCommand

public void onExecuteCommand (TDataGatewayCommand $sender , TDataGatewayResultEventParameter $param )

Raised when a command is executed and the result from the database was returned.

The parameter object is TDataGatewayResultEventParameter of which the TDataGatewayEventParameter::getResult property contains the data return from the database. The data returned can be changed by setting the TDataGatewayEventParameter::setResult property.

Input
TDataGatewayCommand$senderoriginator $sender
TDataGatewayResultEventParameter$param
Output
Exception

setTableInfo

protected void setTableInfo (TDbTableInfo $tableInfo )

Input
TDbTableInfo$tableInfotable or view information.
Output
Exception

setTableName

protected void setTableName (string $tableName )

Sets up the command builder for the given table.

Input
string$tableNametable or view name.
Output
Exception

update

public integer update (array $data , string $criteria , array $parameters )

Updates the table with new name-value pair $data. Each array key must correspond to a column name in the table. The update condition is specified by the $where argument and additional binding values can be specified using the $parameter argument.

This method uses additional arguments as $parameters. E.g.

  1. $gateway->update($data, 'age > ? AND location = ?', $age, $location);

Input
array$datanew record data.
string$criteriaupdate condition
array$parametersadditional binding name-value pairs.
Output
integer number of records updated.
Exception

__call

public mixed __call (mixed $method , mixed $args )

Dynamic find method using parts of method name as search criteria.

Method name starting with "findBy" only returns 1 record. Method name starting with "findAllBy" returns 0 or more records. Method name starting with "deleteBy" deletes records by the trail criteria. The condition is taken as part of the method name after "findBy", "findAllBy" or "deleteBy".

The following are equivalent:

  1. $table->findByName($name)
  2. $table->find('Name = ?', $name);
  1. $table->findByUsernameAndPassword($name,$pass); // OR may be used
  2. $table->findBy_Username_And_Password($name,$pass); // _OR_ may be used
  3. $table->find('Username = ? AND Password = ?', $name, $pass);
  1. $table->findAllByAge($age);
  2. $table->findAll('Age = ?', $age);
  1. $table->deleteAll('Name = ?', $name);
  2. $table->deleteByName($name);

Input
mixed$method
mixed$args
Output
mixed single record if method name starts with "findBy", 0 or more records if method name starts with "findAllBy"
Exception