liquid_feedback_core: 9d811ba751c6 update/core-update.v1.3.1-v1.4.0

liquid_feedback_core

view update/core-update.v1.3.1-v1.4.0_rc4.sql @ 190:9d811ba751c6

UNIQUE constraint for "member_application"."key"

author	jbe
date	Sat Jul 30 15:18:45 2011 +0200 (2011-07-30)
parents	aaf5f62b1aa2
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_rc4', 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" FROM "invite_code";

140

141

142 -- New table "privilege":

143

144 CREATE TABLE "privilege" (

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

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

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

148 "admin_manager" BOOLEAN NOT NULL DEFAULT FALSE,

149 "unit_manager" BOOLEAN NOT NULL DEFAULT FALSE,

150 "area_manager" BOOLEAN NOT NULL DEFAULT FALSE,

151 "voting_right_manager" BOOLEAN NOT NULL DEFAULT FALSE,

152 "voting_right" BOOLEAN NOT NULL DEFAULT TRUE );

153

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

155

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

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

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

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

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

161

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

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

164 FROM "member";

165

166

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

168

169 DROP TABLE "ignored_issue";

170

171

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

173

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

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

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

177

178

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

180

181 DROP FUNCTION "delegation_chain"

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

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

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

185

186 DROP FUNCTION "delegation_chain"

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

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

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

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

191

192 DROP TYPE "delegation_chain_row";

193

194 DROP VIEW "issue_delegation";

195 DROP VIEW "area_delegation";

196 DROP VIEW "global_delegation";

197 DROP VIEW "active_delegation";

198

199

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

201

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

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

204

205 DROP INDEX "delegation_global_truster_id_unique_idx";

206

207 ALTER TABLE "delegation" ALTER "scope" TYPE "delegation_scope"

208 USING CASE WHEN "scope" = 'global'

209 THEN 'unit'::"delegation_scope"

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

211

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

213

214 ALTER TABLE "delegation" ADD CONSTRAINT "no_unit_delegation_to_null"

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

216

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

218

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

220

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

222

223

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

225

226 ALTER TABLE "delegating_population_snapshot" ALTER "scope" TYPE "delegation_scope"

227 USING CASE WHEN "scope" = 'global'

228 THEN 'unit'::"delegation_scope"

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

230

231 ALTER TABLE "delegating_interest_snapshot" ALTER "scope" TYPE "delegation_scope"

232 USING CASE WHEN "scope" = 'global'

233 THEN 'unit'::"delegation_scope"

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

235

236 ALTER TABLE "delegating_voter" ALTER "scope" TYPE "delegation_scope"

237 USING CASE WHEN "scope" = 'global'

238 THEN 'unit'::"delegation_scope"

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

240

241

242 -- New table "non_voter":

243

244 CREATE TABLE "non_voter" (

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

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

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

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

249

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

251

252

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

254

255 CREATE TABLE "issue_comment" (

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

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

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

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

260 "formatting_engine" TEXT,

261 "content" TEXT NOT NULL,

262 "text_search_data" TSVECTOR );

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

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

265 CREATE TRIGGER "update_text_search_data"

266 BEFORE INSERT OR UPDATE ON "issue_comment"

267 FOR EACH ROW EXECUTE PROCEDURE

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

269

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

271

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

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 tables "voting_comment" and "rendered_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 CREATE TABLE "rendered_voting_comment" (

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

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

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

312 ON DELETE CASCADE ON UPDATE CASCADE,

313 "issue_id" INT4,

314 "member_id" INT4,

315 "format" TEXT,

316 "content" TEXT NOT NULL );

317

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

319

320

321 -- New table "event":

322

323 CREATE TYPE "event_type" AS ENUM (

324 'issue_state_changed',

325 'initiative_created_in_new_issue',

326 'initiative_created_in_existing_issue',

327 'initiative_revoked',

328 'new_draft_created',

329 'suggestion_created');

330

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

332

333 CREATE TABLE "event" (

334 "id" SERIAL8 PRIMARY KEY,

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

336 "event" "event_type" NOT NULL,

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

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

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

340 "initiative_id" INT4,

341 "draft_id" INT8,

342 "suggestion_id" INT8,

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

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

345 ON DELETE CASCADE ON UPDATE CASCADE,

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

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

348 ON DELETE CASCADE ON UPDATE CASCADE,

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

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

351 ON DELETE CASCADE ON UPDATE CASCADE,

352 CONSTRAINT "null_constraints_for_issue_state_changed" CHECK (

353 "event" != 'issue_state_changed' OR (

354 "member_id" ISNULL AND

355 "issue_id" NOTNULL AND

356 "state" NOTNULL AND

357 "initiative_id" ISNULL AND

358 "draft_id" ISNULL AND

359 "suggestion_id" ISNULL )),

360 CONSTRAINT "null_constraints_for_initiative_creation_or_revocation_or_new_draft" CHECK (

361 "event" NOT IN (

362 'initiative_created_in_new_issue',

363 'initiative_created_in_existing_issue',

364 'initiative_revoked',

365 'new_draft_created'

366 ) OR (

367 "member_id" NOTNULL AND

368 "issue_id" NOTNULL AND

369 "state" NOTNULL AND

370 "initiative_id" NOTNULL AND

371 "draft_id" NOTNULL AND

372 "suggestion_id" ISNULL )),

373 CONSTRAINT "null_constraints_for_suggestion_creation" CHECK (

374 "event" != 'suggestion_created' OR (

375 "member_id" NOTNULL AND

376 "issue_id" NOTNULL AND

377 "state" NOTNULL AND

378 "initiative_id" NOTNULL AND

379 "draft_id" ISNULL AND

380 "suggestion_id" NOTNULL )) );

381

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

383

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

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

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

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

388

389

390 -- Triggers to fill "event" table:

391

392 CREATE FUNCTION "write_event_issue_state_changed_trigger"()

393 RETURNS TRIGGER

394 LANGUAGE 'plpgsql' VOLATILE AS $$

395 BEGIN

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

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

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

399 END IF;

400 RETURN NULL;

401 END;

402 $$;

403

404 CREATE TRIGGER "write_event_issue_state_changed"

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

406 "write_event_issue_state_changed_trigger"();

407

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

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

410

411 CREATE FUNCTION "write_event_initiative_or_draft_created_trigger"()

412 RETURNS TRIGGER

413 LANGUAGE 'plpgsql' VOLATILE AS $$

414 DECLARE

415 "initiative_row" "initiative"%ROWTYPE;

416 "issue_row" "issue"%ROWTYPE;

417 "event_v" "event_type";

418 BEGIN

419 SELECT * INTO "initiative_row" FROM "initiative"

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

421 SELECT * INTO "issue_row" FROM "issue"

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

423 IF EXISTS (

424 SELECT NULL FROM "draft"

425 WHERE "initiative_id" = NEW."initiative_id"

426 AND "id" != NEW."id"

427 ) THEN

428 "event_v" := 'new_draft_created';

429 ELSE

