# HG changeset patch # User jbe # Date 1306198909 -7200 # Node ID 5edfb00d840a665935daac95d40ebed79e265645 # Parent 96db76cde2184d7ba1c40a3139ed953c10dfa42b Implementation of Schulze's Supermajority Requirements Changes in detail: - Renamed column "agreed" of table "initiative" to "majority" - Added column "winner" to table "initiative" - Extended constraint "non_admitted_initiatives_cant_contain_voting_results" of table "initiative" to "rank" and "winner" columns - Removed constraint "non_agreed_initiatives_cant_get_a_rank" from table "initiative" - Replaced PRIMARY KEY of "battle" table by a single NOT NULL constraint on column "issue_id" and three (partial) indicies allowing NULL values as initiative ids - Added constraint "initiative_ids_not_equal" to "battle" table - Added view "battle_participant" which selects admitted initiatives plus one virtual "status-quo" initiative for each issue denoted by an initiative_id being NULL - Modified "battle_view" to include all admitted initiatives plus the virtual "status-quo" initiative - Modified function "calculate_ranks" to respect all battle participants, to set the rank of all initiatives where the rank is better than status-quo, and to mark the final winner (if existent) diff -r 96db76cde218 -r 5edfb00d840a core.sql --- a/core.sql Sun Mar 13 13:29:33 2011 +0100 +++ b/core.sql Tue May 24 03:01:49 2011 +0200 @@ -537,8 +537,9 @@ "satisfied_informed_supporter_count" INT4, "positive_votes" INT4, "negative_votes" INT4, - "agreed" BOOLEAN, + "majority" BOOLEAN, "rank" INT4, + "winner" BOOLEAN, "text_search_data" TSVECTOR, CONSTRAINT "all_or_none_of_revoked_and_revoked_by_member_id_must_be_null" CHECK ("revoked" NOTNULL = "revoked_by_member_id" NOTNULL), @@ -547,11 +548,9 @@ CONSTRAINT "revoked_initiatives_cant_be_admitted" CHECK ("revoked" ISNULL OR "admitted" ISNULL), CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results" - CHECK (("admitted" NOTNULL AND "admitted" = TRUE) OR ("positive_votes" ISNULL AND "negative_votes" ISNULL AND "agreed" ISNULL)), - CONSTRAINT "all_or_none_of_positive_votes_negative_votes_and_agreed_must_be_null" - CHECK ("positive_votes" NOTNULL = "negative_votes" NOTNULL AND "positive_votes" NOTNULL = "agreed" NOTNULL), - CONSTRAINT "non_agreed_initiatives_cant_get_a_rank" - CHECK (("agreed" NOTNULL AND "agreed" = TRUE) OR "rank" ISNULL) ); + CHECK (("admitted" NOTNULL AND "admitted" = TRUE) OR ("positive_votes" ISNULL AND "negative_votes" ISNULL AND "majority" ISNULL AND "rank" ISNULL AND "winner" ISNULL)), + CONSTRAINT "all_or_none_of_positive_votes_negative_votes_and_majority_must_be_null" + CHECK ("positive_votes" NOTNULL = "negative_votes" NOTNULL AND "positive_votes" NOTNULL = "majority" NOTNULL) ); CREATE INDEX "initiative_created_idx" ON "initiative" ("created"); CREATE INDEX "initiative_revoked_idx" ON "initiative" ("revoked"); CREATE INDEX "initiative_text_search_data_idx" ON "initiative" USING gin ("text_search_data"); @@ -573,20 +572,27 @@ COMMENT ON COLUMN "initiative"."satisfied_informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"'; COMMENT ON COLUMN "initiative"."positive_votes" IS 'Calculated from table "direct_voter"'; COMMENT ON COLUMN "initiative"."negative_votes" IS 'Calculated from table "direct_voter"'; -COMMENT ON COLUMN "initiative"."agreed" IS 'TRUE, if "positive_votes"/("positive_votes"+"negative_votes") is strictly greater or greater-equal than "majority_num"/"majority_den"'; -COMMENT ON COLUMN "initiative"."rank" IS 'Rank of approved initiatives (winner is 1), calculated from table "direct_voter"'; +COMMENT ON COLUMN "initiative"."majority" IS 'TRUE, if "positive_votes"/("positive_votes"+"negative_votes") is strictly greater or greater-equal than "majority_num"/"majority_den"'; +COMMENT ON COLUMN "initiative"."rank" IS 'Rank of initiatives, which are ranked better than the "status-quo"; set to NULL for initiatives ranked worse than status-quo or being non-admitted'; +COMMENT ON COLUMN "initiative"."winner" IS 'TRUE, if initiative is final winner (having "majority"=TRUE and the smallest rank among other initiatives with "majority"=TRUE); set to NULL for non-admitted initiatives'; CREATE TABLE "battle" ( - PRIMARY KEY ("issue_id", "winning_initiative_id", "losing_initiative_id"), - "issue_id" INT4, + "issue_id" INT4 NOT NULL, "winning_initiative_id" INT4, FOREIGN KEY ("issue_id", "winning_initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE, "losing_initiative_id" INT4, FOREIGN KEY ("issue_id", "losing_initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE, - "count" INT4 NOT NULL); - -COMMENT ON TABLE "battle" IS 'Number of members preferring one initiative to another; Filled by "battle_view" when closing an issue'; + "count" INT4 NOT NULL, + CONSTRAINT "initiative_ids_not_equal" CHECK ( + "winning_initiative_id" != "losing_initiative_id" OR + ( ("winning_initiative_id" NOTNULL AND "losing_initiative_id" ISNULL) OR + ("winning_initiative_id" ISNULL AND "losing_initiative_id" NOTNULL) ) ) ); +CREATE UNIQUE INDEX "battle_winning_losing_idx" ON "battle" ("issue_id", "winning_initiative_id", "losing_initiative_id"); +CREATE UNIQUE INDEX "battle_winning_null_idx" ON "battle" ("issue_id", "winning_initiative_id") WHERE "losing_initiative_id" ISNULL; +CREATE UNIQUE INDEX "battle_null_losing_idx" ON "battle" ("issue_id", "losing_initiative_id") WHERE "winning_initiative_id" ISNULL; + +COMMENT ON TABLE "battle" IS 'Number of members preferring one initiative to another; Filled by "battle_view" when closing an issue; NULL as initiative_id denotes virtual "status-quo" initiative'; CREATE TABLE "ignored_initiative" ( @@ -1870,6 +1876,18 @@ COMMENT ON VIEW "critical_opinion" IS 'Opinions currently causing dissatisfaction'; +CREATE VIEW "battle_participant" AS + SELECT "initiative"."id", "initiative"."issue_id" + FROM "issue" JOIN "initiative" + ON "issue"."id" = "initiative"."issue_id" + WHERE "initiative"."admitted" + UNION ALL + SELECT NULL, "id" AS "issue_id" + FROM "issue"; + +COMMENT ON VIEW "battle_participant" IS 'Helper view for "battle_view" containing admitted initiatives plus virtual "status-quo" initiative denoted by NULL reference'; + + CREATE VIEW "battle_view" AS SELECT "issue"."id" AS "issue_id", @@ -1884,12 +1902,10 @@ FROM "issue" LEFT JOIN "direct_voter" ON "issue"."id" = "direct_voter"."issue_id" - JOIN "initiative" AS "winning_initiative" + JOIN "battle_participant" AS "winning_initiative" ON "issue"."id" = "winning_initiative"."issue_id" - AND "winning_initiative"."agreed" - JOIN "initiative" AS "losing_initiative" + JOIN "battle_participant" AS "losing_initiative" ON "issue"."id" = "losing_initiative"."issue_id" - AND "losing_initiative"."agreed" LEFT JOIN "vote" AS "better_vote" ON "direct_voter"."member_id" = "better_vote"."member_id" AND "winning_initiative"."id" = "better_vote"."initiative_id" @@ -1898,13 +1914,16 @@ AND "losing_initiative"."id" = "worse_vote"."initiative_id" WHERE "issue"."closed" NOTNULL AND "issue"."cleaned" ISNULL - AND "winning_initiative"."id" != "losing_initiative"."id" + AND ( + "winning_initiative"."id" != "losing_initiative"."id" OR + ( ("winning_initiative"."id" NOTNULL AND "losing_initiative"."id" ISNULL) OR + ("winning_initiative"."id" ISNULL AND "losing_initiative"."id" NOTNULL) ) ) GROUP BY "issue"."id", "winning_initiative"."id", "losing_initiative"."id"; -COMMENT ON VIEW "battle_view" IS 'Number of members preferring one initiative to another; Used to fill "battle" table'; +COMMENT ON VIEW "battle_view" IS 'Number of members preferring one initiative (or status-quo) to another initiative (or status-quo); Used to fill "battle" table'; CREATE VIEW "expired_session" AS @@ -3495,7 +3514,7 @@ UPDATE "initiative" SET "positive_votes" = "vote_counts"."positive_votes", "negative_votes" = "vote_counts"."negative_votes", - "agreed" = CASE WHEN "majority_strict" THEN + "majority" = CASE WHEN "majority_strict" THEN "vote_counts"."positive_votes" * "majority_den" > "majority_num" * ("vote_counts"."positive_votes"+"vote_counts"."negative_votes") @@ -3652,12 +3671,9 @@ "initiative_id_v" "initiative"."id"%TYPE; BEGIN PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE; - SELECT count(1) INTO "dimension_v" FROM "initiative" - WHERE "issue_id" = "issue_id_p" AND "agreed"; - IF "dimension_v" = 1 THEN - UPDATE "initiative" SET "rank" = 1 - WHERE "issue_id" = "issue_id_p" AND "agreed"; - ELSIF "dimension_v" > 1 THEN + SELECT count(1) INTO "dimension_v" + FROM "battle_participant" WHERE "issue_id" = "issue_id_p"; + IF "dimension_v" > 1 THEN -- Create "vote_matrix" with absolute number of votes in pairwise -- comparison: "vote_matrix" := "square_matrix_init_string"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4) @@ -3665,7 +3681,9 @@ "j" := 2; FOR "battle_row" IN SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p" - ORDER BY "winning_initiative_id", "losing_initiative_id" + 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 @@ -3773,14 +3791,15 @@ "i" := 1; FOR "initiative_id_v" IN SELECT "id" FROM "initiative" - WHERE "issue_id" = "issue_id_p" AND "agreed" + WHERE "issue_id" = "issue_id_p" AND "admitted" ORDER BY "id" LOOP UPDATE "initiative" SET "rank" = "rank_ary"["i"] - WHERE "id" = "initiative_id_v"; + WHERE "id" = "initiative_id_v" + AND "rank_ary"["i"] < "rank_ary"["dimension_v"]; "i" := "i" + 1; END LOOP; - IF "i" != "dimension_v" + 1 THEN + IF "i" != "dimension_v" THEN RAISE EXCEPTION 'Wrong winner count (should not happen)'; END IF; -- straighten ranks (start counting with 1, no equal ranks): @@ -3797,6 +3816,12 @@ WHERE "id" = "initiative_id_v"; "rank_v" := "rank_v" + 1; END LOOP; + -- mark final winner: + UPDATE "initiative" SET "winner" = + ( "majority" = TRUE AND + "rank" = ( + SELECT min("rank") FROM "initiative" + WHERE "issue_id" = "issue_id_p" ) ); END IF; -- mark issue as finished UPDATE "issue" SET