DB2PlatformTest.php 27.3 KB
Newer Older
1 2
<?php

Michael Moravec's avatar
Michael Moravec committed
3 4
declare(strict_types=1);

5
namespace Doctrine\DBAL\Tests\Platforms;
6

7
use Doctrine\DBAL\Exception\ColumnLengthRequired;
8 9
use Doctrine\DBAL\Platforms\AbstractPlatform;
use Doctrine\DBAL\Platforms\DB2Platform;
10 11
use Doctrine\DBAL\Schema\Column;
use Doctrine\DBAL\Schema\ColumnDiff;
12 13
use Doctrine\DBAL\Schema\Index;
use Doctrine\DBAL\Schema\Table;
14 15
use Doctrine\DBAL\Schema\TableDiff;
use Doctrine\DBAL\Types\Type;
16
use Doctrine\DBAL\Types\Types;
17 18 19

class DB2PlatformTest extends AbstractPlatformTestCase
{
Sergei Morozov's avatar
Sergei Morozov committed
20
    /** @var DB2Platform */
Sergei Morozov's avatar
Sergei Morozov committed
21
    protected $platform;
22

23
    public function createPlatform() : AbstractPlatform
24 25 26 27
    {
        return new DB2Platform();
    }

28 29 30 31
    /**
     * {@inheritDoc}
     */
    public function getGenerateAlterTableSql() : array
32
    {
Sergei Morozov's avatar
Sergei Morozov committed
33 34 35
        return [
            'ALTER TABLE mytable ALTER COLUMN baz SET DATA TYPE VARCHAR(255)',
            'ALTER TABLE mytable ALTER COLUMN baz SET NOT NULL',
36
            "ALTER TABLE mytable ALTER COLUMN baz SET DEFAULT 'def'",
Sergei Morozov's avatar
Sergei Morozov committed
37 38
            'ALTER TABLE mytable ALTER COLUMN bloo SET DATA TYPE SMALLINT',
            'ALTER TABLE mytable ALTER COLUMN bloo SET NOT NULL',
39
            "ALTER TABLE mytable ALTER COLUMN bloo SET DEFAULT '0'",
Sergei Morozov's avatar
Sergei Morozov committed
40 41 42
            'ALTER TABLE mytable ' .
            'ADD COLUMN quota INTEGER DEFAULT NULL ' .
            'DROP COLUMN foo',
43 44
            "CALL SYSPROC.ADMIN_CMD ('REORG TABLE mytable')",
            'RENAME TABLE mytable TO userlist',
Sergei Morozov's avatar
Sergei Morozov committed
45
        ];
46 47
    }

48
    public function getGenerateForeignKeySql() : string
49 50 51 52
    {
        return 'ALTER TABLE test ADD FOREIGN KEY (fk_name_id) REFERENCES other_table (id)';
    }

53
    public function getGenerateIndexSql() : string
54 55 56 57
    {
        return 'CREATE INDEX my_idx ON mytable (user_name, last_login)';
    }

58
    public function getGenerateTableSql() : string
59 60 61 62
    {
        return 'CREATE TABLE test (id INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL, test VARCHAR(255) DEFAULT NULL, PRIMARY KEY(id))';
    }

63 64 65 66
    /**
     * {@inheritDoc}
     */
    public function getGenerateTableWithMultiColumnUniqueIndexSql() : array
67
    {
Sergei Morozov's avatar
Sergei Morozov committed
68
        return [
69
            'CREATE TABLE test (foo VARCHAR(255) DEFAULT NULL, bar VARCHAR(255) DEFAULT NULL)',
Sergei Morozov's avatar
Sergei Morozov committed
70 71
            'CREATE UNIQUE INDEX UNIQ_D87F7E0C8C73652176FF8CAA ON test (foo, bar)',
        ];
72 73
    }

74
    public function getGenerateUniqueIndexSql() : string
75 76 77 78
    {
        return 'CREATE UNIQUE INDEX index_name ON test (test, test2)';
    }

79 80 81 82
    /**
     * {@inheritDoc}
     */
    protected function getQuotedColumnInForeignKeySQL() : array
83
    {
Sergei Morozov's avatar
Sergei Morozov committed
84
        return [
85 86 87 88
            'CREATE TABLE "quoted" ("create" VARCHAR(255) NOT NULL, foo VARCHAR(255) NOT NULL, "bar" VARCHAR(255) NOT NULL)',
            'ALTER TABLE "quoted" ADD CONSTRAINT FK_WITH_RESERVED_KEYWORD FOREIGN KEY ("create", foo, "bar") REFERENCES "foreign" ("create", bar, "foo-bar")',
            'ALTER TABLE "quoted" ADD CONSTRAINT FK_WITH_NON_RESERVED_KEYWORD FOREIGN KEY ("create", foo, "bar") REFERENCES foo ("create", bar, "foo-bar")',
            'ALTER TABLE "quoted" ADD CONSTRAINT FK_WITH_INTENDED_QUOTATION FOREIGN KEY ("create", foo, "bar") REFERENCES "foo-bar" ("create", bar, "foo-bar")',
Sergei Morozov's avatar
Sergei Morozov committed
89
        ];
90 91
    }

92 93 94 95
    /**
     * {@inheritDoc}
     */
    protected function getQuotedColumnInIndexSQL() : array
96
    {
Sergei Morozov's avatar
Sergei Morozov committed
97
        return [
98
            'CREATE TABLE "quoted" ("create" VARCHAR(255) NOT NULL)',
Sergei Morozov's avatar
Sergei Morozov committed
99 100
            'CREATE INDEX IDX_22660D028FD6E0FB ON "quoted" ("create")',
        ];
101 102
    }

103 104 105 106
    /**
     * {@inheritDoc}
     */
    protected function getQuotedNameInIndexSQL() : array
Markus Fasselt's avatar
Markus Fasselt committed
107
    {
Sergei Morozov's avatar
Sergei Morozov committed
108
        return [
Markus Fasselt's avatar
Markus Fasselt committed
109 110
            'CREATE TABLE test (column1 VARCHAR(255) NOT NULL)',
            'CREATE INDEX "key" ON test (column1)',
Sergei Morozov's avatar
Sergei Morozov committed
111
        ];
Markus Fasselt's avatar
Markus Fasselt committed
112 113
    }

114 115 116 117
    /**
     * {@inheritDoc}
     */
    protected function getQuotedColumnInPrimaryKeySQL() : array
118
    {
Sergei Morozov's avatar
Sergei Morozov committed
119
        return ['CREATE TABLE "quoted" ("create" VARCHAR(255) NOT NULL, PRIMARY KEY("create"))'];
120 121
    }

122
    protected function getBitAndComparisonExpressionSql(string $value1, string $value2) : string
123 124 125 126
    {
        return 'BITAND(' . $value1 . ', ' . $value2 . ')';
    }

127
    protected function getBitOrComparisonExpressionSql(string $value1, string $value2) : string
128 129 130 131
    {
        return 'BITOR(' . $value1 . ', ' . $value2 . ')';
    }

132 133 134 135
    /**
     * {@inheritDoc}
     */
    public function getCreateTableColumnCommentsSQL() : array
136
    {
Sergei Morozov's avatar
Sergei Morozov committed
137 138
        return [
            'CREATE TABLE test (id INTEGER NOT NULL, PRIMARY KEY(id))',
139
            "COMMENT ON COLUMN test.id IS 'This is a comment'",
Sergei Morozov's avatar
Sergei Morozov committed
140
        ];
141 142
    }

143 144 145 146
    /**
     * {@inheritDoc}
     */
    public function getAlterTableColumnCommentsSQL() : array
147
    {
Sergei Morozov's avatar
Sergei Morozov committed
148 149 150
        return [
            'ALTER TABLE mytable ' .
            'ADD COLUMN quota INTEGER NOT NULL WITH DEFAULT',
151 152 153 154
            "CALL SYSPROC.ADMIN_CMD ('REORG TABLE mytable')",
            "COMMENT ON COLUMN mytable.quota IS 'A comment'",
            "COMMENT ON COLUMN mytable.foo IS ''",
            "COMMENT ON COLUMN mytable.baz IS 'B comment'",
Sergei Morozov's avatar
Sergei Morozov committed
155
        ];
156 157
    }

158 159 160 161
    /**
     * {@inheritDoc}
     */
    public function getCreateTableColumnTypeCommentsSQL() : array
162
    {
Sergei Morozov's avatar
Sergei Morozov committed
163
        return [
164
            'CREATE TABLE test (id INTEGER NOT NULL, "data" CLOB(1M) NOT NULL, PRIMARY KEY(id))',
165
            'COMMENT ON COLUMN test."data" IS \'(DC2Type:array)\'',
Sergei Morozov's avatar
Sergei Morozov committed
166
        ];
167 168
    }

169
    public function testHasCorrectPlatformName() : void
170
    {
Sergei Morozov's avatar
Sergei Morozov committed
171
        self::assertEquals('db2', $this->platform->getName());
172 173
    }

174
    public function testGeneratesCreateTableSQLWithCommonIndexes() : void
175 176 177
    {
        $table = new Table('test');
        $table->addColumn('id', 'integer');
Sergei Morozov's avatar
Sergei Morozov committed
178 179 180 181
        $table->addColumn('name', 'string', ['length' => 50]);
        $table->setPrimaryKey(['id']);
        $table->addIndex(['name']);
        $table->addIndex(['id', 'name'], 'composite_idx');
182

183
        self::assertEquals(
Sergei Morozov's avatar
Sergei Morozov committed
184
            [
185 186
                'CREATE TABLE test (id INTEGER NOT NULL, name VARCHAR(50) NOT NULL, PRIMARY KEY(id))',
                'CREATE INDEX IDX_D87F7E0C5E237E06 ON test (name)',
Sergei Morozov's avatar
Sergei Morozov committed
187 188
                'CREATE INDEX composite_idx ON test (id, name)',
            ],
Sergei Morozov's avatar
Sergei Morozov committed
189
            $this->platform->getCreateTableSQL($table)
190 191 192
        );
    }

193
    public function testGeneratesCreateTableSQLWithForeignKeyConstraints() : void
194 195 196 197 198
    {
        $table = new Table('test');
        $table->addColumn('id', 'integer');
        $table->addColumn('fk_1', 'integer');
        $table->addColumn('fk_2', 'integer');
Sergei Morozov's avatar
Sergei Morozov committed
199 200
        $table->setPrimaryKey(['id']);
        $table->addForeignKeyConstraint('foreign_table', ['fk_1', 'fk_2'], ['pk_1', 'pk_2']);
201 202
        $table->addForeignKeyConstraint(
            'foreign_table2',
Sergei Morozov's avatar
Sergei Morozov committed
203 204 205
            ['fk_1', 'fk_2'],
            ['pk_1', 'pk_2'],
            [],
206 207 208
            'named_fk'
        );

209
        self::assertEquals(
Sergei Morozov's avatar
Sergei Morozov committed
210
            [
211 212 213
                'CREATE TABLE test (id INTEGER NOT NULL, fk_1 INTEGER NOT NULL, fk_2 INTEGER NOT NULL)',
                'ALTER TABLE test ADD CONSTRAINT FK_D87F7E0C177612A38E7F4319 FOREIGN KEY (fk_1, fk_2) REFERENCES foreign_table (pk_1, pk_2)',
                'ALTER TABLE test ADD CONSTRAINT named_fk FOREIGN KEY (fk_1, fk_2) REFERENCES foreign_table2 (pk_1, pk_2)',
Sergei Morozov's avatar
Sergei Morozov committed
214
            ],
Sergei Morozov's avatar
Sergei Morozov committed
215
            $this->platform->getCreateTableSQL($table, AbstractPlatform::CREATE_FOREIGNKEYS)
216 217 218
        );
    }

219
    public function testGeneratesCreateTableSQLWithCheckConstraints() : void
220 221 222
    {
        $table = new Table('test');
        $table->addColumn('id', 'integer');
Sergei Morozov's avatar
Sergei Morozov committed
223 224 225
        $table->addColumn('check_max', 'integer', ['platformOptions' => ['max' => 10]]);
        $table->addColumn('check_min', 'integer', ['platformOptions' => ['min' => 10]]);
        $table->setPrimaryKey(['id']);
226

227
        self::assertEquals(
Sergei Morozov's avatar
Sergei Morozov committed
228
            ['CREATE TABLE test (id INTEGER NOT NULL, check_max INTEGER NOT NULL, check_min INTEGER NOT NULL, PRIMARY KEY(id), CHECK (check_max <= 10), CHECK (check_min >= 10))'],
Sergei Morozov's avatar
Sergei Morozov committed
229
            $this->platform->getCreateTableSQL($table)
230 231 232
        );
    }

233
    public function testGeneratesColumnTypesDeclarationSQL() : void
234
    {
Sergei Morozov's avatar
Sergei Morozov committed
235
        $fullColumnDef = [
236 237 238
            'length' => 10,
            'fixed' => true,
            'unsigned' => true,
Sergei Morozov's avatar
Sergei Morozov committed
239 240
            'autoincrement' => true,
        ];
241

Sergei Morozov's avatar
Sergei Morozov committed
242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257
        self::assertEquals('SMALLINT', $this->platform->getSmallIntTypeDeclarationSQL([]));
        self::assertEquals('SMALLINT', $this->platform->getSmallIntTypeDeclarationSQL(['unsigned' => true]));
        self::assertEquals('SMALLINT GENERATED BY DEFAULT AS IDENTITY', $this->platform->getSmallIntTypeDeclarationSQL($fullColumnDef));
        self::assertEquals('INTEGER', $this->platform->getIntegerTypeDeclarationSQL([]));
        self::assertEquals('INTEGER', $this->platform->getIntegerTypeDeclarationSQL(['unsigned' => true]));
        self::assertEquals('INTEGER GENERATED BY DEFAULT AS IDENTITY', $this->platform->getIntegerTypeDeclarationSQL($fullColumnDef));
        self::assertEquals('BIGINT', $this->platform->getBigIntTypeDeclarationSQL([]));
        self::assertEquals('BIGINT', $this->platform->getBigIntTypeDeclarationSQL(['unsigned' => true]));
        self::assertEquals('BIGINT GENERATED BY DEFAULT AS IDENTITY', $this->platform->getBigIntTypeDeclarationSQL($fullColumnDef));
        self::assertEquals('BLOB(1M)', $this->platform->getBlobTypeDeclarationSQL($fullColumnDef));
        self::assertEquals('SMALLINT', $this->platform->getBooleanTypeDeclarationSQL($fullColumnDef));
        self::assertEquals('CLOB(1M)', $this->platform->getClobTypeDeclarationSQL($fullColumnDef));
        self::assertEquals('DATE', $this->platform->getDateTypeDeclarationSQL($fullColumnDef));
        self::assertEquals('TIMESTAMP(0) WITH DEFAULT', $this->platform->getDateTimeTypeDeclarationSQL(['version' => true]));
        self::assertEquals('TIMESTAMP(0)', $this->platform->getDateTimeTypeDeclarationSQL($fullColumnDef));
        self::assertEquals('TIME', $this->platform->getTimeTypeDeclarationSQL($fullColumnDef));
258 259
    }

260
    public function testInitializesDoctrineTypeMappings() : void
261
    {
Sergei Morozov's avatar
Sergei Morozov committed
262
        $this->platform->initializeDoctrineTypeMappings();
263

Sergei Morozov's avatar
Sergei Morozov committed
264 265
        self::assertTrue($this->platform->hasDoctrineTypeMappingFor('smallint'));
        self::assertSame('smallint', $this->platform->getDoctrineTypeMapping('smallint'));
266

Sergei Morozov's avatar
Sergei Morozov committed
267 268
        self::assertTrue($this->platform->hasDoctrineTypeMappingFor('bigint'));
        self::assertSame('bigint', $this->platform->getDoctrineTypeMapping('bigint'));
269

Sergei Morozov's avatar
Sergei Morozov committed
270 271
        self::assertTrue($this->platform->hasDoctrineTypeMappingFor('integer'));
        self::assertSame('integer', $this->platform->getDoctrineTypeMapping('integer'));
272

Sergei Morozov's avatar
Sergei Morozov committed
273 274
        self::assertTrue($this->platform->hasDoctrineTypeMappingFor('time'));
        self::assertSame('time', $this->platform->getDoctrineTypeMapping('time'));
275

Sergei Morozov's avatar
Sergei Morozov committed
276 277
        self::assertTrue($this->platform->hasDoctrineTypeMappingFor('date'));
        self::assertSame('date', $this->platform->getDoctrineTypeMapping('date'));
278

Sergei Morozov's avatar
Sergei Morozov committed
279 280
        self::assertTrue($this->platform->hasDoctrineTypeMappingFor('varchar'));
        self::assertSame('string', $this->platform->getDoctrineTypeMapping('varchar'));
281

Sergei Morozov's avatar
Sergei Morozov committed
282 283
        self::assertTrue($this->platform->hasDoctrineTypeMappingFor('character'));
        self::assertSame('string', $this->platform->getDoctrineTypeMapping('character'));
284

Sergei Morozov's avatar
Sergei Morozov committed
285 286
        self::assertTrue($this->platform->hasDoctrineTypeMappingFor('clob'));
        self::assertSame('text', $this->platform->getDoctrineTypeMapping('clob'));
287

Sergei Morozov's avatar
Sergei Morozov committed
288 289
        self::assertTrue($this->platform->hasDoctrineTypeMappingFor('blob'));
        self::assertSame('blob', $this->platform->getDoctrineTypeMapping('blob'));
290

Sergei Morozov's avatar
Sergei Morozov committed
291 292
        self::assertTrue($this->platform->hasDoctrineTypeMappingFor('decimal'));
        self::assertSame('decimal', $this->platform->getDoctrineTypeMapping('decimal'));
293

Sergei Morozov's avatar
Sergei Morozov committed
294 295
        self::assertTrue($this->platform->hasDoctrineTypeMappingFor('double'));
        self::assertSame('float', $this->platform->getDoctrineTypeMapping('double'));
296

Sergei Morozov's avatar
Sergei Morozov committed
297 298
        self::assertTrue($this->platform->hasDoctrineTypeMappingFor('real'));
        self::assertSame('float', $this->platform->getDoctrineTypeMapping('real'));
299

Sergei Morozov's avatar
Sergei Morozov committed
300 301
        self::assertTrue($this->platform->hasDoctrineTypeMappingFor('timestamp'));
        self::assertSame('datetime', $this->platform->getDoctrineTypeMapping('timestamp'));
302 303
    }

304 305 306
    /**
     * {@inheritDoc}
     */
307
    public function getIsCommentedDoctrineType() : iterable
308
    {
309
        $types = [];
310

311 312 313
        foreach (parent::getIsCommentedDoctrineType() as $key => $value) {
            $types[$key] = $value;
        }
314

315 316 317
        $types[Types::BOOLEAN] = [Type::getType(Types::BOOLEAN), true];

        return $types;
318 319
    }

320
    public function testGeneratesDDLSnippets() : void
321
    {
Sergei Morozov's avatar
Sergei Morozov committed
322 323 324 325
        self::assertEquals('CREATE DATABASE foobar', $this->platform->getCreateDatabaseSQL('foobar'));
        self::assertEquals('DROP DATABASE foobar', $this->platform->getDropDatabaseSQL('foobar'));
        self::assertEquals('DECLARE GLOBAL TEMPORARY TABLE', $this->platform->getCreateTemporaryTableSnippetSQL());
        self::assertEquals('TRUNCATE foobar IMMEDIATE', $this->platform->getTruncateTableSQL('foobar'));
326 327

        $viewSql = 'SELECT * FROM footable';
Sergei Morozov's avatar
Sergei Morozov committed
328 329
        self::assertEquals('CREATE VIEW fooview AS ' . $viewSql, $this->platform->getCreateViewSQL('fooview', $viewSql));
        self::assertEquals('DROP VIEW fooview', $this->platform->getDropViewSQL('fooview'));
330 331
    }

332
    public function testGeneratesCreateUnnamedPrimaryKeySQL() : void
333
    {
334
        self::assertEquals(
335
            'ALTER TABLE foo ADD PRIMARY KEY (a, b)',
Sergei Morozov's avatar
Sergei Morozov committed
336
            $this->platform->getCreatePrimaryKeySQL(
Sergei Morozov's avatar
Sergei Morozov committed
337
                new Index('any_pk_name', ['a', 'b'], true, true),
338 339 340 341 342
                'foo'
            )
        );
    }

343
    public function testGeneratesSQLSnippets() : void
344
    {
Sergei Morozov's avatar
Sergei Morozov committed
345 346 347
        self::assertEquals('CURRENT DATE', $this->platform->getCurrentDateSQL());
        self::assertEquals('CURRENT TIME', $this->platform->getCurrentTimeSQL());
        self::assertEquals('CURRENT TIMESTAMP', $this->platform->getCurrentTimestampSQL());
Michael Moravec's avatar
Michael Moravec committed
348 349 350 351 352 353 354 355
        self::assertEquals("'1987/05/02' + 4 DAY", $this->platform->getDateAddDaysExpression("'1987/05/02'", '4'));
        self::assertEquals("'1987/05/02' + 12 HOUR", $this->platform->getDateAddHourExpression("'1987/05/02'", '12'));
        self::assertEquals("'1987/05/02' + 2 MINUTE", $this->platform->getDateAddMinutesExpression("'1987/05/02'", '2'));
        self::assertEquals("'1987/05/02' + 102 MONTH", $this->platform->getDateAddMonthExpression("'1987/05/02'", '102'));
        self::assertEquals("'1987/05/02' + (5 * 3) MONTH", $this->platform->getDateAddQuartersExpression("'1987/05/02'", '5'));
        self::assertEquals("'1987/05/02' + 1 SECOND", $this->platform->getDateAddSecondsExpression("'1987/05/02'", '1'));
        self::assertEquals("'1987/05/02' + (3 * 7) DAY", $this->platform->getDateAddWeeksExpression("'1987/05/02'", '3'));
        self::assertEquals("'1987/05/02' + 10 YEAR", $this->platform->getDateAddYearsExpression("'1987/05/02'", '10'));
Sergei Morozov's avatar
Sergei Morozov committed
356
        self::assertEquals("DAYS('1987/05/02') - DAYS('1987/04/01')", $this->platform->getDateDiffExpression("'1987/05/02'", "'1987/04/01'"));
Michael Moravec's avatar
Michael Moravec committed
357 358 359 360 361 362 363 364
        self::assertEquals("'1987/05/02' - 4 DAY", $this->platform->getDateSubDaysExpression("'1987/05/02'", '4'));
        self::assertEquals("'1987/05/02' - 12 HOUR", $this->platform->getDateSubHourExpression("'1987/05/02'", '12'));
        self::assertEquals("'1987/05/02' - 2 MINUTE", $this->platform->getDateSubMinutesExpression("'1987/05/02'", '2'));
        self::assertEquals("'1987/05/02' - 102 MONTH", $this->platform->getDateSubMonthExpression("'1987/05/02'", '102'));
        self::assertEquals("'1987/05/02' - (5 * 3) MONTH", $this->platform->getDateSubQuartersExpression("'1987/05/02'", '5'));
        self::assertEquals("'1987/05/02' - 1 SECOND", $this->platform->getDateSubSecondsExpression("'1987/05/02'", '1'));
        self::assertEquals("'1987/05/02' - (3 * 7) DAY", $this->platform->getDateSubWeeksExpression("'1987/05/02'", '3'));
        self::assertEquals("'1987/05/02' - 10 YEAR", $this->platform->getDateSubYearsExpression("'1987/05/02'", '10'));
Sergei Morozov's avatar
Sergei Morozov committed
365 366
        self::assertEquals(' WITH RR USE AND KEEP UPDATE LOCKS', $this->platform->getForUpdateSQL());
        self::assertEquals('LOCATE(substring_column, string_column)', $this->platform->getLocateExpression('string_column', 'substring_column'));
Michael Moravec's avatar
Michael Moravec committed
367 368 369
        self::assertEquals('LOCATE(substring_column, string_column, 1)', $this->platform->getLocateExpression('string_column', 'substring_column', '1'));
        self::assertEquals('SUBSTR(column, 5)', $this->platform->getSubstringExpression('column', '5'));
        self::assertEquals('SUBSTR(column, 5, 2)', $this->platform->getSubstringExpression('column', '5', '2'));
370 371
    }

372
    public function testModifiesLimitQuery() : void
373
    {
374
        self::assertEquals(
375
            'SELECT * FROM user',
376
            $this->platform->modifyLimitQuery('SELECT * FROM user', null, 0)
377 378
        );

379
        self::assertEquals(
380
            'SELECT db22.* FROM (SELECT db21.*, ROW_NUMBER() OVER() AS DC_ROWNUM FROM (SELECT * FROM user) db21) db22 WHERE db22.DC_ROWNUM <= 10',
Sergei Morozov's avatar
Sergei Morozov committed
381
            $this->platform->modifyLimitQuery('SELECT * FROM user', 10, 0)
382 383
        );

384
        self::assertEquals(
385
            'SELECT db22.* FROM (SELECT db21.*, ROW_NUMBER() OVER() AS DC_ROWNUM FROM (SELECT * FROM user) db21) db22 WHERE db22.DC_ROWNUM <= 10',
Sergei Morozov's avatar
Sergei Morozov committed
386
            $this->platform->modifyLimitQuery('SELECT * FROM user', 10)
387 388
        );

389
        self::assertEquals(
390
            'SELECT db22.* FROM (SELECT db21.*, ROW_NUMBER() OVER() AS DC_ROWNUM FROM (SELECT * FROM user) db21) db22 WHERE db22.DC_ROWNUM >= 6 AND db22.DC_ROWNUM <= 15',
Sergei Morozov's avatar
Sergei Morozov committed
391
            $this->platform->modifyLimitQuery('SELECT * FROM user', 10, 5)
392
        );
393
        self::assertEquals(
394
            'SELECT db22.* FROM (SELECT db21.*, ROW_NUMBER() OVER() AS DC_ROWNUM FROM (SELECT * FROM user) db21) db22 WHERE db22.DC_ROWNUM >= 6 AND db22.DC_ROWNUM <= 5',
Sergei Morozov's avatar
Sergei Morozov committed
395
            $this->platform->modifyLimitQuery('SELECT * FROM user', 0, 5)
396 397 398
        );
    }

399
    public function testPrefersIdentityColumns() : void
400
    {
Sergei Morozov's avatar
Sergei Morozov committed
401
        self::assertTrue($this->platform->prefersIdentityColumns());
402 403
    }

404
    public function testSupportsIdentityColumns() : void
405
    {
Sergei Morozov's avatar
Sergei Morozov committed
406
        self::assertTrue($this->platform->supportsIdentityColumns());
407 408
    }

409
    public function testDoesNotSupportSavePoints() : void
410
    {
Sergei Morozov's avatar
Sergei Morozov committed
411
        self::assertFalse($this->platform->supportsSavepoints());
412 413
    }

414
    public function testDoesNotSupportReleasePoints() : void
415
    {
Sergei Morozov's avatar
Sergei Morozov committed
416
        self::assertFalse($this->platform->supportsReleaseSavepoints());
417 418
    }

419
    public function testDoesNotSupportCreateDropDatabase() : void
420
    {
Sergei Morozov's avatar
Sergei Morozov committed
421
        self::assertFalse($this->platform->supportsCreateDropDatabase());
422 423
    }

424
    public function testReturnsSQLResultCasing() : void
425
    {
Sergei Morozov's avatar
Sergei Morozov committed
426
        self::assertSame('COL', $this->platform->getSQLResultCasing('cOl'));
427
    }
428

429 430 431 432 433 434 435 436
    public function testGetVariableLengthStringTypeDeclarationSQLNoLength() : void
    {
        $this->expectException(ColumnLengthRequired::class);

        parent::testGetVariableLengthStringTypeDeclarationSQLNoLength();
    }

