begin db refactor

7 files changed, 428 insertions, 253 deletions

diff --git a/apioforum/__init__.py b/apioforum/__init__.py
index 087df81..8300948 100644
--- a/apioforum/__init__.py
+++ b/apioforum/__init__.py
@@ -22,6 +22,8 @@ def create_app():
 
     from . import db
     db.init_app(app)
+    app.url_map.converters['db'] = db.DbConverter
+
     from . import permissions
     permissions.init_app(app)
 
diff --git a/apioforum/db.py b/apioforum/db.py
index c0c8c7e..e151cd4 100644
--- a/apioforum/db.py
+++ b/apioforum/db.py
@@ -1,7 +1,10 @@
 import sqlite3
 import click
-from flask import current_app, g
+from flask import current_app, g, abort
 from flask.cli import with_appcontext
+from werkzeug.routing import BaseConverter
+
+from db_migrations import migrations
 
 def get_db():
     if 'db' not in g:
@@ -18,197 +21,6 @@ def close_db(e=None):
     if db is not None:
         db.close()
 
-migrations = [
-"""
-CREATE TABLE users (
-    username TEXT PRIMARY KEY,
-    password TEXT NOT NULL
-);""",
-"""
-CREATE TABLE threads (
-    id INTEGER PRIMARY KEY,
-    title TEXT NOT NULL,
-    creator TEXT NOT NULL REFERENCES users(username),
-    created TIMESTAMP NOT NULL,
-    updated TIMESTAMP NOT NULL
-);
-CREATE TABLE posts (
-    id INTEGER PRIMARY KEY,
-    content TEXT,
-    thread INTEGER NOT NULL REFERENCES threads(id),
-    author TEXT NOT NULL REFERENCES users(username),
-    created TIMESTAMP NOT NULL
-);
-
-CREATE INDEX posts_thread_idx ON posts (thread);
-""",
-"""
-ALTER TABLE posts ADD COLUMN edited INT NOT NULL DEFAULT 0;
-ALTER TABLE posts ADD COLUMN updated TIMESTAMP;
-""",
-"""
-CREATE VIRTUAL TABLE posts_fts USING fts5(
-    content,
-    content=posts,
-    content_rowid=id,
-    tokenize='porter unicode61 remove_diacritics 2'
-);
-INSERT INTO posts_fts (rowid, content) SELECT id, content FROM posts;
-
-CREATE TRIGGER posts_ai AFTER INSERT ON posts BEGIN
-    INSERT INTO posts_fts(rowid, content) VALUES (new.id, new.content);
-END;
-CREATE TRIGGER posts_ad AFTER DELETE ON posts BEGIN
-    INSERT INTO posts_fts(posts_fts, rowid, content) VALUES('delete', old.id, old.content);
-END;
-CREATE TRIGGER posts_au AFTER UPDATE ON posts BEGIN
-    INSERT INTO posts_fts(posts_fts, rowid, content) VALUES('delete', old.id, old.content);
-    INSERT INTO posts_fts(rowid, content) VALUES (new.id, new.content);
-END;
-""",
-"""
-CREATE TABLE tags (
-    id INTEGER PRIMARY KEY,
-    name TEXT NOT NULL,
-    text_colour TEXT NOT NULL,
-    bg_colour TEXT NOT NULL
-);
-CREATE TABLE thread_tags (
-    thread INTEGER NOT NULL REFERENCES threads(id),
-    tag INTEGER NOT NULL REFERENCES tags(id)
-);
-""",
-"""CREATE INDEX thread_tags_thread ON thread_tags (thread);""",
-"""ALTER TABLE users ADD COLUMN admin INT NOT NULL DEFAULT 0""",
-"""
-ALTER TABLE users ADD COLUMN bio TEXT;
-ALTER TABLE users ADD COLUMN joined TIMESTAMP;
-""",
-"""
-CREATE TABLE polls (
-    id INTEGER PRIMARY KEY,
-    title TEXT NOT NULL
-);
-ALTER TABLE threads ADD COLUMN poll INTEGER REFERENCES polls(id);
-
-CREATE TABLE poll_options (
-    poll INTEGER NOT NULL REFERENCES polls(id),
-    text TEXT NOT NULL,
-    option_idx INTEGER NOT NULL,
-    PRIMARY KEY ( poll, option_idx )
-);
-
-CREATE TABLE votes (
-    id INTEGER PRIMARY KEY,
-    user TEXT NOT NULL REFERENCES users(username),
-    poll INTEGER NOT NULL,
-    option_idx INTEGER,
-    time TIMESTAMP NOT NULL,
-    current INTEGER NOT NULL,
-    is_retraction INTEGER,
-    CHECK (is_retraction OR (option_idx NOT NULL)),
-    FOREIGN KEY ( poll, option_idx ) REFERENCES poll_options(poll, option_idx)
-);
-ALTER TABLE posts ADD COLUMN vote INTEGER REFERENCES votes(id);
-""",
-"""
-CREATE VIEW vote_counts AS
-    SELECT poll, option_idx, count(*) AS num FROM votes WHERE current GROUP BY option_idx,poll; 
-""",
-"""
-CREATE TABLE forums (
-    id INTEGER PRIMARY KEY,
-    name TEXT NOT NULL,
-    parent INTEGER REFERENCES forums(id),
-    description TEXT
-);
-INSERT INTO forums (name,parent,description) values ('apioforum',null,
-        'welcome to the apioforum\n\n' ||
-        'forum rules: do not be a bad person. do not do bad things.');
-
-PRAGMA foreign_keys = off;
-BEGIN TRANSACTION;
-CREATE TABLE threads_new (
-    id INTEGER PRIMARY KEY,
-    title TEXT NOT NULL,
-    creator TEXT NOT NULL,
-    created TIMESTAMP NOT NULL,
-    updated TIMESTAMP NOT NULL,
-    forum NOT NULL REFERENCES forums(id),
-    poll INTEGER REFERENCES polls(id)
-);
-INSERT INTO threads_new (id,title,creator,created,updated,forum)
-    SELECT id,title,creator,created,updated,1 FROM threads;
-DROP TABLE threads;
-ALTER TABLE threads_new RENAME TO threads;
-COMMIT;
-PRAGMA foreign_keys = on;
-""",
-"""
-CREATE VIEW most_recent_posts AS
-    SELECT max(id), * FROM posts GROUP BY thread;
-
-CREATE VIEW number_of_posts AS
-    SELECT thread, count(*) AS num_replies FROM posts GROUP BY thread;
-""",
-"""
-CREATE VIEW total_vote_counts AS
-    SELECT poll, count(*) AS total_votes FROM votes WHERE current AND NOT is_retraction GROUP BY poll;
-""",
-"""
-PRAGMA foreign_keys = off;
-BEGIN TRANSACTION;
-CREATE TABLE tags_new (
-	id INTEGER PRIMARY KEY,
-	name TEXT NOT NULL,
-	text_colour TEXT NOT NULL,
-	bg_colour TEXT NOT NULL,
-	forum INTEGER NOT NULL REFERENCES forums(id)
-);
-INSERT INTO tags_new (id,name,text_colour,bg_colour,forum)
-	SELECT id,name,text_colour,bg_colour,1 FROM tags;
-DROP TABLE tags;
-ALTER TABLE tags_new RENAME TO tags;
-PRAGMA foreign_keys = on;
-""",
-"""
-CREATE TABLE role_config (
-    role TEXT NOT NULL,
-    forum NOT NULL REFERENCES forums(id),
-    id INTEGER PRIMARY KEY,
-
-    p_create_threads INT NOT NULL DEFAULT 1,
-    p_reply_threads INT NOT NULL DEFAULT 1,
-    p_view_threads INT NOT NULL DEFAULT 1,
-    p_manage_threads INT NOT NULL DEFAULT 0,
-    p_delete_posts INT NOT NULL DEFAULT 0,
-    p_vote INT NOT NULL DEFAULT 1,
-    p_create_polls INT NOT NULL DEFAULT 1,
-    p_approve INT NOT NULL DEFAULT 0,
-    p_create_subforum INT NOT NULL DEFAULT 0
-);
-
-INSERT INTO role_config (role,forum) VALUES ("approved",1);
-INSERT INTO role_config (role,forum) VALUES ("other",1);
-""",
-"""
-CREATE TABLE role_assignments (
-    user NOT NULL REFERENCES users(username),
-    forum NOT NULL REFERENCES forums(id),
-    role TEXT NOT NULL
-);
-""",
-"""
-ALTER TABLE posts ADD COLUMN deleted NOT NULL DEFAULT 0;
-""",
-"""
-ALTER TABLE forums ADD COLUMN unlisted NOT NULL DEFAULT 0;
-""",
-"""
-ALTER TABLE role_config ADD COLUMN p_view_forum INT NOT NULL DEFAULT 1;
-"""
-]
-
 def init_db():
     db = get_db()
     version = db.execute("PRAGMA user_version;").fetchone()[0]
