jbe@262: BEGIN;
jbe@262: 
jbe@262: CREATE OR REPLACE VIEW "liquid_feedback_version" AS
jbe@262:   SELECT * FROM (VALUES ('2.1.0', 2, 1, 0))
jbe@262:   AS "subquery"("string", "major", "minor", "revision");
jbe@262: 
jbe@286: COMMENT ON TYPE "application_access_level" IS 'DEPRECATED, WILL BE REMOVED! Access privileges for applications using the API';
jbe@286: COMMENT ON TABLE "member_application" IS 'DEPRECATED, WILL BE REMOVED! Registered application being allowed to use the API';
jbe@286: 
jbe@262: ALTER TABLE "policy" ADD COLUMN "polling" BOOLEAN NOT NULL DEFAULT FALSE;
jbe@262: ALTER TABLE "policy" ALTER COLUMN "admission_time"    DROP NOT NULL;
jbe@262: ALTER TABLE "policy" ALTER COLUMN "discussion_time"   DROP NOT NULL;
jbe@262: ALTER TABLE "policy" ALTER COLUMN "verification_time" DROP NOT NULL;
jbe@262: ALTER TABLE "policy" ALTER COLUMN "voting_time"       DROP NOT NULL;
jbe@262: ALTER TABLE "policy" ADD CONSTRAINT "timing" CHECK (
jbe@262:           ( "polling" = FALSE AND
jbe@262:             "admission_time" NOTNULL AND "discussion_time" NOTNULL AND
jbe@262:             "verification_time" NOTNULL AND "voting_time" NOTNULL ) OR
jbe@262:           ( "polling" = TRUE AND
jbe@263:             "admission_time" ISNULL AND "discussion_time" NOTNULL AND
jbe@262:             "verification_time" NOTNULL AND "voting_time" NOTNULL ) OR
jbe@262:           ( "polling" = TRUE AND
jbe@262:             "admission_time" ISNULL AND "discussion_time" ISNULL AND
jbe@262:             "verification_time" ISNULL AND "voting_time" ISNULL ) );
jbe@263: COMMENT ON COLUMN "policy"."polling" IS 'TRUE = special policy for non-user-generated issues, i.e. polls ("admission_time" MUST be set to NULL, the other timings may be set to NULL altogether, allowing individual timing for issues)';
jbe@262: 
jbe@262: ALTER TABLE "initiative" ADD COLUMN "polling" BOOLEAN NOT NULL DEFAULT FALSE;
jbe@262: COMMENT ON COLUMN "initiative"."polling" IS 'Initiative is an option for a poll (see "policy"."polling"), and does not need to pass the initiative quorum';
jbe@262: 
jbe@262: ALTER TABLE "privilege" RENAME COLUMN "voting_right_manager" TO "member_manager";
jbe@262: ALTER TABLE "privilege" ADD COLUMN "initiative_right" BOOLEAN NOT NULL DEFAULT TRUE;
jbe@262: ALTER TABLE "privilege" ADD COLUMN "polling_right"    BOOLEAN NOT NULL DEFAULT FALSE;
jbe@262: UPDATE "privilege" SET "initiative_right" = "voting_right";
jbe@262: COMMENT ON COLUMN "privilege"."admin_manager"    IS 'Grant/revoke any privileges to/from other members';
jbe@262: COMMENT ON COLUMN "privilege"."member_manager"   IS 'Adding/removing members from the unit, granting or revoking "initiative_right" and "voting_right"';
jbe@262: COMMENT ON COLUMN "privilege"."initiative_right" IS 'Right to create an initiative';
jbe@262: COMMENT ON COLUMN "privilege"."voting_right"     IS 'Right to support initiatives, create and rate suggestions, and to vote';
jbe@262: COMMENT ON COLUMN "privilege"."polling_right"    IS 'Right to create polls (see "policy"."polling" and "initiative"."polling")';
jbe@262: 
jbe@285: ALTER TABLE "direct_voter" ADD COLUMN "comment_changed"   TIMESTAMPTZ;
jbe@285: ALTER TABLE "direct_voter" ADD COLUMN "formatting_engine" TEXT;
jbe@285: ALTER TABLE "direct_voter" ADD COLUMN "comment"           TEXT;
jbe@285: ALTER TABLE "direct_voter" ADD COLUMN "text_search_data"  TSVECTOR;
jbe@285: CREATE INDEX "direct_voter_text_search_data_idx" ON "direct_voter" USING gin ("text_search_data");
jbe@285: CREATE TRIGGER "update_text_search_data"
jbe@285:   BEFORE INSERT OR UPDATE ON "direct_voter"
jbe@285:   FOR EACH ROW EXECUTE PROCEDURE
jbe@285:   tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "comment");
jbe@285: 
jbe@285: COMMENT ON COLUMN "direct_voter"."comment_changed"   IS 'Shall be set on comment change, to indicate a comment being modified after voting has been finished; Automatically set to NULL after voting phase; Automatically set to NULL by trigger, if "comment" is set to NULL';
jbe@285: COMMENT ON COLUMN "direct_voter"."formatting_engine" IS 'Allows different formatting engines (i.e. wiki formats) to be used for "direct_voter"."comment"; Automatically set to NULL by trigger, if "comment" is set to NULL';
jbe@285: COMMENT ON COLUMN "direct_voter"."comment"           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@285: 
jbe@285: CREATE TABLE "rendered_voter_comment" (
jbe@285:         PRIMARY KEY ("issue_id", "member_id", "format"),
jbe@285:         FOREIGN KEY ("issue_id", "member_id")
jbe@285:           REFERENCES "direct_voter" ("issue_id", "member_id")
jbe@285:           ON DELETE CASCADE ON UPDATE CASCADE,
jbe@285:         "issue_id"              INT4,
jbe@285:         "member_id"             INT4,
jbe@285:         "format"                TEXT,
jbe@285:         "content"               TEXT            NOT NULL );
jbe@285: 
jbe@285: COMMENT ON TABLE "rendered_voter_comment" IS 'This table may be used by frontends to cache "rendered" voter comments (e.g. HTML output generated from wiki text)';
jbe@285: 
jbe@285: 
jbe@262: DROP TABLE "rendered_issue_comment";
jbe@262: DROP TABLE "issue_comment";
jbe@285: DROP TABLE "rendered_voting_comment";
jbe@285: DROP TABLE "voting_comment";
jbe@262: 
jbe@284: CREATE FUNCTION "non_voter_deletes_direct_voter_trigger"()
jbe@284:   RETURNS TRIGGER
jbe@284:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@284:     BEGIN
jbe@284:       DELETE FROM "direct_voter"
jbe@284:         WHERE "issue_id" = NEW."issue_id" AND "member_id" = NEW."member_id";
jbe@284:       RETURN NULL;
jbe@284:     END;
jbe@284:   $$;
jbe@284: 
jbe@284: CREATE TRIGGER "non_voter_deletes_direct_voter"
jbe@284:   AFTER INSERT OR UPDATE ON "non_voter"
jbe@284:   FOR EACH ROW EXECUTE PROCEDURE
jbe@284:   "non_voter_deletes_direct_voter_trigger"();
jbe@284: 
jbe@284: COMMENT ON FUNCTION "non_voter_deletes_direct_voter_trigger"()     IS 'Implementation of trigger "non_voter_deletes_direct_voter" on table "non_voter"';
jbe@284: COMMENT ON TRIGGER "non_voter_deletes_direct_voter" ON "non_voter" IS 'An entry in the "non_voter" table deletes an entry in the "direct_voter" table (and vice versa due to trigger "direct_voter_deletes_non_voter" on table "direct_voter")';
jbe@284: 
jbe@284: CREATE FUNCTION "direct_voter_deletes_non_voter_trigger"()
jbe@284:   RETURNS TRIGGER
jbe@284:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@284:     BEGIN
jbe@284:       DELETE FROM "non_voter"
jbe@284:         WHERE "issue_id" = NEW."issue_id" AND "member_id" = NEW."member_id";
jbe@284:       RETURN NULL;
jbe@284:     END;
jbe@284:   $$;
jbe@284: 
jbe@284: CREATE TRIGGER "direct_voter_deletes_non_voter"
jbe@284:   AFTER INSERT OR UPDATE ON "direct_voter"
jbe@284:   FOR EACH ROW EXECUTE PROCEDURE
jbe@284:   "direct_voter_deletes_non_voter_trigger"();
jbe@284: 
jbe@284: COMMENT ON FUNCTION "direct_voter_deletes_non_voter_trigger"()        IS 'Implementation of trigger "direct_voter_deletes_non_voter" on table "direct_voter"';
jbe@284: COMMENT ON TRIGGER "direct_voter_deletes_non_voter" ON "direct_voter" IS 'An entry in the "direct_voter" table deletes an entry in the "non_voter" table (and vice versa due to trigger "non_voter_deletes_direct_voter" on table "non_voter")';
jbe@284: 
jbe@285: CREATE FUNCTION "voter_comment_fields_only_set_when_voter_comment_is_set_trigger"()
jbe@285:   RETURNS TRIGGER
jbe@285:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@285:     BEGIN
jbe@285:       IF NEW."comment" ISNULL THEN
jbe@285:         NEW."comment_changed" := NULL;
jbe@285:         NEW."formatting_engine" := NULL;
jbe@285:       END IF;
jbe@285:       RETURN NEW;
jbe@285:     END;
jbe@285:   $$;
jbe@285: 
jbe@285: CREATE TRIGGER "voter_comment_fields_only_set_when_voter_comment_is_set"
jbe@285:   BEFORE INSERT OR UPDATE ON "direct_voter"
jbe@285:   FOR EACH ROW EXECUTE PROCEDURE
jbe@285:   "voter_comment_fields_only_set_when_voter_comment_is_set_trigger"();
jbe@285: 
jbe@285: COMMENT ON FUNCTION "voter_comment_fields_only_set_when_voter_comment_is_set_trigger"() IS 'Implementation of trigger "voter_comment_fields_only_set_when_voter_comment_is_set" ON table "direct_voter"';
jbe@285: COMMENT ON TRIGGER "voter_comment_fields_only_set_when_voter_comment_is_set" ON "direct_voter" IS 'If "comment" is set to NULL, then other comment related fields are also set to NULL.';
jbe@285: 
jbe@262: CREATE OR REPLACE FUNCTION "freeze_after_snapshot"
jbe@262:   ( "issue_id_p" "issue"."id"%TYPE )
jbe@262:   RETURNS VOID
jbe@262:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@262:     DECLARE
jbe@262:       "issue_row"      "issue"%ROWTYPE;
jbe@262:       "policy_row"     "policy"%ROWTYPE;
jbe@262:       "initiative_row" "initiative"%ROWTYPE;
jbe@262:     BEGIN
jbe@262:       SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
jbe@262:       SELECT * INTO "policy_row"
jbe@262:         FROM "policy" WHERE "id" = "issue_row"."policy_id";
jbe@262:       PERFORM "set_snapshot_event"("issue_id_p", 'full_freeze');
jbe@262:       FOR "initiative_row" IN
jbe@262:         SELECT * FROM "initiative"
jbe@262:         WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
jbe@262:       LOOP
jbe@262:         IF
jbe@262:           "initiative_row"."polling" OR (
jbe@262:             "initiative_row"."satisfied_supporter_count" > 0 AND
jbe@262:             "initiative_row"."satisfied_supporter_count" *
jbe@262:             "policy_row"."initiative_quorum_den" >=
jbe@262:             "issue_row"."population" * "policy_row"."initiative_quorum_num"
jbe@262:           )
jbe@262:         THEN
jbe@262:           UPDATE "initiative" SET "admitted" = TRUE
jbe@262:             WHERE "id" = "initiative_row"."id";
jbe@262:         ELSE
jbe@262:           UPDATE "initiative" SET "admitted" = FALSE
jbe@262:             WHERE "id" = "initiative_row"."id";
jbe@262:         END IF;
jbe@262:       END LOOP;
jbe@262:       IF EXISTS (
jbe@262:         SELECT NULL FROM "initiative"
jbe@262:         WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE
jbe@262:       ) THEN
jbe@262:         UPDATE "issue" SET
jbe@262:           "state"        = 'voting',
jbe@262:           "accepted"     = coalesce("accepted", now()),
jbe@262:           "half_frozen"  = coalesce("half_frozen", now()),
jbe@262:           "fully_frozen" = now()
jbe@262:           WHERE "id" = "issue_id_p";
jbe@262:       ELSE
jbe@262:         UPDATE "issue" SET
jbe@262:           "state"           = 'canceled_no_initiative_admitted',
jbe@262:           "accepted"        = coalesce("accepted", now()),
jbe@262:           "half_frozen"     = coalesce("half_frozen", now()),
jbe@262:           "fully_frozen"    = now(),
jbe@262:           "closed"          = now(),
jbe@262:           "ranks_available" = TRUE
jbe@262:           WHERE "id" = "issue_id_p";
jbe@262:         -- NOTE: The following DELETE statements have effect only when
jbe@262:         --       issue state has been manipulated
jbe@262:         DELETE FROM "direct_voter"     WHERE "issue_id" = "issue_id_p";
jbe@262:         DELETE FROM "delegating_voter" WHERE "issue_id" = "issue_id_p";
jbe@262:         DELETE FROM "battle"           WHERE "issue_id" = "issue_id_p";
jbe@262:       END IF;
jbe@262:       RETURN;
jbe@262:     END;
jbe@262:   $$;
jbe@262: 
jbe@262: CREATE OR REPLACE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE)
jbe@262:   RETURNS VOID
jbe@262:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@262:     DECLARE
jbe@262:       "issue_row" "issue"%ROWTYPE;
jbe@262:     BEGIN
jbe@262:       SELECT * INTO "issue_row"
jbe@262:         FROM "issue" WHERE "id" = "issue_id_p"
jbe@262:         FOR UPDATE;
jbe@262:       IF "issue_row"."cleaned" ISNULL THEN
jbe@262:         UPDATE "issue" SET
jbe@262:           "state"           = 'voting',
jbe@262:           "closed"          = NULL,
jbe@262:           "ranks_available" = FALSE
jbe@262:           WHERE "id" = "issue_id_p";
jbe@262:         DELETE FROM "delegating_voter"
jbe@262:           WHERE "issue_id" = "issue_id_p";
jbe@262:         DELETE FROM "direct_voter"
jbe@262:           WHERE "issue_id" = "issue_id_p";
jbe@262:         DELETE FROM "delegating_interest_snapshot"
jbe@262:           WHERE "issue_id" = "issue_id_p";
jbe@262:         DELETE FROM "direct_interest_snapshot"
jbe@262:           WHERE "issue_id" = "issue_id_p";
jbe@262:         DELETE FROM "delegating_population_snapshot"
jbe@262:           WHERE "issue_id" = "issue_id_p";
jbe@262:         DELETE FROM "direct_population_snapshot"
jbe@262:           WHERE "issue_id" = "issue_id_p";
jbe@262:         DELETE FROM "non_voter"
jbe@262:           WHERE "issue_id" = "issue_id_p";
jbe@262:         DELETE FROM "delegation"
jbe@262:           WHERE "issue_id" = "issue_id_p";
jbe@262:         DELETE FROM "supporter"
jbe@262:           WHERE "issue_id" = "issue_id_p";
jbe@262:         UPDATE "issue" SET
jbe@262:           "state"           = "issue_row"."state",
jbe@262:           "closed"          = "issue_row"."closed",
jbe@262:           "ranks_available" = "issue_row"."ranks_available",
jbe@262:           "cleaned"         = now()
jbe@262:           WHERE "id" = "issue_id_p";
jbe@262:       END IF;
jbe@262:       RETURN;
jbe@262:     END;
jbe@262:   $$;
jbe@262: 
jbe@285: CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
jbe@285:   RETURNS VOID
jbe@285:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@285:     DECLARE
jbe@285:       "area_id_v"   "area"."id"%TYPE;
jbe@285:       "unit_id_v"   "unit"."id"%TYPE;
jbe@285:       "member_id_v" "member"."id"%TYPE;
jbe@285:     BEGIN
jbe@285:       PERFORM "lock_issue"("issue_id_p");
jbe@285:       SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";
jbe@285:       SELECT "unit_id" INTO "unit_id_v" FROM "area"  WHERE "id" = "area_id_v";
jbe@285:       -- delete timestamp of voting comment:
jbe@285:       UPDATE "direct_voter" SET "comment_changed" = NULL
jbe@285:         WHERE "issue_id" = "issue_id_p";
jbe@285:       -- delete delegating votes (in cases of manual reset of issue state):
jbe@285:       DELETE FROM "delegating_voter"
jbe@285:         WHERE "issue_id" = "issue_id_p";
jbe@285:       -- delete votes from non-privileged voters:
jbe@285:       DELETE FROM "direct_voter"
jbe@285:         USING (
jbe@285:           SELECT
jbe@285:             "direct_voter"."member_id"
jbe@285:           FROM "direct_voter"
jbe@285:           JOIN "member" ON "direct_voter"."member_id" = "member"."id"
jbe@285:           LEFT JOIN "privilege"
jbe@285:           ON "privilege"."unit_id" = "unit_id_v"
jbe@285:           AND "privilege"."member_id" = "direct_voter"."member_id"
jbe@285:           WHERE "direct_voter"."issue_id" = "issue_id_p" AND (
jbe@285:             "member"."active" = FALSE OR
jbe@285:             "privilege"."voting_right" ISNULL OR
jbe@285:             "privilege"."voting_right" = FALSE
jbe@285:           )
jbe@285:         ) AS "subquery"
jbe@285:         WHERE "direct_voter"."issue_id" = "issue_id_p"
jbe@285:         AND "direct_voter"."member_id" = "subquery"."member_id";
jbe@285:       -- consider delegations:
jbe@285:       UPDATE "direct_voter" SET "weight" = 1
jbe@285:         WHERE "issue_id" = "issue_id_p";
jbe@285:       PERFORM "add_vote_delegations"("issue_id_p");
jbe@285:       -- set voter count and mark issue as being calculated:
jbe@285:       UPDATE "issue" SET
jbe@285:         "state"  = 'calculation',
jbe@285:         "closed" = now(),
jbe@285:         "voter_count" = (
jbe@285:           SELECT coalesce(sum("weight"), 0)
jbe@285:           FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
jbe@285:         )
jbe@285:         WHERE "id" = "issue_id_p";
jbe@285:       -- materialize battle_view:
jbe@285:       -- NOTE: "closed" column of issue must be set at this point
jbe@285:       DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
jbe@285:       INSERT INTO "battle" (
jbe@285:         "issue_id",
jbe@285:         "winning_initiative_id", "losing_initiative_id",
jbe@285:         "count"
jbe@285:       ) SELECT
jbe@285:         "issue_id",
jbe@285:         "winning_initiative_id", "losing_initiative_id",
jbe@285:         "count"
jbe@285:         FROM "battle_view" WHERE "issue_id" = "issue_id_p";
jbe@285:       -- copy "positive_votes" and "negative_votes" from "battle" table:
jbe@285:       UPDATE "initiative" SET
jbe@285:         "positive_votes" = "battle_win"."count",
jbe@285:         "negative_votes" = "battle_lose"."count"
jbe@285:         FROM "battle" AS "battle_win", "battle" AS "battle_lose"
jbe@285:         WHERE
jbe@285:           "battle_win"."issue_id" = "issue_id_p" AND
jbe@285:           "battle_win"."winning_initiative_id" = "initiative"."id" AND
jbe@285:           "battle_win"."losing_initiative_id" ISNULL AND
jbe@285:           "battle_lose"."issue_id" = "issue_id_p" AND
jbe@285:           "battle_lose"."losing_initiative_id" = "initiative"."id" AND
jbe@285:           "battle_lose"."winning_initiative_id" ISNULL;
jbe@285:     END;
jbe@285:   $$;
jbe@285: 
jbe@283: COMMENT ON FUNCTION "delete_private_data"() IS 'Used by lf_export script. DO NOT USE on productive database, but only on a copy! This function deletes all data which should not be publicly available, and can be used to create a database dump for publication. See source code to see which data is deleted. If you need a different behaviour, copy this function and modify lf_export accordingly, to avoid data-leaks after updating.';
jbe@283: 
jbe@262: COMMIT;