liquid_feedback_core: 69d6fba0f84c update/core-update.v1.4.0-v2.0.0.sql

liquid_feedback_core

view update/core-update.v1.4.0-v2.0.0.sql @ 295:69d6fba0f84c

Use EXCLUSIVE MODE table locks in function "lock_issue"("issue"."id")

Avoids deadlocks caused by explicit FOR UPDATE row locks when updating member statements and implicit FOR SHARE row locks when writing snapshots.

author	jbe
date	Thu Sep 13 17:02:22 2012 +0200 (2012-09-13)
parents	d4c754c4aeaa
children

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 ('2.0.0', 2, 0, 0))

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" ADD COLUMN "last_activity" DATE;

13 ALTER TABLE "member" DROP COLUMN "last_login_public";

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

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

17 -- Backported fix of future version to include unused invite codes in member table:

18 ALTER TABLE "member" ALTER COLUMN "name" DROP NOT NULL;

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

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

22 COMMENT ON COLUMN "member"."admin_comment" IS 'Hidden comment for administrative purposes';

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

24 COMMENT ON COLUMN "member"."last_activity" IS 'Date of last activity of member; required to be set for "active" members';

25 COMMENT ON COLUMN "member"."active" IS 'Memberships, support and votes are taken into account when corresponding members are marked as active. Automatically set to FALSE, if "last_activity" is older than "system_setting"."member_ttl".';

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

28 CREATE TYPE "application_access_level" AS ENUM

29 ('member', 'full', 'pseudonymous', 'anonymous');

31 COMMENT ON TYPE "application_access_level" IS 'Access privileges for applications using the API';

33 CREATE TABLE "member_application" (

34 "id" SERIAL8 PRIMARY KEY,

35 UNIQUE ("member_id", "name"),

36 "member_id" INT4 NOT NULL REFERENCES "member" ("id")

37 ON DELETE CASCADE ON UPDATE CASCADE,

38 "name" TEXT NOT NULL,

39 "comment" TEXT,

40 "access_level" "application_access_level" NOT NULL,

41 "key" TEXT NOT NULL UNIQUE,

42 "last_usage" TIMESTAMPTZ );

44 COMMENT ON TABLE "member_application" IS 'Registered application being allowed to use the API';

46 CREATE TABLE "rendered_member_statement" (

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

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

49 "format" TEXT,

50 "content" TEXT NOT NULL );

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

54 DROP VIEW "expired_session";

55 DROP TABLE "session";

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

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

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

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

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

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

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

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

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

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

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

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

70 UPDATE "policy" SET

71 "direct_majority_num" = "majority_num",

72 "direct_majority_den" = "majority_den",

73 "direct_majority_strict" = "majority_strict",

74 "indirect_majority_num" = "majority_num",

75 "indirect_majority_den" = "majority_den",

76 "indirect_majority_strict" = "majority_strict";

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

97 DROP VIEW "open_issue";

98 DROP VIEW "issue_with_ranks_missing";

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

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

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

103

104 CREATE VIEW "open_issue" AS

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

106

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

108

109 CREATE VIEW "issue_with_ranks_missing" AS

110 SELECT * FROM "issue"

111 WHERE "fully_frozen" NOTNULL

112 AND "closed" NOTNULL

113 AND "ranks_available" = FALSE;

114

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

116

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

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

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

120

121 DROP VIEW "battle_view";

122

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

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

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

126

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

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

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

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

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

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

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

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

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

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

137

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

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

140 ( "positive_votes" ISNULL AND "negative_votes" ISNULL AND

141 "direct_majority" ISNULL AND "indirect_majority" ISNULL AND

142 "schulze_rank" ISNULL AND

143 "better_than_status_quo" ISNULL AND "worse_than_status_quo" ISNULL AND

144 "reverse_beat_path" ISNULL AND "multistage_majority" ISNULL AND

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

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

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

148 "eligible" = FALSE OR

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

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

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

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

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

154

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

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

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

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

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

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

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

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

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

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

165

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

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

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

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

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

171 "winning_initiative_id" != "losing_initiative_id" OR

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

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

174

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

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

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

178

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

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

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

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

183

184 DROP TRIGGER "update_text_search_data" ON "suggestion";

185

186 CREATE TRIGGER "update_text_search_data"

187 BEFORE INSERT OR UPDATE ON "suggestion"

188 FOR EACH ROW EXECUTE PROCEDURE

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

190 "name", "content");

191

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

193

194 CREATE TABLE "rendered_suggestion" (

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

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

197 "format" TEXT,

198 "content" TEXT NOT NULL );

199

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

201

202 DROP TABLE "invite_code_unit";

203

204 DROP VIEW "area_member_count";

205

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

207

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

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

210

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

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

213

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

215

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

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

218

219 DROP TRIGGER "default_for_draft_id" ON "supporter";

220 DROP FUNCTION "supporter_default_for_draft_id_trigger"();

221

222 CREATE FUNCTION "default_for_draft_id_trigger"()

223 RETURNS TRIGGER

224 LANGUAGE 'plpgsql' VOLATILE AS $$

225 BEGIN

226 IF NEW."draft_id" ISNULL THEN

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

228 WHERE "initiative_id" = NEW."initiative_id";

