Added some general unit tests (without any engine-specific test nor mapping)
[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 t3lib_sqlparser (extended to make protected methods public)
42 */
43 protected $fixture;
44
45 public function setUp() {
46 $className = self::buildAccessibleProxy('ux_t3lib_sqlparser');
47 $this->fixture = new $className;
48 }
49
50 public function tearDown() {
51 unset($this->fixture);
52 }
53
54 /**
55 * Cleans a SQL query.
56 *
57 * @param mixed $sql
58 * @return mixed (string or array)
59 */
60 private function cleanSql($sql) {
61 if (!is_string($sql)) {
62 return $sql;
63 }
64
65 $sql = str_replace("\n", ' ', $sql);
66 $sql = preg_replace('/\s+/', ' ', $sql);
67 return $sql;
68 }
69
70 /**
71 * @test
72 */
73 public function canExtractPartsOfAQuery() {
74 $parseString = "SELECT *\nFROM pages WHERE pid IN (1,2,3,4)";
75 $regex = '^SELECT[[:space:]]+(.*)[[:space:]]+';
76 $trimAll = TRUE;
77 $fields = $this->fixture->_callRef('nextPart', $parseString, $regex, $trimAll);
78
79 $this->assertEquals(
80 '*',
81 $fields
82 );
83 $this->assertEquals(
84 'FROM pages WHERE pid IN (1,2,3,4)',
85 $parseString
86 );
87
88 $regex = '^FROM ([^)]+) WHERE';
89 $table = $this->fixture->_callRef('nextPart', $parseString, $regex);
90
91 $this->assertEquals(
92 'pages',
93 $table
94 );
95 $this->assertEquals(
96 'pages WHERE pid IN (1,2,3,4)',
97 $parseString
98 );
99 }
100
101 /**
102 * @test
103 */
104 public function canGetIntegerValue() {
105 $parseString = '1024';
106 $value = $this->fixture->_callRef('getValue', $parseString);
107 $expected = array(1024);
108
109 $this->assertEquals($expected, $value);
110 }
111
112 /**
113 * @test
114 */
115 public function canGetStringValue() {
116 $parseString = '"some owner\\\' string"';
117 $value = $this->fixture->_callRef('getValue', $parseString);
118 $expected = array('some owner\' string', '"');
119
120 $this->assertEquals($expected, $value);
121 }
122
123 /**
124 * @test
125 */
126 public function canGetListOfValues() {
127 $parseString = '( 1, 2, 3 ,4)';
128 $operator = 'IN';
129 $values = $this->fixture->_callRef('getValue', $parseString, $operator);
130 $expected = array(
131 array(1),
132 array(2),
133 array(3),
134 array(4)
135 );
136
137 $this->assertEquals($expected, $values);
138 }
139
140 /**
141 * @test
142 */
143 public function parseFromTablesWithInnerJoinReturnsArray() {
144 $parseString = 'be_users INNER JOIN pages ON pages.cruser_id = be_users.uid';
145 $tables = $this->fixture->parseFromTables($parseString);
146
147 $this->assertTrue(is_array($tables), $tables);
148 $this->assertTrue(empty($parseString), 'parseString is not empty');
149 }
150
151 /**
152 * @test
153 */
154 public function parseFromTablesWithLeftOuterJoinReturnsArray() {
155 $parseString = 'be_users LEFT OUTER JOIN pages ON be_users.uid = pages.cruser_id';
156 $tables = $this->fixture->parseFromTables($parseString);
157
158 $this->assertTrue(is_array($tables), $tables);
159 $this->assertTrue(empty($parseString), 'parseString is not empty');
160 }
161
162 /**
163 * @test
164 */
165 public function parseFromTablesWithMultipleJoinsReturnsArray() {
166 $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';
167 $tables = $this->fixture->parseFromTables($parseString);
168
169 $this->assertTrue(is_array($tables), $tables);
170 $this->assertTrue(empty($parseString), 'parseString is not empty');
171 }
172
173 /**
174 * @test
175 */
176 public function parseWhereClauseReturnsArray() {
177 $parseString = 'uid IN (1,2) AND (starttime < ' . time() . ' OR cruser_id + 10 < 20)';
178 $where = $this->fixture->parseWhereClause($parseString);
179
180 $this->assertTrue(is_array($where), $where);
181 $this->assertTrue(empty($parseString), 'parseString is not empty');
182 }
183
184 /**
185 * @test
186 */
187 public function canSelectAllFieldsFromPages() {
188 $sql = 'SELECT * FROM pages';
189 $expected = $sql;
190 $actual = $this->cleanSql($this->fixture->debug_testSQL($sql));
191
192 $this->assertEquals($expected, $actual);
193 }
194
195 /**
196 * @test
197 */
198 public function canUseInnerJoinInSelect() {
199 $sql = 'SELECT pages.uid, be_users.username FROM be_users INNER JOIN pages ON pages.cruser_id = be_users.uid';
200 $expected = 'SELECT pages.uid, be_users.username FROM be_users INNER JOIN pages ON pages.cruser_id=be_users.uid';
201 $actual = $this->cleanSql($this->fixture->debug_testSQL($sql));
202
203 $this->assertEquals($expected, $actual);
204 }
205
206 /**
207 * @test
208 */
209 public function canUseMultipleInnerJoinsInSelect() {
210 $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';
211 $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';
212 $actual = $this->cleanSql($this->fixture->debug_testSQL($sql));
213
214 $this->assertEquals($expected, $actual);
215 }
216
217 }
218 ?>