liquid_feedback_core
diff core.sql @ 147:37a264fb5eef
Merged bugfix in function "close_voting": Create autoreject ballots only for members with "voting_right" in the unit
| author | jbe |
|---|---|
| date | Thu Jun 02 01:25:08 2011 +0200 (2011-06-02) |
| parents | 0fa3c4739ca7 259dd3e127c0 |
| children | ec1fdf2fc8c9 |
line diff
1.1 --- a/core.sql Thu Jun 02 01:23:49 2011 +0200 1.2 +++ b/core.sql Thu Jun 02 01:25:08 2011 +0200 1.3 @@ -7,7 +7,7 @@ 1.4 BEGIN; 1.5 1.6 CREATE VIEW "liquid_feedback_version" AS 1.7 - SELECT * FROM (VALUES ('1.4.0_rc2', 1, 4, -1)) 1.8 + SELECT * FROM (VALUES ('1.4.0_rc1', 1, 4, -1)) 1.9 AS "subquery"("string", "major", "minor", "revision"); 1.10 1.11 1.12 @@ -310,7 +310,9 @@ 1.13 "initiative_quorum_den" INT4 NOT NULL, 1.14 "majority_num" INT4 NOT NULL DEFAULT 1, 1.15 "majority_den" INT4 NOT NULL DEFAULT 2, 1.16 - "majority_strict" BOOLEAN NOT NULL DEFAULT TRUE ); 1.17 + "majority_strict" BOOLEAN NOT NULL DEFAULT TRUE, 1.18 + "majority_positive" INT4 NOT NULL DEFAULT 0, 1.19 + "majority_non_negative" INT4 NOT NULL DEFAULT 0 ); 1.20 CREATE INDEX "policy_active_idx" ON "policy" ("active"); 1.21 1.22 COMMENT ON TABLE "policy" IS 'Policies for a particular proceeding type (timelimits, quorum)'; 1.23 @@ -325,9 +327,11 @@ 1.24 COMMENT ON COLUMN "policy"."issue_quorum_den" IS 'Denominator of potential supporter quorum to be reached by one initiative of an issue to be "accepted"'; 1.25 COMMENT ON COLUMN "policy"."initiative_quorum_num" IS 'Numerator of satisfied supporter quorum to be reached by an initiative to be "admitted" for voting'; 1.26 COMMENT ON COLUMN "policy"."initiative_quorum_den" IS 'Denominator of satisfied supporter quorum to be reached by an initiative to be "admitted" for voting'; 1.27 -COMMENT ON COLUMN "policy"."majority_num" IS 'Numerator of fraction of majority to be reached during voting by an initiative to be aggreed upon'; 1.28 -COMMENT ON COLUMN "policy"."majority_den" IS 'Denominator of fraction of majority to be reached during voting by an initiative to be aggreed upon'; 1.29 +COMMENT ON COLUMN "policy"."majority_num" IS 'Numerator of fraction of majority to be reached during voting by an initiative to be "attainable"'; 1.30 +COMMENT ON COLUMN "policy"."majority_den" IS 'Denominator of fraction of majority to be reached during voting by an initiative to be "attainable"'; 1.31 COMMENT ON COLUMN "policy"."majority_strict" IS 'If TRUE, then the majority must be strictly greater than "majority_num"/"majority_den", otherwise it may also be equal.'; 1.32 +COMMENT ON COLUMN "policy"."majority_positive" IS 'Absolute number of "positive_votes" neccessary for an initiative to be "attainable".'; 1.33 +COMMENT ON COLUMN "policy"."majority_non_negative" IS 'Absolute number of sum of "positive_votes" and abstentions neccessary for an initiative to be "attainable".'; 1.34 1.35 1.36 CREATE TABLE "unit" ( 1.37 @@ -441,8 +445,6 @@ 1.38 "snapshot" TIMESTAMPTZ, 1.39 "latest_snapshot_event" "snapshot_event", 1.40 "population" INT4, 1.41 - "vote_now" INT4, 1.42 - "vote_later" INT4, 1.43 "voter_count" INT4, 1.44 CONSTRAINT "valid_state" CHECK (( 1.45 ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR 1.46 @@ -502,11 +504,9 @@ 1.47 COMMENT ON COLUMN "issue"."discussion_time" IS 'Copied from "policy" table at creation of issue'; 1.48 COMMENT ON COLUMN "issue"."verification_time" IS 'Copied from "policy" table at creation of issue'; 1.49 COMMENT ON COLUMN "issue"."voting_time" IS 'Copied from "policy" table at creation of issue'; 1.50 -COMMENT ON COLUMN "issue"."snapshot" IS 'Point in time, when snapshot tables have been updated and "population", "vote_now", "vote_later" and *_count values were precalculated'; 1.51 +COMMENT ON COLUMN "issue"."snapshot" IS 'Point in time, when snapshot tables have been updated and "population" and *_count values were precalculated'; 1.52 COMMENT ON COLUMN "issue"."latest_snapshot_event" IS 'Event type of latest snapshot for issue; Can be used to select the latest snapshot data in the snapshot tables'; 1.53 COMMENT ON COLUMN "issue"."population" IS 'Sum of "weight" column in table "direct_population_snapshot"'; 1.54 -COMMENT ON COLUMN "issue"."vote_now" IS 'Number of votes in favor of voting now, as calculated from table "direct_interest_snapshot"'; 1.55 -COMMENT ON COLUMN "issue"."vote_later" IS 'Number of votes against voting now, as calculated from table "direct_interest_snapshot"'; 1.56 COMMENT ON COLUMN "issue"."voter_count" IS 'Total number of direct and delegating voters; This value is related to the final voting, while "population" is related to snapshots before the final voting'; 1.57 1.58 1.59 @@ -537,8 +537,13 @@ 1.60 "satisfied_informed_supporter_count" INT4, 1.61 "positive_votes" INT4, 1.62 "negative_votes" INT4, 1.63 - "agreed" BOOLEAN, 1.64 - "rank" INT4, 1.65 + "attainable" BOOLEAN, 1.66 + "favored" BOOLEAN, 1.67 + "unfavored" BOOLEAN, 1.68 + "preliminary_rank" INT4, 1.69 + "final_rank" INT4, 1.70 + "disqualified" BOOLEAN, 1.71 + "winner" BOOLEAN, 1.72 "text_search_data" TSVECTOR, 1.73 CONSTRAINT "all_or_none_of_revoked_and_revoked_by_member_id_must_be_null" 1.74 CHECK ("revoked" NOTNULL = "revoked_by_member_id" NOTNULL), 1.75 @@ -546,12 +551,13 @@ 1.76 CHECK ("revoked" NOTNULL OR "suggested_initiative_id" ISNULL), 1.77 CONSTRAINT "revoked_initiatives_cant_be_admitted" 1.78 CHECK ("revoked" ISNULL OR "admitted" ISNULL), 1.79 - CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results" 1.80 - CHECK (("admitted" NOTNULL AND "admitted" = TRUE) OR ("positive_votes" ISNULL AND "negative_votes" ISNULL AND "agreed" ISNULL)), 1.81 - CONSTRAINT "all_or_none_of_positive_votes_negative_votes_and_agreed_must_be_null" 1.82 - CHECK ("positive_votes" NOTNULL = "negative_votes" NOTNULL AND "positive_votes" NOTNULL = "agreed" NOTNULL), 1.83 - CONSTRAINT "non_agreed_initiatives_cant_get_a_rank" 1.84 - CHECK (("agreed" NOTNULL AND "agreed" = TRUE) OR "rank" ISNULL) ); 1.85 + CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results" CHECK ( 1.86 + ( "admitted" NOTNULL AND "admitted" = TRUE ) OR 1.87 + ( "positive_votes" ISNULL AND "negative_votes" ISNULL AND 1.88 + "attainable" ISNULL AND "favored" ISNULL AND "unfavored" ISNULL AND 1.89 + "disqualified" ISNULL AND "preliminary_rank" ISNULL AND 1.90 + "final_rank" ISNULL AND "winner" ISNULL ) ), 1.91 + CONSTRAINT "favored_excludes_unfavored" CHECK (NOT ("favored" AND "unfavored")) ); 1.92 CREATE INDEX "initiative_created_idx" ON "initiative" ("created"); 1.93 CREATE INDEX "initiative_revoked_idx" ON "initiative" ("revoked"); 1.94 CREATE INDEX "initiative_text_search_data_idx" ON "initiative" USING gin ("text_search_data"); 1.95 @@ -571,22 +577,33 @@ 1.96 COMMENT ON COLUMN "initiative"."informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"'; 1.97 COMMENT ON COLUMN "initiative"."satisfied_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"'; 1.98 COMMENT ON COLUMN "initiative"."satisfied_informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"'; 1.99 -COMMENT ON COLUMN "initiative"."positive_votes" IS 'Calculated from table "direct_voter"'; 1.100 -COMMENT ON COLUMN "initiative"."negative_votes" IS 'Calculated from table "direct_voter"'; 1.101 -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"'; 1.102 -COMMENT ON COLUMN "initiative"."rank" IS 'Rank of approved initiatives (winner is 1), calculated from table "direct_voter"'; 1.103 +COMMENT ON COLUMN "initiative"."positive_votes" IS 'Calculated from table "direct_voter"'; 1.104 +COMMENT ON COLUMN "initiative"."negative_votes" IS 'Calculated from table "direct_voter"'; 1.105 +COMMENT ON COLUMN "initiative"."attainable" IS 'TRUE, if "positive_votes"/("positive_votes"+"negative_votes") is strictly greater or greater-equal than "majority_num"/"majority_den", and "positive_votes" is greater-equal than "majority_positive", and ("positive_votes"+abstentions) is greater-equal than "majority_non_negative"'; 1.106 +COMMENT ON COLUMN "initiative"."favored" IS 'TRUE, if initiative has a schulze-ranking better than the status quo (without tie-breaking)'; 1.107 +COMMENT ON COLUMN "initiative"."unfavored" IS 'TRUE, if initiative has a schulze-ranking worse than the status quo (without tie-breaking)'; 1.108 +COMMENT ON COLUMN "initiative"."preliminary_rank" IS 'Schulze-Ranking without tie-breaking'; 1.109 +COMMENT ON COLUMN "initiative"."final_rank" IS 'Schulze-Ranking after tie-breaking'; 1.110 +COMMENT ON COLUMN "initiative"."disqualified" IS 'TRUE, if initiative may not win, because it either (a) has no better rank than the status quo, or (b) because there exists a better ranked initiative X, which directly beats this initiative, and either more voters prefer X to this initiative than voters preferring X to the status quo or less voters prefer this initiative to X than voters preferring the status quo to X'; 1.111 +COMMENT ON COLUMN "initiative"."winner" IS 'TRUE, if initiative is final winner (best ranked initiative being "attainable" and not "disqualified")'; 1.112 1.113 1.114 CREATE TABLE "battle" ( 1.115 - PRIMARY KEY ("issue_id", "winning_initiative_id", "losing_initiative_id"), 1.116 - "issue_id" INT4, 1.117 + "issue_id" INT4 NOT NULL, 1.118 "winning_initiative_id" INT4, 1.119 FOREIGN KEY ("issue_id", "winning_initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE, 1.120 "losing_initiative_id" INT4, 1.121 FOREIGN KEY ("issue_id", "losing_initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE, 1.122 - "count" INT4 NOT NULL); 1.123 - 1.124 -COMMENT ON TABLE "battle" IS 'Number of members preferring one initiative to another; Filled by "battle_view" when closing an issue'; 1.125 + "count" INT4 NOT NULL, 1.126 + CONSTRAINT "initiative_ids_not_equal" CHECK ( 1.127 + "winning_initiative_id" != "losing_initiative_id" OR 1.128 + ( ("winning_initiative_id" NOTNULL AND "losing_initiative_id" ISNULL) OR 1.129 + ("winning_initiative_id" ISNULL AND "losing_initiative_id" NOTNULL) ) ) ); 1.130 +CREATE UNIQUE INDEX "battle_winning_losing_idx" ON "battle" ("issue_id", "winning_initiative_id", "losing_initiative_id"); 1.131 +CREATE UNIQUE INDEX "battle_winning_null_idx" ON "battle" ("issue_id", "winning_initiative_id") WHERE "losing_initiative_id" ISNULL; 1.132 +CREATE UNIQUE INDEX "battle_null_losing_idx" ON "battle" ("issue_id", "losing_initiative_id") WHERE "winning_initiative_id" ISNULL; 1.133 + 1.134 +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'; 1.135 1.136 1.137 CREATE TABLE "ignored_initiative" ( 1.138 @@ -731,14 +748,12 @@ 1.139 PRIMARY KEY ("issue_id", "member_id"), 1.140 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.141 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.142 - "autoreject" BOOLEAN, 1.143 - "voting_requested" BOOLEAN ); 1.144 + "autoreject" BOOLEAN ); 1.145 CREATE INDEX "interest_member_id_idx" ON "interest" ("member_id"); 1.146 1.147 COMMENT ON TABLE "interest" IS 'Interest of members in a particular issue; Frontends must ensure that interest for fully_frozen or closed issues is not added or removed.'; 1.148 1.149 COMMENT ON COLUMN "interest"."autoreject" IS 'TRUE = member votes against all initiatives in case of not explicitly taking part in the voting procedure'; 1.150 -COMMENT ON COLUMN "interest"."voting_requested" IS 'TRUE = member wants to vote now, FALSE = member wants to vote later, NULL = policy rules should apply'; 1.151 1.152 1.153 CREATE TABLE "initiator" ( 1.154 @@ -854,15 +869,13 @@ 1.155 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.156 "event" "snapshot_event", 1.157 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT, 1.158 - "weight" INT4, 1.159 - "voting_requested" BOOLEAN ); 1.160 + "weight" INT4 ); 1.161 CREATE INDEX "direct_interest_snapshot_member_id_idx" ON "direct_interest_snapshot" ("member_id"); 1.162 1.163 COMMENT ON TABLE "direct_interest_snapshot" IS 'Snapshot of active members having an "interest" in the "issue"'; 1.164 1.165 COMMENT ON COLUMN "direct_interest_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details'; 1.166 COMMENT ON COLUMN "direct_interest_snapshot"."weight" IS 'Weight of member (1 or higher) according to "delegating_interest_snapshot"'; 1.167 -COMMENT ON COLUMN "direct_interest_snapshot"."voting_requested" IS 'Copied from column "voting_requested" of table "interest"'; 1.168 1.169 1.170 CREATE TABLE "delegating_interest_snapshot" ( 1.171 @@ -1870,6 +1883,18 @@ 1.172 COMMENT ON VIEW "critical_opinion" IS 'Opinions currently causing dissatisfaction'; 1.173 1.174 1.175 +CREATE VIEW "battle_participant" AS 1.176 + SELECT "initiative"."id", "initiative"."issue_id" 1.177 + FROM "issue" JOIN "initiative" 1.178 + ON "issue"."id" = "initiative"."issue_id" 1.179 + WHERE "initiative"."admitted" 1.180 + UNION ALL 1.181 + SELECT NULL, "id" AS "issue_id" 1.182 + FROM "issue"; 1.183 + 1.184 +COMMENT ON VIEW "battle_participant" IS 'Helper view for "battle_view" containing admitted initiatives plus virtual "status-quo" initiative denoted by NULL reference'; 1.185 + 1.186 + 1.187 CREATE VIEW "battle_view" AS 1.188 SELECT 1.189 "issue"."id" AS "issue_id", 1.190 @@ -1884,12 +1909,10 @@ 1.191 FROM "issue" 1.192 LEFT JOIN "direct_voter" 1.193 ON "issue"."id" = "direct_voter"."issue_id" 1.194 - JOIN "initiative" AS "winning_initiative" 1.195 + JOIN "battle_participant" AS "winning_initiative" 1.196 ON "issue"."id" = "winning_initiative"."issue_id" 1.197 - AND "winning_initiative"."agreed" 1.198 - JOIN "initiative" AS "losing_initiative" 1.199 + JOIN "battle_participant" AS "losing_initiative" 1.200 ON "issue"."id" = "losing_initiative"."issue_id" 1.201 - AND "losing_initiative"."agreed" 1.202 LEFT JOIN "vote" AS "better_vote" 1.203 ON "direct_voter"."member_id" = "better_vote"."member_id" 1.204 AND "winning_initiative"."id" = "better_vote"."initiative_id" 1.205 @@ -1898,13 +1921,16 @@ 1.206 AND "losing_initiative"."id" = "worse_vote"."initiative_id" 1.207 WHERE "issue"."closed" NOTNULL 1.208 AND "issue"."cleaned" ISNULL 1.209 - AND "winning_initiative"."id" != "losing_initiative"."id" 1.210 + AND ( 1.211 + "winning_initiative"."id" != "losing_initiative"."id" OR 1.212 + ( ("winning_initiative"."id" NOTNULL AND "losing_initiative"."id" ISNULL) OR 1.213 + ("winning_initiative"."id" ISNULL AND "losing_initiative"."id" NOTNULL) ) ) 1.214 GROUP BY 1.215 "issue"."id", 1.216 "winning_initiative"."id", 1.217 "losing_initiative"."id"; 1.218 1.219 -COMMENT ON VIEW "battle_view" IS 'Number of members preferring one initiative to another; Used to fill "battle" table'; 1.220 +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'; 1.221 1.222 1.223 CREATE VIEW "expired_session" AS 1.224 @@ -2918,12 +2944,11 @@ 1.225 WHERE "issue_id" = "issue_id_p" 1.226 AND "event" = 'periodic'; 1.227 INSERT INTO "direct_interest_snapshot" 1.228 - ("issue_id", "event", "member_id", "voting_requested") 1.229 + ("issue_id", "event", "member_id") 1.230 SELECT 1.231 "issue_id_p" AS "issue_id", 1.232 'periodic' AS "event", 1.233 - "member"."id" AS "member_id", 1.234 - "interest"."voting_requested" 1.235 + "member"."id" AS "member_id" 1.236 FROM "issue" 1.237 JOIN "area" ON "issue"."area_id" = "area"."id" 1.238 JOIN "interest" ON "issue"."id" = "interest"."issue_id" 1.239 @@ -3001,20 +3026,6 @@ 1.240 FROM "direct_population_snapshot" 1.241 WHERE "issue_id" = "issue_id_p" 1.242 AND "event" = 'periodic' 1.243 - ), 1.244 - "vote_now" = ( 1.245 - SELECT coalesce(sum("weight"), 0) 1.246 - FROM "direct_interest_snapshot" 1.247 - WHERE "issue_id" = "issue_id_p" 1.248 - AND "event" = 'periodic' 1.249 - AND "voting_requested" = TRUE 1.250 - ), 1.251 - "vote_later" = ( 1.252 - SELECT coalesce(sum("weight"), 0) 1.253 - FROM "direct_interest_snapshot" 1.254 - WHERE "issue_id" = "issue_id_p" 1.255 - AND "event" = 'periodic' 1.256 - AND "voting_requested" = FALSE 1.257 ) 1.258 WHERE "id" = "issue_id_p"; 1.259 FOR "initiative_id_v" IN 1.260 @@ -3407,6 +3418,7 @@ 1.261 PERFORM "lock_issue"("issue_id_p"); 1.262 SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p"; 1.263 SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v"; 1.264 + -- consider delegations and auto-reject: 1.265 DELETE FROM "delegating_voter" 1.266 WHERE "issue_id" = "issue_id_p"; 1.267 DELETE FROM "direct_voter" 1.268 @@ -3492,6 +3504,7 @@ 1.269 FROM "initiative" WHERE "issue_id" = "issue_id_p"; 1.270 END LOOP; 1.271 PERFORM "add_vote_delegations"("issue_id_p"); 1.272 + -- set voter count and mark issue as being calculated: 1.273 UPDATE "issue" SET 1.274 "state" = 'calculation', 1.275 "closed" = now(), 1.276 @@ -3500,18 +3513,23 @@ 1.277 FROM "direct_voter" WHERE "issue_id" = "issue_id_p" 1.278 ) 1.279 WHERE "id" = "issue_id_p"; 1.280 + -- calculate "positive_votes", "negative_votes" and "attainable": 1.281 UPDATE "initiative" SET 1.282 "positive_votes" = "vote_counts"."positive_votes", 1.283 "negative_votes" = "vote_counts"."negative_votes", 1.284 - "agreed" = CASE WHEN "majority_strict" THEN 1.285 - "vote_counts"."positive_votes" * "majority_den" > 1.286 - "majority_num" * 1.287 - ("vote_counts"."positive_votes"+"vote_counts"."negative_votes") 1.288 - ELSE 1.289 - "vote_counts"."positive_votes" * "majority_den" >= 1.290 - "majority_num" * 1.291 - ("vote_counts"."positive_votes"+"vote_counts"."negative_votes") 1.292 - END 1.293 + "attainable" = 1.294 + CASE WHEN "majority_strict" THEN 1.295 + "vote_counts"."positive_votes" * "majority_den" > 1.296 + "majority_num" * 1.297 + ("vote_counts"."positive_votes"+"vote_counts"."negative_votes") 1.298 + ELSE 1.299 + "vote_counts"."positive_votes" * "majority_den" >= 1.300 + "majority_num" * 1.301 + ("vote_counts"."positive_votes"+"vote_counts"."negative_votes") 1.302 + END 1.303 + AND "vote_counts"."positive_votes" >= "policy"."majority_positive" 1.304 + AND "issue"."voter_count"-"vote_counts"."negative_votes" >= 1.305 + "policy"."majority_non_negative" 1.306 FROM 1.307 ( SELECT 1.308 "initiative"."id" AS "initiative_id", 1.309 @@ -3544,6 +3562,7 @@ 1.310 WHERE "vote_counts"."initiative_id" = "initiative"."id" 1.311 AND "issue"."id" = "initiative"."issue_id" 1.312 AND "policy"."id" = "issue"."policy_id"; 1.313 + -- materialize battle_view: 1.314 -- NOTE: "closed" column of issue must be set at this point 1.315 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p"; 1.316 INSERT INTO "battle" ( 1.317 @@ -3646,26 +3665,23 @@ 1.318 RETURNS VOID 1.319 LANGUAGE 'plpgsql' VOLATILE AS $$ 1.320 DECLARE 1.321 - "dimension_v" INTEGER; 1.322 - "vote_matrix" INT4[][]; -- absolute votes 1.323 - "matrix" INT8[][]; -- defeat strength / best paths 1.324 - "i" INTEGER; 1.325 - "j" INTEGER; 1.326 - "k" INTEGER; 1.327 - "battle_row" "battle"%ROWTYPE; 1.328 - "rank_ary" INT4[]; 1.329 - "rank_v" INT4; 1.330 - "done_v" INTEGER; 1.331 - "winners_ary" INTEGER[]; 1.332 - "initiative_id_v" "initiative"."id"%TYPE; 1.333 + "dimension_v" INTEGER; 1.334 + "vote_matrix" INT4[][]; -- absolute votes 1.335 + "matrix" INT8[][]; -- defeat strength / best paths 1.336 + "i" INTEGER; 1.337 + "j" INTEGER; 1.338 + "k" INTEGER; 1.339 + "battle_row" "battle"%ROWTYPE; 1.340 + "rank_ary" INT4[]; 1.341 + "rank_v" INT4; 1.342 + "done_v" INTEGER; 1.343 + "winners_ary" INTEGER[]; 1.344 + "initiative_id_v" "initiative"."id"%TYPE; 1.345 BEGIN 1.346 PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE; 1.347 - SELECT count(1) INTO "dimension_v" FROM "initiative" 1.348 - WHERE "issue_id" = "issue_id_p" AND "agreed"; 1.349 - IF "dimension_v" = 1 THEN 1.350 - UPDATE "initiative" SET "rank" = 1 1.351 - WHERE "issue_id" = "issue_id_p" AND "agreed"; 1.352 - ELSIF "dimension_v" > 1 THEN 1.353 + SELECT count(1) INTO "dimension_v" 1.354 + FROM "battle_participant" WHERE "issue_id" = "issue_id_p"; 1.355 + IF "dimension_v" > 1 THEN 1.356 -- Create "vote_matrix" with absolute number of votes in pairwise 1.357 -- comparison: 1.358 "vote_matrix" := "square_matrix_init_string"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4) 1.359 @@ -3673,7 +3689,9 @@ 1.360 "j" := 2; 1.361 FOR "battle_row" IN 1.362 SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p" 1.363 - ORDER BY "winning_initiative_id", "losing_initiative_id" 1.364 + ORDER BY 1.365 + "winning_initiative_id" NULLS LAST, 1.366 + "losing_initiative_id" NULLS LAST 1.367 LOOP 1.368 "vote_matrix"["i"]["j"] := "battle_row"."count"; 1.369 IF "j" = "dimension_v" THEN 1.370 @@ -3777,42 +3795,93 @@ 1.371 EXIT WHEN "done_v" = "dimension_v"; 1.372 "rank_v" := "rank_v" + 1; 1.373 END LOOP; 1.374 - -- write preliminary ranks: 1.375 + -- write preliminary results: 1.376 "i" := 1; 1.377 FOR "initiative_id_v" IN 1.378 SELECT "id" FROM "initiative" 1.379 - WHERE "issue_id" = "issue_id_p" AND "agreed" 1.380 + WHERE "issue_id" = "issue_id_p" AND "admitted" 1.381 ORDER BY "id" 1.382 LOOP 1.383 - UPDATE "initiative" SET "rank" = "rank_ary"["i"] 1.384 + UPDATE "initiative" SET 1.385 + "favored" = "rank_ary"["i"] < "rank_ary"["dimension_v"], 1.386 + "unfavored" = "rank_ary"["i"] > "rank_ary"["dimension_v"], 1.387 + "preliminary_rank" = "rank_ary"["i"], 1.388 + "disqualified" = "rank_ary"["i"] >= "rank_ary"["dimension_v"], 1.389 + "winner" = FALSE 1.390 WHERE "id" = "initiative_id_v"; 1.391 "i" := "i" + 1; 1.392 END LOOP; 1.393 - IF "i" != "dimension_v" + 1 THEN 1.394 + IF "i" != "dimension_v" THEN 1.395 RAISE EXCEPTION 'Wrong winner count (should not happen)'; 1.396 END IF; 1.397 - -- straighten ranks (start counting with 1, no equal ranks): 1.398 + -- remove possible gap in preliminary ranks: 1.399 + IF NOT EXISTS ( 1.400 + SELECT NULL FROM "initiative" 1.401 + WHERE "issue_id" = "issue_id_p" 1.402 + AND "favored"=FALSE AND "unfavored"=FALSE 1.403 + ) THEN 1.404 + UPDATE "initiative" SET "preliminary_rank" = "preliminary_rank" - 1 1.405 + WHERE "issue_id" = "issue_id_p" AND "unfavored"; 1.406 + END IF; 1.407 + -- disqualify certain initiatives to enforce a stable result: 1.408 + UPDATE "initiative" SET "disqualified" = TRUE 1.409 + FROM ( 1.410 + SELECT "losing_initiative"."id" AS "initiative_id" 1.411 + FROM "initiative" "losing_initiative" 1.412 + JOIN "initiative" "winning_initiative" 1.413 + ON "winning_initiative"."issue_id" = "issue_id_p" 1.414 + AND "winning_initiative"."admitted" 1.415 + JOIN "battle" "battle_win" 1.416 + ON "battle_win"."issue_id" = "issue_id_p" 1.417 + AND "battle_win"."winning_initiative_id" = "winning_initiative"."id" 1.418 + AND "battle_win"."losing_initiative_id" = "losing_initiative"."id" 1.419 + JOIN "battle" "battle_lose" 1.420 + ON "battle_lose"."issue_id" = "issue_id_p" 1.421 + AND "battle_lose"."losing_initiative_id" = "winning_initiative"."id" 1.422 + AND "battle_lose"."winning_initiative_id" = "losing_initiative"."id" 1.423 + WHERE "losing_initiative"."issue_id" = "issue_id_p" 1.424 + AND "losing_initiative"."admitted" 1.425 + AND "winning_initiative"."preliminary_rank" < 1.426 + "losing_initiative"."preliminary_rank" 1.427 + AND "battle_win"."count" > "battle_lose"."count" 1.428 + AND ( 1.429 + "battle_win"."count" > "winning_initiative"."positive_votes" OR 1.430 + "battle_lose"."count" < "losing_initiative"."negative_votes" ) 1.431 + ) AS "subquery" 1.432 + WHERE "id" = "subquery"."initiative_id"; 1.433 + -- calculate final ranks (start counting with 1, no equal ranks): 1.434 "rank_v" := 1; 1.435 FOR "initiative_id_v" IN 1.436 SELECT "id" FROM "initiative" 1.437 - WHERE "issue_id" = "issue_id_p" AND "rank" NOTNULL 1.438 - ORDER BY 1.439 - "rank", 1.440 - "vote_ratio"("positive_votes", "negative_votes") DESC, 1.441 - "id" 1.442 + WHERE "issue_id" = "issue_id_p" AND "admitted" 1.443 + ORDER BY "preliminary_rank", "id" 1.444 LOOP 1.445 - UPDATE "initiative" SET "rank" = "rank_v" 1.446 + UPDATE "initiative" SET "final_rank" = "rank_v" 1.447 WHERE "id" = "initiative_id_v"; 1.448 "rank_v" := "rank_v" + 1; 1.449 END LOOP; 1.450 + -- mark final winner: 1.451 + UPDATE "initiative" SET "winner" = TRUE 1.452 + FROM ( 1.453 + SELECT "id" AS "initiative_id" 1.454 + FROM "initiative" 1.455 + WHERE "issue_id" = "issue_id_p" 1.456 + AND "attainable" AND NOT "disqualified" 1.457 + ORDER BY "final_rank" 1.458 + LIMIT 1 1.459 + ) AS "subquery" 1.460 + WHERE "id" = "subquery"."initiative_id"; 1.461 END IF; 1.462 - -- mark issue as finished 1.463 + -- mark issue as finished: 1.464 UPDATE "issue" SET 1.465 "state" = 1.466 - CASE WHEN "dimension_v" = 0 THEN 1.467 + CASE WHEN EXISTS ( 1.468 + SELECT NULL FROM "initiative" 1.469 + WHERE "issue_id" = "issue_id_p" AND "winner" 1.470 + ) THEN 1.471 + 'finished_with_winner'::"issue_state" 1.472 + ELSE 1.473 'finished_without_winner'::"issue_state" 1.474 - ELSE 1.475 - 'finished_with_winner'::"issue_state" 1.476 END, 1.477 "ranks_available" = TRUE 1.478 WHERE "id" = "issue_id_p"; 1.479 @@ -3838,7 +3907,6 @@ 1.480 DECLARE 1.481 "issue_row" "issue"%ROWTYPE; 1.482 "policy_row" "policy"%ROWTYPE; 1.483 - "voting_requested_v" BOOLEAN; 1.484 BEGIN 1.485 PERFORM "lock_issue"("issue_id_p"); 1.486 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"; 1.487 @@ -3886,21 +3954,8 @@ 1.488 "issue_row"."accepted" NOTNULL AND 1.489 "issue_row"."half_frozen" ISNULL 1.490 THEN 1.491 - SELECT 1.492 - CASE 1.493 - WHEN "vote_now" * 2 > "issue_row"."population" THEN 1.494 - TRUE 1.495 - WHEN "vote_later" * 2 > "issue_row"."population" THEN 1.496 - FALSE 1.497 - ELSE NULL 1.498 - END 1.499 - INTO "voting_requested_v" 1.500 - FROM "issue" WHERE "id" = "issue_id_p"; 1.501 IF 1.502 - "voting_requested_v" OR ( 1.503 - "voting_requested_v" ISNULL AND 1.504 - now() >= "issue_row"."accepted" + "issue_row"."discussion_time" 1.505 - ) 1.506 + now() >= "issue_row"."accepted" + "issue_row"."discussion_time" 1.507 THEN 1.508 PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze'); 1.509 -- NOTE: "issue_row" used later 1.510 @@ -4018,6 +4073,7 @@ 1.511 FROM "issue" WHERE "id" = "issue_id_p" 1.512 FOR UPDATE; 1.513 IF "issue_row"."cleaned" ISNULL THEN 1.514 + -- TODO: might be broken due to new constraints! 1.515 UPDATE "issue" SET 1.516 "closed" = NULL, 1.517 "ranks_available" = FALSE