liquid_feedback_core
changeset 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 | 5b3b20f1278d |
children | e0b8175fda29 |
files | core.sql update/core-update.v4.1.0-v4.2.0.sql update/core-update.v4.1.0-v4.2.1.sql update/core-update.v4.2.0-v4.2.1.sql |
line diff
1.1 --- a/core.sql Fri Nov 27 15:45:31 2020 +0100 1.2 +++ b/core.sql Sat Dec 05 04:04:52 2020 +0100 1.3 @@ -3,12 +3,10 @@ 1.4 1.5 BEGIN; 1.6 1.7 -CREATE EXTENSION IF NOT EXISTS btree_gist; 1.8 CREATE EXTENSION IF NOT EXISTS latlon; 1.9 -CREATE EXTENSION IF NOT EXISTS conflux; 1.10 1.11 CREATE VIEW "liquid_feedback_version" AS 1.12 - SELECT * FROM (VALUES ('4.2.0', 4, 2, 0)) 1.13 + SELECT * FROM (VALUES ('4.2.1', 4, 2, 1)) 1.14 AS "subquery"("string", "major", "minor", "revision"); 1.15 1.16 1.17 @@ -1403,13 +1401,6 @@ 1.18 "initiative_id" ISNULL OR "issue_id" NOTNULL ), 1.19 CONSTRAINT "suggestion_requires_initiative" CHECK ( 1.20 "suggestion_id" ISNULL OR "initiative_id" NOTNULL ) ); 1.21 -CREATE INDEX "posting_global_idx" ON "posting" USING gist ((pstamp("author_id", "id"))); 1.22 -CREATE INDEX "posting_unit_idx" ON "posting" USING gist ("unit_id", (pstamp("author_id", "id"))) WHERE "unit_id" NOTNULL; 1.23 -CREATE INDEX "posting_area_idx" ON "posting" USING gist ("area_id", (pstamp("author_id", "id"))) WHERE "area_id" NOTNULL; 1.24 -CREATE INDEX "posting_policy_idx" ON "posting" USING gist ("policy_id", (pstamp("author_id", "id"))) WHERE "policy_id" NOTNULL; 1.25 -CREATE INDEX "posting_issue_idx" ON "posting" USING gist ("issue_id", (pstamp("author_id", "id"))) WHERE "issue_id" NOTNULL; 1.26 -CREATE INDEX "posting_initiative_idx" ON "posting" USING gist ("initiative_id", (pstamp("author_id", "id"))) WHERE "initiative_id" NOTNULL; 1.27 -CREATE INDEX "posting_suggestion_idx" ON "posting" USING gist ("suggestion_id", (pstamp("author_id", "id"))) WHERE "suggestion_id" NOTNULL; 1.28 1.29 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.30 1.31 @@ -1420,7 +1411,6 @@ 1.32 "posting_id" INT8, 1.33 "lexeme" TEXT, 1.34 "author_id" INT4 ); 1.35 -CREATE INDEX "posting_lexeme_idx" ON "posting_lexeme" USING gist ("lexeme", (pstamp("author_id", "posting_id"))); 1.36 1.37 COMMENT ON TABLE "posting_lexeme" IS 'Helper table to allow searches for hashtags.'; 1.38 1.39 @@ -1773,14 +1763,6 @@ 1.40 "text_value" ISNULL AND 1.41 "old_text_value" ISNULL )) ); 1.42 CREATE INDEX "event_occurrence_idx" ON "event" ("occurrence"); 1.43 -CREATE INDEX "event_tl_global_idx" ON "event" USING gist ((pstamp("member_id", "id"))); 1.44 -CREATE INDEX "event_tl_unit_idx" ON "event" USING gist ("unit_id", (pstamp("member_id", "id"))) WHERE "unit_id" NOTNULL; 1.45 -CREATE INDEX "event_tl_area_idx" ON "event" USING gist ("area_id", (pstamp("member_id", "id"))) WHERE "area_id" NOTNULL; 1.46 -CREATE INDEX "event_tl_policy_idx" ON "event" USING gist ("policy_id", (pstamp("member_id", "id"))) WHERE "policy_id" NOTNULL; 1.47 -CREATE INDEX "event_tl_issue_idx" ON "event" USING gist ("issue_id", (pstamp("member_id", "id"))) WHERE "issue_id" NOTNULL; 1.48 -CREATE INDEX "event_tl_initiative_idx" ON "event" USING gist ("initiative_id", (pstamp("member_id", "id"))) WHERE "initiative_id" NOTNULL; 1.49 -CREATE INDEX "event_tl_suggestion_idx" ON "event" USING gist ("suggestion_id", (pstamp("member_id", "id"))) WHERE "suggestion_id" NOTNULL; 1.50 - 1.51 1.52 COMMENT ON TABLE "event" IS 'Event table, automatically filled by triggers'; 1.53
2.1 --- a/update/core-update.v4.1.0-v4.2.0.sql Fri Nov 27 15:45:31 2020 +0100 2.2 +++ b/update/core-update.v4.1.0-v4.2.0.sql Sat Dec 05 04:04:52 2020 +0100 2.3 @@ -1,5 +1,5 @@ 2.4 CREATE OR REPLACE VIEW "liquid_feedback_version" AS 2.5 - SELECT * FROM (VALUES ('4.2.0', 4, 2, 0)) 2.6 + SELECT * FROM (VALUES ('4.2.0-incomplete-update', 4, 2, -1)) 2.7 AS "subquery"("string", "major", "minor", "revision"); 2.8 2.9 ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'posting_created'; 2.10 @@ -22,7 +22,7 @@ 2.11 DROP VIEW "member_eligible_to_be_notified"; 2.12 2.13 CREATE EXTENSION IF NOT EXISTS btree_gist; 2.14 -CREATE EXTENSION IF NOT EXISTS conflux; 2.15 +CREATE EXTENSION IF NOT EXISTS conflux VERSION "0.5"; 2.16 2.17 DROP FUNCTION "text_search_query" (TEXT); 2.18 2.19 @@ -1200,4 +1200,4 @@ 2.20 2.21 SELECT "copy_current_draft_data" ("id") FROM "initiative"; 2.22 2.23 -END; 2.24 +COMMIT;
3.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000 3.2 +++ b/update/core-update.v4.1.0-v4.2.1.sql Sat Dec 05 04:04:52 2020 +0100 3.3 @@ -0,0 +1,2157 @@ 3.4 +CREATE OR REPLACE VIEW "liquid_feedback_version" AS 3.5 + SELECT * FROM (VALUES ('4.2.0-incomplete-update', 4, 2, -1)) 3.6 + AS "subquery"("string", "major", "minor", "revision"); 3.7 + 3.8 +ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'posting_created'; 3.9 + 3.10 +BEGIN; 3.11 + 3.12 +CREATE OR REPLACE VIEW "liquid_feedback_version" AS 3.13 + SELECT * FROM (VALUES ('4.2.1', 4, 2, 1)) 3.14 + AS "subquery"("string", "major", "minor", "revision"); 3.15 + 3.16 +DROP VIEW "newsletter_to_send"; 3.17 +DROP VIEW "scheduled_notification_to_send"; 3.18 +DROP VIEW "member_contingent_left"; 3.19 +DROP VIEW "member_contingent"; 3.20 +DROP VIEW "expired_snapshot"; 3.21 +DROP VIEW "current_draft"; 3.22 +DROP VIEW "opening_draft"; 3.23 +DROP VIEW "area_with_unaccepted_issues"; 3.24 +DROP VIEW "member_to_notify"; 3.25 +DROP VIEW "member_eligible_to_be_notified"; 3.26 + 3.27 +DROP FUNCTION "text_search_query" (TEXT); 3.28 + 3.29 +ALTER TABLE "system_setting" DROP COLUMN "snapshot_retention"; 3.30 + 3.31 +CREATE TABLE "file" ( 3.32 + "id" SERIAL8 PRIMARY KEY, 3.33 + UNIQUE ("content_type", "hash"), 3.34 + "content_type" TEXT NOT NULL, 3.35 + "hash" TEXT NOT NULL, 3.36 + "data" BYTEA NOT NULL, 3.37 + "preview_content_type" TEXT, 3.38 + "preview_data" BYTEA ); 3.39 + 3.40 +COMMENT ON TABLE "file" IS 'Table holding file contents for draft attachments'; 3.41 + 3.42 +COMMENT ON COLUMN "file"."content_type" IS 'Content type of "data"'; 3.43 +COMMENT ON COLUMN "file"."hash" IS 'Hash of "data" to avoid storing duplicates where content-type and data is identical'; 3.44 +COMMENT ON COLUMN "file"."data" IS 'Binary content'; 3.45 +COMMENT ON COLUMN "file"."preview_content_type" IS 'Content type of "preview_data"'; 3.46 +COMMENT ON COLUMN "file"."preview_data" IS 'Preview (e.g. preview image)'; 3.47 + 3.48 +ALTER TABLE "member" DROP COLUMN "text_search_data"; 3.49 +DROP TRIGGER "update_text_search_data" ON "member"; 3.50 + 3.51 +CREATE INDEX "member_useterms_member_id_contract_identifier" ON "member_useterms" ("member_id", "contract_identifier"); 3.52 + 3.53 +ALTER TABLE "member_profile" DROP COLUMN "text_search_data"; 3.54 +DROP TRIGGER "update_text_search_data" ON "member_profile"; 3.55 + 3.56 +ALTER TABLE "contact" ADD COLUMN "following" BOOLEAN NOT NULL DEFAULT TRUE; 3.57 + 3.58 +COMMENT ON COLUMN "contact"."following" IS 'TRUE = actions of contact are shown in personal timeline'; 3.59 + 3.60 +ALTER TABLE "unit" DROP COLUMN "text_search_data"; 3.61 +DROP TRIGGER "update_text_search_data" ON "unit"; 3.62 + 3.63 +ALTER TABLE "area" DROP COLUMN "text_search_data"; 3.64 +DROP TRIGGER "update_text_search_data" ON "area"; 3.65 + 3.66 +DROP INDEX "issue_accepted_idx"; 3.67 +DROP INDEX "issue_half_frozen_idx"; 3.68 +DROP INDEX "issue_fully_frozen_idx"; 3.69 +ALTER INDEX "issue_created_idx_open" RENAME TO "issue_open_created_idx"; 3.70 +DROP INDEX "issue_closed_idx_canceled"; 3.71 +ALTER INDEX "issue_latest_snapshot_id" RENAME TO "issue_latest_snapshot_id_idx"; 3.72 +ALTER INDEX "issue_admission_snapshot_id" RENAME TO "issue_admission_snapshot_id_idx"; 3.73 +ALTER INDEX "issue_half_freeze_snapshot_id" RENAME TO "issue_half_freeze_snapshot_id_idx"; 3.74 +ALTER INDEX "issue_full_freeze_snapshot_id" RENAME TO "issue_full_freeze_snapshot_id_idx"; 3.75 + 3.76 +ALTER TABLE "initiative" ADD COLUMN "content" TEXT; 3.77 +ALTER TABLE "initiative" DROP COLUMN "text_search_data"; 3.78 +ALTER TABLE "initiative" DROP COLUMN "draft_text_search_data"; 3.79 +DROP INDEX "initiative_revoked_idx"; 3.80 +DROP TRIGGER "update_text_search_data" ON "initiative"; 3.81 + 3.82 +COMMENT ON COLUMN "initiative"."content" IS 'Initiative text (automatically copied from most recent draft)'; 3.83 + 3.84 +ALTER TABLE "battle" DROP CONSTRAINT "initiative_ids_not_equal"; 3.85 +ALTER TABLE "battle" ADD CONSTRAINT "initiative_ids_not_equal" CHECK ( 3.86 + "winning_initiative_id" != "losing_initiative_id" AND 3.87 + ("winning_initiative_id" NOTNULL OR "losing_initiative_id" NOTNULL) ); 3.88 + 3.89 +ALTER TABLE "draft" DROP COLUMN "text_search_data"; 3.90 +DROP TRIGGER "update_text_search_data" ON "draft"; 3.91 + 3.92 +CREATE TABLE "draft_attachment" ( 3.93 + "id" SERIAL8 PRIMARY KEY, 3.94 + "draft_id" INT8 REFERENCES "draft" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 3.95 + "file_id" INT8 REFERENCES "file" ("id") ON DELETE RESTRICT ON UPDATE CASCADE, 3.96 + "title" TEXT, 3.97 + "description" TEXT ); 3.98 + 3.99 +COMMENT ON TABLE "draft_attachment" IS 'Binary attachments for a draft (images, PDF file, etc.); Implicitly ordered through ''id'' column'; 3.100 + 3.101 +ALTER TABLE "suggestion" DROP COLUMN "text_search_data"; 3.102 +DROP TRIGGER "update_text_search_data" ON "suggestion"; 3.103 + 3.104 +ALTER TABLE "direct_voter" DROP COLUMN "text_search_data"; 3.105 +DROP TRIGGER "update_text_search_data" ON "direct_voter"; 3.106 + 3.107 +CREATE TABLE "posting" ( 3.108 + UNIQUE ("author_id", "id"), -- index needed for foreign-key on table "posting_lexeme" 3.109 + "id" SERIAL8 PRIMARY KEY, 3.110 + "author_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE, 3.111 + "created" TIMESTAMPTZ NOT NULL DEFAULT now(), 3.112 + "message" TEXT NOT NULL, 3.113 + "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 3.114 + "area_id" INT4, 3.115 + FOREIGN KEY ("unit_id", "area_id") REFERENCES "area" ("unit_id", "id") ON DELETE CASCADE ON UPDATE CASCADE, 3.116 + "policy_id" INT4 REFERENCES "policy" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 3.117 + "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 3.118 + FOREIGN KEY ("area_id", "issue_id") REFERENCES "issue" ("area_id", "id") ON DELETE CASCADE ON UPDATE CASCADE, 3.119 + FOREIGN KEY ("policy_id", "issue_id") REFERENCES "issue" ("policy_id", "id") ON DELETE CASCADE ON UPDATE CASCADE, 3.120 + "initiative_id" INT4, 3.121 + "suggestion_id" INT8, 3.122 + -- NOTE: no referential integrity for suggestions because those are 3.123 + -- actually deleted 3.124 + -- FOREIGN KEY ("initiative_id", "suggestion_id") 3.125 + -- REFERENCES "suggestion" ("initiative_id", "id") 3.126 + -- ON DELETE CASCADE ON UPDATE CASCADE, 3.127 + CONSTRAINT "area_requires_unit" CHECK ( 3.128 + "area_id" ISNULL OR "unit_id" NOTNULL ), 3.129 + CONSTRAINT "policy_set_when_issue_set" CHECK ( 3.130 + ("policy_id" NOTNULL) = ("issue_id" NOTNULL) ), 3.131 + CONSTRAINT "issue_requires_area" CHECK ( 3.132 + "issue_id" ISNULL OR "area_id" NOTNULL ), 3.133 + CONSTRAINT "initiative_requires_issue" CHECK ( 3.134 + "initiative_id" ISNULL OR "issue_id" NOTNULL ), 3.135 + CONSTRAINT "suggestion_requires_initiative" CHECK ( 3.136 + "suggestion_id" ISNULL OR "initiative_id" NOTNULL ) ); 3.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'; 3.138 + 3.139 +CREATE TABLE "posting_lexeme" ( 3.140 + PRIMARY KEY ("posting_id", "lexeme"), 3.141 + FOREIGN KEY ("posting_id", "author_id") REFERENCES "posting" ("id", "author_id") ON DELETE CASCADE ON UPDATE CASCADE, 3.142 + "posting_id" INT8, 3.143 + "lexeme" TEXT, 3.144 + "author_id" INT4 ); 3.145 + 3.146 +COMMENT ON TABLE "posting_lexeme" IS 'Helper table to allow searches for hashtags.'; 3.147 + 3.148 +ALTER TABLE "event" ADD COLUMN "posting_id" INT8 REFERENCES "posting" ("id") ON DELETE RESTRICT ON UPDATE CASCADE; 3.149 +ALTER TABLE "event" DROP CONSTRAINT "constr_for_issue_state_changed"; 3.150 +ALTER TABLE "event" DROP CONSTRAINT "constr_for_initiative_creation_or_revocation_or_new_draft"; 3.151 +ALTER TABLE "event" DROP CONSTRAINT "constr_for_suggestion_creation"; 3.152 +ALTER TABLE "event" DROP CONSTRAINT "constr_for_suggestion_removal"; 3.153 +ALTER TABLE "event" DROP CONSTRAINT "constr_for_value_less_member_event"; 3.154 +ALTER TABLE "event" DROP CONSTRAINT "constr_for_member_active"; 3.155 +ALTER TABLE "event" DROP CONSTRAINT "constr_for_member_name_updated"; 3.156 +ALTER TABLE "event" DROP CONSTRAINT "constr_for_interest"; 3.157 +ALTER TABLE "event" DROP CONSTRAINT "constr_for_initiator"; 3.158 +ALTER TABLE "event" DROP CONSTRAINT "constr_for_support"; 3.159 +ALTER TABLE "event" DROP CONSTRAINT "constr_for_support_updated"; 3.160 +ALTER TABLE "event" DROP CONSTRAINT "constr_for_suggestion_rated"; 3.161 +ALTER TABLE "event" DROP CONSTRAINT "constr_for_delegation"; 3.162 +ALTER TABLE "event" DROP CONSTRAINT "constr_for_contact"; 3.163 +ALTER TABLE "event" ADD 3.164 + CONSTRAINT "constr_for_issue_state_changed" CHECK ( 3.165 + "event" != 'issue_state_changed' OR ( 3.166 + "posting_id" ISNULL AND 3.167 + "member_id" ISNULL AND 3.168 + "other_member_id" ISNULL AND 3.169 + "scope" ISNULL AND 3.170 + "unit_id" NOTNULL AND 3.171 + "area_id" NOTNULL AND 3.172 + "policy_id" NOTNULL AND 3.173 + "issue_id" NOTNULL AND 3.174 + "state" NOTNULL AND 3.175 + "initiative_id" ISNULL AND 3.176 + "draft_id" ISNULL AND 3.177 + "suggestion_id" ISNULL AND 3.178 + "boolean_value" ISNULL AND 3.179 + "numeric_value" ISNULL AND 3.180 + "text_value" ISNULL AND 3.181 + "old_text_value" ISNULL )); 3.182 +ALTER TABLE "event" ADD 3.183 + CONSTRAINT "constr_for_initiative_creation_or_revocation_or_new_draft" CHECK ( 3.184 + "event" NOT IN ( 3.185 + 'initiative_created_in_new_issue', 3.186 + 'initiative_created_in_existing_issue', 3.187 + 'initiative_revoked', 3.188 + 'new_draft_created' 3.189 + ) OR ( 3.190 + "posting_id" ISNULL AND 3.191 + "member_id" NOTNULL AND 3.192 + "other_member_id" ISNULL AND 3.193 + "scope" ISNULL AND 3.194 + "unit_id" NOTNULL AND 3.195 + "area_id" NOTNULL AND 3.196 + "policy_id" NOTNULL AND 3.197 + "issue_id" NOTNULL AND 3.198 + "state" NOTNULL AND 3.199 + "initiative_id" NOTNULL AND 3.200 + "draft_id" NOTNULL AND 3.201 + "suggestion_id" ISNULL AND 3.202 + "boolean_value" ISNULL AND 3.203 + "numeric_value" ISNULL AND 3.204 + "text_value" ISNULL AND 3.205 + "old_text_value" ISNULL )); 3.206 +ALTER TABLE "event" ADD 3.207 + CONSTRAINT "constr_for_suggestion_creation" CHECK ( 3.208 + "event" != 'suggestion_created' OR ( 3.209 + "posting_id" ISNULL AND 3.210 + "member_id" NOTNULL AND 3.211 + "other_member_id" ISNULL AND 3.212 + "scope" ISNULL AND 3.213 + "unit_id" NOTNULL AND 3.214 + "area_id" NOTNULL AND 3.215 + "policy_id" NOTNULL AND 3.216 + "issue_id" NOTNULL AND 3.217 + "state" NOTNULL AND 3.218 + "initiative_id" NOTNULL AND 3.219 + "draft_id" ISNULL AND 3.220 + "suggestion_id" NOTNULL AND 3.221 + "boolean_value" ISNULL AND 3.222 + "numeric_value" ISNULL AND 3.223 + "text_value" ISNULL AND 3.224 + "old_text_value" ISNULL )); 3.225 +ALTER TABLE "event" ADD 3.226 + CONSTRAINT "constr_for_suggestion_removal" CHECK ( 3.227 + "event" != 'suggestion_deleted' OR ( 3.228 + "posting_id" ISNULL AND 3.229 + "member_id" ISNULL AND 3.230 + "other_member_id" ISNULL AND 3.231 + "scope" ISNULL AND 3.232 + "unit_id" NOTNULL AND 3.233 + "area_id" NOTNULL AND 3.234 + "policy_id" NOTNULL AND 3.235 + "issue_id" NOTNULL AND 3.236 + "state" NOTNULL AND 3.237 + "initiative_id" NOTNULL AND 3.238 + "draft_id" ISNULL AND 3.239 + "suggestion_id" NOTNULL AND 3.240 + "boolean_value" ISNULL AND 3.241 + "numeric_value" ISNULL AND 3.242 + "text_value" ISNULL AND 3.243 + "old_text_value" ISNULL )); 3.244 +ALTER TABLE "event" ADD 3.245 + CONSTRAINT "constr_for_value_less_member_event" CHECK ( 3.246 + "event" NOT IN ( 3.247 + 'member_activated', 3.248 + 'member_deleted', 3.249 + 'member_profile_updated', 3.250 + 'member_image_updated' 3.251 + ) OR ( 3.252 + "posting_id" ISNULL AND 3.253 + "member_id" NOTNULL AND 3.254 + "other_member_id" ISNULL AND 3.255 + "scope" ISNULL AND 3.256 + "unit_id" ISNULL AND 3.257 + "area_id" ISNULL AND 3.258 + "policy_id" ISNULL AND 3.259 + "issue_id" ISNULL AND 3.260 + "state" ISNULL AND 3.261 + "initiative_id" ISNULL AND 3.262 + "draft_id" ISNULL AND 3.263 + "suggestion_id" ISNULL AND 3.264 + "boolean_value" ISNULL AND 3.265 + "numeric_value" ISNULL AND 3.266 + "text_value" ISNULL AND 3.267 + "old_text_value" ISNULL )); 3.268 +ALTER TABLE "event" ADD 3.269 + CONSTRAINT "constr_for_member_active" CHECK ( 3.270 + "event" != 'member_active' OR ( 3.271 + "posting_id" ISNULL AND 3.272 + "member_id" NOTNULL AND 3.273 + "other_member_id" ISNULL AND 3.274 + "scope" ISNULL AND 3.275 + "unit_id" ISNULL AND 3.276 + "area_id" ISNULL AND 3.277 + "policy_id" ISNULL AND 3.278 + "issue_id" ISNULL AND 3.279 + "state" ISNULL AND 3.280 + "initiative_id" ISNULL AND 3.281 + "draft_id" ISNULL AND 3.282 + "suggestion_id" ISNULL AND 3.283 + "boolean_value" NOTNULL AND 3.284 + "numeric_value" ISNULL AND 3.285 + "text_value" ISNULL AND 3.286 + "old_text_value" ISNULL )); 3.287 +ALTER TABLE "event" ADD 3.288 + CONSTRAINT "constr_for_member_name_updated" CHECK ( 3.289 + "event" != 'member_name_updated' OR ( 3.290 + "posting_id" ISNULL AND 3.291 + "member_id" NOTNULL AND 3.292 + "other_member_id" ISNULL AND 3.293 + "scope" ISNULL AND 3.294 + "unit_id" ISNULL AND 3.295 + "area_id" ISNULL AND 3.296 + "policy_id" ISNULL AND 3.297 + "issue_id" ISNULL AND 3.298 + "state" ISNULL AND 3.299 + "initiative_id" ISNULL AND 3.300 + "draft_id" ISNULL AND 3.301 + "suggestion_id" ISNULL AND 3.302 + "boolean_value" ISNULL AND 3.303 + "numeric_value" ISNULL AND 3.304 + "text_value" NOTNULL AND 3.305 + "old_text_value" NOTNULL )); 3.306 +ALTER TABLE "event" ADD 3.307 + CONSTRAINT "constr_for_interest" CHECK ( 3.308 + "event" != 'interest' OR ( 3.309 + "posting_id" ISNULL AND 3.310 + "member_id" NOTNULL AND 3.311 + "other_member_id" ISNULL AND 3.312 + "scope" ISNULL AND 3.313 + "unit_id" NOTNULL AND 3.314 + "area_id" NOTNULL AND 3.315 + "policy_id" NOTNULL AND 3.316 + "issue_id" NOTNULL AND 3.317 + "state" NOTNULL AND 3.318 + "initiative_id" ISNULL AND 3.319 + "draft_id" ISNULL AND 3.320 + "suggestion_id" ISNULL AND 3.321 + "boolean_value" NOTNULL AND 3.322 + "numeric_value" ISNULL AND 3.323 + "text_value" ISNULL AND 3.324 + "old_text_value" ISNULL )); 3.325 +ALTER TABLE "event" ADD 3.326 + CONSTRAINT "constr_for_initiator" CHECK ( 3.327 + "event" != 'initiator' OR ( 3.328 + "posting_id" ISNULL AND 3.329 + "member_id" NOTNULL AND 3.330 + "other_member_id" ISNULL AND 3.331 + "scope" ISNULL AND 3.332 + "unit_id" NOTNULL AND 3.333 + "area_id" NOTNULL AND 3.334 + "policy_id" NOTNULL AND 3.335 + "issue_id" NOTNULL AND 3.336 + "state" NOTNULL AND 3.337 + "initiative_id" NOTNULL AND 3.338 + "draft_id" ISNULL AND 3.339 + "suggestion_id" ISNULL AND 3.340 + "boolean_value" NOTNULL AND 3.341 + "numeric_value" ISNULL AND 3.342 + "text_value" ISNULL AND 3.343 + "old_text_value" ISNULL )); 3.344 +ALTER TABLE "event" ADD 3.345 + CONSTRAINT "constr_for_support" CHECK ( 3.346 + "event" != 'support' OR ( 3.347 + "posting_id" ISNULL AND 3.348 + "member_id" NOTNULL AND 3.349 + "other_member_id" ISNULL AND 3.350 + "scope" ISNULL AND 3.351 + "unit_id" NOTNULL AND 3.352 + "area_id" NOTNULL AND 3.353 + "policy_id" NOTNULL AND 3.354 + "issue_id" NOTNULL AND 3.355 + "state" NOTNULL AND 3.356 + "initiative_id" NOTNULL AND 3.357 + ("draft_id" NOTNULL) = ("boolean_value" = TRUE) AND 3.358 + "suggestion_id" ISNULL AND 3.359 + "boolean_value" NOTNULL AND 3.360 + "numeric_value" ISNULL AND 3.361 + "text_value" ISNULL AND 3.362 + "old_text_value" ISNULL )); 3.363 +ALTER TABLE "event" ADD 3.364 + CONSTRAINT "constr_for_support_updated" CHECK ( 3.365 + "event" != 'support_updated' OR ( 3.366 + "posting_id" ISNULL AND 3.367 + "member_id" NOTNULL AND 3.368 + "other_member_id" ISNULL AND 3.369 + "scope" ISNULL AND 3.370 + "unit_id" NOTNULL AND 3.371 + "area_id" NOTNULL AND 3.372 + "policy_id" NOTNULL AND 3.373 + "issue_id" NOTNULL AND 3.374 + "state" NOTNULL AND 3.375 + "initiative_id" NOTNULL AND 3.376 + "draft_id" NOTNULL AND 3.377 + "suggestion_id" ISNULL AND 3.378 + "boolean_value" ISNULL AND 3.379 + "numeric_value" ISNULL AND 3.380 + "text_value" ISNULL AND 3.381 + "old_text_value" ISNULL )); 3.382 +ALTER TABLE "event" ADD 3.383 + CONSTRAINT "constr_for_suggestion_rated" CHECK ( 3.384 + "event" != 'suggestion_rated' OR ( 3.385 + "posting_id" ISNULL AND 3.386 + "member_id" NOTNULL AND 3.387 + "other_member_id" ISNULL AND 3.388 + "scope" ISNULL AND 3.389 + "unit_id" NOTNULL AND 3.390 + "area_id" NOTNULL AND 3.391 + "policy_id" NOTNULL AND 3.392 + "issue_id" NOTNULL AND 3.393 + "state" NOTNULL AND 3.394 + "initiative_id" NOTNULL AND 3.395 + "draft_id" ISNULL AND 3.396 + "suggestion_id" NOTNULL AND 3.397 + ("boolean_value" NOTNULL) = ("numeric_value" != 0) AND 3.398 + "numeric_value" NOTNULL AND 3.399 + "numeric_value" IN (-2, -1, 0, 1, 2) AND 3.400 + "text_value" ISNULL AND 3.401 + "old_text_value" ISNULL )); 3.402 +ALTER TABLE "event" ADD 3.403 + CONSTRAINT "constr_for_delegation" CHECK ( 3.404 + "event" != 'delegation' OR ( 3.405 + "posting_id" ISNULL AND 3.406 + "member_id" NOTNULL AND 3.407 + (("other_member_id" ISNULL) OR ("boolean_value" = TRUE)) AND 3.408 + "scope" NOTNULL AND 3.409 + "unit_id" NOTNULL AND 3.410 + ("area_id" NOTNULL) = ("scope" != 'unit'::"delegation_scope") AND 3.411 + "policy_id" ISNULL AND 3.412 + ("issue_id" NOTNULL) = ("scope" = 'issue'::"delegation_scope") AND 3.413 + ("state" NOTNULL) = ("scope" = 'issue'::"delegation_scope") AND 3.414 + "initiative_id" ISNULL AND 3.415 + "draft_id" ISNULL AND 3.416 + "suggestion_id" ISNULL AND 3.417 + "boolean_value" NOTNULL AND 3.418 + "numeric_value" ISNULL AND 3.419 + "text_value" ISNULL AND 3.420 + "old_text_value" ISNULL )); 3.421 +ALTER TABLE "event" ADD 3.422 + CONSTRAINT "constr_for_contact" CHECK ( 3.423 + "event" != 'contact' OR ( 3.424 + "posting_id" ISNULL AND 3.425 + "member_id" NOTNULL AND 3.426 + "other_member_id" NOTNULL AND 3.427 + "scope" ISNULL AND 3.428 + "unit_id" ISNULL AND 3.429 + "area_id" ISNULL AND 3.430 + "policy_id" ISNULL AND 3.431 + "issue_id" ISNULL AND 3.432 + "state" ISNULL AND 3.433 + "initiative_id" ISNULL AND 3.434 + "draft_id" ISNULL AND 3.435 + "suggestion_id" ISNULL AND 3.436 + "boolean_value" NOTNULL AND 3.437 + "numeric_value" ISNULL AND 3.438 + "text_value" ISNULL AND 3.439 + "old_text_value" ISNULL )); 3.440 +ALTER TABLE "event" ADD 3.441 + CONSTRAINT "constr_for_posting_created" CHECK ( 3.442 + "event" != 'posting_created' OR ( 3.443 + "posting_id" NOTNULL AND 3.444 + "member_id" NOTNULL AND 3.445 + "other_member_id" ISNULL AND 3.446 + "scope" ISNULL AND 3.447 + "state" ISNULL AND 3.448 + ("area_id" ISNULL OR "unit_id" NOTNULL) AND 3.449 + ("policy_id" NOTNULL) = ("issue_id" NOTNULL) AND 3.450 + ("issue_id" ISNULL OR "area_id" NOTNULL) AND 3.451 + ("state" NOTNULL) = ("issue_id" NOTNULL) AND 3.452 + ("initiative_id" ISNULL OR "issue_id" NOTNULL) AND 3.453 + "draft_id" ISNULL AND 3.454 + ("suggestion_id" ISNULL OR "initiative_id" NOTNULL) AND 3.455 + "boolean_value" ISNULL AND 3.456 + "numeric_value" ISNULL AND 3.457 + "text_value" ISNULL AND 3.458 + "old_text_value" ISNULL )); 3.459 + 3.460 +CREATE OR REPLACE FUNCTION "highlight" 3.461 + ( "body_p" TEXT, 3.462 + "query_text_p" TEXT ) 3.463 + RETURNS TEXT 3.464 + LANGUAGE 'plpgsql' IMMUTABLE AS $$ 3.465 + BEGIN 3.466 + RETURN ts_headline( 3.467 + replace(replace("body_p", e'\\', e'\\\\'), '*', e'\\*'), 3.468 + "plainto_tsquery"("query_text_p"), 3.469 + 'StartSel=* StopSel=* HighlightAll=TRUE' ); 3.470 + END; 3.471 + $$; 3.472 + 3.473 +CREATE FUNCTION "to_tsvector"("member") RETURNS TSVECTOR 3.474 + LANGUAGE SQL IMMUTABLE AS $$ SELECT to_tsvector(concat_ws(' ', 3.475 + $1."name", 3.476 + $1."identification" 3.477 + )) $$; 3.478 +CREATE INDEX "member_to_tsvector_idx" ON "member" USING gin 3.479 + (("to_tsvector"("member".*))); 3.480 + 3.481 +CREATE FUNCTION "to_tsvector"("member_profile") RETURNS TSVECTOR 3.482 + LANGUAGE SQL IMMUTABLE AS $$ SELECT to_tsvector(concat_ws(' ', 3.483 + $1."statement", 3.484 + $1."profile_text_data" 3.485 + )) $$; 3.486 +CREATE INDEX "member_profile_to_tsvector_idx" ON "member_profile" USING gin 3.487 + (("to_tsvector"("member_profile".*))); 3.488 + 3.489 +CREATE FUNCTION "to_tsvector"("unit") RETURNS TSVECTOR 3.490 + LANGUAGE SQL IMMUTABLE AS $$ SELECT to_tsvector(concat_ws(' ', 3.491 + $1."name", 3.492 + $1."description" 3.493 + )) $$; 3.494 +CREATE INDEX "unit_to_tsvector_idx" ON "unit" USING gin 3.495 + (("to_tsvector"("unit".*))); 3.496 + 3.497 +CREATE FUNCTION "to_tsvector"("area") RETURNS TSVECTOR 3.498 + LANGUAGE SQL IMMUTABLE AS $$ SELECT to_tsvector(concat_ws(' ', 3.499 + $1."name", 3.500 + $1."description" 3.501 + )) $$; 3.502 +CREATE INDEX "area_to_tsvector_idx" ON "area" USING gin 3.503 + (("to_tsvector"("area".*))); 3.504 + 3.505 +CREATE FUNCTION "to_tsvector"("initiative") RETURNS TSVECTOR 3.506 + LANGUAGE SQL IMMUTABLE AS $$ SELECT to_tsvector(concat_ws(' ', 3.507 + $1."name", 3.508 + $1."content" 3.509 + )) $$; 3.510 +CREATE INDEX "initiative_to_tsvector_idx" ON "initiative" USING gin 3.511 + (("to_tsvector"("initiative".*))); 3.512 + 3.513 +CREATE FUNCTION "to_tsvector"("draft") RETURNS TSVECTOR 3.514 + LANGUAGE SQL IMMUTABLE AS $$ SELECT to_tsvector(concat_ws(' ', 3.515 + $1."content" 3.516 + )) $$; 3.517 +CREATE INDEX "draft_to_tsvector_idx" ON "draft" USING gin 3.518 + (("to_tsvector"("draft".*))); 3.519 + 3.520 +CREATE FUNCTION "to_tsvector"("suggestion") RETURNS TSVECTOR 3.521 + LANGUAGE SQL IMMUTABLE AS $$ SELECT to_tsvector(concat_ws(' ', 3.522 + $1."name", 3.523 + $1."content" 3.524 + )) $$; 3.525 +CREATE INDEX "suggestion_to_tsvector_idx" ON "suggestion" USING gin 3.526 + (("to_tsvector"("suggestion".*))); 3.527 + 3.528 +CREATE FUNCTION "to_tsvector"("direct_voter") RETURNS TSVECTOR 3.529 + LANGUAGE SQL IMMUTABLE AS $$ SELECT to_tsvector(concat_ws(' ', 3.530 + $1."comment" 3.531 + )) $$; 3.532 +CREATE INDEX "direct_voter_to_tsvector_idx" ON "direct_voter" USING gin 3.533 + (("to_tsvector"("direct_voter".*))); 3.534 + 3.535 +CREATE FUNCTION "update_posting_lexeme_trigger"() 3.536 + RETURNS TRIGGER 3.537 + LANGUAGE 'plpgsql' VOLATILE AS $$ 3.538 + DECLARE 3.539 + "lexeme_v" TEXT; 3.540 + BEGIN 3.541 + IF TG_OP = 'DELETE' OR TG_OP = 'UPDATE' THEN 3.542 + DELETE FROM "posting_lexeme" WHERE "posting_id" = OLD."id"; 3.543 + END IF; 3.544 + IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN 3.545 + FOR "lexeme_v" IN 3.546 + SELECT regexp_matches[1] 3.547 + FROM regexp_matches(NEW."message", '#[^\s.,;:]+') 3.548 + LOOP 3.549 + INSERT INTO "posting_lexeme" ("posting_id", "author_id", "lexeme") 3.550 + VALUES ( 3.551 + NEW."id", 3.552 + NEW."author_id", 3.553 + "lexeme_v" ) 3.554 + ON CONFLICT ("posting_id", "lexeme") DO NOTHING; 3.555 + END LOOP; 3.556 + END IF; 3.557 + RETURN NULL; 3.558 + END; 3.559 + $$; 3.560 + 3.561 +CREATE TRIGGER "update_posting_lexeme" 3.562 + AFTER INSERT OR UPDATE OR DELETE ON "posting" 3.563 + FOR EACH ROW EXECUTE PROCEDURE "update_posting_lexeme_trigger"(); 3.564 + 3.565 +COMMENT ON FUNCTION "update_posting_lexeme_trigger"() IS 'Implementation of trigger "update_posting_lexeme" on table "posting"'; 3.566 +COMMENT ON TRIGGER "update_posting_lexeme" ON "posting" IS 'Keeps table "posting_lexeme" up to date'; 3.567 + 3.568 +CREATE FUNCTION "write_event_posting_trigger"() 3.569 + RETURNS TRIGGER 3.570 + LANGUAGE 'plpgsql' VOLATILE AS $$ 3.571 + BEGIN 3.572 + INSERT INTO "event" ( 3.573 + "event", "posting_id", "member_id", 3.574 + "unit_id", "area_id", "policy_id", 3.575 + "issue_id", "initiative_id", "suggestion_id" 3.576 + ) VALUES ( 3.577 + 'posting_created', NEW."id", NEW."author_id", 3.578 + NEW."unit_id", NEW."area_id", NEW."policy_id", 3.579 + NEW."issue_id", NEW."initiative_id", NEW."suggestion_id" 3.580 + ); 3.581 + RETURN NULL; 3.582 + END; 3.583 + $$; 3.584 + 3.585 +CREATE TRIGGER "write_event_posting" 3.586 + AFTER INSERT ON "posting" FOR EACH ROW EXECUTE PROCEDURE 3.587 + "write_event_posting_trigger"(); 3.588 + 3.589 +COMMENT ON FUNCTION "write_event_posting_trigger"() IS 'Implementation of trigger "write_event_posting" on table "posting"'; 3.590 +COMMENT ON TRIGGER "write_event_posting" ON "posting" IS 'Create entry in "event" table when creating a new posting'; 3.591 + 3.592 +CREATE FUNCTION "file_requires_reference_trigger"() 3.593 + RETURNS TRIGGER 3.594 + LANGUAGE 'plpgsql' VOLATILE AS $$ 3.595 + BEGIN 3.596 + IF NOT EXISTS ( 3.597 + SELECT NULL FROM "draft_attachment" WHERE "file_id" = NEW."id" 3.598 + ) THEN 3.599 + RAISE EXCEPTION 'Cannot create an unreferenced file.' USING 3.600 + ERRCODE = 'integrity_constraint_violation', 3.601 + HINT = 'Create file and its reference in another table within the same transaction.'; 3.602 + END IF; 3.603 + RETURN NULL; 3.604 + END; 3.605 + $$; 3.606 + 3.607 +CREATE CONSTRAINT TRIGGER "file_requires_reference" 3.608 + AFTER INSERT OR UPDATE ON "file" DEFERRABLE INITIALLY DEFERRED 3.609 + FOR EACH ROW EXECUTE PROCEDURE 3.610 + "file_requires_reference_trigger"(); 3.611 + 3.612 +COMMENT ON FUNCTION "file_requires_reference_trigger"() IS 'Implementation of trigger "file_requires_reference" on table "file"'; 3.613 +COMMENT ON TRIGGER "file_requires_reference" ON "file" IS 'Ensure that files are always referenced'; 3.614 + 3.615 +CREATE FUNCTION "last_reference_deletes_file_trigger"() 3.616 + RETURNS TRIGGER 3.617 + LANGUAGE 'plpgsql' VOLATILE AS $$ 3.618 + DECLARE 3.619 + "reference_lost" BOOLEAN; 3.620 + BEGIN 3.621 + IF TG_OP = 'DELETE' THEN 3.622 + "reference_lost" := TRUE; 3.623 + ELSE 3.624 + "reference_lost" := NEW."file_id" != OLD."file_id"; 3.625 + END IF; 3.626 + IF 3.627 + "reference_lost" AND NOT EXISTS ( 3.628 + SELECT NULL FROM "draft_attachment" WHERE "file_id" = OLD."file_id" 3.629 + ) 3.630 + THEN 3.631 + DELETE FROM "file" WHERE "id" = OLD."file_id"; 3.632 + END IF; 3.633 + RETURN NULL; 3.634 + END; 3.635 + $$; 3.636 + 3.637 +CREATE CONSTRAINT TRIGGER "last_reference_deletes_file" 3.638 + AFTER UPDATE OR DELETE ON "draft_attachment" DEFERRABLE INITIALLY DEFERRED 3.639 + FOR EACH ROW EXECUTE PROCEDURE 3.640 + "last_reference_deletes_file_trigger"(); 3.641 + 3.642 +COMMENT ON FUNCTION "last_reference_deletes_file_trigger"() IS 'Implementation of trigger "last_reference_deletes_file" on table "draft_attachment"'; 3.643 +COMMENT ON TRIGGER "last_reference_deletes_file" ON "draft_attachment" IS 'Removing the last reference to a file deletes the file'; 3.644 + 3.645 +CREATE OR REPLACE FUNCTION "copy_current_draft_data" 3.646 + ("initiative_id_p" "initiative"."id"%TYPE ) 3.647 + RETURNS VOID 3.648 + LANGUAGE 'plpgsql' VOLATILE AS $$ 3.649 + BEGIN 3.650 + PERFORM NULL FROM "initiative" WHERE "id" = "initiative_id_p" 3.651 + FOR UPDATE; 3.652 + UPDATE "initiative" SET 3.653 + "location" = "draft"."location", 3.654 + "content" = "draft"."content" 3.655 + FROM "current_draft" AS "draft" 3.656 + WHERE "initiative"."id" = "initiative_id_p" 3.657 + AND "draft"."initiative_id" = "initiative_id_p"; 3.658 + END; 3.659 + $$; 3.660 + 3.661 +CREATE VIEW "follower" AS 3.662 + SELECT 3.663 + "id" AS "follower_id", 3.664 + ( SELECT ARRAY["member"."id"] || array_agg("contact"."other_member_id") 3.665 + FROM "contact" 3.666 + WHERE "contact"."member_id" = "member"."id" AND "contact"."following" ) 3.667 + AS "following_ids" 3.668 + FROM "member"; 3.669 + 3.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'; 3.671 + 3.672 +CREATE OR REPLACE FUNCTION "check_issue" 3.673 + ( "issue_id_p" "issue"."id"%TYPE, 3.674 + "persist" "check_issue_persistence" ) 3.675 + RETURNS "check_issue_persistence" 3.676 + LANGUAGE 'plpgsql' VOLATILE AS $$ 3.677 + DECLARE 3.678 + "issue_row" "issue"%ROWTYPE; 3.679 + "last_calculated_v" "snapshot"."calculated"%TYPE; 3.680 + "policy_row" "policy"%ROWTYPE; 3.681 + "initiative_row" "initiative"%ROWTYPE; 3.682 + "state_v" "issue_state"; 3.683 + BEGIN 3.684 + PERFORM "require_transaction_isolation"(); 3.685 + IF "persist" ISNULL THEN 3.686 + SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p" 3.687 + FOR UPDATE; 3.688 + SELECT "calculated" INTO "last_calculated_v" 3.689 + FROM "snapshot" JOIN "snapshot_issue" 3.690 + ON "snapshot"."id" = "snapshot_issue"."snapshot_id" 3.691 + WHERE "snapshot_issue"."issue_id" = "issue_id_p" 3.692 + ORDER BY "snapshot"."id" DESC; 3.693 + IF "issue_row"."closed" NOTNULL THEN 3.694 + RETURN NULL; 3.695 + END IF; 3.696 + "persist"."state" := "issue_row"."state"; 3.697 + IF 3.698 + ( "issue_row"."state" = 'admission' AND "last_calculated_v" >= 3.699 + "issue_row"."created" + "issue_row"."max_admission_time" ) OR 3.700 + ( "issue_row"."state" = 'discussion' AND now() >= 3.701 + "issue_row"."accepted" + "issue_row"."discussion_time" ) OR 3.702 + ( "issue_row"."state" = 'verification' AND now() >= 3.703 + "issue_row"."half_frozen" + "issue_row"."verification_time" ) OR 3.704 + ( "issue_row"."state" = 'voting' AND now() >= 3.705 + "issue_row"."fully_frozen" + "issue_row"."voting_time" ) 3.706 + THEN 3.707 + "persist"."phase_finished" := TRUE; 3.708 + ELSE 3.709 + "persist"."phase_finished" := FALSE; 3.710 + END IF; 3.711 + IF 3.712 + NOT EXISTS ( 3.713 + -- all initiatives are revoked 3.714 + SELECT NULL FROM "initiative" 3.715 + WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL 3.716 + ) AND ( 3.717 + -- and issue has not been accepted yet 3.718 + "persist"."state" = 'admission' OR 3.719 + -- or verification time has elapsed 3.720 + ( "persist"."state" = 'verification' AND 3.721 + "persist"."phase_finished" ) OR 3.722 + -- or no initiatives have been revoked lately 3.723 + NOT EXISTS ( 3.724 + SELECT NULL FROM "initiative" 3.725 + WHERE "issue_id" = "issue_id_p" 3.726 + AND now() < "revoked" + "issue_row"."verification_time" 3.727 + ) 3.728 + ) 3.729 + THEN 3.730 + "persist"."issue_revoked" := TRUE; 3.731 + ELSE 3.732 + "persist"."issue_revoked" := FALSE; 3.733 + END IF; 3.734 + IF "persist"."phase_finished" OR "persist"."issue_revoked" THEN 3.735 + UPDATE "issue" SET "phase_finished" = now() 3.736 + WHERE "id" = "issue_row"."id"; 3.737 + RETURN "persist"; 3.738 + ELSIF 3.739 + "persist"."state" IN ('admission', 'discussion', 'verification') 3.740 + THEN 3.741 + RETURN "persist"; 3.742 + ELSE 3.743 + RETURN NULL; 3.744 + END IF; 3.745 + END IF; 3.746 + IF 3.747 + "persist"."state" IN ('admission', 'discussion', 'verification') AND 3.748 + coalesce("persist"."snapshot_created", FALSE) = FALSE 3.749 + THEN 3.750 + IF "persist"."state" != 'admission' THEN 3.751 + PERFORM "take_snapshot"("issue_id_p"); 3.752 + PERFORM "finish_snapshot"("issue_id_p"); 3.753 + ELSE 3.754 + UPDATE "issue" SET "issue_quorum" = "issue_quorum"."issue_quorum" 3.755 + FROM "issue_quorum" 3.756 + WHERE "id" = "issue_id_p" 3.757 + AND "issue_quorum"."issue_id" = "issue_id_p"; 3.758 + END IF; 3.759 + "persist"."snapshot_created" = TRUE; 3.760 + IF "persist"."phase_finished" THEN 3.761 + IF "persist"."state" = 'admission' THEN 3.762 + UPDATE "issue" SET "admission_snapshot_id" = "latest_snapshot_id" 3.763 + WHERE "id" = "issue_id_p"; 3.764 + ELSIF "persist"."state" = 'discussion' THEN 3.765 + UPDATE "issue" SET "half_freeze_snapshot_id" = "latest_snapshot_id" 3.766 + WHERE "id" = "issue_id_p"; 3.767 + ELSIF "persist"."state" = 'verification' THEN 3.768 + UPDATE "issue" SET "full_freeze_snapshot_id" = "latest_snapshot_id" 3.769 + WHERE "id" = "issue_id_p"; 3.770 + SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"; 3.771 + FOR "initiative_row" IN 3.772 + SELECT * FROM "initiative" 3.773 + WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL 3.774 + FOR UPDATE 3.775 + LOOP 3.776 + IF 3.777 + "initiative_row"."polling" OR 3.778 + "initiative_row"."satisfied_supporter_count" >= 3.779 + "issue_row"."initiative_quorum" 3.780 + THEN 3.781 + UPDATE "initiative" SET "admitted" = TRUE 3.782 + WHERE "id" = "initiative_row"."id"; 3.783 + ELSE 3.784 + UPDATE "initiative" SET "admitted" = FALSE 3.785 + WHERE "id" = "initiative_row"."id"; 3.786 + END IF; 3.787 + END LOOP; 3.788 + END IF; 3.789 + END IF; 3.790 + RETURN "persist"; 3.791 + END IF; 3.792 + IF 3.793 + "persist"."state" IN ('admission', 'discussion', 'verification') AND 3.794 + coalesce("persist"."harmonic_weights_set", FALSE) = FALSE 3.795 + THEN 3.796 + PERFORM "set_harmonic_initiative_weights"("issue_id_p"); 3.797 + "persist"."harmonic_weights_set" = TRUE; 3.798 + IF 3.799 + "persist"."phase_finished" OR 3.800 + "persist"."issue_revoked" OR 3.801 + "persist"."state" = 'admission' 3.802 + THEN 3.803 + RETURN "persist"; 3.804 + ELSE 3.805 + RETURN NULL; 3.806 + END IF; 3.807 + END IF; 3.808 + IF "persist"."issue_revoked" THEN 3.809 + IF "persist"."state" = 'admission' THEN 3.810 + "state_v" := 'canceled_revoked_before_accepted'; 3.811 + ELSIF "persist"."state" = 'discussion' THEN 3.812 + "state_v" := 'canceled_after_revocation_during_discussion'; 3.813 + ELSIF "persist"."state" = 'verification' THEN 3.814 + "state_v" := 'canceled_after_revocation_during_verification'; 3.815 + END IF; 3.816 + UPDATE "issue" SET 3.817 + "state" = "state_v", 3.818 + "closed" = "phase_finished", 3.819 + "phase_finished" = NULL 3.820 + WHERE "id" = "issue_id_p"; 3.821 + RETURN NULL; 3.822 + END IF; 3.823 + IF "persist"."state" = 'admission' THEN 3.824 + SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p" 3.825 + FOR UPDATE; 3.826 + IF "issue_row"."phase_finished" NOTNULL THEN 3.827 + UPDATE "issue" SET 3.828 + "state" = 'canceled_issue_not_accepted', 3.829 + "closed" = "phase_finished", 3.830 + "phase_finished" = NULL 3.831 + WHERE "id" = "issue_id_p"; 3.832 + END IF; 3.833 + RETURN NULL; 3.834 + END IF; 3.835 + IF "persist"."phase_finished" THEN 3.836 + IF "persist"."state" = 'discussion' THEN 3.837 + UPDATE "issue" SET 3.838 + "state" = 'verification', 3.839 + "half_frozen" = "phase_finished", 3.840 + "phase_finished" = NULL 3.841 + WHERE "id" = "issue_id_p"; 3.842 + RETURN NULL; 3.843 + END IF; 3.844 + IF "persist"."state" = 'verification' THEN 3.845 + SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p" 3.846 + FOR UPDATE; 3.847 + SELECT * INTO "policy_row" FROM "policy" 3.848 + WHERE "id" = "issue_row"."policy_id"; 3.849 + IF EXISTS ( 3.850 + SELECT NULL FROM "initiative" 3.851 + WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE 3.852 + ) THEN 3.853 + UPDATE "issue" SET 3.854 + "state" = 'voting', 3.855 + "fully_frozen" = "phase_finished", 3.856 + "phase_finished" = NULL 3.857 + WHERE "id" = "issue_id_p"; 3.858 + ELSE 3.859 + UPDATE "issue" SET 3.860 + "state" = 'canceled_no_initiative_admitted', 3.861 + "fully_frozen" = "phase_finished", 3.862 + "closed" = "phase_finished", 3.863 + "phase_finished" = NULL 3.864 + WHERE "id" = "issue_id_p"; 3.865 + -- NOTE: The following DELETE statements have effect only when 3.866 + -- issue state has been manipulated 3.867 + DELETE FROM "direct_voter" WHERE "issue_id" = "issue_id_p"; 3.868 + DELETE FROM "delegating_voter" WHERE "issue_id" = "issue_id_p"; 3.869 + DELETE FROM "battle" WHERE "issue_id" = "issue_id_p"; 3.870 + END IF; 3.871 + RETURN NULL; 3.872 + END IF; 3.873 + IF "persist"."state" = 'voting' THEN 3.874 + IF coalesce("persist"."closed_voting", FALSE) = FALSE THEN 3.875 + PERFORM "close_voting"("issue_id_p"); 3.876 + "persist"."closed_voting" = TRUE; 3.877 + RETURN "persist"; 3.878 + END IF; 3.879 + PERFORM "calculate_ranks"("issue_id_p"); 3.880 + RETURN NULL; 3.881 + END IF; 3.882 + END IF; 3.883 + RAISE WARNING 'should not happen'; 3.884 + RETURN NULL; 3.885 + END; 3.886 + $$; 3.887 + 3.888 +CREATE OR REPLACE FUNCTION "check_everything"() 3.889 + RETURNS VOID 3.890 + LANGUAGE 'plpgsql' VOLATILE AS $$ 3.891 + DECLARE 3.892 + "area_id_v" "area"."id"%TYPE; 3.893 + "snapshot_id_v" "snapshot"."id"%TYPE; 3.894 + "issue_id_v" "issue"."id"%TYPE; 3.895 + "persist_v" "check_issue_persistence"; 3.896 + BEGIN 3.897 + RAISE WARNING 'Function "check_everything" should only be used for development and debugging purposes'; 3.898 + DELETE FROM "expired_session"; 3.899 + DELETE FROM "expired_token"; 3.900 + DELETE FROM "unused_snapshot"; 3.901 + PERFORM "check_activity"(); 3.902 + PERFORM "calculate_member_counts"(); 3.903 + FOR "area_id_v" IN SELECT "id" FROM "area_with_unaccepted_issues" LOOP 3.904 + SELECT "take_snapshot"(NULL, "area_id_v") INTO "snapshot_id_v"; 3.905 + PERFORM "finish_snapshot"("issue_id") FROM "snapshot_issue" 3.906 + WHERE "snapshot_id" = "snapshot_id_v"; 3.907 + LOOP 3.908 + EXIT WHEN "issue_admission"("area_id_v") = FALSE; 3.909 + END LOOP; 3.910 + END LOOP; 3.911 + FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP 3.912 + "persist_v" := NULL; 3.913 + LOOP 3.914 + "persist_v" := "check_issue"("issue_id_v", "persist_v"); 3.915 + EXIT WHEN "persist_v" ISNULL; 3.916 + END LOOP; 3.917 + END LOOP; 3.918 + DELETE FROM "unused_snapshot"; 3.919 + RETURN; 3.920 + END; 3.921 + $$; 3.922 + 3.923 +CREATE OR REPLACE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE) 3.924 + RETURNS VOID 3.925 + LANGUAGE 'plpgsql' VOLATILE AS $$ 3.926 + BEGIN 3.927 + UPDATE "member" SET 3.928 + "last_login" = NULL, 3.929 + "last_delegation_check" = NULL, 3.930 + "login" = NULL, 3.931 + "password" = NULL, 3.932 + "authority" = NULL, 3.933 + "authority_uid" = NULL, 3.934 + "authority_login" = NULL, 3.935 + "deleted" = coalesce("deleted", now()), 3.936 + "locked" = TRUE, 3.937 + "active" = FALSE, 3.938 + "notify_email" = NULL, 3.939 + "notify_email_unconfirmed" = NULL, 3.940 + "notify_email_secret" = NULL, 3.941 + "notify_email_secret_expiry" = NULL, 3.942 + "notify_email_lock_expiry" = NULL, 3.943 + "disable_notifications" = TRUE, 3.944 + "notification_counter" = DEFAULT, 3.945 + "notification_sample_size" = 0, 3.946 + "notification_dow" = NULL, 3.947 + "notification_hour" = NULL, 3.948 + "notification_sent" = NULL, 3.949 + "login_recovery_expiry" = NULL, 3.950 + "password_reset_secret" = NULL, 3.951 + "password_reset_secret_expiry" = NULL, 3.952 + "location" = NULL 3.953 + WHERE "id" = "member_id_p"; 3.954 + DELETE FROM "member_settings" WHERE "member_id" = "member_id_p"; 3.955 + DELETE FROM "member_profile" WHERE "member_id" = "member_id_p"; 3.956 + DELETE FROM "rendered_member_statement" WHERE "member_id" = "member_id_p"; 3.957 + DELETE FROM "member_image" WHERE "member_id" = "member_id_p"; 3.958 + DELETE FROM "contact" WHERE "member_id" = "member_id_p"; 3.959 + DELETE FROM "ignored_member" WHERE "member_id" = "member_id_p"; 3.960 + DELETE FROM "session" WHERE "member_id" = "member_id_p"; 3.961 + DELETE FROM "member_application" WHERE "member_id" = "member_id_p"; 3.962 + DELETE FROM "token" WHERE "member_id" = "member_id_p"; 3.963 + DELETE FROM "subscription" WHERE "member_id" = "member_id_p"; 3.964 + DELETE FROM "ignored_area" WHERE "member_id" = "member_id_p"; 3.965 + DELETE FROM "ignored_initiative" WHERE "member_id" = "member_id_p"; 3.966 + DELETE FROM "delegation" WHERE "truster_id" = "member_id_p"; 3.967 + DELETE FROM "non_voter" WHERE "member_id" = "member_id_p"; 3.968 + DELETE FROM "direct_voter" USING "issue" 3.969 + WHERE "direct_voter"."issue_id" = "issue"."id" 3.970 + AND "issue"."closed" ISNULL 3.971 + AND "member_id" = "member_id_p"; 3.972 + DELETE FROM "notification_initiative_sent" WHERE "member_id" = "member_id_p"; 3.973 + RETURN; 3.974 + END; 3.975 + $$; 3.976 + 3.977 +CREATE OR REPLACE FUNCTION "delete_private_data"() 3.978 + RETURNS VOID 3.979 + LANGUAGE 'plpgsql' VOLATILE AS $$ 3.980 + BEGIN 3.981 + DELETE FROM "temporary_transaction_data"; 3.982 + DELETE FROM "temporary_suggestion_counts"; 3.983 + DELETE FROM "member" WHERE "activated" ISNULL; 3.984 + UPDATE "member" SET 3.985 + "invite_code" = NULL, 3.986 + "invite_code_expiry" = NULL, 3.987 + "admin_comment" = NULL, 3.988 + "last_login" = NULL, 3.989 + "last_delegation_check" = NULL, 3.990 + "login" = NULL, 3.991 + "password" = NULL, 3.992 + "authority" = NULL, 3.993 + "authority_uid" = NULL, 3.994 + "authority_login" = NULL, 3.995 + "lang" = NULL, 3.996 + "notify_email" = NULL, 3.997 + "notify_email_unconfirmed" = NULL, 3.998 + "notify_email_secret" = NULL, 3.999 + "notify_email_secret_expiry" = NULL, 3.1000 + "notify_email_lock_expiry" = NULL, 3.1001 + "disable_notifications" = TRUE, 3.1002 + "notification_counter" = DEFAULT, 3.1003 + "notification_sample_size" = 0, 3.1004 + "notification_dow" = NULL, 3.1005 + "notification_hour" = NULL, 3.1006 + "notification_sent" = NULL, 3.1007 + "login_recovery_expiry" = NULL, 3.1008 + "password_reset_secret" = NULL, 3.1009 + "password_reset_secret_expiry" = NULL, 3.1010 + "location" = NULL; 3.1011 + DELETE FROM "verification"; 3.1012 + DELETE FROM "member_settings"; 3.1013 + DELETE FROM "member_useterms"; 3.1014 + DELETE FROM "member_profile"; 3.1015 + DELETE FROM "rendered_member_statement"; 3.1016 + DELETE FROM "member_image"; 3.1017 + DELETE FROM "contact"; 3.1018 + DELETE FROM "ignored_member"; 3.1019 + DELETE FROM "session"; 3.1020 + DELETE FROM "system_application"; 3.1021 + DELETE FROM "system_application_redirect_uri"; 3.1022 + DELETE FROM "dynamic_application_scope"; 3.1023 + DELETE FROM "member_application"; 3.1024 + DELETE FROM "token"; 3.1025 + DELETE FROM "subscription"; 3.1026 + DELETE FROM "ignored_area"; 3.1027 + DELETE FROM "ignored_initiative"; 3.1028 + DELETE FROM "non_voter"; 3.1029 + DELETE FROM "direct_voter" USING "issue" 3.1030 + WHERE "direct_voter"."issue_id" = "issue"."id" 3.1031 + AND "issue"."closed" ISNULL; 3.1032 + DELETE FROM "event_processed"; 3.1033 + DELETE FROM "notification_initiative_sent"; 3.1034 + DELETE FROM "newsletter"; 3.1035 + RETURN; 3.1036 + END; 3.1037 + $$; 3.1038 + 3.1039 +CREATE VIEW "member_eligible_to_be_notified" AS 3.1040 + SELECT * FROM "member" 3.1041 + WHERE "activated" NOTNULL AND "locked" = FALSE; 3.1042 + 3.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")'; 3.1044 + 3.1045 +CREATE VIEW "member_to_notify" AS 3.1046 + SELECT * FROM "member_eligible_to_be_notified" 3.1047 + WHERE "disable_notifications" = FALSE; 3.1048 + 3.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)'; 3.1050 + 3.1051 +CREATE VIEW "area_with_unaccepted_issues" AS 3.1052 + SELECT DISTINCT ON ("area"."id") "area".* 3.1053 + FROM "area" JOIN "issue" ON "area"."id" = "issue"."area_id" 3.1054 + WHERE "issue"."state" = 'admission'; 3.1055 + 3.1056 +COMMENT ON VIEW "area_with_unaccepted_issues" IS 'All areas with unaccepted open issues (needed for issue admission system)'; 3.1057 + 3.1058 +CREATE VIEW "opening_draft" AS 3.1059 + SELECT DISTINCT ON ("initiative_id") * FROM "draft" 3.1060 + ORDER BY "initiative_id", "id"; 3.1061 + 3.1062 +COMMENT ON VIEW "opening_draft" IS 'First drafts of all initiatives'; 3.1063 + 3.1064 +CREATE VIEW "current_draft" AS 3.1065 + SELECT DISTINCT ON ("initiative_id") * FROM "draft" 3.1066 + ORDER BY "initiative_id", "id" DESC; 3.1067 + 3.1068 +COMMENT ON VIEW "current_draft" IS 'All latest drafts for each initiative'; 3.1069 + 3.1070 +CREATE VIEW "member_contingent" AS 3.1071 + SELECT 3.1072 + "member"."id" AS "member_id", 3.1073 + "contingent"."polling", 3.1074 + "contingent"."time_frame", 3.1075 + CASE WHEN "contingent"."text_entry_limit" NOTNULL THEN 3.1076 + ( 3.1077 + SELECT count(1) FROM "draft" 3.1078 + JOIN "initiative" ON "initiative"."id" = "draft"."initiative_id" 3.1079 + WHERE "draft"."author_id" = "member"."id" 3.1080 + AND "initiative"."polling" = "contingent"."polling" 3.1081 + AND "draft"."created" > now() - "contingent"."time_frame" 3.1082 + ) + ( 3.1083 + SELECT count(1) FROM "suggestion" 3.1084 + JOIN "initiative" ON "initiative"."id" = "suggestion"."initiative_id" 3.1085 + WHERE "suggestion"."author_id" = "member"."id" 3.1086 + AND "contingent"."polling" = FALSE 3.1087 + AND "suggestion"."created" > now() - "contingent"."time_frame" 3.1088 + ) 3.1089 + ELSE NULL END AS "text_entry_count", 3.1090 + "contingent"."text_entry_limit", 3.1091 + CASE WHEN "contingent"."initiative_limit" NOTNULL THEN ( 3.1092 + SELECT count(1) FROM "opening_draft" AS "draft" 3.1093 + JOIN "initiative" ON "initiative"."id" = "draft"."initiative_id" 3.1094 + WHERE "draft"."author_id" = "member"."id" 3.1095 + AND "initiative"."polling" = "contingent"."polling" 3.1096 + AND "draft"."created" > now() - "contingent"."time_frame" 3.1097 + ) ELSE NULL END AS "initiative_count", 3.1098 + "contingent"."initiative_limit" 3.1099 + FROM "member" CROSS JOIN "contingent"; 3.1100 + 3.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.'; 3.1102 + 3.1103 +COMMENT ON COLUMN "member_contingent"."text_entry_count" IS 'Only calculated when "text_entry_limit" is not null in the same row'; 3.1104 +COMMENT ON COLUMN "member_contingent"."initiative_count" IS 'Only calculated when "initiative_limit" is not null in the same row'; 3.1105 + 3.1106 +CREATE VIEW "member_contingent_left" AS 3.1107 + SELECT 3.1108 + "member_id", 3.1109 + "polling", 3.1110 + max("text_entry_limit" - "text_entry_count") AS "text_entries_left", 3.1111 + max("initiative_limit" - "initiative_count") AS "initiatives_left" 3.1112 + FROM "member_contingent" GROUP BY "member_id", "polling"; 3.1113 + 3.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.'; 3.1115 + 3.1116 +CREATE VIEW "scheduled_notification_to_send" AS 3.1117 + SELECT * FROM ( 3.1118 + SELECT 3.1119 + "id" AS "recipient_id", 3.1120 + now() - CASE WHEN "notification_dow" ISNULL THEN 3.1121 + ( "notification_sent"::DATE + CASE 3.1122 + WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour" 3.1123 + THEN 0 ELSE 1 END 3.1124 + )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour" 3.1125 + ELSE 3.1126 + ( "notification_sent"::DATE + 3.1127 + ( 7 + "notification_dow" - 3.1128 + EXTRACT(DOW FROM 3.1129 + ( "notification_sent"::DATE + CASE 3.1130 + WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour" 3.1131 + THEN 0 ELSE 1 END 3.1132 + )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour" 3.1133 + )::INTEGER 3.1134 + ) % 7 + 3.1135 + CASE 3.1136 + WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour" 3.1137 + THEN 0 ELSE 1 3.1138 + END 3.1139 + )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour" 3.1140 + END AS "pending" 3.1141 + FROM ( 3.1142 + SELECT 3.1143 + "id", 3.1144 + COALESCE("notification_sent", "activated") AS "notification_sent", 3.1145 + "notification_dow", 3.1146 + "notification_hour" 3.1147 + FROM "member_to_notify" 3.1148 + WHERE "notification_hour" NOTNULL 3.1149 + ) AS "subquery1" 3.1150 + ) AS "subquery2" 3.1151 + WHERE "pending" > '0'::INTERVAL; 3.1152 + 3.1153 +COMMENT ON VIEW "scheduled_notification_to_send" IS 'Set of members where a scheduled notification mail is pending'; 3.1154 + 3.1155 +COMMENT ON COLUMN "scheduled_notification_to_send"."recipient_id" IS '"id" of the member who needs to receive a notification mail'; 3.1156 +COMMENT ON COLUMN "scheduled_notification_to_send"."pending" IS 'Duration for which the notification mail has already been pending'; 3.1157 + 3.1158 +CREATE VIEW "newsletter_to_send" AS 3.1159 + SELECT 3.1160 + "member"."id" AS "recipient_id", 3.1161 + "newsletter"."id" AS "newsletter_id", 3.1162 + "newsletter"."published" 3.1163 + FROM "newsletter" CROSS JOIN "member_eligible_to_be_notified" AS "member" 3.1164 + LEFT JOIN "privilege" ON 3.1165 + "privilege"."member_id" = "member"."id" AND 3.1166 + "privilege"."unit_id" = "newsletter"."unit_id" AND 3.1167 + "privilege"."voting_right" = TRUE 3.1168 + LEFT JOIN "subscription" ON 3.1169 + "subscription"."member_id" = "member"."id" AND 3.1170 + "subscription"."unit_id" = "newsletter"."unit_id" 3.1171 + WHERE "newsletter"."published" <= now() 3.1172 + AND "newsletter"."sent" ISNULL 3.1173 + AND ( 3.1174 + "member"."disable_notifications" = FALSE OR 3.1175 + "newsletter"."include_all_members" = TRUE ) 3.1176 + AND ( 3.1177 + "newsletter"."unit_id" ISNULL OR 3.1178 + "privilege"."member_id" NOTNULL OR 3.1179 + "subscription"."member_id" NOTNULL ); 3.1180 + 3.1181 +COMMENT ON VIEW "newsletter_to_send" IS 'List of "newsletter_id"s for each member that are due to be sent out'; 3.1182 + 3.1183 +COMMENT ON COLUMN "newsletter"."published" IS 'Timestamp when the newsletter was supposed to be sent out (can be used for ordering)'; 3.1184 + 3.1185 +SELECT "copy_current_draft_data" ("id") FROM "initiative"; 3.1186 + 3.1187 +COMMIT; 3.1188 +BEGIN; 3.1189 + 3.1190 +CREATE OR REPLACE VIEW "liquid_feedback_version" AS 3.1191 + SELECT * FROM (VALUES ('4.2.1', 4, 2, 1)) 3.1192 + AS "subquery"("string", "major", "minor", "revision"); 3.1193 + 3.1194 +ALTER TABLE "unit" ADD COLUMN "attr" JSONB NOT NULL DEFAULT '{}' CHECK (jsonb_typeof("attr") = 'object'); 3.1195 +COMMENT ON COLUMN "unit"."attr" IS 'Opaque data structure to store any extended attributes used by frontend or middleware'; 3.1196 + 3.1197 +ALTER TABLE "unit" ADD COLUMN "member_weight" INT4; 3.1198 +COMMENT ON COLUMN "unit"."member_weight" IS 'Sum of active members'' voting weight'; 3.1199 + 3.1200 +ALTER TABLE "snapshot_population" ADD COLUMN "weight" INT4 NOT NULL DEFAULT 1; 3.1201 +ALTER TABLE "snapshot_population" ALTER COLUMN "weight" DROP DEFAULT; 3.1202 + 3.1203 +ALTER TABLE "privilege" ADD COLUMN "weight" INT4 NOT NULL DEFAULT 1 CHECK ("weight" >= 0); 3.1204 +COMMENT ON COLUMN "privilege"."weight" IS 'Voting weight of member in unit'; 3.1205 + 3.1206 +CREATE TABLE "issue_privilege" ( 3.1207 + PRIMARY KEY ("issue_id", "member_id"), 3.1208 + "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 3.1209 + "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 3.1210 + "initiative_right" BOOLEAN, 3.1211 + "voting_right" BOOLEAN, 3.1212 + "polling_right" BOOLEAN, 3.1213 + "weight" INT4 CHECK ("weight" >= 0) ); 3.1214 +CREATE INDEX "issue_privilege_idx" ON "issue_privilege" ("member_id"); 3.1215 +COMMENT ON TABLE "issue_privilege" IS 'Override of "privilege" table for rights of members in certain issues'; 3.1216 + 3.1217 +ALTER TABLE "direct_interest_snapshot" ADD COLUMN "ownweight" INT4 NOT NULL DEFAULT 1; 3.1218 +ALTER TABLE "direct_interest_snapshot" ALTER COLUMN "ownweight" DROP DEFAULT; 3.1219 +COMMENT ON COLUMN "direct_interest_snapshot"."ownweight" IS 'Own voting weight of member, disregading delegations'; 3.1220 +COMMENT ON COLUMN "direct_interest_snapshot"."weight" IS 'Voting weight of member according to own weight and "delegating_interest_snapshot"'; 3.1221 + 3.1222 +ALTER TABLE "delegating_interest_snapshot" ADD COLUMN "ownweight" INT4 NOT NULL DEFAULT 1; 3.1223 +ALTER TABLE "delegating_interest_snapshot" ALTER COLUMN "ownweight" DROP DEFAULT; 3.1224 +COMMENT ON COLUMN "delegating_interest_snapshot"."ownweight" IS 'Own voting weight of member, disregading delegations'; 3.1225 +COMMENT ON COLUMN "delegating_interest_snapshot"."weight" IS 'Intermediate voting weight considering incoming delegations'; 3.1226 + 3.1227 +ALTER TABLE "direct_voter" ADD COLUMN "ownweight" INT4 DEFAULT 1; 3.1228 +ALTER TABLE "direct_voter" ALTER COLUMN "ownweight" DROP DEFAULT; 3.1229 +COMMENT ON COLUMN "direct_voter"."ownweight" IS 'Own voting weight of member, disregarding delegations'; 3.1230 +COMMENT ON COLUMN "direct_voter"."weight" IS 'Voting weight of member according to own weight and "delegating_interest_snapshot"'; 3.1231 + 3.1232 +ALTER TABLE "delegating_voter" ADD COLUMN "ownweight" INT4 NOT NULL DEFAULT 1; 3.1233 +ALTER TABLE "delegating_voter" ALTER COLUMN "ownweight" DROP DEFAULT; 3.1234 +COMMENT ON COLUMN "delegating_voter"."ownweight" IS 'Own voting weight of member, disregarding delegations'; 3.1235 +COMMENT ON COLUMN "delegating_voter"."weight" IS 'Intermediate voting weight considering incoming delegations'; 3.1236 + 3.1237 +ALTER TABLE "posting" ADD FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id"); 3.1238 + 3.1239 +DROP VIEW "issue_delegation"; 3.1240 +CREATE VIEW "issue_delegation" AS 3.1241 + SELECT DISTINCT ON ("issue"."id", "delegation"."truster_id") 3.1242 + "issue"."id" AS "issue_id", 3.1243 + "delegation"."id", 3.1244 + "delegation"."truster_id", 3.1245 + "delegation"."trustee_id", 3.1246 + COALESCE("issue_privilege"."weight", "privilege"."weight") AS "weight", 3.1247 + "delegation"."scope" 3.1248 + FROM "issue" 3.1249 + JOIN "area" 3.1250 + ON "area"."id" = "issue"."area_id" 3.1251 + JOIN "delegation" 3.1252 + ON "delegation"."unit_id" = "area"."unit_id" 3.1253 + OR "delegation"."area_id" = "area"."id" 3.1254 + OR "delegation"."issue_id" = "issue"."id" 3.1255 + JOIN "member" 3.1256 + ON "delegation"."truster_id" = "member"."id" 3.1257 + LEFT JOIN "privilege" 3.1258 + ON "area"."unit_id" = "privilege"."unit_id" 3.1259 + AND "delegation"."truster_id" = "privilege"."member_id" 3.1260 + LEFT JOIN "issue_privilege" 3.1261 + ON "issue"."id" = "issue_privilege"."issue_id" 3.1262 + AND "delegation"."truster_id" = "issue_privilege"."member_id" 3.1263 + WHERE "member"."active" 3.1264 + AND COALESCE("issue_privilege"."voting_right", "privilege"."voting_right") 3.1265 + ORDER BY 3.1266 + "issue"."id", 3.1267 + "delegation"."truster_id", 3.1268 + "delegation"."scope" DESC; 3.1269 +COMMENT ON VIEW "issue_delegation" IS 'Issue delegations where trusters are active and have voting right'; 3.1270 + 3.1271 +CREATE OR REPLACE VIEW "unit_member" AS 3.1272 + SELECT 3.1273 + "privilege"."unit_id" AS "unit_id", 3.1274 + "member"."id" AS "member_id", 3.1275 + "privilege"."weight" 3.1276 + FROM "privilege" JOIN "member" ON "member"."id" = "privilege"."member_id" 3.1277 + WHERE "privilege"."voting_right" AND "member"."active"; 3.1278 + 3.1279 +CREATE OR REPLACE VIEW "unit_member_count" AS 3.1280 + SELECT 3.1281 + "unit"."id" AS "unit_id", 3.1282 + count("unit_member"."member_id") AS "member_count", 3.1283 + sum("unit_member"."weight") AS "member_weight" 3.1284 + FROM "unit" LEFT JOIN "unit_member" 3.1285 + ON "unit"."id" = "unit_member"."unit_id" 3.1286 + GROUP BY "unit"."id"; 3.1287 + 3.1288 +CREATE OR REPLACE VIEW "event_for_notification" AS 3.1289 + SELECT 3.1290 + "member"."id" AS "recipient_id", 3.1291 + "event".* 3.1292 + FROM "member" CROSS JOIN "event" 3.1293 + JOIN "issue" ON "issue"."id" = "event"."issue_id" 3.1294 + JOIN "area" ON "area"."id" = "issue"."area_id" 3.1295 + LEFT JOIN "privilege" ON 3.1296 + "privilege"."member_id" = "member"."id" AND 3.1297 + "privilege"."unit_id" = "area"."unit_id" 3.1298 + LEFT JOIN "issue_privilege" ON 3.1299 + "issue_privilege"."member_id" = "member"."id" AND 3.1300 + "issue_privilege"."issue_id" = "event"."issue_id" 3.1301 + LEFT JOIN "subscription" ON 3.1302 + "subscription"."member_id" = "member"."id" AND 3.1303 + "subscription"."unit_id" = "area"."unit_id" 3.1304 + LEFT JOIN "ignored_area" ON 3.1305 + "ignored_area"."member_id" = "member"."id" AND 3.1306 + "ignored_area"."area_id" = "issue"."area_id" 3.1307 + LEFT JOIN "interest" ON 3.1308 + "interest"."member_id" = "member"."id" AND 3.1309 + "interest"."issue_id" = "event"."issue_id" 3.1310 + LEFT JOIN "supporter" ON 3.1311 + "supporter"."member_id" = "member"."id" AND 3.1312 + "supporter"."initiative_id" = "event"."initiative_id" 3.1313 + WHERE ( 3.1314 + COALESCE("issue_privilege"."voting_right", "privilege"."voting_right") OR 3.1315 + "subscription"."member_id" NOTNULL 3.1316 + ) AND ("ignored_area"."member_id" ISNULL OR "interest"."member_id" NOTNULL) 3.1317 + AND ( 3.1318 + "event"."event" = 'issue_state_changed'::"event_type" OR 3.1319 + ( "event"."event" = 'initiative_revoked'::"event_type" AND 3.1320 + "supporter"."member_id" NOTNULL ) ); 3.1321 + 3.1322 +CREATE OR REPLACE FUNCTION "featured_initiative" 3.1323 + ( "recipient_id_p" "member"."id"%TYPE, 3.1324 + "area_id_p" "area"."id"%TYPE ) 3.1325 + RETURNS SETOF "initiative"."id"%TYPE 3.1326 + LANGUAGE 'plpgsql' STABLE AS $$ 3.1327 + DECLARE 3.1328 + "counter_v" "member"."notification_counter"%TYPE; 3.1329 + "sample_size_v" "member"."notification_sample_size"%TYPE; 3.1330 + "initiative_id_ary" INT4[]; --"initiative"."id"%TYPE[] 3.1331 + "match_v" BOOLEAN; 3.1332 + "member_id_v" "member"."id"%TYPE; 3.1333 + "seed_v" TEXT; 3.1334 + "initiative_id_v" "initiative"."id"%TYPE; 3.1335 + BEGIN 3.1336 + SELECT "notification_counter", "notification_sample_size" 3.1337 + INTO "counter_v", "sample_size_v" 3.1338 + FROM "member" WHERE "id" = "recipient_id_p"; 3.1339 + IF COALESCE("sample_size_v" <= 0, TRUE) THEN 3.1340 + RETURN; 3.1341 + END IF; 3.1342 + "initiative_id_ary" := '{}'; 3.1343 + LOOP 3.1344 + "match_v" := FALSE; 3.1345 + FOR "member_id_v", "seed_v" IN 3.1346 + SELECT * FROM ( 3.1347 + SELECT DISTINCT 3.1348 + "supporter"."member_id", 3.1349 + md5( 3.1350 + "recipient_id_p" || '-' || 3.1351 + "counter_v" || '-' || 3.1352 + "area_id_p" || '-' || 3.1353 + "supporter"."member_id" 3.1354 + ) AS "seed" 3.1355 + FROM "supporter" 3.1356 + JOIN "initiative" ON "initiative"."id" = "supporter"."initiative_id" 3.1357 + JOIN "issue" ON "issue"."id" = "initiative"."issue_id" 3.1358 + WHERE "supporter"."member_id" != "recipient_id_p" 3.1359 + AND "issue"."area_id" = "area_id_p" 3.1360 + AND "issue"."state" IN ('admission', 'discussion', 'verification') 3.1361 + ) AS "subquery" 3.1362 + ORDER BY "seed" 3.1363 + LOOP 3.1364 + SELECT "initiative"."id" INTO "initiative_id_v" 3.1365 + FROM "initiative" 3.1366 + JOIN "issue" ON "issue"."id" = "initiative"."issue_id" 3.1367 + JOIN "area" ON "area"."id" = "issue"."area_id" 3.1368 + JOIN "supporter" ON "supporter"."initiative_id" = "initiative"."id" 3.1369 + LEFT JOIN "supporter" AS "self_support" ON 3.1370 + "self_support"."initiative_id" = "initiative"."id" AND 3.1371 + "self_support"."member_id" = "recipient_id_p" 3.1372 + LEFT JOIN "privilege" ON 3.1373 + "privilege"."member_id" = "recipient_id_p" AND 3.1374 + "privilege"."unit_id" = "area"."unit_id" 3.1375 + LEFT JOIN "issue_privilege" ON 3.1376 + "issue_privilege"."member_id" = "recipient_id_p" AND 3.1377 + "issue_privilege"."issue_id" = "initiative"."issue_id" 3.1378 + LEFT JOIN "subscription" ON 3.1379 + "subscription"."member_id" = "recipient_id_p" AND 3.1380 + "subscription"."unit_id" = "area"."unit_id" 3.1381 + LEFT JOIN "ignored_initiative" ON 3.1382 + "ignored_initiative"."member_id" = "recipient_id_p" AND 3.1383 + "ignored_initiative"."initiative_id" = "initiative"."id" 3.1384 + WHERE "supporter"."member_id" = "member_id_v" 3.1385 + AND "issue"."area_id" = "area_id_p" 3.1386 + AND "issue"."state" IN ('admission', 'discussion', 'verification') 3.1387 + AND "initiative"."revoked" ISNULL 3.1388 + AND "self_support"."member_id" ISNULL 3.1389 + AND NOT "initiative_id_ary" @> ARRAY["initiative"."id"] 3.1390 + AND ( 3.1391 + COALESCE( 3.1392 + "issue_privilege"."voting_right", "privilege"."voting_right" 3.1393 + ) OR "subscription"."member_id" NOTNULL ) 3.1394 + AND "ignored_initiative"."member_id" ISNULL 3.1395 + AND NOT EXISTS ( 3.1396 + SELECT NULL FROM "draft" 3.1397 + JOIN "ignored_member" ON 3.1398 + "ignored_member"."member_id" = "recipient_id_p" AND 3.1399 + "ignored_member"."other_member_id" = "draft"."author_id" 3.1400 + WHERE "draft"."initiative_id" = "initiative"."id" 3.1401 + ) 3.1402 + ORDER BY md5("seed_v" || '-' || "initiative"."id") 3.1403 + LIMIT 1; 3.1404 + IF FOUND THEN 3.1405 + "match_v" := TRUE; 3.1406 + RETURN NEXT "initiative_id_v"; 3.1407 + IF array_length("initiative_id_ary", 1) + 1 >= "sample_size_v" THEN 3.1408 + RETURN; 3.1409 + END IF; 3.1410 + "initiative_id_ary" := "initiative_id_ary" || "initiative_id_v"; 3.1411 + END IF; 3.1412 + END LOOP; 3.1413 + EXIT WHEN NOT "match_v"; 3.1414 + END LOOP; 3.1415 + RETURN; 3.1416 + END; 3.1417 + $$; 3.1418 + 3.1419 +CREATE OR REPLACE FUNCTION "delegation_chain" 3.1420 + ( "member_id_p" "member"."id"%TYPE, 3.1421 + "unit_id_p" "unit"."id"%TYPE, 3.1422 + "area_id_p" "area"."id"%TYPE, 3.1423 + "issue_id_p" "issue"."id"%TYPE, 3.1424 + "simulate_trustee_id_p" "member"."id"%TYPE DEFAULT NULL, 3.1425 + "simulate_default_p" BOOLEAN DEFAULT FALSE ) 3.1426 + RETURNS SETOF "delegation_chain_row" 3.1427 + LANGUAGE 'plpgsql' STABLE AS $$ 3.1428 + DECLARE 3.1429 + "scope_v" "delegation_scope"; 3.1430 + "unit_id_v" "unit"."id"%TYPE; 3.1431 + "area_id_v" "area"."id"%TYPE; 3.1432 + "issue_row" "issue"%ROWTYPE; 3.1433 + "visited_member_ids" INT4[]; -- "member"."id"%TYPE[] 3.1434 + "loop_member_id_v" "member"."id"%TYPE; 3.1435 + "output_row" "delegation_chain_row"; 3.1436 + "output_rows" "delegation_chain_row"[]; 3.1437 + "simulate_v" BOOLEAN; 3.1438 + "simulate_here_v" BOOLEAN; 3.1439 + "delegation_row" "delegation"%ROWTYPE; 3.1440 + "row_count" INT4; 3.1441 + "i" INT4; 3.1442 + "loop_v" BOOLEAN; 3.1443 + BEGIN 3.1444 + IF "simulate_trustee_id_p" NOTNULL AND "simulate_default_p" THEN 3.1445 + RAISE EXCEPTION 'Both "simulate_trustee_id_p" is set, and "simulate_default_p" is true'; 3.1446 + END IF; 3.1447 + IF "simulate_trustee_id_p" NOTNULL OR "simulate_default_p" THEN 3.1448 + "simulate_v" := TRUE; 3.1449 + ELSE 3.1450 + "simulate_v" := FALSE; 3.1451 + END IF; 3.1452 + IF 3.1453 + "unit_id_p" NOTNULL AND 3.1454 + "area_id_p" ISNULL AND 3.1455 + "issue_id_p" ISNULL 3.1456 + THEN 3.1457 + "scope_v" := 'unit'; 3.1458 + "unit_id_v" := "unit_id_p"; 3.1459 + ELSIF 3.1460 + "unit_id_p" ISNULL AND 3.1461 + "area_id_p" NOTNULL AND 3.1462 + "issue_id_p" ISNULL 3.1463 + THEN 3.1464 + "scope_v" := 'area'; 3.1465 + "area_id_v" := "area_id_p"; 3.1466 + SELECT "unit_id" INTO "unit_id_v" 3.1467 + FROM "area" WHERE "id" = "area_id_v"; 3.1468 + ELSIF 3.1469 + "unit_id_p" ISNULL AND 3.1470 + "area_id_p" ISNULL AND 3.1471 + "issue_id_p" NOTNULL 3.1472 + THEN 3.1473 + SELECT INTO "issue_row" * FROM "issue" WHERE "id" = "issue_id_p"; 3.1474 + IF "issue_row"."id" ISNULL THEN 3.1475 + RETURN; 3.1476 + END IF; 3.1477 + IF "issue_row"."closed" NOTNULL THEN 3.1478 + IF "simulate_v" THEN 3.1479 + RAISE EXCEPTION 'Tried to simulate delegation chain for closed issue.'; 3.1480 + END IF; 3.1481 + FOR "output_row" IN 3.1482 + SELECT * FROM 3.1483 + "delegation_chain_for_closed_issue"("member_id_p", "issue_id_p") 3.1484 + LOOP 3.1485 + RETURN NEXT "output_row"; 3.1486 + END LOOP; 3.1487 + RETURN; 3.1488 + END IF; 3.1489 + "scope_v" := 'issue'; 3.1490 + SELECT "area_id" INTO "area_id_v" 3.1491 + FROM "issue" WHERE "id" = "issue_id_p"; 3.1492 + SELECT "unit_id" INTO "unit_id_v" 3.1493 + FROM "area" WHERE "id" = "area_id_v"; 3.1494 + ELSE 3.1495 + RAISE EXCEPTION 'Exactly one of unit_id_p, area_id_p, or issue_id_p must be NOTNULL.'; 3.1496 + END IF; 3.1497 + "visited_member_ids" := '{}'; 3.1498 + "loop_member_id_v" := NULL; 3.1499 + "output_rows" := '{}'; 3.1500 + "output_row"."index" := 0; 3.1501 + "output_row"."member_id" := "member_id_p"; 3.1502 + "output_row"."member_valid" := TRUE; 3.1503 + "output_row"."participation" := FALSE; 3.1504 + "output_row"."overridden" := FALSE; 3.1505 + "output_row"."disabled_out" := FALSE; 3.1506 + "output_row"."scope_out" := NULL; 3.1507 + LOOP 3.1508 + IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN 3.1509 + "loop_member_id_v" := "output_row"."member_id"; 3.1510 + ELSE 3.1511 + "visited_member_ids" := 3.1512 + "visited_member_ids" || "output_row"."member_id"; 3.1513 + END IF; 3.1514 + IF "output_row"."participation" ISNULL THEN 3.1515 + "output_row"."overridden" := NULL; 3.1516 + ELSIF "output_row"."participation" THEN 3.1517 + "output_row"."overridden" := TRUE; 3.1518 + END IF; 3.1519 + "output_row"."scope_in" := "output_row"."scope_out"; 3.1520 + "output_row"."member_valid" := EXISTS ( 3.1521 + SELECT NULL FROM "member" 3.1522 + LEFT JOIN "privilege" 3.1523 + ON "privilege"."member_id" = "member"."id" 3.1524 + AND "privilege"."unit_id" = "unit_id_v" 3.1525 + LEFT JOIN "issue_privilege" 3.1526 + ON "issue_privilege"."member_id" = "member"."id" 3.1527 + AND "issue_privilege"."issue_id" = "issue_id_p" 3.1528 + WHERE "id" = "output_row"."member_id" 3.1529 + AND "member"."active" 3.1530 + AND COALESCE( 3.1531 + "issue_privilege"."voting_right", "privilege"."voting_right") 3.1532 + ); 3.1533 + "simulate_here_v" := ( 3.1534 + "simulate_v" AND 3.1535 + "output_row"."member_id" = "member_id_p" 3.1536 + ); 3.1537 + "delegation_row" := ROW(NULL); 3.1538 + IF "output_row"."member_valid" OR "simulate_here_v" THEN 3.1539 + IF "scope_v" = 'unit' THEN 3.1540 + IF NOT "simulate_here_v" THEN 3.1541 + SELECT * INTO "delegation_row" FROM "delegation" 3.1542 + WHERE "truster_id" = "output_row"."member_id" 3.1543 + AND "unit_id" = "unit_id_v"; 3.1544 + END IF; 3.1545 + ELSIF "scope_v" = 'area' THEN 3.1546 + IF "simulate_here_v" THEN 3.1547 + IF "simulate_trustee_id_p" ISNULL THEN 3.1548 + SELECT * INTO "delegation_row" FROM "delegation" 3.1549 + WHERE "truster_id" = "output_row"."member_id" 3.1550 + AND "unit_id" = "unit_id_v"; 3.1551 + END IF; 3.1552 + ELSE 3.1553 + SELECT * INTO "delegation_row" FROM "delegation" 3.1554 + WHERE "truster_id" = "output_row"."member_id" 3.1555 + AND ( 3.1556 + "unit_id" = "unit_id_v" OR 3.1557 + "area_id" = "area_id_v" 3.1558 + ) 3.1559 + ORDER BY "scope" DESC; 3.1560 + END IF; 3.1561 + ELSIF "scope_v" = 'issue' THEN 3.1562 + IF "issue_row"."fully_frozen" ISNULL THEN 3.1563 + "output_row"."participation" := EXISTS ( 3.1564 + SELECT NULL FROM "interest" 3.1565 + WHERE "issue_id" = "issue_id_p" 3.1566 + AND "member_id" = "output_row"."member_id" 3.1567 + ); 3.1568 + ELSE 3.1569 + IF "output_row"."member_id" = "member_id_p" THEN 3.1570 + "output_row"."participation" := EXISTS ( 3.1571 + SELECT NULL FROM "direct_voter" 3.1572 + WHERE "issue_id" = "issue_id_p" 3.1573 + AND "member_id" = "output_row"."member_id" 3.1574 + ); 3.1575 + ELSE 3.1576 + "output_row"."participation" := NULL; 3.1577 + END IF; 3.1578 + END IF; 3.1579 + IF "simulate_here_v" THEN 3.1580 + IF "simulate_trustee_id_p" ISNULL THEN 3.1581 + SELECT * INTO "delegation_row" FROM "delegation" 3.1582 + WHERE "truster_id" = "output_row"."member_id" 3.1583 + AND ( 3.1584 + "unit_id" = "unit_id_v" OR 3.1585 + "area_id" = "area_id_v" 3.1586 + ) 3.1587 + ORDER BY "scope" DESC; 3.1588 + END IF; 3.1589 + ELSE 3.1590 + SELECT * INTO "delegation_row" FROM "delegation" 3.1591 + WHERE "truster_id" = "output_row"."member_id" 3.1592 + AND ( 3.1593 + "unit_id" = "unit_id_v" OR 3.1594 + "area_id" = "area_id_v" OR 3.1595 + "issue_id" = "issue_id_p" 3.1596 + ) 3.1597 + ORDER BY "scope" DESC; 3.1598 + END IF; 3.1599 + END IF; 3.1600 + ELSE 3.1601 + "output_row"."participation" := FALSE; 3.1602 + END IF; 3.1603 + IF "simulate_here_v" AND "simulate_trustee_id_p" NOTNULL THEN 3.1604 + "output_row"."scope_out" := "scope_v"; 3.1605 + "output_rows" := "output_rows" || "output_row"; 3.1606 + "output_row"."member_id" := "simulate_trustee_id_p"; 3.1607 + ELSIF "delegation_row"."trustee_id" NOTNULL THEN 3.1608 + "output_row"."scope_out" := "delegation_row"."scope"; 3.1609 + "output_rows" := "output_rows" || "output_row"; 3.1610 + "output_row"."member_id" := "delegation_row"."trustee_id"; 3.1611 + ELSIF "delegation_row"."scope" NOTNULL THEN 3.1612 + "output_row"."scope_out" := "delegation_row"."scope"; 3.1613 + "output_row"."disabled_out" := TRUE; 3.1614 + "output_rows" := "output_rows" || "output_row"; 3.1615 + EXIT; 3.1616 + ELSE 3.1617 + "output_row"."scope_out" := NULL; 3.1618 + "output_rows" := "output_rows" || "output_row"; 3.1619 + EXIT; 3.1620 + END IF; 3.1621 + EXIT WHEN "loop_member_id_v" NOTNULL; 3.1622 + "output_row"."index" := "output_row"."index" + 1; 3.1623 + END LOOP; 3.1624 + "row_count" := array_upper("output_rows", 1); 3.1625 + "i" := 1; 3.1626 + "loop_v" := FALSE; 3.1627 + LOOP 3.1628 + "output_row" := "output_rows"["i"]; 3.1629 + EXIT WHEN "output_row" ISNULL; -- NOTE: ISNULL and NOT ... NOTNULL produce different results! 3.1630 + IF "loop_v" THEN 3.1631 + IF "i" + 1 = "row_count" THEN 3.1632 + "output_row"."loop" := 'last'; 3.1633 + ELSIF "i" = "row_count" THEN 3.1634 + "output_row"."loop" := 'repetition'; 3.1635 + ELSE 3.1636 + "output_row"."loop" := 'intermediate'; 3.1637 + END IF; 3.1638 + ELSIF "output_row"."member_id" = "loop_member_id_v" THEN 3.1639 + "output_row"."loop" := 'first'; 3.1640 + "loop_v" := TRUE; 3.1641 + END IF; 3.1642 + IF "scope_v" = 'unit' THEN 3.1643 + "output_row"."participation" := NULL; 3.1644 + END IF; 3.1645 + RETURN NEXT "output_row"; 3.1646 + "i" := "i" + 1; 3.1647 + END LOOP; 3.1648 + RETURN; 3.1649 + END; 3.1650 + $$; 3.1651 + 3.1652 +CREATE OR REPLACE FUNCTION "calculate_member_counts"() 3.1653 + RETURNS VOID 3.1654 + LANGUAGE 'plpgsql' VOLATILE AS $$ 3.1655 + BEGIN 3.1656 + PERFORM "require_transaction_isolation"(); 3.1657 + DELETE FROM "member_count"; 3.1658 + INSERT INTO "member_count" ("total_count") 3.1659 + SELECT "total_count" FROM "member_count_view"; 3.1660 + UPDATE "unit" SET 3.1661 + "member_count" = "view"."member_count", 3.1662 + "member_weight" = "view"."member_weight" 3.1663 + FROM "unit_member_count" AS "view" 3.1664 + WHERE "view"."unit_id" = "unit"."id"; 3.1665 + RETURN; 3.1666 + END; 3.1667 + $$; 3.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"'; 3.1669 + 3.1670 +CREATE OR REPLACE FUNCTION "weight_of_added_delegations_for_snapshot" 3.1671 + ( "snapshot_id_p" "snapshot"."id"%TYPE, 3.1672 + "issue_id_p" "issue"."id"%TYPE, 3.1673 + "member_id_p" "member"."id"%TYPE, 3.1674 + "delegate_member_ids_p" "delegating_interest_snapshot"."delegate_member_ids"%TYPE ) 3.1675 + RETURNS "direct_interest_snapshot"."weight"%TYPE 3.1676 + LANGUAGE 'plpgsql' VOLATILE AS $$ 3.1677 + DECLARE 3.1678 + "issue_delegation_row" "issue_delegation"%ROWTYPE; 3.1679 + "delegate_member_ids_v" "delegating_interest_snapshot"."delegate_member_ids"%TYPE; 3.1680 + "weight_v" INT4; 3.1681 + "sub_weight_v" INT4; 3.1682 + BEGIN 3.1683 + PERFORM "require_transaction_isolation"(); 3.1684 + "weight_v" := 0; 3.1685 + FOR "issue_delegation_row" IN 3.1686 + SELECT * FROM "issue_delegation" 3.1687 + WHERE "trustee_id" = "member_id_p" 3.1688 + AND "issue_id" = "issue_id_p" 3.1689 + LOOP 3.1690 + IF NOT EXISTS ( 3.1691 + SELECT NULL FROM "direct_interest_snapshot" 3.1692 + WHERE "snapshot_id" = "snapshot_id_p" 3.1693 + AND "issue_id" = "issue_id_p" 3.1694 + AND "member_id" = "issue_delegation_row"."truster_id" 3.1695 + ) AND NOT EXISTS ( 3.1696 + SELECT NULL FROM "delegating_interest_snapshot" 3.1697 + WHERE "snapshot_id" = "snapshot_id_p" 3.1698 + AND "issue_id" = "issue_id_p" 3.1699 + AND "member_id" = "issue_delegation_row"."truster_id" 3.1700 + ) THEN 3.1701 + "delegate_member_ids_v" := 3.1702 + "member_id_p" || "delegate_member_ids_p"; 3.1703 + INSERT INTO "delegating_interest_snapshot" ( 3.1704 + "snapshot_id", 3.1705 + "issue_id", 3.1706 + "member_id", 3.1707 + "ownweight", 3.1708 + "scope", 3.1709 + "delegate_member_ids" 3.1710 + ) VALUES ( 3.1711 + "snapshot_id_p", 3.1712 + "issue_id_p", 3.1713 + "issue_delegation_row"."truster_id", 3.1714 + "issue_delegation_row"."weight", 3.1715 + "issue_delegation_row"."scope", 3.1716 + "delegate_member_ids_v" 3.1717 + ); 3.1718 + "sub_weight_v" := "issue_delegation_row"."weight" + 3.1719 + "weight_of_added_delegations_for_snapshot"( 3.1720 + "snapshot_id_p", 3.1721 + "issue_id_p", 3.1722 + "issue_delegation_row"."truster_id", 3.1723 + "delegate_member_ids_v" 3.1724 + ); 3.1725 + UPDATE "delegating_interest_snapshot" 3.1726 + SET "weight" = "sub_weight_v" 3.1727 + WHERE "snapshot_id" = "snapshot_id_p" 3.1728 + AND "issue_id" = "issue_id_p" 3.1729 + AND "member_id" = "issue_delegation_row"."truster_id"; 3.1730 + "weight_v" := "weight_v" + "sub_weight_v"; 3.1731 + END IF; 3.1732 + END LOOP; 3.1733 + RETURN "weight_v"; 3.1734 + END; 3.1735 + $$; 3.1736 + 3.1737 +CREATE OR REPLACE FUNCTION "take_snapshot" 3.1738 + ( "issue_id_p" "issue"."id"%TYPE, 3.1739 + "area_id_p" "area"."id"%TYPE = NULL ) 3.1740 + RETURNS "snapshot"."id"%TYPE 3.1741 + LANGUAGE 'plpgsql' VOLATILE AS $$ 3.1742 + DECLARE 3.1743 + "area_id_v" "area"."id"%TYPE; 3.1744 + "unit_id_v" "unit"."id"%TYPE; 3.1745 + "snapshot_id_v" "snapshot"."id"%TYPE; 3.1746 + "issue_id_v" "issue"."id"%TYPE; 3.1747 + "member_id_v" "member"."id"%TYPE; 3.1748 + BEGIN 3.1749 + IF "issue_id_p" NOTNULL AND "area_id_p" NOTNULL THEN 3.1750 + RAISE EXCEPTION 'One of "issue_id_p" and "area_id_p" must be NULL'; 3.1751 + END IF; 3.1752 + PERFORM "require_transaction_isolation"(); 3.1753 + IF "issue_id_p" ISNULL THEN 3.1754 + "area_id_v" := "area_id_p"; 3.1755 + ELSE 3.1756 + SELECT "area_id" INTO "area_id_v" 3.1757 + FROM "issue" WHERE "id" = "issue_id_p"; 3.1758 + END IF; 3.1759 + SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v"; 3.1760 + INSERT INTO "snapshot" ("area_id", "issue_id") 3.1761 + VALUES ("area_id_v", "issue_id_p") 3.1762 + RETURNING "id" INTO "snapshot_id_v"; 3.1763 + INSERT INTO "snapshot_population" ("snapshot_id", "member_id", "weight") 3.1764 + SELECT 3.1765 + "snapshot_id_v", 3.1766 + "member"."id", 3.1767 + COALESCE("issue_privilege"."weight", "privilege"."weight") 3.1768 + FROM "member" 3.1769 + LEFT JOIN "privilege" 3.1770 + ON "privilege"."unit_id" = "unit_id_v" 3.1771 + AND "privilege"."member_id" = "member"."id" 3.1772 + LEFT JOIN "issue_privilege" 3.1773 + ON "issue_privilege"."issue_id" = "issue_id_p" 3.1774 + AND "issue_privilege"."member_id" = "member"."id" 3.1775 + WHERE "member"."active" AND COALESCE( 3.1776 + "issue_privilege"."voting_right", "privilege"."voting_right"); 3.1777 + UPDATE "snapshot" SET 3.1778 + "population" = ( 3.1779 + SELECT sum("weight") FROM "snapshot_population" 3.1780 + WHERE "snapshot_id" = "snapshot_id_v" 3.1781 + ) WHERE "id" = "snapshot_id_v"; 3.1782 + FOR "issue_id_v" IN 3.1783 + SELECT "id" FROM "issue" 3.1784 + WHERE CASE WHEN "issue_id_p" ISNULL THEN 3.1785 + "area_id" = "area_id_p" AND 3.1786 + "state" = 'admission' 3.1787 + ELSE 3.1788 + "id" = "issue_id_p" 3.1789 + END 3.1790 + LOOP 3.1791 + INSERT INTO "snapshot_issue" ("snapshot_id", "issue_id") 3.1792 + VALUES ("snapshot_id_v", "issue_id_v"); 3.1793 + INSERT INTO "direct_interest_snapshot" 3.1794 + ("snapshot_id", "issue_id", "member_id", "ownweight") 3.1795 + SELECT 3.1796 + "snapshot_id_v" AS "snapshot_id", 3.1797 + "issue_id_v" AS "issue_id", 3.1798 + "member"."id" AS "member_id", 3.1799 + COALESCE( 3.1800 + "issue_privilege"."weight", "privilege"."weight" 3.1801 + ) AS "ownweight" 3.1802 + FROM "issue" 3.1803 + JOIN "area" ON "issue"."area_id" = "area"."id" 3.1804 + JOIN "interest" ON "issue"."id" = "interest"."issue_id" 3.1805 + JOIN "member" ON "interest"."member_id" = "member"."id" 3.1806 + LEFT JOIN "privilege" 3.1807 + ON "privilege"."unit_id" = "area"."unit_id" 3.1808 + AND "privilege"."member_id" = "member"."id" 3.1809 + LEFT JOIN "issue_privilege" 3.1810 + ON "issue_privilege"."issue_id" = "issue_id_v" 3.1811 + AND "issue_privilege"."member_id" = "member"."id" 3.1812 + WHERE "issue"."id" = "issue_id_v" 3.1813 + AND "member"."active" AND COALESCE( 3.1814 + "issue_privilege"."voting_right", "privilege"."voting_right"); 3.1815 + FOR "member_id_v" IN 3.1816 + SELECT "member_id" FROM "direct_interest_snapshot" 3.1817 + WHERE "snapshot_id" = "snapshot_id_v" 3.1818 + AND "issue_id" = "issue_id_v" 3.1819 + LOOP 3.1820 + UPDATE "direct_interest_snapshot" SET 3.1821 + "weight" = "ownweight" + 3.1822 + "weight_of_added_delegations_for_snapshot"( 3.1823 + "snapshot_id_v", 3.1824 + "issue_id_v", 3.1825 + "member_id_v", 3.1826 + '{}' 3.1827 + ) 3.1828 + WHERE "snapshot_id" = "snapshot_id_v" 3.1829 + AND "issue_id" = "issue_id_v" 3.1830 + AND "member_id" = "member_id_v"; 3.1831 + END LOOP; 3.1832 + INSERT INTO "direct_supporter_snapshot" 3.1833 + ( "snapshot_id", "issue_id", "initiative_id", "member_id", 3.1834 + "draft_id", "informed", "satisfied" ) 3.1835 + SELECT 3.1836 + "snapshot_id_v" AS "snapshot_id", 3.1837 + "issue_id_v" AS "issue_id", 3.1838 + "initiative"."id" AS "initiative_id", 3.1839 + "supporter"."member_id" AS "member_id", 3.1840 + "supporter"."draft_id" AS "draft_id", 3.1841 + "supporter"."draft_id" = "current_draft"."id" AS "informed", 3.1842 + NOT EXISTS ( 3.1843 + SELECT NULL FROM "critical_opinion" 3.1844 + WHERE "initiative_id" = "initiative"."id" 3.1845 + AND "member_id" = "supporter"."member_id" 3.1846 + ) AS "satisfied" 3.1847 + FROM "initiative" 3.1848 + JOIN "supporter" 3.1849 + ON "supporter"."initiative_id" = "initiative"."id" 3.1850 + JOIN "current_draft" 3.1851 + ON "initiative"."id" = "current_draft"."initiative_id" 3.1852 + JOIN "direct_interest_snapshot" 3.1853 + ON "snapshot_id_v" = "direct_interest_snapshot"."snapshot_id" 3.1854 + AND "supporter"."member_id" = "direct_interest_snapshot"."member_id" 3.1855 + AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id" 3.1856 + WHERE "initiative"."issue_id" = "issue_id_v"; 3.1857 + DELETE FROM "temporary_suggestion_counts"; 3.1858 + INSERT INTO "temporary_suggestion_counts" 3.1859 + ( "id", 3.1860 + "minus2_unfulfilled_count", "minus2_fulfilled_count", 3.1861 + "minus1_unfulfilled_count", "minus1_fulfilled_count", 3.1862 + "plus1_unfulfilled_count", "plus1_fulfilled_count", 3.1863 + "plus2_unfulfilled_count", "plus2_fulfilled_count" ) 3.1864 + SELECT 3.1865 + "suggestion"."id", 3.1866 + ( SELECT coalesce(sum("di"."weight"), 0) 3.1867 + FROM "opinion" JOIN "direct_interest_snapshot" AS "di" 3.1868 + ON "di"."snapshot_id" = "snapshot_id_v" 3.1869 + AND "di"."issue_id" = "issue_id_v" 3.1870 + AND "di"."member_id" = "opinion"."member_id" 3.1871 + WHERE "opinion"."suggestion_id" = "suggestion"."id" 3.1872 + AND "opinion"."degree" = -2 3.1873 + AND "opinion"."fulfilled" = FALSE 3.1874 + ) AS "minus2_unfulfilled_count", 3.1875 + ( SELECT coalesce(sum("di"."weight"), 0) 3.1876 + FROM "opinion" JOIN "direct_interest_snapshot" AS "di" 3.1877 + ON "di"."snapshot_id" = "snapshot_id_v" 3.1878 + AND "di"."issue_id" = "issue_id_v" 3.1879 + AND "di"."member_id" = "opinion"."member_id" 3.1880 + WHERE "opinion"."suggestion_id" = "suggestion"."id" 3.1881 + AND "opinion"."degree" = -2 3.1882 + AND "opinion"."fulfilled" = TRUE 3.1883 + ) AS "minus2_fulfilled_count", 3.1884 + ( SELECT coalesce(sum("di"."weight"), 0) 3.1885 + FROM "opinion" JOIN "direct_interest_snapshot" AS "di" 3.1886 + ON "di"."snapshot_id" = "snapshot_id_v" 3.1887 + AND "di"."issue_id" = "issue_id_v" 3.1888 + AND "di"."member_id" = "opinion"."member_id" 3.1889 + WHERE "opinion"."suggestion_id" = "suggestion"."id" 3.1890 + AND "opinion"."degree" = -1 3.1891 + AND "opinion"."fulfilled" = FALSE 3.1892 + ) AS "minus1_unfulfilled_count", 3.1893 + ( SELECT coalesce(sum("di"."weight"), 0) 3.1894 + FROM "opinion" JOIN "direct_interest_snapshot" AS "di" 3.1895 + ON "di"."snapshot_id" = "snapshot_id_v" 3.1896 + AND "di"."issue_id" = "issue_id_v" 3.1897 + AND "di"."member_id" = "opinion"."member_id" 3.1898 + WHERE "opinion"."suggestion_id" = "suggestion"."id" 3.1899 + AND "opinion"."degree" = -1 3.1900 + AND "opinion"."fulfilled" = TRUE 3.1901 + ) AS "minus1_fulfilled_count", 3.1902 + ( SELECT coalesce(sum("di"."weight"), 0) 3.1903 + FROM "opinion" JOIN "direct_interest_snapshot" AS "di" 3.1904 + ON "di"."snapshot_id" = "snapshot_id_v" 3.1905 + AND "di"."issue_id" = "issue_id_v" 3.1906 + AND "di"."member_id" = "opinion"."member_id" 3.1907 + WHERE "opinion"."suggestion_id" = "suggestion"."id" 3.1908 + AND "opinion"."degree" = 1 3.1909 + AND "opinion"."fulfilled" = FALSE 3.1910 + ) AS "plus1_unfulfilled_count", 3.1911 + ( SELECT coalesce(sum("di"."weight"), 0) 3.1912 + FROM "opinion" JOIN "direct_interest_snapshot" AS "di" 3.1913 + ON "di"."snapshot_id" = "snapshot_id_v" 3.1914 + AND "di"."issue_id" = "issue_id_v" 3.1915 + AND "di"."member_id" = "opinion"."member_id" 3.1916 + WHERE "opinion"."suggestion_id" = "suggestion"."id" 3.1917 + AND "opinion"."degree" = 1 3.1918 + AND "opinion"."fulfilled" = TRUE 3.1919 + ) AS "plus1_fulfilled_count", 3.1920 + ( SELECT coalesce(sum("di"."weight"), 0) 3.1921 + FROM "opinion" JOIN "direct_interest_snapshot" AS "di" 3.1922 + ON "di"."snapshot_id" = "snapshot_id_v" 3.1923 + AND "di"."issue_id" = "issue_id_v" 3.1924 + AND "di"."member_id" = "opinion"."member_id" 3.1925 + WHERE "opinion"."suggestion_id" = "suggestion"."id" 3.1926 + AND "opinion"."degree" = 2 3.1927 + AND "opinion"."fulfilled" = FALSE 3.1928 + ) AS "plus2_unfulfilled_count", 3.1929 + ( SELECT coalesce(sum("di"."weight"), 0) 3.1930 + FROM "opinion" JOIN "direct_interest_snapshot" AS "di" 3.1931 + ON "di"."snapshot_id" = "snapshot_id_v" 3.1932 + AND "di"."issue_id" = "issue_id_v" 3.1933 + AND "di"."member_id" = "opinion"."member_id" 3.1934 + WHERE "opinion"."suggestion_id" = "suggestion"."id" 3.1935 + AND "opinion"."degree" = 2 3.1936 + AND "opinion"."fulfilled" = TRUE 3.1937 + ) AS "plus2_fulfilled_count" 3.1938 + FROM "suggestion" JOIN "initiative" 3.1939 + ON "suggestion"."initiative_id" = "initiative"."id" 3.1940 + WHERE "initiative"."issue_id" = "issue_id_v"; 3.1941 + END LOOP; 3.1942 + RETURN "snapshot_id_v"; 3.1943 + END; 3.1944 + $$; 3.1945 + 3.1946 +CREATE OR REPLACE FUNCTION "weight_of_added_vote_delegations" 3.1947 + ( "issue_id_p" "issue"."id"%TYPE, 3.1948 + "member_id_p" "member"."id"%TYPE, 3.1949 + "delegate_member_ids_p" "delegating_voter"."delegate_member_ids"%TYPE ) 3.1950 + RETURNS "direct_voter"."weight"%TYPE 3.1951 + LANGUAGE 'plpgsql' VOLATILE AS $$ 3.1952 + DECLARE 3.1953 + "issue_delegation_row" "issue_delegation"%ROWTYPE; 3.1954 + "delegate_member_ids_v" "delegating_voter"."delegate_member_ids"%TYPE; 3.1955 + "weight_v" INT4; 3.1956 + "sub_weight_v" INT4; 3.1957 + BEGIN 3.1958 + PERFORM "require_transaction_isolation"(); 3.1959 + "weight_v" := 0; 3.1960 + FOR "issue_delegation_row" IN 3.1961 + SELECT * FROM "issue_delegation" 3.1962 + WHERE "trustee_id" = "member_id_p" 3.1963 + AND "issue_id" = "issue_id_p" 3.1964 + LOOP 3.1965 + IF NOT EXISTS ( 3.1966 + SELECT NULL FROM "direct_voter" 3.1967 + WHERE "member_id" = "issue_delegation_row"."truster_id" 3.1968 + AND "issue_id" = "issue_id_p" 3.1969 + ) AND NOT EXISTS ( 3.1970 + SELECT NULL FROM "delegating_voter" 3.1971 + WHERE "member_id" = "issue_delegation_row"."truster_id" 3.1972 + AND "issue_id" = "issue_id_p" 3.1973 + ) THEN 3.1974 + "delegate_member_ids_v" := 3.1975 + "member_id_p" || "delegate_member_ids_p"; 3.1976 + INSERT INTO "delegating_voter" ( 3.1977 + "issue_id", 3.1978 + "member_id", 3.1979 + "ownweight", 3.1980 + "scope", 3.1981 + "delegate_member_ids" 3.1982 + ) VALUES ( 3.1983 + "issue_id_p", 3.1984 + "issue_delegation_row"."truster_id", 3.1985 + "issue_delegation_row"."weight", 3.1986 + "issue_delegation_row"."scope", 3.1987 + "delegate_member_ids_v" 3.1988 + ); 3.1989 + "sub_weight_v" := "issue_delegation_row"."weight" + 3.1990 + "weight_of_added_vote_delegations"( 3.1991 + "issue_id_p", 3.1992 + "issue_delegation_row"."truster_id", 3.1993 + "delegate_member_ids_v" 3.1994 + ); 3.1995 + UPDATE "delegating_voter" 3.1996 + SET "weight" = "sub_weight_v" 3.1997 + WHERE "issue_id" = "issue_id_p" 3.1998 + AND "member_id" = "issue_delegation_row"."truster_id"; 3.1999 + "weight_v" := "weight_v" + "sub_weight_v"; 3.2000 + END IF; 3.2001 + END LOOP; 3.2002 + RETURN "weight_v"; 3.2003 + END; 3.2004 + $$; 3.2005 + 3.2006 +CREATE OR REPLACE FUNCTION "add_vote_delegations" 3.2007 + ( "issue_id_p" "issue"."id"%TYPE ) 3.2008 + RETURNS VOID 3.2009 + LANGUAGE 'plpgsql' VOLATILE AS $$ 3.2010 + DECLARE 3.2011 + "member_id_v" "member"."id"%TYPE; 3.2012 + BEGIN 3.2013 + PERFORM "require_transaction_isolation"(); 3.2014 + FOR "member_id_v" IN 3.2015 + SELECT "member_id" FROM "direct_voter" 3.2016 + WHERE "issue_id" = "issue_id_p" 3.2017 + LOOP 3.2018 + UPDATE "direct_voter" SET 3.2019 + "weight" = "ownweight" + "weight_of_added_vote_delegations"( 3.2020 + "issue_id_p", 3.2021 + "member_id_v", 3.2022 + '{}' 3.2023 + ) 3.2024 + WHERE "member_id" = "member_id_v" 3.2025 + AND "issue_id" = "issue_id_p"; 3.2026 + END LOOP; 3.2027 + RETURN; 3.2028 + END; 3.2029 + $$; 3.2030 + 3.2031 +CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE) 3.2032 + RETURNS VOID 3.2033 + LANGUAGE 'plpgsql' VOLATILE AS $$ 3.2034 + DECLARE 3.2035 + "area_id_v" "area"."id"%TYPE; 3.2036 + "unit_id_v" "unit"."id"%TYPE; 3.2037 + "member_id_v" "member"."id"%TYPE; 3.2038 + BEGIN 3.2039 + PERFORM "require_transaction_isolation"(); 3.2040 + SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p"; 3.2041 + SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v"; 3.2042 + -- override protection triggers: 3.2043 + INSERT INTO "temporary_transaction_data" ("key", "value") 3.2044 + VALUES ('override_protection_triggers', TRUE::TEXT); 3.2045 + -- delete timestamp of voting comment: 3.2046 + UPDATE "direct_voter" SET "comment_changed" = NULL 3.2047 + WHERE "issue_id" = "issue_id_p"; 3.2048 + -- delete delegating votes (in cases of manual reset of issue state): 3.2049 + DELETE FROM "delegating_voter" 3.2050 + WHERE "issue_id" = "issue_id_p"; 3.2051 + -- delete votes from non-privileged voters: 3.2052 + DELETE FROM "direct_voter" 3.2053 + USING ( 3.2054 + SELECT "direct_voter"."member_id" 3.2055 + FROM "direct_voter" 3.2056 + JOIN "member" ON "direct_voter"."member_id" = "member"."id" 3.2057 + LEFT JOIN "privilege" 3.2058 + ON "privilege"."unit_id" = "unit_id_v" 3.2059 + AND "privilege"."member_id" = "direct_voter"."member_id" 3.2060 + LEFT JOIN "issue_privilege" 3.2061 + ON "issue_privilege"."issue_id" = "issue_id_p" 3.2062 + AND "issue_privilege"."member_id" = "direct_voter"."member_id" 3.2063 + WHERE "direct_voter"."issue_id" = "issue_id_p" AND ( 3.2064 + "member"."active" = FALSE OR 3.2065 + COALESCE( 3.2066 + "issue_privilege"."voting_right", 3.2067 + "privilege"."voting_right", 3.2068 + FALSE 3.2069 + ) = FALSE 3.2070 + ) 3.2071 + ) AS "subquery" 3.2072 + WHERE "direct_voter"."issue_id" = "issue_id_p" 3.2073 + AND "direct_voter"."member_id" = "subquery"."member_id"; 3.2074 + -- consider voting weight and delegations: 3.2075 + UPDATE "direct_voter" SET "ownweight" = "privilege"."weight" 3.2076 + FROM "privilege" 3.2077 + WHERE "issue_id" = "issue_id_p" 3.2078 + AND "privilege"."unit_id" = "unit_id_v" 3.2079 + AND "privilege"."member_id" = "direct_voter"."member_id"; 3.2080 + UPDATE "direct_voter" SET "ownweight" = "issue_privilege"."weight" 3.2081 + FROM "issue_privilege" 3.2082 + WHERE "direct_voter"."issue_id" = "issue_id_p" 3.2083 + AND "issue_privilege"."issue_id" = "issue_id_p" 3.2084 + AND "issue_privilege"."member_id" = "direct_voter"."member_id"; 3.2085 + PERFORM "add_vote_delegations"("issue_id_p"); 3.2086 + -- mark first preferences: 3.2087 + UPDATE "vote" SET "first_preference" = "subquery"."first_preference" 3.2088 + FROM ( 3.2089 + SELECT 3.2090 + "vote"."initiative_id", 3.2091 + "vote"."member_id", 3.2092 + CASE WHEN "vote"."grade" > 0 THEN 3.2093 + CASE WHEN "vote"."grade" = max("agg"."grade") THEN TRUE ELSE FALSE END 3.2094 + ELSE NULL 3.2095 + END AS "first_preference" 3.2096 + FROM "vote" 3.2097 + JOIN "initiative" -- NOTE: due to missing index on issue_id 3.2098 + ON "vote"."issue_id" = "initiative"."issue_id" 3.2099 + JOIN "vote" AS "agg" 3.2100 + ON "initiative"."id" = "agg"."initiative_id" 3.2101 + AND "vote"."member_id" = "agg"."member_id" 3.2102 + GROUP BY "vote"."initiative_id", "vote"."member_id", "vote"."grade" 3.2103 + ) AS "subquery" 3.2104 + WHERE "vote"."issue_id" = "issue_id_p" 3.2105 + AND "vote"."initiative_id" = "subquery"."initiative_id" 3.2106 + AND "vote"."member_id" = "subquery"."member_id"; 3.2107 + -- finish overriding protection triggers (avoids garbage): 3.2108 + DELETE FROM "temporary_transaction_data" 3.2109 + WHERE "key" = 'override_protection_triggers'; 3.2110 + -- materialize battle_view: 3.2111 + -- NOTE: "closed" column of issue must be set at this point 3.2112 + DELETE FROM "battle" WHERE "issue_id" = "issue_id_p"; 3.2113 + INSERT INTO "battle" ( 3.2114 + "issue_id", 3.2115 + "winning_initiative_id", "losing_initiative_id", 3.2116 + "count" 3.2117 + ) SELECT 3.2118 + "issue_id", 3.2119 + "winning_initiative_id", "losing_initiative_id", 3.2120 + "count" 3.2121 + FROM "battle_view" WHERE "issue_id" = "issue_id_p"; 3.2122 + -- set voter count: 3.2123 + UPDATE "issue" SET 3.2124 + "voter_count" = ( 3.2125 + SELECT coalesce(sum("weight"), 0) 3.2126 + FROM "direct_voter" WHERE "issue_id" = "issue_id_p" 3.2127 + ) 3.2128 + WHERE "id" = "issue_id_p"; 3.2129 + -- copy "positive_votes" and "negative_votes" from "battle" table: 3.2130 + -- NOTE: "first_preference_votes" is set to a default of 0 at this step 3.2131 + UPDATE "initiative" SET 3.2132 + "first_preference_votes" = 0, 3.2133 + "positive_votes" = "battle_win"."count", 3.2134 + "negative_votes" = "battle_lose"."count" 3.2135 + FROM "battle" AS "battle_win", "battle" AS "battle_lose" 3.2136 + WHERE 3.2137 + "battle_win"."issue_id" = "issue_id_p" AND 3.2138 + "battle_win"."winning_initiative_id" = "initiative"."id" AND 3.2139 + "battle_win"."losing_initiative_id" ISNULL AND 3.2140 + "battle_lose"."issue_id" = "issue_id_p" AND 3.2141 + "battle_lose"."losing_initiative_id" = "initiative"."id" AND 3.2142 + "battle_lose"."winning_initiative_id" ISNULL; 3.2143 + -- calculate "first_preference_votes": 3.2144 + -- NOTE: will only set values not equal to zero 3.2145 + UPDATE "initiative" SET "first_preference_votes" = "subquery"."sum" 3.2146 + FROM ( 3.2147 + SELECT "vote"."initiative_id", sum("direct_voter"."weight") 3.2148 + FROM "vote" JOIN "direct_voter" 3.2149 + ON "vote"."issue_id" = "direct_voter"."issue_id" 3.2150 + AND "vote"."member_id" = "direct_voter"."member_id" 3.2151 + WHERE "vote"."first_preference" 3.2152 + GROUP BY "vote"."initiative_id" 3.2153 + ) AS "subquery" 3.2154 + WHERE "initiative"."issue_id" = "issue_id_p" 3.2155 + AND "initiative"."admitted" 3.2156 + AND "initiative"."id" = "subquery"."initiative_id"; 3.2157 + END; 3.2158 + $$; 3.2159 + 3.2160 +COMMIT;
4.1 --- a/update/core-update.v4.2.0-v4.2.1.sql Fri Nov 27 15:45:31 2020 +0100 4.2 +++ b/update/core-update.v4.2.0-v4.2.1.sql Sat Dec 05 04:04:52 2020 +0100 4.3 @@ -1,9 +1,9 @@ 4.4 +BEGIN; 4.5 + 4.6 CREATE OR REPLACE VIEW "liquid_feedback_version" AS 4.7 - SELECT * FROM (VALUES ('4.2.1-incomplete-update', 4, 2, -1)) 4.8 + SELECT * FROM (VALUES ('4.2.1', 4, 2, 1)) 4.9 AS "subquery"("string", "major", "minor", "revision"); 4.10 4.11 -BEGIN; 4.12 - 4.13 ALTER TABLE "unit" ADD COLUMN "attr" JSONB NOT NULL DEFAULT '{}' CHECK (jsonb_typeof("attr") = 'object'); 4.14 COMMENT ON COLUMN "unit"."attr" IS 'Opaque data structure to store any extended attributes used by frontend or middleware'; 4.15 4.16 @@ -970,4 +970,25 @@ 4.17 END; 4.18 $$; 4.19 4.20 +DROP INDEX "posting_global_idx"; 4.21 +DROP INDEX "posting_unit_idx"; 4.22 +DROP INDEX "posting_area_idx"; 4.23 +DROP INDEX "posting_policy_idx"; 4.24 +DROP INDEX "posting_issue_idx"; 4.25 +DROP INDEX "posting_initiative_idx"; 4.26 +DROP INDEX "posting_suggestion_idx"; 4.27 + 4.28 +DROP INDEX "posting_lexeme_idx"; 4.29 + 4.30 +DROP INDEX "event_tl_global_idx"; 4.31 +DROP INDEX "event_tl_unit_idx"; 4.32 +DROP INDEX "event_tl_area_idx"; 4.33 +DROP INDEX "event_tl_policy_idx"; 4.34 +DROP INDEX "event_tl_issue_idx"; 4.35 +DROP INDEX "event_tl_initiative_idx"; 4.36 +DROP INDEX "event_tl_suggestion_idx"; 4.37 + 4.38 +DROP EXTENSION IF EXISTS conflux; 4.39 +DROP EXTENSION IF EXISTS btree_gist; 4.40 + 4.41 COMMIT;