liquid_feedback_core

annotate update/core-update.v2.1.0-v2.2.0.sql @ 341:607e8f5d66eb

find changesets by author, revision, files, or words in the commit message
Write "NOW()" as "now()"
author jbe
date Thu Feb 21 16:40:17 2013 +0100 (2013-02-21)
parents aecc4f182602
children bab555ea2f81
rev   line source
jbe@339 1 BEGIN;
jbe@339 2
jbe@339 3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
jbe@339 4 SELECT * FROM (VALUES ('2.2.0', 2, 2, 0))
jbe@339 5 AS "subquery"("string", "major", "minor", "revision");
jbe@339 6
jbe@339 7 DROP VIEW "issue_with_ranks_missing";
jbe@339 8 DROP VIEW "open_issue"; -- recreated later
jbe@339 9 DROP VIEW "event_seen_by_member"; -- recreated later
jbe@339 10 DROP VIEW "selected_event_seen_by_member"; -- recreated later
jbe@339 11 ALTER TABLE "issue" DROP CONSTRAINT "valid_state";
jbe@339 12 ALTER TABLE "issue" DROP COLUMN "ranks_available";
jbe@339 13 ALTER TABLE "event" DROP CONSTRAINT "event_state_check";
jbe@339 14 ALTER TABLE "event" DROP CONSTRAINT "null_constraints_for_issue_state_changed"; -- recreated later
jbe@339 15 ALTER TABLE "event" DROP CONSTRAINT "null_constraints_for_initiative_creation_or_revocation_or_new_draft"; -- recreated later
jbe@339 16 ALTER TABLE "event" DROP CONSTRAINT "null_constraints_for_suggestion_creation"; -- recreated later
jbe@339 17 ALTER TYPE "issue_state" RENAME TO "issue_state_old";
jbe@339 18 CREATE TYPE "issue_state" AS ENUM (
jbe@339 19 'admission', 'discussion', 'verification', 'voting',
jbe@339 20 'canceled_revoked_before_accepted',
jbe@339 21 'canceled_issue_not_accepted',
jbe@339 22 'canceled_after_revocation_during_discussion',
jbe@339 23 'canceled_after_revocation_during_verification',
jbe@339 24 'canceled_no_initiative_admitted',
jbe@339 25 'finished_without_winner', 'finished_with_winner');
jbe@339 26 ALTER TABLE "issue" ALTER COLUMN "state" DROP DEFAULT;
jbe@339 27 ALTER TABLE "issue" ALTER COLUMN "state" TYPE "issue_state" USING "state"::text::"issue_state";
jbe@339 28 ALTER TABLE "event" ALTER COLUMN "state" TYPE "issue_state" USING "state"::text::"issue_state";
jbe@339 29 DROP TYPE "issue_state_old";
jbe@339 30 ALTER TABLE "issue" ALTER COLUMN "state" SET DEFAULT 'admission';
jbe@339 31 ALTER TABLE "issue" ADD CONSTRAINT "valid_state"
jbe@339 32 CHECK ((
jbe@339 33 ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL ) OR
jbe@339 34 ("accepted" NOTNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL ) OR
jbe@339 35 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL ) OR
jbe@339 36 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL)
jbe@339 37 ) AND (
jbe@339 38 ("state" = 'admission' AND "closed" ISNULL AND "accepted" ISNULL) OR
jbe@339 39 ("state" = 'discussion' AND "closed" ISNULL AND "accepted" NOTNULL AND "half_frozen" ISNULL) OR
jbe@339 40 ("state" = 'verification' AND "closed" ISNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL) OR
jbe@339 41 ("state" = 'voting' AND "closed" ISNULL AND "fully_frozen" NOTNULL) OR
jbe@339 42 ("state" = 'canceled_revoked_before_accepted' AND "closed" NOTNULL AND "accepted" ISNULL) OR
jbe@339 43 ("state" = 'canceled_issue_not_accepted' AND "closed" NOTNULL AND "accepted" ISNULL) OR
jbe@339 44 ("state" = 'canceled_after_revocation_during_discussion' AND "closed" NOTNULL AND "half_frozen" ISNULL) OR
jbe@339 45 ("state" = 'canceled_after_revocation_during_verification' AND "closed" NOTNULL AND "fully_frozen" ISNULL) OR
jbe@339 46 ("state" = 'canceled_no_initiative_admitted' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "closed" = "fully_frozen") OR
jbe@339 47 ("state" = 'finished_without_winner' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "closed" != "fully_frozen") OR
jbe@339 48 ("state" = 'finished_with_winner' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "closed" != "fully_frozen")
jbe@339 49 ));
jbe@339 50 ALTER TABLE "issue" ADD COLUMN "phase_finished" TIMESTAMPTZ;
jbe@339 51 COMMENT ON COLUMN "issue"."phase_finished" IS 'Set to a value NOTNULL, if the current phase has finished, but calculations are pending; No changes in this issue shall be made by the frontend or API when this value is set';
jbe@339 52 ALTER TABLE "issue" ADD CONSTRAINT "phase_finished_only_when_not_closed"
jbe@339 53 CHECK ("phase_finished" ISNULL OR "closed" ISNULL);
jbe@339 54 ALTER TABLE "event" ADD CONSTRAINT "null_constraints_for_issue_state_changed" CHECK (
jbe@339 55 "event" != 'issue_state_changed' OR (
jbe@339 56 "member_id" ISNULL AND
jbe@339 57 "issue_id" NOTNULL AND
jbe@339 58 "state" NOTNULL AND
jbe@339 59 "initiative_id" ISNULL AND
jbe@339 60 "draft_id" ISNULL AND
jbe@339 61 "suggestion_id" ISNULL ));
jbe@339 62 ALTER TABLE "event" ADD CONSTRAINT "null_constraints_for_initiative_creation_or_revocation_or_new_draft" CHECK (
jbe@339 63 "event" NOT IN (
jbe@339 64 'initiative_created_in_new_issue',
jbe@339 65 'initiative_created_in_existing_issue',
jbe@339 66 'initiative_revoked',
jbe@339 67 'new_draft_created'
jbe@339 68 ) OR (
jbe@339 69 "member_id" NOTNULL AND
jbe@339 70 "issue_id" NOTNULL AND
jbe@339 71 "state" NOTNULL AND
jbe@339 72 "initiative_id" NOTNULL AND
jbe@339 73 "draft_id" NOTNULL AND
jbe@339 74 "suggestion_id" ISNULL ));
jbe@339 75 ALTER TABLE "event" ADD CONSTRAINT "null_constraints_for_suggestion_creation" CHECK (
jbe@339 76 "event" != 'suggestion_created' OR (
jbe@339 77 "member_id" NOTNULL AND
jbe@339 78 "issue_id" NOTNULL AND
jbe@339 79 "state" NOTNULL AND
jbe@339 80 "initiative_id" NOTNULL AND
jbe@339 81 "draft_id" ISNULL AND
jbe@339 82 "suggestion_id" NOTNULL ));
jbe@339 83 CREATE VIEW "open_issue" AS
jbe@339 84 SELECT * FROM "issue" WHERE "closed" ISNULL;
jbe@339 85 COMMENT ON VIEW "open_issue" IS 'All open issues';
jbe@339 86 CREATE VIEW "event_seen_by_member" AS
jbe@339 87 SELECT
jbe@339 88 "member"."id" AS "seen_by_member_id",
jbe@339 89 CASE WHEN "event"."state" IN (
jbe@339 90 'voting',
jbe@339 91 'finished_without_winner',
jbe@339 92 'finished_with_winner'
jbe@339 93 ) THEN
jbe@339 94 'voting'::"notify_level"
jbe@339 95 ELSE
jbe@339 96 CASE WHEN "event"."state" IN (
jbe@339 97 'verification',
jbe@339 98 'canceled_after_revocation_during_verification',
jbe@339 99 'canceled_no_initiative_admitted'
jbe@339 100 ) THEN
jbe@339 101 'verification'::"notify_level"
jbe@339 102 ELSE
jbe@339 103 CASE WHEN "event"."state" IN (
jbe@339 104 'discussion',
jbe@339 105 'canceled_after_revocation_during_discussion'
jbe@339 106 ) THEN
jbe@339 107 'discussion'::"notify_level"
jbe@339 108 ELSE
jbe@339 109 'all'::"notify_level"
jbe@339 110 END
jbe@339 111 END
jbe@339 112 END AS "notify_level",
jbe@339 113 "event".*
jbe@339 114 FROM "member" CROSS JOIN "event"
jbe@339 115 LEFT JOIN "issue"
jbe@339 116 ON "event"."issue_id" = "issue"."id"
jbe@339 117 LEFT JOIN "membership"
jbe@339 118 ON "member"."id" = "membership"."member_id"
jbe@339 119 AND "issue"."area_id" = "membership"."area_id"
jbe@339 120 LEFT JOIN "interest"
jbe@339 121 ON "member"."id" = "interest"."member_id"
jbe@339 122 AND "event"."issue_id" = "interest"."issue_id"
jbe@339 123 LEFT JOIN "supporter"
jbe@339 124 ON "member"."id" = "supporter"."member_id"
jbe@339 125 AND "event"."initiative_id" = "supporter"."initiative_id"
jbe@339 126 LEFT JOIN "ignored_member"
jbe@339 127 ON "member"."id" = "ignored_member"."member_id"
jbe@339 128 AND "event"."member_id" = "ignored_member"."other_member_id"
jbe@339 129 LEFT JOIN "ignored_initiative"
jbe@339 130 ON "member"."id" = "ignored_initiative"."member_id"
jbe@339 131 AND "event"."initiative_id" = "ignored_initiative"."initiative_id"
jbe@339 132 WHERE (
jbe@339 133 "supporter"."member_id" NOTNULL OR
jbe@339 134 "interest"."member_id" NOTNULL OR
jbe@339 135 ( "membership"."member_id" NOTNULL AND
jbe@339 136 "event"."event" IN (
jbe@339 137 'issue_state_changed',
jbe@339 138 'initiative_created_in_new_issue',
jbe@339 139 'initiative_created_in_existing_issue',
jbe@339 140 'initiative_revoked' ) ) )
jbe@339 141 AND "ignored_member"."member_id" ISNULL
jbe@339 142 AND "ignored_initiative"."member_id" ISNULL;
jbe@339 143 COMMENT ON VIEW "event_seen_by_member" IS 'Events as seen by a member, depending on its memberships, interests and support, but ignoring members "notify_level"';
jbe@339 144 CREATE VIEW "selected_event_seen_by_member" AS
jbe@339 145 SELECT
jbe@339 146 "member"."id" AS "seen_by_member_id",
jbe@339 147 CASE WHEN "event"."state" IN (
jbe@339 148 'voting',
jbe@339 149 'finished_without_winner',
jbe@339 150 'finished_with_winner'
jbe@339 151 ) THEN
jbe@339 152 'voting'::"notify_level"
jbe@339 153 ELSE
jbe@339 154 CASE WHEN "event"."state" IN (
jbe@339 155 'verification',
jbe@339 156 'canceled_after_revocation_during_verification',
jbe@339 157 'canceled_no_initiative_admitted'
jbe@339 158 ) THEN
jbe@339 159 'verification'::"notify_level"
jbe@339 160 ELSE
jbe@339 161 CASE WHEN "event"."state" IN (
jbe@339 162 'discussion',
jbe@339 163 'canceled_after_revocation_during_discussion'
jbe@339 164 ) THEN
jbe@339 165 'discussion'::"notify_level"
jbe@339 166 ELSE
jbe@339 167 'all'::"notify_level"
jbe@339 168 END
jbe@339 169 END
jbe@339 170 END AS "notify_level",
jbe@339 171 "event".*
jbe@339 172 FROM "member" CROSS JOIN "event"
jbe@339 173 LEFT JOIN "issue"
jbe@339 174 ON "event"."issue_id" = "issue"."id"
jbe@339 175 LEFT JOIN "membership"
jbe@339 176 ON "member"."id" = "membership"."member_id"
jbe@339 177 AND "issue"."area_id" = "membership"."area_id"
jbe@339 178 LEFT JOIN "interest"
jbe@339 179 ON "member"."id" = "interest"."member_id"
jbe@339 180 AND "event"."issue_id" = "interest"."issue_id"
jbe@339 181 LEFT JOIN "supporter"
jbe@339 182 ON "member"."id" = "supporter"."member_id"
jbe@339 183 AND "event"."initiative_id" = "supporter"."initiative_id"
jbe@339 184 LEFT JOIN "ignored_member"
jbe@339 185 ON "member"."id" = "ignored_member"."member_id"
jbe@339 186 AND "event"."member_id" = "ignored_member"."other_member_id"
jbe@339 187 LEFT JOIN "ignored_initiative"
jbe@339 188 ON "member"."id" = "ignored_initiative"."member_id"
jbe@339 189 AND "event"."initiative_id" = "ignored_initiative"."initiative_id"
jbe@339 190 WHERE (
jbe@339 191 ( "member"."notify_level" >= 'all' ) OR
jbe@339 192 ( "member"."notify_level" >= 'voting' AND
jbe@339 193 "event"."state" IN (
jbe@339 194 'voting',
jbe@339 195 'finished_without_winner',
jbe@339 196 'finished_with_winner' ) ) OR
jbe@339 197 ( "member"."notify_level" >= 'verification' AND
jbe@339 198 "event"."state" IN (
jbe@339 199 'verification',
jbe@339 200 'canceled_after_revocation_during_verification',
jbe@339 201 'canceled_no_initiative_admitted' ) ) OR
jbe@339 202 ( "member"."notify_level" >= 'discussion' AND
jbe@339 203 "event"."state" IN (
jbe@339 204 'discussion',
jbe@339 205 'canceled_after_revocation_during_discussion' ) ) )
jbe@339 206 AND (
jbe@339 207 "supporter"."member_id" NOTNULL OR
jbe@339 208 "interest"."member_id" NOTNULL OR
jbe@339 209 ( "membership"."member_id" NOTNULL AND
jbe@339 210 "event"."event" IN (
jbe@339 211 'issue_state_changed',
jbe@339 212 'initiative_created_in_new_issue',
jbe@339 213 'initiative_created_in_existing_issue',
jbe@339 214 'initiative_revoked' ) ) )
jbe@339 215 AND "ignored_member"."member_id" ISNULL
jbe@339 216 AND "ignored_initiative"."member_id" ISNULL;
jbe@339 217 COMMENT ON VIEW "selected_event_seen_by_member" IS 'Events as seen by a member, depending on its memberships, interests, support and members "notify_level"';
jbe@339 218
jbe@339 219 ALTER TABLE "initiative" ADD COLUMN "harmonic_weight" NUMERIC(12, 3);
jbe@339 220 COMMENT ON COLUMN "initiative"."harmonic_weight" IS 'Indicates the relevancy of the initiative, calculated from the potential supporters weighted with the harmonic series to avoid a large number of clones affecting other initiative''s sorting positions too much; shall be used as secondary sorting key after "admitted" as primary sorting key';
jbe@339 221
jbe@339 222 CREATE OR REPLACE FUNCTION "write_event_issue_state_changed_trigger"()
jbe@339 223 RETURNS TRIGGER
jbe@339 224 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@339 225 BEGIN
jbe@339 226 IF NEW."state" != OLD."state" THEN
jbe@339 227 INSERT INTO "event" ("event", "issue_id", "state")
jbe@339 228 VALUES ('issue_state_changed', NEW."id", NEW."state");
jbe@339 229 END IF;
jbe@339 230 RETURN NULL;
jbe@339 231 END;
jbe@339 232 $$;
jbe@339 233
jbe@339 234 CREATE OR REPLACE FUNCTION "forbid_changes_on_closed_issue_trigger"()
jbe@339 235 RETURNS TRIGGER
jbe@339 236 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@339 237 DECLARE
jbe@339 238 "issue_id_v" "issue"."id"%TYPE;
jbe@339 239 "issue_row" "issue"%ROWTYPE;
jbe@339 240 BEGIN
jbe@339 241 IF TG_OP = 'DELETE' THEN
jbe@339 242 "issue_id_v" := OLD."issue_id";
jbe@339 243 ELSE
jbe@339 244 "issue_id_v" := NEW."issue_id";
jbe@339 245 END IF;
jbe@339 246 SELECT INTO "issue_row" * FROM "issue"
jbe@339 247 WHERE "id" = "issue_id_v" FOR SHARE;
jbe@339 248 IF "issue_row"."closed" NOTNULL THEN
jbe@339 249 IF
jbe@339 250 TG_RELID = 'direct_voter'::regclass AND
jbe@339 251 TG_OP = 'UPDATE'
jbe@339 252 THEN
jbe@339 253 IF
jbe@339 254 OLD."issue_id" = NEW."issue_id" AND
jbe@339 255 OLD."member_id" = NEW."member_id" AND
jbe@339 256 OLD."weight" = NEW."weight"
jbe@339 257 THEN
jbe@339 258 RETURN NULL; -- allows changing of voter comment
jbe@339 259 END IF;
jbe@339 260 END IF;
jbe@339 261 RAISE EXCEPTION 'Tried to modify data belonging to a closed issue.';
jbe@339 262 ELSIF
jbe@339 263 "issue_row"."state" = 'voting' AND
jbe@339 264 "issue_row"."phase_finished" NOTNULL
jbe@339 265 THEN
jbe@339 266 IF TG_RELID = 'vote'::regclass THEN
jbe@339 267 RAISE EXCEPTION 'Tried to modify data after voting has been closed.';
jbe@339 268 END IF;
jbe@339 269 END IF;
jbe@339 270 RETURN NULL;
jbe@339 271 END;
jbe@339 272 $$;
jbe@339 273
jbe@339 274 CREATE OR REPLACE VIEW "battle_view" AS
jbe@339 275 SELECT
jbe@339 276 "issue"."id" AS "issue_id",
jbe@339 277 "winning_initiative"."id" AS "winning_initiative_id",
jbe@339 278 "losing_initiative"."id" AS "losing_initiative_id",
jbe@339 279 sum(
jbe@339 280 CASE WHEN
jbe@339 281 coalesce("better_vote"."grade", 0) >
jbe@339 282 coalesce("worse_vote"."grade", 0)
jbe@339 283 THEN "direct_voter"."weight" ELSE 0 END
jbe@339 284 ) AS "count"
jbe@339 285 FROM "issue"
jbe@339 286 LEFT JOIN "direct_voter"
jbe@339 287 ON "issue"."id" = "direct_voter"."issue_id"
jbe@339 288 JOIN "battle_participant" AS "winning_initiative"
jbe@339 289 ON "issue"."id" = "winning_initiative"."issue_id"
jbe@339 290 JOIN "battle_participant" AS "losing_initiative"
jbe@339 291 ON "issue"."id" = "losing_initiative"."issue_id"
jbe@339 292 LEFT JOIN "vote" AS "better_vote"
jbe@339 293 ON "direct_voter"."member_id" = "better_vote"."member_id"
jbe@339 294 AND "winning_initiative"."id" = "better_vote"."initiative_id"
jbe@339 295 LEFT JOIN "vote" AS "worse_vote"
jbe@339 296 ON "direct_voter"."member_id" = "worse_vote"."member_id"
jbe@339 297 AND "losing_initiative"."id" = "worse_vote"."initiative_id"
jbe@339 298 WHERE "issue"."state" = 'voting'
jbe@339 299 AND "issue"."phase_finished" NOTNULL
jbe@339 300 AND (
jbe@339 301 "winning_initiative"."id" != "losing_initiative"."id" OR
jbe@339 302 ( ("winning_initiative"."id" NOTNULL AND "losing_initiative"."id" ISNULL) OR
jbe@339 303 ("winning_initiative"."id" ISNULL AND "losing_initiative"."id" NOTNULL) ) )
jbe@339 304 GROUP BY
jbe@339 305 "issue"."id",
jbe@339 306 "winning_initiative"."id",
jbe@339 307 "losing_initiative"."id";
jbe@339 308
jbe@339 309 DROP VIEW "timeline";
jbe@339 310 DROP VIEW "timeline_issue";
jbe@339 311 DROP VIEW "timeline_initiative";
jbe@339 312 DROP VIEW "timeline_draft";
jbe@339 313 DROP VIEW "timeline_suggestion";
jbe@339 314 DROP TYPE "timeline_event";
jbe@339 315
jbe@339 316 DROP TRIGGER "share_row_lock_issue" ON "initiative";
jbe@339 317 DROP TRIGGER "share_row_lock_issue" ON "interest";
jbe@339 318 DROP TRIGGER "share_row_lock_issue" ON "supporter";
jbe@339 319 DROP TRIGGER "share_row_lock_issue_via_initiative" ON "opinion";
jbe@339 320 DROP TRIGGER "share_row_lock_issue" ON "direct_voter";
jbe@339 321 DROP TRIGGER "share_row_lock_issue" ON "delegating_voter";
jbe@339 322 DROP TRIGGER "share_row_lock_issue" ON "vote";
jbe@339 323 DROP FUNCTION "share_row_lock_issue_trigger"();
jbe@339 324
jbe@339 325 CREATE FUNCTION "require_transaction_isolation"()
jbe@339 326 RETURNS VOID
jbe@339 327 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@339 328 BEGIN
jbe@339 329 IF
jbe@339 330 current_setting('transaction_isolation') NOT IN
jbe@339 331 ('repeatable read', 'serializable')
jbe@339 332 THEN
jbe@339 333 RAISE EXCEPTION 'Insufficient transaction isolation level';
jbe@339 334 END IF;
jbe@339 335 RETURN;
jbe@339 336 END;
jbe@339 337 $$;
jbe@339 338
jbe@339 339 CREATE FUNCTION "dont_require_transaction_isolation"()
jbe@339 340 RETURNS VOID
jbe@339 341 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@339 342 BEGIN
jbe@339 343 IF
jbe@339 344 current_setting('transaction_isolation') IN
jbe@339 345 ('repeatable read', 'serializable')
jbe@339 346 THEN
jbe@339 347 RAISE WARNING 'Unneccessary transaction isolation level: %',
jbe@339 348 current_setting('transaction_isolation');
jbe@339 349 END IF;
jbe@339 350 RETURN;
jbe@339 351 END;
jbe@339 352 $$;
jbe@339 353
jbe@339 354 CREATE OR REPLACE FUNCTION "check_activity"()
jbe@339 355 RETURNS VOID
jbe@339 356 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@339 357 DECLARE
jbe@339 358 "system_setting_row" "system_setting"%ROWTYPE;
jbe@339 359 BEGIN
jbe@339 360 PERFORM "dont_require_transaction_isolation"();
jbe@339 361 SELECT * INTO "system_setting_row" FROM "system_setting";
jbe@339 362 IF "system_setting_row"."member_ttl" NOTNULL THEN
jbe@339 363 UPDATE "member" SET "active" = FALSE
jbe@339 364 WHERE "active" = TRUE
jbe@339 365 AND "last_activity" < (now() - "system_setting_row"."member_ttl")::DATE;
jbe@339 366 END IF;
jbe@339 367 RETURN;
jbe@339 368 END;
jbe@339 369 $$;
jbe@339 370
jbe@339 371 CREATE OR REPLACE FUNCTION "calculate_member_counts"()
jbe@339 372 RETURNS VOID
jbe@339 373 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@339 374 BEGIN
jbe@339 375 PERFORM "require_transaction_isolation"();
jbe@339 376 DELETE FROM "member_count";
jbe@339 377 INSERT INTO "member_count" ("total_count")
jbe@339 378 SELECT "total_count" FROM "member_count_view";
jbe@339 379 UPDATE "unit" SET "member_count" = "view"."member_count"
jbe@339 380 FROM "unit_member_count" AS "view"
jbe@339 381 WHERE "view"."unit_id" = "unit"."id";
jbe@339 382 UPDATE "area" SET
jbe@339 383 "direct_member_count" = "view"."direct_member_count",
jbe@339 384 "member_weight" = "view"."member_weight"
jbe@339 385 FROM "area_member_count" AS "view"
jbe@339 386 WHERE "view"."area_id" = "area"."id";
jbe@339 387 RETURN;
jbe@339 388 END;
jbe@339 389 $$;
jbe@339 390
jbe@339 391 CREATE VIEW "remaining_harmonic_supporter_weight" AS
jbe@339 392 SELECT
jbe@339 393 "direct_interest_snapshot"."issue_id",
jbe@339 394 "direct_interest_snapshot"."event",
jbe@339 395 "direct_interest_snapshot"."member_id",
jbe@339 396 "direct_interest_snapshot"."weight" AS "weight_num",
jbe@339 397 count("initiative"."id") AS "weight_den"
jbe@339 398 FROM "issue"
jbe@339 399 JOIN "direct_interest_snapshot"
jbe@339 400 ON "issue"."id" = "direct_interest_snapshot"."issue_id"
jbe@339 401 AND "issue"."latest_snapshot_event" = "direct_interest_snapshot"."event"
jbe@339 402 JOIN "initiative"
jbe@339 403 ON "issue"."id" = "initiative"."issue_id"
jbe@339 404 AND "initiative"."harmonic_weight" ISNULL
jbe@339 405 JOIN "direct_supporter_snapshot"
jbe@339 406 ON "initiative"."id" = "direct_supporter_snapshot"."initiative_id"
jbe@339 407 AND "direct_interest_snapshot"."event" = "direct_supporter_snapshot"."event"
jbe@339 408 AND "direct_interest_snapshot"."member_id" = "direct_supporter_snapshot"."member_id"
jbe@339 409 AND (
jbe@339 410 "direct_supporter_snapshot"."satisfied" = TRUE OR
jbe@339 411 coalesce("initiative"."admitted", FALSE) = FALSE
jbe@339 412 )
jbe@339 413 GROUP BY
jbe@339 414 "direct_interest_snapshot"."issue_id",
jbe@339 415 "direct_interest_snapshot"."event",
jbe@339 416 "direct_interest_snapshot"."member_id",
jbe@339 417 "direct_interest_snapshot"."weight";
jbe@339 418 COMMENT ON VIEW "remaining_harmonic_supporter_weight" IS 'Helper view for function "set_harmonic_initiative_weights"';
jbe@339 419
jbe@339 420 CREATE VIEW "remaining_harmonic_initiative_weight_summands" AS
jbe@339 421 SELECT
jbe@339 422 "initiative"."issue_id",
jbe@339 423 "initiative"."id" AS "initiative_id",
jbe@339 424 "initiative"."admitted",
jbe@339 425 sum("remaining_harmonic_supporter_weight"."weight_num") AS "weight_num",
jbe@339 426 "remaining_harmonic_supporter_weight"."weight_den"
jbe@339 427 FROM "remaining_harmonic_supporter_weight"
jbe@339 428 JOIN "initiative"
jbe@339 429 ON "remaining_harmonic_supporter_weight"."issue_id" = "initiative"."issue_id"
jbe@339 430 AND "initiative"."harmonic_weight" ISNULL
jbe@339 431 JOIN "direct_supporter_snapshot"
jbe@339 432 ON "initiative"."id" = "direct_supporter_snapshot"."initiative_id"
jbe@339 433 AND "remaining_harmonic_supporter_weight"."event" = "direct_supporter_snapshot"."event"
jbe@339 434 AND "remaining_harmonic_supporter_weight"."member_id" = "direct_supporter_snapshot"."member_id"
jbe@339 435 AND (
jbe@339 436 "direct_supporter_snapshot"."satisfied" = TRUE OR
jbe@339 437 coalesce("initiative"."admitted", FALSE) = FALSE
jbe@339 438 )
jbe@339 439 GROUP BY
jbe@339 440 "initiative"."issue_id",
jbe@339 441 "initiative"."id",
jbe@339 442 "initiative"."admitted",
jbe@339 443 "remaining_harmonic_supporter_weight"."weight_den";
jbe@339 444 COMMENT ON VIEW "remaining_harmonic_initiative_weight_summands" IS 'Helper view for function "set_harmonic_initiative_weights"';
jbe@339 445
jbe@339 446 CREATE FUNCTION "set_harmonic_initiative_weights"
jbe@339 447 ( "issue_id_p" "issue"."id"%TYPE )
jbe@339 448 RETURNS VOID
jbe@339 449 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@339 450 DECLARE
jbe@339 451 "weight_row" "remaining_harmonic_initiative_weight_summands"%ROWTYPE;
jbe@339 452 "i" INT4;
jbe@339 453 "count_v" INT4;
jbe@339 454 "summand_v" FLOAT;
jbe@339 455 "id_ary" INT4[];
jbe@339 456 "weight_ary" FLOAT[];
jbe@339 457 "min_weight_v" FLOAT;
jbe@339 458 BEGIN
jbe@339 459 PERFORM "require_transaction_isolation"();
jbe@339 460 UPDATE "initiative" SET "harmonic_weight" = NULL
jbe@339 461 WHERE "issue_id" = "issue_id_p";
jbe@339 462 LOOP
jbe@339 463 "min_weight_v" := NULL;
jbe@339 464 "i" := 0;
jbe@339 465 "count_v" := 0;
jbe@339 466 FOR "weight_row" IN
jbe@339 467 SELECT * FROM "remaining_harmonic_initiative_weight_summands"
jbe@339 468 WHERE "issue_id" = "issue_id_p"
jbe@339 469 AND (
jbe@339 470 coalesce("admitted", FALSE) = FALSE OR NOT EXISTS (
jbe@339 471 SELECT NULL FROM "initiative"
jbe@339 472 WHERE "issue_id" = "issue_id_p"
jbe@339 473 AND "harmonic_weight" ISNULL
jbe@339 474 AND coalesce("admitted", FALSE) = FALSE
jbe@339 475 )
jbe@339 476 )
jbe@339 477 ORDER BY "initiative_id" DESC, "weight_den" DESC
jbe@339 478 -- NOTE: non-admitted initiatives placed first (at last positions),
jbe@339 479 -- latest initiatives treated worse in case of tie
jbe@339 480 LOOP
jbe@339 481 "summand_v" := "weight_row"."weight_num"::FLOAT / "weight_row"."weight_den"::FLOAT;
jbe@339 482 IF "i" = 0 OR "weight_row"."initiative_id" != "id_ary"["i"] THEN
jbe@339 483 "i" := "i" + 1;
jbe@339 484 "count_v" := "i";
jbe@339 485 "id_ary"["i"] := "weight_row"."initiative_id";
jbe@339 486 "weight_ary"["i"] := "summand_v";
jbe@339 487 ELSE
jbe@339 488 "weight_ary"["i"] := "weight_ary"["i"] + "summand_v";
jbe@339 489 END IF;
jbe@339 490 END LOOP;
jbe@339 491 EXIT WHEN "count_v" = 0;
jbe@339 492 "i" := 1;
jbe@339 493 LOOP
jbe@339 494 "weight_ary"["i"] := "weight_ary"["i"]::NUMERIC(18,9)::NUMERIC(12,3);
jbe@339 495 IF "min_weight_v" ISNULL OR "weight_ary"["i"] < "min_weight_v" THEN
jbe@339 496 "min_weight_v" := "weight_ary"["i"];
jbe@339 497 END IF;
jbe@339 498 "i" := "i" + 1;
jbe@339 499 EXIT WHEN "i" > "count_v";
jbe@339 500 END LOOP;
jbe@339 501 "i" := 1;
jbe@339 502 LOOP
jbe@339 503 IF "weight_ary"["i"] = "min_weight_v" THEN
jbe@339 504 UPDATE "initiative" SET "harmonic_weight" = "min_weight_v"
jbe@339 505 WHERE "id" = "id_ary"["i"];
jbe@339 506 EXIT;
jbe@339 507 END IF;
jbe@339 508 "i" := "i" + 1;
jbe@339 509 END LOOP;
jbe@339 510 END LOOP;
jbe@339 511 UPDATE "initiative" SET "harmonic_weight" = 0
jbe@339 512 WHERE "issue_id" = "issue_id_p" AND "harmonic_weight" ISNULL;
jbe@339 513 END;
jbe@339 514 $$;
jbe@339 515 COMMENT ON FUNCTION "set_harmonic_initiative_weights"
jbe@339 516 ( "issue"."id"%TYPE )
jbe@339 517 IS 'Calculates and sets "harmonic_weight" of initiatives in a given issue';
jbe@339 518
jbe@339 519 CREATE OR REPLACE FUNCTION "weight_of_added_delegations_for_population_snapshot"
jbe@339 520 ( "issue_id_p" "issue"."id"%TYPE,
jbe@339 521 "member_id_p" "member"."id"%TYPE,
jbe@339 522 "delegate_member_ids_p" "delegating_population_snapshot"."delegate_member_ids"%TYPE )
jbe@339 523 RETURNS "direct_population_snapshot"."weight"%TYPE
jbe@339 524 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@339 525 DECLARE
jbe@339 526 "issue_delegation_row" "issue_delegation"%ROWTYPE;
jbe@339 527 "delegate_member_ids_v" "delegating_population_snapshot"."delegate_member_ids"%TYPE;
jbe@339 528 "weight_v" INT4;
jbe@339 529 "sub_weight_v" INT4;
jbe@339 530 BEGIN
jbe@339 531 PERFORM "require_transaction_isolation"();
jbe@339 532 "weight_v" := 0;
jbe@339 533 FOR "issue_delegation_row" IN
jbe@339 534 SELECT * FROM "issue_delegation"
jbe@339 535 WHERE "trustee_id" = "member_id_p"
jbe@339 536 AND "issue_id" = "issue_id_p"
jbe@339 537 LOOP
jbe@339 538 IF NOT EXISTS (
jbe@339 539 SELECT NULL FROM "direct_population_snapshot"
jbe@339 540 WHERE "issue_id" = "issue_id_p"
jbe@339 541 AND "event" = 'periodic'
jbe@339 542 AND "member_id" = "issue_delegation_row"."truster_id"
jbe@339 543 ) AND NOT EXISTS (
jbe@339 544 SELECT NULL FROM "delegating_population_snapshot"
jbe@339 545 WHERE "issue_id" = "issue_id_p"
jbe@339 546 AND "event" = 'periodic'
jbe@339 547 AND "member_id" = "issue_delegation_row"."truster_id"
jbe@339 548 ) THEN
jbe@339 549 "delegate_member_ids_v" :=
jbe@339 550 "member_id_p" || "delegate_member_ids_p";
jbe@339 551 INSERT INTO "delegating_population_snapshot" (
jbe@339 552 "issue_id",
jbe@339 553 "event",
jbe@339 554 "member_id",
jbe@339 555 "scope",
jbe@339 556 "delegate_member_ids"
jbe@339 557 ) VALUES (
jbe@339 558 "issue_id_p",
jbe@339 559 'periodic',
jbe@339 560 "issue_delegation_row"."truster_id",
jbe@339 561 "issue_delegation_row"."scope",
jbe@339 562 "delegate_member_ids_v"
jbe@339 563 );
jbe@339 564 "sub_weight_v" := 1 +
jbe@339 565 "weight_of_added_delegations_for_population_snapshot"(
jbe@339 566 "issue_id_p",
jbe@339 567 "issue_delegation_row"."truster_id",
jbe@339 568 "delegate_member_ids_v"
jbe@339 569 );
jbe@339 570 UPDATE "delegating_population_snapshot"
jbe@339 571 SET "weight" = "sub_weight_v"
jbe@339 572 WHERE "issue_id" = "issue_id_p"
jbe@339 573 AND "event" = 'periodic'
jbe@339 574 AND "member_id" = "issue_delegation_row"."truster_id";
jbe@339 575 "weight_v" := "weight_v" + "sub_weight_v";
jbe@339 576 END IF;
jbe@339 577 END LOOP;
jbe@339 578 RETURN "weight_v";
jbe@339 579 END;
jbe@339 580 $$;
jbe@339 581
jbe@339 582 CREATE OR REPLACE FUNCTION "create_population_snapshot"
jbe@339 583 ( "issue_id_p" "issue"."id"%TYPE )
jbe@339 584 RETURNS VOID
jbe@339 585 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@339 586 DECLARE
jbe@339 587 "member_id_v" "member"."id"%TYPE;
jbe@339 588 BEGIN
jbe@339 589 PERFORM "require_transaction_isolation"();
jbe@339 590 DELETE FROM "direct_population_snapshot"
jbe@339 591 WHERE "issue_id" = "issue_id_p"
jbe@339 592 AND "event" = 'periodic';
jbe@339 593 DELETE FROM "delegating_population_snapshot"
jbe@339 594 WHERE "issue_id" = "issue_id_p"
jbe@339 595 AND "event" = 'periodic';
jbe@339 596 INSERT INTO "direct_population_snapshot"
jbe@339 597 ("issue_id", "event", "member_id")
jbe@339 598 SELECT
jbe@339 599 "issue_id_p" AS "issue_id",
jbe@339 600 'periodic'::"snapshot_event" AS "event",
jbe@339 601 "member"."id" AS "member_id"
jbe@339 602 FROM "issue"
jbe@339 603 JOIN "area" ON "issue"."area_id" = "area"."id"
jbe@339 604 JOIN "membership" ON "area"."id" = "membership"."area_id"
jbe@339 605 JOIN "member" ON "membership"."member_id" = "member"."id"
jbe@339 606 JOIN "privilege"
jbe@339 607 ON "privilege"."unit_id" = "area"."unit_id"
jbe@339 608 AND "privilege"."member_id" = "member"."id"
jbe@339 609 WHERE "issue"."id" = "issue_id_p"
jbe@339 610 AND "member"."active" AND "privilege"."voting_right"
jbe@339 611 UNION
jbe@339 612 SELECT
jbe@339 613 "issue_id_p" AS "issue_id",
jbe@339 614 'periodic'::"snapshot_event" AS "event",
jbe@339 615 "member"."id" AS "member_id"
jbe@339 616 FROM "issue"
jbe@339 617 JOIN "area" ON "issue"."area_id" = "area"."id"
jbe@339 618 JOIN "interest" ON "issue"."id" = "interest"."issue_id"
jbe@339 619 JOIN "member" ON "interest"."member_id" = "member"."id"
jbe@339 620 JOIN "privilege"
jbe@339 621 ON "privilege"."unit_id" = "area"."unit_id"
jbe@339 622 AND "privilege"."member_id" = "member"."id"
jbe@339 623 WHERE "issue"."id" = "issue_id_p"
jbe@339 624 AND "member"."active" AND "privilege"."voting_right";
jbe@339 625 FOR "member_id_v" IN
jbe@339 626 SELECT "member_id" FROM "direct_population_snapshot"
jbe@339 627 WHERE "issue_id" = "issue_id_p"
jbe@339 628 AND "event" = 'periodic'
jbe@339 629 LOOP
jbe@339 630 UPDATE "direct_population_snapshot" SET
jbe@339 631 "weight" = 1 +
jbe@339 632 "weight_of_added_delegations_for_population_snapshot"(
jbe@339 633 "issue_id_p",
jbe@339 634 "member_id_v",
jbe@339 635 '{}'
jbe@339 636 )
jbe@339 637 WHERE "issue_id" = "issue_id_p"
jbe@339 638 AND "event" = 'periodic'
jbe@339 639 AND "member_id" = "member_id_v";
jbe@339 640 END LOOP;
jbe@339 641 RETURN;
jbe@339 642 END;
jbe@339 643 $$;
jbe@339 644
jbe@339 645 CREATE OR REPLACE FUNCTION "weight_of_added_delegations_for_interest_snapshot"
jbe@339 646 ( "issue_id_p" "issue"."id"%TYPE,
jbe@339 647 "member_id_p" "member"."id"%TYPE,
jbe@339 648 "delegate_member_ids_p" "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
jbe@339 649 RETURNS "direct_interest_snapshot"."weight"%TYPE
jbe@339 650 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@339 651 DECLARE
jbe@339 652 "issue_delegation_row" "issue_delegation"%ROWTYPE;
jbe@339 653 "delegate_member_ids_v" "delegating_interest_snapshot"."delegate_member_ids"%TYPE;
jbe@339 654 "weight_v" INT4;
jbe@339 655 "sub_weight_v" INT4;
jbe@339 656 BEGIN
jbe@339 657 PERFORM "require_transaction_isolation"();
jbe@339 658 "weight_v" := 0;
jbe@339 659 FOR "issue_delegation_row" IN
jbe@339 660 SELECT * FROM "issue_delegation"
jbe@339 661 WHERE "trustee_id" = "member_id_p"
jbe@339 662 AND "issue_id" = "issue_id_p"
jbe@339 663 LOOP
jbe@339 664 IF NOT EXISTS (
jbe@339 665 SELECT NULL FROM "direct_interest_snapshot"
jbe@339 666 WHERE "issue_id" = "issue_id_p"
jbe@339 667 AND "event" = 'periodic'
jbe@339 668 AND "member_id" = "issue_delegation_row"."truster_id"
jbe@339 669 ) AND NOT EXISTS (
jbe@339 670 SELECT NULL FROM "delegating_interest_snapshot"
jbe@339 671 WHERE "issue_id" = "issue_id_p"
jbe@339 672 AND "event" = 'periodic'
jbe@339 673 AND "member_id" = "issue_delegation_row"."truster_id"
jbe@339 674 ) THEN
jbe@339 675 "delegate_member_ids_v" :=
jbe@339 676 "member_id_p" || "delegate_member_ids_p";
jbe@339 677 INSERT INTO "delegating_interest_snapshot" (
jbe@339 678 "issue_id",
jbe@339 679 "event",
jbe@339 680 "member_id",
jbe@339 681 "scope",
jbe@339 682 "delegate_member_ids"
jbe@339 683 ) VALUES (
jbe@339 684 "issue_id_p",
jbe@339 685 'periodic',
jbe@339 686 "issue_delegation_row"."truster_id",
jbe@339 687 "issue_delegation_row"."scope",
jbe@339 688 "delegate_member_ids_v"
jbe@339 689 );
jbe@339 690 "sub_weight_v" := 1 +
jbe@339 691 "weight_of_added_delegations_for_interest_snapshot"(
jbe@339 692 "issue_id_p",
jbe@339 693 "issue_delegation_row"."truster_id",
jbe@339 694 "delegate_member_ids_v"
jbe@339 695 );
jbe@339 696 UPDATE "delegating_interest_snapshot"
jbe@339 697 SET "weight" = "sub_weight_v"
jbe@339 698 WHERE "issue_id" = "issue_id_p"
jbe@339 699 AND "event" = 'periodic'
jbe@339 700 AND "member_id" = "issue_delegation_row"."truster_id";
jbe@339 701 "weight_v" := "weight_v" + "sub_weight_v";
jbe@339 702 END IF;
jbe@339 703 END LOOP;
jbe@339 704 RETURN "weight_v";
jbe@339 705 END;
jbe@339 706 $$;
jbe@339 707
jbe@339 708 CREATE OR REPLACE FUNCTION "create_interest_snapshot"
jbe@339 709 ( "issue_id_p" "issue"."id"%TYPE )
jbe@339 710 RETURNS VOID
jbe@339 711 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@339 712 DECLARE
jbe@339 713 "member_id_v" "member"."id"%TYPE;
jbe@339 714 BEGIN
jbe@339 715 PERFORM "require_transaction_isolation"();
jbe@339 716 DELETE FROM "direct_interest_snapshot"
jbe@339 717 WHERE "issue_id" = "issue_id_p"
jbe@339 718 AND "event" = 'periodic';
jbe@339 719 DELETE FROM "delegating_interest_snapshot"
jbe@339 720 WHERE "issue_id" = "issue_id_p"
jbe@339 721 AND "event" = 'periodic';
jbe@339 722 DELETE FROM "direct_supporter_snapshot"
jbe@339 723 USING "initiative" -- NOTE: due to missing index on issue_id
jbe@339 724 WHERE "initiative"."issue_id" = "issue_id_p"
jbe@339 725 AND "direct_supporter_snapshot"."initiative_id" = "initiative"."id"
jbe@339 726 AND "direct_supporter_snapshot"."event" = 'periodic';
jbe@339 727 INSERT INTO "direct_interest_snapshot"
jbe@339 728 ("issue_id", "event", "member_id")
jbe@339 729 SELECT
jbe@339 730 "issue_id_p" AS "issue_id",
jbe@339 731 'periodic' AS "event",
jbe@339 732 "member"."id" AS "member_id"
jbe@339 733 FROM "issue"
jbe@339 734 JOIN "area" ON "issue"."area_id" = "area"."id"
jbe@339 735 JOIN "interest" ON "issue"."id" = "interest"."issue_id"
jbe@339 736 JOIN "member" ON "interest"."member_id" = "member"."id"
jbe@339 737 JOIN "privilege"
jbe@339 738 ON "privilege"."unit_id" = "area"."unit_id"
jbe@339 739 AND "privilege"."member_id" = "member"."id"
jbe@339 740 WHERE "issue"."id" = "issue_id_p"
jbe@339 741 AND "member"."active" AND "privilege"."voting_right";
jbe@339 742 FOR "member_id_v" IN
jbe@339 743 SELECT "member_id" FROM "direct_interest_snapshot"
jbe@339 744 WHERE "issue_id" = "issue_id_p"
jbe@339 745 AND "event" = 'periodic'
jbe@339 746 LOOP
jbe@339 747 UPDATE "direct_interest_snapshot" SET
jbe@339 748 "weight" = 1 +
jbe@339 749 "weight_of_added_delegations_for_interest_snapshot"(
jbe@339 750 "issue_id_p",
jbe@339 751 "member_id_v",
jbe@339 752 '{}'
jbe@339 753 )
jbe@339 754 WHERE "issue_id" = "issue_id_p"
jbe@339 755 AND "event" = 'periodic'
jbe@339 756 AND "member_id" = "member_id_v";
jbe@339 757 END LOOP;
jbe@339 758 INSERT INTO "direct_supporter_snapshot"
jbe@339 759 ( "issue_id", "initiative_id", "event", "member_id",
jbe@339 760 "draft_id", "informed", "satisfied" )
jbe@339 761 SELECT
jbe@339 762 "issue_id_p" AS "issue_id",
jbe@339 763 "initiative"."id" AS "initiative_id",
jbe@339 764 'periodic' AS "event",
jbe@339 765 "supporter"."member_id" AS "member_id",
jbe@339 766 "supporter"."draft_id" AS "draft_id",
jbe@339 767 "supporter"."draft_id" = "current_draft"."id" AS "informed",
jbe@339 768 NOT EXISTS (
jbe@339 769 SELECT NULL FROM "critical_opinion"
jbe@339 770 WHERE "initiative_id" = "initiative"."id"
jbe@339 771 AND "member_id" = "supporter"."member_id"
jbe@339 772 ) AS "satisfied"
jbe@339 773 FROM "initiative"
jbe@339 774 JOIN "supporter"
jbe@339 775 ON "supporter"."initiative_id" = "initiative"."id"
jbe@339 776 JOIN "current_draft"
jbe@339 777 ON "initiative"."id" = "current_draft"."initiative_id"
jbe@339 778 JOIN "direct_interest_snapshot"
jbe@339 779 ON "supporter"."member_id" = "direct_interest_snapshot"."member_id"
jbe@339 780 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"
jbe@339 781 AND "event" = 'periodic'
jbe@339 782 WHERE "initiative"."issue_id" = "issue_id_p";
jbe@339 783 RETURN;
jbe@339 784 END;
jbe@339 785 $$;
jbe@339 786
jbe@339 787 CREATE OR REPLACE FUNCTION "create_snapshot"
jbe@339 788 ( "issue_id_p" "issue"."id"%TYPE )
jbe@339 789 RETURNS VOID
jbe@339 790 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@339 791 DECLARE
jbe@339 792 "initiative_id_v" "initiative"."id"%TYPE;
jbe@339 793 "suggestion_id_v" "suggestion"."id"%TYPE;
jbe@339 794 BEGIN
jbe@339 795 PERFORM "require_transaction_isolation"();
jbe@339 796 PERFORM "create_population_snapshot"("issue_id_p");
jbe@339 797 PERFORM "create_interest_snapshot"("issue_id_p");
jbe@339 798 UPDATE "issue" SET
jbe@339 799 "snapshot" = coalesce("phase_finished", now()),
jbe@339 800 "latest_snapshot_event" = 'periodic',
jbe@339 801 "population" = (
jbe@339 802 SELECT coalesce(sum("weight"), 0)
jbe@339 803 FROM "direct_population_snapshot"
jbe@339 804 WHERE "issue_id" = "issue_id_p"
jbe@339 805 AND "event" = 'periodic'
jbe@339 806 )
jbe@339 807 WHERE "id" = "issue_id_p";
jbe@339 808 FOR "initiative_id_v" IN
jbe@339 809 SELECT "id" FROM "initiative" WHERE "issue_id" = "issue_id_p"
jbe@339 810 LOOP
jbe@339 811 UPDATE "initiative" SET
jbe@339 812 "supporter_count" = (
jbe@339 813 SELECT coalesce(sum("di"."weight"), 0)
jbe@339 814 FROM "direct_interest_snapshot" AS "di"
jbe@339 815 JOIN "direct_supporter_snapshot" AS "ds"
jbe@339 816 ON "di"."member_id" = "ds"."member_id"
jbe@339 817 WHERE "di"."issue_id" = "issue_id_p"
jbe@339 818 AND "di"."event" = 'periodic'
jbe@339 819 AND "ds"."initiative_id" = "initiative_id_v"
jbe@339 820 AND "ds"."event" = 'periodic'
jbe@339 821 ),
jbe@339 822 "informed_supporter_count" = (
jbe@339 823 SELECT coalesce(sum("di"."weight"), 0)
jbe@339 824 FROM "direct_interest_snapshot" AS "di"
jbe@339 825 JOIN "direct_supporter_snapshot" AS "ds"
jbe@339 826 ON "di"."member_id" = "ds"."member_id"
jbe@339 827 WHERE "di"."issue_id" = "issue_id_p"
jbe@339 828 AND "di"."event" = 'periodic'
jbe@339 829 AND "ds"."initiative_id" = "initiative_id_v"
jbe@339 830 AND "ds"."event" = 'periodic'
jbe@339 831 AND "ds"."informed"
jbe@339 832 ),
jbe@339 833 "satisfied_supporter_count" = (
jbe@339 834 SELECT coalesce(sum("di"."weight"), 0)
jbe@339 835 FROM "direct_interest_snapshot" AS "di"
jbe@339 836 JOIN "direct_supporter_snapshot" AS "ds"
jbe@339 837 ON "di"."member_id" = "ds"."member_id"
jbe@339 838 WHERE "di"."issue_id" = "issue_id_p"
jbe@339 839 AND "di"."event" = 'periodic'
jbe@339 840 AND "ds"."initiative_id" = "initiative_id_v"
jbe@339 841 AND "ds"."event" = 'periodic'
jbe@339 842 AND "ds"."satisfied"
jbe@339 843 ),
jbe@339 844 "satisfied_informed_supporter_count" = (
jbe@339 845 SELECT coalesce(sum("di"."weight"), 0)
jbe@339 846 FROM "direct_interest_snapshot" AS "di"
jbe@339 847 JOIN "direct_supporter_snapshot" AS "ds"
jbe@339 848 ON "di"."member_id" = "ds"."member_id"
jbe@339 849 WHERE "di"."issue_id" = "issue_id_p"
jbe@339 850 AND "di"."event" = 'periodic'
jbe@339 851 AND "ds"."initiative_id" = "initiative_id_v"
jbe@339 852 AND "ds"."event" = 'periodic'
jbe@339 853 AND "ds"."informed"
jbe@339 854 AND "ds"."satisfied"
jbe@339 855 )
jbe@339 856 WHERE "id" = "initiative_id_v";
jbe@339 857 FOR "suggestion_id_v" IN
jbe@339 858 SELECT "id" FROM "suggestion"
jbe@339 859 WHERE "initiative_id" = "initiative_id_v"
jbe@339 860 LOOP
jbe@339 861 UPDATE "suggestion" SET
jbe@339 862 "minus2_unfulfilled_count" = (
jbe@339 863 SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@339 864 FROM "issue" CROSS JOIN "opinion"
jbe@339 865 JOIN "direct_interest_snapshot" AS "snapshot"
jbe@339 866 ON "snapshot"."issue_id" = "issue"."id"
jbe@339 867 AND "snapshot"."event" = "issue"."latest_snapshot_event"
jbe@339 868 AND "snapshot"."member_id" = "opinion"."member_id"
jbe@339 869 WHERE "issue"."id" = "issue_id_p"
jbe@339 870 AND "opinion"."suggestion_id" = "suggestion_id_v"
jbe@339 871 AND "opinion"."degree" = -2
jbe@339 872 AND "opinion"."fulfilled" = FALSE
jbe@339 873 ),
jbe@339 874 "minus2_fulfilled_count" = (
jbe@339 875 SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@339 876 FROM "issue" CROSS JOIN "opinion"
jbe@339 877 JOIN "direct_interest_snapshot" AS "snapshot"
jbe@339 878 ON "snapshot"."issue_id" = "issue"."id"
jbe@339 879 AND "snapshot"."event" = "issue"."latest_snapshot_event"
jbe@339 880 AND "snapshot"."member_id" = "opinion"."member_id"
jbe@339 881 WHERE "issue"."id" = "issue_id_p"
jbe@339 882 AND "opinion"."suggestion_id" = "suggestion_id_v"
jbe@339 883 AND "opinion"."degree" = -2
jbe@339 884 AND "opinion"."fulfilled" = TRUE
jbe@339 885 ),
jbe@339 886 "minus1_unfulfilled_count" = (
jbe@339 887 SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@339 888 FROM "issue" CROSS JOIN "opinion"
jbe@339 889 JOIN "direct_interest_snapshot" AS "snapshot"
jbe@339 890 ON "snapshot"."issue_id" = "issue"."id"
jbe@339 891 AND "snapshot"."event" = "issue"."latest_snapshot_event"
jbe@339 892 AND "snapshot"."member_id" = "opinion"."member_id"
jbe@339 893 WHERE "issue"."id" = "issue_id_p"
jbe@339 894 AND "opinion"."suggestion_id" = "suggestion_id_v"
jbe@339 895 AND "opinion"."degree" = -1
jbe@339 896 AND "opinion"."fulfilled" = FALSE
jbe@339 897 ),
jbe@339 898 "minus1_fulfilled_count" = (
jbe@339 899 SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@339 900 FROM "issue" CROSS JOIN "opinion"
jbe@339 901 JOIN "direct_interest_snapshot" AS "snapshot"
jbe@339 902 ON "snapshot"."issue_id" = "issue"."id"
jbe@339 903 AND "snapshot"."event" = "issue"."latest_snapshot_event"
jbe@339 904 AND "snapshot"."member_id" = "opinion"."member_id"
jbe@339 905 WHERE "issue"."id" = "issue_id_p"
jbe@339 906 AND "opinion"."suggestion_id" = "suggestion_id_v"
jbe@339 907 AND "opinion"."degree" = -1
jbe@339 908 AND "opinion"."fulfilled" = TRUE
jbe@339 909 ),
jbe@339 910 "plus1_unfulfilled_count" = (
jbe@339 911 SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@339 912 FROM "issue" CROSS JOIN "opinion"
jbe@339 913 JOIN "direct_interest_snapshot" AS "snapshot"
jbe@339 914 ON "snapshot"."issue_id" = "issue"."id"
jbe@339 915 AND "snapshot"."event" = "issue"."latest_snapshot_event"
jbe@339 916 AND "snapshot"."member_id" = "opinion"."member_id"
jbe@339 917 WHERE "issue"."id" = "issue_id_p"
jbe@339 918 AND "opinion"."suggestion_id" = "suggestion_id_v"
jbe@339 919 AND "opinion"."degree" = 1
jbe@339 920 AND "opinion"."fulfilled" = FALSE
jbe@339 921 ),
jbe@339 922 "plus1_fulfilled_count" = (
jbe@339 923 SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@339 924 FROM "issue" CROSS JOIN "opinion"
jbe@339 925 JOIN "direct_interest_snapshot" AS "snapshot"
jbe@339 926 ON "snapshot"."issue_id" = "issue"."id"
jbe@339 927 AND "snapshot"."event" = "issue"."latest_snapshot_event"
jbe@339 928 AND "snapshot"."member_id" = "opinion"."member_id"
jbe@339 929 WHERE "issue"."id" = "issue_id_p"
jbe@339 930 AND "opinion"."suggestion_id" = "suggestion_id_v"
jbe@339 931 AND "opinion"."degree" = 1
jbe@339 932 AND "opinion"."fulfilled" = TRUE
jbe@339 933 ),
jbe@339 934 "plus2_unfulfilled_count" = (
jbe@339 935 SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@339 936 FROM "issue" CROSS JOIN "opinion"
jbe@339 937 JOIN "direct_interest_snapshot" AS "snapshot"
jbe@339 938 ON "snapshot"."issue_id" = "issue"."id"
jbe@339 939 AND "snapshot"."event" = "issue"."latest_snapshot_event"
jbe@339 940 AND "snapshot"."member_id" = "opinion"."member_id"
jbe@339 941 WHERE "issue"."id" = "issue_id_p"
jbe@339 942 AND "opinion"."suggestion_id" = "suggestion_id_v"
jbe@339 943 AND "opinion"."degree" = 2
jbe@339 944 AND "opinion"."fulfilled" = FALSE
jbe@339 945 ),
jbe@339 946 "plus2_fulfilled_count" = (
jbe@339 947 SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@339 948 FROM "issue" CROSS JOIN "opinion"
jbe@339 949 JOIN "direct_interest_snapshot" AS "snapshot"
jbe@339 950 ON "snapshot"."issue_id" = "issue"."id"
jbe@339 951 AND "snapshot"."event" = "issue"."latest_snapshot_event"
jbe@339 952 AND "snapshot"."member_id" = "opinion"."member_id"
jbe@339 953 WHERE "issue"."id" = "issue_id_p"
jbe@339 954 AND "opinion"."suggestion_id" = "suggestion_id_v"
jbe@339 955 AND "opinion"."degree" = 2
jbe@339 956 AND "opinion"."fulfilled" = TRUE
jbe@339 957 )
jbe@339 958 WHERE "suggestion"."id" = "suggestion_id_v";
jbe@339 959 END LOOP;
jbe@339 960 END LOOP;
jbe@339 961 RETURN;
jbe@339 962 END;
jbe@339 963 $$;
jbe@339 964
jbe@339 965 CREATE OR REPLACE FUNCTION "set_snapshot_event"
jbe@339 966 ( "issue_id_p" "issue"."id"%TYPE,
jbe@339 967 "event_p" "snapshot_event" )
jbe@339 968 RETURNS VOID
jbe@339 969 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@339 970 DECLARE
jbe@339 971 "event_v" "issue"."latest_snapshot_event"%TYPE;
jbe@339 972 BEGIN
jbe@339 973 PERFORM "require_transaction_isolation"();
jbe@339 974 SELECT "latest_snapshot_event" INTO "event_v" FROM "issue"
jbe@339 975 WHERE "id" = "issue_id_p" FOR UPDATE;
jbe@339 976 UPDATE "issue" SET "latest_snapshot_event" = "event_p"
jbe@339 977 WHERE "id" = "issue_id_p";
jbe@339 978 UPDATE "direct_population_snapshot" SET "event" = "event_p"
jbe@339 979 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
jbe@339 980 UPDATE "delegating_population_snapshot" SET "event" = "event_p"
jbe@339 981 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
jbe@339 982 UPDATE "direct_interest_snapshot" SET "event" = "event_p"
jbe@339 983 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
jbe@339 984 UPDATE "delegating_interest_snapshot" SET "event" = "event_p"
jbe@339 985 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
jbe@339 986 UPDATE "direct_supporter_snapshot" SET "event" = "event_p"
jbe@339 987 FROM "initiative" -- NOTE: due to missing index on issue_id
jbe@339 988 WHERE "initiative"."issue_id" = "issue_id_p"
jbe@339 989 AND "direct_supporter_snapshot"."initiative_id" = "initiative"."id"
jbe@339 990 AND "direct_supporter_snapshot"."event" = "event_v";
jbe@339 991 RETURN;
jbe@339 992 END;
jbe@339 993 $$;
jbe@339 994
jbe@339 995 DROP FUNCTION "freeze_after_snapshot"("issue"."id"%TYPE);
jbe@339 996 DROP FUNCTION "manual_freeze"("issue"."id"%TYPE);
jbe@339 997
jbe@339 998 CREATE OR REPLACE FUNCTION "weight_of_added_vote_delegations"
jbe@339 999 ( "issue_id_p" "issue"."id"%TYPE,
jbe@339 1000 "member_id_p" "member"."id"%TYPE,
jbe@339 1001 "delegate_member_ids_p" "delegating_voter"."delegate_member_ids"%TYPE )
jbe@339 1002 RETURNS "direct_voter"."weight"%TYPE
jbe@339 1003 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@339 1004 DECLARE
jbe@339 1005 "issue_delegation_row" "issue_delegation"%ROWTYPE;
jbe@339 1006 "delegate_member_ids_v" "delegating_voter"."delegate_member_ids"%TYPE;
jbe@339 1007 "weight_v" INT4;
jbe@339 1008 "sub_weight_v" INT4;
jbe@339 1009 BEGIN
jbe@339 1010 PERFORM "require_transaction_isolation"();
jbe@339 1011 "weight_v" := 0;
jbe@339 1012 FOR "issue_delegation_row" IN
jbe@339 1013 SELECT * FROM "issue_delegation"
jbe@339 1014 WHERE "trustee_id" = "member_id_p"
jbe@339 1015 AND "issue_id" = "issue_id_p"
jbe@339 1016 LOOP
jbe@339 1017 IF NOT EXISTS (
jbe@339 1018 SELECT NULL FROM "direct_voter"
jbe@339 1019 WHERE "member_id" = "issue_delegation_row"."truster_id"
jbe@339 1020 AND "issue_id" = "issue_id_p"
jbe@339 1021 ) AND NOT EXISTS (
jbe@339 1022 SELECT NULL FROM "delegating_voter"
jbe@339 1023 WHERE "member_id" = "issue_delegation_row"."truster_id"
jbe@339 1024 AND "issue_id" = "issue_id_p"
jbe@339 1025 ) THEN
jbe@339 1026 "delegate_member_ids_v" :=
jbe@339 1027 "member_id_p" || "delegate_member_ids_p";
jbe@339 1028 INSERT INTO "delegating_voter" (
jbe@339 1029 "issue_id",
jbe@339 1030 "member_id",
jbe@339 1031 "scope",
jbe@339 1032 "delegate_member_ids"
jbe@339 1033 ) VALUES (
jbe@339 1034 "issue_id_p",
jbe@339 1035 "issue_delegation_row"."truster_id",
jbe@339 1036 "issue_delegation_row"."scope",
jbe@339 1037 "delegate_member_ids_v"
jbe@339 1038 );
jbe@339 1039 "sub_weight_v" := 1 +
jbe@339 1040 "weight_of_added_vote_delegations"(
jbe@339 1041 "issue_id_p",
jbe@339 1042 "issue_delegation_row"."truster_id",
jbe@339 1043 "delegate_member_ids_v"
jbe@339 1044 );
jbe@339 1045 UPDATE "delegating_voter"
jbe@339 1046 SET "weight" = "sub_weight_v"
jbe@339 1047 WHERE "issue_id" = "issue_id_p"
jbe@339 1048 AND "member_id" = "issue_delegation_row"."truster_id";
jbe@339 1049 "weight_v" := "weight_v" + "sub_weight_v";
jbe@339 1050 END IF;
jbe@339 1051 END LOOP;
jbe@339 1052 RETURN "weight_v";
jbe@339 1053 END;
jbe@339 1054 $$;
jbe@339 1055
jbe@339 1056 CREATE OR REPLACE FUNCTION "add_vote_delegations"
jbe@339 1057 ( "issue_id_p" "issue"."id"%TYPE )
jbe@339 1058 RETURNS VOID
jbe@339 1059 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@339 1060 DECLARE
jbe@339 1061 "member_id_v" "member"."id"%TYPE;
jbe@339 1062 BEGIN
jbe@339 1063 PERFORM "require_transaction_isolation"();
jbe@339 1064 FOR "member_id_v" IN
jbe@339 1065 SELECT "member_id" FROM "direct_voter"
jbe@339 1066 WHERE "issue_id" = "issue_id_p"
jbe@339 1067 LOOP
jbe@339 1068 UPDATE "direct_voter" SET
jbe@339 1069 "weight" = "weight" + "weight_of_added_vote_delegations"(
jbe@339 1070 "issue_id_p",
jbe@339 1071 "member_id_v",
jbe@339 1072 '{}'
jbe@339 1073 )
jbe@339 1074 WHERE "member_id" = "member_id_v"
jbe@339 1075 AND "issue_id" = "issue_id_p";
jbe@339 1076 END LOOP;
jbe@339 1077 RETURN;
jbe@339 1078 END;
jbe@339 1079 $$;
jbe@339 1080
jbe@339 1081 CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
jbe@339 1082 RETURNS VOID
jbe@339 1083 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@339 1084 DECLARE
jbe@339 1085 "area_id_v" "area"."id"%TYPE;
jbe@339 1086 "unit_id_v" "unit"."id"%TYPE;
jbe@339 1087 "member_id_v" "member"."id"%TYPE;
jbe@339 1088 BEGIN
jbe@339 1089 PERFORM "require_transaction_isolation"();
jbe@339 1090 SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";
jbe@339 1091 SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v";
jbe@339 1092 -- delete timestamp of voting comment:
jbe@339 1093 UPDATE "direct_voter" SET "comment_changed" = NULL
jbe@339 1094 WHERE "issue_id" = "issue_id_p";
jbe@339 1095 -- delete delegating votes (in cases of manual reset of issue state):
jbe@339 1096 DELETE FROM "delegating_voter"
jbe@339 1097 WHERE "issue_id" = "issue_id_p";
jbe@339 1098 -- delete votes from non-privileged voters:
jbe@339 1099 DELETE FROM "direct_voter"
jbe@339 1100 USING (
jbe@339 1101 SELECT
jbe@339 1102 "direct_voter"."member_id"
jbe@339 1103 FROM "direct_voter"
jbe@339 1104 JOIN "member" ON "direct_voter"."member_id" = "member"."id"
jbe@339 1105 LEFT JOIN "privilege"
jbe@339 1106 ON "privilege"."unit_id" = "unit_id_v"
jbe@339 1107 AND "privilege"."member_id" = "direct_voter"."member_id"
jbe@339 1108 WHERE "direct_voter"."issue_id" = "issue_id_p" AND (
jbe@339 1109 "member"."active" = FALSE OR
jbe@339 1110 "privilege"."voting_right" ISNULL OR
jbe@339 1111 "privilege"."voting_right" = FALSE
jbe@339 1112 )
jbe@339 1113 ) AS "subquery"
jbe@339 1114 WHERE "direct_voter"."issue_id" = "issue_id_p"
jbe@339 1115 AND "direct_voter"."member_id" = "subquery"."member_id";
jbe@339 1116 -- consider delegations:
jbe@339 1117 UPDATE "direct_voter" SET "weight" = 1
jbe@339 1118 WHERE "issue_id" = "issue_id_p";
jbe@339 1119 PERFORM "add_vote_delegations"("issue_id_p");
jbe@339 1120 -- materialize battle_view:
jbe@339 1121 -- NOTE: "closed" column of issue must be set at this point
jbe@339 1122 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
jbe@339 1123 INSERT INTO "battle" (
jbe@339 1124 "issue_id",
jbe@339 1125 "winning_initiative_id", "losing_initiative_id",
jbe@339 1126 "count"
jbe@339 1127 ) SELECT
jbe@339 1128 "issue_id",
jbe@339 1129 "winning_initiative_id", "losing_initiative_id",
jbe@339 1130 "count"
jbe@339 1131 FROM "battle_view" WHERE "issue_id" = "issue_id_p";
jbe@339 1132 -- set voter count:
jbe@339 1133 UPDATE "issue" SET
jbe@339 1134 "voter_count" = (
jbe@339 1135 SELECT coalesce(sum("weight"), 0)
jbe@339 1136 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
jbe@339 1137 )
jbe@339 1138 WHERE "id" = "issue_id_p";
jbe@339 1139 -- copy "positive_votes" and "negative_votes" from "battle" table:
jbe@339 1140 UPDATE "initiative" SET
jbe@339 1141 "positive_votes" = "battle_win"."count",
jbe@339 1142 "negative_votes" = "battle_lose"."count"
jbe@339 1143 FROM "battle" AS "battle_win", "battle" AS "battle_lose"
jbe@339 1144 WHERE
jbe@339 1145 "battle_win"."issue_id" = "issue_id_p" AND
jbe@339 1146 "battle_win"."winning_initiative_id" = "initiative"."id" AND
jbe@339 1147 "battle_win"."losing_initiative_id" ISNULL AND
jbe@339 1148 "battle_lose"."issue_id" = "issue_id_p" AND
jbe@339 1149 "battle_lose"."losing_initiative_id" = "initiative"."id" AND
jbe@339 1150 "battle_lose"."winning_initiative_id" ISNULL;
jbe@339 1151 END;
jbe@339 1152 $$;
jbe@339 1153
jbe@339 1154 CREATE OR REPLACE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE)
jbe@339 1155 RETURNS VOID
jbe@339 1156 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@339 1157 DECLARE
jbe@339 1158 "issue_row" "issue"%ROWTYPE;
jbe@339 1159 "policy_row" "policy"%ROWTYPE;
jbe@339 1160 "dimension_v" INTEGER;
jbe@339 1161 "vote_matrix" INT4[][]; -- absolute votes
jbe@339 1162 "matrix" INT8[][]; -- defeat strength / best paths
jbe@339 1163 "i" INTEGER;
jbe@339 1164 "j" INTEGER;
jbe@339 1165 "k" INTEGER;
jbe@339 1166 "battle_row" "battle"%ROWTYPE;
jbe@339 1167 "rank_ary" INT4[];
jbe@339 1168 "rank_v" INT4;
jbe@339 1169 "done_v" INTEGER;
jbe@339 1170 "winners_ary" INTEGER[];
jbe@339 1171 "initiative_id_v" "initiative"."id"%TYPE;
jbe@339 1172 BEGIN
jbe@339 1173 PERFORM "require_transaction_isolation"();
jbe@339 1174 SELECT * INTO "issue_row"
jbe@339 1175 FROM "issue" WHERE "id" = "issue_id_p";
jbe@339 1176 SELECT * INTO "policy_row"
jbe@339 1177 FROM "policy" WHERE "id" = "issue_row"."policy_id";
jbe@339 1178 SELECT count(1) INTO "dimension_v"
jbe@339 1179 FROM "battle_participant" WHERE "issue_id" = "issue_id_p";
jbe@339 1180 -- Create "vote_matrix" with absolute number of votes in pairwise
jbe@339 1181 -- comparison:
jbe@339 1182 "vote_matrix" := array_fill(NULL::INT4, ARRAY["dimension_v", "dimension_v"]);
jbe@339 1183 "i" := 1;
jbe@339 1184 "j" := 2;
jbe@339 1185 FOR "battle_row" IN
jbe@339 1186 SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p"
jbe@339 1187 ORDER BY
jbe@339 1188 "winning_initiative_id" NULLS LAST,
jbe@339 1189 "losing_initiative_id" NULLS LAST
jbe@339 1190 LOOP
jbe@339 1191 "vote_matrix"["i"]["j"] := "battle_row"."count";
jbe@339 1192 IF "j" = "dimension_v" THEN
jbe@339 1193 "i" := "i" + 1;
jbe@339 1194 "j" := 1;
jbe@339 1195 ELSE
jbe@339 1196 "j" := "j" + 1;
jbe@339 1197 IF "j" = "i" THEN
jbe@339 1198 "j" := "j" + 1;
jbe@339 1199 END IF;
jbe@339 1200 END IF;
jbe@339 1201 END LOOP;
jbe@339 1202 IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN
jbe@339 1203 RAISE EXCEPTION 'Wrong battle count (should not happen)';
jbe@339 1204 END IF;
jbe@339 1205 -- Store defeat strengths in "matrix" using "defeat_strength"
jbe@339 1206 -- function:
jbe@339 1207 "matrix" := array_fill(NULL::INT8, ARRAY["dimension_v", "dimension_v"]);
jbe@339 1208 "i" := 1;
jbe@339 1209 LOOP
jbe@339 1210 "j" := 1;
jbe@339 1211 LOOP
jbe@339 1212 IF "i" != "j" THEN
jbe@339 1213 "matrix"["i"]["j"] := "defeat_strength"(
jbe@339 1214 "vote_matrix"["i"]["j"],
jbe@339 1215 "vote_matrix"["j"]["i"]
jbe@339 1216 );
jbe@339 1217 END IF;
jbe@339 1218 EXIT WHEN "j" = "dimension_v";
jbe@339 1219 "j" := "j" + 1;
jbe@339 1220 END LOOP;
jbe@339 1221 EXIT WHEN "i" = "dimension_v";
jbe@339 1222 "i" := "i" + 1;
jbe@339 1223 END LOOP;
jbe@339 1224 -- Find best paths:
jbe@339 1225 "i" := 1;
jbe@339 1226 LOOP
jbe@339 1227 "j" := 1;
jbe@339 1228 LOOP
jbe@339 1229 IF "i" != "j" THEN
jbe@339 1230 "k" := 1;
jbe@339 1231 LOOP
jbe@339 1232 IF "i" != "k" AND "j" != "k" THEN
jbe@339 1233 IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN
jbe@339 1234 IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN
jbe@339 1235 "matrix"["j"]["k"] := "matrix"["j"]["i"];
jbe@339 1236 END IF;
jbe@339 1237 ELSE
jbe@339 1238 IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN
jbe@339 1239 "matrix"["j"]["k"] := "matrix"["i"]["k"];
jbe@339 1240 END IF;
jbe@339 1241 END IF;
jbe@339 1242 END IF;
jbe@339 1243 EXIT WHEN "k" = "dimension_v";
jbe@339 1244 "k" := "k" + 1;
jbe@339 1245 END LOOP;
jbe@339 1246 END IF;
jbe@339 1247 EXIT WHEN "j" = "dimension_v";
jbe@339 1248 "j" := "j" + 1;
jbe@339 1249 END LOOP;
jbe@339 1250 EXIT WHEN "i" = "dimension_v";
jbe@339 1251 "i" := "i" + 1;
jbe@339 1252 END LOOP;
jbe@339 1253 -- Determine order of winners:
jbe@339 1254 "rank_ary" := array_fill(NULL::INT4, ARRAY["dimension_v"]);
jbe@339 1255 "rank_v" := 1;
jbe@339 1256 "done_v" := 0;
jbe@339 1257 LOOP
jbe@339 1258 "winners_ary" := '{}';
jbe@339 1259 "i" := 1;
jbe@339 1260 LOOP
jbe@339 1261 IF "rank_ary"["i"] ISNULL THEN
jbe@339 1262 "j" := 1;
jbe@339 1263 LOOP
jbe@339 1264 IF
jbe@339 1265 "i" != "j" AND
jbe@339 1266 "rank_ary"["j"] ISNULL AND
jbe@339 1267 "matrix"["j"]["i"] > "matrix"["i"]["j"]
jbe@339 1268 THEN
jbe@339 1269 -- someone else is better
jbe@339 1270 EXIT;
jbe@339 1271 END IF;
jbe@339 1272 IF "j" = "dimension_v" THEN
jbe@339 1273 -- noone is better
jbe@339 1274 "winners_ary" := "winners_ary" || "i";
jbe@339 1275 EXIT;
jbe@339 1276 END IF;
jbe@339 1277 "j" := "j" + 1;
jbe@339 1278 END LOOP;
jbe@339 1279 END IF;
jbe@339 1280 EXIT WHEN "i" = "dimension_v";
jbe@339 1281 "i" := "i" + 1;
jbe@339 1282 END LOOP;
jbe@339 1283 "i" := 1;
jbe@339 1284 LOOP
jbe@339 1285 "rank_ary"["winners_ary"["i"]] := "rank_v";
jbe@339 1286 "done_v" := "done_v" + 1;
jbe@339 1287 EXIT WHEN "i" = array_upper("winners_ary", 1);
jbe@339 1288 "i" := "i" + 1;
jbe@339 1289 END LOOP;
jbe@339 1290 EXIT WHEN "done_v" = "dimension_v";
jbe@339 1291 "rank_v" := "rank_v" + 1;
jbe@339 1292 END LOOP;
jbe@339 1293 -- write preliminary results:
jbe@339 1294 "i" := 1;
jbe@339 1295 FOR "initiative_id_v" IN
jbe@339 1296 SELECT "id" FROM "initiative"
jbe@339 1297 WHERE "issue_id" = "issue_id_p" AND "admitted"
jbe@339 1298 ORDER BY "id"
jbe@339 1299 LOOP
jbe@339 1300 UPDATE "initiative" SET
jbe@339 1301 "direct_majority" =
jbe@339 1302 CASE WHEN "policy_row"."direct_majority_strict" THEN
jbe@339 1303 "positive_votes" * "policy_row"."direct_majority_den" >
jbe@339 1304 "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")
jbe@339 1305 ELSE
jbe@339 1306 "positive_votes" * "policy_row"."direct_majority_den" >=
jbe@339 1307 "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")
jbe@339 1308 END
jbe@339 1309 AND "positive_votes" >= "policy_row"."direct_majority_positive"
jbe@339 1310 AND "issue_row"."voter_count"-"negative_votes" >=
jbe@339 1311 "policy_row"."direct_majority_non_negative",
jbe@339 1312 "indirect_majority" =
jbe@339 1313 CASE WHEN "policy_row"."indirect_majority_strict" THEN
jbe@339 1314 "positive_votes" * "policy_row"."indirect_majority_den" >
jbe@339 1315 "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")
jbe@339 1316 ELSE
jbe@339 1317 "positive_votes" * "policy_row"."indirect_majority_den" >=
jbe@339 1318 "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")
jbe@339 1319 END
jbe@339 1320 AND "positive_votes" >= "policy_row"."indirect_majority_positive"
jbe@339 1321 AND "issue_row"."voter_count"-"negative_votes" >=
jbe@339 1322 "policy_row"."indirect_majority_non_negative",
jbe@339 1323 "schulze_rank" = "rank_ary"["i"],
jbe@339 1324 "better_than_status_quo" = "rank_ary"["i"] < "rank_ary"["dimension_v"],
jbe@339 1325 "worse_than_status_quo" = "rank_ary"["i"] > "rank_ary"["dimension_v"],
jbe@339 1326 "multistage_majority" = "rank_ary"["i"] >= "rank_ary"["dimension_v"],
jbe@339 1327 "reverse_beat_path" = "matrix"["dimension_v"]["i"] >= 0,
jbe@339 1328 "eligible" = FALSE,
jbe@339 1329 "winner" = FALSE,
jbe@339 1330 "rank" = NULL -- NOTE: in cases of manual reset of issue state
jbe@339 1331 WHERE "id" = "initiative_id_v";
jbe@339 1332 "i" := "i" + 1;
jbe@339 1333 END LOOP;
jbe@339 1334 IF "i" != "dimension_v" THEN
jbe@339 1335 RAISE EXCEPTION 'Wrong winner count (should not happen)';
jbe@339 1336 END IF;
jbe@339 1337 -- take indirect majorities into account:
jbe@339 1338 LOOP
jbe@339 1339 UPDATE "initiative" SET "indirect_majority" = TRUE
jbe@339 1340 FROM (
jbe@339 1341 SELECT "new_initiative"."id" AS "initiative_id"
jbe@339 1342 FROM "initiative" "old_initiative"
jbe@339 1343 JOIN "initiative" "new_initiative"
jbe@339 1344 ON "new_initiative"."issue_id" = "issue_id_p"
jbe@339 1345 AND "new_initiative"."indirect_majority" = FALSE
jbe@339 1346 JOIN "battle" "battle_win"
jbe@339 1347 ON "battle_win"."issue_id" = "issue_id_p"
jbe@339 1348 AND "battle_win"."winning_initiative_id" = "new_initiative"."id"
jbe@339 1349 AND "battle_win"."losing_initiative_id" = "old_initiative"."id"
jbe@339 1350 JOIN "battle" "battle_lose"
jbe@339 1351 ON "battle_lose"."issue_id" = "issue_id_p"
jbe@339 1352 AND "battle_lose"."losing_initiative_id" = "new_initiative"."id"
jbe@339 1353 AND "battle_lose"."winning_initiative_id" = "old_initiative"."id"
jbe@339 1354 WHERE "old_initiative"."issue_id" = "issue_id_p"
jbe@339 1355 AND "old_initiative"."indirect_majority" = TRUE
jbe@339 1356 AND CASE WHEN "policy_row"."indirect_majority_strict" THEN
jbe@339 1357 "battle_win"."count" * "policy_row"."indirect_majority_den" >
jbe@339 1358 "policy_row"."indirect_majority_num" *
jbe@339 1359 ("battle_win"."count"+"battle_lose"."count")
jbe@339 1360 ELSE
jbe@339 1361 "battle_win"."count" * "policy_row"."indirect_majority_den" >=
jbe@339 1362 "policy_row"."indirect_majority_num" *
jbe@339 1363 ("battle_win"."count"+"battle_lose"."count")
jbe@339 1364 END
jbe@339 1365 AND "battle_win"."count" >= "policy_row"."indirect_majority_positive"
jbe@339 1366 AND "issue_row"."voter_count"-"battle_lose"."count" >=
jbe@339 1367 "policy_row"."indirect_majority_non_negative"
jbe@339 1368 ) AS "subquery"
jbe@339 1369 WHERE "id" = "subquery"."initiative_id";
jbe@339 1370 EXIT WHEN NOT FOUND;
jbe@339 1371 END LOOP;
jbe@339 1372 -- set "multistage_majority" for remaining matching initiatives:
jbe@339 1373 UPDATE "initiative" SET "multistage_majority" = TRUE
jbe@339 1374 FROM (
jbe@339 1375 SELECT "losing_initiative"."id" AS "initiative_id"
jbe@339 1376 FROM "initiative" "losing_initiative"
jbe@339 1377 JOIN "initiative" "winning_initiative"
jbe@339 1378 ON "winning_initiative"."issue_id" = "issue_id_p"
jbe@339 1379 AND "winning_initiative"."admitted"
jbe@339 1380 JOIN "battle" "battle_win"
jbe@339 1381 ON "battle_win"."issue_id" = "issue_id_p"
jbe@339 1382 AND "battle_win"."winning_initiative_id" = "winning_initiative"."id"
jbe@339 1383 AND "battle_win"."losing_initiative_id" = "losing_initiative"."id"
jbe@339 1384 JOIN "battle" "battle_lose"
jbe@339 1385 ON "battle_lose"."issue_id" = "issue_id_p"
jbe@339 1386 AND "battle_lose"."losing_initiative_id" = "winning_initiative"."id"
jbe@339 1387 AND "battle_lose"."winning_initiative_id" = "losing_initiative"."id"
jbe@339 1388 WHERE "losing_initiative"."issue_id" = "issue_id_p"
jbe@339 1389 AND "losing_initiative"."admitted"
jbe@339 1390 AND "winning_initiative"."schulze_rank" <
jbe@339 1391 "losing_initiative"."schulze_rank"
jbe@339 1392 AND "battle_win"."count" > "battle_lose"."count"
jbe@339 1393 AND (
jbe@339 1394 "battle_win"."count" > "winning_initiative"."positive_votes" OR
jbe@339 1395 "battle_lose"."count" < "losing_initiative"."negative_votes" )
jbe@339 1396 ) AS "subquery"
jbe@339 1397 WHERE "id" = "subquery"."initiative_id";
jbe@339 1398 -- mark eligible initiatives:
jbe@339 1399 UPDATE "initiative" SET "eligible" = TRUE
jbe@339 1400 WHERE "issue_id" = "issue_id_p"
jbe@339 1401 AND "initiative"."direct_majority"
jbe@339 1402 AND "initiative"."indirect_majority"
jbe@339 1403 AND "initiative"."better_than_status_quo"
jbe@339 1404 AND (
jbe@339 1405 "policy_row"."no_multistage_majority" = FALSE OR
jbe@339 1406 "initiative"."multistage_majority" = FALSE )
jbe@339 1407 AND (
jbe@339 1408 "policy_row"."no_reverse_beat_path" = FALSE OR
jbe@339 1409 "initiative"."reverse_beat_path" = FALSE );
jbe@339 1410 -- mark final winner:
jbe@339 1411 UPDATE "initiative" SET "winner" = TRUE
jbe@339 1412 FROM (
jbe@339 1413 SELECT "id" AS "initiative_id"
jbe@339 1414 FROM "initiative"
jbe@339 1415 WHERE "issue_id" = "issue_id_p" AND "eligible"
jbe@339 1416 ORDER BY
jbe@339 1417 "schulze_rank",
jbe@339 1418 "id"
jbe@339 1419 LIMIT 1
jbe@339 1420 ) AS "subquery"
jbe@339 1421 WHERE "id" = "subquery"."initiative_id";
jbe@339 1422 -- write (final) ranks:
jbe@339 1423 "rank_v" := 1;
jbe@339 1424 FOR "initiative_id_v" IN
jbe@339 1425 SELECT "id"
jbe@339 1426 FROM "initiative"
jbe@339 1427 WHERE "issue_id" = "issue_id_p" AND "admitted"
jbe@339 1428 ORDER BY
jbe@339 1429 "winner" DESC,
jbe@339 1430 "eligible" DESC,
jbe@339 1431 "schulze_rank",
jbe@339 1432 "id"
jbe@339 1433 LOOP
jbe@339 1434 UPDATE "initiative" SET "rank" = "rank_v"
jbe@339 1435 WHERE "id" = "initiative_id_v";
jbe@339 1436 "rank_v" := "rank_v" + 1;
jbe@339 1437 END LOOP;
jbe@339 1438 -- set schulze rank of status quo and mark issue as finished:
jbe@339 1439 UPDATE "issue" SET
jbe@339 1440 "status_quo_schulze_rank" = "rank_ary"["dimension_v"],
jbe@339 1441 "state" =
jbe@339 1442 CASE WHEN EXISTS (
jbe@339 1443 SELECT NULL FROM "initiative"
jbe@339 1444 WHERE "issue_id" = "issue_id_p" AND "winner"
jbe@339 1445 ) THEN
jbe@339 1446 'finished_with_winner'::"issue_state"
jbe@339 1447 ELSE
jbe@339 1448 'finished_without_winner'::"issue_state"
jbe@339 1449 END,
jbe@339 1450 "closed" = "phase_finished",
jbe@339 1451 "phase_finished" = NULL
jbe@339 1452 WHERE "id" = "issue_id_p";
jbe@339 1453 RETURN;
jbe@339 1454 END;
jbe@339 1455 $$;
jbe@339 1456
jbe@339 1457 DROP FUNCTION "check_issue"("issue"."id"%TYPE);
jbe@339 1458
jbe@339 1459 CREATE TYPE "check_issue_persistence" AS (
jbe@339 1460 "state" "issue_state",
jbe@339 1461 "phase_finished" BOOLEAN,
jbe@339 1462 "issue_revoked" BOOLEAN,
jbe@339 1463 "snapshot_created" BOOLEAN,
jbe@339 1464 "harmonic_weights_set" BOOLEAN,
jbe@339 1465 "closed_voting" BOOLEAN );
jbe@339 1466 COMMENT ON TYPE "check_issue_persistence" IS 'Type of data returned by "check_issue" function, to be passed to subsequent calls of the same function';
jbe@339 1467
jbe@339 1468 CREATE FUNCTION "check_issue"
jbe@339 1469 ( "issue_id_p" "issue"."id"%TYPE,
jbe@339 1470 "persist" "check_issue_persistence" )
jbe@339 1471 RETURNS "check_issue_persistence"
jbe@339 1472 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@339 1473 DECLARE
jbe@339 1474 "issue_row" "issue"%ROWTYPE;
jbe@339 1475 "policy_row" "policy"%ROWTYPE;
jbe@339 1476 "initiative_row" "initiative"%ROWTYPE;
jbe@339 1477 "state_v" "issue_state";
jbe@339 1478 BEGIN
jbe@339 1479 PERFORM "require_transaction_isolation"();
jbe@339 1480 IF "persist" ISNULL THEN
jbe@339 1481 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
jbe@339 1482 FOR UPDATE;
jbe@339 1483 IF "issue_row"."closed" NOTNULL THEN
jbe@339 1484 RETURN NULL;
jbe@339 1485 END IF;
jbe@339 1486 "persist"."state" := "issue_row"."state";
jbe@339 1487 IF
jbe@339 1488 ( "issue_row"."state" = 'admission' AND now() >=
jbe@339 1489 "issue_row"."created" + "issue_row"."admission_time" ) OR
jbe@339 1490 ( "issue_row"."state" = 'discussion' AND now() >=
jbe@339 1491 "issue_row"."accepted" + "issue_row"."discussion_time" ) OR
jbe@339 1492 ( "issue_row"."state" = 'verification' AND now() >=
jbe@339 1493 "issue_row"."half_frozen" + "issue_row"."verification_time" ) OR
jbe@339 1494 ( "issue_row"."state" = 'voting' AND now() >=
jbe@339 1495 "issue_row"."fully_frozen" + "issue_row"."voting_time" )
jbe@339 1496 THEN
jbe@339 1497 "persist"."phase_finished" := TRUE;
jbe@339 1498 ELSE
jbe@339 1499 "persist"."phase_finished" := FALSE;
jbe@339 1500 END IF;
jbe@339 1501 IF
jbe@339 1502 NOT EXISTS (
jbe@339 1503 -- all initiatives are revoked
jbe@339 1504 SELECT NULL FROM "initiative"
jbe@339 1505 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
jbe@339 1506 ) AND (
jbe@339 1507 -- and issue has not been accepted yet
jbe@339 1508 "persist"."state" = 'admission' OR
jbe@339 1509 -- or verification time has elapsed
jbe@339 1510 ( "persist"."state" = 'verification' AND
jbe@339 1511 "persist"."phase_finished" ) OR
jbe@339 1512 -- or no initiatives have been revoked lately
jbe@339 1513 NOT EXISTS (
jbe@339 1514 SELECT NULL FROM "initiative"
jbe@339 1515 WHERE "issue_id" = "issue_id_p"
jbe@339 1516 AND now() < "revoked" + "issue_row"."verification_time"
jbe@339 1517 )
jbe@339 1518 )
jbe@339 1519 THEN
jbe@339 1520 "persist"."issue_revoked" := TRUE;
jbe@339 1521 ELSE
jbe@339 1522 "persist"."issue_revoked" := FALSE;
jbe@339 1523 END IF;
jbe@339 1524 IF "persist"."phase_finished" OR "persist"."issue_revoked" THEN
jbe@339 1525 UPDATE "issue" SET "phase_finished" = now()
jbe@339 1526 WHERE "id" = "issue_row"."id";
jbe@339 1527 RETURN "persist";
jbe@339 1528 ELSIF
jbe@339 1529 "persist"."state" IN ('admission', 'discussion', 'verification')
jbe@339 1530 THEN
jbe@339 1531 RETURN "persist";
jbe@339 1532 ELSE
jbe@339 1533 RETURN NULL;
jbe@339 1534 END IF;
jbe@339 1535 END IF;
jbe@339 1536 IF
jbe@339 1537 "persist"."state" IN ('admission', 'discussion', 'verification') AND
jbe@339 1538 coalesce("persist"."snapshot_created", FALSE) = FALSE
jbe@339 1539 THEN
jbe@339 1540 PERFORM "create_snapshot"("issue_id_p");
jbe@339 1541 "persist"."snapshot_created" = TRUE;
jbe@339 1542 IF "persist"."phase_finished" THEN
jbe@339 1543 IF "persist"."state" = 'admission' THEN
jbe@339 1544 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
jbe@339 1545 ELSIF "persist"."state" = 'discussion' THEN
jbe@339 1546 PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze');
jbe@339 1547 ELSIF "persist"."state" = 'verification' THEN
jbe@339 1548 PERFORM "set_snapshot_event"("issue_id_p", 'full_freeze');
jbe@339 1549 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
jbe@339 1550 SELECT * INTO "policy_row" FROM "policy"
jbe@339 1551 WHERE "id" = "issue_row"."policy_id";
jbe@339 1552 FOR "initiative_row" IN
jbe@339 1553 SELECT * FROM "initiative"
jbe@339 1554 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
jbe@339 1555 FOR UPDATE
jbe@339 1556 LOOP
jbe@339 1557 IF
jbe@339 1558 "initiative_row"."polling" OR (
jbe@339 1559 "initiative_row"."satisfied_supporter_count" > 0 AND
jbe@339 1560 "initiative_row"."satisfied_supporter_count" *
jbe@339 1561 "policy_row"."initiative_quorum_den" >=
jbe@339 1562 "issue_row"."population" * "policy_row"."initiative_quorum_num"
jbe@339 1563 )
jbe@339 1564 THEN
jbe@339 1565 UPDATE "initiative" SET "admitted" = TRUE
jbe@339 1566 WHERE "id" = "initiative_row"."id";
jbe@339 1567 ELSE
jbe@339 1568 UPDATE "initiative" SET "admitted" = FALSE
jbe@339 1569 WHERE "id" = "initiative_row"."id";
jbe@339 1570 END IF;
jbe@339 1571 END LOOP;
jbe@339 1572 END IF;
jbe@339 1573 END IF;
jbe@339 1574 RETURN "persist";
jbe@339 1575 END IF;
jbe@339 1576 IF
jbe@339 1577 "persist"."state" IN ('admission', 'discussion', 'verification') AND
jbe@339 1578 coalesce("persist"."harmonic_weights_set", FALSE) = FALSE
jbe@339 1579 THEN
jbe@339 1580 PERFORM "set_harmonic_initiative_weights"("issue_id_p");
jbe@339 1581 "persist"."harmonic_weights_set" = TRUE;
jbe@339 1582 IF
jbe@339 1583 "persist"."phase_finished" OR
jbe@339 1584 "persist"."issue_revoked" OR
jbe@339 1585 "persist"."state" = 'admission'
jbe@339 1586 THEN
jbe@339 1587 RETURN "persist";
jbe@339 1588 ELSE
jbe@339 1589 RETURN NULL;
jbe@339 1590 END IF;
jbe@339 1591 END IF;
jbe@339 1592 IF "persist"."issue_revoked" THEN
jbe@339 1593 IF "persist"."state" = 'admission' THEN
jbe@339 1594 "state_v" := 'canceled_revoked_before_accepted';
jbe@339 1595 ELSIF "persist"."state" = 'discussion' THEN
jbe@339 1596 "state_v" := 'canceled_after_revocation_during_discussion';
jbe@339 1597 ELSIF "persist"."state" = 'verification' THEN
jbe@339 1598 "state_v" := 'canceled_after_revocation_during_verification';
jbe@339 1599 END IF;
jbe@339 1600 UPDATE "issue" SET
jbe@339 1601 "state" = "state_v",
jbe@339 1602 "closed" = "phase_finished",
jbe@339 1603 "phase_finished" = NULL
jbe@339 1604 WHERE "id" = "issue_id_p";
jbe@339 1605 RETURN NULL;
jbe@339 1606 END IF;
jbe@339 1607 IF "persist"."state" = 'admission' THEN
jbe@339 1608 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
jbe@339 1609 FOR UPDATE;
jbe@339 1610 SELECT * INTO "policy_row"
jbe@339 1611 FROM "policy" WHERE "id" = "issue_row"."policy_id";
jbe@339 1612 IF EXISTS (
jbe@339 1613 SELECT NULL FROM "initiative"
jbe@339 1614 WHERE "issue_id" = "issue_id_p"
jbe@339 1615 AND "supporter_count" > 0
jbe@339 1616 AND "supporter_count" * "policy_row"."issue_quorum_den"
jbe@339 1617 >= "issue_row"."population" * "policy_row"."issue_quorum_num"
jbe@339 1618 ) THEN
jbe@339 1619 UPDATE "issue" SET
jbe@339 1620 "state" = 'discussion',
jbe@339 1621 "accepted" = coalesce("phase_finished", now()),
jbe@339 1622 "phase_finished" = NULL
jbe@339 1623 WHERE "id" = "issue_id_p";
jbe@339 1624 ELSIF "issue_row"."phase_finished" NOTNULL THEN
jbe@339 1625 UPDATE "issue" SET
jbe@339 1626 "state" = 'canceled_issue_not_accepted',
jbe@339 1627 "closed" = "phase_finished",
jbe@339 1628 "phase_finished" = NULL
jbe@339 1629 WHERE "id" = "issue_id_p";
jbe@339 1630 END IF;
jbe@339 1631 RETURN NULL;
jbe@339 1632 END IF;
jbe@339 1633 IF "persist"."phase_finished" THEN
jbe@339 1634 if "persist"."state" = 'discussion' THEN
jbe@339 1635 UPDATE "issue" SET
jbe@339 1636 "state" = 'verification',
jbe@339 1637 "half_frozen" = "phase_finished",
jbe@339 1638 "phase_finished" = NULL
jbe@339 1639 WHERE "id" = "issue_id_p";
jbe@339 1640 RETURN NULL;
jbe@339 1641 END IF;
jbe@339 1642 IF "persist"."state" = 'verification' THEN
jbe@339 1643 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
jbe@339 1644 FOR UPDATE;
jbe@339 1645 SELECT * INTO "policy_row" FROM "policy"
jbe@339 1646 WHERE "id" = "issue_row"."policy_id";
jbe@339 1647 IF EXISTS (
jbe@339 1648 SELECT NULL FROM "initiative"
jbe@339 1649 WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE
jbe@339 1650 ) THEN
jbe@339 1651 UPDATE "issue" SET
jbe@339 1652 "state" = 'voting',
jbe@339 1653 "accepted" = coalesce("accepted", "phase_finished"),
jbe@339 1654 "half_frozen" = coalesce("half_frozen", "phase_finished"),
jbe@339 1655 "fully_frozen" = "phase_finished",
jbe@339 1656 "phase_finished" = NULL
jbe@339 1657 WHERE "id" = "issue_id_p";
jbe@339 1658 ELSE
jbe@339 1659 UPDATE "issue" SET
jbe@339 1660 "state" = 'canceled_no_initiative_admitted',
jbe@339 1661 "accepted" = coalesce("accepted", "phase_finished"),
jbe@339 1662 "half_frozen" = coalesce("half_frozen", "phase_finished"),
jbe@339 1663 "fully_frozen" = "phase_finished",
jbe@339 1664 "closed" = "phase_finished",
jbe@339 1665 "phase_finished" = NULL
jbe@339 1666 WHERE "id" = "issue_id_p";
jbe@339 1667 -- NOTE: The following DELETE statements have effect only when
jbe@339 1668 -- issue state has been manipulated
jbe@339 1669 DELETE FROM "direct_voter" WHERE "issue_id" = "issue_id_p";
jbe@339 1670 DELETE FROM "delegating_voter" WHERE "issue_id" = "issue_id_p";
jbe@339 1671 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
jbe@339 1672 END IF;
jbe@339 1673 RETURN NULL;
jbe@339 1674 END IF;
jbe@339 1675 IF "persist"."state" = 'voting' THEN
jbe@339 1676 IF coalesce("persist"."closed_voting", FALSE) = FALSE THEN
jbe@339 1677 PERFORM "close_voting"("issue_id_p");
jbe@339 1678 "persist"."closed_voting" = TRUE;
jbe@339 1679 RETURN "persist";
jbe@339 1680 END IF;
jbe@339 1681 PERFORM "calculate_ranks"("issue_id_p");
jbe@339 1682 RETURN NULL;
jbe@339 1683 END IF;
jbe@339 1684 END IF;
jbe@339 1685 RAISE WARNING 'should not happen';
jbe@339 1686 RETURN NULL;
jbe@339 1687 END;
jbe@339 1688 $$;
jbe@339 1689 COMMENT ON FUNCTION "check_issue"
jbe@339 1690 ( "issue"."id"%TYPE,
jbe@339 1691 "check_issue_persistence" )
jbe@339 1692 IS 'Precalculate supporter counts etc. for a given issue, and check, if status change is required, and perform the status change when necessary; Function must be called multiple times with the previous result as second parameter, until the result is NULL (see source code of function "check_everything")';
jbe@339 1693
jbe@339 1694 CREATE OR REPLACE FUNCTION "check_everything"()
jbe@339 1695 RETURNS VOID
jbe@339 1696 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@339 1697 DECLARE
jbe@339 1698 "issue_id_v" "issue"."id"%TYPE;
jbe@339 1699 "persist_v" "check_issue_persistence";
jbe@339 1700 BEGIN
jbe@339 1701 RAISE WARNING 'Function "check_everything" should only be used for development and debugging purposes';
jbe@339 1702 DELETE FROM "expired_session";
jbe@339 1703 PERFORM "check_activity"();
jbe@339 1704 PERFORM "calculate_member_counts"();
jbe@339 1705 FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP
jbe@339 1706 "persist_v" := NULL;
jbe@339 1707 LOOP
jbe@339 1708 "persist_v" := "check_issue"("issue_id_v", "persist_v");
jbe@339 1709 EXIT WHEN "persist_v" ISNULL;
jbe@339 1710 END LOOP;
jbe@339 1711 END LOOP;
jbe@339 1712 RETURN;
jbe@339 1713 END;
jbe@339 1714 $$;
jbe@339 1715 COMMENT ON FUNCTION "check_everything"() IS 'Amongst other regular tasks this function performs "check_issue" for every open issue. Use this function only for development and debugging purposes, as you may run into locking and/or serialization problems in productive environments.';
jbe@339 1716
jbe@339 1717 CREATE OR REPLACE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE)
jbe@339 1718 RETURNS VOID
jbe@339 1719 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@339 1720 DECLARE
jbe@339 1721 "issue_row" "issue"%ROWTYPE;
jbe@339 1722 BEGIN
jbe@339 1723 SELECT * INTO "issue_row"
jbe@339 1724 FROM "issue" WHERE "id" = "issue_id_p"
jbe@339 1725 FOR UPDATE;
jbe@339 1726 IF "issue_row"."cleaned" ISNULL THEN
jbe@339 1727 UPDATE "issue" SET
jbe@339 1728 "state" = 'voting',
jbe@339 1729 "closed" = NULL
jbe@339 1730 WHERE "id" = "issue_id_p";
jbe@339 1731 DELETE FROM "delegating_voter"
jbe@339 1732 WHERE "issue_id" = "issue_id_p";
jbe@339 1733 DELETE FROM "direct_voter"
jbe@339 1734 WHERE "issue_id" = "issue_id_p";
jbe@339 1735 DELETE FROM "delegating_interest_snapshot"
jbe@339 1736 WHERE "issue_id" = "issue_id_p";
jbe@339 1737 DELETE FROM "direct_interest_snapshot"
jbe@339 1738 WHERE "issue_id" = "issue_id_p";
jbe@339 1739 DELETE FROM "delegating_population_snapshot"
jbe@339 1740 WHERE "issue_id" = "issue_id_p";
jbe@339 1741 DELETE FROM "direct_population_snapshot"
jbe@339 1742 WHERE "issue_id" = "issue_id_p";
jbe@339 1743 DELETE FROM "non_voter"
jbe@339 1744 WHERE "issue_id" = "issue_id_p";
jbe@339 1745 DELETE FROM "delegation"
jbe@339 1746 WHERE "issue_id" = "issue_id_p";
jbe@339 1747 DELETE FROM "supporter"
jbe@339 1748 USING "initiative" -- NOTE: due to missing index on issue_id
jbe@339 1749 WHERE "initiative"."issue_id" = "issue_id_p"
jbe@339 1750 AND "supporter"."initiative_id" = "initiative_id";
jbe@339 1751 UPDATE "issue" SET
jbe@339 1752 "state" = "issue_row"."state",
jbe@339 1753 "closed" = "issue_row"."closed",
jbe@339 1754 "cleaned" = now()
jbe@339 1755 WHERE "id" = "issue_id_p";
jbe@339 1756 END IF;
jbe@339 1757 RETURN;
jbe@339 1758 END;
jbe@339 1759 $$;
jbe@339 1760
jbe@339 1761 COMMIT;

Impressum / About Us