DMLQueryBuilder::upsert() - Code Metrics - Inspection of "big refactoring." - yiisoft/db-mssql - Measure and Improve Code Quality continuously with Scrutinizer

Test Failed

Pull Request — master (#66)

by Wilmer

created 2022-02-12 11:58 UTC

DMLQueryBuilder::upsert() B

↳ Parent: DMLQueryBuilder

Complexity

Conditions	11
Paths	55

Size

Total Lines	74
Code Lines	41

Duplication

Lines	0
Ratio	0 %

Importance

Changes	1
Bugs	0	Features	0

Metric	Value
cc	11
eloc	41
c	1
b	0
f	0
nc	55
nop	4
dl	0
loc	74
rs	7.3166

How to fix Long Method Complexity

<?php

declare(strict_types=1);

namespace Yiisoft\Db\Mssql;

use InvalidArgumentException;
use JsonException;
use Yiisoft\Db\Constraint\Constraint;
use Yiisoft\Db\Exception\Exception;
use Yiisoft\Db\Exception\InvalidConfigException;
use Yiisoft\Db\Exception\NotSupportedException;
use Yiisoft\Db\Expression\Expression;
use Yiisoft\Db\Query\DMLQueryBuilder as AbstractDMLQueryBuilder;
filter:
    dependency_paths: ["lib/*"]
use Yiisoft\Db\Query\Query;
use Yiisoft\Db\Query\QueryBuilderInterface;
filter:
    dependency_paths: ["lib/*"]

final class DMLQueryBuilder extends AbstractDMLQueryBuilder
{
    public function __construct(private QueryBuilderInterface $queryBuilder)
    {
        parent::__construct($queryBuilder);
    }

    /**
     * @throws Exception|\Yiisoft\Db\Exception\InvalidArgumentException|InvalidConfigException|NotSupportedException
     */
    public function insert(string $table, Query|array $columns, array &$params = []): string
    {
        [$names, $placeholders, $values, $params] = $this->queryBuilder->prepareInsertValues($table, $columns, $params);

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

        $schema = $this->queryBuilder->schema()->getTableSchema($table);
        $cols = [];

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

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

    public function resetSequence(string $tableName, $value = null): string
    {
        $table = $this->queryBuilder->schema()->getTableSchema($tableName);

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

            if ($value === null) {
                $pk = $table->getPrimaryKey();
                $key = $this->queryBuilder->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|JsonException|\Yiisoft\Db\Exception\InvalidArgumentException|InvalidConfigException|NotSupportedException
     */
    public function upsert(
        string $table,
        Query|array $insertColumns,
        bool|array $updateColumns,
        array &$params = []
    ): string {
        /** @var Constraint[] $constraints */
        $constraints = [];
        [$uniqueNames, $insertNames, $updateNames] = $this->queryBuilder->prepareUpsertColumns(
            $table,
            $insertColumns,
            $updateColumns,
            $constraints
        );

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

        $onCondition = ['or'];
        $quotedTableName = $this->queryBuilder->quoter()->quoteTableName($table);

        foreach ($constraints as $constraint) {
            $constraintCondition = ['and'];

            foreach ($constraint->getColumnNames() as $name) {
                $quotedName = $this->queryBuilder->quoter()->quoteColumnName($name);
                $constraintCondition[] = "$quotedTableName.$quotedName=[EXCLUDED].$quotedName";
            }

            $onCondition[] = $constraintCondition;
        }

        $on = $this->queryBuilder->buildCondition($onCondition, $params);
        [, $placeholders, $values, $params] = $this->queryBuilder->prepareInsertValues($table, $insertColumns, $params);
        $mergeSql = 'MERGE ' . $this->queryBuilder->quoter()->quoteTableName($table) . ' WITH (HOLDLOCK) '
            . 'USING (' . (!empty($placeholders)
            ? 'VALUES (' . implode(', ', $placeholders) . ')'
            : ltrim($values, ' ')) . ') AS [EXCLUDED] (' . implode(', ', $insertNames) . ') ' . "ON ($on)";
        $insertValues = [];

        foreach ($insertNames as $name) {
            $quotedName = $this->queryBuilder->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 = [];

            foreach ($updateNames as $name) {
                $quotedName = $this->queryBuilder->quoter()->quoteColumnName($name);
                if (strrpos($quotedName, '.') === false) {
                    $quotedName = '[EXCLUDED].' . $quotedName;
                }

                $updateColumns[$name] = new Expression($quotedName);
            }
        }

        [$updates, $params] = $this->queryBuilder->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 InvalidArgumentException;
8			use JsonException;
9			use Yiisoft\Db\Constraint\Constraint;
10			use Yiisoft\Db\Exception\Exception;
11			use Yiisoft\Db\Exception\InvalidConfigException;
12			use Yiisoft\Db\Exception\NotSupportedException;
13			use Yiisoft\Db\Expression\Expression;
14			use Yiisoft\Db\Query\DMLQueryBuilder as AbstractDMLQueryBuilder;
			0 ignored issues – show Bug introduced 2022-02-12 12:16 UTC by Report Bug Copy Issue Report The type `Yiisoft\Db\Query\DMLQueryBuilder` was not found. Maybe you did not declare it correctly or list all dependencies? The issue could also be caused by a filter entry in the build configuration. If the path has been excluded in your configuration, e.g. `excluded_paths: ["lib/"]`, you can move it to the dependency path list as follows: filter: dependency_paths: ["lib/"] For further information see https://scrutinizer-ci.com/docs/tools/php/php-scrutinizer/#list-dependency-paths Loading history...
15			use Yiisoft\Db\Query\Query;
16			use Yiisoft\Db\Query\QueryBuilderInterface;
			0 ignored issues – show Bug introduced 2022-02-12 12:16 UTC by Report Bug Copy Issue Report The type `Yiisoft\Db\Query\QueryBuilderInterface` was not found. Maybe you did not declare it correctly or list all dependencies? The issue could also be caused by a filter entry in the build configuration. If the path has been excluded in your configuration, e.g. `excluded_paths: ["lib/"]`, you can move it to the dependency path list as follows: filter: dependency_paths: ["lib/"] For further information see https://scrutinizer-ci.com/docs/tools/php/php-scrutinizer/#list-dependency-paths Loading history...
17
18			final class DMLQueryBuilder extends AbstractDMLQueryBuilder
19			{
20			public function __construct(private QueryBuilderInterface $queryBuilder)
21			{
22			parent::__construct($queryBuilder);
23			}
24
25			/**
26			* @throws Exception\|\Yiisoft\Db\Exception\InvalidArgumentException\|InvalidConfigException\|NotSupportedException
27			*/
28			public function insert(string $table, Query\|array $columns, array &$params = []): string
29			{
30			[$names, $placeholders, $values, $params] = $this->queryBuilder->prepareInsertValues($table, $columns, $params);
31
32			$sql = 'INSERT INTO '
33			. $this->queryBuilder->quoter()->quoteTableName($table)
34			. (!empty($names) ? ' (' . implode(', ', $names) . ')' : '')
35			. ' OUTPUT INSERTED.* INTO @temporary_inserted'
36			. (!empty($placeholders) ? ' VALUES (' . implode(', ', $placeholders) . ')' : $values);
37
38			$schema = $this->queryBuilder->schema()->getTableSchema($table);
39			$cols = [];
40
41			foreach ($schema->getColumns() as $column) {
42			$cols[] = $this->queryBuilder->quoter()->quoteColumnName($column->getName()) . ' '
43			. $column->getDbType()
44			. (in_array(
45			$column->getDbType(),
46			['char', 'varchar', 'nchar', 'nvarchar', 'binary', 'varbinary']
47			) ? '(MAX)' : '')
48			. ' ' . ($column->isAllowNull() ? 'NULL' : '');
49			}
50
51			return 'SET NOCOUNT ON;DECLARE @temporary_inserted TABLE (' . implode(', ', $cols) . ');'
52			. $sql . ';SELECT * FROM @temporary_inserted';
53			}
54
55			public function resetSequence(string $tableName, $value = null): string
56			{
57			$table = $this->queryBuilder->schema()->getTableSchema($tableName);
58
59			if ($table !== null && $table->getSequenceName() !== null) {
60			$tableName = $this->queryBuilder->quoter()->quoteTableName($tableName);
61
62			if ($value === null) {
63			$pk = $table->getPrimaryKey();
64			$key = $this->queryBuilder->quoter()->quoteColumnName(reset($pk));
65			$value = "(SELECT COALESCE(MAX($key),0) FROM $tableName)+1";
66			} else {
67			$value = (int)$value;
68			}
69
70			return "DBCC CHECKIDENT ('$tableName', RESEED, $value)";
71			}
72
73			throw new InvalidArgumentException("There is not sequence associated with table '$tableName'.");
74			}
75
76			/**
77			* @throws Exception\|JsonException\|\Yiisoft\Db\Exception\InvalidArgumentException\|InvalidConfigException\|NotSupportedException
78			*/
79			public function upsert(
80			string $table,
81			Query\|array $insertColumns,
82			bool\|array $updateColumns,
83			array &$params = []
84			): string {
85			/** @var Constraint[] $constraints */
86			$constraints = [];
87			[$uniqueNames, $insertNames, $updateNames] = $this->queryBuilder->prepareUpsertColumns(
88			$table,
89			$insertColumns,
90			$updateColumns,
91			$constraints
92			);
93
94			if (empty($uniqueNames)) {
95			return $this->insert($table, $insertColumns, $params);
96			}
97
98			$onCondition = ['or'];
99			$quotedTableName = $this->queryBuilder->quoter()->quoteTableName($table);
100
101			foreach ($constraints as $constraint) {
102			$constraintCondition = ['and'];
103
104			foreach ($constraint->getColumnNames() as $name) {
105			$quotedName = $this->queryBuilder->quoter()->quoteColumnName($name);
106			$constraintCondition[] = "$quotedTableName.$quotedName=[EXCLUDED].$quotedName";
107			}
108
109			$onCondition[] = $constraintCondition;
110			}
111
112			$on = $this->queryBuilder->buildCondition($onCondition, $params);
113			[, $placeholders, $values, $params] = $this->queryBuilder->prepareInsertValues($table, $insertColumns, $params);
114			$mergeSql = 'MERGE ' . $this->queryBuilder->quoter()->quoteTableName($table) . ' WITH (HOLDLOCK) '
115			. 'USING (' . (!empty($placeholders)
116			? 'VALUES (' . implode(', ', $placeholders) . ')'
117			: ltrim($values, ' ')) . ') AS [EXCLUDED] (' . implode(', ', $insertNames) . ') ' . "ON ($on)";
118			$insertValues = [];
119
120			foreach ($insertNames as $name) {
121			$quotedName = $this->queryBuilder->quoter()->quoteColumnName($name);
122
123			if (strrpos($quotedName, '.') === false) {
124			$quotedName = '[EXCLUDED].' . $quotedName;
125			}
126
127			$insertValues[] = $quotedName;
128			}
129
130			$insertSql = 'INSERT (' . implode(', ', $insertNames) . ')' . ' VALUES (' . implode(', ', $insertValues) . ')';
131
132			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...
133			return "$mergeSql WHEN NOT MATCHED THEN $insertSql;";
134			}
135
136			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...
137			$updateColumns = [];
138
139			foreach ($updateNames as $name) {
140			$quotedName = $this->queryBuilder->quoter()->quoteColumnName($name);
141			if (strrpos($quotedName, '.') === false) {
142			$quotedName = '[EXCLUDED].' . $quotedName;
143			}
144
145			$updateColumns[$name] = new Expression($quotedName);
146			}
147			}
148
149			[$updates, $params] = $this->queryBuilder->prepareUpdateSets($table, $updateColumns, $params);
150			$updateSql = 'UPDATE SET ' . implode(', ', $updates);
151
152			return "$mergeSql WHEN MATCHED THEN $updateSql WHEN NOT MATCHED THEN $insertSql;";
153			}
154			}
155

yiisoft / db-mssql

Pull Request — master (#66)

DMLQueryBuilder::upsert() B

Complexity

Size

Duplication

Importance

How to fix Long Method Complexity

Long Method

Duplication Side-by-Side

Filter issues like