[Summary view] [Print] [Text view]

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