liquid_feedback_core: 8a1a488a1a67 update/core-update.v1.3.1-v1.4.0

liquid_feedback_core

view update/core-update.v1.3.1-v1.4.0_rc1.sql @ 121:8a1a488a1a67

Bugfix in function "freeze_after_snapshot"; Simplified function "close_voting"

Constraint "valid_state" on table "issue" requires, that "ranks_available" is set to true, if state is set to 'canceled_no_initiative_admitted'.
Function "close_voting" assumes that there was at least one initiative to be voted on (ensured by "freeze_after_snapshot").

author	jbe
date	Mon Mar 07 19:15:14 2011 +0100 (2011-03-07)
parents	7d6c5032262f
children	2a1f0bd5fcff

line source

1 BEGIN; -- NOTE: file contains additional statements AFTER this BEGIN/COMMIT block!

4 -- Update version information:

6 CREATE OR REPLACE VIEW "liquid_feedback_version" AS

7 SELECT * FROM (VALUES ('1.4.0_rc1', 1, 4, -1))

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

11 -- New columns "notify_level" and "notify_event_id" in "member" table:

13 CREATE TYPE "notify_level" AS ENUM

14 ('none', 'voting', 'verification', 'discussion', 'all');

16 COMMENT ON TYPE "notify_level" IS 'Level of notification: ''none'' = no notifications, ''voting'' = notifications about finished issues and issues in voting, ''verification'' = notifications about finished issues, issues in voting and verification phase, ''discussion'' = notifications about everything except issues in admission phase, ''all'' = notifications about everything';

18 ALTER TABLE "member" ADD "notify_level" "notify_level" NOT NULL DEFAULT 'none';

19 ALTER TABLE "member" ADD "notify_event_id" INT8;

21 COMMENT ON COLUMN "member"."notify_level" IS 'Selects which event notifications are to be sent to the "notify_email" mail address';

22 COMMENT ON COLUMN "member"."notify_event_id" IS 'Latest "id" of an "event" the member was notified about';

25 -- Add primary key with type SERIAL8 (INT8) for "invite_code" table:

27 ALTER TABLE "invite_code" DROP CONSTRAINT "invite_code_pkey";

28 ALTER TABLE "invite_code" ALTER "code" SET NOT NULL;

29 ALTER TABLE "invite_code" ADD UNIQUE ("code");

30 ALTER TABLE "invite_code" ADD "id" SERIAL8 PRIMARY KEY;

33 -- Add index for "other_member_id" column of "contact" table:

35 CREATE INDEX "contact_other_member_id_idx" ON "contact" ("other_member_id");

38 -- New table "ignored_member":

40 CREATE TABLE "ignored_member" (

41 PRIMARY KEY ("member_id", "other_member_id"),

42 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,

43 "other_member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );

44 CREATE INDEX "ignored_member_other_member_id_idx" ON "ignored_member" ("other_member_id");

46 COMMENT ON TABLE "ignored_member" IS 'Possibility to filter other members';

48 COMMENT ON COLUMN "ignored_member"."member_id" IS 'Member ignoring someone';

49 COMMENT ON COLUMN "ignored_member"."other_member_id" IS 'Member being ignored';

52 -- New table "unit" with default entry:

54 CREATE TABLE "unit" (

55 "id" SERIAL4 PRIMARY KEY,

56 "parent_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,

57 "active" BOOLEAN NOT NULL DEFAULT TRUE,

58 "name" TEXT NOT NULL,

59 "description" TEXT NOT NULL DEFAULT '',

60 "member_count" INT4,

61 "text_search_data" TSVECTOR );

62 CREATE INDEX "unit_root_idx" ON "unit" ("id") WHERE "parent_id" ISNULL;

63 CREATE INDEX "unit_parent_id_idx" ON "unit" ("parent_id");

64 CREATE INDEX "unit_active_idx" ON "unit" ("active");

65 CREATE INDEX "unit_text_search_data_idx" ON "unit" USING gin ("text_search_data");

66 CREATE TRIGGER "update_text_search_data"

67 BEFORE INSERT OR UPDATE ON "unit"

68 FOR EACH ROW EXECUTE PROCEDURE

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

70 "name", "description" );

72 COMMENT ON TABLE "unit" IS 'Organizational units organized as trees; Delegations are not inherited through these trees.';

74 COMMENT ON COLUMN "unit"."parent_id" IS 'Parent id of tree node; Multiple roots allowed';

75 COMMENT ON COLUMN "unit"."active" IS 'TRUE means new issues can be created in units of this area';

76 COMMENT ON COLUMN "unit"."member_count" IS 'Count of members as determined by column "voting_right" in table "privilege"';

78 INSERT INTO "unit" ("name") VALUES ('Main'); -- NOTE: gets id 1

81 -- New column "unit_id" in table "area":

83 ALTER TABLE "area" ADD "unit_id" INT4 DEFAULT 1

84 NOT NULL REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE;

85 ALTER TABLE "area" ALTER "unit_id" DROP DEFAULT;

87 CREATE INDEX "area_unit_id_idx" ON "area" ("unit_id");

90 -- Issue states:

92 CREATE TYPE "issue_state" AS ENUM (

93 'admission', 'discussion', 'verification', 'voting',

94 'canceled_revoked_before_accepted',

95 'canceled_issue_not_accepted',

96 'canceled_after_revocation_during_discussion',

97 'canceled_after_revocation_during_verification',

98 'calculation',

99 'canceled_no_initiative_admitted',

100 'finished_without_winner', 'finished_with_winner');

101

102 COMMENT ON TYPE "issue_state" IS 'State of issues';

103

104 ALTER TABLE "issue" ADD "state" "issue_state";

105

106 -- NOTE: Filling new column with values is done after this transaction (see below)

107

108

109 -- New column "revoked_by_member_id" in table "initiative":

110

111 ALTER TABLE "initiative" ADD "revoked_by_member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE;

112

113 COMMENT ON COLUMN "initiative"."revoked_by_member_id" IS 'Member, who decided to revoked the initiative';

114

115 -- NOTE: Filling new column with values is done after this transaction (see below)

116

117

118 -- New table "ignored_initiative":

119

120 CREATE TABLE "ignored_initiative" (

121 PRIMARY KEY ("initiative_id", "member_id"),

122 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,

123 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );

124 CREATE INDEX "ignored_initiative_member_id_idx" ON "ignored_initiative" ("member_id");

125

126 COMMENT ON TABLE "ignored_initiative" IS 'Possibility to filter initiatives';

127

128

129 -- New table "invite_code_unit":

130

131 CREATE TABLE "invite_code_unit" (

132 PRIMARY KEY ("invite_code_id", "unit_id"),

133 "invite_code_id" INT8 REFERENCES "invite_code" ("id") ON DELETE CASCADE ON UPDATE CASCADE,

134 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE );

135

136 COMMENT ON TABLE "invite_code_unit" IS 'Units where accounts created with a given invite codes get voting rights';

137

138 INSERT INTO "invite_code_unit" ("invite_code_id", "unit_id")

139 SELECT "id" AS "invite_code_id", 1 AS "unit_id"

140 FROM "invite_code" WHERE "used" ISNULL;

141

142

143 -- New table "privilege":

144

145 CREATE TABLE "privilege" (

146 PRIMARY KEY ("unit_id", "member_id"),

147 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,

148 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,

149 "admin_manager" BOOLEAN NOT NULL DEFAULT FALSE,

150 "unit_manager" BOOLEAN NOT NULL DEFAULT FALSE,

151 "area_manager" BOOLEAN NOT NULL DEFAULT FALSE,

152 "voting_right_manager" BOOLEAN NOT NULL DEFAULT FALSE,

153 "voting_right" BOOLEAN NOT NULL DEFAULT TRUE );

154

155 COMMENT ON TABLE "privilege" IS 'Members rights related to each unit';

156

157 COMMENT ON COLUMN "privilege"."admin_manager" IS 'Grant/revoke admin privileges to/from other users';

158 COMMENT ON COLUMN "privilege"."unit_manager" IS 'Create or lock sub units';

159 COMMENT ON COLUMN "privilege"."area_manager" IS 'Create or lock areas and set area parameters';

160 COMMENT ON COLUMN "privilege"."voting_right_manager" IS 'Select which members are allowed to discuss and vote inside the unit';

161 COMMENT ON COLUMN "privilege"."voting_right" IS 'Right to discuss and vote';

162

163

164 -- Remove table "ignored_issue", which is no longer existent:

165

166 DROP TABLE "ignored_issue";

167

168

169 -- Replace TYPE "delegation_scope" with a new type, where 'global' is replaced by 'unit':

170

171 ALTER TYPE "delegation_scope" RENAME TO "delegation_scope_old"; -- NOTE: dropped later

172 CREATE TYPE "delegation_scope" AS ENUM ('unit', 'area', 'issue');

173 COMMENT ON TYPE "delegation_scope" IS 'Scope for delegations: ''unit'', ''area'', or ''issue'' (order is relevant)';

174

175

176 -- Delete views and functions being dependent on TYPE "delegation_scope":

177

178 DROP FUNCTION "delegation_chain"

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

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

181 "issue_id_p" "issue"."id"%TYPE );

182

183 DROP FUNCTION "delegation_chain"

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

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

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

187 "simulate_trustee_id_p" "member"."id"%TYPE );

188

189 DROP TYPE "delegation_chain_row";

190

191 DROP VIEW "issue_delegation";

192 DROP VIEW "area_delegation";

193 DROP VIEW "global_delegation";

194 DROP VIEW "active_delegation";

195

196

197 -- Modify "delegation" table to use new "delegation_scope" TYPE:

198

199 ALTER TABLE "delegation" DROP CONSTRAINT "no_global_delegation_to_null";

200 ALTER TABLE "delegation" DROP CONSTRAINT "area_id_and_issue_id_set_according_to_scope";

201

202 DROP INDEX "delegation_global_truster_id_unique_idx";

203

204 ALTER TABLE "delegation" ALTER "scope" TYPE "delegation_scope"

205 USING CASE WHEN "scope" = 'global'

206 THEN 'unit'::"delegation_scope"

207 ELSE "scope"::text::"delegation_scope" END;

208

209 ALTER TABLE "delegation" ADD "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE;

210

211 ALTER TABLE "delegation" ADD CONSTRAINT "no_unit_delegation_to_null"

212 CHECK ("trustee_id" NOTNULL OR "scope" != 'unit');

213

214 ALTER TABLE "delegation" ADD UNIQUE ("unit_id", "truster_id");

215

216 COMMENT ON COLUMN "delegation"."unit_id" IS 'Reference to unit, if delegation is unit-wide, otherwise NULL';

217

218 -- NOTE: Column "unit_id" filled after transaction (see below)

219

220

221 -- Modify snapshot tables to use new "delegation_scope" TYPE:

222

223 ALTER TABLE "delegating_population_snapshot" ALTER "scope" TYPE "delegation_scope"

224 USING CASE WHEN "scope" = 'global'

225 THEN 'unit'::"delegation_scope"

226 ELSE "scope"::text::"delegation_scope" END;

