liquid_feedback_core: fc1a491eb916 update/core-update.v1.3.1-v1.4.0.sql

liquid_feedback_core

view update/core-update.v1.3.1-v1.4.0.sql @ 381:fc1a491eb916

Added tag v2.2.1 for changeset 0f5841ef67c6

author	jbe
date	Mon Mar 18 14:06:40 2013 +0100 (2013-03-18)
parents	eee75cff3e5a
children

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', 1, 4, 0))

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" DEFAULT NULL;

105 ALTER TABLE "issue" ALTER "state" SET DEFAULT 'admission';

106

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

108

109

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

111

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

113

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

115

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

117

118

119 -- New table "ignored_initiative":

120

121 CREATE TABLE "ignored_initiative" (

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

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

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

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

126

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

128

129

130 -- New table "invite_code_unit":

131

132 CREATE TABLE "invite_code_unit" (

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

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

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

136

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

138

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

140 SELECT "id" AS "invite_code_id", 1 AS "unit_id" FROM "invite_code";

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 INSERT INTO "privilege" ("unit_id", "member_id", "voting_right")

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

165 FROM "member";

166

167

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

169

170 DROP TABLE "ignored_issue";

171

172

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

174

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

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

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

178

179

180 -- Delete views and functions being dependent on type "delegation_scope":

181

182 DROP FUNCTION "delegation_chain"

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

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

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

186

187 DROP FUNCTION "delegation_chain"

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

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

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

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

192

193 DROP TYPE "delegation_chain_row";

194

195 DROP VIEW "issue_delegation";

196 DROP VIEW "area_delegation";

197 DROP VIEW "global_delegation";

198 DROP VIEW "active_delegation";

199

200

201 -- Modify "delegation" table to use new "delegation_scope" type:

202

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

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

205

206 DROP INDEX "delegation_global_truster_id_unique_idx";

207

208 ALTER TABLE "delegation" ALTER "scope" TYPE "delegation_scope"

209 USING CASE WHEN "scope" = 'global'

210 THEN 'unit'::"delegation_scope"

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

212

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

214

215 ALTER TABLE "delegation" ADD CONSTRAINT "no_unit_delegation_to_null"

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

217

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

219

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

221

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

223

224

225 -- Modify snapshot tables to use new "delegation_scope" type:

226

227 ALTER TABLE "delegating_population_snapshot" ALTER "scope" TYPE "delegation_scope"

228 USING CASE WHEN "scope" = 'global'

229 THEN 'unit'::"delegation_scope"

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

231

232 ALTER TABLE "delegating_interest_snapshot" ALTER "scope" TYPE "delegation_scope"

233 USING CASE WHEN "scope" = 'global'

234 THEN 'unit'::"delegation_scope"

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

236

237 ALTER TABLE "delegating_voter" ALTER "scope" TYPE "delegation_scope"

238 USING CASE WHEN "scope" = 'global'

239 THEN 'unit'::"delegation_scope"

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

241

242

243 -- New table "non_voter":

244

245 CREATE TABLE "non_voter" (

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

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

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

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

250

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

252

253

254 -- New tables "issue_comment" and "rendered_issue_comment":

255

256 CREATE TABLE "issue_comment" (

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

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

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

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

261 "formatting_engine" TEXT,

262 "content" TEXT NOT NULL,

263 "text_search_data" TSVECTOR );

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

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

266 CREATE TRIGGER "update_text_search_data"

267 BEFORE INSERT OR UPDATE ON "issue_comment"

268 FOR EACH ROW EXECUTE PROCEDURE

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

270

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

272

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

274

275 CREATE TABLE "rendered_issue_comment" (

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

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

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

279 ON DELETE CASCADE ON UPDATE CASCADE,

280 "issue_id" INT4,

281 "member_id" INT4,

282 "format" TEXT,

283 "content" TEXT NOT NULL );

284

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

286

287

288 -- New tables "voting_comment" and "rendered_voting_comment":

289

290 CREATE TABLE "voting_comment" (

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

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

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

294 "changed" TIMESTAMPTZ,

295 "formatting_engine" TEXT,

296 "content" TEXT NOT NULL,

297 "text_search_data" TSVECTOR );

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

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

300 CREATE TRIGGER "update_text_search_data"

301 BEFORE INSERT OR UPDATE ON "voting_comment"

302 FOR EACH ROW EXECUTE PROCEDURE

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

304

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

306

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

308

309 CREATE TABLE "rendered_voting_comment" (

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

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

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

313 ON DELETE CASCADE ON UPDATE CASCADE,

314 "issue_id" INT4,

315 "member_id" INT4,

316 "format" TEXT,

317 "content" TEXT NOT NULL );

318

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

320

321

322 -- New table "event":

323

324 CREATE TYPE "event_type" AS ENUM (

325 'issue_state_changed',

326 'initiative_created_in_new_issue',

327 'initiative_created_in_existing_issue',

328 'initiative_revoked',

329 'new_draft_created',

330 'suggestion_created');

331

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

333

334 CREATE TABLE "event" (

335 "id" SERIAL8 PRIMARY KEY,

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

337 "event" "event_type" NOT NULL,

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

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

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

341 "initiative_id" INT4,

342 "draft_id" INT8,

343 "suggestion_id" INT8,

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

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

346 ON DELETE CASCADE ON UPDATE CASCADE,

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

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

349 ON DELETE CASCADE ON UPDATE CASCADE,

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

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

352 ON DELETE CASCADE ON UPDATE CASCADE,

353 CONSTRAINT "null_constraints_for_issue_state_changed" CHECK (

354 "event" != 'issue_state_changed' OR (

355 "member_id" ISNULL AND

356 "issue_id" NOTNULL AND

357 "state" NOTNULL AND

358 "initiative_id" ISNULL AND

359 "draft_id" ISNULL AND

360 "suggestion_id" ISNULL )),

361 CONSTRAINT "null_constraints_for_initiative_creation_or_revocation_or_new_draft" CHECK (

362 "event" NOT IN (

363 'initiative_created_in_new_issue',

364 'initiative_created_in_existing_issue',

365 'initiative_revoked',

366 'new_draft_created'

367 ) OR (

368 "member_id" NOTNULL AND

369 "issue_id" NOTNULL AND

370 "state" NOTNULL AND

371 "initiative_id" NOTNULL AND

372 "draft_id" NOTNULL AND

373 "suggestion_id" ISNULL )),

374 CONSTRAINT "null_constraints_for_suggestion_creation" CHECK (

375 "event" != 'suggestion_created' OR (

376 "member_id" NOTNULL AND

377 "issue_id" NOTNULL AND

378 "state" NOTNULL AND

379 "initiative_id" NOTNULL AND

380 "draft_id" ISNULL AND

381 "suggestion_id" NOTNULL )) );

382

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

384

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

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

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

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

389

390

391 -- Triggers to fill "event" table:

392

393 CREATE FUNCTION "write_event_issue_state_changed_trigger"()

394 RETURNS TRIGGER

395 LANGUAGE 'plpgsql' VOLATILE AS $$

396 BEGIN

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

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

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

400 END IF;

401 RETURN NULL;

402 END;

403 $$;

404

405 CREATE TRIGGER "write_event_issue_state_changed"

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

407 "write_event_issue_state_changed_trigger"();

408

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

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

411

412 CREATE FUNCTION "write_event_initiative_or_draft_created_trigger"()

413 RETURNS TRIGGER

414 LANGUAGE 'plpgsql' VOLATILE AS $$

415 DECLARE

416 "initiative_row" "initiative"%ROWTYPE;

417 "issue_row" "issue"%ROWTYPE;

418 "event_v" "event_type";

419 BEGIN

420 SELECT * INTO "initiative_row" FROM "initiative"

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

422 SELECT * INTO "issue_row" FROM "issue"

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

424 IF EXISTS (

425 SELECT NULL FROM "draft"

426 WHERE "initiative_id" = NEW."initiative_id"

427 AND "id" != NEW."id"

428 ) THEN

429 "event_v" := 'new_draft_created';

430 ELSE

431 IF EXISTS (

432 SELECT NULL FROM "initiative"

433 WHERE "issue_id" = "initiative_row"."issue_id"

434 AND "id" != "initiative_row"."id"

435 ) THEN

436 "event_v" := 'initiative_created_in_existing_issue';

437 ELSE

438 "event_v" := 'initiative_created_in_new_issue';

439 END IF;

440 END IF;

441 INSERT INTO "event" (

442 "event", "member_id",

443 "issue_id", "state", "initiative_id", "draft_id"

444 ) VALUES (

445 "event_v",

446 NEW."author_id",

447 "initiative_row"."issue_id",

448 "issue_row"."state",

449 "initiative_row"."id",

450 NEW."id" );

451 RETURN NULL;

452 END;

453 $$;

454

455 CREATE TRIGGER "write_event_initiative_or_draft_created"

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

457 "write_event_initiative_or_draft_created_trigger"();

458

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

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

461

462 CREATE FUNCTION "write_event_initiative_revoked_trigger"()

463 RETURNS TRIGGER

464 LANGUAGE 'plpgsql' VOLATILE AS $$

465 DECLARE

466 "issue_row" "issue"%ROWTYPE;

467 BEGIN

468 SELECT * INTO "issue_row" FROM "issue"

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

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

471 INSERT INTO "event" (

472 "event", "member_id", "issue_id", "state", "initiative_id"

473 ) VALUES (

474 'initiative_revoked',

475 NEW."revoked_by_member_id",

476 NEW."issue_id",

477 "issue_row"."state",

478 NEW."id" );

479 END IF;

480 RETURN NULL;

481 END;

482 $$;

483

484 CREATE TRIGGER "write_event_initiative_revoked"

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

486 "write_event_initiative_revoked_trigger"();

487

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

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

490

491 CREATE FUNCTION "write_event_suggestion_created_trigger"()

492 RETURNS TRIGGER

493 LANGUAGE 'plpgsql' VOLATILE AS $$

494 DECLARE

495 "initiative_row" "initiative"%ROWTYPE;

496 "issue_row" "issue"%ROWTYPE;

497 BEGIN

498 SELECT * INTO "initiative_row" FROM "initiative"

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

500 SELECT * INTO "issue_row" FROM "issue"

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

502 INSERT INTO "event" (

503 "event", "member_id",

504 "issue_id", "state", "initiative_id", "suggestion_id"

505 ) VALUES (

506 'suggestion_created',

507 NEW."author_id",

508 "initiative_row"."issue_id",

509 "issue_row"."state",

510 "initiative_row"."id",

511 NEW."id" );

512 RETURN NULL;

513 END;

514 $$;

515

516 CREATE TRIGGER "write_event_suggestion_created"

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

518 "write_event_suggestion_created_trigger"();

519

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

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

522

523

524 -- Modified views:

525

526 CREATE VIEW "unit_delegation" AS

527 SELECT

528 "unit"."id" AS "unit_id",

529 "delegation"."id",

530 "delegation"."truster_id",

531 "delegation"."trustee_id",

532 "delegation"."scope"

533 FROM "unit"

534 JOIN "delegation"

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

536 JOIN "member"

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

538 JOIN "privilege"

539 ON "delegation"."unit_id" = "privilege"."unit_id"

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

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

542

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

544

545 CREATE VIEW "area_delegation" AS

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

547 "area"."id" AS "area_id",

548 "delegation"."id",

549 "delegation"."truster_id",

550 "delegation"."trustee_id",

551 "delegation"."scope"

552 FROM "area"

553 JOIN "delegation"

554 ON "delegation"."unit_id" = "area"."unit_id"

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

556 JOIN "member"

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

558 JOIN "privilege"

559 ON "area"."unit_id" = "privilege"."unit_id"

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

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

562 ORDER BY

563 "area"."id",

564 "delegation"."truster_id",

565 "delegation"."scope" DESC;

566

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

568

569 CREATE VIEW "issue_delegation" AS

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

571 "issue"."id" AS "issue_id",

572 "delegation"."id",

573 "delegation"."truster_id",

574 "delegation"."trustee_id",

575 "delegation"."scope"

576 FROM "issue"

577 JOIN "area"

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

579 JOIN "delegation"

580 ON "delegation"."unit_id" = "area"."unit_id"

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

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

583 JOIN "member"

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

585 JOIN "privilege"

586 ON "area"."unit_id" = "privilege"."unit_id"

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

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

589 ORDER BY

590 "issue"."id",

591 "delegation"."truster_id",

592 "delegation"."scope" DESC;

593

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

595

596 CREATE VIEW "unit_member_count" AS

597 SELECT

598 "unit"."id" AS "unit_id",

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

600 FROM "unit"

601 LEFT JOIN "privilege"

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

603 AND "privilege"."voting_right"

604 LEFT JOIN "member"

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

606 AND "member"."active"

607 GROUP BY "unit"."id";

608

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

610

611 DROP VIEW "area_member_count";

612 CREATE VIEW "area_member_count" AS

613 SELECT

614 "area"."id" AS "area_id",

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

616 coalesce(

617 sum(

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

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

620 ELSE 0 END

621 )

622 ) AS "member_weight",

623 coalesce(

624 sum(

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

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

627 ELSE 0 END

628 )

629 ) AS "autoreject_weight"

630 FROM "area"

631 LEFT JOIN "membership"

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

633 LEFT JOIN "privilege"

634 ON "privilege"."unit_id" = "area"."unit_id"

635 AND "privilege"."member_id" = "membership"."member_id"

636 AND "privilege"."voting_right"

637 LEFT JOIN "member"

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

639 AND "member"."active"

640 GROUP BY "area"."id";

641

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

643

644

645 -- New view "event_seen_by_member":

646

647 CREATE VIEW "event_seen_by_member" AS

648 SELECT

649 "member"."id" AS "seen_by_member_id",

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

651 'voting',

652 'finished_without_winner',

653 'finished_with_winner'

654 ) THEN

655 'voting'::"notify_level"

656 ELSE

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

658 'verification',

659 'canceled_after_revocation_during_verification',

660 'canceled_no_initiative_admitted'

661 ) THEN

662 'verification'::"notify_level"

663 ELSE

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

665 'discussion',

666 'canceled_after_revocation_during_discussion'

667 ) THEN

