Schema::loadColumnSchema() - Code Metrics - Inspection of "Consistent behavior of `run()` method in widgets" - yiisoft/yii2 - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Push — 2.1 ( c952e8...98ed49 )

by Carsten

created 2016-06-20 11:40 UTC

Schema::loadColumnSchema() D

↳ Parent: Schema

Complexity

Conditions	16
Paths	184

Size

Total Lines	48
Code Lines	33

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	0
CRAP Score	272

Importance

Changes

Metric	Value
dl	0
loc	48
rs	4.6845
c	0
b	0
f	0
ccs	0
cts	43
cp	0
cc	16
eloc	33
nc	184
nop	1
crap	272

How to fix Complexity

<?php
/**
 * @link http://www.yiiframework.com/
 * @copyright Copyright (c) 2008 Yii Software LLC
 * @license http://www.yiiframework.com/license/
 */

namespace yii\db\mssql;

use yii\db\ColumnSchema;

/**
 * Schema is the class for retrieving metadata from a MS SQL Server databases (version 2008 and above).
 *
 * @author Timur Ruziev <[email protected]>
 * @since 2.0
 */
class Schema extends \yii\db\Schema
{
    /**
     * @var string the default schema used for the current session.
     */
    public $defaultSchema = 'dbo';
    /**
     * @var array mapping from physical column types (keys) to abstract column types (values)
     */
    public $typeMap = [
        // exact numbers
        'bigint' => self::TYPE_BIGINT,
        'numeric' => self::TYPE_DECIMAL,
        'bit' => self::TYPE_SMALLINT,
        'smallint' => self::TYPE_SMALLINT,
        'decimal' => self::TYPE_DECIMAL,
        'smallmoney' => self::TYPE_MONEY,
        'int' => self::TYPE_INTEGER,
        'tinyint' => self::TYPE_SMALLINT,
        'money' => self::TYPE_MONEY,
        // approximate numbers
        'float' => self::TYPE_FLOAT,
        'double' => self::TYPE_DOUBLE,
        'real' => self::TYPE_FLOAT,
        // date and time
        'date' => self::TYPE_DATE,
        'datetimeoffset' => self::TYPE_DATETIME,
        'datetime2' => self::TYPE_DATETIME,
        'smalldatetime' => self::TYPE_DATETIME,
        'datetime' => self::TYPE_DATETIME,
        'time' => self::TYPE_TIME,
        // character strings
        'char' => self::TYPE_CHAR,
        'varchar' => self::TYPE_STRING,
        'text' => self::TYPE_TEXT,
        // unicode character strings
        'nchar' => self::TYPE_CHAR,
        'nvarchar' => self::TYPE_STRING,
        'ntext' => self::TYPE_TEXT,
        // binary strings
        'binary' => self::TYPE_BINARY,
        'varbinary' => self::TYPE_BINARY,
        'image' => self::TYPE_BINARY,
        // other data types
        // 'cursor' type cannot be used with tables
        'timestamp' => self::TYPE_TIMESTAMP,
        'hierarchyid' => self::TYPE_STRING,
        'uniqueidentifier' => self::TYPE_STRING,
        'sql_variant' => self::TYPE_STRING,
        'xml' => self::TYPE_STRING,
        'table' => self::TYPE_STRING,
    ];


    /**
     * @inheritdoc
     */
    public function createSavepoint($name)
    {
        $this->db->createCommand("SAVE TRANSACTION $name")->execute();
    }

    /**
     * @inheritdoc
     */
    public function releaseSavepoint($name)
    {
        // does nothing as MSSQL does not support this
    }

    /**
     * @inheritdoc
     */
    public function rollBackSavepoint($name)
    {
        $this->db->createCommand("ROLLBACK TRANSACTION $name")->execute();
    }

    /**
     * Quotes a table name for use in a query.
     * A simple table name has no schema prefix.
     * @param string $name table name.
     * @return string the properly quoted table name.
     */
    public function quoteSimpleTableName($name)
    {
        return strpos($name, '[') === false ? "[{$name}]" : $name;
    }