@@ -229,3 +41,130 @@ def init_app(app):
     app.teardown_appcontext(close_db)
     app.cli.add_command(migrate_command)
 
+
+class DbWrapper:
+    table = None
+    primary_key = "id"
+    
+    # column name -> DbWrapper child class
+    # this allows the DbWrapper to automatically fetch the referenced object
+    references = {}
+
+    @classmethod
+    def get_row(cls, key):
+        return get_db().execute(
+            f"SELECT * FROM {cls.table} WHERE {cls.primary_key} = ?", (key,))\
+                .fetchone()
+
+    @classmethod
+    def fetch(cls, key):
+        row = cls.get_row(key)
+        if row == None: raise KeyError(key)
+        return cls(row)
+
+    @classmethod
+    def query_some(cls, *args, **kwargs):
+        rows = get_db().execute(*args, **kwargs).fetchall()
+        for row in rows:
+            yield cls(row)
+
+    @classmethod
+    def query(cls, *args, **kwargs):
+        return(next(cls.query_some(*args, **kwargs)))
+
+    def __init__(self, row):
+        self._row = row
+        if self.__class__.primary_key:
+            self._key = row[self.__class__.primary_key]
+        else:
+            self._key = None
+
+    def __getattr__(self, attr):
+        # special attributes are retrieved from the object itself
+        if attr[0] == '_':
+            if not attr in self.__dict__:
+                raise AttributeError()
+            return self.__dict__[attr]
+
+        # changes have been made to the row. fetch it again
+        if self._row == None and self._key != None:
+            self._row = self.__class__.get_row(self._key)
+
+        # if this column is a reference, fetch the referenced row as an object
+        r = self.__class__.references.get(attr, None)
+        if r != None:
+            # do not fetch it more than once
+            if not attr in self.__dict__:
+                self.__dict__[attr] = r.fetch(self._row[attr])
+            return self.__dict__[attr]
+
+        try:
+            return self._row[attr]
+        except KeyError as k:
+            raise AttributeError() from k
+
+    def __setattr__(self, attr, value):
+        if not self.__class__.primary_key:
+            raise(RuntimeError('cannot set attributes on this object'))
+
+        # special attributes are set on the object itself
+        if attr[0] == '_':
+            self.__dict__[attr] = value
+            return
+
+        cls = self.__class__
+
+        if not isinstance(value, DbWrapper):
+            v = value
+        else:
+            v = value._key
+
+        print(f"UPDATE {cls.table} SET {attr} = ? WHERE {cls.primary_key} = ?")
+
+        get_db().execute(
+            f"UPDATE {cls.table} SET {attr} = ? WHERE {cls.primary_key} = ?",
+                (v, self._key))
+
+        # the fetched row is invalidated.
+        # avoid extra queries by querying again only if attributes are accessed
+        self._row = None
+
+    def __eq__(self, other):
+        if self.__class__.primary_key:
+            if isinstance(other, self.__class__):
+                # rows with keys are equivalent if their keys are
+                return self.__class__.table == other.__class__.table\
+                        and self._key == other._key
+            else:
+                # a row can be compared with its key
+                return self._key == other
+        else:
+            return self._row == other._row
+
+    def __conform__(self, protocol):
+        # if used in a database query, convert to database key
+        if protocol is sqlite3.PrepareProtocol:
+            return self._key
+
+# flask path converter
+class DbConverter(BaseConverter):
+    def __init__(self, m, db_class, abort=True):
+        super(DbConverter, self).__init__(m)
+        self.db_class = db_class
+        self.abort = abort
+    
+    def to_python(self, value):
+        try:
+            return self.db_class.fetch(value)
+        except KeyError:
+            if self.abort:
+                abort(404)
+            else:
+                return None
+
+    def to_url(self, value):
+        if isinstance(value, self.db_class):
+            return str(value._key)
+        else:
+            return str(value)
+
diff --git a/apioforum/db_migrations.py b/apioforum/db_migrations.py
new file mode 100644
index 0000000..fc6e22a
--- /dev/null
+++ b/apioforum/db_migrations.py
@@ -0,0 +1,190 @@
+migrations = [
+"""
+CREATE TABLE users (
+    username TEXT PRIMARY KEY,
+    password TEXT NOT NULL
+);""",
+"""
+CREATE TABLE threads (
+    id INTEGER PRIMARY KEY,
+    title TEXT NOT NULL,
+    creator TEXT NOT NULL REFERENCES users(username),
+    created TIMESTAMP NOT NULL,
+    updated TIMESTAMP NOT NULL
+);
+CREATE TABLE posts (
+    id INTEGER PRIMARY KEY,
+    content TEXT,
+    thread INTEGER NOT NULL REFERENCES threads(id),
+    author TEXT NOT NULL REFERENCES users(username),
+    created TIMESTAMP NOT NULL
+);
+
+CREATE INDEX posts_thread_idx ON posts (thread);
+""",
+"""
+ALTER TABLE posts ADD COLUMN edited INT NOT NULL DEFAULT 0;
+ALTER TABLE posts ADD COLUMN updated TIMESTAMP;
+""",
+"""
+CREATE VIRTUAL TABLE posts_fts USING fts5(
+    content,
+    content=posts,
+    content_rowid=id,
+    tokenize='porter unicode61 remove_diacritics 2'
+);
+INSERT INTO posts_fts (rowid, content) SELECT id, content FROM posts;
+
+CREATE TRIGGER posts_ai AFTER INSERT ON posts BEGIN
+    INSERT INTO posts_fts(rowid, content) VALUES (new.id, new.content);
+END;
+CREATE TRIGGER posts_ad AFTER DELETE ON posts BEGIN
+    INSERT INTO posts_fts(posts_fts, rowid, content) VALUES('delete', old.id, old.content);
+END;
+CREATE TRIGGER posts_au AFTER UPDATE ON posts BEGIN
+    INSERT INTO posts_fts(posts_fts, rowid, content) VALUES('delete', old.id, old.content);
+    INSERT INTO posts_fts(rowid, content) VALUES (new.id, new.content);
+END;
+""",
+"""
+CREATE TABLE tags (
+    id INTEGER PRIMARY KEY,
+    name TEXT NOT NULL,
+    text_colour TEXT NOT NULL,
+    bg_colour TEXT NOT NULL
+);
+CREATE TABLE thread_tags (
+    thread INTEGER NOT NULL REFERENCES threads(id),
+    tag INTEGER NOT NULL REFERENCES tags(id)
+);
+""",
+"""CREATE INDEX thread_tags_thread ON thread_tags (thread);""",
+"""ALTER TABLE users ADD COLUMN admin INT NOT NULL DEFAULT 0""",
+"""
+ALTER TABLE users ADD COLUMN bio TEXT;
+ALTER TABLE users ADD COLUMN joined TIMESTAMP;
+""",
+"""
+CREATE TABLE polls (
+    id INTEGER PRIMARY KEY,
+    title TEXT NOT NULL
+);
+ALTER TABLE threads ADD COLUMN poll INTEGER REFERENCES polls(id);
+
+CREATE TABLE poll_options (
+    poll INTEGER NOT NULL REFERENCES polls(id),
+    text TEXT NOT NULL,
+    option_idx INTEGER NOT NULL,
+    PRIMARY KEY ( poll, option_idx )
+);
+
+CREATE TABLE votes (
+    id INTEGER PRIMARY KEY,
+    user TEXT NOT NULL REFERENCES users(username),
+    poll INTEGER NOT NULL,
+    option_idx INTEGER,
+    time TIMESTAMP NOT NULL,
+    current INTEGER NOT NULL,
+    is_retraction INTEGER,
+    CHECK (is_retraction OR (option_idx NOT NULL)),
+    FOREIGN KEY ( poll, option_idx ) REFERENCES poll_options(poll, option_idx)
+);
+ALTER TABLE posts ADD COLUMN vote INTEGER REFERENCES votes(id);
+""",
+"""
+CREATE VIEW vote_counts AS
+    SELECT poll, option_idx, count(*) AS num FROM votes WHERE current GROUP BY option_idx,poll; 
+""",
+"""
+CREATE TABLE forums (
+    id INTEGER PRIMARY KEY,
+    name TEXT NOT NULL,
+    parent INTEGER REFERENCES forums(id),
+    description TEXT
+);
+INSERT INTO forums (name,parent,description) values ('apioforum',null,
+        'welcome to the apioforum\n\n' ||
+        'forum rules: do not be a bad person. do not do bad things.');
+
+PRAGMA foreign_keys = off;
+BEGIN TRANSACTION;
+CREATE TABLE threads_new (
+    id INTEGER PRIMARY KEY,
+    title TEXT NOT NULL,
+    creator TEXT NOT NULL,
+    created TIMESTAMP NOT NULL,
+    updated TIMESTAMP NOT NULL,
+    forum NOT NULL REFERENCES forums(id),
+    poll INTEGER REFERENCES polls(id)
+);
+INSERT INTO threads_new (id,title,creator,created,updated,forum)
+    SELECT id,title,creator,created,updated,1 FROM threads;
+DROP TABLE threads;
+ALTER TABLE threads_new RENAME TO threads;
+COMMIT;
+PRAGMA foreign_keys = on;
+""",
+"""
+CREATE VIEW most_recent_posts AS
+    SELECT max(id), * FROM posts GROUP BY thread;
+
+CREATE VIEW number_of_posts AS
+    SELECT thread, count(*) AS num_replies FROM posts GROUP BY thread;
+""",
+"""
+CREATE VIEW total_vote_counts AS
+    SELECT poll, count(*) AS total_votes FROM votes WHERE current AND NOT is_retraction GROUP BY poll;
+""",
+"""
+PRAGMA foreign_keys = off;
+BEGIN TRANSACTION;
+CREATE TABLE tags_new (
+	id INTEGER PRIMARY KEY,
+	name TEXT NOT NULL,
+	text_colour TEXT NOT NULL,
+	bg_colour TEXT NOT NULL,
+	forum INTEGER NOT NULL REFERENCES forums(id)
+);
+INSERT INTO tags_new (id,name,text_colour,bg_colour,forum)
+	SELECT id,name,text_colour,bg_colour,1 FROM tags;
+DROP TABLE tags;
+ALTER TABLE tags_new RENAME TO tags;
+PRAGMA foreign_keys = on;
+""",
+"""
+CREATE TABLE role_config (
+    role TEXT NOT NULL,
+    forum NOT NULL REFERENCES forums(id),
+    id INTEGER PRIMARY KEY,
+
+    p_create_threads INT NOT NULL DEFAULT 1,
+    p_reply_threads INT NOT NULL DEFAULT 1,
+    p_view_threads INT NOT NULL DEFAULT 1,
+    p_manage_threads INT NOT NULL DEFAULT 0,
+    p_delete_posts INT NOT NULL DEFAULT 0,
+    p_vote INT NOT NULL DEFAULT 1,
+    p_create_polls INT NOT NULL DEFAULT 1,
+    p_approve INT NOT NULL DEFAULT 0,
+    p_create_subforum INT NOT NULL DEFAULT 0
+);
+
+INSERT INTO role_config (role,forum) VALUES ("approved",1);
+INSERT INTO role_config (role,forum) VALUES ("other",1);
+""",
+"""
+CREATE TABLE role_assignments (
+    user NOT NULL REFERENCES users(username),
+    forum NOT NULL REFERENCES forums(id),
+    role TEXT NOT NULL
+);
+""",
+"""
+ALTER TABLE posts ADD COLUMN deleted NOT NULL DEFAULT 0;
+""",
+"""
+ALTER TABLE forums ADD COLUMN unlisted NOT NULL DEFAULT 0;
+""",
+"""
+ALTER TABLE role_config ADD COLUMN p_view_forum INT NOT NULL DEFAULT 1;
+"""
+]
diff --git a/apioforum/forum.py b/apioforum/forum.py
index 2931df9..badbd14 100644
--- a/apioforum/forum.py
+++ b/apioforum/forum.py
@@ -17,6 +17,12 @@ import functools
 
 bp = Blueprint("forum", __name__, url_prefix="/")
 
