a435d4aef3e3e406394cf503b09b54cad6504327
[Packages/TYPO3.CMS.git] / typo3 / sysext / dbal / Tests / DatabaseMssqlTest.php
1 <?php
2 namespace TYPO3\CMS\Dbal\Tests;
3
4 /**
5 * Testcase for class ux_t3lib_db. Testing MS SQL database handling.
6 *
7 * @author Xavier Perseguers <xavier@typo3.org>
8 * @package TYPO3
9 * @subpackage dbal
10 */
11 class DatabaseMssqlTest extends \TYPO3\CMS\Dbal\Tests\BaseTestCase {
12
13 /**
14 * @var t3lib_db
15 */
16 protected $db;
17
18 /**
19 * @var array
20 */
21 protected $dbalConfig;
22
23 /**
24 * Prepares the environment before running a test.
25 */
26 public function setUp() {
27 // Backup DBAL configuration
28 $this->dbalConfig = $GLOBALS['TYPO3_CONF_VARS']['EXTCONF']['dbal'];
29 // Backup database connection
30 $this->db = $GLOBALS['TYPO3_DB'];
31 // Reconfigure DBAL to use MS SQL
32 require 'fixtures/mssql.config.php';
33 $className = self::buildAccessibleProxy('ux_t3lib_db');
34 $GLOBALS['TYPO3_DB'] = new $className();
35 $parserClassName = self::buildAccessibleProxy('ux_TYPO3\\CMS\\Core\\Database\\SqlParser');
36 $GLOBALS['TYPO3_DB']->SQLparser = new $parserClassName();
37 $this->assertFalse($GLOBALS['TYPO3_DB']->isConnected());
38 // Initialize a fake MS SQL connection
39 \TYPO3\CMS\Dbal\Tests\FakeDatabaseConnection::connect($GLOBALS['TYPO3_DB'], 'mssql');
40 $this->assertTrue($GLOBALS['TYPO3_DB']->isConnected());
41 }
42
43 /**
44 * Cleans up the environment after running a test.
45 */
46 public function tearDown() {
47 // Clear DBAL-generated cache files
48 $GLOBALS['TYPO3_DB']->clearCachedFieldInfo();
49 // Restore DBAL configuration
50 $GLOBALS['TYPO3_CONF_VARS']['EXTCONF']['dbal'] = $this->dbalConfig;
51 // Restore DB connection
52 $GLOBALS['TYPO3_DB'] = $this->db;
53 }
54
55 /**
56 * Cleans a SQL query.
57 *
58 * @param mixed $sql
59 * @return mixed (string or array)
60 */
61 private function cleanSql($sql) {
62 if (!is_string($sql)) {
63 return $sql;
64 }
65 $sql = str_replace('
66 ', ' ', $sql);
67 $sql = preg_replace('/\\s+/', ' ', $sql);
68 return trim($sql);
69 }
70
71 /**
72 * @test
73 */
74 public function configurationIsUsingAdodbAndDriverMssql() {
75 $configuration = $GLOBALS['TYPO3_DB']->conf['handlerCfg'];
76 $this->assertTrue(is_array($configuration) && count($configuration) > 0, 'No configuration found');
77 $this->assertEquals('adodb', $configuration['_DEFAULT']['type']);
78 $this->assertTrue($GLOBALS['TYPO3_DB']->runningADOdbDriver('mssql') !== FALSE, 'Not using mssql driver');
79 }
80
81 /**
82 * @test
83 */
84 public function tablesWithMappingAreDetected() {
85 $tablesWithMapping = array_keys($GLOBALS['TYPO3_CONF_VARS']['EXTCONF']['dbal']['mapping']);
86 foreach ($GLOBALS['TYPO3_DB']->cache_fieldType as $table => $fieldTypes) {
87 $tableDef = $GLOBALS['TYPO3_DB']->_call('map_needMapping', $table);
88 if (in_array($table, $tablesWithMapping)) {
89 self::assertTrue(is_array($tableDef), ('Table ' . $table) . ' was expected to need mapping');
90 } else {
91 self::assertFalse($tableDef, ('Table ' . $table) . ' was not expected to need mapping');
92 }
93 }
94 }
95
96 /**
97 * @test
98 * @see http://bugs.typo3.org/view.php?id=14985
99 */
100 public function findInSetIsProperlyRemapped() {
101 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery('*', 'fe_users', 'FIND_IN_SET(10, usergroup)'));
102 $expected = 'SELECT * FROM "fe_users" WHERE \',\'+"usergroup"+\',\' LIKE \'%,10,%\'';
103 $this->assertEquals($expected, $query);
104 }
105
106 /**
107 * @test
108 * @see http://forge.typo3.org/issues/27858
109 */
110 public function canParseSingleQuote() {
111 $parseString = 'SELECT * FROM pages WHERE title=\'1\'\'\' AND deleted=0';
112 $components = $GLOBALS['TYPO3_DB']->SQLparser->_callRef('parseSELECT', $parseString);
113 $this->assertTrue(is_array($components), $components);
114 $this->assertTrue(empty($components['parseString']), 'parseString is not empty');
115 }
116
117 ///////////////////////////////////////
118 // Tests concerning remapping with
119 // external (non-TYPO3) databases
120 ///////////////////////////////////////
121 /**
122 * @test
123 * @see http://bugs.typo3.org/view.php?id=13490
124 */
125 public function canRemapPidToZero() {
126 $selectFields = 'uid, FirstName, LastName';
127 $fromTables = 'Members';
128 $whereClause = 'pid=0 AND cruser_id=1';
129 $groupBy = '';
130 $orderBy = '';
131 $remappedParameters = $GLOBALS['TYPO3_DB']->_call('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
132 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->_call('SELECTqueryFromArray', $remappedParameters));
133 $expected = 'SELECT "MemberID", "FirstName", "LastName" FROM "Members" WHERE 0 = 0 AND 1 = 1';
134 $this->assertEquals($expected, $query);
135 }
136
137 ///////////////////////////////////////
138 // Tests concerning advanced operators
139 ///////////////////////////////////////
140 /**
141 * @test
142 * @see http://bugs.typo3.org/view.php?id=13134
143 */
144 public function locateStatementIsProperlyQuoted() {
145 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery((((('*, CASE WHEN' . ' LOCATE(') . $GLOBALS['TYPO3_DB']->fullQuoteStr('(fce)', 'tx_templavoila_tmplobj')) . ', datastructure)>0 THEN 2') . ' ELSE 1') . ' END AS scope', 'tx_templavoila_tmplobj', '1=1'));
146 $expected = 'SELECT *, CASE WHEN CHARINDEX(\'(fce)\', "datastructure") > 0 THEN 2 ELSE 1 END AS "scope" FROM "tx_templavoila_tmplobj" WHERE 1 = 1';
147 $this->assertEquals($expected, $query);
148 }
149
150 /**
151 * @test
152 * @see http://bugs.typo3.org/view.php?id=13134
153 */
154 public function locateStatementWithPositionIsProperlyQuoted() {
155 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery((((('*, CASE WHEN' . ' LOCATE(') . $GLOBALS['TYPO3_DB']->fullQuoteStr('(fce)', 'tx_templavoila_tmplobj')) . ', datastructure, 4)>0 THEN 2') . ' ELSE 1') . ' END AS scope', 'tx_templavoila_tmplobj', '1=1'));
156 $expected = 'SELECT *, CASE WHEN CHARINDEX(\'(fce)\', "datastructure", 4) > 0 THEN 2 ELSE 1 END AS "scope" FROM "tx_templavoila_tmplobj" WHERE 1 = 1';
157 $this->assertEquals($expected, $query);
158 }
159
160 /**
161 * @test
162 * @see http://bugs.typo3.org/view.php?id=13134
163 */
164 public function locateStatementIsProperlyRemapped() {
165 $selectFields = (((('*, CASE WHEN' . ' LOCATE(') . $GLOBALS['TYPO3_DB']->fullQuoteStr('(fce)', 'tx_templavoila_tmplobj')) . ', datastructure, 4)>0 THEN 2') . ' ELSE 1') . ' END AS scope';
166 $fromTables = 'tx_templavoila_tmplobj';
167 $whereClause = '1=1';
168 $groupBy = '';
169 $orderBy = '';
170 $remappedParameters = $GLOBALS['TYPO3_DB']->_call('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
171 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->_call('SELECTqueryFromArray', $remappedParameters));
172 $expected = 'SELECT *, CASE WHEN CHARINDEX(\'(fce)\', "ds", 4) > 0 THEN 2 ELSE 1 END AS "scope" FROM "tx_templavoila_tmplobj" WHERE 1 = 1';
173 $this->assertEquals($expected, $query);
174 }
175
176 /**
177 * @test
178 * @see http://bugs.typo3.org/view.php?id=13134
179 */
180 public function locateStatementWithExternalTableIsProperlyRemapped() {
181 $selectFields = (((('*, CASE WHEN' . ' LOCATE(') . $GLOBALS['TYPO3_DB']->fullQuoteStr('(fce)', 'tx_templavoila_tmplobj')) . ', tx_templavoila_tmplobj.datastructure, 4)>0 THEN 2') . ' ELSE 1') . ' END AS scope';
182 $fromTables = 'tx_templavoila_tmplobj';
183 $whereClause = '1=1';
184 $groupBy = '';
185 $orderBy = '';
186 $remappedParameters = $GLOBALS['TYPO3_DB']->_call('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
187 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->_call('SELECTqueryFromArray', $remappedParameters));
188 $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';
189 $this->assertEquals($expected, $query);
190 }
191
192 /**
193 * @test
194 * @see http://bugs.typo3.org/view.php?id=6196
195 */
196 public function IfNullIsProperlyRemapped() {
197 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery('*', 'tt_news_cat_mm', 'IFNULL(tt_news_cat_mm.uid_foreign,0) IN (21,22)'));
198 $expected = 'SELECT * FROM "tt_news_cat_mm" WHERE ISNULL("tt_news_cat_mm"."uid_foreign", 0) IN (21,22)';
199 $this->assertEquals($expected, $query);
200 }
201
202 }
203
204
205 ?>