/lib/galaxy/model/migrate/versions/0043_visualization_sharing_tagging_annotating.py

  1"""
  2Migration script to create tables and columns for sharing visualizations.
  3"""
  4
  5from sqlalchemy import *
  6from sqlalchemy.orm import *
  7from migrate import *
  8from migrate.changeset import *
  9
 10import logging
 11log = logging.getLogger( __name__ )
 12
 13metadata = MetaData()
 14
 15# Sharing visualizations.
 16
 17VisualizationUserShareAssociation_table = Table( "visualization_user_share_association", metadata,
 18    Column( "id", Integer, primary_key=True ),
 19    Column( "visualization_id", Integer, ForeignKey( "visualization.id" ), index=True ),
 20    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True )
 21    )
 22
 23# Tagging visualizations.
 24
 25VisualizationTagAssociation_table = Table( "visualization_tag_association", metadata,
 26    Column( "id", Integer, primary_key=True ),
 27    Column( "visualization_id", Integer, ForeignKey( "visualization.id" ), index=True ),
 28    Column( "tag_id", Integer, ForeignKey( "tag.id" ), index=True ),
 29    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
 30    Column( "user_tname", Unicode(255), index=True),
 31    Column( "value", Unicode(255), index=True),
 32    Column( "user_value", Unicode(255), index=True) )
 33
 34# Annotating visualizations.
 35
 36VisualizationAnnotationAssociation_table = Table( "visualization_annotation_association", metadata,
 37    Column( "id", Integer, primary_key=True ),
 38    Column( "visualization_id", Integer, ForeignKey( "visualization.id" ), index=True ),
 39    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
 40    Column( "annotation", TEXT, index=False ) )
 41
 42
 43def upgrade(migrate_engine):
 44    metadata.bind = migrate_engine
 45    print __doc__
 46    db_session = scoped_session( sessionmaker( bind=migrate_engine, autoflush=False, autocommit=True ) )
 47    metadata.reflect()
 48
 49    Visualiation_table = Table( "visualization", metadata, autoload=True )
 50    # Create visualization_user_share_association table.
 51    try:
 52        VisualizationUserShareAssociation_table.create()
 53    except Exception, e:
 54        print "Creating visualization_user_share_association table failed: %s" % str( e )
 55        log.debug( "Creating visualization_user_share_association table failed: %s" % str( e ) )
 56
 57    # Get default boolean value 'false' so that columns can be initialized.
 58    if migrate_engine.name == 'mysql' or migrate_engine.name == 'sqlite':
 59        default_false = "0"
 60    elif migrate_engine.name == 'postgresql':
 61        default_false = "false"
 62
 63    # Add columns & create indices for supporting sharing to visualization table.
 64    deleted_column = Column( "deleted", Boolean, default=False, index=True )
 65    importable_column = Column( "importable", Boolean, default=False, index=True )
 66    slug_column = Column( "slug", TEXT )
 67    published_column = Column( "published", Boolean, index=True )
 68
 69    try:
 70        # Add column.
 71        deleted_column.create( Visualiation_table, index_name = "ix_visualization_deleted")
 72        assert deleted_column is Visualiation_table.c.deleted
 73
 74        # Fill column with default value.
 75        cmd = "UPDATE visualization SET deleted = %s" % default_false
 76        db_session.execute( cmd )
 77    except Exception, e:
 78        print "Adding deleted column to visualization table failed: %s" % str( e )
 79        log.debug( "Adding deleted column to visualization table failed: %s" % str( e ) )
 80
 81    try:
 82        # Add column.
 83        importable_column.create( Visualiation_table, index_name='ix_visualization_importable')
 84        assert importable_column is Visualiation_table.c.importable
 85
 86        # Fill column with default value.
 87        cmd = "UPDATE visualization SET importable = %s" % default_false
 88        db_session.execute( cmd )
 89    except Exception, e:
 90        print "Adding importable column to visualization table failed: %s" % str( e )
 91        log.debug( "Adding importable column to visualization table failed: %s" % str( e ) )
 92
 93    try:
 94        slug_column.create( Visualiation_table )
 95        assert slug_column is Visualiation_table.c.slug
 96    except Exception, e:
 97        print "Adding slug column to visualization table failed: %s" % str( e )
 98        log.debug( "Adding slug column to visualization table failed: %s" % str( e ) )
 99
