liquid_feedback_core
changeset 491:aa94c7dbb20f
New function "get_initiatives_for_notification"
author | jbe |
---|---|
date | Sun Apr 03 19:42:09 2016 +0200 (2016-04-03) |
parents | 16536937933e |
children | 917a65357efb |
files | core.sql |
line diff
1.1 --- a/core.sql Sun Apr 03 18:00:07 2016 +0200 1.2 +++ b/core.sql Sun Apr 03 19:42:09 2016 +0200 1.3 @@ -3093,6 +3093,65 @@ 1.4 1.5 1.6 1.7 +------------------------- 1.8 +-- Notification system -- 1.9 +------------------------- 1.10 + 1.11 +CREATE FUNCTION "get_initiatives_for_notification" 1.12 + ( "member_id_p" "member"."id"%TYPE ) 1.13 + RETURNS SETOF "initiative_for_notification" 1.14 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.15 + DECLARE 1.16 + "result_row" "initiative_for_notification"%ROWTYPE; 1.17 + "last_draft_id_v" "draft"."id"%TYPE; 1.18 + "last_suggestion_id_v" "suggestion"."id"%TYPE; 1.19 + BEGIN 1.20 + PERFORM "require_transaction_isolation"(); 1.21 + PERFORM NULL FROM "member" WHERE "id" = "member_id_p" FOR UPDATE; 1.22 + FOR "result_row" IN 1.23 + SELECT * FROM "initiative_for_notification" 1.24 + WHERE "seen_by_member_id" = "member_id_p" 1.25 + LOOP 1.26 + SELECT "id" INTO "last_draft_id_v" FROM "draft" 1.27 + WHERE "draft"."initiative_id" = "result_row"."id" 1.28 + ORDER BY "id" DESC LIMIT 1; 1.29 + SELECT "id" INTO "last_suggestion_id_v" FROM "suggestion" 1.30 + WHERE "suggestion"."initiative_id" = "result_row"."id" 1.31 + ORDER BY "id" DESC LIMIT 1; 1.32 + INSERT INTO "initiative_notification_sent" 1.33 + ("member_id", "initiative_id", "last_draft_id", "last_suggestion_id") 1.34 + VALUES ( 1.35 + "member_id_p", 1.36 + "result_row"."id", 1.37 + "last_draft_id", 1.38 + "last_suggestion_id" ) 1.39 + ON CONFLICT ("member_id", "initiative_id") DO UPDATE SET 1.40 + "last_draft_id" = CASE 1.41 + WHEN "last_draft_id" > "last_draft_id_v" 1.42 + THEN "last_draft_id" 1.43 + ELSE "last_draft_id_v" 1.44 + END, 1.45 + "last_suggestion_id" = CASE 1.46 + WHEN "last_suggestion_id" > "last_suggestion_id_v" 1.47 + THEN "last_suggestion_id" 1.48 + ELSE "last_suggestion_id_v" 1.49 + END; 1.50 + RETURN NEXT "result_row"; 1.51 + END LOOP; 1.52 + DELETE FROM "initiative_notification_sent" 1.53 + USING "initiative", "issue" 1.54 + WHERE "initiative_notification_sent"."member_id" = "member_id_p" 1.55 + AND "initiative"."id" = "initiative_notification_sent"."initiative_id" 1.56 + AND "issue"."id" = "initiative"."issue_id" 1.57 + AND ( "issue"."closed" NOTNULL OR "issue"."fully_frozen" NOTNULL ); 1.58 + UPDATE "member" SET "notification_counter" = "notification_counter" + 1 1.59 + WHERE "id" = "member_id_p"; 1.60 + RETURN; 1.61 + END; 1.62 + $$; 1.63 + 1.64 + 1.65 + 1.66 ------------------------------------------------------------------------ 1.67 -- Regular tasks, except calculcation of snapshots and voting results -- 1.68 ------------------------------------------------------------------------