Fixed bug #12800: Add functionality to work with caching framework (support for ...
[Packages/TYPO3.CMS.git] / typo3 / sysext / dbal / tests / sqlparser_general_testcase.php
1 <?php
2 /***************************************************************
3 * Copyright notice
4 *
5 * (c) 2009 Xavier Perseguers <typo3@perseguers.ch>
6 * All rights reserved
7 *
8 * This script is part of the TYPO3 project. The TYPO3 project is
9 * free software; you can redistribute it and/or modify
10 * it under the terms of the GNU General Public License as published by
11 * the Free Software Foundation; either version 2 of the License, or
12 * (at your option) any later version.
13 *
14 * The GNU General Public License can be found at
15 * http://www.gnu.org/copyleft/gpl.html.
16 *
17 * This script is distributed in the hope that it will be useful,
18 * but WITHOUT ANY WARRANTY; without even the implied warranty of
19 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
20 * GNU General Public License for more details.
21 *
22 * This copyright notice MUST APPEAR in all copies of the script!
23 ***************************************************************/
24
25
26 require_once('BaseTestCase.php');
27
28 /**
29 * Testcase for class ux_t3lib_sqlparser
30 *
31 * $Id$
32 *
33 * @author Xavier Perseguers <typo3@perseguers.ch>
34 *
35 * @package TYPO3
36 * @subpackage dbal
37 */
38 class sqlparser_general_testcase extends BaseTestCase {
39
40 /**
41 * @var ux_t3lib_sqlparser (extended to make protected methods public)
42 */
43 protected $fixture;
44
45 /**
46 * Prepares the environment before running a test.
47 */
48 public function setUp() {
49 $className = self::buildAccessibleProxy('ux_t3lib_sqlparser');
50 $this->fixture = new $className;
51 }
52
53 /**
54 * Cleans up the environment after running a test.
55 */
56 public function tearDown() {
57 unset($this->fixture);
58 }
59
60 /**
61 * Cleans a SQL query.
62 *
63 * @param mixed $sql
64 * @return mixed (string or array)
65 */
66 private function cleanSql($sql) {
67 if (!is_string($sql)) {
68 return $sql;
69 }
70
71 $sql = str_replace("\n", ' ', $sql);
72 $sql = preg_replace('/\s+/', ' ', $sql);
73 return trim($sql);
74 }
75
76 /**
77 * @test
78 */
79 public function canExtractPartsOfAQuery() {
80 $parseString = "SELECT *\nFROM pages WHERE pid IN (1,2,3,4)";
81 $regex = '^SELECT[[:space:]]+(.*)[[:space:]]+';
82 $trimAll = TRUE;
83 $fields = $this->fixture->_callRef('nextPart', $parseString, $regex, $trimAll);
84
85 $this->assertEquals(
86 '*',
87 $fields
88 );
89 $this->assertEquals(
90 'FROM pages WHERE pid IN (1,2,3,4)',
91 $parseString
92 );
93
94 $regex = '^FROM ([^)]+) WHERE';
95 $table = $this->fixture->_callRef('nextPart', $parseString, $regex);
96
97 $this->assertEquals(
98 'pages',
99 $table
100 );
101 $this->assertEquals(
102 'pages WHERE pid IN (1,2,3,4)',
103 $parseString
104 );
105 }
106
107 /**
108 * @test
109 */
110 public function canGetIntegerValue() {
111 $parseString = '1024';
112 $value = $this->fixture->_callRef('getValue', $parseString);
113 $expected = array(1024);
114
115 $this->assertEquals($expected, $value);
116 }
117
118 /**
119 * @test
120 */
121 public function canGetStringValue() {
122 $parseString = '"some owner\\\' string"';
123 $value = $this->fixture->_callRef('getValue', $parseString);
124 $expected = array('some owner\' string', '"');
125
126 $this->assertEquals($expected, $value);
127 }
128
129 /**
130 * @test
131 */
132 public function canGetListOfValues() {
133 $parseString = '( 1, 2, 3 ,4)';
134 $operator = 'IN';
135 $values = $this->fixture->_callRef('getValue', $parseString, $operator);
136 $expected = array(
137 array(1),
138 array(2),
139 array(3),
140 array(4)
141 );
142
143 $this->assertEquals($expected, $values);
144 }
145
146 /**
147 * @test
148 */
149 public function parseWhereClauseReturnsArray() {
150 $parseString = 'uid IN (1,2) AND (starttime < ' . time() . ' OR cruser_id + 10 < 20)';
151 $where = $this->fixture->parseWhereClause($parseString);
152
153 $this->assertTrue(is_array($where), $where);
154 $this->assertTrue(empty($parseString), 'parseString is not empty');
155 }
156
157 /**
158 * @test
159 */
160 public function canSelectAllFieldsFromPages() {
161 $sql = 'SELECT * FROM pages';
162 $expected = $sql;
163 $actual = $this->cleanSql($this->fixture->debug_testSQL($sql));
164
165 $this->assertEquals($expected, $actual);
166 }
167
168 ///////////////////////////////////////
169 // Tests concerning JOINs
170 ///////////////////////////////////////
171
172 /**
173 * @test
174 */
175 public function parseFromTablesWithInnerJoinReturnsArray() {
176 $parseString = 'be_users INNER JOIN pages ON pages.cruser_id = be_users.uid';
177 $tables = $this->fixture->parseFromTables($parseString);
178
179 $this->assertTrue(is_array($tables), $tables);
180 $this->assertTrue(empty($parseString), 'parseString is not empty');
181 }
182
183 /**
184 * @test
185 */
186 public function parseFromTablesWithLeftOuterJoinReturnsArray() {
187 $parseString = 'be_users LEFT OUTER JOIN pages ON be_users.uid = pages.cruser_id';
188 $tables = $this->fixture->parseFromTables($parseString);
189
190 $this->assertTrue(is_array($tables), $tables);
191 $this->assertTrue(empty($parseString), 'parseString is not empty');
192 }
193
194 /**
195 * @test
196 * @see http://bugs.typo3.org/view.php?id=12596
197 */
198 public function parseFromTablesWithRightOuterJoinReturnsArray() {
199 $parseString = 'tx_powermail_fieldsets RIGHT JOIN tt_content ON tx_powermail_fieldsets.tt_content = tt_content.uid';
200 $tables = $this->fixture->parseFromTables($parseString);
201
202 $this->assertTrue(is_array($tables), $tables);
203 $this->assertTrue(empty($parseString), 'parseString is not empty');
204 }
205
206 /**
207 * @test
208 */
209 public function parseFromTablesWithMultipleJoinsReturnsArray() {
210 $parseString = 'be_users LEFT OUTER JOIN pages ON be_users.uid = pages.cruser_id INNER JOIN cache_pages cp ON cp.page_id = pages.uid';
211 $tables = $this->fixture->parseFromTables($parseString);
212
213 $this->assertTrue(is_array($tables), $tables);
214 $this->assertTrue(empty($parseString), 'parseString is not empty');
215 }
216
217 /**
218 * @test
219 * @see http://bugs.typo3.org/view.php?id=12596
220 */
221 public function parseFromTablesWithMultipleJoinsAndParenthesesReturnsArray() {
222 $parseString = 'tx_powermail_fieldsets RIGHT JOIN tt_content ON tx_powermail_fieldsets.tt_content = tt_content.uid LEFT JOIN tx_powermail_fields ON tx_powermail_fieldsets.uid = tx_powermail_fields.fieldset';
223 $tables = $this->fixture->parseFromTables($parseString);
224
225 $this->assertTrue(is_array($tables), $tables);
226 $this->assertTrue(empty($parseString), 'parseString is not empty');
227 }
228
229 /**
230 * @test
231 */
232 public function canUseInnerJoinInSelect() {
233 $sql = 'SELECT pages.uid, be_users.username FROM be_users INNER JOIN pages ON pages.cruser_id = be_users.uid';
234 $expected = 'SELECT pages.uid, be_users.username FROM be_users INNER JOIN pages ON pages.cruser_id=be_users.uid';
235 $actual = $this->cleanSql($this->fixture->debug_testSQL($sql));
236
237 $this->assertEquals($expected, $actual);
238 }
239
240 /**
241 * @test
242 */
243 public function canUseMultipleInnerJoinsInSelect() {
244 $sql = 'SELECT * FROM tt_news_cat INNER JOIN tt_news_cat_mm ON tt_news_cat.uid = tt_news_cat_mm.uid_foreign INNER JOIN tt_news ON tt_news.uid = tt_news_cat_mm.uid_local';
245 $expected = 'SELECT * FROM tt_news_cat INNER JOIN tt_news_cat_mm ON tt_news_cat.uid=tt_news_cat_mm.uid_foreign INNER JOIN tt_news ON tt_news.uid=tt_news_cat_mm.uid_local';
246 $actual = $this->cleanSql($this->fixture->debug_testSQL($sql));
247
248 $this->assertEquals($expected, $actual);
249 }
250
251 ///////////////////////////////////////
252 // Tests concerning DB management
253 ///////////////////////////////////////
254
255 /**
256 * @test
257 * @see http://bugs.typo3.org/view.php?id=4466
258 */
259 public function indexMayContainALengthRestriction() {
260 $parseString = '
261 CREATE TABLE tx_realurl_uniqalias (
262 uid int(11) NOT NULL auto_increment,
263 tstamp int(11) DEFAULT \'0\' NOT NULL,
264 tablename varchar(60) DEFAULT \'\' NOT NULL,
265 field_alias varchar(255) DEFAULT \'\' NOT NULL,
266 field_id varchar(60) DEFAULT \'\' NOT NULL,
267 value_alias varchar(255) DEFAULT \'\' NOT NULL,
268 value_id int(11) DEFAULT \'0\' NOT NULL,
269 lang int(11) DEFAULT \'0\' NOT NULL,
270 expire int(11) DEFAULT \'0\' NOT NULL,
271
272 PRIMARY KEY (uid),
273 KEY tablename (tablename),
274 KEY bk_realurl01 (field_alias,field_id,value_id,lang,expire),
275 KEY bk_realurl02 (tablename,field_alias,field_id,value_alias(220),expire)
276 );
277 ';
278
279 $createTables = $this->fixture->_callRef('parseCREATETABLE', $parseString);
280 $this->assertTrue(is_array($createTables), $createTables);
281 }
282
283 ///////////////////////////////////////
284 // Tests concerning subqueries
285 ///////////////////////////////////////
286
287 /**
288 * @test
289 * @see http://bugs.typo3.org/view.php?id=12758
290 */
291 public function inWhereClauseSupportsSubquery() {
292 $parseString = 'process_id IN (SELECT process_id FROM tx_crawler_process WHERE active=0 AND deleted=0)';
293 $whereParts = $this->fixture->parseWhereClause($parseString);
294
295 $this->assertTrue(is_array($whereParts), $whereParts);
296 $this->assertTrue(empty($parseString), 'parseString is not empty');
297 }
298
299 /**
300 * @test
301 * @see http://bugs.typo3.org/view.php?id=12758
302 */
303 public function inWhereClauseWithSubqueryIsProperlyCompiled() {
304 $sql = 'SELECT * FROM tx_crawler_queue WHERE process_id IN (SELECT process_id FROM tx_crawler_process WHERE active=0 AND deleted=0)';
305 $expected = 'SELECT * FROM tx_crawler_queue WHERE process_id IN (SELECT process_id FROM tx_crawler_process WHERE active = 0 AND deleted = 0)';
306 $actual = $this->cleanSql($this->fixture->debug_testSQL($sql));
307
308 $this->assertEquals($expected, $actual);
309 }
310 }
311 ?>