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 |
}
|