liquid_feedback_core: e0b8175fda29 update/core-update.v4.2.0-v4.2.1.sql

liquid_feedback_core

view update/core-update.v4.2.0-v4.2.1.sql @ 620:e0b8175fda29

Added TODO notice regarding indices on "posting"

author	jbe
date	Sat Dec 05 18:48:33 2020 +0100 (2020-12-05)
parents	63092784fe9d
children

line source

1 BEGIN;

3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS

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

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

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

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

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

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

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

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

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

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

19 CREATE TABLE "issue_privilege" (

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

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

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

23 "initiative_right" BOOLEAN,

24 "voting_right" BOOLEAN,

25 "polling_right" BOOLEAN,

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

52 DROP VIEW "issue_delegation";

53 CREATE VIEW "issue_delegation" AS

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

55 "issue"."id" AS "issue_id",

56 "delegation"."id",

57 "delegation"."truster_id",

58 "delegation"."trustee_id",

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

60 "delegation"."scope"

61 FROM "issue"

62 JOIN "area"

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

64 JOIN "delegation"

65 ON "delegation"."unit_id" = "area"."unit_id"

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

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

68 JOIN "member"

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

70 LEFT JOIN "privilege"

71 ON "area"."unit_id" = "privilege"."unit_id"

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

73 LEFT JOIN "issue_privilege"

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

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

76 WHERE "member"."active"

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

78 ORDER BY

79 "issue"."id",

80 "delegation"."truster_id",

81 "delegation"."scope" DESC;

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

84 CREATE OR REPLACE VIEW "unit_member" AS

85 SELECT

86 "privilege"."unit_id" AS "unit_id",

87 "member"."id" AS "member_id",

88 "privilege"."weight"

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

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

92 CREATE OR REPLACE VIEW "unit_member_count" AS

93 SELECT

94 "unit"."id" AS "unit_id",

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

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

97 FROM "unit" LEFT JOIN "unit_member"

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

99 GROUP BY "unit"."id";

100

101 CREATE OR REPLACE VIEW "event_for_notification" AS

102 SELECT

103 "member"."id" AS "recipient_id",

104 "event".*

105 FROM "member" CROSS JOIN "event"

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

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

108 LEFT JOIN "privilege" ON

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

110 "privilege"."unit_id" = "area"."unit_id"

111 LEFT JOIN "issue_privilege" ON

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

113 "issue_privilege"."issue_id" = "event"."issue_id"

114 LEFT JOIN "subscription" ON

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

116 "subscription"."unit_id" = "area"."unit_id"

117 LEFT JOIN "ignored_area" ON

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

119 "ignored_area"."area_id" = "issue"."area_id"

120 LEFT JOIN "interest" ON

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

122 "interest"."issue_id" = "event"."issue_id"

123 LEFT JOIN "supporter" ON

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

125 "supporter"."initiative_id" = "event"."initiative_id"

126 WHERE (

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

128 "subscription"."member_id" NOTNULL

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

130 AND (

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

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

133 "supporter"."member_id" NOTNULL ) );

134

135 CREATE OR REPLACE FUNCTION "featured_initiative"

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

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

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

139 LANGUAGE 'plpgsql' STABLE AS $$

140 DECLARE

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

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

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

144 "match_v" BOOLEAN;

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

146 "seed_v" TEXT;

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

148 BEGIN

149 SELECT "notification_counter", "notification_sample_size"

150 INTO "counter_v", "sample_size_v"

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

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

153 RETURN;

154 END IF;

155 "initiative_id_ary" := '{}';

156 LOOP

157 "match_v" := FALSE;

158 FOR "member_id_v", "seed_v" IN

159 SELECT * FROM (

160 SELECT DISTINCT

161 "supporter"."member_id",

162 md5(

163 "recipient_id_p" || '-' ||

164 "counter_v" || '-' ||

165 "area_id_p" || '-' ||

166 "supporter"."member_id"

167 ) AS "seed"

168 FROM "supporter"

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

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

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

172 AND "issue"."area_id" = "area_id_p"

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

174 ) AS "subquery"

175 ORDER BY "seed"

176 LOOP

177 SELECT "initiative"."id" INTO "initiative_id_v"

178 FROM "initiative"

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

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

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

182 LEFT JOIN "supporter" AS "self_support" ON

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

184 "self_support"."member_id" = "recipient_id_p"

185 LEFT JOIN "privilege" ON

186 "privilege"."member_id" = "recipient_id_p" AND

187 "privilege"."unit_id" = "area"."unit_id"

188 LEFT JOIN "issue_privilege" ON

189 "issue_privilege"."member_id" = "recipient_id_p" AND

190 "issue_privilege"."issue_id" = "initiative"."issue_id"

191 LEFT JOIN "subscription" ON

192 "subscription"."member_id" = "recipient_id_p" AND

193 "subscription"."unit_id" = "area"."unit_id"

194 LEFT JOIN "ignored_initiative" ON

195 "ignored_initiative"."member_id" = "recipient_id_p" AND

196 "ignored_initiative"."initiative_id" = "initiative"."id"

197 WHERE "supporter"."member_id" = "member_id_v"

198 AND "issue"."area_id" = "area_id_p"

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

200 AND "initiative"."revoked" ISNULL

201 AND "self_support"."member_id" ISNULL

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

203 AND (

204 COALESCE(

205 "issue_privilege"."voting_right", "privilege"."voting_right"

206 ) OR "subscription"."member_id" NOTNULL )

207 AND "ignored_initiative"."member_id" ISNULL

208 AND NOT EXISTS (

209 SELECT NULL FROM "draft"

210 JOIN "ignored_member" ON

211 "ignored_member"."member_id" = "recipient_id_p" AND

212 "ignored_member"."other_member_id" = "draft"."author_id"

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

214 )

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

216 LIMIT 1;

217 IF FOUND THEN

218 "match_v" := TRUE;

219 RETURN NEXT "initiative_id_v";

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

221 RETURN;

222 END IF;

223 "initiative_id_ary" := "initiative_id_ary" || "initiative_id_v";

224 END IF;

225 END LOOP;

226 EXIT WHEN NOT "match_v";

227 END LOOP;

228 RETURN;

229 END;

230 $$;

231

232 CREATE OR REPLACE FUNCTION "delegation_chain"

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

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

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

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

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

238 "simulate_default_p" BOOLEAN DEFAULT FALSE )

