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

liquid_feedback_core

view update/core-update.v3.2.2-v4.0.0.sql @ 625:7f488033d5ee

Fixed mistake in last-level tie-breaking

author	jbe
date	Wed Jan 01 16:49:21 2025 +0100 (10 months ago)
parents	ed3c3d8bd16b
children

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.0', 4, 0, 0))

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 ALTER TABLE "issue" ADD COLUMN "issue_quorum" INT4;

554 ALTER TABLE "issue" ADD COLUMN "initiative_quorum" INT4;

555

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

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

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

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

560

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

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

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

564

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

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

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

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

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

570

571 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")';

572 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")';

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

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

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

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

577 COMMENT ON COLUMN "issue"."issue_quorum" IS 'Calculated number of supporters needed by an initiative of the issue to be "accepted", i.e. pass from ''admission'' to ''discussion'' state';

578 COMMENT ON COLUMN "issue"."initiative_quorum" IS 'Calculated number of satisfied supporters to be reached by an initiative to be "admitted" for voting';

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

580

581

582 ALTER TABLE "issue" DISABLE TRIGGER USER; -- NOTE: required to modify table later

583

584 UPDATE "issue" SET

585 "issue_quorum" = ceil(

586 ("subquery"."population"::INT8 * "policy"."issue_quorum_num"::INT8) /

587 "policy"."issue_quorum_den"::FLOAT8

588 ),

589 "initiative_quorum" = ceil(

590 ("issue"."population"::INT8 * "policy"."initiative_quorum_num"::INT8) /

591 "policy"."initiative_quorum_den"::FLOAT8

592 )

593 FROM (

594 SELECT "issue_id", sum("weight") AS "population"

595 FROM "direct_population_snapshot"

596 WHERE "event" = 'end_of_admission'

597 GROUP BY "issue_id"

598 ) AS "subquery", "policy"

599 WHERE "issue"."id" = "subquery"."issue_id"

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

601

602 ALTER TABLE "issue" ENABLE TRIGGER USER;

603

604

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

606

607

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

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

610

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

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

613

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

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

616

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

618

619

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

621

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

623

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

625

626

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

628

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

630

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

632

633

634 CREATE TABLE "temporary_suggestion_counts" (

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

636 "minus2_unfulfilled_count" INT4 NOT NULL,

637 "minus2_fulfilled_count" INT4 NOT NULL,

638 "minus1_unfulfilled_count" INT4 NOT NULL,

639 "minus1_fulfilled_count" INT4 NOT NULL,

640 "plus1_unfulfilled_count" INT4 NOT NULL,

641 "plus1_fulfilled_count" INT4 NOT NULL,

642 "plus2_unfulfilled_count" INT4 NOT NULL,

643 "plus2_fulfilled_count" INT4 NOT NULL );

644

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

646

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

648

649

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

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

652

653

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

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

656

657

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

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

660

661

662 CREATE TABLE "snapshot_issue" (

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

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

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

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

667

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

669

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

671

672

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

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

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

676

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

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

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

680

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

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

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

684

685

686 CREATE TABLE "direct_interest_snapshot" (

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

688 "snapshot_id" INT8,

689 "issue_id" INT4,

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

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

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

693 "weight" INT4 );

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

695

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

697

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

699

700

701 CREATE TABLE "delegating_interest_snapshot" (

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

703 "snapshot_id" INT8,

704 "issue_id" INT4,

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

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

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

708 "weight" INT4,

709 "scope" "delegation_scope" NOT NULL,

710 "delegate_member_ids" INT4[] NOT NULL );

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

712

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

714

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

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

717 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"';

718

719

720 CREATE TABLE "direct_supporter_snapshot" (

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

722 "snapshot_id" INT8,

723 "issue_id" INT4 NOT NULL,

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

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

726 "initiative_id" INT4,

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

728 "draft_id" INT8 NOT NULL,

729 "informed" BOOLEAN NOT NULL,

730 "satisfied" BOOLEAN NOT NULL,

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

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

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

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

735

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

737

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

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

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

741

742

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

744 DROP INDEX "non_voter_member_id_idx";

745

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

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

748

749

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

751 SELECT

752 "member_id",

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

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

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

756

757

758 DROP TABLE "setting";

759 DROP TABLE "setting_map";

760 DROP TABLE "member_relation_setting";

761 DROP TABLE "unit_setting";

762 DROP TABLE "area_setting";

763 DROP TABLE "initiative_setting";

764 DROP TABLE "suggestion_setting";

765

766

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

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

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

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

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

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

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

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

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

776

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

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

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

780

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

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

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

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

785

786 UPDATE "event" SET

787 "unit_id" = "area"."unit_id",

788 "area_id" = "issue"."area_id",

789 "policy_id" = "issue"."policy_id"

790 FROM "issue", "area"

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

792

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

794 "event" != 'issue_state_changed' OR (

795 "member_id" ISNULL AND

796 "other_member_id" ISNULL AND

797 "scope" ISNULL AND

798 "unit_id" NOTNULL AND

799 "area_id" NOTNULL AND

800 "policy_id" NOTNULL AND

801 "issue_id" NOTNULL AND

802 "state" NOTNULL AND

803 "initiative_id" ISNULL AND

804 "draft_id" ISNULL AND

805 "suggestion_id" ISNULL AND

806 "boolean_value" ISNULL AND

807 "numeric_value" ISNULL AND

808 "text_value" ISNULL AND

809 "old_text_value" ISNULL ));

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

811 "event" NOT IN (

812 'initiative_created_in_new_issue',

813 'initiative_created_in_existing_issue',

814 'initiative_revoked',

815 'new_draft_created'

816 ) OR (

817 "member_id" NOTNULL AND

818 "other_member_id" ISNULL AND

819 "scope" ISNULL AND

820 "unit_id" NOTNULL AND

821 "area_id" NOTNULL AND

822 "policy_id" NOTNULL AND

823 "issue_id" NOTNULL AND

824 "state" NOTNULL AND

825 "initiative_id" NOTNULL AND

826 "draft_id" NOTNULL AND

827 "suggestion_id" ISNULL AND

828 "boolean_value" ISNULL AND

829 "numeric_value" ISNULL AND

830 "text_value" ISNULL AND

831 "old_text_value" ISNULL ));

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

833 "event" != 'suggestion_created' OR (

834 "member_id" NOTNULL AND

835 "other_member_id" ISNULL AND

836 "scope" ISNULL AND

837 "unit_id" NOTNULL AND

838 "area_id" NOTNULL AND

839 "policy_id" NOTNULL AND

840 "issue_id" NOTNULL AND

841 "state" NOTNULL AND

842 "initiative_id" NOTNULL AND

843 "draft_id" ISNULL AND

844 "suggestion_id" NOTNULL AND

845 "boolean_value" ISNULL AND

846 "numeric_value" ISNULL AND

847 "text_value" ISNULL AND

848 "old_text_value" ISNULL ));

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

850 "event" != 'suggestion_deleted' OR (

851 "member_id" ISNULL AND

852 "other_member_id" ISNULL AND

853 "scope" ISNULL AND

854 "unit_id" NOTNULL AND

855 "area_id" NOTNULL AND

856 "policy_id" NOTNULL AND

857 "issue_id" NOTNULL AND

858 "state" NOTNULL AND

859 "initiative_id" NOTNULL AND

860 "draft_id" ISNULL AND

861 "suggestion_id" NOTNULL AND

862 "boolean_value" ISNULL AND

863 "numeric_value" ISNULL AND

864 "text_value" ISNULL AND

865 "old_text_value" ISNULL ));

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

867 "event" NOT IN (

868 'member_activated',

869 'member_deleted',

870 'member_profile_updated',

871 'member_image_updated'

872 ) OR (

873 "member_id" NOTNULL AND

874 "other_member_id" ISNULL AND

875 "scope" ISNULL AND

876 "unit_id" ISNULL AND

877 "area_id" ISNULL AND

878 "policy_id" ISNULL AND

879 "issue_id" ISNULL AND

880 "state" ISNULL AND

881 "initiative_id" ISNULL AND

882 "draft_id" ISNULL AND

883 "suggestion_id" ISNULL AND

884 "boolean_value" ISNULL AND

885 "numeric_value" ISNULL AND

886 "text_value" ISNULL AND

887 "old_text_value" ISNULL ));

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

889 "event" != 'member_active' OR (

890 "member_id" NOTNULL AND

891 "other_member_id" ISNULL AND

892 "scope" ISNULL AND

893 "unit_id" ISNULL AND

894 "area_id" ISNULL AND

895 "policy_id" ISNULL AND

896 "issue_id" ISNULL AND

897 "state" ISNULL AND

898 "initiative_id" ISNULL AND

899 "draft_id" ISNULL AND

900 "suggestion_id" ISNULL AND

901 "boolean_value" NOTNULL AND

902 "numeric_value" ISNULL AND

903 "text_value" ISNULL AND

904 "old_text_value" ISNULL ));

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

906 "event" != 'member_name_updated' OR (

907 "member_id" NOTNULL AND

908 "other_member_id" ISNULL AND

909 "scope" ISNULL AND

910 "unit_id" ISNULL AND

911 "area_id" ISNULL AND

912 "policy_id" ISNULL AND

913 "issue_id" ISNULL AND

914 "state" ISNULL AND

915 "initiative_id" ISNULL AND

916 "draft_id" ISNULL AND

917 "suggestion_id" ISNULL AND

918 "boolean_value" ISNULL AND

919 "numeric_value" ISNULL AND

920 "text_value" NOTNULL AND

921 "old_text_value" NOTNULL ));

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

923 "event" != 'interest' OR (

924 "member_id" NOTNULL AND

925 "other_member_id" ISNULL AND

926 "scope" ISNULL AND

927 "unit_id" NOTNULL AND

928 "area_id" NOTNULL AND

929 "policy_id" NOTNULL AND

930 "issue_id" NOTNULL AND

931 "state" NOTNULL AND

932 "initiative_id" ISNULL AND

933 "draft_id" ISNULL AND

934 "suggestion_id" ISNULL AND

935 "boolean_value" NOTNULL AND

936 "numeric_value" ISNULL AND

937 "text_value" ISNULL AND

938 "old_text_value" ISNULL ));

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

940 "event" != 'initiator' OR (

941 "member_id" NOTNULL AND

942 "other_member_id" ISNULL AND

943 "scope" ISNULL AND

944 "unit_id" NOTNULL AND

945 "area_id" NOTNULL AND

946 "policy_id" NOTNULL AND

947 "issue_id" NOTNULL AND

948 "state" NOTNULL AND

949 "initiative_id" NOTNULL AND

950 "draft_id" ISNULL AND

951 "suggestion_id" ISNULL AND

952 "boolean_value" NOTNULL AND

953 "numeric_value" ISNULL AND

954 "text_value" ISNULL AND

955 "old_text_value" ISNULL ));

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

957 "event" != 'support' OR (

958 "member_id" NOTNULL AND

959 "other_member_id" ISNULL AND

960 "scope" ISNULL AND

961 "unit_id" NOTNULL AND

962 "area_id" NOTNULL AND

963 "policy_id" NOTNULL AND

964 "issue_id" NOTNULL AND

965 "state" NOTNULL AND

966 "initiative_id" NOTNULL AND

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

968 "suggestion_id" ISNULL AND

969 "boolean_value" NOTNULL AND

970 "numeric_value" ISNULL AND

971 "text_value" ISNULL AND

972 "old_text_value" ISNULL ));

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

974 "event" != 'support_updated' OR (

975 "member_id" NOTNULL AND

976 "other_member_id" ISNULL AND

977 "scope" ISNULL AND

978 "unit_id" NOTNULL AND

979 "area_id" NOTNULL AND

980 "policy_id" NOTNULL AND

981 "issue_id" NOTNULL AND

982 "state" NOTNULL AND

983 "initiative_id" NOTNULL AND

984 "draft_id" NOTNULL AND

985 "suggestion_id" ISNULL AND

986 "boolean_value" ISNULL AND

987 "numeric_value" ISNULL AND

988 "text_value" ISNULL AND

989 "old_text_value" ISNULL ));

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

991 "event" != 'suggestion_rated' OR (

992 "member_id" NOTNULL AND

993 "other_member_id" ISNULL AND

994 "scope" ISNULL AND

995 "unit_id" NOTNULL AND

996 "area_id" NOTNULL AND

997 "policy_id" NOTNULL AND

998 "issue_id" NOTNULL AND

999 "state" NOTNULL AND

1000 "initiative_id" NOTNULL AND

1001 "draft_id" ISNULL AND

1002 "suggestion_id" NOTNULL AND

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

1004 "numeric_value" NOTNULL AND

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

1006 "text_value" ISNULL AND

1007 "old_text_value" ISNULL ));

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

1009 "event" != 'delegation' OR (

1010 "member_id" NOTNULL AND

1011 (("other_member_id" ISNULL) OR ("boolean_value" = TRUE)) AND

1012 "scope" NOTNULL AND

1013 "unit_id" NOTNULL AND

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

1015 "policy_id" ISNULL AND

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

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

1018 "initiative_id" ISNULL AND

1019 "draft_id" ISNULL AND

1020 "suggestion_id" ISNULL AND

1021 "boolean_value" NOTNULL AND

1022 "numeric_value" ISNULL AND

1023 "text_value" ISNULL AND

1024 "old_text_value" ISNULL ));

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

