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 (10 months ago)
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;

Impressum / About Us