1
|
|
|
<?php |
2
|
|
|
|
3
|
|
|
declare(strict_types=1); |
4
|
|
|
|
5
|
|
|
namespace Yiisoft\Db\Mysql\Tests; |
6
|
|
|
|
7
|
|
|
use Yiisoft\Db\Expressions\Expression; |
8
|
|
|
use Yiisoft\Db\Expressions\JsonExpression; |
9
|
|
|
use Yiisoft\Db\Querys\Query; |
10
|
|
|
use Yiisoft\Db\Mysql\Schema; |
11
|
|
|
use Yiisoft\Db\Tests\QueryBuilderTest as AbstractQueryBuilderTest; |
12
|
|
|
|
13
|
|
|
class QueryBuilderTest extends AbstractQueryBuilderTest |
14
|
|
|
{ |
15
|
|
|
protected ?string $driverName = 'mysql'; |
16
|
|
|
|
17
|
|
|
/** |
18
|
|
|
* This is not used as a dataprovider for testGetColumnType to speed up the test when used as dataprovider every |
19
|
|
|
* single line will cause a reconnect with the database which is not needed here. |
20
|
|
|
*/ |
21
|
|
|
public function columnTypes(): array |
22
|
|
|
{ |
23
|
|
|
$columns = [ |
24
|
|
|
[ |
25
|
|
|
Schema::TYPE_PK . ' AFTER `col_before`', |
26
|
|
|
$this->primaryKey()->after('col_before'), |
27
|
|
|
'int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY AFTER `col_before`', |
28
|
|
|
], |
29
|
|
|
[ |
30
|
|
|
Schema::TYPE_PK . ' FIRST', |
31
|
|
|
$this->primaryKey()->first(), |
32
|
|
|
'int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST', |
33
|
|
|
], |
34
|
|
|
[ |
35
|
|
|
Schema::TYPE_PK . ' FIRST', |
36
|
|
|
$this->primaryKey()->first()->after('col_before'), |
37
|
|
|
'int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST', |
38
|
|
|
], |
39
|
|
|
[ |
40
|
|
|
Schema::TYPE_PK . '(8) AFTER `col_before`', |
41
|
|
|
$this->primaryKey(8)->after('col_before'), |
42
|
|
|
'int(8) NOT NULL AUTO_INCREMENT PRIMARY KEY AFTER `col_before`', |
43
|
|
|
], |
44
|
|
|
[ |
45
|
|
|
Schema::TYPE_PK . '(8) FIRST', |
46
|
|
|
$this->primaryKey(8)->first(), |
47
|
|
|
'int(8) NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST', |
48
|
|
|
], |
49
|
|
|
[ |
50
|
|
|
Schema::TYPE_PK . '(8) FIRST', |
51
|
|
|
$this->primaryKey(8)->first()->after('col_before'), |
52
|
|
|
'int(8) NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST', |
53
|
|
|
], |
54
|
|
|
[ |
55
|
|
|
Schema::TYPE_PK . " COMMENT 'test' AFTER `col_before`", |
56
|
|
|
$this->primaryKey()->comment('test')->after('col_before'), |
57
|
|
|
"int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT 'test' AFTER `col_before`", |
58
|
|
|
], |
59
|
|
|
[ |
60
|
|
|
Schema::TYPE_PK . " COMMENT 'testing \'quote\'' AFTER `col_before`", |
61
|
|
|
$this->primaryKey()->comment('testing \'quote\'')->after('col_before'), |
62
|
|
|
"int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT 'testing \'quote\'' AFTER `col_before`", |
63
|
|
|
], |
64
|
|
|
]; |
65
|
|
|
|
66
|
|
|
return array_merge(parent::columnTypes(), $this->columnTimeTypes(), $columns); |
67
|
|
|
} |
68
|
|
|
|
69
|
|
|
public function columnTimeTypes(): array |
70
|
|
|
{ |
71
|
|
|
$columns = [ |
72
|
|
|
[ |
73
|
|
|
Schema::TYPE_DATETIME . ' NOT NULL', |
74
|
|
|
$this->dateTime()->notNull(), |
75
|
|
|
'datetime NOT NULL', |
76
|
|
|
], |
77
|
|
|
[ |
78
|
|
|
Schema::TYPE_DATETIME, |
79
|
|
|
$this->dateTime(), |
80
|
|
|
'datetime', |
81
|
|
|
], |
82
|
|
|
[ |
83
|
|
|
Schema::TYPE_TIME . ' NOT NULL', |
84
|
|
|
$this->time()->notNull(), |
85
|
|
|
'time NOT NULL', |
86
|
|
|
], |
87
|
|
|
[ |
88
|
|
|
Schema::TYPE_TIME, |
89
|
|
|
$this->time(), |
90
|
|
|
'time', |
91
|
|
|
], |
92
|
|
|
[ |
93
|
|
|
Schema::TYPE_TIMESTAMP . ' NOT NULL', |
94
|
|
|
$this->timestamp()->notNull(), |
95
|
|
|
'timestamp NOT NULL', |
96
|
|
|
], |
97
|
|
|
[ |
98
|
|
|
Schema::TYPE_TIMESTAMP . ' NULL DEFAULT NULL', |
99
|
|
|
$this->timestamp()->defaultValue(null), |
100
|
|
|
'timestamp NULL DEFAULT NULL', |
101
|
|
|
], |
102
|
|
|
]; |
103
|
|
|
|
104
|
|
|
/** |
105
|
|
|
* @link https://github.com/yiisoft/yii2/issues/14367 |
106
|
|
|
*/ |
107
|
|
|
$mysqlVersion = $this->getDb()->getSlavePdo()->getAttribute(\PDO::ATTR_SERVER_VERSION); |
108
|
|
|
$supportsFractionalSeconds = version_compare($mysqlVersion, '5.6.4', '>='); |
109
|
|
|
|
110
|
|
|
if ($supportsFractionalSeconds) { |
111
|
|
|
$expectedValues = [ |
112
|
|
|
'datetime(0) NOT NULL', |
113
|
|
|
'datetime(0)', |
114
|
|
|
'time(0) NOT NULL', |
115
|
|
|
'time(0)', |
116
|
|
|
'timestamp(0) NOT NULL', |
117
|
|
|
'timestamp(0) NULL DEFAULT NULL', |
118
|
|
|
]; |
119
|
|
|
|
120
|
|
|
foreach ($expectedValues as $index => $expected) { |
121
|
|
|
$columns[$index][2] = $expected; |
122
|
|
|
} |
123
|
|
|
} |
124
|
|
|
|
125
|
|
|
/** |
126
|
|
|
* @link https://github.com/yiisoft/yii2/issues/14834 |
127
|
|
|
*/ |
128
|
|
|
$sqlModes = $this->getConnection(false)->createCommand('SELECT @@sql_mode')->queryScalar(); |
129
|
|
|
$sqlModes = explode(',', $sqlModes); |
|
|
|
|
130
|
|
|
if (in_array('NO_ZERO_DATE', $sqlModes, true)) { |
131
|
|
|
$this->markTestIncomplete( |
132
|
|
|
"MySQL doesn't allow the 'TIMESTAMP' column definition when the NO_ZERO_DATE mode enabled. " . |
133
|
|
|
"This definition test was skipped." |
134
|
|
|
); |
135
|
|
|
} else { |
136
|
|
|
$columns[] = [ |
137
|
|
|
Schema::TYPE_TIMESTAMP, |
138
|
|
|
$this->timestamp(), |
139
|
|
|
'timestamp', |
140
|
|
|
]; |
141
|
|
|
} |
142
|
|
|
|
143
|
|
|
return $columns; |
144
|
|
|
} |
145
|
|
|
|
146
|
|
|
public function primaryKeysProvider(): array |
147
|
|
|
{ |
148
|
|
|
$result = parent::primaryKeysProvider(); |
149
|
|
|
$result['drop'][0] = 'ALTER TABLE {{T_constraints_1}} DROP PRIMARY KEY'; |
150
|
|
|
$result['add'][0] = 'ALTER TABLE {{T_constraints_1}} ADD CONSTRAINT [[CN_pk]] PRIMARY KEY ([[C_id_1]])'; |
151
|
|
|
$result['add (2 columns)'][0] = 'ALTER TABLE {{T_constraints_1}} ADD CONSTRAINT [[CN_pk]] PRIMARY KEY ([[C_id_1]], [[C_id_2]])'; |
152
|
|
|
|
153
|
|
|
return $result; |
154
|
|
|
} |
155
|
|
|
|
156
|
|
|
public function foreignKeysProvider(): array |
157
|
|
|
{ |
158
|
|
|
$result = parent::foreignKeysProvider(); |
159
|
|
|
$result['drop'][0] = 'ALTER TABLE {{T_constraints_3}} DROP FOREIGN KEY [[CN_constraints_3]]'; |
160
|
|
|
|
161
|
|
|
return $result; |
162
|
|
|
} |
163
|
|
|
|
164
|
|
|
public function indexesProvider(): array |
165
|
|
|
{ |
166
|
|
|
$result = parent::indexesProvider(); |
167
|
|
|
$result['create'][0] = 'ALTER TABLE {{T_constraints_2}} ADD INDEX [[CN_constraints_2_single]] ([[C_index_1]])'; |
168
|
|
|
$result['create (2 columns)'][0] = 'ALTER TABLE {{T_constraints_2}} ADD INDEX [[CN_constraints_2_multi]] ([[C_index_2_1]], [[C_index_2_2]])'; |
169
|
|
|
$result['create unique'][0] = 'ALTER TABLE {{T_constraints_2}} ADD UNIQUE INDEX [[CN_constraints_2_single]] ([[C_index_1]])'; |
170
|
|
|
$result['create unique (2 columns)'][0] = 'ALTER TABLE {{T_constraints_2}} ADD UNIQUE INDEX [[CN_constraints_2_multi]] ([[C_index_2_1]], [[C_index_2_2]])'; |
171
|
|
|
|
172
|
|
|
return $result; |
173
|
|
|
} |
174
|
|
|
|
175
|
|
|
public function uniquesProvider(): array |
176
|
|
|
{ |
177
|
|
|
$result = parent::uniquesProvider(); |
178
|
|
|
$result['drop'][0] = 'DROP INDEX [[CN_unique]] ON {{T_constraints_1}}'; |
179
|
|
|
|
180
|
|
|
return $result; |
181
|
|
|
} |
182
|
|
|
|
183
|
|
|
public function checksProvider(): void |
184
|
|
|
{ |
185
|
|
|
$this->markTestSkipped('Adding/dropping check constraints is not supported in MySQL.'); |
186
|
|
|
} |
187
|
|
|
|
188
|
|
|
public function defaultValuesProvider() |
189
|
|
|
{ |
190
|
|
|
$this->markTestSkipped('Adding/dropping default constraints is not supported in MySQL.'); |
191
|
|
|
} |
192
|
|
|
|
193
|
|
|
public function testResetSequence(): void |
194
|
|
|
{ |
195
|
|
|
$qb = $this->getQueryBuilder(true, true); |
196
|
|
|
|
197
|
|
|
$expected = 'ALTER TABLE `item` AUTO_INCREMENT=6'; |
198
|
|
|
$sql = $qb->resetSequence('item'); |
199
|
|
|
$this->assertEquals($expected, $sql); |
200
|
|
|
|
201
|
|
|
$expected = 'ALTER TABLE `item` AUTO_INCREMENT=4'; |
202
|
|
|
$sql = $qb->resetSequence('item', 4); |
203
|
|
|
$this->assertEquals($expected, $sql); |
204
|
|
|
} |
205
|
|
|
|
206
|
|
|
public function upsertProvider(): array |
207
|
|
|
{ |
208
|
|
|
$concreteData = [ |
209
|
|
|
'regular values' => [ |
210
|
|
|
3 => '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`)', |
211
|
|
|
], |
212
|
|
|
'regular values with update part' => [ |
213
|
|
|
3 => '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', |
214
|
|
|
], |
215
|
|
|
'regular values without update part' => [ |
216
|
|
|
3 => 'INSERT INTO `T_upsert` (`email`, `address`, `status`, `profile_id`) VALUES (:qp0, :qp1, :qp2, :qp3) ON DUPLICATE KEY UPDATE `email`=`T_upsert`.`email`', |
217
|
|
|
], |
218
|
|
|
'query' => [ |
219
|
|
|
3 => '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`)', |
220
|
|
|
], |
221
|
|
|
'query with update part' => [ |
222
|
|
|
3 => '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', |
223
|
|
|
], |
224
|
|
|
'query without update part' => [ |
225
|
|
|
3 => '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`', |
226
|
|
|
], |
227
|
|
|
'values and expressions' => [ |
228
|
|
|
3 => 'INSERT INTO {{%T_upsert}} ({{%T_upsert}}.[[email]], [[ts]]) VALUES (:qp0, now())', |
229
|
|
|
], |
230
|
|
|
'values and expressions with update part' => [ |
231
|
|
|
3 => 'INSERT INTO {{%T_upsert}} ({{%T_upsert}}.[[email]], [[ts]]) VALUES (:qp0, now())', |
232
|
|
|
], |
233
|
|
|
'values and expressions without update part' => [ |
234
|
|
|
3 => 'INSERT INTO {{%T_upsert}} ({{%T_upsert}}.[[email]], [[ts]]) VALUES (:qp0, now())', |
235
|
|
|
], |
236
|
|
|
'query, values and expressions with update part' => [ |
237
|
|
|
3 => 'INSERT INTO {{%T_upsert}} (`email`, [[time]]) SELECT :phEmail AS `email`, now() AS [[time]] ON DUPLICATE KEY UPDATE `ts`=:qp1, [[orders]]=T_upsert.orders + 1', |
238
|
|
|
], |
239
|
|
|
'query, values and expressions without update part' => [ |
240
|
|
|
3 => 'INSERT INTO {{%T_upsert}} (`email`, [[time]]) SELECT :phEmail AS `email`, now() AS [[time]] ON DUPLICATE KEY UPDATE `ts`=:qp1, [[orders]]=T_upsert.orders + 1', |
241
|
|
|
], |
242
|
|
|
'no columns to update' => [ |
243
|
|
|
3 => 'INSERT INTO `T_upsert_1` (`a`) VALUES (:qp0) ON DUPLICATE KEY UPDATE `a`=`T_upsert_1`.`a`', |
244
|
|
|
], |
245
|
|
|
]; |
246
|
|
|
|
247
|
|
|
$newData = parent::upsertProvider(); |
248
|
|
|
|
249
|
|
|
foreach ($concreteData as $testName => $data) { |
250
|
|
|
$newData[$testName] = array_replace($newData[$testName], $data); |
251
|
|
|
} |
252
|
|
|
|
253
|
|
|
return $newData; |
254
|
|
|
} |
255
|
|
|
|
256
|
|
|
public function conditionProvider(): array |
257
|
|
|
{ |
258
|
|
|
$db = $this->createConnection(); |
259
|
|
|
|
260
|
|
|
return array_merge(parent::conditionProvider(), [ |
261
|
|
|
// json conditions |
262
|
|
|
[ |
263
|
|
|
['=', 'jsoncol', new JsonExpression(['lang' => 'uk', 'country' => 'UA'])], |
264
|
|
|
'[[jsoncol]] = CAST(:qp0 AS JSON)', [':qp0' => '{"lang":"uk","country":"UA"}'], |
265
|
|
|
], |
266
|
|
|
[ |
267
|
|
|
['=', 'jsoncol', new JsonExpression([false])], |
268
|
|
|
'[[jsoncol]] = CAST(:qp0 AS JSON)', [':qp0' => '[false]'] |
269
|
|
|
], |
270
|
|
|
'object with type. Type is ignored for MySQL' => [ |
271
|
|
|
['=', 'prices', new JsonExpression(['seeds' => 15, 'apples' => 25], 'jsonb')], |
272
|
|
|
'[[prices]] = CAST(:qp0 AS JSON)', [':qp0' => '{"seeds":15,"apples":25}'], |
273
|
|
|
], |
274
|
|
|
'nested json' => [ |
275
|
|
|
['=', 'data', new JsonExpression(['user' => ['login' => 'silverfire', 'password' => 'c4ny0ur34d17?'], 'props' => ['mood' => 'good']])], |
276
|
|
|
'[[data]] = CAST(:qp0 AS JSON)', [':qp0' => '{"user":{"login":"silverfire","password":"c4ny0ur34d17?"},"props":{"mood":"good"}}'] |
277
|
|
|
], |
278
|
|
|
'null value' => [ |
279
|
|
|
['=', 'jsoncol', new JsonExpression(null)], |
280
|
|
|
'[[jsoncol]] = CAST(:qp0 AS JSON)', [':qp0' => 'null'] |
281
|
|
|
], |
282
|
|
|
'null as array value' => [ |
283
|
|
|
['=', 'jsoncol', new JsonExpression([null])], |
284
|
|
|
'[[jsoncol]] = CAST(:qp0 AS JSON)', [':qp0' => '[null]'] |
285
|
|
|
], |
286
|
|
|
'null as object value' => [ |
287
|
|
|
['=', 'jsoncol', new JsonExpression(['nil' => null])], |
288
|
|
|
'[[jsoncol]] = CAST(:qp0 AS JSON)', [':qp0' => '{"nil":null}'] |
289
|
|
|
], |
290
|
|
|
/*'with object as value' => [ |
291
|
|
|
['=', 'jsoncol', new JsonExpression(new DynamicModel(['a' => 1, 'b' => 2]))], |
292
|
|
|
'[[jsoncol]] = CAST(:qp0 AS JSON)', [':qp0' => '{"a":1,"b":2}'] |
293
|
|
|
],*/ |
294
|
|
|
'query' => [ |
295
|
|
|
['=', 'jsoncol', new JsonExpression((new Query($db))->select('params')->from('user')->where(['id' => 1]))], |
296
|
|
|
'[[jsoncol]] = (SELECT [[params]] FROM [[user]] WHERE [[id]]=:qp0)', [':qp0' => 1] |
297
|
|
|
], |
298
|
|
|
'query with type, that is ignored in MySQL' => [ |
299
|
|
|
['=', 'jsoncol', new JsonExpression((new Query($db))->select('params')->from('user')->where(['id' => 1]), 'jsonb')], |
300
|
|
|
'[[jsoncol]] = (SELECT [[params]] FROM [[user]] WHERE [[id]]=:qp0)', [':qp0' => 1] |
301
|
|
|
], |
302
|
|
|
'nested and combined json expression' => [ |
303
|
|
|
['=', 'jsoncol', new JsonExpression(new JsonExpression(['a' => 1, 'b' => 2, 'd' => new JsonExpression(['e' => 3])]))], |
304
|
|
|
"[[jsoncol]] = CAST(:qp0 AS JSON)", [':qp0' => '{"a":1,"b":2,"d":{"e":3}}'] |
305
|
|
|
], |
306
|
|
|
'search by property in JSON column (issue #15838)' => [ |
307
|
|
|
['=', new Expression("(jsoncol->>'$.someKey')"), '42'], |
308
|
|
|
"(jsoncol->>'$.someKey') = :qp0", [':qp0' => 42] |
309
|
|
|
] |
310
|
|
|
]); |
311
|
|
|
} |
312
|
|
|
|
313
|
|
|
public function updateProvider(): array |
314
|
|
|
{ |
315
|
|
|
$items = parent::updateProvider(); |
316
|
|
|
|
317
|
|
|
$items[] = [ |
318
|
|
|
'profile', |
319
|
|
|
[ |
320
|
|
|
'description' => new JsonExpression(['abc' => 'def', 123, null]), |
321
|
|
|
], |
322
|
|
|
[ |
323
|
|
|
'id' => 1, |
324
|
|
|
], |
325
|
|
|
$this->replaceQuotes('UPDATE [[profile]] SET [[description]]=CAST(:qp0 AS JSON) WHERE [[id]]=:qp1'), |
326
|
|
|
[ |
327
|
|
|
':qp0' => '{"abc":"def","0":123,"1":null}', |
328
|
|
|
':qp1' => 1, |
329
|
|
|
], |
330
|
|
|
]; |
331
|
|
|
|
332
|
|
|
return $items; |
333
|
|
|
} |
334
|
|
|
|
335
|
|
|
public function testIssue17449(): void |
336
|
|
|
{ |
337
|
|
|
$db = $this->getConnection(); |
338
|
|
|
$pdo = $db->getPDO(); |
339
|
|
|
$pdo->exec('DROP TABLE IF EXISTS `issue_17449`'); |
340
|
|
|
|
341
|
|
|
$tableQuery = <<<MySqlStatement |
342
|
|
|
CREATE TABLE `issue_17449` ( |
343
|
|
|
`test_column` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'some comment' CHECK (json_valid(`test_column`)) |
344
|
|
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
345
|
|
|
MySqlStatement; |
346
|
|
|
$db->createCommand($tableQuery)->execute(); |
347
|
|
|
|
348
|
|
|
$actual = $db->createCommand()->addCommentOnColumn('issue_17449', 'test_column', 'Some comment')->getRawSql(); |
349
|
|
|
|
350
|
|
|
$checkPos = stripos($actual, 'check'); |
351
|
|
|
if ($checkPos === false) { |
352
|
|
|
$this->markTestSkipped("The used MySql-Server removed or moved the CHECK from the column line, so the original bug doesn't affect it"); |
353
|
|
|
} |
354
|
|
|
$commentPos = stripos($actual, 'comment'); |
355
|
|
|
$this->assertNotFalse($commentPos); |
356
|
|
|
$this->assertLessThan($checkPos, $commentPos); |
357
|
|
|
} |
358
|
|
|
} |
359
|
|
|
|