    /**
     * Quotes a column name for use in a query.
     * A simple column name has no prefix.
     * @param string $name column name.
     * @return string the properly quoted column name.
     */
    public function quoteSimpleColumnName($name)
    {
        return strpos($name, '[') === false && $name !== '*' ? "[{$name}]" : $name;
    }

    /**
     * Creates a query builder for the MSSQL database.
     * @return QueryBuilder query builder interface.
     */
    public function createQueryBuilder()
    {
        return new QueryBuilder($this->db);
    }

    /**
     * Loads the metadata for the specified table.
     * @param string $name table name
     * @return TableSchema|null driver dependent table metadata. Null if the table does not exist.
     */
    public function loadTableSchema($name)
    {
        $table = new TableSchema();
        $this->resolveTableNames($table, $name);
        $this->findPrimaryKeys($table);
        if ($this->findColumns($table)) {
            $this->findForeignKeys($table);

            return $table;
        } else {
            return null;
        }
    }

    /**
     * Resolves the table name and schema name (if any).
     * @param TableSchema $table the table metadata object
     * @param string $name the table name
     */
    protected function resolveTableNames($table, $name)
    {
        $parts = explode('.', str_replace(['[', ']'], '', $name));
        $partCount = count($parts);
        if ($partCount === 3) {
            // catalog name, schema name and table name passed
            $table->catalogName = $parts[0];
            $table->schemaName = $parts[1];
            $table->name = $parts[2];
            $table->fullName = $table->catalogName . '.' . $table->schemaName . '.' . $table->name;
        } elseif ($partCount === 2) {
            // only schema name and table name passed
            $table->schemaName = $parts[0];
            $table->name = $parts[1];
            $table->fullName = $table->schemaName !== $this->defaultSchema ? $table->schemaName . '.' . $table->name : $table->name;
        } else {
            // only table name passed
            $table->schemaName = $this->defaultSchema;
            $table->fullName = $table->name = $parts[0];
        }
    }

    /**
     * Loads the column information into a [[ColumnSchema]] object.
     * @param array $info column information
     * @return ColumnSchema the column schema object
     */
    protected function loadColumnSchema($info)
    {
        $column = $this->createColumnSchema();

        $column->name = $info['column_name'];
        $column->allowNull = $info['is_nullable'] === 'YES';
        $column->dbType = $info['data_type'];
        $column->enumValues = []; // mssql has only vague equivalents to enum
        $column->isPrimaryKey = null; // primary key will be determined in findColumns() method
        $column->autoIncrement = $info['is_identity'] == 1;
        $column->unsigned = stripos($column->dbType, 'unsigned') !== false;
        $column->comment = $info['comment'] === null ? '' : $info['comment'];

        $column->type = self::TYPE_STRING;
        if (preg_match('/^(\w+)(?:\(([^\)]+)\))?/', $column->dbType, $matches)) {
            $type = $matches[1];
            if (isset($this->typeMap[$type])) {
                $column->type = $this->typeMap[$type];
            }
            if (!empty($matches[2])) {
                $values = explode(',', $matches[2]);
                $column->size = $column->precision = (int) $values[0];
                if (isset($values[1])) {
                    $column->scale = (int) $values[1];
                }
                if ($column->size === 1 && ($type === 'tinyint' || $type === 'bit')) {
                    $column->type = 'boolean';
                } elseif ($type === 'bit') {
                    if ($column->size > 32) {
                        $column->type = 'bigint';
                    } elseif ($column->size === 32) {
                        $column->type = 'integer';
                    }
                }
            }
        }

        $column->phpType = $this->getColumnPhpType($column);

        if ($info['column_default'] === '(NULL)') {
            $info['column_default'] = null;
        }
        if (!$column->isPrimaryKey && ($column->type !== 'timestamp' || $info['column_default'] !== 'CURRENT_TIMESTAMP')) {
            $column->defaultValue = $column->phpTypecast($info['column_default']);
        }

        return $column;
    }

