jbe@423: BEGIN;
jbe@423: 
jbe@423: CREATE OR REPLACE VIEW "liquid_feedback_version" AS
jbe@423:   SELECT * FROM (VALUES ('3.0.2', 3, 0, 2))
jbe@423:   AS "subquery"("string", "major", "minor", "revision");
jbe@423: 
jbe@430: 
jbe@430: CREATE TYPE "defeat_strength" AS ENUM ('simple', 'tuple');
jbe@430: 
jbe@430: COMMENT ON TYPE "defeat_strength" IS 'How pairwise defeats are measured for the Schulze method: ''simple'' = only the number of winning votes, ''tuple'' = primarily the number of winning votes, secondarily the number of losing votes';
jbe@430: 
jbe@430: 
jbe@430: CREATE TYPE "tie_breaking" AS ENUM ('simple', 'variant1', 'variant2');
jbe@430: 
jbe@430: COMMENT ON TYPE "tie_breaking" IS 'Tie-breaker for the Schulze method: ''simple'' = only initiative ids are used, ''variant1'' = use initiative ids in variant 1 for tie breaking of the links (TBRL) and sequentially forbid shared links, ''variant2'' = use initiative ids in variant 2 for tie breaking of the links (TBRL) and sequentially forbid shared links';
jbe@430: 
jbe@430: 
jbe@430: ALTER TABLE "policy" ADD COLUMN "defeat_strength" "defeat_strength" NOT NULL DEFAULT 'tuple';
jbe@430: ALTER TABLE "policy" ADD COLUMN "tie_breaking"    "tie_breaking"    NOT NULL DEFAULT 'variant1';
jbe@430: 
jbe@430: ALTER TABLE "policy" ADD
jbe@430:   CONSTRAINT "no_reverse_beat_path_requires_tuple_defeat_strength" CHECK (
jbe@430:     ("defeat_strength" = 'tuple'::"defeat_strength" OR "no_reverse_beat_path" = FALSE)
jbe@430:   );
jbe@430: 
jbe@430: COMMENT ON COLUMN "policy"."defeat_strength"       IS 'How pairwise defeats are measured for the Schulze method; see type "defeat_strength"; ''tuple'' is the recommended setting';
jbe@430: COMMENT ON COLUMN "policy"."tie_breaking"          IS 'Tie-breaker for the Schulze method; see type "tie_breaking"; ''variant1'' or ''variant2'' are recommended';
jbe@430: COMMENT ON COLUMN "initiative"."reverse_beat_path"      IS 'TRUE, if there is a beat path (may include ties) from this initiative to the status quo; set to NULL if "policy"."defeat_strength" is set to ''simple''';
jbe@430:  
jbe@430: 
jbe@433: CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
jbe@433:   RETURNS VOID
jbe@433:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@433:     DECLARE
jbe@433:       "area_id_v"   "area"."id"%TYPE;
jbe@433:       "unit_id_v"   "unit"."id"%TYPE;
jbe@433:       "member_id_v" "member"."id"%TYPE;
jbe@433:     BEGIN
jbe@433:       PERFORM "require_transaction_isolation"();
jbe@433:       SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";
jbe@433:       SELECT "unit_id" INTO "unit_id_v" FROM "area"  WHERE "id" = "area_id_v";
jbe@433:       -- override protection triggers:
jbe@433:       INSERT INTO "temporary_transaction_data" ("key", "value")
jbe@433:         VALUES ('override_protection_triggers', TRUE::TEXT);
jbe@433:       -- delete timestamp of voting comment:
jbe@433:       UPDATE "direct_voter" SET "comment_changed" = NULL
jbe@433:         WHERE "issue_id" = "issue_id_p";
jbe@433:       -- delete delegating votes (in cases of manual reset of issue state):
jbe@433:       DELETE FROM "delegating_voter"
jbe@433:         WHERE "issue_id" = "issue_id_p";
jbe@433:       -- delete votes from non-privileged voters:
jbe@433:       DELETE FROM "direct_voter"
jbe@433:         USING (
jbe@433:           SELECT
jbe@433:             "direct_voter"."member_id"
jbe@433:           FROM "direct_voter"
jbe@433:           JOIN "member" ON "direct_voter"."member_id" = "member"."id"
jbe@433:           LEFT JOIN "privilege"
jbe@433:           ON "privilege"."unit_id" = "unit_id_v"
jbe@433:           AND "privilege"."member_id" = "direct_voter"."member_id"
jbe@433:           WHERE "direct_voter"."issue_id" = "issue_id_p" AND (
jbe@433:             "member"."active" = FALSE OR
jbe@433:             "privilege"."voting_right" ISNULL OR
jbe@433:             "privilege"."voting_right" = FALSE
jbe@433:           )
jbe@433:         ) AS "subquery"
jbe@433:         WHERE "direct_voter"."issue_id" = "issue_id_p"
jbe@433:         AND "direct_voter"."member_id" = "subquery"."member_id";
jbe@433:       -- consider delegations:
jbe@433:       UPDATE "direct_voter" SET "weight" = 1
jbe@433:         WHERE "issue_id" = "issue_id_p";
jbe@433:       PERFORM "add_vote_delegations"("issue_id_p");
jbe@433:       -- mark first preferences:
jbe@433:       UPDATE "vote" SET "first_preference" = "subquery"."first_preference"
jbe@433:         FROM (
jbe@433:           SELECT
jbe@433:             "vote"."initiative_id",
jbe@433:             "vote"."member_id",
jbe@433:             CASE WHEN "vote"."grade" > 0 THEN
jbe@433:               CASE WHEN "vote"."grade" = max("agg"."grade") THEN TRUE ELSE FALSE END
jbe@433:             ELSE NULL
jbe@433:             END AS "first_preference"
jbe@433:           FROM "vote"
jbe@433:           JOIN "initiative"  -- NOTE: due to missing index on issue_id
jbe@433:           ON "vote"."issue_id" = "initiative"."issue_id"
jbe@433:           JOIN "vote" AS "agg"
jbe@433:           ON "initiative"."id" = "agg"."initiative_id"
jbe@433:           AND "vote"."member_id" = "agg"."member_id"
jbe@433:           GROUP BY "vote"."initiative_id", "vote"."member_id", "vote"."grade"
jbe@433:         ) AS "subquery"
jbe@433:         WHERE "vote"."issue_id" = "issue_id_p"
jbe@433:         AND "vote"."initiative_id" = "subquery"."initiative_id"
jbe@433:         AND "vote"."member_id" = "subquery"."member_id";
jbe@433:       -- finish overriding protection triggers (avoids garbage):
jbe@433:       DELETE FROM "temporary_transaction_data"
jbe@433:         WHERE "key" = 'override_protection_triggers';
jbe@433:       -- materialize battle_view:
jbe@433:       -- NOTE: "closed" column of issue must be set at this point
jbe@433:       DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
jbe@433:       INSERT INTO "battle" (
jbe@433:         "issue_id",
jbe@433:         "winning_initiative_id", "losing_initiative_id",
jbe@433:         "count"
jbe@433:       ) SELECT
jbe@433:         "issue_id",
jbe@433:         "winning_initiative_id", "losing_initiative_id",
jbe@433:         "count"
jbe@433:         FROM "battle_view" WHERE "issue_id" = "issue_id_p";
jbe@433:       -- set voter count:
jbe@433:       UPDATE "issue" SET
jbe@433:         "voter_count" = (
jbe@433:           SELECT coalesce(sum("weight"), 0)
jbe@433:           FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
jbe@433:         )
jbe@433:         WHERE "id" = "issue_id_p";
jbe@433:       -- calculate "first_preference_votes":
jbe@433:       UPDATE "initiative"
jbe@433:         SET "first_preference_votes" = coalesce("subquery"."sum", 0)
jbe@433:         FROM (
jbe@433:           SELECT "vote"."initiative_id", sum("direct_voter"."weight")
jbe@433:           FROM "vote" JOIN "direct_voter"
jbe@433:           ON "vote"."issue_id" = "direct_voter"."issue_id"
jbe@433:           AND "vote"."member_id" = "direct_voter"."member_id"
jbe@433:           WHERE "vote"."first_preference"
jbe@433:           GROUP BY "vote"."initiative_id"
jbe@433:         ) AS "subquery"
jbe@433:         WHERE "initiative"."issue_id" = "issue_id_p"
jbe@433:         AND "initiative"."admitted"
jbe@433:         AND "initiative"."id" = "subquery"."initiative_id";
jbe@433:       -- copy "positive_votes" and "negative_votes" from "battle" table:
jbe@433:       UPDATE "initiative" SET
jbe@433:         "positive_votes" = "battle_win"."count",
jbe@433:         "negative_votes" = "battle_lose"."count"
jbe@433:         FROM "battle" AS "battle_win", "battle" AS "battle_lose"
jbe@433:         WHERE
jbe@433:           "battle_win"."issue_id" = "issue_id_p" AND
jbe@433:           "battle_win"."winning_initiative_id" = "initiative"."id" AND
jbe@433:           "battle_win"."losing_initiative_id" ISNULL AND
jbe@433:           "battle_lose"."issue_id" = "issue_id_p" AND
jbe@433:           "battle_lose"."losing_initiative_id" = "initiative"."id" AND
jbe@433:           "battle_lose"."winning_initiative_id" ISNULL;
jbe@433:     END;
jbe@433:   $$;
jbe@433: 
jbe@433: 
jbe@430: DROP FUNCTION "calculate_ranks"("issue"."id"%TYPE);
jbe@430: DROP FUNCTION "defeat_strength"(INT4, INT4);
jbe@430: 
jbe@430: 
jbe@430: CREATE FUNCTION "defeat_strength"
jbe@430:   ( "positive_votes_p"  INT4,
jbe@430:     "negative_votes_p"  INT4,
jbe@430:     "defeat_strength_p" "defeat_strength" )
jbe@430:   RETURNS INT8
jbe@430:   LANGUAGE 'plpgsql' IMMUTABLE AS $$
jbe@430:     BEGIN
jbe@430:       IF "defeat_strength_p" = 'simple'::"defeat_strength" THEN
jbe@430:         IF "positive_votes_p" > "negative_votes_p" THEN
jbe@430:           RETURN "positive_votes_p";
jbe@430:         ELSE
jbe@430:           RETURN 0;
jbe@430:         END IF;
jbe@430:       ELSE
jbe@430:         IF "positive_votes_p" > "negative_votes_p" THEN
jbe@430:           RETURN ("positive_votes_p"::INT8 << 31) - "negative_votes_p"::INT8;
jbe@430:         ELSIF "positive_votes_p" = "negative_votes_p" THEN
jbe@430:           RETURN 0;
jbe@430:         ELSE
jbe@430:           RETURN -1;
jbe@430:         END IF;
jbe@430:       END IF;
jbe@430:     END;
jbe@430:   $$;
jbe@430: 
jbe@430: COMMENT ON FUNCTION "defeat_strength"(INT4, INT4, "defeat_strength") IS 'Calculates defeat strength (INT8!) according to the "defeat_strength" option (see comment on type "defeat_strength")';
jbe@430: 
jbe@430: 
jbe@430: CREATE FUNCTION "secondary_link_strength"
jbe@430:   ( "initiative1_ord_p" INT4,
jbe@430:     "initiative2_ord_p" INT4,
jbe@430:     "tie_breaking_p"   "tie_breaking" )
jbe@430:   RETURNS INT8
jbe@430:   LANGUAGE 'plpgsql' IMMUTABLE AS $$
jbe@430:     BEGIN
jbe@430:       IF "initiative1_ord_p" = "initiative2_ord_p" THEN
jbe@430:         RAISE EXCEPTION 'Identical initiative ids passed to "secondary_link_strength" function (should not happen)';
jbe@430:       END IF;
jbe@430:       RETURN (
jbe@430:         CASE WHEN "tie_breaking_p" = 'simple'::"tie_breaking" THEN
jbe@430:           0
jbe@430:         ELSE
jbe@430:           CASE WHEN "initiative1_ord_p" < "initiative2_ord_p" THEN
jbe@430:             1::INT8 << 62
jbe@430:           ELSE 0 END
jbe@430:           +
jbe@430:           CASE WHEN "tie_breaking_p" = 'variant2'::"tie_breaking" THEN
jbe@430:             ("initiative2_ord_p"::INT8 << 31) - "initiative1_ord_p"::INT8
jbe@430:           ELSE
jbe@430:             "initiative2_ord_p"::INT8 - ("initiative1_ord_p"::INT8 << 31)
jbe@430:           END
jbe@430:         END
jbe@430:       );
jbe@430:     END;
jbe@430:   $$;
jbe@430: 
jbe@430: COMMENT ON FUNCTION "secondary_link_strength"(INT4, INT4, "tie_breaking") IS 'Calculates a secondary criterion for the defeat strength (tie-breaking of the links)';
jbe@430: 
jbe@430: 
jbe@430: CREATE TYPE "link_strength" AS (
jbe@430:         "primary"               INT8,
jbe@430:         "secondary"             INT8 );
jbe@430: 
jbe@430: COMMENT ON TYPE "link_strength" IS 'Type to store the defeat strength of a link between two candidates plus a secondary criterion to create unique link strengths between the candidates (needed for tie-breaking ''variant1'' and ''variant2'')';
jbe@430: 
jbe@430: 
jbe@430: CREATE FUNCTION "find_best_paths"("matrix_d" "link_strength"[][])
jbe@430:   RETURNS "link_strength"[][]
jbe@430:   LANGUAGE 'plpgsql' IMMUTABLE AS $$
jbe@430:     DECLARE
jbe@430:       "dimension_v" INT4;
jbe@430:       "matrix_p"    "link_strength"[][];
jbe@430:       "i"           INT4;
jbe@430:       "j"           INT4;
jbe@430:       "k"           INT4;
jbe@430:     BEGIN
jbe@430:       "dimension_v" := array_upper("matrix_d", 1);
jbe@430:       "matrix_p" := "matrix_d";
jbe@430:       "i" := 1;
jbe@430:       LOOP
jbe@430:         "j" := 1;
jbe@430:         LOOP
jbe@430:           IF "i" != "j" THEN
jbe@430:             "k" := 1;
jbe@430:             LOOP
jbe@430:               IF "i" != "k" AND "j" != "k" THEN
jbe@430:                 IF "matrix_p"["j"]["i"] < "matrix_p"["i"]["k"] THEN
jbe@430:                   IF "matrix_p"["j"]["i"] > "matrix_p"["j"]["k"] THEN
jbe@430:                     "matrix_p"["j"]["k"] := "matrix_p"["j"]["i"];
jbe@430:                   END IF;
jbe@430:                 ELSE
jbe@430:                   IF "matrix_p"["i"]["k"] > "matrix_p"["j"]["k"] THEN
jbe@430:                     "matrix_p"["j"]["k"] := "matrix_p"["i"]["k"];
jbe@430:                   END IF;
jbe@430:                 END IF;
jbe@430:               END IF;
jbe@430:               EXIT WHEN "k" = "dimension_v";
jbe@430:               "k" := "k" + 1;
jbe@430:             END LOOP;
jbe@430:           END IF;
jbe@430:           EXIT WHEN "j" = "dimension_v";
jbe@430:           "j" := "j" + 1;
jbe@430:         END LOOP;
jbe@430:         EXIT WHEN "i" = "dimension_v";
jbe@430:         "i" := "i" + 1;
jbe@430:       END LOOP;
jbe@430:       RETURN "matrix_p";
jbe@430:     END;
jbe@430:   $$;
jbe@430: 
jbe@430: COMMENT ON FUNCTION "find_best_paths"("link_strength"[][]) IS 'Computes the strengths of the best beat-paths from a square matrix';
jbe@430:  
jbe@430: 
jbe@430: CREATE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE)
jbe@430:   RETURNS VOID
jbe@430:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@430:     DECLARE
jbe@430:       "issue_row"       "issue"%ROWTYPE;
jbe@430:       "policy_row"      "policy"%ROWTYPE;
jbe@430:       "dimension_v"     INT4;
jbe@430:       "matrix_a"        INT4[][];  -- absolute votes
jbe@430:       "matrix_d"        "link_strength"[][];  -- defeat strength (direct)
jbe@430:       "matrix_p"        "link_strength"[][];  -- defeat strength (best path)
jbe@430:       "matrix_t"        "link_strength"[][];  -- defeat strength (tie-breaking)
jbe@430:       "matrix_f"        BOOLEAN[][];  -- forbidden link (tie-breaking)
jbe@430:       "matrix_b"        BOOLEAN[][];  -- final order (who beats who)
jbe@430:       "i"               INT4;
jbe@430:       "j"               INT4;
jbe@430:       "m"               INT4;
jbe@430:       "n"               INT4;
jbe@430:       "battle_row"      "battle"%ROWTYPE;
jbe@430:       "rank_ary"        INT4[];
jbe@430:       "rank_v"          INT4;
jbe@430:       "initiative_id_v" "initiative"."id"%TYPE;
jbe@430:     BEGIN
jbe@430:       PERFORM "require_transaction_isolation"();
jbe@430:       SELECT * INTO "issue_row"
jbe@430:         FROM "issue" WHERE "id" = "issue_id_p";
jbe@430:       SELECT * INTO "policy_row"
jbe@430:         FROM "policy" WHERE "id" = "issue_row"."policy_id";
jbe@430:       SELECT count(1) INTO "dimension_v"
jbe@430:         FROM "battle_participant" WHERE "issue_id" = "issue_id_p";
jbe@430:       -- create "matrix_a" with absolute number of votes in pairwise
jbe@430:       -- comparison:
jbe@430:       "matrix_a" := array_fill(NULL::INT4, ARRAY["dimension_v", "dimension_v"]);
jbe@430:       "i" := 1;
jbe@430:       "j" := 2;
jbe@430:       FOR "battle_row" IN
jbe@430:         SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p"
jbe@430:         ORDER BY
jbe@430:         "winning_initiative_id" NULLS FIRST,
jbe@430:         "losing_initiative_id" NULLS FIRST
jbe@430:       LOOP
jbe@430:         "matrix_a"["i"]["j"] := "battle_row"."count";
jbe@430:         IF "j" = "dimension_v" THEN
jbe@430:           "i" := "i" + 1;
jbe@430:           "j" := 1;
jbe@430:         ELSE
jbe@430:           "j" := "j" + 1;
jbe@430:           IF "j" = "i" THEN
jbe@430:             "j" := "j" + 1;
jbe@430:           END IF;
jbe@430:         END IF;
jbe@430:       END LOOP;
jbe@430:       IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN
jbe@430:         RAISE EXCEPTION 'Wrong battle count (should not happen)';
jbe@430:       END IF;
jbe@430:       -- store direct defeat strengths in "matrix_d" using "defeat_strength"
jbe@430:       -- and "secondary_link_strength" functions:
jbe@430:       "matrix_d" := array_fill(NULL::INT8, ARRAY["dimension_v", "dimension_v"]);
jbe@430:       "i" := 1;
jbe@430:       LOOP
jbe@430:         "j" := 1;
jbe@430:         LOOP
jbe@430:           IF "i" != "j" THEN
jbe@430:             "matrix_d"["i"]["j"] := (
jbe@430:               "defeat_strength"(
jbe@430:                 "matrix_a"["i"]["j"],
jbe@430:                 "matrix_a"["j"]["i"],
jbe@430:                 "policy_row"."defeat_strength"
jbe@430:               ),
jbe@430:               "secondary_link_strength"(
jbe@430:                 "i",
jbe@430:                 "j",
jbe@430:                 "policy_row"."tie_breaking"
jbe@430:               )
jbe@430:             )::"link_strength";
jbe@430:           END IF;
jbe@430:           EXIT WHEN "j" = "dimension_v";
jbe@430:           "j" := "j" + 1;
jbe@430:         END LOOP;
jbe@430:         EXIT WHEN "i" = "dimension_v";
jbe@430:         "i" := "i" + 1;
jbe@430:       END LOOP;
jbe@430:       -- find best paths:
jbe@430:       "matrix_p" := "find_best_paths"("matrix_d");
jbe@430:       -- create partial order:
jbe@430:       "matrix_b" := array_fill(NULL::BOOLEAN, ARRAY["dimension_v", "dimension_v"]);
jbe@430:       "i" := 1;
jbe@430:       LOOP
jbe@430:         "j" := "i" + 1;
jbe@430:         LOOP
jbe@430:           IF "i" != "j" THEN
jbe@430:             IF "matrix_p"["i"]["j"] > "matrix_p"["j"]["i"] THEN
jbe@430:               "matrix_b"["i"]["j"] := TRUE;
jbe@430:               "matrix_b"["j"]["i"] := FALSE;
jbe@430:             ELSIF "matrix_p"["i"]["j"] < "matrix_p"["j"]["i"] THEN
jbe@430:               "matrix_b"["i"]["j"] := FALSE;
jbe@430:               "matrix_b"["j"]["i"] := TRUE;
jbe@430:             END IF;
jbe@430:           END IF;
jbe@430:           EXIT WHEN "j" = "dimension_v";
jbe@430:           "j" := "j" + 1;
jbe@430:         END LOOP;
jbe@430:         EXIT WHEN "i" = "dimension_v" - 1;
jbe@430:         "i" := "i" + 1;
jbe@430:       END LOOP;
jbe@430:       -- tie-breaking by forbidding shared weakest links in beat-paths
jbe@430:       -- (unless "tie_breaking" is set to 'simple', in which case tie-breaking
jbe@430:       -- is performed later by initiative id):
jbe@430:       IF "policy_row"."tie_breaking" != 'simple'::"tie_breaking" THEN
jbe@430:         "m" := 1;
jbe@430:         LOOP
jbe@430:           "n" := "m" + 1;
jbe@430:           LOOP
jbe@430:             -- only process those candidates m and n, which are tied:
jbe@430:             IF "matrix_b"["m"]["n"] ISNULL THEN
jbe@430:               -- start with beat-paths prior tie-breaking:
jbe@430:               "matrix_t" := "matrix_p";
jbe@430:               -- start with all links allowed:
jbe@430:               "matrix_f" := array_fill(FALSE, ARRAY["dimension_v", "dimension_v"]);
jbe@430:               LOOP
jbe@430:                 -- determine (and forbid) that link that is the weakest link
jbe@430:                 -- in both the best path from candidate m to candidate n and
jbe@430:                 -- from candidate n to candidate m:
jbe@430:                 "i" := 1;
jbe@430:                 <<forbid_one_link>>
jbe@430:                 LOOP
jbe@430:                   "j" := 1;
jbe@430:                   LOOP
jbe@430:                     IF "i" != "j" THEN
jbe@430:                       IF "matrix_d"["i"]["j"] = "matrix_t"["m"]["n"] THEN
jbe@430:                         "matrix_f"["i"]["j"] := TRUE;
jbe@430:                         -- exit for performance reasons,
jbe@430:                         -- as exactly one link will be found:
jbe@430:                         EXIT forbid_one_link;
jbe@430:                       END IF;
jbe@430:                     END IF;
jbe@430:                     EXIT WHEN "j" = "dimension_v";
jbe@430:                     "j" := "j" + 1;
jbe@430:                   END LOOP;
jbe@430:                   IF "i" = "dimension_v" THEN
jbe@430:                     RAISE EXCEPTION 'Did not find shared weakest link for tie-breaking (should not happen)';
jbe@430:                   END IF;
jbe@430:                   "i" := "i" + 1;
jbe@430:                 END LOOP;
jbe@430:                 -- calculate best beat-paths while ignoring forbidden links:
jbe@430:                 "i" := 1;
jbe@430:                 LOOP
jbe@430:                   "j" := 1;
jbe@430:                   LOOP
jbe@430:                     IF "i" != "j" THEN
jbe@430:                       "matrix_t"["i"]["j"] := CASE
jbe@430:                          WHEN "matrix_f"["i"]["j"]
jbe@432:                          THEN ((-1::INT8) << 63, 0)::"link_strength"  -- worst possible value
jbe@430:                          ELSE "matrix_d"["i"]["j"] END;
jbe@430:                     END IF;
jbe@430:                     EXIT WHEN "j" = "dimension_v";
jbe@430:                     "j" := "j" + 1;
jbe@430:                   END LOOP;
jbe@430:                   EXIT WHEN "i" = "dimension_v";
jbe@430:                   "i" := "i" + 1;
jbe@430:                 END LOOP;
jbe@430:                 "matrix_t" := "find_best_paths"("matrix_t");
jbe@430:                 -- extend partial order, if tie-breaking was successful:
jbe@430:                 IF "matrix_t"["m"]["n"] > "matrix_t"["n"]["m"] THEN
jbe@430:                   "matrix_b"["m"]["n"] := TRUE;
jbe@430:                   "matrix_b"["n"]["m"] := FALSE;
jbe@430:                   EXIT;
jbe@430:                 ELSIF "matrix_t"["m"]["n"] < "matrix_t"["n"]["m"] THEN
jbe@430:                   "matrix_b"["m"]["n"] := FALSE;
jbe@430:                   "matrix_b"["n"]["m"] := TRUE;
jbe@430:                   EXIT;
jbe@430:                 END IF;
jbe@430:               END LOOP;
jbe@430:             END IF;
jbe@430:             EXIT WHEN "n" = "dimension_v";
jbe@430:             "n" := "n" + 1;
jbe@430:           END LOOP;
jbe@430:           EXIT WHEN "m" = "dimension_v" - 1;
jbe@430:           "m" := "m" + 1;
jbe@430:         END LOOP;
jbe@430:       END IF;
jbe@430:       -- store a unique ranking in "rank_ary":
jbe@430:       "rank_ary" := array_fill(NULL::INT4, ARRAY["dimension_v"]);
jbe@430:       "rank_v" := 1;
jbe@430:       LOOP
jbe@430:         "i" := 1;
jbe@430:         <<assign_next_rank>>
jbe@430:         LOOP
jbe@430:           IF "rank_ary"["i"] ISNULL THEN
jbe@430:             "j" := 1;
jbe@430:             LOOP
jbe@430:               IF
jbe@430:                 "i" != "j" AND
jbe@430:                 "rank_ary"["j"] ISNULL AND
jbe@430:                 ( "matrix_b"["j"]["i"] OR
jbe@430:                   -- tie-breaking by "id"
jbe@430:                   ( "matrix_b"["j"]["i"] ISNULL AND
jbe@430:                     "j" < "i" ) )
jbe@430:               THEN
jbe@430:                 -- someone else is better
jbe@430:                 EXIT;
jbe@430:               END IF;
jbe@430:               IF "j" = "dimension_v" THEN
jbe@430:                 -- noone is better
jbe@430:                 "rank_ary"["i"] := "rank_v";
jbe@430:                 EXIT assign_next_rank;
jbe@430:               END IF;
jbe@430:               "j" := "j" + 1;
jbe@430:             END LOOP;
jbe@430:           END IF;
jbe@430:           "i" := "i" + 1;
jbe@430:           IF "i" > "dimension_v" THEN
jbe@430:             RAISE EXCEPTION 'Schulze ranking does not compute (should not happen)';
jbe@430:           END IF;
jbe@430:         END LOOP;
jbe@430:         EXIT WHEN "rank_v" = "dimension_v";
jbe@430:         "rank_v" := "rank_v" + 1;
jbe@430:       END LOOP;
jbe@430:       -- write preliminary results:
jbe@430:       "i" := 2;  -- omit status quo with "i" = 1
jbe@430:       FOR "initiative_id_v" IN
jbe@430:         SELECT "id" FROM "initiative"
jbe@430:         WHERE "issue_id" = "issue_id_p" AND "admitted"
jbe@430:         ORDER BY "id"
jbe@430:       LOOP
jbe@430:         UPDATE "initiative" SET
jbe@430:           "direct_majority" =
jbe@430:             CASE WHEN "policy_row"."direct_majority_strict" THEN
jbe@430:               "positive_votes" * "policy_row"."direct_majority_den" >
jbe@430:               "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")
jbe@430:             ELSE
jbe@430:               "positive_votes" * "policy_row"."direct_majority_den" >=
jbe@430:               "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")
jbe@430:             END
jbe@430:             AND "positive_votes" >= "policy_row"."direct_majority_positive"
jbe@430:             AND "issue_row"."voter_count"-"negative_votes" >=
jbe@430:                 "policy_row"."direct_majority_non_negative",
jbe@430:             "indirect_majority" =
jbe@430:             CASE WHEN "policy_row"."indirect_majority_strict" THEN
jbe@430:               "positive_votes" * "policy_row"."indirect_majority_den" >
jbe@430:               "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")
jbe@430:             ELSE
jbe@430:               "positive_votes" * "policy_row"."indirect_majority_den" >=
jbe@430:               "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")
jbe@430:             END
jbe@430:             AND "positive_votes" >= "policy_row"."indirect_majority_positive"
jbe@430:             AND "issue_row"."voter_count"-"negative_votes" >=
jbe@430:                 "policy_row"."indirect_majority_non_negative",
jbe@430:           "schulze_rank"           = "rank_ary"["i"],
jbe@430:           "better_than_status_quo" = "rank_ary"["i"] < "rank_ary"[1],
jbe@430:           "worse_than_status_quo"  = "rank_ary"["i"] > "rank_ary"[1],
jbe@430:           "multistage_majority"    = "rank_ary"["i"] >= "rank_ary"[1],
jbe@430:           "reverse_beat_path"      = CASE WHEN "policy_row"."defeat_strength" = 'simple'::"defeat_strength"
jbe@430:                                      THEN NULL
jbe@430:                                      ELSE "matrix_p"[1]["i"]."primary" >= 0 END,
jbe@430:           "eligible"               = FALSE,
jbe@430:           "winner"                 = FALSE,
jbe@430:           "rank"                   = NULL  -- NOTE: in cases of manual reset of issue state
jbe@430:           WHERE "id" = "initiative_id_v";
jbe@430:         "i" := "i" + 1;
jbe@430:       END LOOP;
jbe@430:       IF "i" != "dimension_v" + 1 THEN
jbe@430:         RAISE EXCEPTION 'Wrong winner count (should not happen)';
jbe@430:       END IF;
jbe@430:       -- take indirect majorities into account:
jbe@430:       LOOP
jbe@430:         UPDATE "initiative" SET "indirect_majority" = TRUE
jbe@430:           FROM (
jbe@430:             SELECT "new_initiative"."id" AS "initiative_id"
jbe@430:             FROM "initiative" "old_initiative"
jbe@430:             JOIN "initiative" "new_initiative"
jbe@430:               ON "new_initiative"."issue_id" = "issue_id_p"
jbe@430:               AND "new_initiative"."indirect_majority" = FALSE
jbe@430:             JOIN "battle" "battle_win"
jbe@430:               ON "battle_win"."issue_id" = "issue_id_p"
jbe@430:               AND "battle_win"."winning_initiative_id" = "new_initiative"."id"
jbe@430:               AND "battle_win"."losing_initiative_id" = "old_initiative"."id"
jbe@430:             JOIN "battle" "battle_lose"
jbe@430:               ON "battle_lose"."issue_id" = "issue_id_p"
jbe@430:               AND "battle_lose"."losing_initiative_id" = "new_initiative"."id"
jbe@430:               AND "battle_lose"."winning_initiative_id" = "old_initiative"."id"
jbe@430:             WHERE "old_initiative"."issue_id" = "issue_id_p"
jbe@430:             AND "old_initiative"."indirect_majority" = TRUE
jbe@430:             AND CASE WHEN "policy_row"."indirect_majority_strict" THEN
jbe@430:               "battle_win"."count" * "policy_row"."indirect_majority_den" >
jbe@430:               "policy_row"."indirect_majority_num" *
jbe@430:               ("battle_win"."count"+"battle_lose"."count")
jbe@430:             ELSE
jbe@430:               "battle_win"."count" * "policy_row"."indirect_majority_den" >=
jbe@430:               "policy_row"."indirect_majority_num" *
jbe@430:               ("battle_win"."count"+"battle_lose"."count")
jbe@430:             END
jbe@430:             AND "battle_win"."count" >= "policy_row"."indirect_majority_positive"
jbe@430:             AND "issue_row"."voter_count"-"battle_lose"."count" >=
jbe@430:                 "policy_row"."indirect_majority_non_negative"
jbe@430:           ) AS "subquery"
jbe@430:           WHERE "id" = "subquery"."initiative_id";
jbe@430:         EXIT WHEN NOT FOUND;
jbe@430:       END LOOP;
jbe@430:       -- set "multistage_majority" for remaining matching initiatives:
jbe@430:       UPDATE "initiative" SET "multistage_majority" = TRUE
jbe@430:         FROM (
jbe@430:           SELECT "losing_initiative"."id" AS "initiative_id"
jbe@430:           FROM "initiative" "losing_initiative"
jbe@430:           JOIN "initiative" "winning_initiative"
jbe@430:             ON "winning_initiative"."issue_id" = "issue_id_p"
jbe@430:             AND "winning_initiative"."admitted"
jbe@430:           JOIN "battle" "battle_win"
jbe@430:             ON "battle_win"."issue_id" = "issue_id_p"
jbe@430:             AND "battle_win"."winning_initiative_id" = "winning_initiative"."id"
jbe@430:             AND "battle_win"."losing_initiative_id" = "losing_initiative"."id"
jbe@430:           JOIN "battle" "battle_lose"
jbe@430:             ON "battle_lose"."issue_id" = "issue_id_p"
jbe@430:             AND "battle_lose"."losing_initiative_id" = "winning_initiative"."id"
jbe@430:             AND "battle_lose"."winning_initiative_id" = "losing_initiative"."id"
jbe@430:           WHERE "losing_initiative"."issue_id" = "issue_id_p"
jbe@430:           AND "losing_initiative"."admitted"
jbe@430:           AND "winning_initiative"."schulze_rank" <
jbe@430:               "losing_initiative"."schulze_rank"
jbe@430:           AND "battle_win"."count" > "battle_lose"."count"
jbe@430:           AND (
jbe@430:             "battle_win"."count" > "winning_initiative"."positive_votes" OR
jbe@430:             "battle_lose"."count" < "losing_initiative"."negative_votes" )
jbe@430:         ) AS "subquery"
jbe@430:         WHERE "id" = "subquery"."initiative_id";
jbe@430:       -- mark eligible initiatives:
jbe@430:       UPDATE "initiative" SET "eligible" = TRUE
jbe@430:         WHERE "issue_id" = "issue_id_p"
jbe@430:         AND "initiative"."direct_majority"
jbe@430:         AND "initiative"."indirect_majority"
jbe@430:         AND "initiative"."better_than_status_quo"
jbe@430:         AND (
jbe@430:           "policy_row"."no_multistage_majority" = FALSE OR
jbe@430:           "initiative"."multistage_majority" = FALSE )
jbe@430:         AND (
jbe@430:           "policy_row"."no_reverse_beat_path" = FALSE OR
jbe@430:           coalesce("initiative"."reverse_beat_path", FALSE) = FALSE );
jbe@430:       -- mark final winner:
jbe@430:       UPDATE "initiative" SET "winner" = TRUE
jbe@430:         FROM (
jbe@430:           SELECT "id" AS "initiative_id"
jbe@430:           FROM "initiative"
jbe@430:           WHERE "issue_id" = "issue_id_p" AND "eligible"
jbe@430:           ORDER BY
jbe@430:             "schulze_rank",
jbe@430:             "id"
jbe@430:           LIMIT 1
jbe@430:         ) AS "subquery"
jbe@430:         WHERE "id" = "subquery"."initiative_id";
jbe@430:       -- write (final) ranks:
jbe@430:       "rank_v" := 1;
jbe@430:       FOR "initiative_id_v" IN
jbe@430:         SELECT "id"
jbe@430:         FROM "initiative"
jbe@430:         WHERE "issue_id" = "issue_id_p" AND "admitted"
jbe@430:         ORDER BY
jbe@430:           "winner" DESC,
jbe@430:           "eligible" DESC,
jbe@430:           "schulze_rank",
jbe@430:           "id"
jbe@430:       LOOP
jbe@430:         UPDATE "initiative" SET "rank" = "rank_v"
jbe@430:           WHERE "id" = "initiative_id_v";
jbe@430:         "rank_v" := "rank_v" + 1;
jbe@430:       END LOOP;
jbe@430:       -- set schulze rank of status quo and mark issue as finished:
jbe@430:       UPDATE "issue" SET
jbe@430:         "status_quo_schulze_rank" = "rank_ary"[1],
jbe@430:         "state" =
jbe@430:           CASE WHEN EXISTS (
jbe@430:             SELECT NULL FROM "initiative"
jbe@430:             WHERE "issue_id" = "issue_id_p" AND "winner"
jbe@430:           ) THEN
jbe@430:             'finished_with_winner'::"issue_state"
jbe@430:           ELSE
jbe@430:             'finished_without_winner'::"issue_state"
jbe@430:           END,
jbe@430:         "closed" = "phase_finished",
jbe@430:         "phase_finished" = NULL
jbe@430:         WHERE "id" = "issue_id_p";
jbe@430:       RETURN;
jbe@430:     END;
jbe@430:   $$;
jbe@430: 
jbe@423: 
jbe@423: COMMIT;