[feature/soft-delete] Update post counts within set_post_visibility

This is an additional query in some rare cases, but it makes it much easier to use and understand. This is mostly a preparation for the restore case. PHPBB3-9567
author: Joas Schilling <nickvergessen@gmx.de> 2012-10-08 22:47:50 +0200
committer: Joas Schilling <nickvergessen@gmx.de> 2012-10-08 22:47:50 +0200
commit: 53e01bba19784b0fb36324c10c010f969f05d253 (patch)
tree: 3cccbf982b90825581020becf75342a803e68ecc /phpBB/includes
parent: 91398c9e48df7ce0da6763790d9ec233ab06e729 (diff)
download: forums-53e01bba19784b0fb36324c10c010f969f05d253.tar
forums-53e01bba19784b0fb36324c10c010f969f05d253.tar.gz
forums-53e01bba19784b0fb36324c10c010f969f05d253.tar.bz2
forums-53e01bba19784b0fb36324c10c010f969f05d253.tar.xz
forums-53e01bba19784b0fb36324c10c010f969f05d253.zip
2 files changed, 247 insertions, 112 deletions
diff --git a/phpBB/includes/content_visibility.php b/phpBB/includes/content_visibility.php
index 182df69ec2..7761587c53 100644
--- a/phpBB/includes/content_visibility.php
+++ b/phpBB/includes/content_visibility.php
@@ -156,6 +156,208 @@ class phpbb_content_visibility
 	}
 
 	/**
+	* Change visibility status of one post or all posts of a topic
+	*
+	* @param $visibility	int		Element of {ITEM_APPROVED, ITEM_DELETED}
+	* @param $post_id		mixed	Post ID or array of post IDs to act on,
+	*								if it is empty, all posts of topic_id will be modified
+	* @param $topic_id		int		Topic where $post_id is found
+	* @param $forum_id		int		Forum where $topic_id is found
+	* @param $user_id		int		User performing the action
+	* @param $time			int		Timestamp when the action is performed
+	* @param $reason		string	Reason why the visibilty was changed.
+	* @param $is_starter	bool	Is this the first post of the topic changed?
+	* @param $is_latest		bool	Is this the last post of the topic changed?
+	* @param $limit_visibility	mixed	Limit updating per topic_id to a certain visibility
+	* @param $limit_delete_time	mixed	Limit updating per topic_id to a certain deletion time
+	* @return array		Changed post data, empty array if an error occured.
+	*/
+	static public function set_post_visibility($visibility, $post_id, $topic_id, $forum_id, $user_id, $time, $reason, $is_starter, $is_latest, $limit_visibility = false, $limit_delete_time = false)
+	{
+		global $db;
+
+		if (!in_array($visibility, array(ITEM_APPROVED, ITEM_DELETED)))
+		{
+			return array();
+		}
+
+		if ($post_id)
+		{
+			if (is_array($post_id))
+			{
+				$where_sql = $db->sql_in_set('post_id', array_map('intval', $post_id));
+			}
+			else
+			{
+				$where_sql = 'post_id = ' . (int) $post_id;
+			}
+			$where_sql .= ' AND topic_id = ' . (int) $topic_id;
+		}
+		else
+		{
+			$where_sql = 'topic_id = ' . (int) $topic_id;
+
+			// Limit the posts to a certain visibility and deletion time
+			// This allows us to only restore posts, that were approved
+			// when the topic got soft deleted. So previous soft deleted
+			// and unapproved posts are still soft deleted/unapproved
+			if ($limit_visibility !== false)
+			{
+				$where_sql .= ' AND post_visibility = ' . (int) $limit_visibility;
+			}
+			if ($limit_delete_time !== false)
+			{
+				$where_sql .= ' AND post_delete_time = ' . (int) $limit_delete_time;
+			}
+		}
+
+		$sql = 'SELECT poster_id, post_id, post_postcount, post_visibility
+			FROM ' . POSTS_TABLE . '
+			WHERE ' . $where_sql;
+		$result = $db->sql_query($sql);
+
+		$post_ids = $poster_postcounts = $postcounts = $postcount_visibility = array();
+		while ($row = $db->sql_fetchrow($result))
+		{
+			$post_ids[] = (int) $row['post_id'];
+
+			if ($row['post_visibility'] != $visibility)
+			{
+				if ($row['post_postcount'] && !isset($poster_postcounts[$row['poster_id']]))
+				{
+					$poster_postcounts[$row['poster_id']] = 1;
+				}
+				else if ($row['post_postcount'])
+				{
+					$poster_postcounts[$row['poster_id']]++;
+				}
+
+				if (!isset($postcount_visibility[$row['post_visibility']]))
+				{
+					$postcount_visibility[$row['post_visibility']] = 1;
+				}
+				else
+				{
+					$postcount_visibility[$row['post_visibility']]++;
+				}
+			}
+		}
+		$db->sql_freeresult($result);
+
+		if (empty($post_ids))
+		{
+			return array();
+		}
+
+		$data = array(
+			'post_visibility'		=> (int) $visibility,
+			'post_delete_user'		=> (int) $user_id,
+			'post_delete_time'		=> ((int) $time) ?: time(),
+			'post_delete_reason'	=> truncate_string($reason, 255, 255, false),
+		);
+
+		$sql = 'UPDATE ' . POSTS_TABLE . '
+			SET ' . $db->sql_build_array('UPDATE', $data) . '
+			WHERE ' . $db->sql_in_set('post_id', $post_ids);
+		$db->sql_query($sql);
+
+		// Group the authors by post count, to reduce the number of queries
+		foreach ($poster_postcounts as $poster_id => $num_posts)
+		{
+			$postcounts[$num_posts][] = $poster_id;
+		}
+
+		// Update users postcounts
+		foreach ($postcounts as $num_posts => $poster_ids)
+		{
+			if ($visibility == ITEM_DELETED)
+			{
+				$sql = 'UPDATE ' . USERS_TABLE . '
+					SET user_posts = 0
+					WHERE ' . $db->sql_in_set('user_id', $poster_ids) . '
+						AND user_posts < ' . $num_posts;
+				$db->sql_query($sql);
+
+				$sql = 'UPDATE ' . USERS_TABLE . '
+					SET user_posts = user_posts - ' . $num_posts . '
+					WHERE ' . $db->sql_in_set('user_id', $poster_ids) . '
+						AND user_posts >= ' . $num_posts;
+				$db->sql_query($sql);
+			}
+			else
+			{
+				$sql = 'UPDATE ' . USERS_TABLE . '
+					SET user_posts = user_posts + ' . $num_posts . '
+					WHERE ' . $db->sql_in_set('user_id', $poster_ids) . '
+						AND user_posts >= ' . $num_posts;
+				$db->sql_query($sql);
+			}
+		}
+
+		$update_topic_postcount = true;
+
+		// Sync the first/last topic information if needed
+		if (!$is_starter && $is_latest)
+		{
+			// update_post_information can only update the last post info ...
+			if ($topic_id)
+			{
+				update_post_information('topic', $topic_id, false);
+			}
+			if ($forum_id)
+			{
+				update_post_information('forum', $forum_id, false);
+			}
+		}
+		else if ($is_starter && $topic_id)
+		{
+			// ... so we need to use sync, if the first post is changed.
+			// The forum is resynced recursive by sync() itself.
+			sync('topic', 'topic_id', $topic_id, true);
+
+			// sync recalculates the topic replies and forum posts by itself, so we don't do that.
+			$update_topic_postcount = false;
+		}
+
+		// Update the topic's reply count and the forum's post count
+		if ($update_topic_postcount)
+		{
+			$num_posts = 0;
+			foreach ($postcount_visibility as $post_visibility => $visibility_posts)
+			{
+				// If we soft delete, we need to substract approved posts from the counters ...
+				if ($post_visibility == ITEM_APPROVED && $visibility == ITEM_DELETED)
+				{
+					$num_posts += $visibility_posts;
+				}
+				// ... and when we approve/restore, all others.
+				else if ($post_visibility != ITEM_APPROVED && $visibility == ITEM_APPROVED)
+				{
+					$num_posts += $visibility_posts;
+				}
+			}
+
+			if ($num_posts)
+			{
+				$sql_num_posts = (($visibility == ITEM_DELETED) ? ' - ' : ' + ') . $num_posts;
+
+				// Update the number for replies and posts
+				$sql = 'UPDATE ' . TOPICS_TABLE . "
+					SET topic_replies = topic_replies $sql_num_posts
+					WHERE topic_id = $topic_id";
+				$db->sql_query($sql);
+
+				$sql = 'UPDATE ' . FORUMS_TABLE . "
+					SET forum_posts = forum_posts $sql_num_posts
+					WHERE forum_id = $forum_id";
+				$db->sql_query($sql);
+			}
+		}
+
+		return $data;
+	}
+
+	/**
 	* Set topic visibility
 	*
 	* Allows approving (which is akin to undeleting/restore) or soft deleting an entire topic.
@@ -174,7 +376,7 @@ class phpbb_content_visibility
 	* @param $time			int		Timestamp when the action is performed
 	* @param $reason		string	Reason why the visibilty was changed.
 	* @param $force_update_all	bool	Force to update all posts within the topic
-	* @return void
+	* @return array		Changed topic data, empty array if an error occured.
 	*/
 	static public function set_topic_visibility($visibility, $topic_id, $forum_id, $user_id, $time, $reason, $force_update_all = false)
 	{
@@ -182,7 +384,7 @@ class phpbb_content_visibility
 
 		if (!in_array($visibility, array(ITEM_APPROVED, ITEM_DELETED)))
 		{
-			return;
+			return array();
 		}
 
 		if (!$force_update_all)
@@ -197,7 +399,7 @@ class phpbb_content_visibility
 			if (!$original_topic_data)
 			{
 				// The topic does not exist...
-				return;
+				return array();
 			}
 		}
 
