liquid_feedback_core: 7d6c5032262f update/core-update.v1.3.1-v1.4.0

liquid_feedback_core

view update/core-update.v1.3.1-v1.4.0_rc1.sql @ 118:7d6c5032262f

Reconstructing old events in v1.4.0_rc1 update script

author	jbe
date	Mon Mar 07 13:54:43 2011 +0100 (2011-03-07)
parents	a5d39efbfe5b
children	8a1a488a1a67

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 WHERE "id" = "issue_id_p";

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

1248 -- issue state has been manipulated

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

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

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

1252 END IF;

1253 RETURN;

1254 END;

1255 $$;

1256

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

1258 RETURNS VOID

1259 LANGUAGE 'plpgsql' VOLATILE AS $$

1260 DECLARE

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

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

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

1264 BEGIN

1265 PERFORM "lock_issue"("issue_id_p");

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

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

1268 DELETE FROM "delegating_voter"

1269 WHERE "issue_id" = "issue_id_p";

1270 DELETE FROM "direct_voter"

1271 WHERE "issue_id" = "issue_id_p"

1272 AND "autoreject" = TRUE;

1273 DELETE FROM "direct_voter"

1274 USING (

1275 SELECT

1276 "direct_voter"."member_id"

1277 FROM "direct_voter"

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

1279 LEFT JOIN "privilege"

1280 ON "privilege"."unit_id" = "unit_id_v"

1281 AND "privilege"."member_id" = "direct_voter"."member_id"

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

1283 "member"."active" = FALSE OR

1284 "privilege"."voting_right" ISNULL OR

1285 "privilege"."voting_right" = FALSE

1286 )

1287 ) AS "subquery"

1288 WHERE "direct_voter"."issue_id" = "issue_id_p"

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

1290 UPDATE "direct_voter" SET "weight" = 1

1291 WHERE "issue_id" = "issue_id_p";

1292 PERFORM "add_vote_delegations"("issue_id_p");

1293 FOR "member_id_v" IN

1294 SELECT "interest"."member_id"

1295 FROM "interest"

1296 JOIN "member"

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

1298 LEFT JOIN "direct_voter"

1299 ON "interest"."member_id" = "direct_voter"."member_id"

1300 AND "interest"."issue_id" = "direct_voter"."issue_id"

1301 LEFT JOIN "delegating_voter"

1302 ON "interest"."member_id" = "delegating_voter"."member_id"

1303 AND "interest"."issue_id" = "delegating_voter"."issue_id"

1304 WHERE "interest"."issue_id" = "issue_id_p"

1305 AND "interest"."autoreject" = TRUE

1306 AND "member"."active"

1307 AND "direct_voter"."member_id" ISNULL

1308 AND "delegating_voter"."member_id" ISNULL

1309 UNION SELECT "membership"."member_id"

1310 FROM "membership"

1311 JOIN "member"

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

1313 LEFT JOIN "interest"

1314 ON "membership"."member_id" = "interest"."member_id"

1315 AND "interest"."issue_id" = "issue_id_p"

1316 LEFT JOIN "direct_voter"

1317 ON "membership"."member_id" = "direct_voter"."member_id"

1318 AND "direct_voter"."issue_id" = "issue_id_p"

1319 LEFT JOIN "delegating_voter"

1320 ON "membership"."member_id" = "delegating_voter"."member_id"

1321 AND "delegating_voter"."issue_id" = "issue_id_p"

1322 WHERE "membership"."area_id" = "area_id_v"

1323 AND "membership"."autoreject" = TRUE

1324 AND "member"."active"

1325 AND "interest"."autoreject" ISNULL

1326 AND "direct_voter"."member_id" ISNULL

1327 AND "delegating_voter"."member_id" ISNULL

1328 LOOP

1329 INSERT INTO "direct_voter"

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

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

1332 INSERT INTO "vote" (

1333 "member_id",

1334 "issue_id",

1335 "initiative_id",

1336 "grade"

1337 ) SELECT

1338 "member_id_v" AS "member_id",

1339 "issue_id_p" AS "issue_id",

1340 "id" AS "initiative_id",

1341 -1 AS "grade"

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

1343 END LOOP;

1344 PERFORM "add_vote_delegations"("issue_id_p");

1345 UPDATE "issue" SET

1346 "state" = 'calculation',

