liquid_feedback_core
annotate update/core-update.v1.4.0_rc3-v1.4.0_rc4.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 | 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_rc4', 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" |
jbe@164 | 101 WHERE "issue_id" = "issue_id_p" AND "admitted"; |
jbe@164 | 102 END LOOP; |
jbe@164 | 103 PERFORM "add_vote_delegations"("issue_id_p"); |
jbe@164 | 104 UPDATE "issue" SET |
jbe@164 | 105 "state" = 'calculation', |
jbe@164 | 106 "closed" = now(), |
jbe@164 | 107 "voter_count" = ( |
jbe@164 | 108 SELECT coalesce(sum("weight"), 0) |
jbe@164 | 109 FROM "direct_voter" WHERE "issue_id" = "issue_id_p" |
jbe@164 | 110 ) |
jbe@164 | 111 WHERE "id" = "issue_id_p"; |
jbe@164 | 112 UPDATE "initiative" SET |
jbe@164 | 113 "positive_votes" = "vote_counts"."positive_votes", |
jbe@164 | 114 "negative_votes" = "vote_counts"."negative_votes", |
jbe@164 | 115 "agreed" = CASE WHEN "majority_strict" THEN |
jbe@164 | 116 "vote_counts"."positive_votes" * "majority_den" > |
jbe@164 | 117 "majority_num" * |
jbe@164 | 118 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes") |
jbe@164 | 119 ELSE |
jbe@164 | 120 "vote_counts"."positive_votes" * "majority_den" >= |
jbe@164 | 121 "majority_num" * |
jbe@164 | 122 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes") |
jbe@164 | 123 END |
jbe@164 | 124 FROM |
jbe@164 | 125 ( SELECT |
jbe@164 | 126 "initiative"."id" AS "initiative_id", |
jbe@164 | 127 coalesce( |
jbe@164 | 128 sum( |
jbe@164 | 129 CASE WHEN "grade" > 0 THEN "direct_voter"."weight" ELSE 0 END |
jbe@164 | 130 ), |
jbe@164 | 131 0 |
jbe@164 | 132 ) AS "positive_votes", |
jbe@164 | 133 coalesce( |
jbe@164 | 134 sum( |
jbe@164 | 135 CASE WHEN "grade" < 0 THEN "direct_voter"."weight" ELSE 0 END |
jbe@164 | 136 ), |
jbe@164 | 137 0 |
jbe@164 | 138 ) AS "negative_votes" |
jbe@164 | 139 FROM "initiative" |
jbe@164 | 140 JOIN "issue" ON "initiative"."issue_id" = "issue"."id" |
jbe@164 | 141 JOIN "policy" ON "issue"."policy_id" = "policy"."id" |
jbe@164 | 142 LEFT JOIN "direct_voter" |
jbe@164 | 143 ON "direct_voter"."issue_id" = "initiative"."issue_id" |
jbe@164 | 144 LEFT JOIN "vote" |
jbe@164 | 145 ON "vote"."initiative_id" = "initiative"."id" |
jbe@164 | 146 AND "vote"."member_id" = "direct_voter"."member_id" |
jbe@164 | 147 WHERE "initiative"."issue_id" = "issue_id_p" |
jbe@164 | 148 AND "initiative"."admitted" -- NOTE: NULL case is handled too |
jbe@164 | 149 GROUP BY "initiative"."id" |
jbe@164 | 150 ) AS "vote_counts", |
jbe@164 | 151 "issue", |
jbe@164 | 152 "policy" |
jbe@164 | 153 WHERE "vote_counts"."initiative_id" = "initiative"."id" |
jbe@164 | 154 AND "issue"."id" = "initiative"."issue_id" |
jbe@164 | 155 AND "policy"."id" = "issue"."policy_id"; |
jbe@164 | 156 -- NOTE: "closed" column of issue must be set at this point |
jbe@164 | 157 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p"; |
jbe@164 | 158 INSERT INTO "battle" ( |
jbe@164 | 159 "issue_id", |
jbe@164 | 160 "winning_initiative_id", "losing_initiative_id", |
jbe@164 | 161 "count" |
jbe@164 | 162 ) SELECT |
jbe@164 | 163 "issue_id", |
jbe@164 | 164 "winning_initiative_id", "losing_initiative_id", |
jbe@164 | 165 "count" |
jbe@164 | 166 FROM "battle_view" WHERE "issue_id" = "issue_id_p"; |
jbe@164 | 167 END; |
jbe@164 | 168 $$; |
jbe@164 | 169 |
jbe@164 | 170 CREATE OR REPLACE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE) |
jbe@164 | 171 RETURNS VOID |
jbe@164 | 172 LANGUAGE 'plpgsql' VOLATILE AS $$ |
jbe@164 | 173 DECLARE |
jbe@164 | 174 "issue_row" "issue"%ROWTYPE; |
jbe@164 | 175 BEGIN |
jbe@164 | 176 SELECT * INTO "issue_row" |
jbe@164 | 177 FROM "issue" WHERE "id" = "issue_id_p" |
jbe@164 | 178 FOR UPDATE; |
jbe@164 | 179 IF "issue_row"."cleaned" ISNULL THEN |
jbe@164 | 180 UPDATE "issue" SET |
jbe@164 | 181 "state" = 'voting', |
jbe@164 | 182 "closed" = NULL, |
jbe@164 | 183 "ranks_available" = FALSE |
jbe@164 | 184 WHERE "id" = "issue_id_p"; |
jbe@164 | 185 DELETE FROM "issue_comment" |
jbe@164 | 186 WHERE "issue_id" = "issue_id_p"; |
jbe@164 | 187 DELETE FROM "voting_comment" |
jbe@164 | 188 WHERE "issue_id" = "issue_id_p"; |
jbe@164 | 189 DELETE FROM "delegating_voter" |
jbe@164 | 190 WHERE "issue_id" = "issue_id_p"; |
jbe@164 | 191 DELETE FROM "direct_voter" |
jbe@164 | 192 WHERE "issue_id" = "issue_id_p"; |
jbe@164 | 193 DELETE FROM "delegating_interest_snapshot" |
jbe@164 | 194 WHERE "issue_id" = "issue_id_p"; |
jbe@164 | 195 DELETE FROM "direct_interest_snapshot" |
jbe@164 | 196 WHERE "issue_id" = "issue_id_p"; |
jbe@164 | 197 DELETE FROM "delegating_population_snapshot" |
jbe@164 | 198 WHERE "issue_id" = "issue_id_p"; |
jbe@164 | 199 DELETE FROM "direct_population_snapshot" |
jbe@164 | 200 WHERE "issue_id" = "issue_id_p"; |
jbe@164 | 201 DELETE FROM "non_voter" |
jbe@164 | 202 WHERE "issue_id" = "issue_id_p"; |
jbe@164 | 203 DELETE FROM "delegation" |
jbe@164 | 204 WHERE "issue_id" = "issue_id_p"; |
jbe@164 | 205 DELETE FROM "supporter" |
jbe@164 | 206 WHERE "issue_id" = "issue_id_p"; |
jbe@164 | 207 UPDATE "issue" SET |
jbe@164 | 208 "state" = "issue_row"."state", |
jbe@164 | 209 "closed" = "issue_row"."closed", |
jbe@164 | 210 "ranks_available" = "issue_row"."ranks_available", |
jbe@164 | 211 "cleaned" = now() |
jbe@164 | 212 WHERE "id" = "issue_id_p"; |
jbe@164 | 213 END IF; |
jbe@164 | 214 RETURN; |
jbe@164 | 215 END; |
jbe@164 | 216 $$; |
jbe@164 | 217 |
jbe@164 | 218 COMMIT; |