[Summary view] [Print] [Text view]

   1  <?php
   2  /**
   3   * Part of the Joomla Framework Database Package
   4   *
   5   * @copyright  Copyright (C) 2005 - 2021 Open Source Matters, Inc. All rights reserved.
   6   * @license    GNU General Public License version 2 or later; see LICENSE
   7   */
   8  
   9  namespace Joomla\Database\Mysql;
  10  
  11  use Joomla\Database\DatabaseImporter;
  12  
  13  /**
  14   * MySQL Database Importer.
  15   *
  16   * @since  1.0
  17   */
  18  class MysqlImporter extends DatabaseImporter
  19  {
  20      /**
  21       * Checks if all data and options are in order prior to exporting.
  22       *
  23       * @return  $this
  24       *
  25       * @since   1.0
  26       * @throws  \RuntimeException
  27       */
  28  	public function check()
  29      {
  30          // Check if the db connector has been set.
  31          if (!($this->db instanceof MysqlDriver))
  32          {
  33              throw new \RuntimeException('Database connection wrong type.');
  34          }
  35  
  36          // Check if the tables have been specified.
  37          if (empty($this->from))
  38          {
  39              throw new \RuntimeException('ERROR: No Tables Specified');
  40          }
  41  
  42          return $this;
  43      }
  44  
  45      /**
  46       * Get the SQL syntax to add a key.
  47       *
  48       * @param   string  $table  The table name.
  49       * @param   array   $keys   An array of the fields pertaining to this key.
  50       *
  51       * @return  string
  52       *
  53       * @since   1.0
  54       */
  55  	protected function getAddKeySql($table, $keys)
  56      {
  57          return 'ALTER TABLE ' . $this->db->quoteName($table) . ' ADD ' . $this->getKeySql($keys);
  58      }
  59  
  60      /**
  61       * Get alters for table if there is a difference.
  62       *
  63       * @param   \SimpleXMLElement  $structure  The XML structure of the table.
  64       *
  65       * @return  array
  66       *
  67       * @since   1.0
  68       */
  69  	protected function getAlterTableSql(\SimpleXMLElement $structure)
  70      {
  71          // Initialise variables.
  72          $table     = $this->getRealTableName($structure['name']);
  73          $oldFields = $this->db->getTableColumns($table);
  74          $oldKeys   = $this->db->getTableKeys($table);
  75          $alters    = [];
  76  
  77          // Get the fields and keys from the XML that we are aiming for.
  78          $newFields = $structure->xpath('field');
  79          $newKeys   = $structure->xpath('key');
  80  
  81          // Loop through each field in the new structure.
  82          foreach ($newFields as $field)
  83          {
  84              $fName = (string) $field['Field'];
  85  
  86              if (isset($oldFields[$fName]))
  87              {
  88                  // The field exists, check it's the same.
  89                  $column = $oldFields[$fName];
  90  
  91                  // Test whether there is a change.
  92                  $change = ((string) $field['Type'] !== $column->Type) || ((string) $field['Null'] !== $column->Null)
  93                      || ((string) $field['Default'] !== $column->Default) || ((string) $field['Extra'] !== $column->Extra);
  94  
  95                  if ($change)
  96                  {
  97                      $alters[] = $this->getChangeColumnSql($table, $field);
  98                  }
  99  
 100                  // Unset this field so that what we have left are fields that need to be removed.
 101                  unset($oldFields[$fName]);
 102              }
 103              else
 104              {
 105                  // The field is new.
 106                  $alters[] = $this->getAddColumnSql($table, $field);
 107              }
 108          }
 109  
 110          // Any columns left are orphans
 111          foreach ($oldFields as $name => $column)
 112          {
 113              // Delete the column.
 114              $alters[] = $this->getDropColumnSql($table, $name);
 115          }
 116  
 117          // Get the lookups for the old and new keys.
 118          $oldLookup = $this->getKeyLookup($oldKeys);
 119          $newLookup = $this->getKeyLookup($newKeys);
 120  
 121          // Loop through each key in the new structure.
 122          foreach ($newLookup as $name => $keys)
 123          {
 124              // Check if there are keys on this field in the existing table.
 125              if (isset($oldLookup[$name]))
 126              {
 127                  $same     = true;
 128                  $newCount = \count($newLookup[$name]);
 129                  $oldCount = \count($oldLookup[$name]);
 130  
 131                  // There is a key on this field in the old and new tables. Are they the same?
 132                  if ($newCount === $oldCount)
 133                  {
 134                      // Need to loop through each key and do a fine grained check.
 135                      for ($i = 0; $i < $newCount; $i++)
 136                      {
 137                          $same = (((string) $newLookup[$name][$i]['Non_unique'] === $oldLookup[$name][$i]->Non_unique)
 138                              && ((string) $newLookup[$name][$i]['Column_name'] === $oldLookup[$name][$i]->Column_name)
 139                              && ((string) $newLookup[$name][$i]['Seq_in_index'] === $oldLookup[$name][$i]->Seq_in_index)
 140                              && ((string) $newLookup[$name][$i]['Collation'] === $oldLookup[$name][$i]->Collation)
 141                              && ((string) $newLookup[$name][$i]['Sub_part'] === $oldLookup[$name][$i]->Sub_part)
 142                              && ((string) $newLookup[$name][$i]['Index_type'] === $oldLookup[$name][$i]->Index_type));
 143  
 144                          /*
 145                          Debug.
 146                          echo '<pre>';
 147                          echo '<br>Non_unique:   '.
 148                              ((string) $newLookup[$name][$i]['Non_unique'] == $oldLookup[$name][$i]->Non_unique ? 'Pass' : 'Fail').' '.
 149                              (string) $newLookup[$name][$i]['Non_unique'].' vs '.$oldLookup[$name][$i]->Non_unique;
 150                          echo '<br>Column_name:  '.
 151                              ((string) $newLookup[$name][$i]['Column_name'] == $oldLookup[$name][$i]->Column_name ? 'Pass' : 'Fail').' '.
 152                              (string) $newLookup[$name][$i]['Column_name'].' vs '.$oldLookup[$name][$i]->Column_name;
 153                          echo '<br>Seq_in_index: '.
 154                              ((string) $newLookup[$name][$i]['Seq_in_index'] == $oldLookup[$name][$i]->Seq_in_index ? 'Pass' : 'Fail').' '.
 155                              (string) $newLookup[$name][$i]['Seq_in_index'].' vs '.$oldLookup[$name][$i]->Seq_in_index;
 156                          echo '<br>Collation:    '.
 157                              ((string) $newLookup[$name][$i]['Collation'] == $oldLookup[$name][$i]->Collation ? 'Pass' : 'Fail').' '.
 158                              (string) $newLookup[$name][$i]['Collation'].' vs '.$oldLookup[$name][$i]->Collation;
 159                          echo '<br>Sub_part:    '.
 160                              ((string) $newLookup[$name][$i]['Sub_part'] == $oldLookup[$name][$i]->Sub_part ? 'Pass' : 'Fail').' '.
 161                              (string) $newLookup[$name][$i]['Sub_part'].' vs '.$oldLookup[$name][$i]->Sub_part;
 162                          echo '<br>Index_type:   '.
 163                              ((string) $newLookup[$name][$i]['Index_type'] == $oldLookup[$name][$i]->Index_type ? 'Pass' : 'Fail').' '.
 164                              (string) $newLookup[$name][$i]['Index_type'].' vs '.$oldLookup[$name][$i]->Index_type;
 165                          echo '<br>Same = '.($same ? 'true' : 'false');
 166                          echo '</pre>';
 167                           */
 168  
 169                          if (!$same)
 170                          {
 171                              // Break out of the loop. No need to check further.
 172                              break;
 173                          }
 174                      }
 175                  }
 176                  else
 177                  {
 178                      // Count is different, just drop and add.
 179                      $same = false;
 180                  }
 181  
 182                  if (!$same)
 183                  {
 184                      $alters[] = $this->getDropKeySql($table, $name);
 185                      $alters[] = $this->getAddKeySql($table, $keys);
 186                  }
 187  
 188                  // Unset this field so that what we have left are fields that need to be removed.
 189                  unset($oldLookup[$name]);
 190              }
 191              else
 192              {
 193                  // This is a new key.
 194                  $alters[] = $this->getAddKeySql($table, $keys);
 195              }
 196          }
 197  
 198          // Any keys left are orphans.
 199          foreach ($oldLookup as $name => $keys)
 200          {
 201              if (strtoupper($name) === 'PRIMARY')
 202              {
 203                  $alters[] = $this->getDropPrimaryKeySql($table);
 204              }
 205              else
 206              {
 207                  $alters[] = $this->getDropKeySql($table, $name);
 208              }
 209          }
 210  
 211          return $alters;
 212      }
 213  
 214      /**
 215       * Get the syntax to alter a column.
 216       *
 217       * @param   string             $table  The name of the database table to alter.
 218       * @param   \SimpleXMLElement  $field  The XML definition for the field.
 219       *
 220       * @return  string
 221       *
 222       * @since   1.0
 223       */
 224  	protected function getChangeColumnSql($table, \SimpleXMLElement $field)
 225      {
 226          return 'ALTER TABLE ' . $this->db->quoteName($table) . ' CHANGE COLUMN ' . $this->db->quoteName((string) $field['Field']) . ' '
 227              . $this->getColumnSql($field);
 228      }
 229  
 230      /**
 231       * Get the SQL syntax for a single column that would be included in a table create or alter statement.
 232       *
 233       * @param   \SimpleXMLElement  $field  The XML field definition.
 234       *
 235       * @return  string
 236       *
 237       * @since   1.0
 238       */
 239  	protected function getColumnSql(\SimpleXMLElement $field)
 240      {
 241          // Initialise variables.
 242          // TODO Incorporate into parent class and use $this.
 243          $blobs = ['text', 'smalltext', 'mediumtext', 'largetext'];
 244  
 245          $fName    = (string) $field['Field'];
 246          $fType    = (string) $field['Type'];
 247          $fNull    = (string) $field['Null'];
 248          $fDefault = isset($field['Default']) ? (string) $field['Default'] : null;
 249          $fExtra   = (string) $field['Extra'];
 250  
 251          $sql = $this->db->quoteName($fName) . ' ' . $fType;
 252  
 253          if ($fNull === 'NO')
 254          {
 255              if ($fDefault === null || \in_array($fType, $blobs, true))
 256              {
 257                  $sql .= ' NOT NULL';
 258              }
 259              else
 260              {
 261                  // TODO Don't quote numeric values.
 262                  if (stristr($fDefault, 'CURRENT') !== false)
 263                  {
 264                      $sql .= ' NOT NULL DEFAULT CURRENT_TIMESTAMP()';
 265                  }
 266                  else
 267                  {
 268                      $sql .= ' NOT NULL DEFAULT ' . $this->db->quote($fDefault);
 269                  }
 270              }
 271          }
 272          else
 273          {
 274              if ($fDefault === null)
 275              {
 276                  $sql .= ' DEFAULT NULL';
 277              }
 278              else
 279              {
 280                  // TODO Don't quote numeric values.
 281                  $sql .= ' DEFAULT ' . $this->db->quote($fDefault);
 282              }
 283          }
 284  
 285          if ($fExtra)
 286          {
 287              $sql .= ' ' . strtoupper($fExtra);
 288          }
 289  
 290          return $sql;
 291      }
 292  
 293      /**
 294       * Get the SQL syntax to drop a key.
 295       *
 296       * @param   string  $table  The table name.
 297       * @param   string  $name   The name of the key to drop.
 298       *
 299       * @return  string
 300       *
 301       * @since   1.0
 302       */
 303  	protected function getDropKeySql($table, $name)
 304      {
 305          return 'ALTER TABLE ' . $this->db->quoteName($table) . ' DROP KEY ' . $this->db->quoteName($name);
 306      }
 307  
 308      /**
 309       * Get the SQL syntax to drop a key.
 310       *
 311       * @param   string  $table  The table name.
 312       *
 313       * @return  string
 314       *
 315       * @since   1.0
 316       */
 317  	protected function getDropPrimaryKeySql($table)
 318      {
 319          return 'ALTER TABLE ' . $this->db->quoteName($table) . ' DROP PRIMARY KEY';
 320      }
 321  
 322      /**
 323       * Get the details list of keys for a table.
 324       *
 325       * @param   array  $keys  An array of objects that comprise the keys for the table.
 326       *
 327       * @return  array  The lookup array. array({key name} => array(object, ...))
 328       *
 329       * @since   1.0
 330       * @throws  \Exception
 331       */
 332  	protected function getKeyLookup($keys)
 333      {
 334          // First pass, create a lookup of the keys.
 335          $lookup = [];
 336  
 337          foreach ($keys as $key)
 338          {
 339              if ($key instanceof \SimpleXMLElement)
 340              {
 341                  $kName = (string) $key['Key_name'];
 342              }
 343              else
 344              {
 345                  $kName = $key->Key_name;
 346              }
 347  
 348              if (empty($lookup[$kName]))
 349              {
 350                  $lookup[$kName] = [];
 351              }
 352  
 353              $lookup[$kName][] = $key;
 354          }
 355  
 356          return $lookup;
 357      }
 358  
 359      /**
 360       * Get the SQL syntax for a key.
 361       *
 362       * @param   array  $columns  An array of SimpleXMLElement objects comprising the key.
 363       *
 364       * @return  string
 365       *
 366       * @since   1.0
 367       */
 368  	protected function getKeySql($columns)
 369      {
 370          $kNonUnique = (string) $columns[0]['Non_unique'];
 371          $kName      = (string) $columns[0]['Key_name'];
 372          $prefix     = '';
 373  
 374          if ($kName === 'PRIMARY')
 375          {
 376              $prefix = 'PRIMARY ';
 377          }
 378          elseif ($kNonUnique == 0)
 379          {
 380              $prefix = 'UNIQUE ';
 381          }
 382  
 383          $kColumns = [];
 384  
 385          foreach ($columns as $column)
 386          {
 387              $kLength = '';
 388  
 389              if (!empty($column['Sub_part']))
 390              {
 391                  $kLength = '(' . $column['Sub_part'] . ')';
 392              }
 393  
 394              $kColumns[] = $this->db->quoteName((string) $column['Column_name']) . $kLength;
 395          }
 396  
 397          return $prefix . 'KEY ' . ($kName !== 'PRIMARY' ? $this->db->quoteName($kName) : '') . ' (' . implode(',', $kColumns) . ')';
 398      }
 399  
 400      /**
 401       * Get the SQL syntax to add a table.
 402       *
 403       * @param   \SimpleXMLElement  $table  The table information.
 404       *
 405       * @return  string
 406       *
 407       * @since   2.0.0
 408       * @throws  \RuntimeException
 409       */
 410  	protected function xmlToCreate(\SimpleXMLElement $table)
 411      {
 412          $existingTables = $this->db->getTableList();
 413          $tableName      = (string) $table['name'];
 414  
 415          if (\in_array($tableName, $existingTables))
 416          {
 417              throw new \RuntimeException('The table you are trying to create already exists');
 418          }
 419  
 420          $createTableStatement = 'CREATE TABLE ' . $this->db->quoteName($tableName) . ' (';
 421  
 422          foreach ($table->xpath('field') as $field)
 423          {
 424              $createTableStatement .= $this->getColumnSql($field) . ', ';
 425          }
 426  
 427          $newLookup = $this->getKeyLookup($table->xpath('key'));
 428  
 429          foreach ($newLookup as $key)
 430          {
 431              $createTableStatement .= $this->getKeySql($key) . ', ';
 432          }
 433  
 434          $createTableStatement = rtrim($createTableStatement, ', ');
 435  
 436          $createTableStatement .= ')';
 437  
 438          return $createTableStatement;
 439      }
 440  }