liquid_feedback_core: 725133783c8a update/core-update.v1.3.1-v1.4.0

liquid_feedback_core

view update/core-update.v1.3.1-v1.4.0_rc1.sql @ 157:725133783c8a

Do not ignore the "policy"."no_multistage_majority" flag: It does not affect the winner but affects the "initiative"."eligible" flag

author	jbe
date	Sat Jun 04 11:50:44 2011 +0200 (2011-06-04)
parents	0144b703b261
children	eee75cff3e5a

line source

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

4 -- Update version information:

6 CREATE OR REPLACE VIEW "liquid_feedback_version" AS

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

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

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

13 CREATE TYPE "notify_level" AS ENUM

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

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

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

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

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

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

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

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

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

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

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

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

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

38 -- New table "ignored_member":

40 CREATE TABLE "ignored_member" (

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

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

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

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

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

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

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

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

54 CREATE TABLE "unit" (

55 "id" SERIAL4 PRIMARY KEY,

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

57 "active" BOOLEAN NOT NULL DEFAULT TRUE,

58 "name" TEXT NOT NULL,

59 "description" TEXT NOT NULL DEFAULT '',

60 "member_count" INT4,

61 "text_search_data" TSVECTOR );

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

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

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

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

66 CREATE TRIGGER "update_text_search_data"

67 BEFORE INSERT OR UPDATE ON "unit"

68 FOR EACH ROW EXECUTE PROCEDURE

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

70 "name", "description" );

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

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

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

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

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

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

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

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

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

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

90 -- Issue states:

92 CREATE TYPE "issue_state" AS ENUM (

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

94 'canceled_revoked_before_accepted',

95 'canceled_issue_not_accepted',

96 'canceled_after_revocation_during_discussion',

97 'canceled_after_revocation_during_verification',

98 'calculation',

99 'canceled_no_initiative_admitted',

100 'finished_without_winner', 'finished_with_winner');

101

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

103

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

105

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

107

108

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

110

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

112

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

114

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

116

117

118 -- New table "ignored_initiative":

119

