liquid_feedback_core: 607e8f5d66eb update/core-update.v2.0.8-v2.0.9.sql

liquid_feedback_core

view update/core-update.v2.0.8-v2.0.9.sql @ 341:607e8f5d66eb

Write "NOW()" as "now()"

author	jbe
date	Thu Feb 21 16:40:17 2013 +0100 (2013-02-21)
parents	7b8966b801e5
children

line source

1 BEGIN;

3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS

4 SELECT * FROM (VALUES ('2.0.9', 2, 0, 9))

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

7 -- Bugfix of error in update script to v2.0.0:

8 ALTER TABLE "battle" ALTER COLUMN "winning_initiative_id" DROP NOT NULL;

9 ALTER TABLE "battle" ALTER COLUMN "losing_initiative_id" DROP NOT NULL;

11 CREATE OR REPLACE VIEW "unit_member_count" AS

12 SELECT

13 "unit"."id" AS "unit_id",

14 count("member"."id") AS "member_count"

15 FROM "unit"

16 LEFT JOIN "privilege"

17 ON "privilege"."unit_id" = "unit"."id"

18 AND "privilege"."voting_right"

19 LEFT JOIN "member"

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

21 AND "member"."active"

22 GROUP BY "unit"."id";

24 COMMENT ON TYPE "delegation_chain_row" IS 'Type of rows returned by "delegation_chain" function';

26 CREATE FUNCTION "delegation_chain_for_closed_issue"

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

28 "issue_id_p" "issue"."id"%TYPE )

29 RETURNS SETOF "delegation_chain_row"

30 LANGUAGE 'plpgsql' STABLE AS $$

31 DECLARE

32 "output_row" "delegation_chain_row";

33 "direct_voter_row" "direct_voter"%ROWTYPE;

34 "delegating_voter_row" "delegating_voter"%ROWTYPE;

35 BEGIN

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

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

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

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

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

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

42 LOOP

43 SELECT INTO "direct_voter_row" * FROM "direct_voter"

44 WHERE "issue_id" = "issue_id_p"

45 AND "member_id" = "output_row"."member_id";

46 IF "direct_voter_row"."member_id" NOTNULL THEN

47 "output_row"."participation" := TRUE;

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

49 "output_row"."disabled_out" := NULL;

50 RETURN NEXT "output_row";

51 RETURN;

52 END IF;

53 SELECT INTO "delegating_voter_row" * FROM "delegating_voter"

54 WHERE "issue_id" = "issue_id_p"

55 AND "member_id" = "output_row"."member_id";

56 IF "delegating_voter_row"."member_id" ISNULL THEN

57 RETURN;

58 END IF;

59 "output_row"."scope_out" := "delegating_voter_row"."scope";

60 RETURN NEXT "output_row";

61 "output_row"."member_id" := "delegating_voter_row"."delegate_member_ids"[1];

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

63 END LOOP;

64 END;

65 $$;

67 COMMENT ON FUNCTION "delegation_chain_for_closed_issue"

68 ( "member"."id"%TYPE,

69 "member"."id"%TYPE )

70 IS 'Helper function for "delegation_chain" function, handling the special case of closed issues after voting';

72 DROP FUNCTION "delegation_chain"

73 ( "member"."id"%TYPE,

74 "unit"."id"%TYPE,

75 "area"."id"%TYPE,

76 "issue"."id"%TYPE );

78 DROP FUNCTION "delegation_chain"

79 ( "member"."id"%TYPE,

80 "unit"."id"%TYPE,

81 "area"."id"%TYPE,

82 "issue"."id"%TYPE,

83 "member"."id"%TYPE );

85 CREATE FUNCTION "delegation_chain"

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

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

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

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

90 "simulate_trustee_id_p" "member"."id"%TYPE DEFAULT NULL )

91 RETURNS SETOF "delegation_chain_row"

92 LANGUAGE 'plpgsql' STABLE AS $$

93 DECLARE

94 "scope_v" "delegation_scope";

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

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

97 "issue_row" "issue"%ROWTYPE;

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

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

100 "output_row" "delegation_chain_row";

101 "output_rows" "delegation_chain_row"[];

102 "delegation_row" "delegation"%ROWTYPE;