1347 "closed" = now(),

1348 "voter_count" = (

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

1350 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"

1351 )

1352 WHERE "id" = "issue_id_p";

1353 UPDATE "initiative" SET

1354 "positive_votes" = "vote_counts"."positive_votes",

1355 "negative_votes" = "vote_counts"."negative_votes",

1356 "agreed" = CASE WHEN "majority_strict" THEN

1357 "vote_counts"."positive_votes" * "majority_den" >

1358 "majority_num" *

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

1360 ELSE

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

1362 "majority_num" *

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

1364 END

1365 FROM

1366 ( SELECT

1367 "initiative"."id" AS "initiative_id",

1368 coalesce(

1369 sum(

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

1371 ),

1372 0

1373 ) AS "positive_votes",

1374 coalesce(

1375 sum(

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

1377 ),

1378 0

1379 ) AS "negative_votes"

1380 FROM "initiative"

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

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

1383 LEFT JOIN "direct_voter"

1384 ON "direct_voter"."issue_id" = "initiative"."issue_id"

1385 LEFT JOIN "vote"

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

1387 AND "vote"."member_id" = "direct_voter"."member_id"

1388 WHERE "initiative"."issue_id" = "issue_id_p"

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

1390 GROUP BY "initiative"."id"

1391 ) AS "vote_counts",

1392 "issue",

1393 "policy"

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

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

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

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

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

1399 INSERT INTO "battle" (

1400 "issue_id",

1401 "winning_initiative_id", "losing_initiative_id",

1402 "count"

1403 ) SELECT

1404 "issue_id",

1405 "winning_initiative_id", "losing_initiative_id",

1406 "count"

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

1408 END;

1409 $$;

1410

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

1412 RETURNS VOID

1413 LANGUAGE 'plpgsql' VOLATILE AS $$

1414 DECLARE

1415 "dimension_v" INTEGER;

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

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

1418 "i" INTEGER;

1419 "j" INTEGER;

1420 "k" INTEGER;

1421 "battle_row" "battle"%ROWTYPE;

1422 "rank_ary" INT4[];

1423 "rank_v" INT4;

1424 "done_v" INTEGER;

1425 "winners_ary" INTEGER[];

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

1427 BEGIN

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

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

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

1431 IF "dimension_v" = 1 THEN

1432 UPDATE "initiative" SET "rank" = 1

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

1434 ELSIF "dimension_v" > 1 THEN

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

1436 -- comparison:

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

1438 "i" := 1;

1439 "j" := 2;

1440 FOR "battle_row" IN

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

1442 ORDER BY "winning_initiative_id", "losing_initiative_id"

1443 LOOP

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

1445 IF "j" = "dimension_v" THEN

1446 "i" := "i" + 1;

1447 "j" := 1;

1448 ELSE

1449 "j" := "j" + 1;

1450 IF "j" = "i" THEN

1451 "j" := "j" + 1;

1452 END IF;

1453 END IF;

1454 END LOOP;

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

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

1457 END IF;

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

1459 -- function:

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

1461 "i" := 1;

1462 LOOP

1463 "j" := 1;

1464 LOOP

1465 IF "i" != "j" THEN

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

1467 "vote_matrix"["i"]["j"],

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

1469 );

1470 END IF;

1471 EXIT WHEN "j" = "dimension_v";

1472 "j" := "j" + 1;

1473 END LOOP;

1474 EXIT WHEN "i" = "dimension_v";

1475 "i" := "i" + 1;

1476 END LOOP;

1477 -- Find best paths:

1478 "i" := 1;

1479 LOOP

1480 "j" := 1;

1481 LOOP

1482 IF "i" != "j" THEN

1483 "k" := 1;

1484 LOOP

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

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

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

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

1489 END IF;

1490 ELSE

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

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

1493 END IF;

1494 END IF;

1495 END IF;

1496 EXIT WHEN "k" = "dimension_v";

1497 "k" := "k" + 1;

1498 END LOOP;

1499 END IF;

1500 EXIT WHEN "j" = "dimension_v";

1501 "j" := "j" + 1;

1502 END LOOP;

1503 EXIT WHEN "i" = "dimension_v";

1504 "i" := "i" + 1;

1505 END LOOP;

1506 -- Determine order of winners:

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

1508 "rank_v" := 1;

1509 "done_v" := 0;

