liquid_feedback_core: 2a1f0bd5fcff update/core-update.v1.3.1-v1.4.0

liquid_feedback_core

view update/core-update.v1.3.1-v1.4.0_rc1.sql @ 122:2a1f0bd5fcff

Changed comments in update script to v1.4.0_rc1

author	jbe
date	Mon Mar 07 19:25:59 2011 +0100 (2011-03-07)
parents	8a1a488a1a67
children	1af27a67ee1a

line source

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

4 -- Update version information:

6 CREATE OR REPLACE VIEW "liquid_feedback_version" AS

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

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

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

13 CREATE TYPE "notify_level" AS ENUM

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

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

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

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

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

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

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

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

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

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

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

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

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

38 -- New table "ignored_member":

40 CREATE TABLE "ignored_member" (

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

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

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

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

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

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

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

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

54 CREATE TABLE "unit" (

55 "id" SERIAL4 PRIMARY KEY,

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

57 "active" BOOLEAN NOT NULL DEFAULT TRUE,

58 "name" TEXT NOT NULL,

59 "description" TEXT NOT NULL DEFAULT '',

60 "member_count" INT4,

61 "text_search_data" TSVECTOR );

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

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

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

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

66 CREATE TRIGGER "update_text_search_data"

67 BEFORE INSERT OR UPDATE ON "unit"

68 FOR EACH ROW EXECUTE PROCEDURE

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

70 "name", "description" );

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

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

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

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

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

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

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

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

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

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

90 -- Issue states:

92 CREATE TYPE "issue_state" AS ENUM (

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

94 'canceled_revoked_before_accepted',

95 'canceled_issue_not_accepted',

96 'canceled_after_revocation_during_discussion',

97 'canceled_after_revocation_during_verification',

98 'calculation',

99 'canceled_no_initiative_admitted',

100 'finished_without_winner', 'finished_with_winner');

101

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

103

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

105

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

107

108

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

110

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

112

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

114

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

116

117

118 -- New table "ignored_initiative":

119

