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

liquid_feedback_core

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

Added tag v2.2.1 for changeset 0f5841ef67c6

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

line source

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

4 -- Update version information:

6 CREATE OR REPLACE VIEW "liquid_feedback_version" AS

7 SELECT * FROM (VALUES ('1.4.0_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" DEFAULT NULL;

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

106

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

108

109

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

111

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

113

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

115

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

117

118

119 -- New table "ignored_initiative":

120

121 CREATE TABLE "ignored_initiative" (

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

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

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

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

126

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

128

129

130 -- New table "invite_code_unit":

131

132 CREATE TABLE "invite_code_unit" (

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

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

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

136

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

138

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

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

141

142

143 -- New table "privilege":

144

145 CREATE TABLE "privilege" (

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

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

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

149 "admin_manager" BOOLEAN NOT NULL DEFAULT FALSE,

150 "unit_manager" BOOLEAN NOT NULL DEFAULT FALSE,

151 "area_manager" BOOLEAN NOT NULL DEFAULT FALSE,

152 "voting_right_manager" BOOLEAN NOT NULL DEFAULT FALSE,

153 "voting_right" BOOLEAN NOT NULL DEFAULT TRUE );

154

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

156

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

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

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

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

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

162

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

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

165 FROM "member";

166

167

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

169

170 DROP TABLE "ignored_issue";

171

172

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

174

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

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

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

178

179

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

181

182 DROP FUNCTION "delegation_chain"

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

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

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

186

187 DROP FUNCTION "delegation_chain"

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

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

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

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

192

193 DROP TYPE "delegation_chain_row";

194

195 DROP VIEW "issue_delegation";

196 DROP VIEW "area_delegation";

197 DROP VIEW "global_delegation";

198 DROP VIEW "active_delegation";

199

200

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

202

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

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

205

206 DROP INDEX "delegation_global_truster_id_unique_idx";

207

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

209 USING CASE WHEN "scope" = 'global'

210 THEN 'unit'::"delegation_scope"

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

212

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

214

215 ALTER TABLE "delegation" ADD CONSTRAINT "no_unit_delegation_to_null"

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

217

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

219

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

221

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

223

224

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

226

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

228 USING CASE WHEN "scope" = 'global'

229 THEN 'unit'::"delegation_scope"

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

231

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

233 USING CASE WHEN "scope" = 'global'

234 THEN 'unit'::"delegation_scope"

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

236

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

238 USING CASE WHEN "scope" = 'global'

239 THEN 'unit'::"delegation_scope"

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

241

242

243 -- New table "non_voter":

244

245 CREATE TABLE "non_voter" (

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

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

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

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

250

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

252

253

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

255

256 CREATE TABLE "issue_comment" (

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

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

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

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

261 "formatting_engine" TEXT,

262 "content" TEXT NOT NULL,

263 "text_search_data" TSVECTOR );

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

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

266 CREATE TRIGGER "update_text_search_data"

267 BEFORE INSERT OR UPDATE ON "issue_comment"

268 FOR EACH ROW EXECUTE PROCEDURE

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

270

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

272

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

274

275 CREATE TABLE "rendered_issue_comment" (

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

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

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

279 ON DELETE CASCADE ON UPDATE CASCADE,

280 "issue_id" INT4,

281 "member_id" INT4,

282 "format" TEXT,

283 "content" TEXT NOT NULL );

284

285 COMMENT ON TABLE "rendered_issue_comment" IS 'This table may be used by frontends to cache "rendered" issue comments (e.g. HTML output generated from wiki text)';

286

287

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

289

290 CREATE TABLE "voting_comment" (

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

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

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

294 "changed" TIMESTAMPTZ,

295 "formatting_engine" TEXT,

296 "content" TEXT NOT NULL,

297 "text_search_data" TSVECTOR );

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

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

300 CREATE TRIGGER "update_text_search_data"

301 BEFORE INSERT OR UPDATE ON "voting_comment"

302 FOR EACH ROW EXECUTE PROCEDURE

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

304

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

306

307 COMMENT ON COLUMN "voting_comment"."changed" IS 'Is to be set or updated by the frontend, if comment was inserted or updated AFTER the issue has been closed. Otherwise it shall be set to NULL.';

308

309 CREATE TABLE "rendered_voting_comment" (

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

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

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

313 ON DELETE CASCADE ON UPDATE CASCADE,

314 "issue_id" INT4,

315 "member_id" INT4,

316 "format" TEXT,

317 "content" TEXT NOT NULL );

318

319 COMMENT ON TABLE "rendered_voting_comment" IS 'This table may be used by frontends to cache "rendered" voting comments (e.g. HTML output generated from wiki text)';

320

321

322 -- New table "event":

323

324 CREATE TYPE "event_type" AS ENUM (

325 'issue_state_changed',

326 'initiative_created_in_new_issue',

327 'initiative_created_in_existing_issue',

328 'initiative_revoked',

329 'new_draft_created',

330 'suggestion_created');

331

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

333

334 CREATE TABLE "event" (

335 "id" SERIAL8 PRIMARY KEY,

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

337 "event" "event_type" NOT NULL,

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

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

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

341 "initiative_id" INT4,

342 "draft_id" INT8,

343 "suggestion_id" INT8,

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

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

346 ON DELETE CASCADE ON UPDATE CASCADE,

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

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

349 ON DELETE CASCADE ON UPDATE CASCADE,

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

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

352 ON DELETE CASCADE ON UPDATE CASCADE,

353 CONSTRAINT "null_constraints_for_issue_state_changed" CHECK (

354 "event" != 'issue_state_changed' OR (

355 "member_id" ISNULL AND

356 "issue_id" NOTNULL AND

357 "state" NOTNULL AND

358 "initiative_id" ISNULL AND

359 "draft_id" ISNULL AND

360 "suggestion_id" ISNULL )),

361 CONSTRAINT "null_constraints_for_initiative_creation_or_revocation_or_new_draft" CHECK (

362 "event" NOT IN (

363 'initiative_created_in_new_issue',

364 'initiative_created_in_existing_issue',

365 'initiative_revoked',

366 'new_draft_created'

367 ) OR (

368 "member_id" NOTNULL AND

369 "issue_id" NOTNULL AND

370 "state" NOTNULL AND

371 "initiative_id" NOTNULL AND

372 "draft_id" NOTNULL AND

373 "suggestion_id" ISNULL )),

374 CONSTRAINT "null_constraints_for_suggestion_creation" CHECK (

375 "event" != 'suggestion_created' OR (

376 "member_id" NOTNULL AND

377 "issue_id" NOTNULL AND

378 "state" NOTNULL AND

379 "initiative_id" NOTNULL AND

380 "draft_id" ISNULL AND

381 "suggestion_id" NOTNULL )) );

382

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

384

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

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

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

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

389

390

391 -- Triggers to fill "event" table:

392

393 CREATE FUNCTION "write_event_issue_state_changed_trigger"()

394 RETURNS TRIGGER

395 LANGUAGE 'plpgsql' VOLATILE AS $$

396 BEGIN

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

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

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

400 END IF;

401 RETURN NULL;

402 END;

403 $$;

404

405 CREATE TRIGGER "write_event_issue_state_changed"

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

407 "write_event_issue_state_changed_trigger"();

408

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

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

411

412 CREATE FUNCTION "write_event_initiative_or_draft_created_trigger"()

413 RETURNS TRIGGER

414 LANGUAGE 'plpgsql' VOLATILE AS $$

415 DECLARE

416 "initiative_row" "initiative"%ROWTYPE;

417 "issue_row" "issue"%ROWTYPE;

418 "event_v" "event_type";

419 BEGIN

420 SELECT * INTO "initiative_row" FROM "initiative"

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

422 SELECT * INTO "issue_row" FROM "issue"

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

424 IF EXISTS (

425 SELECT NULL FROM "draft"

426 WHERE "initiative_id" = NEW."initiative_id"

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

428 ) THEN

429 "event_v" := 'new_draft_created';

430 ELSE

431 IF EXISTS (

432 SELECT NULL FROM "initiative"

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

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

435 ) THEN

436 "event_v" := 'initiative_created_in_existing_issue';

437 ELSE

438 "event_v" := 'initiative_created_in_new_issue';

439 END IF;

440 END IF;

441 INSERT INTO "event" (

442 "event", "member_id",

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

444 ) VALUES (

445 "event_v",

446 NEW."author_id",

447 "initiative_row"."issue_id",

448 "issue_row"."state",

449 "initiative_row"."id",

450 NEW."id" );

451 RETURN NULL;

452 END;

453 $$;

454

455 CREATE TRIGGER "write_event_initiative_or_draft_created"

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

457 "write_event_initiative_or_draft_created_trigger"();

458

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

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

461

462 CREATE FUNCTION "write_event_initiative_revoked_trigger"()

463 RETURNS TRIGGER

464 LANGUAGE 'plpgsql' VOLATILE AS $$

465 DECLARE

466 "issue_row" "issue"%ROWTYPE;

467 BEGIN

468 SELECT * INTO "issue_row" FROM "issue"

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

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

471 INSERT INTO "event" (

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

473 ) VALUES (

474 'initiative_revoked',

475 NEW."revoked_by_member_id",

476 NEW."issue_id",

477 "issue_row"."state",

478 NEW."id" );

479 END IF;

480 RETURN NULL;

481 END;

482 $$;

483

484 CREATE TRIGGER "write_event_initiative_revoked"

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

486 "write_event_initiative_revoked_trigger"();

487

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

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

490

491 CREATE FUNCTION "write_event_suggestion_created_trigger"()

492 RETURNS TRIGGER

493 LANGUAGE 'plpgsql' VOLATILE AS $$

494 DECLARE

495 "initiative_row" "initiative"%ROWTYPE;

496 "issue_row" "issue"%ROWTYPE;

497 BEGIN

498 SELECT * INTO "initiative_row" FROM "initiative"

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

500 SELECT * INTO "issue_row" FROM "issue"

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

502 INSERT INTO "event" (

503 "event", "member_id",

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

505 ) VALUES (

506 'suggestion_created',

507 NEW."author_id",

508 "initiative_row"."issue_id",

509 "issue_row"."state",

510 "initiative_row"."id",

511 NEW."id" );

512 RETURN NULL;

513 END;

514 $$;

515

516 CREATE TRIGGER "write_event_suggestion_created"

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

518 "write_event_suggestion_created_trigger"();

519

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

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

522

523

524 -- Modified views:

525

526 CREATE VIEW "unit_delegation" AS

527 SELECT

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

529 "delegation"."id",

530 "delegation"."truster_id",

531 "delegation"."trustee_id",

532 "delegation"."scope"

533 FROM "unit"

534 JOIN "delegation"

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

536 JOIN "member"

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

538 JOIN "privilege"

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

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

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

542

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

544

545 CREATE VIEW "area_delegation" AS

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

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

548 "delegation"."id",

549 "delegation"."truster_id",

550 "delegation"."trustee_id",

551 "delegation"."scope"

552 FROM "area"

553 JOIN "delegation"

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

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

556 JOIN "member"

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

558 JOIN "privilege"

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

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

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

562 ORDER BY

563 "area"."id",

564 "delegation"."truster_id",

565 "delegation"."scope" DESC;

566

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

568

569 CREATE VIEW "issue_delegation" AS

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

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

572 "delegation"."id",

573 "delegation"."truster_id",

574 "delegation"."trustee_id",

575 "delegation"."scope"

576 FROM "issue"

577 JOIN "area"

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

579 JOIN "delegation"

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

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

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

583 JOIN "member"

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

585 JOIN "privilege"

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

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

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

589 ORDER BY

590 "issue"."id",

591 "delegation"."truster_id",

592 "delegation"."scope" DESC;

593

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

595

596 CREATE VIEW "unit_member_count" AS

597 SELECT

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

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

600 FROM "unit"

601 LEFT JOIN "privilege"

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

603 AND "privilege"."voting_right"

604 LEFT JOIN "member"

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

606 AND "member"."active"

607 GROUP BY "unit"."id";

608

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

610

611 DROP VIEW "area_member_count";

612 CREATE VIEW "area_member_count" AS

613 SELECT

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

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

616 coalesce(

617 sum(

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

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

620 ELSE 0 END

621 )

622 ) AS "member_weight",

623 coalesce(

624 sum(

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

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

627 ELSE 0 END

628 )

629 ) AS "autoreject_weight"

630 FROM "area"

631 LEFT JOIN "membership"

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

633 LEFT JOIN "privilege"

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

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

636 AND "privilege"."voting_right"

637 LEFT JOIN "member"

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

639 AND "member"."active"

640 GROUP BY "area"."id";

641

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

643

644

645 -- New view "event_seen_by_member":

646

647 CREATE VIEW "event_seen_by_member" AS

648 SELECT

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

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

651 'voting',

652 'finished_without_winner',

653 'finished_with_winner'

654 ) THEN

655 'voting'::"notify_level"

656 ELSE

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

658 'verification',

659 'canceled_after_revocation_during_verification',

660 'canceled_no_initiative_admitted'

661 ) THEN

662 'verification'::"notify_level"

663 ELSE

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

665 'discussion',

666 'canceled_after_revocation_during_discussion'

667 ) THEN

668 'discussion'::"notify_level"

669 ELSE

670 'all'::"notify_level"

671 END

672 END

673 END AS "notify_level",

674 "event".*

675 FROM "member" CROSS JOIN "event"

676 LEFT JOIN "issue"

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

678 LEFT JOIN "membership"

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

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

681 LEFT JOIN "interest"

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

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

684 LEFT JOIN "supporter"

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

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

687 LEFT JOIN "ignored_member"

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

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

690 LEFT JOIN "ignored_initiative"

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

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

693 WHERE (

694 "supporter"."member_id" NOTNULL OR

695 "interest"."member_id" NOTNULL OR

696 ( "membership"."member_id" NOTNULL AND

697 "event"."event" IN (

698 'issue_state_changed',

699 'initiative_created_in_new_issue',

700 'initiative_created_in_existing_issue',

701 'initiative_revoked' ) ) )

702 AND "ignored_member"."member_id" ISNULL

703 AND "ignored_initiative"."member_id" ISNULL;

704

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

706

707

708 -- New view "pending_notification":

709

710 CREATE VIEW "pending_notification" AS

711 SELECT

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

713 "event".*

714 FROM "member" CROSS JOIN "event"

715 LEFT JOIN "issue"

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

717 LEFT JOIN "membership"

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

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

720 LEFT JOIN "interest"

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

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

723 LEFT JOIN "supporter"

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

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

726 LEFT JOIN "ignored_member"

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

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

729 LEFT JOIN "ignored_initiative"

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

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

732 WHERE (

733 "member"."notify_event_id" ISNULL OR

734 ( "member"."notify_event_id" NOTNULL AND

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

736 AND (

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

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

739 "event"."state" IN (

740 'voting',

741 'finished_without_winner',

742 'finished_with_winner' ) ) OR

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

744 "event"."state" IN (

745 'verification',

746 'canceled_after_revocation_during_verification',

747 'canceled_no_initiative_admitted' ) ) OR

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

749 "event"."state" IN (

750 'discussion',

751 'canceled_after_revocation_during_discussion' ) ) )

752 AND (

753 "supporter"."member_id" NOTNULL OR

754 "interest"."member_id" NOTNULL OR

755 ( "membership"."member_id" NOTNULL AND

756 "event"."event" IN (

757 'issue_state_changed',

758 'initiative_created_in_new_issue',

759 'initiative_created_in_existing_issue',

760 'initiative_revoked' ) ) )

761 AND "ignored_member"."member_id" ISNULL

762 AND "ignored_initiative"."member_id" ISNULL;

763

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

765

766

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

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

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

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

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

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

773

774

775 -- Modified "delegation_chain" functions:

776

777 CREATE TYPE "delegation_chain_row" AS (

778 "index" INT4,

779 "member_id" INT4,

780 "member_valid" BOOLEAN,

781 "participation" BOOLEAN,

782 "overridden" BOOLEAN,

783 "scope_in" "delegation_scope",

784 "scope_out" "delegation_scope",

785 "disabled_out" BOOLEAN,

786 "loop" "delegation_chain_loop_tag" );

787

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

789

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

791 COMMENT ON COLUMN "delegation_chain_row"."participation" IS 'In case of delegation chains for issues: interest, for areas: membership, for global delegation chains: always null';

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

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

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

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

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

797

798

799 CREATE FUNCTION "delegation_chain"

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

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

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

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

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

805 RETURNS SETOF "delegation_chain_row"

806 LANGUAGE 'plpgsql' STABLE AS $$

807 DECLARE

808 "scope_v" "delegation_scope";

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

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

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

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

813 "output_row" "delegation_chain_row";

814 "output_rows" "delegation_chain_row"[];

815 "delegation_row" "delegation"%ROWTYPE;

816 "row_count" INT4;

817 "i" INT4;

818 "loop_v" BOOLEAN;

819 BEGIN

820 IF

821 "unit_id_p" NOTNULL AND

822 "area_id_p" ISNULL AND

823 "issue_id_p" ISNULL

824 THEN

825 "scope_v" := 'unit';

826 "unit_id_v" := "unit_id_p";

827 ELSIF

828 "unit_id_p" ISNULL AND

829 "area_id_p" NOTNULL AND

830 "issue_id_p" ISNULL

831 THEN

832 "scope_v" := 'area';

833 "area_id_v" := "area_id_p";

834 SELECT "unit_id" INTO "unit_id_v"

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

836 ELSIF

837 "unit_id_p" ISNULL AND

838 "area_id_p" ISNULL AND

839 "issue_id_p" NOTNULL

840 THEN

841 "scope_v" := 'issue';

842 SELECT "area_id" INTO "area_id_v"

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

844 SELECT "unit_id" INTO "unit_id_v"

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

846 ELSE

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

848 END IF;

849 "visited_member_ids" := '{}';

850 "loop_member_id_v" := NULL;

851 "output_rows" := '{}';

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

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

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

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

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

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

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

859 LOOP

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

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

862 ELSE

863 "visited_member_ids" :=

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

865 END IF;

866 IF "output_row"."participation" THEN

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

868 END IF;

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

870 IF EXISTS (

871 SELECT NULL FROM "member" JOIN "privilege"

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

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

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

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

876 ) THEN

877 IF "scope_v" = 'unit' THEN

878 SELECT * INTO "delegation_row" FROM "delegation"

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

880 AND "unit_id" = "unit_id_v";

881 ELSIF "scope_v" = 'area' THEN

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

883 SELECT NULL FROM "membership"

884 WHERE "area_id" = "area_id_p"

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

886 );