120 CREATE TABLE "ignored_initiative" (

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

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

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

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

125

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

127

128

129 -- New table "invite_code_unit":

130

131 CREATE TABLE "invite_code_unit" (

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

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

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

135

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

137

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

139 SELECT "id" AS "invite_code_id", 1 AS "unit_id" 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 "id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";

1265 SELECT "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 LEFT JOIN "direct_voter"

1297 ON "interest"."member_id" = "direct_voter"."member_id"

1298 AND "interest"."issue_id" = "direct_voter"."issue_id"

1299 LEFT JOIN "delegating_voter"

1300 ON "interest"."member_id" = "delegating_voter"."member_id"

1301 AND "interest"."issue_id" = "delegating_voter"."issue_id"

1302 WHERE "interest"."issue_id" = "issue_id_p"

1303 AND "interest"."autoreject" = TRUE

1304 AND "member"."active"

1305 AND "direct_voter"."member_id" ISNULL

1306 AND "delegating_voter"."member_id" ISNULL

1307 UNION SELECT "membership"."member_id"

1308 FROM "membership"

1309 JOIN "member"

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

1311 LEFT JOIN "interest"

1312 ON "membership"."member_id" = "interest"."member_id"

1313 AND "interest"."issue_id" = "issue_id_p"

1314 LEFT JOIN "direct_voter"

1315 ON "membership"."member_id" = "direct_voter"."member_id"

1316 AND "direct_voter"."issue_id" = "issue_id_p"

1317 LEFT JOIN "delegating_voter"

1318 ON "membership"."member_id" = "delegating_voter"."member_id"

1319 AND "delegating_voter"."issue_id" = "issue_id_p"

1320 WHERE "membership"."area_id" = "area_id_v"

1321 AND "membership"."autoreject" = TRUE

1322 AND "member"."active"

1323 AND "interest"."autoreject" ISNULL

1324 AND "direct_voter"."member_id" ISNULL

1325 AND "delegating_voter"."member_id" ISNULL

1326 LOOP

1327 INSERT INTO "direct_voter"

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

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

1330 INSERT INTO "vote" (

1331 "member_id",

1332 "issue_id",

1333 "initiative_id",

1334 "grade"

1335 ) SELECT

1336 "member_id_v" AS "member_id",

1337 "issue_id_p" AS "issue_id",

1338 "id" AS "initiative_id",

1339 -1 AS "grade"

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

1341 END LOOP;

1342 PERFORM "add_vote_delegations"("issue_id_p");

1343 UPDATE "issue" SET

1344 "state" = 'calculation',

1345 "closed" = now(),

1346 "voter_count" = (

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

1348 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"

1349 )

1350 WHERE "id" = "issue_id_p";

1351 UPDATE "initiative" SET

1352 "positive_votes" = "vote_counts"."positive_votes",

1353 "negative_votes" = "vote_counts"."negative_votes",

1354 "agreed" = CASE WHEN "majority_strict" THEN

1355 "vote_counts"."positive_votes" * "majority_den" >

1356 "majority_num" *

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

1358 ELSE

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

1360 "majority_num" *

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

1362 END

1363 FROM

1364 ( SELECT

1365 "initiative"."id" AS "initiative_id",

1366 coalesce(

1367 sum(

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

1369 ),

1370 0

1371 ) AS "positive_votes",

1372 coalesce(

1373 sum(

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

1375 ),

1376 0

1377 ) AS "negative_votes"

1378 FROM "initiative"

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

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

1381 LEFT JOIN "direct_voter"

1382 ON "direct_voter"."issue_id" = "initiative"."issue_id"

1383 LEFT JOIN "vote"

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

1385 AND "vote"."member_id" = "direct_voter"."member_id"

1386 WHERE "initiative"."issue_id" = "issue_id_p"

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

1388 GROUP BY "initiative"."id"

1389 ) AS "vote_counts",

1390 "issue",

1391 "policy"

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

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

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

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

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

1397 INSERT INTO "battle" (

1398 "issue_id",

1399 "winning_initiative_id", "losing_initiative_id",

1400 "count"

1401 ) SELECT

1402 "issue_id",

1403 "winning_initiative_id", "losing_initiative_id",

1404 "count"

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

1406 END;

1407 $$;

1408

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

1410 RETURNS VOID

1411 LANGUAGE 'plpgsql' VOLATILE AS $$

1412 DECLARE

1413 "dimension_v" INTEGER;

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

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

1416 "i" INTEGER;

1417 "j" INTEGER;

1418 "k" INTEGER;

1419 "battle_row" "battle"%ROWTYPE;

1420 "rank_ary" INT4[];

1421 "rank_v" INT4;

1422 "done_v" INTEGER;

1423 "winners_ary" INTEGER[];

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

1425 BEGIN

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

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

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

1429 IF "dimension_v" = 1 THEN

1430 UPDATE "initiative" SET "rank" = 1

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

1432 ELSIF "dimension_v" > 1 THEN

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

1434 -- comparison:

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

1436 "i" := 1;

1437 "j" := 2;

1438 FOR "battle_row" IN

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

1440 ORDER BY "winning_initiative_id", "losing_initiative_id"

1441 LOOP

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

1443 IF "j" = "dimension_v" THEN

1444 "i" := "i" + 1;

1445 "j" := 1;

1446 ELSE

1447 "j" := "j" + 1;

1448 IF "j" = "i" THEN

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

1450 END IF;

1451 END IF;

1452 END LOOP;

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

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

1455 END IF;

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

1457 -- function:

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

1459 "i" := 1;

1460 LOOP

1461 "j" := 1;

1462 LOOP

1463 IF "i" != "j" THEN

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

1465 "vote_matrix"["i"]["j"],

1466 "vote_matrix"["j"]["i"]

1467 );

1468 END IF;

1469 EXIT WHEN "j" = "dimension_v";

1470 "j" := "j" + 1;

1471 END LOOP;

1472 EXIT WHEN "i" = "dimension_v";

1473 "i" := "i" + 1;

1474 END LOOP;

1475 -- Find best paths:

1476 "i" := 1;

1477 LOOP

1478 "j" := 1;

1479 LOOP

1480 IF "i" != "j" THEN

1481 "k" := 1;

1482 LOOP

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

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

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

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

1487 END IF;

1488 ELSE

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

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

1491 END IF;

1492 END IF;

1493 END IF;

1494 EXIT WHEN "k" = "dimension_v";

1495 "k" := "k" + 1;

1496 END LOOP;

1497 END IF;

1498 EXIT WHEN "j" = "dimension_v";

1499 "j" := "j" + 1;

1500 END LOOP;

1501 EXIT WHEN "i" = "dimension_v";

1502 "i" := "i" + 1;

1503 END LOOP;

1504 -- Determine order of winners:

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

1506 "rank_v" := 1;

1507 "done_v" := 0;

1508 LOOP

1509 "winners_ary" := '{}';

1510 "i" := 1;

1511 LOOP

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

1513 "j" := 1;

1514 LOOP

1515 IF

1516 "i" != "j" AND

1517 "rank_ary"["j"] ISNULL AND

1518 "matrix"["j"]["i"] > "matrix"["i"]["j"]

1519 THEN

1520 -- someone else is better

1521 EXIT;

1522 END IF;

1523 IF "j" = "dimension_v" THEN

1524 -- noone is better

1525 "winners_ary" := "winners_ary" || "i";

1526 EXIT;

1527 END IF;

1528 "j" := "j" + 1;

1529 END LOOP;

1530 END IF;

1531 EXIT WHEN "i" = "dimension_v";

1532 "i" := "i" + 1;

1533 END LOOP;

1534 "i" := 1;

1535 LOOP

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

1537 "done_v" := "done_v" + 1;

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

1539 "i" := "i" + 1;

1540 END LOOP;

1541 EXIT WHEN "done_v" = "dimension_v";

1542 "rank_v" := "rank_v" + 1;

1543 END LOOP;

1544 -- write preliminary ranks:

1545 "i" := 1;

1546 FOR "initiative_id_v" IN

1547 SELECT "id" FROM "initiative"

1548 WHERE "issue_id" = "issue_id_p" AND "agreed"

1549 ORDER BY "id"

1550 LOOP

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

1552 WHERE "id" = "initiative_id_v";

1553 "i" := "i" + 1;

1554 END LOOP;

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

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

1557 END IF;

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

1559 "rank_v" := 1;

1560 FOR "initiative_id_v" IN

1561 SELECT "id" FROM "initiative"

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

1563 ORDER BY

1564 "rank",

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

1566 "id"

1567 LOOP

1568 UPDATE "initiative" SET "rank" = "rank_v"

1569 WHERE "id" = "initiative_id_v";

1570 "rank_v" := "rank_v" + 1;

1571 END LOOP;

1572 END IF;

1573 -- mark issue as finished

1574 UPDATE "issue" SET

1575 "state" =

1576 CASE WHEN "dimension_v" = 0 THEN

1577 'finished_without_winner'::"issue_state"

1578 ELSE

1579 'finished_with_winner'::"issue_state"

1580 END,

1581 "ranks_available" = TRUE

1582 WHERE "id" = "issue_id_p";

1583 RETURN;

1584 END;

1585 $$;

1586

1587 CREATE OR REPLACE FUNCTION "check_issue"

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

1589 RETURNS VOID

1590 LANGUAGE 'plpgsql' VOLATILE AS $$

1591 DECLARE

1592 "issue_row" "issue"%ROWTYPE;

1593 "policy_row" "policy"%ROWTYPE;

1594 "voting_requested_v" BOOLEAN;

1595 BEGIN

1596 PERFORM "lock_issue"("issue_id_p");

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

1598 -- only process open issues:

1599 IF "issue_row"."closed" ISNULL THEN

1600 SELECT * INTO "policy_row" FROM "policy"

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

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

1603 IF "issue_row"."fully_frozen" ISNULL THEN

1604 PERFORM "create_snapshot"("issue_id_p");

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

1606 END IF;

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

1608 IF "issue_row"."accepted" ISNULL THEN

1609 IF EXISTS (

1610 SELECT NULL FROM "initiative"

1611 WHERE "issue_id" = "issue_id_p"

1612 AND "supporter_count" > 0

1613 AND "supporter_count" * "policy_row"."issue_quorum_den"

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

1615 ) THEN

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

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

1618 -- NOTE: "issue_row" used later

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

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

1621 UPDATE "issue" SET

1622 "state" = "issue_row"."state",

1623 "accepted" = "issue_row"."accepted"

1624 WHERE "id" = "issue_row"."id";

1625 ELSIF

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

1627 THEN

1628 -- close issues, if admission time has expired

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

1630 UPDATE "issue" SET

1631 "state" = 'canceled_issue_not_accepted',

1632 "closed" = now()

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

1634 END IF;

1635 END IF;

1636 -- eventually half freeze issues:

1637 IF

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

1639 "issue_row"."accepted" NOTNULL AND

1640 "issue_row"."half_frozen" ISNULL

1641 THEN

1642 SELECT

1643 CASE

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

1645 TRUE

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

1647 FALSE

1648 ELSE NULL

1649 END

1650 INTO "voting_requested_v"

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

1652 IF

1653 "voting_requested_v" OR (

1654 "voting_requested_v" ISNULL AND

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

1656 )

1657 THEN

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

1659 -- NOTE: "issue_row" used later

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

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

1662 UPDATE "issue" SET

1663 "state" = "issue_row"."state",

1664 "half_frozen" = "issue_row"."half_frozen"

1665 WHERE "id" = "issue_row"."id";

1666 END IF;

1667 END IF;

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

1669 IF

1670 "issue_row"."closed" ISNULL AND

1671 NOT EXISTS (

1672 -- all initiatives are revoked

1673 SELECT NULL FROM "initiative"

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

1675 ) AND (

1676 -- and issue has not been accepted yet

1677 "issue_row"."accepted" ISNULL OR

1678 NOT EXISTS (

1679 -- or no initiatives have been revoked lately

1680 SELECT NULL FROM "initiative"

1681 WHERE "issue_id" = "issue_id_p"

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

1683 ) OR (

1684 -- or verification time has elapsed

1685 "issue_row"."half_frozen" NOTNULL AND

1686 "issue_row"."fully_frozen" ISNULL AND

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

1688 )

1689 )

1690 THEN

1691 -- NOTE: "issue_row" used later

1692 IF "issue_row"."accepted" ISNULL THEN

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

1694 ELSIF "issue_row"."half_frozen" ISNULL THEN

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

1696 ELSE

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

1698 END IF;

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

1700 UPDATE "issue" SET

1701 "state" = "issue_row"."state",

1702 "closed" = "issue_row"."closed"

1703 WHERE "id" = "issue_row"."id";

1704 END IF;

1705 -- fully freeze issue after verification time:

1706 IF

1707 "issue_row"."half_frozen" NOTNULL AND

1708 "issue_row"."fully_frozen" ISNULL AND

1709 "issue_row"."closed" ISNULL AND

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

1711 THEN

1712 PERFORM "freeze_after_snapshot"("issue_id_p");

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

1714 END IF;

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

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

1717 IF

1718 "issue_row"."closed" ISNULL AND

1719 "issue_row"."fully_frozen" NOTNULL AND

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

1721 THEN

1722 PERFORM "close_voting"("issue_id_p");

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

1724 PERFORM "calculate_ranks"("issue_id_p");

1725 END IF;

1726 END IF;

1727 RETURN;

1728 END;

1729 $$;

1730

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

1732 RETURNS VOID

1733 LANGUAGE 'plpgsql' VOLATILE AS $$

1734 DECLARE

1735 "issue_row" "issue"%ROWTYPE;

1736 BEGIN

1737 SELECT * INTO "issue_row"

1738 FROM "issue" WHERE "id" = "issue_id_p"

1739 FOR UPDATE;

1740 IF "issue_row"."cleaned" ISNULL THEN

1741 UPDATE "issue" SET

1742 "closed" = NULL,

1743 "ranks_available" = FALSE

1744 WHERE "id" = "issue_id_p";

1745 DELETE FROM "delegating_voter"

1746 WHERE "issue_id" = "issue_id_p";

1747 DELETE FROM "direct_voter"

1748 WHERE "issue_id" = "issue_id_p";

1749 DELETE FROM "delegating_interest_snapshot"

1750 WHERE "issue_id" = "issue_id_p";

1751 DELETE FROM "direct_interest_snapshot"

1752 WHERE "issue_id" = "issue_id_p";

1753 DELETE FROM "delegating_population_snapshot"

1754 WHERE "issue_id" = "issue_id_p";

1755 DELETE FROM "direct_population_snapshot"

1756 WHERE "issue_id" = "issue_id_p";

1757 DELETE FROM "non_voter"

1758 WHERE "issue_id" = "issue_id_p";

1759 DELETE FROM "delegation"

1760 WHERE "issue_id" = "issue_id_p";

1761 DELETE FROM "supporter"

1762 WHERE "issue_id" = "issue_id_p";

1763 UPDATE "issue" SET

1764 "closed" = "issue_row"."closed",

1765 "ranks_available" = "issue_row"."ranks_available",

1766 "cleaned" = now()

1767 WHERE "id" = "issue_id_p";

1768 END IF;

1769 RETURN;

1770 END;

1771 $$;

1772

1773 CREATE OR REPLACE FUNCTION "check_issue"

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

1775 RETURNS VOID

1776 LANGUAGE 'plpgsql' VOLATILE AS $$

1777 DECLARE

1778 "issue_row" "issue"%ROWTYPE;

1779 "policy_row" "policy"%ROWTYPE;

1780 "voting_requested_v" BOOLEAN;

1781 BEGIN

1782 PERFORM "lock_issue"("issue_id_p");

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

1784 -- only process open issues:

1785 IF "issue_row"."closed" ISNULL THEN

1786 SELECT * INTO "policy_row" FROM "policy"

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

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

1789 IF "issue_row"."fully_frozen" ISNULL THEN

1790 PERFORM "create_snapshot"("issue_id_p");

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

1792 END IF;

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

1794 IF "issue_row"."accepted" ISNULL THEN

1795 IF EXISTS (

1796 SELECT NULL FROM "initiative"

1797 WHERE "issue_id" = "issue_id_p"

1798 AND "supporter_count" > 0

1799 AND "supporter_count" * "policy_row"."issue_quorum_den"

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

1801 ) THEN

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

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

1804 -- NOTE: "issue_row" used later

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

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

1807 UPDATE "issue" SET

1808 "state" = "issue_row"."state",

1809 "accepted" = "issue_row"."accepted"

1810 WHERE "id" = "issue_row"."id";

1811 ELSIF

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

1813 THEN

1814 -- close issues, if admission time has expired

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

1816 UPDATE "issue" SET

1817 "state" = 'canceled_issue_not_accepted',

1818 "closed" = now()

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

1820 END IF;

1821 END IF;

1822 -- eventually half freeze issues:

1823 IF

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

1825 "issue_row"."accepted" NOTNULL AND

1826 "issue_row"."half_frozen" ISNULL

1827 THEN

1828 SELECT

1829 CASE

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

1831 TRUE

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

1833 FALSE

1834 ELSE NULL

1835 END

1836 INTO "voting_requested_v"

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

1838 IF

1839 "voting_requested_v" OR (

1840 "voting_requested_v" ISNULL AND

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

1842 )

1843 THEN

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

1845 -- NOTE: "issue_row" used later

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

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

1848 UPDATE "issue" SET

1849 "state" = "issue_row"."state",

1850 "half_frozen" = "issue_row"."half_frozen"

1851 WHERE "id" = "issue_row"."id";

1852 END IF;

1853 END IF;

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

1855 IF

1856 "issue_row"."closed" ISNULL AND

1857 NOT EXISTS (

1858 -- all initiatives are revoked

1859 SELECT NULL FROM "initiative"

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

1861 ) AND (

1862 -- and issue has not been accepted yet

1863 "issue_row"."accepted" ISNULL OR

1864 NOT EXISTS (

1865 -- or no initiatives have been revoked lately

1866 SELECT NULL FROM "initiative"

1867 WHERE "issue_id" = "issue_id_p"

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

1869 ) OR (

1870 -- or verification time has elapsed

1871 "issue_row"."half_frozen" NOTNULL AND

1872 "issue_row"."fully_frozen" ISNULL AND

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

1874 )

1875 )

1876 THEN

1877 -- NOTE: "issue_row" used later

1878 IF "issue_row"."accepted" ISNULL THEN

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

1880 ELSIF "issue_row"."half_frozen" ISNULL THEN

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

1882 ELSE

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

1884 END IF;

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

1886 UPDATE "issue" SET

1887 "state" = "issue_row"."state",

1888 "closed" = "issue_row"."closed"

1889 WHERE "id" = "issue_row"."id";

1890 END IF;

1891 -- fully freeze issue after verification time:

1892 IF

1893 "issue_row"."half_frozen" NOTNULL AND

1894 "issue_row"."fully_frozen" ISNULL AND

1895 "issue_row"."closed" ISNULL AND

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

1897 THEN

1898 PERFORM "freeze_after_snapshot"("issue_id_p");

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

1900 END IF;

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

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

1903 IF

1904 "issue_row"."closed" ISNULL AND

1905 "issue_row"."fully_frozen" NOTNULL AND

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

1907 THEN

1908 PERFORM "close_voting"("issue_id_p");

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

1910 PERFORM "calculate_ranks"("issue_id_p");

1911 END IF;

1912 END IF;

1913 RETURN;

1914 END;

1915 $$;

1916

1917 CREATE OR REPLACE FUNCTION "delete_private_data"()

1918 RETURNS VOID

1919 LANGUAGE 'plpgsql' VOLATILE AS $$

1920 BEGIN

1921 UPDATE "member" SET

1922 "last_login" = NULL,

1923 "login" = NULL,

1924 "password" = NULL,

1925 "notify_email" = NULL,

1926 "notify_email_unconfirmed" = NULL,

1927 "notify_email_secret" = NULL,

1928 "notify_email_secret_expiry" = NULL,

1929 "notify_email_lock_expiry" = NULL,

1930 "password_reset_secret" = NULL,

1931 "password_reset_secret_expiry" = NULL,

1932 "organizational_unit" = NULL,

1933 "internal_posts" = NULL,

1934 "realname" = NULL,

1935 "birthday" = NULL,

1936 "address" = NULL,

1937 "email" = NULL,

1938 "xmpp_address" = NULL,

1939 "website" = NULL,

1940 "phone" = NULL,

1941 "mobile_phone" = NULL,

1942 "profession" = NULL,

1943 "external_memberships" = NULL,

1944 "external_posts" = NULL,

1945 "statement" = NULL;

1946 -- "text_search_data" is updated by triggers

1947 DELETE FROM "invite_code";

1948 DELETE FROM "setting";

1949 DELETE FROM "setting_map";

1950 DELETE FROM "member_relation_setting";

1951 DELETE FROM "member_image";

1952 DELETE FROM "contact";

1953 DELETE FROM "ignored_member";

1954 DELETE FROM "session";

1955 DELETE FROM "area_setting";

1956 DELETE FROM "issue_setting";

1957 DELETE FROM "ignored_initiative";

1958 DELETE FROM "initiative_setting";

1959 DELETE FROM "suggestion_setting";

1960 DELETE FROM "non_voter";

1961 DELETE FROM "direct_voter" USING "issue"

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

1963 AND "issue"."closed" ISNULL;

1964 RETURN;

1965 END;

1966 $$;

1967

1968

1969 -- Delete old "delegation_scope" TYPE:

1970

1971 DROP TYPE "delegation_scope_old";

1972

1973

1974 COMMIT;

1975

1976

1977 -- Generate issue states and add constraints:

1978

1979 UPDATE "issue" SET "state" =

1980 CASE

1981 WHEN "closed" ISNULL THEN

1982 CASE

1983 WHEN "accepted" ISNULL THEN

1984 'admission'::"issue_state"

1985 WHEN "half_frozen" ISNULL THEN

1986 'discussion'::"issue_state"

1987 WHEN "fully_frozen" ISNULL THEN

1988 'verification'::"issue_state"

1989 ELSE

1990 'voting'::"issue_state"

1991 END

1992 WHEN "fully_frozen" NOTNULL THEN

1993 CASE

1994 WHEN "fully_frozen" = "closed" THEN

1995 'canceled_no_initiative_admitted'::"issue_state"

1996 ELSE

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

1998 END

1999 WHEN "half_frozen" NOTNULL THEN

2000 'canceled_after_revocation_during_verification'::"issue_state"

2001 WHEN "accepted" NOTNULL THEN

2002 'canceled_after_revocation_during_discussion'::"issue_state"

2003 ELSE

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

2005 END;

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

2007 FROM "initiative"

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

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

2010 AND "initiative"."agreed";

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

2012 FROM "initiative"

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

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

2015 AND "initiative"."revoked" ISNULL;

2016

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

2018

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

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

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

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

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

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

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

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

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

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

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

2030 ) AND (

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

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

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

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

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

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

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

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

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

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

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

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

2043 ));

2044

2045

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

2047

2048 UPDATE "initiative" SET "revoked_by_member_id" = "author_id"

2049 FROM "current_draft"

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

2051 AND "initiative"."revoked" NOTNULL;

2052

2053 ALTER TABLE "initiative" ADD

2054 CONSTRAINT "all_or_none_of_revoked_and_revoked_by_member_id_must_be_null"

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

2056

2057

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

2059

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

2061

2062 ALTER TABLE "delegation" ADD CONSTRAINT "area_id_and_issue_id_set_according_to_scope"

2063 CHECK (

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

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

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

2067

2068

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

2070

2071 DELETE FROM "event";

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

2073

2074 INSERT INTO "event"

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

2076 "initiative_id", "draft_id", "suggestion_id" )

2077 SELECT * FROM (

2078 SELECT * FROM (

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

2080 "timeline"."occurrence",

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

2082 'initiative_created_in_new_issue'::"event_type"

2083 ELSE

2084 'initiative_created_in_existing_issue'::"event_type"

2085 END,

2086 "draft"."author_id",

2087 "issue"."id",

2088 CASE

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

2090 'admission'::"issue_state"

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

2092 'discussion'::"issue_state"

2093 ELSE

2094 'verification'::"issue_state"

2095 END,

2096 "initiative"."id",

2097 "draft"."id",

2098 NULL::INT8

2099 FROM "timeline"

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

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

2102 LEFT JOIN "timeline" AS "issue_creation"

2103 ON "initiative"."issue_id" = "issue_creation"."issue_id"

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

2105 AND "timeline"."occurrence" = "issue_creation"."occurrence"

2106 JOIN "draft"

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

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

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

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

2111 UNION ALL

2112 SELECT

2113 "timeline"."occurrence",

2114 'issue_state_changed'::"event_type",

2115 NULL,

2116 "issue"."id",

2117 CASE

2118 WHEN "timeline"."event" IN (

2119 'issue_canceled',

2120 'issue_finished_without_voting',

2121 'issue_finished_after_voting'

2122 ) THEN

2123 "issue"."state"

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

2125 'discussion'::"issue_state"

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

2127 'verification'::"issue_state"

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

2129 'voting'::"issue_state"

2130 END,

2131 NULL,

2132 NULL,

2133 NULL

2134 FROM "timeline"

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

2136 WHERE "timeline"."event" IN (

2137 'issue_canceled',

2138 'issue_accepted',

2139 'issue_half_frozen',

2140 'issue_finished_without_voting',

2141 'issue_voting_started',

2142 'issue_finished_after_voting' )

2143 UNION ALL

2144 SELECT

2145 "timeline"."occurrence",

2146 'initiative_revoked'::"event_type",

2147 "initiative"."revoked_by_member_id",

2148 "issue"."id",

2149 CASE

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

2151 'admission'::"issue_state"

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

2153 'discussion'::"issue_state"

2154 ELSE

2155 'verification'::"issue_state"

2156 END,

2157 "initiative"."id",

2158 "current_draft"."id",

2159 NULL

2160 FROM "timeline"

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

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

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

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

2165 UNION ALL

2166 SELECT

2167 "timeline"."occurrence",

2168 'new_draft_created'::"event_type",

2169 "draft"."author_id",

2170 "issue"."id",

2171 CASE

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

2173 'admission'::"issue_state"

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

2175 'discussion'::"issue_state"

2176 ELSE

2177 'verification'::"issue_state"

2178 END,

2179 "initiative"."id",

2180 "draft"."id",

2181 NULL

2182 FROM "timeline"

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

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

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

2186 LEFT JOIN "timeline" AS "initiative_creation"

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

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

2189 AND "timeline"."occurrence" = "initiative_creation"."occurrence"

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

2191 AND "initiative_creation"."initiative_id" ISNULL

2192 UNION ALL

2193 SELECT

2194 "timeline"."occurrence",

2195 'suggestion_created'::"event_type",

2196 "suggestion"."author_id",

2197 "issue"."id",

2198 CASE

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

2200 'admission'::"issue_state"

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

2202 'discussion'::"issue_state"

2203 ELSE

2204 'verification'::"issue_state"

2205 END,

2206 "initiative"."id",

2207 NULL,

2208 "suggestion"."id"

2209 FROM "timeline"

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

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

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

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

2214 ) AS "subquery"

2215 ORDER BY "occurrence";

2216

2217