DMLQueryBuilder::resetSequence() - Code Metrics - Inspection of "big refactoring." - yiisoft/db-pgsql - Measure and Improve Code Quality continuously with Scrutinizer

Test Failed

Pull Request — master (#98)

by Wilmer

created 2022-02-12 12:21 UTC

DMLQueryBuilder::resetSequence() A

↳ Parent: DMLQueryBuilder

Complexity

Conditions	5
Paths	4

Size

Total Lines	27
Code Lines	14

Duplication

Lines	0
Ratio	0 %

Importance

Changes	1
Bugs	0	Features	0

Metric	Value
cc	5
eloc	14
c	1
b	0
f	0
nc	4
nop	2
dl	0
loc	27
rs	9.4888

<?php

declare(strict_types=1);

namespace Yiisoft\Db\Pgsql;

use Generator;
use JsonException;
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;
filter:
    dependency_paths: ["lib/*"]
use Yiisoft\Db\Query\Query;
use Yiisoft\Db\Query\QueryBuilderInterface;
filter:
    dependency_paths: ["lib/*"]
use Yiisoft\Strings\NumericHelper;

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

    public function batchInsert(string $table, array $columns, iterable|Generator $rows, array &$params = []): string
    {
        if (empty($rows)) {
            return '';
        }

        /**
         * @var array<array-key, object> $columnSchemas
         */
        $columnSchemas = [];
        $schema = $this->queryBuilder->schema();

        if (($tableSchema = $schema->getTableSchema($table)) !== null) {
            $columnSchemas = $tableSchema->getColumns();
        }

        $values = [];

        /**
         * @var array $row
         */
        foreach ($rows as $row) {
            $vs = [];
            /**
             *  @var int $i
             *  @var mixed $value
             */
            foreach ($row as $i => $value) {
                if (isset($columns[$i], $columnSchemas[$columns[$i]])) {
                    /**
                     * @var bool|ExpressionInterface|float|int|string|null $value
                     */
                    $value = $columnSchemas[$columns[$i]]->dbTypecast($value);
                }

                if (is_string($value)) {
                    $value = $this->queryBuilder->quoter()->quoteValue($value);
                } elseif (is_float($value)) {
                    /** ensure type cast always has . as decimal separator in all locales */
                    $value = NumericHelper::normalize((string) $value);
                } elseif ($value === true) {
                    $value = 'TRUE';
                } elseif ($value === false) {
                    $value = 'FALSE';
                } elseif ($value === null) {
                    $value = 'NULL';
                } elseif ($value instanceof ExpressionInterface) {
                    $value = $this->queryBuilder->buildExpression($value, $params);
                }

                /** @var bool|ExpressionInterface|float|int|string|null $value */
                $vs[] = $value;
            }
            $values[] = '(' . implode(', ', $vs) . ')';
        }

        if (empty($values)) {
            return '';
        }

        /** @var string name */
        foreach ($columns as $i => $name) {
            $columns[$i] = $this->queryBuilder->quoter()->quoteColumnName($name);
        }

        return 'INSERT INTO '
            . $this->queryBuilder->quoter()->quoteTableName($table)
            . ' (' . implode(', ', $columns) . ') VALUES ' . implode(', ', $values);
    }

    /**
     * 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|Query $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. Passing of
     * {@see Query|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
     */
    public function insert(string $table, Query|array $columns, array &$params = []): string
    {
        return parent::insert($table, $this->queryBuilder->normalizeTableRowData($table, $columns), $params);
    }

    /**
     * {@see upsert()} implementation for PostgresSQL 9.5 or higher.
     *
     * @param string $table
     * @param array|Query $insertColumns
     * @param bool|array|Query $updateColumns
     * @param array $params
     *
     * @return string
     *@throws Exception|InvalidArgumentException|InvalidConfigException|JsonException|NotSupportedException
     *
     */
    public function newUpsert(
        string $table,
        Query|array $insertColumns,
        bool|array|Query $updateColumns,
        array &$params = []
    ): string {
        $insertSql = $this->insert($table, $insertColumns, $params);

        /** @var array $uniqueNames */
        [$uniqueNames, , $updateNames] = $this->queryBuilder->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->queryBuilder->quoter()->quoteColumnName($name));
            }
        }

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

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

    /**
     * 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.
     *
     * @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, $value = null): string
    {
        $table = $this->queryBuilder->schema()->getTableSchema($tableName);

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

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

            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.
     *
     * @throws Exception|InvalidArgumentException
     *
     * @return string the UPDATE SQL.
     *
     * @psalm-suppress UndefinedInterfaceMethod
     */
    public function update(string $table, array $columns, array|string $condition, array &$params = []): string
    {
        return parent::update(
            $table,
            $this->queryBuilder->normalizeTableRowData($table, $columns),
            $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|Query $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|Query $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.
     *
     * @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
     *
     * @throws Exception|InvalidConfigException|JsonException|NotSupportedException if this is not supported by the
     * underlying DBMS.
     *
     * @psalm-suppress UndefinedInterfaceMethod
     */
    public function upsert(string $table, Query|array $insertColumns, $updateColumns, array &$params = []): string
    {
        $insertColumns = $this->queryBuilder->normalizeTableRowData($table, $insertColumns);

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

        return $this->newUpsert($table, $insertColumns, $updateColumns, $params);
    }
}


1			<?php
2
3			declare(strict_types=1);
4
5			namespace Yiisoft\Db\Pgsql;
6
7			use Generator;
8			use JsonException;
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;
			0 ignored issues – show Bug introduced 2022-02-12 12:43 UTC by Report Bug Copy Issue Report The type `Yiisoft\Db\Query\DMLQueryBuilder` was not found. Maybe you did not declare it correctly or list all dependencies? The issue could also be caused by a filter entry in the build configuration. If the path has been excluded in your configuration, e.g. `excluded_paths: ["lib/"]`, you can move it to the dependency path list as follows: filter: dependency_paths: ["lib/"] For further information see https://scrutinizer-ci.com/docs/tools/php/php-scrutinizer/#list-dependency-paths Loading history...
16			use Yiisoft\Db\Query\Query;
17			use Yiisoft\Db\Query\QueryBuilderInterface;
			0 ignored issues – show Bug introduced 2022-02-12 12:43 UTC by Report Bug Copy Issue Report The type `Yiisoft\Db\Query\QueryBuilderInterface` was not found. Maybe you did not declare it correctly or list all dependencies? The issue could also be caused by a filter entry in the build configuration. If the path has been excluded in your configuration, e.g. `excluded_paths: ["lib/"]`, you can move it to the dependency path list as follows: filter: dependency_paths: ["lib/"] For further information see https://scrutinizer-ci.com/docs/tools/php/php-scrutinizer/#list-dependency-paths Loading history...
18			use Yiisoft\Strings\NumericHelper;
19
20			final class DMLQueryBuilder extends AbstractDMLQueryBuilder
21			{
22			public function __construct(private QueryBuilderInterface $queryBuilder)
23			{
24			parent::__construct($queryBuilder);
25			}
26
27			public function batchInsert(string $table, array $columns, iterable\|Generator $rows, array &$params = []): string
28			{
29			if (empty($rows)) {
30			return '';
31			}
32
33			/**
34			* @var array<array-key, object> $columnSchemas
35			*/
36			$columnSchemas = [];
37			$schema = $this->queryBuilder->schema();
38
39			if (($tableSchema = $schema->getTableSchema($table)) !== null) {
40			$columnSchemas = $tableSchema->getColumns();
41			}
42
43			$values = [];
44
45			/**
46			* @var array $row
47			*/
48			foreach ($rows as $row) {
49			$vs = [];
50			/**
51			* @var int $i
52			* @var mixed $value
53			*/
54			foreach ($row as $i => $value) {
55			if (isset($columns[$i], $columnSchemas[$columns[$i]])) {
56			/**
57			* @var bool\|ExpressionInterface\|float\|int\|string\|null $value
58			*/
59			$value = $columnSchemas[$columns[$i]]->dbTypecast($value);
60			}
61
62			if (is_string($value)) {
63			$value = $this->queryBuilder->quoter()->quoteValue($value);
64			} elseif (is_float($value)) {
65			/** ensure type cast always has . as decimal separator in all locales */
66			$value = NumericHelper::normalize((string) $value);
67			} elseif ($value === true) {
68			$value = 'TRUE';
69			} elseif ($value === false) {
70			$value = 'FALSE';
71			} elseif ($value === null) {
72			$value = 'NULL';
73			} elseif ($value instanceof ExpressionInterface) {
74			$value = $this->queryBuilder->buildExpression($value, $params);
75			}
76
77			/** @var bool\|ExpressionInterface\|float\|int\|string\|null $value */
78			$vs[] = $value;
79			}
80			$values[] = '(' . implode(', ', $vs) . ')';
81			}
82
83			if (empty($values)) {
84			return '';
85			}
86
87			/** @var string name */
88			foreach ($columns as $i => $name) {
89			$columns[$i] = $this->queryBuilder->quoter()->quoteColumnName($name);
90			}
91
92			return 'INSERT INTO '
93			. $this->queryBuilder->quoter()->quoteTableName($table)
94			. ' (' . implode(', ', $columns) . ') VALUES ' . implode(', ', $values);
95			}
96
97			/**
98			* Creates an INSERT SQL statement.
99			*
100			* For example,
101			*
102			* ```php
103			* $sql = $queryBuilder->insert('user', [
104			* 'name' => 'Sam',
105			* 'age' => 30,
106			* ], $params);
107			* ```
108			*
109			* The method will properly escape the table and column names.
110			*
111			* @param string $table the table that new rows will be inserted into.
112			* @param array\|Query $columns the column data (name => value) to be inserted into the table or instance of
113			* {@see Query\|Query} to perform INSERT INTO ... SELECT SQL statement. Passing of
114			* {@see Query\|Query}.
115			* @param array $params the binding parameters that will be generated by this method. They should be bound to the
116			* DB command later.
117			*
118			* @throws Exception\|InvalidArgumentException\|InvalidConfigException\|NotSupportedException
119			*
120			* @return string the INSERT SQL
121			*
122			* @psalm-suppress UndefinedInterfaceMethod
123			*/
124			public function insert(string $table, Query\|array $columns, array &$params = []): string
125			{
126			return parent::insert($table, $this->queryBuilder->normalizeTableRowData($table, $columns), $params);
127			}
128
129			/**
130			* {@see upsert()} implementation for PostgresSQL 9.5 or higher.
131			*
132			* @param string $table
133			* @param array\|Query $insertColumns
134			* @param bool\|array\|Query $updateColumns
135			* @param array $params
136			*
137			* @return string
138			*@throws Exception\|InvalidArgumentException\|InvalidConfigException\|JsonException\|NotSupportedException
139			*
140			*/
141			public function newUpsert(
142			string $table,
143			Query\|array $insertColumns,
144			bool\|array\|Query $updateColumns,
145			array &$params = []
146			): string {
147			$insertSql = $this->insert($table, $insertColumns, $params);
148
149			/** @var array $uniqueNames */
150			[$uniqueNames, , $updateNames] = $this->queryBuilder->prepareUpsertColumns(
151			$table,
152			$insertColumns,
153			$updateColumns,
154			);
155
156			if (empty($uniqueNames)) {
157			return $insertSql;
158			}
159
160			if ($updateNames === []) {
161			/** there are no columns to update */
162			$updateColumns = false;
163			}
164
165			if ($updateColumns === false) {
166			return "$insertSql ON CONFLICT DO NOTHING";
167			}
168
169			if ($updateColumns === true) {
			0 ignored issues – show introduced 2022-02-12 12:43 UTC by Report Bug Copy Issue Report The condition `$updateColumns === true` is always `false`. Loading history...
170			$updateColumns = [];
171
172			/** @var string $name */
173			foreach ($updateNames as $name) {
174			$updateColumns[$name] = new Expression('EXCLUDED.' . $this->queryBuilder->quoter()->quoteColumnName($name));
175			}
176			}
177
178			/** @var array $updates */
179			[$updates, $params] = $this->queryBuilder->prepareUpdateSets($table, $updateColumns, $params);
180
181			return $insertSql
182			. ' ON CONFLICT (' . implode(', ', $uniqueNames) . ') DO UPDATE SET ' . implode(', ', $updates);
183			}
184
185			/**
186			* Creates a SQL statement for resetting the sequence value of a table's primary key.
187			*
188			* The sequence will be reset such that the primary key of the next new row inserted will have the specified value
189			* or 1.
190			*
191			* @param string $tableName the name of the table whose primary key sequence will be reset.
192			* @param mixed $value the value for the primary key of the next new row inserted. If this is not set, the next new
193			* row's primary key will have a value 1.
194			*
195			* @throws Exception\|InvalidArgumentException if the table does not exist or there is no sequence
196			* associated with the table.
197			*
198			* @return string the SQL statement for resetting sequence.
199			*/
200			public function resetSequence(string $tableName, $value = null): string
201			{
202			$table = $this->queryBuilder->schema()->getTableSchema($tableName);
203
204			if ($table !== null && ($sequence = $table->getSequenceName()) !== null) {
205			/**
206			* {@see http://www.postgresql.org/docs/8.1/static/functions-sequence.html}
207			*/
208			$sequence = $this->queryBuilder->quoter()->quoteTableName($sequence);
209			$tableName = $this->queryBuilder->quoter()->quoteTableName($tableName);
210
211			if ($value === null) {
212			$pk = $table->getPrimaryKey();
213			$key = $this->queryBuilder->quoter()->quoteColumnName(reset($pk));
214			$value = "(SELECT COALESCE(MAX($key),0) FROM $tableName)+1";
215			} else {
216			$value = (int) $value;
217			}
218
219			return "SELECT SETVAL('$sequence',$value,false)";
220			}
221
222			if ($table === null) {
223			throw new InvalidArgumentException("Table not found: $tableName");
224			}
225
226			throw new InvalidArgumentException("There is not sequence associated with table '$tableName'.");
227			}
228
229			/**
230			* Creates an UPDATE SQL statement.
231			*
232			* For example,
233			*
234			* ```php
235			* $params = [];
236			* $sql = $queryBuilder->update('user', ['status' => 1], 'age > 30', $params);
237			* ```
238			*
239			* The method will properly escape the table and column names.
240			*
241			* @param string $table the table to be updated.
242			* @param array $columns the column data (name => value) to be updated.
243			* @param array\|string $condition the condition that will be put in the WHERE part. Please refer to
244			* {@see Query::where()} on how to specify condition.
245			* @param array $params the binding parameters that will be modified by this method so that they can be bound to the
246			* DB command later.
247			*
248			* @throws Exception\|InvalidArgumentException
249			*
250			* @return string the UPDATE SQL.
251			*
252			* @psalm-suppress UndefinedInterfaceMethod
253			*/
254			public function update(string $table, array $columns, array\|string $condition, array &$params = []): string
255			{
256			return parent::update(
257			$table,
258			$this->queryBuilder->normalizeTableRowData($table, $columns),
259			$condition,
260			$params,
261			);
262			}
263
264			/**
265			* Creates an SQL statement to insert rows into a database table if they do not already exist (matching unique
266			* constraints), or update them if they do.
267			*
268			* For example,
269			*
270			* ```php
271			* $sql = $queryBuilder->upsert('pages', [
272			* 'name' => 'Front page',
273			* 'url' => 'http://example.com/', // url is unique
274			* 'visits' => 0,
275			* ], [
276			* 'visits' => new \Yiisoft\Db\Expression('visits + 1'),
277			* ], $params);
278			* ```
279			*
280			* The method will properly escape the table and column names.
281			*
282			* @param string $table the table that new rows will be inserted into/updated in.
283			* @param array\|Query $insertColumns the column data (name => value) to be inserted into the table or instance of
284			* {@see Query} to perform `INSERT INTO ... SELECT` SQL statement.
285			* @param array\|bool\|Query $updateColumns the column data (name => value) to be updated if they already exist.
286			* If `true` is passed, the column data will be updated to match the insert column data.
287			* If `false` is passed, no update will be performed if the column data already exists.
288			* @param array $params the binding parameters that will be generated by this method.
289			* They should be bound to the DB command later.
290			*
291			* @return string the resulting SQL.
292			*
293			* @link https://www.postgresql.org/docs/9.5/static/sql-insert.html#SQL-ON-CONFLICT
294			* @link https://stackoverflow.com/questions/1109061/insert-on-duplicate-update-in-postgresql/8702291#8702291
295			*
296			* @throws Exception\|InvalidConfigException\|JsonException\|NotSupportedException if this is not supported by the
297			* underlying DBMS.
298			*
299			* @psalm-suppress UndefinedInterfaceMethod
300			*/
301			public function upsert(string $table, Query\|array $insertColumns, $updateColumns, array &$params = []): string
302			{
303			$insertColumns = $this->queryBuilder->normalizeTableRowData($table, $insertColumns);
304
305			if (!is_bool($updateColumns)) {
306			$updateColumns = $this->queryBuilder->normalizeTableRowData($table, $updateColumns);
307			}
308
309			return $this->newUpsert($table, $insertColumns, $updateColumns, $params);
310			}
311			}
312

yiisoft / db-pgsql

Pull Request — master (#98)

DMLQueryBuilder::resetSequence() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like