Skip to content

Commit a53dd36

Browse files
darkdefdarkdef
authored
Fix of normalize for column names in insert, batchInsert, upsert, update (#443)
* Fix of normalize for column names in insert, batchInsert, upsert, update * remove comments * styleci
1 parent 7fac771 commit a53dd36

5 files changed

Lines changed: 98 additions & 43 deletions

File tree

‎src/QueryBuilder/DMLQueryBuilder.php‎

Lines changed: 76 additions & 29 deletions
Original file line numberDiff line numberDiff line change
@@ -42,6 +42,7 @@ public function __construct(
4242
}
4343

4444
/**
45+
* @psalm-param string[] $columns
4546
* @psalm-suppress MixedArrayOffset
4647
*/
4748
public function batchInsert(string $table, array $columns, iterable|Generator $rows, array &$params = []): string
@@ -56,15 +57,16 @@ public function batchInsert(string $table, array $columns, iterable|Generator $r
5657
$columnSchemas = [];
5758
}
5859

60+
$mappedNames = $this->getNormalizeColumnNames($table, $columns);
5961
$values = [];
6062

6163
/** @psalm-var array<array-key, array<array-key, string>> $rows */
6264
foreach ($rows as $row) {
6365
$placeholders = [];
6466
foreach ($row as $index => $value) {
65-
if (isset($columns[$index], $columnSchemas[$columns[$index]])) {
67+
if (isset($columns[$index], $mappedNames[$columns[$index]], $columnSchemas[$mappedNames[$columns[$index]]])) {
6668
/** @var mixed $value */
67-
$value = $this->getTypecastValue($value, $columnSchemas[$columns[$index]]);
69+
$value = $this->getTypecastValue($value, $columnSchemas[$mappedNames[$columns[$index]]]);
6870
}
6971

7072
if ($value instanceof ExpressionInterface) {
@@ -80,9 +82,8 @@ public function batchInsert(string $table, array $columns, iterable|Generator $r
8082
return '';
8183
}
8284

83-
/** @psalm-var string[] $columns */
8485
foreach ($columns as $i => $name) {
85-
$columns[$i] = $this->quoter->quoteColumnName($name);
86+
$columns[$i] = $this->quoter->quoteColumnName($mappedNames[$name]);
8687
}
8788

8889
return 'INSERT INTO '
@@ -103,6 +104,10 @@ public function delete(string $table, array|string $condition, array &$params):
103104

104105
public function insert(string $table, QueryInterface|array $columns, array &$params = []): string
105106
{
107+
if (!$columns instanceof QueryInterface) {
108+
$columns = $this->normalizeColumnNames($table, $columns);
109+
}
110+
106111
/**
107112
* @psalm-var string[] $names
108113
* @psalm-var string[] $placeholders
@@ -137,6 +142,8 @@ public function resetSequence(string $tableName, int|string|null $value = null):
137142
*/
138143
public function update(string $table, array $columns, array|string $condition, array &$params = []): string
139144
{
145+
$columns = $this->normalizeColumnNames($table, $columns);
146+
140147
/** @psalm-var string[] $lines */
141148
[$lines, $params] = $this->prepareUpdateSets($table, $columns, $params);
142149
$sql = 'UPDATE ' . $this->quoter->quoteTableName($table) . ' SET ' . implode(', ', $lines);
@@ -342,26 +349,11 @@ static function (Constraint $constraint) {
342349
$columnNames = [];
343350
$quoter = $this->quoter;
344351

345-
// Need get filtered column names. Without name of table. And remove columns from other tables
346-
$unquotedColumns = $simpleColumns = [];
347-
$rawTableName = $this->schema->getRawTableName($name);
348-
foreach ($columns as $column) {
349-
$parts = $quoter->getTableNameParts($column, true);
350-
351-
// Skip columns from other tables
352-
if (count($parts) === 2 && $this->schema->getRawTableName($parts[0]) !== $rawTableName) {
353-
continue;
354-
}
355-
356-
$columnName = $quoter->ensureColumnName($parts[count($parts)-1]);
357-
$unquotedColumns[$column] = $simpleColumns[] = $quoter->quoteColumnName($columnName);
358-
}
359-
360352
// Remove all constraints which do not cover the specified column list.
361353
$constraints = array_values(
362354
array_filter(
363355
$constraints,
364-
static function (Constraint $constraint) use ($quoter, $simpleColumns, &$columnNames) {
356+
static function (Constraint $constraint) use ($quoter, $columns, &$columnNames) {
365357
/** @psalm-var string[]|string $getColumnNames */
366358
$getColumnNames = $constraint->getColumnNames() ?? [];
367359
$constraintColumnNames = [];
@@ -372,7 +364,7 @@ static function (Constraint $constraint) use ($quoter, $simpleColumns, &$columnN
372364
}
373365
}
374366

375-
$result = !array_diff($constraintColumnNames, $simpleColumns);
367+
$result = !array_diff($constraintColumnNames, $columns);
376368

377369
if ($result) {
378370
$columnNames = array_merge((array) $columnNames, $constraintColumnNames);
@@ -383,14 +375,8 @@ static function (Constraint $constraint) use ($quoter, $simpleColumns, &$columnN
383375
)
384376
);
385377

386-
// restore original column names
387-
$originalColumnNames = [];
388-
/** @psalm-var string[] $columnNames */
389-
foreach ($columnNames as $columnName) {
390-
$originalColumnNames[] = $unquotedColumns[$columnName] ?? $columnName;
391-
}
392-
393-
return array_unique($originalColumnNames);
378+
/** @psalm-var array $columnNames */
379+
return array_unique($columnNames);
394380
}
395381

396382
protected function getTypecastValue(mixed $value, ColumnSchemaInterface $columnSchema = null): mixed
@@ -401,4 +387,65 @@ protected function getTypecastValue(mixed $value, ColumnSchemaInterface $columnS
401387

402388
return $value;
403389
}
390+
391+
/**
392+
* Normalizes column names
393+
*
394+
* @param string $table the table that data will be saved into.
395+
* @param array $columns the column data (name => value) to be saved into the table or instance of
396+
* {@see QueryInterface} to perform INSERT INTO ... SELECT SQL statement. Passing of
397+
* {@see QueryInterface}.
398+
*
399+
* @return array normalized columns.
400+
*/
401+
protected function normalizeColumnNames(string $table, array $columns): array
402+
{
403+
/** @var string[] $columnsList */
404+
$columnsList = array_keys($columns);
405+
$mappedNames = $this->getNormalizeColumnNames($table, $columnsList);
406+
407+
/** @psalm-var mixed[] $normalizedColumns */
408+
$normalizedColumns = [];
409+
410+
/**
411+
* @var string $name
412+
* @var mixed $value
413+
*/
414+
foreach ($columns as $name => $value) {
415+
$mappedName = $mappedNames[$name] ?? $name;
416+
/** @psalm-suppress MixedAssignment */
417+
$normalizedColumns[$mappedName] = $value;
418+
}
419+
420+
return $normalizedColumns;
421+
}
422+
423+
/**
424+
* Get map of normalized columns
425+
*
426+
* @param string $table
427+
* @param string[] $columns
428+
*
429+
* @return string[]
430+
*/
431+
protected function getNormalizeColumnNames(string $table, array $columns): array
432+
{
433+
$normalizedNames = [];
434+
$rawTableName = $this->schema->getRawTableName($table);
435+
436+
foreach ($columns as $name) {
437+
$parts = $this->quoter->getTableNameParts($name, true);
438+
439+
if (count($parts) === 2 && $this->schema->getRawTableName($parts[0]) === $rawTableName) {
440+
$normalizedName = $parts[count($parts) - 1];
441+
} else {
442+
$normalizedName = $name;
443+
}
444+
$normalizedName = $this->quoter->ensureColumnName($normalizedName);
445+
446+
$normalizedNames[$name] = $normalizedName;
447+
}
448+
449+
return $normalizedNames;
450+
}
404451
}

‎src/QueryBuilder/DMLQueryBuilderInterface.php‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -32,7 +32,7 @@ interface DMLQueryBuilderInterface
3232
* The method will properly escape the column names, and quote the values to be inserted.
3333
*
3434
* @param string $table the table that new rows will be inserted into.
35-
* @param array $columns the column names.
35+
* @param string[] $columns the column names.
3636
* @param Generator|iterable $rows the rows to be batched inserted into the table.
3737
* @param array $params the binding parameters. This parameter exists.
3838
*

‎tests/Common/CommonCommandTest.php‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -278,11 +278,11 @@ public function testBatchInsert(
278278

279279
$command = $db->createCommand();
280280
$command->batchInsert($table, $columns, $values);
281-
$command->prepare(false);
282281

283282
$this->assertSame($expected, $command->getSql());
284283
$this->assertSame($expectedParams, $command->getParams());
285284

285+
$command->prepare(false);
286286
$command->execute();
287287

288288
$this->assertEquals($insertedRow, (new Query($db))->from($table)->count());

‎tests/Provider/AbstractCommandProvider.php‎

Lines changed: 1 addition & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -308,11 +308,10 @@ public function batchInsert(): array
308308
*
309309
* In case table name or table column is passed with curly or square bracelets, QueryBuilder can not
310310
* determine the table schema and typecast values properly.
311-
* TODO: make it work. Impossible without BC breaking for public methods.
312311
*/
313312
'expected' => DbHelper::replaceQuotes(
314313
<<<SQL
315-
INSERT INTO [[type]] ([[type]].[[int_col]], [[float_col]], [[char_col]], [[bool_col]]) VALUES (:qp0, :qp1, :qp2, :qp3)
314+
INSERT INTO [[type]] ([[int_col]], [[float_col]], [[char_col]], [[bool_col]]) VALUES (:qp0, :qp1, :qp2, :qp3)
316315
SQL,
317316
$this->getDriverName(),
318317
),

‎tests/Provider/AbstractQueryBuilderProvider.php‎

Lines changed: 19 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -187,18 +187,24 @@ public function batchInsert(): array
187187
'{{%type}}',
188188
['{{%type}}.[[float_col]]', '[[time]]'],
189189
[[null, new Expression('now()')], [null, new Expression('now()')]],
190-
'expected' => <<<SQL
191-
INSERT INTO {{%type}} ({{%type}}.[[float_col]], [[time]]) VALUES (:qp0, now()), (:qp1, now())
192-
SQL,
190+
'expected' => DbHelper::replaceQuotes(
191+
<<<SQL
192+
INSERT INTO {{%type}} ([[float_col]], [[time]]) VALUES (:qp0, now()), (:qp1, now())
193+
SQL,
194+
$this->getDriverName()
195+
),
193196
[':qp0' => null, ':qp1' => null],
194197
],
195198
'bool-false, time-now()' => [
196199
'{{%type}}',
197200
['{{%type}}.[[bool_col]]', '[[time]]'],
198201
[[false, new Expression('now()')]],
199-
'expected' => <<<SQL
200-
INSERT INTO {{%type}} ({{%type}}.[[bool_col]], [[time]]) VALUES (:qp0, now())
201-
SQL,
202+
'expected' => DbHelper::replaceQuotes(
203+
<<<SQL
204+
INSERT INTO {{%type}} ([[bool_col]], [[time]]) VALUES (:qp0, now())
205+
SQL,
206+
$this->getDriverName()
207+
),
202208
[':qp0' => null],
203209
],
204210
];
@@ -899,11 +905,14 @@ public function insert(): array
899905
],
900906
'params-and-expressions' => [
901907
'{{%type}}',
902-
['{{%type}}.[[related_id]]' => null, '[[time]]' => new Expression('now()')],
908+
['{{%type}}.[[related_id]]' => null, 'time' => new Expression('now()')],
903909
[],
904-
<<<SQL
905-
INSERT INTO {{%type}} ({{%type}}.[[related_id]], [[time]]) VALUES (:qp0, now())
906-
SQL,
910+
DbHelper::replaceQuotes(
911+
<<<SQL
912+
INSERT INTO {{%type}} ([[related_id]], [[time]]) VALUES (:qp0, now())
913+
SQL,
914+
$db->getName(),
915+
),
907916
[':qp0' => null],
908917
],
909918
'carry passed params' => [

0 commit comments

Comments
 (0)