Search moodle.org's
Developer Documentation

See Release Notes

  • Bug fixes for general core bugs in 4.2.x will end 22 April 2024 (12 months).
  • Bug fixes for security issues in 4.2.x will end 7 October 2024 (18 months).
  • PHP version: minimum PHP 8.0.0 Note: minimum PHP version has increased since Moodle 4.1. PHP 8.1.x is supported too.

Differences Between: [Versions 310 and 402] [Versions 311 and 402] [Versions 39 and 402] [Versions 400 and 402] [Versions 401 and 402]

   1  <?php
   2  // This file is part of Moodle - http://moodle.org/
   3  //
   4  // Moodle is free software: you can redistribute it and/or modify
   5  // it under the terms of the GNU General Public License as published by
   6  // the Free Software Foundation, either version 3 of the License, or
   7  // (at your option) any later version.
   8  //
   9  // Moodle is distributed in the hope that it will be useful,
  10  // but WITHOUT ANY WARRANTY; without even the implied warranty of
  11  // MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  12  // GNU General Public License for more details.
  13  //
  14  // You should have received a copy of the GNU General Public License
  15  // along with Moodle.  If not, see <http://www.gnu.org/licenses/>.
  16  
  17  /**
  18   * This class represent the base generator class where all the needed functions to generate proper SQL are defined.
  19   *
  20   * The rest of classes will inherit, by default, the same logic.
  21   * Functions will be overridden as needed to generate correct SQL.
  22   *
  23   * @package    core_ddl
  24   * @copyright  1999 onwards Martin Dougiamas     http://dougiamas.com
  25   *             2001-3001 Eloy Lafuente (stronk7) http://contiento.com
  26   * @license    http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
  27   */
  28  
  29  defined('MOODLE_INTERNAL') || die();
  30  
  31  /**
  32   * Abstract sql generator class, base for all db specific implementations.
  33   *
  34   * @package    core_ddl
  35   * @copyright  1999 onwards Martin Dougiamas     http://dougiamas.com
  36   *             2001-3001 Eloy Lafuente (stronk7) http://contiento.com
  37   * @license    http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
  38   */
  39  abstract class sql_generator {
  40  
  41      // Please, avoid editing this defaults in this base class!
  42      // It could change the behaviour of the rest of generators
  43      // that, by default, inherit this configuration.
  44      // To change any of them, do it in extended classes instead.
  45  
  46      /** @var string Used to quote names. */
  47      public $quote_string = '"';
  48  
  49      /** @var string To be automatically added at the end of each statement. */
  50      public $statement_end = ';';
  51  
  52      /** @var bool To decide if we want to quote all the names or only the reserved ones. */
  53      public $quote_all = false;
  54  
  55      /** @var bool To create all the integers as NUMBER(x) (also called DECIMAL, NUMERIC...). */
  56      public $integer_to_number = false;
  57  
  58      /** @var bool To create all the floats as NUMBER(x) (also called DECIMAL, NUMERIC...). */
  59      public $float_to_number   = false;
  60  
  61      /** @var string Proper type for NUMBER(x) in this DB. */
  62      public $number_type = 'NUMERIC';
  63  
  64      /** @var string To define the default to set for NOT NULLs CHARs without default (null=do nothing).*/
  65      public $default_for_char = null;
  66  
  67      /** @var bool To specify if the generator must use some DEFAULT clause to drop defaults.*/
  68      public $drop_default_value_required = false;
  69  
  70      /** @var string The DEFAULT clause required to drop defaults.*/
  71      public $drop_default_value = '';
  72  
  73      /** @var bool To decide if the default clause of each field must go after the null clause.*/
  74      public $default_after_null = true;
  75  
  76      /** @var bool To force the generator if NULL clauses must be specified. It shouldn't be necessary.*/
  77      public $specify_nulls = false;
  78  
  79      /** @var string To force primary key names to one string (null=no force).*/
  80      public $primary_key_name = null;
  81  
  82      /** @var bool True if the generator builds primary keys.*/
  83      public $primary_keys = true;
  84  
  85      /** @var bool True if the generator builds unique keys.*/
  86      public $unique_keys = false;
  87  
  88      /** @var bool True if the generator builds foreign keys.*/
  89      public $foreign_keys = false;
  90  
  91      /** @var string Template to drop PKs. 'TABLENAME' and 'KEYNAME' will be replaced from this template.*/
  92      public $drop_primary_key = 'ALTER TABLE TABLENAME DROP CONSTRAINT KEYNAME';
  93  
  94      /** @var string Template to drop UKs. 'TABLENAME' and 'KEYNAME' will be replaced from this template.*/
  95      public $drop_unique_key = 'ALTER TABLE TABLENAME DROP CONSTRAINT KEYNAME';
  96  
  97      /** @var string Template to drop FKs. 'TABLENAME' and 'KEYNAME' will be replaced from this template.*/
  98      public $drop_foreign_key = 'ALTER TABLE TABLENAME DROP CONSTRAINT KEYNAME';
  99  
 100      /** @var bool True if the generator needs to add extra code to generate the sequence fields.*/
 101      public $sequence_extra_code = true;
 102  
 103      /** @var string The particular name for inline sequences in this generator.*/
 104      public $sequence_name = 'auto_increment';
 105  
 106      /** @var string|bool Different name for small (4byte) sequences or false if same.*/
 107      public $sequence_name_small = false;
 108  
 109      /**
 110       * @var bool To avoid outputting the rest of the field specs, leaving only the name and the sequence_name returned.
 111       * @see getFieldSQL()
 112       */
 113      public $sequence_only = false;
 114  
 115      /** @var bool True if the generator needs to add code for table comments.*/
 116      public $add_table_comments  = true;
 117  
 118      /** @var bool True if the generator needs to add the after clause for fields.*/
 119      public $add_after_clause = false;
 120  
 121      /**
 122       * @var bool True if the generator needs to prepend the prefix to all the key/index/sequence/trigger/check names.
 123       * @see $prefix
 124       */
 125      public $prefix_on_names = true;
 126  
 127      /** @var int Maximum length for key/index/sequence/trigger/check names (keep 30 for all!).*/
 128      public $names_max_length = 30;
 129  
 130      /** @var string Characters to be used as concatenation operator. If not defined, MySQL CONCAT function will be used.*/
 131      public $concat_character = '||';
 132  
 133      /** @var string SQL sentence to rename one table, both 'OLDNAME' and 'NEWNAME' keywords are dynamically replaced.*/
 134      public $rename_table_sql = 'ALTER TABLE OLDNAME RENAME TO NEWNAME';
 135  
 136      /** @var string SQL sentence to drop one table where the 'TABLENAME' keyword is dynamically replaced.*/
 137      public $drop_table_sql = 'DROP TABLE TABLENAME';
 138  
 139      /** @var string The SQL template to alter columns where the 'TABLENAME' and 'COLUMNSPECS' keywords are dynamically replaced.*/
 140      public $alter_column_sql = 'ALTER TABLE TABLENAME ALTER COLUMN COLUMNSPECS';
 141  
 142      /** @var bool The generator will skip the default clause on alter columns.*/
 143      public $alter_column_skip_default = false;
 144  
 145      /** @var bool The generator will skip the type clause on alter columns.*/
 146      public $alter_column_skip_type = false;
 147  
 148      /** @var bool The generator will skip the null/notnull clause on alter columns.*/
 149      public $alter_column_skip_notnull = false;
 150  
 151      /** @var string SQL sentence to rename one column where 'TABLENAME', 'OLDFIELDNAME' and 'NEWFIELDNAME' keywords are dynamically replaced.*/
 152      public $rename_column_sql = 'ALTER TABLE TABLENAME RENAME COLUMN OLDFIELDNAME TO NEWFIELDNAME';
 153  
 154      /** @var string SQL sentence to drop one index where 'TABLENAME', 'INDEXNAME' keywords are dynamically replaced.*/
 155      public $drop_index_sql = 'DROP INDEX INDEXNAME';
 156  
 157      /** @var string SQL sentence to rename one index where 'TABLENAME', 'OLDINDEXNAME' and 'NEWINDEXNAME' are dynamically replaced.*/
 158      public $rename_index_sql = 'ALTER INDEX OLDINDEXNAME RENAME TO NEWINDEXNAME';
 159  
 160      /** @var string SQL sentence to rename one key 'TABLENAME', 'OLDKEYNAME' and 'NEWKEYNAME' are dynamically replaced.*/
 161      public $rename_key_sql = 'ALTER TABLE TABLENAME CONSTRAINT OLDKEYNAME RENAME TO NEWKEYNAME';
 162  
 163      /** @var string The prefix to be used for all the DB objects.*/
 164      public $prefix;
 165  
 166      /** @var array List of reserved words (in order to quote them properly).*/
 167      public $reserved_words;
 168  
 169      /** @var moodle_database The moodle_database instance.*/
 170      public $mdb;
 171  
 172      /** @var Control existing temptables.*/
 173      protected $temptables;
 174  
 175      /**
 176       * Creates a new sql_generator.
 177       * @param moodle_database $mdb The moodle_database object instance.
 178       * @param moodle_temptables $temptables The optional moodle_temptables instance, null by default.
 179       */
 180      public function __construct($mdb, $temptables = null) {
 181          $this->prefix         = $mdb->get_prefix();
 182          $this->reserved_words = $this->getReservedWords();
 183          $this->mdb            = $mdb; // this creates circular reference - the other link must be unset when closing db
 184          $this->temptables     = $temptables;
 185      }
 186  
 187      /**
 188       * Releases all resources.
 189       */
 190      public function dispose() {
 191          $this->mdb = null;
 192      }
 193  
 194      /**
 195       * Given one string (or one array), ends it with $statement_end .
 196       *
 197       * @see $statement_end
 198       *
 199       * @param array|string $input SQL statement(s).
 200       * @return array|string
 201       */
 202      public function getEndedStatements($input) {
 203  
 204          if (is_array($input)) {
 205              foreach ($input as $key=>$content) {
 206                  $input[$key] = $this->getEndedStatements($content);
 207              }
 208              return $input;
 209          } else {
 210              $input = trim($input).$this->statement_end;
 211              return $input;
 212          }
 213      }
 214  
 215      /**
 216       * Given one xmldb_table, checks if it exists in DB (true/false).
 217       *
 218       * @param mixed $table The table to be searched (string name or xmldb_table instance).
 219       * @return boolean true/false
 220       */
 221      public function table_exists($table) {
 222          if (is_string($table)) {
 223              $tablename = $table;
 224          } else {
 225              // Calculate the name of the table
 226              $tablename = $table->getName();
 227          }
 228  
 229          if ($this->temptables->is_temptable($tablename)) {
 230              return true;
 231          }
 232  
 233          // Get all tables in moodle database.
 234          $tables = $this->mdb->get_tables();
 235          return isset($tables[$tablename]);
 236      }
 237  
 238      /**
 239       * This function will return the SQL code needed to create db tables and statements.
 240       * @see xmldb_structure
 241       *
 242       * @param xmldb_structure $xmldb_structure An xmldb_structure instance.
 243       * @return array
 244       */
 245      public function getCreateStructureSQL($xmldb_structure) {
 246          $results = array();
 247  
 248          if ($tables = $xmldb_structure->getTables()) {
 249              foreach ($tables as $table) {
 250                  $results = array_merge($results, $this->getCreateTableSQL($table));
 251              }
 252          }
 253  
 254          return $results;
 255      }
 256  
 257      /**
 258       * Given one xmldb_table, this returns it's correct name, depending of all the parameterization.
 259       * eg: This appends $prefix to the table name.
 260       *
 261       * @see $prefix
 262       *
 263       * @param xmldb_table $xmldb_table The table whose name we want.
 264       * @param boolean $quoted To specify if the name must be quoted (if reserved word, only!).
 265       * @return string The correct name of the table.
 266       */
 267      public function getTableName(xmldb_table $xmldb_table, $quoted=true) {
 268          // Get the name
 269          $tablename = $this->prefix.$xmldb_table->getName();
 270  
 271          // Apply quotes optionally
 272          if ($quoted) {
 273              $tablename = $this->getEncQuoted($tablename);
 274          }
 275  
 276          return $tablename;
 277      }
 278  
 279      /**
 280       * Given one correct xmldb_table, returns the SQL statements
 281       * to create it (inside one array).
 282       *
 283       * @param xmldb_table $xmldb_table An xmldb_table instance.
 284       * @return array An array of SQL statements, starting with the table creation SQL followed
 285       * by any of its comments, indexes and sequence creation SQL statements.
 286       */
 287      public function getCreateTableSQL($xmldb_table) {
 288          if ($error = $xmldb_table->validateDefinition()) {
 289              throw new coding_exception($error);
 290          }
 291  
 292          $results = array();  //Array where all the sentences will be stored
 293  
 294          // Table header
 295          $table = 'CREATE TABLE ' . $this->getTableName($xmldb_table) . ' (';
 296  
 297          if (!$xmldb_fields = $xmldb_table->getFields()) {
 298              return $results;
 299          }
 300  
 301          $sequencefield = null;
 302  
 303          // Add the fields, separated by commas
 304          foreach ($xmldb_fields as $xmldb_field) {
 305              if ($xmldb_field->getSequence()) {
 306                  $sequencefield = $xmldb_field->getName();
 307              }
 308              $table .= "\n    " . $this->getFieldSQL($xmldb_table, $xmldb_field);
 309              $table .= ',';
 310          }
 311          // Add the keys, separated by commas
 312          if ($xmldb_keys = $xmldb_table->getKeys()) {
 313              foreach ($xmldb_keys as $xmldb_key) {
 314                  if ($keytext = $this->getKeySQL($xmldb_table, $xmldb_key)) {
 315                      $table .= "\nCONSTRAINT " . $keytext . ',';
 316                  }
 317                  // If the key is XMLDB_KEY_FOREIGN_UNIQUE, create it as UNIQUE too
 318                  if ($xmldb_key->getType() == XMLDB_KEY_FOREIGN_UNIQUE) {
 319                      //Duplicate the key
 320                      $xmldb_key->setType(XMLDB_KEY_UNIQUE);
 321                      if ($keytext = $this->getKeySQL($xmldb_table, $xmldb_key)) {
 322                          $table .= "\nCONSTRAINT " . $keytext . ',';
 323                      }
 324                  }
 325                  // make sure sequence field is unique
 326                  if ($sequencefield and $xmldb_key->getType() == XMLDB_KEY_PRIMARY) {
 327                      $fields = $xmldb_key->getFields();
 328                      $field = reset($fields);
 329                      if ($sequencefield === $field) {
 330                          $sequencefield = null;
 331                      }
 332                  }
 333              }
 334          }
 335          // throw error if sequence field does not have unique key defined
 336          if ($sequencefield) {
 337              throw new ddl_exception('ddsequenceerror', $xmldb_table->getName());
 338          }
 339  
 340          // Table footer, trim the latest comma
 341          $table = trim($table,',');
 342          $table .= "\n)";
 343  
 344          // Add the CREATE TABLE to results
 345          $results[] = $table;
 346  
 347          // Add comments if specified and it exists
 348          if ($this->add_table_comments && $xmldb_table->getComment()) {
 349              $comment = $this->getCommentSQL($xmldb_table);
 350              // Add the COMMENT to results
 351              $results = array_merge($results, $comment);
 352          }
 353  
 354          // Add the indexes (each one, one statement)
 355          if ($xmldb_indexes = $xmldb_table->getIndexes()) {
 356              foreach ($xmldb_indexes as $xmldb_index) {
 357                  //tables do not exist yet, which means indexed can not exist yet
 358                  if ($indextext = $this->getCreateIndexSQL($xmldb_table, $xmldb_index)) {
 359                      $results = array_merge($results, $indextext);
 360                  }
 361              }
 362          }
 363  
 364          // Also, add the indexes needed from keys, based on configuration (each one, one statement)
 365          if ($xmldb_keys = $xmldb_table->getKeys()) {
 366              foreach ($xmldb_keys as $xmldb_key) {
 367                  // If we aren't creating the keys OR if the key is XMLDB_KEY_FOREIGN (not underlying index generated
 368                  // automatically by the RDBMS) create the underlying (created by us) index (if doesn't exists)
 369                  if (!$this->getKeySQL($xmldb_table, $xmldb_key) || $xmldb_key->getType() == XMLDB_KEY_FOREIGN) {
 370                      // Create the interim index
 371                      $index = new xmldb_index('anyname');
 372                      $index->setFields($xmldb_key->getFields());
 373                      //tables do not exist yet, which means indexed can not exist yet
 374                      $createindex = false; //By default
 375                      switch ($xmldb_key->getType()) {
 376                          case XMLDB_KEY_UNIQUE:
 377                          case XMLDB_KEY_FOREIGN_UNIQUE:
 378                              $index->setUnique(true);
 379                              $createindex = true;
 380                              break;
 381                          case XMLDB_KEY_FOREIGN:
 382                              $index->setUnique(false);
 383                              $createindex = true;
 384                              break;
 385                      }
 386                      if ($createindex) {
 387                          if ($indextext = $this->getCreateIndexSQL($xmldb_table, $index)) {
 388                              // Add the INDEX to the array
 389                              $results = array_merge($results, $indextext);
 390                          }
 391                      }
 392                  }
 393              }
 394          }
 395  
 396          // Add sequence extra code if needed
 397          if ($this->sequence_extra_code) {
 398              // Iterate over fields looking for sequences
 399              foreach ($xmldb_fields as $xmldb_field) {
 400                  if ($xmldb_field->getSequence()) {
 401                      // returns an array of statements needed to create one sequence
 402                      $sequence_sentences = $this->getCreateSequenceSQL($xmldb_table, $xmldb_field);
 403                      // Add the SEQUENCE to the array
 404                      $results = array_merge($results, $sequence_sentences);
 405                  }
 406              }
 407          }
 408  
 409          return $results;
 410      }
 411  
 412      /**
 413       * Given one correct xmldb_index, returns the SQL statements
 414       * needed to create it (in array).
 415       *
 416       * @param xmldb_table $xmldb_table The xmldb_table instance to create the index on.
 417       * @param xmldb_index $xmldb_index The xmldb_index to create.
 418       * @return array An array of SQL statements to create the index.
 419       * @throws coding_exception Thrown if the xmldb_index does not validate with the xmldb_table.
 420       */
 421      public function getCreateIndexSQL($xmldb_table, $xmldb_index) {
 422          if ($error = $xmldb_index->validateDefinition($xmldb_table)) {
 423              throw new coding_exception($error);
 424          }
 425  
 426          $unique = '';
 427          $suffix = 'ix';
 428          if ($xmldb_index->getUnique()) {
 429              $unique = ' UNIQUE';
 430              $suffix = 'uix';
 431          }
 432  
 433          $index = 'CREATE' . $unique . ' INDEX ';
 434          $index .= $this->getNameForObject($xmldb_table->getName(), implode(', ', $xmldb_index->getFields()), $suffix);
 435          $index .= ' ON ' . $this->getTableName($xmldb_table);
 436          $index .= ' (' . implode(', ', $this->getEncQuoted($xmldb_index->getFields())) . ')';
 437  
 438          return array($index);
 439      }
 440  
 441      /**
 442       * Given one correct xmldb_field, returns the complete SQL line to create it.
 443       *
 444       * @param xmldb_table $xmldb_table The table related to $xmldb_field.
 445       * @param xmldb_field $xmldb_field The instance of xmldb_field to create the SQL from.
 446       * @param string $skip_type_clause The type clause on alter columns, NULL by default.
 447       * @param string $skip_default_clause The default clause on alter columns, NULL by default.
 448       * @param string $skip_notnull_clause The null/notnull clause on alter columns, NULL by default.
 449       * @param string $specify_nulls_clause To force a specific null clause, NULL by default.
 450       * @param bool $specify_field_name Flag to specify fieldname in return.
 451       * @return string The field generating SQL statement.
 452       * @throws coding_exception Thrown when xmldb_field doesn't validate with the xmldb_table.
 453       */
 454      public function getFieldSQL($xmldb_table, $xmldb_field, $skip_type_clause = NULL, $skip_default_clause = NULL, $skip_notnull_clause = NULL, $specify_nulls_clause = NULL, $specify_field_name = true)  {
 455          if ($error = $xmldb_field->validateDefinition($xmldb_table)) {
 456              throw new coding_exception($error);
 457          }
 458  
 459          $skip_type_clause = is_null($skip_type_clause) ? $this->alter_column_skip_type : $skip_type_clause;
 460          $skip_default_clause = is_null($skip_default_clause) ? $this->alter_column_skip_default : $skip_default_clause;
 461          $skip_notnull_clause = is_null($skip_notnull_clause) ? $this->alter_column_skip_notnull : $skip_notnull_clause;
 462          $specify_nulls_clause = is_null($specify_nulls_clause) ? $this->specify_nulls : $specify_nulls_clause;
 463  
 464          // First of all, convert integers to numbers if defined
 465          if ($this->integer_to_number) {
 466              if ($xmldb_field->getType() == XMLDB_TYPE_INTEGER) {
 467                  $xmldb_field->setType(XMLDB_TYPE_NUMBER);
 468              }
 469          }
 470          // Same for floats
 471          if ($this->float_to_number) {
 472              if ($xmldb_field->getType() == XMLDB_TYPE_FLOAT) {
 473                  $xmldb_field->setType(XMLDB_TYPE_NUMBER);
 474              }
 475          }
 476  
 477          $field = ''; // Let's accumulate the whole expression based on params and settings
 478          // The name
 479          if ($specify_field_name) {
 480              $field .= $this->getEncQuoted($xmldb_field->getName());
 481          }
 482          // The type and length only if we don't want to skip it
 483          if (!$skip_type_clause) {
 484              // The type and length
 485              $field .= ' ' . $this->getTypeSQL($xmldb_field->getType(), $xmldb_field->getLength(), $xmldb_field->getDecimals());
 486          }
 487          // note: unsigned is not supported any more since moodle 2.3, all numbers are signed
 488          // Calculate the not null clause
 489          $notnull = '';
 490          // Only if we don't want to skip it
 491          if (!$skip_notnull_clause) {
 492              if ($xmldb_field->getNotNull()) {
 493                  $notnull = ' NOT NULL';
 494              } else {
 495                  if ($specify_nulls_clause) {
 496                      $notnull = ' NULL';
 497                  }
 498              }
 499          }
 500          // Calculate the default clause
 501          $default_clause = '';
 502          if (!$skip_default_clause) { //Only if we don't want to skip it
 503              $default_clause = $this->getDefaultClause($xmldb_field);
 504          }
 505          // Based on default_after_null, set both clauses properly
 506          if ($this->default_after_null) {
 507              $field .= $notnull . $default_clause;
 508          } else {
 509              $field .= $default_clause . $notnull;
 510          }
 511          // The sequence
 512          if ($xmldb_field->getSequence()) {
 513              if($xmldb_field->getLength()<=9 && $this->sequence_name_small) {
 514                  $sequencename=$this->sequence_name_small;
 515              } else {
 516                  $sequencename=$this->sequence_name;
 517              }
 518              $field .= ' ' . $sequencename;
 519              if ($this->sequence_only) {
 520                  // We only want the field name and sequence name to be printed
 521                  // so, calculate it and return
 522                  $sql = $this->getEncQuoted($xmldb_field->getName()) . ' ' . $sequencename;
 523                  return $sql;
 524              }
 525          }
 526          return $field;
 527      }
 528  
 529      /**
 530       * Given one correct xmldb_key, returns its specs.
 531       *
 532       * @param xmldb_table $xmldb_table The table related to $xmldb_key.
 533       * @param xmldb_key $xmldb_key The xmldb_key's specifications requested.
 534       * @return string SQL statement about the xmldb_key.
 535       */
 536      public function getKeySQL($xmldb_table, $xmldb_key) {
 537  
 538          $key = '';
 539  
 540          switch ($xmldb_key->getType()) {
 541              case XMLDB_KEY_PRIMARY:
 542                  if ($this->primary_keys) {
 543                      if ($this->primary_key_name !== null) {
 544                          $key = $this->getEncQuoted($this->primary_key_name);
 545                      } else {
 546                          $key = $this->getNameForObject($xmldb_table->getName(), implode(', ', $xmldb_key->getFields()), 'pk');
 547                      }
 548                      $key .= ' PRIMARY KEY (' . implode(', ', $this->getEncQuoted($xmldb_key->getFields())) . ')';
 549                  }
 550                  break;
 551              case XMLDB_KEY_UNIQUE:
 552                  if ($this->unique_keys) {
 553                      $key = $this->getNameForObject($xmldb_table->getName(), implode(', ', $xmldb_key->getFields()), 'uk');
 554                      $key .= ' UNIQUE (' . implode(', ', $this->getEncQuoted($xmldb_key->getFields())) . ')';
 555                  }
 556                  break;
 557              case XMLDB_KEY_FOREIGN:
 558              case XMLDB_KEY_FOREIGN_UNIQUE:
 559                  if ($this->foreign_keys) {
 560                      $key = $this->getNameForObject($xmldb_table->getName(), implode(', ', $xmldb_key->getFields()), 'fk');
 561                      $key .= ' FOREIGN KEY (' . implode(', ', $this->getEncQuoted($xmldb_key->getFields())) . ')';
 562                      $key .= ' REFERENCES ' . $this->getEncQuoted($this->prefix . $xmldb_key->getRefTable());
 563                      $key .= ' (' . implode(', ', $this->getEncQuoted($xmldb_key->getRefFields())) . ')';
 564                  }
 565                  break;
 566          }
 567  
 568          return $key;
 569      }
 570  
 571      /**
 572       * Give one xmldb_field, returns the correct "default value" for the current configuration
 573       *
 574       * @param xmldb_field $xmldb_field The field.
 575       * @return The default value of the field.
 576       */
 577      public function getDefaultValue($xmldb_field) {
 578  
 579          $default = null;
 580  
 581          if ($xmldb_field->getDefault() !== NULL) {
 582              if ($xmldb_field->getType() == XMLDB_TYPE_CHAR ||
 583                  $xmldb_field->getType() == XMLDB_TYPE_TEXT) {
 584                      if ($xmldb_field->getDefault() === '') { // If passing empty default, use the $default_for_char one instead
 585                          $default = "'" . $this->default_for_char . "'";
 586                      } else {
 587                          $default = "'" . $this->addslashes($xmldb_field->getDefault()) . "'";
 588                      }
 589              } else {
 590                  $default = $xmldb_field->getDefault();
 591              }
 592          } else {
 593              // We force default '' for not null char columns without proper default
 594              // some day this should be out!
 595              if ($this->default_for_char !== NULL &&
 596                  $xmldb_field->getType() == XMLDB_TYPE_CHAR &&
 597                  $xmldb_field->getNotNull()) {
 598                  $default = "'" . $this->default_for_char . "'";
 599              } else {
 600                  // If the DB requires to explicity define some clause to drop one default, do it here
 601                  // never applying defaults to TEXT and BINARY fields
 602                  if ($this->drop_default_value_required &&
 603                      $xmldb_field->getType() != XMLDB_TYPE_TEXT &&
 604                      $xmldb_field->getType() != XMLDB_TYPE_BINARY && !$xmldb_field->getNotNull()) {
 605                      $default = $this->drop_default_value;
 606                  }
 607              }
 608          }
 609          return $default;
 610      }
 611  
 612      /**
 613       * Given one xmldb_field, returns the correct "default clause" for the current configuration.
 614       *
 615       * @param xmldb_field $xmldb_field The xmldb_field.
 616       * @return The SQL clause for generating the default value as in $xmldb_field.
 617       */
 618      public function getDefaultClause($xmldb_field) {
 619  
 620          $defaultvalue = $this->getDefaultValue ($xmldb_field);
 621  
 622          if ($defaultvalue !== null) {
 623              return ' DEFAULT ' . $defaultvalue;
 624          } else {
 625              return null;
 626          }
 627      }
 628  
 629      /**
 630       * Given one correct xmldb_table and the new name, returns the SQL statements
 631       * to rename it (inside one array).
 632       *
 633       * @param xmldb_table $xmldb_table The table to rename.
 634       * @param string $newname The new name to rename the table to.
 635       * @return array SQL statement(s) to rename the table.
 636       */
 637      public function getRenameTableSQL($xmldb_table, $newname) {
 638  
 639          $results = array();  //Array where all the sentences will be stored
 640  
 641          $newt = new xmldb_table($newname); //Temporal table for name calculations
 642  
 643          $rename = str_replace('OLDNAME', $this->getTableName($xmldb_table), $this->rename_table_sql);
 644          $rename = str_replace('NEWNAME', $this->getTableName($newt), $rename);
 645  
 646          $results[] = $rename;
 647  
 648          // Call to getRenameTableExtraSQL() override if needed
 649          $extra_sentences = $this->getRenameTableExtraSQL($xmldb_table, $newname);
 650          $results = array_merge($results, $extra_sentences);
 651  
 652          return $results;
 653      }
 654  
 655      /**
 656       * Given one correct xmldb_table, returns the SQL statements
 657       * to drop it (inside one array). Works also for temporary tables.
 658       *
 659       * @param xmldb_table $xmldb_table The table to drop.
 660       * @return array SQL statement(s) for dropping the specified table.
 661       */
 662      public function getDropTableSQL($xmldb_table) {
 663  
 664          $results = array();  //Array where all the sentences will be stored
 665  
 666          $drop = str_replace('TABLENAME', $this->getTableName($xmldb_table), $this->drop_table_sql);
 667  
 668          $results[] = $drop;
 669  
 670          // call to getDropTableExtraSQL(), override if needed
 671          $extra_sentences = $this->getDropTableExtraSQL($xmldb_table);
 672          $results = array_merge($results, $extra_sentences);
 673  
 674          return $results;
 675      }
 676  
 677      /**
 678       * Performs any clean up that needs to be done after a table is dropped.
 679       *
 680       * @param xmldb_table $table
 681       */
 682      public function cleanup_after_drop(xmldb_table $table): void {
 683          if ($this->temptables->is_temptable($table->getName())) {
 684              $this->temptables->delete_temptable($table->getName());
 685          }
 686      }
 687  
 688      /**
 689       * Given one xmldb_table and one xmldb_field, return the SQL statements needed to add the field to the table.
 690       *
 691       * @param xmldb_table $xmldb_table The table related to $xmldb_field.
 692       * @param xmldb_field $xmldb_field The instance of xmldb_field to create the SQL from.
 693       * @param string $skip_type_clause The type clause on alter columns, NULL by default.
 694       * @param string $skip_default_clause The default clause on alter columns, NULL by default.
 695       * @param string $skip_notnull_clause The null/notnull clause on alter columns, NULL by default.
 696       * @return array The SQL statement for adding a field to the table.
 697       */
 698      public function getAddFieldSQL($xmldb_table, $xmldb_field, $skip_type_clause = NULL, $skip_default_clause = NULL, $skip_notnull_clause = NULL) {
 699  
 700          $skip_type_clause = is_null($skip_type_clause) ? $this->alter_column_skip_type : $skip_type_clause;
 701          $skip_default_clause = is_null($skip_default_clause) ? $this->alter_column_skip_default : $skip_default_clause;
 702          $skip_notnull_clause = is_null($skip_notnull_clause) ? $this->alter_column_skip_notnull : $skip_notnull_clause;
 703  
 704          $results = array();
 705  
 706          // Get the quoted name of the table and field
 707          $tablename = $this->getTableName($xmldb_table);
 708  
 709          // Build the standard alter table add
 710          $sql = $this->getFieldSQL($xmldb_table, $xmldb_field, $skip_type_clause,
 711                                    $skip_default_clause,
 712                                    $skip_notnull_clause);
 713          $altertable = 'ALTER TABLE ' . $tablename . ' ADD ' . $sql;
 714          // Add the after clause if necessary
 715          if ($this->add_after_clause && $xmldb_field->getPrevious()) {
 716              $altertable .= ' AFTER ' . $this->getEncQuoted($xmldb_field->getPrevious());
 717          }
 718          $results[] = $altertable;
 719  
 720          return $results;
 721      }
 722  
 723      /**
 724       * Given one xmldb_table and one xmldb_field, return the SQL statements needed to drop the field from the table.
 725       *
 726       * @param xmldb_table $xmldb_table The table related to $xmldb_field.
 727       * @param xmldb_field $xmldb_field The instance of xmldb_field to create the SQL from.
 728       * @return array The SQL statement for dropping a field from the table.
 729       */
 730      public function getDropFieldSQL($xmldb_table, $xmldb_field) {
 731  
 732          $results = array();
 733  
 734          // Get the quoted name of the table and field
 735          $tablename = $this->getTableName($xmldb_table);
 736          $fieldname = $this->getEncQuoted($xmldb_field->getName());
 737  
 738          // Build the standard alter table drop
 739          $results[] = 'ALTER TABLE ' . $tablename . ' DROP COLUMN ' . $fieldname;
 740  
 741          return $results;
 742      }
 743  
 744      /**
 745       * Given one xmldb_table and one xmldb_field, return the SQL statements needed to alter the field in the table.
 746       *
 747       * @param xmldb_table $xmldb_table The table related to $xmldb_field.
 748       * @param xmldb_field $xmldb_field The instance of xmldb_field to create the SQL from.
 749       * @param string $skip_type_clause The type clause on alter columns, NULL by default.
 750       * @param string $skip_default_clause The default clause on alter columns, NULL by default.
 751       * @param string $skip_notnull_clause The null/notnull clause on alter columns, NULL by default.
 752       * @return array The field altering SQL statement.
 753       */
 754      public function getAlterFieldSQL($xmldb_table, $xmldb_field, $skip_type_clause = NULL, $skip_default_clause = NULL, $skip_notnull_clause = NULL) {
 755  
 756          $skip_type_clause = is_null($skip_type_clause) ? $this->alter_column_skip_type : $skip_type_clause;
 757          $skip_default_clause = is_null($skip_default_clause) ? $this->alter_column_skip_default : $skip_default_clause;
 758          $skip_notnull_clause = is_null($skip_notnull_clause) ? $this->alter_column_skip_notnull : $skip_notnull_clause;
 759  
 760          $results = array();
 761  
 762          // Get the quoted name of the table and field
 763          $tablename = $this->getTableName($xmldb_table);
 764          $fieldname = $this->getEncQuoted($xmldb_field->getName());
 765  
 766          // Build de alter sentence using the alter_column_sql template
 767          $alter = str_replace('TABLENAME', $this->getTableName($xmldb_table), $this->alter_column_sql);
 768          $colspec = $this->getFieldSQL($xmldb_table, $xmldb_field, $skip_type_clause,
 769                                        $skip_default_clause,
 770                                        $skip_notnull_clause,
 771                                        true);
 772          $alter = str_replace('COLUMNSPECS', $colspec, $alter);
 773  
 774          // Add the after clause if necessary
 775          if ($this->add_after_clause && $xmldb_field->getPrevious()) {
 776              $alter .= ' after ' . $this->getEncQuoted($xmldb_field->getPrevious());
 777          }
 778  
 779          // Build the standard alter table modify
 780          $results[] = $alter;
 781  
 782          return $results;
 783      }
 784  
 785      /**
 786       * Given one xmldb_table and one xmldb_field, return the SQL statements needed to modify the default of the field in the table.
 787       *
 788       * @param xmldb_table $xmldb_table The table related to $xmldb_field.
 789       * @param xmldb_field $xmldb_field The instance of xmldb_field to get the modified default value from.
 790       * @return array The SQL statement for modifying the default value.
 791       */
 792      public function getModifyDefaultSQL($xmldb_table, $xmldb_field) {
 793  
 794          $results = array();
 795  
 796          // Get the quoted name of the table and field
 797          $tablename = $this->getTableName($xmldb_table);
 798          $fieldname = $this->getEncQuoted($xmldb_field->getName());
 799  
 800          // Decide if we are going to create/modify or to drop the default
 801          if ($xmldb_field->getDefault() === null) {
 802              $results = $this->getDropDefaultSQL($xmldb_table, $xmldb_field); //Drop
 803          } else {
 804              $results = $this->getCreateDefaultSQL($xmldb_table, $xmldb_field); //Create/modify
 805          }
 806  
 807          return $results;
 808      }
 809  
 810      /**
 811       * Given one correct xmldb_field and the new name, returns the SQL statements
 812       * to rename it (inside one array).
 813       *
 814       * @param xmldb_table $xmldb_table The table related to $xmldb_field.
 815       * @param xmldb_field $xmldb_field The instance of xmldb_field to get the renamed field from.
 816       * @param string $newname The new name to rename the field to.
 817       * @return array The SQL statements for renaming the field.
 818       */
 819      public function getRenameFieldSQL($xmldb_table, $xmldb_field, $newname) {
 820  
 821          $results = array();  //Array where all the sentences will be stored
 822  
 823          // Although this is checked in database_manager::rename_field() - double check
 824          // that we aren't trying to rename one "id" field. Although it could be
 825          // implemented (if adding the necessary code to rename sequences, defaults,
 826          // triggers... and so on under each getRenameFieldExtraSQL() function, it's
 827          // better to forbid it, mainly because this field is the default PK and
 828          // in the future, a lot of FKs can be pointing here. So, this field, more
 829          // or less, must be considered immutable!
 830          if ($xmldb_field->getName() == 'id') {
 831              return array();
 832          }
 833  
 834          $rename = str_replace('TABLENAME', $this->getTableName($xmldb_table), $this->rename_column_sql);
 835          $rename = str_replace('OLDFIELDNAME', $this->getEncQuoted($xmldb_field->getName()), $rename);
 836          $rename = str_replace('NEWFIELDNAME', $this->getEncQuoted($newname), $rename);
 837  
 838          $results[] = $rename;
 839  
 840          // Call to getRenameFieldExtraSQL(), override if needed
 841          $extra_sentences = $this->getRenameFieldExtraSQL($xmldb_table, $xmldb_field, $newname);
 842          $results = array_merge($results, $extra_sentences);
 843  
 844          return $results;
 845      }
 846  
 847      /**
 848       * Given one xmldb_table and one xmldb_key, return the SQL statements needed to add the key to the table
 849       * note that undelying indexes will be added as parametrised by $xxxx_keys and $xxxx_index parameters.
 850       *
 851       * @param xmldb_table $xmldb_table The table related to $xmldb_key.
 852       * @param xmldb_key $xmldb_key The xmldb_key to add.
 853       * @return array SQL statement to add the xmldb_key.
 854       */
 855      public function getAddKeySQL($xmldb_table, $xmldb_key) {
 856  
 857          $results = array();
 858  
 859          // Just use the CreateKeySQL function
 860          if ($keyclause = $this->getKeySQL($xmldb_table, $xmldb_key)) {
 861              $key = 'ALTER TABLE ' . $this->getTableName($xmldb_table) .
 862                 ' ADD CONSTRAINT ' . $keyclause;
 863              $results[] = $key;
 864          }
 865  
 866          // If we aren't creating the keys OR if the key is XMLDB_KEY_FOREIGN (not underlying index generated
 867          // automatically by the RDBMS) create the underlying (created by us) index (if doesn't exists)
 868          if (!$keyclause || $xmldb_key->getType() == XMLDB_KEY_FOREIGN) {
 869              // Only if they don't exist
 870              if ($xmldb_key->getType() == XMLDB_KEY_FOREIGN) {      //Calculate type of index based on type ok key
 871                  $indextype = XMLDB_INDEX_NOTUNIQUE;
 872              } else {
 873                  $indextype = XMLDB_INDEX_UNIQUE;
 874              }
 875              $xmldb_index = new xmldb_index('anyname', $indextype, $xmldb_key->getFields());
 876              if (!$this->mdb->get_manager()->index_exists($xmldb_table, $xmldb_index)) {
 877                  $results = array_merge($results, $this->getAddIndexSQL($xmldb_table, $xmldb_index));
 878              }
 879          }
 880  
 881          // If the key is XMLDB_KEY_FOREIGN_UNIQUE, create it as UNIQUE too
 882          if ($xmldb_key->getType() == XMLDB_KEY_FOREIGN_UNIQUE && $this->unique_keys) {
 883              //Duplicate the key
 884              $xmldb_key->setType(XMLDB_KEY_UNIQUE);
 885              $results = array_merge($results, $this->getAddKeySQL($xmldb_table, $xmldb_key));
 886          }
 887  
 888          // Return results
 889          return $results;
 890      }
 891  
 892      /**
 893       * Given one xmldb_table and one xmldb_index, return the SQL statements needed to drop the index from the table.
 894       *
 895       * @param xmldb_table $xmldb_table The table related to $xmldb_key.
 896       * @param xmldb_key $xmldb_key The xmldb_key to drop.
 897       * @return array SQL statement to drop the xmldb_key.
 898       */
 899      public function getDropKeySQL($xmldb_table, $xmldb_key) {
 900  
 901          $results = array();
 902  
 903          // Get the key name (note that this doesn't introspect DB, so could cause some problems sometimes!)
 904          // TODO: We'll need to overwrite the whole getDropKeySQL() method inside each DB to do the proper queries
 905          // against the dictionary or require ADOdb to support it or change the find_key_name() method to
 906          // perform DB introspection directly. But, for now, as we aren't going to enable referential integrity
 907          // it won't be a problem at all
 908          $dbkeyname = $this->mdb->get_manager()->find_key_name($xmldb_table, $xmldb_key);
 909  
 910          // Only if such type of key generation is enabled
 911          $dropkey = false;
 912          switch ($xmldb_key->getType()) {
 913              case XMLDB_KEY_PRIMARY:
 914                  if ($this->primary_keys) {
 915                      $template = $this->drop_primary_key;
 916                      $dropkey = true;
 917                  }
 918                  break;
 919              case XMLDB_KEY_UNIQUE:
 920                  if ($this->unique_keys) {
 921                      $template = $this->drop_unique_key;
 922                      $dropkey = true;
 923                  }
 924                  break;
 925              case XMLDB_KEY_FOREIGN_UNIQUE:
 926              case XMLDB_KEY_FOREIGN:
 927                  if ($this->foreign_keys) {
 928                      $template = $this->drop_foreign_key;
 929                      $dropkey = true;
 930                  }
 931                  break;
 932          }
 933          // If we have decided to drop the key, let's do it
 934          if ($dropkey) {
 935              // Replace TABLENAME, CONSTRAINTTYPE and KEYNAME as needed
 936              $dropsql = str_replace('TABLENAME', $this->getTableName($xmldb_table), $template);
 937              $dropsql = str_replace('KEYNAME', $dbkeyname, $dropsql);
 938  
 939              $results[] = $dropsql;
 940          }
 941  
 942          // If we aren't dropping the keys OR if the key is XMLDB_KEY_FOREIGN (not underlying index generated
 943          // automatically by the RDBMS) drop the underlying (created by us) index (if exists)
 944          if (!$dropkey || $xmldb_key->getType() == XMLDB_KEY_FOREIGN) {
 945              // Only if they exist
 946              $xmldb_index = new xmldb_index('anyname', XMLDB_INDEX_UNIQUE, $xmldb_key->getFields());
 947              if ($this->mdb->get_manager()->index_exists($xmldb_table, $xmldb_index)) {
 948                  $results = array_merge($results, $this->getDropIndexSQL($xmldb_table, $xmldb_index));
 949              }
 950          }
 951  
 952          // If the key is XMLDB_KEY_FOREIGN_UNIQUE, drop the UNIQUE too
 953          if ($xmldb_key->getType() == XMLDB_KEY_FOREIGN_UNIQUE && $this->unique_keys) {
 954              //Duplicate the key
 955              $xmldb_key->setType(XMLDB_KEY_UNIQUE);
 956              $results = array_merge($results, $this->getDropKeySQL($xmldb_table, $xmldb_key));
 957          }
 958  
 959          // Return results
 960          return $results;
 961      }
 962  
 963      /**
 964       * Given one xmldb_table and one xmldb_key, return the SQL statements needed to rename the key in the table
 965       * Experimental! Shouldn't be used at all!
 966       *
 967       * @param xmldb_table $xmldb_table The table related to $xmldb_key.
 968       * @param xmldb_key $xmldb_key The xmldb_key to rename.
 969       * @param string $newname The xmldb_key's new name.
 970       * @return array SQL statement to rename the xmldb_key.
 971       */
 972      public function getRenameKeySQL($xmldb_table, $xmldb_key, $newname) {
 973  
 974          $results = array();
 975  
 976          // Get the real key name
 977          $dbkeyname = $this->mdb->get_manager()->find_key_name($xmldb_table, $xmldb_key);
 978  
 979          // Check we are really generating this type of keys
 980          if (($xmldb_key->getType() == XMLDB_KEY_PRIMARY && !$this->primary_keys) ||
 981              ($xmldb_key->getType() == XMLDB_KEY_UNIQUE && !$this->unique_keys) ||
 982              ($xmldb_key->getType() == XMLDB_KEY_FOREIGN && !$this->foreign_keys) ||
 983              ($xmldb_key->getType() == XMLDB_KEY_FOREIGN_UNIQUE && !$this->unique_keys && !$this->foreign_keys)) {
 984              // We aren't generating this type of keys, delegate to child indexes
 985              $xmldb_index = new xmldb_index($xmldb_key->getName());
 986              $xmldb_index->setFields($xmldb_key->getFields());
 987              return $this->getRenameIndexSQL($xmldb_table, $xmldb_index, $newname);
 988          }
 989  
 990          // Arrived here so we are working with keys, lets rename them
 991          // Replace TABLENAME and KEYNAME as needed
 992          $renamesql = str_replace('TABLENAME', $this->getTableName($xmldb_table), $this->rename_key_sql);
 993          $renamesql = str_replace('OLDKEYNAME', $dbkeyname, $renamesql);
 994          $renamesql = str_replace('NEWKEYNAME', $newname, $renamesql);
 995  
 996          // Some DB doesn't support key renaming so this can be empty
 997          if ($renamesql) {
 998              $results[] = $renamesql;
 999          }
1000  
1001          return $results;
1002      }
1003  
1004      /**
1005       * Given one xmldb_table and one xmldb_index, return the SQL statements needed to add the index to the table.
1006       *
1007       * @param xmldb_table $xmldb_table The xmldb_table instance to add the index on.
1008       * @param xmldb_index $xmldb_index The xmldb_index to add.
1009       * @return array An array of SQL statements to add the index.
1010       */
1011      public function getAddIndexSQL($xmldb_table, $xmldb_index) {
1012  
1013          // Just use the CreateIndexSQL function
1014          return $this->getCreateIndexSQL($xmldb_table, $xmldb_index);
1015      }
1016  
1017      /**
1018       * Given one xmldb_table and one xmldb_index, return the SQL statements needed to drop the index from the table.
1019       *
1020       * @param xmldb_table $xmldb_table The xmldb_table instance to drop the index on.
1021       * @param xmldb_index $xmldb_index The xmldb_index to drop.
1022       * @return array An array of SQL statements to drop the index.
1023       */
1024      public function getDropIndexSQL($xmldb_table, $xmldb_index) {
1025  
1026          $results = array();
1027  
1028          // Get the real index name
1029          $dbindexnames = $this->mdb->get_manager()->find_index_name($xmldb_table, $xmldb_index, true);
1030  
1031          // Replace TABLENAME and INDEXNAME as needed
1032          if ($dbindexnames) {
1033              foreach ($dbindexnames as $dbindexname) {
1034                  $dropsql = str_replace('TABLENAME', $this->getTableName($xmldb_table), $this->drop_index_sql);
1035                  $dropsql = str_replace('INDEXNAME', $this->getEncQuoted($dbindexname), $dropsql);
1036                  $results[] = $dropsql;
1037              }
1038          }
1039  
1040          return $results;
1041      }
1042  
1043      /**
1044       * Given one xmldb_table and one xmldb_index, return the SQL statements needed to rename the index in the table
1045       * Experimental! Shouldn't be used at all!
1046       *
1047       * @param xmldb_table $xmldb_table The xmldb_table instance to rename the index on.
1048       * @param xmldb_index $xmldb_index The xmldb_index to rename.
1049       * @param string $newname The xmldb_index's new name.
1050       * @return array An array of SQL statements to rename the index.
1051       */
1052      function getRenameIndexSQL($xmldb_table, $xmldb_index, $newname) {
1053          // Some DB doesn't support index renaming (MySQL) so this can be empty
1054          if (empty($this->rename_index_sql)) {
1055              return array();
1056          }
1057  
1058          // Get the real index name
1059          $dbindexname = $this->mdb->get_manager()->find_index_name($xmldb_table, $xmldb_index);
1060          // Replace TABLENAME and INDEXNAME as needed
1061          $renamesql = str_replace('TABLENAME', $this->getTableName($xmldb_table), $this->rename_index_sql);
1062          $renamesql = str_replace('OLDINDEXNAME', $this->getEncQuoted($dbindexname), $renamesql);
1063          $renamesql = str_replace('NEWINDEXNAME', $this->getEncQuoted($newname), $renamesql);
1064  
1065          return array($renamesql);
1066      }
1067  
1068      /**
1069       * Given three strings (table name, list of fields (comma separated) and suffix),
1070       * create the proper object name quoting it if necessary.
1071       *
1072       * IMPORTANT: This function must be used to CALCULATE NAMES of objects TO BE CREATED,
1073       *            NEVER TO GUESS NAMES of EXISTING objects!!!
1074       *
1075       * @param string $tablename The table name.
1076       * @param string $fields A list of comma separated fields.
1077       * @param string $suffix A suffix for the object name.
1078       * @return string Object's name.
1079       */
1080      public function getNameForObject($tablename, $fields, $suffix='') {
1081  
1082          $name = '';
1083  
1084          // Implement one basic cache to avoid object name duplication
1085          // along all the request life, but never to return cached results
1086          // We need this because sql statements are created before executing
1087          // them, hence names doesn't exist "physically" yet in DB, so we need
1088          // to known which ones have been used.
1089          // We track all the keys used, and the previous counters to make subsequent creates faster.
1090          // This may happen a lot with things like bulk backups or restores.
1091          static $usednames = array();
1092          static $previouscounters = array();
1093  
1094          // Use standard naming. See http://docs.moodle.org/en/XMLDB_key_and_index_naming
1095          $tablearr = explode ('_', $tablename);
1096          foreach ($tablearr as $table) {
1097              $name .= substr(trim($table),0,4);
1098          }
1099          $name .= '_';
1100          $fieldsarr = explode (',', $fields);
1101          foreach ($fieldsarr as $field) {
1102              $name .= substr(trim($field),0,3);
1103          }
1104          // Prepend the prefix
1105          $name = trim($this->prefix . $name);
1106  
1107          // Make sure name does not exceed the maximum name length and add suffix.
1108          $maxlengthwithoutsuffix = $this->names_max_length - strlen($suffix) - ($suffix ? 1 : 0);
1109          $namewithsuffix = substr($name, 0, $maxlengthwithoutsuffix) . ($suffix ? ('_' . $suffix) : '');
1110  
1111          if (isset($previouscounters[$name])) {
1112              // If we have a counter stored, we will need to modify the key to the next counter location.
1113              $counter = $previouscounters[$name] + 1;
1114              $namewithsuffix = substr($name, 0, $maxlengthwithoutsuffix - strlen($counter)) .
1115                      $counter . ($suffix ? ('_' . $suffix) : '');
1116          } else {
1117              $counter = 1;
1118          }
1119  
1120          // If the calculated name is in the cache, or if we detect it by introspecting the DB let's modify it.
1121          while (isset($usednames[$namewithsuffix]) || $this->isNameInUse($namewithsuffix, $suffix, $tablename)) {
1122              // Now iterate until not used name is found, incrementing the counter
1123              $counter++;
1124              $namewithsuffix = substr($name, 0, $maxlengthwithoutsuffix - strlen($counter)) .
1125                      $counter . ($suffix ? ('_' . $suffix) : '');
1126          }
1127  
1128          // Add the name to the cache. Using key look with isset because it is much faster than in_array.
1129          $usednames[$namewithsuffix] = true;
1130          $previouscounters[$name] = $counter;
1131  
1132          // Quote it if necessary (reserved words)
1133          $namewithsuffix = $this->getEncQuoted($namewithsuffix);
1134  
1135          return $namewithsuffix;
1136      }
1137  
1138      /**
1139       * Given any string (or one array), enclose it by the proper quotes
1140       * if it's a reserved word
1141       *
1142       * @param string|array $input String to quote.
1143       * @return string|array Quoted string.
1144       */
1145      public function getEncQuoted($input) {
1146  
1147          if (is_array($input)) {
1148              foreach ($input as $key=>$content) {
1149                  $input[$key] = $this->getEncQuoted($content);
1150              }
1151              return $input;
1152          } else {
1153              // Always lowercase
1154              $input = strtolower($input);
1155              // if reserved or quote_all or has hyphens, quote it
1156              if ($this->quote_all || in_array($input, $this->reserved_words) || strpos($input, '-') !== false) {
1157                  $input = $this->quote_string . $input . $this->quote_string;
1158              }
1159              return $input;
1160          }
1161      }
1162  
1163      /**
1164       * Given one XMLDB Statement, build the needed SQL insert sentences to execute it.
1165       *
1166       * @param mixed $statement SQL statement.
1167       * @return array Array of sentences in the SQL statement.
1168       */
1169      function getExecuteInsertSQL($statement) {
1170  
1171           $results = array();  //Array where all the sentences will be stored
1172  
1173           if ($sentences = $statement->getSentences()) {
1174               foreach ($sentences as $sentence) {
1175                   // Get the list of fields
1176                   $fields = $statement->getFieldsFromInsertSentence($sentence);
1177                   // Get the values of fields
1178                   $values = $statement->getValuesFromInsertSentence($sentence);
1179                   // Look if we have some CONCAT value and transform it dynamically
1180                   foreach($values as $key => $value) {
1181                       // Trim single quotes
1182                       $value = trim($value,"'");
1183                       if (stristr($value, 'CONCAT') !== false){
1184                           // Look for data between parenthesis
1185                           preg_match("/CONCAT\s*\((.*)\)$/is", trim($value), $matches);
1186                           if (isset($matches[1])) {
1187                               $part = $matches[1];
1188                               // Convert the comma separated string to an array
1189                               $arr = xmldb_object::comma2array($part);
1190                               if ($arr) {
1191                                   $value = $this->getConcatSQL($arr);
1192                               }
1193                           }
1194                       }
1195                       // Values to be sent to DB must be properly escaped
1196                       $value = $this->addslashes($value);
1197                       // Back trimmed quotes
1198                       $value = "'" . $value . "'";
1199                       // Back to the array
1200                       $values[$key] = $value;
1201                   }
1202  
1203                   // Iterate over fields, escaping them if necessary
1204                   foreach($fields as $key => $field) {
1205                       $fields[$key] = $this->getEncQuoted($field);
1206                   }
1207                   // Build the final SQL sentence and add it to the array of results
1208               $sql = 'INSERT INTO ' . $this->getEncQuoted($this->prefix . $statement->getTable()) .
1209                           '(' . implode(', ', $fields) . ') ' .
1210                           'VALUES (' . implode(', ', $values) . ')';
1211                   $results[] = $sql;
1212               }
1213  
1214           }
1215           return $results;
1216      }
1217  
1218      /**
1219       * Given one array of elements, build the proper CONCAT expression, based
1220       * in the $concat_character setting. If such setting is empty, then
1221       * MySQL's CONCAT function will be used instead.
1222       *
1223       * @param array $elements An array of elements to concatenate.
1224       * @return mixed Returns the result of moodle_database::sql_concat() or false.
1225       * @uses moodle_database::sql_concat()
1226       * @uses call_user_func_array()
1227       */
1228      public function getConcatSQL($elements) {
1229  
1230          // Replace double quoted elements by single quotes
1231          foreach($elements as $key => $element) {
1232              $element = trim($element);
1233              if (substr($element, 0, 1) == '"' &&
1234                  substr($element, -1, 1) == '"') {
1235                      $elements[$key] = "'" . trim($element, '"') . "'";
1236              }
1237          }
1238  
1239          // Now call the standard $DB->sql_concat() DML function
1240          return call_user_func_array(array($this->mdb, 'sql_concat'), array_values($elements));
1241      }
1242  
1243      /**
1244       * Returns the name (string) of the sequence used in the table for the autonumeric pk
1245       * Only some DB have this implemented.
1246       *
1247       * @param xmldb_table $xmldb_table The xmldb_table instance.
1248       * @return bool Returns the sequence from the DB or false.
1249       */
1250      public function getSequenceFromDB($xmldb_table) {
1251          return false;
1252      }
1253  
1254      /**
1255       * Given one object name and it's type (pk, uk, fk, ck, ix, uix, seq, trg).
1256       *
1257       * (MySQL requires the whole xmldb_table object to be specified, so we add it always)
1258       *
1259       * This is invoked from getNameForObject().
1260       * Only some DB have this implemented.
1261       *
1262       * @param string $object_name The object's name to check for.
1263       * @param string $type The object's type (pk, uk, fk, ck, ix, uix, seq, trg).
1264       * @param string $table_name The table's name to check in
1265       * @return bool If such name is currently in use (true) or no (false)
1266       */
1267      public function isNameInUse($object_name, $type, $table_name) {
1268          return false; //For generators not implementing introspection,
1269                        //we always return with the name being free to be used
1270      }
1271  
1272  
1273  // ====== FOLLOWING FUNCTION MUST BE CUSTOMISED BY ALL THE XMLDGenerator classes ========
1274  
1275      /**
1276       * Reset a sequence to the id field of a table.
1277       *
1278       * @param xmldb_table|string $table name of table or the table object.
1279       * @return array of sql statements
1280       */
1281      public abstract function getResetSequenceSQL($table);
1282  
1283      /**
1284       * Given one correct xmldb_table, returns the SQL statements
1285       * to create temporary table (inside one array).
1286       *
1287       * @param xmldb_table $xmldb_table The xmldb_table object instance.
1288       * @return array of sql statements
1289       */
1290      abstract public function getCreateTempTableSQL($xmldb_table);
1291  
1292      /**
1293       * Given one XMLDB Type, length and decimals, returns the DB proper SQL type.
1294       *
1295       * @param int $xmldb_type The xmldb_type defined constant. XMLDB_TYPE_INTEGER and other XMLDB_TYPE_* constants.
1296       * @param int $xmldb_length The length of that data type.
1297       * @param int $xmldb_decimals The decimal places of precision of the data type.
1298       * @return string The DB defined data type.
1299       */
1300      public abstract function getTypeSQL($xmldb_type, $xmldb_length=null, $xmldb_decimals=null);
1301  
1302      /**
1303       * Returns the code (array of statements) needed to execute extra statements on field rename.
1304       *
1305       * @param xmldb_table $xmldb_table The xmldb_table object instance.
1306       * @param xmldb_field $xmldb_field The xmldb_field object instance.
1307       * @return array Array of extra SQL statements to run with a field being renamed.
1308       */
1309      public function getRenameFieldExtraSQL($xmldb_table, $xmldb_field) {
1310          return array();
1311      }
1312  
1313      /**
1314       * Returns the code (array of statements) needed
1315       * to create one sequence for the xmldb_table and xmldb_field passed in.
1316       *
1317       * @param xmldb_table $xmldb_table The xmldb_table object instance.
1318       * @param xmldb_field $xmldb_field The xmldb_field object instance.
1319       * @return array Array of SQL statements to create the sequence.
1320       */
1321      public function getCreateSequenceSQL($xmldb_table, $xmldb_field) {
1322          return array();
1323      }
1324  
1325      /**
1326       * Returns the code (array of statements) needed to add one comment to the table.
1327       *
1328       * @param xmldb_table $xmldb_table The xmldb_table object instance.
1329       * @return array Array of SQL statements to add one comment to the table.
1330       */
1331      public abstract function getCommentSQL($xmldb_table);
1332  
1333      /**
1334       * Returns the code (array of statements) needed to execute extra statements on table rename.
1335       *
1336       * @param xmldb_table $xmldb_table The xmldb_table object instance.
1337       * @param string $newname The new name for the table.
1338       * @return array Array of extra SQL statements to rename a table.
1339       */
1340      public function getRenameTableExtraSQL($xmldb_table, $newname) {
1341          return array();
1342      }
1343  
1344      /**
1345       * Returns the code (array of statements) needed to execute extra statements on table drop
1346       *
1347       * @param xmldb_table $xmldb_table The xmldb_table object instance.
1348       * @return array Array of extra SQL statements to drop a table.
1349       */
1350      public function getDropTableExtraSQL($xmldb_table) {
1351          return array();
1352      }
1353  
1354      /**
1355       * Given one xmldb_table and one xmldb_field, return the SQL statements needed to drop its default
1356       * (usually invoked from getModifyDefaultSQL()
1357       *
1358       * Note that this method may be dropped in future.
1359       *
1360       * @param xmldb_table $xmldb_table The xmldb_table object instance.
1361       * @param xmldb_field $xmldb_field The xmldb_field object instance.
1362       * @return array Array of SQL statements to create a field's default.
1363       *
1364       * @todo MDL-31147 Moodle 2.1 - Drop getDropDefaultSQL()
1365       */
1366      public abstract function getDropDefaultSQL($xmldb_table, $xmldb_field);
1367  
1368      /**
1369       * Given one xmldb_table and one xmldb_field, return the SQL statements needed to add its default
1370       * (usually invoked from getModifyDefaultSQL()
1371       *
1372       * @param xmldb_table $xmldb_table The xmldb_table object instance.
1373       * @param xmldb_field $xmldb_field The xmldb_field object instance.
1374       * @return array Array of SQL statements to create a field's default.
1375       */
1376      public abstract function getCreateDefaultSQL($xmldb_table, $xmldb_field);
1377  
1378      /**
1379       * Returns an array of reserved words (lowercase) for this DB
1380       * You MUST provide the real list for each DB inside every XMLDB class.
1381       * @return array An array of database specific reserved words.
1382       * @throws coding_exception Thrown if not implemented for the specific DB.
1383       */
1384      public static function getReservedWords() {
1385          throw new coding_exception('getReservedWords() method needs to be overridden in each subclass of sql_generator');
1386      }
1387  
1388      /**
1389       * Returns all reserved words in supported databases.
1390       * Reserved words should be lowercase.
1391       * @return array ('word'=>array(databases))
1392       */
1393      public static function getAllReservedWords() {
1394          global $CFG;
1395  
1396          $generators = array('mysql', 'postgres', 'oracle', 'mssql');
1397          $reserved_words = array();
1398  
1399          foreach($generators as $generator) {
1400              $class = $generator . '_sql_generator';
1401              require_once("$CFG->libdir/ddl/$class.php");
1402              foreach (call_user_func(array($class, 'getReservedWords')) as $word) {
1403                  $reserved_words[$word][] = $generator;
1404              }
1405          }
1406          ksort($reserved_words);
1407          return $reserved_words;
1408      }
1409  
1410      /**
1411       * Adds slashes to string.
1412       * @param string $s
1413       * @return string The escaped string.
1414       */
1415      public function addslashes($s) {
1416          // do not use php addslashes() because it depends on PHP quote settings!
1417          $s = str_replace('\\','\\\\',$s);
1418          $s = str_replace("\0","\\\0", $s);
1419          $s = str_replace("'",  "\\'", $s);
1420          return $s;
1421      }
1422  
1423      /**
1424       * Get the fields from an index definition that might be null.
1425       * @param xmldb_table $xmldb_table the table
1426       * @param xmldb_index $xmldb_index the index
1427       * @return array list of fields in the index definition that might be null.
1428       */
1429      public function get_nullable_fields_in_index($xmldb_table, $xmldb_index) {
1430          global $DB;
1431  
1432          // If we don't have the field info passed in, we need to query it from the DB.
1433          $fieldsfromdb = null;
1434  
1435          $nullablefields = [];
1436          foreach ($xmldb_index->getFields() as $fieldname) {
1437              if ($field = $xmldb_table->getField($fieldname)) {
1438                  // We have the field details in the table definition.
1439                  if ($field->getNotNull() !== XMLDB_NOTNULL) {
1440                      $nullablefields[] = $fieldname;
1441                  }
1442  
1443              } else {
1444                  // We don't have the table definition loaded. Need to
1445                  // inspect the database.
1446                  if ($fieldsfromdb === null) {
1447                      $fieldsfromdb = $DB->get_columns($xmldb_table->getName(), false);
1448                  }
1449                  if (!isset($fieldsfromdb[$fieldname])) {
1450                      throw new coding_exception('Unknown field ' . $fieldname .
1451                              ' in index ' . $xmldb_index->getName());
1452                  }
1453  
1454                  if (!$fieldsfromdb[$fieldname]->not_null) {
1455                      $nullablefields[] = $fieldname;
1456                  }
1457              }
1458          }
1459  
1460          return $nullablefields;
1461      }
1462  }