668 'discussion'::"notify_level"

669 ELSE

670 'all'::"notify_level"

671 END

672 END

673 END AS "notify_level",

674 "event".*

675 FROM "member" CROSS JOIN "event"

676 LEFT JOIN "issue"

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

678 LEFT JOIN "membership"

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

680 AND "issue"."area_id" = "membership"."area_id"

681 LEFT JOIN "interest"

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

683 AND "event"."issue_id" = "interest"."issue_id"

684 LEFT JOIN "supporter"

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

686 AND "event"."initiative_id" = "supporter"."initiative_id"

687 LEFT JOIN "ignored_member"

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

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

690 LEFT JOIN "ignored_initiative"

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

692 AND "event"."initiative_id" = "ignored_initiative"."initiative_id"

693 WHERE (

694 "supporter"."member_id" NOTNULL OR

695 "interest"."member_id" NOTNULL OR

696 ( "membership"."member_id" NOTNULL AND

697 "event"."event" IN (

698 'issue_state_changed',

699 'initiative_created_in_new_issue',

700 'initiative_created_in_existing_issue',

701 'initiative_revoked' ) ) )

702 AND "ignored_member"."member_id" ISNULL

703 AND "ignored_initiative"."member_id" ISNULL;

704

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

706

707

708 -- New view "pending_notification":

709

710 CREATE VIEW "pending_notification" AS

