liquid_feedback_core: ed2f94a397cd update/core-update.v1.4.0-v1.5.0.sql

liquid_feedback_core

view update/core-update.v1.4.0-v1.5.0.sql @ 183:ed2f94a397cd

Dropped session table (it is up to frontends to have their own session store)

author	jbe
date	Fri Jul 29 16:04:17 2011 +0200 (2011-07-29)
parents	4c03ef70dd92
children	af3d208e81be

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 "invite_code" TEXT UNIQUE;

10 ALTER TABLE "member" ADD COLUMN "admin_comment" TEXT;

11 ALTER TABLE "member" ADD COLUMN "activated" TIMESTAMPTZ;

12 ALTER TABLE "member" ALTER COLUMN "active" SET DEFAULT FALSE;

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

15 COMMENT ON COLUMN "member"."created" IS 'Creation of member record and/or invite code';

16 COMMENT ON COLUMN "member"."invite_code" IS 'Optional invite code, to allow a member to initialize his/her account the first time';

17 COMMENT ON COLUMN "member"."activated" IS 'Timestamp of activation of account (i.e. usage of "invite_code"); needs to be set for "active" members';

18 COMMENT ON COLUMN "member"."active" IS 'Memberships, support and votes are taken into account when corresponding members are marked as active. When the user does not log in for an extended period of time, this flag may be set to FALSE. If the user is not locked, he/she may reset the active flag by logging in (has to be set to TRUE by frontend on every login).';

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

21 CREATE TABLE "rendered_member_statement" (

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

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

24 "format" TEXT,

25 "content" TEXT NOT NULL );

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

29 DROP VIEW "expired_session";

30 DROP TABLE "session";

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

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

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

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

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

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

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

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

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

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

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

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

45 UPDATE "policy" SET

46 "direct_majority_num" = "majority_num",

47 "direct_majority_den" = "majority_den",

48 "direct_majority_strict" = "majority_strict",

49 "indirect_majority_num" = "majority_num",

50 "indirect_majority_den" = "majority_den",

51 "indirect_majority_strict" = "majority_strict";

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

72 DROP VIEW "open_issue";

73 DROP VIEW "issue_with_ranks_missing";

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

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

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

79 CREATE VIEW "open_issue" AS

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

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

84 CREATE VIEW "issue_with_ranks_missing" AS

85 SELECT * FROM "issue"

86 WHERE "fully_frozen" NOTNULL

87 AND "closed" NOTNULL

88 AND "ranks_available" = FALSE;

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

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

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

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

96 DROP VIEW "battle_view";

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

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

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

101

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

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

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

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

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

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

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

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

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

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

112

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

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

115 ( "positive_votes" ISNULL AND "negative_votes" ISNULL AND

116 "direct_majority" ISNULL AND "indirect_majority" ISNULL AND

117 "schulze_rank" ISNULL AND

118 "better_than_status_quo" ISNULL AND "worse_than_status_quo" ISNULL AND

119 "reverse_beat_path" ISNULL AND "multistage_majority" ISNULL AND

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

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

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

123 "eligible" = FALSE OR

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

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

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

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

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

129

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

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

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

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

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

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

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

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

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

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

140

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

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

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

144 "winning_initiative_id" != "losing_initiative_id" OR

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

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

147

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

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

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

151

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

153 -- more later

154

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

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

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

158

159 DROP TRIGGER "update_text_search_data" ON "suggestion";

160

161 CREATE TRIGGER "update_text_search_data"

162 BEFORE INSERT OR UPDATE ON "suggestion"

163 FOR EACH ROW EXECUTE PROCEDURE

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

165 "name", "content");

166

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

168

169 CREATE TABLE "rendered_suggestion" (

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

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

172 "format" TEXT,

173 "content" TEXT NOT NULL );

174

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

176

177 DROP TABLE "invite_code_unit";

178

179 DROP VIEW "area_member_count";

180

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

182

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

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

185

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

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

188

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

190

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

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

193

194 DROP TRIGGER "default_for_draft_id" ON "supporter";

195 DROP FUNCTION "supporter_default_for_draft_id_trigger"();

196

197 CREATE FUNCTION "default_for_draft_id_trigger"()

198 RETURNS TRIGGER

199 LANGUAGE 'plpgsql' VOLATILE AS $$

200 BEGIN

201 IF NEW."draft_id" ISNULL THEN

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

203 WHERE "initiative_id" = NEW."initiative_id";

204 END IF;

205 RETURN NEW;

206 END;

207 $$;

208

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

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

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

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

213

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

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

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

217

218 CREATE VIEW "area_member_count" AS

219 SELECT

220 "area"."id" AS "area_id",

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

222 coalesce(

223 sum(

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

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

226 ELSE 0 END

227 )

228 ) AS "member_weight"

229 FROM "area"

230 LEFT JOIN "membership"

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

232 LEFT JOIN "privilege"

