liquid_feedback_core
changeset 22:063baac35f79
Copy admission_, discussion_, verification_ and voting_time values into issue
and use copied values for issue state changes
and use copied values for issue state changes
author | jbe |
---|---|
date | Sat Feb 06 03:31:13 2010 +0100 (2010-02-06) |
parents | 406090b1ed8e |
children | 137c98fa0b4f |
files | core.sql |
line diff
1.1 --- a/core.sql Sat Feb 06 03:29:05 2010 +0100 1.2 +++ b/core.sql Sat Feb 06 03:31:13 2010 +0100 1.3 @@ -317,6 +317,10 @@ 1.4 "fully_frozen" TIMESTAMPTZ, 1.5 "closed" TIMESTAMPTZ, 1.6 "ranks_available" BOOLEAN NOT NULL DEFAULT FALSE, 1.7 + "admission_time" INTERVAL NOT NULL, 1.8 + "discussion_time" INTERVAL NOT NULL, 1.9 + "verification_time" INTERVAL NOT NULL, 1.10 + "voting_time" INTERVAL NOT NULL, 1.11 "snapshot" TIMESTAMPTZ, 1.12 "latest_snapshot_event" "snapshot_event", 1.13 "population" INT4, 1.14 @@ -359,6 +363,10 @@ 1.15 COMMENT ON COLUMN "issue"."fully_frozen" IS 'Point in time, when "verification_time" has elapsed; Frontends must ensure that for fully_frozen issues additionally to the restrictions for half_frozen issues a) initiatives are not created, b) no interest is created or removed, c) no supporters are added or removed, d) no opinions are created, changed or deleted.'; 1.16 COMMENT ON COLUMN "issue"."closed" IS 'Point in time, when "admission_time" or "voting_time" have elapsed, and issue is no longer active; Frontends must ensure that for closed issues additionally to the restrictions for half_frozen and fully_frozen issues a) no voter is added or removed to/from the direct_voter table, b) no votes are added, modified or removed.'; 1.17 COMMENT ON COLUMN "issue"."ranks_available" IS 'TRUE = ranks have been calculated'; 1.18 +COMMENT ON COLUMN "issue"."admission_time" IS 'Copied from "policy" table at creation of issue'; 1.19 +COMMENT ON COLUMN "issue"."discussion_time" IS 'Copied from "policy" table at creation of issue'; 1.20 +COMMENT ON COLUMN "issue"."verification_time" IS 'Copied from "policy" table at creation of issue'; 1.21 +COMMENT ON COLUMN "issue"."voting_time" IS 'Copied from "policy" table at creation of issue'; 1.22 COMMENT ON COLUMN "issue"."snapshot" IS 'Point in time, when snapshot tables have been updated and "population", "vote_now", "vote_later" and *_count values were precalculated'; 1.23 COMMENT ON COLUMN "issue"."latest_snapshot_event" IS 'Event type of latest snapshot for issue; Can be used to select the latest snapshot data in the snapshot tables'; 1.24 COMMENT ON COLUMN "issue"."population" IS 'Sum of "weight" column in table "direct_population_snapshot"'; 1.25 @@ -1017,6 +1025,38 @@ 1.26 -- Automatic calculation of certain default values -- 1.27 ----------------------------------------------------- 1.28 1.29 + 1.30 +CREATE FUNCTION "copy_timings_trigger"() 1.31 + RETURNS TRIGGER 1.32 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.33 + DECLARE 1.34 + "policy_row" "policy"%ROWTYPE; 1.35 + BEGIN 1.36 + SELECT * INTO "policy_row" FROM "policy" 1.37 + WHERE "id" = NEW."policy_id"; 1.38 + IF NEW."admission_time" ISNULL THEN 1.39 + NEW."admission_time" := "policy_row"."admission_time"; 1.40 + END IF; 1.41 + IF NEW."discussion_time" ISNULL THEN 1.42 + NEW."discussion_time" := "policy_row"."discussion_time"; 1.43 + END IF; 1.44 + IF NEW."verification_time" ISNULL THEN 1.45 + NEW."verification_time" := "policy_row"."verification_time"; 1.46 + END IF; 1.47 + IF NEW."voting_time" ISNULL THEN 1.48 + NEW."voting_time" := "policy_row"."voting_time"; 1.49 + END IF; 1.50 + RETURN NEW; 1.51 + END; 1.52 + $$; 1.53 + 1.54 +CREATE TRIGGER "copy_timings" BEFORE INSERT OR UPDATE ON "issue" 1.55 + FOR EACH ROW EXECUTE PROCEDURE "copy_timings_trigger"(); 1.56 + 1.57 +COMMENT ON FUNCTION "copy_timings_trigger"() IS 'Implementation of trigger "copy_timings" on table "issue"'; 1.58 +COMMENT ON TRIGGER "copy_timings" ON "issue" IS 'If timing fields are NULL, copy values from policy.'; 1.59 + 1.60 + 1.61 CREATE FUNCTION "copy_autoreject_trigger"() 1.62 RETURNS TRIGGER 1.63 LANGUAGE 'plpgsql' VOLATILE AS $$ 1.64 @@ -1066,6 +1106,7 @@ 1.65 -- Automatic creation of dependencies -- 1.66 ---------------------------------------- 1.67 1.68 + 1.69 CREATE FUNCTION "autocreate_interest_trigger"() 1.70 RETURNS TRIGGER 1.71 LANGUAGE 'plpgsql' VOLATILE AS $$ 1.72 @@ -2913,7 +2954,7 @@ 1.73 UPDATE "issue" SET "accepted" = "issue_row"."accepted" 1.74 WHERE "id" = "issue_row"."id"; 1.75 ELSIF 1.76 - now() >= "issue_row"."created" + "policy_row"."admission_time" 1.77 + now() >= "issue_row"."created" + "issue_row"."admission_time" 1.78 THEN 1.79 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission'); 1.80 UPDATE "issue" SET "closed" = now() 1.81 @@ -2937,7 +2978,7 @@ 1.82 IF 1.83 "voting_requested_v" OR ( 1.84 "voting_requested_v" ISNULL AND 1.85 - now() >= "issue_row"."accepted" + "policy_row"."discussion_time" 1.86 + now() >= "issue_row"."accepted" + "issue_row"."discussion_time" 1.87 ) 1.88 THEN 1.89 PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze'); 1.90 @@ -2949,7 +2990,7 @@ 1.91 IF 1.92 "issue_row"."half_frozen" NOTNULL AND 1.93 "issue_row"."fully_frozen" ISNULL AND 1.94 - now() >= "issue_row"."half_frozen" + "policy_row"."verification_time" 1.95 + now() >= "issue_row"."half_frozen" + "issue_row"."verification_time" 1.96 THEN 1.97 PERFORM "freeze_after_snapshot"("issue_id_p"); 1.98 -- "issue" might change, thus "issue_row" has to be updated below 1.99 @@ -2958,7 +2999,7 @@ 1.100 IF 1.101 "issue_row"."closed" ISNULL AND 1.102 "issue_row"."fully_frozen" NOTNULL AND 1.103 - now() >= "issue_row"."fully_frozen" + "policy_row"."voting_time" 1.104 + now() >= "issue_row"."fully_frozen" + "issue_row"."voting_time" 1.105 THEN 1.106 PERFORM "close_voting"("issue_id_p"); 1.107 END IF;