liquid_feedback_core: 1e7e8b025346 update/core-update.v2.2.6-v3.0.1.sql

liquid_feedback_core

view update/core-update.v2.2.6-v3.0.1.sql @ 467:1e7e8b025346

Stub VIEW that selects issues which are to be included in notifications

author	jbe
date	Sat Mar 26 02:13:42 2016 +0100 (2016-03-26)
parents	34cc98defa8b
children

line source

1 BEGIN;

3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS

4 SELECT * FROM (VALUES ('3.0.1', 3, 0, 1))

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

7 CREATE TABLE "issue_order_in_admission_state" (

8 "id" INT8 PRIMARY KEY, --REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,

9 "order_in_area" INT4,

10 "order_in_unit" INT4 );

12 COMMENT ON TABLE "issue_order_in_admission_state" IS 'Ordering information for issues that are not stored in the "issue" table to avoid locking of multiple issues at once; Filled/updated by "lf_update_issue_order"';

14 COMMENT ON COLUMN "issue_order_in_admission_state"."id" IS 'References "issue" ("id") but has no referential integrity trigger associated, due to performance/locking issues';

15 COMMENT ON COLUMN "issue_order_in_admission_state"."order_in_area" IS 'Order of issues in admission state within a single area; NULL values sort last';

16 COMMENT ON COLUMN "issue_order_in_admission_state"."order_in_unit" IS 'Order of issues in admission state within all areas of a unit; NULL values sort last';

18 CREATE VIEW "issue_supporter_in_admission_state" AS

19 SELECT DISTINCT

20 "area"."unit_id",

21 "issue"."area_id",

22 "issue"."id" AS "issue_id",

23 "supporter"."member_id",

24 "direct_interest_snapshot"."weight"

25 FROM "issue"

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

27 JOIN "supporter" ON "supporter"."issue_id" = "issue"."id"

28 JOIN "direct_interest_snapshot"

29 ON "direct_interest_snapshot"."issue_id" = "issue"."id"

30 AND "direct_interest_snapshot"."event" = "issue"."latest_snapshot_event"

31 AND "direct_interest_snapshot"."member_id" = "supporter"."member_id"

32 WHERE "issue"."state" = 'admission'::"issue_state";

34 COMMENT ON VIEW "issue_supporter_in_admission_state" IS 'Helper view for "lf_update_issue_order" to allow a (proportional) ordering of issues within an area';

36 COMMENT ON COLUMN "initiative"."schulze_rank" IS 'Schulze-Ranking';

37 COMMENT ON COLUMN "initiative"."better_than_status_quo" IS 'TRUE, if initiative has a schulze-ranking better than the status quo';

38 COMMENT ON COLUMN "initiative"."worse_than_status_quo" IS 'TRUE, if initiative has a schulze-ranking worse than the status quo (DEPRECATED, since schulze-ranking is unique per issue; use "better_than_status_quo"=FALSE)';

39 COMMENT ON COLUMN "initiative"."winner" IS 'Winner is the "eligible" initiative with best "schulze_rank"';

41 CREATE OR REPLACE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE)

42 RETURNS VOID

43 LANGUAGE 'plpgsql' VOLATILE AS $$

44 DECLARE

45 "issue_row" "issue"%ROWTYPE;

46 "policy_row" "policy"%ROWTYPE;

47 "dimension_v" INTEGER;

48 "vote_matrix" INT4[][]; -- absolute votes

49 "matrix" INT8[][]; -- defeat strength / best paths

50 "i" INTEGER;

51 "j" INTEGER;

52 "k" INTEGER;

53 "battle_row" "battle"%ROWTYPE;

54 "rank_ary" INT4[];

55 "rank_v" INT4;

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

57 BEGIN

58 PERFORM "require_transaction_isolation"();

59 SELECT * INTO "issue_row"

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

61 SELECT * INTO "policy_row"

62 FROM "policy" WHERE "id" = "issue_row"."policy_id";

63 SELECT count(1) INTO "dimension_v"

64 FROM "battle_participant" WHERE "issue_id" = "issue_id_p";

65 -- Create "vote_matrix" with absolute number of votes in pairwise

66 -- comparison:

67 "vote_matrix" := array_fill(NULL::INT4, ARRAY["dimension_v", "dimension_v"]);