    public function getExpectedFixedLengthBinaryTypeDeclarationSQLNoLength() : string
437
    {
438
        return 'CHAR FOR BIT DATA';
439 440
    }

441
    public function getExpectedFixedLengthBinaryTypeDeclarationSQLWithLength() : string
442
    {
443
        return 'CHAR(16) FOR BIT DATA';
444 445
    }

446
    public function getExpectedVariableLengthBinaryTypeDeclarationSQLNoLength() : string
447
    {
448 449
        return 'CHAR(16) FOR BIT DATA';
    }
450

451 452 453 454 455 456 457 458 459 460
    public function testGetVariableLengthBinaryTypeDeclarationSQLNoLength() : void
    {
        $this->expectException(ColumnLengthRequired::class);

        parent::testGetVariableLengthBinaryTypeDeclarationSQLNoLength();
    }

    public function getExpectedVariableLengthBinaryTypeDeclarationSQLWithLength() : string
    {
        return 'VARCHAR(16) FOR BIT DATA';
461 462
    }

463
    /**
464 465
     * {@inheritDoc}
     *
466 467
     * @group DBAL-234
     */
468
    protected function getAlterTableRenameIndexSQL() : array
469
    {
Sergei Morozov's avatar
Sergei Morozov committed
470
        return ['RENAME INDEX idx_foo TO idx_bar'];
471 472 473
    }

