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