68 "i" := 1;

69 "j" := 2;

70 FOR "battle_row" IN

71 SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p"

72 ORDER BY

73 "winning_initiative_id" NULLS FIRST,

74 "losing_initiative_id" NULLS FIRST

75 LOOP

76 "vote_matrix"["i"]["j"] := "battle_row"."count";

77 IF "j" = "dimension_v" THEN

78 "i" := "i" + 1;

79 "j" := 1;

80 ELSE

81 "j" := "j" + 1;

82 IF "j" = "i" THEN

83 "j" := "j" + 1;

84 END IF;

85 END IF;

86 END LOOP;

87 IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN

88 RAISE EXCEPTION 'Wrong battle count (should not happen)';

89 END IF;

90 -- Store defeat strengths in "matrix" using "defeat_strength"

91 -- function:

92 "matrix" := array_fill(NULL::INT8, ARRAY["dimension_v", "dimension_v"]);

93 "i" := 1;

94 LOOP

95 "j" := 1;

96 LOOP

97 IF "i" != "j" THEN

98 "matrix"["i"]["j"] := "defeat_strength"(

99 "vote_matrix"["i"]["j"],

100 "vote_matrix"["j"]["i"]

101 );

102 END IF;

103 EXIT WHEN "j" = "dimension_v";

104 "j" := "j" + 1;

105 END LOOP;

106 EXIT WHEN "i" = "dimension_v";

107 "i" := "i" + 1;

108 END LOOP;

109 -- Find best paths:

110 "i" := 1;

111 LOOP

112 "j" := 1;

113 LOOP

114 IF "i" != "j" THEN

115 "k" := 1;

116 LOOP

117 IF "i" != "k" AND "j" != "k" THEN

118 IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN

119 IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN

120 "matrix"["j"]["k"] := "matrix"["j"]["i"];

121 END IF;

122 ELSE

123 IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN

124 "matrix"["j"]["k"] := "matrix"["i"]["k"];

125 END IF;

126 END IF;

127 END IF;

128 EXIT WHEN "k" = "dimension_v";

129 "k" := "k" + 1;

130 END LOOP;

131 END IF;

132 EXIT WHEN "j" = "dimension_v";

133 "j" := "j" + 1;

134 END LOOP;

135 EXIT WHEN "i" = "dimension_v";

136 "i" := "i" + 1;

137 END LOOP;

138 -- Determine order of winners:

139 "rank_ary" := array_fill(NULL::INT4, ARRAY["dimension_v"]);

140 "rank_v" := 1;

141 LOOP

142 "i" := 1;

143 LOOP

144 IF "rank_ary"["i"] ISNULL THEN

145 "j" := 1;

146 LOOP

147 IF

148 "i" != "j" AND

149 "rank_ary"["j"] ISNULL AND

150 ( "matrix"["j"]["i"] > "matrix"["i"]["j"] OR

151 -- tie-breaking by "id"

152 ( "matrix"["j"]["i"] = "matrix"["i"]["j"] AND

153 "j" < "i" ) )

154 THEN

155 -- someone else is better

156 EXIT;

157 END IF;

158 "j" := "j" + 1;

159 IF "j" = "dimension_v" + 1 THEN

160 -- noone is better

161 "rank_ary"["i"] := "rank_v";

162 EXIT;

163 END IF;

164 END LOOP;

165 EXIT WHEN "j" = "dimension_v" + 1;

166 END IF;

167 "i" := "i" + 1;

168 IF "i" > "dimension_v" THEN

169 RAISE EXCEPTION 'Schulze ranking does not compute (should not happen)';

170 END IF;

171 END LOOP;

172 EXIT WHEN "rank_v" = "dimension_v";

173 "rank_v" := "rank_v" + 1;

174 END LOOP;

175 -- write preliminary results:

176 "i" := 2; -- omit status quo with "i" = 1

177 FOR "initiative_id_v" IN

178 SELECT "id" FROM "initiative"

179 WHERE "issue_id" = "issue_id_p" AND "admitted"

180 ORDER BY "id"

181 LOOP

182 UPDATE "initiative" SET

183 "direct_majority" =

184 CASE WHEN "policy_row"."direct_majority_strict" THEN

