liquid_feedback_core: 5ae68278492f update/core-update.v4.2.0-v4.2.1.sql

liquid_feedback_core

view update/core-update.v4.2.0-v4.2.1.sql @ 615:5ae68278492f

Added update script core-update.v4.2.0-v4.2.1.sql

author	jbe
date	Sun May 17 13:56:43 2020 +0200 (2020-05-17)
parents
children	ae53fc96c953

line source

1 CREATE OR REPLACE VIEW "liquid_feedback_version" AS

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

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

5 BEGIN;

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

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

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

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

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

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

16 CREATE TABLE "issue_privilege" (

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

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

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

20 "initiative_right" BOOLEAN,

21 "voting_right" BOOLEAN,

22 "polling_right" BOOLEAN,

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

47 DROP VIEW "issue_delegation";

48 CREATE VIEW "issue_delegation" AS

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

50 "issue"."id" AS "issue_id",

51 "delegation"."id",

52 "delegation"."truster_id",

53 "delegation"."trustee_id",

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

55 "delegation"."scope"

56 FROM "issue"

57 JOIN "area"

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

59 JOIN "delegation"

60 ON "delegation"."unit_id" = "area"."unit_id"

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

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

63 JOIN "member"

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

65 LEFT JOIN "privilege"

66 ON "area"."unit_id" = "privilege"."unit_id"

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

68 LEFT JOIN "issue_privilege"

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

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

71 WHERE "member"."active"

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

73 ORDER BY

74 "issue"."id",

75 "delegation"."truster_id",

76 "delegation"."scope" DESC;

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

79 CREATE OR REPLACE VIEW "unit_member" AS

80 SELECT

81 "privilege"."unit_id" AS "unit_id",

82 "member"."id" AS "member_id",

83 "privilege"."weight"

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

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

87 CREATE OR REPLACE VIEW "unit_member_count" AS

88 SELECT

89 "unit"."id" AS "unit_id",

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

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

92 FROM "unit" LEFT JOIN "unit_member"

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

94 GROUP BY "unit"."id";

96 CREATE OR REPLACE VIEW "event_for_notification" AS

97 SELECT

98 "member"."id" AS "recipient_id",

99 "event".*

100 FROM "member" CROSS JOIN "event"

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

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

103 LEFT JOIN "privilege" ON

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

105 "privilege"."unit_id" = "area"."unit_id"

106 LEFT JOIN "issue_privilege" ON

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

108 "issue_privilege"."issue_id" = "event"."issue_id"

109 LEFT JOIN "subscription" ON

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

111 "subscription"."unit_id" = "area"."unit_id"

112 LEFT JOIN "ignored_area" ON

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

114 "ignored_area"."area_id" = "issue"."area_id"

115 LEFT JOIN "interest" ON

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

117 "interest"."issue_id" = "event"."issue_id"

118 LEFT JOIN "supporter" ON

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

120 "supporter"."initiative_id" = "event"."initiative_id"

121 WHERE (

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

123 "subscription"."member_id" NOTNULL

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

125 AND (

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

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

128 "supporter"."member_id" NOTNULL ) );

129

130 CREATE OR REPLACE FUNCTION "featured_initiative"

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

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

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

134 LANGUAGE 'plpgsql' STABLE AS $$

135 DECLARE

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

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

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

139 "match_v" BOOLEAN;

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

141 "seed_v" TEXT;

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

143 BEGIN

144 SELECT "notification_counter", "notification_sample_size"

145 INTO "counter_v", "sample_size_v"

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

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

148 RETURN;

149 END IF;

150 "initiative_id_ary" := '{}';

151 LOOP

152 "match_v" := FALSE;

153 FOR "member_id_v", "seed_v" IN

154 SELECT * FROM (

155 SELECT DISTINCT

156 "supporter"."member_id",

157 md5(

158 "recipient_id_p" || '-' ||

159 "counter_v" || '-' ||

160 "area_id_p" || '-' ||

161 "supporter"."member_id"

162 ) AS "seed"

163 FROM "supporter"

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

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

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

167 AND "issue"."area_id" = "area_id_p"

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

169 ) AS "subquery"

170 ORDER BY "seed"

171 LOOP

172 SELECT "initiative"."id" INTO "initiative_id_v"

173 FROM "initiative"

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

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

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

177 LEFT JOIN "supporter" AS "self_support" ON

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

179 "self_support"."member_id" = "recipient_id_p"

180 LEFT JOIN "privilege" ON

181 "privilege"."member_id" = "recipient_id_p" AND

182 "privilege"."unit_id" = "area"."unit_id"

183 LEFT JOIN "issue_privilege" ON

184 "privilege"."member_id" = "recipient_id_p" AND

185 "privilege"."issue_id" = "initiative"."issue_id"

186 LEFT JOIN "subscription" ON

187 "subscription"."member_id" = "recipient_id_p" AND

188 "subscription"."unit_id" = "area"."unit_id"

189 LEFT JOIN "ignored_initiative" ON

190 "ignored_initiative"."member_id" = "recipient_id_p" AND

191 "ignored_initiative"."initiative_id" = "initiative"."id"

192 WHERE "supporter"."member_id" = "member_id_v"

193 AND "issue"."area_id" = "area_id_p"

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

195 AND "initiative"."revoked" ISNULL

196 AND "self_support"."member_id" ISNULL

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

198 AND (

199 COALESCE(

200 "issue_privilege"."voting_right", "privilege"."voting_right"

201 ) OR "subscription"."member_id" NOTNULL )

202 AND "ignored_initiative"."member_id" ISNULL

203 AND NOT EXISTS (

204 SELECT NULL FROM "draft"

205 JOIN "ignored_member" ON

206 "ignored_member"."member_id" = "recipient_id_p" AND

207 "ignored_member"."other_member_id" = "draft"."author_id"

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

209 )

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

211 LIMIT 1;

212 IF FOUND THEN

213 "match_v" := TRUE;

214 RETURN NEXT "initiative_id_v";

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

216 RETURN;

217 END IF;

218 "initiative_id_ary" := "initiative_id_ary" || "initiative_id_v";

219 END IF;

220 END LOOP;

221 EXIT WHEN NOT "match_v";

222 END LOOP;

223 RETURN;

224 END;

225 $$;

226

227 CREATE OR REPLACE FUNCTION "delegation_chain"

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

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

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

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

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

233 "simulate_default_p" BOOLEAN DEFAULT FALSE )

