Source code for tool_shed.webapp.model.migrate.versions.0005_drop_tool_related_tables

"""
Drops the tool, tool_category_association, event, tool_event_association, tool_rating_association,
tool_tag_association and tool_annotation_association tables since they are no longer used in the
next-gen tool shed.
"""

import datetime
import logging
import sys

from sqlalchemy import Boolean, Column, DateTime, ForeignKey, Integer, MetaData, Table, TEXT
from sqlalchemy.exc import NoSuchTableError

# Need our custom types, but don't import anything else from model
from galaxy.model.custom_types import TrimmedString

now = datetime.datetime.utcnow
log = logging.getLogger(__name__)
log.setLevel(logging.DEBUG)
handler = logging.StreamHandler(sys.stdout)
format = "%(name)s %(levelname)s %(asctime)s %(message)s"
formatter = logging.Formatter(format)
handler.setFormatter(formatter)
log.addHandler(handler)

metadata = MetaData()


[docs]def upgrade(migrate_engine):
    print(__doc__)
    # Load existing tables
    metadata.bind = migrate_engine
    metadata.reflect()
    # Load and then drop the tool_category_association table
    try:
        ToolCategoryAssociation_table = Table("tool_category_association", metadata, autoload=True)
    except NoSuchTableError:
        log.debug("Failed loading table tool_category_association")
    try:
        ToolCategoryAssociation_table.drop()
    except Exception:
        log.exception("Dropping tool_category_association table failed.")
    # Load and then drop the tool_event_association table
    try:
        ToolEventAssociation_table = Table("tool_event_association", metadata, autoload=True)
    except NoSuchTableError:
        log.debug("Failed loading table tool_event_association")
    try:
        ToolEventAssociation_table.drop()
    except Exception:
        log.exception("Dropping tool_event_association table failed.")
    # Load and then drop the tool_rating_association table
    try:
        ToolRatingAssociation_table = Table("tool_rating_association", metadata, autoload=True)
    except NoSuchTableError:
        log.debug("Failed loading table tool_rating_association")
    try:
        ToolRatingAssociation_table.drop()
    except Exception:
        log.exception("Dropping tool_rating_association table failed.")
    # Load and then drop the tool_tag_association table
    try:
        ToolTagAssociation_table = Table("tool_tag_association", metadata, autoload=True)
    except NoSuchTableError:
        log.debug("Failed loading table tool_tag_association")
    try:
        ToolTagAssociation_table.drop()
    except Exception:
        log.exception("Dropping tool_tag_association table failed.")
    # Load and then drop the tool_annotation_association table
    try:
        ToolAnnotationAssociation_table = Table("tool_annotation_association", metadata, autoload=True)
    except NoSuchTableError:
        log.debug("Failed loading table tool_annotation_association")
    try:
        ToolAnnotationAssociation_table.drop()
    except Exception:
        log.exception("Dropping tool_annotation_association table failed.")
    # Load and then drop the event table
    try:
        Event_table = Table("event", metadata, autoload=True)
    except NoSuchTableError:
        log.debug("Failed loading table event")
    try:
        Event_table.drop()
    except Exception:
        log.exception("Dropping event table failed.")
    # Load and then drop the tool table
    try:
        Tool_table = Table("tool", metadata, autoload=True)
    except NoSuchTableError:
        log.debug("Failed loading table tool")
    try:
        Tool_table.drop()
    except Exception:
        log.exception("Dropping tool table failed.")


