/lib/galaxy/webapps/community/model/migrate/versions/0001_initial_tables.py

https://bitbucket.org/cistrome/cistrome-harvard/ · Python · 154 lines · 127 code · 22 blank · 5 comment · 0 complexity · 132c44cb5a36b295ecf1767a800c6cda MD5 · raw file

  1. """
  2. Migration script to create initial tables.
  3. """
  4. from sqlalchemy import *
  5. from migrate import *
  6. import datetime
  7. now = datetime.datetime.utcnow
  8. # Need our custom types, but don't import anything else from model
  9. from galaxy.model.custom_types import *
  10. import sys, logging
  11. log = logging.getLogger( __name__ )
  12. log.setLevel(logging.DEBUG)
  13. handler = logging.StreamHandler( sys.stdout )
  14. format = "%(name)s %(levelname)s %(asctime)s %(message)s"
  15. formatter = logging.Formatter( format )
  16. handler.setFormatter( formatter )
  17. log.addHandler( handler )
  18. metadata = MetaData( migrate_engine )
  19. User_table = Table( "galaxy_user", metadata,
  20. Column( "id", Integer, primary_key=True),
  21. Column( "create_time", DateTime, default=now ),
  22. Column( "update_time", DateTime, default=now, onupdate=now ),
  23. Column( "email", TrimmedString( 255 ), nullable=False ),
  24. Column( "username", String( 255 ), index=True ),
  25. Column( "password", TrimmedString( 40 ), nullable=False ),
  26. Column( "external", Boolean, default=False ),
  27. Column( "deleted", Boolean, index=True, default=False ),
  28. Column( "purged", Boolean, index=True, default=False ) )
  29. Group_table = Table( "galaxy_group", metadata,
  30. Column( "id", Integer, primary_key=True ),
  31. Column( "create_time", DateTime, default=now ),
  32. Column( "update_time", DateTime, default=now, onupdate=now ),
  33. Column( "name", String( 255 ), index=True, unique=True ),
  34. Column( "deleted", Boolean, index=True, default=False ) )
  35. Role_table = Table( "role", metadata,
  36. Column( "id", Integer, primary_key=True ),
  37. Column( "create_time", DateTime, default=now ),
  38. Column( "update_time", DateTime, default=now, onupdate=now ),
  39. Column( "name", String( 255 ), index=True, unique=True ),
  40. Column( "description", TEXT ),
  41. Column( "type", String( 40 ), index=True ),
  42. Column( "deleted", Boolean, index=True, default=False ) )
  43. UserGroupAssociation_table = Table( "user_group_association", metadata,
  44. Column( "id", Integer, primary_key=True ),
  45. Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
  46. Column( "group_id", Integer, ForeignKey( "galaxy_group.id" ), index=True ),
  47. Column( "create_time", DateTime, default=now ),
  48. Column( "update_time", DateTime, default=now, onupdate=now ) )
  49. UserRoleAssociation_table = Table( "user_role_association", metadata,
  50. Column( "id", Integer, primary_key=True ),
  51. Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
  52. Column( "role_id", Integer, ForeignKey( "role.id" ), index=True ),
  53. Column( "create_time", DateTime, default=now ),
  54. Column( "update_time", DateTime, default=now, onupdate=now ) )
  55. GroupRoleAssociation_table = Table( "group_role_association", metadata,
  56. Column( "id", Integer, primary_key=True ),
  57. Column( "group_id", Integer, ForeignKey( "galaxy_group.id" ), index=True ),
  58. Column( "role_id", Integer, ForeignKey( "role.id" ), index=True ),
  59. Column( "create_time", DateTime, default=now ),
  60. Column( "update_time", DateTime, default=now, onupdate=now ) )
  61. GalaxySession_table = Table( "galaxy_session", metadata,
  62. Column( "id", Integer, primary_key=True ),
  63. Column( "create_time", DateTime, default=now ),
  64. Column( "update_time", DateTime, default=now, onupdate=now ),
  65. Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True, nullable=True ),
  66. Column( "remote_host", String( 255 ) ),
  67. Column( "remote_addr", String( 255 ) ),
  68. Column( "referer", TEXT ),
  69. Column( "session_key", TrimmedString( 255 ), index=True, unique=True ), # unique 128 bit random number coerced to a string
  70. Column( "is_valid", Boolean, default=False ),
  71. Column( "prev_session_id", Integer ) # saves a reference to the previous session so we have a way to chain them together
  72. )
  73. Tool_table = Table( "tool", metadata,
  74. Column( "id", Integer, primary_key=True ),
  75. Column( "guid", TrimmedString( 255 ), index=True, unique=True ),
  76. Column( "tool_id", TrimmedString( 255 ), index=True ),
  77. Column( "create_time", DateTime, default=now ),
  78. Column( "update_time", DateTime, default=now, onupdate=now ),
  79. Column( "newer_version_id", Integer, ForeignKey( "tool.id" ), nullable=True ),
  80. Column( "name", TrimmedString( 255 ), index=True ),
  81. Column( "description" , TEXT ),
  82. Column( "user_description" , TEXT ),
  83. Column( "version", TrimmedString( 255 ) ),
  84. Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
  85. Column( "external_filename" , TEXT ),
  86. Column( "deleted", Boolean, index=True, default=False ) )
  87. Event_table = Table( 'event', metadata,
  88. Column( "id", Integer, primary_key=True ),
  89. Column( "create_time", DateTime, default=now ),
  90. Column( "update_time", DateTime, default=now, onupdate=now ),
  91. Column( "state", TrimmedString( 255 ), index=True ),
  92. Column( "comment", TEXT ) )
  93. ToolEventAssociation_table = Table( "tool_event_association", metadata,
  94. Column( "id", Integer, primary_key=True ),
  95. Column( "tool_id", Integer, ForeignKey( "tool.id" ), index=True ),
  96. Column( "event_id", Integer, ForeignKey( "event.id" ), index=True ) )
  97. Category_table = Table( "category", metadata,
  98. Column( "id", Integer, primary_key=True ),
  99. Column( "create_time", DateTime, default=now ),
  100. Column( "update_time", DateTime, default=now, onupdate=now ),
  101. Column( "name", TrimmedString( 255 ), index=True, unique=True ),
  102. Column( "description" , TEXT ),
  103. Column( "deleted", Boolean, index=True, default=False ) )
  104. ToolCategoryAssociation_table = Table( "tool_category_association", metadata,
  105. Column( "id", Integer, primary_key=True ),
  106. Column( "tool_id", Integer, ForeignKey( "tool.id" ), index=True ),
  107. Column( "category_id", Integer, ForeignKey( "category.id" ), index=True ) )
  108. Tag_table = Table( "tag", metadata,
  109. Column( "id", Integer, primary_key=True ),
  110. Column( "type", Integer ),
  111. Column( "parent_id", Integer, ForeignKey( "tag.id" ) ),
  112. Column( "name", TrimmedString(255) ),
  113. UniqueConstraint( "name" ) )
  114. ToolTagAssociation_table = Table( "tool_tag_association", metadata,
  115. Column( "id", Integer, primary_key=True ),
  116. Column( "tool_id", Integer, ForeignKey( "tool.id" ), index=True ),
  117. Column( "tag_id", Integer, ForeignKey( "tag.id" ), index=True ),
  118. Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
  119. Column( "user_tname", TrimmedString(255), index=True),
  120. Column( "value", TrimmedString(255), index=True),
  121. Column( "user_value", TrimmedString(255), index=True) )
  122. ToolAnnotationAssociation_table = Table( "tool_annotation_association", metadata,
  123. Column( "id", Integer, primary_key=True ),
  124. Column( "tool_id", Integer, ForeignKey( "tool.id" ), index=True ),
  125. Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
  126. Column( "annotation", TEXT, index=True) )
  127. def upgrade():
  128. print __doc__
  129. metadata.create_all()
  130. def downgrade():
  131. # Operations to reverse the above upgrade go here.
  132. pass