diff options
Diffstat (limited to 'migrations/1_add_roles.sql')
-rw-r--r-- | migrations/1_add_roles.sql | 164 |
1 files changed, 164 insertions, 0 deletions
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; |