This project does not seem to handle request data directly as such no vulnerable execution paths were found.
include
, or for example
via PHP's auto-loading mechanism.
These results are based on our legacy PHP analysis, consider migrating to our new PHP analysis engine instead. Learn more
1 | <?php |
||
2 | |||
3 | namespace SilverStripe\MSSQL; |
||
4 | |||
5 | use SilverStripe\ORM\Connect\DBSchemaManager; |
||
6 | |||
7 | /** |
||
8 | * Represents and handles all schema management for a MS SQL database |
||
9 | */ |
||
10 | class MSSQLSchemaManager extends DBSchemaManager |
||
11 | { |
||
12 | |||
13 | /** |
||
14 | * Stores per-request cached constraint checks that come from the database. |
||
15 | * |
||
16 | * @var array |
||
17 | */ |
||
18 | protected static $cached_checks = array(); |
||
19 | |||
20 | /** |
||
21 | * Builds the internal MS SQL Server index name given the silverstripe table and index name |
||
22 | * |
||
23 | * @param string $tableName |
||
24 | * @param string $indexName |
||
25 | * @param string $prefix The optional prefix for the index. Defaults to "ix" for indexes. |
||
26 | * @return string The name of the index |
||
27 | */ |
||
28 | public function buildMSSQLIndexName($tableName, $indexName, $prefix = 'ix') |
||
29 | { |
||
30 | |||
31 | // Cleanup names of namespaced tables |
||
32 | $tableName = str_replace('\\', '_', $tableName); |
||
33 | $indexName = str_replace('\\', '_', $indexName); |
||
34 | |||
35 | return "{$prefix}_{$tableName}_{$indexName}"; |
||
36 | } |
||
37 | |||
38 | |||
39 | /** |
||
40 | * This will set up the full text search capabilities. |
||
41 | * |
||
42 | * @param string $name Name of full text catalog to use |
||
43 | */ |
||
44 | public function createFullTextCatalog($name = 'ftCatalog') |
||
45 | { |
||
46 | $result = $this->fullTextCatalogExists(); |
||
47 | if (!$result) { |
||
48 | $this->query("CREATE FULLTEXT CATALOG \"$name\" AS DEFAULT;"); |
||
49 | } |
||
50 | } |
||
51 | |||
52 | /** |
||
53 | * Check that a fulltext catalog has been created yet. |
||
54 | * |
||
55 | * @param string $name Name of full text catalog to use |
||
56 | * @return boolean |
||
57 | */ |
||
58 | public function fullTextCatalogExists($name = 'ftCatalog') |
||
59 | { |
||
60 | return (bool) $this->preparedQuery( |
||
61 | "SELECT name FROM sys.fulltext_catalogs WHERE name = ?;", |
||
62 | array($name) |
||
63 | )->value(); |
||
64 | } |
||
65 | |||
66 | /** |
||
67 | * Sleep until the catalog has been fully rebuilt. This is a busy wait designed for situations |
||
68 | * when you need to be sure the index is up to date - for example in unit tests. |
||
69 | * |
||
70 | * TODO: move this to Database class? Can we assume this will be useful for all databases? |
||
71 | * Also see the wrapper functions "waitUntilIndexingFinished" in SearchFormTest and TranslatableSearchFormTest |
||
72 | * |
||
73 | * @param int $maxWaitingTime Time in seconds to wait for the database. |
||
74 | */ |
||
75 | public function waitUntilIndexingFinished($maxWaitingTime = 15) |
||
76 | { |
||
77 | if (!$this->database->fullTextEnabled()) { |
||
78 | return; |
||
79 | } |
||
80 | |||
81 | $this->query("EXEC sp_fulltext_catalog 'ftCatalog', 'Rebuild';"); |
||
82 | |||
83 | // Busy wait until it's done updating, but no longer than 15 seconds. |
||
84 | $start = time(); |
||
85 | while (time() - $start < $maxWaitingTime) { |
||
86 | $status = $this->query("EXEC sp_help_fulltext_catalogs 'ftCatalog';")->first(); |
||
87 | |||
88 | if (isset($status['STATUS']) && $status['STATUS'] == 0) { |
||
89 | // Idle! |
||
90 | break; |
||
91 | } |
||
92 | sleep(1); |
||
93 | } |
||
94 | } |
||
95 | |||
96 | /** |
||
97 | * Check if a fulltext index exists on a particular table name. |
||
98 | * |
||
99 | * @param string $tableName |
||
100 | * @return boolean TRUE index exists | FALSE index does not exist | NULL no support |
||
101 | */ |
||
102 | public function fulltextIndexExists($tableName) |
||
103 | { |
||
104 | // Special case for no full text index support |
||
105 | if (!$this->database->fullTextEnabled()) { |
||
106 | return null; |
||
107 | } |
||
108 | |||
109 | return (bool) $this->preparedQuery(" |
||
110 | SELECT 1 FROM sys.fulltext_indexes i |
||
111 | JOIN sys.objects o ON i.object_id = o.object_id |
||
112 | WHERE o.name = ?", |
||
113 | array($tableName) |
||
114 | )->value(); |
||
115 | } |
||
116 | |||
117 | /** |
||
118 | * MSSQL stores the primary key column with an internal identifier, |
||
119 | * so a lookup needs to be done to determine it. |
||
120 | * |
||
121 | * @param string $tableName Name of table with primary key column "ID" |
||
122 | * @return string Internal identifier for primary key |
||
123 | */ |
||
124 | public function getPrimaryKey($tableName) |
||
125 | { |
||
126 | $indexes = $this->query("EXEC sp_helpindex '$tableName';"); |
||
127 | $indexName = ''; |
||
128 | foreach ($indexes as $index) { |
||
129 | if ($index['index_keys'] == 'ID') { |
||
130 | $indexName = $index['index_name']; |
||
131 | break; |
||
132 | } |
||
133 | } |
||
134 | |||
135 | return $indexName; |
||
136 | } |
||
137 | |||
138 | /** |
||
139 | * Gets the identity column of a table |
||
140 | * |
||
141 | * @param string $tableName |
||
142 | * @return string|null |
||
143 | */ |
||
144 | public function getIdentityColumn($tableName) |
||
145 | { |
||
146 | return $this->preparedQuery(" |
||
147 | SELECT |
||
148 | TABLE_NAME + '.' + COLUMN_NAME, |
||
149 | TABLE_NAME |
||
150 | FROM |
||
151 | INFORMATION_SCHEMA.COLUMNS |
||
152 | WHERE |
||
153 | TABLE_SCHEMA = ? AND |
||
154 | COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1 AND |
||
155 | TABLE_NAME = ? |
||
156 | ", array('dbo', $tableName))->value(); |
||
157 | } |
||
158 | |||
159 | public function createDatabase($name) |
||
160 | { |
||
161 | $this->query("CREATE DATABASE \"$name\""); |
||
162 | } |
||
163 | |||
164 | public function dropDatabase($name) |
||
165 | { |
||
166 | $this->query("DROP DATABASE \"$name\""); |
||
167 | } |
||
168 | |||
169 | public function databaseExists($name) |
||
170 | { |
||
171 | $databases = $this->databaseList(); |
||
172 | foreach ($databases as $dbname) { |
||
173 | if ($dbname == $name) { |
||
174 | return true; |
||
175 | } |
||
176 | } |
||
177 | return false; |
||
178 | } |
||
179 | |||
180 | public function databaseList() |
||
181 | { |
||
182 | return $this->query('SELECT NAME FROM sys.sysdatabases')->column(); |
||
183 | } |
||
184 | |||
185 | /** |
||
186 | * Create a new table. |
||
187 | * @param string $tableName The name of the table |
||
188 | * @param array $fields A map of field names to field types |
||
189 | * @param array $indexes A map of indexes |
||
190 | * @param array $options An map of additional options. The available keys are as follows: |
||
191 | * - 'MSSQLDatabase'/'MySQLDatabase'/'PostgreSQLDatabase' - database-specific options such as "engine" for MySQL. |
||
192 | * - 'temporary' - If true, then a temporary table will be created |
||
193 | * @param array $advancedOptions |
||
194 | * @return string The table name generated. This may be different from the table name, for example with temporary tables. |
||
195 | */ |
||
196 | public function createTable($tableName, $fields = null, $indexes = null, $options = null, $advancedOptions = null) |
||
197 | { |
||
198 | $fieldSchemas = $indexSchemas = ""; |
||
199 | if ($fields) { |
||
200 | foreach ($fields as $k => $v) { |
||
201 | $fieldSchemas .= "\"$k\" $v,\n"; |
||
202 | } |
||
203 | } |
||
204 | |||
205 | // Temporary tables start with "#" in MSSQL-land |
||
206 | if (!empty($options['temporary'])) { |
||
207 | // Randomize the temp table name to avoid conflicts in the tempdb table which derived databases share |
||
208 | $tableName = "#$tableName" . '-' . rand(1000000, 9999999); |
||
209 | } |
||
210 | |||
211 | $this->query("CREATE TABLE \"$tableName\" ( |
||
212 | $fieldSchemas |
||
213 | primary key (\"ID\") |
||
214 | );"); |
||
215 | |||
216 | //we need to generate indexes like this: CREATE INDEX IX_vault_to_export ON vault (to_export); |
||
217 | //This needs to be done AFTER the table creation, so we can set up the fulltext indexes correctly |
||
218 | if ($indexes) { |
||
219 | foreach ($indexes as $k => $v) { |
||
220 | $indexSchemas .= $this->getIndexSqlDefinition($tableName, $k, $v) . "\n"; |
||
221 | } |
||
222 | } |
||
223 | |||
224 | if ($indexSchemas) { |
||
225 | $this->query($indexSchemas); |
||
226 | } |
||
227 | |||
228 | return $tableName; |
||
229 | } |
||
230 | |||
231 | /** |
||
232 | * Alter a table's schema. |
||
233 | * @param string $tableName The name of the table to alter |
||
234 | * @param array $newFields New fields, a map of field name => field schema |
||
235 | * @param array $newIndexes New indexes, a map of index name => index type |
||
236 | * @param array $alteredFields Updated fields, a map of field name => field schema |
||
237 | * @param array $alteredIndexes Updated indexes, a map of index name => index type |
||
238 | * @param array $alteredOptions |
||
239 | * @param array $advancedOptions |
||
240 | */ |
||
241 | public function alterTable($tableName, $newFields = null, $newIndexes = null, $alteredFields = null, $alteredIndexes = null, $alteredOptions=null, $advancedOptions=null) |
||
242 | { |
||
243 | $alterList = array(); |
||
244 | |||
245 | // drop any fulltext indexes that exist on the table before altering the structure |
||
246 | if ($this->fulltextIndexExists($tableName)) { |
||
247 | $alterList[] = "\nDROP FULLTEXT INDEX ON \"$tableName\";"; |
||
248 | } |
||
249 | |||
250 | if ($newFields) { |
||
251 | foreach ($newFields as $k => $v) { |
||
252 | $alterList[] = "ALTER TABLE \"$tableName\" ADD \"$k\" $v"; |
||
253 | } |
||
254 | } |
||
255 | |||
256 | if ($alteredFields) { |
||
257 | foreach ($alteredFields as $k => $v) { |
||
258 | $alterList[] = $this->alterTableAlterColumn($tableName, $k, $v); |
||
259 | } |
||
260 | } |
||
261 | if ($alteredIndexes) { |
||
262 | foreach ($alteredIndexes as $k => $v) { |
||
263 | $alterList[] = $this->getIndexSqlDefinition($tableName, $k, $v); |
||
264 | } |
||
265 | } |
||
266 | if ($newIndexes) { |
||
267 | foreach ($newIndexes as $k => $v) { |
||
268 | $alterList[] = $this->getIndexSqlDefinition($tableName, $k, $v); |
||
269 | } |
||
270 | } |
||
271 | |||
272 | if ($alterList) { |
||
273 | foreach ($alterList as $alteration) { |
||
274 | if ($alteration != '') { |
||
275 | $this->query($alteration); |
||
276 | } |
||
277 | } |
||
278 | } |
||
279 | } |
||
280 | |||
281 | /** |
||
282 | * Given the table and column name, retrieve the constraint name for that column |
||
283 | * in the table. |
||
284 | * |
||
285 | * @param string $tableName Table name column resides in |
||
286 | * @param string $columnName Column name the constraint is for |
||
287 | * @return string|null |
||
288 | */ |
||
289 | public function getConstraintName($tableName, $columnName) |
||
290 | { |
||
291 | return $this->preparedQuery(" |
||
292 | SELECT CONSTRAINT_NAME |
||
293 | FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE |
||
294 | WHERE TABLE_NAME = ? AND COLUMN_NAME = ?", |
||
295 | array($tableName, $columnName) |
||
296 | )->value(); |
||
297 | } |
||
298 | |||
299 | /** |
||
300 | * Given a table and column name, return a check constraint clause for that column in |
||
301 | * the table. |
||
302 | * |
||
303 | * This is an expensive query, so it is cached per-request and stored by table. The initial |
||
304 | * call for a table that has not been cached will query all columns and store that |
||
305 | * so subsequent calls are fast. |
||
306 | * |
||
307 | * @param string $tableName Table name column resides in |
||
308 | * @param string $columnName Column name the constraint is for |
||
309 | * @return string The check string |
||
310 | */ |
||
311 | public function getConstraintCheckClause($tableName, $columnName) |
||
312 | { |
||
313 | // Check already processed table columns |
||
314 | if (isset(self::$cached_checks[$tableName])) { |
||
315 | if (!isset(self::$cached_checks[$tableName][$columnName])) { |
||
316 | return null; |
||
317 | } |
||
318 | return self::$cached_checks[$tableName][$columnName]; |
||
319 | } |
||
320 | |||
321 | // Regenerate cehcks for this table |
||
322 | $checks = array(); |
||
323 | foreach ($this->preparedQuery(" |
||
324 | SELECT CAST(CHECK_CLAUSE AS TEXT) AS CHECK_CLAUSE, COLUMN_NAME |
||
325 | FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS AS CC |
||
326 | INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS CCU ON CCU.CONSTRAINT_NAME = CC.CONSTRAINT_NAME |
||
327 | WHERE TABLE_NAME = ?", |
||
328 | array($tableName) |
||
329 | ) as $record) { |
||
330 | $checks[$record['COLUMN_NAME']] = $record['CHECK_CLAUSE']; |
||
331 | } |
||
332 | self::$cached_checks[$tableName] = $checks; |
||
333 | |||
334 | // Return via cached records |
||
335 | return $this->getConstraintCheckClause($tableName, $columnName); |
||
336 | } |
||
337 | |||
338 | /** |
||
339 | * Return the name of the default constraint applied to $tableName.$colName. |
||
340 | * Will return null if no such constraint exists |
||
341 | * |
||
342 | * @param string $tableName Name of the table |
||
343 | * @param string $colName Name of the column |
||
344 | * @return string|null |
||
345 | */ |
||
346 | protected function defaultConstraintName($tableName, $colName) |
||
347 | { |
||
348 | return $this->preparedQuery(" |
||
349 | SELECT s.name --default name |
||
350 | FROM sys.sysobjects s |
||
351 | join sys.syscolumns c ON s.parent_obj = c.id |
||
352 | WHERE s.xtype = 'd' |
||
353 | and c.cdefault = s.id |
||
354 | and parent_obj = OBJECT_ID(?) |
||
355 | and c.name = ?", |
||
356 | array($tableName, $colName) |
||
357 | )->value(); |
||
358 | } |
||
359 | |||
360 | /** |
||
361 | * Get enum values from a constraint check clause. |
||
362 | * |
||
363 | * @param string $clause Check clause to parse values from |
||
364 | * @return array Enum values |
||
365 | */ |
||
366 | protected function enumValuesFromCheckClause($clause) |
||
367 | { |
||
368 | $segments = preg_split('/ +OR *\[/i', $clause); |
||
369 | $constraints = array(); |
||
370 | foreach ($segments as $segment) { |
||
371 | $bits = preg_split('/ *= */', $segment); |
||
372 | for ($i = 1; $i < sizeof($bits); $i += 2) { |
||
373 | array_unshift($constraints, substr(rtrim($bits[$i], ')'), 1, -1)); |
||
374 | } |
||
375 | } |
||
376 | return $constraints; |
||
377 | } |
||
378 | |||
379 | /* |
||
380 | * Creates an ALTER expression for a column in MS SQL |
||
381 | * |
||
382 | * @param string $tableName Name of the table to be altered |
||
383 | * @param string $colName Name of the column to be altered |
||
384 | * @param string $colSpec String which contains conditions for a column |
||
385 | * @return string |
||
386 | */ |
||
387 | protected function alterTableAlterColumn($tableName, $colName, $colSpec) |
||
388 | { |
||
389 | |||
390 | // First, we split the column specifications into parts |
||
391 | // TODO: this returns an empty array for the following string: int(11) not null auto_increment |
||
392 | // on second thoughts, why is an auto_increment field being passed through? |
||
393 | $pattern = '/^(?<definition>[\w()]+)\s?(?<null>(?:not\s)?null)?\s?(?<default>default\s[\w\']+)?\s?(?<check>check\s?[\w()\'",\s]+)?$/i'; |
||
394 | $matches = array(); |
||
395 | preg_match($pattern, $colSpec, $matches); |
||
396 | |||
397 | // drop the index if it exists |
||
398 | $alterQueries = array(); |
||
399 | |||
400 | // drop *ALL* indexes on a table before proceeding |
||
401 | // this won't drop primary keys, though |
||
402 | $indexes = $this->indexNames($tableName); |
||
403 | $indexes = array_filter($indexes); |
||
404 | |||
405 | foreach ($indexes as $indexName) { |
||
406 | $alterQueries[] = "IF EXISTS (SELECT name FROM sys.indexes WHERE name = '$indexName' AND object_id = object_id(SCHEMA_NAME() + '.$tableName')) DROP INDEX \"$indexName\" ON \"$tableName\";"; |
||
407 | } |
||
408 | |||
409 | $prefix = "ALTER TABLE \"$tableName\" "; |
||
410 | |||
411 | // Remove the old default prior to adjusting the column. |
||
412 | if ($defaultConstraintName = $this->defaultConstraintName($tableName, $colName)) { |
||
413 | $alterQueries[] = "$prefix DROP CONSTRAINT \"$defaultConstraintName\";"; |
||
414 | } |
||
415 | |||
416 | if (isset($matches['definition'])) { |
||
417 | //We will prevent any changes being made to the ID column. Primary key indexes will have a fit if we do anything here. |
||
418 | if ($colName != 'ID') { |
||
419 | |||
420 | // SET null / not null |
||
421 | $nullFragment = empty($matches['null']) ? '' : " {$matches['null']}"; |
||
422 | $alterQueries[] = "$prefix ALTER COLUMN \"$colName\" {$matches['definition']}$nullFragment;"; |
||
423 | |||
424 | // Add a default back |
||
425 | if (!empty($matches['default'])) { |
||
426 | $alterQueries[] = "$prefix ADD {$matches['default']} FOR \"$colName\";"; |
||
427 | } |
||
428 | |||
429 | // SET check constraint (The constraint HAS to be dropped) |
||
430 | if (!empty($matches['check'])) { |
||
431 | $constraint = $this->getConstraintName($tableName, $colName); |
||
432 | if ($constraint) { |
||
433 | $alterQueries[] = "$prefix DROP CONSTRAINT {$constraint};"; |
||
434 | } |
||
435 | |||
436 | //NOTE: 'with nocheck' seems to solve a few problems I've been having for modifying existing tables. |
||
437 | $alterQueries[] = "$prefix WITH NOCHECK ADD CONSTRAINT \"{$tableName}_{$colName}_check\" {$matches['check']};"; |
||
438 | } |
||
439 | } |
||
440 | } |
||
441 | |||
442 | return implode("\n", $alterQueries); |
||
443 | } |
||
444 | |||
445 | public function renameTable($oldTableName, $newTableName) |
||
446 | { |
||
447 | $this->query("EXEC sp_rename \"$oldTableName\", \"$newTableName\""); |
||
448 | } |
||
449 | |||
450 | /** |
||
451 | * Checks a table's integrity and repairs it if necessary. |
||
452 | * NOTE: MSSQL does not appear to support any vacuum or optimise commands |
||
453 | * |
||
454 | * @var string $tableName The name of the table. |
||
455 | * @return boolean Return true if the table has integrity after the method is complete. |
||
456 | */ |
||
457 | public function checkAndRepairTable($tableName) |
||
458 | { |
||
459 | return true; |
||
460 | } |
||
461 | |||
462 | public function createField($tableName, $fieldName, $fieldSpec) |
||
463 | { |
||
464 | $this->query("ALTER TABLE \"$tableName\" ADD \"$fieldName\" $fieldSpec"); |
||
465 | } |
||
466 | |||
467 | /** |
||
468 | * Change the database type of the given field. |
||
469 | * @param string $tableName The name of the tbale the field is in. |
||
470 | * @param string $fieldName The name of the field to change. |
||
471 | * @param string $fieldSpec The new field specification |
||
472 | */ |
||
473 | public function alterField($tableName, $fieldName, $fieldSpec) |
||
474 | { |
||
475 | $this->query("ALTER TABLE \"$tableName\" CHANGE \"$fieldName\" \"$fieldName\" $fieldSpec"); |
||
476 | } |
||
477 | |||
478 | public function renameField($tableName, $oldName, $newName) |
||
479 | { |
||
480 | $this->query("EXEC sp_rename @objname = '$tableName.$oldName', @newname = '$newName', @objtype = 'COLUMN'"); |
||
481 | } |
||
482 | |||
483 | public function fieldList($table) |
||
484 | { |
||
485 | //This gets us more information than we need, but I've included it all for the moment.... |
||
486 | $fieldRecords = $this->preparedQuery("SELECT ordinal_position, column_name, data_type, column_default, |
||
487 | is_nullable, character_maximum_length, numeric_precision, numeric_scale, collation_name |
||
488 | FROM information_schema.columns WHERE table_name = ? |
||
489 | ORDER BY ordinal_position;", |
||
490 | array($table) |
||
491 | ); |
||
492 | |||
493 | // Cache the records from the query - otherwise a lack of multiple active result sets |
||
494 | // will cause subsequent queries to fail in this method |
||
495 | $fields = array(); |
||
496 | $output = array(); |
||
497 | foreach ($fieldRecords as $record) { |
||
498 | $fields[] = $record; |
||
499 | } |
||
500 | |||
501 | foreach ($fields as $field) { |
||
502 | // Update the data_type field to be a complete column definition string for use by |
||
503 | // SS_Database::requireField() |
||
504 | switch ($field['data_type']) { |
||
505 | case 'int': |
||
506 | case 'bigint': |
||
507 | case 'numeric': |
||
508 | case 'float': |
||
509 | case 'bit': |
||
510 | if ($field['data_type'] != 'bigint' && $field['data_type'] != 'int' && $sizeSuffix = $field['numeric_precision']) { |
||
511 | $field['data_type'] .= "($sizeSuffix)"; |
||
512 | } |
||
513 | |||
514 | View Code Duplication | if ($field['is_nullable'] == 'YES') { |
|
515 | $field['data_type'] .= ' null'; |
||
516 | } else { |
||
517 | $field['data_type'] .= ' not null'; |
||
518 | } |
||
519 | View Code Duplication | if ($field['column_default']) { |
|
520 | $default=substr($field['column_default'], 2, -2); |
||
521 | $field['data_type'] .= " default $default"; |
||
522 | } |
||
523 | break; |
||
524 | |||
525 | case 'decimal': |
||
526 | if ($field['numeric_precision']) { |
||
527 | $sizeSuffix = $field['numeric_precision'] . ',' . $field['numeric_scale']; |
||
528 | $field['data_type'] .= "($sizeSuffix)"; |
||
529 | } |
||
530 | |||
531 | View Code Duplication | if ($field['is_nullable'] == 'YES') { |
|
532 | $field['data_type'] .= ' null'; |
||
533 | } else { |
||
534 | $field['data_type'] .= ' not null'; |
||
535 | } |
||
536 | View Code Duplication | if ($field['column_default']) { |
|
537 | $default=substr($field['column_default'], 2, -2); |
||
538 | $field['data_type'] .= " default $default"; |
||
539 | } |
||
540 | break; |
||
541 | |||
542 | case 'nvarchar': |
||
543 | case 'varchar': |
||
0 ignored issues
–
show
Coding Style
introduced
by
Loading history...
|
|||
544 | //Check to see if there's a constraint attached to this column: |
||
545 | $clause = $this->getConstraintCheckClause($table, $field['column_name']); |
||
546 | if ($clause) { |
||
547 | $constraints = $this->enumValuesFromCheckClause($clause); |
||
548 | $default=substr($field['column_default'], 2, -2); |
||
549 | $field['data_type'] = $this->enum(array( |
||
550 | 'default' => $default, |
||
551 | 'name' => $field['column_name'], |
||
552 | 'enums' => $constraints, |
||
553 | 'table' => $table |
||
554 | )); |
||
555 | break; |
||
556 | } |
||
557 | |||
558 | default: |
||
559 | $sizeSuffix = $field['character_maximum_length']; |
||
560 | if ($sizeSuffix == '-1') { |
||
561 | $sizeSuffix = 'max'; |
||
562 | } |
||
563 | if ($sizeSuffix) { |
||
564 | $field['data_type'] .= "($sizeSuffix)"; |
||
565 | } |
||
566 | |||
567 | View Code Duplication | if ($field['is_nullable'] == 'YES') { |
|
568 | $field['data_type'] .= ' null'; |
||
569 | } else { |
||
570 | $field['data_type'] .= ' not null'; |
||
571 | } |
||
572 | View Code Duplication | if ($field['column_default']) { |
|
573 | $default=substr($field['column_default'], 2, -2); |
||
574 | $field['data_type'] .= " default '$default'"; |
||
575 | } |
||
576 | } |
||
577 | $output[$field['column_name']] = $field; |
||
578 | } |
||
579 | |||
580 | return $output; |
||
581 | } |
||
582 | |||
583 | /** |
||
584 | * Create an index on a table. |
||
585 | * @param string $tableName The name of the table. |
||
586 | * @param string $indexName The name of the index. |
||
587 | * @param string $indexSpec The specification of the index, see SS_Database::requireIndex() for more details. |
||
588 | */ |
||
589 | public function createIndex($tableName, $indexName, $indexSpec) |
||
590 | { |
||
591 | $this->query($this->getIndexSqlDefinition($tableName, $indexName, $indexSpec)); |
||
592 | } |
||
593 | |||
594 | /** |
||
595 | * Return SQL for dropping and recreating an index |
||
596 | * |
||
597 | * @param string $tableName Name of table to create this index against |
||
598 | * @param string $indexName Name of this index |
||
599 | * @param array|string $indexSpec Index specification, either as a raw string |
||
600 | * or parsed array form |
||
601 | * @return string The SQL required to generate this index |
||
602 | */ |
||
603 | protected function getIndexSqlDefinition($tableName, $indexName, $indexSpec) |
||
604 | { |
||
605 | |||
606 | // Determine index name |
||
607 | $index = $this->buildMSSQLIndexName($tableName, $indexName); |
||
608 | |||
609 | // Consolidate/Cleanup spec into array format |
||
610 | $indexSpec = $this->parseIndexSpec($indexName, $indexSpec); |
||
611 | |||
612 | $drop = "IF EXISTS (SELECT name FROM sys.indexes WHERE name = '$index' AND object_id = object_id(SCHEMA_NAME() + '.$tableName')) DROP INDEX $index ON \"$tableName\";"; |
||
613 | |||
614 | // create a type-specific index |
||
615 | if ($indexSpec['type'] == 'fulltext') { |
||
616 | if(!$this->database->fullTextEnabled()) { |
||
617 | return ''; |
||
618 | } |
||
619 | // enable fulltext on this table |
||
620 | $this->createFullTextCatalog(); |
||
621 | $primary_key = $this->getPrimaryKey($tableName); |
||
622 | |||
623 | if ($primary_key) { |
||
624 | return "$drop CREATE FULLTEXT INDEX ON \"$tableName\" ({$indexSpec['value']})" |
||
625 | . "KEY INDEX $primary_key WITH CHANGE_TRACKING AUTO;"; |
||
626 | } |
||
627 | } |
||
628 | |||
629 | if ($indexSpec['type'] == 'unique') { |
||
630 | return "$drop CREATE UNIQUE INDEX $index ON \"$tableName\" ({$indexSpec['value']});"; |
||
631 | } |
||
632 | |||
633 | return "$drop CREATE INDEX $index ON \"$tableName\" ({$indexSpec['value']});"; |
||
634 | } |
||
635 | |||
636 | public function alterIndex($tableName, $indexName, $indexSpec) |
||
637 | { |
||
638 | $this->createIndex($tableName, $indexName, $indexSpec); |
||
639 | } |
||
640 | |||
641 | /** |
||
642 | * Return the list of indexes in a table. |
||
643 | * @param string $table The table name. |
||
644 | * @return array |
||
645 | */ |
||
646 | public function indexList($table) |
||
647 | { |
||
648 | $indexes = $this->query("EXEC sp_helpindex '$table';"); |
||
649 | $indexList = array(); |
||
650 | |||
651 | // Enumerate all basic indexes |
||
652 | foreach ($indexes as $index) { |
||
653 | if (strpos($index['index_description'], 'unique') !== false) { |
||
654 | $indexType = 'unique '; |
||
655 | } else { |
||
656 | $indexType = 'index '; |
||
657 | } |
||
658 | |||
659 | // Extract name from index |
||
660 | $baseIndexName = $this->buildMSSQLIndexName($table, ''); |
||
661 | $indexName = substr($index['index_name'], strlen($baseIndexName)); |
||
662 | |||
663 | // Extract columns |
||
664 | $columns = $this->quoteColumnSpecString($index['index_keys']); |
||
665 | $indexList[$indexName] = $this->parseIndexSpec($indexName, array( |
||
666 | 'name' => $indexName, |
||
667 | 'value' => $columns, |
||
668 | 'type' => $indexType |
||
669 | )); |
||
670 | } |
||
671 | |||
672 | // Now we need to check to see if we have any fulltext indexes attached to this table: |
||
673 | if ($this->database->fullTextEnabled()) { |
||
674 | $result = $this->query('EXEC sp_help_fulltext_columns;'); |
||
675 | |||
676 | // Extract columns from this fulltext definition |
||
677 | $columns = array(); |
||
678 | foreach ($result as $row) { |
||
679 | if ($row['TABLE_NAME'] == $table) { |
||
680 | $columns[] = $row['FULLTEXT_COLUMN_NAME']; |
||
681 | } |
||
682 | } |
||
683 | |||
684 | if (!empty($columns)) { |
||
685 | $indexList['SearchFields'] = $this->parseIndexSpec('SearchFields', array( |
||
686 | 'name' => 'SearchFields', |
||
687 | 'value' => $this->implodeColumnList($columns), |
||
688 | 'type' => 'fulltext' |
||
689 | )); |
||
690 | } |
||
691 | } |
||
692 | |||
693 | return $indexList; |
||
694 | } |
||
695 | |||
696 | /** |
||
697 | * For a given table name, get all the internal index names, |
||
698 | * except for those that are primary keys and fulltext indexes. |
||
699 | * |
||
700 | * @param string $tableName |
||
701 | * @return array |
||
702 | */ |
||
703 | public function indexNames($tableName) |
||
704 | { |
||
705 | return $this->preparedQuery(' |
||
706 | SELECT ind.name FROM sys.indexes ind |
||
707 | INNER JOIN sys.tables t ON ind.object_id = t.object_id |
||
708 | WHERE is_primary_key = 0 AND t.name = ? AND ind.name IS NOT NULL', |
||
709 | array($tableName) |
||
710 | )->column(); |
||
711 | } |
||
712 | |||
713 | public function tableList() |
||
714 | { |
||
715 | $tables = array(); |
||
716 | foreach ($this->query("EXEC sp_tables @table_owner = 'dbo';") as $record) { |
||
717 | $tables[strtolower($record['TABLE_NAME'])] = $record['TABLE_NAME']; |
||
718 | } |
||
719 | return $tables; |
||
720 | } |
||
721 | |||
722 | /** |
||
723 | * Return a boolean type-formatted string |
||
724 | * We use 'bit' so that we can do numeric-based comparisons |
||
725 | * |
||
726 | * @param array $values Contains a tokenised list of info about this data type |
||
727 | * @return string |
||
728 | */ |
||
729 | public function boolean($values) |
||
730 | { |
||
731 | $default = ($values['default']) ? '1' : '0'; |
||
732 | return 'bit not null default ' . $default; |
||
733 | } |
||
734 | |||
735 | /** |
||
736 | * Return a date type-formatted string. |
||
737 | * |
||
738 | * @param array $values Contains a tokenised list of info about this data type |
||
739 | * @return string |
||
740 | */ |
||
741 | public function date($values) |
||
742 | { |
||
743 | return 'date null'; |
||
744 | } |
||
745 | |||
746 | /** |
||
747 | * Return a decimal type-formatted string |
||
748 | * |
||
749 | * @param array $values Contains a tokenised list of info about this data type |
||
750 | * @return string |
||
751 | */ |
||
752 | public function decimal($values) |
||
753 | { |
||
754 | // Avoid empty strings being put in the db |
||
755 | if ($values['precision'] == '') { |
||
756 | $precision = 1; |
||
757 | } else { |
||
758 | $precision = $values['precision']; |
||
759 | } |
||
760 | |||
761 | $defaultValue = '0'; |
||
762 | if (isset($values['default']) && is_numeric($values['default'])) { |
||
763 | $defaultValue = $values['default']; |
||
764 | } |
||
765 | |||
766 | return "decimal($precision) not null default $defaultValue"; |
||
767 | } |
||
768 | |||
769 | /** |
||
770 | * Return a enum type-formatted string |
||
771 | * |
||
772 | * @param array $values Contains a tokenised list of info about this data type |
||
773 | * @return string |
||
774 | */ |
||
775 | public function enum($values) |
||
776 | { |
||
777 | // Enums are a bit different. We'll be creating a varchar(255) with a constraint of all the |
||
778 | // usual enum options. |
||
779 | // NOTE: In this one instance, we are including the table name in the values array |
||
780 | |||
781 | $maxLength = max(array_map('strlen', $values['enums'])); |
||
782 | |||
783 | return "varchar($maxLength) not null default '" . $values['default'] |
||
784 | . "' check(\"" . $values['name'] . "\" in ('" . implode("','", $values['enums']) |
||
785 | . "'))"; |
||
786 | } |
||
787 | |||
788 | /** |
||
789 | * @todo Make this work like {@link MySQLDatabase::set()} |
||
790 | * |
||
791 | * @param array $values |
||
792 | * @return string |
||
793 | */ |
||
794 | public function set($values) |
||
795 | { |
||
796 | return $this->enum($values); |
||
797 | } |
||
798 | |||
799 | /** |
||
800 | * Return a float type-formatted string. |
||
801 | * |
||
802 | * @param array $values Contains a tokenised list of info about this data type |
||
803 | * @return string |
||
804 | */ |
||
805 | public function float($values) |
||
806 | { |
||
807 | return 'float(53) not null default ' . $values['default']; |
||
808 | } |
||
809 | |||
810 | /** |
||
811 | * Return a int type-formatted string |
||
812 | * |
||
813 | * @param array $values Contains a tokenised list of info about this data type |
||
814 | * @return string |
||
815 | */ |
||
816 | public function int($values) |
||
817 | { |
||
818 | return 'int not null default ' . (int) $values['default']; |
||
819 | } |
||
820 | |||
821 | /** |
||
822 | * Return a bigint type-formatted string |
||
823 | * |
||
824 | * @param array $values Contains a tokenised list of info about this data type |
||
825 | * @return string |
||
826 | */ |
||
827 | public function bigint($values) |
||
828 | { |
||
829 | return 'bigint not null default ' . (int) $values['default']; |
||
830 | } |
||
831 | |||
832 | /** |
||
833 | * Return a datetime type-formatted string |
||
834 | * For MS SQL, we simply return the word 'timestamp', no other parameters are necessary |
||
835 | * |
||
836 | * @param array $values Contains a tokenised list of info about this data type |
||
837 | * @return string |
||
838 | */ |
||
839 | public function datetime($values) |
||
840 | { |
||
841 | return 'datetime null'; |
||
842 | } |
||
843 | |||
844 | /** |
||
845 | * Return a text type-formatted string |
||
846 | * |
||
847 | * @param array $values Contains a tokenised list of info about this data type |
||
848 | * @return string |
||
849 | */ |
||
850 | public function text($values) |
||
851 | { |
||
852 | $collation = MSSQLDatabase::get_collation(); |
||
853 | $collationSQL = $collation ? " COLLATE $collation" : ""; |
||
854 | return "nvarchar(max)$collationSQL null"; |
||
855 | } |
||
856 | |||
857 | /** |
||
858 | * Return a time type-formatted string. |
||
859 | * |
||
860 | * @param array $values Contains a tokenised list of info about this data type |
||
861 | * @return string |
||
862 | */ |
||
863 | public function time($values) |
||
864 | { |
||
865 | return 'time null'; |
||
866 | } |
||
867 | |||
868 | /** |
||
869 | * Return a varchar type-formatted string |
||
870 | * |
||
871 | * @param array $values Contains a tokenised list of info about this data type |
||
872 | * @return string |
||
873 | */ |
||
874 | public function varchar($values) |
||
875 | { |
||
876 | $collation = MSSQLDatabase::get_collation(); |
||
877 | $collationSQL = $collation ? " COLLATE $collation" : ""; |
||
878 | return "nvarchar(" . $values['precision'] . ")$collationSQL null"; |
||
879 | } |
||
880 | |||
881 | /** |
||
882 | * Return a 4 digit numeric type. |
||
883 | * |
||
884 | * @param array $values |
||
885 | * @return string |
||
886 | */ |
||
887 | public function year($values) |
||
888 | { |
||
889 | return 'numeric(4)'; |
||
890 | } |
||
891 | |||
892 | /** |
||
893 | * This returns the column which is the primary key for each table |
||
894 | * |
||
895 | * @param bool $asDbValue |
||
896 | * @param bool $hasAutoIncPK |
||
897 | * @return string |
||
898 | */ |
||
899 | public function IdColumn($asDbValue = false, $hasAutoIncPK = true) |
||
900 | { |
||
901 | if ($asDbValue) { |
||
902 | return 'int not null'; |
||
903 | } elseif ($hasAutoIncPK) { |
||
904 | return 'int identity(1,1)'; |
||
905 | } else { |
||
906 | return 'int not null'; |
||
907 | } |
||
908 | } |
||
909 | |||
910 | public function hasTable($tableName) |
||
911 | { |
||
912 | return (bool)$this->preparedQuery( |
||
913 | "SELECT table_name FROM information_schema.tables WHERE table_name = ?", |
||
914 | array($tableName) |
||
915 | )->value(); |
||
916 | } |
||
917 | |||
918 | /** |
||
919 | * Returns the values of the given enum field |
||
920 | * NOTE: Experimental; introduced for db-abstraction and may changed before 2.4 is released. |
||
921 | * |
||
922 | * @param string $tableName |
||
923 | * @param string $fieldName |
||
924 | * @return array |
||
925 | */ |
||
926 | public function enumValuesForField($tableName, $fieldName) |
||
927 | { |
||
928 | $classes = array(); |
||
929 | |||
930 | // Get the enum of all page types from the SiteTree table |
||
931 | $clause = $this->getConstraintCheckClause($tableName, $fieldName); |
||
932 | if ($clause) { |
||
933 | $classes = $this->enumValuesFromCheckClause($clause); |
||
934 | } |
||
935 | |||
936 | return $classes; |
||
937 | } |
||
938 | |||
939 | /** |
||
940 | * This is a lookup table for data types. |
||
941 | * |
||
942 | * For instance, MSSQL uses 'BIGINT', while MySQL uses 'UNSIGNED' |
||
943 | * and PostgreSQL uses 'INT'. |
||
944 | * |
||
945 | * @param string $type |
||
946 | * @return string |
||
947 | */ |
||
948 | public function dbDataType($type) |
||
949 | { |
||
950 | $values = array( |
||
951 | 'unsigned integer'=>'BIGINT' |
||
952 | ); |
||
953 | if (isset($values[$type])) { |
||
954 | return $values[$type]; |
||
955 | } else { |
||
956 | return ''; |
||
957 | } |
||
958 | } |
||
959 | |||
960 | protected function indexKey($table, $index, $spec) |
||
961 | { |
||
962 | return $index; |
||
963 | } |
||
964 | } |
||
965 |