liquid_feedback_core: fc09088587b2 update/core-update.v3.2.2-v4.0.0.sql

liquid_feedback_core

view update/core-update.v3.2.2-v4.0.0.sql @ 563:fc09088587b2

Bugfix regarding locking when logging initiative revocation (cannot combine DISTINCT with FOR SHARE)

author	jbe
date	Mon Sep 25 14:44:35 2017 +0200 (2017-09-25)
parents	c3931054bb55
children	dc48b79b8a7e 17c9cca2938b

line source

1 ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'unit_created';

2 ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'unit_updated';

3 ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'area_created';

4 ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'area_updated';

5 ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'policy_created';

6 ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'policy_updated';

7 ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'suggestion_deleted';

8 ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'member_activated';

9 ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'member_deleted';

10 ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'member_active';

11 ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'member_name_updated';

12 ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'member_profile_updated';

13 ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'member_image_updated';

14 ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'interest';

15 ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'initiator';

16 ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'support';

17 ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'support_updated';

18 ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'suggestion_rated';

19 ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'delegation';

20 ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'contact';

23 BEGIN;

26 CREATE OR REPLACE VIEW "liquid_feedback_version" AS

27 SELECT * FROM (VALUES ('4.0-dev', 4, 0, -1))

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

31 ALTER TABLE "system_setting" ADD COLUMN "snapshot_retention" INTERVAL;

33 COMMENT ON COLUMN "system_setting"."snapshot_retention" IS 'Unreferenced snapshots are retained for the given period of time after creation; set to NULL for infinite retention.';

36 CREATE TABLE "verification" (

37 "id" SERIAL8 PRIMARY KEY,

38 "requested" TIMESTAMPTZ,

39 "request_origin" JSONB,

40 "request_data" JSONB,

41 "requesting_member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,

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

43 "verified_member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,

44 "verified" TIMESTAMPTZ,

45 "verification_data" JSONB,

46 "denied" TIMESTAMPTZ,

47 "comment" TEXT,

48 CONSTRAINT "verified_and_denied_conflict" CHECK (

49 "verified" ISNULL OR "denied" ISNULL ) );

50 CREATE INDEX "verification_requested_idx" ON "verification" ("requested");

51 CREATE INDEX "verification_open_request_idx" ON "verification" ("requested") WHERE "verified" ISNULL AND "denied" ISNULL;

52 CREATE INDEX "verification_requesting_member_id_idx" ON "verification" ("requesting_member_id");

53 CREATE INDEX "verification_verified_member_id_idx" ON "verification" ("verified_member_id");

54 CREATE INDEX "verification_verified_idx" ON "verification" ("verified");

55 CREATE INDEX "verification_denied_idx" ON "verification" ("denied");

57 COMMENT ON TABLE "verification" IS 'Request to verify a participant';

59 COMMENT ON COLUMN "verification"."requested" IS 'Timestamp when request for verification has been submitted';

60 COMMENT ON COLUMN "verification"."request_origin" IS 'JSON data containing information about the origin of the request (e.g. IP address or hostname)';

61 COMMENT ON COLUMN "verification"."request_data" IS 'JSON data containing information about the entity to be verified (e.g. real name, address, etc.)';

62 COMMENT ON COLUMN "verification"."requesting_member_id" IS 'Member who requested verification';

63 COMMENT ON COLUMN "verification"."verifying_member_id" IS 'Member who processed the verification request (i.e. who accepted or denied the request)';

64 COMMENT ON COLUMN "verification"."verified_member_id" IS 'Member entry containing verified information (not necessarily equal to "requesting_member_id" but may be merged with requesting member later)';

65 COMMENT ON COLUMN "verification"."verified" IS 'Timestamp when request for verification has been accepted by authority';

66 COMMENT ON COLUMN "verification"."verification_data" IS 'JSON data containing additional verified data or information about the authority or operator who accepted or denied the request, but all public information shall be copied to "member"."identification", "member"."verification" and/or "member"."name" if applicable for setup';

67 COMMENT ON COLUMN "verification"."denied" IS 'Timestamp when request for verification has been denied by authority';

68 COMMENT ON COLUMN "verification"."comment" IS 'Administrative comment';

71 ALTER TABLE "member" ADD COLUMN "deleted" TIMESTAMPTZ;

72 ALTER TABLE "member" ADD CONSTRAINT "deleted_requires_locked"

73 CHECK ("deleted" ISNULL OR "locked" = TRUE);

75 COMMENT ON COLUMN "member"."deleted" IS 'Timestamp of deletion (set by "delete_member" function)';

78 CREATE TABLE "member_settings" (

79 "member_id" INT4 PRIMARY KEY REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,

80 "settings" JSONB NOT NULL CHECK (jsonb_typeof("settings") = 'object') );

82 COMMENT ON TABLE "member_settings" IS 'Stores a JSON document for each member containing optional (additional) settings for the respective member';

85 CREATE TABLE "member_useterms" (

86 "id" SERIAL8 PRIMARY KEY,

87 "member_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,

88 "accepted" TIMESTAMPTZ NOT NULL DEFAULT now(),

89 "contract_identifier" TEXT NOT NULL );

91 COMMENT ON TABLE "member_useterms" IS 'Keeps record of accepted terms of use; may contain multiple rows per member';

93 COMMENT ON COLUMN "member_useterms"."accepted" IS 'Point in time when user accepted the terms of use';

94 COMMENT ON COLUMN "member_useterms"."contract_identifier" IS 'String identifier to denote the accepted terms of use, including their version or revision';

97 CREATE TABLE "member_profile" (

98 "member_id" INT4 PRIMARY KEY REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,

99 "formatting_engine" TEXT,

100 "statement" TEXT,

101 "profile" JSONB NOT NULL DEFAULT '{}' CHECK (jsonb_typeof("profile") = 'object'),

102 "profile_text_data" TEXT,

103 "text_search_data" TSVECTOR );

104 CREATE INDEX "member_profile_text_search_data_idx" ON "member_profile" USING gin ("text_search_data");

105 CREATE TRIGGER "update_text_search_data"

106 BEFORE INSERT OR UPDATE ON "member_profile"

107 FOR EACH ROW EXECUTE PROCEDURE

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

109 'statement', 'profile_text_data');

110

111 COMMENT ON COLUMN "member_profile"."formatting_engine" IS 'Allows different formatting engines (i.e. wiki formats) to be used for "member_profile"."statement"';

112 COMMENT ON COLUMN "member_profile"."statement" IS 'Freely chosen text of the member for his/her profile';

113 COMMENT ON COLUMN "member_profile"."profile" IS 'Additional profile data as JSON document';

114 COMMENT ON COLUMN "member_profile"."profile_text_data" IS 'Text data from "profile" field for full text search';

115

116

117 INSERT INTO "member_profile"

118 ( "member_id", "formatting_engine", "statement", "profile")

119 SELECT

120 "id" AS "member_id",

121 "formatting_engine",

122 "statement",

123 json_build_object(

124 'organizational_unit', "organizational_unit",

125 'internal_posts', "internal_posts",

126 'realname', "realname",

127 'birthday', to_char("birthday", 'YYYY-MM-DD'),

128 'address', "address",

129 'email', "email",

130 'xmpp_address', "xmpp_address",

131 'website', "website",

132 'phone', "phone",

133 'mobile_phone', "mobile_phone",

134 'profession', "profession",

135 'external_memberships', "external_memberships",

136 'external_posts', "external_posts"

137 ) AS "profile"

138 FROM "member";

139

140 UPDATE "member_profile" SET "profile_text_data" =

141 coalesce(("profile"->>'organizational_unit') || ' ', '') ||

142 coalesce(("profile"->>'internal_posts') || ' ', '') ||

143 coalesce(("profile"->>'realname') || ' ', '') ||

144 coalesce(("profile"->>'birthday') || ' ', '') ||

145 coalesce(("profile"->>'address') || ' ', '') ||

146 coalesce(("profile"->>'email') || ' ', '') ||

147 coalesce(("profile"->>'xmpp_address') || ' ', '') ||

148 coalesce(("profile"->>'website') || ' ', '') ||

149 coalesce(("profile"->>'phone') || ' ', '') ||

150 coalesce(("profile"->>'mobile_phone') || ' ', '') ||

151 coalesce(("profile"->>'profession') || ' ', '') ||

152 coalesce(("profile"->>'external_memberships') || ' ', '') ||

153 coalesce(("profile"->>'external_posts') || ' ', '');

154

155

156 DROP VIEW "newsletter_to_send";

157 DROP VIEW "scheduled_notification_to_send";

158 DROP VIEW "member_to_notify";

159 DROP VIEW "member_eligible_to_be_notified";

160

161

162 ALTER TABLE "member" DROP COLUMN "organizational_unit";

163 ALTER TABLE "member" DROP COLUMN "internal_posts";

164 ALTER TABLE "member" DROP COLUMN "realname";

165 ALTER TABLE "member" DROP COLUMN "birthday";

166 ALTER TABLE "member" DROP COLUMN "address";

167 ALTER TABLE "member" DROP COLUMN "email";

168 ALTER TABLE "member" DROP COLUMN "xmpp_address";

169 ALTER TABLE "member" DROP COLUMN "website";

170 ALTER TABLE "member" DROP COLUMN "phone";

171 ALTER TABLE "member" DROP COLUMN "mobile_phone";

172 ALTER TABLE "member" DROP COLUMN "profession";

173 ALTER TABLE "member" DROP COLUMN "external_memberships";

174 ALTER TABLE "member" DROP COLUMN "external_posts";

175 ALTER TABLE "member" DROP COLUMN "formatting_engine";

176 ALTER TABLE "member" DROP COLUMN "statement";

177

178 ALTER TABLE "member" ADD COLUMN "location" JSONB;

179 COMMENT ON COLUMN "member"."location" IS 'Geographic location on earth as GeoJSON object';

180 CREATE INDEX "member_location_idx" ON "member" USING gist ((GeoJSON_to_ecluster("location")));

181

182 DROP TRIGGER "update_text_search_data" ON "member";

183 CREATE TRIGGER "update_text_search_data"

184 BEFORE INSERT OR UPDATE ON "member"

185 FOR EACH ROW EXECUTE PROCEDURE

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

187 "name", "identification");

188

189

190 CREATE VIEW "member_eligible_to_be_notified" AS

191 SELECT * FROM "member"

192 WHERE "activated" NOTNULL AND "locked" = FALSE;

193

194 COMMENT ON VIEW "member_eligible_to_be_notified" IS 'Filtered "member" table containing only activated and non-locked members (used as helper view for "member_to_notify" and "newsletter_to_send")';

195

196

197 CREATE VIEW "member_to_notify" AS

198 SELECT * FROM "member_eligible_to_be_notified"

199 WHERE "disable_notifications" = FALSE;

200

201 COMMENT ON VIEW "member_to_notify" IS 'Filtered "member" table containing only members that are eligible to and wish to receive notifications; NOTE: "notify_email" may still be NULL and might need to be checked by frontend (this allows other means of messaging)';

202

203

204 CREATE VIEW "scheduled_notification_to_send" AS

205 SELECT * FROM (

206 SELECT

207 "id" AS "recipient_id",

208 now() - CASE WHEN "notification_dow" ISNULL THEN

209 ( "notification_sent"::DATE + CASE

210 WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour"

211 THEN 0 ELSE 1 END

212 )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour"

213 ELSE

214 ( "notification_sent"::DATE +

215 ( 7 + "notification_dow" -

216 EXTRACT(DOW FROM

217 ( "notification_sent"::DATE + CASE

218 WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour"

219 THEN 0 ELSE 1 END

220 )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour"

221 )::INTEGER

222 ) % 7 +

223 CASE

224 WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour"

225 THEN 0 ELSE 1

226 END

227 )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour"

228 END AS "pending"

229 FROM (

230 SELECT

231 "id",

232 COALESCE("notification_sent", "activated") AS "notification_sent",

233 "notification_dow",

234 "notification_hour"

235 FROM "member_to_notify"

236 WHERE "notification_hour" NOTNULL

237 ) AS "subquery1"

238 ) AS "subquery2"

239 WHERE "pending" > '0'::INTERVAL;

240

241 COMMENT ON VIEW "scheduled_notification_to_send" IS 'Set of members where a scheduled notification mail is pending';

242

243 COMMENT ON COLUMN "scheduled_notification_to_send"."recipient_id" IS '"id" of the member who needs to receive a notification mail';

244 COMMENT ON COLUMN "scheduled_notification_to_send"."pending" IS 'Duration for which the notification mail has already been pending';

245

246

247 CREATE VIEW "newsletter_to_send" AS

248 SELECT

249 "member"."id" AS "recipient_id",

250 "newsletter"."id" AS "newsletter_id",

251 "newsletter"."published"

252 FROM "newsletter" CROSS JOIN "member_eligible_to_be_notified" AS "member"

253 LEFT JOIN "privilege" ON

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

255 "privilege"."unit_id" = "newsletter"."unit_id" AND

256 "privilege"."voting_right" = TRUE

257 LEFT JOIN "subscription" ON

258 "subscription"."member_id" = "member"."id" AND

259 "subscription"."unit_id" = "newsletter"."unit_id"

260 WHERE "newsletter"."published" <= now()

261 AND "newsletter"."sent" ISNULL

262 AND (

263 "member"."disable_notifications" = FALSE OR

264 "newsletter"."include_all_members" = TRUE )

265 AND (

266 "newsletter"."unit_id" ISNULL OR

267 "privilege"."member_id" NOTNULL OR

268 "subscription"."member_id" NOTNULL );

269

270 COMMENT ON VIEW "newsletter_to_send" IS 'List of "newsletter_id"s for each member that are due to be sent out';

271

272 COMMENT ON COLUMN "newsletter"."published" IS 'Timestamp when the newsletter was supposed to be sent out (can be used for ordering)';

273

274

275 DROP VIEW "expired_session";

276 DROP TABLE "session";

277

278

279 CREATE TABLE "session" (

280 UNIQUE ("member_id", "id"), -- index needed for foreign-key on table "token"

281 "id" SERIAL8 PRIMARY KEY,

282 "ident" TEXT NOT NULL UNIQUE,

283 "additional_secret" TEXT,

284 "logout_token" TEXT,

285 "expiry" TIMESTAMPTZ NOT NULL DEFAULT now() + '24 hours',

286 "member_id" INT4 REFERENCES "member" ("id") ON DELETE SET NULL,

287 "authority" TEXT,

288 "authority_uid" TEXT,

289 "authority_login" TEXT,

290 "needs_delegation_check" BOOLEAN NOT NULL DEFAULT FALSE,

291 "lang" TEXT );

292 CREATE INDEX "session_expiry_idx" ON "session" ("expiry");

293

294 COMMENT ON TABLE "session" IS 'Sessions, i.e. for a web-frontend or API layer';

295

296 COMMENT ON COLUMN "session"."ident" IS 'Secret session identifier (i.e. random string)';

297 COMMENT ON COLUMN "session"."additional_secret" IS 'Additional field to store a secret, which can be used against CSRF attacks';

298 COMMENT ON COLUMN "session"."logout_token" IS 'Optional token to authorize logout through external component';

299 COMMENT ON COLUMN "session"."member_id" IS 'Reference to member, who is logged in';

300 COMMENT ON COLUMN "session"."authority" IS 'Temporary store for "member"."authority" during member account creation';

301 COMMENT ON COLUMN "session"."authority_uid" IS 'Temporary store for "member"."authority_uid" during member account creation';

302 COMMENT ON COLUMN "session"."authority_login" IS 'Temporary store for "member"."authority_login" during member account creation';

303 COMMENT ON COLUMN "session"."needs_delegation_check" IS 'Set to TRUE, if member must perform a delegation check to proceed with login; see column "last_delegation_check" in "member" table';

304 COMMENT ON COLUMN "session"."lang" IS 'Language code of the selected language';

305

306

307 CREATE TYPE "authflow" AS ENUM ('code', 'token');

308

309 COMMENT ON TYPE "authflow" IS 'OAuth 2.0 flows: ''code'' = Authorization Code flow, ''token'' = Implicit flow';

310

311

312 CREATE TABLE "system_application" (

313 "id" SERIAL4 PRIMARY KEY,

314 "name" TEXT NOT NULL,

315 "discovery_baseurl" TEXT,

316 "client_id" TEXT NOT NULL UNIQUE,

317 "default_redirect_uri" TEXT NOT NULL,

318 "cert_common_name" TEXT,

319 "client_cred_scope" TEXT,

320 "flow" "authflow",

321 "automatic_scope" TEXT,

322 "permitted_scope" TEXT,

323 "forbidden_scope" TEXT );

324

325 COMMENT ON TABLE "system_application" IS 'OAuth 2.0 clients that are registered by the system administrator';

326

327 COMMENT ON COLUMN "system_application"."name" IS 'Human readable name of application';

328 COMMENT ON COLUMN "system_application"."discovery_baseurl" IS 'Base URL for application discovery; NULL for hidden application';

329 COMMENT ON COLUMN "system_application"."client_id" IS 'OAuth 2.0 "client_id"';

330 COMMENT ON COLUMN "system_application"."cert_common_name" IS 'Value for CN field of TLS client certificate';

331 COMMENT ON COLUMN "system_application"."client_cred_scope" IS 'Space-separated list of scopes; If set, Client Credentials Grant is allowed; value determines scope';

332 COMMENT ON COLUMN "system_application"."flow" IS 'If set to ''code'' or ''token'', then Authorization Code or Implicit flow is allowed respectively';

333 COMMENT ON COLUMN "system_application"."automatic_scope" IS 'Space-separated list of scopes; Automatically granted scope for Authorization Code or Implicit flow';

334 COMMENT ON COLUMN "system_application"."permitted_scope" IS 'Space-separated list of scopes; If set, scope that members may grant to the application is limited to the given value';

335 COMMENT ON COLUMN "system_application"."forbidden_scope" IS 'Space-separated list of scopes that may not be granted to the application by a member';

336

337

338 CREATE TABLE "system_application_redirect_uri" (

339 PRIMARY KEY ("system_application_id", "redirect_uri"),

340 "system_application_id" INT4 REFERENCES "system_application" ("id") ON DELETE CASCADE ON UPDATE CASCADE,

341 "redirect_uri" TEXT );

342

343 COMMENT ON TABLE "system_application_redirect_uri" IS 'Additional OAuth 2.0 redirection endpoints, which may be selected through the "redirect_uri" GET parameter';

344

345

346 CREATE TABLE "dynamic_application_scope" (

347 PRIMARY KEY ("redirect_uri", "flow", "scope"),

348 "redirect_uri" TEXT,

349 "flow" TEXT,

350 "scope" TEXT,

351 "expiry" TIMESTAMPTZ NOT NULL DEFAULT now() + '24 hours' );

352 CREATE INDEX "dynamic_application_scope_redirect_uri_scope_idx" ON "dynamic_application_scope" ("redirect_uri", "flow", "scope");

353 CREATE INDEX "dynamic_application_scope_expiry_idx" ON "dynamic_application_scope" ("expiry");

354

355 COMMENT ON TABLE "dynamic_application_scope" IS 'Dynamic OAuth 2.0 client registration data';

356

357 COMMENT ON COLUMN "dynamic_application_scope"."redirect_uri" IS 'Redirection endpoint for which the registration has been done';

358 COMMENT ON COLUMN "dynamic_application_scope"."flow" IS 'OAuth 2.0 flow for which the registration has been done (see also "system_application"."flow")';

359 COMMENT ON COLUMN "dynamic_application_scope"."scope" IS 'Single scope without space characters (use multiple rows for more scopes)';

360 COMMENT ON COLUMN "dynamic_application_scope"."expiry" IS 'Expiry unless renewed';

361

362

363 CREATE TABLE "member_application" (

364 "id" SERIAL4 PRIMARY KEY,

365 UNIQUE ("system_application_id", "member_id"),

366 UNIQUE ("domain", "member_id"),

367 "member_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,

368 "system_application_id" INT4 REFERENCES "system_application" ("id") ON DELETE CASCADE ON UPDATE CASCADE,

369 "domain" TEXT,

370 "session_id" INT8,

371 FOREIGN KEY ("member_id", "session_id") REFERENCES "session" ("member_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,

372 "scope" TEXT NOT NULL,

373 CONSTRAINT "system_application_or_domain_but_not_both" CHECK (

374 ("system_application_id" NOTNULL AND "domain" ISNULL) OR

375 ("system_application_id" ISNULL AND "domain" NOTNULL) ) );

376 CREATE INDEX "member_application_member_id_idx" ON "member_application" ("member_id");

377

378 COMMENT ON TABLE "member_application" IS 'Application authorized by a member';

379

380 COMMENT ON COLUMN "member_application"."system_application_id" IS 'If set, then application is a system application';

381 COMMENT ON COLUMN "member_application"."domain" IS 'If set, then application is a dynamically registered OAuth 2.0 client; value is set to client''s domain';

382 COMMENT ON COLUMN "member_application"."session_id" IS 'If set, registration ends with session';

383 COMMENT ON COLUMN "member_application"."scope" IS 'Granted scope as space-separated list of strings';

384

385

386 CREATE TYPE "token_type" AS ENUM ('authorization', 'refresh', 'access');

387

388 COMMENT ON TYPE "token_type" IS 'Types for entries in "token" table';

389

390

391 CREATE TABLE "token" (

392 "id" SERIAL8 PRIMARY KEY,

393 "token" TEXT NOT NULL UNIQUE,

394 "token_type" "token_type" NOT NULL,

395 "authorization_token_id" INT8 REFERENCES "token" ("id") ON DELETE CASCADE ON UPDATE CASCADE,

396 "member_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,

397 "system_application_id" INT4 REFERENCES "system_application" ("id") ON DELETE CASCADE ON UPDATE CASCADE,

398 "domain" TEXT,

399 FOREIGN KEY ("member_id", "domain") REFERENCES "member_application" ("member_id", "domain") ON DELETE CASCADE ON UPDATE CASCADE,

400 "session_id" INT8,

401 FOREIGN KEY ("member_id", "session_id") REFERENCES "session" ("member_id", "id") ON DELETE RESTRICT ON UPDATE CASCADE, -- NOTE: deletion through "detach_token_from_session" trigger on table "session"

402 "redirect_uri" TEXT,

403 "redirect_uri_explicit" BOOLEAN,

404 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),

405 "expiry" TIMESTAMPTZ DEFAULT now() + '1 hour',

406 "used" BOOLEAN NOT NULL DEFAULT FALSE,

407 "scope" TEXT NOT NULL,

408 CONSTRAINT "access_token_needs_expiry"

409 CHECK ("token_type" != 'access'::"token_type" OR "expiry" NOTNULL),

410 CONSTRAINT "authorization_token_needs_redirect_uri"

411 CHECK ("token_type" != 'authorization'::"token_type" OR ("redirect_uri" NOTNULL AND "redirect_uri_explicit" NOTNULL) ) );

