Page 1 of 2

YADAL - Yet Another Database Abstraction Layer

Posted: Tue Feb 17, 2009 11:46 am
by pickle
Hi all,

Just finished writing this today. The techniques in it may be old hat to some of you, but the concepts of using classes for the result set and result rows, using Exceptions at all, and implementing SPL::ArrayIterator - are all new to me. This isn't in production yet, so I'd really welcome any input you may have about how to improve these classes.

A total of 3 classes:
  1. DBE: Basically abstracts everything to do with the database, that doesn't deal with the results of the query. [ I realize the way the __construct() function gets it's connection parameters is a little...cludgy...but I am dealing with an embedded system somewhat & this is how I have to do it. ]
  2. DBEResult: The result set & provides access to all data (that I think I'll need) with regards to the result set
  3. DBERecord: A particular record
I've got them all in one file, but I'll break them up here for easier reading:

DBE:

Code: Select all

class DBE
{
    private $mysqli;
    
    #####
    # Function: __construct()
    # Purpose: To create the database connection
    # Parameters: $username [string] - the username to connect with.  Defaults to boolean FALSE
    #             $password [string] - the password to connect with.  Defaults to boolean FALSE
    #             $db [string] - the database to connect to.  Defaults to boolean FALSE
    #             $host [string] - the host to connect to.  Defaults to boolean FALSE
    # 
    # If $username,$password, & $db are all not specified, credentials are pulled from $SESSION_GLOBALS
    ##
    function __construct($username=FALSE,$password=FALSE,$db=FALSE,$host='localhost')
    {
        #if credentials were passed, check them
        if($username || $password || $db)
        {
            if(!$username)
                throw new Exception('No username provided');
            if(!$password)
                throw new Exception('No password provided');
            if(!$db)
                throw new Exception('No database name provided');
        }
        # otherwise, if the webapp is defined, import the credentials from $SESSION_GLOBALS
        else if(defined('CALLING_WEBAPP_NAME'))
        {
            global $SESSION_GLOBALS;
            $host = (isset($SESSION_GLOBALS[CALLING_WEBAPP_NAME]['host'])) ? $SESSION_GLOBALS[CALLING_WEBAPP_NAME]['host'] : 'localhost';
            $username = $SESSION_GLOBALS[CALLING_WEBAPP_NAME]['user'];
            $password = $SESSION_GLOBALS[CALLING_WEBAPP_NAME]['password'];
            $db = $SESSION_GLOBALS[CALLING_WEBAPP_NAME]['db'];
        }
        # otherwise we have a problem
        else
            throw new Exception('Database credentials not provided');
        
        # Try to connect
        $this->mysqli = new mysqli($host,$username,$password,$db);
        if($this->mysqli->connect_error)
            throw new Exception('MySQLi connection error: '.$this->mysqli->connect_error);
    }
    
    #####
    # Function: __get()
    # Purpose: to be a magic getter for variables that aren't public
    #
    # Note: only exists if someone wants to access self::mysqli
    ##
    function __get($value)
    {
        if($value == 'mysqli')
            return $this->mysqli;
        return FALSE;
    }
 
    #####
    # Function: getInstance()
    # Purpose: A Simpleton instantiater - used to get the current database connection.
    #          Allows for many calls to DBE::getInstance() without making multiple database connections
    # Parameters: same as __construct()
    ##
    function getInstance($username=FALSE,$password=FALSE,$db=FALSE,$host='localhost')
    {
        static $DBEinstance;
        if(!isset($DBEinstance))
            $DBEinstance = new DBE($username,$password,$db,$host);
        return $DBEinstance;
    }
    
    #####
    # Function: execute()
    # Purpose: To execute the query
    # Parameters: $query (string) - the query to execute
    #             $purpose [string] - what this query does.
    #             $display_query [boolean] - whether or not to display the query if an error occurs
    ##
    function execute($query,$purpose='',$display_query=FALSE)
    {
        $result = $this->mysqli->query($query);
 
        if($result === FALSE)
        {
            $error = $this->mysqli->error;
            $errno = $this->mysqli->errno;
                
            $msg = <<<ERROR
Database error while "$purpose"<br />
Database message: $error<br />
Database error number: $errno
ERROR;
            $msg .= ($display_query) ? "<br />Query: $query" : '';  
                
            throw new Exception($msg);
        }
        else
            return new DBEResult($result);
    }
}
DBEResult

Code: Select all

class DBEResult extends ArrayIterator
{
    private $mysqli;
    private $resultset;
    private $affected_rows;
    
    private $index = 0;
    private $rows = array();
    
    #####
    # Function: __construct()
    # Purpose: To instantiate this object
    # Parameters: $result - the result returned from DBE::execute().  Must be boolean TRUE or a MySQLi_Result object
    ##
    function __construct($result)
    {
        $this->resultset = $result;
        $mysqli = DBE::getInstance()->mysqli;
        
        # Set this immediately rather than pulling from $this->mysqli on demand, as
        # $this->mysqli is singleton, so it could report a number for a different
        # query than the one this resultset is for
        $this->affected_rows = $mysqli->affected_rows;
    }
    
    #####
    # Function: __get()
    # Purpose: To return datum about the result set
    ##
    function __get($value)
    {
        switch($value)
        {
            case 'affected_rows':
                return $this->affected_rows;
                break;
            case 'num_rows':
                return $this->resultset->num_rows;
                break;
            default:
                throw new Exception('Property does not exist');
                break;
        }
    }
    
    #####
    # Function: current()
    # Purpose: To return the DBERecord object representing the row at the current index
    #
    # Implements a cache of sort so only one object per row is generated
    ##
    function current()
    {
        if(!isset($this->rows[$this->index]))
            $this->rows[$this->index] = new DBERecord($this->resultset->fetch_assoc());
            
        return $this->rows[$this->index];
    }
    
    #####
    # Function: key()
    # Purpose: To return the current index
    ##
    function key()
    {
        return $this->index;
    }
    
    #####
    # Function: next()
    # Purpose: To increment the current index
    ##
    function next()
    {
        $this->index++;
    }
    
    #####
    # Function: rewind()
    # Purpose: To reset the index to 0
    ##
    function rewind()
    {
        $this->index = 0;
    }
    
    #####
    # Function: seek()
    # Purpose: To move the index to a particular value
    # Note: Throws an exception if the requested index is outside the size of the resultset
    ##
    function seek($new_index)
    {
        if($new_index >= 0 && $new_index < $this->resultset->num_rows)
            $this->index = $new_index;
        else
            throw new Exception('Seek index out of bounds');
    }       
    
    #####
    # Function: valid()
    # Purpose: To see if there is an actual row for the current index
    ##
    function valid()
    {
        if(is_object($this->resultset))
            if($this->index >= 0 && $this->index < $this->resultset->num_rows)
                return TRUE;
        
        return FALSE;
    }
}
DBERecord

Code: Select all

class DBERecord
{
    private $data = array();
    
    function __construct($row_array)
    {
        $this->data = $row_array;
    }
    
    #####
    # Function: __get()
    # Purpose: To be a magic getter for fields in $data
    #
    # This function allows users to reference columns in the result set as $Record->resultsetcolumn,
    # rather than $Record->data['resultsetcolumn']
    ##
    function __get($name)
    {
        if(isset($this->data[$name]))
            return $this->data[$name];
        else
            throw new Exception('Unknown field name');
    }
}

USAGE
Of particular interest is how the result set can be iterated like an array (an idea recently shared by ~Chris Corbyn), and how the columns in DBERecord can be accessed like class variables.

Code: Select all

<?PHP
 
require 'dbe.php';
 
try{
    $DB = DBE::getInstance();
}
catch(Exception $e){
    echo $e->getMessage();
}
 
$query = <<<SQL
SELECT
    assigned_to,
    author,
    status,
    priority,
    on_behalf
    contents
FROM
    challenge_contents as cc,
    challenge_properties as cp
WHERE
    cp.challenge_id = cc.challenge_id
ORDER BY
    cp.challenge_id DESC
LIMIT 10
SQL;
 
try{
    $Result = $DB->execute($query);
    echo $Result->num_rows;
    foreach($Result as $Row)
    {
        echo $Row->author.'<br />';
    }
}
catch(Exception $e){
    echo $e->getMessage();
}
?>

Re: YADAL - Yet Another Database Abstraction Layer

Posted: Wed Feb 18, 2009 11:48 am
by jason.carter
Good work!

What would you say is one advantage of this DAL when compared to others?

Re: YADAL - Yet Another Database Abstraction Layer

Posted: Wed Feb 18, 2009 11:55 am
by pickle
Hard to say unless I know the features of the other DALs. In comparison to my other DALs, the advantages are the ability to iterate through the result set like an array, the ability to treat num_rows, affected_rows, and the record fields like object variables, and the method of error reporting (using Exceptions).

Re: YADAL - Yet Another Database Abstraction Layer

Posted: Wed Feb 18, 2009 4:54 pm
by VladSun
I always use this template for singleton classes:

Code: Select all

class ${name}
{
    private static $instance;
 
    /**
    * __construct
    * Constructor can't be called directly, thus getInstance() 
    * is the only way to instantiate the class
    *
    * @access private
    */
    private function __construct()
    {
        // Initialize code goes here
    }
 
    /**
    * __clone
    * Singletons can't be cloned
    *
    * @final
    * @access public
    * @throws BadMethodCallException
    */
    public final function __clone()
    {
        throw new BadMethodCallException('Clone is not allowed.');
    }
 
    /**
    * __wakeup
    * Singletons can't be deserialized
    *
    * @final
    * @access public
    * @throws BadMethodCallException
    */
    public final function __wakeup()
    {
        throw new BadMethodCallException('Deserializing is not allowed.');
    }
 
    /**
    * getInstance
    * Return the one and only instance of the class
    *
    *
    * @static
    * @final
    * @access public
    * @return object singleton instance
    */
    public static final function getInstance()
    {
        if (!(self::$instance instanceof self))
            self::$instance = new self;
 
        return self::$instance;
    }
}
And yes - using $SESSION_GLOBALS for DB initialization is somehow weird :)

