1.1 --- a/update/core-update.v3.2.2-v4.0.0.sql	Thu Sep 28 20:12:23 2017 +0200
     1.2 +++ b/update/core-update.v3.2.2-v4.0.0.sql	Fri Sep 29 01:57:43 2017 +0200
     1.3 @@ -550,6 +550,8 @@
     1.4  ALTER TABLE "issue" ADD COLUMN "admission_snapshot_id"   INT8 REFERENCES "snapshot" ("id") ON DELETE SET NULL ON UPDATE CASCADE;
     1.5  ALTER TABLE "issue" ADD COLUMN "half_freeze_snapshot_id" INT8;
     1.6  ALTER TABLE "issue" ADD COLUMN "full_freeze_snapshot_id" INT8;
     1.7 +ALTER TABLE "issue" ADD COLUMN "issue_quorum"            INT4;
     1.8 +ALTER TABLE "issue" ADD COLUMN "initiative_quorum"       INT4;
     1.9  
    1.10  ALTER TABLE "issue" ADD FOREIGN KEY ("id", "half_freeze_snapshot_id")
    1.11    REFERENCES "snapshot" ("issue_id", "id") ON DELETE RESTRICT ON UPDATE CASCADE;
    1.12 @@ -572,6 +574,8 @@
    1.13  COMMENT ON COLUMN "issue"."admission_snapshot_id"   IS 'Snapshot id when issue as accepted or canceled in admission phase';
    1.14  COMMENT ON COLUMN "issue"."half_freeze_snapshot_id" IS 'Snapshot id at end of discussion phase';
    1.15  COMMENT ON COLUMN "issue"."full_freeze_snapshot_id" IS 'Snapshot id at end of verification phase';
    1.16 +COMMENT ON COLUMN "issue"."issue_quorum"            IS 'Calculated number of supporters needed by an initiative of the issue to be "accepted", i.e. pass from ''admission'' to ''discussion'' state';
    1.17 +COMMENT ON COLUMN "issue"."initiative_quorum"       IS 'Calculated number of satisfied supporters to be reached by an initiative to be "admitted" for voting';
    1.18  COMMENT ON COLUMN "issue"."population"              IS 'Count of members in "snapshot_population" table with "snapshot_id" equal to "issue"."latest_snapshot_id"';
    1.19  
    1.20  
    1.21 @@ -2056,6 +2060,30 @@
    1.22  COMMENT ON VIEW "area_quorum" IS 'Area-based quorum considering number of open (accepted) issues';
    1.23  
    1.24  
    1.25 +CREATE VIEW "issue_quorum" AS
    1.26 +  SELECT DISTINCT ON ("issue_id")
    1.27 +    "issue"."id" AS "issue_id",
    1.28 +    "subquery"."issue_quorum"
    1.29 +  FROM "issue"
    1.30 +  CROSS JOIN LATERAL (
    1.31 +    SELECT "area_quorum"."issue_quorum"
    1.32 +    FROM "area_quorum" WHERE "area_quorum"."area_id" = "issue"."area_id"
    1.33 +    UNION ALL
    1.34 +    SELECT "policy"."issue_quorum"
    1.35 +    FROM "policy" WHERE "policy"."id" = "issue"."policy_id"
    1.36 +    UNION ALL
    1.37 +    SELECT
    1.38 +      ceil(
    1.39 +        ("issue"."population"::INT8 * "policy"."issue_quorum_num"::INT8) /
    1.40 +        "policy"."issue_quorum_den"::FLOAT8
    1.41 +      )::INT4
    1.42 +    FROM "policy" WHERE "policy"."id" = "issue"."policy_id"
    1.43 +  ) AS "subquery"
    1.44 +  ORDER BY "issue_id", "issue_quorum" DESC;
    1.45 +
    1.46 +COMMENT ON VIEW "issue_quorum" IS 'Effective quorum for issue admission';
    1.47 +
    1.48 +
    1.49  CREATE VIEW "area_with_unaccepted_issues" AS
    1.50    SELECT DISTINCT ON ("area"."id") "area".*
    1.51    FROM "area" JOIN "issue" ON "area"."id" = "issue"."area_id"
    1.52 @@ -2905,10 +2933,26 @@
    1.53        UPDATE "issue" SET
    1.54          "calculated" = "snapshot"."calculated",
    1.55          "latest_snapshot_id" = "snapshot_id_v",
    1.56 -        "population" = "snapshot"."population"
    1.57 -        FROM "snapshot"
    1.58 +        "population" = "snapshot"."population",
    1.59 +        "initiative_quorum" = CASE WHEN
    1.60 +          "policy"."initiative_quorum" > ceil(
    1.61 +            ( "issue"."population"::INT8 *
    1.62 +              "policy"."initiative_quorum_num"::INT8 ) /
    1.63 +            "policy"."initiative_quorum_den"::FLOAT8
    1.64 +          )::INT4
    1.65 +        THEN
    1.66 +          "policy"."initiative_quorum"
    1.67 +        ELSE
    1.68 +          ceil(
    1.69 +            ( "issue"."population"::INT8 *
    1.70 +              "policy"."initiative_quorum_num"::INT8 ) /
    1.71 +            "policy"."initiative_quorum_den"::FLOAT8
    1.72 +          )::INT4
    1.73 +        END
    1.74 +        FROM "snapshot", "policy"
    1.75          WHERE "issue"."id" = "issue_id_p"
    1.76 -        AND "snapshot"."id" = "snapshot_id_v";
    1.77 +        AND "snapshot"."id" = "snapshot_id_v"
    1.78 +        AND "policy"."id" = "issue"."policy_id";
    1.79        UPDATE "initiative" SET
    1.80          "supporter_count" = (
    1.81            SELECT coalesce(sum("di"."weight"), 0)
    1.82 @@ -2998,8 +3042,11 @@
    1.83          "admission_snapshot_id" = "latest_snapshot_id",
    1.84          "state"                 = 'discussion',
    1.85          "accepted"              = now(),
    1.86 -        "phase_finished"        = NULL
    1.87 -        WHERE "id" = "issue_id_v";
    1.88 +        "phase_finished"        = NULL,
    1.89 +        "issue_quorum"          = "issue_quorum"."issue_quorum"
    1.90 +        FROM "issue_quorum"
    1.91 +        WHERE "id" = "issue_id_v"
    1.92 +        AND "issue_quorum"."issue_id" = "issue_id_v";
    1.93        RETURN TRUE;
    1.94      END;
    1.95    $$;
    1.96 @@ -3089,6 +3136,11 @@
    1.97          IF "persist"."state" != 'admission' THEN
    1.98            PERFORM "take_snapshot"("issue_id_p");
    1.99            PERFORM "finish_snapshot"("issue_id_p");
   1.100 +        ELSE
   1.101 +          UPDATE "issue" SET "issue_quorum" = "issue_quorum"."issue_quorum"
   1.102 +            FROM "issue_quorum"
   1.103 +            WHERE "id" = "issue_id_p"
   1.104 +            AND "issue_quorum"."issue_id" = "issue_id_p";
   1.105          END IF;
   1.106          "persist"."snapshot_created" = TRUE;
   1.107          IF "persist"."phase_finished" THEN
   1.108 @@ -3102,21 +3154,15 @@
   1.109              UPDATE "issue" SET "full_freeze_snapshot_id" = "latest_snapshot_id"
   1.110                WHERE "id" = "issue_id_p";
   1.111              SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
   1.112 -            SELECT * INTO "policy_row" FROM "policy"
   1.113 -              WHERE "id" = "issue_row"."policy_id";
   1.114              FOR "initiative_row" IN
   1.115                SELECT * FROM "initiative"
   1.116                WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
   1.117                FOR UPDATE
   1.118              LOOP
   1.119                IF
   1.120 -                "initiative_row"."polling" OR (
   1.121 -                  "initiative_row"."satisfied_supporter_count" >=
   1.122 -                  "policy_row"."initiative_quorum" AND
   1.123 -                  "initiative_row"."satisfied_supporter_count" *
   1.124 -                  "policy_row"."initiative_quorum_den" >=
   1.125 -                  "issue_row"."population" * "policy_row"."initiative_quorum_num"
   1.126 -                )
   1.127 +                "initiative_row"."polling" OR
   1.128 +                "initiative_row"."satisfied_supporter_count" >=
   1.129 +                "issue_row"."initiative_quorum"
   1.130                THEN
   1.131                  UPDATE "initiative" SET "admitted" = TRUE
   1.132                    WHERE "id" = "initiative_row"."id";