    /**
474 475
     * {@inheritDoc}
     *
476 477
     * @group DBAL-234
     */
478
    protected function getQuotedAlterTableRenameIndexSQL() : array
479
    {
Sergei Morozov's avatar
Sergei Morozov committed
480
        return [
481 482
            'RENAME INDEX "create" TO "select"',
            'RENAME INDEX "foo" TO "bar"',
Sergei Morozov's avatar
Sergei Morozov committed
483
        ];
484
    }
485 486 487 488

    /**
     * {@inheritdoc}
     */
489
    protected function getQuotedAlterTableRenameColumnSQL() : array
490
    {
Sergei Morozov's avatar
Sergei Morozov committed
491
        return ['ALTER TABLE mytable ' .
492 493 494 495 496 497 498 499
            'RENAME COLUMN unquoted1 TO unquoted ' .
            'RENAME COLUMN unquoted2 TO "where" ' .
            'RENAME COLUMN unquoted3 TO "foo" ' .
            'RENAME COLUMN "create" TO reserved_keyword ' .
            'RENAME COLUMN "table" TO "from" ' .
            'RENAME COLUMN "select" TO "bar" ' .
            'RENAME COLUMN quoted1 TO quoted ' .
            'RENAME COLUMN quoted2 TO "and" ' .
Sergei Morozov's avatar
Sergei Morozov committed
500 501
            'RENAME COLUMN quoted3 TO "baz"',
        ];
502
    }
503

504 505 506
    /**
     * {@inheritdoc}
     */
507
    protected function getQuotedAlterTableChangeColumnLengthSQL() : array
508 509 510 511
    {
        $this->markTestIncomplete('Not implemented yet');
    }

512
    /**
513 514
     * {@inheritDoc}
     *
515 516
     * @group DBAL-807
     */
517
    protected function getAlterTableRenameIndexInSchemaSQL() : array
518
    {
Sergei Morozov's avatar
Sergei Morozov committed
519
        return ['RENAME INDEX myschema.idx_foo TO idx_bar'];
520 521 522
    }