412 CREATE INDEX "token_member_id_idx" ON "token" ("member_id");

413 CREATE INDEX "token_authorization_token_id_idx" ON "token" ("authorization_token_id");

414 CREATE INDEX "token_expiry_idx" ON "token" ("expiry");

415

416 COMMENT ON TABLE "token" IS 'Issued OAuth 2.0 authorization codes and access/refresh tokens';

417

418 COMMENT ON COLUMN "token"."token" IS 'String secret (the actual token)';

419 COMMENT ON COLUMN "token"."authorization_token_id" IS 'Reference to authorization token if tokens were originally created by Authorization Code flow (allows deletion if code is used twice)';

420 COMMENT ON COLUMN "token"."system_application_id" IS 'If set, then application is a system application';

421 COMMENT ON COLUMN "token"."domain" IS 'If set, then application is a dynamically registered OAuth 2.0 client; value is set to client''s domain';

422 COMMENT ON COLUMN "token"."session_id" IS 'If set, then token is tied to a session; Deletion of session sets value to NULL (via trigger) and removes all scopes without suffix ''_detached''';

423 COMMENT ON COLUMN "token"."redirect_uri" IS 'Authorization codes must be bound to a specific redirect URI';

424 COMMENT ON COLUMN "token"."redirect_uri_explicit" IS 'True if ''redirect_uri'' parameter was explicitly specified during authorization request of the Authorization Code flow (since RFC 6749 requires it to be included in the access token request in this case)';

425 COMMENT ON COLUMN "token"."expiry" IS 'Point in time when code or token expired; In case of "used" authorization codes, authorization code must not be deleted as long as tokens exist which refer to the authorization code';

426 COMMENT ON COLUMN "token"."used" IS 'Can be set to TRUE for authorization codes that have been used (enables deletion of authorization codes that were used twice)';

427 COMMENT ON COLUMN "token"."scope" IS 'Scope as space-separated list of strings (detached scopes are marked with ''_detached'' suffix)';

428

429

430 CREATE TABLE "token_scope" (

431 PRIMARY KEY ("token_id", "index"),

432 "token_id" INT8 REFERENCES "token" ("id") ON DELETE CASCADE ON UPDATE CASCADE,

433 "index" INT4,

434 "scope" TEXT NOT NULL );

435

436 COMMENT ON TABLE "token_scope" IS 'Additional scopes for an authorization code if ''scope1'', ''scope2'', etc. parameters were used during Authorization Code flow to request several access and refresh tokens at once';

437

438

439 ALTER TABLE "policy" ADD COLUMN "issue_quorum" INT4 CHECK ("issue_quorum" >= 1);

440 ALTER TABLE "policy" ADD COLUMN "initiative_quorum" INT4 CHECK ("initiative_quorum" >= 1);

441

442 UPDATE "policy" SET "issue_quorum" = 1 WHERE "issue_quorum_num" NOTNULL;

443 UPDATE "policy" SET "initiative_quorum" = 1;

444

445 ALTER TABLE "policy" ALTER COLUMN "initiative_quorum" SET NOT NULL;

446

447 ALTER TABLE "policy" DROP CONSTRAINT "timing";

448 ALTER TABLE "policy" DROP CONSTRAINT "issue_quorum_if_and_only_if_not_polling";

449 ALTER TABLE "policy" ADD CONSTRAINT

450 "issue_quorum_if_and_only_if_not_polling" CHECK (

451 "polling" = ("issue_quorum" ISNULL) AND

452 "polling" = ("issue_quorum_num" ISNULL) AND

453 "polling" = ("issue_quorum_den" ISNULL)

454 );

455 ALTER TABLE "policy" ADD CONSTRAINT

456 "min_admission_time_smaller_than_max_admission_time" CHECK (

457 "min_admission_time" < "max_admission_time"

458 );

459 ALTER TABLE "policy" ADD CONSTRAINT

460 "timing_null_or_not_null_constraints" CHECK (

461 ( "polling" = FALSE AND

462 "min_admission_time" NOTNULL AND "max_admission_time" NOTNULL AND

463 "discussion_time" NOTNULL AND

464 "verification_time" NOTNULL AND

465 "voting_time" NOTNULL ) OR

466 ( "polling" = TRUE AND

467 "min_admission_time" ISNULL AND "max_admission_time" ISNULL AND

468 "discussion_time" NOTNULL AND

469 "verification_time" NOTNULL AND

470 "voting_time" NOTNULL ) OR

471 ( "polling" = TRUE AND

472 "min_admission_time" ISNULL AND "max_admission_time" ISNULL AND

473 "discussion_time" ISNULL AND

474 "verification_time" ISNULL AND

475 "voting_time" ISNULL )

476 );

477

478 COMMENT ON COLUMN "policy"."min_admission_time" IS 'Minimum duration of issue state ''admission''; Minimum time an issue stays open; Note: should be considerably smaller than "max_admission_time"';

479 COMMENT ON COLUMN "policy"."issue_quorum" IS 'Absolute number of supporters needed by an initiative to be "accepted", i.e. pass from ''admission'' to ''discussion'' state';

480 COMMENT ON COLUMN "policy"."issue_quorum_num" IS 'Numerator of supporter quorum to be reached by an initiative to be "accepted", i.e. pass from ''admission'' to ''discussion'' state (Note: further requirements apply, see quorum columns of "area" table)';

481 COMMENT ON COLUMN "policy"."issue_quorum_den" IS 'Denominator of supporter quorum to be reached by an initiative to be "accepted", i.e. pass from ''admission'' to ''discussion'' state (Note: further requirements apply, see quorum columns of "area" table)';

482 COMMENT ON COLUMN "policy"."initiative_quorum" IS 'Absolute number of satisfied supporters to be reached by an initiative to be "admitted" for voting';

483 COMMENT ON COLUMN "policy"."initiative_quorum_num" IS 'Numerator of satisfied supporter quorum to be reached by an initiative to be "admitted" for voting';

484 COMMENT ON COLUMN "policy"."initiative_quorum_den" IS 'Denominator of satisfied supporter quorum to be reached by an initiative to be "admitted" for voting';

485

486

487 ALTER TABLE "unit" ADD COLUMN "location" JSONB;

488

489 CREATE INDEX "unit_location_idx" ON "unit" USING gist ((GeoJSON_to_ecluster("location")));

490

491 COMMENT ON COLUMN "unit"."member_count" IS 'Count of members as determined by column "voting_right" in table "privilege" (only active members counted)';

492 COMMENT ON COLUMN "unit"."location" IS 'Geographic location on earth as GeoJSON object indicating valid coordinates for initiatives of issues with this policy';

493

494

495 DROP INDEX "area_unit_id_idx";

496 ALTER TABLE "area" ADD UNIQUE ("unit_id", "id");

497

498 ALTER TABLE "area" ADD COLUMN "quorum_standard" NUMERIC NOT NULL DEFAULT 2 CHECK ("quorum_standard" >= 0);

499 ALTER TABLE "area" ADD COLUMN "quorum_issues" NUMERIC NOT NULL DEFAULT 1 CHECK ("quorum_issues" > 0);

500 ALTER TABLE "area" ADD COLUMN "quorum_time" INTERVAL NOT NULL DEFAULT '1 day' CHECK ("quorum_time" > '0'::INTERVAL);

501 ALTER TABLE "area" ADD COLUMN "quorum_exponent" NUMERIC NOT NULL DEFAULT 0.5 CHECK ("quorum_exponent" BETWEEN 0 AND 1);

502 ALTER TABLE "area" ADD COLUMN "quorum_factor" NUMERIC NOT NULL DEFAULT 2 CHECK ("quorum_factor" >= 1);

503 ALTER TABLE "area" ADD COLUMN "quorum_den" INT4 CHECK ("quorum_den" > 0);

504 ALTER TABLE "area" ADD COLUMN "issue_quorum" INT4;

505 ALTER TABLE "area" ADD COLUMN "location" JSONB;

506

507 ALTER TABLE "area" DROP COLUMN "direct_member_count";

508 ALTER TABLE "area" DROP COLUMN "member_weight";

509

510 CREATE INDEX "area_location_idx" ON "area" USING gist ((GeoJSON_to_ecluster("location")));

511

512 COMMENT ON COLUMN "area"."quorum_standard" IS 'Parameter for dynamic issue quorum: default quorum';

513 COMMENT ON COLUMN "area"."quorum_issues" IS 'Parameter for dynamic issue quorum: number of open issues for default quorum';

514 COMMENT ON COLUMN "area"."quorum_time" IS 'Parameter for dynamic issue quorum: discussion, verification, and voting time of open issues to result in the given default quorum (open issues with shorter time will increase quorum and open issues with longer time will reduce quorum if "quorum_exponent" is greater than zero)';

515 COMMENT ON COLUMN "area"."quorum_exponent" IS 'Parameter for dynamic issue quorum: set to zero to ignore duration of open issues, set to one to fully take duration of open issues into account; defaults to 0.5';

516 COMMENT ON COLUMN "area"."quorum_factor" IS 'Parameter for dynamic issue quorum: factor to increase dynamic quorum when a number of "quorum_issues" issues with "quorum_time" duration of discussion, verification, and voting phase are added to the number of open admitted issues';

517 COMMENT ON COLUMN "area"."quorum_den" IS 'Parameter for dynamic issue quorum: when set, dynamic quorum is multiplied with "issue"."population" and divided by "quorum_den" (and then rounded up)';

518 COMMENT ON COLUMN "area"."issue_quorum" IS 'Additional dynamic issue quorum based on the number of open accepted issues; automatically calculated by function "issue_admission"';

519 COMMENT ON COLUMN "area"."external_reference" IS 'Opaque data field to store an external reference';

520 COMMENT ON COLUMN "area"."location" IS 'Geographic location on earth as GeoJSON object indicating valid coordinates for initiatives of issues with this policy';

521

522

523 CREATE TABLE "snapshot" (

524 UNIQUE ("issue_id", "id"), -- index needed for foreign-key on table "issue"

525 "id" SERIAL8 PRIMARY KEY,

526 "calculated" TIMESTAMPTZ NOT NULL DEFAULT now(),

527 "population" INT4,

528 "area_id" INT4 NOT NULL REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,

529 "issue_id" INT4 ); -- NOTE: following (cyclic) reference is added later through ALTER command: REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE

530

531 COMMENT ON TABLE "snapshot" IS 'Point in time when a snapshot of one or more issues (see table "snapshot_issue") and their supporter situation is taken';

532

533

