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'));
}
}