liquid_feedback_core
diff core.sql @ 5:1cbdd3975a61
Version beta6
Added function for printing delegation chains
Replaced column member_count of table area by 3 new columns:
- direct_member_count
- member_weight (regarding delegations)
- autoreject_weight
Added calculated field to member_count table, storing the time of computation of the total member count and area member counts
Added view liquid_feedback_version
Code cleanup
Added function for printing delegation chains
Replaced column member_count of table area by 3 new columns:
- direct_member_count
- member_weight (regarding delegations)
- autoreject_weight
Added calculated field to member_count table, storing the time of computation of the total member count and area member counts
Added view liquid_feedback_version
Code cleanup
author | jbe |
---|---|
date | Tue Nov 17 12:00:00 2009 +0100 (2009-11-17) |
parents | 6133c0a62378 |
children | 3ea7a72ed7e7 |
line diff
1.1 --- a/core.sql Sun Nov 15 12:00:00 2009 +0100 1.2 +++ b/core.sql Tue Nov 17 12:00:00 2009 +0100 1.3 @@ -5,6 +5,10 @@ 1.4 1.5 BEGIN; 1.6 1.7 +CREATE VIEW "liquid_feedback_version" AS 1.8 + SELECT * FROM (VALUES ('beta6', NULL, NULL, NULL)) 1.9 + AS "subquery"("string", "major", "minor", "revision"); 1.10 + 1.11 1.12 1.13 ------------------------- 1.14 @@ -31,11 +35,13 @@ 1.15 1.16 1.17 CREATE TABLE "member_count" ( 1.18 - "count" INT4 NOT NULL ); 1.19 + "calculated" TIMESTAMPTZ NOT NULL DEFAULT NOW(), 1.20 + "total_count" INT4 NOT NULL ); 1.21 1.22 -COMMENT ON TABLE "member_count" IS 'Holds a single value (single row, single column) of the total count of active(!) members, as calculated from "member_count_view"'; 1.23 +COMMENT ON TABLE "member_count" IS 'Contains one row which contains the total count of active(!) members and a timestamp indicating when the total member count and area member counts were calculated'; 1.24 1.25 -COMMENT ON COLUMN "member_count"."count" IS 'Total count of active(!) members'; 1.26 +COMMENT ON COLUMN "member_count"."calculated" IS 'timestamp indicating when the total member count and area member counts were calculated'; 1.27 +COMMENT ON COLUMN "member_count"."total_count" IS 'Total count of active(!) members'; 1.28 1.29 1.30 CREATE TABLE "contact" ( 1.31 @@ -100,13 +106,17 @@ 1.32 "active" BOOLEAN NOT NULL DEFAULT TRUE, 1.33 "name" TEXT NOT NULL, 1.34 "description" TEXT NOT NULL DEFAULT '', 1.35 - "member_count" INT4 ); 1.36 + "direct_member_count" INT4, 1.37 + "member_weight" INT4, 1.38 + "autoreject_weight" INT4 ); 1.39 CREATE INDEX "area_active_idx" ON "area" ("active"); 1.40 1.41 COMMENT ON TABLE "area" IS 'Subject areas'; 1.42 1.43 -COMMENT ON COLUMN "area"."active" IS 'TRUE means new issues can be created in this area'; 1.44 -COMMENT ON COLUMN "area"."member_count" IS 'Number of active members of that area, as calculated from view "area_member_count"'; 1.45 +COMMENT ON COLUMN "area"."active" IS 'TRUE means new issues can be created in this area'; 1.46 +COMMENT ON COLUMN "area"."direct_member_count" IS 'Number of active members of that area (ignoring their weight), as calculated from view "area_member_count"'; 1.47 +COMMENT ON COLUMN "area"."member_weight" IS 'Same as "direct_member_count" but respecting delegations'; 1.48 +COMMENT ON COLUMN "area"."autoreject_weight" IS 'Sum of weight of members using the autoreject feature'; 1.49 1.50 1.51 CREATE TABLE "issue" ( 1.52 @@ -151,9 +161,9 @@ 1.53 COMMENT ON COLUMN "issue"."closed" IS 'Point in time, when "admission_time" or "voting_time" have elapsed, and issue is no longer active'; 1.54 COMMENT ON COLUMN "issue"."ranks_available" IS 'TRUE = ranks have been calculated'; 1.55 COMMENT ON COLUMN "issue"."snapshot" IS 'Point in time, when snapshot tables have been updated and "population", "vote_now", "vote_later" and *_count values were precalculated'; 1.56 -COMMENT ON COLUMN "issue"."population" IS 'Calculated from table "direct_population_snapshot"'; 1.57 -COMMENT ON COLUMN "issue"."vote_now" IS 'Calculated from table "direct_interest_snapshot"'; 1.58 -COMMENT ON COLUMN "issue"."vote_later" IS 'Calculated from table "direct_interest_snapshot"'; 1.59 +COMMENT ON COLUMN "issue"."population" IS 'Sum of "weight" column in table "direct_population_snapshot"'; 1.60 +COMMENT ON COLUMN "issue"."vote_now" IS 'Number of votes in favor of voting now, as calculated from table "direct_interest_snapshot"'; 1.61 +COMMENT ON COLUMN "issue"."vote_later" IS 'Number of votes against voting now, as calculated from table "direct_interest_snapshot"'; 1.62 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.63 1.64 1.65 @@ -780,14 +790,163 @@ 1.66 -- Views and helper functions for views -- 1.67 ------------------------------------------ 1.68 1.69 + 1.70 +CREATE TYPE "delegation_scope" AS ENUM 1.71 + ('global', 'area', 'issue'); 1.72 + 1.73 +COMMENT ON TYPE "delegation_scope" IS 'Scope for delegations: ''global'', ''area'', or ''issue'''; 1.74 + 1.75 + 1.76 +CREATE VIEW "global_delegation" AS 1.77 + SELECT 1.78 + "delegation"."id", 1.79 + "delegation"."truster_id", 1.80 + "delegation"."trustee_id" 1.81 + FROM "delegation" JOIN "member" 1.82 + ON "delegation"."trustee_id" = "member"."id" 1.83 + WHERE "delegation"."area_id" ISNULL 1.84 + AND "delegation"."issue_id" ISNULL 1.85 + AND "member"."active"; 1.86 + 1.87 +COMMENT ON VIEW "global_delegation" IS 'Global delegations to active members'; 1.88 + 1.89 + 1.90 +CREATE VIEW "area_delegation" AS 1.91 + SELECT "subquery".* FROM ( 1.92 + SELECT DISTINCT ON ("area"."id", "delegation"."truster_id") 1.93 + "area"."id" AS "area_id", 1.94 + "delegation"."id" AS "id", 1.95 + "delegation"."truster_id" AS "truster_id", 1.96 + "delegation"."trustee_id" AS "trustee_id", 1.97 + CASE WHEN "delegation"."area_id" ISNULL THEN 1.98 + 'global'::"delegation_scope" 1.99 + ELSE 1.100 + 'area'::"delegation_scope" 1.101 + END AS "scope" 1.102 + FROM "area" JOIN "delegation" 1.103 + ON ("delegation"."area_id" ISNULL OR "delegation"."area_id" = "area"."id") 1.104 + AND "delegation"."issue_id" ISNULL 1.105 + ORDER BY 1.106 + "area"."id", 1.107 + "delegation"."truster_id", 1.108 + "delegation"."area_id" NULLS LAST 1.109 + ) AS "subquery" 1.110 + JOIN "member" ON "subquery"."trustee_id" = "member"."id" 1.111 + WHERE "member"."active"; 1.112 + 1.113 +COMMENT ON VIEW "area_delegation" IS 'Active delegations for areas'; 1.114 + 1.115 + 1.116 +CREATE VIEW "issue_delegation" AS 1.117 + SELECT "subquery".* FROM ( 1.118 + SELECT DISTINCT ON ("issue"."id", "delegation"."truster_id") 1.119 + "issue"."id" AS "issue_id", 1.120 + "delegation"."id" AS "id", 1.121 + "delegation"."truster_id" AS "truster_id", 1.122 + "delegation"."trustee_id" AS "trustee_id", 1.123 + CASE 1.124 + WHEN 1.125 + "delegation"."area_id" ISNULL AND 1.126 + "delegation"."issue_id" ISNULL 1.127 + THEN 'global'::"delegation_scope" 1.128 + WHEN 1.129 + "delegation"."area_id" NOTNULL 1.130 + THEN 'area'::"delegation_scope" 1.131 + ELSE 'issue'::"delegation_scope" 1.132 + END AS "scope" 1.133 + FROM "issue" JOIN "delegation" 1.134 + ON ( 1.135 + "delegation"."area_id" ISNULL OR 1.136 + "delegation"."area_id" = "issue"."area_id" 1.137 + ) AND ( 1.138 + "delegation"."issue_id" ISNULL OR 1.139 + "delegation"."issue_id" = "issue"."id" 1.140 + ) 1.141 + ORDER BY 1.142 + "issue"."id", 1.143 + "delegation"."truster_id", 1.144 + "delegation"."issue_id" NULLS LAST, 1.145 + "delegation"."area_id" NULLS LAST 1.146 + ) AS "subquery" 1.147 + JOIN "member" ON "subquery"."trustee_id" = "member"."id" 1.148 + WHERE "member"."active"; 1.149 + 1.150 +COMMENT ON VIEW "issue_delegation" IS 'Active delegations for issues'; 1.151 + 1.152 + 1.153 +CREATE FUNCTION "membership_weight_with_skipping" 1.154 + ( "area_id_p" "area"."id"%TYPE, 1.155 + "member_id_p" "member"."id"%TYPE, 1.156 + "skip_member_ids_p" INT4[] ) -- "member"."id"%TYPE[] 1.157 + RETURNS INT4 1.158 + LANGUAGE 'plpgsql' STABLE AS $$ 1.159 + DECLARE 1.160 + "sum_v" INT4; 1.161 + "delegation_row" "area_delegation"%ROWTYPE; 1.162 + BEGIN 1.163 + "sum_v" := 1; 1.164 + FOR "delegation_row" IN 1.165 + SELECT "area_delegation".* 1.166 + FROM "area_delegation" LEFT JOIN "membership" 1.167 + ON "membership"."area_id" = "area_id_p" 1.168 + AND "membership"."member_id" = "area_delegation"."truster_id" 1.169 + WHERE "area_delegation"."area_id" = "area_id_p" 1.170 + AND "area_delegation"."trustee_id" = "member_id_p" 1.171 + AND "membership"."member_id" ISNULL 1.172 + LOOP 1.173 + IF NOT 1.174 + "skip_member_ids_p" @> ARRAY["delegation_row"."truster_id"] 1.175 + THEN 1.176 + "sum_v" := "sum_v" + "membership_weight_with_skipping"( 1.177 + "area_id_p", 1.178 + "delegation_row"."truster_id", 1.179 + "skip_member_ids_p" || "delegation_row"."truster_id" 1.180 + ); 1.181 + END IF; 1.182 + END LOOP; 1.183 + RETURN "sum_v"; 1.184 + END; 1.185 + $$; 1.186 + 1.187 +CREATE FUNCTION "membership_weight" 1.188 + ( "area_id_p" "area"."id"%TYPE, 1.189 + "member_id_p" "member"."id"%TYPE ) -- "member"."id"%TYPE[] 1.190 + RETURNS INT4 1.191 + LANGUAGE 'plpgsql' STABLE AS $$ 1.192 + BEGIN 1.193 + RETURN "membership_weight_with_skipping"( 1.194 + "area_id_p", 1.195 + "member_id_p", 1.196 + ARRAY["member_id_p"] 1.197 + ); 1.198 + END; 1.199 + $$; 1.200 + 1.201 + 1.202 CREATE VIEW "member_count_view" AS 1.203 - SELECT count(1) FROM "member" WHERE "active"; 1.204 + SELECT count(1) AS "total_count" FROM "member" WHERE "active"; 1.205 1.206 COMMENT ON VIEW "member_count_view" IS 'View used to update "member_count" table'; 1.207 1.208 1.209 CREATE VIEW "area_member_count" AS 1.210 - SELECT "area"."id" AS "area_id", count("member"."id") 1.211 + SELECT 1.212 + "area"."id" AS "area_id", 1.213 + count("member"."id") AS "direct_member_count", 1.214 + coalesce( 1.215 + sum( 1.216 + CASE WHEN "member"."id" NOTNULL THEN 1.217 + "membership_weight"("area"."id", "member"."id") 1.218 + ELSE 0 END 1.219 + ) 1.220 + ) AS "member_weight", 1.221 + coalesce( 1.222 + sum( 1.223 + CASE WHEN "member"."id" NOTNULL AND "membership"."autoreject" THEN 1.224 + "membership_weight"("area"."id", "member"."id") 1.225 + ELSE 0 END 1.226 + ) 1.227 + ) AS "autoreject_weight" 1.228 FROM "area" 1.229 LEFT JOIN "membership" 1.230 ON "area"."id" = "membership"."area_id" 1.231 @@ -795,43 +954,11 @@ 1.232 ON "membership"."member_id" = "member"."id" 1.233 AND "member"."active" 1.234 GROUP BY "area"."id"; 1.235 +-- TODO: count delegations 1.236 1.237 COMMENT ON VIEW "area_member_count" IS 'View used to update "member_count" column of table "area"'; 1.238 1.239 1.240 -CREATE VIEW "issue_delegation_with_overridden_and_inactive" AS 1.241 - SELECT "delegation".*, "issue"."id" AS "resulting_issue_id" 1.242 - FROM "delegation" 1.243 - JOIN "issue" ON 1.244 - ("delegation"."area_id" ISNULL AND "delegation"."issue_id" ISNULL) OR 1.245 - "delegation"."area_id" = "issue"."area_id" OR 1.246 - "delegation"."issue_id" = "issue"."id"; 1.247 - 1.248 -COMMENT ON VIEW "issue_delegation_with_overridden_and_inactive" IS 'Helper view for "issue_delegation"'; 1.249 - 1.250 - 1.251 -CREATE VIEW "issue_delegation" AS 1.252 - SELECT 1.253 - "entry"."id" AS "id", 1.254 - "entry"."truster_id" AS "truster_id", 1.255 - "entry"."trustee_id" AS "trustee_id", 1.256 - "entry"."resulting_issue_id" AS "issue_id" 1.257 - FROM "issue_delegation_with_overridden_and_inactive" AS "entry" 1.258 - JOIN "member" AS "truster" ON "entry"."truster_id" = "truster"."id" 1.259 - JOIN "member" AS "trustee" ON "entry"."trustee_id" = "trustee"."id" 1.260 - LEFT JOIN "issue_delegation_with_overridden_and_inactive" AS "override" 1.261 - ON "entry"."truster_id" = "override"."truster_id" 1.262 - AND "entry"."id" != "override"."id" 1.263 - AND ( 1.264 - ("entry"."area_id" ISNULL AND "entry"."issue_id" ISNULL) OR 1.265 - "override"."issue_id" NOTNULL 1.266 - ) 1.267 - WHERE "truster"."active" AND "trustee"."active" 1.268 - AND "override"."truster_id" ISNULL; 1.269 - 1.270 -COMMENT ON VIEW "issue_delegation" IS 'Resulting delegations for issues, without those involving inactive members'; 1.271 - 1.272 - 1.273 CREATE VIEW "current_draft" AS 1.274 SELECT "draft".* FROM ( 1.275 SELECT 1.276 @@ -923,6 +1050,214 @@ 1.277 1.278 1.279 1.280 +-------------------------------------------------- 1.281 +-- Set returning function for delegation chains -- 1.282 +-------------------------------------------------- 1.283 + 1.284 + 1.285 +CREATE TYPE "delegation_chain_loop_tag" AS ENUM 1.286 + ('first', 'intermediate', 'last', 'repetition'); 1.287 + 1.288 +COMMENT ON TYPE "delegation_chain_loop_tag" IS 'Type for loop tags in "delegation_chain_row" type'; 1.289 + 1.290 + 1.291 +CREATE TYPE "delegation_chain_row" AS ( 1.292 + "index" INT4, 1.293 + "member_id" INT4, 1.294 + "member_active" BOOLEAN, 1.295 + "participation" BOOLEAN, 1.296 + "overridden" BOOLEAN, 1.297 + "scope_in" "delegation_scope", 1.298 + "scope_out" "delegation_scope", 1.299 + "loop" "delegation_chain_loop_tag" ); 1.300 + 1.301 +COMMENT ON TYPE "delegation_chain_row" IS 'Type of rows returned by "delegation_chain"(...) functions'; 1.302 + 1.303 +COMMENT ON COLUMN "delegation_chain_row"."index" IS 'Index starting with 0 and counting up'; 1.304 +COMMENT ON COLUMN "delegation_chain_row"."participation" IS 'In case of delegation chains for issues: interest, for areas: membership, for global delegation chains: always null'; 1.305 +COMMENT ON COLUMN "delegation_chain_row"."overridden" IS 'True, if an entry with lower index has "participation" set to true'; 1.306 +COMMENT ON COLUMN "delegation_chain_row"."scope_in" IS 'Scope of used incoming delegation'; 1.307 +COMMENT ON COLUMN "delegation_chain_row"."scope_out" IS 'Scope of used outgoing delegation'; 1.308 +COMMENT ON COLUMN "delegation_chain_row"."loop" IS 'Not null, if member is part of a loop, see "delegation_chain_loop_tag" type'; 1.309 + 1.310 + 1.311 +CREATE FUNCTION "delegation_chain" 1.312 + ( "member_id_p" "member"."id"%TYPE, 1.313 + "area_id_p" "area"."id"%TYPE, 1.314 + "issue_id_p" "issue"."id"%TYPE, 1.315 + "simulate_trustee_id_p" "member"."id"%TYPE ) 1.316 + RETURNS SETOF "delegation_chain_row" 1.317 + LANGUAGE 'plpgsql' STABLE AS $$ 1.318 + DECLARE 1.319 + "issue_row" "issue"%ROWTYPE; 1.320 + "visited_member_ids" INT4[]; -- "member"."id"%TYPE[] 1.321 + "loop_member_id_v" "member"."id"%TYPE; 1.322 + "output_row" "delegation_chain_row"; 1.323 + "output_rows" "delegation_chain_row"[]; 1.324 + "delegation_row" "delegation"%ROWTYPE; 1.325 + "row_count" INT4; 1.326 + "i" INT4; 1.327 + "loop_v" BOOLEAN; 1.328 + BEGIN 1.329 + SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"; 1.330 + "visited_member_ids" := '{}'; 1.331 + "loop_member_id_v" := NULL; 1.332 + "output_rows" := '{}'; 1.333 + "output_row"."index" := 0; 1.334 + "output_row"."member_id" := "member_id_p"; 1.335 + "output_row"."member_active" := TRUE; 1.336 + "output_row"."participation" := FALSE; 1.337 + "output_row"."overridden" := FALSE; 1.338 + "output_row"."scope_out" := NULL; 1.339 + LOOP 1.340 + IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN 1.341 + "loop_member_id_v" := "output_row"."member_id"; 1.342 + ELSE 1.343 + "visited_member_ids" := 1.344 + "visited_member_ids" || "output_row"."member_id"; 1.345 + END IF; 1.346 + IF "output_row"."participation" THEN 1.347 + "output_row"."overridden" := TRUE; 1.348 + END IF; 1.349 + "output_row"."scope_in" := "output_row"."scope_out"; 1.350 + IF EXISTS ( 1.351 + SELECT NULL FROM "member" 1.352 + WHERE "id" = "output_row"."member_id" AND "active" 1.353 + ) THEN 1.354 + IF "area_id_p" ISNULL AND "issue_id_p" ISNULL THEN 1.355 + SELECT * INTO "delegation_row" FROM "delegation" 1.356 + WHERE "truster_id" = "output_row"."member_id" 1.357 + AND "area_id" ISNULL AND "issue_id" ISNULL; 1.358 + ELSIF "area_id_p" NOTNULL AND "issue_id_p" ISNULL THEN 1.359 + "output_row"."participation" := EXISTS ( 1.360 + SELECT NULL FROM "membership" 1.361 + WHERE "area_id" = "area_id_p" 1.362 + AND "member_id" = "output_row"."member_id" 1.363 + ); 1.364 + SELECT * INTO "delegation_row" FROM "delegation" 1.365 + WHERE "truster_id" = "output_row"."member_id" 1.366 + AND ("area_id" ISNULL OR "area_id" = "area_id_p") 1.367 + AND "issue_id" ISNULL 1.368 + ORDER BY "area_id" NULLS LAST; 1.369 + ELSIF "area_id_p" ISNULL AND "issue_id_p" NOTNULL THEN 1.370 + "output_row"."participation" := EXISTS ( 1.371 + SELECT NULL FROM "interest" 1.372 + WHERE "issue_id" = "issue_id_p" 1.373 + AND "member_id" = "output_row"."member_id" 1.374 + ); 1.375 + SELECT * INTO "delegation_row" FROM "delegation" 1.376 + WHERE "truster_id" = "output_row"."member_id" 1.377 + AND ("area_id" ISNULL OR "area_id" = "issue_row"."area_id") 1.378 + AND ("issue_id" ISNULL OR "issue_id" = "issue_id_p") 1.379 + ORDER BY "issue_id" NULLS LAST, "area_id" NULLS LAST; 1.380 + ELSE 1.381 + RAISE EXCEPTION 'Either area_id or issue_id or both must be NULL.'; 1.382 + END IF; 1.383 + ELSE 1.384 + "output_row"."member_active" := FALSE; 1.385 + "output_row"."participation" := FALSE; 1.386 + "output_row"."scope_out" := NULL; 1.387 + "delegation_row" := ROW(NULL); 1.388 + END IF; 1.389 + IF 1.390 + "output_row"."member_id" = "member_id_p" AND 1.391 + "simulate_trustee_id_p" NOTNULL 1.392 + THEN 1.393 + "output_row"."scope_out" := CASE 1.394 + WHEN "area_id_p" ISNULL AND "issue_id_p" ISNULL THEN 'global' 1.395 + WHEN "area_id_p" NOTNULL AND "issue_id_p" ISNULL THEN 'area' 1.396 + WHEN "area_id_p" ISNULL AND "issue_id_p" NOTNULL THEN 'issue' 1.397 + END; 1.398 + "output_rows" := "output_rows" || "output_row"; 1.399 + "output_row"."member_id" := "simulate_trustee_id_p"; 1.400 + ELSIF "delegation_row"."trustee_id" NOTNULL THEN 1.401 + "output_row"."scope_out" := CASE 1.402 + WHEN 1.403 + "delegation_row"."area_id" ISNULL AND 1.404 + "delegation_row"."issue_id" ISNULL 1.405 + THEN 'global' 1.406 + WHEN 1.407 + "delegation_row"."area_id" NOTNULL AND 1.408 + "delegation_row"."issue_id" ISNULL 1.409 + THEN 'area' 1.410 + WHEN 1.411 + "delegation_row"."area_id" ISNULL AND 1.412 + "delegation_row"."issue_id" NOTNULL 1.413 + THEN 'issue' 1.414 + END; 1.415 + "output_rows" := "output_rows" || "output_row"; 1.416 + "output_row"."member_id" := "delegation_row"."trustee_id"; 1.417 + ELSE 1.418 + "output_row"."scope_out" := NULL; 1.419 + "output_rows" := "output_rows" || "output_row"; 1.420 + EXIT; 1.421 + END IF; 1.422 + EXIT WHEN "loop_member_id_v" NOTNULL; 1.423 + "output_row"."index" := "output_row"."index" + 1; 1.424 + END LOOP; 1.425 + "row_count" := array_upper("output_rows", 1); 1.426 + "i" := 1; 1.427 + "loop_v" := FALSE; 1.428 + LOOP 1.429 + "output_row" := "output_rows"["i"]; 1.430 + EXIT WHEN "output_row"."member_id" ISNULL; 1.431 + IF "loop_v" THEN 1.432 + IF "i" + 1 = "row_count" THEN 1.433 + "output_row"."loop" := 'last'; 1.434 + ELSIF "i" = "row_count" THEN 1.435 + "output_row"."loop" := 'repetition'; 1.436 + ELSE 1.437 + "output_row"."loop" := 'intermediate'; 1.438 + END IF; 1.439 + ELSIF "output_row"."member_id" = "loop_member_id_v" THEN 1.440 + "output_row"."loop" := 'first'; 1.441 + "loop_v" := TRUE; 1.442 + END IF; 1.443 + IF "area_id_p" ISNULL AND "issue_id_p" ISNULL THEN 1.444 + "output_row"."participation" := NULL; 1.445 + END IF; 1.446 + RETURN NEXT "output_row"; 1.447 + "i" := "i" + 1; 1.448 + END LOOP; 1.449 + RETURN; 1.450 + END; 1.451 + $$; 1.452 + 1.453 +COMMENT ON FUNCTION "delegation_chain" 1.454 + ( "member"."id"%TYPE, 1.455 + "area"."id"%TYPE, 1.456 + "issue"."id"%TYPE, 1.457 + "member"."id"%TYPE ) 1.458 + IS 'Helper function for frontends to display delegation chains; Not part of internal voting logic'; 1.459 + 1.460 +CREATE FUNCTION "delegation_chain" 1.461 + ( "member_id_p" "member"."id"%TYPE, 1.462 + "area_id_p" "area"."id"%TYPE, 1.463 + "issue_id_p" "issue"."id"%TYPE ) 1.464 + RETURNS SETOF "delegation_chain_row" 1.465 + LANGUAGE 'plpgsql' STABLE AS $$ 1.466 + DECLARE 1.467 + "result_row" "delegation_chain_row"; 1.468 + BEGIN 1.469 + FOR "result_row" IN 1.470 + SELECT * FROM "delegation_chain"( 1.471 + "member_id_p", "area_id_p", "issue_id_p", NULL 1.472 + ) 1.473 + LOOP 1.474 + RETURN NEXT "result_row"; 1.475 + END LOOP; 1.476 + RETURN; 1.477 + END; 1.478 + $$; 1.479 + 1.480 +COMMENT ON FUNCTION "delegation_chain" 1.481 + ( "member"."id"%TYPE, 1.482 + "area"."id"%TYPE, 1.483 + "issue"."id"%TYPE ) 1.484 + IS 'Shortcut for "delegation_chain"(...) function where 4th parameter is null'; 1.485 + 1.486 + 1.487 + 1.488 ------------------------------ 1.489 -- Comparison by vote count -- 1.490 ------------------------------ 1.491 @@ -1003,11 +1338,14 @@ 1.492 LOCK TABLE "area" IN EXCLUSIVE MODE; 1.493 LOCK TABLE "membership" IN EXCLUSIVE MODE; 1.494 DELETE FROM "member_count"; 1.495 - INSERT INTO "member_count" ("count") 1.496 - SELECT "count" FROM "member_count_view"; 1.497 - UPDATE "area" SET "member_count" = "area_member_count"."count" 1.498 - FROM "area_member_count" 1.499 - WHERE "area_member_count"."area_id" = "area"."id"; 1.500 + INSERT INTO "member_count" ("total_count") 1.501 + SELECT "total_count" FROM "member_count_view"; 1.502 + UPDATE "area" SET 1.503 + "direct_member_count" = "view"."direct_member_count", 1.504 + "member_weight" = "view"."member_weight", 1.505 + "autoreject_weight" = "view"."autoreject_weight" 1.506 + FROM "area_member_count" AS "view" 1.507 + WHERE "view"."area_id" = "area"."id"; 1.508 RETURN; 1.509 END; 1.510 $$; 1.511 @@ -1550,7 +1888,7 @@ 1.512 ----------------------- 1.513 1.514 1.515 -CREATE FUNCTION "weight_of_added_delegations" 1.516 +CREATE FUNCTION "weight_of_added_vote_delegations" 1.517 ( "issue_id_p" "issue"."id"%TYPE, 1.518 "member_id_p" "member"."id"%TYPE, 1.519 "delegate_member_ids_p" "delegating_voter"."delegate_member_ids"%TYPE ) 1.520 @@ -1585,18 +1923,19 @@ 1.521 "issue_id_p", 1.522 "delegate_member_ids_v" 1.523 ); 1.524 - "weight_v" := "weight_v" + 1 + "weight_of_added_delegations"( 1.525 - "issue_id_p", 1.526 - "issue_delegation_row"."truster_id", 1.527 - "delegate_member_ids_v" 1.528 - ); 1.529 + "weight_v" := "weight_v" + 1.530 + 1 + "weight_of_added_vote_delegations"( 1.531 + "issue_id_p", 1.532 + "issue_delegation_row"."truster_id", 1.533 + "delegate_member_ids_v" 1.534 + ); 1.535 END IF; 1.536 END LOOP; 1.537 RETURN "weight_v"; 1.538 END; 1.539 $$; 1.540 1.541 -COMMENT ON FUNCTION "weight_of_added_delegations" 1.542 +COMMENT ON FUNCTION "weight_of_added_vote_delegations" 1.543 ( "issue"."id"%TYPE, 1.544 "member"."id"%TYPE, 1.545 "delegating_voter"."delegate_member_ids"%TYPE ) 1.546 @@ -1615,7 +1954,7 @@ 1.547 WHERE "issue_id" = "issue_id_p" 1.548 LOOP 1.549 UPDATE "direct_voter" SET 1.550 - "weight" = "weight" + "weight_of_added_delegations"( 1.551 + "weight" = "weight" + "weight_of_added_vote_delegations"( 1.552 "issue_id_p", 1.553 "member_id_v", 1.554 '{}'