103 "row_count" INT4;

104 "i" INT4;

105 "loop_v" BOOLEAN;

106 BEGIN

107 IF

108 "unit_id_p" NOTNULL AND

109 "area_id_p" ISNULL AND

110 "issue_id_p" ISNULL

111 THEN

112 "scope_v" := 'unit';

113 "unit_id_v" := "unit_id_p";

114 ELSIF

115 "unit_id_p" ISNULL AND

116 "area_id_p" NOTNULL AND

117 "issue_id_p" ISNULL

118 THEN

119 "scope_v" := 'area';

120 "area_id_v" := "area_id_p";

121 SELECT "unit_id" INTO "unit_id_v"

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

123 ELSIF

124 "unit_id_p" ISNULL AND

125 "area_id_p" ISNULL AND

126 "issue_id_p" NOTNULL

127 THEN

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

129 IF "issue_row"."id" ISNULL THEN

130 RETURN;

131 END IF;

132 IF "issue_row"."closed" NOTNULL THEN

133 IF "simulate_trustee_id_p" NOTNULL THEN

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

135 END IF;

136 FOR "output_row" IN

137 SELECT * FROM

138 "delegation_chain_for_closed_issue"("member_id_p", "issue_id_p")

139 LOOP

140 RETURN NEXT "output_row";

141 END LOOP;

142 RETURN;

143 END IF;

144 "scope_v" := 'issue';

145 SELECT "area_id" INTO "area_id_v"

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

147 SELECT "unit_id" INTO "unit_id_v"

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

149 ELSE

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

151 END IF;

152 "visited_member_ids" := '{}';

153 "loop_member_id_v" := NULL;

154 "output_rows" := '{}';

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

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

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

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

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

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

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

162 LOOP

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

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

165 ELSE

166 "visited_member_ids" :=

167 "visited_member_ids" || "output_row"."member_id";

168 END IF;

169 IF "output_row"."participation" ISNULL THEN

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

171 ELSIF "output_row"."participation" THEN

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

173 END IF;

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

175 IF EXISTS (

176 SELECT NULL FROM "member" JOIN "privilege"

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

178 AND "privilege"."unit_id" = "unit_id_v"

179 WHERE "id" = "output_row"."member_id"

180 AND "member"."active" AND "privilege"."voting_right"

181 ) THEN

182 IF "scope_v" = 'unit' THEN

183 SELECT * INTO "delegation_row" FROM "delegation"

184 WHERE "truster_id" = "output_row"."member_id"

185 AND "unit_id" = "unit_id_v";

186 ELSIF "scope_v" = 'area' THEN

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

188 SELECT NULL FROM "membership"

189 WHERE "area_id" = "area_id_p"

190 AND "member_id" = "output_row"."member_id"

191 );

192 SELECT * INTO "delegation_row" FROM "delegation"

193 WHERE "truster_id" = "output_row"."member_id"

194 AND (

195 "unit_id" = "unit_id_v" OR

196 "area_id" = "area_id_v"

197 )

198 ORDER BY "scope" DESC;

199 ELSIF "scope_v" = 'issue' THEN

200 IF "issue_row"."fully_frozen" ISNULL THEN

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

202 SELECT NULL FROM "interest"

203 WHERE "issue_id" = "issue_id_p"

204 AND "member_id" = "output_row"."member_id"

205 );

206 ELSE

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

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

209 SELECT NULL FROM "direct_voter"

210 WHERE "issue_id" = "issue_id_p"

211 AND "member_id" = "output_row"."member_id"

212 );

213 ELSE

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

215 END IF;

216 END IF;

217 SELECT * INTO "delegation_row" FROM "delegation"

218 WHERE "truster_id" = "output_row"."member_id"

219 AND (

220 "unit_id" = "unit_id_v" OR

221 "area_id" = "area_id_v" OR

222 "issue_id" = "issue_id_p"

223 )

224 ORDER BY "scope" DESC;

225 END IF;

226 ELSE

227 "output_row"."member_valid" := FALSE;

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

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

230 "delegation_row" := ROW(NULL);

231 END IF;

232 IF

233 "output_row"."member_id" = "member_id_p" AND

