QueryBuilder::buildLimit() - Code Metrics - Inspection of "Update branch 2.2." - yiisoft/yii2 - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Pull Request — 2.2 (#20357)

by Wilmer

created 2025-04-20 19:22 UTC

QueryBuilder::buildLimit() A

↳ Parent: QueryBuilder

Complexity

Conditions	4
Paths	4

Size

Total Lines	16
Code Lines	8

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	0
CRAP Score	20

Importance

Changes

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

<?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			public function init()
57			{
58			parent::init();
59
60			$this->typeMap = array_merge($this->typeMap, $this->defaultTimeTypeMap());
61			}
62
63			/**
64			* {@inheritdoc}
65			*/
66			protected function defaultExpressionBuilders()
67			{
68			return array_merge(parent::defaultExpressionBuilders(), [
69			'yii\db\JsonExpression' => 'yii\db\mysql\JsonExpressionBuilder',
70			]);
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			public function createIndex($name, $table, $columns, $unique = false)
115			{
116			return 'ALTER TABLE '
117			. $this->db->quoteTableName($table)
118			. ($unique ? ' ADD UNIQUE INDEX ' : ' ADD INDEX ')
119			. $this->db->quoteTableName($name)
120			. ' (' . $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			public function dropForeignKey($name, $table)
130			{
131			return 'ALTER TABLE ' . $this->db->quoteTableName($table)
132			. ' 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			public function dropPrimaryKey($name, $table)
142			{
143			return 'ALTER TABLE ' . $this->db->quoteTableName($table) . ' DROP PRIMARY KEY';
144			}
145
146			/**
147			* {@inheritdoc}
148			*/
149			public function dropUnique($name, $table)
150			{
151			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			public function resetSequence($tableName, $value = null)
183			{
184			$table = $this->db->getTableSchema($tableName);
185			if ($table !== null && $table->sequenceName !== null) {
186			$tableName = $this->db->quoteTableName($tableName);
187			if ($value === null) {
188			$key = reset($table->primaryKey);
189			$value = $this->db->createCommand("SELECT MAX(`$key`) FROM $tableName")->queryScalar() + 1;
190			} else {
191			$value = (int) $value;
192			}
193
194			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			public function checkIntegrity($check = true, $schema = '', $table = '')
210			{
211			return 'SET FOREIGN_KEY_CHECKS = ' . ($check ? 1 : 0);
212			}
213
214			/**
215			* {@inheritdoc}
216			*/
217			public function buildLimit($limit, $offset)
218			{
219			$sql = '';
220			if ($this->hasLimit($limit)) {
221			$sql = 'LIMIT ' . $limit;
222			if ($this->hasOffset($offset)) {
223			$sql .= ' OFFSET ' . $offset;
224			}
225			} 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			$sql = "LIMIT $offset, 18446744073709551615"; // 2^64-1
230			}
231
232			return $sql;
233			}
234
235			/**
236			* {@inheritdoc}
237			*/
238			protected function hasLimit($limit)
239			{
240			// In MySQL limit argument must be nonnegative integer constant
241			return ctype_digit((string) $limit);
242			}
243
244			/**
245			* {@inheritdoc}
246			*/
247			protected function hasOffset($offset)
248			{
249			// In MySQL offset argument must be nonnegative integer constant
250			$offset = (string) $offset;
251			return ctype_digit($offset) && $offset !== '0';
252			}
253
254			/**
255			* {@inheritdoc}
256			*/
257			protected function prepareInsertValues($table, $columns, $params = [])
258			{
259			list($names, $placeholders, $values, $params) = parent::prepareInsertValues($table, $columns, $params);
260			if (!$columns instanceof Query && empty($names)) {
261			$tableSchema = $this->db->getSchema()->getTableSchema($table);
262			if ($tableSchema !== null) {
263			if (!empty($tableSchema->primaryKey)) {
264			$columns = $tableSchema->primaryKey;
265			$defaultValue = 'NULL';
266			} else {
267			$columns = [reset($tableSchema->columns)->name];
268			$defaultValue = 'DEFAULT';
269			}
270
271			foreach ($columns as $name) {
272			$names[] = $this->db->quoteColumnName($name);
273			$placeholders[] = $defaultValue;
274			}
275			}
276			}
277			return [$names, $placeholders, $values, $params];
278			}
279
280			/**
281			* {@inheritdoc}
282			* @see https://downloads.mysql.com/docs/refman-5.1-en.pdf
283			*/
284			public function upsert($table, $insertColumns, $updateColumns, &$params)
285			{
286			$insertSql = $this->insert($table, $insertColumns, $params);
287			list($uniqueNames, , $updateNames) = $this->prepareUpsertColumns($table, $insertColumns, $updateColumns);
288			if (empty($uniqueNames)) {
289			return $insertSql;
290			}
291			if ($updateNames === []) {
292			// there are no columns to update
293			$updateColumns = false;
294			}
295
296			if ($updateColumns === true) {
297			$updateColumns = [];
298			foreach ($updateNames as $name) {
299			$updateColumns[$name] = new Expression('VALUES(' . $this->db->quoteColumnName($name) . ')');
300			}
301			} elseif ($updateColumns === false) {
302			$name = $this->db->quoteColumnName(reset($uniqueNames));
303			$updateColumns = [$name => new Expression($this->db->quoteTableName($table) . '.' . $name)];
304			}
305			list($updates, $params) = $this->prepareUpdateSets($table, $updateColumns, $params);
306			return $insertSql . ' ON DUPLICATE KEY UPDATE ' . implode(', ', $updates);
307			}
308
309			/**
310			* {@inheritdoc}
311			* @since 2.0.8
312			*/
313			public function addCommentOnColumn($table, $column, $comment)
314			{
315			// Strip existing comment which may include escaped quotes
316			$definition = trim(preg_replace("/COMMENT '(?:''\|[^'])*'/i", '', $this->getColumnDefinition($table, $column)));
317
318			$checkRegex = '/CHECK (\(([^()]\|(?-2))\))/';
319			$check = preg_match($checkRegex, $definition, $checkMatches);
320			if ($check === 1) {
321			$definition = preg_replace($checkRegex, '', $definition);
322			}
323			$alterSql = 'ALTER TABLE ' . $this->db->quoteTableName($table)
324			. ' CHANGE ' . $this->db->quoteColumnName($column)
325			. ' ' . $this->db->quoteColumnName($column)
326			. (empty($definition) ? '' : ' ' . $definition)
327			. ' COMMENT ' . $this->db->quoteValue($comment);
328			if ($check === 1) {
329			$alterSql .= ' ' . $checkMatches[0];
330			}
331			return $alterSql;
332			}
333
334			/**
335			* {@inheritdoc}
336			* @since 2.0.8
337			*/
338			public function addCommentOnTable($table, $comment)
339			{
340			return 'ALTER TABLE ' . $this->db->quoteTableName($table) . ' COMMENT ' . $this->db->quoteValue($comment);
341			}
342
343			/**
344			* {@inheritdoc}
345			* @since 2.0.8
346			*/
347			public function dropCommentFromColumn($table, $column)
348			{
349			return $this->addCommentOnColumn($table, $column, '');
350			}
351
352			/**
353			* {@inheritdoc}
354			* @since 2.0.8
355			*/
356			public function dropCommentFromTable($table)
357			{
358			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			private function getColumnDefinition($table, $column)
371			{
372			$quotedTable = $this->db->quoteTableName($table);
373			$row = $this->db->createCommand('SHOW CREATE TABLE ' . $quotedTable)->queryOne();
374			if ($row === false) {
375			throw new Exception("Unable to find column '$column' in table '$table'.");
376			}
377			if (isset($row['Create Table'])) {
378			$sql = $row['Create Table'];
379			} else {
380			$row = array_values($row);
381			$sql = $row[1];
382			}
383			if (preg_match_all('/^\s[`"](.?)[`"]\s+(.*?),?$/m', $sql, $matches)) {
384			foreach ($matches[1] as $i => $c) {
385			if ($c === $column) {
386			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			private function supportsFractionalSeconds()
401			{
402			// use cache to prevent opening MySQL connection
403			// https://github.com/yiisoft/yii2/issues/13749#issuecomment-481657224
404			$key = [__METHOD__, $this->db->dsn];
405			$cache = null;
406			$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			if ($this->db->enableSchemaCache && $schemaCache instanceof CacheInterface && !($schemaCache instanceof DbCache)) {
409			$cache = $schemaCache;
410			}
411			$version = $cache ? $cache->get($key) : null;
412			if (!$version) {
413			$version = $this->db->getSlavePdo(true)->getAttribute(\PDO::ATTR_SERVER_VERSION);
414			if ($cache) {
415			$cache->set($key, $version, $this->db->schemaCacheDuration);
416			}
417			}
418
419			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			private function defaultTimeTypeMap()
430			{
431			$map = [
432			Schema::TYPE_DATETIME => 'datetime',
433			Schema::TYPE_TIMESTAMP => 'timestamp',
434			Schema::TYPE_TIME => 'time',
435			];
436
437			if ($this->supportsFractionalSeconds()) {
438			$map = [
439			Schema::TYPE_DATETIME => 'datetime(0)',
440			Schema::TYPE_TIMESTAMP => 'timestamp(0)',
441			Schema::TYPE_TIME => 'time(0)',
442			];
443			}
444
445			return $map;
446			}
447			}
448

yiisoft / yii2

Pull Request — 2.2 (#20357)

QueryBuilder::buildLimit() A

Complexity

Size

Duplication

Code Coverage

Importance

Duplication Side-by-Side

Filter issues like