Schema::loadTableUniques() - Code Metrics - Inspection of "Fixed ambiguous column error in `BaseActiveRecord:..." - yiisoft/yii2 - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Push — master ( d2781c...0559a9 )

by Carsten

created 2017-07-11 09:48 UTC

Schema::loadTableUniques() A

↳ Parent: Schema

Complexity

Conditions	1
Paths	1

Size

Total Lines	4
Code Lines	2

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	2
CRAP Score	1

Importance

Changes

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

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

namespace yii\db\mysql;

use yii\base\InvalidConfigException;
use yii\base\NotSupportedException;
use yii\db\ColumnSchema;
use yii\db\Constraint;
use yii\db\ConstraintFinderTrait;
use yii\db\Exception;
use yii\db\Expression;
use yii\db\ForeignKeyConstraint;
use yii\db\IndexConstraint;
use yii\db\TableSchema;
use yii\helpers\ArrayHelper;

/**
 * Schema is the class for retrieving metadata from a MySQL database (version 4.1.x and 5.x).
 *
 * @author Qiang Xue <[email protected]>
 * @since 2.0
 */
class Schema extends \yii\db\Schema
{
    use ConstraintFinderTrait;

    /**
     * @var bool whether MySQL used is older than 5.1.
     */
    private $_oldMysql;


    /**
     * @var array mapping from physical column types (keys) to abstract column types (values)
     */
    public $typeMap = [
        'tinyint' => self::TYPE_SMALLINT,
        'bit' => self::TYPE_INTEGER,
        'smallint' => self::TYPE_SMALLINT,
        'mediumint' => self::TYPE_INTEGER,
        'int' => self::TYPE_INTEGER,
        'integer' => self::TYPE_INTEGER,
        'bigint' => self::TYPE_BIGINT,
        'float' => self::TYPE_FLOAT,
        'double' => self::TYPE_DOUBLE,
        'real' => self::TYPE_FLOAT,
        'decimal' => self::TYPE_DECIMAL,
        'numeric' => self::TYPE_DECIMAL,
        'tinytext' => self::TYPE_TEXT,
        'mediumtext' => self::TYPE_TEXT,
        'longtext' => self::TYPE_TEXT,
        'longblob' => self::TYPE_BINARY,
        'blob' => self::TYPE_BINARY,
        'text' => self::TYPE_TEXT,
        'varchar' => self::TYPE_STRING,
        'string' => self::TYPE_STRING,
        'char' => self::TYPE_CHAR,
        'datetime' => self::TYPE_DATETIME,
        'year' => self::TYPE_DATE,
        'date' => self::TYPE_DATE,
        'time' => self::TYPE_TIME,
        'timestamp' => self::TYPE_TIMESTAMP,
        'enum' => self::TYPE_STRING,
        'varbinary' => self::TYPE_BINARY,
    ];

    /**
     * @inheritDoc
     */
    protected function resolveTableName($name)
    {
        $resolvedName = new TableSchema();
        $parts = explode('.', str_replace('`', '', $name));
        if (isset($parts[1])) {
            $resolvedName->schemaName = $parts[0];
            $resolvedName->name = $parts[1];
        } else {
            $resolvedName->schemaName = $this->defaultSchema;
            $resolvedName->name = $name;
        }
        $resolvedName->fullName = ($resolvedName->schemaName !== $this->defaultSchema ? $resolvedName->schemaName . '.' : '') . $resolvedName->name;
        return $resolvedName;
    }

    /**
     * @inheritDoc
     */
    protected function findTableNames($schema = '')
    {
        $sql = 'SHOW TABLES';
        if ($schema !== '') {
            $sql .= ' FROM ' . $this->quoteSimpleTableName($schema);
        }

        return $this->db->createCommand($sql)->queryColumn();
    }

    /**
     * @inheritDoc
     */
    protected function loadTableSchema($name)
    {
        $table = new TableSchema();
        $this->resolveTableNames($table, $name);

        if ($this->findColumns($table)) {
            $this->findConstraints($table);
            return $table;
        }

        return null;
    }

    /**
     * @inheritDoc
     */
    protected function loadTablePrimaryKey($tableName)
    {
        return $this->loadTableConstraints($tableName, 'primaryKey');
    }

    /**
     * @inheritDoc
     */
    protected function loadTableForeignKeys($tableName)
    {
        return $this->loadTableConstraints($tableName, 'foreignKeys');
    }

    /**
     * @inheritDoc
     */
    protected function loadTableIndexes($tableName)
    {
        static $sql = <<<'SQL'
SELECT
    `s`.`INDEX_NAME` AS `name`,
    `s`.`COLUMN_NAME` AS `column_name`,
    `s`.`NON_UNIQUE` ^ 1 AS `index_is_unique`,
    `s`.`INDEX_NAME` = 'PRIMARY' AS `index_is_primary`
FROM `information_schema`.`STATISTICS` AS `s`
WHERE `s`.`TABLE_SCHEMA` = COALESCE(:schemaName, DATABASE()) AND `s`.`INDEX_SCHEMA` = `s`.`TABLE_SCHEMA` AND `s`.`TABLE_NAME` = :tableName
ORDER BY `s`.`SEQ_IN_INDEX` ASC
SQL;

        $resolvedName = $this->resolveTableName($tableName);
        $indexes = $this->db->createCommand($sql, [
            ':schemaName' => $resolvedName->schemaName,
            ':tableName' => $resolvedName->name,
        ])->queryAll();
        $indexes = $this->normalizePdoRowKeyCase($indexes, true);
        $indexes = ArrayHelper::index($indexes, null, 'name');
        $result = [];
        foreach ($indexes as $name => $index) {
            $result[] = new IndexConstraint([
                'isPrimary' => (bool) $index[0]['index_is_primary'],
                'isUnique' => (bool) $index[0]['index_is_unique'],
                'name' => $name !== 'PRIMARY' ? $name : null,
                'columnNames' => ArrayHelper::getColumn($index, 'column_name'),
            ]);
        }
        return $result;
    }