229 END IF;

230 RETURN NEW;

231 END;

232 $$;

233

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

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

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

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

238

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

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

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

242

243 CREATE VIEW "area_member_count" AS

244 SELECT

245 "area"."id" AS "area_id",

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

247 coalesce(

248 sum(

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

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

251 ELSE 0 END

252 )

253 ) AS "member_weight"

254 FROM "area"

255 LEFT JOIN "membership"

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

257 LEFT JOIN "privilege"

258 ON "privilege"."unit_id" = "area"."unit_id"

259 AND "privilege"."member_id" = "membership"."member_id"

260 AND "privilege"."voting_right"

261 LEFT JOIN "member"

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

263 AND "member"."active"

264 GROUP BY "area"."id";

265

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

267

268 CREATE VIEW "battle_participant" AS

269 SELECT "initiative"."id", "initiative"."issue_id"

270 FROM "issue" JOIN "initiative"

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

272 WHERE "initiative"."admitted"

273 UNION ALL

274 SELECT NULL, "id" AS "issue_id"

275 FROM "issue";

276

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

278

279 CREATE VIEW "battle_view" AS

280 SELECT

281 "issue"."id" AS "issue_id",

282 "winning_initiative"."id" AS "winning_initiative_id",

283 "losing_initiative"."id" AS "losing_initiative_id",

284 sum(

285 CASE WHEN

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

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

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

289 ) AS "count"

290 FROM "issue"

291 LEFT JOIN "direct_voter"

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

293 JOIN "battle_participant" AS "winning_initiative"

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

295 JOIN "battle_participant" AS "losing_initiative"

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

297 LEFT JOIN "vote" AS "better_vote"

298 ON "direct_voter"."member_id" = "better_vote"."member_id"

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

300 LEFT JOIN "vote" AS "worse_vote"

301 ON "direct_voter"."member_id" = "worse_vote"."member_id"

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

303 WHERE "issue"."closed" NOTNULL

304 AND "issue"."cleaned" ISNULL