227

228 ALTER TABLE "delegating_interest_snapshot" ALTER "scope" TYPE "delegation_scope"

229 USING CASE WHEN "scope" = 'global'

230 THEN 'unit'::"delegation_scope"

231 ELSE "scope"::text::"delegation_scope" END;

232

233 ALTER TABLE "delegating_voter" ALTER "scope" TYPE "delegation_scope"

234 USING CASE WHEN "scope" = 'global'

235 THEN 'unit'::"delegation_scope"

236 ELSE "scope"::text::"delegation_scope" END;

237

238

239 -- New table "non_voter":

240

241 CREATE TABLE "non_voter" (

242 PRIMARY KEY ("issue_id", "member_id"),

243 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,

244 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );

245 CREATE INDEX "non_voter_member_id_idx" ON "non_voter" ("member_id");

246

247 COMMENT ON TABLE "non_voter" IS 'Members who decided to not vote directly on an issue';

248

249

250 -- New table "issue_comment":

251

252 CREATE TABLE "issue_comment" (

253 PRIMARY KEY ("issue_id", "member_id"),

254 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,

255 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,

256 "changed" TIMESTAMPTZ NOT NULL DEFAULT now(),

257 "formatting_engine" TEXT,

258 "content" TEXT NOT NULL,

259 "text_search_data" TSVECTOR );

260 CREATE INDEX "issue_comment_member_id_idx" ON "issue_comment" ("member_id");

261 CREATE INDEX "issue_comment_text_search_data_idx" ON "issue_comment" USING gin ("text_search_data");

262 CREATE TRIGGER "update_text_search_data"

263 BEFORE INSERT OR UPDATE ON "issue_comment"

264 FOR EACH ROW EXECUTE PROCEDURE

265 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "content");

266

267 COMMENT ON TABLE "issue_comment" IS 'Place to store free comments of members related to issues';

268

269 COMMENT ON COLUMN "issue_comment"."changed" IS 'Time the comment was last changed';

270

271

272 -- New table "rendered_issue_comment":

273

274 CREATE TABLE "rendered_issue_comment" (

275 PRIMARY KEY ("issue_id", "member_id", "format"),

276 FOREIGN KEY ("issue_id", "member_id")

277 REFERENCES "issue_comment" ("issue_id", "member_id")

278 ON DELETE CASCADE ON UPDATE CASCADE,

279 "issue_id" INT4,

280 "member_id" INT4,

281 "format" TEXT,

282 "content" TEXT NOT NULL );

283

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

285

286

287 -- New table "voting_comment":

288

289 CREATE TABLE "voting_comment" (

290 PRIMARY KEY ("issue_id", "member_id"),

291 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,

292 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,

293 "changed" TIMESTAMPTZ,

294 "formatting_engine" TEXT,

295 "content" TEXT NOT NULL,

296 "text_search_data" TSVECTOR );

297 CREATE INDEX "voting_comment_member_id_idx" ON "voting_comment" ("member_id");

298 CREATE INDEX "voting_comment_text_search_data_idx" ON "voting_comment" USING gin ("text_search_data");

299 CREATE TRIGGER "update_text_search_data"

300 BEFORE INSERT OR UPDATE ON "voting_comment"

301 FOR EACH ROW EXECUTE PROCEDURE

302 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "content");

303

304 COMMENT ON TABLE "voting_comment" IS 'Storage for comments of voters to be published after voting has finished.';

305

306 COMMENT ON COLUMN "voting_comment"."changed" IS 'Is to be set or updated by the frontend, if comment was inserted or updated AFTER the issue has been closed. Otherwise it shall be set to NULL.';

307

308

309 -- New table "rendered_voting_comment":

310

311 CREATE TABLE "rendered_voting_comment" (

312 PRIMARY KEY ("issue_id", "member_id", "format"),

313 FOREIGN KEY ("issue_id", "member_id")

314 REFERENCES "voting_comment" ("issue_id", "member_id")

315 ON DELETE CASCADE ON UPDATE CASCADE,

316 "issue_id" INT4,

317 "member_id" INT4,

318 "format" TEXT,

319 "content" TEXT NOT NULL );

320

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

322

323

324 -- New table "event":

325

326 CREATE TYPE "event_type" AS ENUM (

327 'issue_state_changed',

328 'initiative_created_in_new_issue',

329 'initiative_created_in_existing_issue',

330 'initiative_revoked',

331 'new_draft_created',

332 'suggestion_created');

333

334 COMMENT ON TYPE "event_type" IS 'Type used for column "event" of table "event"';

335

336 CREATE TABLE "event" (

337 "id" SERIAL8 PRIMARY KEY,

338 "occurrence" TIMESTAMPTZ NOT NULL DEFAULT now(),

339 "event" "event_type" NOT NULL,

340 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,

341 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,

342 "state" "issue_state" CHECK ("state" != 'calculation'),

343 "initiative_id" INT4,

344 "draft_id" INT8,

345 "suggestion_id" INT8,

346 FOREIGN KEY ("issue_id", "initiative_id")

347 REFERENCES "initiative" ("issue_id", "id")

348 ON DELETE CASCADE ON UPDATE CASCADE,

349 FOREIGN KEY ("initiative_id", "draft_id")

350 REFERENCES "draft" ("initiative_id", "id")

351 ON DELETE CASCADE ON UPDATE CASCADE,

352 FOREIGN KEY ("initiative_id", "suggestion_id")

353 REFERENCES "suggestion" ("initiative_id", "id")

354 ON DELETE CASCADE ON UPDATE CASCADE,

355 CONSTRAINT "null_constraints_for_issue_state_changed" CHECK (

356 "event" != 'issue_state_changed' OR (

357 "member_id" ISNULL AND

358 "issue_id" NOTNULL AND

359 "state" NOTNULL AND

360 "initiative_id" ISNULL AND

361 "draft_id" ISNULL AND

362 "suggestion_id" ISNULL )),

363 CONSTRAINT "null_constraints_for_initiative_creation_or_revocation_or_new_draft" CHECK (

364 "event" NOT IN (

365 'initiative_created_in_new_issue',

366 'initiative_created_in_existing_issue',

367 'initiative_revoked',

368 'new_draft_created'

369 ) OR (

370 "member_id" NOTNULL AND

371 "issue_id" NOTNULL AND

372 "state" NOTNULL AND

373 "initiative_id" NOTNULL AND

374 "draft_id" NOTNULL AND

375 "suggestion_id" ISNULL )),

376 CONSTRAINT "null_constraints_for_suggestion_creation" CHECK (

377 "event" != 'suggestion_created' OR (

378 "member_id" NOTNULL AND

379 "issue_id" NOTNULL AND

380 "state" NOTNULL AND

381 "initiative_id" NOTNULL AND

382 "draft_id" ISNULL AND

383 "suggestion_id" NOTNULL )) );

384

385 COMMENT ON TABLE "event" IS 'Event table, automatically filled by triggers';

386

387 COMMENT ON COLUMN "event"."occurrence" IS 'Point in time, when event occurred';

388 COMMENT ON COLUMN "event"."event" IS 'Type of event (see TYPE "event_type")';

389 COMMENT ON COLUMN "event"."member_id" IS 'Member who caused the event, if applicable';

390 COMMENT ON COLUMN "event"."state" IS 'If issue_id is set: state of affected issue; If state changed: new state';

391

392

393 -- Triggers to fill "event" table:

394

395 CREATE FUNCTION "write_event_issue_state_changed_trigger"()

396 RETURNS TRIGGER

397 LANGUAGE 'plpgsql' VOLATILE AS $$

398 BEGIN

399 IF NEW."state" != OLD."state" AND NEW."state" != 'calculation' THEN

400 INSERT INTO "event" ("event", "issue_id", "state")

401 VALUES ('issue_state_changed', NEW."id", NEW."state");

402 END IF;

403 RETURN NULL;

404 END;

405 $$;

406

407 CREATE TRIGGER "write_event_issue_state_changed"

408 AFTER UPDATE ON "issue" FOR EACH ROW EXECUTE PROCEDURE

409 "write_event_issue_state_changed_trigger"();

410

411 COMMENT ON FUNCTION "write_event_issue_state_changed_trigger"() IS 'Implementation of trigger "write_event_issue_state_changed" on table "issue"';

412 COMMENT ON TRIGGER "write_event_issue_state_changed" ON "issue" IS 'Create entry in "event" table on "state" change';

413

414 CREATE FUNCTION "write_event_initiative_or_draft_created_trigger"()

415 RETURNS TRIGGER

416 LANGUAGE 'plpgsql' VOLATILE AS $$

417 DECLARE

418 "initiative_row" "initiative"%ROWTYPE;

419 "issue_row" "issue"%ROWTYPE;

420 "event_v" "event_type";

421 BEGIN

422 SELECT * INTO "initiative_row" FROM "initiative"

423 WHERE "id" = NEW."initiative_id";

424 SELECT * INTO "issue_row" FROM "issue"

425 WHERE "id" = "initiative_row"."issue_id";

426 IF EXISTS (

427 SELECT NULL FROM "draft"

428 WHERE "initiative_id" = NEW."initiative_id"

429 AND "id" != NEW."id"

430 ) THEN

431 "event_v" := 'new_draft_created';

432 ELSE

433 IF EXISTS (

434 SELECT NULL FROM "initiative"

435 WHERE "issue_id" = "initiative_row"."issue_id"

436 AND "id" != "initiative_row"."id"

437 ) THEN

438 "event_v" := 'initiative_created_in_existing_issue';

439 ELSE

440 "event_v" := 'initiative_created_in_new_issue';

441 END IF;

442 END IF;

443 INSERT INTO "event" (

444 "event", "member_id",

445 "issue_id", "state", "initiative_id", "draft_id"

446 ) VALUES (

447 "event_v",

448 NEW."author_id",

449 "initiative_row"."issue_id",

450 "issue_row"."state",

451 "initiative_row"."id",

452 NEW."id" );

453 RETURN NULL;

454 END;

455 $$;

456

457 CREATE TRIGGER "write_event_initiative_or_draft_created"

458 AFTER INSERT ON "draft" FOR EACH ROW EXECUTE PROCEDURE

459 "write_event_initiative_or_draft_created_trigger"();

460

461 COMMENT ON FUNCTION "write_event_initiative_or_draft_created_trigger"() IS 'Implementation of trigger "write_event_initiative_or_draft_created" on table "issue"';

462 COMMENT ON TRIGGER "write_event_initiative_or_draft_created" ON "draft" IS 'Create entry in "event" table on draft creation';

463

464 CREATE FUNCTION "write_event_initiative_revoked_trigger"()

465 RETURNS TRIGGER

466 LANGUAGE 'plpgsql' VOLATILE AS $$

467 DECLARE

468 "issue_row" "issue"%ROWTYPE;

469 BEGIN

470 SELECT * INTO "issue_row" FROM "issue"

471 WHERE "id" = NEW."issue_id";

472 IF OLD."revoked" ISNULL AND NEW."revoked" NOTNULL THEN

