Active Record

Active Records are objects that wrap a row in a database table or view, encapsulates the database access and adds domain logic on that data. The basics of an Active Record is a business object class, e.g., a Products class, that match very closely the record structure of an underlying database table. Each Active Record will be responsible for saving and loading data to and from the database.

Info: The data structure of an Active Record should match exactly that of a table in the database. Each field in the class must correspond to one column in the table.

When to Use It

Active Record is a good choice for domain logic that isn't too complex, such as creates, reads, updates, and deletes. Derivations and validations based on a single record work well in this structure. Active Record has the primary advantage of simplicity. It's easy to build Active Records, and they are easy to understand.

However, as your business logic grows in complexity, you'll soon want to use your object's direct relationships, collections, inheritance, and so forth. These don't map easily onto Active Record, and adding them piecemeal gets very messy. Another argument against Active Record is the fact that it couples the object design to the database design. This makes it more difficult to refactor as a project goes forward.

The alternative is to use a Data Mapper that separates the roles of the business object and how these objects are stored. Prado provides a complimentary choice between Active Record and SqlMap Data Mapper. A SqlMap Data Mapper can be used to load Active Record objects, in turn, these Active Record objects can be used to update the database. The "relationship" between Active Records and SqlMap is illustrated in the following diagram. More details regarding the SqlMap Data Mapper can be found in the SqlMap Manual. alt="Active Records and SqlMap DataMapper" id="fig:diagram.png" class="figure"/>

The Active Record class has methods that do the following:

The Active Record implementation utilizes the Prado DAO classes for data access. The current Active Record implementation supports MySQL, Postgres SQL and SQLite databases. Support for other databases can be provided when there are sufficient demand.

Defining an Active Record

Let us consider the following "users" table that contains two columns named "username" and "email", where "username" is also the primary key. CREATE TABLE users ( username VARCHAR( 20 ) NOT NULL , email VARCHAR( 200 ) , PRIMARY KEY ( username ) );

