2 namespace TYPO3\CMS\Dbal\Tests\Unit\Database;
34 $subject = $this->getAccessibleMock(\TYPO3\CMS\Dbal\Database\SqlParser::class, array(
'dummy'), array(),
'',
false);
36 $mockDatabaseConnection = $this->getMock(\TYPO3\CMS\Dbal\Database\DatabaseConnection::class, array(), array(),
'',
false);
37 $mockDatabaseConnection->lastHandlerKey =
'_DEFAULT';
38 $subject->_set(
'databaseConnection', $mockDatabaseConnection);
56 'field' =>
'fe_group',
81 'field' =>
'fe_group',
93 'type' =>
'FIND_IN_SET',
107 'type' =>
'FIND_IN_SET',
113 'field' =>
'fe_group'
123 'field' =>
'fe_group',
133 $output = $this->subject->compileWhereClause($clauses);
134 $parts = explode(
' OR ', $output);
135 $this->assertSame(count($clauses), count($parts));
136 $this->assertContains(
'IFNULL', $output);
147 'Nothing to trim' => array(
'SELECT * FROM test WHERE 1=1;',
'SELECT * FROM test WHERE 1=1 '),
148 'Space after ;' => array(
'SELECT * FROM test WHERE 1=1; ',
'SELECT * FROM test WHERE 1=1 '),
149 'Space before ;' => array(
'SELECT * FROM test WHERE 1=1 ;',
'SELECT * FROM test WHERE 1=1 '),
150 'Space before and after ;' => array(
'SELECT * FROM test WHERE 1=1 ; ',
'SELECT * FROM test WHERE 1=1 '),
151 'Linefeed after ;' => array(
'SELECT * FROM test WHERE 1=1' . LF .
';',
'SELECT * FROM test WHERE 1=1 '),
152 'Linefeed before ;' => array(
'SELECT * FROM test WHERE 1=1;' . LF,
'SELECT * FROM test WHERE 1=1 '),
153 'Linefeed before and after ;' => array(
'SELECT * FROM test WHERE 1=1' . LF .
';' . LF,
'SELECT * FROM test WHERE 1=1 '),
154 'Tab after ;' => array(
'SELECT * FROM test WHERE 1=1' . TAB .
';',
'SELECT * FROM test WHERE 1=1 '),
155 'Tab before ;' => array(
'SELECT * FROM test WHERE 1=1;' . TAB,
'SELECT * FROM test WHERE 1=1 '),
156 'Tab before and after ;' => array(
'SELECT * FROM test WHERE 1=1' . TAB .
';' . TAB,
'SELECT * FROM test WHERE 1=1 '),
168 $result = $this->subject->_call(
'trimSQL', $sql);
169 $this->assertSame($expected, $result);
181 'key definition without length' => array(
'(pid,input_1), ',
'_LIST',
'INDEX', array(
'pid',
'input_1')),
182 'key definition with length' => array(
'(pid,input_1(30)), ',
'_LIST',
'INDEX', array(
'pid',
'input_1(30)')),
183 'key definition without length (no mode)' => array(
'(pid,input_1), ',
'_LIST',
'', array(
'pid',
'input_1')),
184 'key definition with length (no mode)' => array(
'(pid,input_1(30)), ',
'_LIST',
'', array(
'pid',
'input_1(30)')),
185 'test1' => array(
'input_1 varchar(255) DEFAULT \'\' NOT NULL,',
'',
'', array(
'input_1')),
186 'test2' => array(
'varchar(255) DEFAULT \'\' NOT NULL,',
'',
'', array(
'varchar(255)')),
187 'test3' => array(
'DEFAULT \'\' NOT NULL,',
'',
'', array(
'DEFAULT')),
188 'test4' => array(
'\'\
' NOT NULL,',
'',
'', array(
'',
'\'')),
189 'test5' => array(
'NOT NULL,',
'',
'', array(
'NOT')),
190 'test6' => array(
'NULL,',
'',
'', array(
'NULL')),
191 'getValueOrParameter' => array(
'NULL,',
'',
'', array(
'NULL')),
205 $result = $this->subject->_callRef(
'getValue', $parseString, $comparator, $mode);
206 $this->assertSame($expected, $result);
217 $testSql[] =
'CREATE TABLE tx_demo (';
218 $testSql[] =
' uid int(11) NOT NULL auto_increment,';
219 $testSql[] =
' pid int(11) DEFAULT \'0\' NOT NULL,';
221 $testSql[] =
' tstamp int(11) unsigned DEFAULT \'0\' NOT NULL,';
222 $testSql[] =
' crdate int(11) unsigned DEFAULT \'0\' NOT NULL,';
223 $testSql[] =
' cruser_id int(11) unsigned DEFAULT \'0\' NOT NULL,';
224 $testSql[] =
' deleted tinyint(4) unsigned DEFAULT \'0\' NOT NULL,';
225 $testSql[] =
' hidden tinyint(4) unsigned DEFAULT \'0\' NOT NULL,';
226 $testSql[] =
' starttime int(11) unsigned DEFAULT \'0\' NOT NULL,';
227 $testSql[] =
' endtime int(11) unsigned DEFAULT \'0\' NOT NULL,';
229 $testSql[] =
' input_1 varchar(255) DEFAULT \'\' NOT NULL,';
230 $testSql[] =
' input_2 varchar(255) DEFAULT \'\' NOT NULL,';
231 $testSql[] =
' select_child int(11) unsigned DEFAULT \'0\' NOT NULL,';
233 $testSql[] =
' PRIMARY KEY (uid),';
234 $testSql[] =
' KEY parent (pid,input_1),';
235 $testSql[] =
' KEY bar (tstamp,input_1(200),input_2(100),endtime)';
237 $testSql = implode(
"\n", $testSql);
239 'type' =>
'CREATETABLE',
240 'TABLE' =>
'tx_demo',
243 'definition' => array(
244 'fieldType' =>
'int',
246 'featureIndex' => array(
248 'keyword' =>
'NOT NULL'
250 'AUTO_INCREMENT' => array(
251 'keyword' =>
'auto_increment'
257 'definition' => array(
258 'fieldType' =>
'int',
260 'featureIndex' => array(
262 'keyword' =>
'DEFAULT',
269 'keyword' =>
'NOT NULL'
275 'definition' => array(
276 'fieldType' =>
'int',
278 'featureIndex' => array(
280 'keyword' =>
'unsigned'
283 'keyword' =>
'DEFAULT',
290 'keyword' =>
'NOT NULL'
296 'definition' => array(
297 'fieldType' =>
'int',
299 'featureIndex' => array(
301 'keyword' =>
'unsigned'
304 'keyword' =>
'DEFAULT',
311 'keyword' =>
'NOT NULL'
316 'cruser_id' => array(
317 'definition' => array(
318 'fieldType' =>
'int',
320 'featureIndex' => array(
322 'keyword' =>
'unsigned'
325 'keyword' =>
'DEFAULT',
332 'keyword' =>
'NOT NULL'
338 'definition' => array(
339 'fieldType' =>
'tinyint',
341 'featureIndex' => array(
343 'keyword' =>
'unsigned'
346 'keyword' =>
'DEFAULT',
353 'keyword' =>
'NOT NULL'
359 'definition' => array(
360 'fieldType' =>
'tinyint',
362 'featureIndex' => array(
364 'keyword' =>
'unsigned'
367 'keyword' =>
'DEFAULT',
374 'keyword' =>
'NOT NULL'
379 'starttime' => array(
380 'definition' => array(
381 'fieldType' =>
'int',
383 'featureIndex' => array(
385 'keyword' =>
'unsigned'
388 'keyword' =>
'DEFAULT',
395 'keyword' =>
'NOT NULL'
401 'definition' => array(
402 'fieldType' =>
'int',
404 'featureIndex' => array(
406 'keyword' =>
'unsigned'
409 'keyword' =>
'DEFAULT',
416 'keyword' =>
'NOT NULL'
422 'definition' => array(
423 'fieldType' =>
'varchar',
425 'featureIndex' => array(
427 'keyword' =>
'DEFAULT',
434 'keyword' =>
'NOT NULL'
440 'definition' => array(
441 'fieldType' =>
'varchar',
443 'featureIndex' => array(
445 'keyword' =>
'DEFAULT',
452 'keyword' =>
'NOT NULL'
457 'select_child' => array(
458 'definition' => array(
459 'fieldType' =>
'int',
461 'featureIndex' => array(
463 'keyword' =>
'unsigned'
466 'keyword' =>
'DEFAULT',
473 'keyword' =>
'NOT NULL'
480 'PRIMARYKEY' => array(
497 'test1' => array($testSql, $expected)
509 $result = $this->subject->_callRef(
'parseSQL', $sql);
510 $this->assertSame($expected, $result);
518 $parseString =
'SELECT *' . LF .
'FROM pages WHERE pid IN (1,2,3,4)';
519 $regex =
'^SELECT[[:space:]]+(.*)[[:space:]]+';
521 $fields = $this->subject->_callRef(
'nextPart', $parseString, $regex, $trimAll);
522 $this->assertEquals(
'*', $fields);
523 $this->assertEquals(
'FROM pages WHERE pid IN (1,2,3,4)', $parseString);
524 $regex =
'^FROM ([^)]+) WHERE';
525 $table = $this->subject->_callRef(
'nextPart', $parseString, $regex);
526 $this->assertEquals(
'pages', $table);
527 $this->assertEquals(
'pages WHERE pid IN (1,2,3,4)', $parseString);
535 $parseString =
'1024';
536 $result = $this->subject->_callRef(
'getValue', $parseString);
537 $expected = array(1024);
538 $this->assertEquals($expected, $result);
547 $parseString =
'"some owner\\\'s string"';
548 $result = $this->subject->_callRef(
'getValue', $parseString);
549 $expected = array(
'some owner\'s string',
'"');
550 $this->assertEquals($expected, $result);
559 $parseString =
'\'some owner\\\
's string\'';
560 $result = $this->subject->_callRef(
'getValue', $parseString);
561 $expected = array(
'some owner\'s string',
'\'');
562 $this->assertEquals($expected, $result);
571 $parseString =
'"the \\"owner\\" is here"';
572 $result = $this->subject->_callRef(
'getValue', $parseString);
573 $expected = array(
'the "owner" is here',
'"');
574 $this->assertEquals($expected, $result);
582 $parseString =
'( 1, 2, 3 ,4)';
584 $result = $this->subject->_callRef(
'getValue', $parseString, $operator);
591 $this->assertEquals($expected, $result);
599 $parseString =
'uid IN (1,2) AND (starttime < ' . time() .
' OR cruser_id + 10 < 20)';
600 $result = $this->subject->parseWhereClause($parseString);
601 $this->assertInternalType(
'array', $result);
602 $this->assertEmpty($parseString);
610 $sql =
'SELECT * FROM pages';
612 $result = $this->subject->debug_testSQL($sql);
613 $this->assertEquals($expected, $this->
cleanSql($result));
621 $sql =
'TRUNCATE TABLE be_users';
623 $result = $this->subject->debug_testSQL($sql);
624 $this->assertEquals($expected, $this->
cleanSql($result));
633 $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\'';
634 $result = $this->subject->parseWhereClause($parseString);
635 $this->assertInternalType(
'array', $result);
636 $this->assertEmpty($parseString);
638 $result = $this->subject->compileWhereClause($result);
639 $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\'';
640 $this->assertEquals($expected, $this->
cleanSql($result));
648 $parseString =
'INSERT INTO static_country_zones VALUES(\'483\', \'0\', \'NL\', \'NLD\', \'528\', \'DR\', \'Drenthe\', \'\');';
649 $components = $this->subject->_callRef(
'parseINSERT', $parseString);
650 $this->assertInternalType(
'array', $components);
652 $result = $this->subject->compileSQL($components);
653 $expected =
'INSERT INTO static_country_zones VALUES (\'483\',\'0\',\'NL\',\'NLD\',\'528\',\'DR\',\'Drenthe\',\'\')';
654 $this->assertEquals($expected, $this->
cleanSql($result));
662 $parseString =
'INSERT INTO static_country_zones VALUES (\'483\', \'0\', \'NL\', \'NLD\', \'528\', \'DR\', \'Drenthe\', \'\');';
663 $components = $this->subject->_callRef(
'parseINSERT', $parseString);
664 $this->assertInternalType(
'array', $components);
666 $result = $this->subject->compileSQL($components);
667 $expected =
'INSERT INTO static_country_zones VALUES (\'483\',\'0\',\'NL\',\'NLD\',\'528\',\'DR\',\'Drenthe\',\'\')';
668 $this->assertEquals($expected, $this->
cleanSql($result));
676 $parseString =
'INSERT INTO static_territories (uid, pid, tr_iso_nr, tr_parent_iso_nr, tr_name_en) ';
677 $parseString .=
'VALUES (\'1\', \'0\', \'2\', \'0\', \'Africa\');';
678 $components = $this->subject->_callRef(
'parseINSERT', $parseString);
679 $this->assertInternalType(
'array', $components);
681 $result = $this->subject->compileSQL($components);
682 $expected =
'INSERT INTO static_territories (uid,pid,tr_iso_nr,tr_parent_iso_nr,tr_name_en) ';
683 $expected .=
'VALUES (\'1\',\'0\',\'2\',\'0\',\'Africa\')';
684 $this->assertEquals($expected, $this->
cleanSql($result));
692 $parseString =
'INSERT INTO static_territories VALUES (\'1\', \'0\', \'2\', \'0\', \'Africa\'),(\'2\', \'0\', \'9\', \'0\', \'Oceania\'),' .
'(\'3\', \'0\', \'19\', \'0\', \'Americas\'),(\'4\', \'0\', \'142\', \'0\', \'Asia\');';
693 $components = $this->subject->_callRef(
'parseINSERT', $parseString);
694 $this->assertInternalType(
'array', $components);
696 $result = $this->subject->compileSQL($components);
697 $expected =
'INSERT INTO static_territories VALUES (\'1\',\'0\',\'2\',\'0\',\'Africa\'),(\'2\',\'0\',\'9\',\'0\',\'Oceania\'),(\'3\',\'0\',\'19\',\'0\',\'Americas\'),(\'4\',\'0\',\'142\',\'0\',\'Asia\')';
698 $this->assertEquals($expected, $this->
cleanSql($result));
706 $parseString =
'INSERT INTO static_territories (uid, pid, tr_iso_nr, tr_parent_iso_nr, tr_name_en) ';
707 $parseString .=
'VALUES (\'1\', \'0\', \'2\', \'0\', \'Africa\'),(\'2\', \'0\', \'9\', \'0\', \'Oceania\');';
708 $components = $this->subject->_callRef(
'parseINSERT', $parseString);
709 $this->assertInternalType(
'array', $components);
711 $result = $this->subject->compileSQL($components);
712 $expected =
'INSERT INTO static_territories (uid,pid,tr_iso_nr,tr_parent_iso_nr,tr_name_en) ';
713 $expected .=
'VALUES (\'1\',\'0\',\'2\',\'0\',\'Africa\'),(\'2\',\'0\',\'9\',\'0\',\'Oceania\')';
714 $this->assertEquals($expected, $this->
cleanSql($result));
723 $parseString =
'IFNULL(tt_news_cat_mm.uid_foreign,0) IN (21,22)';
724 $result = $this->subject->parseWhereClause($parseString);
725 $this->assertInternalType(
'array', $result);
726 $this->assertEmpty($parseString);
735 $parseString =
'1=1 AND IFNULL(tt_news_cat_mm.uid_foreign,0) IN (21,22) AND tt_news.sys_language_uid IN (0,-1) ';
736 $parseString .=
'AND tt_news.pid > 0 AND tt_news.pid IN (61) AND tt_news.deleted=0 AND tt_news.t3ver_state<=0 ';
737 $parseString .=
'AND tt_news.hidden=0 AND tt_news.starttime<=1266065460 AND (tt_news.endtime=0 OR tt_news.endtime>1266065460) ';
738 $parseString .=
'AND (tt_news.fe_group=\'\' OR tt_news.fe_group IS NULL OR tt_news.fe_group=\'0\' ';
739 $parseString .=
'OR (tt_news.fe_group LIKE \'%,0,%\' OR tt_news.fe_group LIKE \'0,%\' OR tt_news.fe_group LIKE \'%,0\' ';
740 $parseString .=
'OR tt_news.fe_group=\'0\') OR (tt_news.fe_group LIKE \'%,-1,%\' OR tt_news.fe_group LIKE \'-1,%\' ';
741 $parseString .=
'OR tt_news.fe_group LIKE \'%,-1\' OR tt_news.fe_group=\'-1\'))';
743 $result = $this->subject->parseWhereClause($parseString);
744 $this->assertInternalType(
'array', $result);
745 $this->assertEmpty($parseString);
754 $parseString =
'SELECT * FROM tx_irfaq_q_cat_mm WHERE IFNULL(tx_irfaq_q_cat_mm.uid_foreign,0) = 1';
755 $components = $this->subject->_callRef(
'parseSELECT', $parseString);
756 $this->assertInternalType(
'array', $components);
758 $result = $this->subject->compileSQL($components);
759 $expected =
'SELECT * FROM tx_irfaq_q_cat_mm WHERE IFNULL(tx_irfaq_q_cat_mm.uid_foreign, 0) = 1';
760 $this->assertEquals($expected, $this->
cleanSql($result));
769 $parseString =
'CAST(parent AS CHAR) != \'\'';
770 $result = $this->subject->parseWhereClause($parseString);
771 $this->assertInternalType(
'array', $result);
772 $this->assertEmpty($parseString);
781 $parseString =
'SELECT * FROM sys_category WHERE CAST(parent AS CHAR) != \'\'';
782 $components = $this->subject->_callRef(
'parseSELECT', $parseString);
783 $this->assertInternalType(
'array', $components);
785 $result = $this->subject->compileSQL($components);
786 $expected =
'SELECT * FROM sys_category WHERE CAST(parent AS CHAR) != \'\'';
787 $this->assertEquals($expected, $this->
cleanSql($result));
796 $parseString =
'ALTER TABLE tx_realurl_pathcache ENGINE=InnoDB';
797 $components = $this->subject->_callRef(
'parseALTERTABLE', $parseString);
798 $this->assertInternalType(
'array', $components);
800 $result = $this->subject->compileSQL($components);
801 $expected =
'ALTER TABLE tx_realurl_pathcache ENGINE = InnoDB';
802 $this->assertEquals($expected, $this->
cleanSql($result));
811 $parseString =
'ALTER TABLE `index_phash` DEFAULT CHARACTER SET utf8';
812 $components = $this->subject->_callRef(
'parseALTERTABLE', $parseString);
813 $this->assertInternalType(
'array', $components);
815 $result = $this->subject->compileSQL($components);
816 $expected =
'ALTER TABLE index_phash DEFAULT CHARACTER SET utf8';
817 $this->assertEquals($expected, $this->
cleanSql($result));
826 $parseString =
'ALTER TABLE sys_collection ADD KEY parent (pid,deleted)';
827 $components = $this->subject->_callRef(
'parseALTERTABLE', $parseString);
828 $this->assertInternalType(
'array', $components);
830 $result = $this->subject->compileSQL($components);
831 $expected =
'ALTER TABLE sys_collection ADD KEY parent (pid,deleted)';
832 $this->assertSame($expected, $this->
cleanSql($result));
841 $parseString =
'ALTER TABLE sys_collection DROP KEY parent';
842 $components = $this->subject->_callRef(
'parseALTERTABLE', $parseString);
843 $this->assertInternalType(
'array', $components);
845 $result = $this->subject->compileSQL($components);
846 $expected =
'ALTER TABLE sys_collection DROP KEY parent';
847 $this->assertSame($expected, $this->
cleanSql($result));
856 $parseString =
'SELECT * FROM fe_users WHERE FIND_IN_SET(10, usergroup)';
857 $components = $this->subject->_callRef(
'parseSELECT', $parseString);
858 $this->assertInternalType(
'array', $components);
860 $result = $this->subject->compileSQL($components);
861 $expected =
'SELECT * FROM fe_users WHERE FIND_IN_SET(10, usergroup)';
862 $this->assertEquals($expected, $this->
cleanSql($result));
871 $parseString =
'SELECT * FROM pages WHERE title=\'1\\\'\' AND deleted=0';
872 $result = $this->subject->_callRef(
'parseSELECT', $parseString);
873 $this->assertInternalType(
'array', $result);
874 $this->assertEmpty($result[
'parseString']);
885 $parseString =
'be_users INNER JOIN pages ON pages.cruser_id = be_users.uid';
887 $result = $this->subject->parseFromTables($parseString);
888 $this->assertInternalType(
'array', $result);
889 $this->assertEmpty($parseString);
897 $parseString =
'be_users LEFT OUTER JOIN pages ON be_users.uid = pages.cruser_id';
899 $result = $this->subject->parseFromTables($parseString);
900 $this->assertInternalType(
'array', $result);
901 $this->assertEmpty($parseString);
910 $parseString =
'tx_powermail_fieldsets RIGHT JOIN tt_content ON tx_powermail_fieldsets.tt_content = tt_content.uid';
912 $result = $this->subject->parseFromTables($parseString);
913 $this->assertInternalType(
'array', $result);
914 $this->assertEmpty($parseString);
922 $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';
923 $result = $this->subject->parseFromTables($parseString);
924 $this->assertInternalType(
'array', $result);
925 $this->assertEmpty($parseString);
934 $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';
935 $result = $this->subject->parseFromTables($parseString);
936 $this->assertInternalType(
'array', $result);
937 $this->assertEmpty($parseString);
945 $sql =
'SELECT pages.uid, be_users.username FROM be_users INNER JOIN pages ON pages.cruser_id = be_users.uid';
947 $result = $this->subject->debug_testSQL($sql);
948 $expected =
'SELECT pages.uid, be_users.username FROM be_users INNER JOIN pages ON pages.cruser_id=be_users.uid';
949 $this->assertEquals($expected, $this->
cleanSql($result));
957 $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';
959 $result = $this->subject->debug_testSQL($sql);
960 $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';
961 $this->assertEquals($expected, $this->
cleanSql($result));
970 $sql =
'SELECT * FROM T1 LEFT OUTER JOIN T2 ON T2.pid = T1.uid AND T2.size = 4 WHERE T1.cr_userid = 1';
972 $result = $this->subject->debug_testSQL($sql);
973 $expected =
'SELECT * FROM T1 LEFT OUTER JOIN T2 ON T2.pid=T1.uid AND T2.size=4 WHERE T1.cr_userid = 1';
974 $this->assertEquals($expected, $this->
cleanSql($result));
983 $sql =
'SELECT * FROM sys_file_processedfile LEFT JOIN sys_registry ON entry_key = sys_file_processedfile.uid AND entry_namespace = \'ProcessedFileChecksumUpdate\'';
985 $result = $this->subject->debug_testSQL($sql);
986 $expected =
'SELECT * FROM sys_file_processedfile LEFT JOIN sys_registry ON entry_key=sys_file_processedfile.uid AND entry_namespace=\'ProcessedFileChecksumUpdate\'';
987 $this->assertEquals($expected, $this->
cleanSql($result));
996 $sql =
'SELECT * FROM sys_file_processedfile LEFT JOIN sys_registry ON CAST(entry_key AS INTEGER) = sys_file_processedfile.uid AND entry_namespace = \'ProcessedFileChecksumUpdate\'';
998 $result = $this->subject->debug_testSQL($sql);
999 $expected =
'SELECT * FROM sys_file_processedfile LEFT JOIN sys_registry ON CAST(entry_key AS INTEGER)=sys_file_processedfile.uid AND entry_namespace=\'ProcessedFileChecksumUpdate\'';
1000 $this->assertEquals($expected, $this->
cleanSql($result));
1009 $sql =
'SELECT * FROM sys_file_processedfile LEFT JOIN sys_registry ON entry_key = CAST(sys_file_processedfile.uid AS CHAR) AND entry_namespace = \'ProcessedFileChecksumUpdate\'';
1011 $result = $this->subject->debug_testSQL($sql);
1012 $expected =
'SELECT * FROM sys_file_processedfile LEFT JOIN sys_registry ON entry_key=CAST(sys_file_processedfile.uid AS CHAR) AND entry_namespace=\'ProcessedFileChecksumUpdate\'';
1013 $this->assertEquals($expected, $this->
cleanSql($result));
1022 $sql =
'SELECT * FROM T1 LEFT OUTER JOIN T2 ON T2.size < 4 OR T2.pid = T1.uid WHERE T1.cr_userid = 1';
1024 $result = $this->subject->debug_testSQL($sql);
1025 $expected =
'SELECT * FROM T1 LEFT OUTER JOIN T2 ON T2.size<4 OR T2.pid=T1.uid WHERE T1.cr_userid = 1';
1026 $this->assertEquals($expected, $this->
cleanSql($result));
1039 CREATE TABLE tx_realurl_uniqalias (
1040 uid int(11) NOT NULL auto_increment,
1041 tstamp int(11) DEFAULT \'0\' NOT NULL,
1042 tablename varchar(60) DEFAULT \'\' NOT NULL,
1043 field_alias varchar(255) DEFAULT \'\' NOT NULL,
1044 field_id varchar(60) DEFAULT \'\' NOT NULL,
1045 value_alias varchar(255) DEFAULT \'\' NOT NULL,
1046 value_id int(11) DEFAULT \'0\' NOT NULL,
1047 lang int(11) DEFAULT \'0\' NOT NULL,
1048 expire int(11) DEFAULT \'0\' NOT NULL,
1051 KEY tablename (tablename),
1052 KEY bk_realurl01 (field_alias,field_id,value_id,lang,expire),
1053 KEY bk_realurl02 (tablename,field_alias,field_id,value_alias(220),expire)
1056 $result = $this->subject->_callRef(
'parseCREATETABLE', $parseString);
1057 $this->assertInternalType(
'array', $result);
1066 $parseString =
'CREATE TABLE fe_users (' .
1067 'testdate date DEFAULT \'0000-00-00\',' .
1068 'testdatetime datetime DEFAULT \'0000-00-00 00:00:00\',' .
1069 'testtimestamp timestamp DEFAULT \'0000-00-00 00:00:00\',' .
1070 'testtime time DEFAULT \'00:00:00\',' .
1071 'testyear year DEFAULT \'0000\')';
1073 $components = $this->subject->_callRef(
'parseCREATETABLE', $parseString);
1074 $actual = $this->subject->compileSQL($components);
1075 $this->assertEquals($this->
cleanSql($parseString), $actual);
1084 $parseString =
'ALTER TABLE tx_realurl_uniqalias ADD KEY bk_realurl02 (tablename,field_alias,field_id,value_alias(220),expire)';
1085 $result = $this->subject->_callRef(
'parseALTERTABLE', $parseString);
1086 $this->assertInternalType(
'array', $result);
1095 $sql =
'ALTER TABLE static_territories ADD UNIQUE uid (uid)';
1097 $alterTables = $this->subject->_callRef(
'parseALTERTABLE', $sql);
1098 $queries = $this->subject->compileSQL($alterTables);
1099 $this->assertEquals($expected, $queries);
1111 $parseString =
'process_id IN (SELECT process_id FROM tx_crawler_process WHERE active=0 AND deleted=0)';
1112 $result = $this->subject->parseWhereClause($parseString);
1113 $this->assertInternalType(
'array', $result);
1114 $this->assertEmpty($parseString);
1123 $sql =
'SELECT * FROM tx_crawler_queue WHERE process_id IN (SELECT process_id FROM tx_crawler_process WHERE active=0 AND deleted=0)';
1125 $result = $this->subject->debug_testSQL($sql);
1126 $expected =
'SELECT * FROM tx_crawler_queue WHERE process_id IN (SELECT process_id FROM tx_crawler_process WHERE active = 0 AND deleted = 0)';
1127 $this->assertEquals($expected, $this->
cleanSql($result));
1136 $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)';
1137 $result = $this->subject->parseWhereClause($parseString);
1138 $this->assertInternalType(
'array', $result);
1139 $this->assertEmpty($parseString);
1148 $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)';
1150 $result = $this->subject->debug_testSQL($sql);
1151 $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)';
1152 $this->assertEquals($expected, $this->
cleanSql($result));
1164 $parseString =
'CASE WHEN 1>0 THEN 2 ELSE 1 END AS foo, other_column';
1165 $result = $this->subject->parseFieldList($parseString);
1166 $this->assertInternalType(
'array', $result);
1167 $this->assertEmpty($parseString);
1176 $sql =
'SELECT CASE WHEN 1>0 THEN 2 ELSE 1 END AS foo, other_column FROM mytable';
1178 $result = $this->subject->debug_testSQL($sql);
1179 $expected =
'SELECT CASE WHEN 1 > 0 THEN 2 ELSE 1 END AS foo, other_column FROM mytable';
1180 $this->assertEquals($expected, $this->
cleanSql($result));
1189 $parseString =
'CASE column WHEN 1 THEN \'one\' WHEN 2 THEN \'two\' ELSE \'out of range\' END AS number';
1190 $result = $this->subject->parseFieldList($parseString);
1191 $this->assertInternalType(
'array', $result);
1192 $this->assertEmpty($parseString);
1201 $parseString =
'MIN(CASE WHEN foo < 100 THEN NULL ELSE foo END) AS foo';
1202 $result = $this->subject->parseFieldList($parseString);
1203 $this->assertInternalType(
'array', $result);
1204 $this->assertEmpty($parseString);
1213 $sql =
'SELECT CASE column WHEN 1 THEN \'one\' WHEN 2 THEN \'two\' ELSE \'out of range\' END AS number FROM mytable';
1215 $result = $this->subject->debug_testSQL($sql);
1216 $expected =
'SELECT CASE column WHEN 1 THEN \'one\' WHEN 2 THEN \'two\' ELSE \'out of range\' END AS number FROM mytable';
1217 $this->assertEquals($expected, $this->
cleanSql($result));
1226 $sql =
'SELECT * FROM tx_templavoila_tmplobj WHERE LOCATE(\'(fce)\', datastructure)>0';
1228 $result = $this->subject->debug_testSQL($sql);
1229 $expected =
'SELECT * FROM tx_templavoila_tmplobj WHERE LOCATE(\'(fce)\', datastructure) > 0';
1230 $this->assertEquals($expected, $this->
cleanSql($result));
1239 $sql =
'SELECT * FROM tx_templavoila_tmplobj WHERE LOCATE(\'(fce)\' , datastructure ,10)>0';
1240 $expected =
'SELECT * FROM tx_templavoila_tmplobj WHERE LOCATE(\'(fce)\', datastructure, 10) > 0';
1241 $result = $this->
cleanSql($this->subject->debug_testSQL($sql));
1242 $this->assertEquals($expected, $result);
1252 $sql =
'SELECT *, CASE WHEN LOCATE(\'(fce)\', datastructure)>0 THEN 2 ELSE 1 END AS scope FROM tx_templavoila_tmplobj';
1254 $result = $this->subject->debug_testSQL($sql);
1255 $expected =
'SELECT *, CASE WHEN LOCATE(\'(fce)\', datastructure) > 0 THEN 2 ELSE 1 END AS scope FROM tx_templavoila_tmplobj';
1256 $this->assertEquals($expected, $this->
cleanSql($result));
1268 $sql =
'SELECT * FROM pages WHERE pid = :pid ORDER BY title';
1270 $result = $this->subject->debug_testSQL($sql);
1271 $expected =
'SELECT * FROM pages WHERE pid = :pid ORDER BY title';
1272 $this->assertEquals($expected, $this->
cleanSql($result));
1281 $sql =
'SELECT * FROM pages WHERE pid = ? ORDER BY title';
1283 $result = $this->subject->debug_testSQL($sql);
1284 $expected =
'SELECT * FROM pages WHERE pid = ? ORDER BY title';
1285 $this->assertEquals($expected, $this->
cleanSql($result));
1294 $sql =
'SELECT * FROM pages WHERE pid = :pid1 OR pid = :pid2';
1295 $components = $this->subject->_callRef(
'parseSELECT', $sql);
1296 $this->assertInternalType(
'array', $components[
'parameters']);
1297 $this->assertEquals(2, count($components[
'parameters']));
1298 $this->assertTrue(isset($components[
'parameters'][
':pid1']));
1299 $this->assertTrue(isset($components[
'parameters'][
':pid2']));
1308 $sql =
'SELECT * FROM pages WHERE uid = :pageId OR uid IN (SELECT uid FROM pages WHERE pid = :pageId)';
1310 $components = $this->subject->_callRef(
'parseSELECT', $sql);
1311 $components[
'parameters'][
':pageId'][0] = $pageId;
1313 $result = $this->subject->compileSQL($components);
1314 $expected =
'SELECT * FROM pages WHERE uid = 12 OR uid IN (SELECT uid FROM pages WHERE pid = 12)';
1315 $this->assertEquals($expected, $this->
cleanSql($result));
1324 $sql =
'SELECT * FROM pages WHERE pid = :pid AND title NOT LIKE \':pid\'';
1326 $components = $this->subject->_callRef(
'parseSELECT', $sql);
1327 $components[
'parameters'][
':pid'][0] = $pid;
1329 $result = $this->subject->compileSQL($components);
1330 $expected =
'SELECT * FROM pages WHERE pid = ' . $pid .
' AND title NOT LIKE \':pid\'';
1331 $this->assertEquals($expected, $this->
cleanSql($result));
1340 $sql =
'SELECT * FROM pages WHERE pid = ? AND timestamp < ? AND title != \'How to test?\'';
1341 $parameterValues = array(12, 1281782690);
1342 $components = $this->subject->_callRef(
'parseSELECT', $sql);
1343 $questionMarkParamCount = count($components[
'parameters'][
'?']);
1344 for ($i = 0; $i < $questionMarkParamCount; $i++) {
1345 $components[
'parameters'][
'?'][$i][0] = $parameterValues[$i];
1348 $result = $this->subject->compileSQL($components);
1349 $expected =
'SELECT * FROM pages WHERE pid = 12 AND timestamp < 1281782690 AND title != \'How to test?\'';
1350 $this->assertEquals($expected, $this->
cleanSql($result));