summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
-rw-r--r--migrations/0_init.sql148
-rw-r--r--migrations/1_add_roles.sql164
-rw-r--r--schema.sql101
-rw-r--r--src/mulk/benki/lazychat.clj28
4 files changed, 417 insertions, 24 deletions
diff --git a/migrations/0_init.sql b/migrations/0_init.sql
new file mode 100644
index 0000000..5c1d4c5
--- /dev/null
+++ b/migrations/0_init.sql
@@ -0,0 +1,148 @@
+--*- mode: sql; coding: utf-8 -*--
+
+BEGIN TRANSACTION;
+
+CREATE TABLE users(
+ id SERIAL NOT NULL,
+ first_name VARCHAR,
+ middle_names VARCHAR,
+ last_name VARCHAR,
+ email VARCHAR,
+ website VARCHAR,
+ status VARCHAR,
+ PRIMARY KEY(id),
+ CHECK (status IN ('admin', 'approved', 'visitor', 'disabled'))
+);
+
+CREATE TABLE openids(
+ "user" INTEGER NOT NULL,
+ openid VARCHAR NOT NULL,
+ PRIMARY KEY(openid),
+ FOREIGN KEY("user") REFERENCES users
+);
+
+CREATE TABLE webids(
+ "user" INTEGER NOT NULL,
+ webid VARCHAR NOT NULL,
+ PRIMARY KEY(webid),
+ FOREIGN KEY("user") REFERENCES users
+);
+
+CREATE TABLE rsa_keys(
+ modulus NUMERIC NOT NULL,
+ exponent NUMERIC NOT NULL,
+ PRIMARY KEY(modulus, exponent)
+);
+
+CREATE TABLE user_rsa_keys(
+ "user" INTEGER NOT NULL,
+ modulus NUMERIC NOT NULL,
+ exponent NUMERIC NOT NULL,
+ PRIMARY KEY("user", modulus, exponent),
+ FOREIGN KEY("user") REFERENCES users,
+ FOREIGN KEY(modulus, exponent) REFERENCES rsa_keys
+);
+
+CREATE TABLE user_email_addresses(
+ "user" INTEGER NOT NULL,
+ email VARCHAR NOT NULL,
+ PRIMARY KEY(email),
+ FOREIGN KEY("user") REFERENCES users
+);
+
+CREATE TABLE user_nicknames(
+ "user" INTEGER NOT NULL,
+ nickname VARCHAR NOT NULL,
+ PRIMARY KEY(nickname),
+ FOREIGN KEY("user") REFERENCES users
+);
+CREATE INDEX user_nicknames_user ON user_nicknames ("user");
+
+CREATE TABLE user_jids(
+ "user" INTEGER NOT NULL,
+ jid VARCHAR NOT NULL,
+ PRIMARY KEY("user", jid),
+ FOREIGN KEY("user") REFERENCES users
+);
+CREATE INDEX user_jids_user ON user_jids ("user");
+
+CREATE TABLE page_keys(
+ "user" INTEGER NOT NULL,
+ page VARCHAR NOT NULL,
+ "key" DECIMAL NOT NULL, -- (~ NUMERIC DECIMAL)
+ PRIMARY KEY(page, "key"),
+ FOREIGN KEY("user") REFERENCES users
+);
+
+CREATE TABLE wiki_pages(
+ id SERIAL NOT NULL,
+ PRIMARY KEY(id)
+);
+
+CREATE TABLE wiki_page_revisions(
+ id SERIAL NOT NULL,
+ page INTEGER NOT NULL,
+ date TIMESTAMP WITH TIME ZONE DEFAULT now(),
+ title VARCHAR,
+ content VARCHAR,
+ author INTEGER,
+ format VARCHAR,
+ PRIMARY KEY(id),
+ FOREIGN KEY(page) REFERENCES wiki_pages,
+ FOREIGN KEY(author) REFERENCES users,
+ CHECK (format IN ('mulkwiki', 'html5', 'xhtml5', 'markdown', 'textile', 'muse', 'bbcode'))
+);
+
+
+CREATE TABLE bookmarks(
+ id SERIAL NOT NULL,
+ owner INTEGER,
+ date TIMESTAMP WITH TIME ZONE DEFAULT now(),
+ uri VARCHAR NOT NULL,
+ title VARCHAR,
+ description VARCHAR,
+ visibility VARCHAR,
+ PRIMARY KEY(id),
+ FOREIGN KEY(owner) REFERENCES users,
+ CHECK (visibility IN ('private', 'protected', 'public'))
+);
+
+CREATE TABLE bookmark_tags(
+ bookmark INTEGER NOT NULL,
+ tag VARCHAR NOT NULL,
+ PRIMARY KEY(bookmark, tag),
+ FOREIGN KEY(bookmark) REFERENCES bookmarks
+);
+
+
+CREATE TABLE lazychat_messages(
+ id SERIAL NOT NULL,
+ author INTEGER,
+ date TIMESTAMP WITH TIME ZONE DEFAULT now(),
+ content VARCHAR,
+ visibility VARCHAR NOT NULL,
+ format VARCHAR NOT NULL,
+ PRIMARY KEY(id),
+ FOREIGN KEY(author) REFERENCES users,
+ CHECK (format IN ('markdown')),
+ CHECK (visibility IN ('personal', 'protected', 'public'))
+);
+
+CREATE TABLE lazychat_targets(
+ message INTEGER NOT NULL,
+ target INTEGER NOT NULL,
+ PRIMARY KEY(message, target),
+ FOREIGN KEY(message) REFERENCES lazychat_messages,
+ FOREIGN KEY(target) REFERENCES users
+);
+
+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
+);
+
+COMMIT;
+
diff --git a/migrations/1_add_roles.sql b/migrations/1_add_roles.sql
new file mode 100644
index 0000000..f55d6bf
--- /dev/null
+++ b/migrations/1_add_roles.sql
@@ -0,0 +1,164 @@
+BEGIN TRANSACTION;
+
+CREATE TABLE roles(
+ id SERIAL NOT NULL,
+ name VARCHAR,
+ PRIMARY KEY(id)
+);
+
+ALTER TABLE lazychat_targets DROP CONSTRAINT lazychat_targets_target_fkey;
+ALTER TABLE lazychat_targets ADD CONSTRAINT lazychat_targets_target_fkey FOREIGN KEY(target) REFERENCES roles;
+
+CREATE TABLE role_tags(
+ "role" INTEGER NOT NULL,
+ tag VARCHAR NOT NULL,
+ PRIMARY KEY("role", tag),
+ CHECK (tag IN ('admin', 'everyone', 'world'))
+);
+
+ALTER TABLE users ADD COLUMN "role" INTEGER REFERENCES roles;
+
+CREATE TABLE user_roles(
+ "user" INTEGER NOT NULL,
+ "role" INTEGER NOT NULL,
+ PRIMARY KEY("user", "role"),
+ FOREIGN KEY("user") REFERENCES users,
+ FOREIGN KEY("role") REFERENCES roles
+);
+
+-- Create singleton roles for existing users.
+CREATE FUNCTION create_singleton_roles() RETURNS VOID AS $$
+DECLARE
+ "user" INTEGER;
+ user_name VARCHAR;
+BEGIN
+ FOR "user", user_name IN SELECT id, concat(first_name, ' ', last_name) FROM users LOOP
+ DECLARE
+ new_role INTEGER;
+ BEGIN
+ INSERT INTO roles("name") VALUES (user_name)
+ RETURNING id INTO STRICT new_role;
+ UPDATE users SET "role" = new_role WHERE id = "user";
+ INSERT INTO user_roles VALUES ("user", new_role);
+ END;
+ END LOOP;
+END;
+$$ LANGUAGE plpgsql;
+SELECT create_singleton_roles();
+DROP FUNCTION create_singleton_roles();
+
+ALTER TABLE users ALTER COLUMN "role" SET NOT NULL;
+
+CREATE TABLE role_subroles(
+ "superrole" INTEGER NOT NULL,
+ "subrole" INTEGER NOT NULL,
+ PRIMARY KEY("superrole", "subrole"),
+ FOREIGN KEY("superrole") REFERENCES roles,
+ FOREIGN KEY("subrole") REFERENCES roles
+);
+
+CREATE VIEW effective_role_subroles AS
+ WITH RECURSIVE t(superrole, subrole) AS (
+ SELECT id, id
+ FROM roles
+ UNION
+ SELECT t.superrole, rs.subrole
+ FROM t t
+ INNER JOIN role_subroles rs
+ ON (rs.superrole = t.subrole)
+ )
+ SELECT * FROM t;
+
+CREATE VIEW effective_user_roles AS
+ (SELECT ur."user", er.subrole AS role
+ FROM user_roles ur
+ INNER JOIN effective_role_subroles er
+ ON (er.superrole = ur.role))
+ UNION
+ (SELECT u.id AS "user", rt.role FROM users u, role_tags rt WHERE rt.tag IN ('everyone', 'world'))
+ UNION
+ (SELECT NULL, rt.role FROM role_tags rt WHERE rt.tag = 'world');
+
+WITH r(id) AS (
+ INSERT INTO roles(name) VALUES ('Admininistrators') RETURNING id
+)
+INSERT INTO role_tags SELECT r.id, 'admin' FROM r;
+
+WITH world(id) AS (
+ INSERT INTO roles(name) VALUES ('World') RETURNING id
+), t AS (
+ INSERT INTO lazychat_targets
+ SELECT m.id, world.id
+ FROM lazychat_messages m, world
+ WHERE m.visibility = 'public';
+)
+INSERT INTO role_tags SELECT id, 'world' FROM world;
+
+WITH r(id) AS (
+ INSERT INTO roles(name) VALUES ('Logged-In Users') RETURNING id
+), t AS (
+ INSERT INTO user_roles SELECT users.id, r.id FROM users, r
+)
+INSERT INTO role_tags SELECT r.id, 'everyone' FROM r;
+
+WITH inner_circle(id) AS (
+ INSERT INTO roles(name) VALUES ('Inner Circle') RETURNING id
+), t AS (
+ INSERT INTO lazychat_targets
+ SELECT m.id, inner_circle.id
+ FROM lazychat_messages m, inner_circle
+ WHERE m.visibility = 'protected'
+)
+INSERT INTO user_roles SELECT users.id, inner_circle.id FROM users, inner_circle;
+
+ALTER TABLE lazychat_messages DROP COLUMN visibility;
+
+CREATE VIEW user_visible_lazychat_messages AS
+ SELECT eur.user, t.message
+ FROM effective_user_roles eur, lazychat_targets t
+ WHERE t.target = eur.role;
+
+CREATE FUNCTION new_user_put_in_universal_role() RETURNS TRIGGER AS $$
+DECLARE
+ universal_role INTEGER;
+BEGIN
+ SELECT "role" FROM role_tags WHERE tag = 'everyone' INTO universal_role;
+ INSERT INTO user_roles VALUES (NEW.id, universal_role);
+ RETURN NEW;
+END;
+$$ LANGUAGE plpgsql;
+
+CREATE TRIGGER new_user_put_in_universal_role
+ AFTER INSERT ON users
+ FOR EACH ROW
+ EXECUTE PROCEDURE put_new_user_in_universal_role();
+
+CREATE FUNCTION new_user_put_user_in_user_role() RETURNS TRIGGER AS $$
+BEGIN
+ INSERT INTO user_roles VALUES (NEW."role", user_role);
+END;
+$$ LANGUAGE plpgsql;
+
+CREATE TRIGGER new_user_put_user_in_user_role
+ AFTER INSERT ON users
+ FOR EACH ROW
+ EXECUTE PROCEDURE new_user_put_user_in_user_role();
+
+CREATE FUNCTION new_user_create_user_role() RETURNS TRIGGER AS $$
+DECLARE
+ new_role INTEGER;
+BEGIN
+ INSERT INTO roles("name") VALUES (concat(NEW.first_name, ' ', NEW.last_name))
+ RETURNING id INTO STRICT new_role;
+ UPDATE users SET "role" = new_role WHERE id = NEW.id;
+ NEW.role := new_role;
+ RETURN NEW;
+END;
+$$ LANGUAGE plpgsql;
+
+CREATE TRIGGER new_user_create_user_role
+ BEFORE INSERT ON users
+ FOR EACH ROW
+ EXECUTE PROCEDURE new_user_create_user_role();
+
+COMMIT;
diff --git a/schema.sql b/schema.sql
index 4170b3b..8934883 100644
--- a/schema.sql
+++ b/schema.sql
@@ -2,6 +2,11 @@
BEGIN TRANSACTION;
+CREATE TABLE roles(
+ id SERIAL NOT NULL,
+ PRIMARY KEY(id)
+);
+
CREATE TABLE users(
id SERIAL NOT NULL,
first_name VARCHAR,
@@ -10,8 +15,10 @@ CREATE TABLE users(
email VARCHAR,
website VARCHAR,
status VARCHAR,
+ "role" INTEGER NOT NULL,
PRIMARY KEY(id),
- CHECK (status IN ('admin', 'approved', 'visitor', 'disabled'))
+ CHECK (status IN ('admin', 'approved', 'visitor', 'disabled')),
+ FOREIGN KEY("role") REFERENCES roles
);
CREATE TABLE openids(
@@ -66,6 +73,22 @@ CREATE TABLE user_jids(
);
CREATE INDEX user_jids_user ON user_jids ("user");
+CREATE TABLE role_subroles(
+ "superrole" INTEGER NOT NULL,
+ "subrole" INTEGER NOT NULL,
+ PRIMARY KEY("superrole", "subrole"),
+ FOREIGN KEY("superrole") REFERENCES roles,
+ FOREIGN KEY("subrole") REFERENCES roles
+);
+
+CREATE TABLE user_roles(
+ "user" INTEGER NOT NULL,
+ "role" INTEGER NOT NULL,
+ PRIMARY KEY("user", "role"),
+ FOREIGN KEY("user") REFERENCES users,
+ FOREIGN KEY("role") REFERENCES roles
+);
+
CREATE TABLE page_keys(
"user" INTEGER NOT NULL,
page VARCHAR NOT NULL,
@@ -120,12 +143,10 @@ CREATE TABLE lazychat_messages(
author INTEGER,
date TIMESTAMP WITH TIME ZONE DEFAULT now(),
content VARCHAR,
- visibility VARCHAR NOT NULL,
format VARCHAR NOT NULL,
PRIMARY KEY(id),
FOREIGN KEY(author) REFERENCES users,
- CHECK (format IN ('markdown')),
- CHECK (visibility IN ('personal', 'protected', 'public'))
+ CHECK (format IN ('markdown'))
);
CREATE TABLE lazychat_targets(
@@ -133,7 +154,7 @@ CREATE TABLE lazychat_targets(
target INTEGER NOT NULL,
PRIMARY KEY(message, target),
FOREIGN KEY(message) REFERENCES lazychat_messages,
- FOREIGN KEY(target) REFERENCES users
+ FOREIGN KEY(target) REFERENCES roles
);
CREATE TABLE lazychat_references(
@@ -144,5 +165,75 @@ CREATE TABLE lazychat_references(
FOREIGN KEY(referee) REFERENCES lazychat_messages
);
+CREATE VIEW effective_role_subroles AS
+ WITH RECURSIVE t(superrole, subrole) AS (
+ SELECT id, id
+ FROM roles
+ UNION
+ SELECT t.superrole, rs.subrole
+ FROM t t
+ INNER JOIN role_subroles rs
+ ON (rs.superrole = t.subrole)
+ )
+ SELECT * FROM t;
+
+CREATE VIEW effective_user_roles AS
+ (SELECT ur."user", er.subrole AS role
+ FROM user_roles ur
+ INNER JOIN effective_role_subroles er
+ ON (er.superrole = ur.role))
+ UNION
+ (SELECT u.id AS "user", rt.role FROM users u, role_tags rt WHERE rt.tag IN ('everyone', 'world'))
+ UNION
+ (SELECT NULL, rt.role FROM role_tags rt WHERE rt.tag = 'world');
+
+CREATE FUNCTION new_user_put_in_universal_role() RETURNS TRIGGER AS $$
+DECLARE
+ universal_role INTEGER;
+BEGIN
+ SELECT "role" FROM role_tags WHERE tag = 'everyone' INTO universal_role;
+ INSERT INTO user_roles VALUES (NEW.id, universal_role);
+ RETURN NEW;
+END;
+$$ LANGUAGE plpgsql;
+
+CREATE TRIGGER new_user_put_in_universal_role
+ AFTER INSERT ON users
+ FOR EACH ROW
+ EXECUTE PROCEDURE put_new_user_in_universal_role();
+
+CREATE FUNCTION new_user_put_user_in_user_role() RETURNS TRIGGER AS $$
+BEGIN
+ INSERT INTO user_roles VALUES (NEW."role", user_role);
+END;
+$$ LANGUAGE plpgsql;
+
+CREATE TRIGGER new_user_put_user_in_user_role
+ AFTER INSERT ON users
+ FOR EACH ROW
+ EXECUTE PROCEDURE new_user_put_user_in_user_role();
+
+CREATE FUNCTION new_user_create_user_role() RETURNS TRIGGER AS $$
+DECLARE
+ new_role INTEGER;
+BEGIN
+ INSERT INTO roles("name") VALUES (concat(NEW.first_name, ' ', NEW.last_name))
+ RETURNING id INTO STRICT new_role;
+ UPDATE users SET "role" = new_role WHERE id = NEW.id;
+ NEW.role := new_role;
+ RETURN NEW;
+END;
+$$ LANGUAGE plpgsql;
+
+CREATE TRIGGER new_user_create_user_role
+ BEFORE INSERT ON users
+ FOR EACH ROW
+ EXECUTE PROCEDURE new_user_create_user_role();
+
+CREATE VIEW user_visible_lazychat_messages AS
+ SELECT eur.user, t.message
+ FROM effective_user_roles eur, lazychat_targets t
+ WHERE t.target = eur.role;
+
ROLLBACK;
--COMMIT;
diff --git a/src/mulk/benki/lazychat.clj b/src/mulk/benki/lazychat.clj
index b32e3af..bfee679 100644
--- a/src/mulk/benki/lazychat.clj
+++ b/src/mulk/benki/lazychat.clj
@@ -32,16 +32,11 @@
[message]
(fmt nil "<~A>\n\n~A" (:first_name message) (:content message)))
-(defn determine-targets [message]
- (letfn [(protected-targets []
- (with-dbt
- (map :id (query "SELECT id FROM users WHERE status IN ('admin', 'approved')"))))]
- (into #{}
- (concat (:targets message)
- (case (keyword (:visibility message))
- :personal nil
- :protected (protected-targets)
- :public (cons nil (protected-targets)))))))
+(defn determine-targets [message-id]
+ (with-dbt
+ (map :user (query "SELECT \"user\" FROM user_visible_lazychat_messages
+ WHERE message = ?"
+ message-id))))
(defn fill-in-author-details [x]
x)
@@ -83,7 +78,7 @@
(create-lazychat-message-by-user! *user* msg))
(defn push-message-to-xmpp [msg]
- (let [targets (filter integer? (determine-targets msg))]
+ (let [targets (filter integer? (determine-targets {:id msg}))]
(enqueue xmpp/messages {:message msg,
:targets targets})))
@@ -137,15 +132,10 @@
["SELECT m.id, m.author, m.date, m.content, m.format, u.first_name, u.last_name
FROM lazychat_messages m
JOIN users u ON (author = u.id)
- WHERE (visibility = 'public'
- OR (visibility = 'protected' AND (?::INTEGER) IS NOT NULL)
- OR (visibility = 'personal'
- AND EXISTS (SELECT *
- FROM lazychat_targets t
- WHERE t.target = (?::INTEGER)
- AND message = m.id)))
+ JOIN user_visible_lazychat_messages uvlm ON (uvlm.message = m.id)
+ WHERE uvlm.user IS NOT DISTINCT FROM ?
ORDER BY m.date DESC"
- ~user ~user]
+ ~user]
~@body))