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