120 CREATE TABLE "ignored_initiative" (

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

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

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

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

125

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

127

128

129 -- New table "invite_code_unit":

130

131 CREATE TABLE "invite_code_unit" (

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

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

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

135

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

137

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

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

140 FROM "invite_code" WHERE "used" ISNULL;

141

142

143 -- New table "privilege":

144

145 CREATE TABLE "privilege" (

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

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

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

149 "admin_manager" BOOLEAN NOT NULL DEFAULT FALSE,

150 "unit_manager" BOOLEAN NOT NULL DEFAULT FALSE,

151 "area_manager" BOOLEAN NOT NULL DEFAULT FALSE,

152 "voting_right_manager" BOOLEAN NOT NULL DEFAULT FALSE,

153 "voting_right" BOOLEAN NOT NULL DEFAULT TRUE );

154

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

156

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

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

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

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

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

162

163

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

165

166 DROP TABLE "ignored_issue";

167

168

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

170

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

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

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

174

175

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

177

178 DROP FUNCTION "delegation_chain"

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

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

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

182

183 DROP FUNCTION "delegation_chain"

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

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

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

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

188

189 DROP TYPE "delegation_chain_row";

190

191 DROP VIEW "issue_delegation";

192 DROP VIEW "area_delegation";

193 DROP VIEW "global_delegation";

194 DROP VIEW "active_delegation";

195

196

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

198

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

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

201

202 DROP INDEX "delegation_global_truster_id_unique_idx";

203

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

205 USING CASE WHEN "scope" = 'global'

206 THEN 'unit'::"delegation_scope"

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

208

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

210

211 ALTER TABLE "delegation" ADD CONSTRAINT "no_unit_delegation_to_null"

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

213

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

215

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

217

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

219

220

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

222

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

224 USING CASE WHEN "scope" = 'global'

225 THEN 'unit'::"delegation_scope"

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

227

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

229 USING CASE WHEN "scope" = 'global'

230 THEN 'unit'::"delegation_scope"

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

232

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

234 USING CASE WHEN "scope" = 'global'

235 THEN 'unit'::"delegation_scope"

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

237

238

239 -- New table "non_voter":

240

241 CREATE TABLE "non_voter" (

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

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

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

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

246

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

248

249

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

251

252 CREATE TABLE "issue_comment" (

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

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

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

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

257 "formatting_engine" TEXT,

258 "content" TEXT NOT NULL,

259 "text_search_data" TSVECTOR );

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

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

262 CREATE TRIGGER "update_text_search_data"

263 BEFORE INSERT OR UPDATE ON "issue_comment"

264 FOR EACH ROW EXECUTE PROCEDURE

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

266

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

268

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

270

271 CREATE TABLE "rendered_issue_comment" (

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

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

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

275 ON DELETE CASCADE ON UPDATE CASCADE,

276 "issue_id" INT4,

277 "member_id" INT4,

278 "format" TEXT,

279 "content" TEXT NOT NULL );

280

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

282

283

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

285

286 CREATE TABLE "voting_comment" (

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

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

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

290 "changed" TIMESTAMPTZ,

291 "formatting_engine" TEXT,

292 "content" TEXT NOT NULL,

293 "text_search_data" TSVECTOR );

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

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

296 CREATE TRIGGER "update_text_search_data"

297 BEFORE INSERT OR UPDATE ON "voting_comment"

298 FOR EACH ROW EXECUTE PROCEDURE

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

300

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

302

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

304

305 CREATE TABLE "rendered_voting_comment" (

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

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

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

309 ON DELETE CASCADE ON UPDATE CASCADE,

310 "issue_id" INT4,

311 "member_id" INT4,

312 "format" TEXT,

313 "content" TEXT NOT NULL );

314

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

316

317

318 -- New table "event":

319

320 CREATE TYPE "event_type" AS ENUM (

321 'issue_state_changed',

322 'initiative_created_in_new_issue',

323 'initiative_created_in_existing_issue',

324 'initiative_revoked',

325 'new_draft_created',

326 'suggestion_created');

327

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

329

330 CREATE TABLE "event" (

331 "id" SERIAL8 PRIMARY KEY,

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

333 "event" "event_type" NOT NULL,

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

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

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

337 "initiative_id" INT4,

338 "draft_id" INT8,

339 "suggestion_id" INT8,

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

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

342 ON DELETE CASCADE ON UPDATE CASCADE,

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

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

345 ON DELETE CASCADE ON UPDATE CASCADE,

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

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

348 ON DELETE CASCADE ON UPDATE CASCADE,

349 CONSTRAINT "null_constraints_for_issue_state_changed" CHECK (

350 "event" != 'issue_state_changed' OR (

351 "member_id" ISNULL AND

352 "issue_id" NOTNULL AND

353 "state" NOTNULL AND

354 "initiative_id" ISNULL AND

355 "draft_id" ISNULL AND

356 "suggestion_id" ISNULL )),

357 CONSTRAINT "null_constraints_for_initiative_creation_or_revocation_or_new_draft" CHECK (

358 "event" NOT IN (

359 'initiative_created_in_new_issue',

360 'initiative_created_in_existing_issue',

361 'initiative_revoked',

362 'new_draft_created'

363 ) OR (

364 "member_id" NOTNULL AND

365 "issue_id" NOTNULL AND

366 "state" NOTNULL AND

367 "initiative_id" NOTNULL AND

368 "draft_id" NOTNULL AND

369 "suggestion_id" ISNULL )),

370 CONSTRAINT "null_constraints_for_suggestion_creation" CHECK (

371 "event" != 'suggestion_created' OR (

372 "member_id" NOTNULL AND

373 "issue_id" NOTNULL AND

374 "state" NOTNULL AND

375 "initiative_id" NOTNULL AND

376 "draft_id" ISNULL AND

377 "suggestion_id" NOTNULL )) );

378

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

380

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

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

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

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

385

386

387 -- Triggers to fill "event" table:

388

389 CREATE FUNCTION "write_event_issue_state_changed_trigger"()

390 RETURNS TRIGGER

391 LANGUAGE 'plpgsql' VOLATILE AS $$

392 BEGIN

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

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

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

396 END IF;

397 RETURN NULL;

398 END;

399 $$;

400

401 CREATE TRIGGER "write_event_issue_state_changed"

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

403 "write_event_issue_state_changed_trigger"();

404

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

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

407

408 CREATE FUNCTION "write_event_initiative_or_draft_created_trigger"()

409 RETURNS TRIGGER

410 LANGUAGE 'plpgsql' VOLATILE AS $$

411 DECLARE

412 "initiative_row" "initiative"%ROWTYPE;

413 "issue_row" "issue"%ROWTYPE;

414 "event_v" "event_type";

415 BEGIN

416 SELECT * INTO "initiative_row" FROM "initiative"

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

418 SELECT * INTO "issue_row" FROM "issue"

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

420 IF EXISTS (

421 SELECT NULL FROM "draft"

422 WHERE "initiative_id" = NEW."initiative_id"

423 AND "id" != NEW."id"

424 ) THEN

425 "event_v" := 'new_draft_created';

426 ELSE

427 IF EXISTS (

428 SELECT NULL FROM "initiative"

429 WHERE "issue_id" = "initiative_row"."issue_id"

430 AND "id" != "initiative_row"."id"

431 ) THEN

432 "event_v" := 'initiative_created_in_existing_issue';

433 ELSE

434 "event_v" := 'initiative_created_in_new_issue';

435 END IF;

436 END IF;

437 INSERT INTO "event" (

438 "event", "member_id",

439 "issue_id", "state", "initiative_id", "draft_id"

440 ) VALUES (

441 "event_v",

442 NEW."author_id",

443 "initiative_row"."issue_id",

444 "issue_row"."state",

445 "initiative_row"."id",

446 NEW."id" );

447 RETURN NULL;

448 END;

449 $$;

450

451 CREATE TRIGGER "write_event_initiative_or_draft_created"

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

453 "write_event_initiative_or_draft_created_trigger"();

454

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

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

457

458 CREATE FUNCTION "write_event_initiative_revoked_trigger"()

459 RETURNS TRIGGER

460 LANGUAGE 'plpgsql' VOLATILE AS $$

461 DECLARE

462 "issue_row" "issue"%ROWTYPE;

463 BEGIN

464 SELECT * INTO "issue_row" FROM "issue"

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

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

467 INSERT INTO "event" (

468 "event", "member_id", "issue_id", "state", "initiative_id"

469 ) VALUES (

470 'initiative_revoked',

471 NEW."revoked_by_member_id",

472 NEW."issue_id",

473 "issue_row"."state",

474 NEW."id" );

475 END IF;

476 RETURN NULL;

477 END;

478 $$;

479

480 CREATE TRIGGER "write_event_initiative_revoked"

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

482 "write_event_initiative_revoked_trigger"();

483

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

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

486

487 CREATE FUNCTION "write_event_suggestion_created_trigger"()

488 RETURNS TRIGGER

489 LANGUAGE 'plpgsql' VOLATILE AS $$

490 DECLARE

491 "initiative_row" "initiative"%ROWTYPE;

492 "issue_row" "issue"%ROWTYPE;

493 BEGIN

494 SELECT * INTO "initiative_row" FROM "initiative"

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

496 SELECT * INTO "issue_row" FROM "issue"

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

498 INSERT INTO "event" (

499 "event", "member_id",

500 "issue_id", "state", "initiative_id", "suggestion_id"

501 ) VALUES (

502 'suggestion_created',

503 NEW."author_id",

504 "initiative_row"."issue_id",

505 "issue_row"."state",

506 "initiative_row"."id",

507 NEW."id" );

508 RETURN NULL;

509 END;

510 $$;

511

512 CREATE TRIGGER "write_event_suggestion_created"

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

514 "write_event_suggestion_created_trigger"();

515

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

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

518

519

520 -- Modified views:

521

522 CREATE VIEW "unit_delegation" AS

523 SELECT

524 "unit"."id" AS "unit_id",

525 "delegation"."id",

526 "delegation"."truster_id",

527 "delegation"."trustee_id",

528 "delegation"."scope"

529 FROM "unit"

530 JOIN "delegation"

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

532 JOIN "member"

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

534 JOIN "privilege"

535 ON "delegation"."unit_id" = "privilege"."unit_id"

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

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

538

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

540

541 CREATE VIEW "area_delegation" AS

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

543 "area"."id" AS "area_id",

544 "delegation"."id",

545 "delegation"."truster_id",

546 "delegation"."trustee_id",

547 "delegation"."scope"

548 FROM "area"

549 JOIN "delegation"

550 ON "delegation"."unit_id" = "area"."unit_id"

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

552 JOIN "member"

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

554 JOIN "privilege"

555 ON "area"."unit_id" = "privilege"."unit_id"

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

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

558 ORDER BY

559 "area"."id",

560 "delegation"."truster_id",

561 "delegation"."scope" DESC;

562

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

564

565 CREATE VIEW "issue_delegation" AS

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

567 "issue"."id" AS "issue_id",

568 "delegation"."id",

569 "delegation"."truster_id",

570 "delegation"."trustee_id",

571 "delegation"."scope"

572 FROM "issue"

573 JOIN "area"

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

575 JOIN "delegation"

576 ON "delegation"."unit_id" = "area"."unit_id"

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

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

579 JOIN "member"

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

581 JOIN "privilege"

582 ON "area"."unit_id" = "privilege"."unit_id"

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

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

585 ORDER BY

586 "issue"."id",

587 "delegation"."truster_id",

588 "delegation"."scope" DESC;

589

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

591

592 CREATE VIEW "unit_member_count" AS

593 SELECT

594 "unit"."id" AS "unit_id",

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

596 FROM "unit"

597 LEFT JOIN "privilege"

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

599 AND "privilege"."voting_right"

600 LEFT JOIN "member"

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

602 AND "member"."active"

603 GROUP BY "unit"."id";

604

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

606

607 DROP VIEW "area_member_count";

608 CREATE VIEW "area_member_count" AS

609 SELECT

610 "area"."id" AS "area_id",

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

612 coalesce(

613 sum(

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

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

616 ELSE 0 END

617 )

618 ) AS "member_weight",

619 coalesce(

620 sum(

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

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

623 ELSE 0 END

624 )

625 ) AS "autoreject_weight"

626 FROM "area"

627 LEFT JOIN "membership"

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

629 LEFT JOIN "privilege"

630 ON "privilege"."unit_id" = "area"."unit_id"

631 AND "privilege"."member_id" = "membership"."member_id"

632 AND "privilege"."voting_right"

633 LEFT JOIN "member"

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

635 AND "member"."active"

636 GROUP BY "area"."id";

637

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

639

640

641 -- New view "event_seen_by_member":

642

643 CREATE VIEW "event_seen_by_member" AS

644 SELECT

645 "member"."id" AS "seen_by_member_id",

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

647 'voting',

648 'finished_without_winner',

649 'finished_with_winner'

650 ) THEN

651 'voting'::"notify_level"

652 ELSE

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

654 'verification',

655 'canceled_after_revocation_during_verification',

656 'canceled_no_initiative_admitted'

657 ) THEN

658 'verification'::"notify_level"

659 ELSE

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

661 'discussion',

662 'canceled_after_revocation_during_discussion'

663 ) THEN

664 'discussion'::"notify_level"

665 ELSE

666 'all'::"notify_level"

667 END

668 END

669 END AS "notify_level",

670 "event".*

671 FROM "member" CROSS JOIN "event"

672 LEFT JOIN "issue"

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

674 LEFT JOIN "membership"

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

