liquid_feedback_core

annotate update/core-update.v3.0.5-v3.1.0.sql @ 580:78f6833f5f19

find changesets by author, revision, files, or words in the commit message
Further work on role accounts (role_verification, real_member_id)
author jbe
date Sun Mar 04 17:59:11 2018 +0100 (2018-03-04)
parents a9c4521466e6
children
rev   line source
jbe@453 1 BEGIN;
jbe@453 2
jbe@453 3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
jbe@453 4 SELECT * FROM (VALUES ('3.1.0', 3, 1, 0))
jbe@453 5 AS "subquery"("string", "major", "minor", "revision");
jbe@453 6
jbe@453 7 ALTER TABLE "member" DROP CONSTRAINT "authority_requires_uid_and_vice_versa";
jbe@453 8 ALTER TABLE "member" ADD CONSTRAINT "authority_requires_uid_and_vice_versa" CHECK (
jbe@453 9 ("authority" NOTNULL) = ("authority_uid" NOTNULL) );
jbe@453 10
jbe@453 11 DROP TABLE "member_application";
jbe@453 12 DROP TYPE "application_access_level";
jbe@453 13
jbe@456 14 ALTER TABLE "policy" ADD COLUMN "min_admission_time" INTERVAL;
jbe@453 15 ALTER TABLE "policy" RENAME COLUMN "admission_time" TO "max_admission_time";
jbe@453 16
jbe@456 17 ALTER TABLE "policy" DROP CONSTRAINT "timing"; -- re-added after UPDATE
jbe@453 18
jbe@453 19 ALTER TABLE "policy" DROP CONSTRAINT "issue_quorum_if_and_only_if_not_polling";
jbe@453 20 ALTER TABLE "policy" ADD CONSTRAINT "issue_quorum_if_and_only_if_not_polling" CHECK (
jbe@453 21 "polling" = ("issue_quorum_num" ISNULL) AND
jbe@453 22 "polling" = ("issue_quorum_den" ISNULL) );
jbe@453 23
jbe@453 24 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 25 COMMENT ON COLUMN "policy"."min_admission_time" IS 'Minimum duration of issue state ''admission''; Minimum time an issue stays open';
jbe@453 26
jbe@456 27 ALTER TABLE "issue" ADD COLUMN "min_admission_time" INTERVAL;
jbe@453 28 ALTER TABLE "issue" RENAME COLUMN "admission_time" TO "max_admission_time";
jbe@453 29
jbe@456 30 ALTER TABLE "issue" DROP CONSTRAINT "admission_time_not_null_unless_instantly_accepted"; -- re-added after UPDATE
jbe@453 31
jbe@453 32 ALTER TABLE "issue" DROP CONSTRAINT "set_both_or_none_of_snapshot_and_latest_snapshot_event";
jbe@453 33 ALTER TABLE "issue" ADD CONSTRAINT "set_both_or_none_of_snapshot_and_latest_snapshot_event" CHECK (
jbe@453 34 (("snapshot" NOTNULL) = ("latest_snapshot_event" NOTNULL)) );
jbe@453 35
jbe@453 36 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 37 COMMENT ON COLUMN "issue"."min_admission_time" IS 'Copied from "policy" table at creation of issue';
jbe@453 38
jbe@453 39 DROP TRIGGER "update_text_search_data" ON "initiative";
jbe@453 40 ALTER TABLE "initiative" DROP COLUMN "discussion_url";
jbe@453 41
jbe@453 42 ALTER TABLE "initiative" DROP CONSTRAINT "all_or_none_of_revoked_and_revoked_by_member_id_must_be_null";
jbe@453 43 ALTER TABLE "initiative" ADD CONSTRAINT "all_or_none_of_revoked_and_revoked_by_member_id_must_be_null" CHECK (
jbe@453 44 ("revoked" NOTNULL) = ("revoked_by_member_id" NOTNULL) );
jbe@453 45
jbe@453 46 CREATE TRIGGER "update_text_search_data"
jbe@453 47 BEFORE INSERT OR UPDATE ON "initiative"
jbe@453 48 FOR EACH ROW EXECUTE PROCEDURE
jbe@453 49 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "name");
jbe@453 50
jbe@453 51 ALTER TABLE "event" DROP CONSTRAINT "null_constraints_for_issue_state_changed";
jbe@453 52 ALTER TABLE "event" DROP CONSTRAINT "null_constraints_for_initiative_creation_or_revocation_or_new_draft";
jbe@453 53 ALTER TABLE "event" DROP CONSTRAINT "null_constraints_for_suggestion_creation";
jbe@453 54
jbe@453 55 ALTER TABLE "event" ADD CONSTRAINT "null_constr_for_issue_state_changed" CHECK (
jbe@453 56 "event" != 'issue_state_changed' OR (
jbe@453 57 "member_id" ISNULL AND
jbe@453 58 "issue_id" NOTNULL AND
jbe@453 59 "state" NOTNULL AND
jbe@453 60 "initiative_id" ISNULL AND
jbe@453 61 "draft_id" ISNULL AND
jbe@453 62 "suggestion_id" ISNULL ) );
jbe@453 63 ALTER TABLE "event" ADD CONSTRAINT "null_constr_for_initiative_creation_or_revocation_or_new_draft" CHECK (
jbe@453 64 "event" NOT IN (
jbe@453 65 'initiative_created_in_new_issue',
jbe@453 66 'initiative_created_in_existing_issue',
jbe@453 67 'initiative_revoked',
jbe@453 68 'new_draft_created'
jbe@453 69 ) OR (
jbe@453 70 "member_id" NOTNULL AND
jbe@453 71 "issue_id" NOTNULL AND
jbe@453 72 "state" NOTNULL AND
jbe@453 73 "initiative_id" NOTNULL AND
jbe@453 74 "draft_id" NOTNULL AND
jbe@453 75 "suggestion_id" ISNULL ) );
jbe@453 76 ALTER TABLE "event" ADD CONSTRAINT "null_constr_for_suggestion_creation" CHECK (
jbe@453 77 "event" != 'suggestion_created' OR (
jbe@453 78 "member_id" NOTNULL AND
jbe@453 79 "issue_id" NOTNULL AND
jbe@453 80 "state" NOTNULL AND
jbe@453 81 "initiative_id" NOTNULL AND
jbe@453 82 "draft_id" ISNULL AND
jbe@453 83 "suggestion_id" NOTNULL ) );
jbe@453 84
jbe@453 85 CREATE OR REPLACE FUNCTION "copy_timings_trigger"()
jbe@453 86 RETURNS TRIGGER
jbe@453 87 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@453 88 DECLARE
jbe@453 89 "policy_row" "policy"%ROWTYPE;
jbe@453 90 BEGIN
jbe@453 91 SELECT * INTO "policy_row" FROM "policy"
jbe@453 92 WHERE "id" = NEW."policy_id";
jbe@453 93 IF NEW."min_admission_time" ISNULL THEN
jbe@453 94 NEW."min_admission_time" := "policy_row"."min_admission_time";
jbe@453 95 END IF;
jbe@453 96 IF NEW."max_admission_time" ISNULL THEN
jbe@453 97 NEW."max_admission_time" := "policy_row"."max_admission_time";
jbe@453 98 END IF;
jbe@453 99 IF NEW."discussion_time" ISNULL THEN
jbe@453 100 NEW."discussion_time" := "policy_row"."discussion_time";
jbe@453 101 END IF;
jbe@453 102 IF NEW."verification_time" ISNULL THEN
jbe@453 103 NEW."verification_time" := "policy_row"."verification_time";
jbe@453 104 END IF;
jbe@453 105 IF NEW."voting_time" ISNULL THEN
jbe@453 106 NEW."voting_time" := "policy_row"."voting_time";
jbe@453 107 END IF;
jbe@453 108 RETURN NEW;
jbe@453 109 END;
jbe@453 110 $$;
jbe@453 111
jbe@453 112 CREATE OR REPLACE FUNCTION "check_issue"
jbe@453 113 ( "issue_id_p" "issue"."id"%TYPE,
jbe@453 114 "persist" "check_issue_persistence" )
jbe@453 115 RETURNS "check_issue_persistence"
jbe@453 116 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@453 117 DECLARE
jbe@453 118 "issue_row" "issue"%ROWTYPE;
jbe@453 119 "policy_row" "policy"%ROWTYPE;
jbe@453 120 "initiative_row" "initiative"%ROWTYPE;
jbe@453 121 "state_v" "issue_state";
jbe@453 122 BEGIN
jbe@453 123 PERFORM "require_transaction_isolation"();
jbe@453 124 IF "persist" ISNULL THEN
jbe@453 125 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
jbe@453 126 FOR UPDATE;
jbe@453 127 IF "issue_row"."closed" NOTNULL THEN
jbe@453 128 RETURN NULL;
jbe@453 129 END IF;
jbe@453 130 "persist"."state" := "issue_row"."state";
jbe@453 131 IF
jbe@453 132 ( "issue_row"."state" = 'admission' AND now() >=
jbe@453 133 "issue_row"."created" + "issue_row"."max_admission_time" ) OR
jbe@453 134 ( "issue_row"."state" = 'discussion' AND now() >=
jbe@453 135 "issue_row"."accepted" + "issue_row"."discussion_time" ) OR
jbe@453 136 ( "issue_row"."state" = 'verification' AND now() >=
jbe@453 137 "issue_row"."half_frozen" + "issue_row"."verification_time" ) OR
jbe@453 138 ( "issue_row"."state" = 'voting' AND now() >=
jbe@453 139 "issue_row"."fully_frozen" + "issue_row"."voting_time" )
jbe@453 140 THEN
jbe@453 141 "persist"."phase_finished" := TRUE;
jbe@453 142 ELSE
jbe@453 143 "persist"."phase_finished" := FALSE;
jbe@453 144 END IF;
jbe@453 145 IF
jbe@453 146 NOT EXISTS (
jbe@453 147 -- all initiatives are revoked
jbe@453 148 SELECT NULL FROM "initiative"
jbe@453 149 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
jbe@453 150 ) AND (
jbe@453 151 -- and issue has not been accepted yet
jbe@453 152 "persist"."state" = 'admission' OR
jbe@453 153 -- or verification time has elapsed
jbe@453 154 ( "persist"."state" = 'verification' AND
jbe@453 155 "persist"."phase_finished" ) OR
jbe@453 156 -- or no initiatives have been revoked lately
jbe@453 157 NOT EXISTS (
jbe@453 158 SELECT NULL FROM "initiative"
jbe@453 159 WHERE "issue_id" = "issue_id_p"
jbe@453 160 AND now() < "revoked" + "issue_row"."verification_time"
jbe@453 161 )
jbe@453 162 )
jbe@453 163 THEN
jbe@453 164 "persist"."issue_revoked" := TRUE;
jbe@453 165 ELSE
jbe@453 166 "persist"."issue_revoked" := FALSE;
jbe@453 167 END IF;
jbe@453 168 IF "persist"."phase_finished" OR "persist"."issue_revoked" THEN
jbe@453 169 UPDATE "issue" SET "phase_finished" = now()
jbe@453 170 WHERE "id" = "issue_row"."id";
jbe@453 171 RETURN "persist";
jbe@453 172 ELSIF
jbe@453 173 "persist"."state" IN ('admission', 'discussion', 'verification')
jbe@453 174 THEN
jbe@453 175 RETURN "persist";
jbe@453 176 ELSE
jbe@453 177 RETURN NULL;
jbe@453 178 END IF;
jbe@453 179 END IF;
jbe@453 180 IF
jbe@453 181 "persist"."state" IN ('admission', 'discussion', 'verification') AND
jbe@453 182 coalesce("persist"."snapshot_created", FALSE) = FALSE
jbe@453 183 THEN
jbe@453 184 PERFORM "create_snapshot"("issue_id_p");
jbe@453 185 "persist"."snapshot_created" = TRUE;
jbe@453 186 IF "persist"."phase_finished" THEN
jbe@453 187 IF "persist"."state" = 'admission' THEN
jbe@453 188 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
jbe@453 189 ELSIF "persist"."state" = 'discussion' THEN
jbe@453 190 PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze');
jbe@453 191 ELSIF "persist"."state" = 'verification' THEN
jbe@453 192 PERFORM "set_snapshot_event"("issue_id_p", 'full_freeze');
jbe@453 193 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
jbe@453 194 SELECT * INTO "policy_row" FROM "policy"
jbe@453 195 WHERE "id" = "issue_row"."policy_id";
jbe@453 196 FOR "initiative_row" IN
jbe@453 197 SELECT * FROM "initiative"
jbe@453 198 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
jbe@453 199 FOR UPDATE
jbe@453 200 LOOP
jbe@453 201 IF
jbe@453 202 "initiative_row"."polling" OR (
jbe@453 203 "initiative_row"."satisfied_supporter_count" > 0 AND
jbe@453 204 "initiative_row"."satisfied_supporter_count" *
jbe@453 205 "policy_row"."initiative_quorum_den" >=
jbe@453 206 "issue_row"."population" * "policy_row"."initiative_quorum_num"
jbe@453 207 )
jbe@453 208 THEN
jbe@453 209 UPDATE "initiative" SET "admitted" = TRUE
jbe@453 210 WHERE "id" = "initiative_row"."id";
jbe@453 211 ELSE
jbe@453 212 UPDATE "initiative" SET "admitted" = FALSE
jbe@453 213 WHERE "id" = "initiative_row"."id";
jbe@453 214 END IF;
jbe@453 215 END LOOP;
jbe@453 216 END IF;
jbe@453 217 END IF;
jbe@453 218 RETURN "persist";
jbe@453 219 END IF;
jbe@453 220 IF
jbe@453 221 "persist"."state" IN ('admission', 'discussion', 'verification') AND
jbe@453 222 coalesce("persist"."harmonic_weights_set", FALSE) = FALSE
jbe@453 223 THEN
jbe@453 224 PERFORM "set_harmonic_initiative_weights"("issue_id_p");
jbe@453 225 "persist"."harmonic_weights_set" = TRUE;
jbe@453 226 IF
jbe@453 227 "persist"."phase_finished" OR
jbe@453 228 "persist"."issue_revoked" OR
jbe@453 229 "persist"."state" = 'admission'
jbe@453 230 THEN
jbe@453 231 RETURN "persist";
jbe@453 232 ELSE
jbe@453 233 RETURN NULL;
jbe@453 234 END IF;
jbe@453 235 END IF;
jbe@453 236 IF "persist"."issue_revoked" THEN
jbe@453 237 IF "persist"."state" = 'admission' THEN
jbe@453 238 "state_v" := 'canceled_revoked_before_accepted';
jbe@453 239 ELSIF "persist"."state" = 'discussion' THEN
jbe@453 240 "state_v" := 'canceled_after_revocation_during_discussion';
jbe@453 241 ELSIF "persist"."state" = 'verification' THEN
jbe@453 242 "state_v" := 'canceled_after_revocation_during_verification';
jbe@453 243 END IF;
jbe@453 244 UPDATE "issue" SET
jbe@453 245 "state" = "state_v",
jbe@453 246 "closed" = "phase_finished",
jbe@453 247 "phase_finished" = NULL
jbe@453 248 WHERE "id" = "issue_id_p";
jbe@453 249 RETURN NULL;
jbe@453 250 END IF;
jbe@453 251 IF "persist"."state" = 'admission' THEN
jbe@453 252 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
jbe@453 253 FOR UPDATE;
jbe@453 254 SELECT * INTO "policy_row"
jbe@453 255 FROM "policy" WHERE "id" = "issue_row"."policy_id";
jbe@453 256 IF
jbe@453 257 ( now() >=
jbe@453 258 "issue_row"."created" + "issue_row"."min_admission_time" ) AND
jbe@453 259 EXISTS (
jbe@453 260 SELECT NULL FROM "initiative"
jbe@453 261 WHERE "issue_id" = "issue_id_p"
jbe@453 262 AND "supporter_count" > 0
jbe@453 263 AND "supporter_count" * "policy_row"."issue_quorum_den"
jbe@453 264 >= "issue_row"."population" * "policy_row"."issue_quorum_num"
jbe@453 265 )
jbe@453 266 THEN
jbe@453 267 UPDATE "issue" SET
jbe@453 268 "state" = 'discussion',
jbe@453 269 "accepted" = coalesce("phase_finished", now()),
jbe@453 270 "phase_finished" = NULL
jbe@453 271 WHERE "id" = "issue_id_p";
jbe@453 272 ELSIF "issue_row"."phase_finished" NOTNULL THEN
jbe@453 273 UPDATE "issue" SET
jbe@453 274 "state" = 'canceled_issue_not_accepted',
jbe@453 275 "closed" = "phase_finished",
jbe@453 276 "phase_finished" = NULL
jbe@453 277 WHERE "id" = "issue_id_p";
jbe@453 278 END IF;
jbe@453 279 RETURN NULL;
jbe@453 280 END IF;
jbe@453 281 IF "persist"."phase_finished" THEN
jbe@453 282 IF "persist"."state" = 'discussion' THEN
jbe@453 283 UPDATE "issue" SET
jbe@453 284 "state" = 'verification',
jbe@453 285 "half_frozen" = "phase_finished",
jbe@453 286 "phase_finished" = NULL
jbe@453 287 WHERE "id" = "issue_id_p";
jbe@453 288 RETURN NULL;
jbe@453 289 END IF;
jbe@453 290 IF "persist"."state" = 'verification' THEN
jbe@453 291 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
jbe@453 292 FOR UPDATE;
jbe@453 293 SELECT * INTO "policy_row" FROM "policy"
jbe@453 294 WHERE "id" = "issue_row"."policy_id";
jbe@453 295 IF EXISTS (
jbe@453 296 SELECT NULL FROM "initiative"
jbe@453 297 WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE
jbe@453 298 ) THEN
jbe@453 299 UPDATE "issue" SET
jbe@453 300 "state" = 'voting',
jbe@453 301 "fully_frozen" = "phase_finished",
jbe@453 302 "phase_finished" = NULL
jbe@453 303 WHERE "id" = "issue_id_p";
jbe@453 304 ELSE
jbe@453 305 UPDATE "issue" SET
jbe@453 306 "state" = 'canceled_no_initiative_admitted',
jbe@453 307 "fully_frozen" = "phase_finished",
jbe@453 308 "closed" = "phase_finished",
jbe@453 309 "phase_finished" = NULL
jbe@453 310 WHERE "id" = "issue_id_p";
jbe@453 311 -- NOTE: The following DELETE statements have effect only when
jbe@453 312 -- issue state has been manipulated
jbe@453 313 DELETE FROM "direct_voter" WHERE "issue_id" = "issue_id_p";
jbe@453 314 DELETE FROM "delegating_voter" WHERE "issue_id" = "issue_id_p";
jbe@453 315 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
jbe@453 316 END IF;
jbe@453 317 RETURN NULL;
jbe@453 318 END IF;
jbe@453 319 IF "persist"."state" = 'voting' THEN
jbe@453 320 IF coalesce("persist"."closed_voting", FALSE) = FALSE THEN
jbe@453 321 PERFORM "close_voting"("issue_id_p");
jbe@453 322 "persist"."closed_voting" = TRUE;
jbe@453 323 RETURN "persist";
jbe@453 324 END IF;
jbe@453 325 PERFORM "calculate_ranks"("issue_id_p");
jbe@453 326 RETURN NULL;
jbe@453 327 END IF;
jbe@453 328 END IF;
jbe@453 329 RAISE WARNING 'should not happen';
jbe@453 330 RETURN NULL;
jbe@453 331 END;
jbe@453 332 $$;
jbe@453 333
jbe@456 334 -- temporarily drop deferred trigger to allow UPDATE with subsequent ALTER TABLE:
jbe@456 335 DROP TRIGGER "issue_requires_first_initiative" ON "issue";
jbe@456 336
jbe@456 337 -- set "min_admission_time" values where appropriate:
jbe@456 338 UPDATE "policy" SET "min_admission_time" = '0' WHERE "max_admission_time" NOTNULL;
jbe@456 339 UPDATE "issue" SET "min_admission_time" = '0' WHERE "max_admission_time" NOTNULL;
jbe@456 340
jbe@456 341 -- add constraints related to "min_admission_time" after UPDATE:
jbe@456 342 ALTER TABLE "policy" ADD CONSTRAINT "timing" CHECK (
jbe@456 343 ( "polling" = FALSE AND
jbe@456 344 "min_admission_time" NOTNULL AND "max_admission_time" NOTNULL AND
jbe@456 345 "min_admission_time" <= "max_admission_time" AND
jbe@456 346 "discussion_time" NOTNULL AND
jbe@456 347 "verification_time" NOTNULL AND
jbe@456 348 "voting_time" NOTNULL ) OR
jbe@456 349 ( "polling" = TRUE AND
jbe@456 350 "min_admission_time" ISNULL AND "max_admission_time" ISNULL AND
jbe@456 351 "discussion_time" NOTNULL AND
jbe@456 352 "verification_time" NOTNULL AND
jbe@456 353 "voting_time" NOTNULL ) OR
jbe@456 354 ( "polling" = TRUE AND
jbe@456 355 "min_admission_time" ISNULL AND "max_admission_time" ISNULL AND
jbe@456 356 "discussion_time" ISNULL AND
jbe@456 357 "verification_time" ISNULL AND
jbe@456 358 "voting_time" ISNULL ) );
jbe@456 359
jbe@456 360 ALTER TABLE "issue" ADD CONSTRAINT "admission_time_not_null_unless_instantly_accepted" CHECK (
jbe@456 361 ("min_admission_time" NOTNULL) = ("max_admission_time" NOTNULL) AND
jbe@456 362 ("min_admission_time" NOTNULL OR ("accepted" NOTNULL AND "accepted" = "created")) );
jbe@456 363
jbe@456 364 -- re-add temporarily dropped trigger:
jbe@456 365 CREATE CONSTRAINT TRIGGER "issue_requires_first_initiative"
jbe@456 366 AFTER INSERT OR UPDATE ON "issue" DEFERRABLE INITIALLY DEFERRED
jbe@456 367 FOR EACH ROW EXECUTE PROCEDURE
jbe@456 368 "issue_requires_first_initiative_trigger"();
jbe@456 369 COMMENT ON TRIGGER "issue_requires_first_initiative" ON "issue" IS 'Ensure that new issues have at least one initiative';
jbe@456 370
jbe@453 371 COMMIT;

Impressum / About Us