305 AND (

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

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

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

309 GROUP BY

310 "issue"."id",

311 "winning_initiative"."id",

312 "losing_initiative"."id";

313

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

315

316 DROP FUNCTION "check_last_login"();

317

318 CREATE FUNCTION "check_activity"()

319 RETURNS VOID

320 LANGUAGE 'plpgsql' VOLATILE AS $$

321 DECLARE

322 "system_setting_row" "system_setting"%ROWTYPE;

323 BEGIN

324 SELECT * INTO "system_setting_row" FROM "system_setting";

325 LOCK TABLE "member" IN SHARE ROW EXCLUSIVE MODE;

326 IF "system_setting_row"."member_ttl" NOTNULL THEN

327 UPDATE "member" SET "active" = FALSE

328 WHERE "active" = TRUE

329 AND "last_activity" < (now() - "system_setting_row"."member_ttl")::DATE;

330 END IF;

331 RETURN;

332 END;

333 $$;

334

335 COMMENT ON FUNCTION "check_activity"() IS 'Deactivates members when "last_activity" is older than "system_setting"."member_ttl".';

336

337 CREATE OR REPLACE FUNCTION "calculate_member_counts"()

338 RETURNS VOID

339 LANGUAGE 'plpgsql' VOLATILE AS $$

340 BEGIN

341 LOCK TABLE "member" IN SHARE MODE;

342 LOCK TABLE "member_count" IN EXCLUSIVE MODE;

343 LOCK TABLE "unit" IN EXCLUSIVE MODE;

344 LOCK TABLE "area" IN EXCLUSIVE MODE;

345 LOCK TABLE "privilege" IN SHARE MODE;

346 LOCK TABLE "membership" IN SHARE MODE;

347 DELETE FROM "member_count";

348 INSERT INTO "member_count" ("total_count")

349 SELECT "total_count" FROM "member_count_view";

350 UPDATE "unit" SET "member_count" = "view"."member_count"

351 FROM "unit_member_count" AS "view"

352 WHERE "view"."unit_id" = "unit"."id";

353 UPDATE "area" SET

354 "direct_member_count" = "view"."direct_member_count",

355 "member_weight" = "view"."member_weight"

356 FROM "area_member_count" AS "view"

357 WHERE "view"."area_id" = "area"."id";

358 RETURN;

359 END;

360 $$;

361

362 CREATE OR REPLACE FUNCTION "create_interest_snapshot"

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

364 RETURNS VOID

365 LANGUAGE 'plpgsql' VOLATILE AS $$

366 DECLARE

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

368 BEGIN

369 DELETE FROM "direct_interest_snapshot"

370 WHERE "issue_id" = "issue_id_p"

371 AND "event" = 'periodic';

372 DELETE FROM "delegating_interest_snapshot"

373 WHERE "issue_id" = "issue_id_p"

374 AND "event" = 'periodic';

375 DELETE FROM "direct_supporter_snapshot"

376 WHERE "issue_id" = "issue_id_p"

377 AND "event" = 'periodic';

378 INSERT INTO "direct_interest_snapshot"

379 ("issue_id", "event", "member_id")

380 SELECT

381 "issue_id_p" AS "issue_id",

382 'periodic' AS "event",

383 "member"."id" AS "member_id"

384 FROM "issue"

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

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

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

388 JOIN "privilege"

389 ON "privilege"."unit_id" = "area"."unit_id"

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

391 WHERE "issue"."id" = "issue_id_p"

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

393 FOR "member_id_v" IN

394 SELECT "member_id" FROM "direct_interest_snapshot"

395 WHERE "issue_id" = "issue_id_p"

396 AND "event" = 'periodic'

397 LOOP

398 UPDATE "direct_interest_snapshot" SET

399 "weight" = 1 +

400 "weight_of_added_delegations_for_interest_snapshot"(

401 "issue_id_p",

402 "member_id_v",

403 '{}'

404 )

405 WHERE "issue_id" = "issue_id_p"

406 AND "event" = 'periodic'

407 AND "member_id" = "member_id_v";

408 END LOOP;

409 INSERT INTO "direct_supporter_snapshot"

410 ( "issue_id", "initiative_id", "event", "member_id",

411 "informed", "satisfied" )

412 SELECT

413 "issue_id_p" AS "issue_id",

414 "initiative"."id" AS "initiative_id",

415 'periodic' AS "event",

416 "supporter"."member_id" AS "member_id",

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

418 NOT EXISTS (

419 SELECT NULL FROM "critical_opinion"

420 WHERE "initiative_id" = "initiative"."id"

421 AND "member_id" = "supporter"."member_id"

422 ) AS "satisfied"

423 FROM "initiative"

424 JOIN "supporter"

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

426 JOIN "current_draft"

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

428 JOIN "direct_interest_snapshot"

429 ON "supporter"."member_id" = "direct_interest_snapshot"."member_id"

430 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"

431 AND "event" = 'periodic'

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

433 RETURN;

434 END;

435 $$;

436

437 CREATE OR REPLACE FUNCTION "create_snapshot"

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

439 RETURNS VOID

440 LANGUAGE 'plpgsql' VOLATILE AS $$

441 DECLARE

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

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

444 BEGIN

445 PERFORM "lock_issue"("issue_id_p");

446 PERFORM "create_population_snapshot"("issue_id_p");

447 PERFORM "create_interest_snapshot"("issue_id_p");

448 UPDATE "issue" SET

449 "snapshot" = now(),

450 "latest_snapshot_event" = 'periodic',

451 "population" = (

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

453 FROM "direct_population_snapshot"

454 WHERE "issue_id" = "issue_id_p"

455 AND "event" = 'periodic'

456 )

457 WHERE "id" = "issue_id_p";

458 FOR "initiative_id_v" IN

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

460 LOOP

461 UPDATE "initiative" SET

462 "supporter_count" = (

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

464 FROM "direct_interest_snapshot" AS "di"

465 JOIN "direct_supporter_snapshot" AS "ds"

466 ON "di"."member_id" = "ds"."member_id"

467 WHERE "di"."issue_id" = "issue_id_p"

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

469 AND "ds"."initiative_id" = "initiative_id_v"

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

471 ),

472 "informed_supporter_count" = (

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

474 FROM "direct_interest_snapshot" AS "di"

475 JOIN "direct_supporter_snapshot" AS "ds"

476 ON "di"."member_id" = "ds"."member_id"

477 WHERE "di"."issue_id" = "issue_id_p"

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

479 AND "ds"."initiative_id" = "initiative_id_v"

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

481 AND "ds"."informed"

482 ),

483 "satisfied_supporter_count" = (

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

485 FROM "direct_interest_snapshot" AS "di"

486 JOIN "direct_supporter_snapshot" AS "ds"

487 ON "di"."member_id" = "ds"."member_id"

488 WHERE "di"."issue_id" = "issue_id_p"

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

490 AND "ds"."initiative_id" = "initiative_id_v"

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

492 AND "ds"."satisfied"

493 ),

494 "satisfied_informed_supporter_count" = (

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

496 FROM "direct_interest_snapshot" AS "di"

497 JOIN "direct_supporter_snapshot" AS "ds"

498 ON "di"."member_id" = "ds"."member_id"

499 WHERE "di"."issue_id" = "issue_id_p"

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

501 AND "ds"."initiative_id" = "initiative_id_v"

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

503 AND "ds"."informed"

504 AND "ds"."satisfied"

505 )

506 WHERE "id" = "initiative_id_v";

507 FOR "suggestion_id_v" IN

508 SELECT "id" FROM "suggestion"

509 WHERE "initiative_id" = "initiative_id_v"

510 LOOP

511 UPDATE "suggestion" SET

512 "minus2_unfulfilled_count" = (

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

514 FROM "issue" CROSS JOIN "opinion"

515 JOIN "direct_interest_snapshot" AS "snapshot"

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

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

518 AND "snapshot"."member_id" = "opinion"."member_id"

519 WHERE "issue"."id" = "issue_id_p"

520 AND "opinion"."suggestion_id" = "suggestion_id_v"

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

522 AND "opinion"."fulfilled" = FALSE

523 ),

524 "minus2_fulfilled_count" = (

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

526 FROM "issue" CROSS JOIN "opinion"

527 JOIN "direct_interest_snapshot" AS "snapshot"

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

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

530 AND "snapshot"."member_id" = "opinion"."member_id"

531 WHERE "issue"."id" = "issue_id_p"

532 AND "opinion"."suggestion_id" = "suggestion_id_v"

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

534 AND "opinion"."fulfilled" = TRUE

535 ),

536 "minus1_unfulfilled_count" = (

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

538 FROM "issue" CROSS JOIN "opinion"

539 JOIN "direct_interest_snapshot" AS "snapshot"

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

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

542 AND "snapshot"."member_id" = "opinion"."member_id"

543 WHERE "issue"."id" = "issue_id_p"

544 AND "opinion"."suggestion_id" = "suggestion_id_v"

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

546 AND "opinion"."fulfilled" = FALSE

547 ),

548 "minus1_fulfilled_count" = (

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

550 FROM "issue" CROSS JOIN "opinion"

551 JOIN "direct_interest_snapshot" AS "snapshot"

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

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

554 AND "snapshot"."member_id" = "opinion"."member_id"

555 WHERE "issue"."id" = "issue_id_p"

556 AND "opinion"."suggestion_id" = "suggestion_id_v"

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

558 AND "opinion"."fulfilled" = TRUE

559 ),

560 "plus1_unfulfilled_count" = (

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

562 FROM "issue" CROSS JOIN "opinion"

563 JOIN "direct_interest_snapshot" AS "snapshot"

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

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

566 AND "snapshot"."member_id" = "opinion"."member_id"

567 WHERE "issue"."id" = "issue_id_p"

568 AND "opinion"."suggestion_id" = "suggestion_id_v"

569 AND "opinion"."degree" = 1

570 AND "opinion"."fulfilled" = FALSE

571 ),

572 "plus1_fulfilled_count" = (

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

574 FROM "issue" CROSS JOIN "opinion"

575 JOIN "direct_interest_snapshot" AS "snapshot"

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

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

578 AND "snapshot"."member_id" = "opinion"."member_id"

579 WHERE "issue"."id" = "issue_id_p"

580 AND "opinion"."suggestion_id" = "suggestion_id_v"

581 AND "opinion"."degree" = 1

582 AND "opinion"."fulfilled" = TRUE

583 ),

584 "plus2_unfulfilled_count" = (

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

586 FROM "issue" CROSS JOIN "opinion"

587 JOIN "direct_interest_snapshot" AS "snapshot"

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

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

590 AND "snapshot"."member_id" = "opinion"."member_id"

591 WHERE "issue"."id" = "issue_id_p"

592 AND "opinion"."suggestion_id" = "suggestion_id_v"

593 AND "opinion"."degree" = 2

594 AND "opinion"."fulfilled" = FALSE

595 ),

596 "plus2_fulfilled_count" = (

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

598 FROM "issue" CROSS JOIN "opinion"

599 JOIN "direct_interest_snapshot" AS "snapshot"

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

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

602 AND "snapshot"."member_id" = "opinion"."member_id"

603 WHERE "issue"."id" = "issue_id_p"

604 AND "opinion"."suggestion_id" = "suggestion_id_v"

605 AND "opinion"."degree" = 2

606 AND "opinion"."fulfilled" = TRUE

607 )

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

609 END LOOP;

610 END LOOP;

611 RETURN;

612 END;

613 $$;

614

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

616 RETURNS VOID

617 LANGUAGE 'plpgsql' VOLATILE AS $$

618 DECLARE

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

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

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

622 BEGIN

623 PERFORM "lock_issue"("issue_id_p");

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

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

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

627 DELETE FROM "delegating_voter"

628 WHERE "issue_id" = "issue_id_p";

629 -- delete votes from non-privileged voters:

630 DELETE FROM "direct_voter"

631 USING (

632 SELECT

633 "direct_voter"."member_id"

634 FROM "direct_voter"

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

636 LEFT JOIN "privilege"

637 ON "privilege"."unit_id" = "unit_id_v"

638 AND "privilege"."member_id" = "direct_voter"."member_id"

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

640 "member"."active" = FALSE OR

641 "privilege"."voting_right" ISNULL OR

642 "privilege"."voting_right" = FALSE

643 )

644 ) AS "subquery"

