vendor/yiisoft/yii2/db/mssql/Schema.php

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