887 SELECT * INTO "delegation_row" FROM "delegation"

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

889 AND (

890 "unit_id" = "unit_id_v" OR

891 "area_id" = "area_id_v"

892 )

893 ORDER BY "scope" DESC;

894 ELSIF "scope_v" = 'issue' THEN

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

896 SELECT NULL FROM "interest"

897 WHERE "issue_id" = "issue_id_p"

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

899 );

900 SELECT * INTO "delegation_row" FROM "delegation"

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

902 AND (

903 "unit_id" = "unit_id_v" OR

904 "area_id" = "area_id_v" OR

905 "issue_id" = "issue_id_p"

906 )

907 ORDER BY "scope" DESC;

908 END IF;

909 ELSE

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

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

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

913 "delegation_row" := ROW(NULL);

914 END IF;

915 IF

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

917 "simulate_trustee_id_p" NOTNULL

918 THEN

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

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

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

922 ELSIF "delegation_row"."trustee_id" NOTNULL THEN

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

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

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

926 ELSIF "delegation_row"."scope" NOTNULL THEN

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

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

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

930 EXIT;

931 ELSE

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

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

934 EXIT;

935 END IF;

936 EXIT WHEN "loop_member_id_v" NOTNULL;

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

938 END LOOP;

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

940 "i" := 1;

