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