[TASK] Add unit-test for SQL-comparator <>
[Packages/TYPO3.CMS.git] / typo3 / sysext / dbal / tests / dbPostgresqlTest.php
1 <?php
2 /***************************************************************
3 * Copyright notice
4 *
5 * (c) 2010-2011 Xavier Perseguers <xavier@typo3.org>
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 require_once('FakeDbConnection.php');
28
29 /**
30 * Testcase for class ux_t3lib_db. Testing PostgreSQL database handling.
31 *
32 * @author Xavier Perseguers <xavier@typo3.org>
33 *
34 * @package TYPO3
35 * @subpackage dbal
36 */
37 class dbPostgresqlTest extends BaseTestCase {
38
39 /**
40 * @var t3lib_db
41 */
42 protected $db;
43
44 /**
45 * @var array
46 */
47 protected $dbalConfig;
48
49 /**
50 * Prepares the environment before running a test.
51 */
52 public function setUp() {
53 // Backup DBAL configuration
54 $this->dbalConfig = $GLOBALS['TYPO3_CONF_VARS']['EXTCONF']['dbal'];
55 // Backup database connection
56 $this->db = $GLOBALS['TYPO3_DB'];
57 // Reconfigure DBAL to use PostgreSQL
58 require('fixtures/postgresql.config.php');
59
60 $className = self::buildAccessibleProxy('ux_t3lib_db');
61 $GLOBALS['TYPO3_DB'] = new $className;
62 $parserClassName = self::buildAccessibleProxy('ux_t3lib_sqlparser');
63 $GLOBALS['TYPO3_DB']->SQLparser = new $parserClassName;
64
65 $this->assertFalse($GLOBALS['TYPO3_DB']->isConnected());
66
67 // Initialize a fake PostgreSQL connection (using 'postgres7' as 'postgres' is remapped to it in AdoDB)
68 FakeDbConnection::connect($GLOBALS['TYPO3_DB'], 'postgres7');
69
70 $this->assertTrue($GLOBALS['TYPO3_DB']->isConnected());
71 }
72
73 /**
74 * Cleans up the environment after running a test.
75 */
76 public function tearDown() {
77 // Clear DBAL-generated cache files
78 $GLOBALS['TYPO3_DB']->clearCachedFieldInfo();
79 // Restore DBAL configuration
80 $GLOBALS['TYPO3_CONF_VARS']['EXTCONF']['dbal'] = $this->dbalConfig;
81 // Restore DB connection
82 $GLOBALS['TYPO3_DB'] = $this->db;
83 }
84
85 /**
86 * Cleans a SQL query.
87 *
88 * @param mixed $sql
89 * @return mixed (string or array)
90 */
91 private function cleanSql($sql) {
92 if (!is_string($sql)) {
93 return $sql;
94 }
95
96 $sql = str_replace("\n", ' ', $sql);
97 $sql = preg_replace('/\s+/', ' ', $sql);
98 return trim($sql);
99 }
100
101 /**
102 * @test
103 */
104 public function configurationIsUsingAdodbAndDriverPostgres() {
105 $configuration = $GLOBALS['TYPO3_DB']->conf['handlerCfg'];
106 $this->assertTrue(is_array($configuration) && count($configuration) > 0, 'No configuration found');
107 $this->assertEquals('adodb', $configuration['_DEFAULT']['type']);
108 $this->assertTrue($GLOBALS['TYPO3_DB']->runningADOdbDriver('postgres') !== FALSE, 'Not using postgres driver');
109 }
110
111 /**
112 * @test
113 */
114 public function tablesWithMappingAreDetected() {
115 $tablesWithMapping = array_keys($GLOBALS['TYPO3_CONF_VARS']['EXTCONF']['dbal']['mapping']);
116
117 foreach ($GLOBALS['TYPO3_DB']->cache_fieldType as $table => $fieldTypes) {
118 $tableDef = $GLOBALS['TYPO3_DB']->_call('map_needMapping', $table);
119
120 if (in_array($table, $tablesWithMapping)) {
121 self::assertTrue(is_array($tableDef), 'Table ' . $table . ' was expected to need mapping');
122 } else {
123 self::assertFalse($tableDef, 'Table ' . $table . ' was not expected to need mapping');
124 }
125 }
126 }
127
128 /**
129 * @test
130 * @see http://bugs.typo3.org/view.php?id=2367
131 */
132 public function limitIsProperlyRemapped() {
133 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
134 '*',
135 'be_users',
136 '1=1',
137 '',
138 '',
139 '20'
140 ));
141 $expected = 'SELECT * FROM "be_users" WHERE 1 = 1 LIMIT 20';
142 $this->assertEquals($expected, $query);
143 }
144
145 /**
146 * @test
147 * @see http://bugs.typo3.org/view.php?id=2367
148 */
149 public function limitWithSkipIsProperlyRemapped() {
150 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
151 '*',
152 'be_users',
153 '1=1',
154 '',
155 '',
156 '20,40'
157 ));
158 $expected = 'SELECT * FROM "be_users" WHERE 1 = 1 LIMIT 40 OFFSET 20';
159 $this->assertEquals($expected, $query);
160 }
161
162 /**
163 * @test
164 * @see http://bugs.typo3.org/view.php?id=14985
165 */
166 public function findInSetIsProperlyRemapped() {
167 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
168 '*',
169 'fe_users',
170 'FIND_IN_SET(10, usergroup)'
171 ));
172 $expected = 'SELECT * FROM "fe_users" WHERE FIND_IN_SET(10, "usergroup") != 0';
173 $this->assertEquals($expected, $query);
174 }
175
176 /**
177 * @test
178 * @see http://bugs.typo3.org/view.php?id=12535
179 */
180 public function likeBinaryOperatorIsRemappedToLike() {
181 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
182 '*',
183 'tt_content',
184 'bodytext LIKE BINARY \'test\''
185 ));
186 $expected = 'SELECT * FROM "tt_content" WHERE "bodytext" LIKE \'test\'';
187 $this->assertEquals($expected, $query);
188 }
189
190 /**
191 * @test
192 * @see http://bugs.typo3.org/view.php?id=12535
193 */
194 public function notLikeBinaryOperatorIsRemappedToNotLike() {
195 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
196 '*',
197 'tt_content',
198 'bodytext NOT LIKE BINARY \'test\''
199 ));
200 $expected = 'SELECT * FROM "tt_content" WHERE "bodytext" NOT LIKE \'test\'';
201 $this->assertEquals($expected, $query);
202 }
203
204 /**
205 * @test
206 * @see http://bugs.typo3.org/view.php?id=12535
207 */
208 public function likeOperatorIsRemappedToIlike() {
209 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
210 '*',
211 'tt_content',
212 'bodytext LIKE \'test\''
213 ));
214 $expected = 'SELECT * FROM "tt_content" WHERE "bodytext" ILIKE \'test\'';
215 $this->assertEquals($expected, $query);
216 }
217
218 /**
219 * @test
220 * @see http://bugs.typo3.org/view.php?id=12535
221 */
222 public function notLikeOperatorIsRemappedToNotIlike() {
223 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
224 '*',
225 'tt_content',
226 'bodytext NOT LIKE \'test\''
227 ));
228 $expected = 'SELECT * FROM "tt_content" WHERE "bodytext" NOT ILIKE \'test\'';
229 $this->assertEquals($expected, $query);
230 }
231
232 /**
233 * @test
234 * @see http://forge.typo3.org/issues/32626
235 */
236 public function notEqualAnsiOperatorCanBeParsed() {
237 $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
238 '*',
239 'pages',
240 'pid<>3'
241 ));
242 $expected = 'SELECT * FROM "pages" WHERE "pid" <> 3';
243 $this->assertEquals($expected, $query);
244 }
245 }
246
247 ?>