liquid_feedback_core

changeset 453:fb7ccc4933a8

Update script for v3.1.0
author jbe
date Thu Dec 03 22:09:25 2015 +0100 (2015-12-03)
parents 8182c297783f
children 63af9100628c
files update/core-update.v3.0.5-v3.1.0.sql
line diff
     1.1 --- /dev/null	Thu Jan 01 00:00:00 1970 +0000
     1.2 +++ b/update/core-update.v3.0.5-v3.1.0.sql	Thu Dec 03 22:09:25 2015 +0100
     1.3 @@ -0,0 +1,355 @@
     1.4 +BEGIN;
     1.5 +
     1.6 +CREATE OR REPLACE VIEW "liquid_feedback_version" AS
     1.7 +  SELECT * FROM (VALUES ('3.1.0', 3, 1, 0))
     1.8 +  AS "subquery"("string", "major", "minor", "revision");
     1.9 +
    1.10 +ALTER TABLE "member" DROP CONSTRAINT "authority_requires_uid_and_vice_versa";
    1.11 +ALTER TABLE "member" ADD CONSTRAINT "authority_requires_uid_and_vice_versa" CHECK (
    1.12 +  ("authority" NOTNULL) = ("authority_uid" NOTNULL) );
    1.13 +
    1.14 +DROP TABLE "member_application";
    1.15 +DROP TYPE "application_access_level";
    1.16 +
    1.17 +ALTER TABLE "policy" ADD COLUMN "min_admission_time" INTERVAL DEFAULT '0';
    1.18 +ALTER TABLE "policy" ALTER COLUMN "min_admission_time" DROP DEFAULT;
    1.19 +ALTER TABLE "policy" RENAME COLUMN "admission_time" TO "max_admission_time";
    1.20 +
    1.21 +ALTER TABLE "policy" DROP CONSTRAINT "timing";
    1.22 +ALTER TABLE "policy" ADD CONSTRAINT "timing" CHECK (
    1.23 +  ( "polling" = FALSE AND
    1.24 +    "min_admission_time" NOTNULL AND "max_admission_time" NOTNULL AND
    1.25 +    "discussion_time" NOTNULL AND
    1.26 +    "verification_time" NOTNULL AND
    1.27 +    "voting_time" NOTNULL ) OR
    1.28 +  ( "polling" = TRUE AND
    1.29 +    "min_admission_time" ISNULL AND "max_admission_time" ISNULL AND
    1.30 +    "discussion_time" NOTNULL AND
    1.31 +    "verification_time" NOTNULL AND
    1.32 +    "voting_time" NOTNULL ) OR
    1.33 +  ( "polling" = TRUE AND
    1.34 +    "min_admission_time" ISNULL AND "max_admission_time" ISNULL AND
    1.35 +    "discussion_time" ISNULL AND
    1.36 +    "verification_time" ISNULL AND
    1.37 +    "voting_time" ISNULL ) );
    1.38 +
    1.39 +ALTER TABLE "policy" DROP CONSTRAINT "issue_quorum_if_and_only_if_not_polling";
    1.40 +ALTER TABLE "policy" ADD CONSTRAINT "issue_quorum_if_and_only_if_not_polling" CHECK (
    1.41 +  "polling" = ("issue_quorum_num" ISNULL) AND
    1.42 +  "polling" = ("issue_quorum_den" ISNULL) );
    1.43 +
    1.44 +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.45 +COMMENT ON COLUMN "policy"."min_admission_time" IS 'Minimum duration of issue state ''admission''; Minimum time an issue stays open';
    1.46 +
    1.47 +ALTER TABLE "issue" ADD COLUMN "min_admission_time" INTERVAL DEFAULT '0';
    1.48 +ALTER TABLE "issue" ALTER COLUMN "min_admission_time" DROP DEFAULT;
    1.49 +ALTER TABLE "issue" RENAME COLUMN "admission_time" TO "max_admission_time";
    1.50 +
    1.51 +ALTER TABLE "issue" DROP CONSTRAINT "admission_time_not_null_unless_instantly_accepted";
    1.52 +ALTER TABLE "issue" ADD CONSTRAINT "admission_time_not_null_unless_instantly_accepted" CHECK (
    1.53 +  ("min_admission_time" NOTNULL) = ("max_admission_time" NOTNULL) AND
    1.54 +  ("min_admission_time" NOTNULL OR ("accepted" NOTNULL AND "accepted" = "created")) );
    1.55 +
    1.56 +ALTER TABLE "issue" DROP CONSTRAINT "set_both_or_none_of_snapshot_and_latest_snapshot_event";
    1.57 +ALTER TABLE "issue" ADD CONSTRAINT "set_both_or_none_of_snapshot_and_latest_snapshot_event" CHECK (
    1.58 +  (("snapshot" NOTNULL) = ("latest_snapshot_event" NOTNULL)) );
    1.59 +
    1.60 +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.';
    1.61 +COMMENT ON COLUMN "issue"."min_admission_time" IS 'Copied from "policy" table at creation of issue';
    1.62 +
    1.63 +DROP TRIGGER "update_text_search_data" ON "initiative";
    1.64 +ALTER TABLE "initiative" DROP COLUMN "discussion_url";
    1.65 +
    1.66 +ALTER TABLE "initiative" DROP CONSTRAINT "all_or_none_of_revoked_and_revoked_by_member_id_must_be_null";
    1.67 +ALTER TABLE "initiative" ADD CONSTRAINT "all_or_none_of_revoked_and_revoked_by_member_id_must_be_null" CHECK (
    1.68 +  ("revoked" NOTNULL) = ("revoked_by_member_id" NOTNULL) );
    1.69 +
    1.70 + CREATE TRIGGER "update_text_search_data"
    1.71 +   BEFORE INSERT OR UPDATE ON "initiative"
    1.72 +   FOR EACH ROW EXECUTE PROCEDURE
    1.73 +   tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "name");
    1.74 +
    1.75 +ALTER TABLE "event" DROP CONSTRAINT "null_constraints_for_issue_state_changed";
    1.76 +ALTER TABLE "event" DROP CONSTRAINT "null_constraints_for_initiative_creation_or_revocation_or_new_draft";
    1.77 +ALTER TABLE "event" DROP CONSTRAINT "null_constraints_for_suggestion_creation";
    1.78 +
    1.79 +ALTER TABLE "event" ADD CONSTRAINT "null_constr_for_issue_state_changed" CHECK (
    1.80 +  "event" != 'issue_state_changed' OR (
    1.81 +    "member_id"     ISNULL  AND
    1.82 +    "issue_id"      NOTNULL AND
    1.83 +    "state"         NOTNULL AND
    1.84 +    "initiative_id" ISNULL  AND
    1.85 +    "draft_id"      ISNULL  AND
    1.86 +    "suggestion_id" ISNULL  ) );
    1.87 +ALTER TABLE "event" ADD CONSTRAINT "null_constr_for_initiative_creation_or_revocation_or_new_draft" CHECK (
    1.88 +  "event" NOT IN (
    1.89 +    'initiative_created_in_new_issue',
    1.90 +    'initiative_created_in_existing_issue',
    1.91 +    'initiative_revoked',
    1.92 +    'new_draft_created'
    1.93 +  ) OR (
    1.94 +    "member_id"     NOTNULL AND
    1.95 +    "issue_id"      NOTNULL AND
    1.96 +    "state"         NOTNULL AND
    1.97 +    "initiative_id" NOTNULL AND
    1.98 +    "draft_id"      NOTNULL AND
    1.99 +    "suggestion_id" ISNULL  ) );
   1.100 +ALTER TABLE "event" ADD CONSTRAINT "null_constr_for_suggestion_creation" CHECK (
   1.101 +  "event" != 'suggestion_created' OR (
   1.102 +    "member_id"     NOTNULL AND
   1.103 +    "issue_id"      NOTNULL AND
   1.104 +    "state"         NOTNULL AND
   1.105 +    "initiative_id" NOTNULL AND
   1.106 +    "draft_id"      ISNULL  AND
   1.107 +    "suggestion_id" NOTNULL ) );
   1.108 +
   1.109 +CREATE OR REPLACE FUNCTION "copy_timings_trigger"()
   1.110 +  RETURNS TRIGGER
   1.111 +  LANGUAGE 'plpgsql' VOLATILE AS $$
   1.112 +    DECLARE
   1.113 +      "policy_row" "policy"%ROWTYPE;
   1.114 +    BEGIN
   1.115 +      SELECT * INTO "policy_row" FROM "policy"
   1.116 +        WHERE "id" = NEW."policy_id";
   1.117 +      IF NEW."min_admission_time" ISNULL THEN
   1.118 +        NEW."min_admission_time" := "policy_row"."min_admission_time";
   1.119 +      END IF;
   1.120 +      IF NEW."max_admission_time" ISNULL THEN
   1.121 +        NEW."max_admission_time" := "policy_row"."max_admission_time";
   1.122 +      END IF;
   1.123 +      IF NEW."discussion_time" ISNULL THEN
   1.124 +        NEW."discussion_time" := "policy_row"."discussion_time";
   1.125 +      END IF;
   1.126 +      IF NEW."verification_time" ISNULL THEN
   1.127 +        NEW."verification_time" := "policy_row"."verification_time";
   1.128 +      END IF;
   1.129 +      IF NEW."voting_time" ISNULL THEN
   1.130 +        NEW."voting_time" := "policy_row"."voting_time";
   1.131 +      END IF;
   1.132 +      RETURN NEW;
   1.133 +    END;
   1.134 +  $$;
   1.135 +
   1.136 +CREATE OR REPLACE FUNCTION "check_issue"
   1.137 +  ( "issue_id_p" "issue"."id"%TYPE,
   1.138 +    "persist"    "check_issue_persistence" )
   1.139 +  RETURNS "check_issue_persistence"
   1.140 +  LANGUAGE 'plpgsql' VOLATILE AS $$
   1.141 +    DECLARE
   1.142 +      "issue_row"      "issue"%ROWTYPE;
   1.143 +      "policy_row"     "policy"%ROWTYPE;
   1.144 +      "initiative_row" "initiative"%ROWTYPE;
   1.145 +      "state_v"        "issue_state";
   1.146 +    BEGIN
   1.147 +      PERFORM "require_transaction_isolation"();
   1.148 +      IF "persist" ISNULL THEN
   1.149 +        SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
   1.150 +          FOR UPDATE;
   1.151 +        IF "issue_row"."closed" NOTNULL THEN
   1.152 +          RETURN NULL;
   1.153 +        END IF;
   1.154 +        "persist"."state" := "issue_row"."state";
   1.155 +        IF
   1.156 +          ( "issue_row"."state" = 'admission' AND now() >=
   1.157 +            "issue_row"."created" + "issue_row"."max_admission_time" ) OR
   1.158 +          ( "issue_row"."state" = 'discussion' AND now() >=
   1.159 +            "issue_row"."accepted" + "issue_row"."discussion_time" ) OR
   1.160 +          ( "issue_row"."state" = 'verification' AND now() >=
   1.161 +            "issue_row"."half_frozen" + "issue_row"."verification_time" ) OR
   1.162 +          ( "issue_row"."state" = 'voting' AND now() >=
   1.163 +            "issue_row"."fully_frozen" + "issue_row"."voting_time" )
   1.164 +        THEN
   1.165 +          "persist"."phase_finished" := TRUE;
   1.166 +        ELSE
   1.167 +          "persist"."phase_finished" := FALSE;
   1.168 +        END IF;
   1.169 +        IF
   1.170 +          NOT EXISTS (
   1.171 +            -- all initiatives are revoked
   1.172 +            SELECT NULL FROM "initiative"
   1.173 +            WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
   1.174 +          ) AND (
   1.175 +            -- and issue has not been accepted yet
   1.176 +            "persist"."state" = 'admission' OR
   1.177 +            -- or verification time has elapsed
   1.178 +            ( "persist"."state" = 'verification' AND
   1.179 +              "persist"."phase_finished" ) OR
   1.180 +            -- or no initiatives have been revoked lately
   1.181 +            NOT EXISTS (
   1.182 +              SELECT NULL FROM "initiative"
   1.183 +              WHERE "issue_id" = "issue_id_p"
   1.184 +              AND now() < "revoked" + "issue_row"."verification_time"
   1.185 +            )
   1.186 +          )
   1.187 +        THEN
   1.188 +          "persist"."issue_revoked" := TRUE;
   1.189 +        ELSE
   1.190 +          "persist"."issue_revoked" := FALSE;
   1.191 +        END IF;
   1.192 +        IF "persist"."phase_finished" OR "persist"."issue_revoked" THEN
   1.193 +          UPDATE "issue" SET "phase_finished" = now()
   1.194 +            WHERE "id" = "issue_row"."id";
   1.195 +          RETURN "persist";
   1.196 +        ELSIF
   1.197 +          "persist"."state" IN ('admission', 'discussion', 'verification')
   1.198 +        THEN
   1.199 +          RETURN "persist";
   1.200 +        ELSE
   1.201 +          RETURN NULL;
   1.202 +        END IF;
   1.203 +      END IF;
   1.204 +      IF
   1.205 +        "persist"."state" IN ('admission', 'discussion', 'verification') AND
   1.206 +        coalesce("persist"."snapshot_created", FALSE) = FALSE
   1.207 +      THEN
   1.208 +        PERFORM "create_snapshot"("issue_id_p");
   1.209 +        "persist"."snapshot_created" = TRUE;
   1.210 +        IF "persist"."phase_finished" THEN
   1.211 +          IF "persist"."state" = 'admission' THEN
   1.212 +            PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
   1.213 +          ELSIF "persist"."state" = 'discussion' THEN
   1.214 +            PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze');
   1.215 +          ELSIF "persist"."state" = 'verification' THEN
   1.216 +            PERFORM "set_snapshot_event"("issue_id_p", 'full_freeze');
   1.217 +            SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
   1.218 +            SELECT * INTO "policy_row" FROM "policy"
   1.219 +              WHERE "id" = "issue_row"."policy_id";
   1.220 +            FOR "initiative_row" IN
   1.221 +              SELECT * FROM "initiative"
   1.222 +              WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
   1.223 +              FOR UPDATE
   1.224 +            LOOP
   1.225 +              IF
   1.226 +                "initiative_row"."polling" OR (
   1.227 +                  "initiative_row"."satisfied_supporter_count" > 0 AND
   1.228 +                  "initiative_row"."satisfied_supporter_count" *
   1.229 +                  "policy_row"."initiative_quorum_den" >=
   1.230 +                  "issue_row"."population" * "policy_row"."initiative_quorum_num"
   1.231 +                )
   1.232 +              THEN
   1.233 +                UPDATE "initiative" SET "admitted" = TRUE
   1.234 +                  WHERE "id" = "initiative_row"."id";
   1.235 +              ELSE
   1.236 +                UPDATE "initiative" SET "admitted" = FALSE
   1.237 +                  WHERE "id" = "initiative_row"."id";
   1.238 +              END IF;
   1.239 +            END LOOP;
   1.240 +          END IF;
   1.241 +        END IF;
   1.242 +        RETURN "persist";
   1.243 +      END IF;
   1.244 +      IF
   1.245 +        "persist"."state" IN ('admission', 'discussion', 'verification') AND
   1.246 +        coalesce("persist"."harmonic_weights_set", FALSE) = FALSE
   1.247 +      THEN
   1.248 +        PERFORM "set_harmonic_initiative_weights"("issue_id_p");
   1.249 +        "persist"."harmonic_weights_set" = TRUE;
   1.250 +        IF
   1.251 +          "persist"."phase_finished" OR
   1.252 +          "persist"."issue_revoked" OR
   1.253 +          "persist"."state" = 'admission'
   1.254 +        THEN
   1.255 +          RETURN "persist";
   1.256 +        ELSE
   1.257 +          RETURN NULL;
   1.258 +        END IF;
   1.259 +      END IF;
   1.260 +      IF "persist"."issue_revoked" THEN
   1.261 +        IF "persist"."state" = 'admission' THEN
   1.262 +          "state_v" := 'canceled_revoked_before_accepted';
   1.263 +        ELSIF "persist"."state" = 'discussion' THEN
   1.264 +          "state_v" := 'canceled_after_revocation_during_discussion';
   1.265 +        ELSIF "persist"."state" = 'verification' THEN
   1.266 +          "state_v" := 'canceled_after_revocation_during_verification';
   1.267 +        END IF;
   1.268 +        UPDATE "issue" SET
   1.269 +          "state"          = "state_v",
   1.270 +          "closed"         = "phase_finished",
   1.271 +          "phase_finished" = NULL
   1.272 +          WHERE "id" = "issue_id_p";
   1.273 +        RETURN NULL;
   1.274 +      END IF;
   1.275 +      IF "persist"."state" = 'admission' THEN
   1.276 +        SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
   1.277 +          FOR UPDATE;
   1.278 +        SELECT * INTO "policy_row"
   1.279 +          FROM "policy" WHERE "id" = "issue_row"."policy_id";
   1.280 +        IF 
   1.281 +          ( now() >=
   1.282 +            "issue_row"."created" + "issue_row"."min_admission_time" ) AND
   1.283 +          EXISTS (
   1.284 +            SELECT NULL FROM "initiative"
   1.285 +            WHERE "issue_id" = "issue_id_p"
   1.286 +            AND "supporter_count" > 0
   1.287 +            AND "supporter_count" * "policy_row"."issue_quorum_den"
   1.288 +            >= "issue_row"."population" * "policy_row"."issue_quorum_num"
   1.289 +          )
   1.290 +        THEN
   1.291 +          UPDATE "issue" SET
   1.292 +            "state"          = 'discussion',
   1.293 +            "accepted"       = coalesce("phase_finished", now()),
   1.294 +            "phase_finished" = NULL
   1.295 +            WHERE "id" = "issue_id_p";
   1.296 +        ELSIF "issue_row"."phase_finished" NOTNULL THEN
   1.297 +          UPDATE "issue" SET
   1.298 +            "state"          = 'canceled_issue_not_accepted',
   1.299 +            "closed"         = "phase_finished",
   1.300 +            "phase_finished" = NULL
   1.301 +            WHERE "id" = "issue_id_p";
   1.302 +        END IF;
   1.303 +        RETURN NULL;
   1.304 +      END IF;
   1.305 +      IF "persist"."phase_finished" THEN
   1.306 +        IF "persist"."state" = 'discussion' THEN
   1.307 +          UPDATE "issue" SET
   1.308 +            "state"          = 'verification',
   1.309 +            "half_frozen"    = "phase_finished",
   1.310 +            "phase_finished" = NULL
   1.311 +            WHERE "id" = "issue_id_p";
   1.312 +          RETURN NULL;
   1.313 +        END IF;
   1.314 +        IF "persist"."state" = 'verification' THEN
   1.315 +          SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
   1.316 +            FOR UPDATE;
   1.317 +          SELECT * INTO "policy_row" FROM "policy"
   1.318 +            WHERE "id" = "issue_row"."policy_id";
   1.319 +          IF EXISTS (
   1.320 +            SELECT NULL FROM "initiative"
   1.321 +            WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE
   1.322 +          ) THEN
   1.323 +            UPDATE "issue" SET
   1.324 +              "state"          = 'voting',
   1.325 +              "fully_frozen"   = "phase_finished",
   1.326 +              "phase_finished" = NULL
   1.327 +              WHERE "id" = "issue_id_p";
   1.328 +          ELSE
   1.329 +            UPDATE "issue" SET
   1.330 +              "state"          = 'canceled_no_initiative_admitted',
   1.331 +              "fully_frozen"   = "phase_finished",
   1.332 +              "closed"         = "phase_finished",
   1.333 +              "phase_finished" = NULL
   1.334 +              WHERE "id" = "issue_id_p";
   1.335 +            -- NOTE: The following DELETE statements have effect only when
   1.336 +            --       issue state has been manipulated
   1.337 +            DELETE FROM "direct_voter"     WHERE "issue_id" = "issue_id_p";
   1.338 +            DELETE FROM "delegating_voter" WHERE "issue_id" = "issue_id_p";
   1.339 +            DELETE FROM "battle"           WHERE "issue_id" = "issue_id_p";
   1.340 +          END IF;
   1.341 +          RETURN NULL;
   1.342 +        END IF;
   1.343 +        IF "persist"."state" = 'voting' THEN
   1.344 +          IF coalesce("persist"."closed_voting", FALSE) = FALSE THEN
   1.345 +            PERFORM "close_voting"("issue_id_p");
   1.346 +            "persist"."closed_voting" = TRUE;
   1.347 +            RETURN "persist";
   1.348 +          END IF;
   1.349 +          PERFORM "calculate_ranks"("issue_id_p");
   1.350 +          RETURN NULL;
   1.351 +        END IF;
   1.352 +      END IF;
   1.353 +      RAISE WARNING 'should not happen';
   1.354 +      RETURN NULL;
   1.355 +    END;
   1.356 +  $$;
   1.357 +
   1.358 +COMMIT;

Impressum / About Us