185 "positive_votes" * "policy_row"."direct_majority_den" >

186 "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")

187 ELSE

188 "positive_votes" * "policy_row"."direct_majority_den" >=

189 "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")

190 END

191 AND "positive_votes" >= "policy_row"."direct_majority_positive"

192 AND "issue_row"."voter_count"-"negative_votes" >=

193 "policy_row"."direct_majority_non_negative",

194 "indirect_majority" =

195 CASE WHEN "policy_row"."indirect_majority_strict" THEN

196 "positive_votes" * "policy_row"."indirect_majority_den" >

197 "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")

198 ELSE

199 "positive_votes" * "policy_row"."indirect_majority_den" >=

200 "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")

201 END

202 AND "positive_votes" >= "policy_row"."indirect_majority_positive"

203 AND "issue_row"."voter_count"-"negative_votes" >=

204 "policy_row"."indirect_majority_non_negative",

205 "schulze_rank" = "rank_ary"["i"],

206 "better_than_status_quo" = "rank_ary"["i"] < "rank_ary"[1],

207 "worse_than_status_quo" = "rank_ary"["i"] > "rank_ary"[1],

208 "multistage_majority" = "rank_ary"["i"] >= "rank_ary"[1],

209 "reverse_beat_path" = "matrix"[1]["i"] >= 0,

210 "eligible" = FALSE,

211 "winner" = FALSE,

212 "rank" = NULL -- NOTE: in cases of manual reset of issue state

213 WHERE "id" = "initiative_id_v";

214 "i" := "i" + 1;

215 END LOOP;

216 IF "i" != "dimension_v" + 1 THEN

217 RAISE EXCEPTION 'Wrong winner count (should not happen)';

218 END IF;

219 -- take indirect majorities into account:

220 LOOP

221 UPDATE "initiative" SET "indirect_majority" = TRUE

222 FROM (

223 SELECT "new_initiative"."id" AS "initiative_id"

224 FROM "initiative" "old_initiative"

225 JOIN "initiative" "new_initiative"

226 ON "new_initiative"."issue_id" = "issue_id_p"

227 AND "new_initiative"."indirect_majority" = FALSE

228 JOIN "battle" "battle_win"

229 ON "battle_win"."issue_id" = "issue_id_p"

230 AND "battle_win"."winning_initiative_id" = "new_initiative"."id"

231 AND "battle_win"."losing_initiative_id" = "old_initiative"."id"

232 JOIN "battle" "battle_lose"

233 ON "battle_lose"."issue_id" = "issue_id_p"

234 AND "battle_lose"."losing_initiative_id" = "new_initiative"."id"

235 AND "battle_lose"."winning_initiative_id" = "old_initiative"."id"

236 WHERE "old_initiative"."issue_id" = "issue_id_p"

237 AND "old_initiative"."indirect_majority" = TRUE

238 AND CASE WHEN "policy_row"."indirect_majority_strict" THEN

239 "battle_win"."count" * "policy_row"."indirect_majority_den" >

240 "policy_row"."indirect_majority_num" *

241 ("battle_win"."count"+"battle_lose"."count")

242 ELSE

243 "battle_win"."count" * "policy_row"."indirect_majority_den" >=

244 "policy_row"."indirect_majority_num" *

245 ("battle_win"."count"+"battle_lose"."count")

246 END

247 AND "battle_win"."count" >= "policy_row"."indirect_majority_positive"

248 AND "issue_row"."voter_count"-"battle_lose"."count" >=

249 "policy_row"."indirect_majority_non_negative"

250 ) AS "subquery"

251 WHERE "id" = "subquery"."initiative_id";

252 EXIT WHEN NOT FOUND;

253 END LOOP;

254 -- set "multistage_majority" for remaining matching initiatives:

255 UPDATE "initiative" SET "multistage_majority" = TRUE

