liquid_feedback_core: 1024224df943 update/core-update.v1.4.0-v1.5.0.sql

liquid_feedback_core

view update/core-update.v1.4.0-v1.5.0.sql @ 180:1024224df943

Update script to v1.5.0: Drop contraints on table "initiative" before changing columns

author	jbe
date	Wed Jun 08 20:53:36 2011 +0200 (2011-06-08)
parents	fb2394116b39
children	4c03ef70dd92

line source

1 SELECT "calculate_ranks"("id") FROM "issue_with_ranks_missing";

3 BEGIN;

5 CREATE OR REPLACE VIEW "liquid_feedback_version" AS

6 SELECT * FROM (VALUES ('1.5.0_devel', 1, 5, -1))

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

9 ALTER TABLE "member" ADD COLUMN "formatting_engine" TEXT;

10 COMMENT ON COLUMN "member"."formatting_engine" IS 'Allows different formatting engines (i.e. wiki formats) to be used for "member"."statement"';

12 CREATE TABLE "rendered_member_statement" (

13 PRIMARY KEY ("member_id", "format"),

14 "member_id" INT8 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,

15 "format" TEXT,

16 "content" TEXT NOT NULL );

18 COMMENT ON TABLE "rendered_member_statement" IS 'This table may be used by frontends to cache "rendered" member statements (e.g. HTML output generated from wiki text)';

20 ALTER TABLE "policy" ADD COLUMN "direct_majority_num" INT4 NOT NULL DEFAULT 1;

21 ALTER TABLE "policy" ADD COLUMN "direct_majority_den" INT4 NOT NULL DEFAULT 2;

22 ALTER TABLE "policy" ADD COLUMN "direct_majority_strict" BOOLEAN NOT NULL DEFAULT TRUE;

23 ALTER TABLE "policy" ADD COLUMN "direct_majority_positive" INT4 NOT NULL DEFAULT 0;

24 ALTER TABLE "policy" ADD COLUMN "direct_majority_non_negative" INT4 NOT NULL DEFAULT 0;

25 ALTER TABLE "policy" ADD COLUMN "indirect_majority_num" INT4 NOT NULL DEFAULT 1;

26 ALTER TABLE "policy" ADD COLUMN "indirect_majority_den" INT4 NOT NULL DEFAULT 2;

27 ALTER TABLE "policy" ADD COLUMN "indirect_majority_strict" BOOLEAN NOT NULL DEFAULT TRUE;

28 ALTER TABLE "policy" ADD COLUMN "indirect_majority_positive" INT4 NOT NULL DEFAULT 0;

29 ALTER TABLE "policy" ADD COLUMN "indirect_majority_non_negative" INT4 NOT NULL DEFAULT 0;

30 ALTER TABLE "policy" ADD COLUMN "no_reverse_beat_path" BOOLEAN NOT NULL DEFAULT TRUE;

31 ALTER TABLE "policy" ADD COLUMN "no_multistage_majority" BOOLEAN NOT NULL DEFAULT FALSE;

33 UPDATE "policy" SET

34 "direct_majority_num" = "majority_num",

35 "direct_majority_den" = "majority_den",

36 "direct_majority_strict" = "majority_strict",

37 "indirect_majority_num" = "majority_num",

38 "indirect_majority_den" = "majority_den",

39 "indirect_majority_strict" = "majority_strict";

41 ALTER TABLE "policy" DROP COLUMN "majority_num";

42 ALTER TABLE "policy" DROP COLUMN "majority_den";

43 ALTER TABLE "policy" DROP COLUMN "majority_strict";

45 COMMENT ON COLUMN "policy"."direct_majority_num" IS 'Numerator of fraction of neccessary direct majority for initiatives to be attainable as winner';

46 COMMENT ON COLUMN "policy"."direct_majority_den" IS 'Denominator of fraction of neccessary direct majority for initaitives to be attainable as winner';

47 COMMENT ON COLUMN "policy"."direct_majority_strict" IS 'If TRUE, then the direct majority must be strictly greater than "direct_majority_num"/"direct_majority_den", otherwise it may also be equal.';

48 COMMENT ON COLUMN "policy"."direct_majority_positive" IS 'Absolute number of "positive_votes" neccessary for an initiative to be attainable as winner';

49 COMMENT ON COLUMN "policy"."direct_majority_non_negative" IS 'Absolute number of sum of "positive_votes" and abstentions neccessary for an initiative to be attainable as winner';

50 COMMENT ON COLUMN "policy"."indirect_majority_num" IS 'Numerator of fraction of neccessary indirect majority (through beat path) for initiatives to be attainable as winner';

51 COMMENT ON COLUMN "policy"."indirect_majority_den" IS 'Denominator of fraction of neccessary indirect majority (through beat path) for initiatives to be attainable as winner';

52 COMMENT ON COLUMN "policy"."indirect_majority_strict" IS 'If TRUE, then the indirect majority must be strictly greater than "indirect_majority_num"/"indirect_majority_den", otherwise it may also be equal.';

53 COMMENT ON COLUMN "policy"."indirect_majority_positive" IS 'Absolute number of votes in favor of the winner neccessary in a beat path to the status quo for an initaitive to be attainable as winner';

54 COMMENT ON COLUMN "policy"."indirect_majority_non_negative" IS 'Absolute number of sum of votes in favor and abstentions in a beat path to the status quo for an initiative to be attainable as winner';

55 COMMENT ON COLUMN "policy"."no_reverse_beat_path" IS 'Causes initiatives with "reverse_beat_path" flag to not be "eligible", thus disallowing them to be winner. See comment on column "initiative"."reverse_beat_path". This option ensures both that a winning initiative is never tied in a (weak) condorcet paradox with the status quo and a winning initiative always beats the status quo directly with a simple majority.';

56 COMMENT ON COLUMN "policy"."no_multistage_majority" IS 'Causes initiatives with "multistage_majority" flag to not be "eligible", thus disallowing them to be winner. See comment on column "initiative"."multistage_majority". This disqualifies initiatives which could cause an instable result. An instable result in this meaning is a result such that repeating the ballot with same preferences but with the winner of the first ballot as status quo would lead to a different winner in the second ballot. If there are no direct majorities required for the winner, or if in direct comparison only simple majorities are required and "no_reverse_beat_path" is true, then results are always stable and this flag does not have any effect on the winner (but still affects the "eligible" flag of an "initiative").';

58 ALTER TABLE "area" DROP COLUMN "autoreject_weight";

60 DROP VIEW "open_issue";

