From d06acd85cdfff5411474b46afee36fb77baa1200 Mon Sep 17 00:00:00 2001 From: Andrey Andreev Date: Fri, 25 May 2012 00:29:09 +0300 Subject: Added update_batch() support for PostgreSQL (issue #356) --- system/database/DB_utility.php | 4 +-- system/database/drivers/postgre/postgre_driver.php | 41 ++++++++++++++++++++++ user_guide_src/source/changelog.rst | 2 ++ 3 files changed, 45 insertions(+), 2 deletions(-) diff --git a/system/database/DB_utility.php b/system/database/DB_utility.php index 587dfdc01..cb97ff448 100644 --- a/system/database/DB_utility.php +++ b/system/database/DB_utility.php @@ -212,7 +212,7 @@ abstract class CI_DB_utility extends CI_DB_forge { $out = rtrim($out).$newline; // Next blast through the result array and build out the rows - foreach ($query->result_array() as $row) + while ($row = $query->unbuffered_row('array')) { foreach ($row as $item) { @@ -258,7 +258,7 @@ abstract class CI_DB_utility extends CI_DB_forge { // Generate the result $xml = '<'.$root.'>'.$newline; - foreach ($query->result_array() as $row) + while ($row = $query->unbuffered_row()) { $xml .= $tab.'<'.$element.'>'.$newline; foreach ($row as $key => $val) diff --git a/system/database/drivers/postgre/postgre_driver.php b/system/database/drivers/postgre/postgre_driver.php index 0ddfd0abe..30689cc70 100644 --- a/system/database/drivers/postgre/postgre_driver.php +++ b/system/database/drivers/postgre/postgre_driver.php @@ -538,6 +538,47 @@ class CI_DB_postgre_driver extends CI_DB { // -------------------------------------------------------------------- + /** + * Update_Batch statement + * + * Generates a platform-specific batch update string from the supplied data + * + * @param string the table name + * @param array the update data + * @param array the where clause + * @return string + */ + protected function _update_batch($table, $values, $index, $where = NULL) + { + $ids = array(); + foreach ($values as $key => $val) + { + $ids[] = $val[$index]; + + foreach (array_keys($val) as $field) + { + if ($field != $index) + { + $final[$field][] = 'WHEN '.$val[$index].' THEN '.$val[$field]; + } + } + } + + $cases = ''; + foreach ($final as $k => $v) + { + $cases .= $k.' = (CASE '.$k."\n" + .implode("\n", $v)."\n" + .'ELSE '.$k.' END), '; + } + + return 'UPDATE '.$table.' SET '.substr($cases, 0, -2) + .' WHERE '.(($where != '' && count($where) > 0) ? implode(' ', $where).' AND ' : '') + .$index.' IN('.implode(',', $ids).')'; + } + + // -------------------------------------------------------------------- + /** * Delete statement * diff --git a/user_guide_src/source/changelog.rst b/user_guide_src/source/changelog.rst index f21f1266d..5407fb05e 100644 --- a/user_guide_src/source/changelog.rst +++ b/user_guide_src/source/changelog.rst @@ -75,6 +75,7 @@ Release Date: Not Released - Added db_set_charset() support. - Added _optimize_table() support for the :doc:`Database Utility Class ` (rebuilds table indexes). - Added boolean data type support in escape(). + - Added update_batch() support. - Added a constructor to the DB_result class and moved all driver-specific properties and logic out of the base DB_driver class to allow better abstraction. - Removed limit() and order_by() support for UPDATE and DELETE queries in PostgreSQL driver. Postgres does not support those features. - Removed protect_identifiers() and renamed internal method _protect_identifiers() to it instead - it was just an alias. @@ -218,6 +219,7 @@ Bug fixes for 3.0 - Fixed a bug (#44, #110) - :doc:`Upload library `'s clean_file_name() method didn't clear '!' and '#' characters. - Fixed a bug (#121) - ``CI_DB_result::row()`` returned an array when there's no actual result to be returned. - Fixed a bug (#319) - SQLSRV's affected_rows() method failed due to a scrollable cursor being created for write-type queries. +- Fixed a bug (#356) - PostgreSQL driver didn't have an _update_batch() method, which resulted in fatal error being triggered when update_batch() is used with it. Version 2.1.1 ============= -- cgit v1.2.3-24-g4f1b From 21cb2d32edd595a38189cdba137e694c3a22e1f0 Mon Sep 17 00:00:00 2001 From: Andrey Andreev Date: Fri, 25 May 2012 01:01:06 +0300 Subject: Fix issue #136 (MySQL escape_like_str()) --- system/database/drivers/mysql/mysql_driver.php | 6 ++++-- system/database/drivers/mysqli/mysqli_driver.php | 6 ++++-- user_guide_src/source/changelog.rst | 2 +- 3 files changed, 9 insertions(+), 5 deletions(-) diff --git a/system/database/drivers/mysql/mysql_driver.php b/system/database/drivers/mysql/mysql_driver.php index 161f99541..d801a9aaf 100644 --- a/system/database/drivers/mysql/mysql_driver.php +++ b/system/database/drivers/mysql/mysql_driver.php @@ -47,7 +47,7 @@ class CI_DB_mysql_driver extends CI_DB { // clause and character used for LIKE escape sequences - not used in MySQL protected $_like_escape_str = ''; - protected $_like_escape_chr = ''; + protected $_like_escape_chr = '\\'; /** * The syntax to count rows is slightly different across different @@ -291,7 +291,9 @@ class CI_DB_mysql_driver extends CI_DB { // escape LIKE condition wildcards if ($like === TRUE) { - return str_replace(array('%', '_'), array('\\%', '\\_'), $str); + return str_replace(array($this->_like_escape_chr, '%', '_'), + array($this->_like_escape_chr.$this->_like_escape_chr, $this->_like_escape_chr.'%', $this->_like_escape_chr.'_'), + $str); } return $str; diff --git a/system/database/drivers/mysqli/mysqli_driver.php b/system/database/drivers/mysqli/mysqli_driver.php index 9261883f5..61761e0c6 100644 --- a/system/database/drivers/mysqli/mysqli_driver.php +++ b/system/database/drivers/mysqli/mysqli_driver.php @@ -47,7 +47,7 @@ class CI_DB_mysqli_driver extends CI_DB { // clause and character used for LIKE escape sequences - not used in MySQL protected $_like_escape_str = ''; - protected $_like_escape_chr = ''; + protected $_like_escape_chr = '\\'; /** * The syntax to count rows is slightly different across different @@ -291,7 +291,9 @@ class CI_DB_mysqli_driver extends CI_DB { // escape LIKE condition wildcards if ($like === TRUE) { - return str_replace(array('%', '_'), array('\\%', '\\_'), $str); + return str_replace(array($this->_like_escape_chr, '%', '_'), + array($this->_like_escape_chr.$this->_like_escape_chr, $this->_like_escape_chr.'%', $this->_like_escape_chr.'_'), + $str); } return $str; diff --git a/user_guide_src/source/changelog.rst b/user_guide_src/source/changelog.rst index 5407fb05e..4b8a0f2d3 100644 --- a/user_guide_src/source/changelog.rst +++ b/user_guide_src/source/changelog.rst @@ -195,7 +195,7 @@ Bug fixes for 3.0 - Fixed a bug in the :doc:`Session Library ` where a PHP E_NOTICE error was triggered by _unserialize() due to results from databases such as MSSQL and Oracle being space-padded on the right. - Fixed a bug (#501) - set_rules() to check if the request method is not 'POST' before aborting, instead of depending on count($_POST) in the :doc:`Form Validation Library `. - Fixed a bug (#940) - csrf_verify() used to set the CSRF cookie while processing a POST request with no actual POST data, which resulted in validating a request that should be considered invalid. -- Fixed a bug in PostgreSQL's escape_str() where it didn't properly escape LIKE wild characters. +- Fixed a bug (#136) - PostgreSQL, MySQL and MySQLi's escape_str() method didn't properly escape LIKE wild characters. - Fixed a bug in the library loader where some PHP versions wouldn't execute the class constructor. - Fixed a bug (#88) - An unexisting property was used for configuration of the Memcache cache driver. - Fixed a bug (#14) - create_database() method in the :doc:`Database Forge Library ` didn't utilize the configured database character set. -- cgit v1.2.3-24-g4f1b From def568fcc8586db7685c4a1c2efd14c3cd75c8ad Mon Sep 17 00:00:00 2001 From: Andrey Andreev Date: Fri, 25 May 2012 01:06:54 +0300 Subject: Apply fix for issue #136 on PDO+MySQL as well --- system/database/drivers/pdo/pdo_driver.php | 6 +++--- 1 file changed, 3 insertions(+), 3 deletions(-) diff --git a/system/database/drivers/pdo/pdo_driver.php b/system/database/drivers/pdo/pdo_driver.php index e38c1145c..4784fc65b 100644 --- a/system/database/drivers/pdo/pdo_driver.php +++ b/system/database/drivers/pdo/pdo_driver.php @@ -79,13 +79,13 @@ class CI_DB_pdo_driver extends CI_DB { // clause and character used for LIKE escape sequences // this one depends on the driver being used - if ($this->pdodriver == 'mysql') + if ($this->pdodriver === 'mysql') { $this->_escape_char = '`'; $this->_like_escape_str = ''; - $this->_like_escape_chr = ''; + $this->_like_escape_chr = '\\'; } - elseif ($this->pdodriver == 'odbc') + elseif ($this->pdodriver === 'odbc') { $this->_like_escape_str = " {escape '%s'} "; } -- cgit v1.2.3-24-g4f1b From 59d6b4fc4f4d7d5265b59cfd8c0f68f885083f69 Mon Sep 17 00:00:00 2001 From: Andrey Andreev Date: Fri, 25 May 2012 02:08:00 +0300 Subject: Alter LIKE escaping tests again --- tests/codeigniter/database/DB_driver_test.php | 4 +--- tests/codeigniter/database/DB_test.php | 4 +--- tests/codeigniter/database/query_builder/escape_test.php | 16 ++++++---------- 3 files changed, 8 insertions(+), 16 deletions(-) diff --git a/tests/codeigniter/database/DB_driver_test.php b/tests/codeigniter/database/DB_driver_test.php index fb40f0608..9e16e29b4 100644 --- a/tests/codeigniter/database/DB_driver_test.php +++ b/tests/codeigniter/database/DB_driver_test.php @@ -2,8 +2,6 @@ class DB_driver_test extends CI_TestCase { - // ------------------------------------------------------------------------ - public function test_initialize() { $config = Mock_Database_DB::config(DB_DRIVER); @@ -32,5 +30,5 @@ class DB_driver_test extends CI_TestCase { { return new Mock_Database_Drivers_Postgre($config); } - + } \ No newline at end of file diff --git a/tests/codeigniter/database/DB_test.php b/tests/codeigniter/database/DB_test.php index 9b93e223d..d5c0dea08 100644 --- a/tests/codeigniter/database/DB_test.php +++ b/tests/codeigniter/database/DB_test.php @@ -2,8 +2,6 @@ class DB_test extends CI_TestCase { - // ------------------------------------------------------------------------ - public function test_db_invalid() { $connection = new Mock_Database_DB(array( @@ -45,5 +43,5 @@ class DB_test extends CI_TestCase { $this->assertTrue($db instanceof CI_DB); $this->assertTrue($db instanceof CI_DB_Driver); } - + } \ No newline at end of file diff --git a/tests/codeigniter/database/query_builder/escape_test.php b/tests/codeigniter/database/query_builder/escape_test.php index 5dd2da058..96fbd078b 100644 --- a/tests/codeigniter/database/query_builder/escape_test.php +++ b/tests/codeigniter/database/query_builder/escape_test.php @@ -23,13 +23,10 @@ class Escape_test extends CI_TestCase { public function test_escape_like_percent_sign() { $string = $this->db->escape_like_str('\%foo'); - $sql = "SELECT `value` FROM `misc` WHERE `key` LIKE '$string%'"; - $sql .= (strpos(DB_DRIVER, 'mysql') !== FALSE) ? ";" : "ESCAPE '!';"; + $res = $this->db->select('value')->from('misc')->like('key', $string, 'after')->get(); - $res = $this->db->query($sql)->result_array(); - // Check the result - $this->assertEquals(1, count($res)); + $this->assertEquals(1, count($res->result_array())); } // ------------------------------------------------------------------------ @@ -40,11 +37,10 @@ class Escape_test extends CI_TestCase { public function test_escape_like_backslash_sign() { $string = $this->db->escape_like_str('\\'); - $sql = "SELECT `value` FROM `misc` WHERE `key` LIKE '$string%'"; - $sql .= (strpos(DB_DRIVER, 'mysql') !== FALSE) ? ";" : "ESCAPE '!';"; - $res = $this->db->query($sql)->result_array(); - + $res = $this->db->select('value')->from('misc')->like('key', $string, 'after')->get(); + // Check the result - $this->assertEquals(2, count($res)); + $this->assertEquals(2, count($res->result_array())); } + } \ No newline at end of file -- cgit v1.2.3-24-g4f1b From 0174d84401b5c5996115a4a6193161f1dab96de2 Mon Sep 17 00:00:00 2001 From: Andrey Andreev Date: Fri, 25 May 2012 02:17:49 +0300 Subject: Alter LIKE escaping tests again --- tests/codeigniter/database/query_builder/escape_test.php | 9 ++++++++- 1 file changed, 8 insertions(+), 1 deletion(-) diff --git a/tests/codeigniter/database/query_builder/escape_test.php b/tests/codeigniter/database/query_builder/escape_test.php index 96fbd078b..9df82c2d0 100644 --- a/tests/codeigniter/database/query_builder/escape_test.php +++ b/tests/codeigniter/database/query_builder/escape_test.php @@ -23,7 +23,10 @@ class Escape_test extends CI_TestCase { public function test_escape_like_percent_sign() { $string = $this->db->escape_like_str('\%foo'); - $res = $this->db->select('value')->from('misc')->like('key', $string, 'after')->get(); + $this->db->select('value'); + $this->db->from('misc'); + $this->db->like('key', $string, 'after'); + $res = $this->db->get(); // Check the result $this->assertEquals(1, count($res->result_array())); @@ -38,6 +41,10 @@ class Escape_test extends CI_TestCase { { $string = $this->db->escape_like_str('\\'); $res = $this->db->select('value')->from('misc')->like('key', $string, 'after')->get(); + $this->db->select('value'); + $this->db->from('misc'); + $this->db->like('key', $string, 'after'); + $res = $this->db->get(); // Check the result $this->assertEquals(2, count($res->result_array())); -- cgit v1.2.3-24-g4f1b From f33e2ff30b0a9c54d6e8adbe88662838b9bd525e Mon Sep 17 00:00:00 2001 From: Andrey Andreev Date: Fri, 25 May 2012 02:24:17 +0300 Subject: Again ... escape_like_str() tests --- tests/codeigniter/database/query_builder/escape_test.php | 7 ++++--- 1 file changed, 4 insertions(+), 3 deletions(-) diff --git a/tests/codeigniter/database/query_builder/escape_test.php b/tests/codeigniter/database/query_builder/escape_test.php index 9df82c2d0..50685922a 100644 --- a/tests/codeigniter/database/query_builder/escape_test.php +++ b/tests/codeigniter/database/query_builder/escape_test.php @@ -22,7 +22,8 @@ class Escape_test extends CI_TestCase { */ public function test_escape_like_percent_sign() { - $string = $this->db->escape_like_str('\%foo'); + $string = '\%foo' +; $this->db->select('value'); $this->db->from('misc'); $this->db->like('key', $string, 'after'); @@ -39,8 +40,8 @@ class Escape_test extends CI_TestCase { */ public function test_escape_like_backslash_sign() { - $string = $this->db->escape_like_str('\\'); - $res = $this->db->select('value')->from('misc')->like('key', $string, 'after')->get(); + $string = '\\'; + $this->db->select('value'); $this->db->from('misc'); $this->db->like('key', $string, 'after'); -- cgit v1.2.3-24-g4f1b