[BUGFIX] DBAL: Properly compile CAST statement after OR condition
[Packages/TYPO3.CMS.git] / typo3 / sysext / dbal / Tests / Unit / Database / DatabaseConnectionTest.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 use TYPO3\CMS\Core\Utility\GeneralUtility;
18
19 /**
20 * Test case
21 */
22 class DatabaseConnectionTest extends AbstractTestCase
23 {
24 /**
25 * @var \TYPO3\CMS\Dbal\Database\DatabaseConnection|\PHPUnit_Framework_MockObject_MockObject|\TYPO3\CMS\Core\Tests\AccessibleObjectInterface
26 */
27 protected $subject;
28
29 /**
30 * Set up
31 */
32 protected function setUp()
33 {
34 $GLOBALS['TYPO3_LOADED_EXT'] = array();
35
36 /** @var \TYPO3\CMS\Dbal\Database\DatabaseConnection|\PHPUnit_Framework_MockObject_MockObject|\TYPO3\CMS\Core\Tests\AccessibleObjectInterface $subject */
37 $subject = $this->getAccessibleMock(\TYPO3\CMS\Dbal\Database\DatabaseConnection::class, array('getFieldInfoCache'), array(), '', false);
38
39 // Disable caching
40 $mockCacheFrontend = $this->getMock(\TYPO3\CMS\Core\Cache\Frontend\PhpFrontend::class, array(), array(), '', false);
41 $subject->expects($this->any())->method('getFieldInfoCache')->will($this->returnValue($mockCacheFrontend));
42
43 // Inject SqlParser - Its logic is tested with the tests, too.
44 $sqlParser = $this->getAccessibleMock(\TYPO3\CMS\Dbal\Database\SqlParser::class, array('dummy'), array(), '', false);
45 $sqlParser->_set('databaseConnection', $subject);
46 $subject->SQLparser = $sqlParser;
47
48 // Mock away schema migration service from install tool
49 $installerSqlMock = $this->getMock(\TYPO3\CMS\Install\Service\SqlSchemaMigrationService::class, array('getFieldDefinitions_fileContent'), array(), '', false);
50 $installerSqlMock->expects($this->any())->method('getFieldDefinitions_fileContent')->will($this->returnValue(array()));
51 $subject->_set('installerSql', $installerSqlMock);
52
53 // Inject DBMS specifics
54 $subject->_set('dbmsSpecifics', GeneralUtility::makeInstance(\TYPO3\CMS\Dbal\Database\Specifics\NullSpecifics::class));
55
56 $subject->initialize();
57 $subject->lastHandlerKey = '_DEFAULT';
58
59 $this->subject = $subject;
60 }
61
62 /**
63 * Creates a fake extension with a given table definition.
64 *
65 * @param string $tableDefinition SQL script to create the extension's tables
66 * @throws \RuntimeException
67 * @return void
68 */
69 protected function createFakeExtension($tableDefinition)
70 {
71 // Prepare a fake extension configuration
72 $ext_tables = GeneralUtility::tempnam('ext_tables');
73 if (!GeneralUtility::writeFile($ext_tables, $tableDefinition)) {
74 throw new \RuntimeException('Can\'t write temporary ext_tables file.');
75 }
76 $this->testFilesToDelete[] = $ext_tables;
77 $GLOBALS['TYPO3_LOADED_EXT'] = array(
78 'test_dbal' => array(
79 'ext_tables.sql' => $ext_tables
80 )
81 );
82 // Append our test table to the list of existing tables
83 $this->subject->initialize();
84 }
85
86 /**
87 * @test
88 */
89 public function tableWithMappingIsDetected()
90 {
91 $dbalConfiguration = array(
92 'mapping' => array(
93 'cf_cache_hash' => array(),
94 ),
95 );
96
97 /** @var \TYPO3\CMS\Dbal\Database\DatabaseConnection|\PHPUnit_Framework_MockObject_MockObject|\TYPO3\CMS\Core\Tests\AccessibleObjectInterface $subject */
98 $subject = $this->getAccessibleMock(\TYPO3\CMS\Dbal\Database\DatabaseConnection::class, array('getFieldInfoCache'), array(), '', false);
99
100 $mockCacheFrontend = $this->getMock(\TYPO3\CMS\Core\Cache\Frontend\PhpFrontend::class, array(), array(), '', false);
101 $subject->expects($this->any())->method('getFieldInfoCache')->will($this->returnValue($mockCacheFrontend));
102
103 $sqlParser = $this->getAccessibleMock(\TYPO3\CMS\Dbal\Database\SqlParser::class, array('dummy'), array(), '', false);
104 $sqlParser->_set('databaseConnection', $subject);
105 $subject->SQLparser = $sqlParser;
106
107 $installerSqlMock = $this->getMock(\TYPO3\CMS\Install\Service\SqlSchemaMigrationService::class, array(), array(), '', false);
108 $subject->_set('installerSql', $installerSqlMock);
109 $schemaMigrationResult = array(
110 'cf_cache_pages' => array(),
111 );
112 $installerSqlMock->expects($this->once())->method('getFieldDefinitions_fileContent')->will($this->returnValue($schemaMigrationResult));
113
114 $subject->conf = $dbalConfiguration;
115 $subject->initialize();
116 $subject->lastHandlerKey = '_DEFAULT';
117
118 $this->assertFalse($subject->_call('map_needMapping', 'cf_cache_pages'));
119 $cfCacheHashNeedsMapping = $subject->_call('map_needMapping', 'cf_cache_hash');
120 $this->assertEquals('cf_cache_hash', $cfCacheHashNeedsMapping[0]['table']);
121 }
122
123 /**
124 * @test
125 * @see https://forge.typo3.org/issues/67067
126 */
127 public function adminGetTablesReturnsArrayWithNameKey()
128 {
129 $handlerMock = $this->getMock('\ADODB_mock', array('MetaTables'), array(), '', false);
130 $handlerMock->expects($this->any())->method('MetaTables')->will($this->returnValue(array('cf_cache_hash')));
131 $this->subject->handlerCfg['_DEFAULT']['type'] = 'adodb';
132 $this->subject->handlerInstance['_DEFAULT'] = $handlerMock;
133
134 $actual = $this->subject->admin_get_tables();
135 $expected = array('cf_cache_hash' => array('Name' => 'cf_cache_hash'));
136 $this->assertSame($expected, $actual);
137 }
138
139 /**
140 * @test
141 * @see http://forge.typo3.org/issues/21502
142 */
143 public function concatCanBeParsedAfterLikeOperator()
144 {
145 $result = $this->subject->SELECTquery('*', 'sys_refindex, tx_dam_file_tracking', 'sys_refindex.tablename = \'tx_dam_file_tracking\'' . ' AND sys_refindex.ref_string LIKE CONCAT(tx_dam_file_tracking.file_path, tx_dam_file_tracking.file_name)');
146 $expected = 'SELECT * FROM sys_refindex, tx_dam_file_tracking WHERE sys_refindex.tablename = \'tx_dam_file_tracking\'';
147 $expected .= ' AND sys_refindex.ref_string LIKE CONCAT(tx_dam_file_tracking.file_path, tx_dam_file_tracking.file_name)';
148 $this->assertEquals($expected, $this->cleanSql($result));
149 }
150
151 /**
152 * @test
153 * @see http://forge.typo3.org/issues/20346
154 */
155 public function floatNumberCanBeStoredInDatabase()
156 {
157 $this->createFakeExtension('
158 CREATE TABLE tx_test_dbal (
159 foo double default \'0\',
160 foobar int default \'0\'
161 );
162 ');
163 $data = array(
164 'foo' => 99.12,
165 'foobar' => -120
166 );
167 $result = $this->subject->INSERTquery('tx_test_dbal', $data);
168 $expected = 'INSERT INTO tx_test_dbal ( foo, foobar ) VALUES ( \'99.12\', \'-120\' )';
169 $this->assertEquals($expected, $this->cleanSql($result));
170 }
171
172 /**
173 * @test
174 * @see http://forge.typo3.org/issues/20427
175 */
176 public function positive64BitIntegerIsSupported()
177 {
178 if (!is_int(9223372036854775806)) {
179 $this->markTestSkipped('Test skipped because running on 32 bit system.');
180 }
181 $this->createFakeExtension('
182 CREATE TABLE tx_test_dbal (
183 foo int default \'0\',
184 foobar bigint default \'0\'
185 );
186 ');
187 $data = array(
188 'foo' => 9223372036854775807,
189 'foobar' => 9223372036854775807
190 );
191 $result = $this->subject->INSERTquery('tx_test_dbal', $data);
192 $expected = 'INSERT INTO tx_test_dbal ( foo, foobar ) VALUES ( \'9223372036854775807\', \'9223372036854775807\' )';
193 $this->assertEquals($expected, $this->cleanSql($result));
194 }
195
196 /**
197 * @test
198 */
199 public function sqlForInsertWithMultipleRowsIsValid()
200 {
201 $fields = array('uid', 'pid', 'title', 'body');
202 $rows = array(
203 array('1', '2', 'Title #1', 'Content #1'),
204 array('3', '4', 'Title #2', 'Content #2'),
205 array('5', '6', 'Title #3', 'Content #3')
206 );
207 $result = $this->subject->INSERTmultipleRows('tt_content', $fields, $rows);
208 $expected = 'INSERT INTO tt_content (uid, pid, title, body) VALUES ';
209 $expected .= '(\'1\', \'2\', \'Title #1\', \'Content #1\'), ';
210 $expected .= '(\'3\', \'4\', \'Title #2\', \'Content #2\'), ';
211 $expected .= '(\'5\', \'6\', \'Title #3\', \'Content #3\')';
212 $this->assertEquals($expected, $this->cleanSql($result));
213 }
214
215 /**
216 * @test
217 */
218 public function sqlForSelectMmQuery()
219 {
220 $result = $this->subject->SELECT_mm_query('*', 'sys_category', 'sys_category_record_mm', 'tt_content', 'AND sys_category.uid = 1', '', 'sys_category.title DESC');
221 $expected = 'SELECT * FROM sys_category,sys_category_record_mm,tt_content WHERE sys_category.uid=sys_category_record_mm.uid_local AND tt_content.uid=sys_category_record_mm.uid_foreign AND sys_category.uid = 1 ORDER BY sys_category.title DESC';
222 $this->assertEquals($expected, $result);
223 }
224
225 /**
226 * @test
227 * @see http://forge.typo3.org/issues/16708
228 */
229 public function minFunctionAndInOperatorCanBeParsed()
230 {
231 $result = $this->subject->SELECTquery('*', 'pages', 'MIN(uid) IN (1,2,3,4)');
232 $expected = 'SELECT * FROM pages WHERE MIN(uid) IN (1,2,3,4)';
233 $this->assertEquals($expected, $this->cleanSql($result));
234 }
235
236 /**
237 * @test
238 * @see http://forge.typo3.org/issues/16708
239 */
240 public function maxFunctionAndInOperatorCanBeParsed()
241 {
242 $result = $this->subject->SELECTquery('*', 'pages', 'MAX(uid) IN (1,2,3,4)');
243 $expected = 'SELECT * FROM pages WHERE MAX(uid) IN (1,2,3,4)';
244 $this->assertEquals($expected, $this->cleanSql($result));
245 }
246
247 /**
248 * @test
249 * @see https://forge.typo3.org/issues/71979
250 */
251 public function canCompileCastOperatorWithOrComparator()
252 {
253 $result = $this->subject->SELECTquery('uid', 'sys_category', 'FIND_IN_SET(\'0\',parent) != 0 OR CAST(parent AS CHAR) = \'\'');
254 $expected = 'SELECT uid FROM sys_category WHERE FIND_IN_SET(\'0\',parent) != 0 OR CAST(parent AS CHAR) = \'\'';
255 $this->assertEquals($expected, $this->cleanSql($result));
256 }
257
258 /**
259 * @test
260 * @see http://forge.typo3.org/issues/21514
261 */
262 public function likeBinaryOperatorIsKept()
263 {
264 $result = $this->cleanSql($this->subject->SELECTquery('*', 'tt_content', 'bodytext LIKE BINARY \'test\''));
265 $expected = 'SELECT * FROM tt_content WHERE bodytext LIKE BINARY \'test\'';
266 $this->assertEquals($expected, $this->cleanSql($result));
267 }
268
269 /**
270 * @test
271 * @see http://forge.typo3.org/issues/21514
272 */
273 public function notLikeBinaryOperatorIsKept()
274 {
275 $result = $this->cleanSql($this->subject->SELECTquery('*', 'tt_content', 'bodytext NOT LIKE BINARY \'test\''));
276 $expected = 'SELECT * FROM tt_content WHERE bodytext NOT LIKE BINARY \'test\'';
277 $this->assertEquals($expected, $this->cleanSql($result));
278 }
279
280 ///////////////////////////////////////
281 // Tests concerning prepared queries
282 ///////////////////////////////////////
283 /**
284 * @test
285 * @see http://forge.typo3.org/issues/23374
286 */
287 public function similarNamedParametersAreProperlyReplaced()
288 {
289 $sql = 'SELECT * FROM cache WHERE tag = :tag1 OR tag = :tag10 OR tag = :tag100';
290 $parameterValues = array(
291 ':tag1' => 'tag-one',
292 ':tag10' => 'tag-two',
293 ':tag100' => 'tag-three'
294 );
295 $className = self::buildAccessibleProxy(\TYPO3\CMS\Core\Database\PreparedStatement::class);
296 $query = $sql;
297 $precompiledQueryParts = array();
298 $statement = new $className($sql, 'cache');
299 $statement->bindValues($parameterValues);
300 $parameters = $statement->_get('parameters');
301 $statement->_callRef('convertNamedPlaceholdersToQuestionMarks', $query, $parameters, $precompiledQueryParts);
302 $expectedQuery = 'SELECT * FROM cache WHERE tag = ? OR tag = ? OR tag = ?';
303 $expectedParameterValues = array(
304 0 => array(
305 'type' => \TYPO3\CMS\Core\Database\PreparedStatement::PARAM_STR,
306 'value' => 'tag-one',
307 ),
308 1 => array(
309 'type' => \TYPO3\CMS\Core\Database\PreparedStatement::PARAM_STR,
310 'value' => 'tag-two',
311 ),
312 2 => array(
313 'type' => \TYPO3\CMS\Core\Database\PreparedStatement::PARAM_STR,
314 'value' => 'tag-three',
315 ),
316 );
317 $this->assertEquals($expectedQuery, $query);
318 $this->assertEquals($expectedParameterValues, $parameters);
319 }
320
321 ///////////////////////////////////////
322 // Tests concerning indexes
323 ///////////////////////////////////////
324 /**
325 * @test
326 * @param string $indexSQL
327 * @param string $expected
328 * @dataProvider equivalentIndexDefinitionDataProvider
329 */
330 public function equivalentIndexDefinitionRemovesLengthInformation($indexSQL, $expected)
331 {
332 $result = $this->subject->getEquivalentIndexDefinition($indexSQL);
333 $this->assertSame($expected, $result);
334 }
335
336 /**
337 * @return array
338 */
339 public function equivalentIndexDefinitionDataProvider()
340 {
341 return array(
342 array('KEY (foo,bar(199))', 'KEY (foo,bar)'),
343 array('KEY (foo(199), bar)', 'KEY (foo, bar)'),
344 array('KEY (foo(199),bar(199))', 'KEY (foo,bar)'),
345 );
346 }
347 }