239 RETURNS SETOF "delegation_chain_row"

240 LANGUAGE 'plpgsql' STABLE AS $$

241 DECLARE

242 "scope_v" "delegation_scope";

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

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

245 "issue_row" "issue"%ROWTYPE;

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

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

248 "output_row" "delegation_chain_row";

249 "output_rows" "delegation_chain_row"[];

250 "simulate_v" BOOLEAN;

251 "simulate_here_v" BOOLEAN;

252 "delegation_row" "delegation"%ROWTYPE;

253 "row_count" INT4;

254 "i" INT4;

255 "loop_v" BOOLEAN;

256 BEGIN

257 IF "simulate_trustee_id_p" NOTNULL AND "simulate_default_p" THEN

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

259 END IF;

260 IF "simulate_trustee_id_p" NOTNULL OR "simulate_default_p" THEN

261 "simulate_v" := TRUE;

262 ELSE

263 "simulate_v" := FALSE;

264 END IF;

265 IF

266 "unit_id_p" NOTNULL AND

267 "area_id_p" ISNULL AND

268 "issue_id_p" ISNULL

269 THEN

270 "scope_v" := 'unit';

271 "unit_id_v" := "unit_id_p";

272 ELSIF

273 "unit_id_p" ISNULL AND

274 "area_id_p" NOTNULL AND

275 "issue_id_p" ISNULL

276 THEN

277 "scope_v" := 'area';

278 "area_id_v" := "area_id_p";

279 SELECT "unit_id" INTO "unit_id_v"

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

