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

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