676 AND "issue"."area_id" = "membership"."area_id"

677 LEFT JOIN "interest"

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

679 AND "event"."issue_id" = "interest"."issue_id"

680 LEFT JOIN "supporter"

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

682 AND "event"."initiative_id" = "supporter"."initiative_id"

683 LEFT JOIN "ignored_member"

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

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

686 LEFT JOIN "ignored_initiative"

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

688 AND "event"."initiative_id" = "ignored_initiative"."initiative_id"

689 WHERE (

690 "supporter"."member_id" NOTNULL OR

691 "interest"."member_id" NOTNULL OR

692 ( "membership"."member_id" NOTNULL AND

693 "event"."event" IN (

694 'issue_state_changed',

695 'initiative_created_in_new_issue',

696 'initiative_created_in_existing_issue',

697 'initiative_revoked' ) ) )

698 AND "ignored_member"."member_id" ISNULL

699 AND "ignored_initiative"."member_id" ISNULL;

700

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

702

703

704 -- New view "pending_notification":

705

706 CREATE VIEW "pending_notification" AS

707 SELECT

708 "member"."id" AS "seen_by_member_id",

709 "event".*

710 FROM "member" CROSS JOIN "event"

711 LEFT JOIN "issue"

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

713 LEFT JOIN "membership"

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

715 AND "issue"."area_id" = "membership"."area_id"

716 LEFT JOIN "interest"

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

718 AND "event"."issue_id" = "interest"."issue_id"

719 LEFT JOIN "supporter"

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

721 AND "event"."initiative_id" = "supporter"."initiative_id"

722 LEFT JOIN "ignored_member"

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

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

725 LEFT JOIN "ignored_initiative"

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

727 AND "event"."initiative_id" = "ignored_initiative"."initiative_id"

728 WHERE (

729 "member"."notify_event_id" ISNULL OR

730 ( "member"."notify_event_id" NOTNULL AND

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

732 AND (

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

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

735 "event"."state" IN (

736 'voting',

737 'finished_without_winner',

738 'finished_with_winner' ) ) OR

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

740 "event"."state" IN (

741 'verification',

742 'canceled_after_revocation_during_verification',

743 'canceled_no_initiative_admitted' ) ) OR

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

745 "event"."state" IN (

746 'discussion',

747 'canceled_after_revocation_during_discussion' ) ) )

748 AND (

749 "supporter"."member_id" NOTNULL OR

750 "interest"."member_id" NOTNULL OR

751 ( "membership"."member_id" NOTNULL AND

752 "event"."event" IN (

753 'issue_state_changed',

754 'initiative_created_in_new_issue',

755 'initiative_created_in_existing_issue',

756 'initiative_revoked' ) ) )

757 AND "ignored_member"."member_id" ISNULL

758 AND "ignored_initiative"."member_id" ISNULL;

759

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

761

762

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

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

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

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

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

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

769

770

771 -- Modified "delegation_chain" functions:

772

773 CREATE TYPE "delegation_chain_row" AS (

774 "index" INT4,

775 "member_id" INT4,

776 "member_valid" BOOLEAN,

777 "participation" BOOLEAN,

778 "overridden" BOOLEAN,

779 "scope_in" "delegation_scope",

780 "scope_out" "delegation_scope",

781 "disabled_out" BOOLEAN,

782 "loop" "delegation_chain_loop_tag" );

783

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

785

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

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

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

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

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

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

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

793

794

795 CREATE FUNCTION "delegation_chain"

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

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

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

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

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

801 RETURNS SETOF "delegation_chain_row"

802 LANGUAGE 'plpgsql' STABLE AS $$

803 DECLARE

804 "scope_v" "delegation_scope";

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

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

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

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

809 "output_row" "delegation_chain_row";

810 "output_rows" "delegation_chain_row"[];

811 "delegation_row" "delegation"%ROWTYPE;

812 "row_count" INT4;

813 "i" INT4;

814 "loop_v" BOOLEAN;

815 BEGIN

816 IF

817 "unit_id_p" NOTNULL AND

818 "area_id_p" ISNULL AND

819 "issue_id_p" ISNULL

820 THEN

821 "scope_v" := 'unit';

822 "unit_id_v" := "unit_id_p";

823 ELSIF

824 "unit_id_p" ISNULL AND

825 "area_id_p" NOTNULL AND

826 "issue_id_p" ISNULL

827 THEN

828 "scope_v" := 'area';

829 "area_id_v" := "area_id_p";

830 SELECT "unit_id" INTO "unit_id_v"

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

832 ELSIF

833 "unit_id_p" ISNULL AND

834 "area_id_p" ISNULL AND

835 "issue_id_p" NOTNULL

836 THEN

837 "scope_v" := 'issue';

838 SELECT "area_id" INTO "area_id_v"

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

840 SELECT "unit_id" INTO "unit_id_v"

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

842 ELSE

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

844 END IF;

845 "visited_member_ids" := '{}';

846 "loop_member_id_v" := NULL;

847 "output_rows" := '{}';

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

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

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

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

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

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

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

855 LOOP

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

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

858 ELSE

859 "visited_member_ids" :=

860 "visited_member_ids" || "output_row"."member_id";

861 END IF;

862 IF "output_row"."participation" THEN

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

864 END IF;

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

866 IF EXISTS (

867 SELECT NULL FROM "member" JOIN "privilege"

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

869 AND "privilege"."unit_id" = "unit_id_v"

870 WHERE "id" = "output_row"."member_id"

871 AND "member"."active" AND "privilege"."voting_right"

872 ) THEN

873 IF "scope_v" = 'unit' THEN

874 SELECT * INTO "delegation_row" FROM "delegation"

875 WHERE "truster_id" = "output_row"."member_id"

876 AND "unit_id" = "unit_id_v";

877 ELSIF "scope_v" = 'area' THEN

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

879 SELECT NULL FROM "membership"

880 WHERE "area_id" = "area_id_p"

881 AND "member_id" = "output_row"."member_id"

882 );

883 SELECT * INTO "delegation_row" FROM "delegation"

884 WHERE "truster_id" = "output_row"."member_id"

885 AND (

886 "unit_id" = "unit_id_v" OR

887 "area_id" = "area_id_v"

888 )

889 ORDER BY "scope" DESC;

890 ELSIF "scope_v" = 'issue' THEN

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

892 SELECT NULL FROM "interest"

893 WHERE "issue_id" = "issue_id_p"

894 AND "member_id" = "output_row"."member_id"

895 );

896 SELECT * INTO "delegation_row" FROM "delegation"

897 WHERE "truster_id" = "output_row"."member_id"

898 AND (

899 "unit_id" = "unit_id_v" OR

900 "area_id" = "area_id_v" OR

901 "issue_id" = "issue_id_p"

902 )

903 ORDER BY "scope" DESC;

904 END IF;

905 ELSE

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

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

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

909 "delegation_row" := ROW(NULL);

910 END IF;

911 IF

912 "output_row"."member_id" = "member_id_p" AND

913 "simulate_trustee_id_p" NOTNULL

914 THEN

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

916 "output_rows" := "output_rows" || "output_row";

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

918 ELSIF "delegation_row"."trustee_id" NOTNULL THEN

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

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

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

922 ELSIF "delegation_row"."scope" NOTNULL THEN

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

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

925 "output_rows" := "output_rows" || "output_row";

926 EXIT;

927 ELSE

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

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

930 EXIT;

931 END IF;

932 EXIT WHEN "loop_member_id_v" NOTNULL;

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

934 END LOOP;

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

936 "i" := 1;

937 "loop_v" := FALSE;

938 LOOP

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

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

941 IF "loop_v" THEN

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

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

944 ELSIF "i" = "row_count" THEN

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

946 ELSE

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

948 END IF;

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

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

951 "loop_v" := TRUE;

952 END IF;