534 CREATE TABLE "snapshot_population" (

535 PRIMARY KEY ("snapshot_id", "member_id"),

536 "snapshot_id" INT8 REFERENCES "snapshot" ("id") ON DELETE CASCADE ON UPDATE CASCADE,

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

538

539 COMMENT ON TABLE "snapshot_population" IS 'Members with voting right relevant for a snapshot';

540

541

542 ALTER TABLE "issue" ADD UNIQUE ("area_id", "id");

543 DROP INDEX "issue_area_id_idx";

544 ALTER TABLE "issue" ADD UNIQUE ("policy_id", "id");

545 DROP INDEX "issue_policy_id_idx";

546

547 ALTER TABLE "issue" RENAME COLUMN "snapshot" TO "calculated";

548

549 ALTER TABLE "issue" ADD COLUMN "latest_snapshot_id" INT8 REFERENCES "snapshot" ("id") ON DELETE RESTRICT ON UPDATE CASCADE;

550 ALTER TABLE "issue" ADD COLUMN "admission_snapshot_id" INT8 REFERENCES "snapshot" ("id") ON DELETE SET NULL ON UPDATE CASCADE;

551 ALTER TABLE "issue" ADD COLUMN "half_freeze_snapshot_id" INT8;

552 ALTER TABLE "issue" ADD COLUMN "full_freeze_snapshot_id" INT8;

553

554 ALTER TABLE "issue" ADD FOREIGN KEY ("id", "half_freeze_snapshot_id")

555 REFERENCES "snapshot" ("issue_id", "id") ON DELETE RESTRICT ON UPDATE CASCADE;

556 ALTER TABLE "issue" ADD FOREIGN KEY ("id", "full_freeze_snapshot_id")

557 REFERENCES "snapshot" ("issue_id", "id") ON DELETE RESTRICT ON UPDATE CASCADE;

558

559 ALTER TABLE "issue" DROP CONSTRAINT "last_snapshot_on_full_freeze";

560 ALTER TABLE "issue" DROP CONSTRAINT "freeze_requires_snapshot";

561 ALTER TABLE "issue" DROP CONSTRAINT "set_both_or_none_of_snapshot_and_latest_snapshot_event";

562

563 CREATE INDEX "issue_state_idx" ON "issue" ("state");

564 CREATE INDEX "issue_latest_snapshot_id" ON "issue" ("latest_snapshot_id");

565 CREATE INDEX "issue_admission_snapshot_id" ON "issue" ("admission_snapshot_id");

566 CREATE INDEX "issue_half_freeze_snapshot_id" ON "issue" ("half_freeze_snapshot_id");

567 CREATE INDEX "issue_full_freeze_snapshot_id" ON "issue" ("full_freeze_snapshot_id");

568

569 COMMENT ON COLUMN "issue"."accepted" IS 'Point in time, when the issue was accepted for further discussion (see columns "issue_quorum_num" and "issue_quorum_den" of table "policy" and quorum columns of table "area")';

570 COMMENT ON COLUMN "issue"."calculated" IS 'Point in time, when most recent snapshot and "population" and *_count values were calculated (NOTE: value is equal to "snapshot"."calculated" of snapshot with "id"="issue"."latest_snapshot_id")';

571 COMMENT ON COLUMN "issue"."latest_snapshot_id" IS 'Snapshot id of most recent snapshot';

572 COMMENT ON COLUMN "issue"."admission_snapshot_id" IS 'Snapshot id when issue as accepted or canceled in admission phase';

573 COMMENT ON COLUMN "issue"."half_freeze_snapshot_id" IS 'Snapshot id at end of discussion phase';

574 COMMENT ON COLUMN "issue"."full_freeze_snapshot_id" IS 'Snapshot id at end of verification phase';

575 COMMENT ON COLUMN "issue"."population" IS 'Count of members in "snapshot_population" table with "snapshot_id" equal to "issue"."latest_snapshot_id"';

576

577

578 ALTER TABLE "snapshot" ADD FOREIGN KEY ("issue_id") REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE;

579

580

581 ALTER TABLE "initiative" DROP CONSTRAINT "initiative_suggested_initiative_id_fkey";

582 ALTER TABLE "initiative" ADD FOREIGN KEY ("suggested_initiative_id") REFERENCES "initiative" ("id") ON DELETE SET NULL ON UPDATE CASCADE;

583

584 ALTER TABLE "initiative" ADD COLUMN "location" JSONB;

585 ALTER TABLE "initiative" ADD COLUMN "draft_text_search_data" TSVECTOR;

586

587 CREATE INDEX "initiative_location_idx" ON "initiative" USING gist ((GeoJSON_to_ecluster("location")));

588 CREATE INDEX "initiative_draft_text_search_data_idx" ON "initiative" USING gin ("draft_text_search_data");

589

590 COMMENT ON COLUMN "initiative"."location" IS 'Geographic location of initiative as GeoJSON object (automatically copied from most recent draft)';

591

592

593 ALTER TABLE "draft" ADD COLUMN "location" JSONB;

594

595 CREATE INDEX "draft_location_idx" ON "draft" USING gist ((GeoJSON_to_ecluster("location")));

596

597 COMMENT ON COLUMN "draft"."location" IS 'Geographic location of initiative as GeoJSON object (automatically copied to "initiative" table if draft is most recent)';

598

599

600 ALTER TABLE "suggestion" ADD COLUMN "location" JSONB;

601

602 CREATE INDEX "suggestion_location_idx" ON "suggestion" USING gist ((GeoJSON_to_ecluster("location")));

603

604 COMMENT ON COLUMN "suggestion"."location" IS 'Geographic location of suggestion as GeoJSON object';

605

606

607 CREATE TABLE "temporary_suggestion_counts" (

608 "id" INT8 PRIMARY KEY, -- NOTE: no referential integrity due to performance/locking issues; REFERENCES "suggestion" ("id") ON DELETE CASCADE ON UPDATE CASCADE,

609 "minus2_unfulfilled_count" INT4 NOT NULL,

610 "minus2_fulfilled_count" INT4 NOT NULL,

611 "minus1_unfulfilled_count" INT4 NOT NULL,

612 "minus1_fulfilled_count" INT4 NOT NULL,

613 "plus1_unfulfilled_count" INT4 NOT NULL,

614 "plus1_fulfilled_count" INT4 NOT NULL,

615 "plus2_unfulfilled_count" INT4 NOT NULL,

616 "plus2_fulfilled_count" INT4 NOT NULL );

617

618 COMMENT ON TABLE "temporary_suggestion_counts" IS 'Holds certain calculated values (suggestion counts) temporarily until they can be copied into table "suggestion"';

619

620 COMMENT ON COLUMN "temporary_suggestion_counts"."id" IS 'References "suggestion" ("id") but has no referential integrity trigger associated, due to performance/locking issues';

621

622

623 ALTER TABLE "interest" DROP CONSTRAINT "interest_member_id_fkey";

624 ALTER TABLE "interest" ADD FOREIGN KEY ("member_id") REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE;

625

626

627 ALTER TABLE "initiator" DROP CONSTRAINT "initiator_member_id_fkey";

628 ALTER TABLE "initiator" ADD FOREIGN KEY ("member_id") REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE;

629

630

631 ALTER TABLE "delegation" DROP CONSTRAINT "delegation_trustee_id_fkey";

632 ALTER TABLE "delegation" ADD FOREIGN KEY ("trustee_id") REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE;

633

634

635 CREATE TABLE "snapshot_issue" (

636 PRIMARY KEY ("snapshot_id", "issue_id"),

637 "snapshot_id" INT8 REFERENCES "snapshot" ("id") ON DELETE CASCADE ON UPDATE CASCADE,

638 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE );

639 CREATE INDEX "snapshot_issue_issue_id_idx" ON "snapshot_issue" ("issue_id");

640

641 COMMENT ON TABLE "snapshot_issue" IS 'List of issues included in a snapshot';

642

643 COMMENT ON COLUMN "snapshot_issue"."issue_id" IS 'Issue being part of the snapshot; Trigger "delete_snapshot_on_partial_delete" on "snapshot_issue" table will delete snapshot if an issue of the snapshot is deleted.';

644

645

646 ALTER TABLE "direct_interest_snapshot" RENAME TO "direct_interest_snapshot_old"; -- TODO!

647 ALTER INDEX "direct_interest_snapshot_pkey" RENAME TO "direct_interest_snapshot_old_pkey";

648 ALTER INDEX "direct_interest_snapshot_member_id_idx" RENAME TO "direct_interest_snapshot_old_member_id_idx";

649

650 ALTER TABLE "delegating_interest_snapshot" RENAME TO "delegating_interest_snapshot_old"; -- TODO!

651 ALTER INDEX "delegating_interest_snapshot_pkey" RENAME TO "delegating_interest_snapshot_old_pkey";

652 ALTER INDEX "delegating_interest_snapshot_member_id_idx" RENAME TO "delegating_interest_snapshot_old_member_id_idx";

653

654 ALTER TABLE "direct_supporter_snapshot" RENAME TO "direct_supporter_snapshot_old"; -- TODO!

655 ALTER INDEX "direct_supporter_snapshot_pkey" RENAME TO "direct_supporter_snapshot_old_pkey";

656 ALTER INDEX "direct_supporter_snapshot_member_id_idx" RENAME TO "direct_supporter_snapshot_old_member_id_idx";

657

658

659 CREATE TABLE "direct_interest_snapshot" (

660 PRIMARY KEY ("snapshot_id", "issue_id", "member_id"),

661 "snapshot_id" INT8,

662 "issue_id" INT4,

663 FOREIGN KEY ("snapshot_id", "issue_id")

664 REFERENCES "snapshot_issue" ("snapshot_id", "issue_id") ON DELETE CASCADE ON UPDATE CASCADE,

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

666 "weight" INT4 );

667 CREATE INDEX "direct_interest_snapshot_member_id_idx" ON "direct_interest_snapshot" ("member_id");

668

669 COMMENT ON TABLE "direct_interest_snapshot" IS 'Snapshot of active members having an "interest" in the "issue"; for corrections refer to column "issue_notice" of "issue" table';

670

671 COMMENT ON COLUMN "direct_interest_snapshot"."weight" IS 'Weight of member (1 or higher) according to "delegating_interest_snapshot"';

672

673

674 CREATE TABLE "delegating_interest_snapshot" (

675 PRIMARY KEY ("snapshot_id", "issue_id", "member_id"),

676 "snapshot_id" INT8,

677 "issue_id" INT4,

678 FOREIGN KEY ("snapshot_id", "issue_id")

679 REFERENCES "snapshot_issue" ("snapshot_id", "issue_id") ON DELETE CASCADE ON UPDATE CASCADE,

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

681 "weight" INT4,

682 "scope" "delegation_scope" NOT NULL,

683 "delegate_member_ids" INT4[] NOT NULL );

684 CREATE INDEX "delegating_interest_snapshot_member_id_idx" ON "delegating_interest_snapshot" ("member_id");

685

686 COMMENT ON TABLE "delegating_interest_snapshot" IS 'Delegations increasing the weight of entries in the "direct_interest_snapshot" table; for corrections refer to column "issue_notice" of "issue" table';

687

688 COMMENT ON COLUMN "delegating_interest_snapshot"."member_id" IS 'Delegating member';

689 COMMENT ON COLUMN "delegating_interest_snapshot"."weight" IS 'Intermediate weight';

690 COMMENT ON COLUMN "delegating_interest_snapshot"."delegate_member_ids" IS 'Chain of members who act as delegates; last entry referes to "member_id" column of table "direct_interest_snapshot"';

691

692

693 CREATE TABLE "direct_supporter_snapshot" (

694 PRIMARY KEY ("snapshot_id", "initiative_id", "member_id"),

695 "snapshot_id" INT8,

696 "issue_id" INT4 NOT NULL,

697 FOREIGN KEY ("snapshot_id", "issue_id")

698 REFERENCES "snapshot_issue" ("snapshot_id", "issue_id") ON DELETE CASCADE ON UPDATE CASCADE,

699 "initiative_id" INT4,

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

701 "draft_id" INT8 NOT NULL,

702 "informed" BOOLEAN NOT NULL,

703 "satisfied" BOOLEAN NOT NULL,

704 FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,

705 FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE NO ACTION ON UPDATE CASCADE,

706 FOREIGN KEY ("snapshot_id", "issue_id", "member_id") REFERENCES "direct_interest_snapshot" ("snapshot_id", "issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );

707 CREATE INDEX "direct_supporter_snapshot_member_id_idx" ON "direct_supporter_snapshot" ("member_id");

708

709 COMMENT ON TABLE "direct_supporter_snapshot" IS 'Snapshot of supporters of initiatives (weight is stored in "direct_interest_snapshot"); for corrections refer to column "issue_notice" of "issue" table';

710

711 COMMENT ON COLUMN "direct_supporter_snapshot"."issue_id" IS 'WARNING: No index: For selections use column "initiative_id" and join via table "initiative" where neccessary';

712 COMMENT ON COLUMN "direct_supporter_snapshot"."informed" IS 'Supporter has seen the latest draft of the initiative';

713 COMMENT ON COLUMN "direct_supporter_snapshot"."satisfied" IS 'Supporter has no "critical_opinion"s';

714

715

716 ALTER TABLE "non_voter" DROP CONSTRAINT "non_voter_pkey";

717 DROP INDEX "non_voter_member_id_idx";

718

719 ALTER TABLE "non_voter" ADD PRIMARY KEY ("member_id", "issue_id");

720 CREATE INDEX "non_voter_issue_id_idx" ON "non_voter" ("issue_id");

721

722

723 INSERT INTO "member_useterms" ("member_id", "accepted", "contract_identifier")

724 SELECT

725 "member_id",

726 regexp_replace("value", '^accepted at ', '')::TIMESTAMPTZ AS "accepted",

727 regexp_replace("key", '^use_terms_checkbox_', '') AS "contract_identifier"

728 FROM "setting" WHERE "key" LIKE 'use_terms_checkbox_%';

729

730

731 DROP TABLE "setting";

732 DROP TABLE "setting_map";

733 DROP TABLE "member_relation_setting";

734 DROP TABLE "unit_setting";

735 DROP TABLE "area_setting";

736 DROP TABLE "initiative_setting";

737 DROP TABLE "suggestion_setting";

738

739

740 ALTER TABLE "event" ADD COLUMN "other_member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE;

741 ALTER TABLE "event" ADD COLUMN "scope" "delegation_scope";

742 ALTER TABLE "event" ADD COLUMN "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE;

743 ALTER TABLE "event" ADD COLUMN "area_id" INT4;

744 ALTER TABLE "event" ADD COLUMN "policy_id" INT4 REFERENCES "policy" ("id") ON DELETE CASCADE ON UPDATE CASCADE;

745 ALTER TABLE "event" ADD COLUMN "boolean_value" BOOLEAN;

746 ALTER TABLE "event" ADD COLUMN "numeric_value" INT4;

747 ALTER TABLE "event" ADD COLUMN "text_value" TEXT;

748 ALTER TABLE "event" ADD COLUMN "old_text_value" TEXT;

749

750 ALTER TABLE "event" ADD FOREIGN KEY ("unit_id", "area_id") REFERENCES "area" ("unit_id", "id") ON DELETE CASCADE ON UPDATE CASCADE;

751 ALTER TABLE "event" ADD FOREIGN KEY ("area_id", "issue_id") REFERENCES "issue" ("area_id", "id") ON DELETE CASCADE ON UPDATE CASCADE;

752 ALTER TABLE "event" ADD FOREIGN KEY ("policy_id", "issue_id") REFERENCES "issue" ("policy_id", "id") ON DELETE CASCADE ON UPDATE CASCADE;

753

754 ALTER TABLE "event" DROP CONSTRAINT "event_initiative_id_fkey1";

755 ALTER TABLE "event" DROP CONSTRAINT "null_constr_for_issue_state_changed";

756 ALTER TABLE "event" DROP CONSTRAINT "null_constr_for_initiative_creation_or_revocation_or_new_draft";

757 ALTER TABLE "event" DROP CONSTRAINT "null_constr_for_suggestion_creation";

758

759 UPDATE "event" SET

760 "unit_id" = "area"."unit_id",

761 "area_id" = "issue"."area_id",

762 "policy_id" = "issue"."policy_id"

763 FROM "issue", "area"

764 WHERE "issue"."id" = "event"."issue_id" AND "area"."id" = "issue"."area_id";

765

766 ALTER TABLE "event" ADD CONSTRAINT "constr_for_issue_state_changed" CHECK (

767 "event" != 'issue_state_changed' OR (

768 "member_id" ISNULL AND

769 "other_member_id" ISNULL AND

770 "scope" ISNULL AND

771 "unit_id" NOTNULL AND

772 "area_id" NOTNULL AND

773 "policy_id" NOTNULL AND

774 "issue_id" NOTNULL AND

775 "state" NOTNULL AND

776 "initiative_id" ISNULL AND

777 "draft_id" ISNULL AND

778 "suggestion_id" ISNULL AND

779 "boolean_value" ISNULL AND

780 "numeric_value" ISNULL AND

781 "text_value" ISNULL AND

782 "old_text_value" ISNULL ));

783 ALTER TABLE "event" ADD CONSTRAINT "constr_for_initiative_creation_or_revocation_or_new_draft" CHECK (

784 "event" NOT IN (

785 'initiative_created_in_new_issue',

786 'initiative_created_in_existing_issue',

787 'initiative_revoked',

788 'new_draft_created'

789 ) OR (

790 "member_id" NOTNULL AND

791 "other_member_id" ISNULL AND

792 "scope" ISNULL AND

793 "unit_id" NOTNULL AND

794 "area_id" NOTNULL AND

795 "policy_id" NOTNULL AND

796 "issue_id" NOTNULL AND

797 "state" NOTNULL AND

798 "initiative_id" NOTNULL AND

799 "draft_id" NOTNULL AND

800 "suggestion_id" ISNULL AND

801 "boolean_value" ISNULL AND

802 "numeric_value" ISNULL AND

803 "text_value" ISNULL AND

804 "old_text_value" ISNULL ));

805 ALTER TABLE "event" ADD CONSTRAINT "constr_for_suggestion_creation" CHECK (

806 "event" != 'suggestion_created' OR (

807 "member_id" NOTNULL AND

808 "other_member_id" ISNULL AND

809 "scope" ISNULL AND

810 "unit_id" NOTNULL AND

811 "area_id" NOTNULL AND

812 "policy_id" NOTNULL AND

813 "issue_id" NOTNULL AND

814 "state" NOTNULL AND

815 "initiative_id" NOTNULL AND

816 "draft_id" ISNULL AND

817 "suggestion_id" NOTNULL AND

818 "boolean_value" ISNULL AND

819 "numeric_value" ISNULL AND

820 "text_value" ISNULL AND

821 "old_text_value" ISNULL ));

822 ALTER TABLE "event" ADD CONSTRAINT "constr_for_suggestion_removal" CHECK (

823 "event" != 'suggestion_deleted' OR (

824 "member_id" ISNULL AND

825 "other_member_id" ISNULL AND

826 "scope" ISNULL AND

827 "unit_id" NOTNULL AND

828 "area_id" NOTNULL AND

829 "policy_id" NOTNULL AND

830 "issue_id" NOTNULL AND

831 "state" NOTNULL AND

832 "initiative_id" NOTNULL AND

833 "draft_id" ISNULL AND

834 "suggestion_id" NOTNULL AND

835 "boolean_value" ISNULL AND

836 "numeric_value" ISNULL AND

837 "text_value" ISNULL AND

838 "old_text_value" ISNULL ));

839 ALTER TABLE "event" ADD CONSTRAINT "constr_for_value_less_member_event" CHECK (

840 "event" NOT IN (

841 'member_activated',

842 'member_deleted',

843 'member_profile_updated',

844 'member_image_updated'

845 ) OR (

846 "member_id" NOTNULL AND

847 "other_member_id" ISNULL AND

848 "scope" ISNULL AND

849 "unit_id" ISNULL AND

850 "area_id" ISNULL AND

851 "policy_id" ISNULL AND

852 "issue_id" ISNULL AND

853 "state" ISNULL AND

854 "initiative_id" ISNULL AND

855 "draft_id" ISNULL AND

856 "suggestion_id" ISNULL AND

857 "boolean_value" ISNULL AND

858 "numeric_value" ISNULL AND

859 "text_value" ISNULL AND

860 "old_text_value" ISNULL ));

861 ALTER TABLE "event" ADD CONSTRAINT "constr_for_member_active" CHECK (

862 "event" != 'member_active' OR (

863 "member_id" NOTNULL AND

864 "other_member_id" ISNULL AND

865 "scope" ISNULL AND

866 "unit_id" ISNULL AND

867 "area_id" ISNULL AND

868 "policy_id" ISNULL AND

869 "issue_id" ISNULL AND

870 "state" ISNULL AND

871 "initiative_id" ISNULL AND

872 "draft_id" ISNULL AND

873 "suggestion_id" ISNULL AND

874 "boolean_value" NOTNULL AND

875 "numeric_value" ISNULL AND

876 "text_value" ISNULL AND

877 "old_text_value" ISNULL ));

878 ALTER TABLE "event" ADD CONSTRAINT "constr_for_member_name_updated" CHECK (

879 "event" != 'member_name_updated' OR (

880 "member_id" NOTNULL AND

881 "other_member_id" ISNULL AND

882 "scope" ISNULL AND

883 "unit_id" ISNULL AND

884 "area_id" ISNULL AND

885 "policy_id" ISNULL AND

886 "issue_id" ISNULL AND

887 "state" ISNULL AND

888 "initiative_id" ISNULL AND

889 "draft_id" ISNULL AND

890 "suggestion_id" ISNULL AND

891 "boolean_value" ISNULL AND

892 "numeric_value" ISNULL AND

893 "text_value" NOTNULL AND

894 "old_text_value" NOTNULL ));

895 ALTER TABLE "event" ADD CONSTRAINT "constr_for_interest" CHECK (

896 "event" != 'interest' OR (

897 "member_id" NOTNULL AND

898 "other_member_id" ISNULL AND

899 "scope" ISNULL AND

900 "unit_id" NOTNULL AND

901 "area_id" NOTNULL AND

902 "policy_id" NOTNULL AND

903 "issue_id" NOTNULL AND

904 "state" NOTNULL AND

905 "initiative_id" ISNULL AND

906 "draft_id" ISNULL AND

907 "suggestion_id" ISNULL AND

908 "boolean_value" NOTNULL AND

909 "numeric_value" ISNULL AND

910 "text_value" ISNULL AND

911 "old_text_value" ISNULL ));

912 ALTER TABLE "event" ADD CONSTRAINT "constr_for_initiator" CHECK (

913 "event" != 'initiator' OR (

914 "member_id" NOTNULL AND

915 "other_member_id" ISNULL AND

916 "scope" ISNULL AND

917 "unit_id" NOTNULL AND

918 "area_id" NOTNULL AND

919 "policy_id" NOTNULL AND

920 "issue_id" NOTNULL AND

921 "state" NOTNULL AND

922 "initiative_id" NOTNULL AND

923 "draft_id" ISNULL AND

924 "suggestion_id" ISNULL AND

925 "boolean_value" NOTNULL AND

926 "numeric_value" ISNULL AND

927 "text_value" ISNULL AND

928 "old_text_value" ISNULL ));

929 ALTER TABLE "event" ADD CONSTRAINT "constr_for_support" CHECK (

930 "event" != 'support' OR (

931 "member_id" NOTNULL AND

932 "other_member_id" ISNULL AND

933 "scope" ISNULL AND

934 "unit_id" NOTNULL AND

935 "area_id" NOTNULL AND

936 "policy_id" NOTNULL AND

937 "issue_id" NOTNULL AND

938 "state" NOTNULL AND

939 "initiative_id" NOTNULL AND

940 ("draft_id" NOTNULL) = ("boolean_value" = TRUE) AND

941 "suggestion_id" ISNULL AND

942 "boolean_value" NOTNULL AND

943 "numeric_value" ISNULL AND

944 "text_value" ISNULL AND

945 "old_text_value" ISNULL ));

946 ALTER TABLE "event" ADD CONSTRAINT "constr_for_support_updated" CHECK (

947 "event" != 'support_updated' OR (

948 "member_id" NOTNULL AND

949 "other_member_id" ISNULL AND

950 "scope" ISNULL AND

951 "unit_id" NOTNULL AND

952 "area_id" NOTNULL AND

953 "policy_id" NOTNULL AND

954 "issue_id" NOTNULL AND

955 "state" NOTNULL AND

956 "initiative_id" NOTNULL AND

957 "draft_id" NOTNULL AND

958 "suggestion_id" ISNULL AND

959 "boolean_value" ISNULL AND

960 "numeric_value" ISNULL AND

961 "text_value" ISNULL AND

962 "old_text_value" ISNULL ));

963 ALTER TABLE "event" ADD CONSTRAINT "constr_for_suggestion_rated" CHECK (

964 "event" != 'suggestion_rated' OR (

965 "member_id" NOTNULL AND

966 "other_member_id" ISNULL AND

967 "scope" ISNULL AND

968 "unit_id" NOTNULL AND

969 "area_id" NOTNULL AND

970 "policy_id" NOTNULL AND

971 "issue_id" NOTNULL AND

972 "state" NOTNULL AND

973 "initiative_id" NOTNULL AND

974 "draft_id" ISNULL AND

975 "suggestion_id" NOTNULL AND

976 ("boolean_value" NOTNULL) = ("numeric_value" != 0) AND

977 "numeric_value" NOTNULL AND

978 "numeric_value" IN (-2, -1, 0, 1, 2) AND

979 "text_value" ISNULL AND

980 "old_text_value" ISNULL ));

981 ALTER TABLE "event" ADD CONSTRAINT "constr_for_delegation" CHECK (

982 "event" != 'delegation' OR (

983 "member_id" NOTNULL AND

984 ("other_member_id" NOTNULL) OR ("boolean_value" = FALSE) AND

985 "scope" NOTNULL AND

986 "unit_id" NOTNULL AND

987 ("area_id" NOTNULL) = ("scope" != 'unit'::"delegation_scope") AND

988 "policy_id" ISNULL AND

989 ("issue_id" NOTNULL) = ("scope" = 'issue'::"delegation_scope") AND

990 ("state" NOTNULL) = ("scope" = 'issue'::"delegation_scope") AND

991 "initiative_id" ISNULL AND

992 "draft_id" ISNULL AND

993 "suggestion_id" ISNULL AND

994 "boolean_value" NOTNULL AND

995 "numeric_value" ISNULL AND

996 "text_value" ISNULL AND

997 "old_text_value" ISNULL ));

998 ALTER TABLE "event" ADD CONSTRAINT "constr_for_contact" CHECK (

999 "event" != 'contact' OR (

1000 "member_id" NOTNULL AND

1001 "other_member_id" NOTNULL AND

1002 "scope" ISNULL AND

1003 "unit_id" ISNULL AND

1004 "area_id" ISNULL AND

1005 "policy_id" ISNULL AND

1006 "issue_id" ISNULL AND

1007 "state" ISNULL AND

1008 "initiative_id" ISNULL AND

1009 "draft_id" ISNULL AND

1010 "suggestion_id" ISNULL AND

1011 "boolean_value" NOTNULL AND

1012 "numeric_value" ISNULL AND

1013 "text_value" ISNULL AND

1014 "old_text_value" ISNULL ));

1015

1016

1017 ALTER TABLE "notification_event_sent" RENAME TO "event_processed";

1018 ALTER INDEX "notification_event_sent_singleton_idx" RENAME TO "event_processed_singleton_idx";

1019

1020 COMMENT ON TABLE "event_processed" IS 'This table stores one row with the last event_id, for which event handlers have been executed (e.g. notifications having been sent out)';

1021 COMMENT ON INDEX "event_processed_singleton_idx" IS 'This index ensures that "event_processed" only contains one row maximum.';

1022

1023

1024 CREATE FUNCTION "write_event_unit_trigger"()

1025 RETURNS TRIGGER

1026 LANGUAGE 'plpgsql' VOLATILE AS $$

1027 DECLARE

1028 "event_v" "event_type";

1029 BEGIN

1030 IF TG_OP = 'UPDATE' THEN

1031 IF OLD."active" = FALSE AND NEW."active" = FALSE THEN

1032 RETURN NULL;

1033 --ELSIF OLD."active" = FALSE AND NEW."active" = TRUE THEN

1034 -- "event_v" := 'unit_created';

1035 --ELSIF OLD."active" = TRUE AND NEW."active" = FALSE THEN

1036 -- "event_v" := 'unit_deleted';

1037 ELSIF OLD != NEW THEN

1038 "event_v" := 'unit_updated';

1039 ELSE

1040 RETURN NULL;

1041 END IF;

1042 ELSE

1043 "event_v" := 'unit_created';

1044 END IF;

1045 INSERT INTO "event" ("event", "unit_id") VALUES ("event_v", NEW."id");

1046 RETURN NULL;

1047 END;

1048 $$;

1049

1050 CREATE TRIGGER "write_event_unit" AFTER INSERT OR UPDATE ON "unit"

1051 FOR EACH ROW EXECUTE PROCEDURE "write_event_unit_trigger"();

1052

1053 COMMENT ON FUNCTION "write_event_unit_trigger"() IS 'Implementation of trigger "write_event_unit" on table "unit"';

1054 COMMENT ON TRIGGER "write_event_unit" ON "unit" IS 'Create entry in "event" table on new or changed/disabled units';

1055

1056

1057 CREATE FUNCTION "write_event_area_trigger"()

1058 RETURNS TRIGGER

1059 LANGUAGE 'plpgsql' VOLATILE AS $$

1060 DECLARE

1061 "event_v" "event_type";

1062 BEGIN

1063 IF TG_OP = 'UPDATE' THEN

1064 IF OLD."active" = FALSE AND NEW."active" = FALSE THEN

1065 RETURN NULL;

1066 --ELSIF OLD."active" = FALSE AND NEW."active" = TRUE THEN

1067 -- "event_v" := 'area_created';

1068 --ELSIF OLD."active" = TRUE AND NEW."active" = FALSE THEN

1069 -- "event_v" := 'area_deleted';

1070 ELSIF OLD != NEW THEN

1071 "event_v" := 'area_updated';

1072 ELSE

1073 RETURN NULL;

1074 END IF;

1075 ELSE

1076 "event_v" := 'area_created';

1077 END IF;

1078 INSERT INTO "event" ("event", "area_id") VALUES ("event_v", NEW."id");

1079 RETURN NULL;

1080 END;

1081 $$;

1082

1083 CREATE TRIGGER "write_event_area" AFTER INSERT OR UPDATE ON "area"

1084 FOR EACH ROW EXECUTE PROCEDURE "write_event_area_trigger"();

1085

1086 COMMENT ON FUNCTION "write_event_area_trigger"() IS 'Implementation of trigger "write_event_area" on table "area"';

1087 COMMENT ON TRIGGER "write_event_area" ON "area" IS 'Create entry in "event" table on new or changed/disabled areas';

1088

1089

1090 CREATE FUNCTION "write_event_policy_trigger"()

1091 RETURNS TRIGGER

1092 LANGUAGE 'plpgsql' VOLATILE AS $$

1093 DECLARE

1094 "event_v" "event_type";

1095 BEGIN

1096 IF TG_OP = 'UPDATE' THEN

1097 IF OLD."active" = FALSE AND NEW."active" = FALSE THEN

1098 RETURN NULL;

1099 --ELSIF OLD."active" = FALSE AND NEW."active" = TRUE THEN

1100 -- "event_v" := 'policy_created';

1101 --ELSIF OLD."active" = TRUE AND NEW."active" = FALSE THEN

1102 -- "event_v" := 'policy_deleted';

1103 ELSIF OLD != NEW THEN

1104 "event_v" := 'policy_updated';

1105 ELSE

1106 RETURN NULL;

1107 END IF;

1108 ELSE

1109 "event_v" := 'policy_created';

1110 END IF;

1111 INSERT INTO "event" ("event", "policy_id") VALUES ("event_v", NEW."id");

1112 RETURN NULL;

1113 END;

1114 $$;

1115

1116 CREATE TRIGGER "write_event_policy" AFTER INSERT OR UPDATE ON "policy"

1117 FOR EACH ROW EXECUTE PROCEDURE "write_event_policy_trigger"();

1118

1119 COMMENT ON FUNCTION "write_event_policy_trigger"() IS 'Implementation of trigger "write_event_policy" on table "policy"';

1120 COMMENT ON TRIGGER "write_event_policy" ON "policy" IS 'Create entry in "event" table on new or changed/disabled policies';

1121

1122

1123 CREATE OR REPLACE FUNCTION "write_event_issue_state_changed_trigger"()

1124 RETURNS TRIGGER

1125 LANGUAGE 'plpgsql' VOLATILE AS $$

1126 DECLARE

1127 "area_row" "area"%ROWTYPE;

1128 BEGIN

1129 IF NEW."state" != OLD."state" THEN

1130 SELECT * INTO "area_row" FROM "area" WHERE "id" = NEW."area_id"

1131 FOR SHARE;

1132 INSERT INTO "event" (

1133 "event",

1134 "unit_id", "area_id", "policy_id", "issue_id", "state"

1135 ) VALUES (

1136 'issue_state_changed',

1137 "area_row"."unit_id", NEW."area_id", NEW."policy_id",

1138 NEW."id", NEW."state"

1139 );

1140 END IF;

1141 RETURN NULL;

1142 END;

1143 $$;

1144

1145

1146 CREATE OR REPLACE FUNCTION "write_event_initiative_or_draft_created_trigger"()

1147 RETURNS TRIGGER

1148 LANGUAGE 'plpgsql' VOLATILE AS $$

1149 DECLARE

1150 "initiative_row" "initiative"%ROWTYPE;

1151 "issue_row" "issue"%ROWTYPE;

1152 "area_row" "area"%ROWTYPE;

1153 "event_v" "event_type";

1154 BEGIN

1155 SELECT * INTO "initiative_row" FROM "initiative"

1156 WHERE "id" = NEW."initiative_id" FOR SHARE;

1157 SELECT * INTO "issue_row" FROM "issue"

1158 WHERE "id" = "initiative_row"."issue_id" FOR SHARE;

1159 SELECT * INTO "area_row" FROM "area"

1160 WHERE "id" = "issue_row"."area_id" FOR SHARE;

1161 IF EXISTS (

1162 SELECT NULL FROM "draft"

1163 WHERE "initiative_id" = NEW."initiative_id" AND "id" != NEW."id"

1164 FOR SHARE

1165 ) THEN

1166 "event_v" := 'new_draft_created';

1167 ELSE

1168 IF EXISTS (

1169 SELECT NULL FROM "initiative"

1170 WHERE "issue_id" = "initiative_row"."issue_id"

1171 AND "id" != "initiative_row"."id"

1172 FOR SHARE

1173 ) THEN

1174 "event_v" := 'initiative_created_in_existing_issue';

1175 ELSE

1176 "event_v" := 'initiative_created_in_new_issue';

1177 END IF;

1178 END IF;

1179 INSERT INTO "event" (

1180 "event", "member_id",

1181 "unit_id", "area_id", "policy_id", "issue_id", "state",

1182 "initiative_id", "draft_id"

1183 ) VALUES (

1184 "event_v", NEW."author_id",

1185 "area_row"."unit_id", "issue_row"."area_id", "issue_row"."policy_id",

1186 "initiative_row"."issue_id", "issue_row"."state",

1187 NEW."initiative_id", NEW."id"

1188 );

1189 RETURN NULL;

1190 END;

1191 $$;

1192

1193

1194 CREATE OR REPLACE FUNCTION "write_event_initiative_revoked_trigger"()

1195 RETURNS TRIGGER

1196 LANGUAGE 'plpgsql' VOLATILE AS $$

1197 DECLARE

1198 "issue_row" "issue"%ROWTYPE;

1199 "area_row" "area"%ROWTYPE;

1200 "draft_id_v" "draft"."id"%TYPE;

1201 BEGIN

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

1203 -- NOTE: lock for primary key update to avoid new drafts

1204 SELECT NULL FROM "initiative" WHERE "id" = NEW."id" FOR UPDATE;

1205 SELECT * INTO "issue_row" FROM "issue"

1206 WHERE "id" = NEW."issue_id" FOR SHARE;

1207 SELECT * INTO "area_row" FROM "area"

1208 WHERE "id" = "issue_row"."area_id" FOR SHARE;

1209 -- NOTE: FOR SHARE cannot be used with DISTINCT in view "current_draft"

1210 SELECT NULL FROM "draft" WHERE "initiative_id" = NEW."id" FOR SHARE;

1211 SELECT "id" INTO "draft_id_v" FROM "current_draft"

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

1213 INSERT INTO "event" (

1214 "event", "member_id",

1215 "unit_id", "area_id", "policy_id", "issue_id", "state",

1216 "initiative_id", "draft_id"

1217 ) VALUES (

1218 'initiative_revoked', NEW."revoked_by_member_id",

1219 "area_row"."unit_id", "issue_row"."area_id",

1220 "issue_row"."policy_id",

1221 NEW."issue_id", "issue_row"."state",

1222 NEW."id", "draft_id_v"

1223 );

1224 END IF;

1225 RETURN NULL;

1226 END;

1227 $$;

1228

1229

1230 CREATE OR REPLACE FUNCTION "write_event_suggestion_created_trigger"()

1231 RETURNS TRIGGER

1232 LANGUAGE 'plpgsql' VOLATILE AS $$

1233 DECLARE

1234 "initiative_row" "initiative"%ROWTYPE;

1235 "issue_row" "issue"%ROWTYPE;

1236 "area_row" "area"%ROWTYPE;

1237 BEGIN

1238 SELECT * INTO "initiative_row" FROM "initiative"

1239 WHERE "id" = NEW."initiative_id" FOR SHARE;

1240 SELECT * INTO "issue_row" FROM "issue"

1241 WHERE "id" = "initiative_row"."issue_id" FOR SHARE;

1242 SELECT * INTO "area_row" FROM "area"

1243 WHERE "id" = "issue_row"."area_id" FOR SHARE;

1244 INSERT INTO "event" (

1245 "event", "member_id",

1246 "unit_id", "area_id", "policy_id", "issue_id", "state",

1247 "initiative_id", "suggestion_id"

1248 ) VALUES (

1249 'suggestion_created', NEW."author_id",

1250 "area_row"."unit_id", "issue_row"."area_id", "issue_row"."policy_id",

1251 "initiative_row"."issue_id", "issue_row"."state",

1252 NEW."initiative_id", NEW."id"

1253 );

1254 RETURN NULL;

1255 END;

1256 $$;

1257

1258

1259 CREATE FUNCTION "write_event_suggestion_removed_trigger"()

1260 RETURNS TRIGGER

1261 LANGUAGE 'plpgsql' VOLATILE AS $$

1262 DECLARE

1263 "initiative_row" "initiative"%ROWTYPE;

1264 "issue_row" "issue"%ROWTYPE;

1265 "area_row" "area"%ROWTYPE;

1266 BEGIN

1267 SELECT * INTO "initiative_row" FROM "initiative"

1268 WHERE "id" = OLD."initiative_id" FOR SHARE;

1269 IF "initiative_row"."id" NOTNULL THEN

1270 SELECT * INTO "issue_row" FROM "issue"

1271 WHERE "id" = "initiative_row"."issue_id" FOR SHARE;

1272 SELECT * INTO "area_row" FROM "area"

1273 WHERE "id" = "issue_row"."area_id" FOR SHARE;

1274 INSERT INTO "event" (

1275 "event",

1276 "unit_id", "area_id", "policy_id", "issue_id", "state",

1277 "initiative_id", "suggestion_id"

1278 ) VALUES (

1279 'suggestion_deleted',

1280 "area_row"."unit_id", "issue_row"."area_id",

1281 "issue_row"."policy_id",

1282 "initiative_row"."issue_id", "issue_row"."state",

1283 OLD."initiative_id", OLD."id"

1284 );

1285 END IF;

1286 RETURN NULL;

1287 END;

1288 $$;

1289

1290 CREATE TRIGGER "write_event_suggestion_removed"

1291 AFTER DELETE ON "suggestion" FOR EACH ROW EXECUTE PROCEDURE

1292 "write_event_suggestion_removed_trigger"();

1293

1294 COMMENT ON FUNCTION "write_event_suggestion_removed_trigger"() IS 'Implementation of trigger "write_event_suggestion_removed" on table "issue"';

1295 COMMENT ON TRIGGER "write_event_suggestion_removed" ON "suggestion" IS 'Create entry in "event" table on suggestion creation';

1296

1297

1298 CREATE FUNCTION "write_event_member_trigger"()

1299 RETURNS TRIGGER

1300 LANGUAGE 'plpgsql' VOLATILE AS $$

1301 BEGIN

1302 IF TG_OP = 'INSERT' THEN

1303 IF NEW."activated" NOTNULL AND NEW."deleted" ISNULL THEN

1304 INSERT INTO "event" ("event", "member_id")

1305 VALUES ('member_activated', NEW."id");

1306 END IF;

1307 IF NEW."active" THEN

1308 INSERT INTO "event" ("event", "member_id", "boolean_value")

1309 VALUES ('member_active', NEW."id", TRUE);

1310 END IF;

1311 ELSIF TG_OP = 'UPDATE' THEN

1312 IF OLD."id" != NEW."id" THEN

1313 RAISE EXCEPTION 'Cannot change member ID';

1314 END IF;

1315 IF

1316 (OLD."activated" ISNULL OR OLD."deleted" NOTNULL) AND

1317 NEW."activated" NOTNULL AND NEW."deleted" ISNULL

1318 THEN

1319 INSERT INTO "event" ("event", "member_id")

1320 VALUES ('member_activated', NEW."id");

1321 END IF;

1322 IF OLD."active" != NEW."active" THEN

1323 INSERT INTO "event" ("event", "member_id", "boolean_value") VALUES (

1324 'member_active', NEW."id", NEW."active"

1325 );

1326 END IF;

1327 IF OLD."name" != NEW."name" THEN

1328 INSERT INTO "event" (

1329 "event", "member_id", "text_value", "old_text_value"

1330 ) VALUES (

1331 'member_name_updated', NEW."id", NEW."name", OLD."name"

1332 );

1333 END IF;

1334 IF

1335 OLD."activated" NOTNULL AND OLD."deleted" ISNULL AND

1336 (NEW."activated" ISNULL OR NEW."deleted" NOTNULL)

1337 THEN

1338 INSERT INTO "event" ("event", "member_id")

1339 VALUES ('member_deleted', NEW."id");

1340 END IF;

1341 END IF;

1342 RETURN NULL;

1343 END;

1344 $$;

1345

1346 CREATE TRIGGER "write_event_member"

1347 AFTER INSERT OR UPDATE ON "member" FOR EACH ROW EXECUTE PROCEDURE

1348 "write_event_member_trigger"();

1349

1350 COMMENT ON FUNCTION "write_event_member_trigger"() IS 'Implementation of trigger "write_event_member" on table "member"';

1351 COMMENT ON TRIGGER "write_event_member" ON "member" IS 'Create entries in "event" table on insertion to member table';

1352

1353

1354 CREATE FUNCTION "write_event_member_profile_updated_trigger"()

1355 RETURNS TRIGGER

1356 LANGUAGE 'plpgsql' VOLATILE AS $$

1357 BEGIN

1358 IF TG_OP = 'DELETE' OR TG_OP = 'UPDATE' THEN

1359 IF EXISTS (SELECT NULL FROM "member" WHERE "id" = OLD."member_id") THEN

1360 INSERT INTO "event" ("event", "member_id") VALUES (

1361 'member_profile_updated', OLD."member_id"

1362 );

1363 END IF;

1364 END IF;

1365 IF TG_OP = 'UPDATE' THEN

1366 IF OLD."member_id" = NEW."member_id" THEN

1367 RETURN NULL;

1368 END IF;

1369 END IF;

1370 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN

1371 INSERT INTO "event" ("event", "member_id") VALUES (

1372 'member_profile_updated', NEW."member_id"

1373 );

1374 END IF;

1375 RETURN NULL;

1376 END;

1377 $$;

1378

1379 CREATE TRIGGER "write_event_member_profile_updated"

1380 AFTER INSERT OR UPDATE OR DELETE ON "member_profile"

1381 FOR EACH ROW EXECUTE PROCEDURE

1382 "write_event_member_profile_updated_trigger"();

1383

1384 COMMENT ON FUNCTION "write_event_member_profile_updated_trigger"() IS 'Implementation of trigger "write_event_member_profile_updated" on table "member_profile"';

1385 COMMENT ON TRIGGER "write_event_member_profile_updated" ON "member_profile" IS 'Creates entries in "event" table on member profile update';

1386

1387

1388 CREATE FUNCTION "write_event_member_image_updated_trigger"()

1389 RETURNS TRIGGER

1390 LANGUAGE 'plpgsql' VOLATILE AS $$

1391 BEGIN

1392 IF TG_OP = 'DELETE' OR TG_OP = 'UPDATE' THEN

1393 IF NOT OLD."scaled" THEN

1394 IF EXISTS (SELECT NULL FROM "member" WHERE "id" = OLD."member_id") THEN

1395 INSERT INTO "event" ("event", "member_id") VALUES (

1396 'member_image_updated', OLD."member_id"

1397 );

1398 END IF;

1399 END IF;

1400 END IF;

1401 IF TG_OP = 'UPDATE' THEN

1402 IF

1403 OLD."member_id" = NEW."member_id" AND

1404 OLD."scaled" = NEW."scaled"

1405 THEN

1406 RETURN NULL;

1407 END IF;

1408 END IF;

1409 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN

1410 IF NOT NEW."scaled" THEN

1411 INSERT INTO "event" ("event", "member_id") VALUES (

1412 'member_image_updated', NEW."member_id"

1413 );

1414 END IF;

1415 END IF;

1416 RETURN NULL;

1417 END;

1418 $$;

1419

1420 CREATE TRIGGER "write_event_member_image_updated"

1421 AFTER INSERT OR UPDATE OR DELETE ON "member_image"

1422 FOR EACH ROW EXECUTE PROCEDURE

1423 "write_event_member_image_updated_trigger"();

1424

1425 COMMENT ON FUNCTION "write_event_member_image_updated_trigger"() IS 'Implementation of trigger "write_event_member_image_updated" on table "member_image"';

1426 COMMENT ON TRIGGER "write_event_member_image_updated" ON "member_image" IS 'Creates entries in "event" table on member image update';

1427

1428

1429 CREATE FUNCTION "write_event_interest_trigger"()

1430 RETURNS TRIGGER

1431 LANGUAGE 'plpgsql' VOLATILE AS $$

1432 DECLARE

1433 "issue_row" "issue"%ROWTYPE;

1434 "area_row" "area"%ROWTYPE;

1435 BEGIN

1436 IF TG_OP = 'UPDATE' THEN

1437 IF OLD = NEW THEN

1438 RETURN NULL;

1439 END IF;

1440 END IF;

1441 IF TG_OP = 'DELETE' OR TG_OP = 'UPDATE' THEN

1442 SELECT * INTO "issue_row" FROM "issue"

1443 WHERE "id" = OLD."issue_id" FOR SHARE;

1444 SELECT * INTO "area_row" FROM "area"

1445 WHERE "id" = "issue_row"."area_id" FOR SHARE;

1446 IF "issue_row"."id" NOTNULL THEN

1447 INSERT INTO "event" (

1448 "event", "member_id",

1449 "unit_id", "area_id", "policy_id", "issue_id", "state",

1450 "boolean_value"

1451 ) VALUES (

1452 'interest', OLD."member_id",

1453 "area_row"."unit_id", "issue_row"."area_id",

1454 "issue_row"."policy_id",

1455 OLD."issue_id", "issue_row"."state",

1456 FALSE

1457 );

1458 END IF;

1459 END IF;

1460 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN

1461 SELECT * INTO "issue_row" FROM "issue"

1462 WHERE "id" = NEW."issue_id" FOR SHARE;

1463 SELECT * INTO "area_row" FROM "area"

1464 WHERE "id" = "issue_row"."area_id" FOR SHARE;

1465 INSERT INTO "event" (

1466 "event", "member_id",

1467 "unit_id", "area_id", "policy_id", "issue_id", "state",

1468 "boolean_value"

1469 ) VALUES (

1470 'interest', NEW."member_id",

1471 "area_row"."unit_id", "issue_row"."area_id",

1472 "issue_row"."policy_id",

1473 NEW."issue_id", "issue_row"."state",

1474 TRUE

1475 );

1476 END IF;

1477 RETURN NULL;

1478 END;

1479 $$;

1480

1481 CREATE TRIGGER "write_event_interest"

1482 AFTER INSERT OR UPDATE OR DELETE ON "interest" FOR EACH ROW EXECUTE PROCEDURE

1483 "write_event_interest_trigger"();

1484

1485 COMMENT ON FUNCTION "write_event_interest_trigger"() IS 'Implementation of trigger "write_event_interest_inserted" on table "interest"';

1486 COMMENT ON TRIGGER "write_event_interest" ON "interest" IS 'Create entry in "event" table on adding or removing interest';

1487

1488

1489 CREATE FUNCTION "write_event_initiator_trigger"()

1490 RETURNS TRIGGER

1491 LANGUAGE 'plpgsql' VOLATILE AS $$

1492 DECLARE

1493 "initiative_row" "initiative"%ROWTYPE;

1494 "issue_row" "issue"%ROWTYPE;

1495 "area_row" "area"%ROWTYPE;

1496 "accepted_v" BOOLEAN = FALSE;

1497 "rejected_v" BOOLEAN = FALSE;

1498 BEGIN

1499 IF TG_OP = 'UPDATE' THEN

1500 IF

1501 OLD."initiative_id" = NEW."initiative_id" AND

1502 OLD."member_id" = NEW."member_id"

1503 THEN

1504 IF

1505 coalesce(OLD."accepted", FALSE) = coalesce(NEW."accepted", FALSE)

1506 THEN

1507 RETURN NULL;

1508 END IF;

1509 IF coalesce(NEW."accepted", FALSE) = TRUE THEN

1510 "accepted_v" := TRUE;

1511 ELSE

1512 "rejected_v" := TRUE;

1513 END IF;

1514 END IF;

1515 END IF;

1516 IF (TG_OP = 'DELETE' OR TG_OP = 'UPDATE') AND NOT "accepted_v" THEN

1517 IF coalesce(OLD."accepted", FALSE) = TRUE THEN

1518 SELECT * INTO "initiative_row" FROM "initiative"

1519 WHERE "id" = OLD."initiative_id" FOR SHARE;

1520 IF "initiative_row"."id" NOTNULL THEN

1521 SELECT * INTO "issue_row" FROM "issue"

1522 WHERE "id" = "initiative_row"."issue_id" FOR SHARE;

1523 SELECT * INTO "area_row" FROM "area"

1524 WHERE "id" = "issue_row"."area_id" FOR SHARE;

1525 INSERT INTO "event" (

1526 "event", "member_id",

1527 "unit_id", "area_id", "policy_id", "issue_id", "state",

1528 "initiative_id", "boolean_value"

1529 ) VALUES (

1530 'initiator', OLD."member_id",

1531 "area_row"."unit_id", "issue_row"."area_id",

1532 "issue_row"."policy_id",

1533 "issue_row"."id", "issue_row"."state",

1534 OLD."initiative_id", FALSE

1535 );

1536 END IF;

1537 END IF;

1538 END IF;

1539 IF TG_OP = 'UPDATE' AND NOT "rejected_v" THEN

1540 IF coalesce(NEW."accepted", FALSE) = TRUE THEN

1541 SELECT * INTO "initiative_row" FROM "initiative"

1542 WHERE "id" = NEW."initiative_id" FOR SHARE;

1543 SELECT * INTO "issue_row" FROM "issue"

1544 WHERE "id" = "initiative_row"."issue_id" FOR SHARE;

1545 SELECT * INTO "area_row" FROM "area"

1546 WHERE "id" = "issue_row"."area_id" FOR SHARE;

1547 INSERT INTO "event" (

1548 "event", "member_id",

1549 "unit_id", "area_id", "policy_id", "issue_id", "state",

1550 "initiative_id", "boolean_value"

1551 ) VALUES (

1552 'initiator', NEW."member_id",

1553 "area_row"."unit_id", "issue_row"."area_id",

1554 "issue_row"."policy_id",

1555 "issue_row"."id", "issue_row"."state",

1556 NEW."initiative_id", TRUE

1557 );

1558 END IF;

1559 END IF;

1560 RETURN NULL;

1561 END;

1562 $$;

1563

1564 CREATE TRIGGER "write_event_initiator"

1565 AFTER UPDATE OR DELETE ON "initiator" FOR EACH ROW EXECUTE PROCEDURE

1566 "write_event_initiator_trigger"();

1567

1568 COMMENT ON FUNCTION "write_event_initiator_trigger"() IS 'Implementation of trigger "write_event_initiator" on table "initiator"';

1569 COMMENT ON TRIGGER "write_event_initiator" ON "initiator" IS 'Create entry in "event" table when accepting or removing initiatorship (NOTE: trigger does not fire on INSERT to avoid events on initiative creation)';

1570

1571

1572 CREATE FUNCTION "write_event_support_trigger"()

1573 RETURNS TRIGGER

1574 LANGUAGE 'plpgsql' VOLATILE AS $$

1575 DECLARE

1576 "issue_row" "issue"%ROWTYPE;

1577 "area_row" "area"%ROWTYPE;

1578 BEGIN

1579 IF TG_OP = 'UPDATE' THEN

1580 IF

1581 OLD."initiative_id" = NEW."initiative_id" AND

1582 OLD."member_id" = NEW."member_id"

1583 THEN

1584 IF OLD."draft_id" != NEW."draft_id" THEN

1585 SELECT * INTO "issue_row" FROM "issue"

1586 WHERE "id" = NEW."issue_id" FOR SHARE;

1587 SELECT * INTO "area_row" FROM "area"

1588 WHERE "id" = "issue_row"."area_id" FOR SHARE;

1589 INSERT INTO "event" (

1590 "event", "member_id",

1591 "unit_id", "area_id", "policy_id", "issue_id", "state",

1592 "initiative_id", "draft_id"

1593 ) VALUES (

1594 'support_updated', NEW."member_id",

1595 "area_row"."unit_id", "issue_row"."area_id",

1596 "issue_row"."policy_id",

1597 "issue_row"."id", "issue_row"."state",

1598 NEW."initiative_id", NEW."draft_id"

1599 );

1600 END IF;

1601 RETURN NULL;

1602 END IF;

1603 END IF;

1604 IF TG_OP = 'DELETE' OR TG_OP = 'UPDATE' THEN

1605 IF EXISTS (

1606 SELECT NULL FROM "initiative" WHERE "id" = OLD."initiative_id"

1607 FOR SHARE

1608 ) THEN

1609 SELECT * INTO "issue_row" FROM "issue"

1610 WHERE "id" = OLD."issue_id" FOR SHARE;

1611 SELECT * INTO "area_row" FROM "area"

1612 WHERE "id" = "issue_row"."area_id" FOR SHARE;

1613 INSERT INTO "event" (

1614 "event", "member_id",

1615 "unit_id", "area_id", "policy_id", "issue_id", "state",

1616 "initiative_id", "boolean_value"

1617 ) VALUES (

1618 'support', OLD."member_id",

1619 "area_row"."unit_id", "issue_row"."area_id",

1620 "issue_row"."policy_id",

1621 "issue_row"."id", "issue_row"."state",

1622 OLD."initiative_id", FALSE

1623 );

1624 END IF;

1625 END IF;

1626 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN

1627 SELECT * INTO "issue_row" FROM "issue"

1628 WHERE "id" = NEW."issue_id" FOR SHARE;

1629 SELECT * INTO "area_row" FROM "area"

1630 WHERE "id" = "issue_row"."area_id" FOR SHARE;

1631 INSERT INTO "event" (

1632 "event", "member_id",

1633 "unit_id", "area_id", "policy_id", "issue_id", "state",

1634 "initiative_id", "draft_id", "boolean_value"

1635 ) VALUES (

1636 'support', NEW."member_id",

1637 "area_row"."unit_id", "issue_row"."area_id",

1638 "issue_row"."policy_id",

1639 "issue_row"."id", "issue_row"."state",

1640 NEW."initiative_id", NEW."draft_id", TRUE

1641 );

1642 END IF;

1643 RETURN NULL;

1644 END;

1645 $$;

1646

1647 CREATE TRIGGER "write_event_support"

1648 AFTER INSERT OR UPDATE OR DELETE ON "supporter" FOR EACH ROW EXECUTE PROCEDURE

1649 "write_event_support_trigger"();

1650

1651 COMMENT ON FUNCTION "write_event_support_trigger"() IS 'Implementation of trigger "write_event_support" on table "supporter"';

1652 COMMENT ON TRIGGER "write_event_support" ON "supporter" IS 'Create entry in "event" table when adding, updating, or removing support';

1653

1654

1655 CREATE FUNCTION "write_event_suggestion_rated_trigger"()

1656 RETURNS TRIGGER

1657 LANGUAGE 'plpgsql' VOLATILE AS $$

1658 DECLARE

1659 "same_pkey_v" BOOLEAN = FALSE;

1660 "initiative_row" "initiative"%ROWTYPE;

1661 "issue_row" "issue"%ROWTYPE;

1662 "area_row" "area"%ROWTYPE;

1663 BEGIN

1664 IF TG_OP = 'UPDATE' THEN

1665 IF

1666 OLD."suggestion_id" = NEW."suggestion_id" AND

1667 OLD."member_id" = NEW."member_id"

1668 THEN

1669 IF

1670 OLD."degree" = NEW."degree" AND

1671 OLD."fulfilled" = NEW."fulfilled"

1672 THEN

1673 RETURN NULL;

1674 END IF;

1675 "same_pkey_v" := TRUE;

1676 END IF;

1677 END IF;

1678 IF (TG_OP = 'DELETE' OR TG_OP = 'UPDATE') AND NOT "same_pkey_v" THEN

1679 IF EXISTS (

1680 SELECT NULL FROM "suggestion" WHERE "id" = OLD."suggestion_id"

1681 FOR SHARE

1682 ) THEN

1683 SELECT * INTO "initiative_row" FROM "initiative"

1684 WHERE "id" = OLD."initiative_id" FOR SHARE;

1685 SELECT * INTO "issue_row" FROM "issue"

1686 WHERE "id" = "initiative_row"."issue_id" FOR SHARE;

1687 SELECT * INTO "area_row" FROM "area"

1688 WHERE "id" = "issue_row"."area_id" FOR SHARE;

1689 INSERT INTO "event" (

1690 "event", "member_id",

1691 "unit_id", "area_id", "policy_id", "issue_id", "state",

1692 "initiative_id", "suggestion_id",

1693 "boolean_value", "numeric_value"

1694 ) VALUES (

1695 'suggestion_rated', OLD."member_id",

1696 "area_row"."unit_id", "issue_row"."area_id",

1697 "issue_row"."policy_id",

1698 "initiative_row"."issue_id", "issue_row"."state",

1699 OLD."initiative_id", OLD."suggestion_id",

1700 NULL, 0

1701 );

1702 END IF;

1703 END IF;

1704 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN

1705 SELECT * INTO "initiative_row" FROM "initiative"

1706 WHERE "id" = NEW."initiative_id" FOR SHARE;

1707 SELECT * INTO "issue_row" FROM "issue"

1708 WHERE "id" = "initiative_row"."issue_id" FOR SHARE;

1709 SELECT * INTO "area_row" FROM "area"

1710 WHERE "id" = "issue_row"."area_id" FOR SHARE;

1711 INSERT INTO "event" (

1712 "event", "member_id",

1713 "unit_id", "area_id", "policy_id", "issue_id", "state",

1714 "initiative_id", "suggestion_id",

1715 "boolean_value", "numeric_value"

1716 ) VALUES (

1717 'suggestion_rated', NEW."member_id",

1718 "area_row"."unit_id", "issue_row"."area_id",

1719 "issue_row"."policy_id",

1720 "initiative_row"."issue_id", "issue_row"."state",

1721 NEW."initiative_id", NEW."suggestion_id",

1722 NEW."fulfilled", NEW."degree"

1723 );

1724 END IF;

1725 RETURN NULL;

1726 END;

1727 $$;

1728

1729 CREATE TRIGGER "write_event_suggestion_rated"

1730 AFTER INSERT OR UPDATE OR DELETE ON "opinion" FOR EACH ROW EXECUTE PROCEDURE

1731 "write_event_suggestion_rated_trigger"();

1732

1733 COMMENT ON FUNCTION "write_event_suggestion_rated_trigger"() IS 'Implementation of trigger "write_event_suggestion_rated" on table "opinion"';

1734 COMMENT ON TRIGGER "write_event_suggestion_rated" ON "opinion" IS 'Create entry in "event" table when adding, updating, or removing support';

1735

1736

1737 CREATE FUNCTION "write_event_delegation_trigger"()

1738 RETURNS TRIGGER

1739 LANGUAGE 'plpgsql' VOLATILE AS $$

1740 DECLARE

1741 "issue_row" "issue"%ROWTYPE;

1742 "area_row" "area"%ROWTYPE;

1743 BEGIN

1744 IF TG_OP = 'DELETE' THEN

1745 IF EXISTS (

1746 SELECT NULL FROM "member" WHERE "id" = OLD."truster_id"

1747 ) AND (CASE OLD."scope"

1748 WHEN 'unit'::"delegation_scope" THEN EXISTS (

1749 SELECT NULL FROM "unit" WHERE "id" = OLD."unit_id"

1750 )

1751 WHEN 'area'::"delegation_scope" THEN EXISTS (

1752 SELECT NULL FROM "area" WHERE "id" = OLD."area_id"

1753 )

1754 WHEN 'issue'::"delegation_scope" THEN EXISTS (

1755 SELECT NULL FROM "issue" WHERE "id" = OLD."issue_id"

1756 )

1757 END) THEN

1758 SELECT * INTO "issue_row" FROM "issue"

1759 WHERE "id" = OLD."issue_id" FOR SHARE;

1760 SELECT * INTO "area_row" FROM "area"

1761 WHERE "id" = COALESCE(OLD."area_id", "issue_row"."area_id")

1762 FOR SHARE;

1763 INSERT INTO "event" (

1764 "event", "member_id", "scope",

1765 "unit_id", "area_id", "issue_id", "state",

1766 "boolean_value"

1767 ) VALUES (

1768 'delegation', OLD."truster_id", OLD."scope",

1769 COALESCE(OLD."unit_id", "area_row"."unit_id"), "area_row"."id",

1770 OLD."issue_id", "issue_row"."state",

1771 FALSE

1772 );

1773 END IF;

1774 ELSE

1775 SELECT * INTO "issue_row" FROM "issue"

1776 WHERE "id" = NEW."issue_id" FOR SHARE;

1777 SELECT * INTO "area_row" FROM "area"

1778 WHERE "id" = COALESCE(NEW."area_id", "issue_row"."area_id")

1779 FOR SHARE;

1780 INSERT INTO "event" (

1781 "event", "member_id", "other_member_id", "scope",

1782 "unit_id", "area_id", "issue_id", "state",

1783 "boolean_value"

1784 ) VALUES (

1785 'delegation', NEW."truster_id", NEW."trustee_id", NEW."scope",

1786 COALESCE(NEW."unit_id", "area_row"."unit_id"), "area_row"."id",

1787 NEW."issue_id", "issue_row"."state",

1788 TRUE

1789 );

1790 END IF;

1791 RETURN NULL;

1792 END;

1793 $$;

1794

1795 CREATE TRIGGER "write_event_delegation"

1796 AFTER INSERT OR UPDATE OR DELETE ON "delegation" FOR EACH ROW EXECUTE PROCEDURE

1797 "write_event_delegation_trigger"();

1798

1799 COMMENT ON FUNCTION "write_event_delegation_trigger"() IS 'Implementation of trigger "write_event_delegation" on table "delegation"';

1800 COMMENT ON TRIGGER "write_event_delegation" ON "delegation" IS 'Create entry in "event" table when adding, updating, or removing a delegation';

1801

1802

1803 CREATE FUNCTION "write_event_contact_trigger"()

1804 RETURNS TRIGGER

1805 LANGUAGE 'plpgsql' VOLATILE AS $$

1806 BEGIN

1807 IF TG_OP = 'UPDATE' THEN

1808 IF

1809 OLD."member_id" = NEW."member_id" AND

1810 OLD."other_member_id" = NEW."other_member_id" AND

1811 OLD."public" = NEW."public"

1812 THEN

1813 RETURN NULL;

1814 END IF;

1815 END IF;

1816 IF TG_OP = 'DELETE' OR TG_OP = 'UPDATE' THEN

1817 IF OLD."public" THEN

1818 IF EXISTS (

1819 SELECT NULL FROM "member" WHERE "id" = OLD."member_id"

1820 FOR SHARE

1821 ) AND EXISTS (

1822 SELECT NULL FROM "member" WHERE "id" = OLD."other_member_id"

1823 FOR SHARE

1824 ) THEN

1825 INSERT INTO "event" (

1826 "event", "member_id", "other_member_id", "boolean_value"

1827 ) VALUES (

1828 'contact', OLD."member_id", OLD."other_member_id", FALSE

1829 );

1830 END IF;

1831 END IF;

1832 END IF;

1833 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN

1834 IF NEW."public" THEN

1835 INSERT INTO "event" (

1836 "event", "member_id", "other_member_id", "boolean_value"

1837 ) VALUES (

1838 'contact', NEW."member_id", NEW."other_member_id", TRUE

1839 );

1840 END IF;

1841 END IF;

1842 RETURN NULL;

1843 END;

1844 $$;

1845

1846 CREATE TRIGGER "write_event_contact"

1847 AFTER INSERT OR UPDATE OR DELETE ON "contact" FOR EACH ROW EXECUTE PROCEDURE

1848 "write_event_contact_trigger"();

1849

1850 COMMENT ON FUNCTION "write_event_contact_trigger"() IS 'Implementation of trigger "write_event_contact" on table "contact"';

1851 COMMENT ON TRIGGER "write_event_contact" ON "contact" IS 'Create entry in "event" table when adding or removing public contacts';

1852

1853

1854 CREATE FUNCTION "send_event_notify_trigger"()

1855 RETURNS TRIGGER

1856 LANGUAGE 'plpgsql' VOLATILE AS $$

1857 BEGIN

1858 EXECUTE 'NOTIFY "event", ''' || NEW."event" || '''';

1859 RETURN NULL;

1860 END;

1861 $$;

1862

1863 CREATE TRIGGER "send_notify"

1864 AFTER INSERT OR UPDATE ON "event" FOR EACH ROW EXECUTE PROCEDURE

1865 "send_event_notify_trigger"();

1866

1867

1868 CREATE FUNCTION "delete_extended_scope_tokens_trigger"()

1869 RETURNS TRIGGER

1870 LANGUAGE 'plpgsql' VOLATILE AS $$

1871 DECLARE

1872 "system_application_row" "system_application"%ROWTYPE;

1873 BEGIN

1874 IF OLD."system_application_id" NOTNULL THEN

1875 SELECT * FROM "system_application" INTO "system_application_row"

1876 WHERE "id" = OLD."system_application_id";

1877 DELETE FROM "token"

1878 WHERE "member_id" = OLD."member_id"

1879 AND "system_application_id" = OLD."system_application_id"

1880 AND NOT COALESCE(

1881 regexp_split_to_array("scope", E'\\s+') <@

1882 regexp_split_to_array(

1883 "system_application_row"."automatic_scope", E'\\s+'

1884 ),

1885 FALSE

1886 );

1887 END IF;

1888 RETURN OLD;

1889 END;

1890 $$;

1891

1892 CREATE TRIGGER "delete_extended_scope_tokens"

1893 BEFORE DELETE ON "member_application" FOR EACH ROW EXECUTE PROCEDURE

1894 "delete_extended_scope_tokens_trigger"();

1895

1896

1897 CREATE FUNCTION "detach_token_from_session_trigger"()

1898 RETURNS TRIGGER

1899 LANGUAGE 'plpgsql' VOLATILE AS $$

1900 BEGIN

1901 UPDATE "token" SET "session_id" = NULL

1902 WHERE "session_id" = OLD."id";

1903 RETURN OLD;

1904 END;

1905 $$;

1906

1907 CREATE TRIGGER "detach_token_from_session"

1908 BEFORE DELETE ON "session" FOR EACH ROW EXECUTE PROCEDURE

1909 "detach_token_from_session_trigger"();

1910

1911

1912 CREATE FUNCTION "delete_non_detached_scope_with_session_trigger"()

1913 RETURNS TRIGGER

1914 LANGUAGE 'plpgsql' VOLATILE AS $$

1915 BEGIN

1916 IF NEW."session_id" ISNULL THEN

1917 SELECT coalesce(string_agg("element", ' '), '') INTO NEW."scope"

1918 FROM unnest(regexp_split_to_array(NEW."scope", E'\\s+')) AS "element"

1919 WHERE "element" LIKE '%_detached';

1920 END IF;

1921 RETURN NEW;

1922 END;

1923 $$;

1924

1925 CREATE TRIGGER "delete_non_detached_scope_with_session"

1926 BEFORE INSERT OR UPDATE ON "token" FOR EACH ROW EXECUTE PROCEDURE

1927 "delete_non_detached_scope_with_session_trigger"();

1928

1929

1930 CREATE FUNCTION "delete_token_with_empty_scope_trigger"()

1931 RETURNS TRIGGER

1932 LANGUAGE 'plpgsql' VOLATILE AS $$

1933 BEGIN

1934 IF NEW."scope" = '' THEN

1935 DELETE FROM "token" WHERE "id" = NEW."id";

1936 END IF;

1937 RETURN NULL;

1938 END;

1939 $$;

1940

1941 CREATE TRIGGER "delete_token_with_empty_scope"

1942 AFTER INSERT OR UPDATE ON "token" FOR EACH ROW EXECUTE PROCEDURE

1943 "delete_token_with_empty_scope_trigger"();

1944

1945

1946 CREATE FUNCTION "delete_snapshot_on_partial_delete_trigger"()

1947 RETURNS TRIGGER

1948 LANGUAGE 'plpgsql' VOLATILE AS $$

1949 BEGIN

1950 IF TG_OP = 'UPDATE' THEN

1951 IF

1952 OLD."snapshot_id" = NEW."snapshot_id" AND

1953 OLD."issue_id" = NEW."issue_id"

1954 THEN

1955 RETURN NULL;

1956 END IF;

1957 END IF;

1958 DELETE FROM "snapshot" WHERE "id" = OLD."snapshot_id";

1959 RETURN NULL;

1960 END;

1961 $$;

1962

1963 CREATE TRIGGER "delete_snapshot_on_partial_delete"

1964 AFTER UPDATE OR DELETE ON "snapshot_issue"

1965 FOR EACH ROW EXECUTE PROCEDURE

1966 "delete_snapshot_on_partial_delete_trigger"();

1967

1968 COMMENT ON FUNCTION "delete_snapshot_on_partial_delete_trigger"() IS 'Implementation of trigger "delete_snapshot_on_partial_delete" on table "snapshot_issue"';

1969 COMMENT ON TRIGGER "delete_snapshot_on_partial_delete" ON "snapshot_issue" IS 'Deletes whole snapshot if one issue is deleted from the snapshot';

1970

1971

1972 CREATE FUNCTION "copy_current_draft_data"

1973 ("initiative_id_p" "initiative"."id"%TYPE )

1974 RETURNS VOID

1975 LANGUAGE 'plpgsql' VOLATILE AS $$

1976 BEGIN

1977 PERFORM NULL FROM "initiative" WHERE "id" = "initiative_id_p"

1978 FOR UPDATE;

1979 UPDATE "initiative" SET

1980 "location" = "draft"."location",

1981 "draft_text_search_data" = "draft"."text_search_data"

1982 FROM "current_draft" AS "draft"

1983 WHERE "initiative"."id" = "initiative_id_p"

1984 AND "draft"."initiative_id" = "initiative_id_p";

1985 END;

1986 $$;

1987

1988 COMMENT ON FUNCTION "copy_current_draft_data"

1989 ( "initiative"."id"%TYPE )

1990 IS 'Helper function for function "copy_current_draft_data_trigger"';

1991

1992

1993 CREATE FUNCTION "copy_current_draft_data_trigger"()

1994 RETURNS TRIGGER

1995 LANGUAGE 'plpgsql' VOLATILE AS $$

1996 BEGIN

1997 IF TG_OP='DELETE' THEN

1998 PERFORM "copy_current_draft_data"(OLD."initiative_id");

1999 ELSE

2000 IF TG_OP='UPDATE' THEN

2001 IF COALESCE(OLD."inititiave_id" != NEW."initiative_id", TRUE) THEN

2002 PERFORM "copy_current_draft_data"(OLD."initiative_id");

2003 END IF;

2004 END IF;

2005 PERFORM "copy_current_draft_data"(NEW."initiative_id");

2006 END IF;

2007 RETURN NULL;

2008 END;

2009 $$;

2010

2011 CREATE TRIGGER "copy_current_draft_data"

2012 AFTER INSERT OR UPDATE OR DELETE ON "draft"

2013 FOR EACH ROW EXECUTE PROCEDURE

2014 "copy_current_draft_data_trigger"();

2015

2016 COMMENT ON FUNCTION "copy_current_draft_data_trigger"() IS 'Implementation of trigger "copy_current_draft_data" on table "draft"';

2017 COMMENT ON TRIGGER "copy_current_draft_data" ON "draft" IS 'Copy certain fields from most recent "draft" to "initiative"';

2018

2019

2020 CREATE VIEW "area_quorum" AS

2021 SELECT

2022 "area"."id" AS "area_id",

2023 ceil(

2024 "area"."quorum_standard"::FLOAT8 * "quorum_factor"::FLOAT8 ^ (

2025 coalesce(

2026 ( SELECT sum(

2027 ( extract(epoch from "area"."quorum_time")::FLOAT8 /

2028 extract(epoch from

2029 ("issue"."accepted"-"issue"."created") +

2030 "issue"."discussion_time" +

2031 "issue"."verification_time" +

2032 "issue"."voting_time"

2033 )::FLOAT8

2034 ) ^ "area"."quorum_exponent"::FLOAT8

2035 )

2036 FROM "issue" JOIN "policy"

2037 ON "issue"."policy_id" = "policy"."id"

2038 WHERE "issue"."area_id" = "area"."id"

2039 AND "issue"."accepted" NOTNULL

2040 AND "issue"."closed" ISNULL

2041 AND "policy"."polling" = FALSE

2042 )::FLOAT8, 0::FLOAT8

2043 ) / "area"."quorum_issues"::FLOAT8 - 1::FLOAT8

2044 ) * CASE WHEN "area"."quorum_den" ISNULL THEN 1 ELSE (

2045 SELECT "snapshot"."population"

2046 FROM "snapshot"

2047 WHERE "snapshot"."area_id" = "area"."id"

2048 AND "snapshot"."issue_id" ISNULL

2049 ORDER BY "snapshot"."id" DESC

2050 LIMIT 1

2051 ) END / coalesce("area"."quorum_den", 1)

2052

2053 )::INT4 AS "issue_quorum"

2054 FROM "area";

2055

2056 COMMENT ON VIEW "area_quorum" IS 'Area-based quorum considering number of open (accepted) issues';

2057

2058

2059 CREATE VIEW "area_with_unaccepted_issues" AS

2060 SELECT DISTINCT ON ("area"."id") "area".*

2061 FROM "area" JOIN "issue" ON "area"."id" = "issue"."area_id"

2062 WHERE "issue"."state" = 'admission';

2063

2064 COMMENT ON VIEW "area_with_unaccepted_issues" IS 'All areas with unaccepted open issues (needed for issue admission system)';

2065

2066

2067 DROP VIEW "area_member_count";

2068

2069

2070 DROP TABLE "membership";

2071

2072

2073 DROP FUNCTION "membership_weight"

2074 ( "area_id_p" "area"."id"%TYPE,

2075 "member_id_p" "member"."id"%TYPE );

2076

2077

2078 DROP FUNCTION "membership_weight_with_skipping"

2079 ( "area_id_p" "area"."id"%TYPE,

2080 "member_id_p" "member"."id"%TYPE,

2081 "skip_member_ids_p" INT4[] ); -- TODO: ordering/cascade

2082

2083

2084 CREATE OR REPLACE VIEW "issue_delegation" AS

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

2086 "issue"."id" AS "issue_id",

2087 "delegation"."id",

2088 "delegation"."truster_id",

2089 "delegation"."trustee_id",

2090 "delegation"."scope"

2091 FROM "issue"

2092 JOIN "area"

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

2094 JOIN "delegation"

2095 ON "delegation"."unit_id" = "area"."unit_id"

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

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

2098 JOIN "member"

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

2100 JOIN "privilege"

2101 ON "area"."unit_id" = "privilege"."unit_id"

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

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

2104 ORDER BY

2105 "issue"."id",

2106 "delegation"."truster_id",

2107 "delegation"."scope" DESC;

2108

2109

2110 CREATE VIEW "unit_member" AS

2111 SELECT

2112 "unit"."id" AS "unit_id",

2113 "member"."id" AS "member_id"

2114 FROM "privilege"

2115 JOIN "unit" ON "unit_id" = "privilege"."unit_id"

2116 JOIN "member" ON "member"."id" = "privilege"."member_id"

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

2118

2119 COMMENT ON VIEW "unit_member" IS 'Active members with voting right in a unit';

2120

2121

2122 CREATE OR REPLACE VIEW "unit_member_count" AS

2123 SELECT

2124 "unit"."id" AS "unit_id",

2125 count("unit_member"."member_id") AS "member_count"

2126 FROM "unit" LEFT JOIN "unit_member"

2127 ON "unit"."id" = "unit_member"."unit_id"

2128 GROUP BY "unit"."id";

2129

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

2131

2132

2133 CREATE OR REPLACE VIEW "opening_draft" AS

2134 SELECT DISTINCT ON ("initiative_id") * FROM "draft"

2135 ORDER BY "initiative_id", "id";

2136

2137

2138 CREATE OR REPLACE VIEW "current_draft" AS

2139 SELECT DISTINCT ON ("initiative_id") * FROM "draft"

2140 ORDER BY "initiative_id", "id" DESC;

2141

2142

2143 CREATE OR REPLACE VIEW "issue_supporter_in_admission_state" AS

2144 SELECT

2145 "area"."unit_id",

2146 "issue"."area_id",

2147 "issue"."id" AS "issue_id",

2148 "supporter"."member_id",

2149 "direct_interest_snapshot"."weight"

2150 FROM "issue"

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

2152 JOIN "supporter" ON "supporter"."issue_id" = "issue"."id"

2153 JOIN "direct_interest_snapshot"

2154 ON "direct_interest_snapshot"."snapshot_id" = "issue"."latest_snapshot_id"

2155 AND "direct_interest_snapshot"."issue_id" = "issue"."id"

2156 AND "direct_interest_snapshot"."member_id" = "supporter"."member_id"

2157 WHERE "issue"."state" = 'admission'::"issue_state";

2158

2159

2160 CREATE OR REPLACE VIEW "individual_suggestion_ranking" AS

2161 SELECT

2162 "opinion"."initiative_id",

2163 "opinion"."member_id",

2164 "direct_interest_snapshot"."weight",

2165 CASE WHEN

2166 ("opinion"."degree" = 2 AND "opinion"."fulfilled" = FALSE) OR

2167 ("opinion"."degree" = -2 AND "opinion"."fulfilled" = TRUE)

2168 THEN 1 ELSE

2169 CASE WHEN

2170 ("opinion"."degree" = 1 AND "opinion"."fulfilled" = FALSE) OR

2171 ("opinion"."degree" = -1 AND "opinion"."fulfilled" = TRUE)

2172 THEN 2 ELSE

2173 CASE WHEN

2174 ("opinion"."degree" = 2 AND "opinion"."fulfilled" = TRUE) OR

2175 ("opinion"."degree" = -2 AND "opinion"."fulfilled" = FALSE)

2176 THEN 3 ELSE 4 END

2177 END

2178 END AS "preference",

2179 "opinion"."suggestion_id"

2180 FROM "opinion"

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

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

2183 JOIN "direct_interest_snapshot"

2184 ON "direct_interest_snapshot"."snapshot_id" = "issue"."latest_snapshot_id"

2185 AND "direct_interest_snapshot"."issue_id" = "issue"."id"

2186 AND "direct_interest_snapshot"."member_id" = "opinion"."member_id";

2187

2188

2189 CREATE VIEW "expired_session" AS

2190 SELECT * FROM "session" WHERE now() > "expiry";

2191

2192 CREATE RULE "delete" AS ON DELETE TO "expired_session" DO INSTEAD

2193 DELETE FROM "session" WHERE "id" = OLD."id";

2194

2195 COMMENT ON VIEW "expired_session" IS 'View containing all expired sessions where DELETE is possible';

2196 COMMENT ON RULE "delete" ON "expired_session" IS 'Rule allowing DELETE on rows in "expired_session" view, i.e. DELETE FROM "expired_session"';

2197

2198

2199 CREATE VIEW "expired_token" AS

2200 SELECT * FROM "token" WHERE now() > "expiry" AND NOT (

2201 "token_type" = 'authorization' AND "used" AND EXISTS (

2202 SELECT NULL FROM "token" AS "other"

2203 WHERE "other"."authorization_token_id" = "id" ) );

2204

2205 CREATE RULE "delete" AS ON DELETE TO "expired_token" DO INSTEAD

2206 DELETE FROM "token" WHERE "id" = OLD."id";

2207

2208 COMMENT ON VIEW "expired_token" IS 'View containing all expired tokens where DELETE is possible; Note that used authorization codes must not be deleted if still referred to by other tokens';

2209

2210

2211 CREATE VIEW "unused_snapshot" AS

2212 SELECT "snapshot".* FROM "snapshot"

2213 LEFT JOIN "issue"

2214 ON "snapshot"."id" = "issue"."latest_snapshot_id"

2215 OR "snapshot"."id" = "issue"."admission_snapshot_id"

2216 OR "snapshot"."id" = "issue"."half_freeze_snapshot_id"

2217 OR "snapshot"."id" = "issue"."full_freeze_snapshot_id"

2218 WHERE "issue"."id" ISNULL;

2219

2220 CREATE RULE "delete" AS ON DELETE TO "unused_snapshot" DO INSTEAD

2221 DELETE FROM "snapshot" WHERE "id" = OLD."id";

2222

2223 COMMENT ON VIEW "unused_snapshot" IS 'Snapshots that are not referenced by any issue (either as latest snapshot or as snapshot at phase/state change)';

2224

2225

2226 CREATE VIEW "expired_snapshot" AS

2227 SELECT "unused_snapshot".* FROM "unused_snapshot" CROSS JOIN "system_setting"

2228 WHERE "unused_snapshot"."calculated" <

2229 now() - "system_setting"."snapshot_retention";

2230

2231 CREATE RULE "delete" AS ON DELETE TO "expired_snapshot" DO INSTEAD

2232 DELETE FROM "snapshot" WHERE "id" = OLD."id";

2233

2234 COMMENT ON VIEW "expired_snapshot" IS 'Contains "unused_snapshot"s that are older than "system_setting"."snapshot_retention" (for deletion)';

2235

2236

2237 COMMENT ON COLUMN "delegation_chain_row"."participation" IS 'In case of delegation chains for issues: interest; for area and global delegation chains: always null';

2238

2239

2240 CREATE OR REPLACE FUNCTION "delegation_chain"

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

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

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

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

2245 "simulate_trustee_id_p" "member"."id"%TYPE DEFAULT NULL,

2246 "simulate_default_p" BOOLEAN DEFAULT FALSE )

2247 RETURNS SETOF "delegation_chain_row"

2248 LANGUAGE 'plpgsql' STABLE AS $$

2249 DECLARE

2250 "scope_v" "delegation_scope";

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

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

2253 "issue_row" "issue"%ROWTYPE;

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

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

2256 "output_row" "delegation_chain_row";

2257 "output_rows" "delegation_chain_row"[];

2258 "simulate_v" BOOLEAN;

2259 "simulate_here_v" BOOLEAN;

2260 "delegation_row" "delegation"%ROWTYPE;

2261 "row_count" INT4;

2262 "i" INT4;

2263 "loop_v" BOOLEAN;

2264 BEGIN

2265 IF "simulate_trustee_id_p" NOTNULL AND "simulate_default_p" THEN

2266 RAISE EXCEPTION 'Both "simulate_trustee_id_p" is set, and "simulate_default_p" is true';

2267 END IF;

2268 IF "simulate_trustee_id_p" NOTNULL OR "simulate_default_p" THEN

2269 "simulate_v" := TRUE;

2270 ELSE

2271 "simulate_v" := FALSE;

2272 END IF;

2273 IF

2274 "unit_id_p" NOTNULL AND

2275 "area_id_p" ISNULL AND

2276 "issue_id_p" ISNULL

2277 THEN

2278 "scope_v" := 'unit';

2279 "unit_id_v" := "unit_id_p";

2280 ELSIF

2281 "unit_id_p" ISNULL AND

2282 "area_id_p" NOTNULL AND

2283 "issue_id_p" ISNULL

2284 THEN

2285 "scope_v" := 'area';

2286 "area_id_v" := "area_id_p";

2287 SELECT "unit_id" INTO "unit_id_v"

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

2289 ELSIF

2290 "unit_id_p" ISNULL AND

2291 "area_id_p" ISNULL AND

2292 "issue_id_p" NOTNULL

2293 THEN

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

2295 IF "issue_row"."id" ISNULL THEN

2296 RETURN;

2297 END IF;

2298 IF "issue_row"."closed" NOTNULL THEN

2299 IF "simulate_v" THEN

2300 RAISE EXCEPTION 'Tried to simulate delegation chain for closed issue.';

2301 END IF;

2302 FOR "output_row" IN

2303 SELECT * FROM

2304 "delegation_chain_for_closed_issue"("member_id_p", "issue_id_p")

2305 LOOP

2306 RETURN NEXT "output_row";

2307 END LOOP;

2308 RETURN;

2309 END IF;

2310 "scope_v" := 'issue';

2311 SELECT "area_id" INTO "area_id_v"

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

2313 SELECT "unit_id" INTO "unit_id_v"

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

2315 ELSE

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

2317 END IF;

2318 "visited_member_ids" := '{}';

2319 "loop_member_id_v" := NULL;

2320 "output_rows" := '{}';

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

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

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

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

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

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

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

2328 LOOP

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

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

2331 ELSE

2332 "visited_member_ids" :=

2333 "visited_member_ids" || "output_row"."member_id";

2334 END IF;

2335 IF "output_row"."participation" ISNULL THEN

2336 "output_row"."overridden" := NULL;

2337 ELSIF "output_row"."participation" THEN

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

2339 END IF;

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

2341 "output_row"."member_valid" := EXISTS (

2342 SELECT NULL FROM "member" JOIN "privilege"

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

2344 AND "privilege"."unit_id" = "unit_id_v"

2345 WHERE "id" = "output_row"."member_id"

2346 AND "member"."active" AND "privilege"."voting_right"

2347 );

2348 "simulate_here_v" := (

2349 "simulate_v" AND

2350 "output_row"."member_id" = "member_id_p"

2351 );

2352 "delegation_row" := ROW(NULL);

2353 IF "output_row"."member_valid" OR "simulate_here_v" THEN

2354 IF "scope_v" = 'unit' THEN

2355 IF NOT "simulate_here_v" THEN

2356 SELECT * INTO "delegation_row" FROM "delegation"

2357 WHERE "truster_id" = "output_row"."member_id"

2358 AND "unit_id" = "unit_id_v";

2359 END IF;

2360 ELSIF "scope_v" = 'area' THEN

2361 IF "simulate_here_v" THEN

2362 IF "simulate_trustee_id_p" ISNULL THEN

2363 SELECT * INTO "delegation_row" FROM "delegation"

2364 WHERE "truster_id" = "output_row"."member_id"

2365 AND "unit_id" = "unit_id_v";

2366 END IF;

2367 ELSE

2368 SELECT * INTO "delegation_row" FROM "delegation"

2369 WHERE "truster_id" = "output_row"."member_id"

2370 AND (

2371 "unit_id" = "unit_id_v" OR

2372 "area_id" = "area_id_v"

2373 )

2374 ORDER BY "scope" DESC;

2375 END IF;

2376 ELSIF "scope_v" = 'issue' THEN

2377 IF "issue_row"."fully_frozen" ISNULL THEN

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

2379 SELECT NULL FROM "interest"

2380 WHERE "issue_id" = "issue_id_p"

2381 AND "member_id" = "output_row"."member_id"

2382 );

2383 ELSE

2384 IF "output_row"."member_id" = "member_id_p" THEN

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

2386 SELECT NULL FROM "direct_voter"

2387 WHERE "issue_id" = "issue_id_p"

2388 AND "member_id" = "output_row"."member_id"

2389 );

2390 ELSE

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

2392 END IF;

2393 END IF;

2394 IF "simulate_here_v" THEN

2395 IF "simulate_trustee_id_p" ISNULL THEN

2396 SELECT * INTO "delegation_row" FROM "delegation"

2397 WHERE "truster_id" = "output_row"."member_id"

2398 AND (

2399 "unit_id" = "unit_id_v" OR

2400 "area_id" = "area_id_v"

2401 )

2402 ORDER BY "scope" DESC;

2403 END IF;

2404 ELSE

2405 SELECT * INTO "delegation_row" FROM "delegation"

2406 WHERE "truster_id" = "output_row"."member_id"

2407 AND (

2408 "unit_id" = "unit_id_v" OR

2409 "area_id" = "area_id_v" OR

2410 "issue_id" = "issue_id_p"

2411 )

2412 ORDER BY "scope" DESC;

2413 END IF;

2414 END IF;

2415 ELSE

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

2417 END IF;

2418 IF "simulate_here_v" AND "simulate_trustee_id_p" NOTNULL THEN

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

2420 "output_rows" := "output_rows" || "output_row";

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

2422 ELSIF "delegation_row"."trustee_id" NOTNULL THEN

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

2424 "output_rows" := "output_rows" || "output_row";

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

2426 ELSIF "delegation_row"."scope" NOTNULL THEN

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

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

2429 "output_rows" := "output_rows" || "output_row";

2430 EXIT;

2431 ELSE

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

2433 "output_rows" := "output_rows" || "output_row";

2434 EXIT;

2435 END IF;

2436 EXIT WHEN "loop_member_id_v" NOTNULL;

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

2438 END LOOP;

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

2440 "i" := 1;

2441 "loop_v" := FALSE;

2442 LOOP

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

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

2445 IF "loop_v" THEN

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

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

2448 ELSIF "i" = "row_count" THEN

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

2450 ELSE

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

2452 END IF;

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

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

2455 "loop_v" := TRUE;

2456 END IF;

2457 IF "scope_v" = 'unit' THEN

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

2459 END IF;

2460 RETURN NEXT "output_row";

2461 "i" := "i" + 1;

2462 END LOOP;

2463 RETURN;

2464 END;

2465 $$;

2466

2467

2468 CREATE OR REPLACE FUNCTION "get_initiatives_for_notification"

2469 ( "recipient_id_p" "member"."id"%TYPE )

2470 RETURNS SETOF "initiative_for_notification"

2471 LANGUAGE 'plpgsql' VOLATILE AS $$

2472 DECLARE

2473 "result_row" "initiative_for_notification"%ROWTYPE;

2474 "last_draft_id_v" "draft"."id"%TYPE;

2475 "last_suggestion_id_v" "suggestion"."id"%TYPE;

2476 BEGIN

2477 PERFORM "require_transaction_isolation"();

2478 PERFORM NULL FROM "member" WHERE "id" = "recipient_id_p" FOR UPDATE;

2479 FOR "result_row" IN

2480 SELECT * FROM "initiative_for_notification"

2481 WHERE "recipient_id" = "recipient_id_p"

2482 LOOP

2483 SELECT "id" INTO "last_draft_id_v" FROM "draft"

2484 WHERE "draft"."initiative_id" = "result_row"."initiative_id"

2485 ORDER BY "id" DESC LIMIT 1;

2486 SELECT "id" INTO "last_suggestion_id_v" FROM "suggestion"

2487 WHERE "suggestion"."initiative_id" = "result_row"."initiative_id"

2488 ORDER BY "id" DESC LIMIT 1;

2489 INSERT INTO "notification_initiative_sent"

2490 ("member_id", "initiative_id", "last_draft_id", "last_suggestion_id")

2491 VALUES (

2492 "recipient_id_p",

2493 "result_row"."initiative_id",

2494 "last_draft_id_v",

2495 "last_suggestion_id_v" )

2496 ON CONFLICT ("member_id", "initiative_id") DO UPDATE SET

2497 "last_draft_id" = "last_draft_id_v",

2498 "last_suggestion_id" = "last_suggestion_id_v";

2499 RETURN NEXT "result_row";

2500 END LOOP;

2501 DELETE FROM "notification_initiative_sent"

2502 USING "initiative", "issue"

2503 WHERE "notification_initiative_sent"."member_id" = "recipient_id_p"

2504 AND "initiative"."id" = "notification_initiative_sent"."initiative_id"

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

2506 AND ( "issue"."closed" NOTNULL OR "issue"."fully_frozen" NOTNULL );

2507 UPDATE "member" SET

2508 "notification_counter" = "notification_counter" + 1,

2509 "notification_sent" = now()

2510 WHERE "id" = "recipient_id_p";

2511 RETURN;

2512 END;

2513 $$;

2514

2515

2516 CREATE OR REPLACE FUNCTION "calculate_member_counts"()

2517 RETURNS VOID

2518 LANGUAGE 'plpgsql' VOLATILE AS $$

2519 BEGIN

2520 PERFORM "require_transaction_isolation"();

2521 DELETE FROM "member_count";

2522 INSERT INTO "member_count" ("total_count")

2523 SELECT "total_count" FROM "member_count_view";

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

2525 FROM "unit_member_count" AS "view"

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

2527 RETURN;

2528 END;

2529 $$;

2530

2531 COMMENT ON FUNCTION "calculate_member_counts"() IS 'Updates "member_count" table and "member_count" column of table "area" by materializing data from views "member_count_view" and "unit_member_count"';

2532

2533

2534 CREATE FUNCTION "calculate_area_quorum"()

2535 RETURNS VOID

2536 LANGUAGE 'plpgsql' VOLATILE AS $$

2537 BEGIN

2538 PERFORM "dont_require_transaction_isolation"();

2539 UPDATE "area" SET "issue_quorum" = "view"."issue_quorum"

2540 FROM "area_quorum" AS "view"

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

2542 RETURN;

2543 END;

2544 $$;

2545

2546 COMMENT ON FUNCTION "calculate_area_quorum"() IS 'Calculate column "issue_quorum" in table "area" from view "area_quorum"';

2547

2548

2549 DROP VIEW "remaining_harmonic_initiative_weight_summands";

2550 DROP VIEW "remaining_harmonic_supporter_weight";

2551

2552

2553 CREATE VIEW "remaining_harmonic_supporter_weight" AS

2554 SELECT

2555 "direct_interest_snapshot"."snapshot_id",

2556 "direct_interest_snapshot"."issue_id",

2557 "direct_interest_snapshot"."member_id",

2558 "direct_interest_snapshot"."weight" AS "weight_num",

2559 count("initiative"."id") AS "weight_den"

2560 FROM "issue"

2561 JOIN "direct_interest_snapshot"

2562 ON "issue"."latest_snapshot_id" = "direct_interest_snapshot"."snapshot_id"

2563 AND "issue"."id" = "direct_interest_snapshot"."issue_id"

2564 JOIN "initiative"

2565 ON "issue"."id" = "initiative"."issue_id"

2566 AND "initiative"."harmonic_weight" ISNULL

2567 JOIN "direct_supporter_snapshot"

2568 ON "issue"."latest_snapshot_id" = "direct_supporter_snapshot"."snapshot_id"

2569 AND "initiative"."id" = "direct_supporter_snapshot"."initiative_id"

2570 AND "direct_interest_snapshot"."member_id" = "direct_supporter_snapshot"."member_id"

2571 AND (

2572 "direct_supporter_snapshot"."satisfied" = TRUE OR

2573 coalesce("initiative"."admitted", FALSE) = FALSE

2574 )

2575 GROUP BY

2576 "direct_interest_snapshot"."snapshot_id",

2577 "direct_interest_snapshot"."issue_id",

2578 "direct_interest_snapshot"."member_id",

2579 "direct_interest_snapshot"."weight";

2580

2581

2582 CREATE VIEW "remaining_harmonic_initiative_weight_summands" AS

2583 SELECT

2584 "initiative"."issue_id",

2585 "initiative"."id" AS "initiative_id",

2586 "initiative"."admitted",

2587 sum("remaining_harmonic_supporter_weight"."weight_num") AS "weight_num",

2588 "remaining_harmonic_supporter_weight"."weight_den"

2589 FROM "remaining_harmonic_supporter_weight"

2590 JOIN "initiative"

2591 ON "remaining_harmonic_supporter_weight"."issue_id" = "initiative"."issue_id"

2592 AND "initiative"."harmonic_weight" ISNULL

2593 JOIN "direct_supporter_snapshot"

2594 ON "remaining_harmonic_supporter_weight"."snapshot_id" = "direct_supporter_snapshot"."snapshot_id"

2595 AND "initiative"."id" = "direct_supporter_snapshot"."initiative_id"

2596 AND "remaining_harmonic_supporter_weight"."member_id" = "direct_supporter_snapshot"."member_id"

2597 AND (

2598 "direct_supporter_snapshot"."satisfied" = TRUE OR

2599 coalesce("initiative"."admitted", FALSE) = FALSE

2600 )

2601 GROUP BY

2602 "initiative"."issue_id",

2603 "initiative"."id",

2604 "initiative"."admitted",

2605 "remaining_harmonic_supporter_weight"."weight_den";

2606

2607

2608 DROP FUNCTION "create_population_snapshot"

2609 ( "issue_id_p" "issue"."id"%TYPE );

2610

2611

2612 DROP FUNCTION "weight_of_added_delegations_for_population_snapshot"

2613 ( "issue_id_p" "issue"."id"%TYPE,

2614 "member_id_p" "member"."id"%TYPE,

2615 "delegate_member_ids_p" "delegating_population_snapshot"."delegate_member_ids"%TYPE );

2616

2617

2618 DROP FUNCTION "weight_of_added_delegations_for_interest_snapshot"

2619 ( "issue_id_p" "issue"."id"%TYPE,

2620 "member_id_p" "member"."id"%TYPE,

2621 "delegate_member_ids_p" "delegating_interest_snapshot"."delegate_member_ids"%TYPE );

2622

2623

2624 CREATE FUNCTION "weight_of_added_delegations_for_snapshot"

2625 ( "snapshot_id_p" "snapshot"."id"%TYPE,

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

2627 "member_id_p" "member"."id"%TYPE,

2628 "delegate_member_ids_p" "delegating_interest_snapshot"."delegate_member_ids"%TYPE )

2629 RETURNS "direct_interest_snapshot"."weight"%TYPE

2630 LANGUAGE 'plpgsql' VOLATILE AS $$

2631 DECLARE

2632 "issue_delegation_row" "issue_delegation"%ROWTYPE;

2633 "delegate_member_ids_v" "delegating_interest_snapshot"."delegate_member_ids"%TYPE;

2634 "weight_v" INT4;

2635 "sub_weight_v" INT4;

2636 BEGIN

2637 PERFORM "require_transaction_isolation"();

2638 "weight_v" := 0;

2639 FOR "issue_delegation_row" IN

2640 SELECT * FROM "issue_delegation"

2641 WHERE "trustee_id" = "member_id_p"

2642 AND "issue_id" = "issue_id_p"

2643 LOOP

2644 IF NOT EXISTS (

2645 SELECT NULL FROM "direct_interest_snapshot"

2646 WHERE "snapshot_id" = "snapshot_id_p"

2647 AND "issue_id" = "issue_id_p"

2648 AND "member_id" = "issue_delegation_row"."truster_id"

2649 ) AND NOT EXISTS (

2650 SELECT NULL FROM "delegating_interest_snapshot"

2651 WHERE "snapshot_id" = "snapshot_id_p"

2652 AND "issue_id" = "issue_id_p"

2653 AND "member_id" = "issue_delegation_row"."truster_id"

2654 ) THEN

2655 "delegate_member_ids_v" :=

2656 "member_id_p" || "delegate_member_ids_p";

2657 INSERT INTO "delegating_interest_snapshot" (

2658 "snapshot_id",

2659 "issue_id",

2660 "member_id",

2661 "scope",

2662 "delegate_member_ids"

2663 ) VALUES (

2664 "snapshot_id_p",

2665 "issue_id_p",

2666 "issue_delegation_row"."truster_id",

2667 "issue_delegation_row"."scope",

2668 "delegate_member_ids_v"

2669 );

2670 "sub_weight_v" := 1 +

2671 "weight_of_added_delegations_for_snapshot"(

2672 "snapshot_id_p",

2673 "issue_id_p",

2674 "issue_delegation_row"."truster_id",

2675 "delegate_member_ids_v"

2676 );

2677 UPDATE "delegating_interest_snapshot"

2678 SET "weight" = "sub_weight_v"

2679 WHERE "snapshot_id" = "snapshot_id_p"

2680 AND "issue_id" = "issue_id_p"

2681 AND "member_id" = "issue_delegation_row"."truster_id";

2682 "weight_v" := "weight_v" + "sub_weight_v";

2683 END IF;

2684 END LOOP;

2685 RETURN "weight_v";

2686 END;

2687 $$;

2688

2689 COMMENT ON FUNCTION "weight_of_added_delegations_for_snapshot"

2690 ( "snapshot"."id"%TYPE,

2691 "issue"."id"%TYPE,

2692 "member"."id"%TYPE,

2693 "delegating_interest_snapshot"."delegate_member_ids"%TYPE )

2694 IS 'Helper function for "fill_snapshot" function';

2695

2696

2697 DROP FUNCTION "create_interest_snapshot"

2698 ( "issue_id_p" "issue"."id"%TYPE );

2699

2700

2701 DROP FUNCTION "create_snapshot"

2702 ( "issue_id_p" "issue"."id"%TYPE );

2703

2704

2705 CREATE FUNCTION "take_snapshot"

2706 ( "issue_id_p" "issue"."id"%TYPE,

2707 "area_id_p" "area"."id"%TYPE = NULL )

2708 RETURNS "snapshot"."id"%TYPE

2709 LANGUAGE 'plpgsql' VOLATILE AS $$

2710 DECLARE

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

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

2713 "snapshot_id_v" "snapshot"."id"%TYPE;

2714 "issue_id_v" "issue"."id"%TYPE;

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

2716 BEGIN

2717 IF "issue_id_p" NOTNULL AND "area_id_p" NOTNULL THEN

2718 RAISE EXCEPTION 'One of "issue_id_p" and "area_id_p" must be NULL';

2719 END IF;

2720 PERFORM "require_transaction_isolation"();

2721 IF "issue_id_p" ISNULL THEN

2722 "area_id_v" := "area_id_p";

2723 ELSE

2724 SELECT "area_id" INTO "area_id_v"

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

2726 END IF;

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

2728 INSERT INTO "snapshot" ("area_id", "issue_id")

2729 VALUES ("area_id_v", "issue_id_p")

2730 RETURNING "id" INTO "snapshot_id_v";

2731 INSERT INTO "snapshot_population" ("snapshot_id", "member_id")

2732 SELECT "snapshot_id_v", "member_id"

2733 FROM "unit_member" WHERE "unit_id" = "unit_id_v";

2734 UPDATE "snapshot" SET

2735 "population" = (

2736 SELECT count(1) FROM "snapshot_population"

2737 WHERE "snapshot_id" = "snapshot_id_v"

2738 ) WHERE "id" = "snapshot_id_v";

2739 FOR "issue_id_v" IN

2740 SELECT "id" FROM "issue"

2741 WHERE CASE WHEN "issue_id_p" ISNULL THEN

2742 "area_id" = "area_id_p" AND

2743 "state" = 'admission'

2744 ELSE

2745 "id" = "issue_id_p"

2746 END

2747 LOOP

2748 INSERT INTO "snapshot_issue" ("snapshot_id", "issue_id")

2749 VALUES ("snapshot_id_v", "issue_id_v");

2750 INSERT INTO "direct_interest_snapshot"

2751 ("snapshot_id", "issue_id", "member_id")

2752 SELECT

2753 "snapshot_id_v" AS "snapshot_id",

2754 "issue_id_v" AS "issue_id",

2755 "member"."id" AS "member_id"

2756 FROM "issue"

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

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

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

2760 JOIN "privilege"

2761 ON "privilege"."unit_id" = "area"."unit_id"

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

2763 WHERE "issue"."id" = "issue_id_v"

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

2765 FOR "member_id_v" IN

2766 SELECT "member_id" FROM "direct_interest_snapshot"

2767 WHERE "snapshot_id" = "snapshot_id_v"

2768 AND "issue_id" = "issue_id_v"

2769 LOOP

2770 UPDATE "direct_interest_snapshot" SET

2771 "weight" = 1 +

2772 "weight_of_added_delegations_for_snapshot"(

2773 "snapshot_id_v",

2774 "issue_id_v",

2775 "member_id_v",

2776 '{}'

2777 )

2778 WHERE "snapshot_id" = "snapshot_id_v"

2779 AND "issue_id" = "issue_id_v"

2780 AND "member_id" = "member_id_v";

2781 END LOOP;

2782 INSERT INTO "direct_supporter_snapshot"

2783 ( "snapshot_id", "issue_id", "initiative_id", "member_id",

2784 "draft_id", "informed", "satisfied" )

2785 SELECT

2786 "snapshot_id_v" AS "snapshot_id",

2787 "issue_id_v" AS "issue_id",

2788 "initiative"."id" AS "initiative_id",

2789 "supporter"."member_id" AS "member_id",

2790 "supporter"."draft_id" AS "draft_id",

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

2792 NOT EXISTS (

2793 SELECT NULL FROM "critical_opinion"

2794 WHERE "initiative_id" = "initiative"."id"

2795 AND "member_id" = "supporter"."member_id"

2796 ) AS "satisfied"

2797 FROM "initiative"

2798 JOIN "supporter"

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

2800 JOIN "current_draft"

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

2802 JOIN "direct_interest_snapshot"

2803 ON "snapshot_id_v" = "direct_interest_snapshot"."snapshot_id"

2804 AND "supporter"."member_id" = "direct_interest_snapshot"."member_id"

2805 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"

2806 WHERE "initiative"."issue_id" = "issue_id_v";

2807 DELETE FROM "temporary_suggestion_counts";

2808 INSERT INTO "temporary_suggestion_counts"

2809 ( "id",

2810 "minus2_unfulfilled_count", "minus2_fulfilled_count",

2811 "minus1_unfulfilled_count", "minus1_fulfilled_count",

2812 "plus1_unfulfilled_count", "plus1_fulfilled_count",

2813 "plus2_unfulfilled_count", "plus2_fulfilled_count" )

2814 SELECT

2815 "suggestion"."id",

2816 ( SELECT coalesce(sum("di"."weight"), 0)

2817 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"

2818 ON "di"."snapshot_id" = "snapshot_id_v"

2819 AND "di"."issue_id" = "issue_id_v"

2820 AND "di"."member_id" = "opinion"."member_id"

2821 WHERE "opinion"."suggestion_id" = "suggestion"."id"

2822 AND "opinion"."degree" = -2

2823 AND "opinion"."fulfilled" = FALSE

2824 ) AS "minus2_unfulfilled_count",

2825 ( SELECT coalesce(sum("di"."weight"), 0)

2826 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"

2827 ON "di"."snapshot_id" = "snapshot_id_v"

2828 AND "di"."issue_id" = "issue_id_v"

2829 AND "di"."member_id" = "opinion"."member_id"

2830 WHERE "opinion"."suggestion_id" = "suggestion"."id"

2831 AND "opinion"."degree" = -2

2832 AND "opinion"."fulfilled" = TRUE

2833 ) AS "minus2_fulfilled_count",

2834 ( SELECT coalesce(sum("di"."weight"), 0)

2835 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"

2836 ON "di"."snapshot_id" = "snapshot_id_v"

2837 AND "di"."issue_id" = "issue_id_v"

2838 AND "di"."member_id" = "opinion"."member_id"

2839 WHERE "opinion"."suggestion_id" = "suggestion"."id"

2840 AND "opinion"."degree" = -1

2841 AND "opinion"."fulfilled" = FALSE

2842 ) AS "minus1_unfulfilled_count",

2843 ( SELECT coalesce(sum("di"."weight"), 0)

2844 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"

2845 ON "di"."snapshot_id" = "snapshot_id_v"

2846 AND "di"."issue_id" = "issue_id_v"

2847 AND "di"."member_id" = "opinion"."member_id"

2848 WHERE "opinion"."suggestion_id" = "suggestion"."id"

2849 AND "opinion"."degree" = -1

2850 AND "opinion"."fulfilled" = TRUE

2851 ) AS "minus1_fulfilled_count",

2852 ( SELECT coalesce(sum("di"."weight"), 0)

2853 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"

2854 ON "di"."snapshot_id" = "snapshot_id_v"

2855 AND "di"."issue_id" = "issue_id_v"

2856 AND "di"."member_id" = "opinion"."member_id"

2857 WHERE "opinion"."suggestion_id" = "suggestion"."id"

2858 AND "opinion"."degree" = 1

2859 AND "opinion"."fulfilled" = FALSE

2860 ) AS "plus1_unfulfilled_count",

2861 ( SELECT coalesce(sum("di"."weight"), 0)

2862 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"

2863 ON "di"."snapshot_id" = "snapshot_id_v"

2864 AND "di"."issue_id" = "issue_id_v"

2865 AND "di"."member_id" = "opinion"."member_id"

2866 WHERE "opinion"."suggestion_id" = "suggestion"."id"

2867 AND "opinion"."degree" = 1

2868 AND "opinion"."fulfilled" = TRUE

2869 ) AS "plus1_fulfilled_count",

2870 ( SELECT coalesce(sum("di"."weight"), 0)

2871 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"

2872 ON "di"."snapshot_id" = "snapshot_id_v"

2873 AND "di"."issue_id" = "issue_id_v"

2874 AND "di"."member_id" = "opinion"."member_id"

2875 WHERE "opinion"."suggestion_id" = "suggestion"."id"

2876 AND "opinion"."degree" = 2

2877 AND "opinion"."fulfilled" = FALSE

2878 ) AS "plus2_unfulfilled_count",

2879 ( SELECT coalesce(sum("di"."weight"), 0)

2880 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"

2881 ON "di"."snapshot_id" = "snapshot_id_v"

2882 AND "di"."issue_id" = "issue_id_v"

2883 AND "di"."member_id" = "opinion"."member_id"

2884 WHERE "opinion"."suggestion_id" = "suggestion"."id"

2885 AND "opinion"."degree" = 2

2886 AND "opinion"."fulfilled" = TRUE

2887 ) AS "plus2_fulfilled_count"

2888 FROM "suggestion" JOIN "initiative"

2889 ON "suggestion"."initiative_id" = "initiative"."id"

2890 WHERE "initiative"."issue_id" = "issue_id_v";

2891 END LOOP;

2892 RETURN "snapshot_id_v";

2893 END;

2894 $$;

2895

2896 COMMENT ON FUNCTION "take_snapshot"

2897 ( "issue"."id"%TYPE,

2898 "area"."id"%TYPE )

2899 IS 'This function creates a new interest/supporter snapshot of a particular issue, or, if the first argument is NULL, for all issues in ''admission'' phase of the area given as second argument. It must be executed with TRANSACTION ISOLATION LEVEL REPEATABLE READ. The snapshot must later be finished by calling "finish_snapshot" for every issue.';

2900

2901

2902 DROP FUNCTION "set_snapshot_event"

2903 ( "issue_id_p" "issue"."id"%TYPE,

2904 "event_p" "snapshot_event" );

2905

2906

2907 CREATE FUNCTION "finish_snapshot"

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

2909 RETURNS VOID

2910 LANGUAGE 'plpgsql' VOLATILE AS $$

2911 DECLARE

2912 "snapshot_id_v" "snapshot"."id"%TYPE;

2913 BEGIN

2914 -- NOTE: function does not require snapshot isolation but we don't call

2915 -- "dont_require_snapshot_isolation" here because this function is

2916 -- also invoked by "check_issue"

2917 LOCK TABLE "snapshot" IN EXCLUSIVE MODE;

2918 SELECT "id" INTO "snapshot_id_v" FROM "snapshot"

2919 ORDER BY "id" DESC LIMIT 1;

2920 UPDATE "issue" SET

2921 "calculated" = "snapshot"."calculated",

2922 "latest_snapshot_id" = "snapshot_id_v",

2923 "population" = "snapshot"."population"

2924 FROM "snapshot"

2925 WHERE "issue"."id" = "issue_id_p"

2926 AND "snapshot"."id" = "snapshot_id_v";

2927 UPDATE "initiative" SET

2928 "supporter_count" = (

2929 SELECT coalesce(sum("di"."weight"), 0)

2930 FROM "direct_interest_snapshot" AS "di"

2931 JOIN "direct_supporter_snapshot" AS "ds"

2932 ON "di"."member_id" = "ds"."member_id"

2933 WHERE "di"."snapshot_id" = "snapshot_id_v"

2934 AND "di"."issue_id" = "issue_id_p"

2935 AND "ds"."snapshot_id" = "snapshot_id_v"

2936 AND "ds"."initiative_id" = "initiative"."id"

2937 ),

2938 "informed_supporter_count" = (

2939 SELECT coalesce(sum("di"."weight"), 0)

2940 FROM "direct_interest_snapshot" AS "di"

2941 JOIN "direct_supporter_snapshot" AS "ds"

2942 ON "di"."member_id" = "ds"."member_id"

2943 WHERE "di"."snapshot_id" = "snapshot_id_v"

2944 AND "di"."issue_id" = "issue_id_p"

2945 AND "ds"."snapshot_id" = "snapshot_id_v"

2946 AND "ds"."initiative_id" = "initiative"."id"

2947 AND "ds"."informed"

2948 ),

2949 "satisfied_supporter_count" = (

2950 SELECT coalesce(sum("di"."weight"), 0)

2951 FROM "direct_interest_snapshot" AS "di"

2952 JOIN "direct_supporter_snapshot" AS "ds"

2953 ON "di"."member_id" = "ds"."member_id"

2954 WHERE "di"."snapshot_id" = "snapshot_id_v"

2955 AND "di"."issue_id" = "issue_id_p"

2956 AND "ds"."snapshot_id" = "snapshot_id_v"

2957 AND "ds"."initiative_id" = "initiative"."id"

2958 AND "ds"."satisfied"

2959 ),

2960 "satisfied_informed_supporter_count" = (

2961 SELECT coalesce(sum("di"."weight"), 0)

2962 FROM "direct_interest_snapshot" AS "di"

2963 JOIN "direct_supporter_snapshot" AS "ds"

2964 ON "di"."member_id" = "ds"."member_id"

2965 WHERE "di"."snapshot_id" = "snapshot_id_v"

2966 AND "di"."issue_id" = "issue_id_p"

2967 AND "ds"."snapshot_id" = "snapshot_id_v"

2968 AND "ds"."initiative_id" = "initiative"."id"

2969 AND "ds"."informed"

2970 AND "ds"."satisfied"

2971 )

2972 WHERE "issue_id" = "issue_id_p";

2973 UPDATE "suggestion" SET

2974 "minus2_unfulfilled_count" = "temp"."minus2_unfulfilled_count",

2975 "minus2_fulfilled_count" = "temp"."minus2_fulfilled_count",

2976 "minus1_unfulfilled_count" = "temp"."minus1_unfulfilled_count",

2977 "minus1_fulfilled_count" = "temp"."minus1_fulfilled_count",

2978 "plus1_unfulfilled_count" = "temp"."plus1_unfulfilled_count",

2979 "plus1_fulfilled_count" = "temp"."plus1_fulfilled_count",

2980 "plus2_unfulfilled_count" = "temp"."plus2_unfulfilled_count",

2981 "plus2_fulfilled_count" = "temp"."plus2_fulfilled_count"

2982 FROM "temporary_suggestion_counts" AS "temp", "initiative"

2983 WHERE "temp"."id" = "suggestion"."id"

2984 AND "initiative"."issue_id" = "issue_id_p"

2985 AND "suggestion"."initiative_id" = "initiative"."id";

2986 DELETE FROM "temporary_suggestion_counts";

2987 RETURN;

2988 END;

2989 $$;

2990

2991 COMMENT ON FUNCTION "finish_snapshot"

2992 ( "issue"."id"%TYPE )

2993 IS 'After calling "take_snapshot", this function "finish_snapshot" needs to be called for every issue in the snapshot (separate function calls keep locking time minimal)';

2994

2995

2996 CREATE FUNCTION "issue_admission"

2997 ( "area_id_p" "area"."id"%TYPE )

2998 RETURNS BOOLEAN

2999 LANGUAGE 'plpgsql' VOLATILE AS $$

3000 DECLARE

3001 "issue_id_v" "issue"."id"%TYPE;

3002 BEGIN

3003 PERFORM "dont_require_transaction_isolation"();

3004 LOCK TABLE "snapshot" IN EXCLUSIVE MODE;

3005 UPDATE "area" SET "issue_quorum" = "view"."issue_quorum"

3006 FROM "area_quorum" AS "view"

3007 WHERE "area"."id" = "view"."area_id"

3008 AND "area"."id" = "area_id_p";

3009 SELECT "id" INTO "issue_id_v" FROM "issue_for_admission"

3010 WHERE "area_id" = "area_id_p";

3011 IF "issue_id_v" ISNULL THEN RETURN FALSE; END IF;

3012 UPDATE "issue" SET

3013 "admission_snapshot_id" = "latest_snapshot_id",

3014 "state" = 'discussion',

3015 "accepted" = now(),

3016 "phase_finished" = NULL

3017 WHERE "id" = "issue_id_v";

3018 RETURN TRUE;

3019 END;

3020 $$;

3021

3022 COMMENT ON FUNCTION "issue_admission"

3023 ( "area"."id"%TYPE )

3024 IS 'Checks if an issue in the area can be admitted for further discussion; returns TRUE on success in which case the function must be called again until it returns FALSE';

3025

3026

3027 CREATE OR REPLACE FUNCTION "check_issue"

3028 ( "issue_id_p" "issue"."id"%TYPE,

3029 "persist" "check_issue_persistence" )

3030 RETURNS "check_issue_persistence"

3031 LANGUAGE 'plpgsql' VOLATILE AS $$

3032 DECLARE

3033 "issue_row" "issue"%ROWTYPE;

3034 "last_calculated_v" "snapshot"."calculated"%TYPE;

3035 "policy_row" "policy"%ROWTYPE;

3036 "initiative_row" "initiative"%ROWTYPE;

3037 "state_v" "issue_state";

3038 BEGIN

3039 PERFORM "require_transaction_isolation"();

3040 IF "persist" ISNULL THEN

3041 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"

3042 FOR UPDATE;

3043 SELECT "calculated" INTO "last_calculated_v"

3044 FROM "snapshot" JOIN "snapshot_issue"

3045 ON "snapshot"."id" = "snapshot_issue"."snapshot_id"

3046 WHERE "snapshot_issue"."issue_id" = "issue_id_p";

3047 IF "issue_row"."closed" NOTNULL THEN

3048 RETURN NULL;

3049 END IF;

3050 "persist"."state" := "issue_row"."state";

3051 IF

3052 ( "issue_row"."state" = 'admission' AND "last_calculated_v" >=

3053 "issue_row"."created" + "issue_row"."max_admission_time" ) OR

3054 ( "issue_row"."state" = 'discussion' AND now() >=

3055 "issue_row"."accepted" + "issue_row"."discussion_time" ) OR

3056 ( "issue_row"."state" = 'verification' AND now() >=

3057 "issue_row"."half_frozen" + "issue_row"."verification_time" ) OR

3058 ( "issue_row"."state" = 'voting' AND now() >=

3059 "issue_row"."fully_frozen" + "issue_row"."voting_time" )

3060 THEN

3061 "persist"."phase_finished" := TRUE;

3062 ELSE

3063 "persist"."phase_finished" := FALSE;

3064 END IF;

3065 IF

3066 NOT EXISTS (

3067 -- all initiatives are revoked

3068 SELECT NULL FROM "initiative"

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

3070 ) AND (

3071 -- and issue has not been accepted yet

3072 "persist"."state" = 'admission' OR

3073 -- or verification time has elapsed

3074 ( "persist"."state" = 'verification' AND

3075 "persist"."phase_finished" ) OR

3076 -- or no initiatives have been revoked lately

3077 NOT EXISTS (

3078 SELECT NULL FROM "initiative"

3079 WHERE "issue_id" = "issue_id_p"

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

3081 )

3082 )

3083 THEN

3084 "persist"."issue_revoked" := TRUE;

3085 ELSE

3086 "persist"."issue_revoked" := FALSE;

3087 END IF;

3088 IF "persist"."phase_finished" OR "persist"."issue_revoked" THEN

3089 UPDATE "issue" SET "phase_finished" = now()

3090 WHERE "id" = "issue_row"."id";

3091 RETURN "persist";

3092 ELSIF

3093 "persist"."state" IN ('admission', 'discussion', 'verification')

3094 THEN

3095 RETURN "persist";

3096 ELSE

3097 RETURN NULL;

3098 END IF;

3099 END IF;

3100 IF

3101 "persist"."state" IN ('admission', 'discussion', 'verification') AND

3102 coalesce("persist"."snapshot_created", FALSE) = FALSE

3103 THEN

3104 IF "persist"."state" != 'admission' THEN

3105 PERFORM "take_snapshot"("issue_id_p");

3106 PERFORM "finish_snapshot"("issue_id_p");

3107 END IF;

3108 "persist"."snapshot_created" = TRUE;

3109 IF "persist"."phase_finished" THEN

3110 IF "persist"."state" = 'admission' THEN

3111 UPDATE "issue" SET "admission_snapshot_id" = "latest_snapshot_id"

3112 WHERE "id" = "issue_id_p";

3113 ELSIF "persist"."state" = 'discussion' THEN

3114 UPDATE "issue" SET "half_freeze_snapshot_id" = "latest_snapshot_id"

3115 WHERE "id" = "issue_id_p";

3116 ELSIF "persist"."state" = 'verification' THEN

3117 UPDATE "issue" SET "full_freeze_snapshot_id" = "latest_snapshot_id"

3118 WHERE "id" = "issue_id_p";

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

3120 SELECT * INTO "policy_row" FROM "policy"

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

3122 FOR "initiative_row" IN

3123 SELECT * FROM "initiative"

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

3125 FOR UPDATE

3126 LOOP

3127 IF

3128 "initiative_row"."polling" OR (

3129 "initiative_row"."satisfied_supporter_count" >

3130 "policy_row"."initiative_quorum" AND

3131 "initiative_row"."satisfied_supporter_count" *

3132 "policy_row"."initiative_quorum_den" >=

3133 "issue_row"."population" * "policy_row"."initiative_quorum_num"

3134 )

3135 THEN

3136 UPDATE "initiative" SET "admitted" = TRUE

3137 WHERE "id" = "initiative_row"."id";

3138 ELSE

3139 UPDATE "initiative" SET "admitted" = FALSE

3140 WHERE "id" = "initiative_row"."id";

3141 END IF;

3142 END LOOP;

3143 END IF;

3144 END IF;

3145 RETURN "persist";

3146 END IF;

3147 IF

3148 "persist"."state" IN ('admission', 'discussion', 'verification') AND

3149 coalesce("persist"."harmonic_weights_set", FALSE) = FALSE

3150 THEN

3151 PERFORM "set_harmonic_initiative_weights"("issue_id_p");

3152 "persist"."harmonic_weights_set" = TRUE;

3153 IF

3154 "persist"."phase_finished" OR

3155 "persist"."issue_revoked" OR

3156 "persist"."state" = 'admission'

3157 THEN

3158 RETURN "persist";

3159 ELSE

3160 RETURN NULL;

3161 END IF;

3162 END IF;

3163 IF "persist"."issue_revoked" THEN

3164 IF "persist"."state" = 'admission' THEN

3165 "state_v" := 'canceled_revoked_before_accepted';

3166 ELSIF "persist"."state" = 'discussion' THEN

3167 "state_v" := 'canceled_after_revocation_during_discussion';

3168 ELSIF "persist"."state" = 'verification' THEN

3169 "state_v" := 'canceled_after_revocation_during_verification';

3170 END IF;

3171 UPDATE "issue" SET

3172 "state" = "state_v",

3173 "closed" = "phase_finished",

3174 "phase_finished" = NULL

3175 WHERE "id" = "issue_id_p";

3176 RETURN NULL;

3177 END IF;

3178 IF "persist"."state" = 'admission' THEN

3179 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"

3180 FOR UPDATE;

3181 IF "issue_row"."phase_finished" NOTNULL THEN

3182 UPDATE "issue" SET

3183 "state" = 'canceled_issue_not_accepted',

3184 "closed" = "phase_finished",

3185 "phase_finished" = NULL

3186 WHERE "id" = "issue_id_p";

3187 END IF;

3188 RETURN NULL;

3189 END IF;

3190 IF "persist"."phase_finished" THEN

3191 IF "persist"."state" = 'discussion' THEN

3192 UPDATE "issue" SET

3193 "state" = 'verification',

3194 "half_frozen" = "phase_finished",

3195 "phase_finished" = NULL

3196 WHERE "id" = "issue_id_p";

3197 RETURN NULL;

3198 END IF;

3199 IF "persist"."state" = 'verification' THEN

3200 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"

3201 FOR UPDATE;

3202 SELECT * INTO "policy_row" FROM "policy"

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

3204 IF EXISTS (

3205 SELECT NULL FROM "initiative"

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

3207 ) THEN

3208 UPDATE "issue" SET

3209 "state" = 'voting',

3210 "fully_frozen" = "phase_finished",

3211 "phase_finished" = NULL

3212 WHERE "id" = "issue_id_p";

3213 ELSE

3214 UPDATE "issue" SET

3215 "state" = 'canceled_no_initiative_admitted',

3216 "fully_frozen" = "phase_finished",

3217 "closed" = "phase_finished",

3218 "phase_finished" = NULL

3219 WHERE "id" = "issue_id_p";

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

3221 -- issue state has been manipulated

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

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

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

3225 END IF;

3226 RETURN NULL;

3227 END IF;

3228 IF "persist"."state" = 'voting' THEN

3229 IF coalesce("persist"."closed_voting", FALSE) = FALSE THEN

3230 PERFORM "close_voting"("issue_id_p");

3231 "persist"."closed_voting" = TRUE;

3232 RETURN "persist";

3233 END IF;

3234 PERFORM "calculate_ranks"("issue_id_p");

3235 RETURN NULL;

3236 END IF;

3237 END IF;

3238 RAISE WARNING 'should not happen';

3239 RETURN NULL;

3240 END;

3241 $$;

3242

3243

3244 CREATE OR REPLACE FUNCTION "check_everything"()

3245 RETURNS VOID

3246 LANGUAGE 'plpgsql' VOLATILE AS $$

3247 DECLARE

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

3249 "snapshot_id_v" "snapshot"."id"%TYPE;

3250 "issue_id_v" "issue"."id"%TYPE;

3251 "persist_v" "check_issue_persistence";

3252 BEGIN

3253 RAISE WARNING 'Function "check_everything" should only be used for development and debugging purposes';

3254 DELETE FROM "expired_session";

3255 DELETE FROM "expired_token";

3256 DELETE FROM "expired_snapshot";

3257 PERFORM "check_activity"();

3258 PERFORM "calculate_member_counts"();

3259 FOR "area_id_v" IN SELECT "id" FROM "area_with_unaccepted_issues" LOOP

3260 SELECT "take_snapshot"(NULL, "area_id_v") INTO "snapshot_id_v";

3261 PERFORM "finish_snapshot"("issue_id") FROM "snapshot_issue"

3262 WHERE "snapshot_id" = "snapshot_id_v";

3263 LOOP

3264 EXIT WHEN "issue_admission"("area_id_v") = FALSE;

3265 END LOOP;

3266 END LOOP;

3267 FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP

3268 "persist_v" := NULL;

3269 LOOP

3270 "persist_v" := "check_issue"("issue_id_v", "persist_v");

3271 EXIT WHEN "persist_v" ISNULL;

3272 END LOOP;

3273 END LOOP;

3274 RETURN;

3275 END;

3276 $$;

3277

3278 COMMENT ON FUNCTION "check_everything"() IS 'Amongst other regular tasks, this function performs "check_issue" for every open issue. Use this function only for development and debugging purposes, as you may run into locking and/or serialization problems in productive environments. For production, use lf_update binary instead';

3279

3280

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

3282 RETURNS VOID

3283 LANGUAGE 'plpgsql' VOLATILE AS $$

3284 BEGIN

3285 IF EXISTS (

3286 SELECT NULL FROM "issue" WHERE "id" = "issue_id_p" AND "cleaned" ISNULL

3287 ) THEN

3288 -- override protection triggers:

3289 INSERT INTO "temporary_transaction_data" ("key", "value")

3290 VALUES ('override_protection_triggers', TRUE::TEXT);

3291 -- clean data:

3292 DELETE FROM "delegating_voter"

3293 WHERE "issue_id" = "issue_id_p";

3294 DELETE FROM "direct_voter"

3295 WHERE "issue_id" = "issue_id_p";

3296 DELETE FROM "delegating_interest_snapshot"

3297 WHERE "issue_id" = "issue_id_p";

3298 DELETE FROM "direct_interest_snapshot"

3299 WHERE "issue_id" = "issue_id_p";

3300 DELETE FROM "non_voter"

3301 WHERE "issue_id" = "issue_id_p";

3302 DELETE FROM "delegation"

3303 WHERE "issue_id" = "issue_id_p";

3304 DELETE FROM "supporter"

3305 USING "initiative" -- NOTE: due to missing index on issue_id

3306 WHERE "initiative"."issue_id" = "issue_id_p"

3307 AND "supporter"."initiative_id" = "initiative_id";

3308 -- mark issue as cleaned:

3309 UPDATE "issue" SET "cleaned" = now() WHERE "id" = "issue_id_p";

3310 -- finish overriding protection triggers (avoids garbage):

3311 DELETE FROM "temporary_transaction_data"

3312 WHERE "key" = 'override_protection_triggers';

3313 END IF;

3314 RETURN;

3315 END;

3316 $$;

3317

3318

3319 CREATE OR REPLACE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)

3320 RETURNS VOID

3321 LANGUAGE 'plpgsql' VOLATILE AS $$

3322 BEGIN

3323 UPDATE "member" SET

3324 "last_login" = NULL,

3325 "last_delegation_check" = NULL,

3326 "login" = NULL,

3327 "password" = NULL,

3328 "authority" = NULL,

3329 "authority_uid" = NULL,

3330 "authority_login" = NULL,

3331 "deleted" = coalesce("deleted", now()),

3332 "locked" = TRUE,

3333 "active" = FALSE,

3334 "notify_email" = NULL,

3335 "notify_email_unconfirmed" = NULL,

3336 "notify_email_secret" = NULL,

3337 "notify_email_secret_expiry" = NULL,

3338 "notify_email_lock_expiry" = NULL,

3339 "disable_notifications" = TRUE,

3340 "notification_counter" = DEFAULT,

3341 "notification_sample_size" = 0,

3342 "notification_dow" = NULL,

3343 "notification_hour" = NULL,

3344 "notification_sent" = NULL,

3345 "login_recovery_expiry" = NULL,

3346 "password_reset_secret" = NULL,

3347 "password_reset_secret_expiry" = NULL,

3348 "location" = NULL

3349 WHERE "id" = "member_id_p";

3350 -- "text_search_data" is updated by triggers

3351 DELETE FROM "member_settings" WHERE "member_id" = "member_id_p";

3352 DELETE FROM "member_profile" WHERE "member_id" = "member_id_p";

3353 DELETE FROM "rendered_member_statement" WHERE "member_id" = "member_id_p";

3354 DELETE FROM "member_image" WHERE "member_id" = "member_id_p";

3355 DELETE FROM "contact" WHERE "member_id" = "member_id_p";

3356 DELETE FROM "ignored_member" WHERE "member_id" = "member_id_p";

3357 DELETE FROM "session" WHERE "member_id" = "member_id_p";

3358 DELETE FROM "member_application" WHERE "member_id" = "member_id_p";

3359 DELETE FROM "token" WHERE "member_id" = "member_id_p";

3360 DELETE FROM "subscription" WHERE "member_id" = "member_id_p";

3361 DELETE FROM "ignored_area" WHERE "member_id" = "member_id_p";

3362 DELETE FROM "ignored_initiative" WHERE "member_id" = "member_id_p";

3363 DELETE FROM "delegation" WHERE "truster_id" = "member_id_p";

3364 DELETE FROM "non_voter" WHERE "member_id" = "member_id_p";

3365 DELETE FROM "direct_voter" USING "issue"

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

3367 AND "issue"."closed" ISNULL

3368 AND "member_id" = "member_id_p";

3369 DELETE FROM "notification_initiative_sent" WHERE "member_id" = "member_id_p";

3370 RETURN;

3371 END;

3372 $$;

3373

3374

3375 CREATE OR REPLACE FUNCTION "delete_private_data"()

3376 RETURNS VOID

3377 LANGUAGE 'plpgsql' VOLATILE AS $$

3378 BEGIN

3379 DELETE FROM "temporary_transaction_data";

3380 DELETE FROM "temporary_suggestion_counts";

3381 DELETE FROM "member" WHERE "activated" ISNULL;

3382 UPDATE "member" SET

3383 "invite_code" = NULL,

3384 "invite_code_expiry" = NULL,

3385 "admin_comment" = NULL,

3386 "last_login" = NULL,

3387 "last_delegation_check" = NULL,

3388 "login" = NULL,

3389 "password" = NULL,

3390 "authority" = NULL,

3391 "authority_uid" = NULL,

3392 "authority_login" = NULL,

3393 "lang" = NULL,

3394 "notify_email" = NULL,

3395 "notify_email_unconfirmed" = NULL,

3396 "notify_email_secret" = NULL,

3397 "notify_email_secret_expiry" = NULL,

3398 "notify_email_lock_expiry" = NULL,

3399 "disable_notifications" = TRUE,

3400 "notification_counter" = DEFAULT,

3401 "notification_sample_size" = 0,

3402 "notification_dow" = NULL,

3403 "notification_hour" = NULL,

3404 "notification_sent" = NULL,

3405 "login_recovery_expiry" = NULL,

3406 "password_reset_secret" = NULL,

3407 "password_reset_secret_expiry" = NULL,

3408 "location" = NULL;

3409 -- "text_search_data" is updated by triggers

3410 DELETE FROM "verification";

3411 DELETE FROM "member_settings";

3412 DELETE FROM "member_useterms";

3413 DELETE FROM "member_profile";

3414 DELETE FROM "rendered_member_statement";

3415 DELETE FROM "member_image";

3416 DELETE FROM "contact";

3417 DELETE FROM "ignored_member";

3418 DELETE FROM "session";

3419 DELETE FROM "system_application";

3420 DELETE FROM "system_application_redirect_uri";

3421 DELETE FROM "dynamic_application_scope";

3422 DELETE FROM "member_application";

3423 DELETE FROM "token";

3424 DELETE FROM "subscription";

3425 DELETE FROM "ignored_area";

3426 DELETE FROM "ignored_initiative";

3427 DELETE FROM "non_voter";

3428 DELETE FROM "direct_voter" USING "issue"

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

3430 AND "issue"."closed" ISNULL;

3431 DELETE FROM "event_processed";

3432 DELETE FROM "notification_initiative_sent";

3433 DELETE FROM "newsletter";

3434 RETURN;

3435 END;

3436 $$;

3437

3438

3439 CREATE TEMPORARY TABLE "old_snapshot" AS

3440 SELECT "ordered".*, row_number() OVER () AS "snapshot_id"

3441 FROM (

3442 SELECT * FROM (

3443 SELECT

3444 "id" AS "issue_id",

3445 'end_of_admission'::"snapshot_event" AS "event",

3446 "accepted" AS "calculated"

3447 FROM "issue" WHERE "accepted" NOTNULL

3448 UNION ALL

3449 SELECT

3450 "id" AS "issue_id",

3451 'half_freeze'::"snapshot_event" AS "event",

3452 "half_frozen" AS "calculated"

3453 FROM "issue" WHERE "half_frozen" NOTNULL

3454 UNION ALL

3455 SELECT

3456 "id" AS "issue_id",

3457 'full_freeze'::"snapshot_event" AS "event",

3458 "fully_frozen" AS "calculated"

3459 FROM "issue" WHERE "fully_frozen" NOTNULL

3460 ) AS "unordered"

3461 ORDER BY "calculated", "issue_id", "event"

3462 ) AS "ordered";

3463

3464

3465 INSERT INTO "snapshot" ("id", "calculated", "population", "area_id", "issue_id")

3466 SELECT

3467 "old_snapshot"."snapshot_id" AS "id",

3468 "old_snapshot"."calculated",

3469 ( SELECT COALESCE(sum("weight"), 0)

3470 FROM "direct_population_snapshot" "dps"

3471 WHERE "dps"."issue_id" = "old_snapshot"."issue_id"

3472 AND "dps"."event" = "old_snapshot"."event"

3473 ) AS "population",

3474 "issue"."area_id" AS "area_id",

3475 "issue"."id" AS "issue_id"

3476 FROM "old_snapshot" JOIN "issue"

3477 ON "old_snapshot"."issue_id" = "issue"."id";

3478

3479

3480 INSERT INTO "snapshot_issue" ("snapshot_id", "issue_id")

3481 SELECT "id" AS "snapshot_id", "issue_id" FROM "snapshot";

3482

3483

3484 INSERT INTO "snapshot_population" ("snapshot_id", "member_id")

3485 SELECT

3486 "old_snapshot"."snapshot_id",

3487 "direct_population_snapshot"."member_id"

3488 FROM "old_snapshot" JOIN "direct_population_snapshot"

3489 ON "old_snapshot"."issue_id" = "direct_population_snapshot"."issue_id"

3490 AND "old_snapshot"."event" = "direct_population_snapshot"."event";

3491

3492 INSERT INTO "snapshot_population" ("snapshot_id", "member_id")

3493 SELECT

3494 "old_snapshot"."snapshot_id",

3495 "delegating_population_snapshot"."member_id"

3496 FROM "old_snapshot" JOIN "delegating_population_snapshot"

3497 ON "old_snapshot"."issue_id" = "delegating_population_snapshot"."issue_id"

3498 AND "old_snapshot"."event" = "delegating_population_snapshot"."event";

3499

3500

3501 INSERT INTO "direct_interest_snapshot"

3502 ("snapshot_id", "issue_id", "member_id", "weight")

3503 SELECT

3504 "old_snapshot"."snapshot_id",

3505 "old_snapshot"."issue_id",

3506 "direct_interest_snapshot_old"."member_id",

3507 "direct_interest_snapshot_old"."weight"

3508 FROM "old_snapshot" JOIN "direct_interest_snapshot_old"

3509 ON "old_snapshot"."issue_id" = "direct_interest_snapshot_old"."issue_id"

3510 AND "old_snapshot"."event" = "direct_interest_snapshot_old"."event";

3511

3512 INSERT INTO "delegating_interest_snapshot"

3513 ( "snapshot_id", "issue_id",

3514 "member_id", "weight", "scope", "delegate_member_ids" )

3515 SELECT

3516 "old_snapshot"."snapshot_id",

3517 "old_snapshot"."issue_id",

3518 "delegating_interest_snapshot_old"."member_id",

3519 "delegating_interest_snapshot_old"."weight",

3520 "delegating_interest_snapshot_old"."scope",

3521 "delegating_interest_snapshot_old"."delegate_member_ids"

3522 FROM "old_snapshot" JOIN "delegating_interest_snapshot_old"

3523 ON "old_snapshot"."issue_id" = "delegating_interest_snapshot_old"."issue_id"

3524 AND "old_snapshot"."event" = "delegating_interest_snapshot_old"."event";

3525

3526 INSERT INTO "direct_supporter_snapshot"

3527 ( "snapshot_id", "issue_id",

3528 "initiative_id", "member_id", "draft_id", "informed", "satisfied" )

3529 SELECT

3530 "old_snapshot"."snapshot_id",

3531 "old_snapshot"."issue_id",

3532 "direct_supporter_snapshot_old"."initiative_id",

3533 "direct_supporter_snapshot_old"."member_id",

3534 "direct_supporter_snapshot_old"."draft_id",

3535 "direct_supporter_snapshot_old"."informed",

3536 "direct_supporter_snapshot_old"."satisfied"

3537 FROM "old_snapshot" JOIN "direct_supporter_snapshot_old"

3538 ON "old_snapshot"."issue_id" = "direct_supporter_snapshot_old"."issue_id"

3539 AND "old_snapshot"."event" = "direct_supporter_snapshot_old"."event";

3540

3541

3542 ALTER TABLE "issue" DISABLE TRIGGER USER; -- NOTE: required to modify table later

3543

3544 UPDATE "issue" SET "latest_snapshot_id" = "snapshot"."id"

3545 FROM (

3546 SELECT DISTINCT ON ("issue_id") "issue_id", "id"

3547 FROM "snapshot" ORDER BY "issue_id", "id" DESC

3548 ) AS "snapshot"

3549 WHERE "snapshot"."issue_id" = "issue"."id";

3550

3551 UPDATE "issue" SET "admission_snapshot_id" = "old_snapshot"."snapshot_id"

3552 FROM "old_snapshot"

3553 WHERE "old_snapshot"."issue_id" = "issue"."id"

3554 AND "old_snapshot"."event" = 'end_of_admission';

3555

3556 UPDATE "issue" SET "half_freeze_snapshot_id" = "old_snapshot"."snapshot_id"

3557 FROM "old_snapshot"

3558 WHERE "old_snapshot"."issue_id" = "issue"."id"

3559 AND "old_snapshot"."event" = 'half_freeze';

3560

3561 UPDATE "issue" SET "full_freeze_snapshot_id" = "old_snapshot"."snapshot_id"

3562 FROM "old_snapshot"

3563 WHERE "old_snapshot"."issue_id" = "issue"."id"

3564 AND "old_snapshot"."event" = 'full_freeze';

3565

3566 ALTER TABLE "issue" ENABLE TRIGGER USER;

3567

3568

3569 DROP TABLE "old_snapshot";

3570

3571 DROP TABLE "direct_supporter_snapshot_old";

3572 DROP TABLE "delegating_interest_snapshot_old";

3573 DROP TABLE "direct_interest_snapshot_old";

3574 DROP TABLE "delegating_population_snapshot";

3575 DROP TABLE "direct_population_snapshot";

3576

3577

3578 DROP VIEW "open_issue";

3579

3580

3581 ALTER TABLE "issue" DROP COLUMN "latest_snapshot_event";

3582

3583

3584 CREATE VIEW "open_issue" AS

3585 SELECT * FROM "issue" WHERE "closed" ISNULL;

3586

3587 COMMENT ON VIEW "open_issue" IS 'All open issues';

3588

3589

3590 -- NOTE: create "issue_for_admission" view after altering table "issue"

3591 CREATE VIEW "issue_for_admission" AS

3592 SELECT DISTINCT ON ("issue"."area_id")

3593 "issue".*,

3594 max("initiative"."supporter_count") AS "max_supporter_count"

3595 FROM "issue"

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

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

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

3599 WHERE "issue"."state" = 'admission'::"issue_state"

3600 AND now() >= "issue"."created" + "issue"."min_admission_time"

3601 AND "initiative"."supporter_count" >= "policy"."issue_quorum"

3602 AND "initiative"."supporter_count" * "policy"."issue_quorum_den" >=

3603 "issue"."population" * "policy"."issue_quorum_num"

3604 AND "initiative"."supporter_count" >= "area"."issue_quorum"

3605 AND "initiative"."revoked" ISNULL

3606 GROUP BY "issue"."id"

3607 ORDER BY "issue"."area_id", "max_supporter_count" DESC, "issue"."id";

3608

3609 COMMENT ON VIEW "issue_for_admission" IS 'Contains up to 1 issue per area eligible to pass from ''admission'' to ''discussion'' state; needs to be recalculated after admitting the issue in this view';

3610

3611

3612 DROP TYPE "snapshot_event";

3613

3614

3615 ALTER TABLE "issue" ADD CONSTRAINT "snapshot_required" CHECK (

3616 ("half_frozen" ISNULL OR "half_freeze_snapshot_id" NOTNULL) AND

3617 ("fully_frozen" ISNULL OR "full_freeze_snapshot_id" NOTNULL) );

3618

3619

3620 COMMIT;