cvs: php4 /pear/DB common.php ibase.php msql.php mssql.php mysql.php oci8.php odbc.php pgsql.php storage.php sybase.php /pear/DB/tests/mysql 008.phpt

ssb Sat Dec 9 19:18:57 2000 EDT Modified files: /php4/pear/DB common.php ibase.php msql.php mssql.php mysql.php oci8.php odbc.php pgsql.php storage.php sybase.php /php4/pear/DB/tests/mysql 008.phpt Log: * added setOption/getOption to DB_common. "optimize" is the first supported option, possible values are "performance" (default) and "portability". * added modifyQuery to DB_common (does nothing) and an implementation in DB_mysql that patches "DELETE FROM table" queries in "optimize portability" mode so affectedRows returns the right value

Index: php4/pear/DB/common.php diff -u php4/pear/DB/common.php:1.32 php4/pear/DB/common.php:1.33 --- php4/pear/DB/common.php:1.32 Sat Dec 9 18:31:26 2000 +++ php4/pear/DB/common.php Sat Dec 9 19:18:56 2000 @@ -40,8 +40,9 @@ var $error_callback; var $last_query = ''; var $fetchmode = DB_FETCHMODE_ORDERED; - var $seq_table = "db_sequence"; - var $options = array(); + var $options = array( + 'optimize' => 'performance', // 'performance' or 'portability' + ); // }}} // {{{ toString() @@ -282,6 +283,29 @@ } // }}} + // {{{ setOption() + + function setOption($option, $value) + { + if (isset($this->options[$option])) { + $this->options[$option] = $value; + return DB_OK; + } + return $this->raiseError("unknown option $option"); + } + + // }}} + // {{{ getOption() + + function getOption($option) + { + if (isset($this->options[$option])) { + return $this->options[$option]; + } + return $this->raiseError("unknown option $option"); + } + + // }}} // {{{ prepare() /** @@ -321,7 +345,6 @@ function execute($stmt, $data = false) { $realquery = $this->executeEmulateQuery($stmt, $data); - $this->last_query = $realquery; $result = $this->simpleQuery($realquery); if (DB::isError($result) || $result === DB_OK) { return $result; Index: php4/pear/DB/ibase.php diff -u php4/pear/DB/ibase.php:1.13 php4/pear/DB/ibase.php:1.14 --- php4/pear/DB/ibase.php:1.13 Sat Dec 9 18:07:00 2000 +++ php4/pear/DB/ibase.php Sat Dec 9 19:18:56 2000 @@ -16,7 +16,7 @@ // | Authors: Sterling Hughes <sterling@php.net> | // +----------------------------------------------------------------------+ // -// $Id: ibase.php,v 1.13 2000/12/10 02:07:00 ssb Exp $ +// $Id: ibase.php,v 1.14 2000/12/10 03:18:56 ssb Exp $ // // Database independent query interface definition for PHP's Interbase // extension. @@ -77,10 +77,11 @@ return @ibase_close($this->connection); } - function simpleQuery($stmt) + function simpleQuery($query) { $this->last_query = $query; - $result = @ibase_query($this->connection, $stmt); + $query = $this->modifyQuery($query); + $result = @ibase_query($this->connection, $query); if (!$result) { return $this->raiseError(); } @@ -89,7 +90,7 @@ } // Determine which queries that should return data, and which // should return an error code only. - return DB::isManip($stmt) ? DB_OK : $result; + return DB::isManip($query) ? DB_OK : $result; } function &fetchRow($result, $fetchmode=DB_FETCHMODE_DEFAULT) @@ -147,6 +148,7 @@ function prepare($query) { $this->last_query = $query; + $query = $this->modifyQuery($query); $stmt = ibase_prepare($query); $this->manip_query[(int)$stmt] = DB::isManip($query); return $stmt; Index: php4/pear/DB/msql.php diff -u php4/pear/DB/msql.php:1.12 php4/pear/DB/msql.php:1.13 --- php4/pear/DB/msql.php:1.12 Sat Dec 9 18:07:00 2000 +++ php4/pear/DB/msql.php Sat Dec 9 19:18:56 2000 @@ -16,7 +16,7 @@ // | Authors: Sterling Hughes <sterling@php.net> | // +----------------------------------------------------------------------+ // -// $Id: msql.php,v 1.12 2000/12/10 02:07:00 ssb Exp $ +// $Id: msql.php,v 1.13 2000/12/10 03:18:56 ssb Exp $ // // Database independent query interface definition for PHP's Mini-SQL // extension. @@ -24,8 +24,8 @@ require_once 'DB/common.php'; -class DB_msql extends DB_common { - +class DB_msql extends DB_common +{ var $connection; var $phptype, $dbsyntax; var $prepare_tokens = array(); @@ -79,16 +79,17 @@ return @msql_close($this->connection); } - function simpleQuery($stmt) + function simpleQuery($query) { - $this->last_query = $stmt; - $result = @msql_query($stmt, $this->connection); + $this->last_query = $query; + $query = $this->modifyQuery($query); + $result = @msql_query($query, $this->connection); if (!$result) { return $this->raiseError(); } // Determine which queries that should return data, and which // should return an error code only. - return DB::isManip($stmt) ? DB_OK : $result; + return DB::isManip($query) ? DB_OK : $result; } function &fetchRow($result, $fetchmode=DB_FETCHMODE_DEFAULT) Index: php4/pear/DB/mssql.php diff -u php4/pear/DB/mssql.php:1.15 php4/pear/DB/mssql.php:1.16 --- php4/pear/DB/mssql.php:1.15 Sat Dec 9 18:07:00 2000 +++ php4/pear/DB/mssql.php Sat Dec 9 19:18:56 2000 @@ -16,7 +16,7 @@ // | Authors: Sterling Hughes <sterling@php.net> | // +----------------------------------------------------------------------+ // -// $Id: mssql.php,v 1.15 2000/12/10 02:07:00 ssb Exp $ +// $Id: mssql.php,v 1.16 2000/12/10 03:18:56 ssb Exp $ // // Database independent query interface definition for PHP's Microsoft SQL Server // extension. @@ -24,8 +24,8 @@ require_once 'DB/common.php'; -class DB_mssql extends DB_common { - +class DB_mssql extends DB_common +{ var $connection; var $phptype, $dbsyntax; var $prepare_tokens = array(); @@ -51,7 +51,7 @@ $dsninfo = DB::parseDSN($dsn); } if (!$dsninfo || !$dsninfo['phptype']) { - return $this->raiseError(); + return $this->raiseError(mssql_get_last_message()); } $user = $dsninfo['username']; @@ -68,7 +68,7 @@ if ($dsninfo['database']) { @mssql_select_db($dsninfo['database'], $conn); } else { - return $this->raiseError(); + return $this->raiseError(mssql_get_last_message()); } $this->connection = $conn; return DB_OK; @@ -79,16 +79,17 @@ return @mssql_close($this->connection); } - function simpleQuery($stmt) + function simpleQuery($query) { $this->last_query = $query; - $result = @mssql_query($stmt, $this->connection); + $query = $this->modifyQuery($query); + $result = @mssql_query($query, $this->connection); if (!$result) { - return $this->raiseError(); + return $this->raiseError(mssql_get_last_message()); } // Determine which queries that should return data, and which // should return an error code only. - return DB::isManip($stmt) ? DB_OK : $result; + return DB::isManip($query) ? DB_OK : $result; } function &fetchRow($result, $fetchmode=DB_FETCHMODE_DEFAULT) @@ -102,7 +103,7 @@ $row = @mssql_fetch_row($result); } if (!$row) { - return $this->raiseError(); + return $this->raiseError(mssql_get_last_message()); } return $row; } @@ -118,7 +119,7 @@ $ar = @mssql_fetch_row($result); } if (!$ar) { - return $this->raiseError(); + return $this->raiseError(mssql_get_last_message()); } return DB_OK; } @@ -140,7 +141,7 @@ { $cols = @mssql_num_fields($result); if (!$cols) { - return $this->raiseError(); + return $this->raiseError(mssql_get_last_message()); } return $cols; } @@ -149,7 +150,7 @@ { $rows = @mssql_num_rows($result); if (!$rows) { - return $this->raiseError(); + return $this->raiseError(mssql_get_last_message()); } return $rows; } Index: php4/pear/DB/mysql.php diff -u php4/pear/DB/mysql.php:1.39 php4/pear/DB/mysql.php:1.40 --- php4/pear/DB/mysql.php:1.39 Sat Dec 9 18:07:00 2000 +++ php4/pear/DB/mysql.php Sat Dec 9 19:18:56 2000 @@ -153,23 +153,22 @@ * Send a query to MySQL and return the results as a MySQL resource * identifier. * - * @param $query the SQL query + * @param the SQL query * * @access public * - * @return int returns a valid MySQL result for successful SELECT + * @return mixed returns a valid MySQL result for successful SELECT * queries, DB_OK for other successful queries. A DB error is * returned on failure. */ function simpleQuery($query) { - $this->last_query = $query; - $result = mysql_query( $query, $this->connection ); - + $this->last_query = $query; + $query = $this->modifyQuery($query); + $result = mysql_query($query, $this->connection); if (!$result) { return $this->raiseError($this->errorCode(mysql_errno($this->connection))); } - // Determine which queries that should return data, and which // should return an error code only. return DB::isManip($query) ? DB_OK : $result; @@ -417,6 +416,19 @@ } // }}} + + function modifyQuery($query) + { + if ($this->options['optimize'] == 'portability') { + // "DELETE FROM table" gives 0 affected rows in MySQL. + // This little hack lets you know how many rows were deleted. + if (preg_match('/^\s*DELETE\s+FROM\s+(\S+)\s*$/i', $query)) { + $query = preg_replace('/^\s*DELETE\s+FROM\s+(\S+)\s*$/', + 'DELETE FROM \1 WHERE 1=1', $query); + } + } + return $query; + } // TODO/wishlist: // simpleFetch Index: php4/pear/DB/oci8.php diff -u php4/pear/DB/oci8.php:1.12 php4/pear/DB/oci8.php:1.13 --- php4/pear/DB/oci8.php:1.12 Sat Dec 9 18:07:00 2000 +++ php4/pear/DB/oci8.php Sat Dec 9 19:18:56 2000 @@ -30,7 +30,8 @@ require_once 'DB/common.php'; -class DB_oci8 extends DB_common { +class DB_oci8 extends DB_common +{ // {{{ properties var $connection; @@ -41,10 +42,10 @@ var $last_stmt = false; // }}} - // {{{ constructor - function DB_oci8() { + function DB_oci8() + { $this->DB_common(); $this->phptype = 'oci8'; $this->dbsyntax = 'oci8'; @@ -57,7 +58,6 @@ } // }}} - // {{{ connect() /** @@ -69,7 +69,8 @@ * * @return int DB_OK on success, a DB error code on failure */ - function connect($dsn, $persistent = false) { + function connect($dsn, $persistent = false) + { if (is_array($dsn)) { $dsninfo = &$dsn; } else { @@ -105,7 +106,8 @@ * * @return bool TRUE on success, FALSE if not connected. */ - function disconnect() { + function disconnect() + { return OCILogOff($this->connection); } @@ -122,8 +124,10 @@ * queries, DB_OK for other successful queries. A DB error code * is returned on failure. */ - function simpleQuery($query) { + function simpleQuery($query) + { $this->last_query = $query; + $query = $this->modifyQuery($query); $result = OCIParse($this->connection, $query); if (!$result) { return $this->raiseError(); @@ -155,7 +159,8 @@ * @return int an array on success, a DB error code on failure, NULL * if there is no more data */ - function &fetchRow($result, $fetchmode = DB_FETCHMODE_DEFAULT) { + function &fetchRow($result, $fetchmode = DB_FETCHMODE_DEFAULT) + { if ($fetchmode == DB_FETCHMODE_DEFAULT) { $fetchmode = $this->fetchmode; } @@ -185,7 +190,8 @@ * * @return int DB_OK on success, a DB error code on failure */ - function fetchInto($result, &$arr, $fetchmode = DB_FETCHMODE_DEFAULT) { + function fetchInto($result, &$arr, $fetchmode = DB_FETCHMODE_DEFAULT) + { if ($fetchmode == DB_FETCHMODE_DEFAULT) { $fetchmode = $this->fetchmode; } @@ -210,7 +216,8 @@ * * @return bool TRUE on success, FALSE if $result is invalid */ - function freeResult($result) { + function freeResult($result) + { if (is_resource($result)) { return OCIFreeStatement($result); } @@ -232,7 +239,8 @@ * * @return int the number of columns per row in $result */ - function numCols($result) { + function numCols($result) + { $cols = OCINumCols($result); if (!$cols) { return $this->raiseError(); @@ -251,7 +259,8 @@ * * @return int native oci8 error code */ - function errorNative() { + function errorNative() + { $error=OCIError($this->connection); if (is_array($error)) { return $error['code']; @@ -269,7 +278,8 @@ * * @return DB statement resource */ - function prepare($query) { + function prepare($query) + { $tokens = split('[\&\?]', $query); $token = 0; $types = array(); @@ -283,15 +293,16 @@ break; } } - $binds=sizeof($tokens)-1; - for ($i=0;$i<$binds;$i++) { - $newquery.=$tokens[$i].":bind".$i; + $binds = sizeof($tokens) - 1; + for ($i = 0; $i < $binds; $i++) { + $newquery .= $tokens[$i] . ":bind" . $i; } - $newquery.=$tokens[$i]; + $newquery .= $tokens[$i]; $this->last_query = $query; - $stmt=OCIParse($this->connection,$newquery); + $newquery = $this->modifyQuery($newquery); + $stmt=OCIParse($this->connection, $newquery); $this->prepare_types[$stmt] = $types; - $this->manip_query[$stmt] = DB::isManip($newquery); + $this->manip_query[$stmt] = DB::isManip($query); return $stmt; } @@ -308,7 +319,8 @@ * SELECT queries, DB_OK for other successful queries. A DB error * code is returned on failure. */ - function execute($stmt, $data = false) { + function execute($stmt, $data = false) + { $types=&$this->prepare_types[$stmt]; if (($size=sizeof($types))!=sizeof($data)) { return $this->raiseError(); @@ -358,7 +370,8 @@ * * @param $onoff true/false whether to autocommit */ - function autoCommit($onoff = false) { + function autoCommit($onoff = false) + { if (!$onoff) { $this->autoCommit=0; } @@ -376,7 +389,8 @@ * * @return DB_ERROR or DB_OK */ - function commit() { + function commit() + { $result = OCICommit($this->connection); if (!$result) { return $this->raiseError(); @@ -392,7 +406,8 @@ * * @return DB_ERROR or DB_OK */ - function rollback() { + function rollback() + { $result = OCIRollback($this->connection); if (!$result) { return $this->raiseError(); @@ -409,7 +424,8 @@ * * @return number of rows affected by the last query or DB_ERROR */ - function affectedRows() { + function affectedRows() + { if ($this->last_stmt === false) { return $this->raiseError(); } Index: php4/pear/DB/odbc.php diff -u php4/pear/DB/odbc.php:1.20 php4/pear/DB/odbc.php:1.21 --- php4/pear/DB/odbc.php:1.20 Sat Dec 9 18:31:26 2000 +++ php4/pear/DB/odbc.php Sat Dec 9 19:18:56 2000 @@ -166,6 +166,7 @@ function simpleQuery($query) { $this->last_query = $query; + $query = $this->modifyQuery($query); $result = odbc_exec($this->connection, $query); if (!$result) { return $this->raiseError(); // XXX ERRORMSG Index: php4/pear/DB/pgsql.php diff -u php4/pear/DB/pgsql.php:1.22 php4/pear/DB/pgsql.php:1.23 --- php4/pear/DB/pgsql.php:1.22 Sat Dec 9 18:07:00 2000 +++ php4/pear/DB/pgsql.php Sat Dec 9 19:18:56 2000 @@ -150,6 +150,7 @@ function simpleQuery($query) { $this->last_query = $query; + $query = $this->modifyQuery($query); $result = @pg_exec($this->connection, $query); if (!$result) { return $this->raiseError(pg_errormessage($this->connection)); @@ -375,7 +376,6 @@ function execute($stmt, $data = false) { $realquery = $this->executeEmulateQuery($stmt, $data); - $this->last_query = $realquery; $result = $this->simpleQuery($realquery); if (DB::isError($result) || $result === DB_OK) { return $result; Index: php4/pear/DB/storage.php diff -u php4/pear/DB/storage.php:1.9 php4/pear/DB/storage.php:1.10 --- php4/pear/DB/storage.php:1.9 Mon Sep 11 17:27:50 2000 +++ php4/pear/DB/storage.php Sat Dec 9 19:18:56 2000 @@ -35,7 +35,8 @@ } } -class DB_storage { +class DB_storage +{ /** the name of the table (or view, if the backend database supports updates in views) we hold data from */ var $_table = null; Index: php4/pear/DB/sybase.php diff -u php4/pear/DB/sybase.php:1.11 php4/pear/DB/sybase.php:1.12 --- php4/pear/DB/sybase.php:1.11 Sat Dec 9 18:07:00 2000 +++ php4/pear/DB/sybase.php Sat Dec 9 19:18:56 2000 @@ -16,7 +16,7 @@ // | Authors: Sterling Hughes <sterling@php.net> | // +----------------------------------------------------------------------+ // -// $Id: sybase.php,v 1.11 2000/12/10 02:07:00 ssb Exp $ +// $Id: sybase.php,v 1.12 2000/12/10 03:18:56 ssb Exp $ // // Database independent query interface definition for PHP's Sybase // extension. @@ -80,16 +80,17 @@ // }}} // {{{ simpleQuery() - function simpleQuery($stmt) + function simpleQuery($query) { - $this->last_query = $stmt; - $result = @sybase_query($stmt, $this->connection); + $this->last_query = $query; + $query = $this->modifyQuery($query); + $result = @sybase_query($query, $this->connection); if (!$result) { return $this->raiseError(); } // Determine which queries that should return data, and which // should return an error code only. - return DB::isManip($stmt) ? DB_OK : $result; + return DB::isManip($query) ? DB_OK : $result; } // }}} Index: php4/pear/DB/tests/mysql/008.phpt diff -u php4/pear/DB/tests/mysql/008.phpt:1.2 php4/pear/DB/tests/mysql/008.phpt:1.3 --- php4/pear/DB/tests/mysql/008.phpt:1.2 Thu Dec 7 16:24:28 2000 +++ php4/pear/DB/tests/mysql/008.phpt Sat Dec 9 19:18:57 2000 @@ -16,10 +16,18 @@ $dbh->query("INSERT INTO phptest (a,b) VALUES(1, 'test')"); $dbh->query("INSERT INTO phptest (a,b) VALUES(2, 'test')"); $dbh->query("DELETE FROM phptest"); -printf("%d after delete all\n", $dbh->affectedRows()); +printf("%d after delete all (optimize=%s)\n", $dbh->affectedRows(), + $dbh->getOption("optimize")); +$dbh->query("INSERT INTO phptest (a,b) VALUES(1, 'test')"); +$dbh->query("INSERT INTO phptest (a,b) VALUES(2, 'test')"); +$dbh->setOption("optimize", "portability"); +$dbh->query("DELETE FROM phptest"); +printf("%d after delete all (optimize=%s)\n", $dbh->affectedRows(), + $dbh->getOption("optimize")); ?> --EXPECT-- 1 after insert 0 after select 2 after delete -0 after delete all +0 after delete all (optimize=performance) +2 after delete all (optimize=portability)