711 SELECT

712 "member"."id" AS "seen_by_member_id",

713 "event".*

714 FROM "member" CROSS JOIN "event"

715 LEFT JOIN "issue"

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

717 LEFT JOIN "membership"

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

719 AND "issue"."area_id" = "membership"."area_id"

720 LEFT JOIN "interest"

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

722 AND "event"."issue_id" = "interest"."issue_id"

723 LEFT JOIN "supporter"

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

725 AND "event"."initiative_id" = "supporter"."initiative_id"

726 LEFT JOIN "ignored_member"

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

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

729 LEFT JOIN "ignored_initiative"

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

731 AND "event"."initiative_id" = "ignored_initiative"."initiative_id"

732 WHERE (

733 "member"."notify_event_id" ISNULL OR

734 ( "member"."notify_event_id" NOTNULL AND

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

736 AND (

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

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

739 "event"."state" IN (

740 'voting',

741 'finished_without_winner',

742 'finished_with_winner' ) ) OR

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

744 "event"."state" IN (

745 'verification',

746 'canceled_after_revocation_during_verification',

747 'canceled_no_initiative_admitted' ) ) OR

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

749 "event"."state" IN (

750 'discussion',

751 'canceled_after_revocation_during_discussion' ) ) )

752 AND (

753 "supporter"."member_id" NOTNULL OR

754 "interest"."member_id" NOTNULL OR

755 ( "membership"."member_id" NOTNULL AND

756 "event"."event" IN (

757 'issue_state_changed',

758 'initiative_created_in_new_issue',

759 'initiative_created_in_existing_issue',

760 'initiative_revoked' ) ) )

761 AND "ignored_member"."member_id" ISNULL

762 AND "ignored_initiative"."member_id" ISNULL;

763

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

765

766

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

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

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

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

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

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

773

774

775 -- Modified "delegation_chain" functions:

776

777 CREATE TYPE "delegation_chain_row" AS (

778 "index" INT4,

779 "member_id" INT4,

780 "member_valid" BOOLEAN,

781 "participation" BOOLEAN,

782 "overridden" BOOLEAN,

783 "scope_in" "delegation_scope",

784 "scope_out" "delegation_scope",

785 "disabled_out" BOOLEAN,

786 "loop" "delegation_chain_loop_tag" );

787

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

789

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

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

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

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

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

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

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

797

798

799 CREATE FUNCTION "delegation_chain"

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

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

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

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

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

805 RETURNS SETOF "delegation_chain_row"

806 LANGUAGE 'plpgsql' STABLE AS $$

807 DECLARE

808 "scope_v" "delegation_scope";

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

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

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

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

813 "output_row" "delegation_chain_row";

814 "output_rows" "delegation_chain_row"[];

815 "delegation_row" "delegation"%ROWTYPE;

816 "row_count" INT4;

817 "i" INT4;

818 "loop_v" BOOLEAN;

819 BEGIN

820 IF

821 "unit_id_p" NOTNULL AND

822 "area_id_p" ISNULL AND

823 "issue_id_p" ISNULL

824 THEN

825 "scope_v" := 'unit';

826 "unit_id_v" := "unit_id_p";

827 ELSIF

828 "unit_id_p" ISNULL AND

829 "area_id_p" NOTNULL AND

830 "issue_id_p" ISNULL

831 THEN

832 "scope_v" := 'area';

833 "area_id_v" := "area_id_p";

834 SELECT "unit_id" INTO "unit_id_v"

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

836 ELSIF

837 "unit_id_p" ISNULL AND

838 "area_id_p" ISNULL AND

839 "issue_id_p" NOTNULL

840 THEN

841 "scope_v" := 'issue';

842 SELECT "area_id" INTO "area_id_v"

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

844 SELECT "unit_id" INTO "unit_id_v"

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

846 ELSE

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

848 END IF;

849 "visited_member_ids" := '{}';

850 "loop_member_id_v" := NULL;

851 "output_rows" := '{}';

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

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

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

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

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

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

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

859 LOOP

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

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

862 ELSE

863 "visited_member_ids" :=

864 "visited_member_ids" || "output_row"."member_id";

865 END IF;

866 IF "output_row"."participation" THEN

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

868 END IF;

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

870 IF EXISTS (

871 SELECT NULL FROM "member" JOIN "privilege"

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

873 AND "privilege"."unit_id" = "unit_id_v"

874 WHERE "id" = "output_row"."member_id"

875 AND "member"."active" AND "privilege"."voting_right"

876 ) THEN

877 IF "scope_v" = 'unit' THEN

878 SELECT * INTO "delegation_row" FROM "delegation"

879 WHERE "truster_id" = "output_row"."member_id"

880 AND "unit_id" = "unit_id_v";

881 ELSIF "scope_v" = 'area' THEN

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

883 SELECT NULL FROM "membership"

884 WHERE "area_id" = "area_id_p"

885 AND "member_id" = "output_row"."member_id"

886 );

887 SELECT * INTO "delegation_row" FROM "delegation"

888 WHERE "truster_id" = "output_row"."member_id"

889 AND (

890 "unit_id" = "unit_id_v" OR

891 "area_id" = "area_id_v"

892 )

893 ORDER BY "scope" DESC;

894 ELSIF "scope_v" = 'issue' THEN

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

896 SELECT NULL FROM "interest"

897 WHERE "issue_id" = "issue_id_p"

898 AND "member_id" = "output_row"."member_id"

899 );

900 SELECT * INTO "delegation_row" FROM "delegation"

901 WHERE "truster_id" = "output_row"."member_id"

902 AND (

903 "unit_id" = "unit_id_v" OR

904 "area_id" = "area_id_v" OR

905 "issue_id" = "issue_id_p"

906 )

907 ORDER BY "scope" DESC;

908 END IF;

909 ELSE

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

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

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

913 "delegation_row" := ROW(NULL);

914 END IF;

915 IF

916 "output_row"."member_id" = "member_id_p" AND

917 "simulate_trustee_id_p" NOTNULL

918 THEN

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

920 "output_rows" := "output_rows" || "output_row";

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

922 ELSIF "delegation_row"."trustee_id" NOTNULL THEN

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

924 "output_rows" := "output_rows" || "output_row";

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

926 ELSIF "delegation_row"."scope" NOTNULL THEN

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

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

929 "output_rows" := "output_rows" || "output_row";

930 EXIT;

931 ELSE

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

933 "output_rows" := "output_rows" || "output_row";

934 EXIT;

935 END IF;

936 EXIT WHEN "loop_member_id_v" NOTNULL;

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

938 END LOOP;

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

940 "i" := 1;

941 "loop_v" := FALSE;

942 LOOP

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

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

945 IF "loop_v" THEN

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

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

948 ELSIF "i" = "row_count" THEN

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

950 ELSE

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

952 END IF;

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

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

955 "loop_v" := TRUE;

956 END IF;

957 IF "scope_v" = 'unit' THEN

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

959 END IF;

960 RETURN NEXT "output_row";

961 "i" := "i" + 1;

962 END LOOP;

963 RETURN;

964 END;

965 $$;

966

967 COMMENT ON FUNCTION "delegation_chain"

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

969 "unit"."id"%TYPE,

970 "area"."id"%TYPE,

971 "issue"."id"%TYPE,

972 "member"."id"%TYPE )

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

974

975

976 CREATE FUNCTION "delegation_chain"

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

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

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

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

981 RETURNS SETOF "delegation_chain_row"

982 LANGUAGE 'plpgsql' STABLE AS $$

983 DECLARE

984 "result_row" "delegation_chain_row";

985 BEGIN

986 FOR "result_row" IN

987 SELECT * FROM "delegation_chain"(

988 "member_id_p", "unit_id_p", "area_id_p", "issue_id_p", NULL

989 )

990 LOOP

991 RETURN NEXT "result_row";

