From 0f96b1aad378b1fc40620ea57df5ee89224fd5eb Mon Sep 17 00:00:00 2001 From: Oleg Pudeyev Date: Tue, 4 Dec 2012 23:35:34 -0500 Subject: [ticket/11162] Move to a separate file to avoid blowing out functions.php. PHPBB3-11162 --- phpBB/includes/functions.php | 87 ----------------- phpBB/includes/functions_tricky_update.php | 103 +++++++++++++++++++++ tests/functions/fixtures/duplicates.xml | 56 ----------- .../update_rows_avoiding_duplicates_test.php | 71 -------------- .../fixtures/duplicates.xml | 56 +++++++++++ .../update_rows_avoiding_duplicates_test.php | 71 ++++++++++++++ 6 files changed, 230 insertions(+), 214 deletions(-) create mode 100644 phpBB/includes/functions_tricky_update.php delete mode 100644 tests/functions/fixtures/duplicates.xml delete mode 100644 tests/functions/update_rows_avoiding_duplicates_test.php create mode 100644 tests/functions_tricky_update/fixtures/duplicates.xml create mode 100644 tests/functions_tricky_update/update_rows_avoiding_duplicates_test.php diff --git a/phpBB/includes/functions.php b/phpBB/includes/functions.php index e3feb59d41..65d8be32ad 100644 --- a/phpBB/includes/functions.php +++ b/phpBB/includes/functions.php @@ -1297,93 +1297,6 @@ function tz_select($default = '', $truncate = false) return $tz_select; } -/** -* Updates rows in given table from a set of values to a new value. -* If this results in rows violating uniqueness constraints, the duplicate -* rows are eliminated. -* -* The only supported tables are bookmarks and topics_watch. -* -* @param dbal $db Database object -* @param string $table Table on which to perform the update -* @param string $column Column whose values to change -* @param array $from_values An array of values that should be changed -* @param int $to_value The new value -* @return null -*/ -function phpbb_update_rows_avoiding_duplicates($db, $table, $column, $from_values, $to_value) -{ - $sql = "SELECT $column, user_id - FROM $table - WHERE " . $db->sql_in_set($column, $from_values); - $result = $db->sql_query($sql); - - $old_user_ids = array(); - while ($row = $db->sql_fetchrow($result)) - { - $old_user_ids[$row[$column]][] = $row['user_id']; - } - $db->sql_freeresult($result); - - $sql = "SELECT $column, user_id - FROM $table - WHERE $column = '" . (int) $to_value . "'"; - $result = $db->sql_query($sql); - - $new_user_ids = array(); - while ($row = $db->sql_fetchrow($result)) - { - $new_user_ids[$row[$column]][] = $row['user_id']; - } - $db->sql_freeresult($result); - - $queries = array(); - $any_found = false; - foreach ($from_values as $from_value) - { - if (!isset($old_user_ids[$from_value])) - { - continue; - } - $any_found = true; - if (empty($new_user_ids)) - { - $sql = "UPDATE $table - SET $column = " . (int) $to_value. " - WHERE $column = '" . $db->sql_escape($from_value) . "'"; - $queries[] = $sql; - } - else - { - $different_user_ids = array_diff($old_user_ids[$from_value], $new_user_ids[$to_value]); - if (!empty($different_user_ids)) - { - $sql = "UPDATE $table - SET $column = " . (int) $to_value. " - WHERE $column = '" . $db->sql_escape($from_value) . "' - AND " . $db->sql_in_set('user_id', $different_user_ids); - $queries[] = $sql; - } - } - } - - if ($any_found) - { - $db->sql_transaction('begin'); - - foreach ($queries as $sql) - { - $db->sql_query($sql); - } - - $sql = "DELETE FROM $table - WHERE " . $db->sql_in_set($column, $from_values); - $db->sql_query($sql); - - $db->sql_transaction('commit'); - } -} - // Functions handling topic/post tracking/marking /** diff --git a/phpBB/includes/functions_tricky_update.php b/phpBB/includes/functions_tricky_update.php new file mode 100644 index 0000000000..a5b9fdacd0 --- /dev/null +++ b/phpBB/includes/functions_tricky_update.php @@ -0,0 +1,103 @@ +sql_in_set($column, $from_values); + $result = $db->sql_query($sql); + + $old_user_ids = array(); + while ($row = $db->sql_fetchrow($result)) + { + $old_user_ids[$row[$column]][] = $row['user_id']; + } + $db->sql_freeresult($result); + + $sql = "SELECT $column, user_id + FROM $table + WHERE $column = '" . (int) $to_value . "'"; + $result = $db->sql_query($sql); + + $new_user_ids = array(); + while ($row = $db->sql_fetchrow($result)) + { + $new_user_ids[$row[$column]][] = $row['user_id']; + } + $db->sql_freeresult($result); + + $queries = array(); + $any_found = false; + foreach ($from_values as $from_value) + { + if (!isset($old_user_ids[$from_value])) + { + continue; + } + $any_found = true; + if (empty($new_user_ids)) + { + $sql = "UPDATE $table + SET $column = " . (int) $to_value. " + WHERE $column = '" . $db->sql_escape($from_value) . "'"; + $queries[] = $sql; + } + else + { + $different_user_ids = array_diff($old_user_ids[$from_value], $new_user_ids[$to_value]); + if (!empty($different_user_ids)) + { + $sql = "UPDATE $table + SET $column = " . (int) $to_value. " + WHERE $column = '" . $db->sql_escape($from_value) . "' + AND " . $db->sql_in_set('user_id', $different_user_ids); + $queries[] = $sql; + } + } + } + + if ($any_found) + { + $db->sql_transaction('begin'); + + foreach ($queries as $sql) + { + $db->sql_query($sql); + } + + $sql = "DELETE FROM $table + WHERE " . $db->sql_in_set($column, $from_values); + $db->sql_query($sql); + + $db->sql_transaction('commit'); + } +} diff --git a/tests/functions/fixtures/duplicates.xml b/tests/functions/fixtures/duplicates.xml deleted file mode 100644 index bc08016a8f..0000000000 --- a/tests/functions/fixtures/duplicates.xml +++ /dev/null @@ -1,56 +0,0 @@ - - - - user_id - topic_id - notify_status - - - - 1 - 1 - 1 - - - - - 2 - 2 - 1 - - - 3 - 3 - 1 - - - - - 1 - 4 - 1 - - - 1 - 5 - 1 - - - - - 1 - 6 - 1 - - - 1 - 7 - 1 - - - 2 - 6 - 1 - -
- diff --git a/tests/functions/update_rows_avoiding_duplicates_test.php b/tests/functions/update_rows_avoiding_duplicates_test.php deleted file mode 100644 index 0d68e22d4a..0000000000 --- a/tests/functions/update_rows_avoiding_duplicates_test.php +++ /dev/null @@ -1,71 +0,0 @@ -createXMLDataSet(dirname(__FILE__).'/fixtures/duplicates.xml'); - } - - public static function fixture_data() - { - return array( - // description - // from array - // to value - // expected count with to value post update - array( - 'trivial', - array(1), - 10, - 1, - ), - array( - 'no conflict', - array(2), - 3, - 2, - ), - array( - 'conflict', - array(4), - 5, - 1, - ), - array( - 'conflict and no conflict', - array(6), - 7, - 2, - ), - ); - } - - /** - * @dataProvider fixture_data - */ - public function test_trivial_update($description, $from, $to, $expected_result_count) - { - $db = $this->new_dbal(); - - phpbb_update_rows_avoiding_duplicates($db, TOPICS_WATCH_TABLE, 'topic_id', $from, $to); - - $sql = 'SELECT COUNT(*) AS remaining_rows - FROM ' . TOPICS_WATCH_TABLE . ' - WHERE topic_id = ' . (int) $to; - $result = $db->sql_query($sql); - $result_count = $db->sql_fetchfield('remaining_rows'); - $db->sql_freeresult($result); - - $this->assertEquals($expected_result_count, $result_count); - } -} diff --git a/tests/functions_tricky_update/fixtures/duplicates.xml b/tests/functions_tricky_update/fixtures/duplicates.xml new file mode 100644 index 0000000000..bc08016a8f --- /dev/null +++ b/tests/functions_tricky_update/fixtures/duplicates.xml @@ -0,0 +1,56 @@ + + + + user_id + topic_id + notify_status + + + + 1 + 1 + 1 + + + + + 2 + 2 + 1 + + + 3 + 3 + 1 + + + + + 1 + 4 + 1 + + + 1 + 5 + 1 + + + + + 1 + 6 + 1 + + + 1 + 7 + 1 + + + 2 + 6 + 1 + +
+ diff --git a/tests/functions_tricky_update/update_rows_avoiding_duplicates_test.php b/tests/functions_tricky_update/update_rows_avoiding_duplicates_test.php new file mode 100644 index 0000000000..6940122393 --- /dev/null +++ b/tests/functions_tricky_update/update_rows_avoiding_duplicates_test.php @@ -0,0 +1,71 @@ +createXMLDataSet(dirname(__FILE__).'/fixtures/duplicates.xml'); + } + + public static function fixture_data() + { + return array( + // description + // from array + // to value + // expected count with to value post update + array( + 'trivial', + array(1), + 10, + 1, + ), + array( + 'no conflict', + array(2), + 3, + 2, + ), + array( + 'conflict', + array(4), + 5, + 1, + ), + array( + 'conflict and no conflict', + array(6), + 7, + 2, + ), + ); + } + + /** + * @dataProvider fixture_data + */ + public function test_trivial_update($description, $from, $to, $expected_result_count) + { + $db = $this->new_dbal(); + + phpbb_update_rows_avoiding_duplicates($db, TOPICS_WATCH_TABLE, 'topic_id', $from, $to); + + $sql = 'SELECT COUNT(*) AS remaining_rows + FROM ' . TOPICS_WATCH_TABLE . ' + WHERE topic_id = ' . (int) $to; + $result = $db->sql_query($sql); + $result_count = $db->sql_fetchfield('remaining_rows'); + $db->sql_freeresult($result); + + $this->assertEquals($expected_result_count, $result_count); + } +} -- cgit v1.2.1