Database Abstraction - simplicity and PDO
Posted: Wed Feb 14, 2007 9:21 am
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).
Not much for testing - the interface is a close fit with PDO so there's little point testing all the methods at this stage.
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
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;
}
}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'));
}
}