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