{"id":238,"date":"2011-07-07T04:01:29","date_gmt":"2011-07-07T04:01:29","guid":{"rendered":"http:\/\/tech.avant.net\/q\/?p=238"},"modified":"2012-12-25T22:39:47","modified_gmt":"2012-12-25T22:39:47","slug":"php-db-revisited","status":"publish","type":"post","link":"https:\/\/tech.avant.net\/q\/php-db-revisited\/","title":{"rendered":"php DB adapter, revisited"},"content":{"rendered":"

I would like to call mysql stored procedures as if they were local php functions.<\/p>\n

For example, let’s use the following mysql stored procedure,<\/p>\n

\r\nDELIMITER \/\/\r\nCREATE PROCEDURE `usersByPage`(page_num INT, per_page INT)\r\nBEGIN\r\n    SET @lim_start = (page_num - 1) * per_page;\r\n    SET @lim_end = per_page;\r\n    PREPARE stmt FROM 'SELECT * FROM foobar_users\r\n                       ORDER BY user_id LIMIT ?,?';\r\n    EXECUTE stmt USING @lim_start, @lim_end;\r\nEND \/\/\r\nDELIMITER ;\r\n<\/pre>\n
The following unit test demonstrates a typical PDO-style call to a stored procedure.<\/p>\n
\r\n    public function test_pdo_call() {\r\n        $db = new DB();\r\n        $stmt = $db->prepare('CALL usersByPage(?,?)');\r\n        $stmt->execute(array(1,9));\r\n        $users = $stmt->fetchAll();\r\n        $this->assertEquals(9,count($users));\r\n    }\r\n<\/pre>\n
I would like to call the usersByPage()<\/em> procedure in php as if it were a php function. The unit tests would look something like this,<\/p>\n
\r\n    public function test_magic_proc() {\r\n        $db = new DB();\r\n        $users = $db->usersByPage(1,9);\r\n        $this->assertEquals(9,count($users));\r\n    }\r\n\r\n    public function test_magic_proc_static() {\r\n        $users = DB::usersByPage(1,9);\r\n        $this->assertEquals(9,count($users));\r\n    }\r\n<\/pre>\n
Rather than wrap each stored procedure in a php function, we can use php magic __call()<\/em> and __callStatic()<\/em> methods to proxy, which can be implemented with something like the following,<\/p>\n
\r\n    public static function _proxy($proc, $params) {\r\n        $conn = self::connection();\r\n        $bind_params = trim( str_repeat('?,',count($params)), ',');\r\n        $stmt = $conn->prepare(\"CALL $proc($bind_params)\");\r\n        $params ? $stmt->execute($params) : $stmt->execute();\r\n        return $stmt->fetchAll();\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<\/pre>\n
This approach can easily be integrated with the DB adapter<\/a> discussed previously, e.g.,<\/p>\n
\r\n<?php\r\nuse config\\DB as conf;\r\n\r\n\/**\r\n * DB adapter, supports:\r\n * * PDO interface\r\n * * encapsulate database handles\r\n * * dynamically choose read or write handle per call\r\n * * magic invocation and lazy connection loading\r\n * * magic calls to stored procedures\r\n *\r\n **\/\r\nclass DB {\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, object invocation\r\n     * * fully encapsulates connection (read vs write) handle\r\n     * * supports both prepared statements and sql execution\r\n     *\r\n     * $db = new DB();\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 = self::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    \/**\r\n     * call stored procedure\r\n     *\r\n     * available only through magic __call or __callStatic,\r\n     * * e.g., to call a stored procedure 'getUserById'\r\n     * $db = new DB();\r\n     * $user = $db->getUserById($id);\r\n     **\/\r\n    private static function callStoredProc($conn, $proc, $params) {\r\n        $bind_params = trim( str_repeat('?,',count($params)), ',');\r\n        $stmt = $conn->prepare(\"CALL $proc($bind_params)\");\r\n        $params ? $stmt->execute($params) : $stmt->execute();\r\n        return $stmt->fetchAll();\r\n    }\r\n\r\n    \/**\r\n     * MAGIC, proxy methods to appropriate PDO connection, or\r\n     * * call stored procedure.\r\n     *\r\n     **\/\r\n    private 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        } else {\r\n            return self::callStoredProc($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}\r\n\r\n\/**\r\n * MAGIC, functional invocation\r\n *\r\n * $array = DB('SELECT * FROM foo');\r\n **\/\r\nfunction DB($sql, $params = false) {\r\n    $db = new DB();\r\n    return $db($sql, $params);\r\n}\r\n\r\n?>\r\n<\/pre>\n
Next, I would like to separate this functionality into components using a Decorator<\/a> design pattern.<\/p>\n","protected":false},"excerpt":{"rendered":"
I would like to call mysql stored procedures as if they were local php functions. For example, let’s use the following mysql stored procedure, DELIMITER \/\/ CREATE PROCEDURE `usersByPage`(page_num INT, per_page INT) BEGIN SET @lim_start = (page_num – 1) * per_page; SET @lim_end = per_page; PREPARE stmt FROM ‘SELECT * FROM foobar_users ORDER BY user_id […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[15,7],"tags":[],"_links":{"self":[{"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/posts\/238"}],"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=238"}],"version-history":[{"count":10,"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/posts\/238\/revisions"}],"predecessor-version":[{"id":723,"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/posts\/238\/revisions\/723"}],"wp:attachment":[{"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/media?parent=238"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/categories?post=238"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/tags?post=238"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}