1026 "event" != 'contact' OR (

1027 "member_id" NOTNULL AND

1028 "other_member_id" NOTNULL AND

1029 "scope" ISNULL AND

1030 "unit_id" ISNULL AND

1031 "area_id" ISNULL AND

1032 "policy_id" ISNULL AND

1033 "issue_id" ISNULL AND

1034 "state" ISNULL AND

1035 "initiative_id" ISNULL AND

1036 "draft_id" ISNULL AND

1037 "suggestion_id" ISNULL AND

1038 "boolean_value" NOTNULL AND

1039 "numeric_value" ISNULL AND

1040 "text_value" ISNULL AND

1041 "old_text_value" ISNULL ));

1042

1043

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

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

1046

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

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

1049

1050

1051 CREATE FUNCTION "write_event_unit_trigger"()

1052 RETURNS TRIGGER

1053 LANGUAGE 'plpgsql' VOLATILE AS $$

1054 DECLARE

1055 "event_v" "event_type";

1056 BEGIN

1057 IF TG_OP = 'UPDATE' THEN

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

1059 RETURN NULL;

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

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

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

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

1064 ELSIF OLD != NEW THEN

1065 "event_v" := 'unit_updated';

1066 ELSE

1067 RETURN NULL;

1068 END IF;

1069 ELSE

1070 "event_v" := 'unit_created';

1071 END IF;

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

1073 RETURN NULL;

1074 END;

1075 $$;

1076

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

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

1079

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

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

1082

1083

1084 CREATE FUNCTION "write_event_area_trigger"()

1085 RETURNS TRIGGER

1086 LANGUAGE 'plpgsql' VOLATILE AS $$

1087 DECLARE

1088 "event_v" "event_type";

1089 BEGIN

1090 IF TG_OP = 'UPDATE' THEN

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

1092 RETURN NULL;

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

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

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

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

1097 ELSIF OLD != NEW THEN

1098 "event_v" := 'area_updated';

1099 ELSE

1100 RETURN NULL;

1101 END IF;

1102 ELSE

1103 "event_v" := 'area_created';

1104 END IF;

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

1106 RETURN NULL;

1107 END;

1108 $$;

1109

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

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

1112

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

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

1115

1116

1117 CREATE FUNCTION "write_event_policy_trigger"()

1118 RETURNS TRIGGER

1119 LANGUAGE 'plpgsql' VOLATILE AS $$

1120 DECLARE

1121 "event_v" "event_type";

1122 BEGIN

1123 IF TG_OP = 'UPDATE' THEN

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

1125 RETURN NULL;

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

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

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

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

1130 ELSIF OLD != NEW THEN

1131 "event_v" := 'policy_updated';

1132 ELSE

1133 RETURN NULL;

1134 END IF;

1135 ELSE

1136 "event_v" := 'policy_created';

1137 END IF;

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

1139 RETURN NULL;

1140 END;

1141 $$;

1142

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

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

1145

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

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

1148

1149

1150 CREATE OR REPLACE FUNCTION "write_event_issue_state_changed_trigger"()

1151 RETURNS TRIGGER

1152 LANGUAGE 'plpgsql' VOLATILE AS $$

1153 DECLARE

1154 "area_row" "area"%ROWTYPE;

1155 BEGIN

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

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

1158 FOR SHARE;

1159 INSERT INTO "event" (

1160 "event",

1161 "unit_id", "area_id", "policy_id", "issue_id", "state"

1162 ) VALUES (

1163 'issue_state_changed',

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

1165 NEW."id", NEW."state"

1166 );

1167 END IF;

1168 RETURN NULL;

1169 END;

1170 $$;

1171

1172

1173 CREATE OR REPLACE FUNCTION "write_event_initiative_or_draft_created_trigger"()

1174 RETURNS TRIGGER

1175 LANGUAGE 'plpgsql' VOLATILE AS $$

1176 DECLARE

1177 "initiative_row" "initiative"%ROWTYPE;

1178 "issue_row" "issue"%ROWTYPE;

1179 "area_row" "area"%ROWTYPE;

1180 "event_v" "event_type";

1181 BEGIN

1182 SELECT * INTO "initiative_row" FROM "initiative"

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

1184 SELECT * INTO "issue_row" FROM "issue"

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

1186 SELECT * INTO "area_row" FROM "area"

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

1188 IF EXISTS (

1189 SELECT NULL FROM "draft"

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

1191 FOR SHARE

1192 ) THEN

1193 "event_v" := 'new_draft_created';

1194 ELSE

1195 IF EXISTS (

1196 SELECT NULL FROM "initiative"

1197 WHERE "issue_id" = "initiative_row"."issue_id"

1198 AND "id" != "initiative_row"."id"

1199 FOR SHARE

1200 ) THEN

1201 "event_v" := 'initiative_created_in_existing_issue';

1202 ELSE

1203 "event_v" := 'initiative_created_in_new_issue';

1204 END IF;

1205 END IF;

1206 INSERT INTO "event" (

1207 "event", "member_id",

1208 "unit_id", "area_id", "policy_id", "issue_id", "state",

1209 "initiative_id", "draft_id"

1210 ) VALUES (

1211 "event_v", NEW."author_id",

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

1213 "initiative_row"."issue_id", "issue_row"."state",

1214 NEW."initiative_id", NEW."id"

1215 );

1216 RETURN NULL;

1217 END;

1218 $$;

1219

1220

1221 CREATE OR REPLACE FUNCTION "write_event_initiative_revoked_trigger"()

1222 RETURNS TRIGGER

1223 LANGUAGE 'plpgsql' VOLATILE AS $$

1224 DECLARE

1225 "issue_row" "issue"%ROWTYPE;

1226 "area_row" "area"%ROWTYPE;

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

1228 BEGIN

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

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

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

1232 SELECT * INTO "issue_row" FROM "issue"

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

1234 SELECT * INTO "area_row" FROM "area"

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

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

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

1238 SELECT "id" INTO "draft_id_v" FROM "current_draft"

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

1240 INSERT INTO "event" (

1241 "event", "member_id",

1242 "unit_id", "area_id", "policy_id", "issue_id", "state",

1243 "initiative_id", "draft_id"

1244 ) VALUES (

1245 'initiative_revoked', NEW."revoked_by_member_id",

1246 "area_row"."unit_id", "issue_row"."area_id",

1247 "issue_row"."policy_id",

1248 NEW."issue_id", "issue_row"."state",

1249 NEW."id", "draft_id_v"

1250 );

1251 END IF;

1252 RETURN NULL;

1253 END;

1254 $$;

1255

1256

1257 CREATE OR REPLACE FUNCTION "write_event_suggestion_created_trigger"()

1258 RETURNS TRIGGER

1259 LANGUAGE 'plpgsql' VOLATILE AS $$

1260 DECLARE

1261 "initiative_row" "initiative"%ROWTYPE;

1262 "issue_row" "issue"%ROWTYPE;

1263 "area_row" "area"%ROWTYPE;

1264 BEGIN

1265 SELECT * INTO "initiative_row" FROM "initiative"

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

1267 SELECT * INTO "issue_row" FROM "issue"

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

1269 SELECT * INTO "area_row" FROM "area"

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

1271 INSERT INTO "event" (

1272 "event", "member_id",

1273 "unit_id", "area_id", "policy_id", "issue_id", "state",

1274 "initiative_id", "suggestion_id"

1275 ) VALUES (

1276 'suggestion_created', NEW."author_id",

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

1278 "initiative_row"."issue_id", "issue_row"."state",

1279 NEW."initiative_id", NEW."id"

1280 );

1281 RETURN NULL;

1282 END;

1283 $$;

1284

1285

1286 CREATE FUNCTION "write_event_suggestion_removed_trigger"()

1287 RETURNS TRIGGER

1288 LANGUAGE 'plpgsql' VOLATILE AS $$

1289 DECLARE

1290 "initiative_row" "initiative"%ROWTYPE;

1291 "issue_row" "issue"%ROWTYPE;

1292 "area_row" "area"%ROWTYPE;

1293 BEGIN

1294 SELECT * INTO "initiative_row" FROM "initiative"

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

1296 IF "initiative_row"."id" NOTNULL THEN

1297 SELECT * INTO "issue_row" FROM "issue"

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

1299 SELECT * INTO "area_row" FROM "area"

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

1301 INSERT INTO "event" (

1302 "event",

1303 "unit_id", "area_id", "policy_id", "issue_id", "state",

1304 "initiative_id", "suggestion_id"

1305 ) VALUES (

1306 'suggestion_deleted',

1307 "area_row"."unit_id", "issue_row"."area_id",

1308 "issue_row"."policy_id",

1309 "initiative_row"."issue_id", "issue_row"."state",

1310 OLD."initiative_id", OLD."id"

1311 );

1312 END IF;

1313 RETURN NULL;

1314 END;

1315 $$;

1316

1317 CREATE TRIGGER "write_event_suggestion_removed"

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

1319 "write_event_suggestion_removed_trigger"();

1320

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

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

1323

1324

1325 CREATE FUNCTION "write_event_member_trigger"()

1326 RETURNS TRIGGER

1327 LANGUAGE 'plpgsql' VOLATILE AS $$

1328 BEGIN

1329 IF TG_OP = 'INSERT' THEN

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

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

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

1333 END IF;

1334 IF NEW."active" THEN

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

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

1337 END IF;

1338 ELSIF TG_OP = 'UPDATE' THEN

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

1340 RAISE EXCEPTION 'Cannot change member ID';

1341 END IF;

1342 IF

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

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

1345 THEN

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

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

1348 END IF;

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

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

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

1352 );

1353 END IF;

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

1355 INSERT INTO "event" (

1356 "event", "member_id", "text_value", "old_text_value"

1357 ) VALUES (

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

1359 );

1360 END IF;

1361 IF

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

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

1364 THEN

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

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

1367 END IF;

1368 END IF;

1369 RETURN NULL;

1370 END;

1371 $$;

1372

1373 CREATE TRIGGER "write_event_member"

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

1375 "write_event_member_trigger"();

1376

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

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

1379

1380

1381 CREATE FUNCTION "write_event_member_profile_updated_trigger"()

1382 RETURNS TRIGGER

1383 LANGUAGE 'plpgsql' VOLATILE AS $$

1384 BEGIN

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

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

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

1388 'member_profile_updated', OLD."member_id"

1389 );

1390 END IF;

1391 END IF;

1392 IF TG_OP = 'UPDATE' THEN

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

1394 RETURN NULL;

1395 END IF;

1396 END IF;

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

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

1399 'member_profile_updated', NEW."member_id"

1400 );

1401 END IF;

1402 RETURN NULL;

1403 END;

1404 $$;

1405

1406 CREATE TRIGGER "write_event_member_profile_updated"

1407 AFTER INSERT OR UPDATE OR DELETE ON "member_profile"

1408 FOR EACH ROW EXECUTE PROCEDURE

1409 "write_event_member_profile_updated_trigger"();

1410

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

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

1413

1414

1415 CREATE FUNCTION "write_event_member_image_updated_trigger"()

1416 RETURNS TRIGGER

1417 LANGUAGE 'plpgsql' VOLATILE AS $$

1418 BEGIN

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

1420 IF NOT OLD."scaled" THEN

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

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

1423 'member_image_updated', OLD."member_id"

1424 );

1425 END IF;

1426 END IF;

1427 END IF;

1428 IF TG_OP = 'UPDATE' THEN

1429 IF

1430 OLD."member_id" = NEW."member_id" AND

1431 OLD."scaled" = NEW."scaled"

1432 THEN

1433 RETURN NULL;

1434 END IF;

1435 END IF;

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

1437 IF NOT NEW."scaled" THEN

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

1439 'member_image_updated', NEW."member_id"

1440 );

1441 END IF;

1442 END IF;

1443 RETURN NULL;

1444 END;

1445 $$;

1446

1447 CREATE TRIGGER "write_event_member_image_updated"

1448 AFTER INSERT OR UPDATE OR DELETE ON "member_image"

1449 FOR EACH ROW EXECUTE PROCEDURE

1450 "write_event_member_image_updated_trigger"();

1451

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

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

1454

1455

1456 CREATE FUNCTION "write_event_interest_trigger"()

1457 RETURNS TRIGGER

1458 LANGUAGE 'plpgsql' VOLATILE AS $$

1459 DECLARE

1460 "issue_row" "issue"%ROWTYPE;

1461 "area_row" "area"%ROWTYPE;

1462 BEGIN

1463 IF TG_OP = 'UPDATE' THEN

1464 IF OLD = NEW THEN

1465 RETURN NULL;

1466 END IF;

1467 END IF;

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

1469 SELECT * INTO "issue_row" FROM "issue"

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

1471 SELECT * INTO "area_row" FROM "area"

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

1473 IF "issue_row"."id" NOTNULL THEN

1474 INSERT INTO "event" (

1475 "event", "member_id",

1476 "unit_id", "area_id", "policy_id", "issue_id", "state",

1477 "boolean_value"

1478 ) VALUES (

1479 'interest', OLD."member_id",

1480 "area_row"."unit_id", "issue_row"."area_id",

1481 "issue_row"."policy_id",

1482 OLD."issue_id", "issue_row"."state",

1483 FALSE

1484 );

1485 END IF;

1486 END IF;

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

1488 SELECT * INTO "issue_row" FROM "issue"

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

1490 SELECT * INTO "area_row" FROM "area"

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

1492 INSERT INTO "event" (

1493 "event", "member_id",

1494 "unit_id", "area_id", "policy_id", "issue_id", "state",

1495 "boolean_value"

1496 ) VALUES (

1497 'interest', NEW."member_id",

1498 "area_row"."unit_id", "issue_row"."area_id",

1499 "issue_row"."policy_id",

1500 NEW."issue_id", "issue_row"."state",

1501 TRUE

1502 );

1503 END IF;

1504 RETURN NULL;

1505 END;

1506 $$;

