[BUGFIX] dbal: Cast field to CHAR for FIND_IN_SET()
[Packages/TYPO3.CMS.git] / typo3 / sysext / core / Tests / Unit / Database / SqlParserTest.php
1 <?php
2 namespace TYPO3\CMS\Core\Tests\Unit\Database;
3
4 /*
5 * This file is part of the TYPO3 CMS project.
6 *
7 * It is free software; you can redistribute it and/or modify it under
8 * the terms of the GNU General Public License, either version 2
9 * of the License, or any later version.
10 *
11 * For the full copyright and license information, please read the
12 * LICENSE.txt file that was distributed with this source code.
13 *
14 * The TYPO3 project - inspiring people to share!
15 */
16
17 /**
18 * Testcase for TYPO3\CMS\Core\Database\SqlParser
19 */
20 class SqlParserTest extends \TYPO3\CMS\Core\Tests\UnitTestCase {
21
22 /**
23 * @var \TYPO3\CMS\Core\Database\SqlParser|\TYPO3\CMS\Core\Tests\AccessibleObjectInterface
24 */
25 protected $subject;
26
27 protected function setUp() {
28 $this->subject = $this->getAccessibleMock(\TYPO3\CMS\Core\Database\SqlParser::class, array('dummy'));
29 }
30
31 /**
32 * Regression test
33 *
34 * @test
35 */
36 public function compileWhereClauseDoesNotDropClauses() {
37 $clauses = array(
38 0 => array(
39 'modifier' => '',
40 'table' => 'pages',
41 'field' => 'fe_group',
42 'calc' => '',
43 'comparator' => '=',
44 'value' => array(
45 0 => '',
46 1 => '\''
47 )
48 ),
49 1 => array(
50 'operator' => 'OR',
51 'modifier' => '',
52 'func' => array(
53 'type' => 'IFNULL',
54 'default' => array(
55 0 => '1',
56 1 => '\''
57 ),
58 'table' => 'pages',
59 'field' => 'fe_group'
60 )
61 ),
62 2 => array(
63 'operator' => 'OR',
64 'modifier' => '',
65 'table' => 'pages',
66 'field' => 'fe_group',
67 'calc' => '',
68 'comparator' => '=',
69 'value' => array(
70 0 => '0',
71 1 => '\''
72 )
73 ),
74 3 => array(
75 'operator' => 'OR',
76 'modifier' => '',
77 'func' => array(
78 'type' => 'FIND_IN_SET',
79 'str' => array(
80 0 => '0',
81 1 => '\''
82 ),
83 'table' => 'pages',
84 'field' => 'fe_group'
85 ),
86 'comparator' => ''
87 ),
88 4 => array(
89 'operator' => 'OR',
90 'modifier' => '',
91 'func' => array(
92 'type' => 'FIND_IN_SET',
93 'str' => array(
94 0 => '-1',
95 1 => '\''
96 ),
97 'table' => 'pages',
98 'field' => 'fe_group'
99 ),
100 'comparator' => ''
101 ),
102 5 => array(
103 'operator' => 'OR',
104 'modifier' => '',
105 'func' => array(
106 'type' => 'CAST',
107 'table' => 'pages',
108 'field' => 'fe_group',
109 'datatype' => 'CHAR'
110 ),
111 'comparator' => '=',
112 'value' => array(
113 0 => '',
114 1 => '\''
115 )
116 )
117 );
118 $output = $this->subject->compileWhereClause($clauses);
119 $parts = explode(' OR ', $output);
120 $this->assertSame(count($clauses), count($parts));
121 $this->assertContains('IFNULL', $output);
122 }
123
124 /**
125 * Data provider for trimSqlReallyTrimsAllWhitespace
126 *
127 * @see trimSqlReallyTrimsAllWhitespace
128 */
129 public function trimSqlReallyTrimsAllWhitespaceDataProvider() {
130 return array(
131 'Nothing to trim' => array('SELECT * FROM test WHERE 1=1;', 'SELECT * FROM test WHERE 1=1 '),
132 'Space after ;' => array('SELECT * FROM test WHERE 1=1; ', 'SELECT * FROM test WHERE 1=1 '),
133 'Space before ;' => array('SELECT * FROM test WHERE 1=1 ;', 'SELECT * FROM test WHERE 1=1 '),
134 'Space before and after ;' => array('SELECT * FROM test WHERE 1=1 ; ', 'SELECT * FROM test WHERE 1=1 '),
135 'Linefeed after ;' => array('SELECT * FROM test WHERE 1=1' . LF . ';', 'SELECT * FROM test WHERE 1=1 '),
136 'Linefeed before ;' => array('SELECT * FROM test WHERE 1=1;' . LF, 'SELECT * FROM test WHERE 1=1 '),
137 'Linefeed before and after ;' => array('SELECT * FROM test WHERE 1=1' . LF . ';' . LF, 'SELECT * FROM test WHERE 1=1 '),
138 'Tab after ;' => array('SELECT * FROM test WHERE 1=1' . TAB . ';', 'SELECT * FROM test WHERE 1=1 '),
139 'Tab before ;' => array('SELECT * FROM test WHERE 1=1;' . TAB, 'SELECT * FROM test WHERE 1=1 '),
140 'Tab before and after ;' => array('SELECT * FROM test WHERE 1=1' . TAB . ';' . TAB, 'SELECT * FROM test WHERE 1=1 '),
141 );
142 }
143
144 /**
145 * @test
146 * @dataProvider trimSqlReallyTrimsAllWhitespaceDataProvider
147 * @param string $sql The SQL to trim
148 * @param string $expected The expected trimmed SQL with single space at the end
149 */
150 public function trimSqlReallyTrimsAllWhitespace($sql, $expected) {
151 $result = $this->subject->_call('trimSQL', $sql);
152 $this->assertSame($expected, $result);
153 }
154
155
156 /**
157 * Data provider for getValueReturnsCorrectValues
158 *
159 * @see getValueReturnsCorrectValues
160 */
161 public function getValueReturnsCorrectValuesDataProvider() {
162 return array(
163 // description => array($parseString, $comparator, $mode, $expected)
164 'key definition without length' => array('(pid,input_1), ', '_LIST', 'INDEX', array('pid', 'input_1')),
165 'key definition with length' => array('(pid,input_1(30)), ', '_LIST', 'INDEX', array('pid', 'input_1(30)')),
166 'key definition without length (no mode)' => array('(pid,input_1), ', '_LIST', '', array('pid', 'input_1')),
167 'key definition with length (no mode)' => array('(pid,input_1(30)), ', '_LIST', '', array('pid', 'input_1(30)')),
168 'test1' => array('input_1 varchar(255) DEFAULT \'\' NOT NULL,', '', '', array('input_1')),
169 'test2' => array('varchar(255) DEFAULT \'\' NOT NULL,', '', '', array('varchar(255)')),
170 'test3' => array('DEFAULT \'\' NOT NULL,', '', '', array('DEFAULT')),
171 'test4' => array('\'\' NOT NULL,', '', '', array('', '\'')),
172 'test5' => array('NOT NULL,', '', '', array('NOT')),
173 'test6' => array('NULL,', '', '', array('NULL')),
174 'getValueOrParameter' => array('NULL,', '', '', array('NULL')),
175 );
176 }
177
178 /**
179 * @test
180 * @dataProvider getValueReturnsCorrectValuesDataProvider
181 * @param string $parseString the string to parse
182 * @param string $comparator The comparator used before. If "NOT IN" or "IN" then the value is expected to be a list of values. Otherwise just an integer (un-quoted) or string (quoted)
183 * @param string $mode The mode, eg. "INDEX
184 * @param string $expected
185 */
186 public function getValueReturnsCorrectValues($parseString, $comparator, $mode, $expected) {
187 $result = $this->subject->_callRef('getValue', $parseString, $comparator, $mode);
188 $this->assertSame($expected, $result);
189 }
190
191 /**
192 * Data provider for parseSQL
193 *
194 * @see parseSQL
195 */
196 public function parseSQLDataProvider() {
197 $testSql = array();
198 $testSql[] = 'CREATE TABLE tx_demo (';
199 $testSql[] = ' uid int(11) NOT NULL auto_increment,';
200 $testSql[] = ' pid int(11) DEFAULT \'0\' NOT NULL,';
201
202 $testSql[] = ' tstamp int(11) unsigned DEFAULT \'0\' NOT NULL,';
203 $testSql[] = ' crdate int(11) unsigned DEFAULT \'0\' NOT NULL,';
204 $testSql[] = ' cruser_id int(11) unsigned DEFAULT \'0\' NOT NULL,';
205 $testSql[] = ' deleted tinyint(4) unsigned DEFAULT \'0\' NOT NULL,';
206 $testSql[] = ' hidden tinyint(4) unsigned DEFAULT \'0\' NOT NULL,';
207 $testSql[] = ' starttime int(11) unsigned DEFAULT \'0\' NOT NULL,';
208 $testSql[] = ' endtime int(11) unsigned DEFAULT \'0\' NOT NULL,';
209
210 $testSql[] = ' input_1 varchar(255) DEFAULT \'\' NOT NULL,';
211 $testSql[] = ' input_2 varchar(255) DEFAULT \'\' NOT NULL,';
212 $testSql[] = ' select_child int(11) unsigned DEFAULT \'0\' NOT NULL,';
213
214 $testSql[] = ' PRIMARY KEY (uid),';
215 $testSql[] = ' KEY parent (pid,input_1),';
216 $testSql[] = ' KEY bar (tstamp,input_1(200),input_2(100),endtime)';
217 $testSql[] = ');';
218 $testSql = implode("\n", $testSql);
219 $expected = array(
220 'type' => 'CREATETABLE',
221 'TABLE' => 'tx_demo',
222 'FIELDS' => array(
223 'uid' => array(
224 'definition' => array(
225 'fieldType' => 'int',
226 'value' => '11',
227 'featureIndex' => array(
228 'NOTNULL' => array(
229 'keyword' => 'NOT NULL'
230 ),
231 'AUTO_INCREMENT' => array(
232 'keyword' => 'auto_increment'
233 )
234 )
235 )
236 ),
237 'pid' => array(
238 'definition' => array(
239 'fieldType' => 'int',
240 'value' => '11',
241 'featureIndex' => array(
242 'DEFAULT' => array(
243 'keyword' => 'DEFAULT',
244 'value' => array(
245 0 => '0',
246 1 => '\'',
247 )
248 ),
249 'NOTNULL' => array(
250 'keyword' => 'NOT NULL'
251 )
252 )
253 )
254 ),
255 'tstamp' => array(
256 'definition' => array(
257 'fieldType' => 'int',
258 'value' => '11',
259 'featureIndex' => array(
260 'UNSIGNED' => array(
261 'keyword' => 'unsigned'
262 ),
263 'DEFAULT' => array(
264 'keyword' => 'DEFAULT',
265 'value' => array(
266 0 => '0',
267 1 => '\''
268 )
269 ),
270 'NOTNULL' => array(
271 'keyword' => 'NOT NULL'
272 )
273 )
274 )
275 ),
276 'crdate' => array(
277 'definition' => array(
278 'fieldType' => 'int',
279 'value' => '11',
280 'featureIndex' => array(
281 'UNSIGNED' => array(
282 'keyword' => 'unsigned'
283 ),
284 'DEFAULT' => array(
285 'keyword' => 'DEFAULT',
286 'value' => array(
287 0 => '0',
288 1 => '\''
289 )
290 ),
291 'NOTNULL' => array(
292 'keyword' => 'NOT NULL'
293 )
294 )
295 )
296 ),
297 'cruser_id' => array(
298 'definition' => array(
299 'fieldType' => 'int',
300 'value' => '11',
301 'featureIndex' => array(
302 'UNSIGNED' => array(
303 'keyword' => 'unsigned'
304 ),
305 'DEFAULT' => array(
306 'keyword' => 'DEFAULT',
307 'value' => array(
308 0 => '0',
309 1 => '\'',
310 )
311 ),
312 'NOTNULL' => array(
313 'keyword' => 'NOT NULL'
314 )
315 )
316 )
317 ),
318 'deleted' => array(
319 'definition' => array(
320 'fieldType' => 'tinyint',
321 'value' => '4',
322 'featureIndex' => array(
323 'UNSIGNED' => array(
324 'keyword' => 'unsigned'
325 ),
326 'DEFAULT' => array(
327 'keyword' => 'DEFAULT',
328 'value' => array(
329 0 => '0',
330 1 => '\''
331 )
332 ),
333 'NOTNULL' => array(
334 'keyword' => 'NOT NULL'
335 )
336 )
337 )
338 ),
339 'hidden' => array(
340 'definition' => array(
341 'fieldType' => 'tinyint',
342 'value' => '4',
343 'featureIndex' => array(
344 'UNSIGNED' => array(
345 'keyword' => 'unsigned'
346 ),
347 'DEFAULT' => array(
348 'keyword' => 'DEFAULT',
349 'value' => array(
350 0 => '0',
351 1 => '\''
352 )
353 ),
354 'NOTNULL' => array(
355 'keyword' => 'NOT NULL'
356 )
357 )
358 )
359 ),
360 'starttime' => array(
361 'definition' => array(
362 'fieldType' => 'int',
363 'value' => '11',
364 'featureIndex' => array(
365 'UNSIGNED' => array(
366 'keyword' => 'unsigned'
367 ),
368 'DEFAULT' => array(
369 'keyword' => 'DEFAULT',
370 'value' => array(
371 0 => '0',
372 1 => '\''
373 )
374 ),
375 'NOTNULL' => array(
376 'keyword' => 'NOT NULL'
377 )
378 )
379 )
380 ),
381 'endtime' => array(
382 'definition' => array(
383 'fieldType' => 'int',
384 'value' => '11',
385 'featureIndex' => array(
386 'UNSIGNED' => array(
387 'keyword' => 'unsigned'
388 ),
389 'DEFAULT' => array(
390 'keyword' => 'DEFAULT',
391 'value' => array(
392 0 => '0',
393 1 => '\'',
394 )
395 ),
396 'NOTNULL' => array(
397 'keyword' => 'NOT NULL'
398 )
399 )
400 )
401 ),
402 'input_1' => array(
403 'definition' => array(
404 'fieldType' => 'varchar',
405 'value' => '255',
406 'featureIndex' => array(
407 'DEFAULT' => array(
408 'keyword' => 'DEFAULT',
409 'value' => array(
410 0 => '',
411 1 => '\'',
412 )
413 ),
414 'NOTNULL' => array(
415 'keyword' => 'NOT NULL'
416 )
417 )
418 )
419 ),
420 'input_2' => array(
421 'definition' => array(
422 'fieldType' => 'varchar',
423 'value' => '255',
424 'featureIndex' => array(
425 'DEFAULT' => array(
426 'keyword' => 'DEFAULT',
427 'value' => array(
428 0 => '',
429 1 => '\'',
430 )
431 ),
432 'NOTNULL' => array(
433 'keyword' => 'NOT NULL'
434 )
435 )
436 )
437 ),
438 'select_child' => array(
439 'definition' => array(
440 'fieldType' => 'int',
441 'value' => '11',
442 'featureIndex' => array(
443 'UNSIGNED' => array(
444 'keyword' => 'unsigned'
445 ),
446 'DEFAULT' => array(
447 'keyword' => 'DEFAULT',
448 'value' => array(
449 0 => '0',
450 1 => '\''
451 )
452 ),
453 'NOTNULL' => array(
454 'keyword' => 'NOT NULL'
455 )
456 )
457 )
458 )
459 ),
460 'KEYS' => array(
461 'PRIMARYKEY' => array(
462 0 => 'uid'
463 ),
464 'parent' => array(
465 0 => 'pid',
466 1 => 'input_1',
467 ),
468 'bar' => array(
469 0 => 'tstamp',
470 1 => 'input_1(200)',
471 2 => 'input_2(100)',
472 3 => 'endtime',
473 )
474 )
475 );
476
477 return array(
478 'test1' => array($testSql, $expected)
479 );
480 }
481
482 /**
483 * @test
484 * @dataProvider parseSQLDataProvider
485 * @param string $sql The SQL to trim
486 * @param array $expected The expected trimmed SQL with single space at the end
487 */
488 public function parseSQL($sql, $expected) {
489 $result = $this->subject->_callRef('parseSQL', $sql);
490 $this->assertSame($expected, $result);
491 }
492 }