953 IF "scope_v" = 'unit' THEN

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

955 END IF;

956 RETURN NEXT "output_row";

957 "i" := "i" + 1;

958 END LOOP;

959 RETURN;

960 END;

961 $$;

962

963 COMMENT ON FUNCTION "delegation_chain"

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

965 "unit"."id"%TYPE,

966 "area"."id"%TYPE,

967 "issue"."id"%TYPE,

968 "member"."id"%TYPE )

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

970

971

972 CREATE FUNCTION "delegation_chain"

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

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

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

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

977 RETURNS SETOF "delegation_chain_row"

978 LANGUAGE 'plpgsql' STABLE AS $$

979 DECLARE

980 "result_row" "delegation_chain_row";

981 BEGIN

982 FOR "result_row" IN

983 SELECT * FROM "delegation_chain"(

984 "member_id_p", "area_id_p", "issue_id_p", NULL

985 )

986 LOOP

987 RETURN NEXT "result_row";

988 END LOOP;

989 RETURN;

990 END;

991 $$;

992

993 COMMENT ON FUNCTION "delegation_chain"

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

995 "unit"."id"%TYPE,

996 "area"."id"%TYPE,

997 "issue"."id"%TYPE )

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

999

1000

1001 -- Other modified functions:

1002

1003 CREATE OR REPLACE FUNCTION "lock_issue"

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

1005 RETURNS VOID

1006 LANGUAGE 'plpgsql' VOLATILE AS $$

1007 BEGIN

1008 LOCK TABLE "member" IN SHARE MODE;

1009 LOCK TABLE "privilege" IN SHARE MODE;

1010 LOCK TABLE "membership" IN SHARE MODE;

1011 LOCK TABLE "policy" IN SHARE MODE;

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

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

1014 -- share_row_lock_issue(_via_initiative)_trigger functions (which

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

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

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

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

1019 LOCK TABLE "delegation" IN SHARE MODE;

1020 LOCK TABLE "direct_population_snapshot" IN EXCLUSIVE MODE;

1021 LOCK TABLE "delegating_population_snapshot" IN EXCLUSIVE MODE;

1022 LOCK TABLE "direct_interest_snapshot" IN EXCLUSIVE MODE;

1023 LOCK TABLE "delegating_interest_snapshot" IN EXCLUSIVE MODE;

1024 LOCK TABLE "direct_supporter_snapshot" IN EXCLUSIVE MODE;

1025 RETURN;

1026 END;

1027 $$;

1028

1029 CREATE OR REPLACE FUNCTION "calculate_member_counts"()

1030 RETURNS VOID

1031 LANGUAGE 'plpgsql' VOLATILE AS $$

1032 BEGIN

1033 LOCK TABLE "member" IN SHARE MODE;

1034 LOCK TABLE "member_count" IN EXCLUSIVE MODE;

1035 LOCK TABLE "unit" IN EXCLUSIVE MODE;

1036 LOCK TABLE "area" IN EXCLUSIVE MODE;

1037 LOCK TABLE "privilege" IN SHARE MODE;

1038 LOCK TABLE "membership" IN SHARE MODE;

1039 DELETE FROM "member_count";

1040 INSERT INTO "member_count" ("total_count")

1041 SELECT "total_count" FROM "member_count_view";

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

1043 FROM "unit_member_count" AS "view"

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

1045 UPDATE "area" SET

1046 "direct_member_count" = "view"."direct_member_count",

1047 "member_weight" = "view"."member_weight",

1048 "autoreject_weight" = "view"."autoreject_weight"

1049 FROM "area_member_count" AS "view"

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

1051 RETURN;

1052 END;

1053 $$;

1054

1055 CREATE OR REPLACE FUNCTION "create_population_snapshot"

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

1057 RETURNS VOID

1058 LANGUAGE 'plpgsql' VOLATILE AS $$

1059 DECLARE

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

1061 BEGIN

1062 DELETE FROM "direct_population_snapshot"

1063 WHERE "issue_id" = "issue_id_p"

1064 AND "event" = 'periodic';

1065 DELETE FROM "delegating_population_snapshot"

1066 WHERE "issue_id" = "issue_id_p"

1067 AND "event" = 'periodic';

1068 INSERT INTO "direct_population_snapshot"

1069 ("issue_id", "event", "member_id")

1070 SELECT

1071 "issue_id_p" AS "issue_id",

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

1073 "member"."id" AS "member_id"

1074 FROM "issue"

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

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

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

1078 JOIN "privilege"

1079 ON "privilege"."unit_id" = "area"."unit_id"

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

1081 WHERE "issue"."id" = "issue_id_p"

1082 AND "member"."active" AND "privilege"."voting_right"

1083 UNION

1084 SELECT

1085 "issue_id_p" AS "issue_id",

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

1087 "member"."id" AS "member_id"

1088 FROM "issue"

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

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

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

1092 JOIN "privilege"

1093 ON "privilege"."unit_id" = "area"."unit_id"

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

1095 WHERE "issue"."id" = "issue_id_p"

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

1097 FOR "member_id_v" IN

1098 SELECT "member_id" FROM "direct_population_snapshot"

1099 WHERE "issue_id" = "issue_id_p"

1100 AND "event" = 'periodic'

1101 LOOP

1102 UPDATE "direct_population_snapshot" SET

1103 "weight" = 1 +

1104 "weight_of_added_delegations_for_population_snapshot"(

1105 "issue_id_p",

1106 "member_id_v",

1107 '{}'

1108 )

1109 WHERE "issue_id" = "issue_id_p"

1110 AND "event" = 'periodic'

1111 AND "member_id" = "member_id_v";

1112 END LOOP;

1113 RETURN;

1114 END;

1115 $$;

1116

1117 CREATE OR REPLACE FUNCTION "create_interest_snapshot"

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

1119 RETURNS VOID

1120 LANGUAGE 'plpgsql' VOLATILE AS $$

1121 DECLARE

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

1123 BEGIN

1124 DELETE FROM "direct_interest_snapshot"

1125 WHERE "issue_id" = "issue_id_p"

1126 AND "event" = 'periodic';

1127 DELETE FROM "delegating_interest_snapshot"

1128 WHERE "issue_id" = "issue_id_p"

1129 AND "event" = 'periodic';

1130 DELETE FROM "direct_supporter_snapshot"

1131 WHERE "issue_id" = "issue_id_p"

1132 AND "event" = 'periodic';

1133 INSERT INTO "direct_interest_snapshot"

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

1135 SELECT

1136 "issue_id_p" AS "issue_id",

1137 'periodic' AS "event",

1138 "member"."id" AS "member_id",

1139 "interest"."voting_requested"

1140 FROM "issue"

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

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

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

1144 JOIN "privilege"

1145 ON "privilege"."unit_id" = "area"."unit_id"

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

1147 WHERE "issue"."id" = "issue_id_p"

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

1149 FOR "member_id_v" IN

1150 SELECT "member_id" FROM "direct_interest_snapshot"

1151 WHERE "issue_id" = "issue_id_p"

1152 AND "event" = 'periodic'

1153 LOOP

1154 UPDATE "direct_interest_snapshot" SET

1155 "weight" = 1 +

1156 "weight_of_added_delegations_for_interest_snapshot"(

1157 "issue_id_p",

1158 "member_id_v",

1159 '{}'

1160 )

1161 WHERE "issue_id" = "issue_id_p"

1162 AND "event" = 'periodic'

1163 AND "member_id" = "member_id_v";

1164 END LOOP;

1165 INSERT INTO "direct_supporter_snapshot"

1166 ( "issue_id", "initiative_id", "event", "member_id",

1167 "informed", "satisfied" )

1168 SELECT

1169 "issue_id_p" AS "issue_id",

1170 "initiative"."id" AS "initiative_id",

1171 'periodic' AS "event",

1172 "supporter"."member_id" AS "member_id",

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

1174 NOT EXISTS (

1175 SELECT NULL FROM "critical_opinion"

1176 WHERE "initiative_id" = "initiative"."id"

1177 AND "member_id" = "supporter"."member_id"

1178 ) AS "satisfied"

1179 FROM "initiative"

1180 JOIN "supporter"

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

1182 JOIN "current_draft"

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

1184 JOIN "direct_interest_snapshot"

1185 ON "supporter"."member_id" = "direct_interest_snapshot"."member_id"

1186 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"

1187 AND "event" = 'periodic'

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

1189 RETURN;

1190 END;

1191 $$;

1192

1193 CREATE OR REPLACE FUNCTION "freeze_after_snapshot"

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

1195 RETURNS VOID

1196 LANGUAGE 'plpgsql' VOLATILE AS $$

1197 DECLARE

1198 "issue_row" "issue"%ROWTYPE;

1199 "policy_row" "policy"%ROWTYPE;

1200 "initiative_row" "initiative"%ROWTYPE;

1201 BEGIN

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

1203 SELECT * INTO "policy_row"

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

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

1206 FOR "initiative_row" IN

1207 SELECT * FROM "initiative"

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

1209 LOOP

1210 IF

1211 "initiative_row"."satisfied_supporter_count" > 0 AND

1212 "initiative_row"."satisfied_supporter_count" *

1213 "policy_row"."initiative_quorum_den" >=

1214 "issue_row"."population" * "policy_row"."initiative_quorum_num"

1215 THEN

1216 UPDATE "initiative" SET "admitted" = TRUE

1217 WHERE "id" = "initiative_row"."id";

1218 ELSE

1219 UPDATE "initiative" SET "admitted" = FALSE

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

1221 END IF;

1222 END LOOP;

1223 IF EXISTS (

1224 SELECT NULL FROM "initiative"

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

1226 ) THEN

1227 UPDATE "issue" SET

1228 "state" = 'voting',

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

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

1231 "fully_frozen" = now()

1232 WHERE "id" = "issue_id_p";

1233 ELSE

1234 UPDATE "issue" SET

1235 "state" = 'canceled_no_initiative_admitted',

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

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

1238 "fully_frozen" = now(),

1239 "closed" = now(),

1240 "ranks_available" = TRUE

1241 WHERE "id" = "issue_id_p";

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

1243 -- issue state has been manipulated

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

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

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

1247 END IF;

1248 RETURN;

1249 END;

1250 $$;

1251

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

1253 RETURNS VOID

1254 LANGUAGE 'plpgsql' VOLATILE AS $$

1255 DECLARE

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

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

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

1259 BEGIN

1260 PERFORM "lock_issue"("issue_id_p");

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

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

1263 DELETE FROM "delegating_voter"

1264 WHERE "issue_id" = "issue_id_p";

1265 DELETE FROM "direct_voter"

1266 WHERE "issue_id" = "issue_id_p"

1267 AND "autoreject" = TRUE;

1268 DELETE FROM "direct_voter"

1269 USING (

1270 SELECT

1271 "direct_voter"."member_id"

1272 FROM "direct_voter"

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

1274 LEFT JOIN "privilege"

1275 ON "privilege"."unit_id" = "unit_id_v"

1276 AND "privilege"."member_id" = "direct_voter"."member_id"

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

1278 "member"."active" = FALSE OR

1279 "privilege"."voting_right" ISNULL OR

1280 "privilege"."voting_right" = FALSE

1281 )

1282 ) AS "subquery"

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

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