1510 LOOP

1511 "winners_ary" := '{}';

1512 "i" := 1;

1513 LOOP

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

1515 "j" := 1;

1516 LOOP

1517 IF

1518 "i" != "j" AND

1519 "rank_ary"["j"] ISNULL AND

1520 "matrix"["j"]["i"] > "matrix"["i"]["j"]

1521 THEN

1522 -- someone else is better

1523 EXIT;

1524 END IF;

1525 IF "j" = "dimension_v" THEN

1526 -- noone is better

1527 "winners_ary" := "winners_ary" || "i";

1528 EXIT;

1529 END IF;

1530 "j" := "j" + 1;

1531 END LOOP;

1532 END IF;

1533 EXIT WHEN "i" = "dimension_v";

1534 "i" := "i" + 1;

1535 END LOOP;

1536 "i" := 1;

1537 LOOP

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

1539 "done_v" := "done_v" + 1;

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

1541 "i" := "i" + 1;

1542 END LOOP;

1543 EXIT WHEN "done_v" = "dimension_v";

1544 "rank_v" := "rank_v" + 1;

1545 END LOOP;

1546 -- write preliminary ranks:

1547 "i" := 1;

1548 FOR "initiative_id_v" IN

1549 SELECT "id" FROM "initiative"

1550 WHERE "issue_id" = "issue_id_p" AND "agreed"

1551 ORDER BY "id"

1552 LOOP

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

1554 WHERE "id" = "initiative_id_v";

1555 "i" := "i" + 1;

1556 END LOOP;

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

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

1559 END IF;

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

1561 "rank_v" := 1;

1562 FOR "initiative_id_v" IN

1563 SELECT "id" FROM "initiative"

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

1565 ORDER BY

1566 "rank",

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

1568 "id"

1569 LOOP

1570 UPDATE "initiative" SET "rank" = "rank_v"

1571 WHERE "id" = "initiative_id_v";

1572 "rank_v" := "rank_v" + 1;

1573 END LOOP;

1574 END IF;

1575 -- mark issue as finished

1576 UPDATE "issue" SET

1577 "state" =

1578 CASE WHEN NOT EXISTS (

1579 SELECT NULL FROM "initiative"

1580 WHERE "issue_id" = "issue_id_p" AND "admitted"

1581 ) THEN

1582 'canceled_no_initiative_admitted'::"issue_state"

1583 ELSE

1584 CASE WHEN "dimension_v" = 0 THEN

1585 'finished_without_winner'::"issue_state"

1586 ELSE

1587 'finished_with_winner'::"issue_state"

1588 END

1589 END,

1590 "ranks_available" = TRUE

1591 WHERE "id" = "issue_id_p";

1592 RETURN;

1593 END;

1594 $$;

1595

1596 CREATE OR REPLACE FUNCTION "check_issue"

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

1598 RETURNS VOID

1599 LANGUAGE 'plpgsql' VOLATILE AS $$

1600 DECLARE

1601 "issue_row" "issue"%ROWTYPE;

1602 "policy_row" "policy"%ROWTYPE;

1603 "voting_requested_v" BOOLEAN;

1604 BEGIN

1605 PERFORM "lock_issue"("issue_id_p");

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

1607 -- only process open issues:

1608 IF "issue_row"."closed" ISNULL THEN

1609 SELECT * INTO "policy_row" FROM "policy"

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

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

1612 IF "issue_row"."fully_frozen" ISNULL THEN

1613 PERFORM "create_snapshot"("issue_id_p");

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

1615 END IF;

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

1617 IF "issue_row"."accepted" ISNULL THEN

1618 IF EXISTS (

1619 SELECT NULL FROM "initiative"

1620 WHERE "issue_id" = "issue_id_p"

1621 AND "supporter_count" > 0

1622 AND "supporter_count" * "policy_row"."issue_quorum_den"

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

1624 ) THEN

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

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

1627 -- NOTE: "issue_row" used later

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

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

1630 UPDATE "issue" SET

1631 "state" = "issue_row"."state",

1632 "accepted" = "issue_row"."accepted"

1633 WHERE "id" = "issue_row"."id";

1634 ELSIF

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

1636 THEN

1637 -- close issues, if admission time has expired

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

1639 UPDATE "issue" SET

1640 "state" = 'canceled_issue_not_accepted',

