liquid_feedback_core

changeset 520:053190248598

Allow a value of zero as "notification_sample_size" to disable featured initiatives
author jbe
date Wed May 04 21:44:18 2016 +0200 (2016-05-04)
parents b1a7ebf67470
children 65bd17395c28
files core.sql update/core-update.v3.2.1-v3.2.2.sql
line diff
     1.1 --- a/core.sql	Sat Apr 30 17:50:48 2016 +0200
     1.2 +++ b/core.sql	Wed May 04 21:44:18 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.1', 3, 2, 1))
     1.8 +  SELECT * FROM (VALUES ('3.2.2', 3, 2, 2))
     1.9    AS "subquery"("string", "major", "minor", "revision");
    1.10  
    1.11  
    1.12 @@ -2451,6 +2451,9 @@
    1.13        SELECT "notification_counter", "notification_sample_size"
    1.14          INTO "counter_v", "sample_size_v"
    1.15          FROM "member" WHERE "id" = "recipient_id_p";
    1.16 +      IF COALESCE("sample_size_v" <= 0, TRUE) THEN
    1.17 +        RETURN;
    1.18 +      END IF;
    1.19        "initiative_id_ary" := '{}';
    1.20        LOOP
    1.21          "match_v" := FALSE;
     2.1 --- /dev/null	Thu Jan 01 00:00:00 1970 +0000
     2.2 +++ b/update/core-update.v3.2.1-v3.2.2.sql	Wed May 04 21:44:18 2016 +0200
     2.3 @@ -0,0 +1,101 @@
     2.4 +BEGIN;
     2.5 +
     2.6 +CREATE OR REPLACE VIEW "liquid_feedback_version" AS
     2.7 +  SELECT * FROM (VALUES ('3.2.2', 3, 2, 2))
     2.8 +  AS "subquery"("string", "major", "minor", "revision");
     2.9 +
    2.10 +CREATE OR REPLACE FUNCTION "featured_initiative"
    2.11 +  ( "recipient_id_p" "member"."id"%TYPE,
    2.12 +    "area_id_p"      "area"."id"%TYPE )
    2.13 +  RETURNS SETOF "initiative"."id"%TYPE
    2.14 +  LANGUAGE 'plpgsql' STABLE AS $$
    2.15 +    DECLARE
    2.16 +      "counter_v"         "member"."notification_counter"%TYPE;
    2.17 +      "sample_size_v"     "member"."notification_sample_size"%TYPE;
    2.18 +      "initiative_id_ary" INT4[];  --"initiative"."id"%TYPE[]
    2.19 +      "match_v"           BOOLEAN;
    2.20 +      "member_id_v"       "member"."id"%TYPE;
    2.21 +      "seed_v"            TEXT;
    2.22 +      "initiative_id_v"   "initiative"."id"%TYPE;
    2.23 +    BEGIN
    2.24 +      SELECT "notification_counter", "notification_sample_size"
    2.25 +        INTO "counter_v", "sample_size_v"
    2.26 +        FROM "member" WHERE "id" = "recipient_id_p";
    2.27 +      IF COALESCE("sample_size_v" <= 0, TRUE) THEN
    2.28 +        RETURN;
    2.29 +      END IF;
    2.30 +      "initiative_id_ary" := '{}';
    2.31 +      LOOP
    2.32 +        "match_v" := FALSE;
    2.33 +        FOR "member_id_v", "seed_v" IN
    2.34 +          SELECT * FROM (
    2.35 +            SELECT DISTINCT
    2.36 +              "supporter"."member_id",
    2.37 +              md5(
    2.38 +                "recipient_id_p" || '-' ||
    2.39 +                "counter_v"      || '-' ||
    2.40 +                "area_id_p"      || '-' ||
    2.41 +                "supporter"."member_id"
    2.42 +              ) AS "seed"
    2.43 +            FROM "supporter"
    2.44 +            JOIN "initiative" ON "initiative"."id" = "supporter"."initiative_id"
    2.45 +            JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
    2.46 +            WHERE "supporter"."member_id" != "recipient_id_p"
    2.47 +            AND "issue"."area_id" = "area_id_p"
    2.48 +            AND "issue"."state" IN ('admission', 'discussion', 'verification')
    2.49 +          ) AS "subquery"
    2.50 +          ORDER BY "seed"
    2.51 +        LOOP
    2.52 +          SELECT "initiative"."id" INTO "initiative_id_v"
    2.53 +            FROM "initiative"
    2.54 +            JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
    2.55 +            JOIN "area" ON "area"."id" = "issue"."area_id"
    2.56 +            JOIN "supporter" ON "supporter"."initiative_id" = "initiative"."id"
    2.57 +            LEFT JOIN "supporter" AS "self_support" ON
    2.58 +              "self_support"."initiative_id" = "initiative"."id" AND
    2.59 +              "self_support"."member_id" = "recipient_id_p"
    2.60 +            LEFT JOIN "privilege" ON
    2.61 +              "privilege"."member_id" = "recipient_id_p" AND
    2.62 +              "privilege"."unit_id" = "area"."unit_id" AND
    2.63 +              "privilege"."voting_right" = TRUE
    2.64 +            LEFT JOIN "subscription" ON
    2.65 +              "subscription"."member_id" = "recipient_id_p" AND
    2.66 +              "subscription"."unit_id" = "area"."unit_id"
    2.67 +            LEFT JOIN "ignored_initiative" ON
    2.68 +              "ignored_initiative"."member_id" = "recipient_id_p" AND
    2.69 +              "ignored_initiative"."initiative_id" = "initiative"."id"
    2.70 +            WHERE "supporter"."member_id" = "member_id_v"
    2.71 +            AND "issue"."area_id" = "area_id_p"
    2.72 +            AND "issue"."state" IN ('admission', 'discussion', 'verification')
    2.73 +            AND "initiative"."revoked" ISNULL
    2.74 +            AND "self_support"."member_id" ISNULL
    2.75 +            AND NOT "initiative_id_ary" @> ARRAY["initiative"."id"]
    2.76 +            AND (
    2.77 +              "privilege"."member_id" NOTNULL OR
    2.78 +              "subscription"."member_id" NOTNULL )
    2.79 +            AND "ignored_initiative"."member_id" ISNULL
    2.80 +            AND NOT EXISTS (
    2.81 +              SELECT NULL FROM "draft"
    2.82 +              JOIN "ignored_member" ON
    2.83 +                "ignored_member"."member_id" = "recipient_id_p" AND
    2.84 +                "ignored_member"."other_member_id" = "draft"."author_id"
    2.85 +              WHERE "draft"."initiative_id" = "initiative"."id"
    2.86 +            )
    2.87 +            ORDER BY md5("seed_v" || '-' || "initiative"."id")
    2.88 +            LIMIT 1;
    2.89 +          IF FOUND THEN
    2.90 +            "match_v" := TRUE;
    2.91 +            RETURN NEXT "initiative_id_v";
    2.92 +            IF array_length("initiative_id_ary", 1) + 1 >= "sample_size_v" THEN
    2.93 +              RETURN;
    2.94 +            END IF;
    2.95 +            "initiative_id_ary" := "initiative_id_ary" || "initiative_id_v";
    2.96 +          END IF;
    2.97 +        END LOOP;
    2.98 +        EXIT WHEN NOT "match_v";
    2.99 +      END LOOP;
   2.100 +      RETURN;
   2.101 +    END;
   2.102 +  $$;
   2.103 +
   2.104 +COMMIT;

Impressum / About Us