
jbe@339: BEGIN;
jbe@339: 
jbe@339: CREATE OR REPLACE VIEW "liquid_feedback_version" AS
jbe@339:   SELECT * FROM (VALUES ('2.2.0', 2, 2, 0))
jbe@339:   AS "subquery"("string", "major", "minor", "revision");
jbe@339: 
jbe@339: DROP VIEW "issue_with_ranks_missing";
jbe@339: DROP VIEW "open_issue"; -- recreated later
jbe@339: DROP VIEW "event_seen_by_member";  -- recreated later
jbe@339: DROP VIEW "selected_event_seen_by_member";  -- recreated later
jbe@339: ALTER TABLE "issue" DROP CONSTRAINT "valid_state";
jbe@339: ALTER TABLE "issue" DROP COLUMN "ranks_available";
jbe@339: ALTER TABLE "event" DROP CONSTRAINT "event_state_check";
jbe@339: ALTER TABLE "event" DROP CONSTRAINT "null_constraints_for_issue_state_changed";  -- recreated later
jbe@339: ALTER TABLE "event" DROP CONSTRAINT "null_constraints_for_initiative_creation_or_revocation_or_new_draft";  -- recreated later
jbe@339: ALTER TABLE "event" DROP CONSTRAINT "null_constraints_for_suggestion_creation";  -- recreated later
jbe@339: ALTER TYPE "issue_state" RENAME TO "issue_state_old";
jbe@339: CREATE TYPE "issue_state" AS ENUM (
jbe@339:         'admission', 'discussion', 'verification', 'voting',
jbe@339:         'canceled_revoked_before_accepted',
jbe@339:         'canceled_issue_not_accepted',
jbe@339:         'canceled_after_revocation_during_discussion',
jbe@339:         'canceled_after_revocation_during_verification',
jbe@339:         'canceled_no_initiative_admitted',
jbe@339:         'finished_without_winner', 'finished_with_winner');
jbe@339: ALTER TABLE "issue" ALTER COLUMN "state" DROP DEFAULT;
jbe@339: ALTER TABLE "issue" ALTER COLUMN "state" TYPE "issue_state" USING "state"::text::"issue_state";
jbe@339: ALTER TABLE "event" ALTER COLUMN "state" TYPE "issue_state" USING "state"::text::"issue_state";
jbe@339: DROP TYPE "issue_state_old";
jbe@339: ALTER TABLE "issue" ALTER COLUMN "state" SET DEFAULT 'admission';
jbe@339: ALTER TABLE "issue" ADD CONSTRAINT "valid_state"
jbe@339:         CHECK ((
jbe@339:           ("accepted" ISNULL  AND "half_frozen" ISNULL  AND "fully_frozen" ISNULL ) OR
jbe@339:           ("accepted" NOTNULL AND "half_frozen" ISNULL  AND "fully_frozen" ISNULL ) OR
jbe@339:           ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL ) OR
jbe@339:           ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL)
jbe@339:         ) AND (
jbe@339:           ("state" = 'admission'    AND "closed" ISNULL AND "accepted" ISNULL) OR
jbe@339:           ("state" = 'discussion'   AND "closed" ISNULL AND "accepted" NOTNULL AND "half_frozen" ISNULL) OR
jbe@339:           ("state" = 'verification' AND "closed" ISNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL) OR
jbe@339:           ("state" = 'voting'       AND "closed" ISNULL AND "fully_frozen" NOTNULL) OR
jbe@339:           ("state" = 'canceled_revoked_before_accepted'              AND "closed" NOTNULL AND "accepted" ISNULL) OR
jbe@339:           ("state" = 'canceled_issue_not_accepted'                   AND "closed" NOTNULL AND "accepted" ISNULL) OR
jbe@339:           ("state" = 'canceled_after_revocation_during_discussion'   AND "closed" NOTNULL AND "half_frozen"  ISNULL) OR
jbe@339:           ("state" = 'canceled_after_revocation_during_verification' AND "closed" NOTNULL AND "fully_frozen" ISNULL) OR
jbe@339:           ("state" = 'canceled_no_initiative_admitted' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "closed" = "fully_frozen") OR
jbe@339:           ("state" = 'finished_without_winner'         AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "closed" != "fully_frozen") OR
jbe@339:           ("state" = 'finished_with_winner'            AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "closed" != "fully_frozen")
jbe@339:         ));
jbe@339: ALTER TABLE "issue" ADD COLUMN "phase_finished" TIMESTAMPTZ;
jbe@339: 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: ALTER TABLE "issue" ADD CONSTRAINT "phase_finished_only_when_not_closed"
jbe@339:           CHECK ("phase_finished" ISNULL OR "closed" ISNULL);
jbe@339: ALTER TABLE "event" ADD CONSTRAINT "null_constraints_for_issue_state_changed" CHECK (
jbe@339:           "event" != 'issue_state_changed' OR (
jbe@339:             "member_id"     ISNULL  AND
jbe@339:             "issue_id"      NOTNULL AND
jbe@339:             "state"         NOTNULL AND
jbe@339:             "initiative_id" ISNULL  AND
jbe@339:             "draft_id"      ISNULL  AND
jbe@339:             "suggestion_id" ISNULL  ));
jbe@339: ALTER TABLE "event" ADD CONSTRAINT "null_constraints_for_initiative_creation_or_revocation_or_new_draft" CHECK (
jbe@339:           "event" NOT IN (
jbe@339:             'initiative_created_in_new_issue',
jbe@339:             'initiative_created_in_existing_issue',
jbe@339:             'initiative_revoked',
jbe@339:             'new_draft_created'
jbe@339:           ) OR (
jbe@339:             "member_id"     NOTNULL AND
jbe@339:             "issue_id"      NOTNULL AND
jbe@339:             "state"         NOTNULL AND
jbe@339:             "initiative_id" NOTNULL AND
jbe@339:             "draft_id"      NOTNULL AND
jbe@339:             "suggestion_id" ISNULL  ));
jbe@339: ALTER TABLE "event" ADD CONSTRAINT "null_constraints_for_suggestion_creation" CHECK (
jbe@339:           "event" != 'suggestion_created' OR (
jbe@339:             "member_id"     NOTNULL AND
jbe@339:             "issue_id"      NOTNULL AND
jbe@339:             "state"         NOTNULL AND
jbe@339:             "initiative_id" NOTNULL AND
jbe@339:             "draft_id"      ISNULL  AND
jbe@339:             "suggestion_id" NOTNULL ));
jbe@339: CREATE VIEW "open_issue" AS
jbe@339:   SELECT * FROM "issue" WHERE "closed" ISNULL;
jbe@339: COMMENT ON VIEW "open_issue" IS 'All open issues';
jbe@339: CREATE VIEW "event_seen_by_member" AS
jbe@339:   SELECT
jbe@339:     "member"."id" AS "seen_by_member_id",
jbe@339:     CASE WHEN "event"."state" IN (
jbe@339:       'voting',
jbe@339:       'finished_without_winner',
jbe@339:       'finished_with_winner'
jbe@339:     ) THEN
jbe@339:       'voting'::"notify_level"
jbe@339:     ELSE
jbe@339:       CASE WHEN "event"."state" IN (
jbe@339:         'verification',
jbe@339:         'canceled_after_revocation_during_verification',
jbe@339:         'canceled_no_initiative_admitted'
jbe@339:       ) THEN
jbe@339:         'verification'::"notify_level"
jbe@339:       ELSE
jbe@339:         CASE WHEN "event"."state" IN (
jbe@339:           'discussion',
jbe@339:           'canceled_after_revocation_during_discussion'
jbe@339:         ) THEN
jbe@339:           'discussion'::"notify_level"
jbe@339:         ELSE
jbe@339:           'all'::"notify_level"
jbe@339:         END
jbe@339:       END
jbe@339:     END AS "notify_level",
jbe@339:     "event".*
jbe@339:   FROM "member" CROSS JOIN "event"
jbe@339:   LEFT JOIN "issue"
jbe@339:     ON "event"."issue_id" = "issue"."id"
jbe@339:   LEFT JOIN "membership"
jbe@339:     ON "member"."id" = "membership"."member_id"
jbe@339:     AND "issue"."area_id" = "membership"."area_id"
jbe@339:   LEFT JOIN "interest"
jbe@339:     ON "member"."id" = "interest"."member_id"
jbe@339:     AND "event"."issue_id" = "interest"."issue_id"
jbe@339:   LEFT JOIN "supporter"
jbe@339:     ON "member"."id" = "supporter"."member_id"
jbe@339:     AND "event"."initiative_id" = "supporter"."initiative_id"
jbe@339:   LEFT JOIN "ignored_member"
jbe@339:     ON "member"."id" = "ignored_member"."member_id"
jbe@339:     AND "event"."member_id" = "ignored_member"."other_member_id"
jbe@339:   LEFT JOIN "ignored_initiative"
jbe@339:     ON "member"."id" = "ignored_initiative"."member_id"
jbe@339:     AND "event"."initiative_id" = "ignored_initiative"."initiative_id"
jbe@339:   WHERE (
jbe@339:     "supporter"."member_id" NOTNULL OR
jbe@339:     "interest"."member_id" NOTNULL OR
jbe@339:     ( "membership"."member_id" NOTNULL AND
jbe@339:       "event"."event" IN (
jbe@339:         'issue_state_changed',
jbe@339:         'initiative_created_in_new_issue',
jbe@339:         'initiative_created_in_existing_issue',
jbe@339:         'initiative_revoked' ) ) )
jbe@339:   AND "ignored_member"."member_id" ISNULL
jbe@339:   AND "ignored_initiative"."member_id" ISNULL;
jbe@339: 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: CREATE VIEW "selected_event_seen_by_member" AS
jbe@339:   SELECT
jbe@339:     "member"."id" AS "seen_by_member_id",
jbe@339:     CASE WHEN "event"."state" IN (
jbe@339:       'voting',
jbe@339:       'finished_without_winner',
jbe@339:       'finished_with_winner'
jbe@339:     ) THEN
jbe@339:       'voting'::"notify_level"
jbe@339:     ELSE
jbe@339:       CASE WHEN "event"."state" IN (
jbe@339:         'verification',
jbe@339:         'canceled_after_revocation_during_verification',
jbe@339:         'canceled_no_initiative_admitted'
jbe@339:       ) THEN
jbe@339:         'verification'::"notify_level"
jbe@339:       ELSE
jbe@339:         CASE WHEN "event"."state" IN (
jbe@339:           'discussion',
jbe@339:           'canceled_after_revocation_during_discussion'
jbe@339:         ) THEN
jbe@339:           'discussion'::"notify_level"
jbe@339:         ELSE
jbe@339:           'all'::"notify_level"
jbe@339:         END
jbe@339:       END
jbe@339:     END AS "notify_level",
jbe@339:     "event".*
jbe@339:   FROM "member" CROSS JOIN "event"
jbe@339:   LEFT JOIN "issue"
jbe@339:     ON "event"."issue_id" = "issue"."id"
jbe@339:   LEFT JOIN "membership"
jbe@339:     ON "member"."id" = "membership"."member_id"
jbe@339:     AND "issue"."area_id" = "membership"."area_id"
jbe@339:   LEFT JOIN "interest"
jbe@339:     ON "member"."id" = "interest"."member_id"
jbe@339:     AND "event"."issue_id" = "interest"."issue_id"
jbe@339:   LEFT JOIN "supporter"
jbe@339:     ON "member"."id" = "supporter"."member_id"
jbe@339:     AND "event"."initiative_id" = "supporter"."initiative_id"
jbe@339:   LEFT JOIN "ignored_member"
jbe@339:     ON "member"."id" = "ignored_member"."member_id"
jbe@339:     AND "event"."member_id" = "ignored_member"."other_member_id"
jbe@339:   LEFT JOIN "ignored_initiative"
jbe@339:     ON "member"."id" = "ignored_initiative"."member_id"
jbe@339:     AND "event"."initiative_id" = "ignored_initiative"."initiative_id"
jbe@339:   WHERE (
jbe@339:     ( "member"."notify_level" >= 'all' ) OR
jbe@339:     ( "member"."notify_level" >= 'voting' AND
jbe@339:       "event"."state" IN (
jbe@339:         'voting',
jbe@339:         'finished_without_winner',
jbe@339:         'finished_with_winner' ) ) OR
jbe@339:     ( "member"."notify_level" >= 'verification' AND
jbe@339:       "event"."state" IN (
jbe@339:         'verification',
jbe@339:         'canceled_after_revocation_during_verification',
jbe@339:         'canceled_no_initiative_admitted' ) ) OR
jbe@339:     ( "member"."notify_level" >= 'discussion' AND
jbe@339:       "event"."state" IN (
jbe@339:         'discussion',
jbe@339:         'canceled_after_revocation_during_discussion' ) ) )
jbe@339:   AND (
jbe@339:     "supporter"."member_id" NOTNULL OR
jbe@339:     "interest"."member_id" NOTNULL OR
jbe@339:     ( "membership"."member_id" NOTNULL AND
jbe@339:       "event"."event" IN (
jbe@339:         'issue_state_changed',
jbe@339:         'initiative_created_in_new_issue',
jbe@339:         'initiative_created_in_existing_issue',
jbe@339:         'initiative_revoked' ) ) )
jbe@339:   AND "ignored_member"."member_id" ISNULL
jbe@339:   AND "ignored_initiative"."member_id" ISNULL;
jbe@339: 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: 
jbe@339: ALTER TABLE "initiative" ADD COLUMN "harmonic_weight" NUMERIC(12, 3);
jbe@339: 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: 
jbe@339: CREATE OR REPLACE FUNCTION "write_event_issue_state_changed_trigger"()
jbe@339:   RETURNS TRIGGER
jbe@339:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@339:     BEGIN
jbe@339:       IF NEW."state" != OLD."state" THEN
jbe@339:         INSERT INTO "event" ("event", "issue_id", "state")
jbe@339:           VALUES ('issue_state_changed', NEW."id", NEW."state");
jbe@339:       END IF;
jbe@339:       RETURN NULL;
jbe@339:     END;
jbe@339:   $$;
jbe@339: 
jbe@339: CREATE OR REPLACE FUNCTION "forbid_changes_on_closed_issue_trigger"()
jbe@339:   RETURNS TRIGGER
jbe@339:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@339:     DECLARE
jbe@339:       "issue_id_v" "issue"."id"%TYPE;
jbe@339:       "issue_row"  "issue"%ROWTYPE;
jbe@339:     BEGIN
jbe@339:       IF TG_OP = 'DELETE' THEN
jbe@339:         "issue_id_v" := OLD."issue_id";
jbe@339:       ELSE
jbe@339:         "issue_id_v" := NEW."issue_id";
jbe@339:       END IF;
jbe@339:       SELECT INTO "issue_row" * FROM "issue"
jbe@339:         WHERE "id" = "issue_id_v" FOR SHARE;
jbe@339:       IF "issue_row"."closed" NOTNULL THEN
jbe@339:         IF
jbe@339:           TG_RELID = 'direct_voter'::regclass AND
jbe@339:           TG_OP = 'UPDATE'
jbe@339:         THEN
jbe@339:           IF
jbe@339:             OLD."issue_id"  = NEW."issue_id"  AND
jbe@339:             OLD."member_id" = NEW."member_id" AND
jbe@339:             OLD."weight" = NEW."weight"
jbe@339:           THEN
jbe@339:             RETURN NULL;  -- allows changing of voter comment
jbe@339:           END IF;
jbe@339:         END IF;
jbe@339:         RAISE EXCEPTION 'Tried to modify data belonging to a closed issue.';
jbe@339:       ELSIF
jbe@339:         "issue_row"."state" = 'voting' AND
jbe@339:         "issue_row"."phase_finished" NOTNULL
jbe@339:       THEN
jbe@339:         IF TG_RELID = 'vote'::regclass THEN
jbe@339:           RAISE EXCEPTION 'Tried to modify data after voting has been closed.';
jbe@339:         END IF;
jbe@339:       END IF;
jbe@339:       RETURN NULL;
jbe@339:     END;
jbe@339:   $$;
jbe@339: 
jbe@339: CREATE OR REPLACE VIEW "battle_view" AS
jbe@339:   SELECT
jbe@339:     "issue"."id" AS "issue_id",
jbe@339:     "winning_initiative"."id" AS "winning_initiative_id",
jbe@339:     "losing_initiative"."id" AS "losing_initiative_id",
jbe@339:     sum(
jbe@339:       CASE WHEN
jbe@339:         coalesce("better_vote"."grade", 0) >
jbe@339:         coalesce("worse_vote"."grade", 0)
jbe@339:       THEN "direct_voter"."weight" ELSE 0 END
jbe@339:     ) AS "count"
jbe@339:   FROM "issue"
jbe@339:   LEFT JOIN "direct_voter"
jbe@339:   ON "issue"."id" = "direct_voter"."issue_id"
jbe@339:   JOIN "battle_participant" AS "winning_initiative"
jbe@339:     ON "issue"."id" = "winning_initiative"."issue_id"
jbe@339:   JOIN "battle_participant" AS "losing_initiative"
jbe@339:     ON "issue"."id" = "losing_initiative"."issue_id"
jbe@339:   LEFT JOIN "vote" AS "better_vote"
jbe@339:     ON "direct_voter"."member_id" = "better_vote"."member_id"
jbe@339:     AND "winning_initiative"."id" = "better_vote"."initiative_id"
jbe@339:   LEFT JOIN "vote" AS "worse_vote"
jbe@339:     ON "direct_voter"."member_id" = "worse_vote"."member_id"
jbe@339:     AND "losing_initiative"."id" = "worse_vote"."initiative_id"
jbe@339:   WHERE "issue"."state" = 'voting'
jbe@339:   AND "issue"."phase_finished" NOTNULL
jbe@339:   AND (
jbe@339:     "winning_initiative"."id" != "losing_initiative"."id" OR
jbe@339:     ( ("winning_initiative"."id" NOTNULL AND "losing_initiative"."id" ISNULL) OR
jbe@339:       ("winning_initiative"."id" ISNULL AND "losing_initiative"."id" NOTNULL) ) )
jbe@339:   GROUP BY
jbe@339:     "issue"."id",
jbe@339:     "winning_initiative"."id",
jbe@339:     "losing_initiative"."id";
jbe@339: 
jbe@339: DROP VIEW "timeline";
jbe@339: DROP VIEW "timeline_issue";
jbe@339: DROP VIEW "timeline_initiative";
jbe@339: DROP VIEW "timeline_draft";
jbe@339: DROP VIEW "timeline_suggestion";
jbe@339: DROP TYPE "timeline_event";
jbe@339: 
jbe@339: DROP TRIGGER "share_row_lock_issue" ON "initiative";
jbe@339: DROP TRIGGER "share_row_lock_issue" ON "interest";
jbe@339: DROP TRIGGER "share_row_lock_issue" ON "supporter";
jbe@339: DROP TRIGGER "share_row_lock_issue_via_initiative" ON "opinion";
jbe@339: DROP TRIGGER "share_row_lock_issue" ON "direct_voter";
jbe@339: DROP TRIGGER "share_row_lock_issue" ON "delegating_voter";
jbe@339: DROP TRIGGER "share_row_lock_issue" ON "vote";
jbe@339: DROP FUNCTION "share_row_lock_issue_trigger"();
jbe@339: 
jbe@339: CREATE FUNCTION "require_transaction_isolation"()
jbe@339:   RETURNS VOID
jbe@339:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@339:     BEGIN
jbe@339:       IF
jbe@339:         current_setting('transaction_isolation') NOT IN
jbe@339:         ('repeatable read', 'serializable')
jbe@339:       THEN
jbe@339:         RAISE EXCEPTION 'Insufficient transaction isolation level';
jbe@339:       END IF;
jbe@339:       RETURN;
jbe@339:     END;
jbe@339:   $$;
jbe@344: COMMENT ON FUNCTION "require_transaction_isolation"() IS 'Throws an exception, if transaction isolation level is too low to provide a consistent snapshot';
jbe@339: 
jbe@339: CREATE FUNCTION "dont_require_transaction_isolation"()
jbe@339:   RETURNS VOID
jbe@339:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@339:     BEGIN
jbe@339:       IF
jbe@339:         current_setting('transaction_isolation') IN
jbe@339:         ('repeatable read', 'serializable')
jbe@339:       THEN
jbe@339:         RAISE WARNING 'Unneccessary transaction isolation level: %',
jbe@339:           current_setting('transaction_isolation');
jbe@339:       END IF;
jbe@339:       RETURN;
jbe@339:     END;
jbe@339:   $$;
jbe@344: COMMENT ON FUNCTION "dont_require_transaction_isolation"() IS 'Raises a warning, if transaction isolation level is higher than READ COMMITTED';
jbe@339: 
jbe@339: CREATE OR REPLACE FUNCTION "check_activity"()
jbe@339:   RETURNS VOID
jbe@339:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@339:     DECLARE
jbe@339:       "system_setting_row" "system_setting"%ROWTYPE;
jbe@339:     BEGIN
jbe@339:       PERFORM "dont_require_transaction_isolation"();
jbe@339:       SELECT * INTO "system_setting_row" FROM "system_setting";
jbe@339:       IF "system_setting_row"."member_ttl" NOTNULL THEN
jbe@339:         UPDATE "member" SET "active" = FALSE
jbe@339:           WHERE "active" = TRUE
jbe@339:           AND "last_activity" < (now() - "system_setting_row"."member_ttl")::DATE;
jbe@339:       END IF;
jbe@339:       RETURN;
jbe@339:     END;
jbe@339:   $$;
jbe@339: 
jbe@339: CREATE OR REPLACE FUNCTION "calculate_member_counts"()
jbe@339:   RETURNS VOID
jbe@339:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@339:     BEGIN
jbe@339:       PERFORM "require_transaction_isolation"();
jbe@339:       DELETE FROM "member_count";
jbe@339:       INSERT INTO "member_count" ("total_count")
jbe@339:         SELECT "total_count" FROM "member_count_view";
jbe@339:       UPDATE "unit" SET "member_count" = "view"."member_count"
jbe@339:         FROM "unit_member_count" AS "view"
jbe@339:         WHERE "view"."unit_id" = "unit"."id";
jbe@339:       UPDATE "area" SET
jbe@339:         "direct_member_count" = "view"."direct_member_count",
jbe@339:         "member_weight"       = "view"."member_weight"
jbe@339:         FROM "area_member_count" AS "view"
jbe@339:         WHERE "view"."area_id" = "area"."id";
jbe@339:       RETURN;
jbe@339:     END;
jbe@339:   $$;
jbe@339: 
jbe@339: CREATE VIEW "remaining_harmonic_supporter_weight" AS
jbe@339:   SELECT
jbe@339:     "direct_interest_snapshot"."issue_id",
jbe@339:     "direct_interest_snapshot"."event",
jbe@339:     "direct_interest_snapshot"."member_id",
jbe@339:     "direct_interest_snapshot"."weight" AS "weight_num",
jbe@339:     count("initiative"."id") AS "weight_den"
jbe@339:   FROM "issue"
jbe@339:   JOIN "direct_interest_snapshot"
jbe@339:     ON "issue"."id" = "direct_interest_snapshot"."issue_id"
jbe@339:     AND "issue"."latest_snapshot_event" = "direct_interest_snapshot"."event"
jbe@339:   JOIN "initiative"
jbe@339:     ON "issue"."id" = "initiative"."issue_id"
jbe@339:     AND "initiative"."harmonic_weight" ISNULL
jbe@339:   JOIN "direct_supporter_snapshot"
jbe@339:     ON "initiative"."id" = "direct_supporter_snapshot"."initiative_id"
jbe@339:     AND "direct_interest_snapshot"."event" = "direct_supporter_snapshot"."event"
jbe@339:     AND "direct_interest_snapshot"."member_id" = "direct_supporter_snapshot"."member_id"
jbe@339:     AND (
jbe@339:       "direct_supporter_snapshot"."satisfied" = TRUE OR
jbe@339:       coalesce("initiative"."admitted", FALSE) = FALSE
jbe@339:     )
jbe@339:   GROUP BY
jbe@339:     "direct_interest_snapshot"."issue_id",
jbe@339:     "direct_interest_snapshot"."event",
jbe@339:     "direct_interest_snapshot"."member_id",
jbe@339:     "direct_interest_snapshot"."weight";
jbe@339: COMMENT ON VIEW "remaining_harmonic_supporter_weight" IS 'Helper view for function "set_harmonic_initiative_weights"';
jbe@339: 
jbe@339: CREATE VIEW "remaining_harmonic_initiative_weight_summands" AS
jbe@339:   SELECT
jbe@339:     "initiative"."issue_id",
jbe@339:     "initiative"."id" AS "initiative_id",
jbe@339:     "initiative"."admitted",
jbe@339:     sum("remaining_harmonic_supporter_weight"."weight_num") AS "weight_num",
jbe@339:     "remaining_harmonic_supporter_weight"."weight_den"
jbe@339:   FROM "remaining_harmonic_supporter_weight"
jbe@339:   JOIN "initiative"
jbe@339:     ON "remaining_harmonic_supporter_weight"."issue_id" = "initiative"."issue_id"
jbe@339:     AND "initiative"."harmonic_weight" ISNULL
jbe@339:   JOIN "direct_supporter_snapshot"
jbe@339:     ON "initiative"."id" = "direct_supporter_snapshot"."initiative_id"
jbe@339:     AND "remaining_harmonic_supporter_weight"."event" = "direct_supporter_snapshot"."event"
jbe@339:     AND "remaining_harmonic_supporter_weight"."member_id" = "direct_supporter_snapshot"."member_id"
jbe@339:     AND (
jbe@339:       "direct_supporter_snapshot"."satisfied" = TRUE OR
jbe@339:       coalesce("initiative"."admitted", FALSE) = FALSE
jbe@339:     )
jbe@339:   GROUP BY
jbe@339:     "initiative"."issue_id",
jbe@339:     "initiative"."id",
jbe@339:     "initiative"."admitted",
jbe@339:     "remaining_harmonic_supporter_weight"."weight_den";
jbe@339: COMMENT ON VIEW "remaining_harmonic_initiative_weight_summands" IS 'Helper view for function "set_harmonic_initiative_weights"';
jbe@339: 
jbe@339: CREATE FUNCTION "set_harmonic_initiative_weights"
jbe@339:   ( "issue_id_p" "issue"."id"%TYPE )
jbe@339:   RETURNS VOID
jbe@339:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@339:     DECLARE
jbe@339:       "weight_row"   "remaining_harmonic_initiative_weight_summands"%ROWTYPE;
jbe@339:       "i"            INT4;
jbe@339:       "count_v"      INT4;
jbe@339:       "summand_v"    FLOAT;
jbe@339:       "id_ary"       INT4[];
jbe@339:       "weight_ary"   FLOAT[];
jbe@339:       "min_weight_v" FLOAT;
jbe@339:     BEGIN
jbe@339:       PERFORM "require_transaction_isolation"();
jbe@339:       UPDATE "initiative" SET "harmonic_weight" = NULL
jbe@339:         WHERE "issue_id" = "issue_id_p";
jbe@339:       LOOP
jbe@339:         "min_weight_v" := NULL;
jbe@339:         "i" := 0;
jbe@339:         "count_v" := 0;
jbe@339:         FOR "weight_row" IN
jbe@339:           SELECT * FROM "remaining_harmonic_initiative_weight_summands"
jbe@339:           WHERE "issue_id" = "issue_id_p"
jbe@339:           AND (
jbe@339:             coalesce("admitted", FALSE) = FALSE OR NOT EXISTS (
jbe@339:               SELECT NULL FROM "initiative"
jbe@339:               WHERE "issue_id" = "issue_id_p"
jbe@339:               AND "harmonic_weight" ISNULL
jbe@339:               AND coalesce("admitted", FALSE) = FALSE
jbe@339:             )
jbe@339:           )
jbe@339:           ORDER BY "initiative_id" DESC, "weight_den" DESC
jbe@339:           -- NOTE: non-admitted initiatives placed first (at last positions),
jbe@339:           --       latest initiatives treated worse in case of tie
jbe@339:         LOOP
jbe@339:           "summand_v" := "weight_row"."weight_num"::FLOAT / "weight_row"."weight_den"::FLOAT;
jbe@339:           IF "i" = 0 OR "weight_row"."initiative_id" != "id_ary"["i"] THEN
jbe@339:             "i" := "i" + 1;
jbe@339:             "count_v" := "i";
jbe@339:             "id_ary"["i"] := "weight_row"."initiative_id";
jbe@339:             "weight_ary"["i"] := "summand_v";
jbe@339:           ELSE
jbe@339:             "weight_ary"["i"] := "weight_ary"["i"] + "summand_v";
jbe@339:           END IF;
jbe@339:         END LOOP;
jbe@339:         EXIT WHEN "count_v" = 0;
jbe@339:         "i" := 1;
jbe@339:         LOOP
jbe@339:           "weight_ary"["i"] := "weight_ary"["i"]::NUMERIC(18,9)::NUMERIC(12,3);
jbe@339:           IF "min_weight_v" ISNULL OR "weight_ary"["i"] < "min_weight_v" THEN
jbe@339:             "min_weight_v" := "weight_ary"["i"];
jbe@339:           END IF;
jbe@339:           "i" := "i" + 1;
jbe@339:           EXIT WHEN "i" > "count_v";
jbe@339:         END LOOP;
jbe@339:         "i" := 1;
jbe@339:         LOOP
jbe@339:           IF "weight_ary"["i"] = "min_weight_v" THEN
jbe@339:             UPDATE "initiative" SET "harmonic_weight" = "min_weight_v"
jbe@339:               WHERE "id" = "id_ary"["i"];
jbe@339:             EXIT;
jbe@339:           END IF;
jbe@339:           "i" := "i" + 1;
jbe@339:         END LOOP;
jbe@339:       END LOOP;
jbe@339:       UPDATE "initiative" SET "harmonic_weight" = 0
jbe@339:         WHERE "issue_id" = "issue_id_p" AND "harmonic_weight" ISNULL;
jbe@339:     END;
jbe@339:   $$;
jbe@339: COMMENT ON FUNCTION "set_harmonic_initiative_weights"
jbe@339:   ( "issue"."id"%TYPE )
jbe@339:   IS 'Calculates and sets "harmonic_weight" of initiatives in a given issue';
jbe@339: 
jbe@339: CREATE OR REPLACE FUNCTION "weight_of_added_delegations_for_population_snapshot"
jbe@339:   ( "issue_id_p"            "issue"."id"%TYPE,
jbe@339:     "member_id_p"           "member"."id"%TYPE,
jbe@339:     "delegate_member_ids_p" "delegating_population_snapshot"."delegate_member_ids"%TYPE )
jbe@339:   RETURNS "direct_population_snapshot"."weight"%TYPE
jbe@339:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@339:     DECLARE
jbe@339:       "issue_delegation_row"  "issue_delegation"%ROWTYPE;
jbe@339:       "delegate_member_ids_v" "delegating_population_snapshot"."delegate_member_ids"%TYPE;
jbe@339:       "weight_v"              INT4;
jbe@339:       "sub_weight_v"          INT4;
jbe@339:     BEGIN
jbe@339:       PERFORM "require_transaction_isolation"();
jbe@339:       "weight_v" := 0;
jbe@339:       FOR "issue_delegation_row" IN
jbe@339:         SELECT * FROM "issue_delegation"
jbe@339:         WHERE "trustee_id" = "member_id_p"
jbe@339:         AND "issue_id" = "issue_id_p"
jbe@339:       LOOP
jbe@339:         IF NOT EXISTS (
jbe@339:           SELECT NULL FROM "direct_population_snapshot"
jbe@339:           WHERE "issue_id" = "issue_id_p"
jbe@339:           AND "event" = 'periodic'
jbe@339:           AND "member_id" = "issue_delegation_row"."truster_id"
jbe@339:         ) AND NOT EXISTS (
jbe@339:           SELECT NULL FROM "delegating_population_snapshot"
jbe@339:           WHERE "issue_id" = "issue_id_p"
jbe@339:           AND "event" = 'periodic'
jbe@339:           AND "member_id" = "issue_delegation_row"."truster_id"
jbe@339:         ) THEN
jbe@339:           "delegate_member_ids_v" :=
jbe@339:             "member_id_p" || "delegate_member_ids_p";
jbe@339:           INSERT INTO "delegating_population_snapshot" (
jbe@339:               "issue_id",
jbe@339:               "event",
jbe@339:               "member_id",
jbe@339:               "scope",
jbe@339:               "delegate_member_ids"
jbe@339:             ) VALUES (
jbe@339:               "issue_id_p",
jbe@339:               'periodic',
jbe@339:               "issue_delegation_row"."truster_id",
jbe@339:               "issue_delegation_row"."scope",
jbe@339:               "delegate_member_ids_v"
jbe@339:             );
jbe@339:           "sub_weight_v" := 1 +
jbe@339:             "weight_of_added_delegations_for_population_snapshot"(
jbe@339:               "issue_id_p",
jbe@339:               "issue_delegation_row"."truster_id",
jbe@339:               "delegate_member_ids_v"
jbe@339:             );
jbe@339:           UPDATE "delegating_population_snapshot"
jbe@339:             SET "weight" = "sub_weight_v"
jbe@339:             WHERE "issue_id" = "issue_id_p"
jbe@339:             AND "event" = 'periodic'
jbe@339:             AND "member_id" = "issue_delegation_row"."truster_id";
jbe@339:           "weight_v" := "weight_v" + "sub_weight_v";
jbe@339:         END IF;
jbe@339:       END LOOP;
jbe@339:       RETURN "weight_v";
jbe@339:     END;
jbe@339:   $$;
jbe@339: 
jbe@339: CREATE OR REPLACE FUNCTION "create_population_snapshot"
jbe@339:   ( "issue_id_p" "issue"."id"%TYPE )
jbe@339:   RETURNS VOID
jbe@339:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@339:     DECLARE
jbe@339:       "member_id_v" "member"."id"%TYPE;
jbe@339:     BEGIN
jbe@339:       PERFORM "require_transaction_isolation"();
jbe@339:       DELETE FROM "direct_population_snapshot"
jbe@339:         WHERE "issue_id" = "issue_id_p"
jbe@339:         AND "event" = 'periodic';
jbe@339:       DELETE FROM "delegating_population_snapshot"
jbe@339:         WHERE "issue_id" = "issue_id_p"
jbe@339:         AND "event" = 'periodic';
jbe@339:       INSERT INTO "direct_population_snapshot"
jbe@339:         ("issue_id", "event", "member_id")
jbe@339:         SELECT
jbe@339:           "issue_id_p"                 AS "issue_id",
jbe@339:           'periodic'::"snapshot_event" AS "event",
jbe@339:           "member"."id"                AS "member_id"
jbe@339:         FROM "issue"
jbe@339:         JOIN "area" ON "issue"."area_id" = "area"."id"
jbe@339:         JOIN "membership" ON "area"."id" = "membership"."area_id"
jbe@339:         JOIN "member" ON "membership"."member_id" = "member"."id"
jbe@339:         JOIN "privilege"
jbe@339:           ON "privilege"."unit_id" = "area"."unit_id"
jbe@339:           AND "privilege"."member_id" = "member"."id"
jbe@339:         WHERE "issue"."id" = "issue_id_p"
jbe@339:         AND "member"."active" AND "privilege"."voting_right"
jbe@339:         UNION
jbe@339:         SELECT
jbe@339:           "issue_id_p"                 AS "issue_id",
jbe@339:           'periodic'::"snapshot_event" AS "event",
jbe@339:           "member"."id"                AS "member_id"
jbe@339:         FROM "issue"
jbe@339:         JOIN "area" ON "issue"."area_id" = "area"."id"
jbe@339:         JOIN "interest" ON "issue"."id" = "interest"."issue_id"
jbe@339:         JOIN "member" ON "interest"."member_id" = "member"."id"
jbe@339:         JOIN "privilege"
jbe@339:           ON "privilege"."unit_id" = "area"."unit_id"
jbe@339:           AND "privilege"."member_id" = "member"."id"
jbe@339:         WHERE "issue"."id" = "issue_id_p"
jbe@339:         AND "member"."active" AND "privilege"."voting_right";
jbe@339:       FOR "member_id_v" IN
jbe@339:         SELECT "member_id" FROM "direct_population_snapshot"
jbe@339:         WHERE "issue_id" = "issue_id_p"
jbe@339:         AND "event" = 'periodic'
jbe@339:       LOOP
jbe@339:         UPDATE "direct_population_snapshot" SET
jbe@339:           "weight" = 1 +
jbe@339:             "weight_of_added_delegations_for_population_snapshot"(
jbe@339:               "issue_id_p",
jbe@339:               "member_id_v",
jbe@339:               '{}'
jbe@339:             )
jbe@339:           WHERE "issue_id" = "issue_id_p"
jbe@339:           AND "event" = 'periodic'
jbe@339:           AND "member_id" = "member_id_v";
jbe@339:       END LOOP;
jbe@339:       RETURN;
jbe@339:     END;
jbe@339:   $$;
jbe@339: 
jbe@339: CREATE OR REPLACE FUNCTION "weight_of_added_delegations_for_interest_snapshot"
jbe@339:   ( "issue_id_p"            "issue"."id"%TYPE,
jbe@339:     "member_id_p"           "member"."id"%TYPE,
jbe@339:     "delegate_member_ids_p" "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
jbe@339:   RETURNS "direct_interest_snapshot"."weight"%TYPE
jbe@339:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@339:     DECLARE
jbe@339:       "issue_delegation_row"  "issue_delegation"%ROWTYPE;
jbe@339:       "delegate_member_ids_v" "delegating_interest_snapshot"."delegate_member_ids"%TYPE;
jbe@339:       "weight_v"              INT4;
jbe@339:       "sub_weight_v"          INT4;
jbe@339:     BEGIN
jbe@339:       PERFORM "require_transaction_isolation"();
jbe@339:       "weight_v" := 0;
jbe@339:       FOR "issue_delegation_row" IN
jbe@339:         SELECT * FROM "issue_delegation"
jbe@339:         WHERE "trustee_id" = "member_id_p"
jbe@339:         AND "issue_id" = "issue_id_p"
jbe@339:       LOOP
jbe@339:         IF NOT EXISTS (
jbe@339:           SELECT NULL FROM "direct_interest_snapshot"
jbe@339:           WHERE "issue_id" = "issue_id_p"
jbe@339:           AND "event" = 'periodic'
jbe@339:           AND "member_id" = "issue_delegation_row"."truster_id"
jbe@339:         ) AND NOT EXISTS (
jbe@339:           SELECT NULL FROM "delegating_interest_snapshot"
jbe@339:           WHERE "issue_id" = "issue_id_p"
jbe@339:           AND "event" = 'periodic'
jbe@339:           AND "member_id" = "issue_delegation_row"."truster_id"
jbe@339:         ) THEN
jbe@339:           "delegate_member_ids_v" :=
jbe@339:             "member_id_p" || "delegate_member_ids_p";
jbe@339:           INSERT INTO "delegating_interest_snapshot" (
jbe@339:               "issue_id",
jbe@339:               "event",
jbe@339:               "member_id",
jbe@339:               "scope",
jbe@339:               "delegate_member_ids"
jbe@339:             ) VALUES (
jbe@339:               "issue_id_p",
jbe@339:               'periodic',
jbe@339:               "issue_delegation_row"."truster_id",
jbe@339:               "issue_delegation_row"."scope",
jbe@339:               "delegate_member_ids_v"
jbe@339:             );
jbe@339:           "sub_weight_v" := 1 +
jbe@339:             "weight_of_added_delegations_for_interest_snapshot"(
jbe@339:               "issue_id_p",
jbe@339:               "issue_delegation_row"."truster_id",
jbe@339:               "delegate_member_ids_v"
jbe@339:             );
jbe@339:           UPDATE "delegating_interest_snapshot"
jbe@339:             SET "weight" = "sub_weight_v"
jbe@339:             WHERE "issue_id" = "issue_id_p"
jbe@339:             AND "event" = 'periodic'
jbe@339:             AND "member_id" = "issue_delegation_row"."truster_id";
jbe@339:           "weight_v" := "weight_v" + "sub_weight_v";
jbe@339:         END IF;
jbe@339:       END LOOP;
jbe@339:       RETURN "weight_v";
jbe@339:     END;
jbe@339:   $$;
jbe@339: 
jbe@339: CREATE OR REPLACE FUNCTION "create_interest_snapshot"
jbe@339:   ( "issue_id_p" "issue"."id"%TYPE )
jbe@339:   RETURNS VOID
jbe@339:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@339:     DECLARE
jbe@339:       "member_id_v" "member"."id"%TYPE;
jbe@339:     BEGIN
jbe@339:       PERFORM "require_transaction_isolation"();
jbe@339:       DELETE FROM "direct_interest_snapshot"
jbe@339:         WHERE "issue_id" = "issue_id_p"
jbe@339:         AND "event" = 'periodic';
jbe@339:       DELETE FROM "delegating_interest_snapshot"
jbe@339:         WHERE "issue_id" = "issue_id_p"
jbe@339:         AND "event" = 'periodic';
jbe@339:       DELETE FROM "direct_supporter_snapshot"
jbe@339:         USING "initiative"  -- NOTE: due to missing index on issue_id
jbe@339:         WHERE "initiative"."issue_id" = "issue_id_p"
jbe@339:         AND "direct_supporter_snapshot"."initiative_id" = "initiative"."id"
jbe@339:         AND "direct_supporter_snapshot"."event" = 'periodic';
jbe@339:       INSERT INTO "direct_interest_snapshot"
jbe@339:         ("issue_id", "event", "member_id")
jbe@339:         SELECT
jbe@339:           "issue_id_p"  AS "issue_id",
jbe@339:           'periodic'    AS "event",
jbe@339:           "member"."id" AS "member_id"
jbe@339:         FROM "issue"
jbe@339:         JOIN "area" ON "issue"."area_id" = "area"."id"
jbe@339:         JOIN "interest" ON "issue"."id" = "interest"."issue_id"
jbe@339:         JOIN "member" ON "interest"."member_id" = "member"."id"
jbe@339:         JOIN "privilege"
jbe@339:           ON "privilege"."unit_id" = "area"."unit_id"
jbe@339:           AND "privilege"."member_id" = "member"."id"
jbe@339:         WHERE "issue"."id" = "issue_id_p"
jbe@339:         AND "member"."active" AND "privilege"."voting_right";
jbe@339:       FOR "member_id_v" IN
jbe@339:         SELECT "member_id" FROM "direct_interest_snapshot"
jbe@339:         WHERE "issue_id" = "issue_id_p"
jbe@339:         AND "event" = 'periodic'
jbe@339:       LOOP
jbe@339:         UPDATE "direct_interest_snapshot" SET
jbe@339:           "weight" = 1 +
jbe@339:             "weight_of_added_delegations_for_interest_snapshot"(
jbe@339:               "issue_id_p",
jbe@339:               "member_id_v",
jbe@339:               '{}'
jbe@339:             )
jbe@339:           WHERE "issue_id" = "issue_id_p"
jbe@339:           AND "event" = 'periodic'
jbe@339:           AND "member_id" = "member_id_v";
jbe@339:       END LOOP;
jbe@339:       INSERT INTO "direct_supporter_snapshot"
jbe@339:         ( "issue_id", "initiative_id", "event", "member_id",
jbe@339:           "draft_id", "informed", "satisfied" )
jbe@339:         SELECT
jbe@339:           "issue_id_p"            AS "issue_id",
jbe@339:           "initiative"."id"       AS "initiative_id",
jbe@339:           'periodic'              AS "event",
jbe@339:           "supporter"."member_id" AS "member_id",
jbe@339:           "supporter"."draft_id"  AS "draft_id",
jbe@339:           "supporter"."draft_id" = "current_draft"."id" AS "informed",
jbe@339:           NOT EXISTS (
jbe@339:             SELECT NULL FROM "critical_opinion"
jbe@339:             WHERE "initiative_id" = "initiative"."id"
jbe@339:             AND "member_id" = "supporter"."member_id"
jbe@339:           ) AS "satisfied"
jbe@339:         FROM "initiative"
jbe@339:         JOIN "supporter"
jbe@339:         ON "supporter"."initiative_id" = "initiative"."id"
jbe@339:         JOIN "current_draft"
jbe@339:         ON "initiative"."id" = "current_draft"."initiative_id"
jbe@339:         JOIN "direct_interest_snapshot"
jbe@339:         ON "supporter"."member_id" = "direct_interest_snapshot"."member_id"
jbe@339:         AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"
jbe@339:         AND "event" = 'periodic'
jbe@339:         WHERE "initiative"."issue_id" = "issue_id_p";
jbe@339:       RETURN;
jbe@339:     END;
jbe@339:   $$;
jbe@339: 
jbe@339: CREATE OR REPLACE FUNCTION "create_snapshot"
jbe@339:   ( "issue_id_p" "issue"."id"%TYPE )
jbe@339:   RETURNS VOID
jbe@339:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@339:     DECLARE
jbe@339:       "initiative_id_v"    "initiative"."id"%TYPE;
jbe@339:       "suggestion_id_v"    "suggestion"."id"%TYPE;
jbe@339:     BEGIN
jbe@339:       PERFORM "require_transaction_isolation"();
jbe@339:       PERFORM "create_population_snapshot"("issue_id_p");
jbe@339:       PERFORM "create_interest_snapshot"("issue_id_p");
jbe@339:       UPDATE "issue" SET
jbe@339:         "snapshot" = coalesce("phase_finished", now()),
jbe@339:         "latest_snapshot_event" = 'periodic',
jbe@339:         "population" = (
jbe@339:           SELECT coalesce(sum("weight"), 0)
jbe@339:           FROM "direct_population_snapshot"
jbe@339:           WHERE "issue_id" = "issue_id_p"
jbe@339:           AND "event" = 'periodic'
jbe@339:         )
jbe@339:         WHERE "id" = "issue_id_p";
jbe@339:       FOR "initiative_id_v" IN
jbe@339:         SELECT "id" FROM "initiative" WHERE "issue_id" = "issue_id_p"
jbe@339:       LOOP
jbe@339:         UPDATE "initiative" SET
jbe@339:           "supporter_count" = (
jbe@339:             SELECT coalesce(sum("di"."weight"), 0)
jbe@339:             FROM "direct_interest_snapshot" AS "di"
jbe@339:             JOIN "direct_supporter_snapshot" AS "ds"
jbe@339:             ON "di"."member_id" = "ds"."member_id"
jbe@339:             WHERE "di"."issue_id" = "issue_id_p"
jbe@339:             AND "di"."event" = 'periodic'
jbe@339:             AND "ds"."initiative_id" = "initiative_id_v"
jbe@339:             AND "ds"."event" = 'periodic'
jbe@339:           ),
jbe@339:           "informed_supporter_count" = (
jbe@339:             SELECT coalesce(sum("di"."weight"), 0)
jbe@339:             FROM "direct_interest_snapshot" AS "di"
jbe@339:             JOIN "direct_supporter_snapshot" AS "ds"
jbe@339:             ON "di"."member_id" = "ds"."member_id"
jbe@339:             WHERE "di"."issue_id" = "issue_id_p"
jbe@339:             AND "di"."event" = 'periodic'
jbe@339:             AND "ds"."initiative_id" = "initiative_id_v"
jbe@339:             AND "ds"."event" = 'periodic'
jbe@339:             AND "ds"."informed"
jbe@339:           ),
jbe@339:           "satisfied_supporter_count" = (
jbe@339:             SELECT coalesce(sum("di"."weight"), 0)
jbe@339:             FROM "direct_interest_snapshot" AS "di"
jbe@339:             JOIN "direct_supporter_snapshot" AS "ds"
jbe@339:             ON "di"."member_id" = "ds"."member_id"
jbe@339:             WHERE "di"."issue_id" = "issue_id_p"
jbe@339:             AND "di"."event" = 'periodic'
jbe@339:             AND "ds"."initiative_id" = "initiative_id_v"
jbe@339:             AND "ds"."event" = 'periodic'
jbe@339:             AND "ds"."satisfied"
jbe@339:           ),
jbe@339:           "satisfied_informed_supporter_count" = (
jbe@339:             SELECT coalesce(sum("di"."weight"), 0)
jbe@339:             FROM "direct_interest_snapshot" AS "di"
jbe@339:             JOIN "direct_supporter_snapshot" AS "ds"
jbe@339:             ON "di"."member_id" = "ds"."member_id"
jbe@339:             WHERE "di"."issue_id" = "issue_id_p"
jbe@339:             AND "di"."event" = 'periodic'
jbe@339:             AND "ds"."initiative_id" = "initiative_id_v"
jbe@339:             AND "ds"."event" = 'periodic'
jbe@339:             AND "ds"."informed"
jbe@339:             AND "ds"."satisfied"
jbe@339:           )
jbe@339:           WHERE "id" = "initiative_id_v";
jbe@339:         FOR "suggestion_id_v" IN
jbe@339:           SELECT "id" FROM "suggestion"
jbe@339:           WHERE "initiative_id" = "initiative_id_v"
jbe@339:         LOOP
jbe@339:           UPDATE "suggestion" SET
jbe@339:             "minus2_unfulfilled_count" = (
jbe@339:               SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@339:               FROM "issue" CROSS JOIN "opinion"
jbe@339:               JOIN "direct_interest_snapshot" AS "snapshot"
jbe@339:               ON "snapshot"."issue_id" = "issue"."id"
jbe@339:               AND "snapshot"."event" = "issue"."latest_snapshot_event"
jbe@339:               AND "snapshot"."member_id" = "opinion"."member_id"
jbe@339:               WHERE "issue"."id" = "issue_id_p"
jbe@339:               AND "opinion"."suggestion_id" = "suggestion_id_v"
jbe@339:               AND "opinion"."degree" = -2
jbe@339:               AND "opinion"."fulfilled" = FALSE
jbe@339:             ),
jbe@339:             "minus2_fulfilled_count" = (
jbe@339:               SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@339:               FROM "issue" CROSS JOIN "opinion"
jbe@339:               JOIN "direct_interest_snapshot" AS "snapshot"
jbe@339:               ON "snapshot"."issue_id" = "issue"."id"
jbe@339:               AND "snapshot"."event" = "issue"."latest_snapshot_event"
jbe@339:               AND "snapshot"."member_id" = "opinion"."member_id"
jbe@339:               WHERE "issue"."id" = "issue_id_p"
jbe@339:               AND "opinion"."suggestion_id" = "suggestion_id_v"
jbe@339:               AND "opinion"."degree" = -2
jbe@339:               AND "opinion"."fulfilled" = TRUE
jbe@339:             ),
jbe@339:             "minus1_unfulfilled_count" = (
jbe@339:               SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@339:               FROM "issue" CROSS JOIN "opinion"
jbe@339:               JOIN "direct_interest_snapshot" AS "snapshot"
jbe@339:               ON "snapshot"."issue_id" = "issue"."id"
jbe@339:               AND "snapshot"."event" = "issue"."latest_snapshot_event"
jbe@339:               AND "snapshot"."member_id" = "opinion"."member_id"
jbe@339:               WHERE "issue"."id" = "issue_id_p"
jbe@339:               AND "opinion"."suggestion_id" = "suggestion_id_v"
jbe@339:               AND "opinion"."degree" = -1
jbe@339:               AND "opinion"."fulfilled" = FALSE
jbe@339:             ),
jbe@339:             "minus1_fulfilled_count" = (
jbe@339:               SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@339:               FROM "issue" CROSS JOIN "opinion"
jbe@339:               JOIN "direct_interest_snapshot" AS "snapshot"
jbe@339:               ON "snapshot"."issue_id" = "issue"."id"
jbe@339:               AND "snapshot"."event" = "issue"."latest_snapshot_event"
jbe@339:               AND "snapshot"."member_id" = "opinion"."member_id"
jbe@339:               WHERE "issue"."id" = "issue_id_p"
jbe@339:               AND "opinion"."suggestion_id" = "suggestion_id_v"
jbe@339:               AND "opinion"."degree" = -1
jbe@339:               AND "opinion"."fulfilled" = TRUE
jbe@339:             ),
jbe@339:             "plus1_unfulfilled_count" = (
jbe@339:               SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@339:               FROM "issue" CROSS JOIN "opinion"
jbe@339:               JOIN "direct_interest_snapshot" AS "snapshot"
jbe@339:               ON "snapshot"."issue_id" = "issue"."id"
jbe@339:               AND "snapshot"."event" = "issue"."latest_snapshot_event"
jbe@339:               AND "snapshot"."member_id" = "opinion"."member_id"
jbe@339:               WHERE "issue"."id" = "issue_id_p"
jbe@339:               AND "opinion"."suggestion_id" = "suggestion_id_v"
jbe@339:               AND "opinion"."degree" = 1
jbe@339:               AND "opinion"."fulfilled" = FALSE
jbe@339:             ),
jbe@339:             "plus1_fulfilled_count" = (
jbe@339:               SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@339:               FROM "issue" CROSS JOIN "opinion"
jbe@339:               JOIN "direct_interest_snapshot" AS "snapshot"
jbe@339:               ON "snapshot"."issue_id" = "issue"."id"
jbe@339:               AND "snapshot"."event" = "issue"."latest_snapshot_event"
jbe@339:               AND "snapshot"."member_id" = "opinion"."member_id"
jbe@339:               WHERE "issue"."id" = "issue_id_p"
jbe@339:               AND "opinion"."suggestion_id" = "suggestion_id_v"
jbe@339:               AND "opinion"."degree" = 1
jbe@339:               AND "opinion"."fulfilled" = TRUE
jbe@339:             ),
jbe@339:             "plus2_unfulfilled_count" = (
jbe@339:               SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@339:               FROM "issue" CROSS JOIN "opinion"
jbe@339:               JOIN "direct_interest_snapshot" AS "snapshot"
jbe@339:               ON "snapshot"."issue_id" = "issue"."id"
jbe@339:               AND "snapshot"."event" = "issue"."latest_snapshot_event"
jbe@339:               AND "snapshot"."member_id" = "opinion"."member_id"
jbe@339:               WHERE "issue"."id" = "issue_id_p"
jbe@339:               AND "opinion"."suggestion_id" = "suggestion_id_v"
jbe@339:               AND "opinion"."degree" = 2
jbe@339:               AND "opinion"."fulfilled" = FALSE
jbe@339:             ),
jbe@339:             "plus2_fulfilled_count" = (
jbe@339:               SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@339:               FROM "issue" CROSS JOIN "opinion"
jbe@339:               JOIN "direct_interest_snapshot" AS "snapshot"
jbe@339:               ON "snapshot"."issue_id" = "issue"."id"
jbe@339:               AND "snapshot"."event" = "issue"."latest_snapshot_event"
jbe@339:               AND "snapshot"."member_id" = "opinion"."member_id"
jbe@339:               WHERE "issue"."id" = "issue_id_p"
jbe@339:               AND "opinion"."suggestion_id" = "suggestion_id_v"
jbe@339:               AND "opinion"."degree" = 2
jbe@339:               AND "opinion"."fulfilled" = TRUE
jbe@339:             )
jbe@339:             WHERE "suggestion"."id" = "suggestion_id_v";
jbe@339:         END LOOP;
jbe@339:       END LOOP;
jbe@339:       RETURN;
jbe@339:     END;
jbe@339:   $$;
jbe@339: 
jbe@339: CREATE OR REPLACE FUNCTION "set_snapshot_event"
jbe@339:   ( "issue_id_p" "issue"."id"%TYPE,
jbe@339:     "event_p" "snapshot_event" )
jbe@339:   RETURNS VOID
jbe@339:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@339:     DECLARE
jbe@339:       "event_v" "issue"."latest_snapshot_event"%TYPE;
jbe@339:     BEGIN
jbe@339:       PERFORM "require_transaction_isolation"();
jbe@339:       SELECT "latest_snapshot_event" INTO "event_v" FROM "issue"
jbe@339:         WHERE "id" = "issue_id_p" FOR UPDATE;
jbe@339:       UPDATE "issue" SET "latest_snapshot_event" = "event_p"
jbe@339:         WHERE "id" = "issue_id_p";
jbe@339:       UPDATE "direct_population_snapshot" SET "event" = "event_p"
jbe@339:         WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
jbe@339:       UPDATE "delegating_population_snapshot" SET "event" = "event_p"
jbe@339:         WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
jbe@339:       UPDATE "direct_interest_snapshot" SET "event" = "event_p"
jbe@339:         WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
jbe@339:       UPDATE "delegating_interest_snapshot" SET "event" = "event_p"
jbe@339:         WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
jbe@339:       UPDATE "direct_supporter_snapshot" SET "event" = "event_p"
jbe@339:         FROM "initiative"  -- NOTE: due to missing index on issue_id
jbe@339:         WHERE "initiative"."issue_id" = "issue_id_p"
jbe@339:         AND "direct_supporter_snapshot"."initiative_id" = "initiative"."id"
jbe@339:         AND "direct_supporter_snapshot"."event" = "event_v";
jbe@339:       RETURN;
jbe@339:     END;
jbe@339:   $$;
jbe@339: 
jbe@339: DROP FUNCTION "freeze_after_snapshot"("issue"."id"%TYPE);
jbe@339: DROP FUNCTION "manual_freeze"("issue"."id"%TYPE);
jbe@339: 
jbe@339: CREATE OR REPLACE FUNCTION "weight_of_added_vote_delegations"
jbe@339:   ( "issue_id_p"            "issue"."id"%TYPE,
jbe@339:     "member_id_p"           "member"."id"%TYPE,
jbe@339:     "delegate_member_ids_p" "delegating_voter"."delegate_member_ids"%TYPE )
jbe@339:   RETURNS "direct_voter"."weight"%TYPE
jbe@339:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@339:     DECLARE
jbe@339:       "issue_delegation_row"  "issue_delegation"%ROWTYPE;
jbe@339:       "delegate_member_ids_v" "delegating_voter"."delegate_member_ids"%TYPE;
jbe@339:       "weight_v"              INT4;
jbe@339:       "sub_weight_v"          INT4;
jbe@339:     BEGIN
jbe@339:       PERFORM "require_transaction_isolation"();
jbe@339:       "weight_v" := 0;
jbe@339:       FOR "issue_delegation_row" IN
jbe@339:         SELECT * FROM "issue_delegation"
jbe@339:         WHERE "trustee_id" = "member_id_p"
jbe@339:         AND "issue_id" = "issue_id_p"
jbe@339:       LOOP
jbe@339:         IF NOT EXISTS (
jbe@339:           SELECT NULL FROM "direct_voter"
jbe@339:           WHERE "member_id" = "issue_delegation_row"."truster_id"
jbe@339:           AND "issue_id" = "issue_id_p"
jbe@339:         ) AND NOT EXISTS (
jbe@339:           SELECT NULL FROM "delegating_voter"
jbe@339:           WHERE "member_id" = "issue_delegation_row"."truster_id"
jbe@339:           AND "issue_id" = "issue_id_p"
jbe@339:         ) THEN
jbe@339:           "delegate_member_ids_v" :=
jbe@339:             "member_id_p" || "delegate_member_ids_p";
jbe@339:           INSERT INTO "delegating_voter" (
jbe@339:               "issue_id",
jbe@339:               "member_id",
jbe@339:               "scope",
jbe@339:               "delegate_member_ids"
jbe@339:             ) VALUES (
jbe@339:               "issue_id_p",
jbe@339:               "issue_delegation_row"."truster_id",
jbe@339:               "issue_delegation_row"."scope",
jbe@339:               "delegate_member_ids_v"
jbe@339:             );
jbe@339:           "sub_weight_v" := 1 +
jbe@339:             "weight_of_added_vote_delegations"(
jbe@339:               "issue_id_p",
jbe@339:               "issue_delegation_row"."truster_id",
jbe@339:               "delegate_member_ids_v"
jbe@339:             );
jbe@339:           UPDATE "delegating_voter"
jbe@339:             SET "weight" = "sub_weight_v"
jbe@339:             WHERE "issue_id" = "issue_id_p"
jbe@339:             AND "member_id" = "issue_delegation_row"."truster_id";
jbe@339:           "weight_v" := "weight_v" + "sub_weight_v";
jbe@339:         END IF;
jbe@339:       END LOOP;
jbe@339:       RETURN "weight_v";
jbe@339:     END;
jbe@339:   $$;
jbe@339: 
jbe@339: CREATE OR REPLACE FUNCTION "add_vote_delegations"
jbe@339:   ( "issue_id_p" "issue"."id"%TYPE )
jbe@339:   RETURNS VOID
jbe@339:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@339:     DECLARE
jbe@339:       "member_id_v" "member"."id"%TYPE;
jbe@339:     BEGIN
jbe@339:       PERFORM "require_transaction_isolation"();
jbe@339:       FOR "member_id_v" IN
jbe@339:         SELECT "member_id" FROM "direct_voter"
jbe@339:         WHERE "issue_id" = "issue_id_p"
jbe@339:       LOOP
jbe@339:         UPDATE "direct_voter" SET
jbe@339:           "weight" = "weight" + "weight_of_added_vote_delegations"(
jbe@339:             "issue_id_p",
jbe@339:             "member_id_v",
jbe@339:             '{}'
jbe@339:           )
jbe@339:           WHERE "member_id" = "member_id_v"
jbe@339:           AND "issue_id" = "issue_id_p";
jbe@339:       END LOOP;
jbe@339:       RETURN;
jbe@339:     END;
jbe@339:   $$;
jbe@339: 
jbe@339: CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
jbe@339:   RETURNS VOID
jbe@339:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@339:     DECLARE
jbe@339:       "area_id_v"   "area"."id"%TYPE;
jbe@339:       "unit_id_v"   "unit"."id"%TYPE;
jbe@339:       "member_id_v" "member"."id"%TYPE;
jbe@339:     BEGIN
jbe@339:       PERFORM "require_transaction_isolation"();
jbe@339:       SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";
jbe@339:       SELECT "unit_id" INTO "unit_id_v" FROM "area"  WHERE "id" = "area_id_v";
jbe@339:       -- delete timestamp of voting comment:
jbe@339:       UPDATE "direct_voter" SET "comment_changed" = NULL
jbe@339:         WHERE "issue_id" = "issue_id_p";
jbe@339:       -- delete delegating votes (in cases of manual reset of issue state):
jbe@339:       DELETE FROM "delegating_voter"
jbe@339:         WHERE "issue_id" = "issue_id_p";
jbe@339:       -- delete votes from non-privileged voters:
jbe@339:       DELETE FROM "direct_voter"
jbe@339:         USING (
jbe@339:           SELECT
jbe@339:             "direct_voter"."member_id"
jbe@339:           FROM "direct_voter"
jbe@339:           JOIN "member" ON "direct_voter"."member_id" = "member"."id"
jbe@339:           LEFT JOIN "privilege"
jbe@339:           ON "privilege"."unit_id" = "unit_id_v"
jbe@339:           AND "privilege"."member_id" = "direct_voter"."member_id"
jbe@339:           WHERE "direct_voter"."issue_id" = "issue_id_p" AND (
jbe@339:             "member"."active" = FALSE OR
jbe@339:             "privilege"."voting_right" ISNULL OR
jbe@339:             "privilege"."voting_right" = FALSE
jbe@339:           )
jbe@339:         ) AS "subquery"
jbe@339:         WHERE "direct_voter"."issue_id" = "issue_id_p"
jbe@339:         AND "direct_voter"."member_id" = "subquery"."member_id";
jbe@339:       -- consider delegations:
jbe@339:       UPDATE "direct_voter" SET "weight" = 1
jbe@339:         WHERE "issue_id" = "issue_id_p";
jbe@339:       PERFORM "add_vote_delegations"("issue_id_p");
jbe@339:       -- materialize battle_view:
jbe@339:       -- NOTE: "closed" column of issue must be set at this point
jbe@339:       DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
jbe@339:       INSERT INTO "battle" (
jbe@339:         "issue_id",
jbe@339:         "winning_initiative_id", "losing_initiative_id",
jbe@339:         "count"
jbe@339:       ) SELECT
jbe@339:         "issue_id",
jbe@339:         "winning_initiative_id", "losing_initiative_id",
jbe@339:         "count"
jbe@339:         FROM "battle_view" WHERE "issue_id" = "issue_id_p";
jbe@339:       -- set voter count:
jbe@339:       UPDATE "issue" SET
jbe@339:         "voter_count" = (
jbe@339:           SELECT coalesce(sum("weight"), 0)
jbe@339:           FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
jbe@339:         )
jbe@339:         WHERE "id" = "issue_id_p";
jbe@339:       -- copy "positive_votes" and "negative_votes" from "battle" table:
jbe@339:       UPDATE "initiative" SET
jbe@339:         "positive_votes" = "battle_win"."count",
jbe@339:         "negative_votes" = "battle_lose"."count"
jbe@339:         FROM "battle" AS "battle_win", "battle" AS "battle_lose"
jbe@339:         WHERE
jbe@339:           "battle_win"."issue_id" = "issue_id_p" AND
jbe@339:           "battle_win"."winning_initiative_id" = "initiative"."id" AND
jbe@339:           "battle_win"."losing_initiative_id" ISNULL AND
jbe@339:           "battle_lose"."issue_id" = "issue_id_p" AND
jbe@339:           "battle_lose"."losing_initiative_id" = "initiative"."id" AND
jbe@339:           "battle_lose"."winning_initiative_id" ISNULL;
jbe@339:     END;
jbe@339:   $$;
jbe@339: 
jbe@339: CREATE OR REPLACE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE)
jbe@339:   RETURNS VOID
jbe@339:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@339:     DECLARE
jbe@339:       "issue_row"         "issue"%ROWTYPE;
jbe@339:       "policy_row"        "policy"%ROWTYPE;
jbe@339:       "dimension_v"       INTEGER;
jbe@339:       "vote_matrix"       INT4[][];  -- absolute votes
jbe@339:       "matrix"            INT8[][];  -- defeat strength / best paths
jbe@339:       "i"                 INTEGER;
jbe@339:       "j"                 INTEGER;
jbe@339:       "k"                 INTEGER;
jbe@339:       "battle_row"        "battle"%ROWTYPE;
jbe@339:       "rank_ary"          INT4[];
jbe@339:       "rank_v"            INT4;
jbe@339:       "done_v"            INTEGER;
jbe@339:       "winners_ary"       INTEGER[];
jbe@339:       "initiative_id_v"   "initiative"."id"%TYPE;
jbe@339:     BEGIN
jbe@339:       PERFORM "require_transaction_isolation"();
jbe@339:       SELECT * INTO "issue_row"
jbe@339:         FROM "issue" WHERE "id" = "issue_id_p";
jbe@339:       SELECT * INTO "policy_row"
jbe@339:         FROM "policy" WHERE "id" = "issue_row"."policy_id";
jbe@339:       SELECT count(1) INTO "dimension_v"
jbe@339:         FROM "battle_participant" WHERE "issue_id" = "issue_id_p";
jbe@339:       -- Create "vote_matrix" with absolute number of votes in pairwise
jbe@339:       -- comparison:
jbe@339:       "vote_matrix" := array_fill(NULL::INT4, ARRAY["dimension_v", "dimension_v"]);
jbe@339:       "i" := 1;
jbe@339:       "j" := 2;
jbe@339:       FOR "battle_row" IN
jbe@339:         SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p"
jbe@339:         ORDER BY
jbe@339:         "winning_initiative_id" NULLS LAST,
jbe@339:         "losing_initiative_id" NULLS LAST
jbe@339:       LOOP
jbe@339:         "vote_matrix"["i"]["j"] := "battle_row"."count";
jbe@339:         IF "j" = "dimension_v" THEN
jbe@339:           "i" := "i" + 1;
jbe@339:           "j" := 1;
jbe@339:         ELSE
jbe@339:           "j" := "j" + 1;
jbe@339:           IF "j" = "i" THEN
jbe@339:             "j" := "j" + 1;
jbe@339:           END IF;
jbe@339:         END IF;
jbe@339:       END LOOP;
jbe@339:       IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN
jbe@339:         RAISE EXCEPTION 'Wrong battle count (should not happen)';
jbe@339:       END IF;
jbe@339:       -- Store defeat strengths in "matrix" using "defeat_strength"
jbe@339:       -- function:
jbe@339:       "matrix" := array_fill(NULL::INT8, ARRAY["dimension_v", "dimension_v"]);
jbe@339:       "i" := 1;
jbe@339:       LOOP
jbe@339:         "j" := 1;
jbe@339:         LOOP
jbe@339:           IF "i" != "j" THEN
jbe@339:             "matrix"["i"]["j"] := "defeat_strength"(
jbe@339:               "vote_matrix"["i"]["j"],
jbe@339:               "vote_matrix"["j"]["i"]
jbe@339:             );
jbe@339:           END IF;
jbe@339:           EXIT WHEN "j" = "dimension_v";
jbe@339:           "j" := "j" + 1;
jbe@339:         END LOOP;
jbe@339:         EXIT WHEN "i" = "dimension_v";
jbe@339:         "i" := "i" + 1;
jbe@339:       END LOOP;
jbe@339:       -- Find best paths:
jbe@339:       "i" := 1;
jbe@339:       LOOP
jbe@339:         "j" := 1;
jbe@339:         LOOP
jbe@339:           IF "i" != "j" THEN
jbe@339:             "k" := 1;
jbe@339:             LOOP
jbe@339:               IF "i" != "k" AND "j" != "k" THEN
jbe@339:                 IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN
jbe@339:                   IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN
jbe@339:                     "matrix"["j"]["k"] := "matrix"["j"]["i"];
jbe@339:                   END IF;
jbe@339:                 ELSE
jbe@339:                   IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN
jbe@339:                     "matrix"["j"]["k"] := "matrix"["i"]["k"];
jbe@339:                   END IF;
jbe@339:                 END IF;
jbe@339:               END IF;
jbe@339:               EXIT WHEN "k" = "dimension_v";
jbe@339:               "k" := "k" + 1;
jbe@339:             END LOOP;
jbe@339:           END IF;
jbe@339:           EXIT WHEN "j" = "dimension_v";
jbe@339:           "j" := "j" + 1;
jbe@339:         END LOOP;
jbe@339:         EXIT WHEN "i" = "dimension_v";
jbe@339:         "i" := "i" + 1;
jbe@339:       END LOOP;
jbe@339:       -- Determine order of winners:
jbe@339:       "rank_ary" := array_fill(NULL::INT4, ARRAY["dimension_v"]);
jbe@339:       "rank_v" := 1;
jbe@339:       "done_v" := 0;
jbe@339:       LOOP
jbe@339:         "winners_ary" := '{}';
jbe@339:         "i" := 1;
jbe@339:         LOOP
jbe@339:           IF "rank_ary"["i"] ISNULL THEN
jbe@339:             "j" := 1;
jbe@339:             LOOP
jbe@339:               IF
jbe@339:                 "i" != "j" AND
jbe@339:                 "rank_ary"["j"] ISNULL AND
jbe@339:                 "matrix"["j"]["i"] > "matrix"["i"]["j"]
jbe@339:               THEN
jbe@339:                 -- someone else is better
jbe@339:                 EXIT;
jbe@339:               END IF;
jbe@339:               IF "j" = "dimension_v" THEN
jbe@339:                 -- noone is better
jbe@339:                 "winners_ary" := "winners_ary" || "i";
jbe@339:                 EXIT;
jbe@339:               END IF;
jbe@339:               "j" := "j" + 1;
jbe@339:             END LOOP;
jbe@339:           END IF;
jbe@339:           EXIT WHEN "i" = "dimension_v";
jbe@339:           "i" := "i" + 1;
jbe@339:         END LOOP;
jbe@339:         "i" := 1;
jbe@339:         LOOP
jbe@339:           "rank_ary"["winners_ary"["i"]] := "rank_v";
jbe@339:           "done_v" := "done_v" + 1;
jbe@339:           EXIT WHEN "i" = array_upper("winners_ary", 1);
jbe@339:           "i" := "i" + 1;
jbe@339:         END LOOP;
jbe@339:         EXIT WHEN "done_v" = "dimension_v";
jbe@339:         "rank_v" := "rank_v" + 1;
jbe@339:       END LOOP;
jbe@339:       -- write preliminary results:
jbe@339:       "i" := 1;
jbe@339:       FOR "initiative_id_v" IN
jbe@339:         SELECT "id" FROM "initiative"
jbe@339:         WHERE "issue_id" = "issue_id_p" AND "admitted"
jbe@339:         ORDER BY "id"
jbe@339:       LOOP
jbe@339:         UPDATE "initiative" SET
jbe@339:           "direct_majority" =
jbe@339:             CASE WHEN "policy_row"."direct_majority_strict" THEN
jbe@339:               "positive_votes" * "policy_row"."direct_majority_den" >
jbe@339:               "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")
jbe@339:             ELSE
jbe@339:               "positive_votes" * "policy_row"."direct_majority_den" >=
jbe@339:               "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")
jbe@339:             END
jbe@339:             AND "positive_votes" >= "policy_row"."direct_majority_positive"
jbe@339:             AND "issue_row"."voter_count"-"negative_votes" >=
jbe@339:                 "policy_row"."direct_majority_non_negative",
jbe@339:             "indirect_majority" =
jbe@339:             CASE WHEN "policy_row"."indirect_majority_strict" THEN
jbe@339:               "positive_votes" * "policy_row"."indirect_majority_den" >
jbe@339:               "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")
jbe@339:             ELSE
jbe@339:               "positive_votes" * "policy_row"."indirect_majority_den" >=
jbe@339:               "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")
jbe@339:             END
jbe@339:             AND "positive_votes" >= "policy_row"."indirect_majority_positive"
jbe@339:             AND "issue_row"."voter_count"-"negative_votes" >=
jbe@339:                 "policy_row"."indirect_majority_non_negative",
jbe@339:           "schulze_rank"           = "rank_ary"["i"],
jbe@339:           "better_than_status_quo" = "rank_ary"["i"] < "rank_ary"["dimension_v"],
jbe@339:           "worse_than_status_quo"  = "rank_ary"["i"] > "rank_ary"["dimension_v"],
jbe@339:           "multistage_majority"    = "rank_ary"["i"] >= "rank_ary"["dimension_v"],
jbe@339:           "reverse_beat_path"      = "matrix"["dimension_v"]["i"] >= 0,
jbe@339:           "eligible"               = FALSE,
jbe@339:           "winner"                 = FALSE,
jbe@339:           "rank"                   = NULL  -- NOTE: in cases of manual reset of issue state
jbe@339:           WHERE "id" = "initiative_id_v";
jbe@339:         "i" := "i" + 1;
jbe@339:       END LOOP;
jbe@339:       IF "i" != "dimension_v" THEN
jbe@339:         RAISE EXCEPTION 'Wrong winner count (should not happen)';
jbe@339:       END IF;
jbe@339:       -- take indirect majorities into account:
jbe@339:       LOOP
jbe@339:         UPDATE "initiative" SET "indirect_majority" = TRUE
jbe@339:           FROM (
jbe@339:             SELECT "new_initiative"."id" AS "initiative_id"
jbe@339:             FROM "initiative" "old_initiative"
jbe@339:             JOIN "initiative" "new_initiative"
jbe@339:               ON "new_initiative"."issue_id" = "issue_id_p"
jbe@339:               AND "new_initiative"."indirect_majority" = FALSE
jbe@339:             JOIN "battle" "battle_win"
jbe@339:               ON "battle_win"."issue_id" = "issue_id_p"
jbe@339:               AND "battle_win"."winning_initiative_id" = "new_initiative"."id"
jbe@339:               AND "battle_win"."losing_initiative_id" = "old_initiative"."id"
jbe@339:             JOIN "battle" "battle_lose"
jbe@339:               ON "battle_lose"."issue_id" = "issue_id_p"
jbe@339:               AND "battle_lose"."losing_initiative_id" = "new_initiative"."id"
jbe@339:               AND "battle_lose"."winning_initiative_id" = "old_initiative"."id"
jbe@339:             WHERE "old_initiative"."issue_id" = "issue_id_p"
jbe@339:             AND "old_initiative"."indirect_majority" = TRUE
jbe@339:             AND CASE WHEN "policy_row"."indirect_majority_strict" THEN
jbe@339:               "battle_win"."count" * "policy_row"."indirect_majority_den" >
jbe@339:               "policy_row"."indirect_majority_num" *
jbe@339:               ("battle_win"."count"+"battle_lose"."count")
jbe@339:             ELSE
jbe@339:               "battle_win"."count" * "policy_row"."indirect_majority_den" >=
jbe@339:               "policy_row"."indirect_majority_num" *
jbe@339:               ("battle_win"."count"+"battle_lose"."count")
jbe@339:             END
jbe@339:             AND "battle_win"."count" >= "policy_row"."indirect_majority_positive"
jbe@339:             AND "issue_row"."voter_count"-"battle_lose"."count" >=
jbe@339:                 "policy_row"."indirect_majority_non_negative"
jbe@339:           ) AS "subquery"
jbe@339:           WHERE "id" = "subquery"."initiative_id";
jbe@339:         EXIT WHEN NOT FOUND;
jbe@339:       END LOOP;
jbe@339:       -- set "multistage_majority" for remaining matching initiatives:
jbe@339:       UPDATE "initiative" SET "multistage_majority" = TRUE
jbe@339:         FROM (
jbe@339:           SELECT "losing_initiative"."id" AS "initiative_id"
jbe@339:           FROM "initiative" "losing_initiative"
jbe@339:           JOIN "initiative" "winning_initiative"
jbe@339:             ON "winning_initiative"."issue_id" = "issue_id_p"
jbe@339:             AND "winning_initiative"."admitted"
jbe@339:           JOIN "battle" "battle_win"
jbe@339:             ON "battle_win"."issue_id" = "issue_id_p"
jbe@339:             AND "battle_win"."winning_initiative_id" = "winning_initiative"."id"
jbe@339:             AND "battle_win"."losing_initiative_id" = "losing_initiative"."id"
jbe@339:           JOIN "battle" "battle_lose"
jbe@339:             ON "battle_lose"."issue_id" = "issue_id_p"
jbe@339:             AND "battle_lose"."losing_initiative_id" = "winning_initiative"."id"
jbe@339:             AND "battle_lose"."winning_initiative_id" = "losing_initiative"."id"
jbe@339:           WHERE "losing_initiative"."issue_id" = "issue_id_p"
jbe@339:           AND "losing_initiative"."admitted"
jbe@339:           AND "winning_initiative"."schulze_rank" <
jbe@339:               "losing_initiative"."schulze_rank"
jbe@339:           AND "battle_win"."count" > "battle_lose"."count"
jbe@339:           AND (
jbe@339:             "battle_win"."count" > "winning_initiative"."positive_votes" OR
jbe@339:             "battle_lose"."count" < "losing_initiative"."negative_votes" )
jbe@339:         ) AS "subquery"
jbe@339:         WHERE "id" = "subquery"."initiative_id";
jbe@339:       -- mark eligible initiatives:
jbe@339:       UPDATE "initiative" SET "eligible" = TRUE
jbe@339:         WHERE "issue_id" = "issue_id_p"
jbe@339:         AND "initiative"."direct_majority"
jbe@339:         AND "initiative"."indirect_majority"
jbe@339:         AND "initiative"."better_than_status_quo"
jbe@339:         AND (
jbe@339:           "policy_row"."no_multistage_majority" = FALSE OR
jbe@339:           "initiative"."multistage_majority" = FALSE )
jbe@339:         AND (
jbe@339:           "policy_row"."no_reverse_beat_path" = FALSE OR
jbe@339:           "initiative"."reverse_beat_path" = FALSE );
jbe@339:       -- mark final winner:
jbe@339:       UPDATE "initiative" SET "winner" = TRUE
jbe@339:         FROM (
jbe@339:           SELECT "id" AS "initiative_id"
jbe@339:           FROM "initiative"
jbe@339:           WHERE "issue_id" = "issue_id_p" AND "eligible"
jbe@339:           ORDER BY
jbe@339:             "schulze_rank",
jbe@339:             "id"
jbe@339:           LIMIT 1
jbe@339:         ) AS "subquery"
jbe@339:         WHERE "id" = "subquery"."initiative_id";
jbe@339:       -- write (final) ranks:
jbe@339:       "rank_v" := 1;
jbe@339:       FOR "initiative_id_v" IN
jbe@339:         SELECT "id"
jbe@339:         FROM "initiative"
jbe@339:         WHERE "issue_id" = "issue_id_p" AND "admitted"
jbe@339:         ORDER BY
jbe@339:           "winner" DESC,
jbe@339:           "eligible" DESC,
jbe@339:           "schulze_rank",
jbe@339:           "id"
jbe@339:       LOOP
jbe@339:         UPDATE "initiative" SET "rank" = "rank_v"
jbe@339:           WHERE "id" = "initiative_id_v";
jbe@339:         "rank_v" := "rank_v" + 1;
jbe@339:       END LOOP;
jbe@339:       -- set schulze rank of status quo and mark issue as finished:
jbe@339:       UPDATE "issue" SET
jbe@339:         "status_quo_schulze_rank" = "rank_ary"["dimension_v"],
jbe@339:         "state" =
jbe@339:           CASE WHEN EXISTS (
jbe@339:             SELECT NULL FROM "initiative"
jbe@339:             WHERE "issue_id" = "issue_id_p" AND "winner"
jbe@339:           ) THEN
jbe@339:             'finished_with_winner'::"issue_state"
jbe@339:           ELSE
jbe@339:             'finished_without_winner'::"issue_state"
jbe@339:           END,
jbe@339:         "closed" = "phase_finished",
jbe@339:         "phase_finished" = NULL
jbe@339:         WHERE "id" = "issue_id_p";
jbe@339:       RETURN;
jbe@339:     END;
jbe@339:   $$;
jbe@339: 
jbe@339: DROP FUNCTION "check_issue"("issue"."id"%TYPE);
jbe@339: 
jbe@339: CREATE TYPE "check_issue_persistence" AS (
jbe@339:         "state"                 "issue_state",
jbe@339:         "phase_finished"        BOOLEAN,
jbe@339:         "issue_revoked"         BOOLEAN,
jbe@339:         "snapshot_created"      BOOLEAN,
jbe@339:         "harmonic_weights_set"  BOOLEAN,
jbe@339:         "closed_voting"         BOOLEAN );
jbe@339: 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: 
jbe@339: CREATE FUNCTION "check_issue"
jbe@339:   ( "issue_id_p" "issue"."id"%TYPE,
jbe@339:     "persist"    "check_issue_persistence" )
jbe@339:   RETURNS "check_issue_persistence"
jbe@339:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@339:     DECLARE
jbe@339:       "issue_row"      "issue"%ROWTYPE;
jbe@339:       "policy_row"     "policy"%ROWTYPE;
jbe@339:       "initiative_row" "initiative"%ROWTYPE;
jbe@339:       "state_v"        "issue_state";
jbe@339:     BEGIN
jbe@339:       PERFORM "require_transaction_isolation"();
jbe@339:       IF "persist" ISNULL THEN
jbe@339:         SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
jbe@339:           FOR UPDATE;
jbe@339:         IF "issue_row"."closed" NOTNULL THEN
jbe@339:           RETURN NULL;
jbe@339:         END IF;
jbe@339:         "persist"."state" := "issue_row"."state";
jbe@339:         IF
jbe@339:           ( "issue_row"."state" = 'admission' AND now() >=
jbe@339:             "issue_row"."created" + "issue_row"."admission_time" ) OR
jbe@339:           ( "issue_row"."state" = 'discussion' AND now() >=
jbe@339:             "issue_row"."accepted" + "issue_row"."discussion_time" ) OR
jbe@339:           ( "issue_row"."state" = 'verification' AND now() >=
jbe@339:             "issue_row"."half_frozen" + "issue_row"."verification_time" ) OR
jbe@339:           ( "issue_row"."state" = 'voting' AND now() >=
jbe@339:             "issue_row"."fully_frozen" + "issue_row"."voting_time" )
jbe@339:         THEN
jbe@339:           "persist"."phase_finished" := TRUE;
jbe@339:         ELSE
jbe@339:           "persist"."phase_finished" := FALSE;
jbe@339:         END IF;
jbe@339:         IF
jbe@339:           NOT EXISTS (
jbe@339:             -- all initiatives are revoked
jbe@339:             SELECT NULL FROM "initiative"
jbe@339:             WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
jbe@339:           ) AND (
jbe@339:             -- and issue has not been accepted yet
jbe@339:             "persist"."state" = 'admission' OR
jbe@339:             -- or verification time has elapsed
jbe@339:             ( "persist"."state" = 'verification' AND
jbe@339:               "persist"."phase_finished" ) OR
jbe@339:             -- or no initiatives have been revoked lately
jbe@339:             NOT EXISTS (
jbe@339:               SELECT NULL FROM "initiative"
jbe@339:               WHERE "issue_id" = "issue_id_p"
jbe@339:               AND now() < "revoked" + "issue_row"."verification_time"
jbe@339:             )
jbe@339:           )
jbe@339:         THEN
jbe@339:           "persist"."issue_revoked" := TRUE;
jbe@339:         ELSE
jbe@339:           "persist"."issue_revoked" := FALSE;
jbe@339:         END IF;
jbe@339:         IF "persist"."phase_finished" OR "persist"."issue_revoked" THEN
jbe@339:           UPDATE "issue" SET "phase_finished" = now()
jbe@339:             WHERE "id" = "issue_row"."id";
jbe@339:           RETURN "persist";
jbe@339:         ELSIF
jbe@339:           "persist"."state" IN ('admission', 'discussion', 'verification')
jbe@339:         THEN
jbe@339:           RETURN "persist";
jbe@339:         ELSE
jbe@339:           RETURN NULL;
jbe@339:         END IF;
jbe@339:       END IF;
jbe@339:       IF
jbe@339:         "persist"."state" IN ('admission', 'discussion', 'verification') AND
jbe@339:         coalesce("persist"."snapshot_created", FALSE) = FALSE
jbe@339:       THEN
jbe@339:         PERFORM "create_snapshot"("issue_id_p");
jbe@339:         "persist"."snapshot_created" = TRUE;
jbe@339:         IF "persist"."phase_finished" THEN
jbe@339:           IF "persist"."state" = 'admission' THEN
jbe@339:             PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
jbe@339:           ELSIF "persist"."state" = 'discussion' THEN
jbe@339:             PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze');
jbe@339:           ELSIF "persist"."state" = 'verification' THEN
jbe@339:             PERFORM "set_snapshot_event"("issue_id_p", 'full_freeze');
jbe@339:             SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
jbe@339:             SELECT * INTO "policy_row" FROM "policy"
jbe@339:               WHERE "id" = "issue_row"."policy_id";
jbe@339:             FOR "initiative_row" IN
jbe@339:               SELECT * FROM "initiative"
jbe@339:               WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
jbe@339:               FOR UPDATE
jbe@339:             LOOP
jbe@339:               IF
jbe@339:                 "initiative_row"."polling" OR (
jbe@339:                   "initiative_row"."satisfied_supporter_count" > 0 AND
jbe@339:                   "initiative_row"."satisfied_supporter_count" *
jbe@339:                   "policy_row"."initiative_quorum_den" >=
jbe@339:                   "issue_row"."population" * "policy_row"."initiative_quorum_num"
jbe@339:                 )
jbe@339:               THEN
jbe@339:                 UPDATE "initiative" SET "admitted" = TRUE
jbe@339:                   WHERE "id" = "initiative_row"."id";
jbe@339:               ELSE
jbe@339:                 UPDATE "initiative" SET "admitted" = FALSE
jbe@339:                   WHERE "id" = "initiative_row"."id";
jbe@339:               END IF;
jbe@339:             END LOOP;
jbe@339:           END IF;
jbe@339:         END IF;
jbe@339:         RETURN "persist";
jbe@339:       END IF;
jbe@339:       IF
jbe@339:         "persist"."state" IN ('admission', 'discussion', 'verification') AND
jbe@339:         coalesce("persist"."harmonic_weights_set", FALSE) = FALSE
jbe@339:       THEN
jbe@339:         PERFORM "set_harmonic_initiative_weights"("issue_id_p");
jbe@339:         "persist"."harmonic_weights_set" = TRUE;
jbe@339:         IF
jbe@339:           "persist"."phase_finished" OR
jbe@339:           "persist"."issue_revoked" OR
jbe@339:           "persist"."state" = 'admission'
jbe@339:         THEN
jbe@339:           RETURN "persist";
jbe@339:         ELSE
jbe@339:           RETURN NULL;
jbe@339:         END IF;
jbe@339:       END IF;
jbe@339:       IF "persist"."issue_revoked" THEN
jbe@339:         IF "persist"."state" = 'admission' THEN
jbe@339:           "state_v" := 'canceled_revoked_before_accepted';
jbe@339:         ELSIF "persist"."state" = 'discussion' THEN
jbe@339:           "state_v" := 'canceled_after_revocation_during_discussion';
jbe@339:         ELSIF "persist"."state" = 'verification' THEN
jbe@339:           "state_v" := 'canceled_after_revocation_during_verification';
jbe@339:         END IF;
jbe@339:         UPDATE "issue" SET
jbe@339:           "state"          = "state_v",
jbe@339:           "closed"         = "phase_finished",
jbe@339:           "phase_finished" = NULL
jbe@339:           WHERE "id" = "issue_id_p";
jbe@339:         RETURN NULL;
jbe@339:       END IF;
jbe@339:       IF "persist"."state" = 'admission' THEN
jbe@339:         SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
jbe@339:           FOR UPDATE;
jbe@339:         SELECT * INTO "policy_row"
jbe@339:           FROM "policy" WHERE "id" = "issue_row"."policy_id";
jbe@339:         IF EXISTS (
jbe@339:           SELECT NULL FROM "initiative"
jbe@339:           WHERE "issue_id" = "issue_id_p"
jbe@339:           AND "supporter_count" > 0
jbe@339:           AND "supporter_count" * "policy_row"."issue_quorum_den"
jbe@339:           >= "issue_row"."population" * "policy_row"."issue_quorum_num"
jbe@339:         ) THEN
jbe@339:           UPDATE "issue" SET
jbe@339:             "state"          = 'discussion',
jbe@339:             "accepted"       = coalesce("phase_finished", now()),
jbe@339:             "phase_finished" = NULL
jbe@339:             WHERE "id" = "issue_id_p";
jbe@339:         ELSIF "issue_row"."phase_finished" NOTNULL THEN
jbe@339:           UPDATE "issue" SET
jbe@339:             "state"          = 'canceled_issue_not_accepted',
jbe@339:             "closed"         = "phase_finished",
jbe@339:             "phase_finished" = NULL
jbe@339:             WHERE "id" = "issue_id_p";
jbe@339:         END IF;
jbe@339:         RETURN NULL;
jbe@339:       END IF;
jbe@339:       IF "persist"."phase_finished" THEN
jbe@339:         if "persist"."state" = 'discussion' THEN
jbe@339:           UPDATE "issue" SET
jbe@339:             "state"          = 'verification',
jbe@339:             "half_frozen"    = "phase_finished",
jbe@339:             "phase_finished" = NULL
jbe@339:             WHERE "id" = "issue_id_p";
jbe@339:           RETURN NULL;
jbe@339:         END IF;
jbe@339:         IF "persist"."state" = 'verification' THEN
jbe@339:           SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
jbe@339:             FOR UPDATE;
jbe@339:           SELECT * INTO "policy_row" FROM "policy"
jbe@339:             WHERE "id" = "issue_row"."policy_id";
jbe@339:           IF EXISTS (
jbe@339:             SELECT NULL FROM "initiative"
jbe@339:             WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE
jbe@339:           ) THEN
jbe@339:             UPDATE "issue" SET
jbe@343:               "state"          = 'voting',
jbe@343:               "fully_frozen"   = "phase_finished",
jbe@339:               "phase_finished" = NULL
jbe@339:               WHERE "id" = "issue_id_p";
jbe@339:           ELSE
jbe@339:             UPDATE "issue" SET
jbe@343:               "state"          = 'canceled_no_initiative_admitted',
jbe@343:               "fully_frozen"   = "phase_finished",
jbe@343:               "closed"         = "phase_finished",
jbe@343:               "phase_finished" = NULL
jbe@339:               WHERE "id" = "issue_id_p";
jbe@339:             -- NOTE: The following DELETE statements have effect only when
jbe@339:             --       issue state has been manipulated
jbe@339:             DELETE FROM "direct_voter"     WHERE "issue_id" = "issue_id_p";
jbe@339:             DELETE FROM "delegating_voter" WHERE "issue_id" = "issue_id_p";
jbe@339:             DELETE FROM "battle"           WHERE "issue_id" = "issue_id_p";
jbe@339:           END IF;
jbe@339:           RETURN NULL;
jbe@339:         END IF;
jbe@339:         IF "persist"."state" = 'voting' THEN
jbe@339:           IF coalesce("persist"."closed_voting", FALSE) = FALSE THEN
jbe@339:             PERFORM "close_voting"("issue_id_p");
jbe@339:             "persist"."closed_voting" = TRUE;
jbe@339:             RETURN "persist";
jbe@339:           END IF;
jbe@339:           PERFORM "calculate_ranks"("issue_id_p");
jbe@339:           RETURN NULL;
jbe@339:         END IF;
jbe@339:       END IF;
jbe@339:       RAISE WARNING 'should not happen';
jbe@339:       RETURN NULL;
jbe@339:     END;
jbe@339:   $$;
jbe@339: COMMENT ON FUNCTION "check_issue"
jbe@339:   ( "issue"."id"%TYPE,
jbe@339:     "check_issue_persistence" )
jbe@339:   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: 
jbe@339: CREATE OR REPLACE FUNCTION "check_everything"()
jbe@339:   RETURNS VOID
jbe@339:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@339:     DECLARE
jbe@339:       "issue_id_v" "issue"."id"%TYPE;
jbe@339:       "persist_v"  "check_issue_persistence";
jbe@339:     BEGIN
jbe@339:       RAISE WARNING 'Function "check_everything" should only be used for development and debugging purposes';
jbe@339:       DELETE FROM "expired_session";
jbe@339:       PERFORM "check_activity"();
jbe@339:       PERFORM "calculate_member_counts"();
jbe@339:       FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP
jbe@339:         "persist_v" := NULL;
jbe@339:         LOOP
jbe@339:           "persist_v" := "check_issue"("issue_id_v", "persist_v");
jbe@339:           EXIT WHEN "persist_v" ISNULL;
jbe@339:         END LOOP;
jbe@339:       END LOOP;
jbe@339:       RETURN;
jbe@339:     END;
jbe@339:   $$;
jbe@339: 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: 
jbe@339: CREATE OR REPLACE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE)
jbe@339:   RETURNS VOID
jbe@339:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@339:     DECLARE
jbe@339:       "issue_row" "issue"%ROWTYPE;
jbe@339:     BEGIN
jbe@339:       SELECT * INTO "issue_row"
jbe@339:         FROM "issue" WHERE "id" = "issue_id_p"
jbe@339:         FOR UPDATE;
jbe@339:       IF "issue_row"."cleaned" ISNULL THEN
jbe@339:         UPDATE "issue" SET
jbe@339:           "state"  = 'voting',
jbe@339:           "closed" = NULL
jbe@339:           WHERE "id" = "issue_id_p";
jbe@339:         DELETE FROM "delegating_voter"
jbe@339:           WHERE "issue_id" = "issue_id_p";
jbe@339:         DELETE FROM "direct_voter"
jbe@339:           WHERE "issue_id" = "issue_id_p";
jbe@339:         DELETE FROM "delegating_interest_snapshot"
jbe@339:           WHERE "issue_id" = "issue_id_p";
jbe@339:         DELETE FROM "direct_interest_snapshot"
jbe@339:           WHERE "issue_id" = "issue_id_p";
jbe@339:         DELETE FROM "delegating_population_snapshot"
jbe@339:           WHERE "issue_id" = "issue_id_p";
jbe@339:         DELETE FROM "direct_population_snapshot"
jbe@339:           WHERE "issue_id" = "issue_id_p";
jbe@339:         DELETE FROM "non_voter"
jbe@339:           WHERE "issue_id" = "issue_id_p";
jbe@339:         DELETE FROM "delegation"
jbe@339:           WHERE "issue_id" = "issue_id_p";
jbe@339:         DELETE FROM "supporter"
jbe@339:           USING "initiative"  -- NOTE: due to missing index on issue_id
jbe@339:           WHERE "initiative"."issue_id" = "issue_id_p"
jbe@339:           AND "supporter"."initiative_id" = "initiative_id";
jbe@339:         UPDATE "issue" SET
jbe@339:           "state"   = "issue_row"."state",
jbe@339:           "closed"  = "issue_row"."closed",
jbe@339:           "cleaned" = now()
jbe@339:           WHERE "id" = "issue_id_p";
jbe@339:       END IF;
jbe@339:       RETURN;
jbe@339:     END;
jbe@339:   $$;
jbe@339: 
jbe@339: COMMIT;




