root/galaxy-central/lib/galaxy/model/mapping.py @ 2

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

import galaxy-central
行番号 
1"""
2Details of how the data model objects are mapped onto the relational database
3are encapsulated here.
4"""
5import logging
6log = logging.getLogger( __name__ )
7
8import sys
9import datetime
10
11from galaxy.model import *
12from galaxy.model.orm import *
13from galaxy.model.orm.ext.assignmapper import *
14from galaxy.model.custom_types import *
15from galaxy.util.bunch import Bunch
16from galaxy.security import GalaxyRBACAgent
17from sqlalchemy.orm.collections import attribute_mapped_collection
18from sqlalchemy.ext.associationproxy import association_proxy
19
20metadata = MetaData()
21context = Session = scoped_session( sessionmaker( autoflush=False, autocommit=True ) )
22
23# For backward compatibility with "context.current"
24context.current = Session
25
26dialect_to_egg = {
27    "sqlite"   : "pysqlite>=2",
28    "postgres" : "psycopg2",
29    "mysql"    : "MySQL_python"
30}
31
32# NOTE REGARDING TIMESTAMPS:
33#   It is currently difficult to have the timestamps calculated by the
34#   database in a portable way, so we're doing it in the client. This
35#   also saves us from needing to postfetch on postgres. HOWEVER: it
36#   relies on the client's clock being set correctly, so if clustering
37#   web servers, use a time server to ensure synchronization
38
39# Return the current time in UTC without any timezone information
40now = datetime.datetime.utcnow
41
42User.table = Table( "galaxy_user", metadata,
43    Column( "id", Integer, primary_key=True),
44    Column( "create_time", DateTime, default=now ),
45    Column( "update_time", DateTime, default=now, onupdate=now ),
46    Column( "email", TrimmedString( 255 ), nullable=False ),
47    Column( "username", TrimmedString( 255 ), index=True, unique=True ),
48    Column( "password", TrimmedString( 40 ), nullable=False ),
49    Column( "external", Boolean, default=False ),
50    Column( "form_values_id", Integer, ForeignKey( "form_values.id" ), index=True ),
51    Column( "deleted", Boolean, index=True, default=False ),
52    Column( "purged", Boolean, index=True, default=False ) )
53
54UserAddress.table = Table( "user_address", metadata,
55    Column( "id", Integer, primary_key=True),
56    Column( "create_time", DateTime, default=now ),
57    Column( "update_time", DateTime, default=now, onupdate=now ),
58    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
59    Column( "desc", TrimmedString( 255 )),   
60    Column( "name", TrimmedString( 255 ), nullable=False),
61    Column( "institution", TrimmedString( 255 )),
62    Column( "address", TrimmedString( 255 ), nullable=False),
63    Column( "city", TrimmedString( 255 ), nullable=False),
64    Column( "state", TrimmedString( 255 ), nullable=False),
65    Column( "postal_code", TrimmedString( 255 ), nullable=False),
66    Column( "country", TrimmedString( 255 ), nullable=False),
67    Column( "phone", TrimmedString( 255 )),
68    Column( "deleted", Boolean, index=True, default=False ),
69    Column( "purged", Boolean, index=True, default=False ) )
70
71History.table = Table( "history", metadata,
72    Column( "id", Integer, primary_key=True),
73    Column( "create_time", DateTime, default=now ),
74    Column( "update_time", DateTime, index=True, default=now, onupdate=now ),
75    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
76    Column( "name", TrimmedString( 255 ) ),
77    Column( "hid_counter", Integer, default=1 ),
78    Column( "deleted", Boolean, index=True, default=False ),
79    Column( "purged", Boolean, index=True, default=False ),
80    Column( "genome_build", TrimmedString( 40 ) ),
81    Column( "importable", Boolean, default=False ),
82    Column( "slug", TEXT, index=True ),
83    Column( "published", Boolean, index=True, default=False ) )
84
85HistoryUserShareAssociation.table = Table( "history_user_share_association", metadata,
86    Column( "id", Integer, primary_key=True ),
87    Column( "history_id", Integer, ForeignKey( "history.id" ), index=True ),
88    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True )
89    )
90
91HistoryDatasetAssociation.table = Table( "history_dataset_association", metadata,
92    Column( "id", Integer, primary_key=True ),
93    Column( "history_id", Integer, ForeignKey( "history.id" ), index=True ),
94    Column( "dataset_id", Integer, ForeignKey( "dataset.id" ), index=True ),
95    Column( "create_time", DateTime, default=now ),
96    Column( "update_time", DateTime, default=now, onupdate=now ),
97    Column( "state", TrimmedString( 64 ), index=True, key="_state" ),
98    Column( "copied_from_history_dataset_association_id", Integer, ForeignKey( "history_dataset_association.id" ), nullable=True ),
99    Column( "copied_from_library_dataset_dataset_association_id", Integer, ForeignKey( "library_dataset_dataset_association.id" ), nullable=True ),
100    Column( "hid", Integer ),
101    Column( "name", TrimmedString( 255 ) ),
102    Column( "info", TrimmedString( 255 ) ),
103    Column( "blurb", TrimmedString( 255 ) ),
104    Column( "peek" , TEXT ),
105    Column( "extension", TrimmedString( 64 ) ),
106    Column( "metadata", MetadataType(), key="_metadata" ),
107    Column( "parent_id", Integer, ForeignKey( "history_dataset_association.id" ), nullable=True ),
108    Column( "designation", TrimmedString( 255 ) ),
109    Column( "deleted", Boolean, index=True, default=False ),
110    Column( "visible", Boolean ) )
111
112Dataset.table = Table( "dataset", metadata,
113    Column( "id", Integer, primary_key=True ),
114    Column( "create_time", DateTime, default=now ),
115    Column( "update_time", DateTime, index=True, default=now, onupdate=now ),
116    Column( "state", TrimmedString( 64 ), index=True ),
117    Column( "deleted", Boolean, index=True, default=False ),
118    Column( "purged", Boolean, index=True, default=False ),
119    Column( "purgable", Boolean, default=True ),
120    Column( "external_filename" , TEXT ),
121    Column( "_extra_files_path", TEXT ),
122    Column( 'file_size', Numeric( 15, 0 ) ) )
123
124HistoryDatasetAssociationDisplayAtAuthorization.table = Table( "history_dataset_association_display_at_authorization", metadata,
125    Column( "id", Integer, primary_key=True ),
126    Column( "create_time", DateTime, default=now ),
127    Column( "update_time", DateTime, index=True, default=now, onupdate=now ),
128    Column( "history_dataset_association_id", Integer, ForeignKey( "history_dataset_association.id" ), index=True ),
129    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
130    Column( "site", TrimmedString( 255 ) ) )
131
132ImplicitlyConvertedDatasetAssociation.table = Table( "implicitly_converted_dataset_association", metadata,
133    Column( "id", Integer, primary_key=True ),
134    Column( "create_time", DateTime, default=now ),
135    Column( "update_time", DateTime, default=now, onupdate=now ),
136    Column( "hda_id", Integer, ForeignKey( "history_dataset_association.id" ), index=True, nullable=True ),
137    Column( "hda_parent_id", Integer, ForeignKey( "history_dataset_association.id" ), index=True ),
138    Column( "deleted", Boolean, index=True, default=False ),
139    Column( "metadata_safe", Boolean, index=True, default=True ),
140    Column( "type", TrimmedString( 255 ) ) )
141
142ValidationError.table = Table( "validation_error", metadata,
143    Column( "id", Integer, primary_key=True ),
144    Column( "dataset_id", Integer, ForeignKey( "history_dataset_association.id" ), index=True ),
145    Column( "message", TrimmedString( 255 ) ),
146    Column( "err_type", TrimmedString( 64 ) ),
147    Column( "attributes", TEXT ) )
148
149Group.table = Table( "galaxy_group", metadata,
150    Column( "id", Integer, primary_key=True ),
151    Column( "create_time", DateTime, default=now ),
152    Column( "update_time", DateTime, default=now, onupdate=now ),
153    Column( "name", String( 255 ), index=True, unique=True ),
154    Column( "deleted", Boolean, index=True, default=False ) )
155
156UserGroupAssociation.table = Table( "user_group_association", metadata,
157    Column( "id", Integer, primary_key=True ),
158    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
159    Column( "group_id", Integer, ForeignKey( "galaxy_group.id" ), index=True ),
160    Column( "create_time", DateTime, default=now ),
161    Column( "update_time", DateTime, default=now, onupdate=now ) )
162
163UserRoleAssociation.table = Table( "user_role_association", metadata,
164    Column( "id", Integer, primary_key=True ),
165    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
166    Column( "role_id", Integer, ForeignKey( "role.id" ), index=True ),
167    Column( "create_time", DateTime, default=now ),
168    Column( "update_time", DateTime, default=now, onupdate=now ) )
169
170GroupRoleAssociation.table = Table( "group_role_association", metadata,
171    Column( "id", Integer, primary_key=True ),
172    Column( "group_id", Integer, ForeignKey( "galaxy_group.id" ), index=True ),
173    Column( "role_id", Integer, ForeignKey( "role.id" ), index=True ),
174    Column( "create_time", DateTime, default=now ),
175    Column( "update_time", DateTime, default=now, onupdate=now ) )
176
177Role.table = Table( "role", metadata,
178    Column( "id", Integer, primary_key=True ),
179    Column( "create_time", DateTime, default=now ),
180    Column( "update_time", DateTime, default=now, onupdate=now ),
181    Column( "name", String( 255 ), index=True, unique=True ),
182    Column( "description", TEXT ),
183    Column( "type", String( 40 ), index=True ),
184    Column( "deleted", Boolean, index=True, default=False ) )
185
186DatasetPermissions.table = Table( "dataset_permissions", metadata,
187    Column( "id", Integer, primary_key=True ),
188    Column( "create_time", DateTime, default=now ),
189    Column( "update_time", DateTime, default=now, onupdate=now ),
190    Column( "action", TEXT ),
191    Column( "dataset_id", Integer, ForeignKey( "dataset.id" ), index=True ),
192    Column( "role_id", Integer, ForeignKey( "role.id" ), index=True ) )
193
194LibraryPermissions.table = Table( "library_permissions", metadata,
195    Column( "id", Integer, primary_key=True ),
196    Column( "create_time", DateTime, default=now ),
197    Column( "update_time", DateTime, default=now, onupdate=now ),
198    Column( "action", TEXT ),
199    Column( "library_id", Integer, ForeignKey( "library.id" ), nullable=True, index=True ),
200    Column( "role_id", Integer, ForeignKey( "role.id" ), index=True ) )
201
202LibraryFolderPermissions.table = Table( "library_folder_permissions", metadata,
203    Column( "id", Integer, primary_key=True ),
204    Column( "create_time", DateTime, default=now ),
205    Column( "update_time", DateTime, default=now, onupdate=now ),
206    Column( "action", TEXT ),
207    Column( "library_folder_id", Integer, ForeignKey( "library_folder.id" ), nullable=True, index=True ),
208    Column( "role_id", Integer, ForeignKey( "role.id" ), index=True ) )
209
210LibraryDatasetPermissions.table = Table( "library_dataset_permissions", metadata,
211    Column( "id", Integer, primary_key=True ),
212    Column( "create_time", DateTime, default=now ),
213    Column( "update_time", DateTime, default=now, onupdate=now ),
214    Column( "action", TEXT ),
215    Column( "library_dataset_id", Integer, ForeignKey( "library_dataset.id" ), nullable=True, index=True ),
216    Column( "role_id", Integer, ForeignKey( "role.id" ), index=True ) )
217
218LibraryDatasetDatasetAssociationPermissions.table = Table( "library_dataset_dataset_association_permissions", metadata,
219    Column( "id", Integer, primary_key=True ),
220    Column( "create_time", DateTime, default=now ),
221    Column( "update_time", DateTime, default=now, onupdate=now ),
222    Column( "action", TEXT ),
223    Column( "library_dataset_dataset_association_id", Integer, ForeignKey( "library_dataset_dataset_association.id" ), nullable=True, index=True ),
224    Column( "role_id", Integer, ForeignKey( "role.id" ), index=True ) )
225
226DefaultUserPermissions.table = Table( "default_user_permissions", metadata,
227    Column( "id", Integer, primary_key=True ),
228    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
229    Column( "action", TEXT ),
230    Column( "role_id", Integer, ForeignKey( "role.id" ), index=True ) )
231
232DefaultHistoryPermissions.table = Table( "default_history_permissions", metadata,
233    Column( "id", Integer, primary_key=True ),
234    Column( "history_id", Integer, ForeignKey( "history.id" ), index=True ),
235    Column( "action", TEXT ),
236    Column( "role_id", Integer, ForeignKey( "role.id" ), index=True ) )
237
238LibraryDataset.table = Table( "library_dataset", metadata,
239    Column( "id", Integer, primary_key=True ),
240    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
241    Column( "folder_id", Integer, ForeignKey( "library_folder.id" ), index=True ),
242    Column( "order_id", Integer ), #not currently being used, but for possible future use
243    Column( "create_time", DateTime, default=now ),
244    Column( "update_time", DateTime, default=now, onupdate=now ),
245    Column( "name", TrimmedString( 255 ), key="_name", index=True ), #when not None/null this will supercede display in library (but not when imported into user's history?)
246    Column( "info", TrimmedString( 255 ),  key="_info" ), #when not None/null this will supercede display in library (but not when imported into user's history?)
247    Column( "deleted", Boolean, index=True, default=False ) )
248
249LibraryDatasetDatasetAssociation.table = Table( "library_dataset_dataset_association", metadata,
250    Column( "id", Integer, primary_key=True ),
251    Column( "library_dataset_id", Integer, ForeignKey( "library_dataset.id" ), index=True ),
252    Column( "dataset_id", Integer, ForeignKey( "dataset.id" ), index=True ),
253    Column( "create_time", DateTime, default=now ),
254    Column( "update_time", DateTime, default=now, onupdate=now ),
255    Column( "state", TrimmedString( 64 ), index=True, key="_state" ),
256    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 ),
257    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 ),
258    Column( "name", TrimmedString( 255 ), index=True ),
259    Column( "info", TrimmedString( 255 ) ),
260    Column( "blurb", TrimmedString( 255 ) ),
261    Column( "peek" , TEXT ),
262    Column( "extension", TrimmedString( 64 ) ),
263    Column( "metadata", MetadataType(), key="_metadata" ),
264    Column( "parent_id", Integer, ForeignKey( "library_dataset_dataset_association.id" ), nullable=True ),
265    Column( "designation", TrimmedString( 255 ) ),
266    Column( "deleted", Boolean, index=True, default=False ),
267    Column( "visible", Boolean ),
268    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
269    Column( "message", TrimmedString( 255 ) ) )
270
271Library.table = Table( "library", metadata,
272    Column( "id", Integer, primary_key=True ),
273    Column( "root_folder_id", Integer, ForeignKey( "library_folder.id" ), index=True ),
274    Column( "create_time", DateTime, default=now ),
275    Column( "update_time", DateTime, default=now, onupdate=now ),
276    Column( "name", String( 255 ), index=True ),
277    Column( "deleted", Boolean, index=True, default=False ),
278    Column( "purged", Boolean, index=True, default=False ),
279    Column( "description", TEXT ),
280    Column( "synopsis", TEXT ) )
281
282LibraryFolder.table = Table( "library_folder", metadata,
283    Column( "id", Integer, primary_key=True ),
284    Column( "parent_id", Integer, ForeignKey( "library_folder.id" ), nullable = True, index=True ),
285    Column( "create_time", DateTime, default=now ),
286    Column( "update_time", DateTime, default=now, onupdate=now ),
287    Column( "name", TEXT, index=True ),
288    Column( "description", TEXT ),
289    Column( "order_id", Integer ), #not currently being used, but for possible future use
290    Column( "item_count", Integer ),
291    Column( "deleted", Boolean, index=True, default=False ),
292    Column( "purged", Boolean, index=True, default=False ),
293    Column( "genome_build", TrimmedString( 40 ) ) )
294
295LibraryInfoAssociation.table = Table( 'library_info_association', metadata,
296    Column( "id", Integer, primary_key=True ),
297    Column( "library_id", Integer, ForeignKey( "library.id" ), index=True ),
298    Column( "form_definition_id", Integer, ForeignKey( "form_definition.id" ), index=True ),
299    Column( "form_values_id", Integer, ForeignKey( "form_values.id" ), index=True ),
300    Column( "inheritable", Boolean, index=True, default=False ),
301    Column( "deleted", Boolean, index=True, default=False ) )
302
303LibraryFolderInfoAssociation.table = Table( 'library_folder_info_association', metadata,
304    Column( "id", Integer, primary_key=True ),
305    Column( "library_folder_id", Integer, ForeignKey( "library_folder.id" ), nullable=True, index=True ),
306    Column( "form_definition_id", Integer, ForeignKey( "form_definition.id" ), index=True ),
307    Column( "form_values_id", Integer, ForeignKey( "form_values.id" ), index=True ),
308    Column( "inheritable", Boolean, index=True, default=False ),
309    Column( "deleted", Boolean, index=True, default=False ) )
310
311LibraryDatasetDatasetInfoAssociation.table = Table( 'library_dataset_dataset_info_association', metadata,
312    Column( "id", Integer, primary_key=True ),
313    Column( "library_dataset_dataset_association_id", Integer, ForeignKey( "library_dataset_dataset_association.id" ), nullable=True, index=True ),
314    Column( "form_definition_id", Integer, ForeignKey( "form_definition.id" ), index=True ),
315    Column( "form_values_id", Integer, ForeignKey( "form_values.id" ), index=True ),
316    Column( "deleted", Boolean, index=True, default=False ) )
317
318Job.table = Table( "job", metadata,
319    Column( "id", Integer, primary_key=True ),
320    Column( "create_time", DateTime, default=now ),
321    Column( "update_time", DateTime, default=now, onupdate=now ),
322    Column( "history_id", Integer, ForeignKey( "history.id" ), index=True ),
323    Column( "library_folder_id", Integer, ForeignKey( "library_folder.id" ), index=True ),
324    Column( "tool_id", String( 255 ) ),
325    Column( "tool_version", TEXT, default="1.0.0" ),
326    Column( "state", String( 64 ), index=True ),
327    Column( "info", TrimmedString( 255 ) ),
328    Column( "command_line", TEXT ),
329    Column( "param_filename", String( 1024 ) ),
330    Column( "runner_name", String( 255 ) ),
331    Column( "stdout", TEXT ),
332    Column( "stderr", TEXT ),
333    Column( "traceback", TEXT ),
334    Column( "session_id", Integer, ForeignKey( "galaxy_session.id" ), index=True, nullable=True ),
335    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True, nullable=True ),
336    Column( "job_runner_name", String( 255 ) ),
337    Column( "job_runner_external_id", String( 255 ) ),
338    Column( "imported", Boolean, default=False, index=True ) )
339   
340JobParameter.table = Table( "job_parameter", metadata,
341    Column( "id", Integer, primary_key=True ),
342    Column( "job_id", Integer, ForeignKey( "job.id" ), index=True ),
343    Column( "name", String(255) ),
344    Column( "value", TEXT ) )
345   
346JobToInputDatasetAssociation.table = Table( "job_to_input_dataset", metadata,
347    Column( "id", Integer, primary_key=True ),
348    Column( "job_id", Integer, ForeignKey( "job.id" ), index=True ),
349    Column( "dataset_id", Integer, ForeignKey( "history_dataset_association.id" ), index=True ),
350    Column( "name", String(255) ) )
351   
352JobToOutputDatasetAssociation.table = Table( "job_to_output_dataset", metadata,
353    Column( "id", Integer, primary_key=True ),
354    Column( "job_id", Integer, ForeignKey( "job.id" ), index=True ),
355    Column( "dataset_id", Integer, ForeignKey( "history_dataset_association.id" ), index=True ),
356    Column( "name", String(255) ) )
357   
358JobToOutputLibraryDatasetAssociation.table = Table( "job_to_output_library_dataset", metadata,
359    Column( "id", Integer, primary_key=True ),
360    Column( "job_id", Integer, ForeignKey( "job.id" ), index=True ),
361    Column( "ldda_id", Integer, ForeignKey( "library_dataset_dataset_association.id" ), index=True ),
362    Column( "name", String(255) ) )
363   
364JobExternalOutputMetadata.table = Table( "job_external_output_metadata", metadata,
365    Column( "id", Integer, primary_key=True ),
366    Column( "job_id", Integer, ForeignKey( "job.id" ), index=True ),
367    Column( "history_dataset_association_id", Integer, ForeignKey( "history_dataset_association.id" ), index=True, nullable=True ),
368    Column( "library_dataset_dataset_association_id", Integer, ForeignKey( "library_dataset_dataset_association.id" ), index=True, nullable=True ),
369    Column( "filename_in", String( 255 ) ),
370    Column( "filename_out", String( 255 ) ),
371    Column( "filename_results_code", String( 255 ) ),
372    Column( "filename_kwds", String( 255 ) ),
373    Column( "filename_override_metadata", String( 255 ) ),
374    Column( "job_runner_external_pid", String( 255 ) ) )
375   
376JobExportHistoryArchive.table = Table( "job_export_history_archive", metadata,
377    Column( "id", Integer, primary_key=True ),
378    Column( "job_id", Integer, ForeignKey( "job.id" ), index=True ),
379    Column( "history_id", Integer, ForeignKey( "history.id" ), index=True ),
380    Column( "dataset_id", Integer, ForeignKey( "dataset.id" ), index=True ),
381    Column( "compressed", Boolean, index=True, default=False ),
382    Column( "history_attrs_filename", TEXT ),
383    Column( "datasets_attrs_filename", TEXT ),
384    Column( "jobs_attrs_filename", TEXT )
385    )
386   
387PostJobAction.table = Table("post_job_action", metadata,
388    Column("id", Integer, primary_key=True),
389    Column("workflow_step_id", Integer, ForeignKey( "workflow_step.id" ), index=True, nullable=False),
390    Column("action_type", String(255), nullable=False),
391    Column("output_name", String(255), nullable=True),
392    Column("action_arguments", JSONType, nullable=True))
393
394PostJobActionAssociation.table = Table("post_job_action_association", metadata,
395    Column("id", Integer, primary_key=True),
396    Column("job_id", Integer, ForeignKey( "job.id" ), index=True, nullable=False),
397    Column("post_job_action_id", Integer, ForeignKey( "post_job_action.id" ), index=True, nullable=False))
398
399Event.table = Table( "event", metadata,
400    Column( "id", Integer, primary_key=True ),
401    Column( "create_time", DateTime, default=now ),
402    Column( "update_time", DateTime, default=now, onupdate=now ),
403    Column( "history_id", Integer, ForeignKey( "history.id" ), index=True, nullable=True ),
404    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True, nullable=True ),
405    Column( "message", TrimmedString( 1024 ) ),
406    Column( "session_id", Integer, ForeignKey( "galaxy_session.id" ), index=True, nullable=True ),
407    Column( "tool_id", String( 255 ) ) )
408
409GalaxySession.table = Table( "galaxy_session", metadata,
410    Column( "id", Integer, primary_key=True ),
411    Column( "create_time", DateTime, default=now ),
412    Column( "update_time", DateTime, default=now, onupdate=now ),
413    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True, nullable=True ),
414    Column( "remote_host", String( 255 ) ),
415    Column( "remote_addr", String( 255 ) ),
416    Column( "referer", TEXT ),
417    Column( "current_history_id", Integer, ForeignKey( "history.id" ), nullable=True ),
418    Column( "session_key", TrimmedString( 255 ), index=True, unique=True ), # unique 128 bit random number coerced to a string
419    Column( "is_valid", Boolean, default=False ),
420    Column( "prev_session_id", Integer ) # saves a reference to the previous session so we have a way to chain them together
421    )
422
423GalaxySessionToHistoryAssociation.table = Table( "galaxy_session_to_history", metadata,
424    Column( "id", Integer, primary_key=True ),
425    Column( "create_time", DateTime, default=now ),
426    Column( "session_id", Integer, ForeignKey( "galaxy_session.id" ), index=True ),
427    Column( "history_id", Integer, ForeignKey( "history.id" ), index=True ) )
428
429StoredWorkflow.table = Table( "stored_workflow", metadata,
430    Column( "id", Integer, primary_key=True ),
431    Column( "create_time", DateTime, default=now ),
432    Column( "update_time", DateTime, default=now, onupdate=now ),
433    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True, nullable=False ),
434    Column( "latest_workflow_id", Integer,
435            ForeignKey( "workflow.id", use_alter=True, name='stored_workflow_latest_workflow_id_fk' ), index=True ),
436    Column( "name", TEXT ),
437    Column( "deleted", Boolean, default=False ),
438    Column( "importable", Boolean, default=False ),
439    Column( "slug", TEXT, index=True ),
440    Column( "published", Boolean, index=True, default=False )
441    )
442
443Workflow.table = Table( "workflow", metadata,
444    Column( "id", Integer, primary_key=True ),
445    Column( "create_time", DateTime, default=now ),
446    Column( "update_time", DateTime, default=now, onupdate=now ),
447    Column( "stored_workflow_id", Integer, ForeignKey( "stored_workflow.id" ), index=True, nullable=False ),
448    Column( "name", TEXT ),
449    Column( "has_cycles", Boolean ),
450    Column( "has_errors", Boolean )
451    )
452
453WorkflowStep.table = Table( "workflow_step", metadata,
454    Column( "id", Integer, primary_key=True ),
455    Column( "create_time", DateTime, default=now ),
456    Column( "update_time", DateTime, default=now, onupdate=now ),
457    Column( "workflow_id", Integer, ForeignKey( "workflow.id" ), index=True, nullable=False ),
458    Column( "type", String(64) ),
459    Column( "tool_id", TEXT ),
460    Column( "tool_version", TEXT ), # Reserved for future
461    Column( "tool_inputs", JSONType ),
462    Column( "tool_errors", JSONType ),
463    Column( "position", JSONType ),
464    Column( "config", JSONType ),
465    Column( "order_index", Integer ),
466    ## Column( "input_connections", JSONType )
467    )
468
469WorkflowStepConnection.table = Table( "workflow_step_connection", metadata,
470    Column( "id", Integer, primary_key=True ),
471    Column( "output_step_id", Integer, ForeignKey( "workflow_step.id" ), index=True ),
472    Column( "input_step_id", Integer, ForeignKey( "workflow_step.id" ), index=True ),
473    Column( "output_name", TEXT ),
474    Column( "input_name", TEXT)
475    )
476
477WorkflowOutput.table = Table( "workflow_output", metadata,
478    Column( "id", Integer, primary_key=True ),
479    Column( "workflow_step_id", Integer, ForeignKey("workflow_step.id"), index=True, nullable=False),
480    Column( "output_name", String(255), nullable=True)
481    )
482
483WorkflowInvocation.table = Table( "workflow_invocation", metadata,
484    Column( "id", Integer, primary_key=True ),
485    Column( "create_time", DateTime, default=now ),
486    Column( "update_time", DateTime, default=now, onupdate=now ),
487    Column( "workflow_id", Integer, ForeignKey( "workflow.id" ), index=True, nullable=False )
488    )
489
490WorkflowInvocationStep.table = Table( "workflow_invocation_step", metadata,
491    Column( "id", Integer, primary_key=True ),
492    Column( "create_time", DateTime, default=now ),
493    Column( "update_time", DateTime, default=now, onupdate=now ),
494    Column( "workflow_invocation_id", Integer, ForeignKey( "workflow_invocation.id" ), index=True, nullable=False ),
495    Column( "workflow_step_id",  Integer, ForeignKey( "workflow_step.id" ), index=True, nullable=False ),
496    Column( "job_id",  Integer, ForeignKey( "job.id" ), index=True, nullable=True )
497    )
498
499StoredWorkflowUserShareAssociation.table = Table( "stored_workflow_user_share_connection", metadata,
500    Column( "id", Integer, primary_key=True ),
501    Column( "stored_workflow_id", Integer, ForeignKey( "stored_workflow.id" ), index=True ),
502    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True )
503    )
504
505StoredWorkflowMenuEntry.table = Table( "stored_workflow_menu_entry", metadata,
506    Column( "id", Integer, primary_key=True ),
507    Column( "stored_workflow_id", Integer, ForeignKey( "stored_workflow.id" ), index=True ),
508    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),                             
509    Column( "order_index", Integer ) )
510
511MetadataFile.table = Table( "metadata_file", metadata,
512    Column( "id", Integer, primary_key=True ),
513    Column( "name", TEXT ),
514    Column( "hda_id", Integer, ForeignKey( "history_dataset_association.id" ), index=True, nullable=True ),
515    Column( "lda_id", Integer, ForeignKey( "library_dataset_dataset_association.id" ), index=True, nullable=True ),
516    Column( "create_time", DateTime, default=now ),
517    Column( "update_time", DateTime, index=True, default=now, onupdate=now ),
518    Column( "deleted", Boolean, index=True, default=False ),
519    Column( "purged", Boolean, index=True, default=False ) )
520
521FormDefinitionCurrent.table = Table('form_definition_current', metadata,
522    Column( "id", Integer, primary_key=True),
523    Column( "create_time", DateTime, default=now ),
524    Column( "update_time", DateTime, default=now, onupdate=now ),
525    Column( "latest_form_id", Integer, ForeignKey( "form_definition.id" ), index=True ),
526    Column( "deleted", Boolean, index=True, default=False ))
527
528FormDefinition.table = Table('form_definition', metadata,
529    Column( "id", Integer, primary_key=True),
530    Column( "create_time", DateTime, default=now ),
531    Column( "update_time", DateTime, default=now, onupdate=now ),
532    Column( "name", TrimmedString( 255 ), nullable=False ),
533    Column( "desc", TEXT ),
534    Column( "form_definition_current_id",
535            Integer,
536            ForeignKey( "form_definition_current.id", name='for_def_form_def_current_id_fk', use_alter=True ),
537            index=True ),
538    Column( "fields", JSONType() ),
539    Column( "type", TrimmedString( 255 ), index=True ),
540    Column( "layout", JSONType() ), )
541
542RequestType.table = Table('request_type', metadata,
543    Column( "id", Integer, primary_key=True),
544    Column( "create_time", DateTime, default=now ),
545    Column( "update_time", DateTime, default=now, onupdate=now ),
546    Column( "name", TrimmedString( 255 ), nullable=False ),
547    Column( "desc", TEXT ),
548    Column( "request_form_id", Integer, ForeignKey( "form_definition.id" ), index=True ),
549    Column( "sample_form_id", Integer, ForeignKey( "form_definition.id" ), index=True ),
550    Column( "datatx_info", JSONType() ),
551    Column( "deleted", Boolean, index=True, default=False ) )
552
553RequestTypePermissions.table = Table( "request_type_permissions", metadata,
554    Column( "id", Integer, primary_key=True ),
555    Column( "create_time", DateTime, default=now ),
556    Column( "update_time", DateTime, default=now, onupdate=now ),
557    Column( "action", TEXT ),
558    Column( "request_type_id", Integer, ForeignKey( "request_type.id" ), nullable=True, index=True ),
559    Column( "role_id", Integer, ForeignKey( "role.id" ), index=True ) )
560
561FormValues.table = Table('form_values', metadata,
562    Column( "id", Integer, primary_key=True),
563    Column( "create_time", DateTime, default=now ),
564    Column( "update_time", DateTime, default=now, onupdate=now ),
565    Column( "form_definition_id", Integer, ForeignKey( "form_definition.id" ), index=True ),
566    Column( "content", JSONType()) )
567
568Request.table = Table('request', metadata,
569    Column( "id", Integer, primary_key=True),
570    Column( "create_time", DateTime, default=now ),
571    Column( "update_time", DateTime, default=now, onupdate=now ),
572    Column( "name", TrimmedString( 255 ), nullable=False ),
573    Column( "desc", TEXT ),
574    Column( "notification", JSONType() ),
575    Column( "form_values_id", Integer, ForeignKey( "form_values.id" ), index=True ),
576    Column( "request_type_id", Integer, ForeignKey( "request_type.id" ), index=True ),
577    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
578    Column( "deleted", Boolean, index=True, default=False ) )
579
580RequestEvent.table = Table('request_event', metadata,
581    Column( "id", Integer, primary_key=True),
582    Column( "create_time", DateTime, default=now ),
583    Column( "update_time", DateTime, default=now, onupdate=now ),
584    Column( "request_id", Integer, ForeignKey( "request.id" ), index=True ),
585    Column( "state", TrimmedString( 255 ),  index=True ),
586    Column( "comment", TEXT ) )
587
588Sample.table = Table('sample', metadata,
589    Column( "id", Integer, primary_key=True ),
590    Column( "create_time", DateTime, default=now ),
591    Column( "update_time", DateTime, default=now, onupdate=now ),
592    Column( "name", TrimmedString( 255 ), nullable=False ),
593    Column( "desc", TEXT ),
594    Column( "form_values_id", Integer, ForeignKey( "form_values.id" ), index=True ),
595    Column( "request_id", Integer, ForeignKey( "request.id" ), index=True ),
596    Column( "bar_code", TrimmedString( 255 ), index=True ),
597    Column( "library_id", Integer, ForeignKey( "library.id" ), index=True ),
598    Column( "folder_id", Integer, ForeignKey( "library_folder.id" ), index=True ),
599    Column( "deleted", Boolean, index=True, default=False ) )
600
601SampleState.table = Table('sample_state', metadata,
602    Column( "id", Integer, primary_key=True ),
603    Column( "create_time", DateTime, default=now ),
604    Column( "update_time", DateTime, default=now, onupdate=now ),
605    Column( "name", TrimmedString( 255 ), nullable=False ),
606    Column( "desc", TEXT ),
607    Column( "request_type_id", Integer, ForeignKey( "request_type.id" ), index=True ) )
608
609SampleEvent.table = Table('sample_event', metadata,
610    Column( "id", Integer, primary_key=True ),
611    Column( "create_time", DateTime, default=now ),
612    Column( "update_time", DateTime, default=now, onupdate=now ),
613    Column( "sample_id", Integer, ForeignKey( "sample.id" ), index=True ),
614    Column( "sample_state_id", Integer, ForeignKey( "sample_state.id" ), index=True ),
615    Column( "comment", TEXT ) )
616
617SampleDataset.table = Table('sample_dataset', metadata,
618    Column( "id", Integer, primary_key=True ),
619    Column( "create_time", DateTime, default=now ),
620    Column( "update_time", DateTime, default=now, onupdate=now ),
621    Column( "sample_id", Integer, ForeignKey( "sample.id" ), index=True ),
622    Column( "name", TrimmedString( 255 ), nullable=False ),
623    Column( "file_path", TEXT ),
624    Column( "status", TrimmedString( 255 ), nullable=False ),
625    Column( "error_msg", TEXT ),
626    Column( "size", TrimmedString( 255 ) ) )
627
628Page.table = Table( "page", metadata,
629    Column( "id", Integer, primary_key=True ),
630    Column( "create_time", DateTime, default=now ),
631    Column( "update_time", DateTime, default=now, onupdate=now ),
632    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True, nullable=False ),
633    Column( "latest_revision_id", Integer,
634            ForeignKey( "page_revision.id", use_alter=True, name='page_latest_revision_id_fk' ), index=True ),
635    Column( "title", TEXT ),
636    Column( "slug", TEXT, unique=True, index=True ),
637    Column( "importable", Boolean, index=True, default=False ),
638    Column( "published", Boolean, index=True, default=False ),
639    Column( "deleted", Boolean, index=True, default=False ),
640    )
641
642PageRevision.table = Table( "page_revision", metadata,
643    Column( "id", Integer, primary_key=True ),
644    Column( "create_time", DateTime, default=now ),
645    Column( "update_time", DateTime, default=now, onupdate=now ),
646    Column( "page_id", Integer, ForeignKey( "page.id" ), index=True, nullable=False ),
647    Column( "title", TEXT ),
648    Column( "content", TEXT )
649    )
650
651PageUserShareAssociation.table = Table( "page_user_share_association", metadata,
652    Column( "id", Integer, primary_key=True ),
653    Column( "page_id", Integer, ForeignKey( "page.id" ), index=True ),
654    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True )
655    )
656
657Visualization.table = Table( "visualization", metadata,
658    Column( "id", Integer, primary_key=True ),
659    Column( "create_time", DateTime, default=now ),
660    Column( "update_time", DateTime, default=now, onupdate=now ),
661    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True, nullable=False ),
662    Column( "latest_revision_id", Integer,
663            ForeignKey( "visualization_revision.id", use_alter=True, name='visualization_latest_revision_id_fk' ), index=True ),
664    Column( "title", TEXT ),
665    Column( "type", TEXT ),
666    Column( "dbkey", TEXT, index=True ),
667    Column( "deleted", Boolean, default=False, index=True ),
668    Column( "importable", Boolean, default=False, index=True ),
669    Column( "slug", TEXT, index=True ),
670    Column( "published", Boolean, default=False, index=True )
671    )
672
673VisualizationRevision.table = Table( "visualization_revision", metadata,
674    Column( "id", Integer, primary_key=True ),
675    Column( "create_time", DateTime, default=now ),
676    Column( "update_time", DateTime, default=now, onupdate=now ),
677    Column( "visualization_id", Integer, ForeignKey( "visualization.id" ), index=True, nullable=False ),
678    Column( "title", TEXT ),
679    Column( "dbkey", TEXT, index=True ),
680    Column( "config", JSONType )
681    )
682   
683VisualizationUserShareAssociation.table = Table( "visualization_user_share_association", metadata,
684    Column( "id", Integer, primary_key=True ),
685    Column( "visualization_id", Integer, ForeignKey( "visualization.id" ), index=True ),
686    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True )
687    )
688   
689# Tagging tables.
690
691Tag.table = Table( "tag", metadata,
692    Column( "id", Integer, primary_key=True ),
693    Column( "type", Integer ),
694    Column( "parent_id", Integer, ForeignKey( "tag.id" ) ),
695    Column( "name", TrimmedString(255) ),
696    UniqueConstraint( "name" ) )
697
698HistoryTagAssociation.table = Table( "history_tag_association", metadata,
699    Column( "id", Integer, primary_key=True ),
700    Column( "history_id", Integer, ForeignKey( "history.id" ), index=True ),
701    Column( "tag_id", Integer, ForeignKey( "tag.id" ), index=True ),
702    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
703    Column( "user_tname", TrimmedString(255), index=True),
704    Column( "value", TrimmedString(255), index=True),
705    Column( "user_value", TrimmedString(255), index=True) )
706   
707DatasetTagAssociation.table = Table( "dataset_tag_association", metadata,
708    Column( "id", Integer, primary_key=True ),
709    Column( "dataset_id", Integer, ForeignKey( "dataset.id" ), index=True ),
710    Column( "tag_id", Integer, ForeignKey( "tag.id" ), index=True ),
711    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
712    Column( "user_tname", TrimmedString(255), index=True),
713    Column( "value", TrimmedString(255), index=True),
714    Column( "user_value", TrimmedString(255), index=True) )
715
716HistoryDatasetAssociationTagAssociation.table = Table( "history_dataset_association_tag_association", metadata,
717    Column( "id", Integer, primary_key=True ),
718    Column( "history_dataset_association_id", Integer, ForeignKey( "history_dataset_association.id" ), index=True ),
719    Column( "tag_id", Integer, ForeignKey( "tag.id" ), index=True ),
720    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
721    Column( "user_tname", TrimmedString(255), index=True),
722    Column( "value", TrimmedString(255), index=True),
723    Column( "user_value", TrimmedString(255), index=True) )
724       
725StoredWorkflowTagAssociation.table = Table( "stored_workflow_tag_association", metadata,
726    Column( "id", Integer, primary_key=True ),
727    Column( "stored_workflow_id", Integer, ForeignKey( "stored_workflow.id" ), index=True ),
728    Column( "tag_id", Integer, ForeignKey( "tag.id" ), index=True ),
729    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
730    Column( "user_tname", Unicode(255), index=True),
731    Column( "value", Unicode(255), index=True),
732    Column( "user_value", Unicode(255), index=True) )
733
734PageTagAssociation.table = Table( "page_tag_association", metadata,
735    Column( "id", Integer, primary_key=True ),
736    Column( "page_id", Integer, ForeignKey( "page.id" ), index=True ),
737    Column( "tag_id", Integer, ForeignKey( "tag.id" ), index=True ),
738    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
739    Column( "user_tname", TrimmedString(255), index=True),
740    Column( "value", TrimmedString(255), index=True),
741    Column( "user_value", TrimmedString(255), index=True) )
742   
743WorkflowStepTagAssociation.table = Table( "workflow_step_tag_association", metadata,
744    Column( "id", Integer, primary_key=True ),
745    Column( "workflow_step_id", Integer, ForeignKey( "workflow_step.id" ), index=True ),
746    Column( "tag_id", Integer, ForeignKey( "tag.id" ), index=True ),
747    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
748    Column( "user_tname", Unicode(255), index=True),
749    Column( "value", Unicode(255), index=True),
750    Column( "user_value", Unicode(255), index=True) )
751   
752VisualizationTagAssociation.table = Table( "visualization_tag_association", metadata,
753    Column( "id", Integer, primary_key=True ),
754    Column( "visualization_id", Integer, ForeignKey( "visualization.id" ), index=True ),
755    Column( "tag_id", Integer, ForeignKey( "tag.id" ), index=True ),
756    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
757    Column( "user_tname", TrimmedString(255), index=True),
758    Column( "value", TrimmedString(255), index=True),
759    Column( "user_value", TrimmedString(255), index=True) )
760   
761# Annotation tables.
762
763HistoryAnnotationAssociation.table = Table( "history_annotation_association", metadata,
764    Column( "id", Integer, primary_key=True ),
765    Column( "history_id", Integer, ForeignKey( "history.id" ), index=True ),
766    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
767    Column( "annotation", TEXT, index=True) )
768
769HistoryDatasetAssociationAnnotationAssociation.table = Table( "history_dataset_association_annotation_association", metadata,
770    Column( "id", Integer, primary_key=True ),
771    Column( "history_dataset_association_id", Integer, ForeignKey( "history_dataset_association.id" ), index=True ),
772    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
773    Column( "annotation", TEXT, index=True) )
774
775StoredWorkflowAnnotationAssociation.table = Table( "stored_workflow_annotation_association", metadata,
776    Column( "id", Integer, primary_key=True ),
777    Column( "stored_workflow_id", Integer, ForeignKey( "stored_workflow.id" ), index=True ),
778    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
779    Column( "annotation", TEXT, index=True) )
780
781WorkflowStepAnnotationAssociation.table = Table( "workflow_step_annotation_association", metadata,
782    Column( "id", Integer, primary_key=True ),
783    Column( "workflow_step_id", Integer, ForeignKey( "workflow_step.id" ), index=True ),
784    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
785    Column( "annotation", TEXT, index=True) )
786   
787PageAnnotationAssociation.table = Table( "page_annotation_association", metadata,
788    Column( "id", Integer, primary_key=True ),
789    Column( "page_id", Integer, ForeignKey( "page.id" ), index=True ),
790    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
791    Column( "annotation", TEXT, index=True) )
792   
793VisualizationAnnotationAssociation.table = Table( "visualization_annotation_association", metadata,
794    Column( "id", Integer, primary_key=True ),
795    Column( "visualization_id", Integer, ForeignKey( "visualization.id" ), index=True ),
796    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
797    Column( "annotation", TEXT, index=True) )
798   
799# Ratings tables.
800HistoryRatingAssociation.table = Table( "history_rating_association", metadata,
801    Column( "id", Integer, primary_key=True ),
802    Column( "history_id", Integer, ForeignKey( "history.id" ), index=True ),
803    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
804    Column( "rating", Integer, index=True) )
805   
806HistoryDatasetAssociationRatingAssociation.table = Table( "history_dataset_association_rating_association", metadata,
807    Column( "id", Integer, primary_key=True ),
808    Column( "history_dataset_association_id", Integer, ForeignKey( "history_dataset_association.id" ), index=True ),
809    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
810    Column( "rating", Integer, index=True) )
811   
812StoredWorkflowRatingAssociation.table = Table( "stored_workflow_rating_association", metadata,
813    Column( "id", Integer, primary_key=True ),
814    Column( "stored_workflow_id", Integer, ForeignKey( "stored_workflow.id" ), index=True ),
815    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
816    Column( "rating", Integer, index=True) )
817   
818PageRatingAssociation.table = Table( "page_rating_association", metadata,
819    Column( "id", Integer, primary_key=True ),
820    Column( "page_id", Integer, ForeignKey( "page.id" ), index=True ),
821    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
822    Column( "rating", Integer, index=True) )
823   
824VisualizationRatingAssociation.table = Table( "visualization_rating_association", metadata,
825    Column( "id", Integer, primary_key=True ),
826    Column( "visualization_id", Integer, ForeignKey( "visualization.id" ), index=True ),
827    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
828    Column( "rating", Integer, index=True) )
829   
830# User tables.
831   
832UserPreference.table = Table( "user_preference", metadata,
833    Column( "id", Integer, primary_key=True ),
834    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
835    Column( "name", Unicode( 255 ), index=True),
836    Column( "value", Unicode( 1024 ) ) )
837   
838UserAction.table = Table( "user_action", metadata,
839    Column( "id", Integer, primary_key=True ),
840    Column( "create_time", DateTime, default=now ),
841    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
842    Column( "session_id", Integer, ForeignKey( "galaxy_session.id" ), index=True ),
843    Column( "action", Unicode( 255 ) ),
844    Column( "context", Unicode( 512 ) ),
845    Column( "params", Unicode( 1024 ) ) )
846
847APIKeys.table = Table( "api_keys", metadata,
848    Column( "id", Integer, primary_key=True ),
849    Column( "create_time", DateTime, default=now ),
850    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
851    Column( "key", TrimmedString( 32 ), index=True, unique=True ) )
852
853# With the tables defined we can define the mappers and setup the
854# relationships between the model objects.
855
856assign_mapper( context, Sample, Sample.table,
857               properties=dict(
858                    events=relation( SampleEvent, backref="sample",
859                        order_by=desc( SampleEvent.table.c.update_time ) ),
860                    datasets=relation( SampleDataset, backref="sample",
861                        order_by=desc( SampleDataset.table.c.update_time ) ),
862                    values=relation( FormValues,
863                        primaryjoin=( Sample.table.c.form_values_id == FormValues.table.c.id ) ),
864                    request=relation( Request,
865                        primaryjoin=( Sample.table.c.request_id == Request.table.c.id ) ),
866                    folder=relation( LibraryFolder,
867                        primaryjoin=( Sample.table.c.folder_id == LibraryFolder.table.c.id ) ),                 
868                    library=relation( Library,
869                        primaryjoin=( Sample.table.c.library_id == Library.table.c.id ) ),
870            ) )
871
872assign_mapper( context, FormValues, FormValues.table,
873               properties=dict( form_definition=relation( FormDefinition,
874                                                          primaryjoin=( FormValues.table.c.form_definition_id == FormDefinition.table.c.id ) )
875             )
876)
877
878assign_mapper( context, Request, Request.table,
879               properties=dict( values=relation( FormValues,
880                                                 primaryjoin=( Request.table.c.form_values_id == FormValues.table.c.id ) ),
881                                type=relation( RequestType,
882                                               primaryjoin=( Request.table.c.request_type_id == RequestType.table.c.id ) ),
883                                user=relation( User,
884                                               primaryjoin=( Request.table.c.user_id == User.table.c.id ),
885                                               backref="requests" ),
886                                samples=relation( Sample,
887                                                  primaryjoin=( Request.table.c.id == Sample.table.c.request_id ),
888                                                  order_by=asc( Sample.table.c.id ) ),
889                                events=relation( RequestEvent, backref="request",
890                                                 order_by=desc( RequestEvent.table.c.update_time ) )
891                              ) )
892
893assign_mapper( context, RequestEvent, RequestEvent.table,
894               properties=None )
895
896assign_mapper( context, RequestType, RequestType.table,               
897               properties=dict( states=relation( SampleState,
898                                                 backref="request_type",
899                                                 primaryjoin=( RequestType.table.c.id == SampleState.table.c.request_type_id ),
900                                                 order_by=asc( SampleState.table.c.update_time ) ),
901                                request_form=relation( FormDefinition,
902                                                       primaryjoin=( RequestType.table.c.request_form_id == FormDefinition.table.c.id ) ),
903                                sample_form=relation( FormDefinition,
904                                                      primaryjoin=( RequestType.table.c.sample_form_id == FormDefinition.table.c.id ) ),
905                              ) )
906
907assign_mapper( context, RequestTypePermissions, RequestTypePermissions.table,
908    properties=dict(
909        request_type=relation( RequestType, backref="actions" ),
910        role=relation( Role, backref="request_type_actions" )
911    )
912)
913
914assign_mapper( context, FormDefinition, FormDefinition.table,
915               properties=dict( current=relation( FormDefinitionCurrent,
916                                                  primaryjoin=( FormDefinition.table.c.form_definition_current_id == FormDefinitionCurrent.table.c.id ) )
917                              ) )
918assign_mapper( context, FormDefinitionCurrent, FormDefinitionCurrent.table,
919                properties=dict( forms=relation( FormDefinition, backref='form_definition_current',
920                                                 cascade="all, delete-orphan",
921                                                 primaryjoin=( FormDefinitionCurrent.table.c.id == FormDefinition.table.c.form_definition_current_id ) ),
922                                 latest_form=relation( FormDefinition, post_update=True,
923                                                       primaryjoin=( FormDefinitionCurrent.table.c.latest_form_id == FormDefinition.table.c.id ) )
924                               ) )
925
926assign_mapper( context, SampleEvent, SampleEvent.table,
927               properties=dict( state=relation( SampleState,
928                                                primaryjoin=( SampleEvent.table.c.sample_state_id == SampleState.table.c.id ) ),
929
930                                ) )
931                               
932assign_mapper( context, SampleState, SampleState.table,
933               properties=None )
934
935assign_mapper( context, SampleDataset, SampleDataset.table,
936               properties=None )
937
938assign_mapper( context, UserAddress, UserAddress.table,
939               properties=dict(
940                    user=relation( User,
941                                   primaryjoin=( UserAddress.table.c.user_id == User.table.c.id ),
942                                   backref='addresses',
943                                   order_by=desc(UserAddress.table.c.update_time)),
944                ) )
945
946
947assign_mapper( context, ValidationError, ValidationError.table )
948
949assign_mapper( context, HistoryDatasetAssociation, HistoryDatasetAssociation.table,
950    properties=dict(
951        dataset=relation(
952            Dataset,
953            primaryjoin=( Dataset.table.c.id == HistoryDatasetAssociation.table.c.dataset_id ), lazy=False ),
954        # .history defined in History mapper
955        copied_to_history_dataset_associations=relation(
956            HistoryDatasetAssociation,
957            primaryjoin=( HistoryDatasetAssociation.table.c.copied_from_history_dataset_association_id == HistoryDatasetAssociation.table.c.id ),
958            backref=backref( "copied_from_history_dataset_association", primaryjoin=( HistoryDatasetAssociation.table.c.copied_from_history_dataset_association_id == HistoryDatasetAssociation.table.c.id ), remote_side=[HistoryDatasetAssociation.table.c.id], uselist=False ) ),
959        copied_to_library_dataset_dataset_associations=relation(
960            LibraryDatasetDatasetAssociation,
961            primaryjoin=( HistoryDatasetAssociation.table.c.copied_from_library_dataset_dataset_association_id == LibraryDatasetDatasetAssociation.table.c.id ),
962            backref=backref( "copied_from_history_dataset_association", primaryjoin=( HistoryDatasetAssociation.table.c.copied_from_library_dataset_dataset_association_id == LibraryDatasetDatasetAssociation.table.c.id ), remote_side=[LibraryDatasetDatasetAssociation.table.c.id], uselist=False ) ),
963        implicitly_converted_datasets=relation(
964            ImplicitlyConvertedDatasetAssociation,
965            primaryjoin=( ImplicitlyConvertedDatasetAssociation.table.c.hda_parent_id == HistoryDatasetAssociation.table.c.id ) ),
966        children=relation(
967            HistoryDatasetAssociation,
968            primaryjoin=( HistoryDatasetAssociation.table.c.parent_id == HistoryDatasetAssociation.table.c.id ),
969            backref=backref( "parent", primaryjoin=( HistoryDatasetAssociation.table.c.parent_id == HistoryDatasetAssociation.table.c.id ), remote_side=[HistoryDatasetAssociation.table.c.id], uselist=False ) ),
970        visible_children=relation(
971            HistoryDatasetAssociation,
972            primaryjoin=( ( HistoryDatasetAssociation.table.c.parent_id == HistoryDatasetAssociation.table.c.id ) & ( HistoryDatasetAssociation.table.c.visible == True ) ) ),
973        tags=relation( HistoryDatasetAssociationTagAssociation, order_by=HistoryDatasetAssociationTagAssociation.table.c.id, backref='history_tag_associations' ),
974        annotations=relation( HistoryDatasetAssociationAnnotationAssociation, order_by=HistoryDatasetAssociationAnnotationAssociation.table.c.id, backref="hdas" ),
975        ratings=relation( HistoryDatasetAssociationRatingAssociation, order_by=HistoryDatasetAssociationRatingAssociation.table.c.id, backref="hdas" ) )
976            )
977
978assign_mapper( context, Dataset, Dataset.table,
979    properties=dict(
980        history_associations=relation(
981            HistoryDatasetAssociation,
982            primaryjoin=( Dataset.table.c.id == HistoryDatasetAssociation.table.c.dataset_id ) ),
983        active_history_associations=relation(
984            HistoryDatasetAssociation,
985            primaryjoin=( ( Dataset.table.c.id == HistoryDatasetAssociation.table.c.dataset_id ) & ( HistoryDatasetAssociation.table.c.deleted == False ) ) ),
986        library_associations=relation(
987            LibraryDatasetDatasetAssociation,
988            primaryjoin=( Dataset.table.c.id == LibraryDatasetDatasetAssociation.table.c.dataset_id ) ),
989        active_library_associations=relation(
990            LibraryDatasetDatasetAssociation,
991            primaryjoin=( ( Dataset.table.c.id == LibraryDatasetDatasetAssociation.table.c.dataset_id ) & ( LibraryDatasetDatasetAssociation.table.c.deleted == False ) ) ),
992        tags=relation(DatasetTagAssociation, order_by=DatasetTagAssociation.table.c.id, backref='datasets')
993            ) )
994
995assign_mapper( context, HistoryDatasetAssociationDisplayAtAuthorization, HistoryDatasetAssociationDisplayAtAuthorization.table,
996    properties=dict( history_dataset_association = relation( HistoryDatasetAssociation ),
997                     user = relation( User ) ) )
998
999assign_mapper( context, ImplicitlyConvertedDatasetAssociation, ImplicitlyConvertedDatasetAssociation.table,
1000    properties=dict( parent=relation(
1001                     HistoryDatasetAssociation,
1002                     primaryjoin=( ImplicitlyConvertedDatasetAssociation.table.c.hda_parent_id == HistoryDatasetAssociation.table.c.id ) ),
1003                     
1004                     dataset=relation(
1005                     HistoryDatasetAssociation,
1006                     primaryjoin=( ImplicitlyConvertedDatasetAssociation.table.c.hda_id == HistoryDatasetAssociation.table.c.id ) ) ) )
1007
1008assign_mapper( context, History, History.table,
1009    properties=dict( galaxy_sessions=relation( GalaxySessionToHistoryAssociation ),
1010                     datasets=relation( HistoryDatasetAssociation, backref="history", order_by=asc(HistoryDatasetAssociation.table.c.hid) ),
1011                     active_datasets=relation( HistoryDatasetAssociation, primaryjoin=( ( HistoryDatasetAssociation.table.c.history_id == History.table.c.id ) & ( not_( HistoryDatasetAssociation.table.c.deleted ) ) ), order_by=asc( HistoryDatasetAssociation.table.c.hid ), viewonly=True ),
1012                     tags=relation( HistoryTagAssociation, order_by=HistoryTagAssociation.table.c.id, backref="histories" ),
1013                     annotations=relation( HistoryAnnotationAssociation, order_by=HistoryAnnotationAssociation.table.c.id, backref="histories" ),
1014                     ratings=relation( HistoryRatingAssociation, order_by=HistoryRatingAssociation.table.c.id, backref="histories" ) ) 
1015                      )
1016
1017# Set up proxy so that
1018#   History.users_shared_with
1019# returns a list of users that history is shared with.
1020History.users_shared_with_dot_users = association_proxy( 'users_shared_with', 'user' )
1021
1022assign_mapper( context, HistoryUserShareAssociation, HistoryUserShareAssociation.table,
1023    properties=dict( user=relation( User, backref='histories_shared_by_others' ),
1024                     history=relation( History, backref='users_shared_with' )
1025                   ) )
1026
1027assign_mapper( context, User, User.table,
1028    properties=dict( histories=relation( History, backref="user",
1029                                         order_by=desc(History.table.c.update_time) ),               
1030                     active_histories=relation( History, primaryjoin=( ( History.table.c.user_id == User.table.c.id ) & ( not_( History.table.c.deleted ) ) ), order_by=desc( History.table.c.update_time ) ),
1031                     galaxy_sessions=relation( GalaxySession, order_by=desc( GalaxySession.table.c.update_time ) ),
1032                     stored_workflow_menu_entries=relation( StoredWorkflowMenuEntry, backref="user",
1033                                                            cascade="all, delete-orphan",
1034                                                            collection_class=ordering_list( 'order_index' ) ),
1035                     _preferences=relation( UserPreference, backref="user", collection_class=attribute_mapped_collection('name')),
1036#                     addresses=relation( UserAddress,
1037#                                         primaryjoin=( User.table.c.id == UserAddress.table.c.user_id ) )
1038                     values=relation( FormValues,
1039                                      primaryjoin=( User.table.c.form_values_id == FormValues.table.c.id ) ),
1040                     api_keys=relation( APIKeys, backref="user", order_by=desc( APIKeys.table.c.create_time ) ),
1041                     ) )
1042                     
1043# Set up proxy so that this syntax is possible:
1044# <user_obj>.preferences[pref_name] = pref_value
1045User.preferences = association_proxy('_preferences', 'value', creator=UserPreference)
1046
1047assign_mapper( context, Group, Group.table,
1048    properties=dict( users=relation( UserGroupAssociation ) ) )
1049
1050assign_mapper( context, UserGroupAssociation, UserGroupAssociation.table,
1051    properties=dict( user=relation( User, backref = "groups" ),
1052                     group=relation( Group, backref = "members" ) ) )
1053
1054assign_mapper( context, DefaultUserPermissions, DefaultUserPermissions.table,
1055    properties=dict( user=relation( User, backref = "default_permissions" ),
1056                     role=relation( Role ) ) )
1057
1058assign_mapper( context, DefaultHistoryPermissions, DefaultHistoryPermissions.table,
1059    properties=dict( history=relation( History, backref = "default_permissions" ),
1060                     role=relation( Role ) ) )
1061
1062assign_mapper( context, Role, Role.table,
1063    properties=dict(
1064        users=relation( UserRoleAssociation ),
1065        groups=relation( GroupRoleAssociation )
1066    )
1067)
1068
1069assign_mapper( context, UserRoleAssociation, UserRoleAssociation.table,
1070    properties=dict(
1071        user=relation( User, backref="roles" ),
1072        non_private_roles=relation( User,
1073                                    backref="non_private_roles",
1074                                    primaryjoin=( ( User.table.c.id == UserRoleAssociation.table.c.user_id ) & ( UserRoleAssociation.table.c.role_id == Role.table.c.id ) & not_( Role.table.c.name == User.table.c.email ) ) ),
1075        role=relation( Role )
1076    )
1077)
1078
1079assign_mapper( context, GroupRoleAssociation, GroupRoleAssociation.table,
1080    properties=dict(
1081        group=relation( Group, backref="roles" ),
1082        role=relation( Role )
1083    )
1084)
1085
1086assign_mapper( context, DatasetPermissions, DatasetPermissions.table,
1087    properties=dict(
1088        dataset=relation( Dataset, backref="actions" ),
1089        role=relation( Role, backref="dataset_actions" )
1090    )
1091)
1092
1093assign_mapper( context, LibraryPermissions, LibraryPermissions.table,
1094    properties=dict(
1095        library=relation( Library, backref="actions" ),
1096        role=relation( Role, backref="library_actions" )
1097    )
1098)
1099
1100assign_mapper( context, LibraryFolderPermissions, LibraryFolderPermissions.table,
1101    properties=dict(
1102        folder=relation( LibraryFolder, backref="actions" ),
1103        role=relation( Role, backref="library_folder_actions" )
1104    )
1105)
1106
1107assign_mapper( context, LibraryDatasetPermissions, LibraryDatasetPermissions.table,
1108    properties=dict(
1109        library_dataset=relation( LibraryDataset, backref="actions" ),
1110        role=relation( Role, backref="library_dataset_actions" )
1111    )
1112)
1113
1114assign_mapper( context, LibraryDatasetDatasetAssociationPermissions, LibraryDatasetDatasetAssociationPermissions.table,
1115    properties=dict(
1116        library_dataset_dataset_association = relation( LibraryDatasetDatasetAssociation, backref="actions" ),
1117        role=relation( Role, backref="library_dataset_dataset_actions" )
1118    )
1119)
1120
1121assign_mapper( context, Library, Library.table,
1122    properties=dict(
1123        root_folder=relation( LibraryFolder, backref=backref( "library_root" ) )
1124        )
1125)
1126
1127assign_mapper( context, LibraryInfoAssociation, LibraryInfoAssociation.table,
1128               properties=dict( library=relation( Library,
1129                                                  primaryjoin=( ( LibraryInfoAssociation.table.c.library_id == Library.table.c.id ) & ( not_( LibraryInfoAssociation.table.c.deleted ) ) ), backref="info_association" ),
1130                                template=relation( FormDefinition,
1131                                                   primaryjoin=( LibraryInfoAssociation.table.c.form_definition_id == FormDefinition.table.c.id ) ),
1132                                info=relation( FormValues,
1133                                               primaryjoin=( LibraryInfoAssociation.table.c.form_values_id == FormValues.table.c.id ) )
1134                              ) )
1135
1136assign_mapper( context, LibraryFolder, LibraryFolder.table,
1137    properties=dict(
1138        folders=relation(
1139            LibraryFolder,
1140            primaryjoin=( LibraryFolder.table.c.parent_id == LibraryFolder.table.c.id ),
1141            order_by=asc( LibraryFolder.table.c.name ),
1142            backref=backref( "parent", primaryjoin=( LibraryFolder.table.c.parent_id == LibraryFolder.table.c.id ), remote_side=[LibraryFolder.table.c.id] ) ),
1143        active_folders=relation( LibraryFolder,
1144            primaryjoin=( ( LibraryFolder.table.c.parent_id == LibraryFolder.table.c.id ) & ( not_( LibraryFolder.table.c.deleted ) ) ),
1145            order_by=asc( LibraryFolder.table.c.name ),
1146            lazy=True, #"""sqlalchemy.exceptions.ArgumentError: Error creating eager relationship 'active_folders' on parent class '<class 'galaxy.model.LibraryFolder'>' to child class '<class 'galaxy.model.LibraryFolder'>': Cant use eager loading on a self referential relationship."""
1147            viewonly=True ),
1148        datasets=relation( LibraryDataset,
1149            primaryjoin=( ( LibraryDataset.table.c.folder_id == LibraryFolder.table.c.id ) ),
1150            order_by=asc( LibraryDataset.table.c._name ),
1151            lazy=False,
1152            viewonly=True )
1153    ) )
1154
1155assign_mapper( context, LibraryFolderInfoAssociation, LibraryFolderInfoAssociation.table,
1156               properties=dict( folder=relation( LibraryFolder,
1157                                                 primaryjoin=( ( LibraryFolderInfoAssociation.table.c.library_folder_id == LibraryFolder.table.c.id ) & ( not_( LibraryFolderInfoAssociation.table.c.deleted ) ) ), backref="info_association" ),
1158                                template=relation( FormDefinition,
1159                                                   primaryjoin=( LibraryFolderInfoAssociation.table.c.form_definition_id == FormDefinition.table.c.id ) ),
1160                                info=relation( FormValues,
1161                                               primaryjoin=( LibraryFolderInfoAssociation.table.c.form_values_id == FormValues.table.c.id ) )
1162                              ) )
1163
1164assign_mapper( context, LibraryDataset, LibraryDataset.table,
1165    properties=dict(
1166        folder=relation( LibraryFolder ),
1167        library_dataset_dataset_association=relation( LibraryDatasetDatasetAssociation, primaryjoin=( LibraryDataset.table.c.library_dataset_dataset_association_id == LibraryDatasetDatasetAssociation.table.c.id ) ),
1168        expired_datasets = relation( LibraryDatasetDatasetAssociation, foreign_keys=[LibraryDataset.table.c.id,LibraryDataset.table.c.library_dataset_dataset_association_id ], primaryjoin=( ( LibraryDataset.table.c.id == LibraryDatasetDatasetAssociation.table.c.library_dataset_id ) & ( not_( LibraryDataset.table.c.library_dataset_dataset_association_id == LibraryDatasetDatasetAssociation.table.c.id ) ) ), viewonly=True, uselist=True )
1169        ) )
1170
1171assign_mapper( context, LibraryDatasetDatasetAssociation, LibraryDatasetDatasetAssociation.table,
1172    properties=dict(
1173        dataset=relation( Dataset ),
1174        library_dataset = relation( LibraryDataset,
1175        primaryjoin=( LibraryDatasetDatasetAssociation.table.c.library_dataset_id == LibraryDataset.table.c.id ) ),
1176        user=relation( User.mapper ),
1177        copied_to_library_dataset_dataset_associations=relation(
1178            LibraryDatasetDatasetAssociation,
1179            primaryjoin=( LibraryDatasetDatasetAssociation.table.c.copied_from_library_dataset_dataset_association_id == LibraryDatasetDatasetAssociation.table.c.id ),
1180            backref=backref( "copied_from_library_dataset_dataset_association", primaryjoin=( LibraryDatasetDatasetAssociation.table.c.copied_from_library_dataset_dataset_association_id == LibraryDatasetDatasetAssociation.table.c.id ), remote_side=[LibraryDatasetDatasetAssociation.table.c.id] ) ),
1181        copied_to_history_dataset_associations=relation(
1182            HistoryDatasetAssociation,
1183            primaryjoin=( HistoryDatasetAssociation.table.c.copied_from_library_dataset_dataset_association_id == LibraryDatasetDatasetAssociation.table.c.id ),
1184            backref=backref( "copied_from_library_dataset_dataset_association", primaryjoin=( HistoryDatasetAssociation.table.c.copied_from_library_dataset_dataset_association_id == LibraryDatasetDatasetAssociation.table.c.id ), remote_side=[LibraryDatasetDatasetAssociation.table.c.id], uselist=False ) ),
1185        children=relation(
1186            LibraryDatasetDatasetAssociation,
1187            primaryjoin=( LibraryDatasetDatasetAssociation.table.c.parent_id == LibraryDatasetDatasetAssociation.table.c.id ),
1188            backref=backref( "parent", primaryjoin=( LibraryDatasetDatasetAssociation.table.c.parent_id == LibraryDatasetDatasetAssociation.table.c.id ), remote_side=[LibraryDatasetDatasetAssociation.table.c.id] ) ),
1189        visible_children=relation(
1190            LibraryDatasetDatasetAssociation,
1191            primaryjoin=( ( LibraryDatasetDatasetAssociation.table.c.parent_id == LibraryDatasetDatasetAssociation.table.c.id ) & ( LibraryDatasetDatasetAssociation.table.c.visible == True ) ) )
1192        ) )
1193
1194assign_mapper( context, LibraryDatasetDatasetInfoAssociation, LibraryDatasetDatasetInfoAssociation.table,
1195               properties=dict( library_dataset_dataset_association=relation( LibraryDatasetDatasetAssociation,
1196                                                                              primaryjoin=( ( LibraryDatasetDatasetInfoAssociation.table.c.library_dataset_dataset_association_id == LibraryDatasetDatasetAssociation.table.c.id ) & ( not_( LibraryDatasetDatasetInfoAssociation.table.c.deleted ) ) ), backref="info_association" ),
1197                                template=relation( FormDefinition,
1198                                                   primaryjoin=( LibraryDatasetDatasetInfoAssociation.table.c.form_definition_id == FormDefinition.table.c.id ) ),
1199                                info=relation( FormValues,
1200                                               primaryjoin=( LibraryDatasetDatasetInfoAssociation.table.c.form_values_id == FormValues.table.c.id ) )
1201                              ) )
1202
1203assign_mapper( context, JobToInputDatasetAssociation, JobToInputDatasetAssociation.table,
1204    properties=dict( job=relation( Job ), dataset=relation( HistoryDatasetAssociation, lazy=False ) ) )
1205
1206assign_mapper( context, JobToOutputDatasetAssociation, JobToOutputDatasetAssociation.table,
1207    properties=dict( job=relation( Job ), dataset=relation( HistoryDatasetAssociation, lazy=False ) ) )
1208
1209assign_mapper( context, JobToOutputLibraryDatasetAssociation, JobToOutputLibraryDatasetAssociation.table,
1210    properties=dict( job=relation( Job ), dataset=relation( LibraryDatasetDatasetAssociation, lazy=False ) ) )
1211
1212assign_mapper( context, JobParameter, JobParameter.table )
1213
1214assign_mapper( context, JobExternalOutputMetadata, JobExternalOutputMetadata.table,
1215    properties=dict( job = relation( Job ),
1216                     history_dataset_association = relation( HistoryDatasetAssociation, lazy = False ),
1217                     library_dataset_dataset_association = relation( LibraryDatasetDatasetAssociation, lazy = False ) ) )
1218                     
1219assign_mapper( context, JobExportHistoryArchive, JobExportHistoryArchive.table,
1220    properties=dict( job = relation( Job ),
1221                     history = relation( History ),
1222                     dataset = relation( Dataset ) ) )
1223
1224assign_mapper( context, PostJobAction, PostJobAction.table,
1225    properties=dict(workflow_step = relation( WorkflowStep, backref='post_job_actions', primaryjoin=(WorkflowStep.table.c.id == PostJobAction.table.c.workflow_step_id))))
1226
1227assign_mapper( context, PostJobActionAssociation, PostJobActionAssociation.table,
1228    properties=dict( job = relation( Job ),
1229                     post_job_action = relation( PostJobAction) ) )
1230
1231assign_mapper( context, Job, Job.table,
1232    properties=dict( user=relation( User.mapper ),
1233                     galaxy_session=relation( GalaxySession ),
1234                     history=relation( History ),
1235                     library_folder=relation( LibraryFolder ),
1236                     parameters=relation( JobParameter, lazy=False ),
1237                     input_datasets=relation( JobToInputDatasetAssociation, lazy=False ),
1238                     output_datasets=relation( JobToOutputDatasetAssociation, lazy=False ),
1239                     post_job_actions=relation( PostJobActionAssociation, lazy=False ),
1240                     output_library_datasets=relation( JobToOutputLibraryDatasetAssociation, lazy=False ),
1241                     external_output_metadata = relation( JobExternalOutputMetadata, lazy = False ) ) )
1242
1243assign_mapper( context, Event, Event.table,
1244    properties=dict( history=relation( History ),
1245                     galaxy_session=relation( GalaxySession ),
1246                     user=relation( User.mapper ) ) )
1247
1248assign_mapper( context, GalaxySession, GalaxySession.table,
1249    properties=dict( histories=relation( GalaxySessionToHistoryAssociation ),
1250                     current_history=relation( History ),
1251                     user=relation( User.mapper ) ) )
1252
1253assign_mapper( context, GalaxySessionToHistoryAssociation, GalaxySessionToHistoryAssociation.table,
1254    properties=dict( galaxy_session=relation( GalaxySession ),
1255                     history=relation( History ) ) )
1256
1257HistoryDatasetAssociation.mapper.add_property( "creating_job_associations", relation( JobToOutputDatasetAssociation ) )
1258
1259assign_mapper( context, Workflow, Workflow.table,
1260    properties=dict( steps=relation( WorkflowStep, backref='workflow',
1261                                     order_by=asc(WorkflowStep.table.c.order_index),
1262                                     cascade="all, delete-orphan",
1263                                     lazy=False ),
1264                     # outputs = relation( WorkflowOutput, backref='workflow',
1265                     #                 primaryjoin=(Workflow.table.c.id == WorkflowStep.table.c.workflow_id),
1266                     #                 secondaryjoin=(WorkflowStep.table.c.id == WorkflowOutput.table.c.workflow_step_id))
1267                                      ) )
1268
1269assign_mapper( context, WorkflowStep, WorkflowStep.table,
1270                properties=dict(
1271                    tags=relation(WorkflowStepTagAssociation, order_by=WorkflowStepTagAssociation.table.c.id, backref="workflow_steps"),
1272                    annotations=relation( WorkflowStepAnnotationAssociation, order_by=WorkflowStepAnnotationAssociation.table.c.id, backref="workflow_steps" ) )
1273                )
1274
1275assign_mapper( context, WorkflowOutput, WorkflowOutput.table,
1276    properties=dict(workflow_step = relation( WorkflowStep, backref='workflow_outputs', primaryjoin=(WorkflowStep.table.c.id == WorkflowOutput.table.c.workflow_step_id))))
1277
1278assign_mapper( context, WorkflowStepConnection, WorkflowStepConnection.table,
1279    properties=dict( input_step=relation( WorkflowStep, backref="input_connections", cascade="all",
1280                                          primaryjoin=( WorkflowStepConnection.table.c.input_step_id == WorkflowStep.table.c.id ) ),
1281                     output_step=relation( WorkflowStep, backref="output_connections", cascade="all",
1282                                           primaryjoin=( WorkflowStepConnection.table.c.output_step_id == WorkflowStep.table.c.id ) ) ) )
1283
1284
1285assign_mapper( context, StoredWorkflow, StoredWorkflow.table,
1286    properties=dict( user=relation( User,
1287                                    primaryjoin=( User.table.c.id == StoredWorkflow.table.c.user_id ),
1288                                    backref='stored_workflows' ),
1289                     workflows=relation( Workflow, backref='stored_workflow',
1290                                         cascade="all, delete-orphan",
1291                                         primaryjoin=( StoredWorkflow.table.c.id == Workflow.table.c.stored_workflow_id ) ),
1292                     latest_workflow=relation( Workflow, post_update=True,
1293                                               primaryjoin=( StoredWorkflow.table.c.latest_workflow_id == Workflow.table.c.id ),
1294                                               lazy=False ),
1295                     tags=relation( StoredWorkflowTagAssociation, order_by=StoredWorkflowTagAssociation.table.c.id, backref="stored_workflows" ),
1296                     annotations=relation( StoredWorkflowAnnotationAssociation, order_by=StoredWorkflowAnnotationAssociation.table.c.id, backref="stored_workflows" ),
1297                     ratings=relation( StoredWorkflowRatingAssociation, order_by=StoredWorkflowRatingAssociation.table.c.id, backref="stored_workflows" ) )
1298                   )
1299                   
1300# Set up proxy so that
1301#   StoredWorkflow.users_shared_with
1302# returns a list of users that workflow is shared with.
1303StoredWorkflow.users_shared_with_dot_users = association_proxy( 'users_shared_with', 'user' )
1304
1305assign_mapper( context, StoredWorkflowUserShareAssociation, StoredWorkflowUserShareAssociation.table,
1306    properties=dict( user=relation( User, backref='workflows_shared_by_others' ),
1307                     stored_workflow=relation( StoredWorkflow, backref='users_shared_with' )
1308                   ) )
1309
1310assign_mapper( context, StoredWorkflowMenuEntry, StoredWorkflowMenuEntry.table,
1311    properties=dict( stored_workflow=relation( StoredWorkflow ) ) )
1312
1313assign_mapper( context, WorkflowInvocation, WorkflowInvocation.table,
1314    properties=dict(
1315        steps=relation( WorkflowInvocationStep, backref='workflow_invocation', lazy=False ),
1316        workflow=relation( Workflow ) ) )
1317
1318assign_mapper( context, WorkflowInvocationStep, WorkflowInvocationStep.table,
1319    properties=dict(
1320        workflow_step = relation( WorkflowStep ),
1321        job = relation( Job, backref=backref( 'workflow_invocation_step', uselist=False ) ) ) )
1322
1323assign_mapper( context, MetadataFile, MetadataFile.table,
1324    properties=dict( history_dataset=relation( HistoryDatasetAssociation ), library_dataset=relation( LibraryDatasetDatasetAssociation ) ) )
1325
1326assign_mapper( context, PageRevision, PageRevision.table )
1327
1328assign_mapper( context, Page, Page.table,
1329    properties=dict( user=relation( User ),
1330                     revisions=relation( PageRevision, backref='page',
1331                                         cascade="all, delete-orphan",
1332                                         primaryjoin=( Page.table.c.id == PageRevision.table.c.page_id ) ),
1333                     latest_revision=relation( PageRevision, post_update=True,
1334                                               primaryjoin=( Page.table.c.latest_revision_id == PageRevision.table.c.id ),
1335                                               lazy=False ),
1336                     tags=relation(PageTagAssociation, order_by=PageTagAssociation.table.c.id, backref="pages"),
1337                     annotations=relation( PageAnnotationAssociation, order_by=PageAnnotationAssociation.table.c.id, backref="pages" ),
1338                     ratings=relation( PageRatingAssociation, order_by=PageRatingAssociation.table.c.id, backref="pages" ) 
1339                   ) )
1340                   
1341# Set up proxy so that
1342#   Page.users_shared_with
1343# returns a list of users that page is shared with.
1344Page.users_shared_with_dot_users = association_proxy( 'users_shared_with', 'user' )
1345                   
1346assign_mapper( context, PageUserShareAssociation, PageUserShareAssociation.table,
1347   properties=dict( user=relation( User, backref='pages_shared_by_others' ),
1348                    page=relation( Page, backref='users_shared_with' )
1349                  ) )
1350
1351assign_mapper( context, VisualizationRevision, VisualizationRevision.table )
1352
1353assign_mapper( context, Visualization, Visualization.table,
1354    properties=dict( user=relation( User ),
1355                     revisions=relation( VisualizationRevision, backref='visualization',
1356                                         cascade="all, delete-orphan",
1357                                         primaryjoin=( Visualization.table.c.id == VisualizationRevision.table.c.visualization_id ) ),
1358                     latest_revision=relation( VisualizationRevision, post_update=True,
1359                                               primaryjoin=( Visualization.table.c.latest_revision_id == VisualizationRevision.table.c.id ),
1360                                               lazy=False ),
1361                     tags=relation( VisualizationTagAssociation, order_by=VisualizationTagAssociation.table.c.id, backref="visualizations" ),
1362                     annotations=relation( VisualizationAnnotationAssociation, order_by=VisualizationAnnotationAssociation.table.c.id, backref="visualizations" ),
1363                     ratings=relation( VisualizationRatingAssociation, order_by=VisualizationRatingAssociation.table.c.id, backref="visualizations" )
1364                   ) )
1365                   
1366# Set up proxy so that
1367#   Visualization.users_shared_with
1368# returns a list of users that visualization is shared with.
1369Visualization.users_shared_with_dot_users = association_proxy( 'users_shared_with', 'user' )
1370                   
1371assign_mapper( context, VisualizationUserShareAssociation, VisualizationUserShareAssociation.table,
1372  properties=dict( user=relation( User, backref='visualizations_shared_by_others' ),
1373                   visualization=relation( Visualization, backref='users_shared_with' )
1374                 ) )
1375                 
1376# Tag tables.
1377
1378assign_mapper( context, Tag, Tag.table,
1379    properties=dict( children=relation(Tag, backref=backref( 'parent', remote_side=[Tag.table.c.id] ) ) 
1380                     ) )
1381
1382assign_mapper( context, HistoryTagAssociation, HistoryTagAssociation.table,
1383    properties=dict( tag=relation(Tag, backref="tagged_histories"), user=relation( User ) )
1384                     )
1385
1386assign_mapper( context, DatasetTagAssociation, DatasetTagAssociation.table,
1387    properties=dict( tag=relation(Tag, backref="tagged_datasets"), user=relation( User ) )
1388                     )
1389
1390assign_mapper( context, HistoryDatasetAssociationTagAssociation, HistoryDatasetAssociationTagAssociation.table,
1391    properties=dict( tag=relation(Tag, backref="tagged_history_dataset_associations"), user=relation( User ) )
1392                     )
1393
1394assign_mapper( context, PageTagAssociation, PageTagAssociation.table,
1395    properties=dict( tag=relation(Tag, backref="tagged_pages"), user=relation( User ) )
1396                    )
1397                   
1398assign_mapper( context, StoredWorkflowTagAssociation, StoredWorkflowTagAssociation.table,
1399    properties=dict( tag=relation(Tag, backref="tagged_workflows"), user=relation( User ) )
1400                    )
1401                   
1402assign_mapper( context, WorkflowStepTagAssociation, WorkflowStepTagAssociation.table,
1403    properties=dict( tag=relation(Tag, backref="tagged_workflow_steps"), user=relation( User ) )
1404                    )
1405                   
1406assign_mapper( context, VisualizationTagAssociation, VisualizationTagAssociation.table,
1407    properties=dict( tag=relation(Tag, backref="tagged_visualizations"), user=relation( User ) )
1408                    )
1409                   
1410# Annotation tables.
1411                   
1412assign_mapper( context, HistoryAnnotationAssociation, HistoryAnnotationAssociation.table,
1413    properties=dict( history=relation( History ), user=relation( User ) )
1414                    )
1415                   
1416assign_mapper( context, HistoryDatasetAssociationAnnotationAssociation, HistoryDatasetAssociationAnnotationAssociation.table,
1417    properties=dict( hda=relation( HistoryDatasetAssociation ), user=relation( User ) )
1418                    )
1419                   
1420assign_mapper( context, StoredWorkflowAnnotationAssociation, StoredWorkflowAnnotationAssociation.table,
1421    properties=dict( stored_workflow=relation( StoredWorkflow ), user=relation( User ) )
1422                    )
1423
1424assign_mapper( context, WorkflowStepAnnotationAssociation, WorkflowStepAnnotationAssociation.table,
1425    properties=dict( workflow_step=relation( WorkflowStep ), user=relation( User ) )
1426                    )
1427                   
1428assign_mapper( context, PageAnnotationAssociation, PageAnnotationAssociation.table,
1429    properties=dict( page=relation( Page ), user=relation( User ) )
1430                    )
1431                   
1432assign_mapper( context, VisualizationAnnotationAssociation, VisualizationAnnotationAssociation.table,
1433    properties=dict( visualization=relation( Visualization ), user=relation( User ) )
1434                    )
1435                   
1436# Rating tables.
1437
1438assign_mapper( context, HistoryRatingAssociation, HistoryRatingAssociation.table,
1439    properties=dict( history=relation( History ), user=relation( User ) )
1440                    )
1441                   
1442assign_mapper( context, HistoryDatasetAssociationRatingAssociation, HistoryDatasetAssociationRatingAssociation.table,
1443    properties=dict( hda=relation( HistoryDatasetAssociation ), user=relation( User ) )
1444                    )
1445                   
1446assign_mapper( context, StoredWorkflowRatingAssociation, StoredWorkflowRatingAssociation.table,
1447    properties=dict( stored_workflow=relation( StoredWorkflow ), user=relation( User ) )
1448                    )
1449
1450assign_mapper( context, PageRatingAssociation, PageRatingAssociation.table,
1451    properties=dict( page=relation( Page ), user=relation( User ) )
1452                    )
1453
1454assign_mapper( context, VisualizationRatingAssociation, VisualizationRatingAssociation.table,
1455    properties=dict( visualization=relation( Visualization ), user=relation( User ) )
1456                    )
1457
1458# User tables.
1459                   
1460assign_mapper( context, UserPreference, UserPreference.table,
1461    properties = {}
1462              )
1463             
1464assign_mapper( context, UserAction, UserAction.table,
1465  properties = dict( user=relation( User.mapper ) )
1466            )
1467
1468assign_mapper( context, APIKeys, APIKeys.table,
1469    properties = {} )
1470   
1471# Helper methods.
1472
1473def db_next_hid( self ):
1474    """
1475    Override __next_hid to generate from the database in a concurrency
1476    safe way.
1477    """
1478    conn = object_session( self ).connection()
1479    table = self.table
1480    trans = conn.begin()
1481    try:
1482        next_hid = select( [table.c.hid_counter], table.c.id == self.id, for_update=True ).scalar()
1483        table.update( table.c.id == self.id ).execute( hid_counter = ( next_hid + 1 ) )
1484        trans.commit()
1485        return next_hid
1486    except:
1487        trans.rollback()
1488        raise
1489
1490History._next_hid = db_next_hid
1491
1492def guess_dialect_for_url( url ):
1493    return (url.split(':', 1))[0]
1494
1495def load_egg_for_url( url ):
1496    # Load the appropriate db module
1497    dialect = guess_dialect_for_url( url )
1498    try:
1499        egg = dialect_to_egg[dialect]
1500        try:
1501            pkg_resources.require( egg )
1502            log.debug( "%s egg successfully loaded for %s dialect" % ( egg, dialect ) )
1503        except:
1504            # If the module's in the path elsewhere (i.e. non-egg), it'll still load.
1505            log.warning( "%s egg not found, but an attempt will be made to use %s anyway" % ( egg, dialect ) )
1506    except KeyError:
1507        # Let this go, it could possibly work with db's we don't support
1508        log.error( "database_connection contains an unknown SQLAlchemy database dialect: %s" % dialect )
1509
1510def init( file_path, url, engine_options={}, create_tables=False, database_query_profiling_proxy=False ):
1511    """Connect mappings to the database"""
1512    # Connect dataset to the file path
1513    Dataset.file_path = file_path
1514    # Load the appropriate db module
1515    load_egg_for_url( url )
1516    # Should we use the logging proxy?
1517    if database_query_profiling_proxy:
1518        import galaxy.model.orm.logging_connection_proxy as logging_connection_proxy
1519        proxy = logging_connection_proxy.LoggingProxy()
1520    else:
1521        proxy = None
1522    # Create the database engine
1523    engine = create_engine( url, proxy=proxy, **engine_options )
1524    # Connect the metadata to the database.
1525    metadata.bind = engine
1526    # Clear any existing contextual sessions and reconfigure
1527    Session.remove()
1528    Session.configure( bind=engine )
1529    # Create tables if needed
1530    if create_tables:
1531        metadata.create_all()
1532        # metadata.engine.commit()
1533    # Pack everything into a bunch
1534    result = Bunch( **globals() )
1535    result.engine = engine
1536    # model.flush() has been removed.
1537    result.session = Session
1538    # For backward compatibility with "model.context.current"
1539    result.context = Session
1540    result.create_tables = create_tables
1541    #load local galaxy security policy
1542    result.security_agent = GalaxyRBACAgent( result )
1543    return result
1544   
1545def get_suite():
1546    """Get unittest suite for this module"""
1547    import unittest, mapping_tests
1548    return unittest.makeSuite( mapping_tests.MappingTests )
1549
Note: リポジトリブラウザについてのヘルプは TracBrowser を参照してください。