    /**
523 524
     * {@inheritDoc}
     *
525 526
     * @group DBAL-807
     */
527
    protected function getQuotedAlterTableRenameIndexInSchemaSQL() : array
528
    {
Sergei Morozov's avatar
Sergei Morozov committed
529
        return [
530 531
            'RENAME INDEX "schema"."create" TO "select"',
            'RENAME INDEX "schema"."foo" TO "bar"',
Sergei Morozov's avatar
Sergei Morozov committed
532
        ];
533
    }
534 535 536 537

    /**
     * @group DBAL-423
     */
538
    public function testReturnsGuidTypeDeclarationSQL() : void
539
    {
Sergei Morozov's avatar
Sergei Morozov committed
540
        self::assertSame('CHAR(36)', $this->platform->getGuidTypeDeclarationSQL([]));
541
    }
542 543 544 545

    /**
     * {@inheritdoc}
     */
546
    public function getAlterTableRenameColumnSQL() : array
547
    {
Sergei Morozov's avatar
Sergei Morozov committed
548
        return ['ALTER TABLE foo RENAME COLUMN bar TO baz'];
549
    }
550 551 552 553

    /**
     * {@inheritdoc}
     */
554
    protected function getQuotesTableIdentifiersInAlterTableSQL() : array
555
    {
Sergei Morozov's avatar
Sergei Morozov committed
556
        return [
557 558
            'ALTER TABLE "foo" DROP FOREIGN KEY fk1',
            'ALTER TABLE "foo" DROP FOREIGN KEY fk2',
559 560 561 562 563
            'ALTER TABLE "foo" ' .
            'ADD COLUMN bloo INTEGER NOT NULL WITH DEFAULT ' .
            'DROP COLUMN baz ' .
            'ALTER COLUMN bar DROP NOT NULL ' .
            'RENAME COLUMN id TO war',
564 565 566 567
            'CALL SYSPROC.ADMIN_CMD (\'REORG TABLE "foo"\')',
            'RENAME TABLE "foo" TO "table"',
            'ALTER TABLE "table" ADD CONSTRAINT fk_add FOREIGN KEY (fk3) REFERENCES fk_table (id)',
            'ALTER TABLE "table" ADD CONSTRAINT fk2 FOREIGN KEY (fk2) REFERENCES fk_table2 (id)',
Sergei Morozov's avatar
Sergei Morozov committed
568
        ];
569
    }
570 571 572 573