@@ -214,6 +416,11 @@ class phpbb_content_visibility
 			WHERE topic_id = ' . (int) $topic_id;
 		$db->sql_query($sql);
 
+		if (!$db->sql_affectedrows())
+		{
+			return array();
+		}
+
 		if (!$force_update_all && $original_topic_data['topic_delete_time'] && $original_topic_data['topic_visibility'] == ITEM_DELETED && $visibility == ITEM_APPROVED)
 		{
 			// If we're restoring a topic we only restore posts, that were soft deleted through the topic soft deletion.
@@ -228,98 +435,50 @@ class phpbb_content_visibility
 		{
 			self::set_post_visibility($visibility, false, $topic_id, $forum_id, $user_id, $time, '', true, true);
 		}
+
+		return $data;
 	}
 
 	/**
-	* Change visibility status of one post or all posts of a topic
+	* Add post to topic and forum statistics
 	*
-	* @param $visibility	int		Element of {ITEM_APPROVED, ITEM_DELETED}
-	* @param $post_id		mixed	Post ID or array of post IDs to act on,
-	*								if it is empty, all posts of topic_id will be modified
-	* @param $topic_id		int		Topic where $post_id is found
-	* @param $forum_id		int		Forum where $topic_id is found
-	* @param $user_id		int		User performing the action
-	* @param $time			int		Timestamp when the action is performed
-	* @param $reason		string	Reason why the visibilty was changed.
-	* @param $is_starter	bool	Is this the first post of the topic changed?
-	* @param $is_latest		bool	Is this the last post of the topic changed?
-	* @param $limit_visibility	mixed	Limit updating per topic_id to a certain visibility
-	* @param $limit_delete_time	mixed	Limit updating per topic_id to a certain deletion time
+	* @param $data			array	Contains information from the topics table about given topic
+	* @param $sql_data		array	Populated with the SQL changes, may be empty at call time
 	* @return void
 	*/
