Reorganized unit-tests
[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 // Tests concerning quoting
130 ///////////////////////////////////////
131
132 /**
133 * @test
134 */
135 public function selectQueryIsProperlyQuoted() {
136 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
137 'uid', // select fields
138 'tt_content', // from table
139 'pid=1', // where clause
140 'cruser_id', // group by
141 'tstamp' // order by
142 ));
143 $expected = 'SELECT "uid" FROM "tt_content" WHERE "pid" = 1 GROUP BY "cruser_id" ORDER BY "tstamp"';
144 $this->assertEquals($expected, $query);
145 }
146
147 /**
148 * @test
149 * @see http://bugs.typo3.org/view.php?id=2438
150 */
151 public function distinctFieldIsProperlyQuoted() {
152 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
153 'COUNT(DISTINCT pid)', // select fields
154 'tt_content', // from table
155 '1=1' // where clause
156 ));
157 $expected = 'SELECT COUNT(DISTINCT "pid") FROM "tt_content" WHERE 1 = 1';
158 $this->assertEquals($expected, $query);
159 }
160
161 /**
162 * @test
163 * @see http://bugs.typo3.org/view.php?id=10411
164 * @remark Remapping is not expected here
165 */
166 public function multipleInnerJoinsAreProperlyQuoted() {
167 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
168 '*',
169 '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',
170 '1=1'
171 ));
172 $expected = 'SELECT * FROM "tt_news_cat"';
173 $expected .= ' INNER JOIN "tt_news_cat_mm" ON "tt_news_cat"."uid"="tt_news_cat_mm"."uid_foreign"';
174 $expected .= ' INNER JOIN "tt_news" ON "tt_news"."uid"="tt_news_cat_mm"."uid_local"';
175 $expected .= ' WHERE 1 = 1';
176 $this->assertEquals($expected, $query);
177 }
178
179 /**
180 * @test
181 * @see http://bugs.typo3.org/view.php?id=6198
182 */
183 public function stringsWithinInClauseAreProperlyQuoted() {
184 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
185 'COUNT(DISTINCT tx_dam.uid) AS count',
186 'tx_dam',
187 'tx_dam.pid IN (1) AND tx_dam.file_type IN (\'gif\',\'png\',\'jpg\',\'jpeg\') AND tx_dam.deleted = 0'
188 ));
189 $expected = 'SELECT COUNT(DISTINCT "tx_dam"."uid") AS "count" FROM "tx_dam"';
190 $expected .= ' WHERE "tx_dam"."pid" IN (1) AND "tx_dam"."file_type" IN (\'gif\',\'png\',\'jpg\',\'jpeg\') AND "tx_dam"."deleted" = 0';
191 $this->assertEquals($expected, $query);
192 }
193
194 /**
195 * @test
196 * @see http://bugs.typo3.org/view.php?id=12515
197 * @remark Remapping is not expected here
198 */
199 public function concatAfterLikeOperatorIsProperlyQuoted() {
200 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
201 '*',
202 'sys_refindex, tx_dam_file_tracking',
203 'sys_refindex.tablename = \'tx_dam_file_tracking\''
204 . ' AND sys_refindex.ref_string LIKE CONCAT(tx_dam_file_tracking.file_path, tx_dam_file_tracking.file_name)'
205 ));
206 $expected = 'SELECT * FROM "sys_refindex", "tx_dam_file_tracking" WHERE "sys_refindex"."tablename" = \'tx_dam_file_tracking\'';
207 $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)';
208 $this->assertEquals($expected, $query);
209 }
210
211 ///////////////////////////////////////
212 // Tests concerning remapping
213 ///////////////////////////////////////
214
215 /**
216 * @test
217 * @see http://bugs.typo3.org/view.php?id=10411
218 * @remark Remapping is expected here
219 */
220 public function tablesAndFieldsAreRemappedInMultipleJoins() {
221 $selectFields = '*';
222 $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';
223 $whereClause = '1=1';
224 $groupBy = '';
225 $orderBy = '';
226
227 $GLOBALS['TYPO3_DB']->_callRef('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
228 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery($selectFields, $fromTables, $whereClause, $groupBy, $orderBy));
229
230 $expected = 'SELECT * FROM "ext_tt_news_cat"';
231 $expected .= ' INNER JOIN "ext_tt_news_cat_mm" ON "ext_tt_news_cat"."cat_uid"="ext_tt_news_cat_mm"."uid_foreign"';
232 $expected .= ' INNER JOIN "ext_tt_news" ON "ext_tt_news"."news_uid"="ext_tt_news_cat_mm"."local_uid"';
233 $expected .= ' WHERE 1 = 1';
234 $this->assertEquals($expected, $query);
235 }
236
237 /**
238 * @test
239 * @see http://bugs.typo3.org/view.php?id=6953
240 */
241 public function fieldWithinSqlFunctionIsRemapped() {
242 $selectFields = 'tstamp, script, SUM(exec_time) AS calc_sum, COUNT(*) AS qrycount, MAX(errorFlag) AS error';
243 $fromTables = 'tx_dbal_debuglog';
244 $whereClause = '1=1';
245 $groupBy = '';
246 $orderBy = '';
247
248 $GLOBALS['TYPO3_DB']->_callRef('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
249 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery($selectFields, $fromTables, $whereClause, $groupBy, $orderBy));
250
251 $expected = 'SELECT "tstamp", "script", SUM("exec_time") AS "calc_sum", COUNT(*) AS "qrycount", MAX("errorflag") AS "error" FROM "tx_dbal_debuglog" WHERE 1 = 1';
252 $this->assertEquals($expected, $query);
253 }
254
255 /**
256 * @test
257 * @see http://bugs.typo3.org/view.php?id=6953
258 */
259 public function tableAndFieldWithinSqlFunctionIsRemapped() {
260 $selectFields = 'MAX(tt_news_cat.uid) AS biggest_id';
261 $fromTables = 'tt_news_cat INNER JOIN tt_news_cat_mm ON tt_news_cat.uid = tt_news_cat_mm.uid_foreign';
262 $whereClause = 'tt_news_cat_mm.uid_local > 50';
263 $groupBy = '';
264 $orderBy = '';
265
266 $GLOBALS['TYPO3_DB']->_callRef('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
267 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery($selectFields, $fromTables, $whereClause, $groupBy, $orderBy));
268
269 $expected = 'SELECT MAX("ext_tt_news_cat"."cat_uid") AS "biggest_id" FROM "ext_tt_news_cat"';
270 $expected .= ' INNER JOIN "ext_tt_news_cat_mm" ON "ext_tt_news_cat"."cat_uid"="ext_tt_news_cat_mm"."uid_foreign"';
271 $expected .= ' WHERE "ext_tt_news_cat_mm"."local_uid" > 50';
272 $this->assertEquals($expected, $query);
273 }
274
275 /**
276 * @test
277 * @see http://bugs.typo3.org/view.php?id=12515
278 * @remark Remapping is expected here
279 */
280 public function concatAfterLikeOperatorIsRemapped() {
281 $selectFields = '*';
282 $fromTables = 'sys_refindex, tx_dam_file_tracking';
283 $whereClause = 'sys_refindex.tablename = \'tx_dam_file_tracking\''
284 . ' AND sys_refindex.ref_string LIKE CONCAT(tx_dam_file_tracking.file_path, tx_dam_file_tracking.file_name)';
285 $groupBy = '';
286 $orderBy = '';
287
288 $GLOBALS['TYPO3_DB']->_callRef('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
289 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery($selectFields, $fromTables, $whereClause, $groupBy, $orderBy));
290
291 $expected = 'SELECT * FROM "sys_refindex", "tx_dam_file_tracking" WHERE "sys_refindex"."tablename" = \'tx_dam_file_tracking\'';
292 $expected .= ' AND (dbms_lob.instr("sys_refindex"."ref_string", CONCAT("tx_dam_file_tracking"."path","tx_dam_file_tracking"."filename"),1,1) > 0)';
293 $this->assertEquals($expected, $query);
294 }
295
296 /**
297 * @test
298 * @see http://bugs.typo3.org/view.php?id=5708
299 */
300 public function fieldIsMappedOnRightSideOfAJoinCondition() {
301 $selectFields = 'cpg_categories.uid, cpg_categories.name';
302 $fromTables = 'cpg_categories, pages';
303 $whereClause = 'pages.uid = cpg_categories.pid AND pages.deleted = 0 AND 1 = 1';
304 $groupBy = '';
305 $orderBy = 'cpg_categories.pos';
306
307 $GLOBALS['TYPO3_DB']->_callRef('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
308 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery($selectFields, $fromTables, $whereClause, $groupBy, $orderBy));
309
310 $expected = 'SELECT "cpg_categories"."uid", "cpg_categories"."name" FROM "cpg_categories", "pages" WHERE "pages"."uid" = "cpg_categories"."page_id"';
311 $expected .= ' AND "pages"."deleted" = 0 AND 1 = 1 ORDER BY "cpg_categories"."pos"';
312 $this->assertEquals($expected, $query);
313 }
314
315 ///////////////////////////////////////
316 // Tests concerning DB management
317 ///////////////////////////////////////
318
319 /**
320 * @test
321 * @see http://bugs.typo3.org/view.php?id=12670
322 */
323 public function notNullableColumnsWithDefaultEmptyStringAreCreatedAsNullable() {
324 $parseString = '
325 CREATE TABLE tx_realurl_uniqalias (
326 uid int(11) NOT NULL auto_increment,
327 tstamp int(11) DEFAULT \'0\' NOT NULL,
328 tablename varchar(60) DEFAULT \'\' NOT NULL,
329 field_alias varchar(255) DEFAULT \'\' NOT NULL,
330 field_id varchar(60) DEFAULT \'\' NOT NULL,
331 value_alias varchar(255) DEFAULT \'\' NOT NULL,
332 value_id int(11) DEFAULT \'0\' NOT NULL,
333 lang int(11) DEFAULT \'0\' NOT NULL,
334 expire int(11) DEFAULT \'0\' NOT NULL,
335
336 PRIMARY KEY (uid),
337 KEY tablename (tablename),
338 KEY bk_realurl01 (field_alias,field_id,value_id,lang,expire),
339 KEY bk_realurl02 (tablename,field_alias,field_id,value_alias(220),expire)
340 );
341 ';
342
343 $components = $GLOBALS['TYPO3_DB']->SQLparser->_callRef('parseCREATETABLE', $parseString);
344 $this->assertTrue(is_array($components), 'Not an array: ' . $components);
345
346 $sqlCommands = $GLOBALS['TYPO3_DB']->SQLparser->_call('compileCREATETABLE', $components);
347 $this->assertTrue(is_array($sqlCommands), 'Not an array: ' . $sqlCommands);
348 $this->assertEquals(4, count($sqlCommands));
349
350 $expected = $this->cleanSql('
351 CREATE TABLE "tx_realurl_uniqalias" (
352 "uid" NUMBER(20) NOT NULL,
353 "tstamp" NUMBER(20) DEFAULT 0,
354 "tablename" VARCHAR(60) DEFAULT \'\',
355 "field_alias" VARCHAR(255) DEFAULT \'\',
356 "field_id" VARCHAR(60) DEFAULT \'\',
357 "value_alias" VARCHAR(255) DEFAULT \'\',
358 "value_id" NUMBER(20) DEFAULT 0,
359 "lang" NUMBER(20) DEFAULT 0,
360 "expire" NUMBER(20) DEFAULT 0,
361 PRIMARY KEY ("uid")
362 )
363 ');
364 $this->assertEquals($expected, $this->cleanSql($sqlCommands[0]));
365 }
366
367 /**
368 * @test
369 * @see http://bugs.typo3.org/view.php?id=11142
370 * @see http://bugs.typo3.org/view.php?id=12670
371 */
372 public function defaultValueIsProperlyQuotedInCreateTable() {
373 $parseString = '
374 CREATE TABLE tx_test (
375 uid int(11) NOT NULL auto_increment,
376 lastname varchar(60) DEFAULT \'unknown\' NOT NULL,
377 firstname varchar(60) DEFAULT \'\' NOT NULL,
378 language varchar(2) NOT NULL,
379 tstamp int(11) DEFAULT \'0\' NOT NULL,
380
381 PRIMARY KEY (uid),
382 KEY name (name)
383 );
384 ';
385
386 $components = $GLOBALS['TYPO3_DB']->SQLparser->_callRef('parseCREATETABLE', $parseString);
387 $this->assertTrue(is_array($components), 'Not an array: ' . $components);
388
389 $sqlCommands = $GLOBALS['TYPO3_DB']->SQLparser->_call('compileCREATETABLE', $components);
390 $this->assertTrue(is_array($sqlCommands), 'Not an array: ' . $sqlCommands);
391 $this->assertEquals(2, count($sqlCommands));
392
393 $expected = $this->cleanSql('
394 CREATE TABLE "tx_test" (
395 "uid" NUMBER(20) NOT NULL,
396 "lastname" VARCHAR(60) DEFAULT \'unknown\',
397 "firstname" VARCHAR(60) DEFAULT \'\',
398 "language" VARCHAR(2) DEFAULT \'\',
399 "tstamp" NUMBER(20) DEFAULT 0,
400 PRIMARY KEY ("uid")
401 )
402 ');
403 $this->assertEquals($expected, $this->cleanSql($sqlCommands[0]));
404 }
405 }
406 ?>