941 "loop_v" := FALSE;

942 LOOP

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

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

945 IF "loop_v" THEN

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

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

948 ELSIF "i" = "row_count" THEN

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

950 ELSE

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

952 END IF;

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

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

955 "loop_v" := TRUE;

956 END IF;

957 IF "scope_v" = 'unit' THEN

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

959 END IF;

960 RETURN NEXT "output_row";

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

962 END LOOP;

963 RETURN;

964 END;

965 $$;

966

967 COMMENT ON FUNCTION "delegation_chain"

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

969 "unit"."id"%TYPE,

970 "area"."id"%TYPE,

971 "issue"."id"%TYPE,

972 "member"."id"%TYPE )

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

974

975

976 CREATE FUNCTION "delegation_chain"

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

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

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

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

981 RETURNS SETOF "delegation_chain_row"

982 LANGUAGE 'plpgsql' STABLE AS $$

983 DECLARE

984 "result_row" "delegation_chain_row";

985 BEGIN

986 FOR "result_row" IN

987 SELECT * FROM "delegation_chain"(

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

989 )

990 LOOP

991 RETURN NEXT "result_row";

992 END LOOP;

993 RETURN;

994 END;

995 $$;

996

997 COMMENT ON FUNCTION "delegation_chain"

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

999 "unit"."id"%TYPE,

1000 "area"."id"%TYPE,

1001 "issue"."id"%TYPE )

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

1003

1004

1005 -- Other modified functions:

1006

1007 CREATE OR REPLACE FUNCTION "lock_issue"

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

1009 RETURNS VOID

1010 LANGUAGE 'plpgsql' VOLATILE AS $$

