From 28c046e169c5874ce066228e7f3658d02317c216 Mon Sep 17 00:00:00 2001
From: Matthias Andreas Benkard <code@mail.matthias.benkard.de>
Date: Sat, 18 Aug 2012 17:49:46 +0200
Subject: Schema: Genericize access control.

---
 schema.sql | 24 ++++++++++++++----------
 1 file changed, 14 insertions(+), 10 deletions(-)

(limited to 'schema.sql')

diff --git a/schema.sql b/schema.sql
index c555166..ea5110f 100644
--- a/schema.sql
+++ b/schema.sql
@@ -146,15 +146,14 @@ CREATE TABLE lazychat_messages INHERITS posts (
   content     VARCHAR,
   format      VARCHAR NOT NULL,
   PRIMARY KEY(id),
-  FOREIGN KEY(author) REFERENCES users,
   CHECK (format IN ('markdown'))
 );
 
-CREATE TABLE lazychat_targets(
+CREATE TABLE post_targets(
   message INTEGER NOT NULL,
-  target  INTEGER NOT NULL,
+  post    INTEGER NOT NULL,
   PRIMARY KEY(message, target),
-  FOREIGN KEY(message) REFERENCES lazychat_messages,
+  --FOREIGN KEY(message) REFERENCES posts AND CHILDREN,
   FOREIGN KEY(target)  REFERENCES roles
 );
 
@@ -162,8 +161,8 @@ CREATE TABLE lazychat_references(
   referrer INTEGER NOT NULL,
   referee  INTEGER NOT NULL,
   PRIMARY KEY(referrer, referee),
-  FOREIGN KEY(referrer) REFERENCES lazychat_messages,
-  FOREIGN KEY(referee)  REFERENCES lazychat_messages
+  --FOREIGN KEY(referee)  REFERENCES posts AND CHILDREN,
+  FOREIGN KEY(referrer) REFERENCES lazychat_messages
 );
 
 CREATE TABLE user_default_target(
@@ -239,13 +238,18 @@ CREATE TRIGGER new_user_create_user_role
   FOR EACH ROW
   EXECUTE PROCEDURE new_user_create_user_role();
 
-CREATE VIEW user_visible_lazychat_messages AS
+CREATE VIEW user_visible_posts AS
    SELECT eur.user, t.message
-     FROM effective_user_roles eur, lazychat_targets t
+     FROM effective_user_roles eur, post_targets t
     WHERE t.target = eur.role
   UNION
-    SELECT m.author, m.message
-    FROM lazychat_messages m;
+    SELECT m.owner, m.message
+    FROM posts m;
+
+CREATE VIEW user_visible_lazychat_messages AS
+  SELECT uvp.user, uvp.message
+    FROM user_visible_posts uvp
+    INNER JOIN lazychat_messages lm ON lm.id = uvp.message;
 
 ROLLBACK;
 --COMMIT;
-- 
cgit v1.2.3