256 FROM (

257 SELECT "losing_initiative"."id" AS "initiative_id"

258 FROM "initiative" "losing_initiative"

259 JOIN "initiative" "winning_initiative"

260 ON "winning_initiative"."issue_id" = "issue_id_p"

261 AND "winning_initiative"."admitted"

262 JOIN "battle" "battle_win"

263 ON "battle_win"."issue_id" = "issue_id_p"

264 AND "battle_win"."winning_initiative_id" = "winning_initiative"."id"

265 AND "battle_win"."losing_initiative_id" = "losing_initiative"."id"

266 JOIN "battle" "battle_lose"

267 ON "battle_lose"."issue_id" = "issue_id_p"

268 AND "battle_lose"."losing_initiative_id" = "winning_initiative"."id"

269 AND "battle_lose"."winning_initiative_id" = "losing_initiative"."id"

270 WHERE "losing_initiative"."issue_id" = "issue_id_p"

271 AND "losing_initiative"."admitted"

272 AND "winning_initiative"."schulze_rank" <

273 "losing_initiative"."schulze_rank"

274 AND "battle_win"."count" > "battle_lose"."count"

275 AND (

276 "battle_win"."count" > "winning_initiative"."positive_votes" OR

277 "battle_lose"."count" < "losing_initiative"."negative_votes" )

278 ) AS "subquery"

279 WHERE "id" = "subquery"."initiative_id";

280 -- mark eligible initiatives:

281 UPDATE "initiative" SET "eligible" = TRUE

282 WHERE "issue_id" = "issue_id_p"

283 AND "initiative"."direct_majority"

284 AND "initiative"."indirect_majority"

285 AND "initiative"."better_than_status_quo"

286 AND (

287 "policy_row"."no_multistage_majority" = FALSE OR

288 "initiative"."multistage_majority" = FALSE )

289 AND (

290 "policy_row"."no_reverse_beat_path" = FALSE OR

291 "initiative"."reverse_beat_path" = FALSE );

292 -- mark final winner:

293 UPDATE "initiative" SET "winner" = TRUE

294 FROM (

295 SELECT "id" AS "initiative_id"

296 FROM "initiative"

297 WHERE "issue_id" = "issue_id_p" AND "eligible"

298 ORDER BY

299 "schulze_rank",

300 "id"

301 LIMIT 1

302 ) AS "subquery"

303 WHERE "id" = "subquery"."initiative_id";

304 -- write (final) ranks:

305 "rank_v" := 1;

306 FOR "initiative_id_v" IN

307 SELECT "id"

308 FROM "initiative"

309 WHERE "issue_id" = "issue_id_p" AND "admitted"

310 ORDER BY

311 "winner" DESC,

312 "eligible" DESC,

313 "schulze_rank",

314 "id"

315 LOOP

316 UPDATE "initiative" SET "rank" = "rank_v"

317 WHERE "id" = "initiative_id_v";

318 "rank_v" := "rank_v" + 1;

319 END LOOP;

320 -- set schulze rank of status quo and mark issue as finished:

321 UPDATE "issue" SET

322 "status_quo_schulze_rank" = "rank_ary"[1],

323 "state" =

324 CASE WHEN EXISTS (

325 SELECT NULL FROM "initiative"

326 WHERE "issue_id" = "issue_id_p" AND "winner"

327 ) THEN

328 'finished_with_winner'::"issue_state"

329 ELSE

330 'finished_without_winner'::"issue_state"

331 END,

332 "closed" = "phase_finished",

333 "phase_finished" = NULL

334 WHERE "id" = "issue_id_p";

335 RETURN;

336 END;

337 $$;

338

339 ALTER TABLE "initiative" ADD COLUMN "first_preference_votes" INT4;

340

341 ALTER TABLE "initiative" DROP CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results";

342 ALTER TABLE "initiative" ADD CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results" CHECK (

343 ( "admitted" NOTNULL AND "admitted" = TRUE ) OR

344 ( "first_preference_votes" ISNULL AND

345 "positive_votes" ISNULL AND "negative_votes" ISNULL AND

346 "direct_majority" ISNULL AND "indirect_majority" ISNULL AND

347 "schulze_rank" ISNULL AND

348 "better_than_status_quo" ISNULL AND "worse_than_status_quo" ISNULL AND

349 "reverse_beat_path" ISNULL AND "multistage_majority" ISNULL AND

350 "eligible" ISNULL AND "winner" ISNULL AND "rank" ISNULL ) );

351

352 COMMENT ON COLUMN "initiative"."first_preference_votes" IS 'Number of direct and delegating voters who ranked this initiative as their first choice';

