PHP Service Layers: Database Adapters

In this third part of a series, I will extend the functionality of the sample application by adding an easily customizable data access layer to it. Implementing this structure will be a fairly straightforward process. Its building blocks will include a simple segregated interface along with a basic MySQL adapter.

While the term may make you nervous, the truth is a service is nothing but an abstraction layer which permits you to interface application logic with different client layers. These client layers can range from several front-ends, to the so-called action controllers that can be found in many MVC frameworks available today.

Put in a simpler way, a service is an enterprise-level pattern, and as such, it’s both language and platform agnostic. This means that it can be implemented in most programming languages, including PHP, even though in general it’s not considered by many to be a true “enterprisey” contender. Of course, abstaining from the joys of creating a functional service layer only because there exist some biased opinions is a lame excuse. Therefore, in previous chapters of this series I started building a basic web application, whose functionality is aimed at performing CRUD operation on some user entities through a basic service.

Admittedly, in its current state the application doesn’t do anything especially useful. So far, I’ve only implemented its domain and mapping layers. However, this is about to change. In this article I’ll be adding to it a whole new layer, tasked with persisting domain objects in the underlying storage mechanism — in this case, a MySQL database, but it could be a web service, etc. As you’ll see in a moment, this data access layer will be made up of a segregated interface and a basic MySQL abstraction class, which you’ll be able to customize at will.

To sum up: are you ready to continue learning how to progressively implement a user service layer in PHP? Then let’s get started.

Interfacing domain objects with the persistence layer: a quick look at the previous data mappers

As usual, before I start developing the persistence layer corresponding to the sample application mentioned in the introduction, I’d like to spend a few moments reviewing the topics covered in the preceding installment of the series. In that tutorial I implemented the application’s mapping layer, which was comprised of a simple segregated interface and a couple of data mappers.

Well, here’s the definition of the pertinent interface, which makes extending the mapping layer a breeze:

(MyApplication/Mapper/DataMapperInterface.php)

<?php

namespace MyApplicationMapper;

interface DataMapperInterface
{
    public function findById($id);

    public function findAll();

    public function search($criteria);

    public function insert($entity);

    public function update($entity);

    public function delete($entity);
}

The contract defined by the above interface speaks for itself. So let’s move on and take a look at the following abstract class, which encapsulates the functionality required to map generic entities:

(MyApplication/Mapper/AbstractDataMapper.php)

<?php

namespace MyApplicationMapper;
use MyApplicationDatabase,
    MyApplicationCollection;

abstract class AbstractDataMapper implements DataMapperInterface
{
    protected $_adapter;
    protected $_collection;
    protected $_entityTable;
    protected $_entityClass;

    /**
     * Constructor
     */
    public function  __construct(DatabaseDatabaseAdapterInterface $adapter, CollectionAbstractCollection $collection, array $entityOptions = array())
    {
        $this->_adapter = $adapter;
        $this->_collection = $collection;
        if (isset($entityOptions['entityTable'])) {
            $this->setEntityTable($entityOptions['entityTable']);
        }
        if (isset($entityOptions['entityClass'])) {
            $this->setEntityClass($entityOptions['entityClass']);
        }
    }

    /**
     * Get the database adapter
     */
    public function getAdapter()
    {
        return $this->_adapter;
    }

    /**
     * Get the collection
     */
    public function getCollection()
    {
        return $this->_collection;
    }

    /**
     * Set the entity table
     */
    public function setEntityTable($entityTable)
    {
        if (!is_string($table) || empty ($entityTable)) {
            throw new DataMapperException(‘The specified entity table is invalid.’);
        }
        $this->_entityTable = $entityTable;
    }

    /**
     * Get the entity table
     */
    public function getEntityTable()
    {
        return $this->_entityTable;
    }

    /**
     * Set the entity class
     */
    public function setEntityClass($entityClass)
    {
        if (!class_exists($entityClass)) {
            throw new DataMapperException(‘The specified entity class is invalid.’);
        }
        $this->_entityClass = $entityClass;
    }

    /**
     * Get the entity class
     */
    public function getEntityClass()
    {
        return $this->_entityClass;
    }

    /**
     * Find an entity by its ID
     */
    public function findById($id)
    {
        $this->_adapter->select($this->_entityTable, "id = $id");
        if ($data = $this->_adapter->fetch()) {
            return new $this->_entityClass($data);
        }
        return null;
    }

    /**
     * Find all the entities
     */
    public function findAll()
    {
        $this->_adapter->select($this->_entityTable);
        while ($data = $this->_adapter->fetch($this->_entityTable)) {
            $this->_collection[] = new $this->_entityClass($data);
        }
        return $this->_collection;
    }

    /**
     * Find all the entities that match the specified criteria
     */
    public function search($criteria)
    {
        $this->_adapter->select($this->_entityTable, $criteria);
        while ($data = $this->_adapter->fetch()) {
            $this->_collection[] = new $this->_entityClass($data);
        }
        return $this->_collection;
    }
   
    /**
     * Insert a new row in the table corresponding to the specified entity
     */
    public function insert($entity)
    {
        if ($entity instanceof $this->_entityClass) {
            return $this->_adapter->insert($this->_entityTable, $entity->toArray());
        }
        throw new DataMapperException(‘The specified entity is not allowed for this mapper.’);
    }

    /**
     * Update the row in the table corresponding to the specified entity
     */
    public function update($entity)
    {
        if ($entity instanceof $this->_entityClass) {
            $data = $entity->toArray();
            $id = $entity->id;
            unset($data['id']);
            return $this->_adapter->update($this->_entityTable, $data, "id = $id");
        }
        throw new DataMapperException(‘The specified entity is not allowed for this mapper.’);
    }

    /**
     * Delete the row in the table corresponding to the specified entity or ID
     */
    public function delete($id)
    {
        if ($id instanceof $this->_entityClass) {
            $id = $id->id;
        }
        return $this->_adapter->delete($this->_entityTable, "id = $id");
    }
}

 

(MyApplication/Mapper/DataMapperException.php)

<?php

namespace MyApplicationMapper;

class DataMapperException extends Exception{}

From the above code fragment, it’s clear to see that all the “AbstractMapper” class does is fetch, save and remove entities from the storage layer. While this is all well and good, my purpose here is to implement a functional user service. Therefore, it’s mandatory to spawn a subclass from the abstract parent that works exclusively with user entities. The one shown below does exactly that:

(MyApplication/Mapper/UserMapper.php)

<?php

namespace MyApplicationMapper;
use MyApplicationDatabase,
    MyApplicationCollection;

class UserMapper extends AbstractDataMapper
{
    protected $_entityClass = ‘MyApplicationEntityUser’;
    protected $_entityTable = ‘users’; 
   
    /**
     * Constructor
     */
     public function __construct(DatabaseDatabaseAdapterInterface $adapter, CollectionUserCollection $collection)
     {
         parent::__construct($adapter, $collection);
     }  
}

That was much easier to accomplish than one might think, wasn’t it? In a few easy steps, I managed to implement an extendable mapping layer, which moves data between the domain model and the underlying database.

However, there’s a couple of things that need to be done to persist the model in the storage mechanism. First, we need to build the database adapter injected in the mappers’ constructor; and second, we need to develop the other dependency, namely a collection handling class.

But first things first, right? Thus, in the following section I’ll be defining the contract implemented by the former, which logically will be represented by an interface.

To learn more about this process, click on the link below and keep reading.

{mospagebreak title=Building a simple data access layer}

I want to make the previous mapping layer flexible enough to switch over different database adapters at runtime. The easiest way to achieve this is by coding an interface, instead of relying heavily on a concrete implementation. 

In this particular case, the interface in question is the same one used here (http://www.devshed.com/showblog/41685/Roll-Your-Own-Repository-in-PHP-the-Data-Access-Layer/) and the contract that it defines is the following:

(MyApplication/Database/DatabaseAdapterInterface.php)

<?php

namespace MyApplicationDatabase;

interface DatabaseAdapterInterface
{
    function connect();
   
    function disconnect(); 
   
    function query($query);
   
    function fetch(); 
   
    function select($table, $conditions, $fields, $order, $limit, $offset);
   
    function insert($table, array $data);
   
    function update($table, array $data, $conditions);
   
    function delete($table, $conditions);
   
    function getInsertId();
   
    function countRows();
   
    function getAffectedRows();
}

Even though the use of interfaces is still somewhat limited in the PHP field (especially when considering the weakly-typed nature of the language), the one above is really useful for implementing a pluggable structure that permits you to inject different types of database adapters into the mappers previously created.

With this interface defining the contract for generic adapters, it’s fairly simple to create one that works specifically with MySQL. To prove this, in the following segment I’ll be creating a concrete adapter, so you can see how it fits into the structure of this sample application. 

Data access layer final touches: implementing a basic MySQL adapter

Constructing an adapter capable of performing some typical operations in a MySQL database is simple. One must only create an implementer of the earlier “DatabaseAdapterInterface” interface and the job is done.

Since I want to keep things clear and graspable, the MySQL adapter that I’ll be using is quite similar to the one coded in some previous articles published here at the Developer Shed Network. Therefore, the logic of the following class should be quite familiar to you. Check it out: 

(MyApplication/Database/MysqlAdapter.php)

<?php

namespace MyApplicationDatabase;

class MysqlAdapter implements DatabaseAdapterInterface
{
    protected $_config = array();
    protected $_link;
    protected $_result;
   
    /**
     * Constructor
     */
    public function __construct(array $config)
    {
        if (count($config) !== 4) {
            throw new MySQLAdapterException(‘Invalid number of connection parameters.’);  
        }
        $this->_config = $config;
    }
   
    /**
     * Connect to MySQL
     */
    public function connect()
    {
        // connect only once
        if ($this->_link !== null) {
            return $this->_link;
        }
        list($host, $user, $password, $database) = $this->_config;
        if (($this->_link = @mysqli_connect($host, $user, $password, $database))) {
            unset($host, $user, $password, $database);
            return $this->_link;
        }
        throw new MySQLAdapterException(‘Error connecting to the server : ‘ . mysqli_connect_error());
    }

    /**
     * Execute the specified query
     */
    public function query($query)
    {
        if (!is_string($query) || empty($query)) {
            throw new MySQLAdapterException(‘The specified query is not valid.’);  
        }
        // lazy connect to MySQL
        $this->connect();
        if ($this->_result = mysqli_query($this->_link, $query)) {
            return $this->_result;
        }
        throw new MySQLAdapterException(‘Error executing the specified query ‘ . $query . mysqli_error($this->_link));
    }
   
    /**
     * Perform a SELECT statement
     */
    public function select($table, $where = ”, $fields = ‘*’, $order = ”, $limit = null, $offset = null)
    {
        $query = ‘SELECT ‘ . $fields . ‘ FROM ‘ . $table
               . (($where) ? ‘ WHERE ‘ . $where : ”)
               . (($limit) ? ‘ LIMIT ‘ . $limit : ”)
               . (($offset && $limit) ? ‘ OFFSET ‘ . $offset : ”)
               . (($order) ? ‘ ORDER BY ‘ . $order : ”);
        $this->query($query);
        return $this->countRows();
    }
   
    /**
     * Perform an INSERT statement
     */ 
    public function insert($table, array $data)
    {
        $fields = implode(‘,’, array_keys($data));
        $values = implode(‘,’, array_map(array($this, ‘quoteValue’), array_values($data)));
        $query = ‘INSERT INTO ‘ . $table . ‘ (‘ . $fields . ‘) ‘ . ‘ VALUES (‘ . $values . ‘)’;
        $this->query($query);
        return $this->getInsertId();
    }
   
    /**
     * Perform an UPDATE statement
     */
    public function update($table, array $data, $where = ”)
    {
        $set = array();
        foreach ($data as $field => $value) {
            $set[] = $field . ‘=’ . $this->quoteValue($value);
        }
        $set = implode(‘,’, $set);
        $query = ‘UPDATE ‘ . $table . ‘ SET ‘ . $set
               . (($where) ? ‘ WHERE ‘ . $where : ”);
        $this->query($query);
        return $this->getAffectedRows(); 
    }
   
    /**
     * Perform a DELETE statement
     */
    public function delete($table, $where = ”)
    {
        $query = ‘DELETE FROM ‘ . $table
               . (($where) ? ‘ WHERE ‘ . $where : ”);
        $this->query($query);
        return $this->getAffectedRows();
    }
   
    /**
     * Escape the specified value
     */
    public function quoteValue($value)
    {
        $this->connect();
        if ($value === null) {
            $value = ‘NULL’;
        }
        else if (!is_numeric($value)) {
            $value = "’" . mysqli_real_escape_string($this->_link, $value) . "’";
        }
        return $value;
    }
   
    /**
     * Fetch a single row from the current result set (as an associative array)
     */
    public function fetch()
    {
        if ($this->_result !== null) {
            if (($row = mysqli_fetch_array($this->_result, MYSQLI_ASSOC)) !== false) {
                return $row;
            }
            $this->freeResult();
            return false;
        }
        return null;
    }

    /**
     * Get the insertion ID
     */
    public function getInsertId()
    {
        return $this->_link !== null ?
               mysqli_insert_id($this->_link) :
               null;
    }
   
    /**
     * Get the number of rows returned by the current result set
     */ 
    public function countRows()
    {
        return $this->_result !== null ?
               mysqli_num_rows($this->_result) :
               0;
    }
   
    /**
     * Get the number of affected rows
     */
    public function getAffectedRows()
    {
        return $this->_link !== null ?
               mysqli_affected_rows($this->_link) :
               0;
    }
   
    /**
     * Free up the current result set
     */
    public function freeResult()
    {
        if ($this->_result !== null) {
            mysqli_free_result($this->_result);
            return true;
        }
        return false;
    }
   
    /**
     * Close explicitly the database connection
     */
    public function disconnect()
    {
        if ($this->_link !== null) {
            mysqli_close($this->_link);
            $this->_link = null;
            return true;
        }
        return false;
    }
   
    /**
     * Close automatically the database connection when the instance of the class is destroyed
     */
    public function __destruct()
    {
        $this->disconnect();
    }
}

 

(MyApplication/Database/MysqlAdapterException.php)

<?php

namespace MyApplicationDatabase;

class MysqlAdapterException extends Exception{}

As you can see from the code above, the “MysqlAdapter” class is nothing but a basic adapter that can be used for performing common CRUD SQL statements against a specified database table, counting and fetching rows in result sets, and so forth. The adapter itself is optional and can be easily replaced with the database class included with the framework of your choice, as long as you make it an implementer of the previous interface. So, if you want use that option, go for it.

With that said, it’s time to summarize what we’ve achieved so far. The domain and data access layers of this example application are already up and running. But, can we say the same about the mapping layer? Not yet, unfortunately. The mappers need the functionality of a collection handling class to function as expected.

But fear not, as the development of this class will be discussed in detail in the upcoming tutorial.  

Final Thoughts

In this third episode of the series, I extended the functionality of this sample application by adding to it an easily customizable data access layer. As you just saw, the implementation of this structure was a fairly straightforward process; its building blocks were a simple segregated interface along with a basic MySQL adapter. 

Does this mean that we’re ready to start implementing the highly-desired user service? Well, not so fast. As I just said, the mappers created before take an additional dependency, which is tasked with handling collections of entities. Therefore, it’s necessary first to bring this collaborator to life before seeing the service in action.

This will be the topic that I’ll be covering in the next tutorial of the series, so don’t miss it!

Google+ Comments

Google+ Comments