[Summary view]

   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   * Experimental SQLite specific SQL code generator.
  19   *
  20   * @package    core_ddl
  21   * @copyright  2008 Andrei Bautu
  22   * @license    http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
  23   */
  24  
  25  defined('MOODLE_INTERNAL') || die();
  26  
  27  require_once($CFG->libdir.'/ddl/sql_generator.php');
  28  
  29  /// This class generate SQL code to be used against SQLite
  30  /// It extends XMLDBgenerator so everything can be
  31  /// overridden as needed to generate correct SQL.
  32  
  33  class sqlite_sql_generator extends sql_generator {
  34  
  35  /// Only set values that are different from the defaults present in XMLDBgenerator
  36  
  37      /** @var bool To specify if the generator must use some DEFAULT clause to drop defaults.*/
  38      public $drop_default_value_required = true;
  39  
  40      /** @var string The DEFAULT clause required to drop defaults.*/
  41      public $drop_default_value = NULL;
  42  
  43      /** @var string Template to drop PKs. 'TABLENAME' and 'KEYNAME' will be replaced from this template.*/
  44      public $drop_primary_key = 'ALTER TABLE TABLENAME DROP PRIMARY KEY';
  45  
  46      /** @var string Template to drop UKs. 'TABLENAME' and 'KEYNAME' will be replaced from this template.*/
  47      public $drop_unique_key = 'ALTER TABLE TABLENAME DROP KEY KEYNAME';
  48  
  49      /** @var string Template to drop FKs. 'TABLENAME' and 'KEYNAME' will be replaced from this template.*/
  50      public $drop_foreign_key = 'ALTER TABLE TABLENAME DROP FOREIGN KEY KEYNAME';
  51  
  52      /** @var string To define the default to set for NOT NULLs CHARs without default (null=do nothing).*/
  53      public $default_for_char = '';
  54  
  55      /** @var bool To avoid outputting the rest of the field specs, leaving only the name and the sequence_name returned.*/
  56      public $sequence_only = true;
  57  
  58      /** @var bool True if the generator needs to add extra code to generate the sequence fields.*/
  59      public $sequence_extra_code = false;
  60  
  61      /** @var string The particular name for inline sequences in this generator.*/
  62      public $sequence_name = 'INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL';
  63  
  64      /** @var string SQL sentence to drop one index where 'TABLENAME', 'INDEXNAME' keywords are dynamically replaced.*/
  65      public $drop_index_sql = 'ALTER TABLE TABLENAME DROP INDEX INDEXNAME';
  66  
  67      /** @var string SQL sentence to rename one index where 'TABLENAME', 'OLDINDEXNAME' and 'NEWINDEXNAME' are dynamically replaced.*/
  68      public $rename_index_sql = null;
  69  
  70      /** @var string SQL sentence to rename one key 'TABLENAME', 'OLDKEYNAME' and 'NEWKEYNAME' are dynamically replaced.*/
  71      public $rename_key_sql = null;
  72  
  73      /**
  74       * Creates one new XMLDBmysql
  75       */
  76      public function __construct($mdb) {
  77          parent::__construct($mdb);
  78      }
  79  
  80      /**
  81       * Reset a sequence to the id field of a table.
  82       *
  83       * @param xmldb_table|string $table name of table or the table object.
  84       * @return array of sql statements
  85       */
  86      public function getResetSequenceSQL($table) {
  87  
  88          if ($table instanceof xmldb_table) {
  89              $table = $table->getName();
  90          }
  91  
  92          // From http://sqlite.org/autoinc.html
  93          $value = (int)$this->mdb->get_field_sql('SELECT MAX(id) FROM {'.$table.'}');
  94          return array("UPDATE sqlite_sequence SET seq=$value WHERE name='{$this->prefix}{$table}'");
  95      }
  96  
  97      /**
  98       * Given one correct xmldb_key, returns its specs
  99       */
 100      public function getKeySQL($xmldb_table, $xmldb_key) {
 101  
 102          $key = '';
 103  
 104          switch ($xmldb_key->getType()) {
 105              case XMLDB_KEY_PRIMARY:
 106                  if ($this->primary_keys && count($xmldb_key->getFields())>1) {
 107                      if ($this->primary_key_name !== null) {
 108                          $key = $this->getEncQuoted($this->primary_key_name);
 109                      } else {
 110                          $key = $this->getNameForObject($xmldb_table->getName(), implode(', ', $xmldb_key->getFields()), 'pk');
 111                      }
 112                      $key .= ' PRIMARY KEY (' . implode(', ', $this->getEncQuoted($xmldb_key->getFields())) . ')';
 113                  }
 114                  break;
 115              case XMLDB_KEY_UNIQUE:
 116                  if ($this->unique_keys) {
 117                      $key = $this->getNameForObject($xmldb_table->getName(), implode(', ', $xmldb_key->getFields()), 'uk');
 118                      $key .= ' UNIQUE (' . implode(', ', $this->getEncQuoted($xmldb_key->getFields())) . ')';
 119                  }
 120                  break;
 121              case XMLDB_KEY_FOREIGN:
 122              case XMLDB_KEY_FOREIGN_UNIQUE:
 123                  if ($this->foreign_keys) {
 124                      $key = $this->getNameForObject($xmldb_table->getName(), implode(', ', $xmldb_key->getFields()), 'fk');
 125                      $key .= ' FOREIGN KEY (' . implode(', ', $this->getEncQuoted($xmldb_key->getFields())) . ')';
 126                      $key .= ' REFERENCES ' . $this->getEncQuoted($this->prefix . $xmldb_key->getRefTable());
 127                      $key .= ' (' . implode(', ', $this->getEncQuoted($xmldb_key->getRefFields())) . ')';
 128                  }
 129                  break;
 130          }
 131  
 132          return $key;
 133      }
 134  
 135      /**
 136       * Given one XMLDB Type, length and decimals, returns the DB proper SQL type.
 137       *
 138       * @param int $xmldb_type The xmldb_type defined constant. XMLDB_TYPE_INTEGER and other XMLDB_TYPE_* constants.
 139       * @param int $xmldb_length The length of that data type.
 140       * @param int $xmldb_decimals The decimal places of precision of the data type.
 141       * @return string The DB defined data type.
 142       */
 143      public function getTypeSQL($xmldb_type, $xmldb_length=null, $xmldb_decimals=null) {
 144  
 145          switch ($xmldb_type) {
 146              case XMLDB_TYPE_INTEGER:    // From http://www.sqlite.org/datatype3.html
 147                  if (empty($xmldb_length)) {
 148                      $xmldb_length = 10;
 149                  }
 150                  $dbtype = 'INTEGER(' . $xmldb_length . ')';
 151                  break;
 152              case XMLDB_TYPE_NUMBER:
 153                  $dbtype = $this->number_type;
 154                  if (!empty($xmldb_length)) {
 155                      $dbtype .= '(' . $xmldb_length;
 156                      if (!empty($xmldb_decimals)) {
 157                          $dbtype .= ',' . $xmldb_decimals;
 158                      }
 159                      $dbtype .= ')';
 160                  }
 161                  break;
 162              case XMLDB_TYPE_FLOAT:
 163                  $dbtype = 'REAL';
 164                  if (!empty($xmldb_length)) {
 165                      $dbtype .= '(' . $xmldb_length;
 166                      if (!empty($xmldb_decimals)) {
 167                          $dbtype .= ',' . $xmldb_decimals;
 168                      }
 169                      $dbtype .= ')';
 170                  }
 171                  break;
 172              case XMLDB_TYPE_CHAR:
 173                  $dbtype = 'VARCHAR';
 174                  if (empty($xmldb_length)) {
 175                      $xmldb_length='255';
 176                  }
 177                  $dbtype .= '(' . $xmldb_length . ')';
 178                  break;
 179              case XMLDB_TYPE_BINARY:
 180                  $dbtype = 'BLOB';
 181                  break;
 182              case XMLDB_TYPE_DATETIME:
 183                  $dbtype = 'DATETIME';
 184              default:
 185              case XMLDB_TYPE_TEXT:
 186                  $dbtype = 'TEXT';
 187                  break;
 188          }
 189          return $dbtype;
 190      }
 191  
 192      /**
 193       * Function to emulate full ALTER TABLE which SQLite does not support.
 194       * The function can be used to drop a column ($xmldb_delete_field != null and
 195       * $xmldb_add_field == null), add a column ($xmldb_delete_field == null and
 196       * $xmldb_add_field != null), change/rename a column ($xmldb_delete_field == null
 197       * and $xmldb_add_field == null).
 198       * @param xmldb_table $xmldb_table table to change
 199       * @param xmldb_field $xmldb_add_field column to create/modify (full specification is required)
 200       * @param xmldb_field $xmldb_delete_field column to delete/modify (only name field is required)
 201       * @return array of strings (SQL statements to alter the table structure)
 202       */
 203      protected function getAlterTableSchema($xmldb_table, $xmldb_add_field=NULL, $xmldb_delete_field=NULL) {
 204      /// Get the quoted name of the table and field
 205          $tablename = $this->getTableName($xmldb_table);
 206  
 207          $oldname = $xmldb_delete_field ? $xmldb_delete_field->getName() : NULL;
 208          $newname = $xmldb_add_field ? $xmldb_add_field->getName() : NULL;
 209          if($xmldb_delete_field) {
 210              $xmldb_table->deleteField($oldname);
 211          }
 212          if($xmldb_add_field) {
 213              $xmldb_table->addField($xmldb_add_field);
 214          }
 215          if($oldname) {
 216              // alter indexes
 217              $indexes = $xmldb_table->getIndexes();
 218              foreach($indexes as $index) {
 219                  $fields = $index->getFields();
 220                  $i = array_search($oldname, $fields);
 221                  if($i!==FALSE) {
 222                      if($newname) {
 223                          $fields[$i] = $newname;
 224                      } else {
 225                          unset($fields[$i]);
 226                      }
 227                      $xmldb_table->deleteIndex($index->getName());
 228                      if(count($fields)) {
 229                          $index->setFields($fields);
 230                          $xmldb_table->addIndex($index);
 231                      }
 232                  }
 233              }
 234              // alter keys
 235              $keys = $xmldb_table->getKeys();
 236              foreach($keys as $key) {
 237                  $fields = $key->getFields();
 238                  $reffields = $key->getRefFields();
 239                  $i = array_search($oldname, $fields);
 240                  if($i!==FALSE) {
 241                      if($newname) {
 242                          $fields[$i] = $newname;
 243                      } else {
 244                          unset($fields[$i]);
 245                          unset($reffields[$i]);
 246                      }
 247                      $xmldb_table->deleteKey($key->getName());
 248                      if(count($fields)) {
 249                          $key->setFields($fields);
 250                          $key->setRefFields($fields);
 251                          $xmldb_table->addkey($key);
 252                      }
 253                  }
 254              }
 255          }
 256          // prepare data copy
 257          $fields = $xmldb_table->getFields();
 258          foreach ($fields as $key => $field) {
 259              $fieldname = $field->getName();
 260              if($fieldname == $newname && $oldname && $oldname != $newname) {
 261                  // field rename operation
 262                  $fields[$key] = $this->getEncQuoted($oldname) . ' AS ' . $this->getEncQuoted($newname);
 263              } else {
 264                  $fields[$key] = $this->getEncQuoted($field->getName());
 265              }
 266          }
 267          $fields = implode(',', $fields);
 268          $results[] = 'BEGIN TRANSACTION';
 269          $results[] = 'CREATE TEMPORARY TABLE temp_data AS SELECT * FROM ' . $tablename;
 270          $results[] = 'DROP TABLE ' . $tablename;
 271          $results = array_merge($results, $this->getCreateTableSQL($xmldb_table));
 272          $results[] = 'INSERT INTO ' . $tablename . ' SELECT ' . $fields . ' FROM temp_data';
 273          $results[] = 'DROP TABLE temp_data';
 274          $results[] = 'COMMIT';
 275          return $results;
 276      }
 277  
 278      /**
 279       * Given one xmldb_table and one xmldb_field, return the SQL statements needed to alter the field in the table.
 280       *
 281       * @param xmldb_table $xmldb_table The table related to $xmldb_field.
 282       * @param xmldb_field $xmldb_field The instance of xmldb_field to create the SQL from.
 283       * @param string $skip_type_clause The type clause on alter columns, NULL by default.
 284       * @param string $skip_default_clause The default clause on alter columns, NULL by default.
 285       * @param string $skip_notnull_clause The null/notnull clause on alter columns, NULL by default.
 286       * @return string The field altering SQL statement.
 287       */
 288      public function getAlterFieldSQL($xmldb_table, $xmldb_field, $skip_type_clause = NULL, $skip_default_clause = NULL, $skip_notnull_clause = NULL) {
 289          return $this->getAlterTableSchema($xmldb_table, $xmldb_field, $xmldb_field);
 290      }
 291  
 292      /**
 293       * Given one xmldb_table and one xmldb_key, return the SQL statements needed to add the key to the table
 294       * note that underlying indexes will be added as parametrised by $xxxx_keys and $xxxx_index parameters
 295       */
 296      public function getAddKeySQL($xmldb_table, $xmldb_key) {
 297          $xmldb_table->addKey($xmldb_key);
 298          return $this->getAlterTableSchema($xmldb_table);
 299      }
 300  
 301      /**
 302       * Given one xmldb_table and one xmldb_field, return the SQL statements needed to add its default
 303       * (usually invoked from getModifyDefaultSQL()
 304       *
 305       * @param xmldb_table $xmldb_table The xmldb_table object instance.
 306       * @param xmldb_field $xmldb_field The xmldb_field object instance.
 307       * @return array Array of SQL statements to create a field's default.
 308       */
 309      public function getCreateDefaultSQL($xmldb_table, $xmldb_field) {
 310          return $this->getAlterTableSchema($xmldb_table, $xmldb_field, $xmldb_field);
 311      }
 312  
 313      /**
 314       * Given one correct xmldb_field and the new name, returns the SQL statements
 315       * to rename it (inside one array).
 316       *
 317       * @param xmldb_table $xmldb_table The table related to $xmldb_field.
 318       * @param xmldb_field $xmldb_field The instance of xmldb_field to get the renamed field from.
 319       * @param string $newname The new name to rename the field to.
 320       * @return array The SQL statements for renaming the field.
 321       */
 322      public function getRenameFieldSQL($xmldb_table, $xmldb_field, $newname) {
 323          $oldfield = clone($xmldb_field);
 324          $xmldb_field->setName($newname);
 325          return $this->getAlterTableSchema($xmldb_table, $xmldb_field, $oldfield);
 326      }
 327  
 328      /**
 329       * Given one xmldb_table and one xmldb_index, return the SQL statements needed to rename the index in the table
 330       */
 331      function getRenameIndexSQL($xmldb_table, $xmldb_index, $newname) {
 332      /// Get the real index name
 333          $dbindexname = $this->mdb->get_manager()->find_index_name($xmldb_table, $xmldb_index);
 334          $xmldb_index->setName($newname);
 335          $results = array('DROP INDEX ' . $dbindexname);
 336          $results = array_merge($results, $this->getCreateIndexSQL($xmldb_table, $xmldb_index));
 337          return $results;
 338      }
 339  
 340      /**
 341       * Given one xmldb_table and one xmldb_key, return the SQL statements needed to rename the key in the table
 342       * Experimental! Shouldn't be used at all!
 343       */
 344      public function getRenameKeySQL($xmldb_table, $xmldb_key, $newname) {
 345          $xmldb_table->deleteKey($xmldb_key->getName());
 346          $xmldb_key->setName($newname);
 347          $xmldb_table->addkey($xmldb_key);
 348          return $this->getAlterTableSchema($xmldb_table);
 349      }
 350  
 351      /**
 352       * Given one xmldb_table and one xmldb_field, return the SQL statements needed to drop the field from the table.
 353       *
 354       * @param xmldb_table $xmldb_table The table related to $xmldb_field.
 355       * @param xmldb_field $xmldb_field The instance of xmldb_field to create the SQL from.
 356       * @return array The SQL statement for dropping a field from the table.
 357       */
 358      public function getDropFieldSQL($xmldb_table, $xmldb_field) {
 359          return $this->getAlterTableSchema($xmldb_table, NULL, $xmldb_field);
 360      }
 361  
 362      /**
 363       * Given one xmldb_table and one xmldb_index, return the SQL statements needed to drop the index from the table
 364       */
 365      public function getDropIndexSQL($xmldb_table, $xmldb_index) {
 366          $xmldb_table->deleteIndex($xmldb_index->getName());
 367          return $this->getAlterTableSchema($xmldb_table);
 368      }
 369  
 370      /**
 371       * Given one xmldb_table and one xmldb_index, return the SQL statements needed to drop the index from the table
 372       */
 373      public function getDropKeySQL($xmldb_table, $xmldb_key) {
 374          $xmldb_table->deleteKey($xmldb_key->getName());
 375          return $this->getAlterTableSchema($xmldb_table);
 376      }
 377  
 378      /**
 379       * Given one xmldb_table and one xmldb_field, return the SQL statements needed to drop its default
 380       * (usually invoked from getModifyDefaultSQL()
 381       *
 382       * Note that this method may be dropped in future.
 383       *
 384       * @param xmldb_table $xmldb_table The xmldb_table object instance.
 385       * @param xmldb_field $xmldb_field The xmldb_field object instance.
 386       * @return array Array of SQL statements to create a field's default.
 387       *
 388       * @todo MDL-31147 Moodle 2.1 - Drop getDropDefaultSQL()
 389       */
 390      public function getDropDefaultSQL($xmldb_table, $xmldb_field) {
 391          return $this->getAlterTableSchema($xmldb_table, $xmldb_field, $xmldb_field);
 392      }
 393  
 394      /**
 395       * Returns the code (array of statements) needed to add one comment to the table.
 396       *
 397       * @param xmldb_table $xmldb_table The xmldb_table object instance.
 398       * @return array Array of SQL statements to add one comment to the table.
 399       */
 400      function getCommentSQL ($xmldb_table) {
 401          return array();
 402      }
 403  
 404      /**
 405       * Given one object name and it's type (pk, uk, fk, ck, ix, uix, seq, trg).
 406       *
 407       * (MySQL requires the whole xmldb_table object to be specified, so we add it always)
 408       *
 409       * This is invoked from getNameForObject().
 410       * Only some DB have this implemented.
 411       *
 412       * @param string $object_name The object's name to check for.
 413       * @param string $type The object's type (pk, uk, fk, ck, ix, uix, seq, trg).
 414       * @param string $table_name The table's name to check in
 415       * @return bool If such name is currently in use (true) or no (false)
 416       */
 417      public function isNameInUse($object_name, $type, $table_name) {
 418          // TODO: add introspection code
 419          return false; //No name in use found
 420      }
 421  
 422      /**
 423       * Returns an array of reserved words (lowercase) for this DB
 424       * @return array An array of database specific reserved words
 425       */
 426      public static function getReservedWords() {
 427      /// From http://www.sqlite.org/lang_keywords.html
 428          $reserved_words = array (
 429              'add', 'all', 'alter', 'and', 'as', 'autoincrement',
 430              'between', 'by',
 431              'case', 'check', 'collate', 'column', 'commit', 'constraint', 'create', 'cross',
 432              'default', 'deferrable', 'delete', 'distinct', 'drop',
 433              'else', 'escape', 'except', 'exists',
 434              'foreign', 'from', 'full',
 435              'group',
 436              'having',
 437              'in', 'index', 'inner', 'insert', 'intersect', 'into', 'is', 'isnull',
 438              'join',
 439              'left', 'limit',
 440              'natural', 'not', 'notnull', 'null',
 441              'on', 'or', 'order', 'outer',
 442              'primary',
 443              'references', 'regexp', 'right', 'rollback',
 444              'select', 'set',
 445              'table', 'then', 'to', 'transaction',
 446              'union', 'unique', 'update', 'using',
 447              'values',
 448              'when', 'where'
 449          );
 450          return $reserved_words;
 451      }
 452  
 453      /**
 454       * Adds slashes to string.
 455       * @param string $s
 456       * @return string The escaped string.
 457       */
 458      public function addslashes($s) {
 459          // do not use php addslashes() because it depends on PHP quote settings!
 460          $s = str_replace("'",  "''", $s);
 461          return $s;
 462      }
 463  }