473 INSERT INTO "event" (

474 "event", "member_id", "issue_id", "state", "initiative_id"

475 ) VALUES (

476 'initiative_revoked',

477 NEW."revoked_by_member_id",

478 NEW."issue_id",

479 "issue_row"."state",

480 NEW."id" );

481 END IF;

482 RETURN NULL;

483 END;

484 $$;

485

486 CREATE TRIGGER "write_event_initiative_revoked"

487 AFTER UPDATE ON "initiative" FOR EACH ROW EXECUTE PROCEDURE

488 "write_event_initiative_revoked_trigger"();

489

490 COMMENT ON FUNCTION "write_event_initiative_revoked_trigger"() IS 'Implementation of trigger "write_event_initiative_revoked" on table "issue"';

491 COMMENT ON TRIGGER "write_event_initiative_revoked" ON "initiative" IS 'Create entry in "event" table, when an initiative is revoked';

492

493 CREATE FUNCTION "write_event_suggestion_created_trigger"()

494 RETURNS TRIGGER

495 LANGUAGE 'plpgsql' VOLATILE AS $$

496 DECLARE

497 "initiative_row" "initiative"%ROWTYPE;

498 "issue_row" "issue"%ROWTYPE;

499 BEGIN

500 SELECT * INTO "initiative_row" FROM "initiative"

501 WHERE "id" = NEW."initiative_id";

502 SELECT * INTO "issue_row" FROM "issue"

503 WHERE "id" = "initiative_row"."issue_id";

504 INSERT INTO "event" (

505 "event", "member_id",

506 "issue_id", "state", "initiative_id", "suggestion_id"

507 ) VALUES (

508 'suggestion_created',

509 NEW."author_id",

510 "initiative_row"."issue_id",

511 "issue_row"."state",

512 "initiative_row"."id",

513 NEW."id" );

514 RETURN NULL;

515 END;

516 $$;

517

518 CREATE TRIGGER "write_event_suggestion_created"

519 AFTER INSERT ON "suggestion" FOR EACH ROW EXECUTE PROCEDURE

520 "write_event_suggestion_created_trigger"();

521

522 COMMENT ON FUNCTION "write_event_suggestion_created_trigger"() IS 'Implementation of trigger "write_event_suggestion_created" on table "issue"';

523 COMMENT ON TRIGGER "write_event_suggestion_created" ON "suggestion" IS 'Create entry in "event" table on suggestion creation';

524

525

526 -- Modified views:

527

528 CREATE VIEW "unit_delegation" AS

529 SELECT

530 "unit"."id" AS "unit_id",

531 "delegation"."id",

532 "delegation"."truster_id",

533 "delegation"."trustee_id",

534 "delegation"."scope"

535 FROM "unit"

536 JOIN "delegation"

537 ON "delegation"."unit_id" = "unit"."id"

538 JOIN "member"

539 ON "delegation"."truster_id" = "member"."id"

540 JOIN "privilege"

541 ON "delegation"."unit_id" = "privilege"."unit_id"

542 AND "delegation"."truster_id" = "privilege"."member_id"

543 WHERE "member"."active" AND "privilege"."voting_right";

544

545 COMMENT ON VIEW "unit_delegation" IS 'Unit delegations where trusters are active and have voting right';

546

547 CREATE VIEW "area_delegation" AS

548 SELECT DISTINCT ON ("area"."id", "delegation"."truster_id")

549 "area"."id" AS "area_id",

550 "delegation"."id",

551 "delegation"."truster_id",

552 "delegation"."trustee_id",

553 "delegation"."scope"

554 FROM "area"

555 JOIN "delegation"

556 ON "delegation"."unit_id" = "area"."unit_id"

557 OR "delegation"."area_id" = "area"."id"

558 JOIN "member"

559 ON "delegation"."truster_id" = "member"."id"

560 JOIN "privilege"

561 ON "area"."unit_id" = "privilege"."unit_id"

562 AND "delegation"."truster_id" = "privilege"."member_id"

563 WHERE "member"."active" AND "privilege"."voting_right"

564 ORDER BY

565 "area"."id",

566 "delegation"."truster_id",

567 "delegation"."scope" DESC;

568

569 COMMENT ON VIEW "area_delegation" IS 'Area delegations where trusters are active and have voting right';

570

571 CREATE VIEW "issue_delegation" AS

572 SELECT DISTINCT ON ("issue"."id", "delegation"."truster_id")

573 "issue"."id" AS "issue_id",

574 "delegation"."id",

575 "delegation"."truster_id",

576 "delegation"."trustee_id",

577 "delegation"."scope"

578 FROM "issue"

579 JOIN "area"

580 ON "area"."id" = "issue"."area_id"

581 JOIN "delegation"

582 ON "delegation"."unit_id" = "area"."unit_id"

583 OR "delegation"."area_id" = "area"."id"

584 OR "delegation"."issue_id" = "issue"."id"

585 JOIN "member"

586 ON "delegation"."truster_id" = "member"."id"

587 JOIN "privilege"

588 ON "area"."unit_id" = "privilege"."unit_id"

589 AND "delegation"."truster_id" = "privilege"."member_id"

590 WHERE "member"."active" AND "privilege"."voting_right"

591 ORDER BY

592 "issue"."id",

593 "delegation"."truster_id",

594 "delegation"."scope" DESC;

595

596 COMMENT ON VIEW "issue_delegation" IS 'Issue delegations where trusters are active and have voting right';

597

598 CREATE VIEW "unit_member_count" AS

599 SELECT

600 "unit"."id" AS "unit_id",

601 sum("member"."id") AS "member_count"

602 FROM "unit"

603 LEFT JOIN "privilege"

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

605 AND "privilege"."voting_right"

606 LEFT JOIN "member"

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

608 AND "member"."active"

609 GROUP BY "unit"."id";

610

611 COMMENT ON VIEW "unit_member_count" IS 'View used to update "member_count" column of "unit" table';

612

613 DROP VIEW "area_member_count";

614 CREATE VIEW "area_member_count" AS

615 SELECT

616 "area"."id" AS "area_id",

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

618 coalesce(

619 sum(

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

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

622 ELSE 0 END

623 )

624 ) AS "member_weight",

625 coalesce(

626 sum(

627 CASE WHEN "member"."id" NOTNULL AND "membership"."autoreject" THEN

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

629 ELSE 0 END

630 )

631 ) AS "autoreject_weight"

632 FROM "area"

633 LEFT JOIN "membership"

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

635 LEFT JOIN "privilege"

636 ON "privilege"."unit_id" = "area"."unit_id"

637 AND "privilege"."member_id" = "membership"."member_id"

638 AND "privilege"."voting_right"

639 LEFT JOIN "member"

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

641 AND "member"."active"

642 GROUP BY "area"."id";

643

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

645

646

647 -- New view "event_seen_by_member":

648

649 CREATE VIEW "event_seen_by_member" AS

650 SELECT

651 "member"."id" AS "seen_by_member_id",

652 CASE WHEN "event"."state" IN (

653 'voting',

654 'finished_without_winner',

655 'finished_with_winner'

656 ) THEN

657 'voting'::"notify_level"

658 ELSE

659 CASE WHEN "event"."state" IN (

660 'verification',

661 'canceled_after_revocation_during_verification',

662 'canceled_no_initiative_admitted'

663 ) THEN

664 'verification'::"notify_level"

665 ELSE

666 CASE WHEN "event"."state" IN (

667 'discussion',

668 'canceled_after_revocation_during_discussion'

669 ) THEN

670 'discussion'::"notify_level"

671 ELSE

672 'all'::"notify_level"

673 END

674 END

675 END AS "notify_level",

676 "event".*

677 FROM "member" CROSS JOIN "event"

678 LEFT JOIN "issue"

679 ON "event"."issue_id" = "issue"."id"

680 LEFT JOIN "membership"

681 ON "member"."id" = "membership"."member_id"

682 AND "issue"."area_id" = "membership"."area_id"

683 LEFT JOIN "interest"

684 ON "member"."id" = "interest"."member_id"

685 AND "event"."issue_id" = "interest"."issue_id"

686 LEFT JOIN "supporter"

687 ON "member"."id" = "supporter"."member_id"

688 AND "event"."initiative_id" = "supporter"."initiative_id"

689 LEFT JOIN "ignored_member"

690 ON "member"."id" = "ignored_member"."member_id"

691 AND "event"."member_id" = "ignored_member"."other_member_id"

692 LEFT JOIN "ignored_initiative"

693 ON "member"."id" = "ignored_initiative"."member_id"

694 AND "event"."initiative_id" = "ignored_initiative"."initiative_id"

695 WHERE (

696 "supporter"."member_id" NOTNULL OR

697 "interest"."member_id" NOTNULL OR

698 ( "membership"."member_id" NOTNULL AND

699 "event"."event" IN (

700 'issue_state_changed',

701 'initiative_created_in_new_issue',

702 'initiative_created_in_existing_issue',

703 'initiative_revoked' ) ) )

704 AND "ignored_member"."member_id" ISNULL

705 AND "ignored_initiative"."member_id" ISNULL;

706

707 COMMENT ON VIEW "event_seen_by_member" IS 'Events as seen by a member, depending on its memberships, interests and support';

708

709

710 -- New view "pending_notification":

711

712 CREATE VIEW "pending_notification" AS

713 SELECT

714 "member"."id" AS "seen_by_member_id",

715 "event".*

716 FROM "member" CROSS JOIN "event"

717 LEFT JOIN "issue"

718 ON "event"."issue_id" = "issue"."id"

719 LEFT JOIN "membership"

720 ON "member"."id" = "membership"."member_id"

721 AND "issue"."area_id" = "membership"."area_id"

722 LEFT JOIN "interest"

723 ON "member"."id" = "interest"."member_id"

724 AND "event"."issue_id" = "interest"."issue_id"

725 LEFT JOIN "supporter"

726 ON "member"."id" = "supporter"."member_id"

727 AND "event"."initiative_id" = "supporter"."initiative_id"

728 LEFT JOIN "ignored_member"

729 ON "member"."id" = "ignored_member"."member_id"

730 AND "event"."member_id" = "ignored_member"."other_member_id"

731 LEFT JOIN "ignored_initiative"

732 ON "member"."id" = "ignored_initiative"."member_id"

733 AND "event"."initiative_id" = "ignored_initiative"."initiative_id"

734 WHERE (

735 "member"."notify_event_id" ISNULL OR

736 ( "member"."notify_event_id" NOTNULL AND

737 "member"."notify_event_id" < "event"."id" ) )

738 AND (

739 ( "member"."notify_level" >= 'all' ) OR

740 ( "member"."notify_level" >= 'voting' AND

741 "event"."state" IN (

742 'voting',

743 'finished_without_winner',

744 'finished_with_winner' ) ) OR

745 ( "member"."notify_level" >= 'verification' AND

746 "event"."state" IN (

747 'verification',

748 'canceled_after_revocation_during_verification',

749 'canceled_no_initiative_admitted' ) ) OR

750 ( "member"."notify_level" >= 'discussion' AND

751 "event"."state" IN (

752 'discussion',

753 'canceled_after_revocation_during_discussion' ) ) )