234 "simulate_trustee_id_p" NOTNULL

235 THEN

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

237 "output_rows" := "output_rows" || "output_row";

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

239 ELSIF "delegation_row"."trustee_id" NOTNULL THEN

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

241 "output_rows" := "output_rows" || "output_row";

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

243 ELSIF "delegation_row"."scope" NOTNULL THEN

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

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

246 "output_rows" := "output_rows" || "output_row";

247 EXIT;

248 ELSE

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

250 "output_rows" := "output_rows" || "output_row";

251 EXIT;

252 END IF;

253 EXIT WHEN "loop_member_id_v" NOTNULL;

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

255 END LOOP;

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

257 "i" := 1;

258 "loop_v" := FALSE;

259 LOOP

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

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

262 IF "loop_v" THEN

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

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

265 ELSIF "i" = "row_count" THEN

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

267 ELSE

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

269 END IF;

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

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

272 "loop_v" := TRUE;

273 END IF;

274 IF "scope_v" = 'unit' THEN

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

276 END IF;

277 RETURN NEXT "output_row";

278 "i" := "i" + 1;

279 END LOOP;

280 RETURN;

281 END;

282 $$;

283

284 COMMENT ON FUNCTION "delegation_chain"

285 ( "member"."id"%TYPE,

286 "unit"."id"%TYPE,

287 "area"."id"%TYPE,

288 "issue"."id"%TYPE,

289 "member"."id"%TYPE )

290 IS 'Shows a delegation chain for unit, area, or issue; See "delegation_chain_row" type for more information';

291

292 CREATE TYPE "delegation_info_loop_type" AS ENUM

293 ('own', 'first', 'first_ellipsis', 'other', 'other_ellipsis');

294

295 COMMENT ON TYPE "delegation_info_loop_type" IS 'Type of "delegation_loop" in "delegation_info_type"; ''own'' means loop to self, ''first'' means loop to first trustee, ''first_ellipsis'' means loop to ellipsis after first trustee, ''other'' means loop to other trustee, ''other_ellipsis'' means loop to ellipsis after other trustee''';

296

297 CREATE TYPE "delegation_info_type" AS (

298 "own_participation" BOOLEAN,

299 "own_delegation_scope" "delegation_scope",

300 "first_trustee_id" INT4,

301 "first_trustee_participation" BOOLEAN,

302 "first_trustee_ellipsis" BOOLEAN,

303 "other_trustee_id" INT4,

304 "other_trustee_participation" BOOLEAN,

305 "other_trustee_ellipsis" BOOLEAN,

306 "delegation_loop" "delegation_info_loop_type");

307

308 COMMENT ON TYPE "delegation_info_type" IS 'Type of result returned by "delegation_info" function; For meaning of "participation" check comment on "delegation_chain_row" type';

309

310 COMMENT ON COLUMN "delegation_info_type"."own_participation" IS 'Member is directly participating';

311 COMMENT ON COLUMN "delegation_info_type"."own_delegation_scope" IS 'Delegation scope of member';

312 COMMENT ON COLUMN "delegation_info_type"."first_trustee_id" IS 'Direct trustee of member';

313 COMMENT ON COLUMN "delegation_info_type"."first_trustee_participation" IS 'Direct trustee of member is participating';

314 COMMENT ON COLUMN "delegation_info_type"."first_trustee_ellipsis" IS 'Ellipsis in delegation chain after "first_trustee"';

315 COMMENT ON COLUMN "delegation_info_type"."other_trustee_id" IS 'Another relevant trustee (due to participation)';

316 COMMENT ON COLUMN "delegation_info_type"."other_trustee_participation" IS 'Another trustee is participating (redundant field: if "other_trustee_id" is set, then "other_trustee_participation" is always TRUE, else "other_trustee_participation" is NULL)';

317 COMMENT ON COLUMN "delegation_info_type"."other_trustee_ellipsis" IS 'Ellipsis in delegation chain after "other_trustee"';

318 COMMENT ON COLUMN "delegation_info_type"."delegation_loop" IS 'Non-NULL value, if delegation chain contains a circle; See comment on "delegation_info_loop_type" for details';

319

320 CREATE FUNCTION "delegation_info"

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

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

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

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

