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\Exception\ColumnLengthRequired; |
||
9 | use Doctrine\DBAL\Platforms\AbstractPlatform; |
||
10 | use Doctrine\DBAL\Platforms\OraclePlatform; |
||
11 | use Doctrine\DBAL\Schema\Column; |
||
12 | use Doctrine\DBAL\Schema\ColumnDiff; |
||
13 | use Doctrine\DBAL\Schema\Comparator; |
||
14 | use Doctrine\DBAL\Schema\ForeignKeyConstraint; |
||
15 | use Doctrine\DBAL\Schema\Sequence; |
||
16 | use Doctrine\DBAL\Schema\Table; |
||
17 | use Doctrine\DBAL\Schema\TableDiff; |
||
18 | use Doctrine\DBAL\TransactionIsolationLevel; |
||
19 | use Doctrine\DBAL\Types\Type; |
||
20 | use function array_walk; |
||
21 | use function assert; |
||
22 | use function preg_replace; |
||
23 | use function sprintf; |
||
24 | use function strtoupper; |
||
25 | use function uniqid; |
||
26 | |||
27 | class OraclePlatformTest extends AbstractPlatformTestCase |
||
28 | { |
||
29 | /** |
||
30 | * @return mixed[][] |
||
31 | */ |
||
32 | public static function dataValidIdentifiers() : iterable |
||
33 | { |
||
34 | return [ |
||
35 | ['a'], |
||
36 | ['foo'], |
||
37 | ['Foo'], |
||
38 | ['Foo123'], |
||
39 | ['Foo#bar_baz$'], |
||
40 | ['"a"'], |
||
41 | ['"1"'], |
||
42 | ['"foo_bar"'], |
||
43 | ['"@$%&!"'], |
||
44 | ]; |
||
45 | } |
||
46 | |||
47 | /** |
||
48 | * @dataProvider dataValidIdentifiers |
||
49 | */ |
||
50 | public function testValidIdentifiers(string $identifier) : void |
||
51 | { |
||
52 | OraclePlatform::assertValidIdentifier($identifier); |
||
53 | |||
54 | $this->addToAssertionCount(1); |
||
55 | } |
||
56 | |||
57 | /** |
||
58 | * @return mixed[][] |
||
59 | */ |
||
60 | public static function dataInvalidIdentifiers() : iterable |
||
61 | { |
||
62 | return [ |
||
63 | ['1'], |
||
64 | ['abc&'], |
||
65 | ['abc-def'], |
||
66 | ['"'], |
||
67 | ['"foo"bar"'], |
||
68 | ]; |
||
69 | } |
||
70 | |||
71 | /** |
||
72 | * @dataProvider dataInvalidIdentifiers |
||
73 | */ |
||
74 | public function testInvalidIdentifiers(string $identifier) : void |
||
75 | { |
||
76 | $this->expectException(DBALException::class); |
||
77 | |||
78 | OraclePlatform::assertValidIdentifier($identifier); |
||
79 | } |
||
80 | |||
81 | public function createPlatform() : AbstractPlatform |
||
82 | { |
||
83 | return new OraclePlatform(); |
||
84 | } |
||
85 | |||
86 | public function getGenerateTableSql() : string |
||
87 | { |
||
88 | return 'CREATE TABLE test (id NUMBER(10) NOT NULL, test VARCHAR2(255) DEFAULT NULL NULL, PRIMARY KEY(id))'; |
||
89 | } |
||
90 | |||
91 | /** |
||
92 | * {@inheritDoc} |
||
93 | */ |
||
94 | public function getGenerateTableWithMultiColumnUniqueIndexSql() : array |
||
95 | { |
||
96 | return [ |
||
97 | 'CREATE TABLE test (foo VARCHAR2(255) DEFAULT NULL NULL, bar VARCHAR2(255) DEFAULT NULL NULL)', |
||
98 | 'CREATE UNIQUE INDEX UNIQ_D87F7E0C8C73652176FF8CAA ON test (foo, bar)', |
||
99 | ]; |
||
100 | } |
||
101 | |||
102 | /** |
||
103 | * {@inheritDoc} |
||
104 | */ |
||
105 | public function getGenerateAlterTableSql() : array |
||
106 | { |
||
107 | return [ |
||
108 | 'ALTER TABLE mytable ADD (quota NUMBER(10) DEFAULT NULL NULL)', |
||
109 | "ALTER TABLE mytable MODIFY (baz VARCHAR2(255) DEFAULT 'def' NOT NULL, bloo NUMBER(1) DEFAULT '0' NOT NULL)", |
||
110 | 'ALTER TABLE mytable DROP (foo)', |
||
111 | 'ALTER TABLE mytable RENAME TO userlist', |
||
112 | ]; |
||
113 | } |
||
114 | |||
115 | public function testRLike() : void |
||
116 | { |
||
117 | $this->expectException(DBALException::class); |
||
118 | |||
119 | self::assertEquals('RLIKE', $this->platform->getRegexpExpression(), 'Regular expression operator is not correct'); |
||
120 | } |
||
121 | |||
122 | public function testGeneratesSqlSnippets() : void |
||
123 | { |
||
124 | self::assertEquals('"', $this->platform->getIdentifierQuoteCharacter(), 'Identifier quote character is not correct'); |
||
125 | self::assertEquals('column1 || column2 || column3', $this->platform->getConcatExpression('column1', 'column2', 'column3'), 'Concatenation expression is not correct'); |
||
126 | } |
||
127 | |||
128 | public function testGeneratesTransactionsCommands() : void |
||
129 | { |
||
130 | self::assertEquals( |
||
131 | 'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED', |
||
132 | $this->platform->getSetTransactionIsolationSQL(TransactionIsolationLevel::READ_UNCOMMITTED) |
||
133 | ); |
||
134 | self::assertEquals( |
||
135 | 'SET TRANSACTION ISOLATION LEVEL READ COMMITTED', |
||
136 | $this->platform->getSetTransactionIsolationSQL(TransactionIsolationLevel::READ_COMMITTED) |
||
137 | ); |
||
138 | self::assertEquals( |
||
139 | 'SET TRANSACTION ISOLATION LEVEL SERIALIZABLE', |
||
140 | $this->platform->getSetTransactionIsolationSQL(TransactionIsolationLevel::REPEATABLE_READ) |
||
141 | ); |
||
142 | self::assertEquals( |
||
143 | 'SET TRANSACTION ISOLATION LEVEL SERIALIZABLE', |
||
144 | $this->platform->getSetTransactionIsolationSQL(TransactionIsolationLevel::SERIALIZABLE) |
||
145 | ); |
||
146 | } |
||
147 | |||
148 | public function testCreateDatabaseThrowsException() : void |
||
149 | { |
||
150 | $this->expectException(DBALException::class); |
||
151 | |||
152 | self::assertEquals('CREATE DATABASE foobar', $this->platform->getCreateDatabaseSQL('foobar')); |
||
153 | } |
||
154 | |||
155 | public function testDropDatabaseThrowsException() : void |
||
156 | { |
||
157 | self::assertEquals('DROP USER foobar CASCADE', $this->platform->getDropDatabaseSQL('foobar')); |
||
158 | } |
||
159 | |||
160 | public function testDropTable() : void |
||
161 | { |
||
162 | self::assertEquals('DROP TABLE foobar', $this->platform->getDropTableSQL('foobar')); |
||
163 | } |
||
164 | |||
165 | public function testGeneratesTypeDeclarationForIntegers() : void |
||
166 | { |
||
167 | self::assertEquals( |
||
168 | 'NUMBER(10)', |
||
169 | $this->platform->getIntegerTypeDeclarationSQL([]) |
||
170 | ); |
||
171 | self::assertEquals( |
||
172 | 'NUMBER(10)', |
||
173 | $this->platform->getIntegerTypeDeclarationSQL(['autoincrement' => true]) |
||
174 | ); |
||
175 | self::assertEquals( |
||
176 | 'NUMBER(10)', |
||
177 | $this->platform->getIntegerTypeDeclarationSQL( |
||
178 | ['autoincrement' => true, 'primary' => true] |
||
179 | ) |
||
180 | ); |
||
181 | } |
||
182 | |||
183 | public function testPrefersIdentityColumns() : void |
||
184 | { |
||
185 | self::assertFalse($this->platform->prefersIdentityColumns()); |
||
186 | } |
||
187 | |||
188 | public function testSupportsIdentityColumns() : void |
||
189 | { |
||
190 | self::assertFalse($this->platform->supportsIdentityColumns()); |
||
191 | } |
||
192 | |||
193 | public function testSupportsSavePoints() : void |
||
194 | { |
||
195 | self::assertTrue($this->platform->supportsSavepoints()); |
||
196 | } |
||
197 | |||
198 | protected function supportsCommentOnStatement() : bool |
||
199 | { |
||
200 | return true; |
||
201 | } |
||
202 | |||
203 | public function getGenerateIndexSql() : string |
||
204 | { |
||
205 | return 'CREATE INDEX my_idx ON mytable (user_name, last_login)'; |
||
206 | } |
||
207 | |||
208 | public function getGenerateUniqueIndexSql() : string |
||
209 | { |
||
210 | return 'CREATE UNIQUE INDEX index_name ON test (test, test2)'; |
||
211 | } |
||
212 | |||
213 | public function getGenerateForeignKeySql() : string |
||
214 | { |
||
215 | return 'ALTER TABLE test ADD FOREIGN KEY (fk_name_id) REFERENCES other_table (id)'; |
||
216 | } |
||
217 | |||
218 | /** |
||
219 | * @param mixed[] $options |
||
220 | * |
||
221 | * @group DBAL-1097 |
||
222 | * @dataProvider getGeneratesAdvancedForeignKeyOptionsSQLData |
||
223 | */ |
||
224 | public function testGeneratesAdvancedForeignKeyOptionsSQL(array $options, string $expectedSql) : void |
||
225 | { |
||
226 | $foreignKey = new ForeignKeyConstraint(['foo'], 'foreign_table', ['bar'], '', $options); |
||
227 | |||
228 | self::assertSame($expectedSql, $this->platform->getAdvancedForeignKeyOptionsSQL($foreignKey)); |
||
229 | } |
||
230 | |||
231 | /** |
||
232 | * @return mixed[][] |
||
233 | */ |
||
234 | public static function getGeneratesAdvancedForeignKeyOptionsSQLData() : iterable |
||
235 | { |
||
236 | return [ |
||
237 | [[], ''], |
||
238 | [['onUpdate' => 'CASCADE'], ''], |
||
239 | [['onDelete' => 'CASCADE'], ' ON DELETE CASCADE'], |
||
240 | [['onDelete' => 'NO ACTION'], ''], |
||
241 | [['onDelete' => 'RESTRICT'], ''], |
||
242 | [['onUpdate' => 'SET NULL', 'onDelete' => 'SET NULL'], ' ON DELETE SET NULL'], |
||
243 | ]; |
||
244 | } |
||
245 | |||
246 | /** |
||
247 | * {@inheritdoc} |
||
248 | */ |
||
249 | public static function getReturnsForeignKeyReferentialActionSQL() : iterable |
||
250 | { |
||
251 | return [ |
||
252 | ['CASCADE', 'CASCADE'], |
||
253 | ['SET NULL', 'SET NULL'], |
||
254 | ['NO ACTION', ''], |
||
255 | ['RESTRICT', ''], |
||
256 | ['CaScAdE', 'CASCADE'], |
||
257 | ]; |
||
258 | } |
||
259 | |||
260 | public function testModifyLimitQuery() : void |
||
261 | { |
||
262 | $sql = $this->platform->modifyLimitQuery('SELECT * FROM user', 10, 0); |
||
263 | self::assertEquals('SELECT a.* FROM (SELECT * FROM user) a WHERE ROWNUM <= 10', $sql); |
||
264 | } |
||
265 | |||
266 | public function testModifyLimitQueryWithEmptyOffset() : void |
||
267 | { |
||
268 | $sql = $this->platform->modifyLimitQuery('SELECT * FROM user', 10); |
||
269 | self::assertEquals('SELECT a.* FROM (SELECT * FROM user) a WHERE ROWNUM <= 10', $sql); |
||
270 | } |
||
271 | |||
272 | public function testModifyLimitQueryWithNonEmptyOffset() : void |
||
273 | { |
||
274 | $sql = $this->platform->modifyLimitQuery('SELECT * FROM user', 10, 10); |
||
275 | self::assertEquals('SELECT * FROM (SELECT a.*, ROWNUM AS doctrine_rownum FROM (SELECT * FROM user) a WHERE ROWNUM <= 20) WHERE doctrine_rownum >= 11', $sql); |
||
276 | } |
||
277 | |||
278 | public function testModifyLimitQueryWithEmptyLimit() : void |
||
279 | { |
||
280 | $sql = $this->platform->modifyLimitQuery('SELECT * FROM user', null, 10); |
||
281 | self::assertEquals('SELECT * FROM (SELECT a.*, ROWNUM AS doctrine_rownum FROM (SELECT * FROM user) a) WHERE doctrine_rownum >= 11', $sql); |
||
282 | } |
||
283 | |||
284 | public function testModifyLimitQueryWithAscOrderBy() : void |
||
285 | { |
||
286 | $sql = $this->platform->modifyLimitQuery('SELECT * FROM user ORDER BY username ASC', 10); |
||
287 | self::assertEquals('SELECT a.* FROM (SELECT * FROM user ORDER BY username ASC) a WHERE ROWNUM <= 10', $sql); |
||
288 | } |
||
289 | |||
290 | public function testModifyLimitQueryWithDescOrderBy() : void |
||
291 | { |
||
292 | $sql = $this->platform->modifyLimitQuery('SELECT * FROM user ORDER BY username DESC', 10); |
||
293 | self::assertEquals('SELECT a.* FROM (SELECT * FROM user ORDER BY username DESC) a WHERE ROWNUM <= 10', $sql); |
||
294 | } |
||
295 | |||
296 | public function testGenerateTableWithAutoincrement() : void |
||
297 | { |
||
298 | $columnName = strtoupper('id' . uniqid()); |
||
299 | $tableName = strtoupper('table' . uniqid()); |
||
300 | $table = new Table($tableName); |
||
301 | |||
302 | $column = $table->addColumn($columnName, 'integer'); |
||
303 | $column->setAutoincrement(true); |
||
304 | $targets = [ |
||
305 | sprintf('CREATE TABLE %s (%s NUMBER(10) NOT NULL)', $tableName, $columnName), |
||
306 | sprintf( |
||
307 | "DECLARE constraints_Count NUMBER; BEGIN SELECT COUNT(CONSTRAINT_NAME) INTO constraints_Count FROM USER_CONSTRAINTS WHERE TABLE_NAME = '%s' AND CONSTRAINT_TYPE = 'P'; IF constraints_Count = 0 OR constraints_Count = '' THEN EXECUTE IMMEDIATE 'ALTER TABLE %s ADD CONSTRAINT %s_AI_PK PRIMARY KEY (%s)'; END IF; END;", |
||
308 | $tableName, |
||
309 | $tableName, |
||
310 | $tableName, |
||
311 | $columnName |
||
312 | ), |
||
313 | sprintf('CREATE SEQUENCE %s_SEQ START WITH 1 MINVALUE 1 INCREMENT BY 1', $tableName), |
||
314 | sprintf( |
||
315 | "CREATE TRIGGER %s_AI_PK BEFORE INSERT ON %s FOR EACH ROW DECLARE last_Sequence NUMBER; last_InsertID NUMBER; BEGIN SELECT %s_SEQ.NEXTVAL INTO :NEW.%s FROM DUAL; IF (:NEW.%s IS NULL OR :NEW.%s = 0) THEN SELECT %s_SEQ.NEXTVAL INTO :NEW.%s FROM DUAL; ELSE SELECT NVL(Last_Number, 0) INTO last_Sequence FROM User_Sequences WHERE Sequence_Name = '%s_SEQ'; SELECT :NEW.%s INTO last_InsertID FROM DUAL; WHILE (last_InsertID > last_Sequence) LOOP SELECT %s_SEQ.NEXTVAL INTO last_Sequence FROM DUAL; END LOOP; END IF; END;", |
||
316 | $tableName, |
||
317 | $tableName, |
||
318 | $tableName, |
||
319 | $columnName, |
||
320 | $columnName, |
||
321 | $columnName, |
||
322 | $tableName, |
||
323 | $columnName, |
||
324 | $tableName, |
||
325 | $columnName, |
||
326 | $tableName |
||
327 | ), |
||
328 | ]; |
||
329 | $statements = $this->platform->getCreateTableSQL($table); |
||
330 | //strip all the whitespace from the statements |
||
331 | array_walk($statements, static function (&$value) : void { |
||
332 | $value = preg_replace('/\s+/', ' ', $value); |
||
333 | }); |
||
334 | foreach ($targets as $key => $sql) { |
||
335 | self::assertArrayHasKey($key, $statements); |
||
336 | self::assertEquals($sql, $statements[$key]); |
||
337 | } |
||
338 | } |
||
339 | |||
340 | /** |
||
341 | * {@inheritDoc} |
||
342 | */ |
||
343 | public function getCreateTableColumnCommentsSQL() : array |
||
344 | { |
||
345 | return [ |
||
346 | 'CREATE TABLE test (id NUMBER(10) NOT NULL, PRIMARY KEY(id))', |
||
347 | "COMMENT ON COLUMN test.id IS 'This is a comment'", |
||
348 | ]; |
||
349 | } |
||
350 | |||
351 | /** |
||
352 | * {@inheritDoc} |
||
353 | */ |
||
354 | public function getCreateTableColumnTypeCommentsSQL() : array |
||
355 | { |
||
356 | return [ |
||
357 | 'CREATE TABLE test (id NUMBER(10) NOT NULL, data CLOB NOT NULL, PRIMARY KEY(id))', |
||
358 | "COMMENT ON COLUMN test.data IS '(DC2Type:array)'", |
||
359 | ]; |
||
360 | } |
||
361 | |||
362 | /** |
||
363 | * {@inheritDoc} |
||
364 | */ |
||
365 | public function getAlterTableColumnCommentsSQL() : array |
||
366 | { |
||
367 | return [ |
||
368 | 'ALTER TABLE mytable ADD (quota NUMBER(10) NOT NULL)', |
||
369 | "COMMENT ON COLUMN mytable.quota IS 'A comment'", |
||
370 | "COMMENT ON COLUMN mytable.foo IS ''", |
||
371 | "COMMENT ON COLUMN mytable.baz IS 'B comment'", |
||
372 | ]; |
||
373 | } |
||
374 | |||
375 | public function getBitAndComparisonExpressionSql(string $value1, string $value2) : string |
||
376 | { |
||
377 | return 'BITAND(' . $value1 . ', ' . $value2 . ')'; |
||
378 | } |
||
379 | |||
380 | public function getBitOrComparisonExpressionSql(string $value1, string $value2) : string |
||
381 | { |
||
382 | return '(' . $value1 . '-' . |
||
383 | $this->getBitAndComparisonExpressionSql($value1, $value2) |
||
384 | . '+' . $value2 . ')'; |
||
385 | } |
||
386 | |||
387 | /** |
||
388 | * {@inheritDoc} |
||
389 | */ |
||
390 | protected function getQuotedColumnInPrimaryKeySQL() : array |
||
391 | { |
||
392 | return ['CREATE TABLE "quoted" ("create" VARCHAR2(255) NOT NULL, PRIMARY KEY("create"))']; |
||
393 | } |
||
394 | |||
395 | /** |
||
396 | * {@inheritDoc} |
||
397 | */ |
||
398 | protected function getQuotedColumnInIndexSQL() : array |
||
399 | { |
||
400 | return [ |
||
401 | 'CREATE TABLE "quoted" ("create" VARCHAR2(255) NOT NULL)', |
||
402 | 'CREATE INDEX IDX_22660D028FD6E0FB ON "quoted" ("create")', |
||
403 | ]; |
||
404 | } |
||
405 | |||
406 | /** |
||
407 | * {@inheritDoc} |
||
408 | */ |
||
409 | protected function getQuotedNameInIndexSQL() : array |
||
410 | { |
||
411 | return [ |
||
412 | 'CREATE TABLE test (column1 VARCHAR2(255) NOT NULL)', |
||
413 | 'CREATE INDEX "key" ON test (column1)', |
||
414 | ]; |
||
415 | } |
||
416 | |||
417 | /** |
||
418 | * {@inheritDoc} |
||
419 | */ |
||
420 | protected function getQuotedColumnInForeignKeySQL() : array |
||
421 | { |
||
422 | return [ |
||
423 | 'CREATE TABLE "quoted" ("create" VARCHAR2(255) NOT NULL, foo VARCHAR2(255) NOT NULL, "bar" VARCHAR2(255) NOT NULL)', |
||
424 | 'ALTER TABLE "quoted" ADD CONSTRAINT FK_WITH_RESERVED_KEYWORD FOREIGN KEY ("create", foo, "bar") REFERENCES foreign ("create", bar, "foo-bar")', |
||
425 | 'ALTER TABLE "quoted" ADD CONSTRAINT FK_WITH_NON_RESERVED_KEYWORD FOREIGN KEY ("create", foo, "bar") REFERENCES foo ("create", bar, "foo-bar")', |
||
426 | 'ALTER TABLE "quoted" ADD CONSTRAINT FK_WITH_INTENDED_QUOTATION FOREIGN KEY ("create", foo, "bar") REFERENCES "foo-bar" ("create", bar, "foo-bar")', |
||
427 | ]; |
||
428 | } |
||
429 | |||
430 | /** |
||
431 | * @group DBAL-472 |
||
432 | * @group DBAL-1001 |
||
433 | */ |
||
434 | public function testAlterTableNotNULL() : void |
||
435 | { |
||
436 | $tableDiff = new TableDiff('mytable'); |
||
437 | |||
438 | $tableDiff->changedColumns['foo'] = new ColumnDiff( |
||
439 | 'foo', |
||
440 | new Column( |
||
441 | 'foo', |
||
442 | Type::getType('string'), |
||
443 | [ |
||
444 | 'length' => 255, |
||
445 | 'default' => 'bla', |
||
446 | 'notnull' => true, |
||
447 | ] |
||
448 | ), |
||
449 | ['type'] |
||
450 | ); |
||
451 | |||
452 | $tableDiff->changedColumns['bar'] = new ColumnDiff( |
||
453 | 'bar', |
||
454 | new Column( |
||
455 | 'baz', |
||
456 | Type::getType('string'), |
||
457 | [ |
||
458 | 'length' => 255, |
||
459 | 'default' => 'bla', |
||
460 | 'notnull' => true, |
||
461 | ] |
||
462 | ), |
||
463 | ['type', 'notnull'] |
||
464 | ); |
||
465 | |||
466 | $tableDiff->changedColumns['metar'] = new ColumnDiff( |
||
467 | 'metar', |
||
468 | new Column( |
||
469 | 'metar', |
||
470 | Type::getType('string'), |
||
471 | [ |
||
472 | 'length' => 2000, |
||
473 | 'notnull' => false, |
||
474 | ] |
||
475 | ), |
||
476 | ['notnull'] |
||
477 | ); |
||
478 | |||
479 | $expectedSql = ["ALTER TABLE mytable MODIFY (foo VARCHAR2(255) DEFAULT 'bla', baz VARCHAR2(255) DEFAULT 'bla' NOT NULL, metar VARCHAR2(2000) DEFAULT NULL NULL)"]; |
||
480 | self::assertEquals($expectedSql, $this->platform->getAlterTableSQL($tableDiff)); |
||
481 | } |
||
482 | |||
483 | /** |
||
484 | * @group DBAL-2555 |
||
485 | */ |
||
486 | public function testInitializesDoctrineTypeMappings() : void |
||
487 | { |
||
488 | self::assertTrue($this->platform->hasDoctrineTypeMappingFor('long raw')); |
||
489 | self::assertSame('blob', $this->platform->getDoctrineTypeMapping('long raw')); |
||
490 | |||
491 | self::assertTrue($this->platform->hasDoctrineTypeMappingFor('raw')); |
||
492 | self::assertSame('binary', $this->platform->getDoctrineTypeMapping('raw')); |
||
493 | |||
494 | self::assertTrue($this->platform->hasDoctrineTypeMappingFor('date')); |
||
495 | self::assertSame('date', $this->platform->getDoctrineTypeMapping('date')); |
||
496 | } |
||
497 | |||
498 | public function testGetVariableLengthStringTypeDeclarationSQLNoLength() : void |
||
499 | { |
||
500 | $this->expectException(ColumnLengthRequired::class); |
||
501 | |||
502 | parent::testGetVariableLengthStringTypeDeclarationSQLNoLength(); |
||
503 | } |
||
504 | |||
505 | protected function getExpectedVariableLengthStringTypeDeclarationSQLWithLength() : string |
||
506 | { |
||
507 | return 'VARCHAR2(16)'; |
||
508 | } |
||
509 | |||
510 | public function testGetFixedLengthBinaryTypeDeclarationSQLNoLength() : void |
||
511 | { |
||
512 | $this->expectException(ColumnLengthRequired::class); |
||
513 | |||
514 | parent::testGetFixedLengthBinaryTypeDeclarationSQLNoLength(); |
||
515 | } |
||
516 | |||
517 | public function getExpectedFixedLengthBinaryTypeDeclarationSQLWithLength() : string |
||
518 | { |
||
519 | return 'RAW(16)'; |
||
520 | } |
||
521 | |||
522 | public function testGetVariableLengthBinaryTypeDeclarationSQLNoLength() : void |
||
523 | { |
||
524 | $this->expectException(ColumnLengthRequired::class); |
||
525 | |||
526 | parent::testGetVariableLengthBinaryTypeDeclarationSQLNoLength(); |
||
527 | } |
||
528 | |||
529 | public function getExpectedVariableLengthBinaryTypeDeclarationSQLWithLength() : string |
||
530 | { |
||
531 | return 'RAW(16)'; |
||
532 | } |
||
533 | |||
534 | public function testDoesNotPropagateUnnecessaryTableAlterationOnBinaryType() : void |
||
535 | { |
||
536 | $table1 = new Table('mytable'); |
||
537 | $table1->addColumn('column_varbinary', 'binary'); |
||
538 | $table1->addColumn('column_binary', 'binary', ['fixed' => true]); |
||
539 | |||
540 | $table2 = new Table('mytable'); |
||
541 | $table2->addColumn('column_varbinary', 'binary', ['fixed' => true]); |
||
542 | $table2->addColumn('column_binary', 'binary'); |
||
543 | |||
544 | $comparator = new Comparator(); |
||
545 | |||
546 | $diff = $comparator->diffTable($table1, $table2); |
||
547 | |||
548 | self::assertNotNull($diff); |
||
549 | |||
550 | // VARBINARY -> BINARY |
||
551 | // BINARY -> VARBINARY |
||
552 | self::assertEmpty($this->platform->getAlterTableSQL($diff)); |
||
553 | } |
||
554 | |||
555 | /** |
||
556 | * @group DBAL-563 |
||
557 | */ |
||
558 | public function testUsesSequenceEmulatedIdentityColumns() : void |
||
559 | { |
||
560 | self::assertTrue($this->platform->usesSequenceEmulatedIdentityColumns()); |
||
561 | } |
||
562 | |||
563 | /** |
||
564 | * @group DBAL-563 |
||
565 | * @group DBAL-831 |
||
566 | */ |
||
567 | public function testReturnsIdentitySequenceName() : void |
||
568 | { |
||
569 | self::assertSame('MYTABLE_SEQ', $this->platform->getIdentitySequenceName('mytable', 'mycolumn')); |
||
570 | self::assertSame('"mytable_SEQ"', $this->platform->getIdentitySequenceName('"mytable"', 'mycolumn')); |
||
571 | self::assertSame('MYTABLE_SEQ', $this->platform->getIdentitySequenceName('mytable', '"mycolumn"')); |
||
572 | self::assertSame('"mytable_SEQ"', $this->platform->getIdentitySequenceName('"mytable"', '"mycolumn"')); |
||
573 | } |
||
574 | |||
575 | /** |
||
576 | * @dataProvider dataCreateSequenceWithCache |
||
577 | * @group DBAL-139 |
||
578 | */ |
||
579 | public function testCreateSequenceWithCache(int $cacheSize, string $expectedSql) : void |
||
580 | { |
||
581 | $sequence = new Sequence('foo', 1, 1, $cacheSize); |
||
582 | self::assertStringContainsString($expectedSql, $this->platform->getCreateSequenceSQL($sequence)); |
||
583 | } |
||
584 | |||
585 | /** |
||
586 | * @return mixed[][] |
||
587 | */ |
||
588 | public static function dataCreateSequenceWithCache() : iterable |
||
589 | { |
||
590 | return [ |
||
591 | [1, 'NOCACHE'], |
||
592 | [0, 'NOCACHE'], |
||
593 | [3, 'CACHE 3'], |
||
594 | ]; |
||
595 | } |
||
596 | |||
597 | /** |
||
598 | * {@inheritDoc} |
||
599 | * |
||
600 | * @group DBAL-234 |
||
601 | */ |
||
602 | protected function getAlterTableRenameIndexSQL() : array |
||
603 | { |
||
604 | return ['ALTER INDEX idx_foo RENAME TO idx_bar']; |
||
605 | } |
||
606 | |||
607 | /** |
||
608 | * {@inheritDoc} |
||
609 | * |
||
610 | * @group DBAL-234 |
||
611 | */ |
||
612 | protected function getQuotedAlterTableRenameIndexSQL() : array |
||
613 | { |
||
614 | return [ |
||
615 | 'ALTER INDEX "create" RENAME TO "select"', |
||
616 | 'ALTER INDEX "foo" RENAME TO "bar"', |
||
617 | ]; |
||
618 | } |
||
619 | |||
620 | /** |
||
621 | * {@inheritdoc} |
||
622 | */ |
||
623 | protected function getQuotedAlterTableRenameColumnSQL() : array |
||
624 | { |
||
625 | return [ |
||
626 | 'ALTER TABLE mytable RENAME COLUMN unquoted1 TO unquoted', |
||
627 | 'ALTER TABLE mytable RENAME COLUMN unquoted2 TO "where"', |
||
628 | 'ALTER TABLE mytable RENAME COLUMN unquoted3 TO "foo"', |
||
629 | 'ALTER TABLE mytable RENAME COLUMN "create" TO reserved_keyword', |
||
630 | 'ALTER TABLE mytable RENAME COLUMN "table" TO "from"', |
||
631 | 'ALTER TABLE mytable RENAME COLUMN "select" TO "bar"', |
||
632 | 'ALTER TABLE mytable RENAME COLUMN quoted1 TO quoted', |
||
633 | 'ALTER TABLE mytable RENAME COLUMN quoted2 TO "and"', |
||
634 | 'ALTER TABLE mytable RENAME COLUMN quoted3 TO "baz"', |
||
635 | ]; |
||
636 | } |
||
637 | |||
638 | /** |
||
639 | * {@inheritdoc} |
||
640 | */ |
||
641 | protected function getQuotedAlterTableChangeColumnLengthSQL() : array |
||
642 | { |
||
643 | self::markTestIncomplete('Not implemented yet'); |
||
0 ignored issues
–
show
|
|||
644 | } |
||
645 | |||
646 | /** |
||
647 | * {@inheritDoc} |
||
648 | * |
||
649 | * @group DBAL-807 |
||
650 | */ |
||
651 | protected function getAlterTableRenameIndexInSchemaSQL() : array |
||
652 | { |
||
653 | return ['ALTER INDEX myschema.idx_foo RENAME TO idx_bar']; |
||
654 | } |
||
655 | |||
656 | /** |
||
657 | * {@inheritDoc} |
||
658 | * |
||
659 | * @group DBAL-807 |
||
660 | */ |
||
661 | protected function getQuotedAlterTableRenameIndexInSchemaSQL() : array |
||
662 | { |
||
663 | return [ |
||
664 | 'ALTER INDEX "schema"."create" RENAME TO "select"', |
||
665 | 'ALTER INDEX "schema"."foo" RENAME TO "bar"', |
||
666 | ]; |
||
667 | } |
||
668 | |||
669 | protected function getQuotesDropForeignKeySQL() : string |
||
670 | { |
||
671 | return 'ALTER TABLE "table" DROP CONSTRAINT "select"'; |
||
672 | } |
||
673 | |||
674 | /** |
||
675 | * @group DBAL-423 |
||
676 | */ |
||
677 | public function testReturnsGuidTypeDeclarationSQL() : void |
||
678 | { |
||
679 | self::assertSame('CHAR(36)', $this->platform->getGuidTypeDeclarationSQL([])); |
||
680 | } |
||
681 | |||
682 | /** |
||
683 | * {@inheritdoc} |
||
684 | */ |
||
685 | public function getAlterTableRenameColumnSQL() : array |
||
686 | { |
||
687 | return ['ALTER TABLE foo RENAME COLUMN bar TO baz']; |
||
688 | } |
||
689 | |||
690 | /** |
||
691 | * @param string[] $expectedSql |
||
692 | * |
||
693 | * @dataProvider getReturnsDropAutoincrementSQL |
||
694 | * @group DBAL-831 |
||
695 | */ |
||
696 | public function testReturnsDropAutoincrementSQL(string $table, array $expectedSql) : void |
||
697 | { |
||
698 | assert($this->platform instanceof OraclePlatform); |
||
699 | |||
700 | self::assertSame($expectedSql, $this->platform->getDropAutoincrementSql($table)); |
||
701 | } |
||
702 | |||
703 | /** |
||
704 | * @return mixed[][] |
||
705 | */ |
||
706 | public static function getReturnsDropAutoincrementSQL() : iterable |
||
707 | { |
||
708 | return [ |
||
709 | [ |
||
710 | 'myTable', |
||
711 | [ |
||
712 | 'DROP TRIGGER MYTABLE_AI_PK', |
||
713 | 'DROP SEQUENCE MYTABLE_SEQ', |
||
714 | 'ALTER TABLE MYTABLE DROP CONSTRAINT MYTABLE_AI_PK', |
||
715 | ], |
||
716 | ], |
||
717 | [ |
||
718 | '"myTable"', |
||
719 | [ |
||
720 | 'DROP TRIGGER "myTable_AI_PK"', |
||
721 | 'DROP SEQUENCE "myTable_SEQ"', |
||
722 | 'ALTER TABLE "myTable" DROP CONSTRAINT "myTable_AI_PK"', |
||
723 | ], |
||
724 | ], |
||
725 | [ |
||
726 | 'table', |
||
727 | [ |
||
728 | 'DROP TRIGGER TABLE_AI_PK', |
||
729 | 'DROP SEQUENCE TABLE_SEQ', |
||
730 | 'ALTER TABLE "TABLE" DROP CONSTRAINT TABLE_AI_PK', |
||
731 | ], |
||
732 | ], |
||
733 | ]; |
||
734 | } |
||
735 | |||
736 | /** |
||
737 | * {@inheritdoc} |
||
738 | */ |
||
739 | protected function getQuotesTableIdentifiersInAlterTableSQL() : array |
||
740 | { |
||
741 | return [ |
||
742 | 'ALTER TABLE "foo" DROP CONSTRAINT fk1', |
||
743 | 'ALTER TABLE "foo" DROP CONSTRAINT fk2', |
||
744 | 'ALTER TABLE "foo" ADD (bloo NUMBER(10) NOT NULL)', |
||
745 | 'ALTER TABLE "foo" MODIFY (bar NUMBER(10) DEFAULT NULL NULL)', |
||
746 | 'ALTER TABLE "foo" RENAME COLUMN id TO war', |
||
747 | 'ALTER TABLE "foo" DROP (baz)', |
||
748 | 'ALTER TABLE "foo" RENAME TO "table"', |
||
749 | 'ALTER TABLE "table" ADD CONSTRAINT fk_add FOREIGN KEY (fk3) REFERENCES fk_table (id)', |
||
750 | 'ALTER TABLE "table" ADD CONSTRAINT fk2 FOREIGN KEY (fk2) REFERENCES fk_table2 (id)', |
||
751 | ]; |
||
752 | } |
||
753 | |||
754 | /** |
||
755 | * {@inheritdoc} |
||
756 | */ |
||
757 | protected function getCommentOnColumnSQL() : array |
||
758 | { |
||
759 | return [ |
||
760 | 'COMMENT ON COLUMN foo.bar IS \'comment\'', |
||
761 | 'COMMENT ON COLUMN "Foo"."BAR" IS \'comment\'', |
||
762 | 'COMMENT ON COLUMN "select"."from" IS \'comment\'', |
||
763 | ]; |
||
764 | } |
||
765 | |||
766 | /** |
||
767 | * @group DBAL-1004 |
||
768 | */ |
||
769 | public function testAltersTableColumnCommentWithExplicitlyQuotedIdentifiers() : void |
||
770 | { |
||
771 | $table1 = new Table('"foo"', [new Column('"bar"', Type::getType('integer'))]); |
||
772 | $table2 = new Table('"foo"', [new Column('"bar"', Type::getType('integer'), ['comment' => 'baz'])]); |
||
773 | |||
774 | $comparator = new Comparator(); |
||
775 | |||
776 | $tableDiff = $comparator->diffTable($table1, $table2); |
||
777 | |||
778 | self::assertInstanceOf(TableDiff::class, $tableDiff); |
||
779 | self::assertSame( |
||
780 | ['COMMENT ON COLUMN "foo"."bar" IS \'baz\''], |
||
781 | $this->platform->getAlterTableSQL($tableDiff) |
||
782 | ); |
||
783 | } |
||
784 | |||
785 | public function testQuotedTableNames() : void |
||
786 | { |
||
787 | $table = new Table('"test"'); |
||
788 | $table->addColumn('"id"', 'integer', ['autoincrement' => true]); |
||
789 | |||
790 | // assert tabel |
||
791 | self::assertTrue($table->isQuoted()); |
||
792 | self::assertEquals('test', $table->getName()); |
||
793 | self::assertEquals('"test"', $table->getQuotedName($this->platform)); |
||
794 | |||
795 | $sql = $this->platform->getCreateTableSQL($table); |
||
796 | self::assertEquals('CREATE TABLE "test" ("id" NUMBER(10) NOT NULL)', $sql[0]); |
||
797 | self::assertEquals('CREATE SEQUENCE "test_SEQ" START WITH 1 MINVALUE 1 INCREMENT BY 1', $sql[2]); |
||
798 | $createTriggerStatement = <<<EOD |
||
799 | CREATE TRIGGER "test_AI_PK" |
||
800 | BEFORE INSERT |
||
801 | ON "test" |
||
802 | FOR EACH ROW |
||
803 | DECLARE |
||
804 | last_Sequence NUMBER; |
||
805 | last_InsertID NUMBER; |
||
806 | BEGIN |
||
807 | SELECT "test_SEQ".NEXTVAL INTO :NEW."id" FROM DUAL; |
||
808 | IF (:NEW."id" IS NULL OR :NEW."id" = 0) THEN |
||
809 | SELECT "test_SEQ".NEXTVAL INTO :NEW."id" FROM DUAL; |
||
810 | ELSE |
||
811 | SELECT NVL(Last_Number, 0) INTO last_Sequence |
||
812 | FROM User_Sequences |
||
813 | WHERE Sequence_Name = 'test_SEQ'; |
||
814 | SELECT :NEW."id" INTO last_InsertID FROM DUAL; |
||
815 | WHILE (last_InsertID > last_Sequence) LOOP |
||
816 | SELECT "test_SEQ".NEXTVAL INTO last_Sequence FROM DUAL; |
||
817 | END LOOP; |
||
818 | END IF; |
||
819 | END; |
||
820 | EOD; |
||
821 | |||
822 | self::assertEquals($createTriggerStatement, $sql[3]); |
||
823 | } |
||
824 | |||
825 | /** |
||
826 | * @dataProvider getReturnsGetListTableColumnsSQL |
||
827 | * @group DBAL-831 |
||
828 | */ |
||
829 | public function testReturnsGetListTableColumnsSQL(?string $database, string $expectedSql) : void |
||
830 | { |
||
831 | // note: this assertion is a bit strict, as it compares a full SQL string. |
||
832 | // Should this break in future, then please try to reduce the matching to substring matching while reworking |
||
833 | // the tests |
||
834 | self::assertEquals($expectedSql, $this->platform->getListTableColumnsSQL('"test"', $database)); |
||
835 | } |
||
836 | |||
837 | /** |
||
838 | * @return mixed[][] |
||
839 | */ |
||
840 | public static function getReturnsGetListTableColumnsSQL() : iterable |
||
841 | { |
||
842 | return [ |
||
843 | [ |
||
844 | null, |
||
845 | <<<'SQL' |
||
846 | SELECT c.*, |
||
847 | ( |
||
848 | SELECT d.comments |
||
849 | FROM user_col_comments d |
||
850 | WHERE d.TABLE_NAME = c.TABLE_NAME |
||
851 | AND d.COLUMN_NAME = c.COLUMN_NAME |
||
852 | ) AS comments |
||
853 | FROM user_tab_columns c |
||
854 | WHERE c.table_name = 'test' |
||
855 | ORDER BY c.column_id |
||
856 | SQL |
||
857 | , |
||
858 | ], |
||
859 | [ |
||
860 | '/', |
||
861 | <<<'SQL' |
||
862 | SELECT c.*, |
||
863 | ( |
||
864 | SELECT d.comments |
||
865 | FROM user_col_comments d |
||
866 | WHERE d.TABLE_NAME = c.TABLE_NAME |
||
867 | AND d.COLUMN_NAME = c.COLUMN_NAME |
||
868 | ) AS comments |
||
869 | FROM user_tab_columns c |
||
870 | WHERE c.table_name = 'test' |
||
871 | ORDER BY c.column_id |
||
872 | SQL |
||
873 | , |
||
874 | ], |
||
875 | [ |
||
876 | 'scott', |
||
877 | <<<'SQL' |
||
878 | SELECT c.*, |
||
879 | ( |
||
880 | SELECT d.comments |
||
881 | FROM all_col_comments d |
||
882 | WHERE d.TABLE_NAME = c.TABLE_NAME AND d.OWNER = c.OWNER |
||
883 | AND d.COLUMN_NAME = c.COLUMN_NAME |
||
884 | ) AS comments |
||
885 | FROM all_tab_columns c |
||
886 | WHERE c.table_name = 'test' AND c.owner = 'SCOTT' |
||
887 | ORDER BY c.column_id |
||
888 | SQL |
||
889 | , |
||
890 | ], |
||
891 | ]; |
||
892 | } |
||
893 | |||
894 | protected function getQuotesReservedKeywordInUniqueConstraintDeclarationSQL() : string |
||
895 | { |
||
896 | return 'CONSTRAINT "select" UNIQUE (foo)'; |
||
897 | } |
||
898 | |||
899 | protected function getQuotesReservedKeywordInIndexDeclarationSQL() : string |
||
900 | { |
||
901 | return 'INDEX "select" (foo)'; |
||
902 | } |
||
903 | |||
904 | protected function getQuotesReservedKeywordInTruncateTableSQL() : string |
||
905 | { |
||
906 | return 'TRUNCATE TABLE "select"'; |
||
907 | } |
||
908 | |||
909 | /** |
||
910 | * {@inheritdoc} |
||
911 | */ |
||
912 | protected function getAlterStringToFixedStringSQL() : array |
||
913 | { |
||
914 | return ['ALTER TABLE mytable MODIFY (name CHAR(2) DEFAULT NULL)']; |
||
915 | } |
||
916 | |||
917 | /** |
||
918 | * {@inheritdoc} |
||
919 | */ |
||
920 | protected function getGeneratesAlterTableRenameIndexUsedByForeignKeySQL() : array |
||
921 | { |
||
922 | return ['ALTER INDEX idx_foo RENAME TO idx_foo_renamed']; |
||
923 | } |
||
924 | |||
925 | /** |
||
926 | * @group DBAL-2436 |
||
927 | */ |
||
928 | public function testQuotesDatabaseNameInListSequencesSQL() : void |
||
929 | { |
||
930 | self::assertStringContainsStringIgnoringCase( |
||
931 | "'Foo''Bar\\'", |
||
932 | $this->platform->getListSequencesSQL("Foo'Bar\\") |
||
933 | ); |
||
934 | } |
||
935 | |||
936 | /** |
||
937 | * @group DBAL-2436 |
||
938 | */ |
||
939 | public function testQuotesTableNameInListTableIndexesSQL() : void |
||
940 | { |
||
941 | self::assertStringContainsStringIgnoringCase( |
||
942 | "'Foo''Bar\\'", |
||
943 | $this->platform->getListTableIndexesSQL("Foo'Bar\\") |
||
944 | ); |
||
945 | } |
||
946 | |||
947 | /** |
||
948 | * @group DBAL-2436 |
||
949 | */ |
||
950 | public function testQuotesTableNameInListTableForeignKeysSQL() : void |
||
951 | { |
||
952 | self::assertStringContainsStringIgnoringCase( |
||
953 | "'Foo''Bar\\'", |
||
954 | $this->platform->getListTableForeignKeysSQL("Foo'Bar\\") |
||
955 | ); |
||
956 | } |
||
957 | |||
958 | /** |
||
959 | * @group DBAL-2436 |
||
960 | */ |
||
961 | public function testQuotesTableNameInListTableConstraintsSQL() : void |
||
962 | { |
||
963 | self::assertStringContainsStringIgnoringCase( |
||
964 | "'Foo''Bar\\'", |
||
965 | $this->platform->getListTableConstraintsSQL("Foo'Bar\\") |
||
966 | ); |
||
967 | } |
||
968 | |||
969 | /** |
||
970 | * @group DBAL-2436 |
||
971 | */ |
||
972 | public function testQuotesTableNameInListTableColumnsSQL() : void |
||
973 | { |
||
974 | self::assertStringContainsStringIgnoringCase( |
||
975 | "'Foo''Bar\\'", |
||
976 | $this->platform->getListTableColumnsSQL("Foo'Bar\\") |
||
977 | ); |
||
978 | } |
||
979 | |||
980 | /** |
||
981 | * @group DBAL-2436 |
||
982 | */ |
||
983 | public function testQuotesDatabaseNameInListTableColumnsSQL() : void |
||
984 | { |
||
985 | self::assertStringContainsStringIgnoringCase( |
||
986 | "'Foo''Bar\\'", |
||
987 | $this->platform->getListTableColumnsSQL('foo_table', "Foo'Bar\\") |
||
988 | ); |
||
989 | } |
||
990 | } |
||
991 |
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: