1
|
|
|
<?php |
2
|
|
|
|
3
|
|
|
declare(strict_types=1); |
4
|
|
|
|
5
|
|
|
namespace Doctrine\DBAL\Tests\Platforms; |
6
|
|
|
|
7
|
|
|
use Doctrine\DBAL\DBALException; |
8
|
|
|
use Doctrine\DBAL\Platforms\AbstractPlatform; |
9
|
|
|
use Doctrine\DBAL\Platforms\SqlitePlatform; |
10
|
|
|
use Doctrine\DBAL\Schema\Column; |
11
|
|
|
use Doctrine\DBAL\Schema\Table; |
12
|
|
|
use Doctrine\DBAL\Schema\TableDiff; |
13
|
|
|
use Doctrine\DBAL\TransactionIsolationLevel; |
14
|
|
|
use Doctrine\DBAL\Types\Type; |
15
|
|
|
use function assert; |
16
|
|
|
|
17
|
|
|
class SqlitePlatformTest extends AbstractPlatformTestCase |
18
|
|
|
{ |
19
|
|
|
public function createPlatform() : AbstractPlatform |
20
|
|
|
{ |
21
|
|
|
return new SqlitePlatform(); |
22
|
|
|
} |
23
|
|
|
|
24
|
|
|
public function getGenerateTableSql() : string |
25
|
|
|
{ |
26
|
|
|
return 'CREATE TABLE test (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, test VARCHAR(255) DEFAULT NULL)'; |
27
|
|
|
} |
28
|
|
|
|
29
|
|
|
/** |
30
|
|
|
* {@inheritDoc} |
31
|
|
|
*/ |
32
|
|
|
public function getGenerateTableWithMultiColumnUniqueIndexSql() : array |
33
|
|
|
{ |
34
|
|
|
return [ |
35
|
|
|
'CREATE TABLE test (foo VARCHAR(255) DEFAULT NULL, bar VARCHAR(255) DEFAULT NULL)', |
36
|
|
|
'CREATE UNIQUE INDEX UNIQ_D87F7E0C8C73652176FF8CAA ON test (foo, bar)', |
37
|
|
|
]; |
38
|
|
|
} |
39
|
|
|
|
40
|
|
|
public function testGeneratesSqlSnippets() : void |
41
|
|
|
{ |
42
|
|
|
self::assertEquals('REGEXP', $this->platform->getRegexpExpression(), 'Regular expression operator is not correct'); |
43
|
|
|
self::assertEquals('SUBSTR(column, 5)', $this->platform->getSubstringExpression('column', '5'), 'Substring expression without length is not correct'); |
44
|
|
|
self::assertEquals('SUBSTR(column, 0, 5)', $this->platform->getSubstringExpression('column', '0', '5'), 'Substring expression with length is not correct'); |
45
|
|
|
} |
46
|
|
|
|
47
|
|
|
public function testGeneratesTransactionCommands() : void |
48
|
|
|
{ |
49
|
|
|
self::assertEquals( |
50
|
|
|
'PRAGMA read_uncommitted = 0', |
51
|
|
|
$this->platform->getSetTransactionIsolationSQL(TransactionIsolationLevel::READ_UNCOMMITTED) |
52
|
|
|
); |
53
|
|
|
self::assertEquals( |
54
|
|
|
'PRAGMA read_uncommitted = 1', |
55
|
|
|
$this->platform->getSetTransactionIsolationSQL(TransactionIsolationLevel::READ_COMMITTED) |
56
|
|
|
); |
57
|
|
|
self::assertEquals( |
58
|
|
|
'PRAGMA read_uncommitted = 1', |
59
|
|
|
$this->platform->getSetTransactionIsolationSQL(TransactionIsolationLevel::REPEATABLE_READ) |
60
|
|
|
); |
61
|
|
|
self::assertEquals( |
62
|
|
|
'PRAGMA read_uncommitted = 1', |
63
|
|
|
$this->platform->getSetTransactionIsolationSQL(TransactionIsolationLevel::SERIALIZABLE) |
64
|
|
|
); |
65
|
|
|
} |
66
|
|
|
|
67
|
|
|
public function testPrefersIdentityColumns() : void |
68
|
|
|
{ |
69
|
|
|
self::assertTrue($this->platform->prefersIdentityColumns()); |
70
|
|
|
} |
71
|
|
|
|
72
|
|
|
public function testIgnoresUnsignedIntegerDeclarationForAutoIncrementalIntegers() : void |
73
|
|
|
{ |
74
|
|
|
self::assertSame( |
75
|
|
|
'INTEGER PRIMARY KEY AUTOINCREMENT', |
76
|
|
|
$this->platform->getIntegerTypeDeclarationSQL(['autoincrement' => true, 'unsigned' => true]) |
77
|
|
|
); |
78
|
|
|
} |
79
|
|
|
|
80
|
|
|
/** |
81
|
|
|
* @group DBAL-752 |
82
|
|
|
* @group DBAL-924 |
83
|
|
|
*/ |
84
|
|
|
public function testGeneratesTypeDeclarationForTinyIntegers() : void |
85
|
|
|
{ |
86
|
|
|
assert($this->platform instanceof SqlitePlatform); |
87
|
|
|
|
88
|
|
|
self::assertEquals( |
89
|
|
|
'TINYINT', |
90
|
|
|
$this->platform->getTinyIntTypeDeclarationSQL([]) |
91
|
|
|
); |
92
|
|
|
self::assertEquals( |
93
|
|
|
'INTEGER PRIMARY KEY AUTOINCREMENT', |
94
|
|
|
$this->platform->getTinyIntTypeDeclarationSQL(['autoincrement' => true]) |
95
|
|
|
); |
96
|
|
|
self::assertEquals( |
97
|
|
|
'INTEGER PRIMARY KEY AUTOINCREMENT', |
98
|
|
|
$this->platform->getTinyIntTypeDeclarationSQL( |
99
|
|
|
['autoincrement' => true, 'primary' => true] |
100
|
|
|
) |
101
|
|
|
); |
102
|
|
|
self::assertEquals( |
103
|
|
|
'TINYINT', |
104
|
|
|
$this->platform->getTinyIntTypeDeclarationSQL(['unsigned' => false]) |
105
|
|
|
); |
106
|
|
|
self::assertEquals( |
107
|
|
|
'TINYINT UNSIGNED', |
108
|
|
|
$this->platform->getTinyIntTypeDeclarationSQL(['unsigned' => true]) |
109
|
|
|
); |
110
|
|
|
} |
111
|
|
|
|
112
|
|
|
/** |
113
|
|
|
* @group DBAL-752 |
114
|
|
|
* @group DBAL-924 |
115
|
|
|
*/ |
116
|
|
|
public function testGeneratesTypeDeclarationForSmallIntegers() : void |
117
|
|
|
{ |
118
|
|
|
assert($this->platform instanceof SqlitePlatform); |
119
|
|
|
|
120
|
|
|
self::assertEquals( |
121
|
|
|
'SMALLINT', |
122
|
|
|
$this->platform->getSmallIntTypeDeclarationSQL([]) |
123
|
|
|
); |
124
|
|
|
self::assertEquals( |
125
|
|
|
'INTEGER PRIMARY KEY AUTOINCREMENT', |
126
|
|
|
$this->platform->getSmallIntTypeDeclarationSQL(['autoincrement' => true]) |
127
|
|
|
); |
128
|
|
|
self::assertEquals( |
129
|
|
|
'INTEGER PRIMARY KEY AUTOINCREMENT', |
130
|
|
|
$this->platform->getTinyIntTypeDeclarationSQL(['autoincrement' => true, 'unsigned' => true]) |
131
|
|
|
); |
132
|
|
|
self::assertEquals( |
133
|
|
|
'INTEGER PRIMARY KEY AUTOINCREMENT', |
134
|
|
|
$this->platform->getSmallIntTypeDeclarationSQL( |
135
|
|
|
['autoincrement' => true, 'primary' => true] |
136
|
|
|
) |
137
|
|
|
); |
138
|
|
|
self::assertEquals( |
139
|
|
|
'SMALLINT', |
140
|
|
|
$this->platform->getSmallIntTypeDeclarationSQL(['unsigned' => false]) |
141
|
|
|
); |
142
|
|
|
self::assertEquals( |
143
|
|
|
'SMALLINT UNSIGNED', |
144
|
|
|
$this->platform->getSmallIntTypeDeclarationSQL(['unsigned' => true]) |
145
|
|
|
); |
146
|
|
|
} |
147
|
|
|
|
148
|
|
|
/** |
149
|
|
|
* @group DBAL-752 |
150
|
|
|
* @group DBAL-924 |
151
|
|
|
*/ |
152
|
|
|
public function testGeneratesTypeDeclarationForMediumIntegers() : void |
153
|
|
|
{ |
154
|
|
|
assert($this->platform instanceof SqlitePlatform); |
155
|
|
|
|
156
|
|
|
self::assertEquals( |
157
|
|
|
'MEDIUMINT', |
158
|
|
|
$this->platform->getMediumIntTypeDeclarationSQL([]) |
159
|
|
|
); |
160
|
|
|
self::assertEquals( |
161
|
|
|
'INTEGER PRIMARY KEY AUTOINCREMENT', |
162
|
|
|
$this->platform->getMediumIntTypeDeclarationSQL(['autoincrement' => true]) |
163
|
|
|
); |
164
|
|
|
self::assertEquals( |
165
|
|
|
'INTEGER PRIMARY KEY AUTOINCREMENT', |
166
|
|
|
$this->platform->getMediumIntTypeDeclarationSQL(['autoincrement' => true, 'unsigned' => true]) |
167
|
|
|
); |
168
|
|
|
self::assertEquals( |
169
|
|
|
'INTEGER PRIMARY KEY AUTOINCREMENT', |
170
|
|
|
$this->platform->getMediumIntTypeDeclarationSQL( |
171
|
|
|
['autoincrement' => true, 'primary' => true] |
172
|
|
|
) |
173
|
|
|
); |
174
|
|
|
self::assertEquals( |
175
|
|
|
'MEDIUMINT', |
176
|
|
|
$this->platform->getMediumIntTypeDeclarationSQL(['unsigned' => false]) |
177
|
|
|
); |
178
|
|
|
self::assertEquals( |
179
|
|
|
'MEDIUMINT UNSIGNED', |
180
|
|
|
$this->platform->getMediumIntTypeDeclarationSQL(['unsigned' => true]) |
181
|
|
|
); |
182
|
|
|
} |
183
|
|
|
|
184
|
|
|
public function testGeneratesTypeDeclarationForIntegers() : void |
185
|
|
|
{ |
186
|
|
|
self::assertEquals( |
187
|
|
|
'INTEGER', |
188
|
|
|
$this->platform->getIntegerTypeDeclarationSQL([]) |
189
|
|
|
); |
190
|
|
|
self::assertEquals( |
191
|
|
|
'INTEGER PRIMARY KEY AUTOINCREMENT', |
192
|
|
|
$this->platform->getIntegerTypeDeclarationSQL(['autoincrement' => true]) |
193
|
|
|
); |
194
|
|
|
self::assertEquals( |
195
|
|
|
'INTEGER PRIMARY KEY AUTOINCREMENT', |
196
|
|
|
$this->platform->getIntegerTypeDeclarationSQL(['autoincrement' => true, 'unsigned' => true]) |
197
|
|
|
); |
198
|
|
|
self::assertEquals( |
199
|
|
|
'INTEGER PRIMARY KEY AUTOINCREMENT', |
200
|
|
|
$this->platform->getIntegerTypeDeclarationSQL( |
201
|
|
|
['autoincrement' => true, 'primary' => true] |
202
|
|
|
) |
203
|
|
|
); |
204
|
|
|
self::assertEquals( |
205
|
|
|
'INTEGER', |
206
|
|
|
$this->platform->getIntegerTypeDeclarationSQL(['unsigned' => false]) |
207
|
|
|
); |
208
|
|
|
self::assertEquals( |
209
|
|
|
'INTEGER UNSIGNED', |
210
|
|
|
$this->platform->getIntegerTypeDeclarationSQL(['unsigned' => true]) |
211
|
|
|
); |
212
|
|
|
} |
213
|
|
|
|
214
|
|
|
/** |
215
|
|
|
* @group DBAL-752 |
216
|
|
|
* @group DBAL-924 |
217
|
|
|
*/ |
218
|
|
|
public function testGeneratesTypeDeclarationForBigIntegers() : void |
219
|
|
|
{ |
220
|
|
|
self::assertEquals( |
221
|
|
|
'BIGINT', |
222
|
|
|
$this->platform->getBigIntTypeDeclarationSQL([]) |
223
|
|
|
); |
224
|
|
|
self::assertEquals( |
225
|
|
|
'INTEGER PRIMARY KEY AUTOINCREMENT', |
226
|
|
|
$this->platform->getBigIntTypeDeclarationSQL(['autoincrement' => true]) |
227
|
|
|
); |
228
|
|
|
self::assertEquals( |
229
|
|
|
'INTEGER PRIMARY KEY AUTOINCREMENT', |
230
|
|
|
$this->platform->getBigIntTypeDeclarationSQL(['autoincrement' => true, 'unsigned' => true]) |
231
|
|
|
); |
232
|
|
|
self::assertEquals( |
233
|
|
|
'INTEGER PRIMARY KEY AUTOINCREMENT', |
234
|
|
|
$this->platform->getBigIntTypeDeclarationSQL( |
235
|
|
|
['autoincrement' => true, 'primary' => true] |
236
|
|
|
) |
237
|
|
|
); |
238
|
|
|
self::assertEquals( |
239
|
|
|
'BIGINT', |
240
|
|
|
$this->platform->getBigIntTypeDeclarationSQL(['unsigned' => false]) |
241
|
|
|
); |
242
|
|
|
self::assertEquals( |
243
|
|
|
'BIGINT UNSIGNED', |
244
|
|
|
$this->platform->getBigIntTypeDeclarationSQL(['unsigned' => true]) |
245
|
|
|
); |
246
|
|
|
} |
247
|
|
|
|
248
|
|
|
public function getGenerateIndexSql() : string |
249
|
|
|
{ |
250
|
|
|
return 'CREATE INDEX my_idx ON mytable (user_name, last_login)'; |
251
|
|
|
} |
252
|
|
|
|
253
|
|
|
public function getGenerateUniqueIndexSql() : string |
254
|
|
|
{ |
255
|
|
|
return 'CREATE UNIQUE INDEX index_name ON test (test, test2)'; |
256
|
|
|
} |
257
|
|
|
|
258
|
|
|
public function testGeneratesForeignKeyCreationSql() : void |
259
|
|
|
{ |
260
|
|
|
$this->expectException(DBALException::class); |
261
|
|
|
|
262
|
|
|
parent::testGeneratesForeignKeyCreationSql(); |
263
|
|
|
} |
264
|
|
|
|
265
|
|
|
public function testGeneratesConstraintCreationSql() : void |
266
|
|
|
{ |
267
|
|
|
$this->expectException(DBALException::class); |
268
|
|
|
|
269
|
|
|
parent::testGeneratesConstraintCreationSql(); |
270
|
|
|
} |
271
|
|
|
|
272
|
|
|
public function getGenerateForeignKeySql() : string |
273
|
|
|
{ |
274
|
|
|
self::fail('Foreign key constraints are not yet supported for SQLite.'); |
|
|
|
|
275
|
|
|
} |
276
|
|
|
|
277
|
|
|
public function testModifyLimitQuery() : void |
278
|
|
|
{ |
279
|
|
|
$sql = $this->platform->modifyLimitQuery('SELECT * FROM user', 10, 0); |
280
|
|
|
self::assertEquals('SELECT * FROM user LIMIT 10', $sql); |
281
|
|
|
} |
282
|
|
|
|
283
|
|
|
public function testModifyLimitQueryWithEmptyOffset() : void |
284
|
|
|
{ |
285
|
|
|
$sql = $this->platform->modifyLimitQuery('SELECT * FROM user', 10); |
286
|
|
|
self::assertEquals('SELECT * FROM user LIMIT 10', $sql); |
287
|
|
|
} |
288
|
|
|
|
289
|
|
|
public function testModifyLimitQueryWithOffsetAndEmptyLimit() : void |
290
|
|
|
{ |
291
|
|
|
$sql = $this->platform->modifyLimitQuery('SELECT * FROM user', null, 10); |
292
|
|
|
self::assertEquals('SELECT * FROM user LIMIT -1 OFFSET 10', $sql); |
293
|
|
|
} |
294
|
|
|
|
295
|
|
|
/** |
296
|
|
|
* {@inheritDoc} |
297
|
|
|
*/ |
298
|
|
|
public function getGenerateAlterTableSql() : array |
299
|
|
|
{ |
300
|
|
|
return [ |
301
|
|
|
'CREATE TEMPORARY TABLE __temp__mytable AS SELECT id, bar, bloo FROM mytable', |
302
|
|
|
'DROP TABLE mytable', |
303
|
|
|
"CREATE TABLE mytable (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, baz VARCHAR(255) DEFAULT 'def' NOT NULL, bloo BOOLEAN DEFAULT '0' NOT NULL, quota INTEGER DEFAULT NULL)", |
304
|
|
|
'INSERT INTO mytable (id, baz, bloo) SELECT id, bar, bloo FROM __temp__mytable', |
305
|
|
|
'DROP TABLE __temp__mytable', |
306
|
|
|
'ALTER TABLE mytable RENAME TO userlist', |
307
|
|
|
]; |
308
|
|
|
} |
309
|
|
|
|
310
|
|
|
/** |
311
|
|
|
* @group DDC-1845 |
312
|
|
|
*/ |
313
|
|
|
public function testGenerateTableSqlShouldNotAutoQuotePrimaryKey() : void |
314
|
|
|
{ |
315
|
|
|
$table = new Table('test'); |
316
|
|
|
$table->addColumn('"like"', 'integer', ['notnull' => true, 'autoincrement' => true]); |
317
|
|
|
$table->setPrimaryKey(['"like"']); |
318
|
|
|
|
319
|
|
|
$createTableSQL = $this->platform->getCreateTableSQL($table); |
320
|
|
|
self::assertEquals( |
321
|
|
|
'CREATE TABLE test ("like" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL)', |
322
|
|
|
$createTableSQL[0] |
323
|
|
|
); |
324
|
|
|
} |
325
|
|
|
|
326
|
|
|
public function testAlterTableAddColumns() : void |
327
|
|
|
{ |
328
|
|
|
$diff = new TableDiff('user'); |
329
|
|
|
$diff->addedColumns['foo'] = new Column('foo', Type::getType('string')); |
330
|
|
|
$diff->addedColumns['count'] = new Column('count', Type::getType('integer'), ['notnull' => false, 'default' => 1]); |
331
|
|
|
|
332
|
|
|
$expected = [ |
333
|
|
|
'ALTER TABLE user ADD COLUMN foo VARCHAR(255) NOT NULL', |
334
|
|
|
'ALTER TABLE user ADD COLUMN count INTEGER DEFAULT 1', |
335
|
|
|
]; |
336
|
|
|
|
337
|
|
|
self::assertEquals($expected, $this->platform->getAlterTableSQL($diff)); |
338
|
|
|
} |
339
|
|
|
|
340
|
|
|
/** |
341
|
|
|
* @dataProvider complexDiffProvider |
342
|
|
|
*/ |
343
|
|
|
public function testAlterTableAddComplexColumns(TableDiff $diff) : void |
344
|
|
|
{ |
345
|
|
|
$this->expectException(DBALException::class); |
346
|
|
|
|
347
|
|
|
$this->platform->getAlterTableSQL($diff); |
348
|
|
|
} |
349
|
|
|
|
350
|
|
|
/** |
351
|
|
|
* @return mixed[][] |
352
|
|
|
*/ |
353
|
|
|
public static function complexDiffProvider() : iterable |
354
|
|
|
{ |
355
|
|
|
$date = new TableDiff('user'); |
356
|
|
|
$date->addedColumns['time'] = new Column('time', Type::getType('date'), ['default' => 'CURRENT_DATE']); |
357
|
|
|
|
358
|
|
|
$id = new TableDiff('user'); |
359
|
|
|
$id->addedColumns['id'] = new Column('id', Type::getType('integer'), ['autoincrement' => true]); |
360
|
|
|
|
361
|
|
|
return [ |
362
|
|
|
'date column with default value' => [$date], |
363
|
|
|
'id column with auto increment' => [$id], |
364
|
|
|
]; |
365
|
|
|
} |
366
|
|
|
|
367
|
|
|
public function testCreateTableWithDeferredForeignKeys() : void |
368
|
|
|
{ |
369
|
|
|
$table = new Table('user'); |
370
|
|
|
$table->addColumn('id', 'integer'); |
371
|
|
|
$table->addColumn('article', 'integer'); |
372
|
|
|
$table->addColumn('post', 'integer'); |
373
|
|
|
$table->addColumn('parent', 'integer'); |
374
|
|
|
$table->setPrimaryKey(['id']); |
375
|
|
|
$table->addForeignKeyConstraint('article', ['article'], ['id'], ['deferrable' => true]); |
376
|
|
|
$table->addForeignKeyConstraint('post', ['post'], ['id'], ['deferred' => true]); |
377
|
|
|
$table->addForeignKeyConstraint('user', ['parent'], ['id'], ['deferrable' => true, 'deferred' => true]); |
378
|
|
|
|
379
|
|
|
$sql = [ |
380
|
|
|
'CREATE TABLE user (' |
381
|
|
|
. 'id INTEGER NOT NULL, article INTEGER NOT NULL, post INTEGER NOT NULL, parent INTEGER NOT NULL' |
382
|
|
|
. ', PRIMARY KEY(id)' |
383
|
|
|
. ', CONSTRAINT FK_8D93D64923A0E66 FOREIGN KEY (article) REFERENCES article (id) DEFERRABLE INITIALLY IMMEDIATE' |
384
|
|
|
. ', CONSTRAINT FK_8D93D6495A8A6C8D FOREIGN KEY (post) REFERENCES post (id) NOT DEFERRABLE INITIALLY DEFERRED' |
385
|
|
|
. ', CONSTRAINT FK_8D93D6493D8E604F FOREIGN KEY (parent) REFERENCES user (id) DEFERRABLE INITIALLY DEFERRED' |
386
|
|
|
. ')', |
387
|
|
|
'CREATE INDEX IDX_8D93D64923A0E66 ON user (article)', |
388
|
|
|
'CREATE INDEX IDX_8D93D6495A8A6C8D ON user (post)', |
389
|
|
|
'CREATE INDEX IDX_8D93D6493D8E604F ON user (parent)', |
390
|
|
|
]; |
391
|
|
|
|
392
|
|
|
self::assertEquals($sql, $this->platform->getCreateTableSQL($table)); |
393
|
|
|
} |
394
|
|
|
|
395
|
|
|
public function testAlterTable() : void |
396
|
|
|
{ |
397
|
|
|
$table = new Table('user'); |
398
|
|
|
$table->addColumn('id', 'integer'); |
399
|
|
|
$table->addColumn('article', 'integer'); |
400
|
|
|
$table->addColumn('post', 'integer'); |
401
|
|
|
$table->addColumn('parent', 'integer'); |
402
|
|
|
$table->setPrimaryKey(['id']); |
403
|
|
|
$table->addForeignKeyConstraint('article', ['article'], ['id'], ['deferrable' => true]); |
404
|
|
|
$table->addForeignKeyConstraint('post', ['post'], ['id'], ['deferred' => true]); |
405
|
|
|
$table->addForeignKeyConstraint('user', ['parent'], ['id'], ['deferrable' => true, 'deferred' => true]); |
406
|
|
|
$table->addIndex(['article', 'post'], 'index1'); |
407
|
|
|
|
408
|
|
|
$diff = new TableDiff('user'); |
409
|
|
|
$diff->fromTable = $table; |
410
|
|
|
$diff->newName = 'client'; |
411
|
|
|
$diff->renamedColumns['id'] = new Column('key', Type::getType('integer'), []); |
412
|
|
|
$diff->renamedColumns['post'] = new Column('comment', Type::getType('integer'), []); |
413
|
|
|
$diff->removedColumns['parent'] = new Column('comment', Type::getType('integer'), []); |
414
|
|
|
$diff->removedIndexes['index1'] = $table->getIndex('index1'); |
415
|
|
|
|
416
|
|
|
$sql = [ |
417
|
|
|
'DROP INDEX IDX_8D93D64923A0E66', |
418
|
|
|
'DROP INDEX IDX_8D93D6495A8A6C8D', |
419
|
|
|
'DROP INDEX IDX_8D93D6493D8E604F', |
420
|
|
|
'DROP INDEX index1', |
421
|
|
|
'CREATE TEMPORARY TABLE __temp__user AS SELECT id, article, post FROM user', |
422
|
|
|
'DROP TABLE user', |
423
|
|
|
'CREATE TABLE user (' |
424
|
|
|
. '"key" INTEGER NOT NULL, article INTEGER NOT NULL, comment INTEGER NOT NULL' |
425
|
|
|
. ', PRIMARY KEY("key")' |
426
|
|
|
. ', CONSTRAINT FK_8D93D64923A0E66 FOREIGN KEY (article) REFERENCES article (id) DEFERRABLE INITIALLY IMMEDIATE' |
427
|
|
|
. ', CONSTRAINT FK_8D93D6495A8A6C8D FOREIGN KEY (comment) REFERENCES post (id) NOT DEFERRABLE INITIALLY DEFERRED' |
428
|
|
|
. ')', |
429
|
|
|
'INSERT INTO user ("key", article, comment) SELECT id, article, post FROM __temp__user', |
430
|
|
|
'DROP TABLE __temp__user', |
431
|
|
|
'ALTER TABLE user RENAME TO client', |
432
|
|
|
'CREATE INDEX IDX_8D93D64923A0E66 ON client (article)', |
433
|
|
|
'CREATE INDEX IDX_8D93D6495A8A6C8D ON client (comment)', |
434
|
|
|
]; |
435
|
|
|
|
436
|
|
|
self::assertEquals($sql, $this->platform->getAlterTableSQL($diff)); |
437
|
|
|
} |
438
|
|
|
|
439
|
|
|
/** |
440
|
|
|
* {@inheritDoc} |
441
|
|
|
*/ |
442
|
|
|
protected function getQuotedColumnInPrimaryKeySQL() : array |
443
|
|
|
{ |
444
|
|
|
return ['CREATE TABLE "quoted" ("create" VARCHAR(255) NOT NULL, PRIMARY KEY("create"))']; |
445
|
|
|
} |
446
|
|
|
|
447
|
|
|
/** |
448
|
|
|
* {@inheritDoc} |
449
|
|
|
*/ |
450
|
|
|
protected function getQuotedColumnInIndexSQL() : array |
451
|
|
|
{ |
452
|
|
|
return [ |
453
|
|
|
'CREATE TABLE "quoted" ("create" VARCHAR(255) NOT NULL)', |
454
|
|
|
'CREATE INDEX IDX_22660D028FD6E0FB ON "quoted" ("create")', |
455
|
|
|
]; |
456
|
|
|
} |
457
|
|
|
|
458
|
|
|
/** |
459
|
|
|
* {@inheritDoc} |
460
|
|
|
*/ |
461
|
|
|
protected function getQuotedNameInIndexSQL() : array |
462
|
|
|
{ |
463
|
|
|
return [ |
464
|
|
|
'CREATE TABLE test (column1 VARCHAR(255) NOT NULL)', |
465
|
|
|
'CREATE INDEX "key" ON test (column1)', |
466
|
|
|
]; |
467
|
|
|
} |
468
|
|
|
|
469
|
|
|
/** |
470
|
|
|
* {@inheritDoc} |
471
|
|
|
*/ |
472
|
|
|
protected function getQuotedColumnInForeignKeySQL() : array |
473
|
|
|
{ |
474
|
|
|
return [ |
475
|
|
|
'CREATE TABLE "quoted" (' . |
476
|
|
|
'"create" VARCHAR(255) NOT NULL, foo VARCHAR(255) NOT NULL, "bar" VARCHAR(255) NOT NULL, ' . |
477
|
|
|
'CONSTRAINT FK_WITH_RESERVED_KEYWORD FOREIGN KEY ("create", foo, "bar") REFERENCES "foreign" ("create", bar, "foo-bar") NOT DEFERRABLE INITIALLY IMMEDIATE, ' . |
478
|
|
|
'CONSTRAINT FK_WITH_NON_RESERVED_KEYWORD FOREIGN KEY ("create", foo, "bar") REFERENCES foo ("create", bar, "foo-bar") NOT DEFERRABLE INITIALLY IMMEDIATE, ' . |
479
|
|
|
'CONSTRAINT FK_WITH_INTENDED_QUOTATION FOREIGN KEY ("create", foo, "bar") REFERENCES "foo-bar" ("create", bar, "foo-bar") NOT DEFERRABLE INITIALLY IMMEDIATE)', |
480
|
|
|
]; |
481
|
|
|
} |
482
|
|
|
|
483
|
|
|
public function getExpectedFixedLengthBinaryTypeDeclarationSQLNoLength() : string |
484
|
|
|
{ |
485
|
|
|
return 'BLOB'; |
486
|
|
|
} |
487
|
|
|
|
488
|
|
|
public function getExpectedFixedLengthBinaryTypeDeclarationSQLWithLength() : string |
489
|
|
|
{ |
490
|
|
|
return 'BLOB'; |
491
|
|
|
} |
492
|
|
|
|
493
|
|
|
public function getExpectedVariableLengthBinaryTypeDeclarationSQLNoLength() : string |
494
|
|
|
{ |
495
|
|
|
return 'BLOB'; |
496
|
|
|
} |
497
|
|
|
|
498
|
|
|
public function getExpectedVariableLengthBinaryTypeDeclarationSQLWithLength() : string |
499
|
|
|
{ |
500
|
|
|
return 'BLOB'; |
501
|
|
|
} |
502
|
|
|
|
503
|
|
|
/** |
504
|
|
|
* {@inheritDoc} |
505
|
|
|
* |
506
|
|
|
* @group DBAL-234 |
507
|
|
|
*/ |
508
|
|
|
protected function getAlterTableRenameIndexSQL() : array |
509
|
|
|
{ |
510
|
|
|
return [ |
511
|
|
|
'CREATE TEMPORARY TABLE __temp__mytable AS SELECT id FROM mytable', |
512
|
|
|
'DROP TABLE mytable', |
513
|
|
|
'CREATE TABLE mytable (id INTEGER NOT NULL, PRIMARY KEY(id))', |
514
|
|
|
'INSERT INTO mytable (id) SELECT id FROM __temp__mytable', |
515
|
|
|
'DROP TABLE __temp__mytable', |
516
|
|
|
'CREATE INDEX idx_bar ON mytable (id)', |
517
|
|
|
]; |
518
|
|
|
} |
519
|
|
|
|
520
|
|
|
/** |
521
|
|
|
* {@inheritDoc} |
522
|
|
|
* |
523
|
|
|
* @group DBAL-234 |
524
|
|
|
*/ |
525
|
|
|
protected function getQuotedAlterTableRenameIndexSQL() : array |
526
|
|
|
{ |
527
|
|
|
return [ |
528
|
|
|
'CREATE TEMPORARY TABLE __temp__table AS SELECT id FROM "table"', |
529
|
|
|
'DROP TABLE "table"', |
530
|
|
|
'CREATE TABLE "table" (id INTEGER NOT NULL, PRIMARY KEY(id))', |
531
|
|
|
'INSERT INTO "table" (id) SELECT id FROM __temp__table', |
532
|
|
|
'DROP TABLE __temp__table', |
533
|
|
|
'CREATE INDEX "select" ON "table" (id)', |
534
|
|
|
'CREATE INDEX "bar" ON "table" (id)', |
535
|
|
|
]; |
536
|
|
|
} |
537
|
|
|
|
538
|
|
|
/** |
539
|
|
|
* {@inheritdoc} |
540
|
|
|
*/ |
541
|
|
|
protected function getQuotedAlterTableRenameColumnSQL() : array |
542
|
|
|
{ |
543
|
|
|
return [ |
544
|
|
|
'CREATE TEMPORARY TABLE __temp__mytable AS SELECT unquoted1, unquoted2, unquoted3, "create", "table", "select", "quoted1", "quoted2", "quoted3" FROM mytable', |
545
|
|
|
'DROP TABLE mytable', |
546
|
|
|
'CREATE TABLE mytable (unquoted INTEGER NOT NULL --Unquoted 1 |
547
|
|
|
, "where" INTEGER NOT NULL --Unquoted 2 |
548
|
|
|
, "foo" INTEGER NOT NULL --Unquoted 3 |
549
|
|
|
, reserved_keyword INTEGER NOT NULL --Reserved keyword 1 |
550
|
|
|
, "from" INTEGER NOT NULL --Reserved keyword 2 |
551
|
|
|
, "bar" INTEGER NOT NULL --Reserved keyword 3 |
552
|
|
|
, quoted INTEGER NOT NULL --Quoted 1 |
553
|
|
|
, "and" INTEGER NOT NULL --Quoted 2 |
554
|
|
|
, "baz" INTEGER NOT NULL --Quoted 3 |
555
|
|
|
)', |
556
|
|
|
'INSERT INTO mytable (unquoted, "where", "foo", reserved_keyword, "from", "bar", quoted, "and", "baz") SELECT unquoted1, unquoted2, unquoted3, "create", "table", "select", "quoted1", "quoted2", "quoted3" FROM __temp__mytable', |
557
|
|
|
'DROP TABLE __temp__mytable', |
558
|
|
|
]; |
559
|
|
|
} |
560
|
|
|
|
561
|
|
|
/** |
562
|
|
|
* {@inheritdoc} |
563
|
|
|
*/ |
564
|
|
|
protected function getQuotedAlterTableChangeColumnLengthSQL() : array |
565
|
|
|
{ |
566
|
|
|
return [ |
567
|
|
|
'CREATE TEMPORARY TABLE __temp__mytable AS SELECT unquoted1, unquoted2, unquoted3, "create", "table", "select" FROM mytable', |
568
|
|
|
'DROP TABLE mytable', |
569
|
|
|
'CREATE TABLE mytable (unquoted1 VARCHAR(255) NOT NULL --Unquoted 1 |
570
|
|
|
, unquoted2 VARCHAR(255) NOT NULL --Unquoted 2 |
571
|
|
|
, unquoted3 VARCHAR(255) NOT NULL --Unquoted 3 |
572
|
|
|
, "create" VARCHAR(255) NOT NULL --Reserved keyword 1 |
573
|
|
|
, "table" VARCHAR(255) NOT NULL --Reserved keyword 2 |
574
|
|
|
, "select" VARCHAR(255) NOT NULL --Reserved keyword 3 |
575
|
|
|
)', |
576
|
|
|
'INSERT INTO mytable (unquoted1, unquoted2, unquoted3, "create", "table", "select") SELECT unquoted1, unquoted2, unquoted3, "create", "table", "select" FROM __temp__mytable', |
577
|
|
|
'DROP TABLE __temp__mytable', |
578
|
|
|
]; |
579
|
|
|
} |
580
|
|
|
|
581
|
|
|
/** |
582
|
|
|
* @group DBAL-807 |
583
|
|
|
*/ |
584
|
|
|
public function testAlterTableRenameIndexInSchema() : void |
585
|
|
|
{ |
586
|
|
|
self::markTestIncomplete( |
587
|
|
|
'Test currently produces broken SQL due to SQLitePlatform::getAlterTable being broken ' . |
588
|
|
|
'when used with schemas.' |
589
|
|
|
); |
590
|
|
|
} |
591
|
|
|
|
592
|
|
|
/** |
593
|
|
|
* @group DBAL-807 |
594
|
|
|
*/ |
595
|
|
|
public function testQuotesAlterTableRenameIndexInSchema() : void |
596
|
|
|
{ |
597
|
|
|
self::markTestIncomplete( |
598
|
|
|
'Test currently produces broken SQL due to SQLitePlatform::getAlterTable being broken ' . |
599
|
|
|
'when used with schemas.' |
600
|
|
|
); |
601
|
|
|
} |
602
|
|
|
|
603
|
|
|
/** |
604
|
|
|
* @group DBAL-423 |
605
|
|
|
*/ |
606
|
|
|
public function testReturnsGuidTypeDeclarationSQL() : void |
607
|
|
|
{ |
608
|
|
|
self::assertSame('CHAR(36)', $this->platform->getGuidTypeDeclarationSQL([])); |
609
|
|
|
} |
610
|
|
|
|
611
|
|
|
/** |
612
|
|
|
* {@inheritdoc} |
613
|
|
|
*/ |
614
|
|
|
public function getAlterTableRenameColumnSQL() : array |
615
|
|
|
{ |
616
|
|
|
return [ |
617
|
|
|
'CREATE TEMPORARY TABLE __temp__foo AS SELECT bar FROM foo', |
618
|
|
|
'DROP TABLE foo', |
619
|
|
|
'CREATE TABLE foo (baz INTEGER DEFAULT 666 NOT NULL --rename test |
620
|
|
|
)', |
621
|
|
|
'INSERT INTO foo (baz) SELECT bar FROM __temp__foo', |
622
|
|
|
'DROP TABLE __temp__foo', |
623
|
|
|
]; |
624
|
|
|
} |
625
|
|
|
|
626
|
|
|
/** |
627
|
|
|
* {@inheritdoc} |
628
|
|
|
*/ |
629
|
|
|
protected function getQuotesTableIdentifiersInAlterTableSQL() : array |
630
|
|
|
{ |
631
|
|
|
return [ |
632
|
|
|
'DROP INDEX IDX_8C736521A81E660E', |
633
|
|
|
'DROP INDEX IDX_8C736521FDC58D6C', |
634
|
|
|
'CREATE TEMPORARY TABLE __temp__foo AS SELECT fk, fk2, id, fk3, bar FROM "foo"', |
635
|
|
|
'DROP TABLE "foo"', |
636
|
|
|
'CREATE TABLE "foo" (fk2 INTEGER NOT NULL, fk3 INTEGER NOT NULL, fk INTEGER NOT NULL, war INTEGER NOT NULL, ' . |
637
|
|
|
'bar INTEGER DEFAULT NULL, bloo INTEGER NOT NULL, ' . |
638
|
|
|
'CONSTRAINT fk2 FOREIGN KEY (fk2) REFERENCES fk_table2 (id) NOT DEFERRABLE INITIALLY IMMEDIATE, ' . |
639
|
|
|
'CONSTRAINT fk_add FOREIGN KEY (fk3) REFERENCES fk_table (id) NOT DEFERRABLE INITIALLY IMMEDIATE)', |
640
|
|
|
'INSERT INTO "foo" (fk, fk2, war, fk3, bar) SELECT fk, fk2, id, fk3, bar FROM __temp__foo', |
641
|
|
|
'DROP TABLE __temp__foo', |
642
|
|
|
'ALTER TABLE "foo" RENAME TO "table"', |
643
|
|
|
'CREATE INDEX IDX_8C736521A81E660E ON "table" (fk)', |
644
|
|
|
'CREATE INDEX IDX_8C736521FDC58D6C ON "table" (fk2)', |
645
|
|
|
]; |
646
|
|
|
} |
647
|
|
|
|
648
|
|
|
/** |
649
|
|
|
* {@inheritdoc} |
650
|
|
|
*/ |
651
|
|
|
protected function getCommentOnColumnSQL() : array |
652
|
|
|
{ |
653
|
|
|
return [ |
654
|
|
|
'COMMENT ON COLUMN foo.bar IS \'comment\'', |
655
|
|
|
'COMMENT ON COLUMN "Foo"."BAR" IS \'comment\'', |
656
|
|
|
'COMMENT ON COLUMN "select"."from" IS \'comment\'', |
657
|
|
|
]; |
658
|
|
|
} |
659
|
|
|
|
660
|
|
|
protected static function getInlineColumnCommentDelimiter() : string |
661
|
|
|
{ |
662
|
|
|
return "\n"; |
663
|
|
|
} |
664
|
|
|
|
665
|
|
|
protected static function getInlineColumnRegularCommentSQL() : string |
666
|
|
|
{ |
667
|
|
|
return "--Regular comment\n"; |
668
|
|
|
} |
669
|
|
|
|
670
|
|
|
protected static function getInlineColumnCommentRequiringEscapingSQL() : string |
671
|
|
|
{ |
672
|
|
|
return "--Using inline comment delimiter \n-- works\n"; |
673
|
|
|
} |
674
|
|
|
|
675
|
|
|
protected static function getInlineColumnEmptyCommentSQL() : string |
676
|
|
|
{ |
677
|
|
|
return ''; |
678
|
|
|
} |
679
|
|
|
|
680
|
|
|
protected function getQuotesReservedKeywordInUniqueConstraintDeclarationSQL() : string |
681
|
|
|
{ |
682
|
|
|
return 'CONSTRAINT "select" UNIQUE (foo)'; |
683
|
|
|
} |
684
|
|
|
|
685
|
|
|
protected function getQuotesReservedKeywordInIndexDeclarationSQL() : string |
686
|
|
|
{ |
687
|
|
|
return 'INDEX "select" (foo)'; |
688
|
|
|
} |
689
|
|
|
|
690
|
|
|
protected function getQuotesReservedKeywordInTruncateTableSQL() : string |
691
|
|
|
{ |
692
|
|
|
return 'DELETE FROM "select"'; |
693
|
|
|
} |
694
|
|
|
|
695
|
|
|
/** |
696
|
|
|
* {@inheritdoc} |
697
|
|
|
*/ |
698
|
|
|
protected function getAlterStringToFixedStringSQL() : array |
699
|
|
|
{ |
700
|
|
|
return [ |
701
|
|
|
'CREATE TEMPORARY TABLE __temp__mytable AS SELECT name FROM mytable', |
702
|
|
|
'DROP TABLE mytable', |
703
|
|
|
'CREATE TABLE mytable (name CHAR(2) NOT NULL)', |
704
|
|
|
'INSERT INTO mytable (name) SELECT name FROM __temp__mytable', |
705
|
|
|
'DROP TABLE __temp__mytable', |
706
|
|
|
]; |
707
|
|
|
} |
708
|
|
|
|
709
|
|
|
/** |
710
|
|
|
* {@inheritdoc} |
711
|
|
|
*/ |
712
|
|
|
protected function getGeneratesAlterTableRenameIndexUsedByForeignKeySQL() : array |
713
|
|
|
{ |
714
|
|
|
return [ |
715
|
|
|
'DROP INDEX idx_foo', |
716
|
|
|
'DROP INDEX idx_bar', |
717
|
|
|
'CREATE TEMPORARY TABLE __temp__mytable AS SELECT foo, bar, baz FROM mytable', |
718
|
|
|
'DROP TABLE mytable', |
719
|
|
|
'CREATE TABLE mytable (foo INTEGER NOT NULL, bar INTEGER NOT NULL, baz INTEGER NOT NULL, CONSTRAINT fk_foo FOREIGN KEY (foo) REFERENCES foreign_table (id) NOT DEFERRABLE INITIALLY IMMEDIATE, CONSTRAINT fk_bar FOREIGN KEY (bar) REFERENCES foreign_table (id) NOT DEFERRABLE INITIALLY IMMEDIATE)', |
720
|
|
|
'INSERT INTO mytable (foo, bar, baz) SELECT foo, bar, baz FROM __temp__mytable', |
721
|
|
|
'DROP TABLE __temp__mytable', |
722
|
|
|
'CREATE INDEX idx_bar ON mytable (bar)', |
723
|
|
|
'CREATE INDEX idx_foo_renamed ON mytable (foo)', |
724
|
|
|
]; |
725
|
|
|
} |
726
|
|
|
|
727
|
|
|
/** |
728
|
|
|
* @group DBAL-2436 |
729
|
|
|
*/ |
730
|
|
|
public function testQuotesTableNameInListTableConstraintsSQL() : void |
731
|
|
|
{ |
732
|
|
|
self::assertStringContainsStringIgnoringCase( |
733
|
|
|
"'Foo''Bar\\'", |
734
|
|
|
$this->platform->getListTableConstraintsSQL("Foo'Bar\\") |
735
|
|
|
); |
736
|
|
|
} |
737
|
|
|
|
738
|
|
|
/** |
739
|
|
|
* @group DBAL-2436 |
740
|
|
|
*/ |
741
|
|
|
public function testQuotesTableNameInListTableColumnsSQL() : void |
742
|
|
|
{ |
743
|
|
|
self::assertStringContainsStringIgnoringCase( |
744
|
|
|
"'Foo''Bar\\'", |
745
|
|
|
$this->platform->getListTableColumnsSQL("Foo'Bar\\") |
746
|
|
|
); |
747
|
|
|
} |
748
|
|
|
|
749
|
|
|
/** |
750
|
|
|
* @group DBAL-2436 |
751
|
|
|
*/ |
752
|
|
|
public function testQuotesTableNameInListTableIndexesSQL() : void |
753
|
|
|
{ |
754
|
|
|
self::assertStringContainsStringIgnoringCase( |
755
|
|
|
"'Foo''Bar\\'", |
756
|
|
|
$this->platform->getListTableIndexesSQL("Foo'Bar\\") |
757
|
|
|
); |
758
|
|
|
} |
759
|
|
|
|
760
|
|
|
/** |
761
|
|
|
* @group DBAL-2436 |
762
|
|
|
*/ |
763
|
|
|
public function testQuotesTableNameInListTableForeignKeysSQL() : void |
764
|
|
|
{ |
765
|
|
|
self::assertStringContainsStringIgnoringCase( |
766
|
|
|
"'Foo''Bar\\'", |
767
|
|
|
$this->platform->getListTableForeignKeysSQL("Foo'Bar\\") |
768
|
|
|
); |
769
|
|
|
} |
770
|
|
|
|
771
|
|
|
public function testDateAddStaticNumberOfDays() : void |
772
|
|
|
{ |
773
|
|
|
self::assertSame("DATETIME(rentalBeginsOn,'+' || 12 || ' DAY')", $this->platform->getDateAddDaysExpression('rentalBeginsOn', '12')); |
774
|
|
|
} |
775
|
|
|
|
776
|
|
|
public function testDateAddNumberOfDaysFromColumn() : void |
777
|
|
|
{ |
778
|
|
|
self::assertSame("DATETIME(rentalBeginsOn,'+' || duration || ' DAY')", $this->platform->getDateAddDaysExpression('rentalBeginsOn', 'duration')); |
779
|
|
|
} |
780
|
|
|
|
781
|
|
|
public function testSupportsColumnCollation() : void |
782
|
|
|
{ |
783
|
|
|
self::assertTrue($this->platform->supportsColumnCollation()); |
784
|
|
|
} |
785
|
|
|
|
786
|
|
|
public function testColumnCollationDeclarationSQL() : void |
787
|
|
|
{ |
788
|
|
|
self::assertSame( |
789
|
|
|
'COLLATE NOCASE', |
790
|
|
|
$this->platform->getColumnCollationDeclarationSQL('NOCASE') |
791
|
|
|
); |
792
|
|
|
} |
793
|
|
|
|
794
|
|
|
public function testGetCreateTableSQLWithColumnCollation() : void |
795
|
|
|
{ |
796
|
|
|
$table = new Table('foo'); |
797
|
|
|
$table->addColumn('no_collation', 'string', ['length' => 255]); |
798
|
|
|
$table->addColumn('column_collation', 'string', ['length' => 255])->setPlatformOption('collation', 'NOCASE'); |
799
|
|
|
|
800
|
|
|
self::assertSame( |
801
|
|
|
['CREATE TABLE foo (no_collation VARCHAR(255) NOT NULL, column_collation VARCHAR(255) NOT NULL COLLATE NOCASE)'], |
802
|
|
|
$this->platform->getCreateTableSQL($table), |
803
|
|
|
'Column "no_collation" will use the default collation (BINARY) and "column_collation" overwrites the collation on this column' |
804
|
|
|
); |
805
|
|
|
} |
806
|
|
|
} |
807
|
|
|
|
For hinted functions/methods where all return statements with the correct type are only reachable via conditions, ?null? gets implicitly returned which may be incompatible with the hinted type. Let?s take a look at an example: