Previously, I created a php database adapter that calls mysql stored procedures as if they were local php functions. Now I want to refactor each of the features of the DB adapter into a decorator design pattern.

The core database adapter encapsulates the connection config and provides lazy-loading connections for both write-master and read-slave. I want to create decorators for the magic SQL and magic stored-procedure components.

The following unit-test demonstrates the desired functionality.

public function test_decorator() {
    $db = new XDB\Adapter();
    $proc = new XDB\MagicProc($db);
    $sql = new XDB\MagicSQL($db);

    $users = $sql('SELECT username FROM foobar_users LIMIT 7');
    $this->assertEquals(7,count($users));

    $bbob = $sql('SELECT * FROM foobar_users WHERE username = ?', array('bbob'));
    $this->assertEquals('bbob',$bbob[0]['username']);

    $page1 = $proc->usersByPage(1,9);
    $this->assertEquals(9,count($page1));
}

In this case, the $db, $sql, and $proc objects can all be used directly as PDO connections — in reality all of these objects are using the same connection (or connections since the adapter manages a read-slave and a write-master).

Decorator

I’ll start with an abstract Decorator that persists the XDB\Adapter:

namespace XDB;
abstract class Decorator {
    protected $db;
    public function __construct(Adapter $db) {
        $this->db = $db;
    }
}

The components extend the Decorator class and add the specific functionality. Here is the magic SQL functionality:

namespace XDB;
class MagicSQL extends Decorator {

    /**
     * MAGIC, object invocation
     *
     * $array1 = $db('SELECT * FROM foo');
     * $array2 = $db('SELECT * FROM foo WHERE bar = ?', $params);
     **/
    public function __invoke($sql, $params = false) {
        $conn = $this->db->connection($sql);
        $stmt = $conn->prepare($sql);
        $params ? $stmt->execute($params) : $stmt->execute();
        return $stmt->fetchAll();
    }
}

Notice that the connection is accessed through $this->db, which is the XDB\Adapter object that was passed into the Decorator constructor.

Here is the magic stored procedure functionality:

namespace XDB;
class MagicProc extends Decorator {

    /**
     * MAGIC, call stored procedure
     *
     * e.g., to call a stored procedure 'getUserById'
     * $user = $db->getUserById($id);
     **/
    public function __call($method, $params) {
        $conn = $this->db->connection();
        if (!method_exists($conn, $method)) {
            $bind_params = trim( str_repeat('?,',count($params)), ',');
            $stmt = $conn->prepare("CALL $method($bind_params)");
            $params ? $stmt->execute($params) : $stmt->execute();
            return $stmt->fetchAll();
        } else {
            return $this->db->_proxy($method, $params);
        }
    }
}

Notice that $this->db (from the Decorator constructor) is accessed to call the _proxy() method. This preserves the PDO proxy interface from XDB\Adapter enabling the decorated object to behave exactly as XDB\Adapter (but with the extended functionality of magic stored procedure calls). Here is a very simple example to demonstrate:

    $db = new XDB\Adapter();
    $proc = new XDB\MagicProc($db);

    // PDO interface
    $stmt = $proc->prepare('CALL usersByPage(?,?)');
    $stmt->execute(array(1,9));
    $users = $stmt->fetchAll();

    // or magic
    $users = $proc->usersByPage(1,9);

Finally, here is the XDB\Adapter class:

namespace XDB;
use config\DB as conf;

/**
 * DB adapter, supports:
 * * PDO interface
 * * dynamically choose read or write handle per call
 * * lazy connection loading
 *
 **/
class Adapter {

    private static $wdb = false;
    private static $rdb = false;

    public static function write_master() {
        if (!self::$wdb) {
            self::$wdb = new \PDO(conf::WDSN, conf::WDB_USER, conf::WDB_PASSWORD);
        }
        return self::$wdb;
    }

    public static function read_slave() {
        if (!self::$rdb) {
            self::$rdb = new \PDO(conf::RDSN, conf::RDB_USER, conf::RDB_PASSWORD);
        }
        return self::$rdb;
    }

    /**
     * dynamically select write master or read slave
     *
     **/
    public static function connection($hint = false) {
        if ($hint and
                stripos($hint,'select') === 0 and
                stripos($hint,'last_insert_id') == false) {
            return self::read_slave();
        } else {
            return self::write_master();
        }
    }

    /**
     * MAGIC, proxy methods to appropriate PDO connection
     * * fully encapsulates connection (read vs write) handle
     *
     **/
    protected static function _proxy($method, $params) {
        $sql = false;
        if (in_array($method, array('query', 'execute')) ) {
            $sql = $params[0];
        }
        $conn = self::connection($sql);
        if (method_exists($conn, $method)) {
            return call_user_func_array(array($conn,$method), $params);
        }
    }

    public function __call($method, $params) {
        return self::_proxy($method, $params);
    }

    public static function __callStatic($method, $params) {
        return self::_proxy($method, $params);
    }
}