The Active Record Pattern

This article, the first of two parts, helps you use design patterns to better organize how your web application interacts with a database. It is excerpted from chapter 14 of the book php|architect’s Guide to PHP Design Patterns, written by Jason E. Sweat (php|architect, 2005; ISBN: 0973589825).

The design patterns you’ve seen so far greatly improve the readability and maintainability of script internals; however, none have confronted a fundamental requirement and challenge of architecting and developing web applications: connecting to a database. This chapter and the next two chapters-Table Data Gateway and Data Mapper-provide three design patterns that better organize how your application interacts with a database.

The Problem

Most web applications persist information in a database. Is there a way to abstract database connectivity to simplify table access and integrate persistence with business logic?

The Solution

Conceptually, the Active Record pattern is the simplest of the database-related design patterns. The Active Record pattern embeds the knowledge of how to interact with the database directly into the class performing the interaction.

While Active Record leads to a high degree of coupling between application code and database structure, in relatively simple circumstances the issues inherent in coupling may be far easier to manage than adopting a more complex solution. Active Record is also sufficient for many first-time database projects. Only if complications arise that cannot be easily addressed with the Active Record pattern should you refactor to a Table Data Gateway (see Chapter 15), a Data Mapper (see Chapter 16), or another database design pattern.

Patterns of Enterprise Application Architecture

According to Martin Fowler’s book, Patterns of Enterprise Application Architecture, an enterprise application is integrated with other applications, contains significant business logic (or illogical, as application requirements often reveal), and includes  lots of concurrently accessed, persistent data that’s accessed from a variety of interfaces. Interestingly, web applications share many of those very characteristics, which may explain why Fowler’s book resonates strongly with PHP programmers. 

PHP Data Objects

One project to watch is PDO. PDO is a PHP extension for high-performance database access (not database abstraction). PDO is a C-language wrapper of the native drivers and is therefore very fast. PDO provides prepared statements for all PDO drivers, enhancing the security of scripts using the library.

{mospagebreak title=Sample Code}

