Fixed bug #6196: IFNULL operator cannot be parsed
[Packages/TYPO3.CMS.git] / typo3 / sysext / dbal / tests / db_oracle_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 require_once('FakeDbConnection.php');
28
29 /**
30 * Testcase for class ux_t3lib_db. Testing Oracle database handling.
31 *
32 * $Id$
33 *
34 * @author Xavier Perseguers <typo3@perseguers.ch>
35 *
36 * @package TYPO3
37 * @subpackage dbal
38 */
39 class db_oracle_testcase extends BaseTestCase {
40
41 /**
42 * @var t3lib_db
43 */
44 protected $db;
45
46 /**
47 * @var array
48 */
49 protected $dbalConfig;
50
51 /**
52 * Prepares the environment before running a test.
53 */
54 public function setUp() {
55 // Backup DBAL configuration
56 $this->dbalConfig = $GLOBALS['TYPO3_CONF_VARS']['EXTCONF']['dbal'];
57 // Backup database connection
58 $this->db = $GLOBALS['TYPO3_DB'];
59 // Reconfigure DBAL to use Oracle
60 require('fixtures/oci8.config.php');
61
62 $className = self::buildAccessibleProxy('ux_t3lib_db');
63 $GLOBALS['TYPO3_DB'] = new $className;
64 $parserClassName = self::buildAccessibleProxy('ux_t3lib_sqlparser');
65 $GLOBALS['TYPO3_DB']->SQLparser = new $parserClassName;
66
67 // Initialize a fake Oracle connection
68 FakeDbConnection::connect($GLOBALS['TYPO3_DB'], 'oci8');
69
70 $this->assertTrue($GLOBALS['TYPO3_DB']->handlerInstance['_DEFAULT']->isConnected());
71 }
72
73 /**
74 * Cleans up the environment after running a test.
75 */
76 public function tearDown() {
77 // Clear DBAL-generated cache files
78 $GLOBALS['TYPO3_DB']->clearCachedFieldInfo();
79 // Restore DBAL configuration
80 $GLOBALS['TYPO3_CONF_VARS']['EXTCONF']['dbal'] = $this->dbalConfig;
81 // Restore DB connection
82 $GLOBALS['TYPO3_DB'] = $this->db;
83 }
84
85 /**
86 * Cleans a SQL query.
87 *
88 * @param mixed $sql
89 * @return mixed (string or array)
90 */
91 private function cleanSql($sql) {
92 if (!is_string($sql)) {
93 return $sql;
94 }
95
96 $sql = str_replace("\n", ' ', $sql);
97 $sql = preg_replace('/\s+/', ' ', $sql);
98 return trim($sql);
99 }
100
101 /**
102 * @test
103 */
104 public function configurationIsUsingAdodbAndDriverOci8() {
105 $configuration = $GLOBALS['TYPO3_DB']->conf['handlerCfg'];
106 $this->assertTrue(is_array($configuration) && count($configuration) > 0, 'No configuration found');
107 $this->assertEquals('adodb', $configuration['_DEFAULT']['type']);
108 $this->assertTrue($GLOBALS['TYPO3_DB']->runningADOdbDriver('oci8') !== FALSE, 'Not using oci8 driver');
109 }
110
111 /**
112 * @test
113 */
114 public function tablesWithMappingAreDetected() {
115 $tablesWithMapping = array_keys($GLOBALS['TYPO3_CONF_VARS']['EXTCONF']['dbal']['mapping']);
116
117 foreach ($GLOBALS['TYPO3_DB']->cache_fieldType as $table => $fieldTypes) {
118 $tableDef = $GLOBALS['TYPO3_DB']->_call('map_needMapping', $table);
119
120 if (in_array($table, $tablesWithMapping)) {
121 self::assertTrue(is_array($tableDef), 'Table ' . $table . ' was expected to need mapping');
122 } else {
123 self::assertFalse($tableDef, 'Table ' . $table . ' was not expected to need mapping');
124 }
125 }
126 }
127
128 /**
129 * @test
130 * @see http://bugs.typo3.org/view.php?id=12897
131 */
132 public function sqlHintIsRemoved() {
133 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
134 '/*! SQL_NO_CACHE */ content',
135 'tx_realurl_urlencodecache',
136 '1=1'
137 ));
138 $expected = 'SELECT "content" FROM "tx_realurl_urlencodecache" WHERE 1 = 1';
139 $this->assertEquals($expected, $query);
140 }
141
142 /**
143 * @test
144 */
145 public function canCompileInsertWithFields() {
146 $parseString = 'INSERT INTO static_territories (uid, pid, tr_iso_nr, tr_parent_iso_nr, tr_name_en) ';
147 $parseString .= "VALUES ('1', '0', '2', '0', 'Africa');";
148 $components = $GLOBALS['TYPO3_DB']->SQLparser->_callRef('parseINSERT', $parseString);
149
150 $this->assertTrue(is_array($components), $components);
151 $insert = $GLOBALS['TYPO3_DB']->SQLparser->_callRef('compileINSERT', $components);
152
153 $expected = array(
154 'uid' => '1',
155 'pid' => '0',
156 'tr_iso_nr' => '2',
157 'tr_parent_iso_nr' => '0',
158 'tr_name_en' => 'Africa',
159 );
160 $this->assertEquals($expected, $insert);
161 }
162
163 /**
164 * @test
165 * http://bugs.typo3.org/view.php?id=13209
166 */
167 public function canCompileExtendedInsert() {
168 $parseString = "INSERT INTO static_territories VALUES ('1', '0', '2', '0', 'Africa'),('2', '0', '9', '0', 'Oceania')," .
169 "('3', '0', '19', '0', 'Americas'),('4', '0', '142', '0', 'Asia');";
170 $components = $GLOBALS['TYPO3_DB']->SQLparser->_callRef('parseINSERT', $parseString);
171
172 $this->assertTrue(is_array($components), $components);
173 $insert = $GLOBALS['TYPO3_DB']->SQLparser->_callRef('compileINSERT', $components);
174
175 $this->assertEquals(4, count($insert));
176
177 for ($i = 0; $i < count($insert); $i++) {
178 foreach (t3lib_div::trimExplode(',', 'uid,pid,tr_iso_nr,tr_parent_iso_nr,tr_name_en') as $field) {
179 $this->assertTrue(isset($insert[$i][$field]), 'Could not find ' . $field . ' column');
180 }
181 }
182 }
183
184 /**
185 * @test
186 * http://bugs.typo3.org/view.php?id=12858
187 */
188 public function sqlForInsertWithMultipleRowsIsValid() {
189 $fields = array('uid', 'pid', 'title', 'body');
190 $rows = array(
191 array('1', '2', 'Title #1', 'Content #1'),
192 array('3', '4', 'Title #2', 'Content #2'),
193 array('5', '6', 'Title #3', 'Content #3'),
194 );
195 $query = $GLOBALS['TYPO3_DB']->INSERTmultipleRows('tt_content', $fields, $rows);
196
197 $expected[0] = 'INSERT INTO "tt_content" ( "uid", "pid", "title", "body" ) VALUES ( \'1\', \'2\', \'Title #1\', \'Content #1\' )';
198 $expected[1] = 'INSERT INTO "tt_content" ( "uid", "pid", "title", "body" ) VALUES ( \'3\', \'4\', \'Title #2\', \'Content #2\' )';
199 $expected[2] = 'INSERT INTO "tt_content" ( "uid", "pid", "title", "body" ) VALUES ( \'5\', \'6\', \'Title #3\', \'Content #3\' )';
200
201 $this->assertEquals(count($expected), count($query));
202 for ($i = 0; $i < count($query); $i++) {
203 $this->assertTrue(is_array($query[$i]), 'Expected array: ' . $query[$i]);
204 $this->assertEquals(1, count($query[$i]));
205 $this->assertEquals($expected[$i], $this->cleanSql($query[$i][0]));
206 }
207 }
208
209 ///////////////////////////////////////
210 // Tests concerning quoting
211 ///////////////////////////////////////
212
213 /**
214 * @test
215 */
216 public function selectQueryIsProperlyQuoted() {
217 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
218 'uid', // select fields
219 'tt_content', // from table
220 'pid=1', // where clause
221 'cruser_id', // group by
222 'tstamp' // order by
223 ));
224 $expected = 'SELECT "uid" FROM "tt_content" WHERE "pid" = 1 GROUP BY "cruser_id" ORDER BY "tstamp"';
225 $this->assertEquals($expected, $query);
226 }
227
228 /**
229 * @test
230 * http://bugs.typo3.org/view.php?id=13504
231 */
232 public function truncateQueryIsProperlyQuoted() {
233 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->TRUNCATEquery('be_users'));
234 $expected = 'TRUNCATE TABLE "be_users"';
235 $this->assertEquals($expected, $query);
236 }
237
238 /**
239 * @test
240 * @see http://bugs.typo3.org/view.php?id=2438
241 */
242 public function distinctFieldIsProperlyQuoted() {
243 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
244 'COUNT(DISTINCT pid)', // select fields
245 'tt_content', // from table
246 '1=1' // where clause
247 ));
248 $expected = 'SELECT COUNT(DISTINCT "pid") FROM "tt_content" WHERE 1 = 1';
249 $this->assertEquals($expected, $query);
250 }
251
252 /**
253 * @test
254 * @see http://bugs.typo3.org/view.php?id=10411
255 * @remark Remapping is not expected here
256 */
257 public function multipleInnerJoinsAreProperlyQuoted() {
258 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
259 '*',
260 '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',
261 '1=1'
262 ));
263 $expected = 'SELECT * FROM "tt_news_cat"';
264 $expected .= ' INNER JOIN "tt_news_cat_mm" ON "tt_news_cat"."uid"="tt_news_cat_mm"."uid_foreign"';
265 $expected .= ' INNER JOIN "tt_news" ON "tt_news"."uid"="tt_news_cat_mm"."uid_local"';
266 $expected .= ' WHERE 1 = 1';
267 $this->assertEquals($expected, $query);
268 }
269
270 /**
271 * @test
272 * @see http://bugs.typo3.org/view.php?id=6198
273 */
274 public function stringsWithinInClauseAreProperlyQuoted() {
275 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
276 'COUNT(DISTINCT tx_dam.uid) AS count',
277 'tx_dam',
278 'tx_dam.pid IN (1) AND tx_dam.file_type IN (\'gif\',\'png\',\'jpg\',\'jpeg\') AND tx_dam.deleted = 0'
279 ));
280 $expected = 'SELECT COUNT(DISTINCT "tx_dam"."uid") AS "count" FROM "tx_dam"';
281 $expected .= ' WHERE "tx_dam"."pid" IN (1) AND "tx_dam"."file_type" IN (\'gif\',\'png\',\'jpg\',\'jpeg\') AND "tx_dam"."deleted" = 0';
282 $this->assertEquals($expected, $query);
283 }
284
285 /**
286 * @test
287 * @see http://bugs.typo3.org/view.php?id=12515
288 * @remark Remapping is not expected here
289 */
290 public function concatAfterLikeOperatorIsProperlyQuoted() {
291 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
292 '*',
293 'sys_refindex, tx_dam_file_tracking',
294 'sys_refindex.tablename = \'tx_dam_file_tracking\''
295 . ' AND sys_refindex.ref_string LIKE CONCAT(tx_dam_file_tracking.file_path, tx_dam_file_tracking.file_name)'
296 ));
297 $expected = 'SELECT * FROM "sys_refindex", "tx_dam_file_tracking" WHERE "sys_refindex"."tablename" = \'tx_dam_file_tracking\'';
298 $expected .= ' AND (dbms_lob.instr("sys_refindex"."ref_string", CONCAT("tx_dam_file_tracking"."file_path","tx_dam_file_tracking"."file_name"),1,1) > 0)';
299 $this->assertEquals($expected, $query);
300 }
301
302 /**
303 * @test
304 * @see http://bugs.typo3.org/view.php?id=12231
305 */
306 public function cachingFrameworkQueryIsProperlyQuoted() {
307 $currentTime = time();
308 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
309 'content',
310 'cache_hash',
311 'identifier = ' . $GLOBALS['TYPO3_DB']->fullQuoteStr('abbbabaf2d4b3f9a63e8dde781f1c106', 'cache_hash') .
312 ' AND (crdate + lifetime >= ' . $currentTime . ' OR lifetime = 0)'
313 ));
314 $expected = 'SELECT "content" FROM "cache_hash" WHERE "identifier" = \'abbbabaf2d4b3f9a63e8dde781f1c106\' AND ("crdate"+"lifetime" >= ' . $currentTime . ' OR "lifetime" = 0)';
315 $this->assertEquals($expected, $query);
316 }
317
318 /**
319 * @test
320 * @see http://bugs.typo3.org/view.php?id=12231
321 */
322 public function calculatedFieldsAreProperlyQuoted() {
323 $currentTime = time();
324 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
325 'identifier',
326 'cachingframework_cache_pages',
327 'crdate + lifetime < ' . $currentTime . ' AND lifetime > 0'
328 ));
329 $expected = 'SELECT "identifier" FROM "cachingframework_cache_pages" WHERE "crdate"+"lifetime" < ' . $currentTime . ' AND "lifetime" > 0';
330 $this->assertEquals($expected, $query);
331 }
332
333 /**
334 * @test
335 * http://bugs.typo3.org/view.php?id=13422
336 */
337 public function numericColumnsAreNotQuoted() {
338 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
339 '1',
340 'be_users',
341 'username = \'_cli_scheduler\' AND admin = 0 AND be_users.deleted = 0'
342 ));
343 $expected = 'SELECT 1 FROM "be_users" WHERE "username" = \'_cli_scheduler\' AND "admin" = 0 AND "be_users"."deleted" = 0';
344 $this->assertEquals($expected, $query);
345 }
346
347 ///////////////////////////////////////
348 // Tests concerning remapping
349 ///////////////////////////////////////
350
351 /**
352 * @test
353 * @see http://bugs.typo3.org/view.php?id=10411
354 * @remark Remapping is expected here
355 */
356 public function tablesAndFieldsAreRemappedInMultipleJoins() {
357 $selectFields = '*';
358 $fromTables = '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';
359 $whereClause = '1=1';
360 $groupBy = '';
361 $orderBy = '';
362
363 $GLOBALS['TYPO3_DB']->_callRef('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
364 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery($selectFields, $fromTables, $whereClause, $groupBy, $orderBy));
365
366 $expected = 'SELECT * FROM "ext_tt_news_cat"';
367 $expected .= ' INNER JOIN "ext_tt_news_cat_mm" ON "ext_tt_news_cat"."cat_uid"="ext_tt_news_cat_mm"."uid_foreign"';
368 $expected .= ' INNER JOIN "ext_tt_news" ON "ext_tt_news"."news_uid"="ext_tt_news_cat_mm"."local_uid"';
369 $expected .= ' WHERE 1 = 1';
370 $this->assertEquals($expected, $query);
371 }
372
373 /**
374 * @test
375 * @see http://bugs.typo3.org/view.php?id=6953
376 */
377 public function fieldWithinSqlFunctionIsRemapped() {
378 $selectFields = 'tstamp, script, SUM(exec_time) AS calc_sum, COUNT(*) AS qrycount, MAX(errorFlag) AS error';
379 $fromTables = 'tx_dbal_debuglog';
380 $whereClause = '1=1';
381 $groupBy = '';
382 $orderBy = '';
383
384 $GLOBALS['TYPO3_DB']->_callRef('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
385 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery($selectFields, $fromTables, $whereClause, $groupBy, $orderBy));
386
387 $expected = 'SELECT "tstamp", "script", SUM("exec_time") AS "calc_sum", COUNT(*) AS "qrycount", MAX("errorflag") AS "error" FROM "tx_dbal_debuglog" WHERE 1 = 1';
388 $this->assertEquals($expected, $query);
389 }
390
391 /**
392 * @test
393 * @see http://bugs.typo3.org/view.php?id=6953
394 */
395 public function tableAndFieldWithinSqlFunctionIsRemapped() {
396 $selectFields = 'MAX(tt_news_cat.uid) AS biggest_id';
397 $fromTables = 'tt_news_cat INNER JOIN tt_news_cat_mm ON tt_news_cat.uid = tt_news_cat_mm.uid_foreign';
398 $whereClause = 'tt_news_cat_mm.uid_local > 50';
399 $groupBy = '';
400 $orderBy = '';
401
402 $GLOBALS['TYPO3_DB']->_callRef('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
403 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery($selectFields, $fromTables, $whereClause, $groupBy, $orderBy));
404
405 $expected = 'SELECT MAX("ext_tt_news_cat"."cat_uid") AS "biggest_id" FROM "ext_tt_news_cat"';
406 $expected .= ' INNER JOIN "ext_tt_news_cat_mm" ON "ext_tt_news_cat"."cat_uid"="ext_tt_news_cat_mm"."uid_foreign"';
407 $expected .= ' WHERE "ext_tt_news_cat_mm"."local_uid" > 50';
408 $this->assertEquals($expected, $query);
409 }
410
411 /**
412 * @test
413 * @see http://bugs.typo3.org/view.php?id=12515
414 * @remark Remapping is expected here
415 */
416 public function concatAfterLikeOperatorIsRemapped() {
417 $selectFields = '*';
418 $fromTables = 'sys_refindex, tx_dam_file_tracking';
419 $whereClause = 'sys_refindex.tablename = \'tx_dam_file_tracking\''
420 . ' AND sys_refindex.ref_string LIKE CONCAT(tx_dam_file_tracking.file_path, tx_dam_file_tracking.file_name)';
421 $groupBy = '';
422 $orderBy = '';
423
424 $GLOBALS['TYPO3_DB']->_callRef('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
425 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery($selectFields, $fromTables, $whereClause, $groupBy, $orderBy));
426
427 $expected = 'SELECT * FROM "sys_refindex", "tx_dam_file_tracking" WHERE "sys_refindex"."tablename" = \'tx_dam_file_tracking\'';
428 $expected .= ' AND (dbms_lob.instr("sys_refindex"."ref_string", CONCAT("tx_dam_file_tracking"."path","tx_dam_file_tracking"."filename"),1,1) > 0)';
429 $this->assertEquals($expected, $query);
430 }
431
432 /**
433 * @test
434 * @see http://bugs.typo3.org/view.php?id=5708
435 */
436 public function fieldIsMappedOnRightSideOfAJoinCondition() {
437 $selectFields = 'cpg_categories.uid, cpg_categories.name';
438 $fromTables = 'cpg_categories, pages';
439 $whereClause = 'pages.uid = cpg_categories.pid AND pages.deleted = 0 AND 1 = 1';
440 $groupBy = '';
441 $orderBy = 'cpg_categories.pos';
442
443 $GLOBALS['TYPO3_DB']->_callRef('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
444 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery($selectFields, $fromTables, $whereClause, $groupBy, $orderBy));
445
446 $expected = 'SELECT "cpg_categories"."uid", "cpg_categories"."name" FROM "cpg_categories", "pages" WHERE "pages"."uid" = "cpg_categories"."page_id"';
447 $expected .= ' AND "pages"."deleted" = 0 AND 1 = 1 ORDER BY "cpg_categories"."pos"';
448 $this->assertEquals($expected, $query);
449 }
450
451 ///////////////////////////////////////
452 // Tests concerning DB management
453 ///////////////////////////////////////
454
455 /**
456 * @test
457 * @see http://bugs.typo3.org/view.php?id=12670
458 */
459 public function notNullableColumnsWithDefaultEmptyStringAreCreatedAsNullable() {
460 $parseString = '
461 CREATE TABLE tx_realurl_uniqalias (
462 uid int(11) NOT NULL auto_increment,
463 tstamp int(11) DEFAULT \'0\' NOT NULL,
464 tablename varchar(60) DEFAULT \'\' NOT NULL,
465 field_alias varchar(255) DEFAULT \'\' NOT NULL,
466 field_id varchar(60) DEFAULT \'\' NOT NULL,
467 value_alias varchar(255) DEFAULT \'\' NOT NULL,
468 value_id int(11) DEFAULT \'0\' NOT NULL,
469 lang int(11) DEFAULT \'0\' NOT NULL,
470 expire int(11) DEFAULT \'0\' NOT NULL,
471
472 PRIMARY KEY (uid),
473 KEY tablename (tablename),
474 KEY bk_realurl01 (field_alias,field_id,value_id,lang,expire),
475 KEY bk_realurl02 (tablename,field_alias,field_id,value_alias(220),expire)
476 );
477 ';
478
479 $components = $GLOBALS['TYPO3_DB']->SQLparser->_callRef('parseCREATETABLE', $parseString);
480 $this->assertTrue(is_array($components), 'Not an array: ' . $components);
481
482 $sqlCommands = $GLOBALS['TYPO3_DB']->SQLparser->_call('compileCREATETABLE', $components);
483 $this->assertTrue(is_array($sqlCommands), 'Not an array: ' . $sqlCommands);
484 $this->assertEquals(4, count($sqlCommands));
485
486 $expected = $this->cleanSql('
487 CREATE TABLE "tx_realurl_uniqalias" (
488 "uid" NUMBER(20) NOT NULL,
489 "tstamp" NUMBER(20) DEFAULT 0,
490 "tablename" VARCHAR(60) DEFAULT \'\',
491 "field_alias" VARCHAR(255) DEFAULT \'\',
492 "field_id" VARCHAR(60) DEFAULT \'\',
493 "value_alias" VARCHAR(255) DEFAULT \'\',
494 "value_id" NUMBER(20) DEFAULT 0,
495 "lang" NUMBER(20) DEFAULT 0,
496 "expire" NUMBER(20) DEFAULT 0,
497 PRIMARY KEY ("uid")
498 )
499 ');
500 $this->assertEquals($expected, $this->cleanSql($sqlCommands[0]));
501 }
502
503 /**
504 * @test
505 * @see http://bugs.typo3.org/view.php?id=11142
506 * @see http://bugs.typo3.org/view.php?id=12670
507 */
508 public function defaultValueIsProperlyQuotedInCreateTable() {
509 $parseString = '
510 CREATE TABLE tx_test (
511 uid int(11) NOT NULL auto_increment,
512 lastname varchar(60) DEFAULT \'unknown\' NOT NULL,
513 firstname varchar(60) DEFAULT \'\' NOT NULL,
514 language varchar(2) NOT NULL,
515 tstamp int(11) DEFAULT \'0\' NOT NULL,
516
517 PRIMARY KEY (uid),
518 KEY name (name)
519 );
520 ';
521
522 $components = $GLOBALS['TYPO3_DB']->SQLparser->_callRef('parseCREATETABLE', $parseString);
523 $this->assertTrue(is_array($components), 'Not an array: ' . $components);
524
525 $sqlCommands = $GLOBALS['TYPO3_DB']->SQLparser->_call('compileCREATETABLE', $components);
526 $this->assertTrue(is_array($sqlCommands), 'Not an array: ' . $sqlCommands);
527 $this->assertEquals(2, count($sqlCommands));
528
529 $expected = $this->cleanSql('
530 CREATE TABLE "tx_test" (
531 "uid" NUMBER(20) NOT NULL,
532 "lastname" VARCHAR(60) DEFAULT \'unknown\',
533 "firstname" VARCHAR(60) DEFAULT \'\',
534 "language" VARCHAR(2) DEFAULT \'\',
535 "tstamp" NUMBER(20) DEFAULT 0,
536 PRIMARY KEY ("uid")
537 )
538 ');
539 $this->assertEquals($expected, $this->cleanSql($sqlCommands[0]));
540 }
541
542 ///////////////////////////////////////
543 // Tests concerning subqueries
544 ///////////////////////////////////////
545
546 /**
547 * @test
548 * @see http://bugs.typo3.org/view.php?id=12758
549 */
550 public function inWhereClauseWithSubqueryIsProperlyQuoted() {
551 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
552 '*',
553 'tx_crawler_queue',
554 'process_id IN (SELECT process_id FROM tx_crawler_process WHERE active=0 AND deleted=0)'
555 ));
556 $expected = 'SELECT * FROM "tx_crawler_queue" WHERE "process_id" IN (SELECT "process_id" FROM "tx_crawler_process" WHERE "active" = 0 AND "deleted" = 0)';
557 $this->assertEquals($expected, $query);
558 }
559
560 /**
561 * @test
562 * @see http://bugs.typo3.org/view.php?id=12758
563 */
564 public function subqueryIsRemappedForInWhereClause() {
565 $selectFields = '*';
566 $fromTables = 'tx_crawler_queue';
567 $whereClause = 'process_id IN (SELECT process_id FROM tx_crawler_process WHERE active=0 AND deleted=0)';
568 $groupBy = '';
569 $orderBy = '';
570
571 $GLOBALS['TYPO3_DB']->_callRef('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
572 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery($selectFields, $fromTables, $whereClause, $groupBy, $orderBy));
573
574 $expected = 'SELECT * FROM "tx_crawler_queue" WHERE "process_id" IN (SELECT "ps_id" FROM "tx_crawler_ps" WHERE "is_active" = 0 AND "deleted" = 0)';
575 $this->assertEquals($expected, $query);
576 }
577
578 /**
579 * @test
580 * @see http://bugs.typo3.org/view.php?id=12800
581 */
582 public function cachingFrameworkQueryIsSupported() {
583 $currentTime = time();
584 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->DELETEquery(
585 'cachingframework_cache_hash_tags',
586 'identifier IN (' .
587 $GLOBALS['TYPO3_DB']->SELECTsubquery(
588 'identifier',
589 'cachingframework_cache_pages',
590 'crdate + lifetime < ' . $currentTime . ' AND lifetime > 0'
591 ) .
592 ')'
593 ));
594 $expected = 'DELETE FROM "cachingframework_cache_hash_tags" WHERE "identifier" IN (';
595 $expected .= 'SELECT "identifier" FROM "cachingframework_cache_pages" WHERE "crdate"+"lifetime" < ' . $currentTime . ' AND "lifetime" > 0';
596 $expected .= ')';
597 $this->assertEquals($expected, $query);
598 }
599
600 /**
601 * @test
602 * @see http://bugs.typo3.org/view.php?id=12800
603 */
604 public function cachingFrameworkQueryIsRemapped() {
605 $currentTime = time();
606 $table = 'cachingframework_cache_hash_tags';
607 $where = 'identifier IN (' .
608 $GLOBALS['TYPO3_DB']->SELECTsubquery(
609 'identifier',
610 'cachingframework_cache_pages',
611 'crdate + lifetime < ' . $currentTime . ' AND lifetime > 0'
612 ) .
613 ')';
614
615 // Perform remapping (as in method exec_DELETEquery)
616 if ($tableArray = $GLOBALS['TYPO3_DB']->_call('map_needMapping', $table)) {
617 // Where clause:
618 $whereParts = $GLOBALS['TYPO3_DB']->SQLparser->parseWhereClause($where);
619 $GLOBALS['TYPO3_DB']->_callRef('map_sqlParts', $whereParts, $tableArray[0]['table']);
620 $where = $GLOBALS['TYPO3_DB']->SQLparser->compileWhereClause($whereParts, FALSE);
621
622 // Table name:
623 if ($GLOBALS['TYPO3_DB']->mapping[$table]['mapTableName']) {
624 $table = $GLOBALS['TYPO3_DB']->mapping[$table]['mapTableName'];
625 }
626 }
627
628 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->DELETEquery($table, $where));
629 $expected = 'DELETE FROM "cf_cache_hash_tags" WHERE "identifier" IN (';
630 $expected .= 'SELECT "identifier" FROM "cf_cache_pages" WHERE "crdate"+"lifetime" < ' . $currentTime . ' AND "lifetime" > 0';
631 $expected .= ')';
632 $this->assertEquals($expected, $query);
633 }
634
635 /**
636 * @test
637 * @see http://bugs.typo3.org/view.php?id=12758
638 */
639 public function existsWhereClauseIsProperlyQuoted() {
640 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
641 '*',
642 'tx_crawler_process',
643 'active = 0 AND NOT EXISTS (' .
644 $GLOBALS['TYPO3_DB']->SELECTsubquery(
645 '*',
646 'tx_crawler_queue',
647 'tx_crawler_queue.process_id = tx_crawler_process.process_id AND tx_crawler_queue.exec_time = 0)'
648 ) .
649 ')'
650 ));
651 $expected = 'SELECT * FROM "tx_crawler_process" WHERE "active" = 0 AND NOT EXISTS (';
652 $expected .= 'SELECT * FROM "tx_crawler_queue" WHERE "tx_crawler_queue"."process_id" = "tx_crawler_process"."process_id" AND "tx_crawler_queue"."exec_time" = 0';
653 $expected .= ')';
654 $this->assertEquals($expected, $query);
655 }
656
657 /**
658 * @test
659 * @see http://bugs.typo3.org/view.php?id=12758
660 */
661 public function subqueryIsRemappedForExistsWhereClause() {
662 $selectFields = '*';
663 $fromTables = 'tx_crawler_process';
664 $whereClause = 'active = 0 AND NOT EXISTS (' .
665 $GLOBALS['TYPO3_DB']->SELECTsubquery(
666 '*',
667 'tx_crawler_queue',
668 'tx_crawler_queue.process_id = tx_crawler_process.process_id AND tx_crawler_queue.exec_time = 0'
669 ) .
670 ')';
671 $groupBy = '';
672 $orderBy = '';
673
674 $GLOBALS['TYPO3_DB']->_callRef('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
675 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery($selectFields, $fromTables, $whereClause, $groupBy, $orderBy));
676
677 $expected = 'SELECT * FROM "tx_crawler_ps" WHERE "is_active" = 0 AND NOT EXISTS (';
678 $expected .= 'SELECT * FROM "tx_crawler_queue" WHERE "tx_crawler_queue"."process_id" = "tx_crawler_ps"."ps_id" AND "tx_crawler_queue"."exec_time" = 0';
679 $expected .= ')';
680 $this->assertEquals($expected, $query);
681 }
682
683 ///////////////////////////////////////
684 // Tests concerning advanced operators
685 ///////////////////////////////////////
686
687 /**
688 * @test
689 * @see http://bugs.typo3.org/view.php?id=13135
690 */
691 public function caseStatementIsProperlyQuoted() {
692 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
693 'process_id, CASE active' .
694 ' WHEN 1 THEN ' . $GLOBALS['TYPO3_DB']->fullQuoteStr('one', 'tx_crawler_process') .
695 ' WHEN 2 THEN ' . $GLOBALS['TYPO3_DB']->fullQuoteStr('two', 'tx_crawler_process') .
696 ' ELSE ' . $GLOBALS['TYPO3_DB']->fullQuoteStr('out of range', 'tx_crawler_process') .
697 ' END AS number',
698 'tx_crawler_process',
699 '1=1'
700 ));
701 $expected = 'SELECT "process_id", CASE "active" WHEN 1 THEN \'one\' WHEN 2 THEN \'two\' ELSE \'out of range\' END AS "number" FROM "tx_crawler_process" WHERE 1 = 1';
702 $this->assertEquals($expected, $query);
703 }
704
705 /**
706 * @test
707 * @see http://bugs.typo3.org/view.php?id=13135
708 */
709 public function caseStatementIsProperlyRemapped() {
710 $selectFields = 'process_id, CASE active' .
711 ' WHEN 1 THEN ' . $GLOBALS['TYPO3_DB']->fullQuoteStr('one', 'tx_crawler_process') .
712 ' WHEN 2 THEN ' . $GLOBALS['TYPO3_DB']->fullQuoteStr('two', 'tx_crawler_process') .
713 ' ELSE ' . $GLOBALS['TYPO3_DB']->fullQuoteStr('out of range', 'tx_crawler_process') .
714 ' END AS number';
715 $fromTables = 'tx_crawler_process';
716 $whereClause = '1=1';
717 $groupBy = '';
718 $orderBy = '';
719
720 $GLOBALS['TYPO3_DB']->_callRef('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
721 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery($selectFields, $fromTables, $whereClause, $groupBy, $orderBy));
722
723 $expected = 'SELECT "ps_id", CASE "is_active" WHEN 1 THEN \'one\' WHEN 2 THEN \'two\' ELSE \'out of range\' END AS "number" ';
724 $expected .= 'FROM "tx_crawler_ps" WHERE 1 = 1';
725 $this->assertEquals($expected, $query);
726 }
727
728 /**
729 * @test
730 * @see http://bugs.typo3.org/view.php?id=13135
731 */
732 public function caseStatementWithExternalTableIsProperlyRemapped() {
733 $selectFields = 'process_id, CASE tt_news.uid' .
734 ' WHEN 1 THEN ' . $GLOBALS['TYPO3_DB']->fullQuoteStr('one', 'tt_news') .
735 ' WHEN 2 THEN ' . $GLOBALS['TYPO3_DB']->fullQuoteStr('two', 'tt_news') .
736 ' ELSE ' . $GLOBALS['TYPO3_DB']->fullQuoteStr('out of range', 'tt_news') .
737 ' END AS number';
738 $fromTables = 'tx_crawler_process, tt_news';
739 $whereClause = '1=1';
740 $groupBy = '';
741 $orderBy = '';
742
743 $GLOBALS['TYPO3_DB']->_callRef('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
744 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery($selectFields, $fromTables, $whereClause, $groupBy, $orderBy));
745
746 $expected = 'SELECT "ps_id", CASE "ext_tt_news"."news_uid" WHEN 1 THEN \'one\' WHEN 2 THEN \'two\' ELSE \'out of range\' END AS "number" ';
747 $expected .= 'FROM "tx_crawler_ps", "ext_tt_news" WHERE 1 = 1';
748 $this->assertEquals($expected, $query);
749 }
750
751 /**
752 * @test
753 * @see http://bugs.typo3.org/view.php?id=13134
754 */
755 public function locateStatementIsProperlyQuoted() {
756 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
757 '*, CASE WHEN' .
758 ' LOCATE(' . $GLOBALS['TYPO3_DB']->fullQuoteStr('(fce)', 'tx_templavoila_tmplobj') . ', datastructure)>0 THEN 2' .
759 ' ELSE 1' .
760 ' END AS scope',
761 'tx_templavoila_tmplobj',
762 '1=1'
763 ));
764 $expected = 'SELECT *, CASE WHEN INSTR("datastructure", \'(fce)\') > 0 THEN 2 ELSE 1 END AS "scope" FROM "tx_templavoila_tmplobj" WHERE 1 = 1';
765 $this->assertEquals($expected, $query);
766 }
767
768 /**
769 * @test
770 * @see http://bugs.typo3.org/view.php?id=13134
771 */
772 public function locateStatementWithPositionIsProperlyQuoted() {
773 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
774 '*, CASE WHEN' .
775 ' LOCATE(' . $GLOBALS['TYPO3_DB']->fullQuoteStr('(fce)', 'tx_templavoila_tmplobj') . ', datastructure, 4)>0 THEN 2' .
776 ' ELSE 1' .
777 ' END AS scope',
778 'tx_templavoila_tmplobj',
779 '1=1'
780 ));
781 $expected = 'SELECT *, CASE WHEN INSTR("datastructure", \'(fce)\', 4) > 0 THEN 2 ELSE 1 END AS "scope" FROM "tx_templavoila_tmplobj" WHERE 1 = 1';
782 $this->assertEquals($expected, $query);
783 }
784
785 /**
786 * @test
787 * @see http://bugs.typo3.org/view.php?id=6196
788 */
789 public function IfNullIsProperlyRemapped() {
790 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
791 '*',
792 'tt_news_cat_mm',
793 'IFNULL(tt_news_cat_mm.uid_foreign,0) IN (21,22)'
794 ));
795 $expected = 'SELECT * FROM "tt_news_cat_mm" WHERE NVL("tt_news_cat_mm"."uid_foreign", 0) IN (21,22)';
796 $this->assertEquals($expected, $query);
797 }
798 }
799 ?>