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