QueryBuilder - Code Metrics - yiisoft/yii2 - Measure and Improve Code Quality continuously with Scrutinizer

QueryBuilder F
last analyzed 2024-04-13 22:48 UTC

↳ Parent: Project

Complexity

Total Complexity

Size/Duplication

Total Lines	421
Duplicated Lines	0 %

Test Coverage

Coverage

80%

Importance

Changes

Metric	Value
eloc	159
dl	0
loc	421
ccs	128
cts	160
cp	0.8
rs	3.2
c	0
b	0
f	0
wmc	65

23 Methods

Rating	Name	Size	Complexity
B	supportsFractionalSeconds()	20	9
A	dropCommentFromColumn()	3	1
A	getColumnDefinition()	22	6
A	addCommentOnTable()	3	1
A	dropCommentFromTable()	3	1
A	defaultTimeTypeMap()	17	2
A	init()	5	1
A	hasLimit()	4	1
A	resetSequence()	18	5
A	defaultExpressionBuilders()	4	1
A	upsert()	23	6
A	renameColumn()	27	6
A	addCheck()	3	1
A	dropCheck()	3	1
A	dropPrimaryKey()	3	1
A	dropForeignKey()	4	1
A	dropUnique()	3	1
A	addCommentOnColumn()	19	4
A	hasOffset()	5	2
A	prepareInsertValues()	21	6
A	checkIntegrity()	3	2
A	buildLimit()	16	4
A	createIndex()	7	2

How to fix Complexity

<?php

/**
 * @link https://www.yiiframework.com/
 * @copyright Copyright (c) 2008 Yii Software LLC
 * @license https://www.yiiframework.com/license/
 */

namespace yii\db\mysql;

use yii\base\InvalidArgumentException;
use yii\base\NotSupportedException;
use yii\caching\CacheInterface;
use yii\caching\DbCache;
use yii\db\Exception;
use yii\db\Expression;
use yii\db\Query;

/**
 * QueryBuilder is the query builder for MySQL databases.
 *
 * @author Qiang Xue <[email protected]>
 * @since 2.0
 */
class QueryBuilder extends \yii\db\QueryBuilder
{
    /**
     * @var array mapping from abstract column types (keys) to physical column types (values).
     */
    public $typeMap = [
        Schema::TYPE_PK => 'int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY',
        Schema::TYPE_UPK => 'int(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY',
        Schema::TYPE_BIGPK => 'bigint(20) NOT NULL AUTO_INCREMENT PRIMARY KEY',
        Schema::TYPE_UBIGPK => 'bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY',
        Schema::TYPE_CHAR => 'char(1)',
        Schema::TYPE_STRING => 'varchar(255)',
        Schema::TYPE_TEXT => 'text',
        Schema::TYPE_TINYINT => 'tinyint(3)',
        Schema::TYPE_SMALLINT => 'smallint(6)',
        Schema::TYPE_INTEGER => 'int(11)',
        Schema::TYPE_BIGINT => 'bigint(20)',
        Schema::TYPE_FLOAT => 'float',
        Schema::TYPE_DOUBLE => 'double',
        Schema::TYPE_DECIMAL => 'decimal(10,0)',
        Schema::TYPE_DATE => 'date',
        Schema::TYPE_BINARY => 'blob',
        Schema::TYPE_BOOLEAN => 'tinyint(1)',
        Schema::TYPE_MONEY => 'decimal(19,4)',
        Schema::TYPE_JSON => 'json'
    ];


    /**
     * {@inheritdoc}
     */
    public function init()
    {
        parent::init();

        $this->typeMap = array_merge($this->typeMap, $this->defaultTimeTypeMap());
    }

    /**
     * {@inheritdoc}
     */
    protected function defaultExpressionBuilders()
    {
        return array_merge(parent::defaultExpressionBuilders(), [
            'yii\db\JsonExpression' => 'yii\db\mysql\JsonExpressionBuilder',
        ]);
    }

