liquid_feedback_core
changeset 160:cc520b58e8ec
Added reference from "suggestion" to "draft_id"; Changed reference from "supporter" to "draft_id" to DELETE NO ACTION
author | jbe |
---|---|
date | Sat Jun 04 22:11:07 2011 +0200 (2011-06-04) |
parents | 32887495ca57 |
children | b49f1a37f8cf |
files | core.sql |
line diff
1.1 --- a/core.sql Sat Jun 04 16:50:50 2011 +0200 1.2 +++ b/core.sql Sat Jun 04 22:11:07 2011 +0200 1.3 @@ -681,6 +681,8 @@ 1.4 UNIQUE ("initiative_id", "id"), -- index needed for foreign-key on table "opinion" 1.5 "initiative_id" INT4 NOT NULL REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.6 "id" SERIAL8 PRIMARY KEY, 1.7 + "draft_id" INT8 NOT NULL, 1.8 + FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE NO ACTION ON UPDATE CASCADE, 1.9 "created" TIMESTAMPTZ NOT NULL DEFAULT now(), 1.10 "author_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE, 1.11 "name" TEXT NOT NULL, 1.12 @@ -706,6 +708,7 @@ 1.13 1.14 COMMENT ON TABLE "suggestion" IS 'Suggestions to initiators, to change the current draft; must not be deleted explicitly, as they vanish automatically if the last opinion is deleted'; 1.15 1.16 +COMMENT ON COLUMN "suggestion"."draft_id" IS 'Draft, which the author has seen when composing the suggestion; should always be set by a frontend, but defaults to current draft of the initiative (implemented by trigger "default_for_draft_id")'; 1.17 COMMENT ON COLUMN "suggestion"."minus2_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters'; 1.18 COMMENT ON COLUMN "suggestion"."minus2_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters'; 1.19 COMMENT ON COLUMN "suggestion"."minus1_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters'; 1.20 @@ -812,12 +815,12 @@ 1.21 "member_id" INT4, 1.22 "draft_id" INT8 NOT NULL, 1.23 FOREIGN KEY ("issue_id", "member_id") REFERENCES "interest" ("issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE, 1.24 - FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE CASCADE ON UPDATE CASCADE ); 1.25 + FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE NO ACTION ON UPDATE CASCADE ); 1.26 CREATE INDEX "supporter_member_id_idx" ON "supporter" ("member_id"); 1.27 1.28 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.29 1.30 -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.31 +COMMENT ON COLUMN "supporter"."draft_id" IS 'Latest seen draft; should always be set by a frontend, but defaults to current draft of the initiative (implemented by trigger "default_for_draft_id")'; 1.32 1.33 1.34 CREATE TABLE "opinion" ( 1.35 @@ -1610,7 +1613,7 @@ 1.36 COMMENT ON TRIGGER "copy_timings" ON "issue" IS 'If timing fields are NULL, copy values from policy.'; 1.37 1.38 1.39 -CREATE FUNCTION "supporter_default_for_draft_id_trigger"() 1.40 +CREATE FUNCTION "default_for_draft_id_trigger"() 1.41 RETURNS TRIGGER 1.42 LANGUAGE 'plpgsql' VOLATILE AS $$ 1.43 BEGIN 1.44 @@ -1622,11 +1625,14 @@ 1.45 END; 1.46 $$; 1.47 1.48 +CREATE TRIGGER "default_for_draft_id" BEFORE INSERT OR UPDATE ON "suggestion" 1.49 + FOR EACH ROW EXECUTE PROCEDURE "default_for_draft_id_trigger"(); 1.50 CREATE TRIGGER "default_for_draft_id" BEFORE INSERT OR UPDATE ON "supporter" 1.51 - FOR EACH ROW EXECUTE PROCEDURE "supporter_default_for_draft_id_trigger"(); 1.52 - 1.53 -COMMENT ON FUNCTION "supporter_default_for_draft_id_trigger"() IS 'Implementation of trigger "default_for_draft" on table "supporter"'; 1.54 -COMMENT ON TRIGGER "default_for_draft_id" ON "supporter" IS 'If "draft_id" is NULL, then use the current draft of the initiative as default'; 1.55 + FOR EACH ROW EXECUTE PROCEDURE "default_for_draft_id_trigger"(); 1.56 + 1.57 +COMMENT ON FUNCTION "default_for_draft_id_trigger"() IS 'Implementation of trigger "default_for_draft" on tables "supporter" and "suggestion"'; 1.58 +COMMENT ON TRIGGER "default_for_draft_id" ON "suggestion" IS 'If "draft_id" is NULL, then use the current draft of the initiative as default'; 1.59 +COMMENT ON TRIGGER "default_for_draft_id" ON "supporter" IS 'If "draft_id" is NULL, then use the current draft of the initiative as default'; 1.60 1.61 1.62