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

DMLQueryBuilder::upsert() B
last analyzed 2024-04-23 09:57 UTC

↳ Parent: DMLQueryBuilder

Complexity

Conditions	10
Paths	37

Size

Total Lines	70
Code Lines	35

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	37
CRAP Score	10

Importance

Changes	1
Bugs	0	Features	0

Metric	Value
cc	10
eloc	35
c	1
b	0
f	0
nc	37
nop	4
dl	0
loc	70
ccs	37
cts	37
cp	1
crap	10
rs	7.6666

How to fix Long Method Complexity

<?php

declare(strict_types=1);

namespace Yiisoft\Db\Mssql;

use JsonException;
use Yiisoft\Db\Constraint\Constraint;
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_flip;
use function array_intersect_key;
use function implode;
use function in_array;

/**
 * Implements a DML (Data Manipulation Language) SQL statements for MSSQL Server.
 */
final class DMLQueryBuilder extends AbstractDMLQueryBuilder
{
    /**
     * @throws Exception
     * @throws InvalidArgumentException
     * @throws InvalidConfigException
     * @throws NotSupportedException
     */
    public function insertWithReturningPks(string $table, QueryInterface|array $columns, array &$params = []): string
    {
        $tableSchema = $this->schema->getTableSchema($table);
        $primaryKeys = $tableSchema?->getPrimaryKey();

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

        $createdCols = [];
        $insertedCols = [];
        $returnColumns = array_intersect_key($tableSchema?->getColumns() ?? [], array_flip($primaryKeys));

        foreach ($returnColumns as $returnColumn) {
            $dbType = $returnColumn->getDbType();

            if (in_array($dbType, ['char', 'varchar', 'nchar', 'nvarchar', 'binary', 'varbinary'], true)) {
                $dbType .= '(MAX)';
            } elseif ($dbType === 'timestamp') {
                $dbType = $returnColumn->isAllowNull() ? 'varbinary(8)' : 'binary(8)';
            }

            $quotedName = $this->quoter->quoteColumnName($returnColumn->getName());
            $createdCols[] = $quotedName . ' ' . (string) $dbType . ' ' . ($returnColumn->isAllowNull() ? 'NULL' : '');
            $insertedCols[] = 'INSERTED.' . $quotedName;
        }

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

        $sql = 'INSERT INTO ' . $this->quoter->quoteTableName($table)
            . (!empty($names) ? ' (' . implode(', ', $names) . ')' : '')
            . ' OUTPUT ' . implode(',', $insertedCols) . ' INTO @temporary_inserted'
            . (!empty($placeholders) ? ' VALUES (' . implode(', ', $placeholders) . ')' : ' ' . $values);

        return 'SET NOCOUNT ON;DECLARE @temporary_inserted TABLE (' . implode(', ', $createdCols) . ');'
            . $sql . ';SELECT * FROM @temporary_inserted;';
    }

