DMLQueryBuilder::prepareInsertValues() - Code Metrics - Inspection of "QueryBuilder refactoring (#118)" - yiisoft/db-mysql - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Push — dev ( 1e6c50...6dcd8f )

by Def

created 2022-05-03 20:49 UTC

DMLQueryBuilder::prepareInsertValues() A

↳ Parent: DMLQueryBuilder

Complexity

Conditions	6
Paths	4

Size

Total Lines	23
Code Lines	11

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	4
CRAP Score	16.6682

Importance

Changes

Metric	Value
eloc	11
c	0
b	0
f	0
dl	0
loc	23
ccs	4
cts	12
cp	0.3333
rs	9.2222
cc	6
nc	4
nop	3
crap	16.6682

<?php

declare(strict_types=1);

namespace Yiisoft\Db\Mysql;

use JsonException;
use Throwable;
use Yiisoft\Db\Exception\Exception;
use Yiisoft\Db\Exception\InvalidArgumentException;
use Yiisoft\Db\Exception\InvalidConfigException;
use Yiisoft\Db\Exception\NotSupportedException;
use Yiisoft\Db\Expression\Expression;
use Yiisoft\Db\Expression\ExpressionInterface;
use Yiisoft\Db\Query\DMLQueryBuilder as AbstractDMLQueryBuilder;
use Yiisoft\Db\Query\Query;
use Yiisoft\Db\Query\QueryBuilderInterface;
use Yiisoft\Db\Query\QueryInterface;

use function implode;
use function reset;

final class DMLQueryBuilder extends AbstractDMLQueryBuilder
{
    public function __construct(private QueryBuilderInterface $queryBuilder)
    {
        parent::__construct($queryBuilder);
    }

    /**
     * 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 array|int|string|null $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.
     *
     * @throws Exception|InvalidArgumentException|InvalidConfigException|Throwable
     *
     * @return string the SQL statement for resetting sequence.
     */
    public function resetSequence(string $tableName, array|int|string|null $value = null): string
    {
        $table = $this->schema->getTableSchema($tableName);

        if ($table === null) {
            throw new InvalidArgumentException("Table not found: $tableName");
        }

        $sequenceName = $table->getSequenceName();
        if ($sequenceName === null) {
            throw new InvalidArgumentException("There is no sequence associated with table '$tableName'.");
        }

        $tableName = $this->quoter->quoteTableName($tableName);

        if ($value !== null) {
            return 'ALTER TABLE ' . $tableName . ' AUTO_INCREMENT=' . (int)$value . ';';
        }

        $pk = $table->getPrimaryKey();
        $key = (string) reset($pk);

        return "SET @new_autoincrement_value := (SELECT MAX(`$key`) + 1 FROM $tableName);
SET @sql = CONCAT('ALTER TABLE $tableName AUTO_INCREMENT =', @new_autoincrement_value);
PREPARE autoincrement_stmt FROM @sql;
EXECUTE autoincrement_stmt";
    }

    /**
     * Creates an SQL statement to insert rows into a database table if they do not already exist (matching unique
     * constraints), or update them if they do.
     *
     * For example,
     *
     * ```php
     * $sql = $queryBuilder->upsert('pages', [
     *     'name' => 'Front page',
     *     'url' => 'http://example.com/', // url is unique
     *     'visits' => 0,
     * ], [
     *     'visits' => new Expression('visits + 1'),
     * ], $params);
     * ```
     *
     * The method will properly escape the table and column names.
     *
     * @param string $table the table that new rows will be inserted into/updated in.
     * @param array|QueryInterface $insertColumns the column data (name => value) to be inserted into the table or
     * instance of {@see Query} to perform `INSERT INTO ... SELECT` SQL statement.
     * @param array|bool $updateColumns the column data (name => value) to be updated if they already exist. If `true`
     * is passed, the column data will be updated to match the insert column data. If `false` is passed, no update will
     * be performed if the column data already exists.
     * @param array $params the binding parameters that will be generated by this method. They should be bound to the DB
     * command later.
     *
     * @throws Exception|InvalidConfigException|JsonException|NotSupportedException if this is not supported by the
     * underlying DBMS.
     *
     * @return string the resulting SQL.
     */
    public function upsert(
        string $table,
        QueryInterface|array $insertColumns,
        bool|array $updateColumns,
        array &$params
    ): string {
        $insertSql = $this->insert($table, $insertColumns, $params);

        /** @var array $uniqueNames */
        [$uniqueNames, , $updateNames] = $this->prepareUpsertColumns(
            $table,
            $insertColumns,
            $updateColumns,
        );

        if (empty($uniqueNames)) {
            return $insertSql;
        }

        if ($updateColumns === true) {

            $updateColumns = [];
            /** @var string $name */
            foreach ($updateNames as $name) {
                $updateColumns[$name] = new Expression(
                    'VALUES(' . $this->quoter->quoteColumnName($name) . ')'
                );
            }
        } elseif ($updateColumns === false) {

            $columnName = (string) reset($uniqueNames);
            $name = $this->quoter->quoteColumnName($columnName);
            $updateColumns = [$name => new Expression($this->quoter->quoteTableName($table) . '.' . $name)];
        }

        /**
         *  @psalm-var array<array-key, string> $updates
         *  @psalm-var array<string, ExpressionInterface|string> $updateColumns
         */
        [$updates, $params] = $this->prepareUpdateSets($table, $updateColumns, $params);

        return $insertSql . ' ON DUPLICATE KEY UPDATE ' . implode(', ', $updates);
    }

