Proyectos de Subversion Moodle

Rev

| Ultima modificación | Ver Log |

Rev Autor Línea Nro. Línea
1 efrain 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
}