PageRenderTime 15ms CodeModel.GetById 1ms app.highlight 10ms RepoModel.GetById 1ms app.codeStats 0ms

/lib/galaxy/model/migrate/versions/0031_community_and_workflow_tags.py

https://bitbucket.org/cistrome/cistrome-harvard/
Python | 182 lines | 178 code | 0 blank | 4 comment | 0 complexity | 09e9c397a6b6217b0dda8f591c4a86a4 MD5 | raw file
  1"""
  2Migration script to (a) add and populate necessary columns for doing community tagging of histories, datasets, and pages and \
  3(b) add table for doing individual and community tagging of workflows.
  4
  5SQLite does not support 'ALTER TABLE ADD FOREIGN KEY', so this script will generate error messages when run against \
  6SQLite; however, script does execute successfully against SQLite.
  7"""
  8
  9from sqlalchemy import *
 10from sqlalchemy.orm import *
 11from migrate import *
 12from migrate.changeset import *
 13
 14import logging
 15log = logging.getLogger( __name__ )
 16
 17metadata = MetaData()
 18
 19StoredWorkflowTagAssociation_table = Table( "stored_workflow_tag_association", metadata,
 20    Column( "id", Integer, primary_key=True ),
 21    Column( "stored_workflow_id", Integer, ForeignKey( "stored_workflow.id" ), index=True ),
 22    Column( "tag_id", Integer, ForeignKey( "tag.id" ), index=True ),
 23    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
 24    Column( "user_tname", Unicode(255), index=True),
 25    Column( "value", Unicode(255), index=True),
 26    Column( "user_value", Unicode(255), index=True) )
 27
 28WorkflowTagAssociation_table = Table( "workflow_tag_association", metadata,
 29    Column( "id", Integer, primary_key=True ),
 30    Column( "workflow_id", Integer, ForeignKey( "workflow.id" ), index=True ),
 31    Column( "tag_id", Integer, ForeignKey( "tag.id" ), index=True ),
 32    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
 33    Column( "user_tname", Unicode(255), index=True),
 34    Column( "value", Unicode(255), index=True),
 35    Column( "user_value", Unicode(255), index=True) )
 36
 37def upgrade(migrate_engine):
 38    metadata.bind = migrate_engine
 39    print __doc__
 40    metadata.reflect()
 41
 42    # Create user_id column in history_tag_association table.
 43    HistoryTagAssociation_table = Table( "history_tag_association", metadata, autoload=True )
 44    if migrate_engine.name != 'sqlite':
 45        c = Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True )
 46        try:
 47            c.create( HistoryTagAssociation_table, index_name='ix_history_tag_association_user_id')
 48            assert c is HistoryTagAssociation_table.c.user_id
 49        except Exception, e:
 50            # SQLite does not support 'ALTER TABLE ADD FOREIGN KEY', so catch exception if it arises.
 51            print str(e)
 52            log.debug( "Adding user_id column to history_tag_association table failed: %s" % str( e ) )
 53    else:
 54        c = Column( "user_id", Integer)
 55        try:
 56            c.create( HistoryTagAssociation_table)
 57            assert c is HistoryTagAssociation_table.c.user_id
 58        except Exception, e:
 59            # SQLite does not support 'ALTER TABLE ADD FOREIGN KEY', so catch exception if it arises.
 60            print str(e)
 61            log.debug( "Adding user_id column to history_tag_association table failed: %s" % str( e ) )
 62
 63    # 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).
 64    if c is HistoryTagAssociation_table.c.user_id:
 65        migrate_engine.execute(
 66            "UPDATE history_tag_association SET user_id=( SELECT user_id FROM history WHERE history_tag_association.history_id = history.id )"
 67                            )
 68
 69    if migrate_engine.name != 'sqlite':
 70        # Create user_id column in history_dataset_association_tag_association table.
 71        HistoryDatasetAssociationTagAssociation_table = Table( "history_dataset_association_tag_association", metadata, autoload=True )
 72        c = Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True )
 73        try:
 74            c.create( HistoryDatasetAssociationTagAssociation_table, index_name='ix_history_dataset_association_tag_association_user_id')
 75            assert c is HistoryDatasetAssociationTagAssociation_table.c.user_id
 76        except Exception, e:
 77            # SQLite does not support 'ALTER TABLE ADD FOREIGN KEY', so catch exception if it arises.
 78            print str(e)
 79            log.debug( "Adding user_id column to history_dataset_association_tag_association table failed: %s" % str( e ) )
 80    else:
 81        #In sqlite, we can no longer quietly fail to add foreign key.
 82        # Create user_id column in history_dataset_association_tag_association table.
 83        HistoryDatasetAssociationTagAssociation_table = Table( "history_dataset_association_tag_association", metadata, autoload=True )
 84        c = Column( "user_id", Integer)
 85        try:
 86            c.create( HistoryDatasetAssociationTagAssociation_table)
 87            assert c is HistoryDatasetAssociationTagAssociation_table.c.user_id
 88        except Exception, e:
 89            # SQLite does not support 'ALTER TABLE ADD FOREIGN KEY', so catch exception if it arises.
 90            print str(e)
 91            log.debug( "Adding user_id column to history_dataset_association_tag_association table failed: %s" % str( e ) )
 92
 93    # 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).
 94    if c is HistoryDatasetAssociationTagAssociation_table.c.user_id:
 95        migrate_engine.execute(
 96            "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)"
 97                            )
 98    if migrate_engine.name != 'sqlite':
 99        # Create user_id column in page_tag_association table.