Next we define our Active Record class that corresponds to the "users" table. class UserRecord extends TActiveRecord { const TABLE='users'; //table name public $username; //the column named "username" in the "users" table public $email; /** * @return TActiveRecord active record finder instance */ public static function finder($className=__CLASS__) { return parent::finder($className); } }

Each property of the UserRecord class must correspond to a column with the same name in the "users" table. The class constant TABLE is optional when the class name is the same as the table name in the database, otherwise TABLE must specify the table name that corresponds to your Active Record class.

Note: You may need to quote (specific to your database) the value of the TABLE. E.g. MySQL uses back-ticks, TABLE = "`database1`.`table1`"

Since TActiveRecord extends TComponent, setter and getter methods can be defined to allow control over how variables are set and returned. For example, adding a $level property to the UserRecord class:

class UserRecord extends TActiveRecord { ... //existing definitions as above private $_level; public function setLevel($value) { $this->_level=TPropertyValue::ensureInteger($value,0); } public function getLevel($value){ return $this->_level; } }
Info: TActiveRecord can also work with database views by specifying the constant TABLE corresponding to the view name. However, objects returned from views are read-only, calling the save() or delete() method will raise an exception.

The static method finder() returns an UserRecord instance that can be used to load records from the database. The loading of records using the finer methods is discuss a little later. The TActiveRecord::finder() static method takes the name of the current Active Record class as parameter.

Setting up a database connection

A default database connection for Active Record can be set as follows. See Establishing Database Connection for futher details regarding creation of database connection in general. //create a connection and give it to the ActiveRecord manager. $dsn = 'pgsql:host=localhost;dbname=test'; //Postgres SQL $conn = new TDbConnection($dsn, 'dbuser','dbpass'); TActiveRecordManager::getInstance()->setDbConnection($conn);

The default database connection can also be configured using a <module> tag in the application.xml or config.xml as follows.

Tip: The EnableCache attribute when set to "true" will cache the table meta data, that is, the table columns names, indexes and constraints are saved in the cache and reused. You must clear or disable the cache if you wish to see chanages made to your table definitions. A cache module must also be defined for the cache to function.

A ConnectionID property can be specified with value corresponding to another TDataSourceConfig module configuration's ID value. This allows the same database connection to be used in other modules such as SqlMap.

Loading data from the database

The TActiveRecord class provides many convenient methods to find records from the database. The simplest is finding records by matching primary keys. See the for more details.

findByPk()

Finds one record using only the primary key or composite primary keys. $finder = UserRecord::finder(); $user = $finder->findByPk($primaryKey); //when the table uses composite keys $record = $finder->findByPk($key1, $key2, ...); $record = $finder->findByPk(array($key1, $key2,...));

findAllByPks()

Finds multiple records using a list of primary keys or composite primary keys. The following are equivalent for scalar primary keys (primary key consisting of only one column/field). $finder = UserRecord::finder(); $users = $finder->findAllByPk($key1, $key2, ...); $users = $finder->findAllByPk(array($key1, $key2, ...)); The following are equivalent for composite keys. //when the table uses composite keys $record = $finder->findAllByPks(array($key1, $key2), array($key3, $key4), ...); $keys = array( array($key1, $key2), array($key3, $key4), ... ); $record = $finder->findAllByPks($keys);

find()

Finds one single record that matches the criteria. The criteria can be a partial SQL string or a TActiveRecordCriteria object.

$finder = UserRecord::finder(); //:name and :pass are place holders for specific values of $name and $pass $finder->find('username = :name AND password = :pass', array(':name'=>$name, ':pass'=>$pass)); //using position place holders $finder->find('username = ? AND password = ?', array($name, $pass)); //same as above $finder->find('username = ? AND password = ?', $name, $pass); //$criteria is of TActiveRecordCriteria $finder->find($criteria); //the 2nd parameter for find() is ignored.

The TActiveRecordCriteria class has the following properties:

$criteria = new TActiveRecordCriteria; $criteria->Condition = 'username = :name AND password = :pass'; $criteria->Parameters[':name'] = 'admin'; $criteria->Parameters[':pass'] = 'prado'; $criteria->OrdersBy['level'] = 'desc'; $criteria->OrdersBy['name'] = 'asc'; $criteria->Limit = 10; $criteria->Offset = 20;

findAll()

Same as find() but returns an array of objects.

findBy*() and findAllBy*()

Dynamic find method using parts of method name as search criteria. Method names starting with findBy return 1 record only. Method names starting with findAllBy return an array of records. The condition is taken as part of the method name after findBy or findAllBy. The following blocks of code are equivalent:

$finder->findByName($name) $finder->find('Name = ?', $name); $finder->findByUsernameAndPassword($name,$pass); $finder->findBy_Username_And_Password($name,$pass); $finder->find('Username = ? AND Password = ?', $name, $pass); $finder->findAllByAge($age); $finder->findAll('Age = ?', $age);
Tip: You may also use OR as a condition in the dynamic methods.

findBySql()

Finds records using full SQL, returns corresponding array of record objects.

count()

Find the number of matchings records.

Inserting and updating records

Add a new record using TActiveRecord is very simple, just create a new Active Record object and call the save() method. E.g.

$user1 = new UserRecord(); $user1->username = "admin"; $user1->email = "admin@example.com"; $user1->save(); //insert a new record $data = array('username'=>'admin', 'email'=>'admin@example.com'); $user2 = new UserRecord($data); //create by passing some existing data $user2->save(); //insert a new record
Tip: The objects are update with the primary key of those the tables that contains definitions that automatically creates a primary key for the newly insert records. For example, if you insert a new record into a MySQL table that has columns defined with "autoincrement", the Active Record objects will be updated with the new incremented values.

To update a record in the database, just change one or more properties of the Active Record object that has been loaded from the database and then call the save() method. $user = UserRecord::finder()->findByName('admin'); $user->email="test@example.com"; //change property $user->save(); //update it.

Active Record objects have a simple life-cycle illustrated in the following diagram. alt="Active Records Life Cycle" id="fig:cycle.png" class="figure"/> We see that new ActiveRecord objects are created by either using one of the find*() methods or using creating a new instance by using PHP's new keyword. Objects created by a find*() method starts with clean state. New instance of ActiveRecords created other than by a find*() method starts with new state. When ever you call the save() method on the ActiveRecord object, the object enters the clean state. Objects in the clean becomes dirty whenever one of more of its internal states are changed. Calling the delete() method on the object ends the object life-cycle, no futher actions can be performed on the object.

Deleting existing records

To delete an existing record that is already loaded, just call the delete() method. You can also delete records in the database by primary keys without loading any records using the deleteByPk() method. For example, to delete one or records with tables having a scalar primary key.

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

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

$finder->deleteByPk(array($key1,$key2)); //delete 1 record //delete multiple records $finder->deleteByPk(array($key1,$key2), array($key3,$key4),...); //delete multiple records $finder->deleteByPk(array( array($key1,$key2), array($key3,$key4), .. ));

deleteAll() and deleteBy*()

To delete by a criteria, use deleteAll($criteria) and deleteBy*() with similar synatx to findAll($criteria) and findAllBy*() as described above.

//delete all records with matching Name $finder->deleteAll('Name = ?', $name); $finder->deleteByName($name); //delete by username and password $finder->deleteBy_Username_And_Password($name,$pass);

Transactions

All Active Record objects contains the property DbConnection that can be used to obtain a transaction object. $finder = UserRecord::finder(); $finder->DbConnection->Active=true; //open if necessary $transaction = $finder->DbConnection->beginTransaction(); try { $user = $finder->findByPk('admin'); $user->email = 'test@example.com'; //alter the $user object $user->save(); $transaction->commit(); } catch(Exception $e) // an exception is raised if a query fails { $transaction->rollBack(); }

References

$Id$