liquid_feedback_core: 63092784fe9d update/core-update.v4.1.0-v4.2.1.sql

liquid_feedback_core

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

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

line source

1 CREATE OR REPLACE VIEW "liquid_feedback_version" AS

2 SELECT * FROM (VALUES ('4.2.0-incomplete-update', 4, 2, -1))

3 AS "subquery"("string", "major", "minor", "revision");

5 ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'posting_created';

7 BEGIN;

9 CREATE OR REPLACE VIEW "liquid_feedback_version" AS

10 SELECT * FROM (VALUES ('4.2.1', 4, 2, 1))

11 AS "subquery"("string", "major", "minor", "revision");

13 DROP VIEW "newsletter_to_send";

14 DROP VIEW "scheduled_notification_to_send";

15 DROP VIEW "member_contingent_left";

16 DROP VIEW "member_contingent";

17 DROP VIEW "expired_snapshot";

18 DROP VIEW "current_draft";

19 DROP VIEW "opening_draft";

20 DROP VIEW "area_with_unaccepted_issues";

21 DROP VIEW "member_to_notify";

22 DROP VIEW "member_eligible_to_be_notified";

24 DROP FUNCTION "text_search_query" (TEXT);

26 ALTER TABLE "system_setting" DROP COLUMN "snapshot_retention";

28 CREATE TABLE "file" (

29 "id" SERIAL8 PRIMARY KEY,

30 UNIQUE ("content_type", "hash"),

31 "content_type" TEXT NOT NULL,

32 "hash" TEXT NOT NULL,

33 "data" BYTEA NOT NULL,

34 "preview_content_type" TEXT,

35 "preview_data" BYTEA );

37 COMMENT ON TABLE "file" IS 'Table holding file contents for draft attachments';

39 COMMENT ON COLUMN "file"."content_type" IS 'Content type of "data"';

40 COMMENT ON COLUMN "file"."hash" IS 'Hash of "data" to avoid storing duplicates where content-type and data is identical';

41 COMMENT ON COLUMN "file"."data" IS 'Binary content';

42 COMMENT ON COLUMN "file"."preview_content_type" IS 'Content type of "preview_data"';

43 COMMENT ON COLUMN "file"."preview_data" IS 'Preview (e.g. preview image)';

45 ALTER TABLE "member" DROP COLUMN "text_search_data";

46 DROP TRIGGER "update_text_search_data" ON "member";

48 CREATE INDEX "member_useterms_member_id_contract_identifier" ON "member_useterms" ("member_id", "contract_identifier");

50 ALTER TABLE "member_profile" DROP COLUMN "text_search_data";

51 DROP TRIGGER "update_text_search_data" ON "member_profile";

53 ALTER TABLE "contact" ADD COLUMN "following" BOOLEAN NOT NULL DEFAULT TRUE;

55 COMMENT ON COLUMN "contact"."following" IS 'TRUE = actions of contact are shown in personal timeline';

57 ALTER TABLE "unit" DROP COLUMN "text_search_data";

58 DROP TRIGGER "update_text_search_data" ON "unit";

60 ALTER TABLE "area" DROP COLUMN "text_search_data";

61 DROP TRIGGER "update_text_search_data" ON "area";

63 DROP INDEX "issue_accepted_idx";

64 DROP INDEX "issue_half_frozen_idx";

65 DROP INDEX "issue_fully_frozen_idx";

66 ALTER INDEX "issue_created_idx_open" RENAME TO "issue_open_created_idx";

67 DROP INDEX "issue_closed_idx_canceled";

68 ALTER INDEX "issue_latest_snapshot_id" RENAME TO "issue_latest_snapshot_id_idx";

69 ALTER INDEX "issue_admission_snapshot_id" RENAME TO "issue_admission_snapshot_id_idx";

70 ALTER INDEX "issue_half_freeze_snapshot_id" RENAME TO "issue_half_freeze_snapshot_id_idx";

71 ALTER INDEX "issue_full_freeze_snapshot_id" RENAME TO "issue_full_freeze_snapshot_id_idx";

73 ALTER TABLE "initiative" ADD COLUMN "content" TEXT;

74 ALTER TABLE "initiative" DROP COLUMN "text_search_data";

75 ALTER TABLE "initiative" DROP COLUMN "draft_text_search_data";

76 DROP INDEX "initiative_revoked_idx";

77 DROP TRIGGER "update_text_search_data" ON "initiative";

79 COMMENT ON COLUMN "initiative"."content" IS 'Initiative text (automatically copied from most recent draft)';

81 ALTER TABLE "battle" DROP CONSTRAINT "initiative_ids_not_equal";

82 ALTER TABLE "battle" ADD CONSTRAINT "initiative_ids_not_equal" CHECK (

83 "winning_initiative_id" != "losing_initiative_id" AND

84 ("winning_initiative_id" NOTNULL OR "losing_initiative_id" NOTNULL) );

86 ALTER TABLE "draft" DROP COLUMN "text_search_data";

87 DROP TRIGGER "update_text_search_data" ON "draft";

89 CREATE TABLE "draft_attachment" (

90 "id" SERIAL8 PRIMARY KEY,

91 "draft_id" INT8 REFERENCES "draft" ("id") ON DELETE CASCADE ON UPDATE CASCADE,

92 "file_id" INT8 REFERENCES "file" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,

93 "title" TEXT,

94 "description" TEXT );

96 COMMENT ON TABLE "draft_attachment" IS 'Binary attachments for a draft (images, PDF file, etc.); Implicitly ordered through ''id'' column';

98 ALTER TABLE "suggestion" DROP COLUMN "text_search_data";

99 DROP TRIGGER "update_text_search_data" ON "suggestion";

100

101 ALTER TABLE "direct_voter" DROP COLUMN "text_search_data";

102 DROP TRIGGER "update_text_search_data" ON "direct_voter";

103

104 CREATE TABLE "posting" (

105 UNIQUE ("author_id", "id"), -- index needed for foreign-key on table "posting_lexeme"

106 "id" SERIAL8 PRIMARY KEY,

107 "author_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,

108 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),

109 "message" TEXT NOT NULL,

110 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,

111 "area_id" INT4,