645 WHERE "direct_voter"."issue_id" = "issue_id_p"

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

647 -- consider delegations:

648 UPDATE "direct_voter" SET "weight" = 1

649 WHERE "issue_id" = "issue_id_p";

650 PERFORM "add_vote_delegations"("issue_id_p");

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

652 UPDATE "issue" SET

653 "state" = 'calculation',

654 "closed" = now(),

655 "voter_count" = (

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

657 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"

658 )

659 WHERE "id" = "issue_id_p";

660 -- materialize battle_view:

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

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

663 INSERT INTO "battle" (

664 "issue_id",

665 "winning_initiative_id", "losing_initiative_id",

666 "count"

667 ) SELECT

668 "issue_id",

669 "winning_initiative_id", "losing_initiative_id",

670 "count"

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

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

673 UPDATE "initiative" SET

674 "positive_votes" = "battle_win"."count",

675 "negative_votes" = "battle_lose"."count"

676 FROM "battle" AS "battle_win", "battle" AS "battle_lose"

677 WHERE

678 "battle_win"."issue_id" = "issue_id_p" AND

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

680 "battle_win"."losing_initiative_id" ISNULL AND

681 "battle_lose"."issue_id" = "issue_id_p" AND

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

683 "battle_lose"."winning_initiative_id" ISNULL;