61 DROP VIEW "issue_with_ranks_missing";

63 ALTER TABLE "issue" DROP COLUMN "vote_now";

64 ALTER TABLE "issue" DROP COLUMN "vote_later";

65 ALTER TABLE "issue" ADD COLUMN "status_quo_schulze_rank" INT4;

67 CREATE VIEW "open_issue" AS

68 SELECT * FROM "issue" WHERE "closed" ISNULL;

70 COMMENT ON VIEW "open_issue" IS 'All open issues';

72 CREATE VIEW "issue_with_ranks_missing" AS

73 SELECT * FROM "issue"

74 WHERE "fully_frozen" NOTNULL

75 AND "closed" NOTNULL

76 AND "ranks_available" = FALSE;

78 COMMENT ON VIEW "issue_with_ranks_missing" IS 'Issues where voting was finished, but no ranks have been calculated yet';

80 COMMENT ON COLUMN "issue"."half_frozen" IS 'Point in time, when "discussion_time" has elapsed; Frontends must ensure that for half_frozen issues a) initiatives are not revoked, b) no new drafts are created, c) no initiators are added or removed.';

81 COMMENT ON COLUMN "issue"."snapshot" IS 'Point in time, when snapshot tables have been updated and "population" and *_count values were precalculated';

82 COMMENT ON COLUMN "issue"."status_quo_schulze_rank" IS 'Schulze rank of status quo, as calculated by "calculate_ranks" function';

84 DROP VIEW "battle_view";

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

87 ALTER TABLE "initiative" DROP CONSTRAINT "all_or_none_of_positive_votes_negative_votes_and_agreed_must_be_null";

88 ALTER TABLE "initiative" DROP CONSTRAINT "non_agreed_initiatives_cant_get_a_rank";

90 ALTER TABLE "initiative" DROP COLUMN "agreed";

91 ALTER TABLE "initiative" ADD COLUMN "direct_majority" BOOLEAN;

92 ALTER TABLE "initiative" ADD COLUMN "indirect_majority" BOOLEAN;

93 ALTER TABLE "initiative" ADD COLUMN "schulze_rank" INT4;

94 ALTER TABLE "initiative" ADD COLUMN "better_than_status_quo" BOOLEAN;

95 ALTER TABLE "initiative" ADD COLUMN "worse_than_status_quo" BOOLEAN;

96 ALTER TABLE "initiative" ADD COLUMN "reverse_beat_path" BOOLEAN;

97 ALTER TABLE "initiative" ADD COLUMN "multistage_majority" BOOLEAN;

98 ALTER TABLE "initiative" ADD COLUMN "eligible" BOOLEAN;

99 ALTER TABLE "initiative" ADD COLUMN "winner" BOOLEAN;

100

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

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

103 ( "positive_votes" ISNULL AND "negative_votes" ISNULL AND

104 "direct_majority" ISNULL AND "indirect_majority" ISNULL AND

105 "schulze_rank" ISNULL AND

106 "better_than_status_quo" ISNULL AND "worse_than_status_quo" ISNULL AND

107 "reverse_beat_path" ISNULL AND "multistage_majority" ISNULL AND

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

109 ALTER TABLE "initiative" ADD CONSTRAINT "better_excludes_worse" CHECK (NOT ("better_than_status_quo" AND "worse_than_status_quo"));

110 ALTER TABLE "initiative" ADD CONSTRAINT "minimum_requirement_to_be_eligible" CHECK (

111 "eligible" = FALSE OR

112 ("direct_majority" AND "indirect_majority" AND "better_than_status_quo") );

113 ALTER TABLE "initiative" ADD CONSTRAINT "winner_must_be_eligible" CHECK ("winner"=FALSE OR "eligible"=TRUE);

114 ALTER TABLE "initiative" ADD CONSTRAINT "winner_must_have_first_rank" CHECK ("winner"=FALSE OR "rank"=1);

115 ALTER TABLE "initiative" ADD CONSTRAINT "eligible_at_first_rank_is_winner" CHECK ("eligible"=FALSE OR "rank"!=1 OR "winner"=TRUE);

116 ALTER TABLE "initiative" ADD CONSTRAINT "unique_rank_per_issue" UNIQUE ("issue_id", "rank");

117

118 COMMENT ON COLUMN "initiative"."direct_majority" IS 'TRUE, if "positive_votes"/("positive_votes"+"negative_votes") is strictly greater or greater-equal than "direct_majority_num"/"direct_majority_den", and "positive_votes" is greater-equal than "direct_majority_positive", and ("positive_votes"+abstentions) is greater-equal than "direct_majority_non_negative"';

119 COMMENT ON COLUMN "initiative"."indirect_majority" IS 'Same as "direct_majority", but also considering indirect beat paths';

120 COMMENT ON COLUMN "initiative"."schulze_rank" IS 'Schulze-Ranking without tie-breaking';

121 COMMENT ON COLUMN "initiative"."better_than_status_quo" IS 'TRUE, if initiative has a schulze-ranking better than the status quo (without tie-breaking)';

122 COMMENT ON COLUMN "initiative"."worse_than_status_quo" IS 'TRUE, if initiative has a schulze-ranking worse than the status quo (without tie-breaking)';

123 COMMENT ON COLUMN "initiative"."reverse_beat_path" IS 'TRUE, if there is a beat path (may include ties), from this initiative to the status quo';

124 COMMENT ON COLUMN "initiative"."multistage_majority" IS 'TRUE, if either (a) this initiative has no better rank than the status quo, or (b) there exists a better ranked initiative X, which directly beats this initiative, and either more voters prefer X to this initiative than voters preferring X to the status quo or less voters prefer this initiative to X than voters preferring the status quo to X';

125 COMMENT ON COLUMN "initiative"."eligible" IS 'Initiative is "attainable" and depending on selected policy has no "reverse_beat_path" or "multistage_majority"';

126 COMMENT ON COLUMN "initiative"."winner" IS 'Winner is the "eligible" initiative with best "schulze_rank" and in case of ties with lowest "id"';

127 COMMENT ON COLUMN "initiative"."rank" IS 'Unique ranking for all "admitted" initiatives per issue; lower rank is better; a winner always has rank 1, but rank 1 does not imply that an initiative is winner; initiatives with "direct_majority" AND "indirect_majority" always have a better (lower) rank than other initiatives';

128

129 ALTER TABLE "battle" DROP CONSTRAINT "battle_pkey";

130 ALTER TABLE "battle" ALTER COLUMN "issue_id" SET NOT NULL;

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

132 "winning_initiative_id" != "losing_initiative_id" OR

133 ( ("winning_initiative_id" NOTNULL AND "losing_initiative_id" ISNULL) OR

134 ("winning_initiative_id" ISNULL AND "losing_initiative_id" NOTNULL) ) );

