-
Notifications
You must be signed in to change notification settings - Fork 15
/
MagicQueryTest.php
303 lines (238 loc) · 15.4 KB
/
MagicQueryTest.php
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
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
<?php
namespace Mouf\Database;
use Doctrine\Common\Cache\ArrayCache;
use Doctrine\DBAL\Schema\Schema;
use Mouf\Database\SchemaAnalyzer\SchemaAnalyzer;
class MagicQueryTest extends \PHPUnit_Framework_TestCase
{
public function testStandardSelect()
{
$magicQuery = new MagicQuery();
$sql = "SELECT GROUP_CONCAT(id SEPARATOR ', ') AS ids FROM users";
$this->assertEquals("SELECT GROUP_CONCAT(id SEPARATOR ', ') AS ids FROM users", self::simplifySql($magicQuery->build($sql)));
$sql = 'SELECT id FROM users WHERE name LIKE :name LIMIT :offset, :limit';
$this->assertEquals("SELECT id FROM users WHERE name LIKE 'foo'", self::simplifySql($magicQuery->build($sql, ['name' => 'foo'])));
$sql = 'SELECT id FROM users WHERE name LIKE :name LIMIT 2, :limit';
$this->assertEquals("SELECT id FROM users WHERE name LIKE 'foo' LIMIT 2, 10", self::simplifySql($magicQuery->build($sql, ['name' => 'foo', 'limit' => 10])));
try {
$exceptionOccurred = false;
$sql = 'SELECT id FROM users WHERE name LIKE :name LIMIT 2, :limit';
self::simplifySql($magicQuery->build($sql, ['name' => 'foo']));
} catch (\Exception $e) {
// We have no limit provided in the parameters so we test that the script return an exception for this case
$exceptionOccurred = true;
}
$this->assertEquals(true, $exceptionOccurred);
$sql = 'SELECT id FROM users WHERE name LIKE :name LIMIT :offset, 5';
$this->assertEquals("SELECT id FROM users WHERE name LIKE 'foo' LIMIT 0, 5", self::simplifySql($magicQuery->build($sql, ['name' => 'foo', 'offset' => 0])));
$sql = 'SELECT id FROM users WHERE name LIKE :name LIMIT :offset, 5';
$this->assertEquals("SELECT id FROM users WHERE name LIKE 'foo' LIMIT 5", self::simplifySql($magicQuery->build($sql, ['name' => 'foo'])));
$sql = 'SELECT id FROM users LIMIT 10';
$this->assertEquals('SELECT id FROM users LIMIT 10', self::simplifySql($magicQuery->build($sql)));
$sql = 'SELECT id FROM users LIMIT 10 OFFSET 20';
$this->assertEquals('SELECT id FROM users LIMIT 20, 10', self::simplifySql($magicQuery->build($sql)));
$sql = 'SELECT id FROM users WHERE name LIKE :name LIMIT :offset, :limit';
$this->assertEquals("SELECT id FROM users WHERE name LIKE 'foo' LIMIT 0, 20", self::simplifySql($magicQuery->build($sql, ['name' => 'foo', 'offset' => 0, 'limit' => 20])));
$sql = 'SELECT id FROM users WHERE name LIKE :name LIMIT 0, 20';
$this->assertEquals("SELECT id FROM users WHERE name LIKE 'foo' LIMIT 0, 20", self::simplifySql($magicQuery->build($sql, ['name' => 'foo'])));
$sql = "SELECT DATE_FORMAT(CURDATE(), '%d/%m/%Y') AS current_date FROM users WHERE name LIKE :name";
$this->assertEquals("SELECT DATE_FORMAT(CURDATE(), '%d/%m/%Y') AS current_date FROM users WHERE name LIKE 'foo'", self::simplifySql($magicQuery->build($sql, ['name' => 'foo'])));
$sql = 'SELECT YEAR(CURDATE()) AS current_year FROM users WHERE name LIKE :name';
$this->assertEquals("SELECT YEAR(CURDATE()) AS current_year FROM users WHERE name LIKE 'foo'", self::simplifySql($magicQuery->build($sql, ['name' => 'foo'])));
$sql = 'SELECT * FROM users';
$this->assertEquals($sql, self::simplifySql($magicQuery->build($sql)));
$sql = 'SELECT * FROM users WHERE name LIKE :name';
$this->assertEquals("SELECT * FROM users WHERE name LIKE 'foo'", self::simplifySql($magicQuery->build($sql, ['name' => 'foo'])));
$this->assertEquals('SELECT * FROM users', self::simplifySql($magicQuery->build($sql)));
$sql = 'SELECT SUM(users.age) FROM users WHERE name LIKE :name AND company LIKE :company';
$this->assertEquals("SELECT SUM(users.age) FROM users WHERE (name LIKE 'foo')", self::simplifySql($magicQuery->build($sql, ['name' => 'foo'])));
$this->assertEquals("SELECT SUM(users.age) FROM users WHERE (name LIKE 'foo') AND (company LIKE 'bar')", self::simplifySql($magicQuery->build($sql, ['name' => 'foo', 'company' => 'bar'])));
$sql = 'SELECT * FROM users WHERE status in :status';
$this->assertEquals("SELECT * FROM users WHERE status IN ('2','4')", self::simplifySql($magicQuery->build($sql, ['status' => [2, 4]])));
$sql = 'SELECT * FROM myTable where someField BETWEEN :value1 AND :value2';
$this->assertEquals("SELECT * FROM myTable WHERE someField BETWEEN '2' AND '4'", self::simplifySql($magicQuery->build($sql, ['value1' => 2, 'value2' => 4])));
$this->assertEquals("SELECT * FROM myTable WHERE someField >= '2'", self::simplifySql($magicQuery->build($sql, ['value1' => 2])));
$this->assertEquals("SELECT * FROM myTable WHERE someField <= '4'", self::simplifySql($magicQuery->build($sql, ['value2' => 4])));
$this->assertEquals('SELECT * FROM myTable', self::simplifySql($magicQuery->build($sql, [])));
// Triggers an "expression"
// TODO: find why it fails!
//$sql = 'SELECT * FROM (users) WHERE name LIKE :name';
//$this->assertEquals("SELECT * FROM users WHERE name LIKE 'foo'", self::simplifySql($magicQuery->build($sql, ['name' => 'foo'])));
//$this->assertEquals('SELECT * FROM users', self::simplifySql($magicQuery->build($sql)));
// Triggers a const node
$sql = 'SELECT id+1 FROM users';
$this->assertEquals("SELECT id + '1' FROM users", self::simplifySql($magicQuery->build($sql)));
// Tests parameters with a ! (to force NULL values)
// Bonus: the = transforms into a IS
$sql = 'SELECT * FROM users WHERE status = :status!';
$this->assertEquals('SELECT * FROM users WHERE status IS null', self::simplifySql($magicQuery->build($sql, ['status' => null])));
// Bonus 2: the <> transforms into a IS NOT NULL
$sql = 'SELECT * FROM users WHERE status <> :status!';
$this->assertEquals('SELECT * FROM users WHERE status IS NOT null', self::simplifySql($magicQuery->build($sql, ['status' => null])));
// Test CASE WHERE
$sql = "SELECT CASE WHEN status = 'on' THEN '1' WHEN status = 'off' THEN '0' ELSE '-1' END AS my_case FROM users";
$this->assertEquals("SELECT CASE WHEN status = 'on' THEN '1' WHEN status = 'off' THEN '0' ELSE '-1' END AS my_case FROM users", self::simplifySql($magicQuery->build($sql)));
// Test CASE WHERE like SWITCH CASE
$sql = "SELECT CASE status WHEN 'on' THEN '1' WHEN 'off' THEN '0' ELSE '-1' END AS my_case FROM users";
$this->assertEquals("SELECT CASE status WHEN 'on' THEN '1' WHEN 'off' THEN '0' ELSE '-1' END AS my_case FROM users", self::simplifySql($magicQuery->build($sql)));
$sql = 'SELECT * FROM users WHERE status IN :statuses!';
$this->assertEquals('SELECT * FROM users WHERE FALSE', self::simplifySql($magicQuery->build($sql, ['statuses' => []])));
}
/**
* @expectedException \Mouf\Database\MagicQueryException
*/
public function testInNullException() {
$magicQuery = new MagicQuery();
$sql = 'SELECT * FROM users WHERE status IN :statuses!';
$magicQuery->build($sql, ['statuses' => NULL]);
}
/**
* @expectedException \Mouf\Database\MagicQueryException
*/
public function testInvalidSql() {
$magicQuery = new MagicQuery();
$sql = 'SELECT * FROM users WHERE date_end => :startDate';
$this->assertEquals('SELECT * FROM users WHERE date_end => \'2014-06-06\'', self::simplifySql($magicQuery->build($sql, ['startDate' => '2014-06-06'])));
}
public function testWithCache()
{
global $db_url;
$config = new \Doctrine\DBAL\Configuration();
// TODO: put this in conf variable
$connectionParams = array(
'url' => $db_url,
);
$conn = \Doctrine\DBAL\DriverManager::getConnection($connectionParams, $config);
$cache = new ArrayCache();
$magicQuery = new MagicQuery($conn, $cache);
$sql = 'SELECT * FROM users';
$this->assertEquals($sql, self::simplifySql($magicQuery->build($sql)));
$select = $cache->fetch('request_'.hash('md4', $sql));
$this->assertInstanceOf('SQLParser\\Query\\Select', $select);
$this->assertEquals($sql, self::simplifySql($magicQuery->build($sql)));
}
/**
* @expectedException \Mouf\Database\MagicQueryParserException
*/
public function testParseError()
{
$magicQuery = new MagicQuery();
$sql = '';
$magicQuery->build($sql);
}
public function testMagicJoin()
{
$schema = new Schema();
$role = $schema->createTable('role');
$role->addColumn('id', 'integer', array('unsigned' => true));
$role->addColumn('label', 'string', array('length' => 32));
$right = $schema->createTable('right');
$right->addColumn('id', 'integer', array('unsigned' => true));
$right->addColumn('label', 'string', array('length' => 32));
$role_right = $schema->createTable('role_right');
$role_right->addColumn('role_id', 'integer', array('unsigned' => true));
$role_right->addColumn('right_id', 'integer', array('unsigned' => true));
$role_right->addForeignKeyConstraint($schema->getTable('role'), array('role_id'), array('id'), array('onUpdate' => 'CASCADE'));
$role_right->addForeignKeyConstraint($schema->getTable('right'), array('right_id'), array('id'), array('onUpdate' => 'CASCADE'));
$role_right->setPrimaryKey(['role_id', 'right_id']);
$schemaAnalyzer = new SchemaAnalyzer(new StubSchemaManager($schema));
$magicQuery = new MagicQuery(null, null, $schemaAnalyzer);
$sql = "SELECT role.* FROM magicjoin(role) WHERE right.label = 'my_right'";
$expectedSql = "SELECT role.* FROM role LEFT JOIN role_right ON (role_right.role_id = role.id) LEFT JOIN right ON (role_right.right_id = right.id) WHERE right.label = 'my_right'";
$this->assertEquals($expectedSql, self::simplifySql($magicQuery->build($sql)));
}
public function testMagicJoin2()
{
$schema = new Schema();
$role = $schema->createTable('role');
$role->addColumn('id', 'integer', array('unsigned' => true));
$role->addColumn('label', 'string', array('length' => 32));
$right = $schema->createTable('right');
$right->addColumn('id', 'integer', array('unsigned' => true));
$right->addColumn('label', 'string', array('length' => 32));
$role_right = $schema->createTable('role_right');
$role_right->addColumn('role_id', 'integer', array('unsigned' => true));
$role_right->addColumn('right_id', 'integer', array('unsigned' => true));
$role_right->addForeignKeyConstraint($schema->getTable('role'), array('role_id'), array('id'), array('onUpdate' => 'CASCADE'));
$role_right->addForeignKeyConstraint($schema->getTable('right'), array('right_id'), array('id'), array('onUpdate' => 'CASCADE'));
$role_right->setPrimaryKey(['role_id', 'right_id']);
$user = $schema->createTable('user');
$user->addColumn('id', 'integer', array('unsigned' => true));
$user->addColumn('login', 'string', array('length' => 32));
$user->addColumn('role_id', 'integer', array('unsigned' => true));
$user->addForeignKeyConstraint($schema->getTable('role'), array('role_id'), array('id'), array('onUpdate' => 'CASCADE'));
$schemaAnalyzer = new SchemaAnalyzer(new StubSchemaManager($schema));
$magicQuery = new MagicQuery(null, null, $schemaAnalyzer);
$sql = "SELECT role.* FROM magicjoin(role) WHERE right.label = 'my_right' AND user.login = 'foo'";
$expectedSql = "SELECT role.* FROM role LEFT JOIN role_right ON (role_right.role_id = role.id) LEFT JOIN right ON (role_right.right_id = right.id) LEFT JOIN user ON (user.role_id = role.id) WHERE (right.label = 'my_right') AND (user.login = 'foo')";
$this->assertEquals($expectedSql, self::simplifySql($magicQuery->build($sql)));
}
public function testMagicJoin3()
{
$schema = new Schema();
$role = $schema->createTable('role');
$role->addColumn('id', 'integer', array('unsigned' => true));
$role->addColumn('label', 'string', array('length' => 32));
$right = $schema->createTable('right');
$right->addColumn('id', 'integer', array('unsigned' => true));
$right->addColumn('label', 'string', array('length' => 32));
$role_right = $schema->createTable('role_right');
$role_right->addColumn('role_id', 'integer', array('unsigned' => true));
$role_right->addColumn('right_id', 'integer', array('unsigned' => true));
$role_right->addForeignKeyConstraint($schema->getTable('role'), array('role_id'), array('id'), array('onUpdate' => 'CASCADE'));
$role_right->addForeignKeyConstraint($schema->getTable('right'), array('right_id'), array('id'), array('onUpdate' => 'CASCADE'));
$role_right->setPrimaryKey(['role_id', 'right_id']);
$status = $schema->createTable('status');
$status->addColumn('id', 'integer', array('unsigned' => true));
$status->addColumn('name', 'string', array('length' => 32));
$role->addColumn('status_id', 'integer', array('unsigned' => true));
$role->addForeignKeyConstraint($schema->getTable('status'), array('status_id'), array('id'), array('onUpdate' => 'CASCADE'));
$schemaAnalyzer = new SchemaAnalyzer(new StubSchemaManager($schema));
$magicQuery = new MagicQuery(null, null, $schemaAnalyzer);
$sql = "SELECT role.* FROM magicjoin(role) WHERE right.label = 'my_right' AND status.name = 'foo'";
$expectedSql = "SELECT role.* FROM role LEFT JOIN role_right ON (role_right.role_id = role.id) LEFT JOIN right ON (role_right.right_id = right.id) LEFT JOIN status ON (role.status_id = status.id) WHERE (right.label = 'my_right') AND (status.name = 'foo')";
$this->assertEquals($expectedSql, self::simplifySql($magicQuery->build($sql)));
}
/**
* @expectedException \Mouf\Database\MagicQueryMissingConnectionException
*/
public function testMisconfiguration()
{
$magicQuery = new MagicQuery();
$sql = "SELECT role.* FROM magicjoin(role) WHERE right.label = 'my_right'";
$magicQuery->build($sql);
}
/**
*
*/
public function testTwig()
{
$magicQuery = new MagicQuery();
$magicQuery->setEnableTwig(true);
$sql = "SELECT * FROM toto {% if id %}WHERE status = 'on'{% endif %}";
$this->assertEquals("SELECT * FROM toto WHERE status = 'on'", $this->simplifySql($magicQuery->build($sql, ['id' => 12])));
$this->assertEquals('SELECT * FROM toto', $this->simplifySql($magicQuery->build($sql, ['id' => null])));
}
/**
* Removes all artifacts.
*/
private static function simplifySql($sql)
{
$sql = str_replace("\n", ' ', $sql);
$sql = str_replace("\t", ' ', $sql);
$sql = str_replace('`', '', $sql);
$sql = str_replace(' ', ' ', $sql);
$sql = str_replace(' ', ' ', $sql);
$sql = str_replace(' ', ' ', $sql);
$sql = str_replace(' ', ' ', $sql);
$sql = str_replace(' ', ' ', $sql);
$sql = str_replace(' ', ' ', $sql);
$sql = str_replace(' ', ' ', $sql);
$sql = str_replace(' ', ' ', $sql);
$sql = str_replace('( ', '(', $sql);
$sql = str_replace(' )', ')', $sql);
$sql = str_replace(' . ', '.', $sql);
$sql = trim($sql);
return $sql;
}
}