1507

1508 CREATE TRIGGER "write_event_interest"

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

1510 "write_event_interest_trigger"();

1511

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

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

1514

1515

1516 CREATE FUNCTION "write_event_initiator_trigger"()

1517 RETURNS TRIGGER

1518 LANGUAGE 'plpgsql' VOLATILE AS $$

1519 DECLARE

1520 "initiative_row" "initiative"%ROWTYPE;

1521 "issue_row" "issue"%ROWTYPE;

1522 "area_row" "area"%ROWTYPE;

1523 "accepted_v" BOOLEAN = FALSE;

1524 "rejected_v" BOOLEAN = FALSE;

1525 BEGIN

1526 IF TG_OP = 'UPDATE' THEN

1527 IF

1528 OLD."initiative_id" = NEW."initiative_id" AND

1529 OLD."member_id" = NEW."member_id"

1530 THEN

1531 IF

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

1533 THEN

1534 RETURN NULL;

1535 END IF;

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

1537 "accepted_v" := TRUE;

1538 ELSE

1539 "rejected_v" := TRUE;

1540 END IF;

1541 END IF;

1542 END IF;

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

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

1545 SELECT * INTO "initiative_row" FROM "initiative"

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

1547 IF "initiative_row"."id" NOTNULL THEN

1548 SELECT * INTO "issue_row" FROM "issue"

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

1550 SELECT * INTO "area_row" FROM "area"

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

1552 INSERT INTO "event" (

1553 "event", "member_id",

1554 "unit_id", "area_id", "policy_id", "issue_id", "state",

1555 "initiative_id", "boolean_value"

1556 ) VALUES (

1557 'initiator', OLD."member_id",

1558 "area_row"."unit_id", "issue_row"."area_id",

1559 "issue_row"."policy_id",

1560 "issue_row"."id", "issue_row"."state",

1561 OLD."initiative_id", FALSE

1562 );

1563 END IF;

1564 END IF;

1565 END IF;

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

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

1568 SELECT * INTO "initiative_row" FROM "initiative"

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

1570 SELECT * INTO "issue_row" FROM "issue"

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

1572 SELECT * INTO "area_row" FROM "area"

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

1574 INSERT INTO "event" (

1575 "event", "member_id",

1576 "unit_id", "area_id", "policy_id", "issue_id", "state",

1577 "initiative_id", "boolean_value"

1578 ) VALUES (

1579 'initiator', NEW."member_id",

1580 "area_row"."unit_id", "issue_row"."area_id",

1581 "issue_row"."policy_id",

1582 "issue_row"."id", "issue_row"."state",

1583 NEW."initiative_id", TRUE

1584 );

1585 END IF;

1586 END IF;

1587 RETURN NULL;

1588 END;

1589 $$;

1590

1591 CREATE TRIGGER "write_event_initiator"

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

1593 "write_event_initiator_trigger"();

1594

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

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

1597

1598

1599 CREATE FUNCTION "write_event_support_trigger"()

1600 RETURNS TRIGGER

1601 LANGUAGE 'plpgsql' VOLATILE AS $$

1602 DECLARE

1603 "issue_row" "issue"%ROWTYPE;

1604 "area_row" "area"%ROWTYPE;

1605 BEGIN

1606 IF TG_OP = 'UPDATE' THEN

1607 IF

1608 OLD."initiative_id" = NEW."initiative_id" AND

1609 OLD."member_id" = NEW."member_id"

1610 THEN

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

1612 SELECT * INTO "issue_row" FROM "issue"

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

1614 SELECT * INTO "area_row" FROM "area"

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

1616 INSERT INTO "event" (

1617 "event", "member_id",

1618 "unit_id", "area_id", "policy_id", "issue_id", "state",

1619 "initiative_id", "draft_id"

1620 ) VALUES (

1621 'support_updated', NEW."member_id",

1622 "area_row"."unit_id", "issue_row"."area_id",

1623 "issue_row"."policy_id",

1624 "issue_row"."id", "issue_row"."state",

1625 NEW."initiative_id", NEW."draft_id"

1626 );

1627 END IF;

1628 RETURN NULL;

1629 END IF;

1630 END IF;

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

1632 IF EXISTS (

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

1634 FOR SHARE

1635 ) THEN

1636 SELECT * INTO "issue_row" FROM "issue"

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

1638 SELECT * INTO "area_row" FROM "area"

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

1640 INSERT INTO "event" (

1641 "event", "member_id",

1642 "unit_id", "area_id", "policy_id", "issue_id", "state",

1643 "initiative_id", "boolean_value"

1644 ) VALUES (

1645 'support', OLD."member_id",

1646 "area_row"."unit_id", "issue_row"."area_id",

1647 "issue_row"."policy_id",

1648 "issue_row"."id", "issue_row"."state",

1649 OLD."initiative_id", FALSE

1650 );

1651 END IF;

1652 END IF;

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

1654 SELECT * INTO "issue_row" FROM "issue"

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

1656 SELECT * INTO "area_row" FROM "area"

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

1658 INSERT INTO "event" (

1659 "event", "member_id",

1660 "unit_id", "area_id", "policy_id", "issue_id", "state",

1661 "initiative_id", "draft_id", "boolean_value"

1662 ) VALUES (

1663 'support', NEW."member_id",

1664 "area_row"."unit_id", "issue_row"."area_id",

1665 "issue_row"."policy_id",

1666 "issue_row"."id", "issue_row"."state",

1667 NEW."initiative_id", NEW."draft_id", TRUE

1668 );

1669 END IF;

1670 RETURN NULL;

1671 END;

1672 $$;

1673

1674 CREATE TRIGGER "write_event_support"

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

1676 "write_event_support_trigger"();

1677

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

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

1680

1681

1682 CREATE FUNCTION "write_event_suggestion_rated_trigger"()

1683 RETURNS TRIGGER

1684 LANGUAGE 'plpgsql' VOLATILE AS $$

1685 DECLARE

1686 "same_pkey_v" BOOLEAN = FALSE;

1687 "initiative_row" "initiative"%ROWTYPE;

1688 "issue_row" "issue"%ROWTYPE;

1689 "area_row" "area"%ROWTYPE;

1690 BEGIN

1691 IF TG_OP = 'UPDATE' THEN

1692 IF

1693 OLD."suggestion_id" = NEW."suggestion_id" AND

1694 OLD."member_id" = NEW."member_id"

1695 THEN

1696 IF

1697 OLD."degree" = NEW."degree" AND

1698 OLD."fulfilled" = NEW."fulfilled"

1699 THEN

1700 RETURN NULL;

1701 END IF;

1702 "same_pkey_v" := TRUE;

1703 END IF;

1704 END IF;

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

1706 IF EXISTS (

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

1708 FOR SHARE

1709 ) THEN

1710 SELECT * INTO "initiative_row" FROM "initiative"

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

1712 SELECT * INTO "issue_row" FROM "issue"

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

1714 SELECT * INTO "area_row" FROM "area"

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

1716 INSERT INTO "event" (

1717 "event", "member_id",

1718 "unit_id", "area_id", "policy_id", "issue_id", "state",

1719 "initiative_id", "suggestion_id",

1720 "boolean_value", "numeric_value"

1721 ) VALUES (

1722 'suggestion_rated', OLD."member_id",

1723 "area_row"."unit_id", "issue_row"."area_id",

1724 "issue_row"."policy_id",

1725 "initiative_row"."issue_id", "issue_row"."state",

1726 OLD."initiative_id", OLD."suggestion_id",

1727 NULL, 0

1728 );

1729 END IF;

1730 END IF;

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

1732 SELECT * INTO "initiative_row" FROM "initiative"

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

1734 SELECT * INTO "issue_row" FROM "issue"

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

1736 SELECT * INTO "area_row" FROM "area"

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

1738 INSERT INTO "event" (

1739 "event", "member_id",

1740 "unit_id", "area_id", "policy_id", "issue_id", "state",

1741 "initiative_id", "suggestion_id",

1742 "boolean_value", "numeric_value"

1743 ) VALUES (

1744 'suggestion_rated', NEW."member_id",

1745 "area_row"."unit_id", "issue_row"."area_id",

1746 "issue_row"."policy_id",

1747 "initiative_row"."issue_id", "issue_row"."state",

1748 NEW."initiative_id", NEW."suggestion_id",

1749 NEW."fulfilled", NEW."degree"

1750 );

1751 END IF;

1752 RETURN NULL;

1753 END;

1754 $$;

1755

1756 CREATE TRIGGER "write_event_suggestion_rated"

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

1758 "write_event_suggestion_rated_trigger"();

1759

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

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

1762

1763

1764 CREATE FUNCTION "write_event_delegation_trigger"()

1765 RETURNS TRIGGER

1766 LANGUAGE 'plpgsql' VOLATILE AS $$

1767 DECLARE

1768 "issue_row" "issue"%ROWTYPE;

1769 "area_row" "area"%ROWTYPE;

1770 BEGIN

1771 IF TG_OP = 'DELETE' THEN

1772 IF EXISTS (

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

1774 ) AND (CASE OLD."scope"

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

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

1777 )

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

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

1780 )

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

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

1783 )

1784 END) THEN

1785 SELECT * INTO "issue_row" FROM "issue"

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

1787 SELECT * INTO "area_row" FROM "area"

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

1789 FOR SHARE;

1790 INSERT INTO "event" (

1791 "event", "member_id", "scope",

1792 "unit_id", "area_id", "issue_id", "state",

1793 "boolean_value"

1794 ) VALUES (

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

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

1797 OLD."issue_id", "issue_row"."state",

1798 FALSE

1799 );

1800 END IF;

1801 ELSE

1802 SELECT * INTO "issue_row" FROM "issue"

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

1804 SELECT * INTO "area_row" FROM "area"

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

1806 FOR SHARE;

1807 INSERT INTO "event" (

1808 "event", "member_id", "other_member_id", "scope",

1809 "unit_id", "area_id", "issue_id", "state",

1810 "boolean_value"

1811 ) VALUES (

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

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

1814 NEW."issue_id", "issue_row"."state",

1815 TRUE

1816 );

1817 END IF;

1818 RETURN NULL;

1819 END;

1820 $$;

1821

1822 CREATE TRIGGER "write_event_delegation"

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

1824 "write_event_delegation_trigger"();

1825

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

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

1828

1829

1830 CREATE FUNCTION "write_event_contact_trigger"()

1831 RETURNS TRIGGER

1832 LANGUAGE 'plpgsql' VOLATILE AS $$

1833 BEGIN

1834 IF TG_OP = 'UPDATE' THEN

1835 IF

1836 OLD."member_id" = NEW."member_id" AND

1837 OLD."other_member_id" = NEW."other_member_id" AND

1838 OLD."public" = NEW."public"

1839 THEN

1840 RETURN NULL;

1841 END IF;

1842 END IF;

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

1844 IF OLD."public" THEN

1845 IF EXISTS (

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

1847 FOR SHARE

1848 ) AND EXISTS (

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

1850 FOR SHARE

1851 ) THEN

1852 INSERT INTO "event" (

1853 "event", "member_id", "other_member_id", "boolean_value"

1854 ) VALUES (

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

1856 );

1857 END IF;

1858 END IF;

1859 END IF;

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

1861 IF NEW."public" THEN

1862 INSERT INTO "event" (

1863 "event", "member_id", "other_member_id", "boolean_value"

1864 ) VALUES (

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

1866 );

1867 END IF;

1868 END IF;

1869 RETURN NULL;

1870 END;

1871 $$;

1872

1873 CREATE TRIGGER "write_event_contact"

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

1875 "write_event_contact_trigger"();

1876

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

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

1879

1880

1881 CREATE FUNCTION "send_event_notify_trigger"()

1882 RETURNS TRIGGER

1883 LANGUAGE 'plpgsql' VOLATILE AS $$

1884 BEGIN

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

1886 RETURN NULL;

1887 END;

1888 $$;

1889

1890 CREATE TRIGGER "send_notify"

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

1892 "send_event_notify_trigger"();

1893

1894

1895 CREATE FUNCTION "delete_extended_scope_tokens_trigger"()

1896 RETURNS TRIGGER

1897 LANGUAGE 'plpgsql' VOLATILE AS $$

1898 DECLARE

1899 "system_application_row" "system_application"%ROWTYPE;

1900 BEGIN

1901 IF OLD."system_application_id" NOTNULL THEN

1902 SELECT * FROM "system_application" INTO "system_application_row"

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

1904 DELETE FROM "token"

1905 WHERE "member_id" = OLD."member_id"

1906 AND "system_application_id" = OLD."system_application_id"

1907 AND NOT COALESCE(

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

1909 regexp_split_to_array(

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

1911 ),

1912 FALSE

1913 );

1914 END IF;

1915 RETURN OLD;

1916 END;

1917 $$;

1918

1919 CREATE TRIGGER "delete_extended_scope_tokens"

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

1921 "delete_extended_scope_tokens_trigger"();

1922

1923

1924 CREATE FUNCTION "detach_token_from_session_trigger"()

1925 RETURNS TRIGGER

1926 LANGUAGE 'plpgsql' VOLATILE AS $$

1927 BEGIN

1928 UPDATE "token" SET "session_id" = NULL

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

1930 RETURN OLD;

1931 END;

1932 $$;

1933

1934 CREATE TRIGGER "detach_token_from_session"

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

1936 "detach_token_from_session_trigger"();

1937

1938

1939 CREATE FUNCTION "delete_non_detached_scope_with_session_trigger"()

1940 RETURNS TRIGGER

1941 LANGUAGE 'plpgsql' VOLATILE AS $$

1942 BEGIN

1943 IF NEW."session_id" ISNULL THEN

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

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