1641 "closed" = now()

1642 WHERE "id" = "issue_row"."id";

1643 END IF;

1644 END IF;

1645 -- eventually half freeze issues:

1646 IF

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

1648 "issue_row"."accepted" NOTNULL AND

1649 "issue_row"."half_frozen" ISNULL

1650 THEN

1651 SELECT

1652 CASE

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

1654 TRUE

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

1656 FALSE

1657 ELSE NULL

1658 END

1659 INTO "voting_requested_v"

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

1661 IF

1662 "voting_requested_v" OR (

1663 "voting_requested_v" ISNULL AND

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

1665 )

1666 THEN

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

1668 -- NOTE: "issue_row" used later

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

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

1671 UPDATE "issue" SET

1672 "state" = "issue_row"."state",

1673 "half_frozen" = "issue_row"."half_frozen"

1674 WHERE "id" = "issue_row"."id";

1675 END IF;

1676 END IF;

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

1678 IF

1679 "issue_row"."closed" ISNULL AND

1680 NOT EXISTS (

1681 -- all initiatives are revoked

1682 SELECT NULL FROM "initiative"

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

1684 ) AND (

1685 -- and issue has not been accepted yet

1686 "issue_row"."accepted" ISNULL OR

1687 NOT EXISTS (

1688 -- or no initiatives have been revoked lately

1689 SELECT NULL FROM "initiative"

1690 WHERE "issue_id" = "issue_id_p"

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

1692 ) OR (

1693 -- or verification time has elapsed

1694 "issue_row"."half_frozen" NOTNULL AND

1695 "issue_row"."fully_frozen" ISNULL AND

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

1697 )

1698 )

1699 THEN

1700 -- NOTE: "issue_row" used later

1701 IF "issue_row"."accepted" ISNULL THEN

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

1703 ELSIF "issue_row"."half_frozen" ISNULL THEN

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

1705 ELSE

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

1707 END IF;

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

1709 UPDATE "issue" SET

1710 "state" = "issue_row"."state",

1711 "closed" = "issue_row"."closed"

1712 WHERE "id" = "issue_row"."id";

1713 END IF;

1714 -- fully freeze issue after verification time:

1715 IF

1716 "issue_row"."half_frozen" NOTNULL AND

1717 "issue_row"."fully_frozen" ISNULL AND

1718 "issue_row"."closed" ISNULL AND

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

1720 THEN

1721 PERFORM "freeze_after_snapshot"("issue_id_p");

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

1723 END IF;

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

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

1726 IF

1727 "issue_row"."closed" ISNULL AND

1728 "issue_row"."fully_frozen" NOTNULL AND

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

1730 THEN

1731 PERFORM "close_voting"("issue_id_p");

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

1733 PERFORM "calculate_ranks"("issue_id_p");

1734 END IF;

1735 END IF;

1736 RETURN;

1737 END;

1738 $$;

1739

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

1741 RETURNS VOID

1742 LANGUAGE 'plpgsql' VOLATILE AS $$

1743 DECLARE

1744 "issue_row" "issue"%ROWTYPE;

1745 BEGIN

1746 SELECT * INTO "issue_row"

1747 FROM "issue" WHERE "id" = "issue_id_p"

1748 FOR UPDATE;

1749 IF "issue_row"."cleaned" ISNULL THEN

1750 UPDATE "issue" SET

1751 "closed" = NULL,

1752 "ranks_available" = FALSE

1753 WHERE "id" = "issue_id_p";

1754 DELETE FROM "delegating_voter"

1755 WHERE "issue_id" = "issue_id_p";

1756 DELETE FROM "direct_voter"

1757 WHERE "issue_id" = "issue_id_p";

1758 DELETE FROM "delegating_interest_snapshot"

1759 WHERE "issue_id" = "issue_id_p";

1760 DELETE FROM "direct_interest_snapshot"

1761 WHERE "issue_id" = "issue_id_p";

1762 DELETE FROM "delegating_population_snapshot"

1763 WHERE "issue_id" = "issue_id_p";

1764 DELETE FROM "direct_population_snapshot"

1765 WHERE "issue_id" = "issue_id_p";

1766 DELETE FROM "non_voter"

1767 WHERE "issue_id" = "issue_id_p";

1768 DELETE FROM "delegation"

