Test Failed
Pull Request — master (#110)
by Def
17:14 queued 14:34
created

Schema   F

Complexity

Total Complexity 83

Size/Duplication

Total Lines 919
Duplicated Lines 0 %

Test Coverage

Coverage 88.55%

Importance

Changes 5
Bugs 0 Features 1
Metric Value
eloc 390
c 5
b 0
f 1
dl 0
loc 919
ccs 232
cts 262
cp 0.8855
rs 2
wmc 83

How to fix   Complexity   

Complex Class

Complex classes like Schema often do a lot of different things. To break such a class down, we need to identify a cohesive component within that class. A common approach to find such a component is to look for fields/methods that share the same prefixes, or suffixes.

Once you have determined the fields that belong together, you can apply the Extract Class refactoring. If the component makes sense as a sub-class, Extract Subclass is also a candidate, and is often faster.

While breaking up the class, it is a good idea to analyze how other classes use Schema, and based on these observations, apply Extract Interface, too.

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