    /**
     * Collects the metadata of table columns.
     * @param TableSchema $table the table metadata
     * @return boolean whether the table exists in the database
     */
    protected function findColumns($table)
    {
        $columnsTableName = 'INFORMATION_SCHEMA.COLUMNS';
        $whereSql = "[t1].[table_name] = '{$table->name}'";
        if ($table->catalogName !== null) {
            $columnsTableName = "{$table->catalogName}.{$columnsTableName}";
            $whereSql .= " AND [t1].[table_catalog] = '{$table->catalogName}'";
        }
        if ($table->schemaName !== null) {
            $whereSql .= " AND [t1].[table_schema] = '{$table->schemaName}'";
        }
        $columnsTableName = $this->quoteTableName($columnsTableName);

        $sql = <<<SQL
SELECT
    [t1].[column_name], [t1].[is_nullable], [t1].[data_type], [t1].[column_default],
    COLUMNPROPERTY(OBJECT_ID([t1].[table_schema] + '.' + [t1].[table_name]), [t1].[column_name], 'IsIdentity') AS is_identity,
    CONVERT(VARCHAR, [t2].[value]) AS comment
FROM {$columnsTableName} AS [t1]
LEFT OUTER JOIN [sys].[extended_properties] AS [t2] ON
    [t2].[minor_id] = COLUMNPROPERTY(OBJECT_ID([t1].[TABLE_SCHEMA] + '.' + [t1].[TABLE_NAME]), [t1].[COLUMN_NAME], 'ColumnID') AND
    OBJECT_NAME([t2].[major_id]) = [t1].[table_name] AND
    [t2].[class] = 1 AND
    [t2].[class_desc] = 'OBJECT_OR_COLUMN' AND
    [t2].[name] = 'MS_Description'
WHERE {$whereSql}
SQL;

        try {
            $columns = $this->db->createCommand($sql)->queryAll();
            if (empty($columns)) {
                return false;
            }
        } catch (\Exception $e) {
            return false;
        }
        foreach ($columns as $column) {
            $column = $this->loadColumnSchema($column);
            foreach ($table->primaryKey as $primaryKey) {
                if (strcasecmp($column->name, $primaryKey) === 0) {
                    $column->isPrimaryKey = true;
                    break;
                }
            }
            if ($column->isPrimaryKey && $column->autoIncrement) {
                $table->sequenceName = '';
            }
            $table->columns[$column->name] = $column;
        }

        return true;
    }

    /**
     * Collects the constraint details for the given table and constraint type.
     * @param TableSchema $table
     * @param string $type either PRIMARY KEY or UNIQUE
     * @return array each entry contains index_name and field_name
     * @since 2.0.4
     */
    protected function findTableConstraints($table, $type)
    {
        $keyColumnUsageTableName = 'INFORMATION_SCHEMA.KEY_COLUMN_USAGE';
        $tableConstraintsTableName = 'INFORMATION_SCHEMA.TABLE_CONSTRAINTS';
        if ($table->catalogName !== null) {
            $keyColumnUsageTableName = $table->catalogName . '.' . $keyColumnUsageTableName;
            $tableConstraintsTableName = $table->catalogName . '.' . $tableConstraintsTableName;
        }
        $keyColumnUsageTableName = $this->quoteTableName($keyColumnUsageTableName);
        $tableConstraintsTableName = $this->quoteTableName($tableConstraintsTableName);

        $sql = <<<SQL
SELECT
    [kcu].[constraint_name] AS [index_name],
    [kcu].[column_name] AS [field_name]
FROM {$keyColumnUsageTableName} AS [kcu]
LEFT JOIN {$tableConstraintsTableName} AS [tc] ON
    [kcu].[table_schema] = [tc].[table_schema] AND
    [kcu].[table_name] = [tc].[table_name] AND
    [kcu].[constraint_name] = [tc].[constraint_name]
WHERE
    [tc].[constraint_type] = :type AND
    [kcu].[table_name] = :tableName AND
    [kcu].[table_schema] = :schemaName
SQL;

        return $this->db
            ->createCommand($sql, [
                ':tableName' => $table->name,
                ':schemaName' => $table->schemaName,
                ':type' => $type,
            ])
            ->queryAll();
    }

    /**
     * Collects the primary key column details for the given table.
     * @param TableSchema $table the table metadata
     */
    protected function findPrimaryKeys($table)
    {
        $result = [];
        foreach ($this->findTableConstraints($table, 'PRIMARY KEY') as $row) {
            $result[] = $row['field_name'];
        }
        $table->primaryKey = $result;
    }

