Passed
Push — master ( c5d372...0a085e )
by Alexander
01:31
created

QueryBuilderTest::updateProvider()   A

Complexity

Conditions 1
Paths 1

Size

Total Lines 20
Code Lines 9

Duplication

Lines 0
Ratio 0 %

Importance

Changes 0
Metric Value
cc 1
eloc 9
nc 1
nop 0
dl 0
loc 20
rs 9.9666
c 0
b 0
f 0
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);
0 ignored issues
show
Bug introduced by
It seems like $sqlModes can also be of type false; however, parameter $string of explode() does only seem to accept string, maybe add an additional type check? ( Ignorable by Annotation )

If this is a false-positive, you can also ignore this issue in your code via the ignore-type  annotation

129
        $sqlModes = explode(',', /** @scrutinizer ignore-type */ $sqlModes);
Loading history...
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