    /**
     * {@inheritdoc}
     */
574
    protected function getCommentOnColumnSQL() : array
575
    {
Sergei Morozov's avatar
Sergei Morozov committed
576
        return [
577 578 579
            'COMMENT ON COLUMN foo.bar IS \'comment\'',
            'COMMENT ON COLUMN "Foo"."BAR" IS \'comment\'',
            'COMMENT ON COLUMN "select"."from" IS \'comment\'',
Sergei Morozov's avatar
Sergei Morozov committed
580
        ];
581
    }
582 583 584 585 586

    /**
     * @group DBAL-944
     * @dataProvider getGeneratesAlterColumnSQL
     */
587
    public function testGeneratesAlterColumnSQL(string $changedProperty, Column $column, ?string $expectedSQLClause = null) : void
588
    {
Sergei Morozov's avatar
Sergei Morozov committed
589 590 591
        $tableDiff                        = new TableDiff('foo');
        $tableDiff->fromTable             = new Table('foo');
        $tableDiff->changedColumns['bar'] = new ColumnDiff('bar', $column, [$changedProperty]);
592

Sergei Morozov's avatar
Sergei Morozov committed
593
        $expectedSQL = [];
594

Sergei Morozov's avatar
Sergei Morozov committed
595
        if ($expectedSQLClause !== null) {
596 597 598 599 600
            $expectedSQL[] = 'ALTER TABLE foo ALTER COLUMN bar ' . $expectedSQLClause;
        }

        $expectedSQL[] = "CALL SYSPROC.ADMIN_CMD ('REORG TABLE foo')";

Sergei Morozov's avatar
Sergei Morozov committed
601
        self::assertSame($expectedSQL, $this->platform->getAlterTableSQL($tableDiff));
602 603 604
    }

