1.1 --- /dev/null	Thu Jan 01 00:00:00 1970 +0000
     1.2 +++ b/update/core-update.v3.2.0-v3.2.1.sql	Sat Apr 30 17:03:48 2016 +0200
     1.3 @@ -0,0 +1,67 @@
     1.4 +BEGIN;
     1.5 +
     1.6 +CREATE OR REPLACE VIEW "liquid_feedback_version" AS
     1.7 +  SELECT * FROM (VALUES ('3.2.1', 3, 2, 1))
     1.8 +  AS "subquery"("string", "major", "minor", "revision");
     1.9 +
    1.10 +CREATE OR REPLACE FUNCTION "get_initiatives_for_notification"
    1.11 +  ( "recipient_id_p" "member"."id"%TYPE )
    1.12 +  RETURNS SETOF "initiative_for_notification"
    1.13 +  LANGUAGE 'plpgsql' VOLATILE AS $$
    1.14 +    DECLARE
    1.15 +      "result_row"           "initiative_for_notification"%ROWTYPE;
    1.16 +      "last_draft_id_v"      "draft"."id"%TYPE;
    1.17 +      "last_suggestion_id_v" "suggestion"."id"%TYPE;
    1.18 +    BEGIN
    1.19 +      PERFORM "require_transaction_isolation"();
    1.20 +      PERFORM NULL FROM "member" WHERE "id" = "recipient_id_p" FOR UPDATE;
    1.21 +      FOR "result_row" IN
    1.22 +        SELECT * FROM "initiative_for_notification"
    1.23 +        WHERE "recipient_id" = "recipient_id_p"
    1.24 +      LOOP
    1.25 +        SELECT "id" INTO "last_draft_id_v" FROM "draft"
    1.26 +          WHERE "draft"."initiative_id" = "result_row"."initiative_id"
    1.27 +          ORDER BY "id" DESC LIMIT 1;
    1.28 +        SELECT "id" INTO "last_suggestion_id_v" FROM "suggestion"
    1.29 +          WHERE "suggestion"."initiative_id" = "result_row"."initiative_id"
    1.30 +          ORDER BY "id" DESC LIMIT 1;
    1.31 +        /* compatibility with PostgreSQL 9.1 */
    1.32 +        DELETE FROM "notification_initiative_sent"
    1.33 +          WHERE "member_id" = "recipient_id_p"
    1.34 +          AND "initiative_id" = "result_row"."initiative_id";
    1.35 +        INSERT INTO "notification_initiative_sent"
    1.36 +          ("member_id", "initiative_id", "last_draft_id", "last_suggestion_id")
    1.37 +          VALUES (
    1.38 +            "recipient_id_p",
    1.39 +            "result_row"."initiative_id",
    1.40 +            "last_draft_id_v",
    1.41 +            "last_suggestion_id_v" );
    1.42 +        /* TODO: use alternative code below, requires PostgreSQL 9.5 or higher
    1.43 +        INSERT INTO "notification_initiative_sent"
    1.44 +          ("member_id", "initiative_id", "last_draft_id", "last_suggestion_id")
    1.45 +          VALUES (
    1.46 +            "recipient_id_p",
    1.47 +            "result_row"."initiative_id",
    1.48 +            "last_draft_id_v",
    1.49 +            "last_suggestion_id_v" )
    1.50 +          ON CONFLICT ("member_id", "initiative_id") DO UPDATE SET
    1.51 +            "last_draft_id" = "last_draft_id_v",
    1.52 +            "last_suggestion_id" = "last_suggestion_id_v";
    1.53 +        */
    1.54 +        RETURN NEXT "result_row";
    1.55 +      END LOOP;
    1.56 +      DELETE FROM "notification_initiative_sent"
    1.57 +        USING "initiative", "issue"
    1.58 +        WHERE "notification_initiative_sent"."member_id" = "recipient_id_p"
    1.59 +        AND "initiative"."id" = "notification_initiative_sent"."initiative_id"
    1.60 +        AND "issue"."id" = "initiative"."issue_id"
    1.61 +        AND ( "issue"."closed" NOTNULL OR "issue"."fully_frozen" NOTNULL );
    1.62 +      UPDATE "member" SET
    1.63 +        "notification_counter" = "notification_counter" + 1,
    1.64 +        "notification_sent" = now()
    1.65 +        WHERE "id" = "recipient_id_p";
    1.66 +      RETURN;
    1.67 +    END;
    1.68 +  $$;
    1.69 +
    1.70 +COMMIT;