liquid_feedback_core: 9975ce3f20ee update/core-update.v1.4.0-v1.5.0.sql

liquid_feedback_core

view update/core-update.v1.4.0-v1.5.0.sql @ 177:9975ce3f20ee

First version of update script from v1.4.0 to v1.5.0

author	jbe
date	Wed Jun 08 17:31:00 2011 +0200 (2011-06-08)
parents
children	184b023a5b1d

line source

1 BEGIN;

3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS

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

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

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

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

10 CREATE TABLE "rendered_member_statement" (

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

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

13 "format" TEXT,

14 "content" TEXT NOT NULL );

16 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)';

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

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

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

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

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

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

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

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

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

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

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

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

31 UPDATE "policy" SET

32 "direct_majority_num" = "majority_num",

33 "direct_majority_den" = "majority_den",

34 "direct_majority_strict" = "majority_strict",

35 "indirect_majority_num" = "majority_num",

36 "indirect_majority_den" = "majority_den",

37 "indirect_majority_strict" = "majority_strict";

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

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

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

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

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

45 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.';

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

47 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';

48 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';

49 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';

50 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.';

51 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';

52 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';

53 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.';

54 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").';

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

58 DROP VIEW "open_issue";

59 DROP VIEW "issue_with_ranks_missing";

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

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

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

65 CREATE VIEW "open_issue" AS

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

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

70 CREATE VIEW "issue_with_ranks_missing" AS

71 SELECT * FROM "issue"

72 WHERE "fully_frozen" NOTNULL

73 AND "closed" NOTNULL

74 AND "ranks_available" = FALSE;

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

78 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.';

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

98 ( "positive_votes" ISNULL AND "negative_votes" ISNULL AND

99 "direct_majority" ISNULL AND "indirect_majority" ISNULL AND

100 "schulze_rank" ISNULL AND

101 "better_than_status_quo" ISNULL AND "worse_than_status_quo" ISNULL AND

102 "reverse_beat_path" ISNULL AND "multistage_majority" ISNULL AND

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

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

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

106 "eligible" = FALSE OR

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

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

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

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

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

112

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

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

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

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

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

118 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';

119 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';

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

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

122 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';

123

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

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

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

127 "winning_initiative_id" != "losing_initiative_id" OR

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

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

130

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

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

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

134

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

136 -- more later

137

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

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

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

141

142 DROP TRIGGER "update_text_search_data" ON "suggestion";

143

144 CREATE TRIGGER "update_text_search_data"

145 BEFORE INSERT OR UPDATE ON "suggestion"

146 FOR EACH ROW EXECUTE PROCEDURE

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

148 "name", "content");

149

150 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")';

151

152 CREATE TABLE "rendered_suggestion" (

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

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

155 "format" TEXT,

156 "content" TEXT NOT NULL );

157

158 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)';

159

160 DROP VIEW "area_member_count";

161

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

163

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

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

166

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

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

169

170 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")';

171

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

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

174

175 DROP TRIGGER "default_for_draft_id" ON "supporter";

176 DROP FUNCTION "supporter_default_for_draft_id_trigger"();

177

178 CREATE FUNCTION "default_for_draft_id_trigger"()

179 RETURNS TRIGGER

180 LANGUAGE 'plpgsql' VOLATILE AS $$

181 BEGIN

182 IF NEW."draft_id" ISNULL THEN

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

184 WHERE "initiative_id" = NEW."initiative_id";

185 END IF;

186 RETURN NEW;

187 END;

188 $$;

189

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

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

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

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

194

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

196 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';

197 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';

198

199 CREATE VIEW "area_member_count" AS

200 SELECT

201 "area"."id" AS "area_id",

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

203 coalesce(

204 sum(

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

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

207 ELSE 0 END

208 )

209 ) AS "member_weight"

210 FROM "area"

211 LEFT JOIN "membership"

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

213 LEFT JOIN "privilege"

214 ON "privilege"."unit_id" = "area"."unit_id"

215 AND "privilege"."member_id" = "membership"."member_id"

216 AND "privilege"."voting_right"

217 LEFT JOIN "member"

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

219 AND "member"."active"

220 GROUP BY "area"."id";

221

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

223

224 CREATE VIEW "battle_participant" AS

225 SELECT "initiative"."id", "initiative"."issue_id"

226 FROM "issue" JOIN "initiative"

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

228 WHERE "initiative"."admitted"

229 UNION ALL

230 SELECT NULL, "id" AS "issue_id"

231 FROM "issue";

232

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

234

235 CREATE OR REPLACE VIEW "battle_view" AS

236 SELECT

237 "issue"."id" AS "issue_id",

238 "winning_initiative"."id" AS "winning_initiative_id",

239 "losing_initiative"."id" AS "losing_initiative_id",

240 sum(

241 CASE WHEN

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

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

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

245 ) AS "count"

246 FROM "issue"

247 LEFT JOIN "direct_voter"

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

249 JOIN "battle_participant" AS "winning_initiative"

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

251 JOIN "battle_participant" AS "losing_initiative"

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

253 LEFT JOIN "vote" AS "better_vote"

254 ON "direct_voter"."member_id" = "better_vote"."member_id"

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

256 LEFT JOIN "vote" AS "worse_vote"

257 ON "direct_voter"."member_id" = "worse_vote"."member_id"

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

259 WHERE "issue"."closed" NOTNULL

260 AND "issue"."cleaned" ISNULL

261 AND (

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

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

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

265 GROUP BY

266 "issue"."id",

267 "winning_initiative"."id",

268 "losing_initiative"."id";

269

270 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';

271

272 CREATE OR REPLACE FUNCTION "create_interest_snapshot"

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

274 RETURNS VOID

275 LANGUAGE 'plpgsql' VOLATILE AS $$

276 DECLARE

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

278 BEGIN

279 DELETE FROM "direct_interest_snapshot"

280 WHERE "issue_id" = "issue_id_p"

281 AND "event" = 'periodic';

282 DELETE FROM "delegating_interest_snapshot"

283 WHERE "issue_id" = "issue_id_p"

284 AND "event" = 'periodic';

285 DELETE FROM "direct_supporter_snapshot"

286 WHERE "issue_id" = "issue_id_p"

287 AND "event" = 'periodic';

288 INSERT INTO "direct_interest_snapshot"

289 ("issue_id", "event", "member_id")

290 SELECT

291 "issue_id_p" AS "issue_id",

292 'periodic' AS "event",

293 "member"."id" AS "member_id"

294 FROM "issue"

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

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

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

298 JOIN "privilege"

299 ON "privilege"."unit_id" = "area"."unit_id"

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

301 WHERE "issue"."id" = "issue_id_p"

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

303 FOR "member_id_v" IN

304 SELECT "member_id" FROM "direct_interest_snapshot"

305 WHERE "issue_id" = "issue_id_p"

306 AND "event" = 'periodic'

307 LOOP

308 UPDATE "direct_interest_snapshot" SET

309 "weight" = 1 +

310 "weight_of_added_delegations_for_interest_snapshot"(

311 "issue_id_p",

312 "member_id_v",

313 '{}'

314 )

315 WHERE "issue_id" = "issue_id_p"

316 AND "event" = 'periodic'

317 AND "member_id" = "member_id_v";

318 END LOOP;

319 INSERT INTO "direct_supporter_snapshot"

320 ( "issue_id", "initiative_id", "event", "member_id",

321 "informed", "satisfied" )

322 SELECT

323 "issue_id_p" AS "issue_id",

324 "initiative"."id" AS "initiative_id",

325 'periodic' AS "event",

326 "supporter"."member_id" AS "member_id",

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

328 NOT EXISTS (

329 SELECT NULL FROM "critical_opinion"

330 WHERE "initiative_id" = "initiative"."id"

331 AND "member_id" = "supporter"."member_id"

332 ) AS "satisfied"

333 FROM "initiative"

334 JOIN "supporter"

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

336 JOIN "current_draft"

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

338 JOIN "direct_interest_snapshot"

339 ON "supporter"."member_id" = "direct_interest_snapshot"."member_id"

340 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"

341 AND "event" = 'periodic'

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

343 RETURN;

344 END;

345 $$;

346

347 CREATE OR REPLACE FUNCTION "create_snapshot"

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

349 RETURNS VOID

350 LANGUAGE 'plpgsql' VOLATILE AS $$

351 DECLARE

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

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

354 BEGIN

355 PERFORM "lock_issue"("issue_id_p");

356 PERFORM "create_population_snapshot"("issue_id_p");

357 PERFORM "create_interest_snapshot"("issue_id_p");

358 UPDATE "issue" SET

359 "snapshot" = now(),

360 "latest_snapshot_event" = 'periodic',

361 "population" = (

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

363 FROM "direct_population_snapshot"

364 WHERE "issue_id" = "issue_id_p"

365 AND "event" = 'periodic'

366 )

367 WHERE "id" = "issue_id_p";

368 FOR "initiative_id_v" IN

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

370 LOOP

371 UPDATE "initiative" SET

372 "supporter_count" = (

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

374 FROM "direct_interest_snapshot" AS "di"

375 JOIN "direct_supporter_snapshot" AS "ds"

376 ON "di"."member_id" = "ds"."member_id"

377 WHERE "di"."issue_id" = "issue_id_p"

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

379 AND "ds"."initiative_id" = "initiative_id_v"

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

381 ),

382 "informed_supporter_count" = (

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

384 FROM "direct_interest_snapshot" AS "di"

385 JOIN "direct_supporter_snapshot" AS "ds"

386 ON "di"."member_id" = "ds"."member_id"

387 WHERE "di"."issue_id" = "issue_id_p"

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

389 AND "ds"."initiative_id" = "initiative_id_v"

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

391 AND "ds"."informed"

392 ),

393 "satisfied_supporter_count" = (

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

395 FROM "direct_interest_snapshot" AS "di"

396 JOIN "direct_supporter_snapshot" AS "ds"

397 ON "di"."member_id" = "ds"."member_id"

398 WHERE "di"."issue_id" = "issue_id_p"

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

400 AND "ds"."initiative_id" = "initiative_id_v"

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

402 AND "ds"."satisfied"

403 ),

404 "satisfied_informed_supporter_count" = (

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

406 FROM "direct_interest_snapshot" AS "di"

407 JOIN "direct_supporter_snapshot" AS "ds"

408 ON "di"."member_id" = "ds"."member_id"

409 WHERE "di"."issue_id" = "issue_id_p"

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

411 AND "ds"."initiative_id" = "initiative_id_v"

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

413 AND "ds"."informed"

414 AND "ds"."satisfied"

415 )

416 WHERE "id" = "initiative_id_v";

417 FOR "suggestion_id_v" IN

418 SELECT "id" FROM "suggestion"

419 WHERE "initiative_id" = "initiative_id_v"

420 LOOP

421 UPDATE "suggestion" SET

422 "minus2_unfulfilled_count" = (

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

424 FROM "issue" CROSS JOIN "opinion"

425 JOIN "direct_interest_snapshot" AS "snapshot"

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

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

428 AND "snapshot"."member_id" = "opinion"."member_id"

429 WHERE "issue"."id" = "issue_id_p"

430 AND "opinion"."suggestion_id" = "suggestion_id_v"

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

432 AND "opinion"."fulfilled" = FALSE

433 ),

434 "minus2_fulfilled_count" = (

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

436 FROM "issue" CROSS JOIN "opinion"

437 JOIN "direct_interest_snapshot" AS "snapshot"

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

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

440 AND "snapshot"."member_id" = "opinion"."member_id"

441 WHERE "issue"."id" = "issue_id_p"

442 AND "opinion"."suggestion_id" = "suggestion_id_v"

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

444 AND "opinion"."fulfilled" = TRUE

445 ),

446 "minus1_unfulfilled_count" = (

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

448 FROM "issue" CROSS JOIN "opinion"

449 JOIN "direct_interest_snapshot" AS "snapshot"

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

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

452 AND "snapshot"."member_id" = "opinion"."member_id"

453 WHERE "issue"."id" = "issue_id_p"

454 AND "opinion"."suggestion_id" = "suggestion_id_v"

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

456 AND "opinion"."fulfilled" = FALSE

457 ),

458 "minus1_fulfilled_count" = (

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

460 FROM "issue" CROSS JOIN "opinion"

461 JOIN "direct_interest_snapshot" AS "snapshot"

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

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

464 AND "snapshot"."member_id" = "opinion"."member_id"

465 WHERE "issue"."id" = "issue_id_p"

466 AND "opinion"."suggestion_id" = "suggestion_id_v"

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

468 AND "opinion"."fulfilled" = TRUE

469 ),

470 "plus1_unfulfilled_count" = (

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

472 FROM "issue" CROSS JOIN "opinion"

473 JOIN "direct_interest_snapshot" AS "snapshot"

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

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

476 AND "snapshot"."member_id" = "opinion"."member_id"

477 WHERE "issue"."id" = "issue_id_p"

478 AND "opinion"."suggestion_id" = "suggestion_id_v"

479 AND "opinion"."degree" = 1

480 AND "opinion"."fulfilled" = FALSE

481 ),

482 "plus1_fulfilled_count" = (

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

484 FROM "issue" CROSS JOIN "opinion"

485 JOIN "direct_interest_snapshot" AS "snapshot"

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

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

488 AND "snapshot"."member_id" = "opinion"."member_id"

489 WHERE "issue"."id" = "issue_id_p"

490 AND "opinion"."suggestion_id" = "suggestion_id_v"

491 AND "opinion"."degree" = 1

492 AND "opinion"."fulfilled" = TRUE

493 ),

494 "plus2_unfulfilled_count" = (

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

496 FROM "issue" CROSS JOIN "opinion"

497 JOIN "direct_interest_snapshot" AS "snapshot"

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

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

500 AND "snapshot"."member_id" = "opinion"."member_id"

501 WHERE "issue"."id" = "issue_id_p"

502 AND "opinion"."suggestion_id" = "suggestion_id_v"

503 AND "opinion"."degree" = 2

504 AND "opinion"."fulfilled" = FALSE

505 ),

506 "plus2_fulfilled_count" = (

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

508 FROM "issue" CROSS JOIN "opinion"

509 JOIN "direct_interest_snapshot" AS "snapshot"

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

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

512 AND "snapshot"."member_id" = "opinion"."member_id"

513 WHERE "issue"."id" = "issue_id_p"

514 AND "opinion"."suggestion_id" = "suggestion_id_v"

515 AND "opinion"."degree" = 2

516 AND "opinion"."fulfilled" = TRUE

517 )

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

519 END LOOP;

520 END LOOP;

521 RETURN;

522 END;

523 $$;

524

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

526 RETURNS VOID

527 LANGUAGE 'plpgsql' VOLATILE AS $$

528 DECLARE

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

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

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

532 BEGIN

533 PERFORM "lock_issue"("issue_id_p");

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

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

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

537 DELETE FROM "delegating_voter"

538 WHERE "issue_id" = "issue_id_p";

539 -- delete votes from non-privileged voters:

540 DELETE FROM "direct_voter"

541 USING (

542 SELECT

543 "direct_voter"."member_id"

544 FROM "direct_voter"

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

546 LEFT JOIN "privilege"

547 ON "privilege"."unit_id" = "unit_id_v"

548 AND "privilege"."member_id" = "direct_voter"."member_id"

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

550 "member"."active" = FALSE OR

551 "privilege"."voting_right" ISNULL OR

552 "privilege"."voting_right" = FALSE

553 )

554 ) AS "subquery"

