liquid_feedback_core
changeset 602:9570aeabf4fc
Added update script to version 4.2.0
| author | jbe |
|---|---|
| date | Fri Feb 07 10:52:37 2020 +0100 (2020-02-07) |
| parents | aa0620c9c4df |
| children | 617ac1725557 |
| files | update/core-update.v4.1.0-v4.2.0.sql |
line diff
1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000 1.2 +++ b/update/core-update.v4.1.0-v4.2.0.sql Fri Feb 07 10:52:37 2020 +0100 1.3 @@ -0,0 +1,1208 @@ 1.4 +BEGIN; 1.5 + 1.6 +CREATE OR REPLACE VIEW "liquid_feedback_version" AS 1.7 + SELECT * FROM (VALUES ('4.2.0-incomplete-update', 4, 2, -1)) 1.8 + AS "subquery"("string", "major", "minor", "revision"); 1.9 + 1.10 +ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'posting_created'; 1.11 + 1.12 +COMMIT; 1.13 + 1.14 +BEGIN; 1.15 + 1.16 +CREATE OR REPLACE VIEW "liquid_feedback_version" AS 1.17 + SELECT * FROM (VALUES ('4.2.0', 4, 2, 0)) 1.18 + AS "subquery"("string", "major", "minor", "revision"); 1.19 + 1.20 +DROP VIEW "newsletter_to_send"; 1.21 +DROP VIEW "scheduled_notification_to_send"; 1.22 +DROP VIEW "member_contingent_left"; 1.23 +DROP VIEW "member_contingent"; 1.24 +DROP VIEW "expired_snapshot"; 1.25 +DROP VIEW "current_draft"; 1.26 +DROP VIEW "opening_draft"; 1.27 +DROP VIEW "area_with_unaccepted_issues"; 1.28 +DROP VIEW "member_to_notify"; 1.29 +DROP VIEW "member_eligible_to_be_notified"; 1.30 + 1.31 +CREATE EXTENSION IF NOT EXISTS btree_gist; 1.32 +CREATE EXTENSION IF NOT EXISTS conflux; 1.33 + 1.34 +DROP FUNCTION "text_search_query" (TEXT); 1.35 + 1.36 +ALTER TABLE "system_setting" DROP COLUMN "snapshot_retention"; 1.37 + 1.38 +CREATE TABLE "file" ( 1.39 + "id" SERIAL8 PRIMARY KEY, 1.40 + UNIQUE ("content_type", "hash"), 1.41 + "content_type" TEXT NOT NULL, 1.42 + "hash" TEXT NOT NULL, 1.43 + "data" BYTEA NOT NULL, 1.44 + "preview_content_type" TEXT, 1.45 + "preview_data" BYTEA ); 1.46 + 1.47 +COMMENT ON TABLE "file" IS 'Table holding file contents for draft attachments'; 1.48 + 1.49 +COMMENT ON COLUMN "file"."content_type" IS 'Content type of "data"'; 1.50 +COMMENT ON COLUMN "file"."hash" IS 'Hash of "data" to avoid storing duplicates where content-type and data is identical'; 1.51 +COMMENT ON COLUMN "file"."data" IS 'Binary content'; 1.52 +COMMENT ON COLUMN "file"."preview_content_type" IS 'Content type of "preview_data"'; 1.53 +COMMENT ON COLUMN "file"."preview_data" IS 'Preview (e.g. preview image)'; 1.54 + 1.55 +ALTER TABLE "member" DROP COLUMN "text_search_data"; 1.56 +DROP TRIGGER "update_text_search_data" ON "member"; 1.57 + 1.58 +CREATE INDEX "member_useterms_member_id_contract_identifier" ON "member_useterms" ("member_id", "contract_identifier"); 1.59 + 1.60 +ALTER TABLE "member_profile" DROP COLUMN "text_search_data"; 1.61 +DROP TRIGGER "update_text_search_data" ON "member_profile"; 1.62 + 1.63 +ALTER TABLE "contact" ADD COLUMN "following" BOOLEAN NOT NULL DEFAULT TRUE; 1.64 + 1.65 +COMMENT ON COLUMN "contact"."following" IS 'TRUE = actions of contact are shown in personal timeline'; 1.66 + 1.67 +ALTER TABLE "unit" DROP COLUMN "text_search_data"; 1.68 +DROP TRIGGER "update_text_search_data" ON "unit"; 1.69 + 1.70 +ALTER TABLE "area" DROP COLUMN "text_search_data"; 1.71 +DROP TRIGGER "update_text_search_data" ON "area"; 1.72 + 1.73 +DROP INDEX "issue_accepted_idx"; 1.74 +DROP INDEX "issue_half_frozen_idx"; 1.75 +DROP INDEX "issue_fully_frozen_idx"; 1.76 +ALTER INDEX "issue_created_idx_open" RENAME TO "issue_open_created_idx"; 1.77 +DROP INDEX "issue_closed_idx_canceled"; 1.78 +ALTER INDEX "issue_latest_snapshot_id" RENAME TO "issue_latest_snapshot_id_idx"; 1.79 +ALTER INDEX "issue_admission_snapshot_id" RENAME TO "issue_admission_snapshot_id_idx"; 1.80 +ALTER INDEX "issue_half_freeze_snapshot_id" RENAME TO "issue_half_freeze_snapshot_id_idx"; 1.81 +ALTER INDEX "issue_full_freeze_snapshot_id" RENAME TO "issue_full_freeze_snapshot_id_idx"; 1.82 + 1.83 +ALTER TABLE "initiative" ADD COLUMN "content" TEXT; 1.84 +ALTER TABLE "initiative" DROP COLUMN "text_search_data"; 1.85 +ALTER TABLE "initiative" DROP COLUMN "draft_text_search_data"; 1.86 +DROP INDEX "initiative_revoked_idx"; 1.87 +DROP TRIGGER "update_text_search_data" ON "initiative"; 1.88 + 1.89 +COMMENT ON COLUMN "initiative"."content" IS 'Initiative text (automatically copied from most recent draft)'; 1.90 + 1.91 +ALTER TABLE "battle" DROP CONSTRAINT "initiative_ids_not_equal"; 1.92 +ALTER TABLE "battle" ADD CONSTRAINT "initiative_ids_not_equal" CHECK ( 1.93 + "winning_initiative_id" != "losing_initiative_id" AND 1.94 + ("winning_initiative_id" NOTNULL OR "losing_initiative_id" NOTNULL) ); 1.95 + 1.96 +ALTER TABLE "draft" DROP COLUMN "text_search_data"; 1.97 +DROP TRIGGER "update_text_search_data" ON "draft"; 1.98 + 1.99 +CREATE TABLE "draft_attachment" ( 1.100 + "id" SERIAL8 PRIMARY KEY, 1.101 + "draft_id" INT8 REFERENCES "draft" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.102 + "file_id" INT8 REFERENCES "file" ("id") ON DELETE RESTRICT ON UPDATE CASCADE, 1.103 + "content_type" TEXT, 1.104 + "title" TEXT, 1.105 + "description" TEXT ); 1.106 + 1.107 +COMMENT ON TABLE "draft_attachment" IS 'Binary attachments for a draft (images, PDF file, etc.); Implicitly ordered through ''id'' column'; 1.108 + 1.109 +ALTER TABLE "suggestion" DROP COLUMN "text_search_data"; 1.110 +DROP TRIGGER "update_text_search_data" ON "suggestion"; 1.111 + 1.112 +ALTER TABLE "direct_voter" DROP COLUMN "text_search_data"; 1.113 +DROP TRIGGER "update_text_search_data" ON "direct_voter"; 1.114 + 1.115 +CREATE TABLE "posting" ( 1.116 + UNIQUE ("author_id", "id"), -- index needed for foreign-key on table "posting_lexeme" 1.117 + "id" SERIAL8 PRIMARY KEY, 1.118 + "author_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE, 1.119 + "created" TIMESTAMPTZ NOT NULL DEFAULT now(), 1.120 + "message" TEXT NOT NULL, 1.121 + "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.122 + "area_id" INT4, 1.123 + FOREIGN KEY ("unit_id", "area_id") REFERENCES "area" ("unit_id", "id") ON DELETE CASCADE ON UPDATE CASCADE, 1.124 + "policy_id" INT4 REFERENCES "policy" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.125 + "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.126 + FOREIGN KEY ("area_id", "issue_id") REFERENCES "issue" ("area_id", "id") ON DELETE CASCADE ON UPDATE CASCADE, 1.127 + FOREIGN KEY ("policy_id", "issue_id") REFERENCES "issue" ("policy_id", "id") ON DELETE CASCADE ON UPDATE CASCADE, 1.128 + "initiative_id" INT4, 1.129 + "suggestion_id" INT8, 1.130 + -- NOTE: no referential integrity for suggestions because those are 1.131 + -- actually deleted 1.132 + -- FOREIGN KEY ("initiative_id", "suggestion_id") 1.133 + -- REFERENCES "suggestion" ("initiative_id", "id") 1.134 + -- ON DELETE CASCADE ON UPDATE CASCADE, 1.135 + CONSTRAINT "area_requires_unit" CHECK ( 1.136 + "area_id" ISNULL OR "unit_id" NOTNULL ), 1.137 + CONSTRAINT "policy_set_when_issue_set" CHECK ( 1.138 + ("policy_id" NOTNULL) = ("issue_id" NOTNULL) ), 1.139 + CONSTRAINT "issue_requires_area" CHECK ( 1.140 + "issue_id" ISNULL OR "area_id" NOTNULL ), 1.141 + CONSTRAINT "initiative_requires_issue" CHECK ( 1.142 + "initiative_id" ISNULL OR "issue_id" NOTNULL ), 1.143 + CONSTRAINT "suggestion_requires_initiative" CHECK ( 1.144 + "suggestion_id" ISNULL OR "initiative_id" NOTNULL ) ); 1.145 +CREATE INDEX "posting_global_idx" ON "posting" USING gist ((pstamp("author_id", "id"))); 1.146 +CREATE INDEX "posting_unit_idx" ON "posting" USING gist ("unit_id", (pstamp("author_id", "id"))) WHERE "unit_id" NOTNULL; 1.147 +CREATE INDEX "posting_area_idx" ON "posting" USING gist ("area_id", (pstamp("author_id", "id"))) WHERE "area_id" NOTNULL; 1.148 +CREATE INDEX "posting_policy_idx" ON "posting" USING gist ("policy_id", (pstamp("author_id", "id"))) WHERE "policy_id" NOTNULL; 1.149 +CREATE INDEX "posting_issue_idx" ON "posting" USING gist ("issue_id", (pstamp("author_id", "id"))) WHERE "issue_id" NOTNULL; 1.150 +CREATE INDEX "posting_initiative_idx" ON "posting" USING gist ("initiative_id", (pstamp("author_id", "id"))) WHERE "initiative_id" NOTNULL; 1.151 +CREATE INDEX "posting_suggestion_idx" ON "posting" USING gist ("suggestion_id", (pstamp("author_id", "id"))) WHERE "suggestion_id" NOTNULL; 1.152 +COMMENT ON TABLE "posting" IS 'Text postings of members; a text posting may optionally be associated to a unit, area, policy, issue, initiative, or suggestion'; 1.153 + 1.154 +CREATE TABLE "posting_lexeme" ( 1.155 + PRIMARY KEY ("posting_id", "lexeme"), 1.156 + FOREIGN KEY ("posting_id", "author_id") REFERENCES "posting" ("id", "author_id") ON DELETE CASCADE ON UPDATE CASCADE, 1.157 + "posting_id" INT8, 1.158 + "lexeme" TEXT, 1.159 + "author_id" INT4 ); 1.160 +CREATE INDEX "posting_lexeme_idx" ON "posting_lexeme" USING gist ("lexeme", (pstamp("author_id", "posting_id"))); 1.161 + 1.162 +COMMENT ON TABLE "posting_lexeme" IS 'Helper table to allow searches for hashtags.'; 1.163 + 1.164 +ALTER TABLE "event" ADD COLUMN "posting_id" INT8 REFERENCES "posting" ("id") ON DELETE RESTRICT ON UPDATE CASCADE; 1.165 +ALTER TABLE "event" DROP CONSTRAINT "constr_for_issue_state_changed"; 1.166 +ALTER TABLE "event" DROP CONSTRAINT "constr_for_initiative_creation_or_revocation_or_new_draft"; 1.167 +ALTER TABLE "event" DROP CONSTRAINT "constr_for_suggestion_creation"; 1.168 +ALTER TABLE "event" DROP CONSTRAINT "constr_for_suggestion_removal"; 1.169 +ALTER TABLE "event" DROP CONSTRAINT "constr_for_value_less_member_event"; 1.170 +ALTER TABLE "event" DROP CONSTRAINT "constr_for_member_active"; 1.171 +ALTER TABLE "event" DROP CONSTRAINT "constr_for_member_name_updated"; 1.172 +ALTER TABLE "event" DROP CONSTRAINT "constr_for_interest"; 1.173 +ALTER TABLE "event" DROP CONSTRAINT "constr_for_initiator"; 1.174 +ALTER TABLE "event" DROP CONSTRAINT "constr_for_support"; 1.175 +ALTER TABLE "event" DROP CONSTRAINT "constr_for_support_updated"; 1.176 +ALTER TABLE "event" DROP CONSTRAINT "constr_for_suggestion_rated"; 1.177 +ALTER TABLE "event" DROP CONSTRAINT "constr_for_delegation"; 1.178 +ALTER TABLE "event" DROP CONSTRAINT "constr_for_contact"; 1.179 +ALTER TABLE "event" ADD 1.180 + CONSTRAINT "constr_for_issue_state_changed" CHECK ( 1.181 + "event" != 'issue_state_changed' OR ( 1.182 + "posting_id" ISNULL AND 1.183 + "member_id" ISNULL AND 1.184 + "other_member_id" ISNULL AND 1.185 + "scope" ISNULL AND 1.186 + "unit_id" NOTNULL AND 1.187 + "area_id" NOTNULL AND 1.188 + "policy_id" NOTNULL AND 1.189 + "issue_id" NOTNULL AND 1.190 + "state" NOTNULL AND 1.191 + "initiative_id" ISNULL AND 1.192 + "draft_id" ISNULL AND 1.193 + "suggestion_id" ISNULL AND 1.194 + "boolean_value" ISNULL AND 1.195 + "numeric_value" ISNULL AND 1.196 + "text_value" ISNULL AND 1.197 + "old_text_value" ISNULL )); 1.198 +ALTER TABLE "event" ADD 1.199 + CONSTRAINT "constr_for_initiative_creation_or_revocation_or_new_draft" CHECK ( 1.200 + "event" NOT IN ( 1.201 + 'initiative_created_in_new_issue', 1.202 + 'initiative_created_in_existing_issue', 1.203 + 'initiative_revoked', 1.204 + 'new_draft_created' 1.205 + ) OR ( 1.206 + "posting_id" ISNULL AND 1.207 + "member_id" NOTNULL AND 1.208 + "other_member_id" ISNULL AND 1.209 + "scope" ISNULL AND 1.210 + "unit_id" NOTNULL AND 1.211 + "area_id" NOTNULL AND 1.212 + "policy_id" NOTNULL AND 1.213 + "issue_id" NOTNULL AND 1.214 + "state" NOTNULL AND 1.215 + "initiative_id" NOTNULL AND 1.216 + "draft_id" NOTNULL AND 1.217 + "suggestion_id" ISNULL AND 1.218 + "boolean_value" ISNULL AND 1.219 + "numeric_value" ISNULL AND 1.220 + "text_value" ISNULL AND 1.221 + "old_text_value" ISNULL )); 1.222 +ALTER TABLE "event" ADD 1.223 + CONSTRAINT "constr_for_suggestion_creation" CHECK ( 1.224 + "event" != 'suggestion_created' OR ( 1.225 + "posting_id" ISNULL AND 1.226 + "member_id" NOTNULL AND 1.227 + "other_member_id" ISNULL AND 1.228 + "scope" ISNULL AND 1.229 + "unit_id" NOTNULL AND 1.230 + "area_id" NOTNULL AND 1.231 + "policy_id" NOTNULL AND 1.232 + "issue_id" NOTNULL AND 1.233 + "state" NOTNULL AND 1.234 + "initiative_id" NOTNULL AND 1.235 + "draft_id" ISNULL AND 1.236 + "suggestion_id" NOTNULL AND 1.237 + "boolean_value" ISNULL AND 1.238 + "numeric_value" ISNULL AND 1.239 + "text_value" ISNULL AND 1.240 + "old_text_value" ISNULL )); 1.241 +ALTER TABLE "event" ADD 1.242 + CONSTRAINT "constr_for_suggestion_removal" CHECK ( 1.243 + "event" != 'suggestion_deleted' OR ( 1.244 + "posting_id" ISNULL AND 1.245 + "member_id" ISNULL AND 1.246 + "other_member_id" ISNULL AND 1.247 + "scope" ISNULL AND 1.248 + "unit_id" NOTNULL AND 1.249 + "area_id" NOTNULL AND 1.250 + "policy_id" NOTNULL AND 1.251 + "issue_id" NOTNULL AND 1.252 + "state" NOTNULL AND 1.253 + "initiative_id" NOTNULL AND 1.254 + "draft_id" ISNULL AND 1.255 + "suggestion_id" NOTNULL AND 1.256 + "boolean_value" ISNULL AND 1.257 + "numeric_value" ISNULL AND 1.258 + "text_value" ISNULL AND 1.259 + "old_text_value" ISNULL )); 1.260 +ALTER TABLE "event" ADD 1.261 + CONSTRAINT "constr_for_value_less_member_event" CHECK ( 1.262 + "event" NOT IN ( 1.263 + 'member_activated', 1.264 + 'member_deleted', 1.265 + 'member_profile_updated', 1.266 + 'member_image_updated' 1.267 + ) OR ( 1.268 + "posting_id" ISNULL AND 1.269 + "member_id" NOTNULL AND 1.270 + "other_member_id" ISNULL AND 1.271 + "scope" ISNULL AND 1.272 + "unit_id" ISNULL AND 1.273 + "area_id" ISNULL AND 1.274 + "policy_id" ISNULL AND 1.275 + "issue_id" ISNULL AND 1.276 + "state" ISNULL AND 1.277 + "initiative_id" ISNULL AND 1.278 + "draft_id" ISNULL AND 1.279 + "suggestion_id" ISNULL AND 1.280 + "boolean_value" ISNULL AND 1.281 + "numeric_value" ISNULL AND 1.282 + "text_value" ISNULL AND 1.283 + "old_text_value" ISNULL )); 1.284 +ALTER TABLE "event" ADD 1.285 + CONSTRAINT "constr_for_member_active" CHECK ( 1.286 + "event" != 'member_active' OR ( 1.287 + "posting_id" ISNULL AND 1.288 + "member_id" NOTNULL AND 1.289 + "other_member_id" ISNULL AND 1.290 + "scope" ISNULL AND 1.291 + "unit_id" ISNULL AND 1.292 + "area_id" ISNULL AND 1.293 + "policy_id" ISNULL AND 1.294 + "issue_id" ISNULL AND 1.295 + "state" ISNULL AND 1.296 + "initiative_id" ISNULL AND 1.297 + "draft_id" ISNULL AND 1.298 + "suggestion_id" ISNULL AND 1.299 + "boolean_value" NOTNULL AND 1.300 + "numeric_value" ISNULL AND 1.301 + "text_value" ISNULL AND 1.302 + "old_text_value" ISNULL )); 1.303 +ALTER TABLE "event" ADD 1.304 + CONSTRAINT "constr_for_member_name_updated" CHECK ( 1.305 + "event" != 'member_name_updated' OR ( 1.306 + "posting_id" ISNULL AND 1.307 + "member_id" NOTNULL AND 1.308 + "other_member_id" ISNULL AND 1.309 + "scope" ISNULL AND 1.310 + "unit_id" ISNULL AND 1.311 + "area_id" ISNULL AND 1.312 + "policy_id" ISNULL AND 1.313 + "issue_id" ISNULL AND 1.314 + "state" ISNULL AND 1.315 + "initiative_id" ISNULL AND 1.316 + "draft_id" ISNULL AND 1.317 + "suggestion_id" ISNULL AND 1.318 + "boolean_value" ISNULL AND 1.319 + "numeric_value" ISNULL AND 1.320 + "text_value" NOTNULL AND 1.321 + "old_text_value" NOTNULL )); 1.322 +ALTER TABLE "event" ADD 1.323 + CONSTRAINT "constr_for_interest" CHECK ( 1.324 + "event" != 'interest' OR ( 1.325 + "posting_id" ISNULL AND 1.326 + "member_id" NOTNULL AND 1.327 + "other_member_id" ISNULL AND 1.328 + "scope" ISNULL AND 1.329 + "unit_id" NOTNULL AND 1.330 + "area_id" NOTNULL AND 1.331 + "policy_id" NOTNULL AND 1.332 + "issue_id" NOTNULL AND 1.333 + "state" NOTNULL AND 1.334 + "initiative_id" ISNULL AND 1.335 + "draft_id" ISNULL AND 1.336 + "suggestion_id" ISNULL AND 1.337 + "boolean_value" NOTNULL AND 1.338 + "numeric_value" ISNULL AND 1.339 + "text_value" ISNULL AND 1.340 + "old_text_value" ISNULL )); 1.341 +ALTER TABLE "event" ADD 1.342 + CONSTRAINT "constr_for_initiator" CHECK ( 1.343 + "event" != 'initiator' OR ( 1.344 + "posting_id" ISNULL AND 1.345 + "member_id" NOTNULL AND 1.346 + "other_member_id" ISNULL AND 1.347 + "scope" ISNULL AND 1.348 + "unit_id" NOTNULL AND 1.349 + "area_id" NOTNULL AND 1.350 + "policy_id" NOTNULL AND 1.351 + "issue_id" NOTNULL AND 1.352 + "state" NOTNULL AND 1.353 + "initiative_id" NOTNULL AND 1.354 + "draft_id" ISNULL AND 1.355 + "suggestion_id" ISNULL AND 1.356 + "boolean_value" NOTNULL AND 1.357 + "numeric_value" ISNULL AND 1.358 + "text_value" ISNULL AND 1.359 + "old_text_value" ISNULL )); 1.360 +ALTER TABLE "event" ADD 1.361 + CONSTRAINT "constr_for_support" CHECK ( 1.362 + "event" != 'support' OR ( 1.363 + "posting_id" ISNULL AND 1.364 + "member_id" NOTNULL AND 1.365 + "other_member_id" ISNULL AND 1.366 + "scope" ISNULL AND 1.367 + "unit_id" NOTNULL AND 1.368 + "area_id" NOTNULL AND 1.369 + "policy_id" NOTNULL AND 1.370 + "issue_id" NOTNULL AND 1.371 + "state" NOTNULL AND 1.372 + "initiative_id" NOTNULL AND 1.373 + ("draft_id" NOTNULL) = ("boolean_value" = TRUE) AND 1.374 + "suggestion_id" ISNULL AND 1.375 + "boolean_value" NOTNULL AND 1.376 + "numeric_value" ISNULL AND 1.377 + "text_value" ISNULL AND 1.378 + "old_text_value" ISNULL )); 1.379 +ALTER TABLE "event" ADD 1.380 + CONSTRAINT "constr_for_support_updated" CHECK ( 1.381 + "event" != 'support_updated' OR ( 1.382 + "posting_id" ISNULL AND 1.383 + "member_id" NOTNULL AND 1.384 + "other_member_id" ISNULL AND 1.385 + "scope" ISNULL AND 1.386 + "unit_id" NOTNULL AND 1.387 + "area_id" NOTNULL AND 1.388 + "policy_id" NOTNULL AND 1.389 + "issue_id" NOTNULL AND 1.390 + "state" NOTNULL AND 1.391 + "initiative_id" NOTNULL AND 1.392 + "draft_id" NOTNULL AND 1.393 + "suggestion_id" ISNULL AND 1.394 + "boolean_value" ISNULL AND 1.395 + "numeric_value" ISNULL AND 1.396 + "text_value" ISNULL AND 1.397 + "old_text_value" ISNULL )); 1.398 +ALTER TABLE "event" ADD 1.399 + CONSTRAINT "constr_for_suggestion_rated" CHECK ( 1.400 + "event" != 'suggestion_rated' OR ( 1.401 + "posting_id" ISNULL AND 1.402 + "member_id" NOTNULL AND 1.403 + "other_member_id" ISNULL AND 1.404 + "scope" ISNULL AND 1.405 + "unit_id" NOTNULL AND 1.406 + "area_id" NOTNULL AND 1.407 + "policy_id" NOTNULL AND 1.408 + "issue_id" NOTNULL AND 1.409 + "state" NOTNULL AND 1.410 + "initiative_id" NOTNULL AND 1.411 + "draft_id" ISNULL AND 1.412 + "suggestion_id" NOTNULL AND 1.413 + ("boolean_value" NOTNULL) = ("numeric_value" != 0) AND 1.414 + "numeric_value" NOTNULL AND 1.415 + "numeric_value" IN (-2, -1, 0, 1, 2) AND 1.416 + "text_value" ISNULL AND 1.417 + "old_text_value" ISNULL )); 1.418 +ALTER TABLE "event" ADD 1.419 + CONSTRAINT "constr_for_delegation" CHECK ( 1.420 + "event" != 'delegation' OR ( 1.421 + "posting_id" ISNULL AND 1.422 + "member_id" NOTNULL AND 1.423 + (("other_member_id" ISNULL) OR ("boolean_value" = TRUE)) AND 1.424 + "scope" NOTNULL AND 1.425 + "unit_id" NOTNULL AND 1.426 + ("area_id" NOTNULL) = ("scope" != 'unit'::"delegation_scope") AND 1.427 + "policy_id" ISNULL AND 1.428 + ("issue_id" NOTNULL) = ("scope" = 'issue'::"delegation_scope") AND 1.429 + ("state" NOTNULL) = ("scope" = 'issue'::"delegation_scope") AND 1.430 + "initiative_id" ISNULL AND 1.431 + "draft_id" ISNULL AND 1.432 + "suggestion_id" ISNULL AND 1.433 + "boolean_value" NOTNULL AND 1.434 + "numeric_value" ISNULL AND 1.435 + "text_value" ISNULL AND 1.436 + "old_text_value" ISNULL )); 1.437 +ALTER TABLE "event" ADD 1.438 + CONSTRAINT "constr_for_contact" CHECK ( 1.439 + "event" != 'contact' OR ( 1.440 + "posting_id" ISNULL AND 1.441 + "member_id" NOTNULL AND 1.442 + "other_member_id" NOTNULL AND 1.443 + "scope" ISNULL AND 1.444 + "unit_id" ISNULL AND 1.445 + "area_id" ISNULL AND 1.446 + "policy_id" ISNULL AND 1.447 + "issue_id" ISNULL AND 1.448 + "state" ISNULL AND 1.449 + "initiative_id" ISNULL AND 1.450 + "draft_id" ISNULL AND 1.451 + "suggestion_id" ISNULL AND 1.452 + "boolean_value" NOTNULL AND 1.453 + "numeric_value" ISNULL AND 1.454 + "text_value" ISNULL AND 1.455 + "old_text_value" ISNULL )); 1.456 +ALTER TABLE "event" ADD 1.457 + CONSTRAINT "constr_for_posting_created" CHECK ( 1.458 + "event" != 'posting_created' OR ( 1.459 + "posting_id" NOTNULL AND 1.460 + "member_id" NOTNULL AND 1.461 + "other_member_id" ISNULL AND 1.462 + "scope" ISNULL AND 1.463 + "state" ISNULL AND 1.464 + ("area_id" ISNULL OR "unit_id" NOTNULL) AND 1.465 + ("policy_id" NOTNULL) = ("issue_id" NOTNULL) AND 1.466 + ("issue_id" ISNULL OR "area_id" NOTNULL) AND 1.467 + ("state" NOTNULL) = ("issue_id" NOTNULL) AND 1.468 + ("initiative_id" ISNULL OR "issue_id" NOTNULL) AND 1.469 + "draft_id" ISNULL AND 1.470 + ("suggestion_id" ISNULL OR "initiative_id" NOTNULL) AND 1.471 + "boolean_value" ISNULL AND 1.472 + "numeric_value" ISNULL AND 1.473 + "text_value" ISNULL AND 1.474 + "old_text_value" ISNULL )); 1.475 + 1.476 +CREATE INDEX "event_tl_global_idx" ON "event" USING gist ((pstamp("member_id", "id"))); 1.477 +CREATE INDEX "event_tl_unit_idx" ON "event" USING gist ("unit_id", (pstamp("member_id", "id"))) WHERE "unit_id" NOTNULL; 1.478 +CREATE INDEX "event_tl_area_idx" ON "event" USING gist ("area_id", (pstamp("member_id", "id"))) WHERE "area_id" NOTNULL; 1.479 +CREATE INDEX "event_tl_policy_idx" ON "event" USING gist ("policy_id", (pstamp("member_id", "id"))) WHERE "policy_id" NOTNULL; 1.480 +CREATE INDEX "event_tl_issue_idx" ON "event" USING gist ("issue_id", (pstamp("member_id", "id"))) WHERE "issue_id" NOTNULL; 1.481 +CREATE INDEX "event_tl_initiative_idx" ON "event" USING gist ("initiative_id", (pstamp("member_id", "id"))) WHERE "initiative_id" NOTNULL; 1.482 +CREATE INDEX "event_tl_suggestion_idx" ON "event" USING gist ("suggestion_id", (pstamp("member_id", "id"))) WHERE "suggestion_id" NOTNULL; 1.483 + 1.484 +CREATE OR REPLACE FUNCTION "highlight" 1.485 + ( "body_p" TEXT, 1.486 + "query_text_p" TEXT ) 1.487 + RETURNS TEXT 1.488 + LANGUAGE 'plpgsql' IMMUTABLE AS $$ 1.489 + BEGIN 1.490 + RETURN ts_headline( 1.491 + replace(replace("body_p", e'\\', e'\\\\'), '*', e'\\*'), 1.492 + "plainto_tsquery"("query_text_p"), 1.493 + 'StartSel=* StopSel=* HighlightAll=TRUE' ); 1.494 + END; 1.495 + $$; 1.496 + 1.497 +CREATE FUNCTION "to_tsvector"("member") RETURNS TSVECTOR 1.498 + LANGUAGE SQL IMMUTABLE AS $$ SELECT to_tsvector(concat_ws(' ', 1.499 + $1."name", 1.500 + $1."identification" 1.501 + )) $$; 1.502 +CREATE INDEX "member_to_tsvector_idx" ON "member" USING gin 1.503 + (("to_tsvector"("member".*))); 1.504 + 1.505 +CREATE FUNCTION "to_tsvector"("member_profile") RETURNS TSVECTOR 1.506 + LANGUAGE SQL IMMUTABLE AS $$ SELECT to_tsvector(concat_ws(' ', 1.507 + $1."statement", 1.508 + $1."profile_text_data" 1.509 + )) $$; 1.510 +CREATE INDEX "member_profile_to_tsvector_idx" ON "member_profile" USING gin 1.511 + (("to_tsvector"("member_profile".*))); 1.512 + 1.513 +CREATE FUNCTION "to_tsvector"("unit") RETURNS TSVECTOR 1.514 + LANGUAGE SQL IMMUTABLE AS $$ SELECT to_tsvector(concat_ws(' ', 1.515 + $1."name", 1.516 + $1."description" 1.517 + )) $$; 1.518 +CREATE INDEX "unit_to_tsvector_idx" ON "unit" USING gin 1.519 + (("to_tsvector"("unit".*))); 1.520 + 1.521 +CREATE FUNCTION "to_tsvector"("area") RETURNS TSVECTOR 1.522 + LANGUAGE SQL IMMUTABLE AS $$ SELECT to_tsvector(concat_ws(' ', 1.523 + $1."name", 1.524 + $1."description" 1.525 + )) $$; 1.526 +CREATE INDEX "area_to_tsvector_idx" ON "area" USING gin 1.527 + (("to_tsvector"("area".*))); 1.528 + 1.529 +CREATE FUNCTION "to_tsvector"("initiative") RETURNS TSVECTOR 1.530 + LANGUAGE SQL IMMUTABLE AS $$ SELECT to_tsvector(concat_ws(' ', 1.531 + $1."name", 1.532 + $1."content" 1.533 + )) $$; 1.534 +CREATE INDEX "initiative_to_tsvector_idx" ON "initiative" USING gin 1.535 + (("to_tsvector"("initiative".*))); 1.536 + 1.537 +CREATE FUNCTION "to_tsvector"("draft") RETURNS TSVECTOR 1.538 + LANGUAGE SQL IMMUTABLE AS $$ SELECT to_tsvector(concat_ws(' ', 1.539 + $1."content" 1.540 + )) $$; 1.541 +CREATE INDEX "draft_to_tsvector_idx" ON "draft" USING gin 1.542 + (("to_tsvector"("draft".*))); 1.543 + 1.544 +CREATE FUNCTION "to_tsvector"("suggestion") RETURNS TSVECTOR 1.545 + LANGUAGE SQL IMMUTABLE AS $$ SELECT to_tsvector(concat_ws(' ', 1.546 + $1."name", 1.547 + $1."content" 1.548 + )) $$; 1.549 +CREATE INDEX "suggestion_to_tsvector_idx" ON "suggestion" USING gin 1.550 + (("to_tsvector"("suggestion".*))); 1.551 + 1.552 +CREATE FUNCTION "to_tsvector"("direct_voter") RETURNS TSVECTOR 1.553 + LANGUAGE SQL IMMUTABLE AS $$ SELECT to_tsvector(concat_ws(' ', 1.554 + $1."comment" 1.555 + )) $$; 1.556 +CREATE INDEX "direct_voter_to_tsvector_idx" ON "direct_voter" USING gin 1.557 + (("to_tsvector"("direct_voter".*))); 1.558 + 1.559 +CREATE FUNCTION "update_posting_lexeme_trigger"() 1.560 + RETURNS TRIGGER 1.561 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.562 + DECLARE 1.563 + "lexeme_v" TEXT; 1.564 + BEGIN 1.565 + IF TG_OP = 'DELETE' OR TG_OP = 'UPDATE' THEN 1.566 + DELETE FROM "posting_lexeme" WHERE "posting_id" = OLD."id"; 1.567 + END IF; 1.568 + IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN 1.569 + FOR "lexeme_v" IN 1.570 + SELECT regexp_matches[1] 1.571 + FROM regexp_matches(NEW."message", '#[^\s.,;:]+') 1.572 + LOOP 1.573 + INSERT INTO "posting_lexeme" ("posting_id", "author_id", "lexeme") 1.574 + VALUES ( 1.575 + NEW."id", 1.576 + NEW."author_id", 1.577 + "lexeme_v" ) 1.578 + ON CONFLICT ("posting_id", "lexeme") DO NOTHING; 1.579 + END LOOP; 1.580 + END IF; 1.581 + RETURN NULL; 1.582 + END; 1.583 + $$; 1.584 + 1.585 +CREATE TRIGGER "update_posting_lexeme" 1.586 + AFTER INSERT OR UPDATE OR DELETE ON "posting" 1.587 + FOR EACH ROW EXECUTE PROCEDURE "update_posting_lexeme_trigger"(); 1.588 + 1.589 +COMMENT ON FUNCTION "update_posting_lexeme_trigger"() IS 'Implementation of trigger "update_posting_lexeme" on table "posting"'; 1.590 +COMMENT ON TRIGGER "update_posting_lexeme" ON "posting" IS 'Keeps table "posting_lexeme" up to date'; 1.591 + 1.592 +CREATE FUNCTION "write_event_posting_trigger"() 1.593 + RETURNS TRIGGER 1.594 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.595 + BEGIN 1.596 + INSERT INTO "event" ( 1.597 + "event", "posting_id", "member_id", 1.598 + "unit_id", "area_id", "policy_id", 1.599 + "issue_id", "initiative_id", "suggestion_id" 1.600 + ) VALUES ( 1.601 + 'posting_created', NEW."id", NEW."author_id", 1.602 + NEW."unit_id", NEW."area_id", NEW."policy_id", 1.603 + NEW."issue_id", NEW."initiative_id", NEW."suggestion_id" 1.604 + ); 1.605 + RETURN NULL; 1.606 + END; 1.607 + $$; 1.608 + 1.609 +CREATE TRIGGER "write_event_posting" 1.610 + AFTER INSERT ON "posting" FOR EACH ROW EXECUTE PROCEDURE 1.611 + "write_event_posting_trigger"(); 1.612 + 1.613 +COMMENT ON FUNCTION "write_event_posting_trigger"() IS 'Implementation of trigger "write_event_posting" on table "posting"'; 1.614 +COMMENT ON TRIGGER "write_event_posting" ON "posting" IS 'Create entry in "event" table when creating a new posting'; 1.615 + 1.616 +CREATE FUNCTION "file_requires_reference_trigger"() 1.617 + RETURNS TRIGGER 1.618 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.619 + BEGIN 1.620 + IF NOT EXISTS ( 1.621 + SELECT NULL FROM "draft_attachment" WHERE "file_id" = NEW."id" 1.622 + ) THEN 1.623 + RAISE EXCEPTION 'Cannot create an unreferenced file.' USING 1.624 + ERRCODE = 'integrity_constraint_violation', 1.625 + HINT = 'Create file and its reference in another table within the same transaction.'; 1.626 + END IF; 1.627 + RETURN NULL; 1.628 + END; 1.629 + $$; 1.630 + 1.631 +CREATE CONSTRAINT TRIGGER "file_requires_reference" 1.632 + AFTER INSERT OR UPDATE ON "file" DEFERRABLE INITIALLY DEFERRED 1.633 + FOR EACH ROW EXECUTE PROCEDURE 1.634 + "file_requires_reference_trigger"(); 1.635 + 1.636 +COMMENT ON FUNCTION "file_requires_reference_trigger"() IS 'Implementation of trigger "file_requires_reference" on table "file"'; 1.637 +COMMENT ON TRIGGER "file_requires_reference" ON "file" IS 'Ensure that files are always referenced'; 1.638 + 1.639 +CREATE FUNCTION "last_reference_deletes_file_trigger"() 1.640 + RETURNS TRIGGER 1.641 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.642 + DECLARE 1.643 + "reference_lost" BOOLEAN; 1.644 + BEGIN 1.645 + IF TG_OP = 'DELETE' THEN 1.646 + "reference_lost" := TRUE; 1.647 + ELSE 1.648 + "reference_lost" := NEW."file_id" != OLD."file_id"; 1.649 + END IF; 1.650 + IF 1.651 + "reference_lost" AND NOT EXISTS ( 1.652 + SELECT NULL FROM "draft_attachment" WHERE "file_id" = OLD."file_id" 1.653 + ) 1.654 + THEN 1.655 + DELETE FROM "file" WHERE "id" = OLD."file_id"; 1.656 + END IF; 1.657 + RETURN NULL; 1.658 + END; 1.659 + $$; 1.660 + 1.661 +CREATE CONSTRAINT TRIGGER "last_reference_deletes_file" 1.662 + AFTER UPDATE OR DELETE ON "draft_attachment" DEFERRABLE INITIALLY DEFERRED 1.663 + FOR EACH ROW EXECUTE PROCEDURE 1.664 + "last_reference_deletes_file_trigger"(); 1.665 + 1.666 +COMMENT ON FUNCTION "last_reference_deletes_file_trigger"() IS 'Implementation of trigger "last_reference_deletes_file" on table "draft_attachment"'; 1.667 +COMMENT ON TRIGGER "last_reference_deletes_file" ON "draft_attachment" IS 'Removing the last reference to a file deletes the file'; 1.668 + 1.669 +CREATE OR REPLACE FUNCTION "copy_current_draft_data" 1.670 + ("initiative_id_p" "initiative"."id"%TYPE ) 1.671 + RETURNS VOID 1.672 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.673 + BEGIN 1.674 + PERFORM NULL FROM "initiative" WHERE "id" = "initiative_id_p" 1.675 + FOR UPDATE; 1.676 + UPDATE "initiative" SET 1.677 + "location" = "draft"."location", 1.678 + "content" = "draft"."content" 1.679 + FROM "current_draft" AS "draft" 1.680 + WHERE "initiative"."id" = "initiative_id_p" 1.681 + AND "draft"."initiative_id" = "initiative_id_p"; 1.682 + END; 1.683 + $$; 1.684 + 1.685 +CREATE VIEW "follower" AS 1.686 + SELECT 1.687 + "id" AS "follower_id", 1.688 + ( SELECT ARRAY["member"."id"] || array_agg("contact"."other_member_id") 1.689 + FROM "contact" 1.690 + WHERE "contact"."member_id" = "member"."id" AND "contact"."following" ) 1.691 + AS "following_ids" 1.692 + FROM "member"; 1.693 + 1.694 +COMMENT ON VIEW "follower" IS 'Provides the contacts of each member that are being followed (including the member itself) as an array of IDs'; 1.695 + 1.696 +CREATE OR REPLACE FUNCTION "check_issue" 1.697 + ( "issue_id_p" "issue"."id"%TYPE, 1.698 + "persist" "check_issue_persistence" ) 1.699 + RETURNS "check_issue_persistence" 1.700 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.701 + DECLARE 1.702 + "issue_row" "issue"%ROWTYPE; 1.703 + "last_calculated_v" "snapshot"."calculated"%TYPE; 1.704 + "policy_row" "policy"%ROWTYPE; 1.705 + "initiative_row" "initiative"%ROWTYPE; 1.706 + "state_v" "issue_state"; 1.707 + BEGIN 1.708 + PERFORM "require_transaction_isolation"(); 1.709 + IF "persist" ISNULL THEN 1.710 + SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p" 1.711 + FOR UPDATE; 1.712 + SELECT "calculated" INTO "last_calculated_v" 1.713 + FROM "snapshot" JOIN "snapshot_issue" 1.714 + ON "snapshot"."id" = "snapshot_issue"."snapshot_id" 1.715 + WHERE "snapshot_issue"."issue_id" = "issue_id_p" 1.716 + ORDER BY "snapshot"."id" DESC; 1.717 + IF "issue_row"."closed" NOTNULL THEN 1.718 + RETURN NULL; 1.719 + END IF; 1.720 + "persist"."state" := "issue_row"."state"; 1.721 + IF 1.722 + ( "issue_row"."state" = 'admission' AND "last_calculated_v" >= 1.723 + "issue_row"."created" + "issue_row"."max_admission_time" ) OR 1.724 + ( "issue_row"."state" = 'discussion' AND now() >= 1.725 + "issue_row"."accepted" + "issue_row"."discussion_time" ) OR 1.726 + ( "issue_row"."state" = 'verification' AND now() >= 1.727 + "issue_row"."half_frozen" + "issue_row"."verification_time" ) OR 1.728 + ( "issue_row"."state" = 'voting' AND now() >= 1.729 + "issue_row"."fully_frozen" + "issue_row"."voting_time" ) 1.730 + THEN 1.731 + "persist"."phase_finished" := TRUE; 1.732 + ELSE 1.733 + "persist"."phase_finished" := FALSE; 1.734 + END IF; 1.735 + IF 1.736 + NOT EXISTS ( 1.737 + -- all initiatives are revoked 1.738 + SELECT NULL FROM "initiative" 1.739 + WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL 1.740 + ) AND ( 1.741 + -- and issue has not been accepted yet 1.742 + "persist"."state" = 'admission' OR 1.743 + -- or verification time has elapsed 1.744 + ( "persist"."state" = 'verification' AND 1.745 + "persist"."phase_finished" ) OR 1.746 + -- or no initiatives have been revoked lately 1.747 + NOT EXISTS ( 1.748 + SELECT NULL FROM "initiative" 1.749 + WHERE "issue_id" = "issue_id_p" 1.750 + AND now() < "revoked" + "issue_row"."verification_time" 1.751 + ) 1.752 + ) 1.753 + THEN 1.754 + "persist"."issue_revoked" := TRUE; 1.755 + ELSE 1.756 + "persist"."issue_revoked" := FALSE; 1.757 + END IF; 1.758 + IF "persist"."phase_finished" OR "persist"."issue_revoked" THEN 1.759 + UPDATE "issue" SET "phase_finished" = now() 1.760 + WHERE "id" = "issue_row"."id"; 1.761 + RETURN "persist"; 1.762 + ELSIF 1.763 + "persist"."state" IN ('admission', 'discussion', 'verification') 1.764 + THEN 1.765 + RETURN "persist"; 1.766 + ELSE 1.767 + RETURN NULL; 1.768 + END IF; 1.769 + END IF; 1.770 + IF 1.771 + "persist"."state" IN ('admission', 'discussion', 'verification') AND 1.772 + coalesce("persist"."snapshot_created", FALSE) = FALSE 1.773 + THEN 1.774 + IF "persist"."state" != 'admission' THEN 1.775 + PERFORM "take_snapshot"("issue_id_p"); 1.776 + PERFORM "finish_snapshot"("issue_id_p"); 1.777 + ELSE 1.778 + UPDATE "issue" SET "issue_quorum" = "issue_quorum"."issue_quorum" 1.779 + FROM "issue_quorum" 1.780 + WHERE "id" = "issue_id_p" 1.781 + AND "issue_quorum"."issue_id" = "issue_id_p"; 1.782 + END IF; 1.783 + "persist"."snapshot_created" = TRUE; 1.784 + IF "persist"."phase_finished" THEN 1.785 + IF "persist"."state" = 'admission' THEN 1.786 + UPDATE "issue" SET "admission_snapshot_id" = "latest_snapshot_id" 1.787 + WHERE "id" = "issue_id_p"; 1.788 + ELSIF "persist"."state" = 'discussion' THEN 1.789 + UPDATE "issue" SET "half_freeze_snapshot_id" = "latest_snapshot_id" 1.790 + WHERE "id" = "issue_id_p"; 1.791 + ELSIF "persist"."state" = 'verification' THEN 1.792 + UPDATE "issue" SET "full_freeze_snapshot_id" = "latest_snapshot_id" 1.793 + WHERE "id" = "issue_id_p"; 1.794 + SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"; 1.795 + FOR "initiative_row" IN 1.796 + SELECT * FROM "initiative" 1.797 + WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL 1.798 + FOR UPDATE 1.799 + LOOP 1.800 + IF 1.801 + "initiative_row"."polling" OR 1.802 + "initiative_row"."satisfied_supporter_count" >= 1.803 + "issue_row"."initiative_quorum" 1.804 + THEN 1.805 + UPDATE "initiative" SET "admitted" = TRUE 1.806 + WHERE "id" = "initiative_row"."id"; 1.807 + ELSE 1.808 + UPDATE "initiative" SET "admitted" = FALSE 1.809 + WHERE "id" = "initiative_row"."id"; 1.810 + END IF; 1.811 + END LOOP; 1.812 + END IF; 1.813 + END IF; 1.814 + RETURN "persist"; 1.815 + END IF; 1.816 + IF 1.817 + "persist"."state" IN ('admission', 'discussion', 'verification') AND 1.818 + coalesce("persist"."harmonic_weights_set", FALSE) = FALSE 1.819 + THEN 1.820 + PERFORM "set_harmonic_initiative_weights"("issue_id_p"); 1.821 + "persist"."harmonic_weights_set" = TRUE; 1.822 + IF 1.823 + "persist"."phase_finished" OR 1.824 + "persist"."issue_revoked" OR 1.825 + "persist"."state" = 'admission' 1.826 + THEN 1.827 + RETURN "persist"; 1.828 + ELSE 1.829 + RETURN NULL; 1.830 + END IF; 1.831 + END IF; 1.832 + IF "persist"."issue_revoked" THEN 1.833 + IF "persist"."state" = 'admission' THEN 1.834 + "state_v" := 'canceled_revoked_before_accepted'; 1.835 + ELSIF "persist"."state" = 'discussion' THEN 1.836 + "state_v" := 'canceled_after_revocation_during_discussion'; 1.837 + ELSIF "persist"."state" = 'verification' THEN 1.838 + "state_v" := 'canceled_after_revocation_during_verification'; 1.839 + END IF; 1.840 + UPDATE "issue" SET 1.841 + "state" = "state_v", 1.842 + "closed" = "phase_finished", 1.843 + "phase_finished" = NULL 1.844 + WHERE "id" = "issue_id_p"; 1.845 + RETURN NULL; 1.846 + END IF; 1.847 + IF "persist"."state" = 'admission' THEN 1.848 + SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p" 1.849 + FOR UPDATE; 1.850 + IF "issue_row"."phase_finished" NOTNULL THEN 1.851 + UPDATE "issue" SET 1.852 + "state" = 'canceled_issue_not_accepted', 1.853 + "closed" = "phase_finished", 1.854 + "phase_finished" = NULL 1.855 + WHERE "id" = "issue_id_p"; 1.856 + END IF; 1.857 + RETURN NULL; 1.858 + END IF; 1.859 + IF "persist"."phase_finished" THEN 1.860 + IF "persist"."state" = 'discussion' THEN 1.861 + UPDATE "issue" SET 1.862 + "state" = 'verification', 1.863 + "half_frozen" = "phase_finished", 1.864 + "phase_finished" = NULL 1.865 + WHERE "id" = "issue_id_p"; 1.866 + RETURN NULL; 1.867 + END IF; 1.868 + IF "persist"."state" = 'verification' THEN 1.869 + SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p" 1.870 + FOR UPDATE; 1.871 + SELECT * INTO "policy_row" FROM "policy" 1.872 + WHERE "id" = "issue_row"."policy_id"; 1.873 + IF EXISTS ( 1.874 + SELECT NULL FROM "initiative" 1.875 + WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE 1.876 + ) THEN 1.877 + UPDATE "issue" SET 1.878 + "state" = 'voting', 1.879 + "fully_frozen" = "phase_finished", 1.880 + "phase_finished" = NULL 1.881 + WHERE "id" = "issue_id_p"; 1.882 + ELSE 1.883 + UPDATE "issue" SET 1.884 + "state" = 'canceled_no_initiative_admitted', 1.885 + "fully_frozen" = "phase_finished", 1.886 + "closed" = "phase_finished", 1.887 + "phase_finished" = NULL 1.888 + WHERE "id" = "issue_id_p"; 1.889 + -- NOTE: The following DELETE statements have effect only when 1.890 + -- issue state has been manipulated 1.891 + DELETE FROM "direct_voter" WHERE "issue_id" = "issue_id_p"; 1.892 + DELETE FROM "delegating_voter" WHERE "issue_id" = "issue_id_p"; 1.893 + DELETE FROM "battle" WHERE "issue_id" = "issue_id_p"; 1.894 + END IF; 1.895 + RETURN NULL; 1.896 + END IF; 1.897 + IF "persist"."state" = 'voting' THEN 1.898 + IF coalesce("persist"."closed_voting", FALSE) = FALSE THEN 1.899 + PERFORM "close_voting"("issue_id_p"); 1.900 + "persist"."closed_voting" = TRUE; 1.901 + RETURN "persist"; 1.902 + END IF; 1.903 + PERFORM "calculate_ranks"("issue_id_p"); 1.904 + RETURN NULL; 1.905 + END IF; 1.906 + END IF; 1.907 + RAISE WARNING 'should not happen'; 1.908 + RETURN NULL; 1.909 + END; 1.910 + $$; 1.911 + 1.912 +CREATE OR REPLACE FUNCTION "check_everything"() 1.913 + RETURNS VOID 1.914 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.915 + DECLARE 1.916 + "area_id_v" "area"."id"%TYPE; 1.917 + "snapshot_id_v" "snapshot"."id"%TYPE; 1.918 + "issue_id_v" "issue"."id"%TYPE; 1.919 + "persist_v" "check_issue_persistence"; 1.920 + BEGIN 1.921 + RAISE WARNING 'Function "check_everything" should only be used for development and debugging purposes'; 1.922 + DELETE FROM "expired_session"; 1.923 + DELETE FROM "expired_token"; 1.924 + DELETE FROM "unused_snapshot"; 1.925 + PERFORM "check_activity"(); 1.926 + PERFORM "calculate_member_counts"(); 1.927 + FOR "area_id_v" IN SELECT "id" FROM "area_with_unaccepted_issues" LOOP 1.928 + SELECT "take_snapshot"(NULL, "area_id_v") INTO "snapshot_id_v"; 1.929 + PERFORM "finish_snapshot"("issue_id") FROM "snapshot_issue" 1.930 + WHERE "snapshot_id" = "snapshot_id_v"; 1.931 + LOOP 1.932 + EXIT WHEN "issue_admission"("area_id_v") = FALSE; 1.933 + END LOOP; 1.934 + END LOOP; 1.935 + FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP 1.936 + "persist_v" := NULL; 1.937 + LOOP 1.938 + "persist_v" := "check_issue"("issue_id_v", "persist_v"); 1.939 + EXIT WHEN "persist_v" ISNULL; 1.940 + END LOOP; 1.941 + END LOOP; 1.942 + DELETE FROM "unused_snapshot"; 1.943 + RETURN; 1.944 + END; 1.945 + $$; 1.946 + 1.947 +CREATE OR REPLACE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE) 1.948 + RETURNS VOID 1.949 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.950 + BEGIN 1.951 + UPDATE "member" SET 1.952 + "last_login" = NULL, 1.953 + "last_delegation_check" = NULL, 1.954 + "login" = NULL, 1.955 + "password" = NULL, 1.956 + "authority" = NULL, 1.957 + "authority_uid" = NULL, 1.958 + "authority_login" = NULL, 1.959 + "deleted" = coalesce("deleted", now()), 1.960 + "locked" = TRUE, 1.961 + "active" = FALSE, 1.962 + "notify_email" = NULL, 1.963 + "notify_email_unconfirmed" = NULL, 1.964 + "notify_email_secret" = NULL, 1.965 + "notify_email_secret_expiry" = NULL, 1.966 + "notify_email_lock_expiry" = NULL, 1.967 + "disable_notifications" = TRUE, 1.968 + "notification_counter" = DEFAULT, 1.969 + "notification_sample_size" = 0, 1.970 + "notification_dow" = NULL, 1.971 + "notification_hour" = NULL, 1.972 + "notification_sent" = NULL, 1.973 + "login_recovery_expiry" = NULL, 1.974 + "password_reset_secret" = NULL, 1.975 + "password_reset_secret_expiry" = NULL, 1.976 + "location" = NULL 1.977 + WHERE "id" = "member_id_p"; 1.978 + DELETE FROM "member_settings" WHERE "member_id" = "member_id_p"; 1.979 + DELETE FROM "member_profile" WHERE "member_id" = "member_id_p"; 1.980 + DELETE FROM "rendered_member_statement" WHERE "member_id" = "member_id_p"; 1.981 + DELETE FROM "member_image" WHERE "member_id" = "member_id_p"; 1.982 + DELETE FROM "contact" WHERE "member_id" = "member_id_p"; 1.983 + DELETE FROM "ignored_member" WHERE "member_id" = "member_id_p"; 1.984 + DELETE FROM "session" WHERE "member_id" = "member_id_p"; 1.985 + DELETE FROM "member_application" WHERE "member_id" = "member_id_p"; 1.986 + DELETE FROM "token" WHERE "member_id" = "member_id_p"; 1.987 + DELETE FROM "subscription" WHERE "member_id" = "member_id_p"; 1.988 + DELETE FROM "ignored_area" WHERE "member_id" = "member_id_p"; 1.989 + DELETE FROM "ignored_initiative" WHERE "member_id" = "member_id_p"; 1.990 + DELETE FROM "delegation" WHERE "truster_id" = "member_id_p"; 1.991 + DELETE FROM "non_voter" WHERE "member_id" = "member_id_p"; 1.992 + DELETE FROM "direct_voter" USING "issue" 1.993 + WHERE "direct_voter"."issue_id" = "issue"."id" 1.994 + AND "issue"."closed" ISNULL 1.995 + AND "member_id" = "member_id_p"; 1.996 + DELETE FROM "notification_initiative_sent" WHERE "member_id" = "member_id_p"; 1.997 + RETURN; 1.998 + END; 1.999 + $$; 1.1000 + 1.1001 +CREATE OR REPLACE FUNCTION "delete_private_data"() 1.1002 + RETURNS VOID 1.1003 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.1004 + BEGIN 1.1005 + DELETE FROM "temporary_transaction_data"; 1.1006 + DELETE FROM "temporary_suggestion_counts"; 1.1007 + DELETE FROM "member" WHERE "activated" ISNULL; 1.1008 + UPDATE "member" SET 1.1009 + "invite_code" = NULL, 1.1010 + "invite_code_expiry" = NULL, 1.1011 + "admin_comment" = NULL, 1.1012 + "last_login" = NULL, 1.1013 + "last_delegation_check" = NULL, 1.1014 + "login" = NULL, 1.1015 + "password" = NULL, 1.1016 + "authority" = NULL, 1.1017 + "authority_uid" = NULL, 1.1018 + "authority_login" = NULL, 1.1019 + "lang" = NULL, 1.1020 + "notify_email" = NULL, 1.1021 + "notify_email_unconfirmed" = NULL, 1.1022 + "notify_email_secret" = NULL, 1.1023 + "notify_email_secret_expiry" = NULL, 1.1024 + "notify_email_lock_expiry" = NULL, 1.1025 + "disable_notifications" = TRUE, 1.1026 + "notification_counter" = DEFAULT, 1.1027 + "notification_sample_size" = 0, 1.1028 + "notification_dow" = NULL, 1.1029 + "notification_hour" = NULL, 1.1030 + "notification_sent" = NULL, 1.1031 + "login_recovery_expiry" = NULL, 1.1032 + "password_reset_secret" = NULL, 1.1033 + "password_reset_secret_expiry" = NULL, 1.1034 + "location" = NULL; 1.1035 + DELETE FROM "verification"; 1.1036 + DELETE FROM "member_settings"; 1.1037 + DELETE FROM "member_useterms"; 1.1038 + DELETE FROM "member_profile"; 1.1039 + DELETE FROM "rendered_member_statement"; 1.1040 + DELETE FROM "member_image"; 1.1041 + DELETE FROM "contact"; 1.1042 + DELETE FROM "ignored_member"; 1.1043 + DELETE FROM "session"; 1.1044 + DELETE FROM "system_application"; 1.1045 + DELETE FROM "system_application_redirect_uri"; 1.1046 + DELETE FROM "dynamic_application_scope"; 1.1047 + DELETE FROM "member_application"; 1.1048 + DELETE FROM "token"; 1.1049 + DELETE FROM "subscription"; 1.1050 + DELETE FROM "ignored_area"; 1.1051 + DELETE FROM "ignored_initiative"; 1.1052 + DELETE FROM "non_voter"; 1.1053 + DELETE FROM "direct_voter" USING "issue" 1.1054 + WHERE "direct_voter"."issue_id" = "issue"."id" 1.1055 + AND "issue"."closed" ISNULL; 1.1056 + DELETE FROM "event_processed"; 1.1057 + DELETE FROM "notification_initiative_sent"; 1.1058 + DELETE FROM "newsletter"; 1.1059 + RETURN; 1.1060 + END; 1.1061 + $$; 1.1062 + 1.1063 +CREATE VIEW "member_eligible_to_be_notified" AS 1.1064 + SELECT * FROM "member" 1.1065 + WHERE "activated" NOTNULL AND "locked" = FALSE; 1.1066 + 1.1067 +COMMENT ON VIEW "member_eligible_to_be_notified" IS 'Filtered "member" table containing only activated and non-locked members (used as helper view for "member_to_notify" and "newsletter_to_send")'; 1.1068 + 1.1069 +CREATE VIEW "member_to_notify" AS 1.1070 + SELECT * FROM "member_eligible_to_be_notified" 1.1071 + WHERE "disable_notifications" = FALSE; 1.1072 + 1.1073 +COMMENT ON VIEW "member_to_notify" IS 'Filtered "member" table containing only members that are eligible to and wish to receive notifications; NOTE: "notify_email" may still be NULL and might need to be checked by frontend (this allows other means of messaging)'; 1.1074 + 1.1075 +CREATE VIEW "area_with_unaccepted_issues" AS 1.1076 + SELECT DISTINCT ON ("area"."id") "area".* 1.1077 + FROM "area" JOIN "issue" ON "area"."id" = "issue"."area_id" 1.1078 + WHERE "issue"."state" = 'admission'; 1.1079 + 1.1080 +COMMENT ON VIEW "area_with_unaccepted_issues" IS 'All areas with unaccepted open issues (needed for issue admission system)'; 1.1081 + 1.1082 +CREATE VIEW "opening_draft" AS 1.1083 + SELECT DISTINCT ON ("initiative_id") * FROM "draft" 1.1084 + ORDER BY "initiative_id", "id"; 1.1085 + 1.1086 +COMMENT ON VIEW "opening_draft" IS 'First drafts of all initiatives'; 1.1087 + 1.1088 +CREATE VIEW "current_draft" AS 1.1089 + SELECT DISTINCT ON ("initiative_id") * FROM "draft" 1.1090 + ORDER BY "initiative_id", "id" DESC; 1.1091 + 1.1092 +COMMENT ON VIEW "current_draft" IS 'All latest drafts for each initiative'; 1.1093 + 1.1094 +CREATE VIEW "member_contingent" AS 1.1095 + SELECT 1.1096 + "member"."id" AS "member_id", 1.1097 + "contingent"."polling", 1.1098 + "contingent"."time_frame", 1.1099 + CASE WHEN "contingent"."text_entry_limit" NOTNULL THEN 1.1100 + ( 1.1101 + SELECT count(1) FROM "draft" 1.1102 + JOIN "initiative" ON "initiative"."id" = "draft"."initiative_id" 1.1103 + WHERE "draft"."author_id" = "member"."id" 1.1104 + AND "initiative"."polling" = "contingent"."polling" 1.1105 + AND "draft"."created" > now() - "contingent"."time_frame" 1.1106 + ) + ( 1.1107 + SELECT count(1) FROM "suggestion" 1.1108 + JOIN "initiative" ON "initiative"."id" = "suggestion"."initiative_id" 1.1109 + WHERE "suggestion"."author_id" = "member"."id" 1.1110 + AND "contingent"."polling" = FALSE 1.1111 + AND "suggestion"."created" > now() - "contingent"."time_frame" 1.1112 + ) 1.1113 + ELSE NULL END AS "text_entry_count", 1.1114 + "contingent"."text_entry_limit", 1.1115 + CASE WHEN "contingent"."initiative_limit" NOTNULL THEN ( 1.1116 + SELECT count(1) FROM "opening_draft" AS "draft" 1.1117 + JOIN "initiative" ON "initiative"."id" = "draft"."initiative_id" 1.1118 + WHERE "draft"."author_id" = "member"."id" 1.1119 + AND "initiative"."polling" = "contingent"."polling" 1.1120 + AND "draft"."created" > now() - "contingent"."time_frame" 1.1121 + ) ELSE NULL END AS "initiative_count", 1.1122 + "contingent"."initiative_limit" 1.1123 + FROM "member" CROSS JOIN "contingent"; 1.1124 + 1.1125 +COMMENT ON VIEW "member_contingent" IS 'Actual counts of text entries and initiatives are calculated per member for each limit in the "contingent" table.'; 1.1126 + 1.1127 +COMMENT ON COLUMN "member_contingent"."text_entry_count" IS 'Only calculated when "text_entry_limit" is not null in the same row'; 1.1128 +COMMENT ON COLUMN "member_contingent"."initiative_count" IS 'Only calculated when "initiative_limit" is not null in the same row'; 1.1129 + 1.1130 +CREATE VIEW "member_contingent_left" AS 1.1131 + SELECT 1.1132 + "member_id", 1.1133 + "polling", 1.1134 + max("text_entry_limit" - "text_entry_count") AS "text_entries_left", 1.1135 + max("initiative_limit" - "initiative_count") AS "initiatives_left" 1.1136 + FROM "member_contingent" GROUP BY "member_id", "polling"; 1.1137 + 1.1138 +COMMENT ON VIEW "member_contingent_left" IS 'Amount of text entries or initiatives which can be posted now instantly by a member. This view should be used by a frontend to determine, if the contingent for posting is exhausted.'; 1.1139 + 1.1140 +CREATE VIEW "scheduled_notification_to_send" AS 1.1141 + SELECT * FROM ( 1.1142 + SELECT 1.1143 + "id" AS "recipient_id", 1.1144 + now() - CASE WHEN "notification_dow" ISNULL THEN 1.1145 + ( "notification_sent"::DATE + CASE 1.1146 + WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour" 1.1147 + THEN 0 ELSE 1 END 1.1148 + )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour" 1.1149 + ELSE 1.1150 + ( "notification_sent"::DATE + 1.1151 + ( 7 + "notification_dow" - 1.1152 + EXTRACT(DOW FROM 1.1153 + ( "notification_sent"::DATE + CASE 1.1154 + WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour" 1.1155 + THEN 0 ELSE 1 END 1.1156 + )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour" 1.1157 + )::INTEGER 1.1158 + ) % 7 + 1.1159 + CASE 1.1160 + WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour" 1.1161 + THEN 0 ELSE 1 1.1162 + END 1.1163 + )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour" 1.1164 + END AS "pending" 1.1165 + FROM ( 1.1166 + SELECT 1.1167 + "id", 1.1168 + COALESCE("notification_sent", "activated") AS "notification_sent", 1.1169 + "notification_dow", 1.1170 + "notification_hour" 1.1171 + FROM "member_to_notify" 1.1172 + WHERE "notification_hour" NOTNULL 1.1173 + ) AS "subquery1" 1.1174 + ) AS "subquery2" 1.1175 + WHERE "pending" > '0'::INTERVAL; 1.1176 + 1.1177 +COMMENT ON VIEW "scheduled_notification_to_send" IS 'Set of members where a scheduled notification mail is pending'; 1.1178 + 1.1179 +COMMENT ON COLUMN "scheduled_notification_to_send"."recipient_id" IS '"id" of the member who needs to receive a notification mail'; 1.1180 +COMMENT ON COLUMN "scheduled_notification_to_send"."pending" IS 'Duration for which the notification mail has already been pending'; 1.1181 + 1.1182 +CREATE VIEW "newsletter_to_send" AS 1.1183 + SELECT 1.1184 + "member"."id" AS "recipient_id", 1.1185 + "newsletter"."id" AS "newsletter_id", 1.1186 + "newsletter"."published" 1.1187 + FROM "newsletter" CROSS JOIN "member_eligible_to_be_notified" AS "member" 1.1188 + LEFT JOIN "privilege" ON 1.1189 + "privilege"."member_id" = "member"."id" AND 1.1190 + "privilege"."unit_id" = "newsletter"."unit_id" AND 1.1191 + "privilege"."voting_right" = TRUE 1.1192 + LEFT JOIN "subscription" ON 1.1193 + "subscription"."member_id" = "member"."id" AND 1.1194 + "subscription"."unit_id" = "newsletter"."unit_id" 1.1195 + WHERE "newsletter"."published" <= now() 1.1196 + AND "newsletter"."sent" ISNULL 1.1197 + AND ( 1.1198 + "member"."disable_notifications" = FALSE OR 1.1199 + "newsletter"."include_all_members" = TRUE ) 1.1200 + AND ( 1.1201 + "newsletter"."unit_id" ISNULL OR 1.1202 + "privilege"."member_id" NOTNULL OR 1.1203 + "subscription"."member_id" NOTNULL ); 1.1204 + 1.1205 +COMMENT ON VIEW "newsletter_to_send" IS 'List of "newsletter_id"s for each member that are due to be sent out'; 1.1206 + 1.1207 +COMMENT ON COLUMN "newsletter"."published" IS 'Timestamp when the newsletter was supposed to be sent out (can be used for ordering)'; 1.1208 + 1.1209 +SELECT "copy_current_draft_data" ("id") FROM "initiative"; 1.1210 + 1.1211 +END;