1 BEGIN;

2

3 -- update version number:

4 CREATE OR REPLACE VIEW "liquid_feedback_version" AS

5 SELECT * FROM (VALUES ('2.0.4', 2, 0, 4))

6 AS "subquery"("string", "major", "minor", "revision");

7

8 -- add table "session":

9 CREATE TABLE "session" (

10 "ident" TEXT PRIMARY KEY,

11 "additional_secret" TEXT,

12 "expiry" TIMESTAMPTZ NOT NULL DEFAULT now() + '24 hours',

13 "member_id" INT8 REFERENCES "member" ("id") ON DELETE SET NULL,

14 "lang" TEXT );

15 CREATE INDEX "session_expiry_idx" ON "session" ("expiry");

16 COMMENT ON TABLE "session" IS 'Sessions, i.e. for a web-frontend or API layer';

17 COMMENT ON COLUMN "session"."ident" IS 'Secret session identifier (i.e. random string)';

18 COMMENT ON COLUMN "session"."additional_secret" IS 'Additional field to store a secret, which can be used against CSRF attacks';

19 COMMENT ON COLUMN "session"."member_id" IS 'Reference to member, who is logged in';

20 COMMENT ON COLUMN "session"."lang" IS 'Language code of the selected language';

21

22 -- add column "lang" to table "member":

23 ALTER TABLE "member" ADD COLUMN "lang" TEXT;

24 COMMENT ON COLUMN "member"."lang" IS 'Language code of the preferred language of the member';

25

26 -- drop view "pending_notification":

27 DROP VIEW "pending_notification";

28

29 -- remove column "notify_event_id" of table "member":

30 ALTER TABLE "member" DROP COLUMN "notify_event_id";

31

32 -- add table "notification_sent":

33 CREATE TABLE "notification_sent" (

34 "event_id" INT8 NOT NULL );

35 CREATE UNIQUE INDEX "notification_sent_singleton_idx" ON "notification_sent" ((1));

36 COMMENT ON TABLE "notification_sent" IS 'This table stores one row with the last event_id, for which notifications have been sent out';

37 COMMENT ON INDEX "notification_sent_singleton_idx" IS 'This index ensures that "notification_sent" only contains one row maximum.';

38

39 -- add view "selected_event_seen_by_member":

40 CREATE VIEW "selected_event_seen_by_member" AS

41 SELECT

42 "member"."id" AS "seen_by_member_id",

43 CASE WHEN "event"."state" IN (

44 'voting',

45 'finished_without_winner',

46 'finished_with_winner'

47 ) THEN

48 'voting'::"notify_level"

49 ELSE

50 CASE WHEN "event"."state" IN (

51 'verification',

52 'canceled_after_revocation_during_verification',

53 'canceled_no_initiative_admitted'

54 ) THEN

55 'verification'::"notify_level"

56 ELSE

57 CASE WHEN "event"."state" IN (

58 'discussion',

59 'canceled_after_revocation_during_discussion'

60 ) THEN

61 'discussion'::"notify_level"

62 ELSE

63 'all'::"notify_level"

64 END

65 END

66 END AS "notify_level",

67 "event".*

68 FROM "member" CROSS JOIN "event"

69 LEFT JOIN "issue"

70 ON "event"."issue_id" = "issue"."id"

71 LEFT JOIN "membership"

72 ON "member"."id" = "membership"."member_id"

73 AND "issue"."area_id" = "membership"."area_id"

74 LEFT JOIN "interest"

75 ON "member"."id" = "interest"."member_id"

76 AND "event"."issue_id" = "interest"."issue_id"

77 LEFT JOIN "supporter"

78 ON "member"."id" = "supporter"."member_id"

79 AND "event"."initiative_id" = "supporter"."initiative_id"

80 LEFT JOIN "ignored_member"

81 ON "member"."id" = "ignored_member"."member_id"

82 AND "event"."member_id" = "ignored_member"."other_member_id"

83 LEFT JOIN "ignored_initiative"

84 ON "member"."id" = "ignored_initiative"."member_id"

85 AND "event"."initiative_id" = "ignored_initiative"."initiative_id"

86 WHERE (

87 ( "member"."notify_level" >= 'all' ) OR

88 ( "member"."notify_level" >= 'voting' AND

89 "event"."state" IN (

90 'voting',

91 'finished_without_winner',

92 'finished_with_winner' ) ) OR

93 ( "member"."notify_level" >= 'verification' AND

94 "event"."state" IN (

95 'verification',

96 'canceled_after_revocation_during_verification',

97 'canceled_no_initiative_admitted' ) ) OR

98 ( "member"."notify_level" >= 'discussion' AND

99 "event"."state" IN (

100 'discussion',

101 'canceled_after_revocation_during_discussion' ) ) )

102 AND (

103 "supporter"."member_id" NOTNULL OR

104 "interest"."member_id" NOTNULL OR

105 ( "membership"."member_id" NOTNULL AND

106 "event"."event" IN (

107 'issue_state_changed',

108 'initiative_created_in_new_issue',

109 'initiative_created_in_existing_issue',

110 'initiative_revoked' ) ) )

111 AND "ignored_member"."member_id" ISNULL

112 AND "ignored_initiative"."member_id" ISNULL;

113 COMMENT ON VIEW "selected_event_seen_by_member" IS 'Events as seen by a member, depending on its memberships, interests, support and members "notify_level"';

114

115 COMMIT;