1285 UPDATE "direct_voter" SET "weight" = 1

1286 WHERE "issue_id" = "issue_id_p";

1287 PERFORM "add_vote_delegations"("issue_id_p");

1288 FOR "member_id_v" IN

1289 SELECT "interest"."member_id"

1290 FROM "interest"

1291 JOIN "member"

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

1293 LEFT JOIN "direct_voter"

1294 ON "interest"."member_id" = "direct_voter"."member_id"

1295 AND "interest"."issue_id" = "direct_voter"."issue_id"

1296 LEFT JOIN "delegating_voter"

1297 ON "interest"."member_id" = "delegating_voter"."member_id"

1298 AND "interest"."issue_id" = "delegating_voter"."issue_id"

1299 WHERE "interest"."issue_id" = "issue_id_p"

1300 AND "interest"."autoreject" = TRUE

1301 AND "member"."active"

1302 AND "direct_voter"."member_id" ISNULL

1303 AND "delegating_voter"."member_id" ISNULL

1304 UNION SELECT "membership"."member_id"

1305 FROM "membership"

1306 JOIN "member"

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

1308 LEFT JOIN "interest"

1309 ON "membership"."member_id" = "interest"."member_id"

1310 AND "interest"."issue_id" = "issue_id_p"

1311 LEFT JOIN "direct_voter"

1312 ON "membership"."member_id" = "direct_voter"."member_id"

1313 AND "direct_voter"."issue_id" = "issue_id_p"

1314 LEFT JOIN "delegating_voter"

1315 ON "membership"."member_id" = "delegating_voter"."member_id"

1316 AND "delegating_voter"."issue_id" = "issue_id_p"

1317 WHERE "membership"."area_id" = "area_id_v"

1318 AND "membership"."autoreject" = TRUE

1319 AND "member"."active"

1320 AND "interest"."autoreject" ISNULL

1321 AND "direct_voter"."member_id" ISNULL

1322 AND "delegating_voter"."member_id" ISNULL

1323 LOOP

1324 INSERT INTO "direct_voter"

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

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

1327 INSERT INTO "vote" (

1328 "member_id",

1329 "issue_id",

1330 "initiative_id",

1331 "grade"

1332 ) SELECT

1333 "member_id_v" AS "member_id",

1334 "issue_id_p" AS "issue_id",

1335 "id" AS "initiative_id",

1336 -1 AS "grade"

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

1338 END LOOP;

1339 PERFORM "add_vote_delegations"("issue_id_p");

1340 UPDATE "issue" SET

1341 "state" = 'calculation',

1342 "closed" = now(),

1343 "voter_count" = (

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

1345 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"

1346 )

1347 WHERE "id" = "issue_id_p";

1348 UPDATE "initiative" SET

1349 "positive_votes" = "vote_counts"."positive_votes",

1350 "negative_votes" = "vote_counts"."negative_votes",

1351 "agreed" = CASE WHEN "majority_strict" THEN

1352 "vote_counts"."positive_votes" * "majority_den" >

1353 "majority_num" *

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

1355 ELSE

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

1357 "majority_num" *

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

1359 END

1360 FROM

1361 ( SELECT

1362 "initiative"."id" AS "initiative_id",

1363 coalesce(

1364 sum(

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

1366 ),

1367 0

1368 ) AS "positive_votes",

1369 coalesce(

1370 sum(

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

1372 ),

1373 0

1374 ) AS "negative_votes"

1375 FROM "initiative"

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

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

1378 LEFT JOIN "direct_voter"

1379 ON "direct_voter"."issue_id" = "initiative"."issue_id"

1380 LEFT JOIN "vote"

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

1382 AND "vote"."member_id" = "direct_voter"."member_id"

1383 WHERE "initiative"."issue_id" = "issue_id_p"

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

1385 GROUP BY "initiative"."id"

1386 ) AS "vote_counts",

1387 "issue",

1388 "policy"

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

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

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

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

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

1394 INSERT INTO "battle" (

1395 "issue_id",

1396 "winning_initiative_id", "losing_initiative_id",

1397 "count"

1398 ) SELECT

1399 "issue_id",

1400 "winning_initiative_id", "losing_initiative_id",

1401 "count"

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

1403 END;

1404 $$;

1405

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

1407 RETURNS VOID

1408 LANGUAGE 'plpgsql' VOLATILE AS $$

1409 DECLARE

1410 "dimension_v" INTEGER;

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

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

