liquid_feedback_core
changeset 609:314626831c04
Issue based privileges to override unit based privileges
author | jbe |
---|---|
date | Sat May 16 17:22:30 2020 +0200 (2020-05-16) |
parents | fa3c406a6775 |
children | 22c6d1da7d78 |
files | core.sql |
line diff
1.1 --- a/core.sql Fri May 15 17:43:06 2020 +0200 1.2 +++ b/core.sql Sat May 16 17:22:30 2020 +0200 1.3 @@ -1119,6 +1119,19 @@ 1.4 COMMENT ON COLUMN "privilege"."weight" IS 'Voting weight of member in unit'; 1.5 1.6 1.7 +CREATE TABLE "issue_privilege" ( 1.8 + PRIMARY KEY ("issue_id", "member_id"), 1.9 + "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.10 + "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.11 + "initiative_right" BOOLEAN, 1.12 + "voting_right" BOOLEAN, 1.13 + "polling_right" BOOLEAN, 1.14 + "weight" INT4 CHECK ("weight" >= 0) ); 1.15 +CREATE INDEX "issue_privilege_idx" ON "issue_privilege" ("member_id"); 1.16 + 1.17 +COMMENT ON TABLE "issue_privilege" IS 'Override of "privilege" table for rights of members in certain issues'; 1.18 + 1.19 + 1.20 CREATE TABLE "interest" ( 1.21 PRIMARY KEY ("issue_id", "member_id"), 1.22 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.23 @@ -3739,7 +3752,7 @@ 1.24 "delegation"."id", 1.25 "delegation"."truster_id", 1.26 "delegation"."trustee_id", 1.27 - "privilege"."weight", 1.28 + COALESCE("issue_privilege"."weight", "privilege"."weight") AS "weight", 1.29 "delegation"."scope" 1.30 FROM "issue" 1.31 JOIN "area" 1.32 @@ -3750,10 +3763,14 @@ 1.33 OR "delegation"."issue_id" = "issue"."id" 1.34 JOIN "member" 1.35 ON "delegation"."truster_id" = "member"."id" 1.36 - JOIN "privilege" 1.37 + LEFT JOIN "privilege" 1.38 ON "area"."unit_id" = "privilege"."unit_id" 1.39 AND "delegation"."truster_id" = "privilege"."member_id" 1.40 - WHERE "member"."active" AND "privilege"."voting_right" 1.41 + LEFT JOIN "issue_privilege" 1.42 + ON "issue"."id" = "issue_privilege"."issue_id" 1.43 + AND "delegation"."truster_id" = "issue_privilege"."member_id" 1.44 + WHERE "member"."active" 1.45 + AND COALESCE("issue_privilege"."voting_right", "privilege"."voting_right") 1.46 ORDER BY 1.47 "issue"."id", 1.48 "delegation"."truster_id", 1.49 @@ -3770,12 +3787,10 @@ 1.50 1.51 CREATE VIEW "unit_member" AS 1.52 SELECT 1.53 - "unit"."id" AS "unit_id", 1.54 - "member"."id" AS "member_id", 1.55 + "privilege"."unit_id" AS "unit_id", 1.56 + "member"."id" AS "member_id", 1.57 "privilege"."weight" 1.58 - FROM "privilege" 1.59 - JOIN "unit" ON "unit"."id" = "privilege"."unit_id" 1.60 - JOIN "member" ON "member"."id" = "privilege"."member_id" 1.61 + FROM "privilege" JOIN "member" ON "member"."id" = "privilege"."member_id" 1.62 WHERE "privilege"."voting_right" AND "member"."active"; 1.63 1.64 COMMENT ON VIEW "unit_member" IS 'Active members with voting right in a unit'; 1.65 @@ -4029,8 +4044,10 @@ 1.66 JOIN "area" ON "area"."id" = "issue"."area_id" 1.67 LEFT JOIN "privilege" ON 1.68 "privilege"."member_id" = "member"."id" AND 1.69 - "privilege"."unit_id" = "area"."unit_id" AND 1.70 - "privilege"."voting_right" = TRUE 1.71 + "privilege"."unit_id" = "area"."unit_id" 1.72 + LEFT JOIN "issue_privilege" ON 1.73 + "issue_privilege"."member_id" = "member"."id" AND 1.74 + "issue_privilege"."issue_id" = "event"."issue_id" 1.75 LEFT JOIN "subscription" ON 1.76 "subscription"."member_id" = "member"."id" AND 1.77 "subscription"."unit_id" = "area"."unit_id" 1.78 @@ -4043,8 +4060,10 @@ 1.79 LEFT JOIN "supporter" ON 1.80 "supporter"."member_id" = "member"."id" AND 1.81 "supporter"."initiative_id" = "event"."initiative_id" 1.82 - WHERE ("privilege"."member_id" NOTNULL OR "subscription"."member_id" NOTNULL) 1.83 - AND ("ignored_area"."member_id" ISNULL OR "interest"."member_id" NOTNULL) 1.84 + WHERE ( 1.85 + COALESCE("issue_privilege"."voting_right", "privilege"."voting_right") OR 1.86 + "subscription"."member_id" NOTNULL 1.87 + ) AND ("ignored_area"."member_id" ISNULL OR "interest"."member_id" NOTNULL) 1.88 AND ( 1.89 "event"."event" = 'issue_state_changed'::"event_type" OR 1.90 ( "event"."event" = 'initiative_revoked'::"event_type" AND 1.91 @@ -4151,8 +4170,10 @@ 1.92 "self_support"."member_id" = "recipient_id_p" 1.93 LEFT JOIN "privilege" ON 1.94 "privilege"."member_id" = "recipient_id_p" AND 1.95 - "privilege"."unit_id" = "area"."unit_id" AND 1.96 - "privilege"."voting_right" = TRUE 1.97 + "privilege"."unit_id" = "area"."unit_id" 1.98 + LEFT JOIN "issue_privilege" ON 1.99 + "privilege"."member_id" = "recipient_id_p" AND 1.100 + "privilege"."issue_id" = "initiative"."issue_id" 1.101 LEFT JOIN "subscription" ON 1.102 "subscription"."member_id" = "recipient_id_p" AND 1.103 "subscription"."unit_id" = "area"."unit_id" 1.104 @@ -4166,8 +4187,9 @@ 1.105 AND "self_support"."member_id" ISNULL 1.106 AND NOT "initiative_id_ary" @> ARRAY["initiative"."id"] 1.107 AND ( 1.108 - "privilege"."member_id" NOTNULL OR 1.109 - "subscription"."member_id" NOTNULL ) 1.110 + COALESCE( 1.111 + "issue_privilege"."voting_right", "privilege"."voting_right" 1.112 + ) OR "subscription"."member_id" NOTNULL ) 1.113 AND "ignored_initiative"."member_id" ISNULL 1.114 AND NOT EXISTS ( 1.115 SELECT NULL FROM "draft" 1.116 @@ -4597,11 +4619,17 @@ 1.117 END IF; 1.118 "output_row"."scope_in" := "output_row"."scope_out"; 1.119 "output_row"."member_valid" := EXISTS ( 1.120 - SELECT NULL FROM "member" JOIN "privilege" 1.121 + SELECT NULL FROM "member" 1.122 + LEFT JOIN "privilege" 1.123 ON "privilege"."member_id" = "member"."id" 1.124 AND "privilege"."unit_id" = "unit_id_v" 1.125 + LEFT JOIN "issue_privilege" 1.126 + ON "issue_privilege"."member_id" = "member"."id" 1.127 + AND "issue_privilege"."issue_id" = "issue_id_p" 1.128 WHERE "id" = "output_row"."member_id" 1.129 - AND "member"."active" AND "privilege"."voting_right" 1.130 + AND "member"."active" 1.131 + AND COALESCE( 1.132 + "issue_privilege"."voting_weight", "privilege"."voting_right") 1.133 ); 1.134 "simulate_here_v" := ( 1.135 "simulate_v" AND 1.136 @@ -5347,8 +5375,19 @@ 1.137 VALUES ("area_id_v", "issue_id_p") 1.138 RETURNING "id" INTO "snapshot_id_v"; 1.139 INSERT INTO "snapshot_population" ("snapshot_id", "member_id", "weight") 1.140 - SELECT "snapshot_id_v", "member_id", "weight" 1.141 - FROM "unit_member" WHERE "unit_id" = "unit_id_v"; 1.142 + SELECT 1.143 + "snapshot_id_v", 1.144 + "member"."id", 1.145 + COALESCE("issue_privilege"."weight", "privilege"."weight") 1.146 + FROM "member" 1.147 + LEFT JOIN "privilege" 1.148 + ON "privilege"."unit_id" = "unit_id_v" 1.149 + AND "privilege"."member_id" = "member"."id" 1.150 + LEFT JOIN "issue_privilege" 1.151 + ON "issue_privilege"."issue_id" = "issue_id_v" 1.152 + AND "issue_privilege"."member_id" = "member"."id" 1.153 + WHERE "member"."active" AND COALESCE( 1.154 + "issue_privilege"."voting_right", "privilege"."voting_right"); 1.155 UPDATE "snapshot" SET 1.156 "population" = ( 1.157 SELECT sum("weight") FROM "snapshot_population" 1.158 @@ -5371,16 +5410,22 @@ 1.159 "snapshot_id_v" AS "snapshot_id", 1.160 "issue_id_v" AS "issue_id", 1.161 "member"."id" AS "member_id", 1.162 - "privilege"."weight" AS "ownweight" 1.163 + COALESCE( 1.164 + "issue_privilege"."weight", "privilege"."weight" 1.165 + ) AS "ownweight" 1.166 FROM "issue" 1.167 JOIN "area" ON "issue"."area_id" = "area"."id" 1.168 JOIN "interest" ON "issue"."id" = "interest"."issue_id" 1.169 JOIN "member" ON "interest"."member_id" = "member"."id" 1.170 - JOIN "privilege" 1.171 + LEFT JOIN "privilege" 1.172 ON "privilege"."unit_id" = "area"."unit_id" 1.173 AND "privilege"."member_id" = "member"."id" 1.174 + LEFT JOIN "issue_privilege" 1.175 + ON "issue_privilege"."issue_id" = "issue_id_v" 1.176 + AND "issue_privilege"."member_id" = "member"."id" 1.177 WHERE "issue"."id" = "issue_id_v" 1.178 - AND "member"."active" AND "privilege"."voting_right"; 1.179 + AND "member"."active" AND COALESCE( 1.180 + "issue_privilege"."voting_right", "privilege"."voting_right"); 1.181 FOR "member_id_v" IN 1.182 SELECT "member_id" FROM "direct_interest_snapshot" 1.183 WHERE "snapshot_id" = "snapshot_id_v" 1.184 @@ -5755,10 +5800,16 @@ 1.185 LEFT JOIN "privilege" 1.186 ON "privilege"."unit_id" = "unit_id_v" 1.187 AND "privilege"."member_id" = "direct_voter"."member_id" 1.188 + LEFT JOIN "issue_privilege" 1.189 + ON "issue_privilege"."issue_id" = "issue_id_p" 1.190 + AND "issue_privilege"."member_id" = "direct_voter"."member_id" 1.191 WHERE "direct_voter"."issue_id" = "issue_id_p" AND ( 1.192 "member"."active" = FALSE OR 1.193 - "privilege"."voting_right" ISNULL OR 1.194 - "privilege"."voting_right" = FALSE 1.195 + COALESCE( 1.196 + "issue_privilege"."voting_right", 1.197 + "privilege"."voting_right", 1.198 + FALSE 1.199 + ) = FALSE 1.200 ) 1.201 ) AS "subquery" 1.202 WHERE "direct_voter"."issue_id" = "issue_id_p" 1.203 @@ -5769,6 +5820,11 @@ 1.204 WHERE "issue_id" = "issue_id_p" 1.205 AND "privilege"."unit_id" = "unit_id_v" 1.206 AND "privilege"."member_id" = "direct_voter"."member_id"; 1.207 + UPDATE "direct_voter" SET "ownweight" = "issue_privilege"."weight" 1.208 + FROM "issue_privilege" 1.209 + WHERE "direct_voter"."issue_id" = "issue_id_p" 1.210 + AND "issue_privilege"."issue_id" = "issue_id_p" 1.211 + AND "issue_privilege"."member_id" = "direct_voter"."member_id"; 1.212 PERFORM "add_vote_delegations"("issue_id_p"); 1.213 -- mark first preferences: 1.214 UPDATE "vote" SET "first_preference" = "subquery"."first_preference"