Raised DBAL version from 1.1.7 to 1.1.8
[Packages/TYPO3.CMS.git] / typo3 / sysext / dbal / tests / dbMssqlTest.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 MS SQL database handling.
31 *
32 * $Id$
33 *
34 * @author Xavier Perseguers <typo3@perseguers.ch>
35 *
36 * @package TYPO3
37 * @subpackage dbal
38 */
39 class dbMssqlTest 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 MS SQL
60 require('fixtures/mssql.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 $this->assertFalse($GLOBALS['TYPO3_DB']->isConnected());
68
69 // Initialize a fake MS SQL connection
70 FakeDbConnection::connect($GLOBALS['TYPO3_DB'], 'mssql');
71
72 $this->assertTrue($GLOBALS['TYPO3_DB']->isConnected());
73 }
74
75 /**
76 * Cleans up the environment after running a test.
77 */
78 public function tearDown() {
79 // Clear DBAL-generated cache files
80 $GLOBALS['TYPO3_DB']->clearCachedFieldInfo();
81 // Restore DBAL configuration
82 $GLOBALS['TYPO3_CONF_VARS']['EXTCONF']['dbal'] = $this->dbalConfig;
83 // Restore DB connection
84 $GLOBALS['TYPO3_DB'] = $this->db;
85 }
86
87 /**
88 * Cleans a SQL query.
89 *
90 * @param mixed $sql
91 * @return mixed (string or array)
92 */
93 private function cleanSql($sql) {
94 if (!is_string($sql)) {
95 return $sql;
96 }
97
98 $sql = str_replace("\n", ' ', $sql);
99 $sql = preg_replace('/\s+/', ' ', $sql);
100 return trim($sql);
101 }
102
103 /**
104 * @test
105 */
106 public function configurationIsUsingAdodbAndDriverMssql() {
107 $configuration = $GLOBALS['TYPO3_DB']->conf['handlerCfg'];
108 $this->assertTrue(is_array($configuration) && count($configuration) > 0, 'No configuration found');
109 $this->assertEquals('adodb', $configuration['_DEFAULT']['type']);
110 $this->assertTrue($GLOBALS['TYPO3_DB']->runningADOdbDriver('mssql') !== FALSE, 'Not using mssql driver');
111 }
112
113 /**
114 * @test
115 */
116 public function tablesWithMappingAreDetected() {
117 $tablesWithMapping = array_keys($GLOBALS['TYPO3_CONF_VARS']['EXTCONF']['dbal']['mapping']);
118
119 foreach ($GLOBALS['TYPO3_DB']->cache_fieldType as $table => $fieldTypes) {
120 $tableDef = $GLOBALS['TYPO3_DB']->_call('map_needMapping', $table);
121
122 if (in_array($table, $tablesWithMapping)) {
123 self::assertTrue(is_array($tableDef), 'Table ' . $table . ' was expected to need mapping');
124 } else {
125 self::assertFalse($tableDef, 'Table ' . $table . ' was not expected to need mapping');
126 }
127 }
128 }
129
130 ///////////////////////////////////////
131 // Tests concerning remapping with
132 // external (non-TYPO3) databases
133 ///////////////////////////////////////
134
135 /**
136 * @test
137 * @see http://bugs.typo3.org/view.php?id=13490
138 */
139 public function canRemapPidToZero() {
140 $selectFields = 'uid, FirstName, LastName';
141 $fromTables = 'Members';
142 $whereClause = 'pid=0 AND cruser_id=1';
143 $groupBy = '';
144 $orderBy = '';
145
146 $GLOBALS['TYPO3_DB']->_callRef('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
147 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery($selectFields, $fromTables, $whereClause, $groupBy, $orderBy));
148
149 $expected = 'SELECT "MemberID", "FirstName", "LastName" FROM "Members" WHERE 0 = 0 AND 1 = 1';
150 $this->assertEquals($expected, $query);
151 }
152
153 ///////////////////////////////////////
154 // Tests concerning advanced operators
155 ///////////////////////////////////////
156
157 /**
158 * @test
159 * @see http://bugs.typo3.org/view.php?id=13134
160 */
161 public function locateStatementIsProperlyQuoted() {
162 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
163 '*, CASE WHEN' .
164 ' LOCATE(' . $GLOBALS['TYPO3_DB']->fullQuoteStr('(fce)', 'tx_templavoila_tmplobj') . ', datastructure)>0 THEN 2' .
165 ' ELSE 1' .
166 ' END AS scope',
167 'tx_templavoila_tmplobj',
168 '1=1'
169 ));
170 $expected = 'SELECT *, CASE WHEN CHARINDEX(\'(fce)\', "datastructure") > 0 THEN 2 ELSE 1 END AS "scope" FROM "tx_templavoila_tmplobj" WHERE 1 = 1';
171 $this->assertEquals($expected, $query);
172 }
173
174 /**
175 * @test
176 * @see http://bugs.typo3.org/view.php?id=13134
177 */
178 public function locateStatementWithPositionIsProperlyQuoted() {
179 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
180 '*, CASE WHEN' .
181 ' LOCATE(' . $GLOBALS['TYPO3_DB']->fullQuoteStr('(fce)', 'tx_templavoila_tmplobj') . ', datastructure, 4)>0 THEN 2' .
182 ' ELSE 1' .
183 ' END AS scope',
184 'tx_templavoila_tmplobj',
185 '1=1'
186 ));
187 $expected = 'SELECT *, CASE WHEN CHARINDEX(\'(fce)\', "datastructure", 4) > 0 THEN 2 ELSE 1 END AS "scope" FROM "tx_templavoila_tmplobj" WHERE 1 = 1';
188 $this->assertEquals($expected, $query);
189 }
190
191 /**
192 * @test
193 * @see http://bugs.typo3.org/view.php?id=13134
194 */
195 public function locateStatementIsProperlyRemapped() {
196 $selectFields = '*, CASE WHEN' .
197 ' LOCATE(' . $GLOBALS['TYPO3_DB']->fullQuoteStr('(fce)', 'tx_templavoila_tmplobj') . ', datastructure, 4)>0 THEN 2' .
198 ' ELSE 1' .
199 ' END AS scope';
200 $fromTables = 'tx_templavoila_tmplobj';
201 $whereClause = '1=1';
202 $groupBy = '';
203 $orderBy = '';
204
205 $GLOBALS['TYPO3_DB']->_callRef('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
206 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery($selectFields, $fromTables, $whereClause, $groupBy, $orderBy));
207
208 $expected = 'SELECT *, CASE WHEN CHARINDEX(\'(fce)\', "ds", 4) > 0 THEN 2 ELSE 1 END AS "scope" FROM "tx_templavoila_tmplobj" WHERE 1 = 1';
209 $this->assertEquals($expected, $query);
210 }
211
212 /**
213 * @test
214 * @see http://bugs.typo3.org/view.php?id=13134
215 */
216 public function locateStatementWithExternalTableIsProperlyRemapped() {
217 $selectFields = '*, CASE WHEN' .
218 ' LOCATE(' . $GLOBALS['TYPO3_DB']->fullQuoteStr('(fce)', 'tx_templavoila_tmplobj') . ', tx_templavoila_tmplobj.datastructure, 4)>0 THEN 2' .
219 ' ELSE 1' .
220 ' END AS scope';
221 $fromTables = 'tx_templavoila_tmplobj';
222 $whereClause = '1=1';
223 $groupBy = '';
224 $orderBy = '';
225
226 $GLOBALS['TYPO3_DB']->_callRef('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
227 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery($selectFields, $fromTables, $whereClause, $groupBy, $orderBy));
228
229 $expected = 'SELECT *, CASE WHEN CHARINDEX(\'(fce)\', "tx_templavoila_tmplobj"."ds", 4) > 0 THEN 2 ELSE 1 END AS "scope" FROM "tx_templavoila_tmplobj" WHERE 1 = 1';
230 $this->assertEquals($expected, $query);
231 }
232
233 /**
234 * @test
235 * @see http://bugs.typo3.org/view.php?id=6196
236 */
237 public function IfNullIsProperlyRemapped() {
238 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
239 '*',
240 'tt_news_cat_mm',
241 'IFNULL(tt_news_cat_mm.uid_foreign,0) IN (21,22)'
242 ));
243 $expected = 'SELECT * FROM "tt_news_cat_mm" WHERE ISNULL("tt_news_cat_mm"."uid_foreign", 0) IN (21,22)';
244 $this->assertEquals($expected, $query);
245 }
246 }
247 ?>