    /**
     * Collects the foreign key column details for the given table.
     * @param TableSchema $table the table metadata
     */
    protected function findForeignKeys($table)
    {
        $referentialConstraintsTableName = 'INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS';
        $keyColumnUsageTableName = 'INFORMATION_SCHEMA.KEY_COLUMN_USAGE';
        if ($table->catalogName !== null) {
            $referentialConstraintsTableName = $table->catalogName . '.' . $referentialConstraintsTableName;
            $keyColumnUsageTableName = $table->catalogName . '.' . $keyColumnUsageTableName;
        }
        $referentialConstraintsTableName = $this->quoteTableName($referentialConstraintsTableName);
        $keyColumnUsageTableName = $this->quoteTableName($keyColumnUsageTableName);

        // please refer to the following page for more details:
        // http://msdn2.microsoft.com/en-us/library/aa175805(SQL.80).aspx
        $sql = <<<SQL
SELECT
    [kcu1].[column_name] AS [fk_column_name],
    [kcu2].[table_name] AS [uq_table_name],
    [kcu2].[column_name] AS [uq_column_name]
FROM {$referentialConstraintsTableName} AS [rc]
JOIN {$keyColumnUsageTableName} AS [kcu1] ON
    [kcu1].[constraint_catalog] = [rc].[constraint_catalog] AND
    [kcu1].[constraint_schema] = [rc].[constraint_schema] AND
    [kcu1].[constraint_name] = [rc].[constraint_name]
JOIN {$keyColumnUsageTableName} AS [kcu2] ON
    [kcu2].[constraint_catalog] = [rc].[constraint_catalog] AND
    [kcu2].[constraint_schema] = [rc].[constraint_schema] AND
    [kcu2].[constraint_name] = [rc].[unique_constraint_name] AND
    [kcu2].[ordinal_position] = [kcu1].[ordinal_position]
WHERE [kcu1].[table_name] = :tableName AND [kcu1].[table_schema] = :schemaName
SQL;

        $rows = $this->db->createCommand($sql, [
            ':tableName' => $table->name,
            ':schemaName' => $table->schemaName,
        ])->queryAll();
        $table->foreignKeys = [];
        foreach ($rows as $row) {
            $table->foreignKeys[] = [$row['uq_table_name'], $row['fk_column_name'] => $row['uq_column_name']];
        }
    }

    /**
     * Returns all table names in the database.
     * @param string $schema the schema of the tables. Defaults to empty string, meaning the current or default schema.
     * @return array all table names in the database. The names have NO schema name prefix.
     */
    protected function findTableNames($schema = '')
    {
        if ($schema === '') {
            $schema = $this->defaultSchema;
        }

        $sql = <<<SQL
SELECT [t].[table_name]
FROM [INFORMATION_SCHEMA].[TABLES] AS [t]
WHERE [t].[table_schema] = :schema AND [t].[table_type] IN ('BASE TABLE', 'VIEW')
ORDER BY [t].[table_name]
SQL;

        return $this->db->createCommand($sql, [':schema' => $schema])->queryColumn();
    }

