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