liquid_feedback_core
diff update/core-update.v1.2.8-v1.2.9.sql @ 84:b0866d51b754
Backed out changesets dfa8e6a1f1e7, ca13b2614d10, and partly 0758a2e02620
- auto_support feature shall be added but implemented in a different way
- Delegating to non-active members shall be possible in database
- Incoming delegations MUST NOT be deleted when calling "delete_member"
- Index on "initiative"("issue_id") is not neccessary due to UNIQUE ("issue_id", "id") statement
- auto_support feature shall be added but implemented in a different way
- Delegating to non-active members shall be possible in database
- Incoming delegations MUST NOT be deleted when calling "delete_member"
- Index on "initiative"("issue_id") is not neccessary due to UNIQUE ("issue_id", "id") statement
author | jbe |
---|---|
date | Sat Oct 16 23:56:35 2010 +0200 (2010-10-16) |
parents | dfa8e6a1f1e7 |
children | 1a412ec5e14e |
line diff
1.1 --- a/update/core-update.v1.2.8-v1.2.9.sql Sat Oct 16 23:39:24 2010 +0200 1.2 +++ b/update/core-update.v1.2.8-v1.2.9.sql Sat Oct 16 23:56:35 2010 +0200 1.3 @@ -4,100 +4,7 @@ 1.4 SELECT * FROM (VALUES ('1.2.9', 1, 2, 9)) 1.5 AS "subquery"("string", "major", "minor", "revision"); 1.6 1.7 - 1.8 -ALTER TABLE "supporter" ADD "auto_support" BOOLEAN NOT NULL DEFAULT 'f'; 1.9 - 1.10 -CREATE FUNCTION VOLATILE update_supporter_drafts() 1.11 - RETURNS trigger 1.12 - LANGUAGE 'plpgsql' AS $$ 1.13 - BEGIN 1.14 - UPDATE supporter SET draft_id = NEW.id 1.15 - WHERE initiative_id = NEW.initiative_id AND 1.16 - (auto_support = 't' OR member_id = NEW.author_id); 1.17 - RETURN new; 1.18 - END 1.19 -$$; 1.20 - 1.21 -COMMENT ON FUNCTION "update_supporter_drafts"() IS 'Automaticly update the supported draft_id to the latest version when auto_support is enabled'; 1.22 - 1.23 -CREATE TRIGGER "update_draft_supporter" 1.24 - AFTER INSERT ON "draft" 1.25 - FOR EACH ROW EXECUTE PROCEDURE 1.26 - update_supporter_drafts(); 1.27 - 1.28 -CREATE FUNCTION "check_delegation"() 1.29 - RETURNS TRIGGER 1.30 - LANGUAGE 'plpgsql' VOLATILE AS $$ 1.31 - BEGIN 1.32 - IF EXISTS ( 1.33 - SELECT NULL FROM "member" WHERE 1.34 - "id" = NEW."trustee_id" AND active = 'n' 1.35 - ) THEN 1.36 - RAISE EXCEPTION 'Cannot delegate to an inactive member'; 1.37 - END IF; 1.38 - RETURN NEW; 1.39 - END; 1.40 -$$; 1.41 - 1.42 -CREATE TRIGGER "update_delegation" 1.43 - BEFORE INSERT OR UPDATE ON "delegation" 1.44 - FOR EACH ROW EXECUTE PROCEDURE 1.45 - check_delegation(); 1.46 - 1.47 -CREATE OR REPLACE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE) 1.48 - RETURNS VOID 1.49 - LANGUAGE 'plpgsql' VOLATILE AS $$ 1.50 - BEGIN 1.51 - UPDATE "member" SET 1.52 - "last_login" = NULL, 1.53 - "login" = NULL, 1.54 - "password" = NULL, 1.55 - "active" = FALSE, 1.56 - "notify_email" = NULL, 1.57 - "notify_email_unconfirmed" = NULL, 1.58 - "notify_email_secret" = NULL, 1.59 - "notify_email_secret_expiry" = NULL, 1.60 - "notify_email_lock_expiry" = NULL, 1.61 - "password_reset_secret" = NULL, 1.62 - "password_reset_secret_expiry" = NULL, 1.63 - "organizational_unit" = NULL, 1.64 - "internal_posts" = NULL, 1.65 - "realname" = NULL, 1.66 - "birthday" = NULL, 1.67 - "address" = NULL, 1.68 - "email" = NULL, 1.69 - "xmpp_address" = NULL, 1.70 - "website" = NULL, 1.71 - "phone" = NULL, 1.72 - "mobile_phone" = NULL, 1.73 - "profession" = NULL, 1.74 - "external_memberships" = NULL, 1.75 - "external_posts" = NULL, 1.76 - "statement" = NULL 1.77 - WHERE "id" = "member_id_p"; 1.78 - -- "text_search_data" is updated by triggers 1.79 - DELETE FROM "setting" WHERE "member_id" = "member_id_p"; 1.80 - DELETE FROM "setting_map" WHERE "member_id" = "member_id_p"; 1.81 - DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p"; 1.82 - DELETE FROM "member_image" WHERE "member_id" = "member_id_p"; 1.83 - DELETE FROM "contact" WHERE "member_id" = "member_id_p"; 1.84 - DELETE FROM "area_setting" WHERE "member_id" = "member_id_p"; 1.85 - DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p"; 1.86 - DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p"; 1.87 - DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p"; 1.88 - DELETE FROM "membership" WHERE "member_id" = "member_id_p"; 1.89 - DELETE FROM "delegation" WHERE "truster_id" = "member_id_p"; 1.90 - DELETE FROM "delegation" WHERE "trustee_id" = "member_id_p"; 1.91 - DELETE FROM "direct_voter" USING "issue" 1.92 - WHERE "direct_voter"."issue_id" = "issue"."id" 1.93 - AND "issue"."closed" ISNULL 1.94 - AND "member_id" = "member_id_p"; 1.95 - RETURN; 1.96 - END; 1.97 - $$; 1.98 - 1.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)'; 1.100 - 1.101 -CREATE INDEX "initiative_issue_id_idx" ON "initiative" ("issue_id"); 1.102 +ALTER TABLE "supporter" ADD COLUMN 1.103 + "auto_support" BOOLEAN NOT NULL DEFAULT FALSE; 1.104 1.105 COMMIT;