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