DMLQueryBuilder::insertEx() - Code Metrics - Inspection of "Removed "as array" and PdoValue behaviour from bin..." - yiisoft/db-mssql - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Push — master ( 9b125d...f5b0c0 )

by Alexander

created 2022-06-19 16:22 UTC

DMLQueryBuilder::insertEx() A

↳ Parent: DMLQueryBuilder

Complexity

Conditions	6
Paths	20

Size

Total Lines	29
Code Lines	19

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	16
CRAP Score	6

Importance

Changes

Metric	Value
cc	6
eloc	19
c	0
b	0
f	0
nc	20
nop	3
dl	0
loc	29
ccs	16
cts	16
cp	1
crap	6
rs	9.0111

<?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\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 function implode;
use function in_array;
use function ltrim;
use function strrpos;
use function is_array;
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);
    }

    /**
     * @throws Exception|InvalidArgumentException|InvalidConfigException|NotSupportedException
     */
    public function insertEx(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);

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

        $cols = [];
        $tableSchema = $this->schema->getTableSchema($table);
        $returnColumns = $tableSchema?->getColumns() ?? [];
        foreach ($returnColumns as $returnColumn) {
            $cols[] = $this->quoter->quoteColumnName($returnColumn->getName()) . ' '
                . $returnColumn->getDbType()
                . (in_array(
                    $returnColumn->getDbType(),
                    ['char', 'varchar', 'nchar', 'nvarchar', 'binary', 'varbinary']
                ) ? '(MAX)' : '')
                . ' ' . ($returnColumn->isAllowNull() ? 'NULL' : '');
        }

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

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

        if ($table !== null && $table->getSequenceName() !== null) {
            $tableName = $this->quoter->quoteTableName($tableName);

            if ($value === null) {
                $pk = $table->getPrimaryKey();
                $key = $this->quoter->quoteColumnName(reset($pk));
                $value = "(SELECT COALESCE(MAX($key),0) FROM $tableName)+1";
            } else {
                $value = (int)$value;
            }

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

        throw new InvalidArgumentException("There is not sequence associated with table '$tableName'.");
    }

    /**
     * @throws Exception|InvalidArgumentException|InvalidConfigException|JsonException|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 ($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\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
21		use function implode;
22		use function in_array;
23		use function ltrim;
24		use function strrpos;
25		use function is_array;
26		use function reset;
27
28		final class DMLQueryBuilder extends AbstractDMLQueryBuilder
29		{
30	386	public function __construct(
31		private QueryBuilderInterface $queryBuilder,
32		private QuoterInterface $quoter,
33		private SchemaInterface $schema
34		) {
35	386	parent::__construct($queryBuilder, $quoter, $schema);
36		}
37
38		/**
39		* @throws Exception\|InvalidArgumentException\|InvalidConfigException\|NotSupportedException
40		*/
41	6	public function insertEx(string $table, QueryInterface\|array $columns, array &$params = []): string
42		{
43		/**
44		* @psalm-var string[] $names
45		* @psalm-var string[] $placeholders
46		*/
47	6	[$names, $placeholders, $values, $params] = $this->prepareInsertValues($table, $columns, $params);
48
49	6	$sql = 'INSERT INTO '
50	6	. $this->quoter->quoteTableName($table)
51	6	. (!empty($names) ? ' (' . implode(', ', $names) . ')' : '')
52		. ' OUTPUT INSERTED.* INTO @temporary_inserted'
53	6	. (!empty($placeholders) ? ' VALUES (' . implode(', ', $placeholders) . ')' : (string) $values);
54
55	6	$cols = [];
56	6	$tableSchema = $this->schema->getTableSchema($table);
57	6	$returnColumns = $tableSchema?->getColumns() ?? [];
58	6	foreach ($returnColumns as $returnColumn) {
59	6	$cols[] = $this->quoter->quoteColumnName($returnColumn->getName()) . ' '
60	6	. $returnColumn->getDbType()
61		. (in_array(
62	6	$returnColumn->getDbType(),
63		['char', 'varchar', 'nchar', 'nvarchar', 'binary', 'varbinary']
64	6	) ? '(MAX)' : '')
65	6	. ' ' . ($returnColumn->isAllowNull() ? 'NULL' : '');
66		}
67
68	6	return 'SET NOCOUNT ON;DECLARE @temporary_inserted TABLE (' . implode(', ', $cols) . ');'
69		. $sql . ';SELECT * FROM @temporary_inserted';
70		}
71
72		/**
73		* @throws InvalidArgumentException
74		*/
75	2	public function resetSequence(string $tableName, mixed $value = null): string
76		{
77	2	$table = $this->schema->getTableSchema($tableName);
78
79	2	if ($table !== null && $table->getSequenceName() !== null) {
80	1	$tableName = $this->quoter->quoteTableName($tableName);
81
82	1	if ($value === null) {
83	1	$pk = $table->getPrimaryKey();
84	1	$key = $this->quoter->quoteColumnName(reset($pk));
85	1	$value = "(SELECT COALESCE(MAX($key),0) FROM $tableName)+1";
86		} else {
87	1	$value = (int)$value;
88		}
89
90	1	return "DBCC CHECKIDENT ('$tableName', RESEED, $value)";
91		}
92
93	1	throw new InvalidArgumentException("There is not sequence associated with table '$tableName'.");
94		}
95
96		/**
97		* @throws Exception\|InvalidArgumentException\|InvalidConfigException\|JsonException\|NotSupportedException
98		*/
99	19	public function upsert(
100		string $table,
101		QueryInterface\|array $insertColumns,
102		bool\|array $updateColumns,
103		array &$params = []
104		): string {
105		/** @psalm-var Constraint[] $constraints */
106	19	$constraints = [];
107
108		/** @psalm-var string[] $insertNames */
109	19	[$uniqueNames, $insertNames, $updateNames] = $this->prepareUpsertColumns(
110		$table,
111		$insertColumns,
112		$updateColumns,
113		$constraints
114		);
115
116	19	if (empty($uniqueNames)) {
117	3	return $this->insert($table, $insertColumns, $params);
118		}
119
120	16	$onCondition = ['or'];
121	16	$quotedTableName = $this->quoter->quoteTableName($table);
122
123	16	foreach ($constraints as $constraint) {
124	16	$constraintCondition = ['and'];
125
126	16	$columnNames = $constraint->getColumnNames() ?? [];
127
128	16	if (is_array($columnNames)) {
129		/** @psalm-var string[] $columnNames */
130	16	foreach ($columnNames as $name) {
131	16	$quotedName = $this->quoter->quoteColumnName($name);
132	16	$constraintCondition[] = "$quotedTableName.$quotedName=[EXCLUDED].$quotedName";
133		}
134		}
135
136	16	$onCondition[] = $constraintCondition;
137		}
138
139	16	$on = $this->queryBuilder->buildCondition($onCondition, $params);
140
141		/** @psalm-var string[] $placeholders */
142	16	[, $placeholders, $values, $params] = $this->prepareInsertValues($table, $insertColumns, $params);
143	16	$mergeSql = 'MERGE ' . $this->quoter->quoteTableName($table) . ' WITH (HOLDLOCK) '
144	16	. 'USING (' . (!empty($placeholders)
145	8	? 'VALUES (' . implode(', ', $placeholders) . ')'
146	16	: ltrim((string) $values, ' ')) . ') AS [EXCLUDED] (' . implode(', ', $insertNames) . ') ' . "ON ($on)";
147	16	$insertValues = [];
148
149	16	foreach ($insertNames as $name) {
150	16	$quotedName = $this->quoter->quoteColumnName($name);
151
152	16	if (strrpos($quotedName, '.') === false) {
153	16	$quotedName = '[EXCLUDED].' . $quotedName;
154		}
155
156	16	$insertValues[] = $quotedName;
157		}
158
159	16	$insertSql = 'INSERT (' . implode(', ', $insertNames) . ')' . ' VALUES (' . implode(', ', $insertValues) . ')';
160
161	16	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...
162	5	return "$mergeSql WHEN NOT MATCHED THEN $insertSql;";
163		}
164
165	11	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...
166	4	$updateColumns = [];
167
168		/** @psalm-var string[] $updateNames */
169	4	foreach ($updateNames as $name) {
170	4	$quotedName = $this->quoter->quoteColumnName($name);
171	4	if (strrpos($quotedName, '.') === false) {
172	4	$quotedName = '[EXCLUDED].' . $quotedName;
173		}
174
175	4	$updateColumns[$name] = new Expression($quotedName);
176		}
177		}
178
179		/**
180		* @var array $params
181		* @psalm-var string[] $updates
182		* @psalm-var array<string, ExpressionInterface\|string> $updateColumns
183		*/
184	11	[$updates, $params] = $this->prepareUpdateSets($table, $updateColumns, $params);
185	11	$updateSql = 'UPDATE SET ' . implode(', ', $updates);
186
187	11	return "$mergeSql WHEN MATCHED THEN $updateSql WHEN NOT MATCHED THEN $insertSql;";
188		}
189		}
190

yiisoft / db-mssql

Push — master ( 9b125d...f5b0c0 )

DMLQueryBuilder::insertEx() A

Complexity

Size

Duplication

Code Coverage

Importance

Duplication Side-by-Side

Filter issues like