+class Forum(DbWrapper):
+    table = "forums"
+    primary_key = "id"
+    references = {"parent", Forum}
+
+
 @bp.route("/")
 def not_actual_index():
     return redirect("/1")
diff --git a/apioforum/templates/view_thread.html b/apioforum/templates/view_thread.html
index 132fd44..06c110b 100644
--- a/apioforum/templates/view_thread.html
+++ b/apioforum/templates/view_thread.html
@@ -6,14 +6,14 @@
 {% endblock %}
 
 {%block content%}
-{% if poll %}
-<p>{{poll.title}}</p>
+{% if thread.poll %}
+<p>{{thread.poll.title}}</p>
 <ol>
-    {%for opt in poll.options%}
+    {%for opt in thread.poll.options%}
     <li value="{{opt.option_idx}}"><i>{{opt.text}}</i>: {{opt.num or 0}} votes</li>
     {%endfor%}
 </ol>
-{{ vote_meter(poll) }}
+{{ vote_meter(thread.poll) }}
 {% endif %}
 <div class="thread-top-bar">
     <span class="thread-top-bar-a">
@@ -34,7 +34,7 @@
 
 <div class="posts">
     {% for post in posts %}
-        {% if votes[post.id] %}
+        {% if post.vote %}
 
             {% set vote = votes[post.id] %}        
             {% set option_idx = vote.option_idx %}
