liquid_feedback_core
changeset 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 | 8422bca3a218 259dd3e127c0 |
children | ec1fdf2fc8c9 |
files | .hgtags core.sql |
line diff
1.1 --- a/.hgtags Thu Jun 02 01:23:49 2011 +0200 1.2 +++ b/.hgtags Thu Jun 02 01:25:08 2011 +0200 1.3 @@ -42,4 +42,5 @@ 1.4 fb9688f31740c4a95a9f2c63c114f64f812ddf05 v1.3.0 1.5 ea4928ccc8eb303fdaf268f57c5572099b89d0ef v1.3.1 1.6 0144b703b2610a4c93158427d5eff79cca3fca84 v1.4.0_rc1 1.7 +07cfc7c1ce3f9c7191a028d3fabf27881edcc8c7 v1.4.0_rc2 1.8 0fa3c4739ca75a0dd55c073903e274ec20fce137 v1.4.0_rc3
2.1 --- a/core.sql Thu Jun 02 01:23:49 2011 +0200 2.2 +++ b/core.sql Thu Jun 02 01:25:08 2011 +0200 2.3 @@ -7,7 +7,7 @@ 2.4 BEGIN; 2.5 2.6 CREATE VIEW "liquid_feedback_version" AS 2.7 - SELECT * FROM (VALUES ('1.4.0_rc2', 1, 4, -1)) 2.8 + SELECT * FROM (VALUES ('1.4.0_rc1', 1, 4, -1)) 2.9 AS "subquery"("string", "major", "minor", "revision"); 2.10 2.11 2.12 @@ -310,7 +310,9 @@ 2.13 "initiative_quorum_den" INT4 NOT NULL, 2.14 "majority_num" INT4 NOT NULL DEFAULT 1, 2.15 "majority_den" INT4 NOT NULL DEFAULT 2, 2.16 - "majority_strict" BOOLEAN NOT NULL DEFAULT TRUE ); 2.17 + "majority_strict" BOOLEAN NOT NULL DEFAULT TRUE, 2.18 + "majority_positive" INT4 NOT NULL DEFAULT 0, 2.19 + "majority_non_negative" INT4 NOT NULL DEFAULT 0 ); 2.20 CREATE INDEX "policy_active_idx" ON "policy" ("active"); 2.21 2.22 COMMENT ON TABLE "policy" IS 'Policies for a particular proceeding type (timelimits, quorum)'; 2.23 @@ -325,9 +327,11 @@ 2.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"'; 2.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'; 2.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'; 2.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'; 2.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'; 2.29 +COMMENT ON COLUMN "policy"."majority_num" IS 'Numerator of fraction of majority to be reached during voting by an initiative to be "attainable"'; 2.30 +COMMENT ON COLUMN "policy"."majority_den" IS 'Denominator of fraction of majority to be reached during voting by an initiative to be "attainable"'; 2.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.'; 2.32 +COMMENT ON COLUMN "policy"."majority_positive" IS 'Absolute number of "positive_votes" neccessary for an initiative to be "attainable".'; 2.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".'; 2.34 2.35 2.36 CREATE TABLE "unit" ( 2.37 @@ -441,8 +445,6 @@ 2.38 "snapshot" TIMESTAMPTZ, 2.39 "latest_snapshot_event" "snapshot_event", 2.40 "population" INT4, 2.41 - "vote_now" INT4, 2.42 - "vote_later" INT4, 2.43 "voter_count" INT4, 2.44 CONSTRAINT "valid_state" CHECK (( 2.45 ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR 2.46 @@ -502,11 +504,9 @@ 2.47 COMMENT ON COLUMN "issue"."discussion_time" IS 'Copied from "policy" table at creation of issue'; 2.48 COMMENT ON COLUMN "issue"."verification_time" IS 'Copied from "policy" table at creation of issue'; 2.49 COMMENT ON COLUMN "issue"."voting_time" IS 'Copied from "policy" table at creation of issue'; 2.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'; 2.51 +COMMENT ON COLUMN "issue"."snapshot" IS 'Point in time, when snapshot tables have been updated and "population" and *_count values were precalculated'; 2.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'; 2.53 COMMENT ON COLUMN "issue"."population" IS 'Sum of "weight" column in table "direct_population_snapshot"'; 2.54 -COMMENT ON COLUMN "issue"."vote_now" IS 'Number of votes in favor of voting now, as calculated from table "direct_interest_snapshot"'; 2.55 -COMMENT ON COLUMN "issue"."vote_later" IS 'Number of votes against voting now, as calculated from table "direct_interest_snapshot"'; 2.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'; 2.57 2.58 2.59 @@ -537,8 +537,13 @@ 2.60 "satisfied_informed_supporter_count" INT4, 2.61 "positive_votes" INT4, 2.62 "negative_votes" INT4, 2.63 - "agreed" BOOLEAN, 2.64 - "rank" INT4, 2.65 + "attainable" BOOLEAN, 2.66 + "favored" BOOLEAN, 2.67 + "unfavored" BOOLEAN, 2.68 + "preliminary_rank" INT4, 2.69 + "final_rank" INT4, 2.70 + "disqualified" BOOLEAN, 2.71 + "winner" BOOLEAN, 2.72 "text_search_data" TSVECTOR, 2.73 CONSTRAINT "all_or_none_of_revoked_and_revoked_by_member_id_must_be_null" 2.74 CHECK ("revoked" NOTNULL = "revoked_by_member_id" NOTNULL), 2.75 @@ -546,12 +551,13 @@ 2.76 CHECK ("revoked" NOTNULL OR "suggested_initiative_id" ISNULL), 2.77 CONSTRAINT "revoked_initiatives_cant_be_admitted" 2.78 CHECK ("revoked" ISNULL OR "admitted" ISNULL), 2.79 - CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results" 2.80 - CHECK (("admitted" NOTNULL AND "admitted" = TRUE) OR ("positive_votes" ISNULL AND "negative_votes" ISNULL AND "agreed" ISNULL)), 2.81 - CONSTRAINT "all_or_none_of_positive_votes_negative_votes_and_agreed_must_be_null" 2.82 - CHECK ("positive_votes" NOTNULL = "negative_votes" NOTNULL AND "positive_votes" NOTNULL = "agreed" NOTNULL), 2.83 - CONSTRAINT "non_agreed_initiatives_cant_get_a_rank" 2.84 - CHECK (("agreed" NOTNULL AND "agreed" = TRUE) OR "rank" ISNULL) ); 2.85 + CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results" CHECK ( 2.86 + ( "admitted" NOTNULL AND "admitted" = TRUE ) OR 2.87 + ( "positive_votes" ISNULL AND "negative_votes" ISNULL AND 2.88 + "attainable" ISNULL AND "favored" ISNULL AND "unfavored" ISNULL AND 2.89 + "disqualified" ISNULL AND "preliminary_rank" ISNULL AND 2.90 + "final_rank" ISNULL AND "winner" ISNULL ) ), 2.91 + CONSTRAINT "favored_excludes_unfavored" CHECK (NOT ("favored" AND "unfavored")) ); 2.92 CREATE INDEX "initiative_created_idx" ON "initiative" ("created"); 2.93 CREATE INDEX "initiative_revoked_idx" ON "initiative" ("revoked"); 2.94 CREATE INDEX "initiative_text_search_data_idx" ON "initiative" USING gin ("text_search_data"); 2.95 @@ -571,22 +577,33 @@ 2.96 COMMENT ON COLUMN "initiative"."informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"'; 2.97 COMMENT ON COLUMN "initiative"."satisfied_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"'; 2.98 COMMENT ON COLUMN "initiative"."satisfied_informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"'; 2.99 -COMMENT ON COLUMN "initiative"."positive_votes" IS 'Calculated from table "direct_voter"'; 2.100 -COMMENT ON COLUMN "initiative"."negative_votes" IS 'Calculated from table "direct_voter"'; 2.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"'; 2.102 -COMMENT ON COLUMN "initiative"."rank" IS 'Rank of approved initiatives (winner is 1), calculated from table "direct_voter"'; 2.103 +COMMENT ON COLUMN "initiative"."positive_votes" IS 'Calculated from table "direct_voter"'; 2.104 +COMMENT ON COLUMN "initiative"."negative_votes" IS 'Calculated from table "direct_voter"'; 2.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"'; 2.106 +COMMENT ON COLUMN "initiative"."favored" IS 'TRUE, if initiative has a schulze-ranking better than the status quo (without tie-breaking)'; 2.107 +COMMENT ON COLUMN "initiative"."unfavored" IS 'TRUE, if initiative has a schulze-ranking worse than the status quo (without tie-breaking)'; 2.108 +COMMENT ON COLUMN "initiative"."preliminary_rank" IS 'Schulze-Ranking without tie-breaking'; 2.109 +COMMENT ON COLUMN "initiative"."final_rank" IS 'Schulze-Ranking after tie-breaking'; 2.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'; 2.111 +COMMENT ON COLUMN "initiative"."winner" IS 'TRUE, if initiative is final winner (best ranked initiative being "attainable" and not "disqualified")'; 2.112 2.113 2.114 CREATE TABLE "battle" ( 2.115 - PRIMARY KEY ("issue_id", "winning_initiative_id", "losing_initiative_id"), 2.116 - "issue_id" INT4, 2.117 + "issue_id" INT4 NOT NULL, 2.118 "winning_initiative_id" INT4, 2.119 FOREIGN KEY ("issue_id", "winning_initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE, 2.120 "losing_initiative_id" INT4, 2.121 FOREIGN KEY ("issue_id", "losing_initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE, 2.122 - "count" INT4 NOT NULL); 2.123 - 2.124 -COMMENT ON TABLE "battle" IS 'Number of members preferring one initiative to another; Filled by "battle_view" when closing an issue'; 2.125 + "count" INT4 NOT NULL, 2.126 + CONSTRAINT "initiative_ids_not_equal" CHECK ( 2.127 + "winning_initiative_id" != "losing_initiative_id" OR 2.128 + ( ("winning_initiative_id" NOTNULL AND "losing_initiative_id" ISNULL) OR 2.129 + ("winning_initiative_id" ISNULL AND "losing_initiative_id" NOTNULL) ) ) ); 2.130 +CREATE UNIQUE INDEX "battle_winning_losing_idx" ON "battle" ("issue_id", "winning_initiative_id", "losing_initiative_id"); 2.131 +CREATE UNIQUE INDEX "battle_winning_null_idx" ON "battle" ("issue_id", "winning_initiative_id") WHERE "losing_initiative_id" ISNULL; 2.132 +CREATE UNIQUE INDEX "battle_null_losing_idx" ON "battle" ("issue_id", "losing_initiative_id") WHERE "winning_initiative_id" ISNULL; 2.133 + 2.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'; 2.135 2.136 2.137 CREATE TABLE "ignored_initiative" ( 2.138 @@ -731,14 +748,12 @@ 2.139 PRIMARY KEY ("issue_id", "member_id"), 2.140 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 2.141 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 2.142 - "autoreject" BOOLEAN, 2.143 - "voting_requested" BOOLEAN ); 2.144 + "autoreject" BOOLEAN ); 2.145 CREATE INDEX "interest_member_id_idx" ON "interest" ("member_id"); 2.146 2.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.'; 2.148 2.149 COMMENT ON COLUMN "interest"."autoreject" IS 'TRUE = member votes against all initiatives in case of not explicitly taking part in the voting procedure'; 2.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'; 2.151 2.152 2.153 CREATE TABLE "initiator" ( 2.154 @@ -854,15 +869,13 @@ 2.155 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 2.156 "event" "snapshot_event", 2.157 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT, 2.158 - "weight" INT4, 2.159 - "voting_requested" BOOLEAN ); 2.160 + "weight" INT4 ); 2.161 CREATE INDEX "direct_interest_snapshot_member_id_idx" ON "direct_interest_snapshot" ("member_id"); 2.162 2.163 COMMENT ON TABLE "direct_interest_snapshot" IS 'Snapshot of active members having an "interest" in the "issue"'; 2.164 2.165 COMMENT ON COLUMN "direct_interest_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details'; 2.166 COMMENT ON COLUMN "direct_interest_snapshot"."weight" IS 'Weight of member (1 or higher) according to "delegating_interest_snapshot"'; 2.167 -COMMENT ON COLUMN "direct_interest_snapshot"."voting_requested" IS 'Copied from column "voting_requested" of table "interest"'; 2.168 2.169 2.170 CREATE TABLE "delegating_interest_snapshot" ( 2.171 @@ -1870,6 +1883,18 @@ 2.172 COMMENT ON VIEW "critical_opinion" IS 'Opinions currently causing dissatisfaction'; 2.173 2.174 2.175 +CREATE VIEW "battle_participant" AS 2.176 + SELECT "initiative"."id", "initiative"."issue_id" 2.177 + FROM "issue" JOIN "initiative" 2.178 + ON "issue"."id" = "initiative"."issue_id" 2.179 + WHERE "initiative"."admitted" 2.180 + UNION ALL 2.181 + SELECT NULL, "id" AS "issue_id" 2.182 + FROM "issue"; 2.183 + 2.184 +COMMENT ON VIEW "battle_participant" IS 'Helper view for "battle_view" containing admitted initiatives plus virtual "status-quo" initiative denoted by NULL reference'; 2.185 + 2.186 + 2.187 CREATE VIEW "battle_view" AS 2.188 SELECT 2.189 "issue"."id" AS "issue_id", 2.190 @@ -1884,12 +1909,10 @@ 2.191 FROM "issue" 2.192 LEFT JOIN "direct_voter" 2.193 ON "issue"."id" = "direct_voter"."issue_id" 2.194 - JOIN "initiative" AS "winning_initiative" 2.195 + JOIN "battle_participant" AS "winning_initiative" 2.196 ON "issue"."id" = "winning_initiative"."issue_id" 2.197 - AND "winning_initiative"."agreed" 2.198 - JOIN "initiative" AS "losing_initiative" 2.199 + JOIN "battle_participant" AS "losing_initiative" 2.200 ON "issue"."id" = "losing_initiative"."issue_id" 2.201 - AND "losing_initiative"."agreed" 2.202 LEFT JOIN "vote" AS "better_vote" 2.203 ON "direct_voter"."member_id" = "better_vote"."member_id" 2.204 AND "winning_initiative"."id" = "better_vote"."initiative_id" 2.205 @@ -1898,13 +1921,16 @@ 2.206 AND "losing_initiative"."id" = "worse_vote"."initiative_id" 2.207 WHERE "issue"."closed" NOTNULL 2.208 AND "issue"."cleaned" ISNULL 2.209 - AND "winning_initiative"."id" != "losing_initiative"."id" 2.210 + AND ( 2.211 + "winning_initiative"."id" != "losing_initiative"."id" OR 2.212 + ( ("winning_initiative"."id" NOTNULL AND "losing_initiative"."id" ISNULL) OR 2.213 + ("winning_initiative"."id" ISNULL AND "losing_initiative"."id" NOTNULL) ) ) 2.214 GROUP BY 2.215 "issue"."id", 2.216 "winning_initiative"."id", 2.217 "losing_initiative"."id"; 2.218 2.219 -COMMENT ON VIEW "battle_view" IS 'Number of members preferring one initiative to another; Used to fill "battle" table'; 2.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'; 2.221 2.222 2.223 CREATE VIEW "expired_session" AS 2.224 @@ -2918,12 +2944,11 @@ 2.225 WHERE "issue_id" = "issue_id_p" 2.226 AND "event" = 'periodic'; 2.227 INSERT INTO "direct_interest_snapshot" 2.228 - ("issue_id", "event", "member_id", "voting_requested") 2.229 + ("issue_id", "event", "member_id") 2.230 SELECT 2.231 "issue_id_p" AS "issue_id", 2.232 'periodic' AS "event", 2.233 - "member"."id" AS "member_id", 2.234 - "interest"."voting_requested" 2.235 + "member"."id" AS "member_id" 2.236 FROM "issue" 2.237 JOIN "area" ON "issue"."area_id" = "area"."id" 2.238 JOIN "interest" ON "issue"."id" = "interest"."issue_id" 2.239 @@ -3001,20 +3026,6 @@ 2.240 FROM "direct_population_snapshot" 2.241 WHERE "issue_id" = "issue_id_p" 2.242 AND "event" = 'periodic' 2.243 - ), 2.244 - "vote_now" = ( 2.245 - SELECT coalesce(sum("weight"), 0) 2.246 - FROM "direct_interest_snapshot" 2.247 - WHERE "issue_id" = "issue_id_p" 2.248 - AND "event" = 'periodic' 2.249 - AND "voting_requested" = TRUE 2.250 - ), 2.251 - "vote_later" = ( 2.252 - SELECT coalesce(sum("weight"), 0) 2.253 - FROM "direct_interest_snapshot" 2.254 - WHERE "issue_id" = "issue_id_p" 2.255 - AND "event" = 'periodic' 2.256 - AND "voting_requested" = FALSE 2.257 ) 2.258 WHERE "id" = "issue_id_p"; 2.259 FOR "initiative_id_v" IN 2.260 @@ -3407,6 +3418,7 @@ 2.261 PERFORM "lock_issue"("issue_id_p"); 2.262 SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p"; 2.263 SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v"; 2.264 + -- consider delegations and auto-reject: 2.265 DELETE FROM "delegating_voter" 2.266 WHERE "issue_id" = "issue_id_p"; 2.267 DELETE FROM "direct_voter" 2.268 @@ -3492,6 +3504,7 @@ 2.269 FROM "initiative" WHERE "issue_id" = "issue_id_p"; 2.270 END LOOP; 2.271 PERFORM "add_vote_delegations"("issue_id_p"); 2.272 + -- set voter count and mark issue as being calculated: 2.273 UPDATE "issue" SET 2.274 "state" = 'calculation', 2.275 "closed" = now(), 2.276 @@ -3500,18 +3513,23 @@ 2.277 FROM "direct_voter" WHERE "issue_id" = "issue_id_p" 2.278 ) 2.279 WHERE "id" = "issue_id_p"; 2.280 + -- calculate "positive_votes", "negative_votes" and "attainable": 2.281 UPDATE "initiative" SET 2.282 "positive_votes" = "vote_counts"."positive_votes", 2.283 "negative_votes" = "vote_counts"."negative_votes", 2.284 - "agreed" = CASE WHEN "majority_strict" THEN 2.285 - "vote_counts"."positive_votes" * "majority_den" > 2.286 - "majority_num" * 2.287 - ("vote_counts"."positive_votes"+"vote_counts"."negative_votes") 2.288 - ELSE 2.289 - "vote_counts"."positive_votes" * "majority_den" >= 2.290 - "majority_num" * 2.291 - ("vote_counts"."positive_votes"+"vote_counts"."negative_votes") 2.292 - END 2.293 + "attainable" = 2.294 + CASE WHEN "majority_strict" THEN 2.295 + "vote_counts"."positive_votes" * "majority_den" > 2.296 + "majority_num" * 2.297 + ("vote_counts"."positive_votes"+"vote_counts"."negative_votes") 2.298 + ELSE 2.299 + "vote_counts"."positive_votes" * "majority_den" >= 2.300 + "majority_num" * 2.301 + ("vote_counts"."positive_votes"+"vote_counts"."negative_votes") 2.302 + END 2.303 + AND "vote_counts"."positive_votes" >= "policy"."majority_positive" 2.304 + AND "issue"."voter_count"-"vote_counts"."negative_votes" >= 2.305 + "policy"."majority_non_negative" 2.306 FROM 2.307 ( SELECT 2.308 "initiative"."id" AS "initiative_id", 2.309 @@ -3544,6 +3562,7 @@ 2.310 WHERE "vote_counts"."initiative_id" = "initiative"."id" 2.311 AND "issue"."id" = "initiative"."issue_id" 2.312 AND "policy"."id" = "issue"."policy_id"; 2.313 + -- materialize battle_view: 2.314 -- NOTE: "closed" column of issue must be set at this point 2.315 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p"; 2.316 INSERT INTO "battle" ( 2.317 @@ -3646,26 +3665,23 @@ 2.318 RETURNS VOID 2.319 LANGUAGE 'plpgsql' VOLATILE AS $$ 2.320 DECLARE 2.321 - "dimension_v" INTEGER; 2.322 - "vote_matrix" INT4[][]; -- absolute votes 2.323 - "matrix" INT8[][]; -- defeat strength / best paths 2.324 - "i" INTEGER; 2.325 - "j" INTEGER; 2.326 - "k" INTEGER; 2.327 - "battle_row" "battle"%ROWTYPE; 2.328 - "rank_ary" INT4[]; 2.329 - "rank_v" INT4; 2.330 - "done_v" INTEGER; 2.331 - "winners_ary" INTEGER[]; 2.332 - "initiative_id_v" "initiative"."id"%TYPE; 2.333 + "dimension_v" INTEGER; 2.334 + "vote_matrix" INT4[][]; -- absolute votes 2.335 + "matrix" INT8[][]; -- defeat strength / best paths 2.336 + "i" INTEGER; 2.337 + "j" INTEGER; 2.338 + "k" INTEGER; 2.339 + "battle_row" "battle"%ROWTYPE; 2.340 + "rank_ary" INT4[]; 2.341 + "rank_v" INT4; 2.342 + "done_v" INTEGER; 2.343 + "winners_ary" INTEGER[]; 2.344 + "initiative_id_v" "initiative"."id"%TYPE; 2.345 BEGIN 2.346 PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE; 2.347 - SELECT count(1) INTO "dimension_v" FROM "initiative" 2.348 - WHERE "issue_id" = "issue_id_p" AND "agreed"; 2.349 - IF "dimension_v" = 1 THEN 2.350 - UPDATE "initiative" SET "rank" = 1 2.351 - WHERE "issue_id" = "issue_id_p" AND "agreed"; 2.352 - ELSIF "dimension_v" > 1 THEN 2.353 + SELECT count(1) INTO "dimension_v" 2.354 + FROM "battle_participant" WHERE "issue_id" = "issue_id_p"; 2.355 + IF "dimension_v" > 1 THEN 2.356 -- Create "vote_matrix" with absolute number of votes in pairwise 2.357 -- comparison: 2.358 "vote_matrix" := "square_matrix_init_string"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4) 2.359 @@ -3673,7 +3689,9 @@ 2.360 "j" := 2; 2.361 FOR "battle_row" IN 2.362 SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p" 2.363 - ORDER BY "winning_initiative_id", "losing_initiative_id" 2.364 + ORDER BY 2.365 + "winning_initiative_id" NULLS LAST, 2.366 + "losing_initiative_id" NULLS LAST 2.367 LOOP 2.368 "vote_matrix"["i"]["j"] := "battle_row"."count"; 2.369 IF "j" = "dimension_v" THEN 2.370 @@ -3777,42 +3795,93 @@ 2.371 EXIT WHEN "done_v" = "dimension_v"; 2.372 "rank_v" := "rank_v" + 1; 2.373 END LOOP; 2.374 - -- write preliminary ranks: 2.375 + -- write preliminary results: 2.376 "i" := 1; 2.377 FOR "initiative_id_v" IN 2.378 SELECT "id" FROM "initiative" 2.379 - WHERE "issue_id" = "issue_id_p" AND "agreed" 2.380 + WHERE "issue_id" = "issue_id_p" AND "admitted" 2.381 ORDER BY "id" 2.382 LOOP 2.383 - UPDATE "initiative" SET "rank" = "rank_ary"["i"] 2.384 + UPDATE "initiative" SET 2.385 + "favored" = "rank_ary"["i"] < "rank_ary"["dimension_v"], 2.386 + "unfavored" = "rank_ary"["i"] > "rank_ary"["dimension_v"], 2.387 + "preliminary_rank" = "rank_ary"["i"], 2.388 + "disqualified" = "rank_ary"["i"] >= "rank_ary"["dimension_v"], 2.389 + "winner" = FALSE 2.390 WHERE "id" = "initiative_id_v"; 2.391 "i" := "i" + 1; 2.392 END LOOP; 2.393 - IF "i" != "dimension_v" + 1 THEN 2.394 + IF "i" != "dimension_v" THEN 2.395 RAISE EXCEPTION 'Wrong winner count (should not happen)'; 2.396 END IF; 2.397 - -- straighten ranks (start counting with 1, no equal ranks): 2.398 + -- remove possible gap in preliminary ranks: 2.399 + IF NOT EXISTS ( 2.400 + SELECT NULL FROM "initiative" 2.401 + WHERE "issue_id" = "issue_id_p" 2.402 + AND "favored"=FALSE AND "unfavored"=FALSE 2.403 + ) THEN 2.404 + UPDATE "initiative" SET "preliminary_rank" = "preliminary_rank" - 1 2.405 + WHERE "issue_id" = "issue_id_p" AND "unfavored"; 2.406 + END IF; 2.407 + -- disqualify certain initiatives to enforce a stable result: 2.408 + UPDATE "initiative" SET "disqualified" = TRUE 2.409 + FROM ( 2.410 + SELECT "losing_initiative"."id" AS "initiative_id" 2.411 + FROM "initiative" "losing_initiative" 2.412 + JOIN "initiative" "winning_initiative" 2.413 + ON "winning_initiative"."issue_id" = "issue_id_p" 2.414 + AND "winning_initiative"."admitted" 2.415 + JOIN "battle" "battle_win" 2.416 + ON "battle_win"."issue_id" = "issue_id_p" 2.417 + AND "battle_win"."winning_initiative_id" = "winning_initiative"."id" 2.418 + AND "battle_win"."losing_initiative_id" = "losing_initiative"."id" 2.419 + JOIN "battle" "battle_lose" 2.420 + ON "battle_lose"."issue_id" = "issue_id_p" 2.421 + AND "battle_lose"."losing_initiative_id" = "winning_initiative"."id" 2.422 + AND "battle_lose"."winning_initiative_id" = "losing_initiative"."id" 2.423 + WHERE "losing_initiative"."issue_id" = "issue_id_p" 2.424 + AND "losing_initiative"."admitted" 2.425 + AND "winning_initiative"."preliminary_rank" < 2.426 + "losing_initiative"."preliminary_rank" 2.427 + AND "battle_win"."count" > "battle_lose"."count" 2.428 + AND ( 2.429 + "battle_win"."count" > "winning_initiative"."positive_votes" OR 2.430 + "battle_lose"."count" < "losing_initiative"."negative_votes" ) 2.431 + ) AS "subquery" 2.432 + WHERE "id" = "subquery"."initiative_id"; 2.433 + -- calculate final ranks (start counting with 1, no equal ranks): 2.434 "rank_v" := 1; 2.435 FOR "initiative_id_v" IN 2.436 SELECT "id" FROM "initiative" 2.437 - WHERE "issue_id" = "issue_id_p" AND "rank" NOTNULL 2.438 - ORDER BY 2.439 - "rank", 2.440 - "vote_ratio"("positive_votes", "negative_votes") DESC, 2.441 - "id" 2.442 + WHERE "issue_id" = "issue_id_p" AND "admitted" 2.443 + ORDER BY "preliminary_rank", "id" 2.444 LOOP 2.445 - UPDATE "initiative" SET "rank" = "rank_v" 2.446 + UPDATE "initiative" SET "final_rank" = "rank_v" 2.447 WHERE "id" = "initiative_id_v"; 2.448 "rank_v" := "rank_v" + 1; 2.449 END LOOP; 2.450 + -- mark final winner: 2.451 + UPDATE "initiative" SET "winner" = TRUE 2.452 + FROM ( 2.453 + SELECT "id" AS "initiative_id" 2.454 + FROM "initiative" 2.455 + WHERE "issue_id" = "issue_id_p" 2.456 + AND "attainable" AND NOT "disqualified" 2.457 + ORDER BY "final_rank" 2.458 + LIMIT 1 2.459 + ) AS "subquery" 2.460 + WHERE "id" = "subquery"."initiative_id"; 2.461 END IF; 2.462 - -- mark issue as finished 2.463 + -- mark issue as finished: 2.464 UPDATE "issue" SET 2.465 "state" = 2.466 - CASE WHEN "dimension_v" = 0 THEN 2.467 + CASE WHEN EXISTS ( 2.468 + SELECT NULL FROM "initiative" 2.469 + WHERE "issue_id" = "issue_id_p" AND "winner" 2.470 + ) THEN 2.471 + 'finished_with_winner'::"issue_state" 2.472 + ELSE 2.473 'finished_without_winner'::"issue_state" 2.474 - ELSE 2.475 - 'finished_with_winner'::"issue_state" 2.476 END, 2.477 "ranks_available" = TRUE 2.478 WHERE "id" = "issue_id_p"; 2.479 @@ -3838,7 +3907,6 @@ 2.480 DECLARE 2.481 "issue_row" "issue"%ROWTYPE; 2.482 "policy_row" "policy"%ROWTYPE; 2.483 - "voting_requested_v" BOOLEAN; 2.484 BEGIN 2.485 PERFORM "lock_issue"("issue_id_p"); 2.486 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"; 2.487 @@ -3886,21 +3954,8 @@ 2.488 "issue_row"."accepted" NOTNULL AND 2.489 "issue_row"."half_frozen" ISNULL 2.490 THEN 2.491 - SELECT 2.492 - CASE 2.493 - WHEN "vote_now" * 2 > "issue_row"."population" THEN 2.494 - TRUE 2.495 - WHEN "vote_later" * 2 > "issue_row"."population" THEN 2.496 - FALSE 2.497 - ELSE NULL 2.498 - END 2.499 - INTO "voting_requested_v" 2.500 - FROM "issue" WHERE "id" = "issue_id_p"; 2.501 IF 2.502 - "voting_requested_v" OR ( 2.503 - "voting_requested_v" ISNULL AND 2.504 - now() >= "issue_row"."accepted" + "issue_row"."discussion_time" 2.505 - ) 2.506 + now() >= "issue_row"."accepted" + "issue_row"."discussion_time" 2.507 THEN 2.508 PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze'); 2.509 -- NOTE: "issue_row" used later 2.510 @@ -4018,6 +4073,7 @@ 2.511 FROM "issue" WHERE "id" = "issue_id_p" 2.512 FOR UPDATE; 2.513 IF "issue_row"."cleaned" ISNULL THEN 2.514 + -- TODO: might be broken due to new constraints! 2.515 UPDATE "issue" SET 2.516 "closed" = NULL, 2.517 "ranks_available" = FALSE
3.1 --- a/demo.sql Thu Jun 02 01:23:49 2011 +0200 3.2 +++ b/demo.sql Thu Jun 02 01:25:08 2011 +0200 3.3 @@ -92,7 +92,7 @@ 3.4 (4, 6, FALSE), 3.5 (4, 9, FALSE), 3.6 (4, 13, FALSE), 3.7 - (4, 22, TRUE); 3.8 + (4, 22, FALSE); 3.9 3.10 -- global delegations 3.11 INSERT INTO "delegation" 3.12 @@ -186,6 +186,53 @@ 3.13 INSERT INTO "opinion" ("member_id", "suggestion_id", "degree", "fulfilled") VALUES 3.14 (19, 1, 2, FALSE); 3.15 3.16 +INSERT INTO "issue" ("area_id", "policy_id") VALUES 3.17 + (4, 1); -- id 2 3.18 + 3.19 +INSERT INTO "initiative" ("issue_id", "name") VALUES 3.20 + (2, 'Initiative A'), -- id 8 3.21 + (2, 'Initiative B'), -- id 9 3.22 + (2, 'Initiative C'), -- id 10 3.23 + (2, 'Initiative D'); -- id 11 3.24 + 3.25 +INSERT INTO "draft" ("initiative_id", "author_id", "content") VALUES 3.26 + ( 8, 1, 'Lorem ipsum...'), -- id 8 3.27 + ( 9, 2, 'Lorem ipsum...'), -- id 9 3.28 + (10, 3, 'Lorem ipsum...'), -- id 10 3.29 + (11, 4, 'Lorem ipsum...'); -- id 11 3.30 + 3.31 +INSERT INTO "initiator" ("initiative_id", "member_id") VALUES 3.32 + ( 8, 1), 3.33 + ( 9, 2), 3.34 + (10, 3), 3.35 + (11, 4); 3.36 + 3.37 +INSERT INTO "supporter" ("member_id", "initiative_id", "draft_id") VALUES 3.38 + (1, 8, 8), 3.39 + (1, 9, 9), 3.40 + (1, 10, 10), 3.41 + (1, 11, 11), 3.42 + (2, 8, 8), 3.43 + (2, 9, 9), 3.44 + (2, 10, 10), 3.45 + (2, 11, 11), 3.46 + (3, 8, 8), 3.47 + (3, 9, 9), 3.48 + (3, 10, 10), 3.49 + (3, 11, 11), 3.50 + (4, 8, 8), 3.51 + (4, 9, 9), 3.52 + (4, 10, 10), 3.53 + (4, 11, 11), 3.54 + (5, 8, 8), 3.55 + (5, 9, 9), 3.56 + (5, 10, 10), 3.57 + (5, 11, 11), 3.58 + (6, 8, 8), 3.59 + (6, 9, 9), 3.60 + (6, 10, 10), 3.61 + (6, 11, 11); 3.62 + 3.63 SELECT "time_warp"(); 3.64 SELECT "time_warp"(); 3.65 SELECT "time_warp"(); 3.66 @@ -247,6 +294,110 @@ 3.67 (20, 1, 5, 1), 3.68 (21, 1, 5, -1); 3.69 3.70 +INSERT INTO "direct_voter" ("member_id", "issue_id") VALUES 3.71 + ( 1, 2), 3.72 + ( 2, 2), 3.73 + ( 3, 2), 3.74 + ( 4, 2), 3.75 + ( 5, 2), 3.76 + ( 6, 2), 3.77 + ( 7, 2), 3.78 + ( 8, 2), 3.79 + ( 9, 2), 3.80 + (10, 2), 3.81 + (11, 2), 3.82 + (12, 2), 3.83 + (13, 2), 3.84 + (14, 2), 3.85 + (15, 2), 3.86 + (16, 2), 3.87 + (17, 2), 3.88 + (18, 2), 3.89 + (19, 2), 3.90 + (20, 2); 3.91 + 3.92 +INSERT INTO "vote" ("member_id", "issue_id", "initiative_id", "grade") VALUES 3.93 + ( 1, 2, 8, 3), 3.94 + ( 1, 2, 9, 4), 3.95 + ( 1, 2, 10, 2), 3.96 + ( 1, 2, 11, 1), 3.97 + ( 2, 2, 8, 3), 3.98 + ( 2, 2, 9, 4), 3.99 + ( 2, 2, 10, 2), 3.100 + ( 2, 2, 11, 1), 3.101 + ( 3, 2, 8, 4), 3.102 + ( 3, 2, 9, 3), 3.103 + ( 3, 2, 10, 2), 3.104 + ( 3, 2, 11, 1), 3.105 + ( 4, 2, 8, 4), 3.106 + ( 4, 2, 9, 3), 3.107 + ( 4, 2, 10, 2), 3.108 + ( 4, 2, 11, 1), 3.109 + ( 5, 2, 8, 4), 3.110 + ( 5, 2, 9, 3), 3.111 + ( 5, 2, 10, 2), 3.112 + ( 5, 2, 11, 1), 3.113 + ( 6, 2, 8, 4), 3.114 + ( 6, 2, 9, 3), 3.115 + ( 6, 2, 10, 2), 3.116 + ( 6, 2, 11, 1), 3.117 + ( 7, 2, 8, 4), 3.118 + ( 7, 2, 9, 3), 3.119 + ( 7, 2, 10, 2), 3.120 + ( 7, 2, 11, 1), 3.121 + ( 8, 2, 8, 4), 3.122 + ( 8, 2, 9, 3), 3.123 + ( 8, 2, 10, 2), 3.124 + ( 8, 2, 11, 1), 3.125 + ( 9, 2, 8, -1), 3.126 + ( 9, 2, 9, 1), 3.127 + ( 9, 2, 10, 3), 3.128 + ( 9, 2, 11, 2), 3.129 + (10, 2, 8, -1), 3.130 + (10, 2, 9, 1), 3.131 + (10, 2, 10, 3), 3.132 + (10, 2, 11, 2), 3.133 + (11, 2, 8, -1), 3.134 + (11, 2, 9, 1), 3.135 + (11, 2, 10, 3), 3.136 + (11, 2, 11, 2), 3.137 + (12, 2, 8, -1), 3.138 + (12, 2, 9, 1), 3.139 + (12, 2, 10, 3), 3.140 + (12, 2, 11, 2), 3.141 + (13, 2, 8, -1), 3.142 + (13, 2, 9, 1), 3.143 + (13, 2, 10, 3), 3.144 + (13, 2, 11, 2), 3.145 + (14, 2, 8, -1), 3.146 + (14, 2, 9, 1), 3.147 + (14, 2, 10, 3), 3.148 + (14, 2, 11, 2), 3.149 + (15, 2, 8, -1), 3.150 + (15, 2, 9, -3), 3.151 + (15, 2, 10, -4), 3.152 + (15, 2, 11, -2), 3.153 + (16, 2, 8, -1), 3.154 + (16, 2, 9, -3), 3.155 + (16, 2, 10, -4), 3.156 + (16, 2, 11, -2), 3.157 + (17, 2, 8, -1), 3.158 + (17, 2, 9, -3), 3.159 + (17, 2, 10, -4), 3.160 + (17, 2, 11, -2), 3.161 + (18, 2, 8, -1), 3.162 + (18, 2, 9, 1), 3.163 + (18, 2, 10, -2), 3.164 + (18, 2, 11, 2), 3.165 + (19, 2, 8, -1), 3.166 + (19, 2, 9, 1), 3.167 + (19, 2, 10, -2), 3.168 + (19, 2, 11, 2), 3.169 + (20, 2, 8, 1), 3.170 + (20, 2, 9, 2), 3.171 + (20, 2, 10, -1), 3.172 + (20, 2, 11, 3); 3.173 + 3.174 SELECT "time_warp"(); 3.175 3.176 DROP FUNCTION "time_warp"();