234 RETURNS SETOF "delegation_chain_row"

235 LANGUAGE 'plpgsql' STABLE AS $$

236 DECLARE

237 "scope_v" "delegation_scope";

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

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

240 "issue_row" "issue"%ROWTYPE;

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

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

243 "output_row" "delegation_chain_row";

244 "output_rows" "delegation_chain_row"[];

245 "simulate_v" BOOLEAN;

246 "simulate_here_v" BOOLEAN;

247 "delegation_row" "delegation"%ROWTYPE;

248 "row_count" INT4;

249 "i" INT4;

250 "loop_v" BOOLEAN;

251 BEGIN

252 IF "simulate_trustee_id_p" NOTNULL AND "simulate_default_p" THEN

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

254 END IF;

255 IF "simulate_trustee_id_p" NOTNULL OR "simulate_default_p" THEN

256 "simulate_v" := TRUE;

257 ELSE

258 "simulate_v" := FALSE;

259 END IF;

260 IF

261 "unit_id_p" NOTNULL AND

262 "area_id_p" ISNULL AND

263 "issue_id_p" ISNULL

264 THEN

265 "scope_v" := 'unit';

266 "unit_id_v" := "unit_id_p";

267 ELSIF

268 "unit_id_p" ISNULL AND

269 "area_id_p" NOTNULL AND