430 IF EXISTS (

431 SELECT NULL FROM "initiative"

432 WHERE "issue_id" = "initiative_row"."issue_id"

433 AND "id" != "initiative_row"."id"

434 ) THEN

435 "event_v" := 'initiative_created_in_existing_issue';

436 ELSE

437 "event_v" := 'initiative_created_in_new_issue';

438 END IF;

439 END IF;

440 INSERT INTO "event" (

441 "event", "member_id",

442 "issue_id", "state", "initiative_id", "draft_id"

443 ) VALUES (

444 "event_v",

445 NEW."author_id",

446 "initiative_row"."issue_id",

447 "issue_row"."state",

448 "initiative_row"."id",

449 NEW."id" );

450 RETURN NULL;

451 END;

452 $$;

453

454 CREATE TRIGGER "write_event_initiative_or_draft_created"

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

456 "write_event_initiative_or_draft_created_trigger"();

457

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

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

460

461 CREATE FUNCTION "write_event_initiative_revoked_trigger"()

462 RETURNS TRIGGER

463 LANGUAGE 'plpgsql' VOLATILE AS $$

464 DECLARE

465 "issue_row" "issue"%ROWTYPE;

466 BEGIN

467 SELECT * INTO "issue_row" FROM "issue"

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

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

470 INSERT INTO "event" (

471 "event", "member_id", "issue_id", "state", "initiative_id"

472 ) VALUES (

473 'initiative_revoked',

474 NEW."revoked_by_member_id",

475 NEW."issue_id",

476 "issue_row"."state",

477 NEW."id" );

478 END IF;

479 RETURN NULL;

480 END;

481 $$;

482

483 CREATE TRIGGER "write_event_initiative_revoked"

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

485 "write_event_initiative_revoked_trigger"();

486

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

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

489

490 CREATE FUNCTION "write_event_suggestion_created_trigger"()

491 RETURNS TRIGGER

492 LANGUAGE 'plpgsql' VOLATILE AS $$

493 DECLARE

494 "initiative_row" "initiative"%ROWTYPE;

495 "issue_row" "issue"%ROWTYPE;

496 BEGIN

497 SELECT * INTO "initiative_row" FROM "initiative"

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

499 SELECT * INTO "issue_row" FROM "issue"

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

501 INSERT INTO "event" (

502 "event", "member_id",

503 "issue_id", "state", "initiative_id", "suggestion_id"

504 ) VALUES (

505 'suggestion_created',

506 NEW."author_id",

507 "initiative_row"."issue_id",

508 "issue_row"."state",

509 "initiative_row"."id",

510 NEW."id" );

511 RETURN NULL;

512 END;

513 $$;

514

515 CREATE TRIGGER "write_event_suggestion_created"

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

517 "write_event_suggestion_created_trigger"();

518

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

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

521

522

523 -- Modified views:

524

525 CREATE VIEW "unit_delegation" AS

526 SELECT

527 "unit"."id" AS "unit_id",

528 "delegation"."id",

529 "delegation"."truster_id",

530 "delegation"."trustee_id",

531 "delegation"."scope"

532 FROM "unit"

533 JOIN "delegation"

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

535 JOIN "member"

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

537 JOIN "privilege"

538 ON "delegation"."unit_id" = "privilege"."unit_id"

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

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

541

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

543

544 CREATE VIEW "area_delegation" AS

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

546 "area"."id" AS "area_id",

547 "delegation"."id",

548 "delegation"."truster_id",

549 "delegation"."trustee_id",

550 "delegation"."scope"

551 FROM "area"

552 JOIN "delegation"

553 ON "delegation"."unit_id" = "area"."unit_id"

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

555 JOIN "member"

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

557 JOIN "privilege"

558 ON "area"."unit_id" = "privilege"."unit_id"

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

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

561 ORDER BY

562 "area"."id",

563 "delegation"."truster_id",

564 "delegation"."scope" DESC;

565

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

567

568 CREATE VIEW "issue_delegation" AS

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

570 "issue"."id" AS "issue_id",

571 "delegation"."id",

572 "delegation"."truster_id",

573 "delegation"."trustee_id",

574 "delegation"."scope"

575 FROM "issue"

576 JOIN "area"

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

578 JOIN "delegation"

579 ON "delegation"."unit_id" = "area"."unit_id"

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

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

582 JOIN "member"

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

584 JOIN "privilege"

585 ON "area"."unit_id" = "privilege"."unit_id"

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

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

588 ORDER BY

589 "issue"."id",

590 "delegation"."truster_id",

591 "delegation"."scope" DESC;

592

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

594

595 CREATE VIEW "unit_member_count" AS

596 SELECT

597 "unit"."id" AS "unit_id",

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

599 FROM "unit"

600 LEFT JOIN "privilege"

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

602 AND "privilege"."voting_right"

603 LEFT JOIN "member"

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

605 AND "member"."active"

606 GROUP BY "unit"."id";

607

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

609

610 DROP VIEW "area_member_count";

611 CREATE VIEW "area_member_count" AS

612 SELECT

613 "area"."id" AS "area_id",

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

615 coalesce(

616 sum(

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

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

619 ELSE 0 END

620 )

621 ) AS "member_weight",

622 coalesce(

623 sum(

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

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

626 ELSE 0 END

627 )

628 ) AS "autoreject_weight"

629 FROM "area"

630 LEFT JOIN "membership"

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

632 LEFT JOIN "privilege"

633 ON "privilege"."unit_id" = "area"."unit_id"

634 AND "privilege"."member_id" = "membership"."member_id"

635 AND "privilege"."voting_right"

636 LEFT JOIN "member"

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

638 AND "member"."active"

639 GROUP BY "area"."id";

640

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

642

643

644 -- New view "event_seen_by_member":

645

646 CREATE VIEW "event_seen_by_member" AS

647 SELECT

648 "member"."id" AS "seen_by_member_id",

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

650 'voting',

651 'finished_without_winner',

652 'finished_with_winner'

653 ) THEN

654 'voting'::"notify_level"

655 ELSE

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

657 'verification',

658 'canceled_after_revocation_during_verification',

659 'canceled_no_initiative_admitted'

660 ) THEN

661 'verification'::"notify_level"

662 ELSE

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

664 'discussion',

665 'canceled_after_revocation_during_discussion'

666 ) THEN

667 'discussion'::"notify_level"

668 ELSE

669 'all'::"notify_level"

670 END

671 END

672 END AS "notify_level",

673 "event".*

674 FROM "member" CROSS JOIN "event"

675 LEFT JOIN "issue"

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

677 LEFT JOIN "membership"

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

679 AND "issue"."area_id" = "membership"."area_id"

680 LEFT JOIN "interest"

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

682 AND "event"."issue_id" = "interest"."issue_id"

683 LEFT JOIN "supporter"

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

685 AND "event"."initiative_id" = "supporter"."initiative_id"

686 LEFT JOIN "ignored_member"

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

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

689 LEFT JOIN "ignored_initiative"

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

691 AND "event"."initiative_id" = "ignored_initiative"."initiative_id"