555 WHERE "direct_voter"."issue_id" = "issue_id_p"

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

557 -- consider delegations:

558 UPDATE "direct_voter" SET "weight" = 1

559 WHERE "issue_id" = "issue_id_p";

560 PERFORM "add_vote_delegations"("issue_id_p");

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

562 UPDATE "issue" SET

563 "state" = 'calculation',

564 "closed" = now(),

565 "voter_count" = (

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

567 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"

568 )

569 WHERE "id" = "issue_id_p";

570 -- materialize battle_view:

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

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

573 INSERT INTO "battle" (

574 "issue_id",

575 "winning_initiative_id", "losing_initiative_id",

576 "count"

577 ) SELECT

578 "issue_id",

579 "winning_initiative_id", "losing_initiative_id",

580 "count"

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

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

583 UPDATE "initiative" SET

584 "positive_votes" = "battle_win"."count",

585 "negative_votes" = "battle_lose"."count"

586 FROM "battle" AS "battle_win", "battle" AS "battle_lose"

587 WHERE

588 "battle_win"."issue_id" = "issue_id_p" AND

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

590 "battle_win"."losing_initiative_id" ISNULL AND

591 "battle_lose"."issue_id" = "issue_id_p" AND

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

593 "battle_lose"."winning_initiative_id" ISNULL;