@@ -48,7 +48,7 @@
 						this post retracted a vote
 					{% endif %}
                 {% else %}
-                    {% set option = poll.options[option_idx-1] %}
+                    {% set option = thread.poll.options[option_idx-1] %}
                     {% if vote.current %}
                         {{post.author}} votes for {{option_idx}}: {{option.text}}
                     {% else %}
@@ -72,9 +72,9 @@
 {% if g.user and has_permission(thread.forum, g.user, "p_reply_threads") %}
 <form class="new-post" action="{{url_for('thread.create_post',thread_id=thread.id)}}" method="POST">
     <textarea class="new-post-box" placeholder="your post here..." name="content"></textarea>
-    {% if poll and has_permission(thread.forum, g.user, "p_vote") %}
+    {% if thread.poll and has_permission(thread.forum, g.user, "p_vote") %}
     <fieldset>
-        <legend>poll: {{poll.title}}</legend>
+        <legend>poll: {{thread.poll.title}}</legend>
         <p>if you want, you can submit a vote along with this post. if you have previously voted
            on this poll, your previous vote will be changed</p>
 
@@ -87,7 +87,7 @@
         <label for="retractvote">clear current vote</label>
         {% endif %}
 
-        {% for opt in poll.options %}
+        {% for opt in thread.poll.options %}
             <br>
             <input type="radio" id="option_{{opt.option_idx}}" name="poll" value="{{opt.option_idx}}">
             <label for="option_{{opt.option_idx}}">#{{opt.option_idx}} - {{opt.text}}</label>
