liquid_feedback_core

changeset 519:003b4cc8e9ef

Merged experimental work on alternative mechanism to the first (issue) quorum
author jbe
date Tue May 03 20:35:30 2016 +0200 (2016-05-03)
parents b1a7ebf67470 fae00a5c1c71
children 2a2f76da1177
files core.sql
line diff
     1.1 --- a/core.sql	Sat Apr 30 17:50:48 2016 +0200
     1.2 +++ b/core.sql	Tue May 03 20:35:30 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.1', 3, 2, 1))
     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 @@ -1959,6 +2006,68 @@
   1.137  ------------------------------------------
   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 @@ -4918,12 +5027,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	Sat Apr 30 17:50:48 2016 +0200
     2.2 +++ b/test.sql	Tue May 03 20:35:30 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  

Impressum / About Us