jbe@0: 
jbe@0: CREATE LANGUAGE plpgsql;  -- Triggers are implemented in PL/pgSQL
jbe@0: 
jbe@0: -- NOTE: In PostgreSQL every UNIQUE constraint implies creation of an index
jbe@0: 
jbe@0: BEGIN;
jbe@0: 
jbe@5: CREATE VIEW "liquid_feedback_version" AS
jbe@5:   SELECT * FROM (VALUES ('beta6', NULL, NULL, NULL))
jbe@5:   AS "subquery"("string", "major", "minor", "revision");
jbe@5: 
jbe@0: 
jbe@0: 
jbe@0: -------------------------
jbe@0: -- Tables and indicies --
jbe@0: -------------------------
jbe@0: 
jbe@0: 
jbe@0: CREATE TABLE "member" (
jbe@0:         "id"                    SERIAL4         PRIMARY KEY,
jbe@0:         "login"                 TEXT            NOT NULL UNIQUE,
jbe@0:         "password"              TEXT,
jbe@0:         "active"                BOOLEAN         NOT NULL DEFAULT TRUE,
jbe@0:         "admin"                 BOOLEAN         NOT NULL DEFAULT FALSE,
jbe@0:         "name"                  TEXT,
jbe@0:         "ident_number"          TEXT            UNIQUE );
jbe@0: CREATE INDEX "member_active_idx" ON "member" ("active");
jbe@0: 
jbe@0: COMMENT ON TABLE "member" IS 'Users of the system, e.g. members of an organization';
jbe@0: 
jbe@0: COMMENT ON COLUMN "member"."login"        IS 'Login name';
jbe@0: COMMENT ON COLUMN "member"."password"     IS 'Password (preferably as crypto-hash, depending on the frontend or access layer)';
jbe@0: COMMENT ON COLUMN "member"."active"       IS 'Inactive members can not login and their supports/votes are not counted by the system.';
jbe@0: COMMENT ON COLUMN "member"."ident_number" IS 'Additional information about the members idenficication number within the organization';
jbe@0: 
jbe@0: 
jbe@4: CREATE TABLE "member_count" (
jbe@5:         "calculated"            TIMESTAMPTZ     NOT NULL DEFAULT NOW(),
jbe@5:         "total_count"           INT4            NOT NULL );
jbe@4: 
jbe@5: COMMENT ON TABLE "member_count" IS 'Contains one row which contains the total count of active(!) members and a timestamp indicating when the total member count and area member counts were calculated';
jbe@4: 
jbe@5: COMMENT ON COLUMN "member_count"."calculated"  IS 'timestamp indicating when the total member count and area member counts were calculated';
jbe@5: COMMENT ON COLUMN "member_count"."total_count" IS 'Total count of active(!) members';
jbe@4: 
jbe@4: 
jbe@0: CREATE TABLE "contact" (
jbe@0:         PRIMARY KEY ("member_id", "other_member_id"),
jbe@0:         "member_id"             INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0:         "other_member_id"       INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0:         "public"                BOOLEAN         NOT NULL DEFAULT FALSE );
jbe@0: 
jbe@0: COMMENT ON TABLE "contact" IS 'Contact lists';
jbe@0: 
jbe@0: COMMENT ON COLUMN "contact"."member_id"       IS 'Member having the contact list';
jbe@0: COMMENT ON COLUMN "contact"."other_member_id" IS 'Member referenced in the contact list';
jbe@0: COMMENT ON COLUMN "contact"."public"          IS 'TRUE = display contact publically';
jbe@0: 
jbe@0: 
jbe@0: CREATE TABLE "session" (
jbe@0:         "ident"                 TEXT            PRIMARY KEY,
jbe@0:         "additional_secret"     TEXT,
jbe@0:         "expiry"                TIMESTAMPTZ     NOT NULL DEFAULT now() + '24 hours',
jbe@0:         "member_id"             INT8            REFERENCES "member" ("id") ON DELETE SET NULL,
jbe@0:         "lang"                  TEXT );
jbe@0: CREATE INDEX "session_expiry_idx" ON "session" ("expiry");
jbe@0: 
jbe@0: COMMENT ON TABLE "session" IS 'Sessions, i.e. for a web-frontend';
jbe@0: 
jbe@0: COMMENT ON COLUMN "session"."ident"             IS 'Secret session identifier (i.e. random string)';
jbe@0: COMMENT ON COLUMN "session"."additional_secret" IS 'Additional field to store a secret, which can be used against CSRF attacks';
jbe@0: COMMENT ON COLUMN "session"."member_id"         IS 'Reference to member, who is logged in';
jbe@0: COMMENT ON COLUMN "session"."lang"              IS 'Language code of the selected language';
jbe@0: 
jbe@0: 
jbe@0: CREATE TABLE "policy" (
jbe@0:         "id"                    SERIAL4         PRIMARY KEY,
jbe@0:         "active"                BOOLEAN         NOT NULL DEFAULT TRUE,
jbe@0:         "name"                  TEXT            NOT NULL UNIQUE,
jbe@0:         "description"           TEXT            NOT NULL DEFAULT '',
jbe@0:         "admission_time"        INTERVAL        NOT NULL,
jbe@0:         "discussion_time"       INTERVAL        NOT NULL,
jbe@3:         "verification_time"     INTERVAL        NOT NULL,
jbe@0:         "voting_time"           INTERVAL        NOT NULL,
jbe@0:         "issue_quorum_num"      INT4            NOT NULL,
jbe@0:         "issue_quorum_den"      INT4            NOT NULL,
jbe@0:         "initiative_quorum_num" INT4            NOT NULL,
jbe@0:         "initiative_quorum_den" INT4            NOT NULL );
jbe@0: CREATE INDEX "policy_active_idx" ON "policy" ("active");
jbe@0: 
jbe@0: COMMENT ON TABLE "policy" IS 'Policies for a particular proceeding type (timelimits, quorum)';
jbe@0: 
jbe@0: COMMENT ON COLUMN "policy"."active"                IS 'TRUE = policy can be used for new issues';
jbe@0: COMMENT ON COLUMN "policy"."admission_time"        IS 'Maximum time an issue stays open without being "accepted"';
jbe@3: COMMENT ON COLUMN "policy"."discussion_time"       IS 'Regular time until an issue is "half_frozen" after being "accepted"';
jbe@3: COMMENT ON COLUMN "policy"."verification_time"     IS 'Regular time until an issue is "fully_frozen" after being "half_frozen"';
jbe@3: COMMENT ON COLUMN "policy"."voting_time"           IS 'Time after an issue is "fully_frozen" but not "closed"';
jbe@0: COMMENT ON COLUMN "policy"."issue_quorum_num"      IS 'Numerator of quorum to be reached by one initiative of an issue to be "accepted"';
jbe@0: COMMENT ON COLUMN "policy"."issue_quorum_den"      IS 'Denominator of quorum to be reached by one initiative of an issue to be "accepted"';
jbe@0: COMMENT ON COLUMN "policy"."initiative_quorum_num" IS 'Numerator of quorum to be reached by an initiative to be "admitted" for voting';
jbe@0: COMMENT ON COLUMN "policy"."initiative_quorum_den" IS 'Denominator of quorum to be reached by an initiative to be "admitted" for voting';
jbe@0: 
jbe@0: 
jbe@0: CREATE TABLE "area" (
jbe@0:         "id"                    SERIAL4         PRIMARY KEY,
jbe@0:         "active"                BOOLEAN         NOT NULL DEFAULT TRUE,
jbe@0:         "name"                  TEXT            NOT NULL,
jbe@4:         "description"           TEXT            NOT NULL DEFAULT '',
jbe@5:         "direct_member_count"   INT4,
jbe@5:         "member_weight"         INT4,
jbe@5:         "autoreject_weight"     INT4 );
jbe@0: CREATE INDEX "area_active_idx" ON "area" ("active");
jbe@0: 
jbe@0: COMMENT ON TABLE "area" IS 'Subject areas';
jbe@0: 
jbe@5: COMMENT ON COLUMN "area"."active"              IS 'TRUE means new issues can be created in this area';
jbe@5: COMMENT ON COLUMN "area"."direct_member_count" IS 'Number of active members of that area (ignoring their weight), as calculated from view "area_member_count"';
jbe@5: COMMENT ON COLUMN "area"."member_weight"       IS 'Same as "direct_member_count" but respecting delegations';
jbe@5: COMMENT ON COLUMN "area"."autoreject_weight"   IS 'Sum of weight of members using the autoreject feature';
jbe@0: 
jbe@0: 
jbe@0: CREATE TABLE "issue" (
jbe@0:         "id"                    SERIAL4         PRIMARY KEY,
jbe@0:         "area_id"               INT4            NOT NULL REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0:         "policy_id"             INT4            NOT NULL REFERENCES "policy" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
jbe@0:         "created"               TIMESTAMPTZ     NOT NULL DEFAULT now(),
jbe@0:         "accepted"              TIMESTAMPTZ,
jbe@3:         "half_frozen"           TIMESTAMPTZ,
jbe@3:         "fully_frozen"          TIMESTAMPTZ,
jbe@0:         "closed"                TIMESTAMPTZ,
jbe@0:         "ranks_available"       BOOLEAN         NOT NULL DEFAULT FALSE,
jbe@0:         "snapshot"              TIMESTAMPTZ,
jbe@0:         "population"            INT4,
jbe@0:         "vote_now"              INT4,
jbe@0:         "vote_later"            INT4,
jbe@4:         "voter_count"           INT4,
jbe@0:         CONSTRAINT "valid_state" CHECK (
jbe@3:           ("accepted" ISNULL  AND "half_frozen" ISNULL  AND "fully_frozen" ISNULL  AND "closed" ISNULL  AND "ranks_available" = FALSE) OR
jbe@3:           ("accepted" ISNULL  AND "half_frozen" ISNULL  AND "fully_frozen" ISNULL  AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
jbe@3:           ("accepted" NOTNULL AND "half_frozen" ISNULL  AND "fully_frozen" ISNULL  AND "closed" ISNULL  AND "ranks_available" = FALSE) OR
jbe@3:           ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL  AND "closed" ISNULL  AND "ranks_available" = FALSE) OR
jbe@3:           ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" ISNULL  AND "ranks_available" = FALSE) OR
jbe@3:           ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
jbe@3:           ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = TRUE) ),
jbe@3:         CONSTRAINT "state_change_order" CHECK (
jbe@3:           "created" <= "accepted" AND
jbe@3:           "accepted" <= "half_frozen" AND
jbe@3:           "half_frozen" <= "fully_frozen" AND
jbe@3:           "fully_frozen" <= "closed" ),
jbe@3:         CONSTRAINT "last_snapshot_on_full_freeze" CHECK ("snapshot" = "fully_frozen"),  -- NOTE: snapshot can be set, while frozen is NULL yet
jbe@3:         CONSTRAINT "freeze_requires_snapshot" CHECK ("fully_frozen" ISNULL OR "snapshot" NOTNULL) );
jbe@0: CREATE INDEX "issue_area_id_idx" ON "issue" ("area_id");
jbe@0: CREATE INDEX "issue_policy_id_idx" ON "issue" ("policy_id");
jbe@0: CREATE INDEX "issue_created_idx_open" ON "issue" ("created") WHERE "closed" ISNULL;
jbe@0: 
jbe@0: COMMENT ON TABLE "issue" IS 'Groups of initiatives';
jbe@0: 
jbe@0: COMMENT ON COLUMN "issue"."accepted"        IS 'Point in time, when one initiative of issue reached the "issue_quorum"';
jbe@3: COMMENT ON COLUMN "issue"."half_frozen"     IS 'Point in time, when "discussion_time" has elapsed, or members voted for voting';
jbe@3: COMMENT ON COLUMN "issue"."fully_frozen"    IS 'Point in time, when "verification_time" has elapsed';
jbe@3: COMMENT ON COLUMN "issue"."closed"          IS 'Point in time, when "admission_time" or "voting_time" have elapsed, and issue is no longer active';
jbe@0: COMMENT ON COLUMN "issue"."ranks_available" IS 'TRUE = ranks have been calculated';
jbe@0: COMMENT ON COLUMN "issue"."snapshot"        IS 'Point in time, when snapshot tables have been updated and "population", "vote_now", "vote_later" and *_count values were precalculated';
jbe@5: COMMENT ON COLUMN "issue"."population"      IS 'Sum of "weight" column in table "direct_population_snapshot"';
jbe@5: COMMENT ON COLUMN "issue"."vote_now"        IS 'Number of votes in favor of voting now, as calculated from table "direct_interest_snapshot"';
jbe@5: COMMENT ON COLUMN "issue"."vote_later"      IS 'Number of votes against voting now, as calculated from table "direct_interest_snapshot"';
jbe@4: COMMENT ON COLUMN "issue"."voter_count"     IS 'Total number of direct and delegating voters; This value is related to the final voting, while "population" is related to snapshots before the final voting';
jbe@0: 
jbe@0: 
jbe@0: CREATE TABLE "initiative" (
jbe@0:         UNIQUE ("issue_id", "id"),  -- index needed for foreign-key on table "vote"
jbe@0:         "issue_id"              INT4            NOT NULL REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0:         "id"                    SERIAL4         PRIMARY KEY,
jbe@0:         "name"                  TEXT            NOT NULL,
jbe@0:         "created"               TIMESTAMPTZ     NOT NULL DEFAULT now(),
jbe@0:         "revoked"               TIMESTAMPTZ,
jbe@0:         "admitted"              BOOLEAN,
jbe@0:         "supporter_count"                    INT4,
jbe@0:         "informed_supporter_count"           INT4,
jbe@0:         "satisfied_supporter_count"          INT4,
jbe@0:         "satisfied_informed_supporter_count" INT4,
jbe@0:         "positive_votes"        INT4,
jbe@0:         "negative_votes"        INT4,
jbe@0:         "rank"                  INT4,
jbe@0:         CONSTRAINT "revoked_initiatives_cant_be_admitted"
jbe@0:           CHECK ("revoked" ISNULL OR "admitted" ISNULL),
jbe@0:         CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results"
jbe@0:           CHECK ("admitted" = TRUE OR ("positive_votes" ISNULL AND "negative_votes" ISNULL AND "rank" ISNULL)) );
jbe@0: 
jbe@0: COMMENT ON TABLE "initiative" IS 'Group of members publishing drafts for resolutions to be passed';
jbe@0: 
jbe@0: COMMENT ON COLUMN "initiative"."revoked"        IS 'Point in time, when one initiator decided to revoke the initiative';
jbe@0: COMMENT ON COLUMN "initiative"."admitted"       IS 'True, if initiative reaches the "initiative_quorum" when freezing the issue';
jbe@0: COMMENT ON COLUMN "initiative"."supporter_count"                    IS 'Calculated from table "direct_supporter_snapshot"';
jbe@0: COMMENT ON COLUMN "initiative"."informed_supporter_count"           IS 'Calculated from table "direct_supporter_snapshot"';
jbe@0: COMMENT ON COLUMN "initiative"."satisfied_supporter_count"          IS 'Calculated from table "direct_supporter_snapshot"';
jbe@0: COMMENT ON COLUMN "initiative"."satisfied_informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
jbe@0: COMMENT ON COLUMN "initiative"."positive_votes" IS 'Calculated from table "direct_voter"';
jbe@0: COMMENT ON COLUMN "initiative"."negative_votes" IS 'Calculated from table "direct_voter"';
jbe@0: COMMENT ON COLUMN "initiative"."rank"           IS 'Rank of approved initiatives (winner is 1), calculated from table "direct_voter"';
jbe@0: 
jbe@0: 
jbe@0: CREATE TABLE "draft" (
jbe@0:         UNIQUE ("initiative_id", "id"),  -- index needed for foreign-key on table "supporter"
jbe@0:         "initiative_id"         INT4            NOT NULL REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0:         "id"                    SERIAL8         PRIMARY KEY,
jbe@0:         "created"               TIMESTAMPTZ     NOT NULL DEFAULT now(),
jbe@0:         "author_id"             INT4            NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
jbe@0:         "content"               TEXT            NOT NULL );
jbe@0: 
jbe@0: COMMENT ON TABLE "draft" IS 'Drafts of initiatives to solve issues';
jbe@0: 
jbe@0: 
jbe@0: CREATE TABLE "suggestion" (
jbe@0:         UNIQUE ("initiative_id", "id"),  -- index needed for foreign-key on table "opinion"
jbe@0:         "initiative_id"         INT4            NOT NULL REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0:         "id"                    SERIAL8         PRIMARY KEY,
jbe@0:         "created"               TIMESTAMPTZ     NOT NULL DEFAULT now(),
jbe@0:         "author_id"             INT4            NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
jbe@0:         "name"                  TEXT            NOT NULL,
jbe@0:         "description"           TEXT            NOT NULL DEFAULT '',
jbe@0:         "minus2_unfulfilled_count" INT4,
jbe@0:         "minus2_fulfilled_count"   INT4,
jbe@0:         "minus1_unfulfilled_count" INT4,
jbe@0:         "minus1_fulfilled_count"   INT4,
jbe@0:         "plus1_unfulfilled_count"  INT4,
jbe@0:         "plus1_fulfilled_count"    INT4,
jbe@0:         "plus2_unfulfilled_count"  INT4,
jbe@0:         "plus2_fulfilled_count"    INT4 );
jbe@0: 
jbe@0: COMMENT ON TABLE "suggestion" IS 'Suggestions to initiators, to change the current draft';
jbe@0: 
jbe@0: COMMENT ON COLUMN "suggestion"."minus2_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
jbe@0: COMMENT ON COLUMN "suggestion"."minus2_fulfilled_count"   IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
jbe@0: COMMENT ON COLUMN "suggestion"."minus1_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
jbe@0: COMMENT ON COLUMN "suggestion"."minus1_fulfilled_count"   IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
jbe@0: COMMENT ON COLUMN "suggestion"."plus1_unfulfilled_count"  IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
jbe@0: COMMENT ON COLUMN "suggestion"."plus1_fulfilled_count"    IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
jbe@0: COMMENT ON COLUMN "suggestion"."plus2_unfulfilled_count"  IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
jbe@0: COMMENT ON COLUMN "suggestion"."plus2_fulfilled_count"    IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
jbe@0: 
jbe@0: 
jbe@0: CREATE TABLE "membership" (
jbe@0:         PRIMARY KEY ("area_id", "member_id"),
jbe@0:         "area_id"               INT4            REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0:         "member_id"             INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0:         "autoreject"            BOOLEAN         NOT NULL DEFAULT FALSE );
jbe@0: CREATE INDEX "membership_member_id_idx" ON "membership" ("member_id");
jbe@0: 
jbe@0: COMMENT ON TABLE "membership" IS 'Interest of members in topic areas';
jbe@0: 
jbe@0: COMMENT ON COLUMN "membership"."autoreject" IS 'TRUE = member votes against all initiatives in case of not explicitly taking part in the voting procedure; If there exists an "interest" entry, the interest entry has precedence';
jbe@0: 
jbe@0: 
jbe@0: CREATE TABLE "interest" (
jbe@0:         PRIMARY KEY ("issue_id", "member_id"),
jbe@0:         "issue_id"              INT4            REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0:         "member_id"             INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0:         "autoreject"            BOOLEAN         NOT NULL,
jbe@0:         "voting_requested"      BOOLEAN );
jbe@0: CREATE INDEX "interest_member_id_idx" ON "interest" ("member_id");
jbe@0: 
jbe@0: COMMENT ON TABLE "interest" IS 'Interest of members in a particular issue';
jbe@0: 
jbe@0: COMMENT ON COLUMN "interest"."autoreject"       IS 'TRUE = member votes against all initiatives in case of not explicitly taking part in the voting procedure';
jbe@0: COMMENT ON COLUMN "interest"."voting_requested" IS 'TRUE = member wants to vote now, FALSE = member wants to vote later, NULL = policy rules should apply';
jbe@0: 
jbe@0: 
jbe@0: CREATE TABLE "initiator" (
jbe@0:         PRIMARY KEY ("initiative_id", "member_id"),
jbe@0:         "initiative_id"         INT4            REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0:         "member_id"             INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0:         "accepted"              BOOLEAN         NOT NULL DEFAULT TRUE );
jbe@0: CREATE INDEX "initiator_member_id_idx" ON "initiator" ("member_id");
jbe@0: 
jbe@0: COMMENT ON TABLE "initiator" IS 'Members who are allowed to post new drafts';
jbe@0: 
jbe@0: COMMENT ON COLUMN "initiator"."accepted" IS 'If "accepted" = FALSE, then the member was invited to be a co-initiator, but has not answered yet.';
jbe@0: 
jbe@0: 
jbe@0: CREATE TABLE "supporter" (
jbe@0:         "issue_id"              INT4            NOT NULL,
jbe@0:         PRIMARY KEY ("initiative_id", "member_id"),
jbe@0:         "initiative_id"         INT4,
jbe@0:         "member_id"             INT4,
jbe@0:         "draft_id"              INT8            NOT NULL,
jbe@0:         FOREIGN KEY ("issue_id", "member_id") REFERENCES "interest" ("issue_id", "member_id") ON DELETE RESTRICT ON UPDATE CASCADE,
jbe@0:         FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE CASCADE ON UPDATE CASCADE );
jbe@0: CREATE INDEX "supporter_member_id_idx" ON "supporter" ("member_id");
jbe@0: 
jbe@0: COMMENT ON TABLE "supporter" IS 'Members who support an initiative (conditionally)';
jbe@0: 
jbe@2: COMMENT ON COLUMN "supporter"."draft_id" IS 'Latest seen draft, defaults to current draft of the initiative (implemented by trigger "default_for_draft_id")';
jbe@0: 
jbe@0: 
jbe@0: CREATE TABLE "opinion" (
jbe@0:         "initiative_id"         INT4            NOT NULL,
jbe@0:         PRIMARY KEY ("suggestion_id", "member_id"),
jbe@0:         "suggestion_id"         INT8,
jbe@0:         "member_id"             INT4,
jbe@0:         "degree"                INT2            NOT NULL CHECK ("degree" >= -2 AND "degree" <= 2 AND "degree" != 0),
jbe@0:         "fulfilled"             BOOLEAN         NOT NULL DEFAULT FALSE,
jbe@0:         FOREIGN KEY ("initiative_id", "suggestion_id") REFERENCES "suggestion" ("initiative_id", "id") ON DELETE RESTRICT ON UPDATE CASCADE,
jbe@0:         FOREIGN KEY ("initiative_id", "member_id") REFERENCES "supporter" ("initiative_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
jbe@0: CREATE INDEX "opinion_member_id_idx" ON "opinion" ("member_id");
jbe@0: 
jbe@0: COMMENT ON TABLE "opinion" IS 'Opinion on suggestions (criticism related to initiatives)';
jbe@0: 
jbe@0: COMMENT ON COLUMN "opinion"."degree" IS '2 = fulfillment required for support; 1 = fulfillment desired; -1 = fulfillment unwanted; -2 = fulfillment cancels support';
jbe@0: 
jbe@0: 
jbe@0: CREATE TABLE "delegation" (
jbe@0:         "id"                    SERIAL8         PRIMARY KEY,
jbe@0:         "truster_id"            INT4            NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0:         "trustee_id"            INT4            NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0:         "area_id"               INT4            REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0:         "issue_id"              INT4            REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0:         CONSTRAINT "cant_delegate_to_yourself" CHECK ("truster_id" != "trustee_id"),
jbe@0:         CONSTRAINT "area_id_or_issue_id_can_be_set_but_not_both" CHECK ("area_id" ISNULL OR "issue_id" ISNULL),
jbe@0:         UNIQUE ("area_id", "truster_id", "trustee_id"),
jbe@0:         UNIQUE ("issue_id", "truster_id", "trustee_id") );
jbe@0: CREATE UNIQUE INDEX "delegation_default_truster_id_trustee_id_unique_idx"
jbe@0:   ON "delegation" ("truster_id", "trustee_id")
jbe@0:   WHERE "area_id" ISNULL AND "issue_id" ISNULL;
jbe@0: CREATE INDEX "delegation_truster_id_idx" ON "delegation" ("truster_id");
jbe@0: CREATE INDEX "delegation_trustee_id_idx" ON "delegation" ("trustee_id");
jbe@0: 
jbe@0: COMMENT ON TABLE "delegation" IS 'Delegation of vote-weight to other members';
jbe@0: 
jbe@0: COMMENT ON COLUMN "delegation"."area_id"  IS 'Reference to area, if delegation is area-wide, otherwise NULL';
jbe@0: COMMENT ON COLUMN "delegation"."issue_id" IS 'Reference to issue, if delegation is issue-wide, otherwise NULL';
jbe@0: 
jbe@0: 
jbe@3: CREATE TYPE "snapshot_event" AS ENUM ('periodic', 'end_of_admission', 'start_of_voting');
jbe@0: 
jbe@3: COMMENT ON TYPE "snapshot_event" IS 'Reason for snapshots: ''periodic'' = due to periodic recalculation, ''end_of_admission'' = saved state at end of admission period, ''start_of_voting'' = saved state at end of verification period';
jbe@0: 
jbe@0: 
jbe@0: CREATE TABLE "direct_population_snapshot" (
jbe@0:         PRIMARY KEY ("issue_id", "event", "member_id"),
jbe@0:         "issue_id"              INT4            REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0:         "event"                 "snapshot_event",
jbe@0:         "member_id"             INT4            REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
jbe@0:         "weight"                INT4,
jbe@0:         "interest_exists"       BOOLEAN         NOT NULL );
jbe@0: CREATE INDEX "direct_population_snapshot_member_id_idx" ON "direct_population_snapshot" ("member_id");
jbe@0: 
jbe@0: COMMENT ON TABLE "direct_population_snapshot" IS 'Snapshot of active members having either a "membership" in the "area" or an "interest" in the "issue"';
jbe@0: 
jbe@0: COMMENT ON COLUMN "direct_population_snapshot"."event"           IS 'Reason for snapshot, see "snapshot_event" type for details';
jbe@0: COMMENT ON COLUMN "direct_population_snapshot"."weight"          IS 'Weight of member (1 or higher) according to "delegating_population_snapshot"';
jbe@0: COMMENT ON COLUMN "direct_population_snapshot"."interest_exists" IS 'TRUE if entry is due to interest in issue, FALSE if entry is only due to membership in area';
jbe@0: 
jbe@0: 
jbe@0: CREATE TABLE "delegating_population_snapshot" (
jbe@0:         PRIMARY KEY ("issue_id", "event", "member_id"),
jbe@0:         "issue_id"              INT4            REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0:         "event"                "snapshot_event",
jbe@0:         "member_id"             INT4            REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
jbe@0:         "delegate_member_ids"   INT4[]          NOT NULL );
jbe@0: CREATE INDEX "delegating_population_snapshot_member_id_idx" ON "delegating_population_snapshot" ("member_id");
jbe@0: 
jbe@0: COMMENT ON TABLE "direct_population_snapshot" IS 'Delegations increasing the weight of entries in the "direct_population_snapshot" table';
jbe@0: 
jbe@0: COMMENT ON COLUMN "delegating_population_snapshot"."event"               IS 'Reason for snapshot, see "snapshot_event" type for details';
jbe@0: COMMENT ON COLUMN "delegating_population_snapshot"."member_id"           IS 'Delegating member';
jbe@0: COMMENT ON COLUMN "delegating_population_snapshot"."delegate_member_ids" IS 'Chain of members who act as delegates; last entry referes to "member_id" column of table "direct_population_snapshot"';
jbe@0: 
jbe@0: 
jbe@0: CREATE TABLE "direct_interest_snapshot" (
jbe@0:         PRIMARY KEY ("issue_id", "event", "member_id"),
jbe@0:         "issue_id"              INT4            REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0:         "event"                 "snapshot_event",
jbe@0:         "member_id"             INT4            REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
jbe@0:         "weight"                INT4,
jbe@0:         "voting_requested"      BOOLEAN );
jbe@0: CREATE INDEX "direct_interest_snapshot_member_id_idx" ON "direct_interest_snapshot" ("member_id");
jbe@0: 
jbe@0: COMMENT ON TABLE "direct_interest_snapshot" IS 'Snapshot of active members having an "interest" in the "issue"';
jbe@0: 
jbe@0: COMMENT ON COLUMN "direct_interest_snapshot"."event"            IS 'Reason for snapshot, see "snapshot_event" type for details';
jbe@0: COMMENT ON COLUMN "direct_interest_snapshot"."weight"           IS 'Weight of member (1 or higher) according to "delegating_interest_snapshot"';
jbe@0: COMMENT ON COLUMN "direct_interest_snapshot"."voting_requested" IS 'Copied from column "voting_requested" of table "interest"';
jbe@0: 
jbe@0: 
jbe@0: CREATE TABLE "delegating_interest_snapshot" (
jbe@0:         PRIMARY KEY ("issue_id", "event", "member_id"),
jbe@0:         "issue_id"         INT4                 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0:         "event"                "snapshot_event",
jbe@0:         "member_id"             INT4            REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
jbe@0:         "delegate_member_ids"   INT4[]          NOT NULL );
jbe@0: CREATE INDEX "delegating_interest_snapshot_member_id_idx" ON "delegating_interest_snapshot" ("member_id");
jbe@0: 
jbe@0: COMMENT ON TABLE "delegating_interest_snapshot" IS 'Delegations increasing the weight of entries in the "direct_interest_snapshot" table';
jbe@0: 
jbe@0: COMMENT ON COLUMN "delegating_interest_snapshot"."event"               IS 'Reason for snapshot, see "snapshot_event" type for details';
jbe@0: COMMENT ON COLUMN "delegating_interest_snapshot"."member_id"           IS 'Delegating member';
jbe@0: COMMENT ON COLUMN "delegating_interest_snapshot"."delegate_member_ids" IS 'Chain of members who act as delegates; last entry referes to "member_id" column of table "direct_interest_snapshot"';
jbe@0: 
jbe@0: 
jbe@0: CREATE TABLE "direct_supporter_snapshot" (
jbe@0:         "issue_id"              INT4            NOT NULL,
jbe@0:         PRIMARY KEY ("initiative_id", "event", "member_id"),
jbe@0:         "initiative_id"         INT4,
jbe@0:         "event"                 "snapshot_event",
jbe@0:         "member_id"             INT4            REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
jbe@0:         "informed"              BOOLEAN         NOT NULL,
jbe@0:         "satisfied"             BOOLEAN         NOT NULL,
jbe@0:         FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0:         FOREIGN KEY ("issue_id", "event", "member_id") REFERENCES "direct_interest_snapshot" ("issue_id", "event", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
jbe@0: CREATE INDEX "direct_supporter_snapshot_member_id_idx" ON "direct_supporter_snapshot" ("member_id");
jbe@0: 
jbe@0: COMMENT ON TABLE "direct_supporter_snapshot" IS 'Snapshot of supporters of initiatives (weight is stored in "direct_interest_snapshot)';
jbe@0: 
jbe@0: COMMENT ON COLUMN "direct_supporter_snapshot"."event"     IS 'Reason for snapshot, see "snapshot_event" type for details';
jbe@0: COMMENT ON COLUMN "direct_supporter_snapshot"."informed"  IS 'Supporter has seen the latest draft of the initiative';
jbe@0: COMMENT ON COLUMN "direct_supporter_snapshot"."satisfied" IS 'Supporter has no "critical_opinion"s';
jbe@0: 
jbe@0: 
jbe@0: CREATE TABLE "direct_voter" (
jbe@0:         PRIMARY KEY ("issue_id", "member_id"),
jbe@0:         "issue_id"              INT4            REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0:         "member_id"             INT4            REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
jbe@0:         "weight"                INT4,
jbe@0:         "autoreject"            BOOLEAN         NOT NULL DEFAULT FALSE );
jbe@0: CREATE INDEX "direct_voter_member_id_idx" ON "direct_voter" ("member_id");
jbe@0: 
jbe@0: COMMENT ON TABLE "direct_voter" IS 'Members having directly voted for/against initiatives of an issue';
jbe@0: 
jbe@0: COMMENT ON COLUMN "direct_voter"."weight"     IS 'Weight of member (1 or higher) according to "delegating_voter" table';
jbe@0: COMMENT ON COLUMN "direct_voter"."autoreject" IS 'Votes were inserted due to "autoreject" feature';
jbe@0: 
jbe@0: 
jbe@0: CREATE TABLE "delegating_voter" (
jbe@0:         PRIMARY KEY ("issue_id", "member_id"),
jbe@0:         "issue_id"              INT4            REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0:         "member_id"             INT4            REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
jbe@0:         "delegate_member_ids"   INT4[]          NOT NULL );
jbe@0: CREATE INDEX "delegating_voter_member_id_idx" ON "direct_voter" ("member_id");
jbe@0: 
jbe@0: COMMENT ON TABLE "delegating_voter" IS 'Delegations increasing the weight of entries in the "direct_voter" table';
jbe@0: 
jbe@0: COMMENT ON COLUMN "delegating_voter"."member_id"           IS 'Delegating member';
jbe@0: COMMENT ON COLUMN "delegating_voter"."delegate_member_ids" IS 'Chain of members who act as delegates; last entry referes to "member_id" column of table "direct_voter"';
jbe@0: 
jbe@0: 
jbe@0: CREATE TABLE "vote" (
jbe@0:         "issue_id"              INT4            NOT NULL,
jbe@0:         PRIMARY KEY ("initiative_id", "member_id"),
jbe@0:         "initiative_id"         INT4,
jbe@0:         "member_id"             INT4,
jbe@0:         "grade"                 INT4,
jbe@0:         FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@0:         FOREIGN KEY ("issue_id", "member_id") REFERENCES "direct_voter" ("issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
jbe@0: CREATE INDEX "vote_member_id_idx" ON "vote" ("member_id");
jbe@0: 
jbe@0: COMMENT ON TABLE "vote" IS 'Manual and delegated votes without abstentions';
jbe@0: 
jbe@0: COMMENT ON COLUMN "vote"."grade" IS 'Values smaller than zero mean reject, values greater than zero mean acceptance, zero or missing row means abstention. Preferences are expressed by different positive or negative numbers.';
jbe@0: 
jbe@0: 
jbe@0: 
jbe@0: ----------------------------
jbe@0: -- Additional constraints --
jbe@0: ----------------------------
jbe@0: 
jbe@0: 
jbe@0: CREATE FUNCTION "issue_requires_first_initiative_trigger"()
jbe@0:   RETURNS TRIGGER
jbe@0:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0:     BEGIN
jbe@0:       IF NOT EXISTS (
jbe@0:         SELECT NULL FROM "initiative" WHERE "issue_id" = NEW."id"
jbe@0:       ) THEN
jbe@0:         --RAISE 'Cannot create issue without an initial initiative.' USING
jbe@0:         --  ERRCODE = 'integrity_constraint_violation',
jbe@0:         --  HINT    = 'Create issue, initiative, and draft within the same transaction.';
jbe@0:         RAISE EXCEPTION 'Cannot create issue without an initial initiative.';
jbe@0:       END IF;
jbe@0:       RETURN NULL;
jbe@0:     END;
jbe@0:   $$;
jbe@0: 
jbe@0: CREATE CONSTRAINT TRIGGER "issue_requires_first_initiative"
jbe@0:   AFTER INSERT OR UPDATE ON "issue" DEFERRABLE INITIALLY DEFERRED
jbe@0:   FOR EACH ROW EXECUTE PROCEDURE
jbe@0:   "issue_requires_first_initiative_trigger"();
jbe@0: 
jbe@0: COMMENT ON FUNCTION "issue_requires_first_initiative_trigger"() IS 'Implementation of trigger "issue_requires_first_initiative" on table "issue"';
jbe@0: COMMENT ON TRIGGER "issue_requires_first_initiative" ON "issue" IS 'Ensure that new issues have at least one initiative';
jbe@0: 
jbe@0: 
jbe@0: CREATE FUNCTION "last_initiative_deletes_issue_trigger"()
jbe@0:   RETURNS TRIGGER
jbe@0:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0:     DECLARE
jbe@0:       "reference_lost" BOOLEAN;
jbe@0:     BEGIN
jbe@0:       IF TG_OP = 'DELETE' THEN
jbe@0:         "reference_lost" := TRUE;
jbe@0:       ELSE
jbe@0:         "reference_lost" := NEW."issue_id" != OLD."issue_id";
jbe@0:       END IF;
jbe@0:       IF
jbe@0:         "reference_lost" AND NOT EXISTS (
jbe@0:           SELECT NULL FROM "initiative" WHERE "issue_id" = OLD."issue_id"
jbe@0:         )
jbe@0:       THEN
jbe@0:         DELETE FROM "issue" WHERE "id" = OLD."issue_id";
jbe@0:       END IF;
jbe@0:       RETURN NULL;
jbe@0:     END;
jbe@0:   $$;
jbe@0: 
jbe@0: CREATE CONSTRAINT TRIGGER "last_initiative_deletes_issue"
jbe@0:   AFTER UPDATE OR DELETE ON "initiative" DEFERRABLE INITIALLY DEFERRED
jbe@0:   FOR EACH ROW EXECUTE PROCEDURE
jbe@0:   "last_initiative_deletes_issue_trigger"();
jbe@0: 
jbe@0: COMMENT ON FUNCTION "last_initiative_deletes_issue_trigger"()      IS 'Implementation of trigger "last_initiative_deletes_issue" on table "initiative"';
jbe@0: COMMENT ON TRIGGER "last_initiative_deletes_issue" ON "initiative" IS 'Removing the last initiative of an issue deletes the issue';
jbe@0: 
jbe@0: 
jbe@0: CREATE FUNCTION "initiative_requires_first_draft_trigger"()
jbe@0:   RETURNS TRIGGER
jbe@0:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0:     BEGIN
jbe@0:       IF NOT EXISTS (
jbe@0:         SELECT NULL FROM "draft" WHERE "initiative_id" = NEW."id"
jbe@0:       ) THEN
jbe@0:         --RAISE 'Cannot create initiative without an initial draft.' USING
jbe@0:         --  ERRCODE = 'integrity_constraint_violation',
jbe@0:         --  HINT    = 'Create issue, initiative and draft within the same transaction.';
jbe@0:         RAISE EXCEPTION 'Cannot create initiative without an initial draft.';
jbe@0:       END IF;
jbe@0:       RETURN NULL;
jbe@0:     END;
jbe@0:   $$;
jbe@0: 
jbe@0: CREATE CONSTRAINT TRIGGER "initiative_requires_first_draft"
jbe@0:   AFTER INSERT OR UPDATE ON "initiative" DEFERRABLE INITIALLY DEFERRED
jbe@0:   FOR EACH ROW EXECUTE PROCEDURE
jbe@0:   "initiative_requires_first_draft_trigger"();
jbe@0: 
jbe@0: COMMENT ON FUNCTION "initiative_requires_first_draft_trigger"()      IS 'Implementation of trigger "initiative_requires_first_draft" on table "initiative"';
jbe@0: COMMENT ON TRIGGER "initiative_requires_first_draft" ON "initiative" IS 'Ensure that new initiatives have at least one draft';
jbe@0: 
jbe@0: 
jbe@0: CREATE FUNCTION "last_draft_deletes_initiative_trigger"()
jbe@0:   RETURNS TRIGGER
jbe@0:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0:     DECLARE
jbe@0:       "reference_lost" BOOLEAN;
jbe@0:     BEGIN
jbe@0:       IF TG_OP = 'DELETE' THEN
jbe@0:         "reference_lost" := TRUE;
jbe@0:       ELSE
jbe@0:         "reference_lost" := NEW."initiative_id" != OLD."initiative_id";
jbe@0:       END IF;
jbe@0:       IF
jbe@0:         "reference_lost" AND NOT EXISTS (
jbe@0:           SELECT NULL FROM "draft" WHERE "initiative_id" = OLD."initiative_id"
jbe@0:         )
jbe@0:       THEN
jbe@0:         DELETE FROM "initiative" WHERE "id" = OLD."initiative_id";
jbe@0:       END IF;
jbe@0:       RETURN NULL;
jbe@0:     END;
jbe@0:   $$;
jbe@0: 
jbe@0: CREATE CONSTRAINT TRIGGER "last_draft_deletes_initiative"
jbe@0:   AFTER UPDATE OR DELETE ON "draft" DEFERRABLE INITIALLY DEFERRED
jbe@0:   FOR EACH ROW EXECUTE PROCEDURE
jbe@0:   "last_draft_deletes_initiative_trigger"();
jbe@0: 
jbe@0: COMMENT ON FUNCTION "last_draft_deletes_initiative_trigger"() IS 'Implementation of trigger "last_draft_deletes_initiative" on table "draft"';
jbe@0: COMMENT ON TRIGGER "last_draft_deletes_initiative" ON "draft" IS 'Removing the last draft of an initiative deletes the initiative';
jbe@0: 
jbe@0: 
jbe@0: CREATE FUNCTION "suggestion_requires_first_opinion_trigger"()
jbe@0:   RETURNS TRIGGER
jbe@0:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0:     BEGIN
jbe@0:       IF NOT EXISTS (
jbe@0:         SELECT NULL FROM "opinion" WHERE "suggestion_id" = NEW."id"
jbe@0:       ) THEN
jbe@0:         RAISE EXCEPTION 'Cannot create a suggestion without an opinion.';
jbe@0:       END IF;
jbe@0:       RETURN NULL;
jbe@0:     END;
jbe@0:   $$;
jbe@0: 
jbe@0: CREATE CONSTRAINT TRIGGER "suggestion_requires_first_opinion"
jbe@0:   AFTER INSERT OR UPDATE ON "suggestion" DEFERRABLE INITIALLY DEFERRED
jbe@0:   FOR EACH ROW EXECUTE PROCEDURE
jbe@0:   "suggestion_requires_first_opinion_trigger"();
jbe@0: 
jbe@0: COMMENT ON FUNCTION "suggestion_requires_first_opinion_trigger"()      IS 'Implementation of trigger "suggestion_requires_first_opinion" on table "suggestion"';
jbe@0: COMMENT ON TRIGGER "suggestion_requires_first_opinion" ON "suggestion" IS 'Ensure that new suggestions have at least one opinion';
jbe@0: 
jbe@0: 
jbe@0: CREATE FUNCTION "last_opinion_deletes_suggestion_trigger"()
jbe@0:   RETURNS TRIGGER
jbe@0:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0:     DECLARE
jbe@0:       "reference_lost" BOOLEAN;
jbe@0:     BEGIN
jbe@0:       IF TG_OP = 'DELETE' THEN
jbe@0:         "reference_lost" := TRUE;
jbe@0:       ELSE
jbe@0:         "reference_lost" := NEW."suggestion_id" != OLD."suggestion_id";
jbe@0:       END IF;
jbe@0:       IF
jbe@0:         "reference_lost" AND NOT EXISTS (
jbe@0:           SELECT NULL FROM "opinion" WHERE "suggestion_id" = OLD."suggestion_id"
jbe@0:         )
jbe@0:       THEN
jbe@0:         DELETE FROM "suggestion" WHERE "id" = OLD."suggestion_id";
jbe@0:       END IF;
jbe@0:       RETURN NULL;
jbe@0:     END;
jbe@0:   $$;
jbe@0: 
jbe@0: CREATE CONSTRAINT TRIGGER "last_opinion_deletes_suggestion"
jbe@0:   AFTER UPDATE OR DELETE ON "opinion" DEFERRABLE INITIALLY DEFERRED
jbe@0:   FOR EACH ROW EXECUTE PROCEDURE
jbe@0:   "last_opinion_deletes_suggestion_trigger"();
jbe@0: 
jbe@0: COMMENT ON FUNCTION "last_opinion_deletes_suggestion_trigger"()   IS 'Implementation of trigger "last_opinion_deletes_suggestion" on table "opinion"';
jbe@0: COMMENT ON TRIGGER "last_opinion_deletes_suggestion" ON "opinion" IS 'Removing the last opinion of a suggestion deletes the suggestion';
jbe@0: 
jbe@0: 
jbe@0: 
jbe@0: --------------------------------------------------------------------
jbe@0: -- Auto-retrieval of fields only needed for referential integrity --
jbe@0: --------------------------------------------------------------------
jbe@0: 
jbe@0: CREATE FUNCTION "autofill_issue_id_trigger"()
jbe@0:   RETURNS TRIGGER
jbe@0:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0:     BEGIN
jbe@0:       IF NEW."issue_id" ISNULL THEN
jbe@0:         SELECT "issue_id" INTO NEW."issue_id"
jbe@0:           FROM "initiative" WHERE "id" = NEW."initiative_id";
jbe@0:       END IF;
jbe@0:       RETURN NEW;
jbe@0:     END;
jbe@0:   $$;
jbe@0: 
jbe@0: CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "supporter"
jbe@0:   FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();
jbe@0: 
jbe@0: CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "vote"
jbe@0:   FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();
jbe@0: 
jbe@0: COMMENT ON FUNCTION "autofill_issue_id_trigger"()     IS 'Implementation of triggers "autofill_issue_id" on tables "supporter" and "vote"';
jbe@0: COMMENT ON TRIGGER "autofill_issue_id" ON "supporter" IS 'Set "issue_id" field automatically, if NULL';
jbe@0: COMMENT ON TRIGGER "autofill_issue_id" ON "vote"      IS 'Set "issue_id" field automatically, if NULL';
jbe@0: 
jbe@0: 
jbe@0: CREATE FUNCTION "autofill_initiative_id_trigger"()
jbe@0:   RETURNS TRIGGER
jbe@0:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0:     BEGIN
jbe@0:       IF NEW."initiative_id" ISNULL THEN
jbe@0:         SELECT "initiative_id" INTO NEW."initiative_id"
jbe@0:           FROM "suggestion" WHERE "id" = NEW."suggestion_id";
jbe@0:       END IF;
jbe@0:       RETURN NEW;
jbe@0:     END;
jbe@0:   $$;
jbe@0: 
jbe@0: CREATE TRIGGER "autofill_initiative_id" BEFORE INSERT ON "opinion"
jbe@0:   FOR EACH ROW EXECUTE PROCEDURE "autofill_initiative_id_trigger"();
jbe@0: 
jbe@0: COMMENT ON FUNCTION "autofill_initiative_id_trigger"()   IS 'Implementation of trigger "autofill_initiative_id" on table "opinion"';
jbe@0: COMMENT ON TRIGGER "autofill_initiative_id" ON "opinion" IS 'Set "initiative_id" field automatically, if NULL';
jbe@0: 
jbe@0: 
jbe@0: 
jbe@4: -----------------------------------------------------
jbe@4: -- Automatic calculation of certain default values --
jbe@4: -----------------------------------------------------
jbe@0: 
jbe@0: CREATE FUNCTION "copy_autoreject_trigger"()
jbe@0:   RETURNS TRIGGER
jbe@0:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0:     BEGIN
jbe@0:       IF NEW."autoreject" ISNULL THEN
jbe@0:         SELECT "membership"."autoreject" INTO NEW."autoreject"
jbe@0:           FROM "issue" JOIN "membership"
jbe@0:           ON "issue"."area_id" = "membership"."area_id"
jbe@0:           WHERE "issue"."id" = NEW."issue_id"
jbe@0:           AND "membership"."member_id" = NEW."member_id";
jbe@0:       END IF;
jbe@0:       IF NEW."autoreject" ISNULL THEN 
jbe@0:         NEW."autoreject" := FALSE;
jbe@0:       END IF;
jbe@0:       RETURN NEW;
jbe@0:     END;
jbe@0:   $$;
jbe@0: 
jbe@0: CREATE TRIGGER "copy_autoreject" BEFORE INSERT OR UPDATE ON "interest"
jbe@0:   FOR EACH ROW EXECUTE PROCEDURE "copy_autoreject_trigger"();
jbe@0: 
jbe@0: COMMENT ON FUNCTION "copy_autoreject_trigger"()    IS 'Implementation of trigger "copy_autoreject" on table "interest"';
jbe@0: COMMENT ON TRIGGER "copy_autoreject" ON "interest" IS 'If "autoreject" is NULL, then copy it from the area setting, or set to FALSE, if no membership existent';
jbe@0: 
jbe@0: 
jbe@2: CREATE FUNCTION "supporter_default_for_draft_id_trigger"()
jbe@2:   RETURNS TRIGGER
jbe@2:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@2:     BEGIN
jbe@2:       IF NEW."draft_id" ISNULL THEN
jbe@2:         SELECT "id" INTO NEW."draft_id" FROM "current_draft"
jbe@2:           WHERE "initiative_id" = NEW."initiative_id";
jbe@2:       END IF;
jbe@2:       RETURN NEW;
jbe@2:     END;
jbe@2:   $$;
jbe@2: 
jbe@2: CREATE TRIGGER "default_for_draft_id" BEFORE INSERT OR UPDATE ON "supporter"
jbe@2:   FOR EACH ROW EXECUTE PROCEDURE "supporter_default_for_draft_id_trigger"();
jbe@2: 
jbe@2: COMMENT ON FUNCTION "supporter_default_for_draft_id_trigger"() IS 'Implementation of trigger "default_for_draft" on table "supporter"';
jbe@2: COMMENT ON TRIGGER "default_for_draft_id" ON "supporter"       IS 'If "draft_id" is NULL, then use the current draft of the initiative as default';
jbe@2: 
jbe@2: 
jbe@0: 
jbe@0: ----------------------------------------
jbe@0: -- Automatic creation of dependencies --
jbe@0: ----------------------------------------
jbe@0: 
jbe@0: CREATE FUNCTION "autocreate_interest_trigger"()
jbe@0:   RETURNS TRIGGER
jbe@0:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0:     BEGIN
jbe@0:       IF NOT EXISTS (
jbe@0:         SELECT NULL FROM "initiative" JOIN "interest"
jbe@0:         ON "initiative"."issue_id" = "interest"."issue_id"
jbe@0:         WHERE "initiative"."id" = NEW."initiative_id"
jbe@0:         AND "interest"."member_id" = NEW."member_id"
jbe@0:       ) THEN
jbe@0:         BEGIN
jbe@0:           INSERT INTO "interest" ("issue_id", "member_id")
jbe@0:             SELECT "issue_id", NEW."member_id"
jbe@0:             FROM "initiative" WHERE "id" = NEW."initiative_id";
jbe@0:         EXCEPTION WHEN unique_violation THEN END;
jbe@0:       END IF;
jbe@0:       RETURN NEW;
jbe@0:     END;
jbe@0:   $$;
jbe@0: 
jbe@0: CREATE TRIGGER "autocreate_interest" BEFORE INSERT ON "supporter"
jbe@0:   FOR EACH ROW EXECUTE PROCEDURE "autocreate_interest_trigger"();
jbe@0: 
jbe@0: COMMENT ON FUNCTION "autocreate_interest_trigger"()     IS 'Implementation of trigger "autocreate_interest" on table "supporter"';
jbe@0: COMMENT ON TRIGGER "autocreate_interest" ON "supporter" IS 'Supporting an initiative implies interest in the issue, thus automatically creates an entry in the "interest" table';
jbe@0: 
jbe@0: 
jbe@0: CREATE FUNCTION "autocreate_supporter_trigger"()
jbe@0:   RETURNS TRIGGER
jbe@0:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0:     BEGIN
jbe@0:       IF NOT EXISTS (
jbe@0:         SELECT NULL FROM "suggestion" JOIN "supporter"
jbe@0:         ON "suggestion"."initiative_id" = "supporter"."initiative_id"
jbe@0:         WHERE "suggestion"."id" = NEW."suggestion_id"
jbe@0:         AND "supporter"."member_id" = NEW."member_id"
jbe@0:       ) THEN
jbe@0:         BEGIN
jbe@0:           INSERT INTO "supporter" ("initiative_id", "member_id")
jbe@0:             SELECT "initiative_id", NEW."member_id"
jbe@0:             FROM "suggestion" WHERE "id" = NEW."suggestion_id";
jbe@0:         EXCEPTION WHEN unique_violation THEN END;
jbe@0:       END IF;
jbe@0:       RETURN NEW;
jbe@0:     END;
jbe@0:   $$;
jbe@0: 
jbe@0: CREATE TRIGGER "autocreate_supporter" BEFORE INSERT ON "opinion"
jbe@0:   FOR EACH ROW EXECUTE PROCEDURE "autocreate_supporter_trigger"();
jbe@0: 
jbe@0: COMMENT ON FUNCTION "autocreate_supporter_trigger"()   IS 'Implementation of trigger "autocreate_supporter" on table "opinion"';
jbe@0: COMMENT ON TRIGGER "autocreate_supporter" ON "opinion" IS 'Opinions can only be added for supported initiatives. This trigger automatrically creates an entry in the "supporter" table, if not existent yet.';
jbe@0: 
jbe@0: 
jbe@0: 
jbe@0: ------------------------------------------
jbe@0: -- Views and helper functions for views --
jbe@0: ------------------------------------------
jbe@0: 
jbe@5: 
jbe@5: CREATE TYPE "delegation_scope" AS ENUM
jbe@5:   ('global', 'area', 'issue');
jbe@5: 
jbe@5: COMMENT ON TYPE "delegation_scope" IS 'Scope for delegations: ''global'', ''area'', or ''issue''';
jbe@5: 
jbe@5: 
jbe@5: CREATE VIEW "global_delegation" AS
jbe@5:   SELECT
jbe@5:     "delegation"."id",
jbe@5:     "delegation"."truster_id",
jbe@5:     "delegation"."trustee_id"
jbe@5:   FROM "delegation" JOIN "member"
jbe@5:   ON "delegation"."trustee_id" = "member"."id"
jbe@5:   WHERE "delegation"."area_id" ISNULL
jbe@5:   AND "delegation"."issue_id" ISNULL
jbe@5:   AND "member"."active";
jbe@5: 
jbe@5: COMMENT ON VIEW "global_delegation" IS 'Global delegations to active members';
jbe@5: 
jbe@5: 
jbe@5: CREATE VIEW "area_delegation" AS
jbe@5:   SELECT "subquery".* FROM (
jbe@5:     SELECT DISTINCT ON ("area"."id", "delegation"."truster_id")
jbe@5:       "area"."id"               AS "area_id",
jbe@5:       "delegation"."id"         AS "id",
jbe@5:       "delegation"."truster_id" AS "truster_id",
jbe@5:       "delegation"."trustee_id" AS "trustee_id",
jbe@5:       CASE WHEN "delegation"."area_id" ISNULL THEN
jbe@5:         'global'::"delegation_scope"
jbe@5:       ELSE
jbe@5:         'area'::"delegation_scope"
jbe@5:       END AS "scope"
jbe@5:     FROM "area" JOIN "delegation"
jbe@5:     ON ("delegation"."area_id" ISNULL OR "delegation"."area_id" = "area"."id")
jbe@5:     AND "delegation"."issue_id" ISNULL
jbe@5:     ORDER BY
jbe@5:       "area"."id",
jbe@5:       "delegation"."truster_id",
jbe@5:       "delegation"."area_id" NULLS LAST
jbe@5:   ) AS "subquery"
jbe@5:   JOIN "member" ON "subquery"."trustee_id" = "member"."id"
jbe@5:   WHERE "member"."active";
jbe@5: 
jbe@5: COMMENT ON VIEW "area_delegation" IS 'Active delegations for areas';
jbe@5: 
jbe@5: 
jbe@5: CREATE VIEW "issue_delegation" AS
jbe@5:   SELECT "subquery".* FROM (
jbe@5:     SELECT DISTINCT ON ("issue"."id", "delegation"."truster_id")
jbe@5:       "issue"."id"              AS "issue_id",
jbe@5:       "delegation"."id"         AS "id",
jbe@5:       "delegation"."truster_id" AS "truster_id",
jbe@5:       "delegation"."trustee_id" AS "trustee_id",
jbe@5:       CASE
jbe@5:         WHEN
jbe@5:           "delegation"."area_id" ISNULL AND
jbe@5:           "delegation"."issue_id" ISNULL
jbe@5:         THEN 'global'::"delegation_scope"
jbe@5:         WHEN
jbe@5:           "delegation"."area_id" NOTNULL
jbe@5:         THEN 'area'::"delegation_scope"
jbe@5:         ELSE 'issue'::"delegation_scope"
jbe@5:       END AS "scope"
jbe@5:     FROM "issue" JOIN "delegation"
jbe@5:     ON (
jbe@5:       "delegation"."area_id" ISNULL OR
jbe@5:       "delegation"."area_id" = "issue"."area_id"
jbe@5:     ) AND (
jbe@5:       "delegation"."issue_id" ISNULL OR
jbe@5:       "delegation"."issue_id" = "issue"."id"
jbe@5:     )
jbe@5:     ORDER BY
jbe@5:       "issue"."id",
jbe@5:       "delegation"."truster_id",
jbe@5:       "delegation"."issue_id" NULLS LAST,
jbe@5:       "delegation"."area_id" NULLS LAST
jbe@5:   ) AS "subquery"
jbe@5:   JOIN "member" ON "subquery"."trustee_id" = "member"."id"
jbe@5:   WHERE "member"."active";
jbe@5: 
jbe@5: COMMENT ON VIEW "issue_delegation" IS 'Active delegations for issues';
jbe@5: 
jbe@5: 
jbe@5: CREATE FUNCTION "membership_weight_with_skipping"
jbe@5:   ( "area_id_p"         "area"."id"%TYPE,
jbe@5:     "member_id_p"       "member"."id"%TYPE,
jbe@5:     "skip_member_ids_p" INT4[] )  -- "member"."id"%TYPE[]
jbe@5:   RETURNS INT4
jbe@5:   LANGUAGE 'plpgsql' STABLE AS $$
jbe@5:     DECLARE
jbe@5:       "sum_v"          INT4;
jbe@5:       "delegation_row" "area_delegation"%ROWTYPE;
jbe@5:     BEGIN
jbe@5:       "sum_v" := 1;
jbe@5:       FOR "delegation_row" IN
jbe@5:         SELECT "area_delegation".*
jbe@5:         FROM "area_delegation" LEFT JOIN "membership"
jbe@5:         ON "membership"."area_id" = "area_id_p"
jbe@5:         AND "membership"."member_id" = "area_delegation"."truster_id"
jbe@5:         WHERE "area_delegation"."area_id" = "area_id_p"
jbe@5:         AND "area_delegation"."trustee_id" = "member_id_p"
jbe@5:         AND "membership"."member_id" ISNULL
jbe@5:       LOOP
jbe@5:         IF NOT
jbe@5:           "skip_member_ids_p" @> ARRAY["delegation_row"."truster_id"]
jbe@5:         THEN
jbe@5:           "sum_v" := "sum_v" + "membership_weight_with_skipping"(
jbe@5:             "area_id_p",
jbe@5:             "delegation_row"."truster_id",
jbe@5:             "skip_member_ids_p" || "delegation_row"."truster_id"
jbe@5:           );
jbe@5:         END IF;
jbe@5:       END LOOP;
jbe@5:       RETURN "sum_v";
jbe@5:     END;
jbe@5:   $$;
jbe@5: 
jbe@5: CREATE FUNCTION "membership_weight"
jbe@5:   ( "area_id_p"         "area"."id"%TYPE,
jbe@5:     "member_id_p"       "member"."id"%TYPE )  -- "member"."id"%TYPE[]
jbe@5:   RETURNS INT4
jbe@5:   LANGUAGE 'plpgsql' STABLE AS $$
jbe@5:     BEGIN
jbe@5:       RETURN "membership_weight_with_skipping"(
jbe@5:         "area_id_p",
jbe@5:         "member_id_p",
jbe@5:         ARRAY["member_id_p"]
jbe@5:       );
jbe@5:     END;
jbe@5:   $$;
jbe@5: 
jbe@5: 
jbe@4: CREATE VIEW "member_count_view" AS
jbe@5:   SELECT count(1) AS "total_count" FROM "member" WHERE "active";
jbe@4: 
jbe@4: COMMENT ON VIEW "member_count_view" IS 'View used to update "member_count" table';
jbe@4: 
jbe@4: 
jbe@4: CREATE VIEW "area_member_count" AS
jbe@5:   SELECT
jbe@5:     "area"."id" AS "area_id",
jbe@5:     count("member"."id") AS "direct_member_count",
jbe@5:     coalesce(
jbe@5:       sum(
jbe@5:         CASE WHEN "member"."id" NOTNULL THEN
jbe@5:           "membership_weight"("area"."id", "member"."id")
jbe@5:         ELSE 0 END
jbe@5:       )
jbe@5:     ) AS "member_weight",
jbe@5:     coalesce(
jbe@5:       sum(
jbe@5:         CASE WHEN "member"."id" NOTNULL AND "membership"."autoreject" THEN
jbe@5:           "membership_weight"("area"."id", "member"."id")
jbe@5:         ELSE 0 END
jbe@5:       )
jbe@5:     ) AS "autoreject_weight"
jbe@4:   FROM "area"
jbe@4:   LEFT JOIN "membership"
jbe@4:   ON "area"."id" = "membership"."area_id"
jbe@4:   LEFT JOIN "member"
jbe@4:   ON "membership"."member_id" = "member"."id"
jbe@4:   AND "member"."active"
jbe@4:   GROUP BY "area"."id";
jbe@5: -- TODO: count delegations
jbe@4: 
jbe@4: COMMENT ON VIEW "area_member_count" IS 'View used to update "member_count" column of table "area"';
jbe@4: 
jbe@4: 
jbe@0: CREATE VIEW "current_draft" AS
jbe@0:   SELECT "draft".* FROM (
jbe@0:     SELECT
jbe@0:       "initiative"."id" AS "initiative_id",
jbe@0:       max("draft"."id") AS "draft_id"
jbe@0:     FROM "initiative" JOIN "draft"
jbe@0:     ON "initiative"."id" = "draft"."initiative_id"
jbe@0:     GROUP BY "initiative"."id"
jbe@0:   ) AS "subquery"
jbe@0:   JOIN "draft" ON "subquery"."draft_id" = "draft"."id";
jbe@0: 
jbe@0: COMMENT ON VIEW "current_draft" IS 'All latest drafts for each initiative';
jbe@0: 
jbe@0: 
jbe@0: CREATE VIEW "critical_opinion" AS
jbe@0:   SELECT * FROM "opinion"
jbe@0:   WHERE ("degree" = 2 AND "fulfilled" = FALSE)
jbe@0:   OR ("degree" = -2 AND "fulfilled" = TRUE);
jbe@0: 
jbe@0: COMMENT ON VIEW "critical_opinion" IS 'Opinions currently causing dissatisfaction';
jbe@0: 
jbe@0: 
jbe@0: CREATE VIEW "battle_participant" AS
jbe@0:   SELECT "issue_id", "id" AS "initiative_id" FROM "initiative"
jbe@0:   WHERE "admitted"
jbe@0:   AND "positive_votes" > "negative_votes";
jbe@0: 
jbe@0: COMMENT ON VIEW "battle_participant" IS 'Helper view for "battle" view';
jbe@0: 
jbe@0: 
jbe@0: CREATE VIEW "battle" AS
jbe@0:   SELECT
jbe@0:     "issue"."id" AS "issue_id",
jbe@0:     "winning_initiative"."initiative_id" AS "winning_initiative_id",
jbe@0:     "losing_initiative"."initiative_id" AS "losing_initiative_id",
jbe@0:     sum(
jbe@0:       CASE WHEN
jbe@0:         coalesce("better_vote"."grade", 0) >
jbe@0:         coalesce("worse_vote"."grade", 0)
jbe@0:       THEN "direct_voter"."weight" ELSE 0 END
jbe@0:     ) AS "count"
jbe@0:   FROM "issue"
jbe@0:   LEFT JOIN "direct_voter"
jbe@0:   ON "issue"."id" = "direct_voter"."issue_id"
jbe@0:   JOIN "battle_participant" AS "winning_initiative"
jbe@0:   ON "issue"."id" = "winning_initiative"."issue_id"
jbe@0:   JOIN "battle_participant" AS "losing_initiative"
jbe@0:   ON "issue"."id" = "losing_initiative"."issue_id"
jbe@0:   LEFT JOIN "vote" AS "better_vote"
jbe@0:   ON "direct_voter"."member_id" = "better_vote"."member_id"
jbe@0:   AND "winning_initiative"."initiative_id" = "better_vote"."initiative_id"
jbe@0:   LEFT JOIN "vote" AS "worse_vote"
jbe@0:   ON "direct_voter"."member_id" = "worse_vote"."member_id"
jbe@0:   AND "losing_initiative"."initiative_id" = "worse_vote"."initiative_id"
jbe@0:   WHERE
jbe@0:     "winning_initiative"."initiative_id" !=
jbe@0:     "losing_initiative"."initiative_id"
jbe@0:   GROUP BY
jbe@0:     "issue"."id",
jbe@0:     "winning_initiative"."initiative_id",
jbe@0:     "losing_initiative"."initiative_id";
jbe@0: 
jbe@0: COMMENT ON VIEW "battle" IS 'Number of members preferring one initiative over another';
jbe@0: 
jbe@0: 
jbe@1: CREATE VIEW "expired_session" AS
jbe@1:   SELECT * FROM "session" WHERE now() > "expiry";
jbe@1: 
jbe@1: CREATE RULE "delete" AS ON DELETE TO "expired_session" DO INSTEAD
jbe@1:   DELETE FROM "session" WHERE "ident" = OLD."ident";
jbe@1: 
jbe@1: COMMENT ON VIEW "expired_session" IS 'View containing all expired sessions where DELETE is possible';
jbe@1: COMMENT ON RULE "delete" ON "expired_session" IS 'Rule allowing DELETE on rows in "expired_session" view, i.e. DELETE FROM "expired_session"';
jbe@1: 
jbe@1: 
jbe@0: CREATE VIEW "open_issue" AS
jbe@0:   SELECT * FROM "issue" WHERE "closed" ISNULL;
jbe@0: 
jbe@0: COMMENT ON VIEW "open_issue" IS 'All open issues';
jbe@0: 
jbe@0: 
jbe@0: CREATE VIEW "issue_with_ranks_missing" AS
jbe@0:   SELECT * FROM "issue"
jbe@3:   WHERE "fully_frozen" NOTNULL
jbe@0:   AND "closed" NOTNULL
jbe@0:   AND "ranks_available" = FALSE;
jbe@0: 
jbe@0: COMMENT ON VIEW "issue_with_ranks_missing" IS 'Issues where voting was finished, but no ranks have been calculated yet';
jbe@0: 
jbe@0: 
jbe@0: 
jbe@5: --------------------------------------------------
jbe@5: -- Set returning function for delegation chains --
jbe@5: --------------------------------------------------
jbe@5: 
jbe@5: 
jbe@5: CREATE TYPE "delegation_chain_loop_tag" AS ENUM
jbe@5:   ('first', 'intermediate', 'last', 'repetition');
jbe@5: 
jbe@5: COMMENT ON TYPE "delegation_chain_loop_tag" IS 'Type for loop tags in "delegation_chain_row" type';
jbe@5: 
jbe@5: 
jbe@5: CREATE TYPE "delegation_chain_row" AS (
jbe@5:         "index"                 INT4,
jbe@5:         "member_id"             INT4,
jbe@5:         "member_active"         BOOLEAN,
jbe@5:         "participation"         BOOLEAN,
jbe@5:         "overridden"            BOOLEAN,
jbe@5:         "scope_in"              "delegation_scope",
jbe@5:         "scope_out"             "delegation_scope",
jbe@5:         "loop"                  "delegation_chain_loop_tag" );
jbe@5: 
jbe@5: COMMENT ON TYPE "delegation_chain_row" IS 'Type of rows returned by "delegation_chain"(...) functions';
jbe@5: 
jbe@5: COMMENT ON COLUMN "delegation_chain_row"."index"         IS 'Index starting with 0 and counting up';
jbe@5: COMMENT ON COLUMN "delegation_chain_row"."participation" IS 'In case of delegation chains for issues: interest, for areas: membership, for global delegation chains: always null';
jbe@5: COMMENT ON COLUMN "delegation_chain_row"."overridden"    IS 'True, if an entry with lower index has "participation" set to true';
jbe@5: COMMENT ON COLUMN "delegation_chain_row"."scope_in"      IS 'Scope of used incoming delegation';
jbe@5: COMMENT ON COLUMN "delegation_chain_row"."scope_out"     IS 'Scope of used outgoing delegation';
jbe@5: COMMENT ON COLUMN "delegation_chain_row"."loop"          IS 'Not null, if member is part of a loop, see "delegation_chain_loop_tag" type';
jbe@5: 
jbe@5: 
jbe@5: CREATE FUNCTION "delegation_chain"
jbe@5:   ( "member_id_p"           "member"."id"%TYPE,
jbe@5:     "area_id_p"             "area"."id"%TYPE,
jbe@5:     "issue_id_p"            "issue"."id"%TYPE,
jbe@5:     "simulate_trustee_id_p" "member"."id"%TYPE )
jbe@5:   RETURNS SETOF "delegation_chain_row"
jbe@5:   LANGUAGE 'plpgsql' STABLE AS $$
jbe@5:     DECLARE
jbe@5:       "issue_row"          "issue"%ROWTYPE;
jbe@5:       "visited_member_ids" INT4[];  -- "member"."id"%TYPE[]
jbe@5:       "loop_member_id_v"   "member"."id"%TYPE;
jbe@5:       "output_row"         "delegation_chain_row";
jbe@5:       "output_rows"        "delegation_chain_row"[];
jbe@5:       "delegation_row"     "delegation"%ROWTYPE;
jbe@5:       "row_count"          INT4;
jbe@5:       "i"                  INT4;
jbe@5:       "loop_v"             BOOLEAN;
jbe@5:     BEGIN
jbe@5:       SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
jbe@5:       "visited_member_ids" := '{}';
jbe@5:       "loop_member_id_v"   := NULL;
jbe@5:       "output_rows"        := '{}';
jbe@5:       "output_row"."index"         := 0;
jbe@5:       "output_row"."member_id"     := "member_id_p";
jbe@5:       "output_row"."member_active" := TRUE;
jbe@5:       "output_row"."participation" := FALSE;
jbe@5:       "output_row"."overridden"    := FALSE;
jbe@5:       "output_row"."scope_out"     := NULL;
jbe@5:       LOOP
jbe@5:         IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN
jbe@5:           "loop_member_id_v" := "output_row"."member_id";
jbe@5:         ELSE
jbe@5:           "visited_member_ids" :=
jbe@5:             "visited_member_ids" || "output_row"."member_id";
jbe@5:         END IF;
jbe@5:         IF "output_row"."participation" THEN
jbe@5:           "output_row"."overridden" := TRUE;
jbe@5:         END IF;
jbe@5:         "output_row"."scope_in" := "output_row"."scope_out";
jbe@5:         IF EXISTS (
jbe@5:           SELECT NULL FROM "member" 
jbe@5:           WHERE "id" = "output_row"."member_id" AND "active"
jbe@5:         ) THEN
jbe@5:           IF "area_id_p" ISNULL AND "issue_id_p" ISNULL THEN
jbe@5:             SELECT * INTO "delegation_row" FROM "delegation"
jbe@5:               WHERE "truster_id" = "output_row"."member_id"
jbe@5:               AND "area_id" ISNULL AND "issue_id" ISNULL;
jbe@5:           ELSIF "area_id_p" NOTNULL AND "issue_id_p" ISNULL THEN
jbe@5:             "output_row"."participation" := EXISTS (
jbe@5:               SELECT NULL FROM "membership"
jbe@5:               WHERE "area_id" = "area_id_p"
jbe@5:               AND "member_id" = "output_row"."member_id"
jbe@5:             );
jbe@5:             SELECT * INTO "delegation_row" FROM "delegation"
jbe@5:               WHERE "truster_id" = "output_row"."member_id"
jbe@5:               AND ("area_id" ISNULL OR "area_id" = "area_id_p")
jbe@5:               AND "issue_id" ISNULL
jbe@5:               ORDER BY "area_id" NULLS LAST;
jbe@5:           ELSIF "area_id_p" ISNULL AND "issue_id_p" NOTNULL THEN
jbe@5:             "output_row"."participation" := EXISTS (
jbe@5:               SELECT NULL FROM "interest"
jbe@5:               WHERE "issue_id" = "issue_id_p"
jbe@5:               AND "member_id" = "output_row"."member_id"
jbe@5:             );
jbe@5:             SELECT * INTO "delegation_row" FROM "delegation"
jbe@5:               WHERE "truster_id" = "output_row"."member_id"
jbe@5:               AND ("area_id" ISNULL OR "area_id" = "issue_row"."area_id")
jbe@5:               AND ("issue_id" ISNULL OR "issue_id" = "issue_id_p")
jbe@5:               ORDER BY "issue_id" NULLS LAST, "area_id" NULLS LAST;
jbe@5:           ELSE
jbe@5:             RAISE EXCEPTION 'Either area_id or issue_id or both must be NULL.';
jbe@5:           END IF;
jbe@5:         ELSE
jbe@5:           "output_row"."member_active" := FALSE;
jbe@5:           "output_row"."participation" := FALSE;
jbe@5:           "output_row"."scope_out"     := NULL;
jbe@5:           "delegation_row" := ROW(NULL);
jbe@5:         END IF;
jbe@5:         IF
jbe@5:           "output_row"."member_id" = "member_id_p" AND
jbe@5:           "simulate_trustee_id_p" NOTNULL
jbe@5:         THEN
jbe@5:           "output_row"."scope_out" := CASE
jbe@5:             WHEN "area_id_p" ISNULL  AND "issue_id_p" ISNULL  THEN 'global'
jbe@5:             WHEN "area_id_p" NOTNULL AND "issue_id_p" ISNULL  THEN 'area'
jbe@5:             WHEN "area_id_p" ISNULL  AND "issue_id_p" NOTNULL THEN 'issue'
jbe@5:           END;
jbe@5:           "output_rows" := "output_rows" || "output_row";
jbe@5:           "output_row"."member_id" := "simulate_trustee_id_p";
jbe@5:         ELSIF "delegation_row"."trustee_id" NOTNULL THEN
jbe@5:           "output_row"."scope_out" := CASE
jbe@5:             WHEN
jbe@5:               "delegation_row"."area_id" ISNULL AND
jbe@5:               "delegation_row"."issue_id" ISNULL
jbe@5:             THEN 'global'
jbe@5:             WHEN
jbe@5:               "delegation_row"."area_id" NOTNULL AND
jbe@5:               "delegation_row"."issue_id" ISNULL
jbe@5:             THEN 'area'
jbe@5:             WHEN
jbe@5:               "delegation_row"."area_id" ISNULL AND
jbe@5:               "delegation_row"."issue_id" NOTNULL
jbe@5:             THEN 'issue'
jbe@5:           END;
jbe@5:           "output_rows" := "output_rows" || "output_row";
jbe@5:           "output_row"."member_id" := "delegation_row"."trustee_id";
jbe@5:         ELSE
jbe@5:           "output_row"."scope_out" := NULL;
jbe@5:           "output_rows" := "output_rows" || "output_row";
jbe@5:           EXIT;
jbe@5:         END IF;
jbe@5:         EXIT WHEN "loop_member_id_v" NOTNULL;
jbe@5:         "output_row"."index" := "output_row"."index" + 1;
jbe@5:       END LOOP;
jbe@5:       "row_count" := array_upper("output_rows", 1);
jbe@5:       "i"      := 1;
jbe@5:       "loop_v" := FALSE;
jbe@5:       LOOP
jbe@5:         "output_row" := "output_rows"["i"];
jbe@5:         EXIT WHEN "output_row"."member_id" ISNULL;
jbe@5:         IF "loop_v" THEN
jbe@5:           IF "i" + 1 = "row_count" THEN
jbe@5:             "output_row"."loop" := 'last';
jbe@5:           ELSIF "i" = "row_count" THEN
jbe@5:             "output_row"."loop" := 'repetition';
jbe@5:           ELSE
jbe@5:             "output_row"."loop" := 'intermediate';
jbe@5:           END IF;
jbe@5:         ELSIF "output_row"."member_id" = "loop_member_id_v" THEN
jbe@5:           "output_row"."loop" := 'first';
jbe@5:           "loop_v" := TRUE;
jbe@5:         END IF;
jbe@5:         IF "area_id_p" ISNULL AND "issue_id_p" ISNULL THEN
jbe@5:           "output_row"."participation" := NULL;
jbe@5:         END IF;
jbe@5:         RETURN NEXT "output_row";
jbe@5:         "i" := "i" + 1;
jbe@5:       END LOOP;
jbe@5:       RETURN;
jbe@5:     END;
jbe@5:   $$;
jbe@5: 
jbe@5: COMMENT ON FUNCTION "delegation_chain"
jbe@5:   ( "member"."id"%TYPE,
jbe@5:     "area"."id"%TYPE,
jbe@5:     "issue"."id"%TYPE,
jbe@5:     "member"."id"%TYPE )
jbe@5:   IS 'Helper function for frontends to display delegation chains; Not part of internal voting logic';
jbe@5: 
jbe@5: CREATE FUNCTION "delegation_chain"
jbe@5:   ( "member_id_p" "member"."id"%TYPE,
jbe@5:     "area_id_p"   "area"."id"%TYPE,
jbe@5:     "issue_id_p"  "issue"."id"%TYPE )
jbe@5:   RETURNS SETOF "delegation_chain_row"
jbe@5:   LANGUAGE 'plpgsql' STABLE AS $$
jbe@5:     DECLARE
jbe@5:       "result_row" "delegation_chain_row";
jbe@5:     BEGIN
jbe@5:       FOR "result_row" IN
jbe@5:         SELECT * FROM "delegation_chain"(
jbe@5:           "member_id_p", "area_id_p", "issue_id_p", NULL
jbe@5:         )
jbe@5:       LOOP
jbe@5:         RETURN NEXT "result_row";
jbe@5:       END LOOP;
jbe@5:       RETURN;
jbe@5:     END;
jbe@5:   $$;
jbe@5: 
jbe@5: COMMENT ON FUNCTION "delegation_chain"
jbe@5:   ( "member"."id"%TYPE,
jbe@5:     "area"."id"%TYPE,
jbe@5:     "issue"."id"%TYPE )
jbe@5:   IS 'Shortcut for "delegation_chain"(...) function where 4th parameter is null';
jbe@5: 
jbe@5: 
jbe@5: 
jbe@0: ------------------------------
jbe@0: -- Comparison by vote count --
jbe@0: ------------------------------
jbe@0: 
jbe@0: CREATE FUNCTION "vote_ratio"
jbe@0:   ( "positive_votes_p" "initiative"."positive_votes"%TYPE,
jbe@0:     "negative_votes_p" "initiative"."negative_votes"%TYPE )
jbe@0:   RETURNS FLOAT8
jbe@0:   LANGUAGE 'plpgsql' STABLE AS $$
jbe@0:     DECLARE
jbe@0:       "total_v" INT4;
jbe@0:     BEGIN
jbe@0:       "total_v" := "positive_votes_p" + "negative_votes_p";
jbe@0:       IF "total_v" > 0 THEN
jbe@0:         RETURN "positive_votes_p"::FLOAT8 / "total_v"::FLOAT8;
jbe@0:       ELSE
jbe@0:         RETURN 0.5;
jbe@0:       END IF;
jbe@0:     END;
jbe@0:   $$;
jbe@0: 
jbe@0: COMMENT ON FUNCTION "vote_ratio"
jbe@0:   ( "initiative"."positive_votes"%TYPE,
jbe@0:     "initiative"."negative_votes"%TYPE )
jbe@0:   IS 'Ratio of positive votes to sum of positive and negative votes; 0.5, if there are neither positive nor negative votes';
jbe@0: 
jbe@0: 
jbe@0: 
jbe@0: ------------------------------------------------
jbe@0: -- Locking for snapshots and voting procedure --
jbe@0: ------------------------------------------------
jbe@0: 
jbe@0: CREATE FUNCTION "global_lock"() RETURNS VOID
jbe@0:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0:     BEGIN
jbe@0:       -- NOTE: PostgreSQL allows reading, while tables are locked in
jbe@0:       -- exclusive move. Transactions should be kept short anyway!
jbe@0:       LOCK TABLE "member"     IN EXCLUSIVE MODE;
jbe@4:       LOCK TABLE "area"       IN EXCLUSIVE MODE;
jbe@4:       LOCK TABLE "membership" IN EXCLUSIVE MODE;
jbe@4:       -- NOTE: "member", "area" and "membership" are locked first to
jbe@4:       -- prevent deadlocks in combination with "calculate_member_counts"()
jbe@0:       LOCK TABLE "policy"     IN EXCLUSIVE MODE;
jbe@0:       LOCK TABLE "issue"      IN EXCLUSIVE MODE;
jbe@0:       LOCK TABLE "initiative" IN EXCLUSIVE MODE;
jbe@0:       LOCK TABLE "draft"      IN EXCLUSIVE MODE;
jbe@0:       LOCK TABLE "suggestion" IN EXCLUSIVE MODE;
jbe@0:       LOCK TABLE "interest"   IN EXCLUSIVE MODE;
jbe@0:       LOCK TABLE "initiator"  IN EXCLUSIVE MODE;
jbe@0:       LOCK TABLE "supporter"  IN EXCLUSIVE MODE;
jbe@0:       LOCK TABLE "opinion"    IN EXCLUSIVE MODE;
jbe@0:       LOCK TABLE "delegation" IN EXCLUSIVE MODE;
jbe@0:       LOCK TABLE "direct_population_snapshot"     IN EXCLUSIVE MODE;
jbe@0:       LOCK TABLE "delegating_population_snapshot" IN EXCLUSIVE MODE;
jbe@0:       LOCK TABLE "direct_interest_snapshot"       IN EXCLUSIVE MODE;
jbe@0:       LOCK TABLE "delegating_interest_snapshot"   IN EXCLUSIVE MODE;
jbe@0:       LOCK TABLE "direct_supporter_snapshot"      IN EXCLUSIVE MODE;
jbe@0:       LOCK TABLE "direct_voter"     IN EXCLUSIVE MODE;
jbe@0:       LOCK TABLE "delegating_voter" IN EXCLUSIVE MODE;
jbe@0:       LOCK TABLE "vote"             IN EXCLUSIVE MODE;
jbe@0:       RETURN;
jbe@0:     END;
jbe@0:   $$;
jbe@0: 
jbe@0: COMMENT ON FUNCTION "global_lock"() IS 'Locks all tables related to support/voting until end of transaction; read access is still possible though';
jbe@0: 
jbe@0: 
jbe@0: 
jbe@4: -------------------------------
jbe@4: -- Materialize member counts --
jbe@4: -------------------------------
jbe@4: 
jbe@4: CREATE FUNCTION "calculate_member_counts"()
jbe@4:   RETURNS VOID
jbe@4:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@4:     BEGIN
jbe@4:       LOCK TABLE "member"     IN EXCLUSIVE MODE;
jbe@4:       LOCK TABLE "area"       IN EXCLUSIVE MODE;
jbe@4:       LOCK TABLE "membership" IN EXCLUSIVE MODE;
jbe@4:       DELETE FROM "member_count";
jbe@5:       INSERT INTO "member_count" ("total_count")
jbe@5:         SELECT "total_count" FROM "member_count_view";
jbe@5:       UPDATE "area" SET
jbe@5:         "direct_member_count" = "view"."direct_member_count",
jbe@5:         "member_weight"       = "view"."member_weight",
jbe@5:         "autoreject_weight"   = "view"."autoreject_weight"
jbe@5:         FROM "area_member_count" AS "view"
jbe@5:         WHERE "view"."area_id" = "area"."id";
jbe@4:       RETURN;
jbe@4:     END;
jbe@4:   $$;
jbe@4: 
jbe@4: COMMENT ON FUNCTION "calculate_member_counts"() IS 'Updates "member_count" table and "member_count" column of table "area" by materializing data from views "member_count_view" and "area_member_count"';
jbe@4: 
jbe@4: 
jbe@4: 
jbe@0: ------------------------------
jbe@0: -- Calculation of snapshots --
jbe@0: ------------------------------
jbe@0: 
jbe@0: CREATE FUNCTION "weight_of_added_delegations_for_population_snapshot"
jbe@0:   ( "issue_id_p"            "issue"."id"%TYPE,
jbe@0:     "member_id_p"           "member"."id"%TYPE,
jbe@0:     "delegate_member_ids_p" "delegating_population_snapshot"."delegate_member_ids"%TYPE )
jbe@0:   RETURNS "direct_population_snapshot"."weight"%TYPE
jbe@0:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0:     DECLARE
jbe@0:       "issue_delegation_row"  "issue_delegation"%ROWTYPE;
jbe@0:       "delegate_member_ids_v" "delegating_population_snapshot"."delegate_member_ids"%TYPE;
jbe@0:       "weight_v"              INT4;
jbe@0:     BEGIN
jbe@0:       "weight_v" := 0;
jbe@0:       FOR "issue_delegation_row" IN
jbe@0:         SELECT * FROM "issue_delegation"
jbe@0:         WHERE "trustee_id" = "member_id_p"
jbe@0:         AND "issue_id" = "issue_id_p"
jbe@0:       LOOP
jbe@0:         IF NOT EXISTS (
jbe@0:           SELECT NULL FROM "direct_population_snapshot"
jbe@0:           WHERE "issue_id" = "issue_id_p"
jbe@0:           AND "event" = 'periodic'
jbe@0:           AND "member_id" = "issue_delegation_row"."truster_id"
jbe@0:         ) AND NOT EXISTS (
jbe@0:           SELECT NULL FROM "delegating_population_snapshot"
jbe@0:           WHERE "issue_id" = "issue_id_p"
jbe@0:           AND "event" = 'periodic'
jbe@0:           AND "member_id" = "issue_delegation_row"."truster_id"
jbe@0:         ) THEN
jbe@0:           "delegate_member_ids_v" :=
jbe@0:             "member_id_p" || "delegate_member_ids_p";
jbe@0:           INSERT INTO "delegating_population_snapshot"
jbe@0:             ("issue_id", "event", "member_id", "delegate_member_ids")
jbe@0:             VALUES (
jbe@0:               "issue_id_p",
jbe@0:               'periodic',
jbe@0:               "issue_delegation_row"."truster_id",
jbe@0:               "delegate_member_ids_v"
jbe@0:             );
jbe@0:           "weight_v" := "weight_v" + 1 +
jbe@0:             "weight_of_added_delegations_for_population_snapshot"(
jbe@0:               "issue_id_p",
jbe@0:               "issue_delegation_row"."truster_id",
jbe@0:               "delegate_member_ids_v"
jbe@0:             );
jbe@0:         END IF;
jbe@0:       END LOOP;
jbe@0:       RETURN "weight_v";
jbe@0:     END;
jbe@0:   $$;
jbe@0: 
jbe@0: COMMENT ON FUNCTION "weight_of_added_delegations_for_population_snapshot"
jbe@0:   ( "issue"."id"%TYPE,
jbe@0:     "member"."id"%TYPE,
jbe@0:     "delegating_population_snapshot"."delegate_member_ids"%TYPE )
jbe@0:   IS 'Helper function for "create_population_snapshot" function';
jbe@0: 
jbe@0: 
jbe@0: CREATE FUNCTION "create_population_snapshot"
jbe@0:   ( "issue_id_p" "issue"."id"%TYPE )
jbe@0:   RETURNS VOID
jbe@0:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0:     DECLARE
jbe@0:       "member_id_v" "member"."id"%TYPE;
jbe@0:     BEGIN
jbe@0:       DELETE FROM "direct_population_snapshot"
jbe@0:         WHERE "issue_id" = "issue_id_p"
jbe@0:         AND "event" = 'periodic';
jbe@0:       DELETE FROM "delegating_population_snapshot"
jbe@0:         WHERE "issue_id" = "issue_id_p"
jbe@0:         AND "event" = 'periodic';
jbe@0:       INSERT INTO "direct_population_snapshot"
jbe@0:         ("issue_id", "event", "member_id", "interest_exists")
jbe@0:         SELECT DISTINCT ON ("issue_id", "member_id")
jbe@0:           "issue_id_p" AS "issue_id",
jbe@0:           'periodic'   AS "event",
jbe@0:           "subquery"."member_id",
jbe@0:           "subquery"."interest_exists"
jbe@0:         FROM (
jbe@0:           SELECT
jbe@0:             "member"."id" AS "member_id",
jbe@0:             FALSE         AS "interest_exists"
jbe@0:           FROM "issue"
jbe@0:           JOIN "area" ON "issue"."area_id" = "area"."id"
jbe@0:           JOIN "membership" ON "area"."id" = "membership"."area_id"
jbe@0:           JOIN "member" ON "membership"."member_id" = "member"."id"
jbe@0:           WHERE "issue"."id" = "issue_id_p"
jbe@0:           AND "member"."active"
jbe@0:           UNION
jbe@0:           SELECT
jbe@0:             "member"."id" AS "member_id",
jbe@0:             TRUE          AS "interest_exists"
jbe@0:           FROM "interest" JOIN "member"
jbe@0:           ON "interest"."member_id" = "member"."id"
jbe@0:           WHERE "interest"."issue_id" = "issue_id_p"
jbe@0:           AND "member"."active"
jbe@0:         ) AS "subquery"
jbe@0:         ORDER BY
jbe@0:           "issue_id_p",
jbe@0:           "subquery"."member_id",
jbe@0:           "subquery"."interest_exists" DESC;
jbe@0:       FOR "member_id_v" IN
jbe@0:         SELECT "member_id" FROM "direct_population_snapshot"
jbe@0:         WHERE "issue_id" = "issue_id_p"
jbe@0:         AND "event" = 'periodic'
jbe@0:       LOOP
jbe@0:         UPDATE "direct_population_snapshot" SET
jbe@0:           "weight" = 1 +
jbe@0:             "weight_of_added_delegations_for_population_snapshot"(
jbe@0:               "issue_id_p",
jbe@0:               "member_id_v",
jbe@0:               '{}'
jbe@0:             )
jbe@0:           WHERE "issue_id" = "issue_id_p"
jbe@0:           AND "event" = 'periodic'
jbe@0:           AND "member_id" = "member_id_v";
jbe@0:       END LOOP;
jbe@0:       RETURN;
jbe@0:     END;
jbe@0:   $$;
jbe@0: 
jbe@0: COMMENT ON FUNCTION "create_population_snapshot"
jbe@0:   ( "issue_id_p" "issue"."id"%TYPE )
jbe@0:   IS 'This function creates a new ''periodic'' population snapshot for the given issue. It does neither lock any tables, nor updates precalculated values in other tables.';
jbe@0: 
jbe@0: 
jbe@0: CREATE FUNCTION "weight_of_added_delegations_for_interest_snapshot"
jbe@0:   ( "issue_id_p"            "issue"."id"%TYPE,
jbe@0:     "member_id_p"           "member"."id"%TYPE,
jbe@0:     "delegate_member_ids_p" "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
jbe@0:   RETURNS "direct_interest_snapshot"."weight"%TYPE
jbe@0:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0:     DECLARE
jbe@0:       "issue_delegation_row"  "issue_delegation"%ROWTYPE;
jbe@0:       "delegate_member_ids_v" "delegating_interest_snapshot"."delegate_member_ids"%TYPE;
jbe@0:       "weight_v"              INT4;
jbe@0:     BEGIN
jbe@0:       "weight_v" := 0;
jbe@0:       FOR "issue_delegation_row" IN
jbe@0:         SELECT * FROM "issue_delegation"
jbe@0:         WHERE "trustee_id" = "member_id_p"
jbe@0:         AND "issue_id" = "issue_id_p"
jbe@0:       LOOP
jbe@0:         IF NOT EXISTS (
jbe@0:           SELECT NULL FROM "direct_interest_snapshot"
jbe@0:           WHERE "issue_id" = "issue_id_p"
jbe@0:           AND "event" = 'periodic'
jbe@0:           AND "member_id" = "issue_delegation_row"."truster_id"
jbe@0:         ) AND NOT EXISTS (
jbe@0:           SELECT NULL FROM "delegating_interest_snapshot"
jbe@0:           WHERE "issue_id" = "issue_id_p"
jbe@0:           AND "event" = 'periodic'
jbe@0:           AND "member_id" = "issue_delegation_row"."truster_id"
jbe@0:         ) THEN
jbe@0:           "delegate_member_ids_v" :=
jbe@0:             "member_id_p" || "delegate_member_ids_p";
jbe@0:           INSERT INTO "delegating_interest_snapshot"
jbe@0:             ("issue_id", "event", "member_id", "delegate_member_ids")
jbe@0:             VALUES (
jbe@0:               "issue_id_p",
jbe@0:               'periodic',
jbe@0:               "issue_delegation_row"."truster_id",
jbe@0:               "delegate_member_ids_v"
jbe@0:             );
jbe@0:           "weight_v" := "weight_v" + 1 +
jbe@0:             "weight_of_added_delegations_for_interest_snapshot"(
jbe@0:               "issue_id_p",
jbe@0:               "issue_delegation_row"."truster_id",
jbe@0:               "delegate_member_ids_v"
jbe@0:             );
jbe@0:         END IF;
jbe@0:       END LOOP;
jbe@0:       RETURN "weight_v";
jbe@0:     END;
jbe@0:   $$;
jbe@0: 
jbe@0: COMMENT ON FUNCTION "weight_of_added_delegations_for_interest_snapshot"
jbe@0:   ( "issue"."id"%TYPE,
jbe@0:     "member"."id"%TYPE,
jbe@0:     "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
jbe@0:   IS 'Helper function for "create_interest_snapshot" function';
jbe@0: 
jbe@0: 
jbe@0: CREATE FUNCTION "create_interest_snapshot"
jbe@0:   ( "issue_id_p" "issue"."id"%TYPE )
jbe@0:   RETURNS VOID
jbe@0:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0:     DECLARE
jbe@0:       "member_id_v" "member"."id"%TYPE;
jbe@0:     BEGIN
jbe@0:       DELETE FROM "direct_interest_snapshot"
jbe@0:         WHERE "issue_id" = "issue_id_p"
jbe@0:         AND "event" = 'periodic';
jbe@0:       DELETE FROM "delegating_interest_snapshot"
jbe@0:         WHERE "issue_id" = "issue_id_p"
jbe@0:         AND "event" = 'periodic';
jbe@0:       DELETE FROM "direct_supporter_snapshot"
jbe@0:         WHERE "issue_id" = "issue_id_p"
jbe@0:         AND "event" = 'periodic';
jbe@0:       INSERT INTO "direct_interest_snapshot"
jbe@0:         ("issue_id", "event", "member_id", "voting_requested")
jbe@0:         SELECT
jbe@0:           "issue_id_p"  AS "issue_id",
jbe@0:           'periodic'    AS "event",
jbe@0:           "member"."id" AS "member_id",
jbe@0:           "interest"."voting_requested"
jbe@0:         FROM "interest" JOIN "member"
jbe@0:         ON "interest"."member_id" = "member"."id"
jbe@0:         WHERE "interest"."issue_id" = "issue_id_p"
jbe@0:         AND "member"."active";
jbe@0:       FOR "member_id_v" IN
jbe@0:         SELECT "member_id" FROM "direct_interest_snapshot"
jbe@0:         WHERE "issue_id" = "issue_id_p"
jbe@0:         AND "event" = 'periodic'
jbe@0:       LOOP
jbe@0:         UPDATE "direct_interest_snapshot" SET
jbe@0:           "weight" = 1 +
jbe@0:             "weight_of_added_delegations_for_interest_snapshot"(
jbe@0:               "issue_id_p",
jbe@0:               "member_id_v",
jbe@0:               '{}'
jbe@0:             )
jbe@0:           WHERE "issue_id" = "issue_id_p"
jbe@0:           AND "event" = 'periodic'
jbe@0:           AND "member_id" = "member_id_v";
jbe@0:       END LOOP;
jbe@0:       INSERT INTO "direct_supporter_snapshot"
jbe@0:         ( "issue_id", "initiative_id", "event", "member_id",
jbe@0:           "informed", "satisfied" )
jbe@0:         SELECT
jbe@0:           "issue_id_p"      AS "issue_id",
jbe@0:           "initiative"."id" AS "initiative_id",
jbe@0:           'periodic'        AS "event",
jbe@0:           "member"."id"     AS "member_id",
jbe@0:           "supporter"."draft_id" = "current_draft"."id" AS "informed",
jbe@0:           NOT EXISTS (
jbe@0:             SELECT NULL FROM "critical_opinion"
jbe@0:             WHERE "initiative_id" = "initiative"."id"
jbe@0:             AND "member_id" = "member"."id"
jbe@0:           ) AS "satisfied"
jbe@0:         FROM "supporter"
jbe@0:         JOIN "member"
jbe@0:         ON "supporter"."member_id" = "member"."id"
jbe@0:         JOIN "initiative"
jbe@0:         ON "supporter"."initiative_id" = "initiative"."id"
jbe@0:         JOIN "current_draft"
jbe@0:         ON "initiative"."id" = "current_draft"."initiative_id"
jbe@0:         JOIN "direct_interest_snapshot"
jbe@0:         ON "member"."id" = "direct_interest_snapshot"."member_id"
jbe@0:         AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"
jbe@3:         AND "event" = 'periodic'
jbe@0:         WHERE "member"."active"
jbe@0:         AND "initiative"."issue_id" = "issue_id_p";
jbe@0:       RETURN;
jbe@0:     END;
jbe@0:   $$;
jbe@0: 
jbe@0: COMMENT ON FUNCTION "create_interest_snapshot"
jbe@0:   ( "issue"."id"%TYPE )
jbe@0:   IS 'This function creates a new ''periodic'' interest/supporter snapshot for the given issue. It does neither lock any tables, nor updates precalculated values in other tables.';
jbe@0: 
jbe@0: 
jbe@0: CREATE FUNCTION "create_snapshot"
jbe@0:   ( "issue_id_p" "issue"."id"%TYPE )
jbe@0:   RETURNS VOID
jbe@0:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0:     DECLARE
jbe@0:       "initiative_id_v"    "initiative"."id"%TYPE;
jbe@0:       "suggestion_id_v"    "suggestion"."id"%TYPE;
jbe@0:     BEGIN
jbe@0:       PERFORM "global_lock"();
jbe@0:       PERFORM "create_population_snapshot"("issue_id_p");
jbe@0:       PERFORM "create_interest_snapshot"("issue_id_p");
jbe@0:       UPDATE "issue" SET
jbe@0:         "snapshot"   = now(),
jbe@0:         "population" = (
jbe@0:           SELECT coalesce(sum("weight"), 0)
jbe@0:           FROM "direct_population_snapshot"
jbe@0:           WHERE "issue_id" = "issue_id_p"
jbe@0:           AND "event" = 'periodic'
jbe@0:         ),
jbe@0:         "vote_now"   = (
jbe@0:           SELECT coalesce(sum("weight"), 0)
jbe@0:           FROM "direct_interest_snapshot"
jbe@0:           WHERE "issue_id" = "issue_id_p"
jbe@0:           AND "event" = 'periodic'
jbe@0:           AND "voting_requested" = TRUE
jbe@0:         ),
jbe@0:         "vote_later" = (
jbe@0:           SELECT coalesce(sum("weight"), 0)
jbe@0:           FROM "direct_interest_snapshot"
jbe@0:           WHERE "issue_id" = "issue_id_p"
jbe@0:           AND "event" = 'periodic'
jbe@0:           AND "voting_requested" = FALSE
jbe@0:         )
jbe@0:         WHERE "id" = "issue_id_p";
jbe@0:       FOR "initiative_id_v" IN
jbe@0:         SELECT "id" FROM "initiative" WHERE "issue_id" = "issue_id_p"
jbe@0:       LOOP
jbe@0:         UPDATE "initiative" SET
jbe@0:           "supporter_count" = (
jbe@0:             SELECT coalesce(sum("di"."weight"), 0)
jbe@0:             FROM "direct_interest_snapshot" AS "di"
jbe@0:             JOIN "direct_supporter_snapshot" AS "ds"
jbe@0:             ON "di"."member_id" = "ds"."member_id"
jbe@0:             WHERE "di"."issue_id" = "issue_id_p"
jbe@0:             AND "di"."event" = 'periodic'
jbe@0:             AND "ds"."initiative_id" = "initiative_id_v"
jbe@0:             AND "ds"."event" = 'periodic'
jbe@0:           ),
jbe@0:           "informed_supporter_count" = (
jbe@0:             SELECT coalesce(sum("di"."weight"), 0)
jbe@0:             FROM "direct_interest_snapshot" AS "di"
jbe@0:             JOIN "direct_supporter_snapshot" AS "ds"
jbe@0:             ON "di"."member_id" = "ds"."member_id"
jbe@0:             WHERE "di"."issue_id" = "issue_id_p"
jbe@0:             AND "di"."event" = 'periodic'
jbe@0:             AND "ds"."initiative_id" = "initiative_id_v"
jbe@0:             AND "ds"."event" = 'periodic'
jbe@0:             AND "ds"."informed"
jbe@0:           ),
jbe@0:           "satisfied_supporter_count" = (
jbe@0:             SELECT coalesce(sum("di"."weight"), 0)
jbe@0:             FROM "direct_interest_snapshot" AS "di"
jbe@0:             JOIN "direct_supporter_snapshot" AS "ds"
jbe@0:             ON "di"."member_id" = "ds"."member_id"
jbe@0:             WHERE "di"."issue_id" = "issue_id_p"
jbe@0:             AND "di"."event" = 'periodic'
jbe@0:             AND "ds"."initiative_id" = "initiative_id_v"
jbe@0:             AND "ds"."event" = 'periodic'
jbe@0:             AND "ds"."satisfied"
jbe@0:           ),
jbe@0:           "satisfied_informed_supporter_count" = (
jbe@0:             SELECT coalesce(sum("di"."weight"), 0)
jbe@0:             FROM "direct_interest_snapshot" AS "di"
jbe@0:             JOIN "direct_supporter_snapshot" AS "ds"
jbe@0:             ON "di"."member_id" = "ds"."member_id"
jbe@0:             WHERE "di"."issue_id" = "issue_id_p"
jbe@0:             AND "di"."event" = 'periodic'
jbe@0:             AND "ds"."initiative_id" = "initiative_id_v"
jbe@0:             AND "ds"."event" = 'periodic'
jbe@0:             AND "ds"."informed"
jbe@0:             AND "ds"."satisfied"
jbe@0:           )
jbe@0:           WHERE "id" = "initiative_id_v";
jbe@0:         FOR "suggestion_id_v" IN
jbe@0:           SELECT "id" FROM "suggestion"
jbe@0:           WHERE "initiative_id" = "initiative_id_v"
jbe@0:         LOOP
jbe@0:           UPDATE "suggestion" SET
jbe@0:             "minus2_unfulfilled_count" = (
jbe@0:               SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@0:               FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
jbe@0:               ON "opinion"."member_id" = "snapshot"."member_id"
jbe@1:               WHERE "opinion"."suggestion_id" = "suggestion_id_v"
jbe@0:               AND "snapshot"."issue_id" = "issue_id_p"
jbe@0:               AND "opinion"."degree" = -2
jbe@0:               AND "opinion"."fulfilled" = FALSE
jbe@0:             ),
jbe@0:             "minus2_fulfilled_count" = (
jbe@0:               SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@0:               FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
jbe@0:               ON "opinion"."member_id" = "snapshot"."member_id"
jbe@1:               WHERE "opinion"."suggestion_id" = "suggestion_id_v"
jbe@0:               AND "snapshot"."issue_id" = "issue_id_p"
jbe@0:               AND "opinion"."degree" = -2
jbe@0:               AND "opinion"."fulfilled" = TRUE
jbe@0:             ),
jbe@0:             "minus1_unfulfilled_count" = (
jbe@0:               SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@0:               FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
jbe@0:               ON "opinion"."member_id" = "snapshot"."member_id"
jbe@1:               WHERE "opinion"."suggestion_id" = "suggestion_id_v"
jbe@0:               AND "snapshot"."issue_id" = "issue_id_p"
jbe@0:               AND "opinion"."degree" = -1
jbe@0:               AND "opinion"."fulfilled" = FALSE
jbe@0:             ),
jbe@0:             "minus1_fulfilled_count" = (
jbe@0:               SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@0:               FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
jbe@0:               ON "opinion"."member_id" = "snapshot"."member_id"
jbe@1:               WHERE "opinion"."suggestion_id" = "suggestion_id_v"
jbe@0:               AND "snapshot"."issue_id" = "issue_id_p"
jbe@0:               AND "opinion"."degree" = -1
jbe@0:               AND "opinion"."fulfilled" = TRUE
jbe@0:             ),
jbe@0:             "plus1_unfulfilled_count" = (
jbe@0:               SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@0:               FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
jbe@0:               ON "opinion"."member_id" = "snapshot"."member_id"
jbe@1:               WHERE "opinion"."suggestion_id" = "suggestion_id_v"
jbe@0:               AND "snapshot"."issue_id" = "issue_id_p"
jbe@0:               AND "opinion"."degree" = 1
jbe@0:               AND "opinion"."fulfilled" = FALSE
jbe@0:             ),
jbe@0:             "plus1_fulfilled_count" = (
jbe@0:               SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@0:               FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
jbe@0:               ON "opinion"."member_id" = "snapshot"."member_id"
jbe@1:               WHERE "opinion"."suggestion_id" = "suggestion_id_v"
jbe@0:               AND "snapshot"."issue_id" = "issue_id_p"
jbe@0:               AND "opinion"."degree" = 1
jbe@0:               AND "opinion"."fulfilled" = TRUE
jbe@0:             ),
jbe@0:             "plus2_unfulfilled_count" = (
jbe@0:               SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@0:               FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
jbe@0:               ON "opinion"."member_id" = "snapshot"."member_id"
jbe@1:               WHERE "opinion"."suggestion_id" = "suggestion_id_v"
jbe@0:               AND "snapshot"."issue_id" = "issue_id_p"
jbe@0:               AND "opinion"."degree" = 2
jbe@0:               AND "opinion"."fulfilled" = FALSE
jbe@0:             ),
jbe@0:             "plus2_fulfilled_count" = (
jbe@0:               SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@0:               FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
jbe@0:               ON "opinion"."member_id" = "snapshot"."member_id"
jbe@1:               WHERE "opinion"."suggestion_id" = "suggestion_id_v"
jbe@0:               AND "snapshot"."issue_id" = "issue_id_p"
jbe@0:               AND "opinion"."degree" = 2
jbe@0:               AND "opinion"."fulfilled" = TRUE
jbe@0:             )
jbe@0:             WHERE "suggestion"."id" = "suggestion_id_v";
jbe@0:         END LOOP;
jbe@0:       END LOOP;
jbe@0:       RETURN;
jbe@0:     END;
jbe@0:   $$;
jbe@0: 
jbe@0: COMMENT ON FUNCTION "create_snapshot"
jbe@0:   ( "issue"."id"%TYPE )
jbe@0:   IS 'This function creates a complete new ''periodic'' snapshot of population, interest and support for the given issue. All involved tables are locked, and after completion precalculated values in the source tables are updated.';
jbe@0: 
jbe@0: 
jbe@0: CREATE FUNCTION "set_snapshot_event"
jbe@0:   ( "issue_id_p" "issue"."id"%TYPE,
jbe@0:     "event_p" "snapshot_event" )
jbe@0:   RETURNS VOID
jbe@0:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0:     BEGIN
jbe@3:       UPDATE "direct_population_snapshot" SET "event" = "event_p"
jbe@0:         WHERE "issue_id" = "issue_id_p" AND "event" = 'periodic';
jbe@3:       UPDATE "delegating_population_snapshot" SET "event" = "event_p"
jbe@0:         WHERE "issue_id" = "issue_id_p" AND "event" = 'periodic';
jbe@3:       UPDATE "direct_interest_snapshot" SET "event" = "event_p"
jbe@0:         WHERE "issue_id" = "issue_id_p" AND "event" = 'periodic';
jbe@3:       UPDATE "delegating_interest_snapshot" SET "event" = "event_p"
jbe@0:         WHERE "issue_id" = "issue_id_p" AND "event" = 'periodic';
jbe@3:       UPDATE "direct_supporter_snapshot" SET "event" = "event_p"
jbe@0:         WHERE "issue_id" = "issue_id_p" AND "event" = 'periodic';
jbe@0:       RETURN;
jbe@0:     END;
jbe@0:   $$;
jbe@0: 
jbe@0: COMMENT ON FUNCTION "set_snapshot_event"
jbe@0:   ( "issue"."id"%TYPE,
jbe@0:     "snapshot_event" )
jbe@0:   IS 'Change "event" attribute of the previous ''periodic'' snapshot';
jbe@0: 
jbe@0: 
jbe@0: 
jbe@0: ---------------------
jbe@0: -- Freezing issues --
jbe@0: ---------------------
jbe@0: 
jbe@0: CREATE FUNCTION "freeze_after_snapshot"
jbe@0:   ( "issue_id_p" "issue"."id"%TYPE )
jbe@0:   RETURNS VOID
jbe@0:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0:     DECLARE
jbe@0:       "issue_row"      "issue"%ROWTYPE;
jbe@0:       "policy_row"     "policy"%ROWTYPE;
jbe@0:       "initiative_row" "initiative"%ROWTYPE;
jbe@0:     BEGIN
jbe@0:       SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
jbe@0:       SELECT * INTO "policy_row"
jbe@0:         FROM "policy" WHERE "id" = "issue_row"."policy_id";
jbe@3:       PERFORM "set_snapshot_event"("issue_id_p", 'start_of_voting');
jbe@3:       UPDATE "issue" SET
jbe@4:         "accepted"     = coalesce("accepted", now()),
jbe@4:         "half_frozen"  = coalesce("half_frozen", now()),
jbe@3:         "fully_frozen" = now()
jbe@3:         WHERE "id" = "issue_id_p";
jbe@0:       FOR "initiative_row" IN
jbe@0:         SELECT * FROM "initiative" WHERE "issue_id" = "issue_id_p"
jbe@0:       LOOP
jbe@0:         IF
jbe@0:           "initiative_row"."satisfied_supporter_count" > 0 AND
jbe@0:           "initiative_row"."satisfied_supporter_count" *
jbe@0:           "policy_row"."initiative_quorum_den" >=
jbe@0:           "issue_row"."population" * "policy_row"."initiative_quorum_num"
jbe@0:         THEN
jbe@0:           UPDATE "initiative" SET "admitted" = TRUE
jbe@0:             WHERE "id" = "initiative_row"."id";
jbe@0:         ELSE
jbe@0:           UPDATE "initiative" SET "admitted" = FALSE
jbe@0:             WHERE "id" = "initiative_row"."id";
jbe@0:         END IF;
jbe@0:       END LOOP;
jbe@0:       RETURN;
jbe@0:     END;
jbe@0:   $$;
jbe@0: 
jbe@0: COMMENT ON FUNCTION "freeze_after_snapshot"
jbe@0:   ( "issue"."id"%TYPE )
jbe@3:   IS 'This function freezes an issue (fully) and starts voting, but must only be called when "create_snapshot" was called in the same transaction';
jbe@0: 
jbe@0: 
jbe@0: CREATE FUNCTION "manual_freeze"("issue_id_p" "issue"."id"%TYPE)
jbe@0:   RETURNS VOID
jbe@0:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0:     DECLARE
jbe@0:       "issue_row" "issue"%ROWTYPE;
jbe@0:     BEGIN
jbe@0:       PERFORM "create_snapshot"("issue_id_p");
jbe@0:       PERFORM "freeze_after_snapshot"("issue_id_p");
jbe@0:       RETURN;
jbe@0:     END;
jbe@0:   $$;
jbe@0: 
jbe@0: COMMENT ON FUNCTION "freeze_after_snapshot"
jbe@0:   ( "issue"."id"%TYPE )
jbe@3:   IS 'Freeze an issue manually (fully) and start voting';
jbe@0: 
jbe@0: 
jbe@0: 
jbe@0: -----------------------
jbe@0: -- Counting of votes --
jbe@0: -----------------------
jbe@0: 
jbe@0: 
jbe@5: CREATE FUNCTION "weight_of_added_vote_delegations"
jbe@0:   ( "issue_id_p"            "issue"."id"%TYPE,
jbe@0:     "member_id_p"           "member"."id"%TYPE,
jbe@0:     "delegate_member_ids_p" "delegating_voter"."delegate_member_ids"%TYPE )
jbe@0:   RETURNS "direct_voter"."weight"%TYPE
jbe@0:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0:     DECLARE
jbe@0:       "issue_delegation_row"  "issue_delegation"%ROWTYPE;
jbe@0:       "delegate_member_ids_v" "delegating_voter"."delegate_member_ids"%TYPE;
jbe@0:       "weight_v"              INT4;
jbe@0:     BEGIN
jbe@0:       "weight_v" := 0;
jbe@0:       FOR "issue_delegation_row" IN
jbe@0:         SELECT * FROM "issue_delegation"
jbe@0:         WHERE "trustee_id" = "member_id_p"
jbe@0:         AND "issue_id" = "issue_id_p"
jbe@0:       LOOP
jbe@0:         IF NOT EXISTS (
jbe@0:           SELECT NULL FROM "direct_voter"
jbe@0:           WHERE "member_id" = "issue_delegation_row"."truster_id"
jbe@0:           AND "issue_id" = "issue_id_p"
jbe@0:         ) AND NOT EXISTS (
jbe@0:           SELECT NULL FROM "delegating_voter"
jbe@0:           WHERE "member_id" = "issue_delegation_row"."truster_id"
jbe@0:           AND "issue_id" = "issue_id_p"
jbe@0:         ) THEN
jbe@0:           "delegate_member_ids_v" :=
jbe@0:             "member_id_p" || "delegate_member_ids_p";
jbe@0:           INSERT INTO "delegating_voter"
jbe@0:             ("member_id", "issue_id", "delegate_member_ids")
jbe@0:             VALUES (
jbe@0:               "issue_delegation_row"."truster_id",
jbe@0:               "issue_id_p",
jbe@0:               "delegate_member_ids_v"
jbe@0:             );
jbe@5:           "weight_v" := "weight_v" +
jbe@5:             1 + "weight_of_added_vote_delegations"(
jbe@5:               "issue_id_p",
jbe@5:               "issue_delegation_row"."truster_id",
jbe@5:               "delegate_member_ids_v"
jbe@5:             );
jbe@0:         END IF;
jbe@0:       END LOOP;
jbe@0:       RETURN "weight_v";
jbe@0:     END;
jbe@0:   $$;
jbe@0: 
jbe@5: COMMENT ON FUNCTION "weight_of_added_vote_delegations"
jbe@0:   ( "issue"."id"%TYPE,
jbe@0:     "member"."id"%TYPE,
jbe@0:     "delegating_voter"."delegate_member_ids"%TYPE )
jbe@0:   IS 'Helper function for "add_vote_delegations" function';
jbe@0: 
jbe@0: 
jbe@0: CREATE FUNCTION "add_vote_delegations"
jbe@0:   ( "issue_id_p" "issue"."id"%TYPE )
jbe@0:   RETURNS VOID
jbe@0:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0:     DECLARE
jbe@0:       "member_id_v" "member"."id"%TYPE;
jbe@0:     BEGIN
jbe@0:       FOR "member_id_v" IN
jbe@0:         SELECT "member_id" FROM "direct_voter"
jbe@0:         WHERE "issue_id" = "issue_id_p"
jbe@0:       LOOP
jbe@0:         UPDATE "direct_voter" SET
jbe@5:           "weight" = "weight" + "weight_of_added_vote_delegations"(
jbe@0:             "issue_id_p",
jbe@0:             "member_id_v",
jbe@0:             '{}'
jbe@0:           )
jbe@0:           WHERE "member_id" = "member_id_v"
jbe@0:           AND "issue_id" = "issue_id_p";
jbe@0:       END LOOP;
jbe@0:       RETURN;
jbe@0:     END;
jbe@0:   $$;
jbe@0: 
jbe@0: COMMENT ON FUNCTION "add_vote_delegations"
jbe@0:   ( "issue_id_p" "issue"."id"%TYPE )
jbe@0:   IS 'Helper function for "close_voting" function';
jbe@0: 
jbe@0: 
jbe@0: CREATE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
jbe@0:   RETURNS VOID
jbe@0:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0:     DECLARE
jbe@0:       "issue_row"   "issue"%ROWTYPE;
jbe@0:       "member_id_v" "member"."id"%TYPE;
jbe@0:     BEGIN
jbe@0:       PERFORM "global_lock"();
jbe@0:       SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
jbe@0:       DELETE FROM "delegating_voter"
jbe@0:         WHERE "issue_id" = "issue_id_p";
jbe@0:       DELETE FROM "direct_voter"
jbe@0:         WHERE "issue_id" = "issue_id_p"
jbe@0:         AND "autoreject" = TRUE;
jbe@0:       DELETE FROM "direct_voter" USING "member"
jbe@0:         WHERE "direct_voter"."member_id" = "member"."id"
jbe@0:         AND "direct_voter"."issue_id" = "issue_id_p"
jbe@0:         AND "member"."active" = FALSE;
jbe@0:       UPDATE "direct_voter" SET "weight" = 1
jbe@0:         WHERE "issue_id" = "issue_id_p";
jbe@0:       PERFORM "add_vote_delegations"("issue_id_p");
jbe@0:       FOR "member_id_v" IN
jbe@0:         SELECT "interest"."member_id"
jbe@0:           FROM "interest"
jbe@0:           LEFT JOIN "direct_voter"
jbe@0:             ON "interest"."member_id" = "direct_voter"."member_id"
jbe@0:             AND "interest"."issue_id" = "direct_voter"."issue_id"
jbe@0:           LEFT JOIN "delegating_voter"
jbe@0:             ON "interest"."member_id" = "delegating_voter"."member_id"
jbe@0:             AND "interest"."issue_id" = "delegating_voter"."issue_id"
jbe@0:           WHERE "interest"."issue_id" = "issue_id_p"
jbe@0:           AND "interest"."autoreject" = TRUE
jbe@0:           AND "direct_voter"."member_id" ISNULL
jbe@0:           AND "delegating_voter"."member_id" ISNULL
jbe@0:         UNION SELECT "membership"."member_id"
jbe@0:           FROM "membership"
jbe@0:           LEFT JOIN "interest"
jbe@0:             ON "membership"."member_id" = "interest"."member_id"
jbe@0:             AND "interest"."issue_id" = "issue_id_p"
jbe@0:           LEFT JOIN "direct_voter"
jbe@0:             ON "membership"."member_id" = "direct_voter"."member_id"
jbe@0:             AND "direct_voter"."issue_id" = "issue_id_p"
jbe@0:           LEFT JOIN "delegating_voter"
jbe@0:             ON "membership"."member_id" = "delegating_voter"."member_id"
jbe@0:             AND "delegating_voter"."issue_id" = "issue_id_p"
jbe@0:           WHERE "membership"."area_id" = "issue_row"."area_id"
jbe@0:           AND "membership"."autoreject" = TRUE
jbe@0:           AND "interest"."autoreject" ISNULL
jbe@0:           AND "direct_voter"."member_id" ISNULL
jbe@0:           AND "delegating_voter"."member_id" ISNULL
jbe@0:       LOOP
jbe@0:         INSERT INTO "direct_voter" ("member_id", "issue_id", "autoreject")
jbe@0:           VALUES ("member_id_v", "issue_id_p", TRUE);
jbe@0:         INSERT INTO "vote" (
jbe@0:           "member_id",
jbe@0:           "issue_id",
jbe@0:           "initiative_id",
jbe@0:           "grade"
jbe@0:           ) SELECT
jbe@0:             "member_id_v" AS "member_id",
jbe@0:             "issue_id_p"  AS "issue_id",
jbe@0:             "id"          AS "initiative_id",
jbe@0:             -1            AS "grade"
jbe@0:           FROM "initiative" WHERE "issue_id" = "issue_id_p";
jbe@0:       END LOOP;
jbe@0:       PERFORM "add_vote_delegations"("issue_id_p");
jbe@4:       UPDATE "issue" SET
jbe@4:         "voter_count" = (
jbe@4:           SELECT coalesce(sum("weight"), 0)
jbe@4:           FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
jbe@4:         );
jbe@0:       UPDATE "initiative" SET
jbe@0:         "positive_votes" = "subquery"."positive_votes",
jbe@0:         "negative_votes" = "subquery"."negative_votes"
jbe@0:         FROM (
jbe@0:           SELECT
jbe@0:             "initiative_id",
jbe@4:             coalesce(
jbe@4:               sum(
jbe@4:                 CASE WHEN "grade" > 0 THEN "direct_voter"."weight" ELSE 0 END
jbe@4:               ),
jbe@4:               0
jbe@0:             ) AS "positive_votes",
jbe@4:             coalesce(
jbe@4:               sum(
jbe@4:                 CASE WHEN "grade" < 0 THEN "direct_voter"."weight" ELSE 0 END
jbe@4:               ),
jbe@4:               0
jbe@0:             ) AS "negative_votes"
jbe@0:           FROM "vote" JOIN "direct_voter"
jbe@0:           ON "vote"."member_id" = "direct_voter"."member_id"
jbe@0:           AND "vote"."issue_id" = "direct_voter"."issue_id"
jbe@0:           WHERE "vote"."issue_id" = "issue_id_p"
jbe@0:           GROUP BY "initiative_id"
jbe@0:         ) AS "subquery"
jbe@0:         WHERE "initiative"."admitted"
jbe@0:         AND "initiative"."id" = "subquery"."initiative_id";
jbe@0:       UPDATE "issue" SET "closed" = now() WHERE "id" = "issue_id_p";
jbe@0:     END;
jbe@0:   $$;
jbe@0: 
jbe@0: COMMENT ON FUNCTION "close_voting"
jbe@0:   ( "issue"."id"%TYPE )
jbe@0:   IS 'Closes the voting on an issue, and calculates positive and negative votes for each initiative; The ranking is not calculated yet, to keep the (locking) transaction short.';
jbe@0: 
jbe@0: 
jbe@0: CREATE FUNCTION "init_array"("dim_p" INTEGER)
jbe@0:   RETURNS INT4[]
jbe@0:   LANGUAGE 'plpgsql' IMMUTABLE AS $$
jbe@0:     DECLARE
jbe@0:       "i"          INTEGER;
jbe@0:       "ary_text_v" TEXT;
jbe@0:     BEGIN
jbe@0:       IF "dim_p" >= 1 THEN
jbe@0:         "ary_text_v" := '{NULL';
jbe@0:         "i" := "dim_p";
jbe@0:         LOOP
jbe@0:           "i" := "i" - 1;
jbe@0:           EXIT WHEN "i" = 0;
jbe@0:           "ary_text_v" := "ary_text_v" || ',NULL';
jbe@0:         END LOOP;
jbe@0:         "ary_text_v" := "ary_text_v" || '}';
jbe@0:         RETURN "ary_text_v"::INT4[][];
jbe@0:       ELSE
jbe@0:         RAISE EXCEPTION 'Dimension needs to be at least 1.';
jbe@0:       END IF;
jbe@0:     END;
jbe@0:   $$;
jbe@0: 
jbe@0: COMMENT ON FUNCTION "init_array"(INTEGER) IS 'Needed for PostgreSQL < 8.4, due to missing "array_fill" function';
jbe@0: 
jbe@0: 
jbe@0: CREATE FUNCTION "init_square_matrix"("dim_p" INTEGER)
jbe@0:   RETURNS INT4[][]
jbe@0:   LANGUAGE 'plpgsql' IMMUTABLE AS $$
jbe@0:     DECLARE
jbe@0:       "i"          INTEGER;
jbe@0:       "row_text_v" TEXT;
jbe@0:       "ary_text_v" TEXT;
jbe@0:     BEGIN
jbe@0:       IF "dim_p" >= 1 THEN
jbe@0:         "row_text_v" := '{NULL';
jbe@0:         "i" := "dim_p";
jbe@0:         LOOP
jbe@0:           "i" := "i" - 1;
jbe@0:           EXIT WHEN "i" = 0;
jbe@0:           "row_text_v" := "row_text_v" || ',NULL';
jbe@0:         END LOOP;
jbe@0:         "row_text_v" := "row_text_v" || '}';
jbe@0:         "ary_text_v" := '{' || "row_text_v";
jbe@0:         "i" := "dim_p";
jbe@0:         LOOP
jbe@0:           "i" := "i" - 1;
jbe@0:           EXIT WHEN "i" = 0;
jbe@0:           "ary_text_v" := "ary_text_v" || ',' || "row_text_v";
jbe@0:         END LOOP;
jbe@0:         "ary_text_v" := "ary_text_v" || '}';
jbe@0:         RETURN "ary_text_v"::INT4[][];
jbe@0:       ELSE
jbe@0:         RAISE EXCEPTION 'Dimension needs to be at least 1.';
jbe@0:       END IF;
jbe@0:     END;
jbe@0:   $$;
jbe@0: 
jbe@0: COMMENT ON FUNCTION "init_square_matrix"(INTEGER) IS 'Needed for PostgreSQL < 8.4, due to missing "array_fill" function';
jbe@0: 
jbe@0: 
jbe@0: CREATE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE)
jbe@0:   RETURNS VOID
jbe@0:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0:     DECLARE
jbe@0:       "dimension_v"     INTEGER;
jbe@0:       "matrix"          INT4[][];
jbe@0:       "i"               INTEGER;
jbe@0:       "j"               INTEGER;
jbe@0:       "k"               INTEGER;
jbe@0:       "battle_row"      "battle"%ROWTYPE;
jbe@0:       "rank_ary"        INT4[];
jbe@0:       "rank_v"          INT4;
jbe@0:       "done_v"          INTEGER;
jbe@0:       "winners_ary"     INTEGER[];
jbe@0:       "initiative_id_v" "initiative"."id"%TYPE;
jbe@0:     BEGIN
jbe@0:       PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE;
jbe@0:       -- Prepare matrix for Schulze-Method:
jbe@0:       SELECT count(1) INTO "dimension_v"
jbe@0:         FROM "battle_participant" WHERE "issue_id" = "issue_id_p";
jbe@0:       IF "dimension_v" = 1 THEN
jbe@0:         UPDATE "initiative" SET
jbe@0:           "rank" = 1
jbe@0:           FROM "battle_participant"
jbe@0:           WHERE "initiative"."issue_id" = "issue_id_p"
jbe@0:           AND "initiative"."id" = "battle_participant"."initiative_id";
jbe@0:       ELSIF "dimension_v" > 1 THEN
jbe@0:         "matrix" := "init_square_matrix"("dimension_v");  -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
jbe@0:         "i" := 1;
jbe@0:         "j" := 2;
jbe@0:         -- Fill matrix with data from "battle" view
jbe@0:         FOR "battle_row" IN
jbe@0:           SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p"
jbe@0:           ORDER BY "winning_initiative_id", "losing_initiative_id"
jbe@0:         LOOP
jbe@0:           "matrix"["i"]["j"] := "battle_row"."count";
jbe@0:           IF "j" = "dimension_v" THEN
jbe@0:             "i" := "i" + 1;
jbe@0:             "j" := 1;
jbe@0:           ELSE
jbe@0:             "j" := "j" + 1;
jbe@0:             IF "j" = "i" THEN
jbe@0:               "j" := "j" + 1;
jbe@0:             END IF;
jbe@0:           END IF;
jbe@0:         END LOOP;
jbe@0:         IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN
jbe@0:           RAISE EXCEPTION 'Wrong battle count (should not happen)';
jbe@0:         END IF;
jbe@0:         -- Delete losers from matrix:
jbe@0:         "i" := 1;
jbe@0:         LOOP
jbe@0:           "j" := "i" + 1;
jbe@0:           LOOP
jbe@0:             IF "i" != "j" THEN
jbe@0:               IF "matrix"["i"]["j"] < "matrix"["j"]["i"] THEN
jbe@0:                 "matrix"["i"]["j"] := 0;
jbe@0:               ELSIF matrix[j][i] < matrix[i][j] THEN
jbe@0:                 "matrix"["j"]["i"] := 0;
jbe@0:               ELSE
jbe@0:                 "matrix"["i"]["j"] := 0;
jbe@0:                 "matrix"["j"]["i"] := 0;
jbe@0:               END IF;
jbe@0:             END IF;
jbe@0:             EXIT WHEN "j" = "dimension_v";
jbe@0:             "j" := "j" + 1;
jbe@0:           END LOOP;
jbe@0:           EXIT WHEN "i" = "dimension_v" - 1;
jbe@0:           "i" := "i" + 1;
jbe@0:         END LOOP;
jbe@0:         -- Find best paths:
jbe@0:         "i" := 1;
jbe@0:         LOOP
jbe@0:           "j" := 1;
jbe@0:           LOOP
jbe@0:             IF "i" != "j" THEN
jbe@0:               "k" := 1;
jbe@0:               LOOP
jbe@0:                 IF "i" != "k" AND "j" != "k" THEN
jbe@0:                   IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN
jbe@0:                     IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN
jbe@0:                       "matrix"["j"]["k"] := "matrix"["j"]["i"];
jbe@0:                     END IF;
jbe@0:                   ELSE
jbe@0:                     IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN
jbe@0:                       "matrix"["j"]["k"] := "matrix"["i"]["k"];
jbe@0:                     END IF;
jbe@0:                   END IF;
jbe@0:                 END IF;
jbe@0:                 EXIT WHEN "k" = "dimension_v";
jbe@0:                 "k" := "k" + 1;
jbe@0:               END LOOP;
jbe@0:             END IF;
jbe@0:             EXIT WHEN "j" = "dimension_v";
jbe@0:             "j" := "j" + 1;
jbe@0:           END LOOP;
jbe@0:           EXIT WHEN "i" = "dimension_v";
jbe@0:           "i" := "i" + 1;
jbe@0:         END LOOP;
jbe@0:         -- Determine order of winners:
jbe@0:         "rank_ary" := "init_array"("dimension_v");  -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
jbe@0:         "rank_v" := 1;
jbe@0:         "done_v" := 0;
jbe@0:         LOOP
jbe@0:           "winners_ary" := '{}';
jbe@0:           "i" := 1;
jbe@0:           LOOP
jbe@0:             IF "rank_ary"["i"] ISNULL THEN
jbe@0:               "j" := 1;
jbe@0:               LOOP
jbe@0:                 IF
jbe@0:                   "i" != "j" AND
jbe@0:                   "rank_ary"["j"] ISNULL AND
jbe@0:                   "matrix"["j"]["i"] > "matrix"["i"]["j"]
jbe@0:                 THEN
jbe@0:                   -- someone else is better
jbe@0:                   EXIT;
jbe@0:                 END IF;
jbe@0:                 IF "j" = "dimension_v" THEN
jbe@0:                   -- noone is better
jbe@0:                   "winners_ary" := "winners_ary" || "i";
jbe@0:                   EXIT;
jbe@0:                 END IF;
jbe@0:                 "j" := "j" + 1;
jbe@0:               END LOOP;
jbe@0:             END IF;
jbe@0:             EXIT WHEN "i" = "dimension_v";
jbe@0:             "i" := "i" + 1;
jbe@0:           END LOOP;
jbe@0:           "i" := 1;
jbe@0:           LOOP
jbe@0:             "rank_ary"["winners_ary"["i"]] := "rank_v";
jbe@0:             "done_v" := "done_v" + 1;
jbe@0:             EXIT WHEN "i" = array_upper("winners_ary", 1);
jbe@0:             "i" := "i" + 1;
jbe@0:           END LOOP;
jbe@0:           EXIT WHEN "done_v" = "dimension_v";
jbe@0:           "rank_v" := "rank_v" + 1;
jbe@0:         END LOOP;
jbe@0:         -- write preliminary ranks:
jbe@0:         "i" := 1;
jbe@0:         FOR "initiative_id_v" IN
jbe@0:           SELECT "initiative"."id"
jbe@0:           FROM "initiative" JOIN "battle_participant"
jbe@0:           ON "initiative"."id" = "battle_participant"."initiative_id"
jbe@0:           WHERE "initiative"."issue_id" = "issue_id_p"
jbe@0:           ORDER BY "initiative"."id"
jbe@0:         LOOP
jbe@0:           UPDATE "initiative" SET "rank" = "rank_ary"["i"]
jbe@0:             WHERE "id" = "initiative_id_v";
jbe@0:           "i" := "i" + 1;
jbe@0:         END LOOP;
jbe@0:         IF "i" != "dimension_v" + 1 THEN
jbe@0:           RAISE EXCEPTION 'Wrong winner count (should not happen)';
jbe@0:         END IF;
jbe@0:         -- straighten ranks (start counting with 1, no equal ranks):
jbe@0:         "rank_v" := 1;
jbe@0:         FOR "initiative_id_v" IN
jbe@0:           SELECT "id" FROM "initiative"
jbe@0:           WHERE "issue_id" = "issue_id_p" AND "rank" NOTNULL
jbe@0:           ORDER BY
jbe@0:             "rank",
jbe@0:             "vote_ratio"("positive_votes", "negative_votes") DESC,
jbe@0:             "id"
jbe@0:         LOOP
jbe@0:           UPDATE "initiative" SET "rank" = "rank_v"
jbe@0:             WHERE "id" = "initiative_id_v";
jbe@0:           "rank_v" := "rank_v" + 1;
jbe@0:         END LOOP;
jbe@0:       END IF;
jbe@0:       -- mark issue as finished
jbe@0:       UPDATE "issue" SET "ranks_available" = TRUE
jbe@0:         WHERE "id" = "issue_id_p";
jbe@0:       RETURN;
jbe@0:     END;
jbe@0:   $$;
jbe@0: 
jbe@0: COMMENT ON FUNCTION "calculate_ranks"
jbe@0:   ( "issue"."id"%TYPE )
jbe@0:   IS 'Determine ranking (Votes have to be counted first)';
jbe@0: 
jbe@0: 
jbe@0: 
jbe@0: -----------------------------
jbe@0: -- Automatic state changes --
jbe@0: -----------------------------
jbe@0: 
jbe@0: 
jbe@0: CREATE FUNCTION "check_issue"
jbe@0:   ( "issue_id_p" "issue"."id"%TYPE )
jbe@0:   RETURNS VOID
jbe@0:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0:     DECLARE
jbe@0:       "issue_row"         "issue"%ROWTYPE;
jbe@0:       "policy_row"        "policy"%ROWTYPE;
jbe@0:       "voting_requested_v" BOOLEAN;
jbe@0:     BEGIN
jbe@0:       PERFORM "global_lock"();
jbe@0:       SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
jbe@0:       IF "issue_row"."closed" ISNULL THEN
jbe@0:         SELECT * INTO "policy_row" FROM "policy"
jbe@0:           WHERE "id" = "issue_row"."policy_id";
jbe@3:         IF "issue_row"."fully_frozen" ISNULL THEN
jbe@0:           PERFORM "create_snapshot"("issue_id_p");
jbe@0:           SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
jbe@0:         END IF;
jbe@0:         IF "issue_row"."accepted" ISNULL THEN
jbe@0:           IF EXISTS (
jbe@0:             SELECT NULL FROM "initiative"
jbe@0:             WHERE "issue_id" = "issue_id_p"
jbe@0:             AND "supporter_count" > 0
jbe@0:             AND "supporter_count" * "policy_row"."issue_quorum_den"
jbe@0:             >= "issue_row"."population" * "policy_row"."issue_quorum_num"
jbe@0:           ) THEN
jbe@3:             PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
jbe@0:             "issue_row"."accepted" = now();  -- NOTE: "issue_row" used later
jbe@0:             UPDATE "issue" SET "accepted" = "issue_row"."accepted"
jbe@0:               WHERE "id" = "issue_row"."id";
jbe@0:           ELSIF
jbe@3:             now() >= "issue_row"."created" + "policy_row"."admission_time"
jbe@0:           THEN
jbe@0:             PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
jbe@0:             UPDATE "issue" SET "closed" = now()
jbe@0:               WHERE "id" = "issue_row"."id";
jbe@0:           END IF;
jbe@0:         END IF;
jbe@0:         IF
jbe@0:           "issue_row"."accepted" NOTNULL AND
jbe@3:           "issue_row"."half_frozen" ISNULL
jbe@0:         THEN
jbe@0:           SELECT
jbe@0:             CASE
jbe@0:               WHEN "vote_now" * 2 > "issue_row"."population" THEN
jbe@0:                 TRUE
jbe@0:               WHEN "vote_later" * 2 > "issue_row"."population" THEN
jbe@0:                 FALSE
jbe@0:               ELSE NULL
jbe@0:             END
jbe@0:             INTO "voting_requested_v"
jbe@0:             FROM "issue" WHERE "id" = "issue_id_p";
jbe@0:           IF
jbe@0:             "voting_requested_v" OR (
jbe@3:               "voting_requested_v" ISNULL AND
jbe@3:               now() >= "issue_row"."accepted" + "policy_row"."discussion_time"
jbe@0:             )
jbe@0:           THEN
jbe@3:             "issue_row"."half_frozen" = now();  -- NOTE: "issue_row" used later
jbe@3:             UPDATE "issue" SET "half_frozen" = "issue_row"."half_frozen"
jbe@3:               WHERE "id" = "issue_row"."id";
jbe@0:           END IF;
jbe@0:         END IF;
jbe@0:         IF
jbe@3:           "issue_row"."half_frozen" NOTNULL AND
jbe@3:           "issue_row"."fully_frozen" ISNULL AND
jbe@3:           now() >= "issue_row"."half_frozen" + "policy_row"."verification_time"
jbe@3:         THEN
jbe@3:           "issue_row"."fully_frozen" = now();  -- NOTE: "issue_row" used later
jbe@3:           PERFORM "freeze_after_snapshot"("issue_id_p");
jbe@3:         END IF;
jbe@3:         IF
jbe@3:           "issue_row"."fully_frozen" NOTNULL AND
jbe@3:           now() >= "issue_row"."fully_frozen" + "policy_row"."voting_time"
jbe@0:         THEN
jbe@0:           PERFORM "close_voting"("issue_id_p");
jbe@0:         END IF;
jbe@0:       END IF;
jbe@0:       RETURN;
jbe@0:     END;
jbe@0:   $$;
jbe@0: 
jbe@0: COMMENT ON FUNCTION "check_issue"
jbe@0:   ( "issue"."id"%TYPE )
jbe@0:   IS 'Precalculate supporter counts etc. for a given issue, and check, if status change is required; At end of voting the ranking is not calculated by this function, but must be calculated in a seperate transaction using the "calculate_ranks" function.';
jbe@0: 
jbe@0: 
jbe@0: CREATE FUNCTION "check_everything"()
jbe@0:   RETURNS VOID
jbe@0:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0:     DECLARE
jbe@0:       "issue_id_v" "issue"."id"%TYPE;
jbe@0:     BEGIN
jbe@1:       DELETE FROM "expired_session";
jbe@4:       PERFORM "calculate_member_counts"();
jbe@4:       FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP
jbe@0:         PERFORM "check_issue"("issue_id_v");
jbe@0:       END LOOP;
jbe@4:       FOR "issue_id_v" IN SELECT "id" FROM "issue_with_ranks_missing" LOOP
jbe@0:         PERFORM "calculate_ranks"("issue_id_v");
jbe@0:       END LOOP;
jbe@0:       RETURN;
jbe@0:     END;
jbe@0:   $$;
jbe@0: 
jbe@0: COMMENT ON FUNCTION "check_everything"() IS 'Perform "check_issue" for every open issue, and if possible, automatically calculate ranks. Use this function only for development and debugging purposes, as long transactions with exclusive locking may result.';
jbe@0: 
jbe@0: 
jbe@0: 
jbe@0: COMMIT;