135

136 CREATE UNIQUE INDEX "battle_winning_losing_idx" ON "battle" ("issue_id", "winning_initiative_id", "losing_initiative_id");

137 CREATE UNIQUE INDEX "battle_winning_null_idx" ON "battle" ("issue_id", "winning_initiative_id") WHERE "losing_initiative_id" ISNULL;

138 CREATE UNIQUE INDEX "battle_null_losing_idx" ON "battle" ("issue_id", "losing_initiative_id") WHERE "winning_initiative_id" ISNULL;

139

140 ALTER TABLE "suggestion" ADD COLUMN "draft_id" INT8;

141 -- more later

142

143 ALTER TABLE "suggestion" ADD FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE NO ACTION ON UPDATE CASCADE;

144 ALTER TABLE "suggestion" ADD COLUMN "formatting_engine" TEXT;

145 ALTER TABLE "suggestion" RENAME COLUMN "description" TO "content";

146

147 DROP TRIGGER "update_text_search_data" ON "suggestion";

148

149 CREATE TRIGGER "update_text_search_data"

150 BEFORE INSERT OR UPDATE ON "suggestion"

151 FOR EACH ROW EXECUTE PROCEDURE

152 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',

153 "name", "content");

154

155 COMMENT ON COLUMN "suggestion"."draft_id" IS 'Draft, which the author has seen when composing the suggestion; should always be set by a frontend, but defaults to current draft of the initiative (implemented by trigger "default_for_draft_id")';

156

157 CREATE TABLE "rendered_suggestion" (

158 PRIMARY KEY ("suggestion_id", "format"),

159 "suggestion_id" INT8 REFERENCES "suggestion" ("id") ON DELETE CASCADE ON UPDATE CASCADE,

160 "format" TEXT,

161 "content" TEXT NOT NULL );

162

163 COMMENT ON TABLE "rendered_suggestion" IS 'This table may be used by frontends to cache "rendered" drafts (e.g. HTML output generated from wiki text)';

164

165 DROP VIEW "area_member_count";

166

167 ALTER TABLE "membership" DROP COLUMN "autoreject";

168

169 ALTER TABLE "interest" DROP COLUMN "autoreject";

170 ALTER TABLE "interest" DROP COLUMN "voting_requested";

171

172 ALTER TABLE "supporter" DROP CONSTRAINT "supporter_initiative_id_fkey";

173 ALTER TABLE "supporter" ADD FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE NO ACTION ON UPDATE CASCADE;

174

175 COMMENT ON COLUMN "supporter"."draft_id" IS 'Latest seen draft; should always be set by a frontend, but defaults to current draft of the initiative (implemented by trigger "default_for_draft_id")';

176

177 ALTER TABLE "direct_interest_snapshot" DROP COLUMN "voting_requested";

178 ALTER TABLE "direct_voter" DROP COLUMN "autoreject";

179

180 DROP TRIGGER "default_for_draft_id" ON "supporter";

181 DROP FUNCTION "supporter_default_for_draft_id_trigger"();

182

183 CREATE FUNCTION "default_for_draft_id_trigger"()

184 RETURNS TRIGGER

185 LANGUAGE 'plpgsql' VOLATILE AS $$

186 BEGIN

187 IF NEW."draft_id" ISNULL THEN

188 SELECT "id" INTO NEW."draft_id" FROM "current_draft"

189 WHERE "initiative_id" = NEW."initiative_id";

190 END IF;

191 RETURN NEW;

192 END;

193 $$;

194

195 CREATE TRIGGER "default_for_draft_id" BEFORE INSERT OR UPDATE ON "suggestion"

196 FOR EACH ROW EXECUTE PROCEDURE "default_for_draft_id_trigger"();

197 CREATE TRIGGER "default_for_draft_id" BEFORE INSERT OR UPDATE ON "supporter"

198 FOR EACH ROW EXECUTE PROCEDURE "default_for_draft_id_trigger"();

199

200 COMMENT ON FUNCTION "default_for_draft_id_trigger"() IS 'Implementation of trigger "default_for_draft" on tables "supporter" and "suggestion"';

201 COMMENT ON TRIGGER "default_for_draft_id" ON "suggestion" IS 'If "draft_id" is NULL, then use the current draft of the initiative as default';

202 COMMENT ON TRIGGER "default_for_draft_id" ON "supporter" IS 'If "draft_id" is NULL, then use the current draft of the initiative as default';

203

204 CREATE VIEW "area_member_count" AS

205 SELECT

206 "area"."id" AS "area_id",

207 count("member"."id") AS "direct_member_count",

208 coalesce(

209 sum(

210 CASE WHEN "member"."id" NOTNULL THEN

211 "membership_weight"("area"."id", "member"."id")

212 ELSE 0 END

213 )

214 ) AS "member_weight"

215 FROM "area"

216 LEFT JOIN "membership"

217 ON "area"."id" = "membership"."area_id"

218 LEFT JOIN "privilege"

219 ON "privilege"."unit_id" = "area"."unit_id"

220 AND "privilege"."member_id" = "membership"."member_id"

221 AND "privilege"."voting_right"

222 LEFT JOIN "member"

223 ON "member"."id" = "privilege"."member_id" -- NOTE: no membership here!

224 AND "member"."active"

225 GROUP BY "area"."id";

226

227 COMMENT ON VIEW "area_member_count" IS 'View used to update "direct_member_count" and "member_weight" columns of table "area"';

228

229 CREATE VIEW "battle_participant" AS

230 SELECT "initiative"."id", "initiative"."issue_id"

231 FROM "issue" JOIN "initiative"

232 ON "issue"."id" = "initiative"."issue_id"

233 WHERE "initiative"."admitted"

234 UNION ALL

235 SELECT NULL, "id" AS "issue_id"

236 FROM "issue";

237

238 COMMENT ON VIEW "battle_participant" IS 'Helper view for "battle_view" containing admitted initiatives plus virtual "status-quo" initiative denoted by NULL reference';

239

240 CREATE VIEW "battle_view" AS

241 SELECT

242 "issue"."id" AS "issue_id",

243 "winning_initiative"."id" AS "winning_initiative_id",

244 "losing_initiative"."id" AS "losing_initiative_id",

