[Summary view] [Print] [Text view]

   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  */
  17  if (!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  */
  36  function 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  */
 121  function 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  }