1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
|
<?php
declare(strict_types=1);
namespace PhpMyAdmin\SqlParser\Tests\Builder;
use Generator;
use PhpMyAdmin\SqlParser\Parser;
use PhpMyAdmin\SqlParser\Tests\TestCase;
use PHPUnit\Framework\Attributes\DataProvider;
class AlterStatementTest extends TestCase
{
public function testBuilder(): void
{
$query = 'ALTER TABLE `actor` ' .
'ADD PRIMARY KEY (`actor_id`), ' .
'ADD KEY `idx_actor_last_name` (`last_name`)';
$parser = new Parser($query);
$stmt = $parser->statements[0];
$this->assertEquals($query, $stmt->build());
}
public function testBuilderWithExpression(): void
{
$query = 'ALTER TABLE `table` '
. 'ADD UNIQUE KEY `functional_index`'
. ' (`field1`,`field2`, (IFNULL(`field3`,0)))';
$parser = new Parser($query);
$stmt = $parser->statements[0];
$this->assertEquals($query, $stmt->build());
}
public function testBuilderWithComments(): void
{
$query = 'ALTER /* comment */ TABLE `actor` ' .
'ADD PRIMARY KEY (`actor_id`), -- comment at the end of the line' . "\n" .
'ADD KEY `idx_actor_last_name` (`last_name`) -- and that is the last comment.';
$expectedQuery = 'ALTER TABLE `actor` ' .
'ADD PRIMARY KEY (`actor_id`), ' .
'ADD KEY `idx_actor_last_name` (`last_name`)';
$parser = new Parser($query);
$stmt = $parser->statements[0];
$this->assertEquals($expectedQuery, $stmt->build());
}
public function testBuilderWithCommentsOnOptions(): void
{
$query = 'ALTER EVENT `myEvent` /* comment */ ' .
'ON SCHEDULE -- Comment at the end of the line' . "\n" .
'AT "2023-01-01 01:23:45"';
$expectedQuery = 'ALTER EVENT `myEvent` ' .
'ON SCHEDULE AT "2023-01-01 01:23:45"';
$parser = new Parser($query);
$stmt = $parser->statements[0];
$this->assertEquals($expectedQuery, $stmt->build());
}
public function testBuilderCompressed(): void
{
$query = 'ALTER TABLE `user` CHANGE `message` `message` TEXT COMPRESSED';
$parser = new Parser($query);
$stmt = $parser->statements[0];
$this->assertEquals($query, $stmt->build());
}
public function testBuilderPartitions(): void
{
$parser = new Parser('ALTER TABLE t1 PARTITION BY HASH(id) PARTITIONS 8');
$stmt = $parser->statements[0];
$this->assertEquals('ALTER TABLE t1 PARTITION BY HASH(id) PARTITIONS 8', $stmt->build());
$parser = new Parser('ALTER TABLE t1 ADD PARTITION (PARTITION p3 VALUES LESS THAN (2002))');
$stmt = $parser->statements[0];
$this->assertEquals(
"ALTER TABLE t1 ADD PARTITION (\n" .
"PARTITION p3 VALUES LESS THAN (2002)\n" .
')',
$stmt->build()
);
$parser = new Parser('ALTER TABLE p PARTITION BY LINEAR KEY ALGORITHM=2 (id) PARTITIONS 32;');
$stmt = $parser->statements[0];
$this->assertEquals(
'ALTER TABLE p PARTITION BY LINEAR KEY ALGORITHM=2 (id) PARTITIONS 32',
$stmt->build()
);
$parser = new Parser('ALTER TABLE t1 DROP PARTITION p0, p1;');
$stmt = $parser->statements[0];
$this->assertEquals(
'ALTER TABLE t1 DROP PARTITION p0, p1',
$stmt->build()
);
$parser = new Parser(
'ALTER TABLE trips PARTITION BY RANGE (MONTH(trip_date))'
. ' (' . "\n"
. ' PARTITION p01 VALUES LESS THAN (02),' . "\n"
. ' PARTITION p02 VALUES LESS THAN (03),' . "\n"
. ' PARTITION p03 VALUES LESS THAN (04),' . "\n"
. ' PARTITION p04 VALUES LESS THAN (05),' . "\n"
. ' PARTITION p05 VALUES LESS THAN (06),' . "\n"
. ' PARTITION p06 VALUES LESS THAN (07),' . "\n"
. ' PARTITION p07 VALUES LESS THAN (08),' . "\n"
. ' PARTITION p08 VALUES LESS THAN (09),' . "\n"
. ' PARTITION p09 VALUES LESS THAN (10),' . "\n"
. ' PARTITION p10 VALUES LESS THAN (11),' . "\n"
. ' PARTITION p11 VALUES LESS THAN (12),' . "\n"
. ' PARTITION p12 VALUES LESS THAN (13),' . "\n"
. ' PARTITION pmaxval VALUES LESS THAN MAXVALUE' . "\n"
. ');'
);
$stmt = $parser->statements[0];
$this->assertEquals(
'ALTER TABLE trips PARTITION BY RANGE (MONTH(trip_date)) (' . "\n"
. 'PARTITION p01 VALUES LESS THAN (02),' . "\n"
. 'PARTITION p02 VALUES LESS THAN (03),' . "\n"
. 'PARTITION p03 VALUES LESS THAN (04),' . "\n"
. 'PARTITION p04 VALUES LESS THAN (05),' . "\n"
. 'PARTITION p05 VALUES LESS THAN (06),' . "\n"
. 'PARTITION p06 VALUES LESS THAN (07),' . "\n"
. 'PARTITION p07 VALUES LESS THAN (08),' . "\n"
. 'PARTITION p08 VALUES LESS THAN (09),' . "\n"
. 'PARTITION p09 VALUES LESS THAN (10),' . "\n"
. 'PARTITION p10 VALUES LESS THAN (11),' . "\n"
. 'PARTITION p11 VALUES LESS THAN (12),' . "\n"
. 'PARTITION p12 VALUES LESS THAN (13),' . "\n"
. 'PARTITION pmaxval VALUES LESS THAN MAXVALUE' . "\n"
. ')',
$stmt->build()
);
}
public function testBuilderEventWithDefiner(): void
{
$query = 'ALTER DEFINER=user EVENT myEvent ENABLE';
$parser = new Parser($query);
$stmt = $parser->statements[0];
$this->assertEquals($query, $stmt->build());
}
/**
* @return Generator<string, array{string}>
*/
public static function provideBuilderForRenameColumn(): Generator
{
$query = 'ALTER TABLE myTable RENAME COLUMN a TO b';
yield 'Single RENAME COLUMN' => [$query];
$query = 'ALTER TABLE myTable RENAME COLUMN a TO b, RENAME COLUMN b TO a';
yield 'Multiple RENAME COLUMN' => [$query];
$query = 'ALTER TABLE myTable ' .
'RENAME COLUMN a TO b, ' .
'RENAME COLUMN b TO a, ' .
'RENAME INDEX oldIndex TO newIndex, ' .
'RENAME TO newTable';
yield 'Mixed RENAME COLUMN + RENAME INDEX + RENAME table' => [$query];
$query = 'ALTER TABLE myTable ' .
'RENAME TO newTable, ' .
'RENAME INDEX oldIndex TO newIndex, ' .
'RENAME COLUMN b TO a, ' .
'RENAME COLUMN a TO b';
yield 'Mixed RENAME table + RENAME INDEX + RENAME COLUMNS' => [$query];
}
#[DataProvider('provideBuilderForRenameColumn')]
public function testBuilderRenameColumn(string $query): void
{
$parser = new Parser($query);
$stmt = $parser->statements[0];
$this->assertEquals($query, $stmt->build());
}
/**
* @return Generator<string, array{string}>
*/
public static function provideBuilderForAlterRoutine(): Generator
{
$query = 'ALTER FUNCTION func_name COMMENT "test"';
yield 'Function with only comment' => [$query];
$query = 'ALTER FUNCTION func_name LANGUAGE SQL';
yield 'Function with only language' => [$query];
$query = 'ALTER FUNCTION func_name COMMENT "test" LANGUAGE SQL CONTAINS SQL SQL SECURITY DEFINER';
yield 'Function with all options combinations #1' => [$query];
$query = 'ALTER FUNCTION func_name COMMENT "test" LANGUAGE SQL NO SQL SQL SECURITY INVOKER';
yield 'Function with all options combinations #2' => [$query];
$query = 'ALTER FUNCTION func_name COMMENT "test" LANGUAGE SQL READS SQL DATA';
yield 'Function with all remaining options #1' => [$query];
$query = 'ALTER FUNCTION func_name COMMENT "test" LANGUAGE SQL MODIFIES SQL DATA';
yield 'Function with all remaining options #2' => [$query];
$query = 'ALTER PROCEDURE func_name COMMENT "test"';
yield 'Procedure with only comment' => [$query];
$query = 'ALTER PROCEDURE proc_name LANGUAGE SQL';
yield 'Procedure with only language' => [$query];
$query = 'ALTER PROCEDURE proc_name COMMENT "test" LANGUAGE SQL CONTAINS SQL SQL SECURITY DEFINER';
yield 'Procedure with all options combinations #1' => [$query];
$query = 'ALTER PROCEDURE proc_name COMMENT "test" LANGUAGE SQL NO SQL SQL SECURITY INVOKER';
yield 'Procedure with all options combinations #2' => [$query];
$query = 'ALTER PROCEDURE proc_name COMMENT "test" LANGUAGE SQL READS SQL DATA';
yield 'Procedure with all remaining options #1' => [$query];
$query = 'ALTER PROCEDURE proc_name COMMENT "test" LANGUAGE SQL MODIFIES SQL DATA';
yield 'Procedure with all remaining options #2' => [$query];
}
#[DataProvider('provideBuilderForAlterRoutine')]
public function testBuilderForAlterRoutine(string $query): void
{
$parser = new Parser($query);
$stmt = $parser->statements[0];
$this->assertEquals($query, $stmt->build());
}
}
|
