jbe@453: BEGIN;
jbe@453: 
jbe@453: CREATE OR REPLACE VIEW "liquid_feedback_version" AS
jbe@453:   SELECT * FROM (VALUES ('3.1.0', 3, 1, 0))
jbe@453:   AS "subquery"("string", "major", "minor", "revision");
jbe@453: 
jbe@453: ALTER TABLE "member" DROP CONSTRAINT "authority_requires_uid_and_vice_versa";
jbe@453: ALTER TABLE "member" ADD CONSTRAINT "authority_requires_uid_and_vice_versa" CHECK (
jbe@453:   ("authority" NOTNULL) = ("authority_uid" NOTNULL) );
jbe@453: 
jbe@453: DROP TABLE "member_application";
jbe@453: DROP TYPE "application_access_level";
jbe@453: 
jbe@456: ALTER TABLE "policy" ADD COLUMN "min_admission_time" INTERVAL;
jbe@453: ALTER TABLE "policy" RENAME COLUMN "admission_time" TO "max_admission_time";
jbe@453: 
jbe@456: ALTER TABLE "policy" DROP CONSTRAINT "timing";  -- re-added after UPDATE
jbe@453: 
jbe@453: ALTER TABLE "policy" DROP CONSTRAINT "issue_quorum_if_and_only_if_not_polling";
jbe@453: ALTER TABLE "policy" ADD CONSTRAINT "issue_quorum_if_and_only_if_not_polling" CHECK (
jbe@453:   "polling" = ("issue_quorum_num" ISNULL) AND
jbe@453:   "polling" = ("issue_quorum_den" ISNULL) );
jbe@453: 
jbe@453: COMMENT ON COLUMN "policy"."polling" IS 'TRUE = special policy for non-user-generated issues without issue quorum, where certain initiatives (those having the "polling" flag set) do not need to pass the initiative quorum; "min_admission_time" and "max_admission_time" MUST be set to NULL, the other timings may be set to NULL altogether, allowing individual timing for those issues';
jbe@453: COMMENT ON COLUMN "policy"."min_admission_time" IS 'Minimum duration of issue state ''admission''; Minimum time an issue stays open';
jbe@453: 
jbe@456: ALTER TABLE "issue" ADD COLUMN "min_admission_time" INTERVAL;
jbe@453: ALTER TABLE "issue" RENAME COLUMN "admission_time" TO "max_admission_time";
jbe@453: 
jbe@456: ALTER TABLE "issue" DROP CONSTRAINT "admission_time_not_null_unless_instantly_accepted";  -- re-added after UPDATE
jbe@453: 
jbe@453: ALTER TABLE "issue" DROP CONSTRAINT "set_both_or_none_of_snapshot_and_latest_snapshot_event";
jbe@453: ALTER TABLE "issue" ADD CONSTRAINT "set_both_or_none_of_snapshot_and_latest_snapshot_event" CHECK (
jbe@453:   (("snapshot" NOTNULL) = ("latest_snapshot_event" NOTNULL)) );
jbe@453: 
jbe@453: COMMENT ON COLUMN "issue"."closed" IS 'Point in time, when "max_admission_time" or "voting_time" have elapsed, and issue is no longer active; Frontends must ensure that for closed issues additionally to the restrictions for half_frozen and fully_frozen issues a) no voter is added or removed to/from the direct_voter table, b) no votes are added, modified or removed.';
jbe@453: COMMENT ON COLUMN "issue"."min_admission_time" IS 'Copied from "policy" table at creation of issue';
jbe@453: 
jbe@453: DROP TRIGGER "update_text_search_data" ON "initiative";
jbe@453: ALTER TABLE "initiative" DROP COLUMN "discussion_url";
jbe@453: 
jbe@453: ALTER TABLE "initiative" DROP CONSTRAINT "all_or_none_of_revoked_and_revoked_by_member_id_must_be_null";
jbe@453: ALTER TABLE "initiative" ADD CONSTRAINT "all_or_none_of_revoked_and_revoked_by_member_id_must_be_null" CHECK (
jbe@453:   ("revoked" NOTNULL) = ("revoked_by_member_id" NOTNULL) );
jbe@453: 
jbe@453:  CREATE TRIGGER "update_text_search_data"
jbe@453:    BEFORE INSERT OR UPDATE ON "initiative"
jbe@453:    FOR EACH ROW EXECUTE PROCEDURE
jbe@453:    tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "name");
jbe@453: 
jbe@453: ALTER TABLE "event" DROP CONSTRAINT "null_constraints_for_issue_state_changed";
jbe@453: ALTER TABLE "event" DROP CONSTRAINT "null_constraints_for_initiative_creation_or_revocation_or_new_draft";
jbe@453: ALTER TABLE "event" DROP CONSTRAINT "null_constraints_for_suggestion_creation";
jbe@453: 
jbe@453: ALTER TABLE "event" ADD CONSTRAINT "null_constr_for_issue_state_changed" CHECK (
jbe@453:   "event" != 'issue_state_changed' OR (
jbe@453:     "member_id"     ISNULL  AND
jbe@453:     "issue_id"      NOTNULL AND
jbe@453:     "state"         NOTNULL AND
jbe@453:     "initiative_id" ISNULL  AND
jbe@453:     "draft_id"      ISNULL  AND
jbe@453:     "suggestion_id" ISNULL  ) );
jbe@453: ALTER TABLE "event" ADD CONSTRAINT "null_constr_for_initiative_creation_or_revocation_or_new_draft" CHECK (
jbe@453:   "event" NOT IN (
jbe@453:     'initiative_created_in_new_issue',
jbe@453:     'initiative_created_in_existing_issue',
jbe@453:     'initiative_revoked',
jbe@453:     'new_draft_created'
jbe@453:   ) OR (
jbe@453:     "member_id"     NOTNULL AND
jbe@453:     "issue_id"      NOTNULL AND
jbe@453:     "state"         NOTNULL AND
jbe@453:     "initiative_id" NOTNULL AND
jbe@453:     "draft_id"      NOTNULL AND
jbe@453:     "suggestion_id" ISNULL  ) );
jbe@453: ALTER TABLE "event" ADD CONSTRAINT "null_constr_for_suggestion_creation" CHECK (
jbe@453:   "event" != 'suggestion_created' OR (
jbe@453:     "member_id"     NOTNULL AND
jbe@453:     "issue_id"      NOTNULL AND
jbe@453:     "state"         NOTNULL AND
jbe@453:     "initiative_id" NOTNULL AND
jbe@453:     "draft_id"      ISNULL  AND
jbe@453:     "suggestion_id" NOTNULL ) );
jbe@453: 
jbe@453: CREATE OR REPLACE FUNCTION "copy_timings_trigger"()
jbe@453:   RETURNS TRIGGER
jbe@453:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@453:     DECLARE
jbe@453:       "policy_row" "policy"%ROWTYPE;
jbe@453:     BEGIN
jbe@453:       SELECT * INTO "policy_row" FROM "policy"
jbe@453:         WHERE "id" = NEW."policy_id";
jbe@453:       IF NEW."min_admission_time" ISNULL THEN
jbe@453:         NEW."min_admission_time" := "policy_row"."min_admission_time";
jbe@453:       END IF;
jbe@453:       IF NEW."max_admission_time" ISNULL THEN
jbe@453:         NEW."max_admission_time" := "policy_row"."max_admission_time";
jbe@453:       END IF;
jbe@453:       IF NEW."discussion_time" ISNULL THEN
jbe@453:         NEW."discussion_time" := "policy_row"."discussion_time";
jbe@453:       END IF;
jbe@453:       IF NEW."verification_time" ISNULL THEN
jbe@453:         NEW."verification_time" := "policy_row"."verification_time";
jbe@453:       END IF;
jbe@453:       IF NEW."voting_time" ISNULL THEN
jbe@453:         NEW."voting_time" := "policy_row"."voting_time";
jbe@453:       END IF;
jbe@453:       RETURN NEW;
jbe@453:     END;
jbe@453:   $$;
jbe@453: 
jbe@453: CREATE OR REPLACE FUNCTION "check_issue"
jbe@453:   ( "issue_id_p" "issue"."id"%TYPE,
jbe@453:     "persist"    "check_issue_persistence" )
jbe@453:   RETURNS "check_issue_persistence"
jbe@453:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@453:     DECLARE
jbe@453:       "issue_row"      "issue"%ROWTYPE;
jbe@453:       "policy_row"     "policy"%ROWTYPE;
jbe@453:       "initiative_row" "initiative"%ROWTYPE;
jbe@453:       "state_v"        "issue_state";
jbe@453:     BEGIN
jbe@453:       PERFORM "require_transaction_isolation"();
jbe@453:       IF "persist" ISNULL THEN
jbe@453:         SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
jbe@453:           FOR UPDATE;
jbe@453:         IF "issue_row"."closed" NOTNULL THEN
jbe@453:           RETURN NULL;
jbe@453:         END IF;
jbe@453:         "persist"."state" := "issue_row"."state";
jbe@453:         IF
jbe@453:           ( "issue_row"."state" = 'admission' AND now() >=
jbe@453:             "issue_row"."created" + "issue_row"."max_admission_time" ) OR
jbe@453:           ( "issue_row"."state" = 'discussion' AND now() >=
jbe@453:             "issue_row"."accepted" + "issue_row"."discussion_time" ) OR
jbe@453:           ( "issue_row"."state" = 'verification' AND now() >=
jbe@453:             "issue_row"."half_frozen" + "issue_row"."verification_time" ) OR
jbe@453:           ( "issue_row"."state" = 'voting' AND now() >=
jbe@453:             "issue_row"."fully_frozen" + "issue_row"."voting_time" )
jbe@453:         THEN
jbe@453:           "persist"."phase_finished" := TRUE;
jbe@453:         ELSE
jbe@453:           "persist"."phase_finished" := FALSE;
jbe@453:         END IF;
jbe@453:         IF
jbe@453:           NOT EXISTS (
jbe@453:             -- all initiatives are revoked
jbe@453:             SELECT NULL FROM "initiative"
jbe@453:             WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
jbe@453:           ) AND (
jbe@453:             -- and issue has not been accepted yet
jbe@453:             "persist"."state" = 'admission' OR
jbe@453:             -- or verification time has elapsed
jbe@453:             ( "persist"."state" = 'verification' AND
jbe@453:               "persist"."phase_finished" ) OR
jbe@453:             -- or no initiatives have been revoked lately
jbe@453:             NOT EXISTS (
jbe@453:               SELECT NULL FROM "initiative"
jbe@453:               WHERE "issue_id" = "issue_id_p"
jbe@453:               AND now() < "revoked" + "issue_row"."verification_time"
jbe@453:             )
jbe@453:           )
jbe@453:         THEN
jbe@453:           "persist"."issue_revoked" := TRUE;
jbe@453:         ELSE
jbe@453:           "persist"."issue_revoked" := FALSE;
jbe@453:         END IF;
jbe@453:         IF "persist"."phase_finished" OR "persist"."issue_revoked" THEN
jbe@453:           UPDATE "issue" SET "phase_finished" = now()
jbe@453:             WHERE "id" = "issue_row"."id";
jbe@453:           RETURN "persist";
jbe@453:         ELSIF
jbe@453:           "persist"."state" IN ('admission', 'discussion', 'verification')
jbe@453:         THEN
jbe@453:           RETURN "persist";
jbe@453:         ELSE
jbe@453:           RETURN NULL;
jbe@453:         END IF;
jbe@453:       END IF;
jbe@453:       IF
jbe@453:         "persist"."state" IN ('admission', 'discussion', 'verification') AND
jbe@453:         coalesce("persist"."snapshot_created", FALSE) = FALSE
jbe@453:       THEN
jbe@453:         PERFORM "create_snapshot"("issue_id_p");
jbe@453:         "persist"."snapshot_created" = TRUE;
jbe@453:         IF "persist"."phase_finished" THEN
jbe@453:           IF "persist"."state" = 'admission' THEN
jbe@453:             PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
jbe@453:           ELSIF "persist"."state" = 'discussion' THEN
jbe@453:             PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze');
jbe@453:           ELSIF "persist"."state" = 'verification' THEN
jbe@453:             PERFORM "set_snapshot_event"("issue_id_p", 'full_freeze');
jbe@453:             SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
jbe@453:             SELECT * INTO "policy_row" FROM "policy"
jbe@453:               WHERE "id" = "issue_row"."policy_id";
jbe@453:             FOR "initiative_row" IN
jbe@453:               SELECT * FROM "initiative"
jbe@453:               WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
jbe@453:               FOR UPDATE
jbe@453:             LOOP
jbe@453:               IF
jbe@453:                 "initiative_row"."polling" OR (
jbe@453:                   "initiative_row"."satisfied_supporter_count" > 0 AND
jbe@453:                   "initiative_row"."satisfied_supporter_count" *
jbe@453:                   "policy_row"."initiative_quorum_den" >=
jbe@453:                   "issue_row"."population" * "policy_row"."initiative_quorum_num"
jbe@453:                 )
jbe@453:               THEN
jbe@453:                 UPDATE "initiative" SET "admitted" = TRUE
jbe@453:                   WHERE "id" = "initiative_row"."id";
jbe@453:               ELSE
jbe@453:                 UPDATE "initiative" SET "admitted" = FALSE
jbe@453:                   WHERE "id" = "initiative_row"."id";
jbe@453:               END IF;
jbe@453:             END LOOP;
jbe@453:           END IF;
jbe@453:         END IF;
jbe@453:         RETURN "persist";
jbe@453:       END IF;
jbe@453:       IF
jbe@453:         "persist"."state" IN ('admission', 'discussion', 'verification') AND
jbe@453:         coalesce("persist"."harmonic_weights_set", FALSE) = FALSE
jbe@453:       THEN
jbe@453:         PERFORM "set_harmonic_initiative_weights"("issue_id_p");
jbe@453:         "persist"."harmonic_weights_set" = TRUE;
jbe@453:         IF
jbe@453:           "persist"."phase_finished" OR
jbe@453:           "persist"."issue_revoked" OR
jbe@453:           "persist"."state" = 'admission'
jbe@453:         THEN
jbe@453:           RETURN "persist";
jbe@453:         ELSE
jbe@453:           RETURN NULL;
jbe@453:         END IF;
jbe@453:       END IF;
jbe@453:       IF "persist"."issue_revoked" THEN
jbe@453:         IF "persist"."state" = 'admission' THEN
jbe@453:           "state_v" := 'canceled_revoked_before_accepted';
jbe@453:         ELSIF "persist"."state" = 'discussion' THEN
jbe@453:           "state_v" := 'canceled_after_revocation_during_discussion';
jbe@453:         ELSIF "persist"."state" = 'verification' THEN
jbe@453:           "state_v" := 'canceled_after_revocation_during_verification';
jbe@453:         END IF;
jbe@453:         UPDATE "issue" SET
jbe@453:           "state"          = "state_v",
jbe@453:           "closed"         = "phase_finished",
jbe@453:           "phase_finished" = NULL
jbe@453:           WHERE "id" = "issue_id_p";
jbe@453:         RETURN NULL;
jbe@453:       END IF;
jbe@453:       IF "persist"."state" = 'admission' THEN
jbe@453:         SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
jbe@453:           FOR UPDATE;
jbe@453:         SELECT * INTO "policy_row"
jbe@453:           FROM "policy" WHERE "id" = "issue_row"."policy_id";
jbe@453:         IF 
jbe@453:           ( now() >=
jbe@453:             "issue_row"."created" + "issue_row"."min_admission_time" ) AND
jbe@453:           EXISTS (
jbe@453:             SELECT NULL FROM "initiative"
jbe@453:             WHERE "issue_id" = "issue_id_p"
jbe@453:             AND "supporter_count" > 0
jbe@453:             AND "supporter_count" * "policy_row"."issue_quorum_den"
jbe@453:             >= "issue_row"."population" * "policy_row"."issue_quorum_num"
jbe@453:           )
jbe@453:         THEN
jbe@453:           UPDATE "issue" SET
jbe@453:             "state"          = 'discussion',
jbe@453:             "accepted"       = coalesce("phase_finished", now()),
jbe@453:             "phase_finished" = NULL
jbe@453:             WHERE "id" = "issue_id_p";
jbe@453:         ELSIF "issue_row"."phase_finished" NOTNULL THEN
jbe@453:           UPDATE "issue" SET
jbe@453:             "state"          = 'canceled_issue_not_accepted',
jbe@453:             "closed"         = "phase_finished",
jbe@453:             "phase_finished" = NULL
jbe@453:             WHERE "id" = "issue_id_p";
jbe@453:         END IF;
jbe@453:         RETURN NULL;
jbe@453:       END IF;
jbe@453:       IF "persist"."phase_finished" THEN
jbe@453:         IF "persist"."state" = 'discussion' THEN
jbe@453:           UPDATE "issue" SET
jbe@453:             "state"          = 'verification',
jbe@453:             "half_frozen"    = "phase_finished",
jbe@453:             "phase_finished" = NULL
jbe@453:             WHERE "id" = "issue_id_p";
jbe@453:           RETURN NULL;
jbe@453:         END IF;
jbe@453:         IF "persist"."state" = 'verification' THEN
jbe@453:           SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
jbe@453:             FOR UPDATE;
jbe@453:           SELECT * INTO "policy_row" FROM "policy"
jbe@453:             WHERE "id" = "issue_row"."policy_id";
jbe@453:           IF EXISTS (
jbe@453:             SELECT NULL FROM "initiative"
jbe@453:             WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE
jbe@453:           ) THEN
jbe@453:             UPDATE "issue" SET
jbe@453:               "state"          = 'voting',
jbe@453:               "fully_frozen"   = "phase_finished",
jbe@453:               "phase_finished" = NULL
jbe@453:               WHERE "id" = "issue_id_p";
jbe@453:           ELSE
jbe@453:             UPDATE "issue" SET
jbe@453:               "state"          = 'canceled_no_initiative_admitted',
jbe@453:               "fully_frozen"   = "phase_finished",
jbe@453:               "closed"         = "phase_finished",
jbe@453:               "phase_finished" = NULL
jbe@453:               WHERE "id" = "issue_id_p";
jbe@453:             -- NOTE: The following DELETE statements have effect only when
jbe@453:             --       issue state has been manipulated
jbe@453:             DELETE FROM "direct_voter"     WHERE "issue_id" = "issue_id_p";
jbe@453:             DELETE FROM "delegating_voter" WHERE "issue_id" = "issue_id_p";
jbe@453:             DELETE FROM "battle"           WHERE "issue_id" = "issue_id_p";
jbe@453:           END IF;
jbe@453:           RETURN NULL;
jbe@453:         END IF;
jbe@453:         IF "persist"."state" = 'voting' THEN
jbe@453:           IF coalesce("persist"."closed_voting", FALSE) = FALSE THEN
jbe@453:             PERFORM "close_voting"("issue_id_p");
jbe@453:             "persist"."closed_voting" = TRUE;
jbe@453:             RETURN "persist";
jbe@453:           END IF;
jbe@453:           PERFORM "calculate_ranks"("issue_id_p");
jbe@453:           RETURN NULL;
jbe@453:         END IF;
jbe@453:       END IF;
jbe@453:       RAISE WARNING 'should not happen';
jbe@453:       RETURN NULL;
jbe@453:     END;
jbe@453:   $$;
jbe@453: 
jbe@456: -- temporarily drop deferred trigger to allow UPDATE with subsequent ALTER TABLE:
jbe@456: DROP TRIGGER "issue_requires_first_initiative" ON "issue";
jbe@456: 
jbe@456: -- set "min_admission_time" values where appropriate:
jbe@456: UPDATE "policy" SET "min_admission_time" = '0' WHERE "max_admission_time" NOTNULL;
jbe@456: UPDATE "issue" SET "min_admission_time" = '0' WHERE "max_admission_time" NOTNULL;
jbe@456: 
jbe@456: -- add constraints related to "min_admission_time" after UPDATE:
jbe@456: ALTER TABLE "policy" ADD CONSTRAINT "timing" CHECK (
jbe@456:   ( "polling" = FALSE AND
jbe@456:     "min_admission_time" NOTNULL AND "max_admission_time" NOTNULL AND
jbe@456:     "min_admission_time" <= "max_admission_time" AND
jbe@456:     "discussion_time" NOTNULL AND
jbe@456:     "verification_time" NOTNULL AND
jbe@456:     "voting_time" NOTNULL ) OR
jbe@456:   ( "polling" = TRUE AND
jbe@456:     "min_admission_time" ISNULL AND "max_admission_time" ISNULL AND
jbe@456:     "discussion_time" NOTNULL AND
jbe@456:     "verification_time" NOTNULL AND
jbe@456:     "voting_time" NOTNULL ) OR
jbe@456:   ( "polling" = TRUE AND
jbe@456:     "min_admission_time" ISNULL AND "max_admission_time" ISNULL AND
jbe@456:     "discussion_time" ISNULL AND
jbe@456:     "verification_time" ISNULL AND
jbe@456:     "voting_time" ISNULL ) );
jbe@456: 
jbe@456: ALTER TABLE "issue" ADD CONSTRAINT "admission_time_not_null_unless_instantly_accepted" CHECK (
jbe@456:   ("min_admission_time" NOTNULL) = ("max_admission_time" NOTNULL) AND
jbe@456:   ("min_admission_time" NOTNULL OR ("accepted" NOTNULL AND "accepted" = "created")) );
jbe@456: 
jbe@456: -- re-add temporarily dropped trigger:
jbe@456: CREATE CONSTRAINT TRIGGER "issue_requires_first_initiative"
jbe@456:   AFTER INSERT OR UPDATE ON "issue" DEFERRABLE INITIALLY DEFERRED
jbe@456:   FOR EACH ROW EXECUTE PROCEDURE
jbe@456:   "issue_requires_first_initiative_trigger"();
jbe@456: COMMENT ON TRIGGER "issue_requires_first_initiative" ON "issue" IS 'Ensure that new issues have at least one initiative';
jbe@456: 
jbe@453: COMMIT;