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