    /**
     * Builds a SQL statement for renaming a column.
     * @param string $table the table whose column is to be renamed. The name will be properly quoted by the method.
     * @param string $oldName the old name of the column. The name will be properly quoted by the method.
     * @param string $newName the new name of the column. The name will be properly quoted by the method.
     * @return string the SQL statement for renaming a DB column.
     * @throws Exception
     */
    public function renameColumn($table, $oldName, $newName)
    {
        $quotedTable = $this->db->quoteTableName($table);
        $row = $this->db->createCommand('SHOW CREATE TABLE ' . $quotedTable)->queryOne();
        if ($row === false) {
            throw new Exception("Unable to find column '$oldName' in table '$table'.");
        }
        if (isset($row['Create Table'])) {
            $sql = $row['Create Table'];
        } else {
            $row = array_values($row);
            $sql = $row[1];
        }
        if (preg_match_all('/^\s*[`"](.*?)[`"]\s+(.*?),?$/m', $sql, $matches)) {
            foreach ($matches[1] as $i => $c) {
                if ($c === $oldName) {
                    return "ALTER TABLE $quotedTable CHANGE "
                        . $this->db->quoteColumnName($oldName) . ' '
                        . $this->db->quoteColumnName($newName) . ' '
                        . $matches[2][$i];
                }
            }
        }
        // try to give back a SQL anyway
        return "ALTER TABLE $quotedTable CHANGE "
            . $this->db->quoteColumnName($oldName) . ' '
            . $this->db->quoteColumnName($newName);
    }

    /**
     * {@inheritdoc}
     * @see https://bugs.mysql.com/bug.php?id=48875
     */
    public function createIndex($name, $table, $columns, $unique = false)
    {
        return 'ALTER TABLE '
        . $this->db->quoteTableName($table)
        . ($unique ? ' ADD UNIQUE INDEX ' : ' ADD INDEX ')
        . $this->db->quoteTableName($name)
        . ' (' . $this->buildColumns($columns) . ')';
    }

    /**
     * Builds a SQL statement for dropping a foreign key constraint.
     * @param string $name the name of the foreign key constraint to be dropped. The name will be properly quoted by the method.
     * @param string $table the table whose foreign is to be dropped. The name will be properly quoted by the method.
     * @return string the SQL statement for dropping a foreign key constraint.
     */
    public function dropForeignKey($name, $table)
    {
        return 'ALTER TABLE ' . $this->db->quoteTableName($table)
            . ' DROP FOREIGN KEY ' . $this->db->quoteColumnName($name);
    }

    /**
     * Builds a SQL statement for removing a primary key constraint to an existing table.
     * @param string $name the name of the primary key constraint to be removed.
     * @param string $table the table that the primary key constraint will be removed from.
     * @return string the SQL statement for removing a primary key constraint from an existing table.
     */
    public function dropPrimaryKey($name, $table)
    {
        return 'ALTER TABLE ' . $this->db->quoteTableName($table) . ' DROP PRIMARY KEY';
    }

    /**
     * {@inheritdoc}
     */
    public function dropUnique($name, $table)
    {
        return $this->dropIndex($name, $table);
    }

    /**
     * {@inheritdoc}
     * @throws NotSupportedException this is not supported by MySQL.
     */
    public function addCheck($name, $table, $expression)
    {
        throw new NotSupportedException(__METHOD__ . ' is not supported by MySQL.');
    }

    /**
     * {@inheritdoc}
     * @throws NotSupportedException this is not supported by MySQL.
     */
    public function dropCheck($name, $table)
    {
        throw new NotSupportedException(__METHOD__ . ' is not supported by MySQL.');
    }

    /**
     * Creates a SQL statement for resetting the sequence value of a table's primary key.
     * The sequence will be reset such that the primary key of the next new row inserted
     * will have the specified value or 1.
     * @param string $tableName the name of the table whose primary key sequence will be reset
     * @param mixed $value the value for the primary key of the next new row inserted. If this is not set,
     * the next new row's primary key will have a value 1.
     * @return string the SQL statement for resetting sequence
     * @throws InvalidArgumentException if the table does not exist or there is no sequence associated with the table.
     */
    public function resetSequence($tableName, $value = null)
    {
        $table = $this->db->getTableSchema($tableName);
        if ($table !== null && $table->sequenceName !== null) {
            $tableName = $this->db->quoteTableName($tableName);
            if ($value === null) {
                $key = reset($table->primaryKey);
                $value = $this->db->createCommand("SELECT MAX(`$key`) FROM $tableName")->queryScalar() + 1;
            } else {
                $value = (int) $value;
            }

            return "ALTER TABLE $tableName AUTO_INCREMENT=$value";
        } elseif ($table === null) {
            throw new InvalidArgumentException("Table not found: $tableName");
        }

        throw new InvalidArgumentException("There is no sequence associated with table '$tableName'.");
    }

