liquid_feedback_core

annotate update/core-update.v2.1.0-v2.2.0.sql @ 347:77d9eccc167c

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

Impressum / About Us