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
|
<?php
declare(strict_types=1);
namespace PhpMyAdmin\SqlParser\Tests\Builder;
use PhpMyAdmin\SqlParser\Parser;
use PhpMyAdmin\SqlParser\Tests\TestCase;
class SelectStatementTest extends TestCase
{
public function testBuilder()
{
$query = 'SELECT * FROM t1 LEFT JOIN (t2, t3, t4) '
. 'ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)';
$parser = new Parser($query);
$stmt = $parser->statements[0];
$this->assertEquals(
'SELECT * FROM t1 LEFT JOIN (t2, t3, t4) '
. 'ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)',
$stmt->build()
);
}
public function testBuilderUnion()
{
$parser = new Parser('SELECT 1 UNION SELECT 2');
$stmt = $parser->statements[0];
$this->assertEquals(
'SELECT 1 UNION SELECT 2',
$stmt->build()
);
}
public function testBuilderAlias()
{
$parser = new Parser(
'SELECT sgu.id, sgu.email_address FROM `sf_guard_user` sgu '
. 'RIGHT JOIN `student_course_booking` scb ON sgu.id = scb.user_id '
. 'WHERE `has_found_course` = \'1\' GROUP BY sgu.id '
. 'ORDER BY scb.id DESC LIMIT 0,300'
);
$stmt = $parser->statements[0];
$this->assertEquals(
'SELECT sgu.id, sgu.email_address FROM `sf_guard_user` AS `sgu` '
. 'RIGHT JOIN `student_course_booking` AS `scb` ON sgu.id = scb.user_id '
. 'WHERE `has_found_course` = \'1\' GROUP BY sgu.id '
. 'ORDER BY scb.id DESC LIMIT 0, 300',
$stmt->build()
);
}
public function testBuilderEndOptions()
{
/* Assertion 1 */
$query = 'SELECT pid, name2 FROM tablename WHERE pid = 20 FOR UPDATE';
$parser = new Parser($query);
$stmt = $parser->statements[0];
$this->assertEquals(
$query,
$stmt->build()
);
/* Assertion 2 */
$query = 'SELECT pid, name2 FROM tablename WHERE pid = 20 LOCK IN SHARE MODE';
$parser = new Parser($query);
$stmt = $parser->statements[0];
$this->assertEquals(
$query,
$stmt->build()
);
}
public function testBuilderIntoOptions()
{
/* Assertion 1 */
$query = 'SELECT a, b, a+b INTO OUTFILE "/tmp/result.txt"'
. ' COLUMNS TERMINATED BY \',\' OPTIONALLY ENCLOSED BY \'"\''
. ' LINES TERMINATED BY \'\n\''
. ' FROM test_table';
$parser = new Parser($query);
$stmt = $parser->statements[0];
$this->assertEquals(
$query,
$stmt->build()
);
}
public function testBuilderGroupBy()
{
$query = 'SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country';
$parser = new Parser($query);
$stmt = $parser->statements[0];
$this->assertEquals(
$query,
$stmt->build()
);
}
public function testBuilderIndexHint()
{
$query = 'SELECT * FROM address FORCE INDEX (idx_fk_city_id) IGNORE KEY FOR GROUP BY (a, b,c) WHERE city_id<0';
$parser = new Parser($query);
$stmt = $parser->statements[0];
$this->assertEquals(
$query,
$stmt->build()
);
}
public function testBuilderSurroundedByParanthesisWithLimit()
{
$query = '(SELECT first_name FROM `actor` LIMIT 1, 2)';
$parser = new Parser($query);
$stmt = $parser->statements[0];
$this->assertEquals(
'SELECT first_name FROM `actor` LIMIT 1, 2',
$stmt->build()
);
}
}
|