992 END LOOP;

993 RETURN;

994 END;

995 $$;

996

997 COMMENT ON FUNCTION "delegation_chain"

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

999 "unit"."id"%TYPE,

1000 "area"."id"%TYPE,

1001 "issue"."id"%TYPE )

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

1003

1004

1005 -- Other modified functions:

1006

1007 CREATE OR REPLACE FUNCTION "lock_issue"

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

1009 RETURNS VOID

1010 LANGUAGE 'plpgsql' VOLATILE AS $$

1011 BEGIN

1012 LOCK TABLE "member" IN SHARE MODE;

1013 LOCK TABLE "privilege" IN SHARE MODE;

1014 LOCK TABLE "membership" IN SHARE MODE;

1015 LOCK TABLE "policy" IN SHARE MODE;

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

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

1018 -- share_row_lock_issue(_via_initiative)_trigger functions (which

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

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

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

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

1023 LOCK TABLE "delegation" IN SHARE MODE;

1024 LOCK TABLE "direct_population_snapshot" IN EXCLUSIVE MODE;

1025 LOCK TABLE "delegating_population_snapshot" IN EXCLUSIVE MODE;

1026 LOCK TABLE "direct_interest_snapshot" IN EXCLUSIVE MODE;

1027 LOCK TABLE "delegating_interest_snapshot" IN EXCLUSIVE MODE;

1028 LOCK TABLE "direct_supporter_snapshot" IN EXCLUSIVE MODE;

1029 RETURN;

1030 END;

1031 $$;

1032

1033 CREATE OR REPLACE FUNCTION "calculate_member_counts"()

1034 RETURNS VOID

1035 LANGUAGE 'plpgsql' VOLATILE AS $$

1036 BEGIN

1037 LOCK TABLE "member" IN SHARE MODE;

1038 LOCK TABLE "member_count" IN EXCLUSIVE MODE;

1039 LOCK TABLE "unit" IN EXCLUSIVE MODE;

1040 LOCK TABLE "area" IN EXCLUSIVE MODE;

1041 LOCK TABLE "privilege" IN SHARE MODE;

1042 LOCK TABLE "membership" IN SHARE MODE;

1043 DELETE FROM "member_count";

1044 INSERT INTO "member_count" ("total_count")

1045 SELECT "total_count" FROM "member_count_view";

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

1047 FROM "unit_member_count" AS "view"

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

1049 UPDATE "area" SET

1050 "direct_member_count" = "view"."direct_member_count",

1051 "member_weight" = "view"."member_weight",

1052 "autoreject_weight" = "view"."autoreject_weight"

1053 FROM "area_member_count" AS "view"

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

1055 RETURN;

1056 END;

1057 $$;

1058

1059 CREATE OR REPLACE FUNCTION "create_population_snapshot"

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

1061 RETURNS VOID

1062 LANGUAGE 'plpgsql' VOLATILE AS $$

1063 DECLARE

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

1065 BEGIN

1066 DELETE FROM "direct_population_snapshot"

1067 WHERE "issue_id" = "issue_id_p"

1068 AND "event" = 'periodic';

1069 DELETE FROM "delegating_population_snapshot"

1070 WHERE "issue_id" = "issue_id_p"

1071 AND "event" = 'periodic';

1072 INSERT INTO "direct_population_snapshot"

1073 ("issue_id", "event", "member_id")

1074 SELECT

1075 "issue_id_p" AS "issue_id",

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

1077 "member"."id" AS "member_id"

1078 FROM "issue"

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

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

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

1082 JOIN "privilege"

1083 ON "privilege"."unit_id" = "area"."unit_id"

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

1085 WHERE "issue"."id" = "issue_id_p"

1086 AND "member"."active" AND "privilege"."voting_right"

1087 UNION

1088 SELECT

1089 "issue_id_p" AS "issue_id",

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

1091 "member"."id" AS "member_id"

1092 FROM "issue"

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

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

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

1096 JOIN "privilege"

1097 ON "privilege"."unit_id" = "area"."unit_id"

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

1099 WHERE "issue"."id" = "issue_id_p"

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

1101 FOR "member_id_v" IN

1102 SELECT "member_id" FROM "direct_population_snapshot"

1103 WHERE "issue_id" = "issue_id_p"

1104 AND "event" = 'periodic'

1105 LOOP

1106 UPDATE "direct_population_snapshot" SET

1107 "weight" = 1 +

1108 "weight_of_added_delegations_for_population_snapshot"(

1109 "issue_id_p",

1110 "member_id_v",

1111 '{}'

1112 )

1113 WHERE "issue_id" = "issue_id_p"

1114 AND "event" = 'periodic'

1115 AND "member_id" = "member_id_v";

1116 END LOOP;

1117 RETURN;

1118 END;

1119 $$;

1120

1121 CREATE OR REPLACE FUNCTION "create_interest_snapshot"

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

1123 RETURNS VOID

1124 LANGUAGE 'plpgsql' VOLATILE AS $$

1125 DECLARE

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

1127 BEGIN

1128 DELETE FROM "direct_interest_snapshot"

1129 WHERE "issue_id" = "issue_id_p"

1130 AND "event" = 'periodic';

1131 DELETE FROM "delegating_interest_snapshot"

1132 WHERE "issue_id" = "issue_id_p"

1133 AND "event" = 'periodic';

1134 DELETE FROM "direct_supporter_snapshot"

1135 WHERE "issue_id" = "issue_id_p"

1136 AND "event" = 'periodic';

1137 INSERT INTO "direct_interest_snapshot"

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

1139 SELECT

1140 "issue_id_p" AS "issue_id",

1141 'periodic' AS "event",

1142 "member"."id" AS "member_id",

1143 "interest"."voting_requested"

1144 FROM "issue"

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

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

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

1148 JOIN "privilege"

1149 ON "privilege"."unit_id" = "area"."unit_id"

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

1151 WHERE "issue"."id" = "issue_id_p"

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

1153 FOR "member_id_v" IN

1154 SELECT "member_id" FROM "direct_interest_snapshot"

1155 WHERE "issue_id" = "issue_id_p"

1156 AND "event" = 'periodic'

1157 LOOP

1158 UPDATE "direct_interest_snapshot" SET

1159 "weight" = 1 +

1160 "weight_of_added_delegations_for_interest_snapshot"(

1161 "issue_id_p",

1162 "member_id_v",

1163 '{}'

1164 )

1165 WHERE "issue_id" = "issue_id_p"

1166 AND "event" = 'periodic'

1167 AND "member_id" = "member_id_v";

1168 END LOOP;

1169 INSERT INTO "direct_supporter_snapshot"

1170 ( "issue_id", "initiative_id", "event", "member_id",

1171 "informed", "satisfied" )

1172 SELECT

1173 "issue_id_p" AS "issue_id",

1174 "initiative"."id" AS "initiative_id",

1175 'periodic' AS "event",

1176 "supporter"."member_id" AS "member_id",

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

1178 NOT EXISTS (

1179 SELECT NULL FROM "critical_opinion"

1180 WHERE "initiative_id" = "initiative"."id"

1181 AND "member_id" = "supporter"."member_id"

1182 ) AS "satisfied"

1183 FROM "initiative"

1184 JOIN "supporter"

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

1186 JOIN "current_draft"

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

1188 JOIN "direct_interest_snapshot"

1189 ON "supporter"."member_id" = "direct_interest_snapshot"."member_id"

1190 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"

1191 AND "event" = 'periodic'

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

1193 RETURN;

1194 END;

1195 $$;

1196

1197 CREATE OR REPLACE FUNCTION "freeze_after_snapshot"

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

1199 RETURNS VOID

1200 LANGUAGE 'plpgsql' VOLATILE AS $$

1201 DECLARE

1202 "issue_row" "issue"%ROWTYPE;

1203 "policy_row" "policy"%ROWTYPE;

1204 "initiative_row" "initiative"%ROWTYPE;

1205 BEGIN

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

1207 SELECT * INTO "policy_row"

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

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

1210 FOR "initiative_row" IN

1211 SELECT * FROM "initiative"

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

1213 LOOP

1214 IF

1215 "initiative_row"."satisfied_supporter_count" > 0 AND

1216 "initiative_row"."satisfied_supporter_count" *

1217 "policy_row"."initiative_quorum_den" >=

1218 "issue_row"."population" * "policy_row"."initiative_quorum_num"

1219 THEN

1220 UPDATE "initiative" SET "admitted" = TRUE

1221 WHERE "id" = "initiative_row"."id";

1222 ELSE

1223 UPDATE "initiative" SET "admitted" = FALSE

1224 WHERE "id" = "initiative_row"."id";

1225 END IF;

1226 END LOOP;

1227 IF EXISTS (

1228 SELECT NULL FROM "initiative"

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

1230 ) THEN

1231 UPDATE "issue" SET

1232 "state" = 'voting',

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

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

1235 "fully_frozen" = now()

1236 WHERE "id" = "issue_id_p";

1237 ELSE

1238 UPDATE "issue" SET

1239 "state" = 'canceled_no_initiative_admitted',

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

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

1242 "fully_frozen" = now(),

1243 "closed" = now(),

1244 "ranks_available" = TRUE

1245 WHERE "id" = "issue_id_p";

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

1247 -- issue state has been manipulated

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

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

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

1251 END IF;

1252 RETURN;

1253 END;

1254 $$;

1255

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

1257 RETURNS VOID

1258 LANGUAGE 'plpgsql' VOLATILE AS $$

1259 DECLARE

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

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

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

1263 BEGIN

1264 PERFORM "lock_issue"("issue_id_p");

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

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

1267 DELETE FROM "delegating_voter"

1268 WHERE "issue_id" = "issue_id_p";

1269 DELETE FROM "direct_voter"

1270 WHERE "issue_id" = "issue_id_p"

1271 AND "autoreject" = TRUE;

1272 DELETE FROM "direct_voter"

1273 USING (

1274 SELECT

1275 "direct_voter"."member_id"

1276 FROM "direct_voter"

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

1278 LEFT JOIN "privilege"

1279 ON "privilege"."unit_id" = "unit_id_v"

1280 AND "privilege"."member_id" = "direct_voter"."member_id"

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

1282 "member"."active" = FALSE OR

1283 "privilege"."voting_right" ISNULL OR

1284 "privilege"."voting_right" = FALSE

1285 )

1286 ) AS "subquery"