    /**
     * Builds a SQL statement for enabling or disabling integrity check.
     * @param bool $check whether to turn on or off the integrity check.
     * @param string $schema the schema of the tables. Meaningless for MySQL.
     * @param string $table the table name. Meaningless for MySQL.
     * @return string the SQL statement for checking integrity
     */
    public function checkIntegrity($check = true, $schema = '', $table = '')
    {
        return 'SET FOREIGN_KEY_CHECKS = ' . ($check ? 1 : 0);
    }

    /**
     * {@inheritdoc}
     */
    public function buildLimit($limit, $offset)
    {
        $sql = '';
        if ($this->hasLimit($limit)) {
            $sql = 'LIMIT ' . $limit;
            if ($this->hasOffset($offset)) {
                $sql .= ' OFFSET ' . $offset;
            }
        } elseif ($this->hasOffset($offset)) {
            // limit is not optional in MySQL
            // https://stackoverflow.com/questions/255517/mysql-offset-infinite-rows/271650#271650
            // https://dev.mysql.com/doc/refman/5.7/en/select.html#idm46193796386608
            $sql = "LIMIT $offset, 18446744073709551615"; // 2^64-1
        }

        return $sql;
    }

    /**
     * {@inheritdoc}
     */
    protected function hasLimit($limit)
    {
        // In MySQL limit argument must be nonnegative integer constant
        return ctype_digit((string) $limit);
    }

    /**
     * {@inheritdoc}
     */
    protected function hasOffset($offset)
    {
        // In MySQL offset argument must be nonnegative integer constant
        $offset = (string) $offset;
        return ctype_digit($offset) && $offset !== '0';
    }

    /**
     * {@inheritdoc}
     */
    protected function prepareInsertValues($table, $columns, $params = [])
    {
        list($names, $placeholders, $values, $params) = parent::prepareInsertValues($table, $columns, $params);
        if (!$columns instanceof Query && empty($names)) {
            $tableSchema = $this->db->getSchema()->getTableSchema($table);
            if ($tableSchema !== null) {
                if (!empty($tableSchema->primaryKey)) {
                    $columns = $tableSchema->primaryKey;
                    $defaultValue = 'NULL';
                } else {
                    $columns = [reset($tableSchema->columns)->name];
                    $defaultValue = 'DEFAULT';
                }

                foreach ($columns as $name) {
                    $names[] = $this->db->quoteColumnName($name);
                    $placeholders[] = $defaultValue;
                }
            }
        }
        return [$names, $placeholders, $values, $params];
    }

    /**
     * {@inheritdoc}
     * @see https://downloads.mysql.com/docs/refman-5.1-en.pdf
     */
    public function upsert($table, $insertColumns, $updateColumns, &$params)
    {
        $insertSql = $this->insert($table, $insertColumns, $params);
        list($uniqueNames, , $updateNames) = $this->prepareUpsertColumns($table, $insertColumns, $updateColumns);
        if (empty($uniqueNames)) {
            return $insertSql;
        }
        if ($updateNames === []) {
            // there are no columns to update
            $updateColumns = false;
        }

        if ($updateColumns === true) {
            $updateColumns = [];
            foreach ($updateNames as $name) {
                $updateColumns[$name] = new Expression('VALUES(' . $this->db->quoteColumnName($name) . ')');
            }
        } elseif ($updateColumns === false) {
            $name = $this->db->quoteColumnName(reset($uniqueNames));
            $updateColumns = [$name => new Expression($this->db->quoteTableName($table) . '.' . $name)];
        }
        list($updates, $params) = $this->prepareUpdateSets($table, $updateColumns, $params);
        return $insertSql . ' ON DUPLICATE KEY UPDATE ' . implode(', ', $updates);
    }

    /**
     * {@inheritdoc}
     * @since 2.0.8
     */
    public function addCommentOnColumn($table, $column, $comment)
    {
        // Strip existing comment which may include escaped quotes
        $definition = trim(preg_replace("/COMMENT '(?:''|[^'])*'/i", '', $this->getColumnDefinition($table, $column)));

        $checkRegex = '/CHECK *(\(([^()]|(?-2))*\))/';
        $check = preg_match($checkRegex, $definition, $checkMatches);
        if ($check === 1) {
            $definition = preg_replace($checkRegex, '', $definition);
        }
        $alterSql = 'ALTER TABLE ' . $this->db->quoteTableName($table)
            . ' CHANGE ' . $this->db->quoteColumnName($column)
            . ' ' . $this->db->quoteColumnName($column)
            . (empty($definition) ? '' : ' ' . $definition)
            . ' COMMENT ' . $this->db->quoteValue($comment);
        if ($check === 1) {
            $alterSql .= ' ' . $checkMatches[0];
        }
        return $alterSql;
    }

