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@225
|
8 -- drop NOT NULL constraints on columns "name" and "notify_level"
|
jbe@225
|
9 -- in table "member", and add new constraint for "name":
|
jbe@225
|
10 ALTER TABLE "member" ALTER COLUMN "notify_level" DROP NOT NULL;
|
jbe@225
|
11 ALTER TABLE "member" ALTER COLUMN "name" DROP NOT NULL;
|
jbe@225
|
12 ALTER TABLE "member" ADD CONSTRAINT "name_not_null_if_activated" CHECK ("activated" ISNULL OR "name" NOTNULL);
|
jbe@225
|
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';
|
jbe@225
|
14 COMMENT ON COLUMN "member"."name" IS 'Distinct name of the member, may be NULL if account has not been activated yet';
|
jbe@225
|
15
|
jbe@224
|
16 -- add table "session":
|
jbe@224
|
17 CREATE TABLE "session" (
|
jbe@224
|
18 "ident" TEXT PRIMARY KEY,
|
jbe@224
|
19 "additional_secret" TEXT,
|
jbe@224
|
20 "expiry" TIMESTAMPTZ NOT NULL DEFAULT now() + '24 hours',
|
jbe@224
|
21 "member_id" INT8 REFERENCES "member" ("id") ON DELETE SET NULL,
|
jbe@224
|
22 "lang" TEXT );
|
jbe@224
|
23 CREATE INDEX "session_expiry_idx" ON "session" ("expiry");
|
jbe@224
|
24 COMMENT ON TABLE "session" IS 'Sessions, i.e. for a web-frontend or API layer';
|
jbe@224
|
25 COMMENT ON COLUMN "session"."ident" IS 'Secret session identifier (i.e. random string)';
|
jbe@224
|
26 COMMENT ON COLUMN "session"."additional_secret" IS 'Additional field to store a secret, which can be used against CSRF attacks';
|
jbe@224
|
27 COMMENT ON COLUMN "session"."member_id" IS 'Reference to member, who is logged in';
|
jbe@224
|
28 COMMENT ON COLUMN "session"."lang" IS 'Language code of the selected language';
|
jbe@224
|
29
|
jbe@224
|
30 -- add column "lang" to table "member":
|
jbe@224
|
31 ALTER TABLE "member" ADD COLUMN "lang" TEXT;
|
jbe@224
|
32 COMMENT ON COLUMN "member"."lang" IS 'Language code of the preferred language of the member';
|
jbe@224
|
33
|
jbe@224
|
34 -- drop view "pending_notification":
|
jbe@224
|
35 DROP VIEW "pending_notification";
|
jbe@224
|
36
|
jbe@224
|
37 -- remove column "notify_event_id" of table "member":
|
jbe@224
|
38 ALTER TABLE "member" DROP COLUMN "notify_event_id";
|
jbe@224
|
39
|
jbe@224
|
40 -- add table "notification_sent":
|
jbe@224
|
41 CREATE TABLE "notification_sent" (
|
jbe@224
|
42 "event_id" INT8 NOT NULL );
|
jbe@224
|
43 CREATE UNIQUE INDEX "notification_sent_singleton_idx" ON "notification_sent" ((1));
|
jbe@224
|
44 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
|
45 COMMENT ON INDEX "notification_sent_singleton_idx" IS 'This index ensures that "notification_sent" only contains one row maximum.';
|
jbe@224
|
46
|
jbe@224
|
47 -- add view "selected_event_seen_by_member":
|
jbe@224
|
48 CREATE VIEW "selected_event_seen_by_member" AS
|
jbe@224
|
49 SELECT
|
jbe@224
|
50 "member"."id" AS "seen_by_member_id",
|
jbe@224
|
51 CASE WHEN "event"."state" IN (
|
jbe@224
|
52 'voting',
|
jbe@224
|
53 'finished_without_winner',
|
jbe@224
|
54 'finished_with_winner'
|
jbe@224
|
55 ) THEN
|
jbe@224
|
56 'voting'::"notify_level"
|
jbe@224
|
57 ELSE
|
jbe@224
|
58 CASE WHEN "event"."state" IN (
|
jbe@224
|
59 'verification',
|
jbe@224
|
60 'canceled_after_revocation_during_verification',
|
jbe@224
|
61 'canceled_no_initiative_admitted'
|
jbe@224
|
62 ) THEN
|
jbe@224
|
63 'verification'::"notify_level"
|
jbe@224
|
64 ELSE
|
jbe@224
|
65 CASE WHEN "event"."state" IN (
|
jbe@224
|
66 'discussion',
|
jbe@224
|
67 'canceled_after_revocation_during_discussion'
|
jbe@224
|
68 ) THEN
|
jbe@224
|
69 'discussion'::"notify_level"
|
jbe@224
|
70 ELSE
|
jbe@224
|
71 'all'::"notify_level"
|
jbe@224
|
72 END
|
jbe@224
|
73 END
|
jbe@224
|
74 END AS "notify_level",
|
jbe@224
|
75 "event".*
|
jbe@224
|
76 FROM "member" CROSS JOIN "event"
|
jbe@224
|
77 LEFT JOIN "issue"
|
jbe@224
|
78 ON "event"."issue_id" = "issue"."id"
|
jbe@224
|
79 LEFT JOIN "membership"
|
jbe@224
|
80 ON "member"."id" = "membership"."member_id"
|
jbe@224
|
81 AND "issue"."area_id" = "membership"."area_id"
|
jbe@224
|
82 LEFT JOIN "interest"
|
jbe@224
|
83 ON "member"."id" = "interest"."member_id"
|
jbe@224
|
84 AND "event"."issue_id" = "interest"."issue_id"
|
jbe@224
|
85 LEFT JOIN "supporter"
|
jbe@224
|
86 ON "member"."id" = "supporter"."member_id"
|
jbe@224
|
87 AND "event"."initiative_id" = "supporter"."initiative_id"
|
jbe@224
|
88 LEFT JOIN "ignored_member"
|
jbe@224
|
89 ON "member"."id" = "ignored_member"."member_id"
|
jbe@224
|
90 AND "event"."member_id" = "ignored_member"."other_member_id"
|
jbe@224
|
91 LEFT JOIN "ignored_initiative"
|
jbe@224
|
92 ON "member"."id" = "ignored_initiative"."member_id"
|
jbe@224
|
93 AND "event"."initiative_id" = "ignored_initiative"."initiative_id"
|
jbe@224
|
94 WHERE (
|
jbe@224
|
95 ( "member"."notify_level" >= 'all' ) OR
|
jbe@224
|
96 ( "member"."notify_level" >= 'voting' AND
|
jbe@224
|
97 "event"."state" IN (
|
jbe@224
|
98 'voting',
|
jbe@224
|
99 'finished_without_winner',
|
jbe@224
|
100 'finished_with_winner' ) ) OR
|
jbe@224
|
101 ( "member"."notify_level" >= 'verification' AND
|
jbe@224
|
102 "event"."state" IN (
|
jbe@224
|
103 'verification',
|
jbe@224
|
104 'canceled_after_revocation_during_verification',
|
jbe@224
|
105 'canceled_no_initiative_admitted' ) ) OR
|
jbe@224
|
106 ( "member"."notify_level" >= 'discussion' AND
|
jbe@224
|
107 "event"."state" IN (
|
jbe@224
|
108 'discussion',
|
jbe@224
|
109 'canceled_after_revocation_during_discussion' ) ) )
|
jbe@224
|
110 AND (
|
jbe@224
|
111 "supporter"."member_id" NOTNULL OR
|
jbe@224
|
112 "interest"."member_id" NOTNULL OR
|
jbe@224
|
113 ( "membership"."member_id" NOTNULL AND
|
jbe@224
|
114 "event"."event" IN (
|
jbe@224
|
115 'issue_state_changed',
|
jbe@224
|
116 'initiative_created_in_new_issue',
|
jbe@224
|
117 'initiative_created_in_existing_issue',
|
jbe@224
|
118 'initiative_revoked' ) ) )
|
jbe@224
|
119 AND "ignored_member"."member_id" ISNULL
|
jbe@224
|
120 AND "ignored_initiative"."member_id" ISNULL;
|
jbe@224
|
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"';
|
jbe@224
|
122
|
jbe@226
|
123 -- delete non-activated members in function "delete_private_data":
|
jbe@226
|
124 CREATE OR REPLACE FUNCTION "delete_private_data"()
|
jbe@226
|
125 RETURNS VOID
|
jbe@226
|
126 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@226
|
127 BEGIN
|
jbe@226
|
128 DELETE FROM "member" WHERE "activated" ISNULL;
|
jbe@226
|
129 UPDATE "member" SET
|
jbe@226
|
130 "invite_code" = NULL,
|
jbe@226
|
131 "last_login" = NULL,
|
jbe@226
|
132 "login" = NULL,
|
jbe@226
|
133 "password" = NULL,
|
jbe@226
|
134 "notify_email" = NULL,
|
jbe@226
|
135 "notify_email_unconfirmed" = NULL,
|
jbe@226
|
136 "notify_email_secret" = NULL,
|
jbe@226
|
137 "notify_email_secret_expiry" = NULL,
|
jbe@226
|
138 "notify_email_lock_expiry" = NULL,
|
jbe@226
|
139 "password_reset_secret" = NULL,
|
jbe@226
|
140 "password_reset_secret_expiry" = NULL,
|
jbe@226
|
141 "organizational_unit" = NULL,
|
jbe@226
|
142 "internal_posts" = NULL,
|
jbe@226
|
143 "realname" = NULL,
|
jbe@226
|
144 "birthday" = NULL,
|
jbe@226
|
145 "address" = NULL,
|
jbe@226
|
146 "email" = NULL,
|
jbe@226
|
147 "xmpp_address" = NULL,
|
jbe@226
|
148 "website" = NULL,
|
jbe@226
|
149 "phone" = NULL,
|
jbe@226
|
150 "mobile_phone" = NULL,
|
jbe@226
|
151 "profession" = NULL,
|
jbe@226
|
152 "external_memberships" = NULL,
|
jbe@226
|
153 "external_posts" = NULL,
|
jbe@226
|
154 "statement" = NULL;
|
jbe@226
|
155 -- "text_search_data" is updated by triggers
|
jbe@226
|
156 DELETE FROM "setting";
|
jbe@226
|
157 DELETE FROM "setting_map";
|
jbe@226
|
158 DELETE FROM "member_relation_setting";
|
jbe@226
|
159 DELETE FROM "member_image";
|
jbe@226
|
160 DELETE FROM "contact";
|
jbe@226
|
161 DELETE FROM "ignored_member";
|
jbe@226
|
162 DELETE FROM "area_setting";
|
jbe@226
|
163 DELETE FROM "issue_setting";
|
jbe@226
|
164 DELETE FROM "ignored_initiative";
|
jbe@226
|
165 DELETE FROM "initiative_setting";
|
jbe@226
|
166 DELETE FROM "suggestion_setting";
|
jbe@226
|
167 DELETE FROM "non_voter";
|
jbe@226
|
168 DELETE FROM "direct_voter" USING "issue"
|
jbe@226
|
169 WHERE "direct_voter"."issue_id" = "issue"."id"
|
jbe@226
|
170 AND "issue"."closed" ISNULL;
|
jbe@226
|
171 RETURN;
|
jbe@226
|
172 END;
|
jbe@226
|
173 $$;
|
jbe@226
|
174
|
jbe@224
|
175 COMMIT;
|