I would like to encapsulate the handling of database connections, and maintain a loose coupling between database connections and application and data-access code. I’ll not focus on connection pooling since that can be handled at the driver level independent of the application and adapter code.<\/p>\n
I would like to leverage the PDO interface as well as magic handling of the connections to allow for complete encapsulation of read-slaves and write-masters.<\/p>\n
For example, I would like data access code to be as simple as<\/p>\n
\r\n<?php\r\n $db = new DB();\r\n\r\n \/\/ should magically use write-master\r\n $stmt = $db->prepare('INSERT INTO users (username) VALUES (?)');\r\n $stmt->execute(array($username));\r\n\r\n \/\/ should magically use a read-slave\r\n $stmt = $db->prepare('SELECT * FROM users');\r\n $stmt->execute();\r\n $users = $stmt->fetchAll(PDO::FETCH_ASSOC);\r\n?>\r\n<\/pre>\nI would like to simplify this even further to support magic object invocation, e.g.,<\/p>\n
\r\n<?php\r\n $db = new DB();\r\n $users = $db('SELECT * FROM users');\r\n?>\r\n<\/pre>\nAnd even simpler, magic function invocation, also supporting prepared statements and bind parameters (without hassle), e.g.,<\/p>\n
\r\n<?php\r\n $user = DB('SELECT * FROM users WHERE id = ?', array($user_id));\r\n?>\r\n<\/pre>\nIn php this can be accomplished with something like the following:<\/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 *\r\n **\/\r\nclass DB {\r\n\r\n private static $wdb = false;\r\n private static $rdb = false;\r\n\r\n \/**\r\n * dynamically select write master or read slave,\r\n * by itself these functions can be used as a DB Connection Factory\r\n * \r\n * $conn = DB::connection();\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 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 * 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 * MAGIC, proxy methods to appropriate PDO connection\r\n *\r\n **\/\r\n public function __call($method, $params) {\r\n $sql = false;\r\n if (stripos($method, 'query') === 0 ||\r\n stripos($method, 'prepare') === 0) {\r\n $sql = $params[0];\r\n }\r\n $conn = self::connection($sql);\r\n return call_user_func_array(array($conn,$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","protected":false},"excerpt":{"rendered":"I would like to encapsulate the handling of database connections, and maintain a loose coupling between database connections and application and data-access code. I’ll not focus on connection pooling since that can be handled at the driver level independent of the application and adapter code. I would like to leverage the PDO interface as well […]<\/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\/69"}],"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=69"}],"version-history":[{"count":10,"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/posts\/69\/revisions"}],"predecessor-version":[{"id":737,"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/posts\/69\/revisions\/737"}],"wp:attachment":[{"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/media?parent=69"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/categories?post=69"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/tags?post=69"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}