ADOdb Specific Functionality
Setting the Table Name
The default behaviour on creating an ADOdb_Active_Record is to "pluralize" the class name and
use that as the table name. Often, this is not the case. For example, the Person class could be reading
from the "People" table.
We provide two ways to define your own table:
1. Use a constructor parameter to override the default table naming behaviour.
class Person extends ADOdb_Active_Record{}
$person = new Person('People');
2. Define it in a class declaration:
class Person extends ADOdb_Active_Record
{
var $_table = 'People';
}
$person = new Person();
$ADODB_ASSOC_CASE
This allows you to control the case of field names and properties. For example, all field names in Oracle are upper-case by default. So you
can force field names to be lowercase using $ADODB_ASSOC_CASE. Legal values are as follows:
0: lower-case
1: upper-case
2: native-case
So to force all Oracle field names to lower-case, use
$ADODB_ASSOC_CASE = 0;
$person = new Person('People');
$person->name = 'Lily';
$ADODB_ASSOC_CASE = 2;
$person2 = new Person('People');
$person2->NAME = 'Lily';
Also see $ADODB_ASSOC_CASE.
ADOdb_Active_Record::Save()
Saves a record by executing an INSERT or UPDATE SQL statement as appropriate.
Returns false on unsuccessful INSERT, true if successsful INSERT.
Returns 0 on failed UPDATE, and 1 on UPDATE if data has changed, and -1 if no data was changed, so no UPDATE statement was executed.
ADOdb_Active_Record::Replace()
ADOdb supports replace functionality, whereby the record is inserted if it does not exists, or updated otherwise.
$rec = new ADOdb_Active_Record("product");
$rec->name = 'John';
$rec->tel_no = '34111145';
$ok = $rec->replace(); // 0=failure, 1=update, 2=insert
ADOdb_Active_Record::Load($where)
Sometimes, we want to load a single record into an Active Record. We can do so using:
$person->load("id=3");
// or using bind parameters
$person->load("id=?", array(3));
Returns false if an error occurs.
ADOdb_Active_Record::Find($whereOrderBy, $bindarr=false, $pkeyArr=false)
We want to retrieve an array of active records based on some search criteria. For example:
class Person extends ADOdb_Active_Record {
var $_table = 'people';
}
$person = new Person();
$peopleArray =& $person->Find("name like ? order by age", array('Sm%'));
Error Handling and Debugging
In PHP5, if adodb-exceptions.inc.php is included, then errors are thrown. Otherwise errors are handled by returning a value. False by default means an error has occurred. You can get the last error message using the ErrorMsg() function.
To check for errors in ADOdb_Active_Record, do not poll ErrorMsg() as the last error message will always be returned, even if it occurred several operations ago. Do this instead:
# right!
$ok = $rec->Save();
if (!$ok) $err = $rec->ErrorMsg();
# wrong :(
$rec->Save();
if ($rec->ErrorMsg()) echo "Wrong way to detect error";
The ADOConnection::Debug property is obeyed. So
if $db->debug is enabled, then ADOdb_Active_Record errors are also outputted to standard output and written to the browser.
ADOdb_Active_Record::Set()
You can convert an array to an ADOdb_Active_Record using Set(). The array must be numerically indexed, and have all fields of the table defined in the array. The elements of the array must be in the table's natural order too.
$row = $db->GetRow("select * from tablex where id=$id");
# PHP4 or PHP5 without enabling exceptions
$obj =& new ADOdb_Active_Record('Products');
if ($obj->ErrorMsg()){
echo $obj->ErrorMsg();
} else {
$obj->Set($row);
}
# in PHP5, with exceptions enabled:
include('adodb-exceptions.inc.php');
try {
$obj =& new ADOdb_Active_Record('Products');
$obj->Set($row);
} catch(exceptions $e) {
echo $e->getMessage();
}
Primary Keys
ADOdb_Active_Record does not require the table to have a primary key. You can insert records for such a table, but you will not be able to update nor delete.
Sometimes you are retrieving data from a view or table that has no primary key, but has a unique index. You can dynamically set the primary key of a table through the constructor, or using ADOdb_Active_Record::SetPrimaryKeys():
$pkeys = array('category','prodcode');
// set primary key using constructor
$rec = new ADOdb_Active_Record('Products', $pkeys);
// or use method
$rec->SetPrimaryKeys($pkeys);
Retrieval of Auto-incrementing ID
When creating a new record, the retrieval of the last auto-incrementing ID is not reliable for databases that do not support the Insert_ID() function call (check $connection->hasInsertID). In this case we perform a SELECT MAX($primarykey) FROM $table, which will not work reliably in a multi-user environment. You can override the ADOdb_Active_Record::LastInsertID() function in this case.
Dealing with Multiple Databases
Sometimes we want to load data from one database and insert it into another using ActiveRecords. This can be done using the optional parameter of the ADOdb_Active_Record constructor. In the following example, we read data from db.table1 and store it in db2.table2:
$db = NewADOConnection(...);
$db2 = NewADOConnection(...);
ADOdb_Active_Record::SetDatabaseAdapter($db2);
$activeRecs = $db->GetActiveRecords('table1');
foreach($activeRecs as $rec) {
$rec2 = new ADOdb_Active_Record('table2',$db2);
$rec2->id = $rec->id;
$rec2->name = $rec->name;
$rec2->Save();
}
If you have to pass in a primary key called "id" and the 2nd db connection in the constructor, you can do so too:
$rec = new ADOdb_Active_Record("table1",array("id"),$db2);
$ADODB_ACTIVE_CACHESECS
You can cache the table metadata (field names, types, and other info such primary keys) in $ADODB_CACHE_DIR (which defaults to /tmp) by setting
the global variable $ADODB_ACTIVE_CACHESECS to a value greater than 0. This will be the number of seconds to cache.
You should set this to a value of 30 seconds or greater for optimal performance.
Active Record Considered Bad?
Although the Active Record concept is useful, you have to be aware of some pitfalls when using Active Record. The level of granularity of Active Record is individual records. It encourages code like the following, used to increase the price of all furniture products by 10%:
$recs = $db->GetActiveRecords("Products","category='Furniture'");
foreach($recs as $rec) {
$rec->price *= 1.1; // increase price by 10% for all Furniture products
$rec->save();
}
Of course a SELECT statement is superior because it's simpler and much more efficient (probably by a factor of x10 or more):
$db->Execute("update Products set price = price * 1.1 where category='Furniture'");
Another issue is performance. For performance sensitive code, using direct SQL will always be faster than using Active Records due to overhead and the fact that all fields in a row are retrieved (rather than only the subset you need) whenever an Active Record is loaded.
Transactions
The default transaction mode in ADOdb is autocommit. So that is the default with active record too.
The general rules for managing transactions still apply. Active Record to the database is a set of insert/update/delete statements, and the db has no knowledge of active records.
Smart transactions, that does an auto-rollback if an error occurs, is still the best method to multiple activities (inserts/updates/deletes) that need to be treated as a single transaction:
$conn->StartTrans();
$parent->save();
$child->save();
$conn->CompleteTrans();
ADOConnection Supplement
ADOConnection::GetActiveRecords()
This allows you to retrieve an array of ADOdb_Active_Records. Returns false if an error occurs.
$table = 'products';
$whereOrderBy = "name LIKE 'A%' ORDER BY Name";
$activeRecArr = $db->GetActiveRecords($table, $whereOrderBy);
foreach($activeRecArr as $rec) {
$rec->id = rand();
$rec->save();
}
And to retrieve all records ordered by specific fields:
$whereOrderBy = "1=1 ORDER BY Name";
$activeRecArr = $db->ADOdb_Active_Records($table);
To use bind variables (assuming ? is the place-holder for your database):
$activeRecArr = $db->GetActiveRecords($tableName, 'name LIKE ?',
array('A%'));
You can also define the primary keys of the table by passing an array of field names:
$activeRecArr = $db->GetActiveRecords($tableName, 'name LIKE ?',
array('A%'), array('id'));
ADOConnection::GetActiveRecordsClass()
This allows you to retrieve an array of objects derived from ADOdb_Active_Records. Returns false if an error occurs.
class Product extends ADOdb_Active_Records{};
$table = 'products';
$whereOrderBy = "name LIKE 'A%' ORDER BY Name";
$activeRecArr = $db->GetActiveRecordsClass('Product',$table, $whereOrderBy);
# the objects in $activeRecArr are of class 'Product'
foreach($activeRecArr as $rec) {
$rec->id = rand();
$rec->save();
}
To use bind variables (assuming ? is the place-holder for your database):
$activeRecArr = $db->GetActiveRecordsClass($className,$tableName, 'name LIKE ?',
array('A%'));
You can also define the primary keys of the table by passing an array of field names:
$activeRecArr = $db->GetActiveRecordsClass($className,$tableName, 'name LIKE ?',
array('A%'), array('id'));