liquid_feedback_core

annotate update/core-update.v2.1.0-v2.2.0.sql @ 378:e88d0606891f

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

Impressum / About Us