liquid_feedback_core
annotate update/core-update.v1.4.0_rc2-v1.4.0_rc3.sql @ 378:e88d0606891f
Bugfix regarding "proportional_order" of suggestions:
Use NULL values explicitly to be sorted last
(includes new suggestions as well as suggestions without any individual rankings)
Use NULL values explicitly to be sorted last
(includes new suggestions as well as suggestions without any individual rankings)
author | jbe |
---|---|
date | Mon Mar 18 09:36:21 2013 +0100 (2013-03-18) |
parents | 1be788313b84 |
children |
rev | line source |
---|---|
jbe@164 | 1 BEGIN; |
jbe@164 | 2 |
jbe@164 | 3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS |
jbe@164 | 4 SELECT * FROM (VALUES ('1.4.0_rc3', 1, 4, -1)) |
jbe@164 | 5 AS "subquery"("string", "major", "minor", "revision"); |
jbe@164 | 6 |
jbe@164 | 7 CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE) |
jbe@164 | 8 RETURNS VOID |
jbe@164 | 9 LANGUAGE 'plpgsql' VOLATILE AS $$ |
jbe@164 | 10 DECLARE |
jbe@164 | 11 "area_id_v" "area"."id"%TYPE; |
jbe@164 | 12 "unit_id_v" "unit"."id"%TYPE; |
jbe@164 | 13 "member_id_v" "member"."id"%TYPE; |
jbe@164 | 14 BEGIN |
jbe@164 | 15 PERFORM "lock_issue"("issue_id_p"); |
jbe@164 | 16 SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p"; |
jbe@164 | 17 SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v"; |
jbe@164 | 18 DELETE FROM "delegating_voter" |
jbe@164 | 19 WHERE "issue_id" = "issue_id_p"; |
jbe@164 | 20 DELETE FROM "direct_voter" |
jbe@164 | 21 WHERE "issue_id" = "issue_id_p" |
jbe@164 | 22 AND "autoreject" = TRUE; |
jbe@164 | 23 DELETE FROM "direct_voter" |
jbe@164 | 24 USING ( |
jbe@164 | 25 SELECT |
jbe@164 | 26 "direct_voter"."member_id" |
jbe@164 | 27 FROM "direct_voter" |
jbe@164 | 28 JOIN "member" ON "direct_voter"."member_id" = "member"."id" |
jbe@164 | 29 LEFT JOIN "privilege" |
jbe@164 | 30 ON "privilege"."unit_id" = "unit_id_v" |
jbe@164 | 31 AND "privilege"."member_id" = "direct_voter"."member_id" |
jbe@164 | 32 WHERE "direct_voter"."issue_id" = "issue_id_p" AND ( |
jbe@164 | 33 "member"."active" = FALSE OR |
jbe@164 | 34 "privilege"."voting_right" ISNULL OR |
jbe@164 | 35 "privilege"."voting_right" = FALSE |
jbe@164 | 36 ) |
jbe@164 | 37 ) AS "subquery" |
jbe@164 | 38 WHERE "direct_voter"."issue_id" = "issue_id_p" |
jbe@164 | 39 AND "direct_voter"."member_id" = "subquery"."member_id"; |
jbe@164 | 40 UPDATE "direct_voter" SET "weight" = 1 |
jbe@164 | 41 WHERE "issue_id" = "issue_id_p"; |
jbe@164 | 42 PERFORM "add_vote_delegations"("issue_id_p"); |
jbe@164 | 43 FOR "member_id_v" IN |
jbe@164 | 44 SELECT "interest"."member_id" |
jbe@164 | 45 FROM "interest" |
jbe@164 | 46 JOIN "member" |
jbe@164 | 47 ON "interest"."member_id" = "member"."id" |
jbe@164 | 48 JOIN "privilege" |
jbe@164 | 49 ON "privilege"."unit_id" = "unit_id_v" |
jbe@164 | 50 AND "privilege"."member_id" = "member"."id" |
jbe@164 | 51 LEFT JOIN "direct_voter" |
jbe@164 | 52 ON "interest"."member_id" = "direct_voter"."member_id" |
jbe@164 | 53 AND "interest"."issue_id" = "direct_voter"."issue_id" |
jbe@164 | 54 LEFT JOIN "delegating_voter" |
jbe@164 | 55 ON "interest"."member_id" = "delegating_voter"."member_id" |
jbe@164 | 56 AND "interest"."issue_id" = "delegating_voter"."issue_id" |
jbe@164 | 57 WHERE "interest"."issue_id" = "issue_id_p" |
jbe@164 | 58 AND "interest"."autoreject" = TRUE |
jbe@164 | 59 AND "member"."active" |
jbe@164 | 60 AND "privilege"."voting_right" |
jbe@164 | 61 AND "direct_voter"."member_id" ISNULL |
jbe@164 | 62 AND "delegating_voter"."member_id" ISNULL |
jbe@164 | 63 UNION SELECT "membership"."member_id" |
jbe@164 | 64 FROM "membership" |
jbe@164 | 65 JOIN "member" |
jbe@164 | 66 ON "membership"."member_id" = "member"."id" |
jbe@164 | 67 JOIN "privilege" |
jbe@164 | 68 ON "privilege"."unit_id" = "unit_id_v" |
jbe@164 | 69 AND "privilege"."member_id" = "member"."id" |
jbe@164 | 70 LEFT JOIN "interest" |
jbe@164 | 71 ON "membership"."member_id" = "interest"."member_id" |
jbe@164 | 72 AND "interest"."issue_id" = "issue_id_p" |
jbe@164 | 73 LEFT JOIN "direct_voter" |
jbe@164 | 74 ON "membership"."member_id" = "direct_voter"."member_id" |
jbe@164 | 75 AND "direct_voter"."issue_id" = "issue_id_p" |
jbe@164 | 76 LEFT JOIN "delegating_voter" |
jbe@164 | 77 ON "membership"."member_id" = "delegating_voter"."member_id" |
jbe@164 | 78 AND "delegating_voter"."issue_id" = "issue_id_p" |
jbe@164 | 79 WHERE "membership"."area_id" = "area_id_v" |
jbe@164 | 80 AND "membership"."autoreject" = TRUE |
jbe@164 | 81 AND "member"."active" |
jbe@164 | 82 AND "privilege"."voting_right" |
jbe@164 | 83 AND "interest"."autoreject" ISNULL |
jbe@164 | 84 AND "direct_voter"."member_id" ISNULL |
jbe@164 | 85 AND "delegating_voter"."member_id" ISNULL |
jbe@164 | 86 LOOP |
jbe@164 | 87 INSERT INTO "direct_voter" |
jbe@164 | 88 ("member_id", "issue_id", "weight", "autoreject") VALUES |
jbe@164 | 89 ("member_id_v", "issue_id_p", 1, TRUE); |
jbe@164 | 90 INSERT INTO "vote" ( |
jbe@164 | 91 "member_id", |
jbe@164 | 92 "issue_id", |
jbe@164 | 93 "initiative_id", |
jbe@164 | 94 "grade" |
jbe@164 | 95 ) SELECT |
jbe@164 | 96 "member_id_v" AS "member_id", |
jbe@164 | 97 "issue_id_p" AS "issue_id", |
jbe@164 | 98 "id" AS "initiative_id", |
jbe@164 | 99 -1 AS "grade" |
jbe@164 | 100 FROM "initiative" WHERE "issue_id" = "issue_id_p"; |
jbe@164 | 101 END LOOP; |
jbe@164 | 102 PERFORM "add_vote_delegations"("issue_id_p"); |
jbe@164 | 103 UPDATE "issue" SET |
jbe@164 | 104 "state" = 'calculation', |
jbe@164 | 105 "closed" = now(), |
jbe@164 | 106 "voter_count" = ( |
jbe@164 | 107 SELECT coalesce(sum("weight"), 0) |
jbe@164 | 108 FROM "direct_voter" WHERE "issue_id" = "issue_id_p" |
jbe@164 | 109 ) |
jbe@164 | 110 WHERE "id" = "issue_id_p"; |
jbe@164 | 111 UPDATE "initiative" SET |
jbe@164 | 112 "positive_votes" = "vote_counts"."positive_votes", |
jbe@164 | 113 "negative_votes" = "vote_counts"."negative_votes", |
jbe@164 | 114 "agreed" = CASE WHEN "majority_strict" THEN |
jbe@164 | 115 "vote_counts"."positive_votes" * "majority_den" > |
jbe@164 | 116 "majority_num" * |
jbe@164 | 117 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes") |
jbe@164 | 118 ELSE |
jbe@164 | 119 "vote_counts"."positive_votes" * "majority_den" >= |
jbe@164 | 120 "majority_num" * |
jbe@164 | 121 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes") |
jbe@164 | 122 END |
jbe@164 | 123 FROM |
jbe@164 | 124 ( SELECT |
jbe@164 | 125 "initiative"."id" AS "initiative_id", |
jbe@164 | 126 coalesce( |
jbe@164 | 127 sum( |
jbe@164 | 128 CASE WHEN "grade" > 0 THEN "direct_voter"."weight" ELSE 0 END |
jbe@164 | 129 ), |
jbe@164 | 130 0 |
jbe@164 | 131 ) AS "positive_votes", |
jbe@164 | 132 coalesce( |
jbe@164 | 133 sum( |
jbe@164 | 134 CASE WHEN "grade" < 0 THEN "direct_voter"."weight" ELSE 0 END |
jbe@164 | 135 ), |
jbe@164 | 136 0 |
jbe@164 | 137 ) AS "negative_votes" |
jbe@164 | 138 FROM "initiative" |
jbe@164 | 139 JOIN "issue" ON "initiative"."issue_id" = "issue"."id" |
jbe@164 | 140 JOIN "policy" ON "issue"."policy_id" = "policy"."id" |
jbe@164 | 141 LEFT JOIN "direct_voter" |
jbe@164 | 142 ON "direct_voter"."issue_id" = "initiative"."issue_id" |
jbe@164 | 143 LEFT JOIN "vote" |
jbe@164 | 144 ON "vote"."initiative_id" = "initiative"."id" |
jbe@164 | 145 AND "vote"."member_id" = "direct_voter"."member_id" |
jbe@164 | 146 WHERE "initiative"."issue_id" = "issue_id_p" |
jbe@164 | 147 AND "initiative"."admitted" -- NOTE: NULL case is handled too |
jbe@164 | 148 GROUP BY "initiative"."id" |
jbe@164 | 149 ) AS "vote_counts", |
jbe@164 | 150 "issue", |
jbe@164 | 151 "policy" |
jbe@164 | 152 WHERE "vote_counts"."initiative_id" = "initiative"."id" |
jbe@164 | 153 AND "issue"."id" = "initiative"."issue_id" |
jbe@164 | 154 AND "policy"."id" = "issue"."policy_id"; |
jbe@164 | 155 -- NOTE: "closed" column of issue must be set at this point |
jbe@164 | 156 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p"; |
jbe@164 | 157 INSERT INTO "battle" ( |
jbe@164 | 158 "issue_id", |
jbe@164 | 159 "winning_initiative_id", "losing_initiative_id", |
jbe@164 | 160 "count" |
jbe@164 | 161 ) SELECT |
jbe@164 | 162 "issue_id", |
jbe@164 | 163 "winning_initiative_id", "losing_initiative_id", |
jbe@164 | 164 "count" |
jbe@164 | 165 FROM "battle_view" WHERE "issue_id" = "issue_id_p"; |
jbe@164 | 166 END; |
jbe@164 | 167 $$; |
jbe@164 | 168 |
jbe@164 | 169 COMMIT; |