Warning

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.0091_add_tool_version_tables

"""
Migration script to create the tool_version and tool_version_association tables and drop the tool_id_guid_map table.
"""

import datetime
import logging
from json import loads

from sqlalchemy import (
    Column,
    DateTime,
    ForeignKey,
    Index,
    Integer,
    MetaData,
    String,
    Table,
    TEXT
)

from galaxy.model.custom_types import (
    _sniffnfix_pg9_hex,
    TrimmedString
)
from galaxy.model.migrate.versions.util import (
    localtimestamp,
    nextval
)

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


ToolVersion_table = Table("tool_version", metadata,
    Column("id", Integer, primary_key=True),
    Column("create_time", DateTime, default=now),
    Column("update_time", DateTime, default=now, onupdate=now),
    Column("tool_id", String(255)),
    Column("tool_shed_repository_id", Integer, ForeignKey("tool_shed_repository.id"), index=True, nullable=True))

ToolVersionAssociation_table = Table("tool_version_association", metadata,
    Column("id", Integer, primary_key=True),
    Column("tool_id", Integer, ForeignKey("tool_version.id"), index=True, nullable=False),
    Column("parent_id", Integer, ForeignKey("tool_version.id"), index=True, nullable=False))


[docs]def upgrade(migrate_engine): print(__doc__) metadata.bind = migrate_engine metadata.reflect() # Create the tables. try: ToolVersion_table.create() except Exception: log.exception("Creating tool_version table failed.") try: ToolVersionAssociation_table.create() except Exception: log.exception("Creating tool_version_association table failed.") # Populate the tool table with tools included in installed tool shed repositories. cmd = "SELECT id, metadata FROM tool_shed_repository" result = migrate_engine.execute(cmd) count = 0 for row in result: if row[1]: tool_shed_repository_id = row[0] repository_metadata = loads(_sniffnfix_pg9_hex(str(row[1]))) # Create a new row in the tool table for each tool included in repository. We will NOT # handle tool_version_associaions because we do not have the information we need to do so. tools = repository_metadata.get('tools', []) for tool_dict in tools: cmd = "INSERT INTO tool_version VALUES (%s, %s, %s, '%s', %s)" % \ (nextval(migrate_engine, 'tool_version'), localtimestamp(migrate_engine), localtimestamp(migrate_engine), tool_dict['guid'], tool_shed_repository_id) migrate_engine.execute(cmd) count += 1 print("Added %d rows to the new tool_version table." % count) # Drop the tool_id_guid_map table since the 2 new tables render it unnecessary. ToolIdGuidMap_table = Table("tool_id_guid_map", metadata, autoload=True) try: ToolIdGuidMap_table.drop() except Exception: log.exception("Dropping tool_id_guid_map table failed.")
[docs]def downgrade(migrate_engine): metadata.bind = migrate_engine ToolIdGuidMap_table = Table( "tool_id_guid_map", metadata, Column("id", Integer, primary_key=True), Column("create_time", DateTime, default=now), Column("update_time", DateTime, default=now, onupdate=now), Column("tool_id", String(255)), Column("tool_version", TEXT), Column("tool_shed", TrimmedString(255)), Column("repository_owner", TrimmedString(255)), Column("repository_name", TrimmedString(255)), Column("guid", TEXT), Index('ix_tool_id_guid_map_guid', 'guid', unique=True, mysql_length=200), ) metadata.reflect() try: ToolVersionAssociation_table.drop() except Exception: log.exception("Dropping tool_version_association table failed.") try: ToolVersion_table.drop() except Exception: log.exception("Dropping tool_version table failed.") try: ToolIdGuidMap_table.create() except Exception: log.exception("Creating tool_id_guid_map table failed.")