DB Abstraction + SQL Generation

This is some pretty ancient code by web standards. However, it was used for a a decent amount of projects, including my early stabs at a community portal for my North American friends while I was living in Australia.

It handles basic CRUD operations without much hassle, and I built a helpdesk around this codebase as well. Unfortunately I haven't been able to keep up with the contact in the company the helpdesk was used at, so I'm unsure how well it eventually scaled.

First off, an example of it's usage:

class GenericFinder extends BaseFinder {

  /**
    *  holding the main SQL in private variables
    *  in each finder makes it much easier to edit
    *  if needed!
    **/

    var $find = "SELECT         
        forum_threads.id, user_id, forum_id, name,
        title, is_sticky, is_locked, is_deleted,
        is_moved, comment
          FROM
        forum_threads
          LEFT JOIN
        forums
          ON     
        forum_threads.forum_id = forums.id";

...

  /**
    *  override find
    *  @param  string id
    *  @return object Record
    *  @access public
    **/

    function &find($id)     {
      $query =& $this->createQuery($this->find);
      $query->addCriteria(
        Criteria::isEqualTo(
          'forum_threads.id', (int)$id
        )
      );

       $result = DBM::getArray($query);
       return new ThreadDecorator($result);
    }
    
}

So a few things to note. Yes it's PHP4. The whole "Why isn't the world onto PHP5 yet?" issue could cover a few blogs worth of information I imagine.

The code we want to take a look at the is find() function. Basically, this is your generic find a record set by primary key idea.

The inherited function createQuery() just returns a fresh Query object, which is populated with one of my SQL statements.

You can then modify the Query object by adding particular criteria (via static calls). In this example, it's just a basic find by primary key.

The result is then obtained via another static call, and we get an array back. (DBM contains a number of methods of grabbing data back, ie, as a Record object for a single object, a RecordSet for multiple records, or even a PagedRecordSet, for multiple records we want to be able to paginate.

The power in this comes from a few things.

  1. It's nice to have all your relevant SQL at the top of each finder, for easier access.
  2. Your SQL "auto-generates" and theoretically is abstracted from the actual DB (I only ever implemented a MySQL driver)
  3. You save time not having to worry about debugging SQL, and for the majority of CRUD operations, I haven't run into use cases where the code was unable to produce the SQL I needed