liquid_feedback_core
view update/core-update.v2.2.3-v2.2.4.sql @ 389:582d270d2653
Added column "admin_notice" to "issue" table, and value 'canceled_by_admin' to "issue_state" type
| author | jbe |
|---|---|
| date | Sun Aug 11 21:34:58 2013 +0200 (2013-08-11) |
| parents | ae69cf82c05f |
| children |
line source
1 BEGIN;
3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
4 SELECT * FROM (VALUES ('2.2.4', 2, 2, 4))
5 AS "subquery"("string", "major", "minor", "revision");
8 ALTER TABLE "member" ADD COLUMN "last_delegation_check" TIMESTAMPTZ;
9 ALTER TABLE "member" ADD COLUMN "login_recovery_expiry" TIMESTAMPTZ;
11 COMMENT ON COLUMN "member"."last_delegation_check" IS 'Timestamp of last delegation check (i.e. confirmation of all unit and area delegations)';
12 COMMENT ON COLUMN "member"."login_recovery_expiry" IS 'Date/time after which another login recovery attempt is allowed';
13 COMMENT ON COLUMN "member"."password_reset_secret" IS 'Secret string sent via e-mail for password recovery';
14 COMMENT ON COLUMN "member"."password_reset_secret_expiry" IS 'Date/time until the password recovery secret is valid, and date/time after which another password recovery attempt is allowed';
16 ALTER TABLE "session" ADD COLUMN "needs_delegation_check" BOOLEAN NOT NULL DEFAULT FALSE;
17 COMMENT ON COLUMN "session"."needs_delegation_check" IS 'Set to TRUE, if member must perform a delegation check to proceed with login; see column "last_delegation_check" in "member" table';
19 CREATE OR REPLACE VIEW "event_seen_by_member" AS
20 SELECT
21 "member"."id" AS "seen_by_member_id",
22 CASE WHEN "event"."state" IN (
23 'voting',
24 'finished_without_winner',
25 'finished_with_winner'
26 ) THEN
27 'voting'::"notify_level"
28 ELSE
29 CASE WHEN "event"."state" IN (
30 'verification',
31 'canceled_after_revocation_during_verification',
32 'canceled_no_initiative_admitted'
33 ) THEN
34 'verification'::"notify_level"
35 ELSE
36 CASE WHEN "event"."state" IN (
37 'discussion',
38 'canceled_after_revocation_during_discussion'
39 ) THEN
40 'discussion'::"notify_level"
41 ELSE
42 'all'::"notify_level"
43 END
44 END
45 END AS "notify_level",
46 "event".*
47 FROM "member" CROSS JOIN "event"
48 LEFT JOIN "issue"
49 ON "event"."issue_id" = "issue"."id"
50 LEFT JOIN "membership"
51 ON "member"."id" = "membership"."member_id"
52 AND "issue"."area_id" = "membership"."area_id"
53 LEFT JOIN "interest"
54 ON "member"."id" = "interest"."member_id"
55 AND "event"."issue_id" = "interest"."issue_id"
56 LEFT JOIN "ignored_member"
57 ON "member"."id" = "ignored_member"."member_id"
58 AND "event"."member_id" = "ignored_member"."other_member_id"
59 LEFT JOIN "ignored_initiative"
60 ON "member"."id" = "ignored_initiative"."member_id"
61 AND "event"."initiative_id" = "ignored_initiative"."initiative_id"
62 WHERE (
63 "interest"."member_id" NOTNULL OR
64 ( "membership"."member_id" NOTNULL AND
65 "event"."event" IN (
66 'issue_state_changed',
67 'initiative_created_in_new_issue',
68 'initiative_created_in_existing_issue',
69 'initiative_revoked' ) ) )
70 AND "ignored_member"."member_id" ISNULL
71 AND "ignored_initiative"."member_id" ISNULL;
73 CREATE OR REPLACE VIEW "selected_event_seen_by_member" AS
74 SELECT
75 "member"."id" AS "seen_by_member_id",
76 CASE WHEN "event"."state" IN (
77 'voting',
78 'finished_without_winner',
79 'finished_with_winner'
80 ) THEN
81 'voting'::"notify_level"
82 ELSE
83 CASE WHEN "event"."state" IN (
84 'verification',
85 'canceled_after_revocation_during_verification',
86 'canceled_no_initiative_admitted'
87 ) THEN
88 'verification'::"notify_level"
89 ELSE
90 CASE WHEN "event"."state" IN (
91 'discussion',
92 'canceled_after_revocation_during_discussion'
93 ) THEN
94 'discussion'::"notify_level"
95 ELSE
96 'all'::"notify_level"
97 END
98 END
99 END AS "notify_level",
100 "event".*
101 FROM "member" CROSS JOIN "event"
102 LEFT JOIN "issue"
103 ON "event"."issue_id" = "issue"."id"
104 LEFT JOIN "membership"
105 ON "member"."id" = "membership"."member_id"
106 AND "issue"."area_id" = "membership"."area_id"
107 LEFT JOIN "interest"
108 ON "member"."id" = "interest"."member_id"
109 AND "event"."issue_id" = "interest"."issue_id"
110 LEFT JOIN "ignored_member"
111 ON "member"."id" = "ignored_member"."member_id"
112 AND "event"."member_id" = "ignored_member"."other_member_id"
113 LEFT JOIN "ignored_initiative"
114 ON "member"."id" = "ignored_initiative"."member_id"
115 AND "event"."initiative_id" = "ignored_initiative"."initiative_id"
116 WHERE (
117 ( "member"."notify_level" >= 'all' ) OR
118 ( "member"."notify_level" >= 'voting' AND
119 "event"."state" IN (
120 'voting',
121 'finished_without_winner',
122 'finished_with_winner' ) ) OR
123 ( "member"."notify_level" >= 'verification' AND
124 "event"."state" IN (
125 'verification',
126 'canceled_after_revocation_during_verification',
127 'canceled_no_initiative_admitted' ) ) OR
128 ( "member"."notify_level" >= 'discussion' AND
129 "event"."state" IN (
130 'discussion',
131 'canceled_after_revocation_during_discussion' ) ) )
132 AND (
133 "interest"."member_id" NOTNULL OR
134 ( "membership"."member_id" NOTNULL AND
135 "event"."event" IN (
136 'issue_state_changed',
137 'initiative_created_in_new_issue',
138 'initiative_created_in_existing_issue',
139 'initiative_revoked' ) ) )
140 AND "ignored_member"."member_id" ISNULL
141 AND "ignored_initiative"."member_id" ISNULL;
143 CREATE OR REPLACE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)
144 RETURNS VOID
145 LANGUAGE 'plpgsql' VOLATILE AS $$
146 BEGIN
147 UPDATE "member" SET
148 "last_login" = NULL,
149 "last_delegation_check" = NULL,
150 "login" = NULL,
151 "password" = NULL,
152 "locked" = TRUE,
153 "active" = FALSE,
154 "notify_email" = NULL,
155 "notify_email_unconfirmed" = NULL,
156 "notify_email_secret" = NULL,
157 "notify_email_secret_expiry" = NULL,
158 "notify_email_lock_expiry" = NULL,
159 "login_recovery_expiry" = NULL,
160 "password_reset_secret" = NULL,
161 "password_reset_secret_expiry" = NULL,
162 "organizational_unit" = NULL,
163 "internal_posts" = NULL,
164 "realname" = NULL,
165 "birthday" = NULL,
166 "address" = NULL,
167 "email" = NULL,
168 "xmpp_address" = NULL,
169 "website" = NULL,
170 "phone" = NULL,
171 "mobile_phone" = NULL,
172 "profession" = NULL,
173 "external_memberships" = NULL,
174 "external_posts" = NULL,
175 "statement" = NULL
176 WHERE "id" = "member_id_p";
177 -- "text_search_data" is updated by triggers
178 DELETE FROM "setting" WHERE "member_id" = "member_id_p";
179 DELETE FROM "setting_map" WHERE "member_id" = "member_id_p";
180 DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p";
181 DELETE FROM "member_image" WHERE "member_id" = "member_id_p";
182 DELETE FROM "contact" WHERE "member_id" = "member_id_p";
183 DELETE FROM "ignored_member" WHERE "member_id" = "member_id_p";
184 DELETE FROM "session" WHERE "member_id" = "member_id_p";
185 DELETE FROM "area_setting" WHERE "member_id" = "member_id_p";
186 DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p";
187 DELETE FROM "ignored_initiative" WHERE "member_id" = "member_id_p";
188 DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p";
189 DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p";
190 DELETE FROM "membership" WHERE "member_id" = "member_id_p";
191 DELETE FROM "delegation" WHERE "truster_id" = "member_id_p";
192 DELETE FROM "non_voter" WHERE "member_id" = "member_id_p";
193 DELETE FROM "direct_voter" USING "issue"
194 WHERE "direct_voter"."issue_id" = "issue"."id"
195 AND "issue"."closed" ISNULL
196 AND "member_id" = "member_id_p";
197 RETURN;
198 END;
199 $$;
201 CREATE OR REPLACE FUNCTION "delete_private_data"()
202 RETURNS VOID
203 LANGUAGE 'plpgsql' VOLATILE AS $$
204 BEGIN
205 DELETE FROM "temporary_transaction_data";
206 DELETE FROM "member" WHERE "activated" ISNULL;
207 UPDATE "member" SET
208 "invite_code" = NULL,
209 "invite_code_expiry" = NULL,
210 "admin_comment" = NULL,
211 "last_login" = NULL,
212 "last_delegation_check" = NULL,
213 "login" = NULL,
214 "password" = NULL,
215 "lang" = NULL,
216 "notify_email" = NULL,
217 "notify_email_unconfirmed" = NULL,
218 "notify_email_secret" = NULL,
219 "notify_email_secret_expiry" = NULL,
220 "notify_email_lock_expiry" = NULL,
221 "notify_level" = NULL,
222 "login_recovery_expiry" = NULL,
223 "password_reset_secret" = NULL,
224 "password_reset_secret_expiry" = NULL,
225 "organizational_unit" = NULL,
226 "internal_posts" = NULL,
227 "realname" = NULL,
228 "birthday" = NULL,
229 "address" = NULL,
230 "email" = NULL,
231 "xmpp_address" = NULL,
232 "website" = NULL,
233 "phone" = NULL,
234 "mobile_phone" = NULL,
235 "profession" = NULL,
236 "external_memberships" = NULL,
237 "external_posts" = NULL,
238 "formatting_engine" = NULL,
239 "statement" = NULL;
240 -- "text_search_data" is updated by triggers
241 DELETE FROM "setting";
242 DELETE FROM "setting_map";
243 DELETE FROM "member_relation_setting";
244 DELETE FROM "member_image";
245 DELETE FROM "contact";
246 DELETE FROM "ignored_member";
247 DELETE FROM "session";
248 DELETE FROM "area_setting";
249 DELETE FROM "issue_setting";
250 DELETE FROM "ignored_initiative";
251 DELETE FROM "initiative_setting";
252 DELETE FROM "suggestion_setting";
253 DELETE FROM "non_voter";
254 DELETE FROM "direct_voter" USING "issue"
255 WHERE "direct_voter"."issue_id" = "issue"."id"
256 AND "issue"."closed" ISNULL;
257 RETURN;
258 END;
259 $$;
261 COMMIT;