liquid_feedback_core

changeset 492:917a65357efb

Updated preliminary update script for new notification system
author jbe
date Sun Apr 03 19:48:32 2016 +0200 (2016-04-03)
parents aa94c7dbb20f
children d932363da4ee
files update/core-update.v3.1.0-v3.2.0.sql
line diff
     1.1 --- a/update/core-update.v3.1.0-v3.2.0.sql	Sun Apr 03 19:42:09 2016 +0200
     1.2 +++ b/update/core-update.v3.1.0-v3.2.0.sql	Sun Apr 03 19:48:32 2016 +0200
     1.3 @@ -5,30 +5,40 @@
     1.4    AS "subquery"("string", "major", "minor", "revision");
     1.5  
     1.6  ALTER TABLE "member" ADD COLUMN "disable_notifications" BOOLEAN NOT NULL DEFAULT FALSE;
     1.7 -ALTER TABLE "member" ADD COLUMN "notification_counter" INT4 NOT NULL DEFAULT 0;
     1.8 -ALTER TABLE "member" ADD COLUMN "sample_size" INT4 NOT NULL DEFAULT 3;
     1.9 -ALTER TABLE "member" ADD COLUMN "last_notified_suggestion_id" INT8;
    1.10 +ALTER TABLE "member" ADD COLUMN "notification_counter" INT4 NOT NULL DEFAULT 1;
    1.11 +ALTER TABLE "member" ADD COLUMN "notification_sample_size" INT4 NOT NULL DEFAULT 3;
    1.12 +ALTER TABLE "member" ADD COLUMN "notification_dow" INT4 CHECK ("notification_dow" BETWEEN 0 AND 6);
    1.13 +ALTER TABLE "member" ADD COLUMN "notification_hour" INT4 CHECK ("notification_hour" BETWEEN 0 AND 23);
    1.14  
    1.15  UPDATE "member" SET "disable_notifications" = TRUE WHERE "notify_level" = 'none';
    1.16   
    1.17 -CREATE TABLE "subscription_time" (
    1.18 -        "member_id"             INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    1.19 -        "day_of_week"           INT2            CONSTRAINT "day_of_week_range" CHECK ("day_of_week" BETWEEN 0 AND 6),
    1.20 -        "time_of_day"           TIME            NOT NULL );
    1.21 -CREATE UNIQUE INDEX "subscription_time_all_days_of_week_time_of_day_idx" ON "subscription_time" ("time_of_day", "member_id") WHERE ("day_of_week" ISNULL);
    1.22 -CREATE UNIQUE INDEX "subscription_time_day_of_week_time_of_day_idx" ON "subscription_time" ("day_of_week", "time_of_day", "member_id");
    1.23 -
    1.24  CREATE TABLE "subscription" (
    1.25          PRIMARY KEY ("member_id", "unit_id"),
    1.26          "member_id"             INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    1.27          "unit_id"               INT4            REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
    1.28  CREATE INDEX "subscription_unit_id_idx" ON "subscription" ("unit_id");
    1.29 - 
    1.30 -DROP VIEW "selected_event_seen_by_member";
    1.31 +
    1.32 +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';
    1.33 +
    1.34 +CREATE TABLE "ignored_area" (
    1.35 +        PRIMARY KEY ("member_id", "area_id"),
    1.36 +        "member_id"             INT4            REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    1.37 +        "area_id"               INT4            REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
    1.38 +CREATE INDEX "ignored_area_area_id_idx" ON "ignored_area" ("area_id");
    1.39 +
    1.40 +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';
    1.41 +
    1.42 +CREATE TABLE "initiative_notification_sent" (
    1.43 +        PRIMARY KEY ("member_id", "initiative_id"),
    1.44 +        "member_id"             INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    1.45 +        "initiative_id"         INT4            REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    1.46 +        "last_draft_id"         INT8            NOT NULL,
    1.47 +        "last_suggestion_id"    INT8            NOT NULL );
    1.48 +CREATE INDEX "initiative_notification_sent_initiative_idx" ON "initiative_notification_sent" ("initiative_id");
    1.49  
    1.50  CREATE VIEW "updated_initiative" AS
    1.51    SELECT
    1.52 -    "member"."id" AS "seen_by_member_id",
    1.53 +    "supporter"."member_id" AS "seen_by_member_id",
    1.54      TRUE AS "supported",
    1.55      EXISTS (
    1.56        SELECT NULL FROM "draft"
    1.57 @@ -36,9 +46,13 @@
    1.58        AND "draft"."id" > "supporter"."draft_id"
    1.59      ) AS "new_draft",
    1.60      ( SELECT count(1) FROM "suggestion"
    1.61 +      LEFT JOIN "opinion" ON
    1.62 +        "opinion"."member_id" = "supporter"."member_id" AND
    1.63 +        "opinion"."suggestion_id" = "suggestion"."id"
    1.64        WHERE "suggestion"."initiative_id" = "initiative"."id"
    1.65 +      AND "opinion"."member_id" ISNULL
    1.66        AND COALESCE(
    1.67 -        "suggestion"."id" > "member"."last_notified_suggestion_id",
    1.68 +        "suggestion"."id" > "sent"."last_suggestion_id",
    1.69          TRUE
    1.70        )
    1.71      ) AS "new_suggestion_count",
    1.72 @@ -54,11 +68,12 @@
    1.73            -"initiative"."id" )
    1.74      ) AS "leading",
    1.75      "initiative".*
    1.76 -  FROM "member" CROSS JOIN "initiative"
    1.77 +  FROM "supporter" JOIN "initiative"
    1.78 +  ON "supporter"."initiative_id" = "initiative"."id"
    1.79 +  LEFT JOIN "initiative_notification_sent" AS "sent"
    1.80 +    ON "sent"."member_id" = "supporter"."member_id"
    1.81 +    AND "sent"."initiative_id" = "initiative"."id"
    1.82    JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
    1.83 -  JOIN "supporter" ON
    1.84 -    "supporter"."member_id" = "member"."id" AND
    1.85 -    "supporter"."initiative_id" = "initiative"."id"
    1.86    WHERE "issue"."state" IN ('admission', 'discussion')
    1.87    AND (
    1.88      EXISTS (
    1.89 @@ -67,9 +82,13 @@
    1.90        AND "draft"."id" > "supporter"."draft_id"
    1.91      ) OR EXISTS (
    1.92        SELECT NULL FROM "suggestion"
    1.93 +      LEFT JOIN "opinion" ON
    1.94 +        "opinion"."member_id" = "supporter"."member_id" AND
    1.95 +        "opinion"."suggestion_id" = "suggestion"."id"
    1.96        WHERE "suggestion"."initiative_id" = "initiative"."id"
    1.97 +      AND "opinion"."member_id" ISNULL
    1.98        AND COALESCE(
    1.99 -        "suggestion"."id" > "member"."last_notified_suggestion_id",
   1.100 +        "suggestion"."id" > "sent"."last_suggestion_id",
   1.101          TRUE
   1.102        )
   1.103      )
   1.104 @@ -125,7 +144,7 @@
   1.105              "match_v" := TRUE;
   1.106              "initiative_id_ary" := "initiative_id_ary" || "result_row"."id";
   1.107              RETURN NEXT "result_row";
   1.108 -            IF array_length("initiative_id_ary", 1) >= "member_row"."sample_size" THEN
   1.109 +            IF array_length("initiative_id_ary", 1) >= "member_row"."notification_sample_size" THEN
   1.110                RETURN;
   1.111              END IF;
   1.112            END IF;
   1.113 @@ -142,8 +161,21 @@
   1.114    SELECT
   1.115      "member"."id" AS "seen_by_member_id",
   1.116      FALSE AS "supported",
   1.117 -    NULL::BOOLEAN AS "new_draft",
   1.118 -    NULL::INTEGER AS "new_suggestion_count",
   1.119 +    EXISTS (
   1.120 +      SELECT NULL FROM "draft"
   1.121 +      WHERE "draft"."initiative_id" = "initiative"."id"
   1.122 +      AND COALESCE(
   1.123 +        "draft"."id" > "sent"."last_draft_id",
   1.124 +        TRUE
   1.125 +      )
   1.126 +    ) AS "new_draft",
   1.127 +    ( SELECT count(1) FROM "suggestion"
   1.128 +      WHERE "suggestion"."initiative_id" = "initiative"."id"
   1.129 +      AND COALESCE(
   1.130 +        "suggestion"."id" > "sent"."last_suggestion_id",
   1.131 +        TRUE
   1.132 +      )
   1.133 +    ) AS "new_suggestion_count",
   1.134      TRUE AS "featured",
   1.135      NOT EXISTS (
   1.136        SELECT NULL FROM "initiative" AS "better_initiative"
   1.137 @@ -158,21 +190,47 @@
   1.138      "initiative".*
   1.139    FROM "member" CROSS JOIN "area"
   1.140    CROSS JOIN LATERAL
   1.141 -    "featured_initiative"("member"."id", "area"."id") AS "initiative";
   1.142 +    "featured_initiative"("member"."id", "area"."id") AS "initiative"
   1.143 +  LEFT JOIN "initiative_notification_sent" AS "sent"
   1.144 +    ON "sent"."member_id" = "member"."id"
   1.145 +    AND "sent"."initiative_id" = "initiative"."id";
   1.146  
   1.147  CREATE VIEW "leading_complement_initiative" AS
   1.148    SELECT * FROM (
   1.149      SELECT DISTINCT ON ("seen_by_member_id", "initiative"."issue_id")
   1.150 -      "updated_or_featured_initiative"."seen_by_member_id",
   1.151 -      FALSE AS "supported",
   1.152 -      NULL::BOOLEAN AS "new_draft",
   1.153 -      NULL::INTEGER AS "new_suggestion_count",
   1.154 +      "uf_initiative"."seen_by_member_id",
   1.155 +      "supporter"."member_id" NOTNULL AS "supported",
   1.156 +      CASE WHEN "supporter"."member_id" NOTNULL THEN FALSE ELSE
   1.157 +        EXISTS (
   1.158 +          SELECT NULL FROM "draft"
   1.159 +          WHERE "draft"."initiative_id" = "initiative"."id"
   1.160 +          AND COALESCE(
   1.161 +            "draft"."id" > "sent"."last_draft_id",
   1.162 +            TRUE
   1.163 +          )
   1.164 +        )
   1.165 +      END AS "new_draft",
   1.166 +      CASE WHEN "supporter"."member_id" NOTNULL THEN 0 ELSE
   1.167 +        ( SELECT count(1) FROM "suggestion"
   1.168 +          WHERE "suggestion"."initiative_id" = "initiative"."id"
   1.169 +          AND COALESCE(
   1.170 +            "suggestion"."id" > "sent"."last_suggestion_id",
   1.171 +            TRUE
   1.172 +          )
   1.173 +        )
   1.174 +      END AS "new_suggestion_count",
   1.175        FALSE AS "featured",
   1.176        TRUE AS "leading",
   1.177        "initiative".*
   1.178 -    FROM "updated_or_featured_initiative"
   1.179 -    JOIN "initiative"
   1.180 -    ON "updated_or_featured_initiative"."issue_id" = "initiative"."issue_id"
   1.181 +    FROM "updated_or_featured_initiative" AS "uf_initiative"
   1.182 +    JOIN "initiative" ON
   1.183 +      "uf_initiative"."issue_id" = "initiative"."issue_id"
   1.184 +    LEFT JOIN "supporter" ON
   1.185 +      "supporter"."member_id" = "uf_initiative"."seen_by_member_id" AND
   1.186 +      "supporter"."initiative_id" = "initiative"."id"
   1.187 +    LEFT JOIN "initiative_notification_sent" AS "sent"
   1.188 +      ON "sent"."member_id" = "uf_initiative"."seen_by_member_id"
   1.189 +      AND "sent"."initiative_id" = "initiative"."id"
   1.190      ORDER BY
   1.191        "seen_by_member_id",
   1.192        "initiative"."issue_id",
   1.193 @@ -185,9 +243,75 @@
   1.194      AND "other"."id" = "subquery"."id"
   1.195    );
   1.196  
   1.197 -CREATE VIEW "initiative_for_notification" AS
   1.198 +CREATE VIEW "unfiltered_initiative_for_notification" AS
   1.199    SELECT * FROM "updated_or_featured_initiative"
   1.200    UNION ALL
   1.201    SELECT * FROM "leading_complement_initiative";
   1.202  
   1.203 +CREATE VIEW "initiative_for_notification" AS
   1.204 +  SELECT "initiative1".*
   1.205 +  FROM "unfiltered_initiative_for_notification" "initiative1"
   1.206 +  JOIN "issue" AS "issue1" ON "initiative1"."issue_id" = "issue1"."id"
   1.207 +  WHERE EXISTS (
   1.208 +    SELECT NULL
   1.209 +    FROM "unfiltered_initiative_for_notification" "initiative2"
   1.210 +    JOIN "issue" AS "issue2" ON "initiative2"."issue_id" = "issue2"."id"
   1.211 +    WHERE "initiative1"."seen_by_member_id" = "initiative2"."seen_by_member_id"
   1.212 +    AND "issue1"."area_id" = "issue2"."area_id"
   1.213 +    AND ( "initiative2"."new_draft" OR "initiative2"."new_suggestion_count" > 0 )
   1.214 +  );
   1.215 +
   1.216 +CREATE FUNCTION "get_initiatives_for_notification"
   1.217 +  ( "member_id_p" "member"."id"%TYPE )
   1.218 +  RETURNS SETOF "initiative_for_notification"
   1.219 +  LANGUAGE 'plpgsql' VOLATILE AS $$
   1.220 +    DECLARE
   1.221 +      "result_row"           "initiative_for_notification"%ROWTYPE;
   1.222 +      "last_draft_id_v"      "draft"."id"%TYPE;
   1.223 +      "last_suggestion_id_v" "suggestion"."id"%TYPE;
   1.224 +    BEGIN
   1.225 +      PERFORM "require_transaction_isolation"();
   1.226 +      PERFORM NULL FROM "member" WHERE "id" = "member_id_p" FOR UPDATE;
   1.227 +      FOR "result_row" IN
   1.228 +        SELECT * FROM "initiative_for_notification"
   1.229 +        WHERE "seen_by_member_id" = "member_id_p"
   1.230 +      LOOP
   1.231 +        SELECT "id" INTO "last_draft_id_v" FROM "draft"
   1.232 +          WHERE "draft"."initiative_id" = "result_row"."id"
   1.233 +          ORDER BY "id" DESC LIMIT 1;
   1.234 +        SELECT "id" INTO "last_suggestion_id_v" FROM "suggestion"
   1.235 +          WHERE "suggestion"."initiative_id" = "result_row"."id"
   1.236 +          ORDER BY "id" DESC LIMIT 1;
   1.237 +        INSERT INTO "initiative_notification_sent"
   1.238 +          ("member_id", "initiative_id", "last_draft_id", "last_suggestion_id")
   1.239 +          VALUES (
   1.240 +            "member_id_p",
   1.241 +            "result_row"."id",
   1.242 +            "last_draft_id",
   1.243 +            "last_suggestion_id" )
   1.244 +          ON CONFLICT ("member_id", "initiative_id") DO UPDATE SET
   1.245 +            "last_draft_id" = CASE
   1.246 +              WHEN "last_draft_id" > "last_draft_id_v"
   1.247 +              THEN "last_draft_id"
   1.248 +              ELSE "last_draft_id_v"
   1.249 +            END,
   1.250 +            "last_suggestion_id" = CASE
   1.251 +              WHEN "last_suggestion_id" > "last_suggestion_id_v"
   1.252 +              THEN "last_suggestion_id"
   1.253 +              ELSE "last_suggestion_id_v"
   1.254 +            END;
   1.255 +        RETURN NEXT "result_row";
   1.256 +      END LOOP;
   1.257 +      DELETE FROM "initiative_notification_sent"
   1.258 +        USING "initiative", "issue"
   1.259 +        WHERE "initiative_notification_sent"."member_id" = "member_id_p"
   1.260 +        AND "initiative"."id" = "initiative_notification_sent"."initiative_id"
   1.261 +        AND "issue"."id" = "initiative"."issue_id"
   1.262 +        AND ( "issue"."closed" NOTNULL OR "issue"."fully_frozen" NOTNULL );
   1.263 +      UPDATE "member" SET "notification_counter" = "notification_counter" + 1
   1.264 +        WHERE "id" = "member_id_p";
   1.265 +      RETURN;
   1.266 +    END;
   1.267 +  $$;
   1.268 +
   1.269  COMMIT;

Impressum / About Us