112 FOREIGN KEY ("unit_id", "area_id") REFERENCES "area" ("unit_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,

113 "policy_id" INT4 REFERENCES "policy" ("id") ON DELETE CASCADE ON UPDATE CASCADE,

114 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,

115 FOREIGN KEY ("area_id", "issue_id") REFERENCES "issue" ("area_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,

116 FOREIGN KEY ("policy_id", "issue_id") REFERENCES "issue" ("policy_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,

117 "initiative_id" INT4,

118 "suggestion_id" INT8,

119 -- NOTE: no referential integrity for suggestions because those are

120 -- actually deleted

121 -- FOREIGN KEY ("initiative_id", "suggestion_id")

122 -- REFERENCES "suggestion" ("initiative_id", "id")

123 -- ON DELETE CASCADE ON UPDATE CASCADE,

124 CONSTRAINT "area_requires_unit" CHECK (

125 "area_id" ISNULL OR "unit_id" NOTNULL ),

126 CONSTRAINT "policy_set_when_issue_set" CHECK (

127 ("policy_id" NOTNULL) = ("issue_id" NOTNULL) ),

128 CONSTRAINT "issue_requires_area" CHECK (

129 "issue_id" ISNULL OR "area_id" NOTNULL ),

130 CONSTRAINT "initiative_requires_issue" CHECK (

131 "initiative_id" ISNULL OR "issue_id" NOTNULL ),

132 CONSTRAINT "suggestion_requires_initiative" CHECK (

133 "suggestion_id" ISNULL OR "initiative_id" NOTNULL ) );

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';

135

136 CREATE TABLE "posting_lexeme" (

137 PRIMARY KEY ("posting_id", "lexeme"),

138 FOREIGN KEY ("posting_id", "author_id") REFERENCES "posting" ("id", "author_id") ON DELETE CASCADE ON UPDATE CASCADE,

139 "posting_id" INT8,

140 "lexeme" TEXT,

141 "author_id" INT4 );

142

143 COMMENT ON TABLE "posting_lexeme" IS 'Helper table to allow searches for hashtags.';

144

145 ALTER TABLE "event" ADD COLUMN "posting_id" INT8 REFERENCES "posting" ("id") ON DELETE RESTRICT ON UPDATE CASCADE;

146 ALTER TABLE "event" DROP CONSTRAINT "constr_for_issue_state_changed";

147 ALTER TABLE "event" DROP CONSTRAINT "constr_for_initiative_creation_or_revocation_or_new_draft";

148 ALTER TABLE "event" DROP CONSTRAINT "constr_for_suggestion_creation";

149 ALTER TABLE "event" DROP CONSTRAINT "constr_for_suggestion_removal";

150 ALTER TABLE "event" DROP CONSTRAINT "constr_for_value_less_member_event";

151 ALTER TABLE "event" DROP CONSTRAINT "constr_for_member_active";

152 ALTER TABLE "event" DROP CONSTRAINT "constr_for_member_name_updated";

153 ALTER TABLE "event" DROP CONSTRAINT "constr_for_interest";

154 ALTER TABLE "event" DROP CONSTRAINT "constr_for_initiator";

155 ALTER TABLE "event" DROP CONSTRAINT "constr_for_support";

156 ALTER TABLE "event" DROP CONSTRAINT "constr_for_support_updated";

157 ALTER TABLE "event" DROP CONSTRAINT "constr_for_suggestion_rated";

158 ALTER TABLE "event" DROP CONSTRAINT "constr_for_delegation";

159 ALTER TABLE "event" DROP CONSTRAINT "constr_for_contact";

160 ALTER TABLE "event" ADD

161 CONSTRAINT "constr_for_issue_state_changed" CHECK (

162 "event" != 'issue_state_changed' OR (

163 "posting_id" ISNULL AND

164 "member_id" ISNULL AND

165 "other_member_id" ISNULL AND

166 "scope" ISNULL AND

167 "unit_id" NOTNULL AND

168 "area_id" NOTNULL AND

169 "policy_id" NOTNULL AND

170 "issue_id" NOTNULL AND

171 "state" NOTNULL AND

172 "initiative_id" ISNULL AND

173 "draft_id" ISNULL AND

174 "suggestion_id" ISNULL AND

175 "boolean_value" ISNULL AND

176 "numeric_value" ISNULL AND

177 "text_value" ISNULL AND

178 "old_text_value" ISNULL ));

179 ALTER TABLE "event" ADD

180 CONSTRAINT "constr_for_initiative_creation_or_revocation_or_new_draft" CHECK (

181 "event" NOT IN (

182 'initiative_created_in_new_issue',

183 'initiative_created_in_existing_issue',

184 'initiative_revoked',

185 'new_draft_created'

186 ) OR (

187 "posting_id" ISNULL AND

188 "member_id" NOTNULL AND

189 "other_member_id" ISNULL AND

190 "scope" ISNULL AND

191 "unit_id" NOTNULL AND

192 "area_id" NOTNULL AND

193 "policy_id" NOTNULL AND

194 "issue_id" NOTNULL AND

195 "state" NOTNULL AND

196 "initiative_id" NOTNULL AND

197 "draft_id" NOTNULL AND

198 "suggestion_id" ISNULL AND

199 "boolean_value" ISNULL AND

200 "numeric_value" ISNULL AND

201 "text_value" ISNULL AND

202 "old_text_value" ISNULL ));

203 ALTER TABLE "event" ADD

204 CONSTRAINT "constr_for_suggestion_creation" CHECK (

205 "event" != 'suggestion_created' OR (

206 "posting_id" ISNULL AND

207 "member_id" NOTNULL AND

208 "other_member_id" ISNULL AND

209 "scope" ISNULL AND

210 "unit_id" NOTNULL AND

211 "area_id" NOTNULL AND

212 "policy_id" NOTNULL AND

213 "issue_id" NOTNULL AND

214 "state" NOTNULL AND

215 "initiative_id" NOTNULL AND

216 "draft_id" ISNULL AND

217 "suggestion_id" NOTNULL AND

218 "boolean_value" ISNULL AND

219 "numeric_value" ISNULL AND

220 "text_value" ISNULL AND

221 "old_text_value" ISNULL ));

222 ALTER TABLE "event" ADD

223 CONSTRAINT "constr_for_suggestion_removal" CHECK (

224 "event" != 'suggestion_deleted' OR (

225 "posting_id" ISNULL AND

226 "member_id" ISNULL AND

227 "other_member_id" ISNULL AND

228 "scope" ISNULL AND

229 "unit_id" NOTNULL AND

230 "area_id" NOTNULL AND

231 "policy_id" NOTNULL AND

232 "issue_id" NOTNULL AND

233 "state" NOTNULL AND

234 "initiative_id" NOTNULL AND

235 "draft_id" ISNULL AND

236 "suggestion_id" NOTNULL AND

237 "boolean_value" ISNULL AND

238 "numeric_value" ISNULL AND

239 "text_value" ISNULL AND

240 "old_text_value" ISNULL ));

241 ALTER TABLE "event" ADD

242 CONSTRAINT "constr_for_value_less_member_event" CHECK (

243 "event" NOT IN (

244 'member_activated',

245 'member_deleted',

246 'member_profile_updated',

247 'member_image_updated'

248 ) OR (

249 "posting_id" ISNULL AND

250 "member_id" NOTNULL AND

251 "other_member_id" ISNULL AND

252 "scope" ISNULL AND

253 "unit_id" ISNULL AND

254 "area_id" ISNULL AND

255 "policy_id" ISNULL AND

256 "issue_id" ISNULL AND

257 "state" ISNULL AND

258 "initiative_id" ISNULL AND

259 "draft_id" ISNULL AND

260 "suggestion_id" ISNULL AND

261 "boolean_value" ISNULL AND

262 "numeric_value" ISNULL AND

263 "text_value" ISNULL AND

264 "old_text_value" ISNULL ));

265 ALTER TABLE "event" ADD

266 CONSTRAINT "constr_for_member_active" CHECK (

267 "event" != 'member_active' OR (

268 "posting_id" ISNULL AND

269 "member_id" NOTNULL AND

270 "other_member_id" ISNULL AND

271 "scope" ISNULL AND

272 "unit_id" ISNULL AND

273 "area_id" ISNULL AND

274 "policy_id" ISNULL AND

275 "issue_id" ISNULL AND

276 "state" ISNULL AND

277 "initiative_id" ISNULL AND

278 "draft_id" ISNULL AND

279 "suggestion_id" ISNULL AND

280 "boolean_value" NOTNULL AND

281 "numeric_value" ISNULL AND

282 "text_value" ISNULL AND

283 "old_text_value" ISNULL ));

284 ALTER TABLE "event" ADD

285 CONSTRAINT "constr_for_member_name_updated" CHECK (

286 "event" != 'member_name_updated' OR (

287 "posting_id" ISNULL AND

288 "member_id" NOTNULL AND

289 "other_member_id" ISNULL AND

290 "scope" ISNULL AND

291 "unit_id" ISNULL AND

292 "area_id" ISNULL AND

293 "policy_id" ISNULL AND

294 "issue_id" ISNULL AND

295 "state" ISNULL AND

296 "initiative_id" ISNULL AND

297 "draft_id" ISNULL AND

298 "suggestion_id" ISNULL AND

299 "boolean_value" ISNULL AND

300 "numeric_value" ISNULL AND

301 "text_value" NOTNULL AND

302 "old_text_value" NOTNULL ));

303 ALTER TABLE "event" ADD

304 CONSTRAINT "constr_for_interest" CHECK (

305 "event" != 'interest' OR (

306 "posting_id" ISNULL AND

307 "member_id" NOTNULL AND

308 "other_member_id" ISNULL AND

309 "scope" ISNULL AND

310 "unit_id" NOTNULL AND

311 "area_id" NOTNULL AND

312 "policy_id" NOTNULL AND

313 "issue_id" NOTNULL AND

314 "state" NOTNULL AND

315 "initiative_id" ISNULL AND

316 "draft_id" ISNULL AND

317 "suggestion_id" ISNULL AND

318 "boolean_value" NOTNULL AND

319 "numeric_value" ISNULL AND

320 "text_value" ISNULL AND

321 "old_text_value" ISNULL ));

322 ALTER TABLE "event" ADD

323 CONSTRAINT "constr_for_initiator" CHECK (

324 "event" != 'initiator' OR (

325 "posting_id" ISNULL AND

326 "member_id" NOTNULL AND

327 "other_member_id" ISNULL AND

328 "scope" ISNULL AND

329 "unit_id" NOTNULL AND

330 "area_id" NOTNULL AND

331 "policy_id" NOTNULL AND

332 "issue_id" NOTNULL AND

333 "state" NOTNULL AND

334 "initiative_id" NOTNULL AND

335 "draft_id" ISNULL AND

336 "suggestion_id" ISNULL AND

337 "boolean_value" NOTNULL AND

338 "numeric_value" ISNULL AND

339 "text_value" ISNULL AND

340 "old_text_value" ISNULL ));

341 ALTER TABLE "event" ADD

342 CONSTRAINT "constr_for_support" CHECK (

343 "event" != 'support' OR (

344 "posting_id" ISNULL AND

345 "member_id" NOTNULL AND

346 "other_member_id" ISNULL AND

347 "scope" ISNULL AND

348 "unit_id" NOTNULL AND

349 "area_id" NOTNULL AND

350 "policy_id" NOTNULL AND

351 "issue_id" NOTNULL AND

352 "state" NOTNULL AND

353 "initiative_id" NOTNULL AND

354 ("draft_id" NOTNULL) = ("boolean_value" = TRUE) AND

355 "suggestion_id" ISNULL AND

356 "boolean_value" NOTNULL AND

357 "numeric_value" ISNULL AND

358 "text_value" ISNULL AND

359 "old_text_value" ISNULL ));

360 ALTER TABLE "event" ADD

361 CONSTRAINT "constr_for_support_updated" CHECK (

362 "event" != 'support_updated' OR (

363 "posting_id" ISNULL AND

364 "member_id" NOTNULL AND

365 "other_member_id" ISNULL AND

366 "scope" ISNULL AND

367 "unit_id" NOTNULL AND

368 "area_id" NOTNULL AND

369 "policy_id" NOTNULL AND

370 "issue_id" NOTNULL AND

371 "state" NOTNULL AND

372 "initiative_id" NOTNULL AND

373 "draft_id" NOTNULL AND

374 "suggestion_id" ISNULL AND

375 "boolean_value" ISNULL AND

376 "numeric_value" ISNULL AND

377 "text_value" ISNULL AND

378 "old_text_value" ISNULL ));

379 ALTER TABLE "event" ADD

380 CONSTRAINT "constr_for_suggestion_rated" CHECK (

381 "event" != 'suggestion_rated' OR (

382 "posting_id" ISNULL AND

383 "member_id" NOTNULL AND

384 "other_member_id" ISNULL AND

385 "scope" ISNULL AND

386 "unit_id" NOTNULL AND

387 "area_id" NOTNULL AND

388 "policy_id" NOTNULL AND

389 "issue_id" NOTNULL AND

390 "state" NOTNULL AND

391 "initiative_id" NOTNULL AND

392 "draft_id" ISNULL AND

393 "suggestion_id" NOTNULL AND

394 ("boolean_value" NOTNULL) = ("numeric_value" != 0) AND

395 "numeric_value" NOTNULL AND

396 "numeric_value" IN (-2, -1, 0, 1, 2) AND

397 "text_value" ISNULL AND

398 "old_text_value" ISNULL ));

399 ALTER TABLE "event" ADD

400 CONSTRAINT "constr_for_delegation" CHECK (

401 "event" != 'delegation' OR (

402 "posting_id" ISNULL AND

403 "member_id" NOTNULL AND

404 (("other_member_id" ISNULL) OR ("boolean_value" = TRUE)) AND

405 "scope" NOTNULL AND

406 "unit_id" NOTNULL AND

407 ("area_id" NOTNULL) = ("scope" != 'unit'::"delegation_scope") AND

408 "policy_id" ISNULL AND

409 ("issue_id" NOTNULL) = ("scope" = 'issue'::"delegation_scope") AND

410 ("state" NOTNULL) = ("scope" = 'issue'::"delegation_scope") AND

411 "initiative_id" ISNULL AND

412 "draft_id" ISNULL AND

413 "suggestion_id" ISNULL AND

414 "boolean_value" NOTNULL AND

415 "numeric_value" ISNULL AND

416 "text_value" ISNULL AND

417 "old_text_value" ISNULL ));

418 ALTER TABLE "event" ADD

419 CONSTRAINT "constr_for_contact" CHECK (

420 "event" != 'contact' OR (

421 "posting_id" ISNULL AND

422 "member_id" NOTNULL AND

423 "other_member_id" NOTNULL AND

424 "scope" ISNULL AND

425 "unit_id" ISNULL AND

426 "area_id" ISNULL AND

427 "policy_id" ISNULL AND

428 "issue_id" ISNULL AND

429 "state" ISNULL AND

430 "initiative_id" ISNULL AND

431 "draft_id" ISNULL AND

432 "suggestion_id" ISNULL AND

433 "boolean_value" NOTNULL AND

434 "numeric_value" ISNULL AND

435 "text_value" ISNULL AND

436 "old_text_value" ISNULL ));

437 ALTER TABLE "event" ADD

438 CONSTRAINT "constr_for_posting_created" CHECK (

439 "event" != 'posting_created' OR (

440 "posting_id" NOTNULL AND

441 "member_id" NOTNULL AND

442 "other_member_id" ISNULL AND

443 "scope" ISNULL AND

444 "state" ISNULL AND

445 ("area_id" ISNULL OR "unit_id" NOTNULL) AND

446 ("policy_id" NOTNULL) = ("issue_id" NOTNULL) AND

447 ("issue_id" ISNULL OR "area_id" NOTNULL) AND

448 ("state" NOTNULL) = ("issue_id" NOTNULL) AND

449 ("initiative_id" ISNULL OR "issue_id" NOTNULL) AND

450 "draft_id" ISNULL AND

451 ("suggestion_id" ISNULL OR "initiative_id" NOTNULL) AND

452 "boolean_value" ISNULL AND

453 "numeric_value" ISNULL AND

454 "text_value" ISNULL AND

455 "old_text_value" ISNULL ));

456

457 CREATE OR REPLACE FUNCTION "highlight"

458 ( "body_p" TEXT,

459 "query_text_p" TEXT )

460 RETURNS TEXT

461 LANGUAGE 'plpgsql' IMMUTABLE AS $$

462 BEGIN

463 RETURN ts_headline(

464 replace(replace("body_p", e'\\', e'\\\\'), '*', e'\\*'),

465 "plainto_tsquery"("query_text_p"),

466 'StartSel=* StopSel=* HighlightAll=TRUE' );

467 END;

468 $$;

469

470 CREATE FUNCTION "to_tsvector"("member") RETURNS TSVECTOR

471 LANGUAGE SQL IMMUTABLE AS $$ SELECT to_tsvector(concat_ws(' ',

472 $1."name",

473 $1."identification"

474 )) $$;

475 CREATE INDEX "member_to_tsvector_idx" ON "member" USING gin

476 (("to_tsvector"("member".*)));

477

478 CREATE FUNCTION "to_tsvector"("member_profile") RETURNS TSVECTOR

479 LANGUAGE SQL IMMUTABLE AS $$ SELECT to_tsvector(concat_ws(' ',

480 $1."statement",

481 $1."profile_text_data"

482 )) $$;

483 CREATE INDEX "member_profile_to_tsvector_idx" ON "member_profile" USING gin

484 (("to_tsvector"("member_profile".*)));

485

486 CREATE FUNCTION "to_tsvector"("unit") RETURNS TSVECTOR

487 LANGUAGE SQL IMMUTABLE AS $$ SELECT to_tsvector(concat_ws(' ',

488 $1."name",

489 $1."description"

490 )) $$;

491 CREATE INDEX "unit_to_tsvector_idx" ON "unit" USING gin

492 (("to_tsvector"("unit".*)));

493

494 CREATE FUNCTION "to_tsvector"("area") RETURNS TSVECTOR

495 LANGUAGE SQL IMMUTABLE AS $$ SELECT to_tsvector(concat_ws(' ',

496 $1."name",

497 $1."description"

498 )) $$;

499 CREATE INDEX "area_to_tsvector_idx" ON "area" USING gin

500 (("to_tsvector"("area".*)));

501

502 CREATE FUNCTION "to_tsvector"("initiative") RETURNS TSVECTOR

503 LANGUAGE SQL IMMUTABLE AS $$ SELECT to_tsvector(concat_ws(' ',

504 $1."name",

505 $1."content"

506 )) $$;

507 CREATE INDEX "initiative_to_tsvector_idx" ON "initiative" USING gin

508 (("to_tsvector"("initiative".*)));

509

510 CREATE FUNCTION "to_tsvector"("draft") RETURNS TSVECTOR

511 LANGUAGE SQL IMMUTABLE AS $$ SELECT to_tsvector(concat_ws(' ',

512 $1."content"

513 )) $$;

514 CREATE INDEX "draft_to_tsvector_idx" ON "draft" USING gin

515 (("to_tsvector"("draft".*)));

516

517 CREATE FUNCTION "to_tsvector"("suggestion") RETURNS TSVECTOR

518 LANGUAGE SQL IMMUTABLE AS $$ SELECT to_tsvector(concat_ws(' ',

519 $1."name",

520 $1."content"

521 )) $$;

522 CREATE INDEX "suggestion_to_tsvector_idx" ON "suggestion" USING gin

523 (("to_tsvector"("suggestion".*)));

524

525 CREATE FUNCTION "to_tsvector"("direct_voter") RETURNS TSVECTOR

526 LANGUAGE SQL IMMUTABLE AS $$ SELECT to_tsvector(concat_ws(' ',

527 $1."comment"

528 )) $$;

529 CREATE INDEX "direct_voter_to_tsvector_idx" ON "direct_voter" USING gin

530 (("to_tsvector"("direct_voter".*)));

531

532 CREATE FUNCTION "update_posting_lexeme_trigger"()

533 RETURNS TRIGGER

534 LANGUAGE 'plpgsql' VOLATILE AS $$

535 DECLARE

536 "lexeme_v" TEXT;

537 BEGIN

538 IF TG_OP = 'DELETE' OR TG_OP = 'UPDATE' THEN

539 DELETE FROM "posting_lexeme" WHERE "posting_id" = OLD."id";

540 END IF;

541 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN

542 FOR "lexeme_v" IN

543 SELECT regexp_matches[1]

544 FROM regexp_matches(NEW."message", '#[^\s.,;:]+')

545 LOOP

546 INSERT INTO "posting_lexeme" ("posting_id", "author_id", "lexeme")

547 VALUES (

548 NEW."id",

549 NEW."author_id",

550 "lexeme_v" )

551 ON CONFLICT ("posting_id", "lexeme") DO NOTHING;

552 END LOOP;

553 END IF;

554 RETURN NULL;

555 END;

556 $$;

557

558 CREATE TRIGGER "update_posting_lexeme"

559 AFTER INSERT OR UPDATE OR DELETE ON "posting"

560 FOR EACH ROW EXECUTE PROCEDURE "update_posting_lexeme_trigger"();

561

562 COMMENT ON FUNCTION "update_posting_lexeme_trigger"() IS 'Implementation of trigger "update_posting_lexeme" on table "posting"';

563 COMMENT ON TRIGGER "update_posting_lexeme" ON "posting" IS 'Keeps table "posting_lexeme" up to date';

564

565 CREATE FUNCTION "write_event_posting_trigger"()

566 RETURNS TRIGGER

567 LANGUAGE 'plpgsql' VOLATILE AS $$

568 BEGIN

569 INSERT INTO "event" (

570 "event", "posting_id", "member_id",

571 "unit_id", "area_id", "policy_id",

572 "issue_id", "initiative_id", "suggestion_id"

573 ) VALUES (

574 'posting_created', NEW."id", NEW."author_id",

575 NEW."unit_id", NEW."area_id", NEW."policy_id",

576 NEW."issue_id", NEW."initiative_id", NEW."suggestion_id"

577 );

578 RETURN NULL;

579 END;

580 $$;

581

582 CREATE TRIGGER "write_event_posting"

583 AFTER INSERT ON "posting" FOR EACH ROW EXECUTE PROCEDURE

584 "write_event_posting_trigger"();

585

586 COMMENT ON FUNCTION "write_event_posting_trigger"() IS 'Implementation of trigger "write_event_posting" on table "posting"';

587 COMMENT ON TRIGGER "write_event_posting" ON "posting" IS 'Create entry in "event" table when creating a new posting';

588

589 CREATE FUNCTION "file_requires_reference_trigger"()

590 RETURNS TRIGGER

591 LANGUAGE 'plpgsql' VOLATILE AS $$

592 BEGIN

593 IF NOT EXISTS (

594 SELECT NULL FROM "draft_attachment" WHERE "file_id" = NEW."id"

595 ) THEN

596 RAISE EXCEPTION 'Cannot create an unreferenced file.' USING

597 ERRCODE = 'integrity_constraint_violation',

598 HINT = 'Create file and its reference in another table within the same transaction.';

599 END IF;

600 RETURN NULL;

601 END;

602 $$;

603

604 CREATE CONSTRAINT TRIGGER "file_requires_reference"

605 AFTER INSERT OR UPDATE ON "file" DEFERRABLE INITIALLY DEFERRED

606 FOR EACH ROW EXECUTE PROCEDURE

607 "file_requires_reference_trigger"();

608

609 COMMENT ON FUNCTION "file_requires_reference_trigger"() IS 'Implementation of trigger "file_requires_reference" on table "file"';

610 COMMENT ON TRIGGER "file_requires_reference" ON "file" IS 'Ensure that files are always referenced';

611

612 CREATE FUNCTION "last_reference_deletes_file_trigger"()

613 RETURNS TRIGGER

614 LANGUAGE 'plpgsql' VOLATILE AS $$

615 DECLARE

616 "reference_lost" BOOLEAN;

617 BEGIN

618 IF TG_OP = 'DELETE' THEN

619 "reference_lost" := TRUE;

620 ELSE

621 "reference_lost" := NEW."file_id" != OLD."file_id";

622 END IF;

623 IF

624 "reference_lost" AND NOT EXISTS (

625 SELECT NULL FROM "draft_attachment" WHERE "file_id" = OLD."file_id"

626 )

627 THEN

628 DELETE FROM "file" WHERE "id" = OLD."file_id";

629 END IF;

630 RETURN NULL;

631 END;

632 $$;

633

634 CREATE CONSTRAINT TRIGGER "last_reference_deletes_file"

635 AFTER UPDATE OR DELETE ON "draft_attachment" DEFERRABLE INITIALLY DEFERRED

636 FOR EACH ROW EXECUTE PROCEDURE

637 "last_reference_deletes_file_trigger"();

638

639 COMMENT ON FUNCTION "last_reference_deletes_file_trigger"() IS 'Implementation of trigger "last_reference_deletes_file" on table "draft_attachment"';

640 COMMENT ON TRIGGER "last_reference_deletes_file" ON "draft_attachment" IS 'Removing the last reference to a file deletes the file';

641

642 CREATE OR REPLACE FUNCTION "copy_current_draft_data"

643 ("initiative_id_p" "initiative"."id"%TYPE )

644 RETURNS VOID

645 LANGUAGE 'plpgsql' VOLATILE AS $$

646 BEGIN

647 PERFORM NULL FROM "initiative" WHERE "id" = "initiative_id_p"

648 FOR UPDATE;

649 UPDATE "initiative" SET

650 "location" = "draft"."location",

651 "content" = "draft"."content"

652 FROM "current_draft" AS "draft"

653 WHERE "initiative"."id" = "initiative_id_p"

654 AND "draft"."initiative_id" = "initiative_id_p";

655 END;

656 $$;

657

658 CREATE VIEW "follower" AS

659 SELECT

660 "id" AS "follower_id",

661 ( SELECT ARRAY["member"."id"] || array_agg("contact"."other_member_id")

662 FROM "contact"

663 WHERE "contact"."member_id" = "member"."id" AND "contact"."following" )

664 AS "following_ids"

665 FROM "member";

666

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';

668

669 CREATE OR REPLACE FUNCTION "check_issue"

670 ( "issue_id_p" "issue"."id"%TYPE,

671 "persist" "check_issue_persistence" )

672 RETURNS "check_issue_persistence"

673 LANGUAGE 'plpgsql' VOLATILE AS $$

674 DECLARE

675 "issue_row" "issue"%ROWTYPE;

676 "last_calculated_v" "snapshot"."calculated"%TYPE;

677 "policy_row" "policy"%ROWTYPE;

678 "initiative_row" "initiative"%ROWTYPE;

679 "state_v" "issue_state";

680 BEGIN

681 PERFORM "require_transaction_isolation"();

682 IF "persist" ISNULL THEN

683 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"

684 FOR UPDATE;

685 SELECT "calculated" INTO "last_calculated_v"

686 FROM "snapshot" JOIN "snapshot_issue"

687 ON "snapshot"."id" = "snapshot_issue"."snapshot_id"

688 WHERE "snapshot_issue"."issue_id" = "issue_id_p"

689 ORDER BY "snapshot"."id" DESC;

690 IF "issue_row"."closed" NOTNULL THEN

691 RETURN NULL;

692 END IF;

693 "persist"."state" := "issue_row"."state";

694 IF

695 ( "issue_row"."state" = 'admission' AND "last_calculated_v" >=

696 "issue_row"."created" + "issue_row"."max_admission_time" ) OR

697 ( "issue_row"."state" = 'discussion' AND now() >=

698 "issue_row"."accepted" + "issue_row"."discussion_time" ) OR

699 ( "issue_row"."state" = 'verification' AND now() >=

700 "issue_row"."half_frozen" + "issue_row"."verification_time" ) OR

701 ( "issue_row"."state" = 'voting' AND now() >=

702 "issue_row"."fully_frozen" + "issue_row"."voting_time" )

703 THEN

704 "persist"."phase_finished" := TRUE;

705 ELSE

706 "persist"."phase_finished" := FALSE;

707 END IF;

708 IF

709 NOT EXISTS (

710 -- all initiatives are revoked

711 SELECT NULL FROM "initiative"

712 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL

713 ) AND (

714 -- and issue has not been accepted yet

715 "persist"."state" = 'admission' OR

716 -- or verification time has elapsed

717 ( "persist"."state" = 'verification' AND

718 "persist"."phase_finished" ) OR

719 -- or no initiatives have been revoked lately

720 NOT EXISTS (

721 SELECT NULL FROM "initiative"

722 WHERE "issue_id" = "issue_id_p"

723 AND now() < "revoked" + "issue_row"."verification_time"

724 )

725 )

726 THEN

727 "persist"."issue_revoked" := TRUE;

728 ELSE

729 "persist"."issue_revoked" := FALSE;

730 END IF;

731 IF "persist"."phase_finished" OR "persist"."issue_revoked" THEN

732 UPDATE "issue" SET "phase_finished" = now()

733 WHERE "id" = "issue_row"."id";

734 RETURN "persist";

735 ELSIF

736 "persist"."state" IN ('admission', 'discussion', 'verification')

737 THEN

738 RETURN "persist";

739 ELSE

740 RETURN NULL;

741 END IF;

742 END IF;

743 IF

744 "persist"."state" IN ('admission', 'discussion', 'verification') AND

745 coalesce("persist"."snapshot_created", FALSE) = FALSE

746 THEN

747 IF "persist"."state" != 'admission' THEN

748 PERFORM "take_snapshot"("issue_id_p");

749 PERFORM "finish_snapshot"("issue_id_p");

750 ELSE

751 UPDATE "issue" SET "issue_quorum" = "issue_quorum"."issue_quorum"

752 FROM "issue_quorum"

753 WHERE "id" = "issue_id_p"

754 AND "issue_quorum"."issue_id" = "issue_id_p";

755 END IF;

756 "persist"."snapshot_created" = TRUE;

757 IF "persist"."phase_finished" THEN

758 IF "persist"."state" = 'admission' THEN

759 UPDATE "issue" SET "admission_snapshot_id" = "latest_snapshot_id"

760 WHERE "id" = "issue_id_p";

761 ELSIF "persist"."state" = 'discussion' THEN

762 UPDATE "issue" SET "half_freeze_snapshot_id" = "latest_snapshot_id"

763 WHERE "id" = "issue_id_p";

764 ELSIF "persist"."state" = 'verification' THEN

765 UPDATE "issue" SET "full_freeze_snapshot_id" = "latest_snapshot_id"

766 WHERE "id" = "issue_id_p";

767 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";

768 FOR "initiative_row" IN

769 SELECT * FROM "initiative"

770 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL

771 FOR UPDATE

772 LOOP

773 IF

774 "initiative_row"."polling" OR

775 "initiative_row"."satisfied_supporter_count" >=

776 "issue_row"."initiative_quorum"

777 THEN

778 UPDATE "initiative" SET "admitted" = TRUE

779 WHERE "id" = "initiative_row"."id";

780 ELSE

781 UPDATE "initiative" SET "admitted" = FALSE

782 WHERE "id" = "initiative_row"."id";

783 END IF;

784 END LOOP;

785 END IF;

786 END IF;

787 RETURN "persist";

788 END IF;

789 IF

790 "persist"."state" IN ('admission', 'discussion', 'verification') AND

791 coalesce("persist"."harmonic_weights_set", FALSE) = FALSE

792 THEN

793 PERFORM "set_harmonic_initiative_weights"("issue_id_p");

794 "persist"."harmonic_weights_set" = TRUE;

795 IF

796 "persist"."phase_finished" OR

797 "persist"."issue_revoked" OR

798 "persist"."state" = 'admission'

799 THEN

800 RETURN "persist";

801 ELSE

802 RETURN NULL;

803 END IF;

804 END IF;

805 IF "persist"."issue_revoked" THEN

806 IF "persist"."state" = 'admission' THEN

807 "state_v" := 'canceled_revoked_before_accepted';

808 ELSIF "persist"."state" = 'discussion' THEN

809 "state_v" := 'canceled_after_revocation_during_discussion';

810 ELSIF "persist"."state" = 'verification' THEN

811 "state_v" := 'canceled_after_revocation_during_verification';

812 END IF;

813 UPDATE "issue" SET

814 "state" = "state_v",

815 "closed" = "phase_finished",

816 "phase_finished" = NULL

817 WHERE "id" = "issue_id_p";

818 RETURN NULL;

819 END IF;

820 IF "persist"."state" = 'admission' THEN

821 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"

822 FOR UPDATE;

823 IF "issue_row"."phase_finished" NOTNULL THEN

824 UPDATE "issue" SET

825 "state" = 'canceled_issue_not_accepted',

826 "closed" = "phase_finished",

827 "phase_finished" = NULL

828 WHERE "id" = "issue_id_p";

829 END IF;

830 RETURN NULL;

831 END IF;

832 IF "persist"."phase_finished" THEN

833 IF "persist"."state" = 'discussion' THEN

834 UPDATE "issue" SET

835 "state" = 'verification',

836 "half_frozen" = "phase_finished",

837 "phase_finished" = NULL

838 WHERE "id" = "issue_id_p";

839 RETURN NULL;

840 END IF;

841 IF "persist"."state" = 'verification' THEN

842 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"

843 FOR UPDATE;

844 SELECT * INTO "policy_row" FROM "policy"

845 WHERE "id" = "issue_row"."policy_id";

846 IF EXISTS (

847 SELECT NULL FROM "initiative"

848 WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE

849 ) THEN

850 UPDATE "issue" SET

851 "state" = 'voting',

852 "fully_frozen" = "phase_finished",

853 "phase_finished" = NULL

854 WHERE "id" = "issue_id_p";

855 ELSE

856 UPDATE "issue" SET

857 "state" = 'canceled_no_initiative_admitted',

858 "fully_frozen" = "phase_finished",

859 "closed" = "phase_finished",

860 "phase_finished" = NULL

861 WHERE "id" = "issue_id_p";

862 -- NOTE: The following DELETE statements have effect only when

863 -- issue state has been manipulated

864 DELETE FROM "direct_voter" WHERE "issue_id" = "issue_id_p";

865 DELETE FROM "delegating_voter" WHERE "issue_id" = "issue_id_p";

866 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";

867 END IF;

868 RETURN NULL;

869 END IF;

870 IF "persist"."state" = 'voting' THEN

871 IF coalesce("persist"."closed_voting", FALSE) = FALSE THEN

872 PERFORM "close_voting"("issue_id_p");

873 "persist"."closed_voting" = TRUE;

874 RETURN "persist";

875 END IF;

876 PERFORM "calculate_ranks"("issue_id_p");

877 RETURN NULL;

878 END IF;

879 END IF;

880 RAISE WARNING 'should not happen';

881 RETURN NULL;

882 END;

883 $$;

884

885 CREATE OR REPLACE FUNCTION "check_everything"()

886 RETURNS VOID

887 LANGUAGE 'plpgsql' VOLATILE AS $$

888 DECLARE

889 "area_id_v" "area"."id"%TYPE;

890 "snapshot_id_v" "snapshot"."id"%TYPE;

891 "issue_id_v" "issue"."id"%TYPE;

892 "persist_v" "check_issue_persistence";

893 BEGIN

894 RAISE WARNING 'Function "check_everything" should only be used for development and debugging purposes';

895 DELETE FROM "expired_session";

896 DELETE FROM "expired_token";

897 DELETE FROM "unused_snapshot";

898 PERFORM "check_activity"();

899 PERFORM "calculate_member_counts"();

900 FOR "area_id_v" IN SELECT "id" FROM "area_with_unaccepted_issues" LOOP

901 SELECT "take_snapshot"(NULL, "area_id_v") INTO "snapshot_id_v";

902 PERFORM "finish_snapshot"("issue_id") FROM "snapshot_issue"

903 WHERE "snapshot_id" = "snapshot_id_v";

904 LOOP

905 EXIT WHEN "issue_admission"("area_id_v") = FALSE;

906 END LOOP;

907 END LOOP;

908 FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP

909 "persist_v" := NULL;

910 LOOP

911 "persist_v" := "check_issue"("issue_id_v", "persist_v");

912 EXIT WHEN "persist_v" ISNULL;

913 END LOOP;

914 END LOOP;

915 DELETE FROM "unused_snapshot";

916 RETURN;

917 END;

918 $$;

919

920 CREATE OR REPLACE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)

921 RETURNS VOID

922 LANGUAGE 'plpgsql' VOLATILE AS $$

923 BEGIN

924 UPDATE "member" SET

925 "last_login" = NULL,

926 "last_delegation_check" = NULL,

927 "login" = NULL,

928 "password" = NULL,

929 "authority" = NULL,

930 "authority_uid" = NULL,

931 "authority_login" = NULL,

932 "deleted" = coalesce("deleted", now()),

933 "locked" = TRUE,

934 "active" = FALSE,

935 "notify_email" = NULL,

936 "notify_email_unconfirmed" = NULL,

937 "notify_email_secret" = NULL,

938 "notify_email_secret_expiry" = NULL,

939 "notify_email_lock_expiry" = NULL,

940 "disable_notifications" = TRUE,

941 "notification_counter" = DEFAULT,

942 "notification_sample_size" = 0,

943 "notification_dow" = NULL,

944 "notification_hour" = NULL,

945 "notification_sent" = NULL,

946 "login_recovery_expiry" = NULL,

947 "password_reset_secret" = NULL,

948 "password_reset_secret_expiry" = NULL,

949 "location" = NULL

950 WHERE "id" = "member_id_p";

951 DELETE FROM "member_settings" WHERE "member_id" = "member_id_p";

952 DELETE FROM "member_profile" WHERE "member_id" = "member_id_p";

953 DELETE FROM "rendered_member_statement" WHERE "member_id" = "member_id_p";

954 DELETE FROM "member_image" WHERE "member_id" = "member_id_p";

955 DELETE FROM "contact" WHERE "member_id" = "member_id_p";

956 DELETE FROM "ignored_member" WHERE "member_id" = "member_id_p";

957 DELETE FROM "session" WHERE "member_id" = "member_id_p";

958 DELETE FROM "member_application" WHERE "member_id" = "member_id_p";

959 DELETE FROM "token" WHERE "member_id" = "member_id_p";

960 DELETE FROM "subscription" WHERE "member_id" = "member_id_p";

961 DELETE FROM "ignored_area" WHERE "member_id" = "member_id_p";

962 DELETE FROM "ignored_initiative" WHERE "member_id" = "member_id_p";

963 DELETE FROM "delegation" WHERE "truster_id" = "member_id_p";

964 DELETE FROM "non_voter" WHERE "member_id" = "member_id_p";

965 DELETE FROM "direct_voter" USING "issue"

966 WHERE "direct_voter"."issue_id" = "issue"."id"

967 AND "issue"."closed" ISNULL

968 AND "member_id" = "member_id_p";

969 DELETE FROM "notification_initiative_sent" WHERE "member_id" = "member_id_p";

970 RETURN;

971 END;

972 $$;

973

974 CREATE OR REPLACE FUNCTION "delete_private_data"()

975 RETURNS VOID

976 LANGUAGE 'plpgsql' VOLATILE AS $$

977 BEGIN

978 DELETE FROM "temporary_transaction_data";

979 DELETE FROM "temporary_suggestion_counts";

980 DELETE FROM "member" WHERE "activated" ISNULL;

981 UPDATE "member" SET

982 "invite_code" = NULL,

983 "invite_code_expiry" = NULL,

984 "admin_comment" = NULL,

985 "last_login" = NULL,

986 "last_delegation_check" = NULL,

987 "login" = NULL,

988 "password" = NULL,

989 "authority" = NULL,

990 "authority_uid" = NULL,

991 "authority_login" = NULL,

992 "lang" = NULL,

993 "notify_email" = NULL,

994 "notify_email_unconfirmed" = NULL,

995 "notify_email_secret" = NULL,

996 "notify_email_secret_expiry" = NULL,

997 "notify_email_lock_expiry" = NULL,

998 "disable_notifications" = TRUE,

999 "notification_counter" = DEFAULT,

1000 "notification_sample_size" = 0,

1001 "notification_dow" = NULL,

1002 "notification_hour" = NULL,

1003 "notification_sent" = NULL,

1004 "login_recovery_expiry" = NULL,

1005 "password_reset_secret" = NULL,

1006 "password_reset_secret_expiry" = NULL,

1007 "location" = NULL;

1008 DELETE FROM "verification";

1009 DELETE FROM "member_settings";

1010 DELETE FROM "member_useterms";

1011 DELETE FROM "member_profile";

1012 DELETE FROM "rendered_member_statement";

1013 DELETE FROM "member_image";

1014 DELETE FROM "contact";

1015 DELETE FROM "ignored_member";

1016 DELETE FROM "session";

1017 DELETE FROM "system_application";

1018 DELETE FROM "system_application_redirect_uri";

1019 DELETE FROM "dynamic_application_scope";

1020 DELETE FROM "member_application";

1021 DELETE FROM "token";

1022 DELETE FROM "subscription";

1023 DELETE FROM "ignored_area";

1024 DELETE FROM "ignored_initiative";

1025 DELETE FROM "non_voter";

1026 DELETE FROM "direct_voter" USING "issue"

1027 WHERE "direct_voter"."issue_id" = "issue"."id"

1028 AND "issue"."closed" ISNULL;

1029 DELETE FROM "event_processed";

1030 DELETE FROM "notification_initiative_sent";

1031 DELETE FROM "newsletter";

1032 RETURN;

1033 END;

1034 $$;

1035

1036 CREATE VIEW "member_eligible_to_be_notified" AS

1037 SELECT * FROM "member"

1038 WHERE "activated" NOTNULL AND "locked" = FALSE;

1039

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")';

1041

1042 CREATE VIEW "member_to_notify" AS

1043 SELECT * FROM "member_eligible_to_be_notified"

1044 WHERE "disable_notifications" = FALSE;

1045

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)';