692 WHERE (

693 "supporter"."member_id" NOTNULL OR

694 "interest"."member_id" NOTNULL OR

695 ( "membership"."member_id" NOTNULL AND

696 "event"."event" IN (

697 'issue_state_changed',

698 'initiative_created_in_new_issue',

699 'initiative_created_in_existing_issue',

700 'initiative_revoked' ) ) )

701 AND "ignored_member"."member_id" ISNULL

702 AND "ignored_initiative"."member_id" ISNULL;

703

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

705

706

707 -- New view "pending_notification":

708

709 CREATE VIEW "pending_notification" AS

710 SELECT

711 "member"."id" AS "seen_by_member_id",

712 "event".*

713 FROM "member" CROSS JOIN "event"

714 LEFT JOIN "issue"

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

716 LEFT JOIN "membership"

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

718 AND "issue"."area_id" = "membership"."area_id"

719 LEFT JOIN "interest"

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

721 AND "event"."issue_id" = "interest"."issue_id"

722 LEFT JOIN "supporter"

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

724 AND "event"."initiative_id" = "supporter"."initiative_id"

725 LEFT JOIN "ignored_member"

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

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

728 LEFT JOIN "ignored_initiative"

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

730 AND "event"."initiative_id" = "ignored_initiative"."initiative_id"

731 WHERE (

732 "member"."notify_event_id" ISNULL OR

733 ( "member"."notify_event_id" NOTNULL AND

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

735 AND (

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

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

738 "event"."state" IN (

739 'voting',

740 'finished_without_winner',

741 'finished_with_winner' ) ) OR

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

743 "event"."state" IN (

744 'verification',

745 'canceled_after_revocation_during_verification',

746 'canceled_no_initiative_admitted' ) ) OR

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

748 "event"."state" IN (

749 'discussion',

750 'canceled_after_revocation_during_discussion' ) ) )

751 AND (

752 "supporter"."member_id" NOTNULL OR

753 "interest"."member_id" NOTNULL OR

754 ( "membership"."member_id" NOTNULL AND

755 "event"."event" IN (

756 'issue_state_changed',

757 'initiative_created_in_new_issue',

758 'initiative_created_in_existing_issue',

759 'initiative_revoked' ) ) )

760 AND "ignored_member"."member_id" ISNULL

761 AND "ignored_initiative"."member_id" ISNULL;

762

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

764

765

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

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

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

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

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

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

772

773

774 -- Modified "delegation_chain" functions:

775

776 CREATE TYPE "delegation_chain_row" AS (

777 "index" INT4,

778 "member_id" INT4,

779 "member_valid" BOOLEAN,

780 "participation" BOOLEAN,

781 "overridden" BOOLEAN,

782 "scope_in" "delegation_scope",

783 "scope_out" "delegation_scope",

784 "disabled_out" BOOLEAN,

785 "loop" "delegation_chain_loop_tag" );

786

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

788

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

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

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

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

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

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

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

796

797

798 CREATE FUNCTION "delegation_chain"

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

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

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

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

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

804 RETURNS SETOF "delegation_chain_row"

805 LANGUAGE 'plpgsql' STABLE AS $$

806 DECLARE

807 "scope_v" "delegation_scope";

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

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

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

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

812 "output_row" "delegation_chain_row";

813 "output_rows" "delegation_chain_row"[];

814 "delegation_row" "delegation"%ROWTYPE;

815 "row_count" INT4;

816 "i" INT4;

817 "loop_v" BOOLEAN;

818 BEGIN

819 IF

820 "unit_id_p" NOTNULL AND

821 "area_id_p" ISNULL AND

822 "issue_id_p" ISNULL

823 THEN

824 "scope_v" := 'unit';

825 "unit_id_v" := "unit_id_p";

826 ELSIF

827 "unit_id_p" ISNULL AND

828 "area_id_p" NOTNULL AND

829 "issue_id_p" ISNULL

830 THEN

831 "scope_v" := 'area';

832 "area_id_v" := "area_id_p";

833 SELECT "unit_id" INTO "unit_id_v"

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

835 ELSIF

836 "unit_id_p" ISNULL AND

837 "area_id_p" ISNULL AND

838 "issue_id_p" NOTNULL

839 THEN

840 "scope_v" := 'issue';

841 SELECT "area_id" INTO "area_id_v"

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

843 SELECT "unit_id" INTO "unit_id_v"

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

845 ELSE

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

847 END IF;

848 "visited_member_ids" := '{}';

849 "loop_member_id_v" := NULL;

850 "output_rows" := '{}';

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

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

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

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

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

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

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

858 LOOP

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

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

861 ELSE

862 "visited_member_ids" :=

863 "visited_member_ids" || "output_row"."member_id";

864 END IF;

865 IF "output_row"."participation" THEN

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

867 END IF;

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

869 IF EXISTS (

870 SELECT NULL FROM "member" JOIN "privilege"

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

872 AND "privilege"."unit_id" = "unit_id_v"

873 WHERE "id" = "output_row"."member_id"

874 AND "member"."active" AND "privilege"."voting_right"

875 ) THEN

876 IF "scope_v" = 'unit' THEN

877 SELECT * INTO "delegation_row" FROM "delegation"

878 WHERE "truster_id" = "output_row"."member_id"

879 AND "unit_id" = "unit_id_v";

880 ELSIF "scope_v" = 'area' THEN

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

882 SELECT NULL FROM "membership"

883 WHERE "area_id" = "area_id_p"

884 AND "member_id" = "output_row"."member_id"

885 );

886 SELECT * INTO "delegation_row" FROM "delegation"

887 WHERE "truster_id" = "output_row"."member_id"

888 AND (

889 "unit_id" = "unit_id_v" OR

890 "area_id" = "area_id_v"

891 )

892 ORDER BY "scope" DESC;

893 ELSIF "scope_v" = 'issue' THEN

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

895 SELECT NULL FROM "interest"

896 WHERE "issue_id" = "issue_id_p"

897 AND "member_id" = "output_row"."member_id"

898 );

899 SELECT * INTO "delegation_row" FROM "delegation"

900 WHERE "truster_id" = "output_row"."member_id"

901 AND (

902 "unit_id" = "unit_id_v" OR

903 "area_id" = "area_id_v" OR

904 "issue_id" = "issue_id_p"

905 )

906 ORDER BY "scope" DESC;

907 END IF;

908 ELSE

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

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

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

912 "delegation_row" := ROW(NULL);

913 END IF;

914 IF

915 "output_row"."member_id" = "member_id_p" AND

916 "simulate_trustee_id_p" NOTNULL

917 THEN

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

919 "output_rows" := "output_rows" || "output_row";

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

921 ELSIF "delegation_row"."trustee_id" NOTNULL THEN

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

923 "output_rows" := "output_rows" || "output_row";

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

925 ELSIF "delegation_row"."scope" NOTNULL THEN

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

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

928 "output_rows" := "output_rows" || "output_row";

929 EXIT;

930 ELSE

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

