| [ Index ] |
PHP Cross Reference of Unnamed Project |
[Summary view] [Print] [Text 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 * 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 /** 144 * Given one correct xmldb_table, returns the SQL statements 145 * to create temporary table (inside one array). 146 * 147 * @param xmldb_table $xmldb_table The xmldb_table object instance. 148 * @return array of sql statements 149 */ 150 public function getCreateTempTableSQL($xmldb_table) { 151 $this->temptables->add_temptable($xmldb_table->getName()); 152 $sqlarr = $this->getCreateTableSQL($xmldb_table); 153 return $sqlarr; 154 } 155 156 /** 157 * Given one correct xmldb_table, returns the SQL statements 158 * to drop it (inside one array). 159 * 160 * @param xmldb_table $xmldb_table The table to drop. 161 * @return array SQL statement(s) for dropping the specified table. 162 */ 163 public function getDropTableSQL($xmldb_table) { 164 $sqlarr = parent::getDropTableSQL($xmldb_table); 165 if ($this->temptables->is_temptable($xmldb_table->getName())) { 166 $this->temptables->delete_temptable($xmldb_table->getName()); 167 } 168 return $sqlarr; 169 } 170 171 /** 172 * Given one XMLDB Type, length and decimals, returns the DB proper SQL type. 173 * 174 * @param int $xmldb_type The xmldb_type defined constant. XMLDB_TYPE_INTEGER and other XMLDB_TYPE_* constants. 175 * @param int $xmldb_length The length of that data type. 176 * @param int $xmldb_decimals The decimal places of precision of the data type. 177 * @return string The DB defined data type. 178 */ 179 public function getTypeSQL($xmldb_type, $xmldb_length=null, $xmldb_decimals=null) { 180 181 switch ($xmldb_type) { 182 case XMLDB_TYPE_INTEGER: // From http://msdn.microsoft.com/library/en-us/tsqlref/ts_da-db_7msw.asp?frame=true 183 if (empty($xmldb_length)) { 184 $xmldb_length = 10; 185 } 186 if ($xmldb_length > 9) { 187 $dbtype = 'BIGINT'; 188 } else if ($xmldb_length > 4) { 189 $dbtype = 'INTEGER'; 190 } else { 191 $dbtype = 'SMALLINT'; 192 } 193 break; 194 case XMLDB_TYPE_NUMBER: 195 $dbtype = $this->number_type; 196 if (!empty($xmldb_length)) { 197 // 38 is the max allowed 198 if ($xmldb_length > 38) { 199 $xmldb_length = 38; 200 } 201 $dbtype .= '(' . $xmldb_length; 202 if (!empty($xmldb_decimals)) { 203 $dbtype .= ',' . $xmldb_decimals; 204 } 205 $dbtype .= ')'; 206 } 207 break; 208 case XMLDB_TYPE_FLOAT: 209 $dbtype = 'FLOAT'; 210 if (!empty($xmldb_decimals)) { 211 if ($xmldb_decimals < 6) { 212 $dbtype = 'REAL'; 213 } 214 } 215 break; 216 case XMLDB_TYPE_CHAR: 217 $dbtype = 'NVARCHAR'; 218 if (empty($xmldb_length)) { 219 $xmldb_length='255'; 220 } 221 $dbtype .= '(' . $xmldb_length . ') COLLATE database_default'; 222 break; 223 case XMLDB_TYPE_TEXT: 224 $dbtype = 'NVARCHAR(MAX) COLLATE database_default'; 225 break; 226 case XMLDB_TYPE_BINARY: 227 $dbtype = 'VARBINARY(MAX)'; 228 break; 229 case XMLDB_TYPE_DATETIME: 230 $dbtype = 'DATETIME'; 231 break; 232 } 233 return $dbtype; 234 } 235 236 /** 237 * Given one xmldb_table and one xmldb_field, return the SQL statements needed to drop the field from the table. 238 * MSSQL overwrites the standard sentence because it needs to do some extra work dropping the default and 239 * check constraints 240 * 241 * @param xmldb_table $xmldb_table The table related to $xmldb_field. 242 * @param xmldb_field $xmldb_field The instance of xmldb_field to create the SQL from. 243 * @return array The SQL statement for dropping a field from the table. 244 */ 245 public function getDropFieldSQL($xmldb_table, $xmldb_field) { 246 $results = array(); 247 248 // Get the quoted name of the table and field 249 $tablename = $this->getTableName($xmldb_table); 250 $fieldname = $this->getEncQuoted($xmldb_field->getName()); 251 252 // Look for any default constraint in this field and drop it 253 if ($defaultname = $this->getDefaultConstraintName($xmldb_table, $xmldb_field)) { 254 $results[] = 'ALTER TABLE ' . $tablename . ' DROP CONSTRAINT ' . $defaultname; 255 } 256 257 // Build the standard alter table drop column 258 $results[] = 'ALTER TABLE ' . $tablename . ' DROP COLUMN ' . $fieldname; 259 260 return $results; 261 } 262 263 /** 264 * Given one correct xmldb_field and the new name, returns the SQL statements 265 * to rename it (inside one array). 266 * 267 * MSSQL is special, so we overload the function here. It needs to 268 * drop the constraints BEFORE renaming the field 269 * 270 * @param xmldb_table $xmldb_table The table related to $xmldb_field. 271 * @param xmldb_field $xmldb_field The instance of xmldb_field to get the renamed field from. 272 * @param string $newname The new name to rename the field to. 273 * @return array The SQL statements for renaming the field. 274 */ 275 public function getRenameFieldSQL($xmldb_table, $xmldb_field, $newname) { 276 277 $results = array(); //Array where all the sentences will be stored 278 279 // Although this is checked in database_manager::rename_field() - double check 280 // that we aren't trying to rename one "id" field. Although it could be 281 // implemented (if adding the necessary code to rename sequences, defaults, 282 // triggers... and so on under each getRenameFieldExtraSQL() function, it's 283 // better to forbid it, mainly because this field is the default PK and 284 // in the future, a lot of FKs can be pointing here. So, this field, more 285 // or less, must be considered immutable! 286 if ($xmldb_field->getName() == 'id') { 287 return array(); 288 } 289 290 // Call to standard (parent) getRenameFieldSQL() function 291 $results = array_merge($results, parent::getRenameFieldSQL($xmldb_table, $xmldb_field, $newname)); 292 293 return $results; 294 } 295 296 /** 297 * Returns the code (array of statements) needed to execute extra statements on table rename. 298 * 299 * @param xmldb_table $xmldb_table The xmldb_table object instance. 300 * @param string $newname The new name for the table. 301 * @return array Array of extra SQL statements to rename a table. 302 */ 303 public function getRenameTableExtraSQL($xmldb_table, $newname) { 304 305 $results = array(); 306 307 return $results; 308 } 309 310 /** 311 * Given one xmldb_table and one xmldb_field, return the SQL statements needed to alter the field in the table. 312 * 313 * @param xmldb_table $xmldb_table The table related to $xmldb_field. 314 * @param xmldb_field $xmldb_field The instance of xmldb_field to create the SQL from. 315 * @param string $skip_type_clause The type clause on alter columns, NULL by default. 316 * @param string $skip_default_clause The default clause on alter columns, NULL by default. 317 * @param string $skip_notnull_clause The null/notnull clause on alter columns, NULL by default. 318 * @return string The field altering SQL statement. 319 */ 320 public function getAlterFieldSQL($xmldb_table, $xmldb_field, $skip_type_clause = NULL, $skip_default_clause = NULL, $skip_notnull_clause = NULL) { 321 322 $results = array(); // To store all the needed SQL commands 323 324 // Get the quoted name of the table and field 325 $tablename = $xmldb_table->getName(); 326 $fieldname = $xmldb_field->getName(); 327 328 // Take a look to field metadata 329 $meta = $this->mdb->get_columns($tablename); 330 $metac = $meta[$fieldname]; 331 $oldmetatype = $metac->meta_type; 332 333 $oldlength = $metac->max_length; 334 $olddecimals = empty($metac->scale) ? null : $metac->scale; 335 $oldnotnull = empty($metac->not_null) ? false : $metac->not_null; 336 //$olddefault = empty($metac->has_default) ? null : strtok($metac->default_value, ':'); 337 338 $typechanged = true; //By default, assume that the column type has changed 339 $lengthchanged = true; //By default, assume that the column length has changed 340 341 // Detect if we are changing the type of the column 342 if (($xmldb_field->getType() == XMLDB_TYPE_INTEGER && $oldmetatype == 'I') || 343 ($xmldb_field->getType() == XMLDB_TYPE_NUMBER && $oldmetatype == 'N') || 344 ($xmldb_field->getType() == XMLDB_TYPE_FLOAT && $oldmetatype == 'F') || 345 ($xmldb_field->getType() == XMLDB_TYPE_CHAR && $oldmetatype == 'C') || 346 ($xmldb_field->getType() == XMLDB_TYPE_TEXT && $oldmetatype == 'X') || 347 ($xmldb_field->getType() == XMLDB_TYPE_BINARY && $oldmetatype == 'B')) { 348 $typechanged = false; 349 } 350 351 // If the new field (and old) specs are for integer, let's be a bit more specific differentiating 352 // types of integers. Else, some combinations can cause things like MDL-21868 353 if ($xmldb_field->getType() == XMLDB_TYPE_INTEGER && $oldmetatype == 'I') { 354 if ($xmldb_field->getLength() > 9) { // Convert our new lenghts to detailed meta types 355 $newmssqlinttype = 'I8'; 356 } else if ($xmldb_field->getLength() > 4) { 357 $newmssqlinttype = 'I'; 358 } else { 359 $newmssqlinttype = 'I2'; 360 } 361 if ($metac->type == 'bigint') { // Convert current DB type to detailed meta type (our metatype is not enough!) 362 $oldmssqlinttype = 'I8'; 363 } else if ($metac->type == 'smallint') { 364 $oldmssqlinttype = 'I2'; 365 } else { 366 $oldmssqlinttype = 'I'; 367 } 368 if ($newmssqlinttype != $oldmssqlinttype) { // Compare new and old meta types 369 $typechanged = true; // Change in meta type means change in type at all effects 370 } 371 } 372 373 // Detect if we are changing the length of the column, not always necessary to drop defaults 374 // if only the length changes, but it's safe to do it always 375 if ($xmldb_field->getLength() == $oldlength) { 376 $lengthchanged = false; 377 } 378 379 // If type or length have changed drop the default if exists 380 if ($typechanged || $lengthchanged) { 381 $results = $this->getDropDefaultSQL($xmldb_table, $xmldb_field); 382 } 383 384 // Some changes of type require multiple alter statements, because mssql lacks direct implicit cast between such types 385 // Here it is the matrix: http://msdn.microsoft.com/en-us/library/ms187928(SQL.90).aspx 386 // Going to store such intermediate alters in array of objects, storing all the info needed 387 $multiple_alter_stmt = array(); 388 $targettype = $xmldb_field->getType(); 389 390 if ($targettype == XMLDB_TYPE_TEXT && $oldmetatype == 'I') { // integer to text 391 $multiple_alter_stmt[0] = new stdClass; // needs conversion to varchar 392 $multiple_alter_stmt[0]->type = XMLDB_TYPE_CHAR; 393 $multiple_alter_stmt[0]->length = 255; 394 395 } else if ($targettype == XMLDB_TYPE_TEXT && $oldmetatype == 'N') { // decimal to text 396 $multiple_alter_stmt[0] = new stdClass; // needs conversion to varchar 397 $multiple_alter_stmt[0]->type = XMLDB_TYPE_CHAR; 398 $multiple_alter_stmt[0]->length = 255; 399 400 } else if ($targettype == XMLDB_TYPE_TEXT && $oldmetatype == 'F') { // float to text 401 $multiple_alter_stmt[0] = new stdClass; // needs conversion to varchar 402 $multiple_alter_stmt[0]->type = XMLDB_TYPE_CHAR; 403 $multiple_alter_stmt[0]->length = 255; 404 405 } else if ($targettype == XMLDB_TYPE_INTEGER && $oldmetatype == 'X') { // text to integer 406 $multiple_alter_stmt[0] = new stdClass; // needs conversion to varchar 407 $multiple_alter_stmt[0]->type = XMLDB_TYPE_CHAR; 408 $multiple_alter_stmt[0]->length = 255; 409 $multiple_alter_stmt[1] = new stdClass; // and also needs conversion to decimal 410 $multiple_alter_stmt[1]->type = XMLDB_TYPE_NUMBER; // without decimal positions 411 $multiple_alter_stmt[1]->length = 10; 412 413 } else if ($targettype == XMLDB_TYPE_NUMBER && $oldmetatype == 'X') { // text to decimal 414 $multiple_alter_stmt[0] = new stdClass; // needs conversion to varchar 415 $multiple_alter_stmt[0]->type = XMLDB_TYPE_CHAR; 416 $multiple_alter_stmt[0]->length = 255; 417 418 } else if ($targettype == XMLDB_TYPE_FLOAT && $oldmetatype == 'X') { // text to float 419 $multiple_alter_stmt[0] = new stdClass; // needs conversion to varchar 420 $multiple_alter_stmt[0]->type = XMLDB_TYPE_CHAR; 421 $multiple_alter_stmt[0]->length = 255; 422 } 423 424 // Just prevent default clauses in this type of sentences for mssql and launch the parent one 425 if (empty($multiple_alter_stmt)) { // Direct implicit conversion allowed, launch it 426 $results = array_merge($results, parent::getAlterFieldSQL($xmldb_table, $xmldb_field, NULL, true, NULL)); 427 428 } else { // Direct implicit conversion forbidden, use the intermediate ones 429 $final_type = $xmldb_field->getType(); // Save final type and length 430 $final_length = $xmldb_field->getLength(); 431 foreach ($multiple_alter_stmt as $alter) { 432 $xmldb_field->setType($alter->type); // Put our intermediate type and length and alter to it 433 $xmldb_field->setLength($alter->length); 434 $results = array_merge($results, parent::getAlterFieldSQL($xmldb_table, $xmldb_field, NULL, true, NULL)); 435 } 436 $xmldb_field->setType($final_type); // Set the final type and length and alter to it 437 $xmldb_field->setLength($final_length); 438 $results = array_merge($results, parent::getAlterFieldSQL($xmldb_table, $xmldb_field, NULL, true, NULL)); 439 } 440 441 // Finally, process the default clause to add it back if necessary 442 if ($typechanged || $lengthchanged) { 443 $results = array_merge($results, $this->getCreateDefaultSQL($xmldb_table, $xmldb_field)); 444 } 445 446 // Return results 447 return $results; 448 } 449 450 /** 451 * Given one xmldb_table and one xmldb_field, return the SQL statements needed to modify the default of the field in the table. 452 * 453 * @param xmldb_table $xmldb_table The table related to $xmldb_field. 454 * @param xmldb_field $xmldb_field The instance of xmldb_field to get the modified default value from. 455 * @return array The SQL statement for modifying the default value. 456 */ 457 public function getModifyDefaultSQL($xmldb_table, $xmldb_field) { 458 // MSSQL is a bit special with default constraints because it implements them as external constraints so 459 // normal ALTER TABLE ALTER COLUMN don't work to change defaults. Because this, we have this method overloaded here 460 461 $results = array(); 462 463 // Decide if we are going to create/modify or to drop the default 464 if ($xmldb_field->getDefault() === null) { 465 $results = $this->getDropDefaultSQL($xmldb_table, $xmldb_field); //Drop but, under some circumstances, re-enable 466 $default_clause = $this->getDefaultClause($xmldb_field); 467 if ($default_clause) { //If getDefaultClause() it must have one default, create it 468 $results = array_merge($results, $this->getCreateDefaultSQL($xmldb_table, $xmldb_field)); //Create/modify 469 } 470 } else { 471 $results = $this->getDropDefaultSQL($xmldb_table, $xmldb_field); //Drop (only if exists) 472 $results = array_merge($results, $this->getCreateDefaultSQL($xmldb_table, $xmldb_field)); //Create/modify 473 } 474 475 return $results; 476 } 477 478 /** 479 * Given one xmldb_table and one xmldb_field, return the SQL statements needed to add its default 480 * (usually invoked from getModifyDefaultSQL() 481 * 482 * @param xmldb_table $xmldb_table The xmldb_table object instance. 483 * @param xmldb_field $xmldb_field The xmldb_field object instance. 484 * @return array Array of SQL statements to create a field's default. 485 */ 486 public function getCreateDefaultSQL($xmldb_table, $xmldb_field) { 487 // MSSQL is a bit special and it requires the corresponding DEFAULT CONSTRAINT to be dropped 488 489 $results = array(); 490 491 // Get the quoted name of the table and field 492 $tablename = $this->getTableName($xmldb_table); 493 $fieldname = $this->getEncQuoted($xmldb_field->getName()); 494 495 // Now, check if, with the current field attributes, we have to build one default 496 $default_clause = $this->getDefaultClause($xmldb_field); 497 if ($default_clause) { 498 // We need to build the default (Moodle) default, so do it 499 $sql = 'ALTER TABLE ' . $tablename . ' ADD' . $default_clause . ' FOR ' . $fieldname; 500 $results[] = $sql; 501 } 502 503 return $results; 504 } 505 506 /** 507 * Given one xmldb_table and one xmldb_field, return the SQL statements needed to drop its default 508 * (usually invoked from getModifyDefaultSQL() 509 * 510 * Note that this method may be dropped in future. 511 * 512 * @param xmldb_table $xmldb_table The xmldb_table object instance. 513 * @param xmldb_field $xmldb_field The xmldb_field object instance. 514 * @return array Array of SQL statements to create a field's default. 515 * 516 * @todo MDL-31147 Moodle 2.1 - Drop getDropDefaultSQL() 517 */ 518 public function getDropDefaultSQL($xmldb_table, $xmldb_field) { 519 // MSSQL is a bit special and it requires the corresponding DEFAULT CONSTRAINT to be dropped 520 521 $results = array(); 522 523 // Get the quoted name of the table and field 524 $tablename = $this->getTableName($xmldb_table); 525 $fieldname = $this->getEncQuoted($xmldb_field->getName()); 526 527 // Look for the default contraint and, if found, drop it 528 if ($defaultname = $this->getDefaultConstraintName($xmldb_table, $xmldb_field)) { 529 $results[] = 'ALTER TABLE ' . $tablename . ' DROP CONSTRAINT ' . $defaultname; 530 } 531 532 return $results; 533 } 534 535 /** 536 * Given one xmldb_table and one xmldb_field, returns the name of its default constraint in DB 537 * or false if not found 538 * This function should be considered internal and never used outside from generator 539 * 540 * @param xmldb_table $xmldb_table The xmldb_table object instance. 541 * @param xmldb_field $xmldb_field The xmldb_field object instance. 542 * @return mixed 543 */ 544 protected function getDefaultConstraintName($xmldb_table, $xmldb_field) { 545 546 // Get the quoted name of the table and field 547 $tablename = $this->getTableName($xmldb_table); 548 $fieldname = $xmldb_field->getName(); 549 550 // Look for any default constraint in this field and drop it 551 if ($default = $this->mdb->get_record_sql("SELECT id, object_name(cdefault) AS defaultconstraint 552 FROM syscolumns 553 WHERE id = object_id(?) 554 AND name = ?", array($tablename, $fieldname))) { 555 return $default->defaultconstraint; 556 } else { 557 return false; 558 } 559 } 560 561 /** 562 * Given three strings (table name, list of fields (comma separated) and suffix), 563 * create the proper object name quoting it if necessary. 564 * 565 * IMPORTANT: This function must be used to CALCULATE NAMES of objects TO BE CREATED, 566 * NEVER TO GUESS NAMES of EXISTING objects!!! 567 * 568 * IMPORTANT: We are overriding this function for the MSSQL generator because objects 569 * belonging to temporary tables aren't searchable in the catalog neither in information 570 * schema tables. So, for temporary tables, we are going to add 4 randomly named "virtual" 571 * fields, so the generated names won't cause concurrency problems. Really nasty hack, 572 * but the alternative involves modifying all the creation table code to avoid naming 573 * constraints for temp objects and that will dupe a lot of code. 574 * 575 * @param string $tablename The table name. 576 * @param string $fields A list of comma separated fields. 577 * @param string $suffix A suffix for the object name. 578 * @return string Object's name. 579 */ 580 public function getNameForObject($tablename, $fields, $suffix='') { 581 if ($this->temptables->is_temptable($tablename)) { // Is temp table, inject random field names 582 $random = strtolower(random_string(12)); // 12cc to be split in 4 parts 583 $fields = $fields . ', ' . implode(', ', str_split($random, 3)); 584 } 585 return parent::getNameForObject($tablename, $fields, $suffix); // Delegate to parent (common) algorithm 586 } 587 588 /** 589 * Given one object name and it's type (pk, uk, fk, ck, ix, uix, seq, trg). 590 * 591 * (MySQL requires the whole xmldb_table object to be specified, so we add it always) 592 * 593 * This is invoked from getNameForObject(). 594 * Only some DB have this implemented. 595 * 596 * @param string $object_name The object's name to check for. 597 * @param string $type The object's type (pk, uk, fk, ck, ix, uix, seq, trg). 598 * @param string $table_name The table's name to check in 599 * @return bool If such name is currently in use (true) or no (false) 600 */ 601 public function isNameInUse($object_name, $type, $table_name) { 602 switch($type) { 603 case 'seq': 604 case 'trg': 605 case 'pk': 606 case 'uk': 607 case 'fk': 608 case 'ck': 609 if ($check = $this->mdb->get_records_sql("SELECT name 610 FROM sysobjects 611 WHERE lower(name) = ?", array(strtolower($object_name)))) { 612 return true; 613 } 614 break; 615 case 'ix': 616 case 'uix': 617 if ($check = $this->mdb->get_records_sql("SELECT name 618 FROM sysindexes 619 WHERE lower(name) = ?", array(strtolower($object_name)))) { 620 return true; 621 } 622 break; 623 } 624 return false; //No name in use found 625 } 626 627 /** 628 * Returns the code (array of statements) needed to add one comment to the table. 629 * 630 * @param xmldb_table $xmldb_table The xmldb_table object instance. 631 * @return array Array of SQL statements to add one comment to the table. 632 */ 633 public function getCommentSQL($xmldb_table) { 634 return array(); 635 } 636 637 /** 638 * Adds slashes to string. 639 * @param string $s 640 * @return string The escaped string. 641 */ 642 public function addslashes($s) { 643 // do not use php addslashes() because it depends on PHP quote settings! 644 $s = str_replace("'", "''", $s); 645 return $s; 646 } 647 648 /** 649 * Returns an array of reserved words (lowercase) for this DB 650 * @return array An array of database specific reserved words 651 */ 652 public static function getReservedWords() { 653 // This file contains the reserved words for MSSQL databases 654 // from http://msdn2.microsoft.com/en-us/library/ms189822.aspx 655 $reserved_words = array ( 656 'add', 'all', 'alter', 'and', 'any', 'as', 'asc', 'authorization', 657 'avg', 'backup', 'begin', 'between', 'break', 'browse', 'bulk', 658 'by', 'cascade', 'case', 'check', 'checkpoint', 'close', 'clustered', 659 'coalesce', 'collate', 'column', 'commit', 'committed', 'compute', 660 'confirm', 'constraint', 'contains', 'containstable', 'continue', 661 'controlrow', 'convert', 'count', 'create', 'cross', 'current', 662 'current_date', 'current_time', 'current_timestamp', 'current_user', 663 'cursor', 'database', 'dbcc', 'deallocate', 'declare', 'default', 'delete', 664 'deny', 'desc', 'disk', 'distinct', 'distributed', 'double', 'drop', 'dummy', 665 'dump', 'else', 'end', 'errlvl', 'errorexit', 'escape', 'except', 'exec', 666 'execute', 'exists', 'exit', 'external', 'fetch', 'file', 'fillfactor', 'floppy', 667 'for', 'foreign', 'freetext', 'freetexttable', 'from', 'full', 'function', 668 'goto', 'grant', 'group', 'having', 'holdlock', 'identity', 'identitycol', 669 'identity_insert', 'if', 'in', 'index', 'inner', 'insert', 'intersect', 'into', 670 'is', 'isolation', 'join', 'key', 'kill', 'left', 'level', 'like', 'lineno', 671 'load', 'max', 'min', 'mirrorexit', 'national', 'nocheck', 'nonclustered', 672 'not', 'null', 'nullif', 'of', 'off', 'offsets', 'on', 'once', 'only', 'open', 673 'opendatasource', 'openquery', 'openrowset', 'openxml', 'option', 'or', 'order', 674 'outer', 'over', 'percent', 'perm', 'permanent', 'pipe', 'pivot', 'plan', 'precision', 675 'prepare', 'primary', 'print', 'privileges', 'proc', 'procedure', 'processexit', 676 'public', 'raiserror', 'read', 'readtext', 'reconfigure', 'references', 677 'repeatable', 'replication', 'restore', 'restrict', 'return', 'revoke', 678 'right', 'rollback', 'rowcount', 'rowguidcol', 'rule', 'save', 'schema', 679 'select', 'serializable', 'session_user', 'set', 'setuser', 'shutdown', 'some', 680 'statistics', 'sum', 'system_user', 'table', 'tape', 'temp', 'temporary', 681 'textsize', 'then', 'to', 'top', 'tran', 'transaction', 'trigger', 'truncate', 682 'tsequal', 'uncommitted', 'union', 'unique', 'update', 'updatetext', 'use', 683 'user', 'values', 'varying', 'view', 'waitfor', 'when', 'where', 'while', 684 'with', 'work', 'writetext' 685 ); 686 return $reserved_words; 687 } 688 }
title
Description
Body
title
Description
Body
title
Description
Body
title
Body
| Generated: Thu Aug 11 10:00:09 2016 | Cross-referenced by PHPXref 0.7.1 |