The Active Record Pattern, concluded

This article, the second 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).

Testing Database Failure

Databases usually just work, but failure is not unheard of. To make sure your code operates correctly under failure conditions, let’s simulate a failure using a Mock Object (see Chapter 6 — The Mock Object Pattern), which stands in for the connection object. 

 


Mock::generate(‘ADOConnection’);
class ActiveRecordTestCase extends UnitTestCase {
  //…
  function testDbFailure() {
    $conn = new MockADOConnection($this);
    $conn->expectOnce(‘execute’, array(‘*’,’*’));
    $conn->setReturnValue(‘execute’,false);
    $conn->expectOnce(‘errorMsg’);
    $conn->setReturnValue(‘errorMsg’,
      ‘The database has exploded!!!!’);
  }
}

This code calls Mock::generate() to create a MockADOConnection class, creates an instance of the mock connection, sets up some basic return values to indicate failure, and defines some expectations about what’s to be called in these circumstances.

However, because the Bookmark constructor makes a call to the static DB:conn() method to retrieve the database connection, it’s difficult to inject the mock connection into that code. There are several possible workarounds: add a method to change $this->conn, add an optional parameter to each method, or add a parameter to the constructor. Let’s opt for the latter: add an optional connection class parameter to the Bookmark constructor:

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

Now new Bookmark works as normal, but new Bookmark(1, $connection) uses the $connection object instead of the normal ADOConnection object.

With that code in place, you can now easily replace the “normal” database connection object with a MockADOconnection and verify the results of a “database failure.”

class ActiveRecordTestCase extends UnitTestCase { 
  // …
  function testDbFailure() {
    $conn = new MockADOConnection($this);
    $conn->expectOnce(‘execute’, array(‘*’,’*’));
    $conn->setReturnValue(‘execute’,false);
    $conn->expectOnce(‘errorMsg’);
    $conn->setReturnValue(‘errorMsg’,
      ‘The database has exploded!!!!’);    
    $link = new Bookmark(1,$conn);
    $this->assertErrorPattern(‘/exploded/i’);
    $conn->tally();
}

{mospagebreak title=Active Record Instance ID}

In the previous example, most of the attributes are public; however, the ID of the bookmark is protected to avoid accidents changing its value (this would be problematic when you wanted to later update the bookmark). Since $id is protected, add an accessor method to retrieve it from the Bookmark.

class Bookmark {
  protected $id;
  //…
  public function getId() {
    return $this->id;
  }
}

How do you test this?

class ActiveRecordTestCase extends UnitTestCase {
  // …
  function testGetId() {
    $this->add(‘http://php.net’, ‘PHP’,
      ‘PHP Language Homepage’, ‘php’);
    // second bookmark, id=2
    $link = $this->add(‘http://phparch.com’,
      ‘php|architect’, ‘php|arch site’, ‘php’);
    $this->assertEqual(2, $link->getId());
  }
} 

Immediately above, add() persists several bookmarks and verifies that the latter of the two matches.

So far, so good, but what if you want to verify the database entry based on a different criteria than the bookmark ID? How can  you make sure the correct ID from the database is being returned? A good technique is to SELECT from the database using a known attribute and verify the ID from the returned row. Here’s a test using this methodology:

class ActiveRecordTestCase extends UnitTestCase {
  // …
  function testGetId() {
    $this->add(‘http://php.net’, ‘PHP’, 
      ‘PHP Language Homepage’, ‘php’);
    // second bookmark, id=2
    $link = $this->add(‘http://phparch.com’,
      ‘php|architect’, ‘php|arch site’, ‘php’);
    $this->assertEqual(2, $link->getId());
    $alt_test = $this->conn->getOne(
      “select id from bookmark where url = ‘http://phparch.com’”);
    $this->assertEqual(2, $alt_test);
    //alternatively
    $this->assertEqual($link->getId(), $alt_test);
  }
} 

Notice that this test resembles the SQL you might execute manually to verify the insertion of the data into the bookmark table. By coding this as a test, rather than simply performing it once by hand, you can continue to verify it is taking place each time you run the tests.

{mospagebreak title=Searching for Records}

At the moment, a Bookmark can be stored in a database and can be (re)created by retrieving the database row that matches the bookmark’s ID.  But what happens—as is usually the case—when the ID is not known or you want to search the database for a more pertinent value, such as a partial name or a URL.  A common solution is to add “finder” methods. 

For example, you might want a  findByUrl() method to find Bookmarks similar to the parameter passed to the method. Here’s that intention expressed as a test:

class ActiveRecordTestCase extends UnitTestCase {
  // …
  function testFindByUrl() {
    $this->add(‘http://blog.casey-sweat.us/’, ‘My Blog’,
      ‘Where I write about stuff’, ‘php’);
    $this->add(‘http://php.net’, ‘PHP’, 
      ‘PHP Language Homepage’, ‘php’);
    $this->add(‘http://phparch.com’, ‘php|architect’, 
      ‘php|arch site’, ‘php’);
    $result = Bookmark::findByUrl(‘php’);
    $this->assertIsA($result, ‘array’);
    $this->assertEqual(2, count($result));
    $this->assertEqual(2, $result[0]->getId());
    $this->assertEqual(‘php|architect’, $result[1]->name);
  }
}

The test creates some data, searches for rows that contain “php” somewhere in the URL, and then verifies characteristics of the returned array of Bookmark objects. FindByUrl() is a static method, because you want Bookmark objects, but do not yet have an instance of the Bookmark class to work with. (Alternatively, you could move these “finder” methods to an object of their own, but for now the finder methods are a part of the Active Record Bookmark class.)

Here’s some code to realize the requirements expressed by the test:

class Bookmark {
  // …
  const SELECT_BY_URL = “
    select id
    from bookmark
    where url like ?”;
  public static function findByUrl($url) {
    $rs = DB::conn()->execute(
      self::SELECT_BY_URL
      ,array(“%$url%”));
    $ret = array();
    if ($rs) {
      foreach ($rs->getArray() as $row) {
        $ret[] = new Bookmark($row[‘id’]);
      }
    }
    return $ret;
  }
}

{mospagebreak title=Updating Records}

The Create and Read portions of CRUD are complete; what about Update? It makes sense to use save() to update an Active Record object, but as it is now, save() only handles INSERT statements. To recap, save() looks like this:

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());
    }
  }
}

