liquid_feedback_core

annotate update/core-update.v4.1.0-v4.2.1.sql @ 619:63092784fe9d

find changesets by author, revision, files, or words in the commit message
Removed indices for timeline retrieval (removes dependency on pgConflux); Changed update script to previous version 4.2.0 to enforce particular version of pgConflux; Added update script to skip intermediate dependency on pgConflux
author jbe
date Sat Dec 05 04:04:52 2020 +0100 (2020-12-05)
parents
children
rev   line source
jbe@619 1 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
jbe@619 2 SELECT * FROM (VALUES ('4.2.0-incomplete-update', 4, 2, -1))
jbe@619 3 AS "subquery"("string", "major", "minor", "revision");
jbe@619 4
jbe@619 5 ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'posting_created';
jbe@619 6
jbe@619 7 BEGIN;
jbe@619 8
jbe@619 9 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
jbe@619 10 SELECT * FROM (VALUES ('4.2.1', 4, 2, 1))
jbe@619 11 AS "subquery"("string", "major", "minor", "revision");
jbe@619 12
jbe@619 13 DROP VIEW "newsletter_to_send";
jbe@619 14 DROP VIEW "scheduled_notification_to_send";
jbe@619 15 DROP VIEW "member_contingent_left";
jbe@619 16 DROP VIEW "member_contingent";
jbe@619 17 DROP VIEW "expired_snapshot";
jbe@619 18 DROP VIEW "current_draft";
jbe@619 19 DROP VIEW "opening_draft";
jbe@619 20 DROP VIEW "area_with_unaccepted_issues";
jbe@619 21 DROP VIEW "member_to_notify";
jbe@619 22 DROP VIEW "member_eligible_to_be_notified";
jbe@619 23
jbe@619 24 DROP FUNCTION "text_search_query" (TEXT);
jbe@619 25
jbe@619 26 ALTER TABLE "system_setting" DROP COLUMN "snapshot_retention";
jbe@619 27
jbe@619 28 CREATE TABLE "file" (
jbe@619 29 "id" SERIAL8 PRIMARY KEY,
jbe@619 30 UNIQUE ("content_type", "hash"),
jbe@619 31 "content_type" TEXT NOT NULL,
jbe@619 32 "hash" TEXT NOT NULL,
jbe@619 33 "data" BYTEA NOT NULL,
jbe@619 34 "preview_content_type" TEXT,
jbe@619 35 "preview_data" BYTEA );
jbe@619 36
jbe@619 37 COMMENT ON TABLE "file" IS 'Table holding file contents for draft attachments';
jbe@619 38
jbe@619 39 COMMENT ON COLUMN "file"."content_type" IS 'Content type of "data"';
jbe@619 40 COMMENT ON COLUMN "file"."hash" IS 'Hash of "data" to avoid storing duplicates where content-type and data is identical';
jbe@619 41 COMMENT ON COLUMN "file"."data" IS 'Binary content';
jbe@619 42 COMMENT ON COLUMN "file"."preview_content_type" IS 'Content type of "preview_data"';
jbe@619 43 COMMENT ON COLUMN "file"."preview_data" IS 'Preview (e.g. preview image)';
jbe@619 44
jbe@619 45 ALTER TABLE "member" DROP COLUMN "text_search_data";
jbe@619 46 DROP TRIGGER "update_text_search_data" ON "member";
jbe@619 47
jbe@619 48 CREATE INDEX "member_useterms_member_id_contract_identifier" ON "member_useterms" ("member_id", "contract_identifier");
jbe@619 49
jbe@619 50 ALTER TABLE "member_profile" DROP COLUMN "text_search_data";
jbe@619 51 DROP TRIGGER "update_text_search_data" ON "member_profile";
jbe@619 52
jbe@619 53 ALTER TABLE "contact" ADD COLUMN "following" BOOLEAN NOT NULL DEFAULT TRUE;
jbe@619 54
jbe@619 55 COMMENT ON COLUMN "contact"."following" IS 'TRUE = actions of contact are shown in personal timeline';
jbe@619 56
jbe@619 57 ALTER TABLE "unit" DROP COLUMN "text_search_data";
jbe@619 58 DROP TRIGGER "update_text_search_data" ON "unit";
jbe@619 59
jbe@619 60 ALTER TABLE "area" DROP COLUMN "text_search_data";
jbe@619 61 DROP TRIGGER "update_text_search_data" ON "area";
jbe@619 62
jbe@619 63 DROP INDEX "issue_accepted_idx";
jbe@619 64 DROP INDEX "issue_half_frozen_idx";
jbe@619 65 DROP INDEX "issue_fully_frozen_idx";
jbe@619 66 ALTER INDEX "issue_created_idx_open" RENAME TO "issue_open_created_idx";
jbe@619 67 DROP INDEX "issue_closed_idx_canceled";
jbe@619 68 ALTER INDEX "issue_latest_snapshot_id" RENAME TO "issue_latest_snapshot_id_idx";
jbe@619 69 ALTER INDEX "issue_admission_snapshot_id" RENAME TO "issue_admission_snapshot_id_idx";
jbe@619 70 ALTER INDEX "issue_half_freeze_snapshot_id" RENAME TO "issue_half_freeze_snapshot_id_idx";
jbe@619 71 ALTER INDEX "issue_full_freeze_snapshot_id" RENAME TO "issue_full_freeze_snapshot_id_idx";
jbe@619 72
jbe@619 73 ALTER TABLE "initiative" ADD COLUMN "content" TEXT;
jbe@619 74 ALTER TABLE "initiative" DROP COLUMN "text_search_data";
jbe@619 75 ALTER TABLE "initiative" DROP COLUMN "draft_text_search_data";
jbe@619 76 DROP INDEX "initiative_revoked_idx";
jbe@619 77 DROP TRIGGER "update_text_search_data" ON "initiative";
jbe@619 78
jbe@619 79 COMMENT ON COLUMN "initiative"."content" IS 'Initiative text (automatically copied from most recent draft)';
jbe@619 80
jbe@619 81 ALTER TABLE "battle" DROP CONSTRAINT "initiative_ids_not_equal";
jbe@619 82 ALTER TABLE "battle" ADD CONSTRAINT "initiative_ids_not_equal" CHECK (
jbe@619 83 "winning_initiative_id" != "losing_initiative_id" AND
jbe@619 84 ("winning_initiative_id" NOTNULL OR "losing_initiative_id" NOTNULL) );
jbe@619 85
jbe@619 86 ALTER TABLE "draft" DROP COLUMN "text_search_data";
jbe@619 87 DROP TRIGGER "update_text_search_data" ON "draft";
jbe@619 88
jbe@619 89 CREATE TABLE "draft_attachment" (
jbe@619 90 "id" SERIAL8 PRIMARY KEY,
jbe@619 91 "draft_id" INT8 REFERENCES "draft" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@619 92 "file_id" INT8 REFERENCES "file" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
jbe@619 93 "title" TEXT,
jbe@619 94 "description" TEXT );
jbe@619 95
jbe@619 96 COMMENT ON TABLE "draft_attachment" IS 'Binary attachments for a draft (images, PDF file, etc.); Implicitly ordered through ''id'' column';
jbe@619 97
jbe@619 98 ALTER TABLE "suggestion" DROP COLUMN "text_search_data";
jbe@619 99 DROP TRIGGER "update_text_search_data" ON "suggestion";
jbe@619 100
jbe@619 101 ALTER TABLE "direct_voter" DROP COLUMN "text_search_data";
jbe@619 102 DROP TRIGGER "update_text_search_data" ON "direct_voter";
jbe@619 103
jbe@619 104 CREATE TABLE "posting" (
jbe@619 105 UNIQUE ("author_id", "id"), -- index needed for foreign-key on table "posting_lexeme"
jbe@619 106 "id" SERIAL8 PRIMARY KEY,
jbe@619 107 "author_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
jbe@619 108 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
jbe@619 109 "message" TEXT NOT NULL,
jbe@619 110 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@619 111 "area_id" INT4,
jbe@619 112 FOREIGN KEY ("unit_id", "area_id") REFERENCES "area" ("unit_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@619 113 "policy_id" INT4 REFERENCES "policy" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@619 114 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@619 115 FOREIGN KEY ("area_id", "issue_id") REFERENCES "issue" ("area_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@619 116 FOREIGN KEY ("policy_id", "issue_id") REFERENCES "issue" ("policy_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@619 117 "initiative_id" INT4,
jbe@619 118 "suggestion_id" INT8,
jbe@619 119 -- NOTE: no referential integrity for suggestions because those are
jbe@619 120 -- actually deleted
jbe@619 121 -- FOREIGN KEY ("initiative_id", "suggestion_id")
jbe@619 122 -- REFERENCES "suggestion" ("initiative_id", "id")
jbe@619 123 -- ON DELETE CASCADE ON UPDATE CASCADE,
jbe@619 124 CONSTRAINT "area_requires_unit" CHECK (
jbe@619 125 "area_id" ISNULL OR "unit_id" NOTNULL ),
jbe@619 126 CONSTRAINT "policy_set_when_issue_set" CHECK (
jbe@619 127 ("policy_id" NOTNULL) = ("issue_id" NOTNULL) ),
jbe@619 128 CONSTRAINT "issue_requires_area" CHECK (
jbe@619 129 "issue_id" ISNULL OR "area_id" NOTNULL ),
jbe@619 130 CONSTRAINT "initiative_requires_issue" CHECK (
jbe@619 131 "initiative_id" ISNULL OR "issue_id" NOTNULL ),
jbe@619 132 CONSTRAINT "suggestion_requires_initiative" CHECK (
jbe@619 133 "suggestion_id" ISNULL OR "initiative_id" NOTNULL ) );
jbe@619 134 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@619 135
jbe@619 136 CREATE TABLE "posting_lexeme" (
jbe@619 137 PRIMARY KEY ("posting_id", "lexeme"),
jbe@619 138 FOREIGN KEY ("posting_id", "author_id") REFERENCES "posting" ("id", "author_id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@619 139 "posting_id" INT8,
jbe@619 140 "lexeme" TEXT,
jbe@619 141 "author_id" INT4 );
jbe@619 142
jbe@619 143 COMMENT ON TABLE "posting_lexeme" IS 'Helper table to allow searches for hashtags.';
jbe@619 144
jbe@619 145 ALTER TABLE "event" ADD COLUMN "posting_id" INT8 REFERENCES "posting" ("id") ON DELETE RESTRICT ON UPDATE CASCADE;
jbe@619 146 ALTER TABLE "event" DROP CONSTRAINT "constr_for_issue_state_changed";
jbe@619 147 ALTER TABLE "event" DROP CONSTRAINT "constr_for_initiative_creation_or_revocation_or_new_draft";
jbe@619 148 ALTER TABLE "event" DROP CONSTRAINT "constr_for_suggestion_creation";
jbe@619 149 ALTER TABLE "event" DROP CONSTRAINT "constr_for_suggestion_removal";
jbe@619 150 ALTER TABLE "event" DROP CONSTRAINT "constr_for_value_less_member_event";
jbe@619 151 ALTER TABLE "event" DROP CONSTRAINT "constr_for_member_active";
jbe@619 152 ALTER TABLE "event" DROP CONSTRAINT "constr_for_member_name_updated";
jbe@619 153 ALTER TABLE "event" DROP CONSTRAINT "constr_for_interest";
jbe@619 154 ALTER TABLE "event" DROP CONSTRAINT "constr_for_initiator";
jbe@619 155 ALTER TABLE "event" DROP CONSTRAINT "constr_for_support";
jbe@619 156 ALTER TABLE "event" DROP CONSTRAINT "constr_for_support_updated";
jbe@619 157 ALTER TABLE "event" DROP CONSTRAINT "constr_for_suggestion_rated";
jbe@619 158 ALTER TABLE "event" DROP CONSTRAINT "constr_for_delegation";
jbe@619 159 ALTER TABLE "event" DROP CONSTRAINT "constr_for_contact";
jbe@619 160 ALTER TABLE "event" ADD
jbe@619 161 CONSTRAINT "constr_for_issue_state_changed" CHECK (
jbe@619 162 "event" != 'issue_state_changed' OR (
jbe@619 163 "posting_id" ISNULL AND
jbe@619 164 "member_id" ISNULL AND
jbe@619 165 "other_member_id" ISNULL AND
jbe@619 166 "scope" ISNULL AND
jbe@619 167 "unit_id" NOTNULL AND
jbe@619 168 "area_id" NOTNULL AND
jbe@619 169 "policy_id" NOTNULL AND
jbe@619 170 "issue_id" NOTNULL AND
jbe@619 171 "state" NOTNULL AND
jbe@619 172 "initiative_id" ISNULL AND
jbe@619 173 "draft_id" ISNULL AND
jbe@619 174 "suggestion_id" ISNULL AND
jbe@619 175 "boolean_value" ISNULL AND
jbe@619 176 "numeric_value" ISNULL AND
jbe@619 177 "text_value" ISNULL AND
jbe@619 178 "old_text_value" ISNULL ));
jbe@619 179 ALTER TABLE "event" ADD
jbe@619 180 CONSTRAINT "constr_for_initiative_creation_or_revocation_or_new_draft" CHECK (
jbe@619 181 "event" NOT IN (
jbe@619 182 'initiative_created_in_new_issue',
jbe@619 183 'initiative_created_in_existing_issue',
jbe@619 184 'initiative_revoked',
jbe@619 185 'new_draft_created'
jbe@619 186 ) OR (
jbe@619 187 "posting_id" ISNULL AND
jbe@619 188 "member_id" NOTNULL AND
jbe@619 189 "other_member_id" ISNULL AND
jbe@619 190 "scope" ISNULL AND
jbe@619 191 "unit_id" NOTNULL AND
jbe@619 192 "area_id" NOTNULL AND
jbe@619 193 "policy_id" NOTNULL AND
jbe@619 194 "issue_id" NOTNULL AND
jbe@619 195 "state" NOTNULL AND
jbe@619 196 "initiative_id" NOTNULL AND
jbe@619 197 "draft_id" NOTNULL AND
jbe@619 198 "suggestion_id" ISNULL AND
jbe@619 199 "boolean_value" ISNULL AND
jbe@619 200 "numeric_value" ISNULL AND
jbe@619 201 "text_value" ISNULL AND
jbe@619 202 "old_text_value" ISNULL ));
jbe@619 203 ALTER TABLE "event" ADD
jbe@619 204 CONSTRAINT "constr_for_suggestion_creation" CHECK (
jbe@619 205 "event" != 'suggestion_created' OR (
jbe@619 206 "posting_id" ISNULL AND
jbe@619 207 "member_id" NOTNULL AND
jbe@619 208 "other_member_id" ISNULL AND
jbe@619 209 "scope" ISNULL AND
jbe@619 210 "unit_id" NOTNULL AND
jbe@619 211 "area_id" NOTNULL AND
jbe@619 212 "policy_id" NOTNULL AND
jbe@619 213 "issue_id" NOTNULL AND
jbe@619 214 "state" NOTNULL AND
jbe@619 215 "initiative_id" NOTNULL AND
jbe@619 216 "draft_id" ISNULL AND
jbe@619 217 "suggestion_id" NOTNULL AND
jbe@619 218 "boolean_value" ISNULL AND
jbe@619 219 "numeric_value" ISNULL AND
jbe@619 220 "text_value" ISNULL AND
jbe@619 221 "old_text_value" ISNULL ));
jbe@619 222 ALTER TABLE "event" ADD
jbe@619 223 CONSTRAINT "constr_for_suggestion_removal" CHECK (
jbe@619 224 "event" != 'suggestion_deleted' OR (
jbe@619 225 "posting_id" ISNULL AND
jbe@619 226 "member_id" ISNULL AND
jbe@619 227 "other_member_id" ISNULL AND
jbe@619 228 "scope" ISNULL AND
jbe@619 229 "unit_id" NOTNULL AND
jbe@619 230 "area_id" NOTNULL AND
jbe@619 231 "policy_id" NOTNULL AND
jbe@619 232 "issue_id" NOTNULL AND
jbe@619 233 "state" NOTNULL AND
jbe@619 234 "initiative_id" NOTNULL AND
jbe@619 235 "draft_id" ISNULL AND
jbe@619 236 "suggestion_id" NOTNULL AND
jbe@619 237 "boolean_value" ISNULL AND
jbe@619 238 "numeric_value" ISNULL AND
jbe@619 239 "text_value" ISNULL AND
jbe@619 240 "old_text_value" ISNULL ));
jbe@619 241 ALTER TABLE "event" ADD
jbe@619 242 CONSTRAINT "constr_for_value_less_member_event" CHECK (
jbe@619 243 "event" NOT IN (
jbe@619 244 'member_activated',
jbe@619 245 'member_deleted',
jbe@619 246 'member_profile_updated',
jbe@619 247 'member_image_updated'
jbe@619 248 ) OR (
jbe@619 249 "posting_id" ISNULL AND
jbe@619 250 "member_id" NOTNULL AND
jbe@619 251 "other_member_id" ISNULL AND
jbe@619 252 "scope" ISNULL AND
jbe@619 253 "unit_id" ISNULL AND
jbe@619 254 "area_id" ISNULL AND
jbe@619 255 "policy_id" ISNULL AND
jbe@619 256 "issue_id" ISNULL AND
jbe@619 257 "state" ISNULL AND
jbe@619 258 "initiative_id" ISNULL AND
jbe@619 259 "draft_id" ISNULL AND
jbe@619 260 "suggestion_id" ISNULL AND
jbe@619 261 "boolean_value" ISNULL AND
jbe@619 262 "numeric_value" ISNULL AND
jbe@619 263 "text_value" ISNULL AND
jbe@619 264 "old_text_value" ISNULL ));
jbe@619 265 ALTER TABLE "event" ADD
jbe@619 266 CONSTRAINT "constr_for_member_active" CHECK (
jbe@619 267 "event" != 'member_active' OR (
jbe@619 268 "posting_id" ISNULL AND
jbe@619 269 "member_id" NOTNULL AND
jbe@619 270 "other_member_id" ISNULL AND
jbe@619 271 "scope" ISNULL AND
jbe@619 272 "unit_id" ISNULL AND
jbe@619 273 "area_id" ISNULL AND
jbe@619 274 "policy_id" ISNULL AND
jbe@619 275 "issue_id" ISNULL AND
jbe@619 276 "state" ISNULL AND
jbe@619 277 "initiative_id" ISNULL AND
jbe@619 278 "draft_id" ISNULL AND
jbe@619 279 "suggestion_id" ISNULL AND
jbe@619 280 "boolean_value" NOTNULL AND
jbe@619 281 "numeric_value" ISNULL AND
jbe@619 282 "text_value" ISNULL AND
jbe@619 283 "old_text_value" ISNULL ));
jbe@619 284 ALTER TABLE "event" ADD
jbe@619 285 CONSTRAINT "constr_for_member_name_updated" CHECK (
jbe@619 286 "event" != 'member_name_updated' OR (
jbe@619 287 "posting_id" ISNULL AND
jbe@619 288 "member_id" NOTNULL AND
jbe@619 289 "other_member_id" ISNULL AND
jbe@619 290 "scope" ISNULL AND
jbe@619 291 "unit_id" ISNULL AND
jbe@619 292 "area_id" ISNULL AND
jbe@619 293 "policy_id" ISNULL AND
jbe@619 294 "issue_id" ISNULL AND
jbe@619 295 "state" ISNULL AND
jbe@619 296 "initiative_id" ISNULL AND
jbe@619 297 "draft_id" ISNULL AND
jbe@619 298 "suggestion_id" ISNULL AND
jbe@619 299 "boolean_value" ISNULL AND
jbe@619 300 "numeric_value" ISNULL AND
jbe@619 301 "text_value" NOTNULL AND
jbe@619 302 "old_text_value" NOTNULL ));
jbe@619 303 ALTER TABLE "event" ADD
jbe@619 304 CONSTRAINT "constr_for_interest" CHECK (
jbe@619 305 "event" != 'interest' OR (
jbe@619 306 "posting_id" ISNULL AND
jbe@619 307 "member_id" NOTNULL AND
jbe@619 308 "other_member_id" ISNULL AND
jbe@619 309 "scope" ISNULL AND
jbe@619 310 "unit_id" NOTNULL AND
jbe@619 311 "area_id" NOTNULL AND
jbe@619 312 "policy_id" NOTNULL AND
jbe@619 313 "issue_id" NOTNULL AND
jbe@619 314 "state" NOTNULL AND
jbe@619 315 "initiative_id" ISNULL AND
jbe@619 316 "draft_id" ISNULL AND
jbe@619 317 "suggestion_id" ISNULL AND
jbe@619 318 "boolean_value" NOTNULL AND
jbe@619 319 "numeric_value" ISNULL AND
jbe@619 320 "text_value" ISNULL AND
jbe@619 321 "old_text_value" ISNULL ));
jbe@619 322 ALTER TABLE "event" ADD
jbe@619 323 CONSTRAINT "constr_for_initiator" CHECK (
jbe@619 324 "event" != 'initiator' OR (
jbe@619 325 "posting_id" ISNULL AND
jbe@619 326 "member_id" NOTNULL AND
jbe@619 327 "other_member_id" ISNULL AND
jbe@619 328 "scope" ISNULL AND
jbe@619 329 "unit_id" NOTNULL AND
jbe@619 330 "area_id" NOTNULL AND
jbe@619 331 "policy_id" NOTNULL AND
jbe@619 332 "issue_id" NOTNULL AND
jbe@619 333 "state" NOTNULL AND
jbe@619 334 "initiative_id" NOTNULL AND
jbe@619 335 "draft_id" ISNULL AND
jbe@619 336 "suggestion_id" ISNULL AND
jbe@619 337 "boolean_value" NOTNULL AND
jbe@619 338 "numeric_value" ISNULL AND
jbe@619 339 "text_value" ISNULL AND
jbe@619 340 "old_text_value" ISNULL ));
jbe@619 341 ALTER TABLE "event" ADD
jbe@619 342 CONSTRAINT "constr_for_support" CHECK (
jbe@619 343 "event" != 'support' OR (
jbe@619 344 "posting_id" ISNULL AND
jbe@619 345 "member_id" NOTNULL AND
jbe@619 346 "other_member_id" ISNULL AND
jbe@619 347 "scope" ISNULL AND
jbe@619 348 "unit_id" NOTNULL AND
jbe@619 349 "area_id" NOTNULL AND
jbe@619 350 "policy_id" NOTNULL AND
jbe@619 351 "issue_id" NOTNULL AND
jbe@619 352 "state" NOTNULL AND
jbe@619 353 "initiative_id" NOTNULL AND
jbe@619 354 ("draft_id" NOTNULL) = ("boolean_value" = TRUE) AND
jbe@619 355 "suggestion_id" ISNULL AND
jbe@619 356 "boolean_value" NOTNULL AND
jbe@619 357 "numeric_value" ISNULL AND
jbe@619 358 "text_value" ISNULL AND
jbe@619 359 "old_text_value" ISNULL ));
jbe@619 360 ALTER TABLE "event" ADD
jbe@619 361 CONSTRAINT "constr_for_support_updated" CHECK (
jbe@619 362 "event" != 'support_updated' OR (
jbe@619 363 "posting_id" ISNULL AND
jbe@619 364 "member_id" NOTNULL AND
jbe@619 365 "other_member_id" ISNULL AND
jbe@619 366 "scope" ISNULL AND
jbe@619 367 "unit_id" NOTNULL AND
jbe@619 368 "area_id" NOTNULL AND
jbe@619 369 "policy_id" NOTNULL AND
jbe@619 370 "issue_id" NOTNULL AND
jbe@619 371 "state" NOTNULL AND
jbe@619 372 "initiative_id" NOTNULL AND
jbe@619 373 "draft_id" NOTNULL AND
jbe@619 374 "suggestion_id" ISNULL AND
jbe@619 375 "boolean_value" ISNULL AND
jbe@619 376 "numeric_value" ISNULL AND
jbe@619 377 "text_value" ISNULL AND
jbe@619 378 "old_text_value" ISNULL ));
jbe@619 379 ALTER TABLE "event" ADD
jbe@619 380 CONSTRAINT "constr_for_suggestion_rated" CHECK (
jbe@619 381 "event" != 'suggestion_rated' OR (
jbe@619 382 "posting_id" ISNULL AND
jbe@619 383 "member_id" NOTNULL AND
jbe@619 384 "other_member_id" ISNULL AND
jbe@619 385 "scope" ISNULL AND
jbe@619 386 "unit_id" NOTNULL AND
jbe@619 387 "area_id" NOTNULL AND
jbe@619 388 "policy_id" NOTNULL AND
jbe@619 389 "issue_id" NOTNULL AND
jbe@619 390 "state" NOTNULL AND
jbe@619 391 "initiative_id" NOTNULL AND
jbe@619 392 "draft_id" ISNULL AND
jbe@619 393 "suggestion_id" NOTNULL AND
jbe@619 394 ("boolean_value" NOTNULL) = ("numeric_value" != 0) AND
jbe@619 395 "numeric_value" NOTNULL AND
jbe@619 396 "numeric_value" IN (-2, -1, 0, 1, 2) AND
jbe@619 397 "text_value" ISNULL AND
jbe@619 398 "old_text_value" ISNULL ));
jbe@619 399 ALTER TABLE "event" ADD
jbe@619 400 CONSTRAINT "constr_for_delegation" CHECK (
jbe@619 401 "event" != 'delegation' OR (
jbe@619 402 "posting_id" ISNULL AND
jbe@619 403 "member_id" NOTNULL AND
jbe@619 404 (("other_member_id" ISNULL) OR ("boolean_value" = TRUE)) AND
jbe@619 405 "scope" NOTNULL AND
jbe@619 406 "unit_id" NOTNULL AND
jbe@619 407 ("area_id" NOTNULL) = ("scope" != 'unit'::"delegation_scope") AND
jbe@619 408 "policy_id" ISNULL AND
jbe@619 409 ("issue_id" NOTNULL) = ("scope" = 'issue'::"delegation_scope") AND
jbe@619 410 ("state" NOTNULL) = ("scope" = 'issue'::"delegation_scope") AND
jbe@619 411 "initiative_id" ISNULL AND
jbe@619 412 "draft_id" ISNULL AND
jbe@619 413 "suggestion_id" ISNULL AND
jbe@619 414 "boolean_value" NOTNULL AND
jbe@619 415 "numeric_value" ISNULL AND
jbe@619 416 "text_value" ISNULL AND
jbe@619 417 "old_text_value" ISNULL ));
jbe@619 418 ALTER TABLE "event" ADD
jbe@619 419 CONSTRAINT "constr_for_contact" CHECK (
jbe@619 420 "event" != 'contact' OR (
jbe@619 421 "posting_id" ISNULL AND
jbe@619 422 "member_id" NOTNULL AND
jbe@619 423 "other_member_id" NOTNULL AND
jbe@619 424 "scope" ISNULL AND
jbe@619 425 "unit_id" ISNULL AND
jbe@619 426 "area_id" ISNULL AND
jbe@619 427 "policy_id" ISNULL AND
jbe@619 428 "issue_id" ISNULL AND
jbe@619 429 "state" ISNULL AND
jbe@619 430 "initiative_id" ISNULL AND
jbe@619 431 "draft_id" ISNULL AND
jbe@619 432 "suggestion_id" ISNULL AND
jbe@619 433 "boolean_value" NOTNULL AND
jbe@619 434 "numeric_value" ISNULL AND
jbe@619 435 "text_value" ISNULL AND
jbe@619 436 "old_text_value" ISNULL ));
jbe@619 437 ALTER TABLE "event" ADD
jbe@619 438 CONSTRAINT "constr_for_posting_created" CHECK (
jbe@619 439 "event" != 'posting_created' OR (
jbe@619 440 "posting_id" NOTNULL AND
jbe@619 441 "member_id" NOTNULL AND
jbe@619 442 "other_member_id" ISNULL AND
jbe@619 443 "scope" ISNULL AND
jbe@619 444 "state" ISNULL AND
jbe@619 445 ("area_id" ISNULL OR "unit_id" NOTNULL) AND
jbe@619 446 ("policy_id" NOTNULL) = ("issue_id" NOTNULL) AND
jbe@619 447 ("issue_id" ISNULL OR "area_id" NOTNULL) AND
jbe@619 448 ("state" NOTNULL) = ("issue_id" NOTNULL) AND
jbe@619 449 ("initiative_id" ISNULL OR "issue_id" NOTNULL) AND
jbe@619 450 "draft_id" ISNULL AND
jbe@619 451 ("suggestion_id" ISNULL OR "initiative_id" NOTNULL) AND
jbe@619 452 "boolean_value" ISNULL AND
jbe@619 453 "numeric_value" ISNULL AND
jbe@619 454 "text_value" ISNULL AND
jbe@619 455 "old_text_value" ISNULL ));
jbe@619 456
jbe@619 457 CREATE OR REPLACE FUNCTION "highlight"
jbe@619 458 ( "body_p" TEXT,
jbe@619 459 "query_text_p" TEXT )
jbe@619 460 RETURNS TEXT
jbe@619 461 LANGUAGE 'plpgsql' IMMUTABLE AS $$
jbe@619 462 BEGIN
jbe@619 463 RETURN ts_headline(
jbe@619 464 replace(replace("body_p", e'\\', e'\\\\'), '*', e'\\*'),
jbe@619 465 "plainto_tsquery"("query_text_p"),
jbe@619 466 'StartSel=* StopSel=* HighlightAll=TRUE' );
jbe@619 467 END;
jbe@619 468 $$;
jbe@619 469
jbe@619 470 CREATE FUNCTION "to_tsvector"("member") RETURNS TSVECTOR
jbe@619 471 LANGUAGE SQL IMMUTABLE AS $$ SELECT to_tsvector(concat_ws(' ',
jbe@619 472 $1."name",
jbe@619 473 $1."identification"
jbe@619 474 )) $$;
jbe@619 475 CREATE INDEX "member_to_tsvector_idx" ON "member" USING gin
jbe@619 476 (("to_tsvector"("member".*)));
jbe@619 477
jbe@619 478 CREATE FUNCTION "to_tsvector"("member_profile") RETURNS TSVECTOR
jbe@619 479 LANGUAGE SQL IMMUTABLE AS $$ SELECT to_tsvector(concat_ws(' ',
jbe@619 480 $1."statement",
jbe@619 481 $1."profile_text_data"
jbe@619 482 )) $$;
jbe@619 483 CREATE INDEX "member_profile_to_tsvector_idx" ON "member_profile" USING gin
jbe@619 484 (("to_tsvector"("member_profile".*)));
jbe@619 485
jbe@619 486 CREATE FUNCTION "to_tsvector"("unit") RETURNS TSVECTOR
jbe@619 487 LANGUAGE SQL IMMUTABLE AS $$ SELECT to_tsvector(concat_ws(' ',
jbe@619 488 $1."name",
jbe@619 489 $1."description"
jbe@619 490 )) $$;
jbe@619 491 CREATE INDEX "unit_to_tsvector_idx" ON "unit" USING gin
jbe@619 492 (("to_tsvector"("unit".*)));
jbe@619 493
jbe@619 494 CREATE FUNCTION "to_tsvector"("area") RETURNS TSVECTOR
jbe@619 495 LANGUAGE SQL IMMUTABLE AS $$ SELECT to_tsvector(concat_ws(' ',
jbe@619 496 $1."name",
jbe@619 497 $1."description"
jbe@619 498 )) $$;
jbe@619 499 CREATE INDEX "area_to_tsvector_idx" ON "area" USING gin
jbe@619 500 (("to_tsvector"("area".*)));
jbe@619 501
jbe@619 502 CREATE FUNCTION "to_tsvector"("initiative") RETURNS TSVECTOR
jbe@619 503 LANGUAGE SQL IMMUTABLE AS $$ SELECT to_tsvector(concat_ws(' ',
jbe@619 504 $1."name",
jbe@619 505 $1."content"
jbe@619 506 )) $$;
jbe@619 507 CREATE INDEX "initiative_to_tsvector_idx" ON "initiative" USING gin
jbe@619 508 (("to_tsvector"("initiative".*)));
jbe@619 509
jbe@619 510 CREATE FUNCTION "to_tsvector"("draft") RETURNS TSVECTOR
jbe@619 511 LANGUAGE SQL IMMUTABLE AS $$ SELECT to_tsvector(concat_ws(' ',
jbe@619 512 $1."content"
jbe@619 513 )) $$;
jbe@619 514 CREATE INDEX "draft_to_tsvector_idx" ON "draft" USING gin
jbe@619 515 (("to_tsvector"("draft".*)));
jbe@619 516
jbe@619 517 CREATE FUNCTION "to_tsvector"("suggestion") RETURNS TSVECTOR
jbe@619 518 LANGUAGE SQL IMMUTABLE AS $$ SELECT to_tsvector(concat_ws(' ',
jbe@619 519 $1."name",
jbe@619 520 $1."content"
jbe@619 521 )) $$;
jbe@619 522 CREATE INDEX "suggestion_to_tsvector_idx" ON "suggestion" USING gin
jbe@619 523 (("to_tsvector"("suggestion".*)));
jbe@619 524
jbe@619 525 CREATE FUNCTION "to_tsvector"("direct_voter") RETURNS TSVECTOR
jbe@619 526 LANGUAGE SQL IMMUTABLE AS $$ SELECT to_tsvector(concat_ws(' ',
jbe@619 527 $1."comment"
jbe@619 528 )) $$;
jbe@619 529 CREATE INDEX "direct_voter_to_tsvector_idx" ON "direct_voter" USING gin
jbe@619 530 (("to_tsvector"("direct_voter".*)));
jbe@619 531
jbe@619 532 CREATE FUNCTION "update_posting_lexeme_trigger"()
jbe@619 533 RETURNS TRIGGER
jbe@619 534 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@619 535 DECLARE
jbe@619 536 "lexeme_v" TEXT;
jbe@619 537 BEGIN
jbe@619 538 IF TG_OP = 'DELETE' OR TG_OP = 'UPDATE' THEN
jbe@619 539 DELETE FROM "posting_lexeme" WHERE "posting_id" = OLD."id";
jbe@619 540 END IF;
jbe@619 541 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
jbe@619 542 FOR "lexeme_v" IN
jbe@619 543 SELECT regexp_matches[1]
jbe@619 544 FROM regexp_matches(NEW."message", '#[^\s.,;:]+')
jbe@619 545 LOOP
jbe@619 546 INSERT INTO "posting_lexeme" ("posting_id", "author_id", "lexeme")
jbe@619 547 VALUES (
jbe@619 548 NEW."id",
jbe@619 549 NEW."author_id",
jbe@619 550 "lexeme_v" )
jbe@619 551 ON CONFLICT ("posting_id", "lexeme") DO NOTHING;
jbe@619 552 END LOOP;
jbe@619 553 END IF;
jbe@619 554 RETURN NULL;
jbe@619 555 END;
jbe@619 556 $$;
jbe@619 557
jbe@619 558 CREATE TRIGGER "update_posting_lexeme"
jbe@619 559 AFTER INSERT OR UPDATE OR DELETE ON "posting"
jbe@619 560 FOR EACH ROW EXECUTE PROCEDURE "update_posting_lexeme_trigger"();
jbe@619 561
jbe@619 562 COMMENT ON FUNCTION "update_posting_lexeme_trigger"() IS 'Implementation of trigger "update_posting_lexeme" on table "posting"';
jbe@619 563 COMMENT ON TRIGGER "update_posting_lexeme" ON "posting" IS 'Keeps table "posting_lexeme" up to date';
jbe@619 564
jbe@619 565 CREATE FUNCTION "write_event_posting_trigger"()
jbe@619 566 RETURNS TRIGGER
jbe@619 567 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@619 568 BEGIN
jbe@619 569 INSERT INTO "event" (
jbe@619 570 "event", "posting_id", "member_id",
jbe@619 571 "unit_id", "area_id", "policy_id",
jbe@619 572 "issue_id", "initiative_id", "suggestion_id"
jbe@619 573 ) VALUES (
jbe@619 574 'posting_created', NEW."id", NEW."author_id",
jbe@619 575 NEW."unit_id", NEW."area_id", NEW."policy_id",
jbe@619 576 NEW."issue_id", NEW."initiative_id", NEW."suggestion_id"
jbe@619 577 );
jbe@619 578 RETURN NULL;
jbe@619 579 END;
jbe@619 580 $$;
jbe@619 581
jbe@619 582 CREATE TRIGGER "write_event_posting"
jbe@619 583 AFTER INSERT ON "posting" FOR EACH ROW EXECUTE PROCEDURE
jbe@619 584 "write_event_posting_trigger"();
jbe@619 585
jbe@619 586 COMMENT ON FUNCTION "write_event_posting_trigger"() IS 'Implementation of trigger "write_event_posting" on table "posting"';
jbe@619 587 COMMENT ON TRIGGER "write_event_posting" ON "posting" IS 'Create entry in "event" table when creating a new posting';
jbe@619 588
jbe@619 589 CREATE FUNCTION "file_requires_reference_trigger"()
jbe@619 590 RETURNS TRIGGER
jbe@619 591 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@619 592 BEGIN
jbe@619 593 IF NOT EXISTS (
jbe@619 594 SELECT NULL FROM "draft_attachment" WHERE "file_id" = NEW."id"
jbe@619 595 ) THEN
jbe@619 596 RAISE EXCEPTION 'Cannot create an unreferenced file.' USING
jbe@619 597 ERRCODE = 'integrity_constraint_violation',
jbe@619 598 HINT = 'Create file and its reference in another table within the same transaction.';
jbe@619 599 END IF;
jbe@619 600 RETURN NULL;
jbe@619 601 END;
jbe@619 602 $$;
jbe@619 603
jbe@619 604 CREATE CONSTRAINT TRIGGER "file_requires_reference"
jbe@619 605 AFTER INSERT OR UPDATE ON "file" DEFERRABLE INITIALLY DEFERRED
jbe@619 606 FOR EACH ROW EXECUTE PROCEDURE
jbe@619 607 "file_requires_reference_trigger"();
jbe@619 608
jbe@619 609 COMMENT ON FUNCTION "file_requires_reference_trigger"() IS 'Implementation of trigger "file_requires_reference" on table "file"';
jbe@619 610 COMMENT ON TRIGGER "file_requires_reference" ON "file" IS 'Ensure that files are always referenced';
jbe@619 611
jbe@619 612 CREATE FUNCTION "last_reference_deletes_file_trigger"()
jbe@619 613 RETURNS TRIGGER
jbe@619 614 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@619 615 DECLARE
jbe@619 616 "reference_lost" BOOLEAN;
jbe@619 617 BEGIN
jbe@619 618 IF TG_OP = 'DELETE' THEN
jbe@619 619 "reference_lost" := TRUE;
jbe@619 620 ELSE
jbe@619 621 "reference_lost" := NEW."file_id" != OLD."file_id";
jbe@619 622 END IF;
jbe@619 623 IF
jbe@619 624 "reference_lost" AND NOT EXISTS (
jbe@619 625 SELECT NULL FROM "draft_attachment" WHERE "file_id" = OLD."file_id"
jbe@619 626 )
jbe@619 627 THEN
jbe@619 628 DELETE FROM "file" WHERE "id" = OLD."file_id";
jbe@619 629 END IF;
jbe@619 630 RETURN NULL;
jbe@619 631 END;
jbe@619 632 $$;
jbe@619 633
jbe@619 634 CREATE CONSTRAINT TRIGGER "last_reference_deletes_file"
jbe@619 635 AFTER UPDATE OR DELETE ON "draft_attachment" DEFERRABLE INITIALLY DEFERRED
jbe@619 636 FOR EACH ROW EXECUTE PROCEDURE
jbe@619 637 "last_reference_deletes_file_trigger"();
jbe@619 638
jbe@619 639 COMMENT ON FUNCTION "last_reference_deletes_file_trigger"() IS 'Implementation of trigger "last_reference_deletes_file" on table "draft_attachment"';
jbe@619 640 COMMENT ON TRIGGER "last_reference_deletes_file" ON "draft_attachment" IS 'Removing the last reference to a file deletes the file';
jbe@619 641
jbe@619 642 CREATE OR REPLACE FUNCTION "copy_current_draft_data"
jbe@619 643 ("initiative_id_p" "initiative"."id"%TYPE )
jbe@619 644 RETURNS VOID
jbe@619 645 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@619 646 BEGIN
jbe@619 647 PERFORM NULL FROM "initiative" WHERE "id" = "initiative_id_p"
jbe@619 648 FOR UPDATE;
jbe@619 649 UPDATE "initiative" SET
jbe@619 650 "location" = "draft"."location",
jbe@619 651 "content" = "draft"."content"
jbe@619 652 FROM "current_draft" AS "draft"
jbe@619 653 WHERE "initiative"."id" = "initiative_id_p"
jbe@619 654 AND "draft"."initiative_id" = "initiative_id_p";
jbe@619 655 END;
jbe@619 656 $$;
jbe@619 657
jbe@619 658 CREATE VIEW "follower" AS
jbe@619 659 SELECT
jbe@619 660 "id" AS "follower_id",
jbe@619 661 ( SELECT ARRAY["member"."id"] || array_agg("contact"."other_member_id")
jbe@619 662 FROM "contact"
jbe@619 663 WHERE "contact"."member_id" = "member"."id" AND "contact"."following" )
jbe@619 664 AS "following_ids"
jbe@619 665 FROM "member";
jbe@619 666
jbe@619 667 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@619 668
jbe@619 669 CREATE OR REPLACE FUNCTION "check_issue"
jbe@619 670 ( "issue_id_p" "issue"."id"%TYPE,
jbe@619 671 "persist" "check_issue_persistence" )
jbe@619 672 RETURNS "check_issue_persistence"
jbe@619 673 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@619 674 DECLARE
jbe@619 675 "issue_row" "issue"%ROWTYPE;
jbe@619 676 "last_calculated_v" "snapshot"."calculated"%TYPE;
jbe@619 677 "policy_row" "policy"%ROWTYPE;
jbe@619 678 "initiative_row" "initiative"%ROWTYPE;
jbe@619 679 "state_v" "issue_state";
jbe@619 680 BEGIN
jbe@619 681 PERFORM "require_transaction_isolation"();
jbe@619 682 IF "persist" ISNULL THEN
jbe@619 683 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
jbe@619 684 FOR UPDATE;
jbe@619 685 SELECT "calculated" INTO "last_calculated_v"
jbe@619 686 FROM "snapshot" JOIN "snapshot_issue"
jbe@619 687 ON "snapshot"."id" = "snapshot_issue"."snapshot_id"
jbe@619 688 WHERE "snapshot_issue"."issue_id" = "issue_id_p"
jbe@619 689 ORDER BY "snapshot"."id" DESC;
jbe@619 690 IF "issue_row"."closed" NOTNULL THEN
jbe@619 691 RETURN NULL;
jbe@619 692 END IF;
jbe@619 693 "persist"."state" := "issue_row"."state";
jbe@619 694 IF
jbe@619 695 ( "issue_row"."state" = 'admission' AND "last_calculated_v" >=
jbe@619 696 "issue_row"."created" + "issue_row"."max_admission_time" ) OR
jbe@619 697 ( "issue_row"."state" = 'discussion' AND now() >=
jbe@619 698 "issue_row"."accepted" + "issue_row"."discussion_time" ) OR
jbe@619 699 ( "issue_row"."state" = 'verification' AND now() >=
jbe@619 700 "issue_row"."half_frozen" + "issue_row"."verification_time" ) OR
jbe@619 701 ( "issue_row"."state" = 'voting' AND now() >=
jbe@619 702 "issue_row"."fully_frozen" + "issue_row"."voting_time" )
jbe@619 703 THEN
jbe@619 704 "persist"."phase_finished" := TRUE;
jbe@619 705 ELSE
jbe@619 706 "persist"."phase_finished" := FALSE;
jbe@619 707 END IF;
jbe@619 708 IF
jbe@619 709 NOT EXISTS (
jbe@619 710 -- all initiatives are revoked
jbe@619 711 SELECT NULL FROM "initiative"
jbe@619 712 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
jbe@619 713 ) AND (
jbe@619 714 -- and issue has not been accepted yet
jbe@619 715 "persist"."state" = 'admission' OR
jbe@619 716 -- or verification time has elapsed
jbe@619 717 ( "persist"."state" = 'verification' AND
jbe@619 718 "persist"."phase_finished" ) OR
jbe@619 719 -- or no initiatives have been revoked lately
jbe@619 720 NOT EXISTS (
jbe@619 721 SELECT NULL FROM "initiative"
jbe@619 722 WHERE "issue_id" = "issue_id_p"
jbe@619 723 AND now() < "revoked" + "issue_row"."verification_time"
jbe@619 724 )
jbe@619 725 )
jbe@619 726 THEN
jbe@619 727 "persist"."issue_revoked" := TRUE;
jbe@619 728 ELSE
jbe@619 729 "persist"."issue_revoked" := FALSE;
jbe@619 730 END IF;
jbe@619 731 IF "persist"."phase_finished" OR "persist"."issue_revoked" THEN
jbe@619 732 UPDATE "issue" SET "phase_finished" = now()
jbe@619 733 WHERE "id" = "issue_row"."id";
jbe@619 734 RETURN "persist";
jbe@619 735 ELSIF
jbe@619 736 "persist"."state" IN ('admission', 'discussion', 'verification')
jbe@619 737 THEN
jbe@619 738 RETURN "persist";
jbe@619 739 ELSE
jbe@619 740 RETURN NULL;
jbe@619 741 END IF;
jbe@619 742 END IF;
jbe@619 743 IF
jbe@619 744 "persist"."state" IN ('admission', 'discussion', 'verification') AND
jbe@619 745 coalesce("persist"."snapshot_created", FALSE) = FALSE
jbe@619 746 THEN
jbe@619 747 IF "persist"."state" != 'admission' THEN
jbe@619 748 PERFORM "take_snapshot"("issue_id_p");
jbe@619 749 PERFORM "finish_snapshot"("issue_id_p");
jbe@619 750 ELSE
jbe@619 751 UPDATE "issue" SET "issue_quorum" = "issue_quorum"."issue_quorum"
jbe@619 752 FROM "issue_quorum"
jbe@619 753 WHERE "id" = "issue_id_p"
jbe@619 754 AND "issue_quorum"."issue_id" = "issue_id_p";
jbe@619 755 END IF;
jbe@619 756 "persist"."snapshot_created" = TRUE;
jbe@619 757 IF "persist"."phase_finished" THEN
jbe@619 758 IF "persist"."state" = 'admission' THEN
jbe@619 759 UPDATE "issue" SET "admission_snapshot_id" = "latest_snapshot_id"
jbe@619 760 WHERE "id" = "issue_id_p";
jbe@619 761 ELSIF "persist"."state" = 'discussion' THEN
jbe@619 762 UPDATE "issue" SET "half_freeze_snapshot_id" = "latest_snapshot_id"
jbe@619 763 WHERE "id" = "issue_id_p";
jbe@619 764 ELSIF "persist"."state" = 'verification' THEN
jbe@619 765 UPDATE "issue" SET "full_freeze_snapshot_id" = "latest_snapshot_id"
jbe@619 766 WHERE "id" = "issue_id_p";
jbe@619 767 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
jbe@619 768 FOR "initiative_row" IN
jbe@619 769 SELECT * FROM "initiative"
jbe@619 770 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
jbe@619 771 FOR UPDATE
jbe@619 772 LOOP
jbe@619 773 IF
jbe@619 774 "initiative_row"."polling" OR
jbe@619 775 "initiative_row"."satisfied_supporter_count" >=
jbe@619 776 "issue_row"."initiative_quorum"
jbe@619 777 THEN
jbe@619 778 UPDATE "initiative" SET "admitted" = TRUE
jbe@619 779 WHERE "id" = "initiative_row"."id";
jbe@619 780 ELSE
jbe@619 781 UPDATE "initiative" SET "admitted" = FALSE
jbe@619 782 WHERE "id" = "initiative_row"."id";
jbe@619 783 END IF;
jbe@619 784 END LOOP;
jbe@619 785 END IF;
jbe@619 786 END IF;
jbe@619 787 RETURN "persist";
jbe@619 788 END IF;
jbe@619 789 IF
jbe@619 790 "persist"."state" IN ('admission', 'discussion', 'verification') AND
jbe@619 791 coalesce("persist"."harmonic_weights_set", FALSE) = FALSE
jbe@619 792 THEN
jbe@619 793 PERFORM "set_harmonic_initiative_weights"("issue_id_p");
jbe@619 794 "persist"."harmonic_weights_set" = TRUE;
jbe@619 795 IF
jbe@619 796 "persist"."phase_finished" OR
jbe@619 797 "persist"."issue_revoked" OR
jbe@619 798 "persist"."state" = 'admission'
jbe@619 799 THEN
jbe@619 800 RETURN "persist";
jbe@619 801 ELSE
jbe@619 802 RETURN NULL;
jbe@619 803 END IF;
jbe@619 804 END IF;
jbe@619 805 IF "persist"."issue_revoked" THEN
jbe@619 806 IF "persist"."state" = 'admission' THEN
jbe@619 807 "state_v" := 'canceled_revoked_before_accepted';
jbe@619 808 ELSIF "persist"."state" = 'discussion' THEN
jbe@619 809 "state_v" := 'canceled_after_revocation_during_discussion';
jbe@619 810 ELSIF "persist"."state" = 'verification' THEN
jbe@619 811 "state_v" := 'canceled_after_revocation_during_verification';
jbe@619 812 END IF;
jbe@619 813 UPDATE "issue" SET
jbe@619 814 "state" = "state_v",
jbe@619 815 "closed" = "phase_finished",
jbe@619 816 "phase_finished" = NULL
jbe@619 817 WHERE "id" = "issue_id_p";
jbe@619 818 RETURN NULL;
jbe@619 819 END IF;
jbe@619 820 IF "persist"."state" = 'admission' THEN
jbe@619 821 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
jbe@619 822 FOR UPDATE;
jbe@619 823 IF "issue_row"."phase_finished" NOTNULL THEN
jbe@619 824 UPDATE "issue" SET
jbe@619 825 "state" = 'canceled_issue_not_accepted',
jbe@619 826 "closed" = "phase_finished",
jbe@619 827 "phase_finished" = NULL
jbe@619 828 WHERE "id" = "issue_id_p";
jbe@619 829 END IF;
jbe@619 830 RETURN NULL;
jbe@619 831 END IF;
jbe@619 832 IF "persist"."phase_finished" THEN
jbe@619 833 IF "persist"."state" = 'discussion' THEN
jbe@619 834 UPDATE "issue" SET
jbe@619 835 "state" = 'verification',
jbe@619 836 "half_frozen" = "phase_finished",
jbe@619 837 "phase_finished" = NULL
jbe@619 838 WHERE "id" = "issue_id_p";
jbe@619 839 RETURN NULL;
jbe@619 840 END IF;
jbe@619 841 IF "persist"."state" = 'verification' THEN
jbe@619 842 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
jbe@619 843 FOR UPDATE;
jbe@619 844 SELECT * INTO "policy_row" FROM "policy"
jbe@619 845 WHERE "id" = "issue_row"."policy_id";
jbe@619 846 IF EXISTS (
jbe@619 847 SELECT NULL FROM "initiative"
jbe@619 848 WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE
jbe@619 849 ) THEN
jbe@619 850 UPDATE "issue" SET
jbe@619 851 "state" = 'voting',
jbe@619 852 "fully_frozen" = "phase_finished",
jbe@619 853 "phase_finished" = NULL
jbe@619 854 WHERE "id" = "issue_id_p";
jbe@619 855 ELSE
jbe@619 856 UPDATE "issue" SET
jbe@619 857 "state" = 'canceled_no_initiative_admitted',
jbe@619 858 "fully_frozen" = "phase_finished",
jbe@619 859 "closed" = "phase_finished",
jbe@619 860 "phase_finished" = NULL
jbe@619 861 WHERE "id" = "issue_id_p";
jbe@619 862 -- NOTE: The following DELETE statements have effect only when
jbe@619 863 -- issue state has been manipulated
jbe@619 864 DELETE FROM "direct_voter" WHERE "issue_id" = "issue_id_p";
jbe@619 865 DELETE FROM "delegating_voter" WHERE "issue_id" = "issue_id_p";
jbe@619 866 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
jbe@619 867 END IF;
jbe@619 868 RETURN NULL;
jbe@619 869 END IF;
jbe@619 870 IF "persist"."state" = 'voting' THEN
jbe@619 871 IF coalesce("persist"."closed_voting", FALSE) = FALSE THEN
jbe@619 872 PERFORM "close_voting"("issue_id_p");
jbe@619 873 "persist"."closed_voting" = TRUE;
jbe@619 874 RETURN "persist";
jbe@619 875 END IF;
jbe@619 876 PERFORM "calculate_ranks"("issue_id_p");
jbe@619 877 RETURN NULL;
jbe@619 878 END IF;
jbe@619 879 END IF;
jbe@619 880 RAISE WARNING 'should not happen';
jbe@619 881 RETURN NULL;
jbe@619 882 END;
jbe@619 883 $$;
jbe@619 884
jbe@619 885 CREATE OR REPLACE FUNCTION "check_everything"()
jbe@619 886 RETURNS VOID
jbe@619 887 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@619 888 DECLARE
jbe@619 889 "area_id_v" "area"."id"%TYPE;
jbe@619 890 "snapshot_id_v" "snapshot"."id"%TYPE;
jbe@619 891 "issue_id_v" "issue"."id"%TYPE;
jbe@619 892 "persist_v" "check_issue_persistence";
jbe@619 893 BEGIN
jbe@619 894 RAISE WARNING 'Function "check_everything" should only be used for development and debugging purposes';
jbe@619 895 DELETE FROM "expired_session";
jbe@619 896 DELETE FROM "expired_token";
jbe@619 897 DELETE FROM "unused_snapshot";
jbe@619 898 PERFORM "check_activity"();
jbe@619 899 PERFORM "calculate_member_counts"();
jbe@619 900 FOR "area_id_v" IN SELECT "id" FROM "area_with_unaccepted_issues" LOOP
jbe@619 901 SELECT "take_snapshot"(NULL, "area_id_v") INTO "snapshot_id_v";
jbe@619 902 PERFORM "finish_snapshot"("issue_id") FROM "snapshot_issue"
jbe@619 903 WHERE "snapshot_id" = "snapshot_id_v";
jbe@619 904 LOOP
jbe@619 905 EXIT WHEN "issue_admission"("area_id_v") = FALSE;
jbe@619 906 END LOOP;
jbe@619 907 END LOOP;
jbe@619 908 FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP
jbe@619 909 "persist_v" := NULL;
jbe@619 910 LOOP
jbe@619 911 "persist_v" := "check_issue"("issue_id_v", "persist_v");
jbe@619 912 EXIT WHEN "persist_v" ISNULL;
jbe@619 913 END LOOP;
jbe@619 914 END LOOP;
jbe@619 915 DELETE FROM "unused_snapshot";
jbe@619 916 RETURN;
jbe@619 917 END;
jbe@619 918 $$;
jbe@619 919
jbe@619 920 CREATE OR REPLACE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)
jbe@619 921 RETURNS VOID
jbe@619 922 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@619 923 BEGIN
jbe@619 924 UPDATE "member" SET
jbe@619 925 "last_login" = NULL,
jbe@619 926 "last_delegation_check" = NULL,
jbe@619 927 "login" = NULL,
jbe@619 928 "password" = NULL,
jbe@619 929 "authority" = NULL,
jbe@619 930 "authority_uid" = NULL,
jbe@619 931 "authority_login" = NULL,
jbe@619 932 "deleted" = coalesce("deleted", now()),
jbe@619 933 "locked" = TRUE,
jbe@619 934 "active" = FALSE,
jbe@619 935 "notify_email" = NULL,
jbe@619 936 "notify_email_unconfirmed" = NULL,
jbe@619 937 "notify_email_secret" = NULL,
jbe@619 938 "notify_email_secret_expiry" = NULL,
jbe@619 939 "notify_email_lock_expiry" = NULL,
jbe@619 940 "disable_notifications" = TRUE,
jbe@619 941 "notification_counter" = DEFAULT,
jbe@619 942 "notification_sample_size" = 0,
jbe@619 943 "notification_dow" = NULL,
jbe@619 944 "notification_hour" = NULL,
jbe@619 945 "notification_sent" = NULL,
jbe@619 946 "login_recovery_expiry" = NULL,
jbe@619 947 "password_reset_secret" = NULL,
jbe@619 948 "password_reset_secret_expiry" = NULL,
jbe@619 949 "location" = NULL
jbe@619 950 WHERE "id" = "member_id_p";
jbe@619 951 DELETE FROM "member_settings" WHERE "member_id" = "member_id_p";
jbe@619 952 DELETE FROM "member_profile" WHERE "member_id" = "member_id_p";
jbe@619 953 DELETE FROM "rendered_member_statement" WHERE "member_id" = "member_id_p";
jbe@619 954 DELETE FROM "member_image" WHERE "member_id" = "member_id_p";
jbe@619 955 DELETE FROM "contact" WHERE "member_id" = "member_id_p";
jbe@619 956 DELETE FROM "ignored_member" WHERE "member_id" = "member_id_p";
jbe@619 957 DELETE FROM "session" WHERE "member_id" = "member_id_p";
jbe@619 958 DELETE FROM "member_application" WHERE "member_id" = "member_id_p";
jbe@619 959 DELETE FROM "token" WHERE "member_id" = "member_id_p";
jbe@619 960 DELETE FROM "subscription" WHERE "member_id" = "member_id_p";
jbe@619 961 DELETE FROM "ignored_area" WHERE "member_id" = "member_id_p";
jbe@619 962 DELETE FROM "ignored_initiative" WHERE "member_id" = "member_id_p";
jbe@619 963 DELETE FROM "delegation" WHERE "truster_id" = "member_id_p";
jbe@619 964 DELETE FROM "non_voter" WHERE "member_id" = "member_id_p";
jbe@619 965 DELETE FROM "direct_voter" USING "issue"
jbe@619 966 WHERE "direct_voter"."issue_id" = "issue"."id"
jbe@619 967 AND "issue"."closed" ISNULL
jbe@619 968 AND "member_id" = "member_id_p";
jbe@619 969 DELETE FROM "notification_initiative_sent" WHERE "member_id" = "member_id_p";
jbe@619 970 RETURN;
jbe@619 971 END;
jbe@619 972 $$;
jbe@619 973
jbe@619 974 CREATE OR REPLACE FUNCTION "delete_private_data"()
jbe@619 975 RETURNS VOID
jbe@619 976 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@619 977 BEGIN
jbe@619 978 DELETE FROM "temporary_transaction_data";
jbe@619 979 DELETE FROM "temporary_suggestion_counts";
jbe@619 980 DELETE FROM "member" WHERE "activated" ISNULL;
jbe@619 981 UPDATE "member" SET
jbe@619 982 "invite_code" = NULL,
jbe@619 983 "invite_code_expiry" = NULL,
jbe@619 984 "admin_comment" = NULL,
jbe@619 985 "last_login" = NULL,
jbe@619 986 "last_delegation_check" = NULL,
jbe@619 987 "login" = NULL,
jbe@619 988 "password" = NULL,
jbe@619 989 "authority" = NULL,
jbe@619 990 "authority_uid" = NULL,
jbe@619 991 "authority_login" = NULL,
jbe@619 992 "lang" = NULL,
jbe@619 993 "notify_email" = NULL,
jbe@619 994 "notify_email_unconfirmed" = NULL,
jbe@619 995 "notify_email_secret" = NULL,
jbe@619 996 "notify_email_secret_expiry" = NULL,
jbe@619 997 "notify_email_lock_expiry" = NULL,
jbe@619 998 "disable_notifications" = TRUE,
jbe@619 999 "notification_counter" = DEFAULT,
jbe@619 1000 "notification_sample_size" = 0,
jbe@619 1001 "notification_dow" = NULL,
jbe@619 1002 "notification_hour" = NULL,
jbe@619 1003 "notification_sent" = NULL,
jbe@619 1004 "login_recovery_expiry" = NULL,
jbe@619 1005 "password_reset_secret" = NULL,
jbe@619 1006 "password_reset_secret_expiry" = NULL,
jbe@619 1007 "location" = NULL;
jbe@619 1008 DELETE FROM "verification";
jbe@619 1009 DELETE FROM "member_settings";
jbe@619 1010 DELETE FROM "member_useterms";
jbe@619 1011 DELETE FROM "member_profile";
jbe@619 1012 DELETE FROM "rendered_member_statement";
jbe@619 1013 DELETE FROM "member_image";
jbe@619 1014 DELETE FROM "contact";
jbe@619 1015 DELETE FROM "ignored_member";
jbe@619 1016 DELETE FROM "session";
jbe@619 1017 DELETE FROM "system_application";
jbe@619 1018 DELETE FROM "system_application_redirect_uri";
jbe@619 1019 DELETE FROM "dynamic_application_scope";
jbe@619 1020 DELETE FROM "member_application";
jbe@619 1021 DELETE FROM "token";
jbe@619 1022 DELETE FROM "subscription";
jbe@619 1023 DELETE FROM "ignored_area";
jbe@619 1024 DELETE FROM "ignored_initiative";
jbe@619 1025 DELETE FROM "non_voter";
jbe@619 1026 DELETE FROM "direct_voter" USING "issue"
jbe@619 1027 WHERE "direct_voter"."issue_id" = "issue"."id"
jbe@619 1028 AND "issue"."closed" ISNULL;
jbe@619 1029 DELETE FROM "event_processed";
jbe@619 1030 DELETE FROM "notification_initiative_sent";
jbe@619 1031 DELETE FROM "newsletter";
jbe@619 1032 RETURN;
jbe@619 1033 END;
jbe@619 1034 $$;
jbe@619 1035
jbe@619 1036 CREATE VIEW "member_eligible_to_be_notified" AS
jbe@619 1037 SELECT * FROM "member"
jbe@619 1038 WHERE "activated" NOTNULL AND "locked" = FALSE;
jbe@619 1039
jbe@619 1040 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@619 1041
jbe@619 1042 CREATE VIEW "member_to_notify" AS
jbe@619 1043 SELECT * FROM "member_eligible_to_be_notified"
jbe@619 1044 WHERE "disable_notifications" = FALSE;
jbe@619 1045
jbe@619 1046 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@619 1047
jbe@619 1048 CREATE VIEW "area_with_unaccepted_issues" AS
jbe@619 1049 SELECT DISTINCT ON ("area"."id") "area".*
jbe@619 1050 FROM "area" JOIN "issue" ON "area"."id" = "issue"."area_id"
jbe@619 1051 WHERE "issue"."state" = 'admission';
jbe@619 1052
jbe@619 1053 COMMENT ON VIEW "area_with_unaccepted_issues" IS 'All areas with unaccepted open issues (needed for issue admission system)';
jbe@619 1054
jbe@619 1055 CREATE VIEW "opening_draft" AS
jbe@619 1056 SELECT DISTINCT ON ("initiative_id") * FROM "draft"
jbe@619 1057 ORDER BY "initiative_id", "id";
jbe@619 1058
jbe@619 1059 COMMENT ON VIEW "opening_draft" IS 'First drafts of all initiatives';
jbe@619 1060
jbe@619 1061 CREATE VIEW "current_draft" AS
jbe@619 1062 SELECT DISTINCT ON ("initiative_id") * FROM "draft"
jbe@619 1063 ORDER BY "initiative_id", "id" DESC;
jbe@619 1064
jbe@619 1065 COMMENT ON VIEW "current_draft" IS 'All latest drafts for each initiative';
jbe@619 1066
jbe@619 1067 CREATE VIEW "member_contingent" AS
jbe@619 1068 SELECT
jbe@619 1069 "member"."id" AS "member_id",
jbe@619 1070 "contingent"."polling",
jbe@619 1071 "contingent"."time_frame",
jbe@619 1072 CASE WHEN "contingent"."text_entry_limit" NOTNULL THEN
jbe@619 1073 (
jbe@619 1074 SELECT count(1) FROM "draft"
jbe@619 1075 JOIN "initiative" ON "initiative"."id" = "draft"."initiative_id"
jbe@619 1076 WHERE "draft"."author_id" = "member"."id"
jbe@619 1077 AND "initiative"."polling" = "contingent"."polling"
jbe@619 1078 AND "draft"."created" > now() - "contingent"."time_frame"
jbe@619 1079 ) + (
jbe@619 1080 SELECT count(1) FROM "suggestion"
jbe@619 1081 JOIN "initiative" ON "initiative"."id" = "suggestion"."initiative_id"
jbe@619 1082 WHERE "suggestion"."author_id" = "member"."id"
jbe@619 1083 AND "contingent"."polling" = FALSE
jbe@619 1084 AND "suggestion"."created" > now() - "contingent"."time_frame"
jbe@619 1085 )
jbe@619 1086 ELSE NULL END AS "text_entry_count",
jbe@619 1087 "contingent"."text_entry_limit",
jbe@619 1088 CASE WHEN "contingent"."initiative_limit" NOTNULL THEN (
jbe@619 1089 SELECT count(1) FROM "opening_draft" AS "draft"
jbe@619 1090 JOIN "initiative" ON "initiative"."id" = "draft"."initiative_id"
jbe@619 1091 WHERE "draft"."author_id" = "member"."id"
jbe@619 1092 AND "initiative"."polling" = "contingent"."polling"
jbe@619 1093 AND "draft"."created" > now() - "contingent"."time_frame"
jbe@619 1094 ) ELSE NULL END AS "initiative_count",
jbe@619 1095 "contingent"."initiative_limit"
jbe@619 1096 FROM "member" CROSS JOIN "contingent";
jbe@619 1097
jbe@619 1098 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@619 1099
jbe@619 1100 COMMENT ON COLUMN "member_contingent"."text_entry_count" IS 'Only calculated when "text_entry_limit" is not null in the same row';
jbe@619 1101 COMMENT ON COLUMN "member_contingent"."initiative_count" IS 'Only calculated when "initiative_limit" is not null in the same row';
jbe@619 1102
jbe@619 1103 CREATE VIEW "member_contingent_left" AS
jbe@619 1104 SELECT
jbe@619 1105 "member_id",
jbe@619 1106 "polling",
jbe@619 1107 max("text_entry_limit" - "text_entry_count") AS "text_entries_left",
jbe@619 1108 max("initiative_limit" - "initiative_count") AS "initiatives_left"
jbe@619 1109 FROM "member_contingent" GROUP BY "member_id", "polling";
jbe@619 1110
jbe@619 1111 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@619 1112
jbe@619 1113 CREATE VIEW "scheduled_notification_to_send" AS
jbe@619 1114 SELECT * FROM (
jbe@619 1115 SELECT
jbe@619 1116 "id" AS "recipient_id",
jbe@619 1117 now() - CASE WHEN "notification_dow" ISNULL THEN
jbe@619 1118 ( "notification_sent"::DATE + CASE
jbe@619 1119 WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour"
jbe@619 1120 THEN 0 ELSE 1 END
jbe@619 1121 )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour"
jbe@619 1122 ELSE
jbe@619 1123 ( "notification_sent"::DATE +
jbe@619 1124 ( 7 + "notification_dow" -
jbe@619 1125 EXTRACT(DOW FROM
jbe@619 1126 ( "notification_sent"::DATE + CASE
jbe@619 1127 WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour"
jbe@619 1128 THEN 0 ELSE 1 END
jbe@619 1129 )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour"
jbe@619 1130 )::INTEGER
jbe@619 1131 ) % 7 +
jbe@619 1132 CASE
jbe@619 1133 WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour"
jbe@619 1134 THEN 0 ELSE 1
jbe@619 1135 END
jbe@619 1136 )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour"
jbe@619 1137 END AS "pending"
jbe@619 1138 FROM (
jbe@619 1139 SELECT
jbe@619 1140 "id",
jbe@619 1141 COALESCE("notification_sent", "activated") AS "notification_sent",
jbe@619 1142 "notification_dow",
jbe@619 1143 "notification_hour"
jbe@619 1144 FROM "member_to_notify"
jbe@619 1145 WHERE "notification_hour" NOTNULL
jbe@619 1146 ) AS "subquery1"
jbe@619 1147 ) AS "subquery2"
jbe@619 1148 WHERE "pending" > '0'::INTERVAL;
jbe@619 1149
jbe@619 1150 COMMENT ON VIEW "scheduled_notification_to_send" IS 'Set of members where a scheduled notification mail is pending';
jbe@619 1151
jbe@619 1152 COMMENT ON COLUMN "scheduled_notification_to_send"."recipient_id" IS '"id" of the member who needs to receive a notification mail';
jbe@619 1153 COMMENT ON COLUMN "scheduled_notification_to_send"."pending" IS 'Duration for which the notification mail has already been pending';
jbe@619 1154
jbe@619 1155 CREATE VIEW "newsletter_to_send" AS
jbe@619 1156 SELECT
jbe@619 1157 "member"."id" AS "recipient_id",
jbe@619 1158 "newsletter"."id" AS "newsletter_id",
jbe@619 1159 "newsletter"."published"
jbe@619 1160 FROM "newsletter" CROSS JOIN "member_eligible_to_be_notified" AS "member"
jbe@619 1161 LEFT JOIN "privilege" ON
jbe@619 1162 "privilege"."member_id" = "member"."id" AND
jbe@619 1163 "privilege"."unit_id" = "newsletter"."unit_id" AND
jbe@619 1164 "privilege"."voting_right" = TRUE
jbe@619 1165 LEFT JOIN "subscription" ON
jbe@619 1166 "subscription"."member_id" = "member"."id" AND
jbe@619 1167 "subscription"."unit_id" = "newsletter"."unit_id"
jbe@619 1168 WHERE "newsletter"."published" <= now()
jbe@619 1169 AND "newsletter"."sent" ISNULL
jbe@619 1170 AND (
jbe@619 1171 "member"."disable_notifications" = FALSE OR
jbe@619 1172 "newsletter"."include_all_members" = TRUE )
jbe@619 1173 AND (
jbe@619 1174 "newsletter"."unit_id" ISNULL OR
jbe@619 1175 "privilege"."member_id" NOTNULL OR
jbe@619 1176 "subscription"."member_id" NOTNULL );
jbe@619 1177
jbe@619 1178 COMMENT ON VIEW "newsletter_to_send" IS 'List of "newsletter_id"s for each member that are due to be sent out';
jbe@619 1179
jbe@619 1180 COMMENT ON COLUMN "newsletter"."published" IS 'Timestamp when the newsletter was supposed to be sent out (can be used for ordering)';
jbe@619 1181
jbe@619 1182 SELECT "copy_current_draft_data" ("id") FROM "initiative";
jbe@619 1183
jbe@619 1184 COMMIT;
jbe@619 1185 BEGIN;
jbe@619 1186
jbe@619 1187 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
jbe@619 1188 SELECT * FROM (VALUES ('4.2.1', 4, 2, 1))
jbe@619 1189 AS "subquery"("string", "major", "minor", "revision");
jbe@619 1190
jbe@619 1191 ALTER TABLE "unit" ADD COLUMN "attr" JSONB NOT NULL DEFAULT '{}' CHECK (jsonb_typeof("attr") = 'object');
jbe@619 1192 COMMENT ON COLUMN "unit"."attr" IS 'Opaque data structure to store any extended attributes used by frontend or middleware';
jbe@619 1193
jbe@619 1194 ALTER TABLE "unit" ADD COLUMN "member_weight" INT4;
jbe@619 1195 COMMENT ON COLUMN "unit"."member_weight" IS 'Sum of active members'' voting weight';
jbe@619 1196
jbe@619 1197 ALTER TABLE "snapshot_population" ADD COLUMN "weight" INT4 NOT NULL DEFAULT 1;
jbe@619 1198 ALTER TABLE "snapshot_population" ALTER COLUMN "weight" DROP DEFAULT;
jbe@619 1199
jbe@619 1200 ALTER TABLE "privilege" ADD COLUMN "weight" INT4 NOT NULL DEFAULT 1 CHECK ("weight" >= 0);
jbe@619 1201 COMMENT ON COLUMN "privilege"."weight" IS 'Voting weight of member in unit';
jbe@619 1202
jbe@619 1203 CREATE TABLE "issue_privilege" (
jbe@619 1204 PRIMARY KEY ("issue_id", "member_id"),
jbe@619 1205 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@619 1206 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@619 1207 "initiative_right" BOOLEAN,
jbe@619 1208 "voting_right" BOOLEAN,
jbe@619 1209 "polling_right" BOOLEAN,
jbe@619 1210 "weight" INT4 CHECK ("weight" >= 0) );
jbe@619 1211 CREATE INDEX "issue_privilege_idx" ON "issue_privilege" ("member_id");
jbe@619 1212 COMMENT ON TABLE "issue_privilege" IS 'Override of "privilege" table for rights of members in certain issues';
jbe@619 1213
jbe@619 1214 ALTER TABLE "direct_interest_snapshot" ADD COLUMN "ownweight" INT4 NOT NULL DEFAULT 1;
jbe@619 1215 ALTER TABLE "direct_interest_snapshot" ALTER COLUMN "ownweight" DROP DEFAULT;
jbe@619 1216 COMMENT ON COLUMN "direct_interest_snapshot"."ownweight" IS 'Own voting weight of member, disregading delegations';
jbe@619 1217 COMMENT ON COLUMN "direct_interest_snapshot"."weight" IS 'Voting weight of member according to own weight and "delegating_interest_snapshot"';
jbe@619 1218
jbe@619 1219 ALTER TABLE "delegating_interest_snapshot" ADD COLUMN "ownweight" INT4 NOT NULL DEFAULT 1;
jbe@619 1220 ALTER TABLE "delegating_interest_snapshot" ALTER COLUMN "ownweight" DROP DEFAULT;
jbe@619 1221 COMMENT ON COLUMN "delegating_interest_snapshot"."ownweight" IS 'Own voting weight of member, disregading delegations';
jbe@619 1222 COMMENT ON COLUMN "delegating_interest_snapshot"."weight" IS 'Intermediate voting weight considering incoming delegations';
jbe@619 1223
jbe@619 1224 ALTER TABLE "direct_voter" ADD COLUMN "ownweight" INT4 DEFAULT 1;
jbe@619 1225 ALTER TABLE "direct_voter" ALTER COLUMN "ownweight" DROP DEFAULT;
jbe@619 1226 COMMENT ON COLUMN "direct_voter"."ownweight" IS 'Own voting weight of member, disregarding delegations';
jbe@619 1227 COMMENT ON COLUMN "direct_voter"."weight" IS 'Voting weight of member according to own weight and "delegating_interest_snapshot"';
jbe@619 1228
jbe@619 1229 ALTER TABLE "delegating_voter" ADD COLUMN "ownweight" INT4 NOT NULL DEFAULT 1;
jbe@619 1230 ALTER TABLE "delegating_voter" ALTER COLUMN "ownweight" DROP DEFAULT;
jbe@619 1231 COMMENT ON COLUMN "delegating_voter"."ownweight" IS 'Own voting weight of member, disregarding delegations';
jbe@619 1232 COMMENT ON COLUMN "delegating_voter"."weight" IS 'Intermediate voting weight considering incoming delegations';
jbe@619 1233
jbe@619 1234 ALTER TABLE "posting" ADD FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id");
jbe@619 1235
jbe@619 1236 DROP VIEW "issue_delegation";
jbe@619 1237 CREATE VIEW "issue_delegation" AS
jbe@619 1238 SELECT DISTINCT ON ("issue"."id", "delegation"."truster_id")
jbe@619 1239 "issue"."id" AS "issue_id",
jbe@619 1240 "delegation"."id",
jbe@619 1241 "delegation"."truster_id",
jbe@619 1242 "delegation"."trustee_id",
jbe@619 1243 COALESCE("issue_privilege"."weight", "privilege"."weight") AS "weight",
jbe@619 1244 "delegation"."scope"
jbe@619 1245 FROM "issue"
jbe@619 1246 JOIN "area"
jbe@619 1247 ON "area"."id" = "issue"."area_id"
jbe@619 1248 JOIN "delegation"
jbe@619 1249 ON "delegation"."unit_id" = "area"."unit_id"
jbe@619 1250 OR "delegation"."area_id" = "area"."id"
jbe@619 1251 OR "delegation"."issue_id" = "issue"."id"
jbe@619 1252 JOIN "member"
jbe@619 1253 ON "delegation"."truster_id" = "member"."id"
jbe@619 1254 LEFT JOIN "privilege"
jbe@619 1255 ON "area"."unit_id" = "privilege"."unit_id"
jbe@619 1256 AND "delegation"."truster_id" = "privilege"."member_id"
jbe@619 1257 LEFT JOIN "issue_privilege"
jbe@619 1258 ON "issue"."id" = "issue_privilege"."issue_id"
jbe@619 1259 AND "delegation"."truster_id" = "issue_privilege"."member_id"
jbe@619 1260 WHERE "member"."active"
jbe@619 1261 AND COALESCE("issue_privilege"."voting_right", "privilege"."voting_right")
jbe@619 1262 ORDER BY
jbe@619 1263 "issue"."id",
jbe@619 1264 "delegation"."truster_id",
jbe@619 1265 "delegation"."scope" DESC;
jbe@619 1266 COMMENT ON VIEW "issue_delegation" IS 'Issue delegations where trusters are active and have voting right';
jbe@619 1267
jbe@619 1268 CREATE OR REPLACE VIEW "unit_member" AS
jbe@619 1269 SELECT
jbe@619 1270 "privilege"."unit_id" AS "unit_id",
jbe@619 1271 "member"."id" AS "member_id",
jbe@619 1272 "privilege"."weight"
jbe@619 1273 FROM "privilege" JOIN "member" ON "member"."id" = "privilege"."member_id"
jbe@619 1274 WHERE "privilege"."voting_right" AND "member"."active";
jbe@619 1275
jbe@619 1276 CREATE OR REPLACE VIEW "unit_member_count" AS
jbe@619 1277 SELECT
jbe@619 1278 "unit"."id" AS "unit_id",
jbe@619 1279 count("unit_member"."member_id") AS "member_count",
jbe@619 1280 sum("unit_member"."weight") AS "member_weight"
jbe@619 1281 FROM "unit" LEFT JOIN "unit_member"
jbe@619 1282 ON "unit"."id" = "unit_member"."unit_id"
jbe@619 1283 GROUP BY "unit"."id";
jbe@619 1284
jbe@619 1285 CREATE OR REPLACE VIEW "event_for_notification" AS
jbe@619 1286 SELECT
jbe@619 1287 "member"."id" AS "recipient_id",
jbe@619 1288 "event".*
jbe@619 1289 FROM "member" CROSS JOIN "event"
jbe@619 1290 JOIN "issue" ON "issue"."id" = "event"."issue_id"
jbe@619 1291 JOIN "area" ON "area"."id" = "issue"."area_id"
jbe@619 1292 LEFT JOIN "privilege" ON
jbe@619 1293 "privilege"."member_id" = "member"."id" AND
jbe@619 1294 "privilege"."unit_id" = "area"."unit_id"
jbe@619 1295 LEFT JOIN "issue_privilege" ON
jbe@619 1296 "issue_privilege"."member_id" = "member"."id" AND
jbe@619 1297 "issue_privilege"."issue_id" = "event"."issue_id"
jbe@619 1298 LEFT JOIN "subscription" ON
jbe@619 1299 "subscription"."member_id" = "member"."id" AND
jbe@619 1300 "subscription"."unit_id" = "area"."unit_id"
jbe@619 1301 LEFT JOIN "ignored_area" ON
jbe@619 1302 "ignored_area"."member_id" = "member"."id" AND
jbe@619 1303 "ignored_area"."area_id" = "issue"."area_id"
jbe@619 1304 LEFT JOIN "interest" ON
jbe@619 1305 "interest"."member_id" = "member"."id" AND
jbe@619 1306 "interest"."issue_id" = "event"."issue_id"
jbe@619 1307 LEFT JOIN "supporter" ON
jbe@619 1308 "supporter"."member_id" = "member"."id" AND
jbe@619 1309 "supporter"."initiative_id" = "event"."initiative_id"
jbe@619 1310 WHERE (
jbe@619 1311 COALESCE("issue_privilege"."voting_right", "privilege"."voting_right") OR
jbe@619 1312 "subscription"."member_id" NOTNULL
jbe@619 1313 ) AND ("ignored_area"."member_id" ISNULL OR "interest"."member_id" NOTNULL)
jbe@619 1314 AND (
jbe@619 1315 "event"."event" = 'issue_state_changed'::"event_type" OR
jbe@619 1316 ( "event"."event" = 'initiative_revoked'::"event_type" AND
jbe@619 1317 "supporter"."member_id" NOTNULL ) );
jbe@619 1318
jbe@619 1319 CREATE OR REPLACE FUNCTION "featured_initiative"
jbe@619 1320 ( "recipient_id_p" "member"."id"%TYPE,
jbe@619 1321 "area_id_p" "area"."id"%TYPE )
jbe@619 1322 RETURNS SETOF "initiative"."id"%TYPE
jbe@619 1323 LANGUAGE 'plpgsql' STABLE AS $$
jbe@619 1324 DECLARE
jbe@619 1325 "counter_v" "member"."notification_counter"%TYPE;
jbe@619 1326 "sample_size_v" "member"."notification_sample_size"%TYPE;
jbe@619 1327 "initiative_id_ary" INT4[]; --"initiative"."id"%TYPE[]
jbe@619 1328 "match_v" BOOLEAN;
jbe@619 1329 "member_id_v" "member"."id"%TYPE;
jbe@619 1330 "seed_v" TEXT;
jbe@619 1331 "initiative_id_v" "initiative"."id"%TYPE;
jbe@619 1332 BEGIN
jbe@619 1333 SELECT "notification_counter", "notification_sample_size"
jbe@619 1334 INTO "counter_v", "sample_size_v"
jbe@619 1335 FROM "member" WHERE "id" = "recipient_id_p";
jbe@619 1336 IF COALESCE("sample_size_v" <= 0, TRUE) THEN
jbe@619 1337 RETURN;
jbe@619 1338 END IF;
jbe@619 1339 "initiative_id_ary" := '{}';
jbe@619 1340 LOOP
jbe@619 1341 "match_v" := FALSE;
jbe@619 1342 FOR "member_id_v", "seed_v" IN
jbe@619 1343 SELECT * FROM (
jbe@619 1344 SELECT DISTINCT
jbe@619 1345 "supporter"."member_id",
jbe@619 1346 md5(
jbe@619 1347 "recipient_id_p" || '-' ||
jbe@619 1348 "counter_v" || '-' ||
jbe@619 1349 "area_id_p" || '-' ||
jbe@619 1350 "supporter"."member_id"
jbe@619 1351 ) AS "seed"
jbe@619 1352 FROM "supporter"
jbe@619 1353 JOIN "initiative" ON "initiative"."id" = "supporter"."initiative_id"
jbe@619 1354 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
jbe@619 1355 WHERE "supporter"."member_id" != "recipient_id_p"
jbe@619 1356 AND "issue"."area_id" = "area_id_p"
jbe@619 1357 AND "issue"."state" IN ('admission', 'discussion', 'verification')
jbe@619 1358 ) AS "subquery"
jbe@619 1359 ORDER BY "seed"
jbe@619 1360 LOOP
jbe@619 1361 SELECT "initiative"."id" INTO "initiative_id_v"
jbe@619 1362 FROM "initiative"
jbe@619 1363 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
jbe@619 1364 JOIN "area" ON "area"."id" = "issue"."area_id"
jbe@619 1365 JOIN "supporter" ON "supporter"."initiative_id" = "initiative"."id"
jbe@619 1366 LEFT JOIN "supporter" AS "self_support" ON
jbe@619 1367 "self_support"."initiative_id" = "initiative"."id" AND
jbe@619 1368 "self_support"."member_id" = "recipient_id_p"
jbe@619 1369 LEFT JOIN "privilege" ON
jbe@619 1370 "privilege"."member_id" = "recipient_id_p" AND
jbe@619 1371 "privilege"."unit_id" = "area"."unit_id"
jbe@619 1372 LEFT JOIN "issue_privilege" ON
jbe@619 1373 "issue_privilege"."member_id" = "recipient_id_p" AND
jbe@619 1374 "issue_privilege"."issue_id" = "initiative"."issue_id"
jbe@619 1375 LEFT JOIN "subscription" ON
jbe@619 1376 "subscription"."member_id" = "recipient_id_p" AND
jbe@619 1377 "subscription"."unit_id" = "area"."unit_id"
jbe@619 1378 LEFT JOIN "ignored_initiative" ON
jbe@619 1379 "ignored_initiative"."member_id" = "recipient_id_p" AND
jbe@619 1380 "ignored_initiative"."initiative_id" = "initiative"."id"
jbe@619 1381 WHERE "supporter"."member_id" = "member_id_v"
jbe@619 1382 AND "issue"."area_id" = "area_id_p"
jbe@619 1383 AND "issue"."state" IN ('admission', 'discussion', 'verification')
jbe@619 1384 AND "initiative"."revoked" ISNULL
jbe@619 1385 AND "self_support"."member_id" ISNULL
jbe@619 1386 AND NOT "initiative_id_ary" @> ARRAY["initiative"."id"]
jbe@619 1387 AND (
jbe@619 1388 COALESCE(
jbe@619 1389 "issue_privilege"."voting_right", "privilege"."voting_right"
jbe@619 1390 ) OR "subscription"."member_id" NOTNULL )
jbe@619 1391 AND "ignored_initiative"."member_id" ISNULL
jbe@619 1392 AND NOT EXISTS (
jbe@619 1393 SELECT NULL FROM "draft"
jbe@619 1394 JOIN "ignored_member" ON
jbe@619 1395 "ignored_member"."member_id" = "recipient_id_p" AND
jbe@619 1396 "ignored_member"."other_member_id" = "draft"."author_id"
jbe@619 1397 WHERE "draft"."initiative_id" = "initiative"."id"
jbe@619 1398 )
jbe@619 1399 ORDER BY md5("seed_v" || '-' || "initiative"."id")
jbe@619 1400 LIMIT 1;
jbe@619 1401 IF FOUND THEN
jbe@619 1402 "match_v" := TRUE;
jbe@619 1403 RETURN NEXT "initiative_id_v";
jbe@619 1404 IF array_length("initiative_id_ary", 1) + 1 >= "sample_size_v" THEN
jbe@619 1405 RETURN;
jbe@619 1406 END IF;
jbe@619 1407 "initiative_id_ary" := "initiative_id_ary" || "initiative_id_v";
jbe@619 1408 END IF;
jbe@619 1409 END LOOP;
jbe@619 1410 EXIT WHEN NOT "match_v";
jbe@619 1411 END LOOP;
jbe@619 1412 RETURN;
jbe@619 1413 END;
jbe@619 1414 $$;
jbe@619 1415
jbe@619 1416 CREATE OR REPLACE FUNCTION "delegation_chain"
jbe@619 1417 ( "member_id_p" "member"."id"%TYPE,
jbe@619 1418 "unit_id_p" "unit"."id"%TYPE,
jbe@619 1419 "area_id_p" "area"."id"%TYPE,
jbe@619 1420 "issue_id_p" "issue"."id"%TYPE,
jbe@619 1421 "simulate_trustee_id_p" "member"."id"%TYPE DEFAULT NULL,
jbe@619 1422 "simulate_default_p" BOOLEAN DEFAULT FALSE )
jbe@619 1423 RETURNS SETOF "delegation_chain_row"
jbe@619 1424 LANGUAGE 'plpgsql' STABLE AS $$
jbe@619 1425 DECLARE
jbe@619 1426 "scope_v" "delegation_scope";
jbe@619 1427 "unit_id_v" "unit"."id"%TYPE;
jbe@619 1428 "area_id_v" "area"."id"%TYPE;
jbe@619 1429 "issue_row" "issue"%ROWTYPE;
jbe@619 1430 "visited_member_ids" INT4[]; -- "member"."id"%TYPE[]
jbe@619 1431 "loop_member_id_v" "member"."id"%TYPE;
jbe@619 1432 "output_row" "delegation_chain_row";
jbe@619 1433 "output_rows" "delegation_chain_row"[];
jbe@619 1434 "simulate_v" BOOLEAN;
jbe@619 1435 "simulate_here_v" BOOLEAN;
jbe@619 1436 "delegation_row" "delegation"%ROWTYPE;
jbe@619 1437 "row_count" INT4;
jbe@619 1438 "i" INT4;
jbe@619 1439 "loop_v" BOOLEAN;
jbe@619 1440 BEGIN
jbe@619 1441 IF "simulate_trustee_id_p" NOTNULL AND "simulate_default_p" THEN
jbe@619 1442 RAISE EXCEPTION 'Both "simulate_trustee_id_p" is set, and "simulate_default_p" is true';
jbe@619 1443 END IF;
jbe@619 1444 IF "simulate_trustee_id_p" NOTNULL OR "simulate_default_p" THEN
jbe@619 1445 "simulate_v" := TRUE;
jbe@619 1446 ELSE
jbe@619 1447 "simulate_v" := FALSE;
jbe@619 1448 END IF;
jbe@619 1449 IF
jbe@619 1450 "unit_id_p" NOTNULL AND
jbe@619 1451 "area_id_p" ISNULL AND
jbe@619 1452 "issue_id_p" ISNULL
jbe@619 1453 THEN
jbe@619 1454 "scope_v" := 'unit';
jbe@619 1455 "unit_id_v" := "unit_id_p";
jbe@619 1456 ELSIF
jbe@619 1457 "unit_id_p" ISNULL AND
jbe@619 1458 "area_id_p" NOTNULL AND
jbe@619 1459 "issue_id_p" ISNULL
jbe@619 1460 THEN
jbe@619 1461 "scope_v" := 'area';
jbe@619 1462 "area_id_v" := "area_id_p";
jbe@619 1463 SELECT "unit_id" INTO "unit_id_v"
jbe@619 1464 FROM "area" WHERE "id" = "area_id_v";
jbe@619 1465 ELSIF
jbe@619 1466 "unit_id_p" ISNULL AND
jbe@619 1467 "area_id_p" ISNULL AND
jbe@619 1468 "issue_id_p" NOTNULL
jbe@619 1469 THEN
jbe@619 1470 SELECT INTO "issue_row" * FROM "issue" WHERE "id" = "issue_id_p";
jbe@619 1471 IF "issue_row"."id" ISNULL THEN
jbe@619 1472 RETURN;
jbe@619 1473 END IF;
jbe@619 1474 IF "issue_row"."closed" NOTNULL THEN
jbe@619 1475 IF "simulate_v" THEN
jbe@619 1476 RAISE EXCEPTION 'Tried to simulate delegation chain for closed issue.';
jbe@619 1477 END IF;
jbe@619 1478 FOR "output_row" IN
jbe@619 1479 SELECT * FROM
jbe@619 1480 "delegation_chain_for_closed_issue"("member_id_p", "issue_id_p")
jbe@619 1481 LOOP
jbe@619 1482 RETURN NEXT "output_row";
jbe@619 1483 END LOOP;
jbe@619 1484 RETURN;
jbe@619 1485 END IF;
jbe@619 1486 "scope_v" := 'issue';
jbe@619 1487 SELECT "area_id" INTO "area_id_v"
jbe@619 1488 FROM "issue" WHERE "id" = "issue_id_p";
jbe@619 1489 SELECT "unit_id" INTO "unit_id_v"
jbe@619 1490 FROM "area" WHERE "id" = "area_id_v";
jbe@619 1491 ELSE
jbe@619 1492 RAISE EXCEPTION 'Exactly one of unit_id_p, area_id_p, or issue_id_p must be NOTNULL.';
jbe@619 1493 END IF;
jbe@619 1494 "visited_member_ids" := '{}';
jbe@619 1495 "loop_member_id_v" := NULL;
jbe@619 1496 "output_rows" := '{}';
jbe@619 1497 "output_row"."index" := 0;
jbe@619 1498 "output_row"."member_id" := "member_id_p";
jbe@619 1499 "output_row"."member_valid" := TRUE;
jbe@619 1500 "output_row"."participation" := FALSE;
jbe@619 1501 "output_row"."overridden" := FALSE;
jbe@619 1502 "output_row"."disabled_out" := FALSE;
jbe@619 1503 "output_row"."scope_out" := NULL;
jbe@619 1504 LOOP
jbe@619 1505 IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN
jbe@619 1506 "loop_member_id_v" := "output_row"."member_id";
jbe@619 1507 ELSE
jbe@619 1508 "visited_member_ids" :=
jbe@619 1509 "visited_member_ids" || "output_row"."member_id";
jbe@619 1510 END IF;
jbe@619 1511 IF "output_row"."participation" ISNULL THEN
jbe@619 1512 "output_row"."overridden" := NULL;
jbe@619 1513 ELSIF "output_row"."participation" THEN
jbe@619 1514 "output_row"."overridden" := TRUE;
jbe@619 1515 END IF;
jbe@619 1516 "output_row"."scope_in" := "output_row"."scope_out";
jbe@619 1517 "output_row"."member_valid" := EXISTS (
jbe@619 1518 SELECT NULL FROM "member"
jbe@619 1519 LEFT JOIN "privilege"
jbe@619 1520 ON "privilege"."member_id" = "member"."id"
jbe@619 1521 AND "privilege"."unit_id" = "unit_id_v"
jbe@619 1522 LEFT JOIN "issue_privilege"
jbe@619 1523 ON "issue_privilege"."member_id" = "member"."id"
jbe@619 1524 AND "issue_privilege"."issue_id" = "issue_id_p"
jbe@619 1525 WHERE "id" = "output_row"."member_id"
jbe@619 1526 AND "member"."active"
jbe@619 1527 AND COALESCE(
jbe@619 1528 "issue_privilege"."voting_right", "privilege"."voting_right")
jbe@619 1529 );
jbe@619 1530 "simulate_here_v" := (
jbe@619 1531 "simulate_v" AND
jbe@619 1532 "output_row"."member_id" = "member_id_p"
jbe@619 1533 );
jbe@619 1534 "delegation_row" := ROW(NULL);
jbe@619 1535 IF "output_row"."member_valid" OR "simulate_here_v" THEN
jbe@619 1536 IF "scope_v" = 'unit' THEN
jbe@619 1537 IF NOT "simulate_here_v" THEN
jbe@619 1538 SELECT * INTO "delegation_row" FROM "delegation"
jbe@619 1539 WHERE "truster_id" = "output_row"."member_id"
jbe@619 1540 AND "unit_id" = "unit_id_v";
jbe@619 1541 END IF;
jbe@619 1542 ELSIF "scope_v" = 'area' THEN
jbe@619 1543 IF "simulate_here_v" THEN
jbe@619 1544 IF "simulate_trustee_id_p" ISNULL THEN
jbe@619 1545 SELECT * INTO "delegation_row" FROM "delegation"
jbe@619 1546 WHERE "truster_id" = "output_row"."member_id"
jbe@619 1547 AND "unit_id" = "unit_id_v";
jbe@619 1548 END IF;
jbe@619 1549 ELSE
jbe@619 1550 SELECT * INTO "delegation_row" FROM "delegation"
jbe@619 1551 WHERE "truster_id" = "output_row"."member_id"
jbe@619 1552 AND (
jbe@619 1553 "unit_id" = "unit_id_v" OR
jbe@619 1554 "area_id" = "area_id_v"
jbe@619 1555 )
jbe@619 1556 ORDER BY "scope" DESC;
jbe@619 1557 END IF;
jbe@619 1558 ELSIF "scope_v" = 'issue' THEN
jbe@619 1559 IF "issue_row"."fully_frozen" ISNULL THEN
jbe@619 1560 "output_row"."participation" := EXISTS (
jbe@619 1561 SELECT NULL FROM "interest"
jbe@619 1562 WHERE "issue_id" = "issue_id_p"
jbe@619 1563 AND "member_id" = "output_row"."member_id"
jbe@619 1564 );
jbe@619 1565 ELSE
jbe@619 1566 IF "output_row"."member_id" = "member_id_p" THEN
jbe@619 1567 "output_row"."participation" := EXISTS (
jbe@619 1568 SELECT NULL FROM "direct_voter"
jbe@619 1569 WHERE "issue_id" = "issue_id_p"
jbe@619 1570 AND "member_id" = "output_row"."member_id"
jbe@619 1571 );
jbe@619 1572 ELSE
jbe@619 1573 "output_row"."participation" := NULL;
jbe@619 1574 END IF;
jbe@619 1575 END IF;
jbe@619 1576 IF "simulate_here_v" THEN
jbe@619 1577 IF "simulate_trustee_id_p" ISNULL THEN
jbe@619 1578 SELECT * INTO "delegation_row" FROM "delegation"
jbe@619 1579 WHERE "truster_id" = "output_row"."member_id"
jbe@619 1580 AND (
jbe@619 1581 "unit_id" = "unit_id_v" OR
jbe@619 1582 "area_id" = "area_id_v"
jbe@619 1583 )
jbe@619 1584 ORDER BY "scope" DESC;
jbe@619 1585 END IF;
jbe@619 1586 ELSE
jbe@619 1587 SELECT * INTO "delegation_row" FROM "delegation"
jbe@619 1588 WHERE "truster_id" = "output_row"."member_id"
jbe@619 1589 AND (
jbe@619 1590 "unit_id" = "unit_id_v" OR
jbe@619 1591 "area_id" = "area_id_v" OR
jbe@619 1592 "issue_id" = "issue_id_p"
jbe@619 1593 )
jbe@619 1594 ORDER BY "scope" DESC;
jbe@619 1595 END IF;
jbe@619 1596 END IF;
jbe@619 1597 ELSE
jbe@619 1598 "output_row"."participation" := FALSE;
jbe@619 1599 END IF;
jbe@619 1600 IF "simulate_here_v" AND "simulate_trustee_id_p" NOTNULL THEN
jbe@619 1601 "output_row"."scope_out" := "scope_v";
jbe@619 1602 "output_rows" := "output_rows" || "output_row";
jbe@619 1603 "output_row"."member_id" := "simulate_trustee_id_p";
jbe@619 1604 ELSIF "delegation_row"."trustee_id" NOTNULL THEN
jbe@619 1605 "output_row"."scope_out" := "delegation_row"."scope";
jbe@619 1606 "output_rows" := "output_rows" || "output_row";
jbe@619 1607 "output_row"."member_id" := "delegation_row"."trustee_id";
jbe@619 1608 ELSIF "delegation_row"."scope" NOTNULL THEN
jbe@619 1609 "output_row"."scope_out" := "delegation_row"."scope";
jbe@619 1610 "output_row"."disabled_out" := TRUE;
jbe@619 1611 "output_rows" := "output_rows" || "output_row";
jbe@619 1612 EXIT;
jbe@619 1613 ELSE
jbe@619 1614 "output_row"."scope_out" := NULL;
jbe@619 1615 "output_rows" := "output_rows" || "output_row";
jbe@619 1616 EXIT;
jbe@619 1617 END IF;
jbe@619 1618 EXIT WHEN "loop_member_id_v" NOTNULL;
jbe@619 1619 "output_row"."index" := "output_row"."index" + 1;
jbe@619 1620 END LOOP;
jbe@619 1621 "row_count" := array_upper("output_rows", 1);
jbe@619 1622 "i" := 1;
jbe@619 1623 "loop_v" := FALSE;
jbe@619 1624 LOOP
jbe@619 1625 "output_row" := "output_rows"["i"];
jbe@619 1626 EXIT WHEN "output_row" ISNULL; -- NOTE: ISNULL and NOT ... NOTNULL produce different results!
jbe@619 1627 IF "loop_v" THEN
jbe@619 1628 IF "i" + 1 = "row_count" THEN
jbe@619 1629 "output_row"."loop" := 'last';
jbe@619 1630 ELSIF "i" = "row_count" THEN
jbe@619 1631 "output_row"."loop" := 'repetition';
jbe@619 1632 ELSE
jbe@619 1633 "output_row"."loop" := 'intermediate';
jbe@619 1634 END IF;
jbe@619 1635 ELSIF "output_row"."member_id" = "loop_member_id_v" THEN
jbe@619 1636 "output_row"."loop" := 'first';
jbe@619 1637 "loop_v" := TRUE;
jbe@619 1638 END IF;
jbe@619 1639 IF "scope_v" = 'unit' THEN
jbe@619 1640 "output_row"."participation" := NULL;
jbe@619 1641 END IF;
jbe@619 1642 RETURN NEXT "output_row";
jbe@619 1643 "i" := "i" + 1;
jbe@619 1644 END LOOP;
jbe@619 1645 RETURN;
jbe@619 1646 END;
jbe@619 1647 $$;
jbe@619 1648
jbe@619 1649 CREATE OR REPLACE FUNCTION "calculate_member_counts"()
jbe@619 1650 RETURNS VOID
jbe@619 1651 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@619 1652 BEGIN
jbe@619 1653 PERFORM "require_transaction_isolation"();
jbe@619 1654 DELETE FROM "member_count";
jbe@619 1655 INSERT INTO "member_count" ("total_count")
jbe@619 1656 SELECT "total_count" FROM "member_count_view";
jbe@619 1657 UPDATE "unit" SET
jbe@619 1658 "member_count" = "view"."member_count",
jbe@619 1659 "member_weight" = "view"."member_weight"
jbe@619 1660 FROM "unit_member_count" AS "view"
jbe@619 1661 WHERE "view"."unit_id" = "unit"."id";
jbe@619 1662 RETURN;
jbe@619 1663 END;
jbe@619 1664 $$;
jbe@619 1665 COMMENT ON FUNCTION "calculate_member_counts"() IS 'Updates "member_count" table and "member_count" and "member_weight" columns of table "area" by materializing data from views "member_count_view" and "unit_member_count"';
jbe@619 1666
jbe@619 1667 CREATE OR REPLACE FUNCTION "weight_of_added_delegations_for_snapshot"
jbe@619 1668 ( "snapshot_id_p" "snapshot"."id"%TYPE,
jbe@619 1669 "issue_id_p" "issue"."id"%TYPE,
jbe@619 1670 "member_id_p" "member"."id"%TYPE,
jbe@619 1671 "delegate_member_ids_p" "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
jbe@619 1672 RETURNS "direct_interest_snapshot"."weight"%TYPE
jbe@619 1673 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@619 1674 DECLARE
jbe@619 1675 "issue_delegation_row" "issue_delegation"%ROWTYPE;
jbe@619 1676 "delegate_member_ids_v" "delegating_interest_snapshot"."delegate_member_ids"%TYPE;
jbe@619 1677 "weight_v" INT4;
jbe@619 1678 "sub_weight_v" INT4;
jbe@619 1679 BEGIN
jbe@619 1680 PERFORM "require_transaction_isolation"();
jbe@619 1681 "weight_v" := 0;
jbe@619 1682 FOR "issue_delegation_row" IN
jbe@619 1683 SELECT * FROM "issue_delegation"
jbe@619 1684 WHERE "trustee_id" = "member_id_p"
jbe@619 1685 AND "issue_id" = "issue_id_p"
jbe@619 1686 LOOP
jbe@619 1687 IF NOT EXISTS (
jbe@619 1688 SELECT NULL FROM "direct_interest_snapshot"
jbe@619 1689 WHERE "snapshot_id" = "snapshot_id_p"
jbe@619 1690 AND "issue_id" = "issue_id_p"
jbe@619 1691 AND "member_id" = "issue_delegation_row"."truster_id"
jbe@619 1692 ) AND NOT EXISTS (
jbe@619 1693 SELECT NULL FROM "delegating_interest_snapshot"
jbe@619 1694 WHERE "snapshot_id" = "snapshot_id_p"
jbe@619 1695 AND "issue_id" = "issue_id_p"
jbe@619 1696 AND "member_id" = "issue_delegation_row"."truster_id"
jbe@619 1697 ) THEN
jbe@619 1698 "delegate_member_ids_v" :=
jbe@619 1699 "member_id_p" || "delegate_member_ids_p";
jbe@619 1700 INSERT INTO "delegating_interest_snapshot" (
jbe@619 1701 "snapshot_id",
jbe@619 1702 "issue_id",
jbe@619 1703 "member_id",
jbe@619 1704 "ownweight",
jbe@619 1705 "scope",
jbe@619 1706 "delegate_member_ids"
jbe@619 1707 ) VALUES (
jbe@619 1708 "snapshot_id_p",
jbe@619 1709 "issue_id_p",
jbe@619 1710 "issue_delegation_row"."truster_id",
jbe@619 1711 "issue_delegation_row"."weight",
jbe@619 1712 "issue_delegation_row"."scope",
jbe@619 1713 "delegate_member_ids_v"
jbe@619 1714 );
jbe@619 1715 "sub_weight_v" := "issue_delegation_row"."weight" +
jbe@619 1716 "weight_of_added_delegations_for_snapshot"(
jbe@619 1717 "snapshot_id_p",
jbe@619 1718 "issue_id_p",
jbe@619 1719 "issue_delegation_row"."truster_id",
jbe@619 1720 "delegate_member_ids_v"
jbe@619 1721 );
jbe@619 1722 UPDATE "delegating_interest_snapshot"
jbe@619 1723 SET "weight" = "sub_weight_v"
jbe@619 1724 WHERE "snapshot_id" = "snapshot_id_p"
jbe@619 1725 AND "issue_id" = "issue_id_p"
jbe@619 1726 AND "member_id" = "issue_delegation_row"."truster_id";
jbe@619 1727 "weight_v" := "weight_v" + "sub_weight_v";
jbe@619 1728 END IF;
jbe@619 1729 END LOOP;
jbe@619 1730 RETURN "weight_v";
jbe@619 1731 END;
jbe@619 1732 $$;
jbe@619 1733
jbe@619 1734 CREATE OR REPLACE FUNCTION "take_snapshot"
jbe@619 1735 ( "issue_id_p" "issue"."id"%TYPE,
jbe@619 1736 "area_id_p" "area"."id"%TYPE = NULL )
jbe@619 1737 RETURNS "snapshot"."id"%TYPE
jbe@619 1738 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@619 1739 DECLARE
jbe@619 1740 "area_id_v" "area"."id"%TYPE;
jbe@619 1741 "unit_id_v" "unit"."id"%TYPE;
jbe@619 1742 "snapshot_id_v" "snapshot"."id"%TYPE;
jbe@619 1743 "issue_id_v" "issue"."id"%TYPE;
jbe@619 1744 "member_id_v" "member"."id"%TYPE;
jbe@619 1745 BEGIN
jbe@619 1746 IF "issue_id_p" NOTNULL AND "area_id_p" NOTNULL THEN
jbe@619 1747 RAISE EXCEPTION 'One of "issue_id_p" and "area_id_p" must be NULL';
jbe@619 1748 END IF;
jbe@619 1749 PERFORM "require_transaction_isolation"();
jbe@619 1750 IF "issue_id_p" ISNULL THEN
jbe@619 1751 "area_id_v" := "area_id_p";
jbe@619 1752 ELSE
jbe@619 1753 SELECT "area_id" INTO "area_id_v"
jbe@619 1754 FROM "issue" WHERE "id" = "issue_id_p";
jbe@619 1755 END IF;
jbe@619 1756 SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v";
jbe@619 1757 INSERT INTO "snapshot" ("area_id", "issue_id")
jbe@619 1758 VALUES ("area_id_v", "issue_id_p")
jbe@619 1759 RETURNING "id" INTO "snapshot_id_v";
jbe@619 1760 INSERT INTO "snapshot_population" ("snapshot_id", "member_id", "weight")
jbe@619 1761 SELECT
jbe@619 1762 "snapshot_id_v",
jbe@619 1763 "member"."id",
jbe@619 1764 COALESCE("issue_privilege"."weight", "privilege"."weight")
jbe@619 1765 FROM "member"
jbe@619 1766 LEFT JOIN "privilege"
jbe@619 1767 ON "privilege"."unit_id" = "unit_id_v"
jbe@619 1768 AND "privilege"."member_id" = "member"."id"
jbe@619 1769 LEFT JOIN "issue_privilege"
jbe@619 1770 ON "issue_privilege"."issue_id" = "issue_id_p"
jbe@619 1771 AND "issue_privilege"."member_id" = "member"."id"
jbe@619 1772 WHERE "member"."active" AND COALESCE(
jbe@619 1773 "issue_privilege"."voting_right", "privilege"."voting_right");
jbe@619 1774 UPDATE "snapshot" SET
jbe@619 1775 "population" = (
jbe@619 1776 SELECT sum("weight") FROM "snapshot_population"
jbe@619 1777 WHERE "snapshot_id" = "snapshot_id_v"
jbe@619 1778 ) WHERE "id" = "snapshot_id_v";
jbe@619 1779 FOR "issue_id_v" IN
jbe@619 1780 SELECT "id" FROM "issue"
jbe@619 1781 WHERE CASE WHEN "issue_id_p" ISNULL THEN
jbe@619 1782 "area_id" = "area_id_p" AND
jbe@619 1783 "state" = 'admission'
jbe@619 1784 ELSE
jbe@619 1785 "id" = "issue_id_p"
jbe@619 1786 END
jbe@619 1787 LOOP
jbe@619 1788 INSERT INTO "snapshot_issue" ("snapshot_id", "issue_id")
jbe@619 1789 VALUES ("snapshot_id_v", "issue_id_v");
jbe@619 1790 INSERT INTO "direct_interest_snapshot"
jbe@619 1791 ("snapshot_id", "issue_id", "member_id", "ownweight")
jbe@619 1792 SELECT
jbe@619 1793 "snapshot_id_v" AS "snapshot_id",
jbe@619 1794 "issue_id_v" AS "issue_id",
jbe@619 1795 "member"."id" AS "member_id",
jbe@619 1796 COALESCE(
jbe@619 1797 "issue_privilege"."weight", "privilege"."weight"
jbe@619 1798 ) AS "ownweight"
jbe@619 1799 FROM "issue"
jbe@619 1800 JOIN "area" ON "issue"."area_id" = "area"."id"
jbe@619 1801 JOIN "interest" ON "issue"."id" = "interest"."issue_id"
jbe@619 1802 JOIN "member" ON "interest"."member_id" = "member"."id"
jbe@619 1803 LEFT JOIN "privilege"
jbe@619 1804 ON "privilege"."unit_id" = "area"."unit_id"
jbe@619 1805 AND "privilege"."member_id" = "member"."id"
jbe@619 1806 LEFT JOIN "issue_privilege"
jbe@619 1807 ON "issue_privilege"."issue_id" = "issue_id_v"
jbe@619 1808 AND "issue_privilege"."member_id" = "member"."id"
jbe@619 1809 WHERE "issue"."id" = "issue_id_v"
jbe@619 1810 AND "member"."active" AND COALESCE(
jbe@619 1811 "issue_privilege"."voting_right", "privilege"."voting_right");
jbe@619 1812 FOR "member_id_v" IN
jbe@619 1813 SELECT "member_id" FROM "direct_interest_snapshot"
jbe@619 1814 WHERE "snapshot_id" = "snapshot_id_v"
jbe@619 1815 AND "issue_id" = "issue_id_v"
jbe@619 1816 LOOP
jbe@619 1817 UPDATE "direct_interest_snapshot" SET
jbe@619 1818 "weight" = "ownweight" +
jbe@619 1819 "weight_of_added_delegations_for_snapshot"(
jbe@619 1820 "snapshot_id_v",
jbe@619 1821 "issue_id_v",
jbe@619 1822 "member_id_v",
jbe@619 1823 '{}'
jbe@619 1824 )
jbe@619 1825 WHERE "snapshot_id" = "snapshot_id_v"
jbe@619 1826 AND "issue_id" = "issue_id_v"
jbe@619 1827 AND "member_id" = "member_id_v";
jbe@619 1828 END LOOP;
jbe@619 1829 INSERT INTO "direct_supporter_snapshot"
jbe@619 1830 ( "snapshot_id", "issue_id", "initiative_id", "member_id",
jbe@619 1831 "draft_id", "informed", "satisfied" )
jbe@619 1832 SELECT
jbe@619 1833 "snapshot_id_v" AS "snapshot_id",
jbe@619 1834 "issue_id_v" AS "issue_id",
jbe@619 1835 "initiative"."id" AS "initiative_id",
jbe@619 1836 "supporter"."member_id" AS "member_id",
jbe@619 1837 "supporter"."draft_id" AS "draft_id",
jbe@619 1838 "supporter"."draft_id" = "current_draft"."id" AS "informed",
jbe@619 1839 NOT EXISTS (
jbe@619 1840 SELECT NULL FROM "critical_opinion"
jbe@619 1841 WHERE "initiative_id" = "initiative"."id"
jbe@619 1842 AND "member_id" = "supporter"."member_id"
jbe@619 1843 ) AS "satisfied"
jbe@619 1844 FROM "initiative"
jbe@619 1845 JOIN "supporter"
jbe@619 1846 ON "supporter"."initiative_id" = "initiative"."id"
jbe@619 1847 JOIN "current_draft"
jbe@619 1848 ON "initiative"."id" = "current_draft"."initiative_id"
jbe@619 1849 JOIN "direct_interest_snapshot"
jbe@619 1850 ON "snapshot_id_v" = "direct_interest_snapshot"."snapshot_id"
jbe@619 1851 AND "supporter"."member_id" = "direct_interest_snapshot"."member_id"
jbe@619 1852 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"
jbe@619 1853 WHERE "initiative"."issue_id" = "issue_id_v";
jbe@619 1854 DELETE FROM "temporary_suggestion_counts";
jbe@619 1855 INSERT INTO "temporary_suggestion_counts"
jbe@619 1856 ( "id",
jbe@619 1857 "minus2_unfulfilled_count", "minus2_fulfilled_count",
jbe@619 1858 "minus1_unfulfilled_count", "minus1_fulfilled_count",
jbe@619 1859 "plus1_unfulfilled_count", "plus1_fulfilled_count",
jbe@619 1860 "plus2_unfulfilled_count", "plus2_fulfilled_count" )
jbe@619 1861 SELECT
jbe@619 1862 "suggestion"."id",
jbe@619 1863 ( SELECT coalesce(sum("di"."weight"), 0)
jbe@619 1864 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
jbe@619 1865 ON "di"."snapshot_id" = "snapshot_id_v"
jbe@619 1866 AND "di"."issue_id" = "issue_id_v"
jbe@619 1867 AND "di"."member_id" = "opinion"."member_id"
jbe@619 1868 WHERE "opinion"."suggestion_id" = "suggestion"."id"
jbe@619 1869 AND "opinion"."degree" = -2
jbe@619 1870 AND "opinion"."fulfilled" = FALSE
jbe@619 1871 ) AS "minus2_unfulfilled_count",
jbe@619 1872 ( SELECT coalesce(sum("di"."weight"), 0)
jbe@619 1873 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
jbe@619 1874 ON "di"."snapshot_id" = "snapshot_id_v"
jbe@619 1875 AND "di"."issue_id" = "issue_id_v"
jbe@619 1876 AND "di"."member_id" = "opinion"."member_id"
jbe@619 1877 WHERE "opinion"."suggestion_id" = "suggestion"."id"
jbe@619 1878 AND "opinion"."degree" = -2
jbe@619 1879 AND "opinion"."fulfilled" = TRUE
jbe@619 1880 ) AS "minus2_fulfilled_count",
jbe@619 1881 ( SELECT coalesce(sum("di"."weight"), 0)
jbe@619 1882 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
jbe@619 1883 ON "di"."snapshot_id" = "snapshot_id_v"
jbe@619 1884 AND "di"."issue_id" = "issue_id_v"
jbe@619 1885 AND "di"."member_id" = "opinion"."member_id"
jbe@619 1886 WHERE "opinion"."suggestion_id" = "suggestion"."id"
jbe@619 1887 AND "opinion"."degree" = -1
jbe@619 1888 AND "opinion"."fulfilled" = FALSE
jbe@619 1889 ) AS "minus1_unfulfilled_count",
jbe@619 1890 ( SELECT coalesce(sum("di"."weight"), 0)
jbe@619 1891 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
jbe@619 1892 ON "di"."snapshot_id" = "snapshot_id_v"
jbe@619 1893 AND "di"."issue_id" = "issue_id_v"
jbe@619 1894 AND "di"."member_id" = "opinion"."member_id"
jbe@619 1895 WHERE "opinion"."suggestion_id" = "suggestion"."id"
jbe@619 1896 AND "opinion"."degree" = -1
jbe@619 1897 AND "opinion"."fulfilled" = TRUE
jbe@619 1898 ) AS "minus1_fulfilled_count",
jbe@619 1899 ( SELECT coalesce(sum("di"."weight"), 0)
jbe@619 1900 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
jbe@619 1901 ON "di"."snapshot_id" = "snapshot_id_v"
jbe@619 1902 AND "di"."issue_id" = "issue_id_v"
jbe@619 1903 AND "di"."member_id" = "opinion"."member_id"
jbe@619 1904 WHERE "opinion"."suggestion_id" = "suggestion"."id"
jbe@619 1905 AND "opinion"."degree" = 1
jbe@619 1906 AND "opinion"."fulfilled" = FALSE
jbe@619 1907 ) AS "plus1_unfulfilled_count",
jbe@619 1908 ( SELECT coalesce(sum("di"."weight"), 0)
jbe@619 1909 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
jbe@619 1910 ON "di"."snapshot_id" = "snapshot_id_v"
jbe@619 1911 AND "di"."issue_id" = "issue_id_v"
jbe@619 1912 AND "di"."member_id" = "opinion"."member_id"
jbe@619 1913 WHERE "opinion"."suggestion_id" = "suggestion"."id"
jbe@619 1914 AND "opinion"."degree" = 1
jbe@619 1915 AND "opinion"."fulfilled" = TRUE
jbe@619 1916 ) AS "plus1_fulfilled_count",
jbe@619 1917 ( SELECT coalesce(sum("di"."weight"), 0)
jbe@619 1918 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
jbe@619 1919 ON "di"."snapshot_id" = "snapshot_id_v"
jbe@619 1920 AND "di"."issue_id" = "issue_id_v"
jbe@619 1921 AND "di"."member_id" = "opinion"."member_id"
jbe@619 1922 WHERE "opinion"."suggestion_id" = "suggestion"."id"
jbe@619 1923 AND "opinion"."degree" = 2
jbe@619 1924 AND "opinion"."fulfilled" = FALSE
jbe@619 1925 ) AS "plus2_unfulfilled_count",
jbe@619 1926 ( SELECT coalesce(sum("di"."weight"), 0)
jbe@619 1927 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
jbe@619 1928 ON "di"."snapshot_id" = "snapshot_id_v"
jbe@619 1929 AND "di"."issue_id" = "issue_id_v"
jbe@619 1930 AND "di"."member_id" = "opinion"."member_id"
jbe@619 1931 WHERE "opinion"."suggestion_id" = "suggestion"."id"
jbe@619 1932 AND "opinion"."degree" = 2
jbe@619 1933 AND "opinion"."fulfilled" = TRUE
jbe@619 1934 ) AS "plus2_fulfilled_count"
jbe@619 1935 FROM "suggestion" JOIN "initiative"
jbe@619 1936 ON "suggestion"."initiative_id" = "initiative"."id"
jbe@619 1937 WHERE "initiative"."issue_id" = "issue_id_v";
jbe@619 1938 END LOOP;
jbe@619 1939 RETURN "snapshot_id_v";
jbe@619 1940 END;
jbe@619 1941 $$;
jbe@619 1942
jbe@619 1943 CREATE OR REPLACE FUNCTION "weight_of_added_vote_delegations"
jbe@619 1944 ( "issue_id_p" "issue"."id"%TYPE,
jbe@619 1945 "member_id_p" "member"."id"%TYPE,
jbe@619 1946 "delegate_member_ids_p" "delegating_voter"."delegate_member_ids"%TYPE )
jbe@619 1947 RETURNS "direct_voter"."weight"%TYPE
jbe@619 1948 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@619 1949 DECLARE
jbe@619 1950 "issue_delegation_row" "issue_delegation"%ROWTYPE;
jbe@619 1951 "delegate_member_ids_v" "delegating_voter"."delegate_member_ids"%TYPE;
jbe@619 1952 "weight_v" INT4;
jbe@619 1953 "sub_weight_v" INT4;
jbe@619 1954 BEGIN
jbe@619 1955 PERFORM "require_transaction_isolation"();
jbe@619 1956 "weight_v" := 0;
jbe@619 1957 FOR "issue_delegation_row" IN
jbe@619 1958 SELECT * FROM "issue_delegation"
jbe@619 1959 WHERE "trustee_id" = "member_id_p"
jbe@619 1960 AND "issue_id" = "issue_id_p"
jbe@619 1961 LOOP
jbe@619 1962 IF NOT EXISTS (
jbe@619 1963 SELECT NULL FROM "direct_voter"
jbe@619 1964 WHERE "member_id" = "issue_delegation_row"."truster_id"
jbe@619 1965 AND "issue_id" = "issue_id_p"
jbe@619 1966 ) AND NOT EXISTS (
jbe@619 1967 SELECT NULL FROM "delegating_voter"
jbe@619 1968 WHERE "member_id" = "issue_delegation_row"."truster_id"
jbe@619 1969 AND "issue_id" = "issue_id_p"
jbe@619 1970 ) THEN
jbe@619 1971 "delegate_member_ids_v" :=
jbe@619 1972 "member_id_p" || "delegate_member_ids_p";
jbe@619 1973 INSERT INTO "delegating_voter" (
jbe@619 1974 "issue_id",
jbe@619 1975 "member_id",
jbe@619 1976 "ownweight",
jbe@619 1977 "scope",
jbe@619 1978 "delegate_member_ids"
jbe@619 1979 ) VALUES (
jbe@619 1980 "issue_id_p",
jbe@619 1981 "issue_delegation_row"."truster_id",
jbe@619 1982 "issue_delegation_row"."weight",
jbe@619 1983 "issue_delegation_row"."scope",
jbe@619 1984 "delegate_member_ids_v"
jbe@619 1985 );
jbe@619 1986 "sub_weight_v" := "issue_delegation_row"."weight" +
jbe@619 1987 "weight_of_added_vote_delegations"(
jbe@619 1988 "issue_id_p",
jbe@619 1989 "issue_delegation_row"."truster_id",
jbe@619 1990 "delegate_member_ids_v"
jbe@619 1991 );
jbe@619 1992 UPDATE "delegating_voter"
jbe@619 1993 SET "weight" = "sub_weight_v"
jbe@619 1994 WHERE "issue_id" = "issue_id_p"
jbe@619 1995 AND "member_id" = "issue_delegation_row"."truster_id";
jbe@619 1996 "weight_v" := "weight_v" + "sub_weight_v";
jbe@619 1997 END IF;
jbe@619 1998 END LOOP;
jbe@619 1999 RETURN "weight_v";
jbe@619 2000 END;
jbe@619 2001 $$;
jbe@619 2002
jbe@619 2003 CREATE OR REPLACE FUNCTION "add_vote_delegations"
jbe@619 2004 ( "issue_id_p" "issue"."id"%TYPE )
jbe@619 2005 RETURNS VOID
jbe@619 2006 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@619 2007 DECLARE
jbe@619 2008 "member_id_v" "member"."id"%TYPE;
jbe@619 2009 BEGIN
jbe@619 2010 PERFORM "require_transaction_isolation"();
jbe@619 2011 FOR "member_id_v" IN
jbe@619 2012 SELECT "member_id" FROM "direct_voter"
jbe@619 2013 WHERE "issue_id" = "issue_id_p"
jbe@619 2014 LOOP
jbe@619 2015 UPDATE "direct_voter" SET
jbe@619 2016 "weight" = "ownweight" + "weight_of_added_vote_delegations"(
jbe@619 2017 "issue_id_p",
jbe@619 2018 "member_id_v",
jbe@619 2019 '{}'
jbe@619 2020 )
jbe@619 2021 WHERE "member_id" = "member_id_v"
jbe@619 2022 AND "issue_id" = "issue_id_p";
jbe@619 2023 END LOOP;
jbe@619 2024 RETURN;
jbe@619 2025 END;
jbe@619 2026 $$;
jbe@619 2027
jbe@619 2028 CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
jbe@619 2029 RETURNS VOID
jbe@619 2030 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@619 2031 DECLARE
jbe@619 2032 "area_id_v" "area"."id"%TYPE;
jbe@619 2033 "unit_id_v" "unit"."id"%TYPE;
jbe@619 2034 "member_id_v" "member"."id"%TYPE;
jbe@619 2035 BEGIN
jbe@619 2036 PERFORM "require_transaction_isolation"();
jbe@619 2037 SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";
jbe@619 2038 SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v";
jbe@619 2039 -- override protection triggers:
jbe@619 2040 INSERT INTO "temporary_transaction_data" ("key", "value")
jbe@619 2041 VALUES ('override_protection_triggers', TRUE::TEXT);
jbe@619 2042 -- delete timestamp of voting comment:
jbe@619 2043 UPDATE "direct_voter" SET "comment_changed" = NULL
jbe@619 2044 WHERE "issue_id" = "issue_id_p";
jbe@619 2045 -- delete delegating votes (in cases of manual reset of issue state):
jbe@619 2046 DELETE FROM "delegating_voter"
jbe@619 2047 WHERE "issue_id" = "issue_id_p";
jbe@619 2048 -- delete votes from non-privileged voters:
jbe@619 2049 DELETE FROM "direct_voter"
jbe@619 2050 USING (
jbe@619 2051 SELECT "direct_voter"."member_id"
jbe@619 2052 FROM "direct_voter"
jbe@619 2053 JOIN "member" ON "direct_voter"."member_id" = "member"."id"
jbe@619 2054 LEFT JOIN "privilege"
jbe@619 2055 ON "privilege"."unit_id" = "unit_id_v"
jbe@619 2056 AND "privilege"."member_id" = "direct_voter"."member_id"
jbe@619 2057 LEFT JOIN "issue_privilege"
jbe@619 2058 ON "issue_privilege"."issue_id" = "issue_id_p"
jbe@619 2059 AND "issue_privilege"."member_id" = "direct_voter"."member_id"
jbe@619 2060 WHERE "direct_voter"."issue_id" = "issue_id_p" AND (
jbe@619 2061 "member"."active" = FALSE OR
jbe@619 2062 COALESCE(
jbe@619 2063 "issue_privilege"."voting_right",
jbe@619 2064 "privilege"."voting_right",
jbe@619 2065 FALSE
jbe@619 2066 ) = FALSE
jbe@619 2067 )
jbe@619 2068 ) AS "subquery"
jbe@619 2069 WHERE "direct_voter"."issue_id" = "issue_id_p"
jbe@619 2070 AND "direct_voter"."member_id" = "subquery"."member_id";
jbe@619 2071 -- consider voting weight and delegations:
jbe@619 2072 UPDATE "direct_voter" SET "ownweight" = "privilege"."weight"
jbe@619 2073 FROM "privilege"
jbe@619 2074 WHERE "issue_id" = "issue_id_p"
jbe@619 2075 AND "privilege"."unit_id" = "unit_id_v"
jbe@619 2076 AND "privilege"."member_id" = "direct_voter"."member_id";
jbe@619 2077 UPDATE "direct_voter" SET "ownweight" = "issue_privilege"."weight"
jbe@619 2078 FROM "issue_privilege"
jbe@619 2079 WHERE "direct_voter"."issue_id" = "issue_id_p"
jbe@619 2080 AND "issue_privilege"."issue_id" = "issue_id_p"
jbe@619 2081 AND "issue_privilege"."member_id" = "direct_voter"."member_id";
jbe@619 2082 PERFORM "add_vote_delegations"("issue_id_p");
jbe@619 2083 -- mark first preferences:
jbe@619 2084 UPDATE "vote" SET "first_preference" = "subquery"."first_preference"
jbe@619 2085 FROM (
jbe@619 2086 SELECT
jbe@619 2087 "vote"."initiative_id",
jbe@619 2088 "vote"."member_id",
jbe@619 2089 CASE WHEN "vote"."grade" > 0 THEN
jbe@619 2090 CASE WHEN "vote"."grade" = max("agg"."grade") THEN TRUE ELSE FALSE END
jbe@619 2091 ELSE NULL
jbe@619 2092 END AS "first_preference"
jbe@619 2093 FROM "vote"
jbe@619 2094 JOIN "initiative" -- NOTE: due to missing index on issue_id
jbe@619 2095 ON "vote"."issue_id" = "initiative"."issue_id"
jbe@619 2096 JOIN "vote" AS "agg"
jbe@619 2097 ON "initiative"."id" = "agg"."initiative_id"
jbe@619 2098 AND "vote"."member_id" = "agg"."member_id"
jbe@619 2099 GROUP BY "vote"."initiative_id", "vote"."member_id", "vote"."grade"
jbe@619 2100 ) AS "subquery"
jbe@619 2101 WHERE "vote"."issue_id" = "issue_id_p"
jbe@619 2102 AND "vote"."initiative_id" = "subquery"."initiative_id"
jbe@619 2103 AND "vote"."member_id" = "subquery"."member_id";
jbe@619 2104 -- finish overriding protection triggers (avoids garbage):
jbe@619 2105 DELETE FROM "temporary_transaction_data"
jbe@619 2106 WHERE "key" = 'override_protection_triggers';
jbe@619 2107 -- materialize battle_view:
jbe@619 2108 -- NOTE: "closed" column of issue must be set at this point
jbe@619 2109 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
jbe@619 2110 INSERT INTO "battle" (
jbe@619 2111 "issue_id",
jbe@619 2112 "winning_initiative_id", "losing_initiative_id",
jbe@619 2113 "count"
jbe@619 2114 ) SELECT
jbe@619 2115 "issue_id",
jbe@619 2116 "winning_initiative_id", "losing_initiative_id",
jbe@619 2117 "count"
jbe@619 2118 FROM "battle_view" WHERE "issue_id" = "issue_id_p";
jbe@619 2119 -- set voter count:
jbe@619 2120 UPDATE "issue" SET
jbe@619 2121 "voter_count" = (
jbe@619 2122 SELECT coalesce(sum("weight"), 0)
jbe@619 2123 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
jbe@619 2124 )
jbe@619 2125 WHERE "id" = "issue_id_p";
jbe@619 2126 -- copy "positive_votes" and "negative_votes" from "battle" table:
jbe@619 2127 -- NOTE: "first_preference_votes" is set to a default of 0 at this step
jbe@619 2128 UPDATE "initiative" SET
jbe@619 2129 "first_preference_votes" = 0,
jbe@619 2130 "positive_votes" = "battle_win"."count",
jbe@619 2131 "negative_votes" = "battle_lose"."count"
jbe@619 2132 FROM "battle" AS "battle_win", "battle" AS "battle_lose"
jbe@619 2133 WHERE
jbe@619 2134 "battle_win"."issue_id" = "issue_id_p" AND
jbe@619 2135 "battle_win"."winning_initiative_id" = "initiative"."id" AND
jbe@619 2136 "battle_win"."losing_initiative_id" ISNULL AND
jbe@619 2137 "battle_lose"."issue_id" = "issue_id_p" AND
jbe@619 2138 "battle_lose"."losing_initiative_id" = "initiative"."id" AND
jbe@619 2139 "battle_lose"."winning_initiative_id" ISNULL;
jbe@619 2140 -- calculate "first_preference_votes":
jbe@619 2141 -- NOTE: will only set values not equal to zero
jbe@619 2142 UPDATE "initiative" SET "first_preference_votes" = "subquery"."sum"
jbe@619 2143 FROM (
jbe@619 2144 SELECT "vote"."initiative_id", sum("direct_voter"."weight")
jbe@619 2145 FROM "vote" JOIN "direct_voter"
jbe@619 2146 ON "vote"."issue_id" = "direct_voter"."issue_id"
jbe@619 2147 AND "vote"."member_id" = "direct_voter"."member_id"
jbe@619 2148 WHERE "vote"."first_preference"
jbe@619 2149 GROUP BY "vote"."initiative_id"
jbe@619 2150 ) AS "subquery"
jbe@619 2151 WHERE "initiative"."issue_id" = "issue_id_p"
jbe@619 2152 AND "initiative"."admitted"
jbe@619 2153 AND "initiative"."id" = "subquery"."initiative_id";
jbe@619 2154 END;
jbe@619 2155 $$;
jbe@619 2156
jbe@619 2157 COMMIT;

Impressum / About Us