1047

1048 CREATE VIEW "area_with_unaccepted_issues" AS

1049 SELECT DISTINCT ON ("area"."id") "area".*

1050 FROM "area" JOIN "issue" ON "area"."id" = "issue"."area_id"

1051 WHERE "issue"."state" = 'admission';

1052

1053 COMMENT ON VIEW "area_with_unaccepted_issues" IS 'All areas with unaccepted open issues (needed for issue admission system)';

1054

1055 CREATE VIEW "opening_draft" AS

1056 SELECT DISTINCT ON ("initiative_id") * FROM "draft"

1057 ORDER BY "initiative_id", "id";

1058

1059 COMMENT ON VIEW "opening_draft" IS 'First drafts of all initiatives';

1060

1061 CREATE VIEW "current_draft" AS

1062 SELECT DISTINCT ON ("initiative_id") * FROM "draft"

1063 ORDER BY "initiative_id", "id" DESC;

1064

1065 COMMENT ON VIEW "current_draft" IS 'All latest drafts for each initiative';

1066

1067 CREATE VIEW "member_contingent" AS

1068 SELECT

1069 "member"."id" AS "member_id",

1070 "contingent"."polling",

1071 "contingent"."time_frame",

1072 CASE WHEN "contingent"."text_entry_limit" NOTNULL THEN

