Creating a database abstraction layer

[roopurt18]

I've an idea about creating the DB abstraction layer on a new project and wanted to get some feedback before I went ahead with it.  I've only ever worked on two web sites; one I built from the ground up for my guild in WoW and the other I inherited from another programmer at my place of employment.

 

The website I built from the ground up was PHP4 and I built my DB layer before I learned about the class keyword; as such the DB code was very procedural.  I had a base file, mydb.php that had functions like: mydb_query, mydb_select, mydb_insert, etc.  Then I created a new file for each interface I needed to expose; for example the file db_users.php with functions: users_get_all, users_create_user, etc.

 

The website I've inherited at work is also PHP4 and takes a different approach.  There is an instantiated $db object which exposes methods like insert, select, etc but it is declared globally.  Each interface is a class declaration that is used statically; for example class UsersDAO with methods UsersDAO::getUsers, UsersDAO::createUser, etc.  Since $db is declared globally, each method has to start with global $db.

 

The second approach is obviously better than the first, but they both suffer from the same drawback.  If the interface requires its own internal constants, they have to be created with define statements; I would much rather use static properties to avoid naming collisions.  They both also use global variables; the first uses individual globals for the mysql connection, user, pass, etc. while the second uses a single global definition.

 

The second approach also has the drawback that there are many files that might need UserDAO.php, so require_once('UserDAO.php') might appear many times in code.  I do not like this because it is redundant work and I have read somewhere there are performance implications with many calls to require_once.

 

Also, some of the DAO class and method names become quite long, leading to extra typing.  The logical step in eliminating the extra typing is to create a variable for each DAO interface, but then I'm left with a bunch of variables floating around everywhere.

 

So here is my solution to all of the above and what I'd like to hear your opinions on.  This is intended to be run on PHP5.

 

Create a DB class:

<?php
  class DB {
    function constructor(){ }
    function query(){ }
    function insert(){ }
    function select(){ }
    // etc.
  }
?>

 

Create a DAO class:

<?php
  class UserDAO {
    function constructor(){ }
    function createUser(){ }
    function deleteUser(){ }
    function isValidUser(){ }
    // etc.
  }
?>

 

Create another DAO class:

<?php
  class ProjectDAO {
    function constructor(){ }
    function createProject(){ }
    function deleteProject(){ }
    function editProject(){ }
    // etc.
  }
?>

 

That is fairly straightforward, here is the tricky part.  In my program I want to have a single instantiated object that exposes all of the DAO classes.  I will call this class DAOInterface and I'll start with how I want my main program to use this class.

 

example.php

<?php
  // Example code that shows how DAOInterface exposes the DAO objects within the program.
  require_once('DAOInterface.php');

  $dao = new DAOInterface(); // Instantiate our object
  
  // Assume example.php needs to work with both the UserDAO and ProjectDAO interfaces...
  $dao->loadDAO('UserDAO');
  $dao->loadDAO('ProjectDAO');

  // At this point, the $dao object should expose all of the public methods contained within UserDAO
  // and ProjectDAO

  // Tell it which interface to use
  $dao->useDAO('UserDAO');
  if( !$dao->isValidUser( 'Bob' ) ){ // isValidUser maps to UserDAO::isValiduser!
    echo "Bad user: Bob";
    exit();
  }

  // User is editing a project
  $dao->useDAO('ProjectDAO');
  $OK = $dao->editProject( 15 /* ID */, "Smoogles" /* New name */ );
  if( !$OK ){
    echo "Couldn't edit project";
    exit();
  }

  echo "Project edited";
?>

 

And now the magic DAOInterface class:

<?php
  class DAOInterface {
    // Define some member vars (Using PHP4 syntax to just give a rough idea)
    var $_db = null, // Our DB object
         $_daos = null, // Array of loaded DAOs
         $_active = null; // Active interface

    function constructor(){
      $this->_db = &new DB(); // Create our DB object
      $this->_daos = Array(); // Init our array
    }

    /**
     * loadDAO
     * Ensures that the DAO interface is loaded and ready to use
     * @param string file path & name of DAO
     */
    function loadDAO($dao){
      if(!file_exists($dao)){ return; } // Doesn't exist
      if($this->_isLoaded($dao)){ return; } // Already loaded
      require_once($dao);
      $tdao = new $dao();
      $tarr = Array();
      $tarr["file"] = basename($dao);
      $tarr["dir"] = dirname($dao);
      $tarr["dao"] = $tdao;
      $this->_loaded[] = $tarr;
      // %% EXPANDED BELOW
    }

    /**
     * useDAO
     * Set a DAO to be the active DAO
     */
    function useDAO($dao){
      $idx = $this->_findDAO($dao);
      if($idx === FALSE){
        $this->_active = null;
      }else{
        $this->_active = $idx;
      }
    }
  }
?>

 

In my loadDAO method I have a comment that says explained below.  After a DAO object is instantiated and saved in the internal _loaded array, all of it's public methods need to be added to the DAOInterface object.  For each public method of the DAO being loaded, DAOInterface needs to create a public method of it's own with the same name.  Each of these public methods will essentially be the same function.  The body of each of them will check if the called function exists in the "active" DAO object and if it is, call the function, otherwise return NULL or FALSE.

 

What do you guys think?

[Buyocat]

I think it's a solid first step.  Here are some suggestions:

1) take advantage of PHP 5's features, such as declaring field variables private instead of var

