liquid_feedback_core
annotate update/core-update.v1.2.1-v1.2.2.sql @ 61:598af132a6f9
Save battle data from calls of "clean_issue"("issue"."id")
Details:
- Calling "clean_issue" is also allowed before ranks are calculated
- View "battle" was renamed to "battle_view"
- "battle_view" only contains entries for closed issues
- New table "battle", which is filled when an issue is closed
- Version change to v1.2.2
- Update script to v1.2.2 materializes battles of existent (closed) issues
Details:
- Calling "clean_issue" is also allowed before ranks are calculated
- View "battle" was renamed to "battle_view"
- "battle_view" only contains entries for closed issues
- New table "battle", which is filled when an issue is closed
- Version change to v1.2.2
- Update script to v1.2.2 materializes battles of existent (closed) issues
author | jbe |
---|---|
date | Tue Jul 20 00:53:30 2010 +0200 (2010-07-20) |
parents | |
children |
rev | line source |
---|---|
jbe@61 | 1 BEGIN; |
jbe@61 | 2 |
jbe@61 | 3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS |
jbe@61 | 4 SELECT * FROM (VALUES ('1.2.2', 1, 2, 2)) |
jbe@61 | 5 AS "subquery"("string", "major", "minor", "revision"); |
jbe@61 | 6 |
jbe@61 | 7 ALTER TABLE "issue" DROP CONSTRAINT "clean_restriction"; |
jbe@61 | 8 |
jbe@61 | 9 ALTER TABLE "issue" ADD CONSTRAINT "only_closed_issues_may_be_cleaned" |
jbe@61 | 10 CHECK ("cleaned" ISNULL OR "closed" NOTNULL); |
jbe@61 | 11 |
jbe@61 | 12 ALTER VIEW "battle" RENAME TO "battle_view"; |
jbe@61 | 13 |
jbe@61 | 14 CREATE TABLE "battle" ( |
jbe@61 | 15 PRIMARY KEY ("issue_id", "winning_initiative_id", "losing_initiative_id"), |
jbe@61 | 16 "issue_id" INT4, |
jbe@61 | 17 "winning_initiative_id" INT4, |
jbe@61 | 18 FOREIGN KEY ("issue_id", "winning_initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE, |
jbe@61 | 19 "losing_initiative_id" INT4, |
jbe@61 | 20 FOREIGN KEY ("issue_id", "losing_initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE, |
jbe@61 | 21 "count" INT4 NOT NULL); |
jbe@61 | 22 |
jbe@61 | 23 COMMENT ON TABLE "battle" IS 'Number of members preferring one initiative to another; Filled by "battle_view" when closing an issue'; |
jbe@61 | 24 |
jbe@61 | 25 CREATE OR REPLACE VIEW "battle_view" AS |
jbe@61 | 26 SELECT |
jbe@61 | 27 "issue"."id" AS "issue_id", |
jbe@61 | 28 "winning_initiative"."id" AS "winning_initiative_id", |
jbe@61 | 29 "losing_initiative"."id" AS "losing_initiative_id", |
jbe@61 | 30 sum( |
jbe@61 | 31 CASE WHEN |
jbe@61 | 32 coalesce("better_vote"."grade", 0) > |
jbe@61 | 33 coalesce("worse_vote"."grade", 0) |
jbe@61 | 34 THEN "direct_voter"."weight" ELSE 0 END |
jbe@61 | 35 ) AS "count" |
jbe@61 | 36 FROM "issue" |
jbe@61 | 37 LEFT JOIN "direct_voter" |
jbe@61 | 38 ON "issue"."id" = "direct_voter"."issue_id" |
jbe@61 | 39 JOIN "initiative" AS "winning_initiative" |
jbe@61 | 40 ON "issue"."id" = "winning_initiative"."issue_id" |
jbe@61 | 41 AND "winning_initiative"."agreed" |
jbe@61 | 42 JOIN "initiative" AS "losing_initiative" |
jbe@61 | 43 ON "issue"."id" = "losing_initiative"."issue_id" |
jbe@61 | 44 AND "losing_initiative"."agreed" |
jbe@61 | 45 LEFT JOIN "vote" AS "better_vote" |
jbe@61 | 46 ON "direct_voter"."member_id" = "better_vote"."member_id" |
jbe@61 | 47 AND "winning_initiative"."id" = "better_vote"."initiative_id" |
jbe@61 | 48 LEFT JOIN "vote" AS "worse_vote" |
jbe@61 | 49 ON "direct_voter"."member_id" = "worse_vote"."member_id" |
jbe@61 | 50 AND "losing_initiative"."id" = "worse_vote"."initiative_id" |
jbe@61 | 51 WHERE "issue"."closed" NOTNULL |
jbe@61 | 52 AND "issue"."cleaned" ISNULL |
jbe@61 | 53 AND "winning_initiative"."id" != "losing_initiative"."id" |
jbe@61 | 54 GROUP BY |
jbe@61 | 55 "issue"."id", |
jbe@61 | 56 "winning_initiative"."id", |
jbe@61 | 57 "losing_initiative"."id"; |
jbe@61 | 58 |
jbe@61 | 59 COMMENT ON VIEW "battle_view" IS 'Number of members preferring one initiative to another; Used to fill "battle" table'; |
jbe@61 | 60 |
jbe@61 | 61 INSERT INTO "battle" ( |
jbe@61 | 62 "issue_id", |
jbe@61 | 63 "winning_initiative_id", |
jbe@61 | 64 "losing_initiative_id", |
jbe@61 | 65 "count" |
jbe@61 | 66 ) SELECT |
jbe@61 | 67 "issue_id", |
jbe@61 | 68 "winning_initiative_id", "losing_initiative_id", |
jbe@61 | 69 "count" |
jbe@61 | 70 FROM "battle_view"; |
jbe@61 | 71 |
jbe@61 | 72 CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE) |
jbe@61 | 73 RETURNS VOID |
jbe@61 | 74 LANGUAGE 'plpgsql' VOLATILE AS $$ |
jbe@61 | 75 DECLARE |
jbe@61 | 76 "issue_row" "issue"%ROWTYPE; |
jbe@61 | 77 "member_id_v" "member"."id"%TYPE; |
jbe@61 | 78 BEGIN |
jbe@61 | 79 PERFORM "global_lock"(); |
jbe@61 | 80 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"; |
jbe@61 | 81 DELETE FROM "delegating_voter" |
jbe@61 | 82 WHERE "issue_id" = "issue_id_p"; |
jbe@61 | 83 DELETE FROM "direct_voter" |
jbe@61 | 84 WHERE "issue_id" = "issue_id_p" |
jbe@61 | 85 AND "autoreject" = TRUE; |
jbe@61 | 86 DELETE FROM "direct_voter" USING "member" |
jbe@61 | 87 WHERE "direct_voter"."member_id" = "member"."id" |
jbe@61 | 88 AND "direct_voter"."issue_id" = "issue_id_p" |
jbe@61 | 89 AND "member"."active" = FALSE; |
jbe@61 | 90 UPDATE "direct_voter" SET "weight" = 1 |
jbe@61 | 91 WHERE "issue_id" = "issue_id_p"; |
jbe@61 | 92 PERFORM "add_vote_delegations"("issue_id_p"); |
jbe@61 | 93 FOR "member_id_v" IN |
jbe@61 | 94 SELECT "interest"."member_id" |
jbe@61 | 95 FROM "interest" |
jbe@61 | 96 LEFT JOIN "direct_voter" |
jbe@61 | 97 ON "interest"."member_id" = "direct_voter"."member_id" |
jbe@61 | 98 AND "interest"."issue_id" = "direct_voter"."issue_id" |
jbe@61 | 99 LEFT JOIN "delegating_voter" |
jbe@61 | 100 ON "interest"."member_id" = "delegating_voter"."member_id" |
jbe@61 | 101 AND "interest"."issue_id" = "delegating_voter"."issue_id" |
jbe@61 | 102 WHERE "interest"."issue_id" = "issue_id_p" |
jbe@61 | 103 AND "interest"."autoreject" = TRUE |
jbe@61 | 104 AND "direct_voter"."member_id" ISNULL |
jbe@61 | 105 AND "delegating_voter"."member_id" ISNULL |
jbe@61 | 106 UNION SELECT "membership"."member_id" |
jbe@61 | 107 FROM "membership" |
jbe@61 | 108 LEFT JOIN "interest" |
jbe@61 | 109 ON "membership"."member_id" = "interest"."member_id" |
jbe@61 | 110 AND "interest"."issue_id" = "issue_id_p" |
jbe@61 | 111 LEFT JOIN "direct_voter" |
jbe@61 | 112 ON "membership"."member_id" = "direct_voter"."member_id" |
jbe@61 | 113 AND "direct_voter"."issue_id" = "issue_id_p" |
jbe@61 | 114 LEFT JOIN "delegating_voter" |
jbe@61 | 115 ON "membership"."member_id" = "delegating_voter"."member_id" |
jbe@61 | 116 AND "delegating_voter"."issue_id" = "issue_id_p" |
jbe@61 | 117 WHERE "membership"."area_id" = "issue_row"."area_id" |
jbe@61 | 118 AND "membership"."autoreject" = TRUE |
jbe@61 | 119 AND "interest"."autoreject" ISNULL |
jbe@61 | 120 AND "direct_voter"."member_id" ISNULL |
jbe@61 | 121 AND "delegating_voter"."member_id" ISNULL |
jbe@61 | 122 LOOP |
jbe@61 | 123 INSERT INTO "direct_voter" |
jbe@61 | 124 ("member_id", "issue_id", "weight", "autoreject") VALUES |
jbe@61 | 125 ("member_id_v", "issue_id_p", 1, TRUE); |
jbe@61 | 126 INSERT INTO "vote" ( |
jbe@61 | 127 "member_id", |
jbe@61 | 128 "issue_id", |
jbe@61 | 129 "initiative_id", |
jbe@61 | 130 "grade" |
jbe@61 | 131 ) SELECT |
jbe@61 | 132 "member_id_v" AS "member_id", |
jbe@61 | 133 "issue_id_p" AS "issue_id", |
jbe@61 | 134 "id" AS "initiative_id", |
jbe@61 | 135 -1 AS "grade" |
jbe@61 | 136 FROM "initiative" WHERE "issue_id" = "issue_id_p"; |
jbe@61 | 137 END LOOP; |
jbe@61 | 138 PERFORM "add_vote_delegations"("issue_id_p"); |
jbe@61 | 139 UPDATE "issue" SET |
jbe@61 | 140 "closed" = now(), |
jbe@61 | 141 "voter_count" = ( |
jbe@61 | 142 SELECT coalesce(sum("weight"), 0) |
jbe@61 | 143 FROM "direct_voter" WHERE "issue_id" = "issue_id_p" |
jbe@61 | 144 ) |
jbe@61 | 145 WHERE "id" = "issue_id_p"; |
jbe@61 | 146 UPDATE "initiative" SET |
jbe@61 | 147 "positive_votes" = "vote_counts"."positive_votes", |
jbe@61 | 148 "negative_votes" = "vote_counts"."negative_votes", |
jbe@61 | 149 "agreed" = CASE WHEN "majority_strict" THEN |
jbe@61 | 150 "vote_counts"."positive_votes" * "majority_den" > |
jbe@61 | 151 "majority_num" * |
jbe@61 | 152 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes") |
jbe@61 | 153 ELSE |
jbe@61 | 154 "vote_counts"."positive_votes" * "majority_den" >= |
jbe@61 | 155 "majority_num" * |
jbe@61 | 156 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes") |
jbe@61 | 157 END |
jbe@61 | 158 FROM |
jbe@61 | 159 ( SELECT |
jbe@61 | 160 "initiative"."id" AS "initiative_id", |
jbe@61 | 161 coalesce( |
jbe@61 | 162 sum( |
jbe@61 | 163 CASE WHEN "grade" > 0 THEN "direct_voter"."weight" ELSE 0 END |
jbe@61 | 164 ), |
jbe@61 | 165 0 |
jbe@61 | 166 ) AS "positive_votes", |
jbe@61 | 167 coalesce( |
jbe@61 | 168 sum( |
jbe@61 | 169 CASE WHEN "grade" < 0 THEN "direct_voter"."weight" ELSE 0 END |
jbe@61 | 170 ), |
jbe@61 | 171 0 |
jbe@61 | 172 ) AS "negative_votes" |
jbe@61 | 173 FROM "initiative" |
jbe@61 | 174 JOIN "issue" ON "initiative"."issue_id" = "issue"."id" |
jbe@61 | 175 JOIN "policy" ON "issue"."policy_id" = "policy"."id" |
jbe@61 | 176 LEFT JOIN "direct_voter" |
jbe@61 | 177 ON "direct_voter"."issue_id" = "initiative"."issue_id" |
jbe@61 | 178 LEFT JOIN "vote" |
jbe@61 | 179 ON "vote"."initiative_id" = "initiative"."id" |
jbe@61 | 180 AND "vote"."member_id" = "direct_voter"."member_id" |
jbe@61 | 181 WHERE "initiative"."issue_id" = "issue_id_p" |
jbe@61 | 182 AND "initiative"."admitted" -- NOTE: NULL case is handled too |
jbe@61 | 183 GROUP BY "initiative"."id" |
jbe@61 | 184 ) AS "vote_counts", |
jbe@61 | 185 "issue", |
jbe@61 | 186 "policy" |
jbe@61 | 187 WHERE "vote_counts"."initiative_id" = "initiative"."id" |
jbe@61 | 188 AND "issue"."id" = "initiative"."issue_id" |
jbe@61 | 189 AND "policy"."id" = "issue"."policy_id"; |
jbe@61 | 190 -- NOTE: "closed" column of issue must be set at this point |
jbe@61 | 191 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p"; |
jbe@61 | 192 INSERT INTO "battle" ( |
jbe@61 | 193 "issue_id", |
jbe@61 | 194 "winning_initiative_id", "losing_initiative_id", |
jbe@61 | 195 "count" |
jbe@61 | 196 ) SELECT |
jbe@61 | 197 "issue_id", |
jbe@61 | 198 "winning_initiative_id", "losing_initiative_id", |
jbe@61 | 199 "count" |
jbe@61 | 200 FROM "battle_view" WHERE "issue_id" = "issue_id_p"; |
jbe@61 | 201 END; |
jbe@61 | 202 $$; |
jbe@61 | 203 |
jbe@61 | 204 COMMIT; |