594 END;

595 $$;

596

597 DROP FUNCTION "array_init_string"(INTEGER);

598 DROP FUNCTION "square_matrix_init_string"(INTEGER);

599

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

601 RETURNS VOID

602 LANGUAGE 'plpgsql' VOLATILE AS $$

603 DECLARE

604 "issue_row" "issue"%ROWTYPE;

605 "policy_row" "policy"%ROWTYPE;

606 "dimension_v" INTEGER;

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

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

609 "i" INTEGER;

610 "j" INTEGER;

611 "k" INTEGER;

612 "battle_row" "battle"%ROWTYPE;

613 "rank_ary" INT4[];

614 "rank_v" INT4;

615 "done_v" INTEGER;

616 "winners_ary" INTEGER[];

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

618 BEGIN

619 SELECT * INTO "issue_row"

620 FROM "issue" WHERE "id" = "issue_id_p"

621 FOR UPDATE;

622 SELECT * INTO "policy_row"

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

624 SELECT count(1) INTO "dimension_v"

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

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

627 -- comparison:

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

629 "i" := 1;

630 "j" := 2;

631 FOR "battle_row" IN

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

633 ORDER BY

634 "winning_initiative_id" NULLS LAST,

635 "losing_initiative_id" NULLS LAST

636 LOOP

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

