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

Test Failed

Pull Request — master (#233)

by Sergei

created 2023-10-31 02:03 UTC

DMLQueryBuilder::upsert() B

↳ Parent: DMLQueryBuilder

Complexity

Conditions	11
Paths	37

Size

Total Lines	75
Code Lines	39

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	41
CRAP Score	11

Importance

Changes	1
Bugs	0	Features	0

Metric	Value
cc	11
eloc	39
c	1
b	0
f	0
nc	37
nop	4
dl	0
loc	75
ccs	41
cts	41
cp	1
crap	11
rs	7.3166

How to fix Long Method Complexity

<?php

declare(strict_types=1);

namespace Yiisoft\Db\Oracle;

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 array_map;
use function implode;
use function count;

/**
 * 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 $rows, array &$params = []): string
    {
        if (empty($rows)) {
            return '';
        }

        $values = [];
        $columns = $this->getNormalizeColumnNames('', $columns);
        $columnSchemas = $this->schema->getTableSchema($table)?->getColumns() ?? [];

        /** @psalm-var array[] $rows */
        foreach ($rows as $row) {
            $i = 0;
            $placeholders = [];
            /** @psalm-var mixed $value */
            foreach ($row as $value) {
                if (isset($columns[$i], $columnSchemas[$columns[$i]])) {
                    /** @psalm-var mixed $value */
                    $value = $columnSchemas[$columns[$i]]->dbTypecast($value);
                }

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

                ++$i;
            }
            $values[] = '(' . implode(', ', $placeholders) . ')';
        }

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

        $columns = array_map(
            [$this->quoter, 'quoteColumnName'],
            $columns,
        );

        $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 {
        $constraints = [];

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

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

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

        foreach ($constraints as $constraint) {
            $columnNames = (array) $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);

        [, $placeholders, $values, $params] = $this->prepareInsertValues($table, $insertColumns, $params);

        if (!empty($placeholders)) {
            $usingSelectValues = [];

            foreach ($insertNames as $index => $name) {
                $usingSelectValues[$name] = new Expression($placeholders[$index]);
            }

            $values = $this->queryBuilder->buildSelect($usingSelectValues, $params)
                . ' ' . $this->queryBuilder->buildFrom(['DUAL'], $params);
        }

        $insertValues = [];
        $mergeSql = 'MERGE INTO ' . $quotedTableName . ' USING (' . $values . ') "EXCLUDED" ON (' . $on . ')';

        foreach ($insertNames as $quotedName) {
            $insertValues[] = '"EXCLUDED".' . $quotedName;
        }

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

        if ($updateColumns === false || $updateNames === []) {

            /** there are no columns to update */
            return "$mergeSql WHEN NOT MATCHED THEN $insertSql";
        }

        if ($updateColumns === true) {

            $updateColumns = [];
            /** @psalm-var string[] $updateNames */
            foreach ($updateNames as $quotedName) {
                $updateColumns[$quotedName] = new Expression('"EXCLUDED".' . $quotedName);
            }
        }

        [$updates, $params] = $this->prepareUpdateSets($table, $updateColumns, $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
    {
        if (empty($columns)) {
            $names = [];
            $placeholders = [];
            $tableSchema = $this->schema->getTableSchema($table);

            if ($tableSchema !== null) {
                if (!empty($tableSchema->getPrimaryKey())) {
                    $columns = $tableSchema->getPrimaryKey();
                } else {
                    $columns = [current($tableSchema->getColumns())->getName()];
                }

                foreach ($columns as $name) {
                    $names[] = $this->quoter->quoteColumnName($name);
                    $placeholders[] = 'DEFAULT';
                }
            }

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

        return parent::prepareInsertValues($table, $columns, $params);
    }

    public function resetSequence(string $table, int|string $value = null): string
    {
        $tableSchema = $this->schema->getTableSchema($table);

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

        $sequenceName = $tableSchema->getSequenceName();

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

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

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

yiisoft / db-oracle

Pull Request — master (#233)

DMLQueryBuilder::upsert() B

Complexity

Size

Duplication

Code Coverage

Importance

How to fix Long Method Complexity

Long Method

Duplication Side-by-Side

Filter issues like