281 ELSIF

282 "unit_id_p" ISNULL AND

283 "area_id_p" ISNULL AND

284 "issue_id_p" NOTNULL

285 THEN

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

287 IF "issue_row"."id" ISNULL THEN

288 RETURN;

289 END IF;

290 IF "issue_row"."closed" NOTNULL THEN

291 IF "simulate_v" THEN

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

293 END IF;

294 FOR "output_row" IN

295 SELECT * FROM

296 "delegation_chain_for_closed_issue"("member_id_p", "issue_id_p")

297 LOOP

298 RETURN NEXT "output_row";

299 END LOOP;

300 RETURN;

301 END IF;

302 "scope_v" := 'issue';

303 SELECT "area_id" INTO "area_id_v"

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

305 SELECT "unit_id" INTO "unit_id_v"

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

307 ELSE

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

309 END IF;

310 "visited_member_ids" := '{}';

311 "loop_member_id_v" := NULL;

312 "output_rows" := '{}';

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

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

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

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

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

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

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

320 LOOP

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

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

323 ELSE

324 "visited_member_ids" :=

325 "visited_member_ids" || "output_row"."member_id";

326 END IF;

327 IF "output_row"."participation" ISNULL THEN

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

329 ELSIF "output_row"."participation" THEN

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

331 END IF;

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

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

334 SELECT NULL FROM "member"

335 LEFT JOIN "privilege"

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

337 AND "privilege"."unit_id" = "unit_id_v"

338 LEFT JOIN "issue_privilege"

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

340 AND "issue_privilege"."issue_id" = "issue_id_p"

341 WHERE "id" = "output_row"."member_id"

342 AND "member"."active"

343 AND COALESCE(

344 "issue_privilege"."voting_right", "privilege"."voting_right")

345 );

346 "simulate_here_v" := (

347 "simulate_v" AND

348 "output_row"."member_id" = "member_id_p"

349 );

350 "delegation_row" := ROW(NULL);

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

352 IF "scope_v" = 'unit' THEN

353 IF NOT "simulate_here_v" THEN

354 SELECT * INTO "delegation_row" FROM "delegation"

355 WHERE "truster_id" = "output_row"."member_id"

356 AND "unit_id" = "unit_id_v";

357 END IF;

358 ELSIF "scope_v" = 'area' THEN

359 IF "simulate_here_v" THEN

360 IF "simulate_trustee_id_p" ISNULL THEN

361 SELECT * INTO "delegation_row" FROM "delegation"

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

363 AND "unit_id" = "unit_id_v";

364 END IF;

365 ELSE

366 SELECT * INTO "delegation_row" FROM "delegation"

367 WHERE "truster_id" = "output_row"."member_id"

368 AND (

369 "unit_id" = "unit_id_v" OR

370 "area_id" = "area_id_v"

371 )

372 ORDER BY "scope" DESC;

373 END IF;

374 ELSIF "scope_v" = 'issue' THEN

375 IF "issue_row"."fully_frozen" ISNULL THEN

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

377 SELECT NULL FROM "interest"

378 WHERE "issue_id" = "issue_id_p"

379 AND "member_id" = "output_row"."member_id"

380 );

381 ELSE

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

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

384 SELECT NULL FROM "direct_voter"

385 WHERE "issue_id" = "issue_id_p"

386 AND "member_id" = "output_row"."member_id"

387 );

388 ELSE

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

390 END IF;

391 END IF;

392 IF "simulate_here_v" THEN

393 IF "simulate_trustee_id_p" ISNULL THEN

394 SELECT * INTO "delegation_row" FROM "delegation"

395 WHERE "truster_id" = "output_row"."member_id"

396 AND (

397 "unit_id" = "unit_id_v" OR

398 "area_id" = "area_id_v"

399 )

400 ORDER BY "scope" DESC;

401 END IF;

402 ELSE

403 SELECT * INTO "delegation_row" FROM "delegation"

404 WHERE "truster_id" = "output_row"."member_id"

