Skip to content

Commit a5b57d2

Browse files
darkdefdarkdef
authored
Fix for upsert behavior (#189)
* Fix for upsert behavior * disable rector
1 parent ac6373d commit a5b57d2

5 files changed

Lines changed: 40 additions & 43 deletions

File tree

.github/workflows/rector.yml renamed to .github/workflows/rector.yml_

File renamed without changes.

‎src/DMLQueryBuilder.php‎

Lines changed: 4 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -133,10 +133,10 @@ public function upsert(
133133
'VALUES(' . $this->quoter->quoteColumnName($name) . ')'
134134
);
135135
}
136-
} elseif ($updateColumns === false) {
137-
$columnName = (string) reset($uniqueNames);
138-
$name = $this->quoter->quoteColumnName($columnName);
139-
$updateColumns = [$name => new Expression($this->quoter->quoteTableName($table) . '.' . $name)];
136+
}
137+
138+
if (empty($updateColumns)) {
139+
return str_replace('INSERT INTO', 'INSERT IGNORE INTO', $insertSql);
140140
}
141141

142142
/**

‎tests/Provider/QueryBuilderProvider.php‎

Lines changed: 24 additions & 38 deletions
Original file line numberDiff line numberDiff line change
@@ -108,68 +108,54 @@ public function upsert(): array
108108
{
109109
$concreteData = [
110110
'regular values' => [
111-
3 => <<<SQL
112-
INSERT INTO `T_upsert` (`email`, `address`, `status`, `profile_id`) VALUES (:qp0, :qp1, :qp2, :qp3) ON DUPLICATE KEY UPDATE `address`=VALUES(`address`), `status`=VALUES(`status`), `profile_id`=VALUES(`profile_id`)
113-
SQL,
111+
3 => 'INSERT INTO `T_upsert` (`email`, `address`, `status`, `profile_id`) VALUES (:qp0, :qp1, :qp2, :qp3) ' .
112+
'ON DUPLICATE KEY UPDATE `address`=VALUES(`address`), `status`=VALUES(`status`), `profile_id`=VALUES(`profile_id`)',
114113
],
115114
'regular values with update part' => [
116-
3 => <<<SQL
117-
INSERT INTO `T_upsert` (`email`, `address`, `status`, `profile_id`) VALUES (:qp0, :qp1, :qp2, :qp3) ON DUPLICATE KEY UPDATE `address`=:qp4, `status`=:qp5, `orders`=T_upsert.orders + 1
118-
SQL,
115+
3 => 'INSERT INTO `T_upsert` (`email`, `address`, `status`, `profile_id`) VALUES (:qp0, :qp1, :qp2, :qp3) ' .
116+
'ON DUPLICATE KEY UPDATE `address`=:qp4, `status`=:qp5, `orders`=T_upsert.orders + 1',
119117
],
120118
'regular values without update part' => [
121-
3 => <<<SQL
122-
INSERT INTO `T_upsert` (`email`, `address`, `status`, `profile_id`) VALUES (:qp0, :qp1, :qp2, :qp3) ON DUPLICATE KEY UPDATE `email`=`T_upsert`.`email`
123-
SQL,
119+
3 => 'INSERT IGNORE INTO `T_upsert` (`email`, `address`, `status`, `profile_id`) VALUES (:qp0, :qp1, :qp2, :qp3)',
124120
],
125121
'query' => [
126-
3 => <<<SQL
127-
INSERT INTO `T_upsert` (`email`, `status`) SELECT `email`, 2 AS `status` FROM `customer` WHERE `name`=:qp0 LIMIT 1 ON DUPLICATE KEY UPDATE `status`=VALUES(`status`)
128-
SQL,
122+
3 => 'INSERT INTO `T_upsert` (`email`, `status`) SELECT `email`, 2 AS `status` FROM `customer` ' .
123+
'WHERE `name`=:qp0 LIMIT 1 ON DUPLICATE KEY UPDATE `status`=VALUES(`status`)',
129124
],
130125
'query with update part' => [
131-
3 => <<<SQL
132-
INSERT INTO `T_upsert` (`email`, `status`) SELECT `email`, 2 AS `status` FROM `customer` WHERE `name`=:qp0 LIMIT 1 ON DUPLICATE KEY UPDATE `address`=:qp1, `status`=:qp2, `orders`=T_upsert.orders + 1
133-
SQL,
126+
3 => 'INSERT INTO `T_upsert` (`email`, `status`) SELECT `email`, 2 AS `status` FROM `customer` ' .
127+
'WHERE `name`=:qp0 LIMIT 1 ON DUPLICATE KEY UPDATE `address`=:qp1, `status`=:qp2, `orders`=T_upsert.orders + 1',
134128
],
135129
'query without update part' => [
136-
3 => <<<SQL
137-
INSERT INTO `T_upsert` (`email`, `status`) SELECT `email`, 2 AS `status` FROM `customer` WHERE `name`=:qp0 LIMIT 1 ON DUPLICATE KEY UPDATE `email`=`T_upsert`.`email`
138-
SQL,
130+
3 => 'INSERT IGNORE INTO `T_upsert` (`email`, `status`) SELECT `email`, 2 AS `status` FROM `customer` ' .
131+
'WHERE `name`=:qp0 LIMIT 1',
139132
],
140133
'values and expressions' => [
141-
3 => <<<SQL
142-
INSERT INTO {{%T_upsert}} ({{%T_upsert}}.[[email]], [[ts]]) VALUES (:qp0, now())
143-
SQL,
134+
3 => 'INSERT INTO {{%T_upsert}} ({{%T_upsert}}.[[email]], [[ts]]) VALUES (:qp0, CURRENT_TIMESTAMP) ' .
135+
'ON DUPLICATE KEY UPDATE [[ts]]=VALUES([[ts]])',
144136
],
145137
'values and expressions with update part' => [
146-
3 => <<<SQL
147-
INSERT INTO {{%T_upsert}} ({{%T_upsert}}.[[email]], [[ts]]) VALUES (:qp0, now())
148-
SQL,
138+
3 => 'INSERT INTO {{%T_upsert}} ({{%T_upsert}}.[[email]], [[ts]]) VALUES (:qp0, CURRENT_TIMESTAMP) ' .
139+
'ON DUPLICATE KEY UPDATE [[orders]]=T_upsert.orders + 1',
149140
],
150141
'values and expressions without update part' => [
151-
3 => <<<SQL
152-
INSERT INTO {{%T_upsert}} ({{%T_upsert}}.[[email]], [[ts]]) VALUES (:qp0, now())
153-
SQL,
142+
3 => 'INSERT IGNORE INTO {{%T_upsert}} ({{%T_upsert}}.[[email]], [[ts]]) VALUES (:qp0, CURRENT_TIMESTAMP)',
154143
],
155144
'query, values and expressions with update part' => [
156-
3 => <<<SQL
157-
INSERT INTO {{%T_upsert}} (`email`, [[time]]) SELECT :phEmail AS `email`, now() AS [[time]] ON DUPLICATE KEY UPDATE `ts`=:qp1, [[orders]]=T_upsert.orders + 1
158-
SQL,
145+
3 => 'INSERT INTO {{%T_upsert}} (`email`, [[ts]]) SELECT :phEmail AS `email`, CURRENT_TIMESTAMP AS [[ts]] ' .
146+
'ON DUPLICATE KEY UPDATE `ts`=:qp1, [[orders]]=T_upsert.orders + 1',
159147
],
160148
'query, values and expressions without update part' => [
161-
3 => <<<SQL
162-
INSERT INTO {{%T_upsert}} (`email`, [[time]]) SELECT :phEmail AS `email`, now() AS [[time]] ON DUPLICATE KEY UPDATE `ts`=:qp1, [[orders]]=T_upsert.orders + 1
163-
SQL,
149+
3 => 'INSERT IGNORE INTO {{%T_upsert}} (`email`, [[ts]]) SELECT :phEmail AS `email`, CURRENT_TIMESTAMP AS [[ts]]',
164150
],
165151
'no columns to update' => [
166-
3 => <<<SQL
167-
INSERT INTO `T_upsert_1` (`a`) VALUES (:qp0) ON DUPLICATE KEY UPDATE `a`=`T_upsert_1`.`a`
168-
SQL,
152+
3 => 'INSERT IGNORE INTO `T_upsert_1` (`a`) VALUES (:qp0)',
169153
],
170-
// @todo - SQL code have a bug. Need fix in next PR
171154
'no columns to update with unique' => [
172-
3 => 'INSERT INTO {{%T_upsert}} (`email`) VALUES (:qp0) ON DUPLICATE KEY UPDATE ',
155+
3 => 'INSERT IGNORE INTO {{%T_upsert}} (`email`) VALUES (:qp0)',
156+
],
157+
'no unique columns in table - simple insert' => [
158+
3 => 'INSERT INTO {{%animal}} (`type`) VALUES (:qp0)',
173159
],
174160
];
175161

‎tests/QueryBuilderTest.php‎

Lines changed: 11 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -555,4 +555,15 @@ public function testUpsert(
555555
): void {
556556
parent::testUpsert($table, $insertColumns, $updateColumns, $expectedSQL, $expectedParams);
557557
}
558+
559+
/**
560+
* @dataProvider \Yiisoft\Db\Mysql\Tests\Provider\QueryBuilderProvider::upsert()
561+
*/
562+
public function testUpsertExecute(
563+
string $table,
564+
array|QueryInterface $insertColumns,
565+
array|bool $updateColumns
566+
): void {
567+
parent::testUpsertExecute($table, $insertColumns, $updateColumns);
568+
}
558569
}

‎tests/Support/Fixture/mysql.sql‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -374,7 +374,7 @@ ENGINE = 'InnoDB' DEFAULT CHARSET = 'utf8';
374374
CREATE TABLE `T_upsert`
375375
(
376376
`id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
377-
`ts` INT NULL,
377+
`ts` BIGINT NULL,
378378
`email` VARCHAR(128) NOT NULL UNIQUE,
379379
`recovery_email` VARCHAR(128) NULL,
380380
`address` TEXT NULL,

0 commit comments

Comments
 (0)