diff options
| -rw-r--r-- | migrations/0_init.sql | 148 | ||||
| -rw-r--r-- | migrations/1_add_roles.sql | 164 | ||||
| -rw-r--r-- | schema.sql | 101 | ||||
| -rw-r--r-- | src/mulk/benki/lazychat.clj | 28 |
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; @@ -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)) |