liquid_feedback_core

changeset 517:e4f619e87664 v3.2.1

Fixed accidental PostgreSQL 9.5 dependency (because of UPSERT aka ON CONFLICT clause)
author jbe
date Sat Apr 30 17:03:48 2016 +0200 (2016-04-30)
parents ba7d5c7ede4f
children b1a7ebf67470
files core.sql update/core-update.v3.1.0-v3.2.1.sql update/core-update.v3.2.0-v3.2.1.sql
line diff
     1.1 --- a/core.sql	Sun Apr 24 21:30:22 2016 +0200
     1.2 +++ b/core.sql	Sat Apr 30 17:03:48 2016 +0200
     1.3 @@ -7,7 +7,7 @@
     1.4  BEGIN;
     1.5  
     1.6  CREATE VIEW "liquid_feedback_version" AS
     1.7 -  SELECT * FROM (VALUES ('3.2.0', 3, 2, 0))
     1.8 +  SELECT * FROM (VALUES ('3.2.1', 3, 2, 1))
     1.9    AS "subquery"("string", "major", "minor", "revision");
    1.10  
    1.11  
    1.12 @@ -3257,6 +3257,18 @@
    1.13          SELECT "id" INTO "last_suggestion_id_v" FROM "suggestion"
    1.14            WHERE "suggestion"."initiative_id" = "result_row"."initiative_id"
    1.15            ORDER BY "id" DESC LIMIT 1;
    1.16 +        /* compatibility with PostgreSQL 9.1 */
    1.17 +        DELETE FROM "notification_initiative_sent"
    1.18 +          WHERE "member_id" = "recipient_id_p"
    1.19 +          AND "initiative_id" = "result_row"."initiative_id";
    1.20 +        INSERT INTO "notification_initiative_sent"
    1.21 +          ("member_id", "initiative_id", "last_draft_id", "last_suggestion_id")
    1.22 +          VALUES (
    1.23 +            "recipient_id_p",
    1.24 +            "result_row"."initiative_id",
    1.25 +            "last_draft_id_v",
    1.26 +            "last_suggestion_id_v" );
    1.27 +        /* TODO: use alternative code below, requires PostgreSQL 9.5 or higher
    1.28          INSERT INTO "notification_initiative_sent"
    1.29            ("member_id", "initiative_id", "last_draft_id", "last_suggestion_id")
    1.30            VALUES (
    1.31 @@ -3265,16 +3277,9 @@
    1.32              "last_draft_id_v",
    1.33              "last_suggestion_id_v" )
    1.34            ON CONFLICT ("member_id", "initiative_id") DO UPDATE SET
    1.35 -            "last_draft_id" = CASE
    1.36 -              WHEN "notification_initiative_sent"."last_draft_id" > "last_draft_id_v"
    1.37 -              THEN "notification_initiative_sent"."last_draft_id"
    1.38 -              ELSE "last_draft_id_v"
    1.39 -            END,
    1.40 -            "last_suggestion_id" = CASE
    1.41 -              WHEN "notification_initiative_sent"."last_suggestion_id" > "last_suggestion_id_v"
    1.42 -              THEN "notification_initiative_sent"."last_suggestion_id"
    1.43 -              ELSE "last_suggestion_id_v"
    1.44 -            END;
    1.45 +            "last_draft_id" = "last_draft_id_v",
    1.46 +            "last_suggestion_id" = "last_suggestion_id_v";
    1.47 +        */
    1.48          RETURN NEXT "result_row";
    1.49        END LOOP;
    1.50        DELETE FROM "notification_initiative_sent"
     2.1 --- /dev/null	Thu Jan 01 00:00:00 1970 +0000
     2.2 +++ b/update/core-update.v3.1.0-v3.2.1.sql	Sat Apr 30 17:03:48 2016 +0200
     2.3 @@ -0,0 +1,795 @@
     2.4 +BEGIN;
     2.5 +
     2.6 +CREATE OR REPLACE VIEW "liquid_feedback_version" AS
     2.7 +  SELECT * FROM (VALUES ('3.2.1', 3, 2, 1))
     2.8 +  AS "subquery"("string", "major", "minor", "revision");
     2.9 +
    2.10 +ALTER TABLE "member" ADD COLUMN "disable_notifications"    BOOLEAN NOT NULL DEFAULT FALSE;
    2.11 +ALTER TABLE "member" ADD COLUMN "notification_counter"     INT4    NOT NULL DEFAULT 1;
    2.12 +ALTER TABLE "member" ADD COLUMN "notification_sample_size" INT4    NOT NULL DEFAULT 3;
    2.13 +ALTER TABLE "member" ADD COLUMN "notification_dow"         INT4    CHECK ("notification_dow" BETWEEN 0 AND 6);
    2.14 +ALTER TABLE "member" ADD COLUMN "notification_hour"        INT4    DEFAULT floor(random() * 24) CHECK ("notification_hour" BETWEEN 0 AND 23);
    2.15 +ALTER TABLE "member" ADD COLUMN "notification_sent"        TIMESTAMP;
    2.16 +ALTER TABLE "member" ADD
    2.17 +  CONSTRAINT "notification_dow_requires_notification_hour"
    2.18 +  CHECK ("notification_dow" ISNULL OR "notification_hour" NOTNULL);
    2.19 +
    2.20 +UPDATE "member" SET "disable_notifications" = TRUE WHERE "notify_level" = 'none'::"notify_level";
    2.21 +
    2.22 +DROP VIEW "selected_event_seen_by_member";
    2.23 +DROP VIEW "event_seen_by_member";
    2.24 +
    2.25 +ALTER TABLE "member" DROP COLUMN "notify_level";
    2.26 +
    2.27 +DROP TYPE "notify_level";
    2.28 +
    2.29 +COMMENT ON COLUMN "member"."disable_notifications"    IS 'TRUE if member does not want to receive notifications';
    2.30 +COMMENT ON COLUMN "member"."notification_counter"     IS 'Sequential number of next scheduled notification message (used as a seed for pseudo-random initiative selection algorithm)';
    2.31 +COMMENT ON COLUMN "member"."notification_sample_size" IS 'Number of featured initiatives per issue in scheduled notification messages';
    2.32 +COMMENT ON COLUMN "member"."notification_dow"         IS 'Day of week for scheduled notifications (NULL to receive a daily digest)';
    2.33 +COMMENT ON COLUMN "member"."notification_hour"        IS 'Time of day when scheduled notifications are sent out';
    2.34 +COMMENT ON COLUMN "member"."notification_sent"        IS 'Timestamp of last scheduled notification mail that has been sent out';
    2.35 +
    2.36 +ALTER TABLE "rendered_member_statement" ALTER COLUMN "member_id" SET DATA TYPE INT4;
    2.37 +
    2.38 +DROP VIEW "expired_session";
    2.39 +
    2.40 +ALTER TABLE "session" ALTER COLUMN "member_id" SET DATA TYPE INT4;
    2.41 +
    2.42 +CREATE VIEW "expired_session" AS
    2.43 +  SELECT * FROM "session" WHERE now() > "expiry";
    2.44 +CREATE RULE "delete" AS ON DELETE TO "expired_session" DO INSTEAD
    2.45 +  DELETE FROM "session" WHERE "ident" = OLD."ident";
    2.46 +
    2.47 +COMMENT ON VIEW "expired_session" IS 'View containing all expired sessions where DELETE is possible';
    2.48 +COMMENT ON RULE "delete" ON "expired_session" IS 'Rule allowing DELETE on rows in "expired_session" view, i.e. DELETE FROM "expired_session"';
    2.49 +
    2.50 +CREATE TABLE "subscription" (
    2.51 +        PRIMARY KEY ("member_id", "unit_id"),
    2.52 +        "member_id"             INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    2.53 +        "unit_id"               INT4            REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
    2.54 +CREATE INDEX "subscription_unit_id_idx" ON "subscription" ("unit_id");
    2.55 +
    2.56 +COMMENT ON TABLE "subscription" IS 'An entry in this table denotes that the member wishes to receive notifications regardless of his/her privileges in the given unit';
    2.57 +
    2.58 +CREATE TABLE "ignored_area" (
    2.59 +        PRIMARY KEY ("member_id", "area_id"),
    2.60 +        "member_id"             INT4            REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    2.61 +        "area_id"               INT4            REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
    2.62 +CREATE INDEX "ignored_area_area_id_idx" ON "ignored_area" ("area_id");
    2.63 +
    2.64 +COMMENT ON TABLE "ignored_area" IS 'An entry in this table denotes that the member does not wish to receive notifications for the given subject area unless he/she declared interested in a particular issue';
    2.65 +
    2.66 +ALTER TABLE "ignored_initiative" DROP CONSTRAINT "ignored_initiative_pkey";
    2.67 +DROP INDEX "ignored_initiative_member_id_idx";
    2.68 +
    2.69 +ALTER TABLE "ignored_initiative" ADD PRIMARY KEY ("member_id", "initiative_id");
    2.70 +CREATE INDEX "ignored_initiative_initiative_id_idx" ON "ignored_initiative" ("initiative_id");
    2.71 +
    2.72 +COMMENT ON TABLE "ignored_initiative" IS 'An entry in this table denotes that the member does not wish to receive notifications for the given initiative';
    2.73 +
    2.74 +ALTER TABLE "notification_sent" RENAME TO "notification_event_sent";
    2.75 +ALTER INDEX "notification_sent_singleton_idx" RENAME TO "notification_event_sent_singleton_idx";
    2.76 +
    2.77 +CREATE TABLE "notification_initiative_sent" (
    2.78 +        PRIMARY KEY ("member_id", "initiative_id"),
    2.79 +        "member_id"             INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    2.80 +        "initiative_id"         INT4            REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    2.81 +        "last_draft_id"         INT8            NOT NULL,
    2.82 +        "last_suggestion_id"    INT8 );
    2.83 +CREATE INDEX "notification_initiative_sent_initiative_idx" ON "notification_initiative_sent" ("initiative_id");
    2.84 +
    2.85 +COMMENT ON TABLE "notification_initiative_sent" IS 'Information which initiatives have been promoted to a member in a scheduled notification mail';
    2.86 +
    2.87 +COMMENT ON COLUMN "notification_initiative_sent"."last_draft_id"      IS 'Current (i.e. last) draft_id when initiative had been promoted';
    2.88 +COMMENT ON COLUMN "notification_initiative_sent"."last_suggestion_id" IS 'Current (i.e. last) draft_id when initiative had been promoted';
    2.89 +
    2.90 +CREATE TABLE "newsletter" (
    2.91 +        "id"                    SERIAL4         PRIMARY KEY,
    2.92 +        "published"             TIMESTAMPTZ     NOT NULL,
    2.93 +        "unit_id"               INT4            REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    2.94 +        "include_all_members"   BOOLEAN         NOT NULL,
    2.95 +        "sent"                  TIMESTAMPTZ,
    2.96 +        "subject"               TEXT            NOT NULL,
    2.97 +        "content"               TEXT            NOT NULL );
    2.98 +CREATE INDEX "newsletter_unit_id_idx" ON "newsletter" ("unit_id", "published");
    2.99 +CREATE INDEX "newsletter_all_units_published_idx" ON "newsletter" ("published") WHERE "unit_id" ISNULL;
   2.100 +CREATE INDEX "newsletter_published_idx" ON "newsletter" ("published");
   2.101 +
   2.102 +COMMENT ON TABLE "newsletter" IS 'Contains newsletters created by administrators to be sent out and for further reference';
   2.103 +
   2.104 +COMMENT ON COLUMN "newsletter"."published"           IS 'Timestamp when the newsletter is to be sent out (and made available in the frontend)';
   2.105 +COMMENT ON COLUMN "newsletter"."unit_id"             IS 'If set, only members with voting right in the given unit are considered to be recipients';
   2.106 +COMMENT ON COLUMN "newsletter"."include_all_members" IS 'TRUE = include all members regardless of their ''disable_notifications'' setting';
   2.107 +COMMENT ON COLUMN "newsletter"."sent"                IS 'Timestamp when the newsletter has been mailed out';
   2.108 +COMMENT ON COLUMN "newsletter"."subject"             IS 'Subject line (e.g. to be used for the email)';
   2.109 +COMMENT ON COLUMN "newsletter"."content"             IS 'Plain text content of the newsletter';
   2.110 +
   2.111 +CREATE OR REPLACE FUNCTION "issue_requires_first_initiative_trigger"()
   2.112 +  RETURNS TRIGGER
   2.113 +  LANGUAGE 'plpgsql' VOLATILE AS $$
   2.114 +    BEGIN
   2.115 +      IF NOT EXISTS (
   2.116 +        SELECT NULL FROM "initiative" WHERE "issue_id" = NEW."id"
   2.117 +      ) THEN
   2.118 +        RAISE EXCEPTION 'Cannot create issue without an initial initiative.' USING
   2.119 +          ERRCODE = 'integrity_constraint_violation',
   2.120 +          HINT    = 'Create issue, initiative, and draft within the same transaction.';
   2.121 +      END IF;
   2.122 +      RETURN NULL;
   2.123 +    END;
   2.124 +  $$;
   2.125 +
   2.126 +CREATE OR REPLACE FUNCTION "initiative_requires_first_draft_trigger"()
   2.127 +  RETURNS TRIGGER
   2.128 +  LANGUAGE 'plpgsql' VOLATILE AS $$
   2.129 +    BEGIN
   2.130 +      IF NOT EXISTS (
   2.131 +        SELECT NULL FROM "draft" WHERE "initiative_id" = NEW."id"
   2.132 +      ) THEN
   2.133 +        RAISE EXCEPTION 'Cannot create initiative without an initial draft.' USING
   2.134 +          ERRCODE = 'integrity_constraint_violation',
   2.135 +          HINT    = 'Create issue, initiative and draft within the same transaction.';
   2.136 +      END IF;
   2.137 +      RETURN NULL;
   2.138 +    END;
   2.139 +  $$;
   2.140 +
   2.141 +CREATE OR REPLACE FUNCTION "suggestion_requires_first_opinion_trigger"()
   2.142 +  RETURNS TRIGGER
   2.143 +  LANGUAGE 'plpgsql' VOLATILE AS $$
   2.144 +    BEGIN
   2.145 +      IF NOT EXISTS (
   2.146 +        SELECT NULL FROM "opinion" WHERE "suggestion_id" = NEW."id"
   2.147 +      ) THEN
   2.148 +        RAISE EXCEPTION 'Cannot create a suggestion without an opinion.' USING
   2.149 +          ERRCODE = 'integrity_constraint_violation',
   2.150 +          HINT    = 'Create suggestion and opinion within the same transaction.';
   2.151 +      END IF;
   2.152 +      RETURN NULL;
   2.153 +    END;
   2.154 +  $$;
   2.155 +
   2.156 +CREATE OR REPLACE FUNCTION "forbid_changes_on_closed_issue_trigger"()
   2.157 +  RETURNS TRIGGER
   2.158 +  LANGUAGE 'plpgsql' VOLATILE AS $$
   2.159 +    DECLARE
   2.160 +      "issue_id_v" "issue"."id"%TYPE;
   2.161 +      "issue_row"  "issue"%ROWTYPE;
   2.162 +    BEGIN
   2.163 +      IF EXISTS (
   2.164 +        SELECT NULL FROM "temporary_transaction_data"
   2.165 +        WHERE "txid" = txid_current()
   2.166 +        AND "key" = 'override_protection_triggers'
   2.167 +        AND "value" = TRUE::TEXT
   2.168 +      ) THEN
   2.169 +        RETURN NULL;
   2.170 +      END IF;
   2.171 +      IF TG_OP = 'DELETE' THEN
   2.172 +        "issue_id_v" := OLD."issue_id";
   2.173 +      ELSE
   2.174 +        "issue_id_v" := NEW."issue_id";
   2.175 +      END IF;
   2.176 +      SELECT INTO "issue_row" * FROM "issue"
   2.177 +        WHERE "id" = "issue_id_v" FOR SHARE;
   2.178 +      IF (
   2.179 +        "issue_row"."closed" NOTNULL OR (
   2.180 +          "issue_row"."state" = 'voting' AND
   2.181 +          "issue_row"."phase_finished" NOTNULL
   2.182 +        )
   2.183 +      ) THEN
   2.184 +        IF
   2.185 +          TG_RELID = 'direct_voter'::regclass AND
   2.186 +          TG_OP = 'UPDATE'
   2.187 +        THEN
   2.188 +          IF
   2.189 +            OLD."issue_id"  = NEW."issue_id"  AND
   2.190 +            OLD."member_id" = NEW."member_id" AND
   2.191 +            OLD."weight" = NEW."weight"
   2.192 +          THEN
   2.193 +            RETURN NULL;  -- allows changing of voter comment
   2.194 +          END IF;
   2.195 +        END IF;
   2.196 +        RAISE EXCEPTION 'Tried to modify data after voting has been closed.' USING
   2.197 +          ERRCODE = 'integrity_constraint_violation';
   2.198 +      END IF;
   2.199 +      RETURN NULL;
   2.200 +    END;
   2.201 +  $$;
   2.202 +
   2.203 +CREATE VIEW "event_for_notification" AS
   2.204 +  SELECT
   2.205 +    "member"."id" AS "recipient_id",
   2.206 +    "event".*
   2.207 +  FROM "member" CROSS JOIN "event"
   2.208 +  JOIN "issue" ON "issue"."id" = "event"."issue_id"
   2.209 +  JOIN "area" ON "area"."id" = "issue"."area_id"
   2.210 +  LEFT JOIN "privilege" ON
   2.211 +    "privilege"."member_id" = "member"."id" AND
   2.212 +    "privilege"."unit_id" = "area"."unit_id" AND
   2.213 +    "privilege"."voting_right" = TRUE
   2.214 +  LEFT JOIN "subscription" ON
   2.215 +    "subscription"."member_id" = "member"."id" AND
   2.216 +    "subscription"."unit_id" = "area"."unit_id"
   2.217 +  LEFT JOIN "ignored_area" ON
   2.218 +    "ignored_area"."member_id" = "member"."id" AND
   2.219 +    "ignored_area"."area_id" = "issue"."area_id"
   2.220 +  LEFT JOIN "interest" ON
   2.221 +    "interest"."member_id" = "member"."id" AND
   2.222 +    "interest"."issue_id" = "event"."issue_id"
   2.223 +  LEFT JOIN "supporter" ON
   2.224 +    "supporter"."member_id" = "member"."id" AND
   2.225 +    "supporter"."initiative_id" = "event"."initiative_id"
   2.226 +  WHERE ("privilege"."member_id" NOTNULL OR "subscription"."member_id" NOTNULL)
   2.227 +  AND ("ignored_area"."member_id" ISNULL OR "interest"."member_id" NOTNULL)
   2.228 +  AND (
   2.229 +    "event"."event" = 'issue_state_changed'::"event_type" OR
   2.230 +    ( "event"."event" = 'initiative_revoked'::"event_type" AND
   2.231 +      "supporter"."member_id" NOTNULL ) );
   2.232 +
   2.233 +COMMENT ON VIEW "event_for_notification" IS 'Entries of the "event" table which are of interest for a particular notification mail recipient';
   2.234 +
   2.235 +COMMENT ON COLUMN "event_for_notification"."recipient_id" IS 'member_id of the recipient of a notification mail';
   2.236 +
   2.237 +CREATE VIEW "updated_initiative" AS
   2.238 +  SELECT
   2.239 +    "supporter"."member_id" AS "recipient_id",
   2.240 +    FALSE AS "featured",
   2.241 +    "supporter"."initiative_id"
   2.242 +  FROM "supporter"
   2.243 +  JOIN "initiative" ON "supporter"."initiative_id" = "initiative"."id"
   2.244 +  JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
   2.245 +  LEFT JOIN "notification_initiative_sent" AS "sent" ON
   2.246 +    "sent"."member_id" = "supporter"."member_id" AND
   2.247 +    "sent"."initiative_id" = "supporter"."initiative_id"
   2.248 +  LEFT JOIN "ignored_initiative" ON
   2.249 +    "ignored_initiative"."member_id" = "supporter"."member_id" AND
   2.250 +    "ignored_initiative"."initiative_id" = "supporter"."initiative_id"
   2.251 +  WHERE "issue"."state" IN ('admission', 'discussion')
   2.252 +  AND "initiative"."revoked" ISNULL
   2.253 +  AND "ignored_initiative"."member_id" ISNULL
   2.254 +  AND (
   2.255 +    EXISTS (
   2.256 +      SELECT NULL FROM "draft"
   2.257 +      LEFT JOIN "ignored_member" ON
   2.258 +        "ignored_member"."member_id" = "supporter"."member_id" AND
   2.259 +        "ignored_member"."other_member_id" = "draft"."author_id"
   2.260 +      WHERE "draft"."initiative_id" = "supporter"."initiative_id"
   2.261 +      AND "draft"."id" > "supporter"."draft_id"
   2.262 +      AND "ignored_member"."member_id" ISNULL
   2.263 +    ) OR EXISTS (
   2.264 +      SELECT NULL FROM "suggestion"
   2.265 +      LEFT JOIN "opinion" ON
   2.266 +        "opinion"."member_id" = "supporter"."member_id" AND
   2.267 +        "opinion"."suggestion_id" = "suggestion"."id"
   2.268 +      LEFT JOIN "ignored_member" ON
   2.269 +        "ignored_member"."member_id" = "supporter"."member_id" AND
   2.270 +        "ignored_member"."other_member_id" = "suggestion"."author_id"
   2.271 +      WHERE "suggestion"."initiative_id" = "supporter"."initiative_id"
   2.272 +      AND "opinion"."member_id" ISNULL
   2.273 +      AND COALESCE("suggestion"."id" > "sent"."last_suggestion_id", TRUE)
   2.274 +      AND "ignored_member"."member_id" ISNULL
   2.275 +    )
   2.276 +  );
   2.277 +
   2.278 +COMMENT ON VIEW "updated_initiative" IS 'Helper view for view "updated_or_featured_initiative"';
   2.279 +
   2.280 +CREATE FUNCTION "featured_initiative"
   2.281 +  ( "recipient_id_p" "member"."id"%TYPE,
   2.282 +    "area_id_p"      "area"."id"%TYPE )
   2.283 +  RETURNS SETOF "initiative"."id"%TYPE
   2.284 +  LANGUAGE 'plpgsql' STABLE AS $$
   2.285 +    DECLARE
   2.286 +      "counter_v"         "member"."notification_counter"%TYPE;
   2.287 +      "sample_size_v"     "member"."notification_sample_size"%TYPE;
   2.288 +      "initiative_id_ary" INT4[];  --"initiative"."id"%TYPE[]
   2.289 +      "match_v"           BOOLEAN;
   2.290 +      "member_id_v"       "member"."id"%TYPE;
   2.291 +      "seed_v"            TEXT;
   2.292 +      "initiative_id_v"   "initiative"."id"%TYPE;
   2.293 +    BEGIN
   2.294 +      SELECT "notification_counter", "notification_sample_size"
   2.295 +        INTO "counter_v", "sample_size_v"
   2.296 +        FROM "member" WHERE "id" = "recipient_id_p";
   2.297 +      "initiative_id_ary" := '{}';
   2.298 +      LOOP
   2.299 +        "match_v" := FALSE;
   2.300 +        FOR "member_id_v", "seed_v" IN
   2.301 +          SELECT * FROM (
   2.302 +            SELECT DISTINCT
   2.303 +              "supporter"."member_id",
   2.304 +              md5(
   2.305 +                "recipient_id_p" || '-' ||
   2.306 +                "counter_v"      || '-' ||
   2.307 +                "area_id_p"      || '-' ||
   2.308 +                "supporter"."member_id"
   2.309 +              ) AS "seed"
   2.310 +            FROM "supporter"
   2.311 +            JOIN "initiative" ON "initiative"."id" = "supporter"."initiative_id"
   2.312 +            JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
   2.313 +            WHERE "supporter"."member_id" != "recipient_id_p"
   2.314 +            AND "issue"."area_id" = "area_id_p"
   2.315 +            AND "issue"."state" IN ('admission', 'discussion', 'verification')
   2.316 +          ) AS "subquery"
   2.317 +          ORDER BY "seed"
   2.318 +        LOOP
   2.319 +          SELECT "initiative"."id" INTO "initiative_id_v"
   2.320 +            FROM "initiative"
   2.321 +            JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
   2.322 +            JOIN "area" ON "area"."id" = "issue"."area_id"
   2.323 +            JOIN "supporter" ON "supporter"."initiative_id" = "initiative"."id"
   2.324 +            LEFT JOIN "supporter" AS "self_support" ON
   2.325 +              "self_support"."initiative_id" = "initiative"."id" AND
   2.326 +              "self_support"."member_id" = "recipient_id_p"
   2.327 +            LEFT JOIN "privilege" ON
   2.328 +              "privilege"."member_id" = "recipient_id_p" AND
   2.329 +              "privilege"."unit_id" = "area"."unit_id" AND
   2.330 +              "privilege"."voting_right" = TRUE
   2.331 +            LEFT JOIN "subscription" ON
   2.332 +              "subscription"."member_id" = "recipient_id_p" AND
   2.333 +              "subscription"."unit_id" = "area"."unit_id"
   2.334 +            LEFT JOIN "ignored_initiative" ON
   2.335 +              "ignored_initiative"."member_id" = "recipient_id_p" AND
   2.336 +              "ignored_initiative"."initiative_id" = "initiative"."id"
   2.337 +            WHERE "supporter"."member_id" = "member_id_v"
   2.338 +            AND "issue"."area_id" = "area_id_p"
   2.339 +            AND "issue"."state" IN ('admission', 'discussion', 'verification')
   2.340 +            AND "initiative"."revoked" ISNULL
   2.341 +            AND "self_support"."member_id" ISNULL
   2.342 +            AND NOT "initiative_id_ary" @> ARRAY["initiative"."id"]
   2.343 +            AND (
   2.344 +              "privilege"."member_id" NOTNULL OR
   2.345 +              "subscription"."member_id" NOTNULL )
   2.346 +            AND "ignored_initiative"."member_id" ISNULL
   2.347 +            AND NOT EXISTS (
   2.348 +              SELECT NULL FROM "draft"
   2.349 +              JOIN "ignored_member" ON
   2.350 +                "ignored_member"."member_id" = "recipient_id_p" AND
   2.351 +                "ignored_member"."other_member_id" = "draft"."author_id"
   2.352 +              WHERE "draft"."initiative_id" = "initiative"."id"
   2.353 +            )
   2.354 +            ORDER BY md5("seed_v" || '-' || "initiative"."id")
   2.355 +            LIMIT 1;
   2.356 +          IF FOUND THEN
   2.357 +            "match_v" := TRUE;
   2.358 +            RETURN NEXT "initiative_id_v";
   2.359 +            IF array_length("initiative_id_ary", 1) + 1 >= "sample_size_v" THEN
   2.360 +              RETURN;
   2.361 +            END IF;
   2.362 +            "initiative_id_ary" := "initiative_id_ary" || "initiative_id_v";
   2.363 +          END IF;
   2.364 +        END LOOP;
   2.365 +        EXIT WHEN NOT "match_v";
   2.366 +      END LOOP;
   2.367 +      RETURN;
   2.368 +    END;
   2.369 +  $$;
   2.370 +
   2.371 +COMMENT ON FUNCTION "featured_initiative"
   2.372 +  ( "recipient_id_p" "member"."id"%TYPE,
   2.373 +    "area_id_p"      "area"."id"%TYPE )
   2.374 +  IS 'Helper function for view "updated_or_featured_initiative"';
   2.375 +
   2.376 +CREATE VIEW "updated_or_featured_initiative" AS
   2.377 +  SELECT
   2.378 +    "subquery".*,
   2.379 +    NOT EXISTS (
   2.380 +      SELECT NULL FROM "initiative" AS "better_initiative"
   2.381 +      WHERE "better_initiative"."issue_id" = "initiative"."issue_id"
   2.382 +      AND
   2.383 +        ( COALESCE("better_initiative"."supporter_count", -1),
   2.384 +          -"better_initiative"."id" ) >
   2.385 +        ( COALESCE("initiative"."supporter_count", -1),
   2.386 +          -"initiative"."id" )
   2.387 +    ) AS "leading"
   2.388 +  FROM (
   2.389 +    SELECT * FROM "updated_initiative"
   2.390 +    UNION ALL
   2.391 +    SELECT
   2.392 +      "member"."id" AS "recipient_id",
   2.393 +      TRUE AS "featured",
   2.394 +      "featured_initiative_id" AS "initiative_id"
   2.395 +    FROM "member" CROSS JOIN "area"
   2.396 +    CROSS JOIN LATERAL
   2.397 +      "featured_initiative"("member"."id", "area"."id") AS "featured_initiative_id"
   2.398 +    JOIN "initiative" ON "initiative"."id" = "featured_initiative_id"
   2.399 +  ) AS "subquery"
   2.400 +  JOIN "initiative" ON "initiative"."id" = "subquery"."initiative_id";
   2.401 +
   2.402 +COMMENT ON VIEW "updated_or_featured_initiative" IS 'Initiatives to be included in a scheduled notification mail because (a) they have been updated or (b) they are featured';
   2.403 +
   2.404 +COMMENT ON COLUMN "updated_or_featured_initiative"."recipient_id" IS '"id" of the member who receives the notification mail';
   2.405 +COMMENT ON COLUMN "updated_or_featured_initiative"."featured" IS 'TRUE if the initiative has been included because it was selected by the "featured_initiative" algorithm (see source of function "featured_initiative")';
   2.406 +COMMENT ON COLUMN "updated_or_featured_initiative"."initiative_id" IS '"id" of the initiative to be included in the notification mail';
   2.407 +COMMENT ON COLUMN "updated_or_featured_initiative"."leading" IS 'TRUE if the initiative has the highest "supporter_count" in the issue';
   2.408 +
   2.409 +CREATE VIEW "leading_complement_initiative" AS
   2.410 +  SELECT * FROM (
   2.411 +    SELECT DISTINCT ON ("uf_initiative"."recipient_id", "initiative"."issue_id")
   2.412 +      "uf_initiative"."recipient_id",
   2.413 +      FALSE AS "featured",
   2.414 +      "uf_initiative"."initiative_id",
   2.415 +      TRUE AS "leading"
   2.416 +    FROM "updated_or_featured_initiative" AS "uf_initiative"
   2.417 +    JOIN "initiative" AS "uf_initiative_full" ON
   2.418 +      "uf_initiative_full"."id" = "uf_initiative"."initiative_id"
   2.419 +    JOIN "initiative" ON
   2.420 +      "initiative"."issue_id" = "uf_initiative_full"."issue_id"
   2.421 +    WHERE "initiative"."revoked" ISNULL
   2.422 +    ORDER BY
   2.423 +      "uf_initiative"."recipient_id",
   2.424 +      "initiative"."issue_id",
   2.425 +      "initiative"."supporter_count" DESC,
   2.426 +      "initiative"."id"
   2.427 +  ) AS "subquery"
   2.428 +  WHERE NOT EXISTS (
   2.429 +    SELECT NULL FROM "updated_or_featured_initiative" AS "other"
   2.430 +    WHERE "other"."recipient_id" = "subquery"."recipient_id"
   2.431 +    AND "other"."initiative_id" = "subquery"."initiative_id"
   2.432 +  );
   2.433 +
   2.434 +COMMENT ON VIEW "leading_complement_initiative" IS 'Helper view for view "unfiltered_initiative_for_notification" in order to always include the most supported initiative of an issue';
   2.435 +COMMENT ON COLUMN "leading_complement_initiative"."featured" IS 'Always FALSE in this view';
   2.436 +COMMENT ON COLUMN "leading_complement_initiative"."initiative_id" IS '"id" of the initiative to be included in the notification mail';
   2.437 +COMMENT ON COLUMN "leading_complement_initiative"."leading" IS 'Always TRUE in this view';
   2.438 +
   2.439 +CREATE VIEW "unfiltered_initiative_for_notification" AS
   2.440 +  SELECT
   2.441 +    "subquery".*,
   2.442 +    "supporter"."member_id" NOTNULL AS "supported",
   2.443 +    CASE WHEN "supporter"."member_id" NOTNULL THEN
   2.444 +      EXISTS (
   2.445 +        SELECT NULL FROM "draft"
   2.446 +        WHERE "draft"."initiative_id" = "subquery"."initiative_id"
   2.447 +        AND "draft"."id" > "supporter"."draft_id"
   2.448 +      )
   2.449 +    ELSE
   2.450 +      EXISTS (
   2.451 +        SELECT NULL FROM "draft"
   2.452 +        WHERE "draft"."initiative_id" = "subquery"."initiative_id"
   2.453 +        AND COALESCE("draft"."id" > "sent"."last_draft_id", TRUE)
   2.454 +      )
   2.455 +    END AS "new_draft",
   2.456 +    CASE WHEN "supporter"."member_id" NOTNULL THEN
   2.457 +      ( SELECT count(1) FROM "suggestion"
   2.458 +        LEFT JOIN "opinion" ON
   2.459 +          "opinion"."member_id" = "supporter"."member_id" AND
   2.460 +          "opinion"."suggestion_id" = "suggestion"."id"
   2.461 +        WHERE "suggestion"."initiative_id" = "subquery"."initiative_id"
   2.462 +        AND "opinion"."member_id" ISNULL
   2.463 +        AND COALESCE("suggestion"."id" > "sent"."last_suggestion_id", TRUE)
   2.464 +      )
   2.465 +    ELSE
   2.466 +      ( SELECT count(1) FROM "suggestion"
   2.467 +        WHERE "suggestion"."initiative_id" = "subquery"."initiative_id"
   2.468 +        AND COALESCE("suggestion"."id" > "sent"."last_suggestion_id", TRUE)
   2.469 +      )
   2.470 +    END AS "new_suggestion_count"
   2.471 +  FROM (
   2.472 +    SELECT * FROM "updated_or_featured_initiative"
   2.473 +    UNION ALL
   2.474 +    SELECT * FROM "leading_complement_initiative"
   2.475 +  ) AS "subquery"
   2.476 +  LEFT JOIN "supporter" ON
   2.477 +    "supporter"."member_id" = "subquery"."recipient_id" AND
   2.478 +    "supporter"."initiative_id" = "subquery"."initiative_id"
   2.479 +  LEFT JOIN "notification_initiative_sent" AS "sent" ON
   2.480 +    "sent"."member_id" = "subquery"."recipient_id" AND
   2.481 +    "sent"."initiative_id" = "subquery"."initiative_id";
   2.482 +
   2.483 +COMMENT ON VIEW "unfiltered_initiative_for_notification" IS 'Helper view which simply combines the views "updated_or_featured_initiative" and "leading_complement_initiative" and adds columns "supported", "new_draft", and "new_suggestion_count';
   2.484 +
   2.485 +COMMENT ON COLUMN "unfiltered_initiative_for_notification"."supported"            IS 'TRUE if initiative is supported by the recipient';
   2.486 +COMMENT ON COLUMN "unfiltered_initiative_for_notification"."new_draft"            IS 'TRUE if a new draft exists (using the "draft_id" column of the "supporter" table in case of "supported" initiatives and the "last_draft_id" column of the "notification_initiative_sent" table in all other cases)';
   2.487 +COMMENT ON COLUMN "unfiltered_initiative_for_notification"."new_suggestion_count" IS 'Number of new suggestions (using the "last_suggestion_id" column of the "notification_initiative_sent" table while ignoring suggestions with an "opinion")';
   2.488 +
   2.489 +CREATE VIEW "initiative_for_notification" AS
   2.490 +  SELECT "unfiltered1".*
   2.491 +  FROM "unfiltered_initiative_for_notification" "unfiltered1"
   2.492 +  JOIN "initiative" AS "initiative1" ON
   2.493 +    "initiative1"."id" = "unfiltered1"."initiative_id"
   2.494 +  JOIN "issue" AS "issue1" ON "issue1"."id" = "initiative1"."issue_id"
   2.495 +  WHERE EXISTS (
   2.496 +    SELECT NULL
   2.497 +    FROM "unfiltered_initiative_for_notification" "unfiltered2"
   2.498 +    JOIN "initiative" AS "initiative2" ON
   2.499 +      "initiative2"."id" = "unfiltered2"."initiative_id"
   2.500 +    JOIN "issue" AS "issue2" ON "issue2"."id" = "initiative2"."issue_id"
   2.501 +    WHERE "unfiltered1"."recipient_id" = "unfiltered2"."recipient_id"
   2.502 +    AND "issue1"."area_id" = "issue2"."area_id"
   2.503 +    AND ("unfiltered2"."new_draft" OR "unfiltered2"."new_suggestion_count" > 0 )
   2.504 +  );
   2.505 +
   2.506 +COMMENT ON VIEW "initiative_for_notification" IS 'Initiatives to be included in a scheduled notification mail';
   2.507 +
   2.508 +COMMENT ON COLUMN "initiative_for_notification"."recipient_id"         IS '"id" of the member who receives the notification mail';
   2.509 +COMMENT ON COLUMN "initiative_for_notification"."featured"             IS 'TRUE if the initiative has been included because it was selected by the "featured_initiative" algorithm (see source of function "featured_initiative")';
   2.510 +COMMENT ON COLUMN "initiative_for_notification"."initiative_id"        IS '"id" of the initiative to be included in the notification mail';
   2.511 +COMMENT ON COLUMN "initiative_for_notification"."leading"              IS 'TRUE if the initiative has the highest "supporter_count" in the issue';
   2.512 +COMMENT ON COLUMN "initiative_for_notification"."supported"            IS 'TRUE if initiative is supported by the recipient';
   2.513 +COMMENT ON COLUMN "initiative_for_notification"."new_draft"            IS 'TRUE if a new draft exists (using the "draft_id" column of the "supporter" table in case of "supported" initiatives and the "last_draft_id" column of the "notification_initiative_sent" table in all other cases)';
   2.514 +COMMENT ON COLUMN "initiative_for_notification"."new_suggestion_count" IS 'Number of new suggestions (using the "last_suggestion_id" column of the "notification_initiative_sent" table while ignoring suggestions with an "opinion")';
   2.515 +
   2.516 +CREATE VIEW "scheduled_notification_to_send" AS
   2.517 +  SELECT * FROM (
   2.518 +    SELECT
   2.519 +      "id" AS "recipient_id",
   2.520 +      now() - CASE WHEN "notification_dow" ISNULL THEN
   2.521 +        ( "notification_sent"::DATE + CASE
   2.522 +          WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour"
   2.523 +          THEN 0 ELSE 1 END
   2.524 +        )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour"
   2.525 +      ELSE
   2.526 +        ( "notification_sent"::DATE +
   2.527 +          ( 7 + "notification_dow" -
   2.528 +            EXTRACT(DOW FROM
   2.529 +              ( "notification_sent"::DATE + CASE
   2.530 +                WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour"
   2.531 +                THEN 0 ELSE 1 END
   2.532 +              )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour"
   2.533 +            )::INTEGER
   2.534 +          ) % 7 +
   2.535 +          CASE
   2.536 +            WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour"
   2.537 +            THEN 0 ELSE 1
   2.538 +          END
   2.539 +        )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour"
   2.540 +      END AS "pending"
   2.541 +    FROM (
   2.542 +      SELECT
   2.543 +        "id",
   2.544 +        COALESCE("notification_sent", "activated") AS "notification_sent",
   2.545 +        "notification_dow",
   2.546 +        "notification_hour"
   2.547 +      FROM "member"
   2.548 +      WHERE "disable_notifications" = FALSE
   2.549 +      AND "notification_hour" NOTNULL
   2.550 +    ) AS "subquery1"
   2.551 +  ) AS "subquery2"
   2.552 +  WHERE "pending" > '0'::INTERVAL;
   2.553 +
   2.554 +COMMENT ON VIEW "scheduled_notification_to_send" IS 'Set of members where a scheduled notification mail is pending';
   2.555 +
   2.556 +COMMENT ON COLUMN "scheduled_notification_to_send"."recipient_id" IS '"id" of the member who needs to receive a notification mail';
   2.557 +COMMENT ON COLUMN "scheduled_notification_to_send"."pending"      IS 'Duration for which the notification mail has already been pending';
   2.558 +
   2.559 +CREATE VIEW "newsletter_to_send" AS
   2.560 +  SELECT
   2.561 +    "member"."id" AS "recipient_id",
   2.562 +    "newsletter"."id" AS "newsletter_id",
   2.563 +    "newsletter"."published"
   2.564 +  FROM "newsletter" CROSS JOIN "member"
   2.565 +  LEFT JOIN "privilege" ON
   2.566 +    "privilege"."member_id" = "member"."id" AND
   2.567 +    "privilege"."unit_id" = "newsletter"."unit_id" AND
   2.568 +    "privilege"."voting_right" = TRUE
   2.569 +  LEFT JOIN "subscription" ON
   2.570 +    "subscription"."member_id" = "member"."id" AND
   2.571 +    "subscription"."unit_id" = "newsletter"."unit_id"
   2.572 +  WHERE "newsletter"."published" <= now()
   2.573 +  AND "newsletter"."sent" ISNULL
   2.574 +  AND "member"."locked" = FALSE
   2.575 +  AND (
   2.576 +    "member"."disable_notifications" = FALSE OR
   2.577 +    "newsletter"."include_all_members" = TRUE )
   2.578 +  AND (
   2.579 +    "newsletter"."unit_id" ISNULL OR
   2.580 +    "privilege"."member_id" NOTNULL OR
   2.581 +    "subscription"."member_id" NOTNULL );
   2.582 +
   2.583 +COMMENT ON VIEW "newsletter_to_send" IS 'List of "newsletter_id"s for each member that are due to be sent out';
   2.584 +COMMENT ON COLUMN "newsletter"."published" IS 'Timestamp when the newsletter was supposed to be sent out (can be used for ordering)';
   2.585 +
   2.586 +CREATE OR REPLACE FUNCTION "require_transaction_isolation"()
   2.587 +  RETURNS VOID
   2.588 +  LANGUAGE 'plpgsql' VOLATILE AS $$
   2.589 +    BEGIN
   2.590 +      IF
   2.591 +        current_setting('transaction_isolation') NOT IN
   2.592 +        ('repeatable read', 'serializable')
   2.593 +      THEN
   2.594 +        RAISE EXCEPTION 'Insufficient transaction isolation level' USING
   2.595 +          HINT = 'Consider using SET TRANSACTION ISOLATION LEVEL REPEATABLE READ.';
   2.596 +      END IF;
   2.597 +      RETURN;
   2.598 +    END;
   2.599 +  $$;
   2.600 + 
   2.601 +CREATE FUNCTION "get_initiatives_for_notification"
   2.602 +  ( "recipient_id_p" "member"."id"%TYPE )
   2.603 +  RETURNS SETOF "initiative_for_notification"
   2.604 +  LANGUAGE 'plpgsql' VOLATILE AS $$
   2.605 +    DECLARE
   2.606 +      "result_row"           "initiative_for_notification"%ROWTYPE;
   2.607 +      "last_draft_id_v"      "draft"."id"%TYPE;
   2.608 +      "last_suggestion_id_v" "suggestion"."id"%TYPE;
   2.609 +    BEGIN
   2.610 +      PERFORM "require_transaction_isolation"();
   2.611 +      PERFORM NULL FROM "member" WHERE "id" = "recipient_id_p" FOR UPDATE;
   2.612 +      FOR "result_row" IN
   2.613 +        SELECT * FROM "initiative_for_notification"
   2.614 +        WHERE "recipient_id" = "recipient_id_p"
   2.615 +      LOOP
   2.616 +        SELECT "id" INTO "last_draft_id_v" FROM "draft"
   2.617 +          WHERE "draft"."initiative_id" = "result_row"."initiative_id"
   2.618 +          ORDER BY "id" DESC LIMIT 1;
   2.619 +        SELECT "id" INTO "last_suggestion_id_v" FROM "suggestion"
   2.620 +          WHERE "suggestion"."initiative_id" = "result_row"."initiative_id"
   2.621 +          ORDER BY "id" DESC LIMIT 1;
   2.622 +        /* compatibility with PostgreSQL 9.1 */
   2.623 +        DELETE FROM "notification_initiative_sent"
   2.624 +          WHERE "member_id" = "recipient_id_p"
   2.625 +          AND "initiative_id" = "result_row"."initiative_id";
   2.626 +        INSERT INTO "notification_initiative_sent"
   2.627 +          ("member_id", "initiative_id", "last_draft_id", "last_suggestion_id")
   2.628 +          VALUES (
   2.629 +            "recipient_id_p",
   2.630 +            "result_row"."initiative_id",
   2.631 +            "last_draft_id_v",
   2.632 +            "last_suggestion_id_v" );
   2.633 +        /* TODO: use alternative code below, requires PostgreSQL 9.5 or higher
   2.634 +        INSERT INTO "notification_initiative_sent"
   2.635 +          ("member_id", "initiative_id", "last_draft_id", "last_suggestion_id")
   2.636 +          VALUES (
   2.637 +            "recipient_id_p",
   2.638 +            "result_row"."initiative_id",
   2.639 +            "last_draft_id_v",
   2.640 +            "last_suggestion_id_v" )
   2.641 +          ON CONFLICT ("member_id", "initiative_id") DO UPDATE SET
   2.642 +            "last_draft_id" = "last_draft_id_v",
   2.643 +            "last_suggestion_id" = "last_suggestion_id_v";
   2.644 +        */
   2.645 +        RETURN NEXT "result_row";
   2.646 +      END LOOP;
   2.647 +      DELETE FROM "notification_initiative_sent"
   2.648 +        USING "initiative", "issue"
   2.649 +        WHERE "notification_initiative_sent"."member_id" = "recipient_id_p"
   2.650 +        AND "initiative"."id" = "notification_initiative_sent"."initiative_id"
   2.651 +        AND "issue"."id" = "initiative"."issue_id"
   2.652 +        AND ( "issue"."closed" NOTNULL OR "issue"."fully_frozen" NOTNULL );
   2.653 +      UPDATE "member" SET
   2.654 +        "notification_counter" = "notification_counter" + 1,
   2.655 +        "notification_sent" = now()
   2.656 +        WHERE "id" = "recipient_id_p";
   2.657 +      RETURN;
   2.658 +    END;
   2.659 +  $$;
   2.660 +
   2.661 +COMMENT ON FUNCTION "get_initiatives_for_notification"
   2.662 +  ( "member"."id"%TYPE )
   2.663 +  IS 'Returns rows from view "initiative_for_notification" for a given recipient while updating table "notification_initiative_sent" and columns "notification_counter" and "notification_sent" of "member" table';
   2.664 +
   2.665 +CREATE OR REPLACE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)
   2.666 +  RETURNS VOID
   2.667 +  LANGUAGE 'plpgsql' VOLATILE AS $$
   2.668 +    BEGIN
   2.669 +      UPDATE "member" SET
   2.670 +        "last_login"                   = NULL,
   2.671 +        "last_delegation_check"        = NULL,
   2.672 +        "login"                        = NULL,
   2.673 +        "password"                     = NULL,
   2.674 +        "authority"                    = NULL,
   2.675 +        "authority_uid"                = NULL,
   2.676 +        "authority_login"              = NULL,
   2.677 +        "locked"                       = TRUE,
   2.678 +        "active"                       = FALSE,
   2.679 +        "notify_email"                 = NULL,
   2.680 +        "notify_email_unconfirmed"     = NULL,
   2.681 +        "notify_email_secret"          = NULL,
   2.682 +        "notify_email_secret_expiry"   = NULL,
   2.683 +        "notify_email_lock_expiry"     = NULL,
   2.684 +        "disable_notifications"        = NULL,
   2.685 +        "notification_counter"         = NULL,
   2.686 +        "notification_sample_size"     = NULL,
   2.687 +        "notification_dow"             = NULL,
   2.688 +        "notification_hour"            = NULL,
   2.689 +        "login_recovery_expiry"        = NULL,
   2.690 +        "password_reset_secret"        = NULL,
   2.691 +        "password_reset_secret_expiry" = NULL,
   2.692 +        "organizational_unit"          = NULL,
   2.693 +        "internal_posts"               = NULL,
   2.694 +        "realname"                     = NULL,
   2.695 +        "birthday"                     = NULL,
   2.696 +        "address"                      = NULL,
   2.697 +        "email"                        = NULL,
   2.698 +        "xmpp_address"                 = NULL,
   2.699 +        "website"                      = NULL,
   2.700 +        "phone"                        = NULL,
   2.701 +        "mobile_phone"                 = NULL,
   2.702 +        "profession"                   = NULL,
   2.703 +        "external_memberships"         = NULL,
   2.704 +        "external_posts"               = NULL,
   2.705 +        "statement"                    = NULL
   2.706 +        WHERE "id" = "member_id_p";
   2.707 +      -- "text_search_data" is updated by triggers
   2.708 +      DELETE FROM "setting"            WHERE "member_id" = "member_id_p";
   2.709 +      DELETE FROM "setting_map"        WHERE "member_id" = "member_id_p";
   2.710 +      DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p";
   2.711 +      DELETE FROM "member_image"       WHERE "member_id" = "member_id_p";
   2.712 +      DELETE FROM "contact"            WHERE "member_id" = "member_id_p";
   2.713 +      DELETE FROM "ignored_member"     WHERE "member_id" = "member_id_p";
   2.714 +      DELETE FROM "session"            WHERE "member_id" = "member_id_p";
   2.715 +      DELETE FROM "area_setting"       WHERE "member_id" = "member_id_p";
   2.716 +      DELETE FROM "issue_setting"      WHERE "member_id" = "member_id_p";
   2.717 +      DELETE FROM "ignored_initiative" WHERE "member_id" = "member_id_p";
   2.718 +      DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p";
   2.719 +      DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p";
   2.720 +      DELETE FROM "membership"         WHERE "member_id" = "member_id_p";
   2.721 +      DELETE FROM "delegation"         WHERE "truster_id" = "member_id_p";
   2.722 +      DELETE FROM "non_voter"          WHERE "member_id" = "member_id_p";
   2.723 +      DELETE FROM "direct_voter" USING "issue"
   2.724 +        WHERE "direct_voter"."issue_id" = "issue"."id"
   2.725 +        AND "issue"."closed" ISNULL
   2.726 +        AND "member_id" = "member_id_p";
   2.727 +      RETURN;
   2.728 +    END;
   2.729 +  $$;
   2.730 +
   2.731 +CREATE OR REPLACE FUNCTION "delete_private_data"()
   2.732 +  RETURNS VOID
   2.733 +  LANGUAGE 'plpgsql' VOLATILE AS $$
   2.734 +    BEGIN
   2.735 +      DELETE FROM "temporary_transaction_data";
   2.736 +      DELETE FROM "member" WHERE "activated" ISNULL;
   2.737 +      UPDATE "member" SET
   2.738 +        "invite_code"                  = NULL,
   2.739 +        "invite_code_expiry"           = NULL,
   2.740 +        "admin_comment"                = NULL,
   2.741 +        "last_login"                   = NULL,
   2.742 +        "last_delegation_check"        = NULL,
   2.743 +        "login"                        = NULL,
   2.744 +        "password"                     = NULL,
   2.745 +        "authority"                    = NULL,
   2.746 +        "authority_uid"                = NULL,
   2.747 +        "authority_login"              = NULL,
   2.748 +        "lang"                         = NULL,
   2.749 +        "notify_email"                 = NULL,
   2.750 +        "notify_email_unconfirmed"     = NULL,
   2.751 +        "notify_email_secret"          = NULL,
   2.752 +        "notify_email_secret_expiry"   = NULL,
   2.753 +        "notify_email_lock_expiry"     = NULL,
   2.754 +        "disable_notifications"        = NULL,
   2.755 +        "notification_counter"         = NULL,
   2.756 +        "notification_sample_size"     = NULL,
   2.757 +        "notification_dow"             = NULL,
   2.758 +        "notification_hour"            = NULL,
   2.759 +        "login_recovery_expiry"        = NULL,
   2.760 +        "password_reset_secret"        = NULL,
   2.761 +        "password_reset_secret_expiry" = NULL,
   2.762 +        "organizational_unit"          = NULL,
   2.763 +        "internal_posts"               = NULL,
   2.764 +        "realname"                     = NULL,
   2.765 +        "birthday"                     = NULL,
   2.766 +        "address"                      = NULL,
   2.767 +        "email"                        = NULL,
   2.768 +        "xmpp_address"                 = NULL,
   2.769 +        "website"                      = NULL,
   2.770 +        "phone"                        = NULL,
   2.771 +        "mobile_phone"                 = NULL,
   2.772 +        "profession"                   = NULL,
   2.773 +        "external_memberships"         = NULL,
   2.774 +        "external_posts"               = NULL,
   2.775 +        "formatting_engine"            = NULL,
   2.776 +        "statement"                    = NULL;
   2.777 +      -- "text_search_data" is updated by triggers
   2.778 +      DELETE FROM "setting";
   2.779 +      DELETE FROM "setting_map";
   2.780 +      DELETE FROM "member_relation_setting";
   2.781 +      DELETE FROM "member_image";
   2.782 +      DELETE FROM "contact";
   2.783 +      DELETE FROM "ignored_member";
   2.784 +      DELETE FROM "session";
   2.785 +      DELETE FROM "area_setting";
   2.786 +      DELETE FROM "issue_setting";
   2.787 +      DELETE FROM "ignored_initiative";
   2.788 +      DELETE FROM "initiative_setting";
   2.789 +      DELETE FROM "suggestion_setting";
   2.790 +      DELETE FROM "non_voter";
   2.791 +      DELETE FROM "direct_voter" USING "issue"
   2.792 +        WHERE "direct_voter"."issue_id" = "issue"."id"
   2.793 +        AND "issue"."closed" ISNULL;
   2.794 +      RETURN;
   2.795 +    END;
   2.796 +  $$;
   2.797 +
   2.798 +COMMIT;
     3.1 --- /dev/null	Thu Jan 01 00:00:00 1970 +0000
     3.2 +++ b/update/core-update.v3.2.0-v3.2.1.sql	Sat Apr 30 17:03:48 2016 +0200
     3.3 @@ -0,0 +1,67 @@
     3.4 +BEGIN;
     3.5 +
     3.6 +CREATE OR REPLACE VIEW "liquid_feedback_version" AS
     3.7 +  SELECT * FROM (VALUES ('3.2.1', 3, 2, 1))
     3.8 +  AS "subquery"("string", "major", "minor", "revision");
     3.9 +
    3.10 +CREATE OR REPLACE FUNCTION "get_initiatives_for_notification"
    3.11 +  ( "recipient_id_p" "member"."id"%TYPE )
    3.12 +  RETURNS SETOF "initiative_for_notification"
    3.13 +  LANGUAGE 'plpgsql' VOLATILE AS $$
    3.14 +    DECLARE
    3.15 +      "result_row"           "initiative_for_notification"%ROWTYPE;
    3.16 +      "last_draft_id_v"      "draft"."id"%TYPE;
    3.17 +      "last_suggestion_id_v" "suggestion"."id"%TYPE;
    3.18 +    BEGIN
    3.19 +      PERFORM "require_transaction_isolation"();
    3.20 +      PERFORM NULL FROM "member" WHERE "id" = "recipient_id_p" FOR UPDATE;
    3.21 +      FOR "result_row" IN
    3.22 +        SELECT * FROM "initiative_for_notification"
    3.23 +        WHERE "recipient_id" = "recipient_id_p"
    3.24 +      LOOP
    3.25 +        SELECT "id" INTO "last_draft_id_v" FROM "draft"
    3.26 +          WHERE "draft"."initiative_id" = "result_row"."initiative_id"
    3.27 +          ORDER BY "id" DESC LIMIT 1;
    3.28 +        SELECT "id" INTO "last_suggestion_id_v" FROM "suggestion"
    3.29 +          WHERE "suggestion"."initiative_id" = "result_row"."initiative_id"
    3.30 +          ORDER BY "id" DESC LIMIT 1;
    3.31 +        /* compatibility with PostgreSQL 9.1 */
    3.32 +        DELETE FROM "notification_initiative_sent"
    3.33 +          WHERE "member_id" = "recipient_id_p"
    3.34 +          AND "initiative_id" = "result_row"."initiative_id";
    3.35 +        INSERT INTO "notification_initiative_sent"
    3.36 +          ("member_id", "initiative_id", "last_draft_id", "last_suggestion_id")
    3.37 +          VALUES (
    3.38 +            "recipient_id_p",
    3.39 +            "result_row"."initiative_id",
    3.40 +            "last_draft_id_v",
    3.41 +            "last_suggestion_id_v" );
    3.42 +        /* TODO: use alternative code below, requires PostgreSQL 9.5 or higher
    3.43 +        INSERT INTO "notification_initiative_sent"
    3.44 +          ("member_id", "initiative_id", "last_draft_id", "last_suggestion_id")
    3.45 +          VALUES (
    3.46 +            "recipient_id_p",
    3.47 +            "result_row"."initiative_id",
    3.48 +            "last_draft_id_v",
    3.49 +            "last_suggestion_id_v" )
    3.50 +          ON CONFLICT ("member_id", "initiative_id") DO UPDATE SET
    3.51 +            "last_draft_id" = "last_draft_id_v",
    3.52 +            "last_suggestion_id" = "last_suggestion_id_v";
    3.53 +        */
    3.54 +        RETURN NEXT "result_row";
    3.55 +      END LOOP;
    3.56 +      DELETE FROM "notification_initiative_sent"
    3.57 +        USING "initiative", "issue"
    3.58 +        WHERE "notification_initiative_sent"."member_id" = "recipient_id_p"
    3.59 +        AND "initiative"."id" = "notification_initiative_sent"."initiative_id"
    3.60 +        AND "issue"."id" = "initiative"."issue_id"
    3.61 +        AND ( "issue"."closed" NOTNULL OR "issue"."fully_frozen" NOTNULL );
    3.62 +      UPDATE "member" SET
    3.63 +        "notification_counter" = "notification_counter" + 1,
    3.64 +        "notification_sent" = now()
    3.65 +        WHERE "id" = "recipient_id_p";
    3.66 +      RETURN;
    3.67 +    END;
    3.68 +  $$;
    3.69 +
    3.70 +COMMIT;

Impressum / About Us