1413 "i" INTEGER;

1414 "j" INTEGER;

1415 "k" INTEGER;

1416 "battle_row" "battle"%ROWTYPE;

1417 "rank_ary" INT4[];

1418 "rank_v" INT4;

1419 "done_v" INTEGER;

1420 "winners_ary" INTEGER[];

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

1422 BEGIN

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

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

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

1426 IF "dimension_v" = 1 THEN

1427 UPDATE "initiative" SET "rank" = 1

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

1429 ELSIF "dimension_v" > 1 THEN

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

1431 -- comparison:

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

1433 "i" := 1;

1434 "j" := 2;

1435 FOR "battle_row" IN

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

1437 ORDER BY "winning_initiative_id", "losing_initiative_id"

1438 LOOP

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

1440 IF "j" = "dimension_v" THEN

1441 "i" := "i" + 1;

1442 "j" := 1;

1443 ELSE

1444 "j" := "j" + 1;

1445 IF "j" = "i" THEN

1446 "j" := "j" + 1;

1447 END IF;

1448 END IF;

1449 END LOOP;

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

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

1452 END IF;

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

1454 -- function:

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

1456 "i" := 1;

1457 LOOP

1458 "j" := 1;

1459 LOOP

1460 IF "i" != "j" THEN

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

1462 "vote_matrix"["i"]["j"],

1463 "vote_matrix"["j"]["i"]

1464 );

1465 END IF;

1466 EXIT WHEN "j" = "dimension_v";

1467 "j" := "j" + 1;

1468 END LOOP;

1469 EXIT WHEN "i" = "dimension_v";

1470 "i" := "i" + 1;

1471 END LOOP;

1472 -- Find best paths:

1473 "i" := 1;

1474 LOOP

1475 "j" := 1;

1476 LOOP

1477 IF "i" != "j" THEN

1478 "k" := 1;

1479 LOOP

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

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

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

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

1484 END IF;

1485 ELSE

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

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

1488 END IF;

1489 END IF;

1490 END IF;

1491 EXIT WHEN "k" = "dimension_v";

1492 "k" := "k" + 1;

1493 END LOOP;

1494 END IF;

1495 EXIT WHEN "j" = "dimension_v";

1496 "j" := "j" + 1;

1497 END LOOP;

1498 EXIT WHEN "i" = "dimension_v";

1499 "i" := "i" + 1;

1500 END LOOP;

1501 -- Determine order of winners:

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

1503 "rank_v" := 1;

1504 "done_v" := 0;

1505 LOOP

1506 "winners_ary" := '{}';

1507 "i" := 1;

1508 LOOP

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

1510 "j" := 1;

1511 LOOP

1512 IF

1513 "i" != "j" AND

1514 "rank_ary"["j"] ISNULL AND

1515 "matrix"["j"]["i"] > "matrix"["i"]["j"]

1516 THEN

1517 -- someone else is better

1518 EXIT;

1519 END IF;

1520 IF "j" = "dimension_v" THEN

1521 -- noone is better

1522 "winners_ary" := "winners_ary" || "i";

1523 EXIT;

1524 END IF;

1525 "j" := "j" + 1;

1526 END LOOP;

1527 END IF;

1528 EXIT WHEN "i" = "dimension_v";

1529 "i" := "i" + 1;

1530 END LOOP;

1531 "i" := 1;

1532 LOOP

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

1534 "done_v" := "done_v" + 1;

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

1536 "i" := "i" + 1;

1537 END LOOP;

1538 EXIT WHEN "done_v" = "dimension_v";

1539 "rank_v" := "rank_v" + 1;

1540 END LOOP;

1541 -- write preliminary ranks:

1542 "i" := 1;

1543 FOR "initiative_id_v" IN

1544 SELECT "id" FROM "initiative"

1545 WHERE "issue_id" = "issue_id_p" AND "agreed"

1546 ORDER BY "id"

1547 LOOP

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

1549 WHERE "id" = "initiative_id_v";

1550 "i" := "i" + 1;

1551 END LOOP;

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

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

1554 END IF;

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

1556 "rank_v" := 1;

1557 FOR "initiative_id_v" IN

1558 SELECT "id" FROM "initiative"

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

1560 ORDER BY

1561 "rank",

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

1563 "id"

1564 LOOP

1565 UPDATE "initiative" SET "rank" = "rank_v"

1566 WHERE "id" = "initiative_id_v";

1567 "rank_v" := "rank_v" + 1;

1568 END LOOP;

1569 END IF;

1570 -- mark issue as finished

1571 UPDATE "issue" SET

1572 "state" =

1573 CASE WHEN "dimension_v" = 0 THEN

1574 'finished_without_winner'::"issue_state"

1575 ELSE

1576 'finished_with_winner'::"issue_state"

1577 END,

1578 "ranks_available" = TRUE

1579 WHERE "id" = "issue_id_p";

1580 RETURN;

1581 END;

1582 $$;

1583

1584 CREATE OR REPLACE FUNCTION "check_issue"

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

1586 RETURNS VOID

1587 LANGUAGE 'plpgsql' VOLATILE AS $$

1588 DECLARE

1589 "issue_row" "issue"%ROWTYPE;

1590 "policy_row" "policy"%ROWTYPE;

1591 "voting_requested_v" BOOLEAN;

1592 BEGIN

1593 PERFORM "lock_issue"("issue_id_p");

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

1595 -- only process open issues:

1596 IF "issue_row"."closed" ISNULL THEN

1597 SELECT * INTO "policy_row" FROM "policy"

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

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

1600 IF "issue_row"."fully_frozen" ISNULL THEN

1601 PERFORM "create_snapshot"("issue_id_p");

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

1603 END IF;

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

1605 IF "issue_row"."accepted" ISNULL THEN

1606 IF EXISTS (

1607 SELECT NULL FROM "initiative"

1608 WHERE "issue_id" = "issue_id_p"

1609 AND "supporter_count" > 0

1610 AND "supporter_count" * "policy_row"."issue_quorum_den"

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

1612 ) THEN

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

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

1615 -- NOTE: "issue_row" used later

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

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

1618 UPDATE "issue" SET

1619 "state" = "issue_row"."state",

1620 "accepted" = "issue_row"."accepted"

1621 WHERE "id" = "issue_row"."id";

1622 ELSIF

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

1624 THEN

1625 -- close issues, if admission time has expired

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

1627 UPDATE "issue" SET

1628 "state" = 'canceled_issue_not_accepted',

1629 "closed" = now()

1630 WHERE "id" = "issue_row"."id";

1631 END IF;

1632 END IF;

1633 -- eventually half freeze issues:

1634 IF

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

1636 "issue_row"."accepted" NOTNULL AND

1637 "issue_row"."half_frozen" ISNULL

1638 THEN

1639 SELECT

1640 CASE

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

1642 TRUE

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

1644 FALSE

1645 ELSE NULL

1646 END

1647 INTO "voting_requested_v"

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

1649 IF

1650 "voting_requested_v" OR (

1651 "voting_requested_v" ISNULL AND

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

1653 )

1654 THEN

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

1656 -- NOTE: "issue_row" used later

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

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

1659 UPDATE "issue" SET

1660 "state" = "issue_row"."state",

1661 "half_frozen" = "issue_row"."half_frozen"

1662 WHERE "id" = "issue_row"."id";

1663 END IF;

1664 END IF;

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

1666 IF

1667 "issue_row"."closed" ISNULL AND

1668 NOT EXISTS (

1669 -- all initiatives are revoked

1670 SELECT NULL FROM "initiative"

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

1672 ) AND (

1673 -- and issue has not been accepted yet

1674 "issue_row"."accepted" ISNULL OR

1675 NOT EXISTS (

1676 -- or no initiatives have been revoked lately

1677 SELECT NULL FROM "initiative"

1678 WHERE "issue_id" = "issue_id_p"

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

1680 ) OR (

1681 -- or verification time has elapsed

1682 "issue_row"."half_frozen" NOTNULL AND

1683 "issue_row"."fully_frozen" ISNULL AND

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

1685 )

1686 )

