DMLQueryBuilder::upsert() - Code Metrics - Inspection of "Merge branch 'master' into add-more-tests" - yiisoft/db-oracle - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Push — add-more-tests ( 149556...e75ca0 )

by Wilmer

created 2023-01-02 11:50 UTC

DMLQueryBuilder::upsert() F

↳ Parent: DMLQueryBuilder

Complexity

Conditions	15
Paths	436

Size

Total Lines	106
Code Lines	56

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	58
CRAP Score	15

Importance

Changes

Metric	Value
cc	15
eloc	56
nc	436
nop	4
dl	0
loc	106
ccs	58
cts	58
cp	1
crap	15
rs	2.5333
c	0
b	0
f	0

How to fix Long Method Complexity

<?php

declare(strict_types=1);

namespace Yiisoft\Db\Oracle;

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\QueryBuilder\DMLQueryBuilder as AbstractDMLQueryBuilder;
use Yiisoft\Db\QueryBuilder\QueryBuilderInterface;
use Yiisoft\Db\Query\QueryInterface;
use Yiisoft\Db\Schema\QuoterInterface;
use Yiisoft\Db\Schema\SchemaInterface;

use function implode;
use function ltrim;
use function strrpos;
use function count;
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);
    }

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

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

        $mappedNames = $this->getNormalizeColumnNames($table, $columns);
        $values = [];

        /** @psalm-var array<array-key, array<array-key, string>> $rows */
        foreach ($rows as $row) {
            $placeholders = [];
            foreach ($row as $index => $value) {
                if (isset($columns[$index], $mappedNames[$columns[$index]], $columnSchemas[$mappedNames[$columns[$index]]])) {
                    /** @var mixed $value */
                    $value = $this->getTypecastValue($value, $columnSchemas[$mappedNames[$columns[$index]]]);
                }

                if ($value instanceof ExpressionInterface) {
                    $placeholders[] = $this->queryBuilder->buildExpression($value, $params);
                } else {
                    $placeholders[] = $this->queryBuilder->bindParam($value, $params);
                }
            }
            $values[] = '(' . implode(', ', $placeholders) . ')';
        }

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

        foreach ($columns as $i => $name) {
            $columns[$i] = $this->quoter->quoteColumnName($mappedNames[$name]);
        }

        $tableAndColumns = ' INTO ' . $this->quoter->quoteTableName($table)
            . ' (' . implode(', ', $columns) . ') VALUES ';

        return 'INSERT ALL ' . $tableAndColumns . implode($tableAndColumns, $values) . ' SELECT 1 FROM SYS.DUAL';
    }

    /**
     * @link https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9016.htm#SQLRF01606
     *
     * @throws Exception|InvalidArgumentException|InvalidConfigException|JsonException|NotSupportedException
     */
    public function upsert(
        string $table,
        QueryInterface|array $insertColumns,
        array|bool $updateColumns,
        array &$params = []
    ): string {
        if (!$insertColumns instanceof QueryInterface) {

            $insertColumns = $this->normalizeColumnNames($table, $insertColumns);
        }

        if (!is_bool($updateColumns)) {

            $updateColumns = $this->normalizeColumnNames($table, $updateColumns);
        }

        $usingValues = null;
        $constraints = [];

        [$uniqueNames, $insertNames, $updateNames] = $this->prepareUpsertColumns(
            $table,
            $insertColumns,
            $updateColumns,
            $constraints
        );

        if (empty($uniqueNames)) {
            return $this->insert($table, $insertColumns, $params);
        }

        if ($updateNames === []) {

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

        $onCondition = ['or'];
        $quotedTableName = $this->quoter->quoteTableName($table);

        foreach ($constraints as $constraint) {
            $columnNames = $constraint->getColumnNames() ?? [];
            $constraintCondition = ['and'];
            /** @psalm-var string[] $columnNames */
            foreach ($columnNames as $name) {
                $quotedName = $this->quoter->quoteColumnName($name);
                $constraintCondition[] = "$quotedTableName.$quotedName=\"EXCLUDED\".$quotedName";
            }

            $onCondition[] = $constraintCondition;
        }

        $on = $this->queryBuilder->buildCondition($onCondition, $params);
        /** @psalm-var string[] $placeholders */
        [, $placeholders, $values, $params] = $this->prepareInsertValues($table, $insertColumns, $params);

        if (!empty($placeholders)) {
            $usingSelectValues = [];
            /** @psalm-var string[] $insertNames */
            foreach ($insertNames as $index => $name) {
                $usingSelectValues[$name] = new Expression($placeholders[$index]);
            }

            /** @psalm-var array $params */
            $usingValues = $this->queryBuilder->buildSelect($usingSelectValues, $params) . ' ' . $this->queryBuilder->buildFrom(['DUAL'], $params);
        }

        $insertValues = [];
        $mergeSql = 'MERGE INTO '
            . $this->quoter->quoteTableName($table)
            . ' '
            . 'USING (' . ($usingValues ?? ltrim((string) $values, ' '))
            . ') "EXCLUDED" '
            . "ON ($on)";

        /** @psalm-var string[] $insertNames */
        foreach ($insertNames as $name) {
            $quotedName = $this->quoter->quoteColumnName($name);

            if (strrpos($quotedName, '.') === false) {
                $quotedName = '"EXCLUDED".' . $quotedName;
            }

            $insertValues[] = $quotedName;
        }

        $insertSql = 'INSERT (' . implode(', ', $insertNames) . ')' . ' VALUES (' . implode(', ', $insertValues) . ')';

        if ($updateColumns === false) {

            return "$mergeSql WHEN NOT MATCHED THEN $insertSql";
        }

        if ($updateColumns === true) {

            $updateColumns = [];
            /** @psalm-var string[] $updateNames */
            foreach ($updateNames as $name) {
                $quotedName = $this->quoter->quoteColumnName($name);

                if (strrpos($quotedName, '.') === false) {
                    $quotedName = '"EXCLUDED".' . $quotedName;
                }
                $updateColumns[$name] = new Expression($quotedName);
            }
        }

        /** @psalm-var string[] $updates */
        [$updates, $params] = $this->prepareUpdateSets($table, $updateColumns, (array) $params);
        $updateSql = 'UPDATE SET ' . implode(', ', $updates);

        return "$mergeSql WHEN MATCHED THEN $updateSql WHEN NOT MATCHED THEN $insertSql";
    }

    protected function prepareInsertValues(string $table, array|QueryInterface $columns, array $params = []): array
    {
        /**
         * @var array $names
         * @var array $placeholders
         */
        [$names, $placeholders, $values, $params] = parent::prepareInsertValues($table, $columns, $params);

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

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

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

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

    /**
     * @throws InvalidArgumentException
     */
    public function resetSequence(string $tableName, int|string $value = null): string
    {
        $tableSchema = $this->schema->getTableSchema($tableName);

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

        $sequenceName = $tableSchema->getSequenceName();

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

        if ($value === null && count($tableSchema->getPrimaryKey()) > 1) {
            throw new InvalidArgumentException("Can't reset sequence for composite primary key in table: $tableName");
        }

        /**
         * Oracle needs at least many queries to reset sequence (see adding transactions and/or use alter method to
         * avoid grants issue?)
         */
        return 'declare
    lastSeq number' . ($value !== null ? (' := ' . $value) : '') . ';
begin' . ($value === null ? '
    SELECT MAX("' . $tableSchema->getPrimaryKey()[0] . '") + 1 INTO lastSeq FROM "' . $tableSchema->getName() . '";' : '') . '
    if lastSeq IS NULL then lastSeq := 1; end if;
    execute immediate \'DROP SEQUENCE "' . $sequenceName . '"\';
    execute immediate \'CREATE SEQUENCE "' . $sequenceName . '" START WITH \' || lastSeq || \' INCREMENT BY 1 NOMAXVALUE NOCACHE\';
end;';
    }
}


1		<?php
2
3		declare(strict_types=1);
4
5		namespace Yiisoft\Db\Oracle;
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\QueryBuilder\DMLQueryBuilder as AbstractDMLQueryBuilder;
16		use Yiisoft\Db\QueryBuilder\QueryBuilderInterface;
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 ltrim;
23		use function strrpos;
24		use function count;
25		use function reset;
26
27		final class DMLQueryBuilder extends AbstractDMLQueryBuilder
28		{
29	479	public function __construct(
30		private QueryBuilderInterface $queryBuilder,
31		private QuoterInterface $quoter,
32		private SchemaInterface $schema
33		) {
34	479	parent::__construct($queryBuilder, $quoter, $schema);
35		}
36
37		/**
38		* @psalm-suppress MixedArrayOffset
39		*/
40	15	public function batchInsert(string $table, array $columns, iterable\|Generator $rows, array &$params = []): string
41		{
42	15	if (empty($rows)) {
43	1	return '';
44		}
45
46	14	if (($tableSchema = $this->schema->getTableSchema($table)) !== null) {
47	14	$columnSchemas = $tableSchema->getColumns();
48		} else {
49		$columnSchemas = [];
50		}
51
52	14	$mappedNames = $this->getNormalizeColumnNames($table, $columns);
53	14	$values = [];
54
55		/** @psalm-var array<array-key, array<array-key, string>> $rows */
56	14	foreach ($rows as $row) {
57	14	$placeholders = [];
58	14	foreach ($row as $index => $value) {
59	14	if (isset($columns[$index], $mappedNames[$columns[$index]], $columnSchemas[$mappedNames[$columns[$index]]])) {
60		/** @var mixed $value */
61	13	$value = $this->getTypecastValue($value, $columnSchemas[$mappedNames[$columns[$index]]]);
62		}
63
64	14	if ($value instanceof ExpressionInterface) {
65	3	$placeholders[] = $this->queryBuilder->buildExpression($value, $params);
66		} else {
67	14	$placeholders[] = $this->queryBuilder->bindParam($value, $params);
68		}
69		}
70	14	$values[] = '(' . implode(', ', $placeholders) . ')';
71		}
72
73	14	if (empty($values)) {
74		return '';
75		}
76
77	14	foreach ($columns as $i => $name) {
78	13	$columns[$i] = $this->quoter->quoteColumnName($mappedNames[$name]);
79		}
80
81	14	$tableAndColumns = ' INTO ' . $this->quoter->quoteTableName($table)
82	14	. ' (' . implode(', ', $columns) . ') VALUES ';
83
84	14	return 'INSERT ALL ' . $tableAndColumns . implode($tableAndColumns, $values) . ' SELECT 1 FROM SYS.DUAL';
85		}
86
87		/**
88		* @link https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9016.htm#SQLRF01606
89		*
90		* @throws Exception\|InvalidArgumentException\|InvalidConfigException\|JsonException\|NotSupportedException
91		*/
92	34	public function upsert(
93		string $table,
94		QueryInterface\|array $insertColumns,
95		array\|bool $updateColumns,
96		array &$params = []
97		): string {
98	34	if (!$insertColumns instanceof QueryInterface) {
		0 ignored issues – show introduced 2022-12-30 17:40 UTC by Report Bug Copy Issue Report `$insertColumns` is never a sub-type of `Yiisoft\Db\Query\QueryInterface`. Loading history...
99	21	$insertColumns = $this->normalizeColumnNames($table, $insertColumns);
100		}
101
102	34	if (!is_bool($updateColumns)) {
		0 ignored issues – show introduced 2022-12-30 17:40 UTC by Report Bug Copy Issue Report The condition `is_bool($updateColumns)` is always `false`. Loading history...
103	10	$updateColumns = $this->normalizeColumnNames($table, $updateColumns);
104		}
105
106	34	$usingValues = null;
107	34	$constraints = [];
108
109	34	[$uniqueNames, $insertNames, $updateNames] = $this->prepareUpsertColumns(
110	34	$table,
111	34	$insertColumns,
112	34	$updateColumns,
113	34	$constraints
114	34	);
115
116	34	if (empty($uniqueNames)) {
117	2	return $this->insert($table, $insertColumns, $params);
118		}
119
120	32	if ($updateNames === []) {
		0 ignored issues – show introduced 2022-05-03 21:19 UTC by Report Bug Copy Issue Report The condition `$updateNames === array()` is always `false`. Loading history...
121		/** there are no columns to update */
122	2	$updateColumns = false;
123		}
124
125	32	$onCondition = ['or'];
126	32	$quotedTableName = $this->quoter->quoteTableName($table);
127
128	32	foreach ($constraints as $constraint) {
129	32	$columnNames = $constraint->getColumnNames() ?? [];
130	32	$constraintCondition = ['and'];
131		/** @psalm-var string[] $columnNames */
132	32	foreach ($columnNames as $name) {
133	32	$quotedName = $this->quoter->quoteColumnName($name);
134	32	$constraintCondition[] = "$quotedTableName.$quotedName=\"EXCLUDED\".$quotedName";
135		}
136
137	32	$onCondition[] = $constraintCondition;
138		}
139
140	32	$on = $this->queryBuilder->buildCondition($onCondition, $params);
141		/** @psalm-var string[] $placeholders */
142	32	[, $placeholders, $values, $params] = $this->prepareInsertValues($table, $insertColumns, $params);
143
144	32	if (!empty($placeholders)) {
145	19	$usingSelectValues = [];
146		/** @psalm-var string[] $insertNames */
147	19	foreach ($insertNames as $index => $name) {
148	19	$usingSelectValues[$name] = new Expression($placeholders[$index]);
149		}
150
151		/** @psalm-var array $params */
152	19	$usingValues = $this->queryBuilder->buildSelect($usingSelectValues, $params) . ' ' . $this->queryBuilder->buildFrom(['DUAL'], $params);
153		}
154
155	32	$insertValues = [];
156	32	$mergeSql = 'MERGE INTO '
157	32	. $this->quoter->quoteTableName($table)
158	32	. ' '
159	32	. 'USING (' . ($usingValues ?? ltrim((string) $values, ' '))
160	32	. ') "EXCLUDED" '
161	32	. "ON ($on)";
162
163		/** @psalm-var string[] $insertNames */
164	32	foreach ($insertNames as $name) {
165	32	$quotedName = $this->quoter->quoteColumnName($name);
166
167	32	if (strrpos($quotedName, '.') === false) {
168	32	$quotedName = '"EXCLUDED".' . $quotedName;
169		}
170
171	32	$insertValues[] = $quotedName;
172		}
173
174	32	$insertSql = 'INSERT (' . implode(', ', $insertNames) . ')' . ' VALUES (' . implode(', ', $insertValues) . ')';
175
176	32	if ($updateColumns === false) {
		0 ignored issues – show introduced 2022-05-03 21:19 UTC by Report Bug Copy Issue Report The condition `$updateColumns === false` is always `false`. Loading history...
177	14	return "$mergeSql WHEN NOT MATCHED THEN $insertSql";
178		}
179
180	18	if ($updateColumns === true) {
		0 ignored issues – show introduced 2022-03-17 18:43 UTC by Report Bug Copy Issue Report The condition `$updateColumns === true` is always `false`. Loading history...
181	8	$updateColumns = [];
182		/** @psalm-var string[] $updateNames */
183	8	foreach ($updateNames as $name) {
184	8	$quotedName = $this->quoter->quoteColumnName($name);
185
186	8	if (strrpos($quotedName, '.') === false) {
187	8	$quotedName = '"EXCLUDED".' . $quotedName;
188		}
189	8	$updateColumns[$name] = new Expression($quotedName);
190		}
191		}
192
193		/** @psalm-var string[] $updates */
194	18	[$updates, $params] = $this->prepareUpdateSets($table, $updateColumns, (array) $params);
195	18	$updateSql = 'UPDATE SET ' . implode(', ', $updates);
196
197	18	return "$mergeSql WHEN MATCHED THEN $updateSql WHEN NOT MATCHED THEN $insertSql";
198		}
199
200	66	protected function prepareInsertValues(string $table, array\|QueryInterface $columns, array $params = []): array
201		{
202		/**
203		* @var array $names
204		* @var array $placeholders
205		*/
206	66	[$names, $placeholders, $values, $params] = parent::prepareInsertValues($table, $columns, $params);
207
208	63	if (!$columns instanceof QueryInterface && empty($names)) {
		0 ignored issues – show introduced 2022-05-24 18:22 UTC by Report Bug Copy Issue Report `$columns` is never a sub-type of `Yiisoft\Db\Query\QueryInterface`. Loading history...
209	1	$tableSchema = $this->schema->getTableSchema($table);
210
211	1	if ($tableSchema !== null) {
212	1	$tableColumns = $tableSchema->getColumns();
213	1	$columns = !empty($tableSchema->getPrimaryKey())
214	1	? $tableSchema->getPrimaryKey() : [reset($tableColumns)->getName()];
215	1	foreach ($columns as $name) {
216		/** @var mixed */
217	1	$names[] = $this->quoter->quoteColumnName($name);
218	1	$placeholders[] = 'DEFAULT';
219		}
220		}
221		}
222
223	63	return [$names, $placeholders, $values, $params];
224		}
225
226		/**
227		* @throws InvalidArgumentException
228		*/
229	4	public function resetSequence(string $tableName, int\|string $value = null): string
230		{
231	4	$tableSchema = $this->schema->getTableSchema($tableName);
232
233	4	if ($tableSchema === null) {
234	1	throw new InvalidArgumentException("Table not found: '$tableName'.");
235		}
236
237	3	$sequenceName = $tableSchema->getSequenceName();
238
239	3	if ($sequenceName === null) {
240	1	throw new InvalidArgumentException("There is not sequence associated with table '$tableName'.");
241		}
242
243	2	if ($value === null && count($tableSchema->getPrimaryKey()) > 1) {
244	1	throw new InvalidArgumentException("Can't reset sequence for composite primary key in table: $tableName");
245		}
246
247		/**
248		* Oracle needs at least many queries to reset sequence (see adding transactions and/or use alter method to
249		* avoid grants issue?)
250		*/
251	1	return 'declare
252	1	lastSeq number' . ($value !== null ? (' := ' . $value) : '') . ';
253	1	begin' . ($value === null ? '
254	1	SELECT MAX("' . $tableSchema->getPrimaryKey()[0] . '") + 1 INTO lastSeq FROM "' . $tableSchema->getName() . '";' : '') . '
255		if lastSeq IS NULL then lastSeq := 1; end if;
256	1	execute immediate \'DROP SEQUENCE "' . $sequenceName . '"\';
257	1	execute immediate \'CREATE SEQUENCE "' . $sequenceName . '" START WITH \' \|\| lastSeq \|\| \' INCREMENT BY 1 NOMAXVALUE NOCACHE\';
258	1	end;';
259		}
260		}
261

yiisoft / db-oracle

Push — add-more-tests ( 149556...e75ca0 )

DMLQueryBuilder::upsert() F

Complexity

Size

Duplication

Code Coverage

Importance

How to fix Long Method Complexity

Long Method

Duplication Side-by-Side

Filter issues like