diff options
| -rw-r--r-- | apioforum/db.py | 19 | ||||
| -rw-r--r-- | apioforum/forum.py | 6 | ||||
| -rw-r--r-- | apioforum/user.py | 11 |
3 files changed, 31 insertions, 5 deletions
diff --git a/apioforum/db.py b/apioforum/db.py index 269bd77..24146e2 100644 --- a/apioforum/db.py +++ b/apioforum/db.py @@ -214,6 +214,25 @@ CREATE TABLE webhooks ( url TEXT NOT NULL, forum INTEGER NOT NULL REFERENCES forums(id) );""", +""" +CREATE VIEW public_forums AS + SELECT f.id as id, + COALESCE(r.p_view_forum,1) as public + FROM forums f + LEFT JOIN role_config r ON + r.forum = f.id AND r.role='other'; +CREATE VIEW forum_thread_of_post AS + SELECT p.id as p_id, t.id as t_id, f.id as f_id + FROM posts p + JOIN threads t on p.thread = t.id + JOIN forums f on t.forum = f.id; +CREATE VIEW public_posts AS + SELECT p.id AS id, + b.public AS public + FROM posts p + JOIN forum_thread_of_post h ON p.id=h.p_id + JOIN public_forums b ON b.id=h.f_id; +""", ] diff --git a/apioforum/forum.py b/apioforum/forum.py index 3d7611b..ca656ff 100644 --- a/apioforum/forum.py +++ b/apioforum/forum.py @@ -423,11 +423,13 @@ def search(): FROM posts_fts JOIN posts ON posts_fts.rowid = posts.id JOIN threads ON threads.id = posts.thread - WHERE posts_fts MATCH ? + JOIN public_posts ON public_posts.id = posts.id + WHERE posts_fts MATCH ? AND public_posts.public ORDER BY rank LIMIT 50 """, (query,)).fetchall() - except OperationalError: + except OperationalError as e: + print(e) flash('your search query was malformed.') return redirect(url_for("forum.not_actual_index")) diff --git a/apioforum/user.py b/apioforum/user.py index 1a884aa..d6fbb60 100644 --- a/apioforum/user.py +++ b/apioforum/user.py @@ -24,10 +24,15 @@ def view_user(username, page=1): abort(404) posts = db.execute(""" SELECT * FROM posts - WHERE author = ? AND deleted = 0 + JOIN public_posts ON public_posts.id = posts.id + WHERE author = ? AND deleted = 0 AND public_posts.public ORDER BY created DESC LIMIT ? OFFSET ?;""",(username,POSTS_PER_PAGE,(page-1)*POSTS_PER_PAGE,)).fetchall() - num_posts = db.execute("SELECT count(*) as count FROM posts WHERE author = ?;",(username,)).fetchone()['count'] + num_posts = db.execute(""" + SELECT count(*) as count FROM posts + JOIN public_posts ON public_posts.id = posts.id + WHERE author = ? AND public_posts.public; + """,(username,)).fetchone()['count'] max_pageno = math.ceil(num_posts/POSTS_PER_PAGE) return render_template( "view_user.html", @@ -73,4 +78,4 @@ def edit_user(username): else: return redirect(url_for("user.view_user",username=username)) - return render_template("user_settings.html",user=user)
\ No newline at end of file + return render_template("user_settings.html",user=user) |