liquid_feedback_core

view update/core-update.v4.1.0-v4.2.0.sql @ 603:617ac1725557

Removed column "content_type" from table "draft_attachment" (Content type is stored in "file" table instead)
author jbe
date Mon Feb 10 20:33:10 2020 +0100 (22 months ago)
parents 9570aeabf4fc
children f69e0d9e5bb3
line source
1 BEGIN;
3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
4 SELECT * FROM (VALUES ('4.2.0-incomplete-update', 4, 2, -1))
5 AS "subquery"("string", "major", "minor", "revision");
7 ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'posting_created';
9 COMMIT;
11 BEGIN;
13 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
14 SELECT * FROM (VALUES ('4.2.0', 4, 2, 0))
15 AS "subquery"("string", "major", "minor", "revision");
17 DROP VIEW "newsletter_to_send";
18 DROP VIEW "scheduled_notification_to_send";
19 DROP VIEW "member_contingent_left";
20 DROP VIEW "member_contingent";
21 DROP VIEW "expired_snapshot";
22 DROP VIEW "current_draft";
23 DROP VIEW "opening_draft";
24 DROP VIEW "area_with_unaccepted_issues";
25 DROP VIEW "member_to_notify";
26 DROP VIEW "member_eligible_to_be_notified";
28 CREATE EXTENSION IF NOT EXISTS btree_gist;
29 CREATE EXTENSION IF NOT EXISTS conflux;
31 DROP FUNCTION "text_search_query" (TEXT);
33 ALTER TABLE "system_setting" DROP COLUMN "snapshot_retention";
35 CREATE TABLE "file" (
36 "id" SERIAL8 PRIMARY KEY,
37 UNIQUE ("content_type", "hash"),
38 "content_type" TEXT NOT NULL,
39 "hash" TEXT NOT NULL,
40 "data" BYTEA NOT NULL,
41 "preview_content_type" TEXT,
42 "preview_data" BYTEA );
44 COMMENT ON TABLE "file" IS 'Table holding file contents for draft attachments';
46 COMMENT ON COLUMN "file"."content_type" IS 'Content type of "data"';
47 COMMENT ON COLUMN "file"."hash" IS 'Hash of "data" to avoid storing duplicates where content-type and data is identical';
48 COMMENT ON COLUMN "file"."data" IS 'Binary content';
49 COMMENT ON COLUMN "file"."preview_content_type" IS 'Content type of "preview_data"';
50 COMMENT ON COLUMN "file"."preview_data" IS 'Preview (e.g. preview image)';
52 ALTER TABLE "member" DROP COLUMN "text_search_data";
53 DROP TRIGGER "update_text_search_data" ON "member";
55 CREATE INDEX "member_useterms_member_id_contract_identifier" ON "member_useterms" ("member_id", "contract_identifier");
57 ALTER TABLE "member_profile" DROP COLUMN "text_search_data";
58 DROP TRIGGER "update_text_search_data" ON "member_profile";
60 ALTER TABLE "contact" ADD COLUMN "following" BOOLEAN NOT NULL DEFAULT TRUE;
62 COMMENT ON COLUMN "contact"."following" IS 'TRUE = actions of contact are shown in personal timeline';
64 ALTER TABLE "unit" DROP COLUMN "text_search_data";
65 DROP TRIGGER "update_text_search_data" ON "unit";
67 ALTER TABLE "area" DROP COLUMN "text_search_data";
68 DROP TRIGGER "update_text_search_data" ON "area";
70 DROP INDEX "issue_accepted_idx";
71 DROP INDEX "issue_half_frozen_idx";
72 DROP INDEX "issue_fully_frozen_idx";
73 ALTER INDEX "issue_created_idx_open" RENAME TO "issue_open_created_idx";
74 DROP INDEX "issue_closed_idx_canceled";
75 ALTER INDEX "issue_latest_snapshot_id" RENAME TO "issue_latest_snapshot_id_idx";
76 ALTER INDEX "issue_admission_snapshot_id" RENAME TO "issue_admission_snapshot_id_idx";
77 ALTER INDEX "issue_half_freeze_snapshot_id" RENAME TO "issue_half_freeze_snapshot_id_idx";
78 ALTER INDEX "issue_full_freeze_snapshot_id" RENAME TO "issue_full_freeze_snapshot_id_idx";
80 ALTER TABLE "initiative" ADD COLUMN "content" TEXT;
81 ALTER TABLE "initiative" DROP COLUMN "text_search_data";
82 ALTER TABLE "initiative" DROP COLUMN "draft_text_search_data";
83 DROP INDEX "initiative_revoked_idx";
84 DROP TRIGGER "update_text_search_data" ON "initiative";
86 COMMENT ON COLUMN "initiative"."content" IS 'Initiative text (automatically copied from most recent draft)';
88 ALTER TABLE "battle" DROP CONSTRAINT "initiative_ids_not_equal";
89 ALTER TABLE "battle" ADD CONSTRAINT "initiative_ids_not_equal" CHECK (
90 "winning_initiative_id" != "losing_initiative_id" AND
91 ("winning_initiative_id" NOTNULL OR "losing_initiative_id" NOTNULL) );
93 ALTER TABLE "draft" DROP COLUMN "text_search_data";
94 DROP TRIGGER "update_text_search_data" ON "draft";
96 CREATE TABLE "draft_attachment" (
97 "id" SERIAL8 PRIMARY KEY,
98 "draft_id" INT8 REFERENCES "draft" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
99 "file_id" INT8 REFERENCES "file" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
100 "title" TEXT,
101 "description" TEXT );
103 COMMENT ON TABLE "draft_attachment" IS 'Binary attachments for a draft (images, PDF file, etc.); Implicitly ordered through ''id'' column';
105 ALTER TABLE "suggestion" DROP COLUMN "text_search_data";
106 DROP TRIGGER "update_text_search_data" ON "suggestion";
108 ALTER TABLE "direct_voter" DROP COLUMN "text_search_data";
109 DROP TRIGGER "update_text_search_data" ON "direct_voter";
111 CREATE TABLE "posting" (
112 UNIQUE ("author_id", "id"), -- index needed for foreign-key on table "posting_lexeme"
113 "id" SERIAL8 PRIMARY KEY,
114 "author_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
115 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
116 "message" TEXT NOT NULL,
117 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
118 "area_id" INT4,
119 FOREIGN KEY ("unit_id", "area_id") REFERENCES "area" ("unit_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
120 "policy_id" INT4 REFERENCES "policy" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
121 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
122 FOREIGN KEY ("area_id", "issue_id") REFERENCES "issue" ("area_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
123 FOREIGN KEY ("policy_id", "issue_id") REFERENCES "issue" ("policy_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
124 "initiative_id" INT4,
125 "suggestion_id" INT8,
126 -- NOTE: no referential integrity for suggestions because those are
127 -- actually deleted
128 -- FOREIGN KEY ("initiative_id", "suggestion_id")
129 -- REFERENCES "suggestion" ("initiative_id", "id")
130 -- ON DELETE CASCADE ON UPDATE CASCADE,
131 CONSTRAINT "area_requires_unit" CHECK (
132 "area_id" ISNULL OR "unit_id" NOTNULL ),
133 CONSTRAINT "policy_set_when_issue_set" CHECK (
134 ("policy_id" NOTNULL) = ("issue_id" NOTNULL) ),
135 CONSTRAINT "issue_requires_area" CHECK (
136 "issue_id" ISNULL OR "area_id" NOTNULL ),
137 CONSTRAINT "initiative_requires_issue" CHECK (
138 "initiative_id" ISNULL OR "issue_id" NOTNULL ),
139 CONSTRAINT "suggestion_requires_initiative" CHECK (
140 "suggestion_id" ISNULL OR "initiative_id" NOTNULL ) );
141 CREATE INDEX "posting_global_idx" ON "posting" USING gist ((pstamp("author_id", "id")));
142 CREATE INDEX "posting_unit_idx" ON "posting" USING gist ("unit_id", (pstamp("author_id", "id"))) WHERE "unit_id" NOTNULL;
143 CREATE INDEX "posting_area_idx" ON "posting" USING gist ("area_id", (pstamp("author_id", "id"))) WHERE "area_id" NOTNULL;
144 CREATE INDEX "posting_policy_idx" ON "posting" USING gist ("policy_id", (pstamp("author_id", "id"))) WHERE "policy_id" NOTNULL;
145 CREATE INDEX "posting_issue_idx" ON "posting" USING gist ("issue_id", (pstamp("author_id", "id"))) WHERE "issue_id" NOTNULL;
146 CREATE INDEX "posting_initiative_idx" ON "posting" USING gist ("initiative_id", (pstamp("author_id", "id"))) WHERE "initiative_id" NOTNULL;
147 CREATE INDEX "posting_suggestion_idx" ON "posting" USING gist ("suggestion_id", (pstamp("author_id", "id"))) WHERE "suggestion_id" NOTNULL;
148 COMMENT ON TABLE "posting" IS 'Text postings of members; a text posting may optionally be associated to a unit, area, policy, issue, initiative, or suggestion';
150 CREATE TABLE "posting_lexeme" (
151 PRIMARY KEY ("posting_id", "lexeme"),
152 FOREIGN KEY ("posting_id", "author_id") REFERENCES "posting" ("id", "author_id") ON DELETE CASCADE ON UPDATE CASCADE,
153 "posting_id" INT8,
154 "lexeme" TEXT,
155 "author_id" INT4 );
156 CREATE INDEX "posting_lexeme_idx" ON "posting_lexeme" USING gist ("lexeme", (pstamp("author_id", "posting_id")));
158 COMMENT ON TABLE "posting_lexeme" IS 'Helper table to allow searches for hashtags.';
160 ALTER TABLE "event" ADD COLUMN "posting_id" INT8 REFERENCES "posting" ("id") ON DELETE RESTRICT ON UPDATE CASCADE;
161 ALTER TABLE "event" DROP CONSTRAINT "constr_for_issue_state_changed";
162 ALTER TABLE "event" DROP CONSTRAINT "constr_for_initiative_creation_or_revocation_or_new_draft";
163 ALTER TABLE "event" DROP CONSTRAINT "constr_for_suggestion_creation";
164 ALTER TABLE "event" DROP CONSTRAINT "constr_for_suggestion_removal";
165 ALTER TABLE "event" DROP CONSTRAINT "constr_for_value_less_member_event";
166 ALTER TABLE "event" DROP CONSTRAINT "constr_for_member_active";
167 ALTER TABLE "event" DROP CONSTRAINT "constr_for_member_name_updated";
168 ALTER TABLE "event" DROP CONSTRAINT "constr_for_interest";
169 ALTER TABLE "event" DROP CONSTRAINT "constr_for_initiator";
170 ALTER TABLE "event" DROP CONSTRAINT "constr_for_support";
171 ALTER TABLE "event" DROP CONSTRAINT "constr_for_support_updated";
172 ALTER TABLE "event" DROP CONSTRAINT "constr_for_suggestion_rated";
173 ALTER TABLE "event" DROP CONSTRAINT "constr_for_delegation";
174 ALTER TABLE "event" DROP CONSTRAINT "constr_for_contact";
175 ALTER TABLE "event" ADD
176 CONSTRAINT "constr_for_issue_state_changed" CHECK (
177 "event" != 'issue_state_changed' OR (
178 "posting_id" ISNULL AND
179 "member_id" ISNULL AND
180 "other_member_id" ISNULL AND
181 "scope" ISNULL AND
182 "unit_id" NOTNULL AND
183 "area_id" NOTNULL AND
184 "policy_id" NOTNULL AND
185 "issue_id" NOTNULL AND
186 "state" NOTNULL AND
187 "initiative_id" ISNULL AND
188 "draft_id" ISNULL AND
189 "suggestion_id" ISNULL AND
190 "boolean_value" ISNULL AND
191 "numeric_value" ISNULL AND
192 "text_value" ISNULL AND
193 "old_text_value" ISNULL ));
194 ALTER TABLE "event" ADD
195 CONSTRAINT "constr_for_initiative_creation_or_revocation_or_new_draft" CHECK (
196 "event" NOT IN (
197 'initiative_created_in_new_issue',
198 'initiative_created_in_existing_issue',
199 'initiative_revoked',
200 'new_draft_created'
201 ) OR (
202 "posting_id" ISNULL AND
203 "member_id" NOTNULL AND
204 "other_member_id" ISNULL AND
205 "scope" ISNULL AND
206 "unit_id" NOTNULL AND
207 "area_id" NOTNULL AND
208 "policy_id" NOTNULL AND
209 "issue_id" NOTNULL AND
210 "state" NOTNULL AND
211 "initiative_id" NOTNULL AND
212 "draft_id" NOTNULL AND
213 "suggestion_id" ISNULL AND
214 "boolean_value" ISNULL AND
215 "numeric_value" ISNULL AND
216 "text_value" ISNULL AND
217 "old_text_value" ISNULL ));
218 ALTER TABLE "event" ADD
219 CONSTRAINT "constr_for_suggestion_creation" CHECK (
220 "event" != 'suggestion_created' OR (
221 "posting_id" ISNULL AND
222 "member_id" NOTNULL AND
223 "other_member_id" ISNULL AND
224 "scope" ISNULL AND
225 "unit_id" NOTNULL AND
226 "area_id" NOTNULL AND
227 "policy_id" NOTNULL AND
228 "issue_id" NOTNULL AND
229 "state" NOTNULL AND
230 "initiative_id" NOTNULL AND
231 "draft_id" ISNULL AND
232 "suggestion_id" NOTNULL AND
233 "boolean_value" ISNULL AND
234 "numeric_value" ISNULL AND
235 "text_value" ISNULL AND
236 "old_text_value" ISNULL ));
237 ALTER TABLE "event" ADD
238 CONSTRAINT "constr_for_suggestion_removal" CHECK (
239 "event" != 'suggestion_deleted' OR (
240 "posting_id" ISNULL AND
241 "member_id" ISNULL AND
242 "other_member_id" ISNULL AND
243 "scope" ISNULL AND
244 "unit_id" NOTNULL AND
245 "area_id" NOTNULL AND
246 "policy_id" NOTNULL AND
247 "issue_id" NOTNULL AND
248 "state" NOTNULL AND
249 "initiative_id" NOTNULL AND
250 "draft_id" ISNULL AND
251 "suggestion_id" NOTNULL AND
252 "boolean_value" ISNULL AND
253 "numeric_value" ISNULL AND
254 "text_value" ISNULL AND
255 "old_text_value" ISNULL ));
256 ALTER TABLE "event" ADD
257 CONSTRAINT "constr_for_value_less_member_event" CHECK (
258 "event" NOT IN (
259 'member_activated',
260 'member_deleted',
261 'member_profile_updated',
262 'member_image_updated'
263 ) OR (
264 "posting_id" ISNULL AND
265 "member_id" NOTNULL AND
266 "other_member_id" ISNULL AND
267 "scope" ISNULL AND
268 "unit_id" ISNULL AND
269 "area_id" ISNULL AND
270 "policy_id" ISNULL AND
271 "issue_id" ISNULL AND
272 "state" ISNULL AND
273 "initiative_id" ISNULL AND
274 "draft_id" ISNULL AND
275 "suggestion_id" ISNULL AND
276 "boolean_value" ISNULL AND
277 "numeric_value" ISNULL AND
278 "text_value" ISNULL AND
279 "old_text_value" ISNULL ));
280 ALTER TABLE "event" ADD
281 CONSTRAINT "constr_for_member_active" CHECK (
282 "event" != 'member_active' OR (
283 "posting_id" ISNULL AND
284 "member_id" NOTNULL AND
285 "other_member_id" ISNULL AND
286 "scope" ISNULL AND
287 "unit_id" ISNULL AND
288 "area_id" ISNULL AND
289 "policy_id" ISNULL AND
290 "issue_id" ISNULL AND
291 "state" ISNULL AND
292 "initiative_id" ISNULL AND
293 "draft_id" ISNULL AND
294 "suggestion_id" ISNULL AND
295 "boolean_value" NOTNULL AND
296 "numeric_value" ISNULL AND
297 "text_value" ISNULL AND
298 "old_text_value" ISNULL ));
299 ALTER TABLE "event" ADD
300 CONSTRAINT "constr_for_member_name_updated" CHECK (
301 "event" != 'member_name_updated' OR (
302 "posting_id" ISNULL AND
303 "member_id" NOTNULL AND
304 "other_member_id" ISNULL AND
305 "scope" ISNULL AND
306 "unit_id" ISNULL AND
307 "area_id" ISNULL AND
308 "policy_id" ISNULL AND
309 "issue_id" ISNULL AND
310 "state" ISNULL AND
311 "initiative_id" ISNULL AND
312 "draft_id" ISNULL AND
313 "suggestion_id" ISNULL AND
314 "boolean_value" ISNULL AND
315 "numeric_value" ISNULL AND
316 "text_value" NOTNULL AND
317 "old_text_value" NOTNULL ));
318 ALTER TABLE "event" ADD
319 CONSTRAINT "constr_for_interest" CHECK (
320 "event" != 'interest' OR (
321 "posting_id" ISNULL AND
322 "member_id" NOTNULL AND
323 "other_member_id" ISNULL AND
324 "scope" ISNULL AND
325 "unit_id" NOTNULL AND
326 "area_id" NOTNULL AND
327 "policy_id" NOTNULL AND
328 "issue_id" NOTNULL AND
329 "state" NOTNULL AND
330 "initiative_id" ISNULL AND
331 "draft_id" ISNULL AND
332 "suggestion_id" ISNULL AND
333 "boolean_value" NOTNULL AND
334 "numeric_value" ISNULL AND
335 "text_value" ISNULL AND
336 "old_text_value" ISNULL ));
337 ALTER TABLE "event" ADD
338 CONSTRAINT "constr_for_initiator" CHECK (
339 "event" != 'initiator' OR (
340 "posting_id" ISNULL AND
341 "member_id" NOTNULL AND
342 "other_member_id" ISNULL AND
343 "scope" ISNULL AND
344 "unit_id" NOTNULL AND
345 "area_id" NOTNULL AND
346 "policy_id" NOTNULL AND
347 "issue_id" NOTNULL AND
348 "state" NOTNULL AND
349 "initiative_id" NOTNULL AND
350 "draft_id" ISNULL AND
351 "suggestion_id" ISNULL AND
352 "boolean_value" NOTNULL AND
353 "numeric_value" ISNULL AND
354 "text_value" ISNULL AND
355 "old_text_value" ISNULL ));
356 ALTER TABLE "event" ADD
357 CONSTRAINT "constr_for_support" CHECK (
358 "event" != 'support' OR (
359 "posting_id" ISNULL AND
360 "member_id" NOTNULL AND
361 "other_member_id" ISNULL AND
362 "scope" ISNULL AND
363 "unit_id" NOTNULL AND
364 "area_id" NOTNULL AND
365 "policy_id" NOTNULL AND
366 "issue_id" NOTNULL AND
367 "state" NOTNULL AND
368 "initiative_id" NOTNULL AND
369 ("draft_id" NOTNULL) = ("boolean_value" = TRUE) AND
370 "suggestion_id" ISNULL AND
371 "boolean_value" NOTNULL AND
372 "numeric_value" ISNULL AND
373 "text_value" ISNULL AND
374 "old_text_value" ISNULL ));
375 ALTER TABLE "event" ADD
376 CONSTRAINT "constr_for_support_updated" CHECK (
377 "event" != 'support_updated' OR (
378 "posting_id" ISNULL AND
379 "member_id" NOTNULL AND
380 "other_member_id" ISNULL AND
381 "scope" ISNULL AND
382 "unit_id" NOTNULL AND
383 "area_id" NOTNULL AND
384 "policy_id" NOTNULL AND
385 "issue_id" NOTNULL AND
386 "state" NOTNULL AND
387 "initiative_id" NOTNULL AND
388 "draft_id" NOTNULL AND
389 "suggestion_id" ISNULL AND
390 "boolean_value" ISNULL AND
391 "numeric_value" ISNULL AND
392 "text_value" ISNULL AND
393 "old_text_value" ISNULL ));
394 ALTER TABLE "event" ADD
395 CONSTRAINT "constr_for_suggestion_rated" CHECK (
396 "event" != 'suggestion_rated' OR (
397 "posting_id" ISNULL AND
398 "member_id" NOTNULL AND
399 "other_member_id" ISNULL AND
400 "scope" ISNULL AND
401 "unit_id" NOTNULL AND
402 "area_id" NOTNULL AND
403 "policy_id" NOTNULL AND
404 "issue_id" NOTNULL AND
405 "state" NOTNULL AND
406 "initiative_id" NOTNULL AND
407 "draft_id" ISNULL AND
408 "suggestion_id" NOTNULL AND
409 ("boolean_value" NOTNULL) = ("numeric_value" != 0) AND
410 "numeric_value" NOTNULL AND
411 "numeric_value" IN (-2, -1, 0, 1, 2) AND
412 "text_value" ISNULL AND
413 "old_text_value" ISNULL ));
414 ALTER TABLE "event" ADD
415 CONSTRAINT "constr_for_delegation" CHECK (
416 "event" != 'delegation' OR (
417 "posting_id" ISNULL AND
418 "member_id" NOTNULL AND
419 (("other_member_id" ISNULL) OR ("boolean_value" = TRUE)) AND
420 "scope" NOTNULL AND
421 "unit_id" NOTNULL AND
422 ("area_id" NOTNULL) = ("scope" != 'unit'::"delegation_scope") AND
423 "policy_id" ISNULL AND
424 ("issue_id" NOTNULL) = ("scope" = 'issue'::"delegation_scope") AND
425 ("state" NOTNULL) = ("scope" = 'issue'::"delegation_scope") AND
426 "initiative_id" ISNULL AND
427 "draft_id" ISNULL AND
428 "suggestion_id" ISNULL AND
429 "boolean_value" NOTNULL AND
430 "numeric_value" ISNULL AND
431 "text_value" ISNULL AND
432 "old_text_value" ISNULL ));
433 ALTER TABLE "event" ADD
434 CONSTRAINT "constr_for_contact" CHECK (
435 "event" != 'contact' OR (
436 "posting_id" ISNULL AND
437 "member_id" NOTNULL AND
438 "other_member_id" NOTNULL AND
439 "scope" ISNULL AND
440 "unit_id" ISNULL AND
441 "area_id" ISNULL AND
442 "policy_id" ISNULL AND
443 "issue_id" ISNULL AND
444 "state" ISNULL AND
445 "initiative_id" ISNULL AND
446 "draft_id" ISNULL AND
447 "suggestion_id" ISNULL AND
448 "boolean_value" NOTNULL AND
449 "numeric_value" ISNULL AND
450 "text_value" ISNULL AND
451 "old_text_value" ISNULL ));
452 ALTER TABLE "event" ADD
453 CONSTRAINT "constr_for_posting_created" CHECK (
454 "event" != 'posting_created' OR (
455 "posting_id" NOTNULL AND
456 "member_id" NOTNULL AND
457 "other_member_id" ISNULL AND
458 "scope" ISNULL AND
459 "state" ISNULL AND
460 ("area_id" ISNULL OR "unit_id" NOTNULL) AND
461 ("policy_id" NOTNULL) = ("issue_id" NOTNULL) AND
462 ("issue_id" ISNULL OR "area_id" NOTNULL) AND
463 ("state" NOTNULL) = ("issue_id" NOTNULL) AND
464 ("initiative_id" ISNULL OR "issue_id" NOTNULL) AND
465 "draft_id" ISNULL AND
466 ("suggestion_id" ISNULL OR "initiative_id" NOTNULL) AND
467 "boolean_value" ISNULL AND
468 "numeric_value" ISNULL AND
469 "text_value" ISNULL AND
470 "old_text_value" ISNULL ));
472 CREATE INDEX "event_tl_global_idx" ON "event" USING gist ((pstamp("member_id", "id")));
473 CREATE INDEX "event_tl_unit_idx" ON "event" USING gist ("unit_id", (pstamp("member_id", "id"))) WHERE "unit_id" NOTNULL;
474 CREATE INDEX "event_tl_area_idx" ON "event" USING gist ("area_id", (pstamp("member_id", "id"))) WHERE "area_id" NOTNULL;
475 CREATE INDEX "event_tl_policy_idx" ON "event" USING gist ("policy_id", (pstamp("member_id", "id"))) WHERE "policy_id" NOTNULL;
476 CREATE INDEX "event_tl_issue_idx" ON "event" USING gist ("issue_id", (pstamp("member_id", "id"))) WHERE "issue_id" NOTNULL;
477 CREATE INDEX "event_tl_initiative_idx" ON "event" USING gist ("initiative_id", (pstamp("member_id", "id"))) WHERE "initiative_id" NOTNULL;
478 CREATE INDEX "event_tl_suggestion_idx" ON "event" USING gist ("suggestion_id", (pstamp("member_id", "id"))) WHERE "suggestion_id" NOTNULL;
480 CREATE OR REPLACE FUNCTION "highlight"
481 ( "body_p" TEXT,
482 "query_text_p" TEXT )
483 RETURNS TEXT
484 LANGUAGE 'plpgsql' IMMUTABLE AS $$
485 BEGIN
486 RETURN ts_headline(
487 replace(replace("body_p", e'\\', e'\\\\'), '*', e'\\*'),
488 "plainto_tsquery"("query_text_p"),
489 'StartSel=* StopSel=* HighlightAll=TRUE' );
490 END;
491 $$;
493 CREATE FUNCTION "to_tsvector"("member") RETURNS TSVECTOR
494 LANGUAGE SQL IMMUTABLE AS $$ SELECT to_tsvector(concat_ws(' ',
495 $1."name",
496 $1."identification"
497 )) $$;
498 CREATE INDEX "member_to_tsvector_idx" ON "member" USING gin
499 (("to_tsvector"("member".*)));
501 CREATE FUNCTION "to_tsvector"("member_profile") RETURNS TSVECTOR
502 LANGUAGE SQL IMMUTABLE AS $$ SELECT to_tsvector(concat_ws(' ',
503 $1."statement",
504 $1."profile_text_data"
505 )) $$;
506 CREATE INDEX "member_profile_to_tsvector_idx" ON "member_profile" USING gin
507 (("to_tsvector"("member_profile".*)));
509 CREATE FUNCTION "to_tsvector"("unit") RETURNS TSVECTOR
510 LANGUAGE SQL IMMUTABLE AS $$ SELECT to_tsvector(concat_ws(' ',
511 $1."name",
512 $1."description"
513 )) $$;
514 CREATE INDEX "unit_to_tsvector_idx" ON "unit" USING gin
515 (("to_tsvector"("unit".*)));
517 CREATE FUNCTION "to_tsvector"("area") RETURNS TSVECTOR
518 LANGUAGE SQL IMMUTABLE AS $$ SELECT to_tsvector(concat_ws(' ',
519 $1."name",
520 $1."description"
521 )) $$;
522 CREATE INDEX "area_to_tsvector_idx" ON "area" USING gin
523 (("to_tsvector"("area".*)));
525 CREATE FUNCTION "to_tsvector"("initiative") RETURNS TSVECTOR
526 LANGUAGE SQL IMMUTABLE AS $$ SELECT to_tsvector(concat_ws(' ',
527 $1."name",
528 $1."content"
529 )) $$;
530 CREATE INDEX "initiative_to_tsvector_idx" ON "initiative" USING gin
531 (("to_tsvector"("initiative".*)));
533 CREATE FUNCTION "to_tsvector"("draft") RETURNS TSVECTOR
534 LANGUAGE SQL IMMUTABLE AS $$ SELECT to_tsvector(concat_ws(' ',
535 $1."content"
536 )) $$;
537 CREATE INDEX "draft_to_tsvector_idx" ON "draft" USING gin
538 (("to_tsvector"("draft".*)));
540 CREATE FUNCTION "to_tsvector"("suggestion") RETURNS TSVECTOR
541 LANGUAGE SQL IMMUTABLE AS $$ SELECT to_tsvector(concat_ws(' ',
542 $1."name",
543 $1."content"
544 )) $$;
545 CREATE INDEX "suggestion_to_tsvector_idx" ON "suggestion" USING gin
546 (("to_tsvector"("suggestion".*)));
548 CREATE FUNCTION "to_tsvector"("direct_voter") RETURNS TSVECTOR
549 LANGUAGE SQL IMMUTABLE AS $$ SELECT to_tsvector(concat_ws(' ',
550 $1."comment"
551 )) $$;
552 CREATE INDEX "direct_voter_to_tsvector_idx" ON "direct_voter" USING gin
553 (("to_tsvector"("direct_voter".*)));
555 CREATE FUNCTION "update_posting_lexeme_trigger"()
556 RETURNS TRIGGER
557 LANGUAGE 'plpgsql' VOLATILE AS $$
558 DECLARE
559 "lexeme_v" TEXT;
560 BEGIN
561 IF TG_OP = 'DELETE' OR TG_OP = 'UPDATE' THEN
562 DELETE FROM "posting_lexeme" WHERE "posting_id" = OLD."id";
563 END IF;
564 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
565 FOR "lexeme_v" IN
566 SELECT regexp_matches[1]
567 FROM regexp_matches(NEW."message", '#[^\s.,;:]+')
568 LOOP
569 INSERT INTO "posting_lexeme" ("posting_id", "author_id", "lexeme")
570 VALUES (
571 NEW."id",
572 NEW."author_id",
573 "lexeme_v" )
574 ON CONFLICT ("posting_id", "lexeme") DO NOTHING;
575 END LOOP;
576 END IF;
577 RETURN NULL;
578 END;
579 $$;
581 CREATE TRIGGER "update_posting_lexeme"
582 AFTER INSERT OR UPDATE OR DELETE ON "posting"
583 FOR EACH ROW EXECUTE PROCEDURE "update_posting_lexeme_trigger"();
585 COMMENT ON FUNCTION "update_posting_lexeme_trigger"() IS 'Implementation of trigger "update_posting_lexeme" on table "posting"';
586 COMMENT ON TRIGGER "update_posting_lexeme" ON "posting" IS 'Keeps table "posting_lexeme" up to date';
588 CREATE FUNCTION "write_event_posting_trigger"()
589 RETURNS TRIGGER
590 LANGUAGE 'plpgsql' VOLATILE AS $$
591 BEGIN
592 INSERT INTO "event" (
593 "event", "posting_id", "member_id",
594 "unit_id", "area_id", "policy_id",
595 "issue_id", "initiative_id", "suggestion_id"
596 ) VALUES (
597 'posting_created', NEW."id", NEW."author_id",
598 NEW."unit_id", NEW."area_id", NEW."policy_id",
599 NEW."issue_id", NEW."initiative_id", NEW."suggestion_id"
600 );
601 RETURN NULL;
602 END;
603 $$;
605 CREATE TRIGGER "write_event_posting"
606 AFTER INSERT ON "posting" FOR EACH ROW EXECUTE PROCEDURE
607 "write_event_posting_trigger"();
609 COMMENT ON FUNCTION "write_event_posting_trigger"() IS 'Implementation of trigger "write_event_posting" on table "posting"';
610 COMMENT ON TRIGGER "write_event_posting" ON "posting" IS 'Create entry in "event" table when creating a new posting';
612 CREATE FUNCTION "file_requires_reference_trigger"()
613 RETURNS TRIGGER
614 LANGUAGE 'plpgsql' VOLATILE AS $$
615 BEGIN
616 IF NOT EXISTS (
617 SELECT NULL FROM "draft_attachment" WHERE "file_id" = NEW."id"
618 ) THEN
619 RAISE EXCEPTION 'Cannot create an unreferenced file.' USING
620 ERRCODE = 'integrity_constraint_violation',
621 HINT = 'Create file and its reference in another table within the same transaction.';
622 END IF;
623 RETURN NULL;
624 END;
625 $$;
627 CREATE CONSTRAINT TRIGGER "file_requires_reference"
628 AFTER INSERT OR UPDATE ON "file" DEFERRABLE INITIALLY DEFERRED
629 FOR EACH ROW EXECUTE PROCEDURE
630 "file_requires_reference_trigger"();
632 COMMENT ON FUNCTION "file_requires_reference_trigger"() IS 'Implementation of trigger "file_requires_reference" on table "file"';
633 COMMENT ON TRIGGER "file_requires_reference" ON "file" IS 'Ensure that files are always referenced';
635 CREATE FUNCTION "last_reference_deletes_file_trigger"()
636 RETURNS TRIGGER
637 LANGUAGE 'plpgsql' VOLATILE AS $$
638 DECLARE
639 "reference_lost" BOOLEAN;
640 BEGIN
641 IF TG_OP = 'DELETE' THEN
642 "reference_lost" := TRUE;
643 ELSE
644 "reference_lost" := NEW."file_id" != OLD."file_id";
645 END IF;
646 IF
647 "reference_lost" AND NOT EXISTS (
648 SELECT NULL FROM "draft_attachment" WHERE "file_id" = OLD."file_id"
649 )
650 THEN
651 DELETE FROM "file" WHERE "id" = OLD."file_id";
652 END IF;
653 RETURN NULL;
654 END;
655 $$;
657 CREATE CONSTRAINT TRIGGER "last_reference_deletes_file"
658 AFTER UPDATE OR DELETE ON "draft_attachment" DEFERRABLE INITIALLY DEFERRED
659 FOR EACH ROW EXECUTE PROCEDURE
660 "last_reference_deletes_file_trigger"();
662 COMMENT ON FUNCTION "last_reference_deletes_file_trigger"() IS 'Implementation of trigger "last_reference_deletes_file" on table "draft_attachment"';
663 COMMENT ON TRIGGER "last_reference_deletes_file" ON "draft_attachment" IS 'Removing the last reference to a file deletes the file';
665 CREATE OR REPLACE FUNCTION "copy_current_draft_data"
666 ("initiative_id_p" "initiative"."id"%TYPE )
667 RETURNS VOID
668 LANGUAGE 'plpgsql' VOLATILE AS $$
669 BEGIN
670 PERFORM NULL FROM "initiative" WHERE "id" = "initiative_id_p"
671 FOR UPDATE;
672 UPDATE "initiative" SET
673 "location" = "draft"."location",
674 "content" = "draft"."content"
675 FROM "current_draft" AS "draft"
676 WHERE "initiative"."id" = "initiative_id_p"
677 AND "draft"."initiative_id" = "initiative_id_p";
678 END;
679 $$;
681 CREATE VIEW "follower" AS
682 SELECT
683 "id" AS "follower_id",
684 ( SELECT ARRAY["member"."id"] || array_agg("contact"."other_member_id")
685 FROM "contact"
686 WHERE "contact"."member_id" = "member"."id" AND "contact"."following" )
687 AS "following_ids"
688 FROM "member";
690 COMMENT ON VIEW "follower" IS 'Provides the contacts of each member that are being followed (including the member itself) as an array of IDs';
692 CREATE OR REPLACE FUNCTION "check_issue"
693 ( "issue_id_p" "issue"."id"%TYPE,
694 "persist" "check_issue_persistence" )
695 RETURNS "check_issue_persistence"
696 LANGUAGE 'plpgsql' VOLATILE AS $$
697 DECLARE
698 "issue_row" "issue"%ROWTYPE;
699 "last_calculated_v" "snapshot"."calculated"%TYPE;
700 "policy_row" "policy"%ROWTYPE;
701 "initiative_row" "initiative"%ROWTYPE;
702 "state_v" "issue_state";
703 BEGIN
704 PERFORM "require_transaction_isolation"();
705 IF "persist" ISNULL THEN
706 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
707 FOR UPDATE;
708 SELECT "calculated" INTO "last_calculated_v"
709 FROM "snapshot" JOIN "snapshot_issue"
710 ON "snapshot"."id" = "snapshot_issue"."snapshot_id"
711 WHERE "snapshot_issue"."issue_id" = "issue_id_p"
712 ORDER BY "snapshot"."id" DESC;
713 IF "issue_row"."closed" NOTNULL THEN
714 RETURN NULL;
715 END IF;
716 "persist"."state" := "issue_row"."state";
717 IF
718 ( "issue_row"."state" = 'admission' AND "last_calculated_v" >=
719 "issue_row"."created" + "issue_row"."max_admission_time" ) OR
720 ( "issue_row"."state" = 'discussion' AND now() >=
721 "issue_row"."accepted" + "issue_row"."discussion_time" ) OR
722 ( "issue_row"."state" = 'verification' AND now() >=
723 "issue_row"."half_frozen" + "issue_row"."verification_time" ) OR
724 ( "issue_row"."state" = 'voting' AND now() >=
725 "issue_row"."fully_frozen" + "issue_row"."voting_time" )
726 THEN
727 "persist"."phase_finished" := TRUE;
728 ELSE
729 "persist"."phase_finished" := FALSE;
730 END IF;
731 IF
732 NOT EXISTS (
733 -- all initiatives are revoked
734 SELECT NULL FROM "initiative"
735 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
736 ) AND (
737 -- and issue has not been accepted yet
738 "persist"."state" = 'admission' OR
739 -- or verification time has elapsed
740 ( "persist"."state" = 'verification' AND
741 "persist"."phase_finished" ) OR
742 -- or no initiatives have been revoked lately
743 NOT EXISTS (
744 SELECT NULL FROM "initiative"
745 WHERE "issue_id" = "issue_id_p"
746 AND now() < "revoked" + "issue_row"."verification_time"
747 )
748 )
749 THEN
750 "persist"."issue_revoked" := TRUE;
751 ELSE
752 "persist"."issue_revoked" := FALSE;
753 END IF;
754 IF "persist"."phase_finished" OR "persist"."issue_revoked" THEN
755 UPDATE "issue" SET "phase_finished" = now()
756 WHERE "id" = "issue_row"."id";
757 RETURN "persist";
758 ELSIF
759 "persist"."state" IN ('admission', 'discussion', 'verification')
760 THEN
761 RETURN "persist";
762 ELSE
763 RETURN NULL;
764 END IF;
765 END IF;
766 IF
767 "persist"."state" IN ('admission', 'discussion', 'verification') AND
768 coalesce("persist"."snapshot_created", FALSE) = FALSE
769 THEN
770 IF "persist"."state" != 'admission' THEN
771 PERFORM "take_snapshot"("issue_id_p");
772 PERFORM "finish_snapshot"("issue_id_p");
773 ELSE
774 UPDATE "issue" SET "issue_quorum" = "issue_quorum"."issue_quorum"
775 FROM "issue_quorum"
776 WHERE "id" = "issue_id_p"
777 AND "issue_quorum"."issue_id" = "issue_id_p";
778 END IF;
779 "persist"."snapshot_created" = TRUE;
780 IF "persist"."phase_finished" THEN
781 IF "persist"."state" = 'admission' THEN
782 UPDATE "issue" SET "admission_snapshot_id" = "latest_snapshot_id"
783 WHERE "id" = "issue_id_p";
784 ELSIF "persist"."state" = 'discussion' THEN
785 UPDATE "issue" SET "half_freeze_snapshot_id" = "latest_snapshot_id"
786 WHERE "id" = "issue_id_p";
787 ELSIF "persist"."state" = 'verification' THEN
788 UPDATE "issue" SET "full_freeze_snapshot_id" = "latest_snapshot_id"
789 WHERE "id" = "issue_id_p";
790 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
791 FOR "initiative_row" IN
792 SELECT * FROM "initiative"
793 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
794 FOR UPDATE
795 LOOP
796 IF
797 "initiative_row"."polling" OR
798 "initiative_row"."satisfied_supporter_count" >=
799 "issue_row"."initiative_quorum"
800 THEN
801 UPDATE "initiative" SET "admitted" = TRUE
802 WHERE "id" = "initiative_row"."id";
803 ELSE
804 UPDATE "initiative" SET "admitted" = FALSE
805 WHERE "id" = "initiative_row"."id";
806 END IF;
807 END LOOP;
808 END IF;
809 END IF;
810 RETURN "persist";
811 END IF;
812 IF
813 "persist"."state" IN ('admission', 'discussion', 'verification') AND
814 coalesce("persist"."harmonic_weights_set", FALSE) = FALSE
815 THEN
816 PERFORM "set_harmonic_initiative_weights"("issue_id_p");
817 "persist"."harmonic_weights_set" = TRUE;
818 IF
819 "persist"."phase_finished" OR
820 "persist"."issue_revoked" OR
821 "persist"."state" = 'admission'
822 THEN
823 RETURN "persist";
824 ELSE
825 RETURN NULL;
826 END IF;
827 END IF;
828 IF "persist"."issue_revoked" THEN
829 IF "persist"."state" = 'admission' THEN
830 "state_v" := 'canceled_revoked_before_accepted';
831 ELSIF "persist"."state" = 'discussion' THEN
832 "state_v" := 'canceled_after_revocation_during_discussion';
833 ELSIF "persist"."state" = 'verification' THEN
834 "state_v" := 'canceled_after_revocation_during_verification';
835 END IF;
836 UPDATE "issue" SET
837 "state" = "state_v",
838 "closed" = "phase_finished",
839 "phase_finished" = NULL
840 WHERE "id" = "issue_id_p";
841 RETURN NULL;
842 END IF;
843 IF "persist"."state" = 'admission' THEN
844 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
845 FOR UPDATE;
846 IF "issue_row"."phase_finished" NOTNULL THEN
847 UPDATE "issue" SET
848 "state" = 'canceled_issue_not_accepted',
849 "closed" = "phase_finished",
850 "phase_finished" = NULL
851 WHERE "id" = "issue_id_p";
852 END IF;
853 RETURN NULL;
854 END IF;
855 IF "persist"."phase_finished" THEN
856 IF "persist"."state" = 'discussion' THEN
857 UPDATE "issue" SET
858 "state" = 'verification',
859 "half_frozen" = "phase_finished",
860 "phase_finished" = NULL
861 WHERE "id" = "issue_id_p";
862 RETURN NULL;
863 END IF;
864 IF "persist"."state" = 'verification' THEN
865 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
866 FOR UPDATE;
867 SELECT * INTO "policy_row" FROM "policy"
868 WHERE "id" = "issue_row"."policy_id";
869 IF EXISTS (
870 SELECT NULL FROM "initiative"
871 WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE
872 ) THEN
873 UPDATE "issue" SET
874 "state" = 'voting',
875 "fully_frozen" = "phase_finished",
876 "phase_finished" = NULL
877 WHERE "id" = "issue_id_p";
878 ELSE
879 UPDATE "issue" SET
880 "state" = 'canceled_no_initiative_admitted',
881 "fully_frozen" = "phase_finished",
882 "closed" = "phase_finished",
883 "phase_finished" = NULL
884 WHERE "id" = "issue_id_p";
885 -- NOTE: The following DELETE statements have effect only when
886 -- issue state has been manipulated
887 DELETE FROM "direct_voter" WHERE "issue_id" = "issue_id_p";
888 DELETE FROM "delegating_voter" WHERE "issue_id" = "issue_id_p";
889 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
890 END IF;
891 RETURN NULL;
892 END IF;
893 IF "persist"."state" = 'voting' THEN
894 IF coalesce("persist"."closed_voting", FALSE) = FALSE THEN
895 PERFORM "close_voting"("issue_id_p");
896 "persist"."closed_voting" = TRUE;
897 RETURN "persist";
898 END IF;
899 PERFORM "calculate_ranks"("issue_id_p");
900 RETURN NULL;
901 END IF;
902 END IF;
903 RAISE WARNING 'should not happen';
904 RETURN NULL;
905 END;
906 $$;
908 CREATE OR REPLACE FUNCTION "check_everything"()
909 RETURNS VOID
910 LANGUAGE 'plpgsql' VOLATILE AS $$
911 DECLARE
912 "area_id_v" "area"."id"%TYPE;
913 "snapshot_id_v" "snapshot"."id"%TYPE;
914 "issue_id_v" "issue"."id"%TYPE;
915 "persist_v" "check_issue_persistence";
916 BEGIN
917 RAISE WARNING 'Function "check_everything" should only be used for development and debugging purposes';
918 DELETE FROM "expired_session";
919 DELETE FROM "expired_token";
920 DELETE FROM "unused_snapshot";
921 PERFORM "check_activity"();
922 PERFORM "calculate_member_counts"();
923 FOR "area_id_v" IN SELECT "id" FROM "area_with_unaccepted_issues" LOOP
924 SELECT "take_snapshot"(NULL, "area_id_v") INTO "snapshot_id_v";
925 PERFORM "finish_snapshot"("issue_id") FROM "snapshot_issue"
926 WHERE "snapshot_id" = "snapshot_id_v";
927 LOOP
928 EXIT WHEN "issue_admission"("area_id_v") = FALSE;
929 END LOOP;
930 END LOOP;
931 FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP
932 "persist_v" := NULL;
933 LOOP
934 "persist_v" := "check_issue"("issue_id_v", "persist_v");
935 EXIT WHEN "persist_v" ISNULL;
936 END LOOP;
937 END LOOP;
938 DELETE FROM "unused_snapshot";
939 RETURN;
940 END;
941 $$;
943 CREATE OR REPLACE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)
944 RETURNS VOID
945 LANGUAGE 'plpgsql' VOLATILE AS $$
946 BEGIN
947 UPDATE "member" SET
948 "last_login" = NULL,
949 "last_delegation_check" = NULL,
950 "login" = NULL,
951 "password" = NULL,
952 "authority" = NULL,
953 "authority_uid" = NULL,
954 "authority_login" = NULL,
955 "deleted" = coalesce("deleted", now()),
956 "locked" = TRUE,
957 "active" = FALSE,
958 "notify_email" = NULL,
959 "notify_email_unconfirmed" = NULL,
960 "notify_email_secret" = NULL,
961 "notify_email_secret_expiry" = NULL,
962 "notify_email_lock_expiry" = NULL,
963 "disable_notifications" = TRUE,
964 "notification_counter" = DEFAULT,
965 "notification_sample_size" = 0,
966 "notification_dow" = NULL,
967 "notification_hour" = NULL,
968 "notification_sent" = NULL,
969 "login_recovery_expiry" = NULL,
970 "password_reset_secret" = NULL,
971 "password_reset_secret_expiry" = NULL,
972 "location" = NULL
973 WHERE "id" = "member_id_p";
974 DELETE FROM "member_settings" WHERE "member_id" = "member_id_p";
975 DELETE FROM "member_profile" WHERE "member_id" = "member_id_p";
976 DELETE FROM "rendered_member_statement" WHERE "member_id" = "member_id_p";
977 DELETE FROM "member_image" WHERE "member_id" = "member_id_p";
978 DELETE FROM "contact" WHERE "member_id" = "member_id_p";
979 DELETE FROM "ignored_member" WHERE "member_id" = "member_id_p";
980 DELETE FROM "session" WHERE "member_id" = "member_id_p";
981 DELETE FROM "member_application" WHERE "member_id" = "member_id_p";
982 DELETE FROM "token" WHERE "member_id" = "member_id_p";
983 DELETE FROM "subscription" WHERE "member_id" = "member_id_p";
984 DELETE FROM "ignored_area" WHERE "member_id" = "member_id_p";
985 DELETE FROM "ignored_initiative" WHERE "member_id" = "member_id_p";
986 DELETE FROM "delegation" WHERE "truster_id" = "member_id_p";
987 DELETE FROM "non_voter" WHERE "member_id" = "member_id_p";
988 DELETE FROM "direct_voter" USING "issue"
989 WHERE "direct_voter"."issue_id" = "issue"."id"
990 AND "issue"."closed" ISNULL
991 AND "member_id" = "member_id_p";
992 DELETE FROM "notification_initiative_sent" WHERE "member_id" = "member_id_p";
993 RETURN;
994 END;
995 $$;
997 CREATE OR REPLACE FUNCTION "delete_private_data"()
998 RETURNS VOID
999 LANGUAGE 'plpgsql' VOLATILE AS $$
1000 BEGIN
1001 DELETE FROM "temporary_transaction_data";
1002 DELETE FROM "temporary_suggestion_counts";
1003 DELETE FROM "member" WHERE "activated" ISNULL;
1004 UPDATE "member" SET
1005 "invite_code" = NULL,
1006 "invite_code_expiry" = NULL,
1007 "admin_comment" = NULL,
1008 "last_login" = NULL,
1009 "last_delegation_check" = NULL,
1010 "login" = NULL,
1011 "password" = NULL,
1012 "authority" = NULL,
1013 "authority_uid" = NULL,
1014 "authority_login" = NULL,
1015 "lang" = NULL,
1016 "notify_email" = NULL,
1017 "notify_email_unconfirmed" = NULL,
1018 "notify_email_secret" = NULL,
1019 "notify_email_secret_expiry" = NULL,
1020 "notify_email_lock_expiry" = NULL,
1021 "disable_notifications" = TRUE,
1022 "notification_counter" = DEFAULT,
1023 "notification_sample_size" = 0,
1024 "notification_dow" = NULL,
1025 "notification_hour" = NULL,
1026 "notification_sent" = NULL,
1027 "login_recovery_expiry" = NULL,
1028 "password_reset_secret" = NULL,
1029 "password_reset_secret_expiry" = NULL,
1030 "location" = NULL;
1031 DELETE FROM "verification";
1032 DELETE FROM "member_settings";
1033 DELETE FROM "member_useterms";
1034 DELETE FROM "member_profile";
1035 DELETE FROM "rendered_member_statement";
1036 DELETE FROM "member_image";
1037 DELETE FROM "contact";
1038 DELETE FROM "ignored_member";
1039 DELETE FROM "session";
1040 DELETE FROM "system_application";
1041 DELETE FROM "system_application_redirect_uri";
1042 DELETE FROM "dynamic_application_scope";
1043 DELETE FROM "member_application";
1044 DELETE FROM "token";
1045 DELETE FROM "subscription";
1046 DELETE FROM "ignored_area";
1047 DELETE FROM "ignored_initiative";
1048 DELETE FROM "non_voter";
1049 DELETE FROM "direct_voter" USING "issue"
1050 WHERE "direct_voter"."issue_id" = "issue"."id"
1051 AND "issue"."closed" ISNULL;
1052 DELETE FROM "event_processed";
1053 DELETE FROM "notification_initiative_sent";
1054 DELETE FROM "newsletter";
1055 RETURN;
1056 END;
1057 $$;
1059 CREATE VIEW "member_eligible_to_be_notified" AS
1060 SELECT * FROM "member"
1061 WHERE "activated" NOTNULL AND "locked" = FALSE;
1063 COMMENT ON VIEW "member_eligible_to_be_notified" IS 'Filtered "member" table containing only activated and non-locked members (used as helper view for "member_to_notify" and "newsletter_to_send")';
1065 CREATE VIEW "member_to_notify" AS
1066 SELECT * FROM "member_eligible_to_be_notified"
1067 WHERE "disable_notifications" = FALSE;
1069 COMMENT ON VIEW "member_to_notify" IS 'Filtered "member" table containing only members that are eligible to and wish to receive notifications; NOTE: "notify_email" may still be NULL and might need to be checked by frontend (this allows other means of messaging)';
1071 CREATE VIEW "area_with_unaccepted_issues" AS
1072 SELECT DISTINCT ON ("area"."id") "area".*
1073 FROM "area" JOIN "issue" ON "area"."id" = "issue"."area_id"
1074 WHERE "issue"."state" = 'admission';
1076 COMMENT ON VIEW "area_with_unaccepted_issues" IS 'All areas with unaccepted open issues (needed for issue admission system)';
1078 CREATE VIEW "opening_draft" AS
1079 SELECT DISTINCT ON ("initiative_id") * FROM "draft"
1080 ORDER BY "initiative_id", "id";
1082 COMMENT ON VIEW "opening_draft" IS 'First drafts of all initiatives';
1084 CREATE VIEW "current_draft" AS
1085 SELECT DISTINCT ON ("initiative_id") * FROM "draft"
1086 ORDER BY "initiative_id", "id" DESC;
1088 COMMENT ON VIEW "current_draft" IS 'All latest drafts for each initiative';
1090 CREATE VIEW "member_contingent" AS
1091 SELECT
1092 "member"."id" AS "member_id",
1093 "contingent"."polling",
1094 "contingent"."time_frame",
1095 CASE WHEN "contingent"."text_entry_limit" NOTNULL THEN
1097 SELECT count(1) FROM "draft"
1098 JOIN "initiative" ON "initiative"."id" = "draft"."initiative_id"
1099 WHERE "draft"."author_id" = "member"."id"
1100 AND "initiative"."polling" = "contingent"."polling"
1101 AND "draft"."created" > now() - "contingent"."time_frame"
1102 ) + (
1103 SELECT count(1) FROM "suggestion"
1104 JOIN "initiative" ON "initiative"."id" = "suggestion"."initiative_id"
1105 WHERE "suggestion"."author_id" = "member"."id"
1106 AND "contingent"."polling" = FALSE
1107 AND "suggestion"."created" > now() - "contingent"."time_frame"
1109 ELSE NULL END AS "text_entry_count",
1110 "contingent"."text_entry_limit",
1111 CASE WHEN "contingent"."initiative_limit" NOTNULL THEN (
1112 SELECT count(1) FROM "opening_draft" AS "draft"
1113 JOIN "initiative" ON "initiative"."id" = "draft"."initiative_id"
1114 WHERE "draft"."author_id" = "member"."id"
1115 AND "initiative"."polling" = "contingent"."polling"
1116 AND "draft"."created" > now() - "contingent"."time_frame"
1117 ) ELSE NULL END AS "initiative_count",
1118 "contingent"."initiative_limit"
1119 FROM "member" CROSS JOIN "contingent";
1121 COMMENT ON VIEW "member_contingent" IS 'Actual counts of text entries and initiatives are calculated per member for each limit in the "contingent" table.';
1123 COMMENT ON COLUMN "member_contingent"."text_entry_count" IS 'Only calculated when "text_entry_limit" is not null in the same row';
1124 COMMENT ON COLUMN "member_contingent"."initiative_count" IS 'Only calculated when "initiative_limit" is not null in the same row';
1126 CREATE VIEW "member_contingent_left" AS
1127 SELECT
1128 "member_id",
1129 "polling",
1130 max("text_entry_limit" - "text_entry_count") AS "text_entries_left",
1131 max("initiative_limit" - "initiative_count") AS "initiatives_left"
1132 FROM "member_contingent" GROUP BY "member_id", "polling";
1134 COMMENT ON VIEW "member_contingent_left" IS 'Amount of text entries or initiatives which can be posted now instantly by a member. This view should be used by a frontend to determine, if the contingent for posting is exhausted.';
1136 CREATE VIEW "scheduled_notification_to_send" AS
1137 SELECT * FROM (
1138 SELECT
1139 "id" AS "recipient_id",
1140 now() - CASE WHEN "notification_dow" ISNULL THEN
1141 ( "notification_sent"::DATE + CASE
1142 WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour"
1143 THEN 0 ELSE 1 END
1144 )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour"
1145 ELSE
1146 ( "notification_sent"::DATE +
1147 ( 7 + "notification_dow" -
1148 EXTRACT(DOW FROM
1149 ( "notification_sent"::DATE + CASE
1150 WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour"
1151 THEN 0 ELSE 1 END
1152 )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour"
1153 )::INTEGER
1154 ) % 7 +
1155 CASE
1156 WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour"
1157 THEN 0 ELSE 1
1158 END
1159 )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour"
1160 END AS "pending"
1161 FROM (
1162 SELECT
1163 "id",
1164 COALESCE("notification_sent", "activated") AS "notification_sent",
1165 "notification_dow",
1166 "notification_hour"
1167 FROM "member_to_notify"
1168 WHERE "notification_hour" NOTNULL
1169 ) AS "subquery1"
1170 ) AS "subquery2"
1171 WHERE "pending" > '0'::INTERVAL;
1173 COMMENT ON VIEW "scheduled_notification_to_send" IS 'Set of members where a scheduled notification mail is pending';
1175 COMMENT ON COLUMN "scheduled_notification_to_send"."recipient_id" IS '"id" of the member who needs to receive a notification mail';
1176 COMMENT ON COLUMN "scheduled_notification_to_send"."pending" IS 'Duration for which the notification mail has already been pending';
1178 CREATE VIEW "newsletter_to_send" AS
1179 SELECT
1180 "member"."id" AS "recipient_id",
1181 "newsletter"."id" AS "newsletter_id",
1182 "newsletter"."published"
1183 FROM "newsletter" CROSS JOIN "member_eligible_to_be_notified" AS "member"
1184 LEFT JOIN "privilege" ON
1185 "privilege"."member_id" = "member"."id" AND
1186 "privilege"."unit_id" = "newsletter"."unit_id" AND
1187 "privilege"."voting_right" = TRUE
1188 LEFT JOIN "subscription" ON
1189 "subscription"."member_id" = "member"."id" AND
1190 "subscription"."unit_id" = "newsletter"."unit_id"
1191 WHERE "newsletter"."published" <= now()
1192 AND "newsletter"."sent" ISNULL
1193 AND (
1194 "member"."disable_notifications" = FALSE OR
1195 "newsletter"."include_all_members" = TRUE )
1196 AND (
1197 "newsletter"."unit_id" ISNULL OR
1198 "privilege"."member_id" NOTNULL OR
1199 "subscription"."member_id" NOTNULL );
1201 COMMENT ON VIEW "newsletter_to_send" IS 'List of "newsletter_id"s for each member that are due to be sent out';
1203 COMMENT ON COLUMN "newsletter"."published" IS 'Timestamp when the newsletter was supposed to be sent out (can be used for ordering)';
1205 SELECT "copy_current_draft_data" ("id") FROM "initiative";
1207 END;

Impressum / About Us