[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  namespace phpbb\db\migration\data\v33x;
  15  
  16  class profilefields_update extends \phpbb\db\migration\migration
  17  {
  18      /** @var string YouTube URLs matcher: handle or custom URL or channel URL */
  19      protected $youtube_url_matcher = '(@[a-zA-Z0-9_.-]{3,30}|c/[a-zA-Z][\w\.,\-_]+|(channel|user)/[a-zA-Z][\w\.,\-_]+)';
  20  
  21  	public static function depends_on(): array
  22      {
  23          return [
  24              '\phpbb\db\migration\data\v33x\v3310',
  25              '\phpbb\db\migration\data\v33x\profilefield_youtube_update',
  26          ];
  27      }
  28  
  29  	public function update_schema(): array
  30      {
  31          return [
  32              'change_columns'    => [
  33                  $this->table_prefix . 'profile_fields'            => [
  34                      'field_validation'        => ['VCHAR_UNI:128', ''],
  35                  ],
  36              ]
  37          ];
  38      }
  39  
  40  	public function revert_schema(): array
  41      {
  42          return [
  43              'change_columns'    => [
  44                  $this->table_prefix . 'profile_fields'            => [
  45                      'field_validation'        => ['VCHAR_UNI:64', ''],
  46                  ],
  47              ]
  48          ];
  49      }
  50  
  51  	public function update_data(): array
  52      {
  53          return [
  54              ['custom', [[$this, 'update_youtube_profile_field']]],
  55              ['custom', [[$this, 'update_other_profile_fields']]],
  56          ];
  57      }
  58  
  59  	public function revert_data(): array
  60      {
  61          return [
  62              ['custom', [[$this, 'revert_youtube_profile_field']]],
  63              ['custom', [[$this, 'revert_other_profile_fields']]],
  64          ];
  65      }
  66  
  67  	public function update_youtube_profile_field(): bool
  68      {
  69          $profile_fields = $this->table_prefix . 'profile_fields';
  70          $profile_fields_data = $this->table_prefix . 'profile_fields_data';
  71          $end_time = time() + 5; // allow up to 5 seconds for migration to run
  72  
  73          $field_data = [
  74              'field_length'            => 20,
  75              'field_minlen'            => 3,
  76              'field_maxlen'            => 60,
  77              'field_validation'        => $this->youtube_url_matcher,
  78              'field_contact_url'        => 'https://youtube.com/%s',
  79              'field_contact_desc'    => 'VIEW_YOUTUBE_PROFILE',
  80          ];
  81  
  82          $sql = 'UPDATE ' . $profile_fields . '
  83              SET ' . $this->db->sql_build_array('UPDATE', $field_data) . "
  84              WHERE field_name = 'phpbb_youtube'";
  85          $this->db->sql_query($sql);
  86  
  87          $yt_profile_field = 'pf_phpbb_youtube';
  88          $has_youtube_url = $this->db->sql_like_expression($this->db->get_any_char() . 'youtube.com/' . $this->db->get_any_char());
  89  
  90          // We're done if the profile field doesn't exist
  91          if (!$this->db_tools->sql_column_exists($profile_fields_data, $yt_profile_field))
  92          {
  93              return true;
  94          }
  95  
  96          $update_aborted = false;
  97  
  98          $sql = 'SELECT user_id, pf_phpbb_youtube
  99              FROM ' . $profile_fields_data . "
 100              WHERE $yt_profile_field <> ''
 101                  AND $yt_profile_field $has_youtube_url";
 102          $result = $this->db->sql_query($sql);
 103          while ($row = $this->db->sql_fetchrow($result))
 104          {
 105              $updated_youtube_url_part = $this->get_youtube_url_part($row['pf_phpbb_youtube']);
 106              if ($updated_youtube_url_part != $row['pf_phpbb_youtube'])
 107              {
 108                  $this->db->sql_query(
 109                      "UPDATE $profile_fields_data
 110                      SET $yt_profile_field = '$updated_youtube_url_part'
 111                      WHERE user_id = {$row['user_id']}"
 112                  );
 113              }
 114  
 115              if (time() > $end_time)
 116              {
 117                  $update_aborted = true;
 118                  break;
 119              }
 120          }
 121          $this->db->sql_freeresult($result);
 122  
 123          return $update_aborted != true;
 124      }
 125  
 126  	public function update_other_profile_fields(): void
 127      {
 128          $profile_fields = $this->table_prefix . 'profile_fields';
 129  
 130          $this->db->sql_query(
 131              "UPDATE $profile_fields
 132                  SET field_contact_url = 'https://facebook.com/%s/'
 133                  WHERE field_name = 'phpbb_facebook'"
 134          );
 135  
 136          $this->db->sql_query(
 137              "UPDATE $profile_fields
 138                  SET field_contact_url = 'https://twitter.com/%s'
 139                  WHERE field_name = 'phpbb_twitter'"
 140          );
 141      }
 142  
 143  	public function revert_youtube_profile_field(): void
 144      {
 145          $profile_fields = $this->table_prefix . 'profile_fields';
 146          $profile_fields_data = $this->table_prefix . 'profile_fields_data';
 147  
 148          $field_data = [
 149              'field_length'        => 40,
 150              'field_minlen'        => strlen('https://youtube.com/c/') + 1,
 151              'field_maxlen'        => 255,
 152              'field_validation'    => profilefield_youtube_update::$youtube_url_matcher,
 153              'field_contact_url'    => '%s'
 154          ];
 155  
 156          $sql = 'UPDATE ' . $profile_fields . '
 157              SET ' . $this->db->sql_build_array('UPDATE', $field_data) . "
 158              WHERE field_name = 'phpbb_youtube'";
 159          $this->db->sql_query($sql);
 160  
 161          $yt_profile_field = 'pf_phpbb_youtube';
 162  
 163          // We're done if the profile field doesn't exist
 164          if (!$this->db_tools->sql_column_exists($profile_fields_data, $yt_profile_field))
 165          {
 166              return;
 167          }
 168  
 169          $prepend_legacy_youtube_url = $this->db->sql_concatenate(
 170              "'https://youtube.com/'", $yt_profile_field
 171          );
 172          $is_not_already_youtube_url = $this->db->sql_not_like_expression(
 173              $this->db->get_any_char()
 174              . 'youtube.com/'
 175              . $this->db->get_any_char()
 176          );
 177  
 178          $this->db->sql_query(
 179              "UPDATE $profile_fields_data SET
 180                  $yt_profile_field = $prepend_legacy_youtube_url
 181                  WHERE $yt_profile_field <> ''
 182                  AND $yt_profile_field $is_not_already_youtube_url"
 183          );
 184      }
 185  
 186  	public function revert_other_profile_fields(): void
 187      {
 188          $profile_fields = $this->table_prefix . 'profile_fields';
 189  
 190          $this->db->sql_query(
 191              "UPDATE $profile_fields
 192                  SET field_contact_url = 'http://facebook.com/%s/'
 193                  WHERE field_name = 'phpbb_facebook'"
 194          );
 195  
 196          $this->db->sql_query(
 197              "UPDATE $profile_fields
 198                  SET field_contact_url = 'http://twitter.com/%s'
 199                  WHERE field_name = 'phpbb_twitter'"
 200          );
 201      }
 202  
 203  	protected function get_youtube_url_part(string $profile_field_string): string
 204      {
 205          return preg_replace('#^https://(?:www\.)?youtube\.com/(.+)$#iu', '$1', $profile_field_string);
 206      }
 207  }