liquid_feedback_core
annotate update/core-update.v1.2.8-v1.2.9.sql @ 235:2a6984869ba3
Delete sessions:
- when they expire
- in function "delete_private_data" (security relevant in certain setups)
- in function "delete_member"
(Sessions had been removed in changeset ed2f94a397cd and re-added in changeset 4e3d8f1ca3a9 without mechanisms for deletion of sessions)
- when they expire
- in function "delete_private_data" (security relevant in certain setups)
- in function "delete_member"
(Sessions had been removed in changeset ed2f94a397cd and re-added in changeset 4e3d8f1ca3a9 without mechanisms for deletion of sessions)
| author | jbe |
|---|---|
| date | Tue Mar 13 18:04:53 2012 +0100 (2012-03-13) |
| parents | e588fdf1676e |
| children |
| rev | line source |
|---|---|
| jbe@87 | 1 BEGIN; |
| jbe@87 | 2 |
| jbe@87 | 3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS |
| jbe@87 | 4 SELECT * FROM (VALUES ('1.2.9', 1, 2, 9)) |
| jbe@87 | 5 AS "subquery"("string", "major", "minor", "revision"); |
| jbe@87 | 6 |
| jbe@87 | 7 CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE) |
| jbe@87 | 8 RETURNS VOID |
| jbe@87 | 9 LANGUAGE 'plpgsql' VOLATILE AS $$ |
| jbe@87 | 10 DECLARE |
| jbe@87 | 11 "issue_row" "issue"%ROWTYPE; |
| jbe@87 | 12 "member_id_v" "member"."id"%TYPE; |
| jbe@87 | 13 BEGIN |
| jbe@87 | 14 PERFORM "lock_issue"("issue_id_p"); |
| jbe@87 | 15 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"; |
| jbe@87 | 16 DELETE FROM "delegating_voter" |
| jbe@87 | 17 WHERE "issue_id" = "issue_id_p"; |
| jbe@87 | 18 DELETE FROM "direct_voter" |
| jbe@87 | 19 WHERE "issue_id" = "issue_id_p" |
| jbe@87 | 20 AND "autoreject" = TRUE; |
| jbe@87 | 21 DELETE FROM "direct_voter" USING "member" |
| jbe@87 | 22 WHERE "direct_voter"."member_id" = "member"."id" |
| jbe@87 | 23 AND "direct_voter"."issue_id" = "issue_id_p" |
| jbe@87 | 24 AND "member"."active" = FALSE; |
| jbe@87 | 25 UPDATE "direct_voter" SET "weight" = 1 |
| jbe@87 | 26 WHERE "issue_id" = "issue_id_p"; |
| jbe@87 | 27 PERFORM "add_vote_delegations"("issue_id_p"); |
| jbe@87 | 28 FOR "member_id_v" IN |
| jbe@87 | 29 SELECT "interest"."member_id" |
| jbe@87 | 30 FROM "interest" |
| jbe@87 | 31 JOIN "member" |
| jbe@87 | 32 ON "interest"."member_id" = "member"."id" |
| jbe@87 | 33 LEFT JOIN "direct_voter" |
| jbe@87 | 34 ON "interest"."member_id" = "direct_voter"."member_id" |
| jbe@87 | 35 AND "interest"."issue_id" = "direct_voter"."issue_id" |
| jbe@87 | 36 LEFT JOIN "delegating_voter" |
| jbe@87 | 37 ON "interest"."member_id" = "delegating_voter"."member_id" |
| jbe@87 | 38 AND "interest"."issue_id" = "delegating_voter"."issue_id" |
| jbe@87 | 39 WHERE "interest"."issue_id" = "issue_id_p" |
| jbe@87 | 40 AND "interest"."autoreject" = TRUE |
| jbe@87 | 41 AND "member"."active" |
| jbe@87 | 42 AND "direct_voter"."member_id" ISNULL |
| jbe@87 | 43 AND "delegating_voter"."member_id" ISNULL |
| jbe@87 | 44 UNION SELECT "membership"."member_id" |
| jbe@87 | 45 FROM "membership" |
| jbe@87 | 46 JOIN "member" |
| jbe@87 | 47 ON "membership"."member_id" = "member"."id" |
| jbe@87 | 48 LEFT JOIN "interest" |
| jbe@87 | 49 ON "membership"."member_id" = "interest"."member_id" |
| jbe@87 | 50 AND "interest"."issue_id" = "issue_id_p" |
| jbe@87 | 51 LEFT JOIN "direct_voter" |
| jbe@87 | 52 ON "membership"."member_id" = "direct_voter"."member_id" |
| jbe@87 | 53 AND "direct_voter"."issue_id" = "issue_id_p" |
| jbe@87 | 54 LEFT JOIN "delegating_voter" |
| jbe@87 | 55 ON "membership"."member_id" = "delegating_voter"."member_id" |
| jbe@87 | 56 AND "delegating_voter"."issue_id" = "issue_id_p" |
| jbe@87 | 57 WHERE "membership"."area_id" = "issue_row"."area_id" |
| jbe@87 | 58 AND "membership"."autoreject" = TRUE |
| jbe@87 | 59 AND "member"."active" |
| jbe@87 | 60 AND "interest"."autoreject" ISNULL |
| jbe@87 | 61 AND "direct_voter"."member_id" ISNULL |
| jbe@87 | 62 AND "delegating_voter"."member_id" ISNULL |
| jbe@87 | 63 LOOP |
| jbe@87 | 64 INSERT INTO "direct_voter" |
| jbe@87 | 65 ("member_id", "issue_id", "weight", "autoreject") VALUES |
| jbe@87 | 66 ("member_id_v", "issue_id_p", 1, TRUE); |
| jbe@87 | 67 INSERT INTO "vote" ( |
| jbe@87 | 68 "member_id", |
| jbe@87 | 69 "issue_id", |
| jbe@87 | 70 "initiative_id", |
| jbe@87 | 71 "grade" |
| jbe@87 | 72 ) SELECT |
| jbe@87 | 73 "member_id_v" AS "member_id", |
| jbe@87 | 74 "issue_id_p" AS "issue_id", |
| jbe@87 | 75 "id" AS "initiative_id", |
| jbe@87 | 76 -1 AS "grade" |
| jbe@87 | 77 FROM "initiative" WHERE "issue_id" = "issue_id_p"; |
| jbe@87 | 78 END LOOP; |
| jbe@87 | 79 PERFORM "add_vote_delegations"("issue_id_p"); |
| jbe@87 | 80 UPDATE "issue" SET |
| jbe@87 | 81 "closed" = now(), |
| jbe@87 | 82 "voter_count" = ( |
| jbe@87 | 83 SELECT coalesce(sum("weight"), 0) |
| jbe@87 | 84 FROM "direct_voter" WHERE "issue_id" = "issue_id_p" |
| jbe@87 | 85 ) |
| jbe@87 | 86 WHERE "id" = "issue_id_p"; |
| jbe@87 | 87 UPDATE "initiative" SET |
| jbe@87 | 88 "positive_votes" = "vote_counts"."positive_votes", |
| jbe@87 | 89 "negative_votes" = "vote_counts"."negative_votes", |
| jbe@87 | 90 "agreed" = CASE WHEN "majority_strict" THEN |
| jbe@87 | 91 "vote_counts"."positive_votes" * "majority_den" > |
| jbe@87 | 92 "majority_num" * |
| jbe@87 | 93 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes") |
| jbe@87 | 94 ELSE |
| jbe@87 | 95 "vote_counts"."positive_votes" * "majority_den" >= |
| jbe@87 | 96 "majority_num" * |
| jbe@87 | 97 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes") |
| jbe@87 | 98 END |
| jbe@87 | 99 FROM |
| jbe@87 | 100 ( SELECT |
| jbe@87 | 101 "initiative"."id" AS "initiative_id", |
| jbe@87 | 102 coalesce( |
| jbe@87 | 103 sum( |
| jbe@87 | 104 CASE WHEN "grade" > 0 THEN "direct_voter"."weight" ELSE 0 END |
| jbe@87 | 105 ), |
| jbe@87 | 106 0 |
| jbe@87 | 107 ) AS "positive_votes", |
| jbe@87 | 108 coalesce( |
| jbe@87 | 109 sum( |
| jbe@87 | 110 CASE WHEN "grade" < 0 THEN "direct_voter"."weight" ELSE 0 END |
| jbe@87 | 111 ), |
| jbe@87 | 112 0 |
| jbe@87 | 113 ) AS "negative_votes" |
| jbe@87 | 114 FROM "initiative" |
| jbe@87 | 115 JOIN "issue" ON "initiative"."issue_id" = "issue"."id" |
| jbe@87 | 116 JOIN "policy" ON "issue"."policy_id" = "policy"."id" |
| jbe@87 | 117 LEFT JOIN "direct_voter" |
| jbe@87 | 118 ON "direct_voter"."issue_id" = "initiative"."issue_id" |
| jbe@87 | 119 LEFT JOIN "vote" |
| jbe@87 | 120 ON "vote"."initiative_id" = "initiative"."id" |
| jbe@87 | 121 AND "vote"."member_id" = "direct_voter"."member_id" |
| jbe@87 | 122 WHERE "initiative"."issue_id" = "issue_id_p" |
| jbe@87 | 123 AND "initiative"."admitted" -- NOTE: NULL case is handled too |
| jbe@87 | 124 GROUP BY "initiative"."id" |
| jbe@87 | 125 ) AS "vote_counts", |
| jbe@87 | 126 "issue", |
| jbe@87 | 127 "policy" |
| jbe@87 | 128 WHERE "vote_counts"."initiative_id" = "initiative"."id" |
| jbe@87 | 129 AND "issue"."id" = "initiative"."issue_id" |
| jbe@87 | 130 AND "policy"."id" = "issue"."policy_id"; |
| jbe@87 | 131 -- NOTE: "closed" column of issue must be set at this point |
| jbe@87 | 132 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p"; |
| jbe@87 | 133 INSERT INTO "battle" ( |
| jbe@87 | 134 "issue_id", |
| jbe@87 | 135 "winning_initiative_id", "losing_initiative_id", |
| jbe@87 | 136 "count" |
| jbe@87 | 137 ) SELECT |
| jbe@87 | 138 "issue_id", |
| jbe@87 | 139 "winning_initiative_id", "losing_initiative_id", |
| jbe@87 | 140 "count" |
| jbe@87 | 141 FROM "battle_view" WHERE "issue_id" = "issue_id_p"; |
| jbe@87 | 142 END; |
| jbe@87 | 143 $$; |
| jbe@87 | 144 |
| jbe@87 | 145 COMMIT; |