1.1 --- /dev/null	Thu Jan 01 00:00:00 1970 +0000
     1.2 +++ b/update/core-update.v3.2.1-v3.2.2.sql	Wed May 04 21:44:18 2016 +0200
     1.3 @@ -0,0 +1,101 @@
     1.4 +BEGIN;
     1.5 +
     1.6 +CREATE OR REPLACE VIEW "liquid_feedback_version" AS
     1.7 +  SELECT * FROM (VALUES ('3.2.2', 3, 2, 2))
     1.8 +  AS "subquery"("string", "major", "minor", "revision");
     1.9 +
    1.10 +CREATE OR REPLACE FUNCTION "featured_initiative"
    1.11 +  ( "recipient_id_p" "member"."id"%TYPE,
    1.12 +    "area_id_p"      "area"."id"%TYPE )
    1.13 +  RETURNS SETOF "initiative"."id"%TYPE
    1.14 +  LANGUAGE 'plpgsql' STABLE AS $$
    1.15 +    DECLARE
    1.16 +      "counter_v"         "member"."notification_counter"%TYPE;
    1.17 +      "sample_size_v"     "member"."notification_sample_size"%TYPE;
    1.18 +      "initiative_id_ary" INT4[];  --"initiative"."id"%TYPE[]
    1.19 +      "match_v"           BOOLEAN;
    1.20 +      "member_id_v"       "member"."id"%TYPE;
    1.21 +      "seed_v"            TEXT;
    1.22 +      "initiative_id_v"   "initiative"."id"%TYPE;
    1.23 +    BEGIN
    1.24 +      SELECT "notification_counter", "notification_sample_size"
    1.25 +        INTO "counter_v", "sample_size_v"
    1.26 +        FROM "member" WHERE "id" = "recipient_id_p";
    1.27 +      IF COALESCE("sample_size_v" <= 0, TRUE) THEN
    1.28 +        RETURN;
    1.29 +      END IF;
    1.30 +      "initiative_id_ary" := '{}';
    1.31 +      LOOP
    1.32 +        "match_v" := FALSE;
    1.33 +        FOR "member_id_v", "seed_v" IN
    1.34 +          SELECT * FROM (
    1.35 +            SELECT DISTINCT
    1.36 +              "supporter"."member_id",
    1.37 +              md5(
    1.38 +                "recipient_id_p" || '-' ||
    1.39 +                "counter_v"      || '-' ||
    1.40 +                "area_id_p"      || '-' ||
    1.41 +                "supporter"."member_id"
    1.42 +              ) AS "seed"
    1.43 +            FROM "supporter"
    1.44 +            JOIN "initiative" ON "initiative"."id" = "supporter"."initiative_id"
    1.45 +            JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
    1.46 +            WHERE "supporter"."member_id" != "recipient_id_p"
    1.47 +            AND "issue"."area_id" = "area_id_p"
    1.48 +            AND "issue"."state" IN ('admission', 'discussion', 'verification')
    1.49 +          ) AS "subquery"
    1.50 +          ORDER BY "seed"
    1.51 +        LOOP
    1.52 +          SELECT "initiative"."id" INTO "initiative_id_v"
    1.53 +            FROM "initiative"
    1.54 +            JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
    1.55 +            JOIN "area" ON "area"."id" = "issue"."area_id"
    1.56 +            JOIN "supporter" ON "supporter"."initiative_id" = "initiative"."id"
    1.57 +            LEFT JOIN "supporter" AS "self_support" ON
    1.58 +              "self_support"."initiative_id" = "initiative"."id" AND
    1.59 +              "self_support"."member_id" = "recipient_id_p"
    1.60 +            LEFT JOIN "privilege" ON
    1.61 +              "privilege"."member_id" = "recipient_id_p" AND
    1.62 +              "privilege"."unit_id" = "area"."unit_id" AND
    1.63 +              "privilege"."voting_right" = TRUE
    1.64 +            LEFT JOIN "subscription" ON
    1.65 +              "subscription"."member_id" = "recipient_id_p" AND
    1.66 +              "subscription"."unit_id" = "area"."unit_id"
    1.67 +            LEFT JOIN "ignored_initiative" ON
    1.68 +              "ignored_initiative"."member_id" = "recipient_id_p" AND
    1.69 +              "ignored_initiative"."initiative_id" = "initiative"."id"
    1.70 +            WHERE "supporter"."member_id" = "member_id_v"
    1.71 +            AND "issue"."area_id" = "area_id_p"
    1.72 +            AND "issue"."state" IN ('admission', 'discussion', 'verification')
    1.73 +            AND "initiative"."revoked" ISNULL
    1.74 +            AND "self_support"."member_id" ISNULL
    1.75 +            AND NOT "initiative_id_ary" @> ARRAY["initiative"."id"]
    1.76 +            AND (
    1.77 +              "privilege"."member_id" NOTNULL OR
    1.78 +              "subscription"."member_id" NOTNULL )
    1.79 +            AND "ignored_initiative"."member_id" ISNULL
    1.80 +            AND NOT EXISTS (
    1.81 +              SELECT NULL FROM "draft"
    1.82 +              JOIN "ignored_member" ON
    1.83 +                "ignored_member"."member_id" = "recipient_id_p" AND
    1.84 +                "ignored_member"."other_member_id" = "draft"."author_id"
    1.85 +              WHERE "draft"."initiative_id" = "initiative"."id"
    1.86 +            )
    1.87 +            ORDER BY md5("seed_v" || '-' || "initiative"."id")
    1.88 +            LIMIT 1;
    1.89 +          IF FOUND THEN
    1.90 +            "match_v" := TRUE;
    1.91 +            RETURN NEXT "initiative_id_v";
    1.92 +            IF array_length("initiative_id_ary", 1) + 1 >= "sample_size_v" THEN
    1.93 +              RETURN;
    1.94 +            END IF;
    1.95 +            "initiative_id_ary" := "initiative_id_ary" || "initiative_id_v";
    1.96 +          END IF;
    1.97 +        END LOOP;
    1.98 +        EXIT WHEN NOT "match_v";
    1.99 +      END LOOP;
   1.100 +      RETURN;
   1.101 +    END;
   1.102 +  $$;
   1.103 +
   1.104 +COMMIT;