100    try:
101        if migrate_engine.name == 'mysql':
102            # Have to create index manually.
103            cmd = "CREATE INDEX ix_visualization_slug ON visualization ( slug ( 100 ) )"
104            db_session.execute( cmd )
105        else:
106            i = Index( "ix_visualization_slug", Visualiation_table.c.slug )
107            i.create()
108    except Exception, e:
109        print "Adding index 'ix_visualization_slug' failed: %s" % str( e )
110        log.debug( "Adding index 'ix_visualization_slug' failed: %s" % str( e ) )
111
112    try:
113        # Add column.
114        published_column.create( Visualiation_table, index_name='ix_visualization_published')
115        assert published_column is Visualiation_table.c.published
116
117        # Fill column with default value.
118        cmd = "UPDATE visualization SET published = %s" % default_false
119        db_session.execute( cmd )
120    except Exception, e:
121        print "Adding published column to visualization table failed: %s" % str( e )
122        log.debug( "Adding published column to visualization table failed: %s" % str( e ) )
123
124    # Create visualization_tag_association table.
125    try:
126        VisualizationTagAssociation_table.create()
127    except Exception, e:
128        print str(e)
129        log.debug( "Creating visualization_tag_association table failed: %s" % str( e ) )
130
131    # Create visualization_annotation_association table.
132    try:
133        VisualizationAnnotationAssociation_table.create()
134    except Exception, e:
135        print str(e)
136        log.debug( "Creating visualization_annotation_association table failed: %s" % str( e ) )
137
138    # Need to create index for visualization annotation manually to deal with errors.
139    try:
140       if migrate_engine.name == 'mysql':
141           # Have to create index manually.
142           cmd = "CREATE INDEX ix_visualization_annotation_association_annotation ON visualization_annotation_association ( annotation ( 100 ) )"
143           db_session.execute( cmd )
144       else:
145           i = Index( "ix_visualization_annotation_association_annotation", VisualizationAnnotationAssociation_table.c.annotation )
146           i.create()
147    except Exception, e:
148       print "Adding index 'ix_visualization_annotation_association_annotation' failed: %s" % str( e )
149       log.debug( "Adding index 'ix_visualization_annotation_association_annotation' failed: %s" % str( e ) )
150
151def downgrade(migrate_engine):
152    metadata.bind = migrate_engine
153    metadata.reflect()
154
155    Visualiation_table = Table( "visualization", metadata, autoload=True )
156    # Drop visualization_user_share_association table.
157    try:
158        VisualizationUserShareAssociation_table.drop()
159    except Exception, e:
160        print str(e)
161        log.debug( "Dropping visualization_user_share_association table failed: %s" % str( e ) )
162
163    # Drop columns for supporting sharing from visualization table.
164    try:
165        Visualiation_table.c.deleted.drop()
166    except Exception, e:
167        print "Dropping deleted column from visualization table failed: %s" % str( e )
168        log.debug( "Dropping deleted column from visualization table failed: %s" % str( e ) )
169
170    try:
171        Visualiation_table.c.importable.drop()
172    except Exception, e:
173        print "Dropping importable column from visualization table failed: %s" % str( e )
174        log.debug( "Dropping importable column from visualization table failed: %s" % str( e ) )
175
176    try:
177        Visualiation_table.c.slug.drop()
178    except Exception, e:
179        print "Dropping slug column from visualization table failed: %s" % str( e )
180        log.debug( "Dropping slug column from visualization table failed: %s" % str( e ) )
181
182    try:
183        Visualiation_table.c.published.drop()
184    except Exception, e:
185        print "Dropping published column from visualization table failed: %s" % str( e )
186        log.debug( "Dropping published column from visualization table failed: %s" % str( e ) )
187
188    # Drop visualization_tag_association table.
189    try:
190        VisualizationTagAssociation_table.drop()
191    except Exception, e:
192        print str(e)
193        log.debug( "Dropping visualization_tag_association table failed: %s" % str( e ) )
194
195    # Drop visualization_annotation_association table.
196    try:
197        VisualizationAnnotationAssociation_table.drop()
198    except Exception, e:
199        print str(e)
200        log.debug( "Dropping visualization_annotation_association table failed: %s" % str( e ) )