Re: YADAL - Yet Another Database Abstraction Layer

Posted: Wed Feb 18, 2009 5:03 pm
by VladSun
Hm, maybe it's better to add a DBE::connect($host, $user, $pass) method instead of connecting to the DB in getInstance() method

Re: YADAL - Yet Another Database Abstraction Layer

Posted: Wed Feb 18, 2009 5:06 pm
by pickle
~VladSun - thanks for the info. The only real difference between your getInstance() and mine (which I must admit I didn't write. I found it somewhere - may have stolen it from ~Everah) is that you declare your $instance as an object variable and I just declare mine in the function. I have to admit I'm a little ignorant of what the advantage/ramifications are for doing it one way or the other.

I had a whole paragraph written about what $SESSION_GLOBALS is & why I use it. As I re-read through it though, it seemed like it might cause a security issue. So, suffice it to say it's strange & necessary.

I'm not sure what you mean about connecting in getInstance()? A new connection is only made when __connect() is invoked, and that's only done once.

Re: YADAL - Yet Another Database Abstraction Layer

Posted: Wed Feb 18, 2009 5:23 pm
by VladSun
pickle wrote:~VladSun - thanks for the info. The only real difference between your getInstance() and mine (which I must admit I didn't write.

I think there are other more important differences (and not only in getInstance())

1. When I said it was "a template" I really meant it :) I've defined PHP singleton class template (NetBeans :twisted:) and when I want to define a new singleton class I need only to select it from templates.
The usage of

Code: Select all

$instance = new self;
doesn't make me edit anything (except constructor if I have to) in the newly created class file - I just add the "real" methods I need.

2. The private (or protected) access of __construct ensures nobody (especially me :)) will succeed instantiating the class by using the new operator.

