Added unit tests for bug #13412: BETWEEN operator is not supported
[Packages/TYPO3.CMS.git] / typo3 / sysext / dbal / tests / sqlparser_general_testcase.php
1 <?php
2 /***************************************************************
3 * Copyright notice
4 *
5 * (c) 2009 Xavier Perseguers <typo3@perseguers.ch>
6 * All rights reserved
7 *
8 * This script is part of the TYPO3 project. The TYPO3 project is
9 * free software; you can redistribute it and/or modify
10 * it under the terms of the GNU General Public License as published by
11 * the Free Software Foundation; either version 2 of the License, or
12 * (at your option) any later version.
13 *
14 * The GNU General Public License can be found at
15 * http://www.gnu.org/copyleft/gpl.html.
16 *
17 * This script is distributed in the hope that it will be useful,
18 * but WITHOUT ANY WARRANTY; without even the implied warranty of
19 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
20 * GNU General Public License for more details.
21 *
22 * This copyright notice MUST APPEAR in all copies of the script!
23 ***************************************************************/
24
25
26 require_once('BaseTestCase.php');
27
28 /**
29 * Testcase for class ux_t3lib_sqlparser
30 *
31 * $Id$
32 *
33 * @author Xavier Perseguers <typo3@perseguers.ch>
34 *
35 * @package TYPO3
36 * @subpackage dbal
37 */
38 class sqlparser_general_testcase extends BaseTestCase {
39
40 /**
41 * @var ux_t3lib_sqlparser (extended to make protected methods public)
42 */
43 protected $fixture;
44
45 /**
46 * Prepares the environment before running a test.
47 */
48 public function setUp() {
49 $className = self::buildAccessibleProxy('ux_t3lib_sqlparser');
50 $this->fixture = new $className;
51 }
52
53 /**
54 * Cleans up the environment after running a test.
55 */
56 public function tearDown() {
57 unset($this->fixture);
58 }
59
60 /**
61 * Cleans a SQL query.
62 *
63 * @param mixed $sql
64 * @return mixed (string or array)
65 */
66 private function cleanSql($sql) {
67 if (!is_string($sql)) {
68 return $sql;
69 }
70
71 $sql = str_replace("\n", ' ', $sql);
72 $sql = preg_replace('/\s+/', ' ', $sql);
73 return trim($sql);
74 }
75
76 /**
77 * @test
78 */
79 public function canExtractPartsOfAQuery() {
80 $parseString = "SELECT *\nFROM pages WHERE pid IN (1,2,3,4)";
81 $regex = '^SELECT[[:space:]]+(.*)[[:space:]]+';
82 $trimAll = TRUE;
83 $fields = $this->fixture->_callRef('nextPart', $parseString, $regex, $trimAll);
84
85 $this->assertEquals(
86 '*',
87 $fields
88 );
89 $this->assertEquals(
90 'FROM pages WHERE pid IN (1,2,3,4)',
91 $parseString
92 );
93
94 $regex = '^FROM ([^)]+) WHERE';
95 $table = $this->fixture->_callRef('nextPart', $parseString, $regex);
96
97 $this->assertEquals(
98 'pages',
99 $table
100 );
101 $this->assertEquals(
102 'pages WHERE pid IN (1,2,3,4)',
103 $parseString
104 );
105 }
106
107 /**
108 * @test
109 */
110 public function canGetIntegerValue() {
111 $parseString = '1024';
112 $value = $this->fixture->_callRef('getValue', $parseString);
113 $expected = array(1024);
114
115 $this->assertEquals($expected, $value);
116 }
117
118 /**
119 * @test
120 * @see http://bugs.typo3.org/view.php?id=13104
121 */
122 public function canGetStringValue() {
123 $parseString = '"some owner\\\'s string"';
124 $value = $this->fixture->_callRef('getValue', $parseString);
125 $expected = array('some owner\'s string', '"');
126
127 $this->assertEquals($expected, $value);
128 }
129
130 /**
131 * @test
132 * @see http://bugs.typo3.org/view.php?id=13104
133 */
134 public function canGetStringValueWithSingleQuote() {
135 $parseString = "'some owner\'s string'";
136 $value = $this->fixture->_callRef('getValue', $parseString);
137 $expected = array('some owner\'s string', "'");
138
139 $this->assertEquals($expected, $value);
140 }
141
142 /**
143 * @test
144 * @see http://bugs.typo3.org/view.php?id=13104
145 */
146 public function canGetStringValueWithDoubleQuote() {
147 $parseString = '"the \"owner\" is here"';
148 $value = $this->fixture->_callRef('getValue', $parseString);
149 $expected = array('the "owner" is here', '"');
150
151 $this->assertEquals($expected, $value);
152 }
153
154 /**
155 * @test
156 */
157 public function canGetListOfValues() {
158 $parseString = '( 1, 2, 3 ,4)';
159 $operator = 'IN';
160 $values = $this->fixture->_callRef('getValue', $parseString, $operator);
161 $expected = array(
162 array(1),
163 array(2),
164 array(3),
165 array(4)
166 );
167
168 $this->assertEquals($expected, $values);
169 }
170
171 /**
172 * @test
173 */
174 public function parseWhereClauseReturnsArray() {
175 $parseString = 'uid IN (1,2) AND (starttime < ' . time() . ' OR cruser_id + 10 < 20)';
176 $where = $this->fixture->parseWhereClause($parseString);
177
178 $this->assertTrue(is_array($where), $where);
179 $this->assertTrue(empty($parseString), 'parseString is not empty');
180 }
181
182 /**
183 * @test
184 */
185 public function canSelectAllFieldsFromPages() {
186 $sql = 'SELECT * FROM pages';
187 $expected = $sql;
188 $actual = $this->cleanSql($this->fixture->debug_testSQL($sql));
189
190 $this->assertEquals($expected, $actual);
191 }
192
193 /**
194 * @test
195 * @see http://bugs.typo3.org/view.php?id=13412
196 */
197 public function canParseAndCompileBetweenOperator() {
198 $parseString = '((scheduled BETWEEN 1265068628 AND 1265068828 ) OR scheduled <= 1265068728) AND NOT exec_time AND NOT process_id AND page_id=1 AND parameters_hash = \'854e9a2a77\'';
199 $where = $this->fixture->parseWhereClause($parseString);
200
201 $this->assertTrue(is_array($where), $where);
202 $this->assertTrue(empty($parseString), 'parseString is not empty');
203
204 $whereClause = $this->cleanSql($this->fixture->compileWhereClause($where));
205 $expected = '((scheduled BETWEEN 1265068628 AND 1265068828) OR scheduled <= 1265068728) AND NOT exec_time AND NOT process_id AND page_id = 1 AND parameters_hash = \'854e9a2a77\'';
206 $this->assertEquals($expected, $whereClause);
207 }
208
209 ///////////////////////////////////////
210 // Tests concerning JOINs
211 ///////////////////////////////////////
212
213 /**
214 * @test
215 */
216 public function parseFromTablesWithInnerJoinReturnsArray() {
217 $parseString = 'be_users INNER JOIN pages ON pages.cruser_id = be_users.uid';
218 $tables = $this->fixture->parseFromTables($parseString);
219
220 $this->assertTrue(is_array($tables), $tables);
221 $this->assertTrue(empty($parseString), 'parseString is not empty');
222 }
223
224 /**
225 * @test
226 */
227 public function parseFromTablesWithLeftOuterJoinReturnsArray() {
228 $parseString = 'be_users LEFT OUTER JOIN pages ON be_users.uid = pages.cruser_id';
229 $tables = $this->fixture->parseFromTables($parseString);
230
231 $this->assertTrue(is_array($tables), $tables);
232 $this->assertTrue(empty($parseString), 'parseString is not empty');
233 }
234
235 /**
236 * @test
237 * @see http://bugs.typo3.org/view.php?id=12596
238 */
239 public function parseFromTablesWithRightOuterJoinReturnsArray() {
240 $parseString = 'tx_powermail_fieldsets RIGHT JOIN tt_content ON tx_powermail_fieldsets.tt_content = tt_content.uid';
241 $tables = $this->fixture->parseFromTables($parseString);
242
243 $this->assertTrue(is_array($tables), $tables);
244 $this->assertTrue(empty($parseString), 'parseString is not empty');
245 }
246
247 /**
248 * @test
249 */
250 public function parseFromTablesWithMultipleJoinsReturnsArray() {
251 $parseString = 'be_users LEFT OUTER JOIN pages ON be_users.uid = pages.cruser_id INNER JOIN cache_pages cp ON cp.page_id = pages.uid';
252 $tables = $this->fixture->parseFromTables($parseString);
253
254 $this->assertTrue(is_array($tables), $tables);
255 $this->assertTrue(empty($parseString), 'parseString is not empty');
256 }
257
258 /**
259 * @test
260 * @see http://bugs.typo3.org/view.php?id=12596
261 */
262 public function parseFromTablesWithMultipleJoinsAndParenthesesReturnsArray() {
263 $parseString = 'tx_powermail_fieldsets RIGHT JOIN tt_content ON tx_powermail_fieldsets.tt_content = tt_content.uid LEFT JOIN tx_powermail_fields ON tx_powermail_fieldsets.uid = tx_powermail_fields.fieldset';
264 $tables = $this->fixture->parseFromTables($parseString);
265
266 $this->assertTrue(is_array($tables), $tables);
267 $this->assertTrue(empty($parseString), 'parseString is not empty');
268 }
269
270 /**
271 * @test
272 */
273 public function canUseInnerJoinInSelect() {
274 $sql = 'SELECT pages.uid, be_users.username FROM be_users INNER JOIN pages ON pages.cruser_id = be_users.uid';
275 $expected = 'SELECT pages.uid, be_users.username FROM be_users INNER JOIN pages ON pages.cruser_id=be_users.uid';
276 $actual = $this->cleanSql($this->fixture->debug_testSQL($sql));
277
278 $this->assertEquals($expected, $actual);
279 }
280
281 /**
282 * @test
283 */
284 public function canUseMultipleInnerJoinsInSelect() {
285 $sql = 'SELECT * FROM tt_news_cat INNER JOIN tt_news_cat_mm ON tt_news_cat.uid = tt_news_cat_mm.uid_foreign INNER JOIN tt_news ON tt_news.uid = tt_news_cat_mm.uid_local';
286 $expected = 'SELECT * FROM tt_news_cat INNER JOIN tt_news_cat_mm ON tt_news_cat.uid=tt_news_cat_mm.uid_foreign INNER JOIN tt_news ON tt_news.uid=tt_news_cat_mm.uid_local';
287 $actual = $this->cleanSql($this->fixture->debug_testSQL($sql));
288
289 $this->assertEquals($expected, $actual);
290 }
291
292 ///////////////////////////////////////
293 // Tests concerning DB management
294 ///////////////////////////////////////
295
296 /**
297 * @test
298 * @see http://bugs.typo3.org/view.php?id=4466
299 */
300 public function indexMayContainALengthRestriction() {
301 $parseString = '
302 CREATE TABLE tx_realurl_uniqalias (
303 uid int(11) NOT NULL auto_increment,
304 tstamp int(11) DEFAULT \'0\' NOT NULL,
305 tablename varchar(60) DEFAULT \'\' NOT NULL,
306 field_alias varchar(255) DEFAULT \'\' NOT NULL,
307 field_id varchar(60) DEFAULT \'\' NOT NULL,
308 value_alias varchar(255) DEFAULT \'\' NOT NULL,
309 value_id int(11) DEFAULT \'0\' NOT NULL,
310 lang int(11) DEFAULT \'0\' NOT NULL,
311 expire int(11) DEFAULT \'0\' NOT NULL,
312
313 PRIMARY KEY (uid),
314 KEY tablename (tablename),
315 KEY bk_realurl01 (field_alias,field_id,value_id,lang,expire),
316 KEY bk_realurl02 (tablename,field_alias,field_id,value_alias(220),expire)
317 );
318 ';
319
320 $createTables = $this->fixture->_callRef('parseCREATETABLE', $parseString);
321 $this->assertTrue(is_array($createTables), $createTables);
322 }
323
324 ///////////////////////////////////////
325 // Tests concerning subqueries
326 ///////////////////////////////////////
327
328 /**
329 * @test
330 * @see http://bugs.typo3.org/view.php?id=12758
331 */
332 public function inWhereClauseSupportsSubquery() {
333 $parseString = 'process_id IN (SELECT process_id FROM tx_crawler_process WHERE active=0 AND deleted=0)';
334 $whereParts = $this->fixture->parseWhereClause($parseString);
335
336 $this->assertTrue(is_array($whereParts), $whereParts);
337 $this->assertTrue(empty($parseString), 'parseString is not empty');
338 }
339
340 /**
341 * @test
342 * @see http://bugs.typo3.org/view.php?id=12758
343 */
344 public function inWhereClauseWithSubqueryIsProperlyCompiled() {
345 $sql = 'SELECT * FROM tx_crawler_queue WHERE process_id IN (SELECT process_id FROM tx_crawler_process WHERE active=0 AND deleted=0)';
346 $expected = 'SELECT * FROM tx_crawler_queue WHERE process_id IN (SELECT process_id FROM tx_crawler_process WHERE active = 0 AND deleted = 0)';
347 $actual = $this->cleanSql($this->fixture->debug_testSQL($sql));
348
349 $this->assertEquals($expected, $actual);
350 }
351
352 /**
353 * @test
354 * @see http://bugs.typo3.org/view.php?id=12758
355 */
356 public function whereClauseSupportsExistsKeyword() {
357 $parseString = 'EXISTS (SELECT * FROM tx_crawler_queue WHERE tx_crawler_queue.process_id = tx_crawler_process.process_id AND tx_crawler_queue.exec_time = 0)';
358 $whereParts = $this->fixture->parseWhereClause($parseString);
359
360 $this->assertTrue(is_array($whereParts), $whereParts);
361 $this->assertTrue(empty($parseString), 'parseString is not empty');
362 }
363
364 /**
365 * @test
366 * @see http://bugs.typo3.org/view.php?id=12758
367 */
368 public function existsClauseIsProperlyCompiled() {
369 $sql = 'SELECT * FROM tx_crawler_process WHERE active = 0 AND NOT EXISTS (SELECT * FROM tx_crawler_queue WHERE tx_crawler_queue.process_id = tx_crawler_process.process_id AND tx_crawler_queue.exec_time = 0)';
370 $expected = 'SELECT * FROM tx_crawler_process WHERE active = 0 AND NOT EXISTS (SELECT * FROM tx_crawler_queue WHERE tx_crawler_queue.process_id = tx_crawler_process.process_id AND tx_crawler_queue.exec_time = 0)';
371 $actual = $this->cleanSql($this->fixture->debug_testSQL($sql));
372
373 $this->assertEquals($expected, $actual);
374 }
375
376 ///////////////////////////////////////
377 // Tests concerning advanced operators
378 ///////////////////////////////////////
379
380 /**
381 * @test
382 * @see http://bugs.typo3.org/view.php?id=13135
383 */
384 public function caseWithBooleanConditionIsSupportedInFields() {
385 $parseString = 'CASE WHEN 1>0 THEN 2 ELSE 1 END AS foo, other_column';
386 $fieldList = $this->fixture->parseFieldList($parseString);
387
388 $this->assertTrue(is_array($fieldList), $fieldList);
389 $this->assertTrue(empty($parseString), 'parseString is not empty');
390 }
391
392 /**
393 * @test
394 * @see http://bugs.typo3.org/view.php?id=13135
395 */
396 public function caseWithBooleanConditionIsProperlyCompiled() {
397 $sql = 'SELECT CASE WHEN 1>0 THEN 2 ELSE 1 END AS foo, other_column FROM mytable';
398 $expected = 'SELECT CASE WHEN 1 > 0 THEN 2 ELSE 1 END AS foo, other_column FROM mytable';
399 $actual = $this->cleanSql($this->fixture->debug_testSQL($sql));
400
401 $this->assertEquals($expected, $actual);
402 }
403
404 /**
405 * @test
406 * @see http://bugs.typo3.org/view.php?id=13135
407 */
408 public function caseWithMultipleWhenIsSupportedInFields() {
409 $parseString = 'CASE column WHEN 1 THEN \'one\' WHEN 2 THEN \'two\' ELSE \'out of range\' END AS number';
410 $fieldList = $this->fixture->parseFieldList($parseString);
411
412 $this->assertTrue(is_array($fieldList), $fieldList);
413 $this->assertTrue(empty($parseString), 'parseString is not empty');
414 }
415
416 /**
417 * @test
418 * @see http://bugs.typo3.org/view.php?id=13135
419 */
420 public function caseWithMultipleWhenIsProperlyCompiled() {
421 $sql = 'SELECT CASE column WHEN 1 THEN \'one\' WHEN 2 THEN \'two\' ELSE \'out of range\' END AS number FROM mytable';
422 $expected = 'SELECT CASE column WHEN 1 THEN \'one\' WHEN 2 THEN \'two\' ELSE \'out of range\' END AS number FROM mytable';
423 $actual = $this->cleanSql($this->fixture->debug_testSQL($sql));
424
425 $this->assertEquals($expected, $actual);
426 }
427
428 /**
429 * @test
430 * @see http://bugs.typo3.org/view.php?id=13134
431 */
432 public function locateIsSupported() {
433 $sql = 'SELECT * FROM tx_templavoila_tmplobj WHERE LOCATE(\'(fce)\', datastructure)>0';
434 $expected = 'SELECT * FROM tx_templavoila_tmplobj WHERE LOCATE(\'(fce)\', datastructure) > 0';
435 $actual = $this->cleanSql($this->fixture->debug_testSQL($sql));
436
437 $this->assertEquals($expected, $actual);
438 }
439
440 /**
441 * @test
442 * @see http://bugs.typo3.org/view.php?id=13134
443 */
444 public function locateWithPositionIsSupported() {
445 $sql = 'SELECT * FROM tx_templavoila_tmplobj WHERE LOCATE(\'(fce)\' , datastructure ,10)>0';
446 $expected = 'SELECT * FROM tx_templavoila_tmplobj WHERE LOCATE(\'(fce)\', datastructure, 10) > 0';
447 $actual = $this->cleanSql($this->fixture->debug_testSQL($sql));
448
449 $this->assertEquals($expected, $actual);
450 }
451
452 /**
453 * @test
454 * @see http://bugs.typo3.org/view.php?id=13134
455 * @see http://bugs.typo3.org/view.php?id=13135
456 */
457 public function locateWithinCaseIsSupported() {
458 $sql = 'SELECT *, CASE WHEN LOCATE(\'(fce)\', datastructure)>0 THEN 2 ELSE 1 END AS scope FROM tx_templavoila_tmplobj';
459 $expected = 'SELECT *, CASE WHEN LOCATE(\'(fce)\', datastructure) > 0 THEN 2 ELSE 1 END AS scope FROM tx_templavoila_tmplobj';
460 $actual = $this->cleanSql($this->fixture->debug_testSQL($sql));
461
462 $this->assertEquals($expected, $actual);
463 }
464 }
465 ?>