liquid_feedback_core
changeset 457:ca21a3f49e4c
Draft for an alternative mechanism to the first (issue) quorum
author | jbe |
---|---|
date | Fri Dec 11 23:44:02 2015 +0100 (2015-12-11) |
parents | a9c4521466e6 |
children | fae00a5c1c71 |
files | core.sql test.sql |
line diff
1.1 --- a/core.sql Fri Dec 04 14:02:19 2015 +0100 1.2 +++ b/core.sql Fri Dec 11 23:44:02 2015 +0100 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.1.0', 3, 1, 0)) 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 @@ -366,8 +366,6 @@ 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 "initiative_quorum_num" INT4 NOT NULL, 1.19 "initiative_quorum_den" INT4 NOT NULL, 1.20 "defeat_strength" "defeat_strength" NOT NULL DEFAULT 'tuple', 1.21 @@ -401,9 +399,6 @@ 1.22 "discussion_time" ISNULL AND 1.23 "verification_time" ISNULL AND 1.24 "voting_time" ISNULL ) ), 1.25 - CONSTRAINT "issue_quorum_if_and_only_if_not_polling" CHECK ( 1.26 - "polling" = ("issue_quorum_num" ISNULL) AND 1.27 - "polling" = ("issue_quorum_den" ISNULL) ), 1.28 CONSTRAINT "no_reverse_beat_path_requires_tuple_defeat_strength" CHECK ( 1.29 "defeat_strength" = 'tuple'::"defeat_strength" OR 1.30 "no_reverse_beat_path" = FALSE ) ); 1.31 @@ -419,8 +414,6 @@ 1.32 COMMENT ON COLUMN "policy"."discussion_time" IS 'Duration of issue state ''discussion''; Regular time until an issue is "half_frozen" after being "accepted"'; 1.33 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.34 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.35 -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.36 -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.37 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.38 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.39 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.40 @@ -477,8 +470,9 @@ 1.41 1.42 1.43 CREATE TABLE "area" ( 1.44 + UNIQUE ("unit_id", "id"), -- index needed for foreign-key on table "admission_rule_condition" 1.45 + "unit_id" INT4 NOT NULL REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.46 "id" SERIAL4 PRIMARY KEY, 1.47 - "unit_id" INT4 NOT NULL REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.48 "active" BOOLEAN NOT NULL DEFAULT TRUE, 1.49 "name" TEXT NOT NULL, 1.50 "description" TEXT NOT NULL DEFAULT '', 1.51 @@ -525,6 +519,36 @@ 1.52 COMMENT ON COLUMN "allowed_policy"."default_policy" IS 'One policy per area can be set as default.'; 1.53 1.54 1.55 +CREATE TABLE "admission_rule" ( 1.56 + UNIQUE ("unit_id", "id"), -- index needed for foreign-key on table "admission_rule_condition" 1.57 + "unit_id" INT4 NOT NULL REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.58 + "id" SERIAL4 PRIMARY KEY ); 1.59 + 1.60 +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.61 + 1.62 + 1.63 +CREATE TABLE "admission_rule_condition" ( 1.64 + "unit_id" INT4 NOT NULL, 1.65 + "admission_rule_id" INT4, 1.66 + FOREIGN KEY ("unit_id", "admission_rule_id") REFERENCES "admission_rule" ("unit_id", "id") ON DELETE CASCADE ON UPDATE CASCADE, 1.67 + "policy_id" INT4 REFERENCES "policy" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.68 + "area_id" INT4, 1.69 + FOREIGN KEY ("unit_id", "area_id") REFERENCES "area" ("unit_id", "id") ON DELETE CASCADE ON UPDATE CASCADE, 1.70 + "holdoff_time" INTERVAL NOT NULL ); 1.71 +CREATE UNIQUE INDEX "admission_rule_condition_unit_idx" ON "admission_rule_condition" ("admission_rule_id") WHERE "policy_id" ISNULL AND "area_id" ISNULL; 1.72 +CREATE UNIQUE INDEX "admission_rule_condition_policy_idx" ON "admission_rule_condition" ("admission_rule_id", "policy_id") WHERE "area_id" ISNULL; 1.73 +CREATE UNIQUE INDEX "admission_rule_condition_area_idx" ON "admission_rule_condition" ("admission_rule_id", "area_id") WHERE "policy_id" ISNULL; 1.74 +CREATE UNIQUE INDEX "admission_rule_condition_policy_area_idx" ON "admission_rule_condition" ("admission_rule_id", "policy_id", "area_id"); 1.75 + 1.76 +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.77 + 1.78 +COMMENT ON COLUMN "admission_rule_condition"."unit_id" IS 'Grouped "admission_rule_condition" rows must have the same "unit_id"'; 1.79 +COMMENT ON COLUMN "admission_rule_condition"."admission_rule_id" IS 'Grouping several "admission_rule_condition" rows'; 1.80 +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.81 +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.82 +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.83 + 1.84 + 1.85 CREATE TYPE "snapshot_event" AS ENUM ('periodic', 'end_of_admission', 'half_freeze', 'full_freeze'); 1.86 1.87 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.88 @@ -620,7 +644,7 @@ 1.89 COMMENT ON COLUMN "issue"."admin_notice" IS 'Public notice by admin to explain manual interventions, or to announce corrections'; 1.90 COMMENT ON COLUMN "issue"."external_reference" IS 'Opaque data field to store an external reference'; 1.91 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.92 -COMMENT ON COLUMN "issue"."accepted" IS 'Point in time, when one initiative of issue reached the "issue_quorum"'; 1.93 +COMMENT ON COLUMN "issue"."accepted" IS 'Point in time, when one initiative of issue reached the "issue_quorum" (TODO)'; 1.94 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.95 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.96 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.97 @@ -1729,6 +1753,25 @@ 1.98 -------------------------------------------------------------------- 1.99 1.100 1.101 +CREATE FUNCTION "autofill_unit_id_from_admission_rule_trigger"() 1.102 + RETURNS TRIGGER 1.103 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.104 + BEGIN 1.105 + IF NEW."unit_id" ISNULL THEN 1.106 + SELECT "unit_id" INTO NEW."unit_id" 1.107 + FROM "admission_rule" WHERE "id" = NEW."admission_rule_id"; 1.108 + END IF; 1.109 + RETURN NEW; 1.110 + END; 1.111 + $$; 1.112 + 1.113 +CREATE TRIGGER "autofill_unit_id" BEFORE INSERT ON "admission_rule_condition" 1.114 + FOR EACH ROW EXECUTE PROCEDURE "autofill_unit_id_from_admission_rule_trigger"(); 1.115 + 1.116 +COMMENT ON FUNCTION "autofill_unit_id_from_admission_rule_trigger"() IS 'Implementation of trigger "autofill_admission_rule_id" on table "admission_rule_entry"'; 1.117 +COMMENT ON TRIGGER "autofill_unit_id" ON "admission_rule_condition" IS 'Set "unit_id" field automatically, if NULL'; 1.118 + 1.119 + 1.120 CREATE FUNCTION "autofill_issue_id_trigger"() 1.121 RETURNS TRIGGER 1.122 LANGUAGE 'plpgsql' VOLATILE AS $$ 1.123 @@ -1899,6 +1942,68 @@ 1.124 ------------------------------------------ 1.125 1.126 1.127 +CREATE VIEW "matching_admission_rule_condition" AS 1.128 + SELECT DISTINCT ON ("issue_id", "admission_rule_condition"."admission_rule_id") 1.129 + "issue"."id" AS "issue_id", 1.130 + "admission_rule_condition".* 1.131 + FROM "admission_rule_condition" 1.132 + JOIN "area" ON "admission_rule_condition"."unit_id" = "area"."unit_id" 1.133 + JOIN "issue" ON "area"."id" = "issue"."area_id" 1.134 + WHERE ( 1.135 + "admission_rule_condition"."policy_id" ISNULL OR 1.136 + "admission_rule_condition"."policy_id" = "issue"."policy_id" 1.137 + ) AND ( 1.138 + "admission_rule_condition"."area_id" ISNULL OR 1.139 + "admission_rule_condition"."area_id" = "area"."id" 1.140 + ) 1.141 + ORDER BY 1.142 + "issue_id", 1.143 + "admission_rule_condition"."admission_rule_id", 1.144 + "admission_rule_condition"."policy_id" ISNULL, 1.145 + "admission_rule_condition"."area_id" ISNULL; 1.146 + 1.147 +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.148 + 1.149 + 1.150 +CREATE VIEW "applicable_admission_rule" AS 1.151 + SELECT * FROM "admission_rule" 1.152 + WHERE NOT EXISTS ( 1.153 + SELECT NULL FROM "issue" 1.154 + JOIN "matching_admission_rule_condition" AS "condition" 1.155 + ON "issue"."id" = "condition"."issue_id" 1.156 + WHERE "condition"."admission_rule_id" = "admission_rule"."id" 1.157 + AND "issue"."accepted" > now() - "condition"."holdoff_time" 1.158 + ); 1.159 + 1.160 +COMMENT ON VIEW "applicable_admission_rule" IS 'Filters "admission_rule"s which are not blocked by a recently admitted issue'; 1.161 + 1.162 + 1.163 +CREATE VIEW "issue_for_admission" AS 1.164 + SELECT 1.165 + "issue".*, 1.166 + max("initiative"."supporter_count") AS "max_supporter_count" 1.167 + FROM "issue" 1.168 + JOIN "initiative" ON "issue"."id" = "initiative"."issue_id" 1.169 + JOIN "area" ON "issue"."area_id" = "area"."id" 1.170 + JOIN "admission_rule_condition" 1.171 + ON "admission_rule_condition"."unit_id" = "area"."unit_id" 1.172 + AND ( 1.173 + "admission_rule_condition"."policy_id" ISNULL OR 1.174 + "admission_rule_condition"."policy_id" = "issue"."policy_id" 1.175 + ) 1.176 + AND ( 1.177 + "admission_rule_condition"."area_id" ISNULL OR 1.178 + "admission_rule_condition"."area_id" = "area"."id" 1.179 + ) 1.180 + JOIN "applicable_admission_rule" 1.181 + ON "admission_rule_condition"."admission_rule_id" = "applicable_admission_rule"."id" 1.182 + WHERE "issue"."state" = 'admission'::"issue_state" 1.183 + GROUP BY "issue"."id" 1.184 + ORDER BY "max_supporter_count" DESC, "issue"."id"; 1.185 + 1.186 +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.187 + 1.188 + 1.189 CREATE VIEW "unit_delegation" AS 1.190 SELECT 1.191 "unit"."id" AS "unit_id", 1.192 @@ -4523,14 +4628,16 @@ 1.193 FROM "policy" WHERE "id" = "issue_row"."policy_id"; 1.194 IF 1.195 ( now() >= 1.196 - "issue_row"."created" + "issue_row"."min_admission_time" ) AND 1.197 - EXISTS ( 1.198 - SELECT NULL FROM "initiative" 1.199 - WHERE "issue_id" = "issue_id_p" 1.200 - AND "supporter_count" > 0 1.201 - AND "supporter_count" * "policy_row"."issue_quorum_den" 1.202 - >= "issue_row"."population" * "policy_row"."issue_quorum_num" 1.203 - ) 1.204 + "issue_row"."created" + "issue_row"."min_admission_time" ) --AND 1.205 + -- TODO: implement new mechanism for issue admission 1.206 + -- 1.207 + --EXISTS ( 1.208 + -- SELECT NULL FROM "initiative" 1.209 + -- WHERE "issue_id" = "issue_id_p" 1.210 + -- AND "supporter_count" > 0 1.211 + -- AND "supporter_count" * "policy_row"."issue_quorum_den" 1.212 + -- >= "issue_row"."population" * "policy_row"."issue_quorum_num" 1.213 + --) 1.214 THEN 1.215 UPDATE "issue" SET 1.216 "state" = 'discussion',
2.1 --- a/test.sql Fri Dec 04 14:02:19 2015 +0100 2.2 +++ b/test.sql Fri Dec 11 23:44:02 2015 +0100 2.3 @@ -42,7 +42,6 @@ 2.4 "discussion_time", 2.5 "verification_time", 2.6 "voting_time", 2.7 - "issue_quorum_num", "issue_quorum_den", 2.8 "initiative_quorum_num", "initiative_quorum_den", 2.9 "direct_majority_num", "direct_majority_den", "direct_majority_strict", 2.10 "no_reverse_beat_path", "no_multistage_majority" 2.11 @@ -50,7 +49,6 @@ 2.12 1, 2.13 'Default policy', 2.14 '0', '1 hour', '1 hour', '1 hour', '1 hour', 2.15 - 25, 100, 2.16 20, 100, 2.17 1, 2, TRUE, 2.18 TRUE, FALSE ); 2.19 @@ -422,8 +420,6 @@ 2.20 "discussion_time", 2.21 "verification_time", 2.22 "voting_time", 2.23 - "issue_quorum_num", 2.24 - "issue_quorum_den", 2.25 "initiative_quorum_num", 2.26 "initiative_quorum_den" 2.27 ) VALUES ( 2.28 @@ -436,7 +432,6 @@ 2.29 '1 second', 2.30 '1 second', 2.31 '1 second', 2.32 - 0, 100, 2.33 0, 100 2.34 ), ( 2.35 1, 2.36 @@ -448,7 +443,6 @@ 2.37 '2 days', 2.38 '1 second', 2.39 '1 second', 2.40 - 0, 100, 2.41 0, 100 2.42 ), ( 2.43 1, 2.44 @@ -460,7 +454,6 @@ 2.45 '5 minutes', 2.46 '2 days', 2.47 '1 second', 2.48 - 0, 100, 2.49 0, 100 2.50 ), ( 2.51 1, 2.52 @@ -472,7 +465,6 @@ 2.53 '5 minutes', 2.54 '1 second', 2.55 '2 days', 2.56 - 0, 100, 2.57 0, 100 2.58 ); 2.59