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;