Merge remote-tracking branch 'p/ticket/11162' into develop-olympus

* p/ticket/11162: (22 commits) [ticket/11162] Reformat. [ticket/11162] Rename tricky updates to database helper. [ticket/11162] Use empty($queries). [ticket/11162] Review comments fixed. [ticket/11162] Reformat. [ticket/11162] Newlines to LF. [ticket/11162] Use correct functions. [ticket/11162] Account for notify_status. [ticket/11162] This test really only works for bookmarks. [ticket/11162] The test is not at all trivial. [ticket/11162] Add includes. [ticket/11162] Move to a separate file to avoid blowing out functions.php. [ticket/11162] No whitespace changes in olympus. [ticket/11162] Fix inaccurately copy pasted comment. [ticket/11162] Use phpbb_update_rows_avoiding_duplicates in mcp. [ticket/11162] Clarify that only the two tables actually work. [ticket/11162] Uncomment transactions. [ticket/11162] An implementation that actually works. [ticket/11162] Make count function upper case. [ticket/11162] Rename count variable name to remaining_rows. ...
author: Andreas Fischer <bantu@phpbb.com> 2012-12-14 02:55:07 +0100
committer: Andreas Fischer <bantu@phpbb.com> 2012-12-14 02:55:07 +0100
commit: f4df152b4b88d2c7e2866cc829059781d2aea437 (patch)
tree: cc337fc0493088a80bbab4740f8529f5b6a38236 /phpBB/includes
parent: 73e7fa8e8ee9c2db7874dbe18605d3a0aa59b86f (diff)
parent: a686910958c0ca6eb64c7be10d60d1ebe15999c9 (diff)
download: forums-f4df152b4b88d2c7e2866cc829059781d2aea437.tar
forums-f4df152b4b88d2c7e2866cc829059781d2aea437.tar.gz
forums-f4df152b4b88d2c7e2866cc829059781d2aea437.tar.bz2
forums-f4df152b4b88d2c7e2866cc829059781d2aea437.tar.xz
forums-f4df152b4b88d2c7e2866cc829059781d2aea437.zip
3 files changed, 217 insertions, 13 deletions
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 @@
+<?php
+/**
+*
+* @package phpBB3
+* @copyright (c) 2012 phpBB Group
+* @license http://opensource.org/licenses/gpl-2.0.php GNU General Public License v2
+*
+*/
+
+/**
+* @ignore
+*/
+if (!defined('IN_PHPBB'))
+{
+	exit;
+}
+
+/**
+* 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 table is bookmarks.
+*
+* @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]][] = (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 b70601b479..db9fbd90bd 100644
--- a/phpBB/includes/mcp/mcp_forum.php
+++ b/phpBB/includes/mcp/mcp_forum.php
@@ -414,13 +414,12 @@ function merge_topics($forum_id, $topic_ids, $to_topic_id)
 		// Message and return links
 		$success_msg = 'POSTS_MERGED_SUCCESS';
 
-		// If the topic no longer exist, we will update the topic watch table.
-		// To not let it error out on users watching both topics, we just return on an error...
-		$db->sql_return_on_error(true);
-		$db->sql_query('UPDATE ' . TOPICS_WATCH_TABLE . ' SET topic_id = ' . (int) $to_topic_id . ' WHERE ' . $db->sql_in_set('topic_id', $topic_ids));
-		$db->sql_return_on_error(false);
-
-		$db->sql_query('DELETE FROM ' . TOPICS_WATCH_TABLE . ' WHERE ' . $db->sql_in_set('topic_id', $topic_ids));
+		// Update the topic watch table.
+		if (!function_exists('phpbb_update_rows_avoiding_duplicates_notify_status'))
+		{
+			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);
 
 		// Link to the new topic
 		$return_link .= (($return_link) ? '<br /><br />' : '') . sprintf($user->lang['RETURN_NEW_TOPIC'], '<a href="' . append_sid("{$phpbb_root_path}viewtopic.$phpEx", 'f=' . $to_forum_id . '&amp;t=' . $to_topic_id) . '">', '</a>');
diff --git a/phpBB/includes/mcp/mcp_topic.php b/phpBB/includes/mcp/mcp_topic.php
index 7d4edaf362..66d0c7a47e 100644
--- a/phpBB/includes/mcp/mcp_topic.php
+++ b/phpBB/includes/mcp/mcp_topic.php
@@ -620,12 +620,11 @@ function merge_posts($topic_id, $to_topic_id)
 		else
 		{
 			// If the topic no longer exist, we will update the topic watch table.
-			// To not let it error out on users watching both topics, we just return on an error...
-			$db->sql_return_on_error(true);
-			$db->sql_query('UPDATE ' . TOPICS_WATCH_TABLE . ' SET topic_id = ' . (int) $to_topic_id . ' WHERE topic_id = ' . (int) $topic_id);
-			$db->sql_return_on_error(false);
-
-			$db->sql_query('DELETE FROM ' . TOPICS_WATCH_TABLE . ' WHERE topic_id = ' . (int) $topic_id);
+			if (!function_exists('phpbb_update_rows_avoiding_duplicates_notify_status'))
+			{
+				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);
 		}
 
 		// Link to the new topic
author	Andreas Fischer <bantu@phpbb.com>	2012-12-14 02:55:07 +0100
committer	Andreas Fischer <bantu@phpbb.com>	2012-12-14 02:55:07 +0100
commit	f4df152b4b88d2c7e2866cc829059781d2aea437 (patch)
tree	cc337fc0493088a80bbab4740f8529f5b6a38236 /phpBB/includes
parent	73e7fa8e8ee9c2db7874dbe18605d3a0aa59b86f (diff)
parent	a686910958c0ca6eb64c7be10d60d1ebe15999c9 (diff)
download	forums-f4df152b4b88d2c7e2866cc829059781d2aea437.tar forums-f4df152b4b88d2c7e2866cc829059781d2aea437.tar.gz forums-f4df152b4b88d2c7e2866cc829059781d2aea437.tar.bz2 forums-f4df152b4b88d2c7e2866cc829059781d2aea437.tar.xz forums-f4df152b4b88d2c7e2866cc829059781d2aea437.zip