1946 WHERE "element" LIKE '%_detached';

1947 END IF;

1948 RETURN NEW;

1949 END;

1950 $$;

1951

1952 CREATE TRIGGER "delete_non_detached_scope_with_session"

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

1954 "delete_non_detached_scope_with_session_trigger"();

1955

1956

1957 CREATE FUNCTION "delete_token_with_empty_scope_trigger"()

1958 RETURNS TRIGGER

1959 LANGUAGE 'plpgsql' VOLATILE AS $$

1960 BEGIN

1961 IF NEW."scope" = '' THEN

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

1963 END IF;

1964 RETURN NULL;

1965 END;

1966 $$;

1967

1968 CREATE TRIGGER "delete_token_with_empty_scope"

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

1970 "delete_token_with_empty_scope_trigger"();

1971

1972

1973 CREATE FUNCTION "delete_snapshot_on_partial_delete_trigger"()

1974 RETURNS TRIGGER

1975 LANGUAGE 'plpgsql' VOLATILE AS $$

1976 BEGIN

1977 IF TG_OP = 'UPDATE' THEN

1978 IF

1979 OLD."snapshot_id" = NEW."snapshot_id" AND

1980 OLD."issue_id" = NEW."issue_id"

1981 THEN

1982 RETURN NULL;

1983 END IF;

1984 END IF;

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

1986 RETURN NULL;

1987 END;

1988 $$;

1989

1990 CREATE TRIGGER "delete_snapshot_on_partial_delete"

1991 AFTER UPDATE OR DELETE ON "snapshot_issue"

1992 FOR EACH ROW EXECUTE PROCEDURE

1993 "delete_snapshot_on_partial_delete_trigger"();

1994

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

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

1997

1998

1999 CREATE FUNCTION "copy_current_draft_data"

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

2001 RETURNS VOID

2002 LANGUAGE 'plpgsql' VOLATILE AS $$

2003 BEGIN

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

2005 FOR UPDATE;

2006 UPDATE "initiative" SET

2007 "location" = "draft"."location",

2008 "draft_text_search_data" = "draft"."text_search_data"

2009 FROM "current_draft" AS "draft"

2010 WHERE "initiative"."id" = "initiative_id_p"

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

2012 END;

2013 $$;

2014

2015 COMMENT ON FUNCTION "copy_current_draft_data"

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

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

2018

2019

2020 CREATE FUNCTION "copy_current_draft_data_trigger"()

2021 RETURNS TRIGGER

2022 LANGUAGE 'plpgsql' VOLATILE AS $$

2023 BEGIN

2024 IF TG_OP='DELETE' THEN

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

2026 ELSE

2027 IF TG_OP='UPDATE' THEN

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

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

2030 END IF;

2031 END IF;

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

2033 END IF;

2034 RETURN NULL;

2035 END;

2036 $$;

2037

2038 CREATE TRIGGER "copy_current_draft_data"

2039 AFTER INSERT OR UPDATE OR DELETE ON "draft"

2040 FOR EACH ROW EXECUTE PROCEDURE

2041 "copy_current_draft_data_trigger"();

2042

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

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

2045

2046

2047 CREATE VIEW "area_quorum" AS

2048 SELECT

2049 "area"."id" AS "area_id",

2050 ceil(

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

2052 coalesce(

2053 ( SELECT sum(

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

2055 extract(epoch from

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

2057 "issue"."discussion_time" +

2058 "issue"."verification_time" +

2059 "issue"."voting_time"

2060 )::FLOAT8

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

2062 )

2063 FROM "issue" JOIN "policy"

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

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

2066 AND "issue"."accepted" NOTNULL

2067 AND "issue"."closed" ISNULL

2068 AND "policy"."polling" = FALSE

2069 )::FLOAT8, 0::FLOAT8

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

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

2072 SELECT "snapshot"."population"

2073 FROM "snapshot"

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

2075 AND "snapshot"."issue_id" ISNULL

2076 ORDER BY "snapshot"."id" DESC

2077 LIMIT 1

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

2079

2080 )::INT4 AS "issue_quorum"

2081 FROM "area";

2082

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

2084

2085

2086 CREATE VIEW "issue_quorum" AS

2087 SELECT DISTINCT ON ("issue_id")

2088 "issue"."id" AS "issue_id",

2089 "subquery"."issue_quorum"

2090 FROM "issue"

2091 CROSS JOIN LATERAL (

2092 SELECT "area_quorum"."issue_quorum"

2093 FROM "area_quorum" WHERE "area_quorum"."area_id" = "issue"."area_id"

2094 UNION ALL

2095 SELECT "policy"."issue_quorum"

2096 FROM "policy" WHERE "policy"."id" = "issue"."policy_id"

2097 UNION ALL

2098 SELECT

2099 ceil(

2100 ("issue"."population"::INT8 * "policy"."issue_quorum_num"::INT8) /

2101 "policy"."issue_quorum_den"::FLOAT8

2102 )::INT4

2103 FROM "policy" WHERE "policy"."id" = "issue"."policy_id"

2104 ) AS "subquery"

2105 ORDER BY "issue_id", "issue_quorum" DESC;

2106

2107 COMMENT ON VIEW "issue_quorum" IS 'Effective quorum for issue admission';

2108

2109

2110 CREATE VIEW "area_with_unaccepted_issues" AS

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

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

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

2114

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

2116

2117

2118 DROP VIEW "area_member_count";

2119

2120

2121 DROP TABLE "membership";

2122

2123

2124 DROP FUNCTION "membership_weight"

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

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

2127

2128

2129 DROP FUNCTION "membership_weight_with_skipping"

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

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

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

2133

2134

2135 CREATE OR REPLACE VIEW "issue_delegation" AS

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

2137 "issue"."id" AS "issue_id",

2138 "delegation"."id",

2139 "delegation"."truster_id",

2140 "delegation"."trustee_id",

2141 "delegation"."scope"

2142 FROM "issue"

2143 JOIN "area"

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

2145 JOIN "delegation"

2146 ON "delegation"."unit_id" = "area"."unit_id"

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

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

2149 JOIN "member"

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

2151 JOIN "privilege"

2152 ON "area"."unit_id" = "privilege"."unit_id"

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

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

2155 ORDER BY

2156 "issue"."id",

2157 "delegation"."truster_id",

2158 "delegation"."scope" DESC;

2159

2160

2161 CREATE VIEW "unit_member" AS

2162 SELECT

2163 "unit"."id" AS "unit_id",

2164 "member"."id" AS "member_id"

2165 FROM "privilege"

2166 JOIN "unit" ON "unit"."id" = "privilege"."unit_id"

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

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

2169

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

2171

2172

2173 CREATE OR REPLACE VIEW "unit_member_count" AS

2174 SELECT

2175 "unit"."id" AS "unit_id",

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

2177 FROM "unit" LEFT JOIN "unit_member"

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

2179 GROUP BY "unit"."id";

2180

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

2182

2183

2184 CREATE OR REPLACE VIEW "opening_draft" AS

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

2186 ORDER BY "initiative_id", "id";

2187

2188

2189 CREATE OR REPLACE VIEW "current_draft" AS

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

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

2192

2193

2194 CREATE OR REPLACE VIEW "issue_supporter_in_admission_state" AS

2195 SELECT

2196 "area"."unit_id",

2197 "issue"."area_id",

2198 "issue"."id" AS "issue_id",

2199 "supporter"."member_id",

2200 "direct_interest_snapshot"."weight"

2201 FROM "issue"

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

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

2204 JOIN "direct_interest_snapshot"

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

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

2207 AND "direct_interest_snapshot"."member_id" = "supporter"."member_id"

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

2209

2210

2211 CREATE OR REPLACE VIEW "individual_suggestion_ranking" AS

2212 SELECT

2213 "opinion"."initiative_id",

2214 "opinion"."member_id",

2215 "direct_interest_snapshot"."weight",

2216 CASE WHEN

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

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

2219 THEN 1 ELSE

2220 CASE WHEN

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

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

2223 THEN 2 ELSE

2224 CASE WHEN

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

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

2227 THEN 3 ELSE 4 END

2228 END

2229 END AS "preference",

2230 "opinion"."suggestion_id"

2231 FROM "opinion"

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

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

2234 JOIN "direct_interest_snapshot"

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

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

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

2238

2239

2240 CREATE VIEW "expired_session" AS

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

2242

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

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

2245

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

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

2248

2249

2250 CREATE VIEW "expired_token" AS

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

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

2253 SELECT NULL FROM "token" AS "other"

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

2255

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

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

2258

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

2260

2261

2262 CREATE VIEW "unused_snapshot" AS

2263 SELECT "snapshot".* FROM "snapshot"

2264 LEFT JOIN "issue"

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

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

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

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

2269 WHERE "issue"."id" ISNULL;

2270

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

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

2273

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

2275

2276

2277 CREATE VIEW "expired_snapshot" AS

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

2279 WHERE "unused_snapshot"."calculated" <

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

2281

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

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

2284

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

2286

2287

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

2289

2290

2291 CREATE OR REPLACE FUNCTION "delegation_chain"

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

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

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

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

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

2297 "simulate_default_p" BOOLEAN DEFAULT FALSE )

2298 RETURNS SETOF "delegation_chain_row"

2299 LANGUAGE 'plpgsql' STABLE AS $$

2300 DECLARE

2301 "scope_v" "delegation_scope";

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

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

2304 "issue_row" "issue"%ROWTYPE;

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

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

2307 "output_row" "delegation_chain_row";

2308 "output_rows" "delegation_chain_row"[];

2309 "simulate_v" BOOLEAN;

2310 "simulate_here_v" BOOLEAN;

2311 "delegation_row" "delegation"%ROWTYPE;

2312 "row_count" INT4;

2313 "i" INT4;

2314 "loop_v" BOOLEAN;

2315 BEGIN

2316 IF "simulate_trustee_id_p" NOTNULL AND "simulate_default_p" THEN

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

2318 END IF;

2319 IF "simulate_trustee_id_p" NOTNULL OR "simulate_default_p" THEN

2320 "simulate_v" := TRUE;

2321 ELSE

2322 "simulate_v" := FALSE;

2323 END IF;

2324 IF

2325 "unit_id_p" NOTNULL AND

2326 "area_id_p" ISNULL AND

2327 "issue_id_p" ISNULL

2328 THEN

2329 "scope_v" := 'unit';

2330 "unit_id_v" := "unit_id_p";

2331 ELSIF

2332 "unit_id_p" ISNULL AND

2333 "area_id_p" NOTNULL AND

2334 "issue_id_p" ISNULL

2335 THEN

2336 "scope_v" := 'area';

2337 "area_id_v" := "area_id_p";

2338 SELECT "unit_id" INTO "unit_id_v"

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

2340 ELSIF

2341 "unit_id_p" ISNULL AND

2342 "area_id_p" ISNULL AND

2343 "issue_id_p" NOTNULL

2344 THEN

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

2346 IF "issue_row"."id" ISNULL THEN

2347 RETURN;

2348 END IF;

2349 IF "issue_row"."closed" NOTNULL THEN

2350 IF "simulate_v" THEN

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

2352 END IF;

2353 FOR "output_row" IN

2354 SELECT * FROM

2355 "delegation_chain_for_closed_issue"("member_id_p", "issue_id_p")

2356 LOOP

2357 RETURN NEXT "output_row";

2358 END LOOP;

2359 RETURN;

2360 END IF;

2361 "scope_v" := 'issue';

2362 SELECT "area_id" INTO "area_id_v"

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

2364 SELECT "unit_id" INTO "unit_id_v"

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

2366 ELSE

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

2368 END IF;

2369 "visited_member_ids" := '{}';

2370 "loop_member_id_v" := NULL;

2371 "output_rows" := '{}';

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

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

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

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

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

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

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

2379 LOOP

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

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

2382 ELSE

2383 "visited_member_ids" :=

2384 "visited_member_ids" || "output_row"."member_id";

2385 END IF;

2386 IF "output_row"."participation" ISNULL THEN

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

2388 ELSIF "output_row"."participation" THEN

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

2390 END IF;

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

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

2393 SELECT NULL FROM "member" JOIN "privilege"

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

2395 AND "privilege"."unit_id" = "unit_id_v"

2396 WHERE "id" = "output_row"."member_id"

2397 AND "member"."active" AND "privilege"."voting_right"

2398 );

2399 "simulate_here_v" := (

2400 "simulate_v" AND

2401 "output_row"."member_id" = "member_id_p"

2402 );

2403 "delegation_row" := ROW(NULL);

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

2405 IF "scope_v" = 'unit' THEN

2406 IF NOT "simulate_here_v" THEN

2407 SELECT * INTO "delegation_row" FROM "delegation"

2408 WHERE "truster_id" = "output_row"."member_id"

2409 AND "unit_id" = "unit_id_v";

2410 END IF;

2411 ELSIF "scope_v" = 'area' THEN

2412 IF "simulate_here_v" THEN

2413 IF "simulate_trustee_id_p" ISNULL THEN

2414 SELECT * INTO "delegation_row" FROM "delegation"

2415 WHERE "truster_id" = "output_row"."member_id"

2416 AND "unit_id" = "unit_id_v";

2417 END IF;

2418 ELSE

2419 SELECT * INTO "delegation_row" FROM "delegation"

2420 WHERE "truster_id" = "output_row"."member_id"

2421 AND (

2422 "unit_id" = "unit_id_v" OR

2423 "area_id" = "area_id_v"

2424 )

2425 ORDER BY "scope" DESC;

2426 END IF;

2427 ELSIF "scope_v" = 'issue' THEN