1287 WHERE "direct_voter"."issue_id" = "issue_id_p"

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

1289 UPDATE "direct_voter" SET "weight" = 1

1290 WHERE "issue_id" = "issue_id_p";

1291 PERFORM "add_vote_delegations"("issue_id_p");

1292 FOR "member_id_v" IN

1293 SELECT "interest"."member_id"

1294 FROM "interest"

1295 JOIN "member"

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

1297 JOIN "privilege"

1298 ON "privilege"."unit_id" = "unit_id_v"

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

1300 LEFT JOIN "direct_voter"

1301 ON "interest"."member_id" = "direct_voter"."member_id"

1302 AND "interest"."issue_id" = "direct_voter"."issue_id"

1303 LEFT JOIN "delegating_voter"

1304 ON "interest"."member_id" = "delegating_voter"."member_id"

1305 AND "interest"."issue_id" = "delegating_voter"."issue_id"

1306 WHERE "interest"."issue_id" = "issue_id_p"

1307 AND "interest"."autoreject" = TRUE

1308 AND "member"."active"

1309 AND "privilege"."voting_right"

1310 AND "direct_voter"."member_id" ISNULL

1311 AND "delegating_voter"."member_id" ISNULL

1312 UNION SELECT "membership"."member_id"

1313 FROM "membership"

1314 JOIN "member"

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

1316 JOIN "privilege"

1317 ON "privilege"."unit_id" = "unit_id_v"

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

1319 LEFT JOIN "interest"

1320 ON "membership"."member_id" = "interest"."member_id"

1321 AND "interest"."issue_id" = "issue_id_p"

1322 LEFT JOIN "direct_voter"

1323 ON "membership"."member_id" = "direct_voter"."member_id"

1324 AND "direct_voter"."issue_id" = "issue_id_p"

1325 LEFT JOIN "delegating_voter"

1326 ON "membership"."member_id" = "delegating_voter"."member_id"

1327 AND "delegating_voter"."issue_id" = "issue_id_p"

1328 WHERE "membership"."area_id" = "area_id_v"

1329 AND "membership"."autoreject" = TRUE

1330 AND "member"."active"

1331 AND "privilege"."voting_right"

1332 AND "interest"."autoreject" ISNULL

1333 AND "direct_voter"."member_id" ISNULL

1334 AND "delegating_voter"."member_id" ISNULL

1335 LOOP

1336 INSERT INTO "direct_voter"

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

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

1339 INSERT INTO "vote" (

1340 "member_id",

1341 "issue_id",

1342 "initiative_id",

1343 "grade"

1344 ) SELECT

1345 "member_id_v" AS "member_id",

1346 "issue_id_p" AS "issue_id",

1347 "id" AS "initiative_id",

1348 -1 AS "grade"

1349 FROM "initiative"

1350 WHERE "issue_id" = "issue_id_p" AND "admitted";

1351 END LOOP;

1352 PERFORM "add_vote_delegations"("issue_id_p");

1353 UPDATE "issue" SET

1354 "state" = 'calculation',

1355 "closed" = now(),

1356 "voter_count" = (

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

1358 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"

1359 )

1360 WHERE "id" = "issue_id_p";

1361 UPDATE "initiative" SET

1362 "positive_votes" = "vote_counts"."positive_votes",

1363 "negative_votes" = "vote_counts"."negative_votes",

1364 "agreed" = CASE WHEN "majority_strict" THEN

1365 "vote_counts"."positive_votes" * "majority_den" >

1366 "majority_num" *

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

1368 ELSE

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

1370 "majority_num" *

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

1372 END

1373 FROM

1374 ( SELECT

1375 "initiative"."id" AS "initiative_id",

1376 coalesce(

1377 sum(

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

1379 ),

1380 0

1381 ) AS "positive_votes",

1382 coalesce(

1383 sum(

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

1385 ),

1386 0

1387 ) AS "negative_votes"

1388 FROM "initiative"

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

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

1391 LEFT JOIN "direct_voter"

1392 ON "direct_voter"."issue_id" = "initiative"."issue_id"

1393 LEFT JOIN "vote"

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

1395 AND "vote"."member_id" = "direct_voter"."member_id"

1396 WHERE "initiative"."issue_id" = "issue_id_p"

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

1398 GROUP BY "initiative"."id"

1399 ) AS "vote_counts",

1400 "issue",

1401 "policy"

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

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

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

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

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

1407 INSERT INTO "battle" (

1408 "issue_id",

1409 "winning_initiative_id", "losing_initiative_id",

1410 "count"

1411 ) SELECT

1412 "issue_id",

1413 "winning_initiative_id", "losing_initiative_id",

1414 "count"

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

1416 END;

1417 $$;

1418

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

1420 RETURNS VOID

1421 LANGUAGE 'plpgsql' VOLATILE AS $$

1422 DECLARE

1423 "dimension_v" INTEGER;

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

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

1426 "i" INTEGER;

1427 "j" INTEGER;

1428 "k" INTEGER;

1429 "battle_row" "battle"%ROWTYPE;

1430 "rank_ary" INT4[];

1431 "rank_v" INT4;

1432 "done_v" INTEGER;

1433 "winners_ary" INTEGER[];

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

1435 BEGIN

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

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

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

1439 IF "dimension_v" = 1 THEN

