[Summary view] [Print] [Text view]

   1  <?php
   2  
   3  /**
   4   * Joomla! Content Management System
   5   *
   6   * @copyright  (C) 2011 Open Source Matters, Inc. <https://www.joomla.org>
   7   * @license    GNU General Public License version 2 or later; see LICENSE.txt
   8   */
   9  
  10  namespace Joomla\CMS\Schema\ChangeItem;
  11  
  12  use Joomla\CMS\Schema\ChangeItem;
  13  
  14  // phpcs:disable PSR1.Files.SideEffects
  15  \defined('JPATH_PLATFORM') or die;
  16  // phpcs:enable PSR1.Files.SideEffects
  17  
  18  /**
  19   * Checks the database schema against one MySQL DDL query to see if it has been run.
  20   *
  21   * @since  2.5
  22   */
  23  class MysqlChangeItem extends ChangeItem
  24  {
  25      /**
  26       * Checks a DDL query to see if it is a known type
  27       * If yes, build a check query to see if the DDL has been run on the database.
  28       * If successful, the $msgElements, $queryType, $checkStatus and $checkQuery fields are populated.
  29       * The $msgElements contains the text to create the user message.
  30       * The $checkQuery contains the SQL query to check whether the schema change has
  31       * been run against the current database. The $queryType contains the type of
  32       * DDL query that was run (for example, CREATE_TABLE, ADD_COLUMN, CHANGE_COLUMN_TYPE, ADD_INDEX).
  33       * The $checkStatus field is set to zero if the query is created
  34       *
  35       * If not successful, $checkQuery is empty and , and $checkStatus is -1.
  36       * For example, this will happen if the current line is a non-DDL statement.
  37       *
  38       * @return void
  39       *
  40       * @since  2.5
  41       */
  42      protected function buildCheckQuery()
  43      {
  44          // Initialize fields in case we can't create a check query
  45  
  46          // Change status to skipped
  47          $this->checkStatus = -1;
  48          $result = null;
  49  
  50          // Remove any newlines
  51          $this->updateQuery = str_replace("\n", '', $this->updateQuery);
  52  
  53          // Fix up extra spaces around () and in general
  54          $find = array('#((\s*)\(\s*([^)\s]+)\s*)(\))#', '#(\s)(\s*)#');
  55          $replace = array('($3)', '$1');
  56          $updateQuery = preg_replace($find, $replace, $this->updateQuery);
  57          $wordArray = preg_split("~'[^']*'(*SKIP)(*F)|\s+~u", trim($updateQuery, "; \t\n\r\0\x0B"));
  58  
  59          // First, make sure we have an array of at least 5 elements
  60          // if not, we can't make a check query for this one
  61          if (\count($wordArray) < 5) {
  62              // Done with method
  63              return;
  64          }
  65  
  66          // We can only make check queries for rename table, alter table and create table queries
  67          $command = strtoupper($wordArray[0] . ' ' . $wordArray[1]);
  68  
  69          if ($command === 'RENAME TABLE') {
  70              $table = $this->fixQuote($wordArray[4]);
  71  
  72              $this->checkQuery  = 'SHOW TABLES LIKE ' . $table;
  73              $this->queryType   = 'RENAME_TABLE';
  74              $this->msgElements = array($table);
  75              $this->checkStatus = 0;
  76  
  77              // Done with method
  78              return;
  79          }
  80  
  81          // For the remaining query types make sure we have an array of at least 6 elements
  82          if (\count($wordArray) < 6) {
  83              // Done with method
  84              return;
  85          }
  86  
  87          if ($command === 'ALTER TABLE') {
  88              $alterCommand = strtoupper($wordArray[3] . ' ' . $wordArray[4]);
  89  
  90              if ($alterCommand === 'ADD COLUMN') {
  91                  $result = 'SHOW COLUMNS IN ' . $wordArray[2] . ' WHERE field = ' . $this->fixQuote($wordArray[5]);
  92                  $this->queryType = 'ADD_COLUMN';
  93                  $this->msgElements = array($this->fixQuote($wordArray[2]), $this->fixQuote($wordArray[5]));
  94              } elseif ($alterCommand === 'ADD INDEX' || $alterCommand === 'ADD KEY') {
  95                  if ($pos = strpos($wordArray[5], '(')) {
  96                      $index = $this->fixQuote(substr($wordArray[5], 0, $pos));
  97                  } else {
  98                      $index = $this->fixQuote($wordArray[5]);
  99                  }
 100  
 101                  $result = 'SHOW INDEXES IN ' . $wordArray[2] . ' WHERE Key_name = ' . $index;
 102                  $this->queryType = 'ADD_INDEX';
 103                  $this->msgElements = array($this->fixQuote($wordArray[2]), $index);
 104              } elseif ($alterCommand === 'ADD UNIQUE') {
 105                  $idxIndexName = 5;
 106  
 107                  if (isset($wordArray[6])) {
 108                      $addCmdCheck = strtoupper($wordArray[5]);
 109  
 110                      if ($addCmdCheck === 'INDEX' || $addCmdCheck === 'KEY') {
 111                          $idxIndexName = 6;
 112                      }
 113                  }
 114  
 115                  if ($pos = strpos($wordArray[$idxIndexName], '(')) {
 116                      $index = $this->fixQuote(substr($wordArray[$idxIndexName], 0, $pos));
 117                  } else {
 118                      $index = $this->fixQuote($wordArray[$idxIndexName]);
 119                  }
 120  
 121                  $result = 'SHOW INDEXES IN ' . $wordArray[2] . ' WHERE Key_name = ' . $index;
 122                  $this->queryType = 'ADD_INDEX';
 123                  $this->msgElements = array($this->fixQuote($wordArray[2]), $index);
 124              } elseif ($alterCommand === 'DROP INDEX' || $alterCommand === 'DROP KEY') {
 125                  $index = $this->fixQuote($wordArray[5]);
 126                  $result = 'SHOW INDEXES IN ' . $wordArray[2] . ' WHERE Key_name = ' . $index;
 127                  $this->queryType = 'DROP_INDEX';
 128                  $this->checkQueryExpected = 0;
 129                  $this->msgElements = array($this->fixQuote($wordArray[2]), $index);
 130              } elseif ($alterCommand === 'DROP COLUMN') {
 131                  $index = $this->fixQuote($wordArray[5]);
 132                  $result = 'SHOW COLUMNS IN ' . $wordArray[2] . ' WHERE Field = ' . $index;
 133                  $this->queryType = 'DROP_COLUMN';
 134                  $this->checkQueryExpected = 0;
 135                  $this->msgElements = array($this->fixQuote($wordArray[2]), $index);
 136              } elseif (strtoupper($wordArray[3]) === 'MODIFY') {
 137                  // Kludge to fix problem with "integer unsigned"
 138                  $type = $wordArray[5];
 139  
 140                  if (isset($wordArray[6])) {
 141                      $type = $this->fixInteger($wordArray[5], $wordArray[6]);
 142                  }
 143  
 144                  // Detect changes in NULL and in DEFAULT column attributes
 145                  $changesArray = \array_slice($wordArray, 6);
 146                  $defaultCheck = $this->checkDefault($changesArray, $type);
 147                  $nullCheck = $this->checkNull($changesArray);
 148  
 149                  /**
 150                   * When we made the UTF8MB4 conversion then text becomes medium text - so loosen the checks to these two types
 151                   * otherwise (for example) the profile fields profile_value check fails - see https://github.com/joomla/joomla-cms/issues/9258
 152                   */
 153                  $typeCheck = $this->fixUtf8mb4TypeChecks($type);
 154  
 155                  $result = 'SHOW COLUMNS IN ' . $wordArray[2] . ' WHERE field = ' . $this->fixQuote($wordArray[4])
 156                      . ' AND ' . $typeCheck
 157                      . ($defaultCheck ? ' AND ' . $defaultCheck : '')
 158                      . ($nullCheck ? ' AND ' . $nullCheck : '');
 159                  $this->queryType = 'CHANGE_COLUMN_TYPE';
 160                  $this->msgElements = array($this->fixQuote($wordArray[2]), $this->fixQuote($wordArray[4]), $type);
 161              } elseif (strtoupper($wordArray[3]) === 'CHANGE') {
 162                  // Kludge to fix problem with "integer unsigned"
 163                  $type = $wordArray[6];
 164  
 165                  if (isset($wordArray[7])) {
 166                      $type = $this->fixInteger($wordArray[6], $wordArray[7]);
 167                  }
 168  
 169                  // Detect changes in NULL and in DEFAULT column attributes
 170                  $changesArray = \array_slice($wordArray, 6);
 171                  $defaultCheck = $this->checkDefault($changesArray, $type);
 172                  $nullCheck = $this->checkNull($changesArray);
 173  
 174                  /**
 175                   * When we made the UTF8MB4 conversion then text becomes medium text - so loosen the checks to these two types
 176                   * otherwise (for example) the profile fields profile_value check fails - see https://github.com/joomla/joomla-cms/issues/9258
 177                   */
 178                  $typeCheck = $this->fixUtf8mb4TypeChecks($type);
 179  
 180                  $result = 'SHOW COLUMNS IN ' . $wordArray[2] . ' WHERE field = ' . $this->fixQuote($wordArray[5])
 181                      . ' AND ' . $typeCheck
 182                      . ($defaultCheck ? ' AND ' . $defaultCheck : '')
 183                      . ($nullCheck ? ' AND ' . $nullCheck : '');
 184                  $this->queryType = 'CHANGE_COLUMN_TYPE';
 185                  $this->msgElements = array($this->fixQuote($wordArray[2]), $this->fixQuote($wordArray[5]), $type);
 186              }
 187          }
 188  
 189          if ($command === 'CREATE TABLE') {
 190              if (strtoupper($wordArray[2] . $wordArray[3] . $wordArray[4]) === 'IFNOTEXISTS') {
 191                  $table = $wordArray[5];
 192              } else {
 193                  $table = $wordArray[2];
 194              }
 195  
 196              $result = 'SHOW TABLES LIKE ' . $this->fixQuote($table);
 197              $this->queryType = 'CREATE_TABLE';
 198              $this->msgElements = array($this->fixQuote($table));
 199          }
 200  
 201          // Set fields based on results
 202          if ($this->checkQuery = $result) {
 203              // Unchecked status
 204              $this->checkStatus = 0;
 205          } else {
 206              // Skipped
 207              $this->checkStatus = -1;
 208          }
 209      }
 210  
 211      /**
 212       * Fix up integer. Fixes problem with MySQL integer descriptions.
 213       * On MySQL 8 display length is not shown anymore.
 214       * This means we have to match e.g. both "int(10) unsigned" and
 215       * "int unsigned", or both "int(11)" and "int" and so on.
 216       * The same applies to the other integer data types "tinyint",
 217       * "smallint", "mediumint" and "bigint".
 218       *
 219       * @param   string  $type1  the column type
 220       * @param   string  $type2  the column attributes
 221       *
 222       * @return  string  The original or changed column type.
 223       *
 224       * @since   2.5
 225       */
 226      private function fixInteger($type1, $type2)
 227      {
 228          $result = $type1;
 229  
 230          if (preg_match('/^(?P<type>(big|medium|small|tiny)?int)(\([0-9]+\))?$/i', $type1, $matches)) {
 231              $result = strtolower($matches['type']);
 232          }
 233  
 234          if (strtolower(substr($type2, 0, 8)) === 'unsigned') {
 235              $result .= ' unsigned';
 236          }
 237  
 238          return $result;
 239      }
 240  
 241      /**
 242       * Fixes up a string for inclusion in a query.
 243       * Replaces name quote character with normal quote for literal.
 244       * Drops trailing semicolon. Injects the database prefix.
 245       *
 246       * @param   string  $string  The input string to be cleaned up.
 247       *
 248       * @return  string  The modified string.
 249       *
 250       * @since   2.5
 251       */
 252      private function fixQuote($string)
 253      {
 254          $string = str_replace('`', '', $string);
 255          $string = str_replace(';', '', $string);
 256          $string = str_replace('#__', $this->db->getPrefix(), $string);
 257  
 258          return $this->db->quote($string);
 259      }
 260  
 261      /**
 262       * Make check query for column changes/modifications tolerant
 263       * for automatic type changes of text columns, e.g. from TEXT
 264       * to MEDIUMTEXT, after conversion from utf8 to utf8mb4, and
 265       * fix integer columns without display length for MySQL 8
 266       * (see also function "fixInteger" above).
 267       *
 268       * @param   string  $type  The column type found in the update query
 269       *
 270       * @return  string  The condition for type check in the check query
 271       *
 272       * @since   3.5
 273       */
 274      private function fixUtf8mb4TypeChecks($type)
 275      {
 276          $uType = strtoupper(str_replace(';', '', $type));
 277  
 278          switch ($uType) {
 279              case 'BIGINT UNSIGNED':
 280              case 'INT UNSIGNED':
 281              case 'MEDIUMINT UNSIGNED':
 282              case 'SMALLINT UNSIGNED':
 283              case 'TINYINT UNSIGNED':
 284                  // Eg for "INT": "UPPER(type) REGEXP '^INT([(][0-9]+[)])? UNSIGNED$'"
 285                  $typeCheck = 'UPPER(type) REGEXP ' . $this->db->quote('^' . str_replace(' ', '([(][0-9]+[)])? ', $uType) . '$');
 286                  break;
 287  
 288              case 'BIGINT':
 289              case 'INT':
 290              case 'MEDIUMINT':
 291              case 'SMALLINT':
 292              case 'TINYINT':
 293                  // Eg for "INT": "UPPER(type) REGEXP '^INT([(][0-9]+[)])?$'"
 294                  $typeCheck = 'UPPER(type) REGEXP ' . $this->db->quote('^' . $uType . '([(][0-9]+[)])?$');
 295                  break;
 296  
 297              case 'MEDIUMTEXT':
 298                  $typeCheck = $this->db->hasUTF8mb4Support()
 299                      ? 'UPPER(type) IN (' . $this->db->quote('MEDIUMTEXT') . ',' . $this->db->quote('LONGTEXT') . ')'
 300                      : 'UPPER(type) = ' . $this->db->quote('MEDIUMTEXT');
 301                  break;
 302  
 303              case 'TEXT':
 304                  $typeCheck = $this->db->hasUTF8mb4Support()
 305                      ? 'UPPER(type) IN (' . $this->db->quote('TEXT') . ',' . $this->db->quote('MEDIUMTEXT') . ')'
 306                      : 'UPPER(type) = ' . $this->db->quote('TEXT');
 307                  break;
 308  
 309              case 'TINYTEXT':
 310                  $typeCheck = $this->db->hasUTF8mb4Support()
 311                      ? 'UPPER(type) IN (' . $this->db->quote('TINYTEXT') . ',' . $this->db->quote('TEXT') . ')'
 312                      : 'UPPER(type) = ' . $this->db->quote('TINYTEXT');
 313                  break;
 314  
 315              default:
 316                  $typeCheck = 'UPPER(type) = ' . $this->db->quote($uType);
 317          }
 318  
 319          return $typeCheck;
 320      }
 321  
 322      /**
 323       * Create query clause for column changes/modifications for NULL attribute
 324       *
 325       * @param   array  $changesArray  The array of words after COLUMN name
 326       *
 327       * @return  string  The query clause for NULL check in the check query
 328       *
 329       * @since   3.8.6
 330       */
 331      private function checkNull($changesArray)
 332      {
 333          // Find NULL keyword
 334          $index = array_search('null', array_map('strtolower', $changesArray));
 335  
 336          // Create the check
 337          if ($index !== false) {
 338              if ($index == 0 || strtolower($changesArray[$index - 1]) !== 'not') {
 339                  return ' `null` = ' . $this->db->quote('YES');
 340              } else {
 341                  return ' `null` = ' . $this->db->quote('NO');
 342              }
 343          }
 344  
 345          return false;
 346      }
 347  
 348      /**
 349       * Create query clause for column changes/modifications for DEFAULT attribute
 350       *
 351       * @param   array   $changesArray  The array of words after COLUMN name
 352       * @param   string  $type          The type of the COLUMN
 353       *
 354       * @return  string  The query clause for DEFAULT check in the check query
 355       *
 356       * @since   3.8.6
 357       */
 358      private function checkDefault($changesArray, $type)
 359      {
 360          // Skip types that do not support default values
 361          $type = strtolower($type);
 362  
 363          if (substr($type, -4) === 'text' || substr($type, -4) === 'blob') {
 364              return false;
 365          }
 366  
 367          // Find DEFAULT keyword
 368          $index = array_search('default', array_map('strtolower', $changesArray));
 369  
 370          // Create the check
 371          if ($index !== false) {
 372              if (strtolower($changesArray[$index + 1]) === 'null') {
 373                  return ' `default` IS NULL';
 374              } else {
 375                  return ' `default` = ' . $changesArray[$index + 1];
 376              }
 377          }
 378  
 379          return false;
 380      }
 381  }