2428 IF "issue_row"."fully_frozen" ISNULL THEN

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

2430 SELECT NULL FROM "interest"

2431 WHERE "issue_id" = "issue_id_p"

2432 AND "member_id" = "output_row"."member_id"

2433 );

2434 ELSE

2435 IF "output_row"."member_id" = "member_id_p" THEN

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

2437 SELECT NULL FROM "direct_voter"

2438 WHERE "issue_id" = "issue_id_p"

2439 AND "member_id" = "output_row"."member_id"

2440 );

2441 ELSE

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

2443 END IF;

2444 END IF;

2445 IF "simulate_here_v" THEN

2446 IF "simulate_trustee_id_p" ISNULL THEN

2447 SELECT * INTO "delegation_row" FROM "delegation"

2448 WHERE "truster_id" = "output_row"."member_id"

2449 AND (

2450 "unit_id" = "unit_id_v" OR

2451 "area_id" = "area_id_v"

2452 )

2453 ORDER BY "scope" DESC;

2454 END IF;

2455 ELSE

2456 SELECT * INTO "delegation_row" FROM "delegation"

2457 WHERE "truster_id" = "output_row"."member_id"

2458 AND (

2459 "unit_id" = "unit_id_v" OR

2460 "area_id" = "area_id_v" OR

2461 "issue_id" = "issue_id_p"

2462 )

2463 ORDER BY "scope" DESC;

2464 END IF;

2465 END IF;

2466 ELSE

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

2468 END IF;

2469 IF "simulate_here_v" AND "simulate_trustee_id_p" NOTNULL THEN

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

2471 "output_rows" := "output_rows" || "output_row";

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

2473 ELSIF "delegation_row"."trustee_id" NOTNULL THEN

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

2475 "output_rows" := "output_rows" || "output_row";

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

2477 ELSIF "delegation_row"."scope" NOTNULL THEN

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

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

2480 "output_rows" := "output_rows" || "output_row";

2481 EXIT;

2482 ELSE

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

2484 "output_rows" := "output_rows" || "output_row";

2485 EXIT;

2486 END IF;

2487 EXIT WHEN "loop_member_id_v" NOTNULL;

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

2489 END LOOP;

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

2491 "i" := 1;

2492 "loop_v" := FALSE;

2493 LOOP

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

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

2496 IF "loop_v" THEN

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

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

2499 ELSIF "i" = "row_count" THEN

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

2501 ELSE

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

2503 END IF;

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

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

2506 "loop_v" := TRUE;

2507 END IF;

2508 IF "scope_v" = 'unit' THEN

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

2510 END IF;

2511 RETURN NEXT "output_row";

2512 "i" := "i" + 1;

2513 END LOOP;

2514 RETURN;

2515 END;

2516 $$;

2517

2518

2519 CREATE OR REPLACE FUNCTION "get_initiatives_for_notification"

2520 ( "recipient_id_p" "member"."id"%TYPE )

2521 RETURNS SETOF "initiative_for_notification"

2522 LANGUAGE 'plpgsql' VOLATILE AS $$

2523 DECLARE

2524 "result_row" "initiative_for_notification"%ROWTYPE;

2525 "last_draft_id_v" "draft"."id"%TYPE;

2526 "last_suggestion_id_v" "suggestion"."id"%TYPE;

2527 BEGIN

2528 PERFORM "require_transaction_isolation"();

2529 PERFORM NULL FROM "member" WHERE "id" = "recipient_id_p" FOR UPDATE;

2530 FOR "result_row" IN

2531 SELECT * FROM "initiative_for_notification"

2532 WHERE "recipient_id" = "recipient_id_p"

2533 LOOP

2534 SELECT "id" INTO "last_draft_id_v" FROM "draft"

2535 WHERE "draft"."initiative_id" = "result_row"."initiative_id"

2536 ORDER BY "id" DESC LIMIT 1;

2537 SELECT "id" INTO "last_suggestion_id_v" FROM "suggestion"

2538 WHERE "suggestion"."initiative_id" = "result_row"."initiative_id"

2539 ORDER BY "id" DESC LIMIT 1;

2540 INSERT INTO "notification_initiative_sent"

2541 ("member_id", "initiative_id", "last_draft_id", "last_suggestion_id")

2542 VALUES (

2543 "recipient_id_p",

2544 "result_row"."initiative_id",

2545 "last_draft_id_v",

2546 "last_suggestion_id_v" )

2547 ON CONFLICT ("member_id", "initiative_id") DO UPDATE SET

2548 "last_draft_id" = "last_draft_id_v",

2549 "last_suggestion_id" = "last_suggestion_id_v";

2550 RETURN NEXT "result_row";

2551 END LOOP;

2552 DELETE FROM "notification_initiative_sent"

2553 USING "initiative", "issue"

2554 WHERE "notification_initiative_sent"."member_id" = "recipient_id_p"

2555 AND "initiative"."id" = "notification_initiative_sent"."initiative_id"

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

2557 AND ( "issue"."closed" NOTNULL OR "issue"."fully_frozen" NOTNULL );

2558 UPDATE "member" SET

2559 "notification_counter" = "notification_counter" + 1,

2560 "notification_sent" = now()

2561 WHERE "id" = "recipient_id_p";

2562 RETURN;

2563 END;

2564 $$;

2565

2566

2567 CREATE OR REPLACE FUNCTION "calculate_member_counts"()

2568 RETURNS VOID

2569 LANGUAGE 'plpgsql' VOLATILE AS $$

2570 BEGIN

2571 PERFORM "require_transaction_isolation"();

2572 DELETE FROM "member_count";

2573 INSERT INTO "member_count" ("total_count")

2574 SELECT "total_count" FROM "member_count_view";

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

2576 FROM "unit_member_count" AS "view"

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

2578 RETURN;

2579 END;

2580 $$;

2581

2582 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"';

2583

2584

2585 DROP VIEW "remaining_harmonic_initiative_weight_summands";

2586 DROP VIEW "remaining_harmonic_supporter_weight";

2587

2588

2589 CREATE VIEW "remaining_harmonic_supporter_weight" AS

2590 SELECT

2591 "direct_interest_snapshot"."snapshot_id",

2592 "direct_interest_snapshot"."issue_id",

2593 "direct_interest_snapshot"."member_id",

2594 "direct_interest_snapshot"."weight" AS "weight_num",

2595 count("initiative"."id") AS "weight_den"

2596 FROM "issue"

2597 JOIN "direct_interest_snapshot"

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

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

2600 JOIN "initiative"

2601 ON "issue"."id" = "initiative"."issue_id"

2602 AND "initiative"."harmonic_weight" ISNULL

2603 JOIN "direct_supporter_snapshot"

2604 ON "issue"."latest_snapshot_id" = "direct_supporter_snapshot"."snapshot_id"

2605 AND "initiative"."id" = "direct_supporter_snapshot"."initiative_id"

2606 AND "direct_interest_snapshot"."member_id" = "direct_supporter_snapshot"."member_id"

2607 AND (

2608 "direct_supporter_snapshot"."satisfied" = TRUE OR

2609 coalesce("initiative"."admitted", FALSE) = FALSE

2610 )

2611 GROUP BY

2612 "direct_interest_snapshot"."snapshot_id",

2613 "direct_interest_snapshot"."issue_id",

2614 "direct_interest_snapshot"."member_id",

2615 "direct_interest_snapshot"."weight";

2616

2617

2618 CREATE VIEW "remaining_harmonic_initiative_weight_summands" AS

2619 SELECT

2620 "initiative"."issue_id",

2621 "initiative"."id" AS "initiative_id",

2622 "initiative"."admitted",

2623 sum("remaining_harmonic_supporter_weight"."weight_num") AS "weight_num",

2624 "remaining_harmonic_supporter_weight"."weight_den"

2625 FROM "remaining_harmonic_supporter_weight"

2626 JOIN "initiative"

2627 ON "remaining_harmonic_supporter_weight"."issue_id" = "initiative"."issue_id"

2628 AND "initiative"."harmonic_weight" ISNULL

2629 JOIN "direct_supporter_snapshot"

2630 ON "remaining_harmonic_supporter_weight"."snapshot_id" = "direct_supporter_snapshot"."snapshot_id"

2631 AND "initiative"."id" = "direct_supporter_snapshot"."initiative_id"

2632 AND "remaining_harmonic_supporter_weight"."member_id" = "direct_supporter_snapshot"."member_id"

2633 AND (

2634 "direct_supporter_snapshot"."satisfied" = TRUE OR

2635 coalesce("initiative"."admitted", FALSE) = FALSE

2636 )

2637 GROUP BY

2638 "initiative"."issue_id",

2639 "initiative"."id",

2640 "initiative"."admitted",

2641 "remaining_harmonic_supporter_weight"."weight_den";

2642

2643

2644 DROP FUNCTION "create_population_snapshot"

2645 ( "issue_id_p" "issue"."id"%TYPE );

2646

2647

2648 DROP FUNCTION "weight_of_added_delegations_for_population_snapshot"

2649 ( "issue_id_p" "issue"."id"%TYPE,

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

2651 "delegate_member_ids_p" "delegating_population_snapshot"."delegate_member_ids"%TYPE );

2652

2653

2654 DROP FUNCTION "weight_of_added_delegations_for_interest_snapshot"

2655 ( "issue_id_p" "issue"."id"%TYPE,

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

2657 "delegate_member_ids_p" "delegating_interest_snapshot"."delegate_member_ids"%TYPE );

2658

2659

2660 CREATE FUNCTION "weight_of_added_delegations_for_snapshot"

2661 ( "snapshot_id_p" "snapshot"."id"%TYPE,

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

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

2664 "delegate_member_ids_p" "delegating_interest_snapshot"."delegate_member_ids"%TYPE )

2665 RETURNS "direct_interest_snapshot"."weight"%TYPE

2666 LANGUAGE 'plpgsql' VOLATILE AS $$

2667 DECLARE

2668 "issue_delegation_row" "issue_delegation"%ROWTYPE;

2669 "delegate_member_ids_v" "delegating_interest_snapshot"."delegate_member_ids"%TYPE;

2670 "weight_v" INT4;

2671 "sub_weight_v" INT4;

2672 BEGIN

2673 PERFORM "require_transaction_isolation"();

2674 "weight_v" := 0;

2675 FOR "issue_delegation_row" IN

2676 SELECT * FROM "issue_delegation"

2677 WHERE "trustee_id" = "member_id_p"

2678 AND "issue_id" = "issue_id_p"

2679 LOOP

2680 IF NOT EXISTS (

2681 SELECT NULL FROM "direct_interest_snapshot"

2682 WHERE "snapshot_id" = "snapshot_id_p"

2683 AND "issue_id" = "issue_id_p"

2684 AND "member_id" = "issue_delegation_row"."truster_id"

2685 ) AND NOT EXISTS (

2686 SELECT NULL FROM "delegating_interest_snapshot"

2687 WHERE "snapshot_id" = "snapshot_id_p"

2688 AND "issue_id" = "issue_id_p"

2689 AND "member_id" = "issue_delegation_row"."truster_id"

2690 ) THEN

2691 "delegate_member_ids_v" :=

2692 "member_id_p" || "delegate_member_ids_p";

2693 INSERT INTO "delegating_interest_snapshot" (

2694 "snapshot_id",

2695 "issue_id",

2696 "member_id",

2697 "scope",

2698 "delegate_member_ids"

2699 ) VALUES (

2700 "snapshot_id_p",

2701 "issue_id_p",

2702 "issue_delegation_row"."truster_id",

2703 "issue_delegation_row"."scope",

2704 "delegate_member_ids_v"

2705 );

2706 "sub_weight_v" := 1 +

2707 "weight_of_added_delegations_for_snapshot"(

2708 "snapshot_id_p",

2709 "issue_id_p",

2710 "issue_delegation_row"."truster_id",

2711 "delegate_member_ids_v"

2712 );

2713 UPDATE "delegating_interest_snapshot"

2714 SET "weight" = "sub_weight_v"

2715 WHERE "snapshot_id" = "snapshot_id_p"

2716 AND "issue_id" = "issue_id_p"

2717 AND "member_id" = "issue_delegation_row"."truster_id";

2718 "weight_v" := "weight_v" + "sub_weight_v";

2719 END IF;

2720 END LOOP;

2721 RETURN "weight_v";

2722 END;

2723 $$;

2724

2725 COMMENT ON FUNCTION "weight_of_added_delegations_for_snapshot"

2726 ( "snapshot"."id"%TYPE,

2727 "issue"."id"%TYPE,

2728 "member"."id"%TYPE,

2729 "delegating_interest_snapshot"."delegate_member_ids"%TYPE )

2730 IS 'Helper function for "fill_snapshot" function';

2731

2732

2733 DROP FUNCTION "create_interest_snapshot"

2734 ( "issue_id_p" "issue"."id"%TYPE );

2735

2736

2737 DROP FUNCTION "create_snapshot"

2738 ( "issue_id_p" "issue"."id"%TYPE );

2739

2740

2741 CREATE FUNCTION "take_snapshot"

2742 ( "issue_id_p" "issue"."id"%TYPE,

2743 "area_id_p" "area"."id"%TYPE = NULL )

2744 RETURNS "snapshot"."id"%TYPE

2745 LANGUAGE 'plpgsql' VOLATILE AS $$

2746 DECLARE

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

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

2749 "snapshot_id_v" "snapshot"."id"%TYPE;

2750 "issue_id_v" "issue"."id"%TYPE;

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

2752 BEGIN

2753 IF "issue_id_p" NOTNULL AND "area_id_p" NOTNULL THEN

2754 RAISE EXCEPTION 'One of "issue_id_p" and "area_id_p" must be NULL';

