root/galaxy-central/lib/galaxy/model/migrate/versions/0003_security_and_libraries.py

リビジョン 2, 40.6 KB (コミッタ: hatakeyama, 14 年 前)

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