233 ON "privilege"."unit_id" = "area"."unit_id"

234 AND "privilege"."member_id" = "membership"."member_id"

235 AND "privilege"."voting_right"

236 LEFT JOIN "member"

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

238 AND "member"."active"

239 GROUP BY "area"."id";

240

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

242

243 CREATE VIEW "battle_participant" AS

244 SELECT "initiative"."id", "initiative"."issue_id"

245 FROM "issue" JOIN "initiative"

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

247 WHERE "initiative"."admitted"

248 UNION ALL

249 SELECT NULL, "id" AS "issue_id"

250 FROM "issue";

251

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

253

254 CREATE VIEW "battle_view" AS

255 SELECT

256 "issue"."id" AS "issue_id",

257 "winning_initiative"."id" AS "winning_initiative_id",

258 "losing_initiative"."id" AS "losing_initiative_id",

259 sum(

260 CASE WHEN

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

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

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

264 ) AS "count"

265 FROM "issue"

266 LEFT JOIN "direct_voter"

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

268 JOIN "battle_participant" AS "winning_initiative"

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

270 JOIN "battle_participant" AS "losing_initiative"

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

272 LEFT JOIN "vote" AS "better_vote"

273 ON "direct_voter"."member_id" = "better_vote"."member_id"

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

275 LEFT JOIN "vote" AS "worse_vote"

276 ON "direct_voter"."member_id" = "worse_vote"."member_id"

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

278 WHERE "issue"."closed" NOTNULL

279 AND "issue"."cleaned" ISNULL