684 END;

685 $$;

686

687 DROP FUNCTION "array_init_string"(INTEGER);

688 DROP FUNCTION "square_matrix_init_string"(INTEGER);

689

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

691 RETURNS VOID

692 LANGUAGE 'plpgsql' VOLATILE AS $$

693 DECLARE

694 "issue_row" "issue"%ROWTYPE;

695 "policy_row" "policy"%ROWTYPE;

696 "dimension_v" INTEGER;

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

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

699 "i" INTEGER;

700 "j" INTEGER;

701 "k" INTEGER;

702 "battle_row" "battle"%ROWTYPE;

703 "rank_ary" INT4[];

704 "rank_v" INT4;

705 "done_v" INTEGER;

706 "winners_ary" INTEGER[];

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

708 BEGIN

709 SELECT * INTO "issue_row"

710 FROM "issue" WHERE "id" = "issue_id_p"

711 FOR UPDATE;

712 SELECT * INTO "policy_row"

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

714 SELECT count(1) INTO "dimension_v"

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

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

717 -- comparison:

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

719 "i" := 1;

720 "j" := 2;

721 FOR "battle_row" IN

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

723 ORDER BY

724 "winning_initiative_id" NULLS LAST,

725 "losing_initiative_id" NULLS LAST

726 LOOP

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

728 IF "j" = "dimension_v" THEN

729 "i" := "i" + 1;

730 "j" := 1;

731 ELSE

732 "j" := "j" + 1;

733 IF "j" = "i" THEN

734 "j" := "j" + 1;

735 END IF;

736 END IF;

737 END LOOP;

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

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

740 END IF;

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

742 -- function:

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

744 "i" := 1;

745 LOOP

746 "j" := 1;

747 LOOP

748 IF "i" != "j" THEN

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

750 "vote_matrix"["i"]["j"],

751 "vote_matrix"["j"]["i"]

752 );

753 END IF;

754 EXIT WHEN "j" = "dimension_v";

755 "j" := "j" + 1;

756 END LOOP;

757 EXIT WHEN "i" = "dimension_v";

758 "i" := "i" + 1;

759 END LOOP;

760 -- Find best paths:

761 "i" := 1;

762 LOOP

763 "j" := 1;

764 LOOP

765 IF "i" != "j" THEN

766 "k" := 1;

767 LOOP

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

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

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

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

772 END IF;

773 ELSE

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

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

776 END IF;

777 END IF;

778 END IF;

779 EXIT WHEN "k" = "dimension_v";

780 "k" := "k" + 1;

781 END LOOP;

782 END IF;

783 EXIT WHEN "j" = "dimension_v";

784 "j" := "j" + 1;

785 END LOOP;

786 EXIT WHEN "i" = "dimension_v";

787 "i" := "i" + 1;

788 END LOOP;

789 -- Determine order of winners:

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

791 "rank_v" := 1;

792 "done_v" := 0;

793 LOOP

794 "winners_ary" := '{}';

795 "i" := 1;

796 LOOP

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

798 "j" := 1;

799 LOOP

800 IF

801 "i" != "j" AND

802 "rank_ary"["j"] ISNULL AND

803 "matrix"["j"]["i"] > "matrix"["i"]["j"]

804 THEN

805 -- someone else is better

806 EXIT;

807 END IF;

808 IF "j" = "dimension_v" THEN

809 -- noone is better

810 "winners_ary" := "winners_ary" || "i";

811 EXIT;

812 END IF;

813 "j" := "j" + 1;

814 END LOOP;

815 END IF;

816 EXIT WHEN "i" = "dimension_v";

817 "i" := "i" + 1;

818 END LOOP;

819 "i" := 1;

820 LOOP

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

822 "done_v" := "done_v" + 1;

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

824 "i" := "i" + 1;

825 END LOOP;

826 EXIT WHEN "done_v" = "dimension_v";

827 "rank_v" := "rank_v" + 1;

828 END LOOP;

829 -- write preliminary results:

830 "i" := 1;

831 FOR "initiative_id_v" IN

832 SELECT "id" FROM "initiative"

833 WHERE "issue_id" = "issue_id_p" AND "admitted"

834 ORDER BY "id"

835 LOOP

836 UPDATE "initiative" SET

837 "direct_majority" =

838 CASE WHEN "policy_row"."direct_majority_strict" THEN

839 "positive_votes" * "policy_row"."direct_majority_den" >

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

841 ELSE

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

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

844 END

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

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

847 "policy_row"."direct_majority_non_negative",

848 "indirect_majority" =

849 CASE WHEN "policy_row"."indirect_majority_strict" THEN

850 "positive_votes" * "policy_row"."indirect_majority_den" >

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

852 ELSE

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

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

855 END

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

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

858 "policy_row"."indirect_majority_non_negative",

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

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

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

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

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

864 "winner" = FALSE

865 WHERE "id" = "initiative_id_v";

866 "i" := "i" + 1;

867 END LOOP;

868 IF "i" != "dimension_v" THEN

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

