Find this useful? Enter your email to receive occasional updates for securing PHP code.
Signing you up...
Thank you for signing up!
PHP Decode
<?php declare(strict_types=1); namespace Doctrine\DBAL\Tests\Functional\Schema; use Dat..
Decoded Output download
<?php
declare(strict_types=1);
namespace Doctrine\DBAL\Tests\Functional\Schema;
use DateTime;
use Doctrine\DBAL\DriverManager;
use Doctrine\DBAL\Exception\DatabaseRequired;
use Doctrine\DBAL\Platforms\AbstractMySQLPlatform;
use Doctrine\DBAL\Platforms\AbstractPlatform;
use Doctrine\DBAL\Platforms\MariaDBPlatform;
use Doctrine\DBAL\Schema\Table;
use Doctrine\DBAL\Tests\Functional\Schema\MySQL\CustomType;
use Doctrine\DBAL\Tests\Functional\Schema\MySQL\PointType;
use Doctrine\DBAL\Tests\TestUtil;
use Doctrine\DBAL\Types\BlobType;
use Doctrine\DBAL\Types\JsonType;
use Doctrine\DBAL\Types\Type;
use Doctrine\DBAL\Types\Types;
use function array_keys;
class MySQLSchemaManagerTest extends SchemaManagerFunctionalTestCase
{
public static function setUpBeforeClass(): void
{
Type::addType('point', PointType::class);
}
protected function supportsPlatform(AbstractPlatform $platform): bool
{
return $platform instanceof AbstractMySQLPlatform;
}
public function testSwitchPrimaryKeyColumns(): void
{
$tableOld = new Table('switch_primary_key_columns');
$tableOld->addColumn('foo_id', Types::INTEGER);
$tableOld->addColumn('bar_id', Types::INTEGER);
$this->schemaManager->createTable($tableOld);
$tableFetched = $this->schemaManager->introspectTable('switch_primary_key_columns');
$tableNew = clone $tableFetched;
$tableNew->setPrimaryKey(['bar_id', 'foo_id']);
$diff = $this->schemaManager->createComparator()
->compareTables($tableFetched, $tableNew);
$this->schemaManager->alterTable($diff);
$table = $this->schemaManager->introspectTable('switch_primary_key_columns');
$primaryKey = $table->getPrimaryKey();
self::assertNotNull($primaryKey);
self::assertSame(['bar_id', 'foo_id'], $primaryKey->getColumns());
}
public function testFulltextIndex(): void
{
$table = new Table('fulltext_index');
$table->addColumn('text', Types::TEXT);
$table->addIndex(['text'], 'f_index');
$table->addOption('engine', 'MyISAM');
$index = $table->getIndex('f_index');
$index->addFlag('fulltext');
$this->dropAndCreateTable($table);
$indexes = $this->schemaManager->listTableIndexes('fulltext_index');
self::assertArrayHasKey('f_index', $indexes);
self::assertTrue($indexes['f_index']->hasFlag('fulltext'));
}
public function testSpatialIndex(): void
{
$table = new Table('spatial_index');
$table->addColumn('point', 'point');
$table->addIndex(['point'], 's_index');
$table->addOption('engine', 'MyISAM');
$index = $table->getIndex('s_index');
$index->addFlag('spatial');
$this->dropAndCreateTable($table);
// see https://github.com/doctrine/dbal/issues/4983
$this->markConnectionNotReusable();
$indexes = $this->schemaManager->listTableIndexes('spatial_index');
self::assertArrayHasKey('s_index', $indexes);
self::assertTrue($indexes['s_index']->hasFlag('spatial'));
self::assertSame([0 => null], $indexes['s_index']->getOption('lengths'));
}
public function testIndexWithLength(): void
{
$table = new Table('index_length');
$table->addColumn('text', Types::STRING, ['length' => 255]);
$table->addIndex(['text'], 'text_index', [], ['lengths' => [128]]);
$this->dropAndCreateTable($table);
$indexes = $this->schemaManager->listTableIndexes('index_length');
self::assertArrayHasKey('text_index', $indexes);
self::assertSame([128], $indexes['text_index']->getOption('lengths'));
}
public function testAlterTableAddPrimaryKey(): void
{
$table = new Table('alter_table_add_pk');
$table->addColumn('id', Types::INTEGER);
$table->addColumn('foo', Types::INTEGER);
$table->addIndex(['id'], 'idx_id');
$this->schemaManager->createTable($table);
$diffTable = clone $table;
$diffTable->dropIndex('idx_id');
$diffTable->setPrimaryKey(['id']);
$diff = $this->schemaManager->createComparator()
->compareTables($table, $diffTable);
$this->schemaManager->alterTable($diff);
$table = $this->schemaManager->introspectTable('alter_table_add_pk');
self::assertFalse($table->hasIndex('idx_id'));
self::assertNotNull($table->getPrimaryKey());
}
public function testDropPrimaryKeyWithAutoincrementColumn(): void
{
$table = new Table('drop_primary_key');
$table->addColumn('id', Types::INTEGER, ['autoincrement' => true]);
$table->addColumn('foo', Types::INTEGER);
$table->setPrimaryKey(['id', 'foo']);
$this->dropAndCreateTable($table);
$diffTable = clone $table;
$diffTable->dropPrimaryKey();
$diff = $this->schemaManager->createComparator()
->compareTables($table, $diffTable);
$this->schemaManager->alterTable($diff);
$table = $this->schemaManager->introspectTable('drop_primary_key');
self::assertNull($table->getPrimaryKey());
self::assertFalse($table->getColumn('id')->getAutoincrement());
}
public function testDoesNotPropagateDefaultValuesForUnsupportedColumnTypes(): void
{
if ($this->connection->getDatabasePlatform() instanceof MariaDBPlatform) {
self::markTestSkipped(
'MariaDB supports default values for BLOB and TEXT columns and will propagate values',
);
}
$table = new Table('text_blob_default_value');
$table->addColumn('def_text', Types::TEXT, ['default' => 'def']);
$table->addColumn('def_text_null', Types::TEXT, ['notnull' => false, 'default' => 'def']);
$table->addColumn('def_blob', Types::BLOB, ['default' => 'def']);
$table->addColumn('def_blob_null', Types::BLOB, ['notnull' => false, 'default' => 'def']);
$this->dropAndCreateTable($table);
$onlineTable = $this->schemaManager->introspectTable('text_blob_default_value');
self::assertNull($onlineTable->getColumn('def_text')->getDefault());
self::assertNull($onlineTable->getColumn('def_text_null')->getDefault());
self::assertFalse($onlineTable->getColumn('def_text_null')->getNotnull());
self::assertNull($onlineTable->getColumn('def_blob')->getDefault());
self::assertNull($onlineTable->getColumn('def_blob_null')->getDefault());
self::assertFalse($onlineTable->getColumn('def_blob_null')->getNotnull());
self::assertTrue(
$this->schemaManager->createComparator()
->compareTables($table, $onlineTable)
->isEmpty(),
);
}
public function testColumnCharset(): void
{
$table = new Table('test_column_charset');
$table->addColumn('id', Types::INTEGER);
$table->addColumn('foo', Types::TEXT)->setPlatformOption('charset', 'ascii');
$table->addColumn('bar', Types::TEXT)->setPlatformOption('charset', 'latin1');
$this->dropAndCreateTable($table);
$columns = $this->schemaManager->listTableColumns('test_column_charset');
self::assertFalse($columns['id']->hasPlatformOption('charset'));
self::assertEquals('ascii', $columns['foo']->getPlatformOption('charset'));
self::assertEquals('latin1', $columns['bar']->getPlatformOption('charset'));
}
public function testAlterColumnCharset(): void
{
$tableName = 'test_alter_column_charset';
$table = new Table($tableName);
$table->addColumn('col_text', Types::TEXT)->setPlatformOption('charset', 'utf8');
$this->dropAndCreateTable($table);
$diffTable = clone $table;
$diffTable->getColumn('col_text')->setPlatformOption('charset', 'ascii');
$diff = $this->schemaManager->createComparator()
->compareTables($table, $diffTable);
$this->schemaManager->alterTable($diff);
$table = $this->schemaManager->introspectTable($tableName);
self::assertEquals('ascii', $table->getColumn('col_text')->getPlatformOption('charset'));
}
public function testColumnCharsetChange(): void
{
$table = new Table('test_column_charset_change');
$table->addColumn('col_string', Types::STRING)
->setLength(100)
->setNotnull(true)
->setPlatformOption('charset', 'utf8');
$this->dropAndCreateTable($table);
$diffTable = clone $table;
$diffTable->getColumn('col_string')->setPlatformOption('charset', 'ascii');
$diff = $this->schemaManager->createComparator()
->compareTables($table, $diffTable);
$this->schemaManager->alterTable($diff);
self::assertEquals(
'ascii',
$this->schemaManager->introspectTable('test_column_charset_change')
->getColumn('col_string')
->getPlatformOption('charset'),
);
}
public function testColumnCollation(): void
{
$table = new Table('test_collation');
$table->addOption('collation', 'latin1_swedish_ci');
$table->addOption('charset', 'latin1');
$table->addColumn('id', Types::INTEGER);
$table->addColumn('text', Types::TEXT);
$table->addColumn('foo', Types::TEXT)->setPlatformOption('collation', 'latin1_swedish_ci');
$table->addColumn('bar', Types::TEXT)->setPlatformOption('collation', 'utf8mb4_general_ci');
$table->addColumn('baz', Types::TEXT)->setPlatformOption('collation', 'binary');
$this->dropAndCreateTable($table);
$columns = $this->schemaManager->listTableColumns('test_collation');
self::assertArrayNotHasKey('collation', $columns['id']->getPlatformOptions());
self::assertEquals('latin1_swedish_ci', $columns['text']->getPlatformOption('collation'));
self::assertEquals('latin1_swedish_ci', $columns['foo']->getPlatformOption('collation'));
self::assertEquals('utf8mb4_general_ci', $columns['bar']->getPlatformOption('collation'));
self::assertInstanceOf(BlobType::class, $columns['baz']->getType());
}
public function testListLobTypeColumns(): void
{
$tableName = 'lob_type_columns';
$table = new Table($tableName);
$table->addColumn('col_tinytext', Types::TEXT, ['length' => AbstractMySQLPlatform::LENGTH_LIMIT_TINYTEXT]);
$table->addColumn('col_text', Types::TEXT, ['length' => AbstractMySQLPlatform::LENGTH_LIMIT_TEXT]);
$table->addColumn('col_mediumtext', Types::TEXT, ['length' => AbstractMySQLPlatform::LENGTH_LIMIT_MEDIUMTEXT]);
$table->addColumn('col_longtext', Types::TEXT);
$table->addColumn('col_tinyblob', Types::TEXT, ['length' => AbstractMySQLPlatform::LENGTH_LIMIT_TINYBLOB]);
$table->addColumn('col_blob', Types::BLOB, ['length' => AbstractMySQLPlatform::LENGTH_LIMIT_BLOB]);
$table->addColumn('col_mediumblob', Types::BLOB, ['length' => AbstractMySQLPlatform::LENGTH_LIMIT_MEDIUMBLOB]);
$table->addColumn('col_longblob', Types::BLOB);
$this->dropAndCreateTable($table);
$platform = $this->connection->getDatabasePlatform();
$onlineColumns = $this->schemaManager->listTableColumns($tableName);
self::assertSame(
$platform->getClobTypeDeclarationSQL($table->getColumn('col_tinytext')->toArray()),
$platform->getClobTypeDeclarationSQL($onlineColumns['col_tinytext']->toArray()),
);
self::assertSame(
$platform->getClobTypeDeclarationSQL($table->getColumn('col_text')->toArray()),
$platform->getClobTypeDeclarationSQL($onlineColumns['col_text']->toArray()),
);
self::assertSame(
$platform->getClobTypeDeclarationSQL($table->getColumn('col_mediumtext')->toArray()),
$platform->getClobTypeDeclarationSQL($onlineColumns['col_mediumtext']->toArray()),
);
self::assertSame(
$platform->getClobTypeDeclarationSQL($table->getColumn('col_longtext')->toArray()),
$platform->getClobTypeDeclarationSQL($onlineColumns['col_longtext']->toArray()),
);
self::assertSame(
$platform->getBlobTypeDeclarationSQL($table->getColumn('col_tinyblob')->toArray()),
$platform->getBlobTypeDeclarationSQL($onlineColumns['col_tinyblob']->toArray()),
);
self::assertSame(
$platform->getBlobTypeDeclarationSQL($table->getColumn('col_blob')->toArray()),
$platform->getBlobTypeDeclarationSQL($onlineColumns['col_blob']->toArray()),
);
self::assertSame(
$platform->getBlobTypeDeclarationSQL($table->getColumn('col_mediumblob')->toArray()),
$platform->getBlobTypeDeclarationSQL($onlineColumns['col_mediumblob']->toArray()),
);
self::assertSame(
$platform->getBlobTypeDeclarationSQL($table->getColumn('col_longblob')->toArray()),
$platform->getBlobTypeDeclarationSQL($onlineColumns['col_longblob']->toArray()),
);
}
public function testDiffListGuidTableColumn(): void
{
$offlineTable = new Table('list_guid_table_column');
$offlineTable->addColumn('col_guid', Types::GUID);
$this->dropAndCreateTable($offlineTable);
$onlineTable = $this->schemaManager->introspectTable('list_guid_table_column');
self::assertTrue(
$this->schemaManager
->createComparator()
->compareTables($onlineTable, $offlineTable)
->isEmpty(),
'No differences should be detected with the offline vs online schema.',
);
}
public function testListDecimalTypeColumns(): void
{
$tableName = 'test_list_decimal_columns';
$table = new Table($tableName);
$table->addColumn('col', Types::DECIMAL, [
'precision' => 10,
'scale' => 6,
]);
$table->addColumn('col_unsigned', Types::DECIMAL, [
'precision' => 10,
'scale' => 6,
'unsigned' => true,
]);
$this->dropAndCreateTable($table);
$columns = $this->schemaManager->listTableColumns($tableName);
self::assertArrayHasKey('col', $columns);
self::assertArrayHasKey('col_unsigned', $columns);
self::assertFalse($columns['col']->getUnsigned());
self::assertTrue($columns['col_unsigned']->getUnsigned());
}
public function testListFloatTypeColumns(): void
{
$tableName = 'test_list_float_columns';
$table = new Table($tableName);
$table->addColumn('col', Types::FLOAT);
$table->addColumn('col_unsigned', Types::FLOAT, ['unsigned' => true]);
$this->dropAndCreateTable($table);
$columns = $this->schemaManager->listTableColumns($tableName);
self::assertArrayHasKey('col', $columns);
self::assertArrayHasKey('col_unsigned', $columns);
self::assertFalse($columns['col']->getUnsigned());
self::assertTrue($columns['col_unsigned']->getUnsigned());
}
public function testJsonColumnType(): void
{
$table = new Table('test_mysql_json');
$table->addColumn('col_json', Types::JSON);
$this->dropAndCreateTable($table);
$columns = $this->schemaManager->listTableColumns('test_mysql_json');
self::assertInstanceOf(JsonType::class, $columns['col_json']->getType());
}
public function testColumnDefaultCurrentTimestamp(): void
{
$platform = $this->connection->getDatabasePlatform();
$table = new Table('test_column_defaults_current_timestamp');
$currentTimeStampSql = $platform->getCurrentTimestampSQL();
$table->addColumn(
'col_datetime',
Types::DATETIME_MUTABLE,
['notnull' => true, 'default' => $currentTimeStampSql],
);
$table->addColumn('col_datetime_nullable', Types::DATETIME_MUTABLE, ['default' => $currentTimeStampSql]);
$this->dropAndCreateTable($table);
$onlineTable = $this->schemaManager->introspectTable('test_column_defaults_current_timestamp');
self::assertSame($currentTimeStampSql, $onlineTable->getColumn('col_datetime')->getDefault());
self::assertSame($currentTimeStampSql, $onlineTable->getColumn('col_datetime_nullable')->getDefault());
self::assertTrue(
$this->schemaManager
->createComparator()
->compareTables($table, $onlineTable)
->isEmpty(),
);
}
public function testColumnDefaultsAreValid(): void
{
$table = new Table('test_column_defaults_are_valid');
$currentTimeStampSql = $this->connection->getDatabasePlatform()->getCurrentTimestampSQL();
$table->addColumn('col_datetime', Types::DATETIME_MUTABLE, ['default' => $currentTimeStampSql]);
$table->addColumn('col_datetime_null', Types::DATETIME_MUTABLE, ['notnull' => false, 'default' => null]);
$table->addColumn('col_int', Types::INTEGER, ['default' => 1]);
$table->addColumn('col_neg_int', Types::INTEGER, ['default' => -1]);
$table->addColumn('col_string', Types::STRING, [
'length' => 1,
'default' => 'A',
]);
$table->addColumn('col_decimal', Types::DECIMAL, ['scale' => 3, 'precision' => 6, 'default' => -2.3]);
$table->addColumn('col_date', Types::DATE_MUTABLE, ['default' => '2012-12-12']);
$this->dropAndCreateTable($table);
$this->connection->executeStatement(
'INSERT INTO test_column_defaults_are_valid () VALUES()',
);
$row = $this->connection->fetchAssociative(
'SELECT *, DATEDIFF(CURRENT_TIMESTAMP(), col_datetime) as diff_seconds FROM test_column_defaults_are_valid',
);
self::assertNotFalse($row);
self::assertInstanceOf(DateTime::class, DateTime::createFromFormat('Y-m-d H:i:s', $row['col_datetime']));
self::assertNull($row['col_datetime_null']);
self::assertSame('2012-12-12', $row['col_date']);
self::assertSame('A', $row['col_string']);
self::assertEquals(1, $row['col_int']);
self::assertEquals(-1, $row['col_neg_int']);
self::assertEquals('-2.300', $row['col_decimal']);
self::assertLessThan(5, $row['diff_seconds']);
}
/**
* MariaDB 10.2+ does support CURRENT_TIME and CURRENT_DATE as
* column default values for time and date columns.
* (Not supported on Mysql as of 5.7.19)
*
* Note that MariaDB 10.2+, when storing default in information_schema,
* silently change CURRENT_TIMESTAMP as 'current_timestamp()',
* CURRENT_TIME as 'currtime()' and CURRENT_DATE as 'currdate()'.
* This test also ensure proper aliasing to not trigger a table diff.
*/
public function testColumnDefaultValuesCurrentTimeAndDate(): void
{
if (! $this->connection->getDatabasePlatform() instanceof MariaDBPlatform) {
self::markTestSkipped('Only relevant for MariaDB.');
}
$platform = $this->connection->getDatabasePlatform();
$table = new Table('test_column_defaults_current_time_and_date');
$currentTimestampSql = $platform->getCurrentTimestampSQL();
$currentTimeSql = $platform->getCurrentTimeSQL();
$currentDateSql = $platform->getCurrentDateSQL();
$table->addColumn('col_datetime', Types::DATETIME_MUTABLE, ['default' => $currentTimestampSql]);
$table->addColumn('col_date', Types::DATE_MUTABLE, ['default' => $currentDateSql]);
$table->addColumn('col_time', Types::TIME_MUTABLE, ['default' => $currentTimeSql]);
$this->dropAndCreateTable($table);
$onlineTable = $this->schemaManager->introspectTable('test_column_defaults_current_time_and_date');
self::assertSame($currentTimestampSql, $onlineTable->getColumn('col_datetime')->getDefault());
self::assertSame($currentDateSql, $onlineTable->getColumn('col_date')->getDefault());
self::assertSame($currentTimeSql, $onlineTable->getColumn('col_time')->getDefault());
self::assertTrue(
$this->schemaManager
->createComparator()
->compareTables($table, $onlineTable)
->isEmpty(),
);
}
public function testEnsureTableOptionsAreReflectedInMetadata(): void
{
$this->connection->executeStatement('DROP TABLE IF EXISTS test_table_metadata');
$sql = <<<'SQL'
CREATE TABLE test_table_metadata(
col1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY
)
COLLATE utf8mb4_general_ci
ENGINE InnoDB
ROW_FORMAT DYNAMIC
COMMENT 'This is a test'
AUTO_INCREMENT=42
PARTITION BY HASH (col1)
SQL;
$this->connection->executeStatement($sql);
$onlineTable = $this->schemaManager->introspectTable('test_table_metadata');
self::assertEquals('InnoDB', $onlineTable->getOption('engine'));
self::assertEquals('utf8mb4_general_ci', $onlineTable->getOption('collation'));
self::assertEquals(42, $onlineTable->getOption('autoincrement'));
self::assertEquals('This is a test', $onlineTable->getOption('comment'));
self::assertEquals([
'row_format' => 'DYNAMIC',
'partitioned' => true,
], $onlineTable->getOption('create_options'));
}
public function testEnsureTableWithoutOptionsAreReflectedInMetadata(): void
{
$this->connection->executeStatement('DROP TABLE IF EXISTS test_table_empty_metadata');
$this->connection->executeStatement('CREATE TABLE test_table_empty_metadata(col1 INT NOT NULL)');
$onlineTable = $this->schemaManager->introspectTable('test_table_empty_metadata');
self::assertNotEmpty($onlineTable->getOption('engine'));
// collation could be set to default or not set, information_schema indicate a possibly null value
self::assertFalse($onlineTable->hasOption('autoincrement'));
self::assertEquals('', $onlineTable->getOption('comment'));
self::assertEquals([], $onlineTable->getOption('create_options'));
}
public function testColumnIntrospection(): void
{
$table = new Table('test_column_introspection');
$doctrineTypes = array_keys(Type::getTypesMap());
foreach ($doctrineTypes as $type) {
$table->addColumn('col_' . $type, $type, ['length' => 8, 'precision' => 8, 'scale' => 2]);
}
$this->dropAndCreateTable($table);
$onlineTable = $this->schemaManager->introspectTable('test_column_introspection');
$diff = $this->schemaManager->createComparator()->compareTables($table, $onlineTable);
self::assertTrue($diff->isEmpty(), 'Tables should be identical.');
}
public function testListTableColumnsThrowsDatabaseRequired(): void
{
$params = TestUtil::getConnectionParams();
unset($params['dbname']);
$connection = DriverManager::getConnection($params);
$schemaManager = $connection->createSchemaManager();
$this->expectException(DatabaseRequired::class);
$schemaManager->listTableColumns('users');
}
public function testSchemaDiffWithCustomColumnTypeWhileDatabaseTypeDiffers(): void
{
Type::addType('custom_type', CustomType::class);
$metadataTable = new Table('table_with_custom_type');
$metadataTable->addColumn('col1', 'custom_type');
self::assertSame(
['CREATE TABLE table_with_custom_type (col1 INT NOT NULL)'],
$this->connection->getDatabasePlatform()->getCreateTableSQL($metadataTable),
);
$this->connection->executeStatement('DROP TABLE IF EXISTS table_with_custom_type');
$this->connection->executeStatement('CREATE TABLE table_with_custom_type (col1 VARCHAR(255) NOT NULL)');
$onlineTable = $this->schemaManager->introspectTable('table_with_custom_type');
$comparator = $this->schemaManager->createComparator();
$tablesDiff = $comparator->compareTables($onlineTable, $metadataTable);
self::assertSame(
['ALTER TABLE table_with_custom_type CHANGE col1 col1 INT NOT NULL'],
$this->connection->getDatabasePlatform()->getAlterTableSQL($tablesDiff),
' The column should be changed from VARCAHR TO INT',
);
}
}
?>
Did this file decode correctly?
Original Code
<?php
declare(strict_types=1);
namespace Doctrine\DBAL\Tests\Functional\Schema;
use DateTime;
use Doctrine\DBAL\DriverManager;
use Doctrine\DBAL\Exception\DatabaseRequired;
use Doctrine\DBAL\Platforms\AbstractMySQLPlatform;
use Doctrine\DBAL\Platforms\AbstractPlatform;
use Doctrine\DBAL\Platforms\MariaDBPlatform;
use Doctrine\DBAL\Schema\Table;
use Doctrine\DBAL\Tests\Functional\Schema\MySQL\CustomType;
use Doctrine\DBAL\Tests\Functional\Schema\MySQL\PointType;
use Doctrine\DBAL\Tests\TestUtil;
use Doctrine\DBAL\Types\BlobType;
use Doctrine\DBAL\Types\JsonType;
use Doctrine\DBAL\Types\Type;
use Doctrine\DBAL\Types\Types;
use function array_keys;
class MySQLSchemaManagerTest extends SchemaManagerFunctionalTestCase
{
public static function setUpBeforeClass(): void
{
Type::addType('point', PointType::class);
}
protected function supportsPlatform(AbstractPlatform $platform): bool
{
return $platform instanceof AbstractMySQLPlatform;
}
public function testSwitchPrimaryKeyColumns(): void
{
$tableOld = new Table('switch_primary_key_columns');
$tableOld->addColumn('foo_id', Types::INTEGER);
$tableOld->addColumn('bar_id', Types::INTEGER);
$this->schemaManager->createTable($tableOld);
$tableFetched = $this->schemaManager->introspectTable('switch_primary_key_columns');
$tableNew = clone $tableFetched;
$tableNew->setPrimaryKey(['bar_id', 'foo_id']);
$diff = $this->schemaManager->createComparator()
->compareTables($tableFetched, $tableNew);
$this->schemaManager->alterTable($diff);
$table = $this->schemaManager->introspectTable('switch_primary_key_columns');
$primaryKey = $table->getPrimaryKey();
self::assertNotNull($primaryKey);
self::assertSame(['bar_id', 'foo_id'], $primaryKey->getColumns());
}
public function testFulltextIndex(): void
{
$table = new Table('fulltext_index');
$table->addColumn('text', Types::TEXT);
$table->addIndex(['text'], 'f_index');
$table->addOption('engine', 'MyISAM');
$index = $table->getIndex('f_index');
$index->addFlag('fulltext');
$this->dropAndCreateTable($table);
$indexes = $this->schemaManager->listTableIndexes('fulltext_index');
self::assertArrayHasKey('f_index', $indexes);
self::assertTrue($indexes['f_index']->hasFlag('fulltext'));
}
public function testSpatialIndex(): void
{
$table = new Table('spatial_index');
$table->addColumn('point', 'point');
$table->addIndex(['point'], 's_index');
$table->addOption('engine', 'MyISAM');
$index = $table->getIndex('s_index');
$index->addFlag('spatial');
$this->dropAndCreateTable($table);
// see https://github.com/doctrine/dbal/issues/4983
$this->markConnectionNotReusable();
$indexes = $this->schemaManager->listTableIndexes('spatial_index');
self::assertArrayHasKey('s_index', $indexes);
self::assertTrue($indexes['s_index']->hasFlag('spatial'));
self::assertSame([0 => null], $indexes['s_index']->getOption('lengths'));
}
public function testIndexWithLength(): void
{
$table = new Table('index_length');
$table->addColumn('text', Types::STRING, ['length' => 255]);
$table->addIndex(['text'], 'text_index', [], ['lengths' => [128]]);
$this->dropAndCreateTable($table);
$indexes = $this->schemaManager->listTableIndexes('index_length');
self::assertArrayHasKey('text_index', $indexes);
self::assertSame([128], $indexes['text_index']->getOption('lengths'));
}
public function testAlterTableAddPrimaryKey(): void
{
$table = new Table('alter_table_add_pk');
$table->addColumn('id', Types::INTEGER);
$table->addColumn('foo', Types::INTEGER);
$table->addIndex(['id'], 'idx_id');
$this->schemaManager->createTable($table);
$diffTable = clone $table;
$diffTable->dropIndex('idx_id');
$diffTable->setPrimaryKey(['id']);
$diff = $this->schemaManager->createComparator()
->compareTables($table, $diffTable);
$this->schemaManager->alterTable($diff);
$table = $this->schemaManager->introspectTable('alter_table_add_pk');
self::assertFalse($table->hasIndex('idx_id'));
self::assertNotNull($table->getPrimaryKey());
}
public function testDropPrimaryKeyWithAutoincrementColumn(): void
{
$table = new Table('drop_primary_key');
$table->addColumn('id', Types::INTEGER, ['autoincrement' => true]);
$table->addColumn('foo', Types::INTEGER);
$table->setPrimaryKey(['id', 'foo']);
$this->dropAndCreateTable($table);
$diffTable = clone $table;
$diffTable->dropPrimaryKey();
$diff = $this->schemaManager->createComparator()
->compareTables($table, $diffTable);
$this->schemaManager->alterTable($diff);
$table = $this->schemaManager->introspectTable('drop_primary_key');
self::assertNull($table->getPrimaryKey());
self::assertFalse($table->getColumn('id')->getAutoincrement());
}
public function testDoesNotPropagateDefaultValuesForUnsupportedColumnTypes(): void
{
if ($this->connection->getDatabasePlatform() instanceof MariaDBPlatform) {
self::markTestSkipped(
'MariaDB supports default values for BLOB and TEXT columns and will propagate values',
);
}
$table = new Table('text_blob_default_value');
$table->addColumn('def_text', Types::TEXT, ['default' => 'def']);
$table->addColumn('def_text_null', Types::TEXT, ['notnull' => false, 'default' => 'def']);
$table->addColumn('def_blob', Types::BLOB, ['default' => 'def']);
$table->addColumn('def_blob_null', Types::BLOB, ['notnull' => false, 'default' => 'def']);
$this->dropAndCreateTable($table);
$onlineTable = $this->schemaManager->introspectTable('text_blob_default_value');
self::assertNull($onlineTable->getColumn('def_text')->getDefault());
self::assertNull($onlineTable->getColumn('def_text_null')->getDefault());
self::assertFalse($onlineTable->getColumn('def_text_null')->getNotnull());
self::assertNull($onlineTable->getColumn('def_blob')->getDefault());
self::assertNull($onlineTable->getColumn('def_blob_null')->getDefault());
self::assertFalse($onlineTable->getColumn('def_blob_null')->getNotnull());
self::assertTrue(
$this->schemaManager->createComparator()
->compareTables($table, $onlineTable)
->isEmpty(),
);
}
public function testColumnCharset(): void
{
$table = new Table('test_column_charset');
$table->addColumn('id', Types::INTEGER);
$table->addColumn('foo', Types::TEXT)->setPlatformOption('charset', 'ascii');
$table->addColumn('bar', Types::TEXT)->setPlatformOption('charset', 'latin1');
$this->dropAndCreateTable($table);
$columns = $this->schemaManager->listTableColumns('test_column_charset');
self::assertFalse($columns['id']->hasPlatformOption('charset'));
self::assertEquals('ascii', $columns['foo']->getPlatformOption('charset'));
self::assertEquals('latin1', $columns['bar']->getPlatformOption('charset'));
}
public function testAlterColumnCharset(): void
{
$tableName = 'test_alter_column_charset';
$table = new Table($tableName);
$table->addColumn('col_text', Types::TEXT)->setPlatformOption('charset', 'utf8');
$this->dropAndCreateTable($table);
$diffTable = clone $table;
$diffTable->getColumn('col_text')->setPlatformOption('charset', 'ascii');
$diff = $this->schemaManager->createComparator()
->compareTables($table, $diffTable);
$this->schemaManager->alterTable($diff);
$table = $this->schemaManager->introspectTable($tableName);
self::assertEquals('ascii', $table->getColumn('col_text')->getPlatformOption('charset'));
}
public function testColumnCharsetChange(): void
{
$table = new Table('test_column_charset_change');
$table->addColumn('col_string', Types::STRING)
->setLength(100)
->setNotnull(true)
->setPlatformOption('charset', 'utf8');
$this->dropAndCreateTable($table);
$diffTable = clone $table;
$diffTable->getColumn('col_string')->setPlatformOption('charset', 'ascii');
$diff = $this->schemaManager->createComparator()
->compareTables($table, $diffTable);
$this->schemaManager->alterTable($diff);
self::assertEquals(
'ascii',
$this->schemaManager->introspectTable('test_column_charset_change')
->getColumn('col_string')
->getPlatformOption('charset'),
);
}
public function testColumnCollation(): void
{
$table = new Table('test_collation');
$table->addOption('collation', 'latin1_swedish_ci');
$table->addOption('charset', 'latin1');
$table->addColumn('id', Types::INTEGER);
$table->addColumn('text', Types::TEXT);
$table->addColumn('foo', Types::TEXT)->setPlatformOption('collation', 'latin1_swedish_ci');
$table->addColumn('bar', Types::TEXT)->setPlatformOption('collation', 'utf8mb4_general_ci');
$table->addColumn('baz', Types::TEXT)->setPlatformOption('collation', 'binary');
$this->dropAndCreateTable($table);
$columns = $this->schemaManager->listTableColumns('test_collation');
self::assertArrayNotHasKey('collation', $columns['id']->getPlatformOptions());
self::assertEquals('latin1_swedish_ci', $columns['text']->getPlatformOption('collation'));
self::assertEquals('latin1_swedish_ci', $columns['foo']->getPlatformOption('collation'));
self::assertEquals('utf8mb4_general_ci', $columns['bar']->getPlatformOption('collation'));
self::assertInstanceOf(BlobType::class, $columns['baz']->getType());
}
public function testListLobTypeColumns(): void
{
$tableName = 'lob_type_columns';
$table = new Table($tableName);
$table->addColumn('col_tinytext', Types::TEXT, ['length' => AbstractMySQLPlatform::LENGTH_LIMIT_TINYTEXT]);
$table->addColumn('col_text', Types::TEXT, ['length' => AbstractMySQLPlatform::LENGTH_LIMIT_TEXT]);
$table->addColumn('col_mediumtext', Types::TEXT, ['length' => AbstractMySQLPlatform::LENGTH_LIMIT_MEDIUMTEXT]);
$table->addColumn('col_longtext', Types::TEXT);
$table->addColumn('col_tinyblob', Types::TEXT, ['length' => AbstractMySQLPlatform::LENGTH_LIMIT_TINYBLOB]);
$table->addColumn('col_blob', Types::BLOB, ['length' => AbstractMySQLPlatform::LENGTH_LIMIT_BLOB]);
$table->addColumn('col_mediumblob', Types::BLOB, ['length' => AbstractMySQLPlatform::LENGTH_LIMIT_MEDIUMBLOB]);
$table->addColumn('col_longblob', Types::BLOB);
$this->dropAndCreateTable($table);
$platform = $this->connection->getDatabasePlatform();
$onlineColumns = $this->schemaManager->listTableColumns($tableName);
self::assertSame(
$platform->getClobTypeDeclarationSQL($table->getColumn('col_tinytext')->toArray()),
$platform->getClobTypeDeclarationSQL($onlineColumns['col_tinytext']->toArray()),
);
self::assertSame(
$platform->getClobTypeDeclarationSQL($table->getColumn('col_text')->toArray()),
$platform->getClobTypeDeclarationSQL($onlineColumns['col_text']->toArray()),
);
self::assertSame(
$platform->getClobTypeDeclarationSQL($table->getColumn('col_mediumtext')->toArray()),
$platform->getClobTypeDeclarationSQL($onlineColumns['col_mediumtext']->toArray()),
);
self::assertSame(
$platform->getClobTypeDeclarationSQL($table->getColumn('col_longtext')->toArray()),
$platform->getClobTypeDeclarationSQL($onlineColumns['col_longtext']->toArray()),
);
self::assertSame(
$platform->getBlobTypeDeclarationSQL($table->getColumn('col_tinyblob')->toArray()),
$platform->getBlobTypeDeclarationSQL($onlineColumns['col_tinyblob']->toArray()),
);
self::assertSame(
$platform->getBlobTypeDeclarationSQL($table->getColumn('col_blob')->toArray()),
$platform->getBlobTypeDeclarationSQL($onlineColumns['col_blob']->toArray()),
);
self::assertSame(
$platform->getBlobTypeDeclarationSQL($table->getColumn('col_mediumblob')->toArray()),
$platform->getBlobTypeDeclarationSQL($onlineColumns['col_mediumblob']->toArray()),
);
self::assertSame(
$platform->getBlobTypeDeclarationSQL($table->getColumn('col_longblob')->toArray()),
$platform->getBlobTypeDeclarationSQL($onlineColumns['col_longblob']->toArray()),
);
}
public function testDiffListGuidTableColumn(): void
{
$offlineTable = new Table('list_guid_table_column');
$offlineTable->addColumn('col_guid', Types::GUID);
$this->dropAndCreateTable($offlineTable);
$onlineTable = $this->schemaManager->introspectTable('list_guid_table_column');
self::assertTrue(
$this->schemaManager
->createComparator()
->compareTables($onlineTable, $offlineTable)
->isEmpty(),
'No differences should be detected with the offline vs online schema.',
);
}
public function testListDecimalTypeColumns(): void
{
$tableName = 'test_list_decimal_columns';
$table = new Table($tableName);
$table->addColumn('col', Types::DECIMAL, [
'precision' => 10,
'scale' => 6,
]);
$table->addColumn('col_unsigned', Types::DECIMAL, [
'precision' => 10,
'scale' => 6,
'unsigned' => true,
]);
$this->dropAndCreateTable($table);
$columns = $this->schemaManager->listTableColumns($tableName);
self::assertArrayHasKey('col', $columns);
self::assertArrayHasKey('col_unsigned', $columns);
self::assertFalse($columns['col']->getUnsigned());
self::assertTrue($columns['col_unsigned']->getUnsigned());
}
public function testListFloatTypeColumns(): void
{
$tableName = 'test_list_float_columns';
$table = new Table($tableName);
$table->addColumn('col', Types::FLOAT);
$table->addColumn('col_unsigned', Types::FLOAT, ['unsigned' => true]);
$this->dropAndCreateTable($table);
$columns = $this->schemaManager->listTableColumns($tableName);
self::assertArrayHasKey('col', $columns);
self::assertArrayHasKey('col_unsigned', $columns);
self::assertFalse($columns['col']->getUnsigned());
self::assertTrue($columns['col_unsigned']->getUnsigned());
}
public function testJsonColumnType(): void
{
$table = new Table('test_mysql_json');
$table->addColumn('col_json', Types::JSON);
$this->dropAndCreateTable($table);
$columns = $this->schemaManager->listTableColumns('test_mysql_json');
self::assertInstanceOf(JsonType::class, $columns['col_json']->getType());
}
public function testColumnDefaultCurrentTimestamp(): void
{
$platform = $this->connection->getDatabasePlatform();
$table = new Table('test_column_defaults_current_timestamp');
$currentTimeStampSql = $platform->getCurrentTimestampSQL();
$table->addColumn(
'col_datetime',
Types::DATETIME_MUTABLE,
['notnull' => true, 'default' => $currentTimeStampSql],
);
$table->addColumn('col_datetime_nullable', Types::DATETIME_MUTABLE, ['default' => $currentTimeStampSql]);
$this->dropAndCreateTable($table);
$onlineTable = $this->schemaManager->introspectTable('test_column_defaults_current_timestamp');
self::assertSame($currentTimeStampSql, $onlineTable->getColumn('col_datetime')->getDefault());
self::assertSame($currentTimeStampSql, $onlineTable->getColumn('col_datetime_nullable')->getDefault());
self::assertTrue(
$this->schemaManager
->createComparator()
->compareTables($table, $onlineTable)
->isEmpty(),
);
}
public function testColumnDefaultsAreValid(): void
{
$table = new Table('test_column_defaults_are_valid');
$currentTimeStampSql = $this->connection->getDatabasePlatform()->getCurrentTimestampSQL();
$table->addColumn('col_datetime', Types::DATETIME_MUTABLE, ['default' => $currentTimeStampSql]);
$table->addColumn('col_datetime_null', Types::DATETIME_MUTABLE, ['notnull' => false, 'default' => null]);
$table->addColumn('col_int', Types::INTEGER, ['default' => 1]);
$table->addColumn('col_neg_int', Types::INTEGER, ['default' => -1]);
$table->addColumn('col_string', Types::STRING, [
'length' => 1,
'default' => 'A',
]);
$table->addColumn('col_decimal', Types::DECIMAL, ['scale' => 3, 'precision' => 6, 'default' => -2.3]);
$table->addColumn('col_date', Types::DATE_MUTABLE, ['default' => '2012-12-12']);
$this->dropAndCreateTable($table);
$this->connection->executeStatement(
'INSERT INTO test_column_defaults_are_valid () VALUES()',
);
$row = $this->connection->fetchAssociative(
'SELECT *, DATEDIFF(CURRENT_TIMESTAMP(), col_datetime) as diff_seconds FROM test_column_defaults_are_valid',
);
self::assertNotFalse($row);
self::assertInstanceOf(DateTime::class, DateTime::createFromFormat('Y-m-d H:i:s', $row['col_datetime']));
self::assertNull($row['col_datetime_null']);
self::assertSame('2012-12-12', $row['col_date']);
self::assertSame('A', $row['col_string']);
self::assertEquals(1, $row['col_int']);
self::assertEquals(-1, $row['col_neg_int']);
self::assertEquals('-2.300', $row['col_decimal']);
self::assertLessThan(5, $row['diff_seconds']);
}
/**
* MariaDB 10.2+ does support CURRENT_TIME and CURRENT_DATE as
* column default values for time and date columns.
* (Not supported on Mysql as of 5.7.19)
*
* Note that MariaDB 10.2+, when storing default in information_schema,
* silently change CURRENT_TIMESTAMP as 'current_timestamp()',
* CURRENT_TIME as 'currtime()' and CURRENT_DATE as 'currdate()'.
* This test also ensure proper aliasing to not trigger a table diff.
*/
public function testColumnDefaultValuesCurrentTimeAndDate(): void
{
if (! $this->connection->getDatabasePlatform() instanceof MariaDBPlatform) {
self::markTestSkipped('Only relevant for MariaDB.');
}
$platform = $this->connection->getDatabasePlatform();
$table = new Table('test_column_defaults_current_time_and_date');
$currentTimestampSql = $platform->getCurrentTimestampSQL();
$currentTimeSql = $platform->getCurrentTimeSQL();
$currentDateSql = $platform->getCurrentDateSQL();
$table->addColumn('col_datetime', Types::DATETIME_MUTABLE, ['default' => $currentTimestampSql]);
$table->addColumn('col_date', Types::DATE_MUTABLE, ['default' => $currentDateSql]);
$table->addColumn('col_time', Types::TIME_MUTABLE, ['default' => $currentTimeSql]);
$this->dropAndCreateTable($table);
$onlineTable = $this->schemaManager->introspectTable('test_column_defaults_current_time_and_date');
self::assertSame($currentTimestampSql, $onlineTable->getColumn('col_datetime')->getDefault());
self::assertSame($currentDateSql, $onlineTable->getColumn('col_date')->getDefault());
self::assertSame($currentTimeSql, $onlineTable->getColumn('col_time')->getDefault());
self::assertTrue(
$this->schemaManager
->createComparator()
->compareTables($table, $onlineTable)
->isEmpty(),
);
}
public function testEnsureTableOptionsAreReflectedInMetadata(): void
{
$this->connection->executeStatement('DROP TABLE IF EXISTS test_table_metadata');
$sql = <<<'SQL'
CREATE TABLE test_table_metadata(
col1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY
)
COLLATE utf8mb4_general_ci
ENGINE InnoDB
ROW_FORMAT DYNAMIC
COMMENT 'This is a test'
AUTO_INCREMENT=42
PARTITION BY HASH (col1)
SQL;
$this->connection->executeStatement($sql);
$onlineTable = $this->schemaManager->introspectTable('test_table_metadata');
self::assertEquals('InnoDB', $onlineTable->getOption('engine'));
self::assertEquals('utf8mb4_general_ci', $onlineTable->getOption('collation'));
self::assertEquals(42, $onlineTable->getOption('autoincrement'));
self::assertEquals('This is a test', $onlineTable->getOption('comment'));
self::assertEquals([
'row_format' => 'DYNAMIC',
'partitioned' => true,
], $onlineTable->getOption('create_options'));
}
public function testEnsureTableWithoutOptionsAreReflectedInMetadata(): void
{
$this->connection->executeStatement('DROP TABLE IF EXISTS test_table_empty_metadata');
$this->connection->executeStatement('CREATE TABLE test_table_empty_metadata(col1 INT NOT NULL)');
$onlineTable = $this->schemaManager->introspectTable('test_table_empty_metadata');
self::assertNotEmpty($onlineTable->getOption('engine'));
// collation could be set to default or not set, information_schema indicate a possibly null value
self::assertFalse($onlineTable->hasOption('autoincrement'));
self::assertEquals('', $onlineTable->getOption('comment'));
self::assertEquals([], $onlineTable->getOption('create_options'));
}
public function testColumnIntrospection(): void
{
$table = new Table('test_column_introspection');
$doctrineTypes = array_keys(Type::getTypesMap());
foreach ($doctrineTypes as $type) {
$table->addColumn('col_' . $type, $type, ['length' => 8, 'precision' => 8, 'scale' => 2]);
}
$this->dropAndCreateTable($table);
$onlineTable = $this->schemaManager->introspectTable('test_column_introspection');
$diff = $this->schemaManager->createComparator()->compareTables($table, $onlineTable);
self::assertTrue($diff->isEmpty(), 'Tables should be identical.');
}
public function testListTableColumnsThrowsDatabaseRequired(): void
{
$params = TestUtil::getConnectionParams();
unset($params['dbname']);
$connection = DriverManager::getConnection($params);
$schemaManager = $connection->createSchemaManager();
$this->expectException(DatabaseRequired::class);
$schemaManager->listTableColumns('users');
}
public function testSchemaDiffWithCustomColumnTypeWhileDatabaseTypeDiffers(): void
{
Type::addType('custom_type', CustomType::class);
$metadataTable = new Table('table_with_custom_type');
$metadataTable->addColumn('col1', 'custom_type');
self::assertSame(
['CREATE TABLE table_with_custom_type (col1 INT NOT NULL)'],
$this->connection->getDatabasePlatform()->getCreateTableSQL($metadataTable),
);
$this->connection->executeStatement('DROP TABLE IF EXISTS table_with_custom_type');
$this->connection->executeStatement('CREATE TABLE table_with_custom_type (col1 VARCHAR(255) NOT NULL)');
$onlineTable = $this->schemaManager->introspectTable('table_with_custom_type');
$comparator = $this->schemaManager->createComparator();
$tablesDiff = $comparator->compareTables($onlineTable, $metadataTable);
self::assertSame(
['ALTER TABLE table_with_custom_type CHANGE col1 col1 INT NOT NULL'],
$this->connection->getDatabasePlatform()->getAlterTableSQL($tablesDiff),
' The column should be changed from VARCAHR TO INT',
);
}
}
Function Calls
None |
Stats
MD5 | 14eb5d6c48fef14679eba01003fe5f4c |
Eval Count | 0 |
Decode Time | 118 ms |