DMLQueryBuilder::prepareInsertValues() - Code Metrics - yiisoft/db-oracle - Measure and Improve Code Quality continuously with Scrutinizer

DMLQueryBuilder::prepareInsertValues() A
last analyzed 2024-04-27 02:31 UTC

↳ Parent: DMLQueryBuilder

Complexity

Conditions	5
Paths	4

Size

Total Lines	24
Code Lines	14

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	14
CRAP Score	5

Importance

Changes

Metric	Value
cc	5
eloc	14
c	0
b	0
f	0
nc	4
nop	3
dl	0
loc	24
ccs	14
cts	14
cp	1
crap	5
rs	9.4888

<?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\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
{
    /**
     * @throws Exception
     * @throws InvalidArgumentException
     * @throws InvalidConfigException
     * @throws NotSupportedException
     */
    public function batchInsert(string $table, array $columns, iterable $rows, array &$params = []): string
    {
        if (empty($rows)) {
            return '';
        }

        $columns = $this->extractColumnNames($rows, $columns);
        $values = $this->prepareBatchInsertValues($table, $rows, $columns, $params);

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

        $tableAndColumns = ' INTO ' . $this->quoter->quoteTableName($table);

        if (count($columns) > 0) {
            $quotedColumnNames = array_map([$this->quoter, 'quoteColumnName'], $columns);

            $tableAndColumns .= ' (' . implode(', ', $quotedColumnNames) . ')';
        }

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

yiisoft / db-oracle

DMLQueryBuilder::prepareInsertValues() A last analyzed 2024-04-27 02:31 UTC

Complexity

Size

Duplication

Code Coverage

Importance

Duplication Side-by-Side

Filter issues like

DMLQueryBuilder::prepareInsertValues() A
last analyzed 2024-04-27 02:31 UTC