However, after you already have a valid instance, you would rather see something like:

class Bookmark {
  // …
  const UPDATE_SQL = “
    update bookmark set
      url = ?,
      name = ?,
      description = ?,
      tag = ?,
      updated = now()
    where id = ?
    “;
  public function save() {
    $this->conn->execute(
      self::UPDATE_SQL
      ,array(
        $this->url,
        $this->name,
        $this->description,
        $this->tag,
        $this->id));
  }
}

To differentiate between INSERT and UPDATE, you need to detect if a bookmark is new or if it’s been loaded from the database.  

First, refactor the two “versions” of save() into individual protected methods with the descriptive names insert() and update().

class Bookmark {
  // …
  protected function insert() {
    $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();
    }
  }
  protected function update() {
      $this->conn->execute(
        self::UPDATE_SQL
        ,array(
          $this->url,
          $this->name,
          $this->description,
          $this->tag,
          $this->id));
  }
}

Now you can change save() to look at this info:

class Bookmark {
  const NEW_BOOKMARK = -1;
  protected $id = Bookmark::NEW_BOOKMARK;
  // …
  public function save() {
    if ($this->id == Bookmark::NEW_BOOKMARK) {
      $this->insert();
    } else {
      $this->update();
    }
  }
}

Just one last issue: timestamps change in the database whenever you insert or update a record. There is no other way to keep an accurate timestamp in the Bookmark other than making another trip to the database to retrieve it. Since this applies to either inserts or updates, change the Active Record class to always update the timestamp before leaving the save() method in order to prevent the latter from getting out of sync.

class Bookmark {
  // …
  public function save() {
    if ($this->id == self::NEW_BOOKMARK) {
      $this->insert();
    } else {
      $this->update();
    }
    $this->setTimeStamps();
  }
  protected function setTimeStamps() {
    $rs = $this->conn->execute(
      self::SELECT_BY_ID
      ,array($this->id));
    if ($rs) {
      $row = $rs->fetchRow();
      $this->created = $row[‘created’];
      $this->updated = $row[‘updated’];
    }
  }
}

Bookmark gets to the heart of the ActiveRecord pattern: save() knows the SQL statement required to update or insert into the database table, knows the object’s current state, and can assemble the needed parameter substitution array from the object’s own attributes.  Let’s test it:

class ActiveRecordTestCase extends UnitTestCase {
  // …
  function testSave() {
    $link = Bookmark::add(
      ‘http://blog.casey-sweat.us/’,
      ‘My Blog’,
      ‘Where I write about stuff’,
      ‘php’);
    $link->description = 
      ‘Where I write about PHP, Linux and other stuff’;
    $link->save();
    $link2 = Bookmark($link->getId());
    $this->assertEqual($link->getId(), $link2->getId());
    $this->assertEqual($link->created, $link2->updated);
  }
}

For now, let’s skip how to implement DELETE. There is an example in Chapter 16—The Data Mapper Pattern, but you can easily derive it from the insert() and update() methods.

{mospagebreak title=Issues}

The Active Record pattern is simple in both concept and execution and probably represents what most initial attempts to refactor from procedural coding to object-oriented programming would look like. It’s nice to have all of your SQL code grouped into a single location and the Active Record pattern gives you a nice way to couple business logic with database access to persist the object.

The example in this chapter used an actual database to develop and test the code. Another way to test simple database code is to use Mock Objects (see Chapter 6) to completely simulate the database connection. Unfortunately though, this approach does not scale. SQL is a complex language and mocking individual statements tightly couples tests with database specifics.  Using freshly-created, actual tables provide a higher degree of comfort, without the brittle effects of Mocking SQL.

If there’s a downside to the Active Record pattern, it’s complexity. An Active Record class can grow quite quickly—it attracts features like a magnet. For example, the Bookmark classes only included a findById() method, but you’d  likely also want findByUrl(), findByDescription(), findByGroup(), findRecentlyCreated(), and so on.

Another issue, which is possible to see in the testing of the save() method, is that objects can become “duplicated.” For example, $link and $link2 in the  test case aren’t the same objects, though they both refer to the same bookmark ID. You could test this explicitly also:

class ActiveRecordTestCase extends UnitTestCase {
  // …
  function testSave() {
    // …
    $this->assertNotIdentical($link, $link2);
  }
}

If it’s important to work around this issue, you might want to add an internal Registry (see Chapter 5) to make sure all instances of the object returned by Bookmark(1) are in fact the same object. Because you’re actually using the new operator to create the objects instead of a Factory method, you might have to restructure the Bookmark class as a Proxy (see Chapter 11) to the actual Active Record class to really pull this off.

Another aspect of the Active Record pattern is that it is designed to work with data one row at a time.  This is fairly typical of “admin” screens for applications where you might be editing an article, a link, a comment or any other row from a database, but a good deal of web pages deal with result sets or combinations of rows, which is more the domain of our next chapter—The Table Data Gateway Pattern.

[gp-comments width="770" linklove="off" ]

chat sex hikayeleri