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 -- drop NOT NULL constraints on columns "name" and "notify_level"

9 -- in table "member", and add new constraint for "name":

10 ALTER TABLE "member" ALTER COLUMN "notify_level" DROP NOT NULL;

11 ALTER TABLE "member" ALTER COLUMN "name" DROP NOT NULL;

12 ALTER TABLE "member" ADD CONSTRAINT "name_not_null_if_activated" CHECK ("activated" ISNULL OR "name" NOTNULL);

13 COMMENT ON COLUMN "member"."notify_level" IS 'Selects which event notifications are to be sent to the "notify_email" mail address, may be NULL if member did not make any selection yet';

14 COMMENT ON COLUMN "member"."name" IS 'Distinct name of the member, may be NULL if account has not been activated yet';

15

16 -- add table "session":

17 CREATE TABLE "session" (

18 "ident" TEXT PRIMARY KEY,

19 "additional_secret" TEXT,

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

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

22 "lang" TEXT );

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

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

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

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

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

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

29

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

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

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

33

34 -- drop view "pending_notification":

35 DROP VIEW "pending_notification";

36

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

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

39

40 -- add table "notification_sent":

41 CREATE TABLE "notification_sent" (

42 "event_id" INT8 NOT NULL );

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

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

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

46

47 -- add view "selected_event_seen_by_member":

48 CREATE VIEW "selected_event_seen_by_member" AS

49 SELECT

50 "member"."id" AS "seen_by_member_id",

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

52 'voting',

53 'finished_without_winner',

54 'finished_with_winner'

55 ) THEN

56 'voting'::"notify_level"

57 ELSE

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

59 'verification',

60 'canceled_after_revocation_during_verification',

61 'canceled_no_initiative_admitted'

62 ) THEN

63 'verification'::"notify_level"

64 ELSE

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

66 'discussion',

67 'canceled_after_revocation_during_discussion'

68 ) THEN

69 'discussion'::"notify_level"

70 ELSE

71 'all'::"notify_level"

72 END

73 END

74 END AS "notify_level",

75 "event".*

76 FROM "member" CROSS JOIN "event"

77 LEFT JOIN "issue"

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

79 LEFT JOIN "membership"

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

81 AND "issue"."area_id" = "membership"."area_id"

82 LEFT JOIN "interest"

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

84 AND "event"."issue_id" = "interest"."issue_id"

85 LEFT JOIN "supporter"

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

87 AND "event"."initiative_id" = "supporter"."initiative_id"

88 LEFT JOIN "ignored_member"

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

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

91 LEFT JOIN "ignored_initiative"

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

93 AND "event"."initiative_id" = "ignored_initiative"."initiative_id"

94 WHERE (

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

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

97 "event"."state" IN (

98 'voting',

99 'finished_without_winner',

100 'finished_with_winner' ) ) OR

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

102 "event"."state" IN (

103 'verification',

104 'canceled_after_revocation_during_verification',

105 'canceled_no_initiative_admitted' ) ) OR

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

107 "event"."state" IN (

108 'discussion',

109 'canceled_after_revocation_during_discussion' ) ) )

110 AND (

111 "supporter"."member_id" NOTNULL OR

112 "interest"."member_id" NOTNULL OR

113 ( "membership"."member_id" NOTNULL AND

114 "event"."event" IN (

115 'issue_state_changed',

116 'initiative_created_in_new_issue',

117 'initiative_created_in_existing_issue',

118 'initiative_revoked' ) ) )

119 AND "ignored_member"."member_id" ISNULL

120 AND "ignored_initiative"."member_id" ISNULL;

121 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"';

122

123 COMMIT;