1011 BEGIN

1012 LOCK TABLE "member" IN SHARE MODE;

1013 LOCK TABLE "privilege" IN SHARE MODE;

1014 LOCK TABLE "membership" IN SHARE MODE;

1015 LOCK TABLE "policy" IN SHARE MODE;

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

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

1018 -- share_row_lock_issue(_via_initiative)_trigger functions (which

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

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

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

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

1023 LOCK TABLE "delegation" IN SHARE MODE;

1024 LOCK TABLE "direct_population_snapshot" IN EXCLUSIVE MODE;

1025 LOCK TABLE "delegating_population_snapshot" IN EXCLUSIVE MODE;

1026 LOCK TABLE "direct_interest_snapshot" IN EXCLUSIVE MODE;

1027 LOCK TABLE "delegating_interest_snapshot" IN EXCLUSIVE MODE;

1028 LOCK TABLE "direct_supporter_snapshot" IN EXCLUSIVE MODE;

1029 RETURN;

1030 END;

1031 $$;

1032

1033 CREATE OR REPLACE FUNCTION "calculate_member_counts"()

1034 RETURNS VOID

1035 LANGUAGE 'plpgsql' VOLATILE AS $$

1036 BEGIN

1037 LOCK TABLE "member" IN SHARE MODE;

1038 LOCK TABLE "member_count" IN EXCLUSIVE MODE;

1039 LOCK TABLE "unit" IN EXCLUSIVE MODE;

1040 LOCK TABLE "area" IN EXCLUSIVE MODE;

1041 LOCK TABLE "privilege" IN SHARE MODE;

1042 LOCK TABLE "membership" IN SHARE MODE;

1043 DELETE FROM "member_count";

1044 INSERT INTO "member_count" ("total_count")

1045 SELECT "total_count" FROM "member_count_view";

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

1047 FROM "unit_member_count" AS "view"

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

1049 UPDATE "area" SET

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

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

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

1053 FROM "area_member_count" AS "view"

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

1055 RETURN;

1056 END;

1057 $$;

1058

1059 CREATE OR REPLACE FUNCTION "create_population_snapshot"

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

1061 RETURNS VOID

1062 LANGUAGE 'plpgsql' VOLATILE AS $$

1063 DECLARE

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

1065 BEGIN

1066 DELETE FROM "direct_population_snapshot"

1067 WHERE "issue_id" = "issue_id_p"

1068 AND "event" = 'periodic';

1069 DELETE FROM "delegating_population_snapshot"

1070 WHERE "issue_id" = "issue_id_p"

1071 AND "event" = 'periodic';

1072 INSERT INTO "direct_population_snapshot"

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

1074 SELECT

1075 "issue_id_p" AS "issue_id",

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

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

1078 FROM "issue"

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

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

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

1082 JOIN "privilege"

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

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

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

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

1087 UNION

1088 SELECT

1089 "issue_id_p" AS "issue_id",

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

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

1092 FROM "issue"

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

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

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

1096 JOIN "privilege"

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

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

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

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

1101 FOR "member_id_v" IN

1102 SELECT "member_id" FROM "direct_population_snapshot"

1103 WHERE "issue_id" = "issue_id_p"

1104 AND "event" = 'periodic'

1105 LOOP

1106 UPDATE "direct_population_snapshot" SET

1107 "weight" = 1 +

1108 "weight_of_added_delegations_for_population_snapshot"(

1109 "issue_id_p",

1110 "member_id_v",

1111 '{}'

1112 )

1113 WHERE "issue_id" = "issue_id_p"

1114 AND "event" = 'periodic'

1115 AND "member_id" = "member_id_v";

1116 END LOOP;

1117 RETURN;

1118 END;

1119 $$;

1120

1121 CREATE OR REPLACE FUNCTION "create_interest_snapshot"

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

1123 RETURNS VOID

1124 LANGUAGE 'plpgsql' VOLATILE AS $$

1125 DECLARE

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

1127 BEGIN

1128 DELETE FROM "direct_interest_snapshot"

1129 WHERE "issue_id" = "issue_id_p"

1130 AND "event" = 'periodic';

1131 DELETE FROM "delegating_interest_snapshot"

1132 WHERE "issue_id" = "issue_id_p"

1133 AND "event" = 'periodic';

1134 DELETE FROM "direct_supporter_snapshot"

1135 WHERE "issue_id" = "issue_id_p"

1136 AND "event" = 'periodic';

1137 INSERT INTO "direct_interest_snapshot"

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

1139 SELECT

1140 "issue_id_p" AS "issue_id",

1141 'periodic' AS "event",

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

1143 "interest"."voting_requested"

1144 FROM "issue"

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

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

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

1148 JOIN "privilege"

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

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

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

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

1153 FOR "member_id_v" IN

1154 SELECT "member_id" FROM "direct_interest_snapshot"

1155 WHERE "issue_id" = "issue_id_p"

1156 AND "event" = 'periodic'

1157 LOOP

1158 UPDATE "direct_interest_snapshot" SET

1159 "weight" = 1 +

1160 "weight_of_added_delegations_for_interest_snapshot"(

1161 "issue_id_p",

1162 "member_id_v",

1163 '{}'

1164 )

1165 WHERE "issue_id" = "issue_id_p"

1166 AND "event" = 'periodic'

1167 AND "member_id" = "member_id_v";

1168 END LOOP;

1169 INSERT INTO "direct_supporter_snapshot"

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

1171 "informed", "satisfied" )

1172 SELECT

1173 "issue_id_p" AS "issue_id",

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

1175 'periodic' AS "event",

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

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

1178 NOT EXISTS (

1179 SELECT NULL FROM "critical_opinion"

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

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

1182 ) AS "satisfied"

1183 FROM "initiative"

1184 JOIN "supporter"

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

1186 JOIN "current_draft"

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

1188 JOIN "direct_interest_snapshot"

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

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

1191 AND "event" = 'periodic'

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

1193 RETURN;

1194 END;

1195 $$;

1196

1197 CREATE OR REPLACE FUNCTION "freeze_after_snapshot"

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

1199 RETURNS VOID

1200 LANGUAGE 'plpgsql' VOLATILE AS $$

1201 DECLARE

1202 "issue_row" "issue"%ROWTYPE;

1203 "policy_row" "policy"%ROWTYPE;

1204 "initiative_row" "initiative"%ROWTYPE;

1205 BEGIN

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

1207 SELECT * INTO "policy_row"

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

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

1210 FOR "initiative_row" IN

1211 SELECT * FROM "initiative"

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

1213 LOOP

1214 IF

1215 "initiative_row"."satisfied_supporter_count" > 0 AND

1216 "initiative_row"."satisfied_supporter_count" *

1217 "policy_row"."initiative_quorum_den" >=

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

1219 THEN

1220 UPDATE "initiative" SET "admitted" = TRUE

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

1222 ELSE

1223 UPDATE "initiative" SET "admitted" = FALSE

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

1225 END IF;

1226 END LOOP;

1227 IF EXISTS (

1228 SELECT NULL FROM "initiative"

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

1230 ) THEN

1231 UPDATE "issue" SET

1232 "state" = 'voting',

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

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

1235 "fully_frozen" = now()

1236 WHERE "id" = "issue_id_p";