    /**
     * @inheritDoc
     */
    protected function loadTableUniques($tableName)
    {
        return $this->loadTableConstraints($tableName, 'uniques');
    }

    /**
     * @inheritDoc
     * @throws NotSupportedException if this method is called.
     */
    protected function loadTableChecks($tableName)

    {
        throw new NotSupportedException('MySQL does not support check constraints.');
    }

    /**
     * @inheritDoc
     * @throws NotSupportedException if this method is called.
     */
    protected function loadTableDefaultValues($tableName)

    {
        throw new NotSupportedException('MySQL does not support default value constraints.');
    }

    /**
     * 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 MySQL database.
     * @return QueryBuilder query builder instance
     */
    public function createQueryBuilder()
    {
        return new QueryBuilder($this->db);
    }

    /**
     * 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));
        if (isset($parts[1])) {
            $table->schemaName = $parts[0];
            $table->name = $parts[1];
            $table->fullName = $table->schemaName . '.' . $table->name;
        } else {
            $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['field'];
        $column->allowNull = $info['null'] === 'YES';
        $column->isPrimaryKey = strpos($info['key'], 'PRI') !== false;
        $column->autoIncrement = stripos($info['extra'], 'auto_increment') !== false;
        $column->comment = $info['comment'];

        $column->dbType = $info['type'];
        $column->unsigned = stripos($column->dbType, 'unsigned') !== false;

        $column->type = self::TYPE_STRING;
        if (preg_match('/^(\w+)(?:\(([^\)]+)\))?/', $column->dbType, $matches)) {
            $type = strtolower($matches[1]);
            if (isset($this->typeMap[$type])) {
                $column->type = $this->typeMap[$type];
            }
            if (!empty($matches[2])) {
                if ($type === 'enum') {
                    preg_match_all("/'[^']*'/", $matches[2], $values);
                    foreach ($values[0] as $i => $value) {
                        $values[$i] = trim($value, "'");
                    }
                    $column->enumValues = $values;
function aContainsB(array $needle = null, array  $haystack) {
    if (!$needle) {
        return false;
    }

    return array_intersect($haystack, $needle) == $haystack;
}
                } else {
                    $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 === '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 (!$column->isPrimaryKey) {
            if ($column->type === 'timestamp' && $info['default'] === 'CURRENT_TIMESTAMP') {
                $column->defaultValue = new Expression('CURRENT_TIMESTAMP');
            } elseif (isset($type) && $type === 'bit') {
                $column->defaultValue = bindec(trim($info['default'], 'b\''));
            } else {
                $column->defaultValue = $column->phpTypecast($info['default']);
            }
        }

        return $column;
    }

    /**
     * Collects the metadata of table columns.
     * @param TableSchema $table the table metadata
     * @return bool whether the table exists in the database
     * @throws \Exception if DB query fails
     */
    protected function findColumns($table)
    {
        $sql = 'SHOW FULL COLUMNS FROM ' . $this->quoteTableName($table->fullName);
        try {
            $columns = $this->db->createCommand($sql)->queryAll();
        } catch (\Exception $e) {
            $previous = $e->getPrevious();
            if ($previous instanceof \PDOException && strpos($previous->getMessage(), 'SQLSTATE[42S02') !== false) {
                // table does not exist
                // https://dev.mysql.com/doc/refman/5.5/en/error-messages-server.html#error_er_bad_table_error
                return false;
            }
            throw $e;
        }
        foreach ($columns as $info) {
            if ($this->db->slavePdo->getAttribute(\PDO::ATTR_CASE) !== \PDO::CASE_LOWER) {
                $info = array_change_key_case($info, CASE_LOWER);
            }
            $column = $this->loadColumnSchema($info);
            $table->columns[$column->name] = $column;
            if ($column->isPrimaryKey) {
                $table->primaryKey[] = $column->name;
                if ($column->autoIncrement) {
                    $table->sequenceName = '';
                }
            }
        }

        return true;
    }

    /**
     * Gets the CREATE TABLE sql string.
     * @param TableSchema $table the table metadata
     * @return string $sql the result of 'SHOW CREATE TABLE'
     */
    protected function getCreateTableSql($table)
    {
        $row = $this->db->createCommand('SHOW CREATE TABLE ' . $this->quoteTableName($table->fullName))->queryOne();
        if (isset($row['Create Table'])) {
            $sql = $row['Create Table'];
        } else {
            $row = array_values($row);
            $sql = $row[1];
        }

        return $sql;
    }