[docs]def downgrade(migrate_engine):
    # Load existing tables
    metadata.bind = migrate_engine
    metadata.reflect()
    # We've lost all of our data, so downgrading is useless. However, we'll
    # at least re-create the dropped tables.
    Event_table = Table('event', metadata,
                        Column("id", Integer, primary_key=True),
                        Column("create_time", DateTime, default=now),
                        Column("update_time", DateTime, default=now, onupdate=now),
                        Column("state", TrimmedString(255), index=True),
                        Column("comment", TEXT))

    Tool_table = Table("tool", metadata,
                       Column("id", Integer, primary_key=True),
                       Column("guid", TrimmedString(255), index=True, unique=True),
                       Column("tool_id", TrimmedString(255), index=True),
                       Column("create_time", DateTime, default=now),
                       Column("update_time", DateTime, default=now, onupdate=now),
                       Column("newer_version_id", Integer, ForeignKey("tool.id"), nullable=True),
                       Column("name", TrimmedString(255), index=True),
                       Column("description", TEXT),
                       Column("user_description", TEXT),
                       Column("version", TrimmedString(255)),
                       Column("user_id", Integer, ForeignKey("galaxy_user.id"), index=True),
                       Column("external_filename", TEXT),
                       Column("deleted", Boolean, index=True, default=False),
                       Column("suite", Boolean, default=False, index=True))

    ToolCategoryAssociation_table = Table("tool_category_association", metadata,
                                          Column("id", Integer, primary_key=True),
                                          Column("tool_id", Integer, ForeignKey("tool.id"), index=True),
                                          Column("category_id", Integer, ForeignKey("category.id"), index=True))

    ToolEventAssociation_table = Table("tool_event_association", metadata,
                                       Column("id", Integer, primary_key=True),
                                       Column("tool_id", Integer, ForeignKey("tool.id"), index=True),
                                       Column("event_id", Integer, ForeignKey("event.id"), index=True))

    ToolRatingAssociation_table = Table("tool_rating_association", metadata,
                                        Column("id", Integer, primary_key=True),
                                        Column("create_time", DateTime, default=now),
                                        Column("update_time", DateTime, default=now, onupdate=now),
                                        Column("tool_id", Integer, ForeignKey("tool.id"), index=True),
                                        Column("user_id", Integer, ForeignKey("galaxy_user.id"), index=True),
                                        Column("rating", Integer, index=True),
                                        Column("comment", TEXT))

    ToolTagAssociation_table = Table("tool_tag_association", metadata,
                                     Column("id", Integer, primary_key=True),
                                     Column("tool_id", Integer, ForeignKey("tool.id"), index=True),
                                     Column("tag_id", Integer, ForeignKey("tag.id"), index=True),
                                     Column("user_id", Integer, ForeignKey("galaxy_user.id"), index=True),
                                     Column("user_tname", TrimmedString(255), index=True),
                                     Column("value", TrimmedString(255), index=True),
                                     Column("user_value", TrimmedString(255), index=True))

    ToolAnnotationAssociation_table = Table("tool_annotation_association", metadata,
                                            Column("id", Integer, primary_key=True),
                                            Column("tool_id", Integer, ForeignKey("tool.id"), index=True),
                                            Column("user_id", Integer, ForeignKey("galaxy_user.id"), index=True),
                                            Column("annotation", TEXT, index=True))

    # Create the event table
    try:
        Event_table.create()
    except Exception:
        log.exception("Creating event table failed.")
    # Create the tool table
    try:
        Tool_table.create()
    except Exception:
        log.exception("Creating tool table failed.")
    # Create the tool_category_association table
    try:
        ToolCategoryAssociation_table.create()
    except Exception:
        log.exception("Creating tool_category_association table failed.")
    # Create the tool_event_association table
    try:
        ToolEventAssociation_table.create()
    except Exception:
        log.exception("Creating tool_event_association table failed.")
    # Create the tool_rating_association table
    try:
        ToolRatingAssociation_table.create()
    except Exception:
        log.exception("Creating tool_rating_association table failed.")
    # Create the tool_tag_association table
    try:
        ToolTagAssociation_table.create()
    except Exception:
        log.exception("Creating tool_tag_association table failed.")
    # Create the tool_annotation_association table
    try:
        ToolAnnotationAssociation_table.create()
    except Exception:
        log.exception("Creating tool_annotation_association table failed.")