-	static public function set_post_visibility($visibility, $post_id, $topic_id, $forum_id, $user_id, $time, $reason, $is_starter, $is_latest, $limit_visibility = false, $limit_delete_time = false)
+	static public function add_post_to_statistic($data, &$sql_data)
 	{
-		global $db;
+		$sql_data[TOPICS_TABLE] = (($sql_data[TOPICS_TABLE]) ? $sql_data[TOPICS_TABLE] . ', ' : '') . 'topic_replies = topic_replies + 1';
 
-		if (!in_array($visibility, array(ITEM_APPROVED, ITEM_DELETED)))
-		{
-			return;
-		}
+		$sql_data[FORUMS_TABLE] = (($sql_data[FORUMS_TABLE]) ? $sql_data[FORUMS_TABLE] . ', ' : '') . 'forum_posts = forum_posts + 1';
 
-		if ($post_id)
+		if ($data['post_postcount'])
 		{
-			if (is_array($post_id))
-			{
-				$where_sql = $db->sql_in_set('post_id', array_map('intval', $post_id));
-			}
-			else
-			{
-				$where_sql = 'post_id = ' . (int) $post_id;
-			}
+			$sql_data[USERS_TABLE] = (($sql_data[USERS_TABLE]) ? $sql_data[USERS_TABLE] . ', ' : '') . 'user_posts = user_posts + 1';
 		}
-		else if ($topic_id)
-		{
-			$where_sql = 'topic_id = ' . (int) $topic_id;
 
-			// Limit the posts to a certain visibility and deletion time
-			// This allows us to only restore posts, that were approved
-			// when the topic got soft deleted. So previous soft deleted
-			// and unapproved posts are still soft deleted/unapproved
-			if ($limit_visibility !== false)
-			{
-				$where_sql .= ' AND post_visibility = ' . (int) $limit_visibility;
-			}
-			if ($limit_delete_time !== false)
-			{
-				$where_sql .= ' AND post_delete_time = ' . (int) $limit_delete_time;
-			}
-		}
-		else
-		{
-			return;
-		}
+		set_config_count('num_posts', 1, true);
+	}
 
