Database Access PatternsDatabase access patterns define the way you will interact with an RDBMS in PHP code. At a simplistic level, this involves determining how and where SQL will appear in the code base. The span of philosophies on this is pretty wide. On one hand is a camp of people who believe that data access is such a fundamental part of an application that SQL should be freely mixed with PHP code whenever a query needs to be performed. On the opposite side are those who feel that SQL should be hidden from developers and that all database access should be contained within deep abstraction layers. I tend not to agree with either of these points of view. The problem with the first is largely a matter of refactoring and reuse. Just as with PHP functions, if you have similar code repeated throughout a code base, for any structural changes that need to be made, you will need to track down every piece of code that might be affected. This creates unmanageable code. The problem I have with the abstraction viewpoint is that abstractions all tend to be lossy. That is, when you wrap something in an abstraction layer, you inevitably lose some of the fine-grained control that you had in the native interface. SQL is a powerful language and is common enough that developers should understand and use it comfortably. Being a centrist on this issue still leaves a good bit of room for variation. The following sections present four database access patternsad hoc queries, the Active Record pattern, the Mapper pattern, and the Integrated Mapper Patternthat apply to both simplistic database query needs and to more complex object-data mapping requirements. Ad Hoc QueriesAd hoc queries are by definition not a pattern, but they can still be useful in many contexts. An ad hoc query is a query that is written to solve a particular problem in a particular spot of code. For example, the following snippet of procedural code to update the country of a user in the users table has an ad hoc character to it: function setUserCountryCode($userid, $countrycode)
{
$dbh = new DB_Mysql_Test;
$query = "UPDATE users SET countrycode = :1 WHERE userid = :2";
$dbh->prepare($query)->execute($countrycode, $userid);
}
Ad hoc queries are not inherently bad. In fact, because an ad hoc query is usually designed to handle a very particular task, it has the opportunity to be tuned (at a SQL level) much more highly than code that serves a more general purpose. The thing to be aware of with ad hoc queries is that they can proliferate through a code base rapidly. You start with a special-purpose ad hoc query here and there, and then suddenly you have 20 different queries spread throughout your code that modify the countrycode column of users. That is a problem because it is very difficult to track down all such queries if you ever need to refactor users. That having been said, I use ad hoc queries quite frequently, as do many professional coders. The trick to keeping them manageable is to keep them in centralized libraries, according to the task they perform and the data they alter. If all your queries that modify users are contained in a single file, in a central place, refactoring and management is made much easier. The Active Record PatternOften you might have classes that directly correspond to rows in a database. With such a setup, it is nice to directly tie together access to the object with the underlying database access. The Active Record pattern encapsulates all the database access for an object into the class itself. The distinguishing factor of the Active Record pattern is that the encapsulating class will have an insert(), an update(), and a delete() method for synchronizing an object with its associated database row. It should also have a set of finder methods to create an object from its database row, given an identifier. Here is an example of an implementation of the User class that corresponds with the user database table we looked at earlier: require_once "DB.inc";
class User {
public $userid;
public $username;
public $firstname;
public $lastname;
public $salutation;
public $countrycode;
public static function findByUsername($username)
{
$dbh = new DB_Mysql_Test;
$query = "SELECT * from users WHERE username = :1";
list($userid) = $dbh->prepare($query)->execute($username)->fetch_row();
if(!$userid) {
throw new Exception("no such user");
}
return new User($userid);
}
public function __construct($userid = false)
{
if(!$userid) {
return;
}
$dbh = new DB_Mysql_Test;
$query = "SELECT * from users WHERE userid = :1";
$data = $dbh->prepare($query)->execute($userid)->fetch_assoc();
foreach( $data as $attr => $value ) {
$this->$attr = $value;
}
}
public function update()
{
if(!$this->userid) {
throw new Exception("User needs userid to call update()");
}
$query = "UPDATE users
SET username = :1, firstname = :2, lastname = :3,
salutation = :4, countrycode = :5
WHERE userid = :6";
$dbh = new DB_Mysql_Test;
$dbh->prepare($query)->execute($this->username, $this->firstname,
$this->lastname, $this->salutation,
$this->countrycode, $this->userid) ;
}
public function insert()
{
if($this->userid) {
throw new Exception("User object has a userid, can't insert");
}
$query = "INSERT INTO users
(username, firstname, lastname, salutation, countrycode)
VALUES(:1, :2, :3, :4, :5)";
$dbh = new DB_Mysql_Test;
$dbh->prepare($query)->execute($this->username, $this->firstname,
$this->lastname, $this->salutation,
$this->countrycode);
list($this->userid) =
$dbh->prepare("select last_insert_id()")->execute()->fetch_row();
}
public function delete()
{
if(!$this->userid) {
throw new Exception("User object has no userid");
}
$query = "DELETE FROM users WHERE userid = :1";
$dbh = new DB_Mysql_Test;
$dbh->prepare($query)->execute($this->userid);
}
}
Using this User class is easy. To instantiate a user by user ID, you pass it into the constructor: $user = new User(1); If you want to find a user by username, you can use the static findByUsername method to create the object: $user = User::findByUsername('george');
Whenever you need to save the object's state permanently, you call the update() method to save its definitions. The following example changes my country of residence to Germany: $user = User::findByUsername('george');
$user->countrycode = 'de';
$user->update();
When you need to create a completely new User object, you instantiate one, fill out its details (except for $userid, which is set by the database), and then call insert on it. This performs the insert and sets the $userid value in the object. The following code creates a user object for Zak Greant: $user = new User; $user->firstname = 'Zak'; $user->lastname = 'Greant'; $user->username = 'zak'; $user->countrycode = 'ca; $user->salutation = 'M.'; $user->insert(); The Active Record pattern is extremely useful for classes that have a simple correspondence with individual database rows. Its simplicity and elegance make it one of my favorite patterns for simple data models, and it is present in many of my personal projects. The Mapper PatternThe Active Record pattern assumes that you are dealing with a single table at a time. In the real world, however, database schemas and application class hierarchies often evolve independently. Not only is this largely unavoidable, it is also not entirely a bad thing: The ability to refactor a database and application code independently of each other is a positive trait. The Mapper pattern uses a class that knows how to save an object in a distinct database schema. The real benefit of the Mapper pattern is that with it you completely decouple your object from your database schema. The class itself needs to know nothing about how it is saved and can evolve completely separately. The Mapper pattern is not restricted to completely decoupled data models. The simplest example of the Mapper pattern is to split out all the database access routines from an Active Record adherent. Here is a reimplementation of the Active Record pattern User class into two classesUser, which handles all the application logic, and UserMapper, which handles moving a User object to and from the database: require_once "DB.inc";
class User {
public $userid;
public $username;
public $firstname;
public $lastname;
public $salutation;
public $countrycode;
public function __construct($userid = false, $username = false,
$firstname = false, $lastname = false,
$salutation = false, $countrycode = false)
{
$this->userid = $userid;
$this->username = $username;
$this->firstname = $firstname;
$this->lastname = $lastname;
$this->salutation = $salutation;
$this->countrycode = $countrycode;
}
}
class UserMapper {
public static function findByUserid($userid)
{
$dbh = new DB_Mysql_Test;
$query = "SELECT * FROM users WHERE userid = :1";
$data = $dbh->prepare($query)->execute($userid)->fetch_assoc();
if(!$data) {
return false;
}
return new User($userid, $data['username'],
$data['firstname'], $data['lastname'],
$data['salutation'], $data['countrycode']);
}
public static function findByUsername($username)
{
$dbh = new DB_Mysql_Test;
$query = "SELECT * FROM users WHERE username = :1";
$data = $dbh->prepare($query)->execute($username)->fetch_assoc();
if(!$data) {
return false;
}
return new User($data['userid'], $data['username'],
$data['firstname'], $data['lastname'],
$data['salutation'], $data['countrycode']);
}
public static function insert(User $user)
{
if($user->userid) {
throw new Exception("User object has a userid, can't insert");
}
$query = "INSERT INTO users
(username, firstname, lastname, salutation, countrycode)
VALUES(:1, :2, :3, :4, :5)";
$dbh = new DB_Mysql_Test;
$dbh->prepare($query)->execute($user->username, $user->firstname,
$user->lastname, $user->salutation,
$user->countrycode);
list($user->userid) =
$dbh->prepare("select last_insert_id()")->execute()->fetch_row();
}
public static function update(User $user)
{
if(!$user->userid) {
throw new Exception("User needs userid to call update()");
}
$query = "UPDATE users
SET username = :1, firstname = :2, lastname = :3,
salutation = :4, countrycode = :5
WHERE userid = :6";
$dbh = new DB_Mysql_Test;
$dbh->prepare($query)->execute($user->username, $user->firstname,
$user->lastname, $user->salutation,
$user->countrycode, $user->userid);
}
public static function delete(User $user)
{
if(!$user->userid) {
throw new Exception("User object has no userid");
}
$query = "DELETE FROM users WHERE userid = :1";
$dbh = new DB_Mysql_Test;
$dbh->prepare($query)->execute($userid);
}
}
User knows absolutely nothing about its corresponding database entries. If you need to refactor the database schema for some reason, User would not have to be changed; only UserMapper would. Similarly, if you refactor User, the database schema does not need to change. The Mapper pattern is thus similar in concept to the Adaptor pattern that you learned about in Chapter 2, "Object-Oriented Programming Through Design Patterns": It glues together two entities that need not know anything about each other. In this new setup, changing my country back to the United States would be done as follows: $user = UserMapper::findByUsername('george');
$user->countrycode = 'us';
UserMapper::update($user);
Refactoring with the Mapper pattern is easy. For example, consider your options if you want to use the name of the user's country as opposed to its ISO code in User. If you are using the Active Record pattern, you have to either change your underlying users table or break the pattern by adding an ad hoc query or accessor method. The Mapper pattern instead instructs you only to change the storage routines in UserMapper. Here is the example refactored in this way: class User {
public $userid;
public $username;
public $firstname;
public $lastname;
public $salutation;
public $countryname;
public function __construct($userid = false, $username = false,
$firstname = false, $lastname = false,
$salutation = false, $countryname = false)
{
$this->userid = $userid;
$this->username = $username;
$this->firstname = $firstname;
$this->lastname = $lastname;
$this->salutation = $salutation;
$this->countryname = $countryname;
}
}
class UserMapper {
public static function findByUserid($userid)
{
$dbh = new DB_Mysql_Test;
$query = "SELECT * FROM users u, countries c
WHERE userid = :1
AND u.countrycode = c.countrycode";
$data = $dbh->prepare($query)->execute($userid)->fetch_assoc();
if(!$data) {
return false;
}
return new User($userid, $data['username'],
$data['firstname'], $data['lastname'],
$data['salutation'], $data['name']);
}
public static function findByUsername($username)
{
$dbh = new DB_Mysql_Test;
$query = "SELECT * FROM users u, countries c
WHERE username = :1
AND u.countrycode = c.countrycode";
$data = $dbh->prepare($query)->execute($username)->fetch_assoc();
if(!$data) {
return false;
}
return new User($data['userid'], $data['username'],
$data['firstname'], $data['lastname'],
$data['salutation'], $data['name']);
}
public static function insert(User $user)
{
if($user->userid) {
throw new Exception("User object has a userid, can't insert");
}
$dbh = new DB_Mysql_Test;
$cc_query = "SELECT countrycode FROM countries WHERE name = :1";
list($countrycode) =
$dbh->prepare($cc_query)->execute($user->countryname)->fetch_row();
if(!$countrycode) {
throw new Exception("Invalid country specified");
}
$query = "INSERT INTO users
(username, firstname, lastname, salutation, countrycode)
VALUES(:1, :2, :3, :4, :5)";
$dbh->prepare($query)->execute($user->username, $user->firstname,
$user->lastname, $user->salutation,
$countrycode) ;
list($user->userid) =
$dbh->prepare("select last_insert_id()")->execute()->fetch_row();
}
public static function update(User $user)
{
if(!$user->userid) {
throw new Exception("User needs userid to call update()");
}
$dbh = new DB_Mysql_Test;
$cc_query = "SELECT countrycode FROM countries WHERE name = :1";
list($countrycode) =
$dbh->prepare($cc_query)->execute($user->countryname)->fetch_row();
if(!$countrycode) {
throw new Exception("Invalid country specified");
}
$query = "UPDATE users
SET username = :1, firstname = :2, lastname = :3,
salutation = :4, countrycode = :5
WHERE userid = :6";
$dbh->prepare($query)->execute($user->username, $user->firstname,
$user->lastname, $user->salutation,
$countrycode, $user->userid);
}
public static function delete(User $user)
{
if(!$user->userid) {
throw new Exception("User object has no userid");
}
$query = "DELETE FROM users WHERE userid = :1";
$dbh = new DB_Mysql_Test;
$dbh->prepare($query)->execute($userid);
}
}
Notice that User is changed in the most naive of ways: The now deprecated $countrycode attribute is removed, and the new $countryname attribute is added. All the work is done in the storage methods. findByUsername() is changed so that it pulls not only the user record but also the country name for the user's record from the countries lookup table. Similarly insert() and update() are changed to perform the necessary work to find the country code for the user's country and update accordingly. The following are the benefits of the Mapper pattern:
The drawback of the Mapper pattern is the amount of infrastructure it requires. To adhere to the pattern, you need to manage an extra class for mapping each complex data type to its database representation. This might seem like overkill in a Web environment. Whether that complaint is valid really depends on the size and complexity of the application. The more complex the objects and data mappings are and the more often the code will be reused, the greater the benefit you will derive from having a flexible albeit large infrastructure in place. The Integrated Mapper PatternIn the Active Record pattern, the object is database awarethat is, it contains all the methods necessary to modify and access itself. In the Mapper pattern, all this responsibility is delegated to an external class, and this is a valid problem with this pattern in many PHP applications. In a simple application, the additional layer required for splitting out the database logic into a separate class from the application logic may be overkill. It incurs overhead and makes your code base perhaps needlessly complex. The Integrated Mapper pattern is a compromise between the Mapper and Active Record patterns that provides a loose coupling of the class and its database schema by pulling the necessary database logic into the class. Here is User with an Integrated Mapper pattern: class User {
public $userid;
public $username;
public $firstname;
public $lastname;
public $salutation;
public $countryname;
public function __construct($userid = false)
{
$dbh = new DB_Mysql_Test;
$query = "SELECT * FROM users u, countries c
WHERE userid = :1
AND u.countrycode = c.countrycode";
$data = $dbh->prepare($query)->execute($userid)->fetch_assoc();
if(!$data) {
throw new Exception("userid does not exist");
}
$this->userid = $userid;
$this->username = $data['username'];
$this->firstname = $data['firstname'];
$this->lastname = $data['lastname'];
$this->salutation = $data['salutation'];
$this->countryname = $data['name'];
}
public static function findByUsername($username)
{
$dbh = new DB_Mysql_Test;
$query = "SELECT userid FROM users u WHERE username = :1";
list($userid) = $dbh->prepare($query)->execute($username)->fetch_row();
if(!$userid) {
throw new Exception("username does not exist");
}
return new User($userid);
}
public function update()
{
if(!$this->userid) {
throw new Exception("User needs userid to call update()");
}
$dbh = new DB_Mysql_Test;
$cc_query = "SELECT countrycode FROM countries WHERE name = :1";
list($countrycode) =
$dbh->prepare($cc_query)->execute($this->countryname)->fetch_row();
if(!$countrycode) {
throw new Exception("Invalid country specified");
}
$query = "UPDATE users
SET username = :1, firstname = :2, lastname = :3,
salutation = :4, countrycode = :5
WHERE userid = :6";
$dbh->prepare($query)->execute($this->username, $this->firstname,
$this->lastname, $this->salutation,
$countrycode, $this->userid);
}
/* update and delete */
// ...
}
This code should look very familiar, as it is almost entirely a merge between the Active Record pattern User class and the database logic of UserMapper. In my mind, the decision between making a Mapper pattern part of a class or an external entity is largely a matter of style. In my experience, I have found that while the elegance of the pure Mapper pattern is very appealing, the ease of refactoring brought about by the identical interface of the Active Record and Integrated Mapper patterns make them my most common choices. |