280 AND (

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

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

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

284 GROUP BY

285 "issue"."id",

286 "winning_initiative"."id",

287 "losing_initiative"."id";

288

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

290

291 CREATE OR REPLACE FUNCTION "create_interest_snapshot"

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

293 RETURNS VOID

294 LANGUAGE 'plpgsql' VOLATILE AS $$

295 DECLARE

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

297 BEGIN

298 DELETE FROM "direct_interest_snapshot"

299 WHERE "issue_id" = "issue_id_p"

300 AND "event" = 'periodic';

301 DELETE FROM "delegating_interest_snapshot"

302 WHERE "issue_id" = "issue_id_p"

303 AND "event" = 'periodic';

304 DELETE FROM "direct_supporter_snapshot"

305 WHERE "issue_id" = "issue_id_p"

306 AND "event" = 'periodic';

307 INSERT INTO "direct_interest_snapshot"

308 ("issue_id", "event", "member_id")

309 SELECT

310 "issue_id_p" AS "issue_id",

311 'periodic' AS "event",

312 "member"."id" AS "member_id"

313 FROM "issue"

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

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

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

317 JOIN "privilege"

318 ON "privilege"."unit_id" = "area"."unit_id"

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

320 WHERE "issue"."id" = "issue_id_p"

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

322 FOR "member_id_v" IN

323 SELECT "member_id" FROM "direct_interest_snapshot"

324 WHERE "issue_id" = "issue_id_p"

325 AND "event" = 'periodic'

326 LOOP

327 UPDATE "direct_interest_snapshot" SET

328 "weight" = 1 +

329 "weight_of_added_delegations_for_interest_snapshot"(

330 "issue_id_p",

331 "member_id_v",

332 '{}'

333 )

334 WHERE "issue_id" = "issue_id_p"

335 AND "event" = 'periodic'

336 AND "member_id" = "member_id_v";

337 END LOOP;

338 INSERT INTO "direct_supporter_snapshot"

339 ( "issue_id", "initiative_id", "event", "member_id",

340 "informed", "satisfied" )

341 SELECT

342 "issue_id_p" AS "issue_id",

343 "initiative"."id" AS "initiative_id",

344 'periodic' AS "event",

345 "supporter"."member_id" AS "member_id",

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

347 NOT EXISTS (

348 SELECT NULL FROM "critical_opinion"

349 WHERE "initiative_id" = "initiative"."id"

350 AND "member_id" = "supporter"."member_id"

351 ) AS "satisfied"

352 FROM "initiative"

353 JOIN "supporter"

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

355 JOIN "current_draft"

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

357 JOIN "direct_interest_snapshot"

358 ON "supporter"."member_id" = "direct_interest_snapshot"."member_id"

359 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"

360 AND "event" = 'periodic'

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

362 RETURN;

363 END;

364 $$;

365

366 CREATE OR REPLACE FUNCTION "create_snapshot"

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

368 RETURNS VOID

369 LANGUAGE 'plpgsql' VOLATILE AS $$

370 DECLARE

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

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

373 BEGIN

374 PERFORM "lock_issue"("issue_id_p");

375 PERFORM "create_population_snapshot"("issue_id_p");

376 PERFORM "create_interest_snapshot"("issue_id_p");

377 UPDATE "issue" SET

378 "snapshot" = now(),

379 "latest_snapshot_event" = 'periodic',

380 "population" = (

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

382 FROM "direct_population_snapshot"

383 WHERE "issue_id" = "issue_id_p"

384 AND "event" = 'periodic'

385 )

386 WHERE "id" = "issue_id_p";

387 FOR "initiative_id_v" IN

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

389 LOOP

390 UPDATE "initiative" SET

391 "supporter_count" = (

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

393 FROM "direct_interest_snapshot" AS "di"

394 JOIN "direct_supporter_snapshot" AS "ds"

395 ON "di"."member_id" = "ds"."member_id"

396 WHERE "di"."issue_id" = "issue_id_p"

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

398 AND "ds"."initiative_id" = "initiative_id_v"

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

400 ),

401 "informed_supporter_count" = (

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

403 FROM "direct_interest_snapshot" AS "di"

404 JOIN "direct_supporter_snapshot" AS "ds"

405 ON "di"."member_id" = "ds"."member_id"

406 WHERE "di"."issue_id" = "issue_id_p"

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

408 AND "ds"."initiative_id" = "initiative_id_v"

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

410 AND "ds"."informed"

411 ),

412 "satisfied_supporter_count" = (

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

414 FROM "direct_interest_snapshot" AS "di"

415 JOIN "direct_supporter_snapshot" AS "ds"

416 ON "di"."member_id" = "ds"."member_id"

417 WHERE "di"."issue_id" = "issue_id_p"

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

419 AND "ds"."initiative_id" = "initiative_id_v"

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

421 AND "ds"."satisfied"

422 ),

423 "satisfied_informed_supporter_count" = (

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

425 FROM "direct_interest_snapshot" AS "di"

426 JOIN "direct_supporter_snapshot" AS "ds"

427 ON "di"."member_id" = "ds"."member_id"

428 WHERE "di"."issue_id" = "issue_id_p"

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

430 AND "ds"."initiative_id" = "initiative_id_v"

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

432 AND "ds"."informed"

433 AND "ds"."satisfied"

434 )

435 WHERE "id" = "initiative_id_v";

436 FOR "suggestion_id_v" IN

437 SELECT "id" FROM "suggestion"

438 WHERE "initiative_id" = "initiative_id_v"

439 LOOP

440 UPDATE "suggestion" SET

441 "minus2_unfulfilled_count" = (

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

443 FROM "issue" CROSS JOIN "opinion"

444 JOIN "direct_interest_snapshot" AS "snapshot"

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

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

447 AND "snapshot"."member_id" = "opinion"."member_id"

448 WHERE "issue"."id" = "issue_id_p"

449 AND "opinion"."suggestion_id" = "suggestion_id_v"

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

451 AND "opinion"."fulfilled" = FALSE

452 ),

453 "minus2_fulfilled_count" = (

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

455 FROM "issue" CROSS JOIN "opinion"

456 JOIN "direct_interest_snapshot" AS "snapshot"

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

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

459 AND "snapshot"."member_id" = "opinion"."member_id"

460 WHERE "issue"."id" = "issue_id_p"

461 AND "opinion"."suggestion_id" = "suggestion_id_v"

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

463 AND "opinion"."fulfilled" = TRUE

464 ),

465 "minus1_unfulfilled_count" = (

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

467 FROM "issue" CROSS JOIN "opinion"

468 JOIN "direct_interest_snapshot" AS "snapshot"

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

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

471 AND "snapshot"."member_id" = "opinion"."member_id"

472 WHERE "issue"."id" = "issue_id_p"

473 AND "opinion"."suggestion_id" = "suggestion_id_v"

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

475 AND "opinion"."fulfilled" = FALSE

476 ),

477 "minus1_fulfilled_count" = (

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

479 FROM "issue" CROSS JOIN "opinion"

480 JOIN "direct_interest_snapshot" AS "snapshot"

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

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

483 AND "snapshot"."member_id" = "opinion"."member_id"

484 WHERE "issue"."id" = "issue_id_p"

485 AND "opinion"."suggestion_id" = "suggestion_id_v"

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

487 AND "opinion"."fulfilled" = TRUE

488 ),

489 "plus1_unfulfilled_count" = (

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

491 FROM "issue" CROSS JOIN "opinion"

492 JOIN "direct_interest_snapshot" AS "snapshot"

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

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

495 AND "snapshot"."member_id" = "opinion"."member_id"

496 WHERE "issue"."id" = "issue_id_p"

497 AND "opinion"."suggestion_id" = "suggestion_id_v"

498 AND "opinion"."degree" = 1

499 AND "opinion"."fulfilled" = FALSE

500 ),

501 "plus1_fulfilled_count" = (

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

503 FROM "issue" CROSS JOIN "opinion"

504 JOIN "direct_interest_snapshot" AS "snapshot"

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

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

507 AND "snapshot"."member_id" = "opinion"."member_id"

508 WHERE "issue"."id" = "issue_id_p"

509 AND "opinion"."suggestion_id" = "suggestion_id_v"

510 AND "opinion"."degree" = 1

511 AND "opinion"."fulfilled" = TRUE

512 ),

513 "plus2_unfulfilled_count" = (

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

515 FROM "issue" CROSS JOIN "opinion"

516 JOIN "direct_interest_snapshot" AS "snapshot"

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

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

519 AND "snapshot"."member_id" = "opinion"."member_id"

520 WHERE "issue"."id" = "issue_id_p"

521 AND "opinion"."suggestion_id" = "suggestion_id_v"

522 AND "opinion"."degree" = 2

523 AND "opinion"."fulfilled" = FALSE

524 ),

525 "plus2_fulfilled_count" = (

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

527 FROM "issue" CROSS JOIN "opinion"

528 JOIN "direct_interest_snapshot" AS "snapshot"

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

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

531 AND "snapshot"."member_id" = "opinion"."member_id"

532 WHERE "issue"."id" = "issue_id_p"

533 AND "opinion"."suggestion_id" = "suggestion_id_v"

534 AND "opinion"."degree" = 2

535 AND "opinion"."fulfilled" = TRUE

536 )

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

538 END LOOP;

539 END LOOP;

540 RETURN;

541 END;

542 $$;

543

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

545 RETURNS VOID

546 LANGUAGE 'plpgsql' VOLATILE AS $$

547 DECLARE

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

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

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

551 BEGIN

552 PERFORM "lock_issue"("issue_id_p");

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

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

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

556 DELETE FROM "delegating_voter"

557 WHERE "issue_id" = "issue_id_p";

558 -- delete votes from non-privileged voters:

559 DELETE FROM "direct_voter"

560 USING (

561 SELECT

562 "direct_voter"."member_id"

563 FROM "direct_voter"

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

565 LEFT JOIN "privilege"

566 ON "privilege"."unit_id" = "unit_id_v"

567 AND "privilege"."member_id" = "direct_voter"."member_id"

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

569 "member"."active" = FALSE OR

570 "privilege"."voting_right" ISNULL OR

571 "privilege"."voting_right" = FALSE

572 )

573 ) AS "subquery"