754 AND (

755 "supporter"."member_id" NOTNULL OR

756 "interest"."member_id" NOTNULL OR

757 ( "membership"."member_id" NOTNULL AND

758 "event"."event" IN (

759 'issue_state_changed',

760 'initiative_created_in_new_issue',

761 'initiative_created_in_existing_issue',

762 'initiative_revoked' ) ) )

763 AND "ignored_member"."member_id" ISNULL

764 AND "ignored_initiative"."member_id" ISNULL;

765

766 COMMENT ON VIEW "pending_notification" IS 'Events to be sent to "notify_email" address of member referred to by "seen_by_member_id"';

767

768

769 COMMENT ON TYPE "timeline_event" IS 'Types of event in timeline tables (DEPRECATED)';

770 COMMENT ON VIEW "timeline_issue" IS 'Helper view for "timeline" view (DEPRECATED)';

771 COMMENT ON VIEW "timeline_initiative" IS 'Helper view for "timeline" view (DEPRECATED)';

772 COMMENT ON VIEW "timeline_draft" IS 'Helper view for "timeline" view (DEPRECATED)';

773 COMMENT ON VIEW "timeline_suggestion" IS 'Helper view for "timeline" view (DEPRECATED)';

774 COMMENT ON VIEW "timeline" IS 'Aggregation of different events in the system (DEPRECATED)';

775

776

777 -- Modified "delegation_chain" functions:

778

779 CREATE TYPE "delegation_chain_row" AS (

780 "index" INT4,

781 "member_id" INT4,

782 "member_valid" BOOLEAN,

783 "participation" BOOLEAN,

784 "overridden" BOOLEAN,

785 "scope_in" "delegation_scope",

786 "scope_out" "delegation_scope",

787 "disabled_out" BOOLEAN,

788 "loop" "delegation_chain_loop_tag" );

789

790 COMMENT ON TYPE "delegation_chain_row" IS 'Type of rows returned by "delegation_chain"(...) functions';

791

792 COMMENT ON COLUMN "delegation_chain_row"."index" IS 'Index starting with 0 and counting up';

793 COMMENT ON COLUMN "delegation_chain_row"."participation" IS 'In case of delegation chains for issues: interest, for areas: membership, for global delegation chains: always null';

794 COMMENT ON COLUMN "delegation_chain_row"."overridden" IS 'True, if an entry with lower index has "participation" set to true';

795 COMMENT ON COLUMN "delegation_chain_row"."scope_in" IS 'Scope of used incoming delegation';

796 COMMENT ON COLUMN "delegation_chain_row"."scope_out" IS 'Scope of used outgoing delegation';

797 COMMENT ON COLUMN "delegation_chain_row"."disabled_out" IS 'Outgoing delegation is explicitly disabled by a delegation with trustee_id set to NULL';

798 COMMENT ON COLUMN "delegation_chain_row"."loop" IS 'Not null, if member is part of a loop, see "delegation_chain_loop_tag" type';

799

800

801 CREATE FUNCTION "delegation_chain"

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

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

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

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

806 "simulate_trustee_id_p" "member"."id"%TYPE )

807 RETURNS SETOF "delegation_chain_row"

808 LANGUAGE 'plpgsql' STABLE AS $$

809 DECLARE

810 "scope_v" "delegation_scope";

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

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

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

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

815 "output_row" "delegation_chain_row";

816 "output_rows" "delegation_chain_row"[];

817 "delegation_row" "delegation"%ROWTYPE;

818 "row_count" INT4;

819 "i" INT4;

820 "loop_v" BOOLEAN;

821 BEGIN

822 IF

823 "unit_id_p" NOTNULL AND

824 "area_id_p" ISNULL AND

825 "issue_id_p" ISNULL

826 THEN

827 "scope_v" := 'unit';

828 "unit_id_v" := "unit_id_p";

829 ELSIF

830 "unit_id_p" ISNULL AND

831 "area_id_p" NOTNULL AND

832 "issue_id_p" ISNULL

833 THEN

834 "scope_v" := 'area';

835 "area_id_v" := "area_id_p";

836 SELECT "unit_id" INTO "unit_id_v"

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

838 ELSIF

839 "unit_id_p" ISNULL AND

840 "area_id_p" ISNULL AND

841 "issue_id_p" NOTNULL

842 THEN

843 "scope_v" := 'issue';

844 SELECT "area_id" INTO "area_id_v"

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

846 SELECT "unit_id" INTO "unit_id_v"

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

848 ELSE

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

850 END IF;

851 "visited_member_ids" := '{}';

852 "loop_member_id_v" := NULL;

853 "output_rows" := '{}';

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

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

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

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

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

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

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

861 LOOP

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

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

864 ELSE

865 "visited_member_ids" :=

866 "visited_member_ids" || "output_row"."member_id";

867 END IF;

868 IF "output_row"."participation" THEN

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

870 END IF;

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

872 IF EXISTS (

873 SELECT NULL FROM "member" JOIN "privilege"

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

875 AND "privilege"."unit_id" = "unit_id_v"

876 WHERE "id" = "output_row"."member_id"

877 AND "member"."active" AND "privilege"."voting_right"

878 ) THEN

879 IF "scope_v" = 'unit' THEN

880 SELECT * INTO "delegation_row" FROM "delegation"

881 WHERE "truster_id" = "output_row"."member_id"

882 AND "unit_id" = "unit_id_v";

883 ELSIF "scope_v" = 'area' THEN

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

885 SELECT NULL FROM "membership"

886 WHERE "area_id" = "area_id_p"

887 AND "member_id" = "output_row"."member_id"

888 );

889 SELECT * INTO "delegation_row" FROM "delegation"

890 WHERE "truster_id" = "output_row"."member_id"

891 AND (

892 "unit_id" = "unit_id_v" OR

893 "area_id" = "area_id_v"

894 )

895 ORDER BY "scope" DESC;

896 ELSIF "scope_v" = 'issue' THEN

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

898 SELECT NULL FROM "interest"

899 WHERE "issue_id" = "issue_id_p"

900 AND "member_id" = "output_row"."member_id"

901 );

902 SELECT * INTO "delegation_row" FROM "delegation"

903 WHERE "truster_id" = "output_row"."member_id"

904 AND (

905 "unit_id" = "unit_id_v" OR

906 "area_id" = "area_id_v" OR

907 "issue_id" = "issue_id_p"

908 )

909 ORDER BY "scope" DESC;

910 END IF;

911 ELSE

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

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

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

915 "delegation_row" := ROW(NULL);

916 END IF;

917 IF

918 "output_row"."member_id" = "member_id_p" AND

919 "simulate_trustee_id_p" NOTNULL

920 THEN

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

922 "output_rows" := "output_rows" || "output_row";

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

924 ELSIF "delegation_row"."trustee_id" NOTNULL THEN

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

926 "output_rows" := "output_rows" || "output_row";

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

928 ELSIF "delegation_row"."scope" NOTNULL THEN

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

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

931 "output_rows" := "output_rows" || "output_row";

932 EXIT;

933 ELSE

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

935 "output_rows" := "output_rows" || "output_row";

936 EXIT;

937 END IF;

938 EXIT WHEN "loop_member_id_v" NOTNULL;

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

940 END LOOP;

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

942 "i" := 1;

943 "loop_v" := FALSE;

944 LOOP

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

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

947 IF "loop_v" THEN

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

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

950 ELSIF "i" = "row_count" THEN

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

952 ELSE

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

954 END IF;

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

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

957 "loop_v" := TRUE;

958 END IF;

959 IF "scope_v" = 'unit' THEN

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

961 END IF;

962 RETURN NEXT "output_row";

963 "i" := "i" + 1;

964 END LOOP;

965 RETURN;

966 END;

967 $$;

968

969 COMMENT ON FUNCTION "delegation_chain"

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

971 "unit"."id"%TYPE,

972 "area"."id"%TYPE,

973 "issue"."id"%TYPE,

974 "member"."id"%TYPE )

975 IS 'Helper function for frontends to display delegation chains; Not part of internal voting logic';

976

977

978 CREATE FUNCTION "delegation_chain"

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

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

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

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

983 RETURNS SETOF "delegation_chain_row"

984 LANGUAGE 'plpgsql' STABLE AS $$

985 DECLARE

986 "result_row" "delegation_chain_row";

987 BEGIN

988 FOR "result_row" IN

989 SELECT * FROM "delegation_chain"(

990 "member_id_p", "area_id_p", "issue_id_p", NULL

991 )

992 LOOP

993 RETURN NEXT "result_row";

994 END LOOP;

995 RETURN;

996 END;

997 $$;

998

999 COMMENT ON FUNCTION "delegation_chain"

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

1001 "unit"."id"%TYPE,

1002 "area"."id"%TYPE,

1003 "issue"."id"%TYPE )

1004 IS 'Shortcut for "delegation_chain"(...) function where 4th parameter is null';

1005

1006

1007 -- Modified core functions:

1008

1009 CREATE OR REPLACE FUNCTION "lock_issue"

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

1011 RETURNS VOID

1012 LANGUAGE 'plpgsql' VOLATILE AS $$

1013 BEGIN

1014 LOCK TABLE "member" IN SHARE MODE;

1015 LOCK TABLE "privilege" IN SHARE MODE;

1016 LOCK TABLE "membership" IN SHARE MODE;

1017 LOCK TABLE "policy" IN SHARE MODE;

1018 PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE;

1019 -- NOTE: The row-level exclusive lock in combination with the

1020 -- share_row_lock_issue(_via_initiative)_trigger functions (which

1021 -- acquire a row-level share lock on the issue) ensure that no data

1022 -- is changed, which could affect calculation of snapshots or

1023 -- counting of votes. Table "delegation" must be table-level-locked,

1024 -- as it also contains issue- and global-scope delegations.

1025 LOCK TABLE "delegation" IN SHARE MODE;

1026 LOCK TABLE "direct_population_snapshot" IN EXCLUSIVE MODE;

1027 LOCK TABLE "delegating_population_snapshot" IN EXCLUSIVE MODE;

1028 LOCK TABLE "direct_interest_snapshot" IN EXCLUSIVE MODE;

1029 LOCK TABLE "delegating_interest_snapshot" IN EXCLUSIVE MODE;

1030 LOCK TABLE "direct_supporter_snapshot" IN EXCLUSIVE MODE;

1031 RETURN;

1032 END;

1033 $$;

1034

1035 CREATE OR REPLACE FUNCTION "calculate_member_counts"()

1036 RETURNS VOID

1037 LANGUAGE 'plpgsql' VOLATILE AS $$

1038 BEGIN

1039 LOCK TABLE "member" IN SHARE MODE;

1040 LOCK TABLE "member_count" IN EXCLUSIVE MODE;

1041 LOCK TABLE "unit" IN EXCLUSIVE MODE;

1042 LOCK TABLE "area" IN EXCLUSIVE MODE;

1043 LOCK TABLE "privilege" IN SHARE MODE;

1044 LOCK TABLE "membership" IN SHARE MODE;