870 END IF;

871 -- take indirect majorities into account:

872 LOOP

873 UPDATE "initiative" SET "indirect_majority" = TRUE

874 FROM (

875 SELECT "new_initiative"."id" AS "initiative_id"

876 FROM "initiative" "old_initiative"

877 JOIN "initiative" "new_initiative"

878 ON "new_initiative"."issue_id" = "issue_id_p"

879 AND "new_initiative"."indirect_majority" = FALSE

880 JOIN "battle" "battle_win"

881 ON "battle_win"."issue_id" = "issue_id_p"

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

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

884 JOIN "battle" "battle_lose"

885 ON "battle_lose"."issue_id" = "issue_id_p"

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

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

888 WHERE "old_initiative"."issue_id" = "issue_id_p"

889 AND "old_initiative"."indirect_majority" = TRUE

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

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

892 "policy_row"."indirect_majority_num" *

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

894 ELSE

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

896 "policy_row"."indirect_majority_num" *

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

898 END

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

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

901 "policy_row"."indirect_majority_non_negative"

902 ) AS "subquery"

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

904 EXIT WHEN NOT FOUND;

905 END LOOP;

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

907 UPDATE "initiative" SET "multistage_majority" = TRUE

908 FROM (

909 SELECT "losing_initiative"."id" AS "initiative_id"

910 FROM "initiative" "losing_initiative"

911 JOIN "initiative" "winning_initiative"

912 ON "winning_initiative"."issue_id" = "issue_id_p"

913 AND "winning_initiative"."admitted"

914 JOIN "battle" "battle_win"

915 ON "battle_win"."issue_id" = "issue_id_p"

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

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

918 JOIN "battle" "battle_lose"

919 ON "battle_lose"."issue_id" = "issue_id_p"

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

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

922 WHERE "losing_initiative"."issue_id" = "issue_id_p"

923 AND "losing_initiative"."admitted"

924 AND "winning_initiative"."schulze_rank" <

925 "losing_initiative"."schulze_rank"

926 AND "battle_win"."count" > "battle_lose"."count"

927 AND (

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

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

930 ) AS "subquery"

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

932 -- mark eligible initiatives:

933 UPDATE "initiative" SET "eligible" = TRUE

934 WHERE "issue_id" = "issue_id_p"

935 AND "initiative"."direct_majority"

936 AND "initiative"."indirect_majority"

937 AND "initiative"."better_than_status_quo"

938 AND (

939 "policy_row"."no_multistage_majority" = FALSE OR

940 "initiative"."multistage_majority" = FALSE )

941 AND (

942 "policy_row"."no_reverse_beat_path" = FALSE OR

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

944 -- mark final winner:

945 UPDATE "initiative" SET "winner" = TRUE

946 FROM (

947 SELECT "id" AS "initiative_id"

948 FROM "initiative"

949 WHERE "issue_id" = "issue_id_p" AND "eligible"

950 ORDER BY "schulze_rank", "id"

951 LIMIT 1

952 ) AS "subquery"

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

954 -- write (final) ranks:

955 "rank_v" := 1;

956 FOR "initiative_id_v" IN

957 SELECT "id"

958 FROM "initiative"

959 WHERE "issue_id" = "issue_id_p" AND "admitted"

960 ORDER BY

961 "winner" DESC,

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

963 "schulze_rank",

964 "id"

965 LOOP

966 UPDATE "initiative" SET "rank" = "rank_v"

967 WHERE "id" = "initiative_id_v";

968 "rank_v" := "rank_v" + 1;

969 END LOOP;

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

971 UPDATE "issue" SET

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

973 "state" =

974 CASE WHEN EXISTS (

975 SELECT NULL FROM "initiative"

976 WHERE "issue_id" = "issue_id_p" AND "winner"

977 ) THEN

978 'finished_with_winner'::"issue_state"

979 ELSE

980 'finished_without_winner'::"issue_state"

981 END,

982 "ranks_available" = TRUE

983 WHERE "id" = "issue_id_p";

984 RETURN;

985 END;

986 $$;

987

988 CREATE OR REPLACE FUNCTION "check_issue"

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

990 RETURNS VOID

991 LANGUAGE 'plpgsql' VOLATILE AS $$

992 DECLARE

993 "issue_row" "issue"%ROWTYPE;

994 "policy_row" "policy"%ROWTYPE;

995 BEGIN

996 PERFORM "lock_issue"("issue_id_p");

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

998 -- only process open issues:

999 IF "issue_row"."closed" ISNULL THEN

1000 SELECT * INTO "policy_row" FROM "policy"

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

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

1003 IF "issue_row"."fully_frozen" ISNULL THEN

1004 PERFORM "create_snapshot"("issue_id_p");

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

1006 END IF;

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

1008 IF "issue_row"."accepted" ISNULL THEN

1009 IF EXISTS (

1010 SELECT NULL FROM "initiative"

1011 WHERE "issue_id" = "issue_id_p"

1012 AND "supporter_count" > 0

1013 AND "supporter_count" * "policy_row"."issue_quorum_den"

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

1015 ) THEN

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

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

1018 -- NOTE: "issue_row" used later

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

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

1021 UPDATE "issue" SET

1022 "state" = "issue_row"."state",

1023 "accepted" = "issue_row"."accepted"

1024 WHERE "id" = "issue_row"."id";

1025 ELSIF

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

1027 THEN

1028 -- close issues, if admission time has expired

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

1030 UPDATE "issue" SET

1031 "state" = 'canceled_issue_not_accepted',

1032 "closed" = now()

1033 WHERE "id" = "issue_row"."id";

1034 END IF;

1035 END IF;

1036 -- eventually half freeze issues:

1037 IF

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

1039 "issue_row"."accepted" NOTNULL AND

1040 "issue_row"."half_frozen" ISNULL

1041 THEN

1042 IF

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

1044 THEN

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

1046 -- NOTE: "issue_row" used later

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

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

1049 UPDATE "issue" SET

1050 "state" = "issue_row"."state",

1051 "half_frozen" = "issue_row"."half_frozen"

1052 WHERE "id" = "issue_row"."id";

1053 END IF;

1054 END IF;

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

1056 IF

1057 "issue_row"."closed" ISNULL AND

1058 NOT EXISTS (

1059 -- all initiatives are revoked

1060 SELECT NULL FROM "initiative"

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

1062 ) AND (

1063 -- and issue has not been accepted yet

1064 "issue_row"."accepted" ISNULL OR

1065 NOT EXISTS (

1066 -- or no initiatives have been revoked lately

1067 SELECT NULL FROM "initiative"

1068 WHERE "issue_id" = "issue_id_p"

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

1070 ) OR (

1071 -- or verification time has elapsed

1072 "issue_row"."half_frozen" NOTNULL AND

1073 "issue_row"."fully_frozen" ISNULL AND

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

1075 )

1076 )

1077 THEN

1078 -- NOTE: "issue_row" used later

1079 IF "issue_row"."accepted" ISNULL THEN

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

1081 ELSIF "issue_row"."half_frozen" ISNULL THEN

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

1083 ELSE

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

1085 END IF;

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

1087 UPDATE "issue" SET

1088 "state" = "issue_row"."state",

1089 "closed" = "issue_row"."closed"

1090 WHERE "id" = "issue_row"."id";

1091 END IF;

1092 -- fully freeze issue after verification time:

1093 IF

1094 "issue_row"."half_frozen" NOTNULL AND

1095 "issue_row"."fully_frozen" ISNULL AND

1096 "issue_row"."closed" ISNULL AND

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

1098 THEN

1099 PERFORM "freeze_after_snapshot"("issue_id_p");

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

1101 END IF;

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

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

1104 IF

1105 "issue_row"."closed" ISNULL AND

1106 "issue_row"."fully_frozen" NOTNULL AND

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

1108 THEN

1109 PERFORM "close_voting"("issue_id_p");

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

1111 PERFORM "calculate_ranks"("issue_id_p");

1112 END IF;

1113 END IF;

1114 RETURN;

1115 END;

1116 $$;

1117

1118 CREATE OR REPLACE FUNCTION "check_everything"()

1119 RETURNS VOID

1120 LANGUAGE 'plpgsql' VOLATILE AS $$

1121 DECLARE

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

1123 BEGIN

1124 PERFORM "check_activity"();

1125 PERFORM "calculate_member_counts"();

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

1127 PERFORM "check_issue"("issue_id_v");

1128 END LOOP;

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

1130 PERFORM "calculate_ranks"("issue_id_v");

1131 END LOOP;

1132 RETURN;

1133 END;

1134 $$;

1135

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

1137 RETURNS VOID

1138 LANGUAGE 'plpgsql' VOLATILE AS $$

1139 BEGIN

1140 UPDATE "member" SET

1141 "last_login" = NULL,

1142 "login" = NULL,

1143 "password" = NULL,

1144 "locked" = TRUE,

1145 "active" = FALSE,

1146 "notify_email" = NULL,

1147 "notify_email_unconfirmed" = NULL,

1148 "notify_email_secret" = NULL,

1149 "notify_email_secret_expiry" = NULL,

1150 "notify_email_lock_expiry" = NULL,

1151 "password_reset_secret" = NULL,

1152 "password_reset_secret_expiry" = NULL,

1153 "organizational_unit" = NULL,

1154 "internal_posts" = NULL,

1155 "realname" = NULL,

1156 "birthday" = NULL,

1157 "address" = NULL,

1158 "email" = NULL,

1159 "xmpp_address" = NULL,

1160 "website" = NULL,

1161 "phone" = NULL,

1162 "mobile_phone" = NULL,

1163 "profession" = NULL,

1164 "external_memberships" = NULL,

1165 "external_posts" = NULL,

1166 "statement" = NULL

1167 WHERE "id" = "member_id_p";

1168 -- "text_search_data" is updated by triggers

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

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

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

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

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

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

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

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

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

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

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

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

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

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

1183 DELETE FROM "direct_voter" USING "issue"

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

1185 AND "issue"."closed" ISNULL

1186 AND "member_id" = "member_id_p";

1187 RETURN;

1188 END;

1189 $$;

1190

1191 CREATE OR REPLACE FUNCTION "delete_private_data"()

1192 RETURNS VOID

1193 LANGUAGE 'plpgsql' VOLATILE AS $$

1194 BEGIN

1195 UPDATE "member" SET

1196 "last_login" = NULL,

1197 "login" = NULL,

1198 "password" = NULL,

1199 "notify_email" = NULL,

1200 "notify_email_unconfirmed" = NULL,

1201 "notify_email_secret" = NULL,

1202 "notify_email_secret_expiry" = NULL,

1203 "notify_email_lock_expiry" = NULL,

1204 "password_reset_secret" = NULL,

1205 "password_reset_secret_expiry" = NULL,

1206 "organizational_unit" = NULL,

1207 "internal_posts" = NULL,

1208 "realname" = NULL,

1209 "birthday" = NULL,

1210 "address" = NULL,

1211 "email" = NULL,

1212 "xmpp_address" = NULL,

1213 "website" = NULL,

1214 "phone" = NULL,

1215 "mobile_phone" = NULL,

1216 "profession" = NULL,

1217 "external_memberships" = NULL,

1218 "external_posts" = NULL,

1219 "statement" = NULL;

1220 -- "text_search_data" is updated by triggers

1221 DELETE FROM "invite_code";

1222 DELETE FROM "setting";

1223 DELETE FROM "setting_map";

1224 DELETE FROM "member_relation_setting";

1225 DELETE FROM "member_image";

1226 DELETE FROM "contact";

1227 DELETE FROM "ignored_member";

1228 DELETE FROM "area_setting";

1229 DELETE FROM "issue_setting";

1230 DELETE FROM "ignored_initiative";

1231 DELETE FROM "initiative_setting";

1232 DELETE FROM "suggestion_setting";

1233 DELETE FROM "non_voter";

1234 DELETE FROM "direct_voter" USING "issue"

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

1236 AND "issue"."closed" ISNULL;

1237 RETURN;

1238 END;

1239 $$;

1240

1241 COMMIT;

1242

1243 BEGIN;

1244

1245 UPDATE "member" SET

1246 "activated" = "created",

1247 "last_activity" = CASE WHEN "active" THEN

1248 coalesce("last_login"::DATE, now())

1249 ELSE

1250 "last_login"::DATE

1251 END;

1252

1253 UPDATE "member" SET

1254 "created" = "invite_code"."created",

1255 "invite_code" = "invite_code"."code",

1256 "admin_comment" = "invite_code"."comment"

1257 FROM "invite_code"

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

1259

1260 INSERT INTO "member" ("created", "invite_code", "admin_comment")

1261 SELECT "created", "code", "comment"

1262 FROM "invite_code" WHERE "member_id" ISNULL;

1263

1264 INSERT INTO "privilege" ("unit_id", "member_id", "voting_right")

1265 SELECT 1 AS "unit_id", "id" AS "member_id", TRUE AS "voting_right"

1266 FROM "member" WHERE "activated" ISNULL;

1267

1268 DROP TABLE "invite_code";

1269

1270 UPDATE "initiative" SET

1271 "direct_majority" = "rank" NOTNULL,

1272 "indirect_majority" = "rank" NOTNULL,

1273 "schulze_rank" = "rank",

1274 "better_than_status_quo" = "rank" NOTNULL,

1275 "worse_than_status_quo" = "rank" ISNULL,

1276 "reverse_beat_path" = "rank" ISNULL,

1277 "multistage_majority" = "rank" ISNULL,

1278 "eligible" = "rank" NOTNULL,

1279 "winner" = ("rank" = 1)

1280 FROM "issue"

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

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

1283 AND "initiative"."admitted";

1284

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

1286 FROM (

1287 SELECT

1288 "issue"."id" AS "issue_id",

1289 COALESCE(max("initiative"."rank") + 1, 1) AS "rank"

1290 FROM "issue" JOIN "initiative"

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

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

1293 AND "initiative"."admitted"

1294 GROUP BY "issue"."id"

1295 ) AS "subquery"

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

1297

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

1299 RETURNS VOID

1300 LANGUAGE 'plpgsql' AS $$

1301 DECLARE

1302 "rank_v" INT4;

1303 "initiative_id_v" INT4;

1304 BEGIN

1305 SELECT "status_quo_schulze_rank" INTO "rank_v"

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

1307 FOR "initiative_id_v" IN

1308 SELECT "id" FROM "initiative"

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

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

1311 LOOP

1312 UPDATE "initiative" SET

1313 "schulze_rank" = "rank_v" + 1,

1314 "rank" = "rank_v"

1315 WHERE "id" = "initiative_id_v";

1316 "rank_v" := "rank_v" + 1;

1317 END LOOP;

1318 RETURN;

1319 END;

1320 $$;

1321

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

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

1324

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

1326

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

1328 FROM (

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

1330 "suggestion"."id" AS "suggestion_id",

1331 "draft"."id" AS "draft_id"

1332 FROM "suggestion" JOIN "draft"

1333 ON "suggestion"."initiative_id" = "draft"."initiative_id"

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

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

1336 ) AS "subquery"

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

1338

1339 COMMIT;

1340

1341 ALTER TABLE "member" ADD CONSTRAINT "active_requires_activated_and_last_activity"

1342 CHECK ("active" = FALSE OR ("activated" NOTNULL AND "last_activity" NOTNULL));

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