325 "simulate_trustee_id_p" "member"."id"%TYPE DEFAULT NULL )

326 RETURNS "delegation_info_type"

327 LANGUAGE 'plpgsql' STABLE AS $$

328 DECLARE

329 "current_row" "delegation_chain_row";

330 "result" "delegation_info_type";

331 BEGIN

332 "result"."own_participation" := FALSE;

333 FOR "current_row" IN

334 SELECT * FROM "delegation_chain"(

335 "member_id_p",

336 "unit_id_p", "area_id_p", "issue_id_p",

337 "simulate_trustee_id_p")

338 LOOP

339 IF "current_row"."member_id" = "member_id_p" THEN

340 "result"."own_participation" := "current_row"."participation";

341 "result"."own_delegation_scope" := "current_row"."scope_out";

342 IF "current_row"."loop" = 'first' THEN

343 "result"."delegation_loop" := 'own';

344 END IF;

345 ELSIF

346 "current_row"."member_valid" AND

347 ( "current_row"."loop" ISNULL OR

348 "current_row"."loop" != 'repetition' )

349 THEN

350 IF "result"."first_trustee_id" ISNULL THEN

351 "result"."first_trustee_id" := "current_row"."member_id";

352 "result"."first_trustee_participation" := "current_row"."participation";

353 "result"."first_trustee_ellipsis" := FALSE;

354 IF "current_row"."loop" = 'first' THEN

355 "result"."delegation_loop" := 'first';

356 END IF;

357 ELSIF "result"."other_trustee_id" ISNULL THEN

358 IF "current_row"."participation" AND NOT "current_row"."overridden" THEN

359 "result"."other_trustee_id" := "current_row"."member_id";

360 "result"."other_trustee_participation" := TRUE;

361 "result"."other_trustee_ellipsis" := FALSE;

362 IF "current_row"."loop" = 'first' THEN

363 "result"."delegation_loop" := 'other';

364 END IF;

365 ELSE

366 "result"."first_trustee_ellipsis" := TRUE;

367 IF "current_row"."loop" = 'first' THEN

368 "result"."delegation_loop" := 'first_ellipsis';

369 END IF;

370 END IF;

371 ELSE

372 "result"."other_trustee_ellipsis" := TRUE;

373 IF "current_row"."loop" = 'first' THEN

374 "result"."delegation_loop" := 'other_ellipsis';

375 END IF;

376 END IF;

377 END IF;

378 END LOOP;

379 RETURN "result";

380 END;

381 $$;

382

383 COMMENT ON FUNCTION "delegation_info"

384 ( "member"."id"%TYPE,

385 "unit"."id"%TYPE,

386 "area"."id"%TYPE,

387 "issue"."id"%TYPE,

388 "member"."id"%TYPE )

389 IS 'Notable information about a delegation chain for unit, area, or issue; See "delegation_info_type" for more information';

390

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

392 RETURNS VOID

393 LANGUAGE 'plpgsql' VOLATILE AS $$

394 DECLARE

395 "issue_row" "issue"%ROWTYPE;

396 "policy_row" "policy"%ROWTYPE;

397 "dimension_v" INTEGER;

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

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

400 "i" INTEGER;

401 "j" INTEGER;

402 "k" INTEGER;

403 "battle_row" "battle"%ROWTYPE;

404 "rank_ary" INT4[];

405 "rank_v" INT4;

406 "done_v" INTEGER;

407 "winners_ary" INTEGER[];

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

409 BEGIN

410 SELECT * INTO "issue_row"

411 FROM "issue" WHERE "id" = "issue_id_p"

412 FOR UPDATE;

413 SELECT * INTO "policy_row"

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

415 SELECT count(1) INTO "dimension_v"

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

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

418 -- comparison:

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

420 "i" := 1;

421 "j" := 2;

422 FOR "battle_row" IN

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

424 ORDER BY

425 "winning_initiative_id" NULLS LAST,

426 "losing_initiative_id" NULLS LAST

427 LOOP

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

429 IF "j" = "dimension_v" THEN

430 "i" := "i" + 1;

431 "j" := 1;

432 ELSE

433 "j" := "j" + 1;

434 IF "j" = "i" THEN

