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

Test Failed

Push — remove-unsed-class ( b4fd4d...4ce299 )

by Wilmer

created 2023-03-27 10:47 UTC

DMLQueryBuilder::upsert() C

↳ Parent: DMLQueryBuilder

Complexity

Conditions	13
Paths	109

Size

Total Lines	98
Code Lines	52

Duplication

Lines	0
Ratio	0 %

Importance

Changes	1
Bugs	0	Features	0

Metric	Value
cc	13
eloc	52
c	1
b	0
f	0
nc	109
nop	4
dl	0
loc	98
rs	6.5416

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\Query\QueryInterface;
use Yiisoft\Db\QueryBuilder\AbstractDMLQueryBuilder;

use function implode;
use function ltrim;
use function strrpos;
use function count;
use function reset;

/**
 * Implements a DML (Data Manipulation Language) SQL statements for Oracle Server.
 */
final class DMLQueryBuilder extends AbstractDMLQueryBuilder
{
    /**
     * @psalm-suppress MixedArrayOffset
     *
     * @throws Exception
     * @throws InvalidArgumentException
     * @throws InvalidConfigException
     * @throws NotSupportedException
     */
    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';
    }

    /**
     * @throws Exception
     * @throws NotSupportedException
     */
    public function insertWithReturningPks(string $table, QueryInterface|array $columns, array &$params = []): string
    {
        throw new NotSupportedException(__METHOD__ . ' is not supported by Oracle.');
    }

    /**
     * @link https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9016.htm#SQLRF01606
     *
     * @throws Exception
     * @throws InvalidArgumentException
     * @throws InvalidConfigException
     * @throws JsonException
     * @throws NotSupportedException
     */
    public function upsert(
        string $table,
        QueryInterface|array $insertColumns,
        array|bool $updateColumns,
        array &$params = []
    ): string {
        $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) {
                    /** @psalm-var mixed */
                    $names[] = $this->quoter->quoteColumnName($name);
                    $placeholders[] = 'DEFAULT';
                }
            }
        }

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

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

yiisoft / db-oracle

Push — remove-unsed-class ( b4fd4d...4ce299 )

DMLQueryBuilder::upsert() C

Complexity

Size

Duplication

Importance

How to fix Long Method Complexity

Long Method

Duplication Side-by-Side

Filter issues like