jbe@396: BEGIN; jbe@396: jbe@396: CREATE OR REPLACE VIEW "liquid_feedback_version" AS jbe@420: SELECT * FROM (VALUES ('3.0.1', 3, 0, 1)) jbe@396: AS "subquery"("string", "major", "minor", "revision"); jbe@396: jbe@410: CREATE TABLE "issue_order_in_admission_state" ( jbe@400: "id" INT8 PRIMARY KEY, --REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE, jbe@410: "order_in_area" INT4, jbe@410: "order_in_unit" INT4 ); jbe@396: jbe@410: COMMENT ON TABLE "issue_order_in_admission_state" IS 'Ordering information for issues that are not stored in the "issue" table to avoid locking of multiple issues at once; Filled/updated by "lf_update_issue_order"'; jbe@398: jbe@410: COMMENT ON COLUMN "issue_order_in_admission_state"."id" IS 'References "issue" ("id") but has no referential integrity trigger associated, due to performance/locking issues'; jbe@410: COMMENT ON COLUMN "issue_order_in_admission_state"."order_in_area" IS 'Order of issues in admission state within a single area; NULL values sort last'; jbe@410: COMMENT ON COLUMN "issue_order_in_admission_state"."order_in_unit" IS 'Order of issues in admission state within all areas of a unit; NULL values sort last'; jbe@396: jbe@396: CREATE VIEW "issue_supporter_in_admission_state" AS jbe@396: SELECT DISTINCT jbe@410: "area"."unit_id", jbe@396: "issue"."area_id", jbe@396: "issue"."id" AS "issue_id", jbe@396: "supporter"."member_id", jbe@396: "direct_interest_snapshot"."weight" jbe@396: FROM "issue" jbe@410: JOIN "area" ON "area"."id" = "issue"."area_id" jbe@396: JOIN "supporter" ON "supporter"."issue_id" = "issue"."id" jbe@396: JOIN "direct_interest_snapshot" jbe@396: ON "direct_interest_snapshot"."issue_id" = "issue"."id" jbe@396: AND "direct_interest_snapshot"."event" = "issue"."latest_snapshot_event" jbe@396: AND "direct_interest_snapshot"."member_id" = "supporter"."member_id" jbe@396: WHERE "issue"."state" = 'admission'::"issue_state"; jbe@396: jbe@396: COMMENT ON VIEW "issue_supporter_in_admission_state" IS 'Helper view for "lf_update_issue_order" to allow a (proportional) ordering of issues within an area'; jbe@396: jbe@411: COMMENT ON COLUMN "initiative"."schulze_rank" IS 'Schulze-Ranking'; jbe@411: COMMENT ON COLUMN "initiative"."better_than_status_quo" IS 'TRUE, if initiative has a schulze-ranking better than the status quo'; jbe@411: COMMENT ON COLUMN "initiative"."worse_than_status_quo" IS 'TRUE, if initiative has a schulze-ranking worse than the status quo (DEPRECATED, since schulze-ranking is unique per issue; use "better_than_status_quo"=FALSE)'; jbe@411: COMMENT ON COLUMN "initiative"."winner" IS 'Winner is the "eligible" initiative with best "schulze_rank"'; jbe@411: jbe@411: CREATE OR REPLACE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE) jbe@411: RETURNS VOID jbe@411: LANGUAGE 'plpgsql' VOLATILE AS $$ jbe@411: DECLARE jbe@411: "issue_row" "issue"%ROWTYPE; jbe@411: "policy_row" "policy"%ROWTYPE; jbe@411: "dimension_v" INTEGER; jbe@411: "vote_matrix" INT4[][]; -- absolute votes jbe@411: "matrix" INT8[][]; -- defeat strength / best paths jbe@411: "i" INTEGER; jbe@411: "j" INTEGER; jbe@411: "k" INTEGER; jbe@411: "battle_row" "battle"%ROWTYPE; jbe@411: "rank_ary" INT4[]; jbe@411: "rank_v" INT4; jbe@411: "initiative_id_v" "initiative"."id"%TYPE; jbe@411: BEGIN jbe@411: PERFORM "require_transaction_isolation"(); jbe@411: SELECT * INTO "issue_row" jbe@411: FROM "issue" WHERE "id" = "issue_id_p"; jbe@411: SELECT * INTO "policy_row" jbe@411: FROM "policy" WHERE "id" = "issue_row"."policy_id"; jbe@411: SELECT count(1) INTO "dimension_v" jbe@411: FROM "battle_participant" WHERE "issue_id" = "issue_id_p"; jbe@411: -- Create "vote_matrix" with absolute number of votes in pairwise jbe@411: -- comparison: jbe@411: "vote_matrix" := array_fill(NULL::INT4, ARRAY["dimension_v", "dimension_v"]); jbe@411: "i" := 1; jbe@411: "j" := 2; jbe@411: FOR "battle_row" IN jbe@411: SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p" jbe@411: ORDER BY jbe@411: "winning_initiative_id" NULLS FIRST, jbe@411: "losing_initiative_id" NULLS FIRST jbe@411: LOOP jbe@411: "vote_matrix"["i"]["j"] := "battle_row"."count"; jbe@411: IF "j" = "dimension_v" THEN jbe@411: "i" := "i" + 1; jbe@411: "j" := 1; jbe@411: ELSE jbe@411: "j" := "j" + 1; jbe@411: IF "j" = "i" THEN jbe@411: "j" := "j" + 1; jbe@411: END IF; jbe@411: END IF; jbe@411: END LOOP; jbe@411: IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN jbe@411: RAISE EXCEPTION 'Wrong battle count (should not happen)'; jbe@411: END IF; jbe@411: -- Store defeat strengths in "matrix" using "defeat_strength" jbe@411: -- function: jbe@411: "matrix" := array_fill(NULL::INT8, ARRAY["dimension_v", "dimension_v"]); jbe@411: "i" := 1; jbe@411: LOOP jbe@411: "j" := 1; jbe@411: LOOP jbe@411: IF "i" != "j" THEN jbe@411: "matrix"["i"]["j"] := "defeat_strength"( jbe@411: "vote_matrix"["i"]["j"], jbe@411: "vote_matrix"["j"]["i"] jbe@411: ); jbe@411: END IF; jbe@411: EXIT WHEN "j" = "dimension_v"; jbe@411: "j" := "j" + 1; jbe@411: END LOOP; jbe@411: EXIT WHEN "i" = "dimension_v"; jbe@411: "i" := "i" + 1; jbe@411: END LOOP; jbe@411: -- Find best paths: jbe@411: "i" := 1; jbe@411: LOOP jbe@411: "j" := 1; jbe@411: LOOP jbe@411: IF "i" != "j" THEN jbe@411: "k" := 1; jbe@411: LOOP jbe@411: IF "i" != "k" AND "j" != "k" THEN jbe@411: IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN jbe@411: IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN jbe@411: "matrix"["j"]["k"] := "matrix"["j"]["i"]; jbe@411: END IF; jbe@411: ELSE jbe@411: IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN jbe@411: "matrix"["j"]["k"] := "matrix"["i"]["k"]; jbe@411: END IF; jbe@411: END IF; jbe@411: END IF; jbe@411: EXIT WHEN "k" = "dimension_v"; jbe@411: "k" := "k" + 1; jbe@411: END LOOP; jbe@411: END IF; jbe@411: EXIT WHEN "j" = "dimension_v"; jbe@411: "j" := "j" + 1; jbe@411: END LOOP; jbe@411: EXIT WHEN "i" = "dimension_v"; jbe@411: "i" := "i" + 1; jbe@411: END LOOP; jbe@411: -- Determine order of winners: jbe@411: "rank_ary" := array_fill(NULL::INT4, ARRAY["dimension_v"]); jbe@411: "rank_v" := 1; jbe@411: LOOP jbe@411: "i" := 1; jbe@411: LOOP jbe@411: IF "rank_ary"["i"] ISNULL THEN jbe@411: "j" := 1; jbe@411: LOOP jbe@411: IF jbe@411: "i" != "j" AND jbe@411: "rank_ary"["j"] ISNULL AND jbe@411: ( "matrix"["j"]["i"] > "matrix"["i"]["j"] OR jbe@411: -- tie-breaking by "id" jbe@411: ( "matrix"["j"]["i"] = "matrix"["i"]["j"] AND jbe@411: "j" < "i" ) ) jbe@411: THEN jbe@411: -- someone else is better jbe@411: EXIT; jbe@411: END IF; jbe@411: "j" := "j" + 1; jbe@411: IF "j" = "dimension_v" + 1 THEN jbe@411: -- noone is better jbe@411: "rank_ary"["i"] := "rank_v"; jbe@411: EXIT; jbe@411: END IF; jbe@411: END LOOP; jbe@411: EXIT WHEN "j" = "dimension_v" + 1; jbe@411: END IF; jbe@411: "i" := "i" + 1; jbe@411: IF "i" > "dimension_v" THEN jbe@411: RAISE EXCEPTION 'Schulze ranking does not compute (should not happen)'; jbe@411: END IF; jbe@411: END LOOP; jbe@411: EXIT WHEN "rank_v" = "dimension_v"; jbe@411: "rank_v" := "rank_v" + 1; jbe@411: END LOOP; jbe@411: -- write preliminary results: jbe@411: "i" := 2; -- omit status quo with "i" = 1 jbe@411: FOR "initiative_id_v" IN jbe@411: SELECT "id" FROM "initiative" jbe@411: WHERE "issue_id" = "issue_id_p" AND "admitted" jbe@411: ORDER BY "id" jbe@411: LOOP jbe@411: UPDATE "initiative" SET jbe@411: "direct_majority" = jbe@411: CASE WHEN "policy_row"."direct_majority_strict" THEN jbe@411: "positive_votes" * "policy_row"."direct_majority_den" > jbe@411: "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes") jbe@411: ELSE jbe@411: "positive_votes" * "policy_row"."direct_majority_den" >= jbe@411: "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes") jbe@411: END jbe@411: AND "positive_votes" >= "policy_row"."direct_majority_positive" jbe@411: AND "issue_row"."voter_count"-"negative_votes" >= jbe@411: "policy_row"."direct_majority_non_negative", jbe@411: "indirect_majority" = jbe@411: CASE WHEN "policy_row"."indirect_majority_strict" THEN jbe@411: "positive_votes" * "policy_row"."indirect_majority_den" > jbe@411: "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes") jbe@411: ELSE jbe@411: "positive_votes" * "policy_row"."indirect_majority_den" >= jbe@411: "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes") jbe@411: END jbe@411: AND "positive_votes" >= "policy_row"."indirect_majority_positive" jbe@411: AND "issue_row"."voter_count"-"negative_votes" >= jbe@411: "policy_row"."indirect_majority_non_negative", jbe@411: "schulze_rank" = "rank_ary"["i"], jbe@411: "better_than_status_quo" = "rank_ary"["i"] < "rank_ary"[1], jbe@411: "worse_than_status_quo" = "rank_ary"["i"] > "rank_ary"[1], jbe@411: "multistage_majority" = "rank_ary"["i"] >= "rank_ary"[1], jbe@411: "reverse_beat_path" = "matrix"[1]["i"] >= 0, jbe@411: "eligible" = FALSE, jbe@411: "winner" = FALSE, jbe@411: "rank" = NULL -- NOTE: in cases of manual reset of issue state jbe@411: WHERE "id" = "initiative_id_v"; jbe@411: "i" := "i" + 1; jbe@411: END LOOP; jbe@411: IF "i" != "dimension_v" + 1 THEN jbe@411: RAISE EXCEPTION 'Wrong winner count (should not happen)'; jbe@411: END IF; jbe@411: -- take indirect majorities into account: jbe@411: LOOP jbe@411: UPDATE "initiative" SET "indirect_majority" = TRUE jbe@411: FROM ( jbe@411: SELECT "new_initiative"."id" AS "initiative_id" jbe@411: FROM "initiative" "old_initiative" jbe@411: JOIN "initiative" "new_initiative" jbe@411: ON "new_initiative"."issue_id" = "issue_id_p" jbe@411: AND "new_initiative"."indirect_majority" = FALSE jbe@411: JOIN "battle" "battle_win" jbe@411: ON "battle_win"."issue_id" = "issue_id_p" jbe@411: AND "battle_win"."winning_initiative_id" = "new_initiative"."id" jbe@411: AND "battle_win"."losing_initiative_id" = "old_initiative"."id" jbe@411: JOIN "battle" "battle_lose" jbe@411: ON "battle_lose"."issue_id" = "issue_id_p" jbe@411: AND "battle_lose"."losing_initiative_id" = "new_initiative"."id" jbe@411: AND "battle_lose"."winning_initiative_id" = "old_initiative"."id" jbe@411: WHERE "old_initiative"."issue_id" = "issue_id_p" jbe@411: AND "old_initiative"."indirect_majority" = TRUE jbe@411: AND CASE WHEN "policy_row"."indirect_majority_strict" THEN jbe@411: "battle_win"."count" * "policy_row"."indirect_majority_den" > jbe@411: "policy_row"."indirect_majority_num" * jbe@411: ("battle_win"."count"+"battle_lose"."count") jbe@411: ELSE jbe@411: "battle_win"."count" * "policy_row"."indirect_majority_den" >= jbe@411: "policy_row"."indirect_majority_num" * jbe@411: ("battle_win"."count"+"battle_lose"."count") jbe@411: END jbe@411: AND "battle_win"."count" >= "policy_row"."indirect_majority_positive" jbe@411: AND "issue_row"."voter_count"-"battle_lose"."count" >= jbe@411: "policy_row"."indirect_majority_non_negative" jbe@411: ) AS "subquery" jbe@411: WHERE "id" = "subquery"."initiative_id"; jbe@411: EXIT WHEN NOT FOUND; jbe@411: END LOOP; jbe@411: -- set "multistage_majority" for remaining matching initiatives: jbe@411: UPDATE "initiative" SET "multistage_majority" = TRUE jbe@411: FROM ( jbe@411: SELECT "losing_initiative"."id" AS "initiative_id" jbe@411: FROM "initiative" "losing_initiative" jbe@411: JOIN "initiative" "winning_initiative" jbe@411: ON "winning_initiative"."issue_id" = "issue_id_p" jbe@411: AND "winning_initiative"."admitted" jbe@411: JOIN "battle" "battle_win" jbe@411: ON "battle_win"."issue_id" = "issue_id_p" jbe@411: AND "battle_win"."winning_initiative_id" = "winning_initiative"."id" jbe@411: AND "battle_win"."losing_initiative_id" = "losing_initiative"."id" jbe@411: JOIN "battle" "battle_lose" jbe@411: ON "battle_lose"."issue_id" = "issue_id_p" jbe@411: AND "battle_lose"."losing_initiative_id" = "winning_initiative"."id" jbe@411: AND "battle_lose"."winning_initiative_id" = "losing_initiative"."id" jbe@411: WHERE "losing_initiative"."issue_id" = "issue_id_p" jbe@411: AND "losing_initiative"."admitted" jbe@411: AND "winning_initiative"."schulze_rank" < jbe@411: "losing_initiative"."schulze_rank" jbe@411: AND "battle_win"."count" > "battle_lose"."count" jbe@411: AND ( jbe@411: "battle_win"."count" > "winning_initiative"."positive_votes" OR jbe@411: "battle_lose"."count" < "losing_initiative"."negative_votes" ) jbe@411: ) AS "subquery" jbe@411: WHERE "id" = "subquery"."initiative_id"; jbe@411: -- mark eligible initiatives: jbe@411: UPDATE "initiative" SET "eligible" = TRUE jbe@411: WHERE "issue_id" = "issue_id_p" jbe@411: AND "initiative"."direct_majority" jbe@411: AND "initiative"."indirect_majority" jbe@411: AND "initiative"."better_than_status_quo" jbe@411: AND ( jbe@411: "policy_row"."no_multistage_majority" = FALSE OR jbe@411: "initiative"."multistage_majority" = FALSE ) jbe@411: AND ( jbe@411: "policy_row"."no_reverse_beat_path" = FALSE OR jbe@411: "initiative"."reverse_beat_path" = FALSE ); jbe@411: -- mark final winner: jbe@411: UPDATE "initiative" SET "winner" = TRUE jbe@411: FROM ( jbe@411: SELECT "id" AS "initiative_id" jbe@411: FROM "initiative" jbe@411: WHERE "issue_id" = "issue_id_p" AND "eligible" jbe@411: ORDER BY jbe@411: "schulze_rank", jbe@411: "id" jbe@411: LIMIT 1 jbe@411: ) AS "subquery" jbe@411: WHERE "id" = "subquery"."initiative_id"; jbe@411: -- write (final) ranks: jbe@411: "rank_v" := 1; jbe@411: FOR "initiative_id_v" IN jbe@411: SELECT "id" jbe@411: FROM "initiative" jbe@411: WHERE "issue_id" = "issue_id_p" AND "admitted" jbe@411: ORDER BY jbe@411: "winner" DESC, jbe@411: "eligible" DESC, jbe@411: "schulze_rank", jbe@411: "id" jbe@411: LOOP jbe@411: UPDATE "initiative" SET "rank" = "rank_v" jbe@411: WHERE "id" = "initiative_id_v"; jbe@411: "rank_v" := "rank_v" + 1; jbe@411: END LOOP; jbe@411: -- set schulze rank of status quo and mark issue as finished: jbe@411: UPDATE "issue" SET jbe@411: "status_quo_schulze_rank" = "rank_ary"[1], jbe@411: "state" = jbe@411: CASE WHEN EXISTS ( jbe@411: SELECT NULL FROM "initiative" jbe@411: WHERE "issue_id" = "issue_id_p" AND "winner" jbe@411: ) THEN jbe@411: 'finished_with_winner'::"issue_state" jbe@411: ELSE jbe@411: 'finished_without_winner'::"issue_state" jbe@411: END, jbe@411: "closed" = "phase_finished", jbe@411: "phase_finished" = NULL jbe@411: WHERE "id" = "issue_id_p"; jbe@411: RETURN; jbe@411: END; jbe@411: $$; jbe@411: jbe@436: ALTER TABLE "initiative" ADD COLUMN "first_preference_votes" INT4; jbe@436: jbe@436: ALTER TABLE "initiative" DROP CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results"; jbe@436: ALTER TABLE "initiative" ADD CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results" CHECK ( jbe@436: ( "admitted" NOTNULL AND "admitted" = TRUE ) OR jbe@436: ( "first_preference_votes" ISNULL AND jbe@436: "positive_votes" ISNULL AND "negative_votes" ISNULL AND jbe@436: "direct_majority" ISNULL AND "indirect_majority" ISNULL AND jbe@436: "schulze_rank" ISNULL AND jbe@436: "better_than_status_quo" ISNULL AND "worse_than_status_quo" ISNULL AND jbe@436: "reverse_beat_path" ISNULL AND "multistage_majority" ISNULL AND jbe@436: "eligible" ISNULL AND "winner" ISNULL AND "rank" ISNULL ) ); jbe@436: jbe@436: COMMENT ON COLUMN "initiative"."first_preference_votes" IS 'Number of direct and delegating voters who ranked this initiative as their first choice'; jbe@436: COMMENT ON COLUMN "initiative"."positive_votes" IS 'Number of direct and delegating voters who ranked this initiative better than the status quo'; jbe@436: COMMENT ON COLUMN "initiative"."negative_votes" IS 'Number of direct and delegating voters who ranked this initiative worse than the status quo'; jbe@436: jbe@436: -- UPDATE TABLE "vote" SET "grade" = 0 WHERE "grade" ISNULL; -- should not be necessary jbe@436: ALTER TABLE "vote" ALTER COLUMN "grade" SET NOT NULL; jbe@436: jbe@436: ALTER TABLE "vote" ADD COLUMN "first_preference" BOOLEAN; jbe@436: jbe@436: ALTER TABLE "vote" ADD jbe@436: CONSTRAINT "first_preference_flag_only_set_on_positive_grades" jbe@436: CHECK ("grade" > 0 OR "first_preference" ISNULL); jbe@436: jbe@436: COMMENT ON COLUMN "vote"."first_preference" IS 'Value is automatically set after voting is finished. For positive grades, this value is set to true for the highest (i.e. best) grade.'; jbe@436: jbe@436: INSERT INTO "temporary_transaction_data" ("key", "value") jbe@436: VALUES ('override_protection_triggers', TRUE::TEXT); jbe@436: jbe@436: UPDATE "vote" SET "first_preference" = "subquery"."first_preference" jbe@436: FROM ( jbe@436: SELECT jbe@436: "vote"."initiative_id", jbe@436: "vote"."member_id", jbe@436: CASE WHEN "vote"."grade" > 0 THEN jbe@436: CASE WHEN "vote"."grade" = max("agg"."grade") THEN TRUE ELSE FALSE END jbe@436: ELSE NULL jbe@436: END AS "first_preference" jbe@436: FROM "vote" jbe@436: JOIN "initiative" -- NOTE: due to missing index on issue_id jbe@436: ON "vote"."issue_id" = "initiative"."issue_id" jbe@436: JOIN "vote" AS "agg" jbe@436: ON "initiative"."id" = "agg"."initiative_id" jbe@436: AND "vote"."member_id" = "agg"."member_id" jbe@436: GROUP BY "vote"."initiative_id", "vote"."member_id" jbe@436: ) AS "subquery" jbe@436: WHERE "vote"."initiative_id" = "subquery"."initiative_id" jbe@436: AND "vote"."member_id" = "subquery"."member_id"; jbe@436: jbe@436: DELETE FROM "temporary_transaction_data" jbe@436: WHERE "key" = 'override_protection_triggers'; jbe@436: jbe@436: UPDATE "initiative" jbe@436: SET "first_preference_votes" = coalesce("subquery"."sum", 0) jbe@436: FROM ( jbe@436: SELECT "vote"."initiative_id", sum("direct_voter"."weight") jbe@436: FROM "vote" JOIN "direct_voter" jbe@436: ON "vote"."issue_id" = "direct_voter"."issue_id" jbe@436: AND "vote"."member_id" = "direct_voter"."member_id" jbe@436: WHERE "vote"."first_preference" jbe@436: GROUP BY "vote"."initiative_id" jbe@436: ) AS "subquery" jbe@436: WHERE "initiative"."admitted" jbe@436: AND "initiative"."id" = "subquery"."initiative_id"; jbe@436: jbe@436: -- reconstruct battle data (originating from LiquidFeedback Core before v2.0.0) jbe@436: -- to avoid future data loss when executing "clean_issue" to delete voting data: jbe@436: INSERT INTO "battle" ( jbe@436: "issue_id", jbe@436: "winning_initiative_id", jbe@436: "losing_initiative_id", jbe@436: "count" jbe@436: ) SELECT jbe@436: "battle_view"."issue_id", jbe@436: "battle_view"."winning_initiative_id", jbe@436: "battle_view"."losing_initiative_id", jbe@436: "battle_view"."count" jbe@436: FROM ( jbe@436: SELECT jbe@436: "issue"."id" AS "issue_id", jbe@436: "winning_initiative"."id" AS "winning_initiative_id", jbe@436: "losing_initiative"."id" AS "losing_initiative_id", jbe@436: sum( jbe@436: CASE WHEN jbe@436: coalesce("better_vote"."grade", 0) > jbe@436: coalesce("worse_vote"."grade", 0) jbe@436: THEN "direct_voter"."weight" ELSE 0 END jbe@436: ) AS "count" jbe@436: FROM "issue" jbe@436: LEFT JOIN "direct_voter" jbe@436: ON "issue"."id" = "direct_voter"."issue_id" jbe@436: JOIN "battle_participant" AS "winning_initiative" jbe@436: ON "issue"."id" = "winning_initiative"."issue_id" jbe@436: JOIN "battle_participant" AS "losing_initiative" jbe@436: ON "issue"."id" = "losing_initiative"."issue_id" jbe@436: LEFT JOIN "vote" AS "better_vote" jbe@436: ON "direct_voter"."member_id" = "better_vote"."member_id" jbe@436: AND "winning_initiative"."id" = "better_vote"."initiative_id" jbe@436: LEFT JOIN "vote" AS "worse_vote" jbe@436: ON "direct_voter"."member_id" = "worse_vote"."member_id" jbe@436: AND "losing_initiative"."id" = "worse_vote"."initiative_id" jbe@436: WHERE "issue"."state" IN ('finished_with_winner', 'finished_without_winner') jbe@436: AND "winning_initiative"."id" != "losing_initiative"."id" jbe@436: -- NOTE: comparisons with status-quo are intentionally omitted to mark jbe@436: -- issues that were counted prior LiquidFeedback Core v2.0.0 jbe@436: GROUP BY jbe@436: "issue"."id", jbe@436: "winning_initiative"."id", jbe@436: "losing_initiative"."id" jbe@436: ) AS "battle_view" jbe@436: LEFT JOIN "battle" jbe@436: ON "battle_view"."winning_initiative_id" = "battle"."winning_initiative_id" jbe@436: AND "battle_view"."losing_initiative_id" = "battle"."losing_initiative_id" jbe@436: WHERE "battle" ISNULL; jbe@436: jbe@436: CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE) jbe@436: RETURNS VOID jbe@436: LANGUAGE 'plpgsql' VOLATILE AS $$ jbe@436: DECLARE jbe@436: "area_id_v" "area"."id"%TYPE; jbe@436: "unit_id_v" "unit"."id"%TYPE; jbe@436: "member_id_v" "member"."id"%TYPE; jbe@436: BEGIN jbe@436: PERFORM "require_transaction_isolation"(); jbe@436: SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p"; jbe@436: SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v"; jbe@436: -- override protection triggers: jbe@436: INSERT INTO "temporary_transaction_data" ("key", "value") jbe@436: VALUES ('override_protection_triggers', TRUE::TEXT); jbe@436: -- delete timestamp of voting comment: jbe@436: UPDATE "direct_voter" SET "comment_changed" = NULL jbe@436: WHERE "issue_id" = "issue_id_p"; jbe@436: -- delete delegating votes (in cases of manual reset of issue state): jbe@436: DELETE FROM "delegating_voter" jbe@436: WHERE "issue_id" = "issue_id_p"; jbe@436: -- delete votes from non-privileged voters: jbe@436: DELETE FROM "direct_voter" jbe@436: USING ( jbe@436: SELECT jbe@436: "direct_voter"."member_id" jbe@436: FROM "direct_voter" jbe@436: JOIN "member" ON "direct_voter"."member_id" = "member"."id" jbe@436: LEFT JOIN "privilege" jbe@436: ON "privilege"."unit_id" = "unit_id_v" jbe@436: AND "privilege"."member_id" = "direct_voter"."member_id" jbe@436: WHERE "direct_voter"."issue_id" = "issue_id_p" AND ( jbe@436: "member"."active" = FALSE OR jbe@436: "privilege"."voting_right" ISNULL OR jbe@436: "privilege"."voting_right" = FALSE jbe@436: ) jbe@436: ) AS "subquery" jbe@436: WHERE "direct_voter"."issue_id" = "issue_id_p" jbe@436: AND "direct_voter"."member_id" = "subquery"."member_id"; jbe@436: -- consider delegations: jbe@436: UPDATE "direct_voter" SET "weight" = 1 jbe@436: WHERE "issue_id" = "issue_id_p"; jbe@436: PERFORM "add_vote_delegations"("issue_id_p"); jbe@436: -- mark first preferences: jbe@436: UPDATE "vote" SET "first_preference" = "subquery"."first_preference" jbe@436: FROM ( jbe@436: SELECT jbe@436: "vote"."initiative_id", jbe@436: "vote"."member_id", jbe@436: CASE WHEN "vote"."grade" > 0 THEN jbe@436: CASE WHEN "vote"."grade" = max("agg"."grade") THEN TRUE ELSE FALSE END jbe@436: ELSE NULL jbe@436: END AS "first_preference" jbe@436: FROM "vote" jbe@436: JOIN "initiative" -- NOTE: due to missing index on issue_id jbe@436: ON "vote"."issue_id" = "initiative"."issue_id" jbe@436: JOIN "vote" AS "agg" jbe@436: ON "initiative"."id" = "agg"."initiative_id" jbe@436: AND "vote"."member_id" = "agg"."member_id" jbe@436: GROUP BY "vote"."initiative_id", "vote"."member_id" jbe@436: ) AS "subquery" jbe@436: WHERE "vote"."issue_id" = "issue_id_p" jbe@436: AND "vote"."initiative_id" = "subquery"."initiative_id" jbe@436: AND "vote"."member_id" = "subquery"."member_id"; jbe@436: -- finish overriding protection triggers (avoids garbage): jbe@436: DELETE FROM "temporary_transaction_data" jbe@436: WHERE "key" = 'override_protection_triggers'; jbe@436: -- materialize battle_view: jbe@436: -- NOTE: "closed" column of issue must be set at this point jbe@436: DELETE FROM "battle" WHERE "issue_id" = "issue_id_p"; jbe@436: INSERT INTO "battle" ( jbe@436: "issue_id", jbe@436: "winning_initiative_id", "losing_initiative_id", jbe@436: "count" jbe@436: ) SELECT jbe@436: "issue_id", jbe@436: "winning_initiative_id", "losing_initiative_id", jbe@436: "count" jbe@436: FROM "battle_view" WHERE "issue_id" = "issue_id_p"; jbe@436: -- set voter count: jbe@436: UPDATE "issue" SET jbe@436: "voter_count" = ( jbe@436: SELECT coalesce(sum("weight"), 0) jbe@436: FROM "direct_voter" WHERE "issue_id" = "issue_id_p" jbe@436: ) jbe@436: WHERE "id" = "issue_id_p"; jbe@436: -- calculate "first_preference_votes": jbe@436: UPDATE "initiative" jbe@436: SET "first_preference_votes" = coalesce("subquery"."sum", 0) jbe@436: FROM ( jbe@436: SELECT "vote"."initiative_id", sum("direct_voter"."weight") jbe@436: FROM "vote" JOIN "direct_voter" jbe@436: ON "vote"."issue_id" = "direct_voter"."issue_id" jbe@436: AND "vote"."member_id" = "direct_voter"."member_id" jbe@436: WHERE "vote"."first_preference" jbe@436: GROUP BY "vote"."initiative_id" jbe@436: ) AS "subquery" jbe@436: WHERE "initiative"."issue_id" = "issue_id_p" jbe@436: AND "initiative"."admitted" jbe@436: AND "initiative"."id" = "subquery"."initiative_id"; jbe@436: -- copy "positive_votes" and "negative_votes" from "battle" table: jbe@436: UPDATE "initiative" SET jbe@436: "positive_votes" = "battle_win"."count", jbe@436: "negative_votes" = "battle_lose"."count" jbe@436: FROM "battle" AS "battle_win", "battle" AS "battle_lose" jbe@436: WHERE jbe@436: "battle_win"."issue_id" = "issue_id_p" AND jbe@436: "battle_win"."winning_initiative_id" = "initiative"."id" AND jbe@436: "battle_win"."losing_initiative_id" ISNULL AND jbe@436: "battle_lose"."issue_id" = "issue_id_p" AND jbe@436: "battle_lose"."losing_initiative_id" = "initiative"."id" AND jbe@436: "battle_lose"."winning_initiative_id" ISNULL; jbe@436: END; jbe@436: $$; jbe@436: jbe@396: COMMIT;