liquid_feedback_core
annotate update/core-update.v1.2.8-v1.2.9.sql @ 79:0758a2e02620
implement auto_support and delegation checks
* users can put their support in auto_support mode that supports every new draft text, this is automaticly done for the author of the draft.
* enforce that new delegations cannot be done set on inactive members.
* delete also incomming delegations on delete of a user.
* users can put their support in auto_support mode that supports every new draft text, this is automaticly done for the author of the draft.
* enforce that new delegations cannot be done set on inactive members.
* delete also incomming delegations on delete of a user.
author | Daniel Poelzleithner <poelzi@poelzi.org> |
---|---|
date | Thu Oct 07 19:45:12 2010 +0200 (2010-10-07) |
parents | |
children | ca13b2614d10 |
rev | line source |
---|---|
poelzi@79 | 1 BEGIN; |
poelzi@79 | 2 |
poelzi@79 | 3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS |
poelzi@79 | 4 SELECT * FROM (VALUES ('1.2.9', 1, 2, 9)) |
poelzi@79 | 5 AS "subquery"("string", "major", "minor", "revision"); |
poelzi@79 | 6 |
poelzi@79 | 7 |
poelzi@79 | 8 ALTER TABLE "supporter" ADD "auto_support" BOOLEAN NOT NULL DEFAULT 'f'; |
poelzi@79 | 9 |
poelzi@79 | 10 CREATE FUNCTION update_supporter_drafts() |
poelzi@79 | 11 RETURNS trigger |
poelzi@79 | 12 LANGUAGE 'plpgsql' AS $$ |
poelzi@79 | 13 BEGIN |
poelzi@79 | 14 UPDATE supporter SET draft_id = NEW.id |
poelzi@79 | 15 WHERE initiative_id = NEW.initiative_id AND |
poelzi@79 | 16 (auto_support = 't' OR member_id = NEW.author_id); |
poelzi@79 | 17 RETURN new; |
poelzi@79 | 18 END |
poelzi@79 | 19 $$; |
poelzi@79 | 20 |
poelzi@79 | 21 COMMENT ON FUNCTION "update_supporter_drafts"() IS 'Automaticly update the supported draft_id to the latest version when auto_support is enabled'; |
poelzi@79 | 22 |
poelzi@79 | 23 CREATE TRIGGER "update_draft_supporter" |
poelzi@79 | 24 AFTER INSERT ON "draft" |
poelzi@79 | 25 FOR EACH ROW EXECUTE PROCEDURE |
poelzi@79 | 26 update_supporter_drafts(); |
poelzi@79 | 27 |
poelzi@79 | 28 CREATE FUNCTION "check_delegation"() |
poelzi@79 | 29 RETURNS TRIGGER |
poelzi@79 | 30 LANGUAGE 'plpgsql' VOLATILE AS $$ |
poelzi@79 | 31 BEGIN |
poelzi@79 | 32 IF EXISTS ( |
poelzi@79 | 33 SELECT NULL FROM "member" WHERE |
poelzi@79 | 34 "id" = NEW."trustee_id" AND active = 'n' |
poelzi@79 | 35 ) THEN |
poelzi@79 | 36 RAISE EXCEPTION 'Cannot delegate to an inactive member'; |
poelzi@79 | 37 END IF; |
poelzi@79 | 38 RETURN NEW; |
poelzi@79 | 39 END; |
poelzi@79 | 40 $$; |
poelzi@79 | 41 |
poelzi@79 | 42 CREATE TRIGGER "update_delegation" |
poelzi@79 | 43 BEFORE INSERT OR UPDATE ON "delegation" |
poelzi@79 | 44 FOR EACH ROW EXECUTE PROCEDURE |
poelzi@79 | 45 check_delegation(); |
poelzi@79 | 46 |
poelzi@79 | 47 CREATE OR REPLACE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE) |
poelzi@79 | 48 RETURNS VOID |
poelzi@79 | 49 LANGUAGE 'plpgsql' VOLATILE AS $$ |
poelzi@79 | 50 BEGIN |
poelzi@79 | 51 UPDATE "member" SET |
poelzi@79 | 52 "last_login" = NULL, |
poelzi@79 | 53 "login" = NULL, |
poelzi@79 | 54 "password" = NULL, |
poelzi@79 | 55 "active" = FALSE, |
poelzi@79 | 56 "notify_email" = NULL, |
poelzi@79 | 57 "notify_email_unconfirmed" = NULL, |
poelzi@79 | 58 "notify_email_secret" = NULL, |
poelzi@79 | 59 "notify_email_secret_expiry" = NULL, |
poelzi@79 | 60 "notify_email_lock_expiry" = NULL, |
poelzi@79 | 61 "password_reset_secret" = NULL, |
poelzi@79 | 62 "password_reset_secret_expiry" = NULL, |
poelzi@79 | 63 "organizational_unit" = NULL, |
poelzi@79 | 64 "internal_posts" = NULL, |
poelzi@79 | 65 "realname" = NULL, |
poelzi@79 | 66 "birthday" = NULL, |
poelzi@79 | 67 "address" = NULL, |
poelzi@79 | 68 "email" = NULL, |
poelzi@79 | 69 "xmpp_address" = NULL, |
poelzi@79 | 70 "website" = NULL, |
poelzi@79 | 71 "phone" = NULL, |
poelzi@79 | 72 "mobile_phone" = NULL, |
poelzi@79 | 73 "profession" = NULL, |
poelzi@79 | 74 "external_memberships" = NULL, |
poelzi@79 | 75 "external_posts" = NULL, |
poelzi@79 | 76 "statement" = NULL |
poelzi@79 | 77 WHERE "id" = "member_id_p"; |
poelzi@79 | 78 -- "text_search_data" is updated by triggers |
poelzi@79 | 79 DELETE FROM "setting" WHERE "member_id" = "member_id_p"; |
poelzi@79 | 80 DELETE FROM "setting_map" WHERE "member_id" = "member_id_p"; |
poelzi@79 | 81 DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p"; |
poelzi@79 | 82 DELETE FROM "member_image" WHERE "member_id" = "member_id_p"; |
poelzi@79 | 83 DELETE FROM "contact" WHERE "member_id" = "member_id_p"; |
poelzi@79 | 84 DELETE FROM "area_setting" WHERE "member_id" = "member_id_p"; |
poelzi@79 | 85 DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p"; |
poelzi@79 | 86 DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p"; |
poelzi@79 | 87 DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p"; |
poelzi@79 | 88 DELETE FROM "membership" WHERE "member_id" = "member_id_p"; |
poelzi@79 | 89 DELETE FROM "delegation" WHERE "truster_id" = "member_id_p"; |
poelzi@79 | 90 DELETE FROM "delegation" WHERE "trustee_id" = "member_id_p"; |
poelzi@79 | 91 DELETE FROM "direct_voter" USING "issue" |
poelzi@79 | 92 WHERE "direct_voter"."issue_id" = "issue"."id" |
poelzi@79 | 93 AND "issue"."closed" ISNULL |
poelzi@79 | 94 AND "member_id" = "member_id_p"; |
poelzi@79 | 95 RETURN; |
poelzi@79 | 96 END; |
poelzi@79 | 97 $$; |
poelzi@79 | 98 |
poelzi@79 | 99 COMMENT ON FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE) IS 'Deactivate member and clear certain settings and data of this member (data protection)'; |
poelzi@79 | 100 |
poelzi@79 | 101 COMMIT; |