liquid_feedback_core

changeset 608:fa3c406a6775

Support for own voting weight other than 1
author jbe
date Fri May 15 17:43:06 2020 +0200 (2020-05-15)
parents e477713a32b6
children 314626831c04
files core.sql
line diff
     1.1 --- a/core.sql	Tue Feb 11 18:22:18 2020 +0100
     1.2 +++ b/core.sql	Fri May 15 17:43:06 2020 +0200
     1.3 @@ -636,6 +636,7 @@
     1.4          "description"           TEXT            NOT NULL DEFAULT '',  -- full text search
     1.5          "external_reference"    TEXT,
     1.6          "member_count"          INT4,
     1.7 +        "member_weight"         INT4,
     1.8          "location"              JSONB );
     1.9  CREATE INDEX "unit_root_idx" ON "unit" ("id") WHERE "parent_id" ISNULL;
    1.10  CREATE INDEX "unit_parent_id_idx" ON "unit" ("parent_id");
    1.11 @@ -648,6 +649,7 @@
    1.12  COMMENT ON COLUMN "unit"."active"             IS 'TRUE means new issues can be created in areas of this unit';
    1.13  COMMENT ON COLUMN "unit"."external_reference" IS 'Opaque data field to store an external reference';
    1.14  COMMENT ON COLUMN "unit"."member_count"       IS 'Count of members as determined by column "voting_right" in table "privilege" (only active members counted)';
    1.15 +COMMENT ON COLUMN "unit"."member_weight"      IS 'Sum of active member''s voting weight';
    1.16  COMMENT ON COLUMN "unit"."location"           IS 'Geographic location on earth as GeoJSON object indicating valid coordinates for initiatives of issues with this policy';
    1.17  
    1.18  
    1.19 @@ -728,7 +730,8 @@
    1.20  CREATE TABLE "snapshot_population" (
    1.21          PRIMARY KEY ("snapshot_id", "member_id"),
    1.22          "snapshot_id"           INT8            REFERENCES "snapshot" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    1.23 -        "member_id"             INT4            REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE );
    1.24 +        "member_id"             INT4            REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
    1.25 +        "weight"                INT4            NOT NULL );
    1.26  
    1.27  COMMENT ON TABLE "snapshot_population" IS 'Members with voting right relevant for a snapshot';
    1.28  
    1.29 @@ -1101,7 +1104,8 @@
    1.30          "member_manager"        BOOLEAN         NOT NULL DEFAULT FALSE,
    1.31          "initiative_right"      BOOLEAN         NOT NULL DEFAULT TRUE,
    1.32          "voting_right"          BOOLEAN         NOT NULL DEFAULT TRUE,
    1.33 -        "polling_right"         BOOLEAN         NOT NULL DEFAULT FALSE );
    1.34 +        "polling_right"         BOOLEAN         NOT NULL DEFAULT FALSE,
    1.35 +        "weight"                INT4            NOT NULL DEFAULT 1 CHECK ("weight" >= 0) );
    1.36  
    1.37  COMMENT ON TABLE "privilege" IS 'Members rights related to each unit';
    1.38  
    1.39 @@ -1112,6 +1116,7 @@
    1.40  COMMENT ON COLUMN "privilege"."initiative_right" IS 'Right to create an initiative';
    1.41  COMMENT ON COLUMN "privilege"."voting_right"     IS 'Right to support initiatives, create and rate suggestions, and to vote';
    1.42  COMMENT ON COLUMN "privilege"."polling_right"    IS 'Right to create issues with policies having the "policy"."polling" flag set, and to add initiatives having the "initiative"."polling" flag set to those issues';
    1.43 +COMMENT ON COLUMN "privilege"."weight"           IS 'Voting weight of member in unit';
    1.44  
    1.45  
    1.46  CREATE TABLE "interest" (
    1.47 @@ -1218,12 +1223,14 @@
    1.48          FOREIGN KEY ("snapshot_id", "issue_id")
    1.49            REFERENCES "snapshot_issue" ("snapshot_id", "issue_id") ON DELETE CASCADE ON UPDATE CASCADE,
    1.50          "member_id"             INT4            REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
    1.51 +        "ownweight"             INT4            NOT NULL,
    1.52          "weight"                INT4 );
    1.53  CREATE INDEX "direct_interest_snapshot_member_id_idx" ON "direct_interest_snapshot" ("member_id");
    1.54  
    1.55  COMMENT ON TABLE "direct_interest_snapshot" IS 'Snapshot of active members having an "interest" in the "issue"; for corrections refer to column "issue_notice" of "issue" table';
    1.56  
    1.57 -COMMENT ON COLUMN "direct_interest_snapshot"."weight" IS 'Weight of member (1 or higher) according to "delegating_interest_snapshot"';
    1.58 +COMMENT ON COLUMN "direct_interest_snapshot"."ownweight" IS 'Own voting weight of member, disregading delegations';
    1.59 +COMMENT ON COLUMN "direct_interest_snapshot"."weight"    IS 'Voting weight of member according to own weight and "delegating_interest_snapshot"';
    1.60  
    1.61  
    1.62  CREATE TABLE "delegating_interest_snapshot" (
    1.63 @@ -1233,6 +1240,7 @@
    1.64          FOREIGN KEY ("snapshot_id", "issue_id")
    1.65            REFERENCES "snapshot_issue" ("snapshot_id", "issue_id") ON DELETE CASCADE ON UPDATE CASCADE,
    1.66          "member_id"             INT4            REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
    1.67 +        "ownweight"             INT4            NOT NULL,
    1.68          "weight"                INT4,
    1.69          "scope"              "delegation_scope" NOT NULL,
    1.70          "delegate_member_ids"   INT4[]          NOT NULL );
    1.71 @@ -1241,7 +1249,8 @@
    1.72  COMMENT ON TABLE "delegating_interest_snapshot" IS 'Delegations increasing the weight of entries in the "direct_interest_snapshot" table; for corrections refer to column "issue_notice" of "issue" table';
    1.73  
    1.74  COMMENT ON COLUMN "delegating_interest_snapshot"."member_id"           IS 'Delegating member';
    1.75 -COMMENT ON COLUMN "delegating_interest_snapshot"."weight"              IS 'Intermediate weight';
    1.76 +COMMENT ON COLUMN "delegating_interest_snapshot"."ownweight"           IS 'Own voting weight of member, disregading delegations';
    1.77 +COMMENT ON COLUMN "delegating_interest_snapshot"."weight"              IS 'Intermediate voting weight considering incoming delegations';
    1.78  COMMENT ON COLUMN "delegating_interest_snapshot"."delegate_member_ids" IS 'Chain of members who act as delegates; last entry referes to "member_id" column of table "direct_interest_snapshot"';
    1.79  
    1.80  
    1.81 @@ -1281,6 +1290,7 @@
    1.82          PRIMARY KEY ("issue_id", "member_id"),
    1.83          "issue_id"              INT4            REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    1.84          "member_id"             INT4            REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
    1.85 +        "ownweight"             INT4,
    1.86          "weight"                INT4,
    1.87          "comment_changed"       TIMESTAMPTZ,
    1.88          "formatting_engine"     TEXT,
    1.89 @@ -1289,7 +1299,8 @@
    1.90  
    1.91  COMMENT ON TABLE "direct_voter" IS 'Members having directly voted for/against initiatives of an issue; frontends must ensure that no voters are added or removed to/from this table when the issue has been closed; for corrections refer to column "issue_notice" of "issue" table';
    1.92  
    1.93 -COMMENT ON COLUMN "direct_voter"."weight"            IS 'Weight of member (1 or higher) according to "delegating_voter" table';
    1.94 +COMMENT ON COLUMN "direct_voter"."ownweight"         IS 'Own voting weight of member, disregarding delegations';
    1.95 +COMMENT ON COLUMN "direct_voter"."weight"            IS 'Voting weight of member according to own weight and "delegating_interest_snapshot"';
    1.96  COMMENT ON COLUMN "direct_voter"."comment_changed"   IS 'Shall be set on comment change, to indicate a comment being modified after voting has been finished; Automatically set to NULL after voting phase; Automatically set to NULL by trigger, if "comment" is set to NULL';
    1.97  COMMENT ON COLUMN "direct_voter"."formatting_engine" IS 'Allows different formatting engines (i.e. wiki formats) to be used for "direct_voter"."comment"; Automatically set to NULL by trigger, if "comment" is set to NULL';
    1.98  COMMENT ON COLUMN "direct_voter"."comment"           IS 'Is to be set or updated by the frontend, if comment was inserted or updated AFTER the issue has been closed. Otherwise it shall be set to NULL.';
    1.99 @@ -1312,6 +1323,7 @@
   1.100          PRIMARY KEY ("issue_id", "member_id"),
   1.101          "issue_id"              INT4            REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
   1.102          "member_id"             INT4            REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
   1.103 +        "ownweight"             INT4            NOT NULL,
   1.104          "weight"                INT4,
   1.105          "scope"              "delegation_scope" NOT NULL,
   1.106          "delegate_member_ids"   INT4[]          NOT NULL );
   1.107 @@ -1320,7 +1332,8 @@
   1.108  COMMENT ON TABLE "delegating_voter" IS 'Delegations increasing the weight of entries in the "direct_voter" table; for corrections refer to column "issue_notice" of "issue" table';
   1.109  
   1.110  COMMENT ON COLUMN "delegating_voter"."member_id"           IS 'Delegating member';
   1.111 -COMMENT ON COLUMN "delegating_voter"."weight"              IS 'Intermediate weight';
   1.112 +COMMENT ON COLUMN "delegating_voter"."ownweight"           IS 'Own voting weight of member, disregarding delegations';
   1.113 +COMMENT ON COLUMN "delegating_voter"."weight"              IS 'Intermediate voting weight considering incoming delegations';
   1.114  COMMENT ON COLUMN "delegating_voter"."delegate_member_ids" IS 'Chain of members who act as delegates; last entry referes to "member_id" column of table "direct_voter"';
   1.115  
   1.116  
   1.117 @@ -3726,6 +3739,7 @@
   1.118      "delegation"."id",
   1.119      "delegation"."truster_id",
   1.120      "delegation"."trustee_id",
   1.121 +    "privilege"."weight",
   1.122      "delegation"."scope"
   1.123    FROM "issue"
   1.124    JOIN "area"
   1.125 @@ -3757,7 +3771,8 @@
   1.126  CREATE VIEW "unit_member" AS
   1.127    SELECT
   1.128      "unit"."id"   AS "unit_id",
   1.129 -    "member"."id" AS "member_id"
   1.130 +    "member"."id" AS "member_id",
   1.131 +    "privilege"."weight"
   1.132    FROM "privilege"
   1.133    JOIN "unit"   ON "unit"."id"   = "privilege"."unit_id"
   1.134    JOIN "member" ON "member"."id" = "privilege"."member_id"
   1.135 @@ -3769,7 +3784,8 @@
   1.136  CREATE VIEW "unit_member_count" AS
   1.137    SELECT
   1.138      "unit"."id" AS "unit_id",
   1.139 -    count("unit_member"."member_id") AS "member_count"
   1.140 +    count("unit_member"."member_id") AS "member_count",
   1.141 +    sum("unit_member"."weight") AS "member_weight"
   1.142    FROM "unit" LEFT JOIN "unit_member"
   1.143    ON "unit"."id" = "unit_member"."unit_id"
   1.144    GROUP BY "unit"."id";
   1.145 @@ -5047,14 +5063,16 @@
   1.146        DELETE FROM "member_count";
   1.147        INSERT INTO "member_count" ("total_count")
   1.148          SELECT "total_count" FROM "member_count_view";
   1.149 -      UPDATE "unit" SET "member_count" = "view"."member_count"
   1.150 +      UPDATE "unit" SET
   1.151 +        "member_count" = "view"."member_count",
   1.152 +        "member_weight" = "view"."member_weight"
   1.153          FROM "unit_member_count" AS "view"
   1.154          WHERE "view"."unit_id" = "unit"."id";
   1.155        RETURN;
   1.156      END;
   1.157    $$;
   1.158  
   1.159 -COMMENT ON FUNCTION "calculate_member_counts"() IS 'Updates "member_count" table and "member_count" column of table "area" by materializing data from views "member_count_view" and "unit_member_count"';
   1.160 +COMMENT ON FUNCTION "calculate_member_counts"() IS 'Updates "member_count" table and "member_count" and "member_weight" columns of table "area" by materializing data from views "member_count_view" and "unit_member_count"';
   1.161  
   1.162  
   1.163  
   1.164 @@ -5264,16 +5282,18 @@
   1.165                "snapshot_id",
   1.166                "issue_id",
   1.167                "member_id",
   1.168 +              "ownweight",
   1.169                "scope",
   1.170                "delegate_member_ids"
   1.171              ) VALUES (
   1.172                "snapshot_id_p",
   1.173                "issue_id_p",
   1.174                "issue_delegation_row"."truster_id",
   1.175 +              "issue_delegation_row"."weight",
   1.176                "issue_delegation_row"."scope",
   1.177                "delegate_member_ids_v"
   1.178              );
   1.179 -          "sub_weight_v" := 1 +
   1.180 +          "sub_weight_v" := "issue_delegation_row"."weight" +
   1.181              "weight_of_added_delegations_for_snapshot"(
   1.182                "snapshot_id_p",
   1.183                "issue_id_p",
   1.184 @@ -5326,12 +5346,12 @@
   1.185        INSERT INTO "snapshot" ("area_id", "issue_id")
   1.186          VALUES ("area_id_v", "issue_id_p")
   1.187          RETURNING "id" INTO "snapshot_id_v";
   1.188 -      INSERT INTO "snapshot_population" ("snapshot_id", "member_id")
   1.189 -        SELECT "snapshot_id_v", "member_id"
   1.190 +      INSERT INTO "snapshot_population" ("snapshot_id", "member_id", "weight")
   1.191 +        SELECT "snapshot_id_v", "member_id", "weight"
   1.192          FROM "unit_member" WHERE "unit_id" = "unit_id_v";
   1.193        UPDATE "snapshot" SET
   1.194          "population" = (
   1.195 -          SELECT count(1) FROM "snapshot_population"
   1.196 +          SELECT sum("weight") FROM "snapshot_population"
   1.197            WHERE "snapshot_id" = "snapshot_id_v"
   1.198          ) WHERE "id" = "snapshot_id_v";
   1.199        FOR "issue_id_v" IN
   1.200 @@ -5346,11 +5366,12 @@
   1.201          INSERT INTO "snapshot_issue" ("snapshot_id", "issue_id")
   1.202            VALUES ("snapshot_id_v", "issue_id_v");
   1.203          INSERT INTO "direct_interest_snapshot"
   1.204 -          ("snapshot_id", "issue_id", "member_id")
   1.205 +          ("snapshot_id", "issue_id", "member_id", "ownweight")
   1.206            SELECT
   1.207              "snapshot_id_v" AS "snapshot_id",
   1.208              "issue_id_v"    AS "issue_id",
   1.209 -            "member"."id"   AS "member_id"
   1.210 +            "member"."id"   AS "member_id",
   1.211 +            "privilege"."weight" AS "ownweight"
   1.212            FROM "issue"
   1.213            JOIN "area" ON "issue"."area_id" = "area"."id"
   1.214            JOIN "interest" ON "issue"."id" = "interest"."issue_id"
   1.215 @@ -5366,7 +5387,7 @@
   1.216            AND "issue_id" = "issue_id_v"
   1.217          LOOP
   1.218            UPDATE "direct_interest_snapshot" SET
   1.219 -            "weight" = 1 +
   1.220 +            "weight" = "ownweight" +
   1.221                "weight_of_added_delegations_for_snapshot"(
   1.222                  "snapshot_id_v",
   1.223                  "issue_id_v",
   1.224 @@ -5641,15 +5662,17 @@
   1.225            INSERT INTO "delegating_voter" (
   1.226                "issue_id",
   1.227                "member_id",
   1.228 +              "ownweight",
   1.229                "scope",
   1.230                "delegate_member_ids"
   1.231              ) VALUES (
   1.232                "issue_id_p",
   1.233                "issue_delegation_row"."truster_id",
   1.234 +              "issue_delegation_row"."weight",
   1.235                "issue_delegation_row"."scope",
   1.236                "delegate_member_ids_v"
   1.237              );
   1.238 -          "sub_weight_v" := 1 +
   1.239 +          "sub_weight_v" := "issue_delegation_row"."weight" +
   1.240              "weight_of_added_vote_delegations"(
   1.241                "issue_id_p",
   1.242                "issue_delegation_row"."truster_id",
   1.243 @@ -5686,7 +5709,7 @@
   1.244          WHERE "issue_id" = "issue_id_p"
   1.245        LOOP
   1.246          UPDATE "direct_voter" SET
   1.247 -          "weight" = "weight" + "weight_of_added_vote_delegations"(
   1.248 +          "weight" = "ownweight" + "weight_of_added_vote_delegations"(
   1.249              "issue_id_p",
   1.250              "member_id_v",
   1.251              '{}'
   1.252 @@ -5726,8 +5749,7 @@
   1.253        -- delete votes from non-privileged voters:
   1.254        DELETE FROM "direct_voter"
   1.255          USING (
   1.256 -          SELECT
   1.257 -            "direct_voter"."member_id"
   1.258 +          SELECT "direct_voter"."member_id"
   1.259            FROM "direct_voter"
   1.260            JOIN "member" ON "direct_voter"."member_id" = "member"."id"
   1.261            LEFT JOIN "privilege"
   1.262 @@ -5741,9 +5763,12 @@
   1.263          ) AS "subquery"
   1.264          WHERE "direct_voter"."issue_id" = "issue_id_p"
   1.265          AND "direct_voter"."member_id" = "subquery"."member_id";
   1.266 -      -- consider delegations:
   1.267 -      UPDATE "direct_voter" SET "weight" = 1
   1.268 -        WHERE "issue_id" = "issue_id_p";
   1.269 +      -- consider voting weight and delegations:
   1.270 +      UPDATE "direct_voter" SET "ownweight" = "privilege"."weight"
   1.271 +        FROM "privilege"
   1.272 +        WHERE "issue_id" = "issue_id_p"
   1.273 +        AND "privilege"."unit_id" = "unit_id_v"
   1.274 +        AND "privilege"."member_id" = "direct_voter"."member_id";
   1.275        PERFORM "add_vote_delegations"("issue_id_p");
   1.276        -- mark first preferences:
   1.277        UPDATE "vote" SET "first_preference" = "subquery"."first_preference"

Impressum / About Us