liquid_feedback_core
changeset 3:3da35844c874 beta4
Version beta4
Introduced half-freeze feature: Policies can cause issues to become half-frozen for a given amount of time before becoming fully frozen for voting. When an issue is half-frozen initiatives may not modify their drafts, but creating a new initiative or removing/adding support and opinions is still possible.
Bugfix: end_of_admission snapshots are now created for accepted issues.
Introduced half-freeze feature: Policies can cause issues to become half-frozen for a given amount of time before becoming fully frozen for voting. When an issue is half-frozen initiatives may not modify their drafts, but creating a new initiative or removing/adding support and opinions is still possible.
Bugfix: end_of_admission snapshots are now created for accepted issues.
author | jbe |
---|---|
date | Sun Nov 15 12:00:00 2009 +0100 (2009-11-15) |
parents | d45919d791ff |
children | 6133c0a62378 |
files | core.sql demo.sql |
line diff
1.1 --- a/core.sql Sat Nov 07 12:00:00 2009 +0100 1.2 +++ b/core.sql Sun Nov 15 12:00:00 2009 +0100 1.3 @@ -66,6 +66,7 @@ 1.4 "description" TEXT NOT NULL DEFAULT '', 1.5 "admission_time" INTERVAL NOT NULL, 1.6 "discussion_time" INTERVAL NOT NULL, 1.7 + "verification_time" INTERVAL NOT NULL, 1.8 "voting_time" INTERVAL NOT NULL, 1.9 "issue_quorum_num" INT4 NOT NULL, 1.10 "issue_quorum_den" INT4 NOT NULL, 1.11 @@ -77,8 +78,9 @@ 1.12 1.13 COMMENT ON COLUMN "policy"."active" IS 'TRUE = policy can be used for new issues'; 1.14 COMMENT ON COLUMN "policy"."admission_time" IS 'Maximum time an issue stays open without being "accepted"'; 1.15 -COMMENT ON COLUMN "policy"."discussion_time" IS 'Regular time until an issue is "frozen" after being "accepted"'; 1.16 -COMMENT ON COLUMN "policy"."voting_time" IS 'Time after an issue is "frozen" but not "closed"'; 1.17 +COMMENT ON COLUMN "policy"."discussion_time" IS 'Regular time until an issue is "half_frozen" after being "accepted"'; 1.18 +COMMENT ON COLUMN "policy"."verification_time" IS 'Regular time until an issue is "fully_frozen" after being "half_frozen"'; 1.19 +COMMENT ON COLUMN "policy"."voting_time" IS 'Time after an issue is "fully_frozen" but not "closed"'; 1.20 COMMENT ON COLUMN "policy"."issue_quorum_num" IS 'Numerator of quorum to be reached by one initiative of an issue to be "accepted"'; 1.21 COMMENT ON COLUMN "policy"."issue_quorum_den" IS 'Denominator of quorum to be reached by one initiative of an issue to be "accepted"'; 1.22 COMMENT ON COLUMN "policy"."initiative_quorum_num" IS 'Numerator of quorum to be reached by an initiative to be "admitted" for voting'; 1.23 @@ -103,7 +105,8 @@ 1.24 "policy_id" INT4 NOT NULL REFERENCES "policy" ("id") ON DELETE RESTRICT ON UPDATE CASCADE, 1.25 "created" TIMESTAMPTZ NOT NULL DEFAULT now(), 1.26 "accepted" TIMESTAMPTZ, 1.27 - "frozen" TIMESTAMPTZ, 1.28 + "half_frozen" TIMESTAMPTZ, 1.29 + "fully_frozen" TIMESTAMPTZ, 1.30 "closed" TIMESTAMPTZ, 1.31 "ranks_available" BOOLEAN NOT NULL DEFAULT FALSE, 1.32 "snapshot" TIMESTAMPTZ, 1.33 @@ -111,15 +114,20 @@ 1.34 "vote_now" INT4, 1.35 "vote_later" INT4, 1.36 CONSTRAINT "valid_state" CHECK ( 1.37 - ("accepted" ISNULL AND "frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR 1.38 - ("accepted" ISNULL AND "frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR 1.39 - ("accepted" NOTNULL AND "frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR 1.40 - ("accepted" NOTNULL AND "frozen" NOTNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR 1.41 - ("accepted" NOTNULL AND "frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR 1.42 - ("accepted" NOTNULL AND "frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = TRUE) ), 1.43 - CONSTRAINT "state_change_order" CHECK ("created" <= "accepted" AND "accepted" <= "frozen" AND "frozen" <= "closed"), 1.44 - CONSTRAINT "last_snapshot_on_freeze" CHECK ("snapshot" = "frozen"), -- NOTE: snapshot can be set, while frozen is NULL yet 1.45 - CONSTRAINT "freeze_requires_snapshot" CHECK ("frozen" ISNULL OR "snapshot" NOTNULL) ); 1.46 + ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR 1.47 + ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR 1.48 + ("accepted" NOTNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR 1.49 + ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR 1.50 + ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR 1.51 + ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR 1.52 + ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = TRUE) ), 1.53 + CONSTRAINT "state_change_order" CHECK ( 1.54 + "created" <= "accepted" AND 1.55 + "accepted" <= "half_frozen" AND 1.56 + "half_frozen" <= "fully_frozen" AND 1.57 + "fully_frozen" <= "closed" ), 1.58 + CONSTRAINT "last_snapshot_on_full_freeze" CHECK ("snapshot" = "fully_frozen"), -- NOTE: snapshot can be set, while frozen is NULL yet 1.59 + CONSTRAINT "freeze_requires_snapshot" CHECK ("fully_frozen" ISNULL OR "snapshot" NOTNULL) ); 1.60 CREATE INDEX "issue_area_id_idx" ON "issue" ("area_id"); 1.61 CREATE INDEX "issue_policy_id_idx" ON "issue" ("policy_id"); 1.62 CREATE INDEX "issue_created_idx_open" ON "issue" ("created") WHERE "closed" ISNULL; 1.63 @@ -127,8 +135,9 @@ 1.64 COMMENT ON TABLE "issue" IS 'Groups of initiatives'; 1.65 1.66 COMMENT ON COLUMN "issue"."accepted" IS 'Point in time, when one initiative of issue reached the "issue_quorum"'; 1.67 -COMMENT ON COLUMN "issue"."frozen" IS 'Point in time, when "discussion_time" has elapsed, or members voted for voting.'; 1.68 -COMMENT ON COLUMN "issue"."closed" IS 'Point in time, when "admission_time" or "voting_time" is over, and issue is no longer active'; 1.69 +COMMENT ON COLUMN "issue"."half_frozen" IS 'Point in time, when "discussion_time" has elapsed, or members voted for voting'; 1.70 +COMMENT ON COLUMN "issue"."fully_frozen" IS 'Point in time, when "verification_time" has elapsed'; 1.71 +COMMENT ON COLUMN "issue"."closed" IS 'Point in time, when "admission_time" or "voting_time" have elapsed, and issue is no longer active'; 1.72 COMMENT ON COLUMN "issue"."ranks_available" IS 'TRUE = ranks have been calculated'; 1.73 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.74 COMMENT ON COLUMN "issue"."population" IS 'Calculated from table "direct_population_snapshot"'; 1.75 @@ -300,9 +309,9 @@ 1.76 COMMENT ON COLUMN "delegation"."issue_id" IS 'Reference to issue, if delegation is issue-wide, otherwise NULL'; 1.77 1.78 1.79 -CREATE TYPE "snapshot_event" AS ENUM ('periodic', 'end_of_admission', 'end_of_discussion'); 1.80 +CREATE TYPE "snapshot_event" AS ENUM ('periodic', 'end_of_admission', 'start_of_voting'); 1.81 1.82 -COMMENT ON TYPE "snapshot_event" IS 'Reason for snapshots: ''periodic'' = due to periodic recalculation, ''end_of_admission'' = saved state at end of admission period, ''end_of_discussion'' = saved state at end of discussion period'; 1.83 +COMMENT ON TYPE "snapshot_event" IS 'Reason for snapshots: ''periodic'' = due to periodic recalculation, ''end_of_admission'' = saved state at end of admission period, ''start_of_voting'' = saved state at end of verification period'; 1.84 1.85 1.86 CREATE TABLE "direct_population_snapshot" ( 1.87 @@ -875,7 +884,7 @@ 1.88 1.89 CREATE VIEW "issue_with_ranks_missing" AS 1.90 SELECT * FROM "issue" 1.91 - WHERE "frozen" NOTNULL 1.92 + WHERE "fully_frozen" NOTNULL 1.93 AND "closed" NOTNULL 1.94 AND "ranks_available" = FALSE; 1.95 1.96 @@ -1202,6 +1211,7 @@ 1.97 JOIN "direct_interest_snapshot" 1.98 ON "member"."id" = "direct_interest_snapshot"."member_id" 1.99 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id" 1.100 + AND "event" = 'periodic' 1.101 WHERE "member"."active" 1.102 AND "initiative"."issue_id" = "issue_id_p"; 1.103 RETURN; 1.104 @@ -1391,20 +1401,15 @@ 1.105 RETURNS VOID 1.106 LANGUAGE 'plpgsql' VOLATILE AS $$ 1.107 BEGIN 1.108 - UPDATE "direct_population_snapshot" 1.109 - SET "event" = 'end_of_discussion' 1.110 + UPDATE "direct_population_snapshot" SET "event" = "event_p" 1.111 WHERE "issue_id" = "issue_id_p" AND "event" = 'periodic'; 1.112 - UPDATE "delegating_population_snapshot" 1.113 - SET "event" = 'end_of_discussion' 1.114 + UPDATE "delegating_population_snapshot" SET "event" = "event_p" 1.115 WHERE "issue_id" = "issue_id_p" AND "event" = 'periodic'; 1.116 - UPDATE "direct_interest_snapshot" 1.117 - SET "event" = 'end_of_discussion' 1.118 + UPDATE "direct_interest_snapshot" SET "event" = "event_p" 1.119 WHERE "issue_id" = "issue_id_p" AND "event" = 'periodic'; 1.120 - UPDATE "delegating_interest_snapshot" 1.121 - SET "event" = 'end_of_discussion' 1.122 + UPDATE "delegating_interest_snapshot" SET "event" = "event_p" 1.123 WHERE "issue_id" = "issue_id_p" AND "event" = 'periodic'; 1.124 - UPDATE "direct_supporter_snapshot" 1.125 - SET "event" = 'end_of_discussion' 1.126 + UPDATE "direct_supporter_snapshot" SET "event" = "event_p" 1.127 WHERE "issue_id" = "issue_id_p" AND "event" = 'periodic'; 1.128 RETURN; 1.129 END; 1.130 @@ -1433,8 +1438,12 @@ 1.131 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"; 1.132 SELECT * INTO "policy_row" 1.133 FROM "policy" WHERE "id" = "issue_row"."policy_id"; 1.134 - PERFORM "set_snapshot_event"("issue_id_p", 'end_of_discussion'); 1.135 - UPDATE "issue" SET "frozen" = now() WHERE "id" = "issue_id_p"; 1.136 + PERFORM "set_snapshot_event"("issue_id_p", 'start_of_voting'); 1.137 + UPDATE "issue" SET 1.138 + "accepted" = COALESCE("accepted", now()), 1.139 + "half_frozen" = COALESCE("half_frozen", now()), 1.140 + "fully_frozen" = now() 1.141 + WHERE "id" = "issue_id_p"; 1.142 FOR "initiative_row" IN 1.143 SELECT * FROM "initiative" WHERE "issue_id" = "issue_id_p" 1.144 LOOP 1.145 @@ -1457,7 +1466,7 @@ 1.146 1.147 COMMENT ON FUNCTION "freeze_after_snapshot" 1.148 ( "issue"."id"%TYPE ) 1.149 - IS 'This function freezes an issue, but must only be called when "create_snapshot" was called in the same transaction'; 1.150 + IS 'This function freezes an issue (fully) and starts voting, but must only be called when "create_snapshot" was called in the same transaction'; 1.151 1.152 1.153 CREATE FUNCTION "manual_freeze"("issue_id_p" "issue"."id"%TYPE) 1.154 @@ -1474,7 +1483,7 @@ 1.155 1.156 COMMENT ON FUNCTION "freeze_after_snapshot" 1.157 ( "issue"."id"%TYPE ) 1.158 - IS 'Freeze an issue manually'; 1.159 + IS 'Freeze an issue manually (fully) and start voting'; 1.160 1.161 1.162 1.163 @@ -1923,7 +1932,7 @@ 1.164 IF "issue_row"."closed" ISNULL THEN 1.165 SELECT * INTO "policy_row" FROM "policy" 1.166 WHERE "id" = "issue_row"."policy_id"; 1.167 - IF "issue_row"."frozen" ISNULL THEN 1.168 + IF "issue_row"."fully_frozen" ISNULL THEN 1.169 PERFORM "create_snapshot"("issue_id_p"); 1.170 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"; 1.171 END IF; 1.172 @@ -1935,11 +1944,12 @@ 1.173 AND "supporter_count" * "policy_row"."issue_quorum_den" 1.174 >= "issue_row"."population" * "policy_row"."issue_quorum_num" 1.175 ) THEN 1.176 + PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission'); 1.177 "issue_row"."accepted" = now(); -- NOTE: "issue_row" used later 1.178 UPDATE "issue" SET "accepted" = "issue_row"."accepted" 1.179 WHERE "id" = "issue_row"."id"; 1.180 ELSIF 1.181 - now() > "issue_row"."created" + "policy_row"."admission_time" 1.182 + now() >= "issue_row"."created" + "policy_row"."admission_time" 1.183 THEN 1.184 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission'); 1.185 UPDATE "issue" SET "closed" = now() 1.186 @@ -1948,7 +1958,7 @@ 1.187 END IF; 1.188 IF 1.189 "issue_row"."accepted" NOTNULL AND 1.190 - "issue_row"."frozen" ISNULL 1.191 + "issue_row"."half_frozen" ISNULL 1.192 THEN 1.193 SELECT 1.194 CASE 1.195 @@ -1962,16 +1972,26 @@ 1.196 FROM "issue" WHERE "id" = "issue_id_p"; 1.197 IF 1.198 "voting_requested_v" OR ( 1.199 - "voting_requested_v" ISNULL AND now() > 1.200 - "issue_row"."accepted" + "policy_row"."discussion_time" 1.201 + "voting_requested_v" ISNULL AND 1.202 + now() >= "issue_row"."accepted" + "policy_row"."discussion_time" 1.203 ) 1.204 THEN 1.205 - PERFORM "freeze_after_snapshot"("issue_id_p"); 1.206 + "issue_row"."half_frozen" = now(); -- NOTE: "issue_row" used later 1.207 + UPDATE "issue" SET "half_frozen" = "issue_row"."half_frozen" 1.208 + WHERE "id" = "issue_row"."id"; 1.209 END IF; 1.210 END IF; 1.211 IF 1.212 - "issue_row"."frozen" NOTNULL AND 1.213 - now() > "issue_row"."frozen" + "policy_row"."voting_time" 1.214 + "issue_row"."half_frozen" NOTNULL AND 1.215 + "issue_row"."fully_frozen" ISNULL AND 1.216 + now() >= "issue_row"."half_frozen" + "policy_row"."verification_time" 1.217 + THEN 1.218 + "issue_row"."fully_frozen" = now(); -- NOTE: "issue_row" used later 1.219 + PERFORM "freeze_after_snapshot"("issue_id_p"); 1.220 + END IF; 1.221 + IF 1.222 + "issue_row"."fully_frozen" NOTNULL AND 1.223 + now() >= "issue_row"."fully_frozen" + "policy_row"."voting_time" 1.224 THEN 1.225 PERFORM "close_voting"("issue_id_p"); 1.226 END IF;
2.1 --- a/demo.sql Sat Nov 07 12:00:00 2009 +0100 2.2 +++ b/demo.sql Sun Nov 15 12:00:00 2009 +0100 2.3 @@ -32,15 +32,32 @@ 2.4 2.5 INSERT INTO "policy" ( 2.6 "name", 2.7 - "admission_time", "discussion_time", "voting_time", 2.8 + "admission_time", 2.9 + "discussion_time", 2.10 + "verification_time", 2.11 + "voting_time", 2.12 "issue_quorum_num", "issue_quorum_den", 2.13 "initiative_quorum_num", "initiative_quorum_den" 2.14 ) VALUES ( 2.15 'Default policy', 2.16 - '1 hour', '1 hour', '1 hour', 2.17 + '1 hour', '1 hour', '1 hour', '1 hour', 2.18 25, 100, 2.19 20, 100 ); 2.20 2.21 +CREATE FUNCTION "time_warp"() RETURNS VOID 2.22 + LANGUAGE 'plpgsql' VOLATILE AS $$ 2.23 + BEGIN 2.24 + UPDATE "issue" SET 2.25 + "snapshot" = "snapshot" - '1 hour 1 minute'::INTERVAL, 2.26 + "created" = "created" - '1 hour 1 minute'::INTERVAL, 2.27 + "accepted" = "accepted" - '1 hour 1 minute'::INTERVAL, 2.28 + "half_frozen" = "half_frozen" - '1 hour 1 minute'::INTERVAL, 2.29 + "fully_frozen" = "fully_frozen" - '1 hour 1 minute'::INTERVAL; 2.30 + PERFORM "check_everything"(); 2.31 + RETURN; 2.32 + END; 2.33 + $$; 2.34 + 2.35 INSERT INTO "area" ("name") VALUES 2.36 ('Area #1'), -- id 1 2.37 ('Area #2'), -- id 2 2.38 @@ -157,10 +174,9 @@ 2.39 INSERT INTO "opinion" ("member_id", "suggestion_id", "degree", "fulfilled") VALUES 2.40 (19, 1, 2, FALSE); 2.41 2.42 -UPDATE "issue" SET "created" = "created" - '3 hour 3 minute'::INTERVAL; 2.43 -SELECT check_everything(); 2.44 -UPDATE "issue" SET "accepted" = "accepted" - '2 hour 2 minute'::INTERVAL; 2.45 -SELECT check_everything(); 2.46 +SELECT "time_warp"(); 2.47 +SELECT "time_warp"(); 2.48 +SELECT "time_warp"(); 2.49 2.50 INSERT INTO "direct_voter" ("member_id", "issue_id") VALUES 2.51 ( 8, 1), 2.52 @@ -219,10 +235,9 @@ 2.53 (20, 1, 5, 1), 2.54 (21, 1, 5, -1); 2.55 2.56 -UPDATE "issue" SET 2.57 - "snapshot" = "snapshot" - '1 hour 1 minute'::INTERVAL, 2.58 - "frozen" = "frozen" - '1 hour 1 minute'::INTERVAL; 2.59 -SELECT check_everything(); 2.60 +SELECT "time_warp"(); 2.61 + 2.62 +DROP FUNCTION "time_warp"(); 2.63 2.64 END; 2.65