353 COMMENT ON COLUMN "initiative"."positive_votes" IS 'Number of direct and delegating voters who ranked this initiative better than the status quo';

354 COMMENT ON COLUMN "initiative"."negative_votes" IS 'Number of direct and delegating voters who ranked this initiative worse than the status quo';

355

356 -- UPDATE TABLE "vote" SET "grade" = 0 WHERE "grade" ISNULL; -- should not be necessary

357 ALTER TABLE "vote" ALTER COLUMN "grade" SET NOT NULL;

358

359 ALTER TABLE "vote" ADD COLUMN "first_preference" BOOLEAN;

360

361 ALTER TABLE "vote" ADD

362 CONSTRAINT "first_preference_flag_only_set_on_positive_grades"

363 CHECK ("grade" > 0 OR "first_preference" ISNULL);

364

365 COMMENT ON COLUMN "vote"."first_preference" IS 'Value is automatically set after voting is finished. For positive grades, this value is set to true for the highest (i.e. best) grade.';

366

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

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

369

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

371 FROM (

372 SELECT

373 "vote"."initiative_id",

374 "vote"."member_id",

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

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

377 ELSE NULL

378 END AS "first_preference"

379 FROM "vote"

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

381 ON "vote"."issue_id" = "initiative"."issue_id"

382 JOIN "vote" AS "agg"

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

384 AND "vote"."member_id" = "agg"."member_id"

385 GROUP BY "vote"."initiative_id", "vote"."member_id"

386 ) AS "subquery"

387 WHERE "vote"."initiative_id" = "subquery"."initiative_id"

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

389

390 DELETE FROM "temporary_transaction_data"

391 WHERE "key" = 'override_protection_triggers';

392

393 UPDATE "initiative"

394 SET "first_preference_votes" = coalesce("subquery"."sum", 0)

395 FROM (

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

397 FROM "vote" JOIN "direct_voter"

398 ON "vote"."issue_id" = "direct_voter"."issue_id"

399 AND "vote"."member_id" = "direct_voter"."member_id"

400 WHERE "vote"."first_preference"

401 GROUP BY "vote"."initiative_id"

402 ) AS "subquery"

403 WHERE "initiative"."admitted"

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

405

406 -- reconstruct battle data (originating from LiquidFeedback Core before v2.0.0)

407 -- to avoid future data loss when executing "clean_issue" to delete voting data:

408 INSERT INTO "battle" (

409 "issue_id",

410 "winning_initiative_id",

411 "losing_initiative_id",

412 "count"

413 ) SELECT

414 "battle_view"."issue_id",

415 "battle_view"."winning_initiative_id",

416 "battle_view"."losing_initiative_id",

417 "battle_view"."count"

418 FROM (

419 SELECT

420 "issue"."id" AS "issue_id",

421 "winning_initiative"."id" AS "winning_initiative_id",

422 "losing_initiative"."id" AS "losing_initiative_id",

423 sum(

424 CASE WHEN

425 coalesce("better_vote"."grade", 0) >

426 coalesce("worse_vote"."grade", 0)

427 THEN "direct_voter"."weight" ELSE 0 END

428 ) AS "count"

429 FROM "issue"

430 LEFT JOIN "direct_voter"

431 ON "issue"."id" = "direct_voter"."issue_id"

432 JOIN "battle_participant" AS "winning_initiative"

433 ON "issue"."id" = "winning_initiative"."issue_id"

434 JOIN "battle_participant" AS "losing_initiative"

435 ON "issue"."id" = "losing_initiative"."issue_id"

436 LEFT JOIN "vote" AS "better_vote"

437 ON "direct_voter"."member_id" = "better_vote"."member_id"

438 AND "winning_initiative"."id" = "better_vote"."initiative_id"

439 LEFT JOIN "vote" AS "worse_vote"

440 ON "direct_voter"."member_id" = "worse_vote"."member_id"

441 AND "losing_initiative"."id" = "worse_vote"."initiative_id"

442 WHERE "issue"."state" IN ('finished_with_winner', 'finished_without_winner')

443 AND "winning_initiative"."id" != "losing_initiative"."id"

444 -- NOTE: comparisons with status-quo are intentionally omitted to mark

445 -- issues that were counted prior LiquidFeedback Core v2.0.0

446 GROUP BY

447 "issue"."id",

448 "winning_initiative"."id",

449 "losing_initiative"."id"

450 ) AS "battle_view"

