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