638 IF "j" = "dimension_v" THEN

639 "i" := "i" + 1;

640 "j" := 1;

641 ELSE

642 "j" := "j" + 1;

643 IF "j" = "i" THEN

644 "j" := "j" + 1;

645 END IF;

646 END IF;

647 END LOOP;

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

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

650 END IF;

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

652 -- function:

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

654 "i" := 1;

655 LOOP

656 "j" := 1;

657 LOOP

658 IF "i" != "j" THEN

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

660 "vote_matrix"["i"]["j"],

661 "vote_matrix"["j"]["i"]

662 );

663 END IF;

664 EXIT WHEN "j" = "dimension_v";

665 "j" := "j" + 1;

666 END LOOP;

667 EXIT WHEN "i" = "dimension_v";

668 "i" := "i" + 1;

669 END LOOP;

670 -- Find best paths:

671 "i" := 1;

672 LOOP

673 "j" := 1;

674 LOOP

675 IF "i" != "j" THEN

676 "k" := 1;

677 LOOP

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

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

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

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

682 END IF;

683 ELSE

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

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

686 END IF;

687 END IF;

688 END IF;

689 EXIT WHEN "k" = "dimension_v";

690 "k" := "k" + 1;

691 END LOOP;

692 END IF;

693 EXIT WHEN "j" = "dimension_v";

694 "j" := "j" + 1;

695 END LOOP;

696 EXIT WHEN "i" = "dimension_v";

697 "i" := "i" + 1;

698 END LOOP;

699 -- Determine order of winners:

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

701 "rank_v" := 1;

702 "done_v" := 0;

703 LOOP

704 "winners_ary" := '{}';

705 "i" := 1;

706 LOOP

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

708 "j" := 1;

709 LOOP

710 IF

711 "i" != "j" AND

712 "rank_ary"["j"] ISNULL AND

713 "matrix"["j"]["i"] > "matrix"["i"]["j"]

714 THEN

715 -- someone else is better

716 EXIT;

717 END IF;

718 IF "j" = "dimension_v" THEN

719 -- noone is better

720 "winners_ary" := "winners_ary" || "i";

721 EXIT;

722 END IF;

723 "j" := "j" + 1;

724 END LOOP;

725 END IF;

726 EXIT WHEN "i" = "dimension_v";

727 "i" := "i" + 1;

728 END LOOP;

729 "i" := 1;

730 LOOP

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

732 "done_v" := "done_v" + 1;

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

734 "i" := "i" + 1;

735 END LOOP;

736 EXIT WHEN "done_v" = "dimension_v";

737 "rank_v" := "rank_v" + 1;

738 END LOOP;

739 -- write preliminary results:

740 "i" := 1;

741 FOR "initiative_id_v" IN

742 SELECT "id" FROM "initiative"

743 WHERE "issue_id" = "issue_id_p" AND "admitted"

744 ORDER BY "id"

745 LOOP

746 UPDATE "initiative" SET

747 "direct_majority" =

748 CASE WHEN "policy_row"."direct_majority_strict" THEN

749 "positive_votes" * "policy_row"."direct_majority_den" >

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

751 ELSE

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

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

754 END

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

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

757 "policy_row"."direct_majority_non_negative",

758 "indirect_majority" =

759 CASE WHEN "policy_row"."indirect_majority_strict" THEN

760 "positive_votes" * "policy_row"."indirect_majority_den" >

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

