# HG changeset patch # User jbe # Date 1349430111 -7200 # Node ID a839e7efde9f8ff43a6ea71cf4d694bec84a1de1 # Parent e818f83e133bc545681b4bc06ce3c4f280de4672 Removed unwanted (and broken) tie-breaking by approval rate diff -r e818f83e133b -r a839e7efde9f core.sql --- a/core.sql Wed Jun 20 21:22:02 2012 +0200 +++ b/core.sql Fri Oct 05 11:41:51 2012 +0200 @@ -7,7 +7,7 @@ BEGIN; CREATE VIEW "liquid_feedback_version" AS - SELECT * FROM (VALUES ('2.0.11', 2, 0, 11)) + SELECT * FROM (VALUES ('2.0.12', 2, 0, 12)) AS "subquery"("string", "major", "minor", "revision"); @@ -2796,37 +2796,6 @@ ------------------------------- --- Comparison by vote count -- ------------------------------- - -CREATE FUNCTION "vote_ratio" - ( "positive_votes_p" "initiative"."positive_votes"%TYPE, - "negative_votes_p" "initiative"."negative_votes"%TYPE ) - RETURNS FLOAT8 - LANGUAGE 'plpgsql' STABLE AS $$ - BEGIN - IF "positive_votes_p" > 0 AND "negative_votes_p" > 0 THEN - RETURN - "positive_votes_p"::FLOAT8 / - ("positive_votes_p" + "negative_votes_p")::FLOAT8; - ELSIF "positive_votes_p" > 0 THEN - RETURN "positive_votes_p"; - ELSIF "negative_votes_p" > 0 THEN - RETURN 1 - "negative_votes_p"; - ELSE - RETURN 0.5; - END IF; - END; - $$; - -COMMENT ON FUNCTION "vote_ratio" - ( "initiative"."positive_votes"%TYPE, - "initiative"."negative_votes"%TYPE ) - IS 'Returns a number, which can be used for comparison of initiatives based on count of approvals and disapprovals. Greater numbers indicate a better result. This function is NOT injective.'; - - - ------------------------------------------------ -- Locking for snapshots and voting procedure -- ------------------------------------------------ @@ -4054,7 +4023,6 @@ WHERE "issue_id" = "issue_id_p" AND "eligible" ORDER BY "schulze_rank", - "vote_ratio"("positive_votes", "negative_votes"), "id" LIMIT 1 ) AS "subquery" @@ -4069,7 +4037,6 @@ "winner" DESC, "eligible" DESC, "schulze_rank", - "vote_ratio"("positive_votes", "negative_votes"), "id" LOOP UPDATE "initiative" SET "rank" = "rank_v" diff -r e818f83e133b -r a839e7efde9f update/core-update.v2.0.11-v2.0.12.sql --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/update/core-update.v2.0.11-v2.0.12.sql Fri Oct 05 11:41:51 2012 +0200 @@ -0,0 +1,309 @@ +BEGIN; + +CREATE OR REPLACE VIEW "liquid_feedback_version" AS + SELECT * FROM (VALUES ('2.0.12', 2, 0, 12)) + AS "subquery"("string", "major", "minor", "revision"); + +CREATE OR REPLACE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE) + RETURNS VOID + LANGUAGE 'plpgsql' VOLATILE AS $$ + DECLARE + "issue_row" "issue"%ROWTYPE; + "policy_row" "policy"%ROWTYPE; + "dimension_v" INTEGER; + "vote_matrix" INT4[][]; -- absolute votes + "matrix" INT8[][]; -- defeat strength / best paths + "i" INTEGER; + "j" INTEGER; + "k" INTEGER; + "battle_row" "battle"%ROWTYPE; + "rank_ary" INT4[]; + "rank_v" INT4; + "done_v" INTEGER; + "winners_ary" INTEGER[]; + "initiative_id_v" "initiative"."id"%TYPE; + BEGIN + SELECT * INTO "issue_row" + FROM "issue" WHERE "id" = "issue_id_p" + FOR UPDATE; + SELECT * INTO "policy_row" + FROM "policy" WHERE "id" = "issue_row"."policy_id"; + SELECT count(1) INTO "dimension_v" + FROM "battle_participant" WHERE "issue_id" = "issue_id_p"; + -- Create "vote_matrix" with absolute number of votes in pairwise + -- comparison: + "vote_matrix" := array_fill(NULL::INT4, ARRAY["dimension_v", "dimension_v"]); + "i" := 1; + "j" := 2; + FOR "battle_row" IN + SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p" + ORDER BY + "winning_initiative_id" NULLS LAST, + "losing_initiative_id" NULLS LAST + LOOP + "vote_matrix"["i"]["j"] := "battle_row"."count"; + IF "j" = "dimension_v" THEN + "i" := "i" + 1; + "j" := 1; + ELSE + "j" := "j" + 1; + IF "j" = "i" THEN + "j" := "j" + 1; + END IF; + END IF; + END LOOP; + IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN + RAISE EXCEPTION 'Wrong battle count (should not happen)'; + END IF; + -- Store defeat strengths in "matrix" using "defeat_strength" + -- function: + "matrix" := array_fill(NULL::INT8, ARRAY["dimension_v", "dimension_v"]); + "i" := 1; + LOOP + "j" := 1; + LOOP + IF "i" != "j" THEN + "matrix"["i"]["j"] := "defeat_strength"( + "vote_matrix"["i"]["j"], + "vote_matrix"["j"]["i"] + ); + END IF; + EXIT WHEN "j" = "dimension_v"; + "j" := "j" + 1; + END LOOP; + EXIT WHEN "i" = "dimension_v"; + "i" := "i" + 1; + END LOOP; + -- Find best paths: + "i" := 1; + LOOP + "j" := 1; + LOOP + IF "i" != "j" THEN + "k" := 1; + LOOP + IF "i" != "k" AND "j" != "k" THEN + IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN + IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN + "matrix"["j"]["k"] := "matrix"["j"]["i"]; + END IF; + ELSE + IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN + "matrix"["j"]["k"] := "matrix"["i"]["k"]; + END IF; + END IF; + END IF; + EXIT WHEN "k" = "dimension_v"; + "k" := "k" + 1; + END LOOP; + END IF; + EXIT WHEN "j" = "dimension_v"; + "j" := "j" + 1; + END LOOP; + EXIT WHEN "i" = "dimension_v"; + "i" := "i" + 1; + END LOOP; + -- Determine order of winners: + "rank_ary" := array_fill(NULL::INT4, ARRAY["dimension_v"]); + "rank_v" := 1; + "done_v" := 0; + LOOP + "winners_ary" := '{}'; + "i" := 1; + LOOP + IF "rank_ary"["i"] ISNULL THEN + "j" := 1; + LOOP + IF + "i" != "j" AND + "rank_ary"["j"] ISNULL AND + "matrix"["j"]["i"] > "matrix"["i"]["j"] + THEN + -- someone else is better + EXIT; + END IF; + IF "j" = "dimension_v" THEN + -- noone is better + "winners_ary" := "winners_ary" || "i"; + EXIT; + END IF; + "j" := "j" + 1; + END LOOP; + END IF; + EXIT WHEN "i" = "dimension_v"; + "i" := "i" + 1; + END LOOP; + "i" := 1; + LOOP + "rank_ary"["winners_ary"["i"]] := "rank_v"; + "done_v" := "done_v" + 1; + EXIT WHEN "i" = array_upper("winners_ary", 1); + "i" := "i" + 1; + END LOOP; + EXIT WHEN "done_v" = "dimension_v"; + "rank_v" := "rank_v" + 1; + END LOOP; + -- write preliminary results: + "i" := 1; + FOR "initiative_id_v" IN + SELECT "id" FROM "initiative" + WHERE "issue_id" = "issue_id_p" AND "admitted" + ORDER BY "id" + LOOP + UPDATE "initiative" SET + "direct_majority" = + CASE WHEN "policy_row"."direct_majority_strict" THEN + "positive_votes" * "policy_row"."direct_majority_den" > + "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes") + ELSE + "positive_votes" * "policy_row"."direct_majority_den" >= + "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes") + END + AND "positive_votes" >= "policy_row"."direct_majority_positive" + AND "issue_row"."voter_count"-"negative_votes" >= + "policy_row"."direct_majority_non_negative", + "indirect_majority" = + CASE WHEN "policy_row"."indirect_majority_strict" THEN + "positive_votes" * "policy_row"."indirect_majority_den" > + "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes") + ELSE + "positive_votes" * "policy_row"."indirect_majority_den" >= + "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes") + END + AND "positive_votes" >= "policy_row"."indirect_majority_positive" + AND "issue_row"."voter_count"-"negative_votes" >= + "policy_row"."indirect_majority_non_negative", + "schulze_rank" = "rank_ary"["i"], + "better_than_status_quo" = "rank_ary"["i"] < "rank_ary"["dimension_v"], + "worse_than_status_quo" = "rank_ary"["i"] > "rank_ary"["dimension_v"], + "multistage_majority" = "rank_ary"["i"] >= "rank_ary"["dimension_v"], + "reverse_beat_path" = "matrix"["dimension_v"]["i"] >= 0, + "eligible" = FALSE, + "winner" = FALSE, + "rank" = NULL -- NOTE: in cases of manual reset of issue state + WHERE "id" = "initiative_id_v"; + "i" := "i" + 1; + END LOOP; + IF "i" != "dimension_v" THEN + RAISE EXCEPTION 'Wrong winner count (should not happen)'; + END IF; + -- take indirect majorities into account: + LOOP + UPDATE "initiative" SET "indirect_majority" = TRUE + FROM ( + SELECT "new_initiative"."id" AS "initiative_id" + FROM "initiative" "old_initiative" + JOIN "initiative" "new_initiative" + ON "new_initiative"."issue_id" = "issue_id_p" + AND "new_initiative"."indirect_majority" = FALSE + JOIN "battle" "battle_win" + ON "battle_win"."issue_id" = "issue_id_p" + AND "battle_win"."winning_initiative_id" = "new_initiative"."id" + AND "battle_win"."losing_initiative_id" = "old_initiative"."id" + JOIN "battle" "battle_lose" + ON "battle_lose"."issue_id" = "issue_id_p" + AND "battle_lose"."losing_initiative_id" = "new_initiative"."id" + AND "battle_lose"."winning_initiative_id" = "old_initiative"."id" + WHERE "old_initiative"."issue_id" = "issue_id_p" + AND "old_initiative"."indirect_majority" = TRUE + AND CASE WHEN "policy_row"."indirect_majority_strict" THEN + "battle_win"."count" * "policy_row"."indirect_majority_den" > + "policy_row"."indirect_majority_num" * + ("battle_win"."count"+"battle_lose"."count") + ELSE + "battle_win"."count" * "policy_row"."indirect_majority_den" >= + "policy_row"."indirect_majority_num" * + ("battle_win"."count"+"battle_lose"."count") + END + AND "battle_win"."count" >= "policy_row"."indirect_majority_positive" + AND "issue_row"."voter_count"-"battle_lose"."count" >= + "policy_row"."indirect_majority_non_negative" + ) AS "subquery" + WHERE "id" = "subquery"."initiative_id"; + EXIT WHEN NOT FOUND; + END LOOP; + -- set "multistage_majority" for remaining matching initiatives: + UPDATE "initiative" SET "multistage_majority" = TRUE + FROM ( + SELECT "losing_initiative"."id" AS "initiative_id" + FROM "initiative" "losing_initiative" + JOIN "initiative" "winning_initiative" + ON "winning_initiative"."issue_id" = "issue_id_p" + AND "winning_initiative"."admitted" + JOIN "battle" "battle_win" + ON "battle_win"."issue_id" = "issue_id_p" + AND "battle_win"."winning_initiative_id" = "winning_initiative"."id" + AND "battle_win"."losing_initiative_id" = "losing_initiative"."id" + JOIN "battle" "battle_lose" + ON "battle_lose"."issue_id" = "issue_id_p" + AND "battle_lose"."losing_initiative_id" = "winning_initiative"."id" + AND "battle_lose"."winning_initiative_id" = "losing_initiative"."id" + WHERE "losing_initiative"."issue_id" = "issue_id_p" + AND "losing_initiative"."admitted" + AND "winning_initiative"."schulze_rank" < + "losing_initiative"."schulze_rank" + AND "battle_win"."count" > "battle_lose"."count" + AND ( + "battle_win"."count" > "winning_initiative"."positive_votes" OR + "battle_lose"."count" < "losing_initiative"."negative_votes" ) + ) AS "subquery" + WHERE "id" = "subquery"."initiative_id"; + -- mark eligible initiatives: + UPDATE "initiative" SET "eligible" = TRUE + WHERE "issue_id" = "issue_id_p" + AND "initiative"."direct_majority" + AND "initiative"."indirect_majority" + AND "initiative"."better_than_status_quo" + AND ( + "policy_row"."no_multistage_majority" = FALSE OR + "initiative"."multistage_majority" = FALSE ) + AND ( + "policy_row"."no_reverse_beat_path" = FALSE OR + "initiative"."reverse_beat_path" = FALSE ); + -- mark final winner: + UPDATE "initiative" SET "winner" = TRUE + FROM ( + SELECT "id" AS "initiative_id" + FROM "initiative" + WHERE "issue_id" = "issue_id_p" AND "eligible" + ORDER BY + "schulze_rank", + "id" + LIMIT 1 + ) AS "subquery" + WHERE "id" = "subquery"."initiative_id"; + -- write (final) ranks: + "rank_v" := 1; + FOR "initiative_id_v" IN + SELECT "id" + FROM "initiative" + WHERE "issue_id" = "issue_id_p" AND "admitted" + ORDER BY + "winner" DESC, + "eligible" DESC, + "schulze_rank", + "id" + LOOP + UPDATE "initiative" SET "rank" = "rank_v" + WHERE "id" = "initiative_id_v"; + "rank_v" := "rank_v" + 1; + END LOOP; + -- set schulze rank of status quo and mark issue as finished: + UPDATE "issue" SET + "status_quo_schulze_rank" = "rank_ary"["dimension_v"], + "state" = + CASE WHEN EXISTS ( + SELECT NULL FROM "initiative" + WHERE "issue_id" = "issue_id_p" AND "winner" + ) THEN + 'finished_with_winner'::"issue_state" + ELSE + 'finished_without_winner'::"issue_state" + END, + "ranks_available" = TRUE + WHERE "id" = "issue_id_p"; + RETURN; + END; + $$; + +COMMIT;