| rev |
line source |
|
jbe@224
|
1 BEGIN;
|
|
jbe@224
|
2
|
|
jbe@224
|
3 -- update version number:
|
|
jbe@224
|
4 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
|
|
jbe@224
|
5 SELECT * FROM (VALUES ('2.0.4', 2, 0, 4))
|
|
jbe@224
|
6 AS "subquery"("string", "major", "minor", "revision");
|
|
jbe@224
|
7
|
|
jbe@224
|
8 -- add table "session":
|
|
jbe@224
|
9 CREATE TABLE "session" (
|
|
jbe@224
|
10 "ident" TEXT PRIMARY KEY,
|
|
jbe@224
|
11 "additional_secret" TEXT,
|
|
jbe@224
|
12 "expiry" TIMESTAMPTZ NOT NULL DEFAULT now() + '24 hours',
|
|
jbe@224
|
13 "member_id" INT8 REFERENCES "member" ("id") ON DELETE SET NULL,
|
|
jbe@224
|
14 "lang" TEXT );
|
|
jbe@224
|
15 CREATE INDEX "session_expiry_idx" ON "session" ("expiry");
|
|
jbe@224
|
16 COMMENT ON TABLE "session" IS 'Sessions, i.e. for a web-frontend or API layer';
|
|
jbe@224
|
17 COMMENT ON COLUMN "session"."ident" IS 'Secret session identifier (i.e. random string)';
|
|
jbe@224
|
18 COMMENT ON COLUMN "session"."additional_secret" IS 'Additional field to store a secret, which can be used against CSRF attacks';
|
|
jbe@224
|
19 COMMENT ON COLUMN "session"."member_id" IS 'Reference to member, who is logged in';
|
|
jbe@224
|
20 COMMENT ON COLUMN "session"."lang" IS 'Language code of the selected language';
|
|
jbe@224
|
21
|
|
jbe@224
|
22 -- add column "lang" to table "member":
|
|
jbe@224
|
23 ALTER TABLE "member" ADD COLUMN "lang" TEXT;
|
|
jbe@224
|
24 COMMENT ON COLUMN "member"."lang" IS 'Language code of the preferred language of the member';
|
|
jbe@224
|
25
|
|
jbe@224
|
26 -- drop view "pending_notification":
|
|
jbe@224
|
27 DROP VIEW "pending_notification";
|
|
jbe@224
|
28
|
|
jbe@224
|
29 -- remove column "notify_event_id" of table "member":
|
|
jbe@224
|
30 ALTER TABLE "member" DROP COLUMN "notify_event_id";
|
|
jbe@224
|
31
|
|
jbe@224
|
32 -- add table "notification_sent":
|
|
jbe@224
|
33 CREATE TABLE "notification_sent" (
|
|
jbe@224
|
34 "event_id" INT8 NOT NULL );
|
|
jbe@224
|
35 CREATE UNIQUE INDEX "notification_sent_singleton_idx" ON "notification_sent" ((1));
|
|
jbe@224
|
36 COMMENT ON TABLE "notification_sent" IS 'This table stores one row with the last event_id, for which notifications have been sent out';
|
|
jbe@224
|
37 COMMENT ON INDEX "notification_sent_singleton_idx" IS 'This index ensures that "notification_sent" only contains one row maximum.';
|
|
jbe@224
|
38
|
|
jbe@224
|
39 -- add view "selected_event_seen_by_member":
|
|
jbe@224
|
40 CREATE VIEW "selected_event_seen_by_member" AS
|
|
jbe@224
|
41 SELECT
|
|
jbe@224
|
42 "member"."id" AS "seen_by_member_id",
|
|
jbe@224
|
43 CASE WHEN "event"."state" IN (
|
|
jbe@224
|
44 'voting',
|
|
jbe@224
|
45 'finished_without_winner',
|
|
jbe@224
|
46 'finished_with_winner'
|
|
jbe@224
|
47 ) THEN
|
|
jbe@224
|
48 'voting'::"notify_level"
|
|
jbe@224
|
49 ELSE
|
|
jbe@224
|
50 CASE WHEN "event"."state" IN (
|
|
jbe@224
|
51 'verification',
|
|
jbe@224
|
52 'canceled_after_revocation_during_verification',
|
|
jbe@224
|
53 'canceled_no_initiative_admitted'
|
|
jbe@224
|
54 ) THEN
|
|
jbe@224
|
55 'verification'::"notify_level"
|
|
jbe@224
|
56 ELSE
|
|
jbe@224
|
57 CASE WHEN "event"."state" IN (
|
|
jbe@224
|
58 'discussion',
|
|
jbe@224
|
59 'canceled_after_revocation_during_discussion'
|
|
jbe@224
|
60 ) THEN
|
|
jbe@224
|
61 'discussion'::"notify_level"
|
|
jbe@224
|
62 ELSE
|
|
jbe@224
|
63 'all'::"notify_level"
|
|
jbe@224
|
64 END
|
|
jbe@224
|
65 END
|
|
jbe@224
|
66 END AS "notify_level",
|
|
jbe@224
|
67 "event".*
|
|
jbe@224
|
68 FROM "member" CROSS JOIN "event"
|
|
jbe@224
|
69 LEFT JOIN "issue"
|
|
jbe@224
|
70 ON "event"."issue_id" = "issue"."id"
|
|
jbe@224
|
71 LEFT JOIN "membership"
|
|
jbe@224
|
72 ON "member"."id" = "membership"."member_id"
|
|
jbe@224
|
73 AND "issue"."area_id" = "membership"."area_id"
|
|
jbe@224
|
74 LEFT JOIN "interest"
|
|
jbe@224
|
75 ON "member"."id" = "interest"."member_id"
|
|
jbe@224
|
76 AND "event"."issue_id" = "interest"."issue_id"
|
|
jbe@224
|
77 LEFT JOIN "supporter"
|
|
jbe@224
|
78 ON "member"."id" = "supporter"."member_id"
|
|
jbe@224
|
79 AND "event"."initiative_id" = "supporter"."initiative_id"
|
|
jbe@224
|
80 LEFT JOIN "ignored_member"
|
|
jbe@224
|
81 ON "member"."id" = "ignored_member"."member_id"
|
|
jbe@224
|
82 AND "event"."member_id" = "ignored_member"."other_member_id"
|
|
jbe@224
|
83 LEFT JOIN "ignored_initiative"
|
|
jbe@224
|
84 ON "member"."id" = "ignored_initiative"."member_id"
|
|
jbe@224
|
85 AND "event"."initiative_id" = "ignored_initiative"."initiative_id"
|
|
jbe@224
|
86 WHERE (
|
|
jbe@224
|
87 ( "member"."notify_level" >= 'all' ) OR
|
|
jbe@224
|
88 ( "member"."notify_level" >= 'voting' AND
|
|
jbe@224
|
89 "event"."state" IN (
|
|
jbe@224
|
90 'voting',
|
|
jbe@224
|
91 'finished_without_winner',
|
|
jbe@224
|
92 'finished_with_winner' ) ) OR
|
|
jbe@224
|
93 ( "member"."notify_level" >= 'verification' AND
|
|
jbe@224
|
94 "event"."state" IN (
|
|
jbe@224
|
95 'verification',
|
|
jbe@224
|
96 'canceled_after_revocation_during_verification',
|
|
jbe@224
|
97 'canceled_no_initiative_admitted' ) ) OR
|
|
jbe@224
|
98 ( "member"."notify_level" >= 'discussion' AND
|
|
jbe@224
|
99 "event"."state" IN (
|
|
jbe@224
|
100 'discussion',
|
|
jbe@224
|
101 'canceled_after_revocation_during_discussion' ) ) )
|
|
jbe@224
|
102 AND (
|
|
jbe@224
|
103 "supporter"."member_id" NOTNULL OR
|
|
jbe@224
|
104 "interest"."member_id" NOTNULL OR
|
|
jbe@224
|
105 ( "membership"."member_id" NOTNULL AND
|
|
jbe@224
|
106 "event"."event" IN (
|
|
jbe@224
|
107 'issue_state_changed',
|
|
jbe@224
|
108 'initiative_created_in_new_issue',
|
|
jbe@224
|
109 'initiative_created_in_existing_issue',
|
|
jbe@224
|
110 'initiative_revoked' ) ) )
|
|
jbe@224
|
111 AND "ignored_member"."member_id" ISNULL
|
|
jbe@224
|
112 AND "ignored_initiative"."member_id" ISNULL;
|
|
jbe@224
|
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"';
|
|
jbe@224
|
114
|
|
jbe@224
|
115 COMMIT;
|