DMLQueryBuilder::prepareInsertValues() - Code Metrics - Inspection of "Psalm fix (#79)" - yiisoft/db-oracle - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Push — master ( c77b03...911b01 )

by Alexander

created 2022-06-19 16:24 UTC

DMLQueryBuilder::prepareInsertValues() A

↳ Parent: DMLQueryBuilder

Complexity

Conditions	6
Paths	4

Size

Total Lines	24
Code Lines	11

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	4
CRAP Score	16.6682

Importance

Changes

Metric	Value
cc	6
eloc	11
c	0
b	0
f	0
nc	4
nop	3
dl	0
loc	24
ccs	4
cts	12
cp	0.3333
crap	16.6682
rs	9.2222

<?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 Yiisoft\Strings\NumericHelper;

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

        $schema = $this->schema;

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

        $values = [];

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

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

                /** @var mixed */
                $vs[] = $value;
            }

            /** @psalm-var string[] $vs */
            $values[] = '(' . implode(', ', $vs) . ')';
        }

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

        /** @psalm-var string[] $columns */
        foreach ($columns as $i => $name) {
            $columns[$i] = $this->quoter->quoteColumnName($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
     *
     * @param string $table
     * @param array|QueryInterface $insertColumns
     * @param array|bool $updateColumns
     * @param array $params
     *
     * @throws Exception|InvalidArgumentException|InvalidConfigException|JsonException|NotSupportedException
     *
     * @return string
     */
    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) {
                    /** @var mixed */
                    $names[] = $this->quoter->quoteColumnName($name);
                    $placeholders[] = 'DEFAULT';
                }
            }
        }

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

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

        if ($tableSchema === null) {
            throw new InvalidArgumentException("Unknown table: $tableName");
        }

        $sequenceName = $tableSchema->getSequenceName();
        if ($sequenceName === null) {
            throw new InvalidArgumentException("There is no sequence associated with table: $tableName");
        }

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

yiisoft / db-oracle

Push — master ( c77b03...911b01 )

DMLQueryBuilder::prepareInsertValues() A

Complexity

Size

Duplication

Code Coverage

Importance

Duplication Side-by-Side

Filter issues like