liquid_feedback_core

changeset 602:9570aeabf4fc

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

Impressum / About Us