Recently, I took a look at ADOdb, ADOdb-Lite, and PDO to find a replacement for PEAR::DB. I’ve looked at PEAR::MDB2, and not been happy with it for the same reasons I’m not happy with PEAR::DB:

  1. Since the application is tied to Postgres, there’s not much benefit from getting a database-agnostic driver. I’d rather have something that supports my database well, instead of all databases in a mediocre and outdated fashion.
  2. Not directly a complaint of PEAR itself, but my manager prefers wrapping existing OSS solutions to modifying the source (less concern about patches). Wrapping around PEAR::DB introduces all sorts of efficiency problems, since whatever data processing happens gets done twice. So not anything for the fault of PEAR, but it doesn’t fit with the development method of “wrap” vs “inject”.
  3. Following from #1, PEAR supports older versions of PHP/Postgres than I need, with the result that it won’t use all of the modern functionality I want.

For those of you who are curious, I did discover that ADOdb does use the existing pg_*() functions. Plus, the ADOdbs both target database independence through API-level SQL generation, instead of raw SQL. I ended up going with PDO for my project with a few modifications:

  • Exceptions are enabled by default
  • query() can take parametrized statements (why isn’t this done by default?)
  • Fetchmode is associative by default, so i can foreach a record
  • Statement execute() returns $this instead of an error code (I’m using exceptions anyways) so I can chain other functions onto it
  • I’ve added a few fetch*() functions for clarity in common use-cases

All of these modifications are intended to improve PDO’s overall usability (interfaces aren’t just for end users).

class PDO_ extends PDO {

  function __construct($dsn, $username, $password) {
    parent::__construct($dsn, $username, $password);
    $this->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  }

  function prepare($sql) {
    $stmt = parent::prepare($sql, array(
      PDO::ATTR_STATEMENT_CLASS => array('PDOStatement_')
    ));

    $stmt->setFetchMode(PDO::FETCH_ASSOC);
    return $stmt;
  }

  function query($sql, $params = array()) {
    $stmt = $this->prepare($sql);
    $stmt->execute($params);
    return $stmt;
  }
}

class PDOStatement_ extends PDOStatement {

  function execute($params = array()) {
    parent::execute($params);
    return $this;
  }

  function fetchSingle() {
    return $this->fetchColumn(0);
  }

  function fetchAssoc() {
    $this->setFetchMode(PDO::FETCH_NUM);
    $data = array();
    while ($row = $this->fetch()) {
      $data[$row[0]] = $row[1];
    }
    return $data;
  }
}

The end result of this is that I can use the connection much more naturally. It’s not quite a fluent interface, but you can see that it’s improved:

chained fetches after statement execution:
$stmt = $db->prepare(
    'SELECT first, last FROM users WHERE uid = ?');
$user1 = $stmt->execute(array(1))->fetch();
$user2 = $stmt->execute(array(2))->fetch();
parametrized direct querying:
$uid = $db->query(
    'SELECT uid FROM users WHERE first = ? AND last = ?',
    array($first, $last));
fetching an associative array of $uid => $username:
$users = $db->query('SELECT uid, username FROM users')
    ->fetchAssoc();
selecting a single aggregate:
$count = $db->query('SELECT COUNT(*) FROM users')
    ->fetchSingle();

I also add RAII-style transactional programming. This object can be created at the top of a function and committed at the bottom of the function. If an exception is thrown in between, it will automatically roll back the transaction that it started upon creation. This is useful in cases where exceptions are not being handled, but must bubble up to higher layers in the code.

class Transaction {

  private $db = NULL;
  private $finished = FALSE;

  function __construct($db) {
    $this->db = $db;
    $this->db->beginTransaction();
  }

  function __destruct() {
    if (!$this->finished) {
      $this->db->rollback();
    }
  }

  function commit() {
    $this->finished = TRUE;
    $this->db->commit();
  }

  function rollback() {
    $this->finished = TRUE;
    $this->db->rollback();
  }
}

This little guy is really convenient. Any model-level code that works with the database typically doesn’t need to address database errors (actually, I usually let them bubble up to the global error handler, which can then log it and print an apologetic 500 HTTP response).

function addUser($db, $username, $friend_users) {
  $txn = new Transaction($db);

  $db->query('INSERT INTO users(username) VALUES (?)', array($username);
  $uid = $db->query('SELECT currval(?)', array('username_id_seq'))
    ->fetchSingle();

  $stmt = $db->prepare('INSERT INTO friendships(uid1, uid2) VALUES (?, ?)
  foreach ($friend_users as $fuid => $fname) {
    $stmt->execute(array($uid, $fuid));
  }
  $txn->commit();
}

No exception handling required at all. If anything throws an error (e.g. an invalid friend), the stack unwinds and $txn automatically rolls back the transaction in its destructor.

Advertisements