liquid_feedback_core
changeset 526:2a2f76da1177
Merged experimental work on alternative mechanism to the first (issue) quorum
author | jbe |
---|---|
date | Fri May 06 10:34:05 2016 +0200 (2016-05-06) |
parents | b81a90434b8b 003b4cc8e9ef |
children | eaa4836e04ee |
files | core.sql |
line diff
1.1 --- a/core.sql Fri May 06 09:35:23 2016 +0200 1.2 +++ b/core.sql Fri May 06 10:34:05 2016 +0200 1.3 @@ -7,7 +7,7 @@ 1.4 BEGIN; 1.5 1.6 CREATE VIEW "liquid_feedback_version" AS 1.7 - SELECT * FROM (VALUES ('3.2.2', 3, 2, 2)) 1.8 + SELECT * FROM (VALUES ('4.0.0', 4, 0, 0)) 1.9 AS "subquery"("string", "major", "minor", "revision"); 1.10 1.11 1.12 @@ -371,8 +371,7 @@ 1.13 "discussion_time" INTERVAL, 1.14 "verification_time" INTERVAL, 1.15 "voting_time" INTERVAL, 1.16 - "issue_quorum_num" INT4, 1.17 - "issue_quorum_den" INT4, 1.18 + "issue_quorum" INT4 NOT NULL, 1.19 "initiative_quorum_num" INT4 NOT NULL, 1.20 "initiative_quorum_den" INT4 NOT NULL, 1.21 "defeat_strength" "defeat_strength" NOT NULL DEFAULT 'tuple', 1.22 @@ -389,6 +388,8 @@ 1.23 "indirect_majority_non_negative" INT4 NOT NULL DEFAULT 0, 1.24 "no_reverse_beat_path" BOOLEAN NOT NULL DEFAULT FALSE, 1.25 "no_multistage_majority" BOOLEAN NOT NULL DEFAULT FALSE, 1.26 + CONSTRAINT "issue_quorum_if_and_only_if_not_polling" CHECK ( 1.27 + "polling" = ("issue_quorum" ISNULL) ), 1.28 CONSTRAINT "timing" CHECK ( 1.29 ( "polling" = FALSE AND 1.30 "min_admission_time" NOTNULL AND "max_admission_time" NOTNULL AND 1.31 @@ -406,9 +407,6 @@ 1.32 "discussion_time" ISNULL AND 1.33 "verification_time" ISNULL AND 1.34 "voting_time" ISNULL ) ), 1.35 - CONSTRAINT "issue_quorum_if_and_only_if_not_polling" CHECK ( 1.36 - "polling" = ("issue_quorum_num" ISNULL) AND 1.37 - "polling" = ("issue_quorum_den" ISNULL) ), 1.38 CONSTRAINT "no_reverse_beat_path_requires_tuple_defeat_strength" CHECK ( 1.39 "defeat_strength" = 'tuple'::"defeat_strength" OR 1.40 "no_reverse_beat_path" = FALSE ) ); 1.41 @@ -424,9 +422,8 @@ 1.42 COMMENT ON COLUMN "policy"."discussion_time" IS 'Duration of issue state ''discussion''; Regular time until an issue is "half_frozen" after being "accepted"'; 1.43 COMMENT ON COLUMN "policy"."verification_time" IS 'Duration of issue state ''verification''; Regular time until an issue is "fully_frozen" (e.g. entering issue state ''voting'') after being "half_frozen"'; 1.44 COMMENT ON COLUMN "policy"."voting_time" IS 'Duration of issue state ''voting''; Time after an issue is "fully_frozen" but not "closed" (duration of issue state ''voting'')'; 1.45 -COMMENT ON COLUMN "policy"."issue_quorum_num" IS 'Numerator of potential supporter quorum to be reached by one initiative of an issue to be "accepted" and enter issue state ''discussion'''; 1.46 -COMMENT ON COLUMN "policy"."issue_quorum_den" IS 'Denominator of potential supporter quorum to be reached by one initiative of an issue to be "accepted" and enter issue state ''discussion'''; 1.47 -COMMENT ON COLUMN "policy"."initiative_quorum_num" IS 'Numerator of satisfied supporter quorum to be reached by an initiative to be "admitted" for voting'; 1.48 +COMMENT ON COLUMN "policy"."issue_quorum" IS 'Minimum number of supporters needed for one initiative of an issue to allow the issue to pass from ''admission'' to ''discussion'' state (Note: further requirements apply, see tables "admission_rule" and "admission_rule_condition")'; 1.49 +COMMENT ON COLUMN "policy"."initiative_quorum_num" IS 'Numerator of satisfied supporter quorum to be reached by an initiative to be "admitted" for voting'; 1.50 COMMENT ON COLUMN "policy"."initiative_quorum_den" IS 'Denominator of satisfied supporter quorum to be reached by an initiative to be "admitted" for voting'; 1.51 COMMENT ON COLUMN "policy"."defeat_strength" IS 'How pairwise defeats are measured for the Schulze method; see type "defeat_strength"; ''tuple'' is the recommended setting'; 1.52 COMMENT ON COLUMN "policy"."tie_breaking" IS 'Tie-breaker for the Schulze method; see type "tie_breaking"; ''variant1'' or ''variant2'' are recommended'; 1.53 @@ -491,8 +488,9 @@ 1.54 1.55 1.56 CREATE TABLE "area" ( 1.57 + UNIQUE ("unit_id", "id"), -- index needed for foreign-key on table "admission_rule_condition" 1.58 + "unit_id" INT4 NOT NULL REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.59 "id" SERIAL4 PRIMARY KEY, 1.60 - "unit_id" INT4 NOT NULL REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.61 "active" BOOLEAN NOT NULL DEFAULT TRUE, 1.62 "name" TEXT NOT NULL, 1.63 "description" TEXT NOT NULL DEFAULT '', 1.64 @@ -548,6 +546,36 @@ 1.65 COMMENT ON COLUMN "allowed_policy"."default_policy" IS 'One policy per area can be set as default.'; 1.66 1.67 1.68 +CREATE TABLE "admission_rule" ( 1.69 + UNIQUE ("unit_id", "id"), -- index needed for foreign-key on table "admission_rule_condition" 1.70 + "unit_id" INT4 NOT NULL REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.71 + "id" SERIAL4 PRIMARY KEY ); 1.72 + 1.73 +COMMENT ON TABLE "admission_rule" IS 'Groups entries in "admission_rule_condition" to regulate how many issues may pass from ''admission'' to ''discussion'' state in a given time'; 1.74 + 1.75 + 1.76 +CREATE TABLE "admission_rule_condition" ( 1.77 + "unit_id" INT4 NOT NULL, 1.78 + "admission_rule_id" INT4, 1.79 + FOREIGN KEY ("unit_id", "admission_rule_id") REFERENCES "admission_rule" ("unit_id", "id") ON DELETE CASCADE ON UPDATE CASCADE, 1.80 + "policy_id" INT4 REFERENCES "policy" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.81 + "area_id" INT4, 1.82 + FOREIGN KEY ("unit_id", "area_id") REFERENCES "area" ("unit_id", "id") ON DELETE CASCADE ON UPDATE CASCADE, 1.83 + "holdoff_time" INTERVAL NOT NULL ); 1.84 +CREATE UNIQUE INDEX "admission_rule_condition_unit_idx" ON "admission_rule_condition" ("admission_rule_id") WHERE "policy_id" ISNULL AND "area_id" ISNULL; 1.85 +CREATE UNIQUE INDEX "admission_rule_condition_policy_idx" ON "admission_rule_condition" ("admission_rule_id", "policy_id") WHERE "area_id" ISNULL; 1.86 +CREATE UNIQUE INDEX "admission_rule_condition_area_idx" ON "admission_rule_condition" ("admission_rule_id", "area_id") WHERE "policy_id" ISNULL; 1.87 +CREATE UNIQUE INDEX "admission_rule_condition_policy_area_idx" ON "admission_rule_condition" ("admission_rule_id", "policy_id", "area_id"); 1.88 + 1.89 +COMMENT ON TABLE "admission_rule_condition" IS 'Regulates how many issues may pass from ''admission'' to ''discussion'' state in a given time; See definition of "issue_for_admission" view for details'; 1.90 + 1.91 +COMMENT ON COLUMN "admission_rule_condition"."unit_id" IS 'Grouped "admission_rule_condition" rows must have the same "unit_id"'; 1.92 +COMMENT ON COLUMN "admission_rule_condition"."admission_rule_id" IS 'Grouping several "admission_rule_condition" rows'; 1.93 +COMMENT ON COLUMN "admission_rule_condition"."policy_id" IS 'Set to link the condition with a given policy, NULL for any policy in the issue'; 1.94 +COMMENT ON COLUMN "admission_rule_condition"."area_id" IS 'Set to link the condition with a given policy, NULL for any area in the issue'; 1.95 +COMMENT ON COLUMN "admission_rule_condition"."holdoff_time" IS 'After an issue in the given unit, policy, and/or area has been admitted, the "admission_rule" is disabled for the selected "holdoff_time", e.g. a "holdoff_time" of ''6 hours'' causes four issues per day to be admitted'; 1.96 + 1.97 + 1.98 CREATE TYPE "snapshot_event" AS ENUM ('periodic', 'end_of_admission', 'half_freeze', 'full_freeze'); 1.99 1.100 COMMENT ON TYPE "snapshot_event" IS 'Reason for snapshots: ''periodic'' = due to periodic recalculation, ''end_of_admission'' = saved state at end of admission period, ''half_freeze'' = saved state at end of discussion period, ''full_freeze'' = saved state at end of verification period'; 1.101 @@ -643,7 +671,7 @@ 1.102 COMMENT ON COLUMN "issue"."admin_notice" IS 'Public notice by admin to explain manual interventions, or to announce corrections'; 1.103 COMMENT ON COLUMN "issue"."external_reference" IS 'Opaque data field to store an external reference'; 1.104 COMMENT ON COLUMN "issue"."phase_finished" IS 'Set to a value NOTNULL, if the current phase has finished, but calculations are pending; No changes in this issue shall be made by the frontend or API when this value is set'; 1.105 -COMMENT ON COLUMN "issue"."accepted" IS 'Point in time, when one initiative of issue reached the "issue_quorum"'; 1.106 +COMMENT ON COLUMN "issue"."accepted" IS 'Point in time, when one initiative of issue reached the "issue_quorum" (TODO)'; 1.107 COMMENT ON COLUMN "issue"."half_frozen" IS 'Point in time, when "discussion_time" has elapsed; Frontends must ensure that for half_frozen issues a) initiatives are not revoked, b) no new drafts are created, c) no initiators are added or removed.'; 1.108 COMMENT ON COLUMN "issue"."fully_frozen" IS 'Point in time, when "verification_time" has elapsed and voting has started; 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.109 COMMENT ON COLUMN "issue"."closed" IS 'Point in time, when "max_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.110 @@ -1789,6 +1817,25 @@ 1.111 -------------------------------------------------------------------- 1.112 1.113 1.114 +CREATE FUNCTION "autofill_unit_id_from_admission_rule_trigger"() 1.115 + RETURNS TRIGGER 1.116 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.117 + BEGIN 1.118 + IF NEW."unit_id" ISNULL THEN 1.119 + SELECT "unit_id" INTO NEW."unit_id" 1.120 + FROM "admission_rule" WHERE "id" = NEW."admission_rule_id"; 1.121 + END IF; 1.122 + RETURN NEW; 1.123 + END; 1.124 + $$; 1.125 + 1.126 +CREATE TRIGGER "autofill_unit_id" BEFORE INSERT ON "admission_rule_condition" 1.127 + FOR EACH ROW EXECUTE PROCEDURE "autofill_unit_id_from_admission_rule_trigger"(); 1.128 + 1.129 +COMMENT ON FUNCTION "autofill_unit_id_from_admission_rule_trigger"() IS 'Implementation of trigger "autofill_admission_rule_id" on table "admission_rule_entry"'; 1.130 +COMMENT ON TRIGGER "autofill_unit_id" ON "admission_rule_condition" IS 'Set "unit_id" field automatically, if NULL'; 1.131 + 1.132 + 1.133 CREATE FUNCTION "autofill_issue_id_trigger"() 1.134 RETURNS TRIGGER 1.135 LANGUAGE 'plpgsql' VOLATILE AS $$ 1.136 @@ -1973,6 +2020,68 @@ 1.137 COMMENT ON VIEW "member_to_notify" IS 'Filtered "member" table containing only members that are eligible to and wish to receive notifications; NOTE: "notify_email" may still be NULL and might need to be checked by frontend (this allows other means of messaging)'; 1.138 1.139 1.140 +CREATE VIEW "matching_admission_rule_condition" AS 1.141 + SELECT DISTINCT ON ("issue_id", "admission_rule_condition"."admission_rule_id") 1.142 + "issue"."id" AS "issue_id", 1.143 + "admission_rule_condition".* 1.144 + FROM "admission_rule_condition" 1.145 + JOIN "area" ON "admission_rule_condition"."unit_id" = "area"."unit_id" 1.146 + JOIN "issue" ON "area"."id" = "issue"."area_id" 1.147 + WHERE ( 1.148 + "admission_rule_condition"."policy_id" ISNULL OR 1.149 + "admission_rule_condition"."policy_id" = "issue"."policy_id" 1.150 + ) AND ( 1.151 + "admission_rule_condition"."area_id" ISNULL OR 1.152 + "admission_rule_condition"."area_id" = "area"."id" 1.153 + ) 1.154 + ORDER BY 1.155 + "issue_id", 1.156 + "admission_rule_condition"."admission_rule_id", 1.157 + "admission_rule_condition"."policy_id" ISNULL, 1.158 + "admission_rule_condition"."area_id" ISNULL; 1.159 + 1.160 +COMMENT ON VIEW "matching_admission_rule_condition" IS 'Selects the most fitting "admission_rule_condition" for a given pair of "issue" and "admission_rule"'; 1.161 + 1.162 + 1.163 +CREATE VIEW "applicable_admission_rule" AS 1.164 + SELECT * FROM "admission_rule" 1.165 + WHERE NOT EXISTS ( 1.166 + SELECT NULL FROM "issue" 1.167 + JOIN "matching_admission_rule_condition" AS "condition" 1.168 + ON "issue"."id" = "condition"."issue_id" 1.169 + WHERE "condition"."admission_rule_id" = "admission_rule"."id" 1.170 + AND "issue"."accepted" > now() - "condition"."holdoff_time" 1.171 + ); 1.172 + 1.173 +COMMENT ON VIEW "applicable_admission_rule" IS 'Filters "admission_rule"s which are not blocked by a recently admitted issue'; 1.174 + 1.175 + 1.176 +CREATE VIEW "issue_for_admission" AS 1.177 + SELECT 1.178 + "issue".*, 1.179 + max("initiative"."supporter_count") AS "max_supporter_count" 1.180 + FROM "issue" 1.181 + JOIN "initiative" ON "issue"."id" = "initiative"."issue_id" 1.182 + JOIN "area" ON "issue"."area_id" = "area"."id" 1.183 + JOIN "admission_rule_condition" 1.184 + ON "admission_rule_condition"."unit_id" = "area"."unit_id" 1.185 + AND ( 1.186 + "admission_rule_condition"."policy_id" ISNULL OR 1.187 + "admission_rule_condition"."policy_id" = "issue"."policy_id" 1.188 + ) 1.189 + AND ( 1.190 + "admission_rule_condition"."area_id" ISNULL OR 1.191 + "admission_rule_condition"."area_id" = "area"."id" 1.192 + ) 1.193 + JOIN "applicable_admission_rule" 1.194 + ON "admission_rule_condition"."admission_rule_id" = "applicable_admission_rule"."id" 1.195 + WHERE "issue"."state" = 'admission'::"issue_state" 1.196 + GROUP BY "issue"."id" 1.197 + ORDER BY "max_supporter_count" DESC, "issue"."id"; 1.198 + 1.199 +COMMENT ON VIEW "issue_for_admission" IS 'Issues that are eligible to pass from ''admission'' to ''discussion'' state; needs to be recalculated after admitting the first issue in this view'; 1.200 + 1.201 + 1.202 CREATE VIEW "unit_delegation" AS 1.203 SELECT 1.204 "unit"."id" AS "unit_id", 1.205 @@ -4933,12 +5042,10 @@ 1.206 IF 1.207 ( now() >= 1.208 "issue_row"."created" + "issue_row"."min_admission_time" ) AND 1.209 + -- TODO: implement new mechanism for issue admission 1.210 EXISTS ( 1.211 - SELECT NULL FROM "initiative" 1.212 - WHERE "issue_id" = "issue_id_p" 1.213 - AND "supporter_count" > 0 1.214 - AND "supporter_count" * "policy_row"."issue_quorum_den" 1.215 - >= "issue_row"."population" * "policy_row"."issue_quorum_num" 1.216 + SELECT NULL FROM "initiative" WHERE "issue_id" = "issue_id_p" 1.217 + AND "supporter_count" >= "policy_row"."issue_quorum" 1.218 ) 1.219 THEN 1.220 UPDATE "issue" SET
2.1 --- a/test.sql Fri May 06 09:35:23 2016 +0200 2.2 +++ b/test.sql Fri May 06 10:34:05 2016 +0200 2.3 @@ -42,7 +42,7 @@ 2.4 "discussion_time", 2.5 "verification_time", 2.6 "voting_time", 2.7 - "issue_quorum_num", "issue_quorum_den", 2.8 + "issue_quorum", 2.9 "initiative_quorum_num", "initiative_quorum_den", 2.10 "direct_majority_num", "direct_majority_den", "direct_majority_strict", 2.11 "no_reverse_beat_path", "no_multistage_majority" 2.12 @@ -50,7 +50,7 @@ 2.13 1, 2.14 'Default policy', 2.15 '0', '1 hour', '1 hour', '1 hour', '1 hour', 2.16 - 25, 100, 2.17 + 3, 2.18 20, 100, 2.19 1, 2, TRUE, 2.20 TRUE, FALSE ); 2.21 @@ -422,8 +422,7 @@ 2.22 "discussion_time", 2.23 "verification_time", 2.24 "voting_time", 2.25 - "issue_quorum_num", 2.26 - "issue_quorum_den", 2.27 + "issue_quorum", 2.28 "initiative_quorum_num", 2.29 "initiative_quorum_den" 2.30 ) VALUES ( 2.31 @@ -436,7 +435,7 @@ 2.32 '1 second', 2.33 '1 second', 2.34 '1 second', 2.35 - 0, 100, 2.36 + 1, 2.37 0, 100 2.38 ), ( 2.39 1, 2.40 @@ -448,7 +447,7 @@ 2.41 '2 days', 2.42 '1 second', 2.43 '1 second', 2.44 - 0, 100, 2.45 + 1, 2.46 0, 100 2.47 ), ( 2.48 1, 2.49 @@ -460,7 +459,7 @@ 2.50 '5 minutes', 2.51 '2 days', 2.52 '1 second', 2.53 - 0, 100, 2.54 + 1, 2.55 0, 100 2.56 ), ( 2.57 1, 2.58 @@ -472,7 +471,7 @@ 2.59 '5 minutes', 2.60 '1 second', 2.61 '2 days', 2.62 - 0, 100, 2.63 + 1, 2.64 0, 100 2.65 ); 2.66