/lib/galaxy/model/migrate/versions/0013_change_lib_item_templates_to_forms.py

  1"""
  2This migration script eliminates all of the tables that were used for the 1st version of the
  3library templates where template fields and contents were each stored as a separate table row
  4in various library item tables.  All of these tables are dropped in this script, eliminating all
  5existing template data.  A total of 14 existing tables are dropped.
  6
  7We're now basing library templates on forms, so field contents are
  8stored as a jsonified list in the form_values table.  This script introduces the following 3
  9new association tables:
 101) library_info_association
 112) library_folder_info_association
 123) library_dataset_dataset_info_association
 13
 14If using mysql, this script will throw an (OperationalError) exception due to a long index name on
 15the library_dataset_dataset_info_association table, which is OK because the script creates an index
 16with a shortened name.
 17"""
 18from sqlalchemy import *
 19from sqlalchemy.orm import *
 20from sqlalchemy.exc import *
 21from migrate import *
 22from migrate.changeset import *
 23import sys, logging
 24
 25log = logging.getLogger( __name__ )
 26log.setLevel(logging.DEBUG)
 27handler = logging.StreamHandler( sys.stdout )
 28format = "%(name)s %(levelname)s %(asctime)s %(message)s"
 29formatter = logging.Formatter( format )
 30handler.setFormatter( formatter )
 31log.addHandler( handler )
 32
 33metadata = MetaData()
 34
 35def display_migration_details():
 36    print "========================================"
 37    print "This migration script eliminates all of the tables that were used for the 1st version of the"
 38    print "library templates where template fields and contents were each stored as a separate table row"
 39    print "in various library item tables.  All of these tables are dropped in this script, eliminating all"
 40    print "existing template data.  A total of 14 existing tables are dropped."
 41    print ""
 42    print "We're now basing library templates on Galaxy forms, so field contents are stored as a jsonified"
 43    print "list in the form_values table.  This script introduces the following 3 new association tables:"
 44    print "1) library_info_association"
 45    print "2) library_folder_info_association"
 46    print "3) library_dataset_dataset_info_association"
 47    print ""
 48    print "If using mysql, this script will throw an (OperationalError) exception due to a long index name"
 49    print "on the library_dataset_dataset_info_association table, which is OK because the script creates"
 50    print "an index with a shortened name."
 51    print "========================================"
 52
 53LibraryInfoAssociation_table = Table( 'library_info_association', metadata,
 54    Column( "id", Integer, primary_key=True),
 55    Column( "library_id", Integer, ForeignKey( "library.id" ), index=True ),
 56    Column( "form_definition_id", Integer, ForeignKey( "form_definition.id" ), index=True ),
 57    Column( "form_values_id", Integer, ForeignKey( "form_values.id" ), index=True ) )
 58
 59LibraryFolderInfoAssociation_table = Table( 'library_folder_info_association', metadata,
 60    Column( "id", Integer, primary_key=True),
 61    Column( "library_folder_id", Integer, ForeignKey( "library_folder.id" ), nullable=True, index=True ),
 62    Column( "form_definition_id", Integer, ForeignKey( "form_definition.id" ), index=True ),
 63    Column( "form_values_id", Integer, ForeignKey( "form_values.id" ), index=True ) )
 64
 65LibraryDatasetDatasetInfoAssociation_table = Table( 'library_dataset_dataset_info_association', metadata,
 66    Column( "id", Integer, primary_key=True),
 67    Column( "library_dataset_dataset_association_id", Integer, ForeignKey( "library_dataset_dataset_association.id" ), nullable=True, index=True ),
 68    Column( "form_definition_id", Integer, ForeignKey( "form_definition.id" ), index=True ),
 69    Column( "form_values_id", Integer, ForeignKey( "form_values.id" ), index=True ) )
 70
 71def upgrade(migrate_engine):
 72    metadata.bind = migrate_engine
 73    display_migration_details()
 74    # Load existing tables
 75    metadata.reflect()
 76    if migrate_engine.name == 'postgres':
 77        # http://blog.pythonisito.com/2008/01/cascading-drop-table-with-sqlalchemy.html
 78        from sqlalchemy.databases import postgres
 79        class PGCascadeSchemaDropper(postgres.PGSchemaDropper):
 80            def visit_table(self, table):
 81                for column in table.columns:
 82                    if column.default is not None:
 83                        self.traverse_single(column.default)
 84                self.append("\nDROP TABLE " +
 85                            self.preparer.format_table(table) +
 86                            " CASCADE")
 87                self.execute()
 88        postgres.dialect.schemadropper = PGCascadeSchemaDropper
 89    # Drop all of the original library_item_info tables
 90    # NOTE: all existing library item into template data is eliminated here via table drops
 91    try:
 92        LibraryItemInfoPermissions_table = Table( "library_item_info_permissions", metadata, autoload=True )
 93    except NoSuchTableError:
 94        LibraryItemInfoPermissions_table = None
 95        log.debug( "Failed loading table library_item_info_permissions" )
 96    try:
 97        LibraryItemInfoPermissions_table.drop()
 98    except Exception, e:
 99        log.debug( "Dropping library_item_info_permissions table failed: %s" % str( e ) )
