liquid_feedback_core
changeset 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 | bb04e4d1c68c |
children | 1a412ec5e14e |
files | core.sql update/core-update.v1.2.8-v1.2.9.sql |
line diff
1.1 --- a/core.sql Sat Oct 16 23:39:24 2010 +0200 1.2 +++ b/core.sql Sat Oct 16 23:56:35 2010 +0200 1.3 @@ -446,7 +446,6 @@ 1.4 CHECK (("agreed" NOTNULL AND "agreed" = TRUE) OR "rank" ISNULL) ); 1.5 CREATE INDEX "initiative_created_idx" ON "initiative" ("created"); 1.6 CREATE INDEX "initiative_revoked_idx" ON "initiative" ("revoked"); 1.7 -CREATE INDEX "initiative_issue_id_idx" ON "initiative" ("issue_id"); 1.8 CREATE INDEX "initiative_text_search_data_idx" ON "initiative" USING gin ("text_search_data"); 1.9 CREATE TRIGGER "update_text_search_data" 1.10 BEFORE INSERT OR UPDATE ON "initiative" 1.11 @@ -615,32 +614,16 @@ 1.12 "initiative_id" INT4, 1.13 "member_id" INT4, 1.14 "draft_id" INT8 NOT NULL, 1.15 - "auto_support" BOOLEAN NOT NULL DEFAULT 'f', 1.16 + "auto_support" BOOLEAN NOT NULL DEFAULT FALSE, 1.17 FOREIGN KEY ("issue_id", "member_id") REFERENCES "interest" ("issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE, 1.18 FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE CASCADE ON UPDATE CASCADE ); 1.19 CREATE INDEX "supporter_member_id_idx" ON "supporter" ("member_id"); 1.20 1.21 COMMENT ON TABLE "supporter" IS 'Members who support an initiative (conditionally); Frontends must ensure that supporters are not added or removed from fully_frozen or closed initiatives.'; 1.22 1.23 -COMMENT ON COLUMN "supporter"."draft_id" IS 'Latest seen draft, defaults to current draft of the initiative (implemented by trigger "default_for_draft_id")'; 1.24 - 1.25 -CREATE FUNCTION update_supporter_drafts() 1.26 - RETURNS trigger 1.27 - LANGUAGE 'plpgsql' VOLATILE AS $$ 1.28 - BEGIN 1.29 - UPDATE supporter SET draft_id = NEW.id 1.30 - WHERE initiative_id = NEW.initiative_id AND 1.31 - (auto_support = 't' OR member_id = NEW.author_id); 1.32 - RETURN new; 1.33 - END 1.34 -$$; 1.35 - 1.36 -CREATE TRIGGER "update_draft_supporter" 1.37 - AFTER INSERT ON "draft" 1.38 - FOR EACH ROW EXECUTE PROCEDURE 1.39 - update_supporter_drafts(); 1.40 - 1.41 -COMMENT ON FUNCTION "update_supporter_drafts"() IS 'Automaticly update the supported draft_id to the latest version when auto_support is enabled'; 1.42 +COMMENT ON COLUMN "supporter"."draft_id" IS 'Latest seen draft, defaults to current draft of the initiative (implemented by trigger "default_for_draft_id")'; 1.43 +COMMENT ON COLUMN "supporter"."auto_support" IS 'Supporting member does not want to confirm new drafts of the initiative'; 1.44 + 1.45 1.46 CREATE TABLE "opinion" ( 1.47 "initiative_id" INT4 NOT NULL, 1.48 @@ -687,26 +670,6 @@ 1.49 COMMENT ON COLUMN "delegation"."area_id" IS 'Reference to area, if delegation is area-wide, otherwise NULL'; 1.50 COMMENT ON COLUMN "delegation"."issue_id" IS 'Reference to issue, if delegation is issue-wide, otherwise NULL'; 1.51 1.52 -CREATE FUNCTION "check_delegation"() 1.53 - RETURNS TRIGGER 1.54 - LANGUAGE 'plpgsql' VOLATILE AS $$ 1.55 - BEGIN 1.56 - IF EXISTS ( 1.57 - SELECT NULL FROM "member" WHERE 1.58 - "id" = NEW."trustee_id" AND active = 'n' 1.59 - ) THEN 1.60 - RAISE EXCEPTION 'Cannot delegate to an inactive member'; 1.61 - END IF; 1.62 - RETURN NEW; 1.63 - END; 1.64 -$$; 1.65 - 1.66 -CREATE TRIGGER "update_delegation" 1.67 - BEFORE INSERT OR UPDATE ON "delegation" 1.68 - FOR EACH ROW EXECUTE PROCEDURE 1.69 - check_delegation(); 1.70 - 1.71 -COMMENT ON FUNCTION "check_delegation"() IS 'Sanity checks for new delegation. Dont allow delegations to inactive members'; 1.72 1.73 CREATE TABLE "direct_population_snapshot" ( 1.74 PRIMARY KEY ("issue_id", "event", "member_id"), 1.75 @@ -3416,7 +3379,6 @@ 1.76 DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p"; 1.77 DELETE FROM "membership" WHERE "member_id" = "member_id_p"; 1.78 DELETE FROM "delegation" WHERE "truster_id" = "member_id_p"; 1.79 - DELETE FROM "delegation" WHERE "trustee_id" = "member_id_p"; 1.80 DELETE FROM "direct_voter" USING "issue" 1.81 WHERE "direct_voter"."issue_id" = "issue"."id" 1.82 AND "issue"."closed" ISNULL
2.1 --- a/update/core-update.v1.2.8-v1.2.9.sql Sat Oct 16 23:39:24 2010 +0200 2.2 +++ b/update/core-update.v1.2.8-v1.2.9.sql Sat Oct 16 23:56:35 2010 +0200 2.3 @@ -4,100 +4,7 @@ 2.4 SELECT * FROM (VALUES ('1.2.9', 1, 2, 9)) 2.5 AS "subquery"("string", "major", "minor", "revision"); 2.6 2.7 - 2.8 -ALTER TABLE "supporter" ADD "auto_support" BOOLEAN NOT NULL DEFAULT 'f'; 2.9 - 2.10 -CREATE FUNCTION VOLATILE update_supporter_drafts() 2.11 - RETURNS trigger 2.12 - LANGUAGE 'plpgsql' AS $$ 2.13 - BEGIN 2.14 - UPDATE supporter SET draft_id = NEW.id 2.15 - WHERE initiative_id = NEW.initiative_id AND 2.16 - (auto_support = 't' OR member_id = NEW.author_id); 2.17 - RETURN new; 2.18 - END 2.19 -$$; 2.20 - 2.21 -COMMENT ON FUNCTION "update_supporter_drafts"() IS 'Automaticly update the supported draft_id to the latest version when auto_support is enabled'; 2.22 - 2.23 -CREATE TRIGGER "update_draft_supporter" 2.24 - AFTER INSERT ON "draft" 2.25 - FOR EACH ROW EXECUTE PROCEDURE 2.26 - update_supporter_drafts(); 2.27 - 2.28 -CREATE FUNCTION "check_delegation"() 2.29 - RETURNS TRIGGER 2.30 - LANGUAGE 'plpgsql' VOLATILE AS $$ 2.31 - BEGIN 2.32 - IF EXISTS ( 2.33 - SELECT NULL FROM "member" WHERE 2.34 - "id" = NEW."trustee_id" AND active = 'n' 2.35 - ) THEN 2.36 - RAISE EXCEPTION 'Cannot delegate to an inactive member'; 2.37 - END IF; 2.38 - RETURN NEW; 2.39 - END; 2.40 -$$; 2.41 - 2.42 -CREATE TRIGGER "update_delegation" 2.43 - BEFORE INSERT OR UPDATE ON "delegation" 2.44 - FOR EACH ROW EXECUTE PROCEDURE 2.45 - check_delegation(); 2.46 - 2.47 -CREATE OR REPLACE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE) 2.48 - RETURNS VOID 2.49 - LANGUAGE 'plpgsql' VOLATILE AS $$ 2.50 - BEGIN 2.51 - UPDATE "member" SET 2.52 - "last_login" = NULL, 2.53 - "login" = NULL, 2.54 - "password" = NULL, 2.55 - "active" = FALSE, 2.56 - "notify_email" = NULL, 2.57 - "notify_email_unconfirmed" = NULL, 2.58 - "notify_email_secret" = NULL, 2.59 - "notify_email_secret_expiry" = NULL, 2.60 - "notify_email_lock_expiry" = NULL, 2.61 - "password_reset_secret" = NULL, 2.62 - "password_reset_secret_expiry" = NULL, 2.63 - "organizational_unit" = NULL, 2.64 - "internal_posts" = NULL, 2.65 - "realname" = NULL, 2.66 - "birthday" = NULL, 2.67 - "address" = NULL, 2.68 - "email" = NULL, 2.69 - "xmpp_address" = NULL, 2.70 - "website" = NULL, 2.71 - "phone" = NULL, 2.72 - "mobile_phone" = NULL, 2.73 - "profession" = NULL, 2.74 - "external_memberships" = NULL, 2.75 - "external_posts" = NULL, 2.76 - "statement" = NULL 2.77 - WHERE "id" = "member_id_p"; 2.78 - -- "text_search_data" is updated by triggers 2.79 - DELETE FROM "setting" WHERE "member_id" = "member_id_p"; 2.80 - DELETE FROM "setting_map" WHERE "member_id" = "member_id_p"; 2.81 - DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p"; 2.82 - DELETE FROM "member_image" WHERE "member_id" = "member_id_p"; 2.83 - DELETE FROM "contact" WHERE "member_id" = "member_id_p"; 2.84 - DELETE FROM "area_setting" WHERE "member_id" = "member_id_p"; 2.85 - DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p"; 2.86 - DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p"; 2.87 - DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p"; 2.88 - DELETE FROM "membership" WHERE "member_id" = "member_id_p"; 2.89 - DELETE FROM "delegation" WHERE "truster_id" = "member_id_p"; 2.90 - DELETE FROM "delegation" WHERE "trustee_id" = "member_id_p"; 2.91 - DELETE FROM "direct_voter" USING "issue" 2.92 - WHERE "direct_voter"."issue_id" = "issue"."id" 2.93 - AND "issue"."closed" ISNULL 2.94 - AND "member_id" = "member_id_p"; 2.95 - RETURN; 2.96 - END; 2.97 - $$; 2.98 - 2.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)'; 2.100 - 2.101 -CREATE INDEX "initiative_issue_id_idx" ON "initiative" ("issue_id"); 2.102 +ALTER TABLE "supporter" ADD COLUMN 2.103 + "auto_support" BOOLEAN NOT NULL DEFAULT FALSE; 2.104 2.105 COMMIT;