make posts in "private" subforums not visible through search or user pages

subforums are considered "private" if the p_view_forum permission
is denied to the 'other' role in that forum. this doesn't consider
inheritance: if a forum doesn't have an 'other' role then it is
considered "public" (!!!). since inheritance is probably going
to be removed soon anyway, and since we currently only have
one "private" subforum, i think this is acceptable.

also note that posts in "private" subforums are hidden in
user pages and in search results to everyone, even people
who do have access to the subforum in question. again, since
the permissions logic is probably going to be changed, i don't
think it's worth implementing actual permission checks here with
the current (confusing, somewhat broken) permissions system.

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)