932 "output_rows" := "output_rows" || "output_row";

933 EXIT;

934 END IF;

935 EXIT WHEN "loop_member_id_v" NOTNULL;

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

937 END LOOP;

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

939 "i" := 1;

940 "loop_v" := FALSE;

941 LOOP

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

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

944 IF "loop_v" THEN

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

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

947 ELSIF "i" = "row_count" THEN

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

949 ELSE

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

951 END IF;

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

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

954 "loop_v" := TRUE;

955 END IF;

956 IF "scope_v" = 'unit' THEN

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

958 END IF;

959 RETURN NEXT "output_row";

960 "i" := "i" + 1;

961 END LOOP;

962 RETURN;

963 END;

964 $$;

965

966 COMMENT ON FUNCTION "delegation_chain"

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

968 "unit"."id"%TYPE,

969 "area"."id"%TYPE,

970 "issue"."id"%TYPE,

971 "member"."id"%TYPE )

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

973

974

975 CREATE FUNCTION "delegation_chain"

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

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

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

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

980 RETURNS SETOF "delegation_chain_row"

981 LANGUAGE 'plpgsql' STABLE AS $$

982 DECLARE

983 "result_row" "delegation_chain_row";

984 BEGIN

985 FOR "result_row" IN

986 SELECT * FROM "delegation_chain"(

987 "member_id_p", "unit_id_p", "area_id_p", "issue_id_p", NULL

988 )

989 LOOP

990 RETURN NEXT "result_row";

991 END LOOP;

992 RETURN;

993 END;

994 $$;

995

996 COMMENT ON FUNCTION "delegation_chain"

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

998 "unit"."id"%TYPE,

999 "area"."id"%TYPE,

1000 "issue"."id"%TYPE )

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

1002

1003

1004 -- Other modified functions:

1005

1006 CREATE OR REPLACE FUNCTION "lock_issue"

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

1008 RETURNS VOID

1009 LANGUAGE 'plpgsql' VOLATILE AS $$

1010 BEGIN

1011 LOCK TABLE "member" IN SHARE MODE;

1012 LOCK TABLE "privilege" IN SHARE MODE;

1013 LOCK TABLE "membership" IN SHARE MODE;

1014 LOCK TABLE "policy" IN SHARE MODE;

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

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

