Passed
Push — dev ( 9eee51...bfddd1 )
by Def
18:10 queued 15:05
created

SchemaPDOMssql::getLastInsertID()   A

Complexity

Conditions 1
Paths 1

Size

Total Lines 3
Code Lines 1

Duplication

Lines 0
Ratio 0 %

Code Coverage

Tests 0
CRAP Score 2

Importance

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