1045 DELETE FROM "member_count";

1046 INSERT INTO "member_count" ("total_count")

1047 SELECT "total_count" FROM "member_count_view";

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

1049 FROM "unit_member_count" AS "view"

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

1051 UPDATE "area" SET

1052 "direct_member_count" = "view"."direct_member_count",

1053 "member_weight" = "view"."member_weight",

1054 "autoreject_weight" = "view"."autoreject_weight"

1055 FROM "area_member_count" AS "view"

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

1057 RETURN;

1058 END;

1059 $$;

1060

1061 CREATE OR REPLACE FUNCTION "create_population_snapshot"

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

1063 RETURNS VOID

1064 LANGUAGE 'plpgsql' VOLATILE AS $$

1065 DECLARE

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

1067 BEGIN

1068 DELETE FROM "direct_population_snapshot"

1069 WHERE "issue_id" = "issue_id_p"

1070 AND "event" = 'periodic';

1071 DELETE FROM "delegating_population_snapshot"

1072 WHERE "issue_id" = "issue_id_p"

1073 AND "event" = 'periodic';

1074 INSERT INTO "direct_population_snapshot"

1075 ("issue_id", "event", "member_id")

1076 SELECT

1077 "issue_id_p" AS "issue_id",

1078 'periodic'::"snapshot_event" AS "event",

1079 "member"."id" AS "member_id"

1080 FROM "issue"

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

1082 JOIN "membership" ON "area"."id" = "membership"."area_id"

1083 JOIN "member" ON "membership"."member_id" = "member"."id"

1084 JOIN "privilege"

1085 ON "privilege"."unit_id" = "area"."unit_id"

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

1087 WHERE "issue"."id" = "issue_id_p"

1088 AND "member"."active" AND "privilege"."voting_right"

1089 UNION

1090 SELECT

1091 "issue_id_p" AS "issue_id",

1092 'periodic'::"snapshot_event" AS "event",

1093 "member"."id" AS "member_id"

1094 FROM "issue"

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

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

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

1098 JOIN "privilege"

1099 ON "privilege"."unit_id" = "area"."unit_id"

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

1101 WHERE "issue"."id" = "issue_id_p"

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

1103 FOR "member_id_v" IN

1104 SELECT "member_id" FROM "direct_population_snapshot"

1105 WHERE "issue_id" = "issue_id_p"

1106 AND "event" = 'periodic'

1107 LOOP

1108 UPDATE "direct_population_snapshot" SET

1109 "weight" = 1 +

1110 "weight_of_added_delegations_for_population_snapshot"(

1111 "issue_id_p",

1112 "member_id_v",

1113 '{}'

1114 )

1115 WHERE "issue_id" = "issue_id_p"

1116 AND "event" = 'periodic'

1117 AND "member_id" = "member_id_v";

1118 END LOOP;

1119 RETURN;

1120 END;

1121 $$;

1122

1123 CREATE OR REPLACE FUNCTION "create_interest_snapshot"

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

1125 RETURNS VOID

1126 LANGUAGE 'plpgsql' VOLATILE AS $$

1127 DECLARE

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

1129 BEGIN

1130 DELETE FROM "direct_interest_snapshot"

1131 WHERE "issue_id" = "issue_id_p"

1132 AND "event" = 'periodic';

1133 DELETE FROM "delegating_interest_snapshot"

1134 WHERE "issue_id" = "issue_id_p"

1135 AND "event" = 'periodic';

1136 DELETE FROM "direct_supporter_snapshot"

1137 WHERE "issue_id" = "issue_id_p"

1138 AND "event" = 'periodic';

1139 INSERT INTO "direct_interest_snapshot"

1140 ("issue_id", "event", "member_id", "voting_requested")

1141 SELECT

1142 "issue_id_p" AS "issue_id",

1143 'periodic' AS "event",

1144 "member"."id" AS "member_id",

1145 "interest"."voting_requested"

1146 FROM "issue"

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

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

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

1150 JOIN "privilege"

1151 ON "privilege"."unit_id" = "area"."unit_id"

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

1153 WHERE "issue"."id" = "issue_id_p"

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

1155 FOR "member_id_v" IN

1156 SELECT "member_id" FROM "direct_interest_snapshot"

1157 WHERE "issue_id" = "issue_id_p"

1158 AND "event" = 'periodic'

1159 LOOP

1160 UPDATE "direct_interest_snapshot" SET

1161 "weight" = 1 +

1162 "weight_of_added_delegations_for_interest_snapshot"(

1163 "issue_id_p",

1164 "member_id_v",

1165 '{}'

1166 )

1167 WHERE "issue_id" = "issue_id_p"

1168 AND "event" = 'periodic'

1169 AND "member_id" = "member_id_v";

1170 END LOOP;

1171 INSERT INTO "direct_supporter_snapshot"

1172 ( "issue_id", "initiative_id", "event", "member_id",

1173 "informed", "satisfied" )

1174 SELECT

1175 "issue_id_p" AS "issue_id",

1176 "initiative"."id" AS "initiative_id",

1177 'periodic' AS "event",

1178 "supporter"."member_id" AS "member_id",

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

1180 NOT EXISTS (

1181 SELECT NULL FROM "critical_opinion"

1182 WHERE "initiative_id" = "initiative"."id"

1183 AND "member_id" = "supporter"."member_id"

1184 ) AS "satisfied"

1185 FROM "initiative"

1186 JOIN "supporter"

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

1188 JOIN "current_draft"

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

1190 JOIN "direct_interest_snapshot"

1191 ON "supporter"."member_id" = "direct_interest_snapshot"."member_id"

1192 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"

1193 AND "event" = 'periodic'

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

1195 RETURN;

1196 END;

1197 $$;

1198

1199 CREATE OR REPLACE FUNCTION "freeze_after_snapshot"

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

1201 RETURNS VOID

1202 LANGUAGE 'plpgsql' VOLATILE AS $$

1203 DECLARE

1204 "issue_row" "issue"%ROWTYPE;

1205 "policy_row" "policy"%ROWTYPE;

1206 "initiative_row" "initiative"%ROWTYPE;

1207 BEGIN

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

1209 SELECT * INTO "policy_row"

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

1211 PERFORM "set_snapshot_event"("issue_id_p", 'full_freeze');

1212 FOR "initiative_row" IN

1213 SELECT * FROM "initiative"

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

1215 LOOP

1216 IF

1217 "initiative_row"."satisfied_supporter_count" > 0 AND

1218 "initiative_row"."satisfied_supporter_count" *

1219 "policy_row"."initiative_quorum_den" >=

1220 "issue_row"."population" * "policy_row"."initiative_quorum_num"

1221 THEN

1222 UPDATE "initiative" SET "admitted" = TRUE

1223 WHERE "id" = "initiative_row"."id";

1224 ELSE

1225 UPDATE "initiative" SET "admitted" = FALSE

1226 WHERE "id" = "initiative_row"."id";

1227 END IF;

1228 END LOOP;

1229 IF EXISTS (

1230 SELECT NULL FROM "initiative"

1231 WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE

1232 ) THEN

1233 UPDATE "issue" SET

1234 "state" = 'voting',

1235 "accepted" = coalesce("accepted", now()),

1236 "half_frozen" = coalesce("half_frozen", now()),

1237 "fully_frozen" = now()

1238 WHERE "id" = "issue_id_p";

1239 ELSE

1240 UPDATE "issue" SET

1241 "state" = 'canceled_no_initiative_admitted',

1242 "accepted" = coalesce("accepted", now()),

1243 "half_frozen" = coalesce("half_frozen", now()),

1244 "fully_frozen" = now(),

1245 "closed" = now(),

1246 "ranks_available" = TRUE

1247 WHERE "id" = "issue_id_p";

1248 -- NOTE: The following DELETE statements have effect only when

1249 -- issue state has been manipulated

1250 DELETE FROM "direct_voter" WHERE "issue_id" = "issue_id_p";

1251 DELETE FROM "delegating_voter" WHERE "issue_id" = "issue_id_p";

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

1253 END IF;

1254 RETURN;

1255 END;

1256 $$;

1257

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

1259 RETURNS VOID

1260 LANGUAGE 'plpgsql' VOLATILE AS $$

1261 DECLARE

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

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

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

1265 BEGIN

1266 PERFORM "lock_issue"("issue_id_p");

1267 SELECT "id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";

1268 SELECT "id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v";

1269 DELETE FROM "delegating_voter"

1270 WHERE "issue_id" = "issue_id_p";

1271 DELETE FROM "direct_voter"

1272 WHERE "issue_id" = "issue_id_p"

1273 AND "autoreject" = TRUE;

1274 DELETE FROM "direct_voter"

1275 USING (

1276 SELECT

1277 "direct_voter"."member_id"

1278 FROM "direct_voter"

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

1280 LEFT JOIN "privilege"

1281 ON "privilege"."unit_id" = "unit_id_v"

1282 AND "privilege"."member_id" = "direct_voter"."member_id"

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

1284 "member"."active" = FALSE OR

1285 "privilege"."voting_right" ISNULL OR

1286 "privilege"."voting_right" = FALSE

1287 )

1288 ) AS "subquery"

1289 WHERE "direct_voter"."issue_id" = "issue_id_p"

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

1291 UPDATE "direct_voter" SET "weight" = 1

1292 WHERE "issue_id" = "issue_id_p";

1293 PERFORM "add_vote_delegations"("issue_id_p");

1294 FOR "member_id_v" IN

1295 SELECT "interest"."member_id"

1296 FROM "interest"

1297 JOIN "member"

1298 ON "interest"."member_id" = "member"."id"

1299 LEFT JOIN "direct_voter"

1300 ON "interest"."member_id" = "direct_voter"."member_id"

1301 AND "interest"."issue_id" = "direct_voter"."issue_id"

1302 LEFT JOIN "delegating_voter"

1303 ON "interest"."member_id" = "delegating_voter"."member_id"

1304 AND "interest"."issue_id" = "delegating_voter"."issue_id"

1305 WHERE "interest"."issue_id" = "issue_id_p"

1306 AND "interest"."autoreject" = TRUE

1307 AND "member"."active"

1308 AND "direct_voter"."member_id" ISNULL

1309 AND "delegating_voter"."member_id" ISNULL

1310 UNION SELECT "membership"."member_id"

1311 FROM "membership"

1312 JOIN "member"

1313 ON "membership"."member_id" = "member"."id"

1314 LEFT JOIN "interest"

1315 ON "membership"."member_id" = "interest"."member_id"

1316 AND "interest"."issue_id" = "issue_id_p"

1317 LEFT JOIN "direct_voter"

1318 ON "membership"."member_id" = "direct_voter"."member_id"

1319 AND "direct_voter"."issue_id" = "issue_id_p"

1320 LEFT JOIN "delegating_voter"

1321 ON "membership"."member_id" = "delegating_voter"."member_id"

1322 AND "delegating_voter"."issue_id" = "issue_id_p"

1323 WHERE "membership"."area_id" = "area_id_v"

1324 AND "membership"."autoreject" = TRUE

1325 AND "member"."active"