1017 -- share_row_lock_issue(_via_initiative)_trigger functions (which

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

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

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

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

1022 LOCK TABLE "delegation" IN SHARE MODE;

1023 LOCK TABLE "direct_population_snapshot" IN EXCLUSIVE MODE;

1024 LOCK TABLE "delegating_population_snapshot" IN EXCLUSIVE MODE;

1025 LOCK TABLE "direct_interest_snapshot" IN EXCLUSIVE MODE;

1026 LOCK TABLE "delegating_interest_snapshot" IN EXCLUSIVE MODE;

1027 LOCK TABLE "direct_supporter_snapshot" IN EXCLUSIVE MODE;

1028 RETURN;

1029 END;

1030 $$;

1031

1032 CREATE OR REPLACE FUNCTION "calculate_member_counts"()

1033 RETURNS VOID

1034 LANGUAGE 'plpgsql' VOLATILE AS $$

1035 BEGIN

1036 LOCK TABLE "member" IN SHARE MODE;

1037 LOCK TABLE "member_count" IN EXCLUSIVE MODE;

1038 LOCK TABLE "unit" IN EXCLUSIVE MODE;

1039 LOCK TABLE "area" IN EXCLUSIVE MODE;

1040 LOCK TABLE "privilege" IN SHARE MODE;

1041 LOCK TABLE "membership" IN SHARE MODE;

1042 DELETE FROM "member_count";

1043 INSERT INTO "member_count" ("total_count")

1044 SELECT "total_count" FROM "member_count_view";

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

1046 FROM "unit_member_count" AS "view"

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

1048 UPDATE "area" SET

1049 "direct_member_count" = "view"."direct_member_count",

1050 "member_weight" = "view"."member_weight",

1051 "autoreject_weight" = "view"."autoreject_weight"

1052 FROM "area_member_count" AS "view"

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

1054 RETURN;

1055 END;

1056 $$;

1057

1058 CREATE OR REPLACE FUNCTION "create_population_snapshot"

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

1060 RETURNS VOID

1061 LANGUAGE 'plpgsql' VOLATILE AS $$

1062 DECLARE

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

1064 BEGIN

1065 DELETE FROM "direct_population_snapshot"

1066 WHERE "issue_id" = "issue_id_p"

1067 AND "event" = 'periodic';

1068 DELETE FROM "delegating_population_snapshot"

1069 WHERE "issue_id" = "issue_id_p"

1070 AND "event" = 'periodic';

1071 INSERT INTO "direct_population_snapshot"

1072 ("issue_id", "event", "member_id")

1073 SELECT

1074 "issue_id_p" AS "issue_id",

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

1076 "member"."id" AS "member_id"

1077 FROM "issue"

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

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

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

1081 JOIN "privilege"

1082 ON "privilege"."unit_id" = "area"."unit_id"

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

1084 WHERE "issue"."id" = "issue_id_p"

1085 AND "member"."active" AND "privilege"."voting_right"

1086 UNION

1087 SELECT

1088 "issue_id_p" AS "issue_id",

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

1090 "member"."id" AS "member_id"

1091 FROM "issue"

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

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

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

1095 JOIN "privilege"

1096 ON "privilege"."unit_id" = "area"."unit_id"

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

1098 WHERE "issue"."id" = "issue_id_p"

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

1100 FOR "member_id_v" IN

1101 SELECT "member_id" FROM "direct_population_snapshot"

1102 WHERE "issue_id" = "issue_id_p"

1103 AND "event" = 'periodic'

1104 LOOP

1105 UPDATE "direct_population_snapshot" SET

1106 "weight" = 1 +

1107 "weight_of_added_delegations_for_population_snapshot"(

1108 "issue_id_p",

1109 "member_id_v",

1110 '{}'

1111 )

1112 WHERE "issue_id" = "issue_id_p"

1113 AND "event" = 'periodic'

1114 AND "member_id" = "member_id_v";

1115 END LOOP;

1116 RETURN;

1117 END;

1118 $$;

1119

1120 CREATE OR REPLACE FUNCTION "create_interest_snapshot"

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

1122 RETURNS VOID

1123 LANGUAGE 'plpgsql' VOLATILE AS $$

1124 DECLARE

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

1126 BEGIN

1127 DELETE FROM "direct_interest_snapshot"

1128 WHERE "issue_id" = "issue_id_p"

1129 AND "event" = 'periodic';

1130 DELETE FROM "delegating_interest_snapshot"

1131 WHERE "issue_id" = "issue_id_p"

1132 AND "event" = 'periodic';

1133 DELETE FROM "direct_supporter_snapshot"

1134 WHERE "issue_id" = "issue_id_p"

1135 AND "event" = 'periodic';

1136 INSERT INTO "direct_interest_snapshot"

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

1138 SELECT

1139 "issue_id_p" AS "issue_id",

1140 'periodic' AS "event",

1141 "member"."id" AS "member_id",

1142 "interest"."voting_requested"

1143 FROM "issue"

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

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

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

1147 JOIN "privilege"

1148 ON "privilege"."unit_id" = "area"."unit_id"

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

1150 WHERE "issue"."id" = "issue_id_p"

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

1152 FOR "member_id_v" IN

1153 SELECT "member_id" FROM "direct_interest_snapshot"

1154 WHERE "issue_id" = "issue_id_p"

1155 AND "event" = 'periodic'

1156 LOOP

1157 UPDATE "direct_interest_snapshot" SET

1158 "weight" = 1 +

1159 "weight_of_added_delegations_for_interest_snapshot"(

1160 "issue_id_p",

1161 "member_id_v",

1162 '{}'

1163 )

1164 WHERE "issue_id" = "issue_id_p"

1165 AND "event" = 'periodic'

1166 AND "member_id" = "member_id_v";

1167 END LOOP;

1168 INSERT INTO "direct_supporter_snapshot"

1169 ( "issue_id", "initiative_id", "event", "member_id",

1170 "informed", "satisfied" )

1171 SELECT

1172 "issue_id_p" AS "issue_id",

1173 "initiative"."id" AS "initiative_id",

1174 'periodic' AS "event",

1175 "supporter"."member_id" AS "member_id",

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

1177 NOT EXISTS (

1178 SELECT NULL FROM "critical_opinion"

1179 WHERE "initiative_id" = "initiative"."id"

1180 AND "member_id" = "supporter"."member_id"

1181 ) AS "satisfied"

1182 FROM "initiative"

1183 JOIN "supporter"

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

1185 JOIN "current_draft"

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

1187 JOIN "direct_interest_snapshot"

1188 ON "supporter"."member_id" = "direct_interest_snapshot"."member_id"

1189 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"

1190 AND "event" = 'periodic'

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

1192 RETURN;

1193 END;

1194 $$;

1195

1196 CREATE OR REPLACE FUNCTION "freeze_after_snapshot"

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

1198 RETURNS VOID

1199 LANGUAGE 'plpgsql' VOLATILE AS $$

1200 DECLARE

1201 "issue_row" "issue"%ROWTYPE;

1202 "policy_row" "policy"%ROWTYPE;

1203 "initiative_row" "initiative"%ROWTYPE;

1204 BEGIN

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

1206 SELECT * INTO "policy_row"

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

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

1209 FOR "initiative_row" IN

1210 SELECT * FROM "initiative"

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

1212 LOOP

1213 IF

1214 "initiative_row"."satisfied_supporter_count" > 0 AND

1215 "initiative_row"."satisfied_supporter_count" *

1216 "policy_row"."initiative_quorum_den" >=

1217 "issue_row"."population" * "policy_row"."initiative_quorum_num"

1218 THEN

1219 UPDATE "initiative" SET "admitted" = TRUE

1220 WHERE "id" = "initiative_row"."id";

1221 ELSE

1222 UPDATE "initiative" SET "admitted" = FALSE

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

1224 END IF;

1225 END LOOP;

1226 IF EXISTS (

1227 SELECT NULL FROM "initiative"

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

1229 ) THEN

1230 UPDATE "issue" SET

1231 "state" = 'voting',

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

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

1234 "fully_frozen" = now()

1235 WHERE "id" = "issue_id_p";

1236 ELSE

1237 UPDATE "issue" SET

1238 "state" = 'canceled_no_initiative_admitted',

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

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

1241 "fully_frozen" = now(),

1242 "closed" = now(),

1243 "ranks_available" = TRUE

1244 WHERE "id" = "issue_id_p";

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

1246 -- issue state has been manipulated

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

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

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

1250 END IF;

1251 RETURN;

1252 END;

1253 $$;

1254

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

1256 RETURNS VOID

1257 LANGUAGE 'plpgsql' VOLATILE AS $$

1258 DECLARE

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

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

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

1262 BEGIN

1263 PERFORM "lock_issue"("issue_id_p");

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

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

1266 DELETE FROM "delegating_voter"

1267 WHERE "issue_id" = "issue_id_p";

1268 DELETE FROM "direct_voter"

1269 WHERE "issue_id" = "issue_id_p"

1270 AND "autoreject" = TRUE;

1271 DELETE FROM "direct_voter"

1272 USING (

1273 SELECT

1274 "direct_voter"."member_id"

1275 FROM "direct_voter"

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

1277 LEFT JOIN "privilege"

1278 ON "privilege"."unit_id" = "unit_id_v"

1279 AND "privilege"."member_id" = "direct_voter"."member_id"

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

1281 "member"."active" = FALSE OR

1282 "privilege"."voting_right" ISNULL OR

1283 "privilege"."voting_right" = FALSE

1284 )

1285 ) AS "subquery"

1286 WHERE "direct_voter"."issue_id" = "issue_id_p"

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

1288 UPDATE "direct_voter" SET "weight" = 1

1289 WHERE "issue_id" = "issue_id_p";

1290 PERFORM "add_vote_delegations"("issue_id_p");

1291 FOR "member_id_v" IN

1292 SELECT "interest"."member_id"

1293 FROM "interest"

1294 JOIN "member"

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

1296 JOIN "privilege"

1297 ON "privilege"."unit_id" = "unit_id_v"

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

1299 LEFT JOIN "direct_voter"

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

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

1302 LEFT JOIN "delegating_voter"

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

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

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

1306 AND "interest"."autoreject" = TRUE

1307 AND "member"."active"

1308 AND "privilege"."voting_right"

1309 AND "direct_voter"."member_id" ISNULL

1310 AND "delegating_voter"."member_id" ISNULL

1311 UNION SELECT "membership"."member_id"

1312 FROM "membership"

1313 JOIN "member"

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

1315 JOIN "privilege"

1316 ON "privilege"."unit_id" = "unit_id_v"

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

1318 LEFT JOIN "interest"

1319 ON "membership"."member_id" = "interest"."member_id"

1320 AND "interest"."issue_id" = "issue_id_p"

1321 LEFT JOIN "direct_voter"

1322 ON "membership"."member_id" = "direct_voter"."member_id"

1323 AND "direct_voter"."issue_id" = "issue_id_p"

1324 LEFT JOIN "delegating_voter"

1325 ON "membership"."member_id" = "delegating_voter"."member_id"

1326 AND "delegating_voter"."issue_id" = "issue_id_p"

1327 WHERE "membership"."area_id" = "area_id_v"

1328 AND "membership"."autoreject" = TRUE

1329 AND "member"."active"

1330 AND "privilege"."voting_right"

1331 AND "interest"."autoreject" ISNULL

1332 AND "direct_voter"."member_id" ISNULL

1333 AND "delegating_voter"."member_id" ISNULL

1334 LOOP

1335 INSERT INTO "direct_voter"

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

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

1338 INSERT INTO "vote" (

1339 "member_id",

1340 "issue_id",

1341 "initiative_id",

1342 "grade"

1343 ) SELECT

