liquid_feedback_core: update/core-update.v2.1.0-v2.2.0.sql annotate

liquid_feedback_core

annotate update/core-update.v2.1.0-v2.2.0.sql @ 423:73c2ab2d068f

Work on configuration of complexity of counting of the votes (extent of tie-breaking):
- added configuration field in "policy" table
- calculation of secondary criterion for the defeat strength (tie-breaking of the links) based on initiative id's

author	jbe
date	Thu Apr 10 00:20:03 2014 +0200 (2014-04-10)
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;