liquid_feedback_core

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

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

Impressum / About Us