liquid_feedback_core
annotate update/core-update.v1.2.4-v1.2.5.sql @ 222:cd5fa6c46e13
Changes in event system:
- replaced column "notify_event_id" of "member" table by singleton table "notification_sent"
- removed view "pending_notification"
- added view "selected_event_seen_by_member"
- replaced column "notify_event_id" of "member" table by singleton table "notification_sent"
- removed view "pending_notification"
- added view "selected_event_seen_by_member"
author | jbe |
---|---|
date | Sat Feb 25 15:13:01 2012 +0100 (2012-02-25) |
parents | 72e5356b5454 |
children |
rev | line source |
---|---|
jbe@67 | 1 BEGIN; |
jbe@67 | 2 |
jbe@67 | 3 |
jbe@67 | 4 CREATE OR REPLACE VIEW "liquid_feedback_version" AS |
jbe@67 | 5 SELECT * FROM (VALUES ('1.2.5', 1, 2, 5)) |
jbe@67 | 6 AS "subquery"("string", "major", "minor", "revision"); |
jbe@67 | 7 |
jbe@67 | 8 |
jbe@67 | 9 CREATE FUNCTION "share_row_lock_issue_trigger"() |
jbe@67 | 10 RETURNS TRIGGER |
jbe@67 | 11 LANGUAGE 'plpgsql' VOLATILE AS $$ |
jbe@67 | 12 BEGIN |
jbe@67 | 13 IF TG_OP = 'UPDATE' OR TG_OP = 'DELETE' THEN |
jbe@67 | 14 PERFORM NULL FROM "issue" WHERE "id" = OLD."issue_id" FOR SHARE; |
jbe@67 | 15 END IF; |
jbe@67 | 16 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN |
jbe@67 | 17 PERFORM NULL FROM "issue" WHERE "id" = NEW."issue_id" FOR SHARE; |
jbe@67 | 18 RETURN NEW; |
jbe@67 | 19 ELSE |
jbe@67 | 20 RETURN OLD; |
jbe@67 | 21 END IF; |
jbe@67 | 22 END; |
jbe@67 | 23 $$; |
jbe@67 | 24 |
jbe@67 | 25 COMMENT ON FUNCTION "share_row_lock_issue_trigger"() IS 'Implementation of triggers "share_row_lock_issue" on multiple tables'; |
jbe@67 | 26 |
jbe@67 | 27 |
jbe@67 | 28 CREATE FUNCTION "share_row_lock_issue_via_initiative_trigger"() |
jbe@67 | 29 RETURNS TRIGGER |
jbe@67 | 30 LANGUAGE 'plpgsql' VOLATILE AS $$ |
jbe@67 | 31 BEGIN |
jbe@67 | 32 IF TG_OP = 'UPDATE' OR TG_OP = 'DELETE' THEN |
jbe@67 | 33 PERFORM NULL FROM "issue" |
jbe@67 | 34 JOIN "initiative" ON "issue"."id" = "initiative"."issue_id" |
jbe@67 | 35 WHERE "initiative"."id" = OLD."initiative_id" |
jbe@67 | 36 FOR SHARE OF "issue"; |
jbe@67 | 37 END IF; |
jbe@67 | 38 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN |
jbe@67 | 39 PERFORM NULL FROM "issue" |
jbe@67 | 40 JOIN "initiative" ON "issue"."id" = "initiative"."issue_id" |
jbe@67 | 41 WHERE "initiative"."id" = NEW."initiative_id" |
jbe@67 | 42 FOR SHARE OF "issue"; |
jbe@67 | 43 RETURN NEW; |
jbe@67 | 44 ELSE |
jbe@67 | 45 RETURN OLD; |
jbe@67 | 46 END IF; |
jbe@67 | 47 END; |
jbe@67 | 48 $$; |
jbe@67 | 49 |
jbe@67 | 50 COMMENT ON FUNCTION "share_row_lock_issue_trigger"() IS 'Implementation of trigger "share_row_lock_issue_via_initiative" on table "opinion"'; |
jbe@67 | 51 |
jbe@67 | 52 |
jbe@67 | 53 CREATE TRIGGER "share_row_lock_issue" |
jbe@67 | 54 BEFORE INSERT OR UPDATE OR DELETE ON "initiative" |
jbe@67 | 55 FOR EACH ROW EXECUTE PROCEDURE |
jbe@67 | 56 "share_row_lock_issue_trigger"(); |
jbe@67 | 57 |
jbe@67 | 58 CREATE TRIGGER "share_row_lock_issue" |
jbe@67 | 59 BEFORE INSERT OR UPDATE OR DELETE ON "interest" |
jbe@67 | 60 FOR EACH ROW EXECUTE PROCEDURE |
jbe@67 | 61 "share_row_lock_issue_trigger"(); |
jbe@67 | 62 |
jbe@67 | 63 CREATE TRIGGER "share_row_lock_issue" |
jbe@67 | 64 BEFORE INSERT OR UPDATE OR DELETE ON "supporter" |
jbe@67 | 65 FOR EACH ROW EXECUTE PROCEDURE |
jbe@67 | 66 "share_row_lock_issue_trigger"(); |
jbe@67 | 67 |
jbe@67 | 68 CREATE TRIGGER "share_row_lock_issue_via_initiative" |
jbe@67 | 69 BEFORE INSERT OR UPDATE OR DELETE ON "opinion" |
jbe@67 | 70 FOR EACH ROW EXECUTE PROCEDURE |
jbe@67 | 71 "share_row_lock_issue_via_initiative_trigger"(); |
jbe@67 | 72 |
jbe@67 | 73 CREATE TRIGGER "share_row_lock_issue" |
jbe@67 | 74 BEFORE INSERT OR UPDATE OR DELETE ON "direct_voter" |
jbe@67 | 75 FOR EACH ROW EXECUTE PROCEDURE |
jbe@67 | 76 "share_row_lock_issue_trigger"(); |
jbe@67 | 77 |
jbe@67 | 78 CREATE TRIGGER "share_row_lock_issue" |
jbe@67 | 79 BEFORE INSERT OR UPDATE OR DELETE ON "delegating_voter" |
jbe@67 | 80 FOR EACH ROW EXECUTE PROCEDURE |
jbe@67 | 81 "share_row_lock_issue_trigger"(); |
jbe@67 | 82 |
jbe@67 | 83 CREATE TRIGGER "share_row_lock_issue" |
jbe@67 | 84 BEFORE INSERT OR UPDATE OR DELETE ON "vote" |
jbe@67 | 85 FOR EACH ROW EXECUTE PROCEDURE |
jbe@67 | 86 "share_row_lock_issue_trigger"(); |
jbe@67 | 87 |
jbe@67 | 88 COMMENT ON TRIGGER "share_row_lock_issue" ON "initiative" IS 'See "lock_issue" function'; |
jbe@67 | 89 COMMENT ON TRIGGER "share_row_lock_issue" ON "interest" IS 'See "lock_issue" function'; |
jbe@67 | 90 COMMENT ON TRIGGER "share_row_lock_issue" ON "supporter" IS 'See "lock_issue" function'; |
jbe@67 | 91 COMMENT ON TRIGGER "share_row_lock_issue_via_initiative" ON "opinion" IS 'See "lock_issue" function'; |
jbe@67 | 92 COMMENT ON TRIGGER "share_row_lock_issue" ON "direct_voter" IS 'See "lock_issue" function'; |
jbe@67 | 93 COMMENT ON TRIGGER "share_row_lock_issue" ON "delegating_voter" IS 'See "lock_issue" function'; |
jbe@67 | 94 COMMENT ON TRIGGER "share_row_lock_issue" ON "vote" IS 'See "lock_issue" function'; |
jbe@67 | 95 |
jbe@67 | 96 |
jbe@67 | 97 CREATE FUNCTION "lock_issue" |
jbe@67 | 98 ( "issue_id_p" "issue"."id"%TYPE ) |
jbe@67 | 99 RETURNS VOID |
jbe@67 | 100 LANGUAGE 'plpgsql' VOLATILE AS $$ |
jbe@67 | 101 BEGIN |
jbe@67 | 102 LOCK TABLE "member" IN SHARE MODE; |
jbe@67 | 103 LOCK TABLE "membership" IN SHARE MODE; |
jbe@67 | 104 LOCK TABLE "policy" IN SHARE MODE; |
jbe@67 | 105 PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE; |
jbe@67 | 106 -- NOTE: The row-level exclusive lock in combination with the |
jbe@67 | 107 -- share_row_lock_issue(_via_initiative)_trigger functions (which |
jbe@67 | 108 -- acquire a row-level share lock on the issue) ensure that no data |
jbe@67 | 109 -- is changed, which could affect calculation of snapshots or |
jbe@67 | 110 -- counting of votes. Table "delegation" must be table-level-locked, |
jbe@67 | 111 -- as it also contains issue- and global-scope delegations. |
jbe@67 | 112 LOCK TABLE "delegation" IN SHARE MODE; |
jbe@67 | 113 LOCK TABLE "direct_population_snapshot" IN EXCLUSIVE MODE; |
jbe@67 | 114 LOCK TABLE "delegating_population_snapshot" IN EXCLUSIVE MODE; |
jbe@67 | 115 LOCK TABLE "direct_interest_snapshot" IN EXCLUSIVE MODE; |
jbe@67 | 116 LOCK TABLE "delegating_interest_snapshot" IN EXCLUSIVE MODE; |
jbe@67 | 117 LOCK TABLE "direct_supporter_snapshot" IN EXCLUSIVE MODE; |
jbe@67 | 118 RETURN; |
jbe@67 | 119 END; |
jbe@67 | 120 $$; |
jbe@67 | 121 |
jbe@67 | 122 COMMENT ON FUNCTION "lock_issue" |
jbe@67 | 123 ( "issue"."id"%TYPE ) |
jbe@67 | 124 IS 'Locks the issue and all other data which is used for calculating snapshots or counting votes.'; |
jbe@67 | 125 |
jbe@67 | 126 |
jbe@67 | 127 CREATE OR REPLACE FUNCTION "calculate_member_counts"() |
jbe@67 | 128 RETURNS VOID |
jbe@67 | 129 LANGUAGE 'plpgsql' VOLATILE AS $$ |
jbe@67 | 130 BEGIN |
jbe@67 | 131 LOCK TABLE "member" IN SHARE MODE; |
jbe@67 | 132 LOCK TABLE "member_count" IN EXCLUSIVE MODE; |
jbe@67 | 133 LOCK TABLE "area" IN EXCLUSIVE MODE; |
jbe@67 | 134 LOCK TABLE "membership" IN SHARE MODE; |
jbe@67 | 135 DELETE FROM "member_count"; |
jbe@67 | 136 INSERT INTO "member_count" ("total_count") |
jbe@67 | 137 SELECT "total_count" FROM "member_count_view"; |
jbe@67 | 138 UPDATE "area" SET |
jbe@67 | 139 "direct_member_count" = "view"."direct_member_count", |
jbe@67 | 140 "member_weight" = "view"."member_weight", |
jbe@67 | 141 "autoreject_weight" = "view"."autoreject_weight" |
jbe@67 | 142 FROM "area_member_count" AS "view" |
jbe@67 | 143 WHERE "view"."area_id" = "area"."id"; |
jbe@67 | 144 RETURN; |
jbe@67 | 145 END; |
jbe@67 | 146 $$; |
jbe@67 | 147 |
jbe@67 | 148 CREATE OR REPLACE FUNCTION "create_snapshot" |
jbe@67 | 149 ( "issue_id_p" "issue"."id"%TYPE ) |
jbe@67 | 150 RETURNS VOID |
jbe@67 | 151 LANGUAGE 'plpgsql' VOLATILE AS $$ |
jbe@67 | 152 DECLARE |
jbe@67 | 153 "initiative_id_v" "initiative"."id"%TYPE; |
jbe@67 | 154 "suggestion_id_v" "suggestion"."id"%TYPE; |
jbe@67 | 155 BEGIN |
jbe@67 | 156 PERFORM "lock_issue"("issue_id_p"); |
jbe@67 | 157 PERFORM "create_population_snapshot"("issue_id_p"); |
jbe@67 | 158 PERFORM "create_interest_snapshot"("issue_id_p"); |
jbe@67 | 159 UPDATE "issue" SET |
jbe@67 | 160 "snapshot" = now(), |
jbe@67 | 161 "latest_snapshot_event" = 'periodic', |
jbe@67 | 162 "population" = ( |
jbe@67 | 163 SELECT coalesce(sum("weight"), 0) |
jbe@67 | 164 FROM "direct_population_snapshot" |
jbe@67 | 165 WHERE "issue_id" = "issue_id_p" |
jbe@67 | 166 AND "event" = 'periodic' |
jbe@67 | 167 ), |
jbe@67 | 168 "vote_now" = ( |
jbe@67 | 169 SELECT coalesce(sum("weight"), 0) |
jbe@67 | 170 FROM "direct_interest_snapshot" |
jbe@67 | 171 WHERE "issue_id" = "issue_id_p" |
jbe@67 | 172 AND "event" = 'periodic' |
jbe@67 | 173 AND "voting_requested" = TRUE |
jbe@67 | 174 ), |
jbe@67 | 175 "vote_later" = ( |
jbe@67 | 176 SELECT coalesce(sum("weight"), 0) |
jbe@67 | 177 FROM "direct_interest_snapshot" |
jbe@67 | 178 WHERE "issue_id" = "issue_id_p" |
jbe@67 | 179 AND "event" = 'periodic' |
jbe@67 | 180 AND "voting_requested" = FALSE |
jbe@67 | 181 ) |
jbe@67 | 182 WHERE "id" = "issue_id_p"; |
jbe@67 | 183 FOR "initiative_id_v" IN |
jbe@67 | 184 SELECT "id" FROM "initiative" WHERE "issue_id" = "issue_id_p" |
jbe@67 | 185 LOOP |
jbe@67 | 186 UPDATE "initiative" SET |
jbe@67 | 187 "supporter_count" = ( |
jbe@67 | 188 SELECT coalesce(sum("di"."weight"), 0) |
jbe@67 | 189 FROM "direct_interest_snapshot" AS "di" |
jbe@67 | 190 JOIN "direct_supporter_snapshot" AS "ds" |
jbe@67 | 191 ON "di"."member_id" = "ds"."member_id" |
jbe@67 | 192 WHERE "di"."issue_id" = "issue_id_p" |
jbe@67 | 193 AND "di"."event" = 'periodic' |
jbe@67 | 194 AND "ds"."initiative_id" = "initiative_id_v" |
jbe@67 | 195 AND "ds"."event" = 'periodic' |
jbe@67 | 196 ), |
jbe@67 | 197 "informed_supporter_count" = ( |
jbe@67 | 198 SELECT coalesce(sum("di"."weight"), 0) |
jbe@67 | 199 FROM "direct_interest_snapshot" AS "di" |
jbe@67 | 200 JOIN "direct_supporter_snapshot" AS "ds" |
jbe@67 | 201 ON "di"."member_id" = "ds"."member_id" |
jbe@67 | 202 WHERE "di"."issue_id" = "issue_id_p" |
jbe@67 | 203 AND "di"."event" = 'periodic' |
jbe@67 | 204 AND "ds"."initiative_id" = "initiative_id_v" |
jbe@67 | 205 AND "ds"."event" = 'periodic' |
jbe@67 | 206 AND "ds"."informed" |
jbe@67 | 207 ), |
jbe@67 | 208 "satisfied_supporter_count" = ( |
jbe@67 | 209 SELECT coalesce(sum("di"."weight"), 0) |
jbe@67 | 210 FROM "direct_interest_snapshot" AS "di" |
jbe@67 | 211 JOIN "direct_supporter_snapshot" AS "ds" |
jbe@67 | 212 ON "di"."member_id" = "ds"."member_id" |
jbe@67 | 213 WHERE "di"."issue_id" = "issue_id_p" |
jbe@67 | 214 AND "di"."event" = 'periodic' |
jbe@67 | 215 AND "ds"."initiative_id" = "initiative_id_v" |
jbe@67 | 216 AND "ds"."event" = 'periodic' |
jbe@67 | 217 AND "ds"."satisfied" |
jbe@67 | 218 ), |
jbe@67 | 219 "satisfied_informed_supporter_count" = ( |
jbe@67 | 220 SELECT coalesce(sum("di"."weight"), 0) |
jbe@67 | 221 FROM "direct_interest_snapshot" AS "di" |
jbe@67 | 222 JOIN "direct_supporter_snapshot" AS "ds" |
jbe@67 | 223 ON "di"."member_id" = "ds"."member_id" |
jbe@67 | 224 WHERE "di"."issue_id" = "issue_id_p" |
jbe@67 | 225 AND "di"."event" = 'periodic' |
jbe@67 | 226 AND "ds"."initiative_id" = "initiative_id_v" |
jbe@67 | 227 AND "ds"."event" = 'periodic' |
jbe@67 | 228 AND "ds"."informed" |
jbe@67 | 229 AND "ds"."satisfied" |
jbe@67 | 230 ) |
jbe@67 | 231 WHERE "id" = "initiative_id_v"; |
jbe@67 | 232 FOR "suggestion_id_v" IN |
jbe@67 | 233 SELECT "id" FROM "suggestion" |
jbe@67 | 234 WHERE "initiative_id" = "initiative_id_v" |
jbe@67 | 235 LOOP |
jbe@67 | 236 UPDATE "suggestion" SET |
jbe@67 | 237 "minus2_unfulfilled_count" = ( |
jbe@67 | 238 SELECT coalesce(sum("snapshot"."weight"), 0) |
jbe@67 | 239 FROM "issue" CROSS JOIN "opinion" |
jbe@67 | 240 JOIN "direct_interest_snapshot" AS "snapshot" |
jbe@67 | 241 ON "snapshot"."issue_id" = "issue"."id" |
jbe@67 | 242 AND "snapshot"."event" = "issue"."latest_snapshot_event" |
jbe@67 | 243 AND "snapshot"."member_id" = "opinion"."member_id" |
jbe@67 | 244 WHERE "issue"."id" = "issue_id_p" |
jbe@67 | 245 AND "opinion"."suggestion_id" = "suggestion_id_v" |
jbe@67 | 246 AND "opinion"."degree" = -2 |
jbe@67 | 247 AND "opinion"."fulfilled" = FALSE |
jbe@67 | 248 ), |
jbe@67 | 249 "minus2_fulfilled_count" = ( |
jbe@67 | 250 SELECT coalesce(sum("snapshot"."weight"), 0) |
jbe@67 | 251 FROM "issue" CROSS JOIN "opinion" |
jbe@67 | 252 JOIN "direct_interest_snapshot" AS "snapshot" |
jbe@67 | 253 ON "snapshot"."issue_id" = "issue"."id" |
jbe@67 | 254 AND "snapshot"."event" = "issue"."latest_snapshot_event" |
jbe@67 | 255 AND "snapshot"."member_id" = "opinion"."member_id" |
jbe@67 | 256 WHERE "issue"."id" = "issue_id_p" |
jbe@67 | 257 AND "opinion"."suggestion_id" = "suggestion_id_v" |
jbe@67 | 258 AND "opinion"."degree" = -2 |
jbe@67 | 259 AND "opinion"."fulfilled" = TRUE |
jbe@67 | 260 ), |
jbe@67 | 261 "minus1_unfulfilled_count" = ( |
jbe@67 | 262 SELECT coalesce(sum("snapshot"."weight"), 0) |
jbe@67 | 263 FROM "issue" CROSS JOIN "opinion" |
jbe@67 | 264 JOIN "direct_interest_snapshot" AS "snapshot" |
jbe@67 | 265 ON "snapshot"."issue_id" = "issue"."id" |
jbe@67 | 266 AND "snapshot"."event" = "issue"."latest_snapshot_event" |
jbe@67 | 267 AND "snapshot"."member_id" = "opinion"."member_id" |
jbe@67 | 268 WHERE "issue"."id" = "issue_id_p" |
jbe@67 | 269 AND "opinion"."suggestion_id" = "suggestion_id_v" |
jbe@67 | 270 AND "opinion"."degree" = -1 |
jbe@67 | 271 AND "opinion"."fulfilled" = FALSE |
jbe@67 | 272 ), |
jbe@67 | 273 "minus1_fulfilled_count" = ( |
jbe@67 | 274 SELECT coalesce(sum("snapshot"."weight"), 0) |
jbe@67 | 275 FROM "issue" CROSS JOIN "opinion" |
jbe@67 | 276 JOIN "direct_interest_snapshot" AS "snapshot" |
jbe@67 | 277 ON "snapshot"."issue_id" = "issue"."id" |
jbe@67 | 278 AND "snapshot"."event" = "issue"."latest_snapshot_event" |
jbe@67 | 279 AND "snapshot"."member_id" = "opinion"."member_id" |
jbe@67 | 280 WHERE "issue"."id" = "issue_id_p" |
jbe@67 | 281 AND "opinion"."suggestion_id" = "suggestion_id_v" |
jbe@67 | 282 AND "opinion"."degree" = -1 |
jbe@67 | 283 AND "opinion"."fulfilled" = TRUE |
jbe@67 | 284 ), |
jbe@67 | 285 "plus1_unfulfilled_count" = ( |
jbe@67 | 286 SELECT coalesce(sum("snapshot"."weight"), 0) |
jbe@67 | 287 FROM "issue" CROSS JOIN "opinion" |
jbe@67 | 288 JOIN "direct_interest_snapshot" AS "snapshot" |
jbe@67 | 289 ON "snapshot"."issue_id" = "issue"."id" |
jbe@67 | 290 AND "snapshot"."event" = "issue"."latest_snapshot_event" |
jbe@67 | 291 AND "snapshot"."member_id" = "opinion"."member_id" |
jbe@67 | 292 WHERE "issue"."id" = "issue_id_p" |
jbe@67 | 293 AND "opinion"."suggestion_id" = "suggestion_id_v" |
jbe@67 | 294 AND "opinion"."degree" = 1 |
jbe@67 | 295 AND "opinion"."fulfilled" = FALSE |
jbe@67 | 296 ), |
jbe@67 | 297 "plus1_fulfilled_count" = ( |
jbe@67 | 298 SELECT coalesce(sum("snapshot"."weight"), 0) |
jbe@67 | 299 FROM "issue" CROSS JOIN "opinion" |
jbe@67 | 300 JOIN "direct_interest_snapshot" AS "snapshot" |
jbe@67 | 301 ON "snapshot"."issue_id" = "issue"."id" |
jbe@67 | 302 AND "snapshot"."event" = "issue"."latest_snapshot_event" |
jbe@67 | 303 AND "snapshot"."member_id" = "opinion"."member_id" |
jbe@67 | 304 WHERE "issue"."id" = "issue_id_p" |
jbe@67 | 305 AND "opinion"."suggestion_id" = "suggestion_id_v" |
jbe@67 | 306 AND "opinion"."degree" = 1 |
jbe@67 | 307 AND "opinion"."fulfilled" = TRUE |
jbe@67 | 308 ), |
jbe@67 | 309 "plus2_unfulfilled_count" = ( |
jbe@67 | 310 SELECT coalesce(sum("snapshot"."weight"), 0) |
jbe@67 | 311 FROM "issue" CROSS JOIN "opinion" |
jbe@67 | 312 JOIN "direct_interest_snapshot" AS "snapshot" |
jbe@67 | 313 ON "snapshot"."issue_id" = "issue"."id" |
jbe@67 | 314 AND "snapshot"."event" = "issue"."latest_snapshot_event" |
jbe@67 | 315 AND "snapshot"."member_id" = "opinion"."member_id" |
jbe@67 | 316 WHERE "issue"."id" = "issue_id_p" |
jbe@67 | 317 AND "opinion"."suggestion_id" = "suggestion_id_v" |
jbe@67 | 318 AND "opinion"."degree" = 2 |
jbe@67 | 319 AND "opinion"."fulfilled" = FALSE |
jbe@67 | 320 ), |
jbe@67 | 321 "plus2_fulfilled_count" = ( |
jbe@67 | 322 SELECT coalesce(sum("snapshot"."weight"), 0) |
jbe@67 | 323 FROM "issue" CROSS JOIN "opinion" |
jbe@67 | 324 JOIN "direct_interest_snapshot" AS "snapshot" |
jbe@67 | 325 ON "snapshot"."issue_id" = "issue"."id" |
jbe@67 | 326 AND "snapshot"."event" = "issue"."latest_snapshot_event" |
jbe@67 | 327 AND "snapshot"."member_id" = "opinion"."member_id" |
jbe@67 | 328 WHERE "issue"."id" = "issue_id_p" |
jbe@67 | 329 AND "opinion"."suggestion_id" = "suggestion_id_v" |
jbe@67 | 330 AND "opinion"."degree" = 2 |
jbe@67 | 331 AND "opinion"."fulfilled" = TRUE |
jbe@67 | 332 ) |
jbe@67 | 333 WHERE "suggestion"."id" = "suggestion_id_v"; |
jbe@67 | 334 END LOOP; |
jbe@67 | 335 END LOOP; |
jbe@67 | 336 RETURN; |
jbe@67 | 337 END; |
jbe@67 | 338 $$; |
jbe@67 | 339 |
jbe@67 | 340 CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE) |
jbe@67 | 341 RETURNS VOID |
jbe@67 | 342 LANGUAGE 'plpgsql' VOLATILE AS $$ |
jbe@67 | 343 DECLARE |
jbe@67 | 344 "issue_row" "issue"%ROWTYPE; |
jbe@67 | 345 "member_id_v" "member"."id"%TYPE; |
jbe@67 | 346 BEGIN |
jbe@67 | 347 PERFORM "lock_issue"("issue_id_p"); |
jbe@67 | 348 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"; |
jbe@67 | 349 DELETE FROM "delegating_voter" |
jbe@67 | 350 WHERE "issue_id" = "issue_id_p"; |
jbe@67 | 351 DELETE FROM "direct_voter" |
jbe@67 | 352 WHERE "issue_id" = "issue_id_p" |
jbe@67 | 353 AND "autoreject" = TRUE; |
jbe@67 | 354 DELETE FROM "direct_voter" USING "member" |
jbe@67 | 355 WHERE "direct_voter"."member_id" = "member"."id" |
jbe@67 | 356 AND "direct_voter"."issue_id" = "issue_id_p" |
jbe@67 | 357 AND "member"."active" = FALSE; |
jbe@67 | 358 UPDATE "direct_voter" SET "weight" = 1 |
jbe@67 | 359 WHERE "issue_id" = "issue_id_p"; |
jbe@67 | 360 PERFORM "add_vote_delegations"("issue_id_p"); |
jbe@67 | 361 FOR "member_id_v" IN |
jbe@67 | 362 SELECT "interest"."member_id" |
jbe@67 | 363 FROM "interest" |
jbe@67 | 364 LEFT JOIN "direct_voter" |
jbe@67 | 365 ON "interest"."member_id" = "direct_voter"."member_id" |
jbe@67 | 366 AND "interest"."issue_id" = "direct_voter"."issue_id" |
jbe@67 | 367 LEFT JOIN "delegating_voter" |
jbe@67 | 368 ON "interest"."member_id" = "delegating_voter"."member_id" |
jbe@67 | 369 AND "interest"."issue_id" = "delegating_voter"."issue_id" |
jbe@67 | 370 WHERE "interest"."issue_id" = "issue_id_p" |
jbe@67 | 371 AND "interest"."autoreject" = TRUE |
jbe@67 | 372 AND "direct_voter"."member_id" ISNULL |
jbe@67 | 373 AND "delegating_voter"."member_id" ISNULL |
jbe@67 | 374 UNION SELECT "membership"."member_id" |
jbe@67 | 375 FROM "membership" |
jbe@67 | 376 LEFT JOIN "interest" |
jbe@67 | 377 ON "membership"."member_id" = "interest"."member_id" |
jbe@67 | 378 AND "interest"."issue_id" = "issue_id_p" |
jbe@67 | 379 LEFT JOIN "direct_voter" |
jbe@67 | 380 ON "membership"."member_id" = "direct_voter"."member_id" |
jbe@67 | 381 AND "direct_voter"."issue_id" = "issue_id_p" |
jbe@67 | 382 LEFT JOIN "delegating_voter" |
jbe@67 | 383 ON "membership"."member_id" = "delegating_voter"."member_id" |
jbe@67 | 384 AND "delegating_voter"."issue_id" = "issue_id_p" |
jbe@67 | 385 WHERE "membership"."area_id" = "issue_row"."area_id" |
jbe@67 | 386 AND "membership"."autoreject" = TRUE |
jbe@67 | 387 AND "interest"."autoreject" ISNULL |
jbe@67 | 388 AND "direct_voter"."member_id" ISNULL |
jbe@67 | 389 AND "delegating_voter"."member_id" ISNULL |
jbe@67 | 390 LOOP |
jbe@67 | 391 INSERT INTO "direct_voter" |
jbe@67 | 392 ("member_id", "issue_id", "weight", "autoreject") VALUES |
jbe@67 | 393 ("member_id_v", "issue_id_p", 1, TRUE); |
jbe@67 | 394 INSERT INTO "vote" ( |
jbe@67 | 395 "member_id", |
jbe@67 | 396 "issue_id", |
jbe@67 | 397 "initiative_id", |
jbe@67 | 398 "grade" |
jbe@67 | 399 ) SELECT |
jbe@67 | 400 "member_id_v" AS "member_id", |
jbe@67 | 401 "issue_id_p" AS "issue_id", |
jbe@67 | 402 "id" AS "initiative_id", |
jbe@67 | 403 -1 AS "grade" |
jbe@67 | 404 FROM "initiative" WHERE "issue_id" = "issue_id_p"; |
jbe@67 | 405 END LOOP; |
jbe@67 | 406 PERFORM "add_vote_delegations"("issue_id_p"); |
jbe@67 | 407 UPDATE "issue" SET |
jbe@67 | 408 "closed" = now(), |
jbe@67 | 409 "voter_count" = ( |
jbe@67 | 410 SELECT coalesce(sum("weight"), 0) |
jbe@67 | 411 FROM "direct_voter" WHERE "issue_id" = "issue_id_p" |
jbe@67 | 412 ) |
jbe@67 | 413 WHERE "id" = "issue_id_p"; |
jbe@67 | 414 UPDATE "initiative" SET |
jbe@67 | 415 "positive_votes" = "vote_counts"."positive_votes", |
jbe@67 | 416 "negative_votes" = "vote_counts"."negative_votes", |
jbe@67 | 417 "agreed" = CASE WHEN "majority_strict" THEN |
jbe@67 | 418 "vote_counts"."positive_votes" * "majority_den" > |
jbe@67 | 419 "majority_num" * |
jbe@67 | 420 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes") |
jbe@67 | 421 ELSE |
jbe@67 | 422 "vote_counts"."positive_votes" * "majority_den" >= |
jbe@67 | 423 "majority_num" * |
jbe@67 | 424 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes") |
jbe@67 | 425 END |
jbe@67 | 426 FROM |
jbe@67 | 427 ( SELECT |
jbe@67 | 428 "initiative"."id" AS "initiative_id", |
jbe@67 | 429 coalesce( |
jbe@67 | 430 sum( |
jbe@67 | 431 CASE WHEN "grade" > 0 THEN "direct_voter"."weight" ELSE 0 END |
jbe@67 | 432 ), |
jbe@67 | 433 0 |
jbe@67 | 434 ) AS "positive_votes", |
jbe@67 | 435 coalesce( |
jbe@67 | 436 sum( |
jbe@67 | 437 CASE WHEN "grade" < 0 THEN "direct_voter"."weight" ELSE 0 END |
jbe@67 | 438 ), |
jbe@67 | 439 0 |
jbe@67 | 440 ) AS "negative_votes" |
jbe@67 | 441 FROM "initiative" |
jbe@67 | 442 JOIN "issue" ON "initiative"."issue_id" = "issue"."id" |
jbe@67 | 443 JOIN "policy" ON "issue"."policy_id" = "policy"."id" |
jbe@67 | 444 LEFT JOIN "direct_voter" |
jbe@67 | 445 ON "direct_voter"."issue_id" = "initiative"."issue_id" |
jbe@67 | 446 LEFT JOIN "vote" |
jbe@67 | 447 ON "vote"."initiative_id" = "initiative"."id" |
jbe@67 | 448 AND "vote"."member_id" = "direct_voter"."member_id" |
jbe@67 | 449 WHERE "initiative"."issue_id" = "issue_id_p" |
jbe@67 | 450 AND "initiative"."admitted" -- NOTE: NULL case is handled too |
jbe@67 | 451 GROUP BY "initiative"."id" |
jbe@67 | 452 ) AS "vote_counts", |
jbe@67 | 453 "issue", |
jbe@67 | 454 "policy" |
jbe@67 | 455 WHERE "vote_counts"."initiative_id" = "initiative"."id" |
jbe@67 | 456 AND "issue"."id" = "initiative"."issue_id" |
jbe@67 | 457 AND "policy"."id" = "issue"."policy_id"; |
jbe@67 | 458 -- NOTE: "closed" column of issue must be set at this point |
jbe@67 | 459 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p"; |
jbe@67 | 460 INSERT INTO "battle" ( |
jbe@67 | 461 "issue_id", |
jbe@67 | 462 "winning_initiative_id", "losing_initiative_id", |
jbe@67 | 463 "count" |
jbe@67 | 464 ) SELECT |
jbe@67 | 465 "issue_id", |
jbe@67 | 466 "winning_initiative_id", "losing_initiative_id", |
jbe@67 | 467 "count" |
jbe@67 | 468 FROM "battle_view" WHERE "issue_id" = "issue_id_p"; |
jbe@67 | 469 END; |
jbe@67 | 470 $$; |
jbe@67 | 471 |
jbe@67 | 472 CREATE OR REPLACE FUNCTION "check_issue" |
jbe@67 | 473 ( "issue_id_p" "issue"."id"%TYPE ) |
jbe@67 | 474 RETURNS VOID |
jbe@67 | 475 LANGUAGE 'plpgsql' VOLATILE AS $$ |
jbe@67 | 476 DECLARE |
jbe@67 | 477 "issue_row" "issue"%ROWTYPE; |
jbe@67 | 478 "policy_row" "policy"%ROWTYPE; |
jbe@67 | 479 "voting_requested_v" BOOLEAN; |
jbe@67 | 480 BEGIN |
jbe@67 | 481 PERFORM "lock_issue"("issue_id_p"); |
jbe@67 | 482 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"; |
jbe@67 | 483 -- only process open issues: |
jbe@67 | 484 IF "issue_row"."closed" ISNULL THEN |
jbe@67 | 485 SELECT * INTO "policy_row" FROM "policy" |
jbe@67 | 486 WHERE "id" = "issue_row"."policy_id"; |
jbe@67 | 487 -- create a snapshot, unless issue is already fully frozen: |
jbe@67 | 488 IF "issue_row"."fully_frozen" ISNULL THEN |
jbe@67 | 489 PERFORM "create_snapshot"("issue_id_p"); |
jbe@67 | 490 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"; |
jbe@67 | 491 END IF; |
jbe@67 | 492 -- eventually close or accept issues, which have not been accepted: |
jbe@67 | 493 IF "issue_row"."accepted" ISNULL THEN |
jbe@67 | 494 IF EXISTS ( |
jbe@67 | 495 SELECT NULL FROM "initiative" |
jbe@67 | 496 WHERE "issue_id" = "issue_id_p" |
jbe@67 | 497 AND "supporter_count" > 0 |
jbe@67 | 498 AND "supporter_count" * "policy_row"."issue_quorum_den" |
jbe@67 | 499 >= "issue_row"."population" * "policy_row"."issue_quorum_num" |
jbe@67 | 500 ) THEN |
jbe@67 | 501 -- accept issues, if supporter count is high enough |
jbe@67 | 502 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission'); |
jbe@67 | 503 "issue_row"."accepted" = now(); -- NOTE: "issue_row" used later |
jbe@67 | 504 UPDATE "issue" SET "accepted" = "issue_row"."accepted" |
jbe@67 | 505 WHERE "id" = "issue_row"."id"; |
jbe@67 | 506 ELSIF |
jbe@67 | 507 now() >= "issue_row"."created" + "issue_row"."admission_time" |
jbe@67 | 508 THEN |
jbe@67 | 509 -- close issues, if admission time has expired |
jbe@67 | 510 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission'); |
jbe@67 | 511 UPDATE "issue" SET "closed" = now() |
jbe@67 | 512 WHERE "id" = "issue_row"."id"; |
jbe@67 | 513 END IF; |
jbe@67 | 514 END IF; |
jbe@67 | 515 -- eventually half freeze issues: |
jbe@67 | 516 IF |
jbe@67 | 517 -- NOTE: issue can't be closed at this point, if it has been accepted |
jbe@67 | 518 "issue_row"."accepted" NOTNULL AND |
jbe@67 | 519 "issue_row"."half_frozen" ISNULL |
jbe@67 | 520 THEN |
jbe@67 | 521 SELECT |
jbe@67 | 522 CASE |
jbe@67 | 523 WHEN "vote_now" * 2 > "issue_row"."population" THEN |
jbe@67 | 524 TRUE |
jbe@67 | 525 WHEN "vote_later" * 2 > "issue_row"."population" THEN |
jbe@67 | 526 FALSE |
jbe@67 | 527 ELSE NULL |
jbe@67 | 528 END |
jbe@67 | 529 INTO "voting_requested_v" |
jbe@67 | 530 FROM "issue" WHERE "id" = "issue_id_p"; |
jbe@67 | 531 IF |
jbe@67 | 532 "voting_requested_v" OR ( |
jbe@67 | 533 "voting_requested_v" ISNULL AND |
jbe@67 | 534 now() >= "issue_row"."accepted" + "issue_row"."discussion_time" |
jbe@67 | 535 ) |
jbe@67 | 536 THEN |
jbe@67 | 537 PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze'); |
jbe@67 | 538 "issue_row"."half_frozen" = now(); -- NOTE: "issue_row" used later |
jbe@67 | 539 UPDATE "issue" SET "half_frozen" = "issue_row"."half_frozen" |
jbe@67 | 540 WHERE "id" = "issue_row"."id"; |
jbe@67 | 541 END IF; |
jbe@67 | 542 END IF; |
jbe@67 | 543 -- close issues after some time, if all initiatives have been revoked: |
jbe@67 | 544 IF |
jbe@67 | 545 "issue_row"."closed" ISNULL AND |
jbe@67 | 546 NOT EXISTS ( |
jbe@67 | 547 -- all initiatives are revoked |
jbe@67 | 548 SELECT NULL FROM "initiative" |
jbe@67 | 549 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL |
jbe@67 | 550 ) AND ( |
jbe@67 | 551 NOT EXISTS ( |
jbe@67 | 552 -- and no initiatives have been revoked lately |
jbe@67 | 553 SELECT NULL FROM "initiative" |
jbe@67 | 554 WHERE "issue_id" = "issue_id_p" |
jbe@67 | 555 AND now() < "revoked" + "issue_row"."verification_time" |
jbe@67 | 556 ) OR ( |
jbe@67 | 557 -- or verification time has elapsed |
jbe@67 | 558 "issue_row"."half_frozen" NOTNULL AND |
jbe@67 | 559 "issue_row"."fully_frozen" ISNULL AND |
jbe@67 | 560 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time" |
jbe@67 | 561 ) |
jbe@67 | 562 ) |
jbe@67 | 563 THEN |
jbe@67 | 564 "issue_row"."closed" = now(); -- NOTE: "issue_row" used later |
jbe@67 | 565 UPDATE "issue" SET "closed" = "issue_row"."closed" |
jbe@67 | 566 WHERE "id" = "issue_row"."id"; |
jbe@67 | 567 END IF; |
jbe@67 | 568 -- fully freeze issue after verification time: |
jbe@67 | 569 IF |
jbe@67 | 570 "issue_row"."half_frozen" NOTNULL AND |
jbe@67 | 571 "issue_row"."fully_frozen" ISNULL AND |
jbe@67 | 572 "issue_row"."closed" ISNULL AND |
jbe@67 | 573 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time" |
jbe@67 | 574 THEN |
jbe@67 | 575 PERFORM "freeze_after_snapshot"("issue_id_p"); |
jbe@67 | 576 -- NOTE: "issue" might change, thus "issue_row" has to be updated below |
jbe@67 | 577 END IF; |
jbe@67 | 578 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"; |
jbe@67 | 579 -- close issue by calling close_voting(...) after voting time: |
jbe@67 | 580 IF |
jbe@67 | 581 "issue_row"."closed" ISNULL AND |
jbe@67 | 582 "issue_row"."fully_frozen" NOTNULL AND |
jbe@67 | 583 now() >= "issue_row"."fully_frozen" + "issue_row"."voting_time" |
jbe@67 | 584 THEN |
jbe@67 | 585 PERFORM "close_voting"("issue_id_p"); |
jbe@67 | 586 END IF; |
jbe@67 | 587 END IF; |
jbe@67 | 588 RETURN; |
jbe@67 | 589 END; |
jbe@67 | 590 $$; |
jbe@67 | 591 |
jbe@67 | 592 |
jbe@67 | 593 DROP FUNCTION "global_lock"(); |
jbe@67 | 594 |
jbe@67 | 595 |
jbe@67 | 596 COMMIT; |