|
1
|
|
|
<?php |
|
2
|
|
|
|
|
3
|
|
|
declare(strict_types=1); |
|
4
|
|
|
|
|
5
|
|
|
namespace PhpMyAdmin\SqlParser\Statements; |
|
6
|
|
|
|
|
7
|
|
|
use PhpMyAdmin\SqlParser\Components\ArrayObj; |
|
8
|
|
|
use PhpMyAdmin\SqlParser\Components\Condition; |
|
9
|
|
|
use PhpMyAdmin\SqlParser\Components\Expression; |
|
10
|
|
|
use PhpMyAdmin\SqlParser\Components\FunctionCall; |
|
11
|
|
|
use PhpMyAdmin\SqlParser\Components\GroupKeyword; |
|
12
|
|
|
use PhpMyAdmin\SqlParser\Components\IndexHint; |
|
13
|
|
|
use PhpMyAdmin\SqlParser\Components\IntoKeyword; |
|
14
|
|
|
use PhpMyAdmin\SqlParser\Components\JoinKeyword; |
|
15
|
|
|
use PhpMyAdmin\SqlParser\Components\Limit; |
|
16
|
|
|
use PhpMyAdmin\SqlParser\Components\OptionsArray; |
|
17
|
|
|
use PhpMyAdmin\SqlParser\Components\OrderKeyword; |
|
18
|
|
|
use PhpMyAdmin\SqlParser\Statement; |
|
19
|
|
|
|
|
20
|
|
|
/** |
|
21
|
|
|
* `SELECT` statement. |
|
22
|
|
|
* |
|
23
|
|
|
* SELECT |
|
24
|
|
|
* [ALL | DISTINCT | DISTINCTROW ] |
|
25
|
|
|
* [HIGH_PRIORITY] |
|
26
|
|
|
* [MAX_STATEMENT_TIME = N] |
|
27
|
|
|
* [STRAIGHT_JOIN] |
|
28
|
|
|
* [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT] |
|
29
|
|
|
* [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS] |
|
30
|
|
|
* select_expr [, select_expr ...] |
|
31
|
|
|
* [FROM table_references |
|
32
|
|
|
* [PARTITION partition_list] |
|
33
|
|
|
* [WHERE where_condition] |
|
34
|
|
|
* [GROUP BY {col_name | expr | position} |
|
35
|
|
|
* [ASC | DESC], ... [WITH ROLLUP]] |
|
36
|
|
|
* [HAVING where_condition] |
|
37
|
|
|
* [ORDER BY {col_name | expr | position} |
|
38
|
|
|
* [ASC | DESC], ...] |
|
39
|
|
|
* [LIMIT {[offset,] row_count | row_count OFFSET offset}] |
|
40
|
|
|
* [PROCEDURE procedure_name(argument_list)] |
|
41
|
|
|
* [INTO OUTFILE 'file_name' |
|
42
|
|
|
* [CHARACTER SET charset_name] |
|
43
|
|
|
* export_options |
|
44
|
|
|
* | INTO DUMPFILE 'file_name' |
|
45
|
|
|
* | INTO var_name [, var_name]] |
|
46
|
|
|
* [FOR UPDATE | LOCK IN SHARE MODE]] |
|
47
|
|
|
*/ |
|
48
|
|
|
class SelectStatement extends Statement |
|
49
|
|
|
{ |
|
50
|
|
|
/** |
|
51
|
|
|
* Options for `SELECT` statements and their slot ID. |
|
52
|
|
|
* |
|
53
|
|
|
* @var array<string, int|array<int, int|string>> |
|
54
|
|
|
* @psalm-var array<string, (positive-int|array{positive-int, ('var'|'var='|'expr'|'expr=')})> |
|
55
|
|
|
*/ |
|
56
|
|
|
public static $statementOptions = [ |
|
57
|
|
|
'ALL' => 1, |
|
58
|
|
|
'DISTINCT' => 1, |
|
59
|
|
|
'DISTINCTROW' => 1, |
|
60
|
|
|
'HIGH_PRIORITY' => 2, |
|
61
|
|
|
'MAX_STATEMENT_TIME' => [ |
|
62
|
|
|
3, |
|
63
|
|
|
'var=', |
|
64
|
|
|
], |
|
65
|
|
|
'STRAIGHT_JOIN' => 4, |
|
66
|
|
|
'SQL_SMALL_RESULT' => 5, |
|
67
|
|
|
'SQL_BIG_RESULT' => 6, |
|
68
|
|
|
'SQL_BUFFER_RESULT' => 7, |
|
69
|
|
|
'SQL_CACHE' => 8, |
|
70
|
|
|
'SQL_NO_CACHE' => 8, |
|
71
|
|
|
'SQL_CALC_FOUND_ROWS' => 9, |
|
72
|
|
|
]; |
|
73
|
|
|
|
|
74
|
|
|
/** |
|
75
|
|
|
* @var array<string, int|array<int, int|string>> |
|
76
|
|
|
* @psalm-var array<string, (positive-int|array{positive-int, ('var'|'var='|'expr'|'expr=')})> |
|
77
|
|
|
*/ |
|
78
|
|
|
public static $statementGroupOptions = ['WITH ROLLUP' => 1]; |
|
79
|
|
|
|
|
80
|
|
|
/** |
|
81
|
|
|
* @var array<string, int|array<int, int|string>> |
|
82
|
|
|
* @psalm-var array<string, (positive-int|array{positive-int, ('var'|'var='|'expr'|'expr=')})> |
|
83
|
|
|
*/ |
|
84
|
|
|
public static $statementEndOptions = [ |
|
85
|
|
|
'FOR UPDATE' => 1, |
|
86
|
|
|
'LOCK IN SHARE MODE' => 1, |
|
87
|
|
|
]; |
|
88
|
|
|
|
|
89
|
|
|
/** |
|
90
|
|
|
* The clauses of this statement, in order. |
|
91
|
|
|
* |
|
92
|
|
|
* @see Statement::$clauses |
|
93
|
|
|
* |
|
94
|
|
|
* @var array<string, array<int, int|string>> |
|
95
|
|
|
* @psalm-var array<string, array{non-empty-string, (1|2|3)}> |
|
96
|
|
|
*/ |
|
97
|
|
|
public static $clauses = [ |
|
98
|
|
|
'SELECT' => [ |
|
99
|
|
|
'SELECT', |
|
100
|
|
|
2, |
|
101
|
|
|
], |
|
102
|
|
|
// Used for options. |
|
103
|
|
|
'_OPTIONS' => [ |
|
104
|
|
|
'_OPTIONS', |
|
105
|
|
|
1, |
|
106
|
|
|
], |
|
107
|
|
|
// Used for selected expressions. |
|
108
|
|
|
'_SELECT' => [ |
|
109
|
|
|
'SELECT', |
|
110
|
|
|
1, |
|
111
|
|
|
], |
|
112
|
|
|
'INTO' => [ |
|
113
|
|
|
'INTO', |
|
114
|
|
|
3, |
|
115
|
|
|
], |
|
116
|
|
|
'FROM' => [ |
|
117
|
|
|
'FROM', |
|
118
|
|
|
3, |
|
119
|
|
|
], |
|
120
|
|
|
'FORCE' => [ |
|
121
|
|
|
'FORCE', |
|
122
|
|
|
1, |
|
123
|
|
|
], |
|
124
|
|
|
'USE' => [ |
|
125
|
|
|
'USE', |
|
126
|
|
|
1, |
|
127
|
|
|
], |
|
128
|
|
|
'IGNORE' => [ |
|
129
|
|
|
'IGNORE', |
|
130
|
|
|
3, |
|
131
|
|
|
], |
|
132
|
|
|
'PARTITION' => [ |
|
133
|
|
|
'PARTITION', |
|
134
|
|
|
3, |
|
135
|
|
|
], |
|
136
|
|
|
|
|
137
|
|
|
'JOIN' => [ |
|
138
|
|
|
'JOIN', |
|
139
|
|
|
1, |
|
140
|
|
|
], |
|
141
|
|
|
'FULL JOIN' => [ |
|
142
|
|
|
'FULL JOIN', |
|
143
|
|
|
1, |
|
144
|
|
|
], |
|
145
|
|
|
'INNER JOIN' => [ |
|
146
|
|
|
'INNER JOIN', |
|
147
|
|
|
1, |
|
148
|
|
|
], |
|
149
|
|
|
'LEFT JOIN' => [ |
|
150
|
|
|
'LEFT JOIN', |
|
151
|
|
|
1, |
|
152
|
|
|
], |
|
153
|
|
|
'LEFT OUTER JOIN' => [ |
|
154
|
|
|
'LEFT OUTER JOIN', |
|
155
|
|
|
1, |
|
156
|
|
|
], |
|
157
|
|
|
'RIGHT JOIN' => [ |
|
158
|
|
|
'RIGHT JOIN', |
|
159
|
|
|
1, |
|
160
|
|
|
], |
|
161
|
|
|
'RIGHT OUTER JOIN' => [ |
|
162
|
|
|
'RIGHT OUTER JOIN', |
|
163
|
|
|
1, |
|
164
|
|
|
], |
|
165
|
|
|
'NATURAL JOIN' => [ |
|
166
|
|
|
'NATURAL JOIN', |
|
167
|
|
|
1, |
|
168
|
|
|
], |
|
169
|
|
|
'NATURAL LEFT JOIN' => [ |
|
170
|
|
|
'NATURAL LEFT JOIN', |
|
171
|
|
|
1, |
|
172
|
|
|
], |
|
173
|
|
|
'NATURAL RIGHT JOIN' => [ |
|
174
|
|
|
'NATURAL RIGHT JOIN', |
|
175
|
|
|
1, |
|
176
|
|
|
], |
|
177
|
|
|
'NATURAL LEFT OUTER JOIN' => [ |
|
178
|
|
|
'NATURAL LEFT OUTER JOIN', |
|
179
|
|
|
1, |
|
180
|
|
|
], |
|
181
|
|
|
'NATURAL RIGHT OUTER JOIN' => [ |
|
182
|
|
|
'NATURAL RIGHT JOIN', |
|
183
|
|
|
1, |
|
184
|
|
|
], |
|
185
|
|
|
'WHERE' => [ |
|
186
|
|
|
'WHERE', |
|
187
|
|
|
3, |
|
188
|
|
|
], |
|
189
|
|
|
'GROUP BY' => [ |
|
190
|
|
|
'GROUP BY', |
|
191
|
|
|
3, |
|
192
|
|
|
], |
|
193
|
|
|
'_GROUP_OPTIONS' => [ |
|
194
|
|
|
'_GROUP_OPTIONS', |
|
195
|
|
|
1, |
|
196
|
|
|
], |
|
197
|
|
|
'HAVING' => [ |
|
198
|
|
|
'HAVING', |
|
199
|
|
|
3, |
|
200
|
|
|
], |
|
201
|
|
|
'ORDER BY' => [ |
|
202
|
|
|
'ORDER BY', |
|
203
|
|
|
3, |
|
204
|
|
|
], |
|
205
|
|
|
'LIMIT' => [ |
|
206
|
|
|
'LIMIT', |
|
207
|
|
|
3, |
|
208
|
|
|
], |
|
209
|
|
|
'PROCEDURE' => [ |
|
210
|
|
|
'PROCEDURE', |
|
211
|
|
|
3, |
|
212
|
|
|
], |
|
213
|
|
|
'UNION' => [ |
|
214
|
|
|
'UNION', |
|
215
|
|
|
1, |
|
216
|
|
|
], |
|
217
|
|
|
'EXCEPT' => [ |
|
218
|
|
|
'EXCEPT', |
|
219
|
|
|
1, |
|
220
|
|
|
], |
|
221
|
|
|
'INTERSECT' => [ |
|
222
|
|
|
'INTERSECT', |
|
223
|
|
|
1, |
|
224
|
|
|
], |
|
225
|
|
|
'_END_OPTIONS' => [ |
|
226
|
|
|
'_END_OPTIONS', |
|
227
|
|
|
1, |
|
228
|
|
|
], |
|
229
|
|
|
// These are available only when `UNION` is present. |
|
230
|
|
|
// 'ORDER BY' => ['ORDER BY', 3], |
|
231
|
|
|
// 'LIMIT' => ['LIMIT', 3], |
|
232
|
|
|
]; |
|
233
|
|
|
|
|
234
|
|
|
/** |
|
235
|
|
|
* Expressions that are being selected by this statement. |
|
236
|
|
|
* |
|
237
|
|
|
* @var Expression[] |
|
238
|
|
|
*/ |
|
239
|
|
|
public $expr = []; |
|
240
|
|
|
|
|
241
|
|
|
/** |
|
242
|
|
|
* Tables used as sources for this statement. |
|
243
|
|
|
* |
|
244
|
|
|
* @var Expression[] |
|
245
|
|
|
*/ |
|
246
|
|
|
public $from = []; |
|
247
|
|
|
|
|
248
|
|
|
/** |
|
249
|
|
|
* Index hints |
|
250
|
|
|
* |
|
251
|
|
|
* @var IndexHint[]|null |
|
252
|
|
|
*/ |
|
253
|
|
|
public $indexHints; |
|
254
|
|
|
|
|
255
|
|
|
/** |
|
256
|
|
|
* Partitions used as source for this statement. |
|
257
|
|
|
* |
|
258
|
|
|
* @var ArrayObj|null |
|
259
|
|
|
*/ |
|
260
|
|
|
public $partition; |
|
261
|
|
|
|
|
262
|
|
|
/** |
|
263
|
|
|
* Conditions used for filtering each row of the result set. |
|
264
|
|
|
* |
|
265
|
|
|
* @var Condition[]|null |
|
266
|
|
|
*/ |
|
267
|
|
|
public $where; |
|
268
|
|
|
|
|
269
|
|
|
/** |
|
270
|
|
|
* Conditions used for grouping the result set. |
|
271
|
|
|
* |
|
272
|
|
|
* @var GroupKeyword[]|null |
|
273
|
|
|
*/ |
|
274
|
|
|
public $group; |
|
275
|
|
|
|
|
276
|
|
|
/** |
|
277
|
|
|
* List of options available for the GROUP BY component. |
|
278
|
|
|
* |
|
279
|
|
|
* @var OptionsArray|null |
|
280
|
|
|
*/ |
|
281
|
|
|
public $groupOptions; |
|
282
|
|
|
|
|
283
|
|
|
/** |
|
284
|
|
|
* Conditions used for filtering the result set. |
|
285
|
|
|
* |
|
286
|
|
|
* @var Condition[]|null |
|
287
|
|
|
*/ |
|
288
|
|
|
public $having; |
|
289
|
|
|
|
|
290
|
|
|
/** |
|
291
|
|
|
* Specifies the order of the rows in the result set. |
|
292
|
|
|
* |
|
293
|
|
|
* @var OrderKeyword[]|null |
|
294
|
|
|
*/ |
|
295
|
|
|
public $order; |
|
296
|
|
|
|
|
297
|
|
|
/** |
|
298
|
|
|
* Conditions used for limiting the size of the result set. |
|
299
|
|
|
* |
|
300
|
|
|
* @var Limit|null |
|
301
|
|
|
*/ |
|
302
|
|
|
public $limit; |
|
303
|
|
|
|
|
304
|
|
|
/** |
|
305
|
|
|
* Procedure that should process the data in the result set. |
|
306
|
|
|
* |
|
307
|
|
|
* @var FunctionCall|null |
|
308
|
|
|
*/ |
|
309
|
|
|
public $procedure; |
|
310
|
|
|
|
|
311
|
|
|
/** |
|
312
|
|
|
* Destination of this result set. |
|
313
|
|
|
* |
|
314
|
|
|
* @var IntoKeyword|null |
|
315
|
|
|
*/ |
|
316
|
|
|
public $into; |
|
317
|
|
|
|
|
318
|
|
|
/** |
|
319
|
|
|
* Joins. |
|
320
|
|
|
* |
|
321
|
|
|
* @var JoinKeyword[]|null |
|
322
|
|
|
*/ |
|
323
|
|
|
public $join; |
|
324
|
|
|
|
|
325
|
|
|
/** |
|
326
|
|
|
* Unions. |
|
327
|
|
|
* |
|
328
|
|
|
* @var SelectStatement[] |
|
329
|
|
|
*/ |
|
330
|
|
|
public $union = []; |
|
331
|
|
|
|
|
332
|
|
|
/** |
|
333
|
|
|
* The end options of this query. |
|
334
|
|
|
* |
|
335
|
|
|
* @see SelectStatement::$statementEndOptions |
|
336
|
|
|
* |
|
337
|
|
|
* @var OptionsArray|null |
|
338
|
|
|
*/ |
|
339
|
|
|
public $endOptions; |
|
340
|
|
|
|
|
341
|
|
|
/** |
|
342
|
|
|
* Gets the clauses of this statement. |
|
343
|
|
|
* |
|
344
|
|
|
* @return array<string, array<int, int|string>> |
|
345
|
|
|
* @psalm-return array<string, array{non-empty-string, (1|2|3)}> |
|
346
|
|
|
*/ |
|
347
|
350 |
|
public function getClauses() |
|
348
|
|
|
{ |
|
349
|
|
|
// This is a cheap fix for `SELECT` statements that contain `UNION`. |
|
350
|
|
|
// The `ORDER BY` and `LIMIT` clauses should be at the end of the |
|
351
|
|
|
// statement. |
|
352
|
350 |
|
if (! empty($this->union)) { |
|
353
|
10 |
|
$clauses = static::$clauses; |
|
354
|
10 |
|
unset($clauses['ORDER BY'], $clauses['LIMIT']); |
|
355
|
10 |
|
$clauses['ORDER BY'] = [ |
|
356
|
10 |
|
'ORDER BY', |
|
357
|
10 |
|
3, |
|
358
|
10 |
|
]; |
|
359
|
10 |
|
$clauses['LIMIT'] = [ |
|
360
|
10 |
|
'LIMIT', |
|
361
|
10 |
|
3, |
|
362
|
10 |
|
]; |
|
363
|
|
|
|
|
364
|
10 |
|
return $clauses; |
|
365
|
|
|
} |
|
366
|
|
|
|
|
367
|
350 |
|
return static::$clauses; |
|
368
|
|
|
} |
|
369
|
|
|
|
|
370
|
|
|
/** |
|
371
|
|
|
* Gets a list of all aliases and their original names. |
|
372
|
|
|
* |
|
373
|
|
|
* @param string $database the name of the database |
|
374
|
|
|
* |
|
375
|
|
|
* @return array<string, array<string, array<string, array<string, array<string, string>|string|null>>|null>> |
|
376
|
|
|
*/ |
|
377
|
14 |
|
public function getAliases(string $database): array |
|
378
|
|
|
{ |
|
379
|
14 |
|
if (empty($this->expr) || empty($this->from)) { |
|
380
|
2 |
|
return []; |
|
381
|
|
|
} |
|
382
|
|
|
|
|
383
|
12 |
|
$retval = []; |
|
384
|
|
|
|
|
385
|
12 |
|
$tables = []; |
|
386
|
|
|
|
|
387
|
|
|
/** |
|
388
|
|
|
* Expressions that may contain aliases. |
|
389
|
|
|
* These are extracted from `FROM` and `JOIN` keywords. |
|
390
|
|
|
*/ |
|
391
|
12 |
|
$expressions = $this->from; |
|
392
|
|
|
|
|
393
|
|
|
// Adding expressions from JOIN. |
|
394
|
12 |
|
if (! empty($this->join)) { |
|
395
|
2 |
|
foreach ($this->join as $join) { |
|
396
|
2 |
|
$expressions[] = $join->expr; |
|
397
|
|
|
} |
|
398
|
|
|
} |
|
399
|
|
|
|
|
400
|
12 |
|
foreach ($expressions as $expr) { |
|
401
|
12 |
|
if (! isset($expr->table) || ($expr->table === '')) { |
|
402
|
4 |
|
continue; |
|
403
|
|
|
} |
|
404
|
|
|
|
|
405
|
8 |
|
$thisDb = isset($expr->database) && ($expr->database !== '') ? |
|
406
|
8 |
|
$expr->database : $database; |
|
407
|
|
|
|
|
408
|
8 |
|
if (! isset($retval[$thisDb])) { |
|
409
|
8 |
|
$retval[$thisDb] = [ |
|
410
|
8 |
|
'alias' => null, |
|
411
|
8 |
|
'tables' => [], |
|
412
|
8 |
|
]; |
|
413
|
|
|
} |
|
414
|
|
|
|
|
415
|
8 |
|
if (! isset($retval[$thisDb]['tables'][$expr->table])) { |
|
416
|
8 |
|
$retval[$thisDb]['tables'][$expr->table] = [ |
|
417
|
8 |
|
'alias' => isset($expr->alias) && ($expr->alias !== '') ? |
|
418
|
6 |
|
$expr->alias : null, |
|
419
|
8 |
|
'columns' => [], |
|
420
|
8 |
|
]; |
|
421
|
|
|
} |
|
422
|
|
|
|
|
423
|
8 |
|
if (! isset($tables[$thisDb])) { |
|
424
|
8 |
|
$tables[$thisDb] = []; |
|
425
|
|
|
} |
|
426
|
|
|
|
|
427
|
8 |
|
$tables[$thisDb][$expr->alias] = $expr->table; |
|
428
|
|
|
} |
|
429
|
|
|
|
|
430
|
12 |
|
foreach ($this->expr as $expr) { |
|
431
|
12 |
|
if (! isset($expr->column, $expr->alias) || ($expr->column === '') || ($expr->alias === '')) { |
|
432
|
8 |
|
continue; |
|
433
|
|
|
} |
|
434
|
|
|
|
|
435
|
6 |
|
$thisDb = isset($expr->database) && ($expr->database !== '') ? |
|
436
|
6 |
|
$expr->database : $database; |
|
437
|
|
|
|
|
438
|
6 |
|
if (isset($expr->table) && ($expr->table !== '')) { |
|
439
|
4 |
|
$thisTable = $tables[$thisDb][$expr->table] ?? $expr->table; |
|
440
|
4 |
|
$retval[$thisDb]['tables'][$thisTable]['columns'][$expr->column] = $expr->alias; |
|
441
|
|
|
} else { |
|
442
|
6 |
|
foreach ($retval[$thisDb]['tables'] as &$table) { |
|
443
|
6 |
|
$table['columns'][$expr->column] = $expr->alias; |
|
444
|
|
|
} |
|
445
|
|
|
} |
|
446
|
|
|
} |
|
447
|
|
|
|
|
448
|
12 |
|
return $retval; |
|
449
|
|
|
} |
|
450
|
|
|
} |
|
451
|
|
|
|