    /**
     * Prepares a `VALUES` part for an `INSERT` SQL statement.
     *
     * @param string $table the table that new rows will be inserted into.
     * @param array|QueryInterface $columns the column data (name => value) to be inserted into the table or instance of
     * {@see Query|Query} to perform INSERT INTO ... SELECT SQL statement.
     * @param array $params the binding parameters that will be generated by this method. They should be bound to the DB
     * command later.
     *
     * @throws Exception|InvalidArgumentException|InvalidConfigException|JsonException|NotSupportedException
     *
     * @return array array of column names, placeholders, values and params.
     */
    protected function prepareInsertValues(string $table, QueryInterface|array $columns, array $params = []): array
    {
        /**
         * @var array $names
         * @var array $placeholders
         */
        [$names, $placeholders, $values, $params] = parent::prepareInsertValues($table, $columns, $params);

        if (!$columns instanceof Query && empty($names)) {

            $tableSchema = $this->schema->getTableSchema($table);

            if ($tableSchema !== null) {
                $columns = $tableSchema->getColumns();
                $columns = !empty($tableSchema->getPrimaryKey())
                    ? $tableSchema->getPrimaryKey() : [reset($columns)->getName()];
                foreach ($columns as $name) {
                    $names[] = $this->quoter->quoteColumnName($name);
                    $placeholders[] = 'DEFAULT';
                }
            }
        }

        return [$names, $placeholders, $values, $params];
    }
}


