Completed
Push — master ( f28f3a...073160 )
by Sergei
10:31 queued 10:26
created

MySqlSchemaManagerTest   A

Complexity

Total Complexity 28

Size/Duplication

Total Lines 590
Duplicated Lines 0 %

Importance

Changes 0
Metric Value
wmc 28
eloc 331
dl 0
loc 590
rs 10
c 0
b 0
f 0

25 Methods

Rating   Name   Duplication   Size   Complexity  
A setUp() 0 11 2
A testFulltextIndex() 0 15 1
A testSwitchPrimaryKeyColumns() 0 20 1
A testSpatialIndex() 0 15 1
A testDiffTableBug() 0 21 1
A testAlterTableAddPrimaryKey() 0 21 1
A testDoesNotPropagateDefaultValuesForUnsupportedColumnTypes() 0 37 2
A testDropPrimaryKeyWithAutoincrementColumn() 0 21 1
A testIndexWithLength() 0 11 1
A testColumnCharset() 0 15 1
A testJsonColumnType() 0 9 1
A testEnsureDefaultsAreUnescapedFromSchemaIntrospection() 0 30 1
A testAlterColumnCharset() 0 19 1
A testListDecimalTypeColumns() 0 16 1
A testEnsureTableWithoutOptionsAreReflectedInMetadata() 0 12 1
A testColumnCharsetChange() 0 13 1
A testColumnCollation() 0 17 1
A testListLobTypeColumns() 0 53 1
A testEnsureTableOptionsAreReflectedInMetadata() 0 27 1
A testListFloatTypeColumns() 0 16 1
A testParseNullCreateOptions() 0 5 1
A testDiffListGuidTableColumn() 0 14 1
A testColumnDefaultValuesCurrentTimeAndDate() 0 30 2
A testColumnDefaultCurrentTimestamp() 0 21 1
A testColumnDefaultsAreValid() 0 31 1
1
<?php
2
3
namespace Doctrine\Tests\DBAL\Functional\Schema;
4
5
use DateTime;
6
use Doctrine\DBAL\Platforms\MariaDb1027Platform;
7
use Doctrine\DBAL\Platforms\MySqlPlatform;
8
use Doctrine\DBAL\Schema\Comparator;
9
use Doctrine\DBAL\Schema\Schema;
10
use Doctrine\DBAL\Schema\Table;
11
use Doctrine\DBAL\Types\Type;
12
use Doctrine\Tests\Types\MySqlPointType;
13
use function implode;
14
use function sprintf;
15
16
class MySqlSchemaManagerTest extends SchemaManagerFunctionalTestCase
17
{
18
    protected function setUp()
19
    {
20
        parent::setUp();
21
22
        if (Type::hasType('point')) {
23
            return;
24
        }
25
26
        $this->resetSharedConn();
27
28
        Type::addType('point', MySqlPointType::class);
29
    }
30
31
    public function testSwitchPrimaryKeyColumns()
32
    {
33
        $tableOld = new Table('switch_primary_key_columns');
34
        $tableOld->addColumn('foo_id', 'integer');
35
        $tableOld->addColumn('bar_id', 'integer');
36
37
        $this->schemaManager->createTable($tableOld);
38
        $tableFetched = $this->schemaManager->listTableDetails('switch_primary_key_columns');
39
        $tableNew     = clone $tableFetched;
40
        $tableNew->setPrimaryKey(['bar_id', 'foo_id']);
41
42
        $comparator = new Comparator();
43
        $this->schemaManager->alterTable($comparator->diffTable($tableFetched, $tableNew));
44
45
        $table      = $this->schemaManager->listTableDetails('switch_primary_key_columns');
46
        $primaryKey = $table->getPrimaryKeyColumns();
47
48
        self::assertCount(2, $primaryKey);
49
        self::assertContains('bar_id', $primaryKey);
50
        self::assertContains('foo_id', $primaryKey);
51
    }
52
53
    public function testDiffTableBug()
54
    {
55
        $schema = new Schema();
56
        $table  = $schema->createTable('diffbug_routing_translations');
57
        $table->addColumn('id', 'integer');
58
        $table->addColumn('route', 'string');
59
        $table->addColumn('locale', 'string');
60
        $table->addColumn('attribute', 'string');
61
        $table->addColumn('localized_value', 'string');
62
        $table->addColumn('original_value', 'string');
63
        $table->setPrimaryKey(['id']);
64
        $table->addUniqueIndex(['route', 'locale', 'attribute']);
65
        $table->addIndex(['localized_value']); // this is much more selective than the unique index
66
67
        $this->schemaManager->createTable($table);
68
        $tableFetched = $this->schemaManager->listTableDetails('diffbug_routing_translations');
69
70
        $comparator = new Comparator();
71
        $diff       = $comparator->diffTable($tableFetched, $table);
72
73
        self::assertFalse($diff, 'no changes expected.');
74
    }
75
76
    public function testFulltextIndex()
77
    {
78
        $table = new Table('fulltext_index');
79
        $table->addColumn('text', 'text');
80
        $table->addIndex(['text'], 'f_index');
81
        $table->addOption('engine', 'MyISAM');
82
83
        $index = $table->getIndex('f_index');
84
        $index->addFlag('fulltext');
85
86
        $this->schemaManager->dropAndCreateTable($table);
87
88
        $indexes = $this->schemaManager->listTableIndexes('fulltext_index');
89
        self::assertArrayHasKey('f_index', $indexes);
90
        self::assertTrue($indexes['f_index']->hasFlag('fulltext'));
91
    }
92
93
    public function testSpatialIndex()
94
    {
95
        $table = new Table('spatial_index');
96
        $table->addColumn('point', 'point');
97
        $table->addIndex(['point'], 's_index');
98
        $table->addOption('engine', 'MyISAM');
99
100
        $index = $table->getIndex('s_index');
101
        $index->addFlag('spatial');
102
103
        $this->schemaManager->dropAndCreateTable($table);
104
105
        $indexes = $this->schemaManager->listTableIndexes('spatial_index');
106
        self::assertArrayHasKey('s_index', $indexes);
107
        self::assertTrue($indexes['s_index']->hasFlag('spatial'));
108
    }
109
110
    public function testIndexWithLength() : void
111
    {
112
        $table = new Table('index_length');
113
        $table->addColumn('text', 'string', ['length' => 255]);
114
        $table->addIndex(['text'], 'text_index', [], ['lengths' => [128]]);
115
116
        $this->schemaManager->dropAndCreateTable($table);
117
118
        $indexes = $this->schemaManager->listTableIndexes('index_length');
119
        self::assertArrayHasKey('text_index', $indexes);
120
        self::assertSame([128], $indexes['text_index']->getOption('lengths'));
121
    }
122
123
    /**
124
     * @group DBAL-400
125
     */
126
    public function testAlterTableAddPrimaryKey()
127
    {
128
        $table = new Table('alter_table_add_pk');
129
        $table->addColumn('id', 'integer');
130
        $table->addColumn('foo', 'integer');
131
        $table->addIndex(['id'], 'idx_id');
132
133
        $this->schemaManager->createTable($table);
134
135
        $comparator = new Comparator();
136
        $diffTable  = clone $table;
137
138
        $diffTable->dropIndex('idx_id');
139
        $diffTable->setPrimaryKey(['id']);
140
141
        $this->schemaManager->alterTable($comparator->diffTable($table, $diffTable));
0 ignored issues
show
Bug introduced by
It seems like $comparator->diffTable($table, $diffTable) can also be of type false; however, parameter $tableDiff of Doctrine\DBAL\Schema\Abs...maManager::alterTable() does only seem to accept Doctrine\DBAL\Schema\TableDiff, maybe add an additional type check? ( Ignorable by Annotation )

If this is a false-positive, you can also ignore this issue in your code via the ignore-type  annotation

141
        $this->schemaManager->alterTable(/** @scrutinizer ignore-type */ $comparator->diffTable($table, $diffTable));
Loading history...
142
143
        $table = $this->schemaManager->listTableDetails('alter_table_add_pk');
144
145
        self::assertFalse($table->hasIndex('idx_id'));
146
        self::assertTrue($table->hasPrimaryKey());
147
    }
148
149
    /**
150
     * @group DBAL-464
151
     */
152
    public function testDropPrimaryKeyWithAutoincrementColumn()
153
    {
154
        $table = new Table('drop_primary_key');
155
        $table->addColumn('id', 'integer', ['autoincrement' => true]);
156
        $table->addColumn('foo', 'integer');
157
        $table->setPrimaryKey(['id', 'foo']);
158
159
        $this->schemaManager->dropAndCreateTable($table);
160
161
        $diffTable = clone $table;
162
163
        $diffTable->dropPrimaryKey();
164
165
        $comparator = new Comparator();
166
167
        $this->schemaManager->alterTable($comparator->diffTable($table, $diffTable));
0 ignored issues
show
Bug introduced by
It seems like $comparator->diffTable($table, $diffTable) can also be of type false; however, parameter $tableDiff of Doctrine\DBAL\Schema\Abs...maManager::alterTable() does only seem to accept Doctrine\DBAL\Schema\TableDiff, maybe add an additional type check? ( Ignorable by Annotation )

If this is a false-positive, you can also ignore this issue in your code via the ignore-type  annotation

167
        $this->schemaManager->alterTable(/** @scrutinizer ignore-type */ $comparator->diffTable($table, $diffTable));
Loading history...
168
169
        $table = $this->schemaManager->listTableDetails('drop_primary_key');
170
171
        self::assertFalse($table->hasPrimaryKey());
172
        self::assertFalse($table->getColumn('id')->getAutoincrement());
173
    }
174
175
    /**
176
     * @group DBAL-789
177
     */
178
    public function testDoesNotPropagateDefaultValuesForUnsupportedColumnTypes()
179
    {
180
        if ($this->schemaManager->getDatabasePlatform() instanceof MariaDb1027Platform) {
181
            $this->markTestSkipped(
182
                'MariaDb102Platform supports default values for BLOB and TEXT columns and will propagate values'
183
            );
184
        }
185
186
        $table = new Table('text_blob_default_value');
187
        $table->addColumn('def_text', 'text', ['default' => 'def']);
188
        $table->addColumn('def_text_null', 'text', ['notnull' => false, 'default' => 'def']);
189
        $table->addColumn('def_blob', 'blob', ['default' => 'def']);
190
        $table->addColumn('def_blob_null', 'blob', ['notnull' => false, 'default' => 'def']);
191
192
        $this->schemaManager->dropAndCreateTable($table);
193
194
        $onlineTable = $this->schemaManager->listTableDetails('text_blob_default_value');
195
196
        self::assertNull($onlineTable->getColumn('def_text')->getDefault());
197
        self::assertNull($onlineTable->getColumn('def_text_null')->getDefault());
198
        self::assertFalse($onlineTable->getColumn('def_text_null')->getNotnull());
199
        self::assertNull($onlineTable->getColumn('def_blob')->getDefault());
200
        self::assertNull($onlineTable->getColumn('def_blob_null')->getDefault());
201
        self::assertFalse($onlineTable->getColumn('def_blob_null')->getNotnull());
202
203
        $comparator = new Comparator();
204
205
        $this->schemaManager->alterTable($comparator->diffTable($table, $onlineTable));
206
207
        $onlineTable = $this->schemaManager->listTableDetails('text_blob_default_value');
208
209
        self::assertNull($onlineTable->getColumn('def_text')->getDefault());
210
        self::assertNull($onlineTable->getColumn('def_text_null')->getDefault());
211
        self::assertFalse($onlineTable->getColumn('def_text_null')->getNotnull());
212
        self::assertNull($onlineTable->getColumn('def_blob')->getDefault());
213
        self::assertNull($onlineTable->getColumn('def_blob_null')->getDefault());
214
        self::assertFalse($onlineTable->getColumn('def_blob_null')->getNotnull());
215
    }
216
217
    public function testColumnCharset()
218
    {
219
        $table = new Table('test_column_charset');
220
        $table->addColumn('id', 'integer');
221
        $table->addColumn('no_charset', 'text');
222
        $table->addColumn('foo', 'text')->setPlatformOption('charset', 'ascii');
223
        $table->addColumn('bar', 'text')->setPlatformOption('charset', 'latin1');
224
        $this->schemaManager->dropAndCreateTable($table);
225
226
        $columns = $this->schemaManager->listTableColumns('test_column_charset');
227
228
        self::assertFalse($columns['id']->hasPlatformOption('charset'));
229
        self::assertEquals('utf8', $columns['no_charset']->getPlatformOption('charset'));
230
        self::assertEquals('ascii', $columns['foo']->getPlatformOption('charset'));
231
        self::assertEquals('latin1', $columns['bar']->getPlatformOption('charset'));
232
    }
233
234
    public function testAlterColumnCharset()
235
    {
236
        $tableName = 'test_alter_column_charset';
237
238
        $table = new Table($tableName);
239
        $table->addColumn('col_text', 'text')->setPlatformOption('charset', 'utf8');
240
241
        $this->schemaManager->dropAndCreateTable($table);
242
243
        $diffTable = clone $table;
244
        $diffTable->getColumn('col_text')->setPlatformOption('charset', 'ascii');
245
246
        $comparator = new Comparator();
247
248
        $this->schemaManager->alterTable($comparator->diffTable($table, $diffTable));
0 ignored issues
show
Bug introduced by
It seems like $comparator->diffTable($table, $diffTable) can also be of type false; however, parameter $tableDiff of Doctrine\DBAL\Schema\Abs...maManager::alterTable() does only seem to accept Doctrine\DBAL\Schema\TableDiff, maybe add an additional type check? ( Ignorable by Annotation )

If this is a false-positive, you can also ignore this issue in your code via the ignore-type  annotation

248
        $this->schemaManager->alterTable(/** @scrutinizer ignore-type */ $comparator->diffTable($table, $diffTable));
Loading history...
249
250
        $table = $this->schemaManager->listTableDetails($tableName);
251
252
        self::assertEquals('ascii', $table->getColumn('col_text')->getPlatformOption('charset'));
253
    }
254
255
    public function testColumnCharsetChange()
256
    {
257
        $table = new Table('test_column_charset_change');
258
        $table->addColumn('col_string', 'string')->setLength(100)->setNotnull(true)->setPlatformOption('charset', 'utf8');
259
260
        $diffTable = clone $table;
261
        $diffTable->getColumn('col_string')->setPlatformOption('charset', 'ascii');
262
263
        $fromSchema = new Schema([$table]);
264
        $toSchema   = new Schema([$diffTable]);
265
266
        $diff = $fromSchema->getMigrateToSql($toSchema, $this->connection->getDatabasePlatform());
267
        self::assertContains('ALTER TABLE test_column_charset_change CHANGE col_string col_string VARCHAR(100) CHARACTER SET ascii NOT NULL', $diff);
268
    }
269
270
    public function testColumnCollation()
271
    {
272
        $table                                  = new Table('test_collation');
273
        $table->addOption('collate', $collation = 'latin1_swedish_ci');
274
        $table->addOption('charset', 'latin1');
275
        $table->addColumn('id', 'integer');
276
        $table->addColumn('text', 'text');
277
        $table->addColumn('foo', 'text')->setPlatformOption('collation', 'latin1_swedish_ci');
278
        $table->addColumn('bar', 'text')->setPlatformOption('collation', 'utf8_general_ci');
279
        $this->schemaManager->dropAndCreateTable($table);
280
281
        $columns = $this->schemaManager->listTableColumns('test_collation');
282
283
        self::assertArrayNotHasKey('collation', $columns['id']->getPlatformOptions());
284
        self::assertEquals('latin1_swedish_ci', $columns['text']->getPlatformOption('collation'));
285
        self::assertEquals('latin1_swedish_ci', $columns['foo']->getPlatformOption('collation'));
286
        self::assertEquals('utf8_general_ci', $columns['bar']->getPlatformOption('collation'));
287
    }
288
289
    /**
290
     * @group DBAL-843
291
     */
292
    public function testListLobTypeColumns()
293
    {
294
        $tableName = 'lob_type_columns';
295
        $table     = new Table($tableName);
296
297
        $table->addColumn('col_tinytext', 'text', ['length' => MySqlPlatform::LENGTH_LIMIT_TINYTEXT]);
298
        $table->addColumn('col_text', 'text', ['length' => MySqlPlatform::LENGTH_LIMIT_TEXT]);
299
        $table->addColumn('col_mediumtext', 'text', ['length' => MySqlPlatform::LENGTH_LIMIT_MEDIUMTEXT]);
300
        $table->addColumn('col_longtext', 'text');
301
302
        $table->addColumn('col_tinyblob', 'text', ['length' => MySqlPlatform::LENGTH_LIMIT_TINYBLOB]);
303
        $table->addColumn('col_blob', 'blob', ['length' => MySqlPlatform::LENGTH_LIMIT_BLOB]);
304
        $table->addColumn('col_mediumblob', 'blob', ['length' => MySqlPlatform::LENGTH_LIMIT_MEDIUMBLOB]);
305
        $table->addColumn('col_longblob', 'blob');
306
307
        $this->schemaManager->dropAndCreateTable($table);
308
309
        $platform       = $this->schemaManager->getDatabasePlatform();
310
        $offlineColumns = $table->getColumns();
311
        $onlineColumns  = $this->schemaManager->listTableColumns($tableName);
312
313
        self::assertSame(
314
            $platform->getClobTypeDeclarationSQL($offlineColumns['col_tinytext']->toArray()),
315
            $platform->getClobTypeDeclarationSQL($onlineColumns['col_tinytext']->toArray())
316
        );
317
        self::assertSame(
318
            $platform->getClobTypeDeclarationSQL($offlineColumns['col_text']->toArray()),
319
            $platform->getClobTypeDeclarationSQL($onlineColumns['col_text']->toArray())
320
        );
321
        self::assertSame(
322
            $platform->getClobTypeDeclarationSQL($offlineColumns['col_mediumtext']->toArray()),
323
            $platform->getClobTypeDeclarationSQL($onlineColumns['col_mediumtext']->toArray())
324
        );
325
        self::assertSame(
326
            $platform->getClobTypeDeclarationSQL($offlineColumns['col_longtext']->toArray()),
327
            $platform->getClobTypeDeclarationSQL($onlineColumns['col_longtext']->toArray())
328
        );
329
330
        self::assertSame(
331
            $platform->getBlobTypeDeclarationSQL($offlineColumns['col_tinyblob']->toArray()),
332
            $platform->getBlobTypeDeclarationSQL($onlineColumns['col_tinyblob']->toArray())
333
        );
334
        self::assertSame(
335
            $platform->getBlobTypeDeclarationSQL($offlineColumns['col_blob']->toArray()),
336
            $platform->getBlobTypeDeclarationSQL($onlineColumns['col_blob']->toArray())
337
        );
338
        self::assertSame(
339
            $platform->getBlobTypeDeclarationSQL($offlineColumns['col_mediumblob']->toArray()),
340
            $platform->getBlobTypeDeclarationSQL($onlineColumns['col_mediumblob']->toArray())
341
        );
342
        self::assertSame(
343
            $platform->getBlobTypeDeclarationSQL($offlineColumns['col_longblob']->toArray()),
344
            $platform->getBlobTypeDeclarationSQL($onlineColumns['col_longblob']->toArray())
345
        );
346
    }
347
348
    /**
349
     * @group DBAL-423
350
     */
351
    public function testDiffListGuidTableColumn()
352
    {
353
        $offlineTable = new Table('list_guid_table_column');
354
        $offlineTable->addColumn('col_guid', 'guid');
355
356
        $this->schemaManager->dropAndCreateTable($offlineTable);
357
358
        $onlineTable = $this->schemaManager->listTableDetails('list_guid_table_column');
359
360
        $comparator = new Comparator();
361
362
        self::assertFalse(
363
            $comparator->diffTable($offlineTable, $onlineTable),
364
            'No differences should be detected with the offline vs online schema.'
365
        );
366
    }
367
368
    /**
369
     * @group DBAL-1082
370
     */
371
    public function testListDecimalTypeColumns()
372
    {
373
        $tableName = 'test_list_decimal_columns';
374
        $table     = new Table($tableName);
375
376
        $table->addColumn('col', 'decimal');
377
        $table->addColumn('col_unsigned', 'decimal', ['unsigned' => true]);
378
379
        $this->schemaManager->dropAndCreateTable($table);
380
381
        $columns = $this->schemaManager->listTableColumns($tableName);
382
383
        self::assertArrayHasKey('col', $columns);
384
        self::assertArrayHasKey('col_unsigned', $columns);
385
        self::assertFalse($columns['col']->getUnsigned());
386
        self::assertTrue($columns['col_unsigned']->getUnsigned());
387
    }
388
389
    /**
390
     * @group DBAL-1082
391
     */
392
    public function testListFloatTypeColumns()
393
    {
394
        $tableName = 'test_list_float_columns';
395
        $table     = new Table($tableName);
396
397
        $table->addColumn('col', 'float');
398
        $table->addColumn('col_unsigned', 'float', ['unsigned' => true]);
399
400
        $this->schemaManager->dropAndCreateTable($table);
401
402
        $columns = $this->schemaManager->listTableColumns($tableName);
403
404
        self::assertArrayHasKey('col', $columns);
405
        self::assertArrayHasKey('col_unsigned', $columns);
406
        self::assertFalse($columns['col']->getUnsigned());
407
        self::assertTrue($columns['col_unsigned']->getUnsigned());
408
    }
409
410
    public function testJsonColumnType() : void
411
    {
412
        $table = new Table('test_mysql_json');
413
        $table->addColumn('col_json', 'json');
414
        $this->schemaManager->dropAndCreateTable($table);
415
416
        $columns = $this->schemaManager->listTableColumns('test_mysql_json');
417
418
        self::assertSame(Type::JSON, $columns['col_json']->getType()->getName());
419
    }
420
421
    public function testColumnDefaultCurrentTimestamp() : void
422
    {
423
        $platform = $this->schemaManager->getDatabasePlatform();
424
425
        $table = new Table('test_column_defaults_current_timestamp');
426
427
        $currentTimeStampSql = $platform->getCurrentTimestampSQL();
428
429
        $table->addColumn('col_datetime', 'datetime', ['notnull' => true, 'default' => $currentTimeStampSql]);
430
        $table->addColumn('col_datetime_nullable', 'datetime', ['default' => $currentTimeStampSql]);
431
432
        $this->schemaManager->dropAndCreateTable($table);
433
434
        $onlineTable = $this->schemaManager->listTableDetails('test_column_defaults_current_timestamp');
435
        self::assertSame($currentTimeStampSql, $onlineTable->getColumn('col_datetime')->getDefault());
436
        self::assertSame($currentTimeStampSql, $onlineTable->getColumn('col_datetime_nullable')->getDefault());
437
438
        $comparator = new Comparator();
439
440
        $diff = $comparator->diffTable($table, $onlineTable);
441
        self::assertFalse($diff, 'Tables should be identical with column defaults.');
442
    }
443
444
    public function testColumnDefaultsAreValid()
445
    {
446
        $table = new Table('test_column_defaults_are_valid');
447
448
        $currentTimeStampSql = $this->schemaManager->getDatabasePlatform()->getCurrentTimestampSQL();
449
        $table->addColumn('col_datetime', 'datetime', ['default' => $currentTimeStampSql]);
450
        $table->addColumn('col_datetime_null', 'datetime', ['notnull' => false, 'default' => null]);
451
        $table->addColumn('col_int', 'integer', ['default' => 1]);
452
        $table->addColumn('col_neg_int', 'integer', ['default' => -1]);
453
        $table->addColumn('col_string', 'string', ['default' => 'A']);
454
        $table->addColumn('col_decimal', 'decimal', ['scale' => 3, 'precision' => 6, 'default' => -2.3]);
455
        $table->addColumn('col_date', 'date', ['default' => '2012-12-12']);
456
457
        $this->schemaManager->dropAndCreateTable($table);
458
459
        $this->connection->executeUpdate(
460
            'INSERT INTO test_column_defaults_are_valid () VALUES()'
461
        );
462
463
        $row = $this->connection->fetchAssoc(
464
            'SELECT *, DATEDIFF(CURRENT_TIMESTAMP(), col_datetime) as diff_seconds FROM test_column_defaults_are_valid'
465
        );
466
467
        self::assertInstanceOf(DateTime::class, DateTime::createFromFormat('Y-m-d H:i:s', $row['col_datetime']));
468
        self::assertNull($row['col_datetime_null']);
469
        self::assertSame('2012-12-12', $row['col_date']);
470
        self::assertSame('A', $row['col_string']);
471
        self::assertEquals(1, $row['col_int']);
472
        self::assertEquals(-1, $row['col_neg_int']);
473
        self::assertEquals('-2.300', $row['col_decimal']);
474
        self::assertLessThan(5, $row['diff_seconds']);
475
    }
476
477
    /**
478
     * MariaDB 10.2+ does support CURRENT_TIME and CURRENT_DATE as
479
     * column default values for time and date columns.
480
     * (Not supported on Mysql as of 5.7.19)
481
     *
482
     * Note that MariaDB 10.2+, when storing default in information_schema,
483
     * silently change CURRENT_TIMESTAMP as 'current_timestamp()',
484
     * CURRENT_TIME as 'currtime()' and CURRENT_DATE as 'currdate()'.
485
     * This test also ensure proper aliasing to not trigger a table diff.
486
     */
487
    public function testColumnDefaultValuesCurrentTimeAndDate() : void
488
    {
489
        if (! $this->schemaManager->getDatabasePlatform() instanceof MariaDb1027Platform) {
490
            $this->markTestSkipped('Only relevant for MariaDb102Platform.');
491
        }
492
493
        $platform = $this->schemaManager->getDatabasePlatform();
494
495
        $table = new Table('test_column_defaults_current_time_and_date');
496
497
        $currentTimestampSql = $platform->getCurrentTimestampSQL();
498
        $currentTimeSql      = $platform->getCurrentTimeSQL();
499
        $currentDateSql      = $platform->getCurrentDateSQL();
500
501
        $table->addColumn('col_datetime', 'datetime', ['default' => $currentTimestampSql]);
502
        $table->addColumn('col_date', 'date', ['default' => $currentDateSql]);
503
        $table->addColumn('col_time', 'time', ['default' => $currentTimeSql]);
504
505
        $this->schemaManager->dropAndCreateTable($table);
506
507
        $onlineTable = $this->schemaManager->listTableDetails('test_column_defaults_current_time_and_date');
508
509
        self::assertSame($currentTimestampSql, $onlineTable->getColumn('col_datetime')->getDefault());
510
        self::assertSame($currentDateSql, $onlineTable->getColumn('col_date')->getDefault());
511
        self::assertSame($currentTimeSql, $onlineTable->getColumn('col_time')->getDefault());
512
513
        $comparator = new Comparator();
514
515
        $diff = $comparator->diffTable($table, $onlineTable);
516
        self::assertFalse($diff, 'Tables should be identical with column defauts time and date.');
517
    }
518
519
    /**
520
     * Ensure default values (un-)escaping is properly done by mysql platforms.
521
     * The test is voluntarily relying on schema introspection due to current
522
     * doctrine limitations. Once #2850 is landed, this test can be removed.
523
     *
524
     * @see https://dev.mysql.com/doc/refman/5.7/en/string-literals.html
525
     */
526
    public function testEnsureDefaultsAreUnescapedFromSchemaIntrospection() : void
527
    {
528
        $platform = $this->schemaManager->getDatabasePlatform();
529
        $this->connection->query('DROP TABLE IF EXISTS test_column_defaults_with_create');
530
531
        $escapeSequences = [
532
            "\\0",          // An ASCII NUL (X'00') character
533
            "\\'",
534
            "''",    // Single quote
535
            '\\"',
536
            '""',    // Double quote
537
            '\\b',          // A backspace character
538
            '\\n',          // A new-line character
539
            '\\r',          // A carriage return character
540
            '\\t',          // A tab character
541
            '\\Z',          // ASCII 26 (Control+Z)
542
            '\\\\',         // A backslash (\) character
543
            '\\%',          // A percent (%) character
544
            '\\_',          // An underscore (_) character
545
        ];
546
547
        $default = implode('+', $escapeSequences);
548
549
        $sql = sprintf(
550
            'CREATE TABLE test_column_defaults_with_create(col1 VARCHAR(255) NULL DEFAULT %s)',
551
            $platform->quoteStringLiteral($default)
552
        );
553
        $this->connection->query($sql);
554
        $onlineTable = $this->schemaManager->listTableDetails('test_column_defaults_with_create');
555
        self::assertSame($default, $onlineTable->getColumn('col1')->getDefault());
556
    }
557
558
    public function testEnsureTableOptionsAreReflectedInMetadata() : void
559
    {
560
        $this->connection->query('DROP TABLE IF EXISTS test_table_metadata');
561
562
        $sql = <<<'SQL'
563
CREATE TABLE test_table_metadata(
564
  col1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY
565
)
566
COLLATE utf8_general_ci
567
ENGINE InnoDB
568
ROW_FORMAT COMPRESSED
569
COMMENT 'This is a test'
570
AUTO_INCREMENT=42
571
PARTITION BY HASH (col1)
572
SQL;
573
574
        $this->connection->query($sql);
575
        $onlineTable = $this->schemaManager->listTableDetails('test_table_metadata');
576
577
        self::assertEquals('InnoDB', $onlineTable->getOption('engine'));
578
        self::assertEquals('utf8_general_ci', $onlineTable->getOption('collation'));
579
        self::assertEquals(42, $onlineTable->getOption('autoincrement'));
580
        self::assertEquals('This is a test', $onlineTable->getOption('comment'));
581
        self::assertEquals([
582
            'row_format' => 'COMPRESSED',
583
            'partitioned' => true,
584
        ], $onlineTable->getOption('create_options'));
585
    }
586
587
    public function testEnsureTableWithoutOptionsAreReflectedInMetadata() : void
588
    {
589
        $this->connection->query('DROP TABLE IF EXISTS test_table_empty_metadata');
590
591
        $this->connection->query('CREATE TABLE test_table_empty_metadata(col1 INT NOT NULL)');
592
        $onlineTable = $this->schemaManager->listTableDetails('test_table_empty_metadata');
593
594
        self::assertNotEmpty($onlineTable->getOption('engine'));
595
        // collation could be set to default or not set, information_schema indicate a possibly null value
596
        self::assertFalse($onlineTable->hasOption('autoincrement'));
597
        self::assertEquals('', $onlineTable->getOption('comment'));
598
        self::assertEquals([], $onlineTable->getOption('create_options'));
599
    }
600
601
    public function testParseNullCreateOptions() : void
602
    {
603
        $table = $this->schemaManager->listTableDetails('sys.processlist');
604
605
        self::assertEquals([], $table->getOption('create_options'));
606
    }
607
}
608