1073 (

1074 SELECT count(1) FROM "draft"

1075 JOIN "initiative" ON "initiative"."id" = "draft"."initiative_id"

1076 WHERE "draft"."author_id" = "member"."id"

1077 AND "initiative"."polling" = "contingent"."polling"

1078 AND "draft"."created" > now() - "contingent"."time_frame"

1079 ) + (

1080 SELECT count(1) FROM "suggestion"

1081 JOIN "initiative" ON "initiative"."id" = "suggestion"."initiative_id"

1082 WHERE "suggestion"."author_id" = "member"."id"

1083 AND "contingent"."polling" = FALSE

1084 AND "suggestion"."created" > now() - "contingent"."time_frame"

1085 )

1086 ELSE NULL END AS "text_entry_count",

1087 "contingent"."text_entry_limit",

1088 CASE WHEN "contingent"."initiative_limit" NOTNULL THEN (

1089 SELECT count(1) FROM "opening_draft" AS "draft"

1090 JOIN "initiative" ON "initiative"."id" = "draft"."initiative_id"

1091 WHERE "draft"."author_id" = "member"."id"

1092 AND "initiative"."polling" = "contingent"."polling"

1093 AND "draft"."created" > now() - "contingent"."time_frame"

1094 ) ELSE NULL END AS "initiative_count",

1095 "contingent"."initiative_limit"

1096 FROM "member" CROSS JOIN "contingent";

1097

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.';

1099

1100 COMMENT ON COLUMN "member_contingent"."text_entry_count" IS 'Only calculated when "text_entry_limit" is not null in the same row';

1101 COMMENT ON COLUMN "member_contingent"."initiative_count" IS 'Only calculated when "initiative_limit" is not null in the same row';

1102

1103 CREATE VIEW "member_contingent_left" AS

1104 SELECT

1105 "member_id",

1106 "polling",

1107 max("text_entry_limit" - "text_entry_count") AS "text_entries_left",

1108 max("initiative_limit" - "initiative_count") AS "initiatives_left"

1109 FROM "member_contingent" GROUP BY "member_id", "polling";

1110

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.';

1112

