liquid_feedback_core

annotate update/core-update.v2.1.0-v2.2.0.sql @ 344:978950dd9e32

find changesets by author, revision, files, or words in the commit message
Added comments for functions "require_transaction_isolation"() and "dont_require_transaction_isolation"()
author jbe
date Thu Feb 21 18:03:15 2013 +0100 (2013-02-21)
parents c0480670965d
children 6c8209b711af
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@344 338 COMMENT ON FUNCTION "require_transaction_isolation"() IS 'Throws an exception, if transaction isolation level is too low to provide a consistent snapshot';
jbe@339 339
jbe@339 340 CREATE FUNCTION "dont_require_transaction_isolation"()
jbe@339 341 RETURNS VOID
jbe@339 342 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@339 343 BEGIN
jbe@339 344 IF
jbe@339 345 current_setting('transaction_isolation') IN
jbe@339 346 ('repeatable read', 'serializable')
jbe@339 347 THEN
jbe@339 348 RAISE WARNING 'Unneccessary transaction isolation level: %',
jbe@339 349 current_setting('transaction_isolation');
jbe@339 350 END IF;
jbe@339 351 RETURN;
jbe@339 352 END;
jbe@339 353 $$;
jbe@344 354 COMMENT ON FUNCTION "dont_require_transaction_isolation"() IS 'Raises a warning, if transaction isolation level is higher than READ COMMITTED';
jbe@339 355
jbe@339 356 CREATE OR REPLACE FUNCTION "check_activity"()
jbe@339 357 RETURNS VOID
jbe@339 358 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@339 359 DECLARE
jbe@339 360 "system_setting_row" "system_setting"%ROWTYPE;
jbe@339 361 BEGIN
jbe@339 362 PERFORM "dont_require_transaction_isolation"();
jbe@339 363 SELECT * INTO "system_setting_row" FROM "system_setting";
jbe@339 364 IF "system_setting_row"."member_ttl" NOTNULL THEN
jbe@339 365 UPDATE "member" SET "active" = FALSE
jbe@339 366 WHERE "active" = TRUE
jbe@339 367 AND "last_activity" < (now() - "system_setting_row"."member_ttl")::DATE;
jbe@339 368 END IF;
jbe@339 369 RETURN;
jbe@339 370 END;
jbe@339 371 $$;
jbe@339 372
jbe@339 373 CREATE OR REPLACE FUNCTION "calculate_member_counts"()
jbe@339 374 RETURNS VOID
jbe@339 375 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@339 376 BEGIN
jbe@339 377 PERFORM "require_transaction_isolation"();
jbe@339 378 DELETE FROM "member_count";
jbe@339 379 INSERT INTO "member_count" ("total_count")
jbe@339 380 SELECT "total_count" FROM "member_count_view";
jbe@339 381 UPDATE "unit" SET "member_count" = "view"."member_count"
jbe@339 382 FROM "unit_member_count" AS "view"
jbe@339 383 WHERE "view"."unit_id" = "unit"."id";
jbe@339 384 UPDATE "area" SET
jbe@339 385 "direct_member_count" = "view"."direct_member_count",
jbe@339 386 "member_weight" = "view"."member_weight"
jbe@339 387 FROM "area_member_count" AS "view"
jbe@339 388 WHERE "view"."area_id" = "area"."id";
jbe@339 389 RETURN;
jbe@339 390 END;
jbe@339 391 $$;
jbe@339 392
jbe@339 393 CREATE VIEW "remaining_harmonic_supporter_weight" AS
jbe@339 394 SELECT
jbe@339 395 "direct_interest_snapshot"."issue_id",
jbe@339 396 "direct_interest_snapshot"."event",
jbe@339 397 "direct_interest_snapshot"."member_id",
jbe@339 398 "direct_interest_snapshot"."weight" AS "weight_num",
jbe@339 399 count("initiative"."id") AS "weight_den"
jbe@339 400 FROM "issue"
jbe@339 401 JOIN "direct_interest_snapshot"
jbe@339 402 ON "issue"."id" = "direct_interest_snapshot"."issue_id"
jbe@339 403 AND "issue"."latest_snapshot_event" = "direct_interest_snapshot"."event"
jbe@339 404 JOIN "initiative"
jbe@339 405 ON "issue"."id" = "initiative"."issue_id"
jbe@339 406 AND "initiative"."harmonic_weight" ISNULL
jbe@339 407 JOIN "direct_supporter_snapshot"
jbe@339 408 ON "initiative"."id" = "direct_supporter_snapshot"."initiative_id"
jbe@339 409 AND "direct_interest_snapshot"."event" = "direct_supporter_snapshot"."event"
jbe@339 410 AND "direct_interest_snapshot"."member_id" = "direct_supporter_snapshot"."member_id"
jbe@339 411 AND (
jbe@339 412 "direct_supporter_snapshot"."satisfied" = TRUE OR
jbe@339 413 coalesce("initiative"."admitted", FALSE) = FALSE
jbe@339 414 )
jbe@339 415 GROUP BY
jbe@339 416 "direct_interest_snapshot"."issue_id",
jbe@339 417 "direct_interest_snapshot"."event",
jbe@339 418 "direct_interest_snapshot"."member_id",
jbe@339 419 "direct_interest_snapshot"."weight";
jbe@339 420 COMMENT ON VIEW "remaining_harmonic_supporter_weight" IS 'Helper view for function "set_harmonic_initiative_weights"';
jbe@339 421
jbe@339 422 CREATE VIEW "remaining_harmonic_initiative_weight_summands" AS
jbe@339 423 SELECT
jbe@339 424 "initiative"."issue_id",
jbe@339 425 "initiative"."id" AS "initiative_id",
jbe@339 426 "initiative"."admitted",
jbe@339 427 sum("remaining_harmonic_supporter_weight"."weight_num") AS "weight_num",
jbe@339 428 "remaining_harmonic_supporter_weight"."weight_den"
jbe@339 429 FROM "remaining_harmonic_supporter_weight"
jbe@339 430 JOIN "initiative"
jbe@339 431 ON "remaining_harmonic_supporter_weight"."issue_id" = "initiative"."issue_id"
jbe@339 432 AND "initiative"."harmonic_weight" ISNULL
jbe@339 433 JOIN "direct_supporter_snapshot"
jbe@339 434 ON "initiative"."id" = "direct_supporter_snapshot"."initiative_id"
jbe@339 435 AND "remaining_harmonic_supporter_weight"."event" = "direct_supporter_snapshot"."event"
jbe@339 436 AND "remaining_harmonic_supporter_weight"."member_id" = "direct_supporter_snapshot"."member_id"
jbe@339 437 AND (
jbe@339 438 "direct_supporter_snapshot"."satisfied" = TRUE OR
jbe@339 439 coalesce("initiative"."admitted", FALSE) = FALSE
jbe@339 440 )
jbe@339 441 GROUP BY
jbe@339 442 "initiative"."issue_id",
jbe@339 443 "initiative"."id",
jbe@339 444 "initiative"."admitted",
jbe@339 445 "remaining_harmonic_supporter_weight"."weight_den";
jbe@339 446 COMMENT ON VIEW "remaining_harmonic_initiative_weight_summands" IS 'Helper view for function "set_harmonic_initiative_weights"';
jbe@339 447
jbe@339 448 CREATE FUNCTION "set_harmonic_initiative_weights"
jbe@339 449 ( "issue_id_p" "issue"."id"%TYPE )
jbe@339 450 RETURNS VOID
jbe@339 451 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@339 452 DECLARE
jbe@339 453 "weight_row" "remaining_harmonic_initiative_weight_summands"%ROWTYPE;
jbe@339 454 "i" INT4;
jbe@339 455 "count_v" INT4;
jbe@339 456 "summand_v" FLOAT;
jbe@339 457 "id_ary" INT4[];
jbe@339 458 "weight_ary" FLOAT[];
jbe@339 459 "min_weight_v" FLOAT;
jbe@339 460 BEGIN
jbe@339 461 PERFORM "require_transaction_isolation"();
jbe@339 462 UPDATE "initiative" SET "harmonic_weight" = NULL
jbe@339 463 WHERE "issue_id" = "issue_id_p";
jbe@339 464 LOOP
jbe@339 465 "min_weight_v" := NULL;
jbe@339 466 "i" := 0;
jbe@339 467 "count_v" := 0;
jbe@339 468 FOR "weight_row" IN
jbe@339 469 SELECT * FROM "remaining_harmonic_initiative_weight_summands"
jbe@339 470 WHERE "issue_id" = "issue_id_p"
jbe@339 471 AND (
jbe@339 472 coalesce("admitted", FALSE) = FALSE OR NOT EXISTS (
jbe@339 473 SELECT NULL FROM "initiative"
jbe@339 474 WHERE "issue_id" = "issue_id_p"
jbe@339 475 AND "harmonic_weight" ISNULL
jbe@339 476 AND coalesce("admitted", FALSE) = FALSE
jbe@339 477 )
jbe@339 478 )
jbe@339 479 ORDER BY "initiative_id" DESC, "weight_den" DESC
jbe@339 480 -- NOTE: non-admitted initiatives placed first (at last positions),
jbe@339 481 -- latest initiatives treated worse in case of tie
jbe@339 482 LOOP
jbe@339 483 "summand_v" := "weight_row"."weight_num"::FLOAT / "weight_row"."weight_den"::FLOAT;
jbe@339 484 IF "i" = 0 OR "weight_row"."initiative_id" != "id_ary"["i"] THEN
jbe@339 485 "i" := "i" + 1;
jbe@339 486 "count_v" := "i";
jbe@339 487 "id_ary"["i"] := "weight_row"."initiative_id";
jbe@339 488 "weight_ary"["i"] := "summand_v";
jbe@339 489 ELSE
jbe@339 490 "weight_ary"["i"] := "weight_ary"["i"] + "summand_v";
jbe@339 491 END IF;
jbe@339 492 END LOOP;
jbe@339 493 EXIT WHEN "count_v" = 0;
jbe@339 494 "i" := 1;
jbe@339 495 LOOP
jbe@339 496 "weight_ary"["i"] := "weight_ary"["i"]::NUMERIC(18,9)::NUMERIC(12,3);
jbe@339 497 IF "min_weight_v" ISNULL OR "weight_ary"["i"] < "min_weight_v" THEN
jbe@339 498 "min_weight_v" := "weight_ary"["i"];
jbe@339 499 END IF;
jbe@339 500 "i" := "i" + 1;
jbe@339 501 EXIT WHEN "i" > "count_v";
jbe@339 502 END LOOP;
jbe@339 503 "i" := 1;
jbe@339 504 LOOP
jbe@339 505 IF "weight_ary"["i"] = "min_weight_v" THEN
jbe@339 506 UPDATE "initiative" SET "harmonic_weight" = "min_weight_v"
jbe@339 507 WHERE "id" = "id_ary"["i"];
jbe@339 508 EXIT;
jbe@339 509 END IF;
jbe@339 510 "i" := "i" + 1;
jbe@339 511 END LOOP;
jbe@339 512 END LOOP;
jbe@339 513 UPDATE "initiative" SET "harmonic_weight" = 0
jbe@339 514 WHERE "issue_id" = "issue_id_p" AND "harmonic_weight" ISNULL;
jbe@339 515 END;
jbe@339 516 $$;
jbe@339 517 COMMENT ON FUNCTION "set_harmonic_initiative_weights"
jbe@339 518 ( "issue"."id"%TYPE )
jbe@339 519 IS 'Calculates and sets "harmonic_weight" of initiatives in a given issue';
jbe@339 520
jbe@339 521 CREATE OR REPLACE FUNCTION "weight_of_added_delegations_for_population_snapshot"
jbe@339 522 ( "issue_id_p" "issue"."id"%TYPE,
jbe@339 523 "member_id_p" "member"."id"%TYPE,
jbe@339 524 "delegate_member_ids_p" "delegating_population_snapshot"."delegate_member_ids"%TYPE )
jbe@339 525 RETURNS "direct_population_snapshot"."weight"%TYPE
jbe@339 526 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@339 527 DECLARE
jbe@339 528 "issue_delegation_row" "issue_delegation"%ROWTYPE;
jbe@339 529 "delegate_member_ids_v" "delegating_population_snapshot"."delegate_member_ids"%TYPE;
jbe@339 530 "weight_v" INT4;
jbe@339 531 "sub_weight_v" INT4;
jbe@339 532 BEGIN
jbe@339 533 PERFORM "require_transaction_isolation"();
jbe@339 534 "weight_v" := 0;
jbe@339 535 FOR "issue_delegation_row" IN
jbe@339 536 SELECT * FROM "issue_delegation"
jbe@339 537 WHERE "trustee_id" = "member_id_p"
jbe@339 538 AND "issue_id" = "issue_id_p"
jbe@339 539 LOOP
jbe@339 540 IF NOT EXISTS (
jbe@339 541 SELECT NULL FROM "direct_population_snapshot"
jbe@339 542 WHERE "issue_id" = "issue_id_p"
jbe@339 543 AND "event" = 'periodic'
jbe@339 544 AND "member_id" = "issue_delegation_row"."truster_id"
jbe@339 545 ) AND NOT EXISTS (
jbe@339 546 SELECT NULL FROM "delegating_population_snapshot"
jbe@339 547 WHERE "issue_id" = "issue_id_p"
jbe@339 548 AND "event" = 'periodic'
jbe@339 549 AND "member_id" = "issue_delegation_row"."truster_id"
jbe@339 550 ) THEN
jbe@339 551 "delegate_member_ids_v" :=
jbe@339 552 "member_id_p" || "delegate_member_ids_p";
jbe@339 553 INSERT INTO "delegating_population_snapshot" (
jbe@339 554 "issue_id",
jbe@339 555 "event",
jbe@339 556 "member_id",
jbe@339 557 "scope",
jbe@339 558 "delegate_member_ids"
jbe@339 559 ) VALUES (
jbe@339 560 "issue_id_p",
jbe@339 561 'periodic',
jbe@339 562 "issue_delegation_row"."truster_id",
jbe@339 563 "issue_delegation_row"."scope",
jbe@339 564 "delegate_member_ids_v"
jbe@339 565 );
jbe@339 566 "sub_weight_v" := 1 +
jbe@339 567 "weight_of_added_delegations_for_population_snapshot"(
jbe@339 568 "issue_id_p",
jbe@339 569 "issue_delegation_row"."truster_id",
jbe@339 570 "delegate_member_ids_v"
jbe@339 571 );
jbe@339 572 UPDATE "delegating_population_snapshot"
jbe@339 573 SET "weight" = "sub_weight_v"
jbe@339 574 WHERE "issue_id" = "issue_id_p"
jbe@339 575 AND "event" = 'periodic'
jbe@339 576 AND "member_id" = "issue_delegation_row"."truster_id";
jbe@339 577 "weight_v" := "weight_v" + "sub_weight_v";
jbe@339 578 END IF;
jbe@339 579 END LOOP;
jbe@339 580 RETURN "weight_v";
jbe@339 581 END;
jbe@339 582 $$;
jbe@339 583
jbe@339 584 CREATE OR REPLACE FUNCTION "create_population_snapshot"
jbe@339 585 ( "issue_id_p" "issue"."id"%TYPE )
jbe@339 586 RETURNS VOID
jbe@339 587 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@339 588 DECLARE
jbe@339 589 "member_id_v" "member"."id"%TYPE;
jbe@339 590 BEGIN
jbe@339 591 PERFORM "require_transaction_isolation"();
jbe@339 592 DELETE FROM "direct_population_snapshot"
jbe@339 593 WHERE "issue_id" = "issue_id_p"
jbe@339 594 AND "event" = 'periodic';
jbe@339 595 DELETE FROM "delegating_population_snapshot"
jbe@339 596 WHERE "issue_id" = "issue_id_p"
jbe@339 597 AND "event" = 'periodic';
jbe@339 598 INSERT INTO "direct_population_snapshot"
jbe@339 599 ("issue_id", "event", "member_id")
jbe@339 600 SELECT
jbe@339 601 "issue_id_p" AS "issue_id",
jbe@339 602 'periodic'::"snapshot_event" AS "event",
jbe@339 603 "member"."id" AS "member_id"
jbe@339 604 FROM "issue"
jbe@339 605 JOIN "area" ON "issue"."area_id" = "area"."id"
jbe@339 606 JOIN "membership" ON "area"."id" = "membership"."area_id"
jbe@339 607 JOIN "member" ON "membership"."member_id" = "member"."id"
jbe@339 608 JOIN "privilege"
jbe@339 609 ON "privilege"."unit_id" = "area"."unit_id"
jbe@339 610 AND "privilege"."member_id" = "member"."id"
jbe@339 611 WHERE "issue"."id" = "issue_id_p"
jbe@339 612 AND "member"."active" AND "privilege"."voting_right"
jbe@339 613 UNION
jbe@339 614 SELECT
jbe@339 615 "issue_id_p" AS "issue_id",
jbe@339 616 'periodic'::"snapshot_event" AS "event",
jbe@339 617 "member"."id" AS "member_id"
jbe@339 618 FROM "issue"
jbe@339 619 JOIN "area" ON "issue"."area_id" = "area"."id"
jbe@339 620 JOIN "interest" ON "issue"."id" = "interest"."issue_id"
jbe@339 621 JOIN "member" ON "interest"."member_id" = "member"."id"
jbe@339 622 JOIN "privilege"
jbe@339 623 ON "privilege"."unit_id" = "area"."unit_id"
jbe@339 624 AND "privilege"."member_id" = "member"."id"
jbe@339 625 WHERE "issue"."id" = "issue_id_p"
jbe@339 626 AND "member"."active" AND "privilege"."voting_right";
jbe@339 627 FOR "member_id_v" IN
jbe@339 628 SELECT "member_id" FROM "direct_population_snapshot"
jbe@339 629 WHERE "issue_id" = "issue_id_p"
jbe@339 630 AND "event" = 'periodic'
jbe@339 631 LOOP
jbe@339 632 UPDATE "direct_population_snapshot" SET
jbe@339 633 "weight" = 1 +
jbe@339 634 "weight_of_added_delegations_for_population_snapshot"(
jbe@339 635 "issue_id_p",
jbe@339 636 "member_id_v",
jbe@339 637 '{}'
jbe@339 638 )
jbe@339 639 WHERE "issue_id" = "issue_id_p"
jbe@339 640 AND "event" = 'periodic'
jbe@339 641 AND "member_id" = "member_id_v";
jbe@339 642 END LOOP;
jbe@339 643 RETURN;
jbe@339 644 END;
jbe@339 645 $$;
jbe@339 646
jbe@339 647 CREATE OR REPLACE FUNCTION "weight_of_added_delegations_for_interest_snapshot"
jbe@339 648 ( "issue_id_p" "issue"."id"%TYPE,
jbe@339 649 "member_id_p" "member"."id"%TYPE,
jbe@339 650 "delegate_member_ids_p" "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
jbe@339 651 RETURNS "direct_interest_snapshot"."weight"%TYPE
jbe@339 652 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@339 653 DECLARE
jbe@339 654 "issue_delegation_row" "issue_delegation"%ROWTYPE;
jbe@339 655 "delegate_member_ids_v" "delegating_interest_snapshot"."delegate_member_ids"%TYPE;
jbe@339 656 "weight_v" INT4;
jbe@339 657 "sub_weight_v" INT4;
jbe@339 658 BEGIN
jbe@339 659 PERFORM "require_transaction_isolation"();
jbe@339 660 "weight_v" := 0;
jbe@339 661 FOR "issue_delegation_row" IN
jbe@339 662 SELECT * FROM "issue_delegation"
jbe@339 663 WHERE "trustee_id" = "member_id_p"
jbe@339 664 AND "issue_id" = "issue_id_p"
jbe@339 665 LOOP
jbe@339 666 IF NOT EXISTS (
jbe@339 667 SELECT NULL FROM "direct_interest_snapshot"
jbe@339 668 WHERE "issue_id" = "issue_id_p"
jbe@339 669 AND "event" = 'periodic'
jbe@339 670 AND "member_id" = "issue_delegation_row"."truster_id"
jbe@339 671 ) AND NOT EXISTS (
jbe@339 672 SELECT NULL FROM "delegating_interest_snapshot"
jbe@339 673 WHERE "issue_id" = "issue_id_p"
jbe@339 674 AND "event" = 'periodic'
jbe@339 675 AND "member_id" = "issue_delegation_row"."truster_id"
jbe@339 676 ) THEN
jbe@339 677 "delegate_member_ids_v" :=
jbe@339 678 "member_id_p" || "delegate_member_ids_p";
jbe@339 679 INSERT INTO "delegating_interest_snapshot" (
jbe@339 680 "issue_id",
jbe@339 681 "event",
jbe@339 682 "member_id",
jbe@339 683 "scope",
jbe@339 684 "delegate_member_ids"
jbe@339 685 ) VALUES (
jbe@339 686 "issue_id_p",
jbe@339 687 'periodic',
jbe@339 688 "issue_delegation_row"."truster_id",
jbe@339 689 "issue_delegation_row"."scope",
jbe@339 690 "delegate_member_ids_v"
jbe@339 691 );
jbe@339 692 "sub_weight_v" := 1 +
jbe@339 693 "weight_of_added_delegations_for_interest_snapshot"(
jbe@339 694 "issue_id_p",
jbe@339 695 "issue_delegation_row"."truster_id",
jbe@339 696 "delegate_member_ids_v"
jbe@339 697 );
jbe@339 698 UPDATE "delegating_interest_snapshot"
jbe@339 699 SET "weight" = "sub_weight_v"
jbe@339 700 WHERE "issue_id" = "issue_id_p"
jbe@339 701 AND "event" = 'periodic'
jbe@339 702 AND "member_id" = "issue_delegation_row"."truster_id";
jbe@339 703 "weight_v" := "weight_v" + "sub_weight_v";
jbe@339 704 END IF;
jbe@339 705 END LOOP;
jbe@339 706 RETURN "weight_v";
jbe@339 707 END;
jbe@339 708 $$;
jbe@339 709
jbe@339 710 CREATE OR REPLACE FUNCTION "create_interest_snapshot"
jbe@339 711 ( "issue_id_p" "issue"."id"%TYPE )
jbe@339 712 RETURNS VOID
jbe@339 713 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@339 714 DECLARE
jbe@339 715 "member_id_v" "member"."id"%TYPE;
jbe@339 716 BEGIN
jbe@339 717 PERFORM "require_transaction_isolation"();
jbe@339 718 DELETE FROM "direct_interest_snapshot"
jbe@339 719 WHERE "issue_id" = "issue_id_p"
jbe@339 720 AND "event" = 'periodic';
jbe@339 721 DELETE FROM "delegating_interest_snapshot"
jbe@339 722 WHERE "issue_id" = "issue_id_p"
jbe@339 723 AND "event" = 'periodic';
jbe@339 724 DELETE FROM "direct_supporter_snapshot"
jbe@339 725 USING "initiative" -- NOTE: due to missing index on issue_id
jbe@339 726 WHERE "initiative"."issue_id" = "issue_id_p"
jbe@339 727 AND "direct_supporter_snapshot"."initiative_id" = "initiative"."id"
jbe@339 728 AND "direct_supporter_snapshot"."event" = 'periodic';
jbe@339 729 INSERT INTO "direct_interest_snapshot"
jbe@339 730 ("issue_id", "event", "member_id")
jbe@339 731 SELECT
jbe@339 732 "issue_id_p" AS "issue_id",
jbe@339 733 'periodic' AS "event",
jbe@339 734 "member"."id" AS "member_id"
jbe@339 735 FROM "issue"
jbe@339 736 JOIN "area" ON "issue"."area_id" = "area"."id"
jbe@339 737 JOIN "interest" ON "issue"."id" = "interest"."issue_id"
jbe@339 738 JOIN "member" ON "interest"."member_id" = "member"."id"
jbe@339 739 JOIN "privilege"
jbe@339 740 ON "privilege"."unit_id" = "area"."unit_id"
jbe@339 741 AND "privilege"."member_id" = "member"."id"
jbe@339 742 WHERE "issue"."id" = "issue_id_p"
jbe@339 743 AND "member"."active" AND "privilege"."voting_right";
jbe@339 744 FOR "member_id_v" IN
jbe@339 745 SELECT "member_id" FROM "direct_interest_snapshot"
jbe@339 746 WHERE "issue_id" = "issue_id_p"
jbe@339 747 AND "event" = 'periodic'
jbe@339 748 LOOP
jbe@339 749 UPDATE "direct_interest_snapshot" SET
jbe@339 750 "weight" = 1 +
jbe@339 751 "weight_of_added_delegations_for_interest_snapshot"(
jbe@339 752 "issue_id_p",
jbe@339 753 "member_id_v",
jbe@339 754 '{}'
jbe@339 755 )
jbe@339 756 WHERE "issue_id" = "issue_id_p"
jbe@339 757 AND "event" = 'periodic'
jbe@339 758 AND "member_id" = "member_id_v";
jbe@339 759 END LOOP;
jbe@339 760 INSERT INTO "direct_supporter_snapshot"
jbe@339 761 ( "issue_id", "initiative_id", "event", "member_id",
jbe@339 762 "draft_id", "informed", "satisfied" )
jbe@339 763 SELECT
jbe@339 764 "issue_id_p" AS "issue_id",
jbe@339 765 "initiative"."id" AS "initiative_id",
jbe@339 766 'periodic' AS "event",
jbe@339 767 "supporter"."member_id" AS "member_id",
jbe@339 768 "supporter"."draft_id" AS "draft_id",
jbe@339 769 "supporter"."draft_id" = "current_draft"."id" AS "informed",
jbe@339 770 NOT EXISTS (
jbe@339 771 SELECT NULL FROM "critical_opinion"
jbe@339 772 WHERE "initiative_id" = "initiative"."id"
jbe@339 773 AND "member_id" = "supporter"."member_id"
jbe@339 774 ) AS "satisfied"
jbe@339 775 FROM "initiative"
jbe@339 776 JOIN "supporter"
jbe@339 777 ON "supporter"."initiative_id" = "initiative"."id"
jbe@339 778 JOIN "current_draft"
jbe@339 779 ON "initiative"."id" = "current_draft"."initiative_id"
jbe@339 780 JOIN "direct_interest_snapshot"
jbe@339 781 ON "supporter"."member_id" = "direct_interest_snapshot"."member_id"
jbe@339 782 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"
jbe@339 783 AND "event" = 'periodic'
jbe@339 784 WHERE "initiative"."issue_id" = "issue_id_p";
jbe@339 785 RETURN;
jbe@339 786 END;
jbe@339 787 $$;
jbe@339 788
jbe@339 789 CREATE OR REPLACE FUNCTION "create_snapshot"
jbe@339 790 ( "issue_id_p" "issue"."id"%TYPE )
jbe@339 791 RETURNS VOID
jbe@339 792 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@339 793 DECLARE
jbe@339 794 "initiative_id_v" "initiative"."id"%TYPE;
jbe@339 795 "suggestion_id_v" "suggestion"."id"%TYPE;
jbe@339 796 BEGIN
jbe@339 797 PERFORM "require_transaction_isolation"();
jbe@339 798 PERFORM "create_population_snapshot"("issue_id_p");
jbe@339 799 PERFORM "create_interest_snapshot"("issue_id_p");
jbe@339 800 UPDATE "issue" SET
jbe@339 801 "snapshot" = coalesce("phase_finished", now()),
jbe@339 802 "latest_snapshot_event" = 'periodic',
jbe@339 803 "population" = (
jbe@339 804 SELECT coalesce(sum("weight"), 0)
jbe@339 805 FROM "direct_population_snapshot"
jbe@339 806 WHERE "issue_id" = "issue_id_p"
jbe@339 807 AND "event" = 'periodic'
jbe@339 808 )
jbe@339 809 WHERE "id" = "issue_id_p";
jbe@339 810 FOR "initiative_id_v" IN
jbe@339 811 SELECT "id" FROM "initiative" WHERE "issue_id" = "issue_id_p"
jbe@339 812 LOOP
jbe@339 813 UPDATE "initiative" SET
jbe@339 814 "supporter_count" = (
jbe@339 815 SELECT coalesce(sum("di"."weight"), 0)
jbe@339 816 FROM "direct_interest_snapshot" AS "di"
jbe@339 817 JOIN "direct_supporter_snapshot" AS "ds"
jbe@339 818 ON "di"."member_id" = "ds"."member_id"
jbe@339 819 WHERE "di"."issue_id" = "issue_id_p"
jbe@339 820 AND "di"."event" = 'periodic'
jbe@339 821 AND "ds"."initiative_id" = "initiative_id_v"
jbe@339 822 AND "ds"."event" = 'periodic'
jbe@339 823 ),
jbe@339 824 "informed_supporter_count" = (
jbe@339 825 SELECT coalesce(sum("di"."weight"), 0)
jbe@339 826 FROM "direct_interest_snapshot" AS "di"
jbe@339 827 JOIN "direct_supporter_snapshot" AS "ds"
jbe@339 828 ON "di"."member_id" = "ds"."member_id"
jbe@339 829 WHERE "di"."issue_id" = "issue_id_p"
jbe@339 830 AND "di"."event" = 'periodic'
jbe@339 831 AND "ds"."initiative_id" = "initiative_id_v"
jbe@339 832 AND "ds"."event" = 'periodic'
jbe@339 833 AND "ds"."informed"
jbe@339 834 ),
jbe@339 835 "satisfied_supporter_count" = (
jbe@339 836 SELECT coalesce(sum("di"."weight"), 0)
jbe@339 837 FROM "direct_interest_snapshot" AS "di"
jbe@339 838 JOIN "direct_supporter_snapshot" AS "ds"
jbe@339 839 ON "di"."member_id" = "ds"."member_id"
jbe@339 840 WHERE "di"."issue_id" = "issue_id_p"
jbe@339 841 AND "di"."event" = 'periodic'
jbe@339 842 AND "ds"."initiative_id" = "initiative_id_v"
jbe@339 843 AND "ds"."event" = 'periodic'
jbe@339 844 AND "ds"."satisfied"
jbe@339 845 ),
jbe@339 846 "satisfied_informed_supporter_count" = (
jbe@339 847 SELECT coalesce(sum("di"."weight"), 0)
jbe@339 848 FROM "direct_interest_snapshot" AS "di"
jbe@339 849 JOIN "direct_supporter_snapshot" AS "ds"
jbe@339 850 ON "di"."member_id" = "ds"."member_id"
jbe@339 851 WHERE "di"."issue_id" = "issue_id_p"
jbe@339 852 AND "di"."event" = 'periodic'
jbe@339 853 AND "ds"."initiative_id" = "initiative_id_v"
jbe@339 854 AND "ds"."event" = 'periodic'
jbe@339 855 AND "ds"."informed"
jbe@339 856 AND "ds"."satisfied"
jbe@339 857 )
jbe@339 858 WHERE "id" = "initiative_id_v";
jbe@339 859 FOR "suggestion_id_v" IN
jbe@339 860 SELECT "id" FROM "suggestion"
jbe@339 861 WHERE "initiative_id" = "initiative_id_v"
jbe@339 862 LOOP
jbe@339 863 UPDATE "suggestion" SET
jbe@339 864 "minus2_unfulfilled_count" = (
jbe@339 865 SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@339 866 FROM "issue" CROSS JOIN "opinion"
jbe@339 867 JOIN "direct_interest_snapshot" AS "snapshot"
jbe@339 868 ON "snapshot"."issue_id" = "issue"."id"
jbe@339 869 AND "snapshot"."event" = "issue"."latest_snapshot_event"
jbe@339 870 AND "snapshot"."member_id" = "opinion"."member_id"
jbe@339 871 WHERE "issue"."id" = "issue_id_p"
jbe@339 872 AND "opinion"."suggestion_id" = "suggestion_id_v"
jbe@339 873 AND "opinion"."degree" = -2
jbe@339 874 AND "opinion"."fulfilled" = FALSE
jbe@339 875 ),
jbe@339 876 "minus2_fulfilled_count" = (
jbe@339 877 SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@339 878 FROM "issue" CROSS JOIN "opinion"
jbe@339 879 JOIN "direct_interest_snapshot" AS "snapshot"
jbe@339 880 ON "snapshot"."issue_id" = "issue"."id"
jbe@339 881 AND "snapshot"."event" = "issue"."latest_snapshot_event"
jbe@339 882 AND "snapshot"."member_id" = "opinion"."member_id"
jbe@339 883 WHERE "issue"."id" = "issue_id_p"
jbe@339 884 AND "opinion"."suggestion_id" = "suggestion_id_v"
jbe@339 885 AND "opinion"."degree" = -2
jbe@339 886 AND "opinion"."fulfilled" = TRUE
jbe@339 887 ),
jbe@339 888 "minus1_unfulfilled_count" = (
jbe@339 889 SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@339 890 FROM "issue" CROSS JOIN "opinion"
jbe@339 891 JOIN "direct_interest_snapshot" AS "snapshot"
jbe@339 892 ON "snapshot"."issue_id" = "issue"."id"
jbe@339 893 AND "snapshot"."event" = "issue"."latest_snapshot_event"
jbe@339 894 AND "snapshot"."member_id" = "opinion"."member_id"
jbe@339 895 WHERE "issue"."id" = "issue_id_p"
jbe@339 896 AND "opinion"."suggestion_id" = "suggestion_id_v"
jbe@339 897 AND "opinion"."degree" = -1
jbe@339 898 AND "opinion"."fulfilled" = FALSE
jbe@339 899 ),
jbe@339 900 "minus1_fulfilled_count" = (
jbe@339 901 SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@339 902 FROM "issue" CROSS JOIN "opinion"
jbe@339 903 JOIN "direct_interest_snapshot" AS "snapshot"
jbe@339 904 ON "snapshot"."issue_id" = "issue"."id"
jbe@339 905 AND "snapshot"."event" = "issue"."latest_snapshot_event"
jbe@339 906 AND "snapshot"."member_id" = "opinion"."member_id"
jbe@339 907 WHERE "issue"."id" = "issue_id_p"
jbe@339 908 AND "opinion"."suggestion_id" = "suggestion_id_v"
jbe@339 909 AND "opinion"."degree" = -1
jbe@339 910 AND "opinion"."fulfilled" = TRUE
jbe@339 911 ),
jbe@339 912 "plus1_unfulfilled_count" = (
jbe@339 913 SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@339 914 FROM "issue" CROSS JOIN "opinion"
jbe@339 915 JOIN "direct_interest_snapshot" AS "snapshot"
jbe@339 916 ON "snapshot"."issue_id" = "issue"."id"
jbe@339 917 AND "snapshot"."event" = "issue"."latest_snapshot_event"
jbe@339 918 AND "snapshot"."member_id" = "opinion"."member_id"
jbe@339 919 WHERE "issue"."id" = "issue_id_p"
jbe@339 920 AND "opinion"."suggestion_id" = "suggestion_id_v"
jbe@339 921 AND "opinion"."degree" = 1
jbe@339 922 AND "opinion"."fulfilled" = FALSE
jbe@339 923 ),
jbe@339 924 "plus1_fulfilled_count" = (
jbe@339 925 SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@339 926 FROM "issue" CROSS JOIN "opinion"
jbe@339 927 JOIN "direct_interest_snapshot" AS "snapshot"
jbe@339 928 ON "snapshot"."issue_id" = "issue"."id"
jbe@339 929 AND "snapshot"."event" = "issue"."latest_snapshot_event"
jbe@339 930 AND "snapshot"."member_id" = "opinion"."member_id"
jbe@339 931 WHERE "issue"."id" = "issue_id_p"
jbe@339 932 AND "opinion"."suggestion_id" = "suggestion_id_v"
jbe@339 933 AND "opinion"."degree" = 1
jbe@339 934 AND "opinion"."fulfilled" = TRUE
jbe@339 935 ),
jbe@339 936 "plus2_unfulfilled_count" = (
jbe@339 937 SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@339 938 FROM "issue" CROSS JOIN "opinion"
jbe@339 939 JOIN "direct_interest_snapshot" AS "snapshot"
jbe@339 940 ON "snapshot"."issue_id" = "issue"."id"
jbe@339 941 AND "snapshot"."event" = "issue"."latest_snapshot_event"
jbe@339 942 AND "snapshot"."member_id" = "opinion"."member_id"
jbe@339 943 WHERE "issue"."id" = "issue_id_p"
jbe@339 944 AND "opinion"."suggestion_id" = "suggestion_id_v"
jbe@339 945 AND "opinion"."degree" = 2
jbe@339 946 AND "opinion"."fulfilled" = FALSE
jbe@339 947 ),
jbe@339 948 "plus2_fulfilled_count" = (
jbe@339 949 SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@339 950 FROM "issue" CROSS JOIN "opinion"
jbe@339 951 JOIN "direct_interest_snapshot" AS "snapshot"
jbe@339 952 ON "snapshot"."issue_id" = "issue"."id"
jbe@339 953 AND "snapshot"."event" = "issue"."latest_snapshot_event"
jbe@339 954 AND "snapshot"."member_id" = "opinion"."member_id"
jbe@339 955 WHERE "issue"."id" = "issue_id_p"
jbe@339 956 AND "opinion"."suggestion_id" = "suggestion_id_v"
jbe@339 957 AND "opinion"."degree" = 2
jbe@339 958 AND "opinion"."fulfilled" = TRUE
jbe@339 959 )
jbe@339 960 WHERE "suggestion"."id" = "suggestion_id_v";
jbe@339 961 END LOOP;
jbe@339 962 END LOOP;
jbe@339 963 RETURN;
jbe@339 964 END;
jbe@339 965 $$;
jbe@339 966
jbe@339 967 CREATE OR REPLACE FUNCTION "set_snapshot_event"
jbe@339 968 ( "issue_id_p" "issue"."id"%TYPE,
jbe@339 969 "event_p" "snapshot_event" )
jbe@339 970 RETURNS VOID
jbe@339 971 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@339 972 DECLARE
jbe@339 973 "event_v" "issue"."latest_snapshot_event"%TYPE;
jbe@339 974 BEGIN
jbe@339 975 PERFORM "require_transaction_isolation"();
jbe@339 976 SELECT "latest_snapshot_event" INTO "event_v" FROM "issue"
jbe@339 977 WHERE "id" = "issue_id_p" FOR UPDATE;
jbe@339 978 UPDATE "issue" SET "latest_snapshot_event" = "event_p"
jbe@339 979 WHERE "id" = "issue_id_p";
jbe@339 980 UPDATE "direct_population_snapshot" SET "event" = "event_p"
jbe@339 981 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
jbe@339 982 UPDATE "delegating_population_snapshot" SET "event" = "event_p"
jbe@339 983 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
jbe@339 984 UPDATE "direct_interest_snapshot" SET "event" = "event_p"
jbe@339 985 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
jbe@339 986 UPDATE "delegating_interest_snapshot" SET "event" = "event_p"
jbe@339 987 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
jbe@339 988 UPDATE "direct_supporter_snapshot" SET "event" = "event_p"
jbe@339 989 FROM "initiative" -- NOTE: due to missing index on issue_id
jbe@339 990 WHERE "initiative"."issue_id" = "issue_id_p"
jbe@339 991 AND "direct_supporter_snapshot"."initiative_id" = "initiative"."id"
jbe@339 992 AND "direct_supporter_snapshot"."event" = "event_v";
jbe@339 993 RETURN;
jbe@339 994 END;
jbe@339 995 $$;
jbe@339 996
jbe@339 997 DROP FUNCTION "freeze_after_snapshot"("issue"."id"%TYPE);
jbe@339 998 DROP FUNCTION "manual_freeze"("issue"."id"%TYPE);
jbe@339 999
jbe@339 1000 CREATE OR REPLACE FUNCTION "weight_of_added_vote_delegations"
jbe@339 1001 ( "issue_id_p" "issue"."id"%TYPE,
jbe@339 1002 "member_id_p" "member"."id"%TYPE,
jbe@339 1003 "delegate_member_ids_p" "delegating_voter"."delegate_member_ids"%TYPE )
jbe@339 1004 RETURNS "direct_voter"."weight"%TYPE
jbe@339 1005 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@339 1006 DECLARE
jbe@339 1007 "issue_delegation_row" "issue_delegation"%ROWTYPE;
jbe@339 1008 "delegate_member_ids_v" "delegating_voter"."delegate_member_ids"%TYPE;
jbe@339 1009 "weight_v" INT4;
jbe@339 1010 "sub_weight_v" INT4;
jbe@339 1011 BEGIN
jbe@339 1012 PERFORM "require_transaction_isolation"();
jbe@339 1013 "weight_v" := 0;
jbe@339 1014 FOR "issue_delegation_row" IN
jbe@339 1015 SELECT * FROM "issue_delegation"
jbe@339 1016 WHERE "trustee_id" = "member_id_p"
jbe@339 1017 AND "issue_id" = "issue_id_p"
jbe@339 1018 LOOP
jbe@339 1019 IF NOT EXISTS (
jbe@339 1020 SELECT NULL FROM "direct_voter"
jbe@339 1021 WHERE "member_id" = "issue_delegation_row"."truster_id"
jbe@339 1022 AND "issue_id" = "issue_id_p"
jbe@339 1023 ) AND NOT EXISTS (
jbe@339 1024 SELECT NULL FROM "delegating_voter"
jbe@339 1025 WHERE "member_id" = "issue_delegation_row"."truster_id"
jbe@339 1026 AND "issue_id" = "issue_id_p"
jbe@339 1027 ) THEN
jbe@339 1028 "delegate_member_ids_v" :=
jbe@339 1029 "member_id_p" || "delegate_member_ids_p";
jbe@339 1030 INSERT INTO "delegating_voter" (
jbe@339 1031 "issue_id",
jbe@339 1032 "member_id",
jbe@339 1033 "scope",
jbe@339 1034 "delegate_member_ids"
jbe@339 1035 ) VALUES (
jbe@339 1036 "issue_id_p",
jbe@339 1037 "issue_delegation_row"."truster_id",
jbe@339 1038 "issue_delegation_row"."scope",
jbe@339 1039 "delegate_member_ids_v"
jbe@339 1040 );
jbe@339 1041 "sub_weight_v" := 1 +
jbe@339 1042 "weight_of_added_vote_delegations"(
jbe@339 1043 "issue_id_p",
jbe@339 1044 "issue_delegation_row"."truster_id",
jbe@339 1045 "delegate_member_ids_v"
jbe@339 1046 );
jbe@339 1047 UPDATE "delegating_voter"
jbe@339 1048 SET "weight" = "sub_weight_v"
jbe@339 1049 WHERE "issue_id" = "issue_id_p"
jbe@339 1050 AND "member_id" = "issue_delegation_row"."truster_id";
jbe@339 1051 "weight_v" := "weight_v" + "sub_weight_v";
jbe@339 1052 END IF;
jbe@339 1053 END LOOP;
jbe@339 1054 RETURN "weight_v";
jbe@339 1055 END;
jbe@339 1056 $$;
jbe@339 1057
jbe@339 1058 CREATE OR REPLACE FUNCTION "add_vote_delegations"
jbe@339 1059 ( "issue_id_p" "issue"."id"%TYPE )
jbe@339 1060 RETURNS VOID
jbe@339 1061 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@339 1062 DECLARE
jbe@339 1063 "member_id_v" "member"."id"%TYPE;
jbe@339 1064 BEGIN
jbe@339 1065 PERFORM "require_transaction_isolation"();
jbe@339 1066 FOR "member_id_v" IN
jbe@339 1067 SELECT "member_id" FROM "direct_voter"
jbe@339 1068 WHERE "issue_id" = "issue_id_p"
jbe@339 1069 LOOP
jbe@339 1070 UPDATE "direct_voter" SET
jbe@339 1071 "weight" = "weight" + "weight_of_added_vote_delegations"(
jbe@339 1072 "issue_id_p",
jbe@339 1073 "member_id_v",
jbe@339 1074 '{}'
jbe@339 1075 )
jbe@339 1076 WHERE "member_id" = "member_id_v"
jbe@339 1077 AND "issue_id" = "issue_id_p";
jbe@339 1078 END LOOP;
jbe@339 1079 RETURN;
jbe@339 1080 END;
jbe@339 1081 $$;
jbe@339 1082
jbe@339 1083 CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
jbe@339 1084 RETURNS VOID
jbe@339 1085 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@339 1086 DECLARE
jbe@339 1087 "area_id_v" "area"."id"%TYPE;
jbe@339 1088 "unit_id_v" "unit"."id"%TYPE;
jbe@339 1089 "member_id_v" "member"."id"%TYPE;
jbe@339 1090 BEGIN
jbe@339 1091 PERFORM "require_transaction_isolation"();
jbe@339 1092 SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";
jbe@339 1093 SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v";
jbe@339 1094 -- delete timestamp of voting comment:
jbe@339 1095 UPDATE "direct_voter" SET "comment_changed" = NULL
jbe@339 1096 WHERE "issue_id" = "issue_id_p";
jbe@339 1097 -- delete delegating votes (in cases of manual reset of issue state):
jbe@339 1098 DELETE FROM "delegating_voter"
jbe@339 1099 WHERE "issue_id" = "issue_id_p";
jbe@339 1100 -- delete votes from non-privileged voters:
jbe@339 1101 DELETE FROM "direct_voter"
jbe@339 1102 USING (
jbe@339 1103 SELECT
jbe@339 1104 "direct_voter"."member_id"
jbe@339 1105 FROM "direct_voter"
jbe@339 1106 JOIN "member" ON "direct_voter"."member_id" = "member"."id"
jbe@339 1107 LEFT JOIN "privilege"
jbe@339 1108 ON "privilege"."unit_id" = "unit_id_v"
jbe@339 1109 AND "privilege"."member_id" = "direct_voter"."member_id"
jbe@339 1110 WHERE "direct_voter"."issue_id" = "issue_id_p" AND (
jbe@339 1111 "member"."active" = FALSE OR
jbe@339 1112 "privilege"."voting_right" ISNULL OR
jbe@339 1113 "privilege"."voting_right" = FALSE
jbe@339 1114 )
jbe@339 1115 ) AS "subquery"
jbe@339 1116 WHERE "direct_voter"."issue_id" = "issue_id_p"
jbe@339 1117 AND "direct_voter"."member_id" = "subquery"."member_id";
jbe@339 1118 -- consider delegations:
jbe@339 1119 UPDATE "direct_voter" SET "weight" = 1
jbe@339 1120 WHERE "issue_id" = "issue_id_p";
jbe@339 1121 PERFORM "add_vote_delegations"("issue_id_p");
jbe@339 1122 -- materialize battle_view:
jbe@339 1123 -- NOTE: "closed" column of issue must be set at this point
jbe@339 1124 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
jbe@339 1125 INSERT INTO "battle" (
jbe@339 1126 "issue_id",
jbe@339 1127 "winning_initiative_id", "losing_initiative_id",
jbe@339 1128 "count"
jbe@339 1129 ) SELECT
jbe@339 1130 "issue_id",
jbe@339 1131 "winning_initiative_id", "losing_initiative_id",
jbe@339 1132 "count"
jbe@339 1133 FROM "battle_view" WHERE "issue_id" = "issue_id_p";
jbe@339 1134 -- set voter count:
jbe@339 1135 UPDATE "issue" SET
jbe@339 1136 "voter_count" = (
jbe@339 1137 SELECT coalesce(sum("weight"), 0)
jbe@339 1138 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
jbe@339 1139 )
jbe@339 1140 WHERE "id" = "issue_id_p";
jbe@339 1141 -- copy "positive_votes" and "negative_votes" from "battle" table:
jbe@339 1142 UPDATE "initiative" SET
jbe@339 1143 "positive_votes" = "battle_win"."count",
jbe@339 1144 "negative_votes" = "battle_lose"."count"
jbe@339 1145 FROM "battle" AS "battle_win", "battle" AS "battle_lose"
jbe@339 1146 WHERE
jbe@339 1147 "battle_win"."issue_id" = "issue_id_p" AND
jbe@339 1148 "battle_win"."winning_initiative_id" = "initiative"."id" AND
jbe@339 1149 "battle_win"."losing_initiative_id" ISNULL AND
jbe@339 1150 "battle_lose"."issue_id" = "issue_id_p" AND
jbe@339 1151 "battle_lose"."losing_initiative_id" = "initiative"."id" AND
jbe@339 1152 "battle_lose"."winning_initiative_id" ISNULL;
jbe@339 1153 END;
jbe@339 1154 $$;
jbe@339 1155
jbe@339 1156 CREATE OR REPLACE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE)
jbe@339 1157 RETURNS VOID
jbe@339 1158 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@339 1159 DECLARE
jbe@339 1160 "issue_row" "issue"%ROWTYPE;
jbe@339 1161 "policy_row" "policy"%ROWTYPE;
jbe@339 1162 "dimension_v" INTEGER;
jbe@339 1163 "vote_matrix" INT4[][]; -- absolute votes
jbe@339 1164 "matrix" INT8[][]; -- defeat strength / best paths
jbe@339 1165 "i" INTEGER;
jbe@339 1166 "j" INTEGER;
jbe@339 1167 "k" INTEGER;
jbe@339 1168 "battle_row" "battle"%ROWTYPE;
jbe@339 1169 "rank_ary" INT4[];
jbe@339 1170 "rank_v" INT4;
jbe@339 1171 "done_v" INTEGER;
jbe@339 1172 "winners_ary" INTEGER[];
jbe@339 1173 "initiative_id_v" "initiative"."id"%TYPE;
jbe@339 1174 BEGIN
jbe@339 1175 PERFORM "require_transaction_isolation"();
jbe@339 1176 SELECT * INTO "issue_row"
jbe@339 1177 FROM "issue" WHERE "id" = "issue_id_p";
jbe@339 1178 SELECT * INTO "policy_row"
jbe@339 1179 FROM "policy" WHERE "id" = "issue_row"."policy_id";
jbe@339 1180 SELECT count(1) INTO "dimension_v"
jbe@339 1181 FROM "battle_participant" WHERE "issue_id" = "issue_id_p";
jbe@339 1182 -- Create "vote_matrix" with absolute number of votes in pairwise
jbe@339 1183 -- comparison:
jbe@339 1184 "vote_matrix" := array_fill(NULL::INT4, ARRAY["dimension_v", "dimension_v"]);
jbe@339 1185 "i" := 1;
jbe@339 1186 "j" := 2;
jbe@339 1187 FOR "battle_row" IN
jbe@339 1188 SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p"
jbe@339 1189 ORDER BY
jbe@339 1190 "winning_initiative_id" NULLS LAST,
jbe@339 1191 "losing_initiative_id" NULLS LAST
jbe@339 1192 LOOP
jbe@339 1193 "vote_matrix"["i"]["j"] := "battle_row"."count";
jbe@339 1194 IF "j" = "dimension_v" THEN
jbe@339 1195 "i" := "i" + 1;
jbe@339 1196 "j" := 1;
jbe@339 1197 ELSE
jbe@339 1198 "j" := "j" + 1;
jbe@339 1199 IF "j" = "i" THEN
jbe@339 1200 "j" := "j" + 1;
jbe@339 1201 END IF;
jbe@339 1202 END IF;
jbe@339 1203 END LOOP;
jbe@339 1204 IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN
jbe@339 1205 RAISE EXCEPTION 'Wrong battle count (should not happen)';
jbe@339 1206 END IF;
jbe@339 1207 -- Store defeat strengths in "matrix" using "defeat_strength"
jbe@339 1208 -- function:
jbe@339 1209 "matrix" := array_fill(NULL::INT8, ARRAY["dimension_v", "dimension_v"]);
jbe@339 1210 "i" := 1;
jbe@339 1211 LOOP
jbe@339 1212 "j" := 1;
jbe@339 1213 LOOP
jbe@339 1214 IF "i" != "j" THEN
jbe@339 1215 "matrix"["i"]["j"] := "defeat_strength"(
jbe@339 1216 "vote_matrix"["i"]["j"],
jbe@339 1217 "vote_matrix"["j"]["i"]
jbe@339 1218 );
jbe@339 1219 END IF;
jbe@339 1220 EXIT WHEN "j" = "dimension_v";
jbe@339 1221 "j" := "j" + 1;
jbe@339 1222 END LOOP;
jbe@339 1223 EXIT WHEN "i" = "dimension_v";
jbe@339 1224 "i" := "i" + 1;
jbe@339 1225 END LOOP;
jbe@339 1226 -- Find best paths:
jbe@339 1227 "i" := 1;
jbe@339 1228 LOOP
jbe@339 1229 "j" := 1;
jbe@339 1230 LOOP
jbe@339 1231 IF "i" != "j" THEN
jbe@339 1232 "k" := 1;
jbe@339 1233 LOOP
jbe@339 1234 IF "i" != "k" AND "j" != "k" THEN
jbe@339 1235 IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN
jbe@339 1236 IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN
jbe@339 1237 "matrix"["j"]["k"] := "matrix"["j"]["i"];
jbe@339 1238 END IF;
jbe@339 1239 ELSE
jbe@339 1240 IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN
jbe@339 1241 "matrix"["j"]["k"] := "matrix"["i"]["k"];
jbe@339 1242 END IF;
jbe@339 1243 END IF;
jbe@339 1244 END IF;
jbe@339 1245 EXIT WHEN "k" = "dimension_v";
jbe@339 1246 "k" := "k" + 1;
jbe@339 1247 END LOOP;
jbe@339 1248 END IF;
jbe@339 1249 EXIT WHEN "j" = "dimension_v";
jbe@339 1250 "j" := "j" + 1;
jbe@339 1251 END LOOP;
jbe@339 1252 EXIT WHEN "i" = "dimension_v";
jbe@339 1253 "i" := "i" + 1;
jbe@339 1254 END LOOP;
jbe@339 1255 -- Determine order of winners:
jbe@339 1256 "rank_ary" := array_fill(NULL::INT4, ARRAY["dimension_v"]);
jbe@339 1257 "rank_v" := 1;
jbe@339 1258 "done_v" := 0;
jbe@339 1259 LOOP
jbe@339 1260 "winners_ary" := '{}';
jbe@339 1261 "i" := 1;
jbe@339 1262 LOOP
jbe@339 1263 IF "rank_ary"["i"] ISNULL THEN
jbe@339 1264 "j" := 1;
jbe@339 1265 LOOP
jbe@339 1266 IF
jbe@339 1267 "i" != "j" AND
jbe@339 1268 "rank_ary"["j"] ISNULL AND
jbe@339 1269 "matrix"["j"]["i"] > "matrix"["i"]["j"]
jbe@339 1270 THEN
jbe@339 1271 -- someone else is better
jbe@339 1272 EXIT;
jbe@339 1273 END IF;
jbe@339 1274 IF "j" = "dimension_v" THEN
jbe@339 1275 -- noone is better
jbe@339 1276 "winners_ary" := "winners_ary" || "i";
jbe@339 1277 EXIT;
jbe@339 1278 END IF;
jbe@339 1279 "j" := "j" + 1;
jbe@339 1280 END LOOP;
jbe@339 1281 END IF;
jbe@339 1282 EXIT WHEN "i" = "dimension_v";
jbe@339 1283 "i" := "i" + 1;
jbe@339 1284 END LOOP;
jbe@339 1285 "i" := 1;
jbe@339 1286 LOOP
jbe@339 1287 "rank_ary"["winners_ary"["i"]] := "rank_v";
jbe@339 1288 "done_v" := "done_v" + 1;
jbe@339 1289 EXIT WHEN "i" = array_upper("winners_ary", 1);
jbe@339 1290 "i" := "i" + 1;
jbe@339 1291 END LOOP;
jbe@339 1292 EXIT WHEN "done_v" = "dimension_v";
jbe@339 1293 "rank_v" := "rank_v" + 1;
jbe@339 1294 END LOOP;
jbe@339 1295 -- write preliminary results:
jbe@339 1296 "i" := 1;
jbe@339 1297 FOR "initiative_id_v" IN
jbe@339 1298 SELECT "id" FROM "initiative"
jbe@339 1299 WHERE "issue_id" = "issue_id_p" AND "admitted"
jbe@339 1300 ORDER BY "id"
jbe@339 1301 LOOP
jbe@339 1302 UPDATE "initiative" SET
jbe@339 1303 "direct_majority" =
jbe@339 1304 CASE WHEN "policy_row"."direct_majority_strict" THEN
jbe@339 1305 "positive_votes" * "policy_row"."direct_majority_den" >
jbe@339 1306 "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")
jbe@339 1307 ELSE
jbe@339 1308 "positive_votes" * "policy_row"."direct_majority_den" >=
jbe@339 1309 "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")
jbe@339 1310 END
jbe@339 1311 AND "positive_votes" >= "policy_row"."direct_majority_positive"
jbe@339 1312 AND "issue_row"."voter_count"-"negative_votes" >=
jbe@339 1313 "policy_row"."direct_majority_non_negative",
jbe@339 1314 "indirect_majority" =
jbe@339 1315 CASE WHEN "policy_row"."indirect_majority_strict" THEN
jbe@339 1316 "positive_votes" * "policy_row"."indirect_majority_den" >
jbe@339 1317 "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")
jbe@339 1318 ELSE
jbe@339 1319 "positive_votes" * "policy_row"."indirect_majority_den" >=
jbe@339 1320 "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")
jbe@339 1321 END
jbe@339 1322 AND "positive_votes" >= "policy_row"."indirect_majority_positive"
jbe@339 1323 AND "issue_row"."voter_count"-"negative_votes" >=
jbe@339 1324 "policy_row"."indirect_majority_non_negative",
jbe@339 1325 "schulze_rank" = "rank_ary"["i"],
jbe@339 1326 "better_than_status_quo" = "rank_ary"["i"] < "rank_ary"["dimension_v"],
jbe@339 1327 "worse_than_status_quo" = "rank_ary"["i"] > "rank_ary"["dimension_v"],
jbe@339 1328 "multistage_majority" = "rank_ary"["i"] >= "rank_ary"["dimension_v"],
jbe@339 1329 "reverse_beat_path" = "matrix"["dimension_v"]["i"] >= 0,
jbe@339 1330 "eligible" = FALSE,
jbe@339 1331 "winner" = FALSE,
jbe@339 1332 "rank" = NULL -- NOTE: in cases of manual reset of issue state
jbe@339 1333 WHERE "id" = "initiative_id_v";
jbe@339 1334 "i" := "i" + 1;
jbe@339 1335 END LOOP;
jbe@339 1336 IF "i" != "dimension_v" THEN
jbe@339 1337 RAISE EXCEPTION 'Wrong winner count (should not happen)';
jbe@339 1338 END IF;
jbe@339 1339 -- take indirect majorities into account:
jbe@339 1340 LOOP
jbe@339 1341 UPDATE "initiative" SET "indirect_majority" = TRUE
jbe@339 1342 FROM (
jbe@339 1343 SELECT "new_initiative"."id" AS "initiative_id"
jbe@339 1344 FROM "initiative" "old_initiative"
jbe@339 1345 JOIN "initiative" "new_initiative"
jbe@339 1346 ON "new_initiative"."issue_id" = "issue_id_p"
jbe@339 1347 AND "new_initiative"."indirect_majority" = FALSE
jbe@339 1348 JOIN "battle" "battle_win"
jbe@339 1349 ON "battle_win"."issue_id" = "issue_id_p"
jbe@339 1350 AND "battle_win"."winning_initiative_id" = "new_initiative"."id"
jbe@339 1351 AND "battle_win"."losing_initiative_id" = "old_initiative"."id"
jbe@339 1352 JOIN "battle" "battle_lose"
jbe@339 1353 ON "battle_lose"."issue_id" = "issue_id_p"
jbe@339 1354 AND "battle_lose"."losing_initiative_id" = "new_initiative"."id"
jbe@339 1355 AND "battle_lose"."winning_initiative_id" = "old_initiative"."id"
jbe@339 1356 WHERE "old_initiative"."issue_id" = "issue_id_p"
jbe@339 1357 AND "old_initiative"."indirect_majority" = TRUE
jbe@339 1358 AND CASE WHEN "policy_row"."indirect_majority_strict" THEN
jbe@339 1359 "battle_win"."count" * "policy_row"."indirect_majority_den" >
jbe@339 1360 "policy_row"."indirect_majority_num" *
jbe@339 1361 ("battle_win"."count"+"battle_lose"."count")
jbe@339 1362 ELSE
jbe@339 1363 "battle_win"."count" * "policy_row"."indirect_majority_den" >=
jbe@339 1364 "policy_row"."indirect_majority_num" *
jbe@339 1365 ("battle_win"."count"+"battle_lose"."count")
jbe@339 1366 END
jbe@339 1367 AND "battle_win"."count" >= "policy_row"."indirect_majority_positive"
jbe@339 1368 AND "issue_row"."voter_count"-"battle_lose"."count" >=
jbe@339 1369 "policy_row"."indirect_majority_non_negative"
jbe@339 1370 ) AS "subquery"
jbe@339 1371 WHERE "id" = "subquery"."initiative_id";
jbe@339 1372 EXIT WHEN NOT FOUND;
jbe@339 1373 END LOOP;
jbe@339 1374 -- set "multistage_majority" for remaining matching initiatives:
jbe@339 1375 UPDATE "initiative" SET "multistage_majority" = TRUE
jbe@339 1376 FROM (
jbe@339 1377 SELECT "losing_initiative"."id" AS "initiative_id"
jbe@339 1378 FROM "initiative" "losing_initiative"
jbe@339 1379 JOIN "initiative" "winning_initiative"
jbe@339 1380 ON "winning_initiative"."issue_id" = "issue_id_p"
jbe@339 1381 AND "winning_initiative"."admitted"
jbe@339 1382 JOIN "battle" "battle_win"
jbe@339 1383 ON "battle_win"."issue_id" = "issue_id_p"
jbe@339 1384 AND "battle_win"."winning_initiative_id" = "winning_initiative"."id"
jbe@339 1385 AND "battle_win"."losing_initiative_id" = "losing_initiative"."id"
jbe@339 1386 JOIN "battle" "battle_lose"
jbe@339 1387 ON "battle_lose"."issue_id" = "issue_id_p"
jbe@339 1388 AND "battle_lose"."losing_initiative_id" = "winning_initiative"."id"
jbe@339 1389 AND "battle_lose"."winning_initiative_id" = "losing_initiative"."id"
jbe@339 1390 WHERE "losing_initiative"."issue_id" = "issue_id_p"
jbe@339 1391 AND "losing_initiative"."admitted"
jbe@339 1392 AND "winning_initiative"."schulze_rank" <
jbe@339 1393 "losing_initiative"."schulze_rank"
jbe@339 1394 AND "battle_win"."count" > "battle_lose"."count"
jbe@339 1395 AND (
jbe@339 1396 "battle_win"."count" > "winning_initiative"."positive_votes" OR
jbe@339 1397 "battle_lose"."count" < "losing_initiative"."negative_votes" )
jbe@339 1398 ) AS "subquery"
jbe@339 1399 WHERE "id" = "subquery"."initiative_id";
jbe@339 1400 -- mark eligible initiatives:
jbe@339 1401 UPDATE "initiative" SET "eligible" = TRUE
jbe@339 1402 WHERE "issue_id" = "issue_id_p"
jbe@339 1403 AND "initiative"."direct_majority"
jbe@339 1404 AND "initiative"."indirect_majority"
jbe@339 1405 AND "initiative"."better_than_status_quo"
jbe@339 1406 AND (
jbe@339 1407 "policy_row"."no_multistage_majority" = FALSE OR
jbe@339 1408 "initiative"."multistage_majority" = FALSE )
jbe@339 1409 AND (
jbe@339 1410 "policy_row"."no_reverse_beat_path" = FALSE OR
jbe@339 1411 "initiative"."reverse_beat_path" = FALSE );
jbe@339 1412 -- mark final winner:
jbe@339 1413 UPDATE "initiative" SET "winner" = TRUE
jbe@339 1414 FROM (
jbe@339 1415 SELECT "id" AS "initiative_id"
jbe@339 1416 FROM "initiative"
jbe@339 1417 WHERE "issue_id" = "issue_id_p" AND "eligible"
jbe@339 1418 ORDER BY
jbe@339 1419 "schulze_rank",
jbe@339 1420 "id"
jbe@339 1421 LIMIT 1
jbe@339 1422 ) AS "subquery"
jbe@339 1423 WHERE "id" = "subquery"."initiative_id";
jbe@339 1424 -- write (final) ranks:
jbe@339 1425 "rank_v" := 1;
jbe@339 1426 FOR "initiative_id_v" IN
jbe@339 1427 SELECT "id"
jbe@339 1428 FROM "initiative"
jbe@339 1429 WHERE "issue_id" = "issue_id_p" AND "admitted"
jbe@339 1430 ORDER BY
jbe@339 1431 "winner" DESC,
jbe@339 1432 "eligible" DESC,
jbe@339 1433 "schulze_rank",
jbe@339 1434 "id"
jbe@339 1435 LOOP
jbe@339 1436 UPDATE "initiative" SET "rank" = "rank_v"
jbe@339 1437 WHERE "id" = "initiative_id_v";
jbe@339 1438 "rank_v" := "rank_v" + 1;
jbe@339 1439 END LOOP;
jbe@339 1440 -- set schulze rank of status quo and mark issue as finished:
jbe@339 1441 UPDATE "issue" SET
jbe@339 1442 "status_quo_schulze_rank" = "rank_ary"["dimension_v"],
jbe@339 1443 "state" =
jbe@339 1444 CASE WHEN EXISTS (
jbe@339 1445 SELECT NULL FROM "initiative"
jbe@339 1446 WHERE "issue_id" = "issue_id_p" AND "winner"
jbe@339 1447 ) THEN
jbe@339 1448 'finished_with_winner'::"issue_state"
jbe@339 1449 ELSE
jbe@339 1450 'finished_without_winner'::"issue_state"
jbe@339 1451 END,
jbe@339 1452 "closed" = "phase_finished",
jbe@339 1453 "phase_finished" = NULL
jbe@339 1454 WHERE "id" = "issue_id_p";
jbe@339 1455 RETURN;
jbe@339 1456 END;
jbe@339 1457 $$;
jbe@339 1458
jbe@339 1459 DROP FUNCTION "check_issue"("issue"."id"%TYPE);
jbe@339 1460
jbe@339 1461 CREATE TYPE "check_issue_persistence" AS (
jbe@339 1462 "state" "issue_state",
jbe@339 1463 "phase_finished" BOOLEAN,
jbe@339 1464 "issue_revoked" BOOLEAN,
jbe@339 1465 "snapshot_created" BOOLEAN,
jbe@339 1466 "harmonic_weights_set" BOOLEAN,
jbe@339 1467 "closed_voting" BOOLEAN );
jbe@339 1468 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 1469
jbe@339 1470 CREATE FUNCTION "check_issue"
jbe@339 1471 ( "issue_id_p" "issue"."id"%TYPE,
jbe@339 1472 "persist" "check_issue_persistence" )
jbe@339 1473 RETURNS "check_issue_persistence"
jbe@339 1474 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@339 1475 DECLARE
jbe@339 1476 "issue_row" "issue"%ROWTYPE;
jbe@339 1477 "policy_row" "policy"%ROWTYPE;
jbe@339 1478 "initiative_row" "initiative"%ROWTYPE;
jbe@339 1479 "state_v" "issue_state";
jbe@339 1480 BEGIN
jbe@339 1481 PERFORM "require_transaction_isolation"();
jbe@339 1482 IF "persist" ISNULL THEN
jbe@339 1483 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
jbe@339 1484 FOR UPDATE;
jbe@339 1485 IF "issue_row"."closed" NOTNULL THEN
jbe@339 1486 RETURN NULL;
jbe@339 1487 END IF;
jbe@339 1488 "persist"."state" := "issue_row"."state";
jbe@339 1489 IF
jbe@339 1490 ( "issue_row"."state" = 'admission' AND now() >=
jbe@339 1491 "issue_row"."created" + "issue_row"."admission_time" ) OR
jbe@339 1492 ( "issue_row"."state" = 'discussion' AND now() >=
jbe@339 1493 "issue_row"."accepted" + "issue_row"."discussion_time" ) OR
jbe@339 1494 ( "issue_row"."state" = 'verification' AND now() >=
jbe@339 1495 "issue_row"."half_frozen" + "issue_row"."verification_time" ) OR
jbe@339 1496 ( "issue_row"."state" = 'voting' AND now() >=
jbe@339 1497 "issue_row"."fully_frozen" + "issue_row"."voting_time" )
jbe@339 1498 THEN
jbe@339 1499 "persist"."phase_finished" := TRUE;
jbe@339 1500 ELSE
jbe@339 1501 "persist"."phase_finished" := FALSE;
jbe@339 1502 END IF;
jbe@339 1503 IF
jbe@339 1504 NOT EXISTS (
jbe@339 1505 -- all initiatives are revoked
jbe@339 1506 SELECT NULL FROM "initiative"
jbe@339 1507 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
jbe@339 1508 ) AND (
jbe@339 1509 -- and issue has not been accepted yet
jbe@339 1510 "persist"."state" = 'admission' OR
jbe@339 1511 -- or verification time has elapsed
jbe@339 1512 ( "persist"."state" = 'verification' AND
jbe@339 1513 "persist"."phase_finished" ) OR
jbe@339 1514 -- or no initiatives have been revoked lately
jbe@339 1515 NOT EXISTS (
jbe@339 1516 SELECT NULL FROM "initiative"
jbe@339 1517 WHERE "issue_id" = "issue_id_p"
jbe@339 1518 AND now() < "revoked" + "issue_row"."verification_time"
jbe@339 1519 )
jbe@339 1520 )
jbe@339 1521 THEN
jbe@339 1522 "persist"."issue_revoked" := TRUE;
jbe@339 1523 ELSE
jbe@339 1524 "persist"."issue_revoked" := FALSE;
jbe@339 1525 END IF;
jbe@339 1526 IF "persist"."phase_finished" OR "persist"."issue_revoked" THEN
jbe@339 1527 UPDATE "issue" SET "phase_finished" = now()
jbe@339 1528 WHERE "id" = "issue_row"."id";
jbe@339 1529 RETURN "persist";
jbe@339 1530 ELSIF
jbe@339 1531 "persist"."state" IN ('admission', 'discussion', 'verification')
jbe@339 1532 THEN
jbe@339 1533 RETURN "persist";
jbe@339 1534 ELSE
jbe@339 1535 RETURN NULL;
jbe@339 1536 END IF;
jbe@339 1537 END IF;
jbe@339 1538 IF
jbe@339 1539 "persist"."state" IN ('admission', 'discussion', 'verification') AND
jbe@339 1540 coalesce("persist"."snapshot_created", FALSE) = FALSE
jbe@339 1541 THEN
jbe@339 1542 PERFORM "create_snapshot"("issue_id_p");
jbe@339 1543 "persist"."snapshot_created" = TRUE;
jbe@339 1544 IF "persist"."phase_finished" THEN
jbe@339 1545 IF "persist"."state" = 'admission' THEN
jbe@339 1546 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
jbe@339 1547 ELSIF "persist"."state" = 'discussion' THEN
jbe@339 1548 PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze');
jbe@339 1549 ELSIF "persist"."state" = 'verification' THEN
jbe@339 1550 PERFORM "set_snapshot_event"("issue_id_p", 'full_freeze');
jbe@339 1551 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
jbe@339 1552 SELECT * INTO "policy_row" FROM "policy"
jbe@339 1553 WHERE "id" = "issue_row"."policy_id";
jbe@339 1554 FOR "initiative_row" IN
jbe@339 1555 SELECT * FROM "initiative"
jbe@339 1556 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
jbe@339 1557 FOR UPDATE
jbe@339 1558 LOOP
jbe@339 1559 IF
jbe@339 1560 "initiative_row"."polling" OR (
jbe@339 1561 "initiative_row"."satisfied_supporter_count" > 0 AND
jbe@339 1562 "initiative_row"."satisfied_supporter_count" *
jbe@339 1563 "policy_row"."initiative_quorum_den" >=
jbe@339 1564 "issue_row"."population" * "policy_row"."initiative_quorum_num"
jbe@339 1565 )
jbe@339 1566 THEN
jbe@339 1567 UPDATE "initiative" SET "admitted" = TRUE
jbe@339 1568 WHERE "id" = "initiative_row"."id";
jbe@339 1569 ELSE
jbe@339 1570 UPDATE "initiative" SET "admitted" = FALSE
jbe@339 1571 WHERE "id" = "initiative_row"."id";
jbe@339 1572 END IF;
jbe@339 1573 END LOOP;
jbe@339 1574 END IF;
jbe@339 1575 END IF;
jbe@339 1576 RETURN "persist";
jbe@339 1577 END IF;
jbe@339 1578 IF
jbe@339 1579 "persist"."state" IN ('admission', 'discussion', 'verification') AND
jbe@339 1580 coalesce("persist"."harmonic_weights_set", FALSE) = FALSE
jbe@339 1581 THEN
jbe@339 1582 PERFORM "set_harmonic_initiative_weights"("issue_id_p");
jbe@339 1583 "persist"."harmonic_weights_set" = TRUE;
jbe@339 1584 IF
jbe@339 1585 "persist"."phase_finished" OR
jbe@339 1586 "persist"."issue_revoked" OR
jbe@339 1587 "persist"."state" = 'admission'
jbe@339 1588 THEN
jbe@339 1589 RETURN "persist";
jbe@339 1590 ELSE
jbe@339 1591 RETURN NULL;
jbe@339 1592 END IF;
jbe@339 1593 END IF;
jbe@339 1594 IF "persist"."issue_revoked" THEN
jbe@339 1595 IF "persist"."state" = 'admission' THEN
jbe@339 1596 "state_v" := 'canceled_revoked_before_accepted';
jbe@339 1597 ELSIF "persist"."state" = 'discussion' THEN
jbe@339 1598 "state_v" := 'canceled_after_revocation_during_discussion';
jbe@339 1599 ELSIF "persist"."state" = 'verification' THEN
jbe@339 1600 "state_v" := 'canceled_after_revocation_during_verification';
jbe@339 1601 END IF;
jbe@339 1602 UPDATE "issue" SET
jbe@339 1603 "state" = "state_v",
jbe@339 1604 "closed" = "phase_finished",
jbe@339 1605 "phase_finished" = NULL
jbe@339 1606 WHERE "id" = "issue_id_p";
jbe@339 1607 RETURN NULL;
jbe@339 1608 END IF;
jbe@339 1609 IF "persist"."state" = 'admission' THEN
jbe@339 1610 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
jbe@339 1611 FOR UPDATE;
jbe@339 1612 SELECT * INTO "policy_row"
jbe@339 1613 FROM "policy" WHERE "id" = "issue_row"."policy_id";
jbe@339 1614 IF EXISTS (
jbe@339 1615 SELECT NULL FROM "initiative"
jbe@339 1616 WHERE "issue_id" = "issue_id_p"
jbe@339 1617 AND "supporter_count" > 0
jbe@339 1618 AND "supporter_count" * "policy_row"."issue_quorum_den"
jbe@339 1619 >= "issue_row"."population" * "policy_row"."issue_quorum_num"
jbe@339 1620 ) THEN
jbe@339 1621 UPDATE "issue" SET
jbe@339 1622 "state" = 'discussion',
jbe@339 1623 "accepted" = coalesce("phase_finished", now()),
jbe@339 1624 "phase_finished" = NULL
jbe@339 1625 WHERE "id" = "issue_id_p";
jbe@339 1626 ELSIF "issue_row"."phase_finished" NOTNULL THEN
jbe@339 1627 UPDATE "issue" SET
jbe@339 1628 "state" = 'canceled_issue_not_accepted',
jbe@339 1629 "closed" = "phase_finished",
jbe@339 1630 "phase_finished" = NULL
jbe@339 1631 WHERE "id" = "issue_id_p";
jbe@339 1632 END IF;
jbe@339 1633 RETURN NULL;
jbe@339 1634 END IF;
jbe@339 1635 IF "persist"."phase_finished" THEN
jbe@339 1636 if "persist"."state" = 'discussion' THEN
jbe@339 1637 UPDATE "issue" SET
jbe@339 1638 "state" = 'verification',
jbe@339 1639 "half_frozen" = "phase_finished",
jbe@339 1640 "phase_finished" = NULL
jbe@339 1641 WHERE "id" = "issue_id_p";
jbe@339 1642 RETURN NULL;
jbe@339 1643 END IF;
jbe@339 1644 IF "persist"."state" = 'verification' THEN
jbe@339 1645 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
jbe@339 1646 FOR UPDATE;
jbe@339 1647 SELECT * INTO "policy_row" FROM "policy"
jbe@339 1648 WHERE "id" = "issue_row"."policy_id";
jbe@339 1649 IF EXISTS (
jbe@339 1650 SELECT NULL FROM "initiative"
jbe@339 1651 WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE
jbe@339 1652 ) THEN
jbe@339 1653 UPDATE "issue" SET
jbe@343 1654 "state" = 'voting',
jbe@343 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@343 1660 "state" = 'canceled_no_initiative_admitted',
jbe@343 1661 "fully_frozen" = "phase_finished",
jbe@343 1662 "closed" = "phase_finished",
jbe@343 1663 "phase_finished" = NULL
jbe@339 1664 WHERE "id" = "issue_id_p";
jbe@339 1665 -- NOTE: The following DELETE statements have effect only when
jbe@339 1666 -- issue state has been manipulated
jbe@339 1667 DELETE FROM "direct_voter" WHERE "issue_id" = "issue_id_p";
jbe@339 1668 DELETE FROM "delegating_voter" WHERE "issue_id" = "issue_id_p";
jbe@339 1669 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
jbe@339 1670 END IF;
jbe@339 1671 RETURN NULL;
jbe@339 1672 END IF;
jbe@339 1673 IF "persist"."state" = 'voting' THEN
jbe@339 1674 IF coalesce("persist"."closed_voting", FALSE) = FALSE THEN
jbe@339 1675 PERFORM "close_voting"("issue_id_p");
jbe@339 1676 "persist"."closed_voting" = TRUE;
jbe@339 1677 RETURN "persist";
jbe@339 1678 END IF;
jbe@339 1679 PERFORM "calculate_ranks"("issue_id_p");
jbe@339 1680 RETURN NULL;
jbe@339 1681 END IF;
jbe@339 1682 END IF;
jbe@339 1683 RAISE WARNING 'should not happen';
jbe@339 1684 RETURN NULL;
jbe@339 1685 END;
jbe@339 1686 $$;
jbe@339 1687 COMMENT ON FUNCTION "check_issue"
jbe@339 1688 ( "issue"."id"%TYPE,
jbe@339 1689 "check_issue_persistence" )
jbe@339 1690 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 1691
jbe@339 1692 CREATE OR REPLACE FUNCTION "check_everything"()
jbe@339 1693 RETURNS VOID
jbe@339 1694 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@339 1695 DECLARE
jbe@339 1696 "issue_id_v" "issue"."id"%TYPE;
jbe@339 1697 "persist_v" "check_issue_persistence";
jbe@339 1698 BEGIN
jbe@339 1699 RAISE WARNING 'Function "check_everything" should only be used for development and debugging purposes';
jbe@339 1700 DELETE FROM "expired_session";
jbe@339 1701 PERFORM "check_activity"();
jbe@339 1702 PERFORM "calculate_member_counts"();
jbe@339 1703 FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP
jbe@339 1704 "persist_v" := NULL;
jbe@339 1705 LOOP
jbe@339 1706 "persist_v" := "check_issue"("issue_id_v", "persist_v");
jbe@339 1707 EXIT WHEN "persist_v" ISNULL;
jbe@339 1708 END LOOP;
jbe@339 1709 END LOOP;
jbe@339 1710 RETURN;
jbe@339 1711 END;
jbe@339 1712 $$;
jbe@339 1713 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 1714
jbe@339 1715 CREATE OR REPLACE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE)
jbe@339 1716 RETURNS VOID
jbe@339 1717 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@339 1718 DECLARE
jbe@339 1719 "issue_row" "issue"%ROWTYPE;
jbe@339 1720 BEGIN
jbe@339 1721 SELECT * INTO "issue_row"
jbe@339 1722 FROM "issue" WHERE "id" = "issue_id_p"
jbe@339 1723 FOR UPDATE;
jbe@339 1724 IF "issue_row"."cleaned" ISNULL THEN
jbe@339 1725 UPDATE "issue" SET
jbe@339 1726 "state" = 'voting',
jbe@339 1727 "closed" = NULL
jbe@339 1728 WHERE "id" = "issue_id_p";
jbe@339 1729 DELETE FROM "delegating_voter"
jbe@339 1730 WHERE "issue_id" = "issue_id_p";
jbe@339 1731 DELETE FROM "direct_voter"
jbe@339 1732 WHERE "issue_id" = "issue_id_p";
jbe@339 1733 DELETE FROM "delegating_interest_snapshot"
jbe@339 1734 WHERE "issue_id" = "issue_id_p";
jbe@339 1735 DELETE FROM "direct_interest_snapshot"
jbe@339 1736 WHERE "issue_id" = "issue_id_p";
jbe@339 1737 DELETE FROM "delegating_population_snapshot"
jbe@339 1738 WHERE "issue_id" = "issue_id_p";
jbe@339 1739 DELETE FROM "direct_population_snapshot"
jbe@339 1740 WHERE "issue_id" = "issue_id_p";
jbe@339 1741 DELETE FROM "non_voter"
jbe@339 1742 WHERE "issue_id" = "issue_id_p";
jbe@339 1743 DELETE FROM "delegation"
jbe@339 1744 WHERE "issue_id" = "issue_id_p";
jbe@339 1745 DELETE FROM "supporter"
jbe@339 1746 USING "initiative" -- NOTE: due to missing index on issue_id
jbe@339 1747 WHERE "initiative"."issue_id" = "issue_id_p"
jbe@339 1748 AND "supporter"."initiative_id" = "initiative_id";
jbe@339 1749 UPDATE "issue" SET
jbe@339 1750 "state" = "issue_row"."state",
jbe@339 1751 "closed" = "issue_row"."closed",
jbe@339 1752 "cleaned" = now()
jbe@339 1753 WHERE "id" = "issue_id_p";
jbe@339 1754 END IF;
jbe@339 1755 RETURN;
jbe@339 1756 END;
jbe@339 1757 $$;
jbe@339 1758
jbe@339 1759 COMMIT;

Impressum / About Us