Search moodle.org's
Developer Documentation
Developer Documentation
- Bug fixes for general core bugs in 3.11.x will end 14 Nov 2022 (12 months plus 6 months extension).
- Bug fixes for security issues in 3.11.x will end 13 Nov 2023 (18 months plus 12 months extension).
- PHP version: minimum PHP 7.3.0 Note: minimum PHP version has increased since Moodle 3.10. PHP 7.4.x is supported too.
/lib/ddl/ -> mssql_sql_generator.php (source)
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 * MSSQL specific SQL code generator. 19 * 20 * @package core_ddl 21 * @copyright 1999 onwards Martin Dougiamas http://dougiamas.com 22 * 2001-3001 Eloy Lafuente (stronk7) http://contiento.com 23 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later 24 */ 25 26 defined('MOODLE_INTERNAL') || die(); 27 28 require_once($CFG->libdir.'/ddl/sql_generator.php'); 29 30 /** 31 * This class generate SQL code to be used against MSSQL 32 * It extends XMLDBgenerator so everything can be 33 * overridden as needed to generate correct SQL. 34 * 35 * @package core_ddl 36 * @copyright 1999 onwards Martin Dougiamas http://dougiamas.com 37 * 2001-3001 Eloy Lafuente (stronk7) http://contiento.com 38 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later 39 */ 40 class mssql_sql_generator extends sql_generator { 41 42 // Only set values that are different from the defaults present in XMLDBgenerator 43 44 /** @var string To be automatically added at the end of each statement. */ 45 public $statement_end = "\ngo"; 46 47 /** @var string Proper type for NUMBER(x) in this DB. */ 48 public $number_type = 'DECIMAL'; 49 50 /** @var string To define the default to set for NOT NULLs CHARs without default (null=do nothing).*/ 51 public $default_for_char = ''; 52 53 /** 54 * @var bool To force the generator if NULL clauses must be specified. It shouldn't be necessary. 55 * note: some mssql drivers require them or everything is created as NOT NULL :-( 56 */ 57 public $specify_nulls = true; 58 59 /** @var bool True if the generator needs to add extra code to generate the sequence fields.*/ 60 public $sequence_extra_code = false; 61 62 /** @var string The particular name for inline sequences in this generator.*/ 63 public $sequence_name = 'IDENTITY(1,1)'; 64 65 /** @var bool To avoid outputting the rest of the field specs, leaving only the name and the sequence_name returned.*/ 66 public $sequence_only = false; 67 68 /** @var bool True if the generator needs to add code for table comments.*/ 69 public $add_table_comments = false; 70 71 /** @var string Characters to be used as concatenation operator.*/ 72 public $concat_character = '+'; 73 74 /** @var string SQL sentence to rename one table, both 'OLDNAME' and 'NEWNAME' keywords are dynamically replaced.*/ 75 public $rename_table_sql = "sp_rename 'OLDNAME', 'NEWNAME'"; 76 77 /** @var string SQL sentence to rename one column where 'TABLENAME', 'OLDFIELDNAME' and 'NEWFIELDNAME' keywords are dynamically replaced.*/ 78 public $rename_column_sql = "sp_rename 'TABLENAME.OLDFIELDNAME', 'NEWFIELDNAME', 'COLUMN'"; 79 80 /** @var string SQL sentence to drop one index where 'TABLENAME', 'INDEXNAME' keywords are dynamically replaced.*/ 81 public $drop_index_sql = 'DROP INDEX TABLENAME.INDEXNAME'; 82 83 /** @var string SQL sentence to rename one index where 'TABLENAME', 'OLDINDEXNAME' and 'NEWINDEXNAME' are dynamically replaced.*/ 84 public $rename_index_sql = "sp_rename 'TABLENAME.OLDINDEXNAME', 'NEWINDEXNAME', 'INDEX'"; 85 86 /** @var string SQL sentence to rename one key 'TABLENAME', 'OLDKEYNAME' and 'NEWKEYNAME' are dynamically replaced.*/ 87 public $rename_key_sql = null; 88 89 /** 90 * Reset a sequence to the id field of a table. 91 * 92 * @param xmldb_table|string $table name of table or the table object. 93 * @return array of sql statements 94 */ 95 public function getResetSequenceSQL($table) { 96 97 if (is_string($table)) { 98 $table = new xmldb_table($table); 99 } 100 101 $value = (int)$this->mdb->get_field_sql('SELECT MAX(id) FROM {'. $table->getName() . '}'); 102 $sqls = array(); 103 104 // MSSQL has one non-consistent behavior to create the first identity value, depending 105 // if the table has been truncated or no. If you are really interested, you can find the 106 // whole description of the problem at: 107 // http://www.justinneff.com/archive/tag/dbcc-checkident 108 if ($value == 0) { 109 // truncate to get consistent result from reseed 110 $sqls[] = "TRUNCATE TABLE " . $this->getTableName($table); 111 $value = 1; 112 } 113 114 // From http://msdn.microsoft.com/en-us/library/ms176057.aspx 115 $sqls[] = "DBCC CHECKIDENT ('" . $this->getTableName($table) . "', RESEED, $value)"; 116 return $sqls; 117 } 118 119 /** 120 * Given one xmldb_table, returns it's correct name, depending of all the parametrization 121 * Overridden to allow change of names in temp tables 122 * 123 * @param xmldb_table table whose name we want 124 * @param boolean to specify if the name must be quoted (if reserved word, only!) 125 * @return string the correct name of the table 126 */ 127 public function getTableName(xmldb_table $xmldb_table, $quoted=true) { 128 // Get the name, supporting special mssql names for temp tables 129 if ($this->temptables->is_temptable($xmldb_table->getName())) { 130 $tablename = $this->temptables->get_correct_name($xmldb_table->getName()); 131 } else { 132 $tablename = $this->prefix . $xmldb_table->getName(); 133 } 134 135 // Apply quotes optionally 136 if ($quoted) { 137 $tablename = $this->getEncQuoted($tablename); 138 } 139 140 return $tablename; 141 } 142 143 public function getCreateIndexSQL($xmldb_table, $xmldb_index) { 144 list($indexsql) = parent::getCreateIndexSQL($xmldb_table, $xmldb_index); 145 146 // Unique indexes need to work-around non-standard SQL server behaviour. 147 if ($xmldb_index->getUnique()) { 148 // Find any nullable columns. We need to add a 149 // WHERE field IS NOT NULL to the index definition for each one. 150 // 151 // For example if you have a unique index on the three columns 152 // (required, option1, option2) where the first one is non-null, 153 // and the others nullable, then the SQL will end up as 154 // 155 // CREATE UNIQUE INDEX index_name ON table_name (required, option1, option2) 156 // WHERE option1 IS NOT NULL AND option2 IS NOT NULL 157 // 158 // The first line comes from parent calls above. The WHERE is added below. 159 $extraconditions = []; 160 foreach ($this->get_nullable_fields_in_index($xmldb_table, $xmldb_index) as $fieldname) { 161 $extraconditions[] = $this->getEncQuoted($fieldname) . 162 ' IS NOT NULL'; 163 } 164 165 if ($extraconditions) { 166 $indexsql .= ' WHERE ' . implode(' AND ', $extraconditions); 167 } 168 } 169 170 return [$indexsql]; 171 } 172 173 /** 174 * Given one correct xmldb_table, returns the SQL statements 175 * to create temporary table (inside one array). 176 * 177 * @param xmldb_table $xmldb_table The xmldb_table object instance. 178 * @return array of sql statements 179 */ 180 public function getCreateTempTableSQL($xmldb_table) { 181 $this->temptables->add_temptable($xmldb_table->getName()); 182 $sqlarr = $this->getCreateTableSQL($xmldb_table); 183 return $sqlarr; 184 } 185 186 /** 187 * Given one XMLDB Type, length and decimals, returns the DB proper SQL type. 188 * 189 * @param int $xmldb_type The xmldb_type defined constant. XMLDB_TYPE_INTEGER and other XMLDB_TYPE_* constants. 190 * @param int $xmldb_length The length of that data type. 191 * @param int $xmldb_decimals The decimal places of precision of the data type. 192 * @return string The DB defined data type. 193 */ 194 public function getTypeSQL($xmldb_type, $xmldb_length=null, $xmldb_decimals=null) { 195 196 switch ($xmldb_type) { 197 case XMLDB_TYPE_INTEGER: // From http://msdn.microsoft.com/library/en-us/tsqlref/ts_da-db_7msw.asp?frame=true 198 if (empty($xmldb_length)) { 199 $xmldb_length = 10; 200 } 201 if ($xmldb_length > 9) { 202 $dbtype = 'BIGINT'; 203 } else if ($xmldb_length > 4) { 204 $dbtype = 'INTEGER'; 205 } else { 206 $dbtype = 'SMALLINT'; 207 } 208 break; 209 case XMLDB_TYPE_NUMBER: 210 $dbtype = $this->number_type; 211 if (!empty($xmldb_length)) { 212 $dbtype .= '(' . $xmldb_length; 213 if (!empty($xmldb_decimals)) { 214 $dbtype .= ',' . $xmldb_decimals; 215 } 216 $dbtype .= ')'; 217 } 218 break; 219 case XMLDB_TYPE_FLOAT: 220 $dbtype = 'FLOAT'; 221 if (!empty($xmldb_decimals)) { 222 if ($xmldb_decimals < 6) { 223 $dbtype = 'REAL'; 224 } 225 } 226 break; 227 case XMLDB_TYPE_CHAR: 228 $dbtype = 'NVARCHAR'; 229 if (empty($xmldb_length)) { 230 $xmldb_length='255'; 231 } 232 $dbtype .= '(' . $xmldb_length . ') COLLATE database_default'; 233 break; 234 case XMLDB_TYPE_TEXT: 235 $dbtype = 'NVARCHAR(MAX) COLLATE database_default'; 236 break; 237 case XMLDB_TYPE_BINARY: 238 $dbtype = 'VARBINARY(MAX)'; 239 break; 240 case XMLDB_TYPE_DATETIME: 241 $dbtype = 'DATETIME'; 242 break; 243 } 244 return $dbtype; 245 } 246 247 /** 248 * Given one xmldb_table and one xmldb_field, return the SQL statements needed to drop the field from the table. 249 * MSSQL overwrites the standard sentence because it needs to do some extra work dropping the default and 250 * check constraints 251 * 252 * @param xmldb_table $xmldb_table The table related to $xmldb_field. 253 * @param xmldb_field $xmldb_field The instance of xmldb_field to create the SQL from. 254 * @return array The SQL statement for dropping a field from the table. 255 */ 256 public function getDropFieldSQL($xmldb_table, $xmldb_field) { 257 $results = array(); 258 259 // Get the quoted name of the table and field 260 $tablename = $this->getTableName($xmldb_table); 261 $fieldname = $this->getEncQuoted($xmldb_field->getName()); 262 263 // Look for any default constraint in this field and drop it 264 if ($defaultname = $this->getDefaultConstraintName($xmldb_table, $xmldb_field)) { 265 $results[] = 'ALTER TABLE ' . $tablename . ' DROP CONSTRAINT ' . $defaultname; 266 } 267 268 // Build the standard alter table drop column 269 $results[] = 'ALTER TABLE ' . $tablename . ' DROP COLUMN ' . $fieldname; 270 271 return $results; 272 } 273 274 /** 275 * Given one correct xmldb_field and the new name, returns the SQL statements 276 * to rename it (inside one array). 277 * 278 * MSSQL is special, so we overload the function here. It needs to 279 * drop the constraints BEFORE renaming the field 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 get the renamed field from. 283 * @param string $newname The new name to rename the field to. 284 * @return array The SQL statements for renaming the field. 285 */ 286 public function getRenameFieldSQL($xmldb_table, $xmldb_field, $newname) { 287 288 $results = array(); //Array where all the sentences will be stored 289 290 // Although this is checked in database_manager::rename_field() - double check 291 // that we aren't trying to rename one "id" field. Although it could be 292 // implemented (if adding the necessary code to rename sequences, defaults, 293 // triggers... and so on under each getRenameFieldExtraSQL() function, it's 294 // better to forbid it, mainly because this field is the default PK and 295 // in the future, a lot of FKs can be pointing here. So, this field, more 296 // or less, must be considered immutable! 297 if ($xmldb_field->getName() == 'id') { 298 return array(); 299 } 300 301 // We can't call to standard (parent) getRenameFieldSQL() function since it would enclose the field name 302 // with improper quotes in MSSQL: here, we use a stored procedure to rename the field i.e. a column object; 303 // we need to take care about how this stored procedure expects parameters to be "qualified". 304 $rename = str_replace('TABLENAME', $this->getTableName($xmldb_table), $this->rename_column_sql); 305 // Qualifying the column object could require brackets: use them, regardless the column name not being a reserved word. 306 $rename = str_replace('OLDFIELDNAME', '[' . $xmldb_field->getName() . ']', $rename); 307 // The new field name should be passed as the actual name, w/o any quote. 308 $rename = str_replace('NEWFIELDNAME', $newname, $rename); 309 310 $results[] = $rename; 311 312 return $results; 313 } 314 315 /** 316 * Returns the code (array of statements) needed to execute extra statements on table rename. 317 * 318 * @param xmldb_table $xmldb_table The xmldb_table object instance. 319 * @param string $newname The new name for the table. 320 * @return array Array of extra SQL statements to rename a table. 321 */ 322 public function getRenameTableExtraSQL($xmldb_table, $newname) { 323 324 $results = array(); 325 326 return $results; 327 } 328 329 /** 330 * Given one xmldb_table and one xmldb_field, return the SQL statements needed to alter the field in the table. 331 * 332 * @param xmldb_table $xmldb_table The table related to $xmldb_field. 333 * @param xmldb_field $xmldb_field The instance of xmldb_field to create the SQL from. 334 * @param string $skip_type_clause The type clause on alter columns, NULL by default. 335 * @param string $skip_default_clause The default clause on alter columns, NULL by default. 336 * @param string $skip_notnull_clause The null/notnull clause on alter columns, NULL by default. 337 * @return string The field altering SQL statement. 338 */ 339 public function getAlterFieldSQL($xmldb_table, $xmldb_field, $skip_type_clause = NULL, $skip_default_clause = NULL, $skip_notnull_clause = NULL) { 340 341 $results = array(); // To store all the needed SQL commands 342 343 // Get the quoted name of the table and field 344 $tablename = $xmldb_table->getName(); 345 $fieldname = $xmldb_field->getName(); 346 347 // Take a look to field metadata 348 $meta = $this->mdb->get_columns($tablename); 349 $metac = $meta[$fieldname]; 350 $oldmetatype = $metac->meta_type; 351 352 $oldlength = $metac->max_length; 353 $olddecimals = empty($metac->scale) ? null : $metac->scale; 354 $oldnotnull = empty($metac->not_null) ? false : $metac->not_null; 355 //$olddefault = empty($metac->has_default) ? null : strtok($metac->default_value, ':'); 356 357 $typechanged = true; //By default, assume that the column type has changed 358 $lengthchanged = true; //By default, assume that the column length has changed 359 360 // Detect if we are changing the type of the column 361 if (($xmldb_field->getType() == XMLDB_TYPE_INTEGER && $oldmetatype == 'I') || 362 ($xmldb_field->getType() == XMLDB_TYPE_NUMBER && $oldmetatype == 'N') || 363 ($xmldb_field->getType() == XMLDB_TYPE_FLOAT && $oldmetatype == 'F') || 364 ($xmldb_field->getType() == XMLDB_TYPE_CHAR && $oldmetatype == 'C') || 365 ($xmldb_field->getType() == XMLDB_TYPE_TEXT && $oldmetatype == 'X') || 366 ($xmldb_field->getType() == XMLDB_TYPE_BINARY && $oldmetatype == 'B')) { 367 $typechanged = false; 368 } 369 370 // If the new field (and old) specs are for integer, let's be a bit more specific differentiating 371 // types of integers. Else, some combinations can cause things like MDL-21868 372 if ($xmldb_field->getType() == XMLDB_TYPE_INTEGER && $oldmetatype == 'I') { 373 if ($xmldb_field->getLength() > 9) { // Convert our new lenghts to detailed meta types 374 $newmssqlinttype = 'I8'; 375 } else if ($xmldb_field->getLength() > 4) { 376 $newmssqlinttype = 'I'; 377 } else { 378 $newmssqlinttype = 'I2'; 379 } 380 if ($metac->type == 'bigint') { // Convert current DB type to detailed meta type (our metatype is not enough!) 381 $oldmssqlinttype = 'I8'; 382 } else if ($metac->type == 'smallint') { 383 $oldmssqlinttype = 'I2'; 384 } else { 385 $oldmssqlinttype = 'I'; 386 } 387 if ($newmssqlinttype != $oldmssqlinttype) { // Compare new and old meta types 388 $typechanged = true; // Change in meta type means change in type at all effects 389 } 390 } 391 392 // Detect if we are changing the length of the column, not always necessary to drop defaults 393 // if only the length changes, but it's safe to do it always 394 if ($xmldb_field->getLength() == $oldlength) { 395 $lengthchanged = false; 396 } 397 398 // If type or length have changed drop the default if exists 399 if ($typechanged || $lengthchanged) { 400 $results = $this->getDropDefaultSQL($xmldb_table, $xmldb_field); 401 } 402 403 // Some changes of type require multiple alter statements, because mssql lacks direct implicit cast between such types 404 // Here it is the matrix: http://msdn.microsoft.com/en-us/library/ms187928(SQL.90).aspx 405 // Going to store such intermediate alters in array of objects, storing all the info needed 406 $multiple_alter_stmt = array(); 407 $targettype = $xmldb_field->getType(); 408 409 if ($targettype == XMLDB_TYPE_TEXT && $oldmetatype == 'I') { // integer to text 410 $multiple_alter_stmt[0] = new stdClass; // needs conversion to varchar 411 $multiple_alter_stmt[0]->type = XMLDB_TYPE_CHAR; 412 $multiple_alter_stmt[0]->length = 255; 413 414 } else if ($targettype == XMLDB_TYPE_TEXT && $oldmetatype == 'N') { // decimal to text 415 $multiple_alter_stmt[0] = new stdClass; // needs conversion to varchar 416 $multiple_alter_stmt[0]->type = XMLDB_TYPE_CHAR; 417 $multiple_alter_stmt[0]->length = 255; 418 419 } else if ($targettype == XMLDB_TYPE_TEXT && $oldmetatype == 'F') { // float to text 420 $multiple_alter_stmt[0] = new stdClass; // needs conversion to varchar 421 $multiple_alter_stmt[0]->type = XMLDB_TYPE_CHAR; 422 $multiple_alter_stmt[0]->length = 255; 423 424 } else if ($targettype == XMLDB_TYPE_INTEGER && $oldmetatype == 'X') { // text to integer 425 $multiple_alter_stmt[0] = new stdClass; // needs conversion to varchar 426 $multiple_alter_stmt[0]->type = XMLDB_TYPE_CHAR; 427 $multiple_alter_stmt[0]->length = 255; 428 $multiple_alter_stmt[1] = new stdClass; // and also needs conversion to decimal 429 $multiple_alter_stmt[1]->type = XMLDB_TYPE_NUMBER; // without decimal positions 430 $multiple_alter_stmt[1]->length = 10; 431 432 } else if ($targettype == XMLDB_TYPE_NUMBER && $oldmetatype == 'X') { // text to decimal 433 $multiple_alter_stmt[0] = new stdClass; // needs conversion to varchar 434 $multiple_alter_stmt[0]->type = XMLDB_TYPE_CHAR; 435 $multiple_alter_stmt[0]->length = 255; 436 437 } else if ($targettype == XMLDB_TYPE_FLOAT && $oldmetatype == 'X') { // text to float 438 $multiple_alter_stmt[0] = new stdClass; // needs conversion to varchar 439 $multiple_alter_stmt[0]->type = XMLDB_TYPE_CHAR; 440 $multiple_alter_stmt[0]->length = 255; 441 } 442 443 // Just prevent default clauses in this type of sentences for mssql and launch the parent one 444 if (empty($multiple_alter_stmt)) { // Direct implicit conversion allowed, launch it 445 $results = array_merge($results, parent::getAlterFieldSQL($xmldb_table, $xmldb_field, NULL, true, NULL)); 446 447 } else { // Direct implicit conversion forbidden, use the intermediate ones 448 $final_type = $xmldb_field->getType(); // Save final type and length 449 $final_length = $xmldb_field->getLength(); 450 foreach ($multiple_alter_stmt as $alter) { 451 $xmldb_field->setType($alter->type); // Put our intermediate type and length and alter to it 452 $xmldb_field->setLength($alter->length); 453 $results = array_merge($results, parent::getAlterFieldSQL($xmldb_table, $xmldb_field, NULL, true, NULL)); 454 } 455 $xmldb_field->setType($final_type); // Set the final type and length and alter to it 456 $xmldb_field->setLength($final_length); 457 $results = array_merge($results, parent::getAlterFieldSQL($xmldb_table, $xmldb_field, NULL, true, NULL)); 458 } 459 460 // Finally, process the default clause to add it back if necessary 461 if ($typechanged || $lengthchanged) { 462 $results = array_merge($results, $this->getCreateDefaultSQL($xmldb_table, $xmldb_field)); 463 } 464 465 // Return results 466 return $results; 467 } 468 469 /** 470 * Given one xmldb_table and one xmldb_field, return the SQL statements needed to modify the default of the field in the table. 471 * 472 * @param xmldb_table $xmldb_table The table related to $xmldb_field. 473 * @param xmldb_field $xmldb_field The instance of xmldb_field to get the modified default value from. 474 * @return array The SQL statement for modifying the default value. 475 */ 476 public function getModifyDefaultSQL($xmldb_table, $xmldb_field) { 477 // MSSQL is a bit special with default constraints because it implements them as external constraints so 478 // normal ALTER TABLE ALTER COLUMN don't work to change defaults. Because this, we have this method overloaded here 479 480 $results = array(); 481 482 // Decide if we are going to create/modify or to drop the default 483 if ($xmldb_field->getDefault() === null) { 484 $results = $this->getDropDefaultSQL($xmldb_table, $xmldb_field); //Drop but, under some circumstances, re-enable 485 $default_clause = $this->getDefaultClause($xmldb_field); 486 if ($default_clause) { //If getDefaultClause() it must have one default, create it 487 $results = array_merge($results, $this->getCreateDefaultSQL($xmldb_table, $xmldb_field)); //Create/modify 488 } 489 } else { 490 $results = $this->getDropDefaultSQL($xmldb_table, $xmldb_field); //Drop (only if exists) 491 $results = array_merge($results, $this->getCreateDefaultSQL($xmldb_table, $xmldb_field)); //Create/modify 492 } 493 494 return $results; 495 } 496 497 /** 498 * Given one xmldb_table and one xmldb_field, return the SQL statements needed to add its default 499 * (usually invoked from getModifyDefaultSQL() 500 * 501 * @param xmldb_table $xmldb_table The xmldb_table object instance. 502 * @param xmldb_field $xmldb_field The xmldb_field object instance. 503 * @return array Array of SQL statements to create a field's default. 504 */ 505 public function getCreateDefaultSQL($xmldb_table, $xmldb_field) { 506 // MSSQL is a bit special and it requires the corresponding DEFAULT CONSTRAINT to be dropped 507 508 $results = array(); 509 510 // Get the quoted name of the table and field 511 $tablename = $this->getTableName($xmldb_table); 512 $fieldname = $this->getEncQuoted($xmldb_field->getName()); 513 514 // Now, check if, with the current field attributes, we have to build one default 515 $default_clause = $this->getDefaultClause($xmldb_field); 516 if ($default_clause) { 517 // We need to build the default (Moodle) default, so do it 518 $sql = 'ALTER TABLE ' . $tablename . ' ADD' . $default_clause . ' FOR ' . $fieldname; 519 $results[] = $sql; 520 } 521 522 return $results; 523 } 524 525 /** 526 * Given one xmldb_table and one xmldb_field, return the SQL statements needed to drop its default 527 * (usually invoked from getModifyDefaultSQL() 528 * 529 * Note that this method may be dropped in future. 530 * 531 * @param xmldb_table $xmldb_table The xmldb_table object instance. 532 * @param xmldb_field $xmldb_field The xmldb_field object instance. 533 * @return array Array of SQL statements to create a field's default. 534 * 535 * @todo MDL-31147 Moodle 2.1 - Drop getDropDefaultSQL() 536 */ 537 public function getDropDefaultSQL($xmldb_table, $xmldb_field) { 538 // MSSQL is a bit special and it requires the corresponding DEFAULT CONSTRAINT to be dropped 539 540 $results = array(); 541 542 // Get the quoted name of the table and field 543 $tablename = $this->getTableName($xmldb_table); 544 $fieldname = $this->getEncQuoted($xmldb_field->getName()); 545 546 // Look for the default contraint and, if found, drop it 547 if ($defaultname = $this->getDefaultConstraintName($xmldb_table, $xmldb_field)) { 548 $results[] = 'ALTER TABLE ' . $tablename . ' DROP CONSTRAINT ' . $defaultname; 549 } 550 551 return $results; 552 } 553 554 /** 555 * Given one xmldb_table and one xmldb_field, returns the name of its default constraint in DB 556 * or false if not found 557 * This function should be considered internal and never used outside from generator 558 * 559 * @param xmldb_table $xmldb_table The xmldb_table object instance. 560 * @param xmldb_field $xmldb_field The xmldb_field object instance. 561 * @return mixed 562 */ 563 protected function getDefaultConstraintName($xmldb_table, $xmldb_field) { 564 565 // Get the quoted name of the table and field 566 $tablename = $this->getTableName($xmldb_table); 567 $fieldname = $xmldb_field->getName(); 568 569 // Look for any default constraint in this field and drop it 570 if ($default = $this->mdb->get_record_sql("SELECT object_id, object_name(default_object_id) AS defaultconstraint 571 FROM sys.columns 572 WHERE object_id = object_id(?) 573 AND name = ?", array($tablename, $fieldname))) { 574 return $default->defaultconstraint; 575 } else { 576 return false; 577 } 578 } 579 580 /** 581 * Given three strings (table name, list of fields (comma separated) and suffix), 582 * create the proper object name quoting it if necessary. 583 * 584 * IMPORTANT: This function must be used to CALCULATE NAMES of objects TO BE CREATED, 585 * NEVER TO GUESS NAMES of EXISTING objects!!! 586 * 587 * IMPORTANT: We are overriding this function for the MSSQL generator because objects 588 * belonging to temporary tables aren't searchable in the catalog neither in information 589 * schema tables. So, for temporary tables, we are going to add 4 randomly named "virtual" 590 * fields, so the generated names won't cause concurrency problems. Really nasty hack, 591 * but the alternative involves modifying all the creation table code to avoid naming 592 * constraints for temp objects and that will dupe a lot of code. 593 * 594 * @param string $tablename The table name. 595 * @param string $fields A list of comma separated fields. 596 * @param string $suffix A suffix for the object name. 597 * @return string Object's name. 598 */ 599 public function getNameForObject($tablename, $fields, $suffix='') { 600 if ($this->temptables->is_temptable($tablename)) { // Is temp table, inject random field names 601 $random = strtolower(random_string(12)); // 12cc to be split in 4 parts 602 $fields = $fields . ', ' . implode(', ', str_split($random, 3)); 603 } 604 return parent::getNameForObject($tablename, $fields, $suffix); // Delegate to parent (common) algorithm 605 } 606 607 /** 608 * Given one object name and it's type (pk, uk, fk, ck, ix, uix, seq, trg). 609 * 610 * (MySQL requires the whole xmldb_table object to be specified, so we add it always) 611 * 612 * This is invoked from getNameForObject(). 613 * Only some DB have this implemented. 614 * 615 * @param string $object_name The object's name to check for. 616 * @param string $type The object's type (pk, uk, fk, ck, ix, uix, seq, trg). 617 * @param string $table_name The table's name to check in 618 * @return bool If such name is currently in use (true) or no (false) 619 */ 620 public function isNameInUse($object_name, $type, $table_name) { 621 switch($type) { 622 case 'seq': 623 case 'trg': 624 case 'pk': 625 case 'uk': 626 case 'fk': 627 case 'ck': 628 if ($check = $this->mdb->get_records_sql("SELECT name 629 FROM sys.objects 630 WHERE lower(name) = ?", array(strtolower($object_name)))) { 631 return true; 632 } 633 break; 634 case 'ix': 635 case 'uix': 636 if ($check = $this->mdb->get_records_sql("SELECT name 637 FROM sys.indexes 638 WHERE lower(name) = ?", array(strtolower($object_name)))) { 639 return true; 640 } 641 break; 642 } 643 return false; //No name in use found 644 } 645 646 /** 647 * Returns the code (array of statements) needed to add one comment to the table. 648 * 649 * @param xmldb_table $xmldb_table The xmldb_table object instance. 650 * @return array Array of SQL statements to add one comment to the table. 651 */ 652 public function getCommentSQL($xmldb_table) { 653 return array(); 654 } 655 656 /** 657 * Adds slashes to string. 658 * @param string $s 659 * @return string The escaped string. 660 */ 661 public function addslashes($s) { 662 // do not use php addslashes() because it depends on PHP quote settings! 663 $s = str_replace("'", "''", $s); 664 return $s; 665 } 666 667 /** 668 * Returns an array of reserved words (lowercase) for this DB 669 * @return array An array of database specific reserved words 670 */ 671 public static function getReservedWords() { 672 // This file contains the reserved words for MSSQL databases 673 // from http://msdn2.microsoft.com/en-us/library/ms189822.aspx 674 // Should be identical to sqlsrv_native_moodle_database::$reservewords. 675 $reserved_words = array ( 676 "add", "all", "alter", "and", "any", "as", "asc", "authorization", "avg", "backup", "begin", "between", "break", 677 "browse", "bulk", "by", "cascade", "case", "check", "checkpoint", "close", "clustered", "coalesce", "collate", "column", 678 "commit", "committed", "compute", "confirm", "constraint", "contains", "containstable", "continue", "controlrow", 679 "convert", "count", "create", "cross", "current", "current_date", "current_time", "current_timestamp", "current_user", 680 "cursor", "database", "dbcc", "deallocate", "declare", "default", "delete", "deny", "desc", "disk", "distinct", 681 "distributed", "double", "drop", "dummy", "dump", "else", "end", "errlvl", "errorexit", "escape", "except", "exec", 682 "execute", "exists", "exit", "external", "fetch", "file", "fillfactor", "floppy", "for", "foreign", "freetext", 683 "freetexttable", "from", "full", "function", "goto", "grant", "group", "having", "holdlock", "identity", 684 "identity_insert", "identitycol", "if", "in", "index", "inner", "insert", "intersect", "into", "is", "isolation", 685 "join", "key", "kill", "left", "level", "like", "lineno", "load", "max", "merge", "min", "mirrorexit", "national", 686 "nocheck", "nonclustered", "not", "null", "nullif", "of", "off", "offsets", "on", "once", "only", "open", 687 "opendatasource", "openquery", "openrowset", "openxml", "option", "or", "order", "outer", "over", "percent", "perm", 688 "permanent", "pipe", "pivot", "plan", "precision", "prepare", "primary", "print", "privileges", "proc", "procedure", 689 "processexit", "public", "raiserror", "read", "readtext", "reconfigure", "references", "repeatable", "replication", 690 "restore", "restrict", "return", "revert", "revoke", "right", "rollback", "rowcount", "rowguidcol", "rule", "save", 691 "schema", "securityaudit", "select", "semantickeyphrasetable", "semanticsimilaritydetailstable", 692 "semanticsimilaritytable", "serializable", "session_user", "set", "setuser", "shutdown", "some", "statistics", "sum", 693 "system_user", "table", "tablesample", "tape", "temp", "temporary", "textsize", "then", "to", "top", "tran", 694 "transaction", "trigger", "truncate", "try_convert", "tsequal", "uncommitted", "union", "unique", "unpivot", "update", 695 "updatetext", "use", "user", "values", "varying", "view", "waitfor", "when", "where", "while", "with", "within group", 696 "work", "writetext" 697 ); 698 return $reserved_words; 699 } 700 }
title
Description
Body
title
Description
Body
title
Description
Body
title
Body
