[FEATURE] Add SELECT_mm_query to DatabaseConnection
[Packages/TYPO3.CMS.git] / typo3 / sysext / core / Tests / Unit / Database / DatabaseConnectionTest.php
1 <?php
2 namespace TYPO3\CMS\Core\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 */
21 class DatabaseConnectionTest extends \TYPO3\CMS\Core\Tests\UnitTestCase {
22
23 //////////////////////////////////////////////////
24 // Write/Read tests for charsets and binaries
25 //////////////////////////////////////////////////
26
27 /**
28 * @test
29 */
30 public function storedFullAsciiRangeCallsLinkObjectWithGivenData() {
31 $binaryString = '';
32 for ($i = 0; $i < 256; $i++) {
33 $binaryString .= chr($i);
34 }
35
36 /** @var \TYPO3\CMS\Core\Database\DatabaseConnection|\PHPUnit_Framework_MockObject_MockObject|\TYPO3\CMS\Core\Tests\AccessibleObjectInterface $subject */
37 $subject = $this->getAccessibleMock(\TYPO3\CMS\Core\Database\DatabaseConnection::class, array('fullQuoteStr'), array(), '', FALSE);
38 $subject->_set('isConnected', TRUE);
39 $subject
40 ->expects($this->any())
41 ->method('fullQuoteStr')
42 ->will($this->returnCallback(function ($data) {
43 return $data;
44 }));
45 $mysqliMock = $this->getMock('mysqli');
46 $mysqliMock
47 ->expects($this->once())
48 ->method('query')
49 ->with('INSERT INTO aTable (fieldblob) VALUES (' . $binaryString . ')');
50 $subject->_set('link', $mysqliMock);
51
52 $subject->exec_INSERTquery('aTable', array('fieldblob' => $binaryString));
53 }
54
55 /**
56 * @test
57 */
58 public function storedGzipCompressedDataReturnsSameData() {
59 $testStringWithBinary = @gzcompress('sdfkljer4587');
60
61 /** @var \TYPO3\CMS\Core\Database\DatabaseConnection|\PHPUnit_Framework_MockObject_MockObject|\TYPO3\CMS\Core\Tests\AccessibleObjectInterface $subject */
62 $subject = $this->getAccessibleMock(\TYPO3\CMS\Core\Database\DatabaseConnection::class, array('fullQuoteStr'), array(), '', FALSE);
63 $subject->_set('isConnected', TRUE);
64 $subject
65 ->expects($this->any())
66 ->method('fullQuoteStr')
67 ->will($this->returnCallback(function ($data) {
68 return $data;
69 }));
70 $mysqliMock = $this->getMock('mysqli');
71 $mysqliMock
72 ->expects($this->once())
73 ->method('query')
74 ->with('INSERT INTO aTable (fieldblob) VALUES (' . $testStringWithBinary . ')');
75 $subject->_set('link', $mysqliMock);
76
77 $subject->exec_INSERTquery('aTable', array('fieldblob' => $testStringWithBinary));
78 }
79
80
81 ////////////////////////////////
82 // Tests concerning listQuery
83 ////////////////////////////////
84
85 /**
86 * @test
87 * @see http://forge.typo3.org/issues/23253
88 */
89 public function listQueryWithIntegerCommaAsValue() {
90 /** @var \TYPO3\CMS\Core\Database\DatabaseConnection|\PHPUnit_Framework_MockObject_MockObject|\TYPO3\CMS\Core\Tests\AccessibleObjectInterface $subject */
91 $subject = $this->getAccessibleMock(\TYPO3\CMS\Core\Database\DatabaseConnection::class, array('quoteStr'), array(), '', FALSE);
92 $subject->_set('isConnected', TRUE);
93 $subject
94 ->expects($this->any())
95 ->method('quoteStr')
96 ->will($this->returnCallback(function ($data) {
97 return $data;
98 }));
99 // Note: 44 = ord(',')
100 $this->assertEquals($subject->listQuery('dummy', 44, 'table'), $subject->listQuery('dummy', '44', 'table'));
101 }
102
103 /**
104 * @test
105 * @expectedException \InvalidArgumentException
106 */
107 public function listQueryThrowsExceptionIfValueContainsComma() {
108 /** @var \TYPO3\CMS\Core\Database\DatabaseConnection|\PHPUnit_Framework_MockObject_MockObject|\TYPO3\CMS\Core\Tests\AccessibleObjectInterface $subject */
109 $subject = $this->getAccessibleMock(\TYPO3\CMS\Core\Database\DatabaseConnection::class, array('quoteStr'), array(), '', FALSE);
110 $subject->_set('isConnected', TRUE);
111 $subject->listQuery('aField', 'foo,bar', 'aTable');
112 }
113
114
115 ////////////////////////////////
116 // Tests concerning searchQuery
117 ////////////////////////////////
118
119 /**
120 * Data provider for searchQueryCreatesQuery
121 *
122 * @return array
123 */
124 public function searchQueryDataProvider() {
125 return array(
126 'One search word in one field' => array(
127 '(pages.title LIKE \'%TYPO3%\')',
128 array('TYPO3'),
129 array('title'),
130 'pages',
131 'AND'
132 ),
133
134 'One search word with special chars (for like)' => array(
135 '(pages.title LIKE \'%TYPO3\\_100\\%%\')',
136 array('TYPO3_100%'),
137 array('title'),
138 'pages',
139 'AND'
140 ),
141
142 'One search word in multiple fields' => array(
143 '(pages.title LIKE \'%TYPO3%\' OR pages.keyword LIKE \'%TYPO3%\' OR pages.description LIKE \'%TYPO3%\')',
144 array('TYPO3'),
145 array('title', 'keyword', 'description'),
146 'pages',
147 'AND'
148 ),
149
150 'Multiple search words in one field with AND constraint' => array(
151 '(pages.title LIKE \'%TYPO3%\') AND (pages.title LIKE \'%is%\') AND (pages.title LIKE \'%great%\')',
152 array('TYPO3', 'is', 'great'),
153 array('title'),
154 'pages',
155 'AND'
156 ),
157
158 'Multiple search words in one field with OR constraint' => array(
159 '(pages.title LIKE \'%TYPO3%\') OR (pages.title LIKE \'%is%\') OR (pages.title LIKE \'%great%\')',
160 array('TYPO3', 'is', 'great'),
161 array('title'),
162 'pages',
163 'OR'
164 ),
165
166 'Multiple search words in multiple fields with AND constraint' => array(
167 '(pages.title LIKE \'%TYPO3%\' OR pages.keywords LIKE \'%TYPO3%\' OR pages.description LIKE \'%TYPO3%\') AND ' .
168 '(pages.title LIKE \'%is%\' OR pages.keywords LIKE \'%is%\' OR pages.description LIKE \'%is%\') AND ' .
169 '(pages.title LIKE \'%great%\' OR pages.keywords LIKE \'%great%\' OR pages.description LIKE \'%great%\')',
170 array('TYPO3', 'is', 'great'),
171 array('title', 'keywords', 'description'),
172 'pages',
173 'AND'
174 ),
175
176 'Multiple search words in multiple fields with OR constraint' => array(
177 '(pages.title LIKE \'%TYPO3%\' OR pages.keywords LIKE \'%TYPO3%\' OR pages.description LIKE \'%TYPO3%\') OR ' .
178 '(pages.title LIKE \'%is%\' OR pages.keywords LIKE \'%is%\' OR pages.description LIKE \'%is%\') OR ' .
179 '(pages.title LIKE \'%great%\' OR pages.keywords LIKE \'%great%\' OR pages.description LIKE \'%great%\')',
180 array('TYPO3', 'is', 'great'),
181 array('title', 'keywords', 'description'),
182 'pages',
183 'OR'
184 ),
185 );
186 }
187
188 /**
189 * @test
190 * @dataProvider searchQueryDataProvider
191 */
192 public function searchQueryCreatesQuery($expectedResult, $searchWords, $fields, $table, $constraint) {
193 /** @var \TYPO3\CMS\Core\Database\DatabaseConnection|\PHPUnit_Framework_MockObject_MockObject $subject */
194 $subject = $this->getMock(\TYPO3\CMS\Core\Database\DatabaseConnection::class, array('quoteStr'), array(), '', FALSE);
195 $subject
196 ->expects($this->any())
197 ->method('quoteStr')
198 ->will($this->returnCallback(function ($data) {
199 return $data;
200 }));
201
202 $this->assertSame($expectedResult, $subject->searchQuery($searchWords, $fields, $table, $constraint));
203 }
204
205
206 /////////////////////////////////////////////////
207 // Tests concerning escapeStringForLikeComparison
208 /////////////////////////////////////////////////
209
210 /**
211 * @test
212 */
213 public function escapeStringForLikeComparison() {
214 /** @var \TYPO3\CMS\Core\Database\DatabaseConnection|\PHPUnit_Framework_MockObject_MockObject $subject */
215 $subject = $this->getMock(\TYPO3\CMS\Core\Database\DatabaseConnection::class, array('dummy'), array(), '', FALSE);
216 $this->assertEquals('foo\\_bar\\%', $subject->escapeStrForLike('foo_bar%', 'table'));
217 }
218
219
220 /////////////////////////////////////////////////
221 // Tests concerning stripOrderByForOrderByKeyword
222 /////////////////////////////////////////////////
223
224 /**
225 * Data Provider for stripGroupByForGroupByKeyword()
226 *
227 * @see stripOrderByForOrderByKeyword()
228 * @return array
229 */
230 public function stripOrderByForOrderByKeywordDataProvider() {
231 return array(
232 'single ORDER BY' => array('ORDER BY name, tstamp', 'name, tstamp'),
233 'single ORDER BY in lower case' => array('order by name, tstamp', 'name, tstamp'),
234 'ORDER BY with additional space behind' => array('ORDER BY name, tstamp', 'name, tstamp'),
235 'ORDER BY without space between the words' => array('ORDERBY name, tstamp', 'name, tstamp'),
236 'ORDER BY added twice' => array('ORDER BY ORDER BY name, tstamp', 'name, tstamp'),
237 'ORDER BY added twice without spaces in the first occurrence' => array('ORDERBY ORDER BY name, tstamp', 'name, tstamp'),
238 'ORDER BY added twice without spaces in the second occurrence' => array('ORDER BYORDERBY name, tstamp', 'name, tstamp'),
239 'ORDER BY added twice without spaces' => array('ORDERBYORDERBY name, tstamp', 'name, tstamp'),
240 'ORDER BY added twice without spaces afterwards' => array('ORDERBYORDERBYname, tstamp', 'name, tstamp'),
241 );
242 }
243
244 /**
245 * @test
246 * @dataProvider stripOrderByForOrderByKeywordDataProvider
247 * @param string $orderByClause The clause to test
248 * @param string $expectedResult The expected result
249 * @return void
250 */
251 public function stripOrderByForOrderByKeyword($orderByClause, $expectedResult) {
252 /** @var \TYPO3\CMS\Core\Database\DatabaseConnection|\PHPUnit_Framework_MockObject_MockObject $subject */
253 $subject = $this->getMock(\TYPO3\CMS\Core\Database\DatabaseConnection::class, array('dummy'), array(), '', FALSE);
254 $strippedQuery = $subject->stripOrderBy($orderByClause);
255 $this->assertEquals($expectedResult, $strippedQuery);
256 }
257
258
259 /////////////////////////////////////////////////
260 // Tests concerning stripGroupByForGroupByKeyword
261 /////////////////////////////////////////////////
262
263 /**
264 * Data Provider for stripGroupByForGroupByKeyword()
265 *
266 * @see stripGroupByForGroupByKeyword()
267 * @return array
268 */
269 public function stripGroupByForGroupByKeywordDataProvider() {
270 return array(
271 'single GROUP BY' => array('GROUP BY name, tstamp', 'name, tstamp'),
272 'single GROUP BY in lower case' => array('group by name, tstamp', 'name, tstamp'),
273 'GROUP BY with additional space behind' => array('GROUP BY name, tstamp', 'name, tstamp'),
274 'GROUP BY without space between the words' => array('GROUPBY name, tstamp', 'name, tstamp'),
275 'GROUP BY added twice' => array('GROUP BY GROUP BY name, tstamp', 'name, tstamp'),
276 'GROUP BY added twice without spaces in the first occurrence' => array('GROUPBY GROUP BY name, tstamp', 'name, tstamp'),
277 'GROUP BY added twice without spaces in the second occurrence' => array('GROUP BYGROUPBY name, tstamp', 'name, tstamp'),
278 'GROUP BY added twice without spaces' => array('GROUPBYGROUPBY name, tstamp', 'name, tstamp'),
279 'GROUP BY added twice without spaces afterwards' => array('GROUPBYGROUPBYname, tstamp', 'name, tstamp'),
280 );
281 }
282
283 /**
284 * @test
285 * @dataProvider stripGroupByForGroupByKeywordDataProvider
286 * @param string $groupByClause The clause to test
287 * @param string $expectedResult The expected result
288 * @return void
289 */
290 public function stripGroupByForGroupByKeyword($groupByClause, $expectedResult) {
291 /** @var \TYPO3\CMS\Core\Database\DatabaseConnection|\PHPUnit_Framework_MockObject_MockObject $subject */
292 $subject = $this->getMock(\TYPO3\CMS\Core\Database\DatabaseConnection::class, array('dummy'), array(), '', FALSE);
293 $strippedQuery = $subject->stripGroupBy($groupByClause);
294 $this->assertEquals($expectedResult, $strippedQuery);
295 }
296
297
298 /////////////////////////////////////////////////
299 // Tests concerning stripOrderByForOrderByKeyword
300 /////////////////////////////////////////////////
301
302 /**
303 * Data Provider for stripGroupByForGroupByKeyword()
304 *
305 * @see stripOrderByForOrderByKeyword()
306 * @return array
307 */
308 public function cleanIntArrayDataProvider() {
309 return array(
310 'simple array' => array(
311 array(1, 2, 3),
312 array(1, 2, 3)
313 ),
314 'string array' => array(
315 array('2', '4', '8'),
316 array(2, 4, 8)
317 ),
318 'string array with letters #1' => array(
319 array('3', '6letters', '12'),
320 array(3, 6, 12)
321 ),
322 'string array with letters #2' => array(
323 array('3', 'letters6', '12'),
324 array(3, 0, 12)
325 ),
326 'string array with letters #3' => array(
327 array('3', '6letters4', '12'),
328 array(3, 6, 12)
329 ),
330 'associative array' => array(
331 array('apples' => 3, 'bananas' => 4, 'kiwis' => 9),
332 array('apples' => 3, 'bananas' => 4, 'kiwis' => 9)
333 ),
334 'associative string array' => array(
335 array('apples' => '1', 'bananas' => '5', 'kiwis' => '7'),
336 array('apples' => 1, 'bananas' => 5, 'kiwis' => 7)
337 ),
338 'associative string array with letters #1' => array(
339 array('apples' => '1', 'bananas' => 'no5', 'kiwis' => '7'),
340 array('apples' => 1, 'bananas' => 0, 'kiwis' => 7)
341 ),
342 'associative string array with letters #2' => array(
343 array('apples' => '1', 'bananas' => '5yes', 'kiwis' => '7'),
344 array('apples' => 1, 'bananas' => 5, 'kiwis' => 7)
345 ),
346 'associative string array with letters #3' => array(
347 array('apples' => '1', 'bananas' => '5yes9', 'kiwis' => '7'),
348 array('apples' => 1, 'bananas' => 5, 'kiwis' => 7)
349 ),
350 'multidimensional associative array' => array(
351 array('apples' => '1', 'bananas' => array(3, 4), 'kiwis' => '7'),
352 // intval(array(...)) is 1
353 // But by specification "cleanIntArray" should only get used on one-dimensional arrays
354 array('apples' => 1, 'bananas' => 1, 'kiwis' => 7)
355 ),
356 );
357 }
358
359 /**
360 * @test
361 * @dataProvider cleanIntArrayDataProvider
362 * @param array $exampleData The array to sanitize
363 * @param array $expectedResult The expected result
364 * @return void
365 */
366 public function cleanIntArray($exampleData, $expectedResult) {
367 /** @var \TYPO3\CMS\Core\Database\DatabaseConnection $subject */
368 $subject = new \TYPO3\CMS\Core\Database\DatabaseConnection();
369 $sanitizedArray = $subject->cleanIntArray($exampleData);
370 $this->assertEquals($expectedResult, $sanitizedArray);
371 }
372
373 /**
374 * @test
375 */
376 public function sqlForSelectMmQuery() {
377 $subject = new \TYPO3\CMS\Core\Database\DatabaseConnection();
378 $result = $subject->SELECT_mm_query('*', 'sys_category', 'sys_category_record_mm', 'tt_content', 'AND sys_category.uid = 1', '', 'sys_category.title DESC');
379 $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';
380 $this->assertEquals($expected, $result);
381 }
382
383 }