1 | <?php |
||
2 | |||
3 | namespace Doctrine\Tests\DBAL\Platforms; |
||
4 | |||
5 | use Doctrine\DBAL\Schema\Column; |
||
6 | use Doctrine\DBAL\Schema\ColumnDiff; |
||
7 | use Doctrine\DBAL\Schema\Table; |
||
8 | use Doctrine\DBAL\Schema\TableDiff; |
||
9 | use Doctrine\DBAL\Types\Type; |
||
10 | |||
11 | abstract class AbstractSQLServerPlatformTestCase extends AbstractPlatformTestCase |
||
12 | { |
||
13 | protected static $selectFromCtePattern = "WITH dctrn_cte AS (%s) SELECT * FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS doctrine_rownum FROM dctrn_cte) AS doctrine_tbl WHERE doctrine_rownum BETWEEN %d AND %d ORDER BY doctrine_rownum ASC"; |
||
14 | |||
15 | public function getGenerateTableSql() |
||
16 | { |
||
17 | return 'CREATE TABLE test (id INT IDENTITY NOT NULL, test NVARCHAR(255), PRIMARY KEY (id))'; |
||
18 | } |
||
19 | |||
20 | public function getGenerateTableWithMultiColumnUniqueIndexSql() |
||
21 | { |
||
22 | return array( |
||
23 | 'CREATE TABLE test (foo NVARCHAR(255), bar NVARCHAR(255))', |
||
24 | 'CREATE UNIQUE INDEX UNIQ_D87F7E0C8C73652176FF8CAA ON test (foo, bar) WHERE foo IS NOT NULL AND bar IS NOT NULL' |
||
25 | ); |
||
26 | } |
||
27 | |||
28 | public function getGenerateAlterTableSql() |
||
29 | { |
||
30 | return array( |
||
31 | 'ALTER TABLE mytable ADD quota INT', |
||
32 | 'ALTER TABLE mytable DROP COLUMN foo', |
||
33 | 'ALTER TABLE mytable ALTER COLUMN baz NVARCHAR(255) NOT NULL', |
||
34 | "ALTER TABLE mytable ADD CONSTRAINT DF_6B2BD609_78240498 DEFAULT 'def' FOR baz", |
||
35 | 'ALTER TABLE mytable ALTER COLUMN bloo BIT NOT NULL', |
||
36 | "ALTER TABLE mytable ADD CONSTRAINT DF_6B2BD609_CECED971 DEFAULT '0' FOR bloo", |
||
37 | "sp_RENAME 'mytable', 'userlist'", |
||
38 | "DECLARE @sql NVARCHAR(MAX) = N''; " . |
||
39 | "SELECT @sql += N'EXEC sp_rename N''' + dc.name + ''', N''' " . |
||
40 | "+ REPLACE(dc.name, '6B2BD609', 'E2B58069') + ''', ''OBJECT'';' " . |
||
41 | "FROM sys.default_constraints dc " . |
||
42 | "JOIN sys.tables tbl ON dc.parent_object_id = tbl.object_id " . |
||
43 | "WHERE tbl.name = 'userlist';" . |
||
44 | "EXEC sp_executesql @sql" |
||
45 | ); |
||
46 | } |
||
47 | |||
48 | /** |
||
49 | * @expectedException \Doctrine\DBAL\DBALException |
||
50 | */ |
||
51 | public function testDoesNotSupportRegexp() |
||
52 | { |
||
53 | $this->_platform->getRegexpExpression(); |
||
54 | } |
||
55 | |||
56 | public function testGeneratesSqlSnippets() |
||
57 | { |
||
58 | self::assertEquals('CONVERT(date, GETDATE())', $this->_platform->getCurrentDateSQL()); |
||
59 | self::assertEquals('CONVERT(time, GETDATE())', $this->_platform->getCurrentTimeSQL()); |
||
60 | self::assertEquals('CURRENT_TIMESTAMP', $this->_platform->getCurrentTimestampSQL()); |
||
61 | self::assertEquals('"', $this->_platform->getIdentifierQuoteCharacter(), 'Identifier quote character is not correct'); |
||
62 | self::assertEquals('(column1 + column2 + column3)', $this->_platform->getConcatExpression('column1', 'column2', 'column3'), 'Concatenation expression is not correct'); |
||
63 | } |
||
64 | |||
65 | View Code Duplication | public function testGeneratesTransactionsCommands() |
|
66 | { |
||
67 | self::assertEquals( |
||
68 | 'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED', |
||
69 | $this->_platform->getSetTransactionIsolationSQL(\Doctrine\DBAL\Connection::TRANSACTION_READ_UNCOMMITTED) |
||
70 | ); |
||
71 | self::assertEquals( |
||
72 | 'SET TRANSACTION ISOLATION LEVEL READ COMMITTED', |
||
73 | $this->_platform->getSetTransactionIsolationSQL(\Doctrine\DBAL\Connection::TRANSACTION_READ_COMMITTED) |
||
74 | ); |
||
75 | self::assertEquals( |
||
76 | 'SET TRANSACTION ISOLATION LEVEL REPEATABLE READ', |
||
77 | $this->_platform->getSetTransactionIsolationSQL(\Doctrine\DBAL\Connection::TRANSACTION_REPEATABLE_READ) |
||
78 | ); |
||
79 | self::assertEquals( |
||
80 | 'SET TRANSACTION ISOLATION LEVEL SERIALIZABLE', |
||
81 | $this->_platform->getSetTransactionIsolationSQL(\Doctrine\DBAL\Connection::TRANSACTION_SERIALIZABLE) |
||
82 | ); |
||
83 | } |
||
84 | |||
85 | View Code Duplication | public function testGeneratesDDLSnippets() |
|
86 | { |
||
87 | $dropDatabaseExpectation = 'DROP DATABASE foobar'; |
||
88 | |||
89 | self::assertEquals('SELECT * FROM sys.databases', $this->_platform->getListDatabasesSQL()); |
||
90 | self::assertEquals('CREATE DATABASE foobar', $this->_platform->getCreateDatabaseSQL('foobar')); |
||
91 | self::assertEquals($dropDatabaseExpectation, $this->_platform->getDropDatabaseSQL('foobar')); |
||
92 | self::assertEquals('DROP TABLE foobar', $this->_platform->getDropTableSQL('foobar')); |
||
93 | } |
||
94 | |||
95 | View Code Duplication | public function testGeneratesTypeDeclarationForIntegers() |
|
96 | { |
||
97 | self::assertEquals( |
||
98 | 'INT', |
||
99 | $this->_platform->getIntegerTypeDeclarationSQL(array()) |
||
100 | ); |
||
101 | self::assertEquals( |
||
102 | 'INT IDENTITY', |
||
103 | $this->_platform->getIntegerTypeDeclarationSQL(array('autoincrement' => true) |
||
104 | )); |
||
105 | self::assertEquals( |
||
106 | 'INT IDENTITY', |
||
107 | $this->_platform->getIntegerTypeDeclarationSQL( |
||
108 | array('autoincrement' => true, 'primary' => true) |
||
109 | )); |
||
110 | } |
||
111 | |||
112 | public function testGeneratesTypeDeclarationsForStrings() |
||
113 | { |
||
114 | self::assertEquals( |
||
115 | 'NCHAR(10)', |
||
116 | $this->_platform->getVarcharTypeDeclarationSQL( |
||
117 | array('length' => 10, 'fixed' => true) |
||
118 | )); |
||
119 | self::assertEquals( |
||
120 | 'NVARCHAR(50)', |
||
121 | $this->_platform->getVarcharTypeDeclarationSQL(array('length' => 50)), |
||
122 | 'Variable string declaration is not correct' |
||
123 | ); |
||
124 | self::assertEquals( |
||
125 | 'NVARCHAR(255)', |
||
126 | $this->_platform->getVarcharTypeDeclarationSQL(array()), |
||
127 | 'Long string declaration is not correct' |
||
128 | ); |
||
129 | self::assertSame('VARCHAR(MAX)', $this->_platform->getClobTypeDeclarationSQL(array())); |
||
130 | self::assertSame( |
||
131 | 'VARCHAR(MAX)', |
||
132 | $this->_platform->getClobTypeDeclarationSQL(array('length' => 5, 'fixed' => true)) |
||
133 | ); |
||
134 | } |
||
135 | |||
136 | public function testPrefersIdentityColumns() |
||
137 | { |
||
138 | self::assertTrue($this->_platform->prefersIdentityColumns()); |
||
139 | } |
||
140 | |||
141 | public function testSupportsIdentityColumns() |
||
142 | { |
||
143 | self::assertTrue($this->_platform->supportsIdentityColumns()); |
||
144 | } |
||
145 | |||
146 | public function testSupportsCreateDropDatabase() |
||
147 | { |
||
148 | self::assertTrue($this->_platform->supportsCreateDropDatabase()); |
||
149 | } |
||
150 | |||
151 | public function testSupportsSchemas() |
||
152 | { |
||
153 | self::assertTrue($this->_platform->supportsSchemas()); |
||
154 | } |
||
155 | |||
156 | public function testDoesNotSupportSavePoints() |
||
157 | { |
||
158 | self::assertTrue($this->_platform->supportsSavepoints()); |
||
159 | } |
||
160 | |||
161 | public function getGenerateIndexSql() |
||
162 | { |
||
163 | return 'CREATE INDEX my_idx ON mytable (user_name, last_login)'; |
||
164 | } |
||
165 | |||
166 | public function getGenerateUniqueIndexSql() |
||
167 | { |
||
168 | return 'CREATE UNIQUE INDEX index_name ON test (test, test2) WHERE test IS NOT NULL AND test2 IS NOT NULL'; |
||
169 | } |
||
170 | |||
171 | public function getGenerateForeignKeySql() |
||
172 | { |
||
173 | return 'ALTER TABLE test ADD FOREIGN KEY (fk_name_id) REFERENCES other_table (id)'; |
||
174 | } |
||
175 | |||
176 | View Code Duplication | public function testModifyLimitQuery() |
|
177 | { |
||
178 | $querySql = 'SELECT * FROM user'; |
||
179 | $alteredSql = 'SELECT TOP 10 * FROM user'; |
||
180 | $sql = $this->_platform->modifyLimitQuery($querySql, 10, 0); |
||
181 | self::assertEquals(sprintf(self::$selectFromCtePattern, $alteredSql, 1, 10), $sql); |
||
182 | } |
||
183 | |||
184 | View Code Duplication | public function testModifyLimitQueryWithEmptyOffset() |
|
185 | { |
||
186 | $querySql = 'SELECT * FROM user'; |
||
187 | $alteredSql = 'SELECT TOP 10 * FROM user'; |
||
188 | $sql = $this->_platform->modifyLimitQuery($querySql, 10); |
||
189 | self::assertEquals(sprintf(self::$selectFromCtePattern, $alteredSql, 1, 10), $sql); |
||
190 | } |
||
191 | |||
192 | View Code Duplication | public function testModifyLimitQueryWithOffset() |
|
193 | { |
||
194 | if ( ! $this->_platform->supportsLimitOffset()) { |
||
195 | $this->markTestSkipped(sprintf('Platform "%s" does not support offsets in result limiting.', $this->_platform->getName())); |
||
196 | } |
||
197 | |||
198 | $querySql = 'SELECT * FROM user ORDER BY username DESC'; |
||
199 | $alteredSql = 'SELECT TOP 15 * FROM user ORDER BY username DESC'; |
||
200 | $sql = $this->_platform->modifyLimitQuery($querySql, 10, 5); |
||
201 | |||
202 | self::assertEquals(sprintf(self::$selectFromCtePattern, $alteredSql, 6, 15), $sql); |
||
203 | } |
||
204 | |||
205 | View Code Duplication | public function testModifyLimitQueryWithAscOrderBy() |
|
206 | { |
||
207 | $querySql = 'SELECT * FROM user ORDER BY username ASC'; |
||
208 | $alteredSql = 'SELECT TOP 10 * FROM user ORDER BY username ASC'; |
||
209 | $sql = $this->_platform->modifyLimitQuery($querySql, 10); |
||
210 | |||
211 | self::assertEquals(sprintf(self::$selectFromCtePattern, $alteredSql, 1, 10), $sql); |
||
212 | } |
||
213 | |||
214 | View Code Duplication | public function testModifyLimitQueryWithLowercaseOrderBy() |
|
215 | { |
||
216 | $querySql = 'SELECT * FROM user order by username'; |
||
217 | $alteredSql = 'SELECT TOP 10 * FROM user order by username'; |
||
218 | $sql = $this->_platform->modifyLimitQuery($querySql, 10); |
||
219 | self::assertEquals(sprintf(self::$selectFromCtePattern, $alteredSql, 1, 10), $sql); |
||
220 | } |
||
221 | |||
222 | View Code Duplication | public function testModifyLimitQueryWithDescOrderBy() |
|
223 | { |
||
224 | $querySql = 'SELECT * FROM user ORDER BY username DESC'; |
||
225 | $alteredSql = 'SELECT TOP 10 * FROM user ORDER BY username DESC'; |
||
226 | $sql = $this->_platform->modifyLimitQuery($querySql, 10); |
||
227 | self::assertEquals(sprintf(self::$selectFromCtePattern, $alteredSql, 1, 10), $sql); |
||
228 | } |
||
229 | |||
230 | View Code Duplication | public function testModifyLimitQueryWithMultipleOrderBy() |
|
231 | { |
||
232 | $querySql = 'SELECT * FROM user ORDER BY username DESC, usereamil ASC'; |
||
233 | $alteredSql = 'SELECT TOP 10 * FROM user ORDER BY username DESC, usereamil ASC'; |
||
234 | $sql = $this->_platform->modifyLimitQuery($querySql, 10); |
||
235 | self::assertEquals(sprintf(self::$selectFromCtePattern, $alteredSql, 1, 10), $sql); |
||
236 | } |
||
237 | |||
238 | View Code Duplication | public function testModifyLimitQueryWithSubSelect() |
|
239 | { |
||
240 | $querySql = 'SELECT * FROM (SELECT u.id as uid, u.name as uname) dctrn_result'; |
||
241 | $alteredSql = 'SELECT TOP 10 * FROM (SELECT u.id as uid, u.name as uname) dctrn_result'; |
||
242 | $sql = $this->_platform->modifyLimitQuery($querySql, 10); |
||
243 | self::assertEquals(sprintf(self::$selectFromCtePattern, $alteredSql, 1, 10), $sql); |
||
244 | } |
||
245 | |||
246 | View Code Duplication | public function testModifyLimitQueryWithSubSelectAndOrder() |
|
247 | { |
||
248 | $querySql = 'SELECT * FROM (SELECT u.id as uid, u.name as uname ORDER BY u.name DESC) dctrn_result'; |
||
249 | $alteredSql = 'SELECT TOP 10 * FROM (SELECT u.id as uid, u.name as uname) dctrn_result'; |
||
250 | $sql = $this->_platform->modifyLimitQuery($querySql, 10); |
||
251 | self::assertEquals(sprintf(self::$selectFromCtePattern, $alteredSql, 1, 10), $sql); |
||
252 | |||
253 | $querySql = 'SELECT * FROM (SELECT u.id, u.name ORDER BY u.name DESC) dctrn_result'; |
||
254 | $alteredSql = 'SELECT TOP 10 * FROM (SELECT u.id, u.name) dctrn_result'; |
||
255 | $sql = $this->_platform->modifyLimitQuery($querySql, 10); |
||
256 | self::assertEquals(sprintf(self::$selectFromCtePattern, $alteredSql, 1, 10), $sql); |
||
257 | } |
||
258 | |||
259 | public function testModifyLimitQueryWithSubSelectAndMultipleOrder() |
||
260 | { |
||
261 | if ( ! $this->_platform->supportsLimitOffset()) { |
||
262 | $this->markTestSkipped(sprintf('Platform "%s" does not support offsets in result limiting.', $this->_platform->getName())); |
||
263 | } |
||
264 | |||
265 | $querySql = 'SELECT * FROM (SELECT u.id as uid, u.name as uname ORDER BY u.name DESC, id ASC) dctrn_result'; |
||
266 | $alteredSql = 'SELECT TOP 15 * FROM (SELECT u.id as uid, u.name as uname) dctrn_result'; |
||
267 | $sql = $this->_platform->modifyLimitQuery($querySql, 10, 5); |
||
268 | self::assertEquals(sprintf(self::$selectFromCtePattern, $alteredSql, 6, 15), $sql); |
||
269 | |||
270 | $querySql = 'SELECT * FROM (SELECT u.id uid, u.name uname ORDER BY u.name DESC, id ASC) dctrn_result'; |
||
271 | $alteredSql = 'SELECT TOP 15 * FROM (SELECT u.id uid, u.name uname) dctrn_result'; |
||
272 | $sql = $this->_platform->modifyLimitQuery($querySql, 10, 5); |
||
273 | self::assertEquals(sprintf(self::$selectFromCtePattern, $alteredSql, 6, 15), $sql); |
||
274 | |||
275 | $querySql = 'SELECT * FROM (SELECT u.id, u.name ORDER BY u.name DESC, id ASC) dctrn_result'; |
||
276 | $alteredSql = 'SELECT TOP 15 * FROM (SELECT u.id, u.name) dctrn_result'; |
||
277 | $sql = $this->_platform->modifyLimitQuery($querySql, 10, 5); |
||
278 | self::assertEquals(sprintf(self::$selectFromCtePattern, $alteredSql, 6, 15), $sql); |
||
279 | } |
||
280 | |||
281 | View Code Duplication | public function testModifyLimitQueryWithFromColumnNames() |
|
282 | { |
||
283 | $querySql = 'SELECT a.fromFoo, fromBar FROM foo'; |
||
284 | $alteredSql = 'SELECT TOP 10 a.fromFoo, fromBar FROM foo'; |
||
285 | $sql = $this->_platform->modifyLimitQuery($querySql, 10); |
||
286 | self::assertEquals(sprintf(self::$selectFromCtePattern, $alteredSql, 1, 10), $sql); |
||
287 | } |
||
288 | |||
289 | /** |
||
290 | * @group DBAL-927 |
||
291 | */ |
||
292 | View Code Duplication | public function testModifyLimitQueryWithExtraLongQuery() |
|
293 | { |
||
294 | $query = 'SELECT table1.column1, table2.column2, table3.column3, table4.column4, table5.column5, table6.column6, table7.column7, table8.column8 FROM table1, table2, table3, table4, table5, table6, table7, table8 '; |
||
295 | $query.= 'WHERE (table1.column1 = table2.column2) AND (table1.column1 = table3.column3) AND (table1.column1 = table4.column4) AND (table1.column1 = table5.column5) AND (table1.column1 = table6.column6) AND (table1.column1 = table7.column7) AND (table1.column1 = table8.column8) AND (table2.column2 = table3.column3) AND (table2.column2 = table4.column4) AND (table2.column2 = table5.column5) AND (table2.column2 = table6.column6) '; |
||
296 | $query.= 'AND (table2.column2 = table7.column7) AND (table2.column2 = table8.column8) AND (table3.column3 = table4.column4) AND (table3.column3 = table5.column5) AND (table3.column3 = table6.column6) AND (table3.column3 = table7.column7) AND (table3.column3 = table8.column8) AND (table4.column4 = table5.column5) AND (table4.column4 = table6.column6) AND (table4.column4 = table7.column7) AND (table4.column4 = table8.column8) '; |
||
297 | $query.= 'AND (table5.column5 = table6.column6) AND (table5.column5 = table7.column7) AND (table5.column5 = table8.column8) AND (table6.column6 = table7.column7) AND (table6.column6 = table8.column8) AND (table7.column7 = table8.column8)'; |
||
298 | |||
299 | $alteredSql = 'SELECT TOP 10 table1.column1, table2.column2, table3.column3, table4.column4, table5.column5, table6.column6, table7.column7, table8.column8 FROM table1, table2, table3, table4, table5, table6, table7, table8 '; |
||
300 | $alteredSql.= 'WHERE (table1.column1 = table2.column2) AND (table1.column1 = table3.column3) AND (table1.column1 = table4.column4) AND (table1.column1 = table5.column5) AND (table1.column1 = table6.column6) AND (table1.column1 = table7.column7) AND (table1.column1 = table8.column8) AND (table2.column2 = table3.column3) AND (table2.column2 = table4.column4) AND (table2.column2 = table5.column5) AND (table2.column2 = table6.column6) '; |
||
301 | $alteredSql.= 'AND (table2.column2 = table7.column7) AND (table2.column2 = table8.column8) AND (table3.column3 = table4.column4) AND (table3.column3 = table5.column5) AND (table3.column3 = table6.column6) AND (table3.column3 = table7.column7) AND (table3.column3 = table8.column8) AND (table4.column4 = table5.column5) AND (table4.column4 = table6.column6) AND (table4.column4 = table7.column7) AND (table4.column4 = table8.column8) '; |
||
302 | $alteredSql.= 'AND (table5.column5 = table6.column6) AND (table5.column5 = table7.column7) AND (table5.column5 = table8.column8) AND (table6.column6 = table7.column7) AND (table6.column6 = table8.column8) AND (table7.column7 = table8.column8)'; |
||
303 | |||
304 | $sql = $this->_platform->modifyLimitQuery($query, 10); |
||
305 | self::assertEquals(sprintf(self::$selectFromCtePattern, $alteredSql, 1, 10), $sql); |
||
306 | } |
||
307 | |||
308 | /** |
||
309 | * @group DDC-2470 |
||
310 | */ |
||
311 | View Code Duplication | public function testModifyLimitQueryWithOrderByClause() |
|
312 | { |
||
313 | if ( ! $this->_platform->supportsLimitOffset()) { |
||
314 | $this->markTestSkipped(sprintf('Platform "%s" does not support offsets in result limiting.', $this->_platform->getName())); |
||
315 | } |
||
316 | |||
317 | $sql = 'SELECT m0_.NOMBRE AS NOMBRE0, m0_.FECHAINICIO AS FECHAINICIO1, m0_.FECHAFIN AS FECHAFIN2 FROM MEDICION m0_ WITH (NOLOCK) INNER JOIN ESTUDIO e1_ ON m0_.ESTUDIO_ID = e1_.ID INNER JOIN CLIENTE c2_ ON e1_.CLIENTE_ID = c2_.ID INNER JOIN USUARIO u3_ ON c2_.ID = u3_.CLIENTE_ID WHERE u3_.ID = ? ORDER BY m0_.FECHAINICIO DESC'; |
||
318 | $alteredSql = 'SELECT TOP 15 m0_.NOMBRE AS NOMBRE0, m0_.FECHAINICIO AS FECHAINICIO1, m0_.FECHAFIN AS FECHAFIN2 FROM MEDICION m0_ WITH (NOLOCK) INNER JOIN ESTUDIO e1_ ON m0_.ESTUDIO_ID = e1_.ID INNER JOIN CLIENTE c2_ ON e1_.CLIENTE_ID = c2_.ID INNER JOIN USUARIO u3_ ON c2_.ID = u3_.CLIENTE_ID WHERE u3_.ID = ? ORDER BY m0_.FECHAINICIO DESC'; |
||
319 | $actual = $this->_platform->modifyLimitQuery($sql, 10, 5); |
||
320 | |||
321 | self::assertEquals(sprintf(self::$selectFromCtePattern, $alteredSql, 6, 15), $actual); |
||
322 | } |
||
323 | |||
324 | /** |
||
325 | * @group DBAL-713 |
||
326 | */ |
||
327 | View Code Duplication | public function testModifyLimitQueryWithSubSelectInSelectList() |
|
328 | { |
||
329 | $querySql = "SELECT " . |
||
330 | "u.id, " . |
||
331 | "(u.foo/2) foodiv, " . |
||
332 | "CONCAT(u.bar, u.baz) barbaz, " . |
||
333 | "(SELECT (SELECT COUNT(*) FROM login l WHERE l.profile_id = p.id) FROM profile p WHERE p.user_id = u.id) login_count " . |
||
334 | "FROM user u " . |
||
335 | "WHERE u.status = 'disabled'"; |
||
336 | $alteredSql = "SELECT TOP 10 " . |
||
337 | "u.id, " . |
||
338 | "(u.foo/2) foodiv, " . |
||
339 | "CONCAT(u.bar, u.baz) barbaz, " . |
||
340 | "(SELECT (SELECT COUNT(*) FROM login l WHERE l.profile_id = p.id) FROM profile p WHERE p.user_id = u.id) login_count " . |
||
341 | "FROM user u " . |
||
342 | "WHERE u.status = 'disabled'"; |
||
343 | $sql = $this->_platform->modifyLimitQuery($querySql, 10); |
||
344 | |||
345 | self::assertEquals(sprintf(self::$selectFromCtePattern, $alteredSql, 1, 10), $sql); |
||
346 | } |
||
347 | |||
348 | /** |
||
349 | * @group DBAL-713 |
||
350 | */ |
||
351 | public function testModifyLimitQueryWithSubSelectInSelectListAndOrderByClause() |
||
352 | { |
||
353 | if ( ! $this->_platform->supportsLimitOffset()) { |
||
354 | $this->markTestSkipped(sprintf('Platform "%s" does not support offsets in result limiting.', $this->_platform->getName())); |
||
355 | } |
||
356 | |||
357 | $querySql = "SELECT " . |
||
358 | "u.id, " . |
||
359 | "(u.foo/2) foodiv, " . |
||
360 | "CONCAT(u.bar, u.baz) barbaz, " . |
||
361 | "(SELECT (SELECT COUNT(*) FROM login l WHERE l.profile_id = p.id) FROM profile p WHERE p.user_id = u.id) login_count " . |
||
362 | "FROM user u " . |
||
363 | "WHERE u.status = 'disabled' " . |
||
364 | "ORDER BY u.username DESC"; |
||
365 | $alteredSql = "SELECT TOP 15 " . |
||
366 | "u.id, " . |
||
367 | "(u.foo/2) foodiv, " . |
||
368 | "CONCAT(u.bar, u.baz) barbaz, " . |
||
369 | "(SELECT (SELECT COUNT(*) FROM login l WHERE l.profile_id = p.id) FROM profile p WHERE p.user_id = u.id) login_count " . |
||
370 | "FROM user u " . |
||
371 | "WHERE u.status = 'disabled' " . |
||
372 | "ORDER BY u.username DESC"; |
||
373 | $sql = $this->_platform->modifyLimitQuery($querySql, 10, 5); |
||
374 | self::assertEquals(sprintf(self::$selectFromCtePattern, $alteredSql, 6, 15), $sql); |
||
375 | } |
||
376 | |||
377 | /** |
||
378 | * @group DBAL-834 |
||
379 | */ |
||
380 | View Code Duplication | public function testModifyLimitQueryWithAggregateFunctionInOrderByClause() |
|
381 | { |
||
382 | $querySql = "SELECT " . |
||
383 | "MAX(heading_id) aliased, " . |
||
384 | "code " . |
||
385 | "FROM operator_model_operator " . |
||
386 | "GROUP BY code " . |
||
387 | "ORDER BY MAX(heading_id) DESC"; |
||
388 | $alteredSql = "SELECT TOP 1 " . |
||
389 | "MAX(heading_id) aliased, " . |
||
390 | "code " . |
||
391 | "FROM operator_model_operator " . |
||
392 | "GROUP BY code " . |
||
393 | "ORDER BY MAX(heading_id) DESC"; |
||
394 | $sql = $this->_platform->modifyLimitQuery($querySql, 1, 0); |
||
395 | self::assertEquals(sprintf(self::$selectFromCtePattern, $alteredSql, 1, 1), $sql); |
||
396 | } |
||
397 | |||
398 | /** |
||
399 | * @throws \Doctrine\DBAL\DBALException |
||
400 | */ |
||
401 | View Code Duplication | public function testModifyLimitSubqueryWithJoinAndSubqueryOrderedByColumnFromBaseTable() |
|
402 | { |
||
403 | $querySql = "SELECT DISTINCT id_0, name_1 " |
||
404 | . "FROM (" |
||
405 | . "SELECT t1.id AS id_0, t2.name AS name_1 " |
||
406 | . "FROM table_parent t1 " |
||
407 | . "LEFT JOIN join_table t2 ON t1.id = t2.table_id " |
||
408 | . "ORDER BY t1.id ASC" |
||
409 | . ") dctrn_result " |
||
410 | . "ORDER BY id_0 ASC"; |
||
411 | $alteredSql = "SELECT DISTINCT TOP 5 id_0, name_1 " |
||
412 | . "FROM (" |
||
413 | . "SELECT t1.id AS id_0, t2.name AS name_1 " |
||
414 | . "FROM table_parent t1 " |
||
415 | . "LEFT JOIN join_table t2 ON t1.id = t2.table_id" |
||
416 | . ") dctrn_result " |
||
417 | . "ORDER BY id_0 ASC"; |
||
418 | $sql = $this->_platform->modifyLimitQuery($querySql, 5); |
||
419 | self::assertEquals(sprintf(self::$selectFromCtePattern, $alteredSql, 1, 5), $sql); |
||
420 | } |
||
421 | |||
422 | |||
423 | /** |
||
424 | * @throws \Doctrine\DBAL\DBALException |
||
425 | */ |
||
426 | View Code Duplication | public function testModifyLimitSubqueryWithJoinAndSubqueryOrderedByColumnFromJoinTable() |
|
427 | { |
||
428 | $querySql = "SELECT DISTINCT id_0, name_1 " |
||
429 | . "FROM (" |
||
430 | . "SELECT t1.id AS id_0, t2.name AS name_1 " |
||
431 | . "FROM table_parent t1 " |
||
432 | . "LEFT JOIN join_table t2 ON t1.id = t2.table_id " |
||
433 | . "ORDER BY t2.name ASC" |
||
434 | . ") dctrn_result " |
||
435 | . "ORDER BY name_1 ASC"; |
||
436 | $alteredSql = "SELECT DISTINCT TOP 5 id_0, name_1 " |
||
437 | . "FROM (" |
||
438 | . "SELECT t1.id AS id_0, t2.name AS name_1 " |
||
439 | . "FROM table_parent t1 " |
||
440 | . "LEFT JOIN join_table t2 ON t1.id = t2.table_id" |
||
441 | . ") dctrn_result " |
||
442 | . "ORDER BY name_1 ASC"; |
||
443 | $sql = $this->_platform->modifyLimitQuery($querySql, 5); |
||
444 | self::assertEquals(sprintf(self::$selectFromCtePattern, $alteredSql, 1, 5), $sql); |
||
445 | } |
||
446 | |||
447 | /** |
||
448 | * @throws \Doctrine\DBAL\DBALException |
||
449 | */ |
||
450 | View Code Duplication | public function testModifyLimitSubqueryWithJoinAndSubqueryOrderedByColumnsFromBothTables() |
|
451 | { |
||
452 | $querySql = "SELECT DISTINCT id_0, name_1, foo_2 " |
||
453 | . "FROM (" |
||
454 | . "SELECT t1.id AS id_0, t2.name AS name_1, t2.foo AS foo_2 " |
||
455 | . "FROM table_parent t1 " |
||
456 | . "LEFT JOIN join_table t2 ON t1.id = t2.table_id " |
||
457 | . "ORDER BY t2.name ASC, t2.foo DESC" |
||
458 | . ") dctrn_result " |
||
459 | . "ORDER BY name_1 ASC, foo_2 DESC"; |
||
460 | $alteredSql = "SELECT DISTINCT TOP 5 id_0, name_1, foo_2 " |
||
461 | . "FROM (" |
||
462 | . "SELECT t1.id AS id_0, t2.name AS name_1, t2.foo AS foo_2 " |
||
463 | . "FROM table_parent t1 " |
||
464 | . "LEFT JOIN join_table t2 ON t1.id = t2.table_id" |
||
465 | . ") dctrn_result " |
||
466 | . "ORDER BY name_1 ASC, foo_2 DESC"; |
||
467 | $sql = $this->_platform->modifyLimitQuery($querySql, 5); |
||
468 | self::assertEquals(sprintf(self::$selectFromCtePattern, $alteredSql, 1, 5), $sql); |
||
469 | } |
||
470 | |||
471 | public function testModifyLimitSubquerySimple() |
||
472 | { |
||
473 | $querySql = "SELECT DISTINCT id_0 FROM " |
||
474 | . "(SELECT k0_.id AS id_0, k0_.field AS field_1 " |
||
475 | . "FROM key_table k0_ WHERE (k0_.where_field IN (1))) dctrn_result"; |
||
476 | $alteredSql = "SELECT DISTINCT TOP 20 id_0 FROM (SELECT k0_.id AS id_0, k0_.field AS field_1 " |
||
477 | . "FROM key_table k0_ WHERE (k0_.where_field IN (1))) dctrn_result"; |
||
478 | $sql = $this->_platform->modifyLimitQuery($querySql, 20); |
||
479 | self::assertEquals(sprintf(self::$selectFromCtePattern, $alteredSql, 1, 20), $sql); |
||
480 | } |
||
481 | |||
482 | /** |
||
483 | * @group DDC-1360 |
||
484 | */ |
||
485 | public function testQuoteIdentifier() |
||
486 | { |
||
487 | self::assertEquals('[fo][o]', $this->_platform->quoteIdentifier('fo]o')); |
||
488 | self::assertEquals('[test]', $this->_platform->quoteIdentifier('test')); |
||
489 | self::assertEquals('[test].[test]', $this->_platform->quoteIdentifier('test.test')); |
||
490 | } |
||
491 | |||
492 | /** |
||
493 | * @group DDC-1360 |
||
494 | */ |
||
495 | public function testQuoteSingleIdentifier() |
||
496 | { |
||
497 | self::assertEquals('[fo][o]', $this->_platform->quoteSingleIdentifier('fo]o')); |
||
498 | self::assertEquals('[test]', $this->_platform->quoteSingleIdentifier('test')); |
||
499 | self::assertEquals('[test.test]', $this->_platform->quoteSingleIdentifier('test.test')); |
||
500 | } |
||
501 | |||
502 | /** |
||
503 | * @group DBAL-220 |
||
504 | */ |
||
505 | public function testCreateClusteredIndex() |
||
506 | { |
||
507 | $idx = new \Doctrine\DBAL\Schema\Index('idx', array('id')); |
||
508 | $idx->addFlag('clustered'); |
||
509 | self::assertEquals('CREATE CLUSTERED INDEX idx ON tbl (id)', $this->_platform->getCreateIndexSQL($idx, 'tbl')); |
||
510 | } |
||
511 | |||
512 | /** |
||
513 | * @group DBAL-220 |
||
514 | */ |
||
515 | public function testCreateNonClusteredPrimaryKeyInTable() |
||
516 | { |
||
517 | $table = new \Doctrine\DBAL\Schema\Table("tbl"); |
||
518 | $table->addColumn("id", "integer"); |
||
519 | $table->setPrimaryKey(Array("id")); |
||
520 | $table->getIndex('primary')->addFlag('nonclustered'); |
||
521 | |||
522 | self::assertEquals(array('CREATE TABLE tbl (id INT NOT NULL, PRIMARY KEY NONCLUSTERED (id))'), $this->_platform->getCreateTableSQL($table)); |
||
523 | } |
||
524 | |||
525 | /** |
||
526 | * @group DBAL-220 |
||
527 | */ |
||
528 | public function testCreateNonClusteredPrimaryKey() |
||
529 | { |
||
530 | $idx = new \Doctrine\DBAL\Schema\Index('idx', array('id'), false, true); |
||
531 | $idx->addFlag('nonclustered'); |
||
532 | self::assertEquals('ALTER TABLE tbl ADD PRIMARY KEY NONCLUSTERED (id)', $this->_platform->getCreatePrimaryKeySQL($idx, 'tbl')); |
||
533 | } |
||
534 | |||
535 | public function testAlterAddPrimaryKey() |
||
536 | { |
||
537 | $idx = new \Doctrine\DBAL\Schema\Index('idx', array('id'), false, true); |
||
538 | self::assertEquals('ALTER TABLE tbl ADD PRIMARY KEY (id)', $this->_platform->getCreateIndexSQL($idx, 'tbl')); |
||
539 | } |
||
540 | |||
541 | protected function getQuotedColumnInPrimaryKeySQL() |
||
542 | { |
||
543 | return array( |
||
544 | 'CREATE TABLE [quoted] ([create] NVARCHAR(255) NOT NULL, PRIMARY KEY ([create]))', |
||
545 | ); |
||
546 | } |
||
547 | |||
548 | protected function getQuotedColumnInIndexSQL() |
||
549 | { |
||
550 | return array( |
||
551 | 'CREATE TABLE [quoted] ([create] NVARCHAR(255) NOT NULL)', |
||
552 | 'CREATE INDEX IDX_22660D028FD6E0FB ON [quoted] ([create])', |
||
553 | ); |
||
554 | } |
||
555 | |||
556 | protected function getQuotedNameInIndexSQL() |
||
557 | { |
||
558 | return array( |
||
559 | 'CREATE TABLE test (column1 NVARCHAR(255) NOT NULL)', |
||
560 | 'CREATE INDEX [key] ON test (column1)', |
||
561 | ); |
||
562 | } |
||
563 | |||
564 | protected function getQuotedColumnInForeignKeySQL() |
||
565 | { |
||
566 | return array( |
||
567 | 'CREATE TABLE [quoted] ([create] NVARCHAR(255) NOT NULL, foo NVARCHAR(255) NOT NULL, [bar] NVARCHAR(255) NOT NULL)', |
||
568 | 'ALTER TABLE [quoted] ADD CONSTRAINT FK_WITH_RESERVED_KEYWORD FOREIGN KEY ([create], foo, [bar]) REFERENCES [foreign] ([create], bar, [foo-bar])', |
||
569 | 'ALTER TABLE [quoted] ADD CONSTRAINT FK_WITH_NON_RESERVED_KEYWORD FOREIGN KEY ([create], foo, [bar]) REFERENCES foo ([create], bar, [foo-bar])', |
||
570 | 'ALTER TABLE [quoted] ADD CONSTRAINT FK_WITH_INTENDED_QUOTATION FOREIGN KEY ([create], foo, [bar]) REFERENCES [foo-bar] ([create], bar, [foo-bar])', |
||
571 | ); |
||
572 | } |
||
573 | |||
574 | public function testGetCreateSchemaSQL() |
||
575 | { |
||
576 | $schemaName = 'schema'; |
||
577 | $sql = $this->_platform->getCreateSchemaSQL($schemaName); |
||
578 | self::assertEquals('CREATE SCHEMA ' . $schemaName, $sql); |
||
579 | } |
||
580 | |||
581 | /** |
||
582 | * @group DBAL-543 |
||
583 | */ |
||
584 | public function getCreateTableColumnCommentsSQL() |
||
585 | { |
||
586 | return array( |
||
587 | "CREATE TABLE test (id INT NOT NULL, PRIMARY KEY (id))", |
||
588 | "EXEC sp_addextendedproperty N'MS_Description', N'This is a comment', N'SCHEMA', dbo, N'TABLE', test, N'COLUMN', id", |
||
589 | ); |
||
590 | } |
||
591 | |||
592 | /** |
||
593 | * @group DBAL-543 |
||
594 | */ |
||
595 | public function getAlterTableColumnCommentsSQL() |
||
596 | { |
||
597 | return array( |
||
598 | "ALTER TABLE mytable ADD quota INT NOT NULL", |
||
599 | "EXEC sp_addextendedproperty N'MS_Description', N'A comment', N'SCHEMA', dbo, N'TABLE', mytable, N'COLUMN', quota", |
||
600 | // todo |
||
601 | //"EXEC sp_addextendedproperty N'MS_Description', N'B comment', N'SCHEMA', dbo, N'TABLE', mytable, N'COLUMN', baz", |
||
602 | ); |
||
603 | } |
||
604 | |||
605 | /** |
||
606 | * @group DBAL-543 |
||
607 | */ |
||
608 | public function getCreateTableColumnTypeCommentsSQL() |
||
609 | { |
||
610 | return array( |
||
611 | "CREATE TABLE test (id INT NOT NULL, data VARCHAR(MAX) NOT NULL, PRIMARY KEY (id))", |
||
612 | "EXEC sp_addextendedproperty N'MS_Description', N'(DC2Type:array)', N'SCHEMA', dbo, N'TABLE', test, N'COLUMN', data", |
||
613 | ); |
||
614 | } |
||
615 | |||
616 | /** |
||
617 | * @group DBAL-543 |
||
618 | */ |
||
619 | public function testGeneratesCreateTableSQLWithColumnComments() |
||
620 | { |
||
621 | $table = new Table('mytable'); |
||
622 | $table->addColumn('id', 'integer', array('autoincrement' => true)); |
||
623 | $table->addColumn('comment_null', 'integer', array('comment' => null)); |
||
624 | $table->addColumn('comment_false', 'integer', array('comment' => false)); |
||
625 | $table->addColumn('comment_empty_string', 'integer', array('comment' => '')); |
||
626 | $table->addColumn('comment_integer_0', 'integer', array('comment' => 0)); |
||
627 | $table->addColumn('comment_float_0', 'integer', array('comment' => 0.0)); |
||
628 | $table->addColumn('comment_string_0', 'integer', array('comment' => '0')); |
||
629 | $table->addColumn('comment', 'integer', array('comment' => 'Doctrine 0wnz you!')); |
||
630 | $table->addColumn('`comment_quoted`', 'integer', array('comment' => 'Doctrine 0wnz comments for explicitly quoted columns!')); |
||
631 | $table->addColumn('create', 'integer', array('comment' => 'Doctrine 0wnz comments for reserved keyword columns!')); |
||
632 | $table->addColumn('commented_type', 'object'); |
||
633 | $table->addColumn('commented_type_with_comment', 'array', array('comment' => 'Doctrine array type.')); |
||
634 | $table->addColumn('comment_with_string_literal_char', 'string', array('comment' => "O'Reilly")); |
||
635 | $table->setPrimaryKey(array('id')); |
||
636 | |||
637 | self::assertEquals( |
||
638 | array( |
||
639 | "CREATE TABLE mytable (id INT IDENTITY NOT NULL, comment_null INT NOT NULL, comment_false INT NOT NULL, comment_empty_string INT NOT NULL, comment_integer_0 INT NOT NULL, comment_float_0 INT NOT NULL, comment_string_0 INT NOT NULL, comment INT NOT NULL, [comment_quoted] INT NOT NULL, [create] INT NOT NULL, commented_type VARCHAR(MAX) NOT NULL, commented_type_with_comment VARCHAR(MAX) NOT NULL, comment_with_string_literal_char NVARCHAR(255) NOT NULL, PRIMARY KEY (id))", |
||
640 | "EXEC sp_addextendedproperty N'MS_Description', N'0', N'SCHEMA', dbo, N'TABLE', mytable, N'COLUMN', comment_integer_0", |
||
641 | "EXEC sp_addextendedproperty N'MS_Description', N'0', N'SCHEMA', dbo, N'TABLE', mytable, N'COLUMN', comment_float_0", |
||
642 | "EXEC sp_addextendedproperty N'MS_Description', N'0', N'SCHEMA', dbo, N'TABLE', mytable, N'COLUMN', comment_string_0", |
||
643 | "EXEC sp_addextendedproperty N'MS_Description', N'Doctrine 0wnz you!', N'SCHEMA', dbo, N'TABLE', mytable, N'COLUMN', comment", |
||
644 | "EXEC sp_addextendedproperty N'MS_Description', N'Doctrine 0wnz comments for explicitly quoted columns!', N'SCHEMA', dbo, N'TABLE', mytable, N'COLUMN', [comment_quoted]", |
||
645 | "EXEC sp_addextendedproperty N'MS_Description', N'Doctrine 0wnz comments for reserved keyword columns!', N'SCHEMA', dbo, N'TABLE', mytable, N'COLUMN', [create]", |
||
646 | "EXEC sp_addextendedproperty N'MS_Description', N'(DC2Type:object)', N'SCHEMA', dbo, N'TABLE', mytable, N'COLUMN', commented_type", |
||
647 | "EXEC sp_addextendedproperty N'MS_Description', N'Doctrine array type.(DC2Type:array)', N'SCHEMA', dbo, N'TABLE', mytable, N'COLUMN', commented_type_with_comment", |
||
648 | "EXEC sp_addextendedproperty N'MS_Description', N'O''Reilly', N'SCHEMA', dbo, N'TABLE', mytable, N'COLUMN', comment_with_string_literal_char", |
||
649 | ), |
||
650 | $this->_platform->getCreateTableSQL($table) |
||
651 | ); |
||
652 | } |
||
653 | |||
654 | /** |
||
655 | * @group DBAL-543 |
||
656 | * @group DBAL-1011 |
||
657 | */ |
||
658 | public function testGeneratesAlterTableSQLWithColumnComments() |
||
659 | { |
||
660 | $table = new Table('mytable'); |
||
661 | $table->addColumn('id', 'integer', array('autoincrement' => true)); |
||
662 | $table->addColumn('comment_null', 'integer', array('comment' => null)); |
||
663 | $table->addColumn('comment_false', 'integer', array('comment' => false)); |
||
664 | $table->addColumn('comment_empty_string', 'integer', array('comment' => '')); |
||
665 | $table->addColumn('comment_integer_0', 'integer', array('comment' => 0)); |
||
666 | $table->addColumn('comment_float_0', 'integer', array('comment' => 0.0)); |
||
667 | $table->addColumn('comment_string_0', 'integer', array('comment' => '0')); |
||
668 | $table->addColumn('comment', 'integer', array('comment' => 'Doctrine 0wnz you!')); |
||
669 | $table->addColumn('`comment_quoted`', 'integer', array('comment' => 'Doctrine 0wnz comments for explicitly quoted columns!')); |
||
670 | $table->addColumn('create', 'integer', array('comment' => 'Doctrine 0wnz comments for reserved keyword columns!')); |
||
671 | $table->addColumn('commented_type', 'object'); |
||
672 | $table->addColumn('commented_type_with_comment', 'array', array('comment' => 'Doctrine array type.')); |
||
673 | $table->addColumn('comment_with_string_literal_quote_char', 'array', array('comment' => "O'Reilly")); |
||
674 | $table->setPrimaryKey(array('id')); |
||
675 | |||
676 | $tableDiff = new TableDiff('mytable'); |
||
677 | $tableDiff->fromTable = $table; |
||
678 | $tableDiff->addedColumns['added_comment_none'] = new Column('added_comment_none', Type::getType('integer')); |
||
679 | $tableDiff->addedColumns['added_comment_null'] = new Column('added_comment_null', Type::getType('integer'), array('comment' => null)); |
||
680 | $tableDiff->addedColumns['added_comment_false'] = new Column('added_comment_false', Type::getType('integer'), array('comment' => false)); |
||
681 | $tableDiff->addedColumns['added_comment_empty_string'] = new Column('added_comment_empty_string', Type::getType('integer'), array('comment' => '')); |
||
682 | $tableDiff->addedColumns['added_comment_integer_0'] = new Column('added_comment_integer_0', Type::getType('integer'), array('comment' => 0)); |
||
683 | $tableDiff->addedColumns['added_comment_float_0'] = new Column('added_comment_float_0', Type::getType('integer'), array('comment' => 0.0)); |
||
684 | $tableDiff->addedColumns['added_comment_string_0'] = new Column('added_comment_string_0', Type::getType('integer'), array('comment' => '0')); |
||
685 | $tableDiff->addedColumns['added_comment'] = new Column('added_comment', Type::getType('integer'), array('comment' => 'Doctrine')); |
||
686 | $tableDiff->addedColumns['`added_comment_quoted`'] = new Column('`added_comment_quoted`', Type::getType('integer'), array('comment' => 'rulez')); |
||
687 | $tableDiff->addedColumns['select'] = new Column('select', Type::getType('integer'), array('comment' => '666')); |
||
688 | $tableDiff->addedColumns['added_commented_type'] = new Column('added_commented_type', Type::getType('object')); |
||
689 | $tableDiff->addedColumns['added_commented_type_with_comment'] = new Column('added_commented_type_with_comment', Type::getType('array'), array('comment' => '666')); |
||
690 | $tableDiff->addedColumns['added_comment_with_string_literal_char'] = new Column('added_comment_with_string_literal_char', Type::getType('string'), array('comment' => "''")); |
||
691 | |||
692 | $tableDiff->renamedColumns['comment_float_0'] = new Column('comment_double_0', Type::getType('decimal'), array('comment' => 'Double for real!')); |
||
693 | |||
694 | // Add comment to non-commented column. |
||
695 | $tableDiff->changedColumns['id'] = new ColumnDiff( |
||
696 | 'id', |
||
697 | new Column('id', Type::getType('integer'), array('autoincrement' => true, 'comment' => 'primary')), |
||
698 | array('comment'), |
||
699 | new Column('id', Type::getType('integer'), array('autoincrement' => true)) |
||
700 | ); |
||
701 | |||
702 | // Remove comment from null-commented column. |
||
703 | $tableDiff->changedColumns['comment_null'] = new ColumnDiff( |
||
704 | 'comment_null', |
||
705 | new Column('comment_null', Type::getType('string')), |
||
706 | array('type'), |
||
707 | new Column('comment_null', Type::getType('integer'), array('comment' => null)) |
||
708 | ); |
||
709 | |||
710 | // Add comment to false-commented column. |
||
711 | $tableDiff->changedColumns['comment_false'] = new ColumnDiff( |
||
712 | 'comment_false', |
||
713 | new Column('comment_false', Type::getType('integer'), array('comment' => 'false')), |
||
714 | array('comment'), |
||
715 | new Column('comment_false', Type::getType('integer'), array('comment' => false)) |
||
716 | ); |
||
717 | |||
718 | // Change type to custom type from empty string commented column. |
||
719 | $tableDiff->changedColumns['comment_empty_string'] = new ColumnDiff( |
||
720 | 'comment_empty_string', |
||
721 | new Column('comment_empty_string', Type::getType('object')), |
||
722 | array('type'), |
||
723 | new Column('comment_empty_string', Type::getType('integer'), array('comment' => '')) |
||
724 | ); |
||
725 | |||
726 | // Change comment to false-comment from zero-string commented column. |
||
727 | $tableDiff->changedColumns['comment_string_0'] = new ColumnDiff( |
||
728 | 'comment_string_0', |
||
729 | new Column('comment_string_0', Type::getType('integer'), array('comment' => false)), |
||
730 | array('comment'), |
||
731 | new Column('comment_string_0', Type::getType('integer'), array('comment' => '0')) |
||
732 | ); |
||
733 | |||
734 | // Remove comment from regular commented column. |
||
735 | $tableDiff->changedColumns['comment'] = new ColumnDiff( |
||
736 | 'comment', |
||
737 | new Column('comment', Type::getType('integer')), |
||
738 | array('comment'), |
||
739 | new Column('comment', Type::getType('integer'), array('comment' => 'Doctrine 0wnz you!')) |
||
740 | ); |
||
741 | |||
742 | // Change comment and change type to custom type from regular commented column. |
||
743 | $tableDiff->changedColumns['`comment_quoted`'] = new ColumnDiff( |
||
744 | '`comment_quoted`', |
||
745 | new Column('`comment_quoted`', Type::getType('array'), array('comment' => 'Doctrine array.')), |
||
746 | array('comment', 'type'), |
||
747 | new Column('`comment_quoted`', Type::getType('integer'), array('comment' => 'Doctrine 0wnz you!')) |
||
748 | ); |
||
749 | |||
750 | // Remove comment and change type to custom type from regular commented column. |
||
751 | $tableDiff->changedColumns['create'] = new ColumnDiff( |
||
752 | 'create', |
||
753 | new Column('create', Type::getType('object')), |
||
754 | array('comment', 'type'), |
||
755 | new Column('create', Type::getType('integer'), array('comment' => 'Doctrine 0wnz comments for reserved keyword columns!')) |
||
756 | ); |
||
757 | |||
758 | // Add comment and change custom type to regular type from non-commented column. |
||
759 | $tableDiff->changedColumns['commented_type'] = new ColumnDiff( |
||
760 | 'commented_type', |
||
761 | new Column('commented_type', Type::getType('integer'), array('comment' => 'foo')), |
||
762 | array('comment', 'type'), |
||
763 | new Column('commented_type', Type::getType('object')) |
||
764 | ); |
||
765 | |||
766 | // Remove comment from commented custom type column. |
||
767 | $tableDiff->changedColumns['commented_type_with_comment'] = new ColumnDiff( |
||
768 | 'commented_type_with_comment', |
||
769 | new Column('commented_type_with_comment', Type::getType('array')), |
||
770 | array('comment'), |
||
771 | new Column('commented_type_with_comment', Type::getType('array'), array('comment' => 'Doctrine array type.')) |
||
772 | ); |
||
773 | |||
774 | // Change comment from comment with string literal char column. |
||
775 | $tableDiff->changedColumns['comment_with_string_literal_char'] = new ColumnDiff( |
||
776 | 'comment_with_string_literal_char', |
||
777 | new Column('comment_with_string_literal_char', Type::getType('string'), array('comment' => "'")), |
||
778 | array('comment'), |
||
779 | new Column('comment_with_string_literal_char', Type::getType('array'), array('comment' => "O'Reilly")) |
||
780 | ); |
||
781 | |||
782 | $tableDiff->removedColumns['comment_integer_0'] = new Column('comment_integer_0', Type::getType('integer'), array('comment' => 0)); |
||
783 | |||
784 | self::assertEquals( |
||
785 | array( |
||
786 | // Renamed columns. |
||
787 | "sp_RENAME 'mytable.comment_float_0', 'comment_double_0', 'COLUMN'", |
||
788 | |||
789 | // Added columns. |
||
790 | "ALTER TABLE mytable ADD added_comment_none INT NOT NULL", |
||
791 | "ALTER TABLE mytable ADD added_comment_null INT NOT NULL", |
||
792 | "ALTER TABLE mytable ADD added_comment_false INT NOT NULL", |
||
793 | "ALTER TABLE mytable ADD added_comment_empty_string INT NOT NULL", |
||
794 | "ALTER TABLE mytable ADD added_comment_integer_0 INT NOT NULL", |
||
795 | "ALTER TABLE mytable ADD added_comment_float_0 INT NOT NULL", |
||
796 | "ALTER TABLE mytable ADD added_comment_string_0 INT NOT NULL", |
||
797 | "ALTER TABLE mytable ADD added_comment INT NOT NULL", |
||
798 | "ALTER TABLE mytable ADD [added_comment_quoted] INT NOT NULL", |
||
799 | "ALTER TABLE mytable ADD [select] INT NOT NULL", |
||
800 | "ALTER TABLE mytable ADD added_commented_type VARCHAR(MAX) NOT NULL", |
||
801 | "ALTER TABLE mytable ADD added_commented_type_with_comment VARCHAR(MAX) NOT NULL", |
||
802 | "ALTER TABLE mytable ADD added_comment_with_string_literal_char NVARCHAR(255) NOT NULL", |
||
803 | "ALTER TABLE mytable DROP COLUMN comment_integer_0", |
||
804 | "ALTER TABLE mytable ALTER COLUMN comment_null NVARCHAR(255) NOT NULL", |
||
805 | "ALTER TABLE mytable ALTER COLUMN comment_empty_string VARCHAR(MAX) NOT NULL", |
||
806 | "ALTER TABLE mytable ALTER COLUMN [comment_quoted] VARCHAR(MAX) NOT NULL", |
||
807 | "ALTER TABLE mytable ALTER COLUMN [create] VARCHAR(MAX) NOT NULL", |
||
808 | "ALTER TABLE mytable ALTER COLUMN commented_type INT NOT NULL", |
||
809 | |||
810 | // Added columns. |
||
811 | "EXEC sp_addextendedproperty N'MS_Description', N'0', N'SCHEMA', dbo, N'TABLE', mytable, N'COLUMN', added_comment_integer_0", |
||
812 | "EXEC sp_addextendedproperty N'MS_Description', N'0', N'SCHEMA', dbo, N'TABLE', mytable, N'COLUMN', added_comment_float_0", |
||
813 | "EXEC sp_addextendedproperty N'MS_Description', N'0', N'SCHEMA', dbo, N'TABLE', mytable, N'COLUMN', added_comment_string_0", |
||
814 | "EXEC sp_addextendedproperty N'MS_Description', N'Doctrine', N'SCHEMA', dbo, N'TABLE', mytable, N'COLUMN', added_comment", |
||
815 | "EXEC sp_addextendedproperty N'MS_Description', N'rulez', N'SCHEMA', dbo, N'TABLE', mytable, N'COLUMN', [added_comment_quoted]", |
||
816 | "EXEC sp_addextendedproperty N'MS_Description', N'666', N'SCHEMA', dbo, N'TABLE', mytable, N'COLUMN', [select]", |
||
817 | "EXEC sp_addextendedproperty N'MS_Description', N'(DC2Type:object)', N'SCHEMA', dbo, N'TABLE', mytable, N'COLUMN', added_commented_type", |
||
818 | "EXEC sp_addextendedproperty N'MS_Description', N'666(DC2Type:array)', N'SCHEMA', dbo, N'TABLE', mytable, N'COLUMN', added_commented_type_with_comment", |
||
819 | "EXEC sp_addextendedproperty N'MS_Description', N'''''', N'SCHEMA', dbo, N'TABLE', mytable, N'COLUMN', added_comment_with_string_literal_char", |
||
820 | |||
821 | // Changed columns. |
||
822 | "EXEC sp_addextendedproperty N'MS_Description', N'primary', N'SCHEMA', dbo, N'TABLE', mytable, N'COLUMN', id", |
||
823 | "EXEC sp_addextendedproperty N'MS_Description', N'false', N'SCHEMA', dbo, N'TABLE', mytable, N'COLUMN', comment_false", |
||
824 | "EXEC sp_addextendedproperty N'MS_Description', N'(DC2Type:object)', N'SCHEMA', dbo, N'TABLE', mytable, N'COLUMN', comment_empty_string", |
||
825 | "EXEC sp_dropextendedproperty N'MS_Description', N'SCHEMA', dbo, N'TABLE', mytable, N'COLUMN', comment_string_0", |
||
826 | "EXEC sp_dropextendedproperty N'MS_Description', N'SCHEMA', dbo, N'TABLE', mytable, N'COLUMN', comment", |
||
827 | "EXEC sp_updateextendedproperty N'MS_Description', N'Doctrine array.(DC2Type:array)', N'SCHEMA', dbo, N'TABLE', mytable, N'COLUMN', [comment_quoted]", |
||
828 | "EXEC sp_updateextendedproperty N'MS_Description', N'(DC2Type:object)', N'SCHEMA', dbo, N'TABLE', mytable, N'COLUMN', [create]", |
||
829 | "EXEC sp_updateextendedproperty N'MS_Description', N'foo', N'SCHEMA', dbo, N'TABLE', mytable, N'COLUMN', commented_type", |
||
830 | "EXEC sp_updateextendedproperty N'MS_Description', N'(DC2Type:array)', N'SCHEMA', dbo, N'TABLE', mytable, N'COLUMN', commented_type_with_comment", |
||
831 | "EXEC sp_updateextendedproperty N'MS_Description', N'''', N'SCHEMA', dbo, N'TABLE', mytable, N'COLUMN', comment_with_string_literal_char", |
||
832 | ), |
||
833 | $this->_platform->getAlterTableSQL($tableDiff) |
||
834 | ); |
||
835 | } |
||
836 | |||
837 | /** |
||
838 | * @group DBAL-122 |
||
839 | */ |
||
840 | public function testInitializesDoctrineTypeMappings() |
||
841 | { |
||
842 | self::assertTrue($this->_platform->hasDoctrineTypeMappingFor('bigint')); |
||
843 | self::assertSame('bigint', $this->_platform->getDoctrineTypeMapping('bigint')); |
||
844 | |||
845 | self::assertTrue($this->_platform->hasDoctrineTypeMappingFor('numeric')); |
||
846 | self::assertSame('decimal', $this->_platform->getDoctrineTypeMapping('numeric')); |
||
847 | |||
848 | self::assertTrue($this->_platform->hasDoctrineTypeMappingFor('bit')); |
||
849 | self::assertSame('boolean', $this->_platform->getDoctrineTypeMapping('bit')); |
||
850 | |||
851 | self::assertTrue($this->_platform->hasDoctrineTypeMappingFor('smallint')); |
||
852 | self::assertSame('smallint', $this->_platform->getDoctrineTypeMapping('smallint')); |
||
853 | |||
854 | self::assertTrue($this->_platform->hasDoctrineTypeMappingFor('decimal')); |
||
855 | self::assertSame('decimal', $this->_platform->getDoctrineTypeMapping('decimal')); |
||
856 | |||
857 | self::assertTrue($this->_platform->hasDoctrineTypeMappingFor('smallmoney')); |
||
858 | self::assertSame('integer', $this->_platform->getDoctrineTypeMapping('smallmoney')); |
||
859 | |||
860 | self::assertTrue($this->_platform->hasDoctrineTypeMappingFor('int')); |
||
861 | self::assertSame('integer', $this->_platform->getDoctrineTypeMapping('int')); |
||
862 | |||
863 | self::assertTrue($this->_platform->hasDoctrineTypeMappingFor('tinyint')); |
||
864 | self::assertSame('smallint', $this->_platform->getDoctrineTypeMapping('tinyint')); |
||
865 | |||
866 | self::assertTrue($this->_platform->hasDoctrineTypeMappingFor('money')); |
||
867 | self::assertSame('integer', $this->_platform->getDoctrineTypeMapping('money')); |
||
868 | |||
869 | self::assertTrue($this->_platform->hasDoctrineTypeMappingFor('float')); |
||
870 | self::assertSame('float', $this->_platform->getDoctrineTypeMapping('float')); |
||
871 | |||
872 | self::assertTrue($this->_platform->hasDoctrineTypeMappingFor('real')); |
||
873 | self::assertSame('float', $this->_platform->getDoctrineTypeMapping('real')); |
||
874 | |||
875 | self::assertTrue($this->_platform->hasDoctrineTypeMappingFor('double')); |
||
876 | self::assertSame('float', $this->_platform->getDoctrineTypeMapping('double')); |
||
877 | |||
878 | self::assertTrue($this->_platform->hasDoctrineTypeMappingFor('double precision')); |
||
879 | self::assertSame('float', $this->_platform->getDoctrineTypeMapping('double precision')); |
||
880 | |||
881 | self::assertTrue($this->_platform->hasDoctrineTypeMappingFor('smalldatetime')); |
||
882 | self::assertSame('datetime', $this->_platform->getDoctrineTypeMapping('smalldatetime')); |
||
883 | |||
884 | self::assertTrue($this->_platform->hasDoctrineTypeMappingFor('datetime')); |
||
885 | self::assertSame('datetime', $this->_platform->getDoctrineTypeMapping('datetime')); |
||
886 | |||
887 | self::assertTrue($this->_platform->hasDoctrineTypeMappingFor('char')); |
||
888 | self::assertSame('string', $this->_platform->getDoctrineTypeMapping('char')); |
||
889 | |||
890 | self::assertTrue($this->_platform->hasDoctrineTypeMappingFor('varchar')); |
||
891 | self::assertSame('string', $this->_platform->getDoctrineTypeMapping('varchar')); |
||
892 | |||
893 | self::assertTrue($this->_platform->hasDoctrineTypeMappingFor('text')); |
||
894 | self::assertSame('text', $this->_platform->getDoctrineTypeMapping('text')); |
||
895 | |||
896 | self::assertTrue($this->_platform->hasDoctrineTypeMappingFor('nchar')); |
||
897 | self::assertSame('string', $this->_platform->getDoctrineTypeMapping('nchar')); |
||
898 | |||
899 | self::assertTrue($this->_platform->hasDoctrineTypeMappingFor('nvarchar')); |
||
900 | self::assertSame('string', $this->_platform->getDoctrineTypeMapping('nvarchar')); |
||
901 | |||
902 | self::assertTrue($this->_platform->hasDoctrineTypeMappingFor('ntext')); |
||
903 | self::assertSame('text', $this->_platform->getDoctrineTypeMapping('ntext')); |
||
904 | |||
905 | self::assertTrue($this->_platform->hasDoctrineTypeMappingFor('binary')); |
||
906 | self::assertSame('binary', $this->_platform->getDoctrineTypeMapping('binary')); |
||
907 | |||
908 | self::assertTrue($this->_platform->hasDoctrineTypeMappingFor('varbinary')); |
||
909 | self::assertSame('binary', $this->_platform->getDoctrineTypeMapping('varbinary')); |
||
910 | |||
911 | self::assertTrue($this->_platform->hasDoctrineTypeMappingFor('image')); |
||
912 | self::assertSame('blob', $this->_platform->getDoctrineTypeMapping('image')); |
||
913 | |||
914 | self::assertTrue($this->_platform->hasDoctrineTypeMappingFor('uniqueidentifier')); |
||
915 | self::assertSame('guid', $this->_platform->getDoctrineTypeMapping('uniqueidentifier')); |
||
916 | } |
||
917 | |||
918 | protected function getBinaryMaxLength() |
||
919 | { |
||
920 | return 8000; |
||
921 | } |
||
922 | |||
923 | View Code Duplication | public function testReturnsBinaryTypeDeclarationSQL() |
|
924 | { |
||
925 | self::assertSame('VARBINARY(255)', $this->_platform->getBinaryTypeDeclarationSQL(array())); |
||
926 | self::assertSame('VARBINARY(255)', $this->_platform->getBinaryTypeDeclarationSQL(array('length' => 0))); |
||
927 | self::assertSame('VARBINARY(8000)', $this->_platform->getBinaryTypeDeclarationSQL(array('length' => 8000))); |
||
928 | self::assertSame('VARBINARY(MAX)', $this->_platform->getBinaryTypeDeclarationSQL(array('length' => 8001))); |
||
929 | |||
930 | self::assertSame('BINARY(255)', $this->_platform->getBinaryTypeDeclarationSQL(array('fixed' => true))); |
||
931 | self::assertSame('BINARY(255)', $this->_platform->getBinaryTypeDeclarationSQL(array('fixed' => true, 'length' => 0))); |
||
932 | self::assertSame('BINARY(8000)', $this->_platform->getBinaryTypeDeclarationSQL(array('fixed' => true, 'length' => 8000))); |
||
933 | self::assertSame('VARBINARY(MAX)', $this->_platform->getBinaryTypeDeclarationSQL(array('fixed' => true, 'length' => 8001))); |
||
934 | } |
||
935 | |||
936 | /** |
||
937 | * @group DBAL-234 |
||
938 | */ |
||
939 | protected function getAlterTableRenameIndexSQL() |
||
940 | { |
||
941 | return array( |
||
942 | "EXEC sp_RENAME N'mytable.idx_foo', N'idx_bar', N'INDEX'", |
||
943 | ); |
||
944 | } |
||
945 | |||
946 | /** |
||
947 | * @group DBAL-234 |
||
948 | */ |
||
949 | protected function getQuotedAlterTableRenameIndexSQL() |
||
950 | { |
||
951 | return array( |
||
952 | "EXEC sp_RENAME N'[table].[create]', N'[select]', N'INDEX'", |
||
953 | "EXEC sp_RENAME N'[table].[foo]', N'[bar]', N'INDEX'", |
||
954 | ); |
||
955 | } |
||
956 | |||
957 | /** |
||
958 | * @group DBAL-825 |
||
959 | */ |
||
960 | public function testChangeColumnsTypeWithDefaultValue() |
||
961 | { |
||
962 | $tableName = 'column_def_change_type'; |
||
963 | $table = new Table($tableName); |
||
964 | |||
965 | $table->addColumn('col_int', 'smallint', array('default' => 666)); |
||
966 | $table->addColumn('col_string', 'string', array('default' => 'foo')); |
||
967 | |||
968 | $tableDiff = new TableDiff($tableName); |
||
969 | $tableDiff->fromTable = $table; |
||
970 | $tableDiff->changedColumns['col_int'] = new ColumnDiff( |
||
971 | 'col_int', |
||
972 | new Column('col_int', Type::getType('integer'), array('default' => 666)), |
||
973 | array('type'), |
||
974 | new Column('col_int', Type::getType('smallint'), array('default' => 666)) |
||
975 | ); |
||
976 | |||
977 | $tableDiff->changedColumns['col_string'] = new ColumnDiff( |
||
978 | 'col_string', |
||
979 | new Column('col_string', Type::getType('string'), array('default' => 666, 'fixed' => true)), |
||
980 | array('fixed'), |
||
981 | new Column('col_string', Type::getType('string'), array('default' => 666)) |
||
982 | ); |
||
983 | |||
984 | $expected = $this->_platform->getAlterTableSQL($tableDiff); |
||
985 | |||
986 | self::assertSame( |
||
987 | $expected, |
||
988 | array( |
||
989 | 'ALTER TABLE column_def_change_type DROP CONSTRAINT DF_829302E0_FA2CB292', |
||
990 | 'ALTER TABLE column_def_change_type ALTER COLUMN col_int INT NOT NULL', |
||
991 | 'ALTER TABLE column_def_change_type ADD CONSTRAINT DF_829302E0_FA2CB292 DEFAULT 666 FOR col_int', |
||
992 | 'ALTER TABLE column_def_change_type DROP CONSTRAINT DF_829302E0_2725A6D0', |
||
993 | 'ALTER TABLE column_def_change_type ALTER COLUMN col_string NCHAR(255) NOT NULL', |
||
994 | "ALTER TABLE column_def_change_type ADD CONSTRAINT DF_829302E0_2725A6D0 DEFAULT '666' FOR col_string", |
||
995 | ) |
||
996 | ); |
||
997 | } |
||
998 | |||
999 | /** |
||
1000 | * {@inheritdoc} |
||
1001 | */ |
||
1002 | protected function getQuotedAlterTableRenameColumnSQL() |
||
1003 | { |
||
1004 | return array( |
||
1005 | "sp_RENAME 'mytable.unquoted1', 'unquoted', 'COLUMN'", |
||
1006 | "sp_RENAME 'mytable.unquoted2', '[where]', 'COLUMN'", |
||
1007 | "sp_RENAME 'mytable.unquoted3', '[foo]', 'COLUMN'", |
||
1008 | "sp_RENAME 'mytable.[create]', 'reserved_keyword', 'COLUMN'", |
||
1009 | "sp_RENAME 'mytable.[table]', '[from]', 'COLUMN'", |
||
1010 | "sp_RENAME 'mytable.[select]', '[bar]', 'COLUMN'", |
||
1011 | "sp_RENAME 'mytable.quoted1', 'quoted', 'COLUMN'", |
||
1012 | "sp_RENAME 'mytable.quoted2', '[and]', 'COLUMN'", |
||
1013 | "sp_RENAME 'mytable.quoted3', '[baz]', 'COLUMN'", |
||
1014 | ); |
||
1015 | } |
||
1016 | |||
1017 | /** |
||
1018 | * {@inheritdoc} |
||
1019 | */ |
||
1020 | protected function getQuotedAlterTableChangeColumnLengthSQL() |
||
1021 | { |
||
1022 | $this->markTestIncomplete('Not implemented yet'); |
||
1023 | } |
||
1024 | |||
1025 | /** |
||
1026 | * @group DBAL-807 |
||
1027 | */ |
||
1028 | protected function getAlterTableRenameIndexInSchemaSQL() |
||
1029 | { |
||
1030 | return array( |
||
1031 | "EXEC sp_RENAME N'myschema.mytable.idx_foo', N'idx_bar', N'INDEX'", |
||
1032 | ); |
||
1033 | } |
||
1034 | |||
1035 | /** |
||
1036 | * @group DBAL-807 |
||
1037 | */ |
||
1038 | protected function getQuotedAlterTableRenameIndexInSchemaSQL() |
||
1039 | { |
||
1040 | return array( |
||
1041 | "EXEC sp_RENAME N'[schema].[table].[create]', N'[select]', N'INDEX'", |
||
1042 | "EXEC sp_RENAME N'[schema].[table].[foo]', N'[bar]', N'INDEX'", |
||
1043 | ); |
||
1044 | } |
||
1045 | |||
1046 | protected function getQuotesDropForeignKeySQL() |
||
1047 | { |
||
1048 | return 'ALTER TABLE [table] DROP CONSTRAINT [select]'; |
||
1049 | } |
||
1050 | |||
1051 | protected function getQuotesDropConstraintSQL() |
||
1052 | { |
||
1053 | return 'ALTER TABLE [table] DROP CONSTRAINT [select]'; |
||
1054 | } |
||
1055 | |||
1056 | /** |
||
1057 | * @dataProvider getGeneratesIdentifierNamesInDefaultConstraintDeclarationSQL |
||
1058 | * @group DBAL-830 |
||
1059 | */ |
||
1060 | public function testGeneratesIdentifierNamesInDefaultConstraintDeclarationSQL($table, $column, $expectedSql) |
||
1061 | { |
||
1062 | self::assertSame($expectedSql, $this->_platform->getDefaultConstraintDeclarationSQL($table, $column)); |
||
0 ignored issues
–
show
Bug
introduced
by
Loading history...
|
|||
1063 | } |
||
1064 | |||
1065 | public function getGeneratesIdentifierNamesInDefaultConstraintDeclarationSQL() |
||
1066 | { |
||
1067 | return array( |
||
1068 | // Unquoted identifiers non-reserved keywords. |
||
1069 | array('mytable', array('name' => 'mycolumn', 'default' => 'foo'), " CONSTRAINT DF_6B2BD609_9BADD926 DEFAULT 'foo' FOR mycolumn"), |
||
1070 | // Quoted identifiers non-reserved keywords. |
||
1071 | array('`mytable`', array('name' => '`mycolumn`', 'default' => 'foo'), " CONSTRAINT DF_6B2BD609_9BADD926 DEFAULT 'foo' FOR [mycolumn]"), |
||
1072 | // Unquoted identifiers reserved keywords. |
||
1073 | array('table', array('name' => 'select', 'default' => 'foo'), " CONSTRAINT DF_F6298F46_4BF2EAC0 DEFAULT 'foo' FOR [select]"), |
||
1074 | // Quoted identifiers reserved keywords. |
||
1075 | array('`table`', array('name' => '`select`', 'default' => 'foo'), " CONSTRAINT DF_F6298F46_4BF2EAC0 DEFAULT 'foo' FOR [select]"), |
||
1076 | ); |
||
1077 | } |
||
1078 | |||
1079 | /** |
||
1080 | * @dataProvider getGeneratesIdentifierNamesInCreateTableSQL |
||
1081 | * @group DBAL-830 |
||
1082 | */ |
||
1083 | public function testGeneratesIdentifierNamesInCreateTableSQL($table, $expectedSql) |
||
1084 | { |
||
1085 | self::assertSame($expectedSql, $this->_platform->getCreateTableSQL($table)); |
||
1086 | } |
||
1087 | |||
1088 | public function getGeneratesIdentifierNamesInCreateTableSQL() |
||
1089 | { |
||
1090 | return array( |
||
1091 | // Unquoted identifiers non-reserved keywords. |
||
1092 | array( |
||
1093 | new Table('mytable', array(new Column('mycolumn', Type::getType('string'), array('default' => 'foo')))), |
||
1094 | array( |
||
1095 | 'CREATE TABLE mytable (mycolumn NVARCHAR(255) NOT NULL)', |
||
1096 | "ALTER TABLE mytable ADD CONSTRAINT DF_6B2BD609_9BADD926 DEFAULT 'foo' FOR mycolumn" |
||
1097 | ) |
||
1098 | ), |
||
1099 | // Quoted identifiers reserved keywords. |
||
1100 | array( |
||
1101 | new Table('`mytable`', array(new Column('`mycolumn`', Type::getType('string'), array('default' => 'foo')))), |
||
1102 | array( |
||
1103 | 'CREATE TABLE [mytable] ([mycolumn] NVARCHAR(255) NOT NULL)', |
||
1104 | "ALTER TABLE [mytable] ADD CONSTRAINT DF_6B2BD609_9BADD926 DEFAULT 'foo' FOR [mycolumn]" |
||
1105 | ) |
||
1106 | ), |
||
1107 | // Unquoted identifiers reserved keywords. |
||
1108 | array( |
||
1109 | new Table('table', array(new Column('select', Type::getType('string'), array('default' => 'foo')))), |
||
1110 | array( |
||
1111 | 'CREATE TABLE [table] ([select] NVARCHAR(255) NOT NULL)', |
||
1112 | "ALTER TABLE [table] ADD CONSTRAINT DF_F6298F46_4BF2EAC0 DEFAULT 'foo' FOR [select]" |
||
1113 | ) |
||
1114 | ), |
||
1115 | // Quoted identifiers reserved keywords. |
||
1116 | array( |
||
1117 | new Table('`table`', array(new Column('`select`', Type::getType('string'), array('default' => 'foo')))), |
||
1118 | array( |
||
1119 | 'CREATE TABLE [table] ([select] NVARCHAR(255) NOT NULL)', |
||
1120 | "ALTER TABLE [table] ADD CONSTRAINT DF_F6298F46_4BF2EAC0 DEFAULT 'foo' FOR [select]" |
||
1121 | ) |
||
1122 | ), |
||
1123 | ); |
||
1124 | } |
||
1125 | |||
1126 | /** |
||
1127 | * @dataProvider getGeneratesIdentifierNamesInAlterTableSQL |
||
1128 | * @group DBAL-830 |
||
1129 | */ |
||
1130 | public function testGeneratesIdentifierNamesInAlterTableSQL($tableDiff, $expectedSql) |
||
1131 | { |
||
1132 | self::assertSame($expectedSql, $this->_platform->getAlterTableSQL($tableDiff)); |
||
1133 | } |
||
1134 | |||
1135 | public function getGeneratesIdentifierNamesInAlterTableSQL() |
||
1136 | { |
||
1137 | return array( |
||
1138 | // Unquoted identifiers non-reserved keywords. |
||
1139 | array( |
||
1140 | new TableDiff( |
||
1141 | 'mytable', |
||
1142 | array(new Column('addcolumn', Type::getType('string'), array('default' => 'foo'))), |
||
1143 | array( |
||
1144 | 'mycolumn' => new ColumnDiff( |
||
1145 | 'mycolumn', |
||
1146 | new Column('mycolumn', Type::getType('string'), array('default' => 'bar')), |
||
1147 | array('default'), |
||
1148 | new Column('mycolumn', Type::getType('string'), array('default' => 'foo')) |
||
1149 | ) |
||
1150 | ), |
||
1151 | array(new Column('removecolumn', Type::getType('string'), array('default' => 'foo'))) |
||
1152 | ), |
||
1153 | array( |
||
1154 | 'ALTER TABLE mytable ADD addcolumn NVARCHAR(255) NOT NULL', |
||
1155 | "ALTER TABLE mytable ADD CONSTRAINT DF_6B2BD609_4AD86123 DEFAULT 'foo' FOR addcolumn", |
||
1156 | 'ALTER TABLE mytable DROP COLUMN removecolumn', |
||
1157 | 'ALTER TABLE mytable DROP CONSTRAINT DF_6B2BD609_9BADD926', |
||
1158 | 'ALTER TABLE mytable ALTER COLUMN mycolumn NVARCHAR(255) NOT NULL', |
||
1159 | "ALTER TABLE mytable ADD CONSTRAINT DF_6B2BD609_9BADD926 DEFAULT 'bar' FOR mycolumn" |
||
1160 | ) |
||
1161 | ), |
||
1162 | // Quoted identifiers non-reserved keywords. |
||
1163 | array( |
||
1164 | new TableDiff( |
||
1165 | '`mytable`', |
||
1166 | array(new Column('`addcolumn`', Type::getType('string'), array('default' => 'foo'))), |
||
1167 | array( |
||
1168 | 'mycolumn' => new ColumnDiff( |
||
1169 | '`mycolumn`', |
||
1170 | new Column('`mycolumn`', Type::getType('string'), array('default' => 'bar')), |
||
1171 | array('default'), |
||
1172 | new Column('`mycolumn`', Type::getType('string'), array('default' => 'foo')) |
||
1173 | ) |
||
1174 | ), |
||
1175 | array(new Column('`removecolumn`', Type::getType('string'), array('default' => 'foo'))) |
||
1176 | ), |
||
1177 | array( |
||
1178 | 'ALTER TABLE [mytable] ADD [addcolumn] NVARCHAR(255) NOT NULL', |
||
1179 | "ALTER TABLE [mytable] ADD CONSTRAINT DF_6B2BD609_4AD86123 DEFAULT 'foo' FOR [addcolumn]", |
||
1180 | 'ALTER TABLE [mytable] DROP COLUMN [removecolumn]', |
||
1181 | 'ALTER TABLE [mytable] DROP CONSTRAINT DF_6B2BD609_9BADD926', |
||
1182 | 'ALTER TABLE [mytable] ALTER COLUMN [mycolumn] NVARCHAR(255) NOT NULL', |
||
1183 | "ALTER TABLE [mytable] ADD CONSTRAINT DF_6B2BD609_9BADD926 DEFAULT 'bar' FOR [mycolumn]" |
||
1184 | ) |
||
1185 | ), |
||
1186 | // Unquoted identifiers reserved keywords. |
||
1187 | array( |
||
1188 | new TableDiff( |
||
1189 | 'table', |
||
1190 | array(new Column('add', Type::getType('string'), array('default' => 'foo'))), |
||
1191 | array( |
||
1192 | 'select' => new ColumnDiff( |
||
1193 | 'select', |
||
1194 | new Column('select', Type::getType('string'), array('default' => 'bar')), |
||
1195 | array('default'), |
||
1196 | new Column('select', Type::getType('string'), array('default' => 'foo')) |
||
1197 | ) |
||
1198 | ), |
||
1199 | array(new Column('drop', Type::getType('string'), array('default' => 'foo'))) |
||
1200 | ), |
||
1201 | array( |
||
1202 | 'ALTER TABLE [table] ADD [add] NVARCHAR(255) NOT NULL', |
||
1203 | "ALTER TABLE [table] ADD CONSTRAINT DF_F6298F46_FD1A73E7 DEFAULT 'foo' FOR [add]", |
||
1204 | 'ALTER TABLE [table] DROP COLUMN [drop]', |
||
1205 | 'ALTER TABLE [table] DROP CONSTRAINT DF_F6298F46_4BF2EAC0', |
||
1206 | 'ALTER TABLE [table] ALTER COLUMN [select] NVARCHAR(255) NOT NULL', |
||
1207 | "ALTER TABLE [table] ADD CONSTRAINT DF_F6298F46_4BF2EAC0 DEFAULT 'bar' FOR [select]" |
||
1208 | ) |
||
1209 | ), |
||
1210 | // Quoted identifiers reserved keywords. |
||
1211 | array( |
||
1212 | new TableDiff( |
||
1213 | '`table`', |
||
1214 | array(new Column('`add`', Type::getType('string'), array('default' => 'foo'))), |
||
1215 | array( |
||
1216 | 'select' => new ColumnDiff( |
||
1217 | '`select`', |
||
1218 | new Column('`select`', Type::getType('string'), array('default' => 'bar')), |
||
1219 | array('default'), |
||
1220 | new Column('`select`', Type::getType('string'), array('default' => 'foo')) |
||
1221 | ) |
||
1222 | ), |
||
1223 | array(new Column('`drop`', Type::getType('string'), array('default' => 'foo'))) |
||
1224 | ), |
||
1225 | array( |
||
1226 | 'ALTER TABLE [table] ADD [add] NVARCHAR(255) NOT NULL', |
||
1227 | "ALTER TABLE [table] ADD CONSTRAINT DF_F6298F46_FD1A73E7 DEFAULT 'foo' FOR [add]", |
||
1228 | 'ALTER TABLE [table] DROP COLUMN [drop]', |
||
1229 | 'ALTER TABLE [table] DROP CONSTRAINT DF_F6298F46_4BF2EAC0', |
||
1230 | 'ALTER TABLE [table] ALTER COLUMN [select] NVARCHAR(255) NOT NULL', |
||
1231 | "ALTER TABLE [table] ADD CONSTRAINT DF_F6298F46_4BF2EAC0 DEFAULT 'bar' FOR [select]" |
||
1232 | ) |
||
1233 | ), |
||
1234 | ); |
||
1235 | } |
||
1236 | |||
1237 | /** |
||
1238 | * @group DBAL-423 |
||
1239 | */ |
||
1240 | public function testReturnsGuidTypeDeclarationSQL() |
||
1241 | { |
||
1242 | self::assertSame('UNIQUEIDENTIFIER', $this->_platform->getGuidTypeDeclarationSQL(array())); |
||
1243 | } |
||
1244 | |||
1245 | /** |
||
1246 | * {@inheritdoc} |
||
1247 | */ |
||
1248 | public function getAlterTableRenameColumnSQL() |
||
1249 | { |
||
1250 | return array( |
||
1251 | "sp_RENAME 'foo.bar', 'baz', 'COLUMN'", |
||
1252 | 'ALTER TABLE foo DROP CONSTRAINT DF_8C736521_76FF8CAA', |
||
1253 | 'ALTER TABLE foo ADD CONSTRAINT DF_8C736521_78240498 DEFAULT 666 FOR baz', |
||
1254 | ); |
||
1255 | } |
||
1256 | |||
1257 | /** |
||
1258 | * {@inheritdoc} |
||
1259 | */ |
||
1260 | protected function getQuotesTableIdentifiersInAlterTableSQL() |
||
1261 | { |
||
1262 | return array( |
||
1263 | 'ALTER TABLE [foo] DROP CONSTRAINT fk1', |
||
1264 | 'ALTER TABLE [foo] DROP CONSTRAINT fk2', |
||
1265 | "sp_RENAME '[foo].id', 'war', 'COLUMN'", |
||
1266 | 'ALTER TABLE [foo] ADD bloo INT NOT NULL', |
||
1267 | 'ALTER TABLE [foo] DROP COLUMN baz', |
||
1268 | 'ALTER TABLE [foo] ALTER COLUMN bar INT', |
||
1269 | "sp_RENAME '[foo]', 'table'", |
||
1270 | "DECLARE @sql NVARCHAR(MAX) = N''; " . |
||
1271 | "SELECT @sql += N'EXEC sp_rename N''' + dc.name + ''', N''' + REPLACE(dc.name, '8C736521', 'F6298F46') + ''', " . |
||
1272 | "''OBJECT'';' FROM sys.default_constraints dc JOIN sys.tables tbl ON dc.parent_object_id = tbl.object_id " . |
||
1273 | "WHERE tbl.name = 'table';EXEC sp_executesql @sql", |
||
1274 | 'ALTER TABLE [table] ADD CONSTRAINT fk_add FOREIGN KEY (fk3) REFERENCES fk_table (id)', |
||
1275 | 'ALTER TABLE [table] ADD CONSTRAINT fk2 FOREIGN KEY (fk2) REFERENCES fk_table2 (id)', |
||
1276 | ); |
||
1277 | } |
||
1278 | |||
1279 | /** |
||
1280 | * {@inheritdoc} |
||
1281 | */ |
||
1282 | protected function getCommentOnColumnSQL() |
||
1283 | { |
||
1284 | return array( |
||
1285 | "COMMENT ON COLUMN foo.bar IS 'comment'", |
||
1286 | "COMMENT ON COLUMN [Foo].[BAR] IS 'comment'", |
||
1287 | "COMMENT ON COLUMN [select].[from] IS 'comment'", |
||
1288 | ); |
||
1289 | } |
||
1290 | |||
1291 | /** |
||
1292 | * {@inheritdoc} |
||
1293 | */ |
||
1294 | View Code Duplication | public function getReturnsForeignKeyReferentialActionSQL() |
|
1295 | { |
||
1296 | return array( |
||
1297 | array('CASCADE', 'CASCADE'), |
||
1298 | array('SET NULL', 'SET NULL'), |
||
1299 | array('NO ACTION', 'NO ACTION'), |
||
1300 | array('RESTRICT', 'NO ACTION'), |
||
1301 | array('SET DEFAULT', 'SET DEFAULT'), |
||
1302 | array('CaScAdE', 'CASCADE'), |
||
1303 | ); |
||
1304 | } |
||
1305 | |||
1306 | /** |
||
1307 | * {@inheritdoc} |
||
1308 | */ |
||
1309 | protected function getQuotesReservedKeywordInUniqueConstraintDeclarationSQL() |
||
1310 | { |
||
1311 | return 'CONSTRAINT [select] UNIQUE (foo) WHERE foo IS NOT NULL'; |
||
1312 | } |
||
1313 | |||
1314 | /** |
||
1315 | * {@inheritdoc} |
||
1316 | */ |
||
1317 | protected function getQuotesReservedKeywordInIndexDeclarationSQL() |
||
1318 | { |
||
1319 | return 'INDEX [select] (foo)'; |
||
1320 | } |
||
1321 | |||
1322 | /** |
||
1323 | * {@inheritdoc} |
||
1324 | */ |
||
1325 | protected function getQuotesReservedKeywordInTruncateTableSQL() |
||
1326 | { |
||
1327 | return 'TRUNCATE TABLE [select]'; |
||
1328 | } |
||
1329 | |||
1330 | /** |
||
1331 | * {@inheritdoc} |
||
1332 | */ |
||
1333 | protected function getAlterStringToFixedStringSQL() |
||
1334 | { |
||
1335 | return array( |
||
1336 | 'ALTER TABLE mytable ALTER COLUMN name NCHAR(2) NOT NULL', |
||
1337 | ); |
||
1338 | } |
||
1339 | |||
1340 | /** |
||
1341 | * {@inheritdoc} |
||
1342 | */ |
||
1343 | protected function getGeneratesAlterTableRenameIndexUsedByForeignKeySQL() |
||
1344 | { |
||
1345 | return array( |
||
1346 | "EXEC sp_RENAME N'mytable.idx_foo', N'idx_foo_renamed', N'INDEX'", |
||
1347 | ); |
||
1348 | } |
||
1349 | |||
1350 | View Code Duplication | public function testModifyLimitQueryWithTopNSubQueryWithOrderBy() |
|
1351 | { |
||
1352 | $querySql = 'SELECT * FROM test t WHERE t.id = (SELECT TOP 1 t2.id FROM test t2 ORDER BY t2.data DESC)'; |
||
1353 | $alteredSql = 'SELECT TOP 10 * FROM test t WHERE t.id = (SELECT TOP 1 t2.id FROM test t2 ORDER BY t2.data DESC)'; |
||
1354 | $sql = $this->_platform->modifyLimitQuery($querySql, 10); |
||
1355 | self::assertEquals(sprintf(static::$selectFromCtePattern, $alteredSql, 1, 10), $sql); |
||
1356 | |||
1357 | $querySql = 'SELECT * FROM test t WHERE t.id = (SELECT TOP 1 t2.id FROM test t2 ORDER BY t2.data DESC) ORDER BY t.data2 DESC'; |
||
1358 | $alteredSql = 'SELECT TOP 10 * FROM test t WHERE t.id = (SELECT TOP 1 t2.id FROM test t2 ORDER BY t2.data DESC) ORDER BY t.data2 DESC'; |
||
1359 | $sql = $this->_platform->modifyLimitQuery($querySql, 10); |
||
1360 | self::assertEquals(sprintf(static::$selectFromCtePattern, $alteredSql, 1, 10), $sql); |
||
1361 | } |
||
1362 | |||
1363 | /** |
||
1364 | * @group DBAL-2436 |
||
1365 | */ |
||
1366 | public function testQuotesTableNameInListTableColumnsSQL() |
||
1367 | { |
||
1368 | self::assertContains("'Foo''Bar\\'", $this->_platform->getListTableColumnsSQL("Foo'Bar\\"), '', true); |
||
1369 | } |
||
1370 | |||
1371 | /** |
||
1372 | * @group DBAL-2436 |
||
1373 | */ |
||
1374 | public function testQuotesSchemaNameInListTableColumnsSQL() |
||
1375 | { |
||
1376 | self::assertContains( |
||
1377 | "'Foo''Bar\\'", |
||
1378 | $this->_platform->getListTableColumnsSQL("Foo'Bar\\.baz_table"), |
||
1379 | '', |
||
1380 | true |
||
1381 | ); |
||
1382 | } |
||
1383 | |||
1384 | /** |
||
1385 | * @group DBAL-2436 |
||
1386 | */ |
||
1387 | public function testQuotesTableNameInListTableForeignKeysSQL() |
||
1388 | { |
||
1389 | self::assertContains("'Foo''Bar\\'", $this->_platform->getListTableForeignKeysSQL("Foo'Bar\\"), '', true); |
||
1390 | } |
||
1391 | |||
1392 | /** |
||
1393 | * @group DBAL-2436 |
||
1394 | */ |
||
1395 | public function testQuotesSchemaNameInListTableForeignKeysSQL() |
||
1396 | { |
||
1397 | self::assertContains( |
||
1398 | "'Foo''Bar\\'", |
||
1399 | $this->_platform->getListTableForeignKeysSQL("Foo'Bar\\.baz_table"), |
||
1400 | '', |
||
1401 | true |
||
1402 | ); |
||
1403 | } |
||
1404 | |||
1405 | /** |
||
1406 | * @group DBAL-2436 |
||
1407 | */ |
||
1408 | public function testQuotesTableNameInListTableIndexesSQL() |
||
1409 | { |
||
1410 | self::assertContains("'Foo''Bar\\'", $this->_platform->getListTableIndexesSQL("Foo'Bar\\"), '', true); |
||
1411 | } |
||
1412 | |||
1413 | /** |
||
1414 | * @group DBAL-2436 |
||
1415 | */ |
||
1416 | public function testQuotesSchemaNameInListTableIndexesSQL() |
||
1417 | { |
||
1418 | self::assertContains( |
||
1419 | "'Foo''Bar\\'", |
||
1420 | $this->_platform->getListTableIndexesSQL("Foo'Bar\\.baz_table"), |
||
1421 | '', |
||
1422 | true |
||
1423 | ); |
||
1424 | } |
||
1425 | |||
1426 | /** |
||
1427 | * @group 2859 |
||
1428 | */ |
||
1429 | View Code Duplication | public function testGetDefaultValueDeclarationSQLForDateType() : void |
|
1430 | { |
||
1431 | $currentDateSql = $this->_platform->getCurrentDateSQL(); |
||
1432 | foreach (['date', 'date_immutable'] as $type) { |
||
1433 | $field = [ |
||
1434 | 'type' => Type::getType($type), |
||
1435 | 'default' => $currentDateSql, |
||
1436 | ]; |
||
1437 | |||
1438 | self::assertSame( |
||
1439 | " DEFAULT '" . $currentDateSql . "'", |
||
1440 | $this->_platform->getDefaultValueDeclarationSQL($field) |
||
1441 | ); |
||
1442 | } |
||
1443 | } |
||
1444 | } |
||
1445 |