
jbe@115: BEGIN;  -- NOTE: file contains additional statements AFTER this BEGIN/COMMIT block!
jbe@115: 
jbe@115: 
jbe@115: -- Update version information:
jbe@115: 
jbe@115: CREATE OR REPLACE VIEW "liquid_feedback_version" AS
jbe@115:   SELECT * FROM (VALUES ('1.4.0_rc1', 1, 4, -1))
jbe@115:   AS "subquery"("string", "major", "minor", "revision");
jbe@115: 
jbe@115: 
jbe@115: -- New columns "notify_level" and "notify_event_id" in "member" table:
jbe@115: 
jbe@115: CREATE TYPE "notify_level" AS ENUM
jbe@115:   ('none', 'voting', 'verification', 'discussion', 'all');
jbe@115: 
jbe@115: COMMENT ON TYPE "notify_level" IS 'Level of notification: ''none'' = no notifications, ''voting'' = notifications about finished issues and issues in voting, ''verification'' = notifications about finished issues, issues in voting and verification phase, ''discussion'' = notifications about everything except issues in admission phase, ''all'' = notifications about everything';
jbe@115: 
jbe@115: ALTER TABLE "member" ADD "notify_level" "notify_level" NOT NULL DEFAULT 'none';
jbe@115: ALTER TABLE "member" ADD "notify_event_id" INT8;
jbe@115: 
jbe@115: COMMENT ON COLUMN "member"."notify_level"    IS 'Selects which event notifications are to be sent to the "notify_email" mail address';
jbe@115: COMMENT ON COLUMN "member"."notify_event_id" IS 'Latest "id" of an "event" the member was notified about';
jbe@115: 
jbe@115: 
jbe@115: -- Add primary key with type SERIAL8 (INT8) for "invite_code" table:
jbe@115: 
jbe@115: ALTER TABLE "invite_code" DROP CONSTRAINT "invite_code_pkey";
jbe@115: ALTER TABLE "invite_code" ALTER "code" SET NOT NULL;
jbe@115: ALTER TABLE "invite_code" ADD UNIQUE ("code");
jbe@115: ALTER TABLE "invite_code" ADD "id" SERIAL8 PRIMARY KEY;
jbe@115: 
jbe@115: 
jbe@115: -- Add index for "other_member_id" column of "contact" table:
jbe@115: 
jbe@115: CREATE INDEX "contact_other_member_id_idx" ON "contact" ("other_member_id");
jbe@115: 
jbe@115: 
jbe@115: -- New table "ignored_member":
jbe@115: 
jbe@115: CREATE TABLE "ignored_member" (
jbe@115:         PRIMARY KEY ("member_id", "other_member_id"),
jbe@115:         "member_id"             INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@115:         "other_member_id"       INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
jbe@115: CREATE INDEX "ignored_member_other_member_id_idx" ON "ignored_member" ("other_member_id");
jbe@115: 
jbe@115: COMMENT ON TABLE "ignored_member" IS 'Possibility to filter other members';
jbe@115: 
jbe@115: COMMENT ON COLUMN "ignored_member"."member_id"       IS 'Member ignoring someone';
jbe@115: COMMENT ON COLUMN "ignored_member"."other_member_id" IS 'Member being ignored';
jbe@115: 
jbe@115: 
jbe@115: -- New table "unit" with default entry:
jbe@115: 
jbe@115: CREATE TABLE "unit" (
jbe@115:         "id"                    SERIAL4         PRIMARY KEY,
jbe@115:         "parent_id"             INT4            REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@115:         "active"                BOOLEAN         NOT NULL DEFAULT TRUE,
jbe@115:         "name"                  TEXT            NOT NULL,
jbe@115:         "description"           TEXT            NOT NULL DEFAULT '',
jbe@115:         "member_count"          INT4,
jbe@115:         "text_search_data"      TSVECTOR );
jbe@115: CREATE INDEX "unit_root_idx" ON "unit" ("id") WHERE "parent_id" ISNULL;
jbe@115: CREATE INDEX "unit_parent_id_idx" ON "unit" ("parent_id");
jbe@115: CREATE INDEX "unit_active_idx" ON "unit" ("active");
jbe@115: CREATE INDEX "unit_text_search_data_idx" ON "unit" USING gin ("text_search_data");
jbe@115: CREATE TRIGGER "update_text_search_data"
jbe@115:   BEFORE INSERT OR UPDATE ON "unit"
jbe@115:   FOR EACH ROW EXECUTE PROCEDURE
jbe@115:   tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
jbe@115:     "name", "description" );
jbe@115: 
jbe@115: COMMENT ON TABLE "unit" IS 'Organizational units organized as trees; Delegations are not inherited through these trees.';
jbe@115: 
jbe@115: COMMENT ON COLUMN "unit"."parent_id"    IS 'Parent id of tree node; Multiple roots allowed';
jbe@115: COMMENT ON COLUMN "unit"."active"       IS 'TRUE means new issues can be created in units of this area';
jbe@115: COMMENT ON COLUMN "unit"."member_count" IS 'Count of members as determined by column "voting_right" in table "privilege"';
jbe@115: 
jbe@115: INSERT INTO "unit" ("name") VALUES ('Main');  -- NOTE: gets id 1
jbe@115: 
jbe@115: 
jbe@115: -- New column "unit_id" in table "area":
jbe@115: 
jbe@115: ALTER TABLE "area" ADD "unit_id" INT4 DEFAULT 1
jbe@115:   NOT NULL REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE;
jbe@115: ALTER TABLE "area" ALTER "unit_id" DROP DEFAULT;
jbe@115: 
jbe@115: CREATE INDEX "area_unit_id_idx" ON "area" ("unit_id");
jbe@115: 
jbe@115: 
jbe@115: -- Issue states:
jbe@115: 
jbe@115: CREATE TYPE "issue_state" AS ENUM (
jbe@115:         'admission', 'discussion', 'verification', 'voting',
jbe@115:         'canceled_revoked_before_accepted',
jbe@115:         'canceled_issue_not_accepted',
jbe@115:         'canceled_after_revocation_during_discussion',
jbe@115:         'canceled_after_revocation_during_verification',
jbe@115:         'calculation',
jbe@115:         'canceled_no_initiative_admitted',
jbe@115:         'finished_without_winner', 'finished_with_winner');
jbe@115: 
jbe@115: COMMENT ON TYPE "issue_state" IS 'State of issues';
jbe@115: 
jbe@205: ALTER TABLE "issue" ADD "state" "issue_state" DEFAULT NULL;
jbe@205: ALTER TABLE "issue" ALTER "state" SET DEFAULT 'admission';
jbe@115: 
jbe@115: -- NOTE: Filling new column with values is done after this transaction (see below)
jbe@115: 
jbe@115: 
jbe@115: -- New column "revoked_by_member_id" in table "initiative":
jbe@115: 
jbe@115: ALTER TABLE "initiative" ADD "revoked_by_member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE;
jbe@115: 
jbe@115: COMMENT ON COLUMN "initiative"."revoked_by_member_id" IS 'Member, who decided to revoked the initiative';
jbe@115: 
jbe@115: -- NOTE: Filling new column with values is done after this transaction (see below)
jbe@115: 
jbe@115: 
jbe@115: -- New table "ignored_initiative":
jbe@115: 
jbe@115: CREATE TABLE "ignored_initiative" (
jbe@115:         PRIMARY KEY ("initiative_id", "member_id"),
jbe@115:         "initiative_id"         INT4            REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@115:         "member_id"             INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
jbe@115: CREATE INDEX "ignored_initiative_member_id_idx" ON "ignored_initiative" ("member_id");
jbe@115: 
jbe@115: COMMENT ON TABLE "ignored_initiative" IS 'Possibility to filter initiatives';
jbe@115: 
jbe@115: 
jbe@115: -- New table "invite_code_unit":
jbe@115: 
jbe@115: CREATE TABLE "invite_code_unit" (
jbe@115:         PRIMARY KEY ("invite_code_id", "unit_id"),
jbe@115:         "invite_code_id"        INT8            REFERENCES "invite_code" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@115:         "unit_id"               INT4            REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
jbe@115: 
jbe@115: COMMENT ON TABLE "invite_code_unit" IS 'Units where accounts created with a given invite codes get voting rights';
jbe@115: 
jbe@117: INSERT INTO "invite_code_unit" ("invite_code_id", "unit_id")
jbe@124:   SELECT "id" AS "invite_code_id", 1 AS "unit_id" FROM "invite_code";
jbe@115: 
jbe@115: 
jbe@115: -- New table "privilege":
jbe@115: 
jbe@115: CREATE TABLE "privilege" (
jbe@115:         PRIMARY KEY ("unit_id", "member_id"),
jbe@115:         "unit_id"               INT4            REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@115:         "member_id"             INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@115:         "admin_manager"         BOOLEAN         NOT NULL DEFAULT FALSE,
jbe@115:         "unit_manager"          BOOLEAN         NOT NULL DEFAULT FALSE,
jbe@115:         "area_manager"          BOOLEAN         NOT NULL DEFAULT FALSE,
jbe@115:         "voting_right_manager"  BOOLEAN         NOT NULL DEFAULT FALSE,
jbe@115:         "voting_right"          BOOLEAN         NOT NULL DEFAULT TRUE );
jbe@115: 
jbe@115: COMMENT ON TABLE "privilege" IS 'Members rights related to each unit';
jbe@115: 
jbe@115: COMMENT ON COLUMN "privilege"."admin_manager"        IS 'Grant/revoke admin privileges to/from other users';
jbe@115: COMMENT ON COLUMN "privilege"."unit_manager"         IS 'Create or lock sub units';
jbe@115: COMMENT ON COLUMN "privilege"."area_manager"         IS 'Create or lock areas and set area parameters';
jbe@115: COMMENT ON COLUMN "privilege"."voting_right_manager" IS 'Select which members are allowed to discuss and vote inside the unit';
jbe@115: COMMENT ON COLUMN "privilege"."voting_right"         IS 'Right to discuss and vote';
jbe@115: 
jbe@124: INSERT INTO "privilege" ("unit_id", "member_id", "voting_right")
jbe@124:   SELECT 1 AS "unit_id", "id" AS "member_id", TRUE AS "voting_right"
jbe@124:   FROM "member";
jbe@124: 
jbe@115: 
jbe@115: -- Remove table "ignored_issue", which is no longer existent:
jbe@115: 
jbe@115: DROP TABLE "ignored_issue";
jbe@115: 
jbe@115: 
jbe@115: -- Replace TYPE "delegation_scope" with a new type, where 'global' is replaced by 'unit':
jbe@115: 
jbe@115: ALTER TYPE "delegation_scope" RENAME TO "delegation_scope_old";  -- NOTE: dropped later
jbe@115: CREATE TYPE "delegation_scope" AS ENUM ('unit', 'area', 'issue');
jbe@115: COMMENT ON TYPE "delegation_scope" IS 'Scope for delegations: ''unit'', ''area'', or ''issue'' (order is relevant)';
jbe@115: 
jbe@115: 
jbe@122: -- Delete views and functions being dependent on type "delegation_scope":
jbe@115: 
jbe@115: DROP FUNCTION "delegation_chain"
jbe@115:   ( "member_id_p" "member"."id"%TYPE,
jbe@115:     "area_id_p"   "area"."id"%TYPE,
jbe@115:     "issue_id_p"  "issue"."id"%TYPE );
jbe@115: 
jbe@115: DROP FUNCTION "delegation_chain"
jbe@115:   ( "member_id_p"           "member"."id"%TYPE,
jbe@115:     "area_id_p"             "area"."id"%TYPE,
jbe@115:     "issue_id_p"            "issue"."id"%TYPE,
jbe@115:     "simulate_trustee_id_p" "member"."id"%TYPE );
jbe@115: 
jbe@115: DROP TYPE "delegation_chain_row";
jbe@115: 
jbe@115: DROP VIEW "issue_delegation";
jbe@115: DROP VIEW "area_delegation";
jbe@115: DROP VIEW "global_delegation";
jbe@115: DROP VIEW "active_delegation";
jbe@115: 
jbe@115: 
jbe@122: -- Modify "delegation" table to use new "delegation_scope" type:
jbe@115: 
jbe@115: ALTER TABLE "delegation" DROP CONSTRAINT "no_global_delegation_to_null";
jbe@115: ALTER TABLE "delegation" DROP CONSTRAINT "area_id_and_issue_id_set_according_to_scope";
jbe@115: 
jbe@115: DROP INDEX "delegation_global_truster_id_unique_idx";
jbe@115: 
jbe@115: ALTER TABLE "delegation" ALTER "scope" TYPE "delegation_scope"
jbe@115:   USING CASE WHEN "scope" = 'global'
jbe@115:   THEN 'unit'::"delegation_scope"
jbe@115:   ELSE "scope"::text::"delegation_scope" END;
jbe@115: 
jbe@115: ALTER TABLE "delegation" ADD "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE;
jbe@115: 
jbe@115: ALTER TABLE "delegation" ADD CONSTRAINT "no_unit_delegation_to_null"
jbe@115:   CHECK ("trustee_id" NOTNULL OR "scope" != 'unit');
jbe@115: 
jbe@115: ALTER TABLE "delegation" ADD UNIQUE ("unit_id", "truster_id");
jbe@115: 
jbe@115: COMMENT ON COLUMN "delegation"."unit_id"  IS 'Reference to unit, if delegation is unit-wide, otherwise NULL';
jbe@115: 
jbe@115: -- NOTE: Column "unit_id" filled after transaction (see below)
jbe@115: 
jbe@115: 
jbe@122: -- Modify snapshot tables to use new "delegation_scope" type:
jbe@115: 
jbe@115: ALTER TABLE "delegating_population_snapshot" ALTER "scope" TYPE "delegation_scope"
jbe@115:   USING CASE WHEN "scope" = 'global'
jbe@115:   THEN 'unit'::"delegation_scope"
jbe@115:   ELSE "scope"::text::"delegation_scope" END;
jbe@115: 
jbe@115: ALTER TABLE "delegating_interest_snapshot" ALTER "scope" TYPE "delegation_scope"
jbe@115:   USING CASE WHEN "scope" = 'global'
jbe@115:   THEN 'unit'::"delegation_scope"
jbe@115:   ELSE "scope"::text::"delegation_scope" END;
jbe@115: 
jbe@115: ALTER TABLE "delegating_voter" ALTER "scope" TYPE "delegation_scope"
jbe@115:   USING CASE WHEN "scope" = 'global'
jbe@115:   THEN 'unit'::"delegation_scope"
jbe@115:   ELSE "scope"::text::"delegation_scope" END;
jbe@115: 
jbe@115: 
jbe@115: -- New table "non_voter":
jbe@115: 
jbe@115: CREATE TABLE "non_voter" (
jbe@115:         PRIMARY KEY ("issue_id", "member_id"),
jbe@115:         "issue_id"              INT4            REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@115:         "member_id"             INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
jbe@115: CREATE INDEX "non_voter_member_id_idx" ON "non_voter" ("member_id");
jbe@115: 
jbe@115: COMMENT ON TABLE "non_voter" IS 'Members who decided to not vote directly on an issue';
jbe@115: 
jbe@115: 
jbe@122: -- New tables "issue_comment" and "rendered_issue_comment":
jbe@115: 
jbe@115: CREATE TABLE "issue_comment" (
jbe@115:         PRIMARY KEY ("issue_id", "member_id"),
jbe@115:         "issue_id"              INT4            REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@115:         "member_id"             INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@115:         "changed"               TIMESTAMPTZ     NOT NULL DEFAULT now(),
jbe@115:         "formatting_engine"     TEXT,
jbe@115:         "content"               TEXT            NOT NULL,
jbe@115:         "text_search_data"      TSVECTOR );
jbe@115: CREATE INDEX "issue_comment_member_id_idx" ON "issue_comment" ("member_id");
jbe@115: CREATE INDEX "issue_comment_text_search_data_idx" ON "issue_comment" USING gin ("text_search_data");
jbe@115: CREATE TRIGGER "update_text_search_data"
jbe@115:   BEFORE INSERT OR UPDATE ON "issue_comment"
jbe@115:   FOR EACH ROW EXECUTE PROCEDURE
jbe@115:   tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "content");
jbe@115: 
jbe@115: COMMENT ON TABLE "issue_comment" IS 'Place to store free comments of members related to issues';
jbe@115: 
jbe@115: COMMENT ON COLUMN "issue_comment"."changed" IS 'Time the comment was last changed';
jbe@115: 
jbe@115: CREATE TABLE "rendered_issue_comment" (
jbe@115:         PRIMARY KEY ("issue_id", "member_id", "format"),
jbe@115:         FOREIGN KEY ("issue_id", "member_id")
jbe@115:           REFERENCES "issue_comment" ("issue_id", "member_id")
jbe@115:           ON DELETE CASCADE ON UPDATE CASCADE,
jbe@115:         "issue_id"              INT4,
jbe@115:         "member_id"             INT4,
jbe@115:         "format"                TEXT,
jbe@115:         "content"               TEXT            NOT NULL );
jbe@115: 
jbe@115: COMMENT ON TABLE "rendered_issue_comment" IS 'This table may be used by frontends to cache "rendered" issue comments (e.g. HTML output generated from wiki text)';
jbe@115: 
jbe@115: 
jbe@122: -- New tables "voting_comment" and "rendered_voting_comment":
jbe@115: 
jbe@115: CREATE TABLE "voting_comment" (
jbe@115:         PRIMARY KEY ("issue_id", "member_id"),
jbe@115:         "issue_id"              INT4            REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@115:         "member_id"             INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@115:         "changed"               TIMESTAMPTZ,
jbe@115:         "formatting_engine"     TEXT,
jbe@115:         "content"               TEXT            NOT NULL,
jbe@115:         "text_search_data"      TSVECTOR );
jbe@115: CREATE INDEX "voting_comment_member_id_idx" ON "voting_comment" ("member_id");
jbe@115: CREATE INDEX "voting_comment_text_search_data_idx" ON "voting_comment" USING gin ("text_search_data");
jbe@115: CREATE TRIGGER "update_text_search_data"
jbe@115:   BEFORE INSERT OR UPDATE ON "voting_comment"
jbe@115:   FOR EACH ROW EXECUTE PROCEDURE
jbe@115:   tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "content");
jbe@115: 
jbe@115: COMMENT ON TABLE "voting_comment" IS 'Storage for comments of voters to be published after voting has finished.';
jbe@115: 
jbe@115: COMMENT ON COLUMN "voting_comment"."changed" IS 'Is to be set or updated by the frontend, if comment was inserted or updated AFTER the issue has been closed. Otherwise it shall be set to NULL.';
jbe@115: 
jbe@115: CREATE TABLE "rendered_voting_comment" (
jbe@115:         PRIMARY KEY ("issue_id", "member_id", "format"),
jbe@115:         FOREIGN KEY ("issue_id", "member_id")
jbe@115:           REFERENCES "voting_comment" ("issue_id", "member_id")
jbe@115:           ON DELETE CASCADE ON UPDATE CASCADE,
jbe@115:         "issue_id"              INT4,
jbe@115:         "member_id"             INT4,
jbe@115:         "format"                TEXT,
jbe@115:         "content"               TEXT            NOT NULL );
jbe@115: 
jbe@115: COMMENT ON TABLE "rendered_voting_comment" IS 'This table may be used by frontends to cache "rendered" voting comments (e.g. HTML output generated from wiki text)';
jbe@115: 
jbe@115: 
jbe@115: -- New table "event":
jbe@115: 
jbe@115: CREATE TYPE "event_type" AS ENUM (
jbe@115:         'issue_state_changed',
jbe@115:         'initiative_created_in_new_issue',
jbe@115:         'initiative_created_in_existing_issue',
jbe@115:         'initiative_revoked',
jbe@115:         'new_draft_created',
jbe@115:         'suggestion_created');
jbe@115: 
jbe@115: COMMENT ON TYPE "event_type" IS 'Type used for column "event" of table "event"';
jbe@115: 
jbe@115: CREATE TABLE "event" (
jbe@115:         "id"                    SERIAL8         PRIMARY KEY,
jbe@115:         "occurrence"            TIMESTAMPTZ     NOT NULL DEFAULT now(),
jbe@115:         "event"                 "event_type"    NOT NULL,
jbe@115:         "member_id"             INT4            REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
jbe@115:         "issue_id"              INT4            REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@115:         "state"                 "issue_state"   CHECK ("state" != 'calculation'),
jbe@115:         "initiative_id"         INT4,
jbe@115:         "draft_id"              INT8,
jbe@115:         "suggestion_id"         INT8,
jbe@115:         FOREIGN KEY ("issue_id", "initiative_id")
jbe@115:           REFERENCES "initiative" ("issue_id", "id")
jbe@115:           ON DELETE CASCADE ON UPDATE CASCADE,
jbe@115:         FOREIGN KEY ("initiative_id", "draft_id")
jbe@115:           REFERENCES "draft" ("initiative_id", "id")
jbe@115:           ON DELETE CASCADE ON UPDATE CASCADE,
jbe@115:         FOREIGN KEY ("initiative_id", "suggestion_id")
jbe@115:           REFERENCES "suggestion" ("initiative_id", "id")
jbe@115:           ON DELETE CASCADE ON UPDATE CASCADE,
jbe@115:         CONSTRAINT "null_constraints_for_issue_state_changed" CHECK (
jbe@115:           "event" != 'issue_state_changed' OR (
jbe@115:             "member_id"     ISNULL  AND
jbe@115:             "issue_id"      NOTNULL AND
jbe@115:             "state"         NOTNULL AND
jbe@115:             "initiative_id" ISNULL  AND
jbe@115:             "draft_id"      ISNULL  AND
jbe@115:             "suggestion_id" ISNULL  )),
jbe@115:         CONSTRAINT "null_constraints_for_initiative_creation_or_revocation_or_new_draft" CHECK (
jbe@115:           "event" NOT IN (
jbe@115:             'initiative_created_in_new_issue',
jbe@115:             'initiative_created_in_existing_issue',
jbe@115:             'initiative_revoked',
jbe@115:             'new_draft_created'
jbe@115:           ) OR (
jbe@115:             "member_id"     NOTNULL AND
jbe@115:             "issue_id"      NOTNULL AND
jbe@115:             "state"         NOTNULL AND
jbe@115:             "initiative_id" NOTNULL AND
jbe@115:             "draft_id"      NOTNULL AND
jbe@115:             "suggestion_id" ISNULL  )),
jbe@115:         CONSTRAINT "null_constraints_for_suggestion_creation" CHECK (
jbe@115:           "event" != 'suggestion_created' OR (
jbe@115:             "member_id"     NOTNULL AND
jbe@115:             "issue_id"      NOTNULL AND
jbe@115:             "state"         NOTNULL AND
jbe@115:             "initiative_id" NOTNULL AND
jbe@115:             "draft_id"      ISNULL  AND
jbe@115:             "suggestion_id" NOTNULL )) );
jbe@115: 
jbe@115: COMMENT ON TABLE "event" IS 'Event table, automatically filled by triggers';
jbe@115: 
jbe@115: COMMENT ON COLUMN "event"."occurrence" IS 'Point in time, when event occurred';
jbe@115: COMMENT ON COLUMN "event"."event"      IS 'Type of event (see TYPE "event_type")';
jbe@115: COMMENT ON COLUMN "event"."member_id"  IS 'Member who caused the event, if applicable';
jbe@115: COMMENT ON COLUMN "event"."state"      IS 'If issue_id is set: state of affected issue; If state changed: new state';
jbe@115: 
jbe@115: 
jbe@115: -- Triggers to fill "event" table:
jbe@115: 
jbe@115: CREATE FUNCTION "write_event_issue_state_changed_trigger"()
jbe@115:   RETURNS TRIGGER
jbe@115:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@115:     BEGIN
jbe@115:       IF NEW."state" != OLD."state" AND NEW."state" != 'calculation' THEN
jbe@115:         INSERT INTO "event" ("event", "issue_id", "state")
jbe@115:           VALUES ('issue_state_changed', NEW."id", NEW."state");
jbe@115:       END IF;
jbe@115:       RETURN NULL;
jbe@115:     END;
jbe@115:   $$;
jbe@115: 
jbe@115: CREATE TRIGGER "write_event_issue_state_changed"
jbe@115:   AFTER UPDATE ON "issue" FOR EACH ROW EXECUTE PROCEDURE
jbe@115:   "write_event_issue_state_changed_trigger"();
jbe@115: 
jbe@115: COMMENT ON FUNCTION "write_event_issue_state_changed_trigger"() IS 'Implementation of trigger "write_event_issue_state_changed" on table "issue"';
jbe@115: COMMENT ON TRIGGER "write_event_issue_state_changed" ON "issue" IS 'Create entry in "event" table on "state" change';
jbe@115: 
jbe@115: CREATE FUNCTION "write_event_initiative_or_draft_created_trigger"()
jbe@115:   RETURNS TRIGGER
jbe@115:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@115:     DECLARE
jbe@115:       "initiative_row" "initiative"%ROWTYPE;
jbe@115:       "issue_row"      "issue"%ROWTYPE;
jbe@115:       "event_v"        "event_type";
jbe@115:     BEGIN
jbe@115:       SELECT * INTO "initiative_row" FROM "initiative"
jbe@115:         WHERE "id" = NEW."initiative_id";
jbe@115:       SELECT * INTO "issue_row" FROM "issue"
jbe@115:         WHERE "id" = "initiative_row"."issue_id";
jbe@115:       IF EXISTS (
jbe@115:         SELECT NULL FROM "draft"
jbe@115:         WHERE "initiative_id" = NEW."initiative_id"
jbe@115:         AND "id" != NEW."id"
jbe@115:       ) THEN
jbe@115:         "event_v" := 'new_draft_created';
jbe@115:       ELSE
jbe@115:         IF EXISTS (
jbe@115:           SELECT NULL FROM "initiative"
jbe@115:           WHERE "issue_id" = "initiative_row"."issue_id"
jbe@115:           AND "id" != "initiative_row"."id"
jbe@115:         ) THEN
jbe@115:           "event_v" := 'initiative_created_in_existing_issue';
jbe@115:         ELSE
jbe@115:           "event_v" := 'initiative_created_in_new_issue';
jbe@115:         END IF;
jbe@115:       END IF;
jbe@115:       INSERT INTO "event" (
jbe@115:           "event", "member_id",
jbe@115:           "issue_id", "state", "initiative_id", "draft_id"
jbe@115:         ) VALUES (
jbe@115:           "event_v",
jbe@115:           NEW."author_id",
jbe@115:           "initiative_row"."issue_id",
jbe@115:           "issue_row"."state",
jbe@115:           "initiative_row"."id",
jbe@115:           NEW."id" );
jbe@115:       RETURN NULL;
jbe@115:     END;
jbe@115:   $$;
jbe@115: 
jbe@115: CREATE TRIGGER "write_event_initiative_or_draft_created"
jbe@115:   AFTER INSERT ON "draft" FOR EACH ROW EXECUTE PROCEDURE
jbe@115:   "write_event_initiative_or_draft_created_trigger"();
jbe@115: 
jbe@115: COMMENT ON FUNCTION "write_event_initiative_or_draft_created_trigger"() IS 'Implementation of trigger "write_event_initiative_or_draft_created" on table "issue"';
jbe@115: COMMENT ON TRIGGER "write_event_initiative_or_draft_created" ON "draft" IS 'Create entry in "event" table on draft creation';
jbe@115: 
jbe@115: CREATE FUNCTION "write_event_initiative_revoked_trigger"()
jbe@115:   RETURNS TRIGGER
jbe@115:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@115:     DECLARE
jbe@115:       "issue_row"      "issue"%ROWTYPE;
jbe@115:     BEGIN
jbe@115:       SELECT * INTO "issue_row" FROM "issue"
jbe@115:         WHERE "id" = NEW."issue_id";
jbe@115:       IF OLD."revoked" ISNULL AND NEW."revoked" NOTNULL THEN
jbe@115:         INSERT INTO "event" (
jbe@115:             "event", "member_id", "issue_id", "state", "initiative_id"
jbe@115:           ) VALUES (
jbe@115:             'initiative_revoked',
jbe@115:             NEW."revoked_by_member_id",
jbe@115:             NEW."issue_id",
jbe@115:             "issue_row"."state",
jbe@115:             NEW."id" );
jbe@115:       END IF;
jbe@115:       RETURN NULL;
jbe@115:     END;
jbe@115:   $$;
jbe@115: 
jbe@115: CREATE TRIGGER "write_event_initiative_revoked"
jbe@115:   AFTER UPDATE ON "initiative" FOR EACH ROW EXECUTE PROCEDURE
jbe@115:   "write_event_initiative_revoked_trigger"();
jbe@115: 
jbe@115: COMMENT ON FUNCTION "write_event_initiative_revoked_trigger"()      IS 'Implementation of trigger "write_event_initiative_revoked" on table "issue"';
jbe@115: COMMENT ON TRIGGER "write_event_initiative_revoked" ON "initiative" IS 'Create entry in "event" table, when an initiative is revoked';
jbe@115: 
jbe@115: CREATE FUNCTION "write_event_suggestion_created_trigger"()
jbe@115:   RETURNS TRIGGER
jbe@115:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@115:     DECLARE
jbe@115:       "initiative_row" "initiative"%ROWTYPE;
jbe@115:       "issue_row"      "issue"%ROWTYPE;
jbe@115:     BEGIN
jbe@115:       SELECT * INTO "initiative_row" FROM "initiative"
jbe@115:         WHERE "id" = NEW."initiative_id";
jbe@115:       SELECT * INTO "issue_row" FROM "issue"
jbe@115:         WHERE "id" = "initiative_row"."issue_id";
jbe@115:       INSERT INTO "event" (
jbe@115:           "event", "member_id",
jbe@115:           "issue_id", "state", "initiative_id", "suggestion_id"
jbe@115:         ) VALUES (
jbe@115:           'suggestion_created',
jbe@115:           NEW."author_id",
jbe@115:           "initiative_row"."issue_id",
jbe@115:           "issue_row"."state",
jbe@115:           "initiative_row"."id",
jbe@115:           NEW."id" );
jbe@115:       RETURN NULL;
jbe@115:     END;
jbe@115:   $$;
jbe@115: 
jbe@115: CREATE TRIGGER "write_event_suggestion_created"
jbe@115:   AFTER INSERT ON "suggestion" FOR EACH ROW EXECUTE PROCEDURE
jbe@115:   "write_event_suggestion_created_trigger"();
jbe@115: 
jbe@115: COMMENT ON FUNCTION "write_event_suggestion_created_trigger"()      IS 'Implementation of trigger "write_event_suggestion_created" on table "issue"';
jbe@115: COMMENT ON TRIGGER "write_event_suggestion_created" ON "suggestion" IS 'Create entry in "event" table on suggestion creation';
jbe@115: 
jbe@115: 
jbe@115: -- Modified views:
jbe@115: 
jbe@115: CREATE VIEW "unit_delegation" AS
jbe@115:   SELECT
jbe@115:     "unit"."id" AS "unit_id",
jbe@115:     "delegation"."id",
jbe@115:     "delegation"."truster_id",
jbe@115:     "delegation"."trustee_id",
jbe@115:     "delegation"."scope"
jbe@115:   FROM "unit"
jbe@115:   JOIN "delegation"
jbe@115:     ON "delegation"."unit_id" = "unit"."id"
jbe@115:   JOIN "member"
jbe@115:     ON "delegation"."truster_id" = "member"."id"
jbe@115:   JOIN "privilege"
jbe@115:     ON "delegation"."unit_id" = "privilege"."unit_id"
jbe@115:     AND "delegation"."truster_id" = "privilege"."member_id"
jbe@115:   WHERE "member"."active" AND "privilege"."voting_right";
jbe@115: 
jbe@115: COMMENT ON VIEW "unit_delegation" IS 'Unit delegations where trusters are active and have voting right';
jbe@115: 
jbe@115: CREATE VIEW "area_delegation" AS
jbe@115:   SELECT DISTINCT ON ("area"."id", "delegation"."truster_id")
jbe@115:     "area"."id" AS "area_id",
jbe@115:     "delegation"."id",
jbe@115:     "delegation"."truster_id",
jbe@115:     "delegation"."trustee_id",
jbe@115:     "delegation"."scope"
jbe@115:   FROM "area"
jbe@115:   JOIN "delegation"
jbe@115:     ON "delegation"."unit_id" = "area"."unit_id"
jbe@115:     OR "delegation"."area_id" = "area"."id"
jbe@115:   JOIN "member"
jbe@115:     ON "delegation"."truster_id" = "member"."id"
jbe@115:   JOIN "privilege"
jbe@115:     ON "area"."unit_id" = "privilege"."unit_id"
jbe@115:     AND "delegation"."truster_id" = "privilege"."member_id"
jbe@115:   WHERE "member"."active" AND "privilege"."voting_right"
jbe@115:   ORDER BY
jbe@115:     "area"."id",
jbe@115:     "delegation"."truster_id",
jbe@115:     "delegation"."scope" DESC;
jbe@115: 
jbe@115: COMMENT ON VIEW "area_delegation" IS 'Area delegations where trusters are active and have voting right';
jbe@115: 
jbe@115: CREATE VIEW "issue_delegation" AS
jbe@115:   SELECT DISTINCT ON ("issue"."id", "delegation"."truster_id")
jbe@115:     "issue"."id" AS "issue_id",
jbe@115:     "delegation"."id",
jbe@115:     "delegation"."truster_id",
jbe@115:     "delegation"."trustee_id",
jbe@115:     "delegation"."scope"
jbe@115:   FROM "issue"
jbe@115:   JOIN "area"
jbe@115:     ON "area"."id" = "issue"."area_id"
jbe@115:   JOIN "delegation"
jbe@115:     ON "delegation"."unit_id" = "area"."unit_id"
jbe@115:     OR "delegation"."area_id" = "area"."id"
jbe@115:     OR "delegation"."issue_id" = "issue"."id"
jbe@115:   JOIN "member"
jbe@115:     ON "delegation"."truster_id" = "member"."id"
jbe@115:   JOIN "privilege"
jbe@115:     ON "area"."unit_id" = "privilege"."unit_id"
jbe@115:     AND "delegation"."truster_id" = "privilege"."member_id"
jbe@115:   WHERE "member"."active" AND "privilege"."voting_right"
jbe@115:   ORDER BY
jbe@115:     "issue"."id",
jbe@115:     "delegation"."truster_id",
jbe@115:     "delegation"."scope" DESC;
jbe@115: 
jbe@115: COMMENT ON VIEW "issue_delegation" IS 'Issue delegations where trusters are active and have voting right';
jbe@115: 
jbe@115: CREATE VIEW "unit_member_count" AS
jbe@115:   SELECT
jbe@115:     "unit"."id" AS "unit_id",
jbe@115:     sum("member"."id") AS "member_count"
jbe@115:   FROM "unit"
jbe@115:   LEFT JOIN "privilege"
jbe@115:   ON "privilege"."unit_id" = "unit"."id" 
jbe@115:   AND "privilege"."voting_right"
jbe@115:   LEFT JOIN "member"
jbe@115:   ON "member"."id" = "privilege"."member_id"
jbe@115:   AND "member"."active"
jbe@115:   GROUP BY "unit"."id";
jbe@115: 
jbe@115: COMMENT ON VIEW "unit_member_count" IS 'View used to update "member_count" column of "unit" table';
jbe@115: 
jbe@115: DROP VIEW "area_member_count";
jbe@115: CREATE VIEW "area_member_count" AS
jbe@115:   SELECT
jbe@115:     "area"."id" AS "area_id",
jbe@115:     count("member"."id") AS "direct_member_count",
jbe@115:     coalesce(
jbe@115:       sum(
jbe@115:         CASE WHEN "member"."id" NOTNULL THEN
jbe@115:           "membership_weight"("area"."id", "member"."id")
jbe@115:         ELSE 0 END
jbe@115:       )
jbe@115:     ) AS "member_weight",
jbe@115:     coalesce(
jbe@115:       sum(
jbe@115:         CASE WHEN "member"."id" NOTNULL AND "membership"."autoreject" THEN
jbe@115:           "membership_weight"("area"."id", "member"."id")
jbe@115:         ELSE 0 END
jbe@115:       )
jbe@115:     ) AS "autoreject_weight"
jbe@115:   FROM "area"
jbe@115:   LEFT JOIN "membership"
jbe@115:   ON "area"."id" = "membership"."area_id"
jbe@115:   LEFT JOIN "privilege"
jbe@115:   ON "privilege"."unit_id" = "area"."unit_id"
jbe@115:   AND "privilege"."member_id" = "membership"."member_id"
jbe@115:   AND "privilege"."voting_right"
jbe@115:   LEFT JOIN "member"
jbe@115:   ON "member"."id" = "privilege"."member_id"  -- NOTE: no membership here!
jbe@115:   AND "member"."active"
jbe@115:   GROUP BY "area"."id";
jbe@115: 
jbe@115: COMMENT ON VIEW "area_member_count" IS 'View used to update "direct_member_count", "member_weight" and "autoreject_weight" columns of table "area"';
jbe@115: 
jbe@115: 
jbe@115: -- New view "event_seen_by_member":
jbe@115: 
jbe@115: CREATE VIEW "event_seen_by_member" AS
jbe@115:   SELECT
jbe@115:     "member"."id" AS "seen_by_member_id",
jbe@115:     CASE WHEN "event"."state" IN (
jbe@115:       'voting',
jbe@115:       'finished_without_winner',
jbe@115:       'finished_with_winner'
jbe@115:     ) THEN
jbe@115:       'voting'::"notify_level"
jbe@115:     ELSE
jbe@115:       CASE WHEN "event"."state" IN (
jbe@115:         'verification',
jbe@115:         'canceled_after_revocation_during_verification',
jbe@115:         'canceled_no_initiative_admitted'
jbe@115:       ) THEN
jbe@115:         'verification'::"notify_level"
jbe@115:       ELSE
jbe@115:         CASE WHEN "event"."state" IN (
jbe@115:           'discussion',
jbe@115:           'canceled_after_revocation_during_discussion'
jbe@115:         ) THEN
jbe@115:           'discussion'::"notify_level"
jbe@115:         ELSE
jbe@115:           'all'::"notify_level"
jbe@115:         END
jbe@115:       END
jbe@115:     END AS "notify_level",
jbe@115:     "event".*
jbe@115:   FROM "member" CROSS JOIN "event"
jbe@115:   LEFT JOIN "issue"
jbe@115:     ON "event"."issue_id" = "issue"."id"
jbe@115:   LEFT JOIN "membership"
jbe@115:     ON "member"."id" = "membership"."member_id"
jbe@115:     AND "issue"."area_id" = "membership"."area_id"
jbe@115:   LEFT JOIN "interest"
jbe@115:     ON "member"."id" = "interest"."member_id"
jbe@115:     AND "event"."issue_id" = "interest"."issue_id"
jbe@115:   LEFT JOIN "supporter"
jbe@115:     ON "member"."id" = "supporter"."member_id"
jbe@115:     AND "event"."initiative_id" = "supporter"."initiative_id"
jbe@115:   LEFT JOIN "ignored_member"
jbe@115:     ON "member"."id" = "ignored_member"."member_id"
jbe@115:     AND "event"."member_id" = "ignored_member"."other_member_id"
jbe@115:   LEFT JOIN "ignored_initiative"
jbe@115:     ON "member"."id" = "ignored_initiative"."member_id"
jbe@115:     AND "event"."initiative_id" = "ignored_initiative"."initiative_id"
jbe@115:   WHERE (
jbe@115:     "supporter"."member_id" NOTNULL OR
jbe@115:     "interest"."member_id" NOTNULL OR
jbe@115:     ( "membership"."member_id" NOTNULL AND
jbe@115:       "event"."event" IN (
jbe@115:         'issue_state_changed',
jbe@115:         'initiative_created_in_new_issue',
jbe@115:         'initiative_created_in_existing_issue',
jbe@115:         'initiative_revoked' ) ) )
jbe@115:   AND "ignored_member"."member_id" ISNULL
jbe@115:   AND "ignored_initiative"."member_id" ISNULL;
jbe@115: 
jbe@115: COMMENT ON VIEW "event_seen_by_member" IS 'Events as seen by a member, depending on its memberships, interests and support';
jbe@115: 
jbe@115: 
jbe@115: -- New view "pending_notification":
jbe@115: 
jbe@115: CREATE VIEW "pending_notification" AS
jbe@115:   SELECT
jbe@115:     "member"."id" AS "seen_by_member_id",
jbe@115:     "event".*
jbe@115:   FROM "member" CROSS JOIN "event"
jbe@115:   LEFT JOIN "issue"
jbe@115:     ON "event"."issue_id" = "issue"."id"
jbe@115:   LEFT JOIN "membership"
jbe@115:     ON "member"."id" = "membership"."member_id"
jbe@115:     AND "issue"."area_id" = "membership"."area_id"
jbe@115:   LEFT JOIN "interest"
jbe@115:     ON "member"."id" = "interest"."member_id"
jbe@115:     AND "event"."issue_id" = "interest"."issue_id"
jbe@115:   LEFT JOIN "supporter"
jbe@115:     ON "member"."id" = "supporter"."member_id"
jbe@115:     AND "event"."initiative_id" = "supporter"."initiative_id"
jbe@115:   LEFT JOIN "ignored_member"
jbe@115:     ON "member"."id" = "ignored_member"."member_id"
jbe@115:     AND "event"."member_id" = "ignored_member"."other_member_id"
jbe@115:   LEFT JOIN "ignored_initiative"
jbe@115:     ON "member"."id" = "ignored_initiative"."member_id"
jbe@115:     AND "event"."initiative_id" = "ignored_initiative"."initiative_id"
jbe@115:   WHERE (
jbe@115:     "member"."notify_event_id" ISNULL OR
jbe@115:     ( "member"."notify_event_id" NOTNULL AND
jbe@115:       "member"."notify_event_id" < "event"."id" ) )
jbe@115:   AND (
jbe@115:     ( "member"."notify_level" >= 'all' ) OR
jbe@115:     ( "member"."notify_level" >= 'voting' AND
jbe@115:       "event"."state" IN (
jbe@115:         'voting',
jbe@115:         'finished_without_winner',
jbe@115:         'finished_with_winner' ) ) OR
jbe@115:     ( "member"."notify_level" >= 'verification' AND
jbe@115:       "event"."state" IN (
jbe@115:         'verification',
jbe@115:         'canceled_after_revocation_during_verification',
jbe@115:         'canceled_no_initiative_admitted' ) ) OR
jbe@115:     ( "member"."notify_level" >= 'discussion' AND
jbe@115:       "event"."state" IN (
jbe@115:         'discussion',
jbe@115:         'canceled_after_revocation_during_discussion' ) ) )
jbe@115:   AND (
jbe@115:     "supporter"."member_id" NOTNULL OR
jbe@115:     "interest"."member_id" NOTNULL OR
jbe@115:     ( "membership"."member_id" NOTNULL AND
jbe@115:       "event"."event" IN (
jbe@115:         'issue_state_changed',
jbe@115:         'initiative_created_in_new_issue',
jbe@115:         'initiative_created_in_existing_issue',
jbe@115:         'initiative_revoked' ) ) )
jbe@115:   AND "ignored_member"."member_id" ISNULL
jbe@115:   AND "ignored_initiative"."member_id" ISNULL;
jbe@115: 
jbe@115: COMMENT ON VIEW "pending_notification" IS 'Events to be sent to "notify_email" address of member referred to by "seen_by_member_id"';
jbe@115: 
jbe@115: 
jbe@115: COMMENT ON TYPE "timeline_event" IS 'Types of event in timeline tables (DEPRECATED)';
jbe@115: COMMENT ON VIEW "timeline_issue" IS 'Helper view for "timeline" view (DEPRECATED)';
jbe@115: COMMENT ON VIEW "timeline_initiative" IS 'Helper view for "timeline" view (DEPRECATED)';
jbe@115: COMMENT ON VIEW "timeline_draft" IS 'Helper view for "timeline" view (DEPRECATED)';
jbe@115: COMMENT ON VIEW "timeline_suggestion" IS 'Helper view for "timeline" view (DEPRECATED)';
jbe@115: COMMENT ON VIEW "timeline" IS 'Aggregation of different events in the system (DEPRECATED)';
jbe@115: 
jbe@115: 
jbe@115: -- Modified "delegation_chain" functions:
jbe@115: 
jbe@115: CREATE TYPE "delegation_chain_row" AS (
jbe@115:         "index"                 INT4,
jbe@115:         "member_id"             INT4,
jbe@115:         "member_valid"          BOOLEAN,
jbe@115:         "participation"         BOOLEAN,
jbe@115:         "overridden"            BOOLEAN,
jbe@115:         "scope_in"              "delegation_scope",
jbe@115:         "scope_out"             "delegation_scope",
jbe@115:         "disabled_out"          BOOLEAN,
jbe@115:         "loop"                  "delegation_chain_loop_tag" );
jbe@115: 
jbe@115: COMMENT ON TYPE "delegation_chain_row" IS 'Type of rows returned by "delegation_chain"(...) functions';
jbe@115: 
jbe@115: COMMENT ON COLUMN "delegation_chain_row"."index"         IS 'Index starting with 0 and counting up';
jbe@115: 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@115: COMMENT ON COLUMN "delegation_chain_row"."overridden"    IS 'True, if an entry with lower index has "participation" set to true';
jbe@115: COMMENT ON COLUMN "delegation_chain_row"."scope_in"      IS 'Scope of used incoming delegation';
jbe@115: COMMENT ON COLUMN "delegation_chain_row"."scope_out"     IS 'Scope of used outgoing delegation';
jbe@115: COMMENT ON COLUMN "delegation_chain_row"."disabled_out"  IS 'Outgoing delegation is explicitly disabled by a delegation with trustee_id set to NULL';
jbe@115: COMMENT ON COLUMN "delegation_chain_row"."loop"          IS 'Not null, if member is part of a loop, see "delegation_chain_loop_tag" type';
jbe@115: 
jbe@115: 
jbe@115: CREATE FUNCTION "delegation_chain"
jbe@115:   ( "member_id_p"           "member"."id"%TYPE,
jbe@115:     "unit_id_p"             "unit"."id"%TYPE,
jbe@115:     "area_id_p"             "area"."id"%TYPE,
jbe@115:     "issue_id_p"            "issue"."id"%TYPE,
jbe@115:     "simulate_trustee_id_p" "member"."id"%TYPE )
jbe@115:   RETURNS SETOF "delegation_chain_row"
jbe@115:   LANGUAGE 'plpgsql' STABLE AS $$
jbe@115:     DECLARE
jbe@115:       "scope_v"            "delegation_scope";
jbe@115:       "unit_id_v"          "unit"."id"%TYPE;
jbe@115:       "area_id_v"          "area"."id"%TYPE;
jbe@115:       "visited_member_ids" INT4[];  -- "member"."id"%TYPE[]
jbe@115:       "loop_member_id_v"   "member"."id"%TYPE;
jbe@115:       "output_row"         "delegation_chain_row";
jbe@115:       "output_rows"        "delegation_chain_row"[];
jbe@115:       "delegation_row"     "delegation"%ROWTYPE;
jbe@115:       "row_count"          INT4;
jbe@115:       "i"                  INT4;
jbe@115:       "loop_v"             BOOLEAN;
jbe@115:     BEGIN
jbe@115:       IF
jbe@115:         "unit_id_p" NOTNULL AND
jbe@115:         "area_id_p" ISNULL AND
jbe@115:         "issue_id_p" ISNULL
jbe@115:       THEN
jbe@115:         "scope_v" := 'unit';
jbe@115:         "unit_id_v" := "unit_id_p";
jbe@115:       ELSIF
jbe@115:         "unit_id_p" ISNULL AND
jbe@115:         "area_id_p" NOTNULL AND
jbe@115:         "issue_id_p" ISNULL
jbe@115:       THEN
jbe@115:         "scope_v" := 'area';
jbe@115:         "area_id_v" := "area_id_p";
jbe@115:         SELECT "unit_id" INTO "unit_id_v"
jbe@115:           FROM "area" WHERE "id" = "area_id_v";
jbe@115:       ELSIF
jbe@115:         "unit_id_p" ISNULL AND
jbe@115:         "area_id_p" ISNULL AND
jbe@115:         "issue_id_p" NOTNULL
jbe@115:       THEN
jbe@115:         "scope_v" := 'issue';
jbe@115:         SELECT "area_id" INTO "area_id_v"
jbe@115:           FROM "issue" WHERE "id" = "issue_id_p";
jbe@115:         SELECT "unit_id" INTO "unit_id_v"
jbe@115:           FROM "area"  WHERE "id" = "area_id_v";
jbe@115:       ELSE
jbe@115:         RAISE EXCEPTION 'Exactly one of unit_id_p, area_id_p, or issue_id_p must be NOTNULL.';
jbe@115:       END IF;
jbe@115:       "visited_member_ids" := '{}';
jbe@115:       "loop_member_id_v"   := NULL;
jbe@115:       "output_rows"        := '{}';
jbe@115:       "output_row"."index"         := 0;
jbe@115:       "output_row"."member_id"     := "member_id_p";
jbe@115:       "output_row"."member_valid"  := TRUE;
jbe@115:       "output_row"."participation" := FALSE;
jbe@115:       "output_row"."overridden"    := FALSE;
jbe@115:       "output_row"."disabled_out"  := FALSE;
jbe@115:       "output_row"."scope_out"     := NULL;
jbe@115:       LOOP
jbe@115:         IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN
jbe@115:           "loop_member_id_v" := "output_row"."member_id";
jbe@115:         ELSE
jbe@115:           "visited_member_ids" :=
jbe@115:             "visited_member_ids" || "output_row"."member_id";
jbe@115:         END IF;
jbe@115:         IF "output_row"."participation" THEN
jbe@115:           "output_row"."overridden" := TRUE;
jbe@115:         END IF;
jbe@115:         "output_row"."scope_in" := "output_row"."scope_out";
jbe@115:         IF EXISTS (
jbe@115:           SELECT NULL FROM "member" JOIN "privilege"
jbe@115:           ON "privilege"."member_id" = "member"."id"
jbe@115:           AND "privilege"."unit_id" = "unit_id_v"
jbe@115:           WHERE "id" = "output_row"."member_id"
jbe@115:           AND "member"."active" AND "privilege"."voting_right"
jbe@115:         ) THEN
jbe@115:           IF "scope_v" = 'unit' THEN
jbe@115:             SELECT * INTO "delegation_row" FROM "delegation"
jbe@115:               WHERE "truster_id" = "output_row"."member_id"
jbe@115:               AND "unit_id" = "unit_id_v";
jbe@115:           ELSIF "scope_v" = 'area' THEN
jbe@115:             "output_row"."participation" := EXISTS (
jbe@115:               SELECT NULL FROM "membership"
jbe@115:               WHERE "area_id" = "area_id_p"
jbe@115:               AND "member_id" = "output_row"."member_id"
jbe@115:             );
jbe@115:             SELECT * INTO "delegation_row" FROM "delegation"
jbe@115:               WHERE "truster_id" = "output_row"."member_id"
jbe@115:               AND (
jbe@115:                 "unit_id" = "unit_id_v" OR
jbe@115:                 "area_id" = "area_id_v"
jbe@115:               )
jbe@115:               ORDER BY "scope" DESC;
jbe@115:           ELSIF "scope_v" = 'issue' THEN
jbe@115:             "output_row"."participation" := EXISTS (
jbe@115:               SELECT NULL FROM "interest"
jbe@115:               WHERE "issue_id" = "issue_id_p"
jbe@115:               AND "member_id" = "output_row"."member_id"
jbe@115:             );
jbe@115:             SELECT * INTO "delegation_row" FROM "delegation"
jbe@115:               WHERE "truster_id" = "output_row"."member_id"
jbe@115:               AND (
jbe@115:                 "unit_id" = "unit_id_v" OR
jbe@115:                 "area_id" = "area_id_v" OR
jbe@115:                 "issue_id" = "issue_id_p"
jbe@115:               )
jbe@115:               ORDER BY "scope" DESC;
jbe@115:           END IF;
jbe@115:         ELSE
jbe@115:           "output_row"."member_valid"  := FALSE;
jbe@115:           "output_row"."participation" := FALSE;
jbe@115:           "output_row"."scope_out"     := NULL;
jbe@115:           "delegation_row" := ROW(NULL);
jbe@115:         END IF;
jbe@115:         IF
jbe@115:           "output_row"."member_id" = "member_id_p" AND
jbe@115:           "simulate_trustee_id_p" NOTNULL
jbe@115:         THEN
jbe@115:           "output_row"."scope_out" := "scope_v";
jbe@115:           "output_rows" := "output_rows" || "output_row";
jbe@115:           "output_row"."member_id" := "simulate_trustee_id_p";
jbe@115:         ELSIF "delegation_row"."trustee_id" NOTNULL THEN
jbe@115:           "output_row"."scope_out" := "delegation_row"."scope";
jbe@115:           "output_rows" := "output_rows" || "output_row";
jbe@115:           "output_row"."member_id" := "delegation_row"."trustee_id";
jbe@115:         ELSIF "delegation_row"."scope" NOTNULL THEN
jbe@115:           "output_row"."scope_out" := "delegation_row"."scope";
jbe@115:           "output_row"."disabled_out" := TRUE;
jbe@115:           "output_rows" := "output_rows" || "output_row";
jbe@115:           EXIT;
jbe@115:         ELSE
jbe@115:           "output_row"."scope_out" := NULL;
jbe@115:           "output_rows" := "output_rows" || "output_row";
jbe@115:           EXIT;
jbe@115:         END IF;
jbe@115:         EXIT WHEN "loop_member_id_v" NOTNULL;
jbe@115:         "output_row"."index" := "output_row"."index" + 1;
jbe@115:       END LOOP;
jbe@115:       "row_count" := array_upper("output_rows", 1);
jbe@115:       "i"      := 1;
jbe@115:       "loop_v" := FALSE;
jbe@115:       LOOP
jbe@115:         "output_row" := "output_rows"["i"];
jbe@115:         EXIT WHEN "output_row" ISNULL;  -- NOTE: ISNULL and NOT ... NOTNULL produce different results!
jbe@115:         IF "loop_v" THEN
jbe@115:           IF "i" + 1 = "row_count" THEN
jbe@115:             "output_row"."loop" := 'last';
jbe@115:           ELSIF "i" = "row_count" THEN
jbe@115:             "output_row"."loop" := 'repetition';
jbe@115:           ELSE
jbe@115:             "output_row"."loop" := 'intermediate';
jbe@115:           END IF;
jbe@115:         ELSIF "output_row"."member_id" = "loop_member_id_v" THEN
jbe@115:           "output_row"."loop" := 'first';
jbe@115:           "loop_v" := TRUE;
jbe@115:         END IF;
jbe@115:         IF "scope_v" = 'unit' THEN
jbe@115:           "output_row"."participation" := NULL;
jbe@115:         END IF;
jbe@115:         RETURN NEXT "output_row";
jbe@115:         "i" := "i" + 1;
jbe@115:       END LOOP;
jbe@115:       RETURN;
jbe@115:     END;
jbe@115:   $$;
jbe@115: 
jbe@115: COMMENT ON FUNCTION "delegation_chain"
jbe@115:   ( "member"."id"%TYPE,
jbe@115:     "unit"."id"%TYPE,
jbe@115:     "area"."id"%TYPE,
jbe@115:     "issue"."id"%TYPE,
jbe@115:     "member"."id"%TYPE )
jbe@115:   IS 'Helper function for frontends to display delegation chains; Not part of internal voting logic';
jbe@115: 
jbe@115: 
jbe@115: CREATE FUNCTION "delegation_chain"
jbe@115:   ( "member_id_p" "member"."id"%TYPE,
jbe@115:     "unit_id_p"   "unit"."id"%TYPE,
jbe@115:     "area_id_p"   "area"."id"%TYPE,
jbe@115:     "issue_id_p"  "issue"."id"%TYPE )
jbe@115:   RETURNS SETOF "delegation_chain_row"
jbe@115:   LANGUAGE 'plpgsql' STABLE AS $$
jbe@115:     DECLARE
jbe@115:       "result_row" "delegation_chain_row";
jbe@115:     BEGIN
jbe@115:       FOR "result_row" IN
jbe@115:         SELECT * FROM "delegation_chain"(
jbe@123:           "member_id_p", "unit_id_p", "area_id_p", "issue_id_p", NULL
jbe@115:         )
jbe@115:       LOOP
jbe@115:         RETURN NEXT "result_row";
jbe@115:       END LOOP;
jbe@115:       RETURN;
jbe@115:     END;
jbe@115:   $$;
jbe@115: 
jbe@115: COMMENT ON FUNCTION "delegation_chain"
jbe@115:   ( "member"."id"%TYPE,
jbe@115:     "unit"."id"%TYPE,
jbe@115:     "area"."id"%TYPE,
jbe@115:     "issue"."id"%TYPE )
jbe@115:   IS 'Shortcut for "delegation_chain"(...) function where 4th parameter is null';
jbe@115: 
jbe@115: 
jbe@122: -- Other modified functions:
jbe@115: 
jbe@115: CREATE OR REPLACE FUNCTION "lock_issue"
jbe@115:   ( "issue_id_p" "issue"."id"%TYPE )
jbe@115:   RETURNS VOID
jbe@115:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@115:     BEGIN
jbe@115:       LOCK TABLE "member"     IN SHARE MODE;
jbe@115:       LOCK TABLE "privilege"  IN SHARE MODE;
jbe@115:       LOCK TABLE "membership" IN SHARE MODE;
jbe@115:       LOCK TABLE "policy"     IN SHARE MODE;
jbe@115:       PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE;
jbe@115:       -- NOTE: The row-level exclusive lock in combination with the
jbe@115:       -- share_row_lock_issue(_via_initiative)_trigger functions (which
jbe@115:       -- acquire a row-level share lock on the issue) ensure that no data
jbe@115:       -- is changed, which could affect calculation of snapshots or
jbe@115:       -- counting of votes. Table "delegation" must be table-level-locked,
jbe@115:       -- as it also contains issue- and global-scope delegations.
jbe@115:       LOCK TABLE "delegation" IN SHARE MODE;
jbe@115:       LOCK TABLE "direct_population_snapshot"     IN EXCLUSIVE MODE;
jbe@115:       LOCK TABLE "delegating_population_snapshot" IN EXCLUSIVE MODE;
jbe@115:       LOCK TABLE "direct_interest_snapshot"       IN EXCLUSIVE MODE;
jbe@115:       LOCK TABLE "delegating_interest_snapshot"   IN EXCLUSIVE MODE;
jbe@115:       LOCK TABLE "direct_supporter_snapshot"      IN EXCLUSIVE MODE;
jbe@115:       RETURN;
jbe@115:     END;
jbe@115:   $$;
jbe@115: 
jbe@115: CREATE OR REPLACE FUNCTION "calculate_member_counts"()
jbe@115:   RETURNS VOID
jbe@115:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@115:     BEGIN
jbe@115:       LOCK TABLE "member"       IN SHARE MODE;
jbe@115:       LOCK TABLE "member_count" IN EXCLUSIVE MODE;
jbe@115:       LOCK TABLE "unit"         IN EXCLUSIVE MODE;
jbe@115:       LOCK TABLE "area"         IN EXCLUSIVE MODE;
jbe@115:       LOCK TABLE "privilege"    IN SHARE MODE;
jbe@115:       LOCK TABLE "membership"   IN SHARE MODE;
jbe@115:       DELETE FROM "member_count";
jbe@115:       INSERT INTO "member_count" ("total_count")
jbe@115:         SELECT "total_count" FROM "member_count_view";
jbe@115:       UPDATE "unit" SET "member_count" = "view"."member_count"
jbe@115:         FROM "unit_member_count" AS "view"
jbe@115:         WHERE "view"."unit_id" = "unit"."id";
jbe@115:       UPDATE "area" SET
jbe@115:         "direct_member_count" = "view"."direct_member_count",
jbe@115:         "member_weight"       = "view"."member_weight",
jbe@115:         "autoreject_weight"   = "view"."autoreject_weight"
jbe@115:         FROM "area_member_count" AS "view"
jbe@115:         WHERE "view"."area_id" = "area"."id";
jbe@115:       RETURN;
jbe@115:     END;
jbe@115:   $$;
jbe@115: 
jbe@115: CREATE OR REPLACE FUNCTION "create_population_snapshot"
jbe@115:   ( "issue_id_p" "issue"."id"%TYPE )
jbe@115:   RETURNS VOID
jbe@115:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@115:     DECLARE
jbe@115:       "member_id_v" "member"."id"%TYPE;
jbe@115:     BEGIN
jbe@115:       DELETE FROM "direct_population_snapshot"
jbe@115:         WHERE "issue_id" = "issue_id_p"
jbe@115:         AND "event" = 'periodic';
jbe@115:       DELETE FROM "delegating_population_snapshot"
jbe@115:         WHERE "issue_id" = "issue_id_p"
jbe@115:         AND "event" = 'periodic';
jbe@115:       INSERT INTO "direct_population_snapshot"
jbe@115:         ("issue_id", "event", "member_id")
jbe@115:         SELECT
jbe@115:           "issue_id_p"                 AS "issue_id",
jbe@115:           'periodic'::"snapshot_event" AS "event",
jbe@115:           "member"."id"                AS "member_id"
jbe@115:         FROM "issue"
jbe@115:         JOIN "area" ON "issue"."area_id" = "area"."id"
jbe@115:         JOIN "membership" ON "area"."id" = "membership"."area_id"
jbe@115:         JOIN "member" ON "membership"."member_id" = "member"."id"
jbe@115:         JOIN "privilege"
jbe@115:           ON "privilege"."unit_id" = "area"."unit_id"
jbe@115:           AND "privilege"."member_id" = "member"."id"
jbe@115:         WHERE "issue"."id" = "issue_id_p"
jbe@115:         AND "member"."active" AND "privilege"."voting_right"
jbe@115:         UNION
jbe@115:         SELECT
jbe@115:           "issue_id_p"                 AS "issue_id",
jbe@115:           'periodic'::"snapshot_event" AS "event",
jbe@115:           "member"."id"                AS "member_id"
jbe@115:         FROM "issue"
jbe@115:         JOIN "area" ON "issue"."area_id" = "area"."id"
jbe@115:         JOIN "interest" ON "issue"."id" = "interest"."issue_id"
jbe@115:         JOIN "member" ON "interest"."member_id" = "member"."id"
jbe@115:         JOIN "privilege"
jbe@115:           ON "privilege"."unit_id" = "area"."unit_id"
jbe@115:           AND "privilege"."member_id" = "member"."id"
jbe@115:         WHERE "issue"."id" = "issue_id_p"
jbe@115:         AND "member"."active" AND "privilege"."voting_right";
jbe@115:       FOR "member_id_v" IN
jbe@115:         SELECT "member_id" FROM "direct_population_snapshot"
jbe@115:         WHERE "issue_id" = "issue_id_p"
jbe@115:         AND "event" = 'periodic'
jbe@115:       LOOP
jbe@115:         UPDATE "direct_population_snapshot" SET
jbe@115:           "weight" = 1 +
jbe@115:             "weight_of_added_delegations_for_population_snapshot"(
jbe@115:               "issue_id_p",
jbe@115:               "member_id_v",
jbe@115:               '{}'
jbe@115:             )
jbe@115:           WHERE "issue_id" = "issue_id_p"
jbe@115:           AND "event" = 'periodic'
jbe@115:           AND "member_id" = "member_id_v";
jbe@115:       END LOOP;
jbe@115:       RETURN;
jbe@115:     END;
jbe@115:   $$;
jbe@115: 
jbe@115: CREATE OR REPLACE FUNCTION "create_interest_snapshot"
jbe@115:   ( "issue_id_p" "issue"."id"%TYPE )
jbe@115:   RETURNS VOID
jbe@115:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@115:     DECLARE
jbe@115:       "member_id_v" "member"."id"%TYPE;
jbe@115:     BEGIN
jbe@115:       DELETE FROM "direct_interest_snapshot"
jbe@115:         WHERE "issue_id" = "issue_id_p"
jbe@115:         AND "event" = 'periodic';
jbe@115:       DELETE FROM "delegating_interest_snapshot"
jbe@115:         WHERE "issue_id" = "issue_id_p"
jbe@115:         AND "event" = 'periodic';
jbe@115:       DELETE FROM "direct_supporter_snapshot"
jbe@115:         WHERE "issue_id" = "issue_id_p"
jbe@115:         AND "event" = 'periodic';
jbe@115:       INSERT INTO "direct_interest_snapshot"
jbe@115:         ("issue_id", "event", "member_id", "voting_requested")
jbe@115:         SELECT
jbe@115:           "issue_id_p"  AS "issue_id",
jbe@115:           'periodic'    AS "event",
jbe@115:           "member"."id" AS "member_id",
jbe@115:           "interest"."voting_requested"
jbe@115:         FROM "issue"
jbe@115:         JOIN "area" ON "issue"."area_id" = "area"."id"
jbe@115:         JOIN "interest" ON "issue"."id" = "interest"."issue_id"
jbe@115:         JOIN "member" ON "interest"."member_id" = "member"."id"
jbe@115:         JOIN "privilege"
jbe@115:           ON "privilege"."unit_id" = "area"."unit_id"
jbe@115:           AND "privilege"."member_id" = "member"."id"
jbe@115:         WHERE "issue"."id" = "issue_id_p"
jbe@115:         AND "member"."active" AND "privilege"."voting_right";
jbe@115:       FOR "member_id_v" IN
jbe@115:         SELECT "member_id" FROM "direct_interest_snapshot"
jbe@115:         WHERE "issue_id" = "issue_id_p"
jbe@115:         AND "event" = 'periodic'
jbe@115:       LOOP
jbe@115:         UPDATE "direct_interest_snapshot" SET
jbe@115:           "weight" = 1 +
jbe@115:             "weight_of_added_delegations_for_interest_snapshot"(
jbe@115:               "issue_id_p",
jbe@115:               "member_id_v",
jbe@115:               '{}'
jbe@115:             )
jbe@115:           WHERE "issue_id" = "issue_id_p"
jbe@115:           AND "event" = 'periodic'
jbe@115:           AND "member_id" = "member_id_v";
jbe@115:       END LOOP;
jbe@115:       INSERT INTO "direct_supporter_snapshot"
jbe@115:         ( "issue_id", "initiative_id", "event", "member_id",
jbe@115:           "informed", "satisfied" )
jbe@115:         SELECT
jbe@115:           "issue_id_p"            AS "issue_id",
jbe@115:           "initiative"."id"       AS "initiative_id",
jbe@115:           'periodic'              AS "event",
jbe@115:           "supporter"."member_id" AS "member_id",
jbe@115:           "supporter"."draft_id" = "current_draft"."id" AS "informed",
jbe@115:           NOT EXISTS (
jbe@115:             SELECT NULL FROM "critical_opinion"
jbe@115:             WHERE "initiative_id" = "initiative"."id"
jbe@115:             AND "member_id" = "supporter"."member_id"
jbe@115:           ) AS "satisfied"
jbe@115:         FROM "initiative"
jbe@115:         JOIN "supporter"
jbe@115:         ON "supporter"."initiative_id" = "initiative"."id"
jbe@115:         JOIN "current_draft"
jbe@115:         ON "initiative"."id" = "current_draft"."initiative_id"
jbe@115:         JOIN "direct_interest_snapshot"
jbe@115:         ON "supporter"."member_id" = "direct_interest_snapshot"."member_id"
jbe@115:         AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"
jbe@115:         AND "event" = 'periodic'
jbe@115:         WHERE "initiative"."issue_id" = "issue_id_p";
jbe@115:       RETURN;
jbe@115:     END;
jbe@115:   $$;
jbe@115: 
jbe@115: CREATE OR REPLACE FUNCTION "freeze_after_snapshot"
jbe@115:   ( "issue_id_p" "issue"."id"%TYPE )
jbe@115:   RETURNS VOID
jbe@115:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@115:     DECLARE
jbe@115:       "issue_row"      "issue"%ROWTYPE;
jbe@115:       "policy_row"     "policy"%ROWTYPE;
jbe@115:       "initiative_row" "initiative"%ROWTYPE;
jbe@115:     BEGIN
jbe@115:       SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
jbe@115:       SELECT * INTO "policy_row"
jbe@115:         FROM "policy" WHERE "id" = "issue_row"."policy_id";
jbe@115:       PERFORM "set_snapshot_event"("issue_id_p", 'full_freeze');
jbe@115:       FOR "initiative_row" IN
jbe@115:         SELECT * FROM "initiative"
jbe@115:         WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
jbe@115:       LOOP
jbe@115:         IF
jbe@115:           "initiative_row"."satisfied_supporter_count" > 0 AND
jbe@115:           "initiative_row"."satisfied_supporter_count" *
jbe@115:           "policy_row"."initiative_quorum_den" >=
jbe@115:           "issue_row"."population" * "policy_row"."initiative_quorum_num"
jbe@115:         THEN
jbe@115:           UPDATE "initiative" SET "admitted" = TRUE
jbe@115:             WHERE "id" = "initiative_row"."id";
jbe@115:         ELSE
jbe@115:           UPDATE "initiative" SET "admitted" = FALSE
jbe@115:             WHERE "id" = "initiative_row"."id";
jbe@115:         END IF;
jbe@115:       END LOOP;
jbe@115:       IF EXISTS (
jbe@115:         SELECT NULL FROM "initiative"
jbe@115:         WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE
jbe@115:       ) THEN
jbe@115:         UPDATE "issue" SET
jbe@115:           "state"        = 'voting',
jbe@115:           "accepted"     = coalesce("accepted", now()),
jbe@115:           "half_frozen"  = coalesce("half_frozen", now()),
jbe@115:           "fully_frozen" = now()
jbe@115:           WHERE "id" = "issue_id_p";
jbe@115:       ELSE
jbe@115:         UPDATE "issue" SET
jbe@121:           "state"           = 'canceled_no_initiative_admitted',
jbe@121:           "accepted"        = coalesce("accepted", now()),
jbe@121:           "half_frozen"     = coalesce("half_frozen", now()),
jbe@121:           "fully_frozen"    = now(),
jbe@121:           "closed"          = now(),
jbe@121:           "ranks_available" = TRUE
jbe@115:           WHERE "id" = "issue_id_p";
jbe@115:         -- NOTE: The following DELETE statements have effect only when
jbe@115:         --       issue state has been manipulated
jbe@115:         DELETE FROM "direct_voter"     WHERE "issue_id" = "issue_id_p";
jbe@115:         DELETE FROM "delegating_voter" WHERE "issue_id" = "issue_id_p";
jbe@115:         DELETE FROM "battle"           WHERE "issue_id" = "issue_id_p";
jbe@115:       END IF;
jbe@115:       RETURN;
jbe@115:     END;
jbe@115:   $$;
jbe@115: 
jbe@115: CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
jbe@115:   RETURNS VOID
jbe@115:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@115:     DECLARE
jbe@115:       "area_id_v"   "area"."id"%TYPE;
jbe@115:       "unit_id_v"   "unit"."id"%TYPE;
jbe@115:       "member_id_v" "member"."id"%TYPE;
jbe@115:     BEGIN
jbe@115:       PERFORM "lock_issue"("issue_id_p");
jbe@115:       SELECT "id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";
jbe@115:       SELECT "id" INTO "unit_id_v" FROM "area"  WHERE "id" = "area_id_v";
jbe@115:       DELETE FROM "delegating_voter"
jbe@115:         WHERE "issue_id" = "issue_id_p";
jbe@115:       DELETE FROM "direct_voter"
jbe@115:         WHERE "issue_id" = "issue_id_p"
jbe@115:         AND "autoreject" = TRUE;
jbe@115:       DELETE FROM "direct_voter"
jbe@115:         USING (
jbe@115:           SELECT
jbe@115:             "direct_voter"."member_id"
jbe@115:           FROM "direct_voter"
jbe@115:           JOIN "member" ON "direct_voter"."member_id" = "member"."id"
jbe@115:           LEFT JOIN "privilege"
jbe@115:           ON "privilege"."unit_id" = "unit_id_v"
jbe@115:           AND "privilege"."member_id" = "direct_voter"."member_id"
jbe@115:           WHERE "direct_voter"."issue_id" = "issue_id_p" AND (
jbe@115:             "member"."active" = FALSE OR
jbe@115:             "privilege"."voting_right" ISNULL OR
jbe@115:             "privilege"."voting_right" = FALSE
jbe@115:           )
jbe@115:         ) AS "subquery"
jbe@115:         WHERE "direct_voter"."issue_id" = "issue_id_p"
jbe@115:         AND "direct_voter"."member_id" = "subquery"."member_id";
jbe@115:       UPDATE "direct_voter" SET "weight" = 1
jbe@115:         WHERE "issue_id" = "issue_id_p";
jbe@115:       PERFORM "add_vote_delegations"("issue_id_p");
jbe@115:       FOR "member_id_v" IN
jbe@115:         SELECT "interest"."member_id"
jbe@115:           FROM "interest"
jbe@115:           JOIN "member"
jbe@115:             ON "interest"."member_id" = "member"."id"
jbe@115:           LEFT JOIN "direct_voter"
jbe@115:             ON "interest"."member_id" = "direct_voter"."member_id"
jbe@115:             AND "interest"."issue_id" = "direct_voter"."issue_id"
jbe@115:           LEFT JOIN "delegating_voter"
jbe@115:             ON "interest"."member_id" = "delegating_voter"."member_id"
jbe@115:             AND "interest"."issue_id" = "delegating_voter"."issue_id"
jbe@115:           WHERE "interest"."issue_id" = "issue_id_p"
jbe@115:           AND "interest"."autoreject" = TRUE
jbe@115:           AND "member"."active"
jbe@115:           AND "direct_voter"."member_id" ISNULL
jbe@115:           AND "delegating_voter"."member_id" ISNULL
jbe@115:         UNION SELECT "membership"."member_id"
jbe@115:           FROM "membership"
jbe@115:           JOIN "member"
jbe@115:             ON "membership"."member_id" = "member"."id"
jbe@115:           LEFT JOIN "interest"
jbe@115:             ON "membership"."member_id" = "interest"."member_id"
jbe@115:             AND "interest"."issue_id" = "issue_id_p"
jbe@115:           LEFT JOIN "direct_voter"
jbe@115:             ON "membership"."member_id" = "direct_voter"."member_id"
jbe@115:             AND "direct_voter"."issue_id" = "issue_id_p"
jbe@115:           LEFT JOIN "delegating_voter"
jbe@115:             ON "membership"."member_id" = "delegating_voter"."member_id"
jbe@115:             AND "delegating_voter"."issue_id" = "issue_id_p"
jbe@115:           WHERE "membership"."area_id" = "area_id_v"
jbe@115:           AND "membership"."autoreject" = TRUE
jbe@115:           AND "member"."active"
jbe@115:           AND "interest"."autoreject" ISNULL
jbe@115:           AND "direct_voter"."member_id" ISNULL
jbe@115:           AND "delegating_voter"."member_id" ISNULL
jbe@115:       LOOP
jbe@115:         INSERT INTO "direct_voter"
jbe@115:           ("member_id", "issue_id", "weight", "autoreject") VALUES
jbe@115:           ("member_id_v", "issue_id_p", 1, TRUE);
jbe@115:         INSERT INTO "vote" (
jbe@115:           "member_id",
jbe@115:           "issue_id",
jbe@115:           "initiative_id",
jbe@115:           "grade"
jbe@115:           ) SELECT
jbe@115:             "member_id_v" AS "member_id",
jbe@115:             "issue_id_p"  AS "issue_id",
jbe@115:             "id"          AS "initiative_id",
jbe@115:             -1            AS "grade"
jbe@115:           FROM "initiative" WHERE "issue_id" = "issue_id_p";
jbe@115:       END LOOP;
jbe@115:       PERFORM "add_vote_delegations"("issue_id_p");
jbe@115:       UPDATE "issue" SET
jbe@115:         "state"  = 'calculation',
jbe@115:         "closed" = now(),
jbe@115:         "voter_count" = (
jbe@115:           SELECT coalesce(sum("weight"), 0)
jbe@115:           FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
jbe@115:         )
jbe@115:         WHERE "id" = "issue_id_p";
jbe@115:       UPDATE "initiative" SET
jbe@115:         "positive_votes" = "vote_counts"."positive_votes",
jbe@115:         "negative_votes" = "vote_counts"."negative_votes",
jbe@115:         "agreed" = CASE WHEN "majority_strict" THEN
jbe@115:           "vote_counts"."positive_votes" * "majority_den" >
jbe@115:           "majority_num" *
jbe@115:           ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
jbe@115:         ELSE
jbe@115:           "vote_counts"."positive_votes" * "majority_den" >=
jbe@115:           "majority_num" *
jbe@115:           ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
jbe@115:         END
jbe@115:         FROM
jbe@115:           ( SELECT
jbe@115:               "initiative"."id" AS "initiative_id",
jbe@115:               coalesce(
jbe@115:                 sum(
jbe@115:                   CASE WHEN "grade" > 0 THEN "direct_voter"."weight" ELSE 0 END
jbe@115:                 ),
jbe@115:                 0
jbe@115:               ) AS "positive_votes",
jbe@115:               coalesce(
jbe@115:                 sum(
jbe@115:                   CASE WHEN "grade" < 0 THEN "direct_voter"."weight" ELSE 0 END
jbe@115:                 ),
jbe@115:                 0
jbe@115:               ) AS "negative_votes"
jbe@115:             FROM "initiative"
jbe@115:             JOIN "issue" ON "initiative"."issue_id" = "issue"."id"
jbe@115:             JOIN "policy" ON "issue"."policy_id" = "policy"."id"
jbe@115:             LEFT JOIN "direct_voter"
jbe@115:               ON "direct_voter"."issue_id" = "initiative"."issue_id"
jbe@115:             LEFT JOIN "vote"
jbe@115:               ON "vote"."initiative_id" = "initiative"."id"
jbe@115:               AND "vote"."member_id" = "direct_voter"."member_id"
jbe@115:             WHERE "initiative"."issue_id" = "issue_id_p"
jbe@115:             AND "initiative"."admitted"  -- NOTE: NULL case is handled too
jbe@115:             GROUP BY "initiative"."id"
jbe@115:           ) AS "vote_counts",
jbe@115:           "issue",
jbe@115:           "policy"
jbe@115:         WHERE "vote_counts"."initiative_id" = "initiative"."id"
jbe@115:         AND "issue"."id" = "initiative"."issue_id"
jbe@115:         AND "policy"."id" = "issue"."policy_id";
jbe@115:       -- NOTE: "closed" column of issue must be set at this point
jbe@115:       DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
jbe@115:       INSERT INTO "battle" (
jbe@115:         "issue_id",
jbe@115:         "winning_initiative_id", "losing_initiative_id",
jbe@115:         "count"
jbe@115:       ) SELECT
jbe@115:         "issue_id",
jbe@115:         "winning_initiative_id", "losing_initiative_id",
jbe@115:         "count"
jbe@115:         FROM "battle_view" WHERE "issue_id" = "issue_id_p";
jbe@115:     END;
jbe@115:   $$;
jbe@115: 
jbe@115: CREATE OR REPLACE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE)
jbe@115:   RETURNS VOID
jbe@115:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@115:     DECLARE
jbe@115:       "dimension_v"     INTEGER;
jbe@115:       "vote_matrix"     INT4[][];  -- absolute votes
jbe@115:       "matrix"          INT8[][];  -- defeat strength / best paths
jbe@115:       "i"               INTEGER;
jbe@115:       "j"               INTEGER;
jbe@115:       "k"               INTEGER;
jbe@115:       "battle_row"      "battle"%ROWTYPE;
jbe@115:       "rank_ary"        INT4[];
jbe@115:       "rank_v"          INT4;
jbe@115:       "done_v"          INTEGER;
jbe@115:       "winners_ary"     INTEGER[];
jbe@115:       "initiative_id_v" "initiative"."id"%TYPE;
jbe@115:     BEGIN
jbe@115:       PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE;
jbe@115:       SELECT count(1) INTO "dimension_v" FROM "initiative"
jbe@115:         WHERE "issue_id" = "issue_id_p" AND "agreed";
jbe@115:       IF "dimension_v" = 1 THEN
jbe@115:         UPDATE "initiative" SET "rank" = 1
jbe@115:           WHERE "issue_id" = "issue_id_p" AND "agreed";
jbe@115:       ELSIF "dimension_v" > 1 THEN
jbe@115:         -- Create "vote_matrix" with absolute number of votes in pairwise
jbe@115:         -- comparison:
jbe@115:         "vote_matrix" := "square_matrix_init_string"("dimension_v");  -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
jbe@115:         "i" := 1;
jbe@115:         "j" := 2;
jbe@115:         FOR "battle_row" IN
jbe@115:           SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p"
jbe@115:           ORDER BY "winning_initiative_id", "losing_initiative_id"
jbe@115:         LOOP
jbe@115:           "vote_matrix"["i"]["j"] := "battle_row"."count";
jbe@115:           IF "j" = "dimension_v" THEN
jbe@115:             "i" := "i" + 1;
jbe@115:             "j" := 1;
jbe@115:           ELSE
jbe@115:             "j" := "j" + 1;
jbe@115:             IF "j" = "i" THEN
jbe@115:               "j" := "j" + 1;
jbe@115:             END IF;
jbe@115:           END IF;
jbe@115:         END LOOP;
jbe@115:         IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN
jbe@115:           RAISE EXCEPTION 'Wrong battle count (should not happen)';
jbe@115:         END IF;
jbe@115:         -- Store defeat strengths in "matrix" using "defeat_strength"
jbe@115:         -- function:
jbe@115:         "matrix" := "square_matrix_init_string"("dimension_v");  -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
jbe@115:         "i" := 1;
jbe@115:         LOOP
jbe@115:           "j" := 1;
jbe@115:           LOOP
jbe@115:             IF "i" != "j" THEN
jbe@115:               "matrix"["i"]["j"] := "defeat_strength"(
jbe@115:                 "vote_matrix"["i"]["j"],
jbe@115:                 "vote_matrix"["j"]["i"]
jbe@115:               );
jbe@115:             END IF;
jbe@115:             EXIT WHEN "j" = "dimension_v";
jbe@115:             "j" := "j" + 1;
jbe@115:           END LOOP;
jbe@115:           EXIT WHEN "i" = "dimension_v";
jbe@115:           "i" := "i" + 1;
jbe@115:         END LOOP;
jbe@115:         -- Find best paths:
jbe@115:         "i" := 1;
jbe@115:         LOOP
jbe@115:           "j" := 1;
jbe@115:           LOOP
jbe@115:             IF "i" != "j" THEN
jbe@115:               "k" := 1;
jbe@115:               LOOP
jbe@115:                 IF "i" != "k" AND "j" != "k" THEN
jbe@115:                   IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN
jbe@115:                     IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN
jbe@115:                       "matrix"["j"]["k"] := "matrix"["j"]["i"];
jbe@115:                     END IF;
jbe@115:                   ELSE
jbe@115:                     IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN
jbe@115:                       "matrix"["j"]["k"] := "matrix"["i"]["k"];
jbe@115:                     END IF;
jbe@115:                   END IF;
jbe@115:                 END IF;
jbe@115:                 EXIT WHEN "k" = "dimension_v";
jbe@115:                 "k" := "k" + 1;
jbe@115:               END LOOP;
jbe@115:             END IF;
jbe@115:             EXIT WHEN "j" = "dimension_v";
jbe@115:             "j" := "j" + 1;
jbe@115:           END LOOP;
jbe@115:           EXIT WHEN "i" = "dimension_v";
jbe@115:           "i" := "i" + 1;
jbe@115:         END LOOP;
jbe@115:         -- Determine order of winners:
jbe@115:         "rank_ary" := "array_init_string"("dimension_v");  -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
jbe@115:         "rank_v" := 1;
jbe@115:         "done_v" := 0;
jbe@115:         LOOP
jbe@115:           "winners_ary" := '{}';
jbe@115:           "i" := 1;
jbe@115:           LOOP
jbe@115:             IF "rank_ary"["i"] ISNULL THEN
jbe@115:               "j" := 1;
jbe@115:               LOOP
jbe@115:                 IF
jbe@115:                   "i" != "j" AND
jbe@115:                   "rank_ary"["j"] ISNULL AND
jbe@115:                   "matrix"["j"]["i"] > "matrix"["i"]["j"]
jbe@115:                 THEN
jbe@115:                   -- someone else is better
jbe@115:                   EXIT;
jbe@115:                 END IF;
jbe@115:                 IF "j" = "dimension_v" THEN
jbe@115:                   -- noone is better
jbe@115:                   "winners_ary" := "winners_ary" || "i";
jbe@115:                   EXIT;
jbe@115:                 END IF;
jbe@115:                 "j" := "j" + 1;
jbe@115:               END LOOP;
jbe@115:             END IF;
jbe@115:             EXIT WHEN "i" = "dimension_v";
jbe@115:             "i" := "i" + 1;
jbe@115:           END LOOP;
jbe@115:           "i" := 1;
jbe@115:           LOOP
jbe@115:             "rank_ary"["winners_ary"["i"]] := "rank_v";
jbe@115:             "done_v" := "done_v" + 1;
jbe@115:             EXIT WHEN "i" = array_upper("winners_ary", 1);
jbe@115:             "i" := "i" + 1;
jbe@115:           END LOOP;
jbe@115:           EXIT WHEN "done_v" = "dimension_v";
jbe@115:           "rank_v" := "rank_v" + 1;
jbe@115:         END LOOP;
jbe@115:         -- write preliminary ranks:
jbe@115:         "i" := 1;
jbe@115:         FOR "initiative_id_v" IN
jbe@115:           SELECT "id" FROM "initiative"
jbe@115:           WHERE "issue_id" = "issue_id_p" AND "agreed"
jbe@115:           ORDER BY "id"
jbe@115:         LOOP
jbe@115:           UPDATE "initiative" SET "rank" = "rank_ary"["i"]
jbe@115:             WHERE "id" = "initiative_id_v";
jbe@115:           "i" := "i" + 1;
jbe@115:         END LOOP;
jbe@115:         IF "i" != "dimension_v" + 1 THEN
jbe@115:           RAISE EXCEPTION 'Wrong winner count (should not happen)';
jbe@115:         END IF;
jbe@115:         -- straighten ranks (start counting with 1, no equal ranks):
jbe@115:         "rank_v" := 1;
jbe@115:         FOR "initiative_id_v" IN
jbe@115:           SELECT "id" FROM "initiative"
jbe@115:           WHERE "issue_id" = "issue_id_p" AND "rank" NOTNULL
jbe@115:           ORDER BY
jbe@115:             "rank",
jbe@115:             "vote_ratio"("positive_votes", "negative_votes") DESC,
jbe@115:             "id"
jbe@115:         LOOP
jbe@115:           UPDATE "initiative" SET "rank" = "rank_v"
jbe@115:             WHERE "id" = "initiative_id_v";
jbe@115:           "rank_v" := "rank_v" + 1;
jbe@115:         END LOOP;
jbe@115:       END IF;
jbe@115:       -- mark issue as finished
jbe@115:       UPDATE "issue" SET
jbe@115:         "state" =
jbe@121:           CASE WHEN "dimension_v" = 0 THEN
jbe@121:             'finished_without_winner'::"issue_state"
jbe@115:           ELSE
jbe@121:             'finished_with_winner'::"issue_state"
jbe@115:           END,
jbe@115:         "ranks_available" = TRUE
jbe@115:         WHERE "id" = "issue_id_p";
jbe@115:       RETURN;
jbe@115:     END;
jbe@115:   $$;
jbe@115: 
jbe@115: CREATE OR REPLACE FUNCTION "check_issue"
jbe@115:   ( "issue_id_p" "issue"."id"%TYPE )
jbe@115:   RETURNS VOID
jbe@115:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@115:     DECLARE
jbe@115:       "issue_row"         "issue"%ROWTYPE;
jbe@115:       "policy_row"        "policy"%ROWTYPE;
jbe@115:       "voting_requested_v" BOOLEAN;
jbe@115:     BEGIN
jbe@115:       PERFORM "lock_issue"("issue_id_p");
jbe@115:       SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
jbe@115:       -- only process open issues:
jbe@115:       IF "issue_row"."closed" ISNULL THEN
jbe@115:         SELECT * INTO "policy_row" FROM "policy"
jbe@115:           WHERE "id" = "issue_row"."policy_id";
jbe@115:         -- create a snapshot, unless issue is already fully frozen:
jbe@115:         IF "issue_row"."fully_frozen" ISNULL THEN
jbe@115:           PERFORM "create_snapshot"("issue_id_p");
jbe@115:           SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
jbe@115:         END IF;
jbe@115:         -- eventually close or accept issues, which have not been accepted:
jbe@115:         IF "issue_row"."accepted" ISNULL THEN
jbe@115:           IF EXISTS (
jbe@115:             SELECT NULL FROM "initiative"
jbe@115:             WHERE "issue_id" = "issue_id_p"
jbe@115:             AND "supporter_count" > 0
jbe@115:             AND "supporter_count" * "policy_row"."issue_quorum_den"
jbe@115:             >= "issue_row"."population" * "policy_row"."issue_quorum_num"
jbe@115:           ) THEN
jbe@115:             -- accept issues, if supporter count is high enough
jbe@115:             PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
jbe@115:             -- NOTE: "issue_row" used later
jbe@115:             "issue_row"."state" := 'discussion';
jbe@115:             "issue_row"."accepted" := now();
jbe@115:             UPDATE "issue" SET
jbe@115:               "state"    = "issue_row"."state",
jbe@115:               "accepted" = "issue_row"."accepted"
jbe@115:               WHERE "id" = "issue_row"."id";
jbe@115:           ELSIF
jbe@115:             now() >= "issue_row"."created" + "issue_row"."admission_time"
jbe@115:           THEN
jbe@115:             -- close issues, if admission time has expired
jbe@115:             PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
jbe@115:             UPDATE "issue" SET
jbe@115:               "state" = 'canceled_issue_not_accepted',
jbe@115:               "closed" = now()
jbe@115:               WHERE "id" = "issue_row"."id";
jbe@115:           END IF;
jbe@115:         END IF;
jbe@115:         -- eventually half freeze issues:
jbe@115:         IF
jbe@115:           -- NOTE: issue can't be closed at this point, if it has been accepted
jbe@115:           "issue_row"."accepted" NOTNULL AND
jbe@115:           "issue_row"."half_frozen" ISNULL
jbe@115:         THEN
jbe@115:           SELECT
jbe@115:             CASE
jbe@115:               WHEN "vote_now" * 2 > "issue_row"."population" THEN
jbe@115:                 TRUE
jbe@115:               WHEN "vote_later" * 2 > "issue_row"."population" THEN
jbe@115:                 FALSE
jbe@115:               ELSE NULL
jbe@115:             END
jbe@115:             INTO "voting_requested_v"
jbe@115:             FROM "issue" WHERE "id" = "issue_id_p";
jbe@115:           IF
jbe@115:             "voting_requested_v" OR (
jbe@115:               "voting_requested_v" ISNULL AND
jbe@115:               now() >= "issue_row"."accepted" + "issue_row"."discussion_time"
jbe@115:             )
jbe@115:           THEN
jbe@115:             PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze');
jbe@115:             -- NOTE: "issue_row" used later
jbe@115:             "issue_row"."state" := 'verification';
jbe@115:             "issue_row"."half_frozen" := now();
jbe@115:             UPDATE "issue" SET
jbe@115:               "state"       = "issue_row"."state",
jbe@115:               "half_frozen" = "issue_row"."half_frozen"
jbe@115:               WHERE "id" = "issue_row"."id";
jbe@115:           END IF;
jbe@115:         END IF;
jbe@115:         -- close issues after some time, if all initiatives have been revoked:
jbe@115:         IF
jbe@115:           "issue_row"."closed" ISNULL AND
jbe@115:           NOT EXISTS (
jbe@115:             -- all initiatives are revoked
jbe@115:             SELECT NULL FROM "initiative"
jbe@115:             WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
jbe@115:           ) AND (
jbe@115:             -- and issue has not been accepted yet
jbe@115:             "issue_row"."accepted" ISNULL OR
jbe@115:             NOT EXISTS (
jbe@115:               -- or no initiatives have been revoked lately
jbe@115:               SELECT NULL FROM "initiative"
jbe@115:               WHERE "issue_id" = "issue_id_p"
jbe@115:               AND now() < "revoked" + "issue_row"."verification_time"
jbe@115:             ) OR (
jbe@115:               -- or verification time has elapsed
jbe@115:               "issue_row"."half_frozen" NOTNULL AND
jbe@115:               "issue_row"."fully_frozen" ISNULL AND
jbe@115:               now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
jbe@115:             )
jbe@115:           )
jbe@115:         THEN
jbe@115:           -- NOTE: "issue_row" used later
jbe@115:           IF "issue_row"."accepted" ISNULL THEN
jbe@115:             "issue_row"."state" := 'canceled_revoked_before_accepted';
jbe@115:           ELSIF "issue_row"."half_frozen" ISNULL THEN
jbe@115:             "issue_row"."state" := 'canceled_after_revocation_during_discussion';
jbe@115:           ELSE
jbe@115:             "issue_row"."state" := 'canceled_after_revocation_during_verification';
jbe@115:           END IF;
jbe@115:           "issue_row"."closed" := now();
jbe@115:           UPDATE "issue" SET
jbe@115:             "state"  = "issue_row"."state",
jbe@115:             "closed" = "issue_row"."closed"
jbe@115:             WHERE "id" = "issue_row"."id";
jbe@115:         END IF;
jbe@115:         -- fully freeze issue after verification time:
jbe@115:         IF
jbe@115:           "issue_row"."half_frozen" NOTNULL AND
jbe@115:           "issue_row"."fully_frozen" ISNULL AND
jbe@115:           "issue_row"."closed" ISNULL AND
jbe@115:           now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
jbe@115:         THEN
jbe@115:           PERFORM "freeze_after_snapshot"("issue_id_p");
jbe@115:           -- NOTE: "issue" might change, thus "issue_row" has to be updated below
jbe@115:         END IF;
jbe@115:         SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
jbe@115:         -- close issue by calling close_voting(...) after voting time:
jbe@115:         IF
jbe@115:           "issue_row"."closed" ISNULL AND
jbe@115:           "issue_row"."fully_frozen" NOTNULL AND
jbe@115:           now() >= "issue_row"."fully_frozen" + "issue_row"."voting_time"
jbe@115:         THEN
jbe@115:           PERFORM "close_voting"("issue_id_p");
jbe@115:           -- calculate ranks will not consume much time and can be done now
jbe@115:           PERFORM "calculate_ranks"("issue_id_p");
jbe@115:         END IF;
jbe@115:       END IF;
jbe@115:       RETURN;
jbe@115:     END;
jbe@115:   $$;
jbe@115: 
jbe@115: CREATE OR REPLACE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE)
jbe@115:   RETURNS VOID
jbe@115:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@115:     DECLARE
jbe@115:       "issue_row" "issue"%ROWTYPE;
jbe@115:     BEGIN
jbe@115:       SELECT * INTO "issue_row"
jbe@115:         FROM "issue" WHERE "id" = "issue_id_p"
jbe@115:         FOR UPDATE;
jbe@115:       IF "issue_row"."cleaned" ISNULL THEN
jbe@115:         UPDATE "issue" SET
jbe@115:           "closed" = NULL,
jbe@115:           "ranks_available" = FALSE
jbe@115:           WHERE "id" = "issue_id_p";
jbe@115:         DELETE FROM "delegating_voter"
jbe@115:           WHERE "issue_id" = "issue_id_p";
jbe@115:         DELETE FROM "direct_voter"
jbe@115:           WHERE "issue_id" = "issue_id_p";
jbe@115:         DELETE FROM "delegating_interest_snapshot"
jbe@115:           WHERE "issue_id" = "issue_id_p";
jbe@115:         DELETE FROM "direct_interest_snapshot"
jbe@115:           WHERE "issue_id" = "issue_id_p";
jbe@115:         DELETE FROM "delegating_population_snapshot"
jbe@115:           WHERE "issue_id" = "issue_id_p";
jbe@115:         DELETE FROM "direct_population_snapshot"
jbe@115:           WHERE "issue_id" = "issue_id_p";
jbe@115:         DELETE FROM "non_voter"
jbe@115:           WHERE "issue_id" = "issue_id_p";
jbe@115:         DELETE FROM "delegation"
jbe@115:           WHERE "issue_id" = "issue_id_p";
jbe@115:         DELETE FROM "supporter"
jbe@115:           WHERE "issue_id" = "issue_id_p";
jbe@115:         UPDATE "issue" SET
jbe@115:           "closed"          = "issue_row"."closed",
jbe@115:           "ranks_available" = "issue_row"."ranks_available",
jbe@115:           "cleaned"         = now()
jbe@115:           WHERE "id" = "issue_id_p";
jbe@115:       END IF;
jbe@115:       RETURN;
jbe@115:     END;
jbe@115:   $$;
jbe@115: 
jbe@115: CREATE OR REPLACE FUNCTION "check_issue"
jbe@115:   ( "issue_id_p" "issue"."id"%TYPE )
jbe@115:   RETURNS VOID
jbe@115:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@115:     DECLARE
jbe@115:       "issue_row"         "issue"%ROWTYPE;
jbe@115:       "policy_row"        "policy"%ROWTYPE;
jbe@115:       "voting_requested_v" BOOLEAN;
jbe@115:     BEGIN
jbe@115:       PERFORM "lock_issue"("issue_id_p");
jbe@115:       SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
jbe@115:       -- only process open issues:
jbe@115:       IF "issue_row"."closed" ISNULL THEN
jbe@115:         SELECT * INTO "policy_row" FROM "policy"
jbe@115:           WHERE "id" = "issue_row"."policy_id";
jbe@115:         -- create a snapshot, unless issue is already fully frozen:
jbe@115:         IF "issue_row"."fully_frozen" ISNULL THEN
jbe@115:           PERFORM "create_snapshot"("issue_id_p");
jbe@115:           SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
jbe@115:         END IF;
jbe@115:         -- eventually close or accept issues, which have not been accepted:
jbe@115:         IF "issue_row"."accepted" ISNULL THEN
jbe@115:           IF EXISTS (
jbe@115:             SELECT NULL FROM "initiative"
jbe@115:             WHERE "issue_id" = "issue_id_p"
jbe@115:             AND "supporter_count" > 0
jbe@115:             AND "supporter_count" * "policy_row"."issue_quorum_den"
jbe@115:             >= "issue_row"."population" * "policy_row"."issue_quorum_num"
jbe@115:           ) THEN
jbe@115:             -- accept issues, if supporter count is high enough
jbe@115:             PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
jbe@115:             -- NOTE: "issue_row" used later
jbe@115:             "issue_row"."state" := 'discussion';
jbe@115:             "issue_row"."accepted" := now();
jbe@115:             UPDATE "issue" SET
jbe@115:               "state"    = "issue_row"."state",
jbe@115:               "accepted" = "issue_row"."accepted"
jbe@115:               WHERE "id" = "issue_row"."id";
jbe@115:           ELSIF
jbe@115:             now() >= "issue_row"."created" + "issue_row"."admission_time"
jbe@115:           THEN
jbe@115:             -- close issues, if admission time has expired
jbe@115:             PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
jbe@115:             UPDATE "issue" SET
jbe@115:               "state" = 'canceled_issue_not_accepted',
jbe@115:               "closed" = now()
jbe@115:               WHERE "id" = "issue_row"."id";
jbe@115:           END IF;
jbe@115:         END IF;
jbe@115:         -- eventually half freeze issues:
jbe@115:         IF
jbe@115:           -- NOTE: issue can't be closed at this point, if it has been accepted
jbe@115:           "issue_row"."accepted" NOTNULL AND
jbe@115:           "issue_row"."half_frozen" ISNULL
jbe@115:         THEN
jbe@115:           SELECT
jbe@115:             CASE
jbe@115:               WHEN "vote_now" * 2 > "issue_row"."population" THEN
jbe@115:                 TRUE
jbe@115:               WHEN "vote_later" * 2 > "issue_row"."population" THEN
jbe@115:                 FALSE
jbe@115:               ELSE NULL
jbe@115:             END
jbe@115:             INTO "voting_requested_v"
jbe@115:             FROM "issue" WHERE "id" = "issue_id_p";
jbe@115:           IF
jbe@115:             "voting_requested_v" OR (
jbe@115:               "voting_requested_v" ISNULL AND
jbe@115:               now() >= "issue_row"."accepted" + "issue_row"."discussion_time"
jbe@115:             )
jbe@115:           THEN
jbe@115:             PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze');
jbe@115:             -- NOTE: "issue_row" used later
jbe@115:             "issue_row"."state" := 'verification';
jbe@115:             "issue_row"."half_frozen" := now();
jbe@115:             UPDATE "issue" SET
jbe@115:               "state"       = "issue_row"."state",
jbe@115:               "half_frozen" = "issue_row"."half_frozen"
jbe@115:               WHERE "id" = "issue_row"."id";
jbe@115:           END IF;
jbe@115:         END IF;
jbe@115:         -- close issues after some time, if all initiatives have been revoked:
jbe@115:         IF
jbe@115:           "issue_row"."closed" ISNULL AND
jbe@115:           NOT EXISTS (
jbe@115:             -- all initiatives are revoked
jbe@115:             SELECT NULL FROM "initiative"
jbe@115:             WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
jbe@115:           ) AND (
jbe@115:             -- and issue has not been accepted yet
jbe@115:             "issue_row"."accepted" ISNULL OR
jbe@115:             NOT EXISTS (
jbe@115:               -- or no initiatives have been revoked lately
jbe@115:               SELECT NULL FROM "initiative"
jbe@115:               WHERE "issue_id" = "issue_id_p"
jbe@115:               AND now() < "revoked" + "issue_row"."verification_time"
jbe@115:             ) OR (
jbe@115:               -- or verification time has elapsed
jbe@115:               "issue_row"."half_frozen" NOTNULL AND
jbe@115:               "issue_row"."fully_frozen" ISNULL AND
jbe@115:               now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
jbe@115:             )
jbe@115:           )
jbe@115:         THEN
jbe@115:           -- NOTE: "issue_row" used later
jbe@115:           IF "issue_row"."accepted" ISNULL THEN
jbe@115:             "issue_row"."state" := 'canceled_revoked_before_accepted';
jbe@115:           ELSIF "issue_row"."half_frozen" ISNULL THEN
jbe@115:             "issue_row"."state" := 'canceled_after_revocation_during_discussion';
jbe@115:           ELSE
jbe@115:             "issue_row"."state" := 'canceled_after_revocation_during_verification';
jbe@115:           END IF;
jbe@115:           "issue_row"."closed" := now();
jbe@115:           UPDATE "issue" SET
jbe@115:             "state"  = "issue_row"."state",
jbe@115:             "closed" = "issue_row"."closed"
jbe@115:             WHERE "id" = "issue_row"."id";
jbe@115:         END IF;
jbe@115:         -- fully freeze issue after verification time:
jbe@115:         IF
jbe@115:           "issue_row"."half_frozen" NOTNULL AND
jbe@115:           "issue_row"."fully_frozen" ISNULL AND
jbe@115:           "issue_row"."closed" ISNULL AND
jbe@115:           now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
jbe@115:         THEN
jbe@115:           PERFORM "freeze_after_snapshot"("issue_id_p");
jbe@115:           -- NOTE: "issue" might change, thus "issue_row" has to be updated below
jbe@115:         END IF;
jbe@115:         SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
jbe@115:         -- close issue by calling close_voting(...) after voting time:
jbe@115:         IF
jbe@115:           "issue_row"."closed" ISNULL AND
jbe@115:           "issue_row"."fully_frozen" NOTNULL AND
jbe@115:           now() >= "issue_row"."fully_frozen" + "issue_row"."voting_time"
jbe@115:         THEN
jbe@115:           PERFORM "close_voting"("issue_id_p");
jbe@115:           -- calculate ranks will not consume much time and can be done now
jbe@115:           PERFORM "calculate_ranks"("issue_id_p");
jbe@115:         END IF;
jbe@115:       END IF;
jbe@115:       RETURN;
jbe@115:     END;
jbe@115:   $$;
jbe@115: 
jbe@115: CREATE OR REPLACE FUNCTION "delete_private_data"()
jbe@115:   RETURNS VOID
jbe@115:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@115:     BEGIN
jbe@115:       UPDATE "member" SET
jbe@115:         "last_login"                   = NULL,
jbe@115:         "login"                        = NULL,
jbe@115:         "password"                     = NULL,
jbe@115:         "notify_email"                 = NULL,
jbe@115:         "notify_email_unconfirmed"     = NULL,
jbe@115:         "notify_email_secret"          = NULL,
jbe@115:         "notify_email_secret_expiry"   = NULL,
jbe@115:         "notify_email_lock_expiry"     = NULL,
jbe@115:         "password_reset_secret"        = NULL,
jbe@115:         "password_reset_secret_expiry" = NULL,
jbe@115:         "organizational_unit"          = NULL,
jbe@115:         "internal_posts"               = NULL,
jbe@115:         "realname"                     = NULL,
jbe@115:         "birthday"                     = NULL,
jbe@115:         "address"                      = NULL,
jbe@115:         "email"                        = NULL,
jbe@115:         "xmpp_address"                 = NULL,
jbe@115:         "website"                      = NULL,
jbe@115:         "phone"                        = NULL,
jbe@115:         "mobile_phone"                 = NULL,
jbe@115:         "profession"                   = NULL,
jbe@115:         "external_memberships"         = NULL,
jbe@115:         "external_posts"               = NULL,
jbe@115:         "statement"                    = NULL;
jbe@115:       -- "text_search_data" is updated by triggers
jbe@115:       DELETE FROM "invite_code";
jbe@115:       DELETE FROM "setting";
jbe@115:       DELETE FROM "setting_map";
jbe@115:       DELETE FROM "member_relation_setting";
jbe@115:       DELETE FROM "member_image";
jbe@115:       DELETE FROM "contact";
jbe@115:       DELETE FROM "ignored_member";
jbe@115:       DELETE FROM "session";
jbe@115:       DELETE FROM "area_setting";
jbe@115:       DELETE FROM "issue_setting";
jbe@115:       DELETE FROM "ignored_initiative";
jbe@115:       DELETE FROM "initiative_setting";
jbe@115:       DELETE FROM "suggestion_setting";
jbe@115:       DELETE FROM "non_voter";
jbe@115:       DELETE FROM "direct_voter" USING "issue"
jbe@115:         WHERE "direct_voter"."issue_id" = "issue"."id"
jbe@115:         AND "issue"."closed" ISNULL;
jbe@115:       RETURN;
jbe@115:     END;
jbe@115:   $$;
jbe@115: 
jbe@115: 
jbe@115: -- Delete old "delegation_scope" TYPE:
jbe@115: 
jbe@115: DROP TYPE "delegation_scope_old";
jbe@115: 
jbe@115: 
jbe@115: COMMIT;
jbe@115: 
jbe@115: 
jbe@122: -- Generate issue states and add constraints:
jbe@115: 
jbe@115: UPDATE "issue" SET "state" =
jbe@115:   CASE
jbe@115:   WHEN "closed" ISNULL THEN
jbe@115:     CASE
jbe@115:     WHEN "accepted" ISNULL THEN
jbe@115:       'admission'::"issue_state"
jbe@115:     WHEN "half_frozen" ISNULL THEN
jbe@115:       'discussion'::"issue_state"
jbe@115:     WHEN "fully_frozen" ISNULL THEN
jbe@115:       'verification'::"issue_state"
jbe@115:     ELSE
jbe@115:       'voting'::"issue_state"
jbe@115:     END
jbe@115:   WHEN "fully_frozen" NOTNULL THEN
jbe@115:     CASE
jbe@115:     WHEN "fully_frozen" = "closed" THEN
jbe@115:       'canceled_no_initiative_admitted'::"issue_state"
jbe@115:     ELSE
jbe@115:       'finished_without_winner'::"issue_state"  -- NOTE: corrected later
jbe@115:     END
jbe@115:   WHEN "half_frozen" NOTNULL THEN
jbe@115:     'canceled_after_revocation_during_verification'::"issue_state"
jbe@115:   WHEN "accepted" NOTNULL THEN
jbe@115:     'canceled_after_revocation_during_discussion'::"issue_state"
jbe@115:   ELSE
jbe@115:     'canceled_revoked_before_accepted'::"issue_state"  -- NOTE: corrected later
jbe@115:   END;
jbe@117: UPDATE "issue" SET "state" = 'finished_with_winner'
jbe@117:   FROM "initiative"
jbe@117:   WHERE "issue"."id" = "initiative"."issue_id"
jbe@117:   AND "issue"."state" = 'finished_without_winner'
jbe@117:   AND "initiative"."agreed";
jbe@117: UPDATE "issue" SET "state" = 'canceled_issue_not_accepted'
jbe@117:   FROM "initiative"
jbe@117:   WHERE "issue"."id" = "initiative"."issue_id"
jbe@117:   AND "issue"."state" = 'canceled_revoked_before_accepted'
jbe@117:   AND "initiative"."revoked" ISNULL;
jbe@115: 
jbe@115: ALTER TABLE "issue" ALTER "state" SET NOT NULL;
jbe@115: 
jbe@115: ALTER TABLE "issue" DROP CONSTRAINT "valid_state";
jbe@115: ALTER TABLE "issue" ADD CONSTRAINT "valid_state" CHECK ((
jbe@115:     ("accepted" ISNULL  AND "half_frozen" ISNULL  AND "fully_frozen" ISNULL  AND "closed" ISNULL  AND "ranks_available" = FALSE) OR
jbe@115:     ("accepted" ISNULL  AND "half_frozen" ISNULL  AND "fully_frozen" ISNULL  AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
jbe@115:     ("accepted" NOTNULL AND "half_frozen" ISNULL  AND "fully_frozen" ISNULL  AND "closed" ISNULL  AND "ranks_available" = FALSE) OR
jbe@115:     ("accepted" NOTNULL AND "half_frozen" ISNULL  AND "fully_frozen" ISNULL  AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
jbe@115:     ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL  AND "closed" ISNULL  AND "ranks_available" = FALSE) OR
jbe@115:     ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL  AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
jbe@115:     ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" ISNULL  AND "ranks_available" = FALSE) OR
jbe@115:     ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
jbe@115:     ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = TRUE)
jbe@115:   ) AND (
jbe@115:     ("state" = 'admission'    AND "closed" ISNULL AND "accepted" ISNULL) OR
jbe@115:     ("state" = 'discussion'   AND "closed" ISNULL AND "accepted" NOTNULL AND "half_frozen" ISNULL) OR
jbe@115:     ("state" = 'verification' AND "closed" ISNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL) OR
jbe@115:     ("state" = 'voting'       AND "closed" ISNULL AND "fully_frozen" NOTNULL) OR
jbe@115:     ("state" = 'canceled_revoked_before_accepted'              AND "closed" NOTNULL AND "accepted" ISNULL) OR
jbe@115:     ("state" = 'canceled_issue_not_accepted'                   AND "closed" NOTNULL AND "accepted" ISNULL) OR
jbe@115:     ("state" = 'canceled_after_revocation_during_discussion'   AND "closed" NOTNULL AND "half_frozen"  ISNULL) OR
jbe@115:     ("state" = 'canceled_after_revocation_during_verification' AND "closed" NOTNULL AND "fully_frozen" ISNULL) OR
jbe@115:     ("state" = 'calculation'                     AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = FALSE) OR
jbe@115:     ("state" = 'canceled_no_initiative_admitted' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE) OR
jbe@115:     ("state" = 'finished_without_winner'         AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE) OR
jbe@115:     ("state" = 'finished_with_winner'            AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE)
jbe@115:   ));
jbe@115: 
jbe@115: 
jbe@122: -- Guess "revoked_by_member_id" values based on author of current draft and add constraint:
jbe@115: 
jbe@115: UPDATE "initiative" SET "revoked_by_member_id" = "author_id"
jbe@115:   FROM "current_draft"
jbe@115:   WHERE "initiative"."id" = "current_draft"."initiative_id"
jbe@115:   AND "initiative"."revoked" NOTNULL;
jbe@115: 
jbe@115: ALTER TABLE "initiative" ADD
jbe@115:   CONSTRAINT "all_or_none_of_revoked_and_revoked_by_member_id_must_be_null"
jbe@115:   CHECK ("revoked" NOTNULL = "revoked_by_member_id" NOTNULL);
jbe@115: 
jbe@115: 
jbe@122: -- Fill "unit_id" column with default value where neccessary and add constraints:
jbe@115: 
jbe@115: UPDATE "delegation" SET "unit_id" = 1 WHERE "scope" = 'unit';
jbe@115: 
jbe@115: ALTER TABLE "delegation" ADD CONSTRAINT "area_id_and_issue_id_set_according_to_scope"
jbe@115:   CHECK (
jbe@115:     ("scope" = 'unit'  AND "unit_id" NOTNULL AND "area_id" ISNULL  AND "issue_id" ISNULL ) OR
jbe@115:     ("scope" = 'area'  AND "unit_id" ISNULL  AND "area_id" NOTNULL AND "issue_id" ISNULL ) OR
jbe@115:     ("scope" = 'issue' AND "unit_id" ISNULL  AND "area_id" ISNULL  AND "issue_id" NOTNULL) );
jbe@117: 
jbe@117: 
jbe@118: -- Filling of "event" table with old (reconstructed) events:
jbe@118: 
jbe@118: DELETE FROM "event";
jbe@118: SELECT setval('event_id_seq', 1, false);
jbe@118: 
jbe@118: INSERT INTO "event"
jbe@118:   ( "occurrence", "event", "member_id", "issue_id", "state",
jbe@118:     "initiative_id", "draft_id", "suggestion_id" )
jbe@118:   SELECT * FROM (
jbe@118:     SELECT * FROM (
jbe@118:       SELECT DISTINCT ON ("initiative"."id")
jbe@118:         "timeline"."occurrence",
jbe@118:         CASE WHEN "issue_creation"."issue_id" NOTNULL THEN
jbe@118:           'initiative_created_in_new_issue'::"event_type"
jbe@118:         ELSE
jbe@118:           'initiative_created_in_existing_issue'::"event_type"
jbe@118:         END,
jbe@118:         "draft"."author_id",
jbe@118:         "issue"."id",
jbe@118:         CASE
jbe@118:           WHEN "timeline"."occurrence" < "issue"."accepted" THEN
jbe@118:             'admission'::"issue_state"
jbe@118:           WHEN "timeline"."occurrence" < "issue"."half_frozen" THEN
jbe@118:             'discussion'::"issue_state"
jbe@118:           ELSE
jbe@118:             'verification'::"issue_state"
jbe@118:         END,
jbe@118:         "initiative"."id",
jbe@118:         "draft"."id",
jbe@118:         NULL::INT8
jbe@118:       FROM "timeline"
jbe@118:       JOIN "initiative" ON "timeline"."initiative_id" = "initiative"."id"
jbe@118:       JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
jbe@118:       LEFT JOIN "timeline" AS "issue_creation"
jbe@118:         ON "initiative"."issue_id" = "issue_creation"."issue_id"
jbe@118:         AND "issue_creation"."event" = 'issue_created'
jbe@118:         AND "timeline"."occurrence" = "issue_creation"."occurrence"
jbe@118:       JOIN "draft"
jbe@118:         ON "initiative"."id" = "draft"."initiative_id"
jbe@118:       WHERE "timeline"."event" = 'initiative_created'
jbe@118:       ORDER BY "initiative"."id", "draft"."id"
jbe@118:     ) AS "subquery"  -- NOTE: subquery needed due to DISTINCT/ORDER
jbe@118:   UNION ALL
jbe@118:     SELECT
jbe@118:       "timeline"."occurrence",
jbe@118:       'issue_state_changed'::"event_type",
jbe@118:       NULL,
jbe@118:       "issue"."id",
jbe@118:       CASE
jbe@118:         WHEN "timeline"."event" IN (
jbe@118:           'issue_canceled',
jbe@118:           'issue_finished_without_voting',
jbe@118:           'issue_finished_after_voting'
jbe@118:         ) THEN
jbe@118:           "issue"."state"
jbe@118:         WHEN "timeline"."event" = 'issue_accepted' THEN
jbe@118:           'discussion'::"issue_state"
jbe@118:         WHEN "timeline"."event" = 'issue_half_frozen' THEN
jbe@118:           'verification'::"issue_state"
jbe@118:         WHEN "timeline"."event" = 'issue_voting_started' THEN
jbe@118:           'voting'::"issue_state"
jbe@118:       END,
jbe@118:       NULL,
jbe@118:       NULL,
jbe@118:       NULL
jbe@118:     FROM "timeline"
jbe@118:     JOIN "issue" ON "timeline"."issue_id" = "issue"."id"
jbe@118:     WHERE "timeline"."event" IN (
jbe@118:       'issue_canceled',
jbe@118:       'issue_accepted',
jbe@118:       'issue_half_frozen',
jbe@118:       'issue_finished_without_voting',
jbe@118:       'issue_voting_started',
jbe@118:       'issue_finished_after_voting' )
jbe@118:   UNION ALL
jbe@118:     SELECT
jbe@118:       "timeline"."occurrence",
jbe@118:       'initiative_revoked'::"event_type",
jbe@118:       "initiative"."revoked_by_member_id",
jbe@118:       "issue"."id",
jbe@118:       CASE
jbe@118:         WHEN "timeline"."occurrence" < "issue"."accepted" THEN
jbe@118:           'admission'::"issue_state"
jbe@118:         WHEN "timeline"."occurrence" < "issue"."half_frozen" THEN
jbe@118:           'discussion'::"issue_state"
jbe@118:         ELSE
jbe@118:           'verification'::"issue_state"
jbe@118:       END,
jbe@118:       "initiative"."id",
jbe@118:       "current_draft"."id",
jbe@118:       NULL
jbe@118:     FROM "timeline"
jbe@118:     JOIN "initiative" ON "timeline"."initiative_id" = "initiative"."id"
jbe@118:     JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
jbe@118:     JOIN "current_draft" ON "initiative"."id" = "current_draft"."initiative_id"
jbe@118:     WHERE "timeline"."event" = 'initiative_revoked'
jbe@118:   UNION ALL
jbe@118:     SELECT
jbe@118:       "timeline"."occurrence",
jbe@118:       'new_draft_created'::"event_type",
jbe@118:       "draft"."author_id",
jbe@118:       "issue"."id",
jbe@118:       CASE
jbe@118:         WHEN "timeline"."occurrence" < "issue"."accepted" THEN
jbe@118:           'admission'::"issue_state"
jbe@118:         WHEN "timeline"."occurrence" < "issue"."half_frozen" THEN
jbe@118:           'discussion'::"issue_state"
jbe@118:         ELSE
jbe@118:           'verification'::"issue_state"
jbe@118:       END,
jbe@118:       "initiative"."id",
jbe@118:       "draft"."id",
jbe@118:       NULL
jbe@118:     FROM "timeline"
jbe@118:     JOIN "draft" ON "timeline"."draft_id" = "draft"."id"
jbe@118:     JOIN "initiative" ON "draft"."initiative_id" = "initiative"."id"
jbe@118:     JOIN "issue" ON "initiative"."issue_id" = "issue"."id"
jbe@118:     LEFT JOIN "timeline" AS "initiative_creation"
jbe@118:       ON "initiative"."id" = "initiative_creation"."initiative_id"
jbe@118:       AND "initiative_creation"."event" = 'initiative_created'
jbe@118:       AND "timeline"."occurrence" = "initiative_creation"."occurrence"
jbe@118:     WHERE "timeline"."event" = 'draft_created'
jbe@118:     AND "initiative_creation"."initiative_id" ISNULL
jbe@118:   UNION ALL
jbe@118:     SELECT
jbe@118:       "timeline"."occurrence",
jbe@118:       'suggestion_created'::"event_type",
jbe@118:       "suggestion"."author_id",
jbe@118:       "issue"."id",
jbe@118:       CASE
jbe@118:         WHEN "timeline"."occurrence" < "issue"."accepted" THEN
jbe@118:           'admission'::"issue_state"
jbe@118:         WHEN "timeline"."occurrence" < "issue"."half_frozen" THEN
jbe@118:           'discussion'::"issue_state"
jbe@118:         ELSE
jbe@118:           'verification'::"issue_state"
jbe@118:       END,
jbe@118:       "initiative"."id",
jbe@118:       NULL,
jbe@118:       "suggestion"."id"
jbe@118:     FROM "timeline"
jbe@118:     JOIN "suggestion" ON "timeline"."suggestion_id" = "suggestion"."id"
jbe@118:     JOIN "initiative" ON "suggestion"."initiative_id" = "initiative"."id"
jbe@118:     JOIN "issue" ON "initiative"."issue_id" = "issue"."id"
jbe@118:     WHERE "timeline"."event" = 'suggestion_created'
jbe@118:   ) AS "subquery"
jbe@118:   ORDER BY "occurrence";
jbe@118: 
jbe@118: 




