root/galaxy-central/lib/galaxy/model/migrate/versions/0031_community_and_workflow_tags.py @ 2

"""
Migration script to (a) add and populate necessary columns for doing community tagging of histories, datasets, and pages and \
(b) add table for doing individual and community tagging of workflows.

SQLite does not support 'ALTER TABLE ADD FOREIGN KEY', so this script will generate error messages when run against \
SQLite; however, script does execute successfully against SQLite.
"""

from sqlalchemy import *
from sqlalchemy.orm import *
from migrate import *
from migrate.changeset import *

import logging
log = logging.getLogger( __name__ )

metadata = MetaData( migrate_engine )
db_session = scoped_session( sessionmaker( bind=migrate_engine, autoflush=False, autocommit=True ) )

StoredWorkflowTagAssociation_table = Table( "stored_workflow_tag_association", metadata,
    Column( "id", Integer, primary_key=True ),
    Column( "stored_workflow_id", Integer, ForeignKey( "stored_workflow.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", Unicode(255), index=True),
    Column( "value", Unicode(255), index=True),
    Column( "user_value", Unicode(255), index=True) )
    
WorkflowTagAssociation_table = Table( "workflow_tag_association", metadata,
    Column( "id", Integer, primary_key=True ),
    Column( "workflow_id", Integer, ForeignKey( "workflow.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", Unicode(255), index=True),
    Column( "value", Unicode(255), index=True),
    Column( "user_value", Unicode(255), index=True) )

def upgrade():
    print __doc__
    metadata.reflect()

    # Create user_id column in history_tag_association table.
    HistoryTagAssociation_table = Table( "history_tag_association", metadata, autoload=True )
    c = Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True )
    try:
        c.create( HistoryTagAssociation_table )
        assert c is HistoryTagAssociation_table.c.user_id
    except Exception, e:
        # SQLite does not support 'ALTER TABLE ADD FOREIGN KEY', so catch exception if it arises.
        print str(e)
        log.debug( "Adding user_id column to history_tag_association table failed: %s" % str( e ) )

    # Create user_id index for history_tag_association table.
    try:
        i = Index( "ix_history_tag_association_user_id", HistoryTagAssociation_table.c.user_id )
        i.create()
    except:
        # Mysql doesn't have a named index, but alter should work
        HistoryTagAssociation_table.c.user_id.alter( unique=False )
        
    # 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).
    if c is HistoryTagAssociation_table.c.user_id:
        db_session.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.
    HistoryDatasetAssociationTagAssociation_table = Table( "history_dataset_association_tag_association", metadata, autoload=True )
    c = Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True )
    try:
        c.create( HistoryDatasetAssociationTagAssociation_table )
        assert c is HistoryDatasetAssociationTagAssociation_table.c.user_id
    except Exception, e:
        # SQLite does not support 'ALTER TABLE ADD FOREIGN KEY', so catch exception if it arises.
        print str(e)
        log.debug( "Adding user_id column to history_dataset_association_tag_association table failed: %s" % str( e ) )

    # Create user_id index for history_dataset_association_tag_association table.
    try:
        i = Index( "ix_history_dataset_association_tag_association_user_id", HistoryDatasetAssociationTagAssociation_table.c.user_id )
        i.create()
    except:
        # Mysql doesn't have a named index, but alter should work
        HistoryDatasetAssociationTagAssociation_table.c.user_id.alter( unique=False )
        
    # 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).
    if c is HistoryDatasetAssociationTagAssociation_table.c.user_id:
        db_session.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.
    PageTagAssociation_table = Table( "page_tag_association", metadata, autoload=True )
    c = Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True )
    try:
        c.create( PageTagAssociation_table )
        assert c is PageTagAssociation_table.c.user_id
    except Exception, e:
        # SQLite does not support 'ALTER TABLE ADD FOREIGN KEY', so catch exception if it arises.
        print str(e)
        log.debug( "Adding user_id column to history_tag_association table failed: %s" % str( e ) )

    # Create user_id index for page_tag_association table.
    try:
        i = Index( "ix_page_tag_association_user_id", PageTagAssociation_table.c.user_id )
        i.create()
    except:
        # Mysql doesn't have a named index, but alter should work
        PageTagAssociation_table.c.user_id.alter( unique=False )
        
    # 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).
    if c is PageTagAssociation_table.c.user_id:
        db_session.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.
    try:
        StoredWorkflowTagAssociation_table.create()
    except Exception, e:
        print str(e)
        log.debug( "Creating stored_workflow_tag_association table failed: %s" % str( e ) )
        
    # Create workflow_tag_association table.
    try:
        WorkflowTagAssociation_table.create()
    except Exception, e:
        print str(e)
        log.debug( "Creating workflow_tag_association table failed: %s" % str( e ) )        

def downgrade():
    metadata.reflect()

    # Drop user_id column from history_tag_association table.
    HistoryTagAssociation_table = Table( "history_tag_association", metadata, autoload=True )
    try:
        HistoryTagAssociation_table.c.user_id.drop()
    except Exception, e:
        print str(e)
        log.debug( "Dropping column user_id from history_tag_association table failed: %s" % str( e ) )
        

    # Drop user_id column from history_dataset_association_tag_association table.
    HistoryDatasetAssociationTagAssociation_table = Table( "history_dataset_association_tag_association", metadata, autoload=True )
    try:
        HistoryDatasetAssociationTagAssociation_table.c.user_id.drop()
    except Exception, e:
        print str(e)
        log.debug( "Dropping column user_id from history_dataset_association_tag_association table failed: %s" % str( e ) )

    # Drop user_id column from page_tag_association table.
    PageTagAssociation_table = Table( "page_tag_association", metadata, autoload=True )
    try:
        PageTagAssociation_table.c.user_id.drop()
    except Exception, e:
        print str(e)
        log.debug( "Dropping column user_id from page_tag_association table failed: %s" % str( e ) )

    # Drop stored_workflow_tag_association table.
    try:
        StoredWorkflowTagAssociation_table.drop()
    except Exception, e:
        print str(e)
        log.debug( "Dropping stored_workflow_tag_association table failed: %s" % str( e ) )
        
    # Drop workflow_tag_association table.
    try:
        WorkflowTagAssociation_table.drop()
    except Exception, e:
        print str(e)
        log.debug( "Dropping workflow_tag_association table failed: %s" % str( e ) )