    /**
     * {@inheritdoc}
     * @since 2.0.8
     */
    public function addCommentOnTable($table, $comment)
    {
        return 'ALTER TABLE ' . $this->db->quoteTableName($table) . ' COMMENT ' . $this->db->quoteValue($comment);
    }

    /**
     * {@inheritdoc}
     * @since 2.0.8
     */
    public function dropCommentFromColumn($table, $column)
    {
        return $this->addCommentOnColumn($table, $column, '');
    }

    /**
     * {@inheritdoc}
     * @since 2.0.8
     */
    public function dropCommentFromTable($table)
    {
        return $this->addCommentOnTable($table, '');
    }


    /**
     * Gets column definition.
     *
     * @param string $table table name
     * @param string $column column name
     * @return string|null the column definition
     * @throws Exception in case when table does not contain column
     */
    private function getColumnDefinition($table, $column)
    {
        $quotedTable = $this->db->quoteTableName($table);
        $row = $this->db->createCommand('SHOW CREATE TABLE ' . $quotedTable)->queryOne();
        if ($row === false) {
            throw new Exception("Unable to find column '$column' in table '$table'.");
        }
        if (isset($row['Create Table'])) {
            $sql = $row['Create Table'];
        } else {
            $row = array_values($row);
            $sql = $row[1];
        }
        if (preg_match_all('/^\s*[`"](.*?)[`"]\s+(.*?),?$/m', $sql, $matches)) {
            foreach ($matches[1] as $i => $c) {
                if ($c === $column) {
                    return $matches[2][$i];
                }
            }
        }

        return null;
    }

    /**
     * Checks the ability to use fractional seconds.
     *
     * @return bool
     * @see https://dev.mysql.com/doc/refman/5.6/en/fractional-seconds.html
     */
    private function supportsFractionalSeconds()
    {
        // use cache to prevent opening MySQL connection
        // https://github.com/yiisoft/yii2/issues/13749#issuecomment-481657224
        $key = [__METHOD__, $this->db->dsn];
        $cache = null;
        $schemaCache = (\Yii::$app && is_string($this->db->schemaCache)) ? \Yii::$app->get($this->db->schemaCache, false) : $this->db->schemaCache;
        // If the `$schemaCache` is an instance of `DbCache` we don't use it to avoid a loop
        if ($this->db->enableSchemaCache && $schemaCache instanceof CacheInterface && !($schemaCache instanceof DbCache)) {
            $cache = $schemaCache;
        }
        $version = $cache ? $cache->get($key) : null;
        if (!$version) {
            $version = $this->db->getSlavePdo(true)->getAttribute(\PDO::ATTR_SERVER_VERSION);
            if ($cache) {
                $cache->set($key, $version, $this->db->schemaCacheDuration);
            }
        }

        return version_compare($version, '5.6.4', '>=');

    }

    /**
     * Returns the map for default time type.
     * If the version of MySQL is lower than 5.6.4, then the types will be without fractional seconds,
     * otherwise with fractional seconds.
     *
     * @return array
     */
    private function defaultTimeTypeMap()
    {
        $map = [
            Schema::TYPE_DATETIME => 'datetime',
            Schema::TYPE_TIMESTAMP => 'timestamp',
            Schema::TYPE_TIME => 'time',
        ];

        if ($this->supportsFractionalSeconds()) {
            $map = [
                Schema::TYPE_DATETIME => 'datetime(0)',
                Schema::TYPE_TIMESTAMP => 'timestamp(0)',
                Schema::TYPE_TIME => 'time(0)',
            ];
        }

        return $map;
    }
}


