Add DBAL 1.0.1 to TYPO3core. Do NOT make changes inside! See misc/core_svn_rules...
[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: sqlparser_general_testcase.php 26966 2009-11-25 15:20:04Z stucki $
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 parseFromTablesWithInnerJoinReturnsArray() {
150 $parseString = 'be_users INNER JOIN pages ON pages.cruser_id = be_users.uid';
151 $tables = $this->fixture->parseFromTables($parseString);
152
153 $this->assertTrue(is_array($tables), $tables);
154 $this->assertTrue(empty($parseString), 'parseString is not empty');
155 }
156
157 /**
158 * @test
159 */
160 public function parseFromTablesWithLeftOuterJoinReturnsArray() {
161 $parseString = 'be_users LEFT OUTER JOIN pages ON be_users.uid = pages.cruser_id';
162 $tables = $this->fixture->parseFromTables($parseString);
163
164 $this->assertTrue(is_array($tables), $tables);
165 $this->assertTrue(empty($parseString), 'parseString is not empty');
166 }
167
168 /**
169 * @test
170 * @see http://bugs.typo3.org/view.php?id=12596
171 */
172 public function parseFromTablesWithRightOuterJoinReturnsArray() {
173 $parseString = 'tx_powermail_fieldsets RIGHT JOIN tt_content ON tx_powermail_fieldsets.tt_content = tt_content.uid';
174 $tables = $this->fixture->parseFromTables($parseString);
175
176 $this->assertTrue(is_array($tables), $tables);
177 $this->assertTrue(empty($parseString), 'parseString is not empty');
178 }
179
180 /**
181 * @test
182 */
183 public function parseFromTablesWithMultipleJoinsReturnsArray() {
184 $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';
185 $tables = $this->fixture->parseFromTables($parseString);
186
187 $this->assertTrue(is_array($tables), $tables);
188 $this->assertTrue(empty($parseString), 'parseString is not empty');
189 }
190
191 /**
192 * @test
193 * @see http://bugs.typo3.org/view.php?id=12596
194 */
195 public function parseFromTablesWithMultipleJoinsAndParenthesesReturnsArray() {
196 $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';
197 $tables = $this->fixture->parseFromTables($parseString);
198
199 $this->assertTrue(is_array($tables), $tables);
200 $this->assertTrue(empty($parseString), 'parseString is not empty');
201 }
202
203 /**
204 * @test
205 */
206 public function parseWhereClauseReturnsArray() {
207 $parseString = 'uid IN (1,2) AND (starttime < ' . time() . ' OR cruser_id + 10 < 20)';
208 $where = $this->fixture->parseWhereClause($parseString);
209
210 $this->assertTrue(is_array($where), $where);
211 $this->assertTrue(empty($parseString), 'parseString is not empty');
212 }
213
214 /**
215 * @test
216 */
217 public function canSelectAllFieldsFromPages() {
218 $sql = 'SELECT * FROM pages';
219 $expected = $sql;
220 $actual = $this->cleanSql($this->fixture->debug_testSQL($sql));
221
222 $this->assertEquals($expected, $actual);
223 }
224
225 /**
226 * @test
227 */
228 public function canUseInnerJoinInSelect() {
229 $sql = 'SELECT pages.uid, be_users.username FROM be_users INNER JOIN pages ON pages.cruser_id = be_users.uid';
230 $expected = 'SELECT pages.uid, be_users.username FROM be_users INNER JOIN pages ON pages.cruser_id=be_users.uid';
231 $actual = $this->cleanSql($this->fixture->debug_testSQL($sql));
232
233 $this->assertEquals($expected, $actual);
234 }
235
236 /**
237 * @test
238 */
239 public function canUseMultipleInnerJoinsInSelect() {
240 $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';
241 $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';
242 $actual = $this->cleanSql($this->fixture->debug_testSQL($sql));
243
244 $this->assertEquals($expected, $actual);
245 }
246
247 /**
248 * @test
249 * @see http://bugs.typo3.org/view.php?id=4466
250 */
251 public function indexMayContainALengthRestriction() {
252 $parseString = '
253 CREATE TABLE tx_realurl_uniqalias (
254 uid int(11) NOT NULL auto_increment,
255 tstamp int(11) DEFAULT \'0\' NOT NULL,
256 tablename varchar(60) DEFAULT \'\' NOT NULL,
257 field_alias varchar(255) DEFAULT \'\' NOT NULL,
258 field_id varchar(60) DEFAULT \'\' NOT NULL,
259 value_alias varchar(255) DEFAULT \'\' NOT NULL,
260 value_id int(11) DEFAULT \'0\' NOT NULL,
261 lang int(11) DEFAULT \'0\' NOT NULL,
262 expire int(11) DEFAULT \'0\' NOT NULL,
263
264 PRIMARY KEY (uid),
265 KEY tablename (tablename),
266 KEY bk_realurl01 (field_alias,field_id,value_id,lang,expire),
267 KEY bk_realurl02 (tablename,field_alias,field_id,value_alias(220),expire)
268 );
269 ';
270
271 $createTables = $this->fixture->_callRef('parseCREATETABLE', $parseString);
272 $this->assertTrue(is_array($createTables), $createTables);
273 }
274 }
275 ?>