migration/versions/002_Flesh_out_core_tables.py

   1 from sqlalchemy import *
   2 from sqlalchemy.sql.expression import text
   3 from migrate import *
   4 import migrate.changeset
   5
   6 from sqlalchemy.ext.declarative import declarative_base
   7 TableBase = declarative_base(bind=migrate_engine)
   8
   9
  10 class User(TableBase):
  11     __tablename__ = 'users'
  12     id = Column(Integer, primary_key=True, autoincrement=True, nullable=False)
  13
  14 class Forum(TableBase):
  15     __tablename__ = 'forums'
  16     id = Column(Integer, primary_key=True, autoincrement=True, nullable=False)
  17     name = Column(Unicode(133), nullable=False)
  18
  19 class Thread(TableBase):
  20     __tablename__ = 'threads'
  21     id = Column(Integer, primary_key=True, autoincrement=True, nullable=False)
  22     forum_id = Column(Integer, ForeignKey('forums.id'), nullable=False)
  23     icon = Column(Unicode(32), nullable=True)
  24     subject = Column(Unicode(133), nullable=False)
  25     post_count = Column(Integer, nullable=False, server_default=text('0'))
  26
  27 thread_post_count_idx = Index('ix_threads_post_count', Thread.post_count)
  28
  29 class Post(TableBase):
  30     __tablename__ = 'posts'
  31     id = Column(Integer, primary_key=True, autoincrement=True, nullable=False)
  32     thread_id = Column(Integer, ForeignKey('threads.id'), nullable=False)
  33     position = Column(Integer, nullable=False, server_default=text('1'))
  34     author_user_id = Column(Integer, ForeignKey('users.id'), nullable=False, server_default=text('1'))
  35     posted_time = Column(DateTime, nullable=False, index=True)
  36     content = Column(Unicode(5120), nullable=False)
  37
  38 thread_position_idx = Index('thread_position', Post.thread_id, Post.position, unique=True)
  39 posted_time_idx = Index('ix_posts_posted_time', Post.posted_time)
  40
  41
  42 objects = [
  43     Thread.__table__.c.icon,
  44     Thread.__table__.c.post_count,
  45     thread_post_count_idx,
  46     Post.__table__.c.position,
  47     Post.__table__.c.author_user_id,
  48     thread_position_idx,
  49     posted_time_idx,
  50 ]
  51
  52 def upgrade():
  53     for obj in objects:
  54         obj.create()
  55
  56 def downgrade():
  57     for obj in reversed(objects):
  58         obj.drop()