diff --git a/apioforum/thread.py b/apioforum/thread.py
index 2fc9dca..d0d7e83 100644
--- a/apioforum/thread.py
+++ b/apioforum/thread.py
@@ -6,41 +6,26 @@ from flask import (
     Blueprint, render_template, abort, request, g, redirect,
     url_for, flash, jsonify
 )
-from .db import get_db
+from .db import get_db, DbWrapper
 from .roles import has_permission
-from .forum import get_avail_tags
+from .forum import get_avail_tags, Forum
+from .user import User
 
 bp = Blueprint("thread", __name__, url_prefix="/thread")
 
-def post_jump(thread_id, post_id):
-    return url_for("thread.view_thread",thread_id=thread_id)+"#post_"+str(post_id)
+class Poll(DbWrapper):
+    table = "polls"
 
-@bp.route("/<int:thread_id>")
-def view_thread(thread_id):
-    db = get_db()
-    thread = db.execute("SELECT * FROM threads WHERE id = ?;",(thread_id,)).fetchone()
-    if thread is None:
-        abort(404)
-    if not has_permission(thread['forum'], g.user, "p_view_threads", False):
-        abort(403)
-    posts = db.execute("""
-        SELECT * FROM posts
-        WHERE posts.thread = ?
-        ORDER BY created ASC;
-        """,(thread_id,)).fetchall()
-    tags = db.execute(
-        """SELECT tags.* FROM tags
-        INNER JOIN thread_tags ON thread_tags.tag = tags.id
-        WHERE thread_tags.thread = ?
-        ORDER BY tags.id""",(thread_id,)).fetchall()
-    poll = None
-    votes = None
-    if thread['poll'] is not None:
-        poll_row= db.execute("""
+    @classmethod
+    def get_row(cls, key):
+        db = get_db()
+        row = db.execute("""
             SELECT polls.*,total_vote_counts.total_votes FROM polls
             LEFT OUTER JOIN total_vote_counts ON polls.id = total_vote_counts.poll
             WHERE polls.id = ?;                
-            """,(thread['poll'],)).fetchone()
+            """,(key,)).fetchone()
+        if row == None:
+            return None
         options = db.execute("""
             SELECT poll_options.*, vote_counts.num
             FROM poll_options
@@ -48,20 +33,66 @@ def view_thread(thread_id):
                                         AND poll_options.option_idx = vote_counts.option_idx 
             WHERE poll_options.poll = ?
             ORDER BY option_idx asc;
-            """,(poll_row['id'],)).fetchall()
-        poll = {}
-        poll.update(poll_row)
-        poll['options'] = options
-        votes = {}
-        # todo: optimise this somehow
-        for post in posts:
-            if post['vote'] is not None:
-                votes[post['id']] = db.execute("SELECT * FROM votes WHERE id = ?",(post['vote'],)).fetchone()
-
-    if g.user is None or poll is None:
+            """,(key,)).fetchall()
+        row['options'] = options
+
+class PollOption(DbWrapper):
+    table = "poll_options"
+    primary_key = None
+    references = {"poll": Poll}
+
+class Vote(DbWrapper):
+    table = "votes"
+    references = {"poll": Poll, "user": User}
+
+class Tag(DbWrapper):
+    table = "tags"
+    references = {"forum": Forum}
+
+class Thread(DbWrapper):
+    table = "threads"
+    references = {"forum": Forum, "poll": Poll}
+
+    def get_posts(self):
+        return Post.query_some("""
+            SELECT * FROM posts
+            WHERE thread = ?
+            ORDER BY created ASC;
+            """,(self,))
+
+    def get_tags(self):
+        return Tag.query_some("""
+            SELECT tags.* FROM tags
+            INNER JOIN thread_tags ON thread_tags.tag = tags.id
+            ORDER BY tags.id
+            """,(self,))
+
+class Post(DbWrapper):
+    table = "posts"
+    references = {"thread": Thread, "author": User, "vote": Vote}
+
+
+def post_jump(thread_id, post_id):
+    return url_for("thread.view_thread",thread_id=thread_id)+"#post_"+str(post_id)
+
+@bp.route("/<db(Thread):thread>")
+def view_thread(thread):
+    db = get_db()
+    if not has_permission(thread['forum'], g.user, "p_view_threads", False):
+        abort(403)
+    posts = thread.get_posts()
+    tags = thread.get_tags()
+
+    if g.user is None or thread.poll is None:
         has_voted = None
     else:
-        v = db.execute("SELECT * FROM votes WHERE poll = ? AND user = ? AND current AND NOT is_retraction;",(poll['id'],g.user)).fetchone()
+        v = Vote.query_one("""
+            SELECT * FROM votes 
+            WHERE poll = ? 
+                AND user = ? 
+                AND current 
+                AND NOT is_retraction;
+            """,(thread.poll,g.user))
         has_voted = v is not None
         
     return render_template(
@@ -70,7 +101,6 @@ def view_thread(thread_id):
         thread=thread,
         tags=tags,
         poll=poll,
-        votes=votes,
         has_voted=has_voted,
     )
 
diff --git a/apioforum/user.py b/apioforum/user.py
index bbdd060..b92d8b3 100644
--- a/apioforum/user.py
+++ b/apioforum/user.py
@@ -5,51 +5,59 @@ from flask import (
 )
 
 from werkzeug.security import check_password_hash, generate_password_hash
-from .db import get_db
+from .db import DbWrapper, get_db
 
 bp = Blueprint("user", __name__, url_prefix="/user")
 
+class User(DbWrapper):
+    table = "users"
+    primary_key = "username"
+    
+    def set_password(self, password):
+        self.password = generate_password_hash(password)
+
 
 @bp.route("/<username>")
 def view_user(username):
     db = get_db()
-    user = db.execute("SELECT * FROM users WHERE username = ?;",(username,)).fetchone()
-    if user is None:
+    try:
+        user = User.fetch(username)
+    except KeyError:
         abort(404)
     posts = db.execute("""
         SELECT * FROM posts
         WHERE author = ? AND deleted = 0
         ORDER BY created DESC 