245 sum(

246 CASE WHEN

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

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

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

250 ) AS "count"

251 FROM "issue"

252 LEFT JOIN "direct_voter"

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

254 JOIN "battle_participant" AS "winning_initiative"

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

256 JOIN "battle_participant" AS "losing_initiative"

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

258 LEFT JOIN "vote" AS "better_vote"

259 ON "direct_voter"."member_id" = "better_vote"."member_id"

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

261 LEFT JOIN "vote" AS "worse_vote"

262 ON "direct_voter"."member_id" = "worse_vote"."member_id"

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

264 WHERE "issue"."closed" NOTNULL

265 AND "issue"."cleaned" ISNULL

266 AND (

267 "winning_initiative"."id" != "losing_initiative"."id" OR

268 ( ("winning_initiative"."id" NOTNULL AND "losing_initiative"."id" ISNULL) OR

269 ("winning_initiative"."id" ISNULL AND "losing_initiative"."id" NOTNULL) ) )

270 GROUP BY

271 "issue"."id",

272 "winning_initiative"."id",

273 "losing_initiative"."id";

274

275 COMMENT ON VIEW "battle_view" IS 'Number of members preferring one initiative (or status-quo) to another initiative (or status-quo); Used to fill "battle" table';

276

277 CREATE OR REPLACE FUNCTION "create_interest_snapshot"

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

279 RETURNS VOID

280 LANGUAGE 'plpgsql' VOLATILE AS $$

281 DECLARE

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

283 BEGIN

284 DELETE FROM "direct_interest_snapshot"

285 WHERE "issue_id" = "issue_id_p"

286 AND "event" = 'periodic';

287 DELETE FROM "delegating_interest_snapshot"

288 WHERE "issue_id" = "issue_id_p"

289 AND "event" = 'periodic';

290 DELETE FROM "direct_supporter_snapshot"

291 WHERE "issue_id" = "issue_id_p"

292 AND "event" = 'periodic';

293 INSERT INTO "direct_interest_snapshot"

294 ("issue_id", "event", "member_id")

295 SELECT

296 "issue_id_p" AS "issue_id",

297 'periodic' AS "event",

298 "member"."id" AS "member_id"

299 FROM "issue"

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

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

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

303 JOIN "privilege"

304 ON "privilege"."unit_id" = "area"."unit_id"

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

306 WHERE "issue"."id" = "issue_id_p"

307 AND "member"."active" AND "privilege"."voting_right";

308 FOR "member_id_v" IN

309 SELECT "member_id" FROM "direct_interest_snapshot"

310 WHERE "issue_id" = "issue_id_p"

311 AND "event" = 'periodic'

312 LOOP

313 UPDATE "direct_interest_snapshot" SET

314 "weight" = 1 +

315 "weight_of_added_delegations_for_interest_snapshot"(

316 "issue_id_p",

317 "member_id_v",

318 '{}'

319 )

320 WHERE "issue_id" = "issue_id_p"

321 AND "event" = 'periodic'

322 AND "member_id" = "member_id_v";

323 END LOOP;

324 INSERT INTO "direct_supporter_snapshot"

325 ( "issue_id", "initiative_id", "event", "member_id",

326 "informed", "satisfied" )

327 SELECT

328 "issue_id_p" AS "issue_id",

329 "initiative"."id" AS "initiative_id",

330 'periodic' AS "event",

331 "supporter"."member_id" AS "member_id",

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

333 NOT EXISTS (

334 SELECT NULL FROM "critical_opinion"

335 WHERE "initiative_id" = "initiative"."id"

336 AND "member_id" = "supporter"."member_id"

337 ) AS "satisfied"

338 FROM "initiative"

339 JOIN "supporter"

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

341 JOIN "current_draft"

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

343 JOIN "direct_interest_snapshot"

344 ON "supporter"."member_id" = "direct_interest_snapshot"."member_id"

345 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"

346 AND "event" = 'periodic'

347 WHERE "initiative"."issue_id" = "issue_id_p";

348 RETURN;

349 END;

350 $$;

351

352 CREATE OR REPLACE FUNCTION "create_snapshot"

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

354 RETURNS VOID

355 LANGUAGE 'plpgsql' VOLATILE AS $$

356 DECLARE

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

358 "suggestion_id_v" "suggestion"."id"%TYPE;

359 BEGIN

360 PERFORM "lock_issue"("issue_id_p");

361 PERFORM "create_population_snapshot"("issue_id_p");

362 PERFORM "create_interest_snapshot"("issue_id_p");

363 UPDATE "issue" SET

364 "snapshot" = now(),

365 "latest_snapshot_event" = 'periodic',

366 "population" = (

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

368 FROM "direct_population_snapshot"

369 WHERE "issue_id" = "issue_id_p"

370 AND "event" = 'periodic'

371 )

372 WHERE "id" = "issue_id_p";

373 FOR "initiative_id_v" IN

374 SELECT "id" FROM "initiative" WHERE "issue_id" = "issue_id_p"

375 LOOP

376 UPDATE "initiative" SET

377 "supporter_count" = (

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

379 FROM "direct_interest_snapshot" AS "di"

380 JOIN "direct_supporter_snapshot" AS "ds"

381 ON "di"."member_id" = "ds"."member_id"

382 WHERE "di"."issue_id" = "issue_id_p"

383 AND "di"."event" = 'periodic'

384 AND "ds"."initiative_id" = "initiative_id_v"

385 AND "ds"."event" = 'periodic'

386 ),

387 "informed_supporter_count" = (

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

389 FROM "direct_interest_snapshot" AS "di"

390 JOIN "direct_supporter_snapshot" AS "ds"

391 ON "di"."member_id" = "ds"."member_id"

392 WHERE "di"."issue_id" = "issue_id_p"

393 AND "di"."event" = 'periodic'

394 AND "ds"."initiative_id" = "initiative_id_v"

395 AND "ds"."event" = 'periodic'

396 AND "ds"."informed"

397 ),

398 "satisfied_supporter_count" = (

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

400 FROM "direct_interest_snapshot" AS "di"

401 JOIN "direct_supporter_snapshot" AS "ds"

402 ON "di"."member_id" = "ds"."member_id"

403 WHERE "di"."issue_id" = "issue_id_p"

404 AND "di"."event" = 'periodic'

405 AND "ds"."initiative_id" = "initiative_id_v"

406 AND "ds"."event" = 'periodic'

407 AND "ds"."satisfied"

408 ),

