From 17f5c6bf71f560be2f4e2ecabae83ab2973bec47 Mon Sep 17 00:00:00 2001 From: Joas Schilling Date: Sat, 18 Feb 2012 12:00:12 +0100 Subject: [ticket/10605] Check for orphan privmsgs when deleting a user Also moved the hole code into a new function. PHPBB3-10605 --- phpBB/includes/functions_privmsgs.php | 116 ++++++++++++++++++++++++++++++++++ 1 file changed, 116 insertions(+) (limited to 'phpBB/includes/functions_privmsgs.php') diff --git a/phpBB/includes/functions_privmsgs.php b/phpBB/includes/functions_privmsgs.php index c40ceb088f..30cff8ed72 100644 --- a/phpBB/includes/functions_privmsgs.php +++ b/phpBB/includes/functions_privmsgs.php @@ -1083,6 +1083,122 @@ function delete_pm($user_id, $msg_ids, $folder_id) return true; } +/** +* Delete all PM(s) for a given user and delete the ones without references +*/ +function delete_user_pms($user_id) +{ + global $db, $user, $phpbb_root_path, $phpEx; + + $user_id = (int) $user_id; + + if (!$user_id) + { + return false; + } + + // Get PM Information for later deleting + $sql = 'SELECT msg_id, author_id, folder_id, pm_unread, pm_new + FROM ' . PRIVMSGS_TO_TABLE . ' + WHERE user_id = ' . $user_id . ' + OR (author_id = ' . $user_id . ' + AND folder_id = ' . PRIVMSGS_NO_BOX . ')'; + $result = $db->sql_query($sql); + + $undelivered_msg = $undelivered_user = $delete_rows = array(); + $num_unread = $num_new = $num_deleted = 0; + while ($row = $db->sql_fetchrow($result)) + { + if ($row['author_id'] == $user_id && $row['folder_id'] == PRIVMSGS_NO_BOX) + { + // Undelivered messages + $undelivered_msg[] = $row['msg_id']; + $undelivered_user[$row['user_id']][] = true; + } + + $delete_rows[$row['msg_id']] = 1; + } + $db->sql_freeresult($result); + + if (!sizeof($delete_rows)) + { + return false; + } + + $db->sql_transaction('begin'); + + if (sizeof($undelivered_msg)) + { + $sql = 'DELETE FROM ' . PRIVMSGS_TABLE . ' + WHERE ' . $db->sql_in_set('msg_id', $undelivered_msg); + $db->sql_query($sql); + } + + foreach ($undelivered_user as $_user_id => $ary) + { + if ($_user_id == $user_id) + { + continue; + } + + $sql = 'UPDATE ' . USERS_TABLE . ' + SET user_new_privmsg = user_new_privmsg - ' . sizeof($ary) . ', + user_unread_privmsg = user_unread_privmsg - ' . sizeof($ary) . ' + WHERE user_id = ' . $_user_id; + $db->sql_query($sql); + } + + // Delete private message data + $sql = 'DELETE FROM ' . PRIVMSGS_TO_TABLE . " + WHERE user_id = $user_id + AND " . $db->sql_in_set('msg_id', array_keys($delete_rows)); + $db->sql_query($sql); + + // Now we have to check which messages we can delete completely + $sql = 'SELECT msg_id + FROM ' . PRIVMSGS_TO_TABLE . ' + WHERE ' . $db->sql_in_set('msg_id', array_keys($delete_rows)); + $result = $db->sql_query($sql); + + while ($row = $db->sql_fetchrow($result)) + { + unset($delete_rows[$row['msg_id']]); + } + $db->sql_freeresult($result); + + $delete_ids = array_keys($delete_rows); + + if (sizeof($delete_ids)) + { + // Check if there are any attachments we need to remove + if (!function_exists('delete_attachments')) + { + include($phpbb_root_path . 'includes/functions_admin.' . $phpEx); + } + + delete_attachments('message', $delete_ids, false); + + $sql = 'DELETE FROM ' . PRIVMSGS_TABLE . ' + WHERE ' . $db->sql_in_set('msg_id', $delete_ids); + $db->sql_query($sql); + } + + // Set the remaining author id to anonymous - this way users are still able to read messages from users being removed + $sql = 'UPDATE ' . PRIVMSGS_TO_TABLE . ' + SET author_id = ' . ANONYMOUS . ' + WHERE author_id = ' . $user_id; + $db->sql_query($sql); + + $sql = 'UPDATE ' . PRIVMSGS_TABLE . ' + SET author_id = ' . ANONYMOUS . ' + WHERE author_id = ' . $user_id; + $db->sql_query($sql); + + $db->sql_transaction('commit'); + + return true; +} + /** * Rebuild message header */ -- cgit v1.2.1 From ba6943a6a0ea50af772dc6e94f13b56292cd9f37 Mon Sep 17 00:00:00 2001 From: Joas Schilling Date: Mon, 12 Mar 2012 10:11:52 +0100 Subject: [ticket/10605] Prefix function with phpbb_ and use true instead of 1 PHPBB3-10605 --- phpBB/includes/functions_privmsgs.php | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) (limited to 'phpBB/includes/functions_privmsgs.php') diff --git a/phpBB/includes/functions_privmsgs.php b/phpBB/includes/functions_privmsgs.php index 30cff8ed72..34f16ea9da 100644 --- a/phpBB/includes/functions_privmsgs.php +++ b/phpBB/includes/functions_privmsgs.php @@ -1086,7 +1086,7 @@ function delete_pm($user_id, $msg_ids, $folder_id) /** * Delete all PM(s) for a given user and delete the ones without references */ -function delete_user_pms($user_id) +function phpbb_delete_user_pms($user_id) { global $db, $user, $phpbb_root_path, $phpEx; @@ -1116,7 +1116,7 @@ function delete_user_pms($user_id) $undelivered_user[$row['user_id']][] = true; } - $delete_rows[$row['msg_id']] = 1; + $delete_rows[$row['msg_id']] = true; } $db->sql_freeresult($result); -- cgit v1.2.1 From e8830f605f73a0c8fa5c3ea579ee18f295b81600 Mon Sep 17 00:00:00 2001 From: Andreas Fischer Date: Tue, 27 Mar 2012 01:18:02 +0200 Subject: [ticket/10605] Use unset() instead of checking user_id over and over again. PHPBB3-10605 --- phpBB/includes/functions_privmsgs.php | 6 +----- 1 file changed, 1 insertion(+), 5 deletions(-) (limited to 'phpBB/includes/functions_privmsgs.php') diff --git a/phpBB/includes/functions_privmsgs.php b/phpBB/includes/functions_privmsgs.php index 34f16ea9da..59dea50094 100644 --- a/phpBB/includes/functions_privmsgs.php +++ b/phpBB/includes/functions_privmsgs.php @@ -1134,13 +1134,9 @@ function phpbb_delete_user_pms($user_id) $db->sql_query($sql); } + unset($undelivered_user[$user_id]); foreach ($undelivered_user as $_user_id => $ary) { - if ($_user_id == $user_id) - { - continue; - } - $sql = 'UPDATE ' . USERS_TABLE . ' SET user_new_privmsg = user_new_privmsg - ' . sizeof($ary) . ', user_unread_privmsg = user_unread_privmsg - ' . sizeof($ary) . ' -- cgit v1.2.1 From 2203bc204e55c33e2e9b212eeb0c7bf2e3060851 Mon Sep 17 00:00:00 2001 From: Andreas Fischer Date: Tue, 27 Mar 2012 01:29:29 +0200 Subject: [ticket/10605] Turn $undelivered_user into a real array of counters. PHPBB3-10605 --- phpBB/includes/functions_privmsgs.php | 16 ++++++++++++---- 1 file changed, 12 insertions(+), 4 deletions(-) (limited to 'phpBB/includes/functions_privmsgs.php') diff --git a/phpBB/includes/functions_privmsgs.php b/phpBB/includes/functions_privmsgs.php index 59dea50094..352bd4d15d 100644 --- a/phpBB/includes/functions_privmsgs.php +++ b/phpBB/includes/functions_privmsgs.php @@ -1113,7 +1113,15 @@ function phpbb_delete_user_pms($user_id) { // Undelivered messages $undelivered_msg[] = $row['msg_id']; - $undelivered_user[$row['user_id']][] = true; + + if (isset($undelivered_user[$row['user_id']])) + { + ++$undelivered_user[$row['user_id']]; + } + else + { + $undelivered_user[$row['user_id']] = 1; + } } $delete_rows[$row['msg_id']] = true; @@ -1135,11 +1143,11 @@ function phpbb_delete_user_pms($user_id) } unset($undelivered_user[$user_id]); - foreach ($undelivered_user as $_user_id => $ary) + foreach ($undelivered_user as $_user_id => $count) { $sql = 'UPDATE ' . USERS_TABLE . ' - SET user_new_privmsg = user_new_privmsg - ' . sizeof($ary) . ', - user_unread_privmsg = user_unread_privmsg - ' . sizeof($ary) . ' + SET user_new_privmsg = user_new_privmsg - ' . $count . ', + user_unread_privmsg = user_unread_privmsg - ' . $count . ' WHERE user_id = ' . $_user_id; $db->sql_query($sql); } -- cgit v1.2.1 From dbc7a69ad2bf6b062a4d7a40d62be29a410b5c18 Mon Sep 17 00:00:00 2001 From: Andreas Fischer Date: Tue, 27 Mar 2012 01:30:52 +0200 Subject: [ticket/10605] Remove unused variable declarations. PHPBB3-10605 --- phpBB/includes/functions_privmsgs.php | 1 - 1 file changed, 1 deletion(-) (limited to 'phpBB/includes/functions_privmsgs.php') diff --git a/phpBB/includes/functions_privmsgs.php b/phpBB/includes/functions_privmsgs.php index 352bd4d15d..4ff0c53420 100644 --- a/phpBB/includes/functions_privmsgs.php +++ b/phpBB/includes/functions_privmsgs.php @@ -1106,7 +1106,6 @@ function phpbb_delete_user_pms($user_id) $result = $db->sql_query($sql); $undelivered_msg = $undelivered_user = $delete_rows = array(); - $num_unread = $num_new = $num_deleted = 0; while ($row = $db->sql_fetchrow($result)) { if ($row['author_id'] == $user_id && $row['folder_id'] == PRIVMSGS_NO_BOX) -- cgit v1.2.1 From 9040f18d7cf9de137acbac6072dc2ffd3cede1b5 Mon Sep 17 00:00:00 2001 From: Andreas Fischer Date: Tue, 27 Mar 2012 01:35:01 +0200 Subject: [ticket/10605] Remove unnecessary array_keys calls on $delete_rows. PHPBB3-10605 --- phpBB/includes/functions_privmsgs.php | 6 +++--- 1 file changed, 3 insertions(+), 3 deletions(-) (limited to 'phpBB/includes/functions_privmsgs.php') diff --git a/phpBB/includes/functions_privmsgs.php b/phpBB/includes/functions_privmsgs.php index 4ff0c53420..00029a1986 100644 --- a/phpBB/includes/functions_privmsgs.php +++ b/phpBB/includes/functions_privmsgs.php @@ -1123,7 +1123,7 @@ function phpbb_delete_user_pms($user_id) } } - $delete_rows[$row['msg_id']] = true; + $delete_rows[(int) $row['msg_id']] = (int) $row['msg_id']; } $db->sql_freeresult($result); @@ -1154,13 +1154,13 @@ function phpbb_delete_user_pms($user_id) // Delete private message data $sql = 'DELETE FROM ' . PRIVMSGS_TO_TABLE . " WHERE user_id = $user_id - AND " . $db->sql_in_set('msg_id', array_keys($delete_rows)); + AND " . $db->sql_in_set('msg_id', $delete_rows); $db->sql_query($sql); // Now we have to check which messages we can delete completely $sql = 'SELECT msg_id FROM ' . PRIVMSGS_TO_TABLE . ' - WHERE ' . $db->sql_in_set('msg_id', array_keys($delete_rows)); + WHERE ' . $db->sql_in_set('msg_id', $delete_rows); $result = $db->sql_query($sql); while ($row = $db->sql_fetchrow($result)) -- cgit v1.2.1 From dd53d0576d8256e37074cc23dd5b9769acc12d1a Mon Sep 17 00:00:00 2001 From: Andreas Fischer Date: Tue, 27 Mar 2012 01:39:49 +0200 Subject: [ticket/10605] Remove unnecessary $delete_ids array. PHPBB3-10605 --- phpBB/includes/functions_privmsgs.php | 8 +++----- 1 file changed, 3 insertions(+), 5 deletions(-) (limited to 'phpBB/includes/functions_privmsgs.php') diff --git a/phpBB/includes/functions_privmsgs.php b/phpBB/includes/functions_privmsgs.php index 00029a1986..23f582641b 100644 --- a/phpBB/includes/functions_privmsgs.php +++ b/phpBB/includes/functions_privmsgs.php @@ -1169,9 +1169,7 @@ function phpbb_delete_user_pms($user_id) } $db->sql_freeresult($result); - $delete_ids = array_keys($delete_rows); - - if (sizeof($delete_ids)) + if (!empty($delete_rows)) { // Check if there are any attachments we need to remove if (!function_exists('delete_attachments')) @@ -1179,10 +1177,10 @@ function phpbb_delete_user_pms($user_id) include($phpbb_root_path . 'includes/functions_admin.' . $phpEx); } - delete_attachments('message', $delete_ids, false); + delete_attachments('message', $delete_rows, false); $sql = 'DELETE FROM ' . PRIVMSGS_TABLE . ' - WHERE ' . $db->sql_in_set('msg_id', $delete_ids); + WHERE ' . $db->sql_in_set('msg_id', $delete_rows); $db->sql_query($sql); } -- cgit v1.2.1 From ad073d22b9e543d5842af7b8ef94fe7e6b443504 Mon Sep 17 00:00:00 2001 From: Andreas Fischer Date: Tue, 27 Mar 2012 01:43:09 +0200 Subject: [ticket/10605] Break long comment into multiple lines 80 chars short. PHPBB3-10605 --- phpBB/includes/functions_privmsgs.php | 3 ++- 1 file changed, 2 insertions(+), 1 deletion(-) (limited to 'phpBB/includes/functions_privmsgs.php') diff --git a/phpBB/includes/functions_privmsgs.php b/phpBB/includes/functions_privmsgs.php index 23f582641b..e7beae3fab 100644 --- a/phpBB/includes/functions_privmsgs.php +++ b/phpBB/includes/functions_privmsgs.php @@ -1184,7 +1184,8 @@ function phpbb_delete_user_pms($user_id) $db->sql_query($sql); } - // Set the remaining author id to anonymous - this way users are still able to read messages from users being removed + // Set the remaining author id to anonymous + // This way users are still able to read messages from users being removed $sql = 'UPDATE ' . PRIVMSGS_TO_TABLE . ' SET author_id = ' . ANONYMOUS . ' WHERE author_id = ' . $user_id; -- cgit v1.2.1 From 9c8aab4d32a001ae66fe005b7124737fc5ad7dd6 Mon Sep 17 00:00:00 2001 From: Andreas Fischer Date: Tue, 27 Mar 2012 02:02:17 +0200 Subject: [ticket/10605] Rename $delete_rows to $delete_ids. PHPBB3-10605 --- phpBB/includes/functions_privmsgs.php | 18 +++++++++--------- 1 file changed, 9 insertions(+), 9 deletions(-) (limited to 'phpBB/includes/functions_privmsgs.php') diff --git a/phpBB/includes/functions_privmsgs.php b/phpBB/includes/functions_privmsgs.php index e7beae3fab..576da4d439 100644 --- a/phpBB/includes/functions_privmsgs.php +++ b/phpBB/includes/functions_privmsgs.php @@ -1105,7 +1105,7 @@ function phpbb_delete_user_pms($user_id) AND folder_id = ' . PRIVMSGS_NO_BOX . ')'; $result = $db->sql_query($sql); - $undelivered_msg = $undelivered_user = $delete_rows = array(); + $undelivered_msg = $undelivered_user = $delete_ids = array(); while ($row = $db->sql_fetchrow($result)) { if ($row['author_id'] == $user_id && $row['folder_id'] == PRIVMSGS_NO_BOX) @@ -1123,11 +1123,11 @@ function phpbb_delete_user_pms($user_id) } } - $delete_rows[(int) $row['msg_id']] = (int) $row['msg_id']; + $delete_ids[(int) $row['msg_id']] = (int) $row['msg_id']; } $db->sql_freeresult($result); - if (!sizeof($delete_rows)) + if (empty($delete_ids)) { return false; } @@ -1154,22 +1154,22 @@ function phpbb_delete_user_pms($user_id) // Delete private message data $sql = 'DELETE FROM ' . PRIVMSGS_TO_TABLE . " WHERE user_id = $user_id - AND " . $db->sql_in_set('msg_id', $delete_rows); + AND " . $db->sql_in_set('msg_id', $delete_ids); $db->sql_query($sql); // Now we have to check which messages we can delete completely $sql = 'SELECT msg_id FROM ' . PRIVMSGS_TO_TABLE . ' - WHERE ' . $db->sql_in_set('msg_id', $delete_rows); + WHERE ' . $db->sql_in_set('msg_id', $delete_ids); $result = $db->sql_query($sql); while ($row = $db->sql_fetchrow($result)) { - unset($delete_rows[$row['msg_id']]); + unset($delete_ids[$row['msg_id']]); } $db->sql_freeresult($result); - if (!empty($delete_rows)) + if (!empty($delete_ids)) { // Check if there are any attachments we need to remove if (!function_exists('delete_attachments')) @@ -1177,10 +1177,10 @@ function phpbb_delete_user_pms($user_id) include($phpbb_root_path . 'includes/functions_admin.' . $phpEx); } - delete_attachments('message', $delete_rows, false); + delete_attachments('message', $delete_ids, false); $sql = 'DELETE FROM ' . PRIVMSGS_TABLE . ' - WHERE ' . $db->sql_in_set('msg_id', $delete_rows); + WHERE ' . $db->sql_in_set('msg_id', $delete_ids); $db->sql_query($sql); } -- cgit v1.2.1 From 0397b462174887bda3fea4bcebf9a26f6b591a3e Mon Sep 17 00:00:00 2001 From: Joas Schilling Date: Tue, 27 Mar 2012 17:29:03 +0200 Subject: [ticket/10605] Split query to be able to use indexes PHPBB3-10605 --- phpBB/includes/functions_privmsgs.php | 34 +++++++++++++++++++++++++++++++--- 1 file changed, 31 insertions(+), 3 deletions(-) (limited to 'phpBB/includes/functions_privmsgs.php') diff --git a/phpBB/includes/functions_privmsgs.php b/phpBB/includes/functions_privmsgs.php index 576da4d439..dd81e8f92d 100644 --- a/phpBB/includes/functions_privmsgs.php +++ b/phpBB/includes/functions_privmsgs.php @@ -1098,11 +1098,11 @@ function phpbb_delete_user_pms($user_id) } // Get PM Information for later deleting + // The two queries where split, so we can use our indexes + // Part 1: get PMs the user received $sql = 'SELECT msg_id, author_id, folder_id, pm_unread, pm_new FROM ' . PRIVMSGS_TO_TABLE . ' - WHERE user_id = ' . $user_id . ' - OR (author_id = ' . $user_id . ' - AND folder_id = ' . PRIVMSGS_NO_BOX . ')'; + WHERE user_id = ' . $user_id; $result = $db->sql_query($sql); $undelivered_msg = $undelivered_user = $delete_ids = array(); @@ -1127,6 +1127,34 @@ function phpbb_delete_user_pms($user_id) } $db->sql_freeresult($result); + // Part 2: get PMs the user sent + $sql = 'SELECT msg_id, author_id, folder_id, pm_unread, pm_new + FROM ' . PRIVMSGS_TO_TABLE . ' + WHERE author_id = ' . $user_id . ' + AND folder_id = ' . PRIVMSGS_NO_BOX; + $result = $db->sql_query($sql); + + while ($row = $db->sql_fetchrow($result)) + { + if ($row['author_id'] == $user_id && $row['folder_id'] == PRIVMSGS_NO_BOX) + { + // Undelivered messages + $undelivered_msg[] = $row['msg_id']; + + if (isset($undelivered_user[$row['user_id']])) + { + ++$undelivered_user[$row['user_id']]; + } + else + { + $undelivered_user[$row['user_id']] = 1; + } + } + + $delete_ids[(int) $row['msg_id']] = (int) $row['msg_id']; + } + $db->sql_freeresult($result); + if (empty($delete_ids)) { return false; -- cgit v1.2.1 From b9324577aca6f7f7632b609e975e510e25d8ec86 Mon Sep 17 00:00:00 2001 From: Joas Schilling Date: Tue, 27 Mar 2012 17:32:55 +0200 Subject: =?UTF-8?q?[ticket/10605]=20Reset=20user=C2=B4s=20pm=20count=20to?= =?UTF-8?q?=200=20when=20deleting=20his=20PMs?= MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit PHPBB3-10605 --- phpBB/includes/functions_privmsgs.php | 12 +++++++++++- 1 file changed, 11 insertions(+), 1 deletion(-) (limited to 'phpBB/includes/functions_privmsgs.php') diff --git a/phpBB/includes/functions_privmsgs.php b/phpBB/includes/functions_privmsgs.php index dd81e8f92d..14a6d83b2a 100644 --- a/phpBB/includes/functions_privmsgs.php +++ b/phpBB/includes/functions_privmsgs.php @@ -1169,7 +1169,17 @@ function phpbb_delete_user_pms($user_id) $db->sql_query($sql); } - unset($undelivered_user[$user_id]); + // Reset the userīs pm count to 0 + if (isset($undelivered_user[$user_id])) + { + $sql = 'UPDATE ' . USERS_TABLE . ' + SET user_new_privmsg = 0, + user_unread_privmsg = 0 + WHERE user_id = ' . $user_id; + $db->sql_query($sql); + unset($undelivered_user[$user_id]); + } + foreach ($undelivered_user as $_user_id => $count) { $sql = 'UPDATE ' . USERS_TABLE . ' -- cgit v1.2.1 From 4b6b41a1e5cb3d4ba7c896dfa4d72082aa2f069e Mon Sep 17 00:00:00 2001 From: Joas Schilling Date: Fri, 13 Apr 2012 16:26:41 +0200 Subject: [ticket/10605] Add parameter documentation to phpbb_delete_user_pms PHPBB3-10605 --- phpBB/includes/functions_privmsgs.php | 4 ++++ 1 file changed, 4 insertions(+) (limited to 'phpBB/includes/functions_privmsgs.php') diff --git a/phpBB/includes/functions_privmsgs.php b/phpBB/includes/functions_privmsgs.php index 14a6d83b2a..00bec11569 100644 --- a/phpBB/includes/functions_privmsgs.php +++ b/phpBB/includes/functions_privmsgs.php @@ -1085,6 +1085,10 @@ function delete_pm($user_id, $msg_ids, $folder_id) /** * Delete all PM(s) for a given user and delete the ones without references +* +* @param int $user_id ID of the user whose private messages we want to delete +* +* @return boolean False if there were no pms found, true otherwise. */ function phpbb_delete_user_pms($user_id) { -- cgit v1.2.1