[FEATURE] Expand db function searchQuery to handle AND and OR constraints
[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 * Copyright notice
6 *
7 * (c) 2010-2011 Ernesto Baschny (ernst@cron-it.de)
8 * All rights reserved
9 *
10 * This script is part of the TYPO3 project. The TYPO3 project is
11 * free software; you can redistribute it and/or modify
12 * it under the terms of the GNU General Public License as published by
13 * the Free Software Foundation; either version 2 of the License, or
14 * (at your option) any later version.
15 *
16 * The GNU General Public License can be found at
17 * http://www.gnu.org/copyleft/gpl.html.
18 *
19 * This script is distributed in the hope that it will be useful,
20 * but WITHOUT ANY WARRANTY; without even the implied warranty of
21 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
22 * GNU General Public License for more details.
23 *
24 * This copyright notice MUST APPEAR in all copies of the script!
25 ***************************************************************/
26
27 /**
28 * Testcase for TYPO3\CMS\Core\Database\DatabaseConnection
29 *
30 * @author Ernesto Baschny <ernst@cron-it.de>
31 */
32 class DatabaseConnectionTest extends \TYPO3\CMS\Core\Tests\UnitTestCase {
33
34 /**
35 * @var \TYPO3\CMS\Core\Database\DatabaseConnection
36 */
37 private $fixture = NULL;
38
39 private $testTable;
40
41 public function setUp() {
42 $this->fixture = $GLOBALS['TYPO3_DB'];
43 $this->testTable = 'test_t3lib_dbtest';
44 $this->fixture->sql_query('CREATE TABLE ' . $this->testTable . ' (
45 id int(11) unsigned NOT NULL auto_increment,
46 fieldblob mediumblob,
47 PRIMARY KEY (id)
48 ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
49 ');
50 }
51
52 public function tearDown() {
53 $this->fixture->sql_query('DROP TABLE ' . $this->testTable . ';');
54 unset($this->fixture);
55 }
56
57 //////////////////////////////////////////////////
58 // Write/Read tests for charsets and binaries
59 //////////////////////////////////////////////////
60 /**
61 * @test
62 */
63 public function storedFullAsciiRangeReturnsSameData() {
64 $binaryString = '';
65 for ($i = 0; $i < 256; $i++) {
66 $binaryString .= chr($i);
67 }
68 $this->fixture->exec_INSERTquery($this->testTable, array('fieldblob' => $binaryString));
69 $id = $this->fixture->sql_insert_id();
70 $entry = $this->fixture->exec_SELECTgetRows('fieldblob', $this->testTable, 'id = ' . $id);
71 $this->assertEquals($binaryString, $entry[0]['fieldblob']);
72 }
73
74 /**
75 * @test
76 */
77 public function storedGzipCompressedDataReturnsSameData() {
78 $testStringWithBinary = @gzcompress('sdfkljer4587');
79 $this->fixture->exec_INSERTquery($this->testTable, array('fieldblob' => $testStringWithBinary));
80 $id = $this->fixture->sql_insert_id();
81 $entry = $this->fixture->exec_SELECTgetRows('fieldblob', $this->testTable, 'id = ' . $id);
82 $this->assertEquals($testStringWithBinary, $entry[0]['fieldblob']);
83 }
84
85 ////////////////////////////////
86 // Tests concerning listQuery
87 ////////////////////////////////
88 /**
89 * @test
90 * @see http://bugs.typo3.org/view.php?id=15211
91 */
92 public function listQueryWithIntegerCommaAsValue() {
93 // Note: 44 = ord(',')
94 $this->assertEquals($this->fixture->listQuery('dummy', 44, 'table'), $this->fixture->listQuery('dummy', '44', 'table'));
95 }
96
97 ////////////////////////////////
98 // Tests concerning searchQuery
99 ////////////////////////////////
100
101 /**
102 * Data provider for searchQueryCreatesQuery
103 *
104 * @return array
105 */
106 public function searchQueryDataProvider() {
107 return array(
108 'One search word in one field' => array(
109 '(pages.title LIKE \'%TYPO3%\')',
110 array('TYPO3'),
111 array('title'),
112 'pages',
113 'AND'
114 ),
115
116 'One search word in multiple fields' => array(
117 '(pages.title LIKE \'%TYPO3%\' OR pages.keyword LIKE \'%TYPO3%\' OR pages.description LIKE \'%TYPO3%\')',
118 array('TYPO3'),
119 array('title', 'keyword', 'description'),
120 'pages',
121 'AND'
122 ),
123
124 'Multiple search words in one field with AND constraint' => array(
125 '(pages.title LIKE \'%TYPO3%\') AND (pages.title LIKE \'%is%\') AND (pages.title LIKE \'%great%\')',
126 array('TYPO3', 'is', 'great'),
127 array('title'),
128 'pages',
129 'AND'
130 ),
131
132 'Multiple search words in one field with OR constraint' => array(
133 '(pages.title LIKE \'%TYPO3%\') OR (pages.title LIKE \'%is%\') OR (pages.title LIKE \'%great%\')',
134 array('TYPO3', 'is', 'great'),
135 array('title'),
136 'pages',
137 'OR'
138 ),
139
140 'Multiple search words in multiple fields with AND constraint' => array(
141 '(pages.title LIKE \'%TYPO3%\' OR pages.keywords LIKE \'%TYPO3%\' OR pages.description LIKE \'%TYPO3%\') AND ' .
142 '(pages.title LIKE \'%is%\' OR pages.keywords LIKE \'%is%\' OR pages.description LIKE \'%is%\') AND ' .
143 '(pages.title LIKE \'%great%\' OR pages.keywords LIKE \'%great%\' OR pages.description LIKE \'%great%\')',
144 array('TYPO3', 'is', 'great'),
145 array('title', 'keywords', 'description'),
146 'pages',
147 'AND'
148 ),
149
150 'Multiple search words in multiple fields with OR constraint' => array(
151 '(pages.title LIKE \'%TYPO3%\' OR pages.keywords LIKE \'%TYPO3%\' OR pages.description LIKE \'%TYPO3%\') OR ' .
152 '(pages.title LIKE \'%is%\' OR pages.keywords LIKE \'%is%\' OR pages.description LIKE \'%is%\') OR ' .
153 '(pages.title LIKE \'%great%\' OR pages.keywords LIKE \'%great%\' OR pages.description LIKE \'%great%\')',
154 array('TYPO3', 'is', 'great'),
155 array('title', 'keywords', 'description'),
156 'pages',
157 'OR'
158 ),
159 );
160 }
161
162 /**
163 * @test
164 * @dataProvider searchQueryDataProvider
165 */
166 public function searchQueryCreatesQuery($expectedResult, $searchWords, $fields, $table, $constraint) {
167 $this->assertSame($expectedResult, $this->fixture->searchQuery($searchWords, $fields, $table, $constraint));
168 }
169
170 /////////////////////////////////////////////////
171 // Tests concerning escapeStringForLikeComparison
172 /////////////////////////////////////////////////
173 /**
174 * @test
175 */
176 public function escapeStringForLikeComparison() {
177 $this->assertEquals('foo\\_bar\\%', $this->fixture->escapeStrForLike('foo_bar%', 'table'));
178 }
179
180 /////////////////////////////////////////////////
181 // Tests concerning stripOrderByForOrderByKeyword
182 /////////////////////////////////////////////////
183
184
185 /**
186 * Data Provider for stripGroupByForGroupByKeyword()
187 *
188 * @see stripOrderByForOrderByKeyword()
189 * @return array
190 */
191 public function stripOrderByForOrderByKeywordDataProvider() {
192 return array(
193 'single ORDER BY' => array('ORDER BY name, tstamp', 'name, tstamp'),
194 'single ORDER BY in lower case' => array('order by name, tstamp', 'name, tstamp'),
195 'ORDER BY with additional space behind' => array('ORDER BY name, tstamp', 'name, tstamp'),
196 'ORDER BY without space between the words' => array('ORDERBY name, tstamp', 'name, tstamp'),
197 'ORDER BY added twice' => array('ORDER BY ORDER BY name, tstamp', 'name, tstamp'),
198 'ORDER BY added twice without spaces in the first occurrence' => array('ORDERBY ORDER BY name, tstamp', 'name, tstamp'),
199 'ORDER BY added twice without spaces in the second occurrence' => array('ORDER BYORDERBY name, tstamp', 'name, tstamp'),
200 'ORDER BY added twice without spaces' => array('ORDERBYORDERBY name, tstamp', 'name, tstamp'),
201 'ORDER BY added twice without spaces afterwards' => array('ORDERBYORDERBYname, tstamp', 'name, tstamp'),
202 );
203 }
204
205 /**
206 * @test
207 * @dataProvider stripOrderByForOrderByKeywordDataProvider
208 * @param string $orderByClause The clause to test
209 * @param string $expectedResult The expected result
210 * @return void
211 */
212 public function stripOrderByForOrderByKeyword($orderByClause, $expectedResult) {
213 $strippedQuery = $this->fixture->stripOrderBy($orderByClause);
214 $this->assertEquals($expectedResult, $strippedQuery);
215 }
216
217 /////////////////////////////////////////////////
218 // Tests concerning stripGroupByForGroupByKeyword
219 /////////////////////////////////////////////////
220
221 /**
222 * Data Provider for stripGroupByForGroupByKeyword()
223 *
224 * @see stripGroupByForGroupByKeyword()
225 * @return array
226 */
227 public function stripGroupByForGroupByKeywordDataProvider() {
228 return array(
229 'single GROUP BY' => array('GROUP BY name, tstamp', 'name, tstamp'),
230 'single GROUP BY in lower case' => array('group by name, tstamp', 'name, tstamp'),
231 'GROUP BY with additional space behind' => array('GROUP BY name, tstamp', 'name, tstamp'),
232 'GROUP BY without space between the words' => array('GROUPBY name, tstamp', 'name, tstamp'),
233 'GROUP BY added twice' => array('GROUP BY GROUP BY name, tstamp', 'name, tstamp'),
234 'GROUP BY added twice without spaces in the first occurrence' => array('GROUPBY GROUP BY name, tstamp', 'name, tstamp'),
235 'GROUP BY added twice without spaces in the second occurrence' => array('GROUP BYGROUPBY name, tstamp', 'name, tstamp'),
236 'GROUP BY added twice without spaces' => array('GROUPBYGROUPBY name, tstamp', 'name, tstamp'),
237 'GROUP BY added twice without spaces afterwards' => array('GROUPBYGROUPBYname, tstamp', 'name, tstamp'),
238 );
239 }
240
241 /**
242 * @test
243 * @dataProvider stripGroupByForGroupByKeywordDataProvider
244 * @param string $groupByClause The clause to test
245 * @param string $expectedResult The expected result
246 * @return void
247 */
248 public function stripGroupByForGroupByKeyword($groupByClause, $expectedResult) {
249 $strippedQuery = $this->fixture->stripGroupBy($groupByClause);
250 $this->assertEquals($expectedResult, $strippedQuery);
251 }
252
253 }
254
255 ?>