    /**
     * Returns all unique indexes for the given table.
     * Each array element is of the following structure:
     *
     * ```php
     * [
     *     'IndexName1' => ['col1' [, ...]],
     *     'IndexName2' => ['col2' [, ...]],
     * ]
     * ```
     *
     * @param TableSchema $table the table metadata
     * @return array all unique indexes for the given table.
     * @since 2.0.4
     */
    public function findUniqueIndexes($table)
    {
        $result = [];
        foreach ($this->findTableConstraints($table, 'UNIQUE') as $row) {
            $result[$row['index_name']][] = $row['field_name'];
        }
        return $result;
    }
}


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\ColumnSchema;
11
12			/**
13			* Schema is the class for retrieving metadata from a MS SQL Server databases (version 2008 and above).
14			*
15			* @author Timur Ruziev <[email protected]>
16			* @since 2.0
17			*/
18			class Schema extends \yii\db\Schema
19			{
20			/**
21			* @var string the default schema used for the current session.
22			*/
23			public $defaultSchema = 'dbo';
24			/**
25			* @var array mapping from physical column types (keys) to abstract column types (values)
26			*/
27			public $typeMap = [
28			// exact numbers
29			'bigint' => self::TYPE_BIGINT,
30			'numeric' => self::TYPE_DECIMAL,
31			'bit' => self::TYPE_SMALLINT,
32			'smallint' => self::TYPE_SMALLINT,
33			'decimal' => self::TYPE_DECIMAL,
34			'smallmoney' => self::TYPE_MONEY,
35			'int' => self::TYPE_INTEGER,
36			'tinyint' => self::TYPE_SMALLINT,
37			'money' => self::TYPE_MONEY,
38			// approximate numbers
39			'float' => self::TYPE_FLOAT,
40			'double' => self::TYPE_DOUBLE,
41			'real' => self::TYPE_FLOAT,
42			// date and time
43			'date' => self::TYPE_DATE,
44			'datetimeoffset' => self::TYPE_DATETIME,
45			'datetime2' => self::TYPE_DATETIME,
46			'smalldatetime' => self::TYPE_DATETIME,
47			'datetime' => self::TYPE_DATETIME,
48			'time' => self::TYPE_TIME,
49			// character strings
50			'char' => self::TYPE_CHAR,
51			'varchar' => self::TYPE_STRING,
52			'text' => self::TYPE_TEXT,
53			// unicode character strings
54			'nchar' => self::TYPE_CHAR,
55			'nvarchar' => self::TYPE_STRING,
56			'ntext' => self::TYPE_TEXT,
57			// binary strings
58			'binary' => self::TYPE_BINARY,
59			'varbinary' => self::TYPE_BINARY,
60			'image' => self::TYPE_BINARY,
61			// other data types
62			// 'cursor' type cannot be used with tables
63			'timestamp' => self::TYPE_TIMESTAMP,
64			'hierarchyid' => self::TYPE_STRING,
65			'uniqueidentifier' => self::TYPE_STRING,
66			'sql_variant' => self::TYPE_STRING,
67			'xml' => self::TYPE_STRING,
68			'table' => self::TYPE_STRING,
69			];
70
71
72			/**
73			* @inheritdoc
74			*/
75			public function createSavepoint($name)
76			{
77			$this->db->createCommand("SAVE TRANSACTION $name")->execute();
78			}
79
80			/**
81			* @inheritdoc
82			*/
83			public function releaseSavepoint($name)
84			{
85			// does nothing as MSSQL does not support this
86			}
87
88			/**
89			* @inheritdoc
90			*/
91			public function rollBackSavepoint($name)
92			{
93			$this->db->createCommand("ROLLBACK TRANSACTION $name")->execute();
94			}
95
96			/**
97			* Quotes a table name for use in a query.
98			* A simple table name has no schema prefix.
99			* @param string $name table name.
100			* @return string the properly quoted table name.
101			*/
102			public function quoteSimpleTableName($name)
103			{
104			return strpos($name, '[') === false ? "[{$name}]" : $name;
105			}
106
107			/**
108			* Quotes a column name for use in a query.
109			* A simple column name has no prefix.
110			* @param string $name column name.
111			* @return string the properly quoted column name.
112			*/
113			public function quoteSimpleColumnName($name)
114			{
115			return strpos($name, '[') === false && $name !== '*' ? "[{$name}]" : $name;
116			}
117
118			/**
119			* Creates a query builder for the MSSQL database.
120			* @return QueryBuilder query builder interface.
121			*/
122			public function createQueryBuilder()
123			{
124			return new QueryBuilder($this->db);
125			}
126
127			/**
128			* Loads the metadata for the specified table.
129			* @param string $name table name
130			* @return TableSchema\|null driver dependent table metadata. Null if the table does not exist.
131			*/
132			public function loadTableSchema($name)
133			{
134			$table = new TableSchema();
135			$this->resolveTableNames($table, $name);
136			$this->findPrimaryKeys($table);
137			if ($this->findColumns($table)) {
138			$this->findForeignKeys($table);
139
140			return $table;
141			} else {
142			return null;
143			}
144			}
145
146			/**
147			* Resolves the table name and schema name (if any).
148			* @param TableSchema $table the table metadata object
149			* @param string $name the table name
150			*/
151			protected function resolveTableNames($table, $name)
152			{
153			$parts = explode('.', str_replace(['[', ']'], '', $name));
154			$partCount = count($parts);
155			if ($partCount === 3) {
156			// catalog name, schema name and table name passed
157			$table->catalogName = $parts[0];
158			$table->schemaName = $parts[1];
159			$table->name = $parts[2];
160			$table->fullName = $table->catalogName . '.' . $table->schemaName . '.' . $table->name;
161			} elseif ($partCount === 2) {
162			// only schema name and table name passed
163			$table->schemaName = $parts[0];
164			$table->name = $parts[1];
165			$table->fullName = $table->schemaName !== $this->defaultSchema ? $table->schemaName . '.' . $table->name : $table->name;
166			} else {
167			// only table name passed
168			$table->schemaName = $this->defaultSchema;
169			$table->fullName = $table->name = $parts[0];
170			}
171			}
172
173			/**
174			* Loads the column information into a [[ColumnSchema]] object.
175			* @param array $info column information
176			* @return ColumnSchema the column schema object
177			*/
178			protected function loadColumnSchema($info)
179			{
180			$column = $this->createColumnSchema();
181
182			$column->name = $info['column_name'];
183			$column->allowNull = $info['is_nullable'] === 'YES';
184			$column->dbType = $info['data_type'];
185			$column->enumValues = []; // mssql has only vague equivalents to enum
186			$column->isPrimaryKey = null; // primary key will be determined in findColumns() method
187			$column->autoIncrement = $info['is_identity'] == 1;
188			$column->unsigned = stripos($column->dbType, 'unsigned') !== false;
189			$column->comment = $info['comment'] === null ? '' : $info['comment'];
190
191			$column->type = self::TYPE_STRING;
192			if (preg_match('/^(\w+)(?:\(([^\)]+)\))?/', $column->dbType, $matches)) {
193			$type = $matches[1];
194			if (isset($this->typeMap[$type])) {
195			$column->type = $this->typeMap[$type];
196			}
197			if (!empty($matches[2])) {
198			$values = explode(',', $matches[2]);
199			$column->size = $column->precision = (int) $values[0];
200			if (isset($values[1])) {
201			$column->scale = (int) $values[1];
202			}
203			if ($column->size === 1 && ($type === 'tinyint' \|\| $type === 'bit')) {
204			$column->type = 'boolean';
205			} elseif ($type === 'bit') {
206			if ($column->size > 32) {
207			$column->type = 'bigint';
208			} elseif ($column->size === 32) {
209			$column->type = 'integer';
210			}
211			}
212			}
213			}
214
215			$column->phpType = $this->getColumnPhpType($column);
216
217			if ($info['column_default'] === '(NULL)') {
218			$info['column_default'] = null;
219			}
220			if (!$column->isPrimaryKey && ($column->type !== 'timestamp' \|\| $info['column_default'] !== 'CURRENT_TIMESTAMP')) {
221			$column->defaultValue = $column->phpTypecast($info['column_default']);
222			}
223
224			return $column;
225			}
226
227			/**
228			* Collects the metadata of table columns.
229			* @param TableSchema $table the table metadata
230			* @return boolean whether the table exists in the database
231			*/
232			protected function findColumns($table)
233			{
234			$columnsTableName = 'INFORMATION_SCHEMA.COLUMNS';
235			$whereSql = "[t1].[table_name] = '{$table->name}'";
236			if ($table->catalogName !== null) {
237			$columnsTableName = "{$table->catalogName}.{$columnsTableName}";
238			$whereSql .= " AND [t1].[table_catalog] = '{$table->catalogName}'";
239			}
240			if ($table->schemaName !== null) {
241			$whereSql .= " AND [t1].[table_schema] = '{$table->schemaName}'";
242			}
243			$columnsTableName = $this->quoteTableName($columnsTableName);
244
245			$sql = <<<SQL
246			SELECT
247			[t1].[column_name], [t1].[is_nullable], [t1].[data_type], [t1].[column_default],
248			COLUMNPROPERTY(OBJECT_ID([t1].[table_schema] + '.' + [t1].[table_name]), [t1].[column_name], 'IsIdentity') AS is_identity,
249			CONVERT(VARCHAR, [t2].[value]) AS comment
250			FROM {$columnsTableName} AS [t1]
251			LEFT OUTER JOIN [sys].[extended_properties] AS [t2] ON
252			[t2].[minor_id] = COLUMNPROPERTY(OBJECT_ID([t1].[TABLE_SCHEMA] + '.' + [t1].[TABLE_NAME]), [t1].[COLUMN_NAME], 'ColumnID') AND
253			OBJECT_NAME([t2].[major_id]) = [t1].[table_name] AND
254			[t2].[class] = 1 AND
255			[t2].[class_desc] = 'OBJECT_OR_COLUMN' AND
256			[t2].[name] = 'MS_Description'
257			WHERE {$whereSql}
258			SQL;
259
260			try {
261			$columns = $this->db->createCommand($sql)->queryAll();
262			if (empty($columns)) {
263			return false;
264			}
265			} catch (\Exception $e) {
266			return false;
267			}
268			foreach ($columns as $column) {
269			$column = $this->loadColumnSchema($column);
270			foreach ($table->primaryKey as $primaryKey) {
271			if (strcasecmp($column->name, $primaryKey) === 0) {
272			$column->isPrimaryKey = true;
273			break;
274			}
275			}
276			if ($column->isPrimaryKey && $column->autoIncrement) {
277			$table->sequenceName = '';
278			}
279			$table->columns[$column->name] = $column;
280			}
281
282			return true;
283			}
284
285			/**
286			* Collects the constraint details for the given table and constraint type.
287			* @param TableSchema $table
288			* @param string $type either PRIMARY KEY or UNIQUE
289			* @return array each entry contains index_name and field_name
290			* @since 2.0.4
291			*/
292			protected function findTableConstraints($table, $type)
293			{
294			$keyColumnUsageTableName = 'INFORMATION_SCHEMA.KEY_COLUMN_USAGE';
295			$tableConstraintsTableName = 'INFORMATION_SCHEMA.TABLE_CONSTRAINTS';
296			if ($table->catalogName !== null) {
297			$keyColumnUsageTableName = $table->catalogName . '.' . $keyColumnUsageTableName;
298			$tableConstraintsTableName = $table->catalogName . '.' . $tableConstraintsTableName;
299			}
300			$keyColumnUsageTableName = $this->quoteTableName($keyColumnUsageTableName);
301			$tableConstraintsTableName = $this->quoteTableName($tableConstraintsTableName);
302
303			$sql = <<<SQL
304			SELECT
305			[kcu].[constraint_name] AS [index_name],
306			[kcu].[column_name] AS [field_name]
307			FROM {$keyColumnUsageTableName} AS [kcu]
308			LEFT JOIN {$tableConstraintsTableName} AS [tc] ON
309			[kcu].[table_schema] = [tc].[table_schema] AND
310			[kcu].[table_name] = [tc].[table_name] AND
311			[kcu].[constraint_name] = [tc].[constraint_name]
312			WHERE
313			[tc].[constraint_type] = :type AND
314			[kcu].[table_name] = :tableName AND
315			[kcu].[table_schema] = :schemaName
316			SQL;
317
318			return $this->db
319			->createCommand($sql, [
320			':tableName' => $table->name,
321			':schemaName' => $table->schemaName,
322			':type' => $type,
323			])
324			->queryAll();
325			}
326
327			/**
328			* Collects the primary key column details for the given table.
329			* @param TableSchema $table the table metadata
330			*/
331			protected function findPrimaryKeys($table)
332			{
333			$result = [];
334			foreach ($this->findTableConstraints($table, 'PRIMARY KEY') as $row) {
335			$result[] = $row['field_name'];
336			}
337			$table->primaryKey = $result;
338			}
339
340			/**
341			* Collects the foreign key column details for the given table.
342			* @param TableSchema $table the table metadata
343			*/
344			protected function findForeignKeys($table)
345			{
346			$referentialConstraintsTableName = 'INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS';
347			$keyColumnUsageTableName = 'INFORMATION_SCHEMA.KEY_COLUMN_USAGE';
348			if ($table->catalogName !== null) {
349			$referentialConstraintsTableName = $table->catalogName . '.' . $referentialConstraintsTableName;
350			$keyColumnUsageTableName = $table->catalogName . '.' . $keyColumnUsageTableName;
351			}
352			$referentialConstraintsTableName = $this->quoteTableName($referentialConstraintsTableName);
353			$keyColumnUsageTableName = $this->quoteTableName($keyColumnUsageTableName);
354
355			// please refer to the following page for more details:
356			// http://msdn2.microsoft.com/en-us/library/aa175805(SQL.80).aspx
357			$sql = <<<SQL
358			SELECT
359			[kcu1].[column_name] AS [fk_column_name],
360			[kcu2].[table_name] AS [uq_table_name],
361			[kcu2].[column_name] AS [uq_column_name]
362			FROM {$referentialConstraintsTableName} AS [rc]
363			JOIN {$keyColumnUsageTableName} AS [kcu1] ON
364			[kcu1].[constraint_catalog] = [rc].[constraint_catalog] AND
365			[kcu1].[constraint_schema] = [rc].[constraint_schema] AND
366			[kcu1].[constraint_name] = [rc].[constraint_name]
367			JOIN {$keyColumnUsageTableName} AS [kcu2] ON
368			[kcu2].[constraint_catalog] = [rc].[constraint_catalog] AND
369			[kcu2].[constraint_schema] = [rc].[constraint_schema] AND
370			[kcu2].[constraint_name] = [rc].[unique_constraint_name] AND
371			[kcu2].[ordinal_position] = [kcu1].[ordinal_position]
372			WHERE [kcu1].[table_name] = :tableName AND [kcu1].[table_schema] = :schemaName
373			SQL;
374
375			$rows = $this->db->createCommand($sql, [
376			':tableName' => $table->name,
377			':schemaName' => $table->schemaName,
378			])->queryAll();
379			$table->foreignKeys = [];
380			foreach ($rows as $row) {
381			$table->foreignKeys[] = [$row['uq_table_name'], $row['fk_column_name'] => $row['uq_column_name']];
382			}
383			}
384
385			/**
386			* Returns all table names in the database.
387			* @param string $schema the schema of the tables. Defaults to empty string, meaning the current or default schema.
388			* @return array all table names in the database. The names have NO schema name prefix.
389			*/
390			protected function findTableNames($schema = '')
391			{
392			if ($schema === '') {
393			$schema = $this->defaultSchema;
394			}
395
396			$sql = <<<SQL
397			SELECT [t].[table_name]
398			FROM [INFORMATION_SCHEMA].[TABLES] AS [t]
399			WHERE [t].[table_schema] = :schema AND [t].[table_type] IN ('BASE TABLE', 'VIEW')
400			ORDER BY [t].[table_name]
401			SQL;
402
403			return $this->db->createCommand($sql, [':schema' => $schema])->queryColumn();
404			}
405
406			/**
407			* Returns all unique indexes for the given table.
408			* Each array element is of the following structure:
409			*
410			* ```php
411			* [
412			* 'IndexName1' => ['col1' [, ...]],
413			* 'IndexName2' => ['col2' [, ...]],
414			* ]
415			* ```
416			*
417			* @param TableSchema $table the table metadata
418			* @return array all unique indexes for the given table.
419			* @since 2.0.4
420			*/
421			public function findUniqueIndexes($table)
422			{
423			$result = [];
424			foreach ($this->findTableConstraints($table, 'UNIQUE') as $row) {
425			$result[$row['index_name']][] = $row['field_name'];
426			}
427			return $result;
428			}
429			}
430

yiisoft / yii2

Push — 2.1 ( c952e8...98ed49 )

Schema::loadColumnSchema() D

Complexity

Size

Duplication

Code Coverage

Importance

How to fix Complexity

Long Method

Duplication Side-by-Side

Filter issues like