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"