    /**
     * Collects the foreign key column details for the given table.
     * @param TableSchema $table the table metadata
     * @throws \Exception
     */
    protected function findConstraints($table)
    {
        $sql = <<<'SQL'
SELECT
    kcu.constraint_name,
    kcu.column_name,
    kcu.referenced_table_name,
    kcu.referenced_column_name
FROM information_schema.referential_constraints AS rc
JOIN information_schema.key_column_usage AS kcu ON
    (
        kcu.constraint_catalog = rc.constraint_catalog OR
        (kcu.constraint_catalog IS NULL AND rc.constraint_catalog IS NULL)
    ) AND
    kcu.constraint_schema = rc.constraint_schema AND
    kcu.constraint_name = rc.constraint_name
WHERE rc.constraint_schema = database() AND kcu.table_schema = database()
AND rc.table_name = :tableName AND kcu.table_name = :tableName1
SQL;

        try {
            $rows = $this->db->createCommand($sql, [':tableName' => $table->name, ':tableName1' => $table->name])->queryAll();
            $constraints = [];

            foreach ($rows as $row) {
                $constraints[$row['constraint_name']]['referenced_table_name'] = $row['referenced_table_name'];
                $constraints[$row['constraint_name']]['columns'][$row['column_name']] = $row['referenced_column_name'];
            }

            $table->foreignKeys = [];
            foreach ($constraints as $name => $constraint) {
                $table->foreignKeys[$name] = array_merge(
                    [$constraint['referenced_table_name']],
                    $constraint['columns']
                );
            }
        } catch (\Exception $e) {
            $previous = $e->getPrevious();
            if (!$previous instanceof \PDOException || strpos($previous->getMessage(), 'SQLSTATE[42S02') === false) {
                throw $e;
            }

            // table does not exist, try to determine the foreign keys using the table creation sql
            $sql = $this->getCreateTableSql($table);
            $regexp = '/FOREIGN KEY\s+\(([^\)]+)\)\s+REFERENCES\s+([^\(^\s]+)\s*\(([^\)]+)\)/mi';
            if (preg_match_all($regexp, $sql, $matches, PREG_SET_ORDER)) {
                foreach ($matches as $match) {
                    $fks = array_map('trim', explode(',', str_replace('`', '', $match[1])));
                    $pks = array_map('trim', explode(',', str_replace('`', '', $match[3])));
                    $constraint = [str_replace('`', '', $match[2])];
                    foreach ($fks as $k => $name) {
                        $constraint[$name] = $pks[$k];
                    }
                    $table->foreignKeys[md5(serialize($constraint))] = $constraint;
                }
                $table->foreignKeys = array_values($table->foreignKeys);
            }
        }
    }

    /**
     * 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.
     */
    public function findUniqueIndexes($table)
    {
        $sql = $this->getCreateTableSql($table);
        $uniqueIndexes = [];

        $regexp = '/UNIQUE KEY\s+([^\(\s]+)\s*\(([^\(\)]+)\)/mi';
        if (preg_match_all($regexp, $sql, $matches, PREG_SET_ORDER)) {
            foreach ($matches as $match) {
                $indexName = str_replace('`', '', $match[1]);
                $indexColumns = array_map('trim', explode(',', str_replace('`', '', $match[2])));
                $uniqueIndexes[$indexName] = $indexColumns;
            }
        }

        return $uniqueIndexes;
    }

    /**
     * @inheritdoc
     */
    public function createColumnSchemaBuilder($type, $length = null)
    {
        return new ColumnSchemaBuilder($type, $length, $this->db);
    }

    /**
     * @return bool whether the version of the MySQL being used is older than 5.1.
     * @throws InvalidConfigException
     * @throws Exception
     * @since 2.0.13
     */
    protected function isOldMysql()
    {
        if ($this->_oldMysql === null) {
            $version = $this->db->getSlavePdo()->getAttribute(\PDO::ATTR_SERVER_VERSION);
            $this->_oldMysql = version_compare($version, '5.1', '<=');
        }
        return $this->_oldMysql;
    }

