YADAL - Yet Another Database Abstraction Layer

Coding Critique is the place to post source code for peer review by other members of DevNetwork. Any kind of code can be posted. Code posted does not have to be limited to PHP. All members are invited to contribute constructive criticism with the goal of improving the code. Posted code should include some background information about it and what areas you specifically would like help with.

Popular code excerpts may be moved to "Code Snippets" by the moderators.

Moderator: General Moderators

User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

YADAL - Yet Another Database Abstraction Layer

Post 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();
}
?>
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
jason.carter
Forum Commoner
Posts: 35
Joined: Sat Jan 10, 2009 10:05 am

Re: YADAL - Yet Another Database Abstraction Layer

Post by jason.carter »

Good work!

What would you say is one advantage of this DAL when compared to others?
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Re: YADAL - Yet Another Database Abstraction Layer

Post 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).
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: YADAL - Yet Another Database Abstraction Layer

Post 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 :)
Last edited by VladSun on Wed Feb 18, 2009 5:28 pm, edited 1 time in total.
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: YADAL - Yet Another Database Abstraction Layer

Post 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
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Re: YADAL - Yet Another Database Abstraction Layer

Post 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.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: YADAL - Yet Another Database Abstraction Layer

Post 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.
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Re: YADAL - Yet Another Database Abstraction Layer

Post 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.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: YADAL - Yet Another Database Abstraction Layer

Post 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.
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Re: YADAL - Yet Another Database Abstraction Layer

Post 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?
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Re: YADAL - Yet Another Database Abstraction Layer

Post 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 :)
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: YADAL - Yet Another Database Abstraction Layer

Post 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.
(#10850)
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Re: YADAL - Yet Another Database Abstraction Layer

Post 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?
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: YADAL - Yet Another Database Abstraction Layer

Post by Christopher »

I would just get rid of getInstance()...
(#10850)
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Re: YADAL - Yet Another Database Abstraction Layer

Post 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.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
Post Reply