2755 END IF;

2756 PERFORM "require_transaction_isolation"();

2757 IF "issue_id_p" ISNULL THEN

2758 "area_id_v" := "area_id_p";

2759 ELSE

2760 SELECT "area_id" INTO "area_id_v"

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

2762 END IF;

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

2764 INSERT INTO "snapshot" ("area_id", "issue_id")

2765 VALUES ("area_id_v", "issue_id_p")

2766 RETURNING "id" INTO "snapshot_id_v";

2767 INSERT INTO "snapshot_population" ("snapshot_id", "member_id")

2768 SELECT "snapshot_id_v", "member_id"

2769 FROM "unit_member" WHERE "unit_id" = "unit_id_v";

2770 UPDATE "snapshot" SET

2771 "population" = (

2772 SELECT count(1) FROM "snapshot_population"

2773 WHERE "snapshot_id" = "snapshot_id_v"

2774 ) WHERE "id" = "snapshot_id_v";

2775 FOR "issue_id_v" IN

2776 SELECT "id" FROM "issue"

2777 WHERE CASE WHEN "issue_id_p" ISNULL THEN

2778 "area_id" = "area_id_p" AND

2779 "state" = 'admission'

2780 ELSE

2781 "id" = "issue_id_p"

2782 END

2783 LOOP

2784 INSERT INTO "snapshot_issue" ("snapshot_id", "issue_id")

2785 VALUES ("snapshot_id_v", "issue_id_v");

2786 INSERT INTO "direct_interest_snapshot"

2787 ("snapshot_id", "issue_id", "member_id")

2788 SELECT

2789 "snapshot_id_v" AS "snapshot_id",

2790 "issue_id_v" AS "issue_id",

2791 "member"."id" AS "member_id"

2792 FROM "issue"

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

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

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

2796 JOIN "privilege"

2797 ON "privilege"."unit_id" = "area"."unit_id"

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

2799 WHERE "issue"."id" = "issue_id_v"

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

2801 FOR "member_id_v" IN

2802 SELECT "member_id" FROM "direct_interest_snapshot"

2803 WHERE "snapshot_id" = "snapshot_id_v"

2804 AND "issue_id" = "issue_id_v"

2805 LOOP

2806 UPDATE "direct_interest_snapshot" SET

2807 "weight" = 1 +

2808 "weight_of_added_delegations_for_snapshot"(

2809 "snapshot_id_v",

2810 "issue_id_v",

2811 "member_id_v",

2812 '{}'

2813 )

2814 WHERE "snapshot_id" = "snapshot_id_v"

2815 AND "issue_id" = "issue_id_v"

2816 AND "member_id" = "member_id_v";

2817 END LOOP;

2818 INSERT INTO "direct_supporter_snapshot"

2819 ( "snapshot_id", "issue_id", "initiative_id", "member_id",

2820 "draft_id", "informed", "satisfied" )

2821 SELECT

2822 "snapshot_id_v" AS "snapshot_id",

2823 "issue_id_v" AS "issue_id",

2824 "initiative"."id" AS "initiative_id",

2825 "supporter"."member_id" AS "member_id",

2826 "supporter"."draft_id" AS "draft_id",

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

2828 NOT EXISTS (

2829 SELECT NULL FROM "critical_opinion"

2830 WHERE "initiative_id" = "initiative"."id"

2831 AND "member_id" = "supporter"."member_id"

2832 ) AS "satisfied"

2833 FROM "initiative"

2834 JOIN "supporter"

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

2836 JOIN "current_draft"

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

2838 JOIN "direct_interest_snapshot"

2839 ON "snapshot_id_v" = "direct_interest_snapshot"."snapshot_id"

2840 AND "supporter"."member_id" = "direct_interest_snapshot"."member_id"

2841 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"

2842 WHERE "initiative"."issue_id" = "issue_id_v";

2843 DELETE FROM "temporary_suggestion_counts";

2844 INSERT INTO "temporary_suggestion_counts"

2845 ( "id",

2846 "minus2_unfulfilled_count", "minus2_fulfilled_count",

2847 "minus1_unfulfilled_count", "minus1_fulfilled_count",

2848 "plus1_unfulfilled_count", "plus1_fulfilled_count",

2849 "plus2_unfulfilled_count", "plus2_fulfilled_count" )

2850 SELECT

2851 "suggestion"."id",

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" = -2

2859 AND "opinion"."fulfilled" = FALSE

2860 ) AS "minus2_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" = -2

2868 AND "opinion"."fulfilled" = TRUE

2869 ) AS "minus2_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" = -1

2877 AND "opinion"."fulfilled" = FALSE

2878 ) AS "minus1_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" = -1

2886 AND "opinion"."fulfilled" = TRUE

2887 ) AS "minus1_fulfilled_count",

2888 ( SELECT coalesce(sum("di"."weight"), 0)

2889 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"

2890 ON "di"."snapshot_id" = "snapshot_id_v"

2891 AND "di"."issue_id" = "issue_id_v"

2892 AND "di"."member_id" = "opinion"."member_id"

2893 WHERE "opinion"."suggestion_id" = "suggestion"."id"

2894 AND "opinion"."degree" = 1

2895 AND "opinion"."fulfilled" = FALSE

2896 ) AS "plus1_unfulfilled_count",

2897 ( SELECT coalesce(sum("di"."weight"), 0)

2898 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"

2899 ON "di"."snapshot_id" = "snapshot_id_v"

2900 AND "di"."issue_id" = "issue_id_v"

2901 AND "di"."member_id" = "opinion"."member_id"

2902 WHERE "opinion"."suggestion_id" = "suggestion"."id"

2903 AND "opinion"."degree" = 1

2904 AND "opinion"."fulfilled" = TRUE

2905 ) AS "plus1_fulfilled_count",

2906 ( SELECT coalesce(sum("di"."weight"), 0)

2907 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"

2908 ON "di"."snapshot_id" = "snapshot_id_v"

2909 AND "di"."issue_id" = "issue_id_v"

2910 AND "di"."member_id" = "opinion"."member_id"

2911 WHERE "opinion"."suggestion_id" = "suggestion"."id"

2912 AND "opinion"."degree" = 2

2913 AND "opinion"."fulfilled" = FALSE

2914 ) AS "plus2_unfulfilled_count",

2915 ( SELECT coalesce(sum("di"."weight"), 0)

2916 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"

2917 ON "di"."snapshot_id" = "snapshot_id_v"

2918 AND "di"."issue_id" = "issue_id_v"

2919 AND "di"."member_id" = "opinion"."member_id"

2920 WHERE "opinion"."suggestion_id" = "suggestion"."id"

2921 AND "opinion"."degree" = 2

2922 AND "opinion"."fulfilled" = TRUE

2923 ) AS "plus2_fulfilled_count"

2924 FROM "suggestion" JOIN "initiative"

2925 ON "suggestion"."initiative_id" = "initiative"."id"

2926 WHERE "initiative"."issue_id" = "issue_id_v";

2927 END LOOP;

2928 RETURN "snapshot_id_v";

2929 END;

2930 $$;

2931

2932 COMMENT ON FUNCTION "take_snapshot"

2933 ( "issue"."id"%TYPE,

2934 "area"."id"%TYPE )

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

2936

2937

2938 DROP FUNCTION "set_snapshot_event"

2939 ( "issue_id_p" "issue"."id"%TYPE,

2940 "event_p" "snapshot_event" );

2941

2942

2943 CREATE FUNCTION "finish_snapshot"

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

2945 RETURNS VOID

2946 LANGUAGE 'plpgsql' VOLATILE AS $$

2947 DECLARE

2948 "snapshot_id_v" "snapshot"."id"%TYPE;

2949 BEGIN

2950 -- NOTE: function does not require snapshot isolation but we don't call

2951 -- "dont_require_snapshot_isolation" here because this function is

2952 -- also invoked by "check_issue"

2953 LOCK TABLE "snapshot" IN EXCLUSIVE MODE;

2954 SELECT "id" INTO "snapshot_id_v" FROM "snapshot"

2955 ORDER BY "id" DESC LIMIT 1;

2956 UPDATE "issue" SET

2957 "calculated" = "snapshot"."calculated",

2958 "latest_snapshot_id" = "snapshot_id_v",

2959 "population" = "snapshot"."population",

2960 "initiative_quorum" = CASE WHEN

2961 "policy"."initiative_quorum" > ceil(

2962 ( "issue"."population"::INT8 *

2963 "policy"."initiative_quorum_num"::INT8 ) /

2964 "policy"."initiative_quorum_den"::FLOAT8

2965 )::INT4

2966 THEN

2967 "policy"."initiative_quorum"

2968 ELSE

2969 ceil(

2970 ( "issue"."population"::INT8 *

2971 "policy"."initiative_quorum_num"::INT8 ) /

2972 "policy"."initiative_quorum_den"::FLOAT8

2973 )::INT4

2974 END

2975 FROM "snapshot", "policy"

2976 WHERE "issue"."id" = "issue_id_p"

2977 AND "snapshot"."id" = "snapshot_id_v"

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

2979 UPDATE "initiative" SET

2980 "supporter_count" = (

2981 SELECT coalesce(sum("di"."weight"), 0)

2982 FROM "direct_interest_snapshot" AS "di"

2983 JOIN "direct_supporter_snapshot" AS "ds"

2984 ON "di"."member_id" = "ds"."member_id"

2985 WHERE "di"."snapshot_id" = "snapshot_id_v"

2986 AND "di"."issue_id" = "issue_id_p"

2987 AND "ds"."snapshot_id" = "snapshot_id_v"

2988 AND "ds"."initiative_id" = "initiative"."id"

2989 ),

2990 "informed_supporter_count" = (

2991 SELECT coalesce(sum("di"."weight"), 0)

2992 FROM "direct_interest_snapshot" AS "di"

2993 JOIN "direct_supporter_snapshot" AS "ds"

2994 ON "di"."member_id" = "ds"."member_id"

2995 WHERE "di"."snapshot_id" = "snapshot_id_v"

2996 AND "di"."issue_id" = "issue_id_p"

2997 AND "ds"."snapshot_id" = "snapshot_id_v"

2998 AND "ds"."initiative_id" = "initiative"."id"

2999 AND "ds"."informed"

3000 ),

3001 "satisfied_supporter_count" = (

3002 SELECT coalesce(sum("di"."weight"), 0)

3003 FROM "direct_interest_snapshot" AS "di"

3004 JOIN "direct_supporter_snapshot" AS "ds"

3005 ON "di"."member_id" = "ds"."member_id"

3006 WHERE "di"."snapshot_id" = "snapshot_id_v"

3007 AND "di"."issue_id" = "issue_id_p"

3008 AND "ds"."snapshot_id" = "snapshot_id_v"

3009 AND "ds"."initiative_id" = "initiative"."id"

3010 AND "ds"."satisfied"

3011 ),

3012 "satisfied_informed_supporter_count" = (

3013 SELECT coalesce(sum("di"."weight"), 0)

3014 FROM "direct_interest_snapshot" AS "di"

3015 JOIN "direct_supporter_snapshot" AS "ds"

3016 ON "di"."member_id" = "ds"."member_id"

3017 WHERE "di"."snapshot_id" = "snapshot_id_v"

3018 AND "di"."issue_id" = "issue_id_p"

3019 AND "ds"."snapshot_id" = "snapshot_id_v"

3020 AND "ds"."initiative_id" = "initiative"."id"

3021 AND "ds"."informed"

3022 AND "ds"."satisfied"

3023 )

3024 WHERE "issue_id" = "issue_id_p";

3025 UPDATE "suggestion" SET

3026 "minus2_unfulfilled_count" = "temp"."minus2_unfulfilled_count",

3027 "minus2_fulfilled_count" = "temp"."minus2_fulfilled_count",

3028 "minus1_unfulfilled_count" = "temp"."minus1_unfulfilled_count",

3029 "minus1_fulfilled_count" = "temp"."minus1_fulfilled_count",

3030 "plus1_unfulfilled_count" = "temp"."plus1_unfulfilled_count",

3031 "plus1_fulfilled_count" = "temp"."plus1_fulfilled_count",

3032 "plus2_unfulfilled_count" = "temp"."plus2_unfulfilled_count",

3033 "plus2_fulfilled_count" = "temp"."plus2_fulfilled_count"

3034 FROM "temporary_suggestion_counts" AS "temp", "initiative"

3035 WHERE "temp"."id" = "suggestion"."id"

3036 AND "initiative"."issue_id" = "issue_id_p"

3037 AND "suggestion"."initiative_id" = "initiative"."id";

3038 DELETE FROM "temporary_suggestion_counts";

3039 RETURN;

3040 END;

3041 $$;

3042

3043 COMMENT ON FUNCTION "finish_snapshot"

3044 ( "issue"."id"%TYPE )

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

3046

3047

3048 CREATE FUNCTION "issue_admission"

3049 ( "area_id_p" "area"."id"%TYPE )

3050 RETURNS BOOLEAN

3051 LANGUAGE 'plpgsql' VOLATILE AS $$

3052 DECLARE

3053 "issue_id_v" "issue"."id"%TYPE;

3054 BEGIN

3055 PERFORM "dont_require_transaction_isolation"();

3056 LOCK TABLE "snapshot" IN EXCLUSIVE MODE;

3057 UPDATE "area" SET "issue_quorum" = "view"."issue_quorum"

3058 FROM "area_quorum" AS "view"

3059 WHERE "area"."id" = "view"."area_id"

3060 AND "area"."id" = "area_id_p";

3061 SELECT "id" INTO "issue_id_v" FROM "issue_for_admission"

