QueryBuilder::upsert() - Code Metrics - rob006/yii2-dev - Measure and Improve Code Quality continuously with Scrutinizer

QueryBuilder::upsert() A
last analyzed 2024-06-12 18:24 UTC

↳ Parent: QueryBuilder

Complexity

Conditions	6
Paths	7

Size

Total Lines	23
Code Lines	15

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	16
CRAP Score	6

Importance

Changes	1
Bugs	0	Features	0

Metric	Value
cc	6
eloc	15
nc	7
nop	4
dl	0
loc	23
ccs	16
cts	16
cp	1
crap	6
rs	9.2222
c	1
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);
    }

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

rob006 / yii2-dev

GitHub Access Token became invalid

QueryBuilder::upsert() A last analyzed 2024-06-12 18:24 UTC

Complexity

Size

Duplication

Code Coverage

Importance

Duplication Side-by-Side

Filter issues like

QueryBuilder::upsert() A
last analyzed 2024-06-12 18:24 UTC