1769 WHERE "issue_id" = "issue_id_p";

1770 DELETE FROM "supporter"

1771 WHERE "issue_id" = "issue_id_p";

1772 UPDATE "issue" SET

1773 "closed" = "issue_row"."closed",

1774 "ranks_available" = "issue_row"."ranks_available",

1775 "cleaned" = now()

1776 WHERE "id" = "issue_id_p";

1777 END IF;

1778 RETURN;

1779 END;

1780 $$;

1781

1782 CREATE OR REPLACE FUNCTION "check_issue"

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

1784 RETURNS VOID

1785 LANGUAGE 'plpgsql' VOLATILE AS $$

1786 DECLARE

1787 "issue_row" "issue"%ROWTYPE;

1788 "policy_row" "policy"%ROWTYPE;

1789 "voting_requested_v" BOOLEAN;

1790 BEGIN

1791 PERFORM "lock_issue"("issue_id_p");

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

1793 -- only process open issues:

1794 IF "issue_row"."closed" ISNULL THEN

1795 SELECT * INTO "policy_row" FROM "policy"

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

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

1798 IF "issue_row"."fully_frozen" ISNULL THEN

1799 PERFORM "create_snapshot"("issue_id_p");

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

1801 END IF;

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

1803 IF "issue_row"."accepted" ISNULL THEN

1804 IF EXISTS (

1805 SELECT NULL FROM "initiative"

1806 WHERE "issue_id" = "issue_id_p"

1807 AND "supporter_count" > 0

1808 AND "supporter_count" * "policy_row"."issue_quorum_den"

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

1810 ) THEN

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

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

1813 -- NOTE: "issue_row" used later

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

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

1816 UPDATE "issue" SET

1817 "state" = "issue_row"."state",

1818 "accepted" = "issue_row"."accepted"

1819 WHERE "id" = "issue_row"."id";

1820 ELSIF

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

1822 THEN

1823 -- close issues, if admission time has expired

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

1825 UPDATE "issue" SET

1826 "state" = 'canceled_issue_not_accepted',

1827 "closed" = now()

1828 WHERE "id" = "issue_row"."id";

1829 END IF;

1830 END IF;

1831 -- eventually half freeze issues:

1832 IF

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

1834 "issue_row"."accepted" NOTNULL AND

1835 "issue_row"."half_frozen" ISNULL

1836 THEN

1837 SELECT

1838 CASE

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

1840 TRUE

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

1842 FALSE

1843 ELSE NULL

1844 END

1845 INTO "voting_requested_v"

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

1847 IF

1848 "voting_requested_v" OR (

1849 "voting_requested_v" ISNULL AND

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

1851 )

1852 THEN

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

1854 -- NOTE: "issue_row" used later

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

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

1857 UPDATE "issue" SET

1858 "state" = "issue_row"."state",

1859 "half_frozen" = "issue_row"."half_frozen"

1860 WHERE "id" = "issue_row"."id";

1861 END IF;

1862 END IF;

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

1864 IF

1865 "issue_row"."closed" ISNULL AND

1866 NOT EXISTS (

1867 -- all initiatives are revoked

1868 SELECT NULL FROM "initiative"

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

1870 ) AND (

1871 -- and issue has not been accepted yet

1872 "issue_row"."accepted" ISNULL OR

1873 NOT EXISTS (

1874 -- or no initiatives have been revoked lately

1875 SELECT NULL FROM "initiative"

1876 WHERE "issue_id" = "issue_id_p"

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

1878 ) OR (

1879 -- or verification time has elapsed

1880 "issue_row"."half_frozen" NOTNULL AND

1881 "issue_row"."fully_frozen" ISNULL AND

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

1883 )

1884 )

1885 THEN

1886 -- NOTE: "issue_row" used later

1887 IF "issue_row"."accepted" ISNULL THEN

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

1889 ELSIF "issue_row"."half_frozen" ISNULL THEN

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

1891 ELSE

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

1893 END IF;

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

1895 UPDATE "issue" SET

1896 "state" = "issue_row"."state",

1897 "closed" = "issue_row"."closed"

1898 WHERE "id" = "issue_row"."id";

1899 END IF;

1900 -- fully freeze issue after verification time:

1901 IF

1902 "issue_row"."half_frozen" NOTNULL AND

1903 "issue_row"."fully_frozen" ISNULL AND