3062 WHERE "area_id" = "area_id_p";

3063 IF "issue_id_v" ISNULL THEN RETURN FALSE; END IF;

3064 UPDATE "issue" SET

3065 "admission_snapshot_id" = "latest_snapshot_id",

3066 "state" = 'discussion',

3067 "accepted" = now(),

3068 "phase_finished" = NULL,

3069 "issue_quorum" = "issue_quorum"."issue_quorum"

3070 FROM "issue_quorum"

3071 WHERE "id" = "issue_id_v"

3072 AND "issue_quorum"."issue_id" = "issue_id_v";

3073 RETURN TRUE;

3074 END;

3075 $$;

3076

3077 COMMENT ON FUNCTION "issue_admission"

3078 ( "area"."id"%TYPE )

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

3080

3081

3082 CREATE OR REPLACE FUNCTION "check_issue"

3083 ( "issue_id_p" "issue"."id"%TYPE,

3084 "persist" "check_issue_persistence" )

3085 RETURNS "check_issue_persistence"

3086 LANGUAGE 'plpgsql' VOLATILE AS $$

3087 DECLARE

3088 "issue_row" "issue"%ROWTYPE;

3089 "last_calculated_v" "snapshot"."calculated"%TYPE;

3090 "policy_row" "policy"%ROWTYPE;

3091 "initiative_row" "initiative"%ROWTYPE;

3092 "state_v" "issue_state";

3093 BEGIN

3094 PERFORM "require_transaction_isolation"();

3095 IF "persist" ISNULL THEN

3096 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"

3097 FOR UPDATE;

3098 SELECT "calculated" INTO "last_calculated_v"

3099 FROM "snapshot" JOIN "snapshot_issue"

3100 ON "snapshot"."id" = "snapshot_issue"."snapshot_id"

3101 WHERE "snapshot_issue"."issue_id" = "issue_id_p";

3102 IF "issue_row"."closed" NOTNULL THEN

3103 RETURN NULL;

3104 END IF;

3105 "persist"."state" := "issue_row"."state";

3106 IF

3107 ( "issue_row"."state" = 'admission' AND "last_calculated_v" >=

3108 "issue_row"."created" + "issue_row"."max_admission_time" ) OR

3109 ( "issue_row"."state" = 'discussion' AND now() >=

3110 "issue_row"."accepted" + "issue_row"."discussion_time" ) OR

3111 ( "issue_row"."state" = 'verification' AND now() >=

3112 "issue_row"."half_frozen" + "issue_row"."verification_time" ) OR

3113 ( "issue_row"."state" = 'voting' AND now() >=

3114 "issue_row"."fully_frozen" + "issue_row"."voting_time" )

3115 THEN

3116 "persist"."phase_finished" := TRUE;

3117 ELSE

3118 "persist"."phase_finished" := FALSE;

3119 END IF;

3120 IF

3121 NOT EXISTS (

3122 -- all initiatives are revoked

3123 SELECT NULL FROM "initiative"

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

3125 ) AND (

3126 -- and issue has not been accepted yet

3127 "persist"."state" = 'admission' OR

3128 -- or verification time has elapsed

3129 ( "persist"."state" = 'verification' AND

3130 "persist"."phase_finished" ) OR

3131 -- or no initiatives have been revoked lately

3132 NOT EXISTS (

3133 SELECT NULL FROM "initiative"

3134 WHERE "issue_id" = "issue_id_p"

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

3136 )

3137 )

3138 THEN

3139 "persist"."issue_revoked" := TRUE;

3140 ELSE

3141 "persist"."issue_revoked" := FALSE;

3142 END IF;

3143 IF "persist"."phase_finished" OR "persist"."issue_revoked" THEN

3144 UPDATE "issue" SET "phase_finished" = now()

3145 WHERE "id" = "issue_row"."id";

3146 RETURN "persist";

3147 ELSIF

3148 "persist"."state" IN ('admission', 'discussion', 'verification')

3149 THEN

3150 RETURN "persist";

3151 ELSE

3152 RETURN NULL;

3153 END IF;

3154 END IF;

3155 IF

3156 "persist"."state" IN ('admission', 'discussion', 'verification') AND

3157 coalesce("persist"."snapshot_created", FALSE) = FALSE

3158 THEN

3159 IF "persist"."state" != 'admission' THEN

3160 PERFORM "take_snapshot"("issue_id_p");

3161 PERFORM "finish_snapshot"("issue_id_p");

3162 ELSE

3163 UPDATE "issue" SET "issue_quorum" = "issue_quorum"."issue_quorum"

3164 FROM "issue_quorum"

3165 WHERE "id" = "issue_id_p"

3166 AND "issue_quorum"."issue_id" = "issue_id_p";

3167 END IF;

3168 "persist"."snapshot_created" = TRUE;

3169 IF "persist"."phase_finished" THEN

3170 IF "persist"."state" = 'admission' THEN

3171 UPDATE "issue" SET "admission_snapshot_id" = "latest_snapshot_id"

3172 WHERE "id" = "issue_id_p";

3173 ELSIF "persist"."state" = 'discussion' THEN

3174 UPDATE "issue" SET "half_freeze_snapshot_id" = "latest_snapshot_id"

3175 WHERE "id" = "issue_id_p";

3176 ELSIF "persist"."state" = 'verification' THEN

3177 UPDATE "issue" SET "full_freeze_snapshot_id" = "latest_snapshot_id"

3178 WHERE "id" = "issue_id_p";

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

3180 FOR "initiative_row" IN

3181 SELECT * FROM "initiative"

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

3183 FOR UPDATE

3184 LOOP

3185 IF

3186 "initiative_row"."polling" OR

3187 "initiative_row"."satisfied_supporter_count" >=

3188 "issue_row"."initiative_quorum"

3189 THEN

3190 UPDATE "initiative" SET "admitted" = TRUE

3191 WHERE "id" = "initiative_row"."id";

3192 ELSE

3193 UPDATE "initiative" SET "admitted" = FALSE

3194 WHERE "id" = "initiative_row"."id";

3195 END IF;

3196 END LOOP;

3197 END IF;

3198 END IF;

3199 RETURN "persist";

3200 END IF;

3201 IF

3202 "persist"."state" IN ('admission', 'discussion', 'verification') AND

3203 coalesce("persist"."harmonic_weights_set", FALSE) = FALSE

3204 THEN

3205 PERFORM "set_harmonic_initiative_weights"("issue_id_p");

3206 "persist"."harmonic_weights_set" = TRUE;

3207 IF

3208 "persist"."phase_finished" OR

3209 "persist"."issue_revoked" OR

3210 "persist"."state" = 'admission'

3211 THEN

3212 RETURN "persist";

3213 ELSE

3214 RETURN NULL;

3215 END IF;

3216 END IF;

3217 IF "persist"."issue_revoked" THEN

3218 IF "persist"."state" = 'admission' THEN

3219 "state_v" := 'canceled_revoked_before_accepted';

3220 ELSIF "persist"."state" = 'discussion' THEN

3221 "state_v" := 'canceled_after_revocation_during_discussion';

3222 ELSIF "persist"."state" = 'verification' THEN

3223 "state_v" := 'canceled_after_revocation_during_verification';

3224 END IF;

3225 UPDATE "issue" SET

3226 "state" = "state_v",

3227 "closed" = "phase_finished",

3228 "phase_finished" = NULL

3229 WHERE "id" = "issue_id_p";

3230 RETURN NULL;

3231 END IF;

3232 IF "persist"."state" = 'admission' THEN

3233 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"

3234 FOR UPDATE;

3235 IF "issue_row"."phase_finished" NOTNULL THEN

3236 UPDATE "issue" SET

3237 "state" = 'canceled_issue_not_accepted',

3238 "closed" = "phase_finished",

3239 "phase_finished" = NULL

3240 WHERE "id" = "issue_id_p";

3241 END IF;

3242 RETURN NULL;

3243 END IF;

3244 IF "persist"."phase_finished" THEN

3245 IF "persist"."state" = 'discussion' THEN

3246 UPDATE "issue" SET

3247 "state" = 'verification',

3248 "half_frozen" = "phase_finished",

3249 "phase_finished" = NULL

3250 WHERE "id" = "issue_id_p";

3251 RETURN NULL;

3252 END IF;

3253 IF "persist"."state" = 'verification' THEN

3254 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"

3255 FOR UPDATE;

3256 SELECT * INTO "policy_row" FROM "policy"

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

3258 IF EXISTS (

3259 SELECT NULL FROM "initiative"

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

3261 ) THEN

3262 UPDATE "issue" SET

3263 "state" = 'voting',

3264 "fully_frozen" = "phase_finished",

3265 "phase_finished" = NULL

3266 WHERE "id" = "issue_id_p";

3267 ELSE

3268 UPDATE "issue" SET

3269 "state" = 'canceled_no_initiative_admitted',

3270 "fully_frozen" = "phase_finished",

3271 "closed" = "phase_finished",

3272 "phase_finished" = NULL

3273 WHERE "id" = "issue_id_p";

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

3275 -- issue state has been manipulated

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

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

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

3279 END IF;

3280 RETURN NULL;

3281 END IF;

3282 IF "persist"."state" = 'voting' THEN

3283 IF coalesce("persist"."closed_voting", FALSE) = FALSE THEN

3284 PERFORM "close_voting"("issue_id_p");

3285 "persist"."closed_voting" = TRUE;

3286 RETURN "persist";

3287 END IF;

3288 PERFORM "calculate_ranks"("issue_id_p");

3289 RETURN NULL;

3290 END IF;

3291 END IF;

3292 RAISE WARNING 'should not happen';

3293 RETURN NULL;

3294 END;

3295 $$;

3296

3297

3298 CREATE OR REPLACE FUNCTION "check_everything"()

3299 RETURNS VOID

3300 LANGUAGE 'plpgsql' VOLATILE AS $$

3301 DECLARE

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

3303 "snapshot_id_v" "snapshot"."id"%TYPE;

3304 "issue_id_v" "issue"."id"%TYPE;

3305 "persist_v" "check_issue_persistence";

3306 BEGIN

3307 RAISE WARNING 'Function "check_everything" should only be used for development and debugging purposes';

3308 DELETE FROM "expired_session";

3309 DELETE FROM "expired_token";

3310 DELETE FROM "expired_snapshot";

3311 PERFORM "check_activity"();

3312 PERFORM "calculate_member_counts"();

3313 FOR "area_id_v" IN SELECT "id" FROM "area_with_unaccepted_issues" LOOP

3314 SELECT "take_snapshot"(NULL, "area_id_v") INTO "snapshot_id_v";

3315 PERFORM "finish_snapshot"("issue_id") FROM "snapshot_issue"

3316 WHERE "snapshot_id" = "snapshot_id_v";

3317 LOOP

3318 EXIT WHEN "issue_admission"("area_id_v") = FALSE;

3319 END LOOP;

3320 END LOOP;

3321 FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP

3322 "persist_v" := NULL;

3323 LOOP

3324 "persist_v" := "check_issue"("issue_id_v", "persist_v");

3325 EXIT WHEN "persist_v" ISNULL;

3326 END LOOP;

3327 END LOOP;

3328 RETURN;

3329 END;

3330 $$;

3331

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

3333

3334

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

3336 RETURNS VOID

3337 LANGUAGE 'plpgsql' VOLATILE AS $$

3338 BEGIN

3339 IF EXISTS (

3340 SELECT NULL FROM "issue" WHERE "id" = "issue_id_p" AND "cleaned" ISNULL

3341 ) THEN

3342 -- override protection triggers:

3343 INSERT INTO "temporary_transaction_data" ("key", "value")

3344 VALUES ('override_protection_triggers', TRUE::TEXT);

3345 -- clean data:

3346 DELETE FROM "delegating_voter"

3347 WHERE "issue_id" = "issue_id_p";

3348 DELETE FROM "direct_voter"

3349 WHERE "issue_id" = "issue_id_p";

3350 DELETE FROM "delegating_interest_snapshot"

3351 WHERE "issue_id" = "issue_id_p";

3352 DELETE FROM "direct_interest_snapshot"

3353 WHERE "issue_id" = "issue_id_p";

3354 DELETE FROM "non_voter"

3355 WHERE "issue_id" = "issue_id_p";

3356 DELETE FROM "delegation"

3357 WHERE "issue_id" = "issue_id_p";

3358 DELETE FROM "supporter"

3359 USING "initiative" -- NOTE: due to missing index on issue_id

3360 WHERE "initiative"."issue_id" = "issue_id_p"

3361 AND "supporter"."initiative_id" = "initiative_id";

3362 -- mark issue as cleaned:

3363 UPDATE "issue" SET "cleaned" = now() WHERE "id" = "issue_id_p";

3364 -- finish overriding protection triggers (avoids garbage):

3365 DELETE FROM "temporary_transaction_data"

3366 WHERE "key" = 'override_protection_triggers';

3367 END IF;

3368 RETURN;

3369 END;

3370 $$;

3371

3372

3373 CREATE OR REPLACE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)

3374 RETURNS VOID

3375 LANGUAGE 'plpgsql' VOLATILE AS $$

3376 BEGIN

3377 UPDATE "member" SET

3378 "last_login" = NULL,

3379 "last_delegation_check" = NULL,

3380 "login" = NULL,

3381 "password" = NULL,

3382 "authority" = NULL,

3383 "authority_uid" = NULL,

3384 "authority_login" = NULL,

3385 "deleted" = coalesce("deleted", now()),

3386 "locked" = TRUE,

3387 "active" = FALSE,

3388 "notify_email" = NULL,

3389 "notify_email_unconfirmed" = NULL,

3390 "notify_email_secret" = NULL,

