From c49955a06909d2dd081bc82131fe8e1450055ee0 Mon Sep 17 00:00:00 2001
From: citrons <citrons>
Date: Wed, 18 Aug 2021 04:06:02 +0000
Subject: begin db refactor
---
apioforum/__init__.py | 2 +
apioforum/db.py | 323 ++++++++++++++---------------------
apioforum/db_migrations.py | 190 +++++++++++++++++++++
apioforum/forum.py | 6 +
apioforum/templates/view_thread.html | 18 +-
apioforum/thread.py | 110 +++++++-----
apioforum/user.py | 32 ++--
7 files changed, 428 insertions(+), 253 deletions(-)
create mode 100644 apioforum/db_migrations.py
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")
--
cgit v1.2.3