DMLQueryBuilder::insertWithReturningPks() - Code Metrics - Inspection of "Change visibility `AbstractDMLQueryBuilder::class`..." - yiisoft/db-mssql - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Push — master ( 4e20f6...9f7e9c )

by Wilmer

created 2023-03-27 10:29 UTC

DMLQueryBuilder::insertWithReturningPks() B

↳ Parent: DMLQueryBuilder

Complexity

Conditions	9
Paths	56

Size

Total Lines	41
Code Lines	22

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	23
CRAP Score	9

Importance

Changes	1
Bugs	0	Features	0

Metric	Value
cc	9
eloc	22
c	1
b	0
f	0
nc	56
nop	3
dl	0
loc	41
ccs	23
cts	23
cp	1
crap	9
rs	8.0555

<?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 Yiisoft\Db\Schema\SchemaInterface;

use function implode;
use function in_array;
use function ltrim;
use function strrpos;
use function is_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
    {
        /**
         * @psalm-var string[] $names
         * @psalm-var string[] $placeholders
         */
        [$names, $placeholders, $values, $params] = $this->prepareInsertValues($table, $columns, $params);


        $createdCols = $insertedCols = [];
        $tableSchema = $this->schema->getTableSchema($table);
        $returnColumns = $tableSchema?->getColumns() ?? [];
        foreach ($returnColumns as $returnColumn) {
            if ($returnColumn->isComputed()) {
                continue;
            }

            $dbType = $returnColumn->getDbType();

            if (in_array($dbType, ['char', 'varchar', 'nchar', 'nvarchar', 'binary', 'varbinary'], true)) {
                $dbType .= '(MAX)';
            }

            if ($returnColumn->getDbType() === SchemaInterface::TYPE_TIMESTAMP) {
                $dbType = $returnColumn->isAllowNull() ? 'varbinary(8)' : 'binary(8)';
            }

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

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


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

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

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

        $sequenceName = $table->getSequenceName();

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

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

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

        /** @psalm-var string[] $insertNames */
        [$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 = $constraint->getColumnNames() ?? [];

            if (is_array($columnNames)) {
                /** @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);
        $mergeSql = 'MERGE ' . $this->quoter->quoteTableName($table) . ' WITH (HOLDLOCK) '
            . 'USING (' . (!empty($placeholders)
            ? 'VALUES (' . implode(', ', $placeholders) . ')'
            : ltrim((string) $values, ' ')) . ') AS [EXCLUDED] (' . implode(', ', $insertNames) . ') ' . "ON ($on)";
        $insertValues = [];

        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 ($updateNames === []) {

            /** there are no columns to update */
            $updateColumns = false;
        }

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

        /**
         * @var array $params
         *
         * @psalm-var string[] $updates
         * @psalm-var array<string, ExpressionInterface|string> $updateColumns
         */
        [$updates, $params] = $this->prepareUpdateSets($table, $updateColumns, $params);
        $updateSql = 'UPDATE SET ' . implode(', ', $updates);

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

yiisoft / db-mssql

Push — master ( 4e20f6...9f7e9c )

DMLQueryBuilder::insertWithReturningPks() B

Complexity

Size

Duplication

Code Coverage

Importance

Duplication Side-by-Side

Filter issues like