liquid_feedback_core

changeset 568:b1326d149247

New columns "issue_quorum" and "initiative_quorum" for "issue" table
author jbe
date Fri Sep 29 01:57:43 2017 +0200 (2017-09-29)
parents 797282760db4
children 622c71ca6428
files core.sql update/core-update.v3.2.2-v4.0.0.sql
line diff
     1.1 --- a/core.sql	Thu Sep 28 20:12:23 2017 +0200
     1.2 +++ b/core.sql	Fri Sep 29 01:57:43 2017 +0200
     1.3 @@ -769,6 +769,8 @@
     1.4          "full_freeze_snapshot_id" INT8,
     1.5          FOREIGN KEY ("id", "full_freeze_snapshot_id")
     1.6            REFERENCES "snapshot" ("issue_id", "id") ON DELETE RESTRICT ON UPDATE CASCADE,
     1.7 +        "issue_quorum"          INT4,
     1.8 +        "initiative_quorum"     INT4,
     1.9          "population"            INT4,
    1.10          "voter_count"           INT4,
    1.11          "status_quo_schulze_rank" INT4,
    1.12 @@ -841,6 +843,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  COMMENT ON COLUMN "issue"."voter_count"             IS 'Total number of direct and delegating voters; This value is related to the final voting, while "population" is related to snapshots before the final voting';
    1.20  COMMENT ON COLUMN "issue"."status_quo_schulze_rank" IS 'Schulze rank of status quo, as calculated by "calculate_ranks" function';
    1.21 @@ -3324,6 +3328,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 @@ -5122,10 +5150,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 @@ -5944,8 +5988,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 @@ -6046,6 +6093,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 @@ -6059,21 +6111,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";
     2.1 --- a/update/core-update.v3.2.2-v4.0.0.sql	Thu Sep 28 20:12:23 2017 +0200
     2.2 +++ b/update/core-update.v3.2.2-v4.0.0.sql	Fri Sep 29 01:57:43 2017 +0200
     2.3 @@ -550,6 +550,8 @@
     2.4  ALTER TABLE "issue" ADD COLUMN "admission_snapshot_id"   INT8 REFERENCES "snapshot" ("id") ON DELETE SET NULL ON UPDATE CASCADE;
     2.5  ALTER TABLE "issue" ADD COLUMN "half_freeze_snapshot_id" INT8;
     2.6  ALTER TABLE "issue" ADD COLUMN "full_freeze_snapshot_id" INT8;
     2.7 +ALTER TABLE "issue" ADD COLUMN "issue_quorum"            INT4;
     2.8 +ALTER TABLE "issue" ADD COLUMN "initiative_quorum"       INT4;
     2.9  
    2.10  ALTER TABLE "issue" ADD FOREIGN KEY ("id", "half_freeze_snapshot_id")
    2.11    REFERENCES "snapshot" ("issue_id", "id") ON DELETE RESTRICT ON UPDATE CASCADE;
    2.12 @@ -572,6 +574,8 @@
    2.13  COMMENT ON COLUMN "issue"."admission_snapshot_id"   IS 'Snapshot id when issue as accepted or canceled in admission phase';
    2.14  COMMENT ON COLUMN "issue"."half_freeze_snapshot_id" IS 'Snapshot id at end of discussion phase';
    2.15  COMMENT ON COLUMN "issue"."full_freeze_snapshot_id" IS 'Snapshot id at end of verification phase';
    2.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';
    2.17 +COMMENT ON COLUMN "issue"."initiative_quorum"       IS 'Calculated number of satisfied supporters to be reached by an initiative to be "admitted" for voting';
    2.18  COMMENT ON COLUMN "issue"."population"              IS 'Count of members in "snapshot_population" table with "snapshot_id" equal to "issue"."latest_snapshot_id"';
    2.19  
    2.20  
    2.21 @@ -2056,6 +2060,30 @@
    2.22  COMMENT ON VIEW "area_quorum" IS 'Area-based quorum considering number of open (accepted) issues';
    2.23  
    2.24  
    2.25 +CREATE VIEW "issue_quorum" AS
    2.26 +  SELECT DISTINCT ON ("issue_id")
    2.27 +    "issue"."id" AS "issue_id",
    2.28 +    "subquery"."issue_quorum"
    2.29 +  FROM "issue"
    2.30 +  CROSS JOIN LATERAL (
    2.31 +    SELECT "area_quorum"."issue_quorum"
    2.32 +    FROM "area_quorum" WHERE "area_quorum"."area_id" = "issue"."area_id"
    2.33 +    UNION ALL
    2.34 +    SELECT "policy"."issue_quorum"
    2.35 +    FROM "policy" WHERE "policy"."id" = "issue"."policy_id"
    2.36 +    UNION ALL
    2.37 +    SELECT
    2.38 +      ceil(
    2.39 +        ("issue"."population"::INT8 * "policy"."issue_quorum_num"::INT8) /
    2.40 +        "policy"."issue_quorum_den"::FLOAT8
    2.41 +      )::INT4
    2.42 +    FROM "policy" WHERE "policy"."id" = "issue"."policy_id"
    2.43 +  ) AS "subquery"
    2.44 +  ORDER BY "issue_id", "issue_quorum" DESC;
    2.45 +
    2.46 +COMMENT ON VIEW "issue_quorum" IS 'Effective quorum for issue admission';
    2.47 +
    2.48 +
    2.49  CREATE VIEW "area_with_unaccepted_issues" AS
    2.50    SELECT DISTINCT ON ("area"."id") "area".*
    2.51    FROM "area" JOIN "issue" ON "area"."id" = "issue"."area_id"
    2.52 @@ -2905,10 +2933,26 @@
    2.53        UPDATE "issue" SET
    2.54          "calculated" = "snapshot"."calculated",
    2.55          "latest_snapshot_id" = "snapshot_id_v",
    2.56 -        "population" = "snapshot"."population"
    2.57 -        FROM "snapshot"
    2.58 +        "population" = "snapshot"."population",
    2.59 +        "initiative_quorum" = CASE WHEN
    2.60 +          "policy"."initiative_quorum" > ceil(
    2.61 +            ( "issue"."population"::INT8 *
    2.62 +              "policy"."initiative_quorum_num"::INT8 ) /
    2.63 +            "policy"."initiative_quorum_den"::FLOAT8
    2.64 +          )::INT4
    2.65 +        THEN
    2.66 +          "policy"."initiative_quorum"
    2.67 +        ELSE
    2.68 +          ceil(
    2.69 +            ( "issue"."population"::INT8 *
    2.70 +              "policy"."initiative_quorum_num"::INT8 ) /
    2.71 +            "policy"."initiative_quorum_den"::FLOAT8
    2.72 +          )::INT4
    2.73 +        END
    2.74 +        FROM "snapshot", "policy"
    2.75          WHERE "issue"."id" = "issue_id_p"
    2.76 -        AND "snapshot"."id" = "snapshot_id_v";
    2.77 +        AND "snapshot"."id" = "snapshot_id_v"
    2.78 +        AND "policy"."id" = "issue"."policy_id";
    2.79        UPDATE "initiative" SET
    2.80          "supporter_count" = (
    2.81            SELECT coalesce(sum("di"."weight"), 0)
    2.82 @@ -2998,8 +3042,11 @@
    2.83          "admission_snapshot_id" = "latest_snapshot_id",
    2.84          "state"                 = 'discussion',
    2.85          "accepted"              = now(),
    2.86 -        "phase_finished"        = NULL
    2.87 -        WHERE "id" = "issue_id_v";
    2.88 +        "phase_finished"        = NULL,
    2.89 +        "issue_quorum"          = "issue_quorum"."issue_quorum"
    2.90 +        FROM "issue_quorum"
    2.91 +        WHERE "id" = "issue_id_v"
    2.92 +        AND "issue_quorum"."issue_id" = "issue_id_v";
    2.93        RETURN TRUE;
    2.94      END;
    2.95    $$;
    2.96 @@ -3089,6 +3136,11 @@
    2.97          IF "persist"."state" != 'admission' THEN
    2.98            PERFORM "take_snapshot"("issue_id_p");
    2.99            PERFORM "finish_snapshot"("issue_id_p");
   2.100 +        ELSE
   2.101 +          UPDATE "issue" SET "issue_quorum" = "issue_quorum"."issue_quorum"
   2.102 +            FROM "issue_quorum"
   2.103 +            WHERE "id" = "issue_id_p"
   2.104 +            AND "issue_quorum"."issue_id" = "issue_id_p";
   2.105          END IF;
   2.106          "persist"."snapshot_created" = TRUE;
   2.107          IF "persist"."phase_finished" THEN
   2.108 @@ -3102,21 +3154,15 @@
   2.109              UPDATE "issue" SET "full_freeze_snapshot_id" = "latest_snapshot_id"
   2.110                WHERE "id" = "issue_id_p";
   2.111              SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
   2.112 -            SELECT * INTO "policy_row" FROM "policy"
   2.113 -              WHERE "id" = "issue_row"."policy_id";
   2.114              FOR "initiative_row" IN
   2.115                SELECT * FROM "initiative"
   2.116                WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
   2.117                FOR UPDATE
   2.118              LOOP
   2.119                IF
   2.120 -                "initiative_row"."polling" OR (
   2.121 -                  "initiative_row"."satisfied_supporter_count" >=
   2.122 -                  "policy_row"."initiative_quorum" AND
   2.123 -                  "initiative_row"."satisfied_supporter_count" *
   2.124 -                  "policy_row"."initiative_quorum_den" >=
   2.125 -                  "issue_row"."population" * "policy_row"."initiative_quorum_num"
   2.126 -                )
   2.127 +                "initiative_row"."polling" OR
   2.128 +                "initiative_row"."satisfied_supporter_count" >=
   2.129 +                "issue_row"."initiative_quorum"
   2.130                THEN
   2.131                  UPDATE "initiative" SET "admitted" = TRUE
   2.132                    WHERE "id" = "initiative_row"."id";

Impressum / About Us