liquid_feedback_core
diff update/core-update.v4.1.0-v4.2.1.sql @ 619:63092784fe9d
Removed indices for timeline retrieval (removes dependency on pgConflux); Changed update script to previous version 4.2.0 to enforce particular version of pgConflux; Added update script to skip intermediate dependency on pgConflux
| author | jbe |
|---|---|
| date | Sat Dec 05 04:04:52 2020 +0100 (2020-12-05) |
| parents | |
| children |
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.1.sql Sat Dec 05 04:04:52 2020 +0100 1.3 @@ -0,0 +1,2157 @@ 1.4 +CREATE OR REPLACE VIEW "liquid_feedback_version" AS 1.5 + SELECT * FROM (VALUES ('4.2.0-incomplete-update', 4, 2, -1)) 1.6 + AS "subquery"("string", "major", "minor", "revision"); 1.7 + 1.8 +ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'posting_created'; 1.9 + 1.10 +BEGIN; 1.11 + 1.12 +CREATE OR REPLACE VIEW "liquid_feedback_version" AS 1.13 + SELECT * FROM (VALUES ('4.2.1', 4, 2, 1)) 1.14 + AS "subquery"("string", "major", "minor", "revision"); 1.15 + 1.16 +DROP VIEW "newsletter_to_send"; 1.17 +DROP VIEW "scheduled_notification_to_send"; 1.18 +DROP VIEW "member_contingent_left"; 1.19 +DROP VIEW "member_contingent"; 1.20 +DROP VIEW "expired_snapshot"; 1.21 +DROP VIEW "current_draft"; 1.22 +DROP VIEW "opening_draft"; 1.23 +DROP VIEW "area_with_unaccepted_issues"; 1.24 +DROP VIEW "member_to_notify"; 1.25 +DROP VIEW "member_eligible_to_be_notified"; 1.26 + 1.27 +DROP FUNCTION "text_search_query" (TEXT); 1.28 + 1.29 +ALTER TABLE "system_setting" DROP COLUMN "snapshot_retention"; 1.30 + 1.31 +CREATE TABLE "file" ( 1.32 + "id" SERIAL8 PRIMARY KEY, 1.33 + UNIQUE ("content_type", "hash"), 1.34 + "content_type" TEXT NOT NULL, 1.35 + "hash" TEXT NOT NULL, 1.36 + "data" BYTEA NOT NULL, 1.37 + "preview_content_type" TEXT, 1.38 + "preview_data" BYTEA ); 1.39 + 1.40 +COMMENT ON TABLE "file" IS 'Table holding file contents for draft attachments'; 1.41 + 1.42 +COMMENT ON COLUMN "file"."content_type" IS 'Content type of "data"'; 1.43 +COMMENT ON COLUMN "file"."hash" IS 'Hash of "data" to avoid storing duplicates where content-type and data is identical'; 1.44 +COMMENT ON COLUMN "file"."data" IS 'Binary content'; 1.45 +COMMENT ON COLUMN "file"."preview_content_type" IS 'Content type of "preview_data"'; 1.46 +COMMENT ON COLUMN "file"."preview_data" IS 'Preview (e.g. preview image)'; 1.47 + 1.48 +ALTER TABLE "member" DROP COLUMN "text_search_data"; 1.49 +DROP TRIGGER "update_text_search_data" ON "member"; 1.50 + 1.51 +CREATE INDEX "member_useterms_member_id_contract_identifier" ON "member_useterms" ("member_id", "contract_identifier"); 1.52 + 1.53 +ALTER TABLE "member_profile" DROP COLUMN "text_search_data"; 1.54 +DROP TRIGGER "update_text_search_data" ON "member_profile"; 1.55 + 1.56 +ALTER TABLE "contact" ADD COLUMN "following" BOOLEAN NOT NULL DEFAULT TRUE; 1.57 + 1.58 +COMMENT ON COLUMN "contact"."following" IS 'TRUE = actions of contact are shown in personal timeline'; 1.59 + 1.60 +ALTER TABLE "unit" DROP COLUMN "text_search_data"; 1.61 +DROP TRIGGER "update_text_search_data" ON "unit"; 1.62 + 1.63 +ALTER TABLE "area" DROP COLUMN "text_search_data"; 1.64 +DROP TRIGGER "update_text_search_data" ON "area"; 1.65 + 1.66 +DROP INDEX "issue_accepted_idx"; 1.67 +DROP INDEX "issue_half_frozen_idx"; 1.68 +DROP INDEX "issue_fully_frozen_idx"; 1.69 +ALTER INDEX "issue_created_idx_open" RENAME TO "issue_open_created_idx"; 1.70 +DROP INDEX "issue_closed_idx_canceled"; 1.71 +ALTER INDEX "issue_latest_snapshot_id" RENAME TO "issue_latest_snapshot_id_idx"; 1.72 +ALTER INDEX "issue_admission_snapshot_id" RENAME TO "issue_admission_snapshot_id_idx"; 1.73 +ALTER INDEX "issue_half_freeze_snapshot_id" RENAME TO "issue_half_freeze_snapshot_id_idx"; 1.74 +ALTER INDEX "issue_full_freeze_snapshot_id" RENAME TO "issue_full_freeze_snapshot_id_idx"; 1.75 + 1.76 +ALTER TABLE "initiative" ADD COLUMN "content" TEXT; 1.77 +ALTER TABLE "initiative" DROP COLUMN "text_search_data"; 1.78 +ALTER TABLE "initiative" DROP COLUMN "draft_text_search_data"; 1.79 +DROP INDEX "initiative_revoked_idx"; 1.80 +DROP TRIGGER "update_text_search_data" ON "initiative"; 1.81 + 1.82 +COMMENT ON COLUMN "initiative"."content" IS 'Initiative text (automatically copied from most recent draft)'; 1.83 + 1.84 +ALTER TABLE "battle" DROP CONSTRAINT "initiative_ids_not_equal"; 1.85 +ALTER TABLE "battle" ADD CONSTRAINT "initiative_ids_not_equal" CHECK ( 1.86 + "winning_initiative_id" != "losing_initiative_id" AND 1.87 + ("winning_initiative_id" NOTNULL OR "losing_initiative_id" NOTNULL) ); 1.88 + 1.89 +ALTER TABLE "draft" DROP COLUMN "text_search_data"; 1.90 +DROP TRIGGER "update_text_search_data" ON "draft"; 1.91 + 1.92 +CREATE TABLE "draft_attachment" ( 1.93 + "id" SERIAL8 PRIMARY KEY, 1.94 + "draft_id" INT8 REFERENCES "draft" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.95 + "file_id" INT8 REFERENCES "file" ("id") ON DELETE RESTRICT ON UPDATE CASCADE, 1.96 + "title" TEXT, 1.97 + "description" TEXT ); 1.98 + 1.99 +COMMENT ON TABLE "draft_attachment" IS 'Binary attachments for a draft (images, PDF file, etc.); Implicitly ordered through ''id'' column'; 1.100 + 1.101 +ALTER TABLE "suggestion" DROP COLUMN "text_search_data"; 1.102 +DROP TRIGGER "update_text_search_data" ON "suggestion"; 1.103 + 1.104 +ALTER TABLE "direct_voter" DROP COLUMN "text_search_data"; 1.105 +DROP TRIGGER "update_text_search_data" ON "direct_voter"; 1.106 + 1.107 +CREATE TABLE "posting" ( 1.108 + UNIQUE ("author_id", "id"), -- index needed for foreign-key on table "posting_lexeme" 1.109 + "id" SERIAL8 PRIMARY KEY, 1.110 + "author_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE, 1.111 + "created" TIMESTAMPTZ NOT NULL DEFAULT now(), 1.112 + "message" TEXT NOT NULL, 1.113 + "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.114 + "area_id" INT4, 1.115 + FOREIGN KEY ("unit_id", "area_id") REFERENCES "area" ("unit_id", "id") ON DELETE CASCADE ON UPDATE CASCADE, 1.116 + "policy_id" INT4 REFERENCES "policy" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.117 + "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.118 + FOREIGN KEY ("area_id", "issue_id") REFERENCES "issue" ("area_id", "id") ON DELETE CASCADE ON UPDATE CASCADE, 1.119 + FOREIGN KEY ("policy_id", "issue_id") REFERENCES "issue" ("policy_id", "id") ON DELETE CASCADE ON UPDATE CASCADE, 1.120 + "initiative_id" INT4, 1.121 + "suggestion_id" INT8, 1.122 + -- NOTE: no referential integrity for suggestions because those are 1.123 + -- actually deleted 1.124 + -- FOREIGN KEY ("initiative_id", "suggestion_id") 1.125 + -- REFERENCES "suggestion" ("initiative_id", "id") 1.126 + -- ON DELETE CASCADE ON UPDATE CASCADE, 1.127 + CONSTRAINT "area_requires_unit" CHECK ( 1.128 + "area_id" ISNULL OR "unit_id" NOTNULL ), 1.129 + CONSTRAINT "policy_set_when_issue_set" CHECK ( 1.130 + ("policy_id" NOTNULL) = ("issue_id" NOTNULL) ), 1.131 + CONSTRAINT "issue_requires_area" CHECK ( 1.132 + "issue_id" ISNULL OR "area_id" NOTNULL ), 1.133 + CONSTRAINT "initiative_requires_issue" CHECK ( 1.134 + "initiative_id" ISNULL OR "issue_id" NOTNULL ), 1.135 + CONSTRAINT "suggestion_requires_initiative" CHECK ( 1.136 + "suggestion_id" ISNULL OR "initiative_id" NOTNULL ) ); 1.137 +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.138 + 1.139 +CREATE TABLE "posting_lexeme" ( 1.140 + PRIMARY KEY ("posting_id", "lexeme"), 1.141 + FOREIGN KEY ("posting_id", "author_id") REFERENCES "posting" ("id", "author_id") ON DELETE CASCADE ON UPDATE CASCADE, 1.142 + "posting_id" INT8, 1.143 + "lexeme" TEXT, 1.144 + "author_id" INT4 ); 1.145 + 1.146 +COMMENT ON TABLE "posting_lexeme" IS 'Helper table to allow searches for hashtags.'; 1.147 + 1.148 +ALTER TABLE "event" ADD COLUMN "posting_id" INT8 REFERENCES "posting" ("id") ON DELETE RESTRICT ON UPDATE CASCADE; 1.149 +ALTER TABLE "event" DROP CONSTRAINT "constr_for_issue_state_changed"; 1.150 +ALTER TABLE "event" DROP CONSTRAINT "constr_for_initiative_creation_or_revocation_or_new_draft"; 1.151 +ALTER TABLE "event" DROP CONSTRAINT "constr_for_suggestion_creation"; 1.152 +ALTER TABLE "event" DROP CONSTRAINT "constr_for_suggestion_removal"; 1.153 +ALTER TABLE "event" DROP CONSTRAINT "constr_for_value_less_member_event"; 1.154 +ALTER TABLE "event" DROP CONSTRAINT "constr_for_member_active"; 1.155 +ALTER TABLE "event" DROP CONSTRAINT "constr_for_member_name_updated"; 1.156 +ALTER TABLE "event" DROP CONSTRAINT "constr_for_interest"; 1.157 +ALTER TABLE "event" DROP CONSTRAINT "constr_for_initiator"; 1.158 +ALTER TABLE "event" DROP CONSTRAINT "constr_for_support"; 1.159 +ALTER TABLE "event" DROP CONSTRAINT "constr_for_support_updated"; 1.160 +ALTER TABLE "event" DROP CONSTRAINT "constr_for_suggestion_rated"; 1.161 +ALTER TABLE "event" DROP CONSTRAINT "constr_for_delegation"; 1.162 +ALTER TABLE "event" DROP CONSTRAINT "constr_for_contact"; 1.163 +ALTER TABLE "event" ADD 1.164 + CONSTRAINT "constr_for_issue_state_changed" CHECK ( 1.165 + "event" != 'issue_state_changed' OR ( 1.166 + "posting_id" ISNULL AND 1.167 + "member_id" ISNULL AND 1.168 + "other_member_id" ISNULL AND 1.169 + "scope" ISNULL AND 1.170 + "unit_id" NOTNULL AND 1.171 + "area_id" NOTNULL AND 1.172 + "policy_id" NOTNULL AND 1.173 + "issue_id" NOTNULL AND 1.174 + "state" NOTNULL AND 1.175 + "initiative_id" ISNULL AND 1.176 + "draft_id" ISNULL AND 1.177 + "suggestion_id" ISNULL AND 1.178 + "boolean_value" ISNULL AND 1.179 + "numeric_value" ISNULL AND 1.180 + "text_value" ISNULL AND 1.181 + "old_text_value" ISNULL )); 1.182 +ALTER TABLE "event" ADD 1.183 + CONSTRAINT "constr_for_initiative_creation_or_revocation_or_new_draft" CHECK ( 1.184 + "event" NOT IN ( 1.185 + 'initiative_created_in_new_issue', 1.186 + 'initiative_created_in_existing_issue', 1.187 + 'initiative_revoked', 1.188 + 'new_draft_created' 1.189 + ) OR ( 1.190 + "posting_id" ISNULL AND 1.191 + "member_id" NOTNULL AND 1.192 + "other_member_id" ISNULL AND 1.193 + "scope" ISNULL AND 1.194 + "unit_id" NOTNULL AND 1.195 + "area_id" NOTNULL AND 1.196 + "policy_id" NOTNULL AND 1.197 + "issue_id" NOTNULL AND 1.198 + "state" NOTNULL AND 1.199 + "initiative_id" NOTNULL AND 1.200 + "draft_id" NOTNULL AND 1.201 + "suggestion_id" ISNULL AND 1.202 + "boolean_value" ISNULL AND 1.203 + "numeric_value" ISNULL AND 1.204 + "text_value" ISNULL AND 1.205 + "old_text_value" ISNULL )); 1.206 +ALTER TABLE "event" ADD 1.207 + CONSTRAINT "constr_for_suggestion_creation" CHECK ( 1.208 + "event" != 'suggestion_created' OR ( 1.209 + "posting_id" ISNULL AND 1.210 + "member_id" NOTNULL AND 1.211 + "other_member_id" ISNULL AND 1.212 + "scope" ISNULL AND 1.213 + "unit_id" NOTNULL AND 1.214 + "area_id" NOTNULL AND 1.215 + "policy_id" NOTNULL AND 1.216 + "issue_id" NOTNULL AND 1.217 + "state" NOTNULL AND 1.218 + "initiative_id" NOTNULL AND 1.219 + "draft_id" ISNULL AND 1.220 + "suggestion_id" NOTNULL AND 1.221 + "boolean_value" ISNULL AND 1.222 + "numeric_value" ISNULL AND 1.223 + "text_value" ISNULL AND 1.224 + "old_text_value" ISNULL )); 1.225 +ALTER TABLE "event" ADD 1.226 + CONSTRAINT "constr_for_suggestion_removal" CHECK ( 1.227 + "event" != 'suggestion_deleted' OR ( 1.228 + "posting_id" ISNULL AND 1.229 + "member_id" ISNULL AND 1.230 + "other_member_id" ISNULL AND 1.231 + "scope" ISNULL AND 1.232 + "unit_id" NOTNULL AND 1.233 + "area_id" NOTNULL AND 1.234 + "policy_id" NOTNULL AND 1.235 + "issue_id" NOTNULL AND 1.236 + "state" NOTNULL AND 1.237 + "initiative_id" NOTNULL AND 1.238 + "draft_id" ISNULL AND 1.239 + "suggestion_id" NOTNULL AND 1.240 + "boolean_value" ISNULL AND 1.241 + "numeric_value" ISNULL AND 1.242 + "text_value" ISNULL AND 1.243 + "old_text_value" ISNULL )); 1.244 +ALTER TABLE "event" ADD 1.245 + CONSTRAINT "constr_for_value_less_member_event" CHECK ( 1.246 + "event" NOT IN ( 1.247 + 'member_activated', 1.248 + 'member_deleted', 1.249 + 'member_profile_updated', 1.250 + 'member_image_updated' 1.251 + ) OR ( 1.252 + "posting_id" ISNULL AND 1.253 + "member_id" NOTNULL AND 1.254 + "other_member_id" ISNULL AND 1.255 + "scope" ISNULL AND 1.256 + "unit_id" ISNULL AND 1.257 + "area_id" ISNULL AND 1.258 + "policy_id" ISNULL AND 1.259 + "issue_id" ISNULL AND 1.260 + "state" ISNULL AND 1.261 + "initiative_id" ISNULL AND 1.262 + "draft_id" ISNULL AND 1.263 + "suggestion_id" ISNULL AND 1.264 + "boolean_value" ISNULL AND 1.265 + "numeric_value" ISNULL AND 1.266 + "text_value" ISNULL AND 1.267 + "old_text_value" ISNULL )); 1.268 +ALTER TABLE "event" ADD 1.269 + CONSTRAINT "constr_for_member_active" CHECK ( 1.270 + "event" != 'member_active' OR ( 1.271 + "posting_id" ISNULL AND 1.272 + "member_id" NOTNULL AND 1.273 + "other_member_id" ISNULL AND 1.274 + "scope" ISNULL AND 1.275 + "unit_id" ISNULL AND 1.276 + "area_id" ISNULL AND 1.277 + "policy_id" ISNULL AND 1.278 + "issue_id" ISNULL AND 1.279 + "state" ISNULL AND 1.280 + "initiative_id" ISNULL AND 1.281 + "draft_id" ISNULL AND 1.282 + "suggestion_id" ISNULL AND 1.283 + "boolean_value" NOTNULL AND 1.284 + "numeric_value" ISNULL AND 1.285 + "text_value" ISNULL AND 1.286 + "old_text_value" ISNULL )); 1.287 +ALTER TABLE "event" ADD 1.288 + CONSTRAINT "constr_for_member_name_updated" CHECK ( 1.289 + "event" != 'member_name_updated' OR ( 1.290 + "posting_id" ISNULL AND 1.291 + "member_id" NOTNULL AND 1.292 + "other_member_id" ISNULL AND 1.293 + "scope" ISNULL AND 1.294 + "unit_id" ISNULL AND 1.295 + "area_id" ISNULL AND 1.296 + "policy_id" ISNULL AND 1.297 + "issue_id" ISNULL AND 1.298 + "state" ISNULL AND 1.299 + "initiative_id" ISNULL AND 1.300 + "draft_id" ISNULL AND 1.301 + "suggestion_id" ISNULL AND 1.302 + "boolean_value" ISNULL AND 1.303 + "numeric_value" ISNULL AND 1.304 + "text_value" NOTNULL AND 1.305 + "old_text_value" NOTNULL )); 1.306 +ALTER TABLE "event" ADD 1.307 + CONSTRAINT "constr_for_interest" CHECK ( 1.308 + "event" != 'interest' OR ( 1.309 + "posting_id" ISNULL AND 1.310 + "member_id" NOTNULL AND 1.311 + "other_member_id" ISNULL AND 1.312 + "scope" ISNULL AND 1.313 + "unit_id" NOTNULL AND 1.314 + "area_id" NOTNULL AND 1.315 + "policy_id" NOTNULL AND 1.316 + "issue_id" NOTNULL AND 1.317 + "state" NOTNULL AND 1.318 + "initiative_id" ISNULL AND 1.319 + "draft_id" ISNULL AND 1.320 + "suggestion_id" ISNULL AND 1.321 + "boolean_value" NOTNULL AND 1.322 + "numeric_value" ISNULL AND 1.323 + "text_value" ISNULL AND 1.324 + "old_text_value" ISNULL )); 1.325 +ALTER TABLE "event" ADD 1.326 + CONSTRAINT "constr_for_initiator" CHECK ( 1.327 + "event" != 'initiator' OR ( 1.328 + "posting_id" ISNULL AND 1.329 + "member_id" NOTNULL AND 1.330 + "other_member_id" ISNULL AND 1.331 + "scope" ISNULL AND 1.332 + "unit_id" NOTNULL AND 1.333 + "area_id" NOTNULL AND 1.334 + "policy_id" NOTNULL AND 1.335 + "issue_id" NOTNULL AND 1.336 + "state" NOTNULL AND 1.337 + "initiative_id" NOTNULL AND 1.338 + "draft_id" ISNULL AND 1.339 + "suggestion_id" ISNULL AND 1.340 + "boolean_value" NOTNULL AND 1.341 + "numeric_value" ISNULL AND 1.342 + "text_value" ISNULL AND 1.343 + "old_text_value" ISNULL )); 1.344 +ALTER TABLE "event" ADD 1.345 + CONSTRAINT "constr_for_support" CHECK ( 1.346 + "event" != 'support' OR ( 1.347 + "posting_id" ISNULL AND 1.348 + "member_id" NOTNULL AND 1.349 + "other_member_id" ISNULL AND 1.350 + "scope" ISNULL AND 1.351 + "unit_id" NOTNULL AND 1.352 + "area_id" NOTNULL AND 1.353 + "policy_id" NOTNULL AND 1.354 + "issue_id" NOTNULL AND 1.355 + "state" NOTNULL AND 1.356 + "initiative_id" NOTNULL AND 1.357 + ("draft_id" NOTNULL) = ("boolean_value" = TRUE) AND 1.358 + "suggestion_id" ISNULL AND 1.359 + "boolean_value" NOTNULL AND 1.360 + "numeric_value" ISNULL AND 1.361 + "text_value" ISNULL AND 1.362 + "old_text_value" ISNULL )); 1.363 +ALTER TABLE "event" ADD 1.364 + CONSTRAINT "constr_for_support_updated" CHECK ( 1.365 + "event" != 'support_updated' OR ( 1.366 + "posting_id" ISNULL AND 1.367 + "member_id" NOTNULL AND 1.368 + "other_member_id" ISNULL AND 1.369 + "scope" ISNULL AND 1.370 + "unit_id" NOTNULL AND 1.371 + "area_id" NOTNULL AND 1.372 + "policy_id" NOTNULL AND 1.373 + "issue_id" NOTNULL AND 1.374 + "state" NOTNULL AND 1.375 + "initiative_id" NOTNULL AND 1.376 + "draft_id" NOTNULL AND 1.377 + "suggestion_id" ISNULL AND 1.378 + "boolean_value" ISNULL AND 1.379 + "numeric_value" ISNULL AND 1.380 + "text_value" ISNULL AND 1.381 + "old_text_value" ISNULL )); 1.382 +ALTER TABLE "event" ADD 1.383 + CONSTRAINT "constr_for_suggestion_rated" CHECK ( 1.384 + "event" != 'suggestion_rated' OR ( 1.385 + "posting_id" ISNULL AND 1.386 + "member_id" NOTNULL AND 1.387 + "other_member_id" ISNULL AND 1.388 + "scope" ISNULL AND 1.389 + "unit_id" NOTNULL AND 1.390 + "area_id" NOTNULL AND 1.391 + "policy_id" NOTNULL AND 1.392 + "issue_id" NOTNULL AND 1.393 + "state" NOTNULL AND 1.394 + "initiative_id" NOTNULL AND 1.395 + "draft_id" ISNULL AND 1.396 + "suggestion_id" NOTNULL AND 1.397 + ("boolean_value" NOTNULL) = ("numeric_value" != 0) AND 1.398 + "numeric_value" NOTNULL AND 1.399 + "numeric_value" IN (-2, -1, 0, 1, 2) AND 1.400 + "text_value" ISNULL AND 1.401 + "old_text_value" ISNULL )); 1.402 +ALTER TABLE "event" ADD 1.403 + CONSTRAINT "constr_for_delegation" CHECK ( 1.404 + "event" != 'delegation' OR ( 1.405 + "posting_id" ISNULL AND 1.406 + "member_id" NOTNULL AND 1.407 + (("other_member_id" ISNULL) OR ("boolean_value" = TRUE)) AND 1.408 + "scope" NOTNULL AND 1.409 + "unit_id" NOTNULL AND 1.410 + ("area_id" NOTNULL) = ("scope" != 'unit'::"delegation_scope") AND 1.411 + "policy_id" ISNULL AND 1.412 + ("issue_id" NOTNULL) = ("scope" = 'issue'::"delegation_scope") AND 1.413 + ("state" NOTNULL) = ("scope" = 'issue'::"delegation_scope") AND 1.414 + "initiative_id" ISNULL AND 1.415 + "draft_id" ISNULL AND 1.416 + "suggestion_id" ISNULL AND 1.417 + "boolean_value" NOTNULL AND 1.418 + "numeric_value" ISNULL AND 1.419 + "text_value" ISNULL AND 1.420 + "old_text_value" ISNULL )); 1.421 +ALTER TABLE "event" ADD 1.422 + CONSTRAINT "constr_for_contact" CHECK ( 1.423 + "event" != 'contact' OR ( 1.424 + "posting_id" ISNULL AND 1.425 + "member_id" NOTNULL AND 1.426 + "other_member_id" NOTNULL AND 1.427 + "scope" ISNULL AND 1.428 + "unit_id" ISNULL AND 1.429 + "area_id" ISNULL AND 1.430 + "policy_id" ISNULL AND 1.431 + "issue_id" ISNULL AND 1.432 + "state" ISNULL AND 1.433 + "initiative_id" ISNULL AND 1.434 + "draft_id" ISNULL AND 1.435 + "suggestion_id" ISNULL AND 1.436 + "boolean_value" NOTNULL AND 1.437 + "numeric_value" ISNULL AND 1.438 + "text_value" ISNULL AND 1.439 + "old_text_value" ISNULL )); 1.440 +ALTER TABLE "event" ADD 1.441 + CONSTRAINT "constr_for_posting_created" CHECK ( 1.442 + "event" != 'posting_created' OR ( 1.443 + "posting_id" NOTNULL AND 1.444 + "member_id" NOTNULL AND 1.445 + "other_member_id" ISNULL AND 1.446 + "scope" ISNULL AND 1.447 + "state" ISNULL AND 1.448 + ("area_id" ISNULL OR "unit_id" NOTNULL) AND 1.449 + ("policy_id" NOTNULL) = ("issue_id" NOTNULL) AND 1.450 + ("issue_id" ISNULL OR "area_id" NOTNULL) AND 1.451 + ("state" NOTNULL) = ("issue_id" NOTNULL) AND 1.452 + ("initiative_id" ISNULL OR "issue_id" NOTNULL) AND 1.453 + "draft_id" ISNULL AND 1.454 + ("suggestion_id" ISNULL OR "initiative_id" NOTNULL) AND 1.455 + "boolean_value" ISNULL AND 1.456 + "numeric_value" ISNULL AND 1.457 + "text_value" ISNULL AND 1.458 + "old_text_value" ISNULL )); 1.459 + 1.460 +CREATE OR REPLACE FUNCTION "highlight" 1.461 + ( "body_p" TEXT, 1.462 + "query_text_p" TEXT ) 1.463 + RETURNS TEXT 1.464 + LANGUAGE 'plpgsql' IMMUTABLE AS $$ 1.465 + BEGIN 1.466 + RETURN ts_headline( 1.467 + replace(replace("body_p", e'\\', e'\\\\'), '*', e'\\*'), 1.468 + "plainto_tsquery"("query_text_p"), 1.469 + 'StartSel=* StopSel=* HighlightAll=TRUE' ); 1.470 + END; 1.471 + $$; 1.472 + 1.473 +CREATE FUNCTION "to_tsvector"("member") RETURNS TSVECTOR 1.474 + LANGUAGE SQL IMMUTABLE AS $$ SELECT to_tsvector(concat_ws(' ', 1.475 + $1."name", 1.476 + $1."identification" 1.477 + )) $$; 1.478 +CREATE INDEX "member_to_tsvector_idx" ON "member" USING gin 1.479 + (("to_tsvector"("member".*))); 1.480 + 1.481 +CREATE FUNCTION "to_tsvector"("member_profile") RETURNS TSVECTOR 1.482 + LANGUAGE SQL IMMUTABLE AS $$ SELECT to_tsvector(concat_ws(' ', 1.483 + $1."statement", 1.484 + $1."profile_text_data" 1.485 + )) $$; 1.486 +CREATE INDEX "member_profile_to_tsvector_idx" ON "member_profile" USING gin 1.487 + (("to_tsvector"("member_profile".*))); 1.488 + 1.489 +CREATE FUNCTION "to_tsvector"("unit") RETURNS TSVECTOR 1.490 + LANGUAGE SQL IMMUTABLE AS $$ SELECT to_tsvector(concat_ws(' ', 1.491 + $1."name", 1.492 + $1."description" 1.493 + )) $$; 1.494 +CREATE INDEX "unit_to_tsvector_idx" ON "unit" USING gin 1.495 + (("to_tsvector"("unit".*))); 1.496 + 1.497 +CREATE FUNCTION "to_tsvector"("area") RETURNS TSVECTOR 1.498 + LANGUAGE SQL IMMUTABLE AS $$ SELECT to_tsvector(concat_ws(' ', 1.499 + $1."name", 1.500 + $1."description" 1.501 + )) $$; 1.502 +CREATE INDEX "area_to_tsvector_idx" ON "area" USING gin 1.503 + (("to_tsvector"("area".*))); 1.504 + 1.505 +CREATE FUNCTION "to_tsvector"("initiative") RETURNS TSVECTOR 1.506 + LANGUAGE SQL IMMUTABLE AS $$ SELECT to_tsvector(concat_ws(' ', 1.507 + $1."name", 1.508 + $1."content" 1.509 + )) $$; 1.510 +CREATE INDEX "initiative_to_tsvector_idx" ON "initiative" USING gin 1.511 + (("to_tsvector"("initiative".*))); 1.512 + 1.513 +CREATE FUNCTION "to_tsvector"("draft") RETURNS TSVECTOR 1.514 + LANGUAGE SQL IMMUTABLE AS $$ SELECT to_tsvector(concat_ws(' ', 1.515 + $1."content" 1.516 + )) $$; 1.517 +CREATE INDEX "draft_to_tsvector_idx" ON "draft" USING gin 1.518 + (("to_tsvector"("draft".*))); 1.519 + 1.520 +CREATE FUNCTION "to_tsvector"("suggestion") RETURNS TSVECTOR 1.521 + LANGUAGE SQL IMMUTABLE AS $$ SELECT to_tsvector(concat_ws(' ', 1.522 + $1."name", 1.523 + $1."content" 1.524 + )) $$; 1.525 +CREATE INDEX "suggestion_to_tsvector_idx" ON "suggestion" USING gin 1.526 + (("to_tsvector"("suggestion".*))); 1.527 + 1.528 +CREATE FUNCTION "to_tsvector"("direct_voter") RETURNS TSVECTOR 1.529 + LANGUAGE SQL IMMUTABLE AS $$ SELECT to_tsvector(concat_ws(' ', 1.530 + $1."comment" 1.531 + )) $$; 1.532 +CREATE INDEX "direct_voter_to_tsvector_idx" ON "direct_voter" USING gin 1.533 + (("to_tsvector"("direct_voter".*))); 1.534 + 1.535 +CREATE FUNCTION "update_posting_lexeme_trigger"() 1.536 + RETURNS TRIGGER 1.537 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.538 + DECLARE 1.539 + "lexeme_v" TEXT; 1.540 + BEGIN 1.541 + IF TG_OP = 'DELETE' OR TG_OP = 'UPDATE' THEN 1.542 + DELETE FROM "posting_lexeme" WHERE "posting_id" = OLD."id"; 1.543 + END IF; 1.544 + IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN 1.545 + FOR "lexeme_v" IN 1.546 + SELECT regexp_matches[1] 1.547 + FROM regexp_matches(NEW."message", '#[^\s.,;:]+') 1.548 + LOOP 1.549 + INSERT INTO "posting_lexeme" ("posting_id", "author_id", "lexeme") 1.550 + VALUES ( 1.551 + NEW."id", 1.552 + NEW."author_id", 1.553 + "lexeme_v" ) 1.554 + ON CONFLICT ("posting_id", "lexeme") DO NOTHING; 1.555 + END LOOP; 1.556 + END IF; 1.557 + RETURN NULL; 1.558 + END; 1.559 + $$; 1.560 + 1.561 +CREATE TRIGGER "update_posting_lexeme" 1.562 + AFTER INSERT OR UPDATE OR DELETE ON "posting" 1.563 + FOR EACH ROW EXECUTE PROCEDURE "update_posting_lexeme_trigger"(); 1.564 + 1.565 +COMMENT ON FUNCTION "update_posting_lexeme_trigger"() IS 'Implementation of trigger "update_posting_lexeme" on table "posting"'; 1.566 +COMMENT ON TRIGGER "update_posting_lexeme" ON "posting" IS 'Keeps table "posting_lexeme" up to date'; 1.567 + 1.568 +CREATE FUNCTION "write_event_posting_trigger"() 1.569 + RETURNS TRIGGER 1.570 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.571 + BEGIN 1.572 + INSERT INTO "event" ( 1.573 + "event", "posting_id", "member_id", 1.574 + "unit_id", "area_id", "policy_id", 1.575 + "issue_id", "initiative_id", "suggestion_id" 1.576 + ) VALUES ( 1.577 + 'posting_created', NEW."id", NEW."author_id", 1.578 + NEW."unit_id", NEW."area_id", NEW."policy_id", 1.579 + NEW."issue_id", NEW."initiative_id", NEW."suggestion_id" 1.580 + ); 1.581 + RETURN NULL; 1.582 + END; 1.583 + $$; 1.584 + 1.585 +CREATE TRIGGER "write_event_posting" 1.586 + AFTER INSERT ON "posting" FOR EACH ROW EXECUTE PROCEDURE 1.587 + "write_event_posting_trigger"(); 1.588 + 1.589 +COMMENT ON FUNCTION "write_event_posting_trigger"() IS 'Implementation of trigger "write_event_posting" on table "posting"'; 1.590 +COMMENT ON TRIGGER "write_event_posting" ON "posting" IS 'Create entry in "event" table when creating a new posting'; 1.591 + 1.592 +CREATE FUNCTION "file_requires_reference_trigger"() 1.593 + RETURNS TRIGGER 1.594 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.595 + BEGIN 1.596 + IF NOT EXISTS ( 1.597 + SELECT NULL FROM "draft_attachment" WHERE "file_id" = NEW."id" 1.598 + ) THEN 1.599 + RAISE EXCEPTION 'Cannot create an unreferenced file.' USING 1.600 + ERRCODE = 'integrity_constraint_violation', 1.601 + HINT = 'Create file and its reference in another table within the same transaction.'; 1.602 + END IF; 1.603 + RETURN NULL; 1.604 + END; 1.605 + $$; 1.606 + 1.607 +CREATE CONSTRAINT TRIGGER "file_requires_reference" 1.608 + AFTER INSERT OR UPDATE ON "file" DEFERRABLE INITIALLY DEFERRED 1.609 + FOR EACH ROW EXECUTE PROCEDURE 1.610 + "file_requires_reference_trigger"(); 1.611 + 1.612 +COMMENT ON FUNCTION "file_requires_reference_trigger"() IS 'Implementation of trigger "file_requires_reference" on table "file"'; 1.613 +COMMENT ON TRIGGER "file_requires_reference" ON "file" IS 'Ensure that files are always referenced'; 1.614 + 1.615 +CREATE FUNCTION "last_reference_deletes_file_trigger"() 1.616 + RETURNS TRIGGER 1.617 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.618 + DECLARE 1.619 + "reference_lost" BOOLEAN; 1.620 + BEGIN 1.621 + IF TG_OP = 'DELETE' THEN 1.622 + "reference_lost" := TRUE; 1.623 + ELSE 1.624 + "reference_lost" := NEW."file_id" != OLD."file_id"; 1.625 + END IF; 1.626 + IF 1.627 + "reference_lost" AND NOT EXISTS ( 1.628 + SELECT NULL FROM "draft_attachment" WHERE "file_id" = OLD."file_id" 1.629 + ) 1.630 + THEN 1.631 + DELETE FROM "file" WHERE "id" = OLD."file_id"; 1.632 + END IF; 1.633 + RETURN NULL; 1.634 + END; 1.635 + $$; 1.636 + 1.637 +CREATE CONSTRAINT TRIGGER "last_reference_deletes_file" 1.638 + AFTER UPDATE OR DELETE ON "draft_attachment" DEFERRABLE INITIALLY DEFERRED 1.639 + FOR EACH ROW EXECUTE PROCEDURE 1.640 + "last_reference_deletes_file_trigger"(); 1.641 + 1.642 +COMMENT ON FUNCTION "last_reference_deletes_file_trigger"() IS 'Implementation of trigger "last_reference_deletes_file" on table "draft_attachment"'; 1.643 +COMMENT ON TRIGGER "last_reference_deletes_file" ON "draft_attachment" IS 'Removing the last reference to a file deletes the file'; 1.644 + 1.645 +CREATE OR REPLACE FUNCTION "copy_current_draft_data" 1.646 + ("initiative_id_p" "initiative"."id"%TYPE ) 1.647 + RETURNS VOID 1.648 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.649 + BEGIN 1.650 + PERFORM NULL FROM "initiative" WHERE "id" = "initiative_id_p" 1.651 + FOR UPDATE; 1.652 + UPDATE "initiative" SET 1.653 + "location" = "draft"."location", 1.654 + "content" = "draft"."content" 1.655 + FROM "current_draft" AS "draft" 1.656 + WHERE "initiative"."id" = "initiative_id_p" 1.657 + AND "draft"."initiative_id" = "initiative_id_p"; 1.658 + END; 1.659 + $$; 1.660 + 1.661 +CREATE VIEW "follower" AS 1.662 + SELECT 1.663 + "id" AS "follower_id", 1.664 + ( SELECT ARRAY["member"."id"] || array_agg("contact"."other_member_id") 1.665 + FROM "contact" 1.666 + WHERE "contact"."member_id" = "member"."id" AND "contact"."following" ) 1.667 + AS "following_ids" 1.668 + FROM "member"; 1.669 + 1.670 +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.671 + 1.672 +CREATE OR REPLACE FUNCTION "check_issue" 1.673 + ( "issue_id_p" "issue"."id"%TYPE, 1.674 + "persist" "check_issue_persistence" ) 1.675 + RETURNS "check_issue_persistence" 1.676 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.677 + DECLARE 1.678 + "issue_row" "issue"%ROWTYPE; 1.679 + "last_calculated_v" "snapshot"."calculated"%TYPE; 1.680 + "policy_row" "policy"%ROWTYPE; 1.681 + "initiative_row" "initiative"%ROWTYPE; 1.682 + "state_v" "issue_state"; 1.683 + BEGIN 1.684 + PERFORM "require_transaction_isolation"(); 1.685 + IF "persist" ISNULL THEN 1.686 + SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p" 1.687 + FOR UPDATE; 1.688 + SELECT "calculated" INTO "last_calculated_v" 1.689 + FROM "snapshot" JOIN "snapshot_issue" 1.690 + ON "snapshot"."id" = "snapshot_issue"."snapshot_id" 1.691 + WHERE "snapshot_issue"."issue_id" = "issue_id_p" 1.692 + ORDER BY "snapshot"."id" DESC; 1.693 + IF "issue_row"."closed" NOTNULL THEN 1.694 + RETURN NULL; 1.695 + END IF; 1.696 + "persist"."state" := "issue_row"."state"; 1.697 + IF 1.698 + ( "issue_row"."state" = 'admission' AND "last_calculated_v" >= 1.699 + "issue_row"."created" + "issue_row"."max_admission_time" ) OR 1.700 + ( "issue_row"."state" = 'discussion' AND now() >= 1.701 + "issue_row"."accepted" + "issue_row"."discussion_time" ) OR 1.702 + ( "issue_row"."state" = 'verification' AND now() >= 1.703 + "issue_row"."half_frozen" + "issue_row"."verification_time" ) OR 1.704 + ( "issue_row"."state" = 'voting' AND now() >= 1.705 + "issue_row"."fully_frozen" + "issue_row"."voting_time" ) 1.706 + THEN 1.707 + "persist"."phase_finished" := TRUE; 1.708 + ELSE 1.709 + "persist"."phase_finished" := FALSE; 1.710 + END IF; 1.711 + IF 1.712 + NOT EXISTS ( 1.713 + -- all initiatives are revoked 1.714 + SELECT NULL FROM "initiative" 1.715 + WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL 1.716 + ) AND ( 1.717 + -- and issue has not been accepted yet 1.718 + "persist"."state" = 'admission' OR 1.719 + -- or verification time has elapsed 1.720 + ( "persist"."state" = 'verification' AND 1.721 + "persist"."phase_finished" ) OR 1.722 + -- or no initiatives have been revoked lately 1.723 + NOT EXISTS ( 1.724 + SELECT NULL FROM "initiative" 1.725 + WHERE "issue_id" = "issue_id_p" 1.726 + AND now() < "revoked" + "issue_row"."verification_time" 1.727 + ) 1.728 + ) 1.729 + THEN 1.730 + "persist"."issue_revoked" := TRUE; 1.731 + ELSE 1.732 + "persist"."issue_revoked" := FALSE; 1.733 + END IF; 1.734 + IF "persist"."phase_finished" OR "persist"."issue_revoked" THEN 1.735 + UPDATE "issue" SET "phase_finished" = now() 1.736 + WHERE "id" = "issue_row"."id"; 1.737 + RETURN "persist"; 1.738 + ELSIF 1.739 + "persist"."state" IN ('admission', 'discussion', 'verification') 1.740 + THEN 1.741 + RETURN "persist"; 1.742 + ELSE 1.743 + RETURN NULL; 1.744 + END IF; 1.745 + END IF; 1.746 + IF 1.747 + "persist"."state" IN ('admission', 'discussion', 'verification') AND 1.748 + coalesce("persist"."snapshot_created", FALSE) = FALSE 1.749 + THEN 1.750 + IF "persist"."state" != 'admission' THEN 1.751 + PERFORM "take_snapshot"("issue_id_p"); 1.752 + PERFORM "finish_snapshot"("issue_id_p"); 1.753 + ELSE 1.754 + UPDATE "issue" SET "issue_quorum" = "issue_quorum"."issue_quorum" 1.755 + FROM "issue_quorum" 1.756 + WHERE "id" = "issue_id_p" 1.757 + AND "issue_quorum"."issue_id" = "issue_id_p"; 1.758 + END IF; 1.759 + "persist"."snapshot_created" = TRUE; 1.760 + IF "persist"."phase_finished" THEN 1.761 + IF "persist"."state" = 'admission' THEN 1.762 + UPDATE "issue" SET "admission_snapshot_id" = "latest_snapshot_id" 1.763 + WHERE "id" = "issue_id_p"; 1.764 + ELSIF "persist"."state" = 'discussion' THEN 1.765 + UPDATE "issue" SET "half_freeze_snapshot_id" = "latest_snapshot_id" 1.766 + WHERE "id" = "issue_id_p"; 1.767 + ELSIF "persist"."state" = 'verification' THEN 1.768 + UPDATE "issue" SET "full_freeze_snapshot_id" = "latest_snapshot_id" 1.769 + WHERE "id" = "issue_id_p"; 1.770 + SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"; 1.771 + FOR "initiative_row" IN 1.772 + SELECT * FROM "initiative" 1.773 + WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL 1.774 + FOR UPDATE 1.775 + LOOP 1.776 + IF 1.777 + "initiative_row"."polling" OR 1.778 + "initiative_row"."satisfied_supporter_count" >= 1.779 + "issue_row"."initiative_quorum" 1.780 + THEN 1.781 + UPDATE "initiative" SET "admitted" = TRUE 1.782 + WHERE "id" = "initiative_row"."id"; 1.783 + ELSE 1.784 + UPDATE "initiative" SET "admitted" = FALSE 1.785 + WHERE "id" = "initiative_row"."id"; 1.786 + END IF; 1.787 + END LOOP; 1.788 + END IF; 1.789 + END IF; 1.790 + RETURN "persist"; 1.791 + END IF; 1.792 + IF 1.793 + "persist"."state" IN ('admission', 'discussion', 'verification') AND 1.794 + coalesce("persist"."harmonic_weights_set", FALSE) = FALSE 1.795 + THEN 1.796 + PERFORM "set_harmonic_initiative_weights"("issue_id_p"); 1.797 + "persist"."harmonic_weights_set" = TRUE; 1.798 + IF 1.799 + "persist"."phase_finished" OR 1.800 + "persist"."issue_revoked" OR 1.801 + "persist"."state" = 'admission' 1.802 + THEN 1.803 + RETURN "persist"; 1.804 + ELSE 1.805 + RETURN NULL; 1.806 + END IF; 1.807 + END IF; 1.808 + IF "persist"."issue_revoked" THEN 1.809 + IF "persist"."state" = 'admission' THEN 1.810 + "state_v" := 'canceled_revoked_before_accepted'; 1.811 + ELSIF "persist"."state" = 'discussion' THEN 1.812 + "state_v" := 'canceled_after_revocation_during_discussion'; 1.813 + ELSIF "persist"."state" = 'verification' THEN 1.814 + "state_v" := 'canceled_after_revocation_during_verification'; 1.815 + END IF; 1.816 + UPDATE "issue" SET 1.817 + "state" = "state_v", 1.818 + "closed" = "phase_finished", 1.819 + "phase_finished" = NULL 1.820 + WHERE "id" = "issue_id_p"; 1.821 + RETURN NULL; 1.822 + END IF; 1.823 + IF "persist"."state" = 'admission' THEN 1.824 + SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p" 1.825 + FOR UPDATE; 1.826 + IF "issue_row"."phase_finished" NOTNULL THEN 1.827 + UPDATE "issue" SET 1.828 + "state" = 'canceled_issue_not_accepted', 1.829 + "closed" = "phase_finished", 1.830 + "phase_finished" = NULL 1.831 + WHERE "id" = "issue_id_p"; 1.832 + END IF; 1.833 + RETURN NULL; 1.834 + END IF; 1.835 + IF "persist"."phase_finished" THEN 1.836 + IF "persist"."state" = 'discussion' THEN 1.837 + UPDATE "issue" SET 1.838 + "state" = 'verification', 1.839 + "half_frozen" = "phase_finished", 1.840 + "phase_finished" = NULL 1.841 + WHERE "id" = "issue_id_p"; 1.842 + RETURN NULL; 1.843 + END IF; 1.844 + IF "persist"."state" = 'verification' THEN 1.845 + SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p" 1.846 + FOR UPDATE; 1.847 + SELECT * INTO "policy_row" FROM "policy" 1.848 + WHERE "id" = "issue_row"."policy_id"; 1.849 + IF EXISTS ( 1.850 + SELECT NULL FROM "initiative" 1.851 + WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE 1.852 + ) THEN 1.853 + UPDATE "issue" SET 1.854 + "state" = 'voting', 1.855 + "fully_frozen" = "phase_finished", 1.856 + "phase_finished" = NULL 1.857 + WHERE "id" = "issue_id_p"; 1.858 + ELSE 1.859 + UPDATE "issue" SET 1.860 + "state" = 'canceled_no_initiative_admitted', 1.861 + "fully_frozen" = "phase_finished", 1.862 + "closed" = "phase_finished", 1.863 + "phase_finished" = NULL 1.864 + WHERE "id" = "issue_id_p"; 1.865 + -- NOTE: The following DELETE statements have effect only when 1.866 + -- issue state has been manipulated 1.867 + DELETE FROM "direct_voter" WHERE "issue_id" = "issue_id_p"; 1.868 + DELETE FROM "delegating_voter" WHERE "issue_id" = "issue_id_p"; 1.869 + DELETE FROM "battle" WHERE "issue_id" = "issue_id_p"; 1.870 + END IF; 1.871 + RETURN NULL; 1.872 + END IF; 1.873 + IF "persist"."state" = 'voting' THEN 1.874 + IF coalesce("persist"."closed_voting", FALSE) = FALSE THEN 1.875 + PERFORM "close_voting"("issue_id_p"); 1.876 + "persist"."closed_voting" = TRUE; 1.877 + RETURN "persist"; 1.878 + END IF; 1.879 + PERFORM "calculate_ranks"("issue_id_p"); 1.880 + RETURN NULL; 1.881 + END IF; 1.882 + END IF; 1.883 + RAISE WARNING 'should not happen'; 1.884 + RETURN NULL; 1.885 + END; 1.886 + $$; 1.887 + 1.888 +CREATE OR REPLACE FUNCTION "check_everything"() 1.889 + RETURNS VOID 1.890 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.891 + DECLARE 1.892 + "area_id_v" "area"."id"%TYPE; 1.893 + "snapshot_id_v" "snapshot"."id"%TYPE; 1.894 + "issue_id_v" "issue"."id"%TYPE; 1.895 + "persist_v" "check_issue_persistence"; 1.896 + BEGIN 1.897 + RAISE WARNING 'Function "check_everything" should only be used for development and debugging purposes'; 1.898 + DELETE FROM "expired_session"; 1.899 + DELETE FROM "expired_token"; 1.900 + DELETE FROM "unused_snapshot"; 1.901 + PERFORM "check_activity"(); 1.902 + PERFORM "calculate_member_counts"(); 1.903 + FOR "area_id_v" IN SELECT "id" FROM "area_with_unaccepted_issues" LOOP 1.904 + SELECT "take_snapshot"(NULL, "area_id_v") INTO "snapshot_id_v"; 1.905 + PERFORM "finish_snapshot"("issue_id") FROM "snapshot_issue" 1.906 + WHERE "snapshot_id" = "snapshot_id_v"; 1.907 + LOOP 1.908 + EXIT WHEN "issue_admission"("area_id_v") = FALSE; 1.909 + END LOOP; 1.910 + END LOOP; 1.911 + FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP 1.912 + "persist_v" := NULL; 1.913 + LOOP 1.914 + "persist_v" := "check_issue"("issue_id_v", "persist_v"); 1.915 + EXIT WHEN "persist_v" ISNULL; 1.916 + END LOOP; 1.917 + END LOOP; 1.918 + DELETE FROM "unused_snapshot"; 1.919 + RETURN; 1.920 + END; 1.921 + $$; 1.922 + 1.923 +CREATE OR REPLACE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE) 1.924 + RETURNS VOID 1.925 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.926 + BEGIN 1.927 + UPDATE "member" SET 1.928 + "last_login" = NULL, 1.929 + "last_delegation_check" = NULL, 1.930 + "login" = NULL, 1.931 + "password" = NULL, 1.932 + "authority" = NULL, 1.933 + "authority_uid" = NULL, 1.934 + "authority_login" = NULL, 1.935 + "deleted" = coalesce("deleted", now()), 1.936 + "locked" = TRUE, 1.937 + "active" = FALSE, 1.938 + "notify_email" = NULL, 1.939 + "notify_email_unconfirmed" = NULL, 1.940 + "notify_email_secret" = NULL, 1.941 + "notify_email_secret_expiry" = NULL, 1.942 + "notify_email_lock_expiry" = NULL, 1.943 + "disable_notifications" = TRUE, 1.944 + "notification_counter" = DEFAULT, 1.945 + "notification_sample_size" = 0, 1.946 + "notification_dow" = NULL, 1.947 + "notification_hour" = NULL, 1.948 + "notification_sent" = NULL, 1.949 + "login_recovery_expiry" = NULL, 1.950 + "password_reset_secret" = NULL, 1.951 + "password_reset_secret_expiry" = NULL, 1.952 + "location" = NULL 1.953 + WHERE "id" = "member_id_p"; 1.954 + DELETE FROM "member_settings" WHERE "member_id" = "member_id_p"; 1.955 + DELETE FROM "member_profile" WHERE "member_id" = "member_id_p"; 1.956 + DELETE FROM "rendered_member_statement" WHERE "member_id" = "member_id_p"; 1.957 + DELETE FROM "member_image" WHERE "member_id" = "member_id_p"; 1.958 + DELETE FROM "contact" WHERE "member_id" = "member_id_p"; 1.959 + DELETE FROM "ignored_member" WHERE "member_id" = "member_id_p"; 1.960 + DELETE FROM "session" WHERE "member_id" = "member_id_p"; 1.961 + DELETE FROM "member_application" WHERE "member_id" = "member_id_p"; 1.962 + DELETE FROM "token" WHERE "member_id" = "member_id_p"; 1.963 + DELETE FROM "subscription" WHERE "member_id" = "member_id_p"; 1.964 + DELETE FROM "ignored_area" WHERE "member_id" = "member_id_p"; 1.965 + DELETE FROM "ignored_initiative" WHERE "member_id" = "member_id_p"; 1.966 + DELETE FROM "delegation" WHERE "truster_id" = "member_id_p"; 1.967 + DELETE FROM "non_voter" WHERE "member_id" = "member_id_p"; 1.968 + DELETE FROM "direct_voter" USING "issue" 1.969 + WHERE "direct_voter"."issue_id" = "issue"."id" 1.970 + AND "issue"."closed" ISNULL 1.971 + AND "member_id" = "member_id_p"; 1.972 + DELETE FROM "notification_initiative_sent" WHERE "member_id" = "member_id_p"; 1.973 + RETURN; 1.974 + END; 1.975 + $$; 1.976 + 1.977 +CREATE OR REPLACE FUNCTION "delete_private_data"() 1.978 + RETURNS VOID 1.979 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.980 + BEGIN 1.981 + DELETE FROM "temporary_transaction_data"; 1.982 + DELETE FROM "temporary_suggestion_counts"; 1.983 + DELETE FROM "member" WHERE "activated" ISNULL; 1.984 + UPDATE "member" SET 1.985 + "invite_code" = NULL, 1.986 + "invite_code_expiry" = NULL, 1.987 + "admin_comment" = NULL, 1.988 + "last_login" = NULL, 1.989 + "last_delegation_check" = NULL, 1.990 + "login" = NULL, 1.991 + "password" = NULL, 1.992 + "authority" = NULL, 1.993 + "authority_uid" = NULL, 1.994 + "authority_login" = NULL, 1.995 + "lang" = NULL, 1.996 + "notify_email" = NULL, 1.997 + "notify_email_unconfirmed" = NULL, 1.998 + "notify_email_secret" = NULL, 1.999 + "notify_email_secret_expiry" = NULL, 1.1000 + "notify_email_lock_expiry" = NULL, 1.1001 + "disable_notifications" = TRUE, 1.1002 + "notification_counter" = DEFAULT, 1.1003 + "notification_sample_size" = 0, 1.1004 + "notification_dow" = NULL, 1.1005 + "notification_hour" = NULL, 1.1006 + "notification_sent" = NULL, 1.1007 + "login_recovery_expiry" = NULL, 1.1008 + "password_reset_secret" = NULL, 1.1009 + "password_reset_secret_expiry" = NULL, 1.1010 + "location" = NULL; 1.1011 + DELETE FROM "verification"; 1.1012 + DELETE FROM "member_settings"; 1.1013 + DELETE FROM "member_useterms"; 1.1014 + DELETE FROM "member_profile"; 1.1015 + DELETE FROM "rendered_member_statement"; 1.1016 + DELETE FROM "member_image"; 1.1017 + DELETE FROM "contact"; 1.1018 + DELETE FROM "ignored_member"; 1.1019 + DELETE FROM "session"; 1.1020 + DELETE FROM "system_application"; 1.1021 + DELETE FROM "system_application_redirect_uri"; 1.1022 + DELETE FROM "dynamic_application_scope"; 1.1023 + DELETE FROM "member_application"; 1.1024 + DELETE FROM "token"; 1.1025 + DELETE FROM "subscription"; 1.1026 + DELETE FROM "ignored_area"; 1.1027 + DELETE FROM "ignored_initiative"; 1.1028 + DELETE FROM "non_voter"; 1.1029 + DELETE FROM "direct_voter" USING "issue" 1.1030 + WHERE "direct_voter"."issue_id" = "issue"."id" 1.1031 + AND "issue"."closed" ISNULL; 1.1032 + DELETE FROM "event_processed"; 1.1033 + DELETE FROM "notification_initiative_sent"; 1.1034 + DELETE FROM "newsletter"; 1.1035 + RETURN; 1.1036 + END; 1.1037 + $$; 1.1038 + 1.1039 +CREATE VIEW "member_eligible_to_be_notified" AS 1.1040 + SELECT * FROM "member" 1.1041 + WHERE "activated" NOTNULL AND "locked" = FALSE; 1.1042 + 1.1043 +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.1044 + 1.1045 +CREATE VIEW "member_to_notify" AS 1.1046 + SELECT * FROM "member_eligible_to_be_notified" 1.1047 + WHERE "disable_notifications" = FALSE; 1.1048 + 1.1049 +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.1050 + 1.1051 +CREATE VIEW "area_with_unaccepted_issues" AS 1.1052 + SELECT DISTINCT ON ("area"."id") "area".* 1.1053 + FROM "area" JOIN "issue" ON "area"."id" = "issue"."area_id" 1.1054 + WHERE "issue"."state" = 'admission'; 1.1055 + 1.1056 +COMMENT ON VIEW "area_with_unaccepted_issues" IS 'All areas with unaccepted open issues (needed for issue admission system)'; 1.1057 + 1.1058 +CREATE VIEW "opening_draft" AS 1.1059 + SELECT DISTINCT ON ("initiative_id") * FROM "draft" 1.1060 + ORDER BY "initiative_id", "id"; 1.1061 + 1.1062 +COMMENT ON VIEW "opening_draft" IS 'First drafts of all initiatives'; 1.1063 + 1.1064 +CREATE VIEW "current_draft" AS 1.1065 + SELECT DISTINCT ON ("initiative_id") * FROM "draft" 1.1066 + ORDER BY "initiative_id", "id" DESC; 1.1067 + 1.1068 +COMMENT ON VIEW "current_draft" IS 'All latest drafts for each initiative'; 1.1069 + 1.1070 +CREATE VIEW "member_contingent" AS 1.1071 + SELECT 1.1072 + "member"."id" AS "member_id", 1.1073 + "contingent"."polling", 1.1074 + "contingent"."time_frame", 1.1075 + CASE WHEN "contingent"."text_entry_limit" NOTNULL THEN 1.1076 + ( 1.1077 + SELECT count(1) FROM "draft" 1.1078 + JOIN "initiative" ON "initiative"."id" = "draft"."initiative_id" 1.1079 + WHERE "draft"."author_id" = "member"."id" 1.1080 + AND "initiative"."polling" = "contingent"."polling" 1.1081 + AND "draft"."created" > now() - "contingent"."time_frame" 1.1082 + ) + ( 1.1083 + SELECT count(1) FROM "suggestion" 1.1084 + JOIN "initiative" ON "initiative"."id" = "suggestion"."initiative_id" 1.1085 + WHERE "suggestion"."author_id" = "member"."id" 1.1086 + AND "contingent"."polling" = FALSE 1.1087 + AND "suggestion"."created" > now() - "contingent"."time_frame" 1.1088 + ) 1.1089 + ELSE NULL END AS "text_entry_count", 1.1090 + "contingent"."text_entry_limit", 1.1091 + CASE WHEN "contingent"."initiative_limit" NOTNULL THEN ( 1.1092 + SELECT count(1) FROM "opening_draft" AS "draft" 1.1093 + JOIN "initiative" ON "initiative"."id" = "draft"."initiative_id" 1.1094 + WHERE "draft"."author_id" = "member"."id" 1.1095 + AND "initiative"."polling" = "contingent"."polling" 1.1096 + AND "draft"."created" > now() - "contingent"."time_frame" 1.1097 + ) ELSE NULL END AS "initiative_count", 1.1098 + "contingent"."initiative_limit" 1.1099 + FROM "member" CROSS JOIN "contingent"; 1.1100 + 1.1101 +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.1102 + 1.1103 +COMMENT ON COLUMN "member_contingent"."text_entry_count" IS 'Only calculated when "text_entry_limit" is not null in the same row'; 1.1104 +COMMENT ON COLUMN "member_contingent"."initiative_count" IS 'Only calculated when "initiative_limit" is not null in the same row'; 1.1105 + 1.1106 +CREATE VIEW "member_contingent_left" AS 1.1107 + SELECT 1.1108 + "member_id", 1.1109 + "polling", 1.1110 + max("text_entry_limit" - "text_entry_count") AS "text_entries_left", 1.1111 + max("initiative_limit" - "initiative_count") AS "initiatives_left" 1.1112 + FROM "member_contingent" GROUP BY "member_id", "polling"; 1.1113 + 1.1114 +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.1115 + 1.1116 +CREATE VIEW "scheduled_notification_to_send" AS 1.1117 + SELECT * FROM ( 1.1118 + SELECT 1.1119 + "id" AS "recipient_id", 1.1120 + now() - CASE WHEN "notification_dow" ISNULL THEN 1.1121 + ( "notification_sent"::DATE + CASE 1.1122 + WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour" 1.1123 + THEN 0 ELSE 1 END 1.1124 + )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour" 1.1125 + ELSE 1.1126 + ( "notification_sent"::DATE + 1.1127 + ( 7 + "notification_dow" - 1.1128 + EXTRACT(DOW FROM 1.1129 + ( "notification_sent"::DATE + CASE 1.1130 + WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour" 1.1131 + THEN 0 ELSE 1 END 1.1132 + )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour" 1.1133 + )::INTEGER 1.1134 + ) % 7 + 1.1135 + CASE 1.1136 + WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour" 1.1137 + THEN 0 ELSE 1 1.1138 + END 1.1139 + )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour" 1.1140 + END AS "pending" 1.1141 + FROM ( 1.1142 + SELECT 1.1143 + "id", 1.1144 + COALESCE("notification_sent", "activated") AS "notification_sent", 1.1145 + "notification_dow", 1.1146 + "notification_hour" 1.1147 + FROM "member_to_notify" 1.1148 + WHERE "notification_hour" NOTNULL 1.1149 + ) AS "subquery1" 1.1150 + ) AS "subquery2" 1.1151 + WHERE "pending" > '0'::INTERVAL; 1.1152 + 1.1153 +COMMENT ON VIEW "scheduled_notification_to_send" IS 'Set of members where a scheduled notification mail is pending'; 1.1154 + 1.1155 +COMMENT ON COLUMN "scheduled_notification_to_send"."recipient_id" IS '"id" of the member who needs to receive a notification mail'; 1.1156 +COMMENT ON COLUMN "scheduled_notification_to_send"."pending" IS 'Duration for which the notification mail has already been pending'; 1.1157 + 1.1158 +CREATE VIEW "newsletter_to_send" AS 1.1159 + SELECT 1.1160 + "member"."id" AS "recipient_id", 1.1161 + "newsletter"."id" AS "newsletter_id", 1.1162 + "newsletter"."published" 1.1163 + FROM "newsletter" CROSS JOIN "member_eligible_to_be_notified" AS "member" 1.1164 + LEFT JOIN "privilege" ON 1.1165 + "privilege"."member_id" = "member"."id" AND 1.1166 + "privilege"."unit_id" = "newsletter"."unit_id" AND 1.1167 + "privilege"."voting_right" = TRUE 1.1168 + LEFT JOIN "subscription" ON 1.1169 + "subscription"."member_id" = "member"."id" AND 1.1170 + "subscription"."unit_id" = "newsletter"."unit_id" 1.1171 + WHERE "newsletter"."published" <= now() 1.1172 + AND "newsletter"."sent" ISNULL 1.1173 + AND ( 1.1174 + "member"."disable_notifications" = FALSE OR 1.1175 + "newsletter"."include_all_members" = TRUE ) 1.1176 + AND ( 1.1177 + "newsletter"."unit_id" ISNULL OR 1.1178 + "privilege"."member_id" NOTNULL OR 1.1179 + "subscription"."member_id" NOTNULL ); 1.1180 + 1.1181 +COMMENT ON VIEW "newsletter_to_send" IS 'List of "newsletter_id"s for each member that are due to be sent out'; 1.1182 + 1.1183 +COMMENT ON COLUMN "newsletter"."published" IS 'Timestamp when the newsletter was supposed to be sent out (can be used for ordering)'; 1.1184 + 1.1185 +SELECT "copy_current_draft_data" ("id") FROM "initiative"; 1.1186 + 1.1187 +COMMIT; 1.1188 +BEGIN; 1.1189 + 1.1190 +CREATE OR REPLACE VIEW "liquid_feedback_version" AS 1.1191 + SELECT * FROM (VALUES ('4.2.1', 4, 2, 1)) 1.1192 + AS "subquery"("string", "major", "minor", "revision"); 1.1193 + 1.1194 +ALTER TABLE "unit" ADD COLUMN "attr" JSONB NOT NULL DEFAULT '{}' CHECK (jsonb_typeof("attr") = 'object'); 1.1195 +COMMENT ON COLUMN "unit"."attr" IS 'Opaque data structure to store any extended attributes used by frontend or middleware'; 1.1196 + 1.1197 +ALTER TABLE "unit" ADD COLUMN "member_weight" INT4; 1.1198 +COMMENT ON COLUMN "unit"."member_weight" IS 'Sum of active members'' voting weight'; 1.1199 + 1.1200 +ALTER TABLE "snapshot_population" ADD COLUMN "weight" INT4 NOT NULL DEFAULT 1; 1.1201 +ALTER TABLE "snapshot_population" ALTER COLUMN "weight" DROP DEFAULT; 1.1202 + 1.1203 +ALTER TABLE "privilege" ADD COLUMN "weight" INT4 NOT NULL DEFAULT 1 CHECK ("weight" >= 0); 1.1204 +COMMENT ON COLUMN "privilege"."weight" IS 'Voting weight of member in unit'; 1.1205 + 1.1206 +CREATE TABLE "issue_privilege" ( 1.1207 + PRIMARY KEY ("issue_id", "member_id"), 1.1208 + "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.1209 + "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.1210 + "initiative_right" BOOLEAN, 1.1211 + "voting_right" BOOLEAN, 1.1212 + "polling_right" BOOLEAN, 1.1213 + "weight" INT4 CHECK ("weight" >= 0) ); 1.1214 +CREATE INDEX "issue_privilege_idx" ON "issue_privilege" ("member_id"); 1.1215 +COMMENT ON TABLE "issue_privilege" IS 'Override of "privilege" table for rights of members in certain issues'; 1.1216 + 1.1217 +ALTER TABLE "direct_interest_snapshot" ADD COLUMN "ownweight" INT4 NOT NULL DEFAULT 1; 1.1218 +ALTER TABLE "direct_interest_snapshot" ALTER COLUMN "ownweight" DROP DEFAULT; 1.1219 +COMMENT ON COLUMN "direct_interest_snapshot"."ownweight" IS 'Own voting weight of member, disregading delegations'; 1.1220 +COMMENT ON COLUMN "direct_interest_snapshot"."weight" IS 'Voting weight of member according to own weight and "delegating_interest_snapshot"'; 1.1221 + 1.1222 +ALTER TABLE "delegating_interest_snapshot" ADD COLUMN "ownweight" INT4 NOT NULL DEFAULT 1; 1.1223 +ALTER TABLE "delegating_interest_snapshot" ALTER COLUMN "ownweight" DROP DEFAULT; 1.1224 +COMMENT ON COLUMN "delegating_interest_snapshot"."ownweight" IS 'Own voting weight of member, disregading delegations'; 1.1225 +COMMENT ON COLUMN "delegating_interest_snapshot"."weight" IS 'Intermediate voting weight considering incoming delegations'; 1.1226 + 1.1227 +ALTER TABLE "direct_voter" ADD COLUMN "ownweight" INT4 DEFAULT 1; 1.1228 +ALTER TABLE "direct_voter" ALTER COLUMN "ownweight" DROP DEFAULT; 1.1229 +COMMENT ON COLUMN "direct_voter"."ownweight" IS 'Own voting weight of member, disregarding delegations'; 1.1230 +COMMENT ON COLUMN "direct_voter"."weight" IS 'Voting weight of member according to own weight and "delegating_interest_snapshot"'; 1.1231 + 1.1232 +ALTER TABLE "delegating_voter" ADD COLUMN "ownweight" INT4 NOT NULL DEFAULT 1; 1.1233 +ALTER TABLE "delegating_voter" ALTER COLUMN "ownweight" DROP DEFAULT; 1.1234 +COMMENT ON COLUMN "delegating_voter"."ownweight" IS 'Own voting weight of member, disregarding delegations'; 1.1235 +COMMENT ON COLUMN "delegating_voter"."weight" IS 'Intermediate voting weight considering incoming delegations'; 1.1236 + 1.1237 +ALTER TABLE "posting" ADD FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id"); 1.1238 + 1.1239 +DROP VIEW "issue_delegation"; 1.1240 +CREATE VIEW "issue_delegation" AS 1.1241 + SELECT DISTINCT ON ("issue"."id", "delegation"."truster_id") 1.1242 + "issue"."id" AS "issue_id", 1.1243 + "delegation"."id", 1.1244 + "delegation"."truster_id", 1.1245 + "delegation"."trustee_id", 1.1246 + COALESCE("issue_privilege"."weight", "privilege"."weight") AS "weight", 1.1247 + "delegation"."scope" 1.1248 + FROM "issue" 1.1249 + JOIN "area" 1.1250 + ON "area"."id" = "issue"."area_id" 1.1251 + JOIN "delegation" 1.1252 + ON "delegation"."unit_id" = "area"."unit_id" 1.1253 + OR "delegation"."area_id" = "area"."id" 1.1254 + OR "delegation"."issue_id" = "issue"."id" 1.1255 + JOIN "member" 1.1256 + ON "delegation"."truster_id" = "member"."id" 1.1257 + LEFT JOIN "privilege" 1.1258 + ON "area"."unit_id" = "privilege"."unit_id" 1.1259 + AND "delegation"."truster_id" = "privilege"."member_id" 1.1260 + LEFT JOIN "issue_privilege" 1.1261 + ON "issue"."id" = "issue_privilege"."issue_id" 1.1262 + AND "delegation"."truster_id" = "issue_privilege"."member_id" 1.1263 + WHERE "member"."active" 1.1264 + AND COALESCE("issue_privilege"."voting_right", "privilege"."voting_right") 1.1265 + ORDER BY 1.1266 + "issue"."id", 1.1267 + "delegation"."truster_id", 1.1268 + "delegation"."scope" DESC; 1.1269 +COMMENT ON VIEW "issue_delegation" IS 'Issue delegations where trusters are active and have voting right'; 1.1270 + 1.1271 +CREATE OR REPLACE VIEW "unit_member" AS 1.1272 + SELECT 1.1273 + "privilege"."unit_id" AS "unit_id", 1.1274 + "member"."id" AS "member_id", 1.1275 + "privilege"."weight" 1.1276 + FROM "privilege" JOIN "member" ON "member"."id" = "privilege"."member_id" 1.1277 + WHERE "privilege"."voting_right" AND "member"."active"; 1.1278 + 1.1279 +CREATE OR REPLACE VIEW "unit_member_count" AS 1.1280 + SELECT 1.1281 + "unit"."id" AS "unit_id", 1.1282 + count("unit_member"."member_id") AS "member_count", 1.1283 + sum("unit_member"."weight") AS "member_weight" 1.1284 + FROM "unit" LEFT JOIN "unit_member" 1.1285 + ON "unit"."id" = "unit_member"."unit_id" 1.1286 + GROUP BY "unit"."id"; 1.1287 + 1.1288 +CREATE OR REPLACE VIEW "event_for_notification" AS 1.1289 + SELECT 1.1290 + "member"."id" AS "recipient_id", 1.1291 + "event".* 1.1292 + FROM "member" CROSS JOIN "event" 1.1293 + JOIN "issue" ON "issue"."id" = "event"."issue_id" 1.1294 + JOIN "area" ON "area"."id" = "issue"."area_id" 1.1295 + LEFT JOIN "privilege" ON 1.1296 + "privilege"."member_id" = "member"."id" AND 1.1297 + "privilege"."unit_id" = "area"."unit_id" 1.1298 + LEFT JOIN "issue_privilege" ON 1.1299 + "issue_privilege"."member_id" = "member"."id" AND 1.1300 + "issue_privilege"."issue_id" = "event"."issue_id" 1.1301 + LEFT JOIN "subscription" ON 1.1302 + "subscription"."member_id" = "member"."id" AND 1.1303 + "subscription"."unit_id" = "area"."unit_id" 1.1304 + LEFT JOIN "ignored_area" ON 1.1305 + "ignored_area"."member_id" = "member"."id" AND 1.1306 + "ignored_area"."area_id" = "issue"."area_id" 1.1307 + LEFT JOIN "interest" ON 1.1308 + "interest"."member_id" = "member"."id" AND 1.1309 + "interest"."issue_id" = "event"."issue_id" 1.1310 + LEFT JOIN "supporter" ON 1.1311 + "supporter"."member_id" = "member"."id" AND 1.1312 + "supporter"."initiative_id" = "event"."initiative_id" 1.1313 + WHERE ( 1.1314 + COALESCE("issue_privilege"."voting_right", "privilege"."voting_right") OR 1.1315 + "subscription"."member_id" NOTNULL 1.1316 + ) AND ("ignored_area"."member_id" ISNULL OR "interest"."member_id" NOTNULL) 1.1317 + AND ( 1.1318 + "event"."event" = 'issue_state_changed'::"event_type" OR 1.1319 + ( "event"."event" = 'initiative_revoked'::"event_type" AND 1.1320 + "supporter"."member_id" NOTNULL ) ); 1.1321 + 1.1322 +CREATE OR REPLACE FUNCTION "featured_initiative" 1.1323 + ( "recipient_id_p" "member"."id"%TYPE, 1.1324 + "area_id_p" "area"."id"%TYPE ) 1.1325 + RETURNS SETOF "initiative"."id"%TYPE 1.1326 + LANGUAGE 'plpgsql' STABLE AS $$ 1.1327 + DECLARE 1.1328 + "counter_v" "member"."notification_counter"%TYPE; 1.1329 + "sample_size_v" "member"."notification_sample_size"%TYPE; 1.1330 + "initiative_id_ary" INT4[]; --"initiative"."id"%TYPE[] 1.1331 + "match_v" BOOLEAN; 1.1332 + "member_id_v" "member"."id"%TYPE; 1.1333 + "seed_v" TEXT; 1.1334 + "initiative_id_v" "initiative"."id"%TYPE; 1.1335 + BEGIN 1.1336 + SELECT "notification_counter", "notification_sample_size" 1.1337 + INTO "counter_v", "sample_size_v" 1.1338 + FROM "member" WHERE "id" = "recipient_id_p"; 1.1339 + IF COALESCE("sample_size_v" <= 0, TRUE) THEN 1.1340 + RETURN; 1.1341 + END IF; 1.1342 + "initiative_id_ary" := '{}'; 1.1343 + LOOP 1.1344 + "match_v" := FALSE; 1.1345 + FOR "member_id_v", "seed_v" IN 1.1346 + SELECT * FROM ( 1.1347 + SELECT DISTINCT 1.1348 + "supporter"."member_id", 1.1349 + md5( 1.1350 + "recipient_id_p" || '-' || 1.1351 + "counter_v" || '-' || 1.1352 + "area_id_p" || '-' || 1.1353 + "supporter"."member_id" 1.1354 + ) AS "seed" 1.1355 + FROM "supporter" 1.1356 + JOIN "initiative" ON "initiative"."id" = "supporter"."initiative_id" 1.1357 + JOIN "issue" ON "issue"."id" = "initiative"."issue_id" 1.1358 + WHERE "supporter"."member_id" != "recipient_id_p" 1.1359 + AND "issue"."area_id" = "area_id_p" 1.1360 + AND "issue"."state" IN ('admission', 'discussion', 'verification') 1.1361 + ) AS "subquery" 1.1362 + ORDER BY "seed" 1.1363 + LOOP 1.1364 + SELECT "initiative"."id" INTO "initiative_id_v" 1.1365 + FROM "initiative" 1.1366 + JOIN "issue" ON "issue"."id" = "initiative"."issue_id" 1.1367 + JOIN "area" ON "area"."id" = "issue"."area_id" 1.1368 + JOIN "supporter" ON "supporter"."initiative_id" = "initiative"."id" 1.1369 + LEFT JOIN "supporter" AS "self_support" ON 1.1370 + "self_support"."initiative_id" = "initiative"."id" AND 1.1371 + "self_support"."member_id" = "recipient_id_p" 1.1372 + LEFT JOIN "privilege" ON 1.1373 + "privilege"."member_id" = "recipient_id_p" AND 1.1374 + "privilege"."unit_id" = "area"."unit_id" 1.1375 + LEFT JOIN "issue_privilege" ON 1.1376 + "issue_privilege"."member_id" = "recipient_id_p" AND 1.1377 + "issue_privilege"."issue_id" = "initiative"."issue_id" 1.1378 + LEFT JOIN "subscription" ON 1.1379 + "subscription"."member_id" = "recipient_id_p" AND 1.1380 + "subscription"."unit_id" = "area"."unit_id" 1.1381 + LEFT JOIN "ignored_initiative" ON 1.1382 + "ignored_initiative"."member_id" = "recipient_id_p" AND 1.1383 + "ignored_initiative"."initiative_id" = "initiative"."id" 1.1384 + WHERE "supporter"."member_id" = "member_id_v" 1.1385 + AND "issue"."area_id" = "area_id_p" 1.1386 + AND "issue"."state" IN ('admission', 'discussion', 'verification') 1.1387 + AND "initiative"."revoked" ISNULL 1.1388 + AND "self_support"."member_id" ISNULL 1.1389 + AND NOT "initiative_id_ary" @> ARRAY["initiative"."id"] 1.1390 + AND ( 1.1391 + COALESCE( 1.1392 + "issue_privilege"."voting_right", "privilege"."voting_right" 1.1393 + ) OR "subscription"."member_id" NOTNULL ) 1.1394 + AND "ignored_initiative"."member_id" ISNULL 1.1395 + AND NOT EXISTS ( 1.1396 + SELECT NULL FROM "draft" 1.1397 + JOIN "ignored_member" ON 1.1398 + "ignored_member"."member_id" = "recipient_id_p" AND 1.1399 + "ignored_member"."other_member_id" = "draft"."author_id" 1.1400 + WHERE "draft"."initiative_id" = "initiative"."id" 1.1401 + ) 1.1402 + ORDER BY md5("seed_v" || '-' || "initiative"."id") 1.1403 + LIMIT 1; 1.1404 + IF FOUND THEN 1.1405 + "match_v" := TRUE; 1.1406 + RETURN NEXT "initiative_id_v"; 1.1407 + IF array_length("initiative_id_ary", 1) + 1 >= "sample_size_v" THEN 1.1408 + RETURN; 1.1409 + END IF; 1.1410 + "initiative_id_ary" := "initiative_id_ary" || "initiative_id_v"; 1.1411 + END IF; 1.1412 + END LOOP; 1.1413 + EXIT WHEN NOT "match_v"; 1.1414 + END LOOP; 1.1415 + RETURN; 1.1416 + END; 1.1417 + $$; 1.1418 + 1.1419 +CREATE OR REPLACE FUNCTION "delegation_chain" 1.1420 + ( "member_id_p" "member"."id"%TYPE, 1.1421 + "unit_id_p" "unit"."id"%TYPE, 1.1422 + "area_id_p" "area"."id"%TYPE, 1.1423 + "issue_id_p" "issue"."id"%TYPE, 1.1424 + "simulate_trustee_id_p" "member"."id"%TYPE DEFAULT NULL, 1.1425 + "simulate_default_p" BOOLEAN DEFAULT FALSE ) 1.1426 + RETURNS SETOF "delegation_chain_row" 1.1427 + LANGUAGE 'plpgsql' STABLE AS $$ 1.1428 + DECLARE 1.1429 + "scope_v" "delegation_scope"; 1.1430 + "unit_id_v" "unit"."id"%TYPE; 1.1431 + "area_id_v" "area"."id"%TYPE; 1.1432 + "issue_row" "issue"%ROWTYPE; 1.1433 + "visited_member_ids" INT4[]; -- "member"."id"%TYPE[] 1.1434 + "loop_member_id_v" "member"."id"%TYPE; 1.1435 + "output_row" "delegation_chain_row"; 1.1436 + "output_rows" "delegation_chain_row"[]; 1.1437 + "simulate_v" BOOLEAN; 1.1438 + "simulate_here_v" BOOLEAN; 1.1439 + "delegation_row" "delegation"%ROWTYPE; 1.1440 + "row_count" INT4; 1.1441 + "i" INT4; 1.1442 + "loop_v" BOOLEAN; 1.1443 + BEGIN 1.1444 + IF "simulate_trustee_id_p" NOTNULL AND "simulate_default_p" THEN 1.1445 + RAISE EXCEPTION 'Both "simulate_trustee_id_p" is set, and "simulate_default_p" is true'; 1.1446 + END IF; 1.1447 + IF "simulate_trustee_id_p" NOTNULL OR "simulate_default_p" THEN 1.1448 + "simulate_v" := TRUE; 1.1449 + ELSE 1.1450 + "simulate_v" := FALSE; 1.1451 + END IF; 1.1452 + IF 1.1453 + "unit_id_p" NOTNULL AND 1.1454 + "area_id_p" ISNULL AND 1.1455 + "issue_id_p" ISNULL 1.1456 + THEN 1.1457 + "scope_v" := 'unit'; 1.1458 + "unit_id_v" := "unit_id_p"; 1.1459 + ELSIF 1.1460 + "unit_id_p" ISNULL AND 1.1461 + "area_id_p" NOTNULL AND 1.1462 + "issue_id_p" ISNULL 1.1463 + THEN 1.1464 + "scope_v" := 'area'; 1.1465 + "area_id_v" := "area_id_p"; 1.1466 + SELECT "unit_id" INTO "unit_id_v" 1.1467 + FROM "area" WHERE "id" = "area_id_v"; 1.1468 + ELSIF 1.1469 + "unit_id_p" ISNULL AND 1.1470 + "area_id_p" ISNULL AND 1.1471 + "issue_id_p" NOTNULL 1.1472 + THEN 1.1473 + SELECT INTO "issue_row" * FROM "issue" WHERE "id" = "issue_id_p"; 1.1474 + IF "issue_row"."id" ISNULL THEN 1.1475 + RETURN; 1.1476 + END IF; 1.1477 + IF "issue_row"."closed" NOTNULL THEN 1.1478 + IF "simulate_v" THEN 1.1479 + RAISE EXCEPTION 'Tried to simulate delegation chain for closed issue.'; 1.1480 + END IF; 1.1481 + FOR "output_row" IN 1.1482 + SELECT * FROM 1.1483 + "delegation_chain_for_closed_issue"("member_id_p", "issue_id_p") 1.1484 + LOOP 1.1485 + RETURN NEXT "output_row"; 1.1486 + END LOOP; 1.1487 + RETURN; 1.1488 + END IF; 1.1489 + "scope_v" := 'issue'; 1.1490 + SELECT "area_id" INTO "area_id_v" 1.1491 + FROM "issue" WHERE "id" = "issue_id_p"; 1.1492 + SELECT "unit_id" INTO "unit_id_v" 1.1493 + FROM "area" WHERE "id" = "area_id_v"; 1.1494 + ELSE 1.1495 + RAISE EXCEPTION 'Exactly one of unit_id_p, area_id_p, or issue_id_p must be NOTNULL.'; 1.1496 + END IF; 1.1497 + "visited_member_ids" := '{}'; 1.1498 + "loop_member_id_v" := NULL; 1.1499 + "output_rows" := '{}'; 1.1500 + "output_row"."index" := 0; 1.1501 + "output_row"."member_id" := "member_id_p"; 1.1502 + "output_row"."member_valid" := TRUE; 1.1503 + "output_row"."participation" := FALSE; 1.1504 + "output_row"."overridden" := FALSE; 1.1505 + "output_row"."disabled_out" := FALSE; 1.1506 + "output_row"."scope_out" := NULL; 1.1507 + LOOP 1.1508 + IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN 1.1509 + "loop_member_id_v" := "output_row"."member_id"; 1.1510 + ELSE 1.1511 + "visited_member_ids" := 1.1512 + "visited_member_ids" || "output_row"."member_id"; 1.1513 + END IF; 1.1514 + IF "output_row"."participation" ISNULL THEN 1.1515 + "output_row"."overridden" := NULL; 1.1516 + ELSIF "output_row"."participation" THEN 1.1517 + "output_row"."overridden" := TRUE; 1.1518 + END IF; 1.1519 + "output_row"."scope_in" := "output_row"."scope_out"; 1.1520 + "output_row"."member_valid" := EXISTS ( 1.1521 + SELECT NULL FROM "member" 1.1522 + LEFT JOIN "privilege" 1.1523 + ON "privilege"."member_id" = "member"."id" 1.1524 + AND "privilege"."unit_id" = "unit_id_v" 1.1525 + LEFT JOIN "issue_privilege" 1.1526 + ON "issue_privilege"."member_id" = "member"."id" 1.1527 + AND "issue_privilege"."issue_id" = "issue_id_p" 1.1528 + WHERE "id" = "output_row"."member_id" 1.1529 + AND "member"."active" 1.1530 + AND COALESCE( 1.1531 + "issue_privilege"."voting_right", "privilege"."voting_right") 1.1532 + ); 1.1533 + "simulate_here_v" := ( 1.1534 + "simulate_v" AND 1.1535 + "output_row"."member_id" = "member_id_p" 1.1536 + ); 1.1537 + "delegation_row" := ROW(NULL); 1.1538 + IF "output_row"."member_valid" OR "simulate_here_v" THEN 1.1539 + IF "scope_v" = 'unit' THEN 1.1540 + IF NOT "simulate_here_v" THEN 1.1541 + SELECT * INTO "delegation_row" FROM "delegation" 1.1542 + WHERE "truster_id" = "output_row"."member_id" 1.1543 + AND "unit_id" = "unit_id_v"; 1.1544 + END IF; 1.1545 + ELSIF "scope_v" = 'area' THEN 1.1546 + IF "simulate_here_v" THEN 1.1547 + IF "simulate_trustee_id_p" ISNULL THEN 1.1548 + SELECT * INTO "delegation_row" FROM "delegation" 1.1549 + WHERE "truster_id" = "output_row"."member_id" 1.1550 + AND "unit_id" = "unit_id_v"; 1.1551 + END IF; 1.1552 + ELSE 1.1553 + SELECT * INTO "delegation_row" FROM "delegation" 1.1554 + WHERE "truster_id" = "output_row"."member_id" 1.1555 + AND ( 1.1556 + "unit_id" = "unit_id_v" OR 1.1557 + "area_id" = "area_id_v" 1.1558 + ) 1.1559 + ORDER BY "scope" DESC; 1.1560 + END IF; 1.1561 + ELSIF "scope_v" = 'issue' THEN 1.1562 + IF "issue_row"."fully_frozen" ISNULL THEN 1.1563 + "output_row"."participation" := EXISTS ( 1.1564 + SELECT NULL FROM "interest" 1.1565 + WHERE "issue_id" = "issue_id_p" 1.1566 + AND "member_id" = "output_row"."member_id" 1.1567 + ); 1.1568 + ELSE 1.1569 + IF "output_row"."member_id" = "member_id_p" THEN 1.1570 + "output_row"."participation" := EXISTS ( 1.1571 + SELECT NULL FROM "direct_voter" 1.1572 + WHERE "issue_id" = "issue_id_p" 1.1573 + AND "member_id" = "output_row"."member_id" 1.1574 + ); 1.1575 + ELSE 1.1576 + "output_row"."participation" := NULL; 1.1577 + END IF; 1.1578 + END IF; 1.1579 + IF "simulate_here_v" THEN 1.1580 + IF "simulate_trustee_id_p" ISNULL THEN 1.1581 + SELECT * INTO "delegation_row" FROM "delegation" 1.1582 + WHERE "truster_id" = "output_row"."member_id" 1.1583 + AND ( 1.1584 + "unit_id" = "unit_id_v" OR 1.1585 + "area_id" = "area_id_v" 1.1586 + ) 1.1587 + ORDER BY "scope" DESC; 1.1588 + END IF; 1.1589 + ELSE 1.1590 + SELECT * INTO "delegation_row" FROM "delegation" 1.1591 + WHERE "truster_id" = "output_row"."member_id" 1.1592 + AND ( 1.1593 + "unit_id" = "unit_id_v" OR 1.1594 + "area_id" = "area_id_v" OR 1.1595 + "issue_id" = "issue_id_p" 1.1596 + ) 1.1597 + ORDER BY "scope" DESC; 1.1598 + END IF; 1.1599 + END IF; 1.1600 + ELSE 1.1601 + "output_row"."participation" := FALSE; 1.1602 + END IF; 1.1603 + IF "simulate_here_v" AND "simulate_trustee_id_p" NOTNULL THEN 1.1604 + "output_row"."scope_out" := "scope_v"; 1.1605 + "output_rows" := "output_rows" || "output_row"; 1.1606 + "output_row"."member_id" := "simulate_trustee_id_p"; 1.1607 + ELSIF "delegation_row"."trustee_id" NOTNULL THEN 1.1608 + "output_row"."scope_out" := "delegation_row"."scope"; 1.1609 + "output_rows" := "output_rows" || "output_row"; 1.1610 + "output_row"."member_id" := "delegation_row"."trustee_id"; 1.1611 + ELSIF "delegation_row"."scope" NOTNULL THEN 1.1612 + "output_row"."scope_out" := "delegation_row"."scope"; 1.1613 + "output_row"."disabled_out" := TRUE; 1.1614 + "output_rows" := "output_rows" || "output_row"; 1.1615 + EXIT; 1.1616 + ELSE 1.1617 + "output_row"."scope_out" := NULL; 1.1618 + "output_rows" := "output_rows" || "output_row"; 1.1619 + EXIT; 1.1620 + END IF; 1.1621 + EXIT WHEN "loop_member_id_v" NOTNULL; 1.1622 + "output_row"."index" := "output_row"."index" + 1; 1.1623 + END LOOP; 1.1624 + "row_count" := array_upper("output_rows", 1); 1.1625 + "i" := 1; 1.1626 + "loop_v" := FALSE; 1.1627 + LOOP 1.1628 + "output_row" := "output_rows"["i"]; 1.1629 + EXIT WHEN "output_row" ISNULL; -- NOTE: ISNULL and NOT ... NOTNULL produce different results! 1.1630 + IF "loop_v" THEN 1.1631 + IF "i" + 1 = "row_count" THEN 1.1632 + "output_row"."loop" := 'last'; 1.1633 + ELSIF "i" = "row_count" THEN 1.1634 + "output_row"."loop" := 'repetition'; 1.1635 + ELSE 1.1636 + "output_row"."loop" := 'intermediate'; 1.1637 + END IF; 1.1638 + ELSIF "output_row"."member_id" = "loop_member_id_v" THEN 1.1639 + "output_row"."loop" := 'first'; 1.1640 + "loop_v" := TRUE; 1.1641 + END IF; 1.1642 + IF "scope_v" = 'unit' THEN 1.1643 + "output_row"."participation" := NULL; 1.1644 + END IF; 1.1645 + RETURN NEXT "output_row"; 1.1646 + "i" := "i" + 1; 1.1647 + END LOOP; 1.1648 + RETURN; 1.1649 + END; 1.1650 + $$; 1.1651 + 1.1652 +CREATE OR REPLACE FUNCTION "calculate_member_counts"() 1.1653 + RETURNS VOID 1.1654 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.1655 + BEGIN 1.1656 + PERFORM "require_transaction_isolation"(); 1.1657 + DELETE FROM "member_count"; 1.1658 + INSERT INTO "member_count" ("total_count") 1.1659 + SELECT "total_count" FROM "member_count_view"; 1.1660 + UPDATE "unit" SET 1.1661 + "member_count" = "view"."member_count", 1.1662 + "member_weight" = "view"."member_weight" 1.1663 + FROM "unit_member_count" AS "view" 1.1664 + WHERE "view"."unit_id" = "unit"."id"; 1.1665 + RETURN; 1.1666 + END; 1.1667 + $$; 1.1668 +COMMENT ON FUNCTION "calculate_member_counts"() IS 'Updates "member_count" table and "member_count" and "member_weight" columns of table "area" by materializing data from views "member_count_view" and "unit_member_count"'; 1.1669 + 1.1670 +CREATE OR REPLACE FUNCTION "weight_of_added_delegations_for_snapshot" 1.1671 + ( "snapshot_id_p" "snapshot"."id"%TYPE, 1.1672 + "issue_id_p" "issue"."id"%TYPE, 1.1673 + "member_id_p" "member"."id"%TYPE, 1.1674 + "delegate_member_ids_p" "delegating_interest_snapshot"."delegate_member_ids"%TYPE ) 1.1675 + RETURNS "direct_interest_snapshot"."weight"%TYPE 1.1676 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.1677 + DECLARE 1.1678 + "issue_delegation_row" "issue_delegation"%ROWTYPE; 1.1679 + "delegate_member_ids_v" "delegating_interest_snapshot"."delegate_member_ids"%TYPE; 1.1680 + "weight_v" INT4; 1.1681 + "sub_weight_v" INT4; 1.1682 + BEGIN 1.1683 + PERFORM "require_transaction_isolation"(); 1.1684 + "weight_v" := 0; 1.1685 + FOR "issue_delegation_row" IN 1.1686 + SELECT * FROM "issue_delegation" 1.1687 + WHERE "trustee_id" = "member_id_p" 1.1688 + AND "issue_id" = "issue_id_p" 1.1689 + LOOP 1.1690 + IF NOT EXISTS ( 1.1691 + SELECT NULL FROM "direct_interest_snapshot" 1.1692 + WHERE "snapshot_id" = "snapshot_id_p" 1.1693 + AND "issue_id" = "issue_id_p" 1.1694 + AND "member_id" = "issue_delegation_row"."truster_id" 1.1695 + ) AND NOT EXISTS ( 1.1696 + SELECT NULL FROM "delegating_interest_snapshot" 1.1697 + WHERE "snapshot_id" = "snapshot_id_p" 1.1698 + AND "issue_id" = "issue_id_p" 1.1699 + AND "member_id" = "issue_delegation_row"."truster_id" 1.1700 + ) THEN 1.1701 + "delegate_member_ids_v" := 1.1702 + "member_id_p" || "delegate_member_ids_p"; 1.1703 + INSERT INTO "delegating_interest_snapshot" ( 1.1704 + "snapshot_id", 1.1705 + "issue_id", 1.1706 + "member_id", 1.1707 + "ownweight", 1.1708 + "scope", 1.1709 + "delegate_member_ids" 1.1710 + ) VALUES ( 1.1711 + "snapshot_id_p", 1.1712 + "issue_id_p", 1.1713 + "issue_delegation_row"."truster_id", 1.1714 + "issue_delegation_row"."weight", 1.1715 + "issue_delegation_row"."scope", 1.1716 + "delegate_member_ids_v" 1.1717 + ); 1.1718 + "sub_weight_v" := "issue_delegation_row"."weight" + 1.1719 + "weight_of_added_delegations_for_snapshot"( 1.1720 + "snapshot_id_p", 1.1721 + "issue_id_p", 1.1722 + "issue_delegation_row"."truster_id", 1.1723 + "delegate_member_ids_v" 1.1724 + ); 1.1725 + UPDATE "delegating_interest_snapshot" 1.1726 + SET "weight" = "sub_weight_v" 1.1727 + WHERE "snapshot_id" = "snapshot_id_p" 1.1728 + AND "issue_id" = "issue_id_p" 1.1729 + AND "member_id" = "issue_delegation_row"."truster_id"; 1.1730 + "weight_v" := "weight_v" + "sub_weight_v"; 1.1731 + END IF; 1.1732 + END LOOP; 1.1733 + RETURN "weight_v"; 1.1734 + END; 1.1735 + $$; 1.1736 + 1.1737 +CREATE OR REPLACE FUNCTION "take_snapshot" 1.1738 + ( "issue_id_p" "issue"."id"%TYPE, 1.1739 + "area_id_p" "area"."id"%TYPE = NULL ) 1.1740 + RETURNS "snapshot"."id"%TYPE 1.1741 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.1742 + DECLARE 1.1743 + "area_id_v" "area"."id"%TYPE; 1.1744 + "unit_id_v" "unit"."id"%TYPE; 1.1745 + "snapshot_id_v" "snapshot"."id"%TYPE; 1.1746 + "issue_id_v" "issue"."id"%TYPE; 1.1747 + "member_id_v" "member"."id"%TYPE; 1.1748 + BEGIN 1.1749 + IF "issue_id_p" NOTNULL AND "area_id_p" NOTNULL THEN 1.1750 + RAISE EXCEPTION 'One of "issue_id_p" and "area_id_p" must be NULL'; 1.1751 + END IF; 1.1752 + PERFORM "require_transaction_isolation"(); 1.1753 + IF "issue_id_p" ISNULL THEN 1.1754 + "area_id_v" := "area_id_p"; 1.1755 + ELSE 1.1756 + SELECT "area_id" INTO "area_id_v" 1.1757 + FROM "issue" WHERE "id" = "issue_id_p"; 1.1758 + END IF; 1.1759 + SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v"; 1.1760 + INSERT INTO "snapshot" ("area_id", "issue_id") 1.1761 + VALUES ("area_id_v", "issue_id_p") 1.1762 + RETURNING "id" INTO "snapshot_id_v"; 1.1763 + INSERT INTO "snapshot_population" ("snapshot_id", "member_id", "weight") 1.1764 + SELECT 1.1765 + "snapshot_id_v", 1.1766 + "member"."id", 1.1767 + COALESCE("issue_privilege"."weight", "privilege"."weight") 1.1768 + FROM "member" 1.1769 + LEFT JOIN "privilege" 1.1770 + ON "privilege"."unit_id" = "unit_id_v" 1.1771 + AND "privilege"."member_id" = "member"."id" 1.1772 + LEFT JOIN "issue_privilege" 1.1773 + ON "issue_privilege"."issue_id" = "issue_id_p" 1.1774 + AND "issue_privilege"."member_id" = "member"."id" 1.1775 + WHERE "member"."active" AND COALESCE( 1.1776 + "issue_privilege"."voting_right", "privilege"."voting_right"); 1.1777 + UPDATE "snapshot" SET 1.1778 + "population" = ( 1.1779 + SELECT sum("weight") FROM "snapshot_population" 1.1780 + WHERE "snapshot_id" = "snapshot_id_v" 1.1781 + ) WHERE "id" = "snapshot_id_v"; 1.1782 + FOR "issue_id_v" IN 1.1783 + SELECT "id" FROM "issue" 1.1784 + WHERE CASE WHEN "issue_id_p" ISNULL THEN 1.1785 + "area_id" = "area_id_p" AND 1.1786 + "state" = 'admission' 1.1787 + ELSE 1.1788 + "id" = "issue_id_p" 1.1789 + END 1.1790 + LOOP 1.1791 + INSERT INTO "snapshot_issue" ("snapshot_id", "issue_id") 1.1792 + VALUES ("snapshot_id_v", "issue_id_v"); 1.1793 + INSERT INTO "direct_interest_snapshot" 1.1794 + ("snapshot_id", "issue_id", "member_id", "ownweight") 1.1795 + SELECT 1.1796 + "snapshot_id_v" AS "snapshot_id", 1.1797 + "issue_id_v" AS "issue_id", 1.1798 + "member"."id" AS "member_id", 1.1799 + COALESCE( 1.1800 + "issue_privilege"."weight", "privilege"."weight" 1.1801 + ) AS "ownweight" 1.1802 + FROM "issue" 1.1803 + JOIN "area" ON "issue"."area_id" = "area"."id" 1.1804 + JOIN "interest" ON "issue"."id" = "interest"."issue_id" 1.1805 + JOIN "member" ON "interest"."member_id" = "member"."id" 1.1806 + LEFT JOIN "privilege" 1.1807 + ON "privilege"."unit_id" = "area"."unit_id" 1.1808 + AND "privilege"."member_id" = "member"."id" 1.1809 + LEFT JOIN "issue_privilege" 1.1810 + ON "issue_privilege"."issue_id" = "issue_id_v" 1.1811 + AND "issue_privilege"."member_id" = "member"."id" 1.1812 + WHERE "issue"."id" = "issue_id_v" 1.1813 + AND "member"."active" AND COALESCE( 1.1814 + "issue_privilege"."voting_right", "privilege"."voting_right"); 1.1815 + FOR "member_id_v" IN 1.1816 + SELECT "member_id" FROM "direct_interest_snapshot" 1.1817 + WHERE "snapshot_id" = "snapshot_id_v" 1.1818 + AND "issue_id" = "issue_id_v" 1.1819 + LOOP 1.1820 + UPDATE "direct_interest_snapshot" SET 1.1821 + "weight" = "ownweight" + 1.1822 + "weight_of_added_delegations_for_snapshot"( 1.1823 + "snapshot_id_v", 1.1824 + "issue_id_v", 1.1825 + "member_id_v", 1.1826 + '{}' 1.1827 + ) 1.1828 + WHERE "snapshot_id" = "snapshot_id_v" 1.1829 + AND "issue_id" = "issue_id_v" 1.1830 + AND "member_id" = "member_id_v"; 1.1831 + END LOOP; 1.1832 + INSERT INTO "direct_supporter_snapshot" 1.1833 + ( "snapshot_id", "issue_id", "initiative_id", "member_id", 1.1834 + "draft_id", "informed", "satisfied" ) 1.1835 + SELECT 1.1836 + "snapshot_id_v" AS "snapshot_id", 1.1837 + "issue_id_v" AS "issue_id", 1.1838 + "initiative"."id" AS "initiative_id", 1.1839 + "supporter"."member_id" AS "member_id", 1.1840 + "supporter"."draft_id" AS "draft_id", 1.1841 + "supporter"."draft_id" = "current_draft"."id" AS "informed", 1.1842 + NOT EXISTS ( 1.1843 + SELECT NULL FROM "critical_opinion" 1.1844 + WHERE "initiative_id" = "initiative"."id" 1.1845 + AND "member_id" = "supporter"."member_id" 1.1846 + ) AS "satisfied" 1.1847 + FROM "initiative" 1.1848 + JOIN "supporter" 1.1849 + ON "supporter"."initiative_id" = "initiative"."id" 1.1850 + JOIN "current_draft" 1.1851 + ON "initiative"."id" = "current_draft"."initiative_id" 1.1852 + JOIN "direct_interest_snapshot" 1.1853 + ON "snapshot_id_v" = "direct_interest_snapshot"."snapshot_id" 1.1854 + AND "supporter"."member_id" = "direct_interest_snapshot"."member_id" 1.1855 + AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id" 1.1856 + WHERE "initiative"."issue_id" = "issue_id_v"; 1.1857 + DELETE FROM "temporary_suggestion_counts"; 1.1858 + INSERT INTO "temporary_suggestion_counts" 1.1859 + ( "id", 1.1860 + "minus2_unfulfilled_count", "minus2_fulfilled_count", 1.1861 + "minus1_unfulfilled_count", "minus1_fulfilled_count", 1.1862 + "plus1_unfulfilled_count", "plus1_fulfilled_count", 1.1863 + "plus2_unfulfilled_count", "plus2_fulfilled_count" ) 1.1864 + SELECT 1.1865 + "suggestion"."id", 1.1866 + ( SELECT coalesce(sum("di"."weight"), 0) 1.1867 + FROM "opinion" JOIN "direct_interest_snapshot" AS "di" 1.1868 + ON "di"."snapshot_id" = "snapshot_id_v" 1.1869 + AND "di"."issue_id" = "issue_id_v" 1.1870 + AND "di"."member_id" = "opinion"."member_id" 1.1871 + WHERE "opinion"."suggestion_id" = "suggestion"."id" 1.1872 + AND "opinion"."degree" = -2 1.1873 + AND "opinion"."fulfilled" = FALSE 1.1874 + ) AS "minus2_unfulfilled_count", 1.1875 + ( SELECT coalesce(sum("di"."weight"), 0) 1.1876 + FROM "opinion" JOIN "direct_interest_snapshot" AS "di" 1.1877 + ON "di"."snapshot_id" = "snapshot_id_v" 1.1878 + AND "di"."issue_id" = "issue_id_v" 1.1879 + AND "di"."member_id" = "opinion"."member_id" 1.1880 + WHERE "opinion"."suggestion_id" = "suggestion"."id" 1.1881 + AND "opinion"."degree" = -2 1.1882 + AND "opinion"."fulfilled" = TRUE 1.1883 + ) AS "minus2_fulfilled_count", 1.1884 + ( SELECT coalesce(sum("di"."weight"), 0) 1.1885 + FROM "opinion" JOIN "direct_interest_snapshot" AS "di" 1.1886 + ON "di"."snapshot_id" = "snapshot_id_v" 1.1887 + AND "di"."issue_id" = "issue_id_v" 1.1888 + AND "di"."member_id" = "opinion"."member_id" 1.1889 + WHERE "opinion"."suggestion_id" = "suggestion"."id" 1.1890 + AND "opinion"."degree" = -1 1.1891 + AND "opinion"."fulfilled" = FALSE 1.1892 + ) AS "minus1_unfulfilled_count", 1.1893 + ( SELECT coalesce(sum("di"."weight"), 0) 1.1894 + FROM "opinion" JOIN "direct_interest_snapshot" AS "di" 1.1895 + ON "di"."snapshot_id" = "snapshot_id_v" 1.1896 + AND "di"."issue_id" = "issue_id_v" 1.1897 + AND "di"."member_id" = "opinion"."member_id" 1.1898 + WHERE "opinion"."suggestion_id" = "suggestion"."id" 1.1899 + AND "opinion"."degree" = -1 1.1900 + AND "opinion"."fulfilled" = TRUE 1.1901 + ) AS "minus1_fulfilled_count", 1.1902 + ( SELECT coalesce(sum("di"."weight"), 0) 1.1903 + FROM "opinion" JOIN "direct_interest_snapshot" AS "di" 1.1904 + ON "di"."snapshot_id" = "snapshot_id_v" 1.1905 + AND "di"."issue_id" = "issue_id_v" 1.1906 + AND "di"."member_id" = "opinion"."member_id" 1.1907 + WHERE "opinion"."suggestion_id" = "suggestion"."id" 1.1908 + AND "opinion"."degree" = 1 1.1909 + AND "opinion"."fulfilled" = FALSE 1.1910 + ) AS "plus1_unfulfilled_count", 1.1911 + ( SELECT coalesce(sum("di"."weight"), 0) 1.1912 + FROM "opinion" JOIN "direct_interest_snapshot" AS "di" 1.1913 + ON "di"."snapshot_id" = "snapshot_id_v" 1.1914 + AND "di"."issue_id" = "issue_id_v" 1.1915 + AND "di"."member_id" = "opinion"."member_id" 1.1916 + WHERE "opinion"."suggestion_id" = "suggestion"."id" 1.1917 + AND "opinion"."degree" = 1 1.1918 + AND "opinion"."fulfilled" = TRUE 1.1919 + ) AS "plus1_fulfilled_count", 1.1920 + ( SELECT coalesce(sum("di"."weight"), 0) 1.1921 + FROM "opinion" JOIN "direct_interest_snapshot" AS "di" 1.1922 + ON "di"."snapshot_id" = "snapshot_id_v" 1.1923 + AND "di"."issue_id" = "issue_id_v" 1.1924 + AND "di"."member_id" = "opinion"."member_id" 1.1925 + WHERE "opinion"."suggestion_id" = "suggestion"."id" 1.1926 + AND "opinion"."degree" = 2 1.1927 + AND "opinion"."fulfilled" = FALSE 1.1928 + ) AS "plus2_unfulfilled_count", 1.1929 + ( SELECT coalesce(sum("di"."weight"), 0) 1.1930 + FROM "opinion" JOIN "direct_interest_snapshot" AS "di" 1.1931 + ON "di"."snapshot_id" = "snapshot_id_v" 1.1932 + AND "di"."issue_id" = "issue_id_v" 1.1933 + AND "di"."member_id" = "opinion"."member_id" 1.1934 + WHERE "opinion"."suggestion_id" = "suggestion"."id" 1.1935 + AND "opinion"."degree" = 2 1.1936 + AND "opinion"."fulfilled" = TRUE 1.1937 + ) AS "plus2_fulfilled_count" 1.1938 + FROM "suggestion" JOIN "initiative" 1.1939 + ON "suggestion"."initiative_id" = "initiative"."id" 1.1940 + WHERE "initiative"."issue_id" = "issue_id_v"; 1.1941 + END LOOP; 1.1942 + RETURN "snapshot_id_v"; 1.1943 + END; 1.1944 + $$; 1.1945 + 1.1946 +CREATE OR REPLACE FUNCTION "weight_of_added_vote_delegations" 1.1947 + ( "issue_id_p" "issue"."id"%TYPE, 1.1948 + "member_id_p" "member"."id"%TYPE, 1.1949 + "delegate_member_ids_p" "delegating_voter"."delegate_member_ids"%TYPE ) 1.1950 + RETURNS "direct_voter"."weight"%TYPE 1.1951 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.1952 + DECLARE 1.1953 + "issue_delegation_row" "issue_delegation"%ROWTYPE; 1.1954 + "delegate_member_ids_v" "delegating_voter"."delegate_member_ids"%TYPE; 1.1955 + "weight_v" INT4; 1.1956 + "sub_weight_v" INT4; 1.1957 + BEGIN 1.1958 + PERFORM "require_transaction_isolation"(); 1.1959 + "weight_v" := 0; 1.1960 + FOR "issue_delegation_row" IN 1.1961 + SELECT * FROM "issue_delegation" 1.1962 + WHERE "trustee_id" = "member_id_p" 1.1963 + AND "issue_id" = "issue_id_p" 1.1964 + LOOP 1.1965 + IF NOT EXISTS ( 1.1966 + SELECT NULL FROM "direct_voter" 1.1967 + WHERE "member_id" = "issue_delegation_row"."truster_id" 1.1968 + AND "issue_id" = "issue_id_p" 1.1969 + ) AND NOT EXISTS ( 1.1970 + SELECT NULL FROM "delegating_voter" 1.1971 + WHERE "member_id" = "issue_delegation_row"."truster_id" 1.1972 + AND "issue_id" = "issue_id_p" 1.1973 + ) THEN 1.1974 + "delegate_member_ids_v" := 1.1975 + "member_id_p" || "delegate_member_ids_p"; 1.1976 + INSERT INTO "delegating_voter" ( 1.1977 + "issue_id", 1.1978 + "member_id", 1.1979 + "ownweight", 1.1980 + "scope", 1.1981 + "delegate_member_ids" 1.1982 + ) VALUES ( 1.1983 + "issue_id_p", 1.1984 + "issue_delegation_row"."truster_id", 1.1985 + "issue_delegation_row"."weight", 1.1986 + "issue_delegation_row"."scope", 1.1987 + "delegate_member_ids_v" 1.1988 + ); 1.1989 + "sub_weight_v" := "issue_delegation_row"."weight" + 1.1990 + "weight_of_added_vote_delegations"( 1.1991 + "issue_id_p", 1.1992 + "issue_delegation_row"."truster_id", 1.1993 + "delegate_member_ids_v" 1.1994 + ); 1.1995 + UPDATE "delegating_voter" 1.1996 + SET "weight" = "sub_weight_v" 1.1997 + WHERE "issue_id" = "issue_id_p" 1.1998 + AND "member_id" = "issue_delegation_row"."truster_id"; 1.1999 + "weight_v" := "weight_v" + "sub_weight_v"; 1.2000 + END IF; 1.2001 + END LOOP; 1.2002 + RETURN "weight_v"; 1.2003 + END; 1.2004 + $$; 1.2005 + 1.2006 +CREATE OR REPLACE FUNCTION "add_vote_delegations" 1.2007 + ( "issue_id_p" "issue"."id"%TYPE ) 1.2008 + RETURNS VOID 1.2009 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.2010 + DECLARE 1.2011 + "member_id_v" "member"."id"%TYPE; 1.2012 + BEGIN 1.2013 + PERFORM "require_transaction_isolation"(); 1.2014 + FOR "member_id_v" IN 1.2015 + SELECT "member_id" FROM "direct_voter" 1.2016 + WHERE "issue_id" = "issue_id_p" 1.2017 + LOOP 1.2018 + UPDATE "direct_voter" SET 1.2019 + "weight" = "ownweight" + "weight_of_added_vote_delegations"( 1.2020 + "issue_id_p", 1.2021 + "member_id_v", 1.2022 + '{}' 1.2023 + ) 1.2024 + WHERE "member_id" = "member_id_v" 1.2025 + AND "issue_id" = "issue_id_p"; 1.2026 + END LOOP; 1.2027 + RETURN; 1.2028 + END; 1.2029 + $$; 1.2030 + 1.2031 +CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE) 1.2032 + RETURNS VOID 1.2033 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.2034 + DECLARE 1.2035 + "area_id_v" "area"."id"%TYPE; 1.2036 + "unit_id_v" "unit"."id"%TYPE; 1.2037 + "member_id_v" "member"."id"%TYPE; 1.2038 + BEGIN 1.2039 + PERFORM "require_transaction_isolation"(); 1.2040 + SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p"; 1.2041 + SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v"; 1.2042 + -- override protection triggers: 1.2043 + INSERT INTO "temporary_transaction_data" ("key", "value") 1.2044 + VALUES ('override_protection_triggers', TRUE::TEXT); 1.2045 + -- delete timestamp of voting comment: 1.2046 + UPDATE "direct_voter" SET "comment_changed" = NULL 1.2047 + WHERE "issue_id" = "issue_id_p"; 1.2048 + -- delete delegating votes (in cases of manual reset of issue state): 1.2049 + DELETE FROM "delegating_voter" 1.2050 + WHERE "issue_id" = "issue_id_p"; 1.2051 + -- delete votes from non-privileged voters: 1.2052 + DELETE FROM "direct_voter" 1.2053 + USING ( 1.2054 + SELECT "direct_voter"."member_id" 1.2055 + FROM "direct_voter" 1.2056 + JOIN "member" ON "direct_voter"."member_id" = "member"."id" 1.2057 + LEFT JOIN "privilege" 1.2058 + ON "privilege"."unit_id" = "unit_id_v" 1.2059 + AND "privilege"."member_id" = "direct_voter"."member_id" 1.2060 + LEFT JOIN "issue_privilege" 1.2061 + ON "issue_privilege"."issue_id" = "issue_id_p" 1.2062 + AND "issue_privilege"."member_id" = "direct_voter"."member_id" 1.2063 + WHERE "direct_voter"."issue_id" = "issue_id_p" AND ( 1.2064 + "member"."active" = FALSE OR 1.2065 + COALESCE( 1.2066 + "issue_privilege"."voting_right", 1.2067 + "privilege"."voting_right", 1.2068 + FALSE 1.2069 + ) = FALSE 1.2070 + ) 1.2071 + ) AS "subquery" 1.2072 + WHERE "direct_voter"."issue_id" = "issue_id_p" 1.2073 + AND "direct_voter"."member_id" = "subquery"."member_id"; 1.2074 + -- consider voting weight and delegations: 1.2075 + UPDATE "direct_voter" SET "ownweight" = "privilege"."weight" 1.2076 + FROM "privilege" 1.2077 + WHERE "issue_id" = "issue_id_p" 1.2078 + AND "privilege"."unit_id" = "unit_id_v" 1.2079 + AND "privilege"."member_id" = "direct_voter"."member_id"; 1.2080 + UPDATE "direct_voter" SET "ownweight" = "issue_privilege"."weight" 1.2081 + FROM "issue_privilege" 1.2082 + WHERE "direct_voter"."issue_id" = "issue_id_p" 1.2083 + AND "issue_privilege"."issue_id" = "issue_id_p" 1.2084 + AND "issue_privilege"."member_id" = "direct_voter"."member_id"; 1.2085 + PERFORM "add_vote_delegations"("issue_id_p"); 1.2086 + -- mark first preferences: 1.2087 + UPDATE "vote" SET "first_preference" = "subquery"."first_preference" 1.2088 + FROM ( 1.2089 + SELECT 1.2090 + "vote"."initiative_id", 1.2091 + "vote"."member_id", 1.2092 + CASE WHEN "vote"."grade" > 0 THEN 1.2093 + CASE WHEN "vote"."grade" = max("agg"."grade") THEN TRUE ELSE FALSE END 1.2094 + ELSE NULL 1.2095 + END AS "first_preference" 1.2096 + FROM "vote" 1.2097 + JOIN "initiative" -- NOTE: due to missing index on issue_id 1.2098 + ON "vote"."issue_id" = "initiative"."issue_id" 1.2099 + JOIN "vote" AS "agg" 1.2100 + ON "initiative"."id" = "agg"."initiative_id" 1.2101 + AND "vote"."member_id" = "agg"."member_id" 1.2102 + GROUP BY "vote"."initiative_id", "vote"."member_id", "vote"."grade" 1.2103 + ) AS "subquery" 1.2104 + WHERE "vote"."issue_id" = "issue_id_p" 1.2105 + AND "vote"."initiative_id" = "subquery"."initiative_id" 1.2106 + AND "vote"."member_id" = "subquery"."member_id"; 1.2107 + -- finish overriding protection triggers (avoids garbage): 1.2108 + DELETE FROM "temporary_transaction_data" 1.2109 + WHERE "key" = 'override_protection_triggers'; 1.2110 + -- materialize battle_view: 1.2111 + -- NOTE: "closed" column of issue must be set at this point 1.2112 + DELETE FROM "battle" WHERE "issue_id" = "issue_id_p"; 1.2113 + INSERT INTO "battle" ( 1.2114 + "issue_id", 1.2115 + "winning_initiative_id", "losing_initiative_id", 1.2116 + "count" 1.2117 + ) SELECT 1.2118 + "issue_id", 1.2119 + "winning_initiative_id", "losing_initiative_id", 1.2120 + "count" 1.2121 + FROM "battle_view" WHERE "issue_id" = "issue_id_p"; 1.2122 + -- set voter count: 1.2123 + UPDATE "issue" SET 1.2124 + "voter_count" = ( 1.2125 + SELECT coalesce(sum("weight"), 0) 1.2126 + FROM "direct_voter" WHERE "issue_id" = "issue_id_p" 1.2127 + ) 1.2128 + WHERE "id" = "issue_id_p"; 1.2129 + -- copy "positive_votes" and "negative_votes" from "battle" table: 1.2130 + -- NOTE: "first_preference_votes" is set to a default of 0 at this step 1.2131 + UPDATE "initiative" SET 1.2132 + "first_preference_votes" = 0, 1.2133 + "positive_votes" = "battle_win"."count", 1.2134 + "negative_votes" = "battle_lose"."count" 1.2135 + FROM "battle" AS "battle_win", "battle" AS "battle_lose" 1.2136 + WHERE 1.2137 + "battle_win"."issue_id" = "issue_id_p" AND 1.2138 + "battle_win"."winning_initiative_id" = "initiative"."id" AND 1.2139 + "battle_win"."losing_initiative_id" ISNULL AND 1.2140 + "battle_lose"."issue_id" = "issue_id_p" AND 1.2141 + "battle_lose"."losing_initiative_id" = "initiative"."id" AND 1.2142 + "battle_lose"."winning_initiative_id" ISNULL; 1.2143 + -- calculate "first_preference_votes": 1.2144 + -- NOTE: will only set values not equal to zero 1.2145 + UPDATE "initiative" SET "first_preference_votes" = "subquery"."sum" 1.2146 + FROM ( 1.2147 + SELECT "vote"."initiative_id", sum("direct_voter"."weight") 1.2148 + FROM "vote" JOIN "direct_voter" 1.2149 + ON "vote"."issue_id" = "direct_voter"."issue_id" 1.2150 + AND "vote"."member_id" = "direct_voter"."member_id" 1.2151 + WHERE "vote"."first_preference" 1.2152 + GROUP BY "vote"."initiative_id" 1.2153 + ) AS "subquery" 1.2154 + WHERE "initiative"."issue_id" = "issue_id_p" 1.2155 + AND "initiative"."admitted" 1.2156 + AND "initiative"."id" = "subquery"."initiative_id"; 1.2157 + END; 1.2158 + $$; 1.2159 + 1.2160 +COMMIT;