From a8d8ba0ccf96b492528bda4c253baa06a593e53e Mon Sep 17 00:00:00 2001 From: Robert Sesek Date: Sat, 22 Sep 2007 14:31:31 -0400 Subject: [PATCH] Database results are now independent objects as opposed to being a resource identifier, which greatly simplifies method calls * Db.php * DbMySql.php * DbMySqlI.php * DbPostgreSql.php * Template.php * UnitTest/DatabaseTestAbstract.php --- Db.php | 175 ++++++++++++++++++------------ DbMySql.php | 96 +++++++++------- DbMySqlI.php | 95 +++++++++------- DbPostgreSql.php | 108 +++++++++--------- Template.php | 2 +- UnitTest/DatabaseTestAbstract.php | 22 ++-- 6 files changed, 282 insertions(+), 216 deletions(-) diff --git a/Db.php b/Db.php index 9234a5e..56ce0b0 100644 --- a/Db.php +++ b/Db.php @@ -143,7 +143,7 @@ abstract class BSDb * * @param string Query string * - * @return integer Result + * @return BSDbResult Result object, or NULL */ public function query($string) { @@ -167,7 +167,11 @@ abstract class BSDb } } - return $this->result; + if (strtoupper(substr(trim($string), 0, 6)) == 'SELECT') + { + $class = get_class($this) . 'Result'; + return new $class($this->result); + } } /** @@ -245,6 +249,94 @@ abstract class BSDb */ protected abstract function _unescapeBinary($string); + // ################################################################### + /** + * Send a query and return the first row of the results + * + * @param string Query string + * @param string Result return function (in the database layer) + * + * @return mixed Results in variable formats + */ + public function queryFirst($string, $callback = 'fetchArray') + { + $resource = $this->query($string); + if ($resource) + { + $return = $resource->$callback(); + $resource->free(); + return $return; + } + else + { + return false; + } + } + + // ################################################################### + /** + * Returns the errror number + */ + public abstract function _errorNumber(); + + /** + * Returns the error string + */ + public abstract function _errorString(); + + // ################################################################### + /** + * Sends the command to start a transaction. This command should never + * be reached as it's always overridden + */ + public abstract function begin(); + + // ################################################################### + /** + * Sends the command to rollback to a given savepoint. This command + * should never be reached as it's always overridden + * + * @param string Named savepoint + */ + public abstract function rollback(); + + // ################################################################### + /** + * Sends the command to commit the entire transaction. This command + * should never be reached as it's always overridden + */ + public abstract function commit(); +} + +/** + * Database Result + * + * This class holds result information for a database result + * + * @author rsesek + * @copyright Copyright (c)2002 - 2007, Blue Static + * @package ISSO + * + */ +abstract class BSDbResult +{ + /** + * The result resource + * @var resource + */ + private $result; + + // ################################################################### + /** + * Sets the resource and returns a result object + * + * @param resource The result of the query + */ + public function __construct($result) + { + $this->result = $result; + } + // ################################################################### /** * Fetch the query result as an array @@ -254,9 +346,9 @@ abstract class BSDb * * @return array A row of the query result */ - public function fetchArray($result, $assoc = true) + public function fetchArray($assoc = true) { - return $this->{($assoc ? '_fetchAssocArray' : '_fetchRowArray')}($result); + return $this->{($assoc ? '_fetchAssocArray' : '_fetchRowArray')}($this->result); } /** @@ -285,9 +377,9 @@ abstract class BSDb * * @return object An object with the query result */ - public function fetchObject($result) + public function fetchObject() { - return $this->_fetchObject($result); + return $this->_fetchObject($this->result); } /** @@ -299,41 +391,15 @@ abstract class BSDb */ public abstract function _fetchObject($result); - // ################################################################### - /** - * Send a query and return the first row of the results - * - * @param string Query string - * @param string Result return function (in the database layer) - * - * @return mixed Results in variable formats - */ - public function queryFirst($string, $callback = 'fetchArray') - { - $resource = $this->query($string); - if ($resource) - { - $return = $this->$callback($resource); - $this->_freeResult($resource); - return $return; - } - else - { - return false; - } - } - // ################################################################### /** * Free the current query result * * @param integer Result */ - public function freeResult($result) + public function free() { $this->_freeResult($result); - $this->result = null; - $this->querystr = ''; } /** @@ -370,9 +436,9 @@ abstract class BSDb * * @return integer Number of rows */ - public function numRows($result) + public function size() { - return $this->_numRows($result); + return $this->_numRows($this->result); } /** @@ -392,9 +458,9 @@ abstract class BSDb * * @return integer Number of affected rows */ - public function affectedRows($result) + public function affectedRows() { - return $this->_affectedRows($result); + return $this->_affectedRows($this->result); } /** @@ -405,40 +471,6 @@ abstract class BSDb * @return integer Number of rows */ protected abstract function _affectedRows($result); - - // ################################################################### - /** - * Returns the errror number - */ - public abstract function _errorNumber(); - - /** - * Returns the error string - */ - public abstract function _errorString(); - - // ################################################################### - /** - * Sends the command to start a transaction. This command should never - * be reached as it's always overridden - */ - public abstract function begin(); - - // ################################################################### - /** - * Sends the command to rollback to a given savepoint. This command - * should never be reached as it's always overridden - * - * @param string Named savepoint - */ - public abstract function rollback(); - - // ################################################################### - /** - * Sends the command to commit the entire transaction. This command - * should never be reached as it's always overridden - */ - public abstract function commit(); } /** @@ -448,7 +480,6 @@ abstract class BSDb * * @author Blue Static * @copyright Copyright (c)2002 - 2007, Blue Static - * @version $Id$ * @package ISSO * */ diff --git a/DbMySql.php b/DbMySql.php index baf83ea..c5c0ed2 100644 --- a/DbMySql.php +++ b/DbMySql.php @@ -115,115 +115,127 @@ class BSDbMySql extends BSDb // ################################################################### /** - * Wrapper: mysql_fetch_assoc + * Starts a database transaction */ - protected function _fetchAssocArray($result) + public function begin() { - return mysql_fetch_assoc($result); + $this->query("BEGIN WORK"); } // ################################################################### /** - * Wrapper: mysql_fetch_row + * Reverts a transaction back to a given savepoint */ - protected function _fetchRowArray($result) + public function rollback() { - return mysql_fetch_row($result); + $this->query("ROLLBACK"); } // ################################################################### /** - * Wrapper: mysql_fetch_object + * Commits a database transaction */ - public function _fetchObject($result) + public function commit() { - return mysql_fetch_object($result); + $this->query("COMMIT"); } // ################################################################### /** - * Wrapper: mysql_free_result + * Returns the error number + * + * @return integer Error number */ - protected function _freeResult($result) + public function _errorNumber() { - mysql_free_result($result); + return mysql_errno($this->dblink); } // ################################################################### /** - * Wrapper: mysql_insert_id + * Returns the error string + * + * @return string Error string */ - protected function _insertId() + public function _errorString() { - return mysql_insert_id($this->dblink); + return mysql_error($this->dblink); } - +} + +/** + * Database Result + * + * This class holds result information for a database result + * + * @author rsesek + * @copyright Copyright (c)2002 - 2007, Blue Static + * @package ISSO + * + */ +class BSDbMySqlResult extends BSDbResult +{ // ################################################################### /** - * Wrapper: mysql_num_rows + * Wrapper: mysql_fetch_assoc */ - protected function _numRows($result) + protected function _fetchAssocArray($result) { - return mysql_num_rows($result); + return mysql_fetch_assoc($result); } // ################################################################### /** - * Wrapper: mysql_affected_rows + * Wrapper: mysql_fetch_row */ - protected function _affectedRows($result) + protected function _fetchRowArray($result) { - return mysql_affected_rows($this->dblink); + return mysql_fetch_row($result); } // ################################################################### /** - * Starts a database transaction + * Wrapper: mysql_fetch_object */ - public function begin() + public function _fetchObject($result) { - $this->query("BEGIN WORK"); + return mysql_fetch_object($result); } - // ################################################################### /** - * Reverts a transaction back to a given savepoint + * Wrapper: mysql_free_result */ - public function rollback() + protected function _freeResult($result) { - $this->query("ROLLBACK"); + mysql_free_result($result); } // ################################################################### /** - * Commits a database transaction + * Wrapper: mysql_insert_id */ - public function commit() + protected function _insertId() { - $this->query("COMMIT"); + return mysql_insert_id($this->dblink); } // ################################################################### /** - * Returns the error number - * - * @return integer Error number + * Wrapper: mysql_num_rows */ - public function _errorNumber() + protected function _numRows($result) { - return mysql_errno($this->dblink); + return mysql_num_rows($result); } // ################################################################### /** - * Returns the error string - * - * @return string Error string + * Wrapper: mysql_affected_rows */ - public function _errorString() + protected function _affectedRows($result) { - return mysql_error($this->dblink); + return mysql_affected_rows($this->dblink); } } diff --git a/DbMySqlI.php b/DbMySqlI.php index e697651..fb29745 100644 --- a/DbMySqlI.php +++ b/DbMySqlI.php @@ -87,114 +87,127 @@ class BSDbMySqlI extends BSDb // ################################################################### /** - * Wrapper: mysql_fetch_assoc + * Starts a database transaction */ - protected function _fetchAssocArray($result) + public function begin() { - return mysqli_fetch_assoc($result); + $this->query("START TRANSACTION"); } // ################################################################### /** - * Wrapper: mysql_fetch_row + * Reverts a transaction back to a given savepoint */ - protected function _fetchRowArray($result) + public function rollback() { - return mysqli_fetch_row($result); + $this->query("ROLLBACK"); } // ################################################################### /** - * Wrapper: mysql_fetch_object + * Commits a database transaction */ - public function _fetchObject($result) + public function commit() { - return mysqli_fetch_object($result); + $this->query("COMMIT"); } // ################################################################### /** - * Wrapper: mysql_free_result + * Returns the error number + * + * @return integer Error number */ - protected function _freeResult($result) + public function _errorNumber() { - mysqli_free_result($result); + return mysqli_errno($this->dblink); } // ################################################################### /** - * Wrapper: mysql_insert_id + * Returns the error string + * + * @return string Error string */ - protected function _insertId() + public function _errorString() { - return mysqli_insert_id($this->dblink); + return mysqli_error($this->dblink); } - +} + +/** + * Database Result + * + * This class holds result information for a database result + * + * @author rsesek + * @copyright Copyright (c)2002 - 2007, Blue Static + * @package ISSO + * + */ +class BSDbMySqlIResult extends BSDbResult +{ // ################################################################### /** - * Wrapper: mysql_num_rows + * Wrapper: mysql_fetch_assoc */ - protected function _numRows($result) + protected function _fetchAssocArray($result) { - return mysqli_num_rows($result); + return mysqli_fetch_assoc($result); } // ################################################################### /** - * Wrapper: mysql_affected_rows + * Wrapper: mysql_fetch_row */ - protected function _affectedRows($result) + protected function _fetchRowArray($result) { - return mysqli_affected_rows($this->dblink); + return mysqli_fetch_row($result); } // ################################################################### /** - * Starts a database transaction + * Wrapper: mysql_fetch_object */ - public function begin() + public function _fetchObject($result) { - $this->query("START TRANSACTION"); + return mysqli_fetch_object($result); } // ################################################################### /** - * Reverts a transaction back to a given savepoint + * Wrapper: mysql_free_result */ - public function rollback() + protected function _freeResult($result) { - $this->query("ROLLBACK"); + mysqli_free_result($result); } // ################################################################### /** - * Commits a database transaction + * Wrapper: mysql_insert_id */ - public function commit() + protected function _insertId() { - $this->query("COMMIT"); + return mysqli_insert_id($this->dblink); } // ################################################################### /** - * Returns the error number - * - * @return integer Error number + * Wrapper: mysql_num_rows */ - public function _errorNumber() + protected function _numRows($result) { - return mysqli_errno($this->dblink); + return mysqli_num_rows($result); } // ################################################################### /** - * Returns the error string - * - * @return string Error string + * Wrapper: mysql_affected_rows */ - public function _errorString() + protected function _affectedRows($result) { - return mysqli_error($this->dblink); + return mysqli_affected_rows($this->dblink); } } diff --git a/DbPostgreSql.php b/DbPostgreSql.php index be7233a..e9ced45 100644 --- a/DbPostgreSql.php +++ b/DbPostgreSql.php @@ -100,120 +100,130 @@ class BSDbPostgreSql extends BSDb // ################################################################### /** - * Overload: insertId + * Starts a database transaction */ - public function insertId($table, $field) - { - $temp = $this->queryFirst("SELECT last_value FROM {$table}_{$field}_seq"); - return $temp['last_value']; - } - - protected function _insertId() + public function start() { - // we never get here + $this->query("BEGIN"); } - + // ################################################################### /** - * Wrapper: pg_fetch_assoc + * Reverts a transaction back to a given savepoint */ - protected function _fetchAssocArray($result) + public function rollback() { - return pg_fetch_assoc($result); + $this->query("ROLLBACK"); } // ################################################################### /** - * Wrapper: pg_fetch_row + * Commits a database transaction */ - protected function _fetchRowArray($result) + public function commit() { - return pg_fetch_row($result); + $this->query("COMMIT"); } // ################################################################### /** - * Wrapper: pg_fetch_object + * Returns the error number + * + * @return integer Error number */ - public function _fetchObject($result) + public function _errorNumber() { - return pg_fetch_object($result); + return -1; } // ################################################################### /** - * Wrapper: pg_free_result + * Returns the error string + * + * @return string Error string */ - protected function _freeResult($result) + public function _errorString() { - pg_free_result($result); + return pg_last_error($this->dblink); } - +} + +/** + * Database Result + * + * This class holds result information for a database result + * + * @author rsesek + * @copyright Copyright (c)2002 - 2007, Blue Static + * @package ISSO + * + */ +class BSDbPostgreSqlResult extends BSDbResult +{ // ################################################################### /** - * Wrapper: pg_num_rows + * Overload: insertId */ - protected function _numRows($result) + public function insertId($table, $field) { - return pg_num_rows($result); + return $this->queryFirst("SELECT last_value FROM {$table}_{$field}_seq")->fetchObject()->last_value; } + protected function _insertId() + {} + // ################################################################### /** - * Wrapper: pg_affected_rows + * Wrapper: pg_fetch_assoc */ - protected function _affectedRows($result) + protected function _fetchAssocArray($result) { - return pg_affected_rows($result); + return pg_fetch_assoc($result); } // ################################################################### /** - * Starts a database transaction + * Wrapper: pg_fetch_row */ - public function start() + protected function _fetchRowArray($result) { - $this->query("BEGIN"); + return pg_fetch_row($result); } - + // ################################################################### /** - * Reverts a transaction back to a given savepoint + * Wrapper: pg_fetch_object */ - public function rollback() + public function _fetchObject($result) { - $this->query("ROLLBACK"); + return pg_fetch_object($result); } // ################################################################### /** - * Commits a database transaction + * Wrapper: pg_free_result */ - public function commit() + protected function _freeResult($result) { - $this->query("COMMIT"); + pg_free_result($result); } // ################################################################### /** - * Returns the error number - * - * @return integer Error number + * Wrapper: pg_num_rows */ - public function _errorNumber() + protected function _numRows($result) { - return -1; + return pg_num_rows($result); } // ################################################################### /** - * Returns the error string - * - * @return string Error string + * Wrapper: pg_affected_rows */ - public function _errorString() + protected function _affectedRows($result) { - return pg_last_error($this->dblink); + return pg_affected_rows($result); } } diff --git a/Template.php b/Template.php index 916d1a3..554e9bb 100644 --- a/Template.php +++ b/Template.php @@ -164,7 +164,7 @@ class BSTemplate { $db =& BSApp::Registry()->getType('Db'); $cache = $db->query("SELECT * FROM {$this->dbCacheTable} WHERE filename IN ('" . implode("', '", $namearray) . "')"); - while ($tpl = $db->fetchArray($cache)) + while ($tpl = $cache->fetchArray()) { $time = filemtime($this->templateDir . $tpl['filename'] . '.' . $this->extension); $template = $tpl['template']; diff --git a/UnitTest/DatabaseTestAbstract.php b/UnitTest/DatabaseTestAbstract.php index e5285f0..fee3455 100644 --- a/UnitTest/DatabaseTestAbstract.php +++ b/UnitTest/DatabaseTestAbstract.php @@ -31,13 +31,13 @@ abstract class DatabaseTestAbstract extends PHPUnit_Framework_TestCase { $res = $this->fixture->query("SELECT * FROM test WHERE textstuff = 'foo'"); - $row = $this->fixture->fetchArray($res); + $row = $res->fetchArray(); $this->assertNotNull($row['textstuff']); - $row = $this->fixture->fetchArray($res); + $row = $res->fetchArray(); $this->assertNotNull($row['textstuff']); - $row = $this->fixture->fetchArray($res); + $row = $res->fetchArray(); $this->assertNull($row['textstuff']); } catch (BSDbException $e) @@ -65,7 +65,7 @@ abstract class DatabaseTestAbstract extends PHPUnit_Framework_TestCase try { $res = $this->fixture->query("SELECT * FROM test"); - $obj = $this->fixture->fetchObject($res); + $obj = $res->fetchObject(); $this->assertNotNull($obj); $this->assertEquals('foo', $obj->textstuff); @@ -121,10 +121,10 @@ abstract class DatabaseTestAbstract extends PHPUnit_Framework_TestCase $this->fixture->query("INSERT INTO test (textstuff) VALUES ('123'), ('456'), ('789')"); $res = $this->fixture->query("SELECT * FROM test"); - $this->assertEquals(3, $this->fixture->numRows($res)); + $this->assertEquals(3, $res->numRows()); $res = $this->fixture->query("SELECT * FROM test WHERE textstuff = '--invalid value--'"); - $this->assertEquals(0, $this->fixture->numRows($res)); + $this->assertEquals(0, $res->numRows()); } catch (BSDbException $e) { @@ -139,10 +139,10 @@ abstract class DatabaseTestAbstract extends PHPUnit_Framework_TestCase $this->fixture->query("INSERT INTO test (textstuff) VALUES ('123'), ('456'), ('123')"); $res = $this->fixture->query("UPDATE test SET textstuff = 'abc' WHERE textstuff = '123'"); - $this->assertEquals(2, $this->fixture->affectedRows($res)); + $this->assertEquals(2, $res->affectedRows()); $res = $this->fixture->query("SELECT * FROM test WHERE textstuff = 'abc'"); - $this->assertEquals(2, $this->fixture->numRows($res)); + $this->assertEquals(2, $res->numRows()); } catch (BSDbException $e) { @@ -170,17 +170,17 @@ abstract class DatabaseTestAbstract extends PHPUnit_Framework_TestCase $this->fixture->commit(); $res = $this->fixture->query("SELECT * FROM test WHERE textstuff = 'foo'"); - $this->assertEquals(1, $this->fixture->numRows($res)); + $this->assertEquals(1, $res->numRows()); $this->fixture->begin(); $this->fixture->query("UPDATE test SET textstuff = 'abc'"); $this->fixture->rollback(); $res = $this->fixture->query("SELECT * FROM test WHERE textstuff = 'abc'"); - $this->assertEquals(0, $this->fixture->numRows($res)); + $this->assertEquals(0, $res->numRows()); $res = $this->fixture->query("SELECT * FROM test WHERE textstuff = 'foo'"); - $this->assertEquals(1, $this->fixture->numRows($res)); + $this->assertEquals(1, $res->numRows()); } } -- 2.22.5