[2] | 1 | """ |
---|
| 2 | Details of how the data model objects are mapped onto the relational database |
---|
| 3 | are encapsulated here. |
---|
| 4 | """ |
---|
| 5 | import logging |
---|
| 6 | log = logging.getLogger( __name__ ) |
---|
| 7 | |
---|
| 8 | import sys |
---|
| 9 | import datetime |
---|
| 10 | |
---|
| 11 | from galaxy.webapps.community.model import * |
---|
| 12 | from galaxy.model.orm import * |
---|
| 13 | from galaxy.model.orm.ext.assignmapper import * |
---|
| 14 | from galaxy.model.custom_types import * |
---|
| 15 | from galaxy.util.bunch import Bunch |
---|
| 16 | from galaxy.webapps.community.security import CommunityRBACAgent |
---|
| 17 | |
---|
| 18 | metadata = MetaData() |
---|
| 19 | context = Session = scoped_session( sessionmaker( autoflush=False, autocommit=True ) ) |
---|
| 20 | |
---|
| 21 | # For backward compatibility with "context.current" |
---|
| 22 | context.current = Session |
---|
| 23 | |
---|
| 24 | dialect_to_egg = { |
---|
| 25 | "sqlite" : "pysqlite>=2", |
---|
| 26 | "postgres" : "psycopg2", |
---|
| 27 | "mysql" : "MySQL_python" |
---|
| 28 | } |
---|
| 29 | |
---|
| 30 | # NOTE REGARDING TIMESTAMPS: |
---|
| 31 | # It is currently difficult to have the timestamps calculated by the |
---|
| 32 | # database in a portable way, so we're doing it in the client. This |
---|
| 33 | # also saves us from needing to postfetch on postgres. HOWEVER: it |
---|
| 34 | # relies on the client's clock being set correctly, so if clustering |
---|
| 35 | # web servers, use a time server to ensure synchronization |
---|
| 36 | |
---|
| 37 | # Return the current time in UTC without any timezone information |
---|
| 38 | now = datetime.datetime.utcnow |
---|
| 39 | |
---|
| 40 | User.table = Table( "galaxy_user", metadata, |
---|
| 41 | Column( "id", Integer, primary_key=True), |
---|
| 42 | Column( "create_time", DateTime, default=now ), |
---|
| 43 | Column( "update_time", DateTime, default=now, onupdate=now ), |
---|
| 44 | Column( "email", TrimmedString( 255 ), nullable=False ), |
---|
| 45 | Column( "username", String( 255 ), index=True ), |
---|
| 46 | Column( "password", TrimmedString( 40 ), nullable=False ), |
---|
| 47 | Column( "external", Boolean, default=False ), |
---|
| 48 | Column( "deleted", Boolean, index=True, default=False ), |
---|
| 49 | Column( "purged", Boolean, index=True, default=False ) ) |
---|
| 50 | |
---|
| 51 | Group.table = Table( "galaxy_group", metadata, |
---|
| 52 | Column( "id", Integer, primary_key=True ), |
---|
| 53 | Column( "create_time", DateTime, default=now ), |
---|
| 54 | Column( "update_time", DateTime, default=now, onupdate=now ), |
---|
| 55 | Column( "name", String( 255 ), index=True, unique=True ), |
---|
| 56 | Column( "deleted", Boolean, index=True, default=False ) ) |
---|
| 57 | |
---|
| 58 | Role.table = Table( "role", metadata, |
---|
| 59 | Column( "id", Integer, primary_key=True ), |
---|
| 60 | Column( "create_time", DateTime, default=now ), |
---|
| 61 | Column( "update_time", DateTime, default=now, onupdate=now ), |
---|
| 62 | Column( "name", String( 255 ), index=True, unique=True ), |
---|
| 63 | Column( "description", TEXT ), |
---|
| 64 | Column( "type", String( 40 ), index=True ), |
---|
| 65 | Column( "deleted", Boolean, index=True, default=False ) ) |
---|
| 66 | |
---|
| 67 | UserGroupAssociation.table = Table( "user_group_association", metadata, |
---|
| 68 | Column( "id", Integer, primary_key=True ), |
---|
| 69 | Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ), |
---|
| 70 | Column( "group_id", Integer, ForeignKey( "galaxy_group.id" ), index=True ), |
---|
| 71 | Column( "create_time", DateTime, default=now ), |
---|
| 72 | Column( "update_time", DateTime, default=now, onupdate=now ) ) |
---|
| 73 | |
---|
| 74 | UserRoleAssociation.table = Table( "user_role_association", metadata, |
---|
| 75 | Column( "id", Integer, primary_key=True ), |
---|
| 76 | Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ), |
---|
| 77 | Column( "role_id", Integer, ForeignKey( "role.id" ), index=True ), |
---|
| 78 | Column( "create_time", DateTime, default=now ), |
---|
| 79 | Column( "update_time", DateTime, default=now, onupdate=now ) ) |
---|
| 80 | |
---|
| 81 | GroupRoleAssociation.table = Table( "group_role_association", metadata, |
---|
| 82 | Column( "id", Integer, primary_key=True ), |
---|
| 83 | Column( "group_id", Integer, ForeignKey( "galaxy_group.id" ), index=True ), |
---|
| 84 | Column( "role_id", Integer, ForeignKey( "role.id" ), index=True ), |
---|
| 85 | Column( "create_time", DateTime, default=now ), |
---|
| 86 | Column( "update_time", DateTime, default=now, onupdate=now ) ) |
---|
| 87 | |
---|
| 88 | GalaxySession.table = Table( "galaxy_session", metadata, |
---|
| 89 | Column( "id", Integer, primary_key=True ), |
---|
| 90 | Column( "create_time", DateTime, default=now ), |
---|
| 91 | Column( "update_time", DateTime, default=now, onupdate=now ), |
---|
| 92 | Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True, nullable=True ), |
---|
| 93 | Column( "remote_host", String( 255 ) ), |
---|
| 94 | Column( "remote_addr", String( 255 ) ), |
---|
| 95 | Column( "referer", TEXT ), |
---|
| 96 | Column( "session_key", TrimmedString( 255 ), index=True, unique=True ), # unique 128 bit random number coerced to a string |
---|
| 97 | Column( "is_valid", Boolean, default=False ), |
---|
| 98 | Column( "prev_session_id", Integer ) # saves a reference to the previous session so we have a way to chain them together |
---|
| 99 | ) |
---|
| 100 | |
---|
| 101 | Tool.table = Table( "tool", metadata, |
---|
| 102 | Column( "id", Integer, primary_key=True ), |
---|
| 103 | Column( "guid", TrimmedString( 255 ), index=True, unique=True ), |
---|
| 104 | Column( "tool_id", TrimmedString( 255 ), index=True ), |
---|
| 105 | Column( "create_time", DateTime, default=now ), |
---|
| 106 | Column( "update_time", DateTime, default=now, onupdate=now ), |
---|
| 107 | Column( "newer_version_id", Integer, ForeignKey( "tool.id" ), nullable=True ), |
---|
| 108 | Column( "name", TrimmedString( 255 ), index=True ), |
---|
| 109 | Column( "description" , TEXT ), |
---|
| 110 | Column( "user_description" , TEXT ), |
---|
| 111 | Column( "version", TrimmedString( 255 ) ), |
---|
| 112 | Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ), |
---|
| 113 | Column( "external_filename" , TEXT ), |
---|
| 114 | Column( "deleted", Boolean, index=True, default=False ), |
---|
| 115 | Column( "suite", Boolean, default=False, index=True ) ) |
---|
| 116 | |
---|
| 117 | Category.table = Table( "category", metadata, |
---|
| 118 | Column( "id", Integer, primary_key=True ), |
---|
| 119 | Column( "create_time", DateTime, default=now ), |
---|
| 120 | Column( "update_time", DateTime, default=now, onupdate=now ), |
---|
| 121 | Column( "name", TrimmedString( 255 ), index=True, unique=True ), |
---|
| 122 | Column( "description" , TEXT ), |
---|
| 123 | Column( "deleted", Boolean, index=True, default=False ) ) |
---|
| 124 | |
---|
| 125 | ToolCategoryAssociation.table = Table( "tool_category_association", metadata, |
---|
| 126 | Column( "id", Integer, primary_key=True ), |
---|
| 127 | Column( "tool_id", Integer, ForeignKey( "tool.id" ), index=True ), |
---|
| 128 | Column( "category_id", Integer, ForeignKey( "category.id" ), index=True ) ) |
---|
| 129 | |
---|
| 130 | Event.table = Table( 'event', metadata, |
---|
| 131 | Column( "id", Integer, primary_key=True ), |
---|
| 132 | Column( "create_time", DateTime, default=now ), |
---|
| 133 | Column( "update_time", DateTime, default=now, onupdate=now ), |
---|
| 134 | Column( "state", TrimmedString( 255 ), index=True ), |
---|
| 135 | Column( "comment", TEXT ) ) |
---|
| 136 | |
---|
| 137 | ToolEventAssociation.table = Table( "tool_event_association", metadata, |
---|
| 138 | Column( "id", Integer, primary_key=True ), |
---|
| 139 | Column( "tool_id", Integer, ForeignKey( "tool.id" ), index=True ), |
---|
| 140 | Column( "event_id", Integer, ForeignKey( "event.id" ), index=True ) ) |
---|
| 141 | |
---|
| 142 | ToolRatingAssociation.table = Table( "tool_rating_association", metadata, |
---|
| 143 | Column( "id", Integer, primary_key=True ), |
---|
| 144 | Column( "create_time", DateTime, default=now ), |
---|
| 145 | Column( "update_time", DateTime, default=now, onupdate=now ), |
---|
| 146 | Column( "tool_id", Integer, ForeignKey( "tool.id" ), index=True ), |
---|
| 147 | Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ), |
---|
| 148 | Column( "rating", Integer, index=True ), |
---|
| 149 | Column( "comment", TEXT ) ) |
---|
| 150 | |
---|
| 151 | Tag.table = Table( "tag", metadata, |
---|
| 152 | Column( "id", Integer, primary_key=True ), |
---|
| 153 | Column( "type", Integer ), |
---|
| 154 | Column( "parent_id", Integer, ForeignKey( "tag.id" ) ), |
---|
| 155 | Column( "name", TrimmedString(255) ), |
---|
| 156 | UniqueConstraint( "name" ) ) |
---|
| 157 | |
---|
| 158 | ToolTagAssociation.table = Table( "tool_tag_association", metadata, |
---|
| 159 | Column( "id", Integer, primary_key=True ), |
---|
| 160 | Column( "tool_id", Integer, ForeignKey( "tool.id" ), index=True ), |
---|
| 161 | Column( "tag_id", Integer, ForeignKey( "tag.id" ), index=True ), |
---|
| 162 | Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ), |
---|
| 163 | Column( "user_tname", TrimmedString(255), index=True), |
---|
| 164 | Column( "value", TrimmedString(255), index=True), |
---|
| 165 | Column( "user_value", TrimmedString(255), index=True) ) |
---|
| 166 | |
---|
| 167 | ToolAnnotationAssociation.table = Table( "tool_annotation_association", metadata, |
---|
| 168 | Column( "id", Integer, primary_key=True ), |
---|
| 169 | Column( "tool_id", Integer, ForeignKey( "tool.id" ), index=True ), |
---|
| 170 | Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ), |
---|
| 171 | Column( "annotation", TEXT, index=True) ) |
---|
| 172 | |
---|
| 173 | # With the tables defined we can define the mappers and setup the |
---|
| 174 | # relationships between the model objects. |
---|
| 175 | assign_mapper( context, User, User.table, |
---|
| 176 | properties=dict( tools=relation( Tool, primaryjoin=( Tool.table.c.user_id == User.table.c.id ), order_by=( Tool.table.c.name ) ), |
---|
| 177 | active_tools=relation( Tool, primaryjoin=( ( Tool.table.c.user_id == User.table.c.id ) & ( not_( Tool.table.c.deleted ) ) ), order_by=( Tool.table.c.name ) ), |
---|
| 178 | galaxy_sessions=relation( GalaxySession, order_by=desc( GalaxySession.table.c.update_time ) ) ) ) |
---|
| 179 | |
---|
| 180 | assign_mapper( context, Group, Group.table, |
---|
| 181 | properties=dict( users=relation( UserGroupAssociation ) ) ) |
---|
| 182 | |
---|
| 183 | assign_mapper( context, Role, Role.table, |
---|
| 184 | properties=dict( |
---|
| 185 | users=relation( UserRoleAssociation ), |
---|
| 186 | groups=relation( GroupRoleAssociation ) |
---|
| 187 | ) |
---|
| 188 | ) |
---|
| 189 | |
---|
| 190 | assign_mapper( context, UserGroupAssociation, UserGroupAssociation.table, |
---|
| 191 | properties=dict( user=relation( User, backref = "groups" ), |
---|
| 192 | group=relation( Group, backref = "members" ) ) ) |
---|
| 193 | |
---|
| 194 | assign_mapper( context, UserRoleAssociation, UserRoleAssociation.table, |
---|
| 195 | properties=dict( |
---|
| 196 | user=relation( User, backref="roles" ), |
---|
| 197 | non_private_roles=relation( User, |
---|
| 198 | backref="non_private_roles", |
---|
| 199 | primaryjoin=( ( User.table.c.id == UserRoleAssociation.table.c.user_id ) & ( UserRoleAssociation.table.c.role_id == Role.table.c.id ) & not_( Role.table.c.name == User.table.c.email ) ) ), |
---|
| 200 | role=relation( Role ) |
---|
| 201 | ) |
---|
| 202 | ) |
---|
| 203 | |
---|
| 204 | assign_mapper( context, GroupRoleAssociation, GroupRoleAssociation.table, |
---|
| 205 | properties=dict( |
---|
| 206 | group=relation( Group, backref="roles" ), |
---|
| 207 | role=relation( Role ) |
---|
| 208 | ) |
---|
| 209 | ) |
---|
| 210 | |
---|
| 211 | assign_mapper( context, GalaxySession, GalaxySession.table, |
---|
| 212 | properties=dict( user=relation( User.mapper ) ) ) |
---|
| 213 | |
---|
| 214 | assign_mapper( context, Tag, Tag.table, |
---|
| 215 | properties=dict( children=relation(Tag, backref=backref( 'parent', remote_side=[Tag.table.c.id] ) ) ) ) |
---|
| 216 | |
---|
| 217 | assign_mapper( context, ToolTagAssociation, ToolTagAssociation.table, |
---|
| 218 | properties=dict( tag=relation(Tag, backref="tagged_tools"), user=relation( User ) ) ) |
---|
| 219 | |
---|
| 220 | assign_mapper( context, ToolAnnotationAssociation, ToolAnnotationAssociation.table, |
---|
| 221 | properties=dict( tool=relation( Tool ), user=relation( User ) ) ) |
---|
| 222 | |
---|
| 223 | assign_mapper( context, Tool, Tool.table, |
---|
| 224 | properties = dict( |
---|
| 225 | categories=relation( ToolCategoryAssociation ), |
---|
| 226 | events=relation( ToolEventAssociation, secondary=Event.table, |
---|
| 227 | primaryjoin=( Tool.table.c.id==ToolEventAssociation.table.c.tool_id ), |
---|
| 228 | secondaryjoin=( ToolEventAssociation.table.c.event_id==Event.table.c.id ), |
---|
| 229 | order_by=desc( Event.table.c.update_time ), |
---|
| 230 | viewonly=True, |
---|
| 231 | uselist=True ), |
---|
| 232 | ratings=relation( ToolRatingAssociation, order_by=desc( ToolRatingAssociation.table.c.update_time ), backref="tools" ), |
---|
| 233 | user=relation( User.mapper ), |
---|
| 234 | older_version=relation( |
---|
| 235 | Tool, |
---|
| 236 | primaryjoin=( Tool.table.c.newer_version_id == Tool.table.c.id ), |
---|
| 237 | backref=backref( "newer_version", primaryjoin=( Tool.table.c.newer_version_id == Tool.table.c.id ), remote_side=[Tool.table.c.id] ) ) |
---|
| 238 | ) ) |
---|
| 239 | |
---|
| 240 | assign_mapper( context, Event, Event.table, |
---|
| 241 | properties=None ) |
---|
| 242 | |
---|
| 243 | assign_mapper( context, ToolEventAssociation, ToolEventAssociation.table, |
---|
| 244 | properties=dict( |
---|
| 245 | tool=relation( Tool ), |
---|
| 246 | event=relation( Event ) |
---|
| 247 | ) |
---|
| 248 | ) |
---|
| 249 | |
---|
| 250 | assign_mapper( context, Category, Category.table, |
---|
| 251 | properties=dict( tools=relation( ToolCategoryAssociation ) ) ) |
---|
| 252 | |
---|
| 253 | assign_mapper( context, ToolCategoryAssociation, ToolCategoryAssociation.table, |
---|
| 254 | properties=dict( |
---|
| 255 | category=relation( Category ), |
---|
| 256 | tool=relation( Tool ) |
---|
| 257 | ) |
---|
| 258 | ) |
---|
| 259 | |
---|
| 260 | assign_mapper( context, ToolRatingAssociation, ToolRatingAssociation.table, |
---|
| 261 | properties=dict( tool=relation( Tool ), user=relation( User ) ) |
---|
| 262 | ) |
---|
| 263 | |
---|
| 264 | |
---|
| 265 | def guess_dialect_for_url( url ): |
---|
| 266 | return (url.split(':', 1))[0] |
---|
| 267 | |
---|
| 268 | def load_egg_for_url( url ): |
---|
| 269 | # Load the appropriate db module |
---|
| 270 | dialect = guess_dialect_for_url( url ) |
---|
| 271 | try: |
---|
| 272 | egg = dialect_to_egg[dialect] |
---|
| 273 | try: |
---|
| 274 | pkg_resources.require( egg ) |
---|
| 275 | log.debug( "%s egg successfully loaded for %s dialect" % ( egg, dialect ) ) |
---|
| 276 | except: |
---|
| 277 | # If the module's in the path elsewhere (i.e. non-egg), it'll still load. |
---|
| 278 | log.warning( "%s egg not found, but an attempt will be made to use %s anyway" % ( egg, dialect ) ) |
---|
| 279 | except KeyError: |
---|
| 280 | # Let this go, it could possibly work with db's we don't support |
---|
| 281 | log.error( "database_connection contains an unknown SQLAlchemy database dialect: %s" % dialect ) |
---|
| 282 | |
---|
| 283 | def init( file_path, url, engine_options={}, create_tables=False ): |
---|
| 284 | """Connect mappings to the database""" |
---|
| 285 | # Connect dataset to the file path |
---|
| 286 | Tool.file_path = file_path |
---|
| 287 | # Load the appropriate db module |
---|
| 288 | load_egg_for_url( url ) |
---|
| 289 | # Create the database engine |
---|
| 290 | engine = create_engine( url, **engine_options ) |
---|
| 291 | # Connect the metadata to the database. |
---|
| 292 | metadata.bind = engine |
---|
| 293 | # Clear any existing contextual sessions and reconfigure |
---|
| 294 | Session.remove() |
---|
| 295 | Session.configure( bind=engine ) |
---|
| 296 | # Create tables if needed |
---|
| 297 | if create_tables: |
---|
| 298 | metadata.create_all() |
---|
| 299 | # Pack everything into a bunch |
---|
| 300 | result = Bunch( **globals() ) |
---|
| 301 | result.engine = engine |
---|
| 302 | result.session = Session |
---|
| 303 | result.create_tables = create_tables |
---|
| 304 | #load local galaxy security policy |
---|
| 305 | result.security_agent = CommunityRBACAgent( result ) |
---|
| 306 | return result |
---|