100        PageTagAssociation_table = Table( "page_tag_association", metadata, autoload=True )
101        c = Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True )
102        try:
103            c.create( PageTagAssociation_table, index_name='ix_page_tag_association_user_id')
104            assert c is PageTagAssociation_table.c.user_id
105        except Exception, e:
106            # SQLite does not support 'ALTER TABLE ADD FOREIGN KEY', so catch exception if it arises.
107            print str(e)
108            log.debug( "Adding user_id column to page_tag_association table failed: %s" % str( e ) )
109    else:
110        # Create user_id column in page_tag_association table.
111        PageTagAssociation_table = Table( "page_tag_association", metadata, autoload=True )
112        c = Column( "user_id", Integer )
113        try:
114            c.create( PageTagAssociation_table )
115            assert c is PageTagAssociation_table.c.user_id
116        except Exception, e:
117            # SQLite does not support 'ALTER TABLE ADD FOREIGN KEY', so catch exception if it arises.
118            print str(e)
119            log.debug( "Adding user_id column to page_tag_association table failed: %s" % str( e ) )
120
121    # 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).
122    if c is PageTagAssociation_table.c.user_id:
123        migrate_engine.execute(
124            "UPDATE page_tag_association SET user_id=( SELECT user_id FROM page WHERE page_tag_association.page_id = page.id )"
125                            )
126
127    # Create stored_workflow_tag_association table.
128    try:
129        StoredWorkflowTagAssociation_table.create()
130    except Exception, e:
131        print str(e)
132        log.debug( "Creating stored_workflow_tag_association table failed: %s" % str( e ) )
133
134    # Create workflow_tag_association table.
135    try:
136        WorkflowTagAssociation_table.create()
137    except Exception, e:
138        print str(e)
139        log.debug( "Creating workflow_tag_association table failed: %s" % str( e ) )
140
141def downgrade(migrate_engine):
142    metadata.bind = migrate_engine
143    metadata.reflect()
144
145    # Drop user_id column from history_tag_association table.
146    HistoryTagAssociation_table = Table( "history_tag_association", metadata, autoload=True )
147    try:
148        HistoryTagAssociation_table.c.user_id.drop()
149    except Exception, e:
150        print str(e)
151        log.debug( "Dropping column user_id from history_tag_association table failed: %s" % str( e ) )
152
153
154    # Drop user_id column from history_dataset_association_tag_association table.
155    HistoryDatasetAssociationTagAssociation_table = Table( "history_dataset_association_tag_association", metadata, autoload=True )
156    try:
157        HistoryDatasetAssociationTagAssociation_table.c.user_id.drop()
158    except Exception, e:
159        print str(e)
160        log.debug( "Dropping column user_id from history_dataset_association_tag_association table failed: %s" % str( e ) )
161
162    # Drop user_id column from page_tag_association table.
163    PageTagAssociation_table = Table( "page_tag_association", metadata, autoload=True )
164    try:
165        PageTagAssociation_table.c.user_id.drop()
166    except Exception, e:
167        print str(e)
168        log.debug( "Dropping column user_id from page_tag_association table failed: %s" % str( e ) )
169
170    # Drop stored_workflow_tag_association table.
171    try:
172        StoredWorkflowTagAssociation_table.drop()
173    except Exception, e:
174        print str(e)
175        log.debug( "Dropping stored_workflow_tag_association table failed: %s" % str( e ) )
176
177    # Drop workflow_tag_association table.
178    try:
179        WorkflowTagAssociation_table.drop()
180    except Exception, e:
181        print str(e)
182        log.debug( "Dropping workflow_tag_association table failed: %s" % str( e ) )