PageRenderTime 180ms CodeModel.GetById 19ms app.highlight 149ms RepoModel.GetById 0ms app.codeStats 1ms

/lib/galaxy/model/mapping.py

https://bitbucket.org/cistrome/cistrome-harvard/
Python | 1910 lines | 1580 code | 266 blank | 64 comment | 6 complexity | 671bc2833c7941cf78847d9c8f1d10bd MD5 | raw file
   1"""
   2Details of how the data model objects are mapped onto the relational database
   3are encapsulated here.
   4"""
   5
   6import logging
   7import pkg_resources
   8
   9from sqlalchemy import and_, asc, Boolean, Column, DateTime, desc, ForeignKey, Integer, MetaData, not_, Numeric, select, String, Table, TEXT, Unicode, UniqueConstraint
  10from sqlalchemy.ext.associationproxy import association_proxy
  11from sqlalchemy.ext.orderinglist import ordering_list
  12from sqlalchemy.types import BigInteger
  13from sqlalchemy.orm import backref, object_session, relation, mapper, class_mapper
  14from sqlalchemy.orm.collections import attribute_mapped_collection
  15
  16from galaxy import model
  17from galaxy.model.orm.engine_factory import build_engine
  18from galaxy.model.orm.now import now
  19from galaxy.model.custom_types import JSONType, MetadataType, TrimmedString, UUIDType
  20from galaxy.model.base import ModelMapping
  21from galaxy.security import GalaxyRBACAgent
  22
  23log = logging.getLogger( __name__ )
  24
  25metadata = MetaData()
  26
  27
  28model.User.table = Table( "galaxy_user", metadata,
  29    Column( "id", Integer, primary_key=True),
  30    Column( "create_time", DateTime, default=now ),
  31    Column( "update_time", DateTime, default=now, onupdate=now ),
  32    Column( "email", TrimmedString( 255 ), index=True, nullable=False ),
  33    Column( "username", TrimmedString( 255 ), index=True, unique=True ),
  34    Column( "password", TrimmedString( 255 ), nullable=False ),
  35    Column( "external", Boolean, default=False ),
  36    Column( "form_values_id", Integer, ForeignKey( "form_values.id" ), index=True ),
  37    Column( "deleted", Boolean, index=True, default=False ),
  38    Column( "purged", Boolean, index=True, default=False ),
  39    Column( "disk_usage", Numeric( 15, 0 ), index=True ) ,
  40    Column( "active", Boolean, index=True, default=True, nullable=False ),
  41    Column( "activation_token", TrimmedString( 64 ), nullable=True, index=True ) )
  42
  43
  44model.UserAddress.table = Table( "user_address", metadata,
  45    Column( "id", Integer, primary_key=True),
  46    Column( "create_time", DateTime, default=now ),
  47    Column( "update_time", DateTime, default=now, onupdate=now ),
  48    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
  49    Column( "desc", TrimmedString( 255 )),
  50    Column( "name", TrimmedString( 255 ), nullable=False),
  51    Column( "institution", TrimmedString( 255 )),
  52    Column( "address", TrimmedString( 255 ), nullable=False),
  53    Column( "city", TrimmedString( 255 ), nullable=False),
  54    Column( "state", TrimmedString( 255 ), nullable=False),
  55    Column( "postal_code", TrimmedString( 255 ), nullable=False),
  56    Column( "country", TrimmedString( 255 ), nullable=False),
  57    Column( "phone", TrimmedString( 255 )),
  58    Column( "deleted", Boolean, index=True, default=False ),
  59    Column( "purged", Boolean, index=True, default=False ) )
  60
  61model.UserOpenID.table = Table( "galaxy_user_openid", metadata,
  62    Column( "id", Integer, primary_key=True ),
  63    Column( "create_time", DateTime, default=now ),
  64    Column( "update_time", DateTime, index=True, default=now, onupdate=now ),
  65    Column( "session_id", Integer, ForeignKey( "galaxy_session.id" ), index=True ),
  66    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
  67    Column( "openid", TEXT, index=True, unique=True ),
  68    Column( "provider", TrimmedString( 255 ) ),
  69    )
  70
  71model.History.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( "importing", Boolean, index=True, default=False ),
  81    Column( "genome_build", TrimmedString( 40 ) ),
  82    Column( "importable", Boolean, default=False ),
  83    Column( "slug", TEXT, index=True ),
  84    Column( "published", Boolean, index=True, default=False ) )
  85
  86model.HistoryUserShareAssociation.table = Table( "history_user_share_association", metadata,
  87    Column( "id", Integer, primary_key=True ),
  88    Column( "history_id", Integer, ForeignKey( "history.id" ), index=True ),
  89    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True )
  90    )
  91
  92model.HistoryDatasetAssociation.table = Table( "history_dataset_association", metadata,
  93    Column( "id", Integer, primary_key=True ),
  94    Column( "history_id", Integer, ForeignKey( "history.id" ), index=True ),
  95    Column( "dataset_id", Integer, ForeignKey( "dataset.id" ), index=True ),
  96    Column( "create_time", DateTime, default=now ),
  97    Column( "update_time", DateTime, default=now, onupdate=now ),
  98    Column( "state", TrimmedString( 64 ), index=True, key="_state" ),
  99    Column( "copied_from_history_dataset_association_id", Integer, ForeignKey( "history_dataset_association.id" ), nullable=True ),
 100    Column( "copied_from_library_dataset_dataset_association_id", Integer, ForeignKey( "library_dataset_dataset_association.id" ), nullable=True ),
 101    Column( "hid", Integer ),
 102    Column( "name", TrimmedString( 255 ) ),
 103    Column( "info", TrimmedString( 255 ) ),
 104    Column( "blurb", TrimmedString( 255 ) ),
 105    Column( "peek" , TEXT ),
 106    Column( "tool_version" , TEXT ),
 107    Column( "extension", TrimmedString( 64 ) ),
 108    Column( "metadata", MetadataType(), key="_metadata" ),
 109    Column( "parent_id", Integer, ForeignKey( "history_dataset_association.id" ), nullable=True ),
 110    Column( "designation", TrimmedString( 255 ) ),
 111    Column( "deleted", Boolean, index=True, default=False ),
 112    Column( "purged", Boolean, index=True, default=False ),
 113    Column( "visible", Boolean ),
 114    Column( "extended_metadata_id", Integer,
 115        ForeignKey( "extended_metadata.id" ), index=True )
 116    )
 117
 118model.Dataset.table = Table( "dataset", metadata,
 119    Column( "id", Integer, primary_key=True ),
 120    Column( "create_time", DateTime, default=now ),
 121    Column( "update_time", DateTime, index=True, default=now, onupdate=now ),
 122    Column( "state", TrimmedString( 64 ), index=True ),
 123    Column( "deleted", Boolean, index=True, default=False ),
 124    Column( "purged", Boolean, index=True, default=False ),
 125    Column( "purgable", Boolean, default=True ),
 126    Column( "object_store_id", TrimmedString( 255 ), index=True ),
 127    Column( "external_filename" , TEXT ),
 128    Column( "_extra_files_path", TEXT ),
 129    Column( 'file_size', Numeric( 15, 0 ) ),
 130    Column( 'total_size', Numeric( 15, 0 ) ),
 131    Column( 'uuid', UUIDType() ) )
 132
 133model.HistoryDatasetAssociationDisplayAtAuthorization.table = Table( "history_dataset_association_display_at_authorization", metadata,
 134    Column( "id", Integer, primary_key=True ),
 135    Column( "create_time", DateTime, default=now ),
 136    Column( "update_time", DateTime, index=True, default=now, onupdate=now ),
 137    Column( "history_dataset_association_id", Integer, ForeignKey( "history_dataset_association.id" ), index=True ),
 138    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
 139    Column( "site", TrimmedString( 255 ) ) )
 140
 141model.HistoryDatasetAssociationSubset.table = Table( "history_dataset_association_subset", metadata,
 142    Column( "id", Integer, primary_key=True ),
 143    Column( "history_dataset_association_id", Integer, ForeignKey( "history_dataset_association.id" ), index=True ),
 144    Column( "history_dataset_association_subset_id", Integer, ForeignKey( "history_dataset_association.id" ), index=True ),
 145    Column( "location", Unicode(255), index=True) )
 146
 147model.ImplicitlyConvertedDatasetAssociation.table = Table( "implicitly_converted_dataset_association", metadata,
 148    Column( "id", Integer, primary_key=True ),
 149    Column( "create_time", DateTime, default=now ),
 150    Column( "update_time", DateTime, default=now, onupdate=now ),
 151    Column( "hda_id", Integer, ForeignKey( "history_dataset_association.id" ), index=True, nullable=True ),
 152    Column( "ldda_id", Integer, ForeignKey( "library_dataset_dataset_association.id" ), index=True, nullable=True ),
 153    Column( "hda_parent_id", Integer, ForeignKey( "history_dataset_association.id" ), index=True ),
 154    Column( "ldda_parent_id", Integer, ForeignKey( "library_dataset_dataset_association.id" ), index=True ),
 155    Column( "deleted", Boolean, index=True, default=False ),
 156    Column( "metadata_safe", Boolean, index=True, default=True ),
 157    Column( "type", TrimmedString( 255 ) ) )
 158
 159model.ValidationError.table = Table( "validation_error", metadata,
 160    Column( "id", Integer, primary_key=True ),
 161    Column( "dataset_id", Integer, ForeignKey( "history_dataset_association.id" ), index=True ),
 162    Column( "message", TrimmedString( 255 ) ),
 163    Column( "err_type", TrimmedString( 64 ) ),
 164    Column( "attributes", TEXT ) )
 165
 166model.Group.table = Table( "galaxy_group", metadata,
 167    Column( "id", Integer, primary_key=True ),
 168    Column( "create_time", DateTime, default=now ),
 169    Column( "update_time", DateTime, default=now, onupdate=now ),
 170    Column( "name", String( 255 ), index=True, unique=True ),
 171    Column( "deleted", Boolean, index=True, default=False ) )
 172
 173model.UserGroupAssociation.table = Table( "user_group_association", metadata,
 174    Column( "id", Integer, primary_key=True ),
 175    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
 176    Column( "group_id", Integer, ForeignKey( "galaxy_group.id" ), index=True ),
 177    Column( "create_time", DateTime, default=now ),
 178    Column( "update_time", DateTime, default=now, onupdate=now ) )
 179
 180model.UserRoleAssociation.table = Table( "user_role_association", metadata,
 181    Column( "id", Integer, primary_key=True ),
 182    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
 183    Column( "role_id", Integer, ForeignKey( "role.id" ), index=True ),
 184    Column( "create_time", DateTime, default=now ),
 185    Column( "update_time", DateTime, default=now, onupdate=now ) )
 186
 187model.GroupRoleAssociation.table = Table( "group_role_association", metadata,
 188    Column( "id", Integer, primary_key=True ),
 189    Column( "group_id", Integer, ForeignKey( "galaxy_group.id" ), index=True ),
 190    Column( "role_id", Integer, ForeignKey( "role.id" ), index=True ),
 191    Column( "create_time", DateTime, default=now ),
 192    Column( "update_time", DateTime, default=now, onupdate=now ) )
 193
 194model.Role.table = Table( "role", 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( "name", String( 255 ), index=True, unique=True ),
 199    Column( "description", TEXT ),
 200    Column( "type", String( 40 ), index=True ),
 201    Column( "deleted", Boolean, index=True, default=False ) )
 202
 203model.UserQuotaAssociation.table = Table( "user_quota_association", metadata,
 204    Column( "id", Integer, primary_key=True ),
 205    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
 206    Column( "quota_id", Integer, ForeignKey( "quota.id" ), index=True ),
 207    Column( "create_time", DateTime, default=now ),
 208    Column( "update_time", DateTime, default=now, onupdate=now ) )
 209
 210model.GroupQuotaAssociation.table = Table( "group_quota_association", metadata,
 211    Column( "id", Integer, primary_key=True ),
 212    Column( "group_id", Integer, ForeignKey( "galaxy_group.id" ), index=True ),
 213    Column( "quota_id", Integer, ForeignKey( "quota.id" ), index=True ),
 214    Column( "create_time", DateTime, default=now ),
 215    Column( "update_time", DateTime, default=now, onupdate=now ) )
 216
 217model.Quota.table = Table( "quota", metadata,
 218    Column( "id", Integer, primary_key=True ),
 219    Column( "create_time", DateTime, default=now ),
 220    Column( "update_time", DateTime, default=now, onupdate=now ),
 221    Column( "name", String( 255 ), index=True, unique=True ),
 222    Column( "description", TEXT ),
 223    Column( "bytes", BigInteger ),
 224    Column( "operation", String( 8 ) ),
 225    Column( "deleted", Boolean, index=True, default=False ) )
 226
 227model.DefaultQuotaAssociation.table = Table( "default_quota_association", metadata,
 228    Column( "id", Integer, primary_key=True ),
 229    Column( "create_time", DateTime, default=now ),
 230    Column( "update_time", DateTime, default=now, onupdate=now ),
 231    Column( "type", String( 32 ), index=True, unique=True ),
 232    Column( "quota_id", Integer, ForeignKey( "quota.id" ), index=True ) )
 233
 234model.DatasetPermissions.table = Table( "dataset_permissions", metadata,
 235    Column( "id", Integer, primary_key=True ),
 236    Column( "create_time", DateTime, default=now ),
 237    Column( "update_time", DateTime, default=now, onupdate=now ),
 238    Column( "action", TEXT ),
 239    Column( "dataset_id", Integer, ForeignKey( "dataset.id" ), index=True ),
 240    Column( "role_id", Integer, ForeignKey( "role.id" ), index=True ) )
 241
 242model.LibraryPermissions.table = Table( "library_permissions", metadata,
 243    Column( "id", Integer, primary_key=True ),
 244    Column( "create_time", DateTime, default=now ),
 245    Column( "update_time", DateTime, default=now, onupdate=now ),
 246    Column( "action", TEXT ),
 247    Column( "library_id", Integer, ForeignKey( "library.id" ), nullable=True, index=True ),
 248    Column( "role_id", Integer, ForeignKey( "role.id" ), index=True ) )
 249
 250model.LibraryFolderPermissions.table = Table( "library_folder_permissions", metadata,
 251    Column( "id", Integer, primary_key=True ),
 252    Column( "create_time", DateTime, default=now ),
 253    Column( "update_time", DateTime, default=now, onupdate=now ),
 254    Column( "action", TEXT ),
 255    Column( "library_folder_id", Integer, ForeignKey( "library_folder.id" ), nullable=True, index=True ),
 256    Column( "role_id", Integer, ForeignKey( "role.id" ), index=True ) )
 257
 258model.LibraryDatasetPermissions.table = Table( "library_dataset_permissions", metadata,
 259    Column( "id", Integer, primary_key=True ),
 260    Column( "create_time", DateTime, default=now ),
 261    Column( "update_time", DateTime, default=now, onupdate=now ),
 262    Column( "action", TEXT ),
 263    Column( "library_dataset_id", Integer, ForeignKey( "library_dataset.id" ), nullable=True, index=True ),
 264    Column( "role_id", Integer, ForeignKey( "role.id" ), index=True ) )
 265
 266model.LibraryDatasetDatasetAssociationPermissions.table = Table( "library_dataset_dataset_association_permissions", metadata,
 267    Column( "id", Integer, primary_key=True ),
 268    Column( "create_time", DateTime, default=now ),
 269    Column( "update_time", DateTime, default=now, onupdate=now ),
 270    Column( "action", TEXT ),
 271    Column( "library_dataset_dataset_association_id", Integer, ForeignKey( "library_dataset_dataset_association.id" ), nullable=True, index=True ),
 272    Column( "role_id", Integer, ForeignKey( "role.id" ), index=True ) )
 273
 274model.DefaultUserPermissions.table = Table( "default_user_permissions", metadata,
 275    Column( "id", Integer, primary_key=True ),
 276    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
 277    Column( "action", TEXT ),
 278    Column( "role_id", Integer, ForeignKey( "role.id" ), index=True ) )
 279
 280model.DefaultHistoryPermissions.table = Table( "default_history_permissions", metadata,
 281    Column( "id", Integer, primary_key=True ),
 282    Column( "history_id", Integer, ForeignKey( "history.id" ), index=True ),
 283    Column( "action", TEXT ),
 284    Column( "role_id", Integer, ForeignKey( "role.id" ), index=True ) )
 285
 286model.LibraryDataset.table = Table( "library_dataset", metadata,
 287    Column( "id", Integer, primary_key=True ),
 288    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
 289    Column( "folder_id", Integer, ForeignKey( "library_folder.id" ), index=True ),
 290    Column( "order_id", Integer ), #not currently being used, but for possible future use
 291    Column( "create_time", DateTime, default=now ),
 292    Column( "update_time", DateTime, default=now, onupdate=now ),
 293    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?)
 294    Column( "info", TrimmedString( 255 ),  key="_info" ), #when not None/null this will supercede display in library (but not when imported into user's history?)
 295    Column( "deleted", Boolean, index=True, default=False ),
 296    Column( "purged", Boolean, index=True, default=False ) )
 297
 298model.LibraryDatasetDatasetAssociation.table = Table( "library_dataset_dataset_association", metadata,
 299    Column( "id", Integer, primary_key=True ),
 300    Column( "library_dataset_id", Integer, ForeignKey( "library_dataset.id" ), index=True ),
 301    Column( "dataset_id", Integer, ForeignKey( "dataset.id" ), index=True ),
 302    Column( "create_time", DateTime, default=now ),
 303    Column( "update_time", DateTime, default=now, onupdate=now ),
 304    Column( "state", TrimmedString( 64 ), index=True, key="_state" ),
 305    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 ),
 306    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 ),
 307    Column( "name", TrimmedString( 255 ), index=True ),
 308    Column( "info", TrimmedString( 255 ) ),
 309    Column( "blurb", TrimmedString( 255 ) ),
 310    Column( "peek" , TEXT ),
 311    Column( "tool_version" , TEXT ),
 312    Column( "extension", TrimmedString( 64 ) ),
 313    Column( "metadata", MetadataType(), key="_metadata" ),
 314    Column( "parent_id", Integer, ForeignKey( "library_dataset_dataset_association.id" ), nullable=True ),
 315    Column( "designation", TrimmedString( 255 ) ),
 316    Column( "deleted", Boolean, index=True, default=False ),
 317    Column( "visible", Boolean ),
 318    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
 319    Column( "message", TrimmedString( 255 ) ),
 320    Column( "extended_metadata_id", Integer,
 321        ForeignKey( "extended_metadata.id" ), index=True )
 322    )
 323
 324
 325model.ExtendedMetadata.table = Table("extended_metadata", metadata,
 326    Column( "id", Integer, primary_key=True ),
 327    Column( "data", JSONType ) )
 328
 329model.ExtendedMetadataIndex.table = Table("extended_metadata_index", metadata,
 330    Column( "id", Integer, primary_key=True ),
 331    Column( "extended_metadata_id", Integer, ForeignKey("extended_metadata.id",
 332                                                        onupdate="CASCADE",
 333                                                        ondelete="CASCADE" ),
 334                                             index=True ),
 335    Column( "path", String( 255 )),
 336    Column( "value", TEXT))
 337
 338model.Library.table = Table( "library", metadata,
 339    Column( "id", Integer, primary_key=True ),
 340    Column( "root_folder_id", Integer, ForeignKey( "library_folder.id" ), index=True ),
 341    Column( "create_time", DateTime, default=now ),
 342    Column( "update_time", DateTime, default=now, onupdate=now ),
 343    Column( "name", String( 255 ), index=True ),
 344    Column( "deleted", Boolean, index=True, default=False ),
 345    Column( "purged", Boolean, index=True, default=False ),
 346    Column( "description", TEXT ),
 347    Column( "synopsis", TEXT ) )
 348
 349model.LibraryFolder.table = Table( "library_folder", metadata,
 350    Column( "id", Integer, primary_key=True ),
 351    Column( "parent_id", Integer, ForeignKey( "library_folder.id" ), nullable = True, index=True ),
 352    Column( "create_time", DateTime, default=now ),
 353    Column( "update_time", DateTime, default=now, onupdate=now ),
 354    Column( "name", TEXT, index=True ),
 355    Column( "description", TEXT ),
 356    Column( "order_id", Integer ), #not currently being used, but for possible future use
 357    Column( "item_count", Integer ),
 358    Column( "deleted", Boolean, index=True, default=False ),
 359    Column( "purged", Boolean, index=True, default=False ),
 360    Column( "genome_build", TrimmedString( 40 ) ) )
 361
 362model.LibraryInfoAssociation.table = Table( 'library_info_association', metadata,
 363    Column( "id", Integer, primary_key=True ),
 364    Column( "library_id", Integer, ForeignKey( "library.id" ), index=True ),
 365    Column( "form_definition_id", Integer, ForeignKey( "form_definition.id" ), index=True ),
 366    Column( "form_values_id", Integer, ForeignKey( "form_values.id" ), index=True ),
 367    Column( "inheritable", Boolean, index=True, default=False ),
 368    Column( "deleted", Boolean, index=True, default=False ) )
 369
 370model.LibraryFolderInfoAssociation.table = Table( 'library_folder_info_association', metadata,
 371    Column( "id", Integer, primary_key=True ),
 372    Column( "library_folder_id", Integer, ForeignKey( "library_folder.id" ), nullable=True, index=True ),
 373    Column( "form_definition_id", Integer, ForeignKey( "form_definition.id" ), index=True ),
 374    Column( "form_values_id", Integer, ForeignKey( "form_values.id" ), index=True ),
 375    Column( "inheritable", Boolean, index=True, default=False ),
 376    Column( "deleted", Boolean, index=True, default=False ) )
 377
 378model.LibraryDatasetDatasetInfoAssociation.table = Table( 'library_dataset_dataset_info_association', metadata,
 379    Column( "id", Integer, primary_key=True ),
 380    Column( "library_dataset_dataset_association_id", Integer, ForeignKey( "library_dataset_dataset_association.id" ), nullable=True, index=True ),
 381    Column( "form_definition_id", Integer, ForeignKey( "form_definition.id" ), index=True ),
 382    Column( "form_values_id", Integer, ForeignKey( "form_values.id" ), index=True ),
 383    Column( "deleted", Boolean, index=True, default=False ) )
 384
 385model.Job.table = Table( "job", metadata,
 386    Column( "id", Integer, primary_key=True ),
 387    Column( "create_time", DateTime, default=now ),
 388    Column( "update_time", DateTime, default=now, onupdate=now ),
 389    Column( "history_id", Integer, ForeignKey( "history.id" ), index=True ),
 390    Column( "library_folder_id", Integer, ForeignKey( "library_folder.id" ), index=True ),
 391    Column( "tool_id", String( 255 ) ),
 392    Column( "tool_version", TEXT, default="1.0.0" ),
 393    Column( "state", String( 64 ), index=True ),
 394    Column( "info", TrimmedString( 255 ) ),
 395    Column( "command_line", TEXT ),
 396    Column( "param_filename", String( 1024 ) ),
 397    Column( "runner_name", String( 255 ) ),
 398    Column( "stdout", TEXT ),
 399    Column( "stderr", TEXT ),
 400    Column( "exit_code", Integer, nullable=True ),
 401    Column( "traceback", TEXT ),
 402    Column( "session_id", Integer, ForeignKey( "galaxy_session.id" ), index=True, nullable=True ),
 403    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True, nullable=True ),
 404    Column( "job_runner_name", String( 255 ) ),
 405    Column( "job_runner_external_id", String( 255 ) ),
 406    Column( "destination_id", String( 255 ), nullable=True ),
 407    Column( "destination_params", JSONType, nullable=True ),
 408    Column( "object_store_id", TrimmedString( 255 ), index=True ),
 409    Column( "imported", Boolean, default=False, index=True ),
 410    Column( "params", TrimmedString(255), index=True ),
 411    Column( "handler", TrimmedString( 255 ), index=True ) )
 412
 413model.JobParameter.table = Table( "job_parameter", metadata,
 414    Column( "id", Integer, primary_key=True ),
 415    Column( "job_id", Integer, ForeignKey( "job.id" ), index=True ),
 416    Column( "name", String(255) ),
 417    Column( "value", TEXT ) )
 418
 419model.JobToInputDatasetAssociation.table = Table( "job_to_input_dataset", metadata,
 420    Column( "id", Integer, primary_key=True ),
 421    Column( "job_id", Integer, ForeignKey( "job.id" ), index=True ),
 422    Column( "dataset_id", Integer, ForeignKey( "history_dataset_association.id" ), index=True ),
 423    Column( "name", String(255) ) )
 424
 425model.JobToOutputDatasetAssociation.table = Table( "job_to_output_dataset", metadata,
 426    Column( "id", Integer, primary_key=True ),
 427    Column( "job_id", Integer, ForeignKey( "job.id" ), index=True ),
 428    Column( "dataset_id", Integer, ForeignKey( "history_dataset_association.id" ), index=True ),
 429    Column( "name", String(255) ) )
 430
 431model.JobToInputLibraryDatasetAssociation.table = Table( "job_to_input_library_dataset", metadata,
 432    Column( "id", Integer, primary_key=True ),
 433    Column( "job_id", Integer, ForeignKey( "job.id" ), index=True ),
 434    Column( "ldda_id", Integer, ForeignKey( "library_dataset_dataset_association.id" ), index=True ),
 435    Column( "name", String(255) ) )
 436
 437model.JobToOutputLibraryDatasetAssociation.table = Table( "job_to_output_library_dataset", metadata,
 438    Column( "id", Integer, primary_key=True ),
 439    Column( "job_id", Integer, ForeignKey( "job.id" ), index=True ),
 440    Column( "ldda_id", Integer, ForeignKey( "library_dataset_dataset_association.id" ), index=True ),
 441    Column( "name", String(255) ) )
 442
 443model.JobExternalOutputMetadata.table = Table( "job_external_output_metadata", metadata,
 444    Column( "id", Integer, primary_key=True ),
 445    Column( "job_id", Integer, ForeignKey( "job.id" ), index=True ),
 446    Column( "history_dataset_association_id", Integer, ForeignKey( "history_dataset_association.id" ), index=True, nullable=True ),
 447    Column( "library_dataset_dataset_association_id", Integer, ForeignKey( "library_dataset_dataset_association.id" ), index=True, nullable=True ),
 448    Column( "filename_in", String( 255 ) ),
 449    Column( "filename_out", String( 255 ) ),
 450    Column( "filename_results_code", String( 255 ) ),
 451    Column( "filename_kwds", String( 255 ) ),
 452    Column( "filename_override_metadata", String( 255 ) ),
 453    Column( "job_runner_external_pid", String( 255 ) ) )
 454
 455model.JobExportHistoryArchive.table = Table( "job_export_history_archive", metadata,
 456    Column( "id", Integer, primary_key=True ),
 457    Column( "job_id", Integer, ForeignKey( "job.id" ), index=True ),
 458    Column( "history_id", Integer, ForeignKey( "history.id" ), index=True ),
 459    Column( "dataset_id", Integer, ForeignKey( "dataset.id" ), index=True ),
 460    Column( "compressed", Boolean, index=True, default=False ),
 461    Column( "history_attrs_filename", TEXT ),
 462    Column( "datasets_attrs_filename", TEXT ),
 463    Column( "jobs_attrs_filename", TEXT )
 464    )
 465
 466model.JobImportHistoryArchive.table = Table( "job_import_history_archive", metadata,
 467    Column( "id", Integer, primary_key=True ),
 468    Column( "job_id", Integer, ForeignKey( "job.id" ), index=True ),
 469    Column( "history_id", Integer, ForeignKey( "history.id" ), index=True ),
 470    Column( "archive_dir", TEXT )
 471    )
 472
 473model.GenomeIndexToolData.table = Table( "genome_index_tool_data", metadata,
 474    Column( "id", Integer, primary_key=True ),
 475    Column( "job_id", Integer, ForeignKey( "job.id" ), index=True ),
 476    Column( "deferred_job_id", Integer, ForeignKey( "deferred_job.id" ), index=True ),
 477    Column( "transfer_job_id", Integer, ForeignKey( "transfer_job.id" ), index=True ),
 478    Column( "dataset_id", Integer, ForeignKey( "dataset.id" ), index=True ),
 479    Column( "fasta_path", String( 255 ) ),
 480    Column( "created_time", DateTime, default=now ),
 481    Column( "modified_time", DateTime, default=now, onupdate=now ),
 482    Column( "indexer", String( 64 ) ),
 483    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
 484    )
 485
 486model.Task.table = Table( "task", metadata,
 487    Column( "id", Integer, primary_key=True ),
 488    Column( "create_time", DateTime, default=now ),
 489    Column( "execution_time", DateTime ),
 490    Column( "update_time", DateTime, default=now, onupdate=now ),
 491    Column( "state", String( 64 ), index=True ),
 492    Column( "command_line", TEXT ),
 493    Column( "param_filename", String( 1024 ) ),
 494    Column( "runner_name", String( 255 ) ),
 495    Column( "stdout", TEXT ),
 496    Column( "stderr", TEXT ),
 497    Column( "exit_code", Integer, nullable=True ),
 498    Column( "info", TrimmedString ( 255 ) ),
 499    Column( "traceback", TEXT ),
 500    Column( "job_id", Integer, ForeignKey( "job.id" ), index=True, nullable=False ),
 501    Column( "working_directory", String(1024)),
 502    Column( "task_runner_name", String( 255 ) ),
 503    Column( "task_runner_external_id", String( 255 ) ),
 504    Column( "prepare_input_files_cmd", TEXT ) )
 505
 506model.PostJobAction.table = Table("post_job_action", metadata,
 507    Column("id", Integer, primary_key=True),
 508    Column("workflow_step_id", Integer, ForeignKey( "workflow_step.id" ), index=True, nullable=False),
 509    Column("action_type", String(255), nullable=False),
 510    Column("output_name", String(255), nullable=True),
 511    Column("action_arguments", JSONType, nullable=True))
 512
 513model.PostJobActionAssociation.table = Table("post_job_action_association", metadata,
 514    Column("id", Integer, primary_key=True),
 515    Column("job_id", Integer, ForeignKey( "job.id" ), index=True, nullable=False),
 516    Column("post_job_action_id", Integer, ForeignKey( "post_job_action.id" ), index=True, nullable=False))
 517
 518model.DeferredJob.table = Table( "deferred_job", metadata,
 519    Column( "id", Integer, primary_key=True ),
 520    Column( "create_time", DateTime, default=now ),
 521    Column( "update_time", DateTime, default=now, onupdate=now ),
 522    Column( "state", String( 64 ), index=True ),
 523    Column( "plugin", String( 128 ), index=True ),
 524    Column( "params", JSONType ) )
 525
 526model.TransferJob.table = Table( "transfer_job", metadata,
 527    Column( "id", Integer, primary_key=True ),
 528    Column( "create_time", DateTime, default=now ),
 529    Column( "update_time", DateTime, default=now, onupdate=now ),
 530    Column( "state", String( 64 ), index=True ),
 531    Column( "path", String( 1024 ) ),
 532    Column( "info", TEXT ),
 533    Column( "pid", Integer ),
 534    Column( "socket", Integer ),
 535    Column( "params", JSONType ) )
 536
 537model.Event.table = Table( "event", metadata,
 538    Column( "id", Integer, primary_key=True ),
 539    Column( "create_time", DateTime, default=now ),
 540    Column( "update_time", DateTime, default=now, onupdate=now ),
 541    Column( "history_id", Integer, ForeignKey( "history.id" ), index=True, nullable=True ),
 542    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True, nullable=True ),
 543    Column( "message", TrimmedString( 1024 ) ),
 544    Column( "session_id", Integer, ForeignKey( "galaxy_session.id" ), index=True, nullable=True ),
 545    Column( "tool_id", String( 255 ) ) )
 546
 547model.GalaxySession.table = Table( "galaxy_session", metadata,
 548    Column( "id", Integer, primary_key=True ),
 549    Column( "create_time", DateTime, default=now ),
 550    Column( "update_time", DateTime, default=now, onupdate=now ),
 551    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True, nullable=True ),
 552    Column( "remote_host", String( 255 ) ),
 553    Column( "remote_addr", String( 255 ) ),
 554    Column( "referer", TEXT ),
 555    Column( "current_history_id", Integer, ForeignKey( "history.id" ), nullable=True ),
 556    Column( "session_key", TrimmedString( 255 ), index=True, unique=True ), # unique 128 bit random number coerced to a string
 557    Column( "is_valid", Boolean, default=False ),
 558    Column( "prev_session_id", Integer ), # saves a reference to the previous session so we have a way to chain them together
 559    Column( "disk_usage", Numeric( 15, 0 ), index=True ) )
 560
 561model.GalaxySessionToHistoryAssociation.table = Table( "galaxy_session_to_history", metadata,
 562    Column( "id", Integer, primary_key=True ),
 563    Column( "create_time", DateTime, default=now ),
 564    Column( "session_id", Integer, ForeignKey( "galaxy_session.id" ), index=True ),
 565    Column( "history_id", Integer, ForeignKey( "history.id" ), index=True ) )
 566
 567model.StoredWorkflow.table = Table( "stored_workflow", metadata,
 568    Column( "id", Integer, primary_key=True ),
 569    Column( "create_time", DateTime, default=now ),
 570    Column( "update_time", DateTime, default=now, onupdate=now ),
 571    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True, nullable=False ),
 572    Column( "latest_workflow_id", Integer,
 573            ForeignKey( "workflow.id", use_alter=True, name='stored_workflow_latest_workflow_id_fk' ), index=True ),
 574    Column( "name", TEXT ),
 575    Column( "deleted", Boolean, default=False ),
 576    Column( "importable", Boolean, default=False ),
 577    Column( "slug", TEXT, index=True ),
 578    Column( "published", Boolean, index=True, default=False )
 579    )
 580
 581model.Workflow.table = Table( "workflow", metadata,
 582    Column( "id", Integer, primary_key=True ),
 583    Column( "create_time", DateTime, default=now ),
 584    Column( "update_time", DateTime, default=now, onupdate=now ),
 585    Column( "stored_workflow_id", Integer, ForeignKey( "stored_workflow.id" ), index=True, nullable=False ),
 586    Column( "name", TEXT ),
 587    Column( "has_cycles", Boolean ),
 588    Column( "has_errors", Boolean )
 589    )
 590
 591model.WorkflowStep.table = Table( "workflow_step", metadata,
 592    Column( "id", Integer, primary_key=True ),
 593    Column( "create_time", DateTime, default=now ),
 594    Column( "update_time", DateTime, default=now, onupdate=now ),
 595    Column( "workflow_id", Integer, ForeignKey( "workflow.id" ), index=True, nullable=False ),
 596    Column( "type", String(64) ),
 597    Column( "tool_id", TEXT ),
 598    Column( "tool_version", TEXT ), # Reserved for future
 599    Column( "tool_inputs", JSONType ),
 600    Column( "tool_errors", JSONType ),
 601    Column( "position", JSONType ),
 602    Column( "config", JSONType ),
 603    Column( "order_index", Integer ),
 604    ## Column( "input_connections", JSONType )
 605    )
 606
 607model.WorkflowStepConnection.table = Table( "workflow_step_connection", metadata,
 608    Column( "id", Integer, primary_key=True ),
 609    Column( "output_step_id", Integer, ForeignKey( "workflow_step.id" ), index=True ),
 610    Column( "input_step_id", Integer, ForeignKey( "workflow_step.id" ), index=True ),
 611    Column( "output_name", TEXT ),
 612    Column( "input_name", TEXT)
 613    )
 614
 615model.WorkflowOutput.table = Table( "workflow_output", metadata,
 616    Column( "id", Integer, primary_key=True ),
 617    Column( "workflow_step_id", Integer, ForeignKey("workflow_step.id"), index=True, nullable=False),
 618    Column( "output_name", String(255), nullable=True)
 619    )
 620
 621model.WorkflowInvocation.table = Table( "workflow_invocation", metadata,
 622    Column( "id", Integer, primary_key=True ),
 623    Column( "create_time", DateTime, default=now ),
 624    Column( "update_time", DateTime, default=now, onupdate=now ),
 625    Column( "workflow_id", Integer, ForeignKey( "workflow.id" ), index=True, nullable=False )
 626    )
 627
 628model.WorkflowInvocationStep.table = Table( "workflow_invocation_step", 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( "workflow_invocation_id", Integer, ForeignKey( "workflow_invocation.id" ), index=True, nullable=False ),
 633    Column( "workflow_step_id",  Integer, ForeignKey( "workflow_step.id" ), index=True, nullable=False ),
 634    Column( "job_id",  Integer, ForeignKey( "job.id" ), index=True, nullable=True )
 635    )
 636
 637model.StoredWorkflowUserShareAssociation.table = Table( "stored_workflow_user_share_connection", metadata,
 638    Column( "id", Integer, primary_key=True ),
 639    Column( "stored_workflow_id", Integer, ForeignKey( "stored_workflow.id" ), index=True ),
 640    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True )
 641    )
 642
 643model.StoredWorkflowMenuEntry.table = Table( "stored_workflow_menu_entry", metadata,
 644    Column( "id", Integer, primary_key=True ),
 645    Column( "stored_workflow_id", Integer, ForeignKey( "stored_workflow.id" ), index=True ),
 646    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
 647    Column( "order_index", Integer ) )
 648
 649model.MetadataFile.table = Table( "metadata_file", metadata,
 650    Column( "id", Integer, primary_key=True ),
 651    Column( "name", TEXT ),
 652    Column( "hda_id", Integer, ForeignKey( "history_dataset_association.id" ), index=True, nullable=True ),
 653    Column( "lda_id", Integer, ForeignKey( "library_dataset_dataset_association.id" ), index=True, nullable=True ),
 654    Column( "create_time", DateTime, default=now ),
 655    Column( "update_time", DateTime, index=True, default=now, onupdate=now ),
 656    Column( "object_store_id", TrimmedString( 255 ), index=True ),
 657    Column( "deleted", Boolean, index=True, default=False ),
 658    Column( "purged", Boolean, index=True, default=False ) )
 659
 660model.FormDefinitionCurrent.table = Table('form_definition_current', metadata,
 661    Column( "id", Integer, primary_key=True),
 662    Column( "create_time", DateTime, default=now ),
 663    Column( "update_time", DateTime, default=now, onupdate=now ),
 664    Column( "latest_form_id", Integer, ForeignKey( "form_definition.id" ), index=True ),
 665    Column( "deleted", Boolean, index=True, default=False ))
 666
 667model.FormDefinition.table = Table('form_definition', metadata,
 668    Column( "id", Integer, primary_key=True),
 669    Column( "create_time", DateTime, default=now ),
 670    Column( "update_time", DateTime, default=now, onupdate=now ),
 671    Column( "name", TrimmedString( 255 ), nullable=False ),
 672    Column( "desc", TEXT ),
 673    Column( "form_definition_current_id",
 674            Integer,
 675            ForeignKey( "form_definition_current.id", name='for_def_form_def_current_id_fk', use_alter=True ),
 676            index=True ),
 677    Column( "fields", JSONType() ),
 678    Column( "type", TrimmedString( 255 ), index=True ),
 679    Column( "layout", JSONType() ), )
 680
 681model.ExternalService.table = Table( 'external_service', metadata,
 682    Column( "id", Integer, primary_key=True ),
 683    Column( "create_time", DateTime, default=now ),
 684    Column( "update_time", DateTime, default=now, onupdate=now ),
 685    Column( "name", TrimmedString( 255 ), nullable=False ),
 686    Column( "description", TEXT ),
 687    Column( "external_service_type_id", TrimmedString( 255 ), nullable=False ),
 688    Column( "version", TrimmedString( 255 ) ),
 689    Column( "form_definition_id", Integer, ForeignKey( "form_definition.id" ), index=True ),
 690    Column( "form_values_id", Integer, ForeignKey( "form_values.id" ), index=True ),
 691    Column( "deleted", Boolean, index=True, default=False ) )
 692
 693model.RequestType.table = Table('request_type', metadata,
 694    Column( "id", Integer, primary_key=True),
 695    Column( "create_time", DateTime, default=now ),
 696    Column( "update_time", DateTime, default=now, onupdate=now ),
 697    Column( "name", TrimmedString( 255 ), nullable=False ),
 698    Column( "desc", TEXT ),
 699    Column( "request_form_id", Integer, ForeignKey( "form_definition.id" ), index=True ),
 700    Column( "sample_form_id", Integer, ForeignKey( "form_definition.id" ), index=True ),
 701    Column( "deleted", Boolean, index=True, default=False ) )
 702
 703model.RequestTypeExternalServiceAssociation.table = Table( "request_type_external_service_association", metadata,
 704    Column( "id", Integer, primary_key=True ),
 705    Column( "request_type_id", Integer, ForeignKey( "request_type.id" ), index=True ),
 706    Column( "external_service_id", Integer, ForeignKey( "external_service.id" ), index=True ) )
 707
 708model.RequestTypePermissions.table = Table( "request_type_permissions", metadata,
 709    Column( "id", Integer, primary_key=True ),
 710    Column( "create_time", DateTime, default=now ),
 711    Column( "update_time", DateTime, default=now, onupdate=now ),
 712    Column( "action", TEXT ),
 713    Column( "request_type_id", Integer, ForeignKey( "request_type.id" ), nullable=True, index=True ),
 714    Column( "role_id", Integer, ForeignKey( "role.id" ), index=True ) )
 715
 716model.FormValues.table = Table('form_values', metadata,
 717    Column( "id", Integer, primary_key=True),
 718    Column( "create_time", DateTime, default=now ),
 719    Column( "update_time", DateTime, default=now, onupdate=now ),
 720    Column( "form_definition_id", Integer, ForeignKey( "form_definition.id" ), index=True ),
 721    Column( "content", JSONType()) )
 722
 723model.Request.table = Table('request', metadata,
 724    Column( "id", Integer, primary_key=True),
 725    Column( "create_time", DateTime, default=now ),
 726    Column( "update_time", DateTime, default=now, onupdate=now ),
 727    Column( "name", TrimmedString( 255 ), nullable=False ),
 728    Column( "desc", TEXT ),
 729    Column( "notification", JSONType() ),
 730    Column( "form_values_id", Integer, ForeignKey( "form_values.id" ), index=True ),
 731    Column( "request_type_id", Integer, ForeignKey( "request_type.id" ), index=True ),
 732    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
 733    Column( "deleted", Boolean, index=True, default=False ) )
 734
 735model.RequestEvent.table = Table('request_event', metadata,
 736    Column( "id", Integer, primary_key=True),
 737    Column( "create_time", DateTime, default=now ),
 738    Column( "update_time", DateTime, default=now, onupdate=now ),
 739    Column( "request_id", Integer, ForeignKey( "request.id" ), index=True ),
 740    Column( "state", TrimmedString( 255 ),  index=True ),
 741    Column( "comment", TEXT ) )
 742
 743model.Sample.table = Table('sample', metadata,
 744    Column( "id", Integer, primary_key=True ),
 745    Column( "create_time", DateTime, default=now ),
 746    Column( "update_time", DateTime, default=now, onupdate=now ),
 747    Column( "name", TrimmedString( 255 ), nullable=False ),
 748    Column( "desc", TEXT ),
 749    Column( "form_values_id", Integer, ForeignKey( "form_values.id" ), index=True ),
 750    Column( "request_id", Integer, ForeignKey( "request.id" ), index=True ),
 751    Column( "bar_code", TrimmedString( 255 ), index=True ),
 752    Column( "library_id", Integer, ForeignKey( "library.id" ), index=True ),
 753    Column( "folder_id", Integer, ForeignKey( "library_folder.id" ), index=True ),
 754    Column( "deleted", Boolean, index=True, default=False ),
 755    Column( "workflow", JSONType, nullable=True ),
 756    Column( "history_id", Integer, ForeignKey( "history.id" ), nullable=True) )
 757
 758model.SampleState.table = Table('sample_state', metadata,
 759    Column( "id", Integer, primary_key=True ),
 760    Column( "create_time", DateTime, default=now ),
 761    Column( "update_time", DateTime, default=now, onupdate=now ),
 762    Column( "name", TrimmedString( 255 ), nullable=False ),
 763    Column( "desc", TEXT ),
 764    Column( "request_type_id", Integer, ForeignKey( "request_type.id" ), index=True ) )
 765
 766model.SampleEvent.table = Table('sample_event', metadata,
 767    Column( "id", Integer, primary_key=True ),
 768    Column( "create_time", DateTime, default=now ),
 769    Column( "update_time", DateTime, default=now, onupdate=now ),
 770    Column( "sample_id", Integer, ForeignKey( "sample.id" ), index=True ),
 771    Column( "sample_state_id", Integer, ForeignKey( "sample_state.id" ), index=True ),
 772    Column( "comment", TEXT ) )
 773
 774model.SampleDataset.table = Table('sample_dataset', metadata,
 775    Column( "id", Integer, primary_key=True ),
 776    Column( "create_time", DateTime, default=now ),
 777    Column( "update_time", DateTime, default=now, onupdate=now ),
 778    Column( "sample_id", Integer, ForeignKey( "sample.id" ), index=True ),
 779    Column( "name", TrimmedString( 255 ), nullable=False ),
 780    Column( "file_path", TEXT ),
 781    Column( "status", TrimmedString( 255 ), nullable=False ),
 782    Column( "error_msg", TEXT ),
 783    Column( "size", TrimmedString( 255 ) ),
 784    Column( "external_service_id", Integer, ForeignKey( "external_service.id" ), index=True ) )
 785
 786model.Run.table = Table( 'run', metadata,
 787    Column( "id", Integer, primary_key=True ),
 788    Column( "create_time", DateTime, default=now ),
 789    Column( "update_time", DateTime, default=now, onupdate=now ),
 790    Column( "form_definition_id", Integer, ForeignKey( "form_definition.id" ), index=True ),
 791    Column( "form_values_id", Integer, ForeignKey( "form_values.id" ), index=True ),
 792    Column( "deleted", Boolean, index=True, default=False ),
 793    Column( "subindex", TrimmedString( 255 ), index=True ) )
 794
 795model.RequestTypeRunAssociation.table = Table( "request_type_run_association", metadata,
 796    Column( "id", Integer, primary_key=True ),
 797    Column( "request_type_id", Integer, ForeignKey( "request_type.id" ), index=True, nullable=False ),
 798    Column( "run_id", Integer, ForeignKey( "run.id" ), index=True, nullable=False ) )
 799
 800model.SampleRunAssociation.table = Table( "sample_run_association", metadata,
 801    Column( "id", Integer, primary_key=True ),
 802    Column( "sample_id", Integer, ForeignKey( "sample.id" ), index=True, nullable=False ),
 803    Column( "run_id", Integer, ForeignKey( "run.id" ), index=True, nullable=False ) )
 804
 805model.Page.table = Table( "page", metadata,
 806    Column( "id", Integer, primary_key=True ),
 807    Column( "create_time", DateTime, default=now ),
 808    Column( "update_time", DateTime, default=now, onupdate=now ),
 809    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True, nullable=False ),
 810    Column( "latest_revision_id", Integer,
 811            ForeignKey( "page_revision.id", use_alter=True, name='page_latest_revision_id_fk' ), index=True ),
 812    Column( "title", TEXT ),
 813    Column( "slug", TEXT, unique=True, index=True ),
 814    Column( "importable", Boolean, index=True, default=False ),
 815    Column( "published", Boolean, index=True, default=False ),
 816    Column( "deleted", Boolean, index=True, default=False ),
 817    )
 818
 819model.PageRevision.table = Table( "page_revision", metadata,
 820    Column( "id", Integer, primary_key=True ),
 821    Column( "create_time", DateTime, default=now ),
 822    Column( "update_time", DateTime, default=now, onupdate=now ),
 823    Column( "page_id", Integer, ForeignKey( "page.id" ), index=True, nullable=False ),
 824    Column( "title", TEXT ),
 825    Column( "content", TEXT )
 826    )
 827
 828model.PageUserShareAssociation.table = Table( "page_user_share_association", metadata,
 829    Column( "id", Integer, primary_key=True ),
 830    Column( "page_id", Integer, ForeignKey( "page.id" ), index=True ),
 831    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True )
 832    )
 833
 834model.Visualization.table = Table( "visualization", metadata,
 835    Column( "id", Integer, primary_key=True ),
 836    Column( "create_time", DateTime, default=now ),
 837    Column( "update_time", DateTime, default=now, onupdate=now ),
 838    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True, nullable=False ),
 839    Column( "latest_revision_id", Integer,
 840            ForeignKey( "visualization_revision.id", use_alter=True, name='visualization_latest_revision_id_fk' ), index=True ),
 841    Column( "title", TEXT ),
 842    Column( "type", TEXT ),
 843    Column( "dbkey", TEXT, index=True ),
 844    Column( "deleted", Boolean, default=False, index=True ),
 845    Column( "importable", Boolean, default=False, index=True ),
 846    Column( "slug", TEXT, index=True ),
 847    Column( "published", Boolean, default=False, index=True )
 848    )
 849
 850model.VisualizationRevision.table = Table( "visualization_revision", metadata,
 851    Column( "id", Integer, primary_key=True ),
 852    Column( "create_time", DateTime, default=now ),
 853    Column( "update_time", DateTime, default=now, onupdate=now ),
 854    Column( "visualization_id", Integer, ForeignKey( "visualization.id" ), index=True, nullable=False ),
 855    Column( "title", TEXT ),
 856    Column( "dbkey", TEXT, index=True ),
 857    Column( "config", JSONType )
 858    )
 859
 860model.VisualizationUserShareAssociation.table = Table( "visualization_user_share_association", metadata,
 861    Column( "id", Integer, primary_key=True ),
 862    Column( "visualization_id", Integer, ForeignKey( "visualization.id" ), index=True ),
 863    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True )
 864    )
 865
 866#Data Manager tables
 867model.DataManagerHistoryAssociation.table = Table( "data_manager_history_association", metadata,
 868    Column( "id", Integer, primary_key=True),
 869    Column( "create_time", DateTime, default=now ),
 870    Column( "update_time", DateTime, index=True, default=now, onupdate=now ),
 871    Column( "history_id", Integer, ForeignKey( "history.id" ), index=True ),
 872    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True )
 873    )
 874
 875model.DataManagerJobAssociation.table = Table( "data_manager_job_association", metadata,
 876    Column( "id", Integer, primary_key=True),
 877    Column( "create_time", DateTime, default=now ),
 878    Column( "update_time", DateTime, index=True, default=now, onupdate=now ),
 879    Column( "job_id", Integer, ForeignKey( "job.id" ), index=True ),
 880    Column( "data_manager_id", TEXT, index=True )
 881    )
 882
 883# Tagging tables.
 884
 885model.Tag.table = Table( "tag", metadata,
 886    Column( "id", Integer, primary_key=True ),
 887    Column( "type", Integer ),
 888    Column( "parent_id", Integer, ForeignKey( "tag.id" ) ),
 889    Column( "name", TrimmedString(255) ),
 890    UniqueConstraint( "name" ) )
 891
 892model.HistoryTagAssociation.table = Table( "history_tag_association", metadata,
 893    Column( "id", Integer, primary_key=True ),
 894    Column( "history_id", Integer, ForeignKey( "history.id" ), index=True ),
 895    Column( "tag_id", Integer, ForeignKey( "tag.id" ), index=True ),
 896    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
 897    Column( "user_tname", TrimmedString(255), index=True),
 898    Column( "value", TrimmedString(255), index=True),
 899    Column( "user_value", TrimmedString(255), index=True) )
 900
 901model.DatasetTagAssociation.table = Table( "dataset_tag_association", metadata,
 902    Column( "id", Integer, primary_key=True ),
 903    Column( "dataset_id", Integer, ForeignKey( "dataset.id" ), index=True ),
 904    Column( "tag_id", Integer, ForeignKey( "tag.id" ), index=True ),
 905    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
 906    Column( "user_tname", TrimmedString(255), index=True),
 907    Column( "value", TrimmedString(255), index=True),
 908    Column( "user_value", TrimmedString(255), index=True) )
 909
 910model.HistoryDatasetAssociationTagAssociation.table = Table( "history_dataset_association_tag_association", metadata,
 911    Column( "id", Integer, primary_key=True ),
 912    Column( "history_dataset_association_id", Integer, ForeignKey( "history_dataset_association.id" ), index=True ),
 913    Column( "tag_id", Integer, ForeignKey( "tag.id" ), index=True ),
 914    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
 915    Column( "user_tname", TrimmedString(255), index=True),
 916    Column( "value", TrimmedString(255), index=True),
 917    Column( "user_value", TrimmedString(255), index=True) )
 918
 919model.StoredWorkflowTagAssociation.table = Table( "stored_workflow_tag_association", metadata,
 920    Column( "id", Integer, primary_key=True ),
 921    Column( "stored_workflow_id", Integer, ForeignKey( "stored_workflow.id" ), index=True ),
 922    Column( "tag_id", Integer, ForeignKey( "tag.id" ), index=True ),
 923    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
 924    Column( "user_tname", Unicode(255), index=True),
 925    Column( "value", Unicode(255), index=True),
 926    Column( "user_value", Unicode(255), index=True) )
 927
 928model.PageTagAssociation.table = Table( "page_tag_association", metadata,
 929    Column( "id", Integer, primary_key=True ),
 930    Column( "page_id", Integer, ForeignKey( "page.id" ), index=True ),
 931    Column( "tag_id", Integer, ForeignKey( "tag.id" ), index=True ),
 932    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
 933    Column( "user_tname", TrimmedString(255), index=True),
 934    Column( "value", TrimmedString(255), index=True),
 935    Column( "user_value", TrimmedString(255), index=True) )
 936
 937model.WorkflowStepTagAssociation.table = Table( "workflow_step_tag_association", metadata,
 938    Column( "id", Integer, primary_key=True ),
 939    Column( "workflow_step_id", Integer, ForeignKey( "workflow_step.id" ), index=True ),
 940    Column( "tag_id", Integer, ForeignKey( "tag.id" ), index=True ),
 941    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
 942    Column( "user_tname", Unicode(255), index=True),
 943    Column( "value", Unicode(255), index=True),
 944    Column( "user_value", Unicode(255), index=True) )
 945
 946model.VisualizationTagAssociation.table = Table( "visualization_tag_association", metadata,
 947    Column( "id", Integer, primary_key=True ),
 948    Column( "visualization_id", Integer, ForeignKey( "visualization.id" ), index=True ),
 949    Column( "tag_id", Integer, ForeignKey( "tag.id" ), index=True ),
 950    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
 951    Column( "user_tname", TrimmedString(255), index=True),
 952    Column( "value", TrimmedString(255), index=True),
 953    Column( "user_value", TrimmedString(255), index=True) )
 954
 955model.ToolTagAssociation.table = Table( "tool_tag_association", metadata,
 956    Column( "id", Integer, primary_key=True ),
 957    Column( "tool_id", TrimmedString(255), index=True ),
 958    Column( "tag_id", Integer, ForeignKey( "tag.id" ), index=True ),
 959    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
 960    Column( "user_tname", TrimmedString(255), index=True),
 961    Column( "value", TrimmedString(255), index=True),
 962    Column( "user_value", TrimmedString(255), index=True) )
 963
 964# Annotation tables.
 965
 966model.HistoryAnnotationAssociation.table = Table( "history_annotation_association", metadata,
 967    Column( "id", Integer, primary_key=True ),
 968    Column( "history_id", Integer, ForeignKey( "history.id" ), index=True ),
 969    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
 970    Column( "annotation", TEXT, index=True) )
 971
 972model.HistoryDatasetAssociationAnnotationAssociation.table = Table( "history_dataset_association_annotation_association", metadata,
 973    Column( "id", Integer, primary_key=True ),
 974    Column( "history_dataset_association_id", Integer, ForeignKey( "history_dataset_association.id" ), index=True ),
 975    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
 976    Column( "annotation", TEXT, index=True) )
 977
 978model.StoredWorkflowAnnotationAssociation.table = Table( "stored_workflow_annotation_association", metadata,
 979    Column( "id", Integer, primary_key=True ),
 980    Column( "stored_workflow_id", Integer, ForeignKey( "stored_workflow.id" ), index=True ),
 981    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
 982    Column( "annotation", TEXT, index=True) )
 983
 984model.WorkflowStepAnnotationAssociation.table = Table( "workflow_step_annotation_association", metadata,
 985    Column( "id", Integer, primary_key=True ),
 986    Column( "workflow_step_id", Integer, ForeignKey( "workflow_step.id" ), index=True ),
 987    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
 988    Column( "annotation", TEXT, index=True) )
 989
 990model.PageAnnotationAssociation.table = Table( "page_annotation_association", metadata,
 991    Column( "id", Integer, primary_key=True ),
 992    Column( "page_id", Integer, ForeignKey( "page.id" ), index=True ),
 993    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
 994    Column( "annotation", TEXT, index=True) )
 995
 996model.VisualizationAnnotationAssociation.table = Table( "visualization_annotation_association", metadata,
 997    Column( "id", Integer, primary_key=True ),
 998    Column( "visualization_id", Integer, ForeignKey( "visualization.id" ), index=True ),
 999    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
1000    Column( "annotation", TEXT, index=True) )
1001
1002# Ratings tables.
1003model.HistoryRatingAssociation.table = Table( "history_rating_association", metadata,
1004    Column( "id", Integer, primary_key=True ),
1005    Column( "history_id", Integer, ForeignKey( "history.id" ), index=True ),
1006    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
1007    Column( "rating", Integer, index=True) )
1008
1009model.HistoryDatasetAssociationRatingAssociation.table = Table( "history_dataset_association_rating_association", metadata,
1010    Column( "id", Integer, primary_key=True ),
1011    Column( "history_dataset_association_id", Integer, ForeignKey( "history_dataset_association.id" ), index=True ),
1012    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
1013    Column( "rating", Integer, index=True) )
1014
1015model.StoredWorkflowRatingAssociation.table = Table( "stored_workflow_rating_association", metadata,
1016    Column( "id", Integer, primary_key=True ),
1017    Column( "stored_workflow_id", Integer, ForeignKey( "stored_workflow.id" ), index=True ),
1018    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
1019    Column( "rating", Integer, index=True) )
1020
1021model.PageRatingAssociation.table = Table( "page_rating_association", metadata,
1022    Column( "id", Integer, primary_key=True ),
1023    Column( "page_id", Integer, ForeignKey( "page.id" ), index=True ),
1024    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
1025    Column( "rating", Integer, index=True) )
1026
1027model.VisualizationRatingAssociation.table = Table( "visualization_rating_association", metadata,
1028    Column( "id", Integer, primary_key=True ),
1029    Column( "visualization_id", Integer, ForeignKey( "visualization.id" ), index=True ),
1030    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
1031    Column( "rating", Integer, index=True) )
1032
1033# User tables.
1034
1035model.UserPreference.table = Table( "user_preference", metadata,
1036    Column( "id", Integer, primary_key=True ),
1037    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
1038    Column( "name", Unicode( 255 ), index=True),
1039    Column( "value", Unicode( 1024 ) ) )
1040
1041model.UserAction.table = Table( "user_action", metadata,
1042    Column( "id", Integer, primary_key=True ),
1043    Column( "create_time", DateTime, default=now ),
1044    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
1045    Column( "session_id", Integer, ForeignKey( "galaxy_session.id" ), index=True ),
1046    Column( "action", Unicode( 255 ) ),
1047    Column( "context", Unicode( 512 ) ),
1048    Column( "params", Unicode( 1024 ) ) )
1049
1050model.APIKeys.table = Table( "api_keys", metadata,
1051    Column( "id", Integer, primary_key=True ),
1052    Column( "create_time", DateTime, default=now ),
1053    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
1054    Column( "key", TrimmedString( 32 ), index=True, unique=True ) )
1055
1056
1057# With the tables defined we can define the mappers and setup the
1058# relationships between the model objects.
1059def simple_mapping( model, **kwds ):
1060    mapper( model, model.table, properties=kwds )
1061
1062
1063mapper( model.Sample, model.Sample.table,
1064               properties=dict(
1065                    events=relation( model.SampleEvent, backref="sample",
1066                        order_by=desc( model.SampleEvent.table.c.update_time ) ),
1067                    datasets=relation( model.SampleDataset, backref="sample",
1068                        order_by=desc( model.SampleDataset.table.c.update_time ) ),
1069                    values=relation( model.FormValues,
1070                        primaryjoin=( model.Sample.table.c.form_values_id == model.FormValues.table.c.id ) ),
1071                    request=relation( model.Request,
1072                        primaryjoin=( model.Sample.table.c.request_id == model.Request.table.c.id ) ),
1073                    folder=relation( model.LibraryFolder,
1074                        primaryjoin=( model.Sample.table.c.folder_id == model.LibraryFolder.table.c.id ) ),
1075                    library=relation( model.Library,
1076                        primaryjoin=( model.Sample.table.c.library_id == model.Library.table.c.id ) ),
1077                    history=relation( model.History,
1078                        primaryjoin=( model.Sample.table.c.history_id == model.History.table.c.id ) ),
1079            ) )
1080
1081
1082mapper( model.FormValues, model.FormValues.table,
1083               properties=dict( form_definition=relation( model.FormDefinition,
1084                                                          primaryjoin=( model.FormValues.table.c.form_definition_id ==
1085                                                              model.FormDefinition.table.c.id ) )
1086             )
1087)
1088
1089mapper( model.Request, model.Request.table,
1090               properties=dict( values=relation( model.FormValues,
1091                                                 primaryjoin=( model.Request.table.c.form_values_id == model.FormValues.table.c.id ) ),
1092                                type=relation( model.RequestType,
1093                                               primaryjoin=( model.Request.table.c.request_type_id == model.RequestType.table.c.id ) ),
1094                                user=relation( model.User,
1095                                               primaryjoin=( model.Request.table.c.user_id == model.User.table.c.id ),
1096                                               backref="requests" ),
1097                                samples=relation( model.Sample,
1098                                                  primaryjoin=( model.Request.table.c.id == model.Sample.table.c.request_id ),
1099                                                  order_by=asc( model.Sample.table.c.id ) ),
1100                                events=relation( model.RequestEvent, backref="request",
1101                                                 order_by=desc( model.RequestEvent.table.c.update_time ) )
1102                              ) )
1103
1104mapper( model.RequestEvent, model.RequestEvent.table,
1105               properties=None )
1106
1107mapper( model.ExternalService, model.ExternalService.table,
1108               properties=dict( form_definition=relation( model.FormDefinition,
1109                                                       primaryjoin=( model.ExternalService.table.c.form_definition_id ==
1110                                                           model.FormDefinition.table.c.id ) ),
1111                                form_values=relation( model.FormValues,
1112                                                      primaryjoin=( model.ExternalService.table.c.form_values_id ==
1113                                                          model.FormValues.table.c.id ) )
1114                                ) )
1115
1116mapper( model.RequestType, model.RequestType.table,
1117               properties=dict( states=relation( model.SampleState,
1118                                                 backref="request_type",
1119                                                 primaryjoin=( model.RequestType.table.c.id == model.SampleState.table.c.request_type_id ),
1120                                                 order_by=asc( model.SampleState.table.c.update_time ) ),
1121                                request_form=relation( model.FormDefinition,
1122                                                       primaryjoin=( model.RequestType.table.c.request_form_id == model.FormDefinition.table.c.id ) ),
1123                                sample_form=relation( model.FormDefinition,
1124                                                      primaryjoin=( model.RequestType.table.c.sample_form_id == model.FormDefinition.table.c.id ) ),
1125                              ) )
1126
1127mapper( model.RequestTypeExternalServiceAssociation, model.RequestTypeExternalServiceAssociation.table,
1128    properties=dict(
1129        request_type=relation( model.RequestType,
1130                               primaryjoin=( ( model.RequestTypeExternalServiceAssociation.table.c.request_type_id == model.RequestType.table.c.id ) ), backref="external_service_associations" ),
1131        external_service=relation( model.ExternalService,
1132                                   primaryjoin=( model.RequestTypeExternalServiceAssociation.table.c.external_service_id
1133                                       == model.ExternalService.table.c.id ) )
1134    )
1135)
1136
1137
1138mapper( model.RequestTypePermissions, model.RequestTypePermissions.table,
1139    properties=dict(
1140        request_type=relation( model.RequestType, backref="actions" ),
1141        role=relation( model.Role, backref="request_type_actions" )
1142    )
1143)
1144
1145mapper( model.FormDefinition, model.FormDefinition.table,
1146               properties=dict( current=relation( model.FormDefinitionCurrent,
1147                                                  primaryjoin=( model.FormDefinition.table.c.form_definition_current_id == model.FormDefinitionCurrent.table.c.id ) )
1148                              ) )
1149mapper( model.FormDefinitionCurrent, model.FormDefinitionCurrent.table,
1150                properties=dict( forms=relation( model.FormDefinition, backref='form_definition_current',
1151                                                 cascade="all, delete-orphan",
1152                                                 primaryjoin=( model.FormDefinitionCurrent.table.c.id == model.FormDefinition.table.c.form_definition_current_id ) ),
1153                                 latest_form=relation( model.FormDefinition, post_update=True,
1154                                                       primaryjoin=( model.FormDefinitionCurrent.table.c.latest_form_id == model.FormDefinition.table.c.id ) )
1155                               ) )
1156
1157mapper( model.SampleEvent, model.SampleEvent.table,
1158               properties=dict( state=relation( model.SampleState,
1159                                                primaryjoin=( model.SampleEvent.table.c.sample_state_id == model.SampleState.table.c.id ) ),
1160
1161                                ) )
1162
1163mapper( model.SampleState, model.SampleState.table,
1164               properties=None )
1165
1166mapper( model.SampleDataset, model.SampleDataset.table,
1167               properties=dict( external_service=relation( model.ExternalService,
1168                                                           primaryjoin=( model.SampleDataset.table.c.external_service_id == model.ExternalService.table.c.id ) )
1169    )
1170)
1171
1172
1173mapper( model.SampleRunAssociation, model.SampleRunAssociation.table,
1174               properties=dict( sample=relation( model.Sample, backref="runs", order_by=desc( model.Run.table.c.update_time ) ),
1175                                run=relation( model.Run, backref="samples", order_by=asc( model.Sample.table.c.id ) ) ) )
1176
1177mapper( model.RequestTypeRunAssociation, model.RequestTypeRunAssociation.table,
1178               properties=dict( request_type=relation( model.RequestType, backref="run" ),
1179                                run=relation( model.Run, backref="request_type" ) ) )
1180
1181mapper( model.Run, model.Run.table,
1182                properties=dict( template=relation( model.FormDefinition,
1183                                                    primaryjoin=( model.Run.table.c.form_definition_id == model.FormDefinition.table.c.id ) ),
1184                                 info=relation( model.FormValues,
1185                                                primaryjoin=( model.Run.table.c.form_values_id == model.FormValues.table.c.id ) ) ) )
1186
1187mapper( model.UserAddress, model.UserAddress.table,
1188               properties=dict(
1189                    user=relation( model.User,
1190                                   primaryjoin=( model.UserAddress.table.c.user_id == model.User.table.c.id ),
1191                                   backref='addresses',
1192                                   order_by=desc(model.UserAddress.table.c.update_time)),
1193                ) )
1194
1195mapper( model.UserOpenID, model.UserOpenID.table,
1196    properties=dict(
1197        session=relation( model.GalaxySession,
1198                          primaryjoin=( model.UserOpenID.table.c.session_id == model.GalaxySession.table.c.id ),
1199                          backref='openids',
1200                          order_by=desc( model.UserOpenID.table.c.update_time ) ),
1201        user=relation( model.User,
1202                       primaryjoin=( model.UserOpenID.table.c.user_id == model.User.table.c.id ),
1203                       backref='openids',
1204                       order_by=desc( model.UserOpenID.table.c.update_time ) ) ) )
1205
1206
1207mapper( model.ValidationError, model.ValidationError.table )
1208
1209simple_mapping( model.HistoryDatasetAssociation,
1210    dataset=relation(
1211        model.Dataset,
1212        primaryjoin=( model.Dataset.table.c.id == model.HistoryDatasetAssociation.table.c.dataset_id ), lazy=False ),
1213    # .history defined in History mapper
1214    copied_from_history_dataset_association=relation(
1215        model.HistoryDatasetAssociation,
1216        primaryjoin=( model.HistoryDatasetAssociation.table.c.copied_from_history_dataset_association_id == model.HistoryDatasetAssociation.table.c.id ),
1217        remote_side=[model.HistoryDatasetAssociation.table.c.id],
1218        uselist=False ),
1219    copied_to_history_dataset_associations=relation(
1220        model.HistoryDatasetAssociation,
1221        primaryjoin=( model.HistoryDatasetAssociation.table.c.copied_from_history_dataset_association_id == model.HistoryDatasetAssociation.table.c.id ) ),
1222    copied_from_library_dataset_dataset_association=relation(
1223        model.LibraryDatasetDatasetAssociation,
1224        primaryjoin=( model.HistoryDatasetAssociation.table.c.copied_from_library_dataset_dataset_association_id == model.LibraryDatasetDatasetAssociation.table.c.id ),
1225        uselist=False ),
1226    copied_to_library_dataset_dataset_associations=relation(
1227        model.LibraryDatasetDatasetAssociation,
1228        primaryjoin=( model.HistoryDatasetAssociation.table.c.copied_from_library_dataset_dataset_association_id == model.LibraryDatasetDatasetAssociation.table.c.id ) ),
1229    implicitly_converted_datasets=relation(
1230        model.ImplicitlyConvertedDatasetAssociation,
1231        primaryjoin=( model.ImplicitlyConvertedDatasetAssociation.table.c.hda_parent_id == model.HistoryDatasetAssociation.table.c.id ) ),
1232    implicitly_converted_parent_datasets=relation(
1233        model.ImplicitlyConvertedDatasetAssociation,
1234        primaryjoin=( model.ImplicitlyConvertedDatasetAssociation.table.c.hda_id == model.HistoryDatasetAssociation.table.c.id ) ),
1235    children=relation(
1236        model.HistoryDatasetAssociation,
1237        primaryjoin=( model.HistoryDatasetAssociation.table.c.parent_id == model.HistoryDatasetAssociation.table.c.id ),
1238        backref=backref( "parent", primaryjoin=( model.HistoryDatasetAssociation.table.c.parent_id == model.HistoryDatasetAssociation.table.c.id ), remote_side=[model.HistoryDatasetAssociation.table.c.id], uselist=False ) ),
1239    visible_children=relation(
1240        model.HistoryDatasetAssociation,
1241        primaryjoin=( ( model.HistoryDatasetAssociation.table.c.parent_id == model.HistoryDatasetAssociation.table.c.id ) & ( model.HistoryDatasetAssociation.table.c.visible == True ) ) ),
1242    tags=relation( model.HistoryDatasetAssociationTagAssociation, order_by=model.HistoryDatasetAssociationTagAssociation.table.c.id, backref='history_tag_associations' ),
1243    annotations=relation( model.HistoryDatasetAssociationAnnotationAssociation, order_by=model.HistoryDatasetAssociationAnnotationAssociation.table.c.id, backref="hdas" ),
1244    ratings=relation( model.HistoryDatasetAssociationRatingAssociation, order_by=model.HistoryDatasetAssociationRatingAssociation.table.c.id, backref="hdas" ),
1245    extended_metadata=relation(
1246            model.ExtendedMetadata,
1247            primaryjoin=( ( model.HistoryDatasetAssociation.table.c.extended_metadata_id == model.ExtendedMetadata.table.c.id ) )
1248        )
1249)
1250
1251simple_mapping( model.Dataset,
1252    history_associations=relation(
1253        model.HistoryDatasetAssociation,
1254        primaryjoin=( model.Dataset.table.c.id == model.HistoryDatasetAssociation.table.c.dataset_id ) ),
1255    active_history_associations=relation(
1256        model.HistoryDatasetAssociation,
1257        primaryjoin=( ( model.Dataset.table.c.id == model.HistoryDatasetAssociation.table.c.dataset_id ) & ( model.HistoryDatasetAssociation.table.c.deleted == False ) & ( model.HistoryDatasetAssociation.table.c.purged == False ) ) ),
1258    purged_history_associations=relation(
1259        model.HistoryDatasetAssociation,
1260        primaryjoin=( ( model.Dataset.table.c.id == model.HistoryDatasetAssociation.table.c.dataset_id ) & ( model.HistoryDatasetAssociation.table.c.purged == True ) ) ),
1261    library_associations=relation(
1262        model.LibraryDatasetDatasetAssociation,
1263        primaryjoin=( model.Dataset.table.c.id == model.LibraryDatasetDatasetAssociation.table.c.dataset_id ) ),
1264    active_library_associations=relation(
1265        model.LibraryDatasetDatasetAssociation,
1266        primaryjoin=( ( model.Dataset.table.c.id == model.LibraryDatasetDatasetAssociation.table.c.dataset_id ) & ( model.LibraryDatasetDatasetAssociation.table.c.deleted == False ) ) ),
1267    tags=relation(model.DatasetTagAssociation, order_by=model.DatasetTagAssociation.table.c.id, backref='datasets')
1268)
1269
1270mapper( model.HistoryDatasetAssociationDisplayAtAuthorization, model.HistoryDatasetAssociationDisplayAtAuthorization.table,
1271    properties=dict( history_dataset_association = relation( model.HistoryDatasetAssociation ),
1272                     user = relation( model.User ) ) )
1273
1274mapper( model.HistoryDatasetAssociationSubset, model.HistoryDatasetAssociationSubset.table,
1275    properties=dict( hda = relation( model.HistoryDatasetAssociation,
1276                        primaryjoin=( model.HistoryDatasetAssociationSubset.table.c.history_dataset_association_id == model.HistoryDatasetAssociation.table.c.id ) ),
1277                     subset = relation( model.HistoryDatasetAssociation,
1278                        primaryjoin=( model.HistoryDatasetAssociationSubset.table.c.history_dataset_association_subset_id == model.HistoryDatasetAssociation.table.c.id ) )
1279                    ) )
1280
1281mapper( model.ImplicitlyConvertedDatasetAssociation, model.ImplicitlyConvertedDatasetAssociation.table,
1282    properties=dict( parent_hda=relation(
1283                        model.HistoryDatasetAssociation,
1284                        primaryjoin=( model.ImplicitlyConvertedDatasetAssociation.table.c.hda_parent_id == model.HistoryDatasetAssociation.table.c.id ) ),
1285                     parent_ldda=relation(
1286                        model.LibraryDatasetDatasetAssociation,
1287                        primaryjoin=( model.ImplicitlyConvertedDatasetAssociation.table.c.ldda_parent_id == model.LibraryDatasetDatasetAssociation.table.c.id ) ),
1288                     dataset_ldda=relation(
1289                        model.LibraryDatasetDatasetAssociation,
1290                        primaryjoin=( model.ImplicitlyConvertedDatasetAssociation.table.c.ldda_id == model.LibraryDatasetDatasetAssociation.table.c.id ) ),
1291                     dataset=relation(
1292                        model.HistoryDatasetAssociation,
1293                        primaryjoin=( model.ImplicitlyConvertedDatasetAssociation.table.c.hda_id == model.HistoryDatasetAssociation.table.c.id ) ) ) )
1294
1295mapper( model.History, model.History.table,
1296    properties=dict( galaxy_sessions=relation( model.GalaxySessionToHistoryAssociation ),
1297                     datasets=relation( model.HistoryDatasetAssociation, backref="history", order_by=asc(model.HistoryDatasetAssociation.table.c.hid) ),
1298                     exports=relation( model.JobExportHistoryArchive, primaryjoin=( model.JobExportHistoryArchive.table.c.history_id == model.History.table.c.id ), order_by=desc( model.JobExportHistoryArchive.table.c.id ) ),
1299                     active_datasets=relation( model.HistoryDatasetAssociation, primaryjoin=( ( model.HistoryDatasetAssociation.table.c.history_id == model.History.table.c.id ) & not_( model.HistoryDatasetAssociation.table.c.deleted ) ), order_by=asc( model.HistoryDatasetAssociation.table.c.hid ), viewonly=True ),
1300                     visible_datasets=relation( model.HistoryDatasetAssociation, primaryjoin=( ( model.HistoryDatasetAssociation.table.c.history_id == model.History.table.c.id ) & not_( model.HistoryDatasetAssociation.table.c.deleted ) & model.HistoryDatasetAssociation.table.c.visible ),
1301                     order_by=asc( model.HistoryDatasetAssociation.table.c.hid ), viewonly=True ),
1302                     tags=relation( model.HistoryTagAssociation, order_by=model.HistoryTagAssociation.table.c.id, backref="histories" ),
1303                     annotations=relation( model.HistoryAnnotationAssociation, order_by=model.HistoryAnnotationAssociation.table.c.id, backref="histories" ),
1304                     ratings=relation( model.HistoryRatingAssociation, order_by=model.HistoryRatingAssociation.table.c.id, backref="histories" ) )
1305                      )
1306
1307# Set up proxy so that
1308#   History.users_shared_with
1309# returns a list of users that history is shared with.
1310model.History.users_shared_with_dot_users = association_proxy( 'users_shared_with', 'user' )
1311
1312mapper( model.HistoryUserShareAssociation, model.HistoryUserShareAssociation.table,
1313    properties=dict( user=relation( model.User, backref='histories_shared_by_others' ),
1314                     history=relation( model.History, backref='users_shared_with' )
1315                   ) )
1316
1317mapper( model.User, model.User.table,
1318    properties=dict( histories=relation( model.History, backref="user",
1319                                         order_by=desc(model.History.table.c.update_time) ),
1320                     active_histories=relation( model.History, primaryjoin=( ( model.History.table.c.user_id == model.User.table.c.id ) & ( not_( model.History.table.c.deleted ) ) ), order_by=desc( model.History.table.c.update_time ) ),
1321                     galaxy_sessions=relation( model.GalaxySession, order_by=desc( model.GalaxySession.table.c.update_time ) ),
1322                     stored_workflow_menu_entries=relation( model.StoredWorkflowMenuEntry, backref="user",
1323                                                            cascade="all, delete-orphan",
1324                                                            collection_class=ordering_list( 'order_index' ) ),
1325                     _preferences=relation( model.UserPreference, backref="user", collection_class=attribute_mapped_collection('name')),
1326#                     addresses=relation( UserAddress,
1327#                                         primaryjoin=( User.table.c.id == UserAddress.table.c.user_id ) )
1328                     values=relation( model.FormValues,
1329                                      primaryjoin=( model.User.table.c.form_values_id == model.FormValues.table.c.id ) ),
1330                     api_keys=relation( model.APIKeys, backref="user", order_by=desc( model.APIKeys.table.c.create_time ) ),
1331                     ) )
1332
1333# Set up proxy so that this syntax is possible:
1334# <user_obj>.preferences[pref_name] = pref_value
1335model.User.preferences = association_proxy('_preferences', 'value', creator=model.UserPreference)
1336
1337mapper( model.Group, model.Group.table,
1338    properties=dict( users=relation( model.UserGroupAssociation ) ) )
1339
1340mapper( model.UserGroupAssociation, model.UserGroupAssociation.table,
1341    properties=dict( user=relation( model.User, backref = "groups" ),
1342                     group=relation( model.Group, backref = "members" ) ) )
1343
1344mapper( model.DefaultUserPermissions, model.DefaultUserPermissions.table,
1345    properties=dict( user=relation( model.User, backref = "default_permissions" ),
1346                     role=relation( model.Role ) ) )
1347
1348mapper( model.DefaultHistoryPermissions, model.DefaultHistoryPermissions.table,
1349    properties=dict( history=relation( model.History, backref = "default_permissions" ),
1350                     role=relation( model.Role ) ) )
1351
1352mapper( model.Role, model.Role.table,
1353    properties=dict(
1354        users=relation( model.UserRoleAssociation ),
1355        groups=relation( model.GroupRoleAssociation )
1356    )
1357)
1358
1359mapper( model.UserRoleAssociation, model.UserRoleAssociation.table,
1360    properties=dict(
1361        user=relation( model.User, backref="roles" ),
1362        non_private_roles=relation( model.User,
1363                                    backref="non_private_roles",
1364                                    primaryjoin=( ( model.User.table.c.id == model.UserRoleAssociation.table.c.user_id ) & ( model.UserRoleAssociation.table.c.role_id == model.Role.table.c.id ) & not_( model.Role.table.c.name == model.User.table.c.email ) ) ),
1365        role=relation( model.Role )
1366    )
1367)
1368
1369mapper( model.GroupRoleAssociation, model.GroupRoleAssociation.table,
1370    properties=dict(
1371        group=relation( model.Group, backref="roles" ),
1372        role=relation( model.Role )
1373    )
1374)
1375
1376mapper( model.Quota, model.Quota.table,
1377    properties=dict( users=relation( model.UserQuotaAssociation ),
1378                     groups=relation( model.GroupQuotaAssociation ) ) )
1379
1380mapper( model.UserQuotaAssociation, model.UserQuotaAssociation.table,
1381    properties=dict( user=relation( model.User, backref="quotas" ),
1382                     quota=relation( model.Quota ) ) )
1383
1384mapper( model.GroupQuotaAssociation, model.GroupQuotaAssociation.table,
1385    properties=dict( group=relation( model.Group, backref="quotas" ),
1386                     quota=relation( model.Quota ) ) )
1387
1388mapper( model.DefaultQuotaAssociation, model.DefaultQuotaAssociation.table,
1389    properties=dict( quota=relation( model.Quota, backref="default" ) ) )
1390
1391mapper( model.DatasetPermissions, model.DatasetPermissions.table,
1392    properties=dict(
1393        dataset=relation( model.Dataset, backref="actions" ),
1394        role=relation( model.Role, backref="dataset_actions" )
1395    )
1396)
1397
1398mapper( model.LibraryPermissions, model.LibraryPermissions.table,
1399    properties=dict(
1400        library=relation( model.Library, backref="actions" ),
1401        role=relation( model.Role, backref="library_actions" )
1402    )
1403)
1404
1405mapper( model.LibraryFolderPermissions, model.LibraryFolderPermissions.table,
1406    properties=dict(
1407        folder=relation( model.LibraryFolder, backref="actions" ),
1408        role=relation( model.Role, backref="library_folder_actions" )
1409    )
1410)
1411
1412mapper( model.LibraryDatasetPermissions, model.LibraryDatasetPermissions.table,
1413    properties=dict(
1414        library_dataset=relation( model.LibraryDataset, backref="actions" ),
1415        role=relation( model.Role, backref="library_dataset_actions" )
1416    )
1417)
1418
1419mapper( model.LibraryDatasetDatasetAssociationPermissions, model.LibraryDatasetDatasetAssociationPermissions.table,
1420    properties=dict(
1421        library_dataset_dataset_association = relation( model.LibraryDatasetDatasetAssociation, backref="actions" ),
1422        role=relation( model.Role, backref="library_dataset_dataset_actions" )
1423    )
1424)
1425
1426mapper( model.Library, model.Library.table,
1427    properties=dict(
1428        root_folder=relation( model.LibraryFolder, backref=backref( "library_root" ) )
1429    )
1430)
1431
1432mapper( model.ExtendedMetadata, model.ExtendedMetadata.table,
1433    properties=dict(
1434        children=relation(
1435            model.ExtendedMetadataIndex,
1436            primaryjoin=( model.ExtendedMetadataIndex.table.c.extended_metadata_id == model.ExtendedMetadata.table.c.id ),
1437            backref=backref("parent",
1438                primaryjoin=( model.ExtendedMetadataIndex.table.c.extended_metadata_id == model.ExtendedMetadata.table.c.id )
1439            )
1440        )
1441    )
1442)
1443
1444mapper( model.ExtendedMetadataIndex, model.ExtendedMetadataIndex.table,
1445    properties=dict(
1446        extended_metadata=relation(
1447            model.ExtendedMetadata,
1448            primaryjoin=( ( model.ExtendedMetadataIndex.table.c.extended_metadata_id == model.ExtendedMetadata.table.c.id ) )
1449        )
1450    )
1451)
1452
1453
1454mapper( model.LibraryInfoAssociation, model.LibraryInfoAssociation.table,
1455               properties=dict( library=relation( model.Library,
1456                                                  primaryjoin=( ( model.LibraryInfoAssociation.table.c.library_id == model.Library.table.c.id ) & ( not_( model.LibraryInfoAssociation.table.c.deleted ) ) ), backref="info_association" ),
1457                                template=relation( model.FormDefinition,
1458                                                   primaryjoin=( model.LibraryInfoAssociation.table.c.form_definition_id == model.FormDefinition.table.c.id ) ),
1459                                info=relation( model.FormValues,
1460                                               primaryjoin=( model.LibraryInfoAssociation.table.c.form_values_id == model.FormValues.table.c.id ) )
1461                              ) )
1462
1463mapper( model.LibraryFolder, model.LibraryFolder.table,
1464    properties=dict(
1465        folders=relation(
1466            model.LibraryFolder,
1467            primaryjoin=( model.LibraryFolder.table.c.parent_id == model.LibraryFolder.table.c.id ),
1468            order_by=asc( model.LibraryFolder.table.c.name ),
1469            backref=backref( "parent", primaryjoin=( model.LibraryFolder.table.c.parent_id == model.LibraryFolder.table.c.id ), remote_side=[model.LibraryFolder.table.c.id] ) ),
1470        active_folders=relation( model.LibraryFolder,
1471            primaryjoin=( ( model.LibraryFolder.table.c.parent_id == model.LibraryFolder.table.c.id ) & ( not_( model.LibraryFolder.table.c.deleted ) ) ),
1472            order_by=asc( model.LibraryFolder.table.c.name ),
1473            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."""
1474            viewonly=True ),
1475        datasets=relation( model.LibraryDataset,
1476            primaryjoin=( ( model.LibraryDataset.table.c.folder_id == model.LibraryFolder.table.c.id ) ),
1477            order_by=asc( model.LibraryDataset.table.c._name ),
1478            lazy=True,
1479            viewonly=True ),
1480        active_datasets=relation( model.LibraryDataset,
1481            primaryjoin=( ( model.LibraryDataset.table.c.folder_id == model.LibraryFolder.table.c.id ) & ( not_( model.LibraryDataset.table.c.deleted ) ) ),
1482            order_by=asc( model.LibraryDataset.table.c._name ),
1483            lazy=True,
1484            viewonly=True )
1485    ) )
1486
1487mapper( model.LibraryFolderInfoAssociation, model.LibraryFolderInfoAssociation.table,
1488               properties=dict( folder=relation( model.LibraryFolder,
1489                                                 primaryjoin=( ( model.LibraryFolderInfoAssociation.table.c.library_folder_id == model.LibraryFolder.table.c.id ) & ( not_( model.LibraryFolderInfoAssociation.table.c.deleted ) ) ), backref="info_association" ),
1490                                template=relation( model.FormDefinition,
1491                                                   primaryjoin=( model.LibraryFolderInfoAssociation.table.c.form_definition_id == model.FormDefinition.table.c.id ) ),
1492                                info=relation( model.FormValues,
1493                                               primaryjoin=( model.LibraryFolderInfoAssociation.table.c.form_values_id == model.FormValues.table.c.id ) )
1494                              ) )
1495
1496mapper( model.LibraryDataset, model.LibraryDataset.table,
1497    properties=dict(
1498        folder=relation( model.LibraryFolder ),
1499        library_dataset_dataset_association=relation( model.LibraryDatasetDatasetAssociation, primaryjoin=( model.LibraryDataset.table.c.library_dataset_dataset_association_id == model.LibraryDatasetDatasetAssociation.table.c.id ) ),
1500        expired_datasets = relation( model.LibraryDatasetDatasetAssociation, foreign_keys=[model.LibraryDataset.table.c.id,model.LibraryDataset.table.c.library_dataset_dataset_association_id ], primaryjoin=( ( model.LibraryDataset.table.c.id == model.LibraryDatasetDatasetAssociation.table.c.library_dataset_id ) & ( not_( model.LibraryDataset.table.c.library_dataset_dataset_association_id == model.LibraryDatasetDatasetAssociation.table.c.id ) ) ), viewonly=True, uselist=True )
1501        ) )
1502
1503mapper( model.LibraryDatasetDatasetAssociation, model.LibraryDatasetDatasetAssociation.table,
1504    properties=dict(
1505        dataset=relation( model.Dataset ),
1506        library_dataset = relation( model.LibraryDataset,
1507        primaryjoin=( model.LibraryDatasetDatasetAssociation.table.c.library_dataset_id == model.LibraryDataset.table.c.id ) ),
1508        #user=relation( model.User.mapper ),
1509        user=relation( model.User ),
1510        copied_from_library_dataset_dataset_association=relation(
1511            model.LibraryDatasetDatasetAssociation,
1512            primaryjoin=( model.LibraryDatasetDatasetAssociation.table.c.copied_from_library_dataset_dataset_association_id == model.LibraryDatasetDatasetAssociation.table.c.id ),
1513            remote_side=[model.LibraryDatasetDatasetAssociation.table.c.id],
1514            uselist=False ),
1515        copied_to_library_dataset_dataset_associations=relation(
1516            model.LibraryDatasetDatasetAssociation,
1517            primaryjoin=( model.LibraryDatasetDatasetAssociation.table.c.copied_from_library_dataset_dataset_association_id == model.LibraryDatasetDatasetAssociation.table.c.id ) ),
1518        copied_from_history_dataset_association=relation(
1519            model.HistoryDatasetAssociation,
1520            primaryjoin=( model.LibraryDatasetDatasetAssociation.table.c.copied_from_history_dataset_association_id == model.HistoryDatasetAssociation.table.c.id  ),
1521            uselist=False ),
1522        copied_to_history_dataset_associations=relation(
1523            model.HistoryDatasetAssociation,
1524            primaryjoin=( model.HistoryDatasetAssociation.table.c.copied_from_library_dataset_dataset_association_id == model.LibraryDatasetDatasetAssociation.table.c.id ) ),
1525        implicitly_converted_datasets=relation(
1526            model.ImplicitlyConvertedDatasetAssociation,
1527            primaryjoin=( model.ImplicitlyConvertedDatasetAssociation.table.c.ldda_parent_id == model.LibraryDatasetDatasetAssociation.table.c.id ) ),
1528        children=relation(
1529            model.LibraryDatasetDatasetAssociation,
1530            primaryjoin=( model.LibraryDatasetDatasetAssociation.table.c.parent_id == model.LibraryDatasetDatasetAssociation.table.c.id ),
1531            backref=backref( "parent", primaryjoin=( model.LibraryDatasetDatasetAssociation.table.c.parent_id == model.LibraryDatasetDatasetAssociation.table.c.id ), remote_side=[model.LibraryDatasetDatasetAssociation.table.c.id] ) ),
1532        visible_children=relation(
1533            model.LibraryDatasetDatasetAssociation,
1534            primaryjoin=( ( model.LibraryDatasetDatasetAssociation.table.c.parent_id == model.LibraryDatasetDatasetAssociation.table.c.id ) & ( model.LibraryDatasetDatasetAssociation.table.c.visible == True ) ) ),
1535        extended_metadata=relation(
1536            model.ExtendedMetadata,
1537            primaryjoin=( ( model.LibraryDatasetDatasetAssociation.table.c.extended_metadata_id == model.ExtendedMetadata.table.c.id ) )
1538        )
1539    ))
1540
1541mapper( model.LibraryDatasetDatasetInfoAssociation, model.LibraryDatasetDatasetInfoAssociation.table,
1542               properties=dict( library_dataset_dataset_association=relation( model.LibraryDatasetDatasetAssociation,
1543                                                                              primaryjoin=( ( model.LibraryDatasetDatasetInfoAssociation.table.c.library_dataset_dataset_association_id == model.LibraryDatasetDatasetAssociation.table.c.id ) & ( not_( model.LibraryDatasetDatasetInfoAssociation.table.c.deleted ) ) ), backref="info_association" ),
1544                                template=relation( model.FormDefinition,
1545                                                   primaryjoin=( model.LibraryDatasetDatasetInfoAssociation.table.c.form_definition_id == model.FormDefinition.table.c.id ) ),
1546                                info=relation( model.FormValues,
1547                                               primaryjoin=( model.LibraryDatasetDatasetInfoAssociation.table.c.form_values_id == model.FormValues.table.c.id ) )
1548                              ) )
1549
1550mapper( model.JobToInputDatasetAssociation,
1551        model.JobToInputDatasetAssociation.table, properties=dict(
1552            job=relation( model.Job ), dataset=relation(
1553                model.HistoryDatasetAssociation, lazy=False,
1554                backref="dependent_jobs" ) ) )
1555
1556mapper( model.JobToOutputDatasetAssociation,
1557        model.JobToOutputDatasetAssociation.table, properties=dict(
1558            job=relation( model.Job ), dataset=relation(
1559                model.HistoryDatasetAssociation, lazy=False ) ) )
1560
1561mapper( model.JobToInputLibraryDatasetAssociation,
1562        model.JobToInputLibraryDatasetAssociation.table, properties=dict(
1563            job=relation( model.Job ), dataset=relation(
1564                model.LibraryDatasetDatasetAssociation, lazy=False,
1565                backref="dependent_jobs" ) ) )
1566
1567mapper( model.JobToOutputLibraryDatasetAssociation,
1568        model.JobToOutputLibraryDatasetAssociation.table, properties=dict(
1569            job=relation( model.Job ), dataset=relation(
1570                model.LibraryDatasetDatasetAssociation, lazy=False ) ) )
1571
1572mapper( model.JobParameter, model.JobParameter.table )
1573
1574mapper( model.JobExternalOutputMetadata, model.JobExternalOutputMetadata.table,
1575    properties=dict( job = relation( model.Job ),
1576                     history_dataset_association = relation( model.HistoryDatasetAssociation, lazy = False ),
1577                     library_dataset_dataset_association = relation( model.LibraryDatasetDatasetAssociation, lazy = False ) ) )
1578
1579mapper( model.JobExportHistoryArchive, model.JobExportHistoryArchive.table,
1580    properties=dict( job = relation( model.Job ),
1581                     history = relation( model.History ),
1582                     dataset = relation( model.Dataset ) ) )
1583
1584mapper( model.JobImportHistoryArchive, model.JobImportHistoryArchive.table,
1585    properties=dict( job = relation( model.Job ), history = relation( model.History ) ) )
1586
1587mapper( model.GenomeIndexToolData, model.GenomeIndexToolData.table,
1588    properties=dict( job = relation( model.Job, backref='job' ),
1589                     dataset = relation( model.Dataset ),
1590                     user = relation( model.User ),
1591                     deferred = relation( model.DeferredJob, backref='deferred_job' ),
1592                     transfer = relation( model.TransferJob, backref='transfer_job' ) ) )
1593
1594mapper(model.PostJobAction, model.PostJobAction.table,
1595    properties=dict(workflow_step = relation( model.WorkflowStep, backref='post_job_actions', primaryjoin=(model.WorkflowStep.table.c.id == model.PostJobAction.table.c.workflow_step_id))))
1596
1597mapper( model.PostJobActionAssociation, model.PostJobActionAssociation.table,
1598    properties=dict( job = relation( model.Job ),
1599                     post_job_action = relation( model.PostJobAction) ) )
1600
1601mapper( model.Job, model.Job.table,
1602    #properties=dict( user=relation( model.User.mapper ),
1603    properties=dict( user=relation( model.User ),
1604                     galaxy_session=relation( model.GalaxySession ),
1605                     history=relation( model.History ),
1606                     library_folder=relation( model.LibraryFolder ),
1607                     parameters=relation( model.JobParameter, lazy=False ),
1608                     input_datasets=relation( model.JobToInputDatasetAssociation ),
1609                     output_datasets=relation( model.JobToOutputDatasetAssociation ),
1610                     post_job_actions=relation( model.PostJobActionAssociation, lazy=False ),
1611                     input_library_datasets=relation( model.JobToInputLibraryDatasetAssociation ),
1612                     output_library_datasets=relation( model.JobToOutputLibraryDatasetAssociation ),
1613                     external_output_metadata = relation( model.JobExternalOutputMetadata, lazy = False ),
1614                     tasks = relation(model.Task) ) )
1615
1616mapper( model.Task, model.Task.table,
1617    properties=dict( job = relation( model.Job ) ) )
1618
1619mapper( model.DeferredJob, model.DeferredJob.table,
1620    properties = {} )
1621
1622mapper( model.TransferJob, model.TransferJob.table,
1623    properties = {} )
1624
1625mapper( model.Event, model.Event.table,
1626    properties=dict( history=relation( model.History ),
1627                     galaxy_session=relation( model.GalaxySession ),
1628                     #user=relation( model.User.mapper ) ) )
1629                     user=relation( model.User ) ) )
1630
1631mapper( model.GalaxySession, model.GalaxySession.table,
1632    properties=dict( histories=relation( model.GalaxySessionToHistoryAssociation ),
1633                     current_history=relation( model.History ),
1634                     #user=relation( model.User.mapper ) ) )
1635                     user=relation( model.User ) ) )
1636
1637mapper( model.GalaxySessionToHistoryAssociation, model.GalaxySessionToHistoryAssociation.table,
1638    properties=dict( galaxy_session=relation( model.GalaxySession ),
1639                     history=relation( model.History ) ) )
1640
1641mapper( model.Workflow, model.Workflow.table,
1642    properties=dict( steps=relation( model.WorkflowStep, backref='workflow',
1643                                     order_by=asc(model.WorkflowStep.table.c.order_index),
1644                                     cascade="all, delete-orphan",
1645                                     lazy=False ),
1646                     # outputs = relation( WorkflowOutput, backref='workflow',
1647                     #                 primaryjoin=(Workflow.table.c.id == WorkflowStep.table.c.workflow_id),
1648                     #                 secondaryjoin=(WorkflowStep.table.c.id == WorkflowOutput.table.c.workflow_step_id))
1649                                      ) )
1650
1651mapper( model.WorkflowStep, model.WorkflowStep.table,
1652                properties=dict(
1653                    tags=relation(model.WorkflowStepTagAssociation, order_by=model.WorkflowStepTagAssociation.table.c.id, backref="workflow_steps"),
1654                    annotations=relation( model.WorkflowStepAnnotationAssociation, order_by=model.WorkflowStepAnnotationAssociation.table.c.id, backref="workflow_steps" ) )
1655                )
1656
1657mapper( model.WorkflowOutput, model.WorkflowOutput.table,
1658    properties=dict(workflow_step = relation( model.WorkflowStep, backref='workflow_outputs', primaryjoin=(model.WorkflowStep.table.c.id == model.WorkflowOutput.table.c.workflow_step_id))))
1659
1660mapper( model.WorkflowStepConnection, model.WorkflowStepConnection.table,
1661    properties=dict( input_step=relation( model.WorkflowStep, backref="input_connections", cascade="all",
1662                                          primaryjoin=( model.WorkflowStepConnection.table.c.input_step_id == model.WorkflowStep.table.c.id ) ),
1663                     output_step=relation( model.WorkflowStep, backref="output_connections", cascade="all",
1664                                           primaryjoin=( model.WorkflowStepConnection.table.c.output_step_id == model.WorkflowStep.table.c.id ) ) ) )
1665
1666
1667mapper( model.StoredWorkflow, model.StoredWorkflow.table,
1668    properties=dict( user=relation( model.User,
1669                                    primaryjoin=( model.User.table.c.id == model.StoredWorkflow.table.c.user_id ),
1670                                    backref='stored_workflows' ),
1671                     workflows=relation( model.Workflow, backref='stored_workflow',
1672                                         cascade="all, delete-orphan",
1673                                         primaryjoin=( model.StoredWorkflow.table.c.id == model.Workflow.table.c.stored_workflow_id ) ),
1674                     latest_workflow=relation( model.Workflow, post_update=True,
1675                                               primaryjoin=( model.StoredWorkflow.table.c.latest_workflow_id == model.Workflow.table.c.id ),
1676                                               lazy=False ),
1677                     tags=relation( model.StoredWorkflowTagAssociation, order_by=model.StoredWorkflowTagAssociation.table.c.id, backref="stored_workflows" ),
1678                     owner_tags=relation( model.StoredWorkflowTagAssociation,
1679                                    primaryjoin=and_( model.StoredWorkflow.table.c.id == model.StoredWorkflowTagAssociation.table.c.stored_workflow_id,
1680                                                      model.StoredWorkflow.table.c.user_id == model.StoredWorkflowTagAssociation.table.c.user_id ),
1681                                    order_by=model.StoredWorkflowTagAssociation.table.c.id ),
1682                     annotations=relation( model.StoredWorkflowAnnotationAssociation, order_by=model.StoredWorkflowAnnotationAssociation.table.c.id, backref="stored_workflows" ),
1683                     ratings=relation( model.StoredWorkflowRatingAssociation, order_by=model.StoredWorkflowRatingAssociation.table.c.id, backref="stored_workflows" ) )
1684                   )
1685
1686# Set up proxy so that
1687#   StoredWorkflow.users_shared_with
1688# returns a list of users that workflow is shared with.
1689model.StoredWorkflow.users_shared_with_dot_users = association_proxy( 'users_shared_with', 'user' )
1690
1691mapper( model.StoredWorkflowUserShareAssociation, model.StoredWorkflowUserShareAssociation.table,
1692    properties=dict( user=relation( model.User, backref='workflows_shared_by_others' ),
1693                     stored_workflow=relation( model.StoredWorkflow, backref='users_shared_with' )
1694                   ) )
1695
1696mapper( model.StoredWorkflowMenuEntry, model.StoredWorkflowMenuEntry.table,
1697    properties=dict( stored_workflow=relation( model.StoredWorkflow ) ) )
1698
1699mapper( model.WorkflowInvocation, model.WorkflowInvocation.table,
1700    properties=dict(
1701        steps=relation( model.WorkflowInvocationStep, backref='workflow_invocation', lazy=False ),
1702        workflow=relation( model.Workflow ) ) )
1703
1704mapper( model.WorkflowInvocationStep, model.WorkflowInvocationStep.table,
1705    properties=dict(
1706        workflow_step = relation( model.WorkflowStep ),
1707        job = relation( model.Job, backref=backref( 'workflow_invocation_step', uselist=False ) ) ) )
1708
1709mapper( model.MetadataFile, model.MetadataFile.table,
1710    properties=dict( history_dataset=relation( model.HistoryDatasetAssociation ), library_dataset=relation( model.LibraryDatasetDatasetAssociation ) ) )
1711
1712mapper( model.PageRevision, model.PageRevision.table )
1713
1714mapper( model.Page, model.Page.table,
1715    properties=dict( user=relation( model.User ),
1716                     revisions=relation( model.PageRevision, backref='page',
1717                                         cascade="all, delete-orphan",
1718                                         primaryjoin=( model.Page.table.c.id == model.PageRevision.table.c.page_id ) ),
1719                     latest_revision=relation( model.PageRevision, post_update=True,
1720                                               primaryjoin=( model.Page.table.c.latest_revision_id == model.PageRevision.table.c.id ),
1721                                               lazy=False ),
1722                     tags=relation(model.PageTagAssociation, order_by=model.PageTagAssociation.table.c.id, backref="pages"),
1723                     annotations=relation( model.PageAnnotationAssociation, order_by=model.PageAnnotationAssociation.table.c.id, backref="pages" ),
1724                     ratings=relation( model.PageRatingAssociation, order_by=model.PageRatingAssociation.table.c.id, backref="pages" )
1725                   ) )
1726
1727# Set up proxy so that
1728#   Page.users_shared_with
1729# returns a list of users that page is shared with.
1730model.Page.users_shared_with_dot_users = association_proxy( 'users_shared_with', 'user' )
1731
1732mapper( model.PageUserShareAssociation, model.PageUserShareAssociation.table,
1733   properties=dict( user=relation( model.User, backref='pages_shared_by_others' ),
1734                    page=relation( model.Page, backref='users_shared_with' )
1735                  ) )
1736
1737mapper( model.VisualizationRevision, model.VisualizationRevision.table )
1738
1739mapper( model.Visualization, model.Visualization.table,
1740    properties=dict( user=relation( model.User ),
1741                     revisions=relation( model.VisualizationRevision, backref='visualization',
1742                                         cascade="all, delete-orphan",
1743                                         primaryjoin=( model.Visualization.table.c.id == model.VisualizationRevision.table.c.visualization_id ) ),
1744                     latest_revision=relation( model.VisualizationRevision, post_update=True,
1745                                               primaryjoin=( model.Visualization.table.c.latest_revision_id == model.VisualizationRevision.table.c.id ),
1746                                               lazy=False ),
1747                     tags=relation( model.VisualizationTagAssociation, order_by=model.VisualizationTagAssociation.table.c.id, backref="visualizations" ),
1748                     annotations=relation( model.VisualizationAnnotationAssociation, order_by=model.VisualizationAnnotationAssociation.table.c.id, backref="visualizations" ),
1749                     ratings=relation( model.VisualizationRatingAssociation, order_by=model.VisualizationRatingAssociation.table.c.id, backref="visualizations" )
1750                   ) )
1751
1752# Set up proxy so that
1753#   Visualization.users_shared_with
1754# returns a list of users that visualization is shared with.
1755model.Visualization.users_shared_with_dot_users = association_proxy( 'users_shared_with', 'user' )
1756
1757mapper( model.VisualizationUserShareAssociation, model.VisualizationUserShareAssociation.table,
1758  properties=dict( user=relation( model.User, backref='visualizations_shared_by_others' ),
1759                   visualization=relation( model.Visualization, backref='users_shared_with' )
1760                 ) )
1761
1762# Tag tables.
1763simple_mapping( model.Tag,
1764    children=relation(model.Tag, backref=backref( 'parent', remote_side=[model.Tag.table.c.id] ) )
1765)
1766
1767
1768def tag_mapping( tag_association_class, backref_name ):
1769    simple_mapping( tag_association_class, tag=relation( model.Tag, backref=backref_name), user=relation( model.User ) )
1770
1771tag_mapping( model.HistoryTagAssociation, "tagged_histories" )
1772
1773tag_mapping( model.DatasetTagAssociation, "tagged_datasets" )
1774
1775tag_mapping( model.HistoryDatasetAssociationTagAssociation, "tagged_history_dataset_associations" )
1776
1777tag_mapping( model.PageTagAssociation, "tagged_pages" )
1778
1779tag_mapping( model.StoredWorkflowTagAssociation, "tagged_workflows" )
1780
1781tag_mapping( model.WorkflowStepTagAssociation, "tagged_workflow_steps" )
1782
1783tag_mapping( model.VisualizationTagAssociation, "tagged_visualizations" )
1784
1785tag_mapping( model.ToolTagAssociation, "tagged_tools" )
1786
1787
1788# Annotation tables.
1789def annotation_mapping( annotation_class, **kwds ):
1790    kwds = dict( [ (key, relation( value ) ) for key, value in kwds.iteritems() ] )
1791    simple_mapping( annotation_class, **dict(user=relation( model.User ), **kwds ) )
1792
1793annotation_mapping( model.HistoryAnnotationAssociation, history=model.History )
1794
1795annotation_mapping( model.HistoryDatasetAssociationAnnotationAssociation, hda=model.HistoryDatasetAssociation )
1796
1797annotation_mapping( model.StoredWorkflowAnnotationAssociation, stored_workflow=model.StoredWorkflow )
1798
1799annotation_mapping( model.WorkflowStepAnnotationAssociation, workflow_step=model.WorkflowStep )
1800
1801annotation_mapping( model.PageAnnotationAssociation, page=model.Page )
1802
1803annotation_mapping( model.VisualizationAnnotationAssociation, visualization=model.Visualization )
1804
1805
1806# Rating tables.
1807def rating_mapping( rating_class, **kwds ):
1808    kwds = dict( [ (key, relation( value ) ) for key, value in kwds.iteritems() ] )
1809    simple_mapping( rating_class, **dict(user=relation( model.User ), **kwds ) )
1810
1811rating_mapping( model.HistoryRatingAssociation, history=model.History )
1812
1813rating_mapping( model.HistoryDatasetAssociationRatingAssociation, hda=model.HistoryDatasetAssociation )
1814
1815rating_mapping( model.StoredWorkflowRatingAssociation, stored_workflow=model.StoredWorkflow )
1816
1817rating_mapping( model.PageRatingAssociation, page=model.Page )
1818
1819rating_mapping( model.VisualizationRatingAssociation, visualizaiton=model.Visualization )
1820
1821#Data Manager tables
1822mapper( model.DataManagerHistoryAssociation, model.DataManagerHistoryAssociation.table,
1823    properties=dict( history=relation( model.History ),
1824                     user=relation( model.User, backref='data_manager_histories' )
1825                    )
1826              )
1827
1828mapper( model.DataManagerJobAssociation, model.DataManagerJobAssociation.table,
1829    properties=dict( job=relation( model.Job, backref=backref('data_manager_association', uselist=False ), uselist=False ) )
1830              )
1831
1832# User tables.
1833
1834mapper( model.UserPreference, model.UserPreference.table,
1835    properties = {}
1836              )
1837
1838mapper( model.UserAction, model.UserAction.table,
1839  #properties = dict( user=relation( model.User.mapper ) )
1840  properties = dict( user=relation( model.User ) )
1841            )
1842
1843mapper( model.APIKeys, model.APIKeys.table,
1844    properties = {} )
1845
1846
1847#model.HistoryDatasetAssociation.mapper.add_property( "creating_job_associations", relation( model.JobToOutputDatasetAssociation ) )
1848#model.LibraryDatasetDatasetAssociation.mapper.add_property( "creating_job_associations", relation( model.JobToOutputLibraryDatasetAssociation ) )
1849class_mapper(model.HistoryDatasetAssociation).add_property( "creating_job_associations", relation( model.JobToOutputDatasetAssociation ) )
1850class_mapper(model.LibraryDatasetDatasetAssociation).add_property( "creating_job_associations", relation( model.JobToOutputLibraryDatasetAssociation ) )
1851
1852# Helper methods.
1853
1854def db_next_hid( self ):
1855    """
1856    db_next_hid( self )
1857    
1858    Override __next_hid to generate from the database in a concurrency safe way.
1859    Loads the next history ID from the DB and returns it. 
1860    It also saves the future next_id into the DB.
1861
1862    :rtype:     int
1863    :returns:   the next history id
1864    """
1865    conn = object_session( self ).connection()
1866    table = self.table
1867    trans = conn.begin()
1868    try:
1869        next_hid = select( [table.c.hid_counter], table.c.id == self.id, for_update=True ).scalar()
1870        table.update( table.c.id == self.id ).execute( hid_counter = ( next_hid + 1 ) )
1871        trans.commit()
1872        return next_hid
1873    except:
1874        trans.rollback()
1875        raise
1876
1877model.History._next_hid = db_next_hid
1878
1879
1880def init( file_path, url, engine_options={}, create_tables=False, map_install_models=False, database_query_profiling_proxy=False, object_store=None, trace_logger=None, use_pbkdf2=True ):
1881    """Connect mappings to the database"""
1882    # Connect dataset to the file path
1883    model.Dataset.file_path = file_path
1884    # Connect dataset to object store
1885    model.Dataset.object_store = object_store
1886    # Use PBKDF2 password hashing?
1887    model.User.use_pbkdf2 = use_pbkdf2
1888    # Load the appropriate db module
1889    engine = build_engine( url, engine_options, database_query_profiling_proxy, trace_logger )
1890
1891    # Connect the metadata to the database.
1892    metadata.bind = engine
1893
1894    model_modules = [model]
1895    if map_install_models:
1896        import galaxy.model.tool_shed_install.mapping
1897        from galaxy.model import tool_shed_install
1898        model_modules.append(tool_shed_install)
1899
1900    result = ModelMapping(model_modules, engine=engine)
1901
1902    # Create tables if needed
1903    if create_tables:
1904        metadata.create_all()
1905        # metadata.engine.commit()
1906
1907    result.create_tables = create_tables
1908    #load local galaxy security policy
1909    result.security_agent = GalaxyRBACAgent( result )
1910    return result