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()
   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 def upgrade(migrate_engine):
  43     TableBase.metadata.bind = migrate_engine
  44
  45     Thread.__table__.c.icon.create()
  46     Thread.__table__.c.post_count.create()
  47     thread_post_count_idx.create(bind=migrate_engine)
  48     Post.__table__.c.position.create()
  49     Post.__table__.c.author_user_id.create()
  50     thread_position_idx.create(bind=migrate_engine)
  51     posted_time_idx.create(bind=migrate_engine)
  52
  53 def downgrade(migrate_engine):
  54     TableBase.metadata.bind = migrate_engine
  55
  56     posted_time_idx.drop(bind=migrate_engine)
  57     thread_position_idx.drop(bind=migrate_engine)
  58     Post.__table__.c.author_user_id.drop()
  59     Post.__table__.c.position.drop()
  60     thread_post_count_idx.drop(bind=migrate_engine)
  61     Thread.__table__.c.post_count.drop()
  62     Thread.__table__.c.icon.drop()