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

https://bitbucket.org/cistrome/cistrome-harvard/ · Python · 249 lines · 188 code · 36 blank · 25 comment · 4 complexity · 19c65a708a703937119cec8e829bf5a6 MD5 · raw file

  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. import sys
  8. import datetime
  9. from galaxy.webapps.community.model import *
  10. from galaxy.model.orm import *
  11. from galaxy.model.orm.ext.assignmapper import *
  12. from galaxy.model.custom_types import *
  13. from galaxy.util.bunch import Bunch
  14. from galaxy.webapps.community.security import CommunityRBACAgent
  15. metadata = MetaData()
  16. context = Session = scoped_session( sessionmaker( autoflush=False, autocommit=True ) )
  17. # For backward compatibility with "context.current"
  18. context.current = Session
  19. dialect_to_egg = {
  20. "sqlite" : "pysqlite>=2",
  21. "postgres" : "psycopg2",
  22. "mysql" : "MySQL_python"
  23. }
  24. # NOTE REGARDING TIMESTAMPS:
  25. # It is currently difficult to have the timestamps calculated by the
  26. # database in a portable way, so we're doing it in the client. This
  27. # also saves us from needing to postfetch on postgres. HOWEVER: it
  28. # relies on the client's clock being set correctly, so if clustering
  29. # web servers, use a time server to ensure synchronization
  30. # Return the current time in UTC without any timezone information
  31. now = datetime.datetime.utcnow
  32. User.table = Table( "galaxy_user", metadata,
  33. Column( "id", Integer, primary_key=True),
  34. Column( "create_time", DateTime, default=now ),
  35. Column( "update_time", DateTime, default=now, onupdate=now ),
  36. Column( "email", TrimmedString( 255 ), nullable=False ),
  37. Column( "username", String( 255 ), index=True ),
  38. Column( "password", TrimmedString( 40 ), nullable=False ),
  39. Column( "external", Boolean, default=False ),
  40. Column( "deleted", Boolean, index=True, default=False ),
  41. Column( "purged", Boolean, index=True, default=False ) )
  42. Group.table = Table( "galaxy_group", metadata,
  43. Column( "id", Integer, primary_key=True ),
  44. Column( "create_time", DateTime, default=now ),
  45. Column( "update_time", DateTime, default=now, onupdate=now ),
  46. Column( "name", String( 255 ), index=True, unique=True ),
  47. Column( "deleted", Boolean, index=True, default=False ) )
  48. Role.table = Table( "role", metadata,
  49. Column( "id", Integer, primary_key=True ),
  50. Column( "create_time", DateTime, default=now ),
  51. Column( "update_time", DateTime, default=now, onupdate=now ),
  52. Column( "name", String( 255 ), index=True, unique=True ),
  53. Column( "description", TEXT ),
  54. Column( "type", String( 40 ), index=True ),
  55. Column( "deleted", Boolean, index=True, default=False ) )
  56. UserGroupAssociation.table = Table( "user_group_association", metadata,
  57. Column( "id", Integer, primary_key=True ),
  58. Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
  59. Column( "group_id", Integer, ForeignKey( "galaxy_group.id" ), index=True ),
  60. Column( "create_time", DateTime, default=now ),
  61. Column( "update_time", DateTime, default=now, onupdate=now ) )
  62. UserRoleAssociation.table = Table( "user_role_association", metadata,
  63. Column( "id", Integer, primary_key=True ),
  64. Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
  65. Column( "role_id", Integer, ForeignKey( "role.id" ), index=True ),
  66. Column( "create_time", DateTime, default=now ),
  67. Column( "update_time", DateTime, default=now, onupdate=now ) )
  68. GroupRoleAssociation.table = Table( "group_role_association", metadata,
  69. Column( "id", Integer, primary_key=True ),
  70. Column( "group_id", Integer, ForeignKey( "galaxy_group.id" ), index=True ),
  71. Column( "role_id", Integer, ForeignKey( "role.id" ), index=True ),
  72. Column( "create_time", DateTime, default=now ),
  73. Column( "update_time", DateTime, default=now, onupdate=now ) )
  74. GalaxySession.table = Table( "galaxy_session", metadata,
  75. Column( "id", Integer, primary_key=True ),
  76. Column( "create_time", DateTime, default=now ),
  77. Column( "update_time", DateTime, default=now, onupdate=now ),
  78. Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True, nullable=True ),
  79. Column( "remote_host", String( 255 ) ),
  80. Column( "remote_addr", String( 255 ) ),
  81. Column( "referer", TEXT ),
  82. Column( "session_key", TrimmedString( 255 ), index=True, unique=True ), # unique 128 bit random number coerced to a string
  83. Column( "is_valid", Boolean, default=False ),
  84. Column( "prev_session_id", Integer ) # saves a reference to the previous session so we have a way to chain them together
  85. )
  86. Repository.table = Table( "repository", metadata,
  87. Column( "id", Integer, primary_key=True ),
  88. Column( "create_time", DateTime, default=now ),
  89. Column( "update_time", DateTime, default=now, onupdate=now ),
  90. Column( "name", TrimmedString( 255 ), index=True ),
  91. Column( "description" , TEXT ),
  92. Column( "long_description" , TEXT ),
  93. Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
  94. Column( "private", Boolean, default=False ),
  95. Column( "deleted", Boolean, index=True, default=False ),
  96. Column( "email_alerts", JSONType, nullable=True ),
  97. Column( "times_downloaded", Integer ) )
  98. RepositoryMetadata.table = Table( "repository_metadata", metadata,
  99. Column( "id", Integer, primary_key=True ),
  100. Column( "create_time", DateTime, default=now ),
  101. Column( "update_time", DateTime, default=now, onupdate=now ),
  102. Column( "repository_id", Integer, ForeignKey( "repository.id" ), index=True ),
  103. Column( "changeset_revision", TrimmedString( 255 ), index=True ),
  104. Column( "metadata", JSONType, nullable=True ),
  105. Column( "malicious", Boolean, default=False ) )
  106. RepositoryRatingAssociation.table = Table( "repository_rating_association", metadata,
  107. Column( "id", Integer, primary_key=True ),
  108. Column( "create_time", DateTime, default=now ),
  109. Column( "update_time", DateTime, default=now, onupdate=now ),
  110. Column( "repository_id", Integer, ForeignKey( "repository.id" ), index=True ),
  111. Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
  112. Column( "rating", Integer, index=True ),
  113. Column( "comment", TEXT ) )
  114. RepositoryCategoryAssociation.table = Table( "repository_category_association", metadata,
  115. Column( "id", Integer, primary_key=True ),
  116. Column( "repository_id", Integer, ForeignKey( "repository.id" ), index=True ),
  117. Column( "category_id", Integer, ForeignKey( "category.id" ), index=True ) )
  118. Category.table = Table( "category", metadata,
  119. Column( "id", Integer, primary_key=True ),
  120. Column( "create_time", DateTime, default=now ),
  121. Column( "update_time", DateTime, default=now, onupdate=now ),
  122. Column( "name", TrimmedString( 255 ), index=True, unique=True ),
  123. Column( "description" , TEXT ),
  124. Column( "deleted", Boolean, index=True, default=False ) )
  125. Tag.table = Table( "tag", metadata,
  126. Column( "id", Integer, primary_key=True ),
  127. Column( "type", Integer ),
  128. Column( "parent_id", Integer, ForeignKey( "tag.id" ) ),
  129. Column( "name", TrimmedString(255) ),
  130. UniqueConstraint( "name" ) )
  131. # With the tables defined we can define the mappers and setup the
  132. # relationships between the model objects.
  133. assign_mapper( context, User, User.table,
  134. 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 ) ),
  135. galaxy_sessions=relation( GalaxySession, order_by=desc( GalaxySession.table.c.update_time ) ) ) )
  136. assign_mapper( context, Group, Group.table,
  137. properties=dict( users=relation( UserGroupAssociation ) ) )
  138. assign_mapper( context, Role, Role.table,
  139. properties=dict(
  140. users=relation( UserRoleAssociation ),
  141. groups=relation( GroupRoleAssociation ) ) )
  142. assign_mapper( context, UserGroupAssociation, UserGroupAssociation.table,
  143. properties=dict( user=relation( User, backref = "groups" ),
  144. group=relation( Group, backref = "members" ) ) )
  145. assign_mapper( context, UserRoleAssociation, UserRoleAssociation.table,
  146. properties=dict(
  147. user=relation( User, backref="roles" ),
  148. non_private_roles=relation( User,
  149. backref="non_private_roles",
  150. 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 ) ) ),
  151. role=relation( Role ) ) )
  152. assign_mapper( context, GroupRoleAssociation, GroupRoleAssociation.table,
  153. properties=dict(
  154. group=relation( Group, backref="roles" ),
  155. role=relation( Role ) ) )
  156. assign_mapper( context, GalaxySession, GalaxySession.table,
  157. properties=dict( user=relation( User.mapper ) ) )
  158. assign_mapper( context, Tag, Tag.table,
  159. properties=dict( children=relation(Tag, backref=backref( 'parent', remote_side=[Tag.table.c.id] ) ) ) )
  160. assign_mapper( context, Category, Category.table,
  161. properties=dict( repositories=relation( RepositoryCategoryAssociation ) ) )
  162. assign_mapper( context, Repository, Repository.table,
  163. properties = dict(
  164. categories=relation( RepositoryCategoryAssociation ),
  165. ratings=relation( RepositoryRatingAssociation, order_by=desc( RepositoryRatingAssociation.table.c.update_time ), backref="repositories" ),
  166. user=relation( User.mapper ),
  167. downloadable_revisions=relation( RepositoryMetadata, order_by=desc( RepositoryMetadata.table.c.id ) ) ) )
  168. assign_mapper( context, RepositoryMetadata, RepositoryMetadata.table,
  169. properties=dict( repository=relation( Repository ) ) )
  170. assign_mapper( context, RepositoryRatingAssociation, RepositoryRatingAssociation.table,
  171. properties=dict( repository=relation( Repository ), user=relation( User ) ) )
  172. assign_mapper( context, RepositoryCategoryAssociation, RepositoryCategoryAssociation.table,
  173. properties=dict(
  174. category=relation( Category ),
  175. repository=relation( Repository ) ) )
  176. def guess_dialect_for_url( url ):
  177. return (url.split(':', 1))[0]
  178. def load_egg_for_url( url ):
  179. # Load the appropriate db module
  180. dialect = guess_dialect_for_url( url )
  181. try:
  182. egg = dialect_to_egg[dialect]
  183. try:
  184. pkg_resources.require( egg )
  185. log.debug( "%s egg successfully loaded for %s dialect" % ( egg, dialect ) )
  186. except:
  187. # If the module's in the path elsewhere (i.e. non-egg), it'll still load.
  188. log.warning( "%s egg not found, but an attempt will be made to use %s anyway" % ( egg, dialect ) )
  189. except KeyError:
  190. # Let this go, it could possibly work with db's we don't support
  191. log.error( "database_connection contains an unknown SQLAlchemy database dialect: %s" % dialect )
  192. def init( file_path, url, engine_options={}, create_tables=False ):
  193. """Connect mappings to the database"""
  194. # Load the appropriate db module
  195. load_egg_for_url( url )
  196. # Create the database engine
  197. engine = create_engine( url, **engine_options )
  198. # Connect the metadata to the database.
  199. metadata.bind = engine
  200. # Clear any existing contextual sessions and reconfigure
  201. Session.remove()
  202. Session.configure( bind=engine )
  203. # Create tables if needed
  204. if create_tables:
  205. metadata.create_all()
  206. # Pack everything into a bunch
  207. result = Bunch( **globals() )
  208. result.engine = engine
  209. result.session = Session
  210. result.create_tables = create_tables
  211. #load local galaxy security policy
  212. result.security_agent = CommunityRBACAgent( result )
  213. return result