405 AND (

406 "unit_id" = "unit_id_v" OR

407 "area_id" = "area_id_v" OR

408 "issue_id" = "issue_id_p"

409 )

410 ORDER BY "scope" DESC;

411 END IF;

412 END IF;

413 ELSE

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

415 END IF;

416 IF "simulate_here_v" AND "simulate_trustee_id_p" NOTNULL THEN

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

418 "output_rows" := "output_rows" || "output_row";

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

420 ELSIF "delegation_row"."trustee_id" NOTNULL THEN

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

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

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

424 ELSIF "delegation_row"."scope" NOTNULL THEN

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

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

427 "output_rows" := "output_rows" || "output_row";

428 EXIT;

429 ELSE

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

431 "output_rows" := "output_rows" || "output_row";

432 EXIT;

433 END IF;

434 EXIT WHEN "loop_member_id_v" NOTNULL;

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

436 END LOOP;

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

438 "i" := 1;

439 "loop_v" := FALSE;

440 LOOP

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

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

443 IF "loop_v" THEN

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

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

446 ELSIF "i" = "row_count" THEN

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

448 ELSE

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

450 END IF;

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

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

453 "loop_v" := TRUE;

454 END IF;

455 IF "scope_v" = 'unit' THEN

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

457 END IF;

458 RETURN NEXT "output_row";

459 "i" := "i" + 1;

460 END LOOP;

461 RETURN;

462 END;

463 $$;

464

465 CREATE OR REPLACE FUNCTION "calculate_member_counts"()

466 RETURNS VOID

467 LANGUAGE 'plpgsql' VOLATILE AS $$

468 BEGIN

469 PERFORM "require_transaction_isolation"();

470 DELETE FROM "member_count";

471 INSERT INTO "member_count" ("total_count")

472 SELECT "total_count" FROM "member_count_view";

473 UPDATE "unit" SET

474 "member_count" = "view"."member_count",

475 "member_weight" = "view"."member_weight"

476 FROM "unit_member_count" AS "view"

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

478 RETURN;

479 END;

480 $$;

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

482

483 CREATE OR REPLACE FUNCTION "weight_of_added_delegations_for_snapshot"

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

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

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

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

488 RETURNS "direct_interest_snapshot"."weight"%TYPE

489 LANGUAGE 'plpgsql' VOLATILE AS $$

490 DECLARE

491 "issue_delegation_row" "issue_delegation"%ROWTYPE;

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

493 "weight_v" INT4;

494 "sub_weight_v" INT4;

495 BEGIN

496 PERFORM "require_transaction_isolation"();

497 "weight_v" := 0;

498 FOR "issue_delegation_row" IN

499 SELECT * FROM "issue_delegation"

500 WHERE "trustee_id" = "member_id_p"

501 AND "issue_id" = "issue_id_p"

502 LOOP

503 IF NOT EXISTS (

504 SELECT NULL FROM "direct_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 ) AND NOT EXISTS (

509 SELECT NULL FROM "delegating_interest_snapshot"

510 WHERE "snapshot_id" = "snapshot_id_p"

511 AND "issue_id" = "issue_id_p"

512 AND "member_id" = "issue_delegation_row"."truster_id"

513 ) THEN

514 "delegate_member_ids_v" :=

515 "member_id_p" || "delegate_member_ids_p";

516 INSERT INTO "delegating_interest_snapshot" (

517 "snapshot_id",

518 "issue_id",

519 "member_id",

520 "ownweight",

521 "scope",

522 "delegate_member_ids"

523 ) VALUES (

524 "snapshot_id_p",

525 "issue_id_p",

526 "issue_delegation_row"."truster_id",

527 "issue_delegation_row"."weight",

528 "issue_delegation_row"."scope",

529 "delegate_member_ids_v"

530 );

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

532 "weight_of_added_delegations_for_snapshot"(

533 "snapshot_id_p",

534 "issue_id_p",

535 "issue_delegation_row"."truster_id",

536 "delegate_member_ids_v"

537 );