1904 "issue_row"."closed" ISNULL AND

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

1906 THEN

1907 PERFORM "freeze_after_snapshot"("issue_id_p");

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

1909 END IF;

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

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

1912 IF

1913 "issue_row"."closed" ISNULL AND

1914 "issue_row"."fully_frozen" NOTNULL AND

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

1916 THEN

1917 PERFORM "close_voting"("issue_id_p");

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

1919 PERFORM "calculate_ranks"("issue_id_p");

1920 END IF;

1921 END IF;

1922 RETURN;

1923 END;

1924 $$;

1925

1926 CREATE OR REPLACE FUNCTION "delete_private_data"()

1927 RETURNS VOID

1928 LANGUAGE 'plpgsql' VOLATILE AS $$

1929 BEGIN

1930 UPDATE "member" SET

1931 "last_login" = NULL,

1932 "login" = NULL,

1933 "password" = NULL,

1934 "notify_email" = NULL,

1935 "notify_email_unconfirmed" = NULL,

1936 "notify_email_secret" = NULL,

1937 "notify_email_secret_expiry" = NULL,

1938 "notify_email_lock_expiry" = NULL,

1939 "password_reset_secret" = NULL,

1940 "password_reset_secret_expiry" = NULL,

1941 "organizational_unit" = NULL,

1942 "internal_posts" = NULL,

1943 "realname" = NULL,

1944 "birthday" = NULL,

1945 "address" = NULL,

1946 "email" = NULL,

1947 "xmpp_address" = NULL,

1948 "website" = NULL,

1949 "phone" = NULL,

1950 "mobile_phone" = NULL,

1951 "profession" = NULL,

1952 "external_memberships" = NULL,

1953 "external_posts" = NULL,

1954 "statement" = NULL;

1955 -- "text_search_data" is updated by triggers

1956 DELETE FROM "invite_code";

1957 DELETE FROM "setting";

1958 DELETE FROM "setting_map";

1959 DELETE FROM "member_relation_setting";

1960 DELETE FROM "member_image";

1961 DELETE FROM "contact";

1962 DELETE FROM "ignored_member";

1963 DELETE FROM "session";

1964 DELETE FROM "area_setting";

1965 DELETE FROM "issue_setting";

1966 DELETE FROM "ignored_initiative";

1967 DELETE FROM "initiative_setting";

1968 DELETE FROM "suggestion_setting";

1969 DELETE FROM "non_voter";

1970 DELETE FROM "direct_voter" USING "issue"

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

1972 AND "issue"."closed" ISNULL;

1973 RETURN;

1974 END;

1975 $$;

1976

1977

1978 -- Delete old "delegation_scope" TYPE:

1979

1980 DROP TYPE "delegation_scope_old";

1981

1982

1983 COMMIT;

1984

1985

1986 -- Generate issue states:

1987

1988 UPDATE "issue" SET "state" =

1989 CASE

1990 WHEN "closed" ISNULL THEN

1991 CASE

1992 WHEN "accepted" ISNULL THEN

1993 'admission'::"issue_state"

1994 WHEN "half_frozen" ISNULL THEN

1995 'discussion'::"issue_state"

1996 WHEN "fully_frozen" ISNULL THEN

1997 'verification'::"issue_state"

1998 ELSE

1999 'voting'::"issue_state"

2000 END

2001 WHEN "fully_frozen" NOTNULL THEN

2002 CASE

2003 WHEN "fully_frozen" = "closed" THEN

2004 'canceled_no_initiative_admitted'::"issue_state"

2005 ELSE

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

2007 END

2008 WHEN "half_frozen" NOTNULL THEN

2009 'canceled_after_revocation_during_verification'::"issue_state"

2010 WHEN "accepted" NOTNULL THEN

2011 'canceled_after_revocation_during_discussion'::"issue_state"

2012 ELSE

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

2014 END;

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

2016 FROM "initiative"

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

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

2019 AND "initiative"."agreed";

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

2021 FROM "initiative"

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

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

2024 AND "initiative"."revoked" ISNULL;

2025

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

2027

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

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

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

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

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

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

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

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

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

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

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

2039 ) AND (

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

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

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

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

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

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

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

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

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

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

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

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

2052 ));

2053

2054

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

2056

2057 UPDATE "initiative" SET "revoked_by_member_id" = "author_id"

