From e2c67a8e42beefe1631a90feeb2215e2137c944b Mon Sep 17 00:00:00 2001 From: Oleg Pudeyev Date: Wed, 12 Dec 2012 21:46:38 -0500 Subject: [ticket/11162] Rename tricky updates to database helper. PHPBB3-11162 --- phpBB/includes/functions_database_helper.php | 206 +++++++++++++++++++++ phpBB/includes/functions_tricky_update.php | 206 --------------------- phpBB/includes/mcp/mcp_forum.php | 2 +- phpBB/includes/mcp/mcp_topic.php | 2 +- .../fixtures/bookmarks_duplicates.xml | 47 +++++ .../fixtures/topics_watch_duplicates.xml | 80 ++++++++ ...rows_avoiding_duplicates_notify_status_test.php | 101 ++++++++++ .../update_rows_avoiding_duplicates_test.php | 71 +++++++ .../fixtures/bookmarks_duplicates.xml | 47 ----- .../fixtures/topics_watch_duplicates.xml | 80 -------- ...rows_avoiding_duplicates_notify_status_test.php | 101 ---------- .../update_rows_avoiding_duplicates_test.php | 71 ------- 12 files changed, 507 insertions(+), 507 deletions(-) create mode 100644 phpBB/includes/functions_database_helper.php delete mode 100644 phpBB/includes/functions_tricky_update.php create mode 100644 tests/functions_database_helper/fixtures/bookmarks_duplicates.xml create mode 100644 tests/functions_database_helper/fixtures/topics_watch_duplicates.xml create mode 100644 tests/functions_database_helper/update_rows_avoiding_duplicates_notify_status_test.php create mode 100644 tests/functions_database_helper/update_rows_avoiding_duplicates_test.php delete mode 100644 tests/functions_tricky_update/fixtures/bookmarks_duplicates.xml delete mode 100644 tests/functions_tricky_update/fixtures/topics_watch_duplicates.xml delete mode 100644 tests/functions_tricky_update/update_rows_avoiding_duplicates_notify_status_test.php delete mode 100644 tests/functions_tricky_update/update_rows_avoiding_duplicates_test.php diff --git a/phpBB/includes/functions_database_helper.php b/phpBB/includes/functions_database_helper.php new file mode 100644 index 0000000000..664c246888 --- /dev/null +++ b/phpBB/includes/functions_database_helper.php @@ -0,0 +1,206 @@ +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]][] = (int) $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]][] = (int) $row['user_id']; + } + $db->sql_freeresult($result); + + $queries = array(); + foreach ($from_values as $from_value) + { + if (!isset($old_user_ids[$from_value])) + { + continue; + } + 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 (!empty($queries)) + { + $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'); + } +} + +/** +* 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 merged respecting notify_status (0 takes precedence over 1). +* +* The only supported table is 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_notify_status($db, $table, $column, $from_values, $to_value) +{ + $sql = "SELECT $column, user_id, notify_status + 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[(int) $row['notify_status']][$row[$column]][] = (int) $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]][] = (int) $row['user_id']; + } + $db->sql_freeresult($result); + + $queries = array(); + $extra_updates = array( + 0 => 'notify_status = 0', + 1 => '', + ); + foreach ($from_values as $from_value) + { + foreach ($extra_updates as $notify_status => $extra_update) + { + if (!isset($old_user_ids[$notify_status][$from_value])) + { + continue; + } + 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[$notify_status][$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 ($extra_update) + { + $same_user_ids = array_diff($old_user_ids[$notify_status][$from_value], $different_user_ids); + if (!empty($same_user_ids)) + { + $sql = "UPDATE $table + SET $extra_update + WHERE $column = '" . (int) $to_value . "' + AND " . $db->sql_in_set('user_id', $same_user_ids); + $queries[] = $sql; + } + } + } + } + } + + if (!empty($queries)) + { + $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/phpBB/includes/functions_tricky_update.php b/phpBB/includes/functions_tricky_update.php deleted file mode 100644 index 664c246888..0000000000 --- a/phpBB/includes/functions_tricky_update.php +++ /dev/null @@ -1,206 +0,0 @@ -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]][] = (int) $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]][] = (int) $row['user_id']; - } - $db->sql_freeresult($result); - - $queries = array(); - foreach ($from_values as $from_value) - { - if (!isset($old_user_ids[$from_value])) - { - continue; - } - 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 (!empty($queries)) - { - $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'); - } -} - -/** -* 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 merged respecting notify_status (0 takes precedence over 1). -* -* The only supported table is 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_notify_status($db, $table, $column, $from_values, $to_value) -{ - $sql = "SELECT $column, user_id, notify_status - 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[(int) $row['notify_status']][$row[$column]][] = (int) $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]][] = (int) $row['user_id']; - } - $db->sql_freeresult($result); - - $queries = array(); - $extra_updates = array( - 0 => 'notify_status = 0', - 1 => '', - ); - foreach ($from_values as $from_value) - { - foreach ($extra_updates as $notify_status => $extra_update) - { - if (!isset($old_user_ids[$notify_status][$from_value])) - { - continue; - } - 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[$notify_status][$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 ($extra_update) - { - $same_user_ids = array_diff($old_user_ids[$notify_status][$from_value], $different_user_ids); - if (!empty($same_user_ids)) - { - $sql = "UPDATE $table - SET $extra_update - WHERE $column = '" . (int) $to_value . "' - AND " . $db->sql_in_set('user_id', $same_user_ids); - $queries[] = $sql; - } - } - } - } - } - - if (!empty($queries)) - { - $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/phpBB/includes/mcp/mcp_forum.php b/phpBB/includes/mcp/mcp_forum.php index cd938e83a0..db9fbd90bd 100644 --- a/phpBB/includes/mcp/mcp_forum.php +++ b/phpBB/includes/mcp/mcp_forum.php @@ -417,7 +417,7 @@ function merge_topics($forum_id, $topic_ids, $to_topic_id) // Update the topic watch table. if (!function_exists('phpbb_update_rows_avoiding_duplicates_notify_status')) { - include($phpbb_root_path . 'includes/functions_tricky_update.' . $phpEx); + include($phpbb_root_path . 'includes/functions_database_helper.' . $phpEx); } phpbb_update_rows_avoiding_duplicates_notify_status($db, TOPICS_WATCH_TABLE, 'topic_id', $topic_ids, $to_topic_id); diff --git a/phpBB/includes/mcp/mcp_topic.php b/phpBB/includes/mcp/mcp_topic.php index 29dabdd2a2..66d0c7a47e 100644 --- a/phpBB/includes/mcp/mcp_topic.php +++ b/phpBB/includes/mcp/mcp_topic.php @@ -622,7 +622,7 @@ function merge_posts($topic_id, $to_topic_id) // If the topic no longer exist, we will update the topic watch table. if (!function_exists('phpbb_update_rows_avoiding_duplicates_notify_status')) { - include($phpbb_root_path . 'includes/functions_tricky_update.' . $phpEx); + include($phpbb_root_path . 'includes/functions_database_helper.' . $phpEx); } phpbb_update_rows_avoiding_duplicates_notify_status($db, TOPICS_WATCH_TABLE, 'topic_id', $topic_ids, $to_topic_id); } diff --git a/tests/functions_database_helper/fixtures/bookmarks_duplicates.xml b/tests/functions_database_helper/fixtures/bookmarks_duplicates.xml new file mode 100644 index 0000000000..d49f76b073 --- /dev/null +++ b/tests/functions_database_helper/fixtures/bookmarks_duplicates.xml @@ -0,0 +1,47 @@ + + + + user_id + topic_id + + + + 1 + 1 + + + + + 2 + 2 + + + 3 + 3 + + + + + 1 + 4 + + + 1 + 5 + + + + + 1 + 6 + + + 1 + 7 + + + 2 + 6 + +
+ diff --git a/tests/functions_database_helper/fixtures/topics_watch_duplicates.xml b/tests/functions_database_helper/fixtures/topics_watch_duplicates.xml new file mode 100644 index 0000000000..c387bb737a --- /dev/null +++ b/tests/functions_database_helper/fixtures/topics_watch_duplicates.xml @@ -0,0 +1,80 @@ + + + + user_id + topic_id + notify_status + + + + 1 + 1 + 1 + + + + + 1 + 2 + 1 + + + 2 + 3 + 0 + + + + + 1 + 4 + 1 + + + 1 + 5 + 1 + + + + + 1 + 6 + 0 + + + 1 + 7 + 1 + + + + + 1 + 8 + 1 + + + 1 + 9 + 0 + + + + + 1 + 10 + 0 + + + 1 + 11 + 1 + + + 2 + 10 + 1 + +
+ diff --git a/tests/functions_database_helper/update_rows_avoiding_duplicates_notify_status_test.php b/tests/functions_database_helper/update_rows_avoiding_duplicates_notify_status_test.php new file mode 100644 index 0000000000..6c0f2da1e1 --- /dev/null +++ b/tests/functions_database_helper/update_rows_avoiding_duplicates_notify_status_test.php @@ -0,0 +1,101 @@ +createXMLDataSet(dirname(__FILE__).'/fixtures/topics_watch_duplicates.xml'); + } + + public static function fixture_data() + { + return array( + // description + // from array + // to value + // expected count with to value post update + // expected notify_status values + array( + 'trivial', + array(1), + 1000, + 1, + 1, + ), + array( + 'no conflict', + array(2), + 3, + 2, + 1, + ), + array( + 'conflict, same notify status', + array(4), + 5, + 1, + 1, + ), + array( + 'conflict, notify status 0 into 1', + array(6), + 7, + 1, + 0, + ), + array( + 'conflict, notify status 1 into 0', + array(8), + 9, + 1, + 0, + ), + array( + 'conflict and no conflict', + array(10), + 11, + 2, + 0, + ), + ); + } + + /** + * @dataProvider fixture_data + */ + public function test_update($description, $from, $to, $expected_result_count, $expected_notify_status) + { + $db = $this->new_dbal(); + + phpbb_update_rows_avoiding_duplicates_notify_status($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); + + // user id of 1 is the user being updated + $sql = 'SELECT notify_status + FROM ' . TOPICS_WATCH_TABLE . ' + WHERE topic_id = ' . (int) $to . ' + AND user_id = 1'; + $result = $db->sql_query($sql); + $notify_status = $db->sql_fetchfield('notify_status'); + $db->sql_freeresult($result); + + $this->assertEquals($expected_notify_status, $notify_status); + } +} diff --git a/tests/functions_database_helper/update_rows_avoiding_duplicates_test.php b/tests/functions_database_helper/update_rows_avoiding_duplicates_test.php new file mode 100644 index 0000000000..2f01d29d15 --- /dev/null +++ b/tests/functions_database_helper/update_rows_avoiding_duplicates_test.php @@ -0,0 +1,71 @@ +createXMLDataSet(dirname(__FILE__).'/fixtures/bookmarks_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_update($description, $from, $to, $expected_result_count) + { + $db = $this->new_dbal(); + + phpbb_update_rows_avoiding_duplicates($db, BOOKMARKS_TABLE, 'topic_id', $from, $to); + + $sql = 'SELECT COUNT(*) AS remaining_rows + FROM ' . BOOKMARKS_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/bookmarks_duplicates.xml b/tests/functions_tricky_update/fixtures/bookmarks_duplicates.xml deleted file mode 100644 index d49f76b073..0000000000 --- a/tests/functions_tricky_update/fixtures/bookmarks_duplicates.xml +++ /dev/null @@ -1,47 +0,0 @@ - - - - user_id - topic_id - - - - 1 - 1 - - - - - 2 - 2 - - - 3 - 3 - - - - - 1 - 4 - - - 1 - 5 - - - - - 1 - 6 - - - 1 - 7 - - - 2 - 6 - -
- diff --git a/tests/functions_tricky_update/fixtures/topics_watch_duplicates.xml b/tests/functions_tricky_update/fixtures/topics_watch_duplicates.xml deleted file mode 100644 index c387bb737a..0000000000 --- a/tests/functions_tricky_update/fixtures/topics_watch_duplicates.xml +++ /dev/null @@ -1,80 +0,0 @@ - - - - user_id - topic_id - notify_status - - - - 1 - 1 - 1 - - - - - 1 - 2 - 1 - - - 2 - 3 - 0 - - - - - 1 - 4 - 1 - - - 1 - 5 - 1 - - - - - 1 - 6 - 0 - - - 1 - 7 - 1 - - - - - 1 - 8 - 1 - - - 1 - 9 - 0 - - - - - 1 - 10 - 0 - - - 1 - 11 - 1 - - - 2 - 10 - 1 - -
- diff --git a/tests/functions_tricky_update/update_rows_avoiding_duplicates_notify_status_test.php b/tests/functions_tricky_update/update_rows_avoiding_duplicates_notify_status_test.php deleted file mode 100644 index 9052585552..0000000000 --- a/tests/functions_tricky_update/update_rows_avoiding_duplicates_notify_status_test.php +++ /dev/null @@ -1,101 +0,0 @@ -createXMLDataSet(dirname(__FILE__).'/fixtures/topics_watch_duplicates.xml'); - } - - public static function fixture_data() - { - return array( - // description - // from array - // to value - // expected count with to value post update - // expected notify_status values - array( - 'trivial', - array(1), - 1000, - 1, - 1, - ), - array( - 'no conflict', - array(2), - 3, - 2, - 1, - ), - array( - 'conflict, same notify status', - array(4), - 5, - 1, - 1, - ), - array( - 'conflict, notify status 0 into 1', - array(6), - 7, - 1, - 0, - ), - array( - 'conflict, notify status 1 into 0', - array(8), - 9, - 1, - 0, - ), - array( - 'conflict and no conflict', - array(10), - 11, - 2, - 0, - ), - ); - } - - /** - * @dataProvider fixture_data - */ - public function test_update($description, $from, $to, $expected_result_count, $expected_notify_status) - { - $db = $this->new_dbal(); - - phpbb_update_rows_avoiding_duplicates_notify_status($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); - - // user id of 1 is the user being updated - $sql = 'SELECT notify_status - FROM ' . TOPICS_WATCH_TABLE . ' - WHERE topic_id = ' . (int) $to . ' - AND user_id = 1'; - $result = $db->sql_query($sql); - $notify_status = $db->sql_fetchfield('notify_status'); - $db->sql_freeresult($result); - - $this->assertEquals($expected_notify_status, $notify_status); - } -} diff --git a/tests/functions_tricky_update/update_rows_avoiding_duplicates_test.php b/tests/functions_tricky_update/update_rows_avoiding_duplicates_test.php deleted file mode 100644 index 6142997408..0000000000 --- a/tests/functions_tricky_update/update_rows_avoiding_duplicates_test.php +++ /dev/null @@ -1,71 +0,0 @@ -createXMLDataSet(dirname(__FILE__).'/fixtures/bookmarks_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_update($description, $from, $to, $expected_result_count) - { - $db = $this->new_dbal(); - - phpbb_update_rows_avoiding_duplicates($db, BOOKMARKS_TABLE, 'topic_id', $from, $to); - - $sql = 'SELECT COUNT(*) AS remaining_rows - FROM ' . BOOKMARKS_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