538 UPDATE "delegating_interest_snapshot"

539 SET "weight" = "sub_weight_v"

540 WHERE "snapshot_id" = "snapshot_id_p"

541 AND "issue_id" = "issue_id_p"

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

543 "weight_v" := "weight_v" + "sub_weight_v";

544 END IF;

545 END LOOP;

546 RETURN "weight_v";

547 END;

548 $$;

549

550 CREATE OR REPLACE FUNCTION "take_snapshot"

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

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

553 RETURNS "snapshot"."id"%TYPE

554 LANGUAGE 'plpgsql' VOLATILE AS $$

555 DECLARE

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

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

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

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

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

561 BEGIN

562 IF "issue_id_p" NOTNULL AND "area_id_p" NOTNULL THEN

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

564 END IF;

565 PERFORM "require_transaction_isolation"();

566 IF "issue_id_p" ISNULL THEN

567 "area_id_v" := "area_id_p";

568 ELSE

569 SELECT "area_id" INTO "area_id_v"

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

571 END IF;

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

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

574 VALUES ("area_id_v", "issue_id_p")

575 RETURNING "id" INTO "snapshot_id_v";

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

577 SELECT

578 "snapshot_id_v",

579 "member"."id",

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

581 FROM "member"

582 LEFT JOIN "privilege"

583 ON "privilege"."unit_id" = "unit_id_v"

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

585 LEFT JOIN "issue_privilege"

586 ON "issue_privilege"."issue_id" = "issue_id_p"

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

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

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

590 UPDATE "snapshot" SET

591 "population" = (

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

593 WHERE "snapshot_id" = "snapshot_id_v"

594 ) WHERE "id" = "snapshot_id_v";

595 FOR "issue_id_v" IN

596 SELECT "id" FROM "issue"

597 WHERE CASE WHEN "issue_id_p" ISNULL THEN

598 "area_id" = "area_id_p" AND

599 "state" = 'admission'

600 ELSE

601 "id" = "issue_id_p"

602 END

603 LOOP

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

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

606 INSERT INTO "direct_interest_snapshot"

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

608 SELECT

609 "snapshot_id_v" AS "snapshot_id",

610 "issue_id_v" AS "issue_id",

611 "member"."id" AS "member_id",

612 COALESCE(

613 "issue_privilege"."weight", "privilege"."weight"

614 ) AS "ownweight"

615 FROM "issue"

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

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

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

619 LEFT JOIN "privilege"

620 ON "privilege"."unit_id" = "area"."unit_id"

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

622 LEFT JOIN "issue_privilege"

623 ON "issue_privilege"."issue_id" = "issue_id_v"

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

625 WHERE "issue"."id" = "issue_id_v"