270 "issue_id_p" ISNULL

271 THEN

272 "scope_v" := 'area';

273 "area_id_v" := "area_id_p";

274 SELECT "unit_id" INTO "unit_id_v"

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

276 ELSIF

277 "unit_id_p" ISNULL AND

278 "area_id_p" ISNULL AND

279 "issue_id_p" NOTNULL

280 THEN

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

282 IF "issue_row"."id" ISNULL THEN

283 RETURN;

284 END IF;

285 IF "issue_row"."closed" NOTNULL THEN

286 IF "simulate_v" THEN

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

288 END IF;

289 FOR "output_row" IN

290 SELECT * FROM

291 "delegation_chain_for_closed_issue"("member_id_p", "issue_id_p")

292 LOOP

293 RETURN NEXT "output_row";

294 END LOOP;

295 RETURN;

296 END IF;

297 "scope_v" := 'issue';

298 SELECT "area_id" INTO "area_id_v"

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

300 SELECT "unit_id" INTO "unit_id_v"

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

302 ELSE

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

304 END IF;

305 "visited_member_ids" := '{}';

306 "loop_member_id_v" := NULL;

307 "output_rows" := '{}';

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

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

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

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

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

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

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

315 LOOP

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

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

318 ELSE

319 "visited_member_ids" :=

320 "visited_member_ids" || "output_row"."member_id";

321 END IF;

322 IF "output_row"."participation" ISNULL THEN

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

324 ELSIF "output_row"."participation" THEN

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

326 END IF;

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

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

329 SELECT NULL FROM "member"

330 LEFT JOIN "privilege"

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

332 AND "privilege"."unit_id" = "unit_id_v"

333 LEFT JOIN "issue_privilege"

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

335 AND "issue_privilege"."issue_id" = "issue_id_p"

336 WHERE "id" = "output_row"."member_id"

337 AND "member"."active"

338 AND COALESCE(

339 "issue_privilege"."voting_right", "privilege"."voting_right")

340 );

341 "simulate_here_v" := (

342 "simulate_v" AND

343 "output_row"."member_id" = "member_id_p"

344 );

345 "delegation_row" := ROW(NULL);

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

347 IF "scope_v" = 'unit' THEN

348 IF NOT "simulate_here_v" THEN

349 SELECT * INTO "delegation_row" FROM "delegation"

350 WHERE "truster_id" = "output_row"."member_id"

351 AND "unit_id" = "unit_id_v";

352 END IF;

353 ELSIF "scope_v" = 'area' THEN

354 IF "simulate_here_v" THEN

355 IF "simulate_trustee_id_p" ISNULL THEN

356 SELECT * INTO "delegation_row" FROM "delegation"

357 WHERE "truster_id" = "output_row"."member_id"

358 AND "unit_id" = "unit_id_v";

359 END IF;

360 ELSE

361 SELECT * INTO "delegation_row" FROM "delegation"

362 WHERE "truster_id" = "output_row"."member_id"

363 AND (

364 "unit_id" = "unit_id_v" OR

365 "area_id" = "area_id_v"

366 )

367 ORDER BY "scope" DESC;

368 END IF;

369 ELSIF "scope_v" = 'issue' THEN

370 IF "issue_row"."fully_frozen" ISNULL THEN

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

372 SELECT NULL FROM "interest"

373 WHERE "issue_id" = "issue_id_p"

374 AND "member_id" = "output_row"."member_id"

375 );

376 ELSE

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

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

379 SELECT NULL FROM "direct_voter"

380 WHERE "issue_id" = "issue_id_p"

381 AND "member_id" = "output_row"."member_id"

382 );

383 ELSE

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

385 END IF;

386 END IF;

387 IF "simulate_here_v" THEN

388 IF "simulate_trustee_id_p" ISNULL THEN

389 SELECT * INTO "delegation_row" FROM "delegation"

390 WHERE "truster_id" = "output_row"."member_id"

