Passed
Push — master ( 55285f...bbefac )
by Wilmer
11:54 queued 10:13
created

Schema::loadColumnSchema()   D

Complexity

Conditions 19
Paths 100

Size

Total Lines 68
Code Lines 44

Duplication

Lines 0
Ratio 0 %

Code Coverage

Tests 40
CRAP Score 19.0389

Importance

Changes 1
Bugs 0 Features 0
Metric Value
cc 19
eloc 44
c 1
b 0
f 0
nc 100
nop 1
dl 0
loc 68
ccs 40
cts 42
cp 0.9524
crap 19.0389
rs 4.5166

How to fix   Long Method    Complexity   

Long Method

Small methods make your code easier to understand, in particular if combined with a good name. Besides, if your method is small, finding a good name is usually much easier.

For example, if you find yourself adding comments to a method's body, this is usually a good sign to extract the commented part to a new method, and use the comment as a starting point when coming up with a good name for this new method.

Commonly applied refactorings include:

1
<?php
2
3
declare(strict_types=1);
4
5
namespace Yiisoft\Db\Mysql\Schema;
6
7
use Yiisoft\Arrays\ArrayHelper;
8
use Yiisoft\Db\Constraint\Constraint;
9
use Yiisoft\Db\Constraint\ConstraintFinderInterface;
10
use Yiisoft\Db\Constraint\ConstraintFinderTrait;
11
use Yiisoft\Db\Constraint\ForeignKeyConstraint;
12
use Yiisoft\Db\Constraint\IndexConstraint;
13
use Yiisoft\Db\Exception\Exception;
14
use Yiisoft\Db\Exception\InvalidArgumentException;
15
use Yiisoft\Db\Exception\InvalidConfigException;
16
use Yiisoft\Db\Exception\NotSupportedException;
17
use Yiisoft\Db\Expression\Expression;
18
use Yiisoft\Db\Mysql\Query\QueryBuilder;
19
use Yiisoft\Db\Schema\Schema as AbstractSchema;
20
21
class Schema extends AbstractSchema implements ConstraintFinderInterface
22
{
23
    use ConstraintFinderTrait;
24
25
    protected string $tableQuoteCharacter = '`';
26
    protected string $columnQuoteCharacter = '`';
27
    private bool $oldMysql;
28
    private array $typeMap = [
29
        'tinyint' => self::TYPE_TINYINT,
30
        'bit' => self::TYPE_INTEGER,
31
        'smallint' => self::TYPE_SMALLINT,
32
        'mediumint' => self::TYPE_INTEGER,
33
        'int' => self::TYPE_INTEGER,
34
        'integer' => self::TYPE_INTEGER,
35
        'bigint' => self::TYPE_BIGINT,
36
        'float' => self::TYPE_FLOAT,
37
        'double' => self::TYPE_DOUBLE,
38
        'real' => self::TYPE_FLOAT,
39
        'decimal' => self::TYPE_DECIMAL,
40
        'numeric' => self::TYPE_DECIMAL,
41
        'tinytext' => self::TYPE_TEXT,
42
        'mediumtext' => self::TYPE_TEXT,
43
        'longtext' => self::TYPE_TEXT,
44
        'longblob' => self::TYPE_BINARY,
45
        'blob' => self::TYPE_BINARY,
46
        'text' => self::TYPE_TEXT,
47
        'varchar' => self::TYPE_STRING,
48
        'string' => self::TYPE_STRING,
49
        'char' => self::TYPE_CHAR,
50
        'datetime' => self::TYPE_DATETIME,
51
        'year' => self::TYPE_DATE,
52
        'date' => self::TYPE_DATE,
53
        'time' => self::TYPE_TIME,
54
        'timestamp' => self::TYPE_TIMESTAMP,
55
        'enum' => self::TYPE_STRING,
56
        'varbinary' => self::TYPE_BINARY,
57
        'json' => self::TYPE_JSON,
58
    ];
59
60
    /**
61
     * Resolves the table name and schema name (if any).
62
     *
63
     * @param string $name the table name.
64
     *
65
     * @return TableSchema
66
     *
67
     * {@see \Yiisoft\Db\Schema\TableSchema}
68
     */
69 58
    protected function resolveTableName(string $name): TableSchema
70
    {
71 58
        $resolvedName = new TableSchema();
72
73 58
        $parts = \explode('.', \str_replace('`', '', $name));
74
75 58
        if (isset($parts[1])) {
76
            $resolvedName->schemaName($parts[0]);
77
            $resolvedName->name($parts[1]);
78
        } else {
79 58
            $resolvedName->schemaName($this->defaultSchema);
80 58
            $resolvedName->name($name);
81
        }
82
83 58
        $resolvedName->fullName(($resolvedName->getSchemaName() !== $this->defaultSchema ?
84 58
            $resolvedName->getSchemaName() . '.' : '') . $resolvedName->getName());
85
86 58
        return $resolvedName;
87
    }
88
89
    /**
90
     * Returns all table names in the database.
91
     *
92
     * This method should be overridden by child classes in order to support this feature because the default
93
     * implementation simply throws an exception.
94
     *
95
     * @param string $schema the schema of the tables. Defaults to empty string, meaning the current or default schema.
96
     *
97
     * @throws Exception
98
     * @throws InvalidConfigException
99
     * @throws InvalidArgumentException
100
     *
101
     * @return array all table names in the database. The names have NO schema name prefix.
102
     */
103 5
    protected function findTableNames(string $schema = ''): array
104
    {
105 5
        $sql = 'SHOW TABLES';
106
107 5
        if ($schema !== '') {
108
            $sql .= ' FROM ' . $this->quoteSimpleTableName($schema);
109
        }
110
111 5
        return $this->getDb()->createCommand($sql)->queryColumn();
112
    }
113
114
    /**
115
     * Loads the metadata for the specified table.
116
     *
117
     * @param string $name table name.
118
     *
119
     * @throws \Exception
120
     *
121
     * @return TableSchema|null DBMS-dependent table metadata, `null` if the table does not exist.
122
     */
123 90
    protected function loadTableSchema(string $name): ?TableSchema
124
    {
125 90
        $table = new TableSchema();
126
127 90
        $this->resolveTableNames($table, $name);
128
129 90
        if ($this->findColumns($table)) {
130 84
            $this->findConstraints($table);
131
132 84
            return $table;
133
        }
134
135 14
        return null;
136
    }
137
138
    /**
139
     * Loads a primary key for the given table.
140
     *
141
     * @param string $tableName table name.
142
     *
143
     * @return Constraint|null primary key for the given table, `null` if the table has no primary key.
144
     */
145 31
    protected function loadTablePrimaryKey(string $tableName): ?Constraint
146
    {
147 31
        return $this->loadTableConstraints($tableName, 'primaryKey');
148
    }
149
150
    /**
151
     * Loads all foreign keys for the given table.
152
     *
153
     * @param string $tableName table name.
154
     *
155
     * @return ForeignKeyConstraint[] foreign keys for the given table.
156
     */
157 4
    protected function loadTableForeignKeys(string $tableName): array
158
    {
159 4
        return $this->loadTableConstraints($tableName, 'foreignKeys');
160
    }
161
162
    /**
163
     * Loads all indexes for the given table.
164
     *
165
     * @param string $tableName table name.
166
     *
167
     * @return IndexConstraint[] indexes for the given table.
168
     */
169 28
    protected function loadTableIndexes(string $tableName): array
170
    {
171 28
        static $sql = <<<'SQL'
172
SELECT
173
    `s`.`INDEX_NAME` AS `name`,
174
    `s`.`COLUMN_NAME` AS `column_name`,
175
    `s`.`NON_UNIQUE` ^ 1 AS `index_is_unique`,
176
    `s`.`INDEX_NAME` = 'PRIMARY' AS `index_is_primary`
177
FROM `information_schema`.`STATISTICS` AS `s`
178
WHERE `s`.`TABLE_SCHEMA` = COALESCE(:schemaName, DATABASE()) AND `s`.`INDEX_SCHEMA` = `s`.`TABLE_SCHEMA` AND `s`.`TABLE_NAME` = :tableName
179
ORDER BY `s`.`SEQ_IN_INDEX` ASC
180
SQL;
181
182 28
        $resolvedName = $this->resolveTableName($tableName);
183
184 28
        $indexes = $this->getDb()->createCommand($sql, [
185 28
            ':schemaName' => $resolvedName->getSchemaName(),
186 28
            ':tableName' => $resolvedName->getName(),
187 28
        ])->queryAll();
188
189 28
        $indexes = $this->normalizePdoRowKeyCase($indexes, true);
190 28
        $indexes = ArrayHelper::index($indexes, null, 'name');
191 28
        $result = [];
192
193 28
        foreach ($indexes as $name => $index) {
194 28
            $ic = new IndexConstraint();
195
196 28
            $ic->primary((bool) $index[0]['index_is_primary']);
197 28
            $ic->unique((bool) $index[0]['index_is_unique']);
198 28
            $ic->name($name !== 'PRIMARY' ? $name : null);
199 28
            $ic->columnNames(ArrayHelper::getColumn($index, 'column_name'));
200
201 28
            $result[] = $ic;
202
        }
203
204 28
        return $result;
205
    }
206
207
    /**
208
     * Loads all unique constraints for the given table.
209
     *
210
     * @param string $tableName table name.
211
     *
212
     * @return Constraint[] unique constraints for the given table.
213
     */
214 13
    protected function loadTableUniques(string $tableName): array
215
    {
216 13
        return $this->loadTableConstraints($tableName, 'uniques');
217
    }
218
219
    /**
220
     * Loads all check constraints for the given table.
221
     *
222
     * @param string $tableName table name.
223
     *
224
     * @return CheckConstraint[] check constraints for the given table.
225
     */
226 12
    protected function loadTableChecks(string $tableName): array
0 ignored issues
show
Unused Code introduced by
The parameter $tableName is not used and could be removed. ( Ignorable by Annotation )

If this is a false-positive, you can also ignore this issue in your code via the ignore-unused  annotation

226
    protected function loadTableChecks(/** @scrutinizer ignore-unused */ string $tableName): array

This check looks for parameters that have been defined for a function or method, but which are not used in the method body.

Loading history...
227
    {
228 12
        throw new NotSupportedException('MySQL does not support check constraints.');
229
    }
230
231
    /**
232
     * Loads all default value constraints for the given table.
233
     *
234
     * @param string $tableName table name.
235
     *
236
     * @return DefaultValueConstraint[] default value constraints for the given table.
237
     */
238 12
    protected function loadTableDefaultValues(string $tableName): array
0 ignored issues
show
Unused Code introduced by
The parameter $tableName is not used and could be removed. ( Ignorable by Annotation )

If this is a false-positive, you can also ignore this issue in your code via the ignore-unused  annotation

238
    protected function loadTableDefaultValues(/** @scrutinizer ignore-unused */ string $tableName): array

This check looks for parameters that have been defined for a function or method, but which are not used in the method body.

Loading history...
239
    {
240 12
        throw new NotSupportedException('MySQL does not support default value constraints.');
241
    }
242
243
    /**
244
     * Creates a query builder for the MySQL database.
245
     *
246
     * @return QueryBuilder query builder instance
247
     */
248 62
    public function createQueryBuilder(): QueryBuilder
249
    {
250 62
        return new QueryBuilder($this->getDb());
0 ignored issues
show
Bug introduced by
It seems like $this->getDb() can also be of type null; however, parameter $db of Yiisoft\Db\Mysql\Query\QueryBuilder::__construct() does only seem to accept Yiisoft\Db\Connection\Connection, maybe add an additional type check? ( Ignorable by Annotation )

If this is a false-positive, you can also ignore this issue in your code via the ignore-type  annotation

250
        return new QueryBuilder(/** @scrutinizer ignore-type */ $this->getDb());
Loading history...
251
    }
252
253
    /**
254
     * Resolves the table name and schema name (if any).
255
     *
256
     * @param TableSchema $table the table metadata object.
257
     * @param string $name the table name.
258
     */
259 90
    protected function resolveTableNames($table, $name): void
260
    {
261 90
        $parts = \explode('.', \str_replace('`', '', $name));
262
263 90
        if (isset($parts[1])) {
264
            $table->schemaName($parts[0]);
265
            $table->name($parts[1]);
266
            $table->fullName($table->getSchemaName() . '.' . $table->getName());
267
        } else {
268 90
            $table->name($parts[0]);
269 90
            $table->fullName($parts[0]);
270
        }
271 90
    }
272
273
    /**
274
     * Loads the column information into a {@see ColumnSchema} object.
275
     *
276
     * @param array $info column information.
277
     *
278
     * @return ColumnSchema the column schema object.
279
     */
280 85
    protected function loadColumnSchema(array $info): ColumnSchema
281
    {
282 85
        $column = $this->createColumnSchema();
283
284 85
        $column->name($info['field']);
285 85
        $column->allowNull($info['null'] === 'YES');
286 85
        $column->primaryKey(\strpos($info['key'], 'PRI') !== false);
287 85
        $column->autoIncrement(\stripos($info['extra'], 'auto_increment') !== false);
288 85
        $column->comment($info['comment']);
289 85
        $column->dbType($info['type']);
290 85
        $column->unsigned(\stripos($column->getDbType(), 'unsigned') !== false);
291 85
        $column->type(self::TYPE_STRING);
292
293 85
        if (\preg_match('/^(\w+)(?:\(([^)]+)\))?/', $column->getDbType(), $matches)) {
294 85
            $type = \strtolower($matches[1]);
295 85
            if (isset($this->typeMap[$type])) {
296 85
                $column->type($this->typeMap[$type]);
297
            }
298 85
            if (!empty($matches[2])) {
299 84
                if ($type === 'enum') {
300 22
                    \preg_match_all("/'[^']*'/", $matches[2], $values);
301 22
                    foreach ($values[0] as $i => $value) {
302 22
                        $values[$i] = \trim($value, "'");
303
                    }
304 22
                    $column->enumValues($values);
305
                } else {
306 84
                    $values = \explode(',', $matches[2]);
307 84
                    $column->precision((int) $values[0]);
308 84
                    $column->size((int) $values[0]);
309 84
                    if (isset($values[1])) {
310 25
                        $column->scale((int) $values[1]);
311
                    }
312 84
                    if ($column->getSize() === 1 && $type === 'bit') {
313 3
                        $column->type('boolean');
314 84
                    } elseif ($type === 'bit') {
315 22
                        if ($column->getSize() > 32) {
316
                            $column->type('bigint');
317 22
                        } elseif ($column->getSize() === 32) {
318
                            $column->type('integer');
319
                        }
320
                    }
321
                }
322
            }
323
        }
324
325 85
        $column->phpType($this->getColumnPhpType($column));
326
327 85
        if (!$column->isPrimaryKey()) {
328
            /**
329
             * When displayed in the INFORMATION_SCHEMA.COLUMNS table, a default CURRENT TIMESTAMP is displayed
330
             * as CURRENT_TIMESTAMP up until MariaDB 10.2.2, and as current_timestamp() from MariaDB 10.2.3.
331
             *
332
             * See details here: https://mariadb.com/kb/en/library/now/#description
333
             */
334
            if (
335 83
                ($column->getType() === 'timestamp' || $column->getType() === 'datetime')
336 83
                && \preg_match('/^current_timestamp(?:\(([0-9]*)\))?$/i', (string) $info['default'], $matches)
337
            ) {
338 25
                $column->defaultValue(new Expression('CURRENT_TIMESTAMP' . (!empty($matches[1])
339 25
                    ? '(' . $matches[1] . ')' : '')));
340 80
            } elseif (isset($type) && $type === 'bit') {
341 22
                $column->defaultValue(\bindec(\trim((string) $info['default'], 'b\'')));
342
            } else {
343 80
                $column->defaultValue($column->phpTypecast($info['default']));
344
            }
345
        }
346
347 85
        return $column;
348
    }
349
350
    /**
351
     * Collects the metadata of table columns.
352
     *
353
     * @param TableSchema $table the table metadata.
354
     *
355
     * @throws \Exception if DB query fails.
356
     *
357
     * @return bool whether the table exists in the database.
358
     */
359 90
    protected function findColumns($table): bool
360
    {
361 90
        $sql = 'SHOW FULL COLUMNS FROM ' . $this->quoteTableName($table->getFullName());
0 ignored issues
show
Bug introduced by
It seems like $table->getFullName() can also be of type null; however, parameter $name of Yiisoft\Db\Schema\Schema::quoteTableName() does only seem to accept string, maybe add an additional type check? ( Ignorable by Annotation )

If this is a false-positive, you can also ignore this issue in your code via the ignore-type  annotation

361
        $sql = 'SHOW FULL COLUMNS FROM ' . $this->quoteTableName(/** @scrutinizer ignore-type */ $table->getFullName());
Loading history...
362
363
        try {
364 90
            $columns = $this->getDb()->createCommand($sql)->queryAll();
365 14
        } catch (\Exception $e) {
366 14
            $previous = $e->getPrevious();
367
368 14
            if ($previous instanceof \PDOException && strpos($previous->getMessage(), 'SQLSTATE[42S02') !== false) {
369
                /**
370
                 * table does not exist.
371
                 *
372
                 * https://dev.mysql.com/doc/refman/5.5/en/error-messages-server.html#error_er_bad_table_error
373
                 */
374 14
                return false;
375
            }
376
377
            throw $e;
378
        }
379
380 84
        foreach ($columns as $info) {
381 84
            if ($this->getDb()->getSlavePdo()->getAttribute(\PDO::ATTR_CASE) !== \PDO::CASE_LOWER) {
382 83
                $info = \array_change_key_case($info, CASE_LOWER);
383
            }
384
385 84
            $column = $this->loadColumnSchema($info);
386 84
            $table->columns($column->getName(), $column);
387
388 84
            if ($column->isPrimaryKey()) {
389 60
                $table->primaryKey($column->getName());
390 60
                if ($column->isAutoIncrement()) {
391 58
                    $table->sequenceName('');
392
                }
393
            }
394
        }
395
396 84
        return true;
397
    }
398
399
    /**
400
     * Gets the CREATE TABLE sql string.
401
     *
402
     * @param TableSchema $table the table metadata.
403
     *
404
     * @throws Exception
405
     * @throws InvalidArgumentException
406
     * @throws InvalidConfigException
407
     *
408
     * @return string $sql the result of 'SHOW CREATE TABLE'.
409
     */
410 1
    protected function getCreateTableSql($table): string
411
    {
412 1
        $row = $this->getDb()->createCommand(
413 1
            'SHOW CREATE TABLE ' . $this->quoteTableName($table->getFullName())
0 ignored issues
show
Bug introduced by
It seems like $table->getFullName() can also be of type null; however, parameter $name of Yiisoft\Db\Schema\Schema::quoteTableName() does only seem to accept string, maybe add an additional type check? ( Ignorable by Annotation )

If this is a false-positive, you can also ignore this issue in your code via the ignore-type  annotation

413
            'SHOW CREATE TABLE ' . $this->quoteTableName(/** @scrutinizer ignore-type */ $table->getFullName())
Loading history...
414 1
        )->queryOne();
415
416 1
        if (isset($row['Create Table'])) {
417 1
            $sql = $row['Create Table'];
418
        } else {
419
            $row = \array_values($row);
0 ignored issues
show
Bug introduced by
$row of type false is incompatible with the type array expected by parameter $input of array_values(). ( Ignorable by Annotation )

If this is a false-positive, you can also ignore this issue in your code via the ignore-type  annotation

419
            $row = \array_values(/** @scrutinizer ignore-type */ $row);
Loading history...
420
            $sql = $row[1];
421
        }
422
423 1
        return $sql;
424
    }
425
426
    /**
427
     * Collects the foreign key column details for the given table.
428
     *
429
     * @param TableSchema $table the table metadata.
430
     *
431
     * @throws \Exception
432
     */
433 84
    protected function findConstraints($table)
434
    {
435
        $sql = <<<'SQL'
436 84
SELECT
437
    kcu.constraint_name,
438
    kcu.column_name,
439
    kcu.referenced_table_name,
440
    kcu.referenced_column_name
441
FROM information_schema.referential_constraints AS rc
442
JOIN information_schema.key_column_usage AS kcu ON
443
    (
444
        kcu.constraint_catalog = rc.constraint_catalog OR
445
        (kcu.constraint_catalog IS NULL AND rc.constraint_catalog IS NULL)
446
    ) AND
447
    kcu.constraint_schema = rc.constraint_schema AND
448
    kcu.constraint_name = rc.constraint_name
449
WHERE rc.constraint_schema = database() AND kcu.table_schema = database()
450
AND rc.table_name = :tableName AND kcu.table_name = :tableName1
451
SQL;
452
453
        try {
454 84
            $rows = $this->getDb()->createCommand(
455
                $sql,
456 84
                [':tableName' => $table->getName(), ':tableName1' => $table->getName()]
457 84
            )->queryAll();
458
459 84
            $constraints = [];
460
461 84
            foreach ($rows as $row) {
462 27
                $constraints[$row['constraint_name']]['referenced_table_name'] = $row['referenced_table_name'];
463 27
                $constraints[$row['constraint_name']]['columns'][$row['column_name']] = $row['referenced_column_name'];
464
            }
465
466 84
            $table->foreignKeys([]);
467
468 84
            foreach ($constraints as $name => $constraint) {
469 27
                $table->foreignKey($name, \array_merge(
470 27
                    [$constraint['referenced_table_name']],
471 27
                    $constraint['columns']
472
                ));
473
            }
474
        } catch (\Exception $e) {
475
            $previous = $e->getPrevious();
476
477
            if (!$previous instanceof \PDOException || \strpos($previous->getMessage(), 'SQLSTATE[42S02') === false) {
478
                throw $e;
479
            }
480
481
            // table does not exist, try to determine the foreign keys using the table creation sql
482
            $sql = $this->getCreateTableSql($table);
483
            $regexp = '/FOREIGN KEY\s+\(([^\)]+)\)\s+REFERENCES\s+([^\(^\s]+)\s*\(([^\)]+)\)/mi';
484
485
            if (\preg_match_all($regexp, $sql, $matches, PREG_SET_ORDER)) {
486
                foreach ($matches as $match) {
487
                    $fks = array_map('trim', \explode(',', \str_replace('`', '', $match[1])));
488
                    $pks = array_map('trim', \explode(',', \str_replace('`', '', $match[3])));
489
                    $constraint = [\str_replace('`', '', $match[2])];
490
491
                    foreach ($fks as $k => $name) {
492
                        $constraint[$name] = $pks[$k];
493
                    }
494
495
                    $table->foreignKey(\md5(\serialize($constraint)), $constraint);
496
                }
497
                $table->foreignKeys(\array_values($table->getForeignKeys()));
498
            }
499
        }
500 84
    }
501
502
    /**
503
     * Returns all unique indexes for the given table.
504
     *
505
     * Each array element is of the following structure:
506
     *
507
     * ```php
508
     * [
509
     *     'IndexName1' => ['col1' [, ...]],
510
     *     'IndexName2' => ['col2' [, ...]],
511
     * ]
512
     * ```
513
     *
514
     * @param TableSchema $table the table metadata.
515
     *
516
     * @return array all unique indexes for the given table.
517
     */
518 1
    public function findUniqueIndexes($table): array
519
    {
520 1
        $sql = $this->getCreateTableSql($table);
521
522 1
        $uniqueIndexes = [];
523
524 1
        $regexp = '/UNIQUE KEY\s+\`(.+)\`\s*\((\`.+\`)+\)/mi';
525
526 1
        if (\preg_match_all($regexp, $sql, $matches, PREG_SET_ORDER)) {
527 1
            foreach ($matches as $match) {
528 1
                $indexName = $match[1];
529 1
                $indexColumns = \array_map('trim', \explode('`,`', \trim($match[2], '`')));
530 1
                $uniqueIndexes[$indexName] = $indexColumns;
531
            }
532
        }
533
534 1
        return $uniqueIndexes;
535
    }
536
537 1
    public function createColumnSchemaBuilder($type, $length = null)
538
    {
539 1
        return new ColumnSchemaBuilder($type, $length, $this->getDb());
540
    }
541
542
    /**
543
     * @throws InvalidConfigException
544
     * @throws Exception
545
     *
546
     * @return bool whether the version of the MySQL being used is older than 5.1.
547
     */
548
    protected function isOldMysql(): bool
549
    {
550
        if ($this->oldMysql === null) {
551
            $version = $this->getDb()->getSlavePdo()->getAttribute(\PDO::ATTR_SERVER_VERSION);
552
553
            $this->oldMysql = \version_compare($version, '5.1', '<=');
554
        }
555
556
        return $this->oldMysql;
557
    }
558
559
    /**
560
     * Loads multiple types of constraints and returns the specified ones.
561
     *
562
     * @param string $tableName table name.
563
     * @param string $returnType return type:
564
     * - primaryKey
565
     * - foreignKeys
566
     * - uniques
567
     *
568
     * @throws Exception
569
     * @throws InvalidArgumentException
570
     * @throws InvalidConfigException
571
     *
572
     * @return mixed constraints.
573
     */
574 48
    private function loadTableConstraints(string $tableName, string $returnType)
575
    {
576 48
        static $sql = <<<'SQL'
577
SELECT
578
    `kcu`.`CONSTRAINT_NAME` AS `name`,
579
    `kcu`.`COLUMN_NAME` AS `column_name`,
580
    `tc`.`CONSTRAINT_TYPE` AS `type`,
581
    CASE
582
        WHEN :schemaName IS NULL AND `kcu`.`REFERENCED_TABLE_SCHEMA` = DATABASE() THEN NULL
583
        ELSE `kcu`.`REFERENCED_TABLE_SCHEMA`
584
    END AS `foreign_table_schema`,
585
    `kcu`.`REFERENCED_TABLE_NAME` AS `foreign_table_name`,
586
    `kcu`.`REFERENCED_COLUMN_NAME` AS `foreign_column_name`,
587
    `rc`.`UPDATE_RULE` AS `on_update`,
588
    `rc`.`DELETE_RULE` AS `on_delete`,
589
    `kcu`.`ORDINAL_POSITION` AS `position`
590
FROM
591
    `information_schema`.`KEY_COLUMN_USAGE` AS `kcu`,
592
    `information_schema`.`REFERENTIAL_CONSTRAINTS` AS `rc`,
593
    `information_schema`.`TABLE_CONSTRAINTS` AS `tc`
594
WHERE
595
    `kcu`.`TABLE_SCHEMA` = COALESCE(:schemaName, DATABASE()) AND `kcu`.`CONSTRAINT_SCHEMA` = `kcu`.`TABLE_SCHEMA` AND `kcu`.`TABLE_NAME` = :tableName
596
    AND `rc`.`CONSTRAINT_SCHEMA` = `kcu`.`TABLE_SCHEMA` AND `rc`.`TABLE_NAME` = :tableName AND `rc`.`CONSTRAINT_NAME` = `kcu`.`CONSTRAINT_NAME`
597
    AND `tc`.`TABLE_SCHEMA` = `kcu`.`TABLE_SCHEMA` AND `tc`.`TABLE_NAME` = :tableName AND `tc`.`CONSTRAINT_NAME` = `kcu`.`CONSTRAINT_NAME` AND `tc`.`CONSTRAINT_TYPE` = 'FOREIGN KEY'
598
UNION
599
SELECT
600
    `kcu`.`CONSTRAINT_NAME` AS `name`,
601
    `kcu`.`COLUMN_NAME` AS `column_name`,
602
    `tc`.`CONSTRAINT_TYPE` AS `type`,
603
    NULL AS `foreign_table_schema`,
604
    NULL AS `foreign_table_name`,
605
    NULL AS `foreign_column_name`,
606
    NULL AS `on_update`,
607
    NULL AS `on_delete`,
608
    `kcu`.`ORDINAL_POSITION` AS `position`
609
FROM
610
    `information_schema`.`KEY_COLUMN_USAGE` AS `kcu`,
611
    `information_schema`.`TABLE_CONSTRAINTS` AS `tc`
612
WHERE
613
    `kcu`.`TABLE_SCHEMA` = COALESCE(:schemaName, DATABASE()) AND `kcu`.`TABLE_NAME` = :tableName
614
    AND `tc`.`TABLE_SCHEMA` = `kcu`.`TABLE_SCHEMA` AND `tc`.`TABLE_NAME` = :tableName AND `tc`.`CONSTRAINT_NAME` = `kcu`.`CONSTRAINT_NAME` AND `tc`.`CONSTRAINT_TYPE` IN ('PRIMARY KEY', 'UNIQUE')
615
ORDER BY `position` ASC
616
SQL;
617
618 48
        $resolvedName = $this->resolveTableName($tableName);
619
620 48
        $constraints = $this->getDb()->createCommand(
621
            $sql,
622
            [
623 48
                ':schemaName' => $resolvedName->getSchemaName(),
624 48
                ':tableName' => $resolvedName->getName(),
625
            ]
626 48
        )->queryAll();
627
628 48
        $constraints = $this->normalizePdoRowKeyCase($constraints, true);
629 48
        $constraints = ArrayHelper::index($constraints, null, ['type', 'name']);
630
631
        $result = [
632 48
            'primaryKey' => null,
633
            'foreignKeys' => [],
634
            'uniques' => [],
635
        ];
636
637 48
        foreach ($constraints as $type => $names) {
638 48
            foreach ($names as $name => $constraint) {
639
                switch ($type) {
640 48
                    case 'PRIMARY KEY':
641 37
                        $ct = (new Constraint())
642 37
                            ->columnNames(ArrayHelper::getColumn($constraint, 'column_name'));
643
644 37
                        $result['primaryKey'] = $ct;
645
646 37
                        break;
647 46
                    case 'FOREIGN KEY':
648 10
                        $fk = (new ForeignKeyConstraint())
649 10
                            ->name($name)
650 10
                            ->columnNames(ArrayHelper::getColumn($constraint, 'column_name'))
651 10
                            ->foreignSchemaName($constraint[0]['foreign_table_schema'])
652 10
                            ->foreignTableName($constraint[0]['foreign_table_name'])
653 10
                            ->foreignColumnNames(ArrayHelper::getColumn($constraint, 'foreign_column_name'))
654 10
                            ->onDelete($constraint[0]['on_delete'])
655 10
                            ->onUpdate($constraint[0]['on_update']);
656
657 10
                        $result['foreignKeys'][] = $fk;
658
659 10
                        break;
660 37
                    case 'UNIQUE':
661 37
                        $ct = (new Constraint())
662 37
                            ->name($name)
663 37
                            ->columnNames(ArrayHelper::getColumn($constraint, 'column_name'));
664
665 37
                        $result['uniques'][] = $ct;
666
667 37
                        break;
668
                }
669
            }
670
        }
671
672 48
        foreach ($result as $type => $data) {
673 48
            $this->setTableMetadata($tableName, $type, $data);
674
        }
675
676 48
        return $result[$returnType];
677
    }
678
679
    /**
680
     * Creates a column schema for the database.
681
     *
682
     * This method may be overridden by child classes to create a DBMS-specific column schema.
683
     *
684
     * @return ColumnSchema column schema instance.
685
     */
686 85
    protected function createColumnSchema(): ColumnSchema
687
    {
688 85
        return new ColumnSchema();
689
    }
690
}
691