02f803ae878e5a31d76cea15fe134eae068c4ba2
[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 * This file is part of the TYPO3 CMS project.
6 *
7 * It is free software; you can redistribute it and/or modify it under
8 * the terms of the GNU General Public License, either version 2
9 * of the License, or any later version.
10 *
11 * For the full copyright and license information, please read the
12 * LICENSE.txt file that was distributed with this source code.
13 *
14 * The TYPO3 project - inspiring people to share!
15 */
16
17 /**
18 * Test case
19 */
20 class DatabaseConnectionMssqlTest extends AbstractTestCase {
21
22 /**
23 * @var \TYPO3\CMS\Dbal\Database\DatabaseConnection|\PHPUnit_Framework_MockObject_MockObject|\TYPO3\CMS\Core\Tests\AccessibleObjectInterface
24 */
25 protected $subject;
26
27 /**
28 * Prepare a DatabaseConnection subject ready to parse mssql queries
29 *
30 * @return \TYPO3\CMS\Dbal\Database\DatabaseConnection|\PHPUnit_Framework_MockObject_MockObject|\TYPO3\CMS\Core\Tests\AccessibleObjectInterface
31 */
32 protected function setUp() {
33 $configuration = array(
34 'handlerCfg' => array(
35 '_DEFAULT' => array(
36 'type' => 'adodb',
37 'config' => array(
38 'driver' => 'mssql',
39 ),
40 ),
41 ),
42 'mapping' => array(
43 'tx_templavoila_tmplobj' => array(
44 'mapFieldNames' => array(
45 'datastructure' => 'ds',
46 ),
47 ),
48 'Members' => array(
49 'mapFieldNames' => array(
50 'pid' => '0',
51 'cruser_id' => '1',
52 'uid' => 'MemberID',
53 ),
54 ),
55 ),
56 );
57 $this->subject = $this->prepareSubject('mssql', $configuration);
58 }
59
60 /**
61 * @test
62 */
63 public function runningADOdbDriverReturnsTrueWithMssqlForMssqlDefaultDriverConfiguration() {
64 $this->assertTrue($this->subject->runningADOdbDriver('mssql'));
65 }
66
67 /**
68 * @test
69 * @see http://forge.typo3.org/issues/23087
70 */
71 public function findInSetIsProperlyRemapped() {
72 $expected = 'SELECT * FROM "fe_users" WHERE \',\'+"usergroup"+\',\' LIKE \'%,10,%\'';
73 $result = $this->subject->SELECTquery('*', 'fe_users', 'FIND_IN_SET(10, usergroup)');
74 $this->assertEquals($expected, $this->cleanSql($result));
75 }
76
77 /**
78 * @test
79 * @see http://forge.typo3.org/issues/27858
80 */
81 public function canParseSingleQuote() {
82 $parseString = 'SELECT * FROM pages WHERE title=\'1\'\'\' AND deleted=0';
83 $components = $this->subject->SQLparser->_callRef('parseSELECT', $parseString);
84
85 $this->assertTrue(is_array($components), $components);
86 $this->assertEmpty($components['parseString']);
87 }
88
89 ///////////////////////////////////////
90 // Tests concerning remapping with
91 // external (non-TYPO3) databases
92 ///////////////////////////////////////
93 /**
94 * @test
95 * @see http://forge.typo3.org/issues/22096
96 */
97 public function canRemapPidToZero() {
98 $selectFields = 'uid, FirstName, LastName';
99 $fromTables = 'Members';
100 $whereClause = 'pid=0 AND cruser_id=1';
101 $groupBy = '';
102 $orderBy = '';
103
104 $remappedParameters = $this->subject->_call('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
105 $result = $this->subject->_call('SELECTqueryFromArray', $remappedParameters);
106 $expected = 'SELECT "MemberID", "FirstName", "LastName" FROM "Members" WHERE 0 = 0 AND 1 = 1';
107 $this->assertEquals($expected, $this->cleanSql($result));
108 }
109
110 ///////////////////////////////////////
111 // Tests concerning advanced operators
112 ///////////////////////////////////////
113 /**
114 * @test
115 * @see http://forge.typo3.org/issues/21902
116 */
117 public function locateStatementIsProperlyQuoted() {
118 $result = $this->subject->SELECTquery('*, CASE WHEN' . ' LOCATE(' . $this->subject->fullQuoteStr('(fce)', 'tx_templavoila_tmplobj') . ', datastructure)>0 THEN 2' . ' ELSE 1' . ' END AS scope', 'tx_templavoila_tmplobj', '1=1');
119 $expected = 'SELECT *, CASE WHEN CHARINDEX(\'(fce)\', "datastructure") > 0 THEN 2 ELSE 1 END AS "scope" FROM "tx_templavoila_tmplobj" WHERE 1 = 1';
120 $this->assertEquals($expected, $this->cleanSql($result));
121 }
122
123 /**
124 * @test
125 * @see http://forge.typo3.org/issues/21902
126 */
127 public function locateStatementWithPositionIsProperlyQuoted() {
128 $result = $this->subject->SELECTquery('*, CASE WHEN' . ' LOCATE(' . $this->subject->fullQuoteStr('(fce)', 'tx_templavoila_tmplobj') . ', datastructure, 4)>0 THEN 2' . ' ELSE 1' . ' END AS scope', 'tx_templavoila_tmplobj', '1=1');
129 $expected = 'SELECT *, CASE WHEN CHARINDEX(\'(fce)\', "datastructure", 4) > 0 THEN 2 ELSE 1 END AS "scope" FROM "tx_templavoila_tmplobj" WHERE 1 = 1';
130 $this->assertEquals($expected, $this->cleanSql($result));
131 }
132
133 /**
134 * @test
135 * @see http://forge.typo3.org/issues/21902
136 */
137 public function locateStatementIsProperlyRemapped() {
138 $selectFields = '*, CASE WHEN' . ' LOCATE(' . $this->subject->fullQuoteStr('(fce)', 'tx_templavoila_tmplobj') . ', datastructure, 4)>0 THEN 2' . ' ELSE 1' . ' END AS scope';
139 $fromTables = 'tx_templavoila_tmplobj';
140 $whereClause = '1=1';
141 $groupBy = '';
142 $orderBy = '';
143 $remappedParameters = $this->subject->_call('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
144
145 $result = $this->subject->_call('SELECTqueryFromArray', $remappedParameters);
146 $expected = 'SELECT *, CASE WHEN CHARINDEX(\'(fce)\', "ds", 4) > 0 THEN 2 ELSE 1 END AS "scope" FROM "tx_templavoila_tmplobj" WHERE 1 = 1';
147 $this->assertEquals($expected, $this->cleanSql($result));
148 }
149
150 /**
151 * @test
152 * @see http://forge.typo3.org/issues/21902
153 */
154 public function locateStatementWithExternalTableIsProperlyRemapped() {
155 $selectFields = '*, CASE WHEN' . ' LOCATE(' . $this->subject->fullQuoteStr('(fce)', 'tx_templavoila_tmplobj') . ', tx_templavoila_tmplobj.datastructure, 4)>0 THEN 2' . ' ELSE 1' . ' END AS scope';
156 $fromTables = 'tx_templavoila_tmplobj';
157 $whereClause = '1=1';
158 $groupBy = '';
159 $orderBy = '';
160 $remappedParameters = $this->subject->_call('map_remapSELECTQueryParts', $selectFields, $fromTables, $whereClause, $groupBy, $orderBy);
161
162 $result = $this->subject->_call('SELECTqueryFromArray', $remappedParameters);
163 $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';
164 $this->assertEquals($expected, $this->cleanSql($result));
165 }
166
167 /**
168 * @test
169 * @see http://forge.typo3.org/issues/17552
170 */
171 public function IfNullIsProperlyRemapped() {
172 $result = $this->subject->SELECTquery('*', 'tt_news_cat_mm', 'IFNULL(tt_news_cat_mm.uid_foreign,0) IN (21,22)');
173 $expected = 'SELECT * FROM "tt_news_cat_mm" WHERE ISNULL("tt_news_cat_mm"."uid_foreign", 0) IN (21,22)';
174 $this->assertEquals($expected, $this->cleanSql($result));
175 }
176
177 }