409 "satisfied_informed_supporter_count" = (

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

411 FROM "direct_interest_snapshot" AS "di"

412 JOIN "direct_supporter_snapshot" AS "ds"

413 ON "di"."member_id" = "ds"."member_id"

414 WHERE "di"."issue_id" = "issue_id_p"

415 AND "di"."event" = 'periodic'

416 AND "ds"."initiative_id" = "initiative_id_v"

417 AND "ds"."event" = 'periodic'

418 AND "ds"."informed"

419 AND "ds"."satisfied"

420 )

421 WHERE "id" = "initiative_id_v";

422 FOR "suggestion_id_v" IN

423 SELECT "id" FROM "suggestion"

424 WHERE "initiative_id" = "initiative_id_v"

425 LOOP

426 UPDATE "suggestion" SET

427 "minus2_unfulfilled_count" = (

428 SELECT coalesce(sum("snapshot"."weight"), 0)

429 FROM "issue" CROSS JOIN "opinion"

430 JOIN "direct_interest_snapshot" AS "snapshot"

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

432 AND "snapshot"."event" = "issue"."latest_snapshot_event"

433 AND "snapshot"."member_id" = "opinion"."member_id"

434 WHERE "issue"."id" = "issue_id_p"

435 AND "opinion"."suggestion_id" = "suggestion_id_v"

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

437 AND "opinion"."fulfilled" = FALSE

438 ),

439 "minus2_fulfilled_count" = (

440 SELECT coalesce(sum("snapshot"."weight"), 0)

441 FROM "issue" CROSS JOIN "opinion"

442 JOIN "direct_interest_snapshot" AS "snapshot"

443 ON "snapshot"."issue_id" = "issue"."id"

444 AND "snapshot"."event" = "issue"."latest_snapshot_event"

445 AND "snapshot"."member_id" = "opinion"."member_id"

446 WHERE "issue"."id" = "issue_id_p"

447 AND "opinion"."suggestion_id" = "suggestion_id_v"

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

449 AND "opinion"."fulfilled" = TRUE

450 ),

451 "minus1_unfulfilled_count" = (

452 SELECT coalesce(sum("snapshot"."weight"), 0)

453 FROM "issue" CROSS JOIN "opinion"

454 JOIN "direct_interest_snapshot" AS "snapshot"

455 ON "snapshot"."issue_id" = "issue"."id"

456 AND "snapshot"."event" = "issue"."latest_snapshot_event"

457 AND "snapshot"."member_id" = "opinion"."member_id"

458 WHERE "issue"."id" = "issue_id_p"

459 AND "opinion"."suggestion_id" = "suggestion_id_v"

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

461 AND "opinion"."fulfilled" = FALSE

462 ),

463 "minus1_fulfilled_count" = (

464 SELECT coalesce(sum("snapshot"."weight"), 0)

465 FROM "issue" CROSS JOIN "opinion"

466 JOIN "direct_interest_snapshot" AS "snapshot"

467 ON "snapshot"."issue_id" = "issue"."id"

468 AND "snapshot"."event" = "issue"."latest_snapshot_event"

469 AND "snapshot"."member_id" = "opinion"."member_id"

470 WHERE "issue"."id" = "issue_id_p"

471 AND "opinion"."suggestion_id" = "suggestion_id_v"

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

473 AND "opinion"."fulfilled" = TRUE

474 ),

475 "plus1_unfulfilled_count" = (

476 SELECT coalesce(sum("snapshot"."weight"), 0)

477 FROM "issue" CROSS JOIN "opinion"

478 JOIN "direct_interest_snapshot" AS "snapshot"

479 ON "snapshot"."issue_id" = "issue"."id"

480 AND "snapshot"."event" = "issue"."latest_snapshot_event"

481 AND "snapshot"."member_id" = "opinion"."member_id"

482 WHERE "issue"."id" = "issue_id_p"

483 AND "opinion"."suggestion_id" = "suggestion_id_v"

484 AND "opinion"."degree" = 1

485 AND "opinion"."fulfilled" = FALSE

486 ),

487 "plus1_fulfilled_count" = (

488 SELECT coalesce(sum("snapshot"."weight"), 0)

489 FROM "issue" CROSS JOIN "opinion"

490 JOIN "direct_interest_snapshot" AS "snapshot"

491 ON "snapshot"."issue_id" = "issue"."id"

492 AND "snapshot"."event" = "issue"."latest_snapshot_event"

493 AND "snapshot"."member_id" = "opinion"."member_id"

494 WHERE "issue"."id" = "issue_id_p"

495 AND "opinion"."suggestion_id" = "suggestion_id_v"

496 AND "opinion"."degree" = 1

497 AND "opinion"."fulfilled" = TRUE

498 ),

499 "plus2_unfulfilled_count" = (

500 SELECT coalesce(sum("snapshot"."weight"), 0)

501 FROM "issue" CROSS JOIN "opinion"

502 JOIN "direct_interest_snapshot" AS "snapshot"

503 ON "snapshot"."issue_id" = "issue"."id"

504 AND "snapshot"."event" = "issue"."latest_snapshot_event"

505 AND "snapshot"."member_id" = "opinion"."member_id"

506 WHERE "issue"."id" = "issue_id_p"

507 AND "opinion"."suggestion_id" = "suggestion_id_v"

508 AND "opinion"."degree" = 2

509 AND "opinion"."fulfilled" = FALSE

510 ),

511 "plus2_fulfilled_count" = (

512 SELECT coalesce(sum("snapshot"."weight"), 0)

513 FROM "issue" CROSS JOIN "opinion"

514 JOIN "direct_interest_snapshot" AS "snapshot"

515 ON "snapshot"."issue_id" = "issue"."id"

516 AND "snapshot"."event" = "issue"."latest_snapshot_event"

517 AND "snapshot"."member_id" = "opinion"."member_id"

518 WHERE "issue"."id" = "issue_id_p"

519 AND "opinion"."suggestion_id" = "suggestion_id_v"

520 AND "opinion"."degree" = 2

521 AND "opinion"."fulfilled" = TRUE

522 )

523 WHERE "suggestion"."id" = "suggestion_id_v";

524 END LOOP;

525 END LOOP;

526 RETURN;

527 END;

528 $$;

529

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

531 RETURNS VOID

532 LANGUAGE 'plpgsql' VOLATILE AS $$

533 DECLARE

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

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

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

537 BEGIN

538 PERFORM "lock_issue"("issue_id_p");

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

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

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

542 DELETE FROM "delegating_voter"

543 WHERE "issue_id" = "issue_id_p";

544 -- delete votes from non-privileged voters:

545 DELETE FROM "direct_voter"

546 USING (

547 SELECT

548 "direct_voter"."member_id"

549 FROM "direct_voter"

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

551 LEFT JOIN "privilege"

552 ON "privilege"."unit_id" = "unit_id_v"

553 AND "privilege"."member_id" = "direct_voter"."member_id"

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

555 "member"."active" = FALSE OR

556 "privilege"."voting_right" ISNULL OR

557 "privilege"."voting_right" = FALSE

558 )

559 ) AS "subquery"

560 WHERE "direct_voter"."issue_id" = "issue_id_p"

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

562 -- consider delegations:

563 UPDATE "direct_voter" SET "weight" = 1

564 WHERE "issue_id" = "issue_id_p";

565 PERFORM "add_vote_delegations"("issue_id_p");

566 -- set voter count and mark issue as being calculated:

567 UPDATE "issue" SET

568 "state" = 'calculation',

569 "closed" = now(),

570 "voter_count" = (

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

572 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"

573 )

574 WHERE "id" = "issue_id_p";

575 -- materialize battle_view:

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

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

578 INSERT INTO "battle" (

579 "issue_id",

580 "winning_initiative_id", "losing_initiative_id",

581 "count"

582 ) SELECT

583 "issue_id",

584 "winning_initiative_id", "losing_initiative_id",

585 "count"

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

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

588 UPDATE "initiative" SET

589 "positive_votes" = "battle_win"."count",

590 "negative_votes" = "battle_lose"."count"

591 FROM "battle" AS "battle_win", "battle" AS "battle_lose"

592 WHERE

593 "battle_win"."issue_id" = "issue_id_p" AND

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

595 "battle_win"."losing_initiative_id" ISNULL AND

596 "battle_lose"."issue_id" = "issue_id_p" AND

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

598 "battle_lose"."winning_initiative_id" ISNULL;

599 END;

600 $$;

601

602 DROP FUNCTION "array_init_string"(INTEGER);

603 DROP FUNCTION "square_matrix_init_string"(INTEGER);

604

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

606 RETURNS VOID

607 LANGUAGE 'plpgsql' VOLATILE AS $$

608 DECLARE

609 "issue_row" "issue"%ROWTYPE;

610 "policy_row" "policy"%ROWTYPE;

611 "dimension_v" INTEGER;

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

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

614 "i" INTEGER;

615 "j" INTEGER;

616 "k" INTEGER;

617 "battle_row" "battle"%ROWTYPE;

618 "rank_ary" INT4[];

619 "rank_v" INT4;

620 "done_v" INTEGER;

621 "winners_ary" INTEGER[];

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

623 BEGIN

624 SELECT * INTO "issue_row"

625 FROM "issue" WHERE "id" = "issue_id_p"

626 FOR UPDATE;

627 SELECT * INTO "policy_row"

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

629 SELECT count(1) INTO "dimension_v"

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

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

632 -- comparison:

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

634 "i" := 1;

635 "j" := 2;

636 FOR "battle_row" IN

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

638 ORDER BY

639 "winning_initiative_id" NULLS LAST,

640 "losing_initiative_id" NULLS LAST

641 LOOP

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

643 IF "j" = "dimension_v" THEN

644 "i" := "i" + 1;

645 "j" := 1;

646 ELSE

647 "j" := "j" + 1;

648 IF "j" = "i" THEN

649 "j" := "j" + 1;

650 END IF;

651 END IF;

652 END LOOP;

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

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

655 END IF;

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

657 -- function:

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

659 "i" := 1;

660 LOOP

661 "j" := 1;

662 LOOP

663 IF "i" != "j" THEN

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

665 "vote_matrix"["i"]["j"],

666 "vote_matrix"["j"]["i"]

667 );

668 END IF;

669 EXIT WHEN "j" = "dimension_v";

670 "j" := "j" + 1;

671 END LOOP;

672 EXIT WHEN "i" = "dimension_v";

673 "i" := "i" + 1;

674 END LOOP;

675 -- Find best paths:

676 "i" := 1;

677 LOOP

678 "j" := 1;

679 LOOP

680 IF "i" != "j" THEN

681 "k" := 1;

682 LOOP

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

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

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

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

687 END IF;

688 ELSE

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

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

691 END IF;

692 END IF;

693 END IF;

694 EXIT WHEN "k" = "dimension_v";

695 "k" := "k" + 1;

696 END LOOP;

697 END IF;

698 EXIT WHEN "j" = "dimension_v";

699 "j" := "j" + 1;

700 END LOOP;

701 EXIT WHEN "i" = "dimension_v";

702 "i" := "i" + 1;

703 END LOOP;

704 -- Determine order of winners:

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

706 "rank_v" := 1;

707 "done_v" := 0;

708 LOOP

709 "winners_ary" := '{}';

710 "i" := 1;

711 LOOP

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

713 "j" := 1;

714 LOOP

715 IF

716 "i" != "j" AND

717 "rank_ary"["j"] ISNULL AND

718 "matrix"["j"]["i"] > "matrix"["i"]["j"]

719 THEN

720 -- someone else is better

721 EXIT;

722 END IF;

723 IF "j" = "dimension_v" THEN

724 -- noone is better

725 "winners_ary" := "winners_ary" || "i";

726 EXIT;

727 END IF;

728 "j" := "j" + 1;

729 END LOOP;

730 END IF;

731 EXIT WHEN "i" = "dimension_v";

732 "i" := "i" + 1;

733 END LOOP;

734 "i" := 1;

735 LOOP

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

737 "done_v" := "done_v" + 1;

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

739 "i" := "i" + 1;

740 END LOOP;

741 EXIT WHEN "done_v" = "dimension_v";

742 "rank_v" := "rank_v" + 1;

743 END LOOP;

744 -- write preliminary results:

745 "i" := 1;

746 FOR "initiative_id_v" IN

747 SELECT "id" FROM "initiative"

748 WHERE "issue_id" = "issue_id_p" AND "admitted"

749 ORDER BY "id"

750 LOOP

751 UPDATE "initiative" SET

752 "direct_majority" =

753 CASE WHEN "policy_row"."direct_majority_strict" THEN

754 "positive_votes" * "policy_row"."direct_majority_den" >

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

756 ELSE

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

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

759 END

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

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

762 "policy_row"."direct_majority_non_negative",

763 "indirect_majority" =

764 CASE WHEN "policy_row"."indirect_majority_strict" THEN

765 "positive_votes" * "policy_row"."indirect_majority_den" >

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