574 WHERE "direct_voter"."issue_id" = "issue_id_p"

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

576 -- consider delegations:

577 UPDATE "direct_voter" SET "weight" = 1

578 WHERE "issue_id" = "issue_id_p";

579 PERFORM "add_vote_delegations"("issue_id_p");

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

581 UPDATE "issue" SET

582 "state" = 'calculation',

583 "closed" = now(),

584 "voter_count" = (

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

586 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"

587 )

588 WHERE "id" = "issue_id_p";

589 -- materialize battle_view:

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

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

592 INSERT INTO "battle" (

593 "issue_id",

594 "winning_initiative_id", "losing_initiative_id",

595 "count"

596 ) SELECT

597 "issue_id",

598 "winning_initiative_id", "losing_initiative_id",

599 "count"

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

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

602 UPDATE "initiative" SET

603 "positive_votes" = "battle_win"."count",

604 "negative_votes" = "battle_lose"."count"

605 FROM "battle" AS "battle_win", "battle" AS "battle_lose"

606 WHERE

607 "battle_win"."issue_id" = "issue_id_p" AND

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

609 "battle_win"."losing_initiative_id" ISNULL AND

610 "battle_lose"."issue_id" = "issue_id_p" AND

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

612 "battle_lose"."winning_initiative_id" ISNULL;

613 END;

614 $$;

615

616 DROP FUNCTION "array_init_string"(INTEGER);

617 DROP FUNCTION "square_matrix_init_string"(INTEGER);

618

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

620 RETURNS VOID

621 LANGUAGE 'plpgsql' VOLATILE AS $$

622 DECLARE

623 "issue_row" "issue"%ROWTYPE;

624 "policy_row" "policy"%ROWTYPE;

625 "dimension_v" INTEGER;

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

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

628 "i" INTEGER;

629 "j" INTEGER;

630 "k" INTEGER;

631 "battle_row" "battle"%ROWTYPE;

632 "rank_ary" INT4[];

633 "rank_v" INT4;

634 "done_v" INTEGER;

635 "winners_ary" INTEGER[];

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

637 BEGIN

638 SELECT * INTO "issue_row"

639 FROM "issue" WHERE "id" = "issue_id_p"

640 FOR UPDATE;

641 SELECT * INTO "policy_row"

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

643 SELECT count(1) INTO "dimension_v"

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

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

646 -- comparison:

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

648 "i" := 1;

649 "j" := 2;

650 FOR "battle_row" IN

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

652 ORDER BY

653 "winning_initiative_id" NULLS LAST,

654 "losing_initiative_id" NULLS LAST

655 LOOP

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

657 IF "j" = "dimension_v" THEN

658 "i" := "i" + 1;

659 "j" := 1;

660 ELSE