1344 "member_id_v" AS "member_id",

1345 "issue_id_p" AS "issue_id",

1346 "id" AS "initiative_id",

1347 -1 AS "grade"

1348 FROM "initiative"

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

1350 END LOOP;

1351 PERFORM "add_vote_delegations"("issue_id_p");

1352 UPDATE "issue" SET

1353 "state" = 'calculation',

1354 "closed" = now(),

1355 "voter_count" = (

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

1357 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"

1358 )

1359 WHERE "id" = "issue_id_p";

1360 UPDATE "initiative" SET

1361 "positive_votes" = "vote_counts"."positive_votes",

1362 "negative_votes" = "vote_counts"."negative_votes",

1363 "agreed" = CASE WHEN "majority_strict" THEN

1364 "vote_counts"."positive_votes" * "majority_den" >

1365 "majority_num" *

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

1367 ELSE

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

1369 "majority_num" *

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

1371 END

1372 FROM

1373 ( SELECT

1374 "initiative"."id" AS "initiative_id",

1375 coalesce(

1376 sum(

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

1378 ),

1379 0

1380 ) AS "positive_votes",

1381 coalesce(

1382 sum(

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

1384 ),

1385 0

1386 ) AS "negative_votes"

1387 FROM "initiative"

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

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

1390 LEFT JOIN "direct_voter"

1391 ON "direct_voter"."issue_id" = "initiative"."issue_id"

1392 LEFT JOIN "vote"

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

1394 AND "vote"."member_id" = "direct_voter"."member_id"

1395 WHERE "initiative"."issue_id" = "issue_id_p"

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

1397 GROUP BY "initiative"."id"

1398 ) AS "vote_counts",

1399 "issue",

1400 "policy"

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

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

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

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

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

1406 INSERT INTO "battle" (

1407 "issue_id",

1408 "winning_initiative_id", "losing_initiative_id",

1409 "count"

1410 ) SELECT

1411 "issue_id",

1412 "winning_initiative_id", "losing_initiative_id",

1413 "count"

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

1415 END;

1416 $$;

1417

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

1419 RETURNS VOID

1420 LANGUAGE 'plpgsql' VOLATILE AS $$

1421 DECLARE

1422 "dimension_v" INTEGER;

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

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

1425 "i" INTEGER;

1426 "j" INTEGER;

1427 "k" INTEGER;

1428 "battle_row" "battle"%ROWTYPE;

1429 "rank_ary" INT4[];

1430 "rank_v" INT4;

1431 "done_v" INTEGER;

1432 "winners_ary" INTEGER[];

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

1434 BEGIN

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

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

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

1438 IF "dimension_v" = 1 THEN

1439 UPDATE "initiative" SET "rank" = 1

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

1441 ELSIF "dimension_v" > 1 THEN

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

1443 -- comparison:

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

1445 "i" := 1;

1446 "j" := 2;

1447 FOR "battle_row" IN

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

1449 ORDER BY "winning_initiative_id", "losing_initiative_id"

1450 LOOP

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

1452 IF "j" = "dimension_v" THEN

1453 "i" := "i" + 1;

1454 "j" := 1;

1455 ELSE

1456 "j" := "j" + 1;

1457 IF "j" = "i" THEN

1458 "j" := "j" + 1;

1459 END IF;

1460 END IF;

1461 END LOOP;

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

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

1464 END IF;

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

1466 -- function:

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

1468 "i" := 1;

1469 LOOP

1470 "j" := 1;

1471 LOOP

1472 IF "i" != "j" THEN

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

1474 "vote_matrix"["i"]["j"],

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

1476 );

1477 END IF;

1478 EXIT WHEN "j" = "dimension_v";

1479 "j" := "j" + 1;

1480 END LOOP;

1481 EXIT WHEN "i" = "dimension_v";

1482 "i" := "i" + 1;

1483 END LOOP;

1484 -- Find best paths:

1485 "i" := 1;

1486 LOOP

1487 "j" := 1;

1488 LOOP

1489 IF "i" != "j" THEN

1490 "k" := 1;

1491 LOOP

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

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

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

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

1496 END IF;

1497 ELSE

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

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

1500 END IF;

1501 END IF;

1502 END IF;

1503 EXIT WHEN "k" = "dimension_v";

1504 "k" := "k" + 1;

1505 END LOOP;

1506 END IF;

1507 EXIT WHEN "j" = "dimension_v";

1508 "j" := "j" + 1;

1509 END LOOP;

1510 EXIT WHEN "i" = "dimension_v";

1511 "i" := "i" + 1;

1512 END LOOP;

1513 -- Determine order of winners:

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

1515 "rank_v" := 1;

1516 "done_v" := 0;

1517 LOOP

1518 "winners_ary" := '{}';

1519 "i" := 1;

1520 LOOP

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

1522 "j" := 1;

1523 LOOP

1524 IF

1525 "i" != "j" AND

1526 "rank_ary"["j"] ISNULL AND

1527 "matrix"["j"]["i"] > "matrix"["i"]["j"]

1528 THEN

1529 -- someone else is better

1530 EXIT;

1531 END IF;

1532 IF "j" = "dimension_v" THEN

1533 -- noone is better

1534 "winners_ary" := "winners_ary" || "i";

1535 EXIT;

1536 END IF;

1537 "j" := "j" + 1;

1538 END LOOP;

1539 END IF;

1540 EXIT WHEN "i" = "dimension_v";

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

1542 END LOOP;

1543 "i" := 1;

1544 LOOP

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

1546 "done_v" := "done_v" + 1;

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

1548 "i" := "i" + 1;

1549 END LOOP;

1550 EXIT WHEN "done_v" = "dimension_v";

1551 "rank_v" := "rank_v" + 1;

1552 END LOOP;

1553 -- write preliminary ranks:

1554 "i" := 1;

1555 FOR "initiative_id_v" IN

1556 SELECT "id" FROM "initiative"

1557 WHERE "issue_id" = "issue_id_p" AND "agreed"

1558 ORDER BY "id"

1559 LOOP

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

1561 WHERE "id" = "initiative_id_v";

1562 "i" := "i" + 1;

1563 END LOOP;

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

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

1566 END IF;

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

1568 "rank_v" := 1;

1569 FOR "initiative_id_v" IN

1570 SELECT "id" FROM "initiative"

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

1572 ORDER BY

1573 "rank",

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

1575 "id"

1576 LOOP

1577 UPDATE "initiative" SET "rank" = "rank_v"

1578 WHERE "id" = "initiative_id_v";

1579 "rank_v" := "rank_v" + 1;

1580 END LOOP;

1581 END IF;

1582 -- mark issue as finished

1583 UPDATE "issue" SET

1584 "state" =

1585 CASE WHEN "dimension_v" = 0 THEN

1586 'finished_without_winner'::"issue_state"

1587 ELSE

1588 'finished_with_winner'::"issue_state"

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 "state" = 'voting',

1752 "closed" = NULL,