767 ELSE

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

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

770 END

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

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

773 "policy_row"."indirect_majority_non_negative",

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

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

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

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

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

779 "winner" = FALSE

780 WHERE "id" = "initiative_id_v";

781 "i" := "i" + 1;

782 END LOOP;

783 IF "i" != "dimension_v" THEN

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

785 END IF;

786 -- take indirect majorities into account:

787 LOOP

788 UPDATE "initiative" SET "indirect_majority" = TRUE

789 FROM (

790 SELECT "new_initiative"."id" AS "initiative_id"

791 FROM "initiative" "old_initiative"

792 JOIN "initiative" "new_initiative"

793 ON "new_initiative"."issue_id" = "issue_id_p"

794 AND "new_initiative"."indirect_majority" = FALSE

795 JOIN "battle" "battle_win"

796 ON "battle_win"."issue_id" = "issue_id_p"

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

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

799 JOIN "battle" "battle_lose"

800 ON "battle_lose"."issue_id" = "issue_id_p"

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

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

803 WHERE "old_initiative"."issue_id" = "issue_id_p"

804 AND "old_initiative"."indirect_majority" = TRUE

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

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

807 "policy_row"."indirect_majority_num" *

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

809 ELSE

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

811 "policy_row"."indirect_majority_num" *

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

813 END

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

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

816 "policy_row"."indirect_majority_non_negative"

817 ) AS "subquery"

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

819 EXIT WHEN NOT FOUND;

820 END LOOP;

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

822 UPDATE "initiative" SET "multistage_majority" = TRUE