    /**
605
     * @return mixed[][]
606
     */
607
    public static function getGeneratesAlterColumnSQL() : iterable
608
    {
Sergei Morozov's avatar
Sergei Morozov committed
609 610
        return [
            [
611
                'columnDefinition',
Sergei Morozov's avatar
Sergei Morozov committed
612 613 614 615
                new Column('bar', Type::getType('decimal'), ['columnDefinition' => 'MONEY NOT NULL']),
                'MONEY NOT NULL',
            ],
            [
616 617
                'type',
                new Column('bar', Type::getType('integer')),
Sergei Morozov's avatar
Sergei Morozov committed
618 619 620
                'SET DATA TYPE INTEGER',
            ],
            [
621
                'length',
Sergei Morozov's avatar
Sergei Morozov committed
622 623 624 625
                new Column('bar', Type::getType('string'), ['length' => 100]),
                'SET DATA TYPE VARCHAR(100)',
            ],
            [
626
                'precision',
Sergei Morozov's avatar
Sergei Morozov committed
627 628 629 630
                new Column('bar', Type::getType('decimal'), ['precision' => 10, 'scale' => 2]),
                'SET DATA TYPE NUMERIC(10, 2)',
            ],
            [
631
                'scale',
Sergei Morozov's avatar
Sergei Morozov committed
632 633 634 635
                new Column('bar', Type::getType('decimal'), ['precision' => 5, 'scale' => 4]),
                'SET DATA TYPE NUMERIC(5, 4)',
            ],
            [
636
                'fixed',
Sergei Morozov's avatar
Sergei Morozov committed
637 638 639 640
                new Column('bar', Type::getType('string'), ['length' => 20, 'fixed' => true]),
                'SET DATA TYPE CHAR(20)',
            ],
            [
641
                'notnull',
Sergei Morozov's avatar
Sergei Morozov committed
642 643 644 645
                new Column('bar', Type::getType('string'), ['notnull' => true]),
                'SET NOT NULL',
            ],
            [
646
                'notnull',
Sergei Morozov's avatar
Sergei Morozov committed
647 648 649 650
                new Column('bar', Type::getType('string'), ['notnull' => false]),
                'DROP NOT NULL',
            ],
            [
651
                'default',
Sergei Morozov's avatar
Sergei Morozov committed
652 653 654 655
                new Column('bar', Type::getType('string'), ['default' => 'foo']),
                "SET DEFAULT 'foo'",
            ],
            [
656
                'default',
Sergei Morozov's avatar
Sergei Morozov committed
657 658 659 660
                new Column('bar', Type::getType('integer'), ['autoincrement' => true, 'default' => 666]),
                null,
            ],
            [
661 662
                'default',
                new Column('bar', Type::getType('string')),
Sergei Morozov's avatar
Sergei Morozov committed
663 664 665
                'DROP DEFAULT',
            ],
        ];
666
    }
667

668
    protected function getQuotesReservedKeywordInUniqueConstraintDeclarationSQL() : string
669 670 671 672
    {
        return 'CONSTRAINT "select" UNIQUE (foo)';
    }

673
    protected function getQuotesReservedKeywordInIndexDeclarationSQL() : string
674 675 676 677
    {
        return ''; // not supported by this platform
    }

678
    protected function getQuotesReservedKeywordInTruncateTableSQL() : string
679 680 681 682
    {
        return 'TRUNCATE "select" IMMEDIATE';
    }

683
    protected function supportsInlineIndexDeclaration() : bool
684 685 686
    {
        return false;
    }
687

688
    protected function supportsCommentOnStatement() : bool
689 690 691 692
    {
        return true;
    }

693 694 695
    /**
     * {@inheritdoc}
     */
696
    protected function getAlterStringToFixedStringSQL() : array
697
    {
Sergei Morozov's avatar
Sergei Morozov committed
698
        return [
699 700
            'ALTER TABLE mytable ALTER COLUMN name SET DATA TYPE CHAR(2)',
            'CALL SYSPROC.ADMIN_CMD (\'REORG TABLE mytable\')',
Sergei Morozov's avatar
Sergei Morozov committed
701
        ];
702
    }
703 704 705 706

