DMLQueryBuilder::upsert() - Code Metrics - Inspection of "Upsert fix" - yiisoft/db-pgsql - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Pull Request — master (#192)

by Def

created 2022-12-22 17:28 UTC

DMLQueryBuilder::upsert() B

↳ Parent: DMLQueryBuilder

Complexity

Conditions	7
Paths	14

Size

Total Lines	55
Code Lines	22

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	24
CRAP Score	7.0031

Importance

Changes	2
Bugs	1	Features	0

Metric	Value
cc	7
eloc	22
c	2
b	1
f	0
nc	14
nop	4
dl	0
loc	55
ccs	24
cts	25
cp	0.96
crap	7.0031
rs	8.6346

How to fix Long Method

<?php

declare(strict_types=1);

namespace Yiisoft\Db\Pgsql;

use JsonException;
use PDO;
use Yiisoft\Db\Command\Param;
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\QueryBuilder\DMLQueryBuilder as AbstractDMLQueryBuilder;
use Yiisoft\Db\QueryBuilder\QueryBuilderInterface;
use Yiisoft\Db\Query\Query;
use Yiisoft\Db\Query\QueryInterface;
use Yiisoft\Db\Schema\QuoterInterface;
use Yiisoft\Db\Schema\Schema;
.
|-- OtherDir
|   |-- Bar.php
|   `-- Foo.php
`-- SomeDir
    `-- Foo.php
use Yiisoft\Db\Schema\SchemaInterface;

use function implode;
use function is_array;
use function is_string;
use function reset;

final class DMLQueryBuilder extends AbstractDMLQueryBuilder
{
    public function __construct(
        private QueryBuilderInterface $queryBuilder,
        private QuoterInterface $quoter,
        private SchemaInterface $schema
    ) {
        parent::__construct($queryBuilder, $quoter, $schema);
    }

    public function insertEx(string $table, QueryInterface|array $columns, array &$params = []): string
    {
        $sql = $this->insert($table, $columns, $params);

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

        $returnColumns = [];
        if ($tableSchema !== null) {
            $returnColumns = $tableSchema->getPrimaryKey();
        }

        if (!empty($returnColumns)) {
            $returning = [];
            foreach ($returnColumns as $name) {
                $returning[] = $this->quoter->quoteColumnName($name);
            }
            $sql .= ' RETURNING ' . implode(', ', $returning);
        }

        return $sql;
    }

    /**
     * Creates an INSERT SQL statement.
     *
     * For example,
     *
     * ```php
     * $sql = $queryBuilder->insert('user', [
     *     'name' => 'Sam',
     *     'age' => 30,
     * ], $params);
     * ```
     *
     * The method will properly escape the table and column names.
     *
     * @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} to perform INSERT INTO ... SELECT SQL statement. Passing of {@see Query}.
     * @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|NotSupportedException
     *
     * @return string the INSERT SQL
     *
     * @psalm-suppress UndefinedInterfaceMethod
     * @psalm-suppress MixedArgument
     */
    public function insert(string $table, QueryInterface|array $columns, array &$params = []): string
    {
        return parent::insert($table, $this->normalizeTableRowData($table, $columns), $params);
    }

    /**
     * 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 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 if the table does not exist or there is no sequence
     * associated with the table.
     *
     * @return string the SQL statement for resetting sequence.
     */
    public function resetSequence(string $tableName, int|string $value = null): string
    {
        $table = $this->schema->getTableSchema($tableName);

        if ($table !== null && ($sequence = $table->getSequenceName()) !== null) {
            /**
             * {@see http://www.postgresql.org/docs/8.1/static/functions-sequence.html}
             */
            $sequence = $this->quoter->quoteTableName($sequence);
            $tableName = $this->quoter->quoteTableName($tableName);

            if ($value === null) {
                $pk = $table->getPrimaryKey();
                $key = $this->quoter->quoteColumnName(reset($pk));
                $value = "(SELECT COALESCE(MAX($key),0) FROM $tableName)+1";
            }

            return "SELECT SETVAL('$sequence',$value,false)";
        }

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

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

    /**
     * Creates an UPDATE SQL statement.
     *
     * For example,
     *
     * ```php
     * $params = [];
     * $sql = $queryBuilder->update('user', ['status' => 1], 'age > 30', $params);
     * ```
     *
     * The method will properly escape the table and column names.
     *
     * @param string $table the table to be updated.
     * @param array $columns the column data (name => value) to be updated.
     * @param array|string $condition the condition that will be put in the WHERE part. Please refer to
     * {@see Query::where()} on how to specify condition.
     * @param array $params the binding parameters that will be modified by this method so that they can be bound to the
     * DB command later.
     *
     * @return string the UPDATE SQL.
     *
     * @psalm-suppress UndefinedInterfaceMethod
     */
    public function update(string $table, array $columns, array|string $condition, array &$params = []): string
    {
        $normalizeTableRowData = $this->normalizeTableRowData($table, $columns);

        return parent::update(
            $table,
            is_array($normalizeTableRowData) ? $normalizeTableRowData : [],

            $condition,
            $params,
        );
    }

    /**
     * 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 \Yiisoft\Db\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|QueryInterface $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|InvalidConfigException|JsonException|NotSupportedException
     * if this is not supported by the underlying DBMS.
     *
     * @return string the resulting SQL.
     *
     * @link https://www.postgresql.org/docs/9.5/static/sql-insert.html#SQL-ON-CONFLICT
     * @link https://stackoverflow.com/questions/1109061/insert-on-duplicate-update-in-postgresql/8702291#8702291
     *
     * @psalm-suppress UndefinedInterfaceMethod
     */
    public function upsert(
        string $table,
        QueryInterface|array $insertColumns,
        $updateColumns,
        array &$params = []
    ): string {
        $insertColumns = $this->normalizeTableRowData($table, $insertColumns);

        if (!is_bool($updateColumns)) {
            $updateColumns = $this->normalizeTableRowData($table, $updateColumns);
        }

        $insertSql = $this->insert($table, $insertColumns, $params);

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

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

        if ($updateNames === []) {

            /** there are no columns to update */
            $updateColumns = false;
        }

        if ($updateColumns === false) {
            return "$insertSql ON CONFLICT DO NOTHING";
        }

        if ($updateColumns === true) {
            $updateColumns = [];

            /** @var string $name */
            foreach ($updateNames as $name) {

                $updateColumns[$name] = new Expression(
                    'EXCLUDED.' . $this->quoter->quoteColumnName($name)
                );
            }
        }

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

        return $insertSql
            . ' ON CONFLICT (' . implode(', ', $uniqueNames) . ') DO UPDATE SET ' . implode(', ', $updates);
    }

    /**
     * Normalizes data to be saved into the table, performing extra preparations and type converting, if necessary.
     *
     * @param string $table the table that data will be saved into.
     * @param array|QueryInterface $columns the column data (name => value) to be saved into the table or instance of
     * {@see QueryInterface} to perform INSERT INTO ... SELECT SQL statement. Passing of
     * {@see QueryInterface}.
     *
     * @return array|QueryInterface normalized columns.
     */
    private function normalizeTableRowData(string $table, QueryInterface|array $columns): QueryInterface|array
    {
        if ($columns instanceof QueryInterface) {

            return $columns;
        }

        if (($tableSchema = $this->schema->getTableSchema($table)) !== null) {
            $columnSchemas = $tableSchema->getColumns();
            /** @var mixed $value */
            foreach ($columns as $name => $value) {
                if (
                    isset($columnSchemas[$name]) &&
                    $columnSchemas[$name]->getType() === Schema::TYPE_BINARY &&
                    is_string($value)
                ) {
                    /** explicitly setup PDO param type for binary column */
                    $columns[$name] = new Param($value, PDO::PARAM_LOB);
                }
            }
        }

        return $columns;
    }
}


1		<?php
2
3		declare(strict_types=1);
4
5		namespace Yiisoft\Db\Pgsql;
6
7		use JsonException;
8		use PDO;
9		use Yiisoft\Db\Command\Param;
10		use Yiisoft\Db\Exception\Exception;
11		use Yiisoft\Db\Exception\InvalidArgumentException;
12		use Yiisoft\Db\Exception\InvalidConfigException;
13		use Yiisoft\Db\Exception\NotSupportedException;
14		use Yiisoft\Db\Expression\Expression;
15		use Yiisoft\Db\QueryBuilder\DMLQueryBuilder as AbstractDMLQueryBuilder;
16		use Yiisoft\Db\QueryBuilder\QueryBuilderInterface;
17		use Yiisoft\Db\Query\Query;
18		use Yiisoft\Db\Query\QueryInterface;
19		use Yiisoft\Db\Schema\QuoterInterface;
20		use Yiisoft\Db\Schema\Schema;
		0 ignored issues – show Bug introduced 2022-05-03 20:17 UTC by Report Bug Copy Issue Report This use statement conflicts with another class in this namespace, `Yiisoft\Db\Pgsql\Schema`. Consider defining an alias. Let?s assume that you have a directory layout like this: . \|-- OtherDir \| \|-- Bar.php \| `-- Foo.php `-- SomeDir `-- Foo.php and let?s assume the following content of `Bar.php`: // Bar.php namespace OtherDir; use SomeDir\Foo; // This now conflicts the class OtherDir\Foo If both files `OtherDir/Foo.php` and `SomeDir/Foo.php` are loaded in the same runtime, you will see a PHP error such as the following: PHP Fatal error: Cannot use SomeDir\Foo as Foo because the name is already in use in OtherDir/Foo.php However, as `OtherDir/Foo.php` does not necessarily have to be loaded and the error is only triggered if it is loaded before `OtherDir/Bar.php`, this problem might go unnoticed for a while. In order to prevent this error from surfacing, you must import the namespace with a different alias: // Bar.php namespace OtherDir; use SomeDir\Foo as SomeDirFoo; // There is no conflict anymore. Loading history...
21		use Yiisoft\Db\Schema\SchemaInterface;
22
23		use function implode;
24		use function is_array;
25		use function is_string;
26		use function reset;
27
28		final class DMLQueryBuilder extends AbstractDMLQueryBuilder
29		{
30	522	public function __construct(
31		private QueryBuilderInterface $queryBuilder,
32		private QuoterInterface $quoter,
33		private SchemaInterface $schema
34		) {
35	522	parent::__construct($queryBuilder, $quoter, $schema);
36		}
37
38	6	public function insertEx(string $table, QueryInterface\|array $columns, array &$params = []): string
39		{
40	6	$sql = $this->insert($table, $columns, $params);
41
42	6	$tableSchema = $this->schema->getTableSchema($table);
43
44	6	$returnColumns = [];
45	6	if ($tableSchema !== null) {
46	6	$returnColumns = $tableSchema->getPrimaryKey();
47		}
48
49	6	if (!empty($returnColumns)) {
50	5	$returning = [];
51	5	foreach ($returnColumns as $name) {
52	5	$returning[] = $this->quoter->quoteColumnName($name);
53		}
54	5	$sql .= ' RETURNING ' . implode(', ', $returning);
55		}
56
57	6	return $sql;
58		}
59
60		/**
61		* Creates an INSERT SQL statement.
62		*
63		* For example,
64		*
65		* ```php
66		* $sql = $queryBuilder->insert('user', [
67		* 'name' => 'Sam',
68		* 'age' => 30,
69		* ], $params);
70		* ```
71		*
72		* The method will properly escape the table and column names.
73		*
74		* @param string $table the table that new rows will be inserted into.
75		* @param array\|QueryInterface $columns the column data (name => value) to be inserted into the table or instance of
76		* {@see Query} to perform INSERT INTO ... SELECT SQL statement. Passing of {@see Query}.
77		* @param array $params the binding parameters that will be generated by this method. They should be bound to the
78		* DB command later.
79		*
80		* @throws Exception\|InvalidArgumentException\|InvalidConfigException\|NotSupportedException
81		*
82		* @return string the INSERT SQL
83		*
84		* @psalm-suppress UndefinedInterfaceMethod
85		* @psalm-suppress MixedArgument
86		*/
87	55	public function insert(string $table, QueryInterface\|array $columns, array &$params = []): string
88		{
89	55	return parent::insert($table, $this->normalizeTableRowData($table, $columns), $params);
90		}
91
92		/**
93		* Creates a SQL statement for resetting the sequence value of a table's primary key.
94		*
95		* The sequence will be reset such that the primary key of the next new row inserted will have the specified value
96		* or 1.
97		*
98		* @param string $tableName the name of the table whose primary key sequence will be reset.
99		* @param int\|string\|null $value the value for the primary key of the next new row inserted. If this is not set, the
100		* next new row's primary key will have a value 1.
101		*
102		* @throws Exception\|InvalidArgumentException if the table does not exist or there is no sequence
103		* associated with the table.
104		*
105		* @return string the SQL statement for resetting sequence.
106		*/
107	4	public function resetSequence(string $tableName, int\|string $value = null): string
108		{
109	4	$table = $this->schema->getTableSchema($tableName);
110
111	4	if ($table !== null && ($sequence = $table->getSequenceName()) !== null) {
112		/**
113		* {@see http://www.postgresql.org/docs/8.1/static/functions-sequence.html}
114		*/
115	2	$sequence = $this->quoter->quoteTableName($sequence);
116	2	$tableName = $this->quoter->quoteTableName($tableName);
117
118	2	if ($value === null) {
119	2	$pk = $table->getPrimaryKey();
120	2	$key = $this->quoter->quoteColumnName(reset($pk));
121	2	$value = "(SELECT COALESCE(MAX($key),0) FROM $tableName)+1";
122		}
123
124	2	return "SELECT SETVAL('$sequence',$value,false)";
125		}
126
127	2	if ($table === null) {
128	1	throw new InvalidArgumentException("Table not found: '$tableName'.");
129		}
130
131	1	throw new InvalidArgumentException("There is not sequence associated with table '$tableName'.");
132		}
133
134		/**
135		* Creates an UPDATE SQL statement.
136		*
137		* For example,
138		*
139		* ```php
140		* $params = [];
141		* $sql = $queryBuilder->update('user', ['status' => 1], 'age > 30', $params);
142		* ```
143		*
144		* The method will properly escape the table and column names.
145		*
146		* @param string $table the table to be updated.
147		* @param array $columns the column data (name => value) to be updated.
148		* @param array\|string $condition the condition that will be put in the WHERE part. Please refer to
149		* {@see Query::where()} on how to specify condition.
150		* @param array $params the binding parameters that will be modified by this method so that they can be bound to the
151		* DB command later.
152		*
153		* @return string the UPDATE SQL.
154		*
155		* @psalm-suppress UndefinedInterfaceMethod
156		*/
157	9	public function update(string $table, array $columns, array\|string $condition, array &$params = []): string
158		{
159	9	$normalizeTableRowData = $this->normalizeTableRowData($table, $columns);
160
161	9	return parent::update(
162	9	$table,
163	9	is_array($normalizeTableRowData) ? $normalizeTableRowData : [],
		0 ignored issues – show introduced 2022-05-03 20:17 UTC by Report Bug Copy Issue Report The condition `is_array($normalizeTableRowData)` is always `true`. Loading history...
164	9	$condition,
165	9	$params,
166	9	);
167		}
168
169		/**
170		* Creates an SQL statement to insert rows into a database table if they do not already exist (matching unique
171		* constraints), or update them if they do.
172		*
173		* For example,
174		*
175		* ```php
176		* $sql = $queryBuilder->upsert('pages', [
177		* 'name' => 'Front page',
178		* 'url' => 'http://example.com/', // url is unique
179		* 'visits' => 0,
180		* ], [
181		* 'visits' => new \Yiisoft\Db\Expression('visits + 1'),
182		* ], $params);
183		* ```
184		*
185		* The method will properly escape the table and column names.
186		*
187		* @param string $table the table that new rows will be inserted into/updated in.
188		* @param array\|QueryInterface $insertColumns the column data (name => value) to be inserted into the table or
189		* instance of {@see Query} to perform `INSERT INTO ... SELECT` SQL statement.
190		* @param array\|bool\|QueryInterface $updateColumns the column data (name => value) to be updated if they already
191		* exist.
192		* If `true` is passed, the column data will be updated to match the insert column data.
193		* If `false` is passed, no update will be performed if the column data already exists.
194		* @param array $params the binding parameters that will be generated by this method.
195		* They should be bound to the DB command later.
196		*
197		* @throws Exception\|InvalidConfigException\|InvalidConfigException\|JsonException\|NotSupportedException
198		* if this is not supported by the underlying DBMS.
199		*
200		* @return string the resulting SQL.
201		*
202		* @link https://www.postgresql.org/docs/9.5/static/sql-insert.html#SQL-ON-CONFLICT
203		* @link https://stackoverflow.com/questions/1109061/insert-on-duplicate-update-in-postgresql/8702291#8702291
204		*
205		* @psalm-suppress UndefinedInterfaceMethod
206		*/
207	18	public function upsert(
208		string $table,
209		QueryInterface\|array $insertColumns,
210		$updateColumns,
211		array &$params = []
212		): string {
213	18	$insertColumns = $this->normalizeTableRowData($table, $insertColumns);
214
215	18	if (!is_bool($updateColumns)) {
216	7	$updateColumns = $this->normalizeTableRowData($table, $updateColumns);
217		}
218
219	18	$insertSql = $this->insert($table, $insertColumns, $params);
220
221		/** @var array $uniqueNames */
222	18	[$uniqueNames, , $updateNames] = $this->prepareUpsertColumns(
223	18	$table,
224	18	$insertColumns,
225	18	$updateColumns,
226	18	);
227
228	18	if (empty($uniqueNames)) {
229	3	return $insertSql;
230		}
231
232	15	if ($updateNames === []) {
		0 ignored issues – show introduced 2022-05-03 20:58 UTC by Report Bug Copy Issue Report The condition `$updateNames === array()` is always `false`. Loading history...
233		/** there are no columns to update */
234		$updateColumns = false;
235		}
236
237	15	if ($updateColumns === false) {
238	5	return "$insertSql ON CONFLICT DO NOTHING";
239		}
240
241	10	if ($updateColumns === true) {
242	4	$updateColumns = [];
243
244		/** @var string $name */
245	4	foreach ($updateNames as $name) {
		0 ignored issues – show Bug introduced 2022-12-22 17:31 UTC by Report Bug Copy Issue Report The expression `$updateNames` of type `null` is not traversable. Loading history...
246	4	$updateColumns[$name] = new Expression(
247	4	'EXCLUDED.' . $this->quoter->quoteColumnName($name)
248	4	);
249		}
250		}
251
252		/**
253		* @var array $updateColumns
254		*
255		* @psalm-var string[] $uniqueNames
256		* @psalm-var string[] $updates
257		*/
258	10	[$updates, $params] = $this->prepareUpdateSets($table, $updateColumns, $params);
259
260	10	return $insertSql
261	10	. ' ON CONFLICT (' . implode(', ', $uniqueNames) . ') DO UPDATE SET ' . implode(', ', $updates);
262		}
263
264		/**
265		* Normalizes data to be saved into the table, performing extra preparations and type converting, if necessary.
266		*
267		* @param string $table the table that data will be saved into.
268		* @param array\|QueryInterface $columns the column data (name => value) to be saved into the table or instance of
269		* {@see QueryInterface} to perform INSERT INTO ... SELECT SQL statement. Passing of
270		* {@see QueryInterface}.
271		*
272		* @return array\|QueryInterface normalized columns.
273		*/
274	62	private function normalizeTableRowData(string $table, QueryInterface\|array $columns): QueryInterface\|array
275		{
276	62	if ($columns instanceof QueryInterface) {
		0 ignored issues – show introduced 2022-05-03 20:17 UTC by Report Bug Copy Issue Report `$columns` is never a sub-type of `Yiisoft\Db\Query\QueryInterface`. Loading history...
277	15	return $columns;
278		}
279
280	53	if (($tableSchema = $this->schema->getTableSchema($table)) !== null) {
281	47	$columnSchemas = $tableSchema->getColumns();
282		/** @var mixed $value */
283	47	foreach ($columns as $name => $value) {
284		if (
285	46	isset($columnSchemas[$name]) &&
286	46	$columnSchemas[$name]->getType() === Schema::TYPE_BINARY &&
287	46	is_string($value)
288		) {
289		/** explicitly setup PDO param type for binary column */
290	3	$columns[$name] = new Param($value, PDO::PARAM_LOB);
291		}
292		}
293		}
294
295	53	return $columns;
296		}
297		}
298

yiisoft / db-pgsql

Pull Request — master (#192)

DMLQueryBuilder::upsert() B

Complexity

Size

Duplication

Code Coverage

Importance

How to fix Long Method

Long Method

Duplication Side-by-Side

Filter issues like