1113 CREATE VIEW "scheduled_notification_to_send" AS

1114 SELECT * FROM (

1115 SELECT

1116 "id" AS "recipient_id",

1117 now() - CASE WHEN "notification_dow" ISNULL THEN

1118 ( "notification_sent"::DATE + CASE

1119 WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour"

1120 THEN 0 ELSE 1 END

1121 )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour"

1122 ELSE

1123 ( "notification_sent"::DATE +

1124 ( 7 + "notification_dow" -

1125 EXTRACT(DOW FROM

1126 ( "notification_sent"::DATE + CASE

1127 WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour"

1128 THEN 0 ELSE 1 END

1129 )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour"

1130 )::INTEGER

1131 ) % 7 +

1132 CASE

1133 WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour"

1134 THEN 0 ELSE 1

1135 END

1136 )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour"

1137 END AS "pending"

1138 FROM (

1139 SELECT

1140 "id",

1141 COALESCE("notification_sent", "activated") AS "notification_sent",

1142 "notification_dow",

1143 "notification_hour"

1144 FROM "member_to_notify"

1145 WHERE "notification_hour" NOTNULL

1146 ) AS "subquery1"

1147 ) AS "subquery2"

1148 WHERE "pending" > '0'::INTERVAL;

1149

1150 COMMENT ON VIEW "scheduled_notification_to_send" IS 'Set of members where a scheduled notification mail is pending';

1151

1152 COMMENT ON COLUMN "scheduled_notification_to_send"."recipient_id" IS '"id" of the member who needs to receive a notification mail';

1153 COMMENT ON COLUMN "scheduled_notification_to_send"."pending" IS 'Duration for which the notification mail has already been pending';

1154

1155 CREATE VIEW "newsletter_to_send" AS

1156 SELECT

1157 "member"."id" AS "recipient_id",

1158 "newsletter"."id" AS "newsletter_id",

1159 "newsletter"."published"

1160 FROM "newsletter" CROSS JOIN "member_eligible_to_be_notified" AS "member"

1161 LEFT JOIN "privilege" ON

1162 "privilege"."member_id" = "member"."id" AND

1163 "privilege"."unit_id" = "newsletter"."unit_id" AND

1164 "privilege"."voting_right" = TRUE

1165 LEFT JOIN "subscription" ON

1166 "subscription"."member_id" = "member"."id" AND

1167 "subscription"."unit_id" = "newsletter"."unit_id"

1168 WHERE "newsletter"."published" <= now()

1169 AND "newsletter"."sent" ISNULL

1170 AND (

1171 "member"."disable_notifications" = FALSE OR

1172 "newsletter"."include_all_members" = TRUE )

1173 AND (

1174 "newsletter"."unit_id" ISNULL OR

1175 "privilege"."member_id" NOTNULL OR

1176 "subscription"."member_id" NOTNULL );

1177

1178 COMMENT ON VIEW "newsletter_to_send" IS 'List of "newsletter_id"s for each member that are due to be sent out';

1179

1180 COMMENT ON COLUMN "newsletter"."published" IS 'Timestamp when the newsletter was supposed to be sent out (can be used for ordering)';

1181

1182 SELECT "copy_current_draft_data" ("id") FROM "initiative";

1183

1184 COMMIT;

1185 BEGIN;

1186

1187 CREATE OR REPLACE VIEW "liquid_feedback_version" AS

1188 SELECT * FROM (VALUES ('4.2.1', 4, 2, 1))

1189 AS "subquery"("string", "major", "minor", "revision");

1190

1191 ALTER TABLE "unit" ADD COLUMN "attr" JSONB NOT NULL DEFAULT '{}' CHECK (jsonb_typeof("attr") = 'object');

1192 COMMENT ON COLUMN "unit"."attr" IS 'Opaque data structure to store any extended attributes used by frontend or middleware';

1193

1194 ALTER TABLE "unit" ADD COLUMN "member_weight" INT4;

1195 COMMENT ON COLUMN "unit"."member_weight" IS 'Sum of active members'' voting weight';

1196

1197 ALTER TABLE "snapshot_population" ADD COLUMN "weight" INT4 NOT NULL DEFAULT 1;

1198 ALTER TABLE "snapshot_population" ALTER COLUMN "weight" DROP DEFAULT;

1199

1200 ALTER TABLE "privilege" ADD COLUMN "weight" INT4 NOT NULL DEFAULT 1 CHECK ("weight" >= 0);

1201 COMMENT ON COLUMN "privilege"."weight" IS 'Voting weight of member in unit';

1202

1203 CREATE TABLE "issue_privilege" (

1204 PRIMARY KEY ("issue_id", "member_id"),

1205 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,

1206 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,

1207 "initiative_right" BOOLEAN,

1208 "voting_right" BOOLEAN,

1209 "polling_right" BOOLEAN,

1210 "weight" INT4 CHECK ("weight" >= 0) );

1211 CREATE INDEX "issue_privilege_idx" ON "issue_privilege" ("member_id");

1212 COMMENT ON TABLE "issue_privilege" IS 'Override of "privilege" table for rights of members in certain issues';

1213

1214 ALTER TABLE "direct_interest_snapshot" ADD COLUMN "ownweight" INT4 NOT NULL DEFAULT 1;

1215 ALTER TABLE "direct_interest_snapshot" ALTER COLUMN "ownweight" DROP DEFAULT;

1216 COMMENT ON COLUMN "direct_interest_snapshot"."ownweight" IS 'Own voting weight of member, disregading delegations';

1217 COMMENT ON COLUMN "direct_interest_snapshot"."weight" IS 'Voting weight of member according to own weight and "delegating_interest_snapshot"';

1218

1219 ALTER TABLE "delegating_interest_snapshot" ADD COLUMN "ownweight" INT4 NOT NULL DEFAULT 1;

1220 ALTER TABLE "delegating_interest_snapshot" ALTER COLUMN "ownweight" DROP DEFAULT;

1221 COMMENT ON COLUMN "delegating_interest_snapshot"."ownweight" IS 'Own voting weight of member, disregading delegations';

1222 COMMENT ON COLUMN "delegating_interest_snapshot"."weight" IS 'Intermediate voting weight considering incoming delegations';

1223

1224 ALTER TABLE "direct_voter" ADD COLUMN "ownweight" INT4 DEFAULT 1;

1225 ALTER TABLE "direct_voter" ALTER COLUMN "ownweight" DROP DEFAULT;

1226 COMMENT ON COLUMN "direct_voter"."ownweight" IS 'Own voting weight of member, disregarding delegations';

1227 COMMENT ON COLUMN "direct_voter"."weight" IS 'Voting weight of member according to own weight and "delegating_interest_snapshot"';

1228

1229 ALTER TABLE "delegating_voter" ADD COLUMN "ownweight" INT4 NOT NULL DEFAULT 1;

1230 ALTER TABLE "delegating_voter" ALTER COLUMN "ownweight" DROP DEFAULT;

1231 COMMENT ON COLUMN "delegating_voter"."ownweight" IS 'Own voting weight of member, disregarding delegations';

1232 COMMENT ON COLUMN "delegating_voter"."weight" IS 'Intermediate voting weight considering incoming delegations';

1233

1234 ALTER TABLE "posting" ADD FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id");

1235

1236 DROP VIEW "issue_delegation";

1237 CREATE VIEW "issue_delegation" AS

1238 SELECT DISTINCT ON ("issue"."id", "delegation"."truster_id")

1239 "issue"."id" AS "issue_id",

1240 "delegation"."id",

1241 "delegation"."truster_id",

1242 "delegation"."trustee_id",

1243 COALESCE("issue_privilege"."weight", "privilege"."weight") AS "weight",

1244 "delegation"."scope"

1245 FROM "issue"

1246 JOIN "area"

1247 ON "area"."id" = "issue"."area_id"

1248 JOIN "delegation"

1249 ON "delegation"."unit_id" = "area"."unit_id"

1250 OR "delegation"."area_id" = "area"."id"

1251 OR "delegation"."issue_id" = "issue"."id"

1252 JOIN "member"

1253 ON "delegation"."truster_id" = "member"."id"

1254 LEFT JOIN "privilege"

1255 ON "area"."unit_id" = "privilege"."unit_id"

1256 AND "delegation"."truster_id" = "privilege"."member_id"

1257 LEFT JOIN "issue_privilege"

1258 ON "issue"."id" = "issue_privilege"."issue_id"

1259 AND "delegation"."truster_id" = "issue_privilege"."member_id"

1260 WHERE "member"."active"

1261 AND COALESCE("issue_privilege"."voting_right", "privilege"."voting_right")

1262 ORDER BY

1263 "issue"."id",

1264 "delegation"."truster_id",

1265 "delegation"."scope" DESC;

1266 COMMENT ON VIEW "issue_delegation" IS 'Issue delegations where trusters are active and have voting right';

1267

1268 CREATE OR REPLACE VIEW "unit_member" AS

1269 SELECT

1270 "privilege"."unit_id" AS "unit_id",

1271 "member"."id" AS "member_id",

1272 "privilege"."weight"

1273 FROM "privilege" JOIN "member" ON "member"."id" = "privilege"."member_id"

1274 WHERE "privilege"."voting_right" AND "member"."active";

1275

1276 CREATE OR REPLACE VIEW "unit_member_count" AS

1277 SELECT

1278 "unit"."id" AS "unit_id",

1279 count("unit_member"."member_id") AS "member_count",

1280 sum("unit_member"."weight") AS "member_weight"

1281 FROM "unit" LEFT JOIN "unit_member"

1282 ON "unit"."id" = "unit_member"."unit_id"

1283 GROUP BY "unit"."id";

1284

1285 CREATE OR REPLACE VIEW "event_for_notification" AS

1286 SELECT

1287 "member"."id" AS "recipient_id",

1288 "event".*

1289 FROM "member" CROSS JOIN "event"

1290 JOIN "issue" ON "issue"."id" = "event"."issue_id"

1291 JOIN "area" ON "area"."id" = "issue"."area_id"

1292 LEFT JOIN "privilege" ON

1293 "privilege"."member_id" = "member"."id" AND

1294 "privilege"."unit_id" = "area"."unit_id"

1295 LEFT JOIN "issue_privilege" ON

1296 "issue_privilege"."member_id" = "member"."id" AND

1297 "issue_privilege"."issue_id" = "event"."issue_id"

1298 LEFT JOIN "subscription" ON

1299 "subscription"."member_id" = "member"."id" AND

1300 "subscription"."unit_id" = "area"."unit_id"

1301 LEFT JOIN "ignored_area" ON

1302 "ignored_area"."member_id" = "member"."id" AND

1303 "ignored_area"."area_id" = "issue"."area_id"

1304 LEFT JOIN "interest" ON

1305 "interest"."member_id" = "member"."id" AND

1306 "interest"."issue_id" = "event"."issue_id"

1307 LEFT JOIN "supporter" ON

1308 "supporter"."member_id" = "member"."id" AND

1309 "supporter"."initiative_id" = "event"."initiative_id"

1310 WHERE (

1311 COALESCE("issue_privilege"."voting_right", "privilege"."voting_right") OR

1312 "subscription"."member_id" NOTNULL

1313 ) AND ("ignored_area"."member_id" ISNULL OR "interest"."member_id" NOTNULL)

1314 AND (

1315 "event"."event" = 'issue_state_changed'::"event_type" OR

1316 ( "event"."event" = 'initiative_revoked'::"event_type" AND

1317 "supporter"."member_id" NOTNULL ) );

1318

1319 CREATE OR REPLACE FUNCTION "featured_initiative"

1320 ( "recipient_id_p" "member"."id"%TYPE,

1321 "area_id_p" "area"."id"%TYPE )

1322 RETURNS SETOF "initiative"."id"%TYPE

1323 LANGUAGE 'plpgsql' STABLE AS $$

1324 DECLARE

1325 "counter_v" "member"."notification_counter"%TYPE;

1326 "sample_size_v" "member"."notification_sample_size"%TYPE;

1327 "initiative_id_ary" INT4[]; --"initiative"."id"%TYPE[]

1328 "match_v" BOOLEAN;

1329 "member_id_v" "member"."id"%TYPE;

1330 "seed_v" TEXT;

1331 "initiative_id_v" "initiative"."id"%TYPE;

1332 BEGIN

1333 SELECT "notification_counter", "notification_sample_size"

1334 INTO "counter_v", "sample_size_v"

1335 FROM "member" WHERE "id" = "recipient_id_p";

1336 IF COALESCE("sample_size_v" <= 0, TRUE) THEN

1337 RETURN;

1338 END IF;

1339 "initiative_id_ary" := '{}';

1340 LOOP

1341 "match_v" := FALSE;

1342 FOR "member_id_v", "seed_v" IN

