[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\Pgsql;
  10  
  11  use Joomla\Database\DatabaseImporter;
  12  
  13  /**
  14   * PDO PostgreSQL Database Importer.
  15   *
  16   * @since  1.5.0
  17   */
  18  class PgsqlImporter 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.5.0
  26       * @throws  \RuntimeException if an error is encountered.
  27       */
  28  	public function check()
  29      {
  30          // Check if the db connector has been set.
  31          if (!($this->db instanceof PgsqlDriver))
  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 an index.
  47       *
  48       * @param   \SimpleXMLElement  $field  The XML index definition.
  49       *
  50       * @return  string
  51       *
  52       * @since   1.0
  53       */
  54  	protected function getAddIndexSql(\SimpleXMLElement $field)
  55      {
  56          return (string) $field['Query'];
  57      }
  58  
  59      /**
  60       * Get alters for table if there is a difference.
  61       *
  62       * @param   \SimpleXMLElement  $structure  The XML structure of the table.
  63       *
  64       * @return  array
  65       *
  66       * @since   1.0
  67       */
  68  	protected function getAlterTableSql(\SimpleXMLElement $structure)
  69      {
  70          $table       = $this->getRealTableName($structure['name']);
  71          $oldFields   = $this->db->getTableColumns($table);
  72          $oldKeys     = $this->db->getTableKeys($table);
  73          $oldSequence = $this->db->getTableSequences($table);
  74          $alters      = [];
  75  
  76          // Get the fields and keys from the XML that we are aiming for.
  77          $newFields   = $structure->xpath('field');
  78          $newKeys     = $structure->xpath('key');
  79          $newSequence = $structure->xpath('sequence');
  80  
  81          /*
  82           * Sequence section
  83           */
  84  
  85          $oldSeq          = $this->getSeqLookup($oldSequence);
  86          $newSequenceLook = $this->getSeqLookup($newSequence);
  87  
  88          foreach ($newSequenceLook as $kSeqName => $vSeq)
  89          {
  90              if (isset($oldSeq[$kSeqName]))
  91              {
  92                  // The field exists, check it's the same.
  93                  $column = $oldSeq[$kSeqName][0];
  94  
  95                  // Test whether there is a change.
  96                  $change = ((string) $vSeq[0]['Type'] !== $column->Type)
  97                      || ((string) $vSeq[0]['Start_Value'] !== $column->Start_Value)
  98                      || ((string) $vSeq[0]['Min_Value'] !== $column->Min_Value)
  99                      || ((string) $vSeq[0]['Max_Value'] !== $column->Max_Value)
 100                      || ((string) $vSeq[0]['Increment'] !== $column->Increment)
 101                      || ((string) $vSeq[0]['Cycle_option'] !== $column->Cycle_option)
 102                      || ((string) $vSeq[0]['Table'] !== $column->Table)
 103                      || ((string) $vSeq[0]['Column'] !== $column->Column)
 104                      || ((string) $vSeq[0]['Schema'] !== $column->Schema)
 105                      || ((string) $vSeq[0]['Name'] !== $column->Name);
 106  
 107                  if ($change)
 108                  {
 109                      $alters[] = $this->getChangeSequenceSql($kSeqName, $vSeq);
 110                      $alters[] = $this->getSetvalSequenceSql($kSeqName, $vSeq);
 111                  }
 112  
 113                  // Unset this field so that what we have left are fields that need to be removed.
 114                  unset($oldSeq[$kSeqName]);
 115              }
 116              else
 117              {
 118                  // The sequence is new
 119                  $alters[] = $this->getAddSequenceSql($newSequenceLook[$kSeqName][0]);
 120                  $alters[] = $this->getSetvalSequenceSql($newSequenceLook[$kSeqName][0]);
 121              }
 122          }
 123  
 124          // Any sequences left are orphans
 125          foreach ($oldSeq as $name => $column)
 126          {
 127              // Delete the sequence.
 128              $alters[] = $this->getDropSequenceSql($name);
 129          }
 130  
 131          /*
 132           * Field section
 133           */
 134  
 135          // Loop through each field in the new structure.
 136          foreach ($newFields as $field)
 137          {
 138              $fName = (string) $field['Field'];
 139  
 140              if (isset($oldFields[$fName]))
 141              {
 142                  // The field exists, check it's the same.
 143                  $column = $oldFields[$fName];
 144  
 145                  // Test whether there is a change.
 146                  $change = ((string) $field['Type'] !== $column->Type) || ((string) $field['Null'] !== $column->Null)
 147                      || ((string) $field['Default'] !== $column->Default);
 148  
 149                  if ($change)
 150                  {
 151                      $alters[] = $this->getChangeColumnSql($table, $field);
 152                  }
 153  
 154                  // Unset this field so that what we have left are fields that need to be removed.
 155                  unset($oldFields[$fName]);
 156              }
 157              else
 158              {
 159                  // The field is new.
 160                  $alters[] = $this->getAddColumnSql($table, $field);
 161              }
 162          }
 163  
 164          // Any columns left are orphans
 165          foreach ($oldFields as $name => $column)
 166          {
 167              // Delete the column.
 168              $alters[] = $this->getDropColumnSql($table, $name);
 169          }
 170  
 171          /*
 172           * Index section
 173           */
 174  
 175          // Get the lookups for the old and new keys
 176          $oldLookup = $this->getKeyLookup($oldKeys);
 177          $newLookup = $this->getKeyLookup($newKeys);
 178  
 179          // Loop through each key in the new structure.
 180          foreach ($newLookup as $name => $keys)
 181          {
 182              // Check if there are keys on this field in the existing table.
 183              if (isset($oldLookup[$name]))
 184              {
 185                  $same     = true;
 186                  $newCount = \count($newLookup[$name]);
 187                  $oldCount = \count($oldLookup[$name]);
 188  
 189                  // There is a key on this field in the old and new tables. Are they the same?
 190                  if ($newCount === $oldCount)
 191                  {
 192                      for ($i = 0; $i < $newCount; $i++)
 193                      {
 194                          // Check only query field -> different query means different index
 195                          $same = ((string) $newLookup[$name][$i]['Query'] === $oldLookup[$name][$i]->Query);
 196  
 197                          if (!$same)
 198                          {
 199                              // Break out of the loop. No need to check further.
 200                              break;
 201                          }
 202                      }
 203                  }
 204                  else
 205                  {
 206                      // Count is different, just drop and add.
 207                      $same = false;
 208                  }
 209  
 210                  if (!$same)
 211                  {
 212                      $alters[] = $this->getDropIndexSql($name);
 213                      $alters[] = (string) $newLookup[$name][0]['Query'];
 214                  }
 215  
 216                  // Unset this field so that what we have left are fields that need to be removed.
 217                  unset($oldLookup[$name]);
 218              }
 219              else
 220              {
 221                  // This is a new key.
 222                  $alters[] = (string) $newLookup[$name][0]['Query'];
 223              }
 224          }
 225  
 226          // Any keys left are orphans.
 227          foreach ($oldLookup as $name => $keys)
 228          {
 229              if ($oldLookup[$name][0]->is_primary === 'TRUE')
 230              {
 231                  $alters[] = $this->getDropPrimaryKeySql($table, $oldLookup[$name][0]->Index);
 232              }
 233              else
 234              {
 235                  $alters[] = $this->getDropIndexSql($name);
 236              }
 237          }
 238  
 239          return $alters;
 240      }
 241  
 242      /**
 243       * Get the SQL syntax to drop a sequence.
 244       *
 245       * @param   string  $name  The name of the sequence to drop.
 246       *
 247       * @return  string
 248       *
 249       * @since   1.0
 250       */
 251  	protected function getDropSequenceSql($name)
 252      {
 253          return 'DROP SEQUENCE ' . $this->db->quoteName($name);
 254      }
 255  
 256      /**
 257       * Get the syntax to add a sequence.
 258       *
 259       * @param   \SimpleXMLElement  $field  The XML definition for the sequence.
 260       *
 261       * @return  string
 262       *
 263       * @since   1.0
 264       */
 265  	protected function getAddSequenceSql(\SimpleXMLElement $field)
 266      {
 267          $sql = 'CREATE SEQUENCE IF NOT EXISTS ' . (string) $field['Name']
 268              . ' INCREMENT BY ' . (string) $field['Increment'] . ' MINVALUE ' . $field['Min_Value']
 269              . ' MAXVALUE ' . (string) $field['Max_Value'] . ' START ' . (string) $field['Start_Value']
 270              . (((string) $field['Cycle_option'] === 'NO') ? ' NO' : '') . ' CYCLE'
 271              . ' OWNED BY ' . $this->db->quoteName((string) $field['Schema'] . '.' . (string) $field['Table'] . '.' . (string) $field['Column']);
 272  
 273          return $sql;
 274      }
 275  
 276      /**
 277       * Get the syntax to alter a sequence.
 278       *
 279       * @param   \SimpleXMLElement  $field  The XML definition for the sequence.
 280       *
 281       * @return  string
 282       *
 283       * @since   1.0
 284       */
 285  	protected function getChangeSequenceSql(\SimpleXMLElement $field)
 286      {
 287          $sql = 'ALTER SEQUENCE ' . (string) $field['Name']
 288              . ' INCREMENT BY ' . (string) $field['Increment'] . ' MINVALUE ' . (string) $field['Min_Value']
 289              . ' MAXVALUE ' . (string) $field['Max_Value'] . ' START ' . (string) $field['Start_Value']
 290              . ' OWNED BY ' . $this->db->quoteName((string) $field['Schema'] . '.' . (string) $field['Table'] . '.' . (string) $field['Column']);
 291  
 292          return $sql;
 293      }
 294  
 295      /**
 296       * Get the syntax to setval a sequence.
 297       *
 298       * @param   \SimpleXMLElement  $field  The XML definition for the sequence.
 299       *
 300       * @return  string
 301       *
 302       * @since   2.0.0
 303       */
 304  	protected function getSetvalSequenceSql($field)
 305      {
 306          $is_called = $field['Is_called'] == 't' || $field['Is_called'] == '1' ? 'TRUE' : 'FALSE';
 307  
 308          return 'SELECT setval(\'' . (string) $field['Name'] . '\', ' . (string) $field['Last_Value'] . ', ' . $is_called . ')';
 309      }
 310  
 311      /**
 312       * Get the syntax to alter a column.
 313       *
 314       * @param   string             $table  The name of the database table to alter.
 315       * @param   \SimpleXMLElement  $field  The XML definition for the field.
 316       *
 317       * @return  string
 318       *
 319       * @since   1.0
 320       */
 321  	protected function getChangeColumnSql($table, \SimpleXMLElement $field)
 322      {
 323          return 'ALTER TABLE ' . $this->db->quoteName($table) . ' ALTER COLUMN ' . $this->db->quoteName((string) $field['Field']) . ' '
 324              . $this->getAlterColumnSql($table, $field);
 325      }
 326  
 327      /**
 328       * Get the SQL syntax for a single column that would be included in a table create statement.
 329       *
 330       * @param   string             $table  The name of the database table to alter.
 331       * @param   \SimpleXMLElement  $field  The XML field definition.
 332       *
 333       * @return  string
 334       *
 335       * @since   1.0
 336       */
 337  	protected function getAlterColumnSql($table, \SimpleXMLElement $field)
 338      {
 339          // TODO Incorporate into parent class and use $this.
 340          $blobs = ['text', 'smalltext', 'mediumtext', 'largetext'];
 341  
 342          $fName = (string) $field['Field'];
 343          $fType = (string) $field['Type'];
 344          $fNull = (string) $field['Null'];
 345  
 346          $fDefault = (isset($field['Default']) && $field['Default'] != 'NULL') ?
 347              preg_match('/^[0-9]$/', $field['Default']) ? $field['Default'] : $this->db->quote((string) $field['Default'])
 348              : null;
 349  
 350          $sql = ' TYPE ' . $fType;
 351  
 352          if ($fNull === 'NO')
 353          {
 354              if ($fDefault === null || \in_array($fType, $blobs, true))
 355              {
 356                  $sql .= ",\nALTER COLUMN " . $this->db->quoteName($fName) . ' SET NOT NULL'
 357                      . ",\nALTER COLUMN " . $this->db->quoteName($fName) . ' DROP DEFAULT';
 358              }
 359              else
 360              {
 361                  $sql .= ",\nALTER COLUMN " . $this->db->quoteName($fName) . ' SET NOT NULL'
 362                      . ",\nALTER COLUMN " . $this->db->quoteName($fName) . ' SET DEFAULT ' . $fDefault;
 363              }
 364          }
 365          else
 366          {
 367              if ($fDefault !== null)
 368              {
 369                  $sql .= ",\nALTER COLUMN " . $this->db->quoteName($fName) . ' DROP NOT NULL'
 370                      . ",\nALTER COLUMN " . $this->db->quoteName($fName) . ' SET DEFAULT ' . $fDefault;
 371              }
 372          }
 373  
 374          // Sequence was created in other function, here is associated a default value but not yet owner
 375          if (strpos($fDefault, 'nextval') !== false)
 376          {
 377              $sequence = $table . '_' . $fName . '_seq';
 378              $owner    = $table . '.' . $fName;
 379  
 380              $sql .= ";\nALTER SEQUENCE " . $this->db->quoteName($sequence) . ' OWNED BY ' . $this->db->quoteName($owner);
 381          }
 382  
 383          return $sql;
 384      }
 385  
 386      /**
 387       * Get the SQL syntax for a single column that would be included in a table create statement.
 388       *
 389       * @param   \SimpleXMLElement  $field  The XML field definition.
 390       *
 391       * @return  string
 392       *
 393       * @since   1.0
 394       */
 395  	protected function getColumnSql(\SimpleXMLElement $field)
 396      {
 397          $fName = (string) $field['Field'];
 398          $fType = (string) $field['Type'];
 399          $fNull = (string) $field['Null'];
 400  
 401          if (strpos($field['Default'], '::') != false)
 402          {
 403              $fDefault = strstr($field['Default'], '::', true);
 404          }
 405          else
 406          {
 407              $fDefault = isset($field['Default']) && strlen($field['Default']) > 0
 408                  ? preg_match('/^[0-9]$/', $field['Default']) ? $field['Default'] : $this->db->quote((string) $field['Default'])
 409                  : null;
 410          }
 411  
 412          // Note, nextval() as default value means that type field is serial.
 413          if (strpos($fDefault, 'nextval') !== false)
 414          {
 415              $sql = $this->db->quoteName($fName) . ' SERIAL';
 416          }
 417          else
 418          {
 419              $sql = $this->db->quoteName($fName) . ' ' . $fType;
 420  
 421              if ($fNull == 'NO')
 422              {
 423                  if ($fDefault === null)
 424                  {
 425                      $sql .= ' NOT NULL';
 426                  }
 427                  else
 428                  {
 429                      $sql .= ' NOT NULL DEFAULT ' . $fDefault;
 430                  }
 431              }
 432              else
 433              {
 434                  if ($fDefault !== null)
 435                  {
 436                      $sql .= ' DEFAULT ' . $fDefault;
 437                  }
 438              }
 439          }
 440  
 441          return $sql;
 442      }
 443  
 444      /**
 445       * Get the SQL syntax to drop an index.
 446       *
 447       * @param   string  $name  The name of the key to drop.
 448       *
 449       * @return  string
 450       *
 451       * @since   1.0
 452       */
 453  	protected function getDropIndexSql($name)
 454      {
 455          return 'DROP INDEX ' . $this->db->quoteName($name);
 456      }
 457  
 458      /**
 459       * Get the SQL syntax to drop a key.
 460       *
 461       * @param   string  $table  The table name.
 462       * @param   string  $name   The constraint name.
 463       *
 464       * @return  string
 465       *
 466       * @since   1.0
 467       */
 468  	protected function getDropPrimaryKeySql($table, $name)
 469      {
 470          return 'ALTER TABLE ONLY ' . $this->db->quoteName($table) . ' DROP CONSTRAINT ' . $this->db->quoteName($name);
 471      }
 472  
 473      /**
 474       * Get the details list of keys for a table.
 475       *
 476       * @param   array  $keys  An array of objects that comprise the keys for the table.
 477       *
 478       * @return  array  The lookup array. array({key name} => array(object, ...))
 479       *
 480       * @since   1.2.0
 481       */
 482  	protected function getKeyLookup($keys)
 483      {
 484          // First pass, create a lookup of the keys.
 485          $lookup = [];
 486  
 487          foreach ($keys as $key)
 488          {
 489              if ($key instanceof \SimpleXMLElement)
 490              {
 491                  $kName = (string) $key['Index'];
 492              }
 493              else
 494              {
 495                  $kName = $key->Index;
 496              }
 497  
 498              if (empty($lookup[$kName]))
 499              {
 500                  $lookup[$kName] = [];
 501              }
 502  
 503              $lookup[$kName][] = $key;
 504          }
 505  
 506          return $lookup;
 507      }
 508  
 509      /**
 510       * Get the SQL syntax to add a unique constraint for a table key.
 511       *
 512       * @param   string  $table  The table name.
 513       * @param   array   $key    The key.
 514       *
 515       * @return  string
 516       *
 517       * @since   2.0.0
 518       */
 519  	protected function getAddUniqueSql($table, $key)
 520      {
 521          if ($key instanceof \SimpleXMLElement)
 522          {
 523              $kName = (string) $key['Key_name'];
 524              $kIndex = (string) $key['Index'];
 525          }
 526          else
 527          {
 528              $kName = $key->Key_name;
 529              $kIndex = $key->Index;
 530          }
 531  
 532          $unique = $kIndex . ' UNIQUE (' . $kName . ')';
 533  
 534          return 'ALTER TABLE ' . $this->db->quoteName($table) . ' ADD CONSTRAINT ' . $unique;
 535      }
 536  
 537      /**
 538       * Get the details list of sequences for a table.
 539       *
 540       * @param   array  $sequences  An array of objects that comprise the sequences for the table.
 541       *
 542       * @return  array  The lookup array. array({key name} => array(object, ...))
 543       *
 544       * @since   1.0
 545       */
 546  	protected function getSeqLookup($sequences)
 547      {
 548          // First pass, create a lookup of the keys.
 549          $lookup = [];
 550  
 551          foreach ($sequences as $seq)
 552          {
 553              if ($seq instanceof \SimpleXMLElement)
 554              {
 555                  $sName = (string) $seq['Name'];
 556              }
 557              else
 558              {
 559                  $sName = $seq->Name;
 560              }
 561  
 562              if (empty($lookup[$sName]))
 563              {
 564                  $lookup[$sName] = [];
 565              }
 566  
 567              $lookup[$sName][] = $seq;
 568          }
 569  
 570          return $lookup;
 571      }
 572  
 573      /**
 574       * Get the SQL syntax to add a table.
 575       *
 576       * @param   \SimpleXMLElement  $table  The table information.
 577       *
 578       * @return  string
 579       *
 580       * @since   2.0.0
 581       * @throws  \RuntimeException
 582       */
 583  	protected function xmlToCreate(\SimpleXMLElement $table)
 584      {
 585          $existingTables = $this->db->getTableList();
 586          $tableName = (string) $table['name'];
 587  
 588          if (in_array($tableName, $existingTables))
 589          {
 590              throw new \RuntimeException('The table you are trying to create already exists');
 591          }
 592  
 593          $createTableStatement = 'CREATE TABLE ' . $this->db->quoteName($tableName) . ' (';
 594  
 595          foreach ($table->xpath('field') as $field)
 596          {
 597              $createTableStatement .= $this->getColumnSql($field) . ', ';
 598          }
 599  
 600          $createTableStatement = rtrim($createTableStatement, ', ');
 601          $createTableStatement .= ');';
 602  
 603          foreach ($table->xpath('sequence') as $seq)
 604          {
 605              $createTableStatement .= $this->getAddSequenceSql($seq) . ';';
 606              $createTableStatement .= $this->getSetvalSequenceSql($seq) . ';';
 607          }
 608  
 609          foreach ($table->xpath('key') as $key)
 610          {
 611              if ((($key['is_primary'] == 'f') || ($key['is_primary'] == '')) && (($key['is_unique'] == 't') || ($key['is_unique'] == '1')))
 612              {
 613                  $createTableStatement .= $this->getAddUniqueSql($tableName, $key) . ';';
 614              }
 615              else
 616              {
 617                  $createTableStatement .= $this->getAddIndexSql($key) . ';';
 618              }
 619          }
 620  
 621          return $createTableStatement;
 622      }
 623  }