823 FROM (

824 SELECT "losing_initiative"."id" AS "initiative_id"

825 FROM "initiative" "losing_initiative"

826 JOIN "initiative" "winning_initiative"

827 ON "winning_initiative"."issue_id" = "issue_id_p"

828 AND "winning_initiative"."admitted"

829 JOIN "battle" "battle_win"

830 ON "battle_win"."issue_id" = "issue_id_p"

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

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

833 JOIN "battle" "battle_lose"

834 ON "battle_lose"."issue_id" = "issue_id_p"

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

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

837 WHERE "losing_initiative"."issue_id" = "issue_id_p"

838 AND "losing_initiative"."admitted"

839 AND "winning_initiative"."schulze_rank" <

840 "losing_initiative"."schulze_rank"

841 AND "battle_win"."count" > "battle_lose"."count"

842 AND (

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

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

845 ) AS "subquery"

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

847 -- mark eligible initiatives:

848 UPDATE "initiative" SET "eligible" = TRUE

849 WHERE "issue_id" = "issue_id_p"

850 AND "initiative"."direct_majority"

851 AND "initiative"."indirect_majority"

852 AND "initiative"."better_than_status_quo"

853 AND (

854 "policy_row"."no_multistage_majority" = FALSE OR

855 "initiative"."multistage_majority" = FALSE )

856 AND (

857 "policy_row"."no_reverse_beat_path" = FALSE OR

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

859 -- mark final winner:

860 UPDATE "initiative" SET "winner" = TRUE

861 FROM (

862 SELECT "id" AS "initiative_id"

863 FROM "initiative"

864 WHERE "issue_id" = "issue_id_p" AND "eligible"

865 ORDER BY "schulze_rank", "id"

866 LIMIT 1

867 ) AS "subquery"

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

869 -- write (final) ranks:

870 "rank_v" := 1;

871 FOR "initiative_id_v" IN

872 SELECT "id"

873 FROM "initiative"

874 WHERE "issue_id" = "issue_id_p" AND "admitted"

875 ORDER BY

876 "winner" DESC,

877 ("direct_majority" AND "indirect_majority") DESC,

878 "schulze_rank",

879 "id"

880 LOOP

881 UPDATE "initiative" SET "rank" = "rank_v"

882 WHERE "id" = "initiative_id_v";

883 "rank_v" := "rank_v" + 1;

884 END LOOP;

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

886 UPDATE "issue" SET

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

888 "state" =

889 CASE WHEN EXISTS (

890 SELECT NULL FROM "initiative"

891 WHERE "issue_id" = "issue_id_p" AND "winner"

892 ) THEN

893 'finished_with_winner'::"issue_state"

894 ELSE

895 'finished_without_winner'::"issue_state"

896 END,

897 "ranks_available" = TRUE

898 WHERE "id" = "issue_id_p";

899 RETURN;

900 END;

901 $$;

902

903 CREATE OR REPLACE FUNCTION "check_issue"

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

905 RETURNS VOID

906 LANGUAGE 'plpgsql' VOLATILE AS $$

907 DECLARE

908 "issue_row" "issue"%ROWTYPE;

909 "policy_row" "policy"%ROWTYPE;

910 BEGIN

911 PERFORM "lock_issue"("issue_id_p");

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

913 -- only process open issues:

914 IF "issue_row"."closed" ISNULL THEN

915 SELECT * INTO "policy_row" FROM "policy"

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

917 -- create a snapshot, unless issue is already fully frozen:

918 IF "issue_row"."fully_frozen" ISNULL THEN

919 PERFORM "create_snapshot"("issue_id_p");

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

921 END IF;

922 -- eventually close or accept issues, which have not been accepted:

923 IF "issue_row"."accepted" ISNULL THEN

924 IF EXISTS (

925 SELECT NULL FROM "initiative"

926 WHERE "issue_id" = "issue_id_p"

927 AND "supporter_count" > 0

928 AND "supporter_count" * "policy_row"."issue_quorum_den"

929 >= "issue_row"."population" * "policy_row"."issue_quorum_num"

930 ) THEN

931 -- accept issues, if supporter count is high enough

932 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');

933 -- NOTE: "issue_row" used later

934 "issue_row"."state" := 'discussion';

935 "issue_row"."accepted" := now();

936 UPDATE "issue" SET

937 "state" = "issue_row"."state",

938 "accepted" = "issue_row"."accepted"

939 WHERE "id" = "issue_row"."id";

940 ELSIF

941 now() >= "issue_row"."created" + "issue_row"."admission_time"

942 THEN

943 -- close issues, if admission time has expired

944 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');

945 UPDATE "issue" SET

946 "state" = 'canceled_issue_not_accepted',

947 "closed" = now()

948 WHERE "id" = "issue_row"."id";

949 END IF;

950 END IF;

951 -- eventually half freeze issues:

952 IF

953 -- NOTE: issue can't be closed at this point, if it has been accepted

954 "issue_row"."accepted" NOTNULL AND

955 "issue_row"."half_frozen" ISNULL

956 THEN

957 IF

958 now() >= "issue_row"."accepted" + "issue_row"."discussion_time"

959 THEN

960 PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze');

961 -- NOTE: "issue_row" used later

962 "issue_row"."state" := 'verification';

963 "issue_row"."half_frozen" := now();

964 UPDATE "issue" SET

965 "state" = "issue_row"."state",

966 "half_frozen" = "issue_row"."half_frozen"

967 WHERE "id" = "issue_row"."id";

968 END IF;

969 END IF;

970 -- close issues after some time, if all initiatives have been revoked:

971 IF

972 "issue_row"."closed" ISNULL AND

973 NOT EXISTS (

974 -- all initiatives are revoked

975 SELECT NULL FROM "initiative"

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

977 ) AND (

978 -- and issue has not been accepted yet

979 "issue_row"."accepted" ISNULL OR

980 NOT EXISTS (

981 -- or no initiatives have been revoked lately

982 SELECT NULL FROM "initiative"

983 WHERE "issue_id" = "issue_id_p"

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

985 ) OR (

986 -- or verification time has elapsed

987 "issue_row"."half_frozen" NOTNULL AND

988 "issue_row"."fully_frozen" ISNULL AND

989 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"

990 )

991 )

992 THEN

993 -- NOTE: "issue_row" used later

994 IF "issue_row"."accepted" ISNULL THEN

995 "issue_row"."state" := 'canceled_revoked_before_accepted';

996 ELSIF "issue_row"."half_frozen" ISNULL THEN

997 "issue_row"."state" := 'canceled_after_revocation_during_discussion';

998 ELSE

999 "issue_row"."state" := 'canceled_after_revocation_during_verification';

1000 END IF;

1001 "issue_row"."closed" := now();

1002 UPDATE "issue" SET

1003 "state" = "issue_row"."state",

1004 "closed" = "issue_row"."closed"

1005 WHERE "id" = "issue_row"."id";

1006 END IF;

1007 -- fully freeze issue after verification time:

1008 IF

1009 "issue_row"."half_frozen" NOTNULL AND

1010 "issue_row"."fully_frozen" ISNULL AND

1011 "issue_row"."closed" ISNULL AND

1012 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"

1013 THEN

1014 PERFORM "freeze_after_snapshot"("issue_id_p");

1015 -- NOTE: "issue" might change, thus "issue_row" has to be updated below

1016 END IF;

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

1018 -- close issue by calling close_voting(...) after voting time:

1019 IF

1020 "issue_row"."closed" ISNULL AND

1021 "issue_row"."fully_frozen" NOTNULL AND

1022 now() >= "issue_row"."fully_frozen" + "issue_row"."voting_time"

1023 THEN

1024 PERFORM "close_voting"("issue_id_p");

1025 -- calculate ranks will not consume much time and can be done now

1026 PERFORM "calculate_ranks"("issue_id_p");

1027 END IF;

1028 END IF;

1029 RETURN;

1030 END;

1031 $$;

1032

1033 COMMIT;

1034

1035 BEGIN;

1036

1037 UPDATE "initiative" SET

1038 "direct_majority" = "rank" NOTNULL,

1039 "indirect_majority" = "rank" NOTNULL,

1040 "schulze_rank" = "rank",

1041 "better_than_status_quo" = "rank" NOTNULL,

1042 "worse_than_status_quo" = "rank" ISNULL,

1043 "reverse_beat_path" = "rank" ISNULL,

1044 "multistage_majority" = "rank" ISNULL,

1045 "eligible" = "rank" NOTNULL,

1046 "winner" = ("rank" = 1)

1047 FROM "issue"

1048 WHERE "issue"."id" = "initiative"."issue_id"

1049 AND "issue"."state" IN ('finished_without_winner', 'finished_with_winner')

1050 AND "initiative"."admitted";

1051

1052 UPDATE "issue" SET "status_quo_schulze_rank" = "subquery"."rank"

1053 FROM (

1054 SELECT

1055 "issue"."id" AS "issue_id",

1056 COALESCE(max("initiative"."rank") + 1) AS "rank"

1057 FROM "issue" JOIN "initiative"

1058 ON "issue"."id" = "initiative"."issue_id"

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

1060 AND "initiative"."admitted"

1061 GROUP BY "issue"."id"

1062 ) AS "subquery"

1063 WHERE "issue"."id" = "subquery"."issue_id";

1064

1065 CREATE FUNCTION "update__set_remaining_ranks"("issue_id_p" "issue"."id"%TYPE)

1066 RETURNS VOID

1067 LANGUAGE 'plpgsql' AS $$

1068 DECLARE

1069 "rank_v" INT4;

1070 "initiative_id_v" INT4;

1071 BEGIN

1072 SELECT "status_quo_schulze_rank" INTO "rank_v"

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

1074 FOR "initiative_id_v" IN

1075 SELECT "id" FROM "initiative"

1076 WHERE "issue_id" = "issue_id_p" AND "admitted" AND "rank" ISNULL

1077 ORDER BY "vote_ratio"("positive_votes", "negative_votes") DESC

1078 LOOP

1079 UPDATE "initiative" SET

1080 "schulze_rank" = "rank_v" + 1,

1081 "rank" = "rank_v"

1082 WHERE "id" = "initiative_id_v";

1083 "rank_v" := "rank_v" + 1;

1084 END LOOP;

1085 RETURN;

1086 END;

1087 $$;

1088

1089 SELECT "update__set_remaining_ranks"("id") FROM "issue"

1090 WHERE "state" IN ('finished_without_winner', 'finished_with_winner');

1091

1092 DROP FUNCTION "update__set_remaining_ranks"("issue"."id"%TYPE);

1093

1094 UPDATE "suggestion" SET "draft_id" = "subquery"."draft_id"

1095 FROM (

1096 SELECT DISTINCT ON ("suggestion"."id")

1097 "suggestion"."id" AS "suggestion_id",

1098 "draft"."id" AS "draft_id"

1099 FROM "suggestion" JOIN "draft"

1100 ON "suggestion"."initiative_id" = "draft"."initiative_id"

1101 WHERE "draft"."created" <= "suggestion"."created"

1102 ORDER BY "suggestion"."id", "draft"."created" DESC

1103 ) AS "subquery"

1104 WHERE "suggestion"."id" = "subquery"."suggestion_id";

1105

1106 COMMIT;

1107

1108 ALTER TABLE "suggestion" ALTER COLUMN "draft_id" SET NOT NULL;