    /**
     * Loads multiple types of constraints and returns the specified ones.
     * @param string $tableName table name.
     * @param string $returnType return type:
     * - primaryKey
     * - foreignKeys
     * - uniques
     * @return mixed constraints.
     */
    private function loadTableConstraints($tableName, $returnType)
    {
        static $sql = <<<'SQL'
SELECT DISTINCT
    `kcu`.`CONSTRAINT_NAME` AS `name`,
    `kcu`.`COLUMN_NAME` AS `column_name`,
    `tc`.`CONSTRAINT_TYPE` AS `type`,
    CASE
        WHEN :schemaName IS NULL AND `kcu`.`REFERENCED_TABLE_SCHEMA` = `sch`.`name` THEN NULL
        ELSE `kcu`.`REFERENCED_TABLE_SCHEMA`
    END AS `foreign_table_schema`,
    `kcu`.`REFERENCED_TABLE_NAME` AS `foreign_table_name`,
    `kcu`.`REFERENCED_COLUMN_NAME` AS `foreign_column_name`,
    `rc`.`UPDATE_RULE` AS `on_update`,
    `rc`.`DELETE_RULE` AS `on_delete`
FROM (SELECT DATABASE() AS `name`) AS `sch`
INNER JOIN `information_schema`.`KEY_COLUMN_USAGE` AS `kcu`
    ON `kcu`.`TABLE_SCHEMA` = COALESCE(:schemaName, `sch`.`name`) AND `kcu`.`CONSTRAINT_SCHEMA` = `kcu`.`TABLE_SCHEMA` AND `kcu`.`TABLE_NAME` = :tableName
LEFT JOIN `information_schema`.`REFERENTIAL_CONSTRAINTS` AS `rc`
    ON `rc`.`CONSTRAINT_SCHEMA` = `kcu`.`TABLE_SCHEMA` AND `rc`.`CONSTRAINT_NAME` = `kcu`.`CONSTRAINT_NAME`
LEFT JOIN `information_schema`.`TABLE_CONSTRAINTS` AS `tc`
    ON `tc`.`TABLE_SCHEMA` = `kcu`.`TABLE_SCHEMA` AND `tc`.`CONSTRAINT_NAME` = `kcu`.`CONSTRAINT_NAME`
ORDER BY `kcu`.`ORDINAL_POSITION` ASC
SQL;

        $resolvedName = $this->resolveTableName($tableName);
        $constraints = $this->db->createCommand($sql, [
            ':schemaName' => $resolvedName->schemaName,
            ':tableName' => $resolvedName->name,
        ])->queryAll();
        $constraints = $this->normalizePdoRowKeyCase($constraints, true);
        $constraints = ArrayHelper::index($constraints, null, ['type', 'name']);
        $result = [
            'primaryKey' => null,
            'foreignKeys' => [],
            'uniques' => [],
        ];
        foreach ($constraints as $type => $names) {
            foreach ($names as $name => $constraint) {
                switch ($type) {
                    case 'PRIMARY KEY':
                        $result['primaryKey'] = new Constraint([
                            'columnNames' => ArrayHelper::getColumn($constraint, 'column_name'),
                        ]);
                        break;
                    case 'FOREIGN KEY':
                        $result['foreignKeys'][] = new ForeignKeyConstraint([
                            'name' => $name,
                            'columnNames' => ArrayHelper::getColumn($constraint, 'column_name'),
                            'foreignSchemaName' => $constraint[0]['foreign_table_schema'],
                            'foreignTableName' => $constraint[0]['foreign_table_name'],
                            'foreignColumnNames' => ArrayHelper::getColumn($constraint, 'foreign_column_name'),
                            'onDelete' => $constraint[0]['on_delete'],
                            'onUpdate' => $constraint[0]['on_update'],
                        ]);
                        break;
                    case 'UNIQUE':
                        $result['uniques'][] = new Constraint([
                            'name' => $name,
                            'columnNames' => ArrayHelper::getColumn($constraint, 'column_name'),
                        ]);
                        break;
                }
            }
        }
        foreach ($result as $type => $data) {
            $this->setTableMetadata($tableName, $type, $data);
        }
        return $result[$returnType];
    }
}


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\mysql;
9
10		use yii\base\InvalidConfigException;
11		use yii\base\NotSupportedException;
12		use yii\db\ColumnSchema;
13		use yii\db\Constraint;
14		use yii\db\ConstraintFinderTrait;
15		use yii\db\Exception;
16		use yii\db\Expression;
17		use yii\db\ForeignKeyConstraint;
18		use yii\db\IndexConstraint;
19		use yii\db\TableSchema;
20		use yii\helpers\ArrayHelper;
21
22		/**
23		* Schema is the class for retrieving metadata from a MySQL database (version 4.1.x and 5.x).
24		*
25		* @author Qiang Xue <[email protected]>
26		* @since 2.0
27		*/
28		class Schema extends \yii\db\Schema
29		{
30		use ConstraintFinderTrait;
31
32		/**
33		* @var bool whether MySQL used is older than 5.1.
34		*/
35		private $_oldMysql;
36
37
38		/**
39		* @var array mapping from physical column types (keys) to abstract column types (values)
40		*/
41		public $typeMap = [
42		'tinyint' => self::TYPE_SMALLINT,
43		'bit' => self::TYPE_INTEGER,
44		'smallint' => self::TYPE_SMALLINT,
45		'mediumint' => self::TYPE_INTEGER,
46		'int' => self::TYPE_INTEGER,
47		'integer' => self::TYPE_INTEGER,
48		'bigint' => self::TYPE_BIGINT,
49		'float' => self::TYPE_FLOAT,
50		'double' => self::TYPE_DOUBLE,
51		'real' => self::TYPE_FLOAT,
52		'decimal' => self::TYPE_DECIMAL,
53		'numeric' => self::TYPE_DECIMAL,
54		'tinytext' => self::TYPE_TEXT,
55		'mediumtext' => self::TYPE_TEXT,
56		'longtext' => self::TYPE_TEXT,
57		'longblob' => self::TYPE_BINARY,
58		'blob' => self::TYPE_BINARY,
59		'text' => self::TYPE_TEXT,
60		'varchar' => self::TYPE_STRING,
61		'string' => self::TYPE_STRING,
62		'char' => self::TYPE_CHAR,
63		'datetime' => self::TYPE_DATETIME,
64		'year' => self::TYPE_DATE,
65		'date' => self::TYPE_DATE,
66		'time' => self::TYPE_TIME,
67		'timestamp' => self::TYPE_TIMESTAMP,
68		'enum' => self::TYPE_STRING,
69		'varbinary' => self::TYPE_BINARY,
70		];
71
72		/**
73		* @inheritDoc
74		*/
75	40	protected function resolveTableName($name)
76		{
77	40	$resolvedName = new TableSchema();
78	40	$parts = explode('.', str_replace('`', '', $name));
79	40	if (isset($parts[1])) {
80		$resolvedName->schemaName = $parts[0];
81		$resolvedName->name = $parts[1];
82		} else {
83	40	$resolvedName->schemaName = $this->defaultSchema;
84	40	$resolvedName->name = $name;
85		}
86	40	$resolvedName->fullName = ($resolvedName->schemaName !== $this->defaultSchema ? $resolvedName->schemaName . '.' : '') . $resolvedName->name;
87	40	return $resolvedName;
88		}
89
90		/**
91		* @inheritDoc
92		*/
93	6	protected function findTableNames($schema = '')
94		{
95	6	$sql = 'SHOW TABLES';
96	6	if ($schema !== '') {
97		$sql .= ' FROM ' . $this->quoteSimpleTableName($schema);
98		}
99
100	6	return $this->db->createCommand($sql)->queryColumn();
101		}
102
103		/**
104		* @inheritDoc
105		*/
106	232	protected function loadTableSchema($name)
107		{
108	232	$table = new TableSchema();
109	232	$this->resolveTableNames($table, $name);
110
111	232	if ($this->findColumns($table)) {
112	227	$this->findConstraints($table);
113	227	return $table;
114		}
115
116	13	return null;
117		}
118
119		/**
120		* @inheritDoc
121		*/
122	13	protected function loadTablePrimaryKey($tableName)
123		{
124	13	return $this->loadTableConstraints($tableName, 'primaryKey');
125		}
126
127		/**
128		* @inheritDoc
129		*/
130	4	protected function loadTableForeignKeys($tableName)
131		{
132	4	return $this->loadTableConstraints($tableName, 'foreignKeys');
133		}
134
135		/**
136		* @inheritDoc
137		*/
138	10	protected function loadTableIndexes($tableName)
139		{
140	10	static $sql = <<<'SQL'
141		SELECT
142		`s`.`INDEX_NAME` AS `name`,
143		`s`.`COLUMN_NAME` AS `column_name`,
144		`s`.`NON_UNIQUE` ^ 1 AS `index_is_unique`,
145		`s`.`INDEX_NAME` = 'PRIMARY' AS `index_is_primary`
146		FROM `information_schema`.`STATISTICS` AS `s`
147		WHERE `s`.`TABLE_SCHEMA` = COALESCE(:schemaName, DATABASE()) AND `s`.`INDEX_SCHEMA` = `s`.`TABLE_SCHEMA` AND `s`.`TABLE_NAME` = :tableName
148		ORDER BY `s`.`SEQ_IN_INDEX` ASC
149		SQL;
150
151	10	$resolvedName = $this->resolveTableName($tableName);
152	10	$indexes = $this->db->createCommand($sql, [
153	10	':schemaName' => $resolvedName->schemaName,
154	10	':tableName' => $resolvedName->name,
155	10	])->queryAll();
156	10	$indexes = $this->normalizePdoRowKeyCase($indexes, true);
157	10	$indexes = ArrayHelper::index($indexes, null, 'name');
158	10	$result = [];
159	10	foreach ($indexes as $name => $index) {
160	10	$result[] = new IndexConstraint([
161	10	'isPrimary' => (bool) $index[0]['index_is_primary'],
162	10	'isUnique' => (bool) $index[0]['index_is_unique'],
163	10	'name' => $name !== 'PRIMARY' ? $name : null,
164	10	'columnNames' => ArrayHelper::getColumn($index, 'column_name'),
165		]);
166		}
167	10	return $result;
168		}
169
170		/**
171		* @inheritDoc
172		*/
173	13	protected function loadTableUniques($tableName)
174		{
175	13	return $this->loadTableConstraints($tableName, 'uniques');
176		}
177
178		/**
179		* @inheritDoc
180		* @throws NotSupportedException if this method is called.
181		*/
182	12	protected function loadTableChecks($tableName)
		0 ignored issues – show Unused Code introduced 2017-05-03 15:22 UTC by Report Bug Copy Issue Report The parameter `$tableName` is not used and could be removed. This check looks from parameters that have been defined for a function or method, but which are not used in the method body. Loading history...
183		{
184	12	throw new NotSupportedException('MySQL does not support check constraints.');
185		}
186
187		/**
188		* @inheritDoc
189		* @throws NotSupportedException if this method is called.
190		*/
191	12	protected function loadTableDefaultValues($tableName)
		0 ignored issues – show Unused Code introduced 2017-05-03 15:22 UTC by Report Bug Copy Issue Report The parameter `$tableName` is not used and could be removed. This check looks from parameters that have been defined for a function or method, but which are not used in the method body. Loading history...
192		{
193	12	throw new NotSupportedException('MySQL does not support default value constraints.');
194		}
195
196		/**
197		* Quotes a table name for use in a query.
198		* A simple table name has no schema prefix.
199		* @param string $name table name
200		* @return string the properly quoted table name
201		*/
202	325	public function quoteSimpleTableName($name)
203		{
204	325	return strpos($name, '`') !== false ? $name : "`$name`";
205		}
206
207		/**
208		* Quotes a column name for use in a query.
209		* A simple column name has no prefix.
210		* @param string $name column name
211		* @return string the properly quoted column name
212		*/
213	324	public function quoteSimpleColumnName($name)
214		{
215	324	return strpos($name, '`') !== false \|\| $name === '*' ? $name : "`$name`";
216		}
217
218		/**
219		* Creates a query builder for the MySQL database.
220		* @return QueryBuilder query builder instance
221		*/
222	245	public function createQueryBuilder()
223		{
224	245	return new QueryBuilder($this->db);
225		}
226
227		/**
228		* Resolves the table name and schema name (if any).
229		* @param TableSchema $table the table metadata object
230		* @param string $name the table name
231		*/
232	232	protected function resolveTableNames($table, $name)
233		{
234	232	$parts = explode('.', str_replace('`', '', $name));
235	232	if (isset($parts[1])) {
236		$table->schemaName = $parts[0];
237		$table->name = $parts[1];
238		$table->fullName = $table->schemaName . '.' . $table->name;
239		} else {
240	232	$table->fullName = $table->name = $parts[0];
241		}
242	232	}
243
244		/**
245		* Loads the column information into a [[ColumnSchema]] object.
246		* @param array $info column information
247		* @return ColumnSchema the column schema object
248		*/
249	227	protected function loadColumnSchema($info)
250		{
251	227	$column = $this->createColumnSchema();
252
253	227	$column->name = $info['field'];
254	227	$column->allowNull = $info['null'] === 'YES';
255	227	$column->isPrimaryKey = strpos($info['key'], 'PRI') !== false;
256	227	$column->autoIncrement = stripos($info['extra'], 'auto_increment') !== false;
257	227	$column->comment = $info['comment'];
258
259	227	$column->dbType = $info['type'];
260	227	$column->unsigned = stripos($column->dbType, 'unsigned') !== false;
261
262	227	$column->type = self::TYPE_STRING;
263	227	if (preg_match('/^(\w+)(?:\(([^\)]+)\))?/', $column->dbType, $matches)) {
264	227	$type = strtolower($matches[1]);
265	227	if (isset($this->typeMap[$type])) {
266	227	$column->type = $this->typeMap[$type];
267		}
268	227	if (!empty($matches[2])) {
269	227	if ($type === 'enum') {
270	14	preg_match_all("/'[^']*'/", $matches[2], $values);
271	14	foreach ($values[0] as $i => $value) {
272	14	$values[$i] = trim($value, "'");
273		}
274	14	$column->enumValues = $values;
		0 ignored issues – show Documentation Bug introduced 2016-11-27 07:33 UTC by Report Bug Copy Issue Report It seems like `$values` can be `null`. However, the property `$enumValues` is declared as `array`. Maybe change the type of the property to `array\|null` or add a type check? Our type inference engine has found an assignment of a scalar value (like a string, an integer or null) to a property which is an array. Either this assignment is in error or the assigned type should be added to the documentation/type hint for that property. To type hint that a parameter can be either an array or null, you can set a type hint of array and a default value of null. The PHP interpreter will then accept both an array or null for that parameter. function aContainsB(array $needle = null, array $haystack) { if (!$needle) { return false; } return array_intersect($haystack, $needle) == $haystack; } The function can be called with either null or an array for the parameter `$needle` but will only accept an array as `$haystack`. Loading history...
275		} else {
276	227	$values = explode(',', $matches[2]);
277	227	$column->size = $column->precision = (int) $values[0];
278	227	if (isset($values[1])) {
279	69	$column->scale = (int) $values[1];
280		}
281	227	if ($column->size === 1 && $type === 'bit') {
282	5	$column->type = 'boolean';
283	227	} elseif ($type === 'bit') {
284	14	if ($column->size > 32) {
285		$column->type = 'bigint';
286	14	} elseif ($column->size === 32) {
287		$column->type = 'integer';
288		}
289		}
290		}
291		}
292		}
293
294	227	$column->phpType = $this->getColumnPhpType($column);
295
296	227	if (!$column->isPrimaryKey) {
297	223	if ($column->type === 'timestamp' && $info['default'] === 'CURRENT_TIMESTAMP') {
298	15	$column->defaultValue = new Expression('CURRENT_TIMESTAMP');
299	223	} elseif (isset($type) && $type === 'bit') {
300	15	$column->defaultValue = bindec(trim($info['default'], 'b\''));
301		} else {
302	222	$column->defaultValue = $column->phpTypecast($info['default']);
303		}
304		}
305
306	227	return $column;
307		}
308
309		/**
310		* Collects the metadata of table columns.
311		* @param TableSchema $table the table metadata
312		* @return bool whether the table exists in the database
313		* @throws \Exception if DB query fails
314		*/
315	232	protected function findColumns($table)
316		{
317	232	$sql = 'SHOW FULL COLUMNS FROM ' . $this->quoteTableName($table->fullName);
318		try {
319	232	$columns = $this->db->createCommand($sql)->queryAll();
320	13	} catch (\Exception $e) {
321	13	$previous = $e->getPrevious();
322	13	if ($previous instanceof \PDOException && strpos($previous->getMessage(), 'SQLSTATE[42S02') !== false) {
323		// table does not exist
324		// https://dev.mysql.com/doc/refman/5.5/en/error-messages-server.html#error_er_bad_table_error
325	13	return false;
326		}
327		throw $e;
328		}
329	227	foreach ($columns as $info) {
330	227	if ($this->db->slavePdo->getAttribute(\PDO::ATTR_CASE) !== \PDO::CASE_LOWER) {
331	226	$info = array_change_key_case($info, CASE_LOWER);
332		}
333	227	$column = $this->loadColumnSchema($info);
334	227	$table->columns[$column->name] = $column;
335	227	if ($column->isPrimaryKey) {
336	213	$table->primaryKey[] = $column->name;
337	213	if ($column->autoIncrement) {
338	227	$table->sequenceName = '';
339		}
340		}
341		}
342
343	227	return true;
344		}
345
346		/**
347		* Gets the CREATE TABLE sql string.
348		* @param TableSchema $table the table metadata
349		* @return string $sql the result of 'SHOW CREATE TABLE'
350		*/
351	1	protected function getCreateTableSql($table)
352		{
353	1	$row = $this->db->createCommand('SHOW CREATE TABLE ' . $this->quoteTableName($table->fullName))->queryOne();
354	1	if (isset($row['Create Table'])) {
355	1	$sql = $row['Create Table'];
356		} else {
357		$row = array_values($row);
358		$sql = $row[1];
359		}
360
361	1	return $sql;
362		}
363
364		/**
365		* Collects the foreign key column details for the given table.
366		* @param TableSchema $table the table metadata
367		* @throws \Exception
368		*/
369	227	protected function findConstraints($table)
370		{
371		$sql = <<<'SQL'
372	227	SELECT
373		kcu.constraint_name,
374		kcu.column_name,
375		kcu.referenced_table_name,
376		kcu.referenced_column_name
377		FROM information_schema.referential_constraints AS rc
378		JOIN information_schema.key_column_usage AS kcu ON
379		(
380		kcu.constraint_catalog = rc.constraint_catalog OR
381		(kcu.constraint_catalog IS NULL AND rc.constraint_catalog IS NULL)
382		) AND
383		kcu.constraint_schema = rc.constraint_schema AND
384		kcu.constraint_name = rc.constraint_name
385		WHERE rc.constraint_schema = database() AND kcu.table_schema = database()
386		AND rc.table_name = :tableName AND kcu.table_name = :tableName1
387		SQL;
388
389		try {
390	227	$rows = $this->db->createCommand($sql, [':tableName' => $table->name, ':tableName1' => $table->name])->queryAll();
391	227	$constraints = [];
392
393	227	foreach ($rows as $row) {
394	157	$constraints[$row['constraint_name']]['referenced_table_name'] = $row['referenced_table_name'];
395	157	$constraints[$row['constraint_name']]['columns'][$row['column_name']] = $row['referenced_column_name'];
396		}
397
398	227	$table->foreignKeys = [];
399	227	foreach ($constraints as $name => $constraint) {
400	157	$table->foreignKeys[$name] = array_merge(
401	157	[$constraint['referenced_table_name']],
402	227	$constraint['columns']
403		);
404		}
405		} catch (\Exception $e) {
406		$previous = $e->getPrevious();
407		if (!$previous instanceof \PDOException \|\| strpos($previous->getMessage(), 'SQLSTATE[42S02') === false) {
408		throw $e;
409		}
410
411		// table does not exist, try to determine the foreign keys using the table creation sql
412		$sql = $this->getCreateTableSql($table);
413		$regexp = '/FOREIGN KEY\s+\(([^\)]+)\)\s+REFERENCES\s+([^\(^\s]+)\s*\(([^\)]+)\)/mi';
414		if (preg_match_all($regexp, $sql, $matches, PREG_SET_ORDER)) {
415		foreach ($matches as $match) {
416		$fks = array_map('trim', explode(',', str_replace('`', '', $match[1])));
417		$pks = array_map('trim', explode(',', str_replace('`', '', $match[3])));
418		$constraint = [str_replace('`', '', $match[2])];
419		foreach ($fks as $k => $name) {
420		$constraint[$name] = $pks[$k];
421		}
422		$table->foreignKeys[md5(serialize($constraint))] = $constraint;
423		}
424		$table->foreignKeys = array_values($table->foreignKeys);
425		}
426		}
427	227	}
428
429		/**
430		* Returns all unique indexes for the given table.
431		* Each array element is of the following structure:
432		*
433		* ```php
434		* [
435		* 'IndexName1' => ['col1' [, ...]],
436		* 'IndexName2' => ['col2' [, ...]],
437		* ]
438		* ```
439		*
440		* @param TableSchema $table the table metadata
441		* @return array all unique indexes for the given table.
442		*/
443	1	public function findUniqueIndexes($table)
444		{
445	1	$sql = $this->getCreateTableSql($table);
446	1	$uniqueIndexes = [];
447
448	1	$regexp = '/UNIQUE KEY\s+([^\(\s]+)\s*\(([^\(\)]+)\)/mi';
449	1	if (preg_match_all($regexp, $sql, $matches, PREG_SET_ORDER)) {
450	1	foreach ($matches as $match) {
451	1	$indexName = str_replace('`', '', $match[1]);
452	1	$indexColumns = array_map('trim', explode(',', str_replace('`', '', $match[2])));
453	1	$uniqueIndexes[$indexName] = $indexColumns;
454		}
455		}
456
457	1	return $uniqueIndexes;
458		}
459
460		/**
461		* @inheritdoc
462		*/
463	9	public function createColumnSchemaBuilder($type, $length = null)
464		{
465	9	return new ColumnSchemaBuilder($type, $length, $this->db);
466		}
467
468		/**
469		* @return bool whether the version of the MySQL being used is older than 5.1.
470		* @throws InvalidConfigException
471		* @throws Exception
472		* @since 2.0.13
473		*/
474		protected function isOldMysql()
475		{
476		if ($this->_oldMysql === null) {
477		$version = $this->db->getSlavePdo()->getAttribute(\PDO::ATTR_SERVER_VERSION);
478		$this->_oldMysql = version_compare($version, '5.1', '<=');
479		}
480		return $this->_oldMysql;
481		}
482
483		/**
484		* Loads multiple types of constraints and returns the specified ones.
485		* @param string $tableName table name.
486		* @param string $returnType return type:
487		* - primaryKey
488		* - foreignKeys
489		* - uniques
490		* @return mixed constraints.
491		*/
492	30	private function loadTableConstraints($tableName, $returnType)
493		{
494	30	static $sql = <<<'SQL'
495		SELECT DISTINCT
496		`kcu`.`CONSTRAINT_NAME` AS `name`,
497		`kcu`.`COLUMN_NAME` AS `column_name`,
498		`tc`.`CONSTRAINT_TYPE` AS `type`,
499		CASE
500		WHEN :schemaName IS NULL AND `kcu`.`REFERENCED_TABLE_SCHEMA` = `sch`.`name` THEN NULL
501		ELSE `kcu`.`REFERENCED_TABLE_SCHEMA`
502		END AS `foreign_table_schema`,
503		`kcu`.`REFERENCED_TABLE_NAME` AS `foreign_table_name`,
504		`kcu`.`REFERENCED_COLUMN_NAME` AS `foreign_column_name`,
505		`rc`.`UPDATE_RULE` AS `on_update`,
506		`rc`.`DELETE_RULE` AS `on_delete`
507		FROM (SELECT DATABASE() AS `name`) AS `sch`
508		INNER JOIN `information_schema`.`KEY_COLUMN_USAGE` AS `kcu`
509		ON `kcu`.`TABLE_SCHEMA` = COALESCE(:schemaName, `sch`.`name`) AND `kcu`.`CONSTRAINT_SCHEMA` = `kcu`.`TABLE_SCHEMA` AND `kcu`.`TABLE_NAME` = :tableName
510		LEFT JOIN `information_schema`.`REFERENTIAL_CONSTRAINTS` AS `rc`
511		ON `rc`.`CONSTRAINT_SCHEMA` = `kcu`.`TABLE_SCHEMA` AND `rc`.`CONSTRAINT_NAME` = `kcu`.`CONSTRAINT_NAME`
512		LEFT JOIN `information_schema`.`TABLE_CONSTRAINTS` AS `tc`
513		ON `tc`.`TABLE_SCHEMA` = `kcu`.`TABLE_SCHEMA` AND `tc`.`CONSTRAINT_NAME` = `kcu`.`CONSTRAINT_NAME`
514		ORDER BY `kcu`.`ORDINAL_POSITION` ASC
515		SQL;
516
517	30	$resolvedName = $this->resolveTableName($tableName);
518	30	$constraints = $this->db->createCommand($sql, [
519	30	':schemaName' => $resolvedName->schemaName,
520	30	':tableName' => $resolvedName->name,
521	30	])->queryAll();
522	30	$constraints = $this->normalizePdoRowKeyCase($constraints, true);
523	30	$constraints = ArrayHelper::index($constraints, null, ['type', 'name']);
524		$result = [
525	30	'primaryKey' => null,
526		'foreignKeys' => [],
527		'uniques' => [],
528		];
529	30	foreach ($constraints as $type => $names) {
530	30	foreach ($names as $name => $constraint) {
531		switch ($type) {
532	30	case 'PRIMARY KEY':
533	19	$result['primaryKey'] = new Constraint([
534	19	'columnNames' => ArrayHelper::getColumn($constraint, 'column_name'),
535		]);
536	19	break;
537	29	case 'FOREIGN KEY':
538	10	$result['foreignKeys'][] = new ForeignKeyConstraint([
539	10	'name' => $name,
540	10	'columnNames' => ArrayHelper::getColumn($constraint, 'column_name'),
541	10	'foreignSchemaName' => $constraint[0]['foreign_table_schema'],
542	10	'foreignTableName' => $constraint[0]['foreign_table_name'],
543	10	'foreignColumnNames' => ArrayHelper::getColumn($constraint, 'foreign_column_name'),
544	10	'onDelete' => $constraint[0]['on_delete'],
545	10	'onUpdate' => $constraint[0]['on_update'],
546		]);
547	10	break;
548	20	case 'UNIQUE':
549	20	$result['uniques'][] = new Constraint([
550	20	'name' => $name,
551	20	'columnNames' => ArrayHelper::getColumn($constraint, 'column_name'),
552		]);
553	30	break;
554		}
555		}
556		}
557	30	foreach ($result as $type => $data) {
558	30	$this->setTableMetadata($tableName, $type, $data);
559		}
560	30	return $result[$returnType];
561		}
562		}
563

yiisoft / yii2

Push — master ( d2781c...0559a9 )

Schema::loadTableUniques() A

Complexity

Size

Duplication

Code Coverage

Importance

Duplication Side-by-Side

Filter issues like