3391 "notify_email_secret_expiry" = NULL,

3392 "notify_email_lock_expiry" = NULL,

3393 "disable_notifications" = TRUE,

3394 "notification_counter" = DEFAULT,

3395 "notification_sample_size" = 0,

3396 "notification_dow" = NULL,

3397 "notification_hour" = NULL,

3398 "notification_sent" = NULL,

3399 "login_recovery_expiry" = NULL,

3400 "password_reset_secret" = NULL,

3401 "password_reset_secret_expiry" = NULL,

3402 "location" = NULL

3403 WHERE "id" = "member_id_p";

3404 -- "text_search_data" is updated by triggers

3405 DELETE FROM "member_settings" WHERE "member_id" = "member_id_p";

3406 DELETE FROM "member_profile" WHERE "member_id" = "member_id_p";

3407 DELETE FROM "rendered_member_statement" WHERE "member_id" = "member_id_p";

3408 DELETE FROM "member_image" WHERE "member_id" = "member_id_p";

3409 DELETE FROM "contact" WHERE "member_id" = "member_id_p";

3410 DELETE FROM "ignored_member" WHERE "member_id" = "member_id_p";

3411 DELETE FROM "session" WHERE "member_id" = "member_id_p";

3412 DELETE FROM "member_application" WHERE "member_id" = "member_id_p";

3413 DELETE FROM "token" WHERE "member_id" = "member_id_p";

3414 DELETE FROM "subscription" WHERE "member_id" = "member_id_p";

3415 DELETE FROM "ignored_area" WHERE "member_id" = "member_id_p";

3416 DELETE FROM "ignored_initiative" WHERE "member_id" = "member_id_p";

3417 DELETE FROM "delegation" WHERE "truster_id" = "member_id_p";

3418 DELETE FROM "non_voter" WHERE "member_id" = "member_id_p";

3419 DELETE FROM "direct_voter" USING "issue"

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

3421 AND "issue"."closed" ISNULL

3422 AND "member_id" = "member_id_p";

3423 DELETE FROM "notification_initiative_sent" WHERE "member_id" = "member_id_p";

3424 RETURN;

3425 END;

3426 $$;

3427

3428

3429 CREATE OR REPLACE FUNCTION "delete_private_data"()

3430 RETURNS VOID

3431 LANGUAGE 'plpgsql' VOLATILE AS $$

3432 BEGIN

3433 DELETE FROM "temporary_transaction_data";

3434 DELETE FROM "temporary_suggestion_counts";

3435 DELETE FROM "member" WHERE "activated" ISNULL;

3436 UPDATE "member" SET

3437 "invite_code" = NULL,

3438 "invite_code_expiry" = NULL,

3439 "admin_comment" = NULL,

3440 "last_login" = NULL,

3441 "last_delegation_check" = NULL,

3442 "login" = NULL,

3443 "password" = NULL,

3444 "authority" = NULL,

3445 "authority_uid" = NULL,

3446 "authority_login" = NULL,

3447 "lang" = NULL,

3448 "notify_email" = NULL,

3449 "notify_email_unconfirmed" = NULL,

3450 "notify_email_secret" = NULL,

3451 "notify_email_secret_expiry" = NULL,

3452 "notify_email_lock_expiry" = NULL,

3453 "disable_notifications" = TRUE,

3454 "notification_counter" = DEFAULT,

3455 "notification_sample_size" = 0,

3456 "notification_dow" = NULL,

3457 "notification_hour" = NULL,

3458 "notification_sent" = NULL,

3459 "login_recovery_expiry" = NULL,

3460 "password_reset_secret" = NULL,

3461 "password_reset_secret_expiry" = NULL,

3462 "location" = NULL;

3463 -- "text_search_data" is updated by triggers

3464 DELETE FROM "verification";

3465 DELETE FROM "member_settings";

3466 DELETE FROM "member_useterms";

3467 DELETE FROM "member_profile";

3468 DELETE FROM "rendered_member_statement";

3469 DELETE FROM "member_image";

3470 DELETE FROM "contact";

3471 DELETE FROM "ignored_member";

3472 DELETE FROM "session";

3473 DELETE FROM "system_application";

3474 DELETE FROM "system_application_redirect_uri";

3475 DELETE FROM "dynamic_application_scope";

3476 DELETE FROM "member_application";

3477 DELETE FROM "token";

3478 DELETE FROM "subscription";

3479 DELETE FROM "ignored_area";

3480 DELETE FROM "ignored_initiative";

3481 DELETE FROM "non_voter";

3482 DELETE FROM "direct_voter" USING "issue"

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

3484 AND "issue"."closed" ISNULL;

3485 DELETE FROM "event_processed";

3486 DELETE FROM "notification_initiative_sent";

3487 DELETE FROM "newsletter";

3488 RETURN;

3489 END;

3490 $$;

3491

3492

3493 CREATE TEMPORARY TABLE "old_snapshot" AS

3494 SELECT "ordered".*, row_number() OVER () AS "snapshot_id"

3495 FROM (

3496 SELECT * FROM (

3497 SELECT

3498 "id" AS "issue_id",

3499 'end_of_admission'::"snapshot_event" AS "event",

3500 "accepted" AS "calculated"

3501 FROM "issue" WHERE "accepted" NOTNULL

3502 UNION ALL

3503 SELECT

3504 "id" AS "issue_id",

3505 'half_freeze'::"snapshot_event" AS "event",

3506 "half_frozen" AS "calculated"

3507 FROM "issue" WHERE "half_frozen" NOTNULL

3508 UNION ALL

3509 SELECT

3510 "id" AS "issue_id",

3511 'full_freeze'::"snapshot_event" AS "event",

3512 "fully_frozen" AS "calculated"

3513 FROM "issue" WHERE "fully_frozen" NOTNULL

3514 ) AS "unordered"

3515 ORDER BY "calculated", "issue_id", "event"

3516 ) AS "ordered";

3517

3518

3519 INSERT INTO "snapshot" ("id", "calculated", "population", "area_id", "issue_id")

3520 SELECT

3521 "old_snapshot"."snapshot_id" AS "id",

3522 "old_snapshot"."calculated",

3523 ( SELECT COALESCE(sum("weight"), 0)

3524 FROM "direct_population_snapshot" "dps"

3525 WHERE "dps"."issue_id" = "old_snapshot"."issue_id"

3526 AND "dps"."event" = "old_snapshot"."event"

3527 ) AS "population",

3528 "issue"."area_id" AS "area_id",

3529 "issue"."id" AS "issue_id"

3530 FROM "old_snapshot" JOIN "issue"

3531 ON "old_snapshot"."issue_id" = "issue"."id";

3532

3533

3534 INSERT INTO "snapshot_issue" ("snapshot_id", "issue_id")

3535 SELECT "id" AS "snapshot_id", "issue_id" FROM "snapshot";

3536

3537

3538 INSERT INTO "snapshot_population" ("snapshot_id", "member_id")

3539 SELECT

3540 "old_snapshot"."snapshot_id",

3541 "direct_population_snapshot"."member_id"

3542 FROM "old_snapshot" JOIN "direct_population_snapshot"

3543 ON "old_snapshot"."issue_id" = "direct_population_snapshot"."issue_id"

3544 AND "old_snapshot"."event" = "direct_population_snapshot"."event";

3545

3546 INSERT INTO "snapshot_population" ("snapshot_id", "member_id")

3547 SELECT

3548 "old_snapshot"."snapshot_id",

3549 "delegating_population_snapshot"."member_id"

3550 FROM "old_snapshot" JOIN "delegating_population_snapshot"

3551 ON "old_snapshot"."issue_id" = "delegating_population_snapshot"."issue_id"

3552 AND "old_snapshot"."event" = "delegating_population_snapshot"."event";

3553

3554

3555 INSERT INTO "direct_interest_snapshot"

3556 ("snapshot_id", "issue_id", "member_id", "weight")

3557 SELECT

3558 "old_snapshot"."snapshot_id",

3559 "old_snapshot"."issue_id",

3560 "direct_interest_snapshot_old"."member_id",

3561 "direct_interest_snapshot_old"."weight"

3562 FROM "old_snapshot" JOIN "direct_interest_snapshot_old"

3563 ON "old_snapshot"."issue_id" = "direct_interest_snapshot_old"."issue_id"

3564 AND "old_snapshot"."event" = "direct_interest_snapshot_old"."event";

3565

3566 INSERT INTO "delegating_interest_snapshot"

3567 ( "snapshot_id", "issue_id",

3568 "member_id", "weight", "scope", "delegate_member_ids" )

3569 SELECT

3570 "old_snapshot"."snapshot_id",

3571 "old_snapshot"."issue_id",

3572 "delegating_interest_snapshot_old"."member_id",

3573 "delegating_interest_snapshot_old"."weight",

3574 "delegating_interest_snapshot_old"."scope",

3575 "delegating_interest_snapshot_old"."delegate_member_ids"

3576 FROM "old_snapshot" JOIN "delegating_interest_snapshot_old"

3577 ON "old_snapshot"."issue_id" = "delegating_interest_snapshot_old"."issue_id"

3578 AND "old_snapshot"."event" = "delegating_interest_snapshot_old"."event";

3579

3580 INSERT INTO "direct_supporter_snapshot"

3581 ( "snapshot_id", "issue_id",

3582 "initiative_id", "member_id", "draft_id", "informed", "satisfied" )

3583 SELECT

3584 "old_snapshot"."snapshot_id",

3585 "old_snapshot"."issue_id",

3586 "direct_supporter_snapshot_old"."initiative_id",

3587 "direct_supporter_snapshot_old"."member_id",

3588 "direct_supporter_snapshot_old"."draft_id",

3589 "direct_supporter_snapshot_old"."informed",

3590 "direct_supporter_snapshot_old"."satisfied"

3591 FROM "old_snapshot" JOIN "direct_supporter_snapshot_old"

3592 ON "old_snapshot"."issue_id" = "direct_supporter_snapshot_old"."issue_id"

3593 AND "old_snapshot"."event" = "direct_supporter_snapshot_old"."event";

3594

3595

3596 ALTER TABLE "issue" DISABLE TRIGGER USER; -- NOTE: required to modify table later

3597

3598 UPDATE "issue" SET "latest_snapshot_id" = "snapshot"."id"

3599 FROM (

3600 SELECT DISTINCT ON ("issue_id") "issue_id", "id"

3601 FROM "snapshot" ORDER BY "issue_id", "id" DESC

3602 ) AS "snapshot"

3603 WHERE "snapshot"."issue_id" = "issue"."id";

3604

3605 UPDATE "issue" SET "admission_snapshot_id" = "old_snapshot"."snapshot_id"

3606 FROM "old_snapshot"

3607 WHERE "old_snapshot"."issue_id" = "issue"."id"

3608 AND "old_snapshot"."event" = 'end_of_admission';

3609

3610 UPDATE "issue" SET "half_freeze_snapshot_id" = "old_snapshot"."snapshot_id"

3611 FROM "old_snapshot"

3612 WHERE "old_snapshot"."issue_id" = "issue"."id"

3613 AND "old_snapshot"."event" = 'half_freeze';

3614

3615 UPDATE "issue" SET "full_freeze_snapshot_id" = "old_snapshot"."snapshot_id"

3616 FROM "old_snapshot"

3617 WHERE "old_snapshot"."issue_id" = "issue"."id"

3618 AND "old_snapshot"."event" = 'full_freeze';

3619

3620 ALTER TABLE "issue" ENABLE TRIGGER USER;

3621

3622

3623 DROP TABLE "old_snapshot";

3624

3625 DROP TABLE "direct_supporter_snapshot_old";

3626 DROP TABLE "delegating_interest_snapshot_old";

3627 DROP TABLE "direct_interest_snapshot_old";

3628 DROP TABLE "delegating_population_snapshot";

3629 DROP TABLE "direct_population_snapshot";

3630

3631

3632 DROP VIEW "open_issue";

3633

3634

3635 ALTER TABLE "issue" DROP COLUMN "latest_snapshot_event";

3636

3637

3638 CREATE VIEW "open_issue" AS

3639 SELECT * FROM "issue" WHERE "closed" ISNULL;

3640

3641 COMMENT ON VIEW "open_issue" IS 'All open issues';

3642

3643

3644 -- NOTE: create "issue_for_admission" view after altering table "issue"

3645 CREATE VIEW "issue_for_admission" AS

3646 SELECT DISTINCT ON ("issue"."area_id")

3647 "issue".*,

3648 max("initiative"."supporter_count") AS "max_supporter_count"

3649 FROM "issue"

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

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

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

3653 WHERE "issue"."state" = 'admission'::"issue_state"

3654 AND now() >= "issue"."created" + "issue"."min_admission_time"

3655 AND "initiative"."supporter_count" >= "policy"."issue_quorum"

3656 AND "initiative"."supporter_count" * "policy"."issue_quorum_den" >=

3657 "issue"."population" * "policy"."issue_quorum_num"

3658 AND "initiative"."supporter_count" >= "area"."issue_quorum"

3659 AND "initiative"."revoked" ISNULL

3660 GROUP BY "issue"."id"

3661 ORDER BY "issue"."area_id", "max_supporter_count" DESC, "issue"."id";

3662

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

3664

3665

3666 DROP TYPE "snapshot_event";

3667

3668

3669 ALTER TABLE "issue" ADD CONSTRAINT "snapshot_required" CHECK (

3670 ("half_frozen" ISNULL OR "half_freeze_snapshot_id" NOTNULL) AND

3671 ("fully_frozen" ISNULL OR "full_freeze_snapshot_id" NOTNULL) );

3672

3673

3674 COMMIT;