762 ELSE

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

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

765 END

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

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

768 "policy_row"."indirect_majority_non_negative",

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

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

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

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

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

774 "winner" = FALSE

775 WHERE "id" = "initiative_id_v";

776 "i" := "i" + 1;

777 END LOOP;

778 IF "i" != "dimension_v" THEN

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

780 END IF;

781 -- take indirect majorities into account:

782 LOOP

783 UPDATE "initiative" SET "indirect_majority" = TRUE

784 FROM (

785 SELECT "new_initiative"."id" AS "initiative_id"

786 FROM "initiative" "old_initiative"

787 JOIN "initiative" "new_initiative"

788 ON "new_initiative"."issue_id" = "issue_id_p"

789 AND "new_initiative"."indirect_majority" = FALSE

790 JOIN "battle" "battle_win"

791 ON "battle_win"."issue_id" = "issue_id_p"

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

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

794 JOIN "battle" "battle_lose"

795 ON "battle_lose"."issue_id" = "issue_id_p"

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

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

798 WHERE "old_initiative"."issue_id" = "issue_id_p"

799 AND "old_initiative"."indirect_majority" = TRUE

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

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

802 "policy_row"."indirect_majority_num" *

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

804 ELSE

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

806 "policy_row"."indirect_majority_num" *

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

808 END

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

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

811 "policy_row"."indirect_majority_non_negative"

812 ) AS "subquery"

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

814 EXIT WHEN NOT FOUND;

815 END LOOP;

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

817 UPDATE "initiative" SET "multistage_majority" = TRUE