1440 UPDATE "initiative" SET "rank" = 1

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

1442 ELSIF "dimension_v" > 1 THEN

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

1444 -- comparison:

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

1446 "i" := 1;

1447 "j" := 2;

1448 FOR "battle_row" IN

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

1450 ORDER BY "winning_initiative_id", "losing_initiative_id"

1451 LOOP

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

1453 IF "j" = "dimension_v" THEN

1454 "i" := "i" + 1;

1455 "j" := 1;

1456 ELSE

1457 "j" := "j" + 1;

1458 IF "j" = "i" THEN

1459 "j" := "j" + 1;

1460 END IF;

1461 END IF;

1462 END LOOP;

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

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

1465 END IF;

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

1467 -- function:

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

1469 "i" := 1;

1470 LOOP

1471 "j" := 1;

1472 LOOP

1473 IF "i" != "j" THEN

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

1475 "vote_matrix"["i"]["j"],

1476 "vote_matrix"["j"]["i"]

1477 );

1478 END IF;

1479 EXIT WHEN "j" = "dimension_v";

1480 "j" := "j" + 1;

1481 END LOOP;

1482 EXIT WHEN "i" = "dimension_v";

1483 "i" := "i" + 1;

1484 END LOOP;

1485 -- Find best paths:

1486 "i" := 1;

1487 LOOP

1488 "j" := 1;

1489 LOOP

1490 IF "i" != "j" THEN

1491 "k" := 1;

1492 LOOP

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

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

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

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

1497 END IF;

1498 ELSE

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

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

1501 END IF;

1502 END IF;

1503 END IF;

1504 EXIT WHEN "k" = "dimension_v";

1505 "k" := "k" + 1;

1506 END LOOP;

1507 END IF;

1508 EXIT WHEN "j" = "dimension_v";

1509 "j" := "j" + 1;

1510 END LOOP;

1511 EXIT WHEN "i" = "dimension_v";

1512 "i" := "i" + 1;

1513 END LOOP;

1514 -- Determine order of winners:

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

1516 "rank_v" := 1;

1517 "done_v" := 0;

1518 LOOP

1519 "winners_ary" := '{}';

1520 "i" := 1;

1521 LOOP

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

1523 "j" := 1;

1524 LOOP

1525 IF

1526 "i" != "j" AND

1527 "rank_ary"["j"] ISNULL AND

1528 "matrix"["j"]["i"] > "matrix"["i"]["j"]

1529 THEN

1530 -- someone else is better

1531 EXIT;

1532 END IF;

1533 IF "j" = "dimension_v" THEN

1534 -- noone is better

1535 "winners_ary" := "winners_ary" || "i";

1536 EXIT;

1537 END IF;

1538 "j" := "j" + 1;

1539 END LOOP;

1540 END IF;

1541 EXIT WHEN "i" = "dimension_v";

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

1543 END LOOP;

1544 "i" := 1;

1545 LOOP

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

1547 "done_v" := "done_v" + 1;

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

1549 "i" := "i" + 1;

1550 END LOOP;

1551 EXIT WHEN "done_v" = "dimension_v";

1552 "rank_v" := "rank_v" + 1;

1553 END LOOP;

1554 -- write preliminary ranks:

1555 "i" := 1;

1556 FOR "initiative_id_v" IN

1557 SELECT "id" FROM "initiative"

1558 WHERE "issue_id" = "issue_id_p" AND "agreed"

1559 ORDER BY "id"

1560 LOOP

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

1562 WHERE "id" = "initiative_id_v";

1563 "i" := "i" + 1;

1564 END LOOP;

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

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

1567 END IF;

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

1569 "rank_v" := 1;

1570 FOR "initiative_id_v" IN

1571 SELECT "id" FROM "initiative"

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

1573 ORDER BY

1574 "rank",

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

1576 "id"

1577 LOOP

1578 UPDATE "initiative" SET "rank" = "rank_v"

1579 WHERE "id" = "initiative_id_v";

1580 "rank_v" := "rank_v" + 1;

1581 END LOOP;

1582 END IF;

1583 -- mark issue as finished

1584 UPDATE "issue" SET

1585 "state" =

1586 CASE WHEN "dimension_v" = 0 THEN

1587 'finished_without_winner'::"issue_state"

1588 ELSE

1589 'finished_with_winner'::"issue_state"

1590 END,

1591 "ranks_available" = TRUE

1592 WHERE "id" = "issue_id_p";

1593 RETURN;

1594 END;

1595 $$;

1596

1597 CREATE OR REPLACE FUNCTION "check_issue"

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

1599 RETURNS VOID

1600 LANGUAGE 'plpgsql' VOLATILE AS $$

1601 DECLARE

1602 "issue_row" "issue"%ROWTYPE;

1603 "policy_row" "policy"%ROWTYPE;

1604 "voting_requested_v" BOOLEAN;

1605 BEGIN

1606 PERFORM "lock_issue"("issue_id_p");

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

1608 -- only process open issues:

1609 IF "issue_row"."closed" ISNULL THEN

1610 SELECT * INTO "policy_row" FROM "policy"

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

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

1613 IF "issue_row"."fully_frozen" ISNULL THEN

1614 PERFORM "create_snapshot"("issue_id_p");

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

1616 END IF;

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

1618 IF "issue_row"."accepted" ISNULL THEN

1619 IF EXISTS (

1620 SELECT NULL FROM "initiative"

1621 WHERE "issue_id" = "issue_id_p"

1622 AND "supporter_count" > 0

1623 AND "supporter_count" * "policy_row"."issue_quorum_den"

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

1625 ) THEN

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

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

1628 -- NOTE: "issue_row" used later

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

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

1631 UPDATE "issue" SET

1632 "state" = "issue_row"."state",

1633 "accepted" = "issue_row"."accepted"

1634 WHERE "id" = "issue_row"."id";

1635 ELSIF

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

1637 THEN

1638 -- close issues, if admission time has expired

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

1640 UPDATE "issue" SET

1641 "state" = 'canceled_issue_not_accepted',

1642 "closed" = now()

1643 WHERE "id" = "issue_row"."id";

1644 END IF;

1645 END IF;

1646 -- eventually half freeze issues:

1647 IF

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

1649 "issue_row"."accepted" NOTNULL AND

1650 "issue_row"."half_frozen" ISNULL

1651 THEN

1652 SELECT

1653 CASE

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

1655 TRUE

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

1657 FALSE

1658 ELSE NULL

1659 END

1660 INTO "voting_requested_v"

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

1662 IF

1663 "voting_requested_v" OR (

1664 "voting_requested_v" ISNULL AND

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

1666 )

1667 THEN

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

1669 -- NOTE: "issue_row" used later

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

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

1672 UPDATE "issue" SET

1673 "state" = "issue_row"."state",

1674 "half_frozen" = "issue_row"."half_frozen"

1675 WHERE "id" = "issue_row"."id";

1676 END IF;

1677 END IF;

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

1679 IF

1680 "issue_row"."closed" ISNULL AND

1681 NOT EXISTS (

1682 -- all initiatives are revoked

1683 SELECT NULL FROM "initiative"

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

1685 ) AND (

1686 -- and issue has not been accepted yet

1687 "issue_row"."accepted" ISNULL OR

1688 NOT EXISTS (

1689 -- or no initiatives have been revoked lately

1690 SELECT NULL FROM "initiative"

1691 WHERE "issue_id" = "issue_id_p"

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

1693 ) OR (

1694 -- or verification time has elapsed

1695 "issue_row"."half_frozen" NOTNULL AND

1696 "issue_row"."fully_frozen" ISNULL AND

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

1698 )

1699 )

1700 THEN

1701 -- NOTE: "issue_row" used later

1702 IF "issue_row"."accepted" ISNULL THEN

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

1704 ELSIF "issue_row"."half_frozen" ISNULL THEN

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