1		<?php
2
3		declare(strict_types=1);
4
5		namespace Yiisoft\Db\Mysql;
6
7		use JsonException;
8		use Throwable;
9		use Yiisoft\Db\Exception\Exception;
10		use Yiisoft\Db\Exception\InvalidArgumentException;
11		use Yiisoft\Db\Exception\InvalidConfigException;
12		use Yiisoft\Db\Exception\NotSupportedException;
13		use Yiisoft\Db\Expression\Expression;
14		use Yiisoft\Db\Expression\ExpressionInterface;
15		use Yiisoft\Db\Query\DMLQueryBuilder as AbstractDMLQueryBuilder;
16		use Yiisoft\Db\Query\Query;
17		use Yiisoft\Db\Query\QueryBuilderInterface;
18		use Yiisoft\Db\Query\QueryInterface;
19
20		use function implode;
21		use function reset;
22
23		final class DMLQueryBuilder extends AbstractDMLQueryBuilder
24		{
25	355	public function __construct(private QueryBuilderInterface $queryBuilder)
26		{
27	355	parent::__construct($queryBuilder);
28		}
29
30		/**
31		* Creates a SQL statement for resetting the sequence value of a table's primary key.
32		*
33		* The sequence will be reset such that the primary key of the next new row inserted will have the specified value
34		* or 1.
35		*
36		* @param string $tableName the name of the table whose primary key sequence will be reset.
37		* @param array\|int\|string\|null $value the value for the primary key of the next new row inserted. If this is not
38		* set, the next new row's primary key will have a value 1.
39		*
40		* @throws Exception\|InvalidArgumentException\|InvalidConfigException\|Throwable
41		*
42		* @return string the SQL statement for resetting sequence.
43		*/
44	3	public function resetSequence(string $tableName, array\|int\|string\|null $value = null): string
45		{
46	3	$table = $this->schema->getTableSchema($tableName);
47
48	3	if ($table === null) {
49	1	throw new InvalidArgumentException("Table not found: $tableName");
50		}
51
52	2	$sequenceName = $table->getSequenceName();
53	2	if ($sequenceName === null) {
54	1	throw new InvalidArgumentException("There is no sequence associated with table '$tableName'.");
55		}
56
57	1	$tableName = $this->quoter->quoteTableName($tableName);
58
59	1	if ($value !== null) {
60	1	return 'ALTER TABLE ' . $tableName . ' AUTO_INCREMENT=' . (int)$value . ';';
61		}
62
63	1	$pk = $table->getPrimaryKey();
64	1	$key = (string) reset($pk);
65
66	1	return "SET @new_autoincrement_value := (SELECT MAX(`$key`) + 1 FROM $tableName);
67		SET @sql = CONCAT('ALTER TABLE $tableName AUTO_INCREMENT =', @new_autoincrement_value);
68		PREPARE autoincrement_stmt FROM @sql;
69		EXECUTE autoincrement_stmt";
70		}
71
72		/**
73		* Creates an SQL statement to insert rows into a database table if they do not already exist (matching unique
74		* constraints), or update them if they do.
75		*
76		* For example,
77		*
78		* ```php
79		* $sql = $queryBuilder->upsert('pages', [
80		* 'name' => 'Front page',
81		* 'url' => 'http://example.com/', // url is unique
82		* 'visits' => 0,
83		* ], [
84		* 'visits' => new Expression('visits + 1'),
85		* ], $params);
86		* ```
87		*
88		* The method will properly escape the table and column names.
89		*
90		* @param string $table the table that new rows will be inserted into/updated in.
91		* @param array\|QueryInterface $insertColumns the column data (name => value) to be inserted into the table or
92		* instance of {@see Query} to perform `INSERT INTO ... SELECT` SQL statement.
93		* @param array\|bool $updateColumns the column data (name => value) to be updated if they already exist. If `true`
94		* is passed, the column data will be updated to match the insert column data. If `false` is passed, no update will
95		* be performed if the column data already exists.
96		* @param array $params the binding parameters that will be generated by this method. They should be bound to the DB
97		* command later.
98		*
99		* @throws Exception\|InvalidConfigException\|JsonException\|NotSupportedException if this is not supported by the
100		* underlying DBMS.
101		*
102		* @return string the resulting SQL.
103		*/
104	18	public function upsert(
105		string $table,
106		QueryInterface\|array $insertColumns,
107		bool\|array $updateColumns,
108		array &$params
109		): string {
110	18	$insertSql = $this->insert($table, $insertColumns, $params);
111
112		/** @var array $uniqueNames */
113	18	[$uniqueNames, , $updateNames] = $this->prepareUpsertColumns(
114		$table,
115		$insertColumns,
116		$updateColumns,
117		);
118
119	18	if (empty($uniqueNames)) {
120	3	return $insertSql;
121		}
122
123	15	if ($updateColumns === true) {
		0 ignored issues – show introduced 2022-02-12 12:28 UTC by Report Bug Copy Issue Report The condition `$updateColumns === true` is always `false`. Loading history...
124	4	$updateColumns = [];
125		/** @var string $name */
126	4	foreach ($updateNames as $name) {
127	4	$updateColumns[$name] = new Expression(
128	4	'VALUES(' . $this->quoter->quoteColumnName($name) . ')'
129		);
130		}
131	11	} elseif ($updateColumns === false) {
		0 ignored issues – show introduced 2022-02-12 12:28 UTC by Report Bug Copy Issue Report The condition `$updateColumns === false` is always `false`. Loading history...
132	5	$columnName = (string) reset($uniqueNames);
133	5	$name = $this->quoter->quoteColumnName($columnName);
134	5	$updateColumns = [$name => new Expression($this->quoter->quoteTableName($table) . '.' . $name)];
135		}
136
137		/**
138		* @psalm-var array<array-key, string> $updates
139		* @psalm-var array<string, ExpressionInterface\|string> $updateColumns
140		*/
141	15	[$updates, $params] = $this->prepareUpdateSets($table, $updateColumns, $params);
142
143	15	return $insertSql . ' ON DUPLICATE KEY UPDATE ' . implode(', ', $updates);
144		}
145
146		/**
147		* Prepares a `VALUES` part for an `INSERT` SQL statement.
148		*
149		* @param string $table the table that new rows will be inserted into.
150		* @param array\|QueryInterface $columns the column data (name => value) to be inserted into the table or instance of
151		* {@see Query\|Query} to perform INSERT INTO ... SELECT SQL statement.
152		* @param array $params the binding parameters that will be generated by this method. They should be bound to the DB
153		* command later.
154		*
155		* @throws Exception\|InvalidArgumentException\|InvalidConfigException\|JsonException\|NotSupportedException
156		*
157		* @return array array of column names, placeholders, values and params.
158		*/
159	52	protected function prepareInsertValues(string $table, QueryInterface\|array $columns, array $params = []): array
160		{
161		/**
162		* @var array $names
163		* @var array $placeholders
164		*/
165	52	[$names, $placeholders, $values, $params] = parent::prepareInsertValues($table, $columns, $params);
166
167	49	if (!$columns instanceof Query && empty($names)) {
		0 ignored issues – show introduced 2022-02-12 12:28 UTC by Report Bug Copy Issue Report `$columns` is never a sub-type of `Yiisoft\Db\Query\Query`. Loading history...
168		$tableSchema = $this->schema->getTableSchema($table);
169
170		if ($tableSchema !== null) {
171		$columns = $tableSchema->getColumns();
172		$columns = !empty($tableSchema->getPrimaryKey())
173		? $tableSchema->getPrimaryKey() : [reset($columns)->getName()];
174		foreach ($columns as $name) {
175		$names[] = $this->quoter->quoteColumnName($name);
176		$placeholders[] = 'DEFAULT';
177		}
178		}
179		}
180
181	49	return [$names, $placeholders, $values, $params];
182		}
183		}
184

yiisoft / db-mysql

Push — dev ( 1e6c50...6dcd8f )

DMLQueryBuilder::prepareInsertValues() A

Complexity

Size

Duplication

Code Coverage

Importance

Duplication Side-by-Side

Filter issues like