jbe@67: BEGIN;
jbe@67: 
jbe@67: 
jbe@67: CREATE OR REPLACE VIEW "liquid_feedback_version" AS
jbe@67:   SELECT * FROM (VALUES ('1.2.5', 1, 2, 5))
jbe@67:    AS "subquery"("string", "major", "minor", "revision");
jbe@67: 
jbe@67: 
jbe@67: CREATE FUNCTION "share_row_lock_issue_trigger"()
jbe@67:   RETURNS TRIGGER
jbe@67:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@67:     BEGIN
jbe@67:       IF TG_OP = 'UPDATE' OR TG_OP = 'DELETE' THEN
jbe@67:         PERFORM NULL FROM "issue" WHERE "id" = OLD."issue_id" FOR SHARE;
jbe@67:       END IF;
jbe@67:       IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
jbe@67:         PERFORM NULL FROM "issue" WHERE "id" = NEW."issue_id" FOR SHARE;
jbe@67:         RETURN NEW;
jbe@67:       ELSE
jbe@67:         RETURN OLD;
jbe@67:       END IF;
jbe@67:     END;
jbe@67:   $$;
jbe@67: 
jbe@67: COMMENT ON FUNCTION "share_row_lock_issue_trigger"() IS 'Implementation of triggers "share_row_lock_issue" on multiple tables';
jbe@67: 
jbe@67: 
jbe@67: CREATE FUNCTION "share_row_lock_issue_via_initiative_trigger"()
jbe@67:   RETURNS TRIGGER
jbe@67:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@67:     BEGIN
jbe@67:       IF TG_OP = 'UPDATE' OR TG_OP = 'DELETE' THEN
jbe@67:         PERFORM NULL FROM "issue"
jbe@67:           JOIN "initiative" ON "issue"."id" = "initiative"."issue_id"
jbe@67:           WHERE "initiative"."id" = OLD."initiative_id"
jbe@67:           FOR SHARE OF "issue";
jbe@67:       END IF;
jbe@67:       IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
jbe@67:         PERFORM NULL FROM "issue"
jbe@67:           JOIN "initiative" ON "issue"."id" = "initiative"."issue_id"
jbe@67:           WHERE "initiative"."id" = NEW."initiative_id"
jbe@67:           FOR SHARE OF "issue";
jbe@67:         RETURN NEW;
jbe@67:       ELSE
jbe@67:         RETURN OLD;
jbe@67:       END IF;
jbe@67:     END;
jbe@67:   $$;
jbe@67: 
jbe@67: COMMENT ON FUNCTION "share_row_lock_issue_trigger"() IS 'Implementation of trigger "share_row_lock_issue_via_initiative" on table "opinion"';
jbe@67: 
jbe@67: 
jbe@67: CREATE TRIGGER "share_row_lock_issue"
jbe@67:   BEFORE INSERT OR UPDATE OR DELETE ON "initiative"
jbe@67:   FOR EACH ROW EXECUTE PROCEDURE
jbe@67:   "share_row_lock_issue_trigger"();
jbe@67: 
jbe@67: CREATE TRIGGER "share_row_lock_issue"
jbe@67:   BEFORE INSERT OR UPDATE OR DELETE ON "interest"
jbe@67:   FOR EACH ROW EXECUTE PROCEDURE
jbe@67:   "share_row_lock_issue_trigger"();
jbe@67: 
jbe@67: CREATE TRIGGER "share_row_lock_issue"
jbe@67:   BEFORE INSERT OR UPDATE OR DELETE ON "supporter"
jbe@67:   FOR EACH ROW EXECUTE PROCEDURE
jbe@67:   "share_row_lock_issue_trigger"();
jbe@67: 
jbe@67: CREATE TRIGGER "share_row_lock_issue_via_initiative"
jbe@67:   BEFORE INSERT OR UPDATE OR DELETE ON "opinion"
jbe@67:   FOR EACH ROW EXECUTE PROCEDURE
jbe@67:   "share_row_lock_issue_via_initiative_trigger"();
jbe@67: 
jbe@67: CREATE TRIGGER "share_row_lock_issue"
jbe@67:   BEFORE INSERT OR UPDATE OR DELETE ON "direct_voter"
jbe@67:   FOR EACH ROW EXECUTE PROCEDURE
jbe@67:   "share_row_lock_issue_trigger"();
jbe@67: 
jbe@67: CREATE TRIGGER "share_row_lock_issue"
jbe@67:   BEFORE INSERT OR UPDATE OR DELETE ON "delegating_voter"
jbe@67:   FOR EACH ROW EXECUTE PROCEDURE
jbe@67:   "share_row_lock_issue_trigger"();
jbe@67: 
jbe@67: CREATE TRIGGER "share_row_lock_issue"
jbe@67:   BEFORE INSERT OR UPDATE OR DELETE ON "vote"
jbe@67:   FOR EACH ROW EXECUTE PROCEDURE
jbe@67:   "share_row_lock_issue_trigger"();
jbe@67: 
jbe@67: COMMENT ON TRIGGER "share_row_lock_issue"                ON "initiative"       IS 'See "lock_issue" function';
jbe@67: COMMENT ON TRIGGER "share_row_lock_issue"                ON "interest"         IS 'See "lock_issue" function';
jbe@67: COMMENT ON TRIGGER "share_row_lock_issue"                ON "supporter"        IS 'See "lock_issue" function';
jbe@67: COMMENT ON TRIGGER "share_row_lock_issue_via_initiative" ON "opinion"          IS 'See "lock_issue" function';
jbe@67: COMMENT ON TRIGGER "share_row_lock_issue"                ON "direct_voter"     IS 'See "lock_issue" function';
jbe@67: COMMENT ON TRIGGER "share_row_lock_issue"                ON "delegating_voter" IS 'See "lock_issue" function';
jbe@67: COMMENT ON TRIGGER "share_row_lock_issue"                ON "vote"             IS 'See "lock_issue" function';
jbe@67: 
jbe@67: 
jbe@67: CREATE FUNCTION "lock_issue"
jbe@67:   ( "issue_id_p" "issue"."id"%TYPE )
jbe@67:   RETURNS VOID
jbe@67:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@67:     BEGIN
jbe@67:       LOCK TABLE "member"     IN SHARE MODE;
jbe@67:       LOCK TABLE "membership" IN SHARE MODE;
jbe@67:       LOCK TABLE "policy"     IN SHARE MODE;
jbe@67:       PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE;
jbe@67:       -- NOTE: The row-level exclusive lock in combination with the
jbe@67:       -- share_row_lock_issue(_via_initiative)_trigger functions (which
jbe@67:       -- acquire a row-level share lock on the issue) ensure that no data
jbe@67:       -- is changed, which could affect calculation of snapshots or
jbe@67:       -- counting of votes. Table "delegation" must be table-level-locked,
jbe@67:       -- as it also contains issue- and global-scope delegations.
jbe@67:       LOCK TABLE "delegation" IN SHARE MODE;
jbe@67:       LOCK TABLE "direct_population_snapshot"     IN EXCLUSIVE MODE;
jbe@67:       LOCK TABLE "delegating_population_snapshot" IN EXCLUSIVE MODE;
jbe@67:       LOCK TABLE "direct_interest_snapshot"       IN EXCLUSIVE MODE;
jbe@67:       LOCK TABLE "delegating_interest_snapshot"   IN EXCLUSIVE MODE;
jbe@67:       LOCK TABLE "direct_supporter_snapshot"      IN EXCLUSIVE MODE;
jbe@67:       RETURN;
jbe@67:     END;
jbe@67:   $$;
jbe@67: 
jbe@67: COMMENT ON FUNCTION "lock_issue"
jbe@67:   ( "issue"."id"%TYPE )
jbe@67:   IS 'Locks the issue and all other data which is used for calculating snapshots or counting votes.';
jbe@67: 
jbe@67: 
jbe@67: CREATE OR REPLACE FUNCTION "calculate_member_counts"()
jbe@67:   RETURNS VOID
jbe@67:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@67:     BEGIN
jbe@67:       LOCK TABLE "member"       IN SHARE MODE;
jbe@67:       LOCK TABLE "member_count" IN EXCLUSIVE MODE;
jbe@67:       LOCK TABLE "area"         IN EXCLUSIVE MODE;
jbe@67:       LOCK TABLE "membership"   IN SHARE MODE;
jbe@67:       DELETE FROM "member_count";
jbe@67:       INSERT INTO "member_count" ("total_count")
jbe@67:         SELECT "total_count" FROM "member_count_view";
jbe@67:       UPDATE "area" SET
jbe@67:         "direct_member_count" = "view"."direct_member_count",
jbe@67:         "member_weight"       = "view"."member_weight",
jbe@67:         "autoreject_weight"   = "view"."autoreject_weight"
jbe@67:         FROM "area_member_count" AS "view"
jbe@67:         WHERE "view"."area_id" = "area"."id";
jbe@67:       RETURN;
jbe@67:     END;
jbe@67:   $$;
jbe@67: 
jbe@67: CREATE OR REPLACE FUNCTION "create_snapshot"
jbe@67:   ( "issue_id_p" "issue"."id"%TYPE )
jbe@67:   RETURNS VOID
jbe@67:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@67:     DECLARE
jbe@67:       "initiative_id_v"    "initiative"."id"%TYPE;
jbe@67:       "suggestion_id_v"    "suggestion"."id"%TYPE;
jbe@67:     BEGIN
jbe@67:       PERFORM "lock_issue"("issue_id_p");
jbe@67:       PERFORM "create_population_snapshot"("issue_id_p");
jbe@67:       PERFORM "create_interest_snapshot"("issue_id_p");
jbe@67:       UPDATE "issue" SET
jbe@67:         "snapshot" = now(),
jbe@67:         "latest_snapshot_event" = 'periodic',
jbe@67:         "population" = (
jbe@67:           SELECT coalesce(sum("weight"), 0)
jbe@67:           FROM "direct_population_snapshot"
jbe@67:           WHERE "issue_id" = "issue_id_p"
jbe@67:           AND "event" = 'periodic'
jbe@67:         ),
jbe@67:         "vote_now" = (
jbe@67:           SELECT coalesce(sum("weight"), 0)
jbe@67:           FROM "direct_interest_snapshot"
jbe@67:           WHERE "issue_id" = "issue_id_p"
jbe@67:           AND "event" = 'periodic'
jbe@67:           AND "voting_requested" = TRUE
jbe@67:         ),
jbe@67:         "vote_later" = (
jbe@67:           SELECT coalesce(sum("weight"), 0)
jbe@67:           FROM "direct_interest_snapshot"
jbe@67:           WHERE "issue_id" = "issue_id_p"
jbe@67:           AND "event" = 'periodic'
jbe@67:           AND "voting_requested" = FALSE
jbe@67:         )
jbe@67:         WHERE "id" = "issue_id_p";
jbe@67:       FOR "initiative_id_v" IN
jbe@67:         SELECT "id" FROM "initiative" WHERE "issue_id" = "issue_id_p"
jbe@67:       LOOP
jbe@67:         UPDATE "initiative" SET
jbe@67:           "supporter_count" = (
jbe@67:             SELECT coalesce(sum("di"."weight"), 0)
jbe@67:             FROM "direct_interest_snapshot" AS "di"
jbe@67:             JOIN "direct_supporter_snapshot" AS "ds"
jbe@67:             ON "di"."member_id" = "ds"."member_id"
jbe@67:             WHERE "di"."issue_id" = "issue_id_p"
jbe@67:             AND "di"."event" = 'periodic'
jbe@67:             AND "ds"."initiative_id" = "initiative_id_v"
jbe@67:             AND "ds"."event" = 'periodic'
jbe@67:           ),
jbe@67:           "informed_supporter_count" = (
jbe@67:             SELECT coalesce(sum("di"."weight"), 0)
jbe@67:             FROM "direct_interest_snapshot" AS "di"
jbe@67:             JOIN "direct_supporter_snapshot" AS "ds"
jbe@67:             ON "di"."member_id" = "ds"."member_id"
jbe@67:             WHERE "di"."issue_id" = "issue_id_p"
jbe@67:             AND "di"."event" = 'periodic'
jbe@67:             AND "ds"."initiative_id" = "initiative_id_v"
jbe@67:             AND "ds"."event" = 'periodic'
jbe@67:             AND "ds"."informed"
jbe@67:           ),
jbe@67:           "satisfied_supporter_count" = (
jbe@67:             SELECT coalesce(sum("di"."weight"), 0)
jbe@67:             FROM "direct_interest_snapshot" AS "di"
jbe@67:             JOIN "direct_supporter_snapshot" AS "ds"
jbe@67:             ON "di"."member_id" = "ds"."member_id"
jbe@67:             WHERE "di"."issue_id" = "issue_id_p"
jbe@67:             AND "di"."event" = 'periodic'
jbe@67:             AND "ds"."initiative_id" = "initiative_id_v"
jbe@67:             AND "ds"."event" = 'periodic'
jbe@67:             AND "ds"."satisfied"
jbe@67:           ),
jbe@67:           "satisfied_informed_supporter_count" = (
jbe@67:             SELECT coalesce(sum("di"."weight"), 0)
jbe@67:             FROM "direct_interest_snapshot" AS "di"
jbe@67:             JOIN "direct_supporter_snapshot" AS "ds"
jbe@67:             ON "di"."member_id" = "ds"."member_id"
jbe@67:             WHERE "di"."issue_id" = "issue_id_p"
jbe@67:             AND "di"."event" = 'periodic'
jbe@67:             AND "ds"."initiative_id" = "initiative_id_v"
jbe@67:             AND "ds"."event" = 'periodic'
jbe@67:             AND "ds"."informed"
jbe@67:             AND "ds"."satisfied"
jbe@67:           )
jbe@67:           WHERE "id" = "initiative_id_v";
jbe@67:         FOR "suggestion_id_v" IN
jbe@67:           SELECT "id" FROM "suggestion"
jbe@67:           WHERE "initiative_id" = "initiative_id_v"
jbe@67:         LOOP
jbe@67:           UPDATE "suggestion" SET
jbe@67:             "minus2_unfulfilled_count" = (
jbe@67:               SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@67:               FROM "issue" CROSS JOIN "opinion"
jbe@67:               JOIN "direct_interest_snapshot" AS "snapshot"
jbe@67:               ON "snapshot"."issue_id" = "issue"."id"
jbe@67:               AND "snapshot"."event" = "issue"."latest_snapshot_event"
jbe@67:               AND "snapshot"."member_id" = "opinion"."member_id"
jbe@67:               WHERE "issue"."id" = "issue_id_p"
jbe@67:               AND "opinion"."suggestion_id" = "suggestion_id_v"
jbe@67:               AND "opinion"."degree" = -2
jbe@67:               AND "opinion"."fulfilled" = FALSE
jbe@67:             ),
jbe@67:             "minus2_fulfilled_count" = (
jbe@67:               SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@67:               FROM "issue" CROSS JOIN "opinion"
jbe@67:               JOIN "direct_interest_snapshot" AS "snapshot"
jbe@67:               ON "snapshot"."issue_id" = "issue"."id"
jbe@67:               AND "snapshot"."event" = "issue"."latest_snapshot_event"
jbe@67:               AND "snapshot"."member_id" = "opinion"."member_id"
jbe@67:               WHERE "issue"."id" = "issue_id_p"
jbe@67:               AND "opinion"."suggestion_id" = "suggestion_id_v"
jbe@67:               AND "opinion"."degree" = -2
jbe@67:               AND "opinion"."fulfilled" = TRUE
jbe@67:             ),
jbe@67:             "minus1_unfulfilled_count" = (
jbe@67:               SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@67:               FROM "issue" CROSS JOIN "opinion"
jbe@67:               JOIN "direct_interest_snapshot" AS "snapshot"
jbe@67:               ON "snapshot"."issue_id" = "issue"."id"
jbe@67:               AND "snapshot"."event" = "issue"."latest_snapshot_event"
jbe@67:               AND "snapshot"."member_id" = "opinion"."member_id"
jbe@67:               WHERE "issue"."id" = "issue_id_p"
jbe@67:               AND "opinion"."suggestion_id" = "suggestion_id_v"
jbe@67:               AND "opinion"."degree" = -1
jbe@67:               AND "opinion"."fulfilled" = FALSE
jbe@67:             ),
jbe@67:             "minus1_fulfilled_count" = (
jbe@67:               SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@67:               FROM "issue" CROSS JOIN "opinion"
jbe@67:               JOIN "direct_interest_snapshot" AS "snapshot"
jbe@67:               ON "snapshot"."issue_id" = "issue"."id"
jbe@67:               AND "snapshot"."event" = "issue"."latest_snapshot_event"
jbe@67:               AND "snapshot"."member_id" = "opinion"."member_id"
jbe@67:               WHERE "issue"."id" = "issue_id_p"
jbe@67:               AND "opinion"."suggestion_id" = "suggestion_id_v"
jbe@67:               AND "opinion"."degree" = -1
jbe@67:               AND "opinion"."fulfilled" = TRUE
jbe@67:             ),
jbe@67:             "plus1_unfulfilled_count" = (
jbe@67:               SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@67:               FROM "issue" CROSS JOIN "opinion"
jbe@67:               JOIN "direct_interest_snapshot" AS "snapshot"
jbe@67:               ON "snapshot"."issue_id" = "issue"."id"
jbe@67:               AND "snapshot"."event" = "issue"."latest_snapshot_event"
jbe@67:               AND "snapshot"."member_id" = "opinion"."member_id"
jbe@67:               WHERE "issue"."id" = "issue_id_p"
jbe@67:               AND "opinion"."suggestion_id" = "suggestion_id_v"
jbe@67:               AND "opinion"."degree" = 1
jbe@67:               AND "opinion"."fulfilled" = FALSE
jbe@67:             ),
jbe@67:             "plus1_fulfilled_count" = (
jbe@67:               SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@67:               FROM "issue" CROSS JOIN "opinion"
jbe@67:               JOIN "direct_interest_snapshot" AS "snapshot"
jbe@67:               ON "snapshot"."issue_id" = "issue"."id"
jbe@67:               AND "snapshot"."event" = "issue"."latest_snapshot_event"
jbe@67:               AND "snapshot"."member_id" = "opinion"."member_id"
jbe@67:               WHERE "issue"."id" = "issue_id_p"
jbe@67:               AND "opinion"."suggestion_id" = "suggestion_id_v"
jbe@67:               AND "opinion"."degree" = 1
jbe@67:               AND "opinion"."fulfilled" = TRUE
jbe@67:             ),
jbe@67:             "plus2_unfulfilled_count" = (
jbe@67:               SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@67:               FROM "issue" CROSS JOIN "opinion"
jbe@67:               JOIN "direct_interest_snapshot" AS "snapshot"
jbe@67:               ON "snapshot"."issue_id" = "issue"."id"
jbe@67:               AND "snapshot"."event" = "issue"."latest_snapshot_event"
jbe@67:               AND "snapshot"."member_id" = "opinion"."member_id"
jbe@67:               WHERE "issue"."id" = "issue_id_p"
jbe@67:               AND "opinion"."suggestion_id" = "suggestion_id_v"
jbe@67:               AND "opinion"."degree" = 2
jbe@67:               AND "opinion"."fulfilled" = FALSE
jbe@67:             ),
jbe@67:             "plus2_fulfilled_count" = (
jbe@67:               SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@67:               FROM "issue" CROSS JOIN "opinion"
jbe@67:               JOIN "direct_interest_snapshot" AS "snapshot"
jbe@67:               ON "snapshot"."issue_id" = "issue"."id"
jbe@67:               AND "snapshot"."event" = "issue"."latest_snapshot_event"
jbe@67:               AND "snapshot"."member_id" = "opinion"."member_id"
jbe@67:               WHERE "issue"."id" = "issue_id_p"
jbe@67:               AND "opinion"."suggestion_id" = "suggestion_id_v"
jbe@67:               AND "opinion"."degree" = 2
jbe@67:               AND "opinion"."fulfilled" = TRUE
jbe@67:             )
jbe@67:             WHERE "suggestion"."id" = "suggestion_id_v";
jbe@67:         END LOOP;
jbe@67:       END LOOP;
jbe@67:       RETURN;
jbe@67:     END;
jbe@67:   $$;
jbe@67: 
jbe@67: CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
jbe@67:   RETURNS VOID
jbe@67:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@67:     DECLARE
jbe@67:       "issue_row"   "issue"%ROWTYPE;
jbe@67:       "member_id_v" "member"."id"%TYPE;
jbe@67:     BEGIN
jbe@67:       PERFORM "lock_issue"("issue_id_p");
jbe@67:       SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
jbe@67:       DELETE FROM "delegating_voter"
jbe@67:         WHERE "issue_id" = "issue_id_p";
jbe@67:       DELETE FROM "direct_voter"
jbe@67:         WHERE "issue_id" = "issue_id_p"
jbe@67:         AND "autoreject" = TRUE;
jbe@67:       DELETE FROM "direct_voter" USING "member"
jbe@67:         WHERE "direct_voter"."member_id" = "member"."id"
jbe@67:         AND "direct_voter"."issue_id" = "issue_id_p"
jbe@67:         AND "member"."active" = FALSE;
jbe@67:       UPDATE "direct_voter" SET "weight" = 1
jbe@67:         WHERE "issue_id" = "issue_id_p";
jbe@67:       PERFORM "add_vote_delegations"("issue_id_p");
jbe@67:       FOR "member_id_v" IN
jbe@67:         SELECT "interest"."member_id"
jbe@67:           FROM "interest"
jbe@67:           LEFT JOIN "direct_voter"
jbe@67:             ON "interest"."member_id" = "direct_voter"."member_id"
jbe@67:             AND "interest"."issue_id" = "direct_voter"."issue_id"
jbe@67:           LEFT JOIN "delegating_voter"
jbe@67:             ON "interest"."member_id" = "delegating_voter"."member_id"
jbe@67:             AND "interest"."issue_id" = "delegating_voter"."issue_id"
jbe@67:           WHERE "interest"."issue_id" = "issue_id_p"
jbe@67:           AND "interest"."autoreject" = TRUE
jbe@67:           AND "direct_voter"."member_id" ISNULL
jbe@67:           AND "delegating_voter"."member_id" ISNULL
jbe@67:         UNION SELECT "membership"."member_id"
jbe@67:           FROM "membership"
jbe@67:           LEFT JOIN "interest"
jbe@67:             ON "membership"."member_id" = "interest"."member_id"
jbe@67:             AND "interest"."issue_id" = "issue_id_p"
jbe@67:           LEFT JOIN "direct_voter"
jbe@67:             ON "membership"."member_id" = "direct_voter"."member_id"
jbe@67:             AND "direct_voter"."issue_id" = "issue_id_p"
jbe@67:           LEFT JOIN "delegating_voter"
jbe@67:             ON "membership"."member_id" = "delegating_voter"."member_id"
jbe@67:             AND "delegating_voter"."issue_id" = "issue_id_p"
jbe@67:           WHERE "membership"."area_id" = "issue_row"."area_id"
jbe@67:           AND "membership"."autoreject" = TRUE
jbe@67:           AND "interest"."autoreject" ISNULL
jbe@67:           AND "direct_voter"."member_id" ISNULL
jbe@67:           AND "delegating_voter"."member_id" ISNULL
jbe@67:       LOOP
jbe@67:         INSERT INTO "direct_voter"
jbe@67:           ("member_id", "issue_id", "weight", "autoreject") VALUES
jbe@67:           ("member_id_v", "issue_id_p", 1, TRUE);
jbe@67:         INSERT INTO "vote" (
jbe@67:           "member_id",
jbe@67:           "issue_id",
jbe@67:           "initiative_id",
jbe@67:           "grade"
jbe@67:           ) SELECT
jbe@67:             "member_id_v" AS "member_id",
jbe@67:             "issue_id_p"  AS "issue_id",
jbe@67:             "id"          AS "initiative_id",
jbe@67:             -1            AS "grade"
jbe@67:           FROM "initiative" WHERE "issue_id" = "issue_id_p";
jbe@67:       END LOOP;
jbe@67:       PERFORM "add_vote_delegations"("issue_id_p");
jbe@67:       UPDATE "issue" SET
jbe@67:         "closed" = now(),
jbe@67:         "voter_count" = (
jbe@67:           SELECT coalesce(sum("weight"), 0)
jbe@67:           FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
jbe@67:         )
jbe@67:         WHERE "id" = "issue_id_p";
jbe@67:       UPDATE "initiative" SET
jbe@67:         "positive_votes" = "vote_counts"."positive_votes",
jbe@67:         "negative_votes" = "vote_counts"."negative_votes",
jbe@67:         "agreed" = CASE WHEN "majority_strict" THEN
jbe@67:           "vote_counts"."positive_votes" * "majority_den" >
jbe@67:           "majority_num" *
jbe@67:           ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
jbe@67:         ELSE
jbe@67:           "vote_counts"."positive_votes" * "majority_den" >=
jbe@67:           "majority_num" *
jbe@67:           ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
jbe@67:         END
jbe@67:         FROM
jbe@67:           ( SELECT
jbe@67:               "initiative"."id" AS "initiative_id",
jbe@67:               coalesce(
jbe@67:                 sum(
jbe@67:                   CASE WHEN "grade" > 0 THEN "direct_voter"."weight" ELSE 0 END
jbe@67:                 ),
jbe@67:                 0
jbe@67:               ) AS "positive_votes",
jbe@67:               coalesce(
jbe@67:                 sum(
jbe@67:                   CASE WHEN "grade" < 0 THEN "direct_voter"."weight" ELSE 0 END
jbe@67:                 ),
jbe@67:                 0
jbe@67:               ) AS "negative_votes"
jbe@67:             FROM "initiative"
jbe@67:             JOIN "issue" ON "initiative"."issue_id" = "issue"."id"
jbe@67:             JOIN "policy" ON "issue"."policy_id" = "policy"."id"
jbe@67:             LEFT JOIN "direct_voter"
jbe@67:               ON "direct_voter"."issue_id" = "initiative"."issue_id"
jbe@67:             LEFT JOIN "vote"
jbe@67:               ON "vote"."initiative_id" = "initiative"."id"
jbe@67:               AND "vote"."member_id" = "direct_voter"."member_id"
jbe@67:             WHERE "initiative"."issue_id" = "issue_id_p"
jbe@67:             AND "initiative"."admitted"  -- NOTE: NULL case is handled too
jbe@67:             GROUP BY "initiative"."id"
jbe@67:           ) AS "vote_counts",
jbe@67:           "issue",
jbe@67:           "policy"
jbe@67:         WHERE "vote_counts"."initiative_id" = "initiative"."id"
jbe@67:         AND "issue"."id" = "initiative"."issue_id"
jbe@67:         AND "policy"."id" = "issue"."policy_id";
jbe@67:       -- NOTE: "closed" column of issue must be set at this point
jbe@67:       DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
jbe@67:       INSERT INTO "battle" (
jbe@67:         "issue_id",
jbe@67:         "winning_initiative_id", "losing_initiative_id",
jbe@67:         "count"
jbe@67:       ) SELECT
jbe@67:         "issue_id",
jbe@67:         "winning_initiative_id", "losing_initiative_id",
jbe@67:         "count"
jbe@67:         FROM "battle_view" WHERE "issue_id" = "issue_id_p";
jbe@67:     END;
jbe@67:   $$;
jbe@67: 
jbe@67: CREATE OR REPLACE FUNCTION "check_issue"
jbe@67:   ( "issue_id_p" "issue"."id"%TYPE )
jbe@67:   RETURNS VOID
jbe@67:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@67:     DECLARE
jbe@67:       "issue_row"         "issue"%ROWTYPE;
jbe@67:       "policy_row"        "policy"%ROWTYPE;
jbe@67:       "voting_requested_v" BOOLEAN;
jbe@67:     BEGIN
jbe@67:       PERFORM "lock_issue"("issue_id_p");
jbe@67:       SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
jbe@67:       -- only process open issues:
jbe@67:       IF "issue_row"."closed" ISNULL THEN
jbe@67:         SELECT * INTO "policy_row" FROM "policy"
jbe@67:           WHERE "id" = "issue_row"."policy_id";
jbe@67:         -- create a snapshot, unless issue is already fully frozen:
jbe@67:         IF "issue_row"."fully_frozen" ISNULL THEN
jbe@67:           PERFORM "create_snapshot"("issue_id_p");
jbe@67:           SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
jbe@67:         END IF;
jbe@67:         -- eventually close or accept issues, which have not been accepted:
jbe@67:         IF "issue_row"."accepted" ISNULL THEN
jbe@67:           IF EXISTS (
jbe@67:             SELECT NULL FROM "initiative"
jbe@67:             WHERE "issue_id" = "issue_id_p"
jbe@67:             AND "supporter_count" > 0
jbe@67:             AND "supporter_count" * "policy_row"."issue_quorum_den"
jbe@67:             >= "issue_row"."population" * "policy_row"."issue_quorum_num"
jbe@67:           ) THEN
jbe@67:             -- accept issues, if supporter count is high enough
jbe@67:             PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
jbe@67:             "issue_row"."accepted" = now();  -- NOTE: "issue_row" used later
jbe@67:             UPDATE "issue" SET "accepted" = "issue_row"."accepted"
jbe@67:               WHERE "id" = "issue_row"."id";
jbe@67:           ELSIF
jbe@67:             now() >= "issue_row"."created" + "issue_row"."admission_time"
jbe@67:           THEN
jbe@67:             -- close issues, if admission time has expired
jbe@67:             PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
jbe@67:             UPDATE "issue" SET "closed" = now()
jbe@67:               WHERE "id" = "issue_row"."id";
jbe@67:           END IF;
jbe@67:         END IF;
jbe@67:         -- eventually half freeze issues:
jbe@67:         IF
jbe@67:           -- NOTE: issue can't be closed at this point, if it has been accepted
jbe@67:           "issue_row"."accepted" NOTNULL AND
jbe@67:           "issue_row"."half_frozen" ISNULL
jbe@67:         THEN
jbe@67:           SELECT
jbe@67:             CASE
jbe@67:               WHEN "vote_now" * 2 > "issue_row"."population" THEN
jbe@67:                 TRUE
jbe@67:               WHEN "vote_later" * 2 > "issue_row"."population" THEN
jbe@67:                 FALSE
jbe@67:               ELSE NULL
jbe@67:             END
jbe@67:             INTO "voting_requested_v"
jbe@67:             FROM "issue" WHERE "id" = "issue_id_p";
jbe@67:           IF
jbe@67:             "voting_requested_v" OR (
jbe@67:               "voting_requested_v" ISNULL AND
jbe@67:               now() >= "issue_row"."accepted" + "issue_row"."discussion_time"
jbe@67:             )
jbe@67:           THEN
jbe@67:             PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze');
jbe@67:             "issue_row"."half_frozen" = now();  -- NOTE: "issue_row" used later
jbe@67:             UPDATE "issue" SET "half_frozen" = "issue_row"."half_frozen"
jbe@67:               WHERE "id" = "issue_row"."id";
jbe@67:           END IF;
jbe@67:         END IF;
jbe@67:         -- close issues after some time, if all initiatives have been revoked:
jbe@67:         IF
jbe@67:           "issue_row"."closed" ISNULL AND
jbe@67:           NOT EXISTS (
jbe@67:             -- all initiatives are revoked
jbe@67:             SELECT NULL FROM "initiative"
jbe@67:             WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
jbe@67:           ) AND (
jbe@67:             NOT EXISTS (
jbe@67:               -- and no initiatives have been revoked lately
jbe@67:               SELECT NULL FROM "initiative"
jbe@67:               WHERE "issue_id" = "issue_id_p"
jbe@67:               AND now() < "revoked" + "issue_row"."verification_time"
jbe@67:             ) OR (
jbe@67:               -- or verification time has elapsed
jbe@67:               "issue_row"."half_frozen" NOTNULL AND
jbe@67:               "issue_row"."fully_frozen" ISNULL AND
jbe@67:               now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
jbe@67:             )
jbe@67:           )
jbe@67:         THEN
jbe@67:           "issue_row"."closed" = now();  -- NOTE: "issue_row" used later
jbe@67:           UPDATE "issue" SET "closed" = "issue_row"."closed"
jbe@67:             WHERE "id" = "issue_row"."id";
jbe@67:         END IF;
jbe@67:         -- fully freeze issue after verification time:
jbe@67:         IF
jbe@67:           "issue_row"."half_frozen" NOTNULL AND
jbe@67:           "issue_row"."fully_frozen" ISNULL AND
jbe@67:           "issue_row"."closed" ISNULL AND
jbe@67:           now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
jbe@67:         THEN
jbe@67:           PERFORM "freeze_after_snapshot"("issue_id_p");
jbe@67:           -- NOTE: "issue" might change, thus "issue_row" has to be updated below
jbe@67:         END IF;
jbe@67:         SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
jbe@67:         -- close issue by calling close_voting(...) after voting time:
jbe@67:         IF
jbe@67:           "issue_row"."closed" ISNULL AND
jbe@67:           "issue_row"."fully_frozen" NOTNULL AND
jbe@67:           now() >= "issue_row"."fully_frozen" + "issue_row"."voting_time"
jbe@67:         THEN
jbe@67:           PERFORM "close_voting"("issue_id_p");
jbe@67:         END IF;
jbe@67:       END IF;
jbe@67:       RETURN;
jbe@67:     END;
jbe@67:   $$;
jbe@67: 
jbe@67: 
jbe@67: DROP FUNCTION "global_lock"();
jbe@67: 
jbe@67: 
jbe@67: COMMIT;