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() |
|
0 ignored issues
–
show
|
|||
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)); |
||
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 |
Duplicated code is one of the most pungent code smells. If you need to duplicate the same code in three or more different places, we strongly encourage you to look into extracting the code into a single class or operation.
You can also find more detailed suggestions in the “Code” section of your repository.