Page 1 of 1

Database Abstraction - simplicity and PDO

Posted: Wed Feb 14, 2007 9:21 am
by Maugrim_The_Reaper
Ok, bit of background. As part of a small project, we're writing a smallish Data Access Object framework. The DAO is unimportant for thos topic, since this one concerns the abstraction layer. Since we have modest needs, I booted up my editor recently and started some coding. The main requirement was a relatively simple Adapter to PDO (mysqli and pgsql in the future), focus on prepared statements, and for results to be iterable.

After figuring out a few odd things (PDO classes are traversable not iterable) and latching onto some other differences between PDO and some of my more usual abstraction libraries I came up with the following - criticism welcome ;). It actually has no custom Result class seeing as SPL supplies support in that area. I think some of my peers question of fetching all results immediately. AFAIK, this is sort of needed since MySQL has issues with parallel queries (must be one by one in FIFO style).

Code: Select all

<?php
/**
 * @internal
 * Quantum Game Library
 *
 * LICENSE
 *
 * This source file is subject to the new BSD license that is bundled
 * with this package in the text file LICENSE located in the root
 * directory of this library.
 * It is also available through the internet at this URL:
 * http://doc.astrumfutura.com/license.html
 *
 * If you did not receive a copy of the license and are unable to
 * obtain it through the internet, please send an email
 * to license@astrumfutura.com so we can send you a copy.
 *
 * @package    Db
 * @subpackage Driver
 * @category   Quantum
 * @copyright  Copyright (c) 2007 The QGL Group (refer to COPYRIGHT file)
 * @version    $Id: Mysql.php 241 2007-02-14 15:01:17Z maugrim_t_r $
 * @license    http://doc.astrumfutura.com/license.html     New BSD License
 */

/** Quantum_Db_Driver_Interface */
require_once 'Quantum/Db/Driver/Interface.php';

/** Quantum_Db_Result_Pdo_Mysql */
//require_once 'Quantum/Db/Result/Pdo/Mysql.php'; - using ArrayIterator for now.

/**
 * @package    Db
 * @subpackage Driver
 * @category   Quantum
 * @author     Pádraic Brady (http://blog.astrumfutura.com)
 *
 * Abstract access to the pdo_mysql extension. The abstraction in general
 * deliberately follows the PDO interface where possible for common methods.
 */
class Quantum_Db_Driver_Pdo_Mysql implements Quantum_Db_Driver_Interface
{

    /**
     * Holds the current PDO extension type.
     *
     * @var string
     */
    protected $_type = 'pdo_mysql';

    /**
     * Holds the current PDO connection object.
     *
     * @var PDO
     */
    protected $_connection = null;

    /**
     * Holds the current PDOStatement object.
     *
     * @var PDOStatement
     */
    protected $_statement = null;

    /**
     * Holds the current Result iterator object.
     *
     * @var ArrayIterator
     */
    protected $_result = null;

    /**
     * Holds the last SQL string prepared.
     *
     * @var string
     */
    protected $_lastPreparedStatement = '';

    /**
     * Constructor - eventually callable from a Factory.
     * Constructs a PDO connection based on the parameters.
     *
     * @param string $dsn
     * @param string $userName
     * @param string $password
     * @return void
     * @todo Amend parameter list as appropriate for the Factory
     */
    public function __construct($dsn, $userName, $password)
    {
        try
        {
            $this->_connection = new PDO($dsn, $userName, $password);
            /*
             * Force lower case as standard.
             */
            $this->_connection->setAttribute(PDO::ATTR_CASE, PDO::CASE_LOWER);
            /*
             * Errors throw Exceptions
             */
            $this->_connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        }
        catch (Exception $e)
        {
            throw new Quantum_Db_Driver_Exception($e->getMessage(), $e->getCode());
        }
    }

    /**
     * Prepare an SQL string for a query (SELECT) and return an ArrayIterator
     * based on the resultset returned from calling PDOStatement::execute().
     *
     * @param string $sql
     * @param array $fields
     * @return ArrayIterator
     * @todo Figure out the most efficient return type.
     */
    public function query($sql, $fields = null)
    {
        if(isset($fields) && !is_array($fields))
        {
            throw new Quantum_Db_Driver_Exception('$fields parameter is not an array');
        }
        try
        {
            /*
             * Store the SQL; if this particular SQL string was already
             * prepared, there's no point calling prepare() again.
             */
            if($this->_lastPreparedStatement != $sql)
            {
                $this->_lastPreparedStatement = $sql;
                $this->_statement = $this->_connection->prepare($sql);
            }
            if(strpos(trim(strtolower($sql)), 'select') === 0)
            {
                return $this->_getResult($fields);
            }
            return $this->_exec($fields);
        }
        catch (Exception $e)
        {
            throw new Quantum_Db_Driver_Exception($e->getMessage(), $e->getCode());
        }
    }

    /**
     * Execute an SQL statement which returns a PDOStatement.
     *
     * @return bool
     */
    protected function _getResult($fields = null)
    {
        $this->_statement->execute($fields);
        $this->_statement->setFetchMode(PDO::FETCH_ASSOC);
        /*
         * Return a buffered ArrayIterator with all records.
         * fetchAll() is flexible, at the cost of a large rowset.
         * Reasoning is MySQL's lack of unbuffered parallel query support
         * and also to keep the returned result simple.
         */
        if($this->_statement->columnCount() == 0)
        {
            return false;
        }
        $this->_result = new ArrayIterator( $this->_statement->fetchAll() );
        return $this->_result;
    }

