liquid_feedback_core

changeset 499:bc4b590a8eec

Revised notification system; Dropped tables "selected_event_seen_by_member" and "event_seen_by_member"
author jbe
date Mon Apr 04 18:42:56 2016 +0200 (2016-04-04)
parents 10b90162e982
children c1a2954078d7
files core.sql update/core-update.v3.1.0-v3.2.0.sql
line diff
     1.1 --- a/core.sql	Sun Apr 03 20:57:44 2016 +0200
     1.2 +++ b/core.sql	Mon Apr 04 18:42:56 2016 +0200
     1.3 @@ -89,12 +89,6 @@
     1.4  COMMENT ON COLUMN "contingent"."initiative_limit" IS 'Number of new initiatives to be opened by each member within a given time frame';
     1.5  
     1.6  
     1.7 -CREATE TYPE "notify_level" AS ENUM
     1.8 -  ('none', 'voting', 'verification', 'discussion', 'all');
     1.9 -
    1.10 -COMMENT ON TYPE "notify_level" IS 'Level of notification: ''none'' = no notifications, ''voting'' = notifications about finished issues and issues in voting, ''verification'' = notifications about finished issues, issues in voting and verification phase, ''discussion'' = notifications about everything except issues in admission phase, ''all'' = notifications about everything';
    1.11 -
    1.12 -
    1.13  CREATE TABLE "member" (
    1.14          "id"                    SERIAL4         PRIMARY KEY,
    1.15          "created"               TIMESTAMPTZ     NOT NULL DEFAULT now(),
    1.16 @@ -2339,135 +2333,96 @@
    1.17  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.18  
    1.19  
    1.20 -CREATE VIEW "event_seen_by_member" AS
    1.21 +CREATE VIEW "event_for_notification" AS
    1.22    SELECT
    1.23 -    "member"."id" AS "seen_by_member_id",
    1.24 -    CASE WHEN "event"."state" IN (
    1.25 -      'voting',
    1.26 -      'finished_without_winner',
    1.27 -      'finished_with_winner'
    1.28 -    ) THEN
    1.29 -      'voting'::"notify_level"
    1.30 -    ELSE
    1.31 -      CASE WHEN "event"."state" IN (
    1.32 -        'verification',
    1.33 -        'canceled_after_revocation_during_verification',
    1.34 -        'canceled_no_initiative_admitted'
    1.35 -      ) THEN
    1.36 -        'verification'::"notify_level"
    1.37 -      ELSE
    1.38 -        CASE WHEN "event"."state" IN (
    1.39 -          'discussion',
    1.40 -          'canceled_after_revocation_during_discussion'
    1.41 -        ) THEN
    1.42 -          'discussion'::"notify_level"
    1.43 -        ELSE
    1.44 -          'all'::"notify_level"
    1.45 -        END
    1.46 -      END
    1.47 -    END AS "notify_level",
    1.48 +    "member"."id" AS "recipient_id",
    1.49      "event".*
    1.50    FROM "member" CROSS JOIN "event"
    1.51 -  LEFT JOIN "issue"
    1.52 -    ON "event"."issue_id" = "issue"."id"
    1.53 -  LEFT JOIN "membership"
    1.54 -    ON "member"."id" = "membership"."member_id"
    1.55 -    AND "issue"."area_id" = "membership"."area_id"
    1.56 -  LEFT JOIN "interest"
    1.57 -    ON "member"."id" = "interest"."member_id"
    1.58 -    AND "event"."issue_id" = "interest"."issue_id"
    1.59 -  LEFT JOIN "ignored_member"
    1.60 -    ON "member"."id" = "ignored_member"."member_id"
    1.61 -    AND "event"."member_id" = "ignored_member"."other_member_id"
    1.62 -  LEFT JOIN "ignored_initiative"
    1.63 -    ON "member"."id" = "ignored_initiative"."member_id"
    1.64 -    AND "event"."initiative_id" = "ignored_initiative"."initiative_id"
    1.65 -  WHERE (
    1.66 -    "interest"."member_id" NOTNULL OR
    1.67 -    ( "membership"."member_id" NOTNULL AND
    1.68 -      "event"."event" IN (
    1.69 -        'issue_state_changed',
    1.70 -        'initiative_created_in_new_issue',
    1.71 -        'initiative_created_in_existing_issue',
    1.72 -        'initiative_revoked' ) ) )
    1.73 -  AND "ignored_member"."member_id" ISNULL
    1.74 -  AND "ignored_initiative"."member_id" ISNULL;
    1.75 -
    1.76 -COMMENT ON VIEW "event_seen_by_member" IS 'Events as seen by a member, depending on its memberships, interests and support, but ignoring members "notify_level"';
    1.77 +  JOIN "issue" ON "issue"."id" = "event"."issue_id"
    1.78 +  JOIN "area" ON "area"."id" = "issue"."area_id"
    1.79 +  LEFT JOIN "privilege" ON
    1.80 +    "privilege"."member_id" = "member"."id" AND
    1.81 +    "privilege"."unit_id" = "area"."unit_id" AND
    1.82 +    "privilege"."voting_right" = TRUE
    1.83 +  LEFT JOIN "subscription" ON
    1.84 +    "subscription"."member_id" = "member"."id" AND
    1.85 +    "subscription"."unit_id" = "area"."unit_id"
    1.86 +  LEFT JOIN "ignored_area" ON
    1.87 +    "ignored_area"."member_id" = "member"."id" AND
    1.88 +    "ignored_area"."area_id" = "issue"."area_id"
    1.89 +  LEFT JOIN "interest" ON
    1.90 +    "interest"."member_id" = "member"."id" AND
    1.91 +    "interest"."issue_id" = "event"."issue_id"
    1.92 +  LEFT JOIN "supporter" ON
    1.93 +    "supporter"."member_id" = "member"."id" AND
    1.94 +    "supporter"."initiative_id" = "event"."initiative_id"
    1.95 +  WHERE ("privilege"."member_id" NOTNULL OR "subscription"."member_id" NOTNULL)
    1.96 +  AND ("ignored_area"."member_id" ISNULL OR "interest"."member_id" NOTNULL)
    1.97 +  AND (
    1.98 +    "event"."event" = 'issue_state_changed'::"event_type" OR
    1.99 +    ( "event"."event" = 'initiative_revoked'::"event_type" AND
   1.100 +      "supporter"."member_id" NOTNULL ) );
   1.101 +
   1.102 +COMMENT ON VIEW "event_for_notification" IS 'TODO: documentation';
   1.103  
   1.104  
   1.105  CREATE VIEW "updated_initiative" AS
   1.106    SELECT
   1.107 -    "supporter"."member_id" AS "seen_by_member_id",
   1.108 -    TRUE AS "supported",
   1.109 -    EXISTS (
   1.110 -      SELECT NULL FROM "draft"
   1.111 -      WHERE "draft"."initiative_id" = "initiative"."id"
   1.112 -      AND "draft"."id" > "supporter"."draft_id"
   1.113 -    ) AS "new_draft",
   1.114 -    ( SELECT count(1) FROM "suggestion"
   1.115 -      LEFT JOIN "opinion" ON
   1.116 -        "opinion"."member_id" = "supporter"."member_id" AND
   1.117 -        "opinion"."suggestion_id" = "suggestion"."id"
   1.118 -      WHERE "suggestion"."initiative_id" = "initiative"."id"
   1.119 -      AND "opinion"."member_id" ISNULL
   1.120 -      AND COALESCE(
   1.121 -        "suggestion"."id" > "sent"."last_suggestion_id",
   1.122 -        TRUE
   1.123 -      )
   1.124 -    ) AS "new_suggestion_count",
   1.125 +    "supporter"."member_id" AS "recipient_id",
   1.126      FALSE AS "featured",
   1.127 -    NOT EXISTS (
   1.128 -      SELECT NULL FROM "initiative" AS "better_initiative"
   1.129 -      WHERE
   1.130 -        "better_initiative"."issue_id" = "initiative"."issue_id"
   1.131 -      AND
   1.132 -        ( COALESCE("better_initiative"."harmonic_weight", -1),
   1.133 -          -"better_initiative"."id" ) >
   1.134 -        ( COALESCE("initiative"."harmonic_weight", -1),
   1.135 -          -"initiative"."id" )
   1.136 -    ) AS "leading",
   1.137 -    "initiative".*
   1.138 -  FROM "supporter" JOIN "initiative"
   1.139 -  ON "supporter"."initiative_id" = "initiative"."id"
   1.140 -  LEFT JOIN "initiative_notification_sent" AS "sent"
   1.141 -    ON "sent"."member_id" = "supporter"."member_id"
   1.142 -    AND "sent"."initiative_id" = "initiative"."id"
   1.143 +    "supporter"."initiative_id"
   1.144 +  FROM "supporter"
   1.145 +  JOIN "initiative" ON "supporter"."initiative_id" = "initiative"."id"
   1.146    JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
   1.147 +  LEFT JOIN "initiative_notification_sent" AS "sent" ON
   1.148 +    "sent"."member_id" = "supporter"."member_id" AND
   1.149 +    "sent"."initiative_id" = "supporter"."initiative_id"
   1.150 +  LEFT JOIN "ignored_initiative" ON
   1.151 +    "ignored_initiative"."member_id" = "supporter"."member_id" AND
   1.152 +    "ignored_initiative"."initiative_id" = "supporter"."initiative_id"
   1.153    WHERE "issue"."state" IN ('admission', 'discussion')
   1.154 +  AND "ignored_initiative"."member_id" ISNULL
   1.155    AND (
   1.156      EXISTS (
   1.157        SELECT NULL FROM "draft"
   1.158 -      WHERE "draft"."initiative_id" = "initiative"."id"
   1.159 +      LEFT JOIN "ignored_member" ON
   1.160 +        "ignored_member"."member_id" = "supporter"."member_id" AND
   1.161 +        "ignored_member"."other_member_id" = "draft"."author_id"
   1.162 +      WHERE "draft"."initiative_id" = "supporter"."initiative_id"
   1.163        AND "draft"."id" > "supporter"."draft_id"
   1.164 +      AND "ignored_member"."member_id" ISNULL
   1.165      ) OR EXISTS (
   1.166        SELECT NULL FROM "suggestion"
   1.167        LEFT JOIN "opinion" ON
   1.168          "opinion"."member_id" = "supporter"."member_id" AND
   1.169          "opinion"."suggestion_id" = "suggestion"."id"
   1.170 -      WHERE "suggestion"."initiative_id" = "initiative"."id"
   1.171 +      LEFT JOIN "ignored_member" ON
   1.172 +        "ignored_member"."member_id" = "supporter"."member_id" AND
   1.173 +        "ignored_member"."other_member_id" = "suggestion"."author_id"
   1.174 +      WHERE "suggestion"."initiative_id" = "supporter"."initiative_id"
   1.175        AND "opinion"."member_id" ISNULL
   1.176 -      AND COALESCE(
   1.177 -        "suggestion"."id" > "sent"."last_suggestion_id",
   1.178 -        TRUE
   1.179 -      )
   1.180 +      AND COALESCE("suggestion"."id" > "sent"."last_suggestion_id", TRUE)
   1.181 +      AND "ignored_member"."member_id" ISNULL
   1.182      )
   1.183    );
   1.184  
   1.185  CREATE FUNCTION "featured_initiative"
   1.186 -  ( "member_id_p" "member"."id"%TYPE,
   1.187 -    "area_id_p"   "area"."id"%TYPE )
   1.188 -  RETURNS SETOF "initiative"
   1.189 +  ( "recipient_id_p" "member"."id"%TYPE,
   1.190 +    "area_id_p"      "area"."id"%TYPE )
   1.191 +  RETURNS SETOF "initiative"."id"%TYPE
   1.192    LANGUAGE 'plpgsql' STABLE AS $$
   1.193      DECLARE
   1.194 -      "member_row"        "member"%ROWTYPE;
   1.195 +      "counter_v"         "member"."notification_counter"%TYPE;
   1.196 +      "sample_size_v"     "member"."notification_sample_size"%TYPE;
   1.197 +      "initiative_id_ary" INT4[];  --"initiative"."id"%TYPE[]
   1.198 +      "match_v"           BOOLEAN;
   1.199        "member_id_v"       "member"."id"%TYPE;
   1.200        "seed_v"            TEXT;
   1.201 -      "result_row"        "initiative"%ROWTYPE;
   1.202 -      "match_v"           BOOLEAN;
   1.203 -      "initiative_id_ary" INT4[];  --"initiative"."id"%TYPE[]
   1.204 +      "initiative_id_v"   "initiative"."id"%TYPE;
   1.205      BEGIN
   1.206 -      SELECT INTO "member_row" * FROM "member" WHERE "id" = "member_id_p";
   1.207 +      SELECT "notification_counter", "notification_sample_size"
   1.208 +        INTO "counter_v", "sample_size_v"
   1.209 +        FROM "member" WHERE "id" = "recipient_id_p";
   1.210        "initiative_id_ary" := '{}';
   1.211        LOOP
   1.212          "match_v" := FALSE;
   1.213 @@ -2475,38 +2430,64 @@
   1.214            SELECT * FROM (
   1.215              SELECT DISTINCT
   1.216                "supporter"."member_id",
   1.217 -              md5("member_id_p" || '-' || "member_row"."notification_counter" || '-' || "area_id_p" || '-' || "supporter"."member_id") AS "seed"
   1.218 +              md5(
   1.219 +                "recipient_id_p" || '-' ||
   1.220 +                "counter_v"      || '-' ||
   1.221 +                "area_id_p"      || '-' ||
   1.222 +                "supporter"."member_id"
   1.223 +              ) AS "seed"
   1.224              FROM "supporter"
   1.225 -            JOIN "member" ON "member"."id" = "supporter"."member_id"
   1.226              JOIN "initiative" ON "initiative"."id" = "supporter"."initiative_id"
   1.227              JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
   1.228 -            WHERE "supporter"."member_id" != "member_id_p"
   1.229 +            WHERE "supporter"."member_id" != "recipient_id_p"
   1.230              AND "issue"."area_id" = "area_id_p"
   1.231              AND "issue"."state" IN ('admission', 'discussion', 'verification')
   1.232            ) AS "subquery"
   1.233            ORDER BY "seed"
   1.234          LOOP
   1.235 -          SELECT "initiative".* INTO "result_row"
   1.236 +          SELECT "initiative"."id" INTO "initiative_id_v"
   1.237              FROM "initiative"
   1.238              JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
   1.239 +            JOIN "area" ON "area"."id" = "issue"."area_id"
   1.240              JOIN "supporter" ON "supporter"."initiative_id" = "initiative"."id"
   1.241              LEFT JOIN "supporter" AS "self_support" ON
   1.242                "self_support"."initiative_id" = "initiative"."id" AND
   1.243 -              "self_support"."member_id" = "member_id_p"
   1.244 +              "self_support"."member_id" = "recipient_id_p"
   1.245 +            LEFT JOIN "privilege" ON
   1.246 +              "privilege"."member_id" = "recipient_id_p" AND
   1.247 +              "privilege"."unit_id" = "area"."unit_id" AND
   1.248 +              "privilege"."voting_right" = TRUE
   1.249 +            LEFT JOIN "subscription" ON
   1.250 +              "subscription"."member_id" = "recipient_id_p" AND
   1.251 +              "subscription"."unit_id" = "area"."unit_id"
   1.252 +            LEFT JOIN "ignored_initiative" ON
   1.253 +              "ignored_initiative"."member_id" = "recipient_id_p" AND
   1.254 +              "ignored_initiative"."initiative_id" = "initiative"."id"
   1.255              WHERE "supporter"."member_id" = "member_id_v"
   1.256              AND "issue"."area_id" = "area_id_p"
   1.257              AND "issue"."state" IN ('admission', 'discussion', 'verification')
   1.258              AND "self_support"."member_id" ISNULL
   1.259              AND NOT "initiative_id_ary" @> ARRAY["initiative"."id"]
   1.260 +            AND (
   1.261 +              "privilege"."member_id" NOTNULL OR
   1.262 +              "subscription"."member_id" NOTNULL )
   1.263 +            AND "ignored_initiative"."member_id" ISNULL
   1.264 +            AND NOT EXISTS (
   1.265 +              SELECT NULL FROM "draft"
   1.266 +              JOIN "ignored_member" ON
   1.267 +                "ignored_member"."member_id" = "recipient_id_p" AND
   1.268 +                "ignored_member"."other_member_id" = "draft"."author_id"
   1.269 +              WHERE "draft"."initiative_id" = "initiative"."id"
   1.270 +            )
   1.271              ORDER BY md5("seed_v" || '-' || "initiative"."id")
   1.272              LIMIT 1;
   1.273            IF FOUND THEN
   1.274              "match_v" := TRUE;
   1.275 -            "initiative_id_ary" := "initiative_id_ary" || "result_row"."id";
   1.276 -            RETURN NEXT "result_row";
   1.277 -            IF array_length("initiative_id_ary", 1) >= "member_row"."notification_sample_size" THEN
   1.278 +            RETURN NEXT "initiative_id_v";
   1.279 +            IF array_length("initiative_id_ary", 1) + 1 >= "sample_size_v" THEN
   1.280                RETURN;
   1.281              END IF;
   1.282 +            "initiative_id_ary" := "initiative_id_ary" || "initiative_id_v";
   1.283            END IF;
   1.284          END LOOP;
   1.285          EXIT WHEN NOT "match_v";
   1.286 @@ -2516,115 +2497,120 @@
   1.287    $$;
   1.288  
   1.289  CREATE VIEW "updated_or_featured_initiative" AS
   1.290 -  SELECT * FROM "updated_initiative"
   1.291 -  UNION ALL
   1.292    SELECT
   1.293 -    "member"."id" AS "seen_by_member_id",
   1.294 -    FALSE AS "supported",
   1.295 -    EXISTS (
   1.296 -      SELECT NULL FROM "draft"
   1.297 -      WHERE "draft"."initiative_id" = "initiative"."id"
   1.298 -      AND COALESCE(
   1.299 -        "draft"."id" > "sent"."last_draft_id",
   1.300 -        TRUE
   1.301 -      )
   1.302 -    ) AS "new_draft",
   1.303 -    ( SELECT count(1) FROM "suggestion"
   1.304 -      WHERE "suggestion"."initiative_id" = "initiative"."id"
   1.305 -      AND COALESCE(
   1.306 -        "suggestion"."id" > "sent"."last_suggestion_id",
   1.307 -        TRUE
   1.308 -      )
   1.309 -    ) AS "new_suggestion_count",
   1.310 -    TRUE AS "featured",
   1.311 +    "subquery".*,
   1.312      NOT EXISTS (
   1.313        SELECT NULL FROM "initiative" AS "better_initiative"
   1.314 -      WHERE
   1.315 -        "better_initiative"."issue_id" = "initiative"."issue_id"
   1.316 +      WHERE "better_initiative"."issue_id" = "initiative"."issue_id"
   1.317        AND
   1.318          ( COALESCE("better_initiative"."harmonic_weight", -1),
   1.319            -"better_initiative"."id" ) >
   1.320          ( COALESCE("initiative"."harmonic_weight", -1),
   1.321            -"initiative"."id" )
   1.322 -    ) AS "leading",
   1.323 -    "initiative".*
   1.324 -  FROM "member" CROSS JOIN "area"
   1.325 -  CROSS JOIN LATERAL
   1.326 -    "featured_initiative"("member"."id", "area"."id") AS "initiative"
   1.327 -  LEFT JOIN "initiative_notification_sent" AS "sent"
   1.328 -    ON "sent"."member_id" = "member"."id"
   1.329 -    AND "sent"."initiative_id" = "initiative"."id";
   1.330 +    ) AS "leading"
   1.331 +  FROM (
   1.332 +    SELECT * FROM "updated_initiative"
   1.333 +    UNION ALL
   1.334 +    SELECT
   1.335 +      "member"."id" AS "recipient_id",
   1.336 +      TRUE AS "featured",
   1.337 +      "featured_initiative_id" AS "initiative_id"
   1.338 +    FROM "member" CROSS JOIN "area"
   1.339 +    CROSS JOIN LATERAL
   1.340 +      "featured_initiative"("member"."id", "area"."id") AS "featured_initiative_id"
   1.341 +    JOIN "initiative" ON "initiative"."id" = "featured_initiative_id"
   1.342 +  ) AS "subquery"
   1.343 +  JOIN "initiative" ON "initiative"."id" = "subquery"."initiative_id";
   1.344  
   1.345  CREATE VIEW "leading_complement_initiative" AS
   1.346    SELECT * FROM (
   1.347 -    SELECT DISTINCT ON ("seen_by_member_id", "initiative"."issue_id")
   1.348 -      "uf_initiative"."seen_by_member_id",
   1.349 -      "supporter"."member_id" NOTNULL AS "supported",
   1.350 -      CASE WHEN "supporter"."member_id" NOTNULL THEN FALSE ELSE
   1.351 -        EXISTS (
   1.352 -          SELECT NULL FROM "draft"
   1.353 -          WHERE "draft"."initiative_id" = "initiative"."id"
   1.354 -          AND COALESCE(
   1.355 -            "draft"."id" > "sent"."last_draft_id",
   1.356 -            TRUE
   1.357 -          )
   1.358 -        )
   1.359 -      END AS "new_draft",
   1.360 -      CASE WHEN "supporter"."member_id" NOTNULL THEN 0 ELSE
   1.361 -        ( SELECT count(1) FROM "suggestion"
   1.362 -          WHERE "suggestion"."initiative_id" = "initiative"."id"
   1.363 -          AND COALESCE(
   1.364 -            "suggestion"."id" > "sent"."last_suggestion_id",
   1.365 -            TRUE
   1.366 -          )
   1.367 -        )
   1.368 -      END AS "new_suggestion_count",
   1.369 +    SELECT DISTINCT ON ("uf_initiative"."recipient_id", "initiative"."issue_id")
   1.370 +      "uf_initiative"."recipient_id",
   1.371        FALSE AS "featured",
   1.372 -      TRUE AS "leading",
   1.373 -      "initiative".*
   1.374 +      "uf_initiative"."initiative_id",
   1.375 +      TRUE AS "leading"
   1.376      FROM "updated_or_featured_initiative" AS "uf_initiative"
   1.377 +    JOIN "initiative" AS "uf_initiative_full" ON
   1.378 +      "uf_initiative_full"."id" = "uf_initiative"."initiative_id"
   1.379      JOIN "initiative" ON
   1.380 -      "uf_initiative"."issue_id" = "initiative"."issue_id"
   1.381 -    LEFT JOIN "supporter" ON
   1.382 -      "supporter"."member_id" = "uf_initiative"."seen_by_member_id" AND
   1.383 -      "supporter"."initiative_id" = "initiative"."id"
   1.384 -    LEFT JOIN "initiative_notification_sent" AS "sent"
   1.385 -      ON "sent"."member_id" = "uf_initiative"."seen_by_member_id"
   1.386 -      AND "sent"."initiative_id" = "initiative"."id"
   1.387 +      "initiative"."issue_id" = "uf_initiative_full"."issue_id"
   1.388      ORDER BY
   1.389 -      "seen_by_member_id",
   1.390 +      "uf_initiative"."recipient_id",
   1.391        "initiative"."issue_id",
   1.392        "initiative"."harmonic_weight" DESC,
   1.393        "initiative"."id"
   1.394    ) AS "subquery"
   1.395    WHERE NOT EXISTS (
   1.396      SELECT NULL FROM "updated_or_featured_initiative" AS "other"
   1.397 -    WHERE "other"."seen_by_member_id" = "subquery"."seen_by_member_id"
   1.398 -    AND "other"."id" = "subquery"."id"
   1.399 +    WHERE "other"."recipient_id" = "subquery"."recipient_id"
   1.400 +    AND "other"."initiative_id" = "subquery"."initiative_id"
   1.401    );
   1.402  
   1.403  CREATE VIEW "unfiltered_initiative_for_notification" AS
   1.404 -  SELECT * FROM "updated_or_featured_initiative"
   1.405 -  UNION ALL
   1.406 -  SELECT * FROM "leading_complement_initiative";
   1.407 +  SELECT
   1.408 +    "subquery".*,
   1.409 +    "supporter"."member_id" NOTNULL AS "supported",
   1.410 +    CASE WHEN "supporter"."member_id" NOTNULL THEN
   1.411 +      EXISTS (
   1.412 +        SELECT NULL FROM "draft"
   1.413 +        WHERE "draft"."initiative_id" = "subquery"."initiative_id"
   1.414 +        AND "draft"."id" > "supporter"."draft_id"
   1.415 +      )
   1.416 +    ELSE
   1.417 +      EXISTS (
   1.418 +        SELECT NULL FROM "draft"
   1.419 +        WHERE "draft"."initiative_id" = "subquery"."initiative_id"
   1.420 +        AND COALESCE("draft"."id" > "sent"."last_draft_id", TRUE)
   1.421 +      )
   1.422 +    END AS "new_draft",
   1.423 +    CASE WHEN "supporter"."member_id" NOTNULL THEN
   1.424 +      ( SELECT count(1) FROM "suggestion"
   1.425 +        LEFT JOIN "opinion" ON
   1.426 +          "opinion"."member_id" = "supporter"."member_id" AND
   1.427 +          "opinion"."suggestion_id" = "suggestion"."id"
   1.428 +        WHERE "suggestion"."initiative_id" = "subquery"."initiative_id"
   1.429 +        AND "opinion"."member_id" ISNULL
   1.430 +        AND COALESCE("suggestion"."id" > "sent"."last_suggestion_id", TRUE)
   1.431 +      )
   1.432 +    ELSE
   1.433 +      ( SELECT count(1) FROM "suggestion"
   1.434 +        WHERE "suggestion"."initiative_id" = "subquery"."initiative_id"
   1.435 +        AND COALESCE("suggestion"."id" > "sent"."last_suggestion_id", TRUE)
   1.436 +      )
   1.437 +    END AS "new_suggestion_count"
   1.438 +  FROM (
   1.439 +    SELECT * FROM "updated_or_featured_initiative"
   1.440 +    UNION ALL
   1.441 +    SELECT * FROM "leading_complement_initiative"
   1.442 +  ) AS "subquery"
   1.443 +  LEFT JOIN "supporter" ON
   1.444 +    "supporter"."member_id" = "subquery"."recipient_id" AND
   1.445 +    "supporter"."initiative_id" = "subquery"."initiative_id"
   1.446 +  LEFT JOIN "initiative_notification_sent" AS "sent" ON
   1.447 +    "sent"."member_id" = "subquery"."recipient_id" AND
   1.448 +    "sent"."initiative_id" = "subquery"."initiative_id";
   1.449  
   1.450  CREATE VIEW "initiative_for_notification" AS
   1.451 -  SELECT "initiative1".*
   1.452 -  FROM "unfiltered_initiative_for_notification" "initiative1"
   1.453 -  JOIN "issue" AS "issue1" ON "initiative1"."issue_id" = "issue1"."id"
   1.454 +  SELECT "unfiltered1".*
   1.455 +  FROM "unfiltered_initiative_for_notification" "unfiltered1"
   1.456 +  JOIN "initiative" AS "initiative1" ON
   1.457 +    "initiative1"."id" = "unfiltered1"."initiative_id"
   1.458 +  JOIN "issue" AS "issue1" ON "issue1"."id" = "initiative1"."issue_id"
   1.459    WHERE EXISTS (
   1.460      SELECT NULL
   1.461 -    FROM "unfiltered_initiative_for_notification" "initiative2"
   1.462 -    JOIN "issue" AS "issue2" ON "initiative2"."issue_id" = "issue2"."id"
   1.463 -    WHERE "initiative1"."seen_by_member_id" = "initiative2"."seen_by_member_id"
   1.464 +    FROM "unfiltered_initiative_for_notification" "unfiltered2"
   1.465 +    JOIN "initiative" AS "initiative2" ON
   1.466 +      "initiative2"."id" = "unfiltered2"."initiative_id"
   1.467 +    JOIN "issue" AS "issue2" ON "issue2"."id" = "initiative2"."issue_id"
   1.468 +    WHERE "unfiltered1"."recipient_id" = "unfiltered2"."recipient_id"
   1.469      AND "issue1"."area_id" = "issue2"."area_id"
   1.470 -    AND ( "initiative2"."new_draft" OR "initiative2"."new_suggestion_count" > 0 )
   1.471 +    AND ("unfiltered2"."new_draft" OR "unfiltered2"."new_suggestion_count" > 0 )
   1.472    );
   1.473  
   1.474  CREATE VIEW "newsletter_to_send" AS
   1.475    SELECT
   1.476 -    "newsletter"."id" AS "newsletter_id",
   1.477 -    "member"."id" AS "member_id"
   1.478 +    "member"."id" AS "recipient_id",
   1.479 +    "newsletter"."id" AS "newsletter_id"
   1.480    FROM "newsletter" CROSS JOIN "member"
   1.481    LEFT JOIN "privilege" ON
   1.482      "privilege"."member_id" = "member"."id" AND
   1.483 @@ -3146,19 +3132,19 @@
   1.484        PERFORM NULL FROM "member" WHERE "id" = "member_id_p" FOR UPDATE;
   1.485        FOR "result_row" IN
   1.486          SELECT * FROM "initiative_for_notification"
   1.487 -        WHERE "seen_by_member_id" = "member_id_p"
   1.488 +        WHERE "member_id" = "member_id_p"
   1.489        LOOP
   1.490          SELECT "id" INTO "last_draft_id_v" FROM "draft"
   1.491 -          WHERE "draft"."initiative_id" = "result_row"."id"
   1.492 +          WHERE "draft"."initiative_id" = "result_row"."initiative_id"
   1.493            ORDER BY "id" DESC LIMIT 1;
   1.494          SELECT "id" INTO "last_suggestion_id_v" FROM "suggestion"
   1.495 -          WHERE "suggestion"."initiative_id" = "result_row"."id"
   1.496 +          WHERE "suggestion"."initiative_id" = "result_row"."initiative_id"
   1.497            ORDER BY "id" DESC LIMIT 1;
   1.498          INSERT INTO "initiative_notification_sent"
   1.499            ("member_id", "initiative_id", "last_draft_id", "last_suggestion_id")
   1.500            VALUES (
   1.501              "member_id_p",
   1.502 -            "result_row"."id",
   1.503 +            "result_row"."initiative_id",
   1.504              "last_draft_id_v",
   1.505              "last_suggestion_id_v" )
   1.506            ON CONFLICT ("member_id", "initiative_id") DO UPDATE SET
   1.507 @@ -4979,6 +4965,11 @@
   1.508          "notify_email_secret"          = NULL,
   1.509          "notify_email_secret_expiry"   = NULL,
   1.510          "notify_email_lock_expiry"     = NULL,
   1.511 +        "disable_notifications"        = NULL,
   1.512 +        "notification_counter"         = NULL,
   1.513 +        "notification_sample_size"     = NULL,
   1.514 +        "notification_dow"             = NULL,
   1.515 +        "notification_hour"            = NULL,
   1.516          "login_recovery_expiry"        = NULL,
   1.517          "password_reset_secret"        = NULL,
   1.518          "password_reset_secret_expiry" = NULL,
   1.519 @@ -5047,7 +5038,11 @@
   1.520          "notify_email_secret"          = NULL,
   1.521          "notify_email_secret_expiry"   = NULL,
   1.522          "notify_email_lock_expiry"     = NULL,
   1.523 -        "notify_level"                 = NULL,
   1.524 +        "disable_notifications"        = NULL,
   1.525 +        "notification_counter"         = NULL,
   1.526 +        "notification_sample_size"     = NULL,
   1.527 +        "notification_dow"             = NULL,
   1.528 +        "notification_hour"            = NULL,
   1.529          "login_recovery_expiry"        = NULL,
   1.530          "password_reset_secret"        = NULL,
   1.531          "password_reset_secret_expiry" = NULL,
     2.1 --- a/update/core-update.v3.1.0-v3.2.0.sql	Sun Apr 03 20:57:44 2016 +0200
     2.2 +++ b/update/core-update.v3.1.0-v3.2.0.sql	Mon Apr 04 18:42:56 2016 +0200
     2.3 @@ -4,6 +4,8 @@
     2.4    SELECT * FROM (VALUES ('3.2.0', 3, 2, 0))
     2.5    AS "subquery"("string", "major", "minor", "revision");
     2.6  
     2.7 +-- TODO: preliminary script
     2.8 +
     2.9  ALTER TABLE "member" ADD COLUMN "disable_notifications" BOOLEAN NOT NULL DEFAULT FALSE;
    2.10  ALTER TABLE "member" ADD COLUMN "notification_counter" INT4 NOT NULL DEFAULT 1;
    2.11  ALTER TABLE "member" ADD COLUMN "notification_sample_size" INT4 NOT NULL DEFAULT 3;
    2.12 @@ -11,6 +13,11 @@
    2.13  ALTER TABLE "member" ADD COLUMN "notification_hour" INT4 CHECK ("notification_hour" BETWEEN 0 AND 23);
    2.14  
    2.15  UPDATE "member" SET "disable_notifications" = TRUE WHERE "notify_level" = 'none';
    2.16 +
    2.17 +DROP TABLE "selected_event_seen_by_member";
    2.18 +DROP TABLE "event_seen_by_member";
    2.19 +ALTER TABLE "member" DROP COLUMN "notify_level";
    2.20 +DROP TYPE "notify_level";
    2.21   
    2.22  CREATE TABLE "subscription" (
    2.23          PRIMARY KEY ("member_id", "unit_id"),
    2.24 @@ -48,78 +55,93 @@
    2.25  CREATE INDEX "newsletter_all_units_published_idx" ON "newsletter" ("published") WHERE "unit_id" ISNULL;
    2.26  CREATE INDEX "newsletter_published_idx" ON "newsletter" ("published");
    2.27  
    2.28 +CREATE VIEW "event_for_notification" AS
    2.29 +  SELECT
    2.30 +    "member"."id" AS "recipient_id",
    2.31 +    "event".*
    2.32 +  FROM "member" CROSS JOIN "event"
    2.33 +  JOIN "issue" ON "issue"."id" = "event"."issue_id"
    2.34 +  JOIN "area" ON "area"."id" = "issue"."area_id"
    2.35 +  LEFT JOIN "privilege" ON
    2.36 +    "privilege"."member_id" = "member"."id" AND
    2.37 +    "privilege"."unit_id" = "area"."unit_id" AND
    2.38 +    "privilege"."voting_right" = TRUE
    2.39 +  LEFT JOIN "subscription" ON
    2.40 +    "subscription"."member_id" = "member"."id" AND
    2.41 +    "subscription"."unit_id" = "area"."unit_id"
    2.42 +  LEFT JOIN "ignored_area" ON
    2.43 +    "ignored_area"."member_id" = "member"."id" AND
    2.44 +    "ignored_area"."area_id" = "issue"."area_id"
    2.45 +  LEFT JOIN "interest" ON
    2.46 +    "interest"."member_id" = "member"."id" AND
    2.47 +    "interest"."issue_id" = "event"."issue_id"
    2.48 +  LEFT JOIN "supporter" ON
    2.49 +    "supporter"."member_id" = "member"."id" AND
    2.50 +    "supporter"."initiative_id" = "event"."initiative_id"
    2.51 +  WHERE ("privilege"."member_id" NOTNULL OR "subscription"."member_id" NOTNULL)
    2.52 +  AND ("ignored_area"."member_id" ISNULL OR "interest"."member_id" NOTNULL)
    2.53 +  AND (
    2.54 +    "event"."event" = 'issue_state_changed'::"event_type" OR
    2.55 +    ( "event"."event" = 'initiative_revoked'::"event_type" AND
    2.56 +      "supporter"."member_id" NOTNULL ) );
    2.57 +
    2.58  CREATE VIEW "updated_initiative" AS
    2.59    SELECT
    2.60 -    "supporter"."member_id" AS "seen_by_member_id",
    2.61 -    TRUE AS "supported",
    2.62 -    EXISTS (
    2.63 -      SELECT NULL FROM "draft"
    2.64 -      WHERE "draft"."initiative_id" = "initiative"."id"
    2.65 -      AND "draft"."id" > "supporter"."draft_id"
    2.66 -    ) AS "new_draft",
    2.67 -    ( SELECT count(1) FROM "suggestion"
    2.68 -      LEFT JOIN "opinion" ON
    2.69 -        "opinion"."member_id" = "supporter"."member_id" AND
    2.70 -        "opinion"."suggestion_id" = "suggestion"."id"
    2.71 -      WHERE "suggestion"."initiative_id" = "initiative"."id"
    2.72 -      AND "opinion"."member_id" ISNULL
    2.73 -      AND COALESCE(
    2.74 -        "suggestion"."id" > "sent"."last_suggestion_id",
    2.75 -        TRUE
    2.76 -      )
    2.77 -    ) AS "new_suggestion_count",
    2.78 +    "supporter"."member_id" AS "recipient_id",
    2.79      FALSE AS "featured",
    2.80 -    NOT EXISTS (
    2.81 -      SELECT NULL FROM "initiative" AS "better_initiative"
    2.82 -      WHERE
    2.83 -        "better_initiative"."issue_id" = "initiative"."issue_id"
    2.84 -      AND
    2.85 -        ( COALESCE("better_initiative"."harmonic_weight", -1),
    2.86 -          -"better_initiative"."id" ) >
    2.87 -        ( COALESCE("initiative"."harmonic_weight", -1),
    2.88 -          -"initiative"."id" )
    2.89 -    ) AS "leading",
    2.90 -    "initiative".*
    2.91 -  FROM "supporter" JOIN "initiative"
    2.92 -  ON "supporter"."initiative_id" = "initiative"."id"
    2.93 -  LEFT JOIN "initiative_notification_sent" AS "sent"
    2.94 -    ON "sent"."member_id" = "supporter"."member_id"
    2.95 -    AND "sent"."initiative_id" = "initiative"."id"
    2.96 +    "supporter"."initiative_id"
    2.97 +  FROM "supporter"
    2.98 +  JOIN "initiative" ON "supporter"."initiative_id" = "initiative"."id"
    2.99    JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
   2.100 +  LEFT JOIN "initiative_notification_sent" AS "sent" ON
   2.101 +    "sent"."member_id" = "supporter"."member_id" AND
   2.102 +    "sent"."initiative_id" = "supporter"."initiative_id"
   2.103 +  LEFT JOIN "ignored_initiative" ON
   2.104 +    "ignored_initiative"."member_id" = "supporter"."member_id" AND
   2.105 +    "ignored_initiative"."initiative_id" = "supporter"."initiative_id"
   2.106    WHERE "issue"."state" IN ('admission', 'discussion')
   2.107 +  AND "ignored_initiative"."member_id" ISNULL
   2.108    AND (
   2.109      EXISTS (
   2.110        SELECT NULL FROM "draft"
   2.111 -      WHERE "draft"."initiative_id" = "initiative"."id"
   2.112 +      LEFT JOIN "ignored_member" ON
   2.113 +        "ignored_member"."member_id" = "supporter"."member_id" AND
   2.114 +        "ignored_member"."other_member_id" = "draft"."author_id"
   2.115 +      WHERE "draft"."initiative_id" = "supporter"."initiative_id"
   2.116        AND "draft"."id" > "supporter"."draft_id"
   2.117 +      AND "ignored_member"."member_id" ISNULL
   2.118      ) OR EXISTS (
   2.119        SELECT NULL FROM "suggestion"
   2.120        LEFT JOIN "opinion" ON
   2.121          "opinion"."member_id" = "supporter"."member_id" AND
   2.122          "opinion"."suggestion_id" = "suggestion"."id"
   2.123 -      WHERE "suggestion"."initiative_id" = "initiative"."id"
   2.124 +      LEFT JOIN "ignored_member" ON
   2.125 +        "ignored_member"."member_id" = "supporter"."member_id" AND
   2.126 +        "ignored_member"."other_member_id" = "suggestion"."author_id"
   2.127 +      WHERE "suggestion"."initiative_id" = "supporter"."initiative_id"
   2.128        AND "opinion"."member_id" ISNULL
   2.129 -      AND COALESCE(
   2.130 -        "suggestion"."id" > "sent"."last_suggestion_id",
   2.131 -        TRUE
   2.132 -      )
   2.133 +      AND COALESCE("suggestion"."id" > "sent"."last_suggestion_id", TRUE)
   2.134 +      AND "ignored_member"."member_id" ISNULL
   2.135      )
   2.136    );
   2.137  
   2.138  CREATE FUNCTION "featured_initiative"
   2.139 -  ( "member_id_p" "member"."id"%TYPE,
   2.140 -    "area_id_p"   "area"."id"%TYPE )
   2.141 -  RETURNS SETOF "initiative"
   2.142 +  ( "recipient_id_p" "member"."id"%TYPE,
   2.143 +    "area_id_p"      "area"."id"%TYPE )
   2.144 +  RETURNS SETOF "initiative"."id"%TYPE
   2.145    LANGUAGE 'plpgsql' STABLE AS $$
   2.146      DECLARE
   2.147 -      "member_row"        "member"%ROWTYPE;
   2.148 +      "counter_v"         "member"."notification_counter"%TYPE;
   2.149 +      "sample_size_v"     "member"."notification_sample_size"%TYPE;
   2.150 +      "initiative_id_ary" INT4[];  --"initiative"."id"%TYPE[]
   2.151 +      "match_v"           BOOLEAN;
   2.152        "member_id_v"       "member"."id"%TYPE;
   2.153        "seed_v"            TEXT;
   2.154 -      "result_row"        "initiative"%ROWTYPE;
   2.155 -      "match_v"           BOOLEAN;
   2.156 -      "initiative_id_ary" INT4[];  --"initiative"."id"%TYPE[]
   2.157 +      "initiative_id_v"   "initiative"."id"%TYPE;
   2.158      BEGIN
   2.159 -      SELECT INTO "member_row" * FROM "member" WHERE "id" = "member_id_p";
   2.160 +      SELECT "notification_counter", "notification_sample_size"
   2.161 +        INTO "counter_v", "sample_size_v"
   2.162 +        FROM "member" WHERE "id" = "recipient_id_p";
   2.163        "initiative_id_ary" := '{}';
   2.164        LOOP
   2.165          "match_v" := FALSE;
   2.166 @@ -127,38 +149,64 @@
   2.167            SELECT * FROM (
   2.168              SELECT DISTINCT
   2.169                "supporter"."member_id",
   2.170 -              md5("member_id_p" || '-' || "member_row"."notification_counter" || '-' || "area_id_p" || '-' || "supporter"."member_id") AS "seed"
   2.171 +              md5(
   2.172 +                "recipient_id_p" || '-' ||
   2.173 +                "counter_v"      || '-' ||
   2.174 +                "area_id_p"      || '-' ||
   2.175 +                "supporter"."member_id"
   2.176 +              ) AS "seed"
   2.177              FROM "supporter"
   2.178 -            JOIN "member" ON "member"."id" = "supporter"."member_id"
   2.179              JOIN "initiative" ON "initiative"."id" = "supporter"."initiative_id"
   2.180              JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
   2.181 -            WHERE "supporter"."member_id" != "member_id_p"
   2.182 +            WHERE "supporter"."member_id" != "recipient_id_p"
   2.183              AND "issue"."area_id" = "area_id_p"
   2.184              AND "issue"."state" IN ('admission', 'discussion', 'verification')
   2.185            ) AS "subquery"
   2.186            ORDER BY "seed"
   2.187          LOOP
   2.188 -          SELECT "initiative".* INTO "result_row"
   2.189 +          SELECT "initiative"."id" INTO "initiative_id_v"
   2.190              FROM "initiative"
   2.191              JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
   2.192 +            JOIN "area" ON "area"."id" = "issue"."area_id"
   2.193              JOIN "supporter" ON "supporter"."initiative_id" = "initiative"."id"
   2.194              LEFT JOIN "supporter" AS "self_support" ON
   2.195                "self_support"."initiative_id" = "initiative"."id" AND
   2.196 -              "self_support"."member_id" = "member_id_p"
   2.197 +              "self_support"."member_id" = "recipient_id_p"
   2.198 +            LEFT JOIN "privilege" ON
   2.199 +              "privilege"."member_id" = "recipient_id_p" AND
   2.200 +              "privilege"."unit_id" = "area"."unit_id" AND
   2.201 +              "privilege"."voting_right" = TRUE
   2.202 +            LEFT JOIN "subscription" ON
   2.203 +              "subscription"."member_id" = "recipient_id_p" AND
   2.204 +              "subscription"."unit_id" = "area"."unit_id"
   2.205 +            LEFT JOIN "ignored_initiative" ON
   2.206 +              "ignored_initiative"."member_id" = "recipient_id_p" AND
   2.207 +              "ignored_initiative"."initiative_id" = "initiative"."id"
   2.208              WHERE "supporter"."member_id" = "member_id_v"
   2.209              AND "issue"."area_id" = "area_id_p"
   2.210              AND "issue"."state" IN ('admission', 'discussion', 'verification')
   2.211              AND "self_support"."member_id" ISNULL
   2.212              AND NOT "initiative_id_ary" @> ARRAY["initiative"."id"]
   2.213 +            AND (
   2.214 +              "privilege"."member_id" NOTNULL OR
   2.215 +              "subscription"."member_id" NOTNULL )
   2.216 +            AND "ignored_initiative"."member_id" ISNULL
   2.217 +            AND NOT EXISTS (
   2.218 +              SELECT NULL FROM "draft"
   2.219 +              JOIN "ignored_member" ON
   2.220 +                "ignored_member"."member_id" = "recipient_id_p" AND
   2.221 +                "ignored_member"."other_member_id" = "draft"."author_id"
   2.222 +              WHERE "draft"."initiative_id" = "initiative"."id"
   2.223 +            )
   2.224              ORDER BY md5("seed_v" || '-' || "initiative"."id")
   2.225              LIMIT 1;
   2.226            IF FOUND THEN
   2.227              "match_v" := TRUE;
   2.228 -            "initiative_id_ary" := "initiative_id_ary" || "result_row"."id";
   2.229 -            RETURN NEXT "result_row";
   2.230 -            IF array_length("initiative_id_ary", 1) >= "member_row"."notification_sample_size" THEN
   2.231 +            RETURN NEXT "initiative_id_v";
   2.232 +            IF array_length("initiative_id_ary", 1) + 1 >= "sample_size_v" THEN
   2.233                RETURN;
   2.234              END IF;
   2.235 +            "initiative_id_ary" := "initiative_id_ary" || "initiative_id_v";
   2.236            END IF;
   2.237          END LOOP;
   2.238          EXIT WHEN NOT "match_v";
   2.239 @@ -168,115 +216,120 @@
   2.240    $$;
   2.241  
   2.242  CREATE VIEW "updated_or_featured_initiative" AS
   2.243 -  SELECT * FROM "updated_initiative"
   2.244 -  UNION ALL
   2.245    SELECT
   2.246 -    "member"."id" AS "seen_by_member_id",
   2.247 -    FALSE AS "supported",
   2.248 -    EXISTS (
   2.249 -      SELECT NULL FROM "draft"
   2.250 -      WHERE "draft"."initiative_id" = "initiative"."id"
   2.251 -      AND COALESCE(
   2.252 -        "draft"."id" > "sent"."last_draft_id",
   2.253 -        TRUE
   2.254 -      )
   2.255 -    ) AS "new_draft",
   2.256 -    ( SELECT count(1) FROM "suggestion"
   2.257 -      WHERE "suggestion"."initiative_id" = "initiative"."id"
   2.258 -      AND COALESCE(
   2.259 -        "suggestion"."id" > "sent"."last_suggestion_id",
   2.260 -        TRUE
   2.261 -      )
   2.262 -    ) AS "new_suggestion_count",
   2.263 -    TRUE AS "featured",
   2.264 +    "subquery".*,
   2.265      NOT EXISTS (
   2.266        SELECT NULL FROM "initiative" AS "better_initiative"
   2.267 -      WHERE
   2.268 -        "better_initiative"."issue_id" = "initiative"."issue_id"
   2.269 +      WHERE "better_initiative"."issue_id" = "initiative"."issue_id"
   2.270        AND
   2.271          ( COALESCE("better_initiative"."harmonic_weight", -1),
   2.272            -"better_initiative"."id" ) >
   2.273          ( COALESCE("initiative"."harmonic_weight", -1),
   2.274            -"initiative"."id" )
   2.275 -    ) AS "leading",
   2.276 -    "initiative".*
   2.277 -  FROM "member" CROSS JOIN "area"
   2.278 -  CROSS JOIN LATERAL
   2.279 -    "featured_initiative"("member"."id", "area"."id") AS "initiative"
   2.280 -  LEFT JOIN "initiative_notification_sent" AS "sent"
   2.281 -    ON "sent"."member_id" = "member"."id"
   2.282 -    AND "sent"."initiative_id" = "initiative"."id";
   2.283 +    ) AS "leading"
   2.284 +  FROM (
   2.285 +    SELECT * FROM "updated_initiative"
   2.286 +    UNION ALL
   2.287 +    SELECT
   2.288 +      "member"."id" AS "recipient_id",
   2.289 +      TRUE AS "featured",
   2.290 +      "featured_initiative_id" AS "initiative_id"
   2.291 +    FROM "member" CROSS JOIN "area"
   2.292 +    CROSS JOIN LATERAL
   2.293 +      "featured_initiative"("member"."id", "area"."id") AS "featured_initiative_id"
   2.294 +    JOIN "initiative" ON "initiative"."id" = "featured_initiative_id"
   2.295 +  ) AS "subquery"
   2.296 +  JOIN "initiative" ON "initiative"."id" = "subquery"."initiative_id";
   2.297  
   2.298  CREATE VIEW "leading_complement_initiative" AS
   2.299    SELECT * FROM (
   2.300 -    SELECT DISTINCT ON ("seen_by_member_id", "initiative"."issue_id")
   2.301 -      "uf_initiative"."seen_by_member_id",
   2.302 -      "supporter"."member_id" NOTNULL AS "supported",
   2.303 -      CASE WHEN "supporter"."member_id" NOTNULL THEN FALSE ELSE
   2.304 -        EXISTS (
   2.305 -          SELECT NULL FROM "draft"
   2.306 -          WHERE "draft"."initiative_id" = "initiative"."id"
   2.307 -          AND COALESCE(
   2.308 -            "draft"."id" > "sent"."last_draft_id",
   2.309 -            TRUE
   2.310 -          )
   2.311 -        )
   2.312 -      END AS "new_draft",
   2.313 -      CASE WHEN "supporter"."member_id" NOTNULL THEN 0 ELSE
   2.314 -        ( SELECT count(1) FROM "suggestion"
   2.315 -          WHERE "suggestion"."initiative_id" = "initiative"."id"
   2.316 -          AND COALESCE(
   2.317 -            "suggestion"."id" > "sent"."last_suggestion_id",
   2.318 -            TRUE
   2.319 -          )
   2.320 -        )
   2.321 -      END AS "new_suggestion_count",
   2.322 +    SELECT DISTINCT ON ("uf_initiative"."recipient_id", "initiative"."issue_id")
   2.323 +      "uf_initiative"."recipient_id",
   2.324        FALSE AS "featured",
   2.325 -      TRUE AS "leading",
   2.326 -      "initiative".*
   2.327 +      "uf_initiative"."initiative_id",
   2.328 +      TRUE AS "leading"
   2.329      FROM "updated_or_featured_initiative" AS "uf_initiative"
   2.330 +    JOIN "initiative" AS "uf_initiative_full" ON
   2.331 +      "uf_initiative_full"."id" = "uf_initiative"."initiative_id"
   2.332      JOIN "initiative" ON
   2.333 -      "uf_initiative"."issue_id" = "initiative"."issue_id"
   2.334 -    LEFT JOIN "supporter" ON
   2.335 -      "supporter"."member_id" = "uf_initiative"."seen_by_member_id" AND
   2.336 -      "supporter"."initiative_id" = "initiative"."id"
   2.337 -    LEFT JOIN "initiative_notification_sent" AS "sent"
   2.338 -      ON "sent"."member_id" = "uf_initiative"."seen_by_member_id"
   2.339 -      AND "sent"."initiative_id" = "initiative"."id"
   2.340 +      "initiative"."issue_id" = "uf_initiative_full"."issue_id"
   2.341      ORDER BY
   2.342 -      "seen_by_member_id",
   2.343 +      "uf_initiative"."recipient_id",
   2.344        "initiative"."issue_id",
   2.345        "initiative"."harmonic_weight" DESC,
   2.346        "initiative"."id"
   2.347    ) AS "subquery"
   2.348    WHERE NOT EXISTS (
   2.349      SELECT NULL FROM "updated_or_featured_initiative" AS "other"
   2.350 -    WHERE "other"."seen_by_member_id" = "subquery"."seen_by_member_id"
   2.351 -    AND "other"."id" = "subquery"."id"
   2.352 +    WHERE "other"."recipient_id" = "subquery"."recipient_id"
   2.353 +    AND "other"."initiative_id" = "subquery"."initiative_id"
   2.354    );
   2.355  
   2.356  CREATE VIEW "unfiltered_initiative_for_notification" AS
   2.357 -  SELECT * FROM "updated_or_featured_initiative"
   2.358 -  UNION ALL
   2.359 -  SELECT * FROM "leading_complement_initiative";
   2.360 +  SELECT
   2.361 +    "subquery".*,
   2.362 +    "supporter"."member_id" NOTNULL AS "supported",
   2.363 +    CASE WHEN "supporter"."member_id" NOTNULL THEN
   2.364 +      EXISTS (
   2.365 +        SELECT NULL FROM "draft"
   2.366 +        WHERE "draft"."initiative_id" = "subquery"."initiative_id"
   2.367 +        AND "draft"."id" > "supporter"."draft_id"
   2.368 +      )
   2.369 +    ELSE
   2.370 +      EXISTS (
   2.371 +        SELECT NULL FROM "draft"
   2.372 +        WHERE "draft"."initiative_id" = "subquery"."initiative_id"
   2.373 +        AND COALESCE("draft"."id" > "sent"."last_draft_id", TRUE)
   2.374 +      )
   2.375 +    END AS "new_draft",
   2.376 +    CASE WHEN "supporter"."member_id" NOTNULL THEN
   2.377 +      ( SELECT count(1) FROM "suggestion"
   2.378 +        LEFT JOIN "opinion" ON
   2.379 +          "opinion"."member_id" = "supporter"."member_id" AND
   2.380 +          "opinion"."suggestion_id" = "suggestion"."id"
   2.381 +        WHERE "suggestion"."initiative_id" = "subquery"."initiative_id"
   2.382 +        AND "opinion"."member_id" ISNULL
   2.383 +        AND COALESCE("suggestion"."id" > "sent"."last_suggestion_id", TRUE)
   2.384 +      )
   2.385 +    ELSE
   2.386 +      ( SELECT count(1) FROM "suggestion"
   2.387 +        WHERE "suggestion"."initiative_id" = "subquery"."initiative_id"
   2.388 +        AND COALESCE("suggestion"."id" > "sent"."last_suggestion_id", TRUE)
   2.389 +      )
   2.390 +    END AS "new_suggestion_count"
   2.391 +  FROM (
   2.392 +    SELECT * FROM "updated_or_featured_initiative"
   2.393 +    UNION ALL
   2.394 +    SELECT * FROM "leading_complement_initiative"
   2.395 +  ) AS "subquery"
   2.396 +  LEFT JOIN "supporter" ON
   2.397 +    "supporter"."member_id" = "subquery"."recipient_id" AND
   2.398 +    "supporter"."initiative_id" = "subquery"."initiative_id"
   2.399 +  LEFT JOIN "initiative_notification_sent" AS "sent" ON
   2.400 +    "sent"."member_id" = "subquery"."recipient_id" AND
   2.401 +    "sent"."initiative_id" = "subquery"."initiative_id";
   2.402  
   2.403  CREATE VIEW "initiative_for_notification" AS
   2.404 -  SELECT "initiative1".*
   2.405 -  FROM "unfiltered_initiative_for_notification" "initiative1"
   2.406 -  JOIN "issue" AS "issue1" ON "initiative1"."issue_id" = "issue1"."id"
   2.407 +  SELECT "unfiltered1".*
   2.408 +  FROM "unfiltered_initiative_for_notification" "unfiltered1"
   2.409 +  JOIN "initiative" AS "initiative1" ON
   2.410 +    "initiative1"."id" = "unfiltered1"."initiative_id"
   2.411 +  JOIN "issue" AS "issue1" ON "issue1"."id" = "initiative1"."issue_id"
   2.412    WHERE EXISTS (
   2.413      SELECT NULL
   2.414 -    FROM "unfiltered_initiative_for_notification" "initiative2"
   2.415 -    JOIN "issue" AS "issue2" ON "initiative2"."issue_id" = "issue2"."id"
   2.416 -    WHERE "initiative1"."seen_by_member_id" = "initiative2"."seen_by_member_id"
   2.417 +    FROM "unfiltered_initiative_for_notification" "unfiltered2"
   2.418 +    JOIN "initiative" AS "initiative2" ON
   2.419 +      "initiative2"."id" = "unfiltered2"."initiative_id"
   2.420 +    JOIN "issue" AS "issue2" ON "issue2"."id" = "initiative2"."issue_id"
   2.421 +    WHERE "unfiltered1"."recipient_id" = "unfiltered2"."recipient_id"
   2.422      AND "issue1"."area_id" = "issue2"."area_id"
   2.423 -    AND ( "initiative2"."new_draft" OR "initiative2"."new_suggestion_count" > 0 )
   2.424 +    AND ("unfiltered2"."new_draft" OR "unfiltered2"."new_suggestion_count" > 0 )
   2.425    );
   2.426  
   2.427  CREATE VIEW "newsletter_to_send" AS
   2.428    SELECT
   2.429 -    "newsletter"."id" AS "newsletter_id",
   2.430 -    "member"."id" AS "member_id"
   2.431 +    "member"."id" AS "recipient_id",
   2.432 +    "newsletter"."id" AS "newsletter_id"
   2.433    FROM "newsletter" CROSS JOIN "member"
   2.434    LEFT JOIN "privilege" ON
   2.435      "privilege"."member_id" = "member"."id" AND
   2.436 @@ -309,19 +362,19 @@
   2.437        PERFORM NULL FROM "member" WHERE "id" = "member_id_p" FOR UPDATE;
   2.438        FOR "result_row" IN
   2.439          SELECT * FROM "initiative_for_notification"
   2.440 -        WHERE "seen_by_member_id" = "member_id_p"
   2.441 +        WHERE "member_id" = "member_id_p"
   2.442        LOOP
   2.443          SELECT "id" INTO "last_draft_id_v" FROM "draft"
   2.444 -          WHERE "draft"."initiative_id" = "result_row"."id"
   2.445 +          WHERE "draft"."initiative_id" = "result_row"."initiative_id"
   2.446            ORDER BY "id" DESC LIMIT 1;
   2.447          SELECT "id" INTO "last_suggestion_id_v" FROM "suggestion"
   2.448 -          WHERE "suggestion"."initiative_id" = "result_row"."id"
   2.449 +          WHERE "suggestion"."initiative_id" = "result_row"."initiative_id"
   2.450            ORDER BY "id" DESC LIMIT 1;
   2.451          INSERT INTO "initiative_notification_sent"
   2.452            ("member_id", "initiative_id", "last_draft_id", "last_suggestion_id")
   2.453            VALUES (
   2.454              "member_id_p",
   2.455 -            "result_row"."id",
   2.456 +            "result_row"."initiative_id",
   2.457              "last_draft_id_v",
   2.458              "last_suggestion_id_v" )
   2.459            ON CONFLICT ("member_id", "initiative_id") DO UPDATE SET

Impressum / About Us