|
1
|
|
|
<?php |
|
2
|
|
|
|
|
3
|
|
|
namespace Doctrine\Tests\DBAL\Platforms; |
|
4
|
|
|
|
|
5
|
|
|
use Doctrine\DBAL\Platforms\SQLServer2012Platform; |
|
6
|
|
|
use Doctrine\DBAL\Schema\Sequence; |
|
7
|
|
|
use Doctrine\DBAL\Types\Type; |
|
8
|
|
|
|
|
9
|
|
|
class SQLServer2012PlatformTest extends AbstractSQLServerPlatformTestCase |
|
10
|
|
|
{ |
|
11
|
|
|
public function createPlatform() |
|
12
|
|
|
{ |
|
13
|
|
|
return new SQLServer2012Platform; |
|
14
|
|
|
} |
|
15
|
|
|
|
|
16
|
|
|
public function testSupportsSequences() |
|
17
|
|
|
{ |
|
18
|
|
|
self::assertTrue($this->_platform->supportsSequences()); |
|
19
|
|
|
} |
|
20
|
|
|
|
|
21
|
|
|
public function testDoesNotPreferSequences() |
|
22
|
|
|
{ |
|
23
|
|
|
self::assertFalse($this->_platform->prefersSequences()); |
|
24
|
|
|
} |
|
25
|
|
|
|
|
26
|
|
View Code Duplication |
public function testGeneratesSequenceSqlCommands() |
|
|
|
|
|
|
27
|
|
|
{ |
|
28
|
|
|
$sequence = new Sequence('myseq', 20, 1); |
|
29
|
|
|
self::assertEquals( |
|
30
|
|
|
'CREATE SEQUENCE myseq START WITH 1 INCREMENT BY 20 MINVALUE 1', |
|
31
|
|
|
$this->_platform->getCreateSequenceSQL($sequence) |
|
32
|
|
|
); |
|
33
|
|
|
self::assertEquals( |
|
34
|
|
|
'ALTER SEQUENCE myseq INCREMENT BY 20', |
|
35
|
|
|
$this->_platform->getAlterSequenceSQL($sequence) |
|
36
|
|
|
); |
|
37
|
|
|
self::assertEquals( |
|
38
|
|
|
'DROP SEQUENCE myseq', |
|
39
|
|
|
$this->_platform->getDropSequenceSQL('myseq') |
|
40
|
|
|
); |
|
41
|
|
|
self::assertEquals( |
|
42
|
|
|
"SELECT NEXT VALUE FOR myseq", |
|
43
|
|
|
$this->_platform->getSequenceNextValSQL('myseq') |
|
44
|
|
|
); |
|
45
|
|
|
} |
|
46
|
|
|
|
|
47
|
|
|
|
|
48
|
|
|
public function testModifyLimitQuery() |
|
49
|
|
|
{ |
|
50
|
|
|
$sql = $this->_platform->modifyLimitQuery('SELECT * FROM user', 10, 0); |
|
51
|
|
|
self::assertEquals('SELECT * FROM user ORDER BY (SELECT 0) OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY', $sql); |
|
52
|
|
|
} |
|
53
|
|
|
|
|
54
|
|
|
public function testModifyLimitQueryWithEmptyOffset() |
|
55
|
|
|
{ |
|
56
|
|
|
$sql = $this->_platform->modifyLimitQuery('SELECT * FROM user', 10); |
|
57
|
|
|
self::assertEquals('SELECT * FROM user ORDER BY (SELECT 0) OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY', $sql); |
|
58
|
|
|
} |
|
59
|
|
|
|
|
60
|
|
|
public function testModifyLimitQueryWithOffset() |
|
61
|
|
|
{ |
|
62
|
|
|
$sql = $this->_platform->modifyLimitQuery('SELECT * FROM user ORDER BY username DESC', 10, 5); |
|
63
|
|
|
self::assertEquals('SELECT * FROM user ORDER BY username DESC OFFSET 5 ROWS FETCH NEXT 10 ROWS ONLY', $sql); |
|
64
|
|
|
} |
|
65
|
|
|
|
|
66
|
|
|
public function testModifyLimitQueryWithAscOrderBy() |
|
67
|
|
|
{ |
|
68
|
|
|
$sql = $this->_platform->modifyLimitQuery('SELECT * FROM user ORDER BY username ASC', 10); |
|
69
|
|
|
self::assertEquals('SELECT * FROM user ORDER BY username ASC OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY', $sql); |
|
70
|
|
|
} |
|
71
|
|
|
|
|
72
|
|
|
public function testModifyLimitQueryWithLowercaseOrderBy() |
|
73
|
|
|
{ |
|
74
|
|
|
$sql = $this->_platform->modifyLimitQuery('SELECT * FROM user order by username', 10); |
|
75
|
|
|
self::assertEquals('SELECT * FROM user order by username OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY', $sql); |
|
76
|
|
|
} |
|
77
|
|
|
|
|
78
|
|
|
public function testModifyLimitQueryWithDescOrderBy() |
|
79
|
|
|
{ |
|
80
|
|
|
$sql = $this->_platform->modifyLimitQuery('SELECT * FROM user ORDER BY username DESC', 10); |
|
81
|
|
|
self::assertEquals('SELECT * FROM user ORDER BY username DESC OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY', $sql); |
|
82
|
|
|
} |
|
83
|
|
|
|
|
84
|
|
|
public function testModifyLimitQueryWithMultipleOrderBy() |
|
85
|
|
|
{ |
|
86
|
|
|
$sql = $this->_platform->modifyLimitQuery('SELECT * FROM user ORDER BY username DESC, usereamil ASC', 10); |
|
87
|
|
|
self::assertEquals('SELECT * FROM user ORDER BY username DESC, usereamil ASC OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY', $sql); |
|
88
|
|
|
} |
|
89
|
|
|
|
|
90
|
|
|
public function testModifyLimitQueryWithSubSelect() |
|
91
|
|
|
{ |
|
92
|
|
|
$sql = $this->_platform->modifyLimitQuery('SELECT * FROM (SELECT u.id as uid, u.name as uname) dctrn_result', 10); |
|
93
|
|
|
self::assertEquals('SELECT * FROM (SELECT u.id as uid, u.name as uname) dctrn_result ORDER BY (SELECT 0) OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY', $sql); |
|
94
|
|
|
} |
|
95
|
|
|
|
|
96
|
|
|
public function testModifyLimitQueryWithSubSelectAndOrder() |
|
97
|
|
|
{ |
|
98
|
|
|
$sql = $this->_platform->modifyLimitQuery('SELECT * FROM (SELECT u.id as uid, u.name as uname) dctrn_result ORDER BY uname DESC', 10); |
|
99
|
|
|
self::assertEquals('SELECT * FROM (SELECT u.id as uid, u.name as uname) dctrn_result ORDER BY uname DESC OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY', $sql); |
|
100
|
|
|
|
|
101
|
|
|
$sql = $this->_platform->modifyLimitQuery('SELECT * FROM (SELECT u.id, u.name) dctrn_result ORDER BY name DESC', 10); |
|
102
|
|
|
self::assertEquals('SELECT * FROM (SELECT u.id, u.name) dctrn_result ORDER BY name DESC OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY', $sql); |
|
103
|
|
|
} |
|
104
|
|
|
|
|
105
|
|
|
public function testModifyLimitQueryWithSubSelectAndMultipleOrder() |
|
106
|
|
|
{ |
|
107
|
|
|
$sql = $this->_platform->modifyLimitQuery('SELECT * FROM (SELECT u.id as uid, u.name as uname) dctrn_result ORDER BY uname DESC, uid ASC', 10, 5); |
|
108
|
|
|
self::assertEquals('SELECT * FROM (SELECT u.id as uid, u.name as uname) dctrn_result ORDER BY uname DESC, uid ASC OFFSET 5 ROWS FETCH NEXT 10 ROWS ONLY', $sql); |
|
109
|
|
|
|
|
110
|
|
|
$sql = $this->_platform->modifyLimitQuery('SELECT * FROM (SELECT u.id uid, u.name uname) dctrn_result ORDER BY uname DESC, uid ASC', 10, 5); |
|
111
|
|
|
self::assertEquals('SELECT * FROM (SELECT u.id uid, u.name uname) dctrn_result ORDER BY uname DESC, uid ASC OFFSET 5 ROWS FETCH NEXT 10 ROWS ONLY', $sql); |
|
112
|
|
|
|
|
113
|
|
|
$sql = $this->_platform->modifyLimitQuery('SELECT * FROM (SELECT u.id, u.name) dctrn_result ORDER BY name DESC, id ASC', 10, 5); |
|
114
|
|
|
self::assertEquals('SELECT * FROM (SELECT u.id, u.name) dctrn_result ORDER BY name DESC, id ASC OFFSET 5 ROWS FETCH NEXT 10 ROWS ONLY', $sql); |
|
115
|
|
|
} |
|
116
|
|
|
|
|
117
|
|
|
public function testModifyLimitQueryWithFromColumnNames() |
|
118
|
|
|
{ |
|
119
|
|
|
$sql = $this->_platform->modifyLimitQuery('SELECT a.fromFoo, fromBar FROM foo', 10); |
|
120
|
|
|
self::assertEquals('SELECT a.fromFoo, fromBar FROM foo ORDER BY (SELECT 0) OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY', $sql); |
|
121
|
|
|
} |
|
122
|
|
|
|
|
123
|
|
|
/** |
|
124
|
|
|
* @group DBAL-927 |
|
125
|
|
|
*/ |
|
126
|
|
View Code Duplication |
public function testModifyLimitQueryWithExtraLongQuery() |
|
|
|
|
|
|
127
|
|
|
{ |
|
128
|
|
|
$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 '; |
|
129
|
|
|
$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) '; |
|
130
|
|
|
$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) '; |
|
131
|
|
|
$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)'; |
|
132
|
|
|
|
|
133
|
|
|
$sql = $this->_platform->modifyLimitQuery($query, 10); |
|
134
|
|
|
|
|
135
|
|
|
$expected = '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 '; |
|
136
|
|
|
$expected.= '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) '; |
|
137
|
|
|
$expected.= '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) '; |
|
138
|
|
|
$expected.= '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) '; |
|
139
|
|
|
$expected.= 'ORDER BY (SELECT 0) OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY'; |
|
140
|
|
|
|
|
141
|
|
|
|
|
142
|
|
|
self::assertEquals($expected, $sql); |
|
143
|
|
|
} |
|
144
|
|
|
|
|
145
|
|
|
/** |
|
146
|
|
|
* @group DDC-2470 |
|
147
|
|
|
*/ |
|
148
|
|
|
public function testModifyLimitQueryWithOrderByClause() |
|
149
|
|
|
{ |
|
150
|
|
|
$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'; |
|
151
|
|
|
$expected = '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 OFFSET 5 ROWS FETCH NEXT 10 ROWS ONLY'; |
|
152
|
|
|
$actual = $this->_platform->modifyLimitQuery($sql, 10, 5); |
|
153
|
|
|
|
|
154
|
|
|
self::assertEquals($expected, $actual); |
|
155
|
|
|
} |
|
156
|
|
|
|
|
157
|
|
|
/** |
|
158
|
|
|
* @group DBAL-713 |
|
159
|
|
|
*/ |
|
160
|
|
View Code Duplication |
public function testModifyLimitQueryWithSubSelectInSelectList() |
|
|
|
|
|
|
161
|
|
|
{ |
|
162
|
|
|
$sql = $this->_platform->modifyLimitQuery( |
|
163
|
|
|
"SELECT " . |
|
164
|
|
|
"u.id, " . |
|
165
|
|
|
"(u.foo/2) foodiv, " . |
|
166
|
|
|
"CONCAT(u.bar, u.baz) barbaz, " . |
|
167
|
|
|
"(SELECT (SELECT COUNT(*) FROM login l WHERE l.profile_id = p.id) FROM profile p WHERE p.user_id = u.id) login_count " . |
|
168
|
|
|
"FROM user u " . |
|
169
|
|
|
"WHERE u.status = 'disabled'", |
|
170
|
|
|
10 |
|
171
|
|
|
); |
|
172
|
|
|
|
|
173
|
|
|
self::assertEquals( |
|
174
|
|
|
|
|
175
|
|
|
"SELECT " . |
|
176
|
|
|
"u.id, " . |
|
177
|
|
|
"(u.foo/2) foodiv, " . |
|
178
|
|
|
"CONCAT(u.bar, u.baz) barbaz, " . |
|
179
|
|
|
"(SELECT (SELECT COUNT(*) FROM login l WHERE l.profile_id = p.id) FROM profile p WHERE p.user_id = u.id) login_count " . |
|
180
|
|
|
"FROM user u " . |
|
181
|
|
|
"WHERE u.status = 'disabled' " . |
|
182
|
|
|
"ORDER BY (SELECT 0) OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY", |
|
183
|
|
|
$sql |
|
184
|
|
|
); |
|
185
|
|
|
} |
|
186
|
|
|
|
|
187
|
|
|
/** |
|
188
|
|
|
* @group DBAL-713 |
|
189
|
|
|
*/ |
|
190
|
|
|
public function testModifyLimitQueryWithSubSelectInSelectListAndOrderByClause() |
|
191
|
|
|
{ |
|
192
|
|
|
$sql = $this->_platform->modifyLimitQuery( |
|
193
|
|
|
"SELECT " . |
|
194
|
|
|
"u.id, " . |
|
195
|
|
|
"(u.foo/2) foodiv, " . |
|
196
|
|
|
"CONCAT(u.bar, u.baz) barbaz, " . |
|
197
|
|
|
"(SELECT (SELECT COUNT(*) FROM login l WHERE l.profile_id = p.id) FROM profile p WHERE p.user_id = u.id) login_count " . |
|
198
|
|
|
"FROM user u " . |
|
199
|
|
|
"WHERE u.status = 'disabled' " . |
|
200
|
|
|
"ORDER BY u.username DESC", |
|
201
|
|
|
10, |
|
202
|
|
|
5 |
|
203
|
|
|
); |
|
204
|
|
|
|
|
205
|
|
|
self::assertEquals( |
|
206
|
|
|
"SELECT " . |
|
207
|
|
|
"u.id, " . |
|
208
|
|
|
"(u.foo/2) foodiv, " . |
|
209
|
|
|
"CONCAT(u.bar, u.baz) barbaz, " . |
|
210
|
|
|
"(SELECT (SELECT COUNT(*) FROM login l WHERE l.profile_id = p.id) FROM profile p WHERE p.user_id = u.id) login_count " . |
|
211
|
|
|
"FROM user u " . |
|
212
|
|
|
"WHERE u.status = 'disabled' " . |
|
213
|
|
|
"ORDER BY u.username DESC " . |
|
214
|
|
|
"OFFSET 5 ROWS FETCH NEXT 10 ROWS ONLY", |
|
215
|
|
|
$sql |
|
216
|
|
|
); |
|
217
|
|
|
} |
|
218
|
|
|
|
|
219
|
|
|
/** |
|
220
|
|
|
* @group DBAL-834 |
|
221
|
|
|
*/ |
|
222
|
|
View Code Duplication |
public function testModifyLimitQueryWithAggregateFunctionInOrderByClause() |
|
|
|
|
|
|
223
|
|
|
{ |
|
224
|
|
|
$sql = $this->_platform->modifyLimitQuery( |
|
225
|
|
|
"SELECT " . |
|
226
|
|
|
"MAX(heading_id) aliased, " . |
|
227
|
|
|
"code " . |
|
228
|
|
|
"FROM operator_model_operator " . |
|
229
|
|
|
"GROUP BY code " . |
|
230
|
|
|
"ORDER BY MAX(heading_id) DESC", |
|
231
|
|
|
1, |
|
232
|
|
|
0 |
|
233
|
|
|
); |
|
234
|
|
|
|
|
235
|
|
|
self::assertEquals( |
|
236
|
|
|
"SELECT " . |
|
237
|
|
|
"MAX(heading_id) aliased, " . |
|
238
|
|
|
"code " . |
|
239
|
|
|
"FROM operator_model_operator " . |
|
240
|
|
|
"GROUP BY code " . |
|
241
|
|
|
"ORDER BY MAX(heading_id) DESC " . |
|
242
|
|
|
"OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY", |
|
243
|
|
|
$sql |
|
244
|
|
|
); |
|
245
|
|
|
} |
|
246
|
|
|
|
|
247
|
|
|
public function testModifyLimitQueryWithFromSubquery() |
|
248
|
|
|
{ |
|
249
|
|
|
$sql = $this->_platform->modifyLimitQuery("SELECT DISTINCT id_0 FROM (SELECT k0_.id AS id_0 FROM key_measure k0_ WHERE (k0_.id_zone in(2))) dctrn_result", 10); |
|
250
|
|
|
|
|
251
|
|
|
$expected = "SELECT DISTINCT id_0 FROM (SELECT k0_.id AS id_0 FROM key_measure k0_ WHERE (k0_.id_zone in(2))) dctrn_result ORDER BY 1 OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY"; |
|
252
|
|
|
|
|
253
|
|
|
self::assertEquals($sql, $expected); |
|
254
|
|
|
} |
|
255
|
|
|
|
|
256
|
|
|
public function testModifyLimitQueryWithFromSubqueryAndOrder() |
|
257
|
|
|
{ |
|
258
|
|
|
$sql = $this->_platform->modifyLimitQuery("SELECT DISTINCT id_0, value_1 FROM (SELECT k0_.id AS id_0, k0_.value AS value_1 FROM key_measure k0_ WHERE (k0_.id_zone in(2))) dctrn_result ORDER BY value_1 DESC", 10); |
|
259
|
|
|
|
|
260
|
|
|
$expected = "SELECT DISTINCT id_0, value_1 FROM (SELECT k0_.id AS id_0, k0_.value AS value_1 FROM key_measure k0_ WHERE (k0_.id_zone in(2))) dctrn_result ORDER BY value_1 DESC OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY"; |
|
261
|
|
|
|
|
262
|
|
|
self::assertEquals($sql, $expected); |
|
263
|
|
|
} |
|
264
|
|
|
|
|
265
|
|
|
public function testModifyLimitQueryWithComplexOrderByExpression() |
|
266
|
|
|
{ |
|
267
|
|
|
$sql = $this->_platform->modifyLimitQuery("SELECT * FROM table ORDER BY (table.x * table.y) DESC", 10); |
|
268
|
|
|
|
|
269
|
|
|
$expected = "SELECT * FROM table ORDER BY (table.x * table.y) DESC OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY"; |
|
270
|
|
|
|
|
271
|
|
|
self::assertEquals($sql, $expected); |
|
272
|
|
|
} |
|
273
|
|
|
|
|
274
|
|
|
|
|
275
|
|
|
/** |
|
276
|
|
|
* @throws \Doctrine\DBAL\DBALException |
|
277
|
|
|
*/ |
|
278
|
|
View Code Duplication |
public function testModifyLimitSubqueryWithJoinAndSubqueryOrderedByColumnFromBaseTable() |
|
|
|
|
|
|
279
|
|
|
{ |
|
280
|
|
|
$querySql = "SELECT DISTINCT id_0, name_1 " |
|
281
|
|
|
. "FROM (" |
|
282
|
|
|
. "SELECT t1.id AS id_0, t2.name AS name_1 " |
|
283
|
|
|
. "FROM table_parent t1 " |
|
284
|
|
|
. "LEFT JOIN join_table t2 ON t1.id = t2.table_id" |
|
285
|
|
|
. ") dctrn_result " |
|
286
|
|
|
. "ORDER BY id_0 ASC"; |
|
287
|
|
|
$alteredSql = "SELECT DISTINCT id_0, name_1 " |
|
288
|
|
|
. "FROM (" |
|
289
|
|
|
. "SELECT t1.id AS id_0, t2.name AS name_1 " |
|
290
|
|
|
. "FROM table_parent t1 " |
|
291
|
|
|
. "LEFT JOIN join_table t2 ON t1.id = t2.table_id" |
|
292
|
|
|
. ") dctrn_result " |
|
293
|
|
|
. "ORDER BY id_0 ASC OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY"; |
|
294
|
|
|
$sql = $this->_platform->modifyLimitQuery($querySql, 5); |
|
295
|
|
|
self::assertEquals($alteredSql, $sql); |
|
296
|
|
|
} |
|
297
|
|
|
|
|
298
|
|
|
/** |
|
299
|
|
|
* @throws \Doctrine\DBAL\DBALException |
|
300
|
|
|
*/ |
|
301
|
|
View Code Duplication |
public function testModifyLimitSubqueryWithJoinAndSubqueryOrderedByColumnFromJoinTable() |
|
|
|
|
|
|
302
|
|
|
{ |
|
303
|
|
|
$querySql = "SELECT DISTINCT id_0, name_1 " |
|
304
|
|
|
. "FROM (" |
|
305
|
|
|
. "SELECT t1.id AS id_0, t2.name AS name_1 " |
|
306
|
|
|
. "FROM table_parent t1 " |
|
307
|
|
|
. "LEFT JOIN join_table t2 ON t1.id = t2.table_id" |
|
308
|
|
|
. ") dctrn_result " |
|
309
|
|
|
. "ORDER BY name_1 ASC"; |
|
310
|
|
|
$alteredSql = "SELECT DISTINCT id_0, name_1 " |
|
311
|
|
|
. "FROM (" |
|
312
|
|
|
. "SELECT t1.id AS id_0, t2.name AS name_1 " |
|
313
|
|
|
. "FROM table_parent t1 " |
|
314
|
|
|
. "LEFT JOIN join_table t2 ON t1.id = t2.table_id" |
|
315
|
|
|
. ") dctrn_result " |
|
316
|
|
|
. "ORDER BY name_1 ASC OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY"; |
|
317
|
|
|
$sql = $this->_platform->modifyLimitQuery($querySql, 5); |
|
318
|
|
|
self::assertEquals($alteredSql, $sql); |
|
319
|
|
|
} |
|
320
|
|
|
|
|
321
|
|
|
/** |
|
322
|
|
|
* @throws \Doctrine\DBAL\DBALException |
|
323
|
|
|
*/ |
|
324
|
|
View Code Duplication |
public function testModifyLimitSubqueryWithJoinAndSubqueryOrderedByColumnsFromBothTables() |
|
|
|
|
|
|
325
|
|
|
{ |
|
326
|
|
|
$querySql = "SELECT DISTINCT id_0, name_1, foo_2 " |
|
327
|
|
|
. "FROM (" |
|
328
|
|
|
. "SELECT t1.id AS id_0, t2.name AS name_1, t2.foo AS foo_2 " |
|
329
|
|
|
. "FROM table_parent t1 " |
|
330
|
|
|
. "LEFT JOIN join_table t2 ON t1.id = t2.table_id" |
|
331
|
|
|
. ") dctrn_result " |
|
332
|
|
|
. "ORDER BY name_1 ASC, foo_2 DESC"; |
|
333
|
|
|
$alteredSql = "SELECT DISTINCT id_0, name_1, foo_2 " |
|
334
|
|
|
. "FROM (" |
|
335
|
|
|
. "SELECT t1.id AS id_0, t2.name AS name_1, t2.foo AS foo_2 " |
|
336
|
|
|
. "FROM table_parent t1 " |
|
337
|
|
|
. "LEFT JOIN join_table t2 ON t1.id = t2.table_id" |
|
338
|
|
|
. ") dctrn_result " |
|
339
|
|
|
. "ORDER BY name_1 ASC, foo_2 DESC OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY"; |
|
340
|
|
|
$sql = $this->_platform->modifyLimitQuery($querySql, 5); |
|
341
|
|
|
self::assertEquals($alteredSql, $sql); |
|
342
|
|
|
} |
|
343
|
|
|
|
|
344
|
|
|
public function testModifyLimitSubquerySimple() |
|
345
|
|
|
{ |
|
346
|
|
|
$querySql = "SELECT DISTINCT id_0 FROM " |
|
347
|
|
|
. "(SELECT k0_.id AS id_0, k0_.field AS field_1 " |
|
348
|
|
|
. "FROM key_table k0_ WHERE (k0_.where_field IN (1))) dctrn_result"; |
|
349
|
|
|
$alteredSql = "SELECT DISTINCT id_0 FROM (SELECT k0_.id AS id_0, k0_.field AS field_1 " |
|
350
|
|
|
. "FROM key_table k0_ WHERE (k0_.where_field IN (1))) dctrn_result ORDER BY 1 OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY"; |
|
351
|
|
|
$sql = $this->_platform->modifyLimitQuery($querySql, 20); |
|
352
|
|
|
self::assertEquals($alteredSql, $sql); |
|
353
|
|
|
} |
|
354
|
|
|
|
|
355
|
|
|
public function testModifyLimitQueryWithTopNSubQueryWithOrderBy() |
|
356
|
|
|
{ |
|
357
|
|
|
$querySql = 'SELECT * FROM test t WHERE t.id = (SELECT TOP 1 t2.id FROM test t2 ORDER BY t2.data DESC)'; |
|
358
|
|
|
$expectedSql = 'SELECT * FROM test t WHERE t.id = (SELECT TOP 1 t2.id FROM test t2 ORDER BY t2.data DESC) ORDER BY (SELECT 0) OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY'; |
|
359
|
|
|
$sql = $this->_platform->modifyLimitQuery($querySql, 10); |
|
360
|
|
|
self::assertEquals($expectedSql, $sql); |
|
361
|
|
|
|
|
362
|
|
|
$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'; |
|
363
|
|
|
$expectedSql = '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 OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY'; |
|
364
|
|
|
$sql = $this->_platform->modifyLimitQuery($querySql, 10); |
|
365
|
|
|
self::assertEquals($expectedSql, $sql); |
|
366
|
|
|
} |
|
367
|
|
|
|
|
368
|
|
|
public function testModifyLimitQueryWithNewlineBeforeOrderBy() |
|
369
|
|
|
{ |
|
370
|
|
|
$querySql = "SELECT * FROM test\nORDER BY col DESC"; |
|
371
|
|
|
$expectedSql = "SELECT * FROM test\nORDER BY col DESC OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY"; |
|
372
|
|
|
$sql = $this->_platform->modifyLimitQuery($querySql, 10); |
|
373
|
|
|
self::assertEquals($expectedSql, $sql); |
|
374
|
|
|
} |
|
375
|
|
|
|
|
376
|
|
View Code Duplication |
public function testGetDefaultValueDeclarationSQLForDateType() |
|
|
|
|
|
|
377
|
|
|
{ |
|
378
|
|
|
$currentDateSql = $this->_platform->getCurrentDateSQL(); |
|
379
|
|
|
$field = array( |
|
380
|
|
|
'type' => Type::getType('date'), |
|
381
|
|
|
'default' => $currentDateSql, |
|
382
|
|
|
); |
|
383
|
|
|
|
|
384
|
|
|
$this->assertEquals( |
|
385
|
|
|
" DEFAULT '".$currentDateSql."'", |
|
386
|
|
|
$this->_platform->getDefaultValueDeclarationSQL($field) |
|
387
|
|
|
); |
|
388
|
|
|
} |
|
389
|
|
|
} |
|
390
|
|
|
|
doctrine /
dbal
Loading history...
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.