From c49955a06909d2dd081bc82131fe8e1450055ee0 Mon Sep 17 00:00:00 2001 From: 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 %} -

{{poll.title}}

+{% if thread.poll %} +

{{thread.poll.title}}

    - {%for opt in poll.options%} + {%for opt in thread.poll.options%}
  1. {{opt.text}}: {{opt.num or 0}} votes
    2. {%endfor%}
-{{ vote_meter(poll) }} +{{ vote_meter(thread.poll) }} {% endif %}
@@ -34,7 +34,7 @@
{% 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") %}
- {% if poll and has_permission(thread.forum, g.user, "p_vote") %} + {% if thread.poll and has_permission(thread.forum, g.user, "p_vote") %}
- poll: {{poll.title}} + poll: {{thread.poll.title}}

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

@@ -87,7 +87,7 @@ {% endif %} - {% for opt in poll.options %} + {% for opt in thread.poll.options %}
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("/") -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("/") +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("/") 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("//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