1237 ELSE

1238 UPDATE "issue" SET

1239 "state" = 'canceled_no_initiative_admitted',

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

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

1242 "fully_frozen" = now(),

1243 "closed" = now(),

1244 "ranks_available" = TRUE

1245 WHERE "id" = "issue_id_p";

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

1247 -- issue state has been manipulated

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

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

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

1251 END IF;

1252 RETURN;

1253 END;

1254 $$;

1255

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

1257 RETURNS VOID

1258 LANGUAGE 'plpgsql' VOLATILE AS $$

1259 DECLARE

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

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

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

1263 BEGIN

1264 PERFORM "lock_issue"("issue_id_p");

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

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

1267 DELETE FROM "delegating_voter"

1268 WHERE "issue_id" = "issue_id_p";

1269 DELETE FROM "direct_voter"

1270 WHERE "issue_id" = "issue_id_p"

1271 AND "autoreject" = TRUE;

1272 DELETE FROM "direct_voter"

1273 USING (

1274 SELECT

1275 "direct_voter"."member_id"

1276 FROM "direct_voter"

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

1278 LEFT JOIN "privilege"

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

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

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

1282 "member"."active" = FALSE OR

1283 "privilege"."voting_right" ISNULL OR

1284 "privilege"."voting_right" = FALSE

1285 )

1286 ) AS "subquery"

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

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

1289 UPDATE "direct_voter" SET "weight" = 1

1290 WHERE "issue_id" = "issue_id_p";

1291 PERFORM "add_vote_delegations"("issue_id_p");

1292 FOR "member_id_v" IN

1293 SELECT "interest"."member_id"

1294 FROM "interest"

1295 JOIN "member"

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

1297 LEFT JOIN "direct_voter"

1298 ON "interest"."member_id" = "direct_voter"."member_id"

1299 AND "interest"."issue_id" = "direct_voter"."issue_id"

1300 LEFT JOIN "delegating_voter"

1301 ON "interest"."member_id" = "delegating_voter"."member_id"

1302 AND "interest"."issue_id" = "delegating_voter"."issue_id"

1303 WHERE "interest"."issue_id" = "issue_id_p"

1304 AND "interest"."autoreject" = TRUE

1305 AND "member"."active"

1306 AND "direct_voter"."member_id" ISNULL

1307 AND "delegating_voter"."member_id" ISNULL

1308 UNION SELECT "membership"."member_id"

1309 FROM "membership"

1310 JOIN "member"

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

1312 LEFT JOIN "interest"

1313 ON "membership"."member_id" = "interest"."member_id"

1314 AND "interest"."issue_id" = "issue_id_p"

1315 LEFT JOIN "direct_voter"

1316 ON "membership"."member_id" = "direct_voter"."member_id"

1317 AND "direct_voter"."issue_id" = "issue_id_p"

1318 LEFT JOIN "delegating_voter"

1319 ON "membership"."member_id" = "delegating_voter"."member_id"

1320 AND "delegating_voter"."issue_id" = "issue_id_p"

1321 WHERE "membership"."area_id" = "area_id_v"

1322 AND "membership"."autoreject" = TRUE

1323 AND "member"."active"

1324 AND "interest"."autoreject" ISNULL

1325 AND "direct_voter"."member_id" ISNULL

1326 AND "delegating_voter"."member_id" ISNULL

1327 LOOP

1328 INSERT INTO "direct_voter"

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

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

1331 INSERT INTO "vote" (

1332 "member_id",

1333 "issue_id",

1334 "initiative_id",

1335 "grade"

1336 ) SELECT

1337 "member_id_v" AS "member_id",

1338 "issue_id_p" AS "issue_id",

1339 "id" AS "initiative_id",

1340 -1 AS "grade"

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

1342 END LOOP;

1343 PERFORM "add_vote_delegations"("issue_id_p");

1344 UPDATE "issue" SET

1345 "state" = 'calculation',

1346 "closed" = now(),

1347 "voter_count" = (

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

1349 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"

1350 )

1351 WHERE "id" = "issue_id_p";

1352 UPDATE "initiative" SET

1353 "positive_votes" = "vote_counts"."positive_votes",

1354 "negative_votes" = "vote_counts"."negative_votes",

1355 "agreed" = CASE WHEN "majority_strict" THEN

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

1357 "majority_num" *

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

1359 ELSE

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

1361 "majority_num" *

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

1363 END

1364 FROM

1365 ( SELECT

1366 "initiative"."id" AS "initiative_id",

1367 coalesce(

1368 sum(

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

1370 ),

1371 0

1372 ) AS "positive_votes",

1373 coalesce(

1374 sum(

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

1376 ),

1377 0

1378 ) AS "negative_votes"

1379 FROM "initiative"

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

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

1382 LEFT JOIN "direct_voter"

1383 ON "direct_voter"."issue_id" = "initiative"."issue_id"

1384 LEFT JOIN "vote"

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

1386 AND "vote"."member_id" = "direct_voter"."member_id"

1387 WHERE "initiative"."issue_id" = "issue_id_p"

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

1389 GROUP BY "initiative"."id"

1390 ) AS "vote_counts",

1391 "issue",

1392 "policy"

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

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

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

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

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

1398 INSERT INTO "battle" (

1399 "issue_id",

1400 "winning_initiative_id", "losing_initiative_id",

1401 "count"

1402 ) SELECT

1403 "issue_id",

1404 "winning_initiative_id", "losing_initiative_id",

1405 "count"

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

1407 END;

1408 $$;

1409

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

1411 RETURNS VOID

1412 LANGUAGE 'plpgsql' VOLATILE AS $$

1413 DECLARE

1414 "dimension_v" INTEGER;

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

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

1417 "i" INTEGER;

1418 "j" INTEGER;

1419 "k" INTEGER;

1420 "battle_row" "battle"%ROWTYPE;

1421 "rank_ary" INT4[];

1422 "rank_v" INT4;

1423 "done_v" INTEGER;

1424 "winners_ary" INTEGER[];

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

1426 BEGIN

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

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

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

1430 IF "dimension_v" = 1 THEN

1431 UPDATE "initiative" SET "rank" = 1

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

1433 ELSIF "dimension_v" > 1 THEN

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

1435 -- comparison:

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

1437 "i" := 1;

1438 "j" := 2;

1439 FOR "battle_row" IN

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

1441 ORDER BY "winning_initiative_id", "losing_initiative_id"

1442 LOOP

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

1444 IF "j" = "dimension_v" THEN

1445 "i" := "i" + 1;

1446 "j" := 1;

1447 ELSE

1448 "j" := "j" + 1;

1449 IF "j" = "i" THEN

1450 "j" := "j" + 1;

1451 END IF;

1452 END IF;

1453 END LOOP;

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

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

1456 END IF;

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

1458 -- function:

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

1460 "i" := 1;

1461 LOOP

1462 "j" := 1;

1463 LOOP

1464 IF "i" != "j" THEN

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

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

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

1468 );

1469 END IF;

1470 EXIT WHEN "j" = "dimension_v";

1471 "j" := "j" + 1;

1472 END LOOP;

1473 EXIT WHEN "i" = "dimension_v";

1474 "i" := "i" + 1;

1475 END LOOP;

1476 -- Find best paths:

