DMLQueryBuilder::insertWithReturningPks() - Code Metrics - Inspection of "Add insertEx additional test + bugfix for oracle" - yiisoft/db-pgsql - Measure and Improve Code Quality continuously with Scrutinizer

Test Failed

Pull Request — master (#205)

by Def

created 2023-01-05 16:22 UTC

DMLQueryBuilder::insertWithReturningPks() A

↳ Parent: DMLQueryBuilder

Complexity

Conditions	4
Paths	4

Size

Total Lines	20
Code Lines	11

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	12
CRAP Score	4

Importance

Changes

Metric	Value
cc	4
eloc	11
nc	4
nop	3
dl	0
loc	20
ccs	12
cts	12
cp	1
crap	4
rs	9.9
c	0
b	0
f	0

<?php

declare(strict_types=1);

namespace Yiisoft\Db\Pgsql;

use JsonException;
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\SchemaInterface;

use function implode;
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 insertWithReturningPks(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 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 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 {
        $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);
    }
}


1		<?php
2
3		declare(strict_types=1);
4
5		namespace Yiisoft\Db\Pgsql;
6
7		use JsonException;
8		use Yiisoft\Db\Command\Param;
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\QueryBuilder\DMLQueryBuilder as AbstractDMLQueryBuilder;
15		use Yiisoft\Db\QueryBuilder\QueryBuilderInterface;
16		use Yiisoft\Db\Query\Query;
17		use Yiisoft\Db\Query\QueryInterface;
18		use Yiisoft\Db\Schema\QuoterInterface;
19		use Yiisoft\Db\Schema\SchemaInterface;
20
21		use function implode;
22		use function reset;
23
24		final class DMLQueryBuilder extends AbstractDMLQueryBuilder
25		{
26	553	public function __construct(
27		private QueryBuilderInterface $queryBuilder,
28		private QuoterInterface $quoter,
29		private SchemaInterface $schema
30		) {
31	553	parent::__construct($queryBuilder, $quoter, $schema);
32		}
33
34	7	public function insertWithReturningPks(string $table, QueryInterface\|array $columns, array &$params = []): string
35		{
36	7	$sql = $this->insert($table, $columns, $params);
37
38	7	$tableSchema = $this->schema->getTableSchema($table);
39
40	7	$returnColumns = [];
41	7	if ($tableSchema !== null) {
42	7	$returnColumns = $tableSchema->getPrimaryKey();
43		}
44
45	7	if (!empty($returnColumns)) {
46	6	$returning = [];
47	6	foreach ($returnColumns as $name) {
48	6	$returning[] = $this->quoter->quoteColumnName($name);
49		}
50	6	$sql .= ' RETURNING ' . implode(', ', $returning);
51		}
52
53	7	return $sql;
54		}
55
56		/**
57		* Creates a SQL statement for resetting the sequence value of a table's primary key.
58		*
59		* The sequence will be reset such that the primary key of the next new row inserted will have the specified value
60		* or 1.
61		*
62		* @param string $tableName the name of the table whose primary key sequence will be reset.
63		* @param int\|string\|null $value the value for the primary key of the next new row inserted. If this is not set, the
64		* next new row's primary key will have a value 1.
65		*
66		* @throws Exception\|InvalidArgumentException if the table does not exist or there is no sequence
67		* associated with the table.
68		*
69		* @return string the SQL statement for resetting sequence.
70		*/
71	5	public function resetSequence(string $tableName, int\|string $value = null): string
72		{
73	5	$table = $this->schema->getTableSchema($tableName);
74
75	5	if ($table !== null && ($sequence = $table->getSequenceName()) !== null) {
76		/**
77		* {@see http://www.postgresql.org/docs/8.1/static/functions-sequence.html}
78		*/
79	3	$sequence = $this->quoter->quoteTableName($sequence);
80	3	$tableName = $this->quoter->quoteTableName($tableName);
81
82	3	if ($value === null) {
83	3	$pk = $table->getPrimaryKey();
84	3	$key = $this->quoter->quoteColumnName(reset($pk));
85	3	$value = "(SELECT COALESCE(MAX($key),0) FROM $tableName)+1";
86		}
87
88	3	return "SELECT SETVAL('$sequence',$value,false)";
89		}
90
91	2	if ($table === null) {
92	1	throw new InvalidArgumentException("Table not found: '$tableName'.");
93		}
94
95	1	throw new InvalidArgumentException("There is not sequence associated with table '$tableName'.");
96		}
97
98		/**
99		* Creates an SQL statement to insert rows into a database table if they do not already exist (matching unique
100		* constraints), or update them if they do.
101		*
102		* For example,
103		*
104		* ```php
105		* $sql = $queryBuilder->upsert('pages', [
106		* 'name' => 'Front page',
107		* 'url' => 'http://example.com/', // url is unique
108		* 'visits' => 0,
109		* ], [
110		* 'visits' => new \Yiisoft\Db\Expression('visits + 1'),
111		* ], $params);
112		* ```
113		*
114		* The method will properly escape the table and column names.
115		*
116		* @param string $table the table that new rows will be inserted into/updated in.
117		* @param array\|QueryInterface $insertColumns the column data (name => value) to be inserted into the table or
118		* instance of {@see Query} to perform `INSERT INTO ... SELECT` SQL statement.
119		* @param array\|bool\|QueryInterface $updateColumns the column data (name => value) to be updated if they already
120		* exist.
121		* If `true` is passed, the column data will be updated to match the insert column data.
122		* If `false` is passed, no update will be performed if the column data already exists.
123		* @param array $params the binding parameters that will be generated by this method.
124		* They should be bound to the DB command later.
125		*
126		* @throws Exception\|InvalidConfigException\|InvalidConfigException\|JsonException\|NotSupportedException
127		* if this is not supported by the underlying DBMS.
128		*
129		* @return string the resulting SQL.
130		*
131		* @link https://www.postgresql.org/docs/9.5/static/sql-insert.html#SQL-ON-CONFLICT
132		* @link https://stackoverflow.com/questions/1109061/insert-on-duplicate-update-in-postgresql/8702291#8702291
133		*
134		* @psalm-suppress UndefinedInterfaceMethod
135		*/
136	34	public function upsert(
137		string $table,
138		QueryInterface\|array $insertColumns,
139		$updateColumns,
140		array &$params = []
141		): string {
142	34	$insertSql = $this->insert($table, $insertColumns, $params);
143
144		/** @var array $uniqueNames */
145	34	[$uniqueNames, , $updateNames] = $this->prepareUpsertColumns(
146	34	$table,
147	34	$insertColumns,
148	34	$updateColumns,
149	34	);
150
151	34	if (empty($uniqueNames)) {
152	2	return $insertSql;
153		}
154
155	32	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...
156		/** there are no columns to update */
157	2	$updateColumns = false;
158		}
159
160	32	if ($updateColumns === false) {
161	14	return "$insertSql ON CONFLICT DO NOTHING";
162		}
163
164	18	if ($updateColumns === true) {
165	8	$updateColumns = [];
166
167		/** @var string $name */
168	8	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...
169	8	$updateColumns[$name] = new Expression(
170	8	'EXCLUDED.' . $this->quoter->quoteColumnName($name)
171	8	);
172		}
173		}
174
175		/**
176		* @var array $updateColumns
177		*
178		* @psalm-var string[] $uniqueNames
179		* @psalm-var string[] $updates
180		*/
181	18	[$updates, $params] = $this->prepareUpdateSets($table, $updateColumns, $params);
182
183	18	return $insertSql
184	18	. ' ON CONFLICT (' . implode(', ', $uniqueNames) . ') DO UPDATE SET ' . implode(', ', $updates);
185		}
186		}
187

yiisoft / db-pgsql

Pull Request — master (#205)

DMLQueryBuilder::insertWithReturningPks() A

Complexity

Size

Duplication

Code Coverage

Importance

Duplication Side-by-Side

Filter issues like