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); }