liquid_feedback_core
diff core.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 | bb04e4d1c68c |
children | 3a86196ed0bf |
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