626 AND "member"."active" AND COALESCE(

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

628 FOR "member_id_v" IN

629 SELECT "member_id" FROM "direct_interest_snapshot"

630 WHERE "snapshot_id" = "snapshot_id_v"

631 AND "issue_id" = "issue_id_v"

632 LOOP

633 UPDATE "direct_interest_snapshot" SET

634 "weight" = "ownweight" +

635 "weight_of_added_delegations_for_snapshot"(

636 "snapshot_id_v",

637 "issue_id_v",

638 "member_id_v",

639 '{}'

640 )

641 WHERE "snapshot_id" = "snapshot_id_v"

642 AND "issue_id" = "issue_id_v"

643 AND "member_id" = "member_id_v";

644 END LOOP;

645 INSERT INTO "direct_supporter_snapshot"

646 ( "snapshot_id", "issue_id", "initiative_id", "member_id",

647 "draft_id", "informed", "satisfied" )

648 SELECT

649 "snapshot_id_v" AS "snapshot_id",

650 "issue_id_v" AS "issue_id",

651 "initiative"."id" AS "initiative_id",

652 "supporter"."member_id" AS "member_id",

653 "supporter"."draft_id" AS "draft_id",

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

655 NOT EXISTS (

656 SELECT NULL FROM "critical_opinion"

657 WHERE "initiative_id" = "initiative"."id"

658 AND "member_id" = "supporter"."member_id"

659 ) AS "satisfied"

660 FROM "initiative"

661 JOIN "supporter"

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

663 JOIN "current_draft"

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

665 JOIN "direct_interest_snapshot"

666 ON "snapshot_id_v" = "direct_interest_snapshot"."snapshot_id"

667 AND "supporter"."member_id" = "direct_interest_snapshot"."member_id"

668 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"

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

670 DELETE FROM "temporary_suggestion_counts";

671 INSERT INTO "temporary_suggestion_counts"

672 ( "id",

673 "minus2_unfulfilled_count", "minus2_fulfilled_count",

674 "minus1_unfulfilled_count", "minus1_fulfilled_count",

675 "plus1_unfulfilled_count", "plus1_fulfilled_count",

676 "plus2_unfulfilled_count", "plus2_fulfilled_count" )

677 SELECT

678 "suggestion"."id",

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

680 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"

681 ON "di"."snapshot_id" = "snapshot_id_v"

682 AND "di"."issue_id" = "issue_id_v"

683 AND "di"."member_id" = "opinion"."member_id"

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

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

686 AND "opinion"."fulfilled" = FALSE

687 ) AS "minus2_unfulfilled_count",

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

689 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"

690 ON "di"."snapshot_id" = "snapshot_id_v"

691 AND "di"."issue_id" = "issue_id_v"

692 AND "di"."member_id" = "opinion"."member_id"

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

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

695 AND "opinion"."fulfilled" = TRUE

696 ) AS "minus2_fulfilled_count",

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

698 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"

699 ON "di"."snapshot_id" = "snapshot_id_v"

700 AND "di"."issue_id" = "issue_id_v"

701 AND "di"."member_id" = "opinion"."member_id"

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

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

704 AND "opinion"."fulfilled" = FALSE

705 ) AS "minus1_unfulfilled_count",

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

707 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"

708 ON "di"."snapshot_id" = "snapshot_id_v"

709 AND "di"."issue_id" = "issue_id_v"

710 AND "di"."member_id" = "opinion"."member_id"

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

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

713 AND "opinion"."fulfilled" = TRUE

714 ) AS "minus1_fulfilled_count",

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

716 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"

717 ON "di"."snapshot_id" = "snapshot_id_v"

718 AND "di"."issue_id" = "issue_id_v"

719 AND "di"."member_id" = "opinion"."member_id"

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

721 AND "opinion"."degree" = 1

722 AND "opinion"."fulfilled" = FALSE

723 ) AS "plus1_unfulfilled_count",

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

725 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"

726 ON "di"."snapshot_id" = "snapshot_id_v"

727 AND "di"."issue_id" = "issue_id_v"

728 AND "di"."member_id" = "opinion"."member_id"

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

730 AND "opinion"."degree" = 1

731 AND "opinion"."fulfilled" = TRUE

732 ) AS "plus1_fulfilled_count",

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

734 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"

735 ON "di"."snapshot_id" = "snapshot_id_v"

736 AND "di"."issue_id" = "issue_id_v"

737 AND "di"."member_id" = "opinion"."member_id"

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

739 AND "opinion"."degree" = 2

740 AND "opinion"."fulfilled" = FALSE

741 ) AS "plus2_unfulfilled_count",

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

743 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"

744 ON "di"."snapshot_id" = "snapshot_id_v"

745 AND "di"."issue_id" = "issue_id_v"

746 AND "di"."member_id" = "opinion"."member_id"

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

748 AND "opinion"."degree" = 2

749 AND "opinion"."fulfilled" = TRUE

750 ) AS "plus2_fulfilled_count"

751 FROM "suggestion" JOIN "initiative"

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

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

754 END LOOP;

755 RETURN "snapshot_id_v";

756 END;

757 $$;

758

759 CREATE OR REPLACE FUNCTION "weight_of_added_vote_delegations"

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

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

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