1343 SELECT * FROM (

1344 SELECT DISTINCT

1345 "supporter"."member_id",

1346 md5(

1347 "recipient_id_p" || '-' ||

1348 "counter_v" || '-' ||

1349 "area_id_p" || '-' ||

1350 "supporter"."member_id"

1351 ) AS "seed"

1352 FROM "supporter"

1353 JOIN "initiative" ON "initiative"."id" = "supporter"."initiative_id"

1354 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"

1355 WHERE "supporter"."member_id" != "recipient_id_p"

1356 AND "issue"."area_id" = "area_id_p"

1357 AND "issue"."state" IN ('admission', 'discussion', 'verification')

1358 ) AS "subquery"

1359 ORDER BY "seed"

1360 LOOP

1361 SELECT "initiative"."id" INTO "initiative_id_v"

1362 FROM "initiative"

1363 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"

1364 JOIN "area" ON "area"."id" = "issue"."area_id"

1365 JOIN "supporter" ON "supporter"."initiative_id" = "initiative"."id"

1366 LEFT JOIN "supporter" AS "self_support" ON

1367 "self_support"."initiative_id" = "initiative"."id" AND

1368 "self_support"."member_id" = "recipient_id_p"

1369 LEFT JOIN "privilege" ON

1370 "privilege"."member_id" = "recipient_id_p" AND

1371 "privilege"."unit_id" = "area"."unit_id"

1372 LEFT JOIN "issue_privilege" ON

1373 "issue_privilege"."member_id" = "recipient_id_p" AND

1374 "issue_privilege"."issue_id" = "initiative"."issue_id"

1375 LEFT JOIN "subscription" ON

1376 "subscription"."member_id" = "recipient_id_p" AND

1377 "subscription"."unit_id" = "area"."unit_id"

1378 LEFT JOIN "ignored_initiative" ON

1379 "ignored_initiative"."member_id" = "recipient_id_p" AND

1380 "ignored_initiative"."initiative_id" = "initiative"."id"

1381 WHERE "supporter"."member_id" = "member_id_v"

1382 AND "issue"."area_id" = "area_id_p"

1383 AND "issue"."state" IN ('admission', 'discussion', 'verification')

1384 AND "initiative"."revoked" ISNULL

1385 AND "self_support"."member_id" ISNULL

1386 AND NOT "initiative_id_ary" @> ARRAY["initiative"."id"]

1387 AND (

1388 COALESCE(

1389 "issue_privilege"."voting_right", "privilege"."voting_right"

1390 ) OR "subscription"."member_id" NOTNULL )

1391 AND "ignored_initiative"."member_id" ISNULL

1392 AND NOT EXISTS (

1393 SELECT NULL FROM "draft"

1394 JOIN "ignored_member" ON

1395 "ignored_member"."member_id" = "recipient_id_p" AND

1396 "ignored_member"."other_member_id" = "draft"."author_id"

1397 WHERE "draft"."initiative_id" = "initiative"."id"

1398 )

1399 ORDER BY md5("seed_v" || '-' || "initiative"."id")

1400 LIMIT 1;

1401 IF FOUND THEN

1402 "match_v" := TRUE;

1403 RETURN NEXT "initiative_id_v";

1404 IF array_length("initiative_id_ary", 1) + 1 >= "sample_size_v" THEN

1405 RETURN;

1406 END IF;

1407 "initiative_id_ary" := "initiative_id_ary" || "initiative_id_v";

1408 END IF;

1409 END LOOP;

1410 EXIT WHEN NOT "match_v";

1411 END LOOP;

1412 RETURN;

1413 END;

1414 $$;

1415

1416 CREATE OR REPLACE FUNCTION "delegation_chain"

1417 ( "member_id_p" "member"."id"%TYPE,

1418 "unit_id_p" "unit"."id"%TYPE,

1419 "area_id_p" "area"."id"%TYPE,

1420 "issue_id_p" "issue"."id"%TYPE,

1421 "simulate_trustee_id_p" "member"."id"%TYPE DEFAULT NULL,

1422 "simulate_default_p" BOOLEAN DEFAULT FALSE )

1423 RETURNS SETOF "delegation_chain_row"

1424 LANGUAGE 'plpgsql' STABLE AS $$

1425 DECLARE

1426 "scope_v" "delegation_scope";

1427 "unit_id_v" "unit"."id"%TYPE;

1428 "area_id_v" "area"."id"%TYPE;

1429 "issue_row" "issue"%ROWTYPE;

1430 "visited_member_ids" INT4[]; -- "member"."id"%TYPE[]

1431 "loop_member_id_v" "member"."id"%TYPE;

1432 "output_row" "delegation_chain_row";

1433 "output_rows" "delegation_chain_row"[];

1434 "simulate_v" BOOLEAN;

1435 "simulate_here_v" BOOLEAN;

1436 "delegation_row" "delegation"%ROWTYPE;

1437 "row_count" INT4;

1438 "i" INT4;

1439 "loop_v" BOOLEAN;

1440 BEGIN

1441 IF "simulate_trustee_id_p" NOTNULL AND "simulate_default_p" THEN

1442 RAISE EXCEPTION 'Both "simulate_trustee_id_p" is set, and "simulate_default_p" is true';

1443 END IF;

1444 IF "simulate_trustee_id_p" NOTNULL OR "simulate_default_p" THEN

1445 "simulate_v" := TRUE;

1446 ELSE

1447 "simulate_v" := FALSE;

1448 END IF;

1449 IF

1450 "unit_id_p" NOTNULL AND

1451 "area_id_p" ISNULL AND

1452 "issue_id_p" ISNULL

1453 THEN

1454 "scope_v" := 'unit';

1455 "unit_id_v" := "unit_id_p";

1456 ELSIF

1457 "unit_id_p" ISNULL AND

1458 "area_id_p" NOTNULL AND

1459 "issue_id_p" ISNULL

1460 THEN

1461 "scope_v" := 'area';

1462 "area_id_v" := "area_id_p";

1463 SELECT "unit_id" INTO "unit_id_v"

1464 FROM "area" WHERE "id" = "area_id_v";

1465 ELSIF

1466 "unit_id_p" ISNULL AND

1467 "area_id_p" ISNULL AND

1468 "issue_id_p" NOTNULL

1469 THEN

1470 SELECT INTO "issue_row" * FROM "issue" WHERE "id" = "issue_id_p";

1471 IF "issue_row"."id" ISNULL THEN

1472 RETURN;

1473 END IF;

1474 IF "issue_row"."closed" NOTNULL THEN

1475 IF "simulate_v" THEN

1476 RAISE EXCEPTION 'Tried to simulate delegation chain for closed issue.';

1477 END IF;

1478 FOR "output_row" IN

1479 SELECT * FROM

1480 "delegation_chain_for_closed_issue"("member_id_p", "issue_id_p")

1481 LOOP

1482 RETURN NEXT "output_row";

1483 END LOOP;

1484 RETURN;

1485 END IF;

1486 "scope_v" := 'issue';

1487 SELECT "area_id" INTO "area_id_v"

1488 FROM "issue" WHERE "id" = "issue_id_p";

1489 SELECT "unit_id" INTO "unit_id_v"

1490 FROM "area" WHERE "id" = "area_id_v";

1491 ELSE

1492 RAISE EXCEPTION 'Exactly one of unit_id_p, area_id_p, or issue_id_p must be NOTNULL.';

1493 END IF;

1494 "visited_member_ids" := '{}';

1495 "loop_member_id_v" := NULL;

1496 "output_rows" := '{}';

1497 "output_row"."index" := 0;

1498 "output_row"."member_id" := "member_id_p";

1499 "output_row"."member_valid" := TRUE;

1500 "output_row"."participation" := FALSE;

1501 "output_row"."overridden" := FALSE;

1502 "output_row"."disabled_out" := FALSE;

1503 "output_row"."scope_out" := NULL;

1504 LOOP

1505 IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN

1506 "loop_member_id_v" := "output_row"."member_id";

1507 ELSE

1508 "visited_member_ids" :=

1509 "visited_member_ids" || "output_row"."member_id";

1510 END IF;

1511 IF "output_row"."participation" ISNULL THEN

1512 "output_row"."overridden" := NULL;

1513 ELSIF "output_row"."participation" THEN

1514 "output_row"."overridden" := TRUE;

1515 END IF;

1516 "output_row"."scope_in" := "output_row"."scope_out";

1517 "output_row"."member_valid" := EXISTS (

1518 SELECT NULL FROM "member"

1519 LEFT JOIN "privilege"

1520 ON "privilege"."member_id" = "member"."id"

1521 AND "privilege"."unit_id" = "unit_id_v"

1522 LEFT JOIN "issue_privilege"

1523 ON "issue_privilege"."member_id" = "member"."id"

1524 AND "issue_privilege"."issue_id" = "issue_id_p"

1525 WHERE "id" = "output_row"."member_id"

1526 AND "member"."active"

1527 AND COALESCE(

1528 "issue_privilege"."voting_right", "privilege"."voting_right")

1529 );

1530 "simulate_here_v" := (

1531 "simulate_v" AND

1532 "output_row"."member_id" = "member_id_p"

1533 );

1534 "delegation_row" := ROW(NULL);

1535 IF "output_row"."member_valid" OR "simulate_here_v" THEN

1536 IF "scope_v" = 'unit' THEN

1537 IF NOT "simulate_here_v" THEN

1538 SELECT * INTO "delegation_row" FROM "delegation"

1539 WHERE "truster_id" = "output_row"."member_id"

1540 AND "unit_id" = "unit_id_v";

1541 END IF;

1542 ELSIF "scope_v" = 'area' THEN

1543 IF "simulate_here_v" THEN

1544 IF "simulate_trustee_id_p" ISNULL THEN

1545 SELECT * INTO "delegation_row" FROM "delegation"

1546 WHERE "truster_id" = "output_row"."member_id"

1547 AND "unit_id" = "unit_id_v";

1548 END IF;

1549 ELSE

1550 SELECT * INTO "delegation_row" FROM "delegation"

1551 WHERE "truster_id" = "output_row"."member_id"

1552 AND (

1553 "unit_id" = "unit_id_v" OR

1554 "area_id" = "area_id_v"

1555 )

1556 ORDER BY "scope" DESC;

1557 END IF;

1558 ELSIF "scope_v" = 'issue' THEN

1559 IF "issue_row"."fully_frozen" ISNULL THEN

1560 "output_row"."participation" := EXISTS (

1561 SELECT NULL FROM "interest"

1562 WHERE "issue_id" = "issue_id_p"

1563 AND "member_id" = "output_row"."member_id"

1564 );

1565 ELSE

1566 IF "output_row"."member_id" = "member_id_p" THEN

1567 "output_row"."participation" := EXISTS (

1568 SELECT NULL FROM "direct_voter"

1569 WHERE "issue_id" = "issue_id_p"

1570 AND "member_id" = "output_row"."member_id"

1571 );

1572 ELSE

1573 "output_row"."participation" := NULL;

1574 END IF;

1575 END IF;

1576 IF "simulate_here_v" THEN

1577 IF "simulate_trustee_id_p" ISNULL THEN

1578 SELECT * INTO "delegation_row" FROM "delegation"

1579 WHERE "truster_id" = "output_row"."member_id"

1580 AND (

1581 "unit_id" = "unit_id_v" OR

1582 "area_id" = "area_id_v"

1583 )

1584 ORDER BY "scope" DESC;

1585 END IF;

1586 ELSE

1587 SELECT * INTO "delegation_row" FROM "delegation"

1588 WHERE "truster_id" = "output_row"."member_id"

1589 AND (

1590 "unit_id" = "unit_id_v" OR

1591 "area_id" = "area_id_v" OR

1592 "issue_id" = "issue_id_p"

1593 )

1594 ORDER BY "scope" DESC;

1595 END IF;

1596 END IF;

1597 ELSE

1598 "output_row"."participation" := FALSE;

1599 END IF;

1600 IF "simulate_here_v" AND "simulate_trustee_id_p" NOTNULL THEN

1601 "output_row"."scope_out" := "scope_v";

1602 "output_rows" := "output_rows" || "output_row";

1603 "output_row"."member_id" := "simulate_trustee_id_p";

1604 ELSIF "delegation_row"."trustee_id" NOTNULL THEN

1605 "output_row"."scope_out" := "delegation_row"."scope";

1606 "output_rows" := "output_rows" || "output_row";

1607 "output_row"."member_id" := "delegation_row"."trustee_id";

1608 ELSIF "delegation_row"."scope" NOTNULL THEN

1609 "output_row"."scope_out" := "delegation_row"."scope";

1610 "output_row"."disabled_out" := TRUE;

1611 "output_rows" := "output_rows" || "output_row";

1612 EXIT;

1613 ELSE

1614 "output_row"."scope_out" := NULL;

1615 "output_rows" := "output_rows" || "output_row";

1616 EXIT;

1617 END IF;

1618 EXIT WHEN "loop_member_id_v" NOTNULL;

1619 "output_row"."index" := "output_row"."index" + 1;

1620 END LOOP;

1621 "row_count" := array_upper("output_rows", 1);

1622 "i" := 1;

1623 "loop_v" := FALSE;

1624 LOOP

1625 "output_row" := "output_rows"["i"];