661 "j" := "j" + 1;

662 IF "j" = "i" THEN

663 "j" := "j" + 1;

664 END IF;

665 END IF;

666 END LOOP;

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

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

669 END IF;

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

671 -- function:

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

673 "i" := 1;

674 LOOP

675 "j" := 1;

676 LOOP

677 IF "i" != "j" THEN

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

679 "vote_matrix"["i"]["j"],

680 "vote_matrix"["j"]["i"]

681 );

682 END IF;

683 EXIT WHEN "j" = "dimension_v";

684 "j" := "j" + 1;

685 END LOOP;

686 EXIT WHEN "i" = "dimension_v";

687 "i" := "i" + 1;

688 END LOOP;

689 -- Find best paths:

690 "i" := 1;

691 LOOP

692 "j" := 1;

693 LOOP

694 IF "i" != "j" THEN

695 "k" := 1;

696 LOOP

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

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

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

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

701 END IF;

702 ELSE

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

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

705 END IF;

706 END IF;

707 END IF;

708 EXIT WHEN "k" = "dimension_v";

709 "k" := "k" + 1;

710 END LOOP;

711 END IF;

712 EXIT WHEN "j" = "dimension_v";

713 "j" := "j" + 1;

714 END LOOP;

715 EXIT WHEN "i" = "dimension_v";

716 "i" := "i" + 1;

717 END LOOP;

718 -- Determine order of winners:

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

720 "rank_v" := 1;

721 "done_v" := 0;

722 LOOP

723 "winners_ary" := '{}';

724 "i" := 1;

725 LOOP

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

727 "j" := 1;

728 LOOP

729 IF

730 "i" != "j" AND

731 "rank_ary"["j"] ISNULL AND

732 "matrix"["j"]["i"] > "matrix"["i"]["j"]

733 THEN

734 -- someone else is better

735 EXIT;

736 END IF;

737 IF "j" = "dimension_v" THEN

738 -- noone is better

739 "winners_ary" := "winners_ary" || "i";

740 EXIT;

741 END IF;

742 "j" := "j" + 1;

743 END LOOP;

744 END IF;

745 EXIT WHEN "i" = "dimension_v";

746 "i" := "i" + 1;

747 END LOOP;

748 "i" := 1;

749 LOOP

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

751 "done_v" := "done_v" + 1;

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

753 "i" := "i" + 1;

754 END LOOP;

755 EXIT WHEN "done_v" = "dimension_v";

756 "rank_v" := "rank_v" + 1;

757 END LOOP;

758 -- write preliminary results:

759 "i" := 1;

760 FOR "initiative_id_v" IN

761 SELECT "id" FROM "initiative"

762 WHERE "issue_id" = "issue_id_p" AND "admitted"

763 ORDER BY "id"

764 LOOP

765 UPDATE "initiative" SET

766 "direct_majority" =

767 CASE WHEN "policy_row"."direct_majority_strict" THEN

768 "positive_votes" * "policy_row"."direct_majority_den" >

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

770 ELSE

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

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

773 END

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

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

776 "policy_row"."direct_majority_non_negative",

777 "indirect_majority" =

778 CASE WHEN "policy_row"."indirect_majority_strict" THEN

779 "positive_votes" * "policy_row"."indirect_majority_den" >

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

781 ELSE

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

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

784 END

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

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

787 "policy_row"."indirect_majority_non_negative",

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

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

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

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

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

793 "winner" = FALSE

794 WHERE "id" = "initiative_id_v";

795 "i" := "i" + 1;

796 END LOOP;

797 IF "i" != "dimension_v" THEN

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

799 END IF;

800 -- take indirect majorities into account:

801 LOOP

802 UPDATE "initiative" SET "indirect_majority" = TRUE

803 FROM (

804 SELECT "new_initiative"."id" AS "initiative_id"

805 FROM "initiative" "old_initiative"

806 JOIN "initiative" "new_initiative"

807 ON "new_initiative"."issue_id" = "issue_id_p"

808 AND "new_initiative"."indirect_majority" = FALSE

809 JOIN "battle" "battle_win"

810 ON "battle_win"."issue_id" = "issue_id_p"

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

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

813 JOIN "battle" "battle_lose"

814 ON "battle_lose"."issue_id" = "issue_id_p"

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

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

817 WHERE "old_initiative"."issue_id" = "issue_id_p"

818 AND "old_initiative"."indirect_majority" = TRUE

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

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

821 "policy_row"."indirect_majority_num" *

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

823 ELSE

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

825 "policy_row"."indirect_majority_num" *

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

827 END

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

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

830 "policy_row"."indirect_majority_non_negative"

831 ) AS "subquery"

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

833 EXIT WHEN NOT FOUND;

834 END LOOP;

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

836 UPDATE "initiative" SET "multistage_majority" = TRUE

