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']); |
||
0 ignored issues
–
show
Bug
introduced
by
![]() |
|||
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 |