diff options
| author | Oleg Pudeyev <oleg@bsdpower.com> | 2012-12-05 00:07:01 -0500 |
|---|---|---|
| committer | Oleg Pudeyev <oleg@bsdpower.com> | 2012-12-05 00:07:01 -0500 |
| commit | 6872104aa95a9f2aad565189e25bbf54abc3ca2c (patch) | |
| tree | 9de1ed5fcd79454e1c1a42ba2bfdaba7db7f21dd | |
| parent | efe122b03200155479920890defc2a3dc689bdd7 (diff) | |
| download | forums-6872104aa95a9f2aad565189e25bbf54abc3ca2c.tar forums-6872104aa95a9f2aad565189e25bbf54abc3ca2c.tar.gz forums-6872104aa95a9f2aad565189e25bbf54abc3ca2c.tar.bz2 forums-6872104aa95a9f2aad565189e25bbf54abc3ca2c.tar.xz forums-6872104aa95a9f2aad565189e25bbf54abc3ca2c.zip | |
[ticket/11162] Account for notify_status.
PHPBB3-11162
3 files changed, 239 insertions, 10 deletions
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 @@ <!-- non-conflicting entries --> <row> - <value>2</value> + <value>1</value> <value>2</value> <value>1</value> </row> <row> + <value>2</value> <value>3</value> - <value>3</value> - <value>1</value> + <value>0</value> </row> - <!-- conflicting entries --> + <!-- conflicting entries, same notify status --> <row> <value>1</value> <value>4</value> @@ -36,20 +36,44 @@ <value>1</value> </row> - <!-- conflicting and non-conflicting entries --> + <!-- conflicting entries, notify status 0 into 1 --> <row> <value>1</value> <value>6</value> - <value>1</value> + <value>0</value> </row> <row> <value>1</value> <value>7</value> <value>1</value> </row> + + <!-- conflicting entries, notify status 1 into 0 --> + <row> + <value>1</value> + <value>8</value> + <value>1</value> + </row> + <row> + <value>1</value> + <value>9</value> + <value>0</value> + </row> + + <!-- conflicting and non-conflicting entries --> + <row> + <value>1</value> + <value>10</value> + <value>0</value> + </row> + <row> + <value>1</value> + <value>11</value> + <value>1</value> + </row> <row> <value>2</value> - <value>6</value> + <value>10</value> <value>1</value> </row> </table> 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 @@ +<?php +/** +* +* @package testing +* @copyright (c) 2012 phpBB Group +* @license http://opensource.org/licenses/gpl-2.0.php GNU General Public License v2 +* +*/ + +require_once dirname(__FILE__) . '/../../phpBB/includes/functions_tricky_update.php'; + +class phpbb_update_rows_avoiding_duplicates_notify_status_test extends phpbb_database_test_case +{ + public function getDataSet() + { + return $this->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); + } +} |