-		$data = array(
-			'post_visibility'		=> (int) $visibility,
-			'post_delete_user'		=> (int) $user_id,
-			'post_delete_time'		=> ((int) $time) ?: time(),
-			'post_delete_reason'	=> truncate_string($reason, 255, 255, false),
-		);
+	/**
+	* Remove post from topic and forum statistics
+	*
+	* @param $data			array	Contains information from the topics table about given topic
+	* @param $sql_data		array	Populated with the SQL changes, may be empty at call time
+	* @return void
+	*/
+	static public function remove_post_from_statistic($data, &$sql_data)
+	{
+		$sql_data[TOPICS_TABLE] = (($sql_data[TOPICS_TABLE]) ? $sql_data[TOPICS_TABLE] . ', ' : '') . 'topic_replies = topic_replies - 1';
 
-		$sql = 'UPDATE ' . POSTS_TABLE . '
-			SET ' . $db->sql_build_array('UPDATE', $data) . '
-			WHERE ' . $where_sql;
-		$db->sql_query($sql);
+		$sql_data[FORUMS_TABLE] = (($sql_data[FORUMS_TABLE]) ? $sql_data[FORUMS_TABLE] . ', ' : '') . 'forum_posts = forum_posts - 1';
 
-		// Sync the first/last topic information if needed
-		if (!$is_starter && $is_latest)
-		{
-			// update_post_information can only update the last post info ...
-			if ($topic_id)
-			{
-				update_post_information('topic', $topic_id, false);
-			}
-			if ($forum_id)
-			{
-				update_post_information('forum', $forum_id, false);
-			}
-		}
-		else if ($is_starter && $topic_id)
+		if ($data['post_postcount'])
 		{
-			// ... so we need to use sync, if the first post is changed.
-			// The forum is resynced recursive by sync() itself.
-			sync('topic', 'topic_id', $topic_id, true);
+			$sql_data[USERS_TABLE] = (($sql_data[USERS_TABLE]) ? $sql_data[USERS_TABLE] . ', ' : '') . 'user_posts = user_posts - 1';
 		}
+
+		set_config_count('num_posts', -1, true);
 	}
 
 	/**
@@ -367,28 +526,6 @@ class phpbb_content_visibility
 	}
 
 	/**
-	* Remove post from topic and forum statistics
-	*
-	* @param $forum_id		int		Forum where the topic is found
-	* @param $data			array	Contains information from the topics table about given topic
-	* @param $sql_data		array	Populated with the SQL changes, may be empty at call time
-	* @return void
-	*/
-	static public function remove_post_from_statistic($forum_id, $data, &$sql_data)
-	{
-		$sql_data[TOPICS_TABLE] = (($sql_data[TOPICS_TABLE]) ? $sql_data[TOPICS_TABLE] . ', ' : '') . 'topic_replies = topic_replies - 1';
-
-		$sql_data[FORUMS_TABLE] = (($sql_data[FORUMS_TABLE]) ? $sql_data[FORUMS_TABLE] . ', ' : '') . 'forum_posts = forum_posts - 1';
-
-		if ($data['post_postcount'])
-		{
-			$sql_data[USERS_TABLE] = (($sql_data[USERS_TABLE]) ? $sql_data[USERS_TABLE] . ', ' : '') . 'user_posts = user_posts - 1';
-		}
-
-		set_config_count('num_posts', -1, true);
-	}
-
-	/**
 	* One function to rule them all... and unhide posts and topics. This could
 	* reasonably be broken up, I straight copied this code from the mcp_queue.php
 	* file here for global access.
diff --git a/phpBB/includes/functions_posting.php b/phpBB/includes/functions_posting.php
index 2ae4fe2bd8..250d20b005 100644
--- a/phpBB/includes/functions_posting.php
+++ b/phpBB/includes/functions_posting.php
@@ -1601,21 +1601,19 @@ function delete_post($forum_id, $topic_id, $post_id, &$data, $is_soft = false, $
 		break;
 	}
 
-	if (($post_mode == 'delete') || ($post_mode == 'delete_last_post') || ($post_mode == 'delete_first_post' && !$is_soft))
-	{
-		if ($data['post_visibility'] == ITEM_APPROVED)
-		{
-			phpbb_content_visibility::remove_post_from_statistic($forum_id, $data, $sql_data);
-		}
 
+	if (($post_mode == 'delete') || ($post_mode == 'delete_last_post') || ($post_mode == 'delete_first_post'))
+	{
 		if (!$is_soft)
 		{
-			$sql_data[TOPICS_TABLE] .= ', topic_replies_real = topic_replies_real - 1';
+			if ($data['post_visibility'] == ITEM_APPROVED)
+			{
+				phpbb_content_visibility::remove_post_from_statistic($data, $sql_data);
+			}
+
+			$sql_data[TOPICS_TABLE] = (($sql_data[TOPICS_TABLE]) ? $sql_data[TOPICS_TABLE] . ', ' : '') . 'topic_replies_real = topic_replies_real - 1';
 		}
-	}
 
-	if (($post_mode == 'delete') || ($post_mode == 'delete_last_post') || ($post_mode == 'delete_first_post'))
-	{
 		$sql = 'SELECT 1 AS has_attachments
 			FROM ' . ATTACHMENTS_TABLE . '
 			WHERE topic_id = ' . $topic_id;
@@ -1625,7 +1623,7 @@ function delete_post($forum_id, $topic_id, $post_id, &$data, $is_soft = false, $
 
 		if (!$has_attachments)
 		{
-			$sql_data[TOPICS_TABLE] .= ', topic_attachment = 0';
+			$sql_data[TOPICS_TABLE] = (($sql_data[TOPICS_TABLE]) ? $sql_data[TOPICS_TABLE] . ', ' : '') . 'topic_attachment = 0';
 		}
 	}
 
@@ -1969,7 +1967,7 @@ function submit_post($mode, $subject, $username, $topic_type, &$poll, &$data, $u
 			// Correctly set back the topic replies and forum posts... but only if the post was approved before.
 			if (!$post_approval && $data['post_visibility'] == ITEM_APPROVED)
 			{
-				//phpbb_content_visibility::remove_post_from_statistic($forum_id, $current_time, $sql_data);
+				//phpbb_content_visibility::remove_post_from_statistic($current_time, $sql_data);
 				// ^^ remove_post_from_statistic SQL is identical, except that it does not include the ['stat'] sub-array
 				$sql_data[TOPICS_TABLE]['stat'][] = 'topic_replies = topic_replies - 1, topic_last_view_time = ' . $current_time;
 				$sql_data[FORUMS_TABLE]['stat'][] = 'forum_posts = forum_posts - 1';
author	Joas Schilling <nickvergessen@gmx.de>	2012-10-08 22:47:50 +0200
committer	Joas Schilling <nickvergessen@gmx.de>	2012-10-08 22:47:50 +0200
commit	53e01bba19784b0fb36324c10c010f969f05d253 (patch)
tree	3cccbf982b90825581020becf75342a803e68ecc /phpBB/includes
parent	91398c9e48df7ce0da6763790d9ec233ab06e729 (diff)
download	forums-53e01bba19784b0fb36324c10c010f969f05d253.tar forums-53e01bba19784b0fb36324c10c010f969f05d253.tar.gz forums-53e01bba19784b0fb36324c10c010f969f05d253.tar.bz2 forums-53e01bba19784b0fb36324c10c010f969f05d253.tar.xz forums-53e01bba19784b0fb36324c10c010f969f05d253.zip