liquid_feedback_core
annotate update/core-update.v3.2.0-v3.2.1.sql @ 532:5855ff9e5c8f
Several changes/additions for upcoming major release
- OAuth 2.0 support
- storing profiles as JSON document
- removed subject area membership
- revised snapshot system
- additional issue limiter (dynamic quorum in subject area)
- extended event logging in "event" table
- OAuth 2.0 support
- storing profiles as JSON document
- removed subject area membership
- revised snapshot system
- additional issue limiter (dynamic quorum in subject area)
- extended event logging in "event" table
author | jbe |
---|---|
date | Thu Mar 30 19:42:38 2017 +0200 (2017-03-30) |
parents | e4f619e87664 |
children |
rev | line source |
---|---|
jbe@517 | 1 BEGIN; |
jbe@517 | 2 |
jbe@517 | 3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS |
jbe@517 | 4 SELECT * FROM (VALUES ('3.2.1', 3, 2, 1)) |
jbe@517 | 5 AS "subquery"("string", "major", "minor", "revision"); |
jbe@517 | 6 |
jbe@517 | 7 CREATE OR REPLACE FUNCTION "get_initiatives_for_notification" |
jbe@517 | 8 ( "recipient_id_p" "member"."id"%TYPE ) |
jbe@517 | 9 RETURNS SETOF "initiative_for_notification" |
jbe@517 | 10 LANGUAGE 'plpgsql' VOLATILE AS $$ |
jbe@517 | 11 DECLARE |
jbe@517 | 12 "result_row" "initiative_for_notification"%ROWTYPE; |
jbe@517 | 13 "last_draft_id_v" "draft"."id"%TYPE; |
jbe@517 | 14 "last_suggestion_id_v" "suggestion"."id"%TYPE; |
jbe@517 | 15 BEGIN |
jbe@517 | 16 PERFORM "require_transaction_isolation"(); |
jbe@517 | 17 PERFORM NULL FROM "member" WHERE "id" = "recipient_id_p" FOR UPDATE; |
jbe@517 | 18 FOR "result_row" IN |
jbe@517 | 19 SELECT * FROM "initiative_for_notification" |
jbe@517 | 20 WHERE "recipient_id" = "recipient_id_p" |
jbe@517 | 21 LOOP |
jbe@517 | 22 SELECT "id" INTO "last_draft_id_v" FROM "draft" |
jbe@517 | 23 WHERE "draft"."initiative_id" = "result_row"."initiative_id" |
jbe@517 | 24 ORDER BY "id" DESC LIMIT 1; |
jbe@517 | 25 SELECT "id" INTO "last_suggestion_id_v" FROM "suggestion" |
jbe@517 | 26 WHERE "suggestion"."initiative_id" = "result_row"."initiative_id" |
jbe@517 | 27 ORDER BY "id" DESC LIMIT 1; |
jbe@517 | 28 /* compatibility with PostgreSQL 9.1 */ |
jbe@517 | 29 DELETE FROM "notification_initiative_sent" |
jbe@517 | 30 WHERE "member_id" = "recipient_id_p" |
jbe@517 | 31 AND "initiative_id" = "result_row"."initiative_id"; |
jbe@517 | 32 INSERT INTO "notification_initiative_sent" |
jbe@517 | 33 ("member_id", "initiative_id", "last_draft_id", "last_suggestion_id") |
jbe@517 | 34 VALUES ( |
jbe@517 | 35 "recipient_id_p", |
jbe@517 | 36 "result_row"."initiative_id", |
jbe@517 | 37 "last_draft_id_v", |
jbe@517 | 38 "last_suggestion_id_v" ); |
jbe@517 | 39 /* TODO: use alternative code below, requires PostgreSQL 9.5 or higher |
jbe@517 | 40 INSERT INTO "notification_initiative_sent" |
jbe@517 | 41 ("member_id", "initiative_id", "last_draft_id", "last_suggestion_id") |
jbe@517 | 42 VALUES ( |
jbe@517 | 43 "recipient_id_p", |
jbe@517 | 44 "result_row"."initiative_id", |
jbe@517 | 45 "last_draft_id_v", |
jbe@517 | 46 "last_suggestion_id_v" ) |
jbe@517 | 47 ON CONFLICT ("member_id", "initiative_id") DO UPDATE SET |
jbe@517 | 48 "last_draft_id" = "last_draft_id_v", |
jbe@517 | 49 "last_suggestion_id" = "last_suggestion_id_v"; |
jbe@517 | 50 */ |
jbe@517 | 51 RETURN NEXT "result_row"; |
jbe@517 | 52 END LOOP; |
jbe@517 | 53 DELETE FROM "notification_initiative_sent" |
jbe@517 | 54 USING "initiative", "issue" |
jbe@517 | 55 WHERE "notification_initiative_sent"."member_id" = "recipient_id_p" |
jbe@517 | 56 AND "initiative"."id" = "notification_initiative_sent"."initiative_id" |
jbe@517 | 57 AND "issue"."id" = "initiative"."issue_id" |
jbe@517 | 58 AND ( "issue"."closed" NOTNULL OR "issue"."fully_frozen" NOTNULL ); |
jbe@517 | 59 UPDATE "member" SET |
jbe@517 | 60 "notification_counter" = "notification_counter" + 1, |
jbe@517 | 61 "notification_sent" = now() |
jbe@517 | 62 WHERE "id" = "recipient_id_p"; |
jbe@517 | 63 RETURN; |
jbe@517 | 64 END; |
jbe@517 | 65 $$; |
jbe@517 | 66 |
jbe@517 | 67 COMMIT; |