2058 FROM "current_draft"

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

2060 AND "initiative"."revoked" NOTNULL;

2061

2062 ALTER TABLE "initiative" ADD

2063 CONSTRAINT "all_or_none_of_revoked_and_revoked_by_member_id_must_be_null"

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

2065

2066

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

2068

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

2070

2071 ALTER TABLE "delegation" ADD CONSTRAINT "area_id_and_issue_id_set_according_to_scope"

2072 CHECK (

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

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

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

2076

2077

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

2079

2080 DELETE FROM "event";

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

2082

2083 INSERT INTO "event"

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

2085 "initiative_id", "draft_id", "suggestion_id" )

2086 SELECT * FROM (

2087 SELECT * FROM (

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

2089 "timeline"."occurrence",

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

2091 'initiative_created_in_new_issue'::"event_type"

2092 ELSE

2093 'initiative_created_in_existing_issue'::"event_type"

2094 END,

2095 "draft"."author_id",

2096 "issue"."id",

2097 CASE

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

2099 'admission'::"issue_state"

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

2101 'discussion'::"issue_state"

2102 ELSE

2103 'verification'::"issue_state"

2104 END,

2105 "initiative"."id",

2106 "draft"."id",

2107 NULL::INT8

2108 FROM "timeline"

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

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

2111 LEFT JOIN "timeline" AS "issue_creation"

2112 ON "initiative"."issue_id" = "issue_creation"."issue_id"

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

2114 AND "timeline"."occurrence" = "issue_creation"."occurrence"

2115 JOIN "draft"

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

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

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

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

2120 UNION ALL

2121 SELECT

2122 "timeline"."occurrence",

2123 'issue_state_changed'::"event_type",

2124 NULL,

2125 "issue"."id",

2126 CASE

2127 WHEN "timeline"."event" IN (

2128 'issue_canceled',

2129 'issue_finished_without_voting',

2130 'issue_finished_after_voting'

2131 ) THEN

2132 "issue"."state"

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

2134 'discussion'::"issue_state"

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

2136 'verification'::"issue_state"

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

2138 'voting'::"issue_state"

2139 END,

2140 NULL,

2141 NULL,

2142 NULL

2143 FROM "timeline"

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

2145 WHERE "timeline"."event" IN (

2146 'issue_canceled',

2147 'issue_accepted',

2148 'issue_half_frozen',

2149 'issue_finished_without_voting',

2150 'issue_voting_started',

2151 'issue_finished_after_voting' )

2152 UNION ALL

2153 SELECT

2154 "timeline"."occurrence",

2155 'initiative_revoked'::"event_type",

2156 "initiative"."revoked_by_member_id",

2157 "issue"."id",

2158 CASE

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

2160 'admission'::"issue_state"

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

2162 'discussion'::"issue_state"

2163 ELSE

2164 'verification'::"issue_state"

2165 END,

2166 "initiative"."id",

2167 "current_draft"."id",

2168 NULL

2169 FROM "timeline"

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

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

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

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

2174 UNION ALL

2175 SELECT

2176 "timeline"."occurrence",

2177 'new_draft_created'::"event_type",

2178 "draft"."author_id",

2179 "issue"."id",

2180 CASE

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

2182 'admission'::"issue_state"

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

2184 'discussion'::"issue_state"

2185 ELSE

2186 'verification'::"issue_state"

2187 END,

2188 "initiative"."id",

2189 "draft"."id",

2190 NULL

2191 FROM "timeline"

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

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

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

2195 LEFT JOIN "timeline" AS "initiative_creation"

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

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

2198 AND "timeline"."occurrence" = "initiative_creation"."occurrence"

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

2200 AND "initiative_creation"."initiative_id" ISNULL

2201 UNION ALL

2202 SELECT

2203 "timeline"."occurrence",

2204 'suggestion_created'::"event_type",

2205 "suggestion"."author_id",

2206 "issue"."id",

2207 CASE

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

2209 'admission'::"issue_state"

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

2211 'discussion'::"issue_state"

2212 ELSE

2213 'verification'::"issue_state"

2214 END,

2215 "initiative"."id",

2216 NULL,

2217 "suggestion"."id"

2218 FROM "timeline"

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

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

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

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

2223 ) AS "subquery"

2224 ORDER BY "occurrence";

2225

2226