[BUGFIX] InstallTool: Optimize ALTER TABLE for auto_increment columns
[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, CAST("usergroup" AS CHAR)) != 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->compileSQL($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/69304
164 */
165 public function alterTableAddFieldWithAutoIncrementIsRemappedToSerialType() {
166 $parseString = 'ALTER TABLE sys_file ADD uid INT(11) NOT NULL AUTO_INCREMENT';
167 $components = $this->subject->SQLparser->_callRef('parseALTERTABLE', $parseString);
168 $this->assertInternalType('array', $components);
169
170 $result = $this->subject->SQLparser->compileSQL($components);
171 $expected = array('ALTER TABLE "sys_file" ADD COLUMN "uid" SERIAL');
172 $this->assertSame($expected, $this->cleanSql($result));
173 }
174
175 /**
176 * @test
177 * @see http://forge.typo3.org/issues/67445
178 */
179 public function canParseAlterTableDropKeyStatement() {
180 $parseString = 'ALTER TABLE sys_collection DROP KEY parent';
181 $components = $this->subject->SQLparser->_callRef('parseALTERTABLE', $parseString);
182 $this->assertInternalType('array', $components);
183
184 $result = $this->subject->SQLparser->compileSQL($components);
185 $expected = array('DROP INDEX "dd81ee97_parent"');
186 $this->assertSame($expected, $this->cleanSql($result));
187 }
188
189 /**
190 * @test
191 * @see http://forge.typo3.org/issues/43262
192 */
193 public function countFieldInOrderByIsInGroupBy() {
194 $result = $this->subject->SELECTquery('COUNT(title)', 'pages', '', 'title', 'title');
195 $expected = 'SELECT COUNT("title") FROM "pages" GROUP BY "title" ORDER BY "title"';
196 $this->assertEquals($expected, $this->cleanSql($result));
197 }
198
199 /**
200 * @test
201 * @see http://forge.typo3.org/issues/43262
202 */
203 public function multipleCountFieldsInOrderByAreInGroupBy() {
204 $result = $this->subject->SELECTquery('COUNT(title), COUNT(pid)', 'pages', '', 'title, pid', 'title, pid');
205 $expected = 'SELECT COUNT("title"), COUNT("pid") FROM "pages" GROUP BY "title", "pid" ORDER BY "title", "pid"';
206 $this->assertEquals($expected, $this->cleanSql($result));
207 }
208
209 /**
210 * @test
211 * @see http://forge.typo3.org/issues/43262
212 */
213 public function countFieldInOrderByIsNotInGroupBy() {
214 $result = $this->subject->SELECTquery('COUNT(title)', 'pages', '', '', 'title');
215 $expected = 'SELECT COUNT("title") FROM "pages"';
216 $this->assertEquals($expected, $this->cleanSql($result));
217 }
218
219 /**
220 * @test
221 * @see http://forge.typo3.org/issues/43262
222 */
223 public function multipleCountFieldsInOrderByAreNotInGroupBy() {
224 $result = $this->subject->SELECTquery('COUNT(title), COUNT(pid)', 'pages', '', '', 'title, pid');
225 $expected = 'SELECT COUNT("title"), COUNT("pid") FROM "pages"';
226 $this->assertEquals($expected, $this->cleanSql($result));
227 }
228
229 /**
230 * @test
231 * @see http://forge.typo3.org/issues/43262
232 */
233 public function someCountFieldsInOrderByAreNotInGroupBy() {
234 $result = $this->subject->SELECTquery('COUNT(title), COUNT(pid)', 'pages', '', 'title', 'title, pid');
235 $expected = 'SELECT COUNT("title"), COUNT("pid") FROM "pages" GROUP BY "title" ORDER BY "title"';
236 $this->assertEquals($expected, $this->cleanSql($result));
237 }
238
239 /**
240 * @test
241 * @param string $fieldSQL
242 * @param string $expected
243 * @dataProvider equivalentFieldTypeDataProvider
244 * @see http://forge.typo3.org/issues/67301
245 */
246 public function suggestEquivalentFieldDefinitions($fieldSQL, $expected) {
247 $actual= $this->subject->getEquivalentFieldDefinition($fieldSQL);
248 $this->assertSame($expected, $actual);
249 }
250
251 /**
252 * @return array
253 */
254 public function equivalentFieldTypeDataProvider() {
255 return array(
256 array('int(11) NOT NULL default \'0\'', 'int(11) NOT NULL default \'0\''),
257 array('int(10) NOT NULL', 'int(11) NOT NULL'),
258 array('tinyint(3)', 'smallint(6)'),
259 array('bigint(20) NOT NULL', 'bigint(20) NOT NULL'),
260 array('tinytext NOT NULL', 'varchar(255) NOT NULL default \'\''),
261 array('tinytext', 'varchar(255) default NULL'),
262 array('mediumtext', 'longtext')
263 );
264 }
265 }