[TASK] dbal: skip update suggestions for equivalent fields in Install Tool
[Packages/TYPO3.CMS.git] / typo3 / sysext / dbal / Tests / Unit / Database / DatabaseConnectionPostgresqlTest.php
1 <?php
2 namespace TYPO3\CMS\Dbal\Tests\Unit\Database;
3
4 /*
5 * This file is part of the TYPO3 CMS project.
6 *
7 * It is free software; you can redistribute it and/or modify it under
8 * the terms of the GNU General Public License, either version 2
9 * of the License, or any later version.
10 *
11 * For the full copyright and license information, please read the
12 * LICENSE.txt file that was distributed with this source code.
13 *
14 * The TYPO3 project - inspiring people to share!
15 */
16
17 /**
18 * Test case
19 */
20 class DatabaseConnectionPostgresqlTest extends AbstractTestCase {
21
22 /**
23 * @var \TYPO3\CMS\Dbal\Database\DatabaseConnection|\PHPUnit_Framework_MockObject_MockObject|\TYPO3\CMS\Core\Tests\AccessibleObjectInterface
24 */
25 protected $subject;
26
27 /**
28 * Prepare a DatabaseConnection subject ready to parse mssql queries
29 *
30 * @return void
31 */
32 protected function setUp() {
33 $configuration = array(
34 'handlerCfg' => array(
35 '_DEFAULT' => array(
36 'type' => 'adodb',
37 'config' => array(
38 'driver' => 'postgres',
39 ),
40 ),
41 ),
42 'mapping' => array(
43 'tx_templavoila_tmplobj' => array(
44 'mapFieldNames' => array(
45 'datastructure' => 'ds',
46 ),
47 ),
48 'Members' => array(
49 'mapFieldNames' => array(
50 'pid' => '0',
51 'cruser_id' => '1',
52 'uid' => 'MemberID',
53 ),
54 ),
55 ),
56 );
57 $this->subject = $this->prepareSubject('postgres7', $configuration);
58 }
59
60 /**
61 * @test
62 */
63 public function runningADOdbDriverReturnsTrueWithPostgresForPostgres8DefaultDriverConfiguration() {
64 $this->assertTrue($this->subject->runningADOdbDriver('postgres'));
65 }
66
67 /**
68 * @test
69 * @see http://forge.typo3.org/issues/15492
70 */
71 public function limitIsProperlyRemapped() {
72 $result = $this->subject->SELECTquery('*', 'be_users', '1=1', '', '', '20');
73 $expected = 'SELECT * FROM "be_users" WHERE 1 = 1 LIMIT 20';
74 $this->assertEquals($expected, $this->cleanSql($result));
75 }
76
77 /**
78 * @test
79 * @see http://forge.typo3.org/issues/15492
80 */
81 public function limitWithSkipIsProperlyRemapped() {
82 $result = $this->subject->SELECTquery('*', 'be_users', '1=1', '', '', '20,40');
83 $expected = 'SELECT * FROM "be_users" WHERE 1 = 1 LIMIT 40 OFFSET 20';
84 $this->assertEquals($expected, $this->cleanSql($result));
85 }
86
87 /**
88 * @test
89 * @see http://forge.typo3.org/issues/23087
90 */
91 public function findInSetIsProperlyRemapped() {
92 $result = $this->subject->SELECTquery('*', 'fe_users', 'FIND_IN_SET(10, usergroup)');
93 $expected = 'SELECT * FROM "fe_users" WHERE FIND_IN_SET(10, "usergroup") != 0';
94 $this->assertEquals($expected, $this->cleanSql($result));
95 }
96
97 /**
98 * @test
99 * @see http://forge.typo3.org/issues/21514
100 */
101 public function likeBinaryOperatorIsRemappedToLike() {
102 $result = $this->subject->SELECTquery('*', 'tt_content', 'bodytext LIKE BINARY \'test\'');
103 $expected = 'SELECT * FROM "tt_content" WHERE "bodytext" LIKE \'test\'';
104 $this->assertEquals($expected, $this->cleanSql($result));
105 }
106
107 /**
108 * @test
109 * @see http://forge.typo3.org/issues/21514
110 */
111 public function notLikeBinaryOperatorIsRemappedToNotLike() {
112 $result = $this->subject->SELECTquery('*', 'tt_content', 'bodytext NOT LIKE BINARY \'test\'');
113 $expected = 'SELECT * FROM "tt_content" WHERE "bodytext" NOT LIKE \'test\'';
114 $this->assertEquals($expected, $this->cleanSql($result));
115 }
116
117 /**
118 * @test
119 * @see http://forge.typo3.org/issues/21514
120 */
121 public function likeOperatorIsRemappedToIlike() {
122 $result = $this->subject->SELECTquery('*', 'tt_content', 'bodytext LIKE \'test\'');
123 $expected = 'SELECT * FROM "tt_content" WHERE "bodytext" ILIKE \'test\'';
124 $this->assertEquals($expected, $this->cleanSql($result));
125 }
126
127 /**
128 * @test
129 * @see http://forge.typo3.org/issues/21514
130 */
131 public function notLikeOperatorIsRemappedToNotIlike() {
132 $result = $this->subject->SELECTquery('*', 'tt_content', 'bodytext NOT LIKE \'test\'');
133 $expected = 'SELECT * FROM "tt_content" WHERE "bodytext" NOT ILIKE \'test\'';
134 $this->assertEquals($expected, $this->cleanSql($result));
135 }
136
137 /**
138 * @test
139 * @see http://forge.typo3.org/issues/32626
140 */
141 public function notEqualAnsiOperatorCanBeParsed() {
142 $result = $this->subject->SELECTquery('*', 'pages', 'pid<>3');
143 $expected = 'SELECT * FROM "pages" WHERE "pid" <> 3';
144 $this->assertEquals($expected, $this->cleanSql($result));
145 }
146
147 /**
148 * @test
149 * @see http://forge.typo3.org/issues/67445
150 */
151 public function alterTableAddKeyStatementIsRemappedToCreateIndex() {
152 $parseString = 'ALTER TABLE sys_collection ADD KEY parent (pid,deleted)';
153 $components = $this->subject->SQLparser->_callRef('parseALTERTABLE', $parseString);
154 $this->assertInternalType('array', $components);
155
156 $result = $this->subject->SQLparser->_callRef('compileALTERTABLE', $components);
157 $expected = array('CREATE INDEX "dd81ee97_parent" ON "sys_collection" ("pid", "deleted")');
158 $this->assertSame($expected, $this->cleanSql($result));
159 }
160
161 /**
162 * @test
163 * @see http://forge.typo3.org/issues/67445
164 */
165 public function canParseAlterTableDropKeyStatement() {
166 $parseString = 'ALTER TABLE sys_collection DROP KEY parent';
167 $components = $this->subject->SQLparser->_callRef('parseALTERTABLE', $parseString);
168 $this->assertInternalType('array', $components);
169
170 $result = $this->subject->SQLparser->_callRef('compileALTERTABLE', $components);
171 $expected = array('DROP INDEX "dd81ee97_parent"');
172 $this->assertSame($expected, $this->cleanSql($result));
173 }
174
175 /**
176 * @test
177 * @see http://forge.typo3.org/issues/43262
178 */
179 public function countFieldInOrderByIsInGroupBy() {
180 $result = $this->subject->SELECTquery('COUNT(title)', 'pages', '', 'title', 'title');
181 $expected = 'SELECT COUNT("title") FROM "pages" GROUP BY "title" ORDER BY "title"';
182 $this->assertEquals($expected, $this->cleanSql($result));
183 }
184
185 /**
186 * @test
187 * @see http://forge.typo3.org/issues/43262
188 */
189 public function multipleCountFieldsInOrderByAreInGroupBy() {
190 $result = $this->subject->SELECTquery('COUNT(title), COUNT(pid)', 'pages', '', 'title, pid', 'title, pid');
191 $expected = 'SELECT COUNT("title"), COUNT("pid") FROM "pages" GROUP BY "title", "pid" ORDER BY "title", "pid"';
192 $this->assertEquals($expected, $this->cleanSql($result));
193 }
194
195 /**
196 * @test
197 * @see http://forge.typo3.org/issues/43262
198 */
199 public function countFieldInOrderByIsNotInGroupBy() {
200 $result = $this->subject->SELECTquery('COUNT(title)', 'pages', '', '', 'title');
201 $expected = 'SELECT COUNT("title") FROM "pages"';
202 $this->assertEquals($expected, $this->cleanSql($result));
203 }
204
205 /**
206 * @test
207 * @see http://forge.typo3.org/issues/43262
208 */
209 public function multipleCountFieldsInOrderByAreNotInGroupBy() {
210 $result = $this->subject->SELECTquery('COUNT(title), COUNT(pid)', 'pages', '', '', 'title, pid');
211 $expected = 'SELECT COUNT("title"), COUNT("pid") FROM "pages"';
212 $this->assertEquals($expected, $this->cleanSql($result));
213 }
214
215 /**
216 * @test
217 * @see http://forge.typo3.org/issues/43262
218 */
219 public function someCountFieldsInOrderByAreNotInGroupBy() {
220 $result = $this->subject->SELECTquery('COUNT(title), COUNT(pid)', 'pages', '', 'title', 'title, pid');
221 $expected = 'SELECT COUNT("title"), COUNT("pid") FROM "pages" GROUP BY "title" ORDER BY "title"';
222 $this->assertEquals($expected, $this->cleanSql($result));
223 }
224
225 /**
226 * @test
227 * @param string $fieldSQL
228 * @param string $expected
229 * @dataProvider equivalentFieldTypeDataProvider
230 * @see http://forge.typo3.org/issues/67301
231 */
232 public function suggestEquivalentFieldDefinitions($fieldSQL, $expected) {
233 $actual= $this->subject->getEquivalentFieldDefinition($fieldSQL);
234 $this->assertSame($expected, $actual);
235 }
236
237 /**
238 * @return array
239 */
240 public function equivalentFieldTypeDataProvider() {
241 return array(
242 array('int(11) NOT NULL default \'0\'', 'int(11) NOT NULL default \'0\''),
243 array('int(10) NOT NULL', 'int(11) NOT NULL'),
244 array('tinyint(3)', 'smallint(6)'),
245 array('bigint(20) NOT NULL', 'bigint(20) NOT NULL'),
246 array('tinytext NOT NULL', 'varchar(255) NOT NULL default \'\''),
247 array('tinytext', 'varchar(255) default NULL'),
248 array('mediumtext', 'longtext')
249 );
250 }
251 }