1626 EXIT WHEN "output_row" ISNULL; -- NOTE: ISNULL and NOT ... NOTNULL produce different results!

1627 IF "loop_v" THEN

1628 IF "i" + 1 = "row_count" THEN

1629 "output_row"."loop" := 'last';

1630 ELSIF "i" = "row_count" THEN

1631 "output_row"."loop" := 'repetition';

1632 ELSE

1633 "output_row"."loop" := 'intermediate';

1634 END IF;

1635 ELSIF "output_row"."member_id" = "loop_member_id_v" THEN

1636 "output_row"."loop" := 'first';

1637 "loop_v" := TRUE;

1638 END IF;

1639 IF "scope_v" = 'unit' THEN

1640 "output_row"."participation" := NULL;

1641 END IF;

1642 RETURN NEXT "output_row";

1643 "i" := "i" + 1;

1644 END LOOP;

1645 RETURN;

1646 END;

1647 $$;

1648

1649 CREATE OR REPLACE FUNCTION "calculate_member_counts"()

1650 RETURNS VOID

1651 LANGUAGE 'plpgsql' VOLATILE AS $$

1652 BEGIN

1653 PERFORM "require_transaction_isolation"();

1654 DELETE FROM "member_count";

1655 INSERT INTO "member_count" ("total_count")

1656 SELECT "total_count" FROM "member_count_view";

1657 UPDATE "unit" SET

1658 "member_count" = "view"."member_count",

1659 "member_weight" = "view"."member_weight"

1660 FROM "unit_member_count" AS "view"

1661 WHERE "view"."unit_id" = "unit"."id";

1662 RETURN;

1663 END;

1664 $$;

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"';

1666

1667 CREATE OR REPLACE FUNCTION "weight_of_added_delegations_for_snapshot"

1668 ( "snapshot_id_p" "snapshot"."id"%TYPE,

1669 "issue_id_p" "issue"."id"%TYPE,

1670 "member_id_p" "member"."id"%TYPE,

1671 "delegate_member_ids_p" "delegating_interest_snapshot"."delegate_member_ids"%TYPE )

1672 RETURNS "direct_interest_snapshot"."weight"%TYPE

1673 LANGUAGE 'plpgsql' VOLATILE AS $$

1674 DECLARE

1675 "issue_delegation_row" "issue_delegation"%ROWTYPE;

1676 "delegate_member_ids_v" "delegating_interest_snapshot"."delegate_member_ids"%TYPE;

1677 "weight_v" INT4;

1678 "sub_weight_v" INT4;

1679 BEGIN

1680 PERFORM "require_transaction_isolation"();

1681 "weight_v" := 0;

1682 FOR "issue_delegation_row" IN

1683 SELECT * FROM "issue_delegation"

1684 WHERE "trustee_id" = "member_id_p"

1685 AND "issue_id" = "issue_id_p"

1686 LOOP

1687 IF NOT EXISTS (

1688 SELECT NULL FROM "direct_interest_snapshot"

1689 WHERE "snapshot_id" = "snapshot_id_p"

1690 AND "issue_id" = "issue_id_p"

1691 AND "member_id" = "issue_delegation_row"."truster_id"

1692 ) AND NOT EXISTS (

1693 SELECT NULL FROM "delegating_interest_snapshot"

1694 WHERE "snapshot_id" = "snapshot_id_p"

1695 AND "issue_id" = "issue_id_p"

1696 AND "member_id" = "issue_delegation_row"."truster_id"

1697 ) THEN

1698 "delegate_member_ids_v" :=

1699 "member_id_p" || "delegate_member_ids_p";

1700 INSERT INTO "delegating_interest_snapshot" (

1701 "snapshot_id",

1702 "issue_id",

1703 "member_id",

1704 "ownweight",

1705 "scope",

1706 "delegate_member_ids"

1707 ) VALUES (

1708 "snapshot_id_p",

1709 "issue_id_p",

1710 "issue_delegation_row"."truster_id",

1711 "issue_delegation_row"."weight",

1712 "issue_delegation_row"."scope",

1713 "delegate_member_ids_v"

1714 );

1715 "sub_weight_v" := "issue_delegation_row"."weight" +

1716 "weight_of_added_delegations_for_snapshot"(

1717 "snapshot_id_p",

1718 "issue_id_p",

1719 "issue_delegation_row"."truster_id",

1720 "delegate_member_ids_v"

1721 );

1722 UPDATE "delegating_interest_snapshot"

1723 SET "weight" = "sub_weight_v"

1724 WHERE "snapshot_id" = "snapshot_id_p"

1725 AND "issue_id" = "issue_id_p"

1726 AND "member_id" = "issue_delegation_row"."truster_id";

1727 "weight_v" := "weight_v" + "sub_weight_v";

1728 END IF;

1729 END LOOP;

1730 RETURN "weight_v";

1731 END;

1732 $$;

1733

1734 CREATE OR REPLACE FUNCTION "take_snapshot"

1735 ( "issue_id_p" "issue"."id"%TYPE,

1736 "area_id_p" "area"."id"%TYPE = NULL )

1737 RETURNS "snapshot"."id"%TYPE

1738 LANGUAGE 'plpgsql' VOLATILE AS $$

1739 DECLARE

1740 "area_id_v" "area"."id"%TYPE;

1741 "unit_id_v" "unit"."id"%TYPE;

1742 "snapshot_id_v" "snapshot"."id"%TYPE;

1743 "issue_id_v" "issue"."id"%TYPE;

1744 "member_id_v" "member"."id"%TYPE;

1745 BEGIN

1746 IF "issue_id_p" NOTNULL AND "area_id_p" NOTNULL THEN

1747 RAISE EXCEPTION 'One of "issue_id_p" and "area_id_p" must be NULL';

1748 END IF;

1749 PERFORM "require_transaction_isolation"();

1750 IF "issue_id_p" ISNULL THEN

1751 "area_id_v" := "area_id_p";

1752 ELSE

1753 SELECT "area_id" INTO "area_id_v"

1754 FROM "issue" WHERE "id" = "issue_id_p";

1755 END IF;

1756 SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v";

1757 INSERT INTO "snapshot" ("area_id", "issue_id")

1758 VALUES ("area_id_v", "issue_id_p")

1759 RETURNING "id" INTO "snapshot_id_v";

1760 INSERT INTO "snapshot_population" ("snapshot_id", "member_id", "weight")

1761 SELECT

1762 "snapshot_id_v",

1763 "member"."id",

1764 COALESCE("issue_privilege"."weight", "privilege"."weight")

1765 FROM "member"

1766 LEFT JOIN "privilege"

1767 ON "privilege"."unit_id" = "unit_id_v"

1768 AND "privilege"."member_id" = "member"."id"

1769 LEFT JOIN "issue_privilege"

1770 ON "issue_privilege"."issue_id" = "issue_id_p"

1771 AND "issue_privilege"."member_id" = "member"."id"

1772 WHERE "member"."active" AND COALESCE(

1773 "issue_privilege"."voting_right", "privilege"."voting_right");

1774 UPDATE "snapshot" SET

1775 "population" = (

1776 SELECT sum("weight") FROM "snapshot_population"

1777 WHERE "snapshot_id" = "snapshot_id_v"

1778 ) WHERE "id" = "snapshot_id_v";

1779 FOR "issue_id_v" IN

1780 SELECT "id" FROM "issue"

1781 WHERE CASE WHEN "issue_id_p" ISNULL THEN

1782 "area_id" = "area_id_p" AND

1783 "state" = 'admission'

1784 ELSE

1785 "id" = "issue_id_p"

1786 END

1787 LOOP

1788 INSERT INTO "snapshot_issue" ("snapshot_id", "issue_id")

1789 VALUES ("snapshot_id_v", "issue_id_v");

1790 INSERT INTO "direct_interest_snapshot"

1791 ("snapshot_id", "issue_id", "member_id", "ownweight")

1792 SELECT

1793 "snapshot_id_v" AS "snapshot_id",

1794 "issue_id_v" AS "issue_id",

1795 "member"."id" AS "member_id",

1796 COALESCE(

1797 "issue_privilege"."weight", "privilege"."weight"

1798 ) AS "ownweight"

1799 FROM "issue"

1800 JOIN "area" ON "issue"."area_id" = "area"."id"

1801 JOIN "interest" ON "issue"."id" = "interest"."issue_id"

1802 JOIN "member" ON "interest"."member_id" = "member"."id"

1803 LEFT JOIN "privilege"

1804 ON "privilege"."unit_id" = "area"."unit_id"

1805 AND "privilege"."member_id" = "member"."id"

1806 LEFT JOIN "issue_privilege"

1807 ON "issue_privilege"."issue_id" = "issue_id_v"

1808 AND "issue_privilege"."member_id" = "member"."id"

1809 WHERE "issue"."id" = "issue_id_v"

1810 AND "member"."active" AND COALESCE(

1811 "issue_privilege"."voting_right", "privilege"."voting_right");

1812 FOR "member_id_v" IN

1813 SELECT "member_id" FROM "direct_interest_snapshot"

1814 WHERE "snapshot_id" = "snapshot_id_v"

1815 AND "issue_id" = "issue_id_v"

1816 LOOP

1817 UPDATE "direct_interest_snapshot" SET

1818 "weight" = "ownweight" +

1819 "weight_of_added_delegations_for_snapshot"(

1820 "snapshot_id_v",

1821 "issue_id_v",

1822 "member_id_v",

1823 '{}'

1824 )

1825 WHERE "snapshot_id" = "snapshot_id_v"

1826 AND "issue_id" = "issue_id_v"

1827 AND "member_id" = "member_id_v";

1828 END LOOP;

1829 INSERT INTO "direct_supporter_snapshot"

1830 ( "snapshot_id", "issue_id", "initiative_id", "member_id",

1831 "draft_id", "informed", "satisfied" )

1832 SELECT

1833 "snapshot_id_v" AS "snapshot_id",

1834 "issue_id_v" AS "issue_id",

1835 "initiative"."id" AS "initiative_id",

1836 "supporter"."member_id" AS "member_id",

1837 "supporter"."draft_id" AS "draft_id",

1838 "supporter"."draft_id" = "current_draft"."id" AS "informed",

1839 NOT EXISTS (

1840 SELECT NULL FROM "critical_opinion"

1841 WHERE "initiative_id" = "initiative"."id"

1842 AND "member_id" = "supporter"."member_id"

1843 ) AS "satisfied"

1844 FROM "initiative"

1845 JOIN "supporter"

1846 ON "supporter"."initiative_id" = "initiative"."id"

1847 JOIN "current_draft"

1848 ON "initiative"."id" = "current_draft"."initiative_id"

1849 JOIN "direct_interest_snapshot"

1850 ON "snapshot_id_v" = "direct_interest_snapshot"."snapshot_id"

1851 AND "supporter"."member_id" = "direct_interest_snapshot"."member_id"

1852 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"

1853 WHERE "initiative"."issue_id" = "issue_id_v";

1854 DELETE FROM "temporary_suggestion_counts";

1855 INSERT INTO "temporary_suggestion_counts"

1856 ( "id",

1857 "minus2_unfulfilled_count", "minus2_fulfilled_count",

1858 "minus1_unfulfilled_count", "minus1_fulfilled_count",

1859 "plus1_unfulfilled_count", "plus1_fulfilled_count",

1860 "plus2_unfulfilled_count", "plus2_fulfilled_count" )

1861 SELECT

1862 "suggestion"."id",

1863 ( SELECT coalesce(sum("di"."weight"), 0)

1864 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"

1865 ON "di"."snapshot_id" = "snapshot_id_v"

1866 AND "di"."issue_id" = "issue_id_v"

1867 AND "di"."member_id" = "opinion"."member_id"

1868 WHERE "opinion"."suggestion_id" = "suggestion"."id"

1869 AND "opinion"."degree" = -2

1870 AND "opinion"."fulfilled" = FALSE

1871 ) AS "minus2_unfulfilled_count",

1872 ( SELECT coalesce(sum("di"."weight"), 0)

1873 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"

1874 ON "di"."snapshot_id" = "snapshot_id_v"

1875 AND "di"."issue_id" = "issue_id_v"

1876 AND "di"."member_id" = "opinion"."member_id"

1877 WHERE "opinion"."suggestion_id" = "suggestion"."id"

1878 AND "opinion"."degree" = -2

1879 AND "opinion"."fulfilled" = TRUE

1880 ) AS "minus2_fulfilled_count",

1881 ( SELECT coalesce(sum("di"."weight"), 0)

1882 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"

1883 ON "di"."snapshot_id" = "snapshot_id_v"

1884 AND "di"."issue_id" = "issue_id_v"

1885 AND "di"."member_id" = "opinion"."member_id"

1886 WHERE "opinion"."suggestion_id" = "suggestion"."id"

1887 AND "opinion"."degree" = -1

1888 AND "opinion"."fulfilled" = FALSE

1889 ) AS "minus1_unfulfilled_count",

