root/galaxy-central/lib/galaxy/webapps/community/model/mapping.py

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

import galaxy-central

行番号 
1"""
2Details of how the data model objects are mapped onto the relational database
3are encapsulated here.
4"""
5import logging
6log = logging.getLogger( __name__ )
7
8import sys
9import datetime
10
11from galaxy.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
101Tool.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
117Category.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
125ToolCategoryAssociation.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
130Event.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
137ToolEventAssociation.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
142ToolRatingAssociation.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
151Tag.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
158ToolTagAssociation.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
167ToolAnnotationAssociation.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.
175assign_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
180assign_mapper( context, Group, Group.table,
181    properties=dict( users=relation( UserGroupAssociation ) ) )
182
183assign_mapper( context, Role, Role.table,
184    properties=dict(
185        users=relation( UserRoleAssociation ),
186        groups=relation( GroupRoleAssociation )
187    )
188)
189
190assign_mapper( context, UserGroupAssociation, UserGroupAssociation.table,
191    properties=dict( user=relation( User, backref = "groups" ),
192                     group=relation( Group, backref = "members" ) ) )
193
194assign_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
204assign_mapper( context, GroupRoleAssociation, GroupRoleAssociation.table,
205    properties=dict(
206        group=relation( Group, backref="roles" ),
207        role=relation( Role )
208    )
209)
210
211assign_mapper( context, GalaxySession, GalaxySession.table,
212    properties=dict( user=relation( User.mapper ) ) )
213
214assign_mapper( context, Tag, Tag.table,
215    properties=dict( children=relation(Tag, backref=backref( 'parent', remote_side=[Tag.table.c.id] ) ) ) )
216
217assign_mapper( context, ToolTagAssociation, ToolTagAssociation.table,
218    properties=dict( tag=relation(Tag, backref="tagged_tools"), user=relation( User ) ) )
219                   
220assign_mapper( context, ToolAnnotationAssociation, ToolAnnotationAssociation.table,
221    properties=dict( tool=relation( Tool ), user=relation( User ) ) )
222
223assign_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
240assign_mapper( context, Event, Event.table,
241               properties=None )
242
243assign_mapper( context, ToolEventAssociation, ToolEventAssociation.table,
244    properties=dict(
245        tool=relation( Tool ),
246        event=relation( Event )
247    )
248)
249
250assign_mapper( context, Category, Category.table,
251    properties=dict( tools=relation( ToolCategoryAssociation ) ) )
252
253assign_mapper( context, ToolCategoryAssociation, ToolCategoryAssociation.table,
254    properties=dict(
255        category=relation( Category ),
256        tool=relation( Tool )
257    )
258)
259
260assign_mapper( context, ToolRatingAssociation, ToolRatingAssociation.table,
261    properties=dict( tool=relation( Tool ), user=relation( User ) )
262                    )
263
264
265def guess_dialect_for_url( url ):
266    return (url.split(':', 1))[0]
267
268def 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
283def 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
Note: リポジトリブラウザについてのヘルプは TracBrowser を参照してください。