Schema::quoteSimpleColumnName() - Code Metrics - Inspection of "Update README.md" - rob006/yii2-dev - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Push — master ( ecd2dc...aafa57 )

by Robert

created 2017-03-01 11:05 UTC

Schema::quoteSimpleColumnName() A

↳ Parent: Schema

Complexity

Conditions	3
Paths	4

Size

Total Lines	4
Code Lines	2

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	0
CRAP Score	12

Importance

Changes

Metric	Value
dl	0
loc	4
rs	10
c	0
b	0
f	0
ccs	0
cts	4
cp	0
cc	3
eloc	2
nc	4
nop	1
crap	12

<?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;
use yii\db\ViewFinderTrait;

/**
 * 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
{
    use ViewFinderTrait;

    /**
     * @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 === 4) {
            // server name, catalog name, schema name and table name passed
            $table->catalogName = $parts[1];
            $table->schemaName = $parts[2];
            $table->name = $parts[3];
            $table->fullName = $table->catalogName . '.' . $table->schemaName . '.' . $table->name;
        } elseif ($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 bool 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
    [rc].[constraint_name] AS [fk_name],
    [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['fk_name']] = [$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();
    }

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

rob006 / yii2-dev

GitHub Access Token became invalid

Push — master ( ecd2dc...aafa57 )

Schema::quoteSimpleColumnName() A

Complexity

Size

Duplication

Code Coverage

Importance

Duplication Side-by-Side

Filter issues like