818 FROM (

819 SELECT "losing_initiative"."id" AS "initiative_id"

820 FROM "initiative" "losing_initiative"

821 JOIN "initiative" "winning_initiative"

822 ON "winning_initiative"."issue_id" = "issue_id_p"

823 AND "winning_initiative"."admitted"

824 JOIN "battle" "battle_win"

825 ON "battle_win"."issue_id" = "issue_id_p"

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

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

828 JOIN "battle" "battle_lose"

829 ON "battle_lose"."issue_id" = "issue_id_p"

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

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

832 WHERE "losing_initiative"."issue_id" = "issue_id_p"

833 AND "losing_initiative"."admitted"

834 AND "winning_initiative"."schulze_rank" <

835 "losing_initiative"."schulze_rank"

836 AND "battle_win"."count" > "battle_lose"."count"

837 AND (

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

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

840 ) AS "subquery"

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

842 -- mark eligible initiatives:

843 UPDATE "initiative" SET "eligible" = TRUE

844 WHERE "issue_id" = "issue_id_p"

845 AND "initiative"."direct_majority"

846 AND "initiative"."indirect_majority"

847 AND "initiative"."better_than_status_quo"

848 AND (

849 "policy_row"."no_multistage_majority" = FALSE OR

850 "initiative"."multistage_majority" = FALSE )

851 AND (

852 "policy_row"."no_reverse_beat_path" = FALSE OR

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

854 -- mark final winner:

855 UPDATE "initiative" SET "winner" = TRUE

856 FROM (

857 SELECT "id" AS "initiative_id"

858 FROM "initiative"

859 WHERE "issue_id" = "issue_id_p" AND "eligible"

860 ORDER BY "schulze_rank", "id"

861 LIMIT 1

862 ) AS "subquery"

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

864 -- write (final) ranks:

865 "rank_v" := 1;

866 FOR "initiative_id_v" IN

867 SELECT "id"

868 FROM "initiative"

869 WHERE "issue_id" = "issue_id_p" AND "admitted"

870 ORDER BY

871 "winner" DESC,

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

873 "schulze_rank",

874 "id"

875 LOOP

876 UPDATE "initiative" SET "rank" = "rank_v"

877 WHERE "id" = "initiative_id_v";

878 "rank_v" := "rank_v" + 1;

879 END LOOP;

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

881 UPDATE "issue" SET

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

883 "state" =

884 CASE WHEN EXISTS (

885 SELECT NULL FROM "initiative"

886 WHERE "issue_id" = "issue_id_p" AND "winner"

887 ) THEN

888 'finished_with_winner'::"issue_state"

889 ELSE

890 'finished_without_winner'::"issue_state"

891 END,

892 "ranks_available" = TRUE

893 WHERE "id" = "issue_id_p";

894 RETURN;

895 END;

896 $$;

897

898 CREATE OR REPLACE FUNCTION "check_issue"

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

900 RETURNS VOID

901 LANGUAGE 'plpgsql' VOLATILE AS $$

902 DECLARE

903 "issue_row" "issue"%ROWTYPE;

904 "policy_row" "policy"%ROWTYPE;

905 BEGIN

906 PERFORM "lock_issue"("issue_id_p");

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

908 -- only process open issues:

909 IF "issue_row"."closed" ISNULL THEN

910 SELECT * INTO "policy_row" FROM "policy"

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

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

913 IF "issue_row"."fully_frozen" ISNULL THEN

914 PERFORM "create_snapshot"("issue_id_p");

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

916 END IF;

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

918 IF "issue_row"."accepted" ISNULL THEN

919 IF EXISTS (

920 SELECT NULL FROM "initiative"

921 WHERE "issue_id" = "issue_id_p"

922 AND "supporter_count" > 0

923 AND "supporter_count" * "policy_row"."issue_quorum_den"

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

925 ) THEN

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

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

928 -- NOTE: "issue_row" used later

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

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

931 UPDATE "issue" SET

932 "state" = "issue_row"."state",

933 "accepted" = "issue_row"."accepted"

934 WHERE "id" = "issue_row"."id";

935 ELSIF

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

937 THEN

938 -- close issues, if admission time has expired

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

940 UPDATE "issue" SET

941 "state" = 'canceled_issue_not_accepted',

942 "closed" = now()

943 WHERE "id" = "issue_row"."id";

944 END IF;

945 END IF;

946 -- eventually half freeze issues:

947 IF

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

949 "issue_row"."accepted" NOTNULL AND

950 "issue_row"."half_frozen" ISNULL

951 THEN

952 IF

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

954 THEN

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

956 -- NOTE: "issue_row" used later

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

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

959 UPDATE "issue" SET

960 "state" = "issue_row"."state",

961 "half_frozen" = "issue_row"."half_frozen"

962 WHERE "id" = "issue_row"."id";

963 END IF;

964 END IF;

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

966 IF

967 "issue_row"."closed" ISNULL AND

968 NOT EXISTS (

969 -- all initiatives are revoked

970 SELECT NULL FROM "initiative"

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

972 ) AND (

973 -- and issue has not been accepted yet

974 "issue_row"."accepted" ISNULL OR

975 NOT EXISTS (

976 -- or no initiatives have been revoked lately

977 SELECT NULL FROM "initiative"

978 WHERE "issue_id" = "issue_id_p"

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

980 ) OR (

981 -- or verification time has elapsed

982 "issue_row"."half_frozen" NOTNULL AND

983 "issue_row"."fully_frozen" ISNULL AND

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

985 )

986 )

987 THEN

988 -- NOTE: "issue_row" used later

989 IF "issue_row"."accepted" ISNULL THEN

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

991 ELSIF "issue_row"."half_frozen" ISNULL THEN

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

993 ELSE

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

995 END IF;

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

997 UPDATE "issue" SET

998 "state" = "issue_row"."state",

999 "closed" = "issue_row"."closed"

1000 WHERE "id" = "issue_row"."id";

1001 END IF;

1002 -- fully freeze issue after verification time:

1003 IF

1004 "issue_row"."half_frozen" NOTNULL AND

1005 "issue_row"."fully_frozen" ISNULL AND

1006 "issue_row"."closed" ISNULL AND

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

1008 THEN

1009 PERFORM "freeze_after_snapshot"("issue_id_p");

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

1011 END IF;

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

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

1014 IF

1015 "issue_row"."closed" ISNULL AND

1016 "issue_row"."fully_frozen" NOTNULL AND

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

1018 THEN

1019 PERFORM "close_voting"("issue_id_p");

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

1021 PERFORM "calculate_ranks"("issue_id_p");

1022 END IF;

1023 END IF;

1024 RETURN;

1025 END;

1026 $$;

1027

1028 -- TODO: recalculate voting results

1029

1030 COMMIT;

1031

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

1033 FROM (

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

1035 "suggestion"."id" AS "suggestion_id",

1036 "draft"."id" AS "draft_id"

1037 FROM "suggestion" JOIN "draft"

1038 ON "suggestion"."initiative_id" = "draft"."initiative_id"

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

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

1041 ) AS "subquery"

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

1043

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