1706 ELSE

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

1708 END IF;

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

1710 UPDATE "issue" SET

1711 "state" = "issue_row"."state",

1712 "closed" = "issue_row"."closed"

1713 WHERE "id" = "issue_row"."id";

1714 END IF;

1715 -- fully freeze issue after verification time:

1716 IF

1717 "issue_row"."half_frozen" NOTNULL AND

1718 "issue_row"."fully_frozen" ISNULL AND

1719 "issue_row"."closed" ISNULL AND

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

1721 THEN

1722 PERFORM "freeze_after_snapshot"("issue_id_p");

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

1724 END IF;

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

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

1727 IF

1728 "issue_row"."closed" ISNULL AND

1729 "issue_row"."fully_frozen" NOTNULL AND

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

1731 THEN

1732 PERFORM "close_voting"("issue_id_p");

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

1734 PERFORM "calculate_ranks"("issue_id_p");

1735 END IF;

1736 END IF;

1737 RETURN;

1738 END;

1739 $$;

1740

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

1742 RETURNS VOID

1743 LANGUAGE 'plpgsql' VOLATILE AS $$

1744 DECLARE

1745 "issue_row" "issue"%ROWTYPE;

1746 BEGIN

1747 SELECT * INTO "issue_row"

1748 FROM "issue" WHERE "id" = "issue_id_p"

1749 FOR UPDATE;

1750 IF "issue_row"."cleaned" ISNULL THEN

1751 UPDATE "issue" SET

1752 "state" = 'voting',

1753 "closed" = NULL,

1754 "ranks_available" = FALSE

1755 WHERE "id" = "issue_id_p";

1756 DELETE FROM "issue_comment"

1757 WHERE "issue_id" = "issue_id_p";

1758 DELETE FROM "voting_comment"

1759 WHERE "issue_id" = "issue_id_p";

1760 DELETE FROM "delegating_voter"

1761 WHERE "issue_id" = "issue_id_p";

1762 DELETE FROM "direct_voter"

1763 WHERE "issue_id" = "issue_id_p";

1764 DELETE FROM "delegating_interest_snapshot"

1765 WHERE "issue_id" = "issue_id_p";

1766 DELETE FROM "direct_interest_snapshot"

1767 WHERE "issue_id" = "issue_id_p";

1768 DELETE FROM "delegating_population_snapshot"

1769 WHERE "issue_id" = "issue_id_p";

1770 DELETE FROM "direct_population_snapshot"

1771 WHERE "issue_id" = "issue_id_p";

1772 DELETE FROM "non_voter"

1773 WHERE "issue_id" = "issue_id_p";

1774 DELETE FROM "delegation"

1775 WHERE "issue_id" = "issue_id_p";

1776 DELETE FROM "supporter"

1777 WHERE "issue_id" = "issue_id_p";

1778 UPDATE "issue" SET

1779 "state" = "issue_row"."state",

1780 "closed" = "issue_row"."closed",

1781 "ranks_available" = "issue_row"."ranks_available",

1782 "cleaned" = now()

1783 WHERE "id" = "issue_id_p";

1784 END IF;

1785 RETURN;

1786 END;

1787 $$;

1788

1789 CREATE OR REPLACE FUNCTION "check_issue"

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

1791 RETURNS VOID

1792 LANGUAGE 'plpgsql' VOLATILE AS $$

1793 DECLARE

1794 "issue_row" "issue"%ROWTYPE;

1795 "policy_row" "policy"%ROWTYPE;

1796 "voting_requested_v" BOOLEAN;

1797 BEGIN

1798 PERFORM "lock_issue"("issue_id_p");

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

1800 -- only process open issues:

1801 IF "issue_row"."closed" ISNULL THEN

1802 SELECT * INTO "policy_row" FROM "policy"

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

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

1805 IF "issue_row"."fully_frozen" ISNULL THEN

1806 PERFORM "create_snapshot"("issue_id_p");

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

1808 END IF;

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

1810 IF "issue_row"."accepted" ISNULL THEN

1811 IF EXISTS (

1812 SELECT NULL FROM "initiative"

1813 WHERE "issue_id" = "issue_id_p"

1814 AND "supporter_count" > 0

1815 AND "supporter_count" * "policy_row"."issue_quorum_den"

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

1817 ) THEN

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

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

1820 -- NOTE: "issue_row" used later

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

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

1823 UPDATE "issue" SET

1824 "state" = "issue_row"."state",

1825 "accepted" = "issue_row"."accepted"

1826 WHERE "id" = "issue_row"."id";

1827 ELSIF

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

1829 THEN

1830 -- close issues, if admission time has expired

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

1832 UPDATE "issue" SET

1833 "state" = 'canceled_issue_not_accepted',

1834 "closed" = now()

1835 WHERE "id" = "issue_row"."id";

1836 END IF;

1837 END IF;

1838 -- eventually half freeze issues:

1839 IF

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

1841 "issue_row"."accepted" NOTNULL AND

1842 "issue_row"."half_frozen" ISNULL

1843 THEN

1844 SELECT

1845 CASE

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

1847 TRUE

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

1849 FALSE

1850 ELSE NULL

1851 END

1852 INTO "voting_requested_v"

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

1854 IF

1855 "voting_requested_v" OR (

1856 "voting_requested_v" ISNULL AND

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

1858 )

1859 THEN

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

1861 -- NOTE: "issue_row" used later

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

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

1864 UPDATE "issue" SET

1865 "state" = "issue_row"."state",

1866 "half_frozen" = "issue_row"."half_frozen"

1867 WHERE "id" = "issue_row"."id";

1868 END IF;

1869 END IF;

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

1871 IF

1872 "issue_row"."closed" ISNULL AND

1873 NOT EXISTS (

1874 -- all initiatives are revoked

1875 SELECT NULL FROM "initiative"

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

1877 ) AND (

1878 -- and issue has not been accepted yet

1879 "issue_row"."accepted" ISNULL OR

1880 NOT EXISTS (

1881 -- or no initiatives have been revoked lately

1882 SELECT NULL FROM "initiative"

1883 WHERE "issue_id" = "issue_id_p"

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

1885 ) OR (

1886 -- or verification time has elapsed

1887 "issue_row"."half_frozen" NOTNULL AND

1888 "issue_row"."fully_frozen" ISNULL AND

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

1890 )

1891 )

1892 THEN

1893 -- NOTE: "issue_row" used later

1894 IF "issue_row"."accepted" ISNULL THEN

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

1896 ELSIF "issue_row"."half_frozen" ISNULL THEN

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

1898 ELSE

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

1900 END IF;

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

1902 UPDATE "issue" SET

1903 "state" = "issue_row"."state",

1904 "closed" = "issue_row"."closed"

1905 WHERE "id" = "issue_row"."id";

1906 END IF;

1907 -- fully freeze issue after verification time:

1908 IF

1909 "issue_row"."half_frozen" NOTNULL AND

1910 "issue_row"."fully_frozen" ISNULL AND

1911 "issue_row"."closed" ISNULL AND

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

1913 THEN

1914 PERFORM "freeze_after_snapshot"("issue_id_p");

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

1916 END IF;

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

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

1919 IF

1920 "issue_row"."closed" ISNULL AND

1921 "issue_row"."fully_frozen" NOTNULL AND

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

1923 THEN

1924 PERFORM "close_voting"("issue_id_p");

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

1926 PERFORM "calculate_ranks"("issue_id_p");

1927 END IF;

1928 END IF;

1929 RETURN;

1930 END;

1931 $$;

1932

1933 CREATE OR REPLACE FUNCTION "delete_private_data"()

1934 RETURNS VOID

1935 LANGUAGE 'plpgsql' VOLATILE AS $$

1936 BEGIN

1937 UPDATE "member" SET

1938 "last_login" = NULL,