1477 "i" := 1;

1478 LOOP

1479 "j" := 1;

1480 LOOP

1481 IF "i" != "j" THEN

1482 "k" := 1;

1483 LOOP

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

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

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

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

1488 END IF;

1489 ELSE

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

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

1492 END IF;

1493 END IF;

1494 END IF;

1495 EXIT WHEN "k" = "dimension_v";

1496 "k" := "k" + 1;

1497 END LOOP;

1498 END IF;

1499 EXIT WHEN "j" = "dimension_v";

1500 "j" := "j" + 1;

1501 END LOOP;

1502 EXIT WHEN "i" = "dimension_v";

1503 "i" := "i" + 1;

1504 END LOOP;

1505 -- Determine order of winners:

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

1507 "rank_v" := 1;

1508 "done_v" := 0;

1509 LOOP

1510 "winners_ary" := '{}';

1511 "i" := 1;

1512 LOOP

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

1514 "j" := 1;

1515 LOOP

1516 IF

1517 "i" != "j" AND

1518 "rank_ary"["j"] ISNULL AND

1519 "matrix"["j"]["i"] > "matrix"["i"]["j"]

1520 THEN

1521 -- someone else is better

1522 EXIT;

1523 END IF;

1524 IF "j" = "dimension_v" THEN

1525 -- noone is better

1526 "winners_ary" := "winners_ary" || "i";

1527 EXIT;

1528 END IF;

1529 "j" := "j" + 1;

1530 END LOOP;

1531 END IF;

1532 EXIT WHEN "i" = "dimension_v";

1533 "i" := "i" + 1;

1534 END LOOP;

1535 "i" := 1;

1536 LOOP

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

1538 "done_v" := "done_v" + 1;

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

1540 "i" := "i" + 1;

1541 END LOOP;

1542 EXIT WHEN "done_v" = "dimension_v";

1543 "rank_v" := "rank_v" + 1;

1544 END LOOP;

1545 -- write preliminary ranks:

1546 "i" := 1;

1547 FOR "initiative_id_v" IN

1548 SELECT "id" FROM "initiative"

1549 WHERE "issue_id" = "issue_id_p" AND "agreed"

1550 ORDER BY "id"

1551 LOOP

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

1553 WHERE "id" = "initiative_id_v";

1554 "i" := "i" + 1;

1555 END LOOP;

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

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

1558 END IF;

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

1560 "rank_v" := 1;

1561 FOR "initiative_id_v" IN

1562 SELECT "id" FROM "initiative"

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

1564 ORDER BY

1565 "rank",

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

1567 "id"

1568 LOOP

1569 UPDATE "initiative" SET "rank" = "rank_v"

1570 WHERE "id" = "initiative_id_v";

1571 "rank_v" := "rank_v" + 1;

1572 END LOOP;

1573 END IF;

1574 -- mark issue as finished

1575 UPDATE "issue" SET

1576 "state" =

1577 CASE WHEN "dimension_v" = 0 THEN

1578 'finished_without_winner'::"issue_state"

1579 ELSE

1580 'finished_with_winner'::"issue_state"

1581 END,

1582 "ranks_available" = TRUE

1583 WHERE "id" = "issue_id_p";

1584 RETURN;

1585 END;

1586 $$;

1587

1588 CREATE OR REPLACE FUNCTION "check_issue"

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

1590 RETURNS VOID

1591 LANGUAGE 'plpgsql' VOLATILE AS $$

1592 DECLARE

1593 "issue_row" "issue"%ROWTYPE;

1594 "policy_row" "policy"%ROWTYPE;

1595 "voting_requested_v" BOOLEAN;

1596 BEGIN

1597 PERFORM "lock_issue"("issue_id_p");

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

1599 -- only process open issues:

1600 IF "issue_row"."closed" ISNULL THEN

1601 SELECT * INTO "policy_row" FROM "policy"

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

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

1604 IF "issue_row"."fully_frozen" ISNULL THEN

1605 PERFORM "create_snapshot"("issue_id_p");

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

1607 END IF;

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

1609 IF "issue_row"."accepted" ISNULL THEN

1610 IF EXISTS (

1611 SELECT NULL FROM "initiative"

1612 WHERE "issue_id" = "issue_id_p"

1613 AND "supporter_count" > 0

1614 AND "supporter_count" * "policy_row"."issue_quorum_den"

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

1616 ) THEN

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

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

1619 -- NOTE: "issue_row" used later

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

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

1622 UPDATE "issue" SET

1623 "state" = "issue_row"."state",

1624 "accepted" = "issue_row"."accepted"

1625 WHERE "id" = "issue_row"."id";

1626 ELSIF

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

1628 THEN

1629 -- close issues, if admission time has expired

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

1631 UPDATE "issue" SET

1632 "state" = 'canceled_issue_not_accepted',

1633 "closed" = now()

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

1635 END IF;

1636 END IF;

1637 -- eventually half freeze issues:

1638 IF

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

1640 "issue_row"."accepted" NOTNULL AND

1641 "issue_row"."half_frozen" ISNULL

1642 THEN

1643 SELECT

1644 CASE

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

1646 TRUE

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

1648 FALSE

1649 ELSE NULL

1650 END

1651 INTO "voting_requested_v"

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

1653 IF

1654 "voting_requested_v" OR (

1655 "voting_requested_v" ISNULL AND

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

1657 )

1658 THEN

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

1660 -- NOTE: "issue_row" used later

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

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

1663 UPDATE "issue" SET

1664 "state" = "issue_row"."state",

1665 "half_frozen" = "issue_row"."half_frozen"

1666 WHERE "id" = "issue_row"."id";

1667 END IF;

1668 END IF;

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

1670 IF

1671 "issue_row"."closed" ISNULL AND

1672 NOT EXISTS (

1673 -- all initiatives are revoked

1674 SELECT NULL FROM "initiative"

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

1676 ) AND (

1677 -- and issue has not been accepted yet

1678 "issue_row"."accepted" ISNULL OR

1679 NOT EXISTS (

1680 -- or no initiatives have been revoked lately

1681 SELECT NULL FROM "initiative"

1682 WHERE "issue_id" = "issue_id_p"

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

1684 ) OR (

1685 -- or verification time has elapsed

1686 "issue_row"."half_frozen" NOTNULL AND

1687 "issue_row"."fully_frozen" ISNULL AND

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

1689 )

1690 )

1691 THEN

1692 -- NOTE: "issue_row" used later

1693 IF "issue_row"."accepted" ISNULL THEN

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

1695 ELSIF "issue_row"."half_frozen" ISNULL THEN

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

1697 ELSE

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

1699 END IF;

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

1701 UPDATE "issue" SET

1702 "state" = "issue_row"."state",

1703 "closed" = "issue_row"."closed"

1704 WHERE "id" = "issue_row"."id";

1705 END IF;

1706 -- fully freeze issue after verification time:

1707 IF

1708 "issue_row"."half_frozen" NOTNULL AND

1709 "issue_row"."fully_frozen" ISNULL AND

1710 "issue_row"."closed" ISNULL AND

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

1712 THEN

1713 PERFORM "freeze_after_snapshot"("issue_id_p");

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

1715 END IF;

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

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

1718 IF

1719 "issue_row"."closed" ISNULL AND

