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);
}
| public static getForeignKeyHandlingSql ( Doctrine\DBAL\Schema\Schema $schema, Doctrine\DBAL\Platforms\AbstractPlatform $platform, array $tableNames, string $search, string $replace ) : array | ||
| $schema | Doctrine\DBAL\Schema\Schema | |
| $platform | Doctrine\DBAL\Platforms\AbstractPlatform | |
| $tableNames | array | |
| $search | string | |
| $replace | string | |
| return | array | |
/**
* @param Schema $schema
* @return void
*/
public function down(Schema $schema)
{
$this->abortIf($this->connection->getDatabasePlatform()->getName() != "mysql");
// collect foreign keys pointing to "our" tables
$tableNames = array('typo3_party_domain_model_abstractparty', 'typo3_party_domain_model_electronicaddress', 'typo3_party_domain_model_person', 'typo3_party_domain_model_personname');
$foreignKeyHandlingSql = Service::getForeignKeyHandlingSql($schema, $this->platform, $tableNames, 'persistence_object_identifier', 'flow3_persistence_identifier');
// drop FK constraints
foreach ($foreignKeyHandlingSql['drop'] as $sql) {
$this->addSql($sql);
}
// rename identifier fields
$this->addSql("ALTER TABLE typo3_party_domain_model_abstractparty DROP PRIMARY KEY");
$this->addSql("ALTER TABLE typo3_party_domain_model_abstractparty CHANGE persistence_object_identifier flow3_persistence_identifier VARCHAR(40) NOT NULL");
$this->addSql("ALTER TABLE typo3_party_domain_model_abstractparty ADD PRIMARY KEY (flow3_persistence_identifier)");
$this->addSql("ALTER TABLE typo3_party_domain_model_electronicaddress DROP PRIMARY KEY");
$this->addSql("ALTER TABLE typo3_party_domain_model_electronicaddress CHANGE persistence_object_identifier flow3_persistence_identifier VARCHAR(40) NOT NULL");
$this->addSql("ALTER TABLE typo3_party_domain_model_electronicaddress ADD PRIMARY KEY (flow3_persistence_identifier)");
$this->addSql("ALTER TABLE typo3_party_domain_model_person DROP PRIMARY KEY");
$this->addSql("ALTER TABLE typo3_party_domain_model_person CHANGE persistence_object_identifier flow3_persistence_identifier VARCHAR(40) NOT NULL");
$this->addSql("ALTER TABLE typo3_party_domain_model_person ADD PRIMARY KEY (flow3_persistence_identifier)");
$this->addSql("ALTER TABLE typo3_party_domain_model_personname DROP PRIMARY KEY");
$this->addSql("ALTER TABLE typo3_party_domain_model_personname CHANGE persistence_object_identifier flow3_persistence_identifier VARCHAR(40) NOT NULL");
$this->addSql("ALTER TABLE typo3_party_domain_model_personname ADD PRIMARY KEY (flow3_persistence_identifier)");
// add back FK constraints
foreach ($foreignKeyHandlingSql['add'] as $sql) {
$this->addSql($sql);
}
}
/**
* @param Schema $schema
* @return void
*/
public function down(Schema $schema)
{
$this->abortIf($this->connection->getDatabasePlatform()->getName() != "postgresql");
// collect foreign keys pointing to "our" tables
$tableNames = array('typo3_flow_resource_publishing_abstractpublishingconfiguration', 'typo3_flow_resource_resource', 'typo3_flow_security_account', 'typo3_flow_security_authorization_resource_securitypublis_861cb');
$foreignKeyHandlingSql = Service::getForeignKeyHandlingSql($schema, $this->platform, $tableNames, 'persistence_object_identifier', 'flow3_persistence_identifier');
// drop FK constraints
foreach ($foreignKeyHandlingSql['drop'] as $sql) {
$this->addSql($sql);
}
// rename identifier fields
$this->addSql("ALTER TABLE typo3_flow_resource_publishing_abstractpublishingconfiguration RENAME COLUMN persistence_object_identifier TO flow3_persistence_identifier");
$this->addSql("ALTER TABLE typo3_flow_resource_resource RENAME COLUMN persistence_object_identifier TO flow3_persistence_identifier");
$this->addSql("ALTER TABLE typo3_flow_security_account RENAME COLUMN persistence_object_identifier TO flow3_persistence_identifier");
$this->addSql("ALTER TABLE typo3_flow_security_authorization_resource_securitypublis_861cb RENAME COLUMN persistence_object_identifier TO flow3_persistence_identifier");
// add back FK constraints
foreach ($foreignKeyHandlingSql['add'] as $sql) {
$this->addSql($sql);
}
// rename tables
$this->addSql("ALTER TABLE typo3_flow_mvc_routing_objectpathmapping RENAME TO typo3_flow3_mvc_routing_objectpathmapping");
$this->addSql("ALTER TABLE typo3_flow_resource_publishing_abstractpublishingconfiguration RENAME TO typo3_flow3_resource_publishing_abstractpublishingconfiguration");
$this->addSql("ALTER TABLE typo3_flow_resource_resource RENAME TO typo3_flow3_resource_resource");
$this->addSql("ALTER TABLE typo3_flow_resource_resourcepointer RENAME TO typo3_flow3_resource_resourcepointer");
$this->addSql("ALTER TABLE typo3_flow_security_account RENAME TO typo3_flow3_security_account");
$this->addSql("ALTER TABLE typo3_flow_security_authorization_resource_securitypublis_861cb RENAME TO typo3_flow3_security_authorization_resource_securitypubli_6180a");
$this->addSql("ALTER TABLE typo3_flow_security_policy_role RENAME TO typo3_flow3_security_policy_role");
}
/**
* @param Schema $schema
* @return void
*/
public function down(Schema $schema)
{
$this->abortIf($this->connection->getDatabasePlatform()->getName() != "mysql");
// collect foreign keys pointing to "our" tables
$tableNames = array('typo3_typo3cr_migration_domain_model_migrationstatus', 'typo3_typo3cr_domain_model_contentobjectproxy', 'typo3_typo3cr_domain_model_node', 'typo3_typo3cr_domain_model_workspace');
$foreignKeyHandlingSql = Service::getForeignKeyHandlingSql($schema, $this->platform, $tableNames, 'persistence_object_identifier', 'flow3_persistence_identifier');
// drop FK constraints
foreach ($foreignKeyHandlingSql['drop'] as $sql) {
$this->addSql($sql);
}
// rename identifier fields
$this->addSql("ALTER TABLE typo3_typo3cr_domain_model_contentobjectproxy DROP PRIMARY KEY");
$this->addSql("ALTER TABLE typo3_typo3cr_domain_model_contentobjectproxy CHANGE persistence_object_identifier flow3_persistence_identifier VARCHAR(40) NOT NULL");
$this->addSql("ALTER TABLE typo3_typo3cr_domain_model_contentobjectproxy ADD PRIMARY KEY (flow3_persistence_identifier)");
$this->addSql("ALTER TABLE typo3_typo3cr_domain_model_node DROP PRIMARY KEY");
$this->addSql("ALTER TABLE typo3_typo3cr_domain_model_node CHANGE persistence_object_identifier flow3_persistence_identifier VARCHAR(40) NOT NULL");
$this->addSql("ALTER TABLE typo3_typo3cr_domain_model_node ADD PRIMARY KEY (flow3_persistence_identifier)");
$this->addSql("ALTER TABLE typo3_typo3cr_domain_model_workspace DROP PRIMARY KEY");
$this->addSql("ALTER TABLE typo3_typo3cr_domain_model_workspace CHANGE persistence_object_identifier flow3_persistence_identifier VARCHAR(40) NOT NULL");
$this->addSql("ALTER TABLE typo3_typo3cr_domain_model_workspace ADD PRIMARY KEY (flow3_persistence_identifier)");
$this->addSql("ALTER TABLE typo3_typo3cr_migration_domain_model_migrationstatus DROP PRIMARY KEY");
$this->addSql("ALTER TABLE typo3_typo3cr_migration_domain_model_migrationstatus CHANGE persistence_object_identifier flow3_persistence_identifier VARCHAR(40) NOT NULL");
$this->addSql("ALTER TABLE typo3_typo3cr_migration_domain_model_migrationstatus ADD PRIMARY KEY (flow3_persistence_identifier)");
// add back FK constraints
foreach ($foreignKeyHandlingSql['add'] as $sql) {
$this->addSql($sql);
}
}
/**
* @param Schema $schema
* @return void
*/
public function down(Schema $schema)
{
$this->abortIf($this->connection->getDatabasePlatform()->getName() != "postgresql");
// collect foreign keys pointing to "our" tables
$foreignKeyHandlingSql = Service::getForeignKeyHandlingSql($schema, $this->platform, array('typo3_media_domain_model_image'), 'persistence_object_identifier', 'flow3_persistence_identifier');
// drop FK constraints
foreach ($foreignKeyHandlingSql['drop'] as $sql) {
$this->addSql($sql);
}
// rename identifier fields
$this->addSql("ALTER TABLE typo3_media_domain_model_image RENAME COLUMN persistence_object_identifier TO flow3_persistence_identifier");
// add back FK constraints
foreach ($foreignKeyHandlingSql['add'] as $sql) {
$this->addSql($sql);
}
}
/**
* @param Schema $schema
* @return void
*/
public function down(Schema $schema)
{
$this->abortIf($this->connection->getDatabasePlatform()->getName() != "postgresql");
// collect foreign keys pointing to "our" tables
$tableNames = array('typo3_typo3cr_migration_domain_model_migrationstatus', 'typo3_typo3cr_domain_model_contentobjectproxy', 'typo3_typo3cr_domain_model_node', 'typo3_typo3cr_domain_model_workspace');
$foreignKeyHandlingSql = Service::getForeignKeyHandlingSql($schema, $this->platform, $tableNames, 'persistence_object_identifier', 'flow3_persistence_identifier');
// drop FK constraints
foreach ($foreignKeyHandlingSql['drop'] as $sql) {
$this->addSql($sql);
}
// rename identifier fields
$this->addSql("ALTER TABLE typo3_typo3cr_domain_model_contentobjectproxy RENAME COLUMN persistence_object_identifier TO flow3_persistence_identifier");
$this->addSql("ALTER TABLE typo3_typo3cr_domain_model_node RENAME COLUMN persistence_object_identifier TO flow3_persistence_identifier");
$this->addSql("ALTER TABLE typo3_typo3cr_domain_model_workspace RENAME COLUMN persistence_object_identifier TO flow3_persistence_identifier");
$this->addSql("ALTER TABLE typo3_typo3cr_migration_domain_model_migrationstatus RENAME COLUMN persistence_object_identifier TO flow3_persistence_identifier");
// add back FK constraints
foreach ($foreignKeyHandlingSql['add'] as $sql) {
$this->addSql($sql);
}
}
/**
* @param Schema $schema
* @return void
*/
public function down(Schema $schema)
{
$this->abortIf($this->connection->getDatabasePlatform()->getName() != "mysql");
// collect foreign keys pointing to "our" tables
$tableNames = array('typo3_flow_resource_publishing_abstractpublishingconfiguration', 'typo3_flow_resource_resource', 'typo3_flow_security_account', 'typo3_flow_security_authorization_resource_securitypublis_861cb');
$foreignKeyHandlingSql = \Neos\Flow\Persistence\Doctrine\Service::getForeignKeyHandlingSql($schema, $this->platform, $tableNames, 'persistence_object_identifier', 'flow3_persistence_identifier');
// drop FK constraints
foreach ($foreignKeyHandlingSql['drop'] as $sql) {
$this->addSql($sql);
}
// rename identifier fields
$this->addSql("ALTER TABLE typo3_flow_resource_publishing_abstractpublishingconfiguration DROP PRIMARY KEY");
$this->addSql("ALTER TABLE typo3_flow_resource_publishing_abstractpublishingconfiguration CHANGE persistence_object_identifier flow3_persistence_identifier VARCHAR(40) NOT NULL");
$this->addSql("ALTER TABLE typo3_flow_resource_publishing_abstractpublishingconfiguration ADD PRIMARY KEY (flow3_persistence_identifier)");
$this->addSql("ALTER TABLE typo3_flow_resource_resource DROP PRIMARY KEY");
$this->addSql("ALTER TABLE typo3_flow_resource_resource CHANGE persistence_object_identifier flow3_persistence_identifier VARCHAR(40) NOT NULL");
$this->addSql("ALTER TABLE typo3_flow_resource_resource ADD PRIMARY KEY (flow3_persistence_identifier)");
$this->addSql("ALTER TABLE typo3_flow_security_account DROP PRIMARY KEY");
$this->addSql("ALTER TABLE typo3_flow_security_account CHANGE persistence_object_identifier flow3_persistence_identifier VARCHAR(40) NOT NULL");
$this->addSql("ALTER TABLE typo3_flow_security_account ADD PRIMARY KEY (flow3_persistence_identifier)");
$this->addSql("ALTER TABLE typo3_flow_security_authorization_resource_securitypublis_861cb DROP PRIMARY KEY");
$this->addSql("ALTER TABLE typo3_flow_security_authorization_resource_securitypublis_861cb CHANGE persistence_object_identifier flow3_persistence_identifier VARCHAR(40) NOT NULL");
$this->addSql("ALTER TABLE typo3_flow_security_authorization_resource_securitypublis_861cb ADD PRIMARY KEY (flow3_persistence_identifier)");
// add back FK constraints
foreach ($foreignKeyHandlingSql['add'] as $sql) {
$this->addSql($sql);
}
// rename tables
$this->addSql("RENAME TABLE typo3_flow_mvc_routing_objectpathmapping TO typo3_flow3_mvc_routing_objectpathmapping");
$this->addSql("RENAME TABLE typo3_flow_resource_publishing_abstractpublishingconfiguration TO typo3_flow3_resource_publishing_abstractpublishingconfiguration");
$this->addSql("RENAME TABLE typo3_flow_resource_resource TO typo3_flow3_resource_resource");
$this->addSql("RENAME TABLE typo3_flow_resource_resourcepointer TO typo3_flow3_resource_resourcepointer");
$this->addSql("RENAME TABLE typo3_flow_security_account TO typo3_flow3_security_account");
$this->addSql("RENAME TABLE typo3_flow_security_authorization_resource_securitypublis_861cb TO typo3_flow3_security_authorization_resource_securitypubli_6180a");
$this->addSql("RENAME TABLE typo3_flow_security_policy_role TO typo3_flow3_security_policy_role");
}
