php DB adapter, with magic

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 as magic handling of the connections to allow for complete encapsulation of read-slaves and write-masters.

For example, I would like data access code to be as simple as

<?php
  $db = new DB();

  // should magically use write-master
  $stmt = $db->prepare('INSERT INTO users (username) VALUES (?)');
  $stmt->execute(array($username));

  // should magically use a read-slave
  $stmt = $db->prepare('SELECT * FROM users');
  $stmt->execute();
  $users = $stmt->fetchAll(PDO::FETCH_ASSOC);
?>

I would like to simplify this even further to support magic object invocation, e.g.,

<?php
  $db = new DB();
  $users = $db('SELECT * FROM users');
?>

And even simpler, magic function invocation, also supporting prepared statements and bind parameters (without hassle), e.g.,

<?php
  $user = DB('SELECT * FROM users WHERE id = ?', array($user_id));
?>

In php this can be accomplished with something like the following:

<?php
use config\DB as conf;

/**
 * DB adapter, supports:
 * * PDO interface
 * * encapsulate database handles
 * * dynamically choose read or write handle per call
 * * magic invocation and lazy connection loading
 *
 **/
class DB {

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

  /**
   * dynamically select write master or read slave,
   * by itself these functions can be used as a DB Connection Factory
   * 
   * $conn = DB::connection();
   **/
  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();
    }
  }

  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;
  }

  /**
   * MAGIC, object invocation
   * * fully encapsulates connection (read vs write) handle
   * * supports both prepared statements and sql execution
   *
   * $db = new DB();
   * $array1 = $db('SELECT * FROM foo');
   * $array2 = $db('SELECT * FROM foo WHERE bar = ?', $params);
   **/
  public function __invoke($sql, $params = false) {
    $conn = self::connection($sql);
    $stmt = $conn->prepare($sql);
    $params ? $stmt->execute($params) : $stmt->execute();
    return $stmt->fetchAll();
  }

  /**
   * MAGIC, proxy methods to appropriate PDO connection
   *
   **/
  public function __call($method, $params) {
    $sql = false;
    if (stripos($method, 'query') === 0 ||
        stripos($method, 'prepare') === 0) {
      $sql = $params[0];
    }
    $conn = self::connection($sql);
    return call_user_func_array(array($conn,$method), $params);
  }

}

/**
 * MAGIC, functional invocation
 *
 * $array = DB('SELECT * FROM foo');
 **/
function DB($sql, $params = false) {
  $db = new DB();
  return $db($sql, $params);
}

?>
This entry was posted in mysql, php. Bookmark the permalink.