1687 THEN

1688 -- NOTE: "issue_row" used later

1689 IF "issue_row"."accepted" ISNULL THEN

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

1691 ELSIF "issue_row"."half_frozen" ISNULL THEN

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

1693 ELSE

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

1695 END IF;

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

1697 UPDATE "issue" SET

1698 "state" = "issue_row"."state",

1699 "closed" = "issue_row"."closed"

1700 WHERE "id" = "issue_row"."id";

1701 END IF;

1702 -- fully freeze issue after verification time:

1703 IF

1704 "issue_row"."half_frozen" NOTNULL AND

1705 "issue_row"."fully_frozen" ISNULL AND

1706 "issue_row"."closed" ISNULL AND

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

1708 THEN

1709 PERFORM "freeze_after_snapshot"("issue_id_p");

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

1711 END IF;

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

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

1714 IF

1715 "issue_row"."closed" ISNULL AND

1716 "issue_row"."fully_frozen" NOTNULL AND

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

1718 THEN

1719 PERFORM "close_voting"("issue_id_p");

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

1721 PERFORM "calculate_ranks"("issue_id_p");

1722 END IF;

1723 END IF;

1724 RETURN;

1725 END;

1726 $$;

1727

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

1729 RETURNS VOID

1730 LANGUAGE 'plpgsql' VOLATILE AS $$

1731 DECLARE

1732 "issue_row" "issue"%ROWTYPE;

1733 BEGIN

1734 SELECT * INTO "issue_row"

1735 FROM "issue" WHERE "id" = "issue_id_p"

1736 FOR UPDATE;

1737 IF "issue_row"."cleaned" ISNULL THEN

1738 UPDATE "issue" SET

1739 "closed" = NULL,

1740 "ranks_available" = FALSE

1741 WHERE "id" = "issue_id_p";

1742 DELETE FROM "delegating_voter"

1743 WHERE "issue_id" = "issue_id_p";

1744 DELETE FROM "direct_voter"

1745 WHERE "issue_id" = "issue_id_p";

1746 DELETE FROM "delegating_interest_snapshot"

1747 WHERE "issue_id" = "issue_id_p";

1748 DELETE FROM "direct_interest_snapshot"

1749 WHERE "issue_id" = "issue_id_p";

1750 DELETE FROM "delegating_population_snapshot"

1751 WHERE "issue_id" = "issue_id_p";

1752 DELETE FROM "direct_population_snapshot"

1753 WHERE "issue_id" = "issue_id_p";

1754 DELETE FROM "non_voter"

1755 WHERE "issue_id" = "issue_id_p";

1756 DELETE FROM "delegation"

1757 WHERE "issue_id" = "issue_id_p";

1758 DELETE FROM "supporter"

1759 WHERE "issue_id" = "issue_id_p";

1760 UPDATE "issue" SET

1761 "closed" = "issue_row"."closed",

1762 "ranks_available" = "issue_row"."ranks_available",

1763 "cleaned" = now()

1764 WHERE "id" = "issue_id_p";

1765 END IF;

1766 RETURN;

1767 END;

1768 $$;

1769

1770 CREATE OR REPLACE FUNCTION "check_issue"

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

1772 RETURNS VOID

1773 LANGUAGE 'plpgsql' VOLATILE AS $$

1774 DECLARE

1775 "issue_row" "issue"%ROWTYPE;

1776 "policy_row" "policy"%ROWTYPE;

1777 "voting_requested_v" BOOLEAN;

1778 BEGIN

1779 PERFORM "lock_issue"("issue_id_p");

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

1781 -- only process open issues:

1782 IF "issue_row"."closed" ISNULL THEN

1783 SELECT * INTO "policy_row" FROM "policy"

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

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

1786 IF "issue_row"."fully_frozen" ISNULL THEN

1787 PERFORM "create_snapshot"("issue_id_p");

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

1789 END IF;

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

1791 IF "issue_row"."accepted" ISNULL THEN

1792 IF EXISTS (

1793 SELECT NULL FROM "initiative"

1794 WHERE "issue_id" = "issue_id_p"

1795 AND "supporter_count" > 0

1796 AND "supporter_count" * "policy_row"."issue_quorum_den"

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

1798 ) THEN

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

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

1801 -- NOTE: "issue_row" used later

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

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

1804 UPDATE "issue" SET

1805 "state" = "issue_row"."state",

1806 "accepted" = "issue_row"."accepted"

1807 WHERE "id" = "issue_row"."id";

1808 ELSIF

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

1810 THEN

1811 -- close issues, if admission time has expired

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

1813 UPDATE "issue" SET

1814 "state" = 'canceled_issue_not_accepted',

1815 "closed" = now()

1816 WHERE "id" = "issue_row"."id";

1817 END IF;

1818 END IF;

1819 -- eventually half freeze issues:

1820 IF

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

1822 "issue_row"."accepted" NOTNULL AND

1823 "issue_row"."half_frozen" ISNULL

1824 THEN

1825 SELECT

1826 CASE

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

1828 TRUE

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

1830 FALSE

1831 ELSE NULL

1832 END

1833 INTO "voting_requested_v"

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

1835 IF

1836 "voting_requested_v" OR (

1837 "voting_requested_v" ISNULL AND

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

1839 )

1840 THEN

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

1842 -- NOTE: "issue_row" used later

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

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

1845 UPDATE "issue" SET

1846 "state" = "issue_row"."state",

1847 "half_frozen" = "issue_row"."half_frozen"

1848 WHERE "id" = "issue_row"."id";

1849 END IF;

1850 END IF;

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

1852 IF

1853 "issue_row"."closed" ISNULL AND

1854 NOT EXISTS (

1855 -- all initiatives are revoked

1856 SELECT NULL FROM "initiative"

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

1858 ) AND (

1859 -- and issue has not been accepted yet

1860 "issue_row"."accepted" ISNULL OR

1861 NOT EXISTS (

1862 -- or no initiatives have been revoked lately

1863 SELECT NULL FROM "initiative"

1864 WHERE "issue_id" = "issue_id_p"

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

1866 ) OR (

1867 -- or verification time has elapsed

1868 "issue_row"."half_frozen" NOTNULL AND

1869 "issue_row"."fully_frozen" ISNULL AND

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

1871 )

1872 )

1873 THEN

1874 -- NOTE: "issue_row" used later

1875 IF "issue_row"."accepted" ISNULL THEN

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

1877 ELSIF "issue_row"."half_frozen" ISNULL THEN

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

1879 ELSE

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

1881 END IF;

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

1883 UPDATE "issue" SET

1884 "state" = "issue_row"."state",

1885 "closed" = "issue_row"."closed"

1886 WHERE "id" = "issue_row"."id";

1887 END IF;

1888 -- fully freeze issue after verification time:

1889 IF

1890 "issue_row"."half_frozen" NOTNULL AND

1891 "issue_row"."fully_frozen" ISNULL AND

1892 "issue_row"."closed" ISNULL AND

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

1894 THEN

1895 PERFORM "freeze_after_snapshot"("issue_id_p");

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

1897 END IF;

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

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

1900 IF

1901 "issue_row"."closed" ISNULL AND

1902 "issue_row"."fully_frozen" NOTNULL AND

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

1904 THEN

1905 PERFORM "close_voting"("issue_id_p");

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

1907 PERFORM "calculate_ranks"("issue_id_p");

1908 END IF;

1909 END IF;

1910 RETURN;

1911 END;

1912 $$;

1913

1914 CREATE OR REPLACE FUNCTION "delete_private_data"()

1915 RETURNS VOID

