liquid_feedback_core

annotate update/core-update.v4.1.0-v4.2.0.sql @ 604:f69e0d9e5bb3

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

Impressum / About Us