1
|
|
|
<?php |
2
|
|
|
/** |
3
|
|
|
* CakePHP(tm) : Rapid Development Framework (https://cakephp.org) |
4
|
|
|
* Copyright (c) Cake Software Foundation, Inc. (https://cakefoundation.org) |
5
|
|
|
* |
6
|
|
|
* Licensed under The MIT License |
7
|
|
|
* For full copyright and license information, please see the LICENSE.txt |
8
|
|
|
* Redistributions of files must retain the above copyright notice. |
9
|
|
|
* |
10
|
|
|
* @copyright Copyright (c) Cake Software Foundation, Inc. (https://cakefoundation.org) |
11
|
|
|
* @link https://cakephp.org CakePHP(tm) Project |
12
|
|
|
* @since 3.0.0 |
13
|
|
|
* @license https://opensource.org/licenses/mit-license.php MIT License |
14
|
|
|
*/ |
15
|
|
|
namespace Cake\Database\Schema; |
16
|
|
|
|
17
|
|
|
/** |
18
|
|
|
* Schema management/reflection features for SQLServer. |
19
|
|
|
*/ |
20
|
|
|
class SqlserverSchema extends BaseSchema |
21
|
|
|
{ |
22
|
|
|
const DEFAULT_SCHEMA_NAME = 'dbo'; |
23
|
|
|
|
24
|
|
|
/** |
25
|
|
|
* {@inheritDoc} |
26
|
|
|
*/ |
27
|
|
|
public function listTablesSql($config) |
28
|
|
|
{ |
29
|
|
|
$sql = "SELECT TABLE_NAME |
30
|
|
|
FROM INFORMATION_SCHEMA.TABLES |
31
|
|
|
WHERE TABLE_SCHEMA = ? |
32
|
|
|
AND (TABLE_TYPE = 'BASE TABLE' OR TABLE_TYPE = 'VIEW') |
33
|
|
|
ORDER BY TABLE_NAME"; |
34
|
|
|
$schema = empty($config['schema']) ? static::DEFAULT_SCHEMA_NAME : $config['schema']; |
35
|
|
|
|
36
|
|
|
return [$sql, [$schema]]; |
37
|
|
|
} |
38
|
|
|
|
39
|
|
|
/** |
40
|
|
|
* {@inheritDoc} |
41
|
|
|
*/ |
42
|
|
View Code Duplication |
public function describeColumnSql($tableName, $config) |
43
|
|
|
{ |
44
|
|
|
$sql = 'SELECT DISTINCT |
45
|
|
|
AC.column_id AS [column_id], |
46
|
|
|
AC.name AS [name], |
47
|
|
|
TY.name AS [type], |
48
|
|
|
AC.max_length AS [char_length], |
49
|
|
|
AC.precision AS [precision], |
50
|
|
|
AC.scale AS [scale], |
51
|
|
|
AC.is_identity AS [autoincrement], |
52
|
|
|
AC.is_nullable AS [null], |
53
|
|
|
OBJECT_DEFINITION(AC.default_object_id) AS [default], |
54
|
|
|
AC.collation_name AS [collation_name] |
55
|
|
|
FROM sys.[objects] T |
56
|
|
|
INNER JOIN sys.[schemas] S ON S.[schema_id] = T.[schema_id] |
57
|
|
|
INNER JOIN sys.[all_columns] AC ON T.[object_id] = AC.[object_id] |
58
|
|
|
INNER JOIN sys.[types] TY ON TY.[user_type_id] = AC.[user_type_id] |
59
|
|
|
WHERE T.[name] = ? AND S.[name] = ? |
60
|
|
|
ORDER BY column_id'; |
61
|
|
|
|
62
|
|
|
$schema = empty($config['schema']) ? static::DEFAULT_SCHEMA_NAME : $config['schema']; |
63
|
|
|
|
64
|
|
|
return [$sql, [$tableName, $schema]]; |
65
|
|
|
} |
66
|
|
|
|
67
|
|
|
/** |
68
|
|
|
* Convert a column definition to the abstract types. |
69
|
|
|
* |
70
|
|
|
* The returned type will be a type that |
71
|
|
|
* Cake\Database\Type can handle. |
72
|
|
|
* |
73
|
|
|
* @param string $col The column type |
74
|
|
|
* @param int|null $length the column length |
75
|
|
|
* @param int|null $precision The column precision |
76
|
|
|
* @param int|null $scale The column scale |
77
|
|
|
* @return array Array of column information. |
78
|
|
|
* @link https://technet.microsoft.com/en-us/library/ms187752.aspx |
79
|
|
|
*/ |
80
|
|
|
protected function _convertColumn($col, $length = null, $precision = null, $scale = null) |
81
|
|
|
{ |
82
|
|
|
$col = strtolower($col); |
83
|
|
|
$length = $length !== null ? (int)$length : $length; |
84
|
|
|
$precision = $precision !== null ? (int)$precision : $precision; |
85
|
|
|
$scale = $scale !== null ? (int)$scale : $scale; |
86
|
|
|
|
87
|
|
View Code Duplication |
if (in_array($col, ['date', 'time'])) { |
88
|
|
|
return ['type' => $col, 'length' => null]; |
89
|
|
|
} |
90
|
|
|
if (strpos($col, 'datetime') !== false) { |
91
|
|
|
return ['type' => TableSchema::TYPE_TIMESTAMP, 'length' => null]; |
92
|
|
|
} |
93
|
|
|
|
94
|
|
|
if ($col === 'tinyint') { |
95
|
|
|
return ['type' => TableSchema::TYPE_TINYINTEGER, 'length' => $precision ?: 3]; |
96
|
|
|
} |
97
|
|
|
if ($col === 'smallint') { |
98
|
|
|
return ['type' => TableSchema::TYPE_SMALLINTEGER, 'length' => $precision ?: 5]; |
99
|
|
|
} |
100
|
|
|
if ($col === 'int' || $col === 'integer') { |
101
|
|
|
return ['type' => TableSchema::TYPE_INTEGER, 'length' => $precision ?: 10]; |
102
|
|
|
} |
103
|
|
|
if ($col === 'bigint') { |
104
|
|
|
return ['type' => TableSchema::TYPE_BIGINTEGER, 'length' => $precision ?: 20]; |
105
|
|
|
} |
106
|
|
|
if ($col === 'bit') { |
107
|
|
|
return ['type' => TableSchema::TYPE_BOOLEAN, 'length' => null]; |
108
|
|
|
} |
109
|
|
View Code Duplication |
if ( |
110
|
|
|
strpos($col, 'numeric') !== false || |
111
|
|
|
strpos($col, 'money') !== false || |
112
|
|
|
strpos($col, 'decimal') !== false |
113
|
|
|
) { |
114
|
|
|
return ['type' => TableSchema::TYPE_DECIMAL, 'length' => $precision, 'precision' => $scale]; |
115
|
|
|
} |
116
|
|
|
|
117
|
|
|
if ($col === 'real' || $col === 'float') { |
118
|
|
|
return ['type' => TableSchema::TYPE_FLOAT, 'length' => null]; |
119
|
|
|
} |
120
|
|
|
// SqlServer schema reflection returns double length for unicode |
121
|
|
|
// columns because internally it uses UTF16/UCS2 |
122
|
|
|
if ($col === 'nvarchar' || $col === 'nchar' || $col === 'ntext') { |
123
|
|
|
$length /= 2; |
124
|
|
|
} |
125
|
|
|
if (strpos($col, 'varchar') !== false && $length < 0) { |
126
|
|
|
return ['type' => TableSchema::TYPE_TEXT, 'length' => null]; |
127
|
|
|
} |
128
|
|
|
|
129
|
|
|
if (strpos($col, 'varchar') !== false) { |
130
|
|
|
return ['type' => TableSchema::TYPE_STRING, 'length' => $length ?: 255]; |
131
|
|
|
} |
132
|
|
|
|
133
|
|
View Code Duplication |
if (strpos($col, 'char') !== false) { |
134
|
|
|
return ['type' => TableSchema::TYPE_STRING, 'fixed' => true, 'length' => $length]; |
135
|
|
|
} |
136
|
|
|
|
137
|
|
|
if (strpos($col, 'text') !== false) { |
138
|
|
|
return ['type' => TableSchema::TYPE_TEXT, 'length' => null]; |
139
|
|
|
} |
140
|
|
|
|
141
|
|
|
if ($col === 'image' || strpos($col, 'binary') !== false) { |
142
|
|
|
// -1 is the value for MAX which we treat as a 'long' binary |
143
|
|
|
if ($length == -1) { |
144
|
|
|
$length = TableSchema::LENGTH_LONG; |
145
|
|
|
} |
146
|
|
|
|
147
|
|
|
return ['type' => TableSchema::TYPE_BINARY, 'length' => $length]; |
148
|
|
|
} |
149
|
|
|
|
150
|
|
|
if ($col === 'uniqueidentifier') { |
151
|
|
|
return ['type' => TableSchema::TYPE_UUID]; |
152
|
|
|
} |
153
|
|
|
|
154
|
|
|
return ['type' => TableSchema::TYPE_STRING, 'length' => null]; |
155
|
|
|
} |
156
|
|
|
|
157
|
|
|
/** |
158
|
|
|
* {@inheritDoc} |
159
|
|
|
*/ |
160
|
|
|
public function convertColumnDescription(TableSchema $schema, $row) |
161
|
|
|
{ |
162
|
|
|
$field = $this->_convertColumn( |
163
|
|
|
$row['type'], |
164
|
|
|
$row['char_length'], |
165
|
|
|
$row['precision'], |
166
|
|
|
$row['scale'] |
167
|
|
|
); |
168
|
|
|
if (!empty($row['default'])) { |
169
|
|
|
$row['default'] = trim($row['default'], '()'); |
170
|
|
|
} |
171
|
|
|
if (!empty($row['autoincrement'])) { |
172
|
|
|
$field['autoIncrement'] = true; |
173
|
|
|
} |
174
|
|
|
if ($field['type'] === TableSchema::TYPE_BOOLEAN) { |
175
|
|
|
$row['default'] = (int)$row['default']; |
176
|
|
|
} |
177
|
|
|
|
178
|
|
|
$field += [ |
179
|
|
|
'null' => $row['null'] === '1', |
180
|
|
|
'default' => $this->_defaultValue($row['default']), |
181
|
|
|
'collate' => $row['collation_name'], |
182
|
|
|
]; |
183
|
|
|
$schema->addColumn($row['name'], $field); |
184
|
|
|
} |
185
|
|
|
|
186
|
|
|
/** |
187
|
|
|
* Manipulate the default value. |
188
|
|
|
* |
189
|
|
|
* Sqlite includes quotes and bared NULLs in default values. |
190
|
|
|
* We need to remove those. |
191
|
|
|
* |
192
|
|
|
* @param string|null $default The default value. |
193
|
|
|
* @return string|null |
194
|
|
|
*/ |
195
|
|
View Code Duplication |
protected function _defaultValue($default) |
196
|
|
|
{ |
197
|
|
|
if ($default === 'NULL') { |
198
|
|
|
return null; |
199
|
|
|
} |
200
|
|
|
|
201
|
|
|
// Remove quotes |
202
|
|
|
if (preg_match("/^N?'(.*)'/", $default, $matches)) { |
203
|
|
|
return str_replace("''", "'", $matches[1]); |
204
|
|
|
} |
205
|
|
|
|
206
|
|
|
return $default; |
207
|
|
|
} |
208
|
|
|
|
209
|
|
|
/** |
210
|
|
|
* {@inheritDoc} |
211
|
|
|
*/ |
212
|
|
View Code Duplication |
public function describeIndexSql($tableName, $config) |
213
|
|
|
{ |
214
|
|
|
$sql = "SELECT |
215
|
|
|
I.[name] AS [index_name], |
216
|
|
|
IC.[index_column_id] AS [index_order], |
217
|
|
|
AC.[name] AS [column_name], |
218
|
|
|
I.[is_unique], I.[is_primary_key], |
219
|
|
|
I.[is_unique_constraint] |
220
|
|
|
FROM sys.[tables] AS T |
221
|
|
|
INNER JOIN sys.[schemas] S ON S.[schema_id] = T.[schema_id] |
222
|
|
|
INNER JOIN sys.[indexes] I ON T.[object_id] = I.[object_id] |
223
|
|
|
INNER JOIN sys.[index_columns] IC ON I.[object_id] = IC.[object_id] AND I.[index_id] = IC.[index_id] |
224
|
|
|
INNER JOIN sys.[all_columns] AC ON T.[object_id] = AC.[object_id] AND IC.[column_id] = AC.[column_id] |
225
|
|
|
WHERE T.[is_ms_shipped] = 0 AND I.[type_desc] <> 'HEAP' AND T.[name] = ? AND S.[name] = ? |
226
|
|
|
ORDER BY I.[index_id], IC.[index_column_id]"; |
227
|
|
|
|
228
|
|
|
$schema = empty($config['schema']) ? static::DEFAULT_SCHEMA_NAME : $config['schema']; |
229
|
|
|
|
230
|
|
|
return [$sql, [$tableName, $schema]]; |
231
|
|
|
} |
232
|
|
|
|
233
|
|
|
/** |
234
|
|
|
* {@inheritDoc} |
235
|
|
|
*/ |
236
|
|
|
public function convertIndexDescription(TableSchema $schema, $row) |
237
|
|
|
{ |
238
|
|
|
$type = TableSchema::INDEX_INDEX; |
239
|
|
|
$name = $row['index_name']; |
240
|
|
|
if ($row['is_primary_key']) { |
241
|
|
|
$name = $type = TableSchema::CONSTRAINT_PRIMARY; |
242
|
|
|
} |
243
|
|
|
if ($row['is_unique_constraint'] && $type === TableSchema::INDEX_INDEX) { |
244
|
|
|
$type = TableSchema::CONSTRAINT_UNIQUE; |
245
|
|
|
} |
246
|
|
|
|
247
|
|
|
if ($type === TableSchema::INDEX_INDEX) { |
248
|
|
|
$existing = $schema->getIndex($name); |
249
|
|
|
} else { |
250
|
|
|
$existing = $schema->getConstraint($name); |
251
|
|
|
} |
252
|
|
|
|
253
|
|
|
$columns = [$row['column_name']]; |
254
|
|
|
if (!empty($existing)) { |
255
|
|
|
$columns = array_merge($existing['columns'], $columns); |
256
|
|
|
} |
257
|
|
|
|
258
|
|
|
if ($type === TableSchema::CONSTRAINT_PRIMARY || $type === TableSchema::CONSTRAINT_UNIQUE) { |
259
|
|
|
$schema->addConstraint($name, [ |
260
|
|
|
'type' => $type, |
261
|
|
|
'columns' => $columns, |
262
|
|
|
]); |
263
|
|
|
|
264
|
|
|
return; |
265
|
|
|
} |
266
|
|
|
$schema->addIndex($name, [ |
267
|
|
|
'type' => $type, |
268
|
|
|
'columns' => $columns, |
269
|
|
|
]); |
270
|
|
|
} |
271
|
|
|
|
272
|
|
|
/** |
273
|
|
|
* {@inheritDoc} |
274
|
|
|
*/ |
275
|
|
View Code Duplication |
public function describeForeignKeySql($tableName, $config) |
276
|
|
|
{ |
277
|
|
|
$sql = 'SELECT FK.[name] AS [foreign_key_name], FK.[delete_referential_action_desc] AS [delete_type], |
278
|
|
|
FK.[update_referential_action_desc] AS [update_type], C.name AS [column], RT.name AS [reference_table], |
279
|
|
|
RC.name AS [reference_column] |
280
|
|
|
FROM sys.foreign_keys FK |
281
|
|
|
INNER JOIN sys.foreign_key_columns FKC ON FKC.constraint_object_id = FK.object_id |
282
|
|
|
INNER JOIN sys.tables T ON T.object_id = FKC.parent_object_id |
283
|
|
|
INNER JOIN sys.tables RT ON RT.object_id = FKC.referenced_object_id |
284
|
|
|
INNER JOIN sys.schemas S ON S.schema_id = T.schema_id AND S.schema_id = RT.schema_id |
285
|
|
|
INNER JOIN sys.columns C ON C.column_id = FKC.parent_column_id AND C.object_id = FKC.parent_object_id |
286
|
|
|
INNER JOIN sys.columns RC ON RC.column_id = FKC.referenced_column_id AND RC.object_id = FKC.referenced_object_id |
287
|
|
|
WHERE FK.is_ms_shipped = 0 AND T.name = ? AND S.name = ?'; |
288
|
|
|
|
289
|
|
|
$schema = empty($config['schema']) ? static::DEFAULT_SCHEMA_NAME : $config['schema']; |
290
|
|
|
|
291
|
|
|
return [$sql, [$tableName, $schema]]; |
292
|
|
|
} |
293
|
|
|
|
294
|
|
|
/** |
295
|
|
|
* {@inheritDoc} |
296
|
|
|
*/ |
297
|
|
View Code Duplication |
public function convertForeignKeyDescription(TableSchema $schema, $row) |
298
|
|
|
{ |
299
|
|
|
$data = [ |
300
|
|
|
'type' => TableSchema::CONSTRAINT_FOREIGN, |
301
|
|
|
'columns' => [$row['column']], |
302
|
|
|
'references' => [$row['reference_table'], $row['reference_column']], |
303
|
|
|
'update' => $this->_convertOnClause($row['update_type']), |
304
|
|
|
'delete' => $this->_convertOnClause($row['delete_type']), |
305
|
|
|
]; |
306
|
|
|
$name = $row['foreign_key_name']; |
307
|
|
|
$schema->addConstraint($name, $data); |
308
|
|
|
} |
309
|
|
|
|
310
|
|
|
/** |
311
|
|
|
* {@inheritDoc} |
312
|
|
|
*/ |
313
|
|
|
protected function _foreignOnClause($on) |
314
|
|
|
{ |
315
|
|
|
$parent = parent::_foreignOnClause($on); |
316
|
|
|
|
317
|
|
|
return $parent === 'RESTRICT' ? parent::_foreignOnClause(TableSchema::ACTION_SET_NULL) : $parent; |
318
|
|
|
} |
319
|
|
|
|
320
|
|
|
/** |
321
|
|
|
* {@inheritDoc} |
322
|
|
|
*/ |
323
|
|
|
protected function _convertOnClause($clause) |
324
|
|
|
{ |
325
|
|
|
switch ($clause) { |
326
|
|
|
case 'NO_ACTION': |
327
|
|
|
return TableSchema::ACTION_NO_ACTION; |
328
|
|
|
case 'CASCADE': |
329
|
|
|
return TableSchema::ACTION_CASCADE; |
330
|
|
|
case 'SET_NULL': |
331
|
|
|
return TableSchema::ACTION_SET_NULL; |
332
|
|
|
case 'SET_DEFAULT': |
333
|
|
|
return TableSchema::ACTION_SET_DEFAULT; |
334
|
|
|
} |
335
|
|
|
|
336
|
|
|
return TableSchema::ACTION_SET_NULL; |
337
|
|
|
} |
338
|
|
|
|
339
|
|
|
/** |
340
|
|
|
* {@inheritDoc} |
341
|
|
|
*/ |
342
|
|
|
public function columnSql(TableSchema $schema, $name) |
343
|
|
|
{ |
344
|
|
|
$data = $schema->getColumn($name); |
345
|
|
|
$out = $this->_driver->quoteIdentifier($name); |
346
|
|
|
$typeMap = [ |
347
|
|
|
TableSchema::TYPE_TINYINTEGER => ' TINYINT', |
348
|
|
|
TableSchema::TYPE_SMALLINTEGER => ' SMALLINT', |
349
|
|
|
TableSchema::TYPE_INTEGER => ' INTEGER', |
350
|
|
|
TableSchema::TYPE_BIGINTEGER => ' BIGINT', |
351
|
|
|
TableSchema::TYPE_BINARY_UUID => ' UNIQUEIDENTIFIER', |
352
|
|
|
TableSchema::TYPE_BOOLEAN => ' BIT', |
353
|
|
|
TableSchema::TYPE_FLOAT => ' FLOAT', |
354
|
|
|
TableSchema::TYPE_DECIMAL => ' DECIMAL', |
355
|
|
|
TableSchema::TYPE_DATE => ' DATE', |
356
|
|
|
TableSchema::TYPE_TIME => ' TIME', |
357
|
|
|
TableSchema::TYPE_DATETIME => ' DATETIME', |
358
|
|
|
TableSchema::TYPE_TIMESTAMP => ' DATETIME', |
359
|
|
|
TableSchema::TYPE_UUID => ' UNIQUEIDENTIFIER', |
360
|
|
|
TableSchema::TYPE_JSON => ' NVARCHAR(MAX)', |
361
|
|
|
]; |
362
|
|
|
|
363
|
|
|
if (isset($typeMap[$data['type']])) { |
364
|
|
|
$out .= $typeMap[$data['type']]; |
365
|
|
|
} |
366
|
|
|
|
367
|
|
|
if ($data['type'] === TableSchema::TYPE_INTEGER || $data['type'] === TableSchema::TYPE_BIGINTEGER) { |
368
|
|
|
if ([$name] === $schema->primaryKey() || $data['autoIncrement'] === true) { |
369
|
|
|
unset($data['null'], $data['default']); |
370
|
|
|
$out .= ' IDENTITY(1, 1)'; |
371
|
|
|
} |
372
|
|
|
} |
373
|
|
|
|
374
|
|
View Code Duplication |
if ($data['type'] === TableSchema::TYPE_TEXT && $data['length'] !== TableSchema::LENGTH_TINY) { |
375
|
|
|
$out .= ' NVARCHAR(MAX)'; |
376
|
|
|
} |
377
|
|
|
|
378
|
|
|
if ($data['type'] === TableSchema::TYPE_BINARY) { |
379
|
|
|
if ( |
380
|
|
|
!isset($data['length']) |
381
|
|
|
|| in_array($data['length'], [TableSchema::LENGTH_MEDIUM, TableSchema::LENGTH_LONG], true) |
382
|
|
|
) { |
383
|
|
|
$data['length'] = 'MAX'; |
384
|
|
|
} |
385
|
|
|
|
386
|
|
|
if ($data['length'] === 1) { |
387
|
|
|
$out .= ' BINARY(1)'; |
388
|
|
|
} else { |
389
|
|
|
$out .= ' VARBINARY'; |
390
|
|
|
|
391
|
|
|
$out .= sprintf('(%s)', $data['length']); |
392
|
|
|
} |
393
|
|
|
} |
394
|
|
|
|
395
|
|
|
if ( |
396
|
|
|
$data['type'] === TableSchema::TYPE_STRING || |
397
|
|
|
($data['type'] === TableSchema::TYPE_TEXT && $data['length'] === TableSchema::LENGTH_TINY) |
398
|
|
|
) { |
399
|
|
|
$type = ' NVARCHAR'; |
400
|
|
|
|
401
|
|
|
if (!empty($data['fixed'])) { |
402
|
|
|
$type = ' NCHAR'; |
403
|
|
|
} |
404
|
|
|
|
405
|
|
|
if (!isset($data['length'])) { |
406
|
|
|
$data['length'] = 255; |
407
|
|
|
} |
408
|
|
|
|
409
|
|
|
$out .= sprintf('%s(%d)', $type, $data['length']); |
410
|
|
|
} |
411
|
|
|
|
412
|
|
|
$hasCollate = [TableSchema::TYPE_TEXT, TableSchema::TYPE_STRING]; |
413
|
|
View Code Duplication |
if (in_array($data['type'], $hasCollate, true) && isset($data['collate']) && $data['collate'] !== '') { |
414
|
|
|
$out .= ' COLLATE ' . $data['collate']; |
415
|
|
|
} |
416
|
|
|
|
417
|
|
View Code Duplication |
if ($data['type'] === TableSchema::TYPE_FLOAT && isset($data['precision'])) { |
418
|
|
|
$out .= '(' . (int)$data['precision'] . ')'; |
419
|
|
|
} |
420
|
|
|
|
421
|
|
View Code Duplication |
if ( |
422
|
|
|
$data['type'] === TableSchema::TYPE_DECIMAL && |
423
|
|
|
(isset($data['length']) || isset($data['precision'])) |
424
|
|
|
) { |
425
|
|
|
$out .= '(' . (int)$data['length'] . ',' . (int)$data['precision'] . ')'; |
426
|
|
|
} |
427
|
|
|
|
428
|
|
|
if (isset($data['null']) && $data['null'] === false) { |
429
|
|
|
$out .= ' NOT NULL'; |
430
|
|
|
} |
431
|
|
|
|
432
|
|
|
if ( |
433
|
|
|
isset($data['default']) && |
434
|
|
|
in_array($data['type'], [TableSchema::TYPE_TIMESTAMP, TableSchema::TYPE_DATETIME]) && |
435
|
|
|
strtolower($data['default']) === 'current_timestamp' |
436
|
|
|
) { |
437
|
|
|
$out .= ' DEFAULT CURRENT_TIMESTAMP'; |
438
|
|
|
} elseif (isset($data['default'])) { |
439
|
|
|
$default = is_bool($data['default']) ? (int)$data['default'] : $this->_driver->schemaValue($data['default']); |
440
|
|
|
$out .= ' DEFAULT ' . $default; |
441
|
|
|
} elseif (isset($data['null']) && $data['null'] !== false) { |
442
|
|
|
$out .= ' DEFAULT NULL'; |
443
|
|
|
} |
444
|
|
|
|
445
|
|
|
return $out; |
446
|
|
|
} |
447
|
|
|
|
448
|
|
|
/** |
449
|
|
|
* {@inheritDoc} |
450
|
|
|
*/ |
451
|
|
View Code Duplication |
public function addConstraintSql(TableSchema $schema) |
452
|
|
|
{ |
453
|
|
|
$sqlPattern = 'ALTER TABLE %s ADD %s;'; |
454
|
|
|
$sql = []; |
455
|
|
|
|
456
|
|
|
foreach ($schema->constraints() as $name) { |
457
|
|
|
$constraint = $schema->getConstraint($name); |
458
|
|
|
if ($constraint['type'] === TableSchema::CONSTRAINT_FOREIGN) { |
459
|
|
|
$tableName = $this->_driver->quoteIdentifier($schema->name()); |
460
|
|
|
$sql[] = sprintf($sqlPattern, $tableName, $this->constraintSql($schema, $name)); |
461
|
|
|
} |
462
|
|
|
} |
463
|
|
|
|
464
|
|
|
return $sql; |
465
|
|
|
} |
466
|
|
|
|
467
|
|
|
/** |
468
|
|
|
* {@inheritDoc} |
469
|
|
|
*/ |
470
|
|
View Code Duplication |
public function dropConstraintSql(TableSchema $schema) |
471
|
|
|
{ |
472
|
|
|
$sqlPattern = 'ALTER TABLE %s DROP CONSTRAINT %s;'; |
473
|
|
|
$sql = []; |
474
|
|
|
|
475
|
|
|
foreach ($schema->constraints() as $name) { |
476
|
|
|
$constraint = $schema->getConstraint($name); |
477
|
|
|
if ($constraint['type'] === TableSchema::CONSTRAINT_FOREIGN) { |
478
|
|
|
$tableName = $this->_driver->quoteIdentifier($schema->name()); |
479
|
|
|
$constraintName = $this->_driver->quoteIdentifier($name); |
480
|
|
|
$sql[] = sprintf($sqlPattern, $tableName, $constraintName); |
481
|
|
|
} |
482
|
|
|
} |
483
|
|
|
|
484
|
|
|
return $sql; |
485
|
|
|
} |
486
|
|
|
|
487
|
|
|
/** |
488
|
|
|
* {@inheritDoc} |
489
|
|
|
*/ |
490
|
|
View Code Duplication |
public function indexSql(TableSchema $schema, $name) |
491
|
|
|
{ |
492
|
|
|
$data = $schema->getIndex($name); |
493
|
|
|
$columns = array_map( |
494
|
|
|
[$this->_driver, 'quoteIdentifier'], |
495
|
|
|
$data['columns'] |
496
|
|
|
); |
497
|
|
|
|
498
|
|
|
return sprintf( |
499
|
|
|
'CREATE INDEX %s ON %s (%s)', |
500
|
|
|
$this->_driver->quoteIdentifier($name), |
501
|
|
|
$this->_driver->quoteIdentifier($schema->name()), |
502
|
|
|
implode(', ', $columns) |
503
|
|
|
); |
504
|
|
|
} |
505
|
|
|
|
506
|
|
|
/** |
507
|
|
|
* {@inheritDoc} |
508
|
|
|
*/ |
509
|
|
View Code Duplication |
public function constraintSql(TableSchema $schema, $name) |
510
|
|
|
{ |
511
|
|
|
$data = $schema->getConstraint($name); |
512
|
|
|
$out = 'CONSTRAINT ' . $this->_driver->quoteIdentifier($name); |
513
|
|
|
if ($data['type'] === TableSchema::CONSTRAINT_PRIMARY) { |
514
|
|
|
$out = 'PRIMARY KEY'; |
515
|
|
|
} |
516
|
|
|
if ($data['type'] === TableSchema::CONSTRAINT_UNIQUE) { |
517
|
|
|
$out .= ' UNIQUE'; |
518
|
|
|
} |
519
|
|
|
|
520
|
|
|
return $this->_keySql($out, $data); |
521
|
|
|
} |
522
|
|
|
|
523
|
|
|
/** |
524
|
|
|
* Helper method for generating key SQL snippets. |
525
|
|
|
* |
526
|
|
|
* @param string $prefix The key prefix |
527
|
|
|
* @param array $data Key data. |
528
|
|
|
* @return string |
529
|
|
|
*/ |
530
|
|
View Code Duplication |
protected function _keySql($prefix, $data) |
531
|
|
|
{ |
532
|
|
|
$columns = array_map( |
533
|
|
|
[$this->_driver, 'quoteIdentifier'], |
534
|
|
|
$data['columns'] |
535
|
|
|
); |
536
|
|
|
if ($data['type'] === TableSchema::CONSTRAINT_FOREIGN) { |
537
|
|
|
return $prefix . sprintf( |
538
|
|
|
' FOREIGN KEY (%s) REFERENCES %s (%s) ON UPDATE %s ON DELETE %s', |
539
|
|
|
implode(', ', $columns), |
540
|
|
|
$this->_driver->quoteIdentifier($data['references'][0]), |
541
|
|
|
$this->_convertConstraintColumns($data['references'][1]), |
542
|
|
|
$this->_foreignOnClause($data['update']), |
543
|
|
|
$this->_foreignOnClause($data['delete']) |
544
|
|
|
); |
545
|
|
|
} |
546
|
|
|
|
547
|
|
|
return $prefix . ' (' . implode(', ', $columns) . ')'; |
548
|
|
|
} |
549
|
|
|
|
550
|
|
|
/** |
551
|
|
|
* {@inheritDoc} |
552
|
|
|
*/ |
553
|
|
|
public function createTableSql(TableSchema $schema, $columns, $constraints, $indexes) |
554
|
|
|
{ |
555
|
|
|
$content = array_merge($columns, $constraints); |
556
|
|
|
$content = implode(",\n", array_filter($content)); |
557
|
|
|
$tableName = $this->_driver->quoteIdentifier($schema->name()); |
558
|
|
|
$out = []; |
559
|
|
|
$out[] = sprintf("CREATE TABLE %s (\n%s\n)", $tableName, $content); |
560
|
|
|
foreach ($indexes as $index) { |
561
|
|
|
$out[] = $index; |
562
|
|
|
} |
563
|
|
|
|
564
|
|
|
return $out; |
565
|
|
|
} |
566
|
|
|
|
567
|
|
|
/** |
568
|
|
|
* {@inheritDoc} |
569
|
|
|
*/ |
570
|
|
|
public function truncateTableSql(TableSchema $schema) |
571
|
|
|
{ |
572
|
|
|
$name = $this->_driver->quoteIdentifier($schema->name()); |
573
|
|
|
$queries = [ |
574
|
|
|
sprintf('DELETE FROM %s', $name), |
575
|
|
|
]; |
576
|
|
|
|
577
|
|
|
// Restart identity sequences |
578
|
|
|
$pk = $schema->primaryKey(); |
579
|
|
|
if (count($pk) === 1) { |
580
|
|
|
$column = $schema->getColumn($pk[0]); |
581
|
|
|
if (in_array($column['type'], ['integer', 'biginteger'])) { |
582
|
|
|
$queries[] = sprintf( |
583
|
|
|
"DBCC CHECKIDENT('%s', RESEED, 0)", |
584
|
|
|
$schema->name() |
585
|
|
|
); |
586
|
|
|
} |
587
|
|
|
} |
588
|
|
|
|
589
|
|
|
return $queries; |
590
|
|
|
} |
591
|
|
|
} |
592
|
|
|
|