72 Squared :: Web Development Software

5/16/04

SQL Statement Constructor Classes and the DAO

I've spent a lot of time trying to come up with a way to build SQL statements dynamically using object-oriented programming. In conjunction with a set of DAO classes and a database abstraction, I had manufactured a set of sql statement classes that allowed the programmer to treat database tables as objects.

I'm convinced now that this approach was a mistake. I know the PROPEL project for PHP5 takes this same approach. I've looked at their source code and it looks like they did a good job of it, but I have decided this approach is not for me. Too much energy is wasted trying to mimic the database structure and translate it into OOP. The SQL language is an excellent interface to the database.
My approach now does not model the database structure. Instead, I build individual classes that provide me answers to specific questions. If I want to know all of the names of authors of a set of books, I build a single class that efficiently answers this question.
The trick is to extend different base DAO objects to provide answers to specific questions or manipulate data. Modeling the data structure is redundant since the structure is already contained in the database. My DAO contains a datasource connection and after manipulating the sql statement it passes it to the database and returns the database result set upon execution. Simple. here is an example of how I read session data from the database:

<?php
class DAO_SessionRead extends DaoModel
{
    function DAO_SessionRead()
    {
        $sql =& new SQLSelect('t_system_sessions');
        $sql->setWhat('data');
        $sql->setIsPrepared(false);
        $this->sql =& $sql;
    }

    function prepare($id)
    {
        $sql =& $this->sql();
        $sql->setWhere("session_id LIKE '" . $id . "'");
        $sql->setIsPrepared(true);
    }

    function get($id)
    {
        $this->prepare($id);
        $rs =& $this->execute();
        if(!$rs->getRowCount()) return '';
        $row = $rs->getRow(0, DB_NUM);
        $row[0];
    }
}
?>
Now, i can access session data easily from anywhere in my project using the following two lines:
<?
$dao =& DaoFactory::create('SessionRead');
$data = $dao->get(session_id());
?>
This is just a simple example of how I approach the problem of communicating with the database. More on this later ...