1890 ( SELECT coalesce(sum("di"."weight"), 0)

1891 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"

1892 ON "di"."snapshot_id" = "snapshot_id_v"

1893 AND "di"."issue_id" = "issue_id_v"

1894 AND "di"."member_id" = "opinion"."member_id"

1895 WHERE "opinion"."suggestion_id" = "suggestion"."id"

1896 AND "opinion"."degree" = -1

1897 AND "opinion"."fulfilled" = TRUE

1898 ) AS "minus1_fulfilled_count",

1899 ( SELECT coalesce(sum("di"."weight"), 0)

1900 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"

1901 ON "di"."snapshot_id" = "snapshot_id_v"

1902 AND "di"."issue_id" = "issue_id_v"

1903 AND "di"."member_id" = "opinion"."member_id"

1904 WHERE "opinion"."suggestion_id" = "suggestion"."id"

1905 AND "opinion"."degree" = 1

1906 AND "opinion"."fulfilled" = FALSE

1907 ) AS "plus1_unfulfilled_count",

1908 ( SELECT coalesce(sum("di"."weight"), 0)

1909 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"

1910 ON "di"."snapshot_id" = "snapshot_id_v"

1911 AND "di"."issue_id" = "issue_id_v"

1912 AND "di"."member_id" = "opinion"."member_id"

1913 WHERE "opinion"."suggestion_id" = "suggestion"."id"

1914 AND "opinion"."degree" = 1

1915 AND "opinion"."fulfilled" = TRUE

1916 ) AS "plus1_fulfilled_count",

1917 ( SELECT coalesce(sum("di"."weight"), 0)

1918 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"

1919 ON "di"."snapshot_id" = "snapshot_id_v"

1920 AND "di"."issue_id" = "issue_id_v"

1921 AND "di"."member_id" = "opinion"."member_id"

1922 WHERE "opinion"."suggestion_id" = "suggestion"."id"

1923 AND "opinion"."degree" = 2

1924 AND "opinion"."fulfilled" = FALSE

1925 ) AS "plus2_unfulfilled_count",

1926 ( SELECT coalesce(sum("di"."weight"), 0)

1927 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"

1928 ON "di"."snapshot_id" = "snapshot_id_v"

1929 AND "di"."issue_id" = "issue_id_v"

1930 AND "di"."member_id" = "opinion"."member_id"

1931 WHERE "opinion"."suggestion_id" = "suggestion"."id"

1932 AND "opinion"."degree" = 2

1933 AND "opinion"."fulfilled" = TRUE

1934 ) AS "plus2_fulfilled_count"

1935 FROM "suggestion" JOIN "initiative"

1936 ON "suggestion"."initiative_id" = "initiative"."id"

1937 WHERE "initiative"."issue_id" = "issue_id_v";

1938 END LOOP;

1939 RETURN "snapshot_id_v";

1940 END;

1941 $$;

1942

1943 CREATE OR REPLACE FUNCTION "weight_of_added_vote_delegations"

1944 ( "issue_id_p" "issue"."id"%TYPE,

1945 "member_id_p" "member"."id"%TYPE,

1946 "delegate_member_ids_p" "delegating_voter"."delegate_member_ids"%TYPE )

1947 RETURNS "direct_voter"."weight"%TYPE

1948 LANGUAGE 'plpgsql' VOLATILE AS $$

1949 DECLARE

1950 "issue_delegation_row" "issue_delegation"%ROWTYPE;

1951 "delegate_member_ids_v" "delegating_voter"."delegate_member_ids"%TYPE;

1952 "weight_v" INT4;

1953 "sub_weight_v" INT4;

1954 BEGIN

1955 PERFORM "require_transaction_isolation"();

1956 "weight_v" := 0;

1957 FOR "issue_delegation_row" IN

1958 SELECT * FROM "issue_delegation"

1959 WHERE "trustee_id" = "member_id_p"

1960 AND "issue_id" = "issue_id_p"

1961 LOOP

1962 IF NOT EXISTS (

1963 SELECT NULL FROM "direct_voter"

1964 WHERE "member_id" = "issue_delegation_row"."truster_id"

1965 AND "issue_id" = "issue_id_p"

1966 ) AND NOT EXISTS (

1967 SELECT NULL FROM "delegating_voter"

1968 WHERE "member_id" = "issue_delegation_row"."truster_id"

1969 AND "issue_id" = "issue_id_p"

1970 ) THEN

1971 "delegate_member_ids_v" :=

1972 "member_id_p" || "delegate_member_ids_p";

1973 INSERT INTO "delegating_voter" (

1974 "issue_id",

1975 "member_id",

1976 "ownweight",

1977 "scope",

1978 "delegate_member_ids"

1979 ) VALUES (

1980 "issue_id_p",

1981 "issue_delegation_row"."truster_id",

1982 "issue_delegation_row"."weight",

1983 "issue_delegation_row"."scope",

1984 "delegate_member_ids_v"

1985 );

1986 "sub_weight_v" := "issue_delegation_row"."weight" +

1987 "weight_of_added_vote_delegations"(

1988 "issue_id_p",

1989 "issue_delegation_row"."truster_id",

1990 "delegate_member_ids_v"

1991 );

1992 UPDATE "delegating_voter"

1993 SET "weight" = "sub_weight_v"

1994 WHERE "issue_id" = "issue_id_p"

1995 AND "member_id" = "issue_delegation_row"."truster_id";

1996 "weight_v" := "weight_v" + "sub_weight_v";

1997 END IF;

1998 END LOOP;

1999 RETURN "weight_v";

2000 END;

2001 $$;

2002

2003 CREATE OR REPLACE FUNCTION "add_vote_delegations"

2004 ( "issue_id_p" "issue"."id"%TYPE )

2005 RETURNS VOID

2006 LANGUAGE 'plpgsql' VOLATILE AS $$

2007 DECLARE

2008 "member_id_v" "member"."id"%TYPE;

2009 BEGIN

2010 PERFORM "require_transaction_isolation"();

2011 FOR "member_id_v" IN

2012 SELECT "member_id" FROM "direct_voter"

2013 WHERE "issue_id" = "issue_id_p"

2014 LOOP

2015 UPDATE "direct_voter" SET

2016 "weight" = "ownweight" + "weight_of_added_vote_delegations"(

2017 "issue_id_p",

2018 "member_id_v",

2019 '{}'

2020 )

2021 WHERE "member_id" = "member_id_v"

2022 AND "issue_id" = "issue_id_p";

2023 END LOOP;

2024 RETURN;

2025 END;

2026 $$;

2027

2028 CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)

2029 RETURNS VOID

2030 LANGUAGE 'plpgsql' VOLATILE AS $$

2031 DECLARE

2032 "area_id_v" "area"."id"%TYPE;

2033 "unit_id_v" "unit"."id"%TYPE;

2034 "member_id_v" "member"."id"%TYPE;

2035 BEGIN

2036 PERFORM "require_transaction_isolation"();

2037 SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";

2038 SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v";

2039 -- override protection triggers:

2040 INSERT INTO "temporary_transaction_data" ("key", "value")

2041 VALUES ('override_protection_triggers', TRUE::TEXT);

2042 -- delete timestamp of voting comment:

2043 UPDATE "direct_voter" SET "comment_changed" = NULL

2044 WHERE "issue_id" = "issue_id_p";

2045 -- delete delegating votes (in cases of manual reset of issue state):

2046 DELETE FROM "delegating_voter"

2047 WHERE "issue_id" = "issue_id_p";

2048 -- delete votes from non-privileged voters:

2049 DELETE FROM "direct_voter"

2050 USING (

2051 SELECT "direct_voter"."member_id"

2052 FROM "direct_voter"

2053 JOIN "member" ON "direct_voter"."member_id" = "member"."id"

2054 LEFT JOIN "privilege"

2055 ON "privilege"."unit_id" = "unit_id_v"

2056 AND "privilege"."member_id" = "direct_voter"."member_id"

2057 LEFT JOIN "issue_privilege"

2058 ON "issue_privilege"."issue_id" = "issue_id_p"

2059 AND "issue_privilege"."member_id" = "direct_voter"."member_id"

2060 WHERE "direct_voter"."issue_id" = "issue_id_p" AND (

2061 "member"."active" = FALSE OR

2062 COALESCE(

2063 "issue_privilege"."voting_right",

2064 "privilege"."voting_right",

2065 FALSE

2066 ) = FALSE

2067 )

2068 ) AS "subquery"

2069 WHERE "direct_voter"."issue_id" = "issue_id_p"

2070 AND "direct_voter"."member_id" = "subquery"."member_id";

2071 -- consider voting weight and delegations:

2072 UPDATE "direct_voter" SET "ownweight" = "privilege"."weight"

2073 FROM "privilege"

2074 WHERE "issue_id" = "issue_id_p"

2075 AND "privilege"."unit_id" = "unit_id_v"

2076 AND "privilege"."member_id" = "direct_voter"."member_id";

2077 UPDATE "direct_voter" SET "ownweight" = "issue_privilege"."weight"

2078 FROM "issue_privilege"

2079 WHERE "direct_voter"."issue_id" = "issue_id_p"

2080 AND "issue_privilege"."issue_id" = "issue_id_p"

2081 AND "issue_privilege"."member_id" = "direct_voter"."member_id";

2082 PERFORM "add_vote_delegations"("issue_id_p");

2083 -- mark first preferences:

2084 UPDATE "vote" SET "first_preference" = "subquery"."first_preference"

2085 FROM (

2086 SELECT

2087 "vote"."initiative_id",

2088 "vote"."member_id",

2089 CASE WHEN "vote"."grade" > 0 THEN

2090 CASE WHEN "vote"."grade" = max("agg"."grade") THEN TRUE ELSE FALSE END

2091 ELSE NULL

2092 END AS "first_preference"

2093 FROM "vote"

2094 JOIN "initiative" -- NOTE: due to missing index on issue_id

2095 ON "vote"."issue_id" = "initiative"."issue_id"

2096 JOIN "vote" AS "agg"

2097 ON "initiative"."id" = "agg"."initiative_id"

2098 AND "vote"."member_id" = "agg"."member_id"

2099 GROUP BY "vote"."initiative_id", "vote"."member_id", "vote"."grade"

2100 ) AS "subquery"

2101 WHERE "vote"."issue_id" = "issue_id_p"

2102 AND "vote"."initiative_id" = "subquery"."initiative_id"

2103 AND "vote"."member_id" = "subquery"."member_id";

2104 -- finish overriding protection triggers (avoids garbage):

2105 DELETE FROM "temporary_transaction_data"

2106 WHERE "key" = 'override_protection_triggers';

2107 -- materialize battle_view:

2108 -- NOTE: "closed" column of issue must be set at this point

2109 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";

2110 INSERT INTO "battle" (

2111 "issue_id",

2112 "winning_initiative_id", "losing_initiative_id",

2113 "count"

2114 ) SELECT

2115 "issue_id",

2116 "winning_initiative_id", "losing_initiative_id",

2117 "count"

2118 FROM "battle_view" WHERE "issue_id" = "issue_id_p";

2119 -- set voter count:

2120 UPDATE "issue" SET

2121 "voter_count" = (

2122 SELECT coalesce(sum("weight"), 0)

2123 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"

2124 )

2125 WHERE "id" = "issue_id_p";

2126 -- copy "positive_votes" and "negative_votes" from "battle" table:

2127 -- NOTE: "first_preference_votes" is set to a default of 0 at this step

2128 UPDATE "initiative" SET

2129 "first_preference_votes" = 0,

2130 "positive_votes" = "battle_win"."count",

2131 "negative_votes" = "battle_lose"."count"

2132 FROM "battle" AS "battle_win", "battle" AS "battle_lose"

2133 WHERE

2134 "battle_win"."issue_id" = "issue_id_p" AND

2135 "battle_win"."winning_initiative_id" = "initiative"."id" AND

2136 "battle_win"."losing_initiative_id" ISNULL AND

2137 "battle_lose"."issue_id" = "issue_id_p" AND

2138 "battle_lose"."losing_initiative_id" = "initiative"."id" AND

2139 "battle_lose"."winning_initiative_id" ISNULL;

2140 -- calculate "first_preference_votes":

2141 -- NOTE: will only set values not equal to zero

2142 UPDATE "initiative" SET "first_preference_votes" = "subquery"."sum"

2143 FROM (

2144 SELECT "vote"."initiative_id", sum("direct_voter"."weight")

2145 FROM "vote" JOIN "direct_voter"

2146 ON "vote"."issue_id" = "direct_voter"."issue_id"

2147 AND "vote"."member_id" = "direct_voter"."member_id"

2148 WHERE "vote"."first_preference"

2149 GROUP BY "vote"."initiative_id"

2150 ) AS "subquery"

2151 WHERE "initiative"."issue_id" = "issue_id_p"

2152 AND "initiative"."admitted"

2153 AND "initiative"."id" = "subquery"."initiative_id";

2154 END;

2155 $$;

2156

2157 COMMIT;