1753 "ranks_available" = FALSE

1754 WHERE "id" = "issue_id_p";

1755 DELETE FROM "issue_comment"

1756 WHERE "issue_id" = "issue_id_p";

1757 DELETE FROM "voting_comment"

1758 WHERE "issue_id" = "issue_id_p";

1759 DELETE FROM "delegating_voter"

1760 WHERE "issue_id" = "issue_id_p";

1761 DELETE FROM "direct_voter"

1762 WHERE "issue_id" = "issue_id_p";

1763 DELETE FROM "delegating_interest_snapshot"

1764 WHERE "issue_id" = "issue_id_p";

1765 DELETE FROM "direct_interest_snapshot"

1766 WHERE "issue_id" = "issue_id_p";

1767 DELETE FROM "delegating_population_snapshot"

1768 WHERE "issue_id" = "issue_id_p";

1769 DELETE FROM "direct_population_snapshot"

1770 WHERE "issue_id" = "issue_id_p";

1771 DELETE FROM "non_voter"

1772 WHERE "issue_id" = "issue_id_p";

1773 DELETE FROM "delegation"

1774 WHERE "issue_id" = "issue_id_p";

1775 DELETE FROM "supporter"

1776 WHERE "issue_id" = "issue_id_p";

1777 UPDATE "issue" SET

1778 "state" = "issue_row"."state",

1779 "closed" = "issue_row"."closed",

1780 "ranks_available" = "issue_row"."ranks_available",

1781 "cleaned" = now()

1782 WHERE "id" = "issue_id_p";

1783 END IF;

1784 RETURN;

1785 END;

1786 $$;

1787

1788 CREATE OR REPLACE FUNCTION "check_issue"

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

1790 RETURNS VOID

1791 LANGUAGE 'plpgsql' VOLATILE AS $$

1792 DECLARE

1793 "issue_row" "issue"%ROWTYPE;

1794 "policy_row" "policy"%ROWTYPE;

1795 "voting_requested_v" BOOLEAN;

1796 BEGIN

1797 PERFORM "lock_issue"("issue_id_p");

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

1799 -- only process open issues:

1800 IF "issue_row"."closed" ISNULL THEN

1801 SELECT * INTO "policy_row" FROM "policy"

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

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

1804 IF "issue_row"."fully_frozen" ISNULL THEN

1805 PERFORM "create_snapshot"("issue_id_p");

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

1807 END IF;

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

1809 IF "issue_row"."accepted" ISNULL THEN

1810 IF EXISTS (

1811 SELECT NULL FROM "initiative"

1812 WHERE "issue_id" = "issue_id_p"

1813 AND "supporter_count" > 0

1814 AND "supporter_count" * "policy_row"."issue_quorum_den"

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

1816 ) THEN

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

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

1819 -- NOTE: "issue_row" used later

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

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

1822 UPDATE "issue" SET

1823 "state" = "issue_row"."state",

1824 "accepted" = "issue_row"."accepted"

1825 WHERE "id" = "issue_row"."id";

1826 ELSIF

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

1828 THEN

1829 -- close issues, if admission time has expired

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

1831 UPDATE "issue" SET

1832 "state" = 'canceled_issue_not_accepted',

1833 "closed" = now()

1834 WHERE "id" = "issue_row"."id";

1835 END IF;

1836 END IF;

1837 -- eventually half freeze issues:

1838 IF

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

1840 "issue_row"."accepted" NOTNULL AND

1841 "issue_row"."half_frozen" ISNULL

1842 THEN

1843 SELECT

1844 CASE

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

1846 TRUE

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

1848 FALSE

1849 ELSE NULL

1850 END

1851 INTO "voting_requested_v"

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

1853 IF

1854 "voting_requested_v" OR (

1855 "voting_requested_v" ISNULL AND

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

1857 )

1858 THEN

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

1860 -- NOTE: "issue_row" used later

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

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

1863 UPDATE "issue" SET

1864 "state" = "issue_row"."state",

1865 "half_frozen" = "issue_row"."half_frozen"

1866 WHERE "id" = "issue_row"."id";

1867 END IF;

1868 END IF;

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

1870 IF

1871 "issue_row"."closed" ISNULL AND

1872 NOT EXISTS (

1873 -- all initiatives are revoked

1874 SELECT NULL FROM "initiative"

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

1876 ) AND (

1877 -- and issue has not been accepted yet

1878 "issue_row"."accepted" ISNULL OR

1879 NOT EXISTS (

1880 -- or no initiatives have been revoked lately

1881 SELECT NULL FROM "initiative"

1882 WHERE "issue_id" = "issue_id_p"

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

1884 ) OR (

1885 -- or verification time has elapsed

1886 "issue_row"."half_frozen" NOTNULL AND

1887 "issue_row"."fully_frozen" ISNULL AND

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

1889 )

1890 )

1891 THEN

1892 -- NOTE: "issue_row" used later

1893 IF "issue_row"."accepted" ISNULL THEN

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

1895 ELSIF "issue_row"."half_frozen" ISNULL THEN

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

1897 ELSE

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

1899 END IF;

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

1901 UPDATE "issue" SET

1902 "state" = "issue_row"."state",

1903 "closed" = "issue_row"."closed"

1904 WHERE "id" = "issue_row"."id";

1905 END IF;

1906 -- fully freeze issue after verification time:

1907 IF

1908 "issue_row"."half_frozen" NOTNULL AND

1909 "issue_row"."fully_frozen" ISNULL AND

1910 "issue_row"."closed" ISNULL AND

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

1912 THEN

1913 PERFORM "freeze_after_snapshot"("issue_id_p");

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

1915 END IF;

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

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

1918 IF

1919 "issue_row"."closed" ISNULL AND

1920 "issue_row"."fully_frozen" NOTNULL AND

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

1922 THEN

1923 PERFORM "close_voting"("issue_id_p");

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

1925 PERFORM "calculate_ranks"("issue_id_p");

1926 END IF;

1927 END IF;

1928 RETURN;

1929 END;

1930 $$;

1931

1932 CREATE OR REPLACE FUNCTION "delete_private_data"()

1933 RETURNS VOID

1934 LANGUAGE 'plpgsql' VOLATILE AS $$

1935 BEGIN

1936 UPDATE "member" SET

1937 "last_login" = NULL,

1938 "login" = NULL,

1939 "password" = NULL,

1940 "notify_email" = NULL,

1941 "notify_email_unconfirmed" = NULL,

1942 "notify_email_secret" = NULL,

1943 "notify_email_secret_expiry" = NULL,

1944 "notify_email_lock_expiry" = NULL,

1945 "password_reset_secret" = NULL,

1946 "password_reset_secret_expiry" = NULL,

1947 "organizational_unit" = NULL,

1948 "internal_posts" = NULL,

1949 "realname" = NULL,

1950 "birthday" = NULL,

1951 "address" = NULL,

1952 "email" = NULL,

1953 "xmpp_address" = NULL,

1954 "website" = NULL,

1955 "phone" = NULL,

1956 "mobile_phone" = NULL,

1957 "profession" = NULL,

1958 "external_memberships" = NULL,

1959 "external_posts" = NULL,

1960 "statement" = NULL;

1961 -- "text_search_data" is updated by triggers

1962 DELETE FROM "invite_code";

1963 DELETE FROM "setting";

1964 DELETE FROM "setting_map";

1965 DELETE FROM "member_relation_setting";

1966 DELETE FROM "member_image";

1967 DELETE FROM "contact";

1968 DELETE FROM "ignored_member";

1969 DELETE FROM "session";

1970 DELETE FROM "area_setting";

1971 DELETE FROM "issue_setting";

1972 DELETE FROM "ignored_initiative";

1973 DELETE FROM "initiative_setting";

1974 DELETE FROM "suggestion_setting";

1975 DELETE FROM "non_voter";

1976 DELETE FROM "direct_voter" USING "issue"

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

1978 AND "issue"."closed" ISNULL;

1979 RETURN;

1980 END;

1981 $$;

1982

1983

1984 -- Delete old "delegation_scope" TYPE:

1985

1986 DROP TYPE "delegation_scope_old";

1987

1988

1989 COMMIT;

1990

1991

1992 -- Generate issue states and add constraints:

1993

1994 UPDATE "issue" SET "state" =

1995 CASE

1996 WHEN "closed" ISNULL THEN

1997 CASE

1998 WHEN "accepted" ISNULL THEN

1999 'admission'::"issue_state"

2000 WHEN "half_frozen" ISNULL THEN

2001 'discussion'::"issue_state"

2002 WHEN "fully_frozen" ISNULL THEN

2003 'verification'::"issue_state"

2004 ELSE

2005 'voting'::"issue_state"

2006 END

2007 WHEN "fully_frozen" NOTNULL THEN

2008 CASE

2009 WHEN "fully_frozen" = "closed" THEN

2010 'canceled_no_initiative_admitted'::"issue_state"

2011 ELSE

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

2013 END

2014 WHEN "half_frozen" NOTNULL THEN

2015 'canceled_after_revocation_during_verification'::"issue_state"

2016 WHEN "accepted" NOTNULL THEN

2017 'canceled_after_revocation_during_discussion'::"issue_state"

2018 ELSE

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

2020 END;

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

2022 FROM "initiative"

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

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

2025 AND "initiative"."agreed";

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

2027 FROM "initiative"

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

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

2030 AND "initiative"."revoked" ISNULL;

2031

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

2033

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

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

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

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

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

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

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

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

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

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

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

2045 ) AND (

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

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

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

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

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

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

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

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

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

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

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

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

2058 ));

2059

2060

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

2062

2063 UPDATE "initiative" SET "revoked_by_member_id" = "author_id"

2064 FROM "current_draft"

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

2066 AND "initiative"."revoked" NOTNULL;

2067

2068 ALTER TABLE "initiative" ADD

2069 CONSTRAINT "all_or_none_of_revoked_and_revoked_by_member_id_must_be_null"

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

2071

2072

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

2074

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

2076

2077 ALTER TABLE "delegation" ADD CONSTRAINT "area_id_and_issue_id_set_according_to_scope"

2078 CHECK (

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

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

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

2082

2083

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

2085

2086 DELETE FROM "event";

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

2088

2089 INSERT INTO "event"

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

2091 "initiative_id", "draft_id", "suggestion_id" )

2092 SELECT * FROM (

2093 SELECT * FROM (

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

2095 "timeline"."occurrence",

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

2097 'initiative_created_in_new_issue'::"event_type"

2098 ELSE

2099 'initiative_created_in_existing_issue'::"event_type"

2100 END,

2101 "draft"."author_id",

2102 "issue"."id",

2103 CASE

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

2105 'admission'::"issue_state"

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

2107 'discussion'::"issue_state"

2108 ELSE

2109 'verification'::"issue_state"

2110 END,

2111 "initiative"."id",

2112 "draft"."id",

2113 NULL::INT8

2114 FROM "timeline"

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

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

2117 LEFT JOIN "timeline" AS "issue_creation"

2118 ON "initiative"."issue_id" = "issue_creation"."issue_id"

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

2120 AND "timeline"."occurrence" = "issue_creation"."occurrence"

2121 JOIN "draft"

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

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

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

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

2126 UNION ALL

2127 SELECT

2128 "timeline"."occurrence",

2129 'issue_state_changed'::"event_type",

2130 NULL,

2131 "issue"."id",

2132 CASE

2133 WHEN "timeline"."event" IN (

2134 'issue_canceled',

2135 'issue_finished_without_voting',

2136 'issue_finished_after_voting'

2137 ) THEN

2138 "issue"."state"

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

2140 'discussion'::"issue_state"

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

2142 'verification'::"issue_state"

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

2144 'voting'::"issue_state"

2145 END,

2146 NULL,

2147 NULL,

2148 NULL

2149 FROM "timeline"

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

2151 WHERE "timeline"."event" IN (

2152 'issue_canceled',

2153 'issue_accepted',

2154 'issue_half_frozen',

2155 'issue_finished_without_voting',

2156 'issue_voting_started',

2157 'issue_finished_after_voting' )

2158 UNION ALL

2159 SELECT

2160 "timeline"."occurrence",

2161 'initiative_revoked'::"event_type",

2162 "initiative"."revoked_by_member_id",

2163 "issue"."id",

2164 CASE

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

2166 'admission'::"issue_state"

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

2168 'discussion'::"issue_state"

2169 ELSE

2170 'verification'::"issue_state"

2171 END,

2172 "initiative"."id",

2173 "current_draft"."id",

2174 NULL

2175 FROM "timeline"

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

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

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

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

2180 UNION ALL

2181 SELECT

2182 "timeline"."occurrence",

2183 'new_draft_created'::"event_type",

2184 "draft"."author_id",

2185 "issue"."id",

2186 CASE

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

2188 'admission'::"issue_state"

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

2190 'discussion'::"issue_state"

2191 ELSE

2192 'verification'::"issue_state"

2193 END,

2194 "initiative"."id",

2195 "draft"."id",

2196 NULL

2197 FROM "timeline"

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

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

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

2201 LEFT JOIN "timeline" AS "initiative_creation"

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

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

2204 AND "timeline"."occurrence" = "initiative_creation"."occurrence"

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

2206 AND "initiative_creation"."initiative_id" ISNULL

2207 UNION ALL

2208 SELECT

2209 "timeline"."occurrence",

2210 'suggestion_created'::"event_type",

2211 "suggestion"."author_id",

2212 "issue"."id",

2213 CASE

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

2215 'admission'::"issue_state"

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

2217 'discussion'::"issue_state"

2218 ELSE

2219 'verification'::"issue_state"

2220 END,

2221 "initiative"."id",

2222 NULL,

2223 "suggestion"."id"

2224 FROM "timeline"

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

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

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

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

2229 ) AS "subquery"

2230 ORDER BY "occurrence";

2231

2232