763 RETURNS "direct_voter"."weight"%TYPE

764 LANGUAGE 'plpgsql' VOLATILE AS $$

765 DECLARE

766 "issue_delegation_row" "issue_delegation"%ROWTYPE;

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

768 "weight_v" INT4;

769 "sub_weight_v" INT4;

770 BEGIN

771 PERFORM "require_transaction_isolation"();

772 "weight_v" := 0;

773 FOR "issue_delegation_row" IN

774 SELECT * FROM "issue_delegation"

775 WHERE "trustee_id" = "member_id_p"

776 AND "issue_id" = "issue_id_p"

777 LOOP

778 IF NOT EXISTS (

779 SELECT NULL FROM "direct_voter"

780 WHERE "member_id" = "issue_delegation_row"."truster_id"

781 AND "issue_id" = "issue_id_p"

782 ) AND NOT EXISTS (

783 SELECT NULL FROM "delegating_voter"

784 WHERE "member_id" = "issue_delegation_row"."truster_id"

785 AND "issue_id" = "issue_id_p"

786 ) THEN

787 "delegate_member_ids_v" :=

788 "member_id_p" || "delegate_member_ids_p";

789 INSERT INTO "delegating_voter" (

790 "issue_id",

791 "member_id",

792 "ownweight",

793 "scope",

794 "delegate_member_ids"

795 ) VALUES (

796 "issue_id_p",

797 "issue_delegation_row"."truster_id",

798 "issue_delegation_row"."weight",

799 "issue_delegation_row"."scope",

800 "delegate_member_ids_v"

801 );

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

803 "weight_of_added_vote_delegations"(

804 "issue_id_p",

805 "issue_delegation_row"."truster_id",

806 "delegate_member_ids_v"

807 );

808 UPDATE "delegating_voter"

809 SET "weight" = "sub_weight_v"

810 WHERE "issue_id" = "issue_id_p"

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

812 "weight_v" := "weight_v" + "sub_weight_v";

813 END IF;

814 END LOOP;

815 RETURN "weight_v";

816 END;

817 $$;

818

819 CREATE OR REPLACE FUNCTION "add_vote_delegations"

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

821 RETURNS VOID

822 LANGUAGE 'plpgsql' VOLATILE AS $$

823 DECLARE

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

825 BEGIN

826 PERFORM "require_transaction_isolation"();

827 FOR "member_id_v" IN

828 SELECT "member_id" FROM "direct_voter"

829 WHERE "issue_id" = "issue_id_p"

830 LOOP

831 UPDATE "direct_voter" SET

832 "weight" = "ownweight" + "weight_of_added_vote_delegations"(

833 "issue_id_p",

834 "member_id_v",

835 '{}'

836 )

837 WHERE "member_id" = "member_id_v"

838 AND "issue_id" = "issue_id_p";

839 END LOOP;

840 RETURN;

841 END;

842 $$;

843

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

845 RETURNS VOID

846 LANGUAGE 'plpgsql' VOLATILE AS $$

847 DECLARE

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

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

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

851 BEGIN

852 PERFORM "require_transaction_isolation"();

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

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

855 -- override protection triggers:

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

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

858 -- delete timestamp of voting comment:

859 UPDATE "direct_voter" SET "comment_changed" = NULL

860 WHERE "issue_id" = "issue_id_p";

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

862 DELETE FROM "delegating_voter"

863 WHERE "issue_id" = "issue_id_p";

864 -- delete votes from non-privileged voters:

865 DELETE FROM "direct_voter"

866 USING (

867 SELECT "direct_voter"."member_id"

868 FROM "direct_voter"

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

870 LEFT JOIN "privilege"

871 ON "privilege"."unit_id" = "unit_id_v"

872 AND "privilege"."member_id" = "direct_voter"."member_id"

873 LEFT JOIN "issue_privilege"

874 ON "issue_privilege"."issue_id" = "issue_id_p"

875 AND "issue_privilege"."member_id" = "direct_voter"."member_id"

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

877 "member"."active" = FALSE OR

878 COALESCE(

879 "issue_privilege"."voting_right",

880 "privilege"."voting_right",

881 FALSE

882 ) = FALSE

883 )