391 AND (

392 "unit_id" = "unit_id_v" OR

393 "area_id" = "area_id_v"

394 )

395 ORDER BY "scope" DESC;

396 END IF;

397 ELSE

398 SELECT * INTO "delegation_row" FROM "delegation"

399 WHERE "truster_id" = "output_row"."member_id"

400 AND (

401 "unit_id" = "unit_id_v" OR

402 "area_id" = "area_id_v" OR

403 "issue_id" = "issue_id_p"

404 )

405 ORDER BY "scope" DESC;

406 END IF;

407 END IF;

408 ELSE

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

410 END IF;

411 IF "simulate_here_v" AND "simulate_trustee_id_p" NOTNULL THEN

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

413 "output_rows" := "output_rows" || "output_row";

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

415 ELSIF "delegation_row"."trustee_id" NOTNULL THEN

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

417 "output_rows" := "output_rows" || "output_row";

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

419 ELSIF "delegation_row"."scope" NOTNULL THEN

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

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

422 "output_rows" := "output_rows" || "output_row";

423 EXIT;

424 ELSE

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

426 "output_rows" := "output_rows" || "output_row";

427 EXIT;

428 END IF;

429 EXIT WHEN "loop_member_id_v" NOTNULL;

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

431 END LOOP;

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

433 "i" := 1;

434 "loop_v" := FALSE;

435 LOOP

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

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

438 IF "loop_v" THEN

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

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

441 ELSIF "i" = "row_count" THEN

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

443 ELSE

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

445 END IF;

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

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

448 "loop_v" := TRUE;

449 END IF;

450 IF "scope_v" = 'unit' THEN

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

452 END IF;

453 RETURN NEXT "output_row";

454 "i" := "i" + 1;

455 END LOOP;

456 RETURN;

457 END;

458 $$;

459

460 CREATE OR REPLACE FUNCTION "calculate_member_counts"()

461 RETURNS VOID

462 LANGUAGE 'plpgsql' VOLATILE AS $$

463 BEGIN

464 PERFORM "require_transaction_isolation"();

465 DELETE FROM "member_count";

466 INSERT INTO "member_count" ("total_count")

467 SELECT "total_count" FROM "member_count_view";

468 UPDATE "unit" SET

469 "member_count" = "view"."member_count",

470 "member_weight" = "view"."member_weight"

471 FROM "unit_member_count" AS "view"

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

473 RETURN;

474 END;

475 $$;

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

477

478 CREATE OR REPLACE FUNCTION "weight_of_added_delegations_for_snapshot"

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

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

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

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

483 RETURNS "direct_interest_snapshot"."weight"%TYPE

484 LANGUAGE 'plpgsql' VOLATILE AS $$

485 DECLARE

486 "issue_delegation_row" "issue_delegation"%ROWTYPE;

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

488 "weight_v" INT4;

489 "sub_weight_v" INT4;

490 BEGIN

491 PERFORM "require_transaction_isolation"();

492 "weight_v" := 0;

493 FOR "issue_delegation_row" IN

494 SELECT * FROM "issue_delegation"

495 WHERE "trustee_id" = "member_id_p"

496 AND "issue_id" = "issue_id_p"

497 LOOP

498 IF NOT EXISTS (

499 SELECT NULL FROM "direct_interest_snapshot"

500 WHERE "snapshot_id" = "snapshot_id_p"

501 AND "issue_id" = "issue_id_p"

502 AND "member_id" = "issue_delegation_row"."truster_id"

503 ) AND NOT EXISTS (

504 SELECT NULL FROM "delegating_interest_snapshot"

505 WHERE "snapshot_id" = "snapshot_id_p"

506 AND "issue_id" = "issue_id_p"

507 AND "member_id" = "issue_delegation_row"."truster_id"

508 ) THEN

509 "delegate_member_ids_v" :=

510 "member_id_p" || "delegate_member_ids_p";