100
101    try:
102        LibraryItemInfoTemplatePermissions_table = Table( "library_item_info_template_permissions", metadata, autoload=True )
103    except NoSuchTableError:
104        LibraryItemInfoTemplatePermissions_table = None
105        log.debug( "Failed loading table library_item_info_template_permissions" )
106    try:
107        LibraryItemInfoTemplatePermissions_table.drop()
108    except Exception, e:
109        log.debug( "Dropping library_item_info_template_permissions table failed: %s" % str( e ) )
110
111    try:
112        LibraryItemInfoElement_table = Table( "library_item_info_element", metadata, autoload=True )
113    except NoSuchTableError:
114        LibraryItemInfoElement_table = None
115        log.debug( "Failed loading table library_item_info_element" )
116    try:
117        LibraryItemInfoElement_table.drop()
118    except Exception, e:
119        log.debug( "Dropping library_item_info_element table failed: %s" % str( e ) )
120
121    try:
122        LibraryItemInfoTemplateElement_table = Table( "library_item_info_template_element", metadata, autoload=True )
123    except NoSuchTableError:
124        LibraryItemInfoTemplateElement_table = None
125        log.debug( "Failed loading table library_item_info_template_element" )
126    try:
127        LibraryItemInfoTemplateElement_table.drop()
128    except Exception, e:
129        log.debug( "Dropping library_item_info_template_element table failed: %s" % str( e ) )
130
131    try:
132        LibraryInfoTemplateAssociation_table = Table( "library_info_template_association", metadata, autoload=True )
133    except NoSuchTableError:
134        LibraryInfoTemplateAssociation_table = None
135        log.debug( "Failed loading table library_info_template_association" )
136    try:
137        LibraryInfoTemplateAssociation_table.drop()
138    except Exception, e:
139        log.debug( "Dropping library_info_template_association table failed: %s" % str( e ) )
140
141    try:
142        LibraryFolderInfoTemplateAssociation_table = Table( "library_folder_info_template_association", metadata, autoload=True )
143    except NoSuchTableError:
144        LibraryFolderInfoTemplateAssociation_table = None
145        log.debug( "Failed loading table library_folder_info_template_association" )
146    try:
147        LibraryFolderInfoTemplateAssociation_table.drop()
148    except Exception, e:
149        log.debug( "Dropping library_folder_info_template_association table failed: %s" % str( e ) )
150
151    try:
152        LibraryDatasetInfoTemplateAssociation_table = Table( "library_dataset_info_template_association", metadata, autoload=True )
153    except NoSuchTableError:
154        LibraryDatasetInfoTemplateAssociation_table = None
155        log.debug( "Failed loading table library_dataset_info_template_association" )
156    try:
157        LibraryDatasetInfoTemplateAssociation_table.drop()
158    except Exception, e:
159        log.debug( "Dropping library_dataset_info_template_association table failed: %s" % str( e ) )
160
161    try:
162        LibraryDatasetDatasetInfoTemplateAssociation_table = Table( "library_dataset_dataset_info_template_association", metadata, autoload=True )
163    except NoSuchTableError:
164        LibraryDatasetDatasetInfoTemplateAssociation_table = None
165        log.debug( "Failed loading table library_dataset_dataset_info_template_association" )
166    try:
167        LibraryDatasetDatasetInfoTemplateAssociation_table.drop()
168    except Exception, e:
169        log.debug( "Dropping library_dataset_dataset_info_template_association table failed: %s" % str( e ) )
170
171    try:
172        LibraryInfoAssociation_table = Table( "library_info_association", metadata, autoload=True )
173    except NoSuchTableError:
174        LibraryInfoAssociation_table = None
175        log.debug( "Failed loading table library_info_association" )
176    try:
177        LibraryInfoAssociation_table.drop()
178    except Exception, e:
179        log.debug( "Dropping library_info_association table failed: %s" % str( e ) )
180
181    try:
182        LibraryFolderInfoAssociation_table = Table( "library_folder_info_association", metadata, autoload=True )
183    except NoSuchTableError:
184        LibraryFolderInfoAssociation_table = None
185        log.debug( "Failed loading table library_folder_info_association" )
186    try:
187        LibraryFolderInfoAssociation_table.drop()
188    except Exception, e:
189        log.debug( "Dropping library_folder_info_association table failed: %s" % str( e ) )
190
191    try:
192        LibraryDatasetInfoAssociation_table = Table( "library_dataset_info_association", metadata, autoload=True )
193    except NoSuchTableError:
194        LibraryDatasetInfoAssociation_table = None
195        log.debug( "Failed loading table library_dataset_info_association" )
196    try:
197        LibraryDatasetInfoAssociation_table.drop()
198    except Exception, e:
199        log.debug( "Dropping library_dataset_info_association table failed: %s" % str( e ) )
200
201    try:
202        LibraryDatasetDatasetInfoAssociation_table = Table( "library_dataset_dataset_info_association", metadata, autoload=True )
203    except NoSuchTableError:
204        LibraryDatasetDatasetInfoAssociation_table = None
205        log.debug( "Failed loading table library_dataset_dataset_info_association" )
206    try:
207        LibraryDatasetDatasetInfoAssociation_table.drop()
208    except Exception, e:
209        log.debug( "Dropping library_dataset_dataset_info_association table failed: %s" % str( e ) )
210
211    try:
212        LibraryItemInfo_table = Table( "library_item_info", metadata, autoload=True )
213    except NoSuchTableError:
214        LibraryItemInfo_table = None
215        log.debug( "Failed loading table library_item_info" )
216    try:
217        LibraryItemInfo_table.drop()
218    except Exception, e:
219        log.debug( "Dropping library_item_info table failed: %s" % str( e ) )
220
221    try:
222        LibraryItemInfoTemplate_table = Table( "library_item_info_template", metadata, autoload=True )
223    except NoSuchTableError:
224        LibraryItemInfoTemplate_table = None
225        log.debug( "Failed loading table library_item_info_template" )
226    try:
227        LibraryItemInfoTemplate_table.drop()
228    except Exception, e:
229        log.debug( "Dropping library_item_info_template table failed: %s" % str( e ) )
230
231    # Create all new tables above
232    try:
233        LibraryInfoAssociation_table.create()
234    except Exception, e:
235        log.debug( "Creating library_info_association table failed: %s" % str( e ) )
236    try:
237        LibraryFolderInfoAssociation_table.create()
238    except Exception, e:
239        log.debug( "Creating library_folder_info_association table failed: %s" % str( e ) )
240    try:
241        LibraryDatasetDatasetInfoAssociation_table.create()
242    except Exception, e:
243        log.debug( "Creating library_dataset_dataset_info_association table failed: %s" % str( e ) )
244    # Fix index on LibraryDatasetDatasetInfoAssociation_table for mysql
245    if migrate_engine.name == 'mysql':
246        # Load existing tables
247        metadata.reflect()
248        i = Index( "ix_lddaia_ldda_id", LibraryDatasetDatasetInfoAssociation_table.c.library_dataset_dataset_association_id )
249        try:
250            i.create()
251        except Exception, e:
252            log.debug( "Adding index 'ix_lddaia_ldda_id' to table 'library_dataset_dataset_info_association' table failed: %s" % str( e ) )
253
254def downgrade(migrate_engine):
255    metadata.bind = migrate_engine
256    log.debug( "Downgrade is not possible." )