    /**
     * @throws InvalidArgumentException
     */
    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'.'");
        }

        $tableName = $this->quoter->quoteTableName($table);

        if ($value === null) {
            return "DBCC CHECKIDENT ('$tableName', RESEED, 0) WITH NO_INFOMSGS;DBCC CHECKIDENT ('$tableName', RESEED)";
        }

        return "DBCC CHECKIDENT ('$tableName', RESEED, $value)";
    }

    /**
     * @throws Exception
     * @throws InvalidArgumentException
     * @throws InvalidConfigException
     * @throws JsonException
     * @throws NotSupportedException
     */
    public function upsert(
        string $table,
        QueryInterface|array $insertColumns,
        bool|array $updateColumns,
        array &$params = []
    ): string {
        /** @psalm-var Constraint[] $constraints */
        $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) {
            $constraintCondition = ['and'];
            $columnNames = (array) $constraint->getColumnNames();

            /** @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);

        $mergeSql = 'MERGE ' . $quotedTableName . ' WITH (HOLDLOCK) USING ('
            . (!empty($placeholders) ? 'VALUES (' . implode(', ', $placeholders) . ')' : $values)
            . ') AS [EXCLUDED] (' . implode(', ', $insertNames) . ') ' . "ON ($on)";

        $insertValues = [];

        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);

        return "$mergeSql WHEN MATCHED THEN UPDATE SET " . implode(', ', $updates)
            . " WHEN NOT MATCHED THEN $insertSql;";
    }
}


1		<?php
2
3		declare(strict_types=1);
4
5		namespace Yiisoft\Db\Mssql;
6
7		use JsonException;
8		use Yiisoft\Db\Constraint\Constraint;
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\Query\QueryInterface;
15		use Yiisoft\Db\QueryBuilder\AbstractDMLQueryBuilder;
16
17		use function array_flip;
18		use function array_intersect_key;
19		use function implode;
20		use function in_array;
21
22		/**
23		* Implements a DML (Data Manipulation Language) SQL statements for MSSQL Server.
24		*/
25		final class DMLQueryBuilder extends AbstractDMLQueryBuilder
26		{
27		/**
28		* @throws Exception
29		* @throws InvalidArgumentException
30		* @throws InvalidConfigException
31		* @throws NotSupportedException
32		*/
33	14	public function insertWithReturningPks(string $table, QueryInterface\|array $columns, array &$params = []): string
34		{
35	14	$tableSchema = $this->schema->getTableSchema($table);
36	14	$primaryKeys = $tableSchema?->getPrimaryKey();
37
38	14	if (empty($primaryKeys)) {
39	1	return $this->insert($table, $columns, $params);
40		}
41
42	13	$createdCols = [];
43	13	$insertedCols = [];
44	13	$returnColumns = array_intersect_key($tableSchema?->getColumns() ?? [], array_flip($primaryKeys));
45
46	13	foreach ($returnColumns as $returnColumn) {
47	13	$dbType = $returnColumn->getDbType();
48
49	13	if (in_array($dbType, ['char', 'varchar', 'nchar', 'nvarchar', 'binary', 'varbinary'], true)) {
50		$dbType .= '(MAX)';
51	13	} elseif ($dbType === 'timestamp') {
52		$dbType = $returnColumn->isAllowNull() ? 'varbinary(8)' : 'binary(8)';
53		}
54
55	13	$quotedName = $this->quoter->quoteColumnName($returnColumn->getName());
56	13	$createdCols[] = $quotedName . ' ' . (string) $dbType . ' ' . ($returnColumn->isAllowNull() ? 'NULL' : '');
57	13	$insertedCols[] = 'INSERTED.' . $quotedName;
58		}
59
60	13	[$names, $placeholders, $values, $params] = $this->prepareInsertValues($table, $columns, $params);
61
62	13	$sql = 'INSERT INTO ' . $this->quoter->quoteTableName($table)
63	13	. (!empty($names) ? ' (' . implode(', ', $names) . ')' : '')
64	13	. ' OUTPUT ' . implode(',', $insertedCols) . ' INTO @temporary_inserted'
65	13	. (!empty($placeholders) ? ' VALUES (' . implode(', ', $placeholders) . ')' : ' ' . $values);
66
67	13	return 'SET NOCOUNT ON;DECLARE @temporary_inserted TABLE (' . implode(', ', $createdCols) . ');'
68	13	. $sql . ';SELECT * FROM @temporary_inserted;';
69		}
70
71		/**
72		* @throws InvalidArgumentException
73		*/
74	5	public function resetSequence(string $table, int\|string $value = null): string
75		{
76	5	$tableSchema = $this->schema->getTableSchema($table);
77
78	5	if ($tableSchema === null) {
79	1	throw new InvalidArgumentException("Table not found: '$table'.");
80		}
81
82	4	$sequenceName = $tableSchema->getSequenceName();
83
84	4	if ($sequenceName === null) {
85	1	throw new InvalidArgumentException("There is not sequence associated with table '$table'.'");
86		}
87
88	3	$tableName = $this->quoter->quoteTableName($table);
89
90	3	if ($value === null) {
91	3	return "DBCC CHECKIDENT ('$tableName', RESEED, 0) WITH NO_INFOMSGS;DBCC CHECKIDENT ('$tableName', RESEED)";
92		}
93
94	1	return "DBCC CHECKIDENT ('$tableName', RESEED, $value)";
95		}
96
97		/**
98		* @throws Exception
99		* @throws InvalidArgumentException
100		* @throws InvalidConfigException
101		* @throws JsonException
102		* @throws NotSupportedException
103		*/
104	37	public function upsert(
105		string $table,
106		QueryInterface\|array $insertColumns,
107		bool\|array $updateColumns,
108		array &$params = []
109		): string {
110		/** @psalm-var Constraint[] $constraints */
111	37	$constraints = [];
112
113	37	[$uniqueNames, $insertNames, $updateNames] = $this->prepareUpsertColumns(
114	37	$table,
115	37	$insertColumns,
116	37	$updateColumns,
117	37	$constraints
118	37	);
119
120	37	if (empty($uniqueNames)) {
121	2	return $this->insert($table, $insertColumns, $params);
122		}
123
124	35	$onCondition = ['or'];
125	35	$quotedTableName = $this->quoter->quoteTableName($table);
126
127	35	foreach ($constraints as $constraint) {
128	35	$constraintCondition = ['and'];
129	35	$columnNames = (array) $constraint->getColumnNames();
130
131		/** @psalm-var string[] $columnNames */
132	35	foreach ($columnNames as $name) {
133	35	$quotedName = $this->quoter->quoteColumnName($name);
134	35	$constraintCondition[] = "$quotedTableName.$quotedName=[EXCLUDED].$quotedName";
135		}
136
137	35	$onCondition[] = $constraintCondition;
138		}
139
140	35	$on = $this->queryBuilder->buildCondition($onCondition, $params);
141
142	35	[, $placeholders, $values, $params] = $this->prepareInsertValues($table, $insertColumns, $params);
143
144	35	$mergeSql = 'MERGE ' . $quotedTableName . ' WITH (HOLDLOCK) USING ('
145	35	. (!empty($placeholders) ? 'VALUES (' . implode(', ', $placeholders) . ')' : $values)
146	35	. ') AS [EXCLUDED] (' . implode(', ', $insertNames) . ') ' . "ON ($on)";
147
148	35	$insertValues = [];
149
150	35	foreach ($insertNames as $quotedName) {
151	35	$insertValues[] = '[EXCLUDED].' . $quotedName;
152		}
153
154	35	$insertSql = 'INSERT (' . implode(', ', $insertNames) . ') VALUES (' . implode(', ', $insertValues) . ')';
155
156	35	if ($updateColumns === false \|\| $updateNames === []) {
		0 ignored issues – show introduced 2022-12-28 07:05 UTC by Report Bug Copy Issue Report The condition `$updateNames === array()` is always `false`. Loading history...
157		/** there are no columns to update */
158	14	return "$mergeSql WHEN NOT MATCHED THEN $insertSql;";
159		}
160
161	21	if ($updateColumns === true) {
		0 ignored issues – show introduced 2022-02-12 12:16 UTC by Report Bug Copy Issue Report The condition `$updateColumns === true` is always `false`. Loading history...
162	10	$updateColumns = [];
163
164		/** @psalm-var string[] $updateNames */
165	10	foreach ($updateNames as $quotedName) {
166	10	$updateColumns[$quotedName] = new Expression('[EXCLUDED].' . $quotedName);
167		}
168		}
169
170	21	[$updates, $params] = $this->prepareUpdateSets($table, $updateColumns, $params);
171
172	21	return "$mergeSql WHEN MATCHED THEN UPDATE SET " . implode(', ', $updates)
173	21	. " WHEN NOT MATCHED THEN $insertSql;";
174		}
175		}
176

yiisoft / db-mssql

DMLQueryBuilder::upsert() B last analyzed 2024-04-23 09:57 UTC

Complexity

Size

Duplication

Code Coverage

Importance

How to fix Long Method Complexity

Long Method

Duplication Side-by-Side

Filter issues like

DMLQueryBuilder::upsert() B
last analyzed 2024-04-23 09:57 UTC