def upgrade(migrate_engine): print(__doc__) metadata.bind = migrate_engine metadata.reflect() for table in tables: create_table(table)
def downgrade(migrate_engine): metadata.bind = migrate_engine NewWorkflowStepConnection_table = Table("workflow_step_connection", metadata, autoload=True) for index in NewWorkflowStepConnection_table.indexes: index.drop() NewWorkflowStepConnection_table.rename("workflow_step_connection_predowngrade145") # Try to deregister that table to work around some caching problems it seems. NewWorkflowStepConnection_table.deregister() metadata._remove_table("workflow_step_connection", metadata.schema) metadata.reflect() OldWorkflowStepConnection_table = Table( "workflow_step_connection", metadata, Column("id", Integer, primary_key=True), Column("output_step_id", Integer, ForeignKey("workflow_step.id"), index=True), Column("input_step_id", Integer, ForeignKey("workflow_step.id"), index=True), Column("output_name", TEXT), Column("input_name", TEXT), Column("input_subworkflow_step_id", Integer, ForeignKey("workflow_step.id"), index=True), ) create_table(OldWorkflowStepConnection_table) insert_step_connections_cmd = \ "INSERT INTO workflow_step_connection (output_step_id, input_step_id, output_name, input_name, input_subworkflow_step_id) " + \ "SELECT wsc.output_step_id, wsi.workflow_step_id, wsc.output_name, wsi.name, wsc.input_subworkflow_step_id " + \ "FROM workflow_step_connection_predowngrade145 AS wsc JOIN workflow_step_input AS wsi ON wsc.input_step_input_id = wsi.id ORDER BY wsc.id" migrate_engine.execute(insert_step_connections_cmd) for table in (NewWorkflowStepConnection_table, WorkflowStepInput_table): drop_table(table)
def upgrade(migrate_engine): print(__doc__) metadata.bind = migrate_engine metadata.reflect() for table in TABLES: create_table(table) History_column = Column("history_id", Integer, ForeignKey("history.id"), nullable=True) State_column = Column("state", TrimmedString(64)) # TODO: Handle indexes correctly SchedulerId_column = Column("scheduler", TrimmedString(255)) HandlerId_column = Column("handler", TrimmedString(255)) WorkflowUUID_column = Column("uuid", UUIDType, nullable=True) add_column(History_column, "workflow_invocation", metadata) add_column(State_column, "workflow_invocation", metadata) add_column(SchedulerId_column, "workflow_invocation", metadata, index_nane="id_workflow_invocation_scheduler") add_column(HandlerId_column, "workflow_invocation", metadata, index_name="id_workflow_invocation_handler") add_column(WorkflowUUID_column, "workflow_invocation", metadata) # All previous invocations have been scheduled... cmd = "UPDATE workflow_invocation SET state = 'scheduled'" try: migrate_engine.execute(cmd) except Exception: log.exception("failed to update past workflow invocation states.") WorkflowInvocationStepAction_column = Column("action", JSONType, nullable=True) add_column(WorkflowInvocationStepAction_column, "workflow_invocation_step", metadata)
def upgrade(migrate_engine): print(__doc__) metadata.bind = migrate_engine metadata.reflect() # 1) Drop for table_name in ["workflow_invocation_step", "workflow_invocation"]: t = Table(table_name, metadata, autoload=True) drop_table(t) metadata.remove(t) # 2) Re-add WorkflowInvocation_table = Table("workflow_invocation", metadata, Column("id", Integer, primary_key=True), Column("create_time", DateTime, default=now), Column("update_time", DateTime, default=now, onupdate=now), Column("workflow_id", Integer, ForeignKey("workflow.id"), index=True, nullable=False)) WorkflowInvocationStep_table = Table("workflow_invocation_step", metadata, Column("id", Integer, primary_key=True), Column("create_time", DateTime, default=now), Column("update_time", DateTime, default=now, onupdate=now), Column("workflow_invocation_id", Integer, ForeignKey("workflow_invocation.id"), index=True, nullable=False), Column("workflow_step_id", Integer, ForeignKey("workflow_step.id"), index=True, nullable=False), Column("job_id", Integer, ForeignKey("job.id"), index=True, nullable=True)) for table in [WorkflowInvocation_table, WorkflowInvocationStep_table]: create_table(table)
def upgrade(migrate_engine): print(__doc__) metadata.bind = migrate_engine metadata.reflect() if migrate_engine.name in ['postgres', 'postgresql']: subworkflow_id_column = Column("subworkflow_id", Integer, ForeignKey("workflow.id"), nullable=True) input_subworkflow_step_id_column = Column("input_subworkflow_step_id", Integer, ForeignKey("workflow_step.id"), nullable=True) parent_workflow_id_column = Column("parent_workflow_id", Integer, ForeignKey("workflow.id"), nullable=True) else: subworkflow_id_column = Column("subworkflow_id", Integer, nullable=True) input_subworkflow_step_id_column = Column("input_subworkflow_step_id", Integer, nullable=True) parent_workflow_id_column = Column("parent_workflow_id", Integer, nullable=True) add_column(subworkflow_id_column, "workflow_step", metadata) add_column(input_subworkflow_step_id_column, "workflow_step_connection", metadata) add_column(parent_workflow_id_column, "workflow", metadata) workflow_output_label_column = Column("label", TrimmedString(255)) workflow_output_uuid_column = Column("uuid", UUIDType, nullable=True) add_column(workflow_output_label_column, "workflow_output", metadata) add_column(workflow_output_uuid_column, "workflow_output", metadata) # Make stored_workflow_id nullable, since now workflows can belong to either # a stored workflow or a parent workflow. alter_column("stored_workflow_id", "workflow", metadata, nullable=True) for table in TABLES: # Indexes are automatically created when the tables are. create_table(table)
def upgrade(migrate_engine): print(__doc__) metadata.bind = migrate_engine metadata.reflect() for table in TABLES: create_table(table) # Add columns & create indices for supporting sharing to visualization table. Visualization_table = Table("visualization", metadata, autoload=True) deleted_column = Column("deleted", Boolean, default=False, index=True) add_column(deleted_column, Visualization_table, metadata, index_name="ix_visualization_deleted") try: # Fill column with default value. cmd = "UPDATE visualization SET deleted = %s" % engine_false( migrate_engine) migrate_engine.execute(cmd) except Exception: log.exception( "Updating column 'deleted' of table 'visualization' failed.") importable_column = Column("importable", Boolean, default=False, index=True) add_column(importable_column, Visualization_table, metadata, index_name='ix_visualization_importable') try: # Fill column with default value. cmd = "UPDATE visualization SET importable = %s" % engine_false( migrate_engine) migrate_engine.execute(cmd) except Exception: log.exception( "Updating column 'importable' of table 'visualization' failed.") slug_column = Column("slug", TEXT) add_column(slug_column, Visualization_table, metadata) # Index needs to be added separately because MySQL cannot index a TEXT/BLOB # column without specifying mysql_length add_index('ix_visualization_slug', Visualization_table, 'slug') published_column = Column("published", Boolean, index=True) add_column(published_column, Visualization_table, metadata, index_name='ix_visualization_published') try: # Fill column with default value. cmd = "UPDATE visualization SET published = %s" % engine_false( migrate_engine) migrate_engine.execute(cmd) except Exception: log.exception( "Updating column 'published' of table 'visualization' failed.")
def upgrade(migrate_engine): print(__doc__) metadata.bind = migrate_engine metadata.reflect() try: if migrate_engine.name == 'mysql': # Strip slug index prior to creation so we can do it manually. slug_index = None for ix in Page_table.indexes: if ix.name == 'ix_page_slug': slug_index = ix Page_table.indexes.remove(slug_index) Page_table.create() if migrate_engine.name == 'mysql': # Create slug index manually afterward. i = Index("ix_page_slug", Page_table.c.slug, mysql_length=200) i.create() except Exception: log.exception("Could not create page table") create_table(PageRevision_table) col = Column('username', String(255), index=True, unique=True, default=False) add_column(col, 'galaxy_user', metadata, index_name='ix_galaxy_user_username', unique_name='username')
def upgrade(migrate_engine): print(__doc__) metadata.bind = migrate_engine metadata.reflect() create_table(RepositoryDependency_table) create_table(RepositoryRepositoryDependencyAssociation_table)
def upgrade(migrate_engine): print(__doc__) metadata.bind = migrate_engine metadata.reflect() for table in TABLES: create_table(table) if migrate_engine.name in ['postgres', 'postgresql']: workflow_dynamic_tool_id_column = Column("dynamic_tool_id", Integer, ForeignKey("dynamic_tool.id"), nullable=True) job_workflow_dynamic_tool_id_column = Column( "dynamic_tool_id", Integer, ForeignKey("dynamic_tool.id"), nullable=True) else: workflow_dynamic_tool_id_column = Column("dynamic_tool_id", Integer, nullable=True) job_workflow_dynamic_tool_id_column = Column("dynamic_tool_id", Integer, nullable=True) add_column(workflow_dynamic_tool_id_column, "workflow_step", metadata) add_column(job_workflow_dynamic_tool_id_column, "job", metadata)
def upgrade(migrate_engine): print(__doc__) metadata.bind = migrate_engine metadata.reflect() for table in get_new_tables(): create_table(table) # Set default for creation to scheduled, actual mapping has new as default. workflow_invocation_step_state_column = Column("state", TrimmedString(64), default="scheduled") if migrate_engine.name in ['postgres', 'postgresql']: implicit_collection_jobs_id_column = Column("implicit_collection_jobs_id", Integer, ForeignKey("implicit_collection_jobs.id"), nullable=True) job_id_column = Column("job_id", Integer, ForeignKey("job.id"), nullable=True) else: implicit_collection_jobs_id_column = Column("implicit_collection_jobs_id", Integer, nullable=True) job_id_column = Column("job_id", Integer, nullable=True) dataset_collection_element_count_column = Column("element_count", Integer, nullable=True) add_column(implicit_collection_jobs_id_column, "history_dataset_collection_association", metadata) add_column(job_id_column, "history_dataset_collection_association", metadata) add_column(dataset_collection_element_count_column, "dataset_collection", metadata) implicit_collection_jobs_id_column = Column("implicit_collection_jobs_id", Integer, ForeignKey("implicit_collection_jobs.id"), nullable=True) add_column(implicit_collection_jobs_id_column, "workflow_invocation_step", metadata) add_column(workflow_invocation_step_state_column, "workflow_invocation_step", metadata) cmd = \ "UPDATE dataset_collection SET element_count = " + \ "(SELECT (CASE WHEN count(*) > 0 THEN count(*) ELSE 0 END) FROM dataset_collection_element WHERE " + \ "dataset_collection_element.dataset_collection_id = dataset_collection.id)" migrate_engine.execute(cmd)
def upgrade(migrate_engine): print(__doc__) metadata.bind = migrate_engine metadata.reflect() create_table(HistoryUserShareAssociation_table) col = Column('importable', Boolean, index=True, default=False) add_column(col, 'history', metadata, index_name='ix_history_importable')
def downgrade(migrate_engine): metadata.bind = migrate_engine metadata.reflect() try: create_table(TransferJob_table) add_column(transfer_job_id, 'genome_index_tool_data', metadata, index_name='ix_genome_index_tool_data_transfer_job_id') except Exception: log.exception("Creating transfer_job table failed")
def upgrade(migrate_engine): print(__doc__) metadata.bind = migrate_engine metadata.reflect() create_table(Page_table) create_table(PageRevision_table) col = Column('username', String(255), index=True, unique=True, default=False) add_column(col, 'galaxy_user', metadata, index_name='ix_galaxy_user_username', unique_name='username')
def upgrade(migrate_engine): print(__doc__) metadata.bind = migrate_engine metadata.reflect() create_table(MigrateTools_table) try: cmd = "INSERT INTO migrate_tools VALUES ('GalaxyTools', 'lib/galaxy/tool_shed/migrate', %d)" % 1 migrate_engine.execute(cmd) except Exception: log.exception("Inserting into table 'migrate_tools' failed.")
def upgrade(migrate_engine): print(__doc__) metadata.bind = migrate_engine metadata.reflect() for table in TABLES: create_table(table) # TODO: Find a better name for this column... HiddenBeneathCollection_column = Column("hidden_beneath_collection_instance_id", Integer, ForeignKey("history_dataset_collection_association.id"), nullable=True) add_column(HiddenBeneathCollection_column, 'history_dataset_association', metadata)
def upgrade(migrate_engine): print(__doc__) metadata.bind = migrate_engine metadata.reflect() # Drop all of the original library_item_info tables # NOTE: all existing library item into template data is eliminated here via table drops for table_name in OLD_TABLE_NAMES: drop_table(table_name, metadata) # Create all new tables above for table in NEW_TABLES: create_table(table)
def upgrade(migrate_engine): print(__doc__) metadata.bind = migrate_engine metadata.reflect() for table in TABLES: create_table(table) extended_metadata_ldda_col = Column("extended_metadata_id", Integer, ForeignKey("extended_metadata.id"), nullable=True) add_column(extended_metadata_ldda_col, 'library_dataset_dataset_association', metadata)
def upgrade(migrate_engine): print(__doc__) metadata.bind = migrate_engine metadata.reflect() create_table(HistoryRatingAssociation_table) # Create history_dataset_association_rating_association table. try: HistoryDatasetAssociationRatingAssociation_table.create() except Exception as e: # MySQL cannot handle long index names; when we see this error, create the index name manually. if migrate_engine.name == 'mysql' and \ str(e).lower().find("identifier name 'ix_history_dataset_association_rating_association_history_dataset_association_id' is too long"): add_index('ix_hda_rating_association_hda_id', HistoryDatasetAssociationRatingAssociation_table, 'history_dataset_association_id') else: log.exception( "Creating history_dataset_association_rating_association table failed." ) create_table(StoredWorkflowRatingAssociation_table) create_table(PageRatingAssociation_table) create_table(VisualizationRatingAssociation_table)
def upgrade(migrate_engine): print(__doc__) metadata.bind = migrate_engine metadata.reflect() # Create the job_to_output_library_dataset table create_table(JobToOutputLibraryDataset_table) # Create the library_folder_id column col = Column("library_folder_id", Integer, ForeignKey('library_folder.id', name='job_library_folder_id_fk'), index=True) add_column(col, 'job', metadata, index_name='ix_job_library_folder_id') # Create the ix_dataset_state index add_index('ix_dataset_state', 'dataset', 'state', metadata)
def upgrade(migrate_engine): print(__doc__) metadata.bind = migrate_engine metadata.reflect() create_table(JobToImplicitOutputDatasetCollectionAssociation_table) dataset_collection_table = Table("dataset_collection", metadata, autoload=True) # need server_default because column in non-null populated_state_column = Column('populated_state', TrimmedString(64), default='ok', server_default="ok", nullable=False) add_column(populated_state_column, dataset_collection_table, metadata) populated_message_column = Column('populated_state_message', TEXT, nullable=True) add_column(populated_message_column, dataset_collection_table, metadata)
def upgrade(migrate_engine): print(__doc__) metadata.bind = migrate_engine metadata.reflect() for table in TABLES: create_table(table) try: hda_table = Table("history_dataset_association", metadata, autoload=True) HiddenBeneathCollection_column.create(hda_table) except Exception: log.exception("Creating HDA column failed.")
def upgrade(migrate_engine): print(__doc__) metadata.bind = migrate_engine metadata.reflect() create_table(Tag_table) create_table(HistoryTagAssociation_table) create_table(DatasetTagAssociation_table) create_table(HistoryDatasetAssociationTagAssociation_table)
def upgrade(migrate_engine): print(__doc__) metadata.bind = migrate_engine metadata.reflect() # Create user_id column in history_tag_association table. # SQLAlchemy Migrate has a bug when adding a column with both a ForeignKey and a index in SQLite if migrate_engine.name != 'sqlite': c = Column("user_id", Integer, ForeignKey("galaxy_user.id"), index=True) else: c = Column("user_id", Integer, index=True) add_column(c, 'history_tag_association', metadata, index_name='ix_history_tag_association_user_id') # Populate column so that user_id is the id of the user who owns the history (and, up to now, was the only person able to tag the history). migrate_engine.execute( "UPDATE history_tag_association SET user_id=( SELECT user_id FROM history WHERE history_tag_association.history_id = history.id )") # Create user_id column in history_dataset_association_tag_association table. # SQLAlchemy Migrate has a bug when adding a column with both a ForeignKey and a index in SQLite if migrate_engine.name != 'sqlite': c = Column("user_id", Integer, ForeignKey("galaxy_user.id"), index=True) else: c = Column("user_id", Integer, index=True) add_column(c, 'history_dataset_association_tag_association', metadata, index_name='ix_history_dataset_association_tag_association_user_id') # Populate column so that user_id is the id of the user who owns the history_dataset_association (and, up to now, was the only person able to tag the page). migrate_engine.execute( "UPDATE history_dataset_association_tag_association SET user_id=( SELECT history.user_id FROM history, history_dataset_association WHERE history_dataset_association.history_id = history.id AND history_dataset_association.id = history_dataset_association_tag_association.history_dataset_association_id)") # Create user_id column in page_tag_association table. # SQLAlchemy Migrate has a bug when adding a column with both a ForeignKey and a index in SQLite if migrate_engine.name != 'sqlite': c = Column("user_id", Integer, ForeignKey("galaxy_user.id"), index=True) else: # Create user_id column in page_tag_association table. c = Column("user_id", Integer, index=True) add_column(c, 'page_tag_association', metadata, index_name='ix_page_tag_association_user_id') # Populate column so that user_id is the id of the user who owns the page (and, up to now, was the only person able to tag the page). migrate_engine.execute( "UPDATE page_tag_association SET user_id=( SELECT user_id FROM page WHERE page_tag_association.page_id = page.id )") # Create stored_workflow_tag_association table. create_table(StoredWorkflowTagAssociation_table) # Create workflow_tag_association table. create_table(WorkflowTagAssociation_table)
def upgrade(migrate_engine): print(__doc__) metadata.bind = migrate_engine metadata.reflect() create_table(DynamicTool_table) workflow_dynamic_tool_id_column = Column("dynamic_tool_id", Integer, ForeignKey("dynamic_tool.id"), nullable=True) add_column(workflow_dynamic_tool_id_column, "workflow_step", metadata) job_workflow_dynamic_tool_id_column = Column("dynamic_tool_id", Integer, ForeignKey("dynamic_tool.id"), nullable=True) add_column(job_workflow_dynamic_tool_id_column, "job", metadata)
def downgrade(migrate_engine): metadata.bind = migrate_engine metadata.reflect() create_table(validation_error_table) history_dataset_association_table = Table("history_dataset_association", metadata, autoload=True) library_dataset_dataset_association_table = Table( "library_dataset_dataset_association", metadata, autoload=True) for dataset_instance_table in [ history_dataset_association_table, library_dataset_dataset_association_table ]: drop_column('validated_state', dataset_instance_table, metadata) drop_column('validated_state_message', dataset_instance_table, metadata)
def upgrade(migrate_engine): print(__doc__) metadata.bind = migrate_engine metadata.reflect() OldWorkflowStepConnection_table = Table("workflow_step_connection", metadata, autoload=True) for fkc in OldWorkflowStepConnection_table.foreign_key_constraints: mfkc = MigrateForeignKeyConstraint([_.parent for _ in fkc.elements], [_.column for _ in fkc.elements], name=fkc.name) try: mfkc.drop() except Exception: log.exception("Dropping foreign key constraint '%s' from table '%s' failed", mfkc.name, OldWorkflowStepConnection_table) for index in OldWorkflowStepConnection_table.indexes: drop_index(index, OldWorkflowStepConnection_table) OldWorkflowStepConnection_table.rename("workflow_step_connection_preupgrade145") # Try to deregister that table to work around some caching problems it seems. OldWorkflowStepConnection_table.deregister() metadata._remove_table("workflow_step_connection", metadata.schema) metadata.reflect() NewWorkflowStepConnection_table = Table( "workflow_step_connection", metadata, Column("id", Integer, primary_key=True), Column("output_step_id", Integer, ForeignKey("workflow_step.id"), index=True), Column("input_step_input_id", Integer, ForeignKey("workflow_step_input.id"), index=True), Column("output_name", TEXT), Column("input_subworkflow_step_id", Integer, ForeignKey("workflow_step.id"), index=True), ) for table in (WorkflowStepInput_table, NewWorkflowStepConnection_table): create_table(table) insert_step_inputs_cmd = \ "INSERT INTO workflow_step_input (workflow_step_id, name) " + \ "SELECT DISTINCT input_step_id, input_name FROM workflow_step_connection_preupgrade145" migrate_engine.execute(insert_step_inputs_cmd) insert_step_connections_cmd = \ "INSERT INTO workflow_step_connection (output_step_id, input_step_input_id, output_name, input_subworkflow_step_id) " + \ "SELECT wsc.output_step_id, wsi.id, wsc.output_name, wsc.input_subworkflow_step_id " + \ "FROM workflow_step_connection_preupgrade145 AS wsc JOIN workflow_step_input AS wsi ON wsc.input_step_id = wsi.workflow_step_id AND wsc.input_name = wsi.name ORDER BY wsc.id" migrate_engine.execute(insert_step_connections_cmd) drop_table(OldWorkflowStepConnection_table)
def upgrade(migrate_engine): print(__doc__) metadata.bind = migrate_engine metadata.reflect() # Add all of the new tables above create_table(UserAddress_table) # Add 1 column to the request_type table col = Column("deleted", Boolean, index=True, default=False) add_column(col, 'request_type', metadata, index_name='ix_request_type_deleted') # Delete the submitted column # This fails for sqlite, so skip the drop -- no conflicts in the future Request_table = Table("request", metadata, autoload=True) # SQLAlchemy Migrate has a bug when dropping a boolean column in SQLite if migrate_engine.name != 'sqlite': drop_column('submitted', Request_table) col = Column("state", TrimmedString(255), index=True) add_column(col, Request_table, metadata, index_name='ix_request_state')
def upgrade(migrate_engine): print(__doc__) metadata.bind = migrate_engine metadata.reflect() # create table + index AuditTable.drop(migrate_engine, checkfirst=True) create_table(AuditTable) # populate with update_time from every history copy_update_times = """ INSERT INTO history_audit (history_id, update_time) SELECT id, update_time FROM history """ migrate_engine.execute(copy_update_times) # drop existing timestamp triggers old_triggers.drop_timestamp_triggers(migrate_engine) # install new timestamp triggers new_triggers.install(migrate_engine)
def upgrade(migrate_engine): print(__doc__) metadata.bind = migrate_engine metadata.reflect() create_table(dataset_source_table) create_table(dataset_hash_table) create_table(dataset_source_hash_table)
def upgrade(migrate_engine): print(__doc__) metadata.bind = migrate_engine metadata.reflect() create_table(RequestEvent_table) # move the current state of all existing requests to the request_event table cmd = \ "INSERT INTO request_event " + \ "SELECT %s AS id," + \ "%s AS create_time," + \ "%s AS update_time," + \ "request.id AS request_id," + \ "request.state AS state," + \ "'%s' AS comment " + \ "FROM request;" cmd = cmd % (nextval(migrate_engine, 'request_event'), localtimestamp(migrate_engine), localtimestamp(migrate_engine), 'Imported from request table') migrate_engine.execute(cmd) drop_column('state', 'request', metadata)