1326 AND "interest"."autoreject" ISNULL

1327 AND "direct_voter"."member_id" ISNULL

1328 AND "delegating_voter"."member_id" ISNULL

1329 LOOP

1330 INSERT INTO "direct_voter"

1331 ("member_id", "issue_id", "weight", "autoreject") VALUES

1332 ("member_id_v", "issue_id_p", 1, TRUE);

1333 INSERT INTO "vote" (

1334 "member_id",

1335 "issue_id",

1336 "initiative_id",

1337 "grade"

1338 ) SELECT

1339 "member_id_v" AS "member_id",

1340 "issue_id_p" AS "issue_id",

1341 "id" AS "initiative_id",

1342 -1 AS "grade"

1343 FROM "initiative" WHERE "issue_id" = "issue_id_p";

1344 END LOOP;

1345 PERFORM "add_vote_delegations"("issue_id_p");

1346 UPDATE "issue" SET

1347 "state" = 'calculation',

1348 "closed" = now(),

1349 "voter_count" = (

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

1351 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"

1352 )

1353 WHERE "id" = "issue_id_p";

1354 UPDATE "initiative" SET

1355 "positive_votes" = "vote_counts"."positive_votes",

1356 "negative_votes" = "vote_counts"."negative_votes",

1357 "agreed" = CASE WHEN "majority_strict" THEN

1358 "vote_counts"."positive_votes" * "majority_den" >

1359 "majority_num" *

1360 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")

1361 ELSE

1362 "vote_counts"."positive_votes" * "majority_den" >=

1363 "majority_num" *

1364 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")

1365 END

1366 FROM

1367 ( SELECT

1368 "initiative"."id" AS "initiative_id",

1369 coalesce(

1370 sum(

1371 CASE WHEN "grade" > 0 THEN "direct_voter"."weight" ELSE 0 END

1372 ),

1373 0

1374 ) AS "positive_votes",

1375 coalesce(

1376 sum(

1377 CASE WHEN "grade" < 0 THEN "direct_voter"."weight" ELSE 0 END

1378 ),

1379 0

1380 ) AS "negative_votes"

1381 FROM "initiative"

1382 JOIN "issue" ON "initiative"."issue_id" = "issue"."id"

1383 JOIN "policy" ON "issue"."policy_id" = "policy"."id"

1384 LEFT JOIN "direct_voter"

1385 ON "direct_voter"."issue_id" = "initiative"."issue_id"

1386 LEFT JOIN "vote"

1387 ON "vote"."initiative_id" = "initiative"."id"

1388 AND "vote"."member_id" = "direct_voter"."member_id"

1389 WHERE "initiative"."issue_id" = "issue_id_p"

1390 AND "initiative"."admitted" -- NOTE: NULL case is handled too

1391 GROUP BY "initiative"."id"

1392 ) AS "vote_counts",

1393 "issue",

1394 "policy"

1395 WHERE "vote_counts"."initiative_id" = "initiative"."id"

1396 AND "issue"."id" = "initiative"."issue_id"

1397 AND "policy"."id" = "issue"."policy_id";

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

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

1400 INSERT INTO "battle" (

1401 "issue_id",

1402 "winning_initiative_id", "losing_initiative_id",

1403 "count"

1404 ) SELECT

1405 "issue_id",

1406 "winning_initiative_id", "losing_initiative_id",

1407 "count"

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

1409 END;

1410 $$;

1411

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

1413 RETURNS VOID

1414 LANGUAGE 'plpgsql' VOLATILE AS $$

1415 DECLARE

1416 "dimension_v" INTEGER;

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

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

1419 "i" INTEGER;

1420 "j" INTEGER;

1421 "k" INTEGER;

1422 "battle_row" "battle"%ROWTYPE;

1423 "rank_ary" INT4[];

1424 "rank_v" INT4;

1425 "done_v" INTEGER;

1426 "winners_ary" INTEGER[];

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

1428 BEGIN

1429 PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE;

1430 SELECT count(1) INTO "dimension_v" FROM "initiative"

1431 WHERE "issue_id" = "issue_id_p" AND "agreed";

1432 IF "dimension_v" = 1 THEN

1433 UPDATE "initiative" SET "rank" = 1

1434 WHERE "issue_id" = "issue_id_p" AND "agreed";

1435 ELSIF "dimension_v" > 1 THEN

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

1437 -- comparison:

1438 "vote_matrix" := "square_matrix_init_string"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4)

1439 "i" := 1;

1440 "j" := 2;

1441 FOR "battle_row" IN

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

1443 ORDER BY "winning_initiative_id", "losing_initiative_id"

1444 LOOP

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

1446 IF "j" = "dimension_v" THEN

1447 "i" := "i" + 1;

1448 "j" := 1;

1449 ELSE

1450 "j" := "j" + 1;

1451 IF "j" = "i" THEN

1452 "j" := "j" + 1;

1453 END IF;

1454 END IF;

1455 END LOOP;

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

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

1458 END IF;

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

1460 -- function:

1461 "matrix" := "square_matrix_init_string"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4)

1462 "i" := 1;

1463 LOOP

1464 "j" := 1;

1465 LOOP

1466 IF "i" != "j" THEN

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

1468 "vote_matrix"["i"]["j"],

1469 "vote_matrix"["j"]["i"]

1470 );

1471 END IF;

1472 EXIT WHEN "j" = "dimension_v";

1473 "j" := "j" + 1;

1474 END LOOP;

1475 EXIT WHEN "i" = "dimension_v";

1476 "i" := "i" + 1;

1477 END LOOP;

1478 -- Find best paths:

1479 "i" := 1;

1480 LOOP

1481 "j" := 1;

1482 LOOP

1483 IF "i" != "j" THEN

1484 "k" := 1;

1485 LOOP

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

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

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

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

1490 END IF;

1491 ELSE

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

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

1494 END IF;

1495 END IF;

1496 END IF;

1497 EXIT WHEN "k" = "dimension_v";

1498 "k" := "k" + 1;

1499 END LOOP;

1500 END IF;

1501 EXIT WHEN "j" = "dimension_v";

1502 "j" := "j" + 1;

1503 END LOOP;

1504 EXIT WHEN "i" = "dimension_v";

1505 "i" := "i" + 1;

1506 END LOOP;

1507 -- Determine order of winners:

1508 "rank_ary" := "array_init_string"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4)

1509 "rank_v" := 1;

1510 "done_v" := 0;

1511 LOOP

1512 "winners_ary" := '{}';

1513 "i" := 1;

1514 LOOP

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

1516 "j" := 1;

1517 LOOP

1518 IF

1519 "i" != "j" AND

1520 "rank_ary"["j"] ISNULL AND

1521 "matrix"["j"]["i"] > "matrix"["i"]["j"]

1522 THEN

1523 -- someone else is better

1524 EXIT;

1525 END IF;

1526 IF "j" = "dimension_v" THEN

1527 -- noone is better

1528 "winners_ary" := "winners_ary" || "i";

1529 EXIT;

1530 END IF;

1531 "j" := "j" + 1;

1532 END LOOP;

1533 END IF;

1534 EXIT WHEN "i" = "dimension_v";

1535 "i" := "i" + 1;

1536 END LOOP;

1537 "i" := 1;

1538 LOOP

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

1540 "done_v" := "done_v" + 1;

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

1542 "i" := "i" + 1;

1543 END LOOP;

1544 EXIT WHEN "done_v" = "dimension_v";

1545 "rank_v" := "rank_v" + 1;

1546 END LOOP;

1547 -- write preliminary ranks:

1548 "i" := 1;

1549 FOR "initiative_id_v" IN

1550 SELECT "id" FROM "initiative"

1551 WHERE "issue_id" = "issue_id_p" AND "agreed"

1552 ORDER BY "id"

1553 LOOP

1554 UPDATE "initiative" SET "rank" = "rank_ary"["i"]

1555 WHERE "id" = "initiative_id_v";

1556 "i" := "i" + 1;

1557 END LOOP;

1558 IF "i" != "dimension_v" + 1 THEN

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

1560 END IF;

1561 -- straighten ranks (start counting with 1, no equal ranks):

1562 "rank_v" := 1;

1563 FOR "initiative_id_v" IN

1564 SELECT "id" FROM "initiative"

1565 WHERE "issue_id" = "issue_id_p" AND "rank" NOTNULL

1566 ORDER BY

1567 "rank",

1568 "vote_ratio"("positive_votes", "negative_votes") DESC,

1569 "id"

1570 LOOP

1571 UPDATE "initiative" SET "rank" = "rank_v"

1572 WHERE "id" = "initiative_id_v";

1573 "rank_v" := "rank_v" + 1;

1574 END LOOP;

1575 END IF;

1576 -- mark issue as finished

1577 UPDATE "issue" SET

1578 "state" =

1579 CASE WHEN "dimension_v" = 0 THEN

1580 'finished_without_winner'::"issue_state"

1581 ELSE

1582 'finished_with_winner'::"issue_state"

1583 END,

1584 "ranks_available" = TRUE

1585 WHERE "id" = "issue_id_p";

1586 RETURN;

1587 END;

1588 $$;

1589

1590 CREATE OR REPLACE FUNCTION "check_issue"

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

1592 RETURNS VOID

1593 LANGUAGE 'plpgsql' VOLATILE AS $$

1594 DECLARE

1595 "issue_row" "issue"%ROWTYPE;

1596 "policy_row" "policy"%ROWTYPE;

1597 "voting_requested_v" BOOLEAN;

1598 BEGIN

1599 PERFORM "lock_issue"("issue_id_p");

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

1601 -- only process open issues:

1602 IF "issue_row"."closed" ISNULL THEN

1603 SELECT * INTO "policy_row" FROM "policy"

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

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

1606 IF "issue_row"."fully_frozen" ISNULL THEN

1607 PERFORM "create_snapshot"("issue_id_p");

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

1609 END IF;

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

1611 IF "issue_row"."accepted" ISNULL THEN

1612 IF EXISTS (

1613 SELECT NULL FROM "initiative"

1614 WHERE "issue_id" = "issue_id_p"

1615 AND "supporter_count" > 0

1616 AND "supporter_count" * "policy_row"."issue_quorum_den"

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

1618 ) THEN

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

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

1621 -- NOTE: "issue_row" used later

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

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

1624 UPDATE "issue" SET

1625 "state" = "issue_row"."state",

1626 "accepted" = "issue_row"."accepted"

1627 WHERE "id" = "issue_row"."id";

1628 ELSIF

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

1630 THEN

1631 -- close issues, if admission time has expired

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

1633 UPDATE "issue" SET

1634 "state" = 'canceled_issue_not_accepted',

1635 "closed" = now()

1636 WHERE "id" = "issue_row"."id";

1637 END IF;

1638 END IF;

1639 -- eventually half freeze issues:

1640 IF

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

1642 "issue_row"."accepted" NOTNULL AND

1643 "issue_row"."half_frozen" ISNULL

1644 THEN

1645 SELECT

1646 CASE

1647 WHEN "vote_now" * 2 > "issue_row"."population" THEN

1648 TRUE