3. The declaration and implementation of __clone and __wakeup have similar purposes.

pickle wrote:I found it somewhere - may have stolen it from ~Everah[/i])
I have stolen it from Wiki :)
pickle wrote:I'm not sure what you mean about connecting in getInstance()? A new connection is only made when __connect() is invoked, and that's only done once.
(I couldn't find any __connect() methods in you code :( )

When you call DBE::getInstance(......) for the first time it calls __construct which in turn calls $this->mysqli = new mysqli($host,$username,$password,$db);

So, in fact you connect to the DB at the first call to getInstance and I suggest you to use a special, separate method for providing DB credentials and connecting to the DB.

Re: YADAL - Yet Another Database Abstraction Layer

Posted: Wed Feb 18, 2009 5:55 pm
by pickle
VladSun wrote:
pickle wrote:I'm not sure what you mean about connecting in getInstance()? A new connection is only made when __connect() is invoked, and that's only done once.
(I couldn't find any __connect() methods in you code :( )
I meant __construct() :oops:

VladSun wrote:I suggest you to use a special, separate method for providing DB credentials and connecting to the DB.
What would the advantage of that be, other than possible clarity of code?

As for your other points, I like the idea of making __construct() private. __clone() and __wakeup() don't really apply to me - I'm the only developer using this & I don't see those being a problem.

Re: YADAL - Yet Another Database Abstraction Layer

Posted: Wed Feb 18, 2009 6:14 pm
by VladSun
It seems to me a quick&dirty solution to pass any arguments to the getInstance (in other words - passing them to the constructor) method of any singleton object.
I think, because the constructor will be called just once it introduces some issues - e.g. in case you have two DBs.

Re: YADAL - Yet Another Database Abstraction Layer

Posted: Thu Feb 19, 2009 9:46 am
by pickle
Hmm, you raise a good point about connecting to multiple databases. There's no way to simultaneously connect to multiple databases with a singleton object is there?

Re: YADAL - Yet Another Database Abstraction Layer

Posted: Thu Feb 19, 2009 11:33 pm
by Chris Corbyn
pickle wrote:Hmm, you raise a good point about connecting to multiple databases. There's no way to simultaneously connect to multiple databases with a singleton object is there?
No, but you can use a registry to do that :)

Re: YADAL - Yet Another Database Abstraction Layer

Posted: Fri Feb 20, 2009 2:27 am
by Christopher
Or just pass in separate connection objects. Let's not go pattern happy with Singletons and Registries when plain old parameters will do fine.

Re: YADAL - Yet Another Database Abstraction Layer

Posted: Fri Feb 20, 2009 10:28 am
by pickle
Ok, so here's what I've done - make a hash of each set of connection parameters. Then when getInstance() is called, the instance is returned for those parameters:

Code: Select all

    public function getInstance($username=FALSE,$password=FALSE,$db=FALSE,$host='localhost')
    {
        # generate a unique key for these connection parameters
        $connection_hash = md5($username.$password.$db.$host);  
        
        # If a connection with these parameters doesn't exist, make one.  This allows connections
        # to multiple databases
        if(!isset(self::$instances[$connection_hash]))
            self::$instances[$connection_hash] = new DBE($username,$password,$db,$host);
        return self::$instances[$connection_hash];
    }
What do you guys think? Think that should work?

Re: YADAL - Yet Another Database Abstraction Layer

Posted: Fri Feb 20, 2009 11:34 am
by Christopher
I would just get rid of getInstance()...

Re: YADAL - Yet Another Database Abstraction Layer

Posted: Fri Feb 20, 2009 11:49 am
by pickle
arborint wrote:I would just get rid of getInstance()...
It is quite useful actually. Say, for example, I've got a calendar of events, with each event being it's own object. Each Event object needs to access the database to retrieve it's information. With a singleton DAL, each unique Event object uses the same database connection, rather than making one connection for each object.