435 "j" := "j" + 1;

436 END IF;

437 END IF;

438 END LOOP;

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

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

441 END IF;

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

443 -- function:

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

445 "i" := 1;

446 LOOP

447 "j" := 1;

448 LOOP

449 IF "i" != "j" THEN

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

451 "vote_matrix"["i"]["j"],

452 "vote_matrix"["j"]["i"]

453 );

454 END IF;

455 EXIT WHEN "j" = "dimension_v";

456 "j" := "j" + 1;

457 END LOOP;

458 EXIT WHEN "i" = "dimension_v";

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

460 END LOOP;

461 -- Find best paths:

462 "i" := 1;

463 LOOP

464 "j" := 1;

465 LOOP

466 IF "i" != "j" THEN

467 "k" := 1;

468 LOOP

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

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

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

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

473 END IF;

474 ELSE

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

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

477 END IF;

478 END IF;

479 END IF;

480 EXIT WHEN "k" = "dimension_v";

481 "k" := "k" + 1;

482 END LOOP;

483 END IF;

484 EXIT WHEN "j" = "dimension_v";

485 "j" := "j" + 1;

486 END LOOP;

487 EXIT WHEN "i" = "dimension_v";

488 "i" := "i" + 1;

489 END LOOP;

490 -- Determine order of winners:

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

492 "rank_v" := 1;

493 "done_v" := 0;

494 LOOP

495 "winners_ary" := '{}';

496 "i" := 1;

497 LOOP

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

499 "j" := 1;

500 LOOP

501 IF

502 "i" != "j" AND

503 "rank_ary"["j"] ISNULL AND

504 "matrix"["j"]["i"] > "matrix"["i"]["j"]

505 THEN

506 -- someone else is better

507 EXIT;

508 END IF;

509 IF "j" = "dimension_v" THEN

510 -- noone is better

511 "winners_ary" := "winners_ary" || "i";

512 EXIT;

513 END IF;

514 "j" := "j" + 1;

515 END LOOP;

516 END IF;

517 EXIT WHEN "i" = "dimension_v";

518 "i" := "i" + 1;

519 END LOOP;

520 "i" := 1;

521 LOOP

522 "rank_ary"["winners_ary"["i"]] := "rank_v";

523 "done_v" := "done_v" + 1;

524 EXIT WHEN "i" = array_upper("winners_ary", 1);

525 "i" := "i" + 1;

526 END LOOP;

527 EXIT WHEN "done_v" = "dimension_v";

528 "rank_v" := "rank_v" + 1;

529 END LOOP;

530 -- write preliminary results:

531 "i" := 1;

532 FOR "initiative_id_v" IN

533 SELECT "id" FROM "initiative"

534 WHERE "issue_id" = "issue_id_p" AND "admitted"

535 ORDER BY "id"

536 LOOP

537 UPDATE "initiative" SET

538 "direct_majority" =

539 CASE WHEN "policy_row"."direct_majority_strict" THEN

540 "positive_votes" * "policy_row"."direct_majority_den" >

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

542 ELSE

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

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

545 END

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

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

548 "policy_row"."direct_majority_non_negative",

549 "indirect_majority" =

550 CASE WHEN "policy_row"."indirect_majority_strict" THEN

551 "positive_votes" * "policy_row"."indirect_majority_den" >

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

553 ELSE

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

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

556 END

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

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

559 "policy_row"."indirect_majority_non_negative",

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

561 "better_than_status_quo" = "rank_ary"["i"] < "rank_ary"["dimension_v"],

562 "worse_than_status_quo" = "rank_ary"["i"] > "rank_ary"["dimension_v"],

563 "multistage_majority" = "rank_ary"["i"] >= "rank_ary"["dimension_v"],

564 "reverse_beat_path" = "matrix"["dimension_v"]["i"] >= 0,

565 "eligible" = FALSE,

566 "winner" = FALSE,

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

568 WHERE "id" = "initiative_id_v";

569 "i" := "i" + 1;

570 END LOOP;

571 IF "i" != "dimension_v" THEN

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

573 END IF;

574 -- take indirect majorities into account:

575 LOOP

576 UPDATE "initiative" SET "indirect_majority" = TRUE

