liquid_feedback_core

changeset 456:a9c4521466e6 v3.1.0

Fixed v3.1.0 update script to properly update policies/issues with "polling" set to TRUE
author jbe
date Fri Dec 04 14:02:19 2015 +0100 (2015-12-04)
parents b858e90f6cdd
children ca21a3f49e4c 0efd132b3f6d
files update/core-update.v3.0.5-v3.1.0.sql
line diff
     1.1 --- a/update/core-update.v3.0.5-v3.1.0.sql	Thu Dec 03 22:32:05 2015 +0100
     1.2 +++ b/update/core-update.v3.0.5-v3.1.0.sql	Fri Dec 04 14:02:19 2015 +0100
     1.3 @@ -11,28 +11,10 @@
     1.4  DROP TABLE "member_application";
     1.5  DROP TYPE "application_access_level";
     1.6  
     1.7 -ALTER TABLE "policy" ADD COLUMN "min_admission_time" INTERVAL DEFAULT '0';
     1.8 -ALTER TABLE "policy" ALTER COLUMN "min_admission_time" DROP DEFAULT;
     1.9 +ALTER TABLE "policy" ADD COLUMN "min_admission_time" INTERVAL;
    1.10  ALTER TABLE "policy" RENAME COLUMN "admission_time" TO "max_admission_time";
    1.11  
    1.12 -ALTER TABLE "policy" DROP CONSTRAINT "timing";
    1.13 -ALTER TABLE "policy" ADD CONSTRAINT "timing" CHECK (
    1.14 -  ( "polling" = FALSE AND
    1.15 -    "min_admission_time" NOTNULL AND "max_admission_time" NOTNULL AND
    1.16 -    "min_admission_time" <= "max_admission_time" AND
    1.17 -    "discussion_time" NOTNULL AND
    1.18 -    "verification_time" NOTNULL AND
    1.19 -    "voting_time" NOTNULL ) OR
    1.20 -  ( "polling" = TRUE AND
    1.21 -    "min_admission_time" ISNULL AND "max_admission_time" ISNULL AND
    1.22 -    "discussion_time" NOTNULL AND
    1.23 -    "verification_time" NOTNULL AND
    1.24 -    "voting_time" NOTNULL ) OR
    1.25 -  ( "polling" = TRUE AND
    1.26 -    "min_admission_time" ISNULL AND "max_admission_time" ISNULL AND
    1.27 -    "discussion_time" ISNULL AND
    1.28 -    "verification_time" ISNULL AND
    1.29 -    "voting_time" ISNULL ) );
    1.30 +ALTER TABLE "policy" DROP CONSTRAINT "timing";  -- re-added after UPDATE
    1.31  
    1.32  ALTER TABLE "policy" DROP CONSTRAINT "issue_quorum_if_and_only_if_not_polling";
    1.33  ALTER TABLE "policy" ADD CONSTRAINT "issue_quorum_if_and_only_if_not_polling" CHECK (
    1.34 @@ -42,14 +24,10 @@
    1.35  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';
    1.36  COMMENT ON COLUMN "policy"."min_admission_time" IS 'Minimum duration of issue state ''admission''; Minimum time an issue stays open';
    1.37  
    1.38 -ALTER TABLE "issue" ADD COLUMN "min_admission_time" INTERVAL DEFAULT '0';
    1.39 -ALTER TABLE "issue" ALTER COLUMN "min_admission_time" DROP DEFAULT;
    1.40 +ALTER TABLE "issue" ADD COLUMN "min_admission_time" INTERVAL;
    1.41  ALTER TABLE "issue" RENAME COLUMN "admission_time" TO "max_admission_time";
    1.42  
    1.43 -ALTER TABLE "issue" DROP CONSTRAINT "admission_time_not_null_unless_instantly_accepted";
    1.44 -ALTER TABLE "issue" ADD CONSTRAINT "admission_time_not_null_unless_instantly_accepted" CHECK (
    1.45 -  ("min_admission_time" NOTNULL) = ("max_admission_time" NOTNULL) AND
    1.46 -  ("min_admission_time" NOTNULL OR ("accepted" NOTNULL AND "accepted" = "created")) );
    1.47 +ALTER TABLE "issue" DROP CONSTRAINT "admission_time_not_null_unless_instantly_accepted";  -- re-added after UPDATE
    1.48  
    1.49  ALTER TABLE "issue" DROP CONSTRAINT "set_both_or_none_of_snapshot_and_latest_snapshot_event";
    1.50  ALTER TABLE "issue" ADD CONSTRAINT "set_both_or_none_of_snapshot_and_latest_snapshot_event" CHECK (
    1.51 @@ -353,4 +331,41 @@
    1.52      END;
    1.53    $$;
    1.54  
    1.55 +-- temporarily drop deferred trigger to allow UPDATE with subsequent ALTER TABLE:
    1.56 +DROP TRIGGER "issue_requires_first_initiative" ON "issue";
    1.57 +
    1.58 +-- set "min_admission_time" values where appropriate:
    1.59 +UPDATE "policy" SET "min_admission_time" = '0' WHERE "max_admission_time" NOTNULL;
    1.60 +UPDATE "issue" SET "min_admission_time" = '0' WHERE "max_admission_time" NOTNULL;
    1.61 +
    1.62 +-- add constraints related to "min_admission_time" after UPDATE:
    1.63 +ALTER TABLE "policy" ADD CONSTRAINT "timing" CHECK (
    1.64 +  ( "polling" = FALSE AND
    1.65 +    "min_admission_time" NOTNULL AND "max_admission_time" NOTNULL AND
    1.66 +    "min_admission_time" <= "max_admission_time" AND
    1.67 +    "discussion_time" NOTNULL AND
    1.68 +    "verification_time" NOTNULL AND
    1.69 +    "voting_time" NOTNULL ) OR
    1.70 +  ( "polling" = TRUE AND
    1.71 +    "min_admission_time" ISNULL AND "max_admission_time" ISNULL AND
    1.72 +    "discussion_time" NOTNULL AND
    1.73 +    "verification_time" NOTNULL AND
    1.74 +    "voting_time" NOTNULL ) OR
    1.75 +  ( "polling" = TRUE AND
    1.76 +    "min_admission_time" ISNULL AND "max_admission_time" ISNULL AND
    1.77 +    "discussion_time" ISNULL AND
    1.78 +    "verification_time" ISNULL AND
    1.79 +    "voting_time" ISNULL ) );
    1.80 +
    1.81 +ALTER TABLE "issue" ADD CONSTRAINT "admission_time_not_null_unless_instantly_accepted" CHECK (
    1.82 +  ("min_admission_time" NOTNULL) = ("max_admission_time" NOTNULL) AND
    1.83 +  ("min_admission_time" NOTNULL OR ("accepted" NOTNULL AND "accepted" = "created")) );
    1.84 +
    1.85 +-- re-add temporarily dropped trigger:
    1.86 +CREATE CONSTRAINT TRIGGER "issue_requires_first_initiative"
    1.87 +  AFTER INSERT OR UPDATE ON "issue" DEFERRABLE INITIALLY DEFERRED
    1.88 +  FOR EACH ROW EXECUTE PROCEDURE
    1.89 +  "issue_requires_first_initiative_trigger"();
    1.90 +COMMENT ON TRIGGER "issue_requires_first_initiative" ON "issue" IS 'Ensure that new issues have at least one initiative';
    1.91 +
    1.92  COMMIT;

Impressum / About Us