511 INSERT INTO "delegating_interest_snapshot" (

512 "snapshot_id",

513 "issue_id",

514 "member_id",

515 "ownweight",

516 "scope",

517 "delegate_member_ids"

518 ) VALUES (

519 "snapshot_id_p",

520 "issue_id_p",

521 "issue_delegation_row"."truster_id",

522 "issue_delegation_row"."weight",

523 "issue_delegation_row"."scope",

524 "delegate_member_ids_v"

525 );

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

527 "weight_of_added_delegations_for_snapshot"(

528 "snapshot_id_p",

529 "issue_id_p",

530 "issue_delegation_row"."truster_id",

531 "delegate_member_ids_v"

532 );

533 UPDATE "delegating_interest_snapshot"

534 SET "weight" = "sub_weight_v"

535 WHERE "snapshot_id" = "snapshot_id_p"

536 AND "issue_id" = "issue_id_p"

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

538 "weight_v" := "weight_v" + "sub_weight_v";

539 END IF;

540 END LOOP;

541 RETURN "weight_v";

542 END;

543 $$;

544

545 CREATE OR REPLACE FUNCTION "take_snapshot"

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

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

548 RETURNS "snapshot"."id"%TYPE

549 LANGUAGE 'plpgsql' VOLATILE AS $$

550 DECLARE

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

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

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

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

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

556 BEGIN

557 IF "issue_id_p" NOTNULL AND "area_id_p" NOTNULL THEN

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

559 END IF;

560 PERFORM "require_transaction_isolation"();

561 IF "issue_id_p" ISNULL THEN

562 "area_id_v" := "area_id_p";

563 ELSE

564 SELECT "area_id" INTO "area_id_v"

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

566 END IF;

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

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

569 VALUES ("area_id_v", "issue_id_p")

570 RETURNING "id" INTO "snapshot_id_v";

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

572 SELECT

573 "snapshot_id_v",

574 "member"."id",

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

576 FROM "member"

577 LEFT JOIN "privilege"

578 ON "privilege"."unit_id" = "unit_id_v"

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

580 LEFT JOIN "issue_privilege"

581 ON "issue_privilege"."issue_id" = "issue_id_p"

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

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

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

585 UPDATE "snapshot" SET

586 "population" = (

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

588 WHERE "snapshot_id" = "snapshot_id_v"

589 ) WHERE "id" = "snapshot_id_v";

590 FOR "issue_id_v" IN

591 SELECT "id" FROM "issue"

592 WHERE CASE WHEN "issue_id_p" ISNULL THEN

593 "area_id" = "area_id_p" AND

594 "state" = 'admission'

595 ELSE

596 "id" = "issue_id_p"

597 END

598 LOOP

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

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

601 INSERT INTO "direct_interest_snapshot"

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

603 SELECT

604 "snapshot_id_v" AS "snapshot_id",

605 "issue_id_v" AS "issue_id",

606 "member"."id" AS "member_id",

607 COALESCE(

608 "issue_privilege"."weight", "privilege"."weight"

609 ) AS "ownweight"

610 FROM "issue"

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

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

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

614 LEFT JOIN "privilege"

615 ON "privilege"."unit_id" = "area"."unit_id"

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

617 LEFT JOIN "issue_privilege"

618 ON "issue_privilege"."issue_id" = "issue_id_v"

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

620 WHERE "issue"."id" = "issue_id_v"