837 FROM (

838 SELECT "losing_initiative"."id" AS "initiative_id"

839 FROM "initiative" "losing_initiative"

840 JOIN "initiative" "winning_initiative"

841 ON "winning_initiative"."issue_id" = "issue_id_p"

842 AND "winning_initiative"."admitted"

843 JOIN "battle" "battle_win"

844 ON "battle_win"."issue_id" = "issue_id_p"

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

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

847 JOIN "battle" "battle_lose"

848 ON "battle_lose"."issue_id" = "issue_id_p"

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

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

851 WHERE "losing_initiative"."issue_id" = "issue_id_p"

852 AND "losing_initiative"."admitted"

853 AND "winning_initiative"."schulze_rank" <

854 "losing_initiative"."schulze_rank"

855 AND "battle_win"."count" > "battle_lose"."count"

856 AND (

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

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

859 ) AS "subquery"

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

861 -- mark eligible initiatives:

862 UPDATE "initiative" SET "eligible" = TRUE

863 WHERE "issue_id" = "issue_id_p"

864 AND "initiative"."direct_majority"

865 AND "initiative"."indirect_majority"

866 AND "initiative"."better_than_status_quo"

867 AND (

868 "policy_row"."no_multistage_majority" = FALSE OR

869 "initiative"."multistage_majority" = FALSE )

870 AND (

871 "policy_row"."no_reverse_beat_path" = FALSE OR

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

873 -- mark final winner:

874 UPDATE "initiative" SET "winner" = TRUE

875 FROM (

876 SELECT "id" AS "initiative_id"

877 FROM "initiative"

878 WHERE "issue_id" = "issue_id_p" AND "eligible"

879 ORDER BY "schulze_rank", "id"

880 LIMIT 1

881 ) AS "subquery"

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

883 -- write (final) ranks:

884 "rank_v" := 1;

885 FOR "initiative_id_v" IN

886 SELECT "id"

887 FROM "initiative"

888 WHERE "issue_id" = "issue_id_p" AND "admitted"

889 ORDER BY

890 "winner" DESC,

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

892 "schulze_rank",

893 "id"

894 LOOP

895 UPDATE "initiative" SET "rank" = "rank_v"

896 WHERE "id" = "initiative_id_v";

897 "rank_v" := "rank_v" + 1;

898 END LOOP;

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

900 UPDATE "issue" SET

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

902 "state" =

903 CASE WHEN EXISTS (

904 SELECT NULL FROM "initiative"

905 WHERE "issue_id" = "issue_id_p" AND "winner"

906 ) THEN

907 'finished_with_winner'::"issue_state"

908 ELSE

909 'finished_without_winner'::"issue_state"

910 END,

911 "ranks_available" = TRUE

912 WHERE "id" = "issue_id_p";

913 RETURN;

914 END;

915 $$;

916

917 CREATE OR REPLACE FUNCTION "check_issue"

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

919 RETURNS VOID

920 LANGUAGE 'plpgsql' VOLATILE AS $$

921 DECLARE

922 "issue_row" "issue"%ROWTYPE;

923 "policy_row" "policy"%ROWTYPE;

924 BEGIN

925 PERFORM "lock_issue"("issue_id_p");

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

927 -- only process open issues:

928 IF "issue_row"."closed" ISNULL THEN

929 SELECT * INTO "policy_row" FROM "policy"

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

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

932 IF "issue_row"."fully_frozen" ISNULL THEN

933 PERFORM "create_snapshot"("issue_id_p");

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

935 END IF;

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

937 IF "issue_row"."accepted" ISNULL THEN

938 IF EXISTS (

939 SELECT NULL FROM "initiative"

940 WHERE "issue_id" = "issue_id_p"

941 AND "supporter_count" > 0

942 AND "supporter_count" * "policy_row"."issue_quorum_den"

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

944 ) THEN

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

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

947 -- NOTE: "issue_row" used later

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

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

950 UPDATE "issue" SET

951 "state" = "issue_row"."state",

952 "accepted" = "issue_row"."accepted"

953 WHERE "id" = "issue_row"."id";

954 ELSIF

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

956 THEN

957 -- close issues, if admission time has expired

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

959 UPDATE "issue" SET

960 "state" = 'canceled_issue_not_accepted',

961 "closed" = now()

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

963 END IF;

964 END IF;

965 -- eventually half freeze issues:

966 IF

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

968 "issue_row"."accepted" NOTNULL AND

969 "issue_row"."half_frozen" ISNULL

970 THEN

971 IF

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

973 THEN

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

975 -- NOTE: "issue_row" used later

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

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

978 UPDATE "issue" SET

979 "state" = "issue_row"."state",

980 "half_frozen" = "issue_row"."half_frozen"

981 WHERE "id" = "issue_row"."id";

982 END IF;

983 END IF;

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

985 IF

986 "issue_row"."closed" ISNULL AND

987 NOT EXISTS (

988 -- all initiatives are revoked

989 SELECT NULL FROM "initiative"

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

991 ) AND (

992 -- and issue has not been accepted yet

993 "issue_row"."accepted" ISNULL OR

994 NOT EXISTS (

995 -- or no initiatives have been revoked lately

996 SELECT NULL FROM "initiative"

997 WHERE "issue_id" = "issue_id_p"

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

999 ) OR (

1000 -- or verification time has elapsed

1001 "issue_row"."half_frozen" NOTNULL AND

1002 "issue_row"."fully_frozen" ISNULL AND

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

1004 )

1005 )

1006 THEN

1007 -- NOTE: "issue_row" used later

1008 IF "issue_row"."accepted" ISNULL THEN

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

1010 ELSIF "issue_row"."half_frozen" ISNULL THEN

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

1012 ELSE

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

1014 END IF;

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

1016 UPDATE "issue" SET

1017 "state" = "issue_row"."state",

1018 "closed" = "issue_row"."closed"

1019 WHERE "id" = "issue_row"."id";

1020 END IF;

1021 -- fully freeze issue after verification time:

1022 IF

1023 "issue_row"."half_frozen" NOTNULL AND

1024 "issue_row"."fully_frozen" ISNULL AND

1025 "issue_row"."closed" ISNULL AND

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

1027 THEN

1028 PERFORM "freeze_after_snapshot"("issue_id_p");

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

1030 END IF;

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

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

1033 IF

1034 "issue_row"."closed" ISNULL AND

1035 "issue_row"."fully_frozen" NOTNULL AND

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

1037 THEN

1038 PERFORM "close_voting"("issue_id_p");

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

1040 PERFORM "calculate_ranks"("issue_id_p");

1041 END IF;

1042 END IF;

1043 RETURN;

1044 END;

1045 $$;

1046

1047 CREATE OR REPLACE FUNCTION "check_everything"()

1048 RETURNS VOID

1049 LANGUAGE 'plpgsql' VOLATILE AS $$

1050 DECLARE

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

1052 BEGIN

1053 PERFORM "check_last_login"();

1054 PERFORM "calculate_member_counts"();

1055 FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP

1056 PERFORM "check_issue"("issue_id_v");

1057 END LOOP;

1058 FOR "issue_id_v" IN SELECT "id" FROM "issue_with_ranks_missing" LOOP

1059 PERFORM "calculate_ranks"("issue_id_v");

1060 END LOOP;

1061 RETURN;

1062 END;

1063 $$;

1064

1065 CREATE OR REPLACE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)

1066 RETURNS VOID

1067 LANGUAGE 'plpgsql' VOLATILE AS $$

1068 BEGIN

1069 UPDATE "member" SET

1070 "last_login" = NULL,

1071 "last_login_public" = NULL,

1072 "login" = NULL,

1073 "password" = NULL,

1074 "locked" = TRUE,

1075 "active" = FALSE,

1076 "notify_email" = NULL,

1077 "notify_email_unconfirmed" = NULL,

1078 "notify_email_secret" = NULL,

1079 "notify_email_secret_expiry" = NULL,

1080 "notify_email_lock_expiry" = NULL,

1081 "password_reset_secret" = NULL,

1082 "password_reset_secret_expiry" = NULL,

1083 "organizational_unit" = NULL,

1084 "internal_posts" = NULL,

1085 "realname" = NULL,

1086 "birthday" = NULL,

1087 "address" = NULL,

1088 "email" = NULL,

1089 "xmpp_address" = NULL,

1090 "website" = NULL,

1091 "phone" = NULL,

1092 "mobile_phone" = NULL,

1093 "profession" = NULL,

1094 "external_memberships" = NULL,

1095 "external_posts" = NULL,

1096 "statement" = NULL

1097 WHERE "id" = "member_id_p";

1098 -- "text_search_data" is updated by triggers

1099 DELETE FROM "setting" WHERE "member_id" = "member_id_p";

1100 DELETE FROM "setting_map" WHERE "member_id" = "member_id_p";

1101 DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p";

1102 DELETE FROM "member_image" WHERE "member_id" = "member_id_p";

1103 DELETE FROM "contact" WHERE "member_id" = "member_id_p";

1104 DELETE FROM "ignored_member" WHERE "member_id" = "member_id_p";

1105 DELETE FROM "area_setting" WHERE "member_id" = "member_id_p";

1106 DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p";

1107 DELETE FROM "ignored_initiative" WHERE "member_id" = "member_id_p";

1108 DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p";

1109 DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p";

1110 DELETE FROM "membership" WHERE "member_id" = "member_id_p";

1111 DELETE FROM "delegation" WHERE "truster_id" = "member_id_p";

1112 DELETE FROM "non_voter" WHERE "member_id" = "member_id_p";

1113 DELETE FROM "direct_voter" USING "issue"

1114 WHERE "direct_voter"."issue_id" = "issue"."id"

1115 AND "issue"."closed" ISNULL

1116 AND "member_id" = "member_id_p";

1117 RETURN;

1118 END;

1119 $$;

1120

1121 CREATE OR REPLACE FUNCTION "delete_private_data"()

1122 RETURNS VOID

1123 LANGUAGE 'plpgsql' VOLATILE AS $$

1124 BEGIN

1125 UPDATE "member" SET

1126 "last_login" = NULL,

1127 "login" = NULL,

1128 "password" = NULL,

1129 "notify_email" = NULL,

1130 "notify_email_unconfirmed" = NULL,

1131 "notify_email_secret" = NULL,

1132 "notify_email_secret_expiry" = NULL,

1133 "notify_email_lock_expiry" = NULL,

1134 "password_reset_secret" = NULL,

1135 "password_reset_secret_expiry" = NULL,

1136 "organizational_unit" = NULL,

1137 "internal_posts" = NULL,

1138 "realname" = NULL,

1139 "birthday" = NULL,

1140 "address" = NULL,

1141 "email" = NULL,

1142 "xmpp_address" = NULL,

1143 "website" = NULL,

1144 "phone" = NULL,

1145 "mobile_phone" = NULL,

1146 "profession" = NULL,

1147 "external_memberships" = NULL,

1148 "external_posts" = NULL,

1149 "statement" = NULL;

1150 -- "text_search_data" is updated by triggers

1151 DELETE FROM "invite_code";

1152 DELETE FROM "setting";

1153 DELETE FROM "setting_map";

1154 DELETE FROM "member_relation_setting";

1155 DELETE FROM "member_image";

1156 DELETE FROM "contact";

1157 DELETE FROM "ignored_member";

1158 DELETE FROM "area_setting";

1159 DELETE FROM "issue_setting";

1160 DELETE FROM "ignored_initiative";

1161 DELETE FROM "initiative_setting";

1162 DELETE FROM "suggestion_setting";

1163 DELETE FROM "non_voter";

1164 DELETE FROM "direct_voter" USING "issue"

1165 WHERE "direct_voter"."issue_id" = "issue"."id"

1166 AND "issue"."closed" ISNULL;

1167 RETURN;

1168 END;

1169 $$;

1170

1171 COMMIT;

1172

1173 BEGIN;

1174

1175 UPDATE "member" SET "activated" = "created";

1176

1177 UPDATE "member" SET

1178 "created" = "invite_code"."created",

1179 "invite_code" = "invite_code"."code",

1180 "admin_comment" = "invite_code"."comment"

1181 FROM "invite_code"

1182 WHERE "member"."id" = "invite_code"."member_id";

1183

1184 DROP TABLE "invite_code";

1185

1186 UPDATE "initiative" SET

1187 "direct_majority" = "rank" NOTNULL,

1188 "indirect_majority" = "rank" NOTNULL,

1189 "schulze_rank" = "rank",

1190 "better_than_status_quo" = "rank" NOTNULL,

1191 "worse_than_status_quo" = "rank" ISNULL,

1192 "reverse_beat_path" = "rank" ISNULL,

1193 "multistage_majority" = "rank" ISNULL,

1194 "eligible" = "rank" NOTNULL,

1195 "winner" = ("rank" = 1)

1196 FROM "issue"

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

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

1199 AND "initiative"."admitted";

1200

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

1202 FROM (

1203 SELECT

1204 "issue"."id" AS "issue_id",

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

1206 FROM "issue" JOIN "initiative"

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

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

1209 AND "initiative"."admitted"

1210 GROUP BY "issue"."id"

1211 ) AS "subquery"

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

1213

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

1215 RETURNS VOID

1216 LANGUAGE 'plpgsql' AS $$

1217 DECLARE

1218 "rank_v" INT4;

1219 "initiative_id_v" INT4;

1220 BEGIN

1221 SELECT "status_quo_schulze_rank" INTO "rank_v"

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

1223 FOR "initiative_id_v" IN

1224 SELECT "id" FROM "initiative"

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

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

1227 LOOP

1228 UPDATE "initiative" SET

1229 "schulze_rank" = "rank_v" + 1,

1230 "rank" = "rank_v"

1231 WHERE "id" = "initiative_id_v";

1232 "rank_v" := "rank_v" + 1;

1233 END LOOP;

1234 RETURN;

1235 END;

1236 $$;

1237

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

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

1240

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

1242

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

1244 FROM (

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

1246 "suggestion"."id" AS "suggestion_id",

1247 "draft"."id" AS "draft_id"

1248 FROM "suggestion" JOIN "draft"

1249 ON "suggestion"."initiative_id" = "draft"."initiative_id"

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

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

1252 ) AS "subquery"

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

1254

1255 COMMIT;

1256

1257 ALTER TABLE "member" ADD CONSTRAINT "not_active_without_activated"

1258 CHECK ("activated" NOTNULL OR "active" = FALSE);

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