Any discussion of database connectivity depends on choosing both a database and an access layer. This and the following two chapters use the popular open source database MySQL (http://www.mysql.com/) and the ADOdb (http://adodb.sf.net/) access layer. I established ADOdb as a standard in my workplace because it has excellent performance and it abstracts the Oracle OCI interface and interfaces to PostgreSQL, Sybase, MySQL, and other databases in a uniform, simple-to-use PHP API, allowing you to focus on your programming and business logic.

Feel free to substitute you own database and access layer, as most of the concepts presented here readily port to other solutions.

Before looking at the Active Record pattern, let’s start with basic database connectivity. It’s ideal to have a central, simple way to specify connection parameters (the hostname, username, password, and database) and to create a database connection object.  A Singleton (see Chapter 4) typically suffices.

Here’s a DB class with a conn() method that returns the Singleton instance of the ADOConnection class.

  // PHP5
 
require_once ‘adodb/adodb.inc.php’;
 
class DB {
 
  //static class, we do not need a constructor
   
private function __construct() {}
    public static function conn() {
 
    static $conn;
 
    if (!$conn) {
 
      $conn = adoNewConnection(‘mysql’);
 
      $conn->connect(‘localhost’, ‘username’, ‘passwd’, ‘database’);
 
      $conn->setFetchMode(ADODB_FETCH_ASSOC);
      }
      return $conn;
    }
 
}

The DB class allows you to control the type of database and the connection parameters used in connecting to the database. At the top, the code includes the ADOdb library (you may need to adjust the include path to suit your environment); The DB constructor is private since there’s no need to ever create an instance of DB; And the line $conn->setFetchMode (ADODB_FETCH_ASSOC) instructs the result set object to return rows as associative arrays of field_name => value. Using an associative array is an important best practice to adopt in working with databases, so your code remains unaffected (less brittle) by the ordering of fields in SELECT clauses of your SQL statements.

As an example application, let’s create an Active Record object to maintain a table of hyperlinks. Here’s the SQL to create the hyperlinks table in a MySQL database:

  define(‘BOOKMARK_TABLE_DDL’, <<<EOS
 
CREATE TABLE `bookmark` (
 
  `id` INT NOT NULL AUTO_INCREMENT ,
 
  `url` VARCHAR( 255 ) NOT NULL ,
 
  `name` VARCHAR( 255 ) NOT NULL ,
 
  `description` MEDIUMTEXT,
 
  `tag` VARCHAR( 50 ) ,
 
  `created` DATETIME NOT NULL ,
 
  `updated` DATETIME NOT NULL ,
 
PRIMARY KEY ( `id` )
 
)
 
EOS
  );

{mospagebreak title=Test Independence}

Tests should be independent of each other; otherwise, the mere running of a certain test could interfere with the results of latter tests.

To avoid interference between tests that rely on a database, it’s best to drop and recreate the database (or just specific tables) between each test method. SimpleTest provides the standard xUnit setup() method to prepare for each test.

Here’s how you might “reset” the database between each test:

class ActiveRecordTestCase extends UnitTestCase {  
  protected $conn;
  function __construct($name=”) {
    $this->UnitTestCase($name);
    $this->conn = DB::conn();
  }
  function setup() {
    $this->conn->execute(‘drop table bookmark’);
    $this->conn->execute(BOOKMARK_TABLE_DDL);
  }
}

The code populates the $conn attribute with a standard ADOConnection object and then uses the connection’s execute() method to perform SQL statements dropping and recreating the table. Because this is in the setup() method, each test method starts out with a fresh copy of the database table to work with.

Going a little further, you can do some basic sanity checks of the setup() method (and learn a little bit about the ADOConnection  API along the way):

class ActiveRecordTestCase extends UnitTestCase { 
  // …
  function testSetupLeavesTableEmptyWithCorrectStructure() {
    $rs = $this->conn->execute(‘select * from bookmark’);
    $this->assertIsA($rs, ‘ADORecordSet’);
    $this->assertEqual(0,$rs->recordCount());
    foreach(array(
      ‘id’,
      ‘url’,
      ‘name’,
      ‘description’,
      ‘tag’,
      ‘created’,
      ‘updated’) as $i => $name) {
      $this->assertEqual($name, $rs->fetchField($i)->name);
    }
  }
}

Even if you’re unfamiliar with ADOdb, you can probably still discern that the execute() method returns an ADORecordSet object if successful. The object has a recordCount() method, which is used here to verify the table is empty. The record set object also has some methods to explore result set metadata and the fetchField() is used to verify the structure of the table.

{mospagebreak title=Record Creation}

After connecting to the database, your “Create, Read, Update, and Delete” (CRUD) application must be able to create rows in the database.

CRUD

The acronym CRUD stands for Create, Read, Update and Delete. These are the basic foundations of any application that interacts with a database. Many PHP web applications are examples of CrudScreen applications (http://c2.com/cgi/wiki?CrudScreen).

The sample application saves bookmarks to a database, so let’s name the Active Record class Bookmark. To create a new bookmark, use new to create a Bookmark and set the instance’s properties. When all of the (mandatory) properties are set, use the save() method to store the bookmark in the database.

This test captures that intent: 

class ActiveRecordTestCase extends UnitTestCase {
  // …
  function testNew() {
    $link = new Bookmark;
    $link->url = ‘http://simpletest.org/’;
    $link->name = ‘SimpleTest’;
    $link->description = ‘SimpleTest project homepage’;
    $link->tag = ‘testing’;
    $link->save();
    $this->assertEqual(1, $link->getId());
  }
}

According to this test, the class Bookmark has a few public attributes and a save() method. After the instance is saved in the database, getId() should return the database row ID assigned to this Bookmark.

Here are the Bookmark class attributes:

  class Bookmark {
 
  public $url;
 
  public $name;
 
  public $description;
 
  public $tag;
 
}

Let’s turn to the save() method. It requires a database connection, so let’s use the DB::conn() connection factory in the constructor:

  class Bookmark {
 
  protected $id;
 
  protected $conn;
 
  // …
    public function __construct() {
 
    $this->conn = DB::conn();
    }
 
}

$conn is now a database connection suitable for save() to use.

  class Bookmark {
    // …
    const INSERT_SQL = “
 
    insert into bookmark (url, name, description,
        tag, created, updated)
 
    values (?, ?, ?, ?, now(), now())
 
    “;
 
  protected function save() {
 
    $rs = $this->conn->execute(
 
      self::INSERT_SQL
 
      ,array($this->url, $this->name,
          $this->description, $this->tag));
 
    if ($rs) {
 
      $this->id = (int)$this->conn->Insert_ID();
 
    } else {
 
      trigger_error(‘DB Error: ‘.$this->conn->errorMsg());
 
    }
 
  }
 
}

The ADOdb MySQL driver supports positional parameter substitution and also properly quotes the parameters. SQL parameters are indicated in a query by question marks (?) and you pass the substitution values in an array as a second parameter to the execute() method.

The Insert_ID() method should catch your eye: it returns the value of the AUTO_INCREMENT field from the last executed insert statement.

So far, the tests have proven that attributes can be set, that save() is functional,  and that  the $id attribute has been set to 1. Let’s dig a little more into the database table and verify that the other bookmark attributes have been set properly, too.

class ActiveRecordTestCase extends UnitTestCase {
  // …
  function testNew() {
    $link = new Bookmark;
    $link->url = ‘http://simpletest.org/’;
    $link->name = ‘SimpleTest’;
    $link->description = ‘SimpleTest project homepage’;
    $link->tag = ‘testing’;
    $link->save();
    $this->assertEqual(1, $link->getId());
    // fetch the table as an array of hashes
    $rs = $this->conn->getAll(‘select * from bookmark’);
    $this->assertEqual(1, count($rs), ‘returned 1 row’);
    foreach(array(‘url’, ‘name’, ‘description’, ‘tag’) as $key) {
      $this->assertEqual($link->$key, $rs[0][$key]);

    }
 
}
}

The highlighted code fetches the entire bookmark table. The getAll() method executes the passed query and returns the resultset as an array of row hashes. The assertEqual() line validates that only a single row is present in the result test. The foreach loop compares the attributes of the object $link to fields in the row returned.

The code works, but adding bookmarks this way-setting each attribute by hand-can get a bit tedious. Instead, let’s add a convenience method to the test case to facilitate adding bookmark objects.

The ActiveRecordTestCase::add() method takes four parameters and creates and inserts a new Active Record Bookmark object. And just in case you want to use the new object in tests later, add() returns the created Bookmark object as well.

class ActiveRecordTestCase extends UnitTestCase {
  // …
  function add($url, $name, $description, $tag) {
    $link = new Bookmark;
    $link->url = $url;
    $link->name = $name;
    $link->description = $description;
    $link->tag = $tag;
    $link->save();
    return $link;
  }
}

You can actually write a test method inside the test case to prove this works:

class ActiveRecordTestCase extends UnitTestCase {
  // …
  function testAdd() {
    $this->add(‘http://php.net’, ‘PHP’, 
      ‘PHP Language Homepage’, ‘php’);
    $this->add(‘http://phparch.com’, ‘php|architect’, 
      ‘php|arch site’, ‘php’);
    $rs = $this->conn->execute(‘select * from bookmark’);
    $this->assertEqual(2,$rs->recordCount());
    $this->assertEqual(2,$this->conn->Insert_ID());
  }
}

Now that bookmarks can be created and saved to the database, let’s add a way for an Active Record Bookmark object to easily retrieve data from the database and store the values as instance attributes. A common technique to create an Active Record object is to pass an identifier such as the bookmark ID (or some set of criteria) to its constructor and load the row associated with that ID from the database. Here is a test that demonstrates that:

class ActiveRecordTestCase extends UnitTestCase {
  // …
  function testCreateById() {
    $link = $this->add(
      ‘http://blog.casey-sweat.us/’,
      ‘My Blog’,
      ‘Where I write about stuff’,
      ‘php’);
    $this->assertEqual(1, $link->getId());  
    $link2 = new Bookmark(1);
    $this->assertIsA($link2, ‘Bookmark’);   
    $this->assertEqual($link, $link2);
  }
}

This test passes an ID to the constructor, something the existing tests do not do. Passing an ID has to be optional, because existing tests that create new, empty Bookmark instances must continue to work.

Here’s some code to realize the requirements of the test(s):

class Bookmark {
  
// …
 
const SELECT_BY_ID = ‘select * from bookmark where id = ?’;
  public function __construct($id=false) {
    $this->conn DB::conn();
    if ($id) {
      $rs = $this->conn->execute(
        self::SELECT_BY_ID
        ,array((int)$id));
      if ($rs) {  
        $row = $rs->fetchRow();
        foreach($row as $field => $value) {
          $this->$field = $value;
        }
      } else {
        trigger_error(‘DB Error: ‘.$this->conn->errorMsg());
      }
    }
  }
  // …
}

This constructor allows an $id parameter, which is false by default. If a non-false $id parameter is passed, then Bookmark queries the database for a row in the bookmark table with the corresponding ID. If such a row exists, all of the attributes of the object are set to the values recovered by the database query.

[gp-comments width="770" linklove="off" ]
antalya escort bayan antalya escort bayan