[Summary view] [Print] [Text view]

   1  <?php // $Id: mysql.class.php,v 1.38 2007/10/10 05:25:24 nicolasconnault Exp $
   2  
   3  ///////////////////////////////////////////////////////////////////////////
   4  //                                                                       //
   5  // NOTICE OF COPYRIGHT                                                   //
   6  //                                                                       //
   7  // Moodle - Modular Object-Oriented Dynamic Learning Environment         //
   8  //          http://moodle.com                                            //
   9  //                                                                       //
  10  // Copyright (C) 1999 onwards Martin Dougiamas        http://dougiamas.com  //
  11  //           (C) 2001-3001 Eloy Lafuente (stronk7) http://contiento.com  //
  12  //                                                                       //
  13  // This program is free software; you can redistribute it and/or modify  //
  14  // it under the terms of the GNU General Public License as published by  //
  15  // the Free Software Foundation; either version 2 of the License, or     //
  16  // (at your option) any later version.                                   //
  17  //                                                                       //
  18  // This program is distributed in the hope that it will be useful,       //
  19  // but WITHOUT ANY WARRANTY; without even the implied warranty of        //
  20  // MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the         //
  21  // GNU General Public License for more details:                          //
  22  //                                                                       //
  23  //          http://www.gnu.org/copyleft/gpl.html                         //
  24  //                                                                       //
  25  ///////////////////////////////////////////////////////////////////////////
  26  
  27  /// This class generate SQL code to be used against MySQL
  28  /// It extends XMLDBgenerator so everything can be
  29  /// overriden as needed to generate correct SQL.
  30  
  31  class XMLDBmysql extends XMLDBGenerator {
  32  
  33  /// Only set values that are different from the defaults present in XMLDBgenerator
  34  
  35      var $quote_string = '`';   // String used to quote names
  36  
  37      var $default_for_char = '';      // To define the default to set for NOT NULLs CHARs without default (null=do nothing)
  38  
  39      var $drop_default_clause_required = true; //To specify if the generator must use some DEFAULT clause to drop defaults
  40      var $drop_default_clause = 'NULL'; //The DEFAULT clause required to drop defaults
  41  
  42      var $primary_key_name = ''; //To force primary key names to one string (null=no force)
  43  
  44      var $drop_primary_key = 'ALTER TABLE TABLENAME DROP PRIMARY KEY'; // Template to drop PKs
  45                  // with automatic replace for TABLENAME and KEYNAME
  46  
  47      var $drop_unique_key = 'ALTER TABLE TABLENAME DROP KEY KEYNAME'; // Template to drop UKs
  48                  // with automatic replace for TABLENAME and KEYNAME
  49  
  50      var $drop_foreign_key = 'ALTER TABLE TABLENAME DROP FOREIGN KEY KEYNAME'; // Template to drop FKs
  51                  // with automatic replace for TABLENAME and KEYNAME
  52  
  53      var $sequence_extra_code = false; //Does the generator need to add extra code to generate the sequence fields
  54      var $sequence_name = 'auto_increment'; //Particular name for inline sequences in this generator
  55  
  56      var $enum_extra_code = false; //Does the generator need to add extra code to generate code for the enums in the table
  57  
  58      var $add_after_clause = true; // Does the generator need to add the after clause for fields
  59  
  60      var $concat_character = null; //Characters to be used as concatenation operator. If not defined
  61                                    //MySQL CONCAT function will be use
  62  
  63      var $alter_column_sql = 'ALTER TABLE TABLENAME MODIFY COLUMN COLUMNSPECS'; //The SQL template to alter columns
  64  
  65      var $drop_index_sql = 'ALTER TABLE TABLENAME DROP INDEX INDEXNAME'; //SQL sentence to drop one index
  66                                                                 //TABLENAME, INDEXNAME are dinamically replaced
  67  
  68      var $rename_index_sql = null; //SQL sentence to rename one index (MySQL doesn't support this!)
  69                                        //TABLENAME, OLDINDEXNAME, NEWINDEXNAME are dinamically replaced
  70  
  71      var $rename_key_sql = null; //SQL sentence to rename one key (MySQL doesn't support this!)
  72                                        //TABLENAME, OLDKEYNAME, NEWKEYNAME are dinamically replaced
  73  
  74      /**
  75       * Creates one new XMLDBmysql
  76       */
  77      function XMLDBmysql() {
  78          parent::XMLDBGenerator();
  79          global $CFG;
  80          $this->prefix = '';
  81          $this->reserved_words = $this->getReservedWords();
  82      }
  83  
  84      /**
  85       * Given one XMLDB Type, lenght and decimals, returns the DB proper SQL type
  86       */
  87      function getTypeSQL ($xmldb_type, $xmldb_length=null, $xmldb_decimals=null) {
  88  
  89          switch ($xmldb_type) {
  90              case XMLDB_TYPE_INTEGER:    // From http://mysql.com/doc/refman/5.0/en/numeric-types.html!
  91                  if (empty($xmldb_length)) {
  92                      $xmldb_length = 10;
  93                  }
  94                  if ($xmldb_length > 9) {
  95                      $dbtype = 'BIGINT';
  96                  } else if ($xmldb_length > 6) {
  97                      $dbtype = 'INT';
  98                  } else if ($xmldb_length > 4) {
  99                      $dbtype = 'MEDIUMINT';
 100                  } else if ($xmldb_length > 2) {
 101                      $dbtype = 'SMALLINT';
 102                  } else {
 103                      $dbtype = 'TINYINT';
 104                  }
 105                  $dbtype .= '(' . $xmldb_length . ')';
 106                  break;
 107              case XMLDB_TYPE_NUMBER:
 108                  $dbtype = $this->number_type;
 109                  if (!empty($xmldb_length)) {
 110                      $dbtype .= '(' . $xmldb_length;
 111                      if (!empty($xmldb_decimals)) {
 112                          $dbtype .= ',' . $xmldb_decimals;
 113                      }
 114                      $dbtype .= ')';
 115                  }
 116                  break;
 117              case XMLDB_TYPE_FLOAT:
 118                  $dbtype = 'DOUBLE';
 119                  if (!empty($xmldb_decimals)) {
 120                      if ($xmldb_decimals < 6) {
 121                          $dbtype = 'FLOAT';
 122                      }
 123                  }
 124                  if (!empty($xmldb_length)) {
 125                      $dbtype .= '(' . $xmldb_length;
 126                      if (!empty($xmldb_decimals)) {
 127                          $dbtype .= ',' . $xmldb_decimals;
 128                      }
 129                      $dbtype .= ')';
 130                  }
 131                  break;
 132              case XMLDB_TYPE_CHAR:
 133                  $dbtype = 'VARCHAR';
 134                  if (empty($xmldb_length)) {
 135                      $xmldb_length='255';
 136                  }
 137                  $dbtype .= '(' . $xmldb_length . ')';
 138                  break;
 139              case XMLDB_TYPE_TEXT:
 140                  if (empty($xmldb_length)) {
 141                      $xmldb_length = 'small';
 142                  }
 143                  if ($xmldb_length == 'small') {
 144                      $dbtype = 'TEXT';
 145                  } else if ($xmldb_length == 'medium') {
 146                      $dbtype = 'MEDIUMTEXT';
 147                  } else {
 148                      $dbtype = 'LONGTEXT';
 149                  }
 150                  break;
 151              case XMLDB_TYPE_BINARY:
 152                  if (empty($xmldb_length)) {
 153                      $xmldb_length = 'small';
 154                  }
 155                  if ($xmldb_length == 'small') {
 156                      $dbtype = 'BLOB';
 157                  } else if ($xmldb_length == 'medium') {
 158                      $dbtype = 'MEDIUMBLOB';
 159                  } else {
 160                      $dbtype = 'LONGBLOB';
 161                  }
 162                  break;
 163              case XMLDB_TYPE_DATETIME:
 164                  $dbtype = 'DATETIME';
 165          }
 166          return $dbtype;
 167      }
 168  
 169      /**
 170       * Given one XMLDBTable and one XMLDBField, return the SQL statements needded to create its enum 
 171       * (usually invoked from getModifyEnumSQL()
 172       */
 173      function getCreateEnumSQL($xmldb_table, $xmldb_field) {
 174      /// For MySQL, just alter the field
 175          return $this->getAlterFieldSQL($xmldb_table, $xmldb_field);
 176      }
 177  
 178      /**     
 179       * Given one XMLDBTable and one XMLDBField, return the SQL statements needded to drop its enum 
 180       * (usually invoked from getModifyEnumSQL()
 181       */
 182      function getDropEnumSQL($xmldb_table, $xmldb_field) {
 183      /// For MySQL, just alter the field
 184          return $this->getAlterFieldSQL($xmldb_table, $xmldb_field);
 185      }
 186  
 187      /**
 188       * Given one XMLDBTable and one XMLDBField, return the SQL statements needded to create its default 
 189       * (usually invoked from getModifyDefaultSQL()
 190       */
 191      function getCreateDefaultSQL($xmldb_table, $xmldb_field) {
 192      /// Just a wrapper over the getAlterFieldSQL() function for MySQL that
 193      /// is capable of handling defaults
 194          return $this->getAlterFieldSQL($xmldb_table, $xmldb_field);
 195      }
 196  
 197      /**
 198       * Given one correct XMLDBField and the new name, returns the SQL statements
 199       * to rename it (inside one array)
 200       * MySQL is pretty diferent from the standard to justify this oveloading
 201       */
 202      function getRenameFieldSQL($xmldb_table, $xmldb_field, $newname) {
 203  
 204          $results = array();  //Array where all the sentences will be stored
 205  
 206      /// Need a clone of xmldb_field to perform the change leaving original unmodified
 207          $xmldb_field_clone = clone($xmldb_field);
 208  
 209      /// Change the name of the field to perform the change
 210          $xmldb_field_clone->setName($xmldb_field_clone->getName() . ' ' . $newname);
 211  
 212          $results[] = 'ALTER TABLE ' . $this->getTableName($xmldb_table) . ' CHANGE ' .
 213                       $this->getFieldSQL($xmldb_field_clone);
 214  
 215          return $results;
 216      }
 217  
 218      /**
 219       * Given one XMLDBTable and one XMLDBField, return the SQL statements needded to drop its default 
 220       * (usually invoked from getModifyDefaultSQL()
 221       */
 222      function getDropDefaultSQL($xmldb_table, $xmldb_field) {
 223      /// Just a wrapper over the getAlterFieldSQL() function for MySQL that
 224      /// is capable of handling defaults
 225          return $this->getAlterFieldSQL($xmldb_table, $xmldb_field);
 226      }
 227  
 228      /**
 229       * Given one XMLDB Field, return its enum SQL
 230       */
 231      function getEnumSQL ($xmldb_field) {
 232          return 'enum(' . implode(', ', $xmldb_field->getEnumValues()) . ')';
 233      }
 234  
 235      /**
 236       * Returns the code (in array) needed to add one comment to the table
 237       */
 238      function getCommentSQL ($xmldb_table) {
 239  
 240          $comment = '';
 241  
 242          if ($xmldb_table->getComment()) {
 243              $comment .= 'ALTER TABLE ' . $this->getTableName($xmldb_table);
 244              $comment .= " COMMENT='" . addslashes(substr($xmldb_table->getComment(), 0, 60)) . "'";
 245          }
 246          return array($comment);
 247      }
 248  
 249      /**
 250       * Given one XMLDBTable returns one array with all the check constrainsts
 251       * in the table (fetched from DB)
 252       * Optionally the function allows one xmldb_field to be specified in
 253       * order to return only the check constraints belonging to one field.
 254       * Each element contains the name of the constraint and its description
 255       * If no check constraints are found, returns an empty array
 256       * MySQL doesn't have check constraints in this implementation, but
 257       * we return them based on the enum fields in the table
 258       */
 259      function getCheckConstraintsFromDB($xmldb_table, $xmldb_field = null) {
 260  
 261          global $db;
 262  
 263          $results = array();
 264  
 265          $tablename = $this->getTableName($xmldb_table);
 266  
 267      /// Fetch all the columns in the table
 268          if ($columns = $db->MetaColumns($tablename)) {
 269          /// Normalize array keys
 270              $columns = array_change_key_case($columns, CASE_LOWER);
 271          /// Iterate over columns searching for enums
 272              foreach ($columns as $key => $column) {
 273              /// Enum found, let's add it to the constraints list
 274                  if (!empty($column->enums)) {
 275                      $result = new object;
 276                      $result->name = $key;
 277                      $result->description = implode(', ', $column->enums);
 278                      $results[$key] = $result;
 279                  }
 280              }
 281          }
 282  
 283      /// Filter by the required field if specified
 284          if ($xmldb_field) {
 285              $filter = $xmldb_field->getName();
 286          /// Check if some of the checks belong to the field (easy under MySQL)
 287              if (array_key_exists($filter, $results)) {
 288                  $results = array($filter => $results[$filter]);
 289              } else {
 290                  $results = array();
 291              }
 292          }
 293  
 294          return $results;
 295      }
 296  
 297      /**
 298       * Given one object name and it's type (pk, uk, fk, ck, ix, uix, seq, trg)
 299       * return if such name is currently in use (true) or no (false)
 300       * (invoked from getNameForObject()
 301       */
 302      function isNameInUse($object_name, $type, $table_name) {
 303  
 304          global $db;
 305  
 306      /// Calculate the real table name
 307          $xmldb_table = new XMLDBTable($table_name);
 308          $tname = $this->getTableName($xmldb_table);
 309          
 310          switch($type) {
 311              case 'ix':
 312              case 'uix':
 313              /// First of all, check table exists
 314                  $metatables = $db->MetaTables();
 315                  $metatables = array_flip($metatables);
 316                  $metatables = array_change_key_case($metatables, CASE_LOWER);
 317                  if (array_key_exists($tname,  $metatables)) {
 318                  /// Fetch all the indexes in the table
 319                      if ($indexes = $db->MetaIndexes($tname)) {
 320                      /// Normalize array keys
 321                          $indexes = array_change_key_case($indexes, CASE_LOWER);
 322                      /// Look for existing index in array
 323                          if (array_key_exists(strtolower($object_name), $indexes)) {
 324                              return true;
 325                          }
 326                      }
 327                  }
 328                  break;
 329          }
 330          return false; //No name in use found
 331      }
 332  
 333  
 334      /**
 335       * Returns an array of reserved words (lowercase) for this DB
 336       */
 337      function getReservedWords() {
 338      /// This file contains the reserved words for MySQL databases
 339      /// from http://dev.mysql.com/doc/refman/5.0/en/reserved-words.html
 340          $reserved_words = array (
 341              'add', 'all', 'alter', 'analyze', 'and', 'as', 'asc',
 342              'asensitive', 'before', 'between', 'bigint', 'binary',
 343              'blob', 'both', 'by', 'call', 'cascade', 'case', 'change',
 344              'char', 'character', 'check', 'collate', 'column',
 345              'condition', 'connection', 'constraint', 'continue',
 346              'convert', 'create', 'cross', 'current_date', 'current_time',
 347              'current_timestamp', 'current_user', 'cursor', 'database',
 348              'databases', 'day_hour', 'day_microsecond',
 349              'day_minute', 'day_second', 'dec', 'decimal', 'declare',
 350              'default', 'delayed', 'delete', 'desc', 'describe',
 351              'deterministic', 'distinct', 'distinctrow', 'div', 'double',
 352              'drop', 'dual', 'each', 'else', 'elseif', 'enclosed', 'escaped',
 353              'exists', 'exit', 'explain', 'false', 'fetch', 'float', 'float4',
 354              'float8', 'for', 'force', 'foreign', 'from', 'fulltext', 'grant',
 355              'group', 'having', 'high_priority', 'hour_microsecond',
 356              'hour_minute', 'hour_second', 'if', 'ignore', 'in', 'index',
 357              'infile', 'inner', 'inout', 'insensitive', 'insert', 'int', 'int1',
 358              'int2', 'int3', 'int4', 'int8', 'integer', 'interval', 'into', 'is',
 359              'iterate', 'join', 'key', 'keys', 'kill', 'leading', 'leave', 'left',
 360              'like', 'limit', 'lines', 'load', 'localtime', 'localtimestamp',
 361              'lock', 'long', 'longblob', 'longtext', 'loop', 'low_priority',
 362              'match', 'mediumblob', 'mediumint', 'mediumtext',
 363              'middleint', 'minute_microsecond', 'minute_second',
 364              'mod', 'modifies', 'natural', 'not', 'no_write_to_binlog',
 365              'null', 'numeric', 'on', 'optimize', 'option', 'optionally',
 366              'or', 'order', 'out', 'outer', 'outfile', 'precision', 'primary',
 367              'procedure', 'purge', 'raid0', 'read', 'reads', 'real',
 368              'references', 'regexp', 'release', 'rename', 'repeat', 'replace',
 369              'require', 'restrict', 'return', 'revoke', 'right', 'rlike', 'schema',
 370              'schemas', 'second_microsecond', 'select', 'sensitive',
 371              'separator', 'set', 'show', 'smallint', 'soname', 'spatial',
 372              'specific', 'sql', 'sqlexception', 'sqlstate', 'sqlwarning',
 373              'sql_big_result', 'sql_calc_found_rows', 'sql_small_result',
 374              'ssl', 'starting', 'straight_join', 'table', 'terminated', 'then',
 375              'tinyblob', 'tinyint', 'tinytext', 'to', 'trailing', 'trigger', 'true',
 376              'undo', 'union', 'unique', 'unlock', 'unsigned', 'update',
 377              'upgrade', 'usage', 'use', 'using', 'utc_date', 'utc_time',
 378              'utc_timestamp', 'values', 'varbinary', 'varchar', 'varcharacter',
 379              'varying', 'when', 'where', 'while', 'with', 'write', 'x509',
 380              'xor', 'year_month', 'zerofill'
 381          );
 382          return $reserved_words;
 383      }
 384  }
 385  
 386  ?>