liquid_feedback_core
annotate update/core-update.v2.0.8-v2.0.9.sql @ 297:779ba19a07df
Different locking levels and different locking order in function "lock_issue"("issue"."id") to avoid deadlocks
(includes update of update/core-update.v2.0.11-v2.1.0.sql)
(includes update of update/core-update.v2.0.11-v2.1.0.sql)
author | jbe |
---|---|
date | Tue Sep 25 02:32:51 2012 +0200 (2012-09-25) |
parents | 7b8966b801e5 |
children |
rev | line source |
---|---|
jbe@244 | 1 BEGIN; |
jbe@244 | 2 |
jbe@244 | 3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS |
jbe@244 | 4 SELECT * FROM (VALUES ('2.0.9', 2, 0, 9)) |
jbe@244 | 5 AS "subquery"("string", "major", "minor", "revision"); |
jbe@244 | 6 |
jbe@249 | 7 -- Bugfix of error in update script to v2.0.0: |
jbe@249 | 8 ALTER TABLE "battle" ALTER COLUMN "winning_initiative_id" DROP NOT NULL; |
jbe@249 | 9 ALTER TABLE "battle" ALTER COLUMN "losing_initiative_id" DROP NOT NULL; |
jbe@249 | 10 |
jbe@248 | 11 CREATE OR REPLACE VIEW "unit_member_count" AS |
jbe@248 | 12 SELECT |
jbe@248 | 13 "unit"."id" AS "unit_id", |
jbe@248 | 14 count("member"."id") AS "member_count" |
jbe@248 | 15 FROM "unit" |
jbe@248 | 16 LEFT JOIN "privilege" |
jbe@248 | 17 ON "privilege"."unit_id" = "unit"."id" |
jbe@248 | 18 AND "privilege"."voting_right" |
jbe@248 | 19 LEFT JOIN "member" |
jbe@248 | 20 ON "member"."id" = "privilege"."member_id" |
jbe@248 | 21 AND "member"."active" |
jbe@248 | 22 GROUP BY "unit"."id"; |
jbe@248 | 23 |
jbe@244 | 24 COMMENT ON TYPE "delegation_chain_row" IS 'Type of rows returned by "delegation_chain" function'; |
jbe@244 | 25 |
jbe@244 | 26 CREATE FUNCTION "delegation_chain_for_closed_issue" |
jbe@244 | 27 ( "member_id_p" "member"."id"%TYPE, |
jbe@244 | 28 "issue_id_p" "issue"."id"%TYPE ) |
jbe@244 | 29 RETURNS SETOF "delegation_chain_row" |
jbe@244 | 30 LANGUAGE 'plpgsql' STABLE AS $$ |
jbe@244 | 31 DECLARE |
jbe@244 | 32 "output_row" "delegation_chain_row"; |
jbe@244 | 33 "direct_voter_row" "direct_voter"%ROWTYPE; |
jbe@244 | 34 "delegating_voter_row" "delegating_voter"%ROWTYPE; |
jbe@244 | 35 BEGIN |
jbe@244 | 36 "output_row"."index" := 0; |
jbe@244 | 37 "output_row"."member_id" := "member_id_p"; |
jbe@244 | 38 "output_row"."member_valid" := TRUE; |
jbe@244 | 39 "output_row"."participation" := FALSE; |
jbe@244 | 40 "output_row"."overridden" := FALSE; |
jbe@244 | 41 "output_row"."disabled_out" := FALSE; |
jbe@244 | 42 LOOP |
jbe@244 | 43 SELECT INTO "direct_voter_row" * FROM "direct_voter" |
jbe@244 | 44 WHERE "issue_id" = "issue_id_p" |
jbe@244 | 45 AND "member_id" = "output_row"."member_id"; |
jbe@244 | 46 IF "direct_voter_row"."member_id" NOTNULL THEN |
jbe@244 | 47 "output_row"."participation" := TRUE; |
jbe@244 | 48 "output_row"."scope_out" := NULL; |
jbe@244 | 49 "output_row"."disabled_out" := NULL; |
jbe@244 | 50 RETURN NEXT "output_row"; |
jbe@244 | 51 RETURN; |
jbe@244 | 52 END IF; |
jbe@244 | 53 SELECT INTO "delegating_voter_row" * FROM "delegating_voter" |
jbe@244 | 54 WHERE "issue_id" = "issue_id_p" |
jbe@244 | 55 AND "member_id" = "output_row"."member_id"; |
jbe@244 | 56 IF "delegating_voter_row"."member_id" ISNULL THEN |
jbe@244 | 57 RETURN; |
jbe@244 | 58 END IF; |
jbe@244 | 59 "output_row"."scope_out" := "delegating_voter_row"."scope"; |
jbe@244 | 60 RETURN NEXT "output_row"; |
jbe@244 | 61 "output_row"."member_id" := "delegating_voter_row"."delegate_member_ids"[1]; |
jbe@244 | 62 "output_row"."scope_in" := "output_row"."scope_out"; |
jbe@244 | 63 END LOOP; |
jbe@244 | 64 END; |
jbe@244 | 65 $$; |
jbe@244 | 66 |
jbe@244 | 67 COMMENT ON FUNCTION "delegation_chain_for_closed_issue" |
jbe@244 | 68 ( "member"."id"%TYPE, |
jbe@244 | 69 "member"."id"%TYPE ) |
jbe@244 | 70 IS 'Helper function for "delegation_chain" function, handling the special case of closed issues after voting'; |
jbe@244 | 71 |
jbe@244 | 72 DROP FUNCTION "delegation_chain" |
jbe@244 | 73 ( "member"."id"%TYPE, |
jbe@244 | 74 "unit"."id"%TYPE, |
jbe@244 | 75 "area"."id"%TYPE, |
jbe@244 | 76 "issue"."id"%TYPE ); |
jbe@244 | 77 |
jbe@244 | 78 DROP FUNCTION "delegation_chain" |
jbe@244 | 79 ( "member"."id"%TYPE, |
jbe@244 | 80 "unit"."id"%TYPE, |
jbe@244 | 81 "area"."id"%TYPE, |
jbe@244 | 82 "issue"."id"%TYPE, |
jbe@244 | 83 "member"."id"%TYPE ); |
jbe@244 | 84 |
jbe@244 | 85 CREATE FUNCTION "delegation_chain" |
jbe@244 | 86 ( "member_id_p" "member"."id"%TYPE, |
jbe@244 | 87 "unit_id_p" "unit"."id"%TYPE, |
jbe@244 | 88 "area_id_p" "area"."id"%TYPE, |
jbe@244 | 89 "issue_id_p" "issue"."id"%TYPE, |
jbe@244 | 90 "simulate_trustee_id_p" "member"."id"%TYPE DEFAULT NULL ) |
jbe@244 | 91 RETURNS SETOF "delegation_chain_row" |
jbe@244 | 92 LANGUAGE 'plpgsql' STABLE AS $$ |
jbe@244 | 93 DECLARE |
jbe@244 | 94 "scope_v" "delegation_scope"; |
jbe@244 | 95 "unit_id_v" "unit"."id"%TYPE; |
jbe@244 | 96 "area_id_v" "area"."id"%TYPE; |
jbe@244 | 97 "issue_row" "issue"%ROWTYPE; |
jbe@244 | 98 "visited_member_ids" INT4[]; -- "member"."id"%TYPE[] |
jbe@244 | 99 "loop_member_id_v" "member"."id"%TYPE; |
jbe@244 | 100 "output_row" "delegation_chain_row"; |
jbe@244 | 101 "output_rows" "delegation_chain_row"[]; |
jbe@244 | 102 "delegation_row" "delegation"%ROWTYPE; |
jbe@244 | 103 "row_count" INT4; |
jbe@244 | 104 "i" INT4; |
jbe@244 | 105 "loop_v" BOOLEAN; |
jbe@244 | 106 BEGIN |
jbe@244 | 107 IF |
jbe@244 | 108 "unit_id_p" NOTNULL AND |
jbe@244 | 109 "area_id_p" ISNULL AND |
jbe@244 | 110 "issue_id_p" ISNULL |
jbe@244 | 111 THEN |
jbe@244 | 112 "scope_v" := 'unit'; |
jbe@244 | 113 "unit_id_v" := "unit_id_p"; |
jbe@244 | 114 ELSIF |
jbe@244 | 115 "unit_id_p" ISNULL AND |
jbe@244 | 116 "area_id_p" NOTNULL AND |
jbe@244 | 117 "issue_id_p" ISNULL |
jbe@244 | 118 THEN |
jbe@244 | 119 "scope_v" := 'area'; |
jbe@244 | 120 "area_id_v" := "area_id_p"; |
jbe@244 | 121 SELECT "unit_id" INTO "unit_id_v" |
jbe@244 | 122 FROM "area" WHERE "id" = "area_id_v"; |
jbe@244 | 123 ELSIF |
jbe@244 | 124 "unit_id_p" ISNULL AND |
jbe@244 | 125 "area_id_p" ISNULL AND |
jbe@244 | 126 "issue_id_p" NOTNULL |
jbe@244 | 127 THEN |
jbe@244 | 128 SELECT INTO "issue_row" * FROM "issue" WHERE "id" = "issue_id_p"; |
jbe@244 | 129 IF "issue_row"."id" ISNULL THEN |
jbe@244 | 130 RETURN; |
jbe@244 | 131 END IF; |
jbe@244 | 132 IF "issue_row"."closed" NOTNULL THEN |
jbe@244 | 133 IF "simulate_trustee_id_p" NOTNULL THEN |
jbe@244 | 134 RAISE EXCEPTION 'Tried to simulate delegation chain for closed issue.'; |
jbe@244 | 135 END IF; |
jbe@244 | 136 FOR "output_row" IN |
jbe@244 | 137 SELECT * FROM |
jbe@244 | 138 "delegation_chain_for_closed_issue"("member_id_p", "issue_id_p") |
jbe@244 | 139 LOOP |
jbe@244 | 140 RETURN NEXT "output_row"; |
jbe@244 | 141 END LOOP; |
jbe@244 | 142 RETURN; |
jbe@244 | 143 END IF; |
jbe@244 | 144 "scope_v" := 'issue'; |
jbe@244 | 145 SELECT "area_id" INTO "area_id_v" |
jbe@244 | 146 FROM "issue" WHERE "id" = "issue_id_p"; |
jbe@244 | 147 SELECT "unit_id" INTO "unit_id_v" |
jbe@244 | 148 FROM "area" WHERE "id" = "area_id_v"; |
jbe@244 | 149 ELSE |
jbe@244 | 150 RAISE EXCEPTION 'Exactly one of unit_id_p, area_id_p, or issue_id_p must be NOTNULL.'; |
jbe@244 | 151 END IF; |
jbe@244 | 152 "visited_member_ids" := '{}'; |
jbe@244 | 153 "loop_member_id_v" := NULL; |
jbe@244 | 154 "output_rows" := '{}'; |
jbe@244 | 155 "output_row"."index" := 0; |
jbe@244 | 156 "output_row"."member_id" := "member_id_p"; |
jbe@244 | 157 "output_row"."member_valid" := TRUE; |
jbe@244 | 158 "output_row"."participation" := FALSE; |
jbe@244 | 159 "output_row"."overridden" := FALSE; |
jbe@244 | 160 "output_row"."disabled_out" := FALSE; |
jbe@244 | 161 "output_row"."scope_out" := NULL; |
jbe@244 | 162 LOOP |
jbe@244 | 163 IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN |
jbe@244 | 164 "loop_member_id_v" := "output_row"."member_id"; |
jbe@244 | 165 ELSE |
jbe@244 | 166 "visited_member_ids" := |
jbe@244 | 167 "visited_member_ids" || "output_row"."member_id"; |
jbe@244 | 168 END IF; |
jbe@244 | 169 IF "output_row"."participation" ISNULL THEN |
jbe@244 | 170 "output_row"."overridden" := NULL; |
jbe@244 | 171 ELSIF "output_row"."participation" THEN |
jbe@244 | 172 "output_row"."overridden" := TRUE; |
jbe@244 | 173 END IF; |
jbe@244 | 174 "output_row"."scope_in" := "output_row"."scope_out"; |
jbe@244 | 175 IF EXISTS ( |
jbe@244 | 176 SELECT NULL FROM "member" JOIN "privilege" |
jbe@244 | 177 ON "privilege"."member_id" = "member"."id" |
jbe@244 | 178 AND "privilege"."unit_id" = "unit_id_v" |
jbe@244 | 179 WHERE "id" = "output_row"."member_id" |
jbe@244 | 180 AND "member"."active" AND "privilege"."voting_right" |
jbe@244 | 181 ) THEN |
jbe@244 | 182 IF "scope_v" = 'unit' THEN |
jbe@244 | 183 SELECT * INTO "delegation_row" FROM "delegation" |
jbe@244 | 184 WHERE "truster_id" = "output_row"."member_id" |
jbe@244 | 185 AND "unit_id" = "unit_id_v"; |
jbe@244 | 186 ELSIF "scope_v" = 'area' THEN |
jbe@244 | 187 "output_row"."participation" := EXISTS ( |
jbe@244 | 188 SELECT NULL FROM "membership" |
jbe@244 | 189 WHERE "area_id" = "area_id_p" |
jbe@244 | 190 AND "member_id" = "output_row"."member_id" |
jbe@244 | 191 ); |
jbe@244 | 192 SELECT * INTO "delegation_row" FROM "delegation" |
jbe@244 | 193 WHERE "truster_id" = "output_row"."member_id" |
jbe@244 | 194 AND ( |
jbe@244 | 195 "unit_id" = "unit_id_v" OR |
jbe@244 | 196 "area_id" = "area_id_v" |
jbe@244 | 197 ) |
jbe@244 | 198 ORDER BY "scope" DESC; |
jbe@244 | 199 ELSIF "scope_v" = 'issue' THEN |
jbe@244 | 200 IF "issue_row"."fully_frozen" ISNULL THEN |
jbe@244 | 201 "output_row"."participation" := EXISTS ( |
jbe@244 | 202 SELECT NULL FROM "interest" |
jbe@244 | 203 WHERE "issue_id" = "issue_id_p" |
jbe@244 | 204 AND "member_id" = "output_row"."member_id" |
jbe@244 | 205 ); |
jbe@244 | 206 ELSE |
jbe@244 | 207 IF "output_row"."member_id" = "member_id_p" THEN |
jbe@244 | 208 "output_row"."participation" := EXISTS ( |
jbe@244 | 209 SELECT NULL FROM "direct_voter" |
jbe@244 | 210 WHERE "issue_id" = "issue_id_p" |
jbe@244 | 211 AND "member_id" = "output_row"."member_id" |
jbe@244 | 212 ); |
jbe@244 | 213 ELSE |
jbe@244 | 214 "output_row"."participation" := NULL; |
jbe@244 | 215 END IF; |
jbe@244 | 216 END IF; |
jbe@244 | 217 SELECT * INTO "delegation_row" FROM "delegation" |
jbe@244 | 218 WHERE "truster_id" = "output_row"."member_id" |
jbe@244 | 219 AND ( |
jbe@244 | 220 "unit_id" = "unit_id_v" OR |
jbe@244 | 221 "area_id" = "area_id_v" OR |
jbe@244 | 222 "issue_id" = "issue_id_p" |
jbe@244 | 223 ) |
jbe@244 | 224 ORDER BY "scope" DESC; |
jbe@244 | 225 END IF; |
jbe@244 | 226 ELSE |
jbe@244 | 227 "output_row"."member_valid" := FALSE; |
jbe@244 | 228 "output_row"."participation" := FALSE; |
jbe@244 | 229 "output_row"."scope_out" := NULL; |
jbe@244 | 230 "delegation_row" := ROW(NULL); |
jbe@244 | 231 END IF; |
jbe@244 | 232 IF |
jbe@244 | 233 "output_row"."member_id" = "member_id_p" AND |
jbe@244 | 234 "simulate_trustee_id_p" NOTNULL |
jbe@244 | 235 THEN |
jbe@244 | 236 "output_row"."scope_out" := "scope_v"; |
jbe@244 | 237 "output_rows" := "output_rows" || "output_row"; |
jbe@244 | 238 "output_row"."member_id" := "simulate_trustee_id_p"; |
jbe@244 | 239 ELSIF "delegation_row"."trustee_id" NOTNULL THEN |
jbe@244 | 240 "output_row"."scope_out" := "delegation_row"."scope"; |
jbe@244 | 241 "output_rows" := "output_rows" || "output_row"; |
jbe@244 | 242 "output_row"."member_id" := "delegation_row"."trustee_id"; |
jbe@244 | 243 ELSIF "delegation_row"."scope" NOTNULL THEN |
jbe@244 | 244 "output_row"."scope_out" := "delegation_row"."scope"; |
jbe@244 | 245 "output_row"."disabled_out" := TRUE; |
jbe@244 | 246 "output_rows" := "output_rows" || "output_row"; |
jbe@244 | 247 EXIT; |
jbe@244 | 248 ELSE |
jbe@244 | 249 "output_row"."scope_out" := NULL; |
jbe@244 | 250 "output_rows" := "output_rows" || "output_row"; |
jbe@244 | 251 EXIT; |
jbe@244 | 252 END IF; |
jbe@244 | 253 EXIT WHEN "loop_member_id_v" NOTNULL; |
jbe@244 | 254 "output_row"."index" := "output_row"."index" + 1; |
jbe@244 | 255 END LOOP; |
jbe@244 | 256 "row_count" := array_upper("output_rows", 1); |
jbe@244 | 257 "i" := 1; |
jbe@244 | 258 "loop_v" := FALSE; |
jbe@244 | 259 LOOP |
jbe@244 | 260 "output_row" := "output_rows"["i"]; |
jbe@244 | 261 EXIT WHEN "output_row" ISNULL; -- NOTE: ISNULL and NOT ... NOTNULL produce different results! |
jbe@244 | 262 IF "loop_v" THEN |
jbe@244 | 263 IF "i" + 1 = "row_count" THEN |
jbe@244 | 264 "output_row"."loop" := 'last'; |
jbe@244 | 265 ELSIF "i" = "row_count" THEN |
jbe@244 | 266 "output_row"."loop" := 'repetition'; |
jbe@244 | 267 ELSE |
jbe@244 | 268 "output_row"."loop" := 'intermediate'; |
jbe@244 | 269 END IF; |
jbe@244 | 270 ELSIF "output_row"."member_id" = "loop_member_id_v" THEN |
jbe@244 | 271 "output_row"."loop" := 'first'; |
jbe@244 | 272 "loop_v" := TRUE; |
jbe@244 | 273 END IF; |
jbe@244 | 274 IF "scope_v" = 'unit' THEN |
jbe@244 | 275 "output_row"."participation" := NULL; |
jbe@244 | 276 END IF; |
jbe@244 | 277 RETURN NEXT "output_row"; |
jbe@244 | 278 "i" := "i" + 1; |
jbe@244 | 279 END LOOP; |
jbe@244 | 280 RETURN; |
jbe@244 | 281 END; |
jbe@244 | 282 $$; |
jbe@244 | 283 |
jbe@244 | 284 COMMENT ON FUNCTION "delegation_chain" |
jbe@244 | 285 ( "member"."id"%TYPE, |
jbe@244 | 286 "unit"."id"%TYPE, |
jbe@244 | 287 "area"."id"%TYPE, |
jbe@244 | 288 "issue"."id"%TYPE, |
jbe@244 | 289 "member"."id"%TYPE ) |
jbe@244 | 290 IS 'Shows a delegation chain for unit, area, or issue; See "delegation_chain_row" type for more information'; |
jbe@244 | 291 |
jbe@244 | 292 CREATE TYPE "delegation_info_loop_type" AS ENUM |
jbe@244 | 293 ('own', 'first', 'first_ellipsis', 'other', 'other_ellipsis'); |
jbe@244 | 294 |
jbe@244 | 295 COMMENT ON TYPE "delegation_info_loop_type" IS 'Type of "delegation_loop" in "delegation_info_type"; ''own'' means loop to self, ''first'' means loop to first trustee, ''first_ellipsis'' means loop to ellipsis after first trustee, ''other'' means loop to other trustee, ''other_ellipsis'' means loop to ellipsis after other trustee'''; |
jbe@244 | 296 |
jbe@244 | 297 CREATE TYPE "delegation_info_type" AS ( |
jbe@244 | 298 "own_participation" BOOLEAN, |
jbe@244 | 299 "own_delegation_scope" "delegation_scope", |
jbe@244 | 300 "first_trustee_id" INT4, |
jbe@244 | 301 "first_trustee_participation" BOOLEAN, |
jbe@244 | 302 "first_trustee_ellipsis" BOOLEAN, |
jbe@244 | 303 "other_trustee_id" INT4, |
jbe@244 | 304 "other_trustee_participation" BOOLEAN, |
jbe@244 | 305 "other_trustee_ellipsis" BOOLEAN, |
jbe@244 | 306 "delegation_loop" "delegation_info_loop_type"); |
jbe@244 | 307 |
jbe@244 | 308 COMMENT ON TYPE "delegation_info_type" IS 'Type of result returned by "delegation_info" function; For meaning of "participation" check comment on "delegation_chain_row" type'; |
jbe@244 | 309 |
jbe@244 | 310 COMMENT ON COLUMN "delegation_info_type"."own_participation" IS 'Member is directly participating'; |
jbe@244 | 311 COMMENT ON COLUMN "delegation_info_type"."own_delegation_scope" IS 'Delegation scope of member'; |
jbe@244 | 312 COMMENT ON COLUMN "delegation_info_type"."first_trustee_id" IS 'Direct trustee of member'; |
jbe@244 | 313 COMMENT ON COLUMN "delegation_info_type"."first_trustee_participation" IS 'Direct trustee of member is participating'; |
jbe@244 | 314 COMMENT ON COLUMN "delegation_info_type"."first_trustee_ellipsis" IS 'Ellipsis in delegation chain after "first_trustee"'; |
jbe@244 | 315 COMMENT ON COLUMN "delegation_info_type"."other_trustee_id" IS 'Another relevant trustee (due to participation)'; |
jbe@244 | 316 COMMENT ON COLUMN "delegation_info_type"."other_trustee_participation" IS 'Another trustee is participating (redundant field: if "other_trustee_id" is set, then "other_trustee_participation" is always TRUE, else "other_trustee_participation" is NULL)'; |
jbe@244 | 317 COMMENT ON COLUMN "delegation_info_type"."other_trustee_ellipsis" IS 'Ellipsis in delegation chain after "other_trustee"'; |
jbe@244 | 318 COMMENT ON COLUMN "delegation_info_type"."delegation_loop" IS 'Non-NULL value, if delegation chain contains a circle; See comment on "delegation_info_loop_type" for details'; |
jbe@244 | 319 |
jbe@244 | 320 CREATE FUNCTION "delegation_info" |
jbe@244 | 321 ( "member_id_p" "member"."id"%TYPE, |
jbe@244 | 322 "unit_id_p" "unit"."id"%TYPE, |
jbe@244 | 323 "area_id_p" "area"."id"%TYPE, |
jbe@244 | 324 "issue_id_p" "issue"."id"%TYPE, |
jbe@244 | 325 "simulate_trustee_id_p" "member"."id"%TYPE DEFAULT NULL ) |
jbe@244 | 326 RETURNS "delegation_info_type" |
jbe@244 | 327 LANGUAGE 'plpgsql' STABLE AS $$ |
jbe@244 | 328 DECLARE |
jbe@244 | 329 "current_row" "delegation_chain_row"; |
jbe@244 | 330 "result" "delegation_info_type"; |
jbe@244 | 331 BEGIN |
jbe@244 | 332 "result"."own_participation" := FALSE; |
jbe@244 | 333 FOR "current_row" IN |
jbe@244 | 334 SELECT * FROM "delegation_chain"( |
jbe@244 | 335 "member_id_p", |
jbe@244 | 336 "unit_id_p", "area_id_p", "issue_id_p", |
jbe@244 | 337 "simulate_trustee_id_p") |
jbe@244 | 338 LOOP |
jbe@244 | 339 IF "current_row"."member_id" = "member_id_p" THEN |
jbe@244 | 340 "result"."own_participation" := "current_row"."participation"; |
jbe@244 | 341 "result"."own_delegation_scope" := "current_row"."scope_out"; |
jbe@244 | 342 IF "current_row"."loop" = 'first' THEN |
jbe@244 | 343 "result"."delegation_loop" := 'own'; |
jbe@244 | 344 END IF; |
jbe@244 | 345 ELSIF |
jbe@244 | 346 "current_row"."member_valid" AND |
jbe@244 | 347 ( "current_row"."loop" ISNULL OR |
jbe@244 | 348 "current_row"."loop" != 'repetition' ) |
jbe@244 | 349 THEN |
jbe@244 | 350 IF "result"."first_trustee_id" ISNULL THEN |
jbe@244 | 351 "result"."first_trustee_id" := "current_row"."member_id"; |
jbe@244 | 352 "result"."first_trustee_participation" := "current_row"."participation"; |
jbe@244 | 353 "result"."first_trustee_ellipsis" := FALSE; |
jbe@244 | 354 IF "current_row"."loop" = 'first' THEN |
jbe@244 | 355 "result"."delegation_loop" := 'first'; |
jbe@244 | 356 END IF; |
jbe@244 | 357 ELSIF "result"."other_trustee_id" ISNULL THEN |
jbe@247 | 358 IF "current_row"."participation" AND NOT "current_row"."overridden" THEN |
jbe@244 | 359 "result"."other_trustee_id" := "current_row"."member_id"; |
jbe@244 | 360 "result"."other_trustee_participation" := TRUE; |
jbe@244 | 361 "result"."other_trustee_ellipsis" := FALSE; |
jbe@244 | 362 IF "current_row"."loop" = 'first' THEN |
jbe@244 | 363 "result"."delegation_loop" := 'other'; |
jbe@244 | 364 END IF; |
jbe@244 | 365 ELSE |
jbe@244 | 366 "result"."first_trustee_ellipsis" := TRUE; |
jbe@244 | 367 IF "current_row"."loop" = 'first' THEN |
jbe@244 | 368 "result"."delegation_loop" := 'first_ellipsis'; |
jbe@244 | 369 END IF; |
jbe@244 | 370 END IF; |
jbe@244 | 371 ELSE |
jbe@244 | 372 "result"."other_trustee_ellipsis" := TRUE; |
jbe@244 | 373 IF "current_row"."loop" = 'first' THEN |
jbe@244 | 374 "result"."delegation_loop" := 'other_ellipsis'; |
jbe@244 | 375 END IF; |
jbe@244 | 376 END IF; |
jbe@244 | 377 END IF; |
jbe@244 | 378 END LOOP; |
jbe@244 | 379 RETURN "result"; |
jbe@244 | 380 END; |
jbe@244 | 381 $$; |
jbe@244 | 382 |
jbe@244 | 383 COMMENT ON FUNCTION "delegation_info" |
jbe@244 | 384 ( "member"."id"%TYPE, |
jbe@244 | 385 "unit"."id"%TYPE, |
jbe@244 | 386 "area"."id"%TYPE, |
jbe@244 | 387 "issue"."id"%TYPE, |
jbe@244 | 388 "member"."id"%TYPE ) |
jbe@244 | 389 IS 'Notable information about a delegation chain for unit, area, or issue; See "delegation_info_type" for more information'; |
jbe@244 | 390 |
jbe@250 | 391 CREATE OR REPLACE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE) |
jbe@250 | 392 RETURNS VOID |
jbe@250 | 393 LANGUAGE 'plpgsql' VOLATILE AS $$ |
jbe@250 | 394 DECLARE |
jbe@250 | 395 "issue_row" "issue"%ROWTYPE; |
jbe@250 | 396 "policy_row" "policy"%ROWTYPE; |
jbe@250 | 397 "dimension_v" INTEGER; |
jbe@250 | 398 "vote_matrix" INT4[][]; -- absolute votes |
jbe@250 | 399 "matrix" INT8[][]; -- defeat strength / best paths |
jbe@250 | 400 "i" INTEGER; |
jbe@250 | 401 "j" INTEGER; |
jbe@250 | 402 "k" INTEGER; |
jbe@250 | 403 "battle_row" "battle"%ROWTYPE; |
jbe@250 | 404 "rank_ary" INT4[]; |
jbe@250 | 405 "rank_v" INT4; |
jbe@250 | 406 "done_v" INTEGER; |
jbe@250 | 407 "winners_ary" INTEGER[]; |
jbe@250 | 408 "initiative_id_v" "initiative"."id"%TYPE; |
jbe@250 | 409 BEGIN |
jbe@250 | 410 SELECT * INTO "issue_row" |
jbe@250 | 411 FROM "issue" WHERE "id" = "issue_id_p" |
jbe@250 | 412 FOR UPDATE; |
jbe@250 | 413 SELECT * INTO "policy_row" |
jbe@250 | 414 FROM "policy" WHERE "id" = "issue_row"."policy_id"; |
jbe@250 | 415 SELECT count(1) INTO "dimension_v" |
jbe@250 | 416 FROM "battle_participant" WHERE "issue_id" = "issue_id_p"; |
jbe@250 | 417 -- Create "vote_matrix" with absolute number of votes in pairwise |
jbe@250 | 418 -- comparison: |
jbe@250 | 419 "vote_matrix" := array_fill(NULL::INT4, ARRAY["dimension_v", "dimension_v"]); |
jbe@250 | 420 "i" := 1; |
jbe@250 | 421 "j" := 2; |
jbe@250 | 422 FOR "battle_row" IN |
jbe@250 | 423 SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p" |
jbe@250 | 424 ORDER BY |
jbe@250 | 425 "winning_initiative_id" NULLS LAST, |
jbe@250 | 426 "losing_initiative_id" NULLS LAST |
jbe@250 | 427 LOOP |
jbe@250 | 428 "vote_matrix"["i"]["j"] := "battle_row"."count"; |
jbe@250 | 429 IF "j" = "dimension_v" THEN |
jbe@250 | 430 "i" := "i" + 1; |
jbe@250 | 431 "j" := 1; |
jbe@250 | 432 ELSE |
jbe@250 | 433 "j" := "j" + 1; |
jbe@250 | 434 IF "j" = "i" THEN |
jbe@250 | 435 "j" := "j" + 1; |
jbe@250 | 436 END IF; |
jbe@250 | 437 END IF; |
jbe@250 | 438 END LOOP; |
jbe@250 | 439 IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN |
jbe@250 | 440 RAISE EXCEPTION 'Wrong battle count (should not happen)'; |
jbe@250 | 441 END IF; |
jbe@250 | 442 -- Store defeat strengths in "matrix" using "defeat_strength" |
jbe@250 | 443 -- function: |
jbe@250 | 444 "matrix" := array_fill(NULL::INT8, ARRAY["dimension_v", "dimension_v"]); |
jbe@250 | 445 "i" := 1; |
jbe@250 | 446 LOOP |
jbe@250 | 447 "j" := 1; |
jbe@250 | 448 LOOP |
jbe@250 | 449 IF "i" != "j" THEN |
jbe@250 | 450 "matrix"["i"]["j"] := "defeat_strength"( |
jbe@250 | 451 "vote_matrix"["i"]["j"], |
jbe@250 | 452 "vote_matrix"["j"]["i"] |
jbe@250 | 453 ); |
jbe@250 | 454 END IF; |
jbe@250 | 455 EXIT WHEN "j" = "dimension_v"; |
jbe@250 | 456 "j" := "j" + 1; |
jbe@250 | 457 END LOOP; |
jbe@250 | 458 EXIT WHEN "i" = "dimension_v"; |
jbe@250 | 459 "i" := "i" + 1; |
jbe@250 | 460 END LOOP; |
jbe@250 | 461 -- Find best paths: |
jbe@250 | 462 "i" := 1; |
jbe@250 | 463 LOOP |
jbe@250 | 464 "j" := 1; |
jbe@250 | 465 LOOP |
jbe@250 | 466 IF "i" != "j" THEN |
jbe@250 | 467 "k" := 1; |
jbe@250 | 468 LOOP |
jbe@250 | 469 IF "i" != "k" AND "j" != "k" THEN |
jbe@250 | 470 IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN |
jbe@250 | 471 IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN |
jbe@250 | 472 "matrix"["j"]["k"] := "matrix"["j"]["i"]; |
jbe@250 | 473 END IF; |
jbe@250 | 474 ELSE |
jbe@250 | 475 IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN |
jbe@250 | 476 "matrix"["j"]["k"] := "matrix"["i"]["k"]; |
jbe@250 | 477 END IF; |
jbe@250 | 478 END IF; |
jbe@250 | 479 END IF; |
jbe@250 | 480 EXIT WHEN "k" = "dimension_v"; |
jbe@250 | 481 "k" := "k" + 1; |
jbe@250 | 482 END LOOP; |
jbe@250 | 483 END IF; |
jbe@250 | 484 EXIT WHEN "j" = "dimension_v"; |
jbe@250 | 485 "j" := "j" + 1; |
jbe@250 | 486 END LOOP; |
jbe@250 | 487 EXIT WHEN "i" = "dimension_v"; |
jbe@250 | 488 "i" := "i" + 1; |
jbe@250 | 489 END LOOP; |
jbe@250 | 490 -- Determine order of winners: |
jbe@250 | 491 "rank_ary" := array_fill(NULL::INT4, ARRAY["dimension_v"]); |
jbe@250 | 492 "rank_v" := 1; |
jbe@250 | 493 "done_v" := 0; |
jbe@250 | 494 LOOP |
jbe@250 | 495 "winners_ary" := '{}'; |
jbe@250 | 496 "i" := 1; |
jbe@250 | 497 LOOP |
jbe@250 | 498 IF "rank_ary"["i"] ISNULL THEN |
jbe@250 | 499 "j" := 1; |
jbe@250 | 500 LOOP |
jbe@250 | 501 IF |
jbe@250 | 502 "i" != "j" AND |
jbe@250 | 503 "rank_ary"["j"] ISNULL AND |
jbe@250 | 504 "matrix"["j"]["i"] > "matrix"["i"]["j"] |
jbe@250 | 505 THEN |
jbe@250 | 506 -- someone else is better |
jbe@250 | 507 EXIT; |
jbe@250 | 508 END IF; |
jbe@250 | 509 IF "j" = "dimension_v" THEN |
jbe@250 | 510 -- noone is better |
jbe@250 | 511 "winners_ary" := "winners_ary" || "i"; |
jbe@250 | 512 EXIT; |
jbe@250 | 513 END IF; |
jbe@250 | 514 "j" := "j" + 1; |
jbe@250 | 515 END LOOP; |
jbe@250 | 516 END IF; |
jbe@250 | 517 EXIT WHEN "i" = "dimension_v"; |
jbe@250 | 518 "i" := "i" + 1; |
jbe@250 | 519 END LOOP; |
jbe@250 | 520 "i" := 1; |
jbe@250 | 521 LOOP |
jbe@250 | 522 "rank_ary"["winners_ary"["i"]] := "rank_v"; |
jbe@250 | 523 "done_v" := "done_v" + 1; |
jbe@250 | 524 EXIT WHEN "i" = array_upper("winners_ary", 1); |
jbe@250 | 525 "i" := "i" + 1; |
jbe@250 | 526 END LOOP; |
jbe@250 | 527 EXIT WHEN "done_v" = "dimension_v"; |
jbe@250 | 528 "rank_v" := "rank_v" + 1; |
jbe@250 | 529 END LOOP; |
jbe@250 | 530 -- write preliminary results: |
jbe@250 | 531 "i" := 1; |
jbe@250 | 532 FOR "initiative_id_v" IN |
jbe@250 | 533 SELECT "id" FROM "initiative" |
jbe@250 | 534 WHERE "issue_id" = "issue_id_p" AND "admitted" |
jbe@250 | 535 ORDER BY "id" |
jbe@250 | 536 LOOP |
jbe@250 | 537 UPDATE "initiative" SET |
jbe@250 | 538 "direct_majority" = |
jbe@250 | 539 CASE WHEN "policy_row"."direct_majority_strict" THEN |
jbe@250 | 540 "positive_votes" * "policy_row"."direct_majority_den" > |
jbe@250 | 541 "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes") |
jbe@250 | 542 ELSE |
jbe@250 | 543 "positive_votes" * "policy_row"."direct_majority_den" >= |
jbe@250 | 544 "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes") |
jbe@250 | 545 END |
jbe@250 | 546 AND "positive_votes" >= "policy_row"."direct_majority_positive" |
jbe@250 | 547 AND "issue_row"."voter_count"-"negative_votes" >= |
jbe@250 | 548 "policy_row"."direct_majority_non_negative", |
jbe@250 | 549 "indirect_majority" = |
jbe@250 | 550 CASE WHEN "policy_row"."indirect_majority_strict" THEN |
jbe@250 | 551 "positive_votes" * "policy_row"."indirect_majority_den" > |
jbe@250 | 552 "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes") |
jbe@250 | 553 ELSE |
jbe@250 | 554 "positive_votes" * "policy_row"."indirect_majority_den" >= |
jbe@250 | 555 "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes") |
jbe@250 | 556 END |
jbe@250 | 557 AND "positive_votes" >= "policy_row"."indirect_majority_positive" |
jbe@250 | 558 AND "issue_row"."voter_count"-"negative_votes" >= |
jbe@250 | 559 "policy_row"."indirect_majority_non_negative", |
jbe@250 | 560 "schulze_rank" = "rank_ary"["i"], |
jbe@250 | 561 "better_than_status_quo" = "rank_ary"["i"] < "rank_ary"["dimension_v"], |
jbe@250 | 562 "worse_than_status_quo" = "rank_ary"["i"] > "rank_ary"["dimension_v"], |
jbe@250 | 563 "multistage_majority" = "rank_ary"["i"] >= "rank_ary"["dimension_v"], |
jbe@250 | 564 "reverse_beat_path" = "matrix"["dimension_v"]["i"] >= 0, |
jbe@250 | 565 "eligible" = FALSE, |
jbe@250 | 566 "winner" = FALSE, |
jbe@250 | 567 "rank" = NULL -- NOTE: in cases of manual reset of issue state |
jbe@250 | 568 WHERE "id" = "initiative_id_v"; |
jbe@250 | 569 "i" := "i" + 1; |
jbe@250 | 570 END LOOP; |
jbe@250 | 571 IF "i" != "dimension_v" THEN |
jbe@250 | 572 RAISE EXCEPTION 'Wrong winner count (should not happen)'; |
jbe@250 | 573 END IF; |
jbe@250 | 574 -- take indirect majorities into account: |
jbe@250 | 575 LOOP |
jbe@250 | 576 UPDATE "initiative" SET "indirect_majority" = TRUE |
jbe@250 | 577 FROM ( |
jbe@250 | 578 SELECT "new_initiative"."id" AS "initiative_id" |
jbe@250 | 579 FROM "initiative" "old_initiative" |
jbe@250 | 580 JOIN "initiative" "new_initiative" |
jbe@250 | 581 ON "new_initiative"."issue_id" = "issue_id_p" |
jbe@250 | 582 AND "new_initiative"."indirect_majority" = FALSE |
jbe@250 | 583 JOIN "battle" "battle_win" |
jbe@250 | 584 ON "battle_win"."issue_id" = "issue_id_p" |
jbe@250 | 585 AND "battle_win"."winning_initiative_id" = "new_initiative"."id" |
jbe@250 | 586 AND "battle_win"."losing_initiative_id" = "old_initiative"."id" |
jbe@250 | 587 JOIN "battle" "battle_lose" |
jbe@250 | 588 ON "battle_lose"."issue_id" = "issue_id_p" |
jbe@250 | 589 AND "battle_lose"."losing_initiative_id" = "new_initiative"."id" |
jbe@250 | 590 AND "battle_lose"."winning_initiative_id" = "old_initiative"."id" |
jbe@250 | 591 WHERE "old_initiative"."issue_id" = "issue_id_p" |
jbe@250 | 592 AND "old_initiative"."indirect_majority" = TRUE |
jbe@250 | 593 AND CASE WHEN "policy_row"."indirect_majority_strict" THEN |
jbe@250 | 594 "battle_win"."count" * "policy_row"."indirect_majority_den" > |
jbe@250 | 595 "policy_row"."indirect_majority_num" * |
jbe@250 | 596 ("battle_win"."count"+"battle_lose"."count") |
jbe@250 | 597 ELSE |
jbe@250 | 598 "battle_win"."count" * "policy_row"."indirect_majority_den" >= |
jbe@250 | 599 "policy_row"."indirect_majority_num" * |
jbe@250 | 600 ("battle_win"."count"+"battle_lose"."count") |
jbe@250 | 601 END |
jbe@250 | 602 AND "battle_win"."count" >= "policy_row"."indirect_majority_positive" |
jbe@250 | 603 AND "issue_row"."voter_count"-"battle_lose"."count" >= |
jbe@250 | 604 "policy_row"."indirect_majority_non_negative" |
jbe@250 | 605 ) AS "subquery" |
jbe@250 | 606 WHERE "id" = "subquery"."initiative_id"; |
jbe@250 | 607 EXIT WHEN NOT FOUND; |
jbe@250 | 608 END LOOP; |
jbe@250 | 609 -- set "multistage_majority" for remaining matching initiatives: |
jbe@250 | 610 UPDATE "initiative" SET "multistage_majority" = TRUE |
jbe@250 | 611 FROM ( |
jbe@250 | 612 SELECT "losing_initiative"."id" AS "initiative_id" |
jbe@250 | 613 FROM "initiative" "losing_initiative" |
jbe@250 | 614 JOIN "initiative" "winning_initiative" |
jbe@250 | 615 ON "winning_initiative"."issue_id" = "issue_id_p" |
jbe@250 | 616 AND "winning_initiative"."admitted" |
jbe@250 | 617 JOIN "battle" "battle_win" |
jbe@250 | 618 ON "battle_win"."issue_id" = "issue_id_p" |
jbe@250 | 619 AND "battle_win"."winning_initiative_id" = "winning_initiative"."id" |
jbe@250 | 620 AND "battle_win"."losing_initiative_id" = "losing_initiative"."id" |
jbe@250 | 621 JOIN "battle" "battle_lose" |
jbe@250 | 622 ON "battle_lose"."issue_id" = "issue_id_p" |
jbe@250 | 623 AND "battle_lose"."losing_initiative_id" = "winning_initiative"."id" |
jbe@250 | 624 AND "battle_lose"."winning_initiative_id" = "losing_initiative"."id" |
jbe@250 | 625 WHERE "losing_initiative"."issue_id" = "issue_id_p" |
jbe@250 | 626 AND "losing_initiative"."admitted" |
jbe@250 | 627 AND "winning_initiative"."schulze_rank" < |
jbe@250 | 628 "losing_initiative"."schulze_rank" |
jbe@250 | 629 AND "battle_win"."count" > "battle_lose"."count" |
jbe@250 | 630 AND ( |
jbe@250 | 631 "battle_win"."count" > "winning_initiative"."positive_votes" OR |
jbe@250 | 632 "battle_lose"."count" < "losing_initiative"."negative_votes" ) |
jbe@250 | 633 ) AS "subquery" |
jbe@250 | 634 WHERE "id" = "subquery"."initiative_id"; |
jbe@250 | 635 -- mark eligible initiatives: |
jbe@250 | 636 UPDATE "initiative" SET "eligible" = TRUE |
jbe@250 | 637 WHERE "issue_id" = "issue_id_p" |
jbe@250 | 638 AND "initiative"."direct_majority" |
jbe@250 | 639 AND "initiative"."indirect_majority" |
jbe@250 | 640 AND "initiative"."better_than_status_quo" |
jbe@250 | 641 AND ( |
jbe@250 | 642 "policy_row"."no_multistage_majority" = FALSE OR |
jbe@250 | 643 "initiative"."multistage_majority" = FALSE ) |
jbe@250 | 644 AND ( |
jbe@250 | 645 "policy_row"."no_reverse_beat_path" = FALSE OR |
jbe@250 | 646 "initiative"."reverse_beat_path" = FALSE ); |
jbe@250 | 647 -- mark final winner: |
jbe@250 | 648 UPDATE "initiative" SET "winner" = TRUE |
jbe@250 | 649 FROM ( |
jbe@250 | 650 SELECT "id" AS "initiative_id" |
jbe@250 | 651 FROM "initiative" |
jbe@250 | 652 WHERE "issue_id" = "issue_id_p" AND "eligible" |
jbe@250 | 653 ORDER BY |
jbe@250 | 654 "schulze_rank", |
jbe@250 | 655 "vote_ratio"("positive_votes", "negative_votes"), |
jbe@250 | 656 "id" |
jbe@250 | 657 LIMIT 1 |
jbe@250 | 658 ) AS "subquery" |
jbe@250 | 659 WHERE "id" = "subquery"."initiative_id"; |
jbe@250 | 660 -- write (final) ranks: |
jbe@250 | 661 "rank_v" := 1; |
jbe@250 | 662 FOR "initiative_id_v" IN |
jbe@250 | 663 SELECT "id" |
jbe@250 | 664 FROM "initiative" |
jbe@250 | 665 WHERE "issue_id" = "issue_id_p" AND "admitted" |
jbe@250 | 666 ORDER BY |
jbe@250 | 667 "winner" DESC, |
jbe@250 | 668 "eligible" DESC, |
jbe@250 | 669 "schulze_rank", |
jbe@250 | 670 "vote_ratio"("positive_votes", "negative_votes"), |
jbe@250 | 671 "id" |
jbe@250 | 672 LOOP |
jbe@250 | 673 UPDATE "initiative" SET "rank" = "rank_v" |
jbe@250 | 674 WHERE "id" = "initiative_id_v"; |
jbe@250 | 675 "rank_v" := "rank_v" + 1; |
jbe@250 | 676 END LOOP; |
jbe@250 | 677 -- set schulze rank of status quo and mark issue as finished: |
jbe@250 | 678 UPDATE "issue" SET |
jbe@250 | 679 "status_quo_schulze_rank" = "rank_ary"["dimension_v"], |
jbe@250 | 680 "state" = |
jbe@250 | 681 CASE WHEN EXISTS ( |
jbe@250 | 682 SELECT NULL FROM "initiative" |
jbe@250 | 683 WHERE "issue_id" = "issue_id_p" AND "winner" |
jbe@250 | 684 ) THEN |
jbe@250 | 685 'finished_with_winner'::"issue_state" |
jbe@250 | 686 ELSE |
jbe@250 | 687 'finished_without_winner'::"issue_state" |
jbe@250 | 688 END, |
jbe@250 | 689 "ranks_available" = TRUE |
jbe@250 | 690 WHERE "id" = "issue_id_p"; |
jbe@250 | 691 RETURN; |
jbe@250 | 692 END; |
jbe@250 | 693 $$; |
jbe@250 | 694 |
jbe@244 | 695 COMMIT; |