1720 "issue_row"."fully_frozen" NOTNULL AND

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

1722 THEN

1723 PERFORM "close_voting"("issue_id_p");

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

1725 PERFORM "calculate_ranks"("issue_id_p");

1726 END IF;

1727 END IF;

1728 RETURN;

1729 END;

1730 $$;

1731

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

1733 RETURNS VOID

1734 LANGUAGE 'plpgsql' VOLATILE AS $$

1735 DECLARE

1736 "issue_row" "issue"%ROWTYPE;

1737 BEGIN

1738 SELECT * INTO "issue_row"

1739 FROM "issue" WHERE "id" = "issue_id_p"

1740 FOR UPDATE;

1741 IF "issue_row"."cleaned" ISNULL THEN

1742 UPDATE "issue" SET

1743 "closed" = NULL,

1744 "ranks_available" = FALSE

1745 WHERE "id" = "issue_id_p";

1746 DELETE FROM "delegating_voter"

1747 WHERE "issue_id" = "issue_id_p";

1748 DELETE FROM "direct_voter"

1749 WHERE "issue_id" = "issue_id_p";

1750 DELETE FROM "delegating_interest_snapshot"

1751 WHERE "issue_id" = "issue_id_p";

1752 DELETE FROM "direct_interest_snapshot"

1753 WHERE "issue_id" = "issue_id_p";

1754 DELETE FROM "delegating_population_snapshot"

1755 WHERE "issue_id" = "issue_id_p";

1756 DELETE FROM "direct_population_snapshot"

1757 WHERE "issue_id" = "issue_id_p";

1758 DELETE FROM "non_voter"

1759 WHERE "issue_id" = "issue_id_p";

1760 DELETE FROM "delegation"

1761 WHERE "issue_id" = "issue_id_p";

1762 DELETE FROM "supporter"

1763 WHERE "issue_id" = "issue_id_p";

1764 UPDATE "issue" SET

1765 "closed" = "issue_row"."closed",

1766 "ranks_available" = "issue_row"."ranks_available",

1767 "cleaned" = now()

1768 WHERE "id" = "issue_id_p";

1769 END IF;

1770 RETURN;

1771 END;

1772 $$;

1773

1774 CREATE OR REPLACE FUNCTION "check_issue"

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

1776 RETURNS VOID

1777 LANGUAGE 'plpgsql' VOLATILE AS $$

1778 DECLARE

1779 "issue_row" "issue"%ROWTYPE;

1780 "policy_row" "policy"%ROWTYPE;

1781 "voting_requested_v" BOOLEAN;

1782 BEGIN

1783 PERFORM "lock_issue"("issue_id_p");

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

1785 -- only process open issues:

1786 IF "issue_row"."closed" ISNULL THEN

1787 SELECT * INTO "policy_row" FROM "policy"

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

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

1790 IF "issue_row"."fully_frozen" ISNULL THEN

1791 PERFORM "create_snapshot"("issue_id_p");

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

1793 END IF;

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

1795 IF "issue_row"."accepted" ISNULL THEN

1796 IF EXISTS (

1797 SELECT NULL FROM "initiative"

1798 WHERE "issue_id" = "issue_id_p"

1799 AND "supporter_count" > 0

1800 AND "supporter_count" * "policy_row"."issue_quorum_den"

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

1802 ) THEN

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

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

1805 -- NOTE: "issue_row" used later

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

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

1808 UPDATE "issue" SET

1809 "state" = "issue_row"."state",

1810 "accepted" = "issue_row"."accepted"

1811 WHERE "id" = "issue_row"."id";

1812 ELSIF

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

1814 THEN

1815 -- close issues, if admission time has expired

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

1817 UPDATE "issue" SET

1818 "state" = 'canceled_issue_not_accepted',

1819 "closed" = now()

1820 WHERE "id" = "issue_row"."id";

1821 END IF;

1822 END IF;

1823 -- eventually half freeze issues:

1824 IF

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

1826 "issue_row"."accepted" NOTNULL AND

1827 "issue_row"."half_frozen" ISNULL

1828 THEN

1829 SELECT

1830 CASE

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

1832 TRUE

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

1834 FALSE

1835 ELSE NULL

1836 END

1837 INTO "voting_requested_v"

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

1839 IF

1840 "voting_requested_v" OR (

1841 "voting_requested_v" ISNULL AND

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

1843 )

1844 THEN

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

1846 -- NOTE: "issue_row" used later

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

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

1849 UPDATE "issue" SET

1850 "state" = "issue_row"."state",

1851 "half_frozen" = "issue_row"."half_frozen"

1852 WHERE "id" = "issue_row"."id";

1853 END IF;

1854 END IF;

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

1856 IF

1857 "issue_row"."closed" ISNULL AND

1858 NOT EXISTS (

1859 -- all initiatives are revoked

1860 SELECT NULL FROM "initiative"

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

1862 ) AND (

1863 -- and issue has not been accepted yet

1864 "issue_row"."accepted" ISNULL OR

1865 NOT EXISTS (

1866 -- or no initiatives have been revoked lately

1867 SELECT NULL FROM "initiative"

1868 WHERE "issue_id" = "issue_id_p"

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

1870 ) OR (

1871 -- or verification time has elapsed

1872 "issue_row"."half_frozen" NOTNULL AND

1873 "issue_row"."fully_frozen" ISNULL AND

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

1875 )

1876 )

1877 THEN

1878 -- NOTE: "issue_row" used later

1879 IF "issue_row"."accepted" ISNULL THEN

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

1881 ELSIF "issue_row"."half_frozen" ISNULL THEN

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

1883 ELSE

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

1885 END IF;

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

1887 UPDATE "issue" SET

1888 "state" = "issue_row"."state",

1889 "closed" = "issue_row"."closed"

1890 WHERE "id" = "issue_row"."id";

1891 END IF;

1892 -- fully freeze issue after verification time:

1893 IF

1894 "issue_row"."half_frozen" NOTNULL AND

1895 "issue_row"."fully_frozen" ISNULL AND

1896 "issue_row"."closed" ISNULL AND

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

1898 THEN

1899 PERFORM "freeze_after_snapshot"("issue_id_p");

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

1901 END IF;

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

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

1904 IF

1905 "issue_row"."closed" ISNULL AND

1906 "issue_row"."fully_frozen" NOTNULL AND

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

1908 THEN

1909 PERFORM "close_voting"("issue_id_p");

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

1911 PERFORM "calculate_ranks"("issue_id_p");

1912 END IF;

1913 END IF;

1914 RETURN;

1915 END;

1916 $$;

1917

1918 CREATE OR REPLACE FUNCTION "delete_private_data"()

1919 RETURNS VOID

1920 LANGUAGE 'plpgsql' VOLATILE AS $$

1921 BEGIN

1922 UPDATE "member" SET

1923 "last_login" = NULL,

1924 "login" = NULL,

1925 "password" = NULL,

1926 "notify_email" = NULL,

1927 "notify_email_unconfirmed" = NULL,

1928 "notify_email_secret" = NULL,

1929 "notify_email_secret_expiry" = NULL,

1930 "notify_email_lock_expiry" = NULL,

1931 "password_reset_secret" = NULL,

1932 "password_reset_secret_expiry" = NULL,

1933 "organizational_unit" = NULL,