1649 WHEN "vote_later" * 2 > "issue_row"."population" THEN

1650 FALSE

1651 ELSE NULL

1652 END

1653 INTO "voting_requested_v"

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

1655 IF

1656 "voting_requested_v" OR (

1657 "voting_requested_v" ISNULL AND

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

1659 )

1660 THEN

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

1662 -- NOTE: "issue_row" used later

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

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

1665 UPDATE "issue" SET

1666 "state" = "issue_row"."state",

1667 "half_frozen" = "issue_row"."half_frozen"

1668 WHERE "id" = "issue_row"."id";

1669 END IF;

1670 END IF;

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

1672 IF

1673 "issue_row"."closed" ISNULL AND

1674 NOT EXISTS (

1675 -- all initiatives are revoked

1676 SELECT NULL FROM "initiative"

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

1678 ) AND (

1679 -- and issue has not been accepted yet

1680 "issue_row"."accepted" ISNULL OR

1681 NOT EXISTS (

1682 -- or no initiatives have been revoked lately

1683 SELECT NULL FROM "initiative"

1684 WHERE "issue_id" = "issue_id_p"

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

1686 ) OR (

1687 -- or verification time has elapsed

1688 "issue_row"."half_frozen" NOTNULL AND

1689 "issue_row"."fully_frozen" ISNULL AND

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

1691 )

1692 )

1693 THEN

1694 -- NOTE: "issue_row" used later

1695 IF "issue_row"."accepted" ISNULL THEN

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

1697 ELSIF "issue_row"."half_frozen" ISNULL THEN

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

1699 ELSE

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

1701 END IF;

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

1703 UPDATE "issue" SET

1704 "state" = "issue_row"."state",

1705 "closed" = "issue_row"."closed"

1706 WHERE "id" = "issue_row"."id";

1707 END IF;

1708 -- fully freeze issue after verification time:

1709 IF

1710 "issue_row"."half_frozen" NOTNULL AND

1711 "issue_row"."fully_frozen" ISNULL AND

1712 "issue_row"."closed" ISNULL AND

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

1714 THEN

1715 PERFORM "freeze_after_snapshot"("issue_id_p");

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

1717 END IF;

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

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

1720 IF

1721 "issue_row"."closed" ISNULL AND

1722 "issue_row"."fully_frozen" NOTNULL AND

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

1724 THEN

1725 PERFORM "close_voting"("issue_id_p");

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

1727 PERFORM "calculate_ranks"("issue_id_p");

1728 END IF;

1729 END IF;

1730 RETURN;

1731 END;

1732 $$;

1733

1734 CREATE OR REPLACE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE)

1735 RETURNS VOID

1736 LANGUAGE 'plpgsql' VOLATILE AS $$

1737 DECLARE

1738 "issue_row" "issue"%ROWTYPE;

1739 BEGIN

1740 SELECT * INTO "issue_row"

1741 FROM "issue" WHERE "id" = "issue_id_p"

1742 FOR UPDATE;

1743 IF "issue_row"."cleaned" ISNULL THEN

1744 UPDATE "issue" SET

1745 "closed" = NULL,

1746 "ranks_available" = FALSE

1747 WHERE "id" = "issue_id_p";

1748 DELETE FROM "delegating_voter"

1749 WHERE "issue_id" = "issue_id_p";

1750 DELETE FROM "direct_voter"

1751 WHERE "issue_id" = "issue_id_p";

1752 DELETE FROM "delegating_interest_snapshot"

1753 WHERE "issue_id" = "issue_id_p";

1754 DELETE FROM "direct_interest_snapshot"

1755 WHERE "issue_id" = "issue_id_p";

1756 DELETE FROM "delegating_population_snapshot"

1757 WHERE "issue_id" = "issue_id_p";

1758 DELETE FROM "direct_population_snapshot"

1759 WHERE "issue_id" = "issue_id_p";

1760 DELETE FROM "non_voter"

1761 WHERE "issue_id" = "issue_id_p";

1762 DELETE FROM "delegation"

1763 WHERE "issue_id" = "issue_id_p";

1764 DELETE FROM "supporter"

1765 WHERE "issue_id" = "issue_id_p";

1766 UPDATE "issue" SET

1767 "closed" = "issue_row"."closed",

1768 "ranks_available" = "issue_row"."ranks_available",

1769 "cleaned" = now()

1770 WHERE "id" = "issue_id_p";

1771 END IF;

1772 RETURN;

1773 END;

1774 $$;

1775

1776 CREATE OR REPLACE FUNCTION "check_issue"

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

1778 RETURNS VOID

1779 LANGUAGE 'plpgsql' VOLATILE AS $$

1780 DECLARE

1781 "issue_row" "issue"%ROWTYPE;

1782 "policy_row" "policy"%ROWTYPE;

1783 "voting_requested_v" BOOLEAN;

1784 BEGIN

1785 PERFORM "lock_issue"("issue_id_p");

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

1787 -- only process open issues:

1788 IF "issue_row"."closed" ISNULL THEN

1789 SELECT * INTO "policy_row" FROM "policy"

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

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

1792 IF "issue_row"."fully_frozen" ISNULL THEN

1793 PERFORM "create_snapshot"("issue_id_p");

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

1795 END IF;

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

1797 IF "issue_row"."accepted" ISNULL THEN

1798 IF EXISTS (

1799 SELECT NULL FROM "initiative"

1800 WHERE "issue_id" = "issue_id_p"

1801 AND "supporter_count" > 0

1802 AND "supporter_count" * "policy_row"."issue_quorum_den"

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

1804 ) THEN

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

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

1807 -- NOTE: "issue_row" used later

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

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

1810 UPDATE "issue" SET

1811 "state" = "issue_row"."state",

1812 "accepted" = "issue_row"."accepted"

1813 WHERE "id" = "issue_row"."id";

1814 ELSIF

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

1816 THEN

1817 -- close issues, if admission time has expired

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

1819 UPDATE "issue" SET

1820 "state" = 'canceled_issue_not_accepted',

1821 "closed" = now()

1822 WHERE "id" = "issue_row"."id";

1823 END IF;

1824 END IF;

1825 -- eventually half freeze issues:

1826 IF

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

1828 "issue_row"."accepted" NOTNULL AND

1829 "issue_row"."half_frozen" ISNULL

1830 THEN

1831 SELECT

1832 CASE

1833 WHEN "vote_now" * 2 > "issue_row"."population" THEN

1834 TRUE

1835 WHEN "vote_later" * 2 > "issue_row"."population" THEN

1836 FALSE

1837 ELSE NULL

1838 END

1839 INTO "voting_requested_v"

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

1841 IF

1842 "voting_requested_v" OR (

1843 "voting_requested_v" ISNULL AND

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

1845 )

1846 THEN

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

1848 -- NOTE: "issue_row" used later

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

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

1851 UPDATE "issue" SET

1852 "state" = "issue_row"."state",

1853 "half_frozen" = "issue_row"."half_frozen"

1854 WHERE "id" = "issue_row"."id";

1855 END IF;

1856 END IF;

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

1858 IF

1859 "issue_row"."closed" ISNULL AND

1860 NOT EXISTS (

1861 -- all initiatives are revoked

1862 SELECT NULL FROM "initiative"

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

1864 ) AND (

1865 -- and issue has not been accepted yet

1866 "issue_row"."accepted" ISNULL OR

1867 NOT EXISTS (

1868 -- or no initiatives have been revoked lately

1869 SELECT NULL FROM "initiative"

1870 WHERE "issue_id" = "issue_id_p"

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

1872 ) OR (

1873 -- or verification time has elapsed

1874 "issue_row"."half_frozen" NOTNULL AND

1875 "issue_row"."fully_frozen" ISNULL AND

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

1877 )

1878 )

1879 THEN

1880 -- NOTE: "issue_row" used later

1881 IF "issue_row"."accepted" ISNULL THEN

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

1883 ELSIF "issue_row"."half_frozen" ISNULL THEN

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

1885 ELSE

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

1887 END IF;

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

1889 UPDATE "issue" SET

1890 "state" = "issue_row"."state",

1891 "closed" = "issue_row"."closed"

1892 WHERE "id" = "issue_row"."id";

1893 END IF;

1894 -- fully freeze issue after verification time:

1895 IF

1896 "issue_row"."half_frozen" NOTNULL AND

1897 "issue_row"."fully_frozen" ISNULL AND

1898 "issue_row"."closed" ISNULL AND

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

1900 THEN

1901 PERFORM "freeze_after_snapshot"("issue_id_p");

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

1903 END IF;

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

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

1906 IF

1907 "issue_row"."closed" ISNULL AND

1908 "issue_row"."fully_frozen" NOTNULL AND

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

1910 THEN

1911 PERFORM "close_voting"("issue_id_p");

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

1913 PERFORM "calculate_ranks"("issue_id_p");

1914 END IF;

1915 END IF;

1916 RETURN;

1917 END;

1918 $$;

1919

1920 CREATE OR REPLACE FUNCTION "delete_private_data"()

1921 RETURNS VOID

1922 LANGUAGE 'plpgsql' VOLATILE AS $$

1923 BEGIN

1924 UPDATE "member" SET

1925 "last_login" = NULL,

1926 "login" = NULL,

1927 "password" = NULL,

1928 "notify_email" = NULL,

1929 "notify_email_unconfirmed" = NULL,

1930 "notify_email_secret" = NULL,

1931 "notify_email_secret_expiry" = NULL,

1932 "notify_email_lock_expiry" = NULL,

1933 "password_reset_secret" = NULL,

1934 "password_reset_secret_expiry" = NULL,

1935 "organizational_unit" = NULL,

1936 "internal_posts" = NULL,

1937 "realname" = NULL,

1938 "birthday" = NULL,

1939 "address" = NULL,

1940 "email" = NULL,

1941 "xmpp_address" = NULL,

1942 "website" = NULL,

1943 "phone" = NULL,

1944 "mobile_phone" = NULL,

1945 "profession" = NULL,

1946 "external_memberships" = NULL,

1947 "external_posts" = NULL,

1948 "statement" = NULL;

1949 -- "text_search_data" is updated by triggers

1950 DELETE FROM "invite_code";

1951 DELETE FROM "setting";

1952 DELETE FROM "setting_map";

1953 DELETE FROM "member_relation_setting";

1954 DELETE FROM "member_image";

1955 DELETE FROM "contact";

1956 DELETE FROM "ignored_member";

1957 DELETE FROM "session";

1958 DELETE FROM "area_setting";

1959 DELETE FROM "issue_setting";

1960 DELETE FROM "ignored_initiative";

1961 DELETE FROM "initiative_setting";

1962 DELETE FROM "suggestion_setting";

1963 DELETE FROM "non_voter";

1964 DELETE FROM "direct_voter" USING "issue"

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

1966 AND "issue"."closed" ISNULL;

1967 RETURN;

1968 END;

1969 $$;

1970

1971

1972 -- Delete old "delegation_scope" TYPE:

1973

1974 DROP TYPE "delegation_scope_old";

1975

1976

1977 COMMIT;

1978

