This document is for an in-development version of Galaxy. You can alternatively view this page in the latest release if it exists or view the top of the latest release's documentation.

Source code for galaxy.model.migrate.versions.0068_rename_sequencer_to_external_services

This migration script renames the sequencer table to 'external_service' table and
creates a association table, 'request_type_external_service_association' and
populates it. The 'sequencer_id' foreign_key from the 'request_type' table is removed.
The 'sequencer_type_id' column is renamed to 'external_service_type_id' in the renamed
table 'external_service'. Finally, adds a foreign key to the external_service table in the
sample_dataset table and populates it.
from __future__ import print_function

import datetime
import logging

from sqlalchemy import (

from galaxy.model.custom_types import TrimmedString
from galaxy.model.migrate.versions.util import (

now = datetime.datetime.utcnow
log = logging.getLogger(__name__)
metadata = MetaData()

[docs]def upgrade(migrate_engine): print(__doc__) metadata.bind = migrate_engine metadata.reflect() # rename 'sequencer' table to 'external_service' Sequencer_table = Table("sequencer", metadata, autoload=True) Sequencer_table.rename('external_service') # if running PostgreSQL, rename the primary key sequence too if migrate_engine.name in ['postgres', 'postgresql']: cmd = "ALTER SEQUENCE sequencer_id_seq RENAME TO external_service_id_seq" migrate_engine.execute(cmd) # Add 'external_services_id' column to 'sample_dataset' table SampleDataset_table = Table("sample_dataset", metadata, autoload=True) col = Column("external_service_id", Integer, ForeignKey("external_service.id", name='sample_dataset_external_services_id_fk'), index=True) add_column(col, SampleDataset_table, metadata, index_name="ix_sample_dataset_external_service_id") # populate the column cmd = "SELECT sample_dataset.id, request_type.sequencer_id " \ + " FROM sample_dataset, sample, request, request_type " \ + " WHERE sample.id=sample_dataset.sample_id and request.id=sample.request_id and request.request_type_id=request_type.id " \ + " ORDER BY sample_dataset.id" try: result = migrate_engine.execute(cmd) for r in result: sample_dataset_id = int(r[0]) sequencer_id = int(r[1]) cmd = "UPDATE sample_dataset SET external_service_id='%i' where id=%i" % (sequencer_id, sample_dataset_id) migrate_engine.execute(cmd) except Exception: log.exception("Exception executing SQL command: %s", cmd) # rename 'sequencer_type_id' column to 'external_service_type_id' in the table 'external_service' # create the column as 'external_service_type_id' ExternalServices_table = Table("external_service", metadata, autoload=True) col = Column("external_service_type_id", TrimmedString(255)) add_column(col, ExternalServices_table, metadata) # populate this new column cmd = "UPDATE external_service SET external_service_type_id=sequencer_type_id" migrate_engine.execute(cmd) # remove the 'sequencer_type_id' column drop_column('sequencer_type_id', ExternalServices_table) # create 'request_type_external_service_association' table RequestTypeExternalServiceAssociation_table = Table("request_type_external_service_association", metadata, Column("id", Integer, primary_key=True), Column("request_type_id", Integer, ForeignKey("request_type.id"), index=True), Column("external_service_id", Integer, ForeignKey("external_service.id"), index=True)) create_table(RequestTypeExternalServiceAssociation_table) # populate 'request_type_external_service_association' table cmd = "SELECT id, sequencer_id FROM request_type ORDER BY id ASC" result = migrate_engine.execute(cmd) results_list = result.fetchall() # Proceed only if request_types exists for row in results_list: request_type_id = row[0] sequencer_id = row[1] if not sequencer_id: sequencer_id = 'null' cmd = "INSERT INTO request_type_external_service_association VALUES ( %s, %s, %s )" % ( nextval(migrate_engine, 'request_type_external_service_association'), request_type_id, sequencer_id) migrate_engine.execute(cmd) # TODO: Dropping a column used in a foreign key fails in MySQL, need to remove the FK first. drop_column('sequencer_id', 'request_type', metadata)
[docs]def downgrade(migrate_engine): metadata.bind = migrate_engine metadata.reflect() # SQLite does not always update foreign key constraints when the target # table is renamed, so we start with the table rename. # rename the 'external_service' table to 'sequencer' ExternalServices_table = Table("external_service", metadata, autoload=True) ExternalServices_table.rename('sequencer') # if running PostgreSQL, rename the primary key sequence too if migrate_engine.name in ['postgres', 'postgresql']: cmd = "ALTER SEQUENCE external_service_id_seq RENAME TO sequencer_id_seq" migrate_engine.execute(cmd) # create the 'sequencer_id' column in the 'request_type' table col = Column("sequencer_id", Integer, ForeignKey("sequencer.id"), nullable=True) add_column(col, 'request_type', metadata) # populate 'sequencer_id' column in the 'request_type' table from the # 'request_type_external_service_association' table cmd = "SELECT request_type_id, external_service_id FROM request_type_external_service_association ORDER BY id ASC" result = migrate_engine.execute(cmd) results_list = result.fetchall() for row in results_list: request_type_id = row[0] external_service_id = row[1] cmd = "UPDATE request_type SET sequencer_id=%i WHERE id=%i" % (external_service_id, request_type_id) migrate_engine.execute(cmd) # remove the 'request_type_external_service_association' table RequestTypeExternalServiceAssociation_table = Table("request_type_external_service_association", metadata, autoload=True) drop_table(RequestTypeExternalServiceAssociation_table) # rename 'external_service_type_id' column to 'sequencer_type_id' in the table 'sequencer' # create the column 'sequencer_type_id' Sequencer_table = Table("sequencer", metadata, autoload=True) col = Column("sequencer_type_id", TrimmedString(255)) # should also have nullable=False add_column(col, Sequencer_table, metadata) # populate this new column cmd = "UPDATE sequencer SET sequencer_type_id=external_service_type_id" migrate_engine.execute(cmd) # remove the 'external_service_type_id' column drop_column('external_service_type_id', Sequencer_table) # drop the 'external_service_id' column in the 'sample_dataset' table drop_column('external_service_id', 'sample_dataset', metadata)