    /**
     * Execute an SQL statement which does not require a returned PDOStatement.
     *
     * @return bool
     */
    protected function _exec($fields = null)
    {
        return $this->_statement->execute($fields);
    }

    /**
     * Quote and escape the passed value for inclusion in an SQL statement.
     *
     * @param mixed $value
     * @return mixed
     */
    public function quote($value)
    {
        return $this->_connection->quote($value);
    }

    /**
     * Return the last autoincrement or Sequential value from an INSERT.
     * The optional parameter is for PostgreSQL sequences.
     *
     * @param mixed $sequence
     * @return int
     */
    public function lastInsertId($sequence = null)
    {
        return $this->_connection->lastInsertId();
    }

    /**
     * Begin a transaction.
     *
     * @return void
     */
    public function beginTransaction()
    {
        $this->_connection->beginTransaction();
    }

    /**
     * Commit a transaction.
     *
     * @return void
     */
    public function commit()
    {
        $this->_connection->commit();
    }

    /**
     * Roll back a transaction.
     *
     * @return void
     */
    public function rollBack()
    {
        $this->_connection->rollBack();
    }

    /**
     * Close the current connection; just set the PDO variable to NULL.
     *
     * @return void
     */
    public function close()
    {
        $this->_connection = null;
    }

}
Not much for testing - the interface is a close fit with PDO so there's little point testing all the methods at this stage.

Code: Select all

<?php

require_once 'Quantum/Db/Driver/Pdo/Mysql.php';

define('TEST_SQL_TABLE_CREATE', "
CREATE TABLE `user` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(32) collate utf8_unicode_ci NOT NULL,
  `password` varchar(64) collate utf8_unicode_ci NOT NULL,
  `email` varchar(64) collate utf8_unicode_ci NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `user_email` (`email`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=5 ;

INSERT INTO `user` VALUES (1, 'Padraic', 'password', 'padraic@example.com');
INSERT INTO `user` VALUES (2, 'Jacob', 'password', 'jacob@example.com');
INSERT INTO `user` VALUES (3, 'Lee', 'password', 'lee@example.com');
INSERT INTO `user` VALUES (4, 'Ben', 'password', 'ben@example.com');
");

class Test_Db_Row extends UnitTestCase {

    private $_config = null;

    private $_connection = null;

	public function __construct() {
		$this->UnitTestCase('Test of Quantum_Db_Driver_Pdo_Mysql');
        $this->_config = parse_ini_file('data/quantum_db_test_config.ini'); // just a quick config file for a test database
	}

	public function setUp() {
        $this->_connection = new PDO('mysql:host=' . $this->_config['host'] . ';dbname=' . $this->_config['database'], $this->_config['user'], $this->_config['password']);
        $this->_connection->exec(TEST_SQL_TABLE_CREATE);
	}

	public function tearDown() {
        $this->_connection->exec('DROP TABLE user');
	}

    public function testConstructor()
    {
        try
        {
            $driver = new Quantum_Db_Driver_Pdo_Mysql('mysql:host=' . $this->_config['host'] . ';dbname=' . $this->_config['database'], $this->_config['user'], $this->_config['password']);
            $this->pass();
        } catch (Exception $e) {
            $this->fail($e->getMessage());
        }
    }

    public function testQuery()
    {
        $driver = new Quantum_Db_Driver_Pdo_Mysql('mysql:host=' . $this->_config['host'] . ';dbname=' . $this->_config['database'], $this->_config['user'], $this->_config['password']);
        $result = $driver->query("SELECT * FROM user");
        /*
         * Result should be an ArrayIterator
         * The extra tests just make sure the expected data is present.
         * ArrayIterator extends SeekableIterator
         */
        $this->assertIsA($result, 'ArrayIterator');
        $this->assertEqual($result->count(), 4);
        foreach($result as $row)
        {
            $this->assertEqual(count($row), 4);
            $this->assertTrue(is_array($row));
        }
        $result->seek(1);
        $this->assertIdentical($result->current(), array('id'=>'2', 'name'=>'Jacob', 'password'=>'password', 'email'=>'jacob@example.com'));
    }

}

Posted: Wed Feb 21, 2007 3:21 pm
by Begby
Another way you can approach this is to extend PDO and PDOStatement. That way instead of wrapping every bit of functionality you need, you can instead override methods and make it return stuff as iterable instead of traversable.

Below is a snippet from my PDO class, you can see how I changed what object gets returned in place of the vanilla PDOStatement and then how I overrode a method to make code insight reference the correct class. I actually have a lot more methods but just cut out the top.

Code: Select all

/**
 * Guild PDO class for database access, adds additional functional to PHP base PDO
 *
 * @author Jeff Dorsch
 * @package FCP
 * @subpackage DB
 */
class FCP_DB_PDO extends PDO
{
	
	/**
	 * Constructor
	 *
	 * @param string $dbURI 
	 * @param string $login
	 * @param string $password
	 */
	public function __construct( $dbURI, $login, $password)
	{
		parent::__construct( $dbURI, $login, $password ) ;
		$this->setAttribute( PDO::ATTR_STATEMENT_CLASS, array( 'FCP_DB_PDOStatement', array() ) ) ;
		$this->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION ) ;
	}

	
	/**
	 * Prepare an SQL statement, this is only overridden for autocomplete purposes because this extended PDO returns a FCP_DB_PDOStatement
	 *
	 * @param string $sql
	 * @return FCP_DB_PDOStatement
	 */
	public function prepare( $sql )
	{
		return parent::prepare( $sql ) ;
	}
}