1<?php 2/** 3* 4* This file is part of the phpBB Forum Software package. 5* 6* @copyright (c) phpBB Limited <https://www.phpbb.com> 7* @license GNU General Public License, version 2 (GPL-2.0) 8* 9* For full copyright and license information, please see 10* the docs/CREDITS.txt file. 11* 12*/ 13 14/** 15* @ignore 16*/ 17if (!defined('IN_PHPBB')) 18{ 19 exit; 20} 21 22/** 23* Updates rows in given table from a set of values to a new value. 24* If this results in rows violating uniqueness constraints, the duplicate 25* rows are eliminated. 26* 27* The only supported table is bookmarks. 28* 29* @param \phpbb\db\driver\driver_interface $db Database object 30* @param string $table Table on which to perform the update 31* @param string $column Column whose values to change 32* @param array $from_values An array of values that should be changed 33* @param int $to_value The new value 34* @return null 35*/ 36function phpbb_update_rows_avoiding_duplicates(\phpbb\db\driver\driver_interface $db, $table, $column, $from_values, $to_value) 37{ 38 $sql = "SELECT $column, user_id 39 FROM $table 40 WHERE " . $db->sql_in_set($column, $from_values); 41 $result = $db->sql_query($sql); 42 43 $old_user_ids = array(); 44 while ($row = $db->sql_fetchrow($result)) 45 { 46 $old_user_ids[$row[$column]][] = (int) $row['user_id']; 47 } 48 $db->sql_freeresult($result); 49 50 $sql = "SELECT $column, user_id 51 FROM $table 52 WHERE $column = " . (int) $to_value; 53 $result = $db->sql_query($sql); 54 55 $new_user_ids = array(); 56 while ($row = $db->sql_fetchrow($result)) 57 { 58 $new_user_ids[$row[$column]][] = (int) $row['user_id']; 59 } 60 $db->sql_freeresult($result); 61 62 $queries = array(); 63 foreach ($from_values as $from_value) 64 { 65 if (!isset($old_user_ids[$from_value])) 66 { 67 continue; 68 } 69 if (empty($new_user_ids)) 70 { 71 $sql = "UPDATE $table 72 SET $column = " . (int) $to_value . " 73 WHERE $column = '" . $db->sql_escape($from_value) . "'"; 74 $queries[] = $sql; 75 } 76 else 77 { 78 $different_user_ids = array_diff($old_user_ids[$from_value], $new_user_ids[$to_value]); 79 if (!empty($different_user_ids)) 80 { 81 $sql = "UPDATE $table 82 SET $column = " . (int) $to_value . " 83 WHERE $column = '" . $db->sql_escape($from_value) . "' 84 AND " . $db->sql_in_set('user_id', $different_user_ids); 85 $queries[] = $sql; 86 } 87 } 88 } 89 90 if (!empty($queries)) 91 { 92 $db->sql_transaction('begin'); 93 94 foreach ($queries as $sql) 95 { 96 $db->sql_query($sql); 97 } 98 99 $sql = "DELETE FROM $table 100 WHERE " . $db->sql_in_set($column, $from_values); 101 $db->sql_query($sql); 102 103 $db->sql_transaction('commit'); 104 } 105} 106 107/** 108* Updates rows in given table from a set of values to a new value. 109* If this results in rows violating uniqueness constraints, the duplicate 110* rows are merged respecting notify_status (0 takes precedence over 1). 111* 112* The only supported table is topics_watch. 113* 114* @param \phpbb\db\driver\driver_interface $db Database object 115* @param string $table Table on which to perform the update 116* @param string $column Column whose values to change 117* @param array $from_values An array of values that should be changed 118* @param int $to_value The new value 119* @return null 120*/ 121function phpbb_update_rows_avoiding_duplicates_notify_status(\phpbb\db\driver\driver_interface $db, $table, $column, $from_values, $to_value) 122{ 123 $sql = "SELECT $column, user_id, notify_status 124 FROM $table 125 WHERE " . $db->sql_in_set($column, $from_values); 126 $result = $db->sql_query($sql); 127 128 $old_user_ids = array(); 129 while ($row = $db->sql_fetchrow($result)) 130 { 131 $old_user_ids[(int) $row['notify_status']][$row[$column]][] = (int) $row['user_id']; 132 } 133 $db->sql_freeresult($result); 134 135 $sql = "SELECT $column, user_id 136 FROM $table 137 WHERE $column = " . (int) $to_value; 138 $result = $db->sql_query($sql); 139 140 $new_user_ids = array(); 141 while ($row = $db->sql_fetchrow($result)) 142 { 143 $new_user_ids[$row[$column]][] = (int) $row['user_id']; 144 } 145 $db->sql_freeresult($result); 146 147 $queries = array(); 148 $extra_updates = array( 149 0 => 'notify_status = 0', 150 1 => '', 151 ); 152 foreach ($from_values as $from_value) 153 { 154 foreach ($extra_updates as $notify_status => $extra_update) 155 { 156 if (!isset($old_user_ids[$notify_status][$from_value])) 157 { 158 continue; 159 } 160 if (empty($new_user_ids)) 161 { 162 $sql = "UPDATE $table 163 SET $column = " . (int) $to_value . " 164 WHERE $column = '" . $db->sql_escape($from_value) . "'"; 165 $queries[] = $sql; 166 } 167 else 168 { 169 $different_user_ids = array_diff($old_user_ids[$notify_status][$from_value], $new_user_ids[$to_value]); 170 if (!empty($different_user_ids)) 171 { 172 $sql = "UPDATE $table 173 SET $column = " . (int) $to_value . " 174 WHERE $column = '" . $db->sql_escape($from_value) . "' 175 AND " . $db->sql_in_set('user_id', $different_user_ids); 176 $queries[] = $sql; 177 } 178 179 if ($extra_update) 180 { 181 $same_user_ids = array_diff($old_user_ids[$notify_status][$from_value], $different_user_ids); 182 if (!empty($same_user_ids)) 183 { 184 $sql = "UPDATE $table 185 SET $extra_update 186 WHERE $column = '" . (int) $to_value . "' 187 AND " . $db->sql_in_set('user_id', $same_user_ids); 188 $queries[] = $sql; 189 } 190 } 191 } 192 } 193 } 194 195 if (!empty($queries)) 196 { 197 $db->sql_transaction('begin'); 198 199 foreach ($queries as $sql) 200 { 201 $db->sql_query($sql); 202 } 203 204 $sql = "DELETE FROM $table 205 WHERE " . $db->sql_in_set($column, $from_values); 206 $db->sql_query($sql); 207 208 $db->sql_transaction('commit'); 209 } 210} 211