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;