liquid_feedback_core
annotate update/core-update.v1.2.4-v1.2.5.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 | 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; |