/lib/galaxy/webapps/community/model/mapping.py

  1"""
  2Details of how the data model objects are mapped onto the relational database
  3are encapsulated here. 
  4"""
  5import logging
  6log = logging.getLogger( __name__ )
  7
  8import sys
  9import datetime
 10
 11from galaxy.webapps.community.model import *
 12from galaxy.model.orm import *
 13from galaxy.model.orm.ext.assignmapper import *
 14from galaxy.model.custom_types import *
 15from galaxy.util.bunch import Bunch
 16from galaxy.webapps.community.security import CommunityRBACAgent
 17
 18metadata = MetaData()
 19context = Session = scoped_session( sessionmaker( autoflush=False, autocommit=True ) )
 20
 21# For backward compatibility with "context.current"
 22context.current = Session
 23
 24dialect_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
 38now = datetime.datetime.utcnow
 39
 40User.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
 51Group.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
 58Role.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
 67UserGroupAssociation.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
 74UserRoleAssociation.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
 81GroupRoleAssociation.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
 88GalaxySession.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
101Repository.table = Table( "repository", metadata,
102    Column( "id", Integer, primary_key=True ),
103    Column( "create_time", DateTime, default=now ),
104    Column( "update_time", DateTime, default=now, onupdate=now ),
105    Column( "name", TrimmedString( 255 ), index=True ),
106    Column( "description" , TEXT ),
107    Column( "long_description" , TEXT ),
108    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
109    Column( "private", Boolean, default=False ),
110    Column( "deleted", Boolean, index=True, default=False ),
111    Column( "email_alerts", JSONType, nullable=True ),
112    Column( "times_downloaded", Integer ) )
113
114RepositoryMetadata.table = Table( "repository_metadata", metadata,
115    Column( "id", Integer, primary_key=True ),
116    Column( "create_time", DateTime, default=now ),
117    Column( "update_time", DateTime, default=now, onupdate=now ),
118    Column( "repository_id", Integer, ForeignKey( "repository.id" ), index=True ),
119    Column( "changeset_revision", TrimmedString( 255 ), index=True ),
120    Column( "metadata", JSONType, nullable=True ),
121    Column( "malicious", Boolean, default=False ) )
122
123RepositoryRatingAssociation.table = Table( "repository_rating_association", metadata,
124    Column( "id", Integer, primary_key=True ),
125    Column( "create_time", DateTime, default=now ),
126    Column( "update_time", DateTime, default=now, onupdate=now ),
127    Column( "repository_id", Integer, ForeignKey( "repository.id" ), index=True ),
128    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
129    Column( "rating", Integer, index=True ),
130    Column( "comment", TEXT ) )
131
132RepositoryCategoryAssociation.table = Table( "repository_category_association", metadata,
133    Column( "id", Integer, primary_key=True ),
134    Column( "repository_id", Integer, ForeignKey( "repository.id" ), index=True ),
135    Column( "category_id", Integer, ForeignKey( "category.id" ), index=True ) )
136
137Category.table = Table( "category", metadata,
138    Column( "id", Integer, primary_key=True ),
139    Column( "create_time", DateTime, default=now ),
140    Column( "update_time", DateTime, default=now, onupdate=now ),
141    Column( "name", TrimmedString( 255 ), index=True, unique=True ),
142    Column( "description" , TEXT ),
143    Column( "deleted", Boolean, index=True, default=False ) )
144
145Tag.table = Table( "tag", metadata,
146    Column( "id", Integer, primary_key=True ),
147    Column( "type", Integer ),
148    Column( "parent_id", Integer, ForeignKey( "tag.id" ) ),
149    Column( "name", TrimmedString(255) ), 
150    UniqueConstraint( "name" ) )
151
152# With the tables defined we can define the mappers and setup the 
153# relationships between the model objects.
154assign_mapper( context, User, User.table, 
155    properties=dict( active_repositories=relation( Repository, primaryjoin=( ( Repository.table.c.user_id == User.table.c.id ) & ( not_( Repository.table.c.deleted ) ) ), order_by=( Repository.table.c.name ) ),
156                     galaxy_sessions=relation( GalaxySession, order_by=desc( GalaxySession.table.c.update_time ) ) ) )
157
158assign_mapper( context, Group, Group.table,
159    properties=dict( users=relation( UserGroupAssociation ) ) )
160
161assign_mapper( context, Role, Role.table,
162    properties=dict(
163        users=relation( UserRoleAssociation ),
164        groups=relation( GroupRoleAssociation ) ) )
165
166assign_mapper( context, UserGroupAssociation, UserGroupAssociation.table,
167    properties=dict( user=relation( User, backref = "groups" ),
168                     group=relation( Group, backref = "members" ) ) )
169
170assign_mapper( context, UserRoleAssociation, UserRoleAssociation.table,
171    properties=dict(
172        user=relation( User, backref="roles" ),
173        non_private_roles=relation( User, 
174                                    backref="non_private_roles",
175                                    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 ) ) ),
176        role=relation( Role ) ) )
177
178assign_mapper( context, GroupRoleAssociation, GroupRoleAssociation.table,
179    properties=dict(
180        group=relation( Group, backref="roles" ),
181        role=relation( Role ) ) )
182
183assign_mapper( context, GalaxySession, GalaxySession.table,
184    properties=dict( user=relation( User.mapper ) ) )
185
186assign_mapper( context, Tag, Tag.table,
187    properties=dict( children=relation(Tag, backref=backref( 'parent', remote_side=[Tag.table.c.id] ) ) ) )
188
189assign_mapper( context, Category, Category.table,
190    properties=dict( repositories=relation( RepositoryCategoryAssociation ) ) )
191
192assign_mapper( context, Repository, Repository.table, 
193    properties = dict(
194        categories=relation( RepositoryCategoryAssociation ),
195        ratings=relation( RepositoryRatingAssociation, order_by=desc( RepositoryRatingAssociation.table.c.update_time ), backref="repositories" ),
196        user=relation( User.mapper ),
197        downloadable_revisions=relation( RepositoryMetadata, order_by=desc( RepositoryMetadata.table.c.id ) ) ) )
198
199assign_mapper( context, RepositoryMetadata, RepositoryMetadata.table,
200    properties=dict( repository=relation( Repository ) ) )
201
202assign_mapper( context, RepositoryRatingAssociation, RepositoryRatingAssociation.table,
203    properties=dict( repository=relation( Repository ), user=relation( User ) ) )
204
205assign_mapper( context, RepositoryCategoryAssociation, RepositoryCategoryAssociation.table,
206    properties=dict(
207        category=relation( Category ),
208        repository=relation( Repository ) ) )
209
210def guess_dialect_for_url( url ):
211    return (url.split(':', 1))[0]
212
213def load_egg_for_url( url ):
214    # Load the appropriate db module
215    dialect = guess_dialect_for_url( url )
216    try:
217        egg = dialect_to_egg[dialect]
218        try:
219            pkg_resources.require( egg )
220            log.debug( "%s egg successfully loaded for %s dialect" % ( egg, dialect ) )
221        except:
222            # If the module's in the path elsewhere (i.e. non-egg), it'll still load.
223            log.warning( "%s egg not found, but an attempt will be made to use %s anyway" % ( egg, dialect ) )
224    except KeyError:
225        # Let this go, it could possibly work with db's we don't support
226        log.error( "database_connection contains an unknown SQLAlchemy database dialect: %s" % dialect )
227
228def init( file_path, url, engine_options={}, create_tables=False ):
229    """Connect mappings to the database"""
230    # Load the appropriate db module
231    load_egg_for_url( url )
232    # Create the database engine
233    engine = create_engine( url, **engine_options )
234    # Connect the metadata to the database.
235    metadata.bind = engine
236    # Clear any existing contextual sessions and reconfigure
237    Session.remove()
238    Session.configure( bind=engine )
239    # Create tables if needed
240    if create_tables:
241        metadata.create_all()
242    # Pack everything into a bunch
243    result = Bunch( **globals() )
244    result.engine = engine
245    result.session = Session
246    result.create_tables = create_tables
247    #load local galaxy security policy
248    result.security_agent = CommunityRBACAgent( result )
249    return result