Flesh out core forum schema.

diff --git a/migration/versions/002_Flesh_out_core_tables.py b/migration/versions/002_Flesh_out_core_tables.py
new file mode 100644 (file)
index 0000000..49c495f
--- /dev/null
+++ b/migration/versions/002_Flesh_out_core_tables.py
@@ -0,0 +1,58 @@
+from sqlalchemy import *
+from sqlalchemy.sql.expression import text
+from migrate import *
+import migrate.changeset
+
+from sqlalchemy.ext.declarative import declarative_base
+TableBase = declarative_base(bind=migrate_engine)
+
+
+class User(TableBase):
+    __tablename__ = 'users'
+    id = Column(Integer, primary_key=True, autoincrement=True, nullable=False)
+
+class Forum(TableBase):
+    __tablename__ = 'forums'
+    id = Column(Integer, primary_key=True, autoincrement=True, nullable=False)
+    name = Column(Unicode(133), nullable=False)
+
+class Thread(TableBase):
+    __tablename__ = 'threads'
+    id = Column(Integer, primary_key=True, autoincrement=True, nullable=False)
+    forum_id = Column(Integer, ForeignKey('forums.id'), nullable=False)
+    icon = Column(Unicode(32), nullable=True)
+    subject = Column(Unicode(133), nullable=False)
+    post_count = Column(Integer, nullable=False, server_default=text('0'))
+
+thread_post_count_idx = Index('ix_threads_post_count', Thread.post_count)
+
+class Post(TableBase):
+    __tablename__ = 'posts'
+    id = Column(Integer, primary_key=True, autoincrement=True, nullable=False)
+    thread_id = Column(Integer, ForeignKey('threads.id'), nullable=False)
+    position = Column(Integer, nullable=False, server_default=text('1'))
+    author_user_id = Column(Integer, ForeignKey('users.id'), nullable=False, server_default=text('1'))
+    posted_time = Column(DateTime, nullable=False, index=True)
+    content = Column(Unicode(5120), nullable=False)
+
+thread_position_idx = Index('thread_position', Post.thread_id, Post.position, unique=True)
+posted_time_idx = Index('ix_posts_posted_time', Post.posted_time)
+
+
+objects = [
+    Thread.__table__.c.icon,
+    Thread.__table__.c.post_count,
+    thread_post_count_idx,
+    Post.__table__.c.position,
+    Post.__table__.c.author_user_id,
+    thread_position_idx,
+    posted_time_idx,
+]
+
+def upgrade():
+    for obj in objects:
+        obj.create()
+
+def downgrade():
+    for obj in reversed(objects):
+        obj.drop()

diff --git a/splinext/forum/model/__init__.py b/splinext/forum/model/__init__.py
index 5ee242b..089a879 100644 (file)
--- a/splinext/forum/model/__init__.py
+++ b/splinext/forum/model/__init__.py
@@ -1,8 +1,12 @@
-from sqlalchemy import Column, ForeignKey
+from sqlalchemy import and_, Column, ForeignKey, Index
 from sqlalchemy.orm import relation
 from sqlalchemy.types import DateTime, Integer, Unicode
 
 from spline.model.meta import TableBase
+from splinext.users import model as users_model
+
+
+### Core
 
 class Forum(TableBase):
     __tablename__ = 'forums'
@@ -13,17 +17,39 @@ class Thread(TableBase):
     __tablename__ = 'threads'
     id = Column(Integer, primary_key=True, autoincrement=True, nullable=False)
     forum_id = Column(Integer, ForeignKey('forums.id'), nullable=False)
+    icon = Column(Unicode(32), nullable=True)
     subject = Column(Unicode(133), nullable=False)
+    post_count = Column(Integer, nullable=False, default=0, index=True)
+
+    def specific_post(self, position):
+        """Returns post number `position` in this thread.
+
+        Positions are one-indexed.  Negative indexes are allowed.
+        """
+
+        # Handle negative indexes
+        if position < 0:
+            position = self.post_count + position + 1
+
+        return self.posts.filter_by(position=position).one()
 
 class Post(TableBase):
     __tablename__ = 'posts'
     id = Column(Integer, primary_key=True, autoincrement=True, nullable=False)
     thread_id = Column(Integer, ForeignKey('threads.id'), nullable=False)
-    posted_time = Column(DateTime, nullable=False)
+    position = Column(Integer, nullable=False)
+    author_user_id = Column(Integer, ForeignKey('users.id'), nullable=False)
+    posted_time = Column(DateTime, nullable=False, index=True)
     content = Column(Unicode(5120), nullable=False)
 
+Index('thread_position', Post.thread_id, Post.position, unique=True)
+
 
 # XXX sort by time, how?
-Forum.threads = relation(Thread, order_by=Thread.id.desc(), backref='forum')
+Forum.threads = relation(Thread, order_by=Thread.id.desc(), lazy='dynamic', backref='forum')
+
+Thread.posts = relation(Post, order_by=Post.posted_time.desc(), lazy='dynamic', backref='thread')
+Thread.first_post = relation(Post, primaryjoin=and_(Post.thread_id == Thread.id, Post.position == 1), uselist=False)
+Thread.last_post = relation(Post, primaryjoin=and_(Post.thread_id == Thread.id, Post.position == Thread.post_count), uselist=False)
 
-Thread.posts = relation(Post, order_by=Post.posted_time.desc(), backref='thread')
+Post.author = relation(users_model.User, backref='posts')