1934 "internal_posts" = NULL,

1935 "realname" = NULL,

1936 "birthday" = NULL,

1937 "address" = NULL,

1938 "email" = NULL,

1939 "xmpp_address" = NULL,

1940 "website" = NULL,

1941 "phone" = NULL,

1942 "mobile_phone" = NULL,

1943 "profession" = NULL,

1944 "external_memberships" = NULL,

1945 "external_posts" = NULL,

1946 "statement" = NULL;

1947 -- "text_search_data" is updated by triggers

1948 DELETE FROM "invite_code";

1949 DELETE FROM "setting";

1950 DELETE FROM "setting_map";

1951 DELETE FROM "member_relation_setting";

1952 DELETE FROM "member_image";

1953 DELETE FROM "contact";

1954 DELETE FROM "ignored_member";

1955 DELETE FROM "session";

1956 DELETE FROM "area_setting";

1957 DELETE FROM "issue_setting";

1958 DELETE FROM "ignored_initiative";

1959 DELETE FROM "initiative_setting";

1960 DELETE FROM "suggestion_setting";

1961 DELETE FROM "non_voter";

1962 DELETE FROM "direct_voter" USING "issue"

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

1964 AND "issue"."closed" ISNULL;

1965 RETURN;

1966 END;

1967 $$;

1968

1969

1970 -- Delete old "delegation_scope" TYPE:

1971

1972 DROP TYPE "delegation_scope_old";

1973

1974

1975 COMMIT;

1976

1977

1978 -- Generate issue states and add constraints:

1979

1980 UPDATE "issue" SET "state" =

1981 CASE

1982 WHEN "closed" ISNULL THEN

1983 CASE

1984 WHEN "accepted" ISNULL THEN

1985 'admission'::"issue_state"

1986 WHEN "half_frozen" ISNULL THEN

1987 'discussion'::"issue_state"

1988 WHEN "fully_frozen" ISNULL THEN

1989 'verification'::"issue_state"

1990 ELSE

1991 'voting'::"issue_state"

1992 END

1993 WHEN "fully_frozen" NOTNULL THEN

1994 CASE

1995 WHEN "fully_frozen" = "closed" THEN

1996 'canceled_no_initiative_admitted'::"issue_state"

1997 ELSE

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

1999 END

2000 WHEN "half_frozen" NOTNULL THEN

2001 'canceled_after_revocation_during_verification'::"issue_state"

2002 WHEN "accepted" NOTNULL THEN

2003 'canceled_after_revocation_during_discussion'::"issue_state"

2004 ELSE

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

2006 END;

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

2008 FROM "initiative"

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

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

2011 AND "initiative"."agreed";

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

2013 FROM "initiative"

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

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

2016 AND "initiative"."revoked" ISNULL;

2017

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

2019

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

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

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

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

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

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

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

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

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

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

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

2031 ) AND (

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

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

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

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

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

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

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

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

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

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

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

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

2044 ));

2045

2046

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

2048

2049 UPDATE "initiative" SET "revoked_by_member_id" = "author_id"

2050 FROM "current_draft"

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

2052 AND "initiative"."revoked" NOTNULL;

2053

2054 ALTER TABLE "initiative" ADD

2055 CONSTRAINT "all_or_none_of_revoked_and_revoked_by_member_id_must_be_null"

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

2057

2058

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

2060

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

2062

2063 ALTER TABLE "delegation" ADD CONSTRAINT "area_id_and_issue_id_set_according_to_scope"

2064 CHECK (

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

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

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

2068

2069

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

2071

2072 DELETE FROM "event";

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

2074

2075 INSERT INTO "event"

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

2077 "initiative_id", "draft_id", "suggestion_id" )

2078 SELECT * FROM (

2079 SELECT * FROM (

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

2081 "timeline"."occurrence",

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

2083 'initiative_created_in_new_issue'::"event_type"

2084 ELSE

2085 'initiative_created_in_existing_issue'::"event_type"

2086 END,

2087 "draft"."author_id",

2088 "issue"."id",

2089 CASE

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

2091 'admission'::"issue_state"

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

2093 'discussion'::"issue_state"

2094 ELSE

2095 'verification'::"issue_state"

2096 END,

2097 "initiative"."id",

2098 "draft"."id",

2099 NULL::INT8

2100 FROM "timeline"

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

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

2103 LEFT JOIN "timeline" AS "issue_creation"

2104 ON "initiative"."issue_id" = "issue_creation"."issue_id"

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

2106 AND "timeline"."occurrence" = "issue_creation"."occurrence"

2107 JOIN "draft"

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

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

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

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

2112 UNION ALL

2113 SELECT

2114 "timeline"."occurrence",

2115 'issue_state_changed'::"event_type",

2116 NULL,

2117 "issue"."id",

2118 CASE

2119 WHEN "timeline"."event" IN (

2120 'issue_canceled',

2121 'issue_finished_without_voting',

2122 'issue_finished_after_voting'

2123 ) THEN

2124 "issue"."state"

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

2126 'discussion'::"issue_state"

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

2128 'verification'::"issue_state"

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

2130 'voting'::"issue_state"

2131 END,

2132 NULL,

2133 NULL,

2134 NULL

2135 FROM "timeline"

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

2137 WHERE "timeline"."event" IN (

2138 'issue_canceled',

2139 'issue_accepted',

2140 'issue_half_frozen',

2141 'issue_finished_without_voting',

2142 'issue_voting_started',

2143 'issue_finished_after_voting' )

2144 UNION ALL

2145 SELECT

2146 "timeline"."occurrence",

2147 'initiative_revoked'::"event_type",

2148 "initiative"."revoked_by_member_id",

2149 "issue"."id",

2150 CASE

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

2152 'admission'::"issue_state"

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

2154 'discussion'::"issue_state"

2155 ELSE

2156 'verification'::"issue_state"

2157 END,

2158 "initiative"."id",

2159 "current_draft"."id",

2160 NULL

2161 FROM "timeline"

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

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

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

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

2166 UNION ALL

2167 SELECT

2168 "timeline"."occurrence",

2169 'new_draft_created'::"event_type",

2170 "draft"."author_id",

2171 "issue"."id",

2172 CASE

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

2174 'admission'::"issue_state"

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

2176 'discussion'::"issue_state"

2177 ELSE

2178 'verification'::"issue_state"

2179 END,

2180 "initiative"."id",

2181 "draft"."id",

2182 NULL

2183 FROM "timeline"

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

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

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

2187 LEFT JOIN "timeline" AS "initiative_creation"

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

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

2190 AND "timeline"."occurrence" = "initiative_creation"."occurrence"

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

2192 AND "initiative_creation"."initiative_id" ISNULL

2193 UNION ALL

2194 SELECT

2195 "timeline"."occurrence",

2196 'suggestion_created'::"event_type",

2197 "suggestion"."author_id",

2198 "issue"."id",

2199 CASE

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

2201 'admission'::"issue_state"

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

2203 'discussion'::"issue_state"

2204 ELSE

2205 'verification'::"issue_state"

2206 END,

2207 "initiative"."id",

2208 NULL,

2209 "suggestion"."id"

2210 FROM "timeline"

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

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

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

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

2215 ) AS "subquery"

2216 ORDER BY "occurrence";

2217

2218