884 ) AS "subquery"

885 WHERE "direct_voter"."issue_id" = "issue_id_p"

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

887 -- consider voting weight and delegations:

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

889 FROM "privilege"

890 WHERE "issue_id" = "issue_id_p"

891 AND "privilege"."unit_id" = "unit_id_v"

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

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

894 FROM "issue_privilege"

895 WHERE "direct_voter"."issue_id" = "issue_id_p"

896 AND "issue_privilege"."issue_id" = "issue_id_p"

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

898 PERFORM "add_vote_delegations"("issue_id_p");

899 -- mark first preferences:

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

901 FROM (

902 SELECT

903 "vote"."initiative_id",

904 "vote"."member_id",

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

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

907 ELSE NULL

908 END AS "first_preference"

909 FROM "vote"

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

911 ON "vote"."issue_id" = "initiative"."issue_id"

912 JOIN "vote" AS "agg"

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

914 AND "vote"."member_id" = "agg"."member_id"

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

916 ) AS "subquery"

917 WHERE "vote"."issue_id" = "issue_id_p"

918 AND "vote"."initiative_id" = "subquery"."initiative_id"

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

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

921 DELETE FROM "temporary_transaction_data"

922 WHERE "key" = 'override_protection_triggers';

923 -- materialize battle_view:

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

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

926 INSERT INTO "battle" (

927 "issue_id",

928 "winning_initiative_id", "losing_initiative_id",

929 "count"

930 ) SELECT

931 "issue_id",

932 "winning_initiative_id", "losing_initiative_id",

933 "count"

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

935 -- set voter count:

936 UPDATE "issue" SET

937 "voter_count" = (

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

939 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"

940 )

941 WHERE "id" = "issue_id_p";

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

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

944 UPDATE "initiative" SET

945 "first_preference_votes" = 0,

946 "positive_votes" = "battle_win"."count",

947 "negative_votes" = "battle_lose"."count"

948 FROM "battle" AS "battle_win", "battle" AS "battle_lose"

949 WHERE

950 "battle_win"."issue_id" = "issue_id_p" AND

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

952 "battle_win"."losing_initiative_id" ISNULL AND

953 "battle_lose"."issue_id" = "issue_id_p" AND

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

955 "battle_lose"."winning_initiative_id" ISNULL;

956 -- calculate "first_preference_votes":

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

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

959 FROM (

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

961 FROM "vote" JOIN "direct_voter"

962 ON "vote"."issue_id" = "direct_voter"."issue_id"

963 AND "vote"."member_id" = "direct_voter"."member_id"

964 WHERE "vote"."first_preference"

965 GROUP BY "vote"."initiative_id"

966 ) AS "subquery"

967 WHERE "initiative"."issue_id" = "issue_id_p"

968 AND "initiative"."admitted"

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

970 END;

971 $$;

972

973 DROP INDEX "posting_global_idx";

974 DROP INDEX "posting_unit_idx";

975 DROP INDEX "posting_area_idx";

976 DROP INDEX "posting_policy_idx";

977 DROP INDEX "posting_issue_idx";

978 DROP INDEX "posting_initiative_idx";

979 DROP INDEX "posting_suggestion_idx";

980

981 DROP INDEX "posting_lexeme_idx";

982

983 DROP INDEX "event_tl_global_idx";

984 DROP INDEX "event_tl_unit_idx";

985 DROP INDEX "event_tl_area_idx";

986 DROP INDEX "event_tl_policy_idx";

987 DROP INDEX "event_tl_issue_idx";

988 DROP INDEX "event_tl_initiative_idx";

989 DROP INDEX "event_tl_suggestion_idx";

990

991 DROP EXTENSION IF EXISTS conflux;

992 DROP EXTENSION IF EXISTS btree_gist;

993

994 COMMIT;