577 FROM (

578 SELECT "new_initiative"."id" AS "initiative_id"

579 FROM "initiative" "old_initiative"

580 JOIN "initiative" "new_initiative"

581 ON "new_initiative"."issue_id" = "issue_id_p"

582 AND "new_initiative"."indirect_majority" = FALSE

583 JOIN "battle" "battle_win"

584 ON "battle_win"."issue_id" = "issue_id_p"

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

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

587 JOIN "battle" "battle_lose"

588 ON "battle_lose"."issue_id" = "issue_id_p"

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

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

591 WHERE "old_initiative"."issue_id" = "issue_id_p"

592 AND "old_initiative"."indirect_majority" = TRUE

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

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

595 "policy_row"."indirect_majority_num" *

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

597 ELSE

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

599 "policy_row"."indirect_majority_num" *

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

601 END

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

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

604 "policy_row"."indirect_majority_non_negative"

605 ) AS "subquery"

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

607 EXIT WHEN NOT FOUND;

608 END LOOP;

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

610 UPDATE "initiative" SET "multistage_majority" = TRUE

611 FROM (

612 SELECT "losing_initiative"."id" AS "initiative_id"

613 FROM "initiative" "losing_initiative"

614 JOIN "initiative" "winning_initiative"

615 ON "winning_initiative"."issue_id" = "issue_id_p"

616 AND "winning_initiative"."admitted"

617 JOIN "battle" "battle_win"

618 ON "battle_win"."issue_id" = "issue_id_p"

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

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

621 JOIN "battle" "battle_lose"

622 ON "battle_lose"."issue_id" = "issue_id_p"

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

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

625 WHERE "losing_initiative"."issue_id" = "issue_id_p"

626 AND "losing_initiative"."admitted"

627 AND "winning_initiative"."schulze_rank" <

628 "losing_initiative"."schulze_rank"

629 AND "battle_win"."count" > "battle_lose"."count"

630 AND (

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

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

633 ) AS "subquery"

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

635 -- mark eligible initiatives:

636 UPDATE "initiative" SET "eligible" = TRUE

637 WHERE "issue_id" = "issue_id_p"

638 AND "initiative"."direct_majority"

639 AND "initiative"."indirect_majority"

640 AND "initiative"."better_than_status_quo"

641 AND (

642 "policy_row"."no_multistage_majority" = FALSE OR

643 "initiative"."multistage_majority" = FALSE )

644 AND (

645 "policy_row"."no_reverse_beat_path" = FALSE OR

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

647 -- mark final winner:

648 UPDATE "initiative" SET "winner" = TRUE

649 FROM (

650 SELECT "id" AS "initiative_id"

651 FROM "initiative"

652 WHERE "issue_id" = "issue_id_p" AND "eligible"

653 ORDER BY

654 "schulze_rank",

655 "vote_ratio"("positive_votes", "negative_votes"),

656 "id"

657 LIMIT 1

658 ) AS "subquery"

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

660 -- write (final) ranks:

661 "rank_v" := 1;

662 FOR "initiative_id_v" IN

663 SELECT "id"

664 FROM "initiative"

665 WHERE "issue_id" = "issue_id_p" AND "admitted"

666 ORDER BY

667 "winner" DESC,

668 "eligible" DESC,

669 "schulze_rank",

670 "vote_ratio"("positive_votes", "negative_votes"),

671 "id"

672 LOOP

673 UPDATE "initiative" SET "rank" = "rank_v"

674 WHERE "id" = "initiative_id_v";

675 "rank_v" := "rank_v" + 1;

676 END LOOP;

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

678 UPDATE "issue" SET

679 "status_quo_schulze_rank" = "rank_ary"["dimension_v"],

680 "state" =

681 CASE WHEN EXISTS (

682 SELECT NULL FROM "initiative"

683 WHERE "issue_id" = "issue_id_p" AND "winner"

684 ) THEN

685 'finished_with_winner'::"issue_state"

686 ELSE

687 'finished_without_winner'::"issue_state"

688 END,

689 "ranks_available" = TRUE

690 WHERE "id" = "issue_id_p";

691 RETURN;

692 END;

693 $$;

694

695 COMMIT;