2) do you really plan to access several DAO's at a given time?  If not it might be easier conceptually and programmatically to add a static factory method to the DAO's themselves and just use that to get instances of the class.  i.e. UserDao::getSingleton();

3) If it's important to you it might be worth moving all the strings you echo out into a resource packge or just a static array so that they can be easily changed, thus making it easier to localize your code

4) I don't see the reason for the expanded code you spoke of.  It seems like you could just return an instance of the UserDao and interact with that directly, what is the benefit to generating code on the fly to mirror existing code?

 

As I said I think it's a good start.  My overall concern is why you've split the DAO into several  unrelated objects, but are still trying to treat them as a uniform object.  You cannot exploit polymorphism if they have different interfaces so it may be worth letting that idea go.  What it looks like you're really trying to achieve is a class which concentrates the basic query operations, and several other classes which own an instance of the first and pass tailored queries to it.  In that sense the project would be a lot like ADODB or PDO.  Your User and Project objects would be domain specific, not interchangabe, and built around the foundation provided by your database abstraction object.  Thus you could reuse code to execute a query, but keep domain code separate.

 

If including files is the problem you could alleviate this by leveraging some sort of factory pattern.  This would make more sense than trying to shoehorn two distinct classes into a single definition.  You could leverage PHP 5's __autolod, or make your own factory.  The factory would work much like your current load and use methods, which might be conflated into a single method which returned a new instance of a requested object -- in this could it would be any object becuase it wouldn't cut across class definitions.

 

That was a lot, I hope it's somewhat clear and helpful.  Feel free to ask me questions or challenge my claims, Buyo

 

[roopurt18]

Thank you for your insightful input, it is much appreciated.

 

1) take advantage of PHP 5's features, such as declaring field variables private instead of var

Yes, of course.  I should have labeled my code examples as "loose" PHP or psuedo-code; I was expressing what I wanted to do in the simplest terms to get the point across and wasn't concerned with correctness.

 

2) do you really plan to access several DAO's at a given time?  If not it might be easier conceptually and programmatically to add a static factory method to the DAO's themselves and just use that to get instances of the class.  i.e. UserDao::getSingleton();

This is a really good question.  In the first site I wrote I don't recall ever having to use more than one at a time; however I'm not sure if that's because it was well-organized or because the site itself was rather simple.  In my work project I find myself occasionally having to use more than one DAO at a time but I think that's because the project itself is unorganized in some areas.  I think I'd like to plan for the ability to use more than one at a time if the need arises.

 

3) If it's important to you it might be worth moving all the strings you echo out into a resource packge or just a static array so that they can be easily changed, thus making it easier to localize your code

I actually did this in the original site I wrote, but my work designs software for home builders in the U.S.; it is unlikely we will need to support this product outside the U.S. for at least a few years.

 

4) I don't see the reason for the expanded code you spoke of.  It seems like you could just return an instance of the UserDao and interact with that directly, what is the benefit to generating code on the fly to mirror existing code?

The expanded code would just be a wrapper function that calls the corresponding function in the instantiated DAO object.  Let us assume the following public methods in each of our objects:

UserDAO

Create()

Delete()

isValidUser()

 

ProjectDAO

Create()

Delete()

isValidProject()

 

Then the instantiated DAOInterface object would have the following public methods:

Create()

Delete()

isValidUser()

isValidProject()

 

Each of these methods would have essentially the same body, here is the psuedo-code:

  function lambaFunction(){
    $funcName = get the name of this method
    if( this method does NOT exist in our active DAO object ){
      return FALSE;
    }
    $args = get the args passed to this function
    return $this->_active->$funcName($args);
  }

 

The one disadvantage that comes to mind with this approach is the extra overhead when calling functions; while I have no testing to back this claim up, my gut instinct tells me this extra overhead would not cause a major performance hit.

 

I can think of a couple advantages off the top of my head.  From the caller's point of view, it simplifies things greatly.  The calling application only has to manage a single variable for all of its DB interactions.  Also if I wanted to add code that needs to be called before and after every DB access function, then this approach makes that very simple.  Just change the code of the dynamic function in one place and I'd be done.

 

Traditional methodology would dictate the use of singletons or a DAOInterface object that returned instantiated objects like you suggested.

 

My goal here is to simplify the DB interactions as much as possible in the calling application at the expense of extra complexity under the hood.  I've never come across an implementation like the one I've proposed and I don't know if that's because it's been tried and failed or if it's because I'm thinking way outside the box.

[Buyocat]

My sense is that you should just combine the two DAO's into a single unified interface.  The example interfaces are so similar that there's really no reason not to; the advantages you'd gain from polymorphism would greatly outweigh the slightly less communicative method names.

 

UserDAO

Create()

Delete()

isValidUser()

 

ProjectDAO

Create()

Delete()

isValidProject()

 

Combine the interface into:

Create()

Delete()

isValid()

 

This would save you from having to implement functions dynamically, which means less work for you, and less of a headache maintaining a complicated procedure.  It would also mean you could make a factory which returns instances of the unified interface.

 

The result of course would mean you have a single interface with the method signatures and then 2 concrete classes which implement said interface.  The concrete classes would house their specific logic.

 

If you'd like to explain some more what you mean when you talk about code generation than I think I can say some more.  At this point I'm not sure whether you're dynamically creating queries based on some meta data, or whether you're just loading methods defined in some file.  If it's the later than you're already going more or less what I've suggested, if it's the former than I'd need to know more to speak about its desin.