{"id":247,"date":"2011-07-07T10:33:10","date_gmt":"2011-07-07T10:33:10","guid":{"rendered":"http:\/\/tech.avant.net\/q\/?p=247"},"modified":"2012-12-25T22:39:44","modified_gmt":"2012-12-25T22:39:44","slug":"php-db-adapter-decorator","status":"publish","type":"post","link":"https:\/\/tech.avant.net\/q\/php-db-adapter-decorator\/","title":{"rendered":"php DB adapter, decorator"},"content":{"rendered":"

Previously, I created a php database adapter<\/a> 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.<\/p>\n

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.<\/p>\n

The following unit-test demonstrates the desired functionality.<\/p>\n

\r\npublic function test_decorator() {\r\n    $db = new XDB\\Adapter();\r\n    $proc = new XDB\\MagicProc($db);\r\n    $sql = new XDB\\MagicSQL($db);\r\n\r\n    $users = $sql('SELECT username FROM foobar_users LIMIT 7');\r\n    $this->assertEquals(7,count($users));\r\n\r\n    $bbob = $sql('SELECT * FROM foobar_users WHERE username = ?', array('bbob'));\r\n    $this->assertEquals('bbob',$bbob[0]['username']);\r\n\r\n    $page1 = $proc->usersByPage(1,9);\r\n    $this->assertEquals(9,count($page1));\r\n}\r\n<\/pre>\n
In this case, the $db<\/em>, $sql<\/em>, and $proc<\/em> 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).<\/p>\n
Decorator<\/h2>\n
I’ll start with an abstract Decorator that persists the XDB\\Adapter<\/em>:<\/p>\n
\r\nnamespace XDB;\r\nabstract class Decorator {\r\n    protected $db;\r\n    public function __construct(Adapter $db) {\r\n        $this->db = $db;\r\n    }\r\n}\r\n<\/pre>\n
The components extend the Decorator<\/em> class and add the specific functionality. Here is the magic SQL functionality:<\/p>\n
\r\nnamespace XDB;\r\nclass MagicSQL extends Decorator {\r\n\r\n    \/**\r\n     * MAGIC, object invocation\r\n     *\r\n     * $array1 = $db('SELECT * FROM foo');\r\n     * $array2 = $db('SELECT * FROM foo WHERE bar = ?', $params);\r\n     **\/\r\n    public function __invoke($sql, $params = false) {\r\n        $conn = $this->db->connection($sql);\r\n        $stmt = $conn->prepare($sql);\r\n        $params ? $stmt->execute($params) : $stmt->execute();\r\n        return $stmt->fetchAll();\r\n    }\r\n}\r\n<\/pre>\n
Notice that the connection is accessed through $this->db<\/em>, which is the XDB\\Adapter<\/em> object that was passed into the Decorator<\/em> constructor.<\/p>\n
Here is the magic stored procedure functionality:<\/p>\n
\r\nnamespace XDB;\r\nclass MagicProc extends Decorator {\r\n\r\n    \/**\r\n     * MAGIC, call stored procedure\r\n     *\r\n     * e.g., to call a stored procedure 'getUserById'\r\n     * $user = $db->getUserById($id);\r\n     **\/\r\n    public function __call($method, $params) {\r\n        $conn = $this->db->connection();\r\n        if (!method_exists($conn, $method)) {\r\n            $bind_params = trim( str_repeat('?,',count($params)), ',');\r\n            $stmt = $conn->prepare(\"CALL $method($bind_params)\");\r\n            $params ? $stmt->execute($params) : $stmt->execute();\r\n            return $stmt->fetchAll();\r\n        } else {\r\n            return $this->db->_proxy($method, $params);\r\n        }\r\n    }\r\n}\r\n<\/pre>\n
Notice that $this->db<\/em> (from the Decorator constructor) is accessed to call the _proxy()<\/em> method. This preserves the PDO proxy interface from XDB\\Adapter<\/em> enabling the decorated object to behave exactly as XDB\\Adapter<\/em> (but with the extended functionality of magic stored procedure calls). Here is a very simple example to demonstrate:<\/p>\n
\r\n    $db = new XDB\\Adapter();\r\n    $proc = new XDB\\MagicProc($db);\r\n\r\n    \/\/ PDO interface\r\n    $stmt = $proc->prepare('CALL usersByPage(?,?)');\r\n    $stmt->execute(array(1,9));\r\n    $users = $stmt->fetchAll();\r\n\r\n    \/\/ or magic\r\n    $users = $proc->usersByPage(1,9);\r\n<\/pre>\n
Finally, here is the XDB\\Adapter<\/em> class:<\/p>\n
\r\nnamespace XDB;\r\nuse config\\DB as conf;\r\n\r\n\/**\r\n * DB adapter, supports:\r\n * * PDO interface\r\n * * dynamically choose read or write handle per call\r\n * * lazy connection loading\r\n *\r\n **\/\r\nclass Adapter {\r\n\r\n    private static $wdb = false;\r\n    private static $rdb = false;\r\n\r\n    public static function write_master() {\r\n        if (!self::$wdb) {\r\n            self::$wdb = new \\PDO(conf::WDSN, conf::WDB_USER, conf::WDB_PASSWORD);\r\n        }\r\n        return self::$wdb;\r\n    }\r\n\r\n    public static function read_slave() {\r\n        if (!self::$rdb) {\r\n            self::$rdb = new \\PDO(conf::RDSN, conf::RDB_USER, conf::RDB_PASSWORD);\r\n        }\r\n        return self::$rdb;\r\n    }\r\n\r\n    \/**\r\n     * dynamically select write master or read slave\r\n     *\r\n     **\/\r\n    public static function connection($hint = false) {\r\n        if ($hint and\r\n                stripos($hint,'select') === 0 and\r\n                stripos($hint,'last_insert_id') == false) {\r\n            return self::read_slave();\r\n        } else {\r\n            return self::write_master();\r\n        }\r\n    }\r\n\r\n    \/**\r\n     * MAGIC, proxy methods to appropriate PDO connection\r\n     * * fully encapsulates connection (read vs write) handle\r\n     *\r\n     **\/\r\n    protected static function _proxy($method, $params) {\r\n        $sql = false;\r\n        if (in_array($method, array('query', 'execute')) ) {\r\n            $sql = $params[0];\r\n        }\r\n        $conn = self::connection($sql);\r\n        if (method_exists($conn, $method)) {\r\n            return call_user_func_array(array($conn,$method), $params);\r\n        }\r\n    }\r\n\r\n    public function __call($method, $params) {\r\n        return self::_proxy($method, $params);\r\n    }\r\n\r\n    public static function __callStatic($method, $params) {\r\n        return self::_proxy($method, $params);\r\n    }\r\n}\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[7],"tags":[],"_links":{"self":[{"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/posts\/247"}],"collection":[{"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/comments?post=247"}],"version-history":[{"count":10,"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/posts\/247\/revisions"}],"predecessor-version":[{"id":722,"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/posts\/247\/revisions\/722"}],"wp:attachment":[{"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/media?parent=247"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/categories?post=247"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/tags?post=247"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}