621 AND "member"."active" AND COALESCE(

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

623 FOR "member_id_v" IN

624 SELECT "member_id" FROM "direct_interest_snapshot"

625 WHERE "snapshot_id" = "snapshot_id_v"

626 AND "issue_id" = "issue_id_v"

627 LOOP

628 UPDATE "direct_interest_snapshot" SET

629 "weight" = "ownweight" +

630 "weight_of_added_delegations_for_snapshot"(

631 "snapshot_id_v",

632 "issue_id_v",

633 "member_id_v",

634 '{}'

635 )

636 WHERE "snapshot_id" = "snapshot_id_v"

637 AND "issue_id" = "issue_id_v"

638 AND "member_id" = "member_id_v";

639 END LOOP;

640 INSERT INTO "direct_supporter_snapshot"

641 ( "snapshot_id", "issue_id", "initiative_id", "member_id",

642 "draft_id", "informed", "satisfied" )

643 SELECT

644 "snapshot_id_v" AS "snapshot_id",

645 "issue_id_v" AS "issue_id",

646 "initiative"."id" AS "initiative_id",

647 "supporter"."member_id" AS "member_id",

648 "supporter"."draft_id" AS "draft_id",

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

650 NOT EXISTS (

651 SELECT NULL FROM "critical_opinion"

652 WHERE "initiative_id" = "initiative"."id"

653 AND "member_id" = "supporter"."member_id"

654 ) AS "satisfied"

655 FROM "initiative"

656 JOIN "supporter"

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

658 JOIN "current_draft"

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

660 JOIN "direct_interest_snapshot"

661 ON "snapshot_id_v" = "direct_interest_snapshot"."snapshot_id"

662 AND "supporter"."member_id" = "direct_interest_snapshot"."member_id"

663 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"

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

665 DELETE FROM "temporary_suggestion_counts";

666 INSERT INTO "temporary_suggestion_counts"

667 ( "id",

668 "minus2_unfulfilled_count", "minus2_fulfilled_count",

669 "minus1_unfulfilled_count", "minus1_fulfilled_count",

670 "plus1_unfulfilled_count", "plus1_fulfilled_count",

671 "plus2_unfulfilled_count", "plus2_fulfilled_count" )

672 SELECT

673 "suggestion"."id",

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

675 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"

676 ON "di"."snapshot_id" = "snapshot_id_v"

677 AND "di"."issue_id" = "issue_id_v"

678 AND "di"."member_id" = "opinion"."member_id"

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

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

681 AND "opinion"."fulfilled" = FALSE

682 ) AS "minus2_unfulfilled_count",

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

684 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"

685 ON "di"."snapshot_id" = "snapshot_id_v"

686 AND "di"."issue_id" = "issue_id_v"

687 AND "di"."member_id" = "opinion"."member_id"

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

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

690 AND "opinion"."fulfilled" = TRUE

691 ) AS "minus2_fulfilled_count",

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

693 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"

694 ON "di"."snapshot_id" = "snapshot_id_v"

695 AND "di"."issue_id" = "issue_id_v"

696 AND "di"."member_id" = "opinion"."member_id"

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

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

699 AND "opinion"."fulfilled" = FALSE

700 ) AS "minus1_unfulfilled_count",

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

702 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"

703 ON "di"."snapshot_id" = "snapshot_id_v"

704 AND "di"."issue_id" = "issue_id_v"

705 AND "di"."member_id" = "opinion"."member_id"

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

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

708 AND "opinion"."fulfilled" = TRUE

709 ) AS "minus1_fulfilled_count",

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

711 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"

712 ON "di"."snapshot_id" = "snapshot_id_v"

713 AND "di"."issue_id" = "issue_id_v"

714 AND "di"."member_id" = "opinion"."member_id"

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

716 AND "opinion"."degree" = 1

717 AND "opinion"."fulfilled" = FALSE

718 ) AS "plus1_unfulfilled_count",

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

720 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"

721 ON "di"."snapshot_id" = "snapshot_id_v"

722 AND "di"."issue_id" = "issue_id_v"

723 AND "di"."member_id" = "opinion"."member_id"

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

725 AND "opinion"."degree" = 1

726 AND "opinion"."fulfilled" = TRUE

727 ) AS "plus1_fulfilled_count",

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

729 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"

730 ON "di"."snapshot_id" = "snapshot_id_v"

731 AND "di"."issue_id" = "issue_id_v"

732 AND "di"."member_id" = "opinion"."member_id"

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

