/lib/galaxy/webapps/community/model/mapping.py
Python | 249 lines | 188 code | 36 blank | 25 comment | 4 complexity | 19c65a708a703937119cec8e829bf5a6 MD5 | raw file
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