1 files changed, 19 insertions, 0 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;
+""",
 ]