734 AND "opinion"."degree" = 2

735 AND "opinion"."fulfilled" = FALSE

736 ) AS "plus2_unfulfilled_count",

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

738 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"

739 ON "di"."snapshot_id" = "snapshot_id_v"

740 AND "di"."issue_id" = "issue_id_v"

741 AND "di"."member_id" = "opinion"."member_id"

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

743 AND "opinion"."degree" = 2

744 AND "opinion"."fulfilled" = TRUE

745 ) AS "plus2_fulfilled_count"

746 FROM "suggestion" JOIN "initiative"

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

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

749 END LOOP;

750 RETURN "snapshot_id_v";

751 END;

752 $$;

753

754 CREATE OR REPLACE FUNCTION "weight_of_added_vote_delegations"

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

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

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

758 RETURNS "direct_voter"."weight"%TYPE

759 LANGUAGE 'plpgsql' VOLATILE AS $$

760 DECLARE

761 "issue_delegation_row" "issue_delegation"%ROWTYPE;

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

763 "weight_v" INT4;

764 "sub_weight_v" INT4;

765 BEGIN

766 PERFORM "require_transaction_isolation"();

767 "weight_v" := 0;

768 FOR "issue_delegation_row" IN

769 SELECT * FROM "issue_delegation"

770 WHERE "trustee_id" = "member_id_p"

771 AND "issue_id" = "issue_id_p"

772 LOOP

773 IF NOT EXISTS (

774 SELECT NULL FROM "direct_voter"

775 WHERE "member_id" = "issue_delegation_row"."truster_id"

776 AND "issue_id" = "issue_id_p"

777 ) AND NOT EXISTS (

778 SELECT NULL FROM "delegating_voter"

779 WHERE "member_id" = "issue_delegation_row"."truster_id"

780 AND "issue_id" = "issue_id_p"

781 ) THEN

782 "delegate_member_ids_v" :=

783 "member_id_p" || "delegate_member_ids_p";

784 INSERT INTO "delegating_voter" (

785 "issue_id",

786 "member_id",

787 "ownweight",

788 "scope",

789 "delegate_member_ids"

790 ) VALUES (

791 "issue_id_p",

792 "issue_delegation_row"."truster_id",

793 "issue_delegation_row"."weight",

794 "issue_delegation_row"."scope",

795 "delegate_member_ids_v"

796 );

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

798 "weight_of_added_vote_delegations"(

799 "issue_id_p",

800 "issue_delegation_row"."truster_id",

801 "delegate_member_ids_v"

802 );

803 UPDATE "delegating_voter"

804 SET "weight" = "sub_weight_v"

805 WHERE "issue_id" = "issue_id_p"

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

807 "weight_v" := "weight_v" + "sub_weight_v";

808 END IF;

809 END LOOP;

810 RETURN "weight_v";

811 END;

812 $$;

813

814 CREATE OR REPLACE FUNCTION "add_vote_delegations"

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

816 RETURNS VOID

817 LANGUAGE 'plpgsql' VOLATILE AS $$

818 DECLARE

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

820 BEGIN

821 PERFORM "require_transaction_isolation"();

822 FOR "member_id_v" IN

823 SELECT "member_id" FROM "direct_voter"

824 WHERE "issue_id" = "issue_id_p"

825 LOOP

826 UPDATE "direct_voter" SET

827 "weight" = "ownweight" + "weight_of_added_vote_delegations"(

828 "issue_id_p",

829 "member_id_v",

830 '{}'

831 )

832 WHERE "member_id" = "member_id_v"

833 AND "issue_id" = "issue_id_p";

834 END LOOP;

835 RETURN;

836 END;

837 $$;

838

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

840 RETURNS VOID

841 LANGUAGE 'plpgsql' VOLATILE AS $$

842 DECLARE

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

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

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

846 BEGIN

847 PERFORM "require_transaction_isolation"();

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

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

