1
|
|
|
<?php |
2
|
|
|
/** |
3
|
|
|
* @link https://www.yiiframework.com/ |
4
|
|
|
* @copyright Copyright (c) 2008 Yii Software LLC |
5
|
|
|
* @license https://www.yiiframework.com/license/ |
6
|
|
|
*/ |
7
|
|
|
|
8
|
|
|
namespace yii\db\mssql; |
9
|
|
|
|
10
|
|
|
use Yii; |
11
|
|
|
use yii\db\CheckConstraint; |
12
|
|
|
use yii\db\Constraint; |
13
|
|
|
use yii\db\ConstraintFinderInterface; |
14
|
|
|
use yii\db\ConstraintFinderTrait; |
15
|
|
|
use yii\db\DefaultValueConstraint; |
16
|
|
|
use yii\db\ForeignKeyConstraint; |
17
|
|
|
use yii\db\IndexConstraint; |
18
|
|
|
use yii\db\ViewFinderTrait; |
19
|
|
|
use yii\helpers\ArrayHelper; |
20
|
|
|
|
21
|
|
|
/** |
22
|
|
|
* Schema is the class for retrieving metadata from MS SQL Server databases (version 2008 and above). |
23
|
|
|
* |
24
|
|
|
* @author Timur Ruziev <[email protected]> |
25
|
|
|
* @since 2.0 |
26
|
|
|
*/ |
27
|
|
|
class Schema extends \yii\db\Schema implements ConstraintFinderInterface |
28
|
|
|
{ |
29
|
|
|
use ViewFinderTrait; |
30
|
|
|
use ConstraintFinderTrait; |
31
|
|
|
|
32
|
|
|
/** |
33
|
|
|
* {@inheritdoc} |
34
|
|
|
*/ |
35
|
|
|
public $columnSchemaClass = 'yii\db\mssql\ColumnSchema'; |
36
|
|
|
/** |
37
|
|
|
* @var string the default schema used for the current session. |
38
|
|
|
*/ |
39
|
|
|
public $defaultSchema = 'dbo'; |
40
|
|
|
/** |
41
|
|
|
* @var array mapping from physical column types (keys) to abstract column types (values) |
42
|
|
|
*/ |
43
|
|
|
public $typeMap = [ |
44
|
|
|
// exact numbers |
45
|
|
|
'bigint' => self::TYPE_BIGINT, |
46
|
|
|
'numeric' => self::TYPE_DECIMAL, |
47
|
|
|
'bit' => self::TYPE_SMALLINT, |
48
|
|
|
'smallint' => self::TYPE_SMALLINT, |
49
|
|
|
'decimal' => self::TYPE_DECIMAL, |
50
|
|
|
'smallmoney' => self::TYPE_MONEY, |
51
|
|
|
'int' => self::TYPE_INTEGER, |
52
|
|
|
'tinyint' => self::TYPE_TINYINT, |
53
|
|
|
'money' => self::TYPE_MONEY, |
54
|
|
|
// approximate numbers |
55
|
|
|
'float' => self::TYPE_FLOAT, |
56
|
|
|
'double' => self::TYPE_DOUBLE, |
57
|
|
|
'real' => self::TYPE_FLOAT, |
58
|
|
|
// date and time |
59
|
|
|
'date' => self::TYPE_DATE, |
60
|
|
|
'datetimeoffset' => self::TYPE_DATETIME, |
61
|
|
|
'datetime2' => self::TYPE_DATETIME, |
62
|
|
|
'smalldatetime' => self::TYPE_DATETIME, |
63
|
|
|
'datetime' => self::TYPE_DATETIME, |
64
|
|
|
'time' => self::TYPE_TIME, |
65
|
|
|
// character strings |
66
|
|
|
'char' => self::TYPE_CHAR, |
67
|
|
|
'varchar' => self::TYPE_STRING, |
68
|
|
|
'text' => self::TYPE_TEXT, |
69
|
|
|
// unicode character strings |
70
|
|
|
'nchar' => self::TYPE_CHAR, |
71
|
|
|
'nvarchar' => self::TYPE_STRING, |
72
|
|
|
'ntext' => self::TYPE_TEXT, |
73
|
|
|
// binary strings |
74
|
|
|
'binary' => self::TYPE_BINARY, |
75
|
|
|
'varbinary' => self::TYPE_BINARY, |
76
|
|
|
'image' => self::TYPE_BINARY, |
77
|
|
|
// other data types |
78
|
|
|
// 'cursor' type cannot be used with tables |
79
|
|
|
'timestamp' => self::TYPE_TIMESTAMP, |
80
|
|
|
'hierarchyid' => self::TYPE_STRING, |
81
|
|
|
'uniqueidentifier' => self::TYPE_STRING, |
82
|
|
|
'sql_variant' => self::TYPE_STRING, |
83
|
|
|
'xml' => self::TYPE_STRING, |
84
|
|
|
'table' => self::TYPE_STRING, |
85
|
|
|
]; |
86
|
|
|
|
87
|
|
|
/** |
88
|
|
|
* {@inheritdoc} |
89
|
|
|
*/ |
90
|
|
|
protected $tableQuoteCharacter = ['[', ']']; |
91
|
|
|
/** |
92
|
|
|
* {@inheritdoc} |
93
|
|
|
*/ |
94
|
|
|
protected $columnQuoteCharacter = ['[', ']']; |
95
|
|
|
|
96
|
|
|
|
97
|
|
|
/** |
98
|
|
|
* Resolves the table name and schema name (if any). |
99
|
|
|
* @param string $name the table name |
100
|
|
|
* @return TableSchema resolved table, schema, etc. names. |
101
|
|
|
*/ |
102
|
|
|
protected function resolveTableName($name) |
103
|
|
|
{ |
104
|
|
|
$resolvedName = new TableSchema(); |
105
|
|
|
$parts = $this->getTableNameParts($name); |
106
|
|
|
$partCount = count($parts); |
107
|
|
|
if ($partCount === 4) { |
108
|
|
|
// server name, catalog name, schema name and table name passed |
109
|
|
|
$resolvedName->catalogName = $parts[1]; |
110
|
|
|
$resolvedName->schemaName = $parts[2]; |
111
|
|
|
$resolvedName->name = $parts[3]; |
112
|
|
|
$resolvedName->fullName = $resolvedName->catalogName . '.' . $resolvedName->schemaName . '.' . $resolvedName->name; |
113
|
|
|
} elseif ($partCount === 3) { |
114
|
|
|
// catalog name, schema name and table name passed |
115
|
|
|
$resolvedName->catalogName = $parts[0]; |
116
|
|
|
$resolvedName->schemaName = $parts[1]; |
117
|
|
|
$resolvedName->name = $parts[2]; |
118
|
|
|
$resolvedName->fullName = $resolvedName->catalogName . '.' . $resolvedName->schemaName . '.' . $resolvedName->name; |
119
|
|
|
} elseif ($partCount === 2) { |
120
|
|
|
// only schema name and table name passed |
121
|
|
|
$resolvedName->schemaName = $parts[0]; |
122
|
|
|
$resolvedName->name = $parts[1]; |
123
|
|
|
$resolvedName->fullName = ($resolvedName->schemaName !== $this->defaultSchema ? $resolvedName->schemaName . '.' : '') . $resolvedName->name; |
124
|
|
|
} else { |
125
|
|
|
// only table name passed |
126
|
|
|
$resolvedName->schemaName = $this->defaultSchema; |
127
|
|
|
$resolvedName->fullName = $resolvedName->name = $parts[0]; |
128
|
|
|
} |
129
|
|
|
|
130
|
|
|
return $resolvedName; |
131
|
|
|
} |
132
|
|
|
|
133
|
|
|
/** |
134
|
|
|
* {@inheritDoc} |
135
|
|
|
* @param string $name |
136
|
|
|
* @return array |
137
|
|
|
* @since 2.0.22 |
138
|
|
|
*/ |
139
|
|
|
protected function getTableNameParts($name) |
140
|
|
|
{ |
141
|
|
|
$parts = [$name]; |
142
|
|
|
preg_match_all('/([^.\[\]]+)|\[([^\[\]]+)\]/', $name, $matches); |
143
|
|
|
if (isset($matches[0]) && is_array($matches[0]) && !empty($matches[0])) { |
144
|
|
|
$parts = $matches[0]; |
145
|
|
|
} |
146
|
|
|
|
147
|
|
|
$parts = str_replace(['[', ']'], '', $parts); |
148
|
|
|
|
149
|
|
|
return $parts; |
150
|
|
|
} |
151
|
|
|
|
152
|
|
|
/** |
153
|
|
|
* {@inheritdoc} |
154
|
|
|
* @see https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-database-principals-transact-sql |
155
|
|
|
*/ |
156
|
|
|
protected function findSchemaNames() |
157
|
|
|
{ |
158
|
|
|
static $sql = <<<'SQL' |
159
|
|
|
SELECT [s].[name] |
160
|
|
|
FROM [sys].[schemas] AS [s] |
161
|
|
|
INNER JOIN [sys].[database_principals] AS [p] ON [p].[principal_id] = [s].[principal_id] |
162
|
|
|
WHERE [p].[is_fixed_role] = 0 AND [p].[sid] IS NOT NULL |
163
|
|
|
ORDER BY [s].[name] ASC |
164
|
|
|
SQL; |
165
|
|
|
|
166
|
|
|
return $this->db->createCommand($sql)->queryColumn(); |
167
|
|
|
} |
168
|
|
|
|
169
|
|
|
/** |
170
|
|
|
* {@inheritdoc} |
171
|
|
|
*/ |
172
|
|
|
protected function findTableNames($schema = '') |
173
|
|
|
{ |
174
|
|
|
if ($schema === '') { |
175
|
|
|
$schema = $this->defaultSchema; |
176
|
|
|
} |
177
|
|
|
|
178
|
|
|
$sql = <<<'SQL' |
179
|
|
|
SELECT [t].[table_name] |
180
|
|
|
FROM [INFORMATION_SCHEMA].[TABLES] AS [t] |
181
|
|
|
WHERE [t].[table_schema] = :schema AND [t].[table_type] IN ('BASE TABLE', 'VIEW') |
182
|
|
|
ORDER BY [t].[table_name] |
183
|
|
|
SQL; |
184
|
|
|
return $this->db->createCommand($sql, [':schema' => $schema])->queryColumn(); |
185
|
|
|
} |
186
|
|
|
|
187
|
|
|
/** |
188
|
|
|
* {@inheritdoc} |
189
|
|
|
*/ |
190
|
|
|
protected function loadTableSchema($name) |
191
|
|
|
{ |
192
|
|
|
$table = new TableSchema(); |
193
|
|
|
$this->resolveTableNames($table, $name); |
194
|
|
|
$this->findPrimaryKeys($table); |
195
|
|
|
if ($this->findColumns($table)) { |
196
|
|
|
$this->findForeignKeys($table); |
197
|
|
|
return $table; |
198
|
|
|
} |
199
|
|
|
|
200
|
|
|
return null; |
201
|
|
|
} |
202
|
|
|
|
203
|
|
|
/** |
204
|
|
|
* {@inheritdoc} |
205
|
|
|
*/ |
206
|
|
|
protected function getSchemaMetadata($schema, $type, $refresh) |
207
|
|
|
{ |
208
|
|
|
$metadata = []; |
209
|
|
|
$methodName = 'getTable' . ucfirst($type); |
210
|
|
|
$tableNames = array_map(function ($table) { |
211
|
|
|
return $this->quoteSimpleTableName($table); |
212
|
|
|
}, $this->getTableNames($schema, $refresh)); |
213
|
|
|
foreach ($tableNames as $name) { |
214
|
|
|
if ($schema !== '') { |
215
|
|
|
$name = $schema . '.' . $name; |
216
|
|
|
} |
217
|
|
|
$tableMetadata = $this->$methodName($name, $refresh); |
218
|
|
|
if ($tableMetadata !== null) { |
219
|
|
|
$metadata[] = $tableMetadata; |
220
|
|
|
} |
221
|
|
|
} |
222
|
|
|
|
223
|
|
|
return $metadata; |
224
|
|
|
} |
225
|
|
|
|
226
|
|
|
/** |
227
|
|
|
* {@inheritdoc} |
228
|
|
|
*/ |
229
|
|
|
protected function loadTablePrimaryKey($tableName) |
230
|
|
|
{ |
231
|
|
|
return $this->loadTableConstraints($tableName, 'primaryKey'); |
232
|
|
|
} |
233
|
|
|
|
234
|
|
|
/** |
235
|
|
|
* {@inheritdoc} |
236
|
|
|
*/ |
237
|
|
|
protected function loadTableForeignKeys($tableName) |
238
|
|
|
{ |
239
|
|
|
return $this->loadTableConstraints($tableName, 'foreignKeys'); |
240
|
|
|
} |
241
|
|
|
|
242
|
|
|
/** |
243
|
|
|
* {@inheritdoc} |
244
|
|
|
*/ |
245
|
|
|
protected function loadTableIndexes($tableName) |
246
|
|
|
{ |
247
|
|
|
static $sql = <<<'SQL' |
248
|
|
|
SELECT |
249
|
|
|
[i].[name] AS [name], |
250
|
|
|
[iccol].[name] AS [column_name], |
251
|
|
|
[i].[is_unique] AS [index_is_unique], |
252
|
|
|
[i].[is_primary_key] AS [index_is_primary] |
253
|
|
|
FROM [sys].[indexes] AS [i] |
254
|
|
|
INNER JOIN [sys].[index_columns] AS [ic] |
255
|
|
|
ON [ic].[object_id] = [i].[object_id] AND [ic].[index_id] = [i].[index_id] |
256
|
|
|
INNER JOIN [sys].[columns] AS [iccol] |
257
|
|
|
ON [iccol].[object_id] = [ic].[object_id] AND [iccol].[column_id] = [ic].[column_id] |
258
|
|
|
WHERE [i].[object_id] = OBJECT_ID(:fullName) |
259
|
|
|
ORDER BY [ic].[key_ordinal] ASC |
260
|
|
|
SQL; |
261
|
|
|
|
262
|
|
|
$resolvedName = $this->resolveTableName($tableName); |
263
|
|
|
$indexes = $this->db->createCommand($sql, [ |
264
|
|
|
':fullName' => $resolvedName->fullName, |
265
|
|
|
])->queryAll(); |
266
|
|
|
$indexes = $this->normalizePdoRowKeyCase($indexes, true); |
267
|
|
|
$indexes = ArrayHelper::index($indexes, null, 'name'); |
268
|
|
|
$result = []; |
269
|
|
|
foreach ($indexes as $name => $index) { |
270
|
|
|
$result[] = new IndexConstraint([ |
271
|
|
|
'isPrimary' => (bool)$index[0]['index_is_primary'], |
272
|
|
|
'isUnique' => (bool)$index[0]['index_is_unique'], |
273
|
|
|
'name' => $name, |
274
|
|
|
'columnNames' => ArrayHelper::getColumn($index, 'column_name'), |
275
|
|
|
]); |
276
|
|
|
} |
277
|
|
|
|
278
|
|
|
return $result; |
279
|
|
|
} |
280
|
|
|
|
281
|
|
|
/** |
282
|
|
|
* {@inheritdoc} |
283
|
|
|
*/ |
284
|
|
|
protected function loadTableUniques($tableName) |
285
|
|
|
{ |
286
|
|
|
return $this->loadTableConstraints($tableName, 'uniques'); |
287
|
|
|
} |
288
|
|
|
|
289
|
|
|
/** |
290
|
|
|
* {@inheritdoc} |
291
|
|
|
*/ |
292
|
|
|
protected function loadTableChecks($tableName) |
293
|
|
|
{ |
294
|
|
|
return $this->loadTableConstraints($tableName, 'checks'); |
295
|
|
|
} |
296
|
|
|
|
297
|
|
|
/** |
298
|
|
|
* {@inheritdoc} |
299
|
|
|
*/ |
300
|
|
|
protected function loadTableDefaultValues($tableName) |
301
|
|
|
{ |
302
|
|
|
return $this->loadTableConstraints($tableName, 'defaults'); |
303
|
|
|
} |
304
|
|
|
|
305
|
|
|
/** |
306
|
|
|
* {@inheritdoc} |
307
|
|
|
*/ |
308
|
|
|
public function createSavepoint($name) |
309
|
|
|
{ |
310
|
|
|
$this->db->createCommand("SAVE TRANSACTION $name")->execute(); |
311
|
|
|
} |
312
|
|
|
|
313
|
|
|
/** |
314
|
|
|
* {@inheritdoc} |
315
|
|
|
*/ |
316
|
|
|
public function releaseSavepoint($name) |
317
|
|
|
{ |
318
|
|
|
// does nothing as MSSQL does not support this |
319
|
|
|
} |
320
|
|
|
|
321
|
|
|
/** |
322
|
|
|
* {@inheritdoc} |
323
|
|
|
*/ |
324
|
|
|
public function rollBackSavepoint($name) |
325
|
|
|
{ |
326
|
|
|
$this->db->createCommand("ROLLBACK TRANSACTION $name")->execute(); |
327
|
|
|
} |
328
|
|
|
|
329
|
|
|
/** |
330
|
|
|
* Creates a query builder for the MSSQL database. |
331
|
|
|
* @return QueryBuilder query builder interface. |
332
|
|
|
*/ |
333
|
|
|
public function createQueryBuilder() |
334
|
|
|
{ |
335
|
|
|
return Yii::createObject(QueryBuilder::className(), [$this->db]); |
|
|
|
|
336
|
|
|
} |
337
|
|
|
|
338
|
|
|
/** |
339
|
|
|
* Resolves the table name and schema name (if any). |
340
|
|
|
* @param TableSchema $table the table metadata object |
341
|
|
|
* @param string $name the table name |
342
|
|
|
*/ |
343
|
|
|
protected function resolveTableNames($table, $name) |
344
|
|
|
{ |
345
|
|
|
$parts = $this->getTableNameParts($name); |
346
|
|
|
$partCount = count($parts); |
347
|
|
|
if ($partCount === 4) { |
348
|
|
|
// server name, catalog name, schema name and table name passed |
349
|
|
|
$table->catalogName = $parts[1]; |
350
|
|
|
$table->schemaName = $parts[2]; |
351
|
|
|
$table->name = $parts[3]; |
352
|
|
|
$table->fullName = $table->catalogName . '.' . $table->schemaName . '.' . $table->name; |
353
|
|
|
} elseif ($partCount === 3) { |
354
|
|
|
// catalog name, schema name and table name passed |
355
|
|
|
$table->catalogName = $parts[0]; |
356
|
|
|
$table->schemaName = $parts[1]; |
357
|
|
|
$table->name = $parts[2]; |
358
|
|
|
$table->fullName = $table->catalogName . '.' . $table->schemaName . '.' . $table->name; |
359
|
|
|
} elseif ($partCount === 2) { |
360
|
|
|
// only schema name and table name passed |
361
|
|
|
$table->schemaName = $parts[0]; |
362
|
|
|
$table->name = $parts[1]; |
363
|
|
|
$table->fullName = $table->schemaName !== $this->defaultSchema ? $table->schemaName . '.' . $table->name : $table->name; |
364
|
|
|
} else { |
365
|
|
|
// only table name passed |
366
|
|
|
$table->schemaName = $this->defaultSchema; |
367
|
|
|
$table->fullName = $table->name = $parts[0]; |
368
|
|
|
} |
369
|
|
|
} |
370
|
|
|
|
371
|
|
|
/** |
372
|
|
|
* Loads the column information into a [[ColumnSchema]] object. |
373
|
|
|
* @param array $info column information |
374
|
|
|
* @return ColumnSchema the column schema object |
375
|
|
|
*/ |
376
|
|
|
protected function loadColumnSchema($info) |
377
|
|
|
{ |
378
|
|
|
$isVersion2017orLater = version_compare($this->db->getSchema()->getServerVersion(), '14', '>='); |
379
|
|
|
$column = $this->createColumnSchema(); |
380
|
|
|
|
381
|
|
|
$column->name = $info['column_name']; |
382
|
|
|
$column->allowNull = $info['is_nullable'] === 'YES'; |
383
|
|
|
$column->dbType = $info['data_type']; |
384
|
|
|
$column->enumValues = []; // mssql has only vague equivalents to enum |
385
|
|
|
$column->isPrimaryKey = null; // primary key will be determined in findColumns() method |
386
|
|
|
$column->autoIncrement = $info['is_identity'] == 1; |
387
|
|
|
$column->isComputed = (bool)$info['is_computed']; |
|
|
|
|
388
|
|
|
$column->unsigned = stripos($column->dbType, 'unsigned') !== false; |
389
|
|
|
$column->comment = $info['comment'] === null ? '' : $info['comment']; |
390
|
|
|
|
391
|
|
|
$column->type = self::TYPE_STRING; |
392
|
|
|
if (preg_match('/^(\w+)(?:\(([^\)]+)\))?/', $column->dbType, $matches)) { |
393
|
|
|
$type = $matches[1]; |
394
|
|
|
if (isset($this->typeMap[$type])) { |
395
|
|
|
$column->type = $this->typeMap[$type]; |
396
|
|
|
} |
397
|
|
|
|
398
|
|
|
if ($isVersion2017orLater && $type === 'bit') { |
399
|
|
|
$column->type = 'boolean'; |
400
|
|
|
} |
401
|
|
|
|
402
|
|
|
if (!empty($matches[2])) { |
403
|
|
|
$values = explode(',', $matches[2]); |
404
|
|
|
$column->size = $column->precision = (int) $values[0]; |
405
|
|
|
|
406
|
|
|
if (isset($values[1])) { |
407
|
|
|
$column->scale = (int) $values[1]; |
408
|
|
|
} |
409
|
|
|
|
410
|
|
|
if ($isVersion2017orLater === false) { |
411
|
|
|
$column->type = $this->booleanTypeLegacy($column->size, $type); |
412
|
|
|
} |
413
|
|
|
} |
414
|
|
|
} |
415
|
|
|
|
416
|
|
|
$column->phpType = $this->getColumnPhpType($column); |
417
|
|
|
|
418
|
|
|
if ($info['column_default'] === '(NULL)') { |
419
|
|
|
$info['column_default'] = null; |
420
|
|
|
} |
421
|
|
|
if (!$column->isPrimaryKey && ($column->type !== 'timestamp' || $info['column_default'] !== 'CURRENT_TIMESTAMP')) { |
422
|
|
|
$column->defaultValue = $column->defaultPhpTypecast($info['column_default']); |
|
|
|
|
423
|
|
|
} |
424
|
|
|
|
425
|
|
|
return $column; |
426
|
|
|
} |
427
|
|
|
|
428
|
|
|
/** |
429
|
|
|
* Collects the metadata of table columns. |
430
|
|
|
* @param TableSchema $table the table metadata |
431
|
|
|
* @return bool whether the table exists in the database |
432
|
|
|
*/ |
433
|
|
|
protected function findColumns($table) |
434
|
|
|
{ |
435
|
|
|
$columnsTableName = 'INFORMATION_SCHEMA.COLUMNS'; |
436
|
|
|
$whereSql = "[t1].[table_name] = " . $this->db->quoteValue($table->name); |
437
|
|
|
if ($table->catalogName !== null) { |
438
|
|
|
$columnsTableName = "{$table->catalogName}.{$columnsTableName}"; |
439
|
|
|
$whereSql .= " AND [t1].[table_catalog] = '{$table->catalogName}'"; |
440
|
|
|
} |
441
|
|
|
if ($table->schemaName !== null) { |
442
|
|
|
$whereSql .= " AND [t1].[table_schema] = '{$table->schemaName}'"; |
443
|
|
|
} |
444
|
|
|
$columnsTableName = $this->quoteTableName($columnsTableName); |
445
|
|
|
|
446
|
|
|
$sql = <<<SQL |
447
|
|
|
SELECT |
448
|
|
|
[t1].[column_name], |
449
|
|
|
[t1].[is_nullable], |
450
|
|
|
CASE WHEN [t1].[data_type] IN ('char','varchar','nchar','nvarchar','binary','varbinary') THEN |
451
|
|
|
CASE WHEN [t1].[character_maximum_length] = NULL OR [t1].[character_maximum_length] = -1 THEN |
452
|
|
|
[t1].[data_type] |
453
|
|
|
ELSE |
454
|
|
|
[t1].[data_type] + '(' + LTRIM(RTRIM(CONVERT(CHAR,[t1].[character_maximum_length]))) + ')' |
455
|
|
|
END |
456
|
|
|
ELSE |
457
|
|
|
[t1].[data_type] |
458
|
|
|
END AS 'data_type', |
459
|
|
|
[t1].[column_default], |
460
|
|
|
COLUMNPROPERTY(OBJECT_ID([t1].[table_schema] + '.' + [t1].[table_name]), [t1].[column_name], 'IsIdentity') AS is_identity, |
461
|
|
|
COLUMNPROPERTY(OBJECT_ID([t1].[table_schema] + '.' + [t1].[table_name]), [t1].[column_name], 'IsComputed') AS is_computed, |
462
|
|
|
( |
463
|
|
|
SELECT CONVERT(VARCHAR, [t2].[value]) |
464
|
|
|
FROM [sys].[extended_properties] AS [t2] |
465
|
|
|
WHERE |
466
|
|
|
[t2].[class] = 1 AND |
467
|
|
|
[t2].[class_desc] = 'OBJECT_OR_COLUMN' AND |
468
|
|
|
[t2].[name] = 'MS_Description' AND |
469
|
|
|
[t2].[major_id] = OBJECT_ID([t1].[TABLE_SCHEMA] + '.' + [t1].[table_name]) AND |
470
|
|
|
[t2].[minor_id] = COLUMNPROPERTY(OBJECT_ID([t1].[TABLE_SCHEMA] + '.' + [t1].[TABLE_NAME]), [t1].[COLUMN_NAME], 'ColumnID') |
471
|
|
|
) as comment |
472
|
|
|
FROM {$columnsTableName} AS [t1] |
473
|
|
|
WHERE {$whereSql} |
474
|
|
|
SQL; |
475
|
|
|
|
476
|
|
|
try { |
477
|
|
|
$columns = $this->db->createCommand($sql)->queryAll(); |
478
|
|
|
if (empty($columns)) { |
479
|
|
|
return false; |
480
|
|
|
} |
481
|
|
|
} catch (\Exception $e) { |
482
|
|
|
return false; |
483
|
|
|
} |
484
|
|
|
foreach ($columns as $column) { |
485
|
|
|
$column = $this->loadColumnSchema($column); |
486
|
|
|
foreach ($table->primaryKey as $primaryKey) { |
487
|
|
|
if (strcasecmp($column->name, $primaryKey) === 0) { |
488
|
|
|
$column->isPrimaryKey = true; |
489
|
|
|
break; |
490
|
|
|
} |
491
|
|
|
} |
492
|
|
|
if ($column->isPrimaryKey && $column->autoIncrement) { |
493
|
|
|
$table->sequenceName = ''; |
494
|
|
|
} |
495
|
|
|
$table->columns[$column->name] = $column; |
496
|
|
|
} |
497
|
|
|
|
498
|
|
|
return true; |
499
|
|
|
} |
500
|
|
|
|
501
|
|
|
/** |
502
|
|
|
* Collects the constraint details for the given table and constraint type. |
503
|
|
|
* @param TableSchema $table |
504
|
|
|
* @param string $type either PRIMARY KEY or UNIQUE |
505
|
|
|
* @return array each entry contains index_name and field_name |
506
|
|
|
* @since 2.0.4 |
507
|
|
|
*/ |
508
|
|
|
protected function findTableConstraints($table, $type) |
509
|
|
|
{ |
510
|
|
|
$keyColumnUsageTableName = 'INFORMATION_SCHEMA.KEY_COLUMN_USAGE'; |
511
|
|
|
$tableConstraintsTableName = 'INFORMATION_SCHEMA.TABLE_CONSTRAINTS'; |
512
|
|
|
if ($table->catalogName !== null) { |
513
|
|
|
$keyColumnUsageTableName = $table->catalogName . '.' . $keyColumnUsageTableName; |
514
|
|
|
$tableConstraintsTableName = $table->catalogName . '.' . $tableConstraintsTableName; |
515
|
|
|
} |
516
|
|
|
$keyColumnUsageTableName = $this->quoteTableName($keyColumnUsageTableName); |
517
|
|
|
$tableConstraintsTableName = $this->quoteTableName($tableConstraintsTableName); |
518
|
|
|
|
519
|
|
|
$sql = <<<SQL |
520
|
|
|
SELECT |
521
|
|
|
[kcu].[constraint_name] AS [index_name], |
522
|
|
|
[kcu].[column_name] AS [field_name] |
523
|
|
|
FROM {$keyColumnUsageTableName} AS [kcu] |
524
|
|
|
LEFT JOIN {$tableConstraintsTableName} AS [tc] ON |
525
|
|
|
[kcu].[table_schema] = [tc].[table_schema] AND |
526
|
|
|
[kcu].[table_name] = [tc].[table_name] AND |
527
|
|
|
[kcu].[constraint_name] = [tc].[constraint_name] |
528
|
|
|
WHERE |
529
|
|
|
[tc].[constraint_type] = :type AND |
530
|
|
|
[kcu].[table_name] = :tableName AND |
531
|
|
|
[kcu].[table_schema] = :schemaName |
532
|
|
|
SQL; |
533
|
|
|
|
534
|
|
|
return $this->db |
535
|
|
|
->createCommand($sql, [ |
536
|
|
|
':tableName' => $table->name, |
537
|
|
|
':schemaName' => $table->schemaName, |
538
|
|
|
':type' => $type, |
539
|
|
|
]) |
540
|
|
|
->queryAll(); |
541
|
|
|
} |
542
|
|
|
|
543
|
|
|
/** |
544
|
|
|
* Collects the primary key column details for the given table. |
545
|
|
|
* @param TableSchema $table the table metadata |
546
|
|
|
*/ |
547
|
|
|
protected function findPrimaryKeys($table) |
548
|
|
|
{ |
549
|
|
|
$result = []; |
550
|
|
|
foreach ($this->findTableConstraints($table, 'PRIMARY KEY') as $row) { |
551
|
|
|
$result[] = $row['field_name']; |
552
|
|
|
} |
553
|
|
|
$table->primaryKey = $result; |
554
|
|
|
} |
555
|
|
|
|
556
|
|
|
/** |
557
|
|
|
* Collects the foreign key column details for the given table. |
558
|
|
|
* @param TableSchema $table the table metadata |
559
|
|
|
*/ |
560
|
|
|
protected function findForeignKeys($table) |
561
|
|
|
{ |
562
|
|
|
$object = $table->name; |
563
|
|
|
if ($table->schemaName !== null) { |
564
|
|
|
$object = $table->schemaName . '.' . $object; |
565
|
|
|
} |
566
|
|
|
if ($table->catalogName !== null) { |
567
|
|
|
$object = $table->catalogName . '.' . $object; |
568
|
|
|
} |
569
|
|
|
|
570
|
|
|
// please refer to the following page for more details: |
571
|
|
|
// http://msdn2.microsoft.com/en-us/library/aa175805(SQL.80).aspx |
572
|
|
|
$sql = <<<'SQL' |
573
|
|
|
SELECT |
574
|
|
|
[fk].[name] AS [fk_name], |
575
|
|
|
[cp].[name] AS [fk_column_name], |
576
|
|
|
OBJECT_NAME([fk].[referenced_object_id]) AS [uq_table_name], |
577
|
|
|
[cr].[name] AS [uq_column_name] |
578
|
|
|
FROM |
579
|
|
|
[sys].[foreign_keys] AS [fk] |
580
|
|
|
INNER JOIN [sys].[foreign_key_columns] AS [fkc] ON |
581
|
|
|
[fk].[object_id] = [fkc].[constraint_object_id] |
582
|
|
|
INNER JOIN [sys].[columns] AS [cp] ON |
583
|
|
|
[fk].[parent_object_id] = [cp].[object_id] AND |
584
|
|
|
[fkc].[parent_column_id] = [cp].[column_id] |
585
|
|
|
INNER JOIN [sys].[columns] AS [cr] ON |
586
|
|
|
[fk].[referenced_object_id] = [cr].[object_id] AND |
587
|
|
|
[fkc].[referenced_column_id] = [cr].[column_id] |
588
|
|
|
WHERE |
589
|
|
|
[fk].[parent_object_id] = OBJECT_ID(:object) |
590
|
|
|
SQL; |
591
|
|
|
|
592
|
|
|
$rows = $this->db->createCommand($sql, [ |
593
|
|
|
':object' => $object, |
594
|
|
|
])->queryAll(); |
595
|
|
|
|
596
|
|
|
$table->foreignKeys = []; |
597
|
|
|
foreach ($rows as $row) { |
598
|
|
|
if (!isset($table->foreignKeys[$row['fk_name']])) { |
599
|
|
|
$table->foreignKeys[$row['fk_name']][] = $row['uq_table_name']; |
600
|
|
|
} |
601
|
|
|
$table->foreignKeys[$row['fk_name']][$row['fk_column_name']] = $row['uq_column_name']; |
602
|
|
|
} |
603
|
|
|
} |
604
|
|
|
|
605
|
|
|
/** |
606
|
|
|
* {@inheritdoc} |
607
|
|
|
*/ |
608
|
|
|
protected function findViewNames($schema = '') |
609
|
|
|
{ |
610
|
|
|
if ($schema === '') { |
611
|
|
|
$schema = $this->defaultSchema; |
612
|
|
|
} |
613
|
|
|
|
614
|
|
|
$sql = <<<'SQL' |
615
|
|
|
SELECT [t].[table_name] |
616
|
|
|
FROM [INFORMATION_SCHEMA].[TABLES] AS [t] |
617
|
|
|
WHERE [t].[table_schema] = :schema AND [t].[table_type] = 'VIEW' |
618
|
|
|
ORDER BY [t].[table_name] |
619
|
|
|
SQL; |
620
|
|
|
|
621
|
|
|
return $this->db->createCommand($sql, [':schema' => $schema])->queryColumn(); |
622
|
|
|
} |
623
|
|
|
|
624
|
|
|
/** |
625
|
|
|
* Returns all unique indexes for the given table. |
626
|
|
|
* |
627
|
|
|
* Each array element is of the following structure: |
628
|
|
|
* |
629
|
|
|
* ```php |
630
|
|
|
* [ |
631
|
|
|
* 'IndexName1' => ['col1' [, ...]], |
632
|
|
|
* 'IndexName2' => ['col2' [, ...]], |
633
|
|
|
* ] |
634
|
|
|
* ``` |
635
|
|
|
* |
636
|
|
|
* @param TableSchema $table the table metadata |
637
|
|
|
* @return array all unique indexes for the given table. |
638
|
|
|
* @since 2.0.4 |
639
|
|
|
*/ |
640
|
|
|
public function findUniqueIndexes($table) |
641
|
|
|
{ |
642
|
|
|
$result = []; |
643
|
|
|
foreach ($this->findTableConstraints($table, 'UNIQUE') as $row) { |
644
|
|
|
$result[$row['index_name']][] = $row['field_name']; |
645
|
|
|
} |
646
|
|
|
|
647
|
|
|
return $result; |
648
|
|
|
} |
649
|
|
|
|
650
|
|
|
/** |
651
|
|
|
* Loads multiple types of constraints and returns the specified ones. |
652
|
|
|
* @param string $tableName table name. |
653
|
|
|
* @param string $returnType return type: |
654
|
|
|
* - primaryKey |
655
|
|
|
* - foreignKeys |
656
|
|
|
* - uniques |
657
|
|
|
* - checks |
658
|
|
|
* - defaults |
659
|
|
|
* @return mixed constraints. |
660
|
|
|
*/ |
661
|
|
|
private function loadTableConstraints($tableName, $returnType) |
662
|
|
|
{ |
663
|
|
|
static $sql = <<<'SQL' |
664
|
|
|
SELECT |
665
|
|
|
[o].[name] AS [name], |
666
|
|
|
COALESCE([ccol].[name], [dcol].[name], [fccol].[name], [kiccol].[name]) AS [column_name], |
667
|
|
|
RTRIM([o].[type]) AS [type], |
668
|
|
|
OBJECT_SCHEMA_NAME([f].[referenced_object_id]) AS [foreign_table_schema], |
669
|
|
|
OBJECT_NAME([f].[referenced_object_id]) AS [foreign_table_name], |
670
|
|
|
[ffccol].[name] AS [foreign_column_name], |
671
|
|
|
[f].[update_referential_action_desc] AS [on_update], |
672
|
|
|
[f].[delete_referential_action_desc] AS [on_delete], |
673
|
|
|
[c].[definition] AS [check_expr], |
674
|
|
|
[d].[definition] AS [default_expr] |
675
|
|
|
FROM (SELECT OBJECT_ID(:fullName) AS [object_id]) AS [t] |
676
|
|
|
INNER JOIN [sys].[objects] AS [o] |
677
|
|
|
ON [o].[parent_object_id] = [t].[object_id] AND [o].[type] IN ('PK', 'UQ', 'C', 'D', 'F') |
678
|
|
|
LEFT JOIN [sys].[check_constraints] AS [c] |
679
|
|
|
ON [c].[object_id] = [o].[object_id] |
680
|
|
|
LEFT JOIN [sys].[columns] AS [ccol] |
681
|
|
|
ON [ccol].[object_id] = [c].[parent_object_id] AND [ccol].[column_id] = [c].[parent_column_id] |
682
|
|
|
LEFT JOIN [sys].[default_constraints] AS [d] |
683
|
|
|
ON [d].[object_id] = [o].[object_id] |
684
|
|
|
LEFT JOIN [sys].[columns] AS [dcol] |
685
|
|
|
ON [dcol].[object_id] = [d].[parent_object_id] AND [dcol].[column_id] = [d].[parent_column_id] |
686
|
|
|
LEFT JOIN [sys].[key_constraints] AS [k] |
687
|
|
|
ON [k].[object_id] = [o].[object_id] |
688
|
|
|
LEFT JOIN [sys].[index_columns] AS [kic] |
689
|
|
|
ON [kic].[object_id] = [k].[parent_object_id] AND [kic].[index_id] = [k].[unique_index_id] |
690
|
|
|
LEFT JOIN [sys].[columns] AS [kiccol] |
691
|
|
|
ON [kiccol].[object_id] = [kic].[object_id] AND [kiccol].[column_id] = [kic].[column_id] |
692
|
|
|
LEFT JOIN [sys].[foreign_keys] AS [f] |
693
|
|
|
ON [f].[object_id] = [o].[object_id] |
694
|
|
|
LEFT JOIN [sys].[foreign_key_columns] AS [fc] |
695
|
|
|
ON [fc].[constraint_object_id] = [o].[object_id] |
696
|
|
|
LEFT JOIN [sys].[columns] AS [fccol] |
697
|
|
|
ON [fccol].[object_id] = [fc].[parent_object_id] AND [fccol].[column_id] = [fc].[parent_column_id] |
698
|
|
|
LEFT JOIN [sys].[columns] AS [ffccol] |
699
|
|
|
ON [ffccol].[object_id] = [fc].[referenced_object_id] AND [ffccol].[column_id] = [fc].[referenced_column_id] |
700
|
|
|
ORDER BY [kic].[key_ordinal] ASC, [fc].[constraint_column_id] ASC |
701
|
|
|
SQL; |
702
|
|
|
|
703
|
|
|
$resolvedName = $this->resolveTableName($tableName); |
704
|
|
|
$constraints = $this->db->createCommand($sql, [ |
705
|
|
|
':fullName' => $resolvedName->fullName, |
706
|
|
|
])->queryAll(); |
707
|
|
|
$constraints = $this->normalizePdoRowKeyCase($constraints, true); |
708
|
|
|
$constraints = ArrayHelper::index($constraints, null, ['type', 'name']); |
709
|
|
|
$result = [ |
710
|
|
|
'primaryKey' => null, |
711
|
|
|
'foreignKeys' => [], |
712
|
|
|
'uniques' => [], |
713
|
|
|
'checks' => [], |
714
|
|
|
'defaults' => [], |
715
|
|
|
]; |
716
|
|
|
foreach ($constraints as $type => $names) { |
717
|
|
|
foreach ($names as $name => $constraint) { |
718
|
|
|
switch ($type) { |
719
|
|
|
case 'PK': |
720
|
|
|
$result['primaryKey'] = new Constraint([ |
721
|
|
|
'name' => $name, |
722
|
|
|
'columnNames' => ArrayHelper::getColumn($constraint, 'column_name'), |
723
|
|
|
]); |
724
|
|
|
break; |
725
|
|
|
case 'F': |
726
|
|
|
$result['foreignKeys'][] = new ForeignKeyConstraint([ |
727
|
|
|
'name' => $name, |
728
|
|
|
'columnNames' => ArrayHelper::getColumn($constraint, 'column_name'), |
729
|
|
|
'foreignSchemaName' => $constraint[0]['foreign_table_schema'], |
730
|
|
|
'foreignTableName' => $constraint[0]['foreign_table_name'], |
731
|
|
|
'foreignColumnNames' => ArrayHelper::getColumn($constraint, 'foreign_column_name'), |
732
|
|
|
'onDelete' => str_replace('_', '', $constraint[0]['on_delete']), |
733
|
|
|
'onUpdate' => str_replace('_', '', $constraint[0]['on_update']), |
734
|
|
|
]); |
735
|
|
|
break; |
736
|
|
|
case 'UQ': |
737
|
|
|
$result['uniques'][] = new Constraint([ |
738
|
|
|
'name' => $name, |
739
|
|
|
'columnNames' => ArrayHelper::getColumn($constraint, 'column_name'), |
740
|
|
|
]); |
741
|
|
|
break; |
742
|
|
|
case 'C': |
743
|
|
|
$result['checks'][] = new CheckConstraint([ |
744
|
|
|
'name' => $name, |
745
|
|
|
'columnNames' => ArrayHelper::getColumn($constraint, 'column_name'), |
746
|
|
|
'expression' => $constraint[0]['check_expr'], |
747
|
|
|
]); |
748
|
|
|
break; |
749
|
|
|
case 'D': |
750
|
|
|
$result['defaults'][] = new DefaultValueConstraint([ |
751
|
|
|
'name' => $name, |
752
|
|
|
'columnNames' => ArrayHelper::getColumn($constraint, 'column_name'), |
753
|
|
|
'value' => $constraint[0]['default_expr'], |
754
|
|
|
]); |
755
|
|
|
break; |
756
|
|
|
} |
757
|
|
|
} |
758
|
|
|
} |
759
|
|
|
foreach ($result as $type => $data) { |
760
|
|
|
$this->setTableMetadata($tableName, $type, $data); |
761
|
|
|
} |
762
|
|
|
|
763
|
|
|
return $result[$returnType]; |
764
|
|
|
} |
765
|
|
|
|
766
|
|
|
/** |
767
|
|
|
* {@inheritdoc} |
768
|
|
|
*/ |
769
|
|
|
public function quoteColumnName($name) |
770
|
|
|
{ |
771
|
|
|
if (preg_match('/^\[.*\]$/', $name)) { |
772
|
|
|
return $name; |
773
|
|
|
} |
774
|
|
|
|
775
|
|
|
return parent::quoteColumnName($name); |
776
|
|
|
} |
777
|
|
|
|
778
|
|
|
/** |
779
|
|
|
* Retrieving inserted data from a primary key request of type uniqueidentifier (for SQL Server 2005 or later) |
780
|
|
|
* {@inheritdoc} |
781
|
|
|
*/ |
782
|
|
|
public function insert($table, $columns) |
783
|
|
|
{ |
784
|
|
|
$command = $this->db->createCommand()->insert($table, $columns); |
785
|
|
|
if (!$command->execute()) { |
786
|
|
|
return false; |
787
|
|
|
} |
788
|
|
|
|
789
|
|
|
$isVersion2005orLater = version_compare($this->db->getSchema()->getServerVersion(), '9', '>='); |
790
|
|
|
$inserted = $isVersion2005orLater ? $command->pdoStatement->fetch() : []; |
791
|
|
|
|
792
|
|
|
$tableSchema = $this->getTableSchema($table); |
793
|
|
|
$result = []; |
794
|
|
|
foreach ($tableSchema->primaryKey as $name) { |
795
|
|
|
// @see https://github.com/yiisoft/yii2/issues/13828 & https://github.com/yiisoft/yii2/issues/17474 |
796
|
|
|
if (isset($inserted[$name])) { |
797
|
|
|
$result[$name] = $inserted[$name]; |
798
|
|
|
} elseif ($tableSchema->columns[$name]->autoIncrement) { |
799
|
|
|
// for a version earlier than 2005 |
800
|
|
|
$result[$name] = $this->getLastInsertID($tableSchema->sequenceName); |
801
|
|
|
} elseif (isset($columns[$name])) { |
802
|
|
|
$result[$name] = $columns[$name]; |
803
|
|
|
} else { |
804
|
|
|
$result[$name] = $tableSchema->columns[$name]->defaultValue; |
805
|
|
|
} |
806
|
|
|
} |
807
|
|
|
|
808
|
|
|
return $result; |
809
|
|
|
} |
810
|
|
|
|
811
|
|
|
/** |
812
|
|
|
* {@inheritdoc} |
813
|
|
|
*/ |
814
|
|
|
public function createColumnSchemaBuilder($type, $length = null) |
815
|
|
|
{ |
816
|
|
|
return Yii::createObject(ColumnSchemaBuilder::className(), [$type, $length, $this->db]); |
|
|
|
|
817
|
|
|
} |
818
|
|
|
|
819
|
|
|
/** |
820
|
|
|
* Assigns a type boolean for the column type bit, for legacy versions of MSSQL. |
821
|
|
|
* |
822
|
|
|
* @param int $size column size. |
823
|
|
|
* @param string $type column type. |
824
|
|
|
* |
825
|
|
|
* @return string column type. |
826
|
|
|
*/ |
827
|
|
|
private function booleanTypeLegacy($size, $type) |
828
|
|
|
{ |
829
|
|
|
if ($size === 1 && ($type === 'tinyint' || $type === 'bit')) { |
830
|
|
|
return 'boolean'; |
831
|
|
|
} elseif ($type === 'bit') { |
832
|
|
|
if ($size > 32) { |
833
|
|
|
return 'bigint'; |
834
|
|
|
} elseif ($size === 32) { |
835
|
|
|
return 'integer'; |
836
|
|
|
} |
837
|
|
|
} |
838
|
|
|
|
839
|
|
|
return $type; |
840
|
|
|
} |
841
|
|
|
} |
842
|
|
|
|
This function has been deprecated. The supplier of the function has supplied an explanatory message.
The explanatory message should give you some clue as to whether and when the function will be removed and what other function to use instead.