    /**
     * {@inheritdoc}
     */
707
    protected function getGeneratesAlterTableRenameIndexUsedByForeignKeySQL() : array
708
    {
Sergei Morozov's avatar
Sergei Morozov committed
709
        return ['RENAME INDEX idx_foo TO idx_foo_renamed'];
710
    }
711 712 713 714

    /**
     * @group DBAL-2436
     */
715
    public function testQuotesTableNameInListTableColumnsSQL() : void
716
    {
717 718 719 720
        self::assertStringContainsStringIgnoringCase(
            "'Foo''Bar\\'",
            $this->platform->getListTableColumnsSQL("Foo'Bar\\")
        );
721 722 723 724 725
    }

    /**
     * @group DBAL-2436
     */
726
    public function testQuotesTableNameInListTableIndexesSQL() : void
727
    {
728 729 730 731
        self::assertStringContainsStringIgnoringCase(
            "'Foo''Bar\\'",
            $this->platform->getListTableIndexesSQL("Foo'Bar\\")
        );
732 733 734 735 736
    }

    /**
     * @group DBAL-2436
     */
737
    public function testQuotesTableNameInListTableForeignKeysSQL() : void
738
    {
739 740 741 742
        self::assertStringContainsStringIgnoringCase(
            "'Foo''Bar\\'",
            $this->platform->getListTableForeignKeysSQL("Foo'Bar\\")
        );
743
    }
744
}