DMLQueryBuilder::update() - Code Metrics - Inspection of "Work with attributes on connection" - yiisoft/db-pgsql - Measure and Improve Code Quality continuously with Scrutinizer

Test Failed

Pull Request — dev (#126)

by Def

created 2022-05-09 15:14 UTC

DMLQueryBuilder::update() A

↳ Parent: DMLQueryBuilder

Complexity

Conditions	2
Paths	1

Size

Total Lines	9
Code Lines	6

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	1
CRAP Score	2

Importance

Changes	1
Bugs	0	Features	0

Metric	Value
cc	2
eloc	6
c	1
b	0
f	0
nc	1
nop	4
dl	0
loc	9
rs	10
ccs	1
cts	1
cp	1
crap	2

<?php

declare(strict_types=1);

namespace Yiisoft\Db\Pgsql;

use Generator;
use JsonException;
use PDO;
use Yiisoft\Db\Driver\PDO\PDOValue;
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\Query\DMLQueryBuilder as AbstractDMLQueryBuilder;
use Yiisoft\Db\Query\Query;
use Yiisoft\Db\Query\QueryBuilderInterface;
use Yiisoft\Db\Query\QueryInterface;
use Yiisoft\Db\Schema\QuoterInterface;
use Yiisoft\Db\Schema\Schema;
.
|-- OtherDir
|   |-- Bar.php
|   `-- Foo.php
`-- SomeDir
    `-- Foo.php
use Yiisoft\Db\Schema\SchemaInterface;
use Yiisoft\Strings\NumericHelper;

use function implode;
use function is_array;
use function is_string;
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);
    }

    public function insertEx(string $table, QueryInterface|array $columns, array &$params = []): string
    {
        $sql = $this->insert($table, $columns, $params);

        $tableSchema = $this->schema->getTableSchema($table);

        $returnColumns = [];
        if ($tableSchema !== null) {
            $returnColumns = $tableSchema->getPrimaryKey();
        }

        if (!empty($returnColumns)) {
            $returning = [];
            foreach ($returnColumns as $name) {
                $returning[] = $this->quoter->quoteColumnName($name);
            }
            $sql .= ' RETURNING ' . implode(', ', $returning);
        }

        return $sql;
    }

    /**
     * @throws Exception|InvalidArgumentException|InvalidConfigException|NotSupportedException
     *
     * @psalm-suppress MixedArrayOffset
     */
    public function batchInsert(string $table, array $columns, iterable|Generator $rows, array &$params = []): string
    {
        if (empty($rows)) {
            return '';
        }

        /**
         * @psalm-var array<array-key, ColumnSchema> $columnSchemas
         */
        $columnSchemas = [];
        $schema = $this->schema;

        if (($tableSchema = $schema->getTableSchema($table)) !== null) {
            $columnSchemas = $tableSchema->getColumns();
        }

        $values = [];

        /**
         * @var array $row
         */
        foreach ($rows as $row) {
            $vs = [];
            /**
             *  @var int $i
             *  @var mixed $value
             */
            foreach ($row as $i => $value) {
                if (isset($columns[$i], $columnSchemas[$columns[$i]])) {
                    /**
                     * @var bool|ExpressionInterface|float|int|string|null $value
                     */
                    $value = $columnSchemas[$columns[$i]]->dbTypecast($value);
                }

                if (is_string($value)) {
                    /** @var mixed */
                    $value = $this->quoter->quoteValue($value);
                } elseif (is_float($value)) {
                    /** ensure type cast always has . as decimal separator in all locales */
                    $value = NumericHelper::normalize((string) $value);
                } elseif ($value === true) {
                    $value = 'TRUE';
                } elseif ($value === false) {
                    $value = 'FALSE';
                } elseif ($value === null) {
                    $value = 'NULL';
                } elseif ($value instanceof ExpressionInterface) {
                    $value = $this->queryBuilder->buildExpression($value, $params);
                }

                /** @var bool|ExpressionInterface|float|int|string|null $value */
                $vs[] = $value;
            }
            $values[] = '(' . implode(', ', $vs) . ')';
        }

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

        /** @var string name */
        foreach ($columns as $i => $name) {
            $columns[$i] = $this->quoter->quoteColumnName($name);
        }

        /**
         * @psalm-var string[] $columns
         * @psalm-var string[] $values
         */
        return 'INSERT INTO '
            . $this->quoter->quoteTableName($table)
            . ' (' . implode(', ', $columns) . ') VALUES ' . implode(', ', $values);
    }

    /**
     * Creates an INSERT SQL statement.
     *
     * For example,
     *
     * ```php
     * $sql = $queryBuilder->insert('user', [
     *     'name' => 'Sam',
     *     'age' => 30,
     * ], $params);
     * ```
     *
     * The method will properly escape the table and column names.
     *
     * @param string $table the table that new rows will be inserted into.
     * @param array|QueryInterface $columns the column data (name => value) to be inserted into the table or instance of
     * {@see Query} to perform INSERT INTO ... SELECT SQL statement. Passing of {@see Query}.
     * @param array $params the binding parameters that will be generated by this method. They should be bound to the
     * DB command later.
     *
     * @throws Exception|InvalidArgumentException|InvalidConfigException|NotSupportedException
     *
     * @return string the INSERT SQL
     *
     * @psalm-suppress UndefinedInterfaceMethod
     * @psalm-suppress MixedArgument
     */
    public function insert(string $table, QueryInterface|array $columns, array &$params = []): string
    {
        return parent::insert($table, $this->normalizeTableRowData($table, $columns), $params);
    }

    /**
     * {@see upsert()} implementation for PostgresSQL 9.5 or higher.
     *
     * @param string $table
     * @param array|QueryInterface $insertColumns
     * @param array|bool|QueryInterface $updateColumns
     * @param array $params
     *
     * @throws Exception|InvalidArgumentException|InvalidConfigException|JsonException|NotSupportedException
     *
     * @return string
     */
    public function newUpsert(
        string $table,
        QueryInterface|array $insertColumns,
        bool|array|QueryInterface $updateColumns,
        array &$params = []
    ): string {
        $insertSql = $this->insert($table, $insertColumns, $params);

        /** @var array $uniqueNames */
        [$uniqueNames, , $updateNames] = $this->prepareUpsertColumns(
            $table,
            $insertColumns,
            $updateColumns,
        );

        if (empty($uniqueNames)) {
            return $insertSql;
        }

        if ($updateNames === []) {

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

        if ($updateColumns === false) {

            return "$insertSql ON CONFLICT DO NOTHING";
        }

        if ($updateColumns === true) {

            $updateColumns = [];

            /** @var string $name */
            foreach ($updateNames as $name) {
                $updateColumns[$name] = new Expression(
                    'EXCLUDED.' . $this->quoter->quoteColumnName($name)
                );
            }
        }

        /**
         * @var array $updateColumns
         *
         * @psalm-var string[] $uniqueNames
         * @psalm-var string[] $updates
         */
        [$updates, $params] = $this->prepareUpdateSets($table, $updateColumns, $params);

        return $insertSql
            . ' ON CONFLICT (' . implode(', ', $uniqueNames) . ') DO UPDATE SET ' . implode(', ', $updates);
    }

    /**
     * Creates a SQL statement for resetting the sequence value of a table's primary key.
     *
     * The sequence will be reset such that the primary key of the next new row inserted will have the specified value
     * or 1.
     *
     * @param string $tableName the name of the table whose primary key sequence will be reset.
     * @param mixed $value the value for the primary key of the next new row inserted. If this is not set, the next new
     * row's primary key will have a value 1.
     *
     * @throws Exception|InvalidArgumentException if the table does not exist or there is no sequence
     * associated with the table.
     *
     * @return string the SQL statement for resetting sequence.
     */
    public function resetSequence(string $tableName, $value = null): string
    {
        $table = $this->schema->getTableSchema($tableName);

        if ($table !== null && ($sequence = $table->getSequenceName()) !== null) {
            /**
             * {@see http://www.postgresql.org/docs/8.1/static/functions-sequence.html}
             */
            $sequence = $this->quoter->quoteTableName($sequence);
            $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 "SELECT SETVAL('$sequence',$value,false)";
        }

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

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

    public function truncateTable(string $table): string
    {
        return 'TRUNCATE TABLE ' . $this->quoter->quoteTableName($table) . ' RESTART IDENTITY';
    }

    /**
     * Creates an UPDATE SQL statement.
     *
     * For example,
     *
     * ```php
     * $params = [];
     * $sql = $queryBuilder->update('user', ['status' => 1], 'age > 30', $params);
     * ```
     *
     * The method will properly escape the table and column names.
     *
     * @param string $table the table to be updated.
     * @param array $columns the column data (name => value) to be updated.
     * @param array|string $condition the condition that will be put in the WHERE part. Please refer to
     * {@see Query::where()} on how to specify condition.
     * @param array $params the binding parameters that will be modified by this method so that they can be bound to the
     * DB command later.
     *
     * @return string the UPDATE SQL.
     *
     * @psalm-suppress UndefinedInterfaceMethod
     */
    public function update(string $table, array $columns, array|string $condition, array &$params = []): string
    {
        $normalizeTableRowData = $this->normalizeTableRowData($table, $columns);

        return parent::update(
            $table,
            is_array($normalizeTableRowData) ? $normalizeTableRowData : [],

            $condition,
            $params,
        );
    }

    /**
     * Creates an SQL statement to insert rows into a database table if they do not already exist (matching unique
     * constraints), or update them if they do.
     *
     * For example,
     *
     * ```php
     * $sql = $queryBuilder->upsert('pages', [
     *     'name' => 'Front page',
     *     'url' => 'http://example.com/', // url is unique
     *     'visits' => 0,
     * ], [
     *     'visits' => new \Yiisoft\Db\Expression('visits + 1'),
     * ], $params);
     * ```
     *
     * The method will properly escape the table and column names.
     *
     * @param string $table the table that new rows will be inserted into/updated in.
     * @param array|QueryInterface $insertColumns the column data (name => value) to be inserted into the table or
     * instance of {@see Query} to perform `INSERT INTO ... SELECT` SQL statement.
     * @param array|bool|QueryInterface $updateColumns the column data (name => value) to be updated if they already
     * exist.
     * If `true` is passed, the column data will be updated to match the insert column data.
     * If `false` is passed, no update will be performed if the column data already exists.
     * @param array $params the binding parameters that will be generated by this method.
     * They should be bound to the DB command later.
     *
     * @throws Exception|InvalidConfigException|JsonException|NotSupportedException if this is not supported by the
     * underlying DBMS.
     *
     * @return string the resulting SQL.
     *
     * @link https://www.postgresql.org/docs/9.5/static/sql-insert.html#SQL-ON-CONFLICT
     * @link https://stackoverflow.com/questions/1109061/insert-on-duplicate-update-in-postgresql/8702291#8702291
     *
     * @psalm-suppress UndefinedInterfaceMethod
     */
    public function upsert(
        string $table,
        QueryInterface|array $insertColumns,
        $updateColumns,
        array &$params = []
    ): string {
        $insertColumns = $this->normalizeTableRowData($table, $insertColumns);

        if (!is_bool($updateColumns)) {
            $updateColumns = $this->normalizeTableRowData($table, $updateColumns);
        }

        return $this->newUpsert($table, $insertColumns, $updateColumns, $params);
    }

    /**
     * Normalizes data to be saved into the table, performing extra preparations and type converting, if necessary.
     *
     * @param string $table the table that data will be saved into.
     * @param array|QueryInterface $columns the column data (name => value) to be saved into the table or instance of
     * {@see QueryInterface} to perform INSERT INTO ... SELECT SQL statement. Passing of
     * {@see QueryInterface}.
     *
     * @return array|QueryInterface normalized columns.
     */
    private function normalizeTableRowData(string $table, QueryInterface|array $columns): QueryInterface|array
    {
        if ($columns instanceof QueryInterface) {

            return $columns;
        }

        if (($tableSchema = $this->schema->getTableSchema($table)) !== null) {
            $columnSchemas = $tableSchema->getColumns();
            /** @var mixed $value */
            foreach ($columns as $name => $value) {
                if (
                    isset($columnSchemas[$name]) &&
                    $columnSchemas[$name]->getType() === Schema::TYPE_BINARY &&
                    is_string($value)
                ) {
                    /** explicitly setup PDO param type for binary column */
                    $columns[$name] = new PDOValue($value, PDO::PARAM_LOB);
                }
            }
        }

        return $columns;
    }
}


1		<?php
2
3		declare(strict_types=1);
4
5		namespace Yiisoft\Db\Pgsql;
6
7		use Generator;
8		use JsonException;
9		use PDO;
10		use Yiisoft\Db\Driver\PDO\PDOValue;
11		use Yiisoft\Db\Exception\Exception;
12		use Yiisoft\Db\Exception\InvalidArgumentException;
13		use Yiisoft\Db\Exception\InvalidConfigException;
14		use Yiisoft\Db\Exception\NotSupportedException;
15		use Yiisoft\Db\Expression\Expression;
16		use Yiisoft\Db\Expression\ExpressionInterface;
17		use Yiisoft\Db\Query\DMLQueryBuilder as AbstractDMLQueryBuilder;
18		use Yiisoft\Db\Query\Query;
19		use Yiisoft\Db\Query\QueryBuilderInterface;
20		use Yiisoft\Db\Query\QueryInterface;
21		use Yiisoft\Db\Schema\QuoterInterface;
22		use Yiisoft\Db\Schema\Schema;
		0 ignored issues – show Bug introduced 2022-05-03 20:17 UTC by Report Bug Copy Issue Report This use statement conflicts with another class in this namespace, `Yiisoft\Db\Pgsql\Schema`. Consider defining an alias. Let?s assume that you have a directory layout like this: . \|-- OtherDir \| \|-- Bar.php \| `-- Foo.php `-- SomeDir `-- Foo.php and let?s assume the following content of `Bar.php`: // Bar.php namespace OtherDir; use SomeDir\Foo; // This now conflicts the class OtherDir\Foo If both files `OtherDir/Foo.php` and `SomeDir/Foo.php` are loaded in the same runtime, you will see a PHP error such as the following: PHP Fatal error: Cannot use SomeDir\Foo as Foo because the name is already in use in OtherDir/Foo.php However, as `OtherDir/Foo.php` does not necessarily have to be loaded and the error is only triggered if it is loaded before `OtherDir/Bar.php`, this problem might go unnoticed for a while. In order to prevent this error from surfacing, you must import the namespace with a different alias: // Bar.php namespace OtherDir; use SomeDir\Foo as SomeDirFoo; // There is no conflict anymore. Loading history...
23		use Yiisoft\Db\Schema\SchemaInterface;
24		use Yiisoft\Strings\NumericHelper;
25
26		use function implode;
27		use function is_array;
28		use function is_string;
29		use function reset;
30
31		final class DMLQueryBuilder extends AbstractDMLQueryBuilder
32		{
33	414	public function __construct(
34		private QueryBuilderInterface $queryBuilder,
35		private QuoterInterface $quoter,
36		private SchemaInterface $schema
37		) {
38	414	parent::__construct($queryBuilder, $quoter, $schema);
39		}
40
41	1	public function insertEx(string $table, QueryInterface\|array $columns, array &$params = []): string
42		{
43	1	$sql = $this->insert($table, $columns, $params);
44
45	1	$tableSchema = $this->schema->getTableSchema($table);
46
47	1	$returnColumns = [];
48	1	if ($tableSchema !== null) {
49	1	$returnColumns = $tableSchema->getPrimaryKey();
50		}
51
52	1	if (!empty($returnColumns)) {
53	1	$returning = [];
54	1	foreach ($returnColumns as $name) {
55	1	$returning[] = $this->quoter->quoteColumnName($name);
56		}
57	1	$sql .= ' RETURNING ' . implode(', ', $returning);
58		}
59
60	1	return $sql;
61		}
62
63		/**
64		* @throws Exception\|InvalidArgumentException\|InvalidConfigException\|NotSupportedException
65		*
66		* @psalm-suppress MixedArrayOffset
67		*/
68	20	public function batchInsert(string $table, array $columns, iterable\|Generator $rows, array &$params = []): string
69		{
70	20	if (empty($rows)) {
71	2	return '';
72		}
73
74		/**
75		* @psalm-var array<array-key, ColumnSchema> $columnSchemas
76		*/
77	19	$columnSchemas = [];
78	19	$schema = $this->schema;
79
80	19	if (($tableSchema = $schema->getTableSchema($table)) !== null) {
81	19	$columnSchemas = $tableSchema->getColumns();
82		}
83
84	19	$values = [];
85
86		/**
87		* @var array $row
88		*/
89	19	foreach ($rows as $row) {
90	19	$vs = [];
91		/**
92		* @var int $i
93		* @var mixed $value
94		*/
95	19	foreach ($row as $i => $value) {
96	19	if (isset($columns[$i], $columnSchemas[$columns[$i]])) {
97		/**
98		* @var bool\|ExpressionInterface\|float\|int\|string\|null $value
99		*/
100	16	$value = $columnSchemas[$columns[$i]]->dbTypecast($value);
101		}
102
103	19	if (is_string($value)) {
104		/** @var mixed */
105	9	$value = $this->quoter->quoteValue($value);
106	13	} elseif (is_float($value)) {
107		/** ensure type cast always has . as decimal separator in all locales */
108	1	$value = NumericHelper::normalize((string) $value);
109	13	} elseif ($value === true) {
110	3	$value = 'TRUE';
111	13	} elseif ($value === false) {
112	5	$value = 'FALSE';
113	11	} elseif ($value === null) {
114	4	$value = 'NULL';
115	8	} elseif ($value instanceof ExpressionInterface) {
116	6	$value = $this->queryBuilder->buildExpression($value, $params);
117		}
118
119		/** @var bool\|ExpressionInterface\|float\|int\|string\|null $value */
120	19	$vs[] = $value;
121		}
122	19	$values[] = '(' . implode(', ', $vs) . ')';
123		}
124
125	19	if (empty($values)) {
126		return '';
127		}
128
129		/** @var string name */
130	19	foreach ($columns as $i => $name) {
131	18	$columns[$i] = $this->quoter->quoteColumnName($name);
132		}
133
134		/**
135		* @psalm-var string[] $columns
136		* @psalm-var string[] $values
137		*/
138		return 'INSERT INTO '
139	19	. $this->quoter->quoteTableName($table)
140	19	. ' (' . implode(', ', $columns) . ') VALUES ' . implode(', ', $values);
141		}
142
143		/**
144		* Creates an INSERT SQL statement.
145		*
146		* For example,
147		*
148		* ```php
149		* $sql = $queryBuilder->insert('user', [
150		* 'name' => 'Sam',
151		* 'age' => 30,
152		* ], $params);
153		* ```
154		*
155		* The method will properly escape the table and column names.
156		*
157		* @param string $table the table that new rows will be inserted into.
158		* @param array\|QueryInterface $columns the column data (name => value) to be inserted into the table or instance of
159		* {@see Query} to perform INSERT INTO ... SELECT SQL statement. Passing of {@see Query}.
160		* @param array $params the binding parameters that will be generated by this method. They should be bound to the
161		* DB command later.
162		*
163		* @throws Exception\|InvalidArgumentException\|InvalidConfigException\|NotSupportedException
164		*
165		* @return string the INSERT SQL
166		*
167		* @psalm-suppress UndefinedInterfaceMethod
168		* @psalm-suppress MixedArgument
169		*/
170	49	public function insert(string $table, QueryInterface\|array $columns, array &$params = []): string
171		{
172	49	return parent::insert($table, $this->normalizeTableRowData($table, $columns), $params);
173		}
174
175		/**
176		* {@see upsert()} implementation for PostgresSQL 9.5 or higher.
177		*
178		* @param string $table
179		* @param array\|QueryInterface $insertColumns
180		* @param array\|bool\|QueryInterface $updateColumns
181		* @param array $params
182		*
183		* @throws Exception\|InvalidArgumentException\|InvalidConfigException\|JsonException\|NotSupportedException
184		*
185		* @return string
186		*/
187	18	public function newUpsert(
188		string $table,
189		QueryInterface\|array $insertColumns,
190		bool\|array\|QueryInterface $updateColumns,
191		array &$params = []
192		): string {
193	18	$insertSql = $this->insert($table, $insertColumns, $params);
194
195		/** @var array $uniqueNames */
196	18	[$uniqueNames, , $updateNames] = $this->prepareUpsertColumns(
197		$table,
198		$insertColumns,
199		$updateColumns,
200		);
201
202	18	if (empty($uniqueNames)) {
203	3	return $insertSql;
204		}
205
206	15	if ($updateNames === []) {
		0 ignored issues – show introduced 2022-05-03 20:58 UTC by Report Bug Copy Issue Report The condition `$updateNames === array()` is always `false`. Loading history...
207		/** there are no columns to update */
208		$updateColumns = false;
209		}
210
211	15	if ($updateColumns === false) {
		0 ignored issues – show introduced 2022-05-03 20:58 UTC by Report Bug Copy Issue Report The condition `$updateColumns === false` is always `false`. Loading history...
212	5	return "$insertSql ON CONFLICT DO NOTHING";
213		}
214
215	10	if ($updateColumns === true) {
		0 ignored issues – show introduced 2022-02-12 12:43 UTC by Report Bug Copy Issue Report The condition `$updateColumns === true` is always `false`. Loading history...
216	4	$updateColumns = [];
217
218		/** @var string $name */
219	4	foreach ($updateNames as $name) {
220	4	$updateColumns[$name] = new Expression(
221	4	'EXCLUDED.' . $this->quoter->quoteColumnName($name)
222		);
223		}
224		}
225
226		/**
227		* @var array $updateColumns
228		*
229		* @psalm-var string[] $uniqueNames
230		* @psalm-var string[] $updates
231		*/
232	10	[$updates, $params] = $this->prepareUpdateSets($table, $updateColumns, $params);
233
234		return $insertSql
235	10	. ' ON CONFLICT (' . implode(', ', $uniqueNames) . ') DO UPDATE SET ' . implode(', ', $updates);
236		}
237
238		/**
239		* Creates a SQL statement for resetting the sequence value of a table's primary key.
240		*
241		* The sequence will be reset such that the primary key of the next new row inserted will have the specified value
242		* or 1.
243		*
244		* @param string $tableName the name of the table whose primary key sequence will be reset.
245		* @param mixed $value the value for the primary key of the next new row inserted. If this is not set, the next new
246		* row's primary key will have a value 1.
247		*
248		* @throws Exception\|InvalidArgumentException if the table does not exist or there is no sequence
249		* associated with the table.
250		*
251		* @return string the SQL statement for resetting sequence.
252		*/
253	2	public function resetSequence(string $tableName, $value = null): string
254		{
255	2	$table = $this->schema->getTableSchema($tableName);
256
257	2	if ($table !== null && ($sequence = $table->getSequenceName()) !== null) {
258		/**
259		* {@see http://www.postgresql.org/docs/8.1/static/functions-sequence.html}
260		*/
261	2	$sequence = $this->quoter->quoteTableName($sequence);
262	2	$tableName = $this->quoter->quoteTableName($tableName);
263
264	2	if ($value === null) {
265	2	$pk = $table->getPrimaryKey();
266	2	$key = $this->quoter->quoteColumnName(reset($pk));
267	2	$value = "(SELECT COALESCE(MAX($key),0) FROM $tableName)+1";
268		} else {
269	2	$value = (int) $value;
270		}
271
272	2	return "SELECT SETVAL('$sequence',$value,false)";
273		}
274
275		if ($table === null) {
276		throw new InvalidArgumentException("Table not found: $tableName");
277		}
278
279		throw new InvalidArgumentException("There is not sequence associated with table '$tableName'.");
280		}
281
282		public function truncateTable(string $table): string
283		{
284		return 'TRUNCATE TABLE ' . $this->quoter->quoteTableName($table) . ' RESTART IDENTITY';
285		}
286
287		/**
288		* Creates an UPDATE SQL statement.
289		*
290		* For example,
291		*
292		* ```php
293		* $params = [];
294		* $sql = $queryBuilder->update('user', ['status' => 1], 'age > 30', $params);
295		* ```
296		*
297		* The method will properly escape the table and column names.
298		*
299		* @param string $table the table to be updated.
300		* @param array $columns the column data (name => value) to be updated.
301		* @param array\|string $condition the condition that will be put in the WHERE part. Please refer to
302		* {@see Query::where()} on how to specify condition.
303		* @param array $params the binding parameters that will be modified by this method so that they can be bound to the
304		* DB command later.
305	4	*
306		* @return string the UPDATE SQL.
307	4	*
308		* @psalm-suppress UndefinedInterfaceMethod
309	4	*/
310		public function update(string $table, array $columns, array\|string $condition, array &$params = []): string
311	4	{
312		$normalizeTableRowData = $this->normalizeTableRowData($table, $columns);
313
314		return parent::update(
315		$table,
316		is_array($normalizeTableRowData) ? $normalizeTableRowData : [],
		0 ignored issues – show introduced 2022-05-03 20:17 UTC by Report Bug Copy Issue Report The condition `is_array($normalizeTableRowData)` is always `true`. Loading history...
317		$condition,
318		$params,
319		);
320		}
321
322		/**
323		* Creates an SQL statement to insert rows into a database table if they do not already exist (matching unique
324		* constraints), or update them if they do.
325		*
326		* For example,
327		*
328		* ```php
329		* $sql = $queryBuilder->upsert('pages', [
330		* 'name' => 'Front page',
331		* 'url' => 'http://example.com/', // url is unique
332		* 'visits' => 0,
333		* ], [
334		* 'visits' => new \Yiisoft\Db\Expression('visits + 1'),
335		* ], $params);
336		* ```
337		*
338		* The method will properly escape the table and column names.
339		*
340		* @param string $table the table that new rows will be inserted into/updated in.
341		* @param array\|QueryInterface $insertColumns the column data (name => value) to be inserted into the table or
342		* instance of {@see Query} to perform `INSERT INTO ... SELECT` SQL statement.
343		* @param array\|bool\|QueryInterface $updateColumns the column data (name => value) to be updated if they already
344		* exist.
345		* If `true` is passed, the column data will be updated to match the insert column data.
346		* If `false` is passed, no update will be performed if the column data already exists.
347		* @param array $params the binding parameters that will be generated by this method.
348		* They should be bound to the DB command later.
349		*
350		* @throws Exception\|InvalidConfigException\|JsonException\|NotSupportedException if this is not supported by the
351		* underlying DBMS.
352		*
353		* @return string the resulting SQL.
354		*
355	18	* @link https://www.postgresql.org/docs/9.5/static/sql-insert.html#SQL-ON-CONFLICT
356		* @link https://stackoverflow.com/questions/1109061/insert-on-duplicate-update-in-postgresql/8702291#8702291
357		*
358		* @psalm-suppress UndefinedInterfaceMethod
359		*/
360		public function upsert(
361	18	string $table,
362		QueryInterface\|array $insertColumns,
363	18	$updateColumns,
364	7	array &$params = []
365		): string {
366		$insertColumns = $this->normalizeTableRowData($table, $insertColumns);
367	18
368		if (!is_bool($updateColumns)) {
369		$updateColumns = $this->normalizeTableRowData($table, $updateColumns);
370		}
371
372		return $this->newUpsert($table, $insertColumns, $updateColumns, $params);
373		}
374
375		/**
376		* Normalizes data to be saved into the table, performing extra preparations and type converting, if necessary.
377		*
378		* @param string $table the table that data will be saved into.
379		* @param array\|QueryInterface $columns the column data (name => value) to be saved into the table or instance of
380	51	* {@see QueryInterface} to perform INSERT INTO ... SELECT SQL statement. Passing of
381		* {@see QueryInterface}.
382	51	*
383	14	* @return array\|QueryInterface normalized columns.
384		*/
385		private function normalizeTableRowData(string $table, QueryInterface\|array $columns): QueryInterface\|array
386	43	{
387	43	if ($columns instanceof QueryInterface) {
		0 ignored issues – show introduced 2022-05-03 20:17 UTC by Report Bug Copy Issue Report `$columns` is never a sub-type of `Yiisoft\Db\Query\QueryInterface`. Loading history...
388		return $columns;
389	43	}
390
391	43	if (($tableSchema = $this->schema->getTableSchema($table)) !== null) {
392	43	$columnSchemas = $tableSchema->getColumns();
393	43	/** @var mixed $value */
394		foreach ($columns as $name => $value) {
395		if (
396	2	isset($columnSchemas[$name]) &&
397		$columnSchemas[$name]->getType() === Schema::TYPE_BINARY &&
398		is_string($value)
399		) {
400		/** explicitly setup PDO param type for binary column */
401	43	$columns[$name] = new PDOValue($value, PDO::PARAM_LOB);
402		}
403		}
404		}
405
406		return $columns;
407		}
408		}
409

yiisoft / db-pgsql

Pull Request — dev (#126)

DMLQueryBuilder::update() A

Complexity

Size

Duplication

Code Coverage

Importance

Duplication Side-by-Side

Filter issues like