liquid_feedback_core
changeset 615:5ae68278492f
Added update script core-update.v4.2.0-v4.2.1.sql
author | jbe |
---|---|
date | Sun May 17 13:56:43 2020 +0200 (2020-05-17) |
parents | 7f564126cffb |
children | ae53fc96c953 |
files | update/core-update.v4.2.0-v4.2.1.sql |
line diff
1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000 1.2 +++ b/update/core-update.v4.2.0-v4.2.1.sql Sun May 17 13:56:43 2020 +0200 1.3 @@ -0,0 +1,968 @@ 1.4 +CREATE OR REPLACE VIEW "liquid_feedback_version" AS 1.5 + SELECT * FROM (VALUES ('4.2.1-incomplete-update', 4, 2, -1)) 1.6 + AS "subquery"("string", "major", "minor", "revision"); 1.7 + 1.8 +BEGIN; 1.9 + 1.10 +ALTER TABLE "unit" ADD COLUMN "member_weight" INT4; 1.11 +COMMENT ON COLUMN "unit"."member_weight" IS 'Sum of active members'' voting weight'; 1.12 + 1.13 +ALTER TABLE "snapshot_population" ADD COLUMN "weight" INT4 NOT NULL DEFAULT 1; 1.14 +ALTER TABLE "snapshot_population" ALTER COLUMN "weight" DROP DEFAULT; 1.15 + 1.16 +ALTER TABLE "privilege" ADD COLUMN "weight" INT4 NOT NULL DEFAULT 1 CHECK ("weight" >= 0); 1.17 +COMMENT ON COLUMN "privilege"."weight" IS 'Voting weight of member in unit'; 1.18 + 1.19 +CREATE TABLE "issue_privilege" ( 1.20 + PRIMARY KEY ("issue_id", "member_id"), 1.21 + "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.22 + "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.23 + "initiative_right" BOOLEAN, 1.24 + "voting_right" BOOLEAN, 1.25 + "polling_right" BOOLEAN, 1.26 + "weight" INT4 CHECK ("weight" >= 0) ); 1.27 +CREATE INDEX "issue_privilege_idx" ON "issue_privilege" ("member_id"); 1.28 +COMMENT ON TABLE "issue_privilege" IS 'Override of "privilege" table for rights of members in certain issues'; 1.29 + 1.30 +ALTER TABLE "direct_interest_snapshot" ADD COLUMN "ownweight" INT4 NOT NULL DEFAULT 1; 1.31 +ALTER TABLE "direct_interest_snapshot" ALTER COLUMN "ownweight" DROP DEFAULT; 1.32 +COMMENT ON COLUMN "direct_interest_snapshot"."ownweight" IS 'Own voting weight of member, disregading delegations'; 1.33 +COMMENT ON COLUMN "direct_interest_snapshot"."weight" IS 'Voting weight of member according to own weight and "delegating_interest_snapshot"'; 1.34 + 1.35 +ALTER TABLE "delegating_interest_snapshot" ADD COLUMN "ownweight" INT4 NOT NULL DEFAULT 1; 1.36 +ALTER TABLE "delegating_interest_snapshot" ALTER COLUMN "ownweight" DROP DEFAULT; 1.37 +COMMENT ON COLUMN "delegating_interest_snapshot"."ownweight" IS 'Own voting weight of member, disregading delegations'; 1.38 +COMMENT ON COLUMN "delegating_interest_snapshot"."weight" IS 'Intermediate voting weight considering incoming delegations'; 1.39 + 1.40 +ALTER TABLE "direct_voter" ADD COLUMN "ownweight" INT4 DEFAULT 1; 1.41 +ALTER TABLE "direct_voter" ALTER COLUMN "ownweight" DROP DEFAULT; 1.42 +COMMENT ON COLUMN "direct_voter"."ownweight" IS 'Own voting weight of member, disregarding delegations'; 1.43 +COMMENT ON COLUMN "direct_voter"."weight" IS 'Voting weight of member according to own weight and "delegating_interest_snapshot"'; 1.44 + 1.45 +ALTER TABLE "delegating_voter" ADD COLUMN "ownweight" INT4 NOT NULL DEFAULT 1; 1.46 +ALTER TABLE "delegating_voter" ALTER COLUMN "ownweight" DROP DEFAULT; 1.47 +COMMENT ON COLUMN "delegating_voter"."ownweight" IS 'Own voting weight of member, disregarding delegations'; 1.48 +COMMENT ON COLUMN "delegating_voter"."weight" IS 'Intermediate voting weight considering incoming delegations'; 1.49 + 1.50 +DROP VIEW "issue_delegation"; 1.51 +CREATE VIEW "issue_delegation" AS 1.52 + SELECT DISTINCT ON ("issue"."id", "delegation"."truster_id") 1.53 + "issue"."id" AS "issue_id", 1.54 + "delegation"."id", 1.55 + "delegation"."truster_id", 1.56 + "delegation"."trustee_id", 1.57 + COALESCE("issue_privilege"."weight", "privilege"."weight") AS "weight", 1.58 + "delegation"."scope" 1.59 + FROM "issue" 1.60 + JOIN "area" 1.61 + ON "area"."id" = "issue"."area_id" 1.62 + JOIN "delegation" 1.63 + ON "delegation"."unit_id" = "area"."unit_id" 1.64 + OR "delegation"."area_id" = "area"."id" 1.65 + OR "delegation"."issue_id" = "issue"."id" 1.66 + JOIN "member" 1.67 + ON "delegation"."truster_id" = "member"."id" 1.68 + LEFT JOIN "privilege" 1.69 + ON "area"."unit_id" = "privilege"."unit_id" 1.70 + AND "delegation"."truster_id" = "privilege"."member_id" 1.71 + LEFT JOIN "issue_privilege" 1.72 + ON "issue"."id" = "issue_privilege"."issue_id" 1.73 + AND "delegation"."truster_id" = "issue_privilege"."member_id" 1.74 + WHERE "member"."active" 1.75 + AND COALESCE("issue_privilege"."voting_right", "privilege"."voting_right") 1.76 + ORDER BY 1.77 + "issue"."id", 1.78 + "delegation"."truster_id", 1.79 + "delegation"."scope" DESC; 1.80 +COMMENT ON VIEW "issue_delegation" IS 'Issue delegations where trusters are active and have voting right'; 1.81 + 1.82 +CREATE OR REPLACE VIEW "unit_member" AS 1.83 + SELECT 1.84 + "privilege"."unit_id" AS "unit_id", 1.85 + "member"."id" AS "member_id", 1.86 + "privilege"."weight" 1.87 + FROM "privilege" JOIN "member" ON "member"."id" = "privilege"."member_id" 1.88 + WHERE "privilege"."voting_right" AND "member"."active"; 1.89 + 1.90 +CREATE OR REPLACE VIEW "unit_member_count" AS 1.91 + SELECT 1.92 + "unit"."id" AS "unit_id", 1.93 + count("unit_member"."member_id") AS "member_count", 1.94 + sum("unit_member"."weight") AS "member_weight" 1.95 + FROM "unit" LEFT JOIN "unit_member" 1.96 + ON "unit"."id" = "unit_member"."unit_id" 1.97 + GROUP BY "unit"."id"; 1.98 + 1.99 +CREATE OR REPLACE VIEW "event_for_notification" AS 1.100 + SELECT 1.101 + "member"."id" AS "recipient_id", 1.102 + "event".* 1.103 + FROM "member" CROSS JOIN "event" 1.104 + JOIN "issue" ON "issue"."id" = "event"."issue_id" 1.105 + JOIN "area" ON "area"."id" = "issue"."area_id" 1.106 + LEFT JOIN "privilege" ON 1.107 + "privilege"."member_id" = "member"."id" AND 1.108 + "privilege"."unit_id" = "area"."unit_id" 1.109 + LEFT JOIN "issue_privilege" ON 1.110 + "issue_privilege"."member_id" = "member"."id" AND 1.111 + "issue_privilege"."issue_id" = "event"."issue_id" 1.112 + LEFT JOIN "subscription" ON 1.113 + "subscription"."member_id" = "member"."id" AND 1.114 + "subscription"."unit_id" = "area"."unit_id" 1.115 + LEFT JOIN "ignored_area" ON 1.116 + "ignored_area"."member_id" = "member"."id" AND 1.117 + "ignored_area"."area_id" = "issue"."area_id" 1.118 + LEFT JOIN "interest" ON 1.119 + "interest"."member_id" = "member"."id" AND 1.120 + "interest"."issue_id" = "event"."issue_id" 1.121 + LEFT JOIN "supporter" ON 1.122 + "supporter"."member_id" = "member"."id" AND 1.123 + "supporter"."initiative_id" = "event"."initiative_id" 1.124 + WHERE ( 1.125 + COALESCE("issue_privilege"."voting_right", "privilege"."voting_right") OR 1.126 + "subscription"."member_id" NOTNULL 1.127 + ) AND ("ignored_area"."member_id" ISNULL OR "interest"."member_id" NOTNULL) 1.128 + AND ( 1.129 + "event"."event" = 'issue_state_changed'::"event_type" OR 1.130 + ( "event"."event" = 'initiative_revoked'::"event_type" AND 1.131 + "supporter"."member_id" NOTNULL ) ); 1.132 + 1.133 +CREATE OR REPLACE FUNCTION "featured_initiative" 1.134 + ( "recipient_id_p" "member"."id"%TYPE, 1.135 + "area_id_p" "area"."id"%TYPE ) 1.136 + RETURNS SETOF "initiative"."id"%TYPE 1.137 + LANGUAGE 'plpgsql' STABLE AS $$ 1.138 + DECLARE 1.139 + "counter_v" "member"."notification_counter"%TYPE; 1.140 + "sample_size_v" "member"."notification_sample_size"%TYPE; 1.141 + "initiative_id_ary" INT4[]; --"initiative"."id"%TYPE[] 1.142 + "match_v" BOOLEAN; 1.143 + "member_id_v" "member"."id"%TYPE; 1.144 + "seed_v" TEXT; 1.145 + "initiative_id_v" "initiative"."id"%TYPE; 1.146 + BEGIN 1.147 + SELECT "notification_counter", "notification_sample_size" 1.148 + INTO "counter_v", "sample_size_v" 1.149 + FROM "member" WHERE "id" = "recipient_id_p"; 1.150 + IF COALESCE("sample_size_v" <= 0, TRUE) THEN 1.151 + RETURN; 1.152 + END IF; 1.153 + "initiative_id_ary" := '{}'; 1.154 + LOOP 1.155 + "match_v" := FALSE; 1.156 + FOR "member_id_v", "seed_v" IN 1.157 + SELECT * FROM ( 1.158 + SELECT DISTINCT 1.159 + "supporter"."member_id", 1.160 + md5( 1.161 + "recipient_id_p" || '-' || 1.162 + "counter_v" || '-' || 1.163 + "area_id_p" || '-' || 1.164 + "supporter"."member_id" 1.165 + ) AS "seed" 1.166 + FROM "supporter" 1.167 + JOIN "initiative" ON "initiative"."id" = "supporter"."initiative_id" 1.168 + JOIN "issue" ON "issue"."id" = "initiative"."issue_id" 1.169 + WHERE "supporter"."member_id" != "recipient_id_p" 1.170 + AND "issue"."area_id" = "area_id_p" 1.171 + AND "issue"."state" IN ('admission', 'discussion', 'verification') 1.172 + ) AS "subquery" 1.173 + ORDER BY "seed" 1.174 + LOOP 1.175 + SELECT "initiative"."id" INTO "initiative_id_v" 1.176 + FROM "initiative" 1.177 + JOIN "issue" ON "issue"."id" = "initiative"."issue_id" 1.178 + JOIN "area" ON "area"."id" = "issue"."area_id" 1.179 + JOIN "supporter" ON "supporter"."initiative_id" = "initiative"."id" 1.180 + LEFT JOIN "supporter" AS "self_support" ON 1.181 + "self_support"."initiative_id" = "initiative"."id" AND 1.182 + "self_support"."member_id" = "recipient_id_p" 1.183 + LEFT JOIN "privilege" ON 1.184 + "privilege"."member_id" = "recipient_id_p" AND 1.185 + "privilege"."unit_id" = "area"."unit_id" 1.186 + LEFT JOIN "issue_privilege" ON 1.187 + "privilege"."member_id" = "recipient_id_p" AND 1.188 + "privilege"."issue_id" = "initiative"."issue_id" 1.189 + LEFT JOIN "subscription" ON 1.190 + "subscription"."member_id" = "recipient_id_p" AND 1.191 + "subscription"."unit_id" = "area"."unit_id" 1.192 + LEFT JOIN "ignored_initiative" ON 1.193 + "ignored_initiative"."member_id" = "recipient_id_p" AND 1.194 + "ignored_initiative"."initiative_id" = "initiative"."id" 1.195 + WHERE "supporter"."member_id" = "member_id_v" 1.196 + AND "issue"."area_id" = "area_id_p" 1.197 + AND "issue"."state" IN ('admission', 'discussion', 'verification') 1.198 + AND "initiative"."revoked" ISNULL 1.199 + AND "self_support"."member_id" ISNULL 1.200 + AND NOT "initiative_id_ary" @> ARRAY["initiative"."id"] 1.201 + AND ( 1.202 + COALESCE( 1.203 + "issue_privilege"."voting_right", "privilege"."voting_right" 1.204 + ) OR "subscription"."member_id" NOTNULL ) 1.205 + AND "ignored_initiative"."member_id" ISNULL 1.206 + AND NOT EXISTS ( 1.207 + SELECT NULL FROM "draft" 1.208 + JOIN "ignored_member" ON 1.209 + "ignored_member"."member_id" = "recipient_id_p" AND 1.210 + "ignored_member"."other_member_id" = "draft"."author_id" 1.211 + WHERE "draft"."initiative_id" = "initiative"."id" 1.212 + ) 1.213 + ORDER BY md5("seed_v" || '-' || "initiative"."id") 1.214 + LIMIT 1; 1.215 + IF FOUND THEN 1.216 + "match_v" := TRUE; 1.217 + RETURN NEXT "initiative_id_v"; 1.218 + IF array_length("initiative_id_ary", 1) + 1 >= "sample_size_v" THEN 1.219 + RETURN; 1.220 + END IF; 1.221 + "initiative_id_ary" := "initiative_id_ary" || "initiative_id_v"; 1.222 + END IF; 1.223 + END LOOP; 1.224 + EXIT WHEN NOT "match_v"; 1.225 + END LOOP; 1.226 + RETURN; 1.227 + END; 1.228 + $$; 1.229 + 1.230 +CREATE OR REPLACE FUNCTION "delegation_chain" 1.231 + ( "member_id_p" "member"."id"%TYPE, 1.232 + "unit_id_p" "unit"."id"%TYPE, 1.233 + "area_id_p" "area"."id"%TYPE, 1.234 + "issue_id_p" "issue"."id"%TYPE, 1.235 + "simulate_trustee_id_p" "member"."id"%TYPE DEFAULT NULL, 1.236 + "simulate_default_p" BOOLEAN DEFAULT FALSE ) 1.237 + RETURNS SETOF "delegation_chain_row" 1.238 + LANGUAGE 'plpgsql' STABLE AS $$ 1.239 + DECLARE 1.240 + "scope_v" "delegation_scope"; 1.241 + "unit_id_v" "unit"."id"%TYPE; 1.242 + "area_id_v" "area"."id"%TYPE; 1.243 + "issue_row" "issue"%ROWTYPE; 1.244 + "visited_member_ids" INT4[]; -- "member"."id"%TYPE[] 1.245 + "loop_member_id_v" "member"."id"%TYPE; 1.246 + "output_row" "delegation_chain_row"; 1.247 + "output_rows" "delegation_chain_row"[]; 1.248 + "simulate_v" BOOLEAN; 1.249 + "simulate_here_v" BOOLEAN; 1.250 + "delegation_row" "delegation"%ROWTYPE; 1.251 + "row_count" INT4; 1.252 + "i" INT4; 1.253 + "loop_v" BOOLEAN; 1.254 + BEGIN 1.255 + IF "simulate_trustee_id_p" NOTNULL AND "simulate_default_p" THEN 1.256 + RAISE EXCEPTION 'Both "simulate_trustee_id_p" is set, and "simulate_default_p" is true'; 1.257 + END IF; 1.258 + IF "simulate_trustee_id_p" NOTNULL OR "simulate_default_p" THEN 1.259 + "simulate_v" := TRUE; 1.260 + ELSE 1.261 + "simulate_v" := FALSE; 1.262 + END IF; 1.263 + IF 1.264 + "unit_id_p" NOTNULL AND 1.265 + "area_id_p" ISNULL AND 1.266 + "issue_id_p" ISNULL 1.267 + THEN 1.268 + "scope_v" := 'unit'; 1.269 + "unit_id_v" := "unit_id_p"; 1.270 + ELSIF 1.271 + "unit_id_p" ISNULL AND 1.272 + "area_id_p" NOTNULL AND 1.273 + "issue_id_p" ISNULL 1.274 + THEN 1.275 + "scope_v" := 'area'; 1.276 + "area_id_v" := "area_id_p"; 1.277 + SELECT "unit_id" INTO "unit_id_v" 1.278 + FROM "area" WHERE "id" = "area_id_v"; 1.279 + ELSIF 1.280 + "unit_id_p" ISNULL AND 1.281 + "area_id_p" ISNULL AND 1.282 + "issue_id_p" NOTNULL 1.283 + THEN 1.284 + SELECT INTO "issue_row" * FROM "issue" WHERE "id" = "issue_id_p"; 1.285 + IF "issue_row"."id" ISNULL THEN 1.286 + RETURN; 1.287 + END IF; 1.288 + IF "issue_row"."closed" NOTNULL THEN 1.289 + IF "simulate_v" THEN 1.290 + RAISE EXCEPTION 'Tried to simulate delegation chain for closed issue.'; 1.291 + END IF; 1.292 + FOR "output_row" IN 1.293 + SELECT * FROM 1.294 + "delegation_chain_for_closed_issue"("member_id_p", "issue_id_p") 1.295 + LOOP 1.296 + RETURN NEXT "output_row"; 1.297 + END LOOP; 1.298 + RETURN; 1.299 + END IF; 1.300 + "scope_v" := 'issue'; 1.301 + SELECT "area_id" INTO "area_id_v" 1.302 + FROM "issue" WHERE "id" = "issue_id_p"; 1.303 + SELECT "unit_id" INTO "unit_id_v" 1.304 + FROM "area" WHERE "id" = "area_id_v"; 1.305 + ELSE 1.306 + RAISE EXCEPTION 'Exactly one of unit_id_p, area_id_p, or issue_id_p must be NOTNULL.'; 1.307 + END IF; 1.308 + "visited_member_ids" := '{}'; 1.309 + "loop_member_id_v" := NULL; 1.310 + "output_rows" := '{}'; 1.311 + "output_row"."index" := 0; 1.312 + "output_row"."member_id" := "member_id_p"; 1.313 + "output_row"."member_valid" := TRUE; 1.314 + "output_row"."participation" := FALSE; 1.315 + "output_row"."overridden" := FALSE; 1.316 + "output_row"."disabled_out" := FALSE; 1.317 + "output_row"."scope_out" := NULL; 1.318 + LOOP 1.319 + IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN 1.320 + "loop_member_id_v" := "output_row"."member_id"; 1.321 + ELSE 1.322 + "visited_member_ids" := 1.323 + "visited_member_ids" || "output_row"."member_id"; 1.324 + END IF; 1.325 + IF "output_row"."participation" ISNULL THEN 1.326 + "output_row"."overridden" := NULL; 1.327 + ELSIF "output_row"."participation" THEN 1.328 + "output_row"."overridden" := TRUE; 1.329 + END IF; 1.330 + "output_row"."scope_in" := "output_row"."scope_out"; 1.331 + "output_row"."member_valid" := EXISTS ( 1.332 + SELECT NULL FROM "member" 1.333 + LEFT JOIN "privilege" 1.334 + ON "privilege"."member_id" = "member"."id" 1.335 + AND "privilege"."unit_id" = "unit_id_v" 1.336 + LEFT JOIN "issue_privilege" 1.337 + ON "issue_privilege"."member_id" = "member"."id" 1.338 + AND "issue_privilege"."issue_id" = "issue_id_p" 1.339 + WHERE "id" = "output_row"."member_id" 1.340 + AND "member"."active" 1.341 + AND COALESCE( 1.342 + "issue_privilege"."voting_right", "privilege"."voting_right") 1.343 + ); 1.344 + "simulate_here_v" := ( 1.345 + "simulate_v" AND 1.346 + "output_row"."member_id" = "member_id_p" 1.347 + ); 1.348 + "delegation_row" := ROW(NULL); 1.349 + IF "output_row"."member_valid" OR "simulate_here_v" THEN 1.350 + IF "scope_v" = 'unit' THEN 1.351 + IF NOT "simulate_here_v" THEN 1.352 + SELECT * INTO "delegation_row" FROM "delegation" 1.353 + WHERE "truster_id" = "output_row"."member_id" 1.354 + AND "unit_id" = "unit_id_v"; 1.355 + END IF; 1.356 + ELSIF "scope_v" = 'area' THEN 1.357 + IF "simulate_here_v" THEN 1.358 + IF "simulate_trustee_id_p" ISNULL THEN 1.359 + SELECT * INTO "delegation_row" FROM "delegation" 1.360 + WHERE "truster_id" = "output_row"."member_id" 1.361 + AND "unit_id" = "unit_id_v"; 1.362 + END IF; 1.363 + ELSE 1.364 + SELECT * INTO "delegation_row" FROM "delegation" 1.365 + WHERE "truster_id" = "output_row"."member_id" 1.366 + AND ( 1.367 + "unit_id" = "unit_id_v" OR 1.368 + "area_id" = "area_id_v" 1.369 + ) 1.370 + ORDER BY "scope" DESC; 1.371 + END IF; 1.372 + ELSIF "scope_v" = 'issue' THEN 1.373 + IF "issue_row"."fully_frozen" ISNULL THEN 1.374 + "output_row"."participation" := EXISTS ( 1.375 + SELECT NULL FROM "interest" 1.376 + WHERE "issue_id" = "issue_id_p" 1.377 + AND "member_id" = "output_row"."member_id" 1.378 + ); 1.379 + ELSE 1.380 + IF "output_row"."member_id" = "member_id_p" THEN 1.381 + "output_row"."participation" := EXISTS ( 1.382 + SELECT NULL FROM "direct_voter" 1.383 + WHERE "issue_id" = "issue_id_p" 1.384 + AND "member_id" = "output_row"."member_id" 1.385 + ); 1.386 + ELSE 1.387 + "output_row"."participation" := NULL; 1.388 + END IF; 1.389 + END IF; 1.390 + IF "simulate_here_v" THEN 1.391 + IF "simulate_trustee_id_p" ISNULL THEN 1.392 + SELECT * INTO "delegation_row" FROM "delegation" 1.393 + WHERE "truster_id" = "output_row"."member_id" 1.394 + AND ( 1.395 + "unit_id" = "unit_id_v" OR 1.396 + "area_id" = "area_id_v" 1.397 + ) 1.398 + ORDER BY "scope" DESC; 1.399 + END IF; 1.400 + ELSE 1.401 + SELECT * INTO "delegation_row" FROM "delegation" 1.402 + WHERE "truster_id" = "output_row"."member_id" 1.403 + AND ( 1.404 + "unit_id" = "unit_id_v" OR 1.405 + "area_id" = "area_id_v" OR 1.406 + "issue_id" = "issue_id_p" 1.407 + ) 1.408 + ORDER BY "scope" DESC; 1.409 + END IF; 1.410 + END IF; 1.411 + ELSE 1.412 + "output_row"."participation" := FALSE; 1.413 + END IF; 1.414 + IF "simulate_here_v" AND "simulate_trustee_id_p" NOTNULL THEN 1.415 + "output_row"."scope_out" := "scope_v"; 1.416 + "output_rows" := "output_rows" || "output_row"; 1.417 + "output_row"."member_id" := "simulate_trustee_id_p"; 1.418 + ELSIF "delegation_row"."trustee_id" NOTNULL THEN 1.419 + "output_row"."scope_out" := "delegation_row"."scope"; 1.420 + "output_rows" := "output_rows" || "output_row"; 1.421 + "output_row"."member_id" := "delegation_row"."trustee_id"; 1.422 + ELSIF "delegation_row"."scope" NOTNULL THEN 1.423 + "output_row"."scope_out" := "delegation_row"."scope"; 1.424 + "output_row"."disabled_out" := TRUE; 1.425 + "output_rows" := "output_rows" || "output_row"; 1.426 + EXIT; 1.427 + ELSE 1.428 + "output_row"."scope_out" := NULL; 1.429 + "output_rows" := "output_rows" || "output_row"; 1.430 + EXIT; 1.431 + END IF; 1.432 + EXIT WHEN "loop_member_id_v" NOTNULL; 1.433 + "output_row"."index" := "output_row"."index" + 1; 1.434 + END LOOP; 1.435 + "row_count" := array_upper("output_rows", 1); 1.436 + "i" := 1; 1.437 + "loop_v" := FALSE; 1.438 + LOOP 1.439 + "output_row" := "output_rows"["i"]; 1.440 + EXIT WHEN "output_row" ISNULL; -- NOTE: ISNULL and NOT ... NOTNULL produce different results! 1.441 + IF "loop_v" THEN 1.442 + IF "i" + 1 = "row_count" THEN 1.443 + "output_row"."loop" := 'last'; 1.444 + ELSIF "i" = "row_count" THEN 1.445 + "output_row"."loop" := 'repetition'; 1.446 + ELSE 1.447 + "output_row"."loop" := 'intermediate'; 1.448 + END IF; 1.449 + ELSIF "output_row"."member_id" = "loop_member_id_v" THEN 1.450 + "output_row"."loop" := 'first'; 1.451 + "loop_v" := TRUE; 1.452 + END IF; 1.453 + IF "scope_v" = 'unit' THEN 1.454 + "output_row"."participation" := NULL; 1.455 + END IF; 1.456 + RETURN NEXT "output_row"; 1.457 + "i" := "i" + 1; 1.458 + END LOOP; 1.459 + RETURN; 1.460 + END; 1.461 + $$; 1.462 + 1.463 +CREATE OR REPLACE FUNCTION "calculate_member_counts"() 1.464 + RETURNS VOID 1.465 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.466 + BEGIN 1.467 + PERFORM "require_transaction_isolation"(); 1.468 + DELETE FROM "member_count"; 1.469 + INSERT INTO "member_count" ("total_count") 1.470 + SELECT "total_count" FROM "member_count_view"; 1.471 + UPDATE "unit" SET 1.472 + "member_count" = "view"."member_count", 1.473 + "member_weight" = "view"."member_weight" 1.474 + FROM "unit_member_count" AS "view" 1.475 + WHERE "view"."unit_id" = "unit"."id"; 1.476 + RETURN; 1.477 + END; 1.478 + $$; 1.479 +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.480 + 1.481 +CREATE OR REPLACE FUNCTION "weight_of_added_delegations_for_snapshot" 1.482 + ( "snapshot_id_p" "snapshot"."id"%TYPE, 1.483 + "issue_id_p" "issue"."id"%TYPE, 1.484 + "member_id_p" "member"."id"%TYPE, 1.485 + "delegate_member_ids_p" "delegating_interest_snapshot"."delegate_member_ids"%TYPE ) 1.486 + RETURNS "direct_interest_snapshot"."weight"%TYPE 1.487 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.488 + DECLARE 1.489 + "issue_delegation_row" "issue_delegation"%ROWTYPE; 1.490 + "delegate_member_ids_v" "delegating_interest_snapshot"."delegate_member_ids"%TYPE; 1.491 + "weight_v" INT4; 1.492 + "sub_weight_v" INT4; 1.493 + BEGIN 1.494 + PERFORM "require_transaction_isolation"(); 1.495 + "weight_v" := 0; 1.496 + FOR "issue_delegation_row" IN 1.497 + SELECT * FROM "issue_delegation" 1.498 + WHERE "trustee_id" = "member_id_p" 1.499 + AND "issue_id" = "issue_id_p" 1.500 + LOOP 1.501 + IF NOT EXISTS ( 1.502 + SELECT NULL FROM "direct_interest_snapshot" 1.503 + WHERE "snapshot_id" = "snapshot_id_p" 1.504 + AND "issue_id" = "issue_id_p" 1.505 + AND "member_id" = "issue_delegation_row"."truster_id" 1.506 + ) AND NOT EXISTS ( 1.507 + SELECT NULL FROM "delegating_interest_snapshot" 1.508 + WHERE "snapshot_id" = "snapshot_id_p" 1.509 + AND "issue_id" = "issue_id_p" 1.510 + AND "member_id" = "issue_delegation_row"."truster_id" 1.511 + ) THEN 1.512 + "delegate_member_ids_v" := 1.513 + "member_id_p" || "delegate_member_ids_p"; 1.514 + INSERT INTO "delegating_interest_snapshot" ( 1.515 + "snapshot_id", 1.516 + "issue_id", 1.517 + "member_id", 1.518 + "ownweight", 1.519 + "scope", 1.520 + "delegate_member_ids" 1.521 + ) VALUES ( 1.522 + "snapshot_id_p", 1.523 + "issue_id_p", 1.524 + "issue_delegation_row"."truster_id", 1.525 + "issue_delegation_row"."weight", 1.526 + "issue_delegation_row"."scope", 1.527 + "delegate_member_ids_v" 1.528 + ); 1.529 + "sub_weight_v" := "issue_delegation_row"."weight" + 1.530 + "weight_of_added_delegations_for_snapshot"( 1.531 + "snapshot_id_p", 1.532 + "issue_id_p", 1.533 + "issue_delegation_row"."truster_id", 1.534 + "delegate_member_ids_v" 1.535 + ); 1.536 + UPDATE "delegating_interest_snapshot" 1.537 + SET "weight" = "sub_weight_v" 1.538 + WHERE "snapshot_id" = "snapshot_id_p" 1.539 + AND "issue_id" = "issue_id_p" 1.540 + AND "member_id" = "issue_delegation_row"."truster_id"; 1.541 + "weight_v" := "weight_v" + "sub_weight_v"; 1.542 + END IF; 1.543 + END LOOP; 1.544 + RETURN "weight_v"; 1.545 + END; 1.546 + $$; 1.547 + 1.548 +CREATE OR REPLACE FUNCTION "take_snapshot" 1.549 + ( "issue_id_p" "issue"."id"%TYPE, 1.550 + "area_id_p" "area"."id"%TYPE = NULL ) 1.551 + RETURNS "snapshot"."id"%TYPE 1.552 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.553 + DECLARE 1.554 + "area_id_v" "area"."id"%TYPE; 1.555 + "unit_id_v" "unit"."id"%TYPE; 1.556 + "snapshot_id_v" "snapshot"."id"%TYPE; 1.557 + "issue_id_v" "issue"."id"%TYPE; 1.558 + "member_id_v" "member"."id"%TYPE; 1.559 + BEGIN 1.560 + IF "issue_id_p" NOTNULL AND "area_id_p" NOTNULL THEN 1.561 + RAISE EXCEPTION 'One of "issue_id_p" and "area_id_p" must be NULL'; 1.562 + END IF; 1.563 + PERFORM "require_transaction_isolation"(); 1.564 + IF "issue_id_p" ISNULL THEN 1.565 + "area_id_v" := "area_id_p"; 1.566 + ELSE 1.567 + SELECT "area_id" INTO "area_id_v" 1.568 + FROM "issue" WHERE "id" = "issue_id_p"; 1.569 + END IF; 1.570 + SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v"; 1.571 + INSERT INTO "snapshot" ("area_id", "issue_id") 1.572 + VALUES ("area_id_v", "issue_id_p") 1.573 + RETURNING "id" INTO "snapshot_id_v"; 1.574 + INSERT INTO "snapshot_population" ("snapshot_id", "member_id", "weight") 1.575 + SELECT 1.576 + "snapshot_id_v", 1.577 + "member"."id", 1.578 + COALESCE("issue_privilege"."weight", "privilege"."weight") 1.579 + FROM "member" 1.580 + LEFT JOIN "privilege" 1.581 + ON "privilege"."unit_id" = "unit_id_v" 1.582 + AND "privilege"."member_id" = "member"."id" 1.583 + LEFT JOIN "issue_privilege" 1.584 + ON "issue_privilege"."issue_id" = "issue_id_p" 1.585 + AND "issue_privilege"."member_id" = "member"."id" 1.586 + WHERE "member"."active" AND COALESCE( 1.587 + "issue_privilege"."voting_right", "privilege"."voting_right"); 1.588 + UPDATE "snapshot" SET 1.589 + "population" = ( 1.590 + SELECT sum("weight") FROM "snapshot_population" 1.591 + WHERE "snapshot_id" = "snapshot_id_v" 1.592 + ) WHERE "id" = "snapshot_id_v"; 1.593 + FOR "issue_id_v" IN 1.594 + SELECT "id" FROM "issue" 1.595 + WHERE CASE WHEN "issue_id_p" ISNULL THEN 1.596 + "area_id" = "area_id_p" AND 1.597 + "state" = 'admission' 1.598 + ELSE 1.599 + "id" = "issue_id_p" 1.600 + END 1.601 + LOOP 1.602 + INSERT INTO "snapshot_issue" ("snapshot_id", "issue_id") 1.603 + VALUES ("snapshot_id_v", "issue_id_v"); 1.604 + INSERT INTO "direct_interest_snapshot" 1.605 + ("snapshot_id", "issue_id", "member_id", "ownweight") 1.606 + SELECT 1.607 + "snapshot_id_v" AS "snapshot_id", 1.608 + "issue_id_v" AS "issue_id", 1.609 + "member"."id" AS "member_id", 1.610 + COALESCE( 1.611 + "issue_privilege"."weight", "privilege"."weight" 1.612 + ) AS "ownweight" 1.613 + FROM "issue" 1.614 + JOIN "area" ON "issue"."area_id" = "area"."id" 1.615 + JOIN "interest" ON "issue"."id" = "interest"."issue_id" 1.616 + JOIN "member" ON "interest"."member_id" = "member"."id" 1.617 + LEFT JOIN "privilege" 1.618 + ON "privilege"."unit_id" = "area"."unit_id" 1.619 + AND "privilege"."member_id" = "member"."id" 1.620 + LEFT JOIN "issue_privilege" 1.621 + ON "issue_privilege"."issue_id" = "issue_id_v" 1.622 + AND "issue_privilege"."member_id" = "member"."id" 1.623 + WHERE "issue"."id" = "issue_id_v" 1.624 + AND "member"."active" AND COALESCE( 1.625 + "issue_privilege"."voting_right", "privilege"."voting_right"); 1.626 + FOR "member_id_v" IN 1.627 + SELECT "member_id" FROM "direct_interest_snapshot" 1.628 + WHERE "snapshot_id" = "snapshot_id_v" 1.629 + AND "issue_id" = "issue_id_v" 1.630 + LOOP 1.631 + UPDATE "direct_interest_snapshot" SET 1.632 + "weight" = "ownweight" + 1.633 + "weight_of_added_delegations_for_snapshot"( 1.634 + "snapshot_id_v", 1.635 + "issue_id_v", 1.636 + "member_id_v", 1.637 + '{}' 1.638 + ) 1.639 + WHERE "snapshot_id" = "snapshot_id_v" 1.640 + AND "issue_id" = "issue_id_v" 1.641 + AND "member_id" = "member_id_v"; 1.642 + END LOOP; 1.643 + INSERT INTO "direct_supporter_snapshot" 1.644 + ( "snapshot_id", "issue_id", "initiative_id", "member_id", 1.645 + "draft_id", "informed", "satisfied" ) 1.646 + SELECT 1.647 + "snapshot_id_v" AS "snapshot_id", 1.648 + "issue_id_v" AS "issue_id", 1.649 + "initiative"."id" AS "initiative_id", 1.650 + "supporter"."member_id" AS "member_id", 1.651 + "supporter"."draft_id" AS "draft_id", 1.652 + "supporter"."draft_id" = "current_draft"."id" AS "informed", 1.653 + NOT EXISTS ( 1.654 + SELECT NULL FROM "critical_opinion" 1.655 + WHERE "initiative_id" = "initiative"."id" 1.656 + AND "member_id" = "supporter"."member_id" 1.657 + ) AS "satisfied" 1.658 + FROM "initiative" 1.659 + JOIN "supporter" 1.660 + ON "supporter"."initiative_id" = "initiative"."id" 1.661 + JOIN "current_draft" 1.662 + ON "initiative"."id" = "current_draft"."initiative_id" 1.663 + JOIN "direct_interest_snapshot" 1.664 + ON "snapshot_id_v" = "direct_interest_snapshot"."snapshot_id" 1.665 + AND "supporter"."member_id" = "direct_interest_snapshot"."member_id" 1.666 + AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id" 1.667 + WHERE "initiative"."issue_id" = "issue_id_v"; 1.668 + DELETE FROM "temporary_suggestion_counts"; 1.669 + INSERT INTO "temporary_suggestion_counts" 1.670 + ( "id", 1.671 + "minus2_unfulfilled_count", "minus2_fulfilled_count", 1.672 + "minus1_unfulfilled_count", "minus1_fulfilled_count", 1.673 + "plus1_unfulfilled_count", "plus1_fulfilled_count", 1.674 + "plus2_unfulfilled_count", "plus2_fulfilled_count" ) 1.675 + SELECT 1.676 + "suggestion"."id", 1.677 + ( SELECT coalesce(sum("di"."weight"), 0) 1.678 + FROM "opinion" JOIN "direct_interest_snapshot" AS "di" 1.679 + ON "di"."snapshot_id" = "snapshot_id_v" 1.680 + AND "di"."issue_id" = "issue_id_v" 1.681 + AND "di"."member_id" = "opinion"."member_id" 1.682 + WHERE "opinion"."suggestion_id" = "suggestion"."id" 1.683 + AND "opinion"."degree" = -2 1.684 + AND "opinion"."fulfilled" = FALSE 1.685 + ) AS "minus2_unfulfilled_count", 1.686 + ( SELECT coalesce(sum("di"."weight"), 0) 1.687 + FROM "opinion" JOIN "direct_interest_snapshot" AS "di" 1.688 + ON "di"."snapshot_id" = "snapshot_id_v" 1.689 + AND "di"."issue_id" = "issue_id_v" 1.690 + AND "di"."member_id" = "opinion"."member_id" 1.691 + WHERE "opinion"."suggestion_id" = "suggestion"."id" 1.692 + AND "opinion"."degree" = -2 1.693 + AND "opinion"."fulfilled" = TRUE 1.694 + ) AS "minus2_fulfilled_count", 1.695 + ( SELECT coalesce(sum("di"."weight"), 0) 1.696 + FROM "opinion" JOIN "direct_interest_snapshot" AS "di" 1.697 + ON "di"."snapshot_id" = "snapshot_id_v" 1.698 + AND "di"."issue_id" = "issue_id_v" 1.699 + AND "di"."member_id" = "opinion"."member_id" 1.700 + WHERE "opinion"."suggestion_id" = "suggestion"."id" 1.701 + AND "opinion"."degree" = -1 1.702 + AND "opinion"."fulfilled" = FALSE 1.703 + ) AS "minus1_unfulfilled_count", 1.704 + ( SELECT coalesce(sum("di"."weight"), 0) 1.705 + FROM "opinion" JOIN "direct_interest_snapshot" AS "di" 1.706 + ON "di"."snapshot_id" = "snapshot_id_v" 1.707 + AND "di"."issue_id" = "issue_id_v" 1.708 + AND "di"."member_id" = "opinion"."member_id" 1.709 + WHERE "opinion"."suggestion_id" = "suggestion"."id" 1.710 + AND "opinion"."degree" = -1 1.711 + AND "opinion"."fulfilled" = TRUE 1.712 + ) AS "minus1_fulfilled_count", 1.713 + ( SELECT coalesce(sum("di"."weight"), 0) 1.714 + FROM "opinion" JOIN "direct_interest_snapshot" AS "di" 1.715 + ON "di"."snapshot_id" = "snapshot_id_v" 1.716 + AND "di"."issue_id" = "issue_id_v" 1.717 + AND "di"."member_id" = "opinion"."member_id" 1.718 + WHERE "opinion"."suggestion_id" = "suggestion"."id" 1.719 + AND "opinion"."degree" = 1 1.720 + AND "opinion"."fulfilled" = FALSE 1.721 + ) AS "plus1_unfulfilled_count", 1.722 + ( SELECT coalesce(sum("di"."weight"), 0) 1.723 + FROM "opinion" JOIN "direct_interest_snapshot" AS "di" 1.724 + ON "di"."snapshot_id" = "snapshot_id_v" 1.725 + AND "di"."issue_id" = "issue_id_v" 1.726 + AND "di"."member_id" = "opinion"."member_id" 1.727 + WHERE "opinion"."suggestion_id" = "suggestion"."id" 1.728 + AND "opinion"."degree" = 1 1.729 + AND "opinion"."fulfilled" = TRUE 1.730 + ) AS "plus1_fulfilled_count", 1.731 + ( SELECT coalesce(sum("di"."weight"), 0) 1.732 + FROM "opinion" JOIN "direct_interest_snapshot" AS "di" 1.733 + ON "di"."snapshot_id" = "snapshot_id_v" 1.734 + AND "di"."issue_id" = "issue_id_v" 1.735 + AND "di"."member_id" = "opinion"."member_id" 1.736 + WHERE "opinion"."suggestion_id" = "suggestion"."id" 1.737 + AND "opinion"."degree" = 2 1.738 + AND "opinion"."fulfilled" = FALSE 1.739 + ) AS "plus2_unfulfilled_count", 1.740 + ( SELECT coalesce(sum("di"."weight"), 0) 1.741 + FROM "opinion" JOIN "direct_interest_snapshot" AS "di" 1.742 + ON "di"."snapshot_id" = "snapshot_id_v" 1.743 + AND "di"."issue_id" = "issue_id_v" 1.744 + AND "di"."member_id" = "opinion"."member_id" 1.745 + WHERE "opinion"."suggestion_id" = "suggestion"."id" 1.746 + AND "opinion"."degree" = 2 1.747 + AND "opinion"."fulfilled" = TRUE 1.748 + ) AS "plus2_fulfilled_count" 1.749 + FROM "suggestion" JOIN "initiative" 1.750 + ON "suggestion"."initiative_id" = "initiative"."id" 1.751 + WHERE "initiative"."issue_id" = "issue_id_v"; 1.752 + END LOOP; 1.753 + RETURN "snapshot_id_v"; 1.754 + END; 1.755 + $$; 1.756 + 1.757 +CREATE OR REPLACE FUNCTION "weight_of_added_vote_delegations" 1.758 + ( "issue_id_p" "issue"."id"%TYPE, 1.759 + "member_id_p" "member"."id"%TYPE, 1.760 + "delegate_member_ids_p" "delegating_voter"."delegate_member_ids"%TYPE ) 1.761 + RETURNS "direct_voter"."weight"%TYPE 1.762 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.763 + DECLARE 1.764 + "issue_delegation_row" "issue_delegation"%ROWTYPE; 1.765 + "delegate_member_ids_v" "delegating_voter"."delegate_member_ids"%TYPE; 1.766 + "weight_v" INT4; 1.767 + "sub_weight_v" INT4; 1.768 + BEGIN 1.769 + PERFORM "require_transaction_isolation"(); 1.770 + "weight_v" := 0; 1.771 + FOR "issue_delegation_row" IN 1.772 + SELECT * FROM "issue_delegation" 1.773 + WHERE "trustee_id" = "member_id_p" 1.774 + AND "issue_id" = "issue_id_p" 1.775 + LOOP 1.776 + IF NOT EXISTS ( 1.777 + SELECT NULL FROM "direct_voter" 1.778 + WHERE "member_id" = "issue_delegation_row"."truster_id" 1.779 + AND "issue_id" = "issue_id_p" 1.780 + ) AND NOT EXISTS ( 1.781 + SELECT NULL FROM "delegating_voter" 1.782 + WHERE "member_id" = "issue_delegation_row"."truster_id" 1.783 + AND "issue_id" = "issue_id_p" 1.784 + ) THEN 1.785 + "delegate_member_ids_v" := 1.786 + "member_id_p" || "delegate_member_ids_p"; 1.787 + INSERT INTO "delegating_voter" ( 1.788 + "issue_id", 1.789 + "member_id", 1.790 + "ownweight", 1.791 + "scope", 1.792 + "delegate_member_ids" 1.793 + ) VALUES ( 1.794 + "issue_id_p", 1.795 + "issue_delegation_row"."truster_id", 1.796 + "issue_delegation_row"."weight", 1.797 + "issue_delegation_row"."scope", 1.798 + "delegate_member_ids_v" 1.799 + ); 1.800 + "sub_weight_v" := "issue_delegation_row"."weight" + 1.801 + "weight_of_added_vote_delegations"( 1.802 + "issue_id_p", 1.803 + "issue_delegation_row"."truster_id", 1.804 + "delegate_member_ids_v" 1.805 + ); 1.806 + UPDATE "delegating_voter" 1.807 + SET "weight" = "sub_weight_v" 1.808 + WHERE "issue_id" = "issue_id_p" 1.809 + AND "member_id" = "issue_delegation_row"."truster_id"; 1.810 + "weight_v" := "weight_v" + "sub_weight_v"; 1.811 + END IF; 1.812 + END LOOP; 1.813 + RETURN "weight_v"; 1.814 + END; 1.815 + $$; 1.816 + 1.817 +CREATE OR REPLACE FUNCTION "add_vote_delegations" 1.818 + ( "issue_id_p" "issue"."id"%TYPE ) 1.819 + RETURNS VOID 1.820 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.821 + DECLARE 1.822 + "member_id_v" "member"."id"%TYPE; 1.823 + BEGIN 1.824 + PERFORM "require_transaction_isolation"(); 1.825 + FOR "member_id_v" IN 1.826 + SELECT "member_id" FROM "direct_voter" 1.827 + WHERE "issue_id" = "issue_id_p" 1.828 + LOOP 1.829 + UPDATE "direct_voter" SET 1.830 + "weight" = "ownweight" + "weight_of_added_vote_delegations"( 1.831 + "issue_id_p", 1.832 + "member_id_v", 1.833 + '{}' 1.834 + ) 1.835 + WHERE "member_id" = "member_id_v" 1.836 + AND "issue_id" = "issue_id_p"; 1.837 + END LOOP; 1.838 + RETURN; 1.839 + END; 1.840 + $$; 1.841 + 1.842 +CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE) 1.843 + RETURNS VOID 1.844 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.845 + DECLARE 1.846 + "area_id_v" "area"."id"%TYPE; 1.847 + "unit_id_v" "unit"."id"%TYPE; 1.848 + "member_id_v" "member"."id"%TYPE; 1.849 + BEGIN 1.850 + PERFORM "require_transaction_isolation"(); 1.851 + SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p"; 1.852 + SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v"; 1.853 + -- override protection triggers: 1.854 + INSERT INTO "temporary_transaction_data" ("key", "value") 1.855 + VALUES ('override_protection_triggers', TRUE::TEXT); 1.856 + -- delete timestamp of voting comment: 1.857 + UPDATE "direct_voter" SET "comment_changed" = NULL 1.858 + WHERE "issue_id" = "issue_id_p"; 1.859 + -- delete delegating votes (in cases of manual reset of issue state): 1.860 + DELETE FROM "delegating_voter" 1.861 + WHERE "issue_id" = "issue_id_p"; 1.862 + -- delete votes from non-privileged voters: 1.863 + DELETE FROM "direct_voter" 1.864 + USING ( 1.865 + SELECT "direct_voter"."member_id" 1.866 + FROM "direct_voter" 1.867 + JOIN "member" ON "direct_voter"."member_id" = "member"."id" 1.868 + LEFT JOIN "privilege" 1.869 + ON "privilege"."unit_id" = "unit_id_v" 1.870 + AND "privilege"."member_id" = "direct_voter"."member_id" 1.871 + LEFT JOIN "issue_privilege" 1.872 + ON "issue_privilege"."issue_id" = "issue_id_p" 1.873 + AND "issue_privilege"."member_id" = "direct_voter"."member_id" 1.874 + WHERE "direct_voter"."issue_id" = "issue_id_p" AND ( 1.875 + "member"."active" = FALSE OR 1.876 + COALESCE( 1.877 + "issue_privilege"."voting_right", 1.878 + "privilege"."voting_right", 1.879 + FALSE 1.880 + ) = FALSE 1.881 + ) 1.882 + ) AS "subquery" 1.883 + WHERE "direct_voter"."issue_id" = "issue_id_p" 1.884 + AND "direct_voter"."member_id" = "subquery"."member_id"; 1.885 + -- consider voting weight and delegations: 1.886 + UPDATE "direct_voter" SET "ownweight" = "privilege"."weight" 1.887 + FROM "privilege" 1.888 + WHERE "issue_id" = "issue_id_p" 1.889 + AND "privilege"."unit_id" = "unit_id_v" 1.890 + AND "privilege"."member_id" = "direct_voter"."member_id"; 1.891 + UPDATE "direct_voter" SET "ownweight" = "issue_privilege"."weight" 1.892 + FROM "issue_privilege" 1.893 + WHERE "direct_voter"."issue_id" = "issue_id_p" 1.894 + AND "issue_privilege"."issue_id" = "issue_id_p" 1.895 + AND "issue_privilege"."member_id" = "direct_voter"."member_id"; 1.896 + PERFORM "add_vote_delegations"("issue_id_p"); 1.897 + -- mark first preferences: 1.898 + UPDATE "vote" SET "first_preference" = "subquery"."first_preference" 1.899 + FROM ( 1.900 + SELECT 1.901 + "vote"."initiative_id", 1.902 + "vote"."member_id", 1.903 + CASE WHEN "vote"."grade" > 0 THEN 1.904 + CASE WHEN "vote"."grade" = max("agg"."grade") THEN TRUE ELSE FALSE END 1.905 + ELSE NULL 1.906 + END AS "first_preference" 1.907 + FROM "vote" 1.908 + JOIN "initiative" -- NOTE: due to missing index on issue_id 1.909 + ON "vote"."issue_id" = "initiative"."issue_id" 1.910 + JOIN "vote" AS "agg" 1.911 + ON "initiative"."id" = "agg"."initiative_id" 1.912 + AND "vote"."member_id" = "agg"."member_id" 1.913 + GROUP BY "vote"."initiative_id", "vote"."member_id", "vote"."grade" 1.914 + ) AS "subquery" 1.915 + WHERE "vote"."issue_id" = "issue_id_p" 1.916 + AND "vote"."initiative_id" = "subquery"."initiative_id" 1.917 + AND "vote"."member_id" = "subquery"."member_id"; 1.918 + -- finish overriding protection triggers (avoids garbage): 1.919 + DELETE FROM "temporary_transaction_data" 1.920 + WHERE "key" = 'override_protection_triggers'; 1.921 + -- materialize battle_view: 1.922 + -- NOTE: "closed" column of issue must be set at this point 1.923 + DELETE FROM "battle" WHERE "issue_id" = "issue_id_p"; 1.924 + INSERT INTO "battle" ( 1.925 + "issue_id", 1.926 + "winning_initiative_id", "losing_initiative_id", 1.927 + "count" 1.928 + ) SELECT 1.929 + "issue_id", 1.930 + "winning_initiative_id", "losing_initiative_id", 1.931 + "count" 1.932 + FROM "battle_view" WHERE "issue_id" = "issue_id_p"; 1.933 + -- set voter count: 1.934 + UPDATE "issue" SET 1.935 + "voter_count" = ( 1.936 + SELECT coalesce(sum("weight"), 0) 1.937 + FROM "direct_voter" WHERE "issue_id" = "issue_id_p" 1.938 + ) 1.939 + WHERE "id" = "issue_id_p"; 1.940 + -- copy "positive_votes" and "negative_votes" from "battle" table: 1.941 + -- NOTE: "first_preference_votes" is set to a default of 0 at this step 1.942 + UPDATE "initiative" SET 1.943 + "first_preference_votes" = 0, 1.944 + "positive_votes" = "battle_win"."count", 1.945 + "negative_votes" = "battle_lose"."count" 1.946 + FROM "battle" AS "battle_win", "battle" AS "battle_lose" 1.947 + WHERE 1.948 + "battle_win"."issue_id" = "issue_id_p" AND 1.949 + "battle_win"."winning_initiative_id" = "initiative"."id" AND 1.950 + "battle_win"."losing_initiative_id" ISNULL AND 1.951 + "battle_lose"."issue_id" = "issue_id_p" AND 1.952 + "battle_lose"."losing_initiative_id" = "initiative"."id" AND 1.953 + "battle_lose"."winning_initiative_id" ISNULL; 1.954 + -- calculate "first_preference_votes": 1.955 + -- NOTE: will only set values not equal to zero 1.956 + UPDATE "initiative" SET "first_preference_votes" = "subquery"."sum" 1.957 + FROM ( 1.958 + SELECT "vote"."initiative_id", sum("direct_voter"."weight") 1.959 + FROM "vote" JOIN "direct_voter" 1.960 + ON "vote"."issue_id" = "direct_voter"."issue_id" 1.961 + AND "vote"."member_id" = "direct_voter"."member_id" 1.962 + WHERE "vote"."first_preference" 1.963 + GROUP BY "vote"."initiative_id" 1.964 + ) AS "subquery" 1.965 + WHERE "initiative"."issue_id" = "issue_id_p" 1.966 + AND "initiative"."admitted" 1.967 + AND "initiative"."id" = "subquery"."initiative_id"; 1.968 + END; 1.969 + $$; 1.970 + 1.971 +COMMIT;