Follow-up of changeset 29769: Using a real extended insert when dealing with 'native...
[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 * @see http://bugs.typo3.org/view.php?id=2438
231 */
232 public function distinctFieldIsProperlyQuoted() {
233 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
234 'COUNT(DISTINCT pid)', // select fields
235 'tt_content', // from table
236 '1=1' // where clause
237 ));
238 $expected = 'SELECT COUNT(DISTINCT "pid") FROM "tt_content" WHERE 1 = 1';
239 $this->assertEquals($expected, $query);
240 }
241
242 /**
243 * @test
244 * @see http://bugs.typo3.org/view.php?id=10411
245 * @remark Remapping is not expected here
246 */
247 public function multipleInnerJoinsAreProperlyQuoted() {
248 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
249 '*',
250 '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',
251 '1=1'
252 ));
253 $expected = 'SELECT * FROM "tt_news_cat"';
254 $expected .= ' INNER JOIN "tt_news_cat_mm" ON "tt_news_cat"."uid"="tt_news_cat_mm"."uid_foreign"';
255 $expected .= ' INNER JOIN "tt_news" ON "tt_news"."uid"="tt_news_cat_mm"."uid_local"';
256 $expected .= ' WHERE 1 = 1';
257 $this->assertEquals($expected, $query);
258 }
259
260 /**
261 * @test
262 * @see http://bugs.typo3.org/view.php?id=6198
263 */
264 public function stringsWithinInClauseAreProperlyQuoted() {
265 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
266 'COUNT(DISTINCT tx_dam.uid) AS count',
267 'tx_dam',
268 'tx_dam.pid IN (1) AND tx_dam.file_type IN (\'gif\',\'png\',\'jpg\',\'jpeg\') AND tx_dam.deleted = 0'
269 ));
270 $expected = 'SELECT COUNT(DISTINCT "tx_dam"."uid") AS "count" FROM "tx_dam"';
271 $expected .= ' WHERE "tx_dam"."pid" IN (1) AND "tx_dam"."file_type" IN (\'gif\',\'png\',\'jpg\',\'jpeg\') AND "tx_dam"."deleted" = 0';
272 $this->assertEquals($expected, $query);
273 }
274
275 /**
276 * @test
277 * @see http://bugs.typo3.org/view.php?id=12515
278 * @remark Remapping is not expected here
279 */
280 public function concatAfterLikeOperatorIsProperlyQuoted() {
281 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
282 '*',
283 'sys_refindex, tx_dam_file_tracking',
284 'sys_refindex.tablename = \'tx_dam_file_tracking\''
285 . ' AND sys_refindex.ref_string LIKE CONCAT(tx_dam_file_tracking.file_path, tx_dam_file_tracking.file_name)'
286 ));
287 $expected = 'SELECT * FROM "sys_refindex", "tx_dam_file_tracking" WHERE "sys_refindex"."tablename" = \'tx_dam_file_tracking\'';
288 $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)';
289 $this->assertEquals($expected, $query);
290 }
291
292 /**
293 * @test
294 * @see http://bugs.typo3.org/view.php?id=12231
295 */
296 public function cachingFrameworkQueryIsProperlyQuoted() {
297 $currentTime = time();
298 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
299 'content',
300 'cache_hash',
301 'identifier = ' . $GLOBALS['TYPO3_DB']->fullQuoteStr('abbbabaf2d4b3f9a63e8dde781f1c106', 'cache_hash') .
302 ' AND (crdate + lifetime >= ' . $currentTime . ' OR lifetime = 0)'
303 ));
304 $expected = 'SELECT "content" FROM "cache_hash" WHERE "identifier" = \'abbbabaf2d4b3f9a63e8dde781f1c106\' AND ("crdate"+"lifetime" >= ' . $currentTime . ' OR "lifetime" = 0)';
305 $this->assertEquals($expected, $query);
306 }
307
308 /**
309 * @test
310 * @see http://bugs.typo3.org/view.php?id=12231
311 */
312 public function calculatedFieldsAreProperlyQuoted() {
313 $currentTime = time();
314 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
315 'identifier',
316 'cachingframework_cache_pages',
317 'crdate + lifetime < ' . $currentTime . ' AND lifetime > 0'
318 ));
319 $expected = 'SELECT "identifier" FROM "cachingframework_cache_pages" WHERE "crdate"+"lifetime" < ' . $currentTime . ' AND "lifetime" > 0';
320 $this->assertEquals($expected, $query);
321 }
322
323 /**
324 * @test
325 * http://bugs.typo3.org/view.php?id=13422
326 */
327 public function numericColumnsAreNotQuoted() {
328 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
329 '1',
330 'be_users',
331 'username = \'_cli_scheduler\' AND admin = 0 AND be_users.deleted = 0'
332 ));
333 $expected = 'SELECT 1 FROM "be_users" WHERE "username" = \'_cli_scheduler\' AND "admin" = 0 AND "be_users"."deleted" = 0';
334 $this->assertEquals($expected, $query);
335 }
336
337 ///////////////////////////////////////
338 // Tests concerning remapping
339 ///////////////////////////////////////
340
341 /**
342 * @test
343 * @see http://bugs.typo3.org/view.php?id=10411
344 * @remark Remapping is expected here
345 */
346 public function tablesAndFieldsAreRemappedInMultipleJoins() {
347 $selectFields = '*';
348 $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';
349 $whereClause = '1=1';
350 $groupBy = '';
351 $orderBy = '';
352
353 $GLOBALS['TYPO3_DB']->_callRef('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
354 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery($selectFields, $fromTables, $whereClause, $groupBy, $orderBy));
355
356 $expected = 'SELECT * FROM "ext_tt_news_cat"';
357 $expected .= ' INNER JOIN "ext_tt_news_cat_mm" ON "ext_tt_news_cat"."cat_uid"="ext_tt_news_cat_mm"."uid_foreign"';
358 $expected .= ' INNER JOIN "ext_tt_news" ON "ext_tt_news"."news_uid"="ext_tt_news_cat_mm"."local_uid"';
359 $expected .= ' WHERE 1 = 1';
360 $this->assertEquals($expected, $query);
361 }
362
363 /**
364 * @test
365 * @see http://bugs.typo3.org/view.php?id=6953
366 */
367 public function fieldWithinSqlFunctionIsRemapped() {
368 $selectFields = 'tstamp, script, SUM(exec_time) AS calc_sum, COUNT(*) AS qrycount, MAX(errorFlag) AS error';
369 $fromTables = 'tx_dbal_debuglog';
370 $whereClause = '1=1';
371 $groupBy = '';
372 $orderBy = '';
373
374 $GLOBALS['TYPO3_DB']->_callRef('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
375 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery($selectFields, $fromTables, $whereClause, $groupBy, $orderBy));
376
377 $expected = 'SELECT "tstamp", "script", SUM("exec_time") AS "calc_sum", COUNT(*) AS "qrycount", MAX("errorflag") AS "error" FROM "tx_dbal_debuglog" WHERE 1 = 1';
378 $this->assertEquals($expected, $query);
379 }
380
381 /**
382 * @test
383 * @see http://bugs.typo3.org/view.php?id=6953
384 */
385 public function tableAndFieldWithinSqlFunctionIsRemapped() {
386 $selectFields = 'MAX(tt_news_cat.uid) AS biggest_id';
387 $fromTables = 'tt_news_cat INNER JOIN tt_news_cat_mm ON tt_news_cat.uid = tt_news_cat_mm.uid_foreign';
388 $whereClause = 'tt_news_cat_mm.uid_local > 50';
389 $groupBy = '';
390 $orderBy = '';
391
392 $GLOBALS['TYPO3_DB']->_callRef('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
393 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery($selectFields, $fromTables, $whereClause, $groupBy, $orderBy));
394
395 $expected = 'SELECT MAX("ext_tt_news_cat"."cat_uid") AS "biggest_id" FROM "ext_tt_news_cat"';
396 $expected .= ' INNER JOIN "ext_tt_news_cat_mm" ON "ext_tt_news_cat"."cat_uid"="ext_tt_news_cat_mm"."uid_foreign"';
397 $expected .= ' WHERE "ext_tt_news_cat_mm"."local_uid" > 50';
398 $this->assertEquals($expected, $query);
399 }
400
401 /**
402 * @test
403 * @see http://bugs.typo3.org/view.php?id=12515
404 * @remark Remapping is expected here
405 */
406 public function concatAfterLikeOperatorIsRemapped() {
407 $selectFields = '*';
408 $fromTables = 'sys_refindex, tx_dam_file_tracking';
409 $whereClause = 'sys_refindex.tablename = \'tx_dam_file_tracking\''
410 . ' AND sys_refindex.ref_string LIKE CONCAT(tx_dam_file_tracking.file_path, tx_dam_file_tracking.file_name)';
411 $groupBy = '';
412 $orderBy = '';
413
414 $GLOBALS['TYPO3_DB']->_callRef('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
415 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery($selectFields, $fromTables, $whereClause, $groupBy, $orderBy));
416
417 $expected = 'SELECT * FROM "sys_refindex", "tx_dam_file_tracking" WHERE "sys_refindex"."tablename" = \'tx_dam_file_tracking\'';
418 $expected .= ' AND (dbms_lob.instr("sys_refindex"."ref_string", CONCAT("tx_dam_file_tracking"."path","tx_dam_file_tracking"."filename"),1,1) > 0)';
419 $this->assertEquals($expected, $query);
420 }
421
422 /**
423 * @test
424 * @see http://bugs.typo3.org/view.php?id=5708
425 */
426 public function fieldIsMappedOnRightSideOfAJoinCondition() {
427 $selectFields = 'cpg_categories.uid, cpg_categories.name';
428 $fromTables = 'cpg_categories, pages';
429 $whereClause = 'pages.uid = cpg_categories.pid AND pages.deleted = 0 AND 1 = 1';
430 $groupBy = '';
431 $orderBy = 'cpg_categories.pos';
432
433 $GLOBALS['TYPO3_DB']->_callRef('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
434 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery($selectFields, $fromTables, $whereClause, $groupBy, $orderBy));
435
436 $expected = 'SELECT "cpg_categories"."uid", "cpg_categories"."name" FROM "cpg_categories", "pages" WHERE "pages"."uid" = "cpg_categories"."page_id"';
437 $expected .= ' AND "pages"."deleted" = 0 AND 1 = 1 ORDER BY "cpg_categories"."pos"';
438 $this->assertEquals($expected, $query);
439 }
440
441 ///////////////////////////////////////
442 // Tests concerning DB management
443 ///////////////////////////////////////
444
445 /**
446 * @test
447 * @see http://bugs.typo3.org/view.php?id=12670
448 */
449 public function notNullableColumnsWithDefaultEmptyStringAreCreatedAsNullable() {
450 $parseString = '
451 CREATE TABLE tx_realurl_uniqalias (
452 uid int(11) NOT NULL auto_increment,
453 tstamp int(11) DEFAULT \'0\' NOT NULL,
454 tablename varchar(60) DEFAULT \'\' NOT NULL,
455 field_alias varchar(255) DEFAULT \'\' NOT NULL,
456 field_id varchar(60) DEFAULT \'\' NOT NULL,
457 value_alias varchar(255) DEFAULT \'\' NOT NULL,
458 value_id int(11) DEFAULT \'0\' NOT NULL,
459 lang int(11) DEFAULT \'0\' NOT NULL,
460 expire int(11) DEFAULT \'0\' NOT NULL,
461
462 PRIMARY KEY (uid),
463 KEY tablename (tablename),
464 KEY bk_realurl01 (field_alias,field_id,value_id,lang,expire),
465 KEY bk_realurl02 (tablename,field_alias,field_id,value_alias(220),expire)
466 );
467 ';
468
469 $components = $GLOBALS['TYPO3_DB']->SQLparser->_callRef('parseCREATETABLE', $parseString);
470 $this->assertTrue(is_array($components), 'Not an array: ' . $components);
471
472 $sqlCommands = $GLOBALS['TYPO3_DB']->SQLparser->_call('compileCREATETABLE', $components);
473 $this->assertTrue(is_array($sqlCommands), 'Not an array: ' . $sqlCommands);
474 $this->assertEquals(4, count($sqlCommands));
475
476 $expected = $this->cleanSql('
477 CREATE TABLE "tx_realurl_uniqalias" (
478 "uid" NUMBER(20) NOT NULL,
479 "tstamp" NUMBER(20) DEFAULT 0,
480 "tablename" VARCHAR(60) DEFAULT \'\',
481 "field_alias" VARCHAR(255) DEFAULT \'\',
482 "field_id" VARCHAR(60) DEFAULT \'\',
483 "value_alias" VARCHAR(255) DEFAULT \'\',
484 "value_id" NUMBER(20) DEFAULT 0,
485 "lang" NUMBER(20) DEFAULT 0,
486 "expire" NUMBER(20) DEFAULT 0,
487 PRIMARY KEY ("uid")
488 )
489 ');
490 $this->assertEquals($expected, $this->cleanSql($sqlCommands[0]));
491 }
492
493 /**
494 * @test
495 * @see http://bugs.typo3.org/view.php?id=11142
496 * @see http://bugs.typo3.org/view.php?id=12670
497 */
498 public function defaultValueIsProperlyQuotedInCreateTable() {
499 $parseString = '
500 CREATE TABLE tx_test (
501 uid int(11) NOT NULL auto_increment,
502 lastname varchar(60) DEFAULT \'unknown\' NOT NULL,
503 firstname varchar(60) DEFAULT \'\' NOT NULL,
504 language varchar(2) NOT NULL,
505 tstamp int(11) DEFAULT \'0\' NOT NULL,
506
507 PRIMARY KEY (uid),
508 KEY name (name)
509 );
510 ';
511
512 $components = $GLOBALS['TYPO3_DB']->SQLparser->_callRef('parseCREATETABLE', $parseString);
513 $this->assertTrue(is_array($components), 'Not an array: ' . $components);
514
515 $sqlCommands = $GLOBALS['TYPO3_DB']->SQLparser->_call('compileCREATETABLE', $components);
516 $this->assertTrue(is_array($sqlCommands), 'Not an array: ' . $sqlCommands);
517 $this->assertEquals(2, count($sqlCommands));
518
519 $expected = $this->cleanSql('
520 CREATE TABLE "tx_test" (
521 "uid" NUMBER(20) NOT NULL,
522 "lastname" VARCHAR(60) DEFAULT \'unknown\',
523 "firstname" VARCHAR(60) DEFAULT \'\',
524 "language" VARCHAR(2) DEFAULT \'\',
525 "tstamp" NUMBER(20) DEFAULT 0,
526 PRIMARY KEY ("uid")
527 )
528 ');
529 $this->assertEquals($expected, $this->cleanSql($sqlCommands[0]));
530 }
531
532 ///////////////////////////////////////
533 // Tests concerning subqueries
534 ///////////////////////////////////////
535
536 /**
537 * @test
538 * @see http://bugs.typo3.org/view.php?id=12758
539 */
540 public function inWhereClauseWithSubqueryIsProperlyQuoted() {
541 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
542 '*',
543 'tx_crawler_queue',
544 'process_id IN (SELECT process_id FROM tx_crawler_process WHERE active=0 AND deleted=0)'
545 ));
546 $expected = 'SELECT * FROM "tx_crawler_queue" WHERE "process_id" IN (SELECT "process_id" FROM "tx_crawler_process" WHERE "active" = 0 AND "deleted" = 0)';
547 $this->assertEquals($expected, $query);
548 }
549
550 /**
551 * @test
552 * @see http://bugs.typo3.org/view.php?id=12758
553 */
554 public function subqueryIsRemappedForInWhereClause() {
555 $selectFields = '*';
556 $fromTables = 'tx_crawler_queue';
557 $whereClause = 'process_id IN (SELECT process_id FROM tx_crawler_process WHERE active=0 AND deleted=0)';
558 $groupBy = '';
559 $orderBy = '';
560
561 $GLOBALS['TYPO3_DB']->_callRef('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
562 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery($selectFields, $fromTables, $whereClause, $groupBy, $orderBy));
563
564 $expected = 'SELECT * FROM "tx_crawler_queue" WHERE "process_id" IN (SELECT "ps_id" FROM "tx_crawler_ps" WHERE "is_active" = 0 AND "deleted" = 0)';
565 $this->assertEquals($expected, $query);
566 }
567
568 /**
569 * @test
570 * @see http://bugs.typo3.org/view.php?id=12800
571 */
572 public function cachingFrameworkQueryIsSupported() {
573 $currentTime = time();
574 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->DELETEquery(
575 'cachingframework_cache_hash_tags',
576 'identifier IN (' .
577 $GLOBALS['TYPO3_DB']->SELECTsubquery(
578 'identifier',
579 'cachingframework_cache_pages',
580 'crdate + lifetime < ' . $currentTime . ' AND lifetime > 0'
581 ) .
582 ')'
583 ));
584 $expected = 'DELETE FROM "cachingframework_cache_hash_tags" WHERE "identifier" IN (';
585 $expected .= 'SELECT "identifier" FROM "cachingframework_cache_pages" WHERE "crdate"+"lifetime" < ' . $currentTime . ' AND "lifetime" > 0';
586 $expected .= ')';
587 $this->assertEquals($expected, $query);
588 }
589
590 /**
591 * @test
592 * @see http://bugs.typo3.org/view.php?id=12800
593 */
594 public function cachingFrameworkQueryIsRemapped() {
595 $currentTime = time();
596 $table = 'cachingframework_cache_hash_tags';
597 $where = 'identifier IN (' .
598 $GLOBALS['TYPO3_DB']->SELECTsubquery(
599 'identifier',
600 'cachingframework_cache_pages',
601 'crdate + lifetime < ' . $currentTime . ' AND lifetime > 0'
602 ) .
603 ')';
604
605 // Perform remapping (as in method exec_DELETEquery)
606 if ($tableArray = $GLOBALS['TYPO3_DB']->_call('map_needMapping', $table)) {
607 // Where clause:
608 $whereParts = $GLOBALS['TYPO3_DB']->SQLparser->parseWhereClause($where);
609 $GLOBALS['TYPO3_DB']->_callRef('map_sqlParts', $whereParts, $tableArray[0]['table']);
610 $where = $GLOBALS['TYPO3_DB']->SQLparser->compileWhereClause($whereParts, FALSE);
611
612 // Table name:
613 if ($GLOBALS['TYPO3_DB']->mapping[$table]['mapTableName']) {
614 $table = $GLOBALS['TYPO3_DB']->mapping[$table]['mapTableName'];
615 }
616 }
617
618 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->DELETEquery($table, $where));
619 $expected = 'DELETE FROM "cf_cache_hash_tags" WHERE "identifier" IN (';
620 $expected .= 'SELECT "identifier" FROM "cf_cache_pages" WHERE "crdate"+"lifetime" < ' . $currentTime . ' AND "lifetime" > 0';
621 $expected .= ')';
622 $this->assertEquals($expected, $query);
623 }
624
625 /**
626 * @test
627 * @see http://bugs.typo3.org/view.php?id=12758
628 */
629 public function existsWhereClauseIsProperlyQuoted() {
630 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
631 '*',
632 'tx_crawler_process',
633 'active = 0 AND NOT EXISTS (' .
634 $GLOBALS['TYPO3_DB']->SELECTsubquery(
635 '*',
636 'tx_crawler_queue',
637 'tx_crawler_queue.process_id = tx_crawler_process.process_id AND tx_crawler_queue.exec_time = 0)'
638 ) .
639 ')'
640 ));
641 $expected = 'SELECT * FROM "tx_crawler_process" WHERE "active" = 0 AND NOT EXISTS (';
642 $expected .= 'SELECT * FROM "tx_crawler_queue" WHERE "tx_crawler_queue"."process_id" = "tx_crawler_process"."process_id" AND "tx_crawler_queue"."exec_time" = 0';
643 $expected .= ')';
644 $this->assertEquals($expected, $query);
645 }
646
647 /**
648 * @test
649 * @see http://bugs.typo3.org/view.php?id=12758
650 */
651 public function subqueryIsRemappedForExistsWhereClause() {
652 $selectFields = '*';
653 $fromTables = 'tx_crawler_process';
654 $whereClause = 'active = 0 AND NOT EXISTS (' .
655 $GLOBALS['TYPO3_DB']->SELECTsubquery(
656 '*',
657 'tx_crawler_queue',
658 'tx_crawler_queue.process_id = tx_crawler_process.process_id AND tx_crawler_queue.exec_time = 0'
659 ) .
660 ')';
661 $groupBy = '';
662 $orderBy = '';
663
664 $GLOBALS['TYPO3_DB']->_callRef('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
665 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery($selectFields, $fromTables, $whereClause, $groupBy, $orderBy));
666
667 $expected = 'SELECT * FROM "tx_crawler_ps" WHERE "is_active" = 0 AND NOT EXISTS (';
668 $expected .= 'SELECT * FROM "tx_crawler_queue" WHERE "tx_crawler_queue"."process_id" = "tx_crawler_ps"."ps_id" AND "tx_crawler_queue"."exec_time" = 0';
669 $expected .= ')';
670 $this->assertEquals($expected, $query);
671 }
672
673 ///////////////////////////////////////
674 // Tests concerning advanced operators
675 ///////////////////////////////////////
676
677 /**
678 * @test
679 * @see http://bugs.typo3.org/view.php?id=13135
680 */
681 public function caseStatementIsProperlyQuoted() {
682 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
683 'process_id, CASE active' .
684 ' WHEN 1 THEN ' . $GLOBALS['TYPO3_DB']->fullQuoteStr('one', 'tx_crawler_process') .
685 ' WHEN 2 THEN ' . $GLOBALS['TYPO3_DB']->fullQuoteStr('two', 'tx_crawler_process') .
686 ' ELSE ' . $GLOBALS['TYPO3_DB']->fullQuoteStr('out of range', 'tx_crawler_process') .
687 ' END AS number',
688 'tx_crawler_process',
689 '1=1'
690 ));
691 $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';
692 $this->assertEquals($expected, $query);
693 }
694
695 /**
696 * @test
697 * @see http://bugs.typo3.org/view.php?id=13135
698 */
699 public function caseStatementIsProperlyRemapped() {
700 $selectFields = 'process_id, CASE active' .
701 ' WHEN 1 THEN ' . $GLOBALS['TYPO3_DB']->fullQuoteStr('one', 'tx_crawler_process') .
702 ' WHEN 2 THEN ' . $GLOBALS['TYPO3_DB']->fullQuoteStr('two', 'tx_crawler_process') .
703 ' ELSE ' . $GLOBALS['TYPO3_DB']->fullQuoteStr('out of range', 'tx_crawler_process') .
704 ' END AS number';
705 $fromTables = 'tx_crawler_process';
706 $whereClause = '1=1';
707 $groupBy = '';
708 $orderBy = '';
709
710 $GLOBALS['TYPO3_DB']->_callRef('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
711 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery($selectFields, $fromTables, $whereClause, $groupBy, $orderBy));
712
713 $expected = 'SELECT "ps_id", CASE "is_active" WHEN 1 THEN \'one\' WHEN 2 THEN \'two\' ELSE \'out of range\' END AS "number" ';
714 $expected .= 'FROM "tx_crawler_ps" WHERE 1 = 1';
715 $this->assertEquals($expected, $query);
716 }
717
718 /**
719 * @test
720 * @see http://bugs.typo3.org/view.php?id=13135
721 */
722 public function caseStatementWithExternalTableIsProperlyRemapped() {
723 $selectFields = 'process_id, CASE tt_news.uid' .
724 ' WHEN 1 THEN ' . $GLOBALS['TYPO3_DB']->fullQuoteStr('one', 'tt_news') .
725 ' WHEN 2 THEN ' . $GLOBALS['TYPO3_DB']->fullQuoteStr('two', 'tt_news') .
726 ' ELSE ' . $GLOBALS['TYPO3_DB']->fullQuoteStr('out of range', 'tt_news') .
727 ' END AS number';
728 $fromTables = 'tx_crawler_process, tt_news';
729 $whereClause = '1=1';
730 $groupBy = '';
731 $orderBy = '';
732
733 $GLOBALS['TYPO3_DB']->_callRef('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
734 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery($selectFields, $fromTables, $whereClause, $groupBy, $orderBy));
735
736 $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" ';
737 $expected .= 'FROM "tx_crawler_ps", "ext_tt_news" WHERE 1 = 1';
738 $this->assertEquals($expected, $query);
739 }
740
741 /**
742 * @test
743 * @see http://bugs.typo3.org/view.php?id=13134
744 */
745 public function locateStatementIsProperlyQuoted() {
746 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
747 '*, CASE WHEN' .
748 ' LOCATE(' . $GLOBALS['TYPO3_DB']->fullQuoteStr('(fce)', 'tx_templavoila_tmplobj') . ', datastructure)>0 THEN 2' .
749 ' ELSE 1' .
750 ' END AS scope',
751 'tx_templavoila_tmplobj',
752 '1=1'
753 ));
754 $expected = 'SELECT *, CASE WHEN INSTR("datastructure", \'(fce)\') > 0 THEN 2 ELSE 1 END AS "scope" FROM "tx_templavoila_tmplobj" WHERE 1 = 1';
755 $this->assertEquals($expected, $query);
756 }
757
758 /**
759 * @test
760 * @see http://bugs.typo3.org/view.php?id=13134
761 */
762 public function locateStatementWithPositionIsProperlyQuoted() {
763 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
764 '*, CASE WHEN' .
765 ' LOCATE(' . $GLOBALS['TYPO3_DB']->fullQuoteStr('(fce)', 'tx_templavoila_tmplobj') . ', datastructure, 4)>0 THEN 2' .
766 ' ELSE 1' .
767 ' END AS scope',
768 'tx_templavoila_tmplobj',
769 '1=1'
770 ));
771 $expected = 'SELECT *, CASE WHEN INSTR("datastructure", \'(fce)\', 4) > 0 THEN 2 ELSE 1 END AS "scope" FROM "tx_templavoila_tmplobj" WHERE 1 = 1';
772 $this->assertEquals($expected, $query);
773 }
774 }
775 ?>