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
|
<?php
namespace Wikimedia\Tests\Rdbms;
use MediaWikiUnitTestCase;
use Wikimedia\Rdbms\Platform\SQLPlatform;
use Wikimedia\Rdbms\QueryBuilderFromRawSql;
/**
* @covers \Wikimedia\Rdbms\QueryBuilderFromRawSql
*/
class QueryBuilderFromRawSqlTest extends MediaWikiUnitTestCase {
/**
* @dataProvider provideBuildQuery
*/
public function testBuildQuery( $sql, $expectedVerb, $expectedFlags ) {
$query = QueryBuilderFromRawSql::buildQuery( $sql, 0 );
$this->assertSame( $expectedVerb, $query->getVerb() );
$this->assertSame( $expectedFlags, $query->getFlags() );
}
public function provideBuildQuery() {
return [
[
'SELECT * FROM foo',
'SELECT', SQLPlatform::QUERY_CHANGE_NONE,
],
[
'SELECT * FROM foo LOCK IN SHARE MODE',
// should probably be QUERY_CHANGE_LOCKS but this is how we handle it today
'SELECT', SQLPlatform::QUERY_CHANGE_NONE,
],
[
'SELECT * FROM foo FOR UPDATE',
// should probably be QUERY_CHANGE_LOCKS but this is how we handle it today
'SELECT', SQLPlatform::QUERY_CHANGE_ROWS,
],
[
'INSERT INTO foo VALUES (1)',
'INSERT', SQLPlatform::QUERY_CHANGE_ROWS,
],
[
'INSERT INTO foo (SELECT * FROM bar)',
'INSERT', SQLPlatform::QUERY_CHANGE_ROWS,
],
[
'UPDATE foo SET bar = 1',
'UPDATE', SQLPlatform::QUERY_CHANGE_ROWS,
],
[
'DELETE FROM foo',
'DELETE', SQLPlatform::QUERY_CHANGE_ROWS,
],
[
'EXPLAIN INSERT INTO foo VALUES (1)',
'EXPLAIN', SQLPlatform::QUERY_CHANGE_NONE,
],
[
'BEGIN',
'BEGIN', SQLPlatform::QUERY_CHANGE_TRX,
],
[
'COMMIT',
'COMMIT', SQLPlatform::QUERY_CHANGE_TRX,
],
[
'ROLLBACK',
'ROLLBACK', SQLPlatform::QUERY_CHANGE_TRX,
],
[
'SAVEPOINT foo',
'SAVEPOINT', SQLPlatform::QUERY_CHANGE_TRX,
],
[
'RELEASE SAVEPOINT foo',
'RELEASE SAVEPOINT', SQLPlatform::QUERY_CHANGE_TRX,
],
[
'ROLLBACK TO SAVEPOINT foo',
'ROLLBACK TO SAVEPOINT', SQLPlatform::QUERY_CHANGE_TRX,
],
// Most DDL test cases don't match the documentation of Query::__construct() but
// they are accurate representation of the current logic.
[
'CREATE TABLE foo (id INT)',
'CREATE', SQLPlatform::QUERY_CHANGE_SCHEMA,
],
[
'CREATE TEMPORARY TABLE foo (id INT)',
'CREATE TEMPORARY', SQLPlatform::QUERY_CHANGE_SCHEMA,
],
[
'CREATE INDEX foo ON bar (baz)',
'CREATE INDEX', SQLPlatform::QUERY_CHANGE_SCHEMA,
],
[
'DROP TABLE foo',
'DROP', SQLPlatform::QUERY_CHANGE_SCHEMA,
],
[
'DROP INDEX foo ON bar',
'DROP INDEX', SQLPlatform::QUERY_CHANGE_SCHEMA,
],
[
'ALTER TABLE foo ADD COLUMN bar INT',
'ALTER', SQLPlatform::QUERY_CHANGE_SCHEMA,
],
[
'CREATE DATABASE foo',
'CREATE DATABASE', SQLPlatform::QUERY_CHANGE_SCHEMA,
],
[
'ALTER DATABASE foo',
'ALTER DATABASE', SQLPlatform::QUERY_CHANGE_SCHEMA,
],
[
'DROP DATABASE foo',
'DROP DATABASE', SQLPlatform::QUERY_CHANGE_SCHEMA,
],
];
}
/**
* @param string $sql
* @param bool $res
* @dataProvider provideIsWriteQuery
*/
public function testIsWriteQuery( string $sql, bool $res ) {
$query = QueryBuilderFromRawSql::buildQuery( $sql, 0 );
$this->assertSame( $res, $query->isWriteQuery() );
}
public static function provideIsWriteQuery(): array {
return [
[ 'SELECT foo', false ],
[ ' SELECT foo FROM bar', false ],
[ 'BEGIN', false ],
[ 'SHOW EXPLAIN FOR 12;', false ],
[ 'USE foobar', false ],
[ '(SELECT 1)', false ],
[ 'INSERT INTO foo', true ],
[ 'TRUNCATE bar', true ],
[ 'DELETE FROM baz', true ],
[ 'CREATE TABLE foobar', true ]
];
}
/**
* @dataProvider provideGeneralizeSQL
*/
public function testGeneralizeSQL( string $sql, string $generalizedSql ) {
// Note that the default pcre.backtrack_limit is 1000000
$this->assertSame( $generalizedSql, QueryBuilderFromRawSql::generalizeSQL( $sql ) );
}
public static function provideGeneralizeSQL() {
return [
[
'SELECT * FROM t1 WHERE c1 = 5',
'SELECT * FROM t1 WHERE c1 = N'
],
[
'SELECT * FROM t1 WHERE c1 = "A"',
'SELECT * FROM t1 WHERE c1 = \'X\''
],
[
'SELECT * FROM t1 WHERE c1 = \'A\'',
'SELECT * FROM t1 WHERE c1 = \'X\''
],
[
'SELECT * FROM t1 WHERE c1 IN (5,-6)',
'SELECT * FROM t1 WHERE c1 IN (N,...,N)'
],
[
'SELECT * FROM t1 WHERE c1 IN ("A","B")',
'SELECT * FROM t1 WHERE c1 IN (\'X\')'
],
[
'SELECT * FROM t2 WHERE c1 IN ("1","2","3","4")',
'SELECT * FROM t2 WHERE c1 IN (\'X\')'
],
[
'SELECT * FROM t3 WHERE c1 IN (' . implode( ',', range( -1, -5000 ) ) . ')',
'SELECT * FROM t3 WHERE c1 IN (N,...,N)'
]
];
}
}
|