1939 "login" = NULL,

1940 "password" = NULL,

1941 "notify_email" = NULL,

1942 "notify_email_unconfirmed" = NULL,

1943 "notify_email_secret" = NULL,

1944 "notify_email_secret_expiry" = NULL,

1945 "notify_email_lock_expiry" = NULL,

1946 "password_reset_secret" = NULL,

1947 "password_reset_secret_expiry" = NULL,

1948 "organizational_unit" = NULL,

1949 "internal_posts" = NULL,

1950 "realname" = NULL,

1951 "birthday" = NULL,

1952 "address" = NULL,

1953 "email" = NULL,

1954 "xmpp_address" = NULL,

1955 "website" = NULL,

1956 "phone" = NULL,

1957 "mobile_phone" = NULL,

1958 "profession" = NULL,

1959 "external_memberships" = NULL,

1960 "external_posts" = NULL,

1961 "statement" = NULL;

1962 -- "text_search_data" is updated by triggers

1963 DELETE FROM "invite_code";

1964 DELETE FROM "setting";

1965 DELETE FROM "setting_map";

1966 DELETE FROM "member_relation_setting";

1967 DELETE FROM "member_image";

1968 DELETE FROM "contact";

1969 DELETE FROM "ignored_member";

1970 DELETE FROM "session";

1971 DELETE FROM "area_setting";

1972 DELETE FROM "issue_setting";

1973 DELETE FROM "ignored_initiative";

1974 DELETE FROM "initiative_setting";

1975 DELETE FROM "suggestion_setting";

1976 DELETE FROM "non_voter";

1977 DELETE FROM "direct_voter" USING "issue"

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

1979 AND "issue"."closed" ISNULL;

1980 RETURN;

1981 END;

1982 $$;

1983

1984

1985 -- Delete old "delegation_scope" TYPE:

1986

1987 DROP TYPE "delegation_scope_old";

1988

1989

1990 COMMIT;

1991

1992

1993 -- Generate issue states and add constraints:

1994

1995 UPDATE "issue" SET "state" =

1996 CASE

1997 WHEN "closed" ISNULL THEN

1998 CASE

1999 WHEN "accepted" ISNULL THEN

2000 'admission'::"issue_state"

2001 WHEN "half_frozen" ISNULL THEN

2002 'discussion'::"issue_state"

2003 WHEN "fully_frozen" ISNULL THEN

2004 'verification'::"issue_state"

2005 ELSE

2006 'voting'::"issue_state"

2007 END

2008 WHEN "fully_frozen" NOTNULL THEN

2009 CASE

2010 WHEN "fully_frozen" = "closed" THEN

2011 'canceled_no_initiative_admitted'::"issue_state"

2012 ELSE

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

2014 END

2015 WHEN "half_frozen" NOTNULL THEN

2016 'canceled_after_revocation_during_verification'::"issue_state"

2017 WHEN "accepted" NOTNULL THEN

2018 'canceled_after_revocation_during_discussion'::"issue_state"

2019 ELSE

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

2021 END;

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

2023 FROM "initiative"

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

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

2026 AND "initiative"."agreed";

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

2028 FROM "initiative"

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

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

2031 AND "initiative"."revoked" ISNULL;

2032

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

2034

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

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

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

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

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

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

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

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

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

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

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

2046 ) AND (

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

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

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

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

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

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

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

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

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

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

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

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

2059 ));

2060

2061

2062 -- Guess "revoked_by_member_id" values based on author of current draft and add constraint:

2063

2064 UPDATE "initiative" SET "revoked_by_member_id" = "author_id"

2065 FROM "current_draft"

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

2067 AND "initiative"."revoked" NOTNULL;

2068

2069 ALTER TABLE "initiative" ADD

2070 CONSTRAINT "all_or_none_of_revoked_and_revoked_by_member_id_must_be_null"

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

2072

2073

2074 -- Fill "unit_id" column with default value where neccessary and add constraints:

2075

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

2077

2078 ALTER TABLE "delegation" ADD CONSTRAINT "area_id_and_issue_id_set_according_to_scope"

2079 CHECK (

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

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

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

2083

2084

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

2086

2087 DELETE FROM "event";

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

2089

2090 INSERT INTO "event"

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

2092 "initiative_id", "draft_id", "suggestion_id" )

2093 SELECT * FROM (

2094 SELECT * FROM (

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

2096 "timeline"."occurrence",

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

2098 'initiative_created_in_new_issue'::"event_type"

2099 ELSE

2100 'initiative_created_in_existing_issue'::"event_type"

2101 END,

2102 "draft"."author_id",

2103 "issue"."id",

2104 CASE

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

2106 'admission'::"issue_state"

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

2108 'discussion'::"issue_state"

2109 ELSE

2110 'verification'::"issue_state"

2111 END,

2112 "initiative"."id",

2113 "draft"."id",

2114 NULL::INT8

2115 FROM "timeline"

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

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

2118 LEFT JOIN "timeline" AS "issue_creation"

2119 ON "initiative"."issue_id" = "issue_creation"."issue_id"

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

2121 AND "timeline"."occurrence" = "issue_creation"."occurrence"

2122 JOIN "draft"

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

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

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

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

2127 UNION ALL

2128 SELECT

2129 "timeline"."occurrence",

2130 'issue_state_changed'::"event_type",

2131 NULL,

2132 "issue"."id",

2133 CASE

2134 WHEN "timeline"."event" IN (

2135 'issue_canceled',

2136 'issue_finished_without_voting',

2137 'issue_finished_after_voting'

2138 ) THEN

2139 "issue"."state"

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

2141 'discussion'::"issue_state"

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

2143 'verification'::"issue_state"

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

2145 'voting'::"issue_state"

2146 END,

2147 NULL,

2148 NULL,

2149 NULL

2150 FROM "timeline"

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

2152 WHERE "timeline"."event" IN (

2153 'issue_canceled',

2154 'issue_accepted',

2155 'issue_half_frozen',

2156 'issue_finished_without_voting',

2157 'issue_voting_started',

2158 'issue_finished_after_voting' )

2159 UNION ALL

2160 SELECT

2161 "timeline"."occurrence",

2162 'initiative_revoked'::"event_type",

2163 "initiative"."revoked_by_member_id",

2164 "issue"."id",

2165 CASE

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

2167 'admission'::"issue_state"

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

2169 'discussion'::"issue_state"

2170 ELSE

2171 'verification'::"issue_state"

2172 END,

2173 "initiative"."id",

2174 "current_draft"."id",

2175 NULL

2176 FROM "timeline"

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

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

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

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

2181 UNION ALL

2182 SELECT

2183 "timeline"."occurrence",

2184 'new_draft_created'::"event_type",

2185 "draft"."author_id",

2186 "issue"."id",

2187 CASE

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

2189 'admission'::"issue_state"

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

2191 'discussion'::"issue_state"

2192 ELSE

2193 'verification'::"issue_state"

2194 END,

2195 "initiative"."id",

2196 "draft"."id",

2197 NULL

2198 FROM "timeline"

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

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

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

2202 LEFT JOIN "timeline" AS "initiative_creation"

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

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

2205 AND "timeline"."occurrence" = "initiative_creation"."occurrence"

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

2207 AND "initiative_creation"."initiative_id" ISNULL

2208 UNION ALL

2209 SELECT

2210 "timeline"."occurrence",

2211 'suggestion_created'::"event_type",

2212 "suggestion"."author_id",

2213 "issue"."id",

2214 CASE

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

2216 'admission'::"issue_state"

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

2218 'discussion'::"issue_state"

2219 ELSE

2220 'verification'::"issue_state"

2221 END,

2222 "initiative"."id",

2223 NULL,

2224 "suggestion"."id"

2225 FROM "timeline"

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

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

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

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

2230 ) AS "subquery"

2231 ORDER BY "occurrence";

2232

2233