jbe@305: BEGIN; jbe@305: jbe@305: CREATE OR REPLACE VIEW "liquid_feedback_version" AS jbe@305: SELECT * FROM (VALUES ('2.0.12', 2, 0, 12)) jbe@305: AS "subquery"("string", "major", "minor", "revision"); jbe@305: jbe@305: CREATE OR REPLACE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE) jbe@305: RETURNS VOID jbe@305: LANGUAGE 'plpgsql' VOLATILE AS $$ jbe@305: DECLARE jbe@305: "issue_row" "issue"%ROWTYPE; jbe@305: "policy_row" "policy"%ROWTYPE; jbe@305: "dimension_v" INTEGER; jbe@305: "vote_matrix" INT4[][]; -- absolute votes jbe@305: "matrix" INT8[][]; -- defeat strength / best paths jbe@305: "i" INTEGER; jbe@305: "j" INTEGER; jbe@305: "k" INTEGER; jbe@305: "battle_row" "battle"%ROWTYPE; jbe@305: "rank_ary" INT4[]; jbe@305: "rank_v" INT4; jbe@305: "done_v" INTEGER; jbe@305: "winners_ary" INTEGER[]; jbe@305: "initiative_id_v" "initiative"."id"%TYPE; jbe@305: BEGIN jbe@305: SELECT * INTO "issue_row" jbe@305: FROM "issue" WHERE "id" = "issue_id_p" jbe@305: FOR UPDATE; jbe@305: SELECT * INTO "policy_row" jbe@305: FROM "policy" WHERE "id" = "issue_row"."policy_id"; jbe@305: SELECT count(1) INTO "dimension_v" jbe@305: FROM "battle_participant" WHERE "issue_id" = "issue_id_p"; jbe@305: -- Create "vote_matrix" with absolute number of votes in pairwise jbe@305: -- comparison: jbe@305: "vote_matrix" := array_fill(NULL::INT4, ARRAY["dimension_v", "dimension_v"]); jbe@305: "i" := 1; jbe@305: "j" := 2; jbe@305: FOR "battle_row" IN jbe@305: SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p" jbe@305: ORDER BY jbe@305: "winning_initiative_id" NULLS LAST, jbe@305: "losing_initiative_id" NULLS LAST jbe@305: LOOP jbe@305: "vote_matrix"["i"]["j"] := "battle_row"."count"; jbe@305: IF "j" = "dimension_v" THEN jbe@305: "i" := "i" + 1; jbe@305: "j" := 1; jbe@305: ELSE jbe@305: "j" := "j" + 1; jbe@305: IF "j" = "i" THEN jbe@305: "j" := "j" + 1; jbe@305: END IF; jbe@305: END IF; jbe@305: END LOOP; jbe@305: IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN jbe@305: RAISE EXCEPTION 'Wrong battle count (should not happen)'; jbe@305: END IF; jbe@305: -- Store defeat strengths in "matrix" using "defeat_strength" jbe@305: -- function: jbe@305: "matrix" := array_fill(NULL::INT8, ARRAY["dimension_v", "dimension_v"]); jbe@305: "i" := 1; jbe@305: LOOP jbe@305: "j" := 1; jbe@305: LOOP jbe@305: IF "i" != "j" THEN jbe@305: "matrix"["i"]["j"] := "defeat_strength"( jbe@305: "vote_matrix"["i"]["j"], jbe@305: "vote_matrix"["j"]["i"] jbe@305: ); jbe@305: END IF; jbe@305: EXIT WHEN "j" = "dimension_v"; jbe@305: "j" := "j" + 1; jbe@305: END LOOP; jbe@305: EXIT WHEN "i" = "dimension_v"; jbe@305: "i" := "i" + 1; jbe@305: END LOOP; jbe@305: -- Find best paths: jbe@305: "i" := 1; jbe@305: LOOP jbe@305: "j" := 1; jbe@305: LOOP jbe@305: IF "i" != "j" THEN jbe@305: "k" := 1; jbe@305: LOOP jbe@305: IF "i" != "k" AND "j" != "k" THEN jbe@305: IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN jbe@305: IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN jbe@305: "matrix"["j"]["k"] := "matrix"["j"]["i"]; jbe@305: END IF; jbe@305: ELSE jbe@305: IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN jbe@305: "matrix"["j"]["k"] := "matrix"["i"]["k"]; jbe@305: END IF; jbe@305: END IF; jbe@305: END IF; jbe@305: EXIT WHEN "k" = "dimension_v"; jbe@305: "k" := "k" + 1; jbe@305: END LOOP; jbe@305: END IF; jbe@305: EXIT WHEN "j" = "dimension_v"; jbe@305: "j" := "j" + 1; jbe@305: END LOOP; jbe@305: EXIT WHEN "i" = "dimension_v"; jbe@305: "i" := "i" + 1; jbe@305: END LOOP; jbe@305: -- Determine order of winners: jbe@305: "rank_ary" := array_fill(NULL::INT4, ARRAY["dimension_v"]); jbe@305: "rank_v" := 1; jbe@305: "done_v" := 0; jbe@305: LOOP jbe@305: "winners_ary" := '{}'; jbe@305: "i" := 1; jbe@305: LOOP jbe@305: IF "rank_ary"["i"] ISNULL THEN jbe@305: "j" := 1; jbe@305: LOOP jbe@305: IF jbe@305: "i" != "j" AND jbe@305: "rank_ary"["j"] ISNULL AND jbe@305: "matrix"["j"]["i"] > "matrix"["i"]["j"] jbe@305: THEN jbe@305: -- someone else is better jbe@305: EXIT; jbe@305: END IF; jbe@305: IF "j" = "dimension_v" THEN jbe@305: -- noone is better jbe@305: "winners_ary" := "winners_ary" || "i"; jbe@305: EXIT; jbe@305: END IF; jbe@305: "j" := "j" + 1; jbe@305: END LOOP; jbe@305: END IF; jbe@305: EXIT WHEN "i" = "dimension_v"; jbe@305: "i" := "i" + 1; jbe@305: END LOOP; jbe@305: "i" := 1; jbe@305: LOOP jbe@305: "rank_ary"["winners_ary"["i"]] := "rank_v"; jbe@305: "done_v" := "done_v" + 1; jbe@305: EXIT WHEN "i" = array_upper("winners_ary", 1); jbe@305: "i" := "i" + 1; jbe@305: END LOOP; jbe@305: EXIT WHEN "done_v" = "dimension_v"; jbe@305: "rank_v" := "rank_v" + 1; jbe@305: END LOOP; jbe@305: -- write preliminary results: jbe@305: "i" := 1; jbe@305: FOR "initiative_id_v" IN jbe@305: SELECT "id" FROM "initiative" jbe@305: WHERE "issue_id" = "issue_id_p" AND "admitted" jbe@305: ORDER BY "id" jbe@305: LOOP jbe@305: UPDATE "initiative" SET jbe@305: "direct_majority" = jbe@305: CASE WHEN "policy_row"."direct_majority_strict" THEN jbe@305: "positive_votes" * "policy_row"."direct_majority_den" > jbe@305: "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes") jbe@305: ELSE jbe@305: "positive_votes" * "policy_row"."direct_majority_den" >= jbe@305: "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes") jbe@305: END jbe@305: AND "positive_votes" >= "policy_row"."direct_majority_positive" jbe@305: AND "issue_row"."voter_count"-"negative_votes" >= jbe@305: "policy_row"."direct_majority_non_negative", jbe@305: "indirect_majority" = jbe@305: CASE WHEN "policy_row"."indirect_majority_strict" THEN jbe@305: "positive_votes" * "policy_row"."indirect_majority_den" > jbe@305: "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes") jbe@305: ELSE jbe@305: "positive_votes" * "policy_row"."indirect_majority_den" >= jbe@305: "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes") jbe@305: END jbe@305: AND "positive_votes" >= "policy_row"."indirect_majority_positive" jbe@305: AND "issue_row"."voter_count"-"negative_votes" >= jbe@305: "policy_row"."indirect_majority_non_negative", jbe@305: "schulze_rank" = "rank_ary"["i"], jbe@305: "better_than_status_quo" = "rank_ary"["i"] < "rank_ary"["dimension_v"], jbe@305: "worse_than_status_quo" = "rank_ary"["i"] > "rank_ary"["dimension_v"], jbe@305: "multistage_majority" = "rank_ary"["i"] >= "rank_ary"["dimension_v"], jbe@305: "reverse_beat_path" = "matrix"["dimension_v"]["i"] >= 0, jbe@305: "eligible" = FALSE, jbe@305: "winner" = FALSE, jbe@305: "rank" = NULL -- NOTE: in cases of manual reset of issue state jbe@305: WHERE "id" = "initiative_id_v"; jbe@305: "i" := "i" + 1; jbe@305: END LOOP; jbe@305: IF "i" != "dimension_v" THEN jbe@305: RAISE EXCEPTION 'Wrong winner count (should not happen)'; jbe@305: END IF; jbe@305: -- take indirect majorities into account: jbe@305: LOOP jbe@305: UPDATE "initiative" SET "indirect_majority" = TRUE jbe@305: FROM ( jbe@305: SELECT "new_initiative"."id" AS "initiative_id" jbe@305: FROM "initiative" "old_initiative" jbe@305: JOIN "initiative" "new_initiative" jbe@305: ON "new_initiative"."issue_id" = "issue_id_p" jbe@305: AND "new_initiative"."indirect_majority" = FALSE jbe@305: JOIN "battle" "battle_win" jbe@305: ON "battle_win"."issue_id" = "issue_id_p" jbe@305: AND "battle_win"."winning_initiative_id" = "new_initiative"."id" jbe@305: AND "battle_win"."losing_initiative_id" = "old_initiative"."id" jbe@305: JOIN "battle" "battle_lose" jbe@305: ON "battle_lose"."issue_id" = "issue_id_p" jbe@305: AND "battle_lose"."losing_initiative_id" = "new_initiative"."id" jbe@305: AND "battle_lose"."winning_initiative_id" = "old_initiative"."id" jbe@305: WHERE "old_initiative"."issue_id" = "issue_id_p" jbe@305: AND "old_initiative"."indirect_majority" = TRUE jbe@305: AND CASE WHEN "policy_row"."indirect_majority_strict" THEN jbe@305: "battle_win"."count" * "policy_row"."indirect_majority_den" > jbe@305: "policy_row"."indirect_majority_num" * jbe@305: ("battle_win"."count"+"battle_lose"."count") jbe@305: ELSE jbe@305: "battle_win"."count" * "policy_row"."indirect_majority_den" >= jbe@305: "policy_row"."indirect_majority_num" * jbe@305: ("battle_win"."count"+"battle_lose"."count") jbe@305: END jbe@305: AND "battle_win"."count" >= "policy_row"."indirect_majority_positive" jbe@305: AND "issue_row"."voter_count"-"battle_lose"."count" >= jbe@305: "policy_row"."indirect_majority_non_negative" jbe@305: ) AS "subquery" jbe@305: WHERE "id" = "subquery"."initiative_id"; jbe@305: EXIT WHEN NOT FOUND; jbe@305: END LOOP; jbe@305: -- set "multistage_majority" for remaining matching initiatives: jbe@305: UPDATE "initiative" SET "multistage_majority" = TRUE jbe@305: FROM ( jbe@305: SELECT "losing_initiative"."id" AS "initiative_id" jbe@305: FROM "initiative" "losing_initiative" jbe@305: JOIN "initiative" "winning_initiative" jbe@305: ON "winning_initiative"."issue_id" = "issue_id_p" jbe@305: AND "winning_initiative"."admitted" jbe@305: JOIN "battle" "battle_win" jbe@305: ON "battle_win"."issue_id" = "issue_id_p" jbe@305: AND "battle_win"."winning_initiative_id" = "winning_initiative"."id" jbe@305: AND "battle_win"."losing_initiative_id" = "losing_initiative"."id" jbe@305: JOIN "battle" "battle_lose" jbe@305: ON "battle_lose"."issue_id" = "issue_id_p" jbe@305: AND "battle_lose"."losing_initiative_id" = "winning_initiative"."id" jbe@305: AND "battle_lose"."winning_initiative_id" = "losing_initiative"."id" jbe@305: WHERE "losing_initiative"."issue_id" = "issue_id_p" jbe@305: AND "losing_initiative"."admitted" jbe@305: AND "winning_initiative"."schulze_rank" < jbe@305: "losing_initiative"."schulze_rank" jbe@305: AND "battle_win"."count" > "battle_lose"."count" jbe@305: AND ( jbe@305: "battle_win"."count" > "winning_initiative"."positive_votes" OR jbe@305: "battle_lose"."count" < "losing_initiative"."negative_votes" ) jbe@305: ) AS "subquery" jbe@305: WHERE "id" = "subquery"."initiative_id"; jbe@305: -- mark eligible initiatives: jbe@305: UPDATE "initiative" SET "eligible" = TRUE jbe@305: WHERE "issue_id" = "issue_id_p" jbe@305: AND "initiative"."direct_majority" jbe@305: AND "initiative"."indirect_majority" jbe@305: AND "initiative"."better_than_status_quo" jbe@305: AND ( jbe@305: "policy_row"."no_multistage_majority" = FALSE OR jbe@305: "initiative"."multistage_majority" = FALSE ) jbe@305: AND ( jbe@305: "policy_row"."no_reverse_beat_path" = FALSE OR jbe@305: "initiative"."reverse_beat_path" = FALSE ); jbe@305: -- mark final winner: jbe@305: UPDATE "initiative" SET "winner" = TRUE jbe@305: FROM ( jbe@305: SELECT "id" AS "initiative_id" jbe@305: FROM "initiative" jbe@305: WHERE "issue_id" = "issue_id_p" AND "eligible" jbe@305: ORDER BY jbe@305: "schulze_rank", jbe@305: "id" jbe@305: LIMIT 1 jbe@305: ) AS "subquery" jbe@305: WHERE "id" = "subquery"."initiative_id"; jbe@305: -- write (final) ranks: jbe@305: "rank_v" := 1; jbe@305: FOR "initiative_id_v" IN jbe@305: SELECT "id" jbe@305: FROM "initiative" jbe@305: WHERE "issue_id" = "issue_id_p" AND "admitted" jbe@305: ORDER BY jbe@305: "winner" DESC, jbe@305: "eligible" DESC, jbe@305: "schulze_rank", jbe@305: "id" jbe@305: LOOP jbe@305: UPDATE "initiative" SET "rank" = "rank_v" jbe@305: WHERE "id" = "initiative_id_v"; jbe@305: "rank_v" := "rank_v" + 1; jbe@305: END LOOP; jbe@305: -- set schulze rank of status quo and mark issue as finished: jbe@305: UPDATE "issue" SET jbe@305: "status_quo_schulze_rank" = "rank_ary"["dimension_v"], jbe@305: "state" = jbe@305: CASE WHEN EXISTS ( jbe@305: SELECT NULL FROM "initiative" jbe@305: WHERE "issue_id" = "issue_id_p" AND "winner" jbe@305: ) THEN jbe@305: 'finished_with_winner'::"issue_state" jbe@305: ELSE jbe@305: 'finished_without_winner'::"issue_state" jbe@305: END, jbe@305: "ranks_available" = TRUE jbe@305: WHERE "id" = "issue_id_p"; jbe@305: RETURN; jbe@305: END; jbe@305: $$; jbe@305: jbe@308: DROP FUNCTION IF EXISTS "vote_ratio" jbe@308: ( "initiative"."positive_votes"%TYPE, jbe@308: "initiative"."negative_votes"%TYPE ); jbe@308: jbe@305: COMMIT;