/lib/galaxy/model/migrate/versions/0003_security_and_libraries.py

  1from sqlalchemy import *
  2from sqlalchemy.orm import *
  3from sqlalchemy.exc import *
  4from migrate import *
  5from migrate.changeset import *
  6
  7import datetime
  8now = datetime.datetime.utcnow
  9
 10import sys, logging
 11log = logging.getLogger( __name__ )
 12log.setLevel(logging.DEBUG)
 13handler = logging.StreamHandler( sys.stdout )
 14format = "%(name)s %(levelname)s %(asctime)s %(message)s"
 15formatter = logging.Formatter( format )
 16handler.setFormatter( formatter )
 17log.addHandler( handler )
 18
 19# Need our custom types, but don't import anything else from model
 20from galaxy.model.custom_types import *
 21
 22metadata = MetaData()
 23
 24# New tables as of changeset 2341:5498ac35eedd
 25Group_table = Table( "galaxy_group", metadata,
 26    Column( "id", Integer, primary_key=True ),
 27    Column( "create_time", DateTime, default=now ),
 28    Column( "update_time", DateTime, default=now, onupdate=now ),
 29    Column( "name", String( 255 ), index=True, unique=True ),
 30    Column( "deleted", Boolean, index=True, default=False ) )
 31
 32UserGroupAssociation_table = Table( "user_group_association", metadata,
 33    Column( "id", Integer, primary_key=True ),
 34    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
 35    Column( "group_id", Integer, ForeignKey( "galaxy_group.id" ), index=True ),
 36    Column( "create_time", DateTime, default=now ),
 37    Column( "update_time", DateTime, default=now, onupdate=now ) )
 38
 39UserRoleAssociation_table = Table( "user_role_association", metadata,
 40    Column( "id", Integer, primary_key=True ),
 41    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
 42    Column( "role_id", Integer, ForeignKey( "role.id" ), index=True ),
 43    Column( "create_time", DateTime, default=now ),
 44    Column( "update_time", DateTime, default=now, onupdate=now ) )
 45
 46GroupRoleAssociation_table = Table( "group_role_association", metadata,
 47    Column( "id", Integer, primary_key=True ),
 48    Column( "group_id", Integer, ForeignKey( "galaxy_group.id" ), index=True ),
 49    Column( "role_id", Integer, ForeignKey( "role.id" ), index=True ),
 50    Column( "create_time", DateTime, default=now ),
 51    Column( "update_time", DateTime, default=now, onupdate=now ) )
 52
 53Role_table = Table( "role", metadata,
 54    Column( "id", Integer, primary_key=True ),
 55    Column( "create_time", DateTime, default=now ),
 56    Column( "update_time", DateTime, default=now, onupdate=now ),
 57    Column( "name", String( 255 ), index=True, unique=True ),
 58    Column( "description", TEXT ),
 59    Column( "type", String( 40 ), index=True ),
 60    Column( "deleted", Boolean, index=True, default=False ) )
 61
 62DatasetPermissions_table = Table( "dataset_permissions", metadata,
 63    Column( "id", Integer, primary_key=True ),
 64    Column( "create_time", DateTime, default=now ),
 65    Column( "update_time", DateTime, default=now, onupdate=now ),
 66    Column( "action", TEXT ),
 67    Column( "dataset_id", Integer, ForeignKey( "dataset.id" ), index=True ),
 68    Column( "role_id", Integer, ForeignKey( "role.id" ), index=True ) )
 69
 70LibraryPermissions_table = Table( "library_permissions", metadata,
 71    Column( "id", Integer, primary_key=True ),
 72    Column( "create_time", DateTime, default=now ),
 73    Column( "update_time", DateTime, default=now, onupdate=now ),
 74    Column( "action", TEXT ),
 75    Column( "library_id", Integer, ForeignKey( "library.id" ), nullable=True, index=True ),
 76    Column( "role_id", Integer, ForeignKey( "role.id" ), index=True ) )
 77
 78LibraryFolderPermissions_table = Table( "library_folder_permissions", metadata,
 79    Column( "id", Integer, primary_key=True ),
 80    Column( "create_time", DateTime, default=now ),
 81    Column( "update_time", DateTime, default=now, onupdate=now ),
 82    Column( "action", TEXT ),
 83    Column( "library_folder_id", Integer, ForeignKey( "library_folder.id" ), nullable=True, index=True ),
 84    Column( "role_id", Integer, ForeignKey( "role.id" ), index=True ) )
 85
 86LibraryDatasetPermissions_table = Table( "library_dataset_permissions", metadata,
 87    Column( "id", Integer, primary_key=True ),
 88    Column( "create_time", DateTime, default=now ),
 89    Column( "update_time", DateTime, default=now, onupdate=now ),
 90    Column( "action", TEXT ),
 91    Column( "library_dataset_id", Integer, ForeignKey( "library_dataset.id" ), nullable=True, index=True ),
 92    Column( "role_id", Integer, ForeignKey( "role.id" ), index=True ) )
 93
 94LibraryDatasetDatasetAssociationPermissions_table = Table( "library_dataset_dataset_association_permissions", metadata,
 95    Column( "id", Integer, primary_key=True ),
 96    Column( "create_time", DateTime, default=now ),
 97    Column( "update_time", DateTime, default=now, onupdate=now ),
 98    Column( "action", TEXT ),
 99    Column( "library_dataset_dataset_association_id", Integer, ForeignKey( "library_dataset_dataset_association.id" ), nullable=True ),
100    Column( "role_id", Integer, ForeignKey( "role.id" ), index=True ) )
101Index( "ix_lddap_library_dataset_dataset_association_id", LibraryDatasetDatasetAssociationPermissions_table.c.library_dataset_dataset_association_id )
102
103LibraryItemInfoPermissions_table = Table( "library_item_info_permissions", metadata,
104    Column( "id", Integer, primary_key=True ),
105    Column( "create_time", DateTime, default=now ),
106    Column( "update_time", DateTime, default=now, onupdate=now ),
107    Column( "action", TEXT ),
108    Column( "library_item_info_id", Integer, ForeignKey( "library_item_info.id" ), nullable=True, index=True ),
109    Column( "role_id", Integer, ForeignKey( "role.id" ), index=True ) )
110
111LibraryItemInfoTemplatePermissions_table = Table( "library_item_info_template_permissions", metadata,
112    Column( "id", Integer, primary_key=True ),
113    Column( "create_time", DateTime, default=now ),
114    Column( "update_time", DateTime, default=now, onupdate=now ),
115    Column( "action", TEXT ),
116    Column( "library_item_info_template_id", Integer, ForeignKey( "library_item_info_template.id" ), nullable=True ),
117    Column( "role_id", Integer, ForeignKey( "role.id" ), index=True ) )
118Index( "ix_liitp_library_item_info_template_id", LibraryItemInfoTemplatePermissions_table.c.library_item_info_template_id )
119
120DefaultUserPermissions_table = Table( "default_user_permissions", metadata,
121    Column( "id", Integer, primary_key=True ),
122    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
123    Column( "action", TEXT ),
124    Column( "role_id", Integer, ForeignKey( "role.id" ), index=True ) )
125
126DefaultHistoryPermissions_table = Table( "default_history_permissions", metadata,
127    Column( "id", Integer, primary_key=True ),
128    Column( "history_id", Integer, ForeignKey( "history.id" ), index=True ),
129    Column( "action", TEXT ),
130    Column( "role_id", Integer, ForeignKey( "role.id" ), index=True ) )
131
132LibraryDataset_table = Table( "library_dataset", metadata,
133    Column( "id", Integer, primary_key=True ),
134    Column( "library_dataset_dataset_association_id", Integer, ForeignKey( "library_dataset_dataset_association.id", use_alter=True, name="library_dataset_dataset_association_id_fk" ), nullable=True, index=True ),#current version of dataset, if null, there is not a current version selected
135    Column( "folder_id", Integer, ForeignKey( "library_folder.id" ), index=True ),
136    Column( "order_id", Integer ),
137    Column( "create_time", DateTime, default=now ),
138    Column( "update_time", DateTime, default=now, onupdate=now ),
139    Column( "name", TrimmedString( 255 ), key="_name" ), #when not None/null this will supercede display in library (but not when imported into user's history?)
140    Column( "info", TrimmedString( 255 ),  key="_info" ), #when not None/null this will supercede display in library (but not when imported into user's history?)
141    Column( "deleted", Boolean, index=True, default=False ) )
142
143LibraryDatasetDatasetAssociation_table = Table( "library_dataset_dataset_association", metadata,
144    Column( "id", Integer, primary_key=True ),
145    Column( "library_dataset_id", Integer, ForeignKey( "library_dataset.id" ), index=True ),
146    Column( "dataset_id", Integer, ForeignKey( "dataset.id" ), index=True ),
147    Column( "create_time", DateTime, default=now ),
148    Column( "update_time", DateTime, default=now, onupdate=now ),
149    Column( "copied_from_history_dataset_association_id", Integer, ForeignKey( "history_dataset_association.id", use_alter=True, name='history_dataset_association_dataset_id_fkey' ), nullable=True ),
150    Column( "copied_from_library_dataset_dataset_association_id", Integer, ForeignKey( "library_dataset_dataset_association.id", use_alter=True, name='library_dataset_dataset_association_id_fkey' ), nullable=True ),
151    Column( "name", TrimmedString( 255 ) ),
152    Column( "info", TrimmedString( 255 ) ),
153    Column( "blurb", TrimmedString( 255 ) ),
154    Column( "peek" , TEXT ),
155    Column( "extension", TrimmedString( 64 ) ),
156    Column( "metadata", MetadataType(), key="_metadata" ),
157    Column( "parent_id", Integer, ForeignKey( "library_dataset_dataset_association.id" ), nullable=True ),
158    Column( "designation", TrimmedString( 255 ) ),
159    Column( "deleted", Boolean, index=True, default=False ),
160    Column( "visible", Boolean ),
161    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
162    Column( "message", TrimmedString( 255 ) ) )
163
164Library_table = Table( "library", metadata,
165    Column( "id", Integer, primary_key=True ),
166    Column( "root_folder_id", Integer, ForeignKey( "library_folder.id" ), index=True ),
167    Column( "create_time", DateTime, default=now ),
168    Column( "update_time", DateTime, default=now, onupdate=now ),
169    Column( "name", String( 255 ), index=True ),
170    Column( "deleted", Boolean, index=True, default=False ),
171    Column( "purged", Boolean, index=True, default=False ),
172    Column( "description", TEXT ) )
173
174LibraryFolder_table = Table( "library_folder", metadata,
175    Column( "id", Integer, primary_key=True ),
176    Column( "parent_id", Integer, ForeignKey( "library_folder.id" ), nullable = True, index=True ),
177    Column( "create_time", DateTime, default=now ),
178    Column( "update_time", DateTime, default=now, onupdate=now ),
179    Column( "name", TEXT ),
180    Column( "description", TEXT ),
181    Column( "order_id", Integer ),
182    Column( "item_count", Integer ),
183    Column( "deleted", Boolean, index=True, default=False ),
184    Column( "purged", Boolean, index=True, default=False ),
185    Column( "genome_build", TrimmedString( 40 ) ) )
186
187LibraryItemInfoTemplateElement_table = Table( "library_item_info_template_element", metadata,
188    Column( "id", Integer, primary_key=True ),
189    Column( "create_time", DateTime, default=now ),
190    Column( "update_time", DateTime, default=now, onupdate=now ),
191    Column( "optional", Boolean, index=True, default=True ),
192    Column( "deleted", Boolean, index=True, default=False ),
193    Column( "name", TEXT ),
194    Column( "description", TEXT ),
195    Column( "type", TEXT, default='string' ),
196    Column( "order_id", Integer ),
197    Column( "options", JSONType() ),
198    Column( "library_item_info_template_id", Integer, ForeignKey( "library_item_info_template.id" ) ) )
199Index( "ix_liite_library_item_info_template_id", LibraryItemInfoTemplateElement_table.c.library_item_info_template_id )
200
201LibraryItemInfoTemplate_table = Table( "library_item_info_template", metadata,
202    Column( "id", Integer, primary_key=True ),
203    Column( "create_time", DateTime, default=now ),
204    Column( "update_time", DateTime, default=now, onupdate=now ),
205    Column( "optional", Boolean, index=True, default=True ),
206    Column( "deleted", Boolean, index=True, default=False ),
207    Column( "name", TEXT ),
208    Column( "description", TEXT ),
209    Column( "item_count", Integer, default=0 ) )
210
211LibraryInfoTemplateAssociation_table = Table( "library_info_template_association", metadata,
212    Column( "id", Integer, primary_key=True ),
213    Column( "create_time", DateTime, default=now ),
214    Column( "update_time", DateTime, default=now, onupdate=now ),
215    Column( "library_id", Integer, ForeignKey( "library.id" ), nullable=True, index=True ),
216    Column( "library_item_info_template_id", Integer, ForeignKey( "library_item_info_template.id" ) ) )
217Index( "ix_lita_library_item_info_template_id", LibraryInfoTemplateAssociation_table.c.library_item_info_template_id )
218
219LibraryFolderInfoTemplateAssociation_table = Table( "library_folder_info_template_association", metadata,
220    Column( "id", Integer, primary_key=True ),
221    Column( "create_time", DateTime, default=now ),
222    Column( "update_time", DateTime, default=now, onupdate=now ),
223    Column( "library_folder_id", Integer, ForeignKey( "library_folder.id" ), nullable=True, index=True ),
224    Column( "library_item_info_template_id", Integer, ForeignKey( "library_item_info_template.id" ) ) )
225Index( "ix_lfita_library_item_info_template_id", LibraryFolderInfoTemplateAssociation_table.c.library_item_info_template_id )
226
227LibraryDatasetInfoTemplateAssociation_table = Table( "library_dataset_info_template_association", metadata,
228    Column( "id", Integer, primary_key=True ),
229    Column( "create_time", DateTime, default=now ),
230    Column( "update_time", DateTime, default=now, onupdate=now ),
231    Column( "library_dataset_id", Integer, ForeignKey( "library_dataset.id" ), nullable=True, index=True ),
232    Column( "library_item_info_template_id", Integer, ForeignKey( "library_item_info_template.id" ) ) )
233Index( "ix_ldita_library_item_info_template_id", LibraryDatasetInfoTemplateAssociation_table.c.library_item_info_template_id )
234
235LibraryDatasetDatasetInfoTemplateAssociation_table = Table( "library_dataset_dataset_info_template_association", metadata,
236    Column( "id", Integer, primary_key=True ),
237    Column( "create_time", DateTime, default=now ),
238    Column( "update_time", DateTime, default=now, onupdate=now ),
239    Column( "library_dataset_dataset_association_id", Integer, ForeignKey( "library_dataset_dataset_association.id" ), nullable=True ),
240    Column( "library_item_info_template_id", Integer, ForeignKey( "library_item_info_template.id" ) ) )
241Index( "ix_lddita_library_dataset_dataset_association_id", LibraryDatasetDatasetInfoTemplateAssociation_table.c.library_dataset_dataset_association_id )
242Index( "ix_lddita_library_item_info_template_id", LibraryDatasetDatasetInfoTemplateAssociation_table.c.library_item_info_template_id )
243
244LibraryItemInfoElement_table = Table( "library_item_info_element", metadata,
245    Column( "id", Integer, primary_key=True ),
246    Column( "create_time", DateTime, default=now ),
247    Column( "update_time", DateTime, default=now, onupdate=now ),
248    Column( "contents", JSONType() ),
249    Column( "library_item_info_id", Integer, ForeignKey( "library_item_info.id" ), index=True ),
250    Column( "library_item_info_template_element_id", Integer, ForeignKey( "library_item_info_template_element.id" ) ) )
251Index( "ix_liie_library_item_info_template_element_id", LibraryItemInfoElement_table.c.library_item_info_template_element_id )
252
253LibraryItemInfo_table = Table( "library_item_info", metadata,
254    Column( "id", Integer, primary_key=True ),
255    Column( "create_time", DateTime, default=now ),
256    Column( "update_time", DateTime, default=now, onupdate=now ),
257    Column( "deleted", Boolean, index=True, default=False ),
258    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), nullable=True, index=True ),
259    Column( "library_item_info_template_id", Integer, ForeignKey( "library_item_info_template.id" ), nullable=True, index=True ) )
260
261LibraryInfoAssociation_table = Table( "library_info_association", metadata,
262    Column( "id", Integer, primary_key=True ),
263    Column( "create_time", DateTime, default=now ),
264    Column( "update_time", DateTime, default=now, onupdate=now ),
265    Column( "library_id", Integer, ForeignKey( "library.id" ), nullable=True, index=True ),
266    Column( "library_item_info_id", Integer, ForeignKey( "library_item_info.id" ), index=True ),
267    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), nullable=True, index=True ) )
268
269LibraryFolderInfoAssociation_table = Table( "library_folder_info_association", metadata,
270    Column( "id", Integer, primary_key=True ),
271    Column( "create_time", DateTime, default=now ),
272    Column( "update_time", DateTime, default=now, onupdate=now ),
273    Column( "library_folder_id", Integer, ForeignKey( "library_folder.id" ), nullable=True, index=True ),
274    Column( "library_item_info_id", Integer, ForeignKey( "library_item_info.id" ), index=True ),
275    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), nullable=True, index=True ) )
276
277LibraryDatasetInfoAssociation_table = Table( "library_dataset_info_association", metadata,
278    Column( "id", Integer, primary_key=True ),
279    Column( "create_time", DateTime, default=now ),
280    Column( "update_time", DateTime, default=now, onupdate=now ),
281    Column( "library_dataset_id", Integer, ForeignKey( "library_dataset.id" ), nullable=True, index=True ),
282    Column( "library_item_info_id", Integer, ForeignKey( "library_item_info.id" ), index=True ),
283    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), nullable=True, index=True ) )
284
285LibraryDatasetDatasetInfoAssociation_table = Table( "library_dataset_dataset_info_association", metadata,
286    Column( "id", Integer, primary_key=True ),
287    Column( "create_time", DateTime, default=now ),
288    Column( "update_time", DateTime, default=now, onupdate=now ),
289    Column( "library_dataset_dataset_association_id", Integer, ForeignKey( "library_dataset_dataset_association.id" ), nullable=True ),
290    Column( "library_item_info_id", Integer, ForeignKey( "library_item_info.id" ) ),
291    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), nullable=True, index=True ) )
292Index( "ix_lddia_library_dataset_dataset_association_id", LibraryDatasetDatasetInfoAssociation_table.c.library_dataset_dataset_association_id )
293Index( "ix_lddia_library_item_info_id", LibraryDatasetDatasetInfoAssociation_table.c.library_item_info_id )
294
295JobExternalOutputMetadata_table = Table( "job_external_output_metadata", metadata,
296    Column( "id", Integer, primary_key=True ),
297    Column( "job_id", Integer, ForeignKey( "job.id" ), index=True ),
298    Column( "history_dataset_association_id", Integer, ForeignKey( "history_dataset_association.id" ), index=True, nullable=True ),
299    Column( "library_dataset_dataset_association_id", Integer, ForeignKey( "library_dataset_dataset_association.id" ), nullable=True ),
300    Column( "filename_in", String( 255 ) ),
301    Column( "filename_out", String( 255 ) ),
302    Column( "filename_results_code", String( 255 ) ),
303    Column( "filename_kwds", String( 255 ) ),
304    Column( "job_runner_external_pid", String( 255 ) ) )
305Index( "ix_jeom_library_dataset_dataset_association_id", JobExternalOutputMetadata_table.c.library_dataset_dataset_association_id )
306
307def upgrade(migrate_engine):
308    db_session = scoped_session( sessionmaker( bind=migrate_engine, autoflush=False, autocommit=True ) )
309    metadata.bind = migrate_engine
310    # Load existing tables
311    metadata.reflect()
312
313    def nextval( table, col='id' ):
314        if migrate_engine.name == 'postgres':
315            return "nextval('%s_%s_seq')" % ( table, col )
316        elif migrate_engine.name == 'mysql' or migrate_engine.name == 'sqlite':
317            return "null"
318        else:
319            raise Exception( 'Unable to convert data for unknown database type: %s' % migrate_engine.name )
320
321    def localtimestamp():
322       if migrate_engine.name == 'postgres' or migrate_engine.name == 'mysql':
323           return "LOCALTIMESTAMP"
324       elif migrate_engine.name == 'sqlite':
325           return "current_date || ' ' || current_time"
326       else:
327           raise Exception( 'Unable to convert data for unknown database type: %s' % db )
328
329    def boolean_false():
330       if migrate_engine.name == 'postgres' or migrate_engine.name == 'mysql':
331           return False
332       elif migrate_engine.name == 'sqlite':
333           return 0
334       else:
335           raise Exception( 'Unable to convert data for unknown database type: %s' % db )
336
337    # Add 2 new columns to the galaxy_user table
338    try:
339        User_table = Table( "galaxy_user", metadata, autoload=True )
340    except NoSuchTableError:
341        User_table = None
342        log.debug( "Failed loading table galaxy_user" )
343    if User_table is not None:
344        try:
345            col = Column( 'deleted', Boolean, index=True, default=False )
346            col.create( User_table, index_name='ix_user_deleted')
347            assert col is User_table.c.deleted
348        except Exception, e:
349            log.debug( "Adding column 'deleted' to galaxy_user table failed: %s" % ( str( e ) ) )
350        try:
351            col = Column( 'purged', Boolean, index=True, default=False )
352            col.create( User_table, index_name='ix_user_purged')
353            assert col is User_table.c.purged
354        except Exception, e:
355            log.debug( "Adding column 'purged' to galaxy_user table failed: %s" % ( str( e ) ) )
356    # Add 1 new column to the history_dataset_association table
357    try:
358        HistoryDatasetAssociation_table = Table( "history_dataset_association", metadata, autoload=True )
359    except NoSuchTableError:
360        HistoryDatasetAssociation_table = None
361        log.debug( "Failed loading table history_dataset_association" )
362    if HistoryDatasetAssociation_table is not None:
363        try:
364            col = Column( 'copied_from_library_dataset_dataset_association_id', Integer, nullable=True )
365            col.create( HistoryDatasetAssociation_table)
366            assert col is HistoryDatasetAssociation_table.c.copied_from_library_dataset_dataset_association_id
367        except Exception, e:
368            log.debug( "Adding column 'copied_from_library_dataset_dataset_association_id' to history_dataset_association table failed: %s" % ( str( e ) ) )
369    # Add 1 new column to the metadata_file table
370    try:
371        MetadataFile_table = Table( "metadata_file", metadata, autoload=True )
372    except NoSuchTableError:
373        MetadataFile_table = None
374        log.debug( "Failed loading table metadata_file" )
375    if MetadataFile_table is not None:
376        try:
377            col = Column( 'lda_id', Integer, index=True, nullable=True )
378            col.create( MetadataFile_table, index_name='ix_metadata_file_lda_id')
379            assert col is MetadataFile_table.c.lda_id
380        except Exception, e:
381            log.debug( "Adding column 'lda_id' to metadata_file table failed: %s" % ( str( e ) ) )
382    # Add 1 new column to the stored_workflow table - changeset 2328
383    try:
384        StoredWorkflow_table = Table( "stored_workflow", metadata,
385            Column( "latest_workflow_id", Integer,
386                ForeignKey( "workflow.id", use_alter=True, name='stored_workflow_latest_workflow_id_fk' ), index=True ),
387            autoload=True, extend_existing=True )
388    except NoSuchTableError:
389        StoredWorkflow_table = None
390        log.debug( "Failed loading table stored_workflow" )
391    if StoredWorkflow_table is not None:
392        try:
393            col = Column( 'importable', Boolean, default=False )
394            col.create( StoredWorkflow_table )
395            assert col is StoredWorkflow_table.c.importable
396        except Exception, e:
397            log.debug( "Adding column 'importable' to stored_workflow table failed: %s" % ( str( e ) ) )
398    # Create an index on the Job.state column - changeset 2192
399    try:
400        Job_table = Table( "job", metadata, autoload=True )
401    except NoSuchTableError:
402        Job_table = None
403        log.debug( "Failed loading table job" )
404    if Job_table is not None:
405        try:
406            i = Index( 'ix_job_state', Job_table.c.state )
407            i.create()
408        except Exception, e:
409            log.debug( "Adding index to job.state column failed: %s" % ( str( e ) ) )
410    # Add all of the new tables above
411    metadata.create_all()
412    # Add 1 foreign key constraint to the history_dataset_association table
413    try:
414        HistoryDatasetAssociation_table = Table( "history_dataset_association", metadata, autoload=True )
415    except NoSuchTableError:
416        HistoryDatasetAssociation_table = None
417        log.debug( "Failed loading table history_dataset_association" )
418    try:
419        LibraryDatasetDatasetAssociation_table = Table( "library_dataset_dataset_association", metadata, autoload=True )
420    except NoSuchTableError:
421        LibraryDatasetDatasetAssociation_table = None
422        log.debug( "Failed loading table library_dataset_dataset_association" )
423    if HistoryDatasetAssociation_table is not None and LibraryDatasetDatasetAssociation_table is not None:
424        try:
425            cons = ForeignKeyConstraint( [HistoryDatasetAssociation_table.c.copied_from_library_dataset_dataset_association_id],
426                                         [LibraryDatasetDatasetAssociation_table.c.id],
427                                         name='history_dataset_association_copied_from_library_dataset_da_fkey' )
428            # Create the constraint
429            cons.create()
430        except Exception, e:
431            log.debug( "Adding foreign key constraint 'history_dataset_association_copied_from_library_dataset_da_fkey' to table 'history_dataset_association' failed: %s" % ( str( e ) ) )
432    # Add 1 foreign key constraint to the metadata_file table
433    try:
434        MetadataFile_table = Table( "metadata_file", metadata, autoload=True )
435    except NoSuchTableError:
436        MetadataFile_table = None
437        log.debug( "Failed loading table metadata_file" )
438    try:
439        LibraryDatasetDatasetAssociation_table = Table( "library_dataset_dataset_association", metadata, autoload=True )
440    except NoSuchTableError:
441        LibraryDatasetDatasetAssociation_table = None
442        log.debug( "Failed loading table library_dataset_dataset_association" )
443    if migrate_engine.name != 'sqlite':
444        #Sqlite can't alter table add foreign key.
445        if MetadataFile_table is not None and LibraryDatasetDatasetAssociation_table is not None:
446            try:
447                cons = ForeignKeyConstraint( [MetadataFile_table.c.lda_id],
448                                             [LibraryDatasetDatasetAssociation_table.c.id],
449                                             name='metadata_file_lda_id_fkey' )
450                # Create the constraint
451                cons.create()
452            except Exception, e:
453                log.debug( "Adding foreign key constraint 'metadata_file_lda_id_fkey' to table 'metadata_file' failed: %s" % ( str( e ) ) )
454    # Make sure we have at least 1 user
455    cmd = "SELECT * FROM galaxy_user;"
456    users = db_session.execute( cmd ).fetchall()
457    if users:
458        cmd = "SELECT * FROM role;"
459        roles = db_session.execute( cmd ).fetchall()
460        if not roles:
461            # Create private roles for each user - pass 1
462            cmd = \
463                "INSERT INTO role " + \
464                "SELECT %s AS id," + \
465                "%s AS create_time," + \
466                "%s AS update_time," + \
467                "email AS name," + \
468                "email AS description," + \
469                "'private' As type," + \
470                "%s AS deleted " + \
471                "FROM galaxy_user " + \
472                "ORDER BY id;"
473            cmd = cmd % ( nextval('role'), localtimestamp(), localtimestamp(), boolean_false() )
474            db_session.execute( cmd )
475            # Create private roles for each user - pass 2
476            if migrate_engine.name == 'postgres' or migrate_engine.name == 'sqlite':
477                cmd = "UPDATE role SET description = 'Private role for ' || description;"
478            elif migrate_engine.name == 'mysql':
479                cmd = "UPDATE role SET description = CONCAT( 'Private role for ', description );"
480            db_session.execute( cmd )
481            # Create private roles for each user - pass 3
482            cmd = \
483                "INSERT INTO user_role_association " + \
484                "SELECT %s AS id," + \
485                "galaxy_user.id AS user_id," + \
486                "role.id AS role_id," + \
487                "%s AS create_time," + \
488                "%s AS update_time " + \
489                "FROM galaxy_user, role " + \
490                "WHERE galaxy_user.email = role.name " + \
491                "ORDER BY galaxy_user.id;"
492            cmd = cmd % ( nextval('user_role_association'), localtimestamp(), localtimestamp() )
493            db_session.execute( cmd )
494            # Create default permissions for each user
495            cmd = \
496                "INSERT INTO default_user_permissions " + \
497                "SELECT %s AS id," + \
498                "galaxy_user.id AS user_id," + \
499                "'manage permissions' AS action," + \
500                "user_role_association.role_id AS role_id " + \
501                "FROM galaxy_user " + \
502                "JOIN user_role_association ON user_role_association.user_id = galaxy_user.id " + \
503                "ORDER BY galaxy_user.id;"
504            cmd = cmd % nextval('default_user_permissions')
505            db_session.execute( cmd )
506            # Create default history permissions for each active history associated with a user
507
508            cmd = \
509                "INSERT INTO default_history_permissions " + \
510                "SELECT %s AS id," + \
511                "history.id AS history_id," + \
512                "'manage permissions' AS action," + \
513                "user_role_association.role_id AS role_id " + \
514                "FROM history " + \
515                "JOIN user_role_association ON user_role_association.user_id = history.user_id " + \
516                "WHERE history.purged = %s AND history.user_id IS NOT NULL;"
517            cmd = cmd % ( nextval('default_history_permissions'), boolean_false() )
518            db_session.execute( cmd )
519            # Create "manage permissions" dataset_permissions for all activate-able datasets
520            cmd = \
521                "INSERT INTO dataset_permissions " + \
522                "SELECT %s AS id," + \
523                "%s AS create_time," + \
524                "%s AS update_time," + \
525                "'manage permissions' AS action," + \
526                "history_dataset_association.dataset_id AS dataset_id," + \
527                "user_role_association.role_id AS role_id " + \
528                "FROM history " + \
529                "JOIN history_dataset_association ON history_dataset_association.history_id = history.id " + \
530                "JOIN dataset ON history_dataset_association.dataset_id = dataset.id " + \
531                "JOIN user_role_association ON user_role_association.user_id = history.user_id " + \
532                "WHERE dataset.purged = %s AND history.user_id IS NOT NULL;"
533            cmd = cmd % ( nextval('dataset_permissions'), localtimestamp(), localtimestamp(), boolean_false() )
534            db_session.execute( cmd )
535
536def downgrade(migrate_engine):
537    metadata.bind = migrate_engine
538    if migrate_engine.name == 'postgres':
539        # http://blog.pythonisito.com/2008/01/cascading-drop-table-with-sqlalchemy.html
540        from sqlalchemy.databases import postgres
541        class PGCascadeSchemaDropper(postgres.PGSchemaDropper):
542            def visit_table(self, table):
543                for column in table.columns:
544                    if column.default is not None:
545                        self.traverse_single(column.default)
546                self.append("\nDROP TABLE " +
547                            self.preparer.format_table(table) +
548                            " CASCADE")
549                self.execute()
550        postgres.dialect.schemadropper = PGCascadeSchemaDropper
551    # Load existing tables
552    metadata.reflect()
553    # NOTE: all new data added in the upgrade method is eliminated here via table drops
554    # Drop 1 foreign key constraint from the metadata_file table
555    try:
556        MetadataFile_table = Table( "metadata_file", metadata, autoload=True )
557    except NoSuchTableError:
558        MetadataFile_table = None
559        log.debug( "Failed loading table metadata_file" )
560    try:
561        LibraryDatasetDatasetAssociation_table = Table( "library_dataset_dataset_association", metadata, autoload=True )
562    except NoSuchTableError:
563        LibraryDatasetDatasetAssociation_table = None
564        log.debug( "Failed loading table library_dataset_dataset_association" )
565    if MetadataFile_table is not None and LibraryDatasetDatasetAssociation_table is not None:
566        try:
567            cons = ForeignKeyConstraint( [MetadataFile_table.c.lda_id],
568                                         [LibraryDatasetDatasetAssociation_table.c.id],
569                                         name='metadata_file_lda_id_fkey' )
570            # Drop the constraint
571            cons.drop()
572        except Exception, e:
573            log.debug( "Dropping foreign key constraint 'metadata_file_lda_id_fkey' from table 'metadata_file' failed: %s" % ( str( e ) ) )
574    # Drop 1 foreign key constraint from the history_dataset_association table
575    try:
576        HistoryDatasetAssociation_table = Table( "history_dataset_association", metadata, autoload=True )
577    except NoSuchTableError:
578        HistoryDatasetAssociation_table = None
579        log.debug( "Failed loading table history_dataset_association" )
580    try:
581        LibraryDatasetDatasetAssociation_table = Table( "library_dataset_dataset_association", metadata, autoload=True )
582    except NoSuchTableError:
583        LibraryDatasetDatasetAssociation_table = None
584        log.debug( "Failed loading table library_dataset_dataset_association" )
585    if HistoryDatasetAssociation_table is not None and LibraryDatasetDatasetAssociation_table is not None:
586        try:
587            cons = ForeignKeyConstraint( [HistoryDatasetAssociation_table.c.copied_from_library_dataset_dataset_association_id],
588                                         [LibraryDatasetDatasetAssociation_table.c.id],
589                                         name='history_dataset_association_copied_from_library_dataset_da_fkey' )
590            # Drop the constraint
591            cons.drop()
592        except Exception, e:
593            log.debug( "Dropping foreign key constraint 'history_dataset_association_copied_from_library_dataset_da_fkey' from table 'history_dataset_association' failed: %s" % ( str( e ) ) )
594    # Drop all of the new tables above
595    try:
596        UserGroupAssociation_table.drop()
597    except Exception, e:
598        log.debug( "Dropping user_group_association table failed: %s" % str( e ) )
599    try:
600        UserRoleAssociation_table.drop()
601    except Exception, e:
602        log.debug( "Dropping user_role_association table failed: %s" % str( e ) )
603    try:
604        GroupRoleAssociation_table.drop()
605    except Exception, e:
606        log.debug( "Dropping group_role_association table failed: %s" % str( e ) )
607    try:
608        Group_table.drop()
609    except Exception, e:
610        log.debug( "Dropping galaxy_group table failed: %s" % str( e ) )
611    try:
612        DatasetPermissions_table.drop()
613    except Exception, e:
614        log.debug( "Dropping dataset_permissions table failed: %s" % str( e ) )
615    try:
616        LibraryPermissions_table.drop()
617    except Exception, e:
618        log.debug( "Dropping library_permissions table failed: %s" % str( e ) )
619    try:
620        LibraryFolderPermissions_table.drop()
621    except Exception, e:
622        log.debug( "Dropping library_folder_permissions table failed: %s" % str( e ) )
623    try:
624        LibraryDatasetPermissions_table.drop()
625    except Exception, e:
626        log.debug( "Dropping library_dataset_permissions table failed: %s" % str( e ) )
627    try:
628        LibraryDatasetDatasetAssociationPermissions_table.drop()
629    except Exception, e:
630        log.debug( "Dropping library_dataset_dataset_association_permissions table failed: %s" % str( e ) )
631    try:
632        LibraryItemInfoPermissions_table.drop()
633    except Exception, e:
634        log.debug( "Dropping library_item_info_permissions table failed: %s" % str( e ) )
635    try:
636        LibraryItemInfoTemplatePermissions_table.drop()
637    except Exception, e:
638        log.debug( "Dropping library_item_info_template_permissions table failed: %s" % str( e ) )
639    try:
640        DefaultUserPermissions_table.drop()
641    except Exception, e:
642        log.debug( "Dropping default_user_permissions table failed: %s" % str( e ) )
643    try:
644        DefaultHistoryPermissions_table.drop()
645    except Exception, e:
646        log.debug( "Dropping default_history_permissions table failed: %s" % str( e ) )
647    try:
648        Role_table.drop()
649    except Exception, e:
650        log.debug( "Dropping role table failed: %s" % str( e ) )
651    try:
652        LibraryDatasetDatasetInfoAssociation_table.drop()
653    except Exception, e:
654        log.debug( "Dropping library_dataset_dataset_info_association table failed: %s" % str( e ) )
655    try:
656        LibraryDataset_table.drop()
657    except Exception, e:
658        log.debug( "Dropping library_dataset table failed: %s" % str( e ) )
659    try:
660        LibraryDatasetDatasetAssociation_table.drop()
661    except Exception, e:
662        log.debug( "Dropping library_dataset_dataset_association table failed: %s" % str( e ) )
663    try:
664        LibraryDatasetDatasetInfoTemplateAssociation_table.drop()
665    except Exception, e:
666        log.debug( "Dropping library_dataset_dataset_info_template_association table failed: %s" % str( e ) )
667    try:
668        JobExternalOutputMetadata_table.drop()
669    except Exception, e:
670        log.debug( "Dropping job_external_output_metadata table failed: %s" % str( e ) )
671    try:
672        Library_table.drop()
673    except Exception, e:
674        log.debug( "Dropping library table failed: %s" % str( e ) )
675    try:
676        LibraryFolder_table.drop()
677    except Exception, e:
678        log.debug( "Dropping library_folder table failed: %s" % str( e ) )
679    try:
680        LibraryItemInfoTemplateElement_table.drop()
681    except Exception, e:
682        log.debug( "Dropping library_item_info_template_element table failed: %s" % str( e ) )
683    try:
684        LibraryInfoTemplateAssociation_table.drop()
685    except Exception, e:
686        log.debug( "Dropping library_info_template_association table failed: %s" % str( e ) )
687    try:
688        LibraryFolderInfoTemplateAssociation_table.drop()
689    except Exception, e:
690        log.debug( "Dropping library_folder_info_template_association table failed: %s" % str( e ) )
691    try:
692        LibraryDatasetInfoTemplateAssociation_table.drop()
693    except Exception, e:
694        log.debug( "Dropping library_dataset_info_template_association table failed: %s" % str( e ) )
695    try:
696        LibraryInfoAssociation_table.drop()
697    except Exception, e:
698        log.debug( "Dropping library_info_association table failed: %s" % str( e ) )
699    try:
700        LibraryFolderInfoAssociation_table.drop()
701    except Exception, e:
702        log.debug( "Dropping library_folder_info_association table failed: %s" % str( e ) )
703    try:
704        LibraryDatasetInfoAssociation_table.drop()
705    except Exception, e:
706        log.debug( "Dropping library_dataset_info_association table failed: %s" % str( e ) )
707    try:
708        LibraryItemInfoElement_table.drop()
709    except Exception, e:
710        log.debug( "Dropping library_item_info_element table failed: %s" % str( e ) )
711    try:
712        LibraryItemInfo_table.drop()
713    except Exception, e:
714        log.debug( "Dropping library_item_info table failed: %s" % str( e ) )
715    try:
716        LibraryItemInfoTemplate_table.drop()
717    except Exception, e:
718        log.debug( "Dropping library_item_info_template table failed: %s" % str( e ) )
719    # Drop the index on the Job.state column - changeset 2192
720    try:
721        Job_table = Table( "job", metadata, autoload=True )
722    except NoSuchTableError:
723        Job_table = None
724        log.debug( "Failed loading table job" )
725    if Job_table is not None:
726        try:
727            i = Index( 'ix_job_state', Job_table.c.state )
728            i.drop()
729        except Exception, e:
730            log.debug( "Dropping index from job.state column failed: %s" % ( str( e ) ) )
731    # Drop 1 column from the stored_workflow table - changeset 2328
732    try:
733        StoredWorkflow_table = Table( "stored_workflow", metadata, autoload=True )
734    except NoSuchTableError:
735        StoredWorkflow_table = None
736        log.debug( "Failed loading table stored_workflow" )
737    if StoredWorkflow_table is not None:
738        try:
739            col = StoredWorkflow_table.c.importable
740            col.drop()
741        except Exception, e:
742            log.debug( "Dropping column 'importable' from stored_workflow table failed: %s" % ( str( e ) ) )
743    # Drop 1 column from the metadata_file table
744    try:
745        MetadataFile_table = Table( "metadata_file", metadata, autoload=True )
746    except NoSuchTableError:
747        MetadataFile_table = None
748        log.debug( "Failed loading table metadata_file" )
749    if MetadataFile_table is not None:
750        try:
751            col = MetadataFile_table.c.lda_id
752            col.drop()
753        except Exception, e:
754            log.debug( "Dropping column 'lda_id' from metadata_file table failed: %s" % ( str( e ) ) )
755    # Drop 1 column from the history_dataset_association table
756    try:
757        HistoryDatasetAssociation_table = Table( "history_dataset_association", metadata, autoload=True )
758    except NoSuchTableError:
759        HistoryDatasetAssociation_table = None
760        log.debug( "Failed loading table history_dataset_association" )
761    if HistoryDatasetAssociation_table is not None:
762        try:
763            col = HistoryDatasetAssociation_table.c.copied_from_library_dataset_dataset_association_id
764            col.drop()
765        except Exception, e:
766            log.debug( "Dropping column 'copied_from_library_dataset_dataset_association_id' from history_dataset_association table failed: %s" % ( str( e ) ) )
767    # Drop 2 columns from the galaxy_user table
768    try:
769        User_table = Table( "galaxy_user", metadata, autoload=True )
770    except NoSuchTableError:
771        User_table = None
772        log.debug( "Failed loading table galaxy_user" )
773    if User_table is not None:
774        try:
775            col = User_table.c.deleted
776            col.drop()
777        except Exception, e:
778            log.debug( "Dropping column 'deleted' from galaxy_user table failed: %s" % ( str( e ) ) )
779        try:
780            col = User_table.c.purged
781            col.drop()
782        except Exception, e:
783            log.debug( "Dropping column 'purged' from galaxy_user table failed: %s" % ( str( e ) ) )