1979

1980 -- Generate issue states:

1981

1982 UPDATE "issue" SET "state" =

1983 CASE

1984 WHEN "closed" ISNULL THEN

1985 CASE

1986 WHEN "accepted" ISNULL THEN

1987 'admission'::"issue_state"

1988 WHEN "half_frozen" ISNULL THEN

1989 'discussion'::"issue_state"

1990 WHEN "fully_frozen" ISNULL THEN

1991 'verification'::"issue_state"

1992 ELSE

1993 'voting'::"issue_state"

1994 END

1995 WHEN "fully_frozen" NOTNULL THEN

1996 CASE

1997 WHEN "fully_frozen" = "closed" THEN

1998 'canceled_no_initiative_admitted'::"issue_state"

1999 ELSE

2000 'finished_without_winner'::"issue_state" -- NOTE: corrected later

2001 END

2002 WHEN "half_frozen" NOTNULL THEN

2003 'canceled_after_revocation_during_verification'::"issue_state"

2004 WHEN "accepted" NOTNULL THEN

2005 'canceled_after_revocation_during_discussion'::"issue_state"

2006 ELSE

2007 'canceled_revoked_before_accepted'::"issue_state" -- NOTE: corrected later

2008 END;

2009 UPDATE "issue" SET "state" = 'finished_with_winner'

2010 FROM "initiative"

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

2012 AND "issue"."state" = 'finished_without_winner'

2013 AND "initiative"."agreed";

2014 UPDATE "issue" SET "state" = 'canceled_issue_not_accepted'

2015 FROM "initiative"

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

2017 AND "issue"."state" = 'canceled_revoked_before_accepted'

2018 AND "initiative"."revoked" ISNULL;

2019

2020 ALTER TABLE "issue" ALTER "state" SET NOT NULL;

2021

2022 ALTER TABLE "issue" DROP CONSTRAINT "valid_state";

2023 ALTER TABLE "issue" ADD CONSTRAINT "valid_state" CHECK ((

2024 ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR

2025 ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR

2026 ("accepted" NOTNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR

2027 ("accepted" NOTNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR

2028 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR

2029 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR

2030 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR

2031 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR

2032 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = TRUE)

2033 ) AND (

2034 ("state" = 'admission' AND "closed" ISNULL AND "accepted" ISNULL) OR

2035 ("state" = 'discussion' AND "closed" ISNULL AND "accepted" NOTNULL AND "half_frozen" ISNULL) OR

2036 ("state" = 'verification' AND "closed" ISNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL) OR

2037 ("state" = 'voting' AND "closed" ISNULL AND "fully_frozen" NOTNULL) OR

2038 ("state" = 'canceled_revoked_before_accepted' AND "closed" NOTNULL AND "accepted" ISNULL) OR

2039 ("state" = 'canceled_issue_not_accepted' AND "closed" NOTNULL AND "accepted" ISNULL) OR

2040 ("state" = 'canceled_after_revocation_during_discussion' AND "closed" NOTNULL AND "half_frozen" ISNULL) OR

2041 ("state" = 'canceled_after_revocation_during_verification' AND "closed" NOTNULL AND "fully_frozen" ISNULL) OR

2042 ("state" = 'calculation' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = FALSE) OR

2043 ("state" = 'canceled_no_initiative_admitted' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE) OR

2044 ("state" = 'finished_without_winner' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE) OR

2045 ("state" = 'finished_with_winner' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE)

2046 ));

2047

2048

2049 -- Guess "revoked_by_member_id" values based on author of current draft:

2050

2051 UPDATE "initiative" SET "revoked_by_member_id" = "author_id"

2052 FROM "current_draft"

2053 WHERE "initiative"."id" = "current_draft"."initiative_id"

2054 AND "initiative"."revoked" NOTNULL;

2055

2056 ALTER TABLE "initiative" ADD

2057 CONSTRAINT "all_or_none_of_revoked_and_revoked_by_member_id_must_be_null"

2058 CHECK ("revoked" NOTNULL = "revoked_by_member_id" NOTNULL);

2059

2060

2061 -- Fill "unit_id" column with default value where neccessary:

2062

2063 UPDATE "delegation" SET "unit_id" = 1 WHERE "scope" = 'unit';

2064

2065 ALTER TABLE "delegation" ADD CONSTRAINT "area_id_and_issue_id_set_according_to_scope"

2066 CHECK (

2067 ("scope" = 'unit' AND "unit_id" NOTNULL AND "area_id" ISNULL AND "issue_id" ISNULL ) OR

2068 ("scope" = 'area' AND "unit_id" ISNULL AND "area_id" NOTNULL AND "issue_id" ISNULL ) OR

2069 ("scope" = 'issue' AND "unit_id" ISNULL AND "area_id" ISNULL AND "issue_id" NOTNULL) );

2070

2071

2072 -- Filling of "event" table with old (reconstructed) events:

2073

2074 DELETE FROM "event";

2075 SELECT setval('event_id_seq', 1, false);

2076

2077 INSERT INTO "event"

2078 ( "occurrence", "event", "member_id", "issue_id", "state",

2079 "initiative_id", "draft_id", "suggestion_id" )

2080 SELECT * FROM (

2081 SELECT * FROM (

2082 SELECT DISTINCT ON ("initiative"."id")

2083 "timeline"."occurrence",

2084 CASE WHEN "issue_creation"."issue_id" NOTNULL THEN

2085 'initiative_created_in_new_issue'::"event_type"

2086 ELSE

2087 'initiative_created_in_existing_issue'::"event_type"

2088 END,

2089 "draft"."author_id",

2090 "issue"."id",

2091 CASE

2092 WHEN "timeline"."occurrence" < "issue"."accepted" THEN

2093 'admission'::"issue_state"

2094 WHEN "timeline"."occurrence" < "issue"."half_frozen" THEN

2095 'discussion'::"issue_state"

2096 ELSE

2097 'verification'::"issue_state"

2098 END,

2099 "initiative"."id",

2100 "draft"."id",

2101 NULL::INT8

2102 FROM "timeline"

2103 JOIN "initiative" ON "timeline"."initiative_id" = "initiative"."id"

2104 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"

2105 LEFT JOIN "timeline" AS "issue_creation"

2106 ON "initiative"."issue_id" = "issue_creation"."issue_id"

2107 AND "issue_creation"."event" = 'issue_created'

2108 AND "timeline"."occurrence" = "issue_creation"."occurrence"

2109 JOIN "draft"

2110 ON "initiative"."id" = "draft"."initiative_id"

2111 WHERE "timeline"."event" = 'initiative_created'

2112 ORDER BY "initiative"."id", "draft"."id"

2113 ) AS "subquery" -- NOTE: subquery needed due to DISTINCT/ORDER

2114 UNION ALL

2115 SELECT

2116 "timeline"."occurrence",

2117 'issue_state_changed'::"event_type",

2118 NULL,

2119 "issue"."id",

2120 CASE

2121 WHEN "timeline"."event" IN (

2122 'issue_canceled',

2123 'issue_finished_without_voting',

2124 'issue_finished_after_voting'

2125 ) THEN

2126 "issue"."state"

2127 WHEN "timeline"."event" = 'issue_accepted' THEN

2128 'discussion'::"issue_state"

2129 WHEN "timeline"."event" = 'issue_half_frozen' THEN

2130 'verification'::"issue_state"

2131 WHEN "timeline"."event" = 'issue_voting_started' THEN

2132 'voting'::"issue_state"

2133 END,

2134 NULL,

2135 NULL,

2136 NULL

2137 FROM "timeline"

2138 JOIN "issue" ON "timeline"."issue_id" = "issue"."id"

2139 WHERE "timeline"."event" IN (

2140 'issue_canceled',

2141 'issue_accepted',

2142 'issue_half_frozen',

2143 'issue_finished_without_voting',

2144 'issue_voting_started',

2145 'issue_finished_after_voting' )

2146 UNION ALL

2147 SELECT

2148 "timeline"."occurrence",

2149 'initiative_revoked'::"event_type",

2150 "initiative"."revoked_by_member_id",

2151 "issue"."id",

2152 CASE

2153 WHEN "timeline"."occurrence" < "issue"."accepted" THEN

2154 'admission'::"issue_state"

2155 WHEN "timeline"."occurrence" < "issue"."half_frozen" THEN

2156 'discussion'::"issue_state"

2157 ELSE

2158 'verification'::"issue_state"

2159 END,

2160 "initiative"."id",

2161 "current_draft"."id",

2162 NULL

2163 FROM "timeline"

2164 JOIN "initiative" ON "timeline"."initiative_id" = "initiative"."id"

2165 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"

2166 JOIN "current_draft" ON "initiative"."id" = "current_draft"."initiative_id"

2167 WHERE "timeline"."event" = 'initiative_revoked'

2168 UNION ALL

2169 SELECT

2170 "timeline"."occurrence",

2171 'new_draft_created'::"event_type",

2172 "draft"."author_id",

2173 "issue"."id",

2174 CASE

2175 WHEN "timeline"."occurrence" < "issue"."accepted" THEN

2176 'admission'::"issue_state"

2177 WHEN "timeline"."occurrence" < "issue"."half_frozen" THEN

2178 'discussion'::"issue_state"

2179 ELSE

2180 'verification'::"issue_state"

2181 END,

2182 "initiative"."id",

2183 "draft"."id",

2184 NULL

2185 FROM "timeline"

2186 JOIN "draft" ON "timeline"."draft_id" = "draft"."id"

2187 JOIN "initiative" ON "draft"."initiative_id" = "initiative"."id"

2188 JOIN "issue" ON "initiative"."issue_id" = "issue"."id"

2189 LEFT JOIN "timeline" AS "initiative_creation"

2190 ON "initiative"."id" = "initiative_creation"."initiative_id"

2191 AND "initiative_creation"."event" = 'initiative_created'

2192 AND "timeline"."occurrence" = "initiative_creation"."occurrence"

2193 WHERE "timeline"."event" = 'draft_created'

2194 AND "initiative_creation"."initiative_id" ISNULL

2195 UNION ALL

2196 SELECT

2197 "timeline"."occurrence",

2198 'suggestion_created'::"event_type",

2199 "suggestion"."author_id",

2200 "issue"."id",

2201 CASE

2202 WHEN "timeline"."occurrence" < "issue"."accepted" THEN

2203 'admission'::"issue_state"

2204 WHEN "timeline"."occurrence" < "issue"."half_frozen" THEN

2205 'discussion'::"issue_state"

2206 ELSE

2207 'verification'::"issue_state"

2208 END,

2209 "initiative"."id",

2210 NULL,

2211 "suggestion"."id"

2212 FROM "timeline"

2213 JOIN "suggestion" ON "timeline"."suggestion_id" = "suggestion"."id"

2214 JOIN "initiative" ON "suggestion"."initiative_id" = "initiative"."id"

2215 JOIN "issue" ON "initiative"."issue_id" = "issue"."id"

2216 WHERE "timeline"."event" = 'suggestion_created'

2217 ) AS "subquery"

2218 ORDER BY "occurrence";

2219

2220