850 -- override protection triggers:

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

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

853 -- delete timestamp of voting comment:

854 UPDATE "direct_voter" SET "comment_changed" = NULL

855 WHERE "issue_id" = "issue_id_p";

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

857 DELETE FROM "delegating_voter"

858 WHERE "issue_id" = "issue_id_p";

859 -- delete votes from non-privileged voters:

860 DELETE FROM "direct_voter"

861 USING (

862 SELECT "direct_voter"."member_id"

863 FROM "direct_voter"

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

865 LEFT JOIN "privilege"

866 ON "privilege"."unit_id" = "unit_id_v"

867 AND "privilege"."member_id" = "direct_voter"."member_id"

868 LEFT JOIN "issue_privilege"

869 ON "issue_privilege"."issue_id" = "issue_id_p"

870 AND "issue_privilege"."member_id" = "direct_voter"."member_id"

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

872 "member"."active" = FALSE OR

873 COALESCE(

874 "issue_privilege"."voting_right",

875 "privilege"."voting_right",

876 FALSE

877 ) = FALSE

878 )

879 ) AS "subquery"

880 WHERE "direct_voter"."issue_id" = "issue_id_p"

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

882 -- consider voting weight and delegations:

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

884 FROM "privilege"

885 WHERE "issue_id" = "issue_id_p"

886 AND "privilege"."unit_id" = "unit_id_v"

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

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

889 FROM "issue_privilege"

890 WHERE "direct_voter"."issue_id" = "issue_id_p"

891 AND "issue_privilege"."issue_id" = "issue_id_p"

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

893 PERFORM "add_vote_delegations"("issue_id_p");

894 -- mark first preferences:

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

896 FROM (

897 SELECT

898 "vote"."initiative_id",

899 "vote"."member_id",

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

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

902 ELSE NULL

903 END AS "first_preference"

904 FROM "vote"

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

906 ON "vote"."issue_id" = "initiative"."issue_id"

907 JOIN "vote" AS "agg"

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

909 AND "vote"."member_id" = "agg"."member_id"

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

911 ) AS "subquery"

912 WHERE "vote"."issue_id" = "issue_id_p"

913 AND "vote"."initiative_id" = "subquery"."initiative_id"

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

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

916 DELETE FROM "temporary_transaction_data"

917 WHERE "key" = 'override_protection_triggers';

918 -- materialize battle_view:

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

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

921 INSERT INTO "battle" (

922 "issue_id",

923 "winning_initiative_id", "losing_initiative_id",

924 "count"

925 ) SELECT

926 "issue_id",

927 "winning_initiative_id", "losing_initiative_id",

928 "count"

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

930 -- set voter count:

931 UPDATE "issue" SET

932 "voter_count" = (

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

934 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"

935 )

936 WHERE "id" = "issue_id_p";

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

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

939 UPDATE "initiative" SET

940 "first_preference_votes" = 0,

941 "positive_votes" = "battle_win"."count",

942 "negative_votes" = "battle_lose"."count"

943 FROM "battle" AS "battle_win", "battle" AS "battle_lose"

944 WHERE

945 "battle_win"."issue_id" = "issue_id_p" AND

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

947 "battle_win"."losing_initiative_id" ISNULL AND

948 "battle_lose"."issue_id" = "issue_id_p" AND

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

950 "battle_lose"."winning_initiative_id" ISNULL;

951 -- calculate "first_preference_votes":

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

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

954 FROM (

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

956 FROM "vote" JOIN "direct_voter"

957 ON "vote"."issue_id" = "direct_voter"."issue_id"

958 AND "vote"."member_id" = "direct_voter"."member_id"

959 WHERE "vote"."first_preference"

960 GROUP BY "vote"."initiative_id"

961 ) AS "subquery"

962 WHERE "initiative"."issue_id" = "issue_id_p"

963 AND "initiative"."admitted"

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

965 END;

966 $$;

967

968 COMMIT;