1		<?php
2
3		/**
4		* @link https://www.yiiframework.com/
5		* @copyright Copyright (c) 2008 Yii Software LLC
6		* @license https://www.yiiframework.com/license/
7		*/
8
9		namespace yii\db\mysql;
10
11		use yii\base\InvalidArgumentException;
12		use yii\base\NotSupportedException;
13		use yii\caching\CacheInterface;
14		use yii\caching\DbCache;
15		use yii\db\Exception;
16		use yii\db\Expression;
17		use yii\db\Query;
18
19		/**
20		* QueryBuilder is the query builder for MySQL databases.
21		*
22		* @author Qiang Xue <[email protected]>
23		* @since 2.0
24		*/
25		class QueryBuilder extends \yii\db\QueryBuilder
26		{
27		/**
28		* @var array mapping from abstract column types (keys) to physical column types (values).
29		*/
30		public $typeMap = [
31		Schema::TYPE_PK => 'int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY',
32		Schema::TYPE_UPK => 'int(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY',
33		Schema::TYPE_BIGPK => 'bigint(20) NOT NULL AUTO_INCREMENT PRIMARY KEY',
34		Schema::TYPE_UBIGPK => 'bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY',
35		Schema::TYPE_CHAR => 'char(1)',
36		Schema::TYPE_STRING => 'varchar(255)',
37		Schema::TYPE_TEXT => 'text',
38		Schema::TYPE_TINYINT => 'tinyint(3)',
39		Schema::TYPE_SMALLINT => 'smallint(6)',
40		Schema::TYPE_INTEGER => 'int(11)',
41		Schema::TYPE_BIGINT => 'bigint(20)',
42		Schema::TYPE_FLOAT => 'float',
43		Schema::TYPE_DOUBLE => 'double',
44		Schema::TYPE_DECIMAL => 'decimal(10,0)',
45		Schema::TYPE_DATE => 'date',
46		Schema::TYPE_BINARY => 'blob',
47		Schema::TYPE_BOOLEAN => 'tinyint(1)',
48		Schema::TYPE_MONEY => 'decimal(19,4)',
49		Schema::TYPE_JSON => 'json'
50		];
51
52
53		/**
54		* {@inheritdoc}
55		*/
56	602	public function init()
57		{
58	602	parent::init();
59
60	602	$this->typeMap = array_merge($this->typeMap, $this->defaultTimeTypeMap());
61		}
62
63		/**
64		* {@inheritdoc}
65		*/
66	602	protected function defaultExpressionBuilders()
67		{
68	602	return array_merge(parent::defaultExpressionBuilders(), [
69	602	'yii\db\JsonExpression' => 'yii\db\mysql\JsonExpressionBuilder',
70	602	]);
71		}
72
73		/**
74		* Builds a SQL statement for renaming a column.
75		* @param string $table the table whose column is to be renamed. The name will be properly quoted by the method.
76		* @param string $oldName the old name of the column. The name will be properly quoted by the method.
77		* @param string $newName the new name of the column. The name will be properly quoted by the method.
78		* @return string the SQL statement for renaming a DB column.
79		* @throws Exception
80		*/
81		public function renameColumn($table, $oldName, $newName)
82		{
83		$quotedTable = $this->db->quoteTableName($table);
84		$row = $this->db->createCommand('SHOW CREATE TABLE ' . $quotedTable)->queryOne();
85		if ($row === false) {
86		throw new Exception("Unable to find column '$oldName' in table '$table'.");
87		}
88		if (isset($row['Create Table'])) {
89		$sql = $row['Create Table'];
90		} else {
91		$row = array_values($row);
92		$sql = $row[1];
93		}
94		if (preg_match_all('/^\s[`"](.?)[`"]\s+(.*?),?$/m', $sql, $matches)) {
95		foreach ($matches[1] as $i => $c) {
96		if ($c === $oldName) {
97		return "ALTER TABLE $quotedTable CHANGE "
98		. $this->db->quoteColumnName($oldName) . ' '
99		. $this->db->quoteColumnName($newName) . ' '
100		. $matches[2][$i];
101		}
102		}
103		}
104		// try to give back a SQL anyway
105		return "ALTER TABLE $quotedTable CHANGE "
106		. $this->db->quoteColumnName($oldName) . ' '
107		. $this->db->quoteColumnName($newName);
108		}
109
110		/**
111		* {@inheritdoc}
112		* @see https://bugs.mysql.com/bug.php?id=48875
113		*/
114	12	public function createIndex($name, $table, $columns, $unique = false)
115		{
116	12	return 'ALTER TABLE '
117	12	. $this->db->quoteTableName($table)
118	12	. ($unique ? ' ADD UNIQUE INDEX ' : ' ADD INDEX ')
119	12	. $this->db->quoteTableName($name)
120	12	. ' (' . $this->buildColumns($columns) . ')';
121		}
122
123		/**
124		* Builds a SQL statement for dropping a foreign key constraint.
125		* @param string $name the name of the foreign key constraint to be dropped. The name will be properly quoted by the method.
126		* @param string $table the table whose foreign is to be dropped. The name will be properly quoted by the method.
127		* @return string the SQL statement for dropping a foreign key constraint.
128		*/
129	4	public function dropForeignKey($name, $table)
130		{
131	4	return 'ALTER TABLE ' . $this->db->quoteTableName($table)
132	4	. ' DROP FOREIGN KEY ' . $this->db->quoteColumnName($name);
133		}
134
135		/**
136		* Builds a SQL statement for removing a primary key constraint to an existing table.
137		* @param string $name the name of the primary key constraint to be removed.
138		* @param string $table the table that the primary key constraint will be removed from.
139		* @return string the SQL statement for removing a primary key constraint from an existing table.
140		*/
141	2	public function dropPrimaryKey($name, $table)
142		{
143	2	return 'ALTER TABLE ' . $this->db->quoteTableName($table) . ' DROP PRIMARY KEY';
144		}
145
146		/**
147		* {@inheritdoc}
148		*/
149	2	public function dropUnique($name, $table)
150		{
151	2	return $this->dropIndex($name, $table);
152		}
153
154		/**
155		* {@inheritdoc}
156		* @throws NotSupportedException this is not supported by MySQL.
157		*/
158		public function addCheck($name, $table, $expression)
159		{
160		throw new NotSupportedException(__METHOD__ . ' is not supported by MySQL.');
161		}
162
163		/**
164		* {@inheritdoc}
165		* @throws NotSupportedException this is not supported by MySQL.
166		*/
167		public function dropCheck($name, $table)
168		{
169		throw new NotSupportedException(__METHOD__ . ' is not supported by MySQL.');
170		}
171
172		/**
173		* Creates a SQL statement for resetting the sequence value of a table's primary key.
174		* The sequence will be reset such that the primary key of the next new row inserted
175		* will have the specified value or 1.
176		* @param string $tableName the name of the table whose primary key sequence will be reset
177		* @param mixed $value the value for the primary key of the next new row inserted. If this is not set,
178		* the next new row's primary key will have a value 1.
179		* @return string the SQL statement for resetting sequence
180		* @throws InvalidArgumentException if the table does not exist or there is no sequence associated with the table.
181		*/
182	16	public function resetSequence($tableName, $value = null)
183		{
184	16	$table = $this->db->getTableSchema($tableName);
185	16	if ($table !== null && $table->sequenceName !== null) {
186	16	$tableName = $this->db->quoteTableName($tableName);
187	16	if ($value === null) {
188	1	$key = reset($table->primaryKey);
189	1	$value = $this->db->createCommand("SELECT MAX(`$key`) FROM $tableName")->queryScalar() + 1;
190		} else {
191	16	$value = (int) $value;
192		}
193
194	16	return "ALTER TABLE $tableName AUTO_INCREMENT=$value";
195		} elseif ($table === null) {
196		throw new InvalidArgumentException("Table not found: $tableName");
197		}
198
199		throw new InvalidArgumentException("There is no sequence associated with table '$tableName'.");
200		}
201
202		/**
203		* Builds a SQL statement for enabling or disabling integrity check.
204		* @param bool $check whether to turn on or off the integrity check.
205		* @param string $schema the schema of the tables. Meaningless for MySQL.
206		* @param string $table the table name. Meaningless for MySQL.
207		* @return string the SQL statement for checking integrity
208		*/
209	5	public function checkIntegrity($check = true, $schema = '', $table = '')
210		{
211	5	return 'SET FOREIGN_KEY_CHECKS = ' . ($check ? 1 : 0);
212		}
213
214		/**
215		* {@inheritdoc}
216		*/
217	483	public function buildLimit($limit, $offset)
218		{
219	483	$sql = '';
220	483	if ($this->hasLimit($limit)) {
221	39	$sql = 'LIMIT ' . $limit;
222	39	if ($this->hasOffset($offset)) {
223	39	$sql .= ' OFFSET ' . $offset;
224		}
225	469	} elseif ($this->hasOffset($offset)) {
226		// limit is not optional in MySQL
227		// https://stackoverflow.com/questions/255517/mysql-offset-infinite-rows/271650#271650
228		// https://dev.mysql.com/doc/refman/5.7/en/select.html#idm46193796386608
229	2	$sql = "LIMIT $offset, 18446744073709551615"; // 2^64-1
230		}
231
232	483	return $sql;
233		}
234
235		/**
236		* {@inheritdoc}
237		*/
238	483	protected function hasLimit($limit)
239		{
240		// In MySQL limit argument must be nonnegative integer constant
241	483	return ctype_digit((string) $limit);
242		}
243
244		/**
245		* {@inheritdoc}
246		*/
247	483	protected function hasOffset($offset)
248		{
249		// In MySQL offset argument must be nonnegative integer constant
250	483	$offset = (string) $offset;
251	483	return ctype_digit($offset) && $offset !== '0';
252		}
253
254		/**
255		* {@inheritdoc}
256		*/
257	220	protected function prepareInsertValues($table, $columns, $params = [])
258		{
259	220	list($names, $placeholders, $values, $params) = parent::prepareInsertValues($table, $columns, $params);
260	217	if (!$columns instanceof Query && empty($names)) {
261	2	$tableSchema = $this->db->getSchema()->getTableSchema($table);
262	2	if ($tableSchema !== null) {
263	2	if (!empty($tableSchema->primaryKey)) {
264	2	$columns = $tableSchema->primaryKey;
265	2	$defaultValue = 'NULL';
266		} else {
267	1	$columns = [reset($tableSchema->columns)->name];
268	1	$defaultValue = 'DEFAULT';
269		}
270
271	2	foreach ($columns as $name) {
272	2	$names[] = $this->db->quoteColumnName($name);
273	2	$placeholders[] = $defaultValue;
274		}
275		}
276		}
277	217	return [$names, $placeholders, $values, $params];
278		}
279
280		/**
281		* {@inheritdoc}
282		* @see https://downloads.mysql.com/docs/refman-5.1-en.pdf
283		*/
284	41	public function upsert($table, $insertColumns, $updateColumns, &$params)
285		{
286	41	$insertSql = $this->insert($table, $insertColumns, $params);
287	41	list($uniqueNames, , $updateNames) = $this->prepareUpsertColumns($table, $insertColumns, $updateColumns);
288	41	if (empty($uniqueNames)) {
289	3	return $insertSql;
290		}
291	38	if ($updateNames === []) {
292		// there are no columns to update
293	1	$updateColumns = false;
294		}
295
296	38	if ($updateColumns === true) {
297	27	$updateColumns = [];
298	27	foreach ($updateNames as $name) {
299	27	$updateColumns[$name] = new Expression('VALUES(' . $this->db->quoteColumnName($name) . ')');
300		}
301	11	} elseif ($updateColumns === false) {
302	5	$name = $this->db->quoteColumnName(reset($uniqueNames));
303	5	$updateColumns = [$name => new Expression($this->db->quoteTableName($table) . '.' . $name)];
304		}
305	38	list($updates, $params) = $this->prepareUpdateSets($table, $updateColumns, $params);
306	38	return $insertSql . ' ON DUPLICATE KEY UPDATE ' . implode(', ', $updates);
307		}
308
309		/**
310		* {@inheritdoc}
311		* @since 2.0.8
312		*/
313	2	public function addCommentOnColumn($table, $column, $comment)
314		{
315		// Strip existing comment which may include escaped quotes
316	2	$definition = trim(preg_replace("/COMMENT '(?:''\|[^'])*'/i", '', $this->getColumnDefinition($table, $column)));
317
318	2	$checkRegex = '/CHECK (\(([^()]\|(?-2))\))/';
319	2	$check = preg_match($checkRegex, $definition, $checkMatches);
320	2	if ($check === 1) {
321		$definition = preg_replace($checkRegex, '', $definition);
322		}
323	2	$alterSql = 'ALTER TABLE ' . $this->db->quoteTableName($table)
324	2	. ' CHANGE ' . $this->db->quoteColumnName($column)
325	2	. ' ' . $this->db->quoteColumnName($column)
326	2	. (empty($definition) ? '' : ' ' . $definition)
327	2	. ' COMMENT ' . $this->db->quoteValue($comment);
328	2	if ($check === 1) {
329		$alterSql .= ' ' . $checkMatches[0];
330		}
331	2	return $alterSql;
332		}
333
334		/**
335		* {@inheritdoc}
336		* @since 2.0.8
337		*/
338	1	public function addCommentOnTable($table, $comment)
339		{
340	1	return 'ALTER TABLE ' . $this->db->quoteTableName($table) . ' COMMENT ' . $this->db->quoteValue($comment);
341		}
342
343		/**
344		* {@inheritdoc}
345		* @since 2.0.8
346		*/
347	2	public function dropCommentFromColumn($table, $column)
348		{
349	2	return $this->addCommentOnColumn($table, $column, '');
350		}
351
352		/**
353		* {@inheritdoc}
354		* @since 2.0.8
355		*/
356	1	public function dropCommentFromTable($table)
357		{
358	1	return $this->addCommentOnTable($table, '');
359		}
360
361
362		/**
363		* Gets column definition.
364		*
365		* @param string $table table name
366		* @param string $column column name
367		* @return string\|null the column definition
368		* @throws Exception in case when table does not contain column
369		*/
370	2	private function getColumnDefinition($table, $column)
371		{
372	2	$quotedTable = $this->db->quoteTableName($table);
373	2	$row = $this->db->createCommand('SHOW CREATE TABLE ' . $quotedTable)->queryOne();
374	2	if ($row === false) {
375		throw new Exception("Unable to find column '$column' in table '$table'.");
376		}
377	2	if (isset($row['Create Table'])) {
378	2	$sql = $row['Create Table'];
379		} else {
380		$row = array_values($row);
381		$sql = $row[1];
382		}
383	2	if (preg_match_all('/^\s[`"](.?)[`"]\s+(.*?),?$/m', $sql, $matches)) {
384	2	foreach ($matches[1] as $i => $c) {
385	2	if ($c === $column) {
386	2	return $matches[2][$i];
387		}
388		}
389		}
390
391		return null;
392		}
393
394		/**
395		* Checks the ability to use fractional seconds.
396		*
397		* @return bool
398		* @see https://dev.mysql.com/doc/refman/5.6/en/fractional-seconds.html
399		*/
400	602	private function supportsFractionalSeconds()
401		{
402		// use cache to prevent opening MySQL connection
403		// https://github.com/yiisoft/yii2/issues/13749#issuecomment-481657224
404	602	$key = [__METHOD__, $this->db->dsn];
405	602	$cache = null;
406	602	$schemaCache = (\Yii::$app && is_string($this->db->schemaCache)) ? \Yii::$app->get($this->db->schemaCache, false) : $this->db->schemaCache;
407		// If the `$schemaCache` is an instance of `DbCache` we don't use it to avoid a loop
408	602	if ($this->db->enableSchemaCache && $schemaCache instanceof CacheInterface && !($schemaCache instanceof DbCache)) {
409	2	$cache = $schemaCache;
410		}
411	602	$version = $cache ? $cache->get($key) : null;
412	602	if (!$version) {
413	602	$version = $this->db->getSlavePdo(true)->getAttribute(\PDO::ATTR_SERVER_VERSION);
414	602	if ($cache) {
415	2	$cache->set($key, $version, $this->db->schemaCacheDuration);
416		}
417		}
418
419	602	return version_compare($version, '5.6.4', '>=');
		0 ignored issues – show Bug Best Practice introduced 2021-01-11 08:29 UTC by Report Bug Copy Issue Report The expression `return version_compare($version, '5.6.4', '>=')` also could return the type `integer` which is incompatible with the documented return type `boolean`. Loading history...
420		}
421
422		/**
423		* Returns the map for default time type.
424		* If the version of MySQL is lower than 5.6.4, then the types will be without fractional seconds,
425		* otherwise with fractional seconds.
426		*
427		* @return array
428		*/
429	602	private function defaultTimeTypeMap()
430		{
431	602	$map = [
432	602	Schema::TYPE_DATETIME => 'datetime',
433	602	Schema::TYPE_TIMESTAMP => 'timestamp',
434	602	Schema::TYPE_TIME => 'time',
435	602	];
436
437	602	if ($this->supportsFractionalSeconds()) {
438	602	$map = [
439	602	Schema::TYPE_DATETIME => 'datetime(0)',
440	602	Schema::TYPE_TIMESTAMP => 'timestamp(0)',
441	602	Schema::TYPE_TIME => 'time(0)',
442	602	];
443		}
444
445	602	return $map;
446		}
447		}
448

yiisoft / yii2

QueryBuilder F last analyzed 2024-04-13 22:48 UTC

Complexity

Size/Duplication

Test Coverage

Importance

23 Methods

How to fix Complexity

Complex Class

Duplication Side-by-Side

Filter issues like

QueryBuilder F
last analyzed 2024-04-13 22:48 UTC