public function up(Schema $schema)
{
// this up() migration is auto-generated, please modify it to your needs
$this->abortIf($this->connection->getDatabasePlatform()->getName() != "mysql", "Migration can only be executed safely on 'mysql'.");
$tables = $schema->getTables();
foreach ($tables as $table) {
if ($table->getName() === "os_user") {
if ($table->hasForeignKey('FK_7BB0CD52BF396750')) {
$this->addSql("ALTER TABLE os_user DROP FOREIGN KEY FK_7BB0CD52BF396750");
}
} else {
if ($table->getName() === "os_clock") {
if (!$table->hasColumn("uid")) {
$this->addSql("ALTER TABLE os_clock ADD uid INT DEFAULT NULL");
$this->addSql("UPDATE os_clock SET uid = id");
}
if (!$table->hasForeignKey('FK_34E85465539B0606')) {
$this->addSql("ALTER TABLE os_clock ADD CONSTRAINT FK_34E85465539B0606 FOREIGN KEY (uid) REFERENCES os_user (id)");
}
if (!$table->hasIndex('UNIQ_34E85465539B0606')) {
$this->addSql("CREATE UNIQUE INDEX UNIQ_34E85465539B0606 ON os_clock (uid)");
}
}
}
}
}
/**
* @param Schema $schema
* @param QueryBag $queries
* @param string $tableName
* @param string $columnName
* @param string $type
*
* @throws \Exception
*/
public function changePrimaryKeyType(Schema $schema, QueryBag $queries, $tableName, $columnName, $type)
{
$targetColumn = $schema->getTable($tableName)->getColumn($columnName);
$type = Type::getType($type);
if ($targetColumn->getType() === $type) {
return;
}
/** @var ForeignKeyConstraint[] $foreignKeys */
$foreignKeys = [];
foreach ($schema->getTables() as $table) {
/** @var ForeignKeyConstraint[] $tableForeignKeys */
$tableForeignKeys = array_filter($table->getForeignKeys(), function (ForeignKeyConstraint $tableForeignKey) use($tableName, $columnName) {
if ($tableForeignKey->getForeignTableName() !== $tableName) {
return false;
}
return $tableForeignKey->getForeignColumns() === [$columnName];
});
foreach ($tableForeignKeys as $tableForeignKey) {
$foreignKeys[$tableForeignKey->getName()] = $tableForeignKey;
$foreignKeyTableName = $tableForeignKey->getLocalTable()->getName();
$foreignKeyColumnNames = $tableForeignKey->getLocalColumns();
$queries->addPreQuery($this->platform->getDropForeignKeySQL($tableForeignKey, $foreignKeyTableName));
$column = $schema->getTable($foreignKeyTableName)->getColumn(reset($foreignKeyColumnNames));
if ($column instanceof ExtendColumn) {
$column->disableExtendOptions()->setType($type)->enableExtendOptions();
} else {
$column->setType($type);
}
}
}
$targetColumn->setType($type);
foreach ($foreignKeys as $foreignKey) {
$queries->addPostQuery($this->platform->getCreateForeignKeySQL($foreignKey, $foreignKey->getLocalTable()));
}
}
protected function assertSchemaTypes(Schema $schema)
{
foreach ($schema->getTables() as $table) {
$this->assertInstanceOf('Oro\\Bundle\\EntityExtendBundle\\Migration\\Schema\\ExtendTable', $table);
foreach ($table->getColumns() as $column) {
$this->assertInstanceOf('Oro\\Bundle\\EntityExtendBundle\\Migration\\Schema\\ExtendColumn', $column);
}
}
}
/**
* Return schema with Diamante tables
* @param Schema $schema
* @param array $entitiesTableName
*
* @return Schema
*/
protected function getTargetSchema(Schema $schema, array $entitiesTableName)
{
$allTables = $schema->getTables();
$targetTables = array();
foreach ($allTables as $tableName => $table) {
if (in_array($tableName, $entitiesTableName)) {
$targetTables[$tableName] = $table;
}
}
return new Schema($targetTables);
}
/**
* @param Schema $schema
*
* @return string[]
*/
protected function getExistingEnumTables(Schema $schema)
{
$result = [];
foreach ($schema->getTables() as $table) {
$tableName = $table->getName();
if (strrpos($tableName, 'oro_enum') === 0 && $tableName !== 'oro_enum_value_trans') {
$result[] = $tableName;
}
}
return $result;
}
/**
* @group DBAL-204
*/
public function testRemoveNamespacedAssets()
{
$config = new SchemaConfig();
$config->setName("test");
$schema = new Schema(array(), array(), $config);
$schema->createTable("test.test");
$schema->createTable("foo.bar");
$schema->createTable("baz");
$schema->visit(new RemoveNamespacedAssets());
$tables = $schema->getTables();
$this->assertEquals(array("test.test", "test.baz"), array_keys($tables), "Only 2 tables should be present, both in 'test' namespace.");
}
protected function renameCustomEntityTables(Schema $schema, QueryBag $queries)
{
$tables = $schema->getTables();
foreach ($tables as $table) {
if (strpos($table->getName(), self::OLD_CUSTOM_TABLE_PREFIX) === 0) {
$oldTableName = $table->getName();
$oldTablePrefix = strpos($oldTableName, self::OLD_CUSTOM_TABLE_PREFIX . 'extendentity') === 0 ? self::OLD_CUSTOM_TABLE_PREFIX . 'extendentity' : self::OLD_CUSTOM_TABLE_PREFIX;
$newTableName = ExtendDbIdentifierNameGenerator::CUSTOM_TABLE_PREFIX . substr($oldTableName, strlen($oldTablePrefix));
$this->renameExtension->renameTable($schema, $queries, $oldTableName, $newTableName);
}
}
}
/**
* @param Schema $schema
*/
public function up(Schema $schema)
{
// Check if the tables already exist
$tables = $schema->getTables();
foreach ($tables as $table) {
if ($table->getName() === 'tags') {
return;
}
}
$this->createTagsTable($schema);
$this->createShortUrlsInTagsTable($schema);
}
public function testAddTable()
{
$tableName = "public.foo";
$table = new Table($tableName);
$schema = new Schema(array($table));
$this->assertTrue($schema->hasTable($tableName));
$tables = $schema->getTables();
$this->assertTrue(isset($tables[$tableName]));
$this->assertSame($table, $tables[$tableName]);
$this->assertSame($table, $schema->getTable($tableName));
$this->assertTrue($schema->hasTable($tableName));
}
/**
* Truncate all known tables
*
* @param \Doctrine\ORM\EntityManager $entityManager
* @return void
*/
public function truncateTables($entityManager)
{
$connection = $entityManager->getConnection();
$tables = self::$databaseSchema->getTables();
switch ($connection->getDatabasePlatform()->getName()) {
case 'mysql':
$sql = 'SET FOREIGN_KEY_CHECKS=0;';
foreach ($tables as $table) {
$sql .= 'TRUNCATE `' . $table->getName() . '`;';
}
$sql .= 'SET FOREIGN_KEY_CHECKS=1;';
$connection->executeQuery($sql);
break;
case 'postgresql':
default:
foreach ($tables as $table) {
$sql = 'TRUNCATE ' . $table->getName() . ' CASCADE;';
$connection->executeQuery($sql);
}
break;
}
}
/**
* @param Schema $targetSchema
* @param \Doctrine\DBAL\Connection $connection
* @return \Doctrine\DBAL\Schema\SchemaDiff
*/
protected function getDiff(Schema $targetSchema, \Doctrine\DBAL\Connection $connection)
{
$platform = $connection->getDatabasePlatform();
$platform->registerDoctrineTypeMapping('tinyint unsigned', 'integer');
$platform->registerDoctrineTypeMapping('smallint unsigned', 'integer');
$platform->registerDoctrineTypeMapping('varchar ', 'string');
// with sqlite autoincrement columns is of type integer
foreach ($targetSchema->getTables() as $table) {
foreach ($table->getColumns() as $column) {
if ($column->getType() instanceof BigIntType && $column->getAutoincrement()) {
$column->setType(Type::getType('integer'));
}
}
}
return parent::getDiff($targetSchema, $connection);
}
/**
* @param Schema $targetSchema
* @throws \OC\DB\MigrationException
*/
public function checkMigrate(Schema $targetSchema)
{
/**
* @var \Doctrine\DBAL\Schema\Table[] $tables
*/
$tables = $targetSchema->getTables();
$existingTables = $this->connection->getSchemaManager()->listTableNames();
foreach ($tables as $table) {
if (strpos($table->getName(), '.')) {
list(, $tableName) = explode('.', $table->getName());
} else {
$tableName = $table->getName();
}
// don't need to check for new tables
if (array_search($tableName, $existingTables) !== false) {
$this->checkTableMigrate($table);
}
}
}
/**
* @param Schema $targetSchema
* @throws \OC\DB\MigrationException
*/
public function checkMigrate(Schema $targetSchema)
{
/**
* @var \Doctrine\DBAL\Schema\Table[] $tables
*/
$tables = $targetSchema->getTables();
$this->connection->getConfiguration()->setFilterSchemaAssetsExpression('/^' . $this->config->getSystemValue('dbtableprefix', 'oc_') . '/');
$existingTables = $this->connection->getSchemaManager()->listTableNames();
foreach ($tables as $table) {
if (strpos($table->getName(), '.')) {
list(, $tableName) = explode('.', $table->getName());
} else {
$tableName = $table->getName();
}
// don't need to check for new tables
if (array_search($tableName, $existingTables) !== false) {
$this->checkTableMigrate($table);
}
}
}
/**
* This serves a rather strange use case: renaming columns used in FK constraints.
*
* For a column that is used in a FK constraint to be renamed, the FK constraint has to be
* dropped first, then the column can be renamed and last the FK constraint needs to be
* added back (using the new name, of course).
*
* This method helps with the task of handling the FK constraints during this. Given a list
* of tables that contain columns to be renamed and a search/replace pair for the column name,
* it will return an array with arrays with drop and add SQL statements.
*
* Use them like this before and after renaming the affected fields:
*
* // collect foreign keys pointing to "our" tables
* $tableNames = array(...);
* $foreignKeyHandlingSql = $this->getForeignKeyHandlingSql($schema, $tableNames, 'old_name', 'new_name');
*
* // drop FK constraints
* foreach ($foreignKeyHandlingSql['drop'] as $sql) {
* $this->addSql($sql);
* }
*
* // rename columns now
*
* // add back FK constraints
* foreach ($foreignKeyHandlingSql['add'] as $sql) {
* $this->addSql($sql);
* }
*
* @param \Doctrine\DBAL\Schema\Schema $schema
* @param \Doctrine\DBAL\Platforms\AbstractPlatform $platform
* @param array $tableNames
* @param string $search
* @param string $replace
* @return array
*/
public static function getForeignKeyHandlingSql(\Doctrine\DBAL\Schema\Schema $schema, \Doctrine\DBAL\Platforms\AbstractPlatform $platform, $tableNames, $search, $replace)
{
$foreignKeyHandlingSql = array('drop' => array(), 'add' => array());
$tables = $schema->getTables();
foreach ($tables as $table) {
$foreignKeys = $table->getForeignKeys();
foreach ($foreignKeys as $foreignKey) {
if (!in_array($table->getName(), $tableNames) && !in_array($foreignKey->getForeignTableName(), $tableNames)) {
continue;
}
$localColumns = $foreignKey->getLocalColumns();
$foreignColumns = $foreignKey->getForeignColumns();
if (in_array($search, $foreignColumns) || in_array($search, $localColumns)) {
if (in_array($foreignKey->getLocalTableName(), $tableNames)) {
array_walk($localColumns, function (&$value) use($search, $replace) {
if ($value === $search) {
$value = $replace;
}
});
}
if (in_array($foreignKey->getForeignTableName(), $tableNames)) {
array_walk($foreignColumns, function (&$value) use($search, $replace) {
if ($value === $search) {
$value = $replace;
}
});
}
$identifierConstructorCallback = function ($columnName) {
return new Identifier($columnName);
};
$localColumns = array_map($identifierConstructorCallback, $localColumns);
$foreignColumns = array_map($identifierConstructorCallback, $foreignColumns);
$newForeignKey = clone $foreignKey;
\TYPO3\Flow\Reflection\ObjectAccess::setProperty($newForeignKey, '_localColumnNames', $localColumns, true);
\TYPO3\Flow\Reflection\ObjectAccess::setProperty($newForeignKey, '_foreignColumnNames', $foreignColumns, true);
$foreignKeyHandlingSql['drop'][] = $platform->getDropForeignKeySQL($foreignKey, $table);
$foreignKeyHandlingSql['add'][] = $platform->getCreateForeignKeySQL($newForeignKey, $table);
}
}
}
return $foreignKeyHandlingSql;
}
/**
* {@inheritdoc}
*/
public function getDropSchema(Schema $dropSchema)
{
$visitor = new DropSchemaSqlCollector($this->platform);
$sm = $this->conn->getSchemaManager();
$fullSchema = $sm->createSchema();
foreach ($fullSchema->getTables() as $table) {
if ($dropSchema->hasTable($table->getName())) {
$visitor->acceptTable($table);
}
foreach ($table->getForeignKeys() as $foreignKey) {
if (!$dropSchema->hasTable($table->getName())) {
continue;
}
if (!$dropSchema->hasTable($foreignKey->getForeignTableName())) {
continue;
}
$visitor->acceptForeignKey($table, $foreignKey);
}
}
if (!$this->platform->supportsSequences()) {
return $visitor->getQueries();
}
foreach ($dropSchema->getSequences() as $sequence) {
$visitor->acceptSequence($sequence);
}
foreach ($dropSchema->getTables() as $table) {
if (!$table->hasPrimaryKey()) {
continue;
}
$columns = $table->getPrimaryKey()->getColumns();
if (count($columns) > 1) {
continue;
}
$checkSequence = $table->getName() . "_" . $columns[0] . "_seq";
if ($fullSchema->hasSequence($checkSequence)) {
$visitor->acceptSequence($fullSchema->getSequence($checkSequence));
}
}
return $visitor->getQueries();
}
/**
* Returns a SchemaDiff object containing the differences between the schemas $fromSchema and $toSchema.
*
* The returned differences are returned in such a way that they contain the
* operations to change the schema stored in $fromSchema to the schema that is
* stored in $toSchema.
*
* @param \Doctrine\DBAL\Schema\Schema $fromSchema
* @param \Doctrine\DBAL\Schema\Schema $toSchema
*
* @return \Doctrine\DBAL\Schema\SchemaDiff
*/
public function compare(Schema $fromSchema, Schema $toSchema)
{
$diff = new SchemaDiff();
$diff->fromSchema = $fromSchema;
$foreignKeysToTable = array();
foreach ($toSchema->getNamespaces() as $namespace) {
if (!$fromSchema->hasNamespace($namespace)) {
$diff->newNamespaces[$namespace] = $namespace;
}
}
foreach ($fromSchema->getNamespaces() as $namespace) {
if (!$toSchema->hasNamespace($namespace)) {
$diff->removedNamespaces[$namespace] = $namespace;
}
}
foreach ($toSchema->getTables() as $table) {
$tableName = $table->getShortestName($toSchema->getName());
if (!$fromSchema->hasTable($tableName)) {
$diff->newTables[$tableName] = $toSchema->getTable($tableName);
} else {
$tableDifferences = $this->diffTable($fromSchema->getTable($tableName), $toSchema->getTable($tableName));
if ($tableDifferences !== false) {
$diff->changedTables[$tableName] = $tableDifferences;
}
}
}
/* Check if there are tables removed */
foreach ($fromSchema->getTables() as $table) {
$tableName = $table->getShortestName($fromSchema->getName());
$table = $fromSchema->getTable($tableName);
if (!$toSchema->hasTable($tableName)) {
$diff->removedTables[$tableName] = $table;
}
// also remember all foreign keys that point to a specific table
foreach ($table->getForeignKeys() as $foreignKey) {
$foreignTable = strtolower($foreignKey->getForeignTableName());
if (!isset($foreignKeysToTable[$foreignTable])) {
$foreignKeysToTable[$foreignTable] = array();
}
$foreignKeysToTable[$foreignTable][] = $foreignKey;
}
}
foreach ($diff->removedTables as $tableName => $table) {
if (isset($foreignKeysToTable[$tableName])) {
$diff->orphanedForeignKeys = array_merge($diff->orphanedForeignKeys, $foreignKeysToTable[$tableName]);
// deleting duplicated foreign keys present on both on the orphanedForeignKey
// and the removedForeignKeys from changedTables
foreach ($foreignKeysToTable[$tableName] as $foreignKey) {
// strtolower the table name to make if compatible with getShortestName
$localTableName = strtolower($foreignKey->getLocalTableName());
if (isset($diff->changedTables[$localTableName])) {
foreach ($diff->changedTables[$localTableName]->removedForeignKeys as $key => $removedForeignKey) {
# BUG-1481
if ($removedForeignKey->getLocalTableName() == $tableName) {
unset($diff->changedTables[$localTableName]->removedForeignKeys[$key]);
}
}
}
}
}
}
foreach ($toSchema->getSequences() as $sequence) {
$sequenceName = $sequence->getShortestName($toSchema->getName());
if (!$fromSchema->hasSequence($sequenceName)) {
if (!$this->isAutoIncrementSequenceInSchema($fromSchema, $sequence)) {
$diff->newSequences[] = $sequence;
}
} else {
if ($this->diffSequence($sequence, $fromSchema->getSequence($sequenceName))) {
$diff->changedSequences[] = $toSchema->getSequence($sequenceName);
}
}
}
foreach ($fromSchema->getSequences() as $sequence) {
if ($this->isAutoIncrementSequenceInSchema($toSchema, $sequence)) {
continue;
}
$sequenceName = $sequence->getShortestName($fromSchema->getName());
if (!$toSchema->hasSequence($sequenceName)) {
$diff->removedSequences[] = $sequence;
}
}
return $diff;
}
/**
* @param \Doctrine\DBAL\Schema\Schema $schema
* @param \Doctrine\DBAL\Schema\Sequence $sequence
*
* @return boolean
*/
private function isAutoIncrementSequenceInSchema($schema, $sequence)
{
foreach ($schema->getTables() as $table) {
if ($sequence->isAutoIncrementsFor($table)) {
return true;
}
}
return false;
}
/**
* @param Schema $targetSchema
* @throws \OC\DB\MigrationException
*/
public function checkMigrate(Schema $targetSchema)
{
$this->noEmit = true;
/**@var \Doctrine\DBAL\Schema\Table[] $tables */
$tables = $targetSchema->getTables();
$filterExpression = $this->getFilterExpression();
$this->connection->getConfiguration()->setFilterSchemaAssetsExpression($filterExpression);
$existingTables = $this->connection->getSchemaManager()->listTableNames();
$step = 0;
foreach ($tables as $table) {
if (strpos($table->getName(), '.')) {
list(, $tableName) = explode('.', $table->getName());
} else {
$tableName = $table->getName();
}
$this->emitCheckStep($tableName, $step++, count($tables));
// don't need to check for new tables
if (array_search($tableName, $existingTables) !== false) {
$this->checkTableMigrate($table);
}
}
}
/**
* @param Schema $targetSchema
* @param \Doctrine\DBAL\Connection $connection
* @return \Doctrine\DBAL\Schema\SchemaDiff
* @throws DBALException
*/
protected function getDiff(Schema $targetSchema, \Doctrine\DBAL\Connection $connection)
{
// adjust varchar columns with a length higher then getVarcharMaxLength to clob
foreach ($targetSchema->getTables() as $table) {
foreach ($table->getColumns() as $column) {
if ($column->getType() instanceof StringType) {
if ($column->getLength() > $connection->getDatabasePlatform()->getVarcharMaxLength()) {
$column->setType(Type::getType('text'));
$column->setLength(null);
}
}
}
}
$filterExpression = $this->getFilterExpression();
$this->connection->getConfiguration()->setFilterSchemaAssetsExpression($filterExpression);
$sourceSchema = $connection->getSchemaManager()->createSchema();
// remove tables we don't know about
/** @var $table \Doctrine\DBAL\Schema\Table */
foreach ($sourceSchema->getTables() as $table) {
if (!$targetSchema->hasTable($table->getName())) {
$sourceSchema->dropTable($table->getName());
}
}
// remove sequences we don't know about
foreach ($sourceSchema->getSequences() as $table) {
if (!$targetSchema->hasSequence($table->getName())) {
$sourceSchema->dropSequence($table->getName());
}
}
$comparator = new Comparator();
return $comparator->compare($sourceSchema, $targetSchema);
}
