January 02, 2005
I’d been working on implementing Martin Fowler’s active record design pattern using PHP 5 and for several reasons have decided to start completely over with it and document its development here. Fowler defines the active record pattern as “An object that wraps a row in a database table or view, encapsulates the database access and adds domain logic on that data.” George Schlossnagle includes an implementation in, “Advanced PHP Programming” and I was comfortable using that until I had the opportunity to see David Hansson’s Ruby on Rails in action (quicktime demo here) and really had my eyes opened as to the possibilities that a full implementation of the pattern affords. This isn’t going to be a fully detailed description of the development process - I’m going to hit the high points and go into detail on several of the implementation decisions along the way for two reasons. First, I hope to give non-developers a glimpse into what it is that a developer is doing when they’re staring off into space and, second, I’d love feedback from other developers as to how similar their experiences are and also criticism/suggestions of the active record class as it unfolds.
I’m going to use the Creole database abstraction layer for this project. It was developed to be used with Propel and by checking it out, I know that it supports the ability to grab metadata from the database tables columns and we’re going to need that capability for some of the stuff that I have planned. It’s also implemented in PHP 5 and supports all of the major databases. Installation and setup is well documented and quickly finished.
I want to flesh out the definition of the active record design pattern as defined in Fowler’s “Patterns of Enterprise Application Architecture” (I strongly encourage all developers to get a copy of this book for themselves). First, several types of methods are listed as being included in the class:
- Construct an instance of the Active Record from a SQL result set view
- Construct a new instance for later insertion into the table
- Static finder methods to wrap commonly used SQL queries and return Active Record objects
- Update the database and insert into it the data in the Active Record
- Get and set the fields
- Implement some pieces of business logic
For our first pass at an Active Record implementation, we’re doing the following:
- Setting up the Active Record class that we’ll subclass for each specific entity
- Create static methods used to query the database based on supplied parameters and will return objects of the Active Record subclasses
- Code the __destruct() method so that, based on the autowrite attribute, modified objects will automatically be saved when they’re destroyed. We’ll also need getAutowrite() and setAutowrite() methods.
- Use the $attributes array to write the SQL for the create(), insert(), update() and delete() methods
- Write the __toString() method to print the objects attributes when called
First, I’ll start with a basic class consisting of the properties and constructor:
<?php
// include the autoloader
include_once($_SERVER['DOCUMENT_ROOT'] . "/includes/loader.php");
/**
* Implements Martin Fowler's Active Record design pattern
*
* @package active_record
* @author Jason Perkins <jperkins@sneer.org>
* @version $Revision: 1.4 $
* @copyright Jason Perkins
*/
class active_record
{
/**
* a hash to store the attributes
* @var object
*/
public $attributes = NULL;
/**
* the class name
* @var string
*/
public $class_name = '';
/**
* the ID of this instance
* @var integer
*/
public $id = NULL;
/**
* if the object should be saved if isModified is true
* @var boolean
*/
public $autowrite = true;
/**
* if the object has been modified w/out writing to database
* @var boolean
*/
public $isModified = false;
/**
* if the object has been modified w/out writing to database
* @var resource
*/
public static $db_handle = NULL;
/**
* connects to the database
* @access protected
*/
protected static function db_connect() {
if( self::$db_handle ) {
self::$db_handle = db_factory::getConnection();
}
}
/**
* constructor
*
* @access public
* @param integer $id ID of the wanted row
* @return object the instantian of the underlying row, specified by the ID
*/
public function __construct( $id = NULL )
{
// get a db connection
self::db_connect();
// set ID of this object
if ( $id )
{
$this->id = $id;
}
$this->attributes = new attributes_aggr();
$this->loadAttributes();
return;
}
/**
* destructor
*
* @access public
*/
public function __destruct()
{
if(( $this->autowrite ) && ( $this->isModified ))
{
if( $this->id )
{
$this->update();
}
else
{
$this->create();
}
}
}
/**
* returns the current value of the getAutowrite property
*
* @access public
* @return boolean value of the autowrite property
*/
public function getAutowrite()
{
return $this->autowrite;
}
/**
* sets the current value of the getAutowrite property to the
* supplied parameter
*
* @access public
* @param boolean $value value to assign to the autowrite property
*/
public function setAutowrite( $value )
{
$this->autowrite = $value;
return;
}
}
?>
The include_once makes use of the autoload function, new to PHP 5. The content of the loader.php file is:
<?php
/**
* autoloader
*
*
* @param string $class class to be loaded
*/
function __autoload ( $class )
{
$path = $_SERVER['DOCUMENT_ROOT'] . '/includes/' . $class . '.php';
include_once $path;
return;
}
?>
If a class (or file) is used and can’t be found by the PHP interpreter, the class name is passed to the autoload() function. You can then do any processing on the class name to determine its actual location. This means that class names similar to those in Java can be used (i.e. - collections.aggregate ) and when PHP can’t find the class, the class name is passed to the autoload() function which we can then have substitute directories for periods except for the class itself which we’ll add a ‘.php’ too and then prepend that value with $_SERVER[‘DOCUMENT_ROOT’] and ‘/includes/’, so collections.aggregate becomes $_SERVER[‘DOCUMENT_ROOT’] . ‘/includes/collections/aggregate.php’. I’m not making use of the full benenfits at this point to remain as flexible as possible as we proceed. I’m assuming that I’ll want a directory structure that accomadates the Java style layout for the collections that we’ll be using and at the same time allow us to store all of the active_record descendants in a directory named ‘entities.’ Like I said, I’m not sure about all of this so I’m going to put off making that decision for the time being.
A brief description of the active_record properties:
$attributes is going to be used as a container holding $attribute objects - one attribute object for each column of the underlying table with the columns metadata initialized in the $attribute object. If we’re loading the object from the database, then they’ll be initialized to those values.
$class_name holds the name of the underlying table.
$id is the value of the primary key for the row associated with this specific active record.
$autowrite is a boolean value that is checked in the destructor - if it’s true (which it is by default) and the active_record isModified then we write the values of the $attributes back to the database.
$isModified is another boolean value with an initial value of false that becomes true each time one of the $attributes is changed and is set back to false on a successful insert() or update().
$db_handle holds the resource to the database connection.
db_connect() is a static method that’s used to initiate a connection to the database, storing the database resource handle in $db_handle.
When created, an active_record object will make sure that a $db_handle exists, creating one if it doesn’t and if it was passed an $id, will set the $id property, create a new attribute aggregate (assigning it to $attributes) and then call loadAttributes().
The attribute_aggr class looks like this:
<?php
// include the autoloader
include_once($_SERVER['DOCUMENT_ROOT'] . "/includes/loader.php");
/**
* class that represents an aggregate
*
* @package hosting_package
* @author Jason Perkins <jperkins@sneer.org>
* @version $Revision: 1.2 $
* @copyright Jason Perkins
*
*/
class attributes_aggr extends aggregate {
/**
* constructur
*
* @access public
* @param datatype $paramname description
* @return datatype description
*/
public function __construct()
{
parent::__construct();
}
/*
* Adds the passed $item(s) to the $this->collection
*
* @access public
* @returns integer The number of parts
*/
public function add( $item )
{
$this->container[ $item->getName() ] = $item;
}
/**
* __get
*
* @access public
* @param datatype $paramname description
* @return datatype description
*/
public function __get( $property )
{
if( $this->isPresent( $property ))
{
return $this->container[ $property ]->getValue();
}
}
/**
* __set
*
* @access public
* @param datatype $paramname description
* @return datatype description
*/
public function __set( $property, $value )
{
if( $this->isPresent( $property ))
{
$this->container[ $property ]->setValue( $value );
return;
}
}
/**
* __toString
*
* @access public
* @param datatype $paramname description
* @return datatype description
*/
public function __toString()
{
$string = "Attributes <br />";
foreach( $this->container as $attribute => $value )
{
$string .= "{$attribute}: {$value->getValue()}";
$string .= "<br />";
}
return $string;
}
}
?>
The attributes_aggr extends the aggregate class where I’ve defined the necessary methods to implement SPL’s iterator interface (introduction to the SPL) along with a couple of other methods. The __get(), __set() and __toString() methods are also new to PHP 5. The __get() and __set() operators allow us to intercept calls to non-existent properties and deal with accessing them. We need to do this so that a call to attributes_aggr that gets or sets a property can be redirected to the container array (the container array is defined in the aggregate parent class). The __toString() method is called when we print the class if it’s defined — very useful for debugging.
We’ve got to add similar __get() and set() methods to active_record so that individual attributes in the attributes collection can be retriedved and set:
<?php
/**
* get interceptor
*
* @access public
* @param string $attribute attribute to be gotten
* @return unknown value of the $attribute
*/
public function __get( $property )
{
if ($this->attributes->isPresent( $property ))
{
return $this->attributes->$property;
}
}
/**
* set interceptor
*
* @access public
* @param string $attribute attribute to be set
* @param unknown $value value to set attribute to
*/
public function __set( $property, $value )
{
if ($this->attributes->isPresent( $property ))
{
$this->attributes->$property = $value;
$this->isModified = true;
return;
}
}
?>
Implementing the iterator interface will allow us to pass the attributes_aggr to foreach and have each of contained objects iterated over in turn. I did this implementation in the aggregate class:
<?php
// include the autoloader
include_once($_SERVER['DOCUMENT_ROOT'] . "/includes/loader.php");
/**
* class that represents an aggregate
*
* @package hosting_package
* @author Jason Perkins <jperkins@sneer.org>
* @version $Revision: 1.2 $
* @copyright Jason Perkins
*
*/
class aggregate implements Iterator
{
/**
* array of properties
* @var array
*/
public $container = array();
/**
* resets iterator list to its start
*
* @access public
* @param type $containeriable
* @return datatype description
*
*/
public function isPresent( $value )
{
if ( array_key_exists( $value, $this->container ) )
{
return true;
}
else
{
return false;
}
}
/**
* resets iterator list to its start
*
* @access public
* @param type $containeriable
* @return datatype description
*
*/
public function rewind()
{
reset($this->container);
}
/**
* returns the current item
*
* @access public
* @return datatype description
*
*/
public function current()
{
$container = current($this->container);
return $container;
}
/**
* returns the key of the current item
*
* @access public
* @return datatype description
*
*/
public function key()
{
$container = key($this->container);
return $container;
}
/**
* movers iterator to the next item in the list
*
* @access public
* @return datatype description
*
*/
public function next()
{
$container = next($this->container);
return $container;
}
/**
* says if there are additional items in the list
*
* @access public
* @return datatype description
*
*/
public function valid()
{
$container = $this->current() !== false;
return $container;
}
/*
* Returns the number of parts
*
* @access public
* @returns integer The number of parts
*/
public function count()
{
return count( $this->container);
}
/*
* Adds the passed $item(s) to the $this->collection
*
* @access public
* @returns integer The number of parts
*/
public function add( $item )
{
if( is_array($item) )
{
foreach( $item as $current_index => $current_item )
{
$this->container[] = $current_item;
}
}
else
{
$this->container[] = $item;
}
}
/*
* Removes the passed item(s) from $this->collection
*
* @access public
* @returns integer The number of parts
*/
public function remove( $item )
{
$found = false;
if (is_array( $item ))
{
foreach ( $item as $current_index => $current_item )
{
foreach ( $this->container as $container_index => $container_item )
{
if ( $current_item === $container_item )
{
unset ( $this->container[ $container_index ] );
$found = true;
}
}
}
}
else
{
foreach ( $this->container as $current_index => $current_item )
{
if ( $current_item === $item )
{
unset ( $this->container[ $current_index ] );
$found = true;
}
}
}
}
}
?>
Finally, we need to go over the attribute class which represent a specific attribute for an active_record implementation. I have big plans for the attribute class, so we’re going to load it up with functionality that at this point will be overkill. Have faith. Here’s the class definition:
<?php
// include the autoloader
include_once($_SERVER['DOCUMENT_ROOT'] . "/includes/loader.php");
/**
* represents an attribute of an active_record
*
* @package active_record
* @author Jason Perkins <jperkins@sneer.org>
* @version $Revision: 1.1.1.1 $
* @copyright Jason Perkins
*/
class attribute {
/**
* description of variable
* @var type
*/
protected $name = '';
/**
* description of variable
* @var type
*/
protected $value = '';
/**
* description of variable
* @var type
*/
protected $formatted_name = '';
/**
* description of variable
* @var type
*/
protected $size = '';
/**
* description of variable
* @var type
*/
protected $type = '';
/**
* description of variable
* @var type
*/
protected $is_nullable = '';
/**
* description of variable
* @var type
*/
protected $is_fk = '';
/**
* description of variable
* @var type
*/
protected $fk_table = '';
/**
* constructor
*
* @access public
* @param datatype $variable description
* @return datatype description
*/
public function __construct( $name ) {
$this->name = $name;
}
/**
* getValue
*
* @access public
* @return datatype description
*/
public function getValue()
{
return $this->value;
}
/**
* setValue
*
* @access public
* @param datatype $paramname description
* @return datatype description
*/
public function setValue( $value )
{
$this->value = $value;
return;
}
/**
* getFormattedName
*
* @access public
* @return datatype description
*/
public function getFormattedName()
{
return $this->formatted_name;
}
/**
* setFormattedName
*
* @access public
* @param datatype $paramname description
* @return datatype description
*/
public function setFormattedName( $name )
{
$this->formatted_name = $name;
return;
}
/**
* getSize
*
* @access public
* @return datatype description
*/
public function getSize()
{
return $this->size;
}
/**
* setSize
*
* @access public
* @param datatype $paramname description
* @return datatype description
*/
public function setSize( $size )
{
$this->size = $size;
return;
}
/**
* getType
*
* @access public
* @return datatype description
*/
public function getType()
{
return $this->type;
}
/**
* setType
*
* @access public
* @param datatype $paramname description
* @return datatype description
*/
public function setType( $type )
{
$this->type = $type;
return;
}
/**
* getIsNullable
*
* @access public
* @return datatype description
*/
public function getIsNullable()
{
return $this->is_nullable;
}
/**
* setIsNullable
*
* @access public
* @param datatype $paramname description
* @return datatype description
*/
public function setIsNullable( $is_nullable )
{
$this->is_nullable = $is_nullable;
return;
}
/**
* getIsFK
*
* @access public
* @return datatype description
*/
public function getIsFK()
{
return $this->is_fk;
}
/**
* setIsFK
*
* @access public
* @param datatype $paramname description
* @return datatype description
*/
public function setIsFK( $is_fk )
{
$this->is_fk = $is_fk;
return;
}
/**
* getFKTable
*
* @access public
* @return datatype description
*/
public function getFKTable()
{
return $this->is_fk;
}
/**
* getFKTable
*
* @access public
* @param datatype $paramname description
* @return datatype description
*/
public function setFKTable( $fk_table )
{
$this->fk_table = $fk_table;
return;
}
/**
* getLength
*
* @access public
* @return datatype description
*/
public function getLength()
{
return strlen( $this->value );
}
/**
* getName
*
* @access public
* @return datatype description
*/
public function getName()
{
return $this->name;
}
}
?>
We have to add a method to the active_record class that instantiates the $attribute objects and loads the metadata and (if $this->id is set) the data for each $attribute:
<?php
/**
* loads the attributes for this active_record instance
*
* @access public
*/
public function loadAttributes()
{
// load attribute metadata
$table_info = self::$db_handle->getDatabaseInfo()->getTable( __CLASS__ );
foreach( $table_info->getColumns() as $col)
{
// set column name
$col_name = $col->getName();
// don't initialize an attribute object for ID
if( $col_name == 'id' )
{
continue;
}
$attribute = new attribute( $col_name );
$attribute->setSize( $col->getSize() );
// set creole type
$type = CreoleTypes::getCreoleName( $col->getType() );
$attribute->setType( $type );
// set the formatted name
$formatted = str_replace( '_', ' ', $col->getName() );
$attribute->setFormattedName( $formatted );
// set is_nullable
if ( $col->isNullable )
{
$attribute->setIsNullable( true );
}
else
{
$attribute->setIsNullable( false );
}
// set the is_fk and fk_table
$pattern = '/^(.*)_id$/';
if ( preg_match($pattern, $col_name, $matches) )
{
$attribute->setIsFK( true );
$attribute->setFKTable( $matches[ 1 ] );
}
else
{
$attribute->setIsFK( false );
}
$this->attributes->add( $attribute );
#unset( $attribute );
}
// if $this->id is set, then load the values for this instance
if( $this->id )
{
// build sql query to load attribute values
$sql = "select * from {$this->class_name} where id = '{$this->id}'";
$rs = self::$db_handle->executeQuery( $sql );
$rs->next();
$row = $rs->getRow();
// load the attribute values
foreach( $row as $attribute => $value ) {
if( $attribute == 'id' )
{
continue;
}
$this->$attribute = $value;
}
}
return;
}
?>
The only things of particular note here are: the constraint that columns containing foreign keys must be named with the form foreign_table_name + _id so that we can identify them as we loop over the column names; the formatted_name which is constructed by replacing any underscores present in the column name with spaces; and that we don’t load the primary key (assumed to be identified by the name ‘id’).
When now need three methods to corresponding to SQLs insert, update and delete commands which I’ve named: insert(), update() and delete(). Here are their definitions:
<?php
/**
* updates the underlying row in the database
*
* @access public
*/
public function update()
{
if( !$this->id )
{
throw new Exception("{$this->class_name} object needs an ID to update()");
}
// run the validation method
if (!$this->validate())
{
print "validation failed. ";
exit;
}
$sql = "UPDATE {$this->class_name} SET ";
foreach ($this->attributes as $attribute)
{
// don't set the primary key
if ( $attribute == 'id' )
{
continue;
}
$sql .= "{$attribute->getName()} = '{$attribute->getValue()}', \r";
}
$sql = rtrim($sql, "\r ,");
$sql .= " WHERE id = '{$this->id}'";
$rs = self::$db_handle->executeQuery( $sql );
return;
}
/**
* creates a new table row for this object and inserts its current
* attribute values into their corresponding fields in the table and
* set the id property
*
* @access public
* @param array $attributes a hash indexed on the attribute with its property
*/
public function insert( $attributes = NULL )
{
if ( $attributes )
{
foreach($attributes as $current_attribute => $value)
{
$this->$attribute = $value;
}
}
if( $this->id )
{
throw new Exception("{$this->class_name} object has an ID, can't create()");
}
// run the validate method
if (!$this->validate())
{
print "validation failed. ";
exit;
}
$sql = "INSERT INTO {$this->class_name}
SET ";
foreach ($this->attributes as $attribute)
{
// don't set the primary key
if ( $attribute == 'id' )
{
continue;
}
$sql .= "{$attribute->getName()} = '{$attribute->getValue()}', \r";
}
$sql = rtrim($sql, "\r ,");
$rs = self::$db_handle->executeQuery( $sql );
// now we need to get the id of the new row and set $this->id
return;
}
/**
* deletes the underlying row from the database
*
* @access public
* @param datatype $variable description
* @return datatype description
*/
public function delete()
{
if( !$this->id )
{
throw new Exception("{$this->class_name} object has no ID, can't delete()");
}
$sql = "delete from {$this->class_name} where id = {$this->id}";
$rs = self::$db_handle->executeQuery( $sql );
return;
}
?>
At this point, things are such that we can create an object based on an underlying database table and if we pass an ID to the constructor, it will load up the table row with that ID. We can then modify any of the attribute values or set them for the first time if this is to be a new row and they’ll be saved to the database when the object is destroyed (if we leave the autosave property to its default property of ‘true’).
What we lack is a means of identifying the IDs that we want to load — and that’s the last thing that we’re going to do today by adding static find* methods to the active_record class:
<?php
/**
* Find all
*
* Returns an array of all the objects that could be instantiated from
* the associated table in the database.
*
* @access public
* @param string $table_name name of table to execute query against
* @param string $conditions conditions to apply in query
* @param string $orderings order to return results with
* @param string $limit any limit to apply to query
* @return object first object that matches "conditions" and "orderings"
*/
public static function find_all($table_name, $conditions = NULL,
$orderings = NULL, $limit = NULL, $joins = NULL) {
// construct the sql
$sql = "select id from " . __CLASS__;
if ( $conditions ) {
$sql .= " where $conditions";
}
if ( $orderings ) {
$sql .= " order by $orderings";
}
if ( $limit ) {
$sql .= " limit $limit";
}
$sales_order_array = self::find_by_sql( $sql, $table_name );
return $sales_order_array;
}
/**
* find_first
*
* Returns the object for the first record matching "conditions"
*
* @access public
* @param string $table_name name of table to execute query against
* @param string $conditions conditions to apply in query
* @param string $orderings order to return results with
* @return object first object that matches "conditions" and "orderings"
*/
public function find_first($table_name, $conditions = NULL,
$orderings = NULL)
{
// generate sql
$sql = "select id from " . __CLASS__;
if ( !is_null( $conditions ) )
{
$sql .= " where $conditions";
}
if ( !is_null( $orderings ))
{
$sql .= " order by $orderings";
}
// tack on a limit of 1 so that we only get one record returned
$sql .= " limit 1";
$sales_order_array = self::find_by_sql( $sql );
$sales_order = $sales_order_array[0];
return $sales_order;
}
/**
* find_by_sql
*
* Works like find_all, but requires a complete SQL string.
*
* @access public
* @param string $sql query to execute
* @return datatype description
*/
public function find_by_sql( $sql = NULL )
{
if ( self::$db_handle == false)
{
self::db_connect();
}
$rs = self::$dbh->executeQuery( $sql );
$count = $rs->getRecordCount();
if ( $count == 0 )
{
// no records were returned - throw an error
print "no records were found. \n\n";
return;
}
else
{
while( $rs->next() )
{
$id = $rs->get('id');
$object_array[] = new $table_name( $id );
}
return $object_array;
}
}
?>
The interfaces for the find* methods are shamelessly lifted from Ruby on Rails active_record component. These methods also show one of the issues that being irking the hell out of me — the entites will descend from active_record so when we call one of the find methods on the entities the call will be passed to active_record and because they’re static methods, we don’t know the name of the class that the call originally went to and we have to have that to specify the table in the SQL in the find methods. The kludge work around that I’ve used is to require that the table name be passed to the find methods, but this just sucks — I’d really appreciate comments directed toward overcoming that limitation. Also, please note that a lot of stuff was either glossed over or simply omitted like the db handle factory that is too incidental (I thought) to the active_record class to be included here.
That’s it for now — I’ve hit the most salient points (and many not-so-many salient points). We need to add four more methods to add the basics of handling RDBMS relationships and I plan to cover them one at a time over the next couple of days instead of with one huge post. My apologies for the length of this entry. The active_record class still isn’t released — I’m going to wait until those other four methods are added, giving us a bare minimum of functionality for what it is that I have in mind before I open it up to y’all. Comments to this post are closed; I’ve setup a mailing list at chassis@sneer.org and would prefer comments, suggestions, criticicms, etc. be directed there. To subscribe, please send an empty email to chassis-subscribe@sneer.org (and chassis-unsubscribe@sneer.org to unsubscribe).
I hope that this isn’t construed as a (bad) attempt to steal Ruby on Rail’s thunder (thought it’d be an unearned compliment given how fantastic Rails is). I’ve had the chance to play around with both Ruby and Ruby on Rails and love both. Unfortunately, at this point PHP adoption is too widespread to ignore the need of something similar for those of us that can’t simply drop PHP and head over to the Ruby camp. And I’m also having a good time pushing PHP 5.