liquid_feedback_core

annotate update/core-update.v2.0.8-v2.0.9.sql @ 495:bb420abbc2fa

find changesets by author, revision, files, or words in the commit message
Removed NOT NULL constraint for column "last_suggestion_id" in table "initiative_notification_sent" (NULL means that no suggestion existed)
author jbe
date Sun Apr 03 20:04:31 2016 +0200 (2016-04-03)
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;

Impressum / About Us