451 LEFT JOIN "battle"

452 ON "battle_view"."winning_initiative_id" = "battle"."winning_initiative_id"

453 AND "battle_view"."losing_initiative_id" = "battle"."losing_initiative_id"

454 WHERE "battle" ISNULL;

455

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

457 RETURNS VOID

458 LANGUAGE 'plpgsql' VOLATILE AS $$

459 DECLARE

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

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

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

463 BEGIN

464 PERFORM "require_transaction_isolation"();

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

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

467 -- override protection triggers:

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

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

470 -- delete timestamp of voting comment:

471 UPDATE "direct_voter" SET "comment_changed" = NULL

472 WHERE "issue_id" = "issue_id_p";

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

474 DELETE FROM "delegating_voter"

475 WHERE "issue_id" = "issue_id_p";

476 -- delete votes from non-privileged voters:

477 DELETE FROM "direct_voter"

478 USING (

479 SELECT

480 "direct_voter"."member_id"

481 FROM "direct_voter"

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

483 LEFT JOIN "privilege"

484 ON "privilege"."unit_id" = "unit_id_v"

485 AND "privilege"."member_id" = "direct_voter"."member_id"

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

487 "member"."active" = FALSE OR

488 "privilege"."voting_right" ISNULL OR

489 "privilege"."voting_right" = FALSE

490 )

491 ) AS "subquery"

492 WHERE "direct_voter"."issue_id" = "issue_id_p"

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

494 -- consider delegations:

495 UPDATE "direct_voter" SET "weight" = 1

496 WHERE "issue_id" = "issue_id_p";

497 PERFORM "add_vote_delegations"("issue_id_p");

498 -- mark first preferences:

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

500 FROM (

501 SELECT

502 "vote"."initiative_id",

503 "vote"."member_id",

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

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

506 ELSE NULL

507 END AS "first_preference"

508 FROM "vote"

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

510 ON "vote"."issue_id" = "initiative"."issue_id"

511 JOIN "vote" AS "agg"

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

513 AND "vote"."member_id" = "agg"."member_id"

514 GROUP BY "vote"."initiative_id", "vote"."member_id"

515 ) AS "subquery"

516 WHERE "vote"."issue_id" = "issue_id_p"

517 AND "vote"."initiative_id" = "subquery"."initiative_id"

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

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

520 DELETE FROM "temporary_transaction_data"

521 WHERE "key" = 'override_protection_triggers';

522 -- materialize battle_view:

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

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

525 INSERT INTO "battle" (

526 "issue_id",

527 "winning_initiative_id", "losing_initiative_id",

528 "count"

529 ) SELECT

530 "issue_id",

531 "winning_initiative_id", "losing_initiative_id",

532 "count"

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

534 -- set voter count:

535 UPDATE "issue" SET

536 "voter_count" = (

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

538 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"

539 )

540 WHERE "id" = "issue_id_p";

541 -- calculate "first_preference_votes":

542 UPDATE "initiative"

543 SET "first_preference_votes" = coalesce("subquery"."sum", 0)

544 FROM (

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

546 FROM "vote" JOIN "direct_voter"

547 ON "vote"."issue_id" = "direct_voter"."issue_id"

548 AND "vote"."member_id" = "direct_voter"."member_id"

549 WHERE "vote"."first_preference"

550 GROUP BY "vote"."initiative_id"

551 ) AS "subquery"

552 WHERE "initiative"."issue_id" = "issue_id_p"

553 AND "initiative"."admitted"

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

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

556 UPDATE "initiative" SET

557 "positive_votes" = "battle_win"."count",

558 "negative_votes" = "battle_lose"."count"

559 FROM "battle" AS "battle_win", "battle" AS "battle_lose"

560 WHERE

561 "battle_win"."issue_id" = "issue_id_p" AND

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

563 "battle_win"."losing_initiative_id" ISNULL AND

564 "battle_lose"."issue_id" = "issue_id_p" AND

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

566 "battle_lose"."winning_initiative_id" ISNULL;

567 END;

568 $$;

569

570 COMMIT;