Added feature #13209: MySQL extended INSERT statements cannot be parsed
[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 * @test
211 * http://bugs.typo3.org/view.php?id=13430
212 */
213 public function canParseInsertWithoutSpaceAfterValues() {
214 $parseString = "INSERT INTO static_country_zones VALUES('483', '0', 'NL', 'NLD', '528', 'DR', 'Drenthe', '');";
215 $components = $this->fixture->_callRef('parseINSERT', $parseString);
216
217 $this->assertTrue(is_array($components), $components);
218 $insert = $this->cleanSql($this->fixture->compileINSERT($components));
219 $expected = "INSERT INTO static_country_zones VALUES ('483', '0', 'NL', 'NLD', '528', 'DR', 'Drenthe', '')";
220 $this->assertEquals($expected, $insert);
221 }
222
223 /**
224 * @test
225 * http://bugs.typo3.org/view.php?id=13430
226 */
227 public function canParseInsertWithSpaceAfterValues() {
228 $parseString = "INSERT INTO static_country_zones VALUES ('483', '0', 'NL', 'NLD', '528', 'DR', 'Drenthe', '');";
229 $components = $this->fixture->_callRef('parseINSERT', $parseString);
230
231 $this->assertTrue(is_array($components), $components);
232 $insert = $this->cleanSql($this->fixture->compileINSERT($components));
233 $expected = "INSERT INTO static_country_zones VALUES ('483', '0', 'NL', 'NLD', '528', 'DR', 'Drenthe', '')";
234 $this->assertEquals($expected, $insert);
235 }
236
237 /**
238 * @test
239 */
240 public function canParseInsertWithFields() {
241 $parseString = 'INSERT INTO static_territories (uid, pid, tr_iso_nr, tr_parent_iso_nr, tr_name_en) ';
242 $parseString .= "VALUES ('1', '0', '2', '0', 'Africa');";
243 $components = $this->fixture->_callRef('parseINSERT', $parseString);
244
245 $this->assertTrue(is_array($components), $components);
246 $insert = $this->cleanSql($this->fixture->compileINSERT($components));
247 $expected = 'INSERT INTO static_territories (uid, pid, tr_iso_nr, tr_parent_iso_nr, tr_name_en) ';
248 $expected .= "VALUES ('1', '0', '2', '0', 'Africa')";
249 $this->assertEquals($expected, $insert);
250 }
251
252 /**
253 * @test
254 * http://bugs.typo3.org/view.php?id=13209
255 */
256 public function canParseExtendedInsert() {
257 $parseString = "INSERT INTO static_territories VALUES ('1', '0', '2', '0', 'Africa'),('2', '0', '9', '0', 'Oceania')," .
258 "('3', '0', '19', '0', 'Americas'),('4', '0', '142', '0', 'Asia');";
259 $components = $this->fixture->_callRef('parseINSERT', $parseString);
260
261 $this->assertTrue(is_array($components), $components);
262 $insert = $this->cleanSql($this->fixture->compileINSERT($components));
263 $expected = "INSERT INTO static_territories VALUES ('1', '0', '2', '0', 'Africa'), ('2', '0', '9', '0', 'Oceania'), " .
264 "('3', '0', '19', '0', 'Americas'), ('4', '0', '142', '0', 'Asia')";
265 $this->assertEquals($expected, $insert);
266 }
267
268 /**
269 * @test
270 * http://bugs.typo3.org/view.php?id=13209
271 */
272 public function canParseExtendedInsertWithFields() {
273 $parseString = 'INSERT INTO static_territories (uid, pid, tr_iso_nr, tr_parent_iso_nr, tr_name_en) ';
274 $parseString .= "VALUES ('1', '0', '2', '0', 'Africa'),('2', '0', '9', '0', 'Oceania');";
275 $components = $this->fixture->_callRef('parseINSERT', $parseString);
276
277 $this->assertTrue(is_array($components), $components);
278 $insert = $this->cleanSql($this->fixture->compileINSERT($components));
279 $expected = 'INSERT INTO static_territories (uid, pid, tr_iso_nr, tr_parent_iso_nr, tr_name_en) ';
280 $expected .= "VALUES ('1', '0', '2', '0', 'Africa'), ('2', '0', '9', '0', 'Oceania')";
281 $this->assertEquals($expected, $insert);
282 }
283
284 ///////////////////////////////////////
285 // Tests concerning JOINs
286 ///////////////////////////////////////
287
288 /**
289 * @test
290 */
291 public function parseFromTablesWithInnerJoinReturnsArray() {
292 $parseString = 'be_users INNER JOIN pages ON pages.cruser_id = be_users.uid';
293 $tables = $this->fixture->parseFromTables($parseString);
294
295 $this->assertTrue(is_array($tables), $tables);
296 $this->assertTrue(empty($parseString), 'parseString is not empty');
297 }
298
299 /**
300 * @test
301 */
302 public function parseFromTablesWithLeftOuterJoinReturnsArray() {
303 $parseString = 'be_users LEFT OUTER JOIN pages ON be_users.uid = pages.cruser_id';
304 $tables = $this->fixture->parseFromTables($parseString);
305
306 $this->assertTrue(is_array($tables), $tables);
307 $this->assertTrue(empty($parseString), 'parseString is not empty');
308 }
309
310 /**
311 * @test
312 * @see http://bugs.typo3.org/view.php?id=12596
313 */
314 public function parseFromTablesWithRightOuterJoinReturnsArray() {
315 $parseString = 'tx_powermail_fieldsets RIGHT JOIN tt_content ON tx_powermail_fieldsets.tt_content = tt_content.uid';
316 $tables = $this->fixture->parseFromTables($parseString);
317
318 $this->assertTrue(is_array($tables), $tables);
319 $this->assertTrue(empty($parseString), 'parseString is not empty');
320 }
321
322 /**
323 * @test
324 */
325 public function parseFromTablesWithMultipleJoinsReturnsArray() {
326 $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';
327 $tables = $this->fixture->parseFromTables($parseString);
328
329 $this->assertTrue(is_array($tables), $tables);
330 $this->assertTrue(empty($parseString), 'parseString is not empty');
331 }
332
333 /**
334 * @test
335 * @see http://bugs.typo3.org/view.php?id=12596
336 */
337 public function parseFromTablesWithMultipleJoinsAndParenthesesReturnsArray() {
338 $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';
339 $tables = $this->fixture->parseFromTables($parseString);
340
341 $this->assertTrue(is_array($tables), $tables);
342 $this->assertTrue(empty($parseString), 'parseString is not empty');
343 }
344
345 /**
346 * @test
347 */
348 public function canUseInnerJoinInSelect() {
349 $sql = 'SELECT pages.uid, be_users.username FROM be_users INNER JOIN pages ON pages.cruser_id = be_users.uid';
350 $expected = 'SELECT pages.uid, be_users.username FROM be_users INNER JOIN pages ON pages.cruser_id=be_users.uid';
351 $actual = $this->cleanSql($this->fixture->debug_testSQL($sql));
352
353 $this->assertEquals($expected, $actual);
354 }
355
356 /**
357 * @test
358 */
359 public function canUseMultipleInnerJoinsInSelect() {
360 $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';
361 $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';
362 $actual = $this->cleanSql($this->fixture->debug_testSQL($sql));
363
364 $this->assertEquals($expected, $actual);
365 }
366
367 ///////////////////////////////////////
368 // Tests concerning DB management
369 ///////////////////////////////////////
370
371 /**
372 * @test
373 * @see http://bugs.typo3.org/view.php?id=4466
374 */
375 public function indexMayContainALengthRestriction() {
376 $parseString = '
377 CREATE TABLE tx_realurl_uniqalias (
378 uid int(11) NOT NULL auto_increment,
379 tstamp int(11) DEFAULT \'0\' NOT NULL,
380 tablename varchar(60) DEFAULT \'\' NOT NULL,
381 field_alias varchar(255) DEFAULT \'\' NOT NULL,
382 field_id varchar(60) DEFAULT \'\' NOT NULL,
383 value_alias varchar(255) DEFAULT \'\' NOT NULL,
384 value_id int(11) DEFAULT \'0\' NOT NULL,
385 lang int(11) DEFAULT \'0\' NOT NULL,
386 expire int(11) DEFAULT \'0\' NOT NULL,
387
388 PRIMARY KEY (uid),
389 KEY tablename (tablename),
390 KEY bk_realurl01 (field_alias,field_id,value_id,lang,expire),
391 KEY bk_realurl02 (tablename,field_alias,field_id,value_alias(220),expire)
392 );
393 ';
394
395 $createTables = $this->fixture->_callRef('parseCREATETABLE', $parseString);
396 $this->assertTrue(is_array($createTables), $createTables);
397 }
398
399 ///////////////////////////////////////
400 // Tests concerning subqueries
401 ///////////////////////////////////////
402
403 /**
404 * @test
405 * @see http://bugs.typo3.org/view.php?id=12758
406 */
407 public function inWhereClauseSupportsSubquery() {
408 $parseString = 'process_id IN (SELECT process_id FROM tx_crawler_process WHERE active=0 AND deleted=0)';
409 $whereParts = $this->fixture->parseWhereClause($parseString);
410
411 $this->assertTrue(is_array($whereParts), $whereParts);
412 $this->assertTrue(empty($parseString), 'parseString is not empty');
413 }
414
415 /**
416 * @test
417 * @see http://bugs.typo3.org/view.php?id=12758
418 */
419 public function inWhereClauseWithSubqueryIsProperlyCompiled() {
420 $sql = 'SELECT * FROM tx_crawler_queue WHERE process_id IN (SELECT process_id FROM tx_crawler_process WHERE active=0 AND deleted=0)';
421 $expected = 'SELECT * FROM tx_crawler_queue WHERE process_id IN (SELECT process_id FROM tx_crawler_process WHERE active = 0 AND deleted = 0)';
422 $actual = $this->cleanSql($this->fixture->debug_testSQL($sql));
423
424 $this->assertEquals($expected, $actual);
425 }
426
427 /**
428 * @test
429 * @see http://bugs.typo3.org/view.php?id=12758
430 */
431 public function whereClauseSupportsExistsKeyword() {
432 $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)';
433 $whereParts = $this->fixture->parseWhereClause($parseString);
434
435 $this->assertTrue(is_array($whereParts), $whereParts);
436 $this->assertTrue(empty($parseString), 'parseString is not empty');
437 }
438
439 /**
440 * @test
441 * @see http://bugs.typo3.org/view.php?id=12758
442 */
443 public function existsClauseIsProperlyCompiled() {
444 $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)';
445 $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)';
446 $actual = $this->cleanSql($this->fixture->debug_testSQL($sql));
447
448 $this->assertEquals($expected, $actual);
449 }
450
451 ///////////////////////////////////////
452 // Tests concerning advanced operators
453 ///////////////////////////////////////
454
455 /**
456 * @test
457 * @see http://bugs.typo3.org/view.php?id=13135
458 */
459 public function caseWithBooleanConditionIsSupportedInFields() {
460 $parseString = 'CASE WHEN 1>0 THEN 2 ELSE 1 END AS foo, other_column';
461 $fieldList = $this->fixture->parseFieldList($parseString);
462
463 $this->assertTrue(is_array($fieldList), $fieldList);
464 $this->assertTrue(empty($parseString), 'parseString is not empty');
465 }
466
467 /**
468 * @test
469 * @see http://bugs.typo3.org/view.php?id=13135
470 */
471 public function caseWithBooleanConditionIsProperlyCompiled() {
472 $sql = 'SELECT CASE WHEN 1>0 THEN 2 ELSE 1 END AS foo, other_column FROM mytable';
473 $expected = 'SELECT CASE WHEN 1 > 0 THEN 2 ELSE 1 END AS foo, other_column FROM mytable';
474 $actual = $this->cleanSql($this->fixture->debug_testSQL($sql));
475
476 $this->assertEquals($expected, $actual);
477 }
478
479 /**
480 * @test
481 * @see http://bugs.typo3.org/view.php?id=13135
482 */
483 public function caseWithMultipleWhenIsSupportedInFields() {
484 $parseString = 'CASE column WHEN 1 THEN \'one\' WHEN 2 THEN \'two\' ELSE \'out of range\' END AS number';
485 $fieldList = $this->fixture->parseFieldList($parseString);
486
487 $this->assertTrue(is_array($fieldList), $fieldList);
488 $this->assertTrue(empty($parseString), 'parseString is not empty');
489 }
490
491 /**
492 * @test
493 * @see http://bugs.typo3.org/view.php?id=13135
494 */
495 public function caseWithMultipleWhenIsProperlyCompiled() {
496 $sql = 'SELECT CASE column WHEN 1 THEN \'one\' WHEN 2 THEN \'two\' ELSE \'out of range\' END AS number FROM mytable';
497 $expected = 'SELECT CASE column WHEN 1 THEN \'one\' WHEN 2 THEN \'two\' ELSE \'out of range\' END AS number FROM mytable';
498 $actual = $this->cleanSql($this->fixture->debug_testSQL($sql));
499
500 $this->assertEquals($expected, $actual);
501 }
502
503 /**
504 * @test
505 * @see http://bugs.typo3.org/view.php?id=13134
506 */
507 public function locateIsSupported() {
508 $sql = 'SELECT * FROM tx_templavoila_tmplobj WHERE LOCATE(\'(fce)\', datastructure)>0';
509 $expected = 'SELECT * FROM tx_templavoila_tmplobj WHERE LOCATE(\'(fce)\', datastructure) > 0';
510 $actual = $this->cleanSql($this->fixture->debug_testSQL($sql));
511
512 $this->assertEquals($expected, $actual);
513 }
514
515 /**
516 * @test
517 * @see http://bugs.typo3.org/view.php?id=13134
518 */
519 public function locateWithPositionIsSupported() {
520 $sql = 'SELECT * FROM tx_templavoila_tmplobj WHERE LOCATE(\'(fce)\' , datastructure ,10)>0';
521 $expected = 'SELECT * FROM tx_templavoila_tmplobj WHERE LOCATE(\'(fce)\', datastructure, 10) > 0';
522 $actual = $this->cleanSql($this->fixture->debug_testSQL($sql));
523
524 $this->assertEquals($expected, $actual);
525 }
526
527 /**
528 * @test
529 * @see http://bugs.typo3.org/view.php?id=13134
530 * @see http://bugs.typo3.org/view.php?id=13135
531 */
532 public function locateWithinCaseIsSupported() {
533 $sql = 'SELECT *, CASE WHEN LOCATE(\'(fce)\', datastructure)>0 THEN 2 ELSE 1 END AS scope FROM tx_templavoila_tmplobj';
534 $expected = 'SELECT *, CASE WHEN LOCATE(\'(fce)\', datastructure) > 0 THEN 2 ELSE 1 END AS scope FROM tx_templavoila_tmplobj';
535 $actual = $this->cleanSql($this->fixture->debug_testSQL($sql));
536
537 $this->assertEquals($expected, $actual);
538 }
539 }
540 ?>