From 6872104aa95a9f2aad565189e25bbf54abc3ca2c Mon Sep 17 00:00:00 2001 From: Oleg Pudeyev Date: Wed, 5 Dec 2012 00:07:01 -0500 Subject: [ticket/11162] Account for notify_status. PHPBB3-11162 --- phpBB/includes/functions_tricky_update.php | 111 ++++++++++++++++++++- .../fixtures/topics_watch_duplicates.xml | 38 +++++-- ...rows_avoiding_duplicates_notify_status_test.php | 100 +++++++++++++++++++ 3 files changed, 239 insertions(+), 10 deletions(-) create mode 100644 tests/functions_tricky_update/update_rows_avoiding_duplicates_notify_status_test.php diff --git a/phpBB/includes/functions_tricky_update.php b/phpBB/includes/functions_tricky_update.php index a5b9fdacd0..cf922d7014 100644 --- a/phpBB/includes/functions_tricky_update.php +++ b/phpBB/includes/functions_tricky_update.php @@ -20,7 +20,7 @@ if (!defined('IN_PHPBB')) * If this results in rows violating uniqueness constraints, the duplicate * rows are eliminated. * -* The only supported tables are bookmarks and topics_watch. +* The only supported table is bookmarks. * * @param dbal $db Database object * @param string $table Table on which to perform the update @@ -67,7 +67,7 @@ function phpbb_update_rows_avoiding_duplicates($db, $table, $column, $from_value if (empty($new_user_ids)) { $sql = "UPDATE $table - SET $column = " . (int) $to_value. " + SET $column = " . (int) $to_value . " WHERE $column = '" . $db->sql_escape($from_value) . "'"; $queries[] = $sql; } @@ -77,7 +77,7 @@ function phpbb_update_rows_avoiding_duplicates($db, $table, $column, $from_value if (!empty($different_user_ids)) { $sql = "UPDATE $table - SET $column = " . (int) $to_value. " + SET $column = " . (int) $to_value . " WHERE $column = '" . $db->sql_escape($from_value) . "' AND " . $db->sql_in_set('user_id', $different_user_ids); $queries[] = $sql; @@ -101,3 +101,108 @@ function phpbb_update_rows_avoiding_duplicates($db, $table, $column, $from_value $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]][] = $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; + $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; + } + $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[$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 ($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_tricky_update/fixtures/topics_watch_duplicates.xml b/tests/functions_tricky_update/fixtures/topics_watch_duplicates.xml index bc08016a8f..c387bb737a 100644 --- a/tests/functions_tricky_update/fixtures/topics_watch_duplicates.xml +++ b/tests/functions_tricky_update/fixtures/topics_watch_duplicates.xml @@ -14,17 +14,17 @@ - 2 + 1 2 1 + 2 3 - 3 - 1 + 0 - + 1 4 @@ -36,20 +36,44 @@ 1 - + 1 6 - 1 + 0 1 7 1 + + + + 1 + 8 + 1 + + + 1 + 9 + 0 + + + + + 1 + 10 + 0 + + + 1 + 11 + 1 + 2 - 6 + 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 new file mode 100644 index 0000000000..aa739c5f04 --- /dev/null +++ b/tests/functions_tricky_update/update_rows_avoiding_duplicates_notify_status_test.php @@ -0,0 +1,100 @@ +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); + } +} -- cgit v1.2.1