-        LIMIT 25;""",(username,)).fetchall()
+        LIMIT 25;""",(user,)).fetchall()
     return render_template("view_user.html", user=user, posts=posts)
 
 @bp.route("/<username>/edit", methods=["GET","POST"])
 def edit_user(username):
-    db = get_db()
-    user = db.execute("SELECT * FROM users WHERE username = ?;",(username,)).fetchone()
-    if user is None:
+    try:
+        user = User.fetch(username)
+    except KeyError:
         abort(404)
     if username != g.user:
         flash("you cannot modify other people")
         return redirect(url_for("user.view_user",username=username))
 
+    db = get_db()
     if request.method == "POST":
         err = []
         if len(request.form['new_password']) > 0:
-            if not check_password_hash(user['password'],request.form['password']):
+            if not check_password_hash(user.password,request.form['password']):
                 err.append("entered password does not match current password")
             else:
-                db.execute("update users set password = ? where username = ?", 
-                        (generate_password_hash(request.form["new_password"]), username))
+                user.set_password(request.form['new_password'])
                 db.commit()
                 flash("password changed changefully")
-        if request.form['bio'] != user['bio']:
+        if request.form['bio'] != user.bio:
             if len(request.form['bio'].strip()) == 0:
                 err.append("please submit nonempty bio")
             elif len(request.form['bio']) > 4500:
                 err.append("bio is too long!!")
             else:
-                db.execute("update users set bio = ? where username = ?", (request.form['bio'], username))
+                user.bio = request.form['bio']
                 db.commit()
                 flash("bio updated successfully")