1916 LANGUAGE 'plpgsql' VOLATILE AS $$

1917 BEGIN

1918 UPDATE "member" SET

1919 "last_login" = NULL,

1920 "login" = NULL,

1921 "password" = NULL,

1922 "notify_email" = NULL,

1923 "notify_email_unconfirmed" = NULL,

1924 "notify_email_secret" = NULL,

1925 "notify_email_secret_expiry" = NULL,

1926 "notify_email_lock_expiry" = NULL,

1927 "password_reset_secret" = NULL,

1928 "password_reset_secret_expiry" = NULL,

1929 "organizational_unit" = NULL,

1930 "internal_posts" = NULL,

1931 "realname" = NULL,

1932 "birthday" = NULL,

1933 "address" = NULL,

1934 "email" = NULL,

1935 "xmpp_address" = NULL,

1936 "website" = NULL,

1937 "phone" = NULL,

1938 "mobile_phone" = NULL,

1939 "profession" = NULL,

1940 "external_memberships" = NULL,

1941 "external_posts" = NULL,

1942 "statement" = NULL;

1943 -- "text_search_data" is updated by triggers

1944 DELETE FROM "invite_code";

1945 DELETE FROM "setting";

1946 DELETE FROM "setting_map";

1947 DELETE FROM "member_relation_setting";

1948 DELETE FROM "member_image";

1949 DELETE FROM "contact";

1950 DELETE FROM "ignored_member";

1951 DELETE FROM "session";

1952 DELETE FROM "area_setting";

1953 DELETE FROM "issue_setting";

1954 DELETE FROM "ignored_initiative";

1955 DELETE FROM "initiative_setting";

1956 DELETE FROM "suggestion_setting";

1957 DELETE FROM "non_voter";

1958 DELETE FROM "direct_voter" USING "issue"

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

1960 AND "issue"."closed" ISNULL;

1961 RETURN;

1962 END;

1963 $$;

1964

1965

1966 -- Delete old "delegation_scope" TYPE:

1967

1968 DROP TYPE "delegation_scope_old";

1969

1970

1971 COMMIT;

1972

1973

1974 -- Generate issue states and add constraints:

1975

1976 UPDATE "issue" SET "state" =

1977 CASE

1978 WHEN "closed" ISNULL THEN

1979 CASE

1980 WHEN "accepted" ISNULL THEN

1981 'admission'::"issue_state"

1982 WHEN "half_frozen" ISNULL THEN

1983 'discussion'::"issue_state"

1984 WHEN "fully_frozen" ISNULL THEN

1985 'verification'::"issue_state"

1986 ELSE

1987 'voting'::"issue_state"

1988 END

1989 WHEN "fully_frozen" NOTNULL THEN

1990 CASE

1991 WHEN "fully_frozen" = "closed" THEN

1992 'canceled_no_initiative_admitted'::"issue_state"

1993 ELSE

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

1995 END

1996 WHEN "half_frozen" NOTNULL THEN

1997 'canceled_after_revocation_during_verification'::"issue_state"

1998 WHEN "accepted" NOTNULL THEN

1999 'canceled_after_revocation_during_discussion'::"issue_state"

2000 ELSE

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

2002 END;

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

2004 FROM "initiative"

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

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

2007 AND "initiative"."agreed";

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

2009 FROM "initiative"

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

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

2012 AND "initiative"."revoked" ISNULL;

2013

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

2015

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

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

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

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

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

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

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

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

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

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

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

2027 ) AND (

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

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

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

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

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

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

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

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

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

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

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

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

2040 ));

2041

2042

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

2044

2045 UPDATE "initiative" SET "revoked_by_member_id" = "author_id"

2046 FROM "current_draft"

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

2048 AND "initiative"."revoked" NOTNULL;

2049

2050 ALTER TABLE "initiative" ADD

2051 CONSTRAINT "all_or_none_of_revoked_and_revoked_by_member_id_must_be_null"

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

2053

2054

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

2056

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

2058

2059 ALTER TABLE "delegation" ADD CONSTRAINT "area_id_and_issue_id_set_according_to_scope"

2060 CHECK (

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

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

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

2064

2065

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

2067

2068 DELETE FROM "event";

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

2070

2071 INSERT INTO "event"

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

2073 "initiative_id", "draft_id", "suggestion_id" )

2074 SELECT * FROM (

2075 SELECT * FROM (

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

2077 "timeline"."occurrence",

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

2079 'initiative_created_in_new_issue'::"event_type"

2080 ELSE

2081 'initiative_created_in_existing_issue'::"event_type"

2082 END,

2083 "draft"."author_id",

2084 "issue"."id",

2085 CASE

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

2087 'admission'::"issue_state"

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

2089 'discussion'::"issue_state"

2090 ELSE

2091 'verification'::"issue_state"

2092 END,

2093 "initiative"."id",

2094 "draft"."id",

2095 NULL::INT8

2096 FROM "timeline"

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

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

2099 LEFT JOIN "timeline" AS "issue_creation"

2100 ON "initiative"."issue_id" = "issue_creation"."issue_id"

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

2102 AND "timeline"."occurrence" = "issue_creation"."occurrence"

2103 JOIN "draft"

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

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

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

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

2108 UNION ALL

2109 SELECT

2110 "timeline"."occurrence",

2111 'issue_state_changed'::"event_type",

2112 NULL,

2113 "issue"."id",

2114 CASE

2115 WHEN "timeline"."event" IN (

2116 'issue_canceled',

2117 'issue_finished_without_voting',

2118 'issue_finished_after_voting'

2119 ) THEN

2120 "issue"."state"

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

2122 'discussion'::"issue_state"

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

2124 'verification'::"issue_state"

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

2126 'voting'::"issue_state"

2127 END,

2128 NULL,

2129 NULL,

2130 NULL

2131 FROM "timeline"

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

2133 WHERE "timeline"."event" IN (

2134 'issue_canceled',

2135 'issue_accepted',

2136 'issue_half_frozen',

2137 'issue_finished_without_voting',

2138 'issue_voting_started',

2139 'issue_finished_after_voting' )

2140 UNION ALL

2141 SELECT

2142 "timeline"."occurrence",

2143 'initiative_revoked'::"event_type",

2144 "initiative"."revoked_by_member_id",

2145 "issue"."id",

2146 CASE

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

2148 'admission'::"issue_state"

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

2150 'discussion'::"issue_state"

2151 ELSE

2152 'verification'::"issue_state"

2153 END,

2154 "initiative"."id",

2155 "current_draft"."id",

2156 NULL

2157 FROM "timeline"

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

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

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

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

2162 UNION ALL

2163 SELECT

2164 "timeline"."occurrence",

2165 'new_draft_created'::"event_type",

2166 "draft"."author_id",

2167 "issue"."id",

2168 CASE

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

2170 'admission'::"issue_state"

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

2172 'discussion'::"issue_state"

2173 ELSE

2174 'verification'::"issue_state"

2175 END,

2176 "initiative"."id",

2177 "draft"."id",

2178 NULL

2179 FROM "timeline"

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

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

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

2183 LEFT JOIN "timeline" AS "initiative_creation"

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

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

2186 AND "timeline"."occurrence" = "initiative_creation"."occurrence"

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

2188 AND "initiative_creation"."initiative_id" ISNULL

2189 UNION ALL

2190 SELECT

2191 "timeline"."occurrence",

2192 'suggestion_created'::"event_type",

2193 "suggestion"."author_id",

2194 "issue"."id",

2195 CASE

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

2197 'admission'::"issue_state"

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

2199 'discussion'::"issue_state"

2200 ELSE

2201 'verification'::"issue_state"

2202 END,

2203 "initiative"."id",

2204 NULL,

2205 "suggestion"."id"

2206 FROM "timeline"

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

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

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

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

2211 ) AS "subquery"

2212 ORDER BY "occurrence";

2213

2214