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

liquid_feedback_core

view update/core-update.v3.2.2-v4.0.0.sql @ 569:622c71ca6428

Bugfix in view "unit_member" (wrong ON clause for JOIN)

author	jbe
date	Fri Sep 29 19:37:49 2017 +0200 (2017-09-29)
parents	b1326d149247
children	4c40333dac66

line source

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

23 BEGIN;

26 CREATE OR REPLACE VIEW "liquid_feedback_version" AS

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

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

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

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

36 CREATE TABLE "verification" (

37 "id" SERIAL8 PRIMARY KEY,

38 "requested" TIMESTAMPTZ,

39 "request_origin" JSONB,

40 "request_data" JSONB,

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

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

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

44 "verified" TIMESTAMPTZ,

45 "verification_data" JSONB,

46 "denied" TIMESTAMPTZ,

47 "comment" TEXT,

48 CONSTRAINT "verified_and_denied_conflict" CHECK (

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

72 ALTER TABLE "member" ADD CONSTRAINT "deleted_requires_locked"

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

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

78 CREATE TABLE "member_settings" (

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

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

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

85 CREATE TABLE "member_useterms" (

86 "id" SERIAL8 PRIMARY KEY,

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

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

89 "contract_identifier" TEXT NOT NULL );

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

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

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

97 CREATE TABLE "member_profile" (

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

99 "formatting_engine" TEXT,

100 "statement" TEXT,

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

102 "profile_text_data" TEXT,

103 "text_search_data" TSVECTOR );

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

105 CREATE TRIGGER "update_text_search_data"

106 BEFORE INSERT OR UPDATE ON "member_profile"

107 FOR EACH ROW EXECUTE PROCEDURE

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

109 'statement', 'profile_text_data');

110

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

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

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

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

115

116

117 INSERT INTO "member_profile"

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

119 SELECT

120 "id" AS "member_id",

121 "formatting_engine",

122 "statement",

123 json_build_object(

124 'organizational_unit', "organizational_unit",

125 'internal_posts', "internal_posts",

126 'realname', "realname",

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

128 'address', "address",

129 'email', "email",

130 'xmpp_address', "xmpp_address",

131 'website', "website",

132 'phone', "phone",

133 'mobile_phone', "mobile_phone",

134 'profession', "profession",

135 'external_memberships', "external_memberships",

136 'external_posts', "external_posts"

137 ) AS "profile"

138 FROM "member";

139

140 UPDATE "member_profile" SET "profile_text_data" =

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

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

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

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

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

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

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

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

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

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

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

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

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

154

155

156 DROP VIEW "newsletter_to_send";

157 DROP VIEW "scheduled_notification_to_send";

158 DROP VIEW "member_to_notify";

159 DROP VIEW "member_eligible_to_be_notified";

160

161

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

177

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

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

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

181

182 DROP TRIGGER "update_text_search_data" ON "member";

183 CREATE TRIGGER "update_text_search_data"

184 BEFORE INSERT OR UPDATE ON "member"

185 FOR EACH ROW EXECUTE PROCEDURE

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

187 "name", "identification");

188

189

190 CREATE VIEW "member_eligible_to_be_notified" AS

191 SELECT * FROM "member"

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

193

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

195

196

197 CREATE VIEW "member_to_notify" AS

198 SELECT * FROM "member_eligible_to_be_notified"

199 WHERE "disable_notifications" = FALSE;

200

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

202

203

204 CREATE VIEW "scheduled_notification_to_send" AS

205 SELECT * FROM (

206 SELECT

207 "id" AS "recipient_id",

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

209 ( "notification_sent"::DATE + CASE

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

211 THEN 0 ELSE 1 END

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

213 ELSE

214 ( "notification_sent"::DATE +

215 ( 7 + "notification_dow" -

216 EXTRACT(DOW FROM

217 ( "notification_sent"::DATE + CASE

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

219 THEN 0 ELSE 1 END

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

221 )::INTEGER

222 ) % 7 +

223 CASE

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

225 THEN 0 ELSE 1

226 END

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

228 END AS "pending"

229 FROM (

230 SELECT

231 "id",

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

233 "notification_dow",

234 "notification_hour"

235 FROM "member_to_notify"

236 WHERE "notification_hour" NOTNULL

237 ) AS "subquery1"

238 ) AS "subquery2"

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

240

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

242

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

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

245

246

247 CREATE VIEW "newsletter_to_send" AS

248 SELECT

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

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

251 "newsletter"."published"

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

253 LEFT JOIN "privilege" ON

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

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

256 "privilege"."voting_right" = TRUE

257 LEFT JOIN "subscription" ON

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

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

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

261 AND "newsletter"."sent" ISNULL

262 AND (

263 "member"."disable_notifications" = FALSE OR

264 "newsletter"."include_all_members" = TRUE )

265 AND (

266 "newsletter"."unit_id" ISNULL OR

267 "privilege"."member_id" NOTNULL OR

268 "subscription"."member_id" NOTNULL );

269

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

271

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

273

274

275 DROP VIEW "expired_session";

276 DROP TABLE "session";

277

278

279 CREATE TABLE "session" (

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

281 "id" SERIAL8 PRIMARY KEY,

282 "ident" TEXT NOT NULL UNIQUE,

283 "additional_secret" TEXT,

284 "logout_token" TEXT,

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

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

287 "authority" TEXT,

288 "authority_uid" TEXT,

289 "authority_login" TEXT,

290 "needs_delegation_check" BOOLEAN NOT NULL DEFAULT FALSE,

291 "lang" TEXT );

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

293

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

295

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

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

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

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

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

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

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

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

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

305

306

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

308

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

310

311

312 CREATE TABLE "system_application" (

313 "id" SERIAL4 PRIMARY KEY,

314 "name" TEXT NOT NULL,

315 "discovery_baseurl" TEXT,

316 "client_id" TEXT NOT NULL UNIQUE,

317 "default_redirect_uri" TEXT NOT NULL,

318 "cert_common_name" TEXT,

319 "client_cred_scope" TEXT,

320 "flow" "authflow",

321 "automatic_scope" TEXT,

322 "permitted_scope" TEXT,

323 "forbidden_scope" TEXT );

324

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

326

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

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

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

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

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

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

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

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

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

336

337

338 CREATE TABLE "system_application_redirect_uri" (

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

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

341 "redirect_uri" TEXT );

342

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

344

345

346 CREATE TABLE "dynamic_application_scope" (

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

348 "redirect_uri" TEXT,

349 "flow" TEXT,

350 "scope" TEXT,

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

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

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

354

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

356

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

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

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

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

361

362

363 CREATE TABLE "member_application" (

364 "id" SERIAL4 PRIMARY KEY,

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

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

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

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

369 "domain" TEXT,

370 "session_id" INT8,

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

372 "scope" TEXT NOT NULL,

373 CONSTRAINT "system_application_or_domain_but_not_both" CHECK (

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

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

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

377

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

379

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

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

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

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

384

385

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

387

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

389

390

391 CREATE TABLE "token" (

392 "id" SERIAL8 PRIMARY KEY,

393 "token" TEXT NOT NULL UNIQUE,

394 "token_type" "token_type" NOT NULL,

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

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

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

398 "domain" TEXT,

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

400 "session_id" INT8,

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

402 "redirect_uri" TEXT,

403 "redirect_uri_explicit" BOOLEAN,

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

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

406 "used" BOOLEAN NOT NULL DEFAULT FALSE,

407 "scope" TEXT NOT NULL,

408 CONSTRAINT "access_token_needs_expiry"

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

410 CONSTRAINT "authorization_token_needs_redirect_uri"

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

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

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

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

415

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

417

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

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

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

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

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

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

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

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

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

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

428

429

430 CREATE TABLE "token_scope" (

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

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

433 "index" INT4,

434 "scope" TEXT NOT NULL );

435

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

437

438

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

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

441

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

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

444

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

446

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

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

449 ALTER TABLE "policy" ADD CONSTRAINT

450 "issue_quorum_if_and_only_if_not_polling" CHECK (

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

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

453 "polling" = ("issue_quorum_den" ISNULL)

454 );

455 ALTER TABLE "policy" ADD CONSTRAINT

456 "min_admission_time_smaller_than_max_admission_time" CHECK (

457 "min_admission_time" < "max_admission_time"

458 );

459 ALTER TABLE "policy" ADD CONSTRAINT

460 "timing_null_or_not_null_constraints" CHECK (

461 ( "polling" = FALSE AND

462 "min_admission_time" NOTNULL AND "max_admission_time" NOTNULL AND

463 "discussion_time" NOTNULL AND

464 "verification_time" NOTNULL AND

465 "voting_time" NOTNULL ) OR

466 ( "polling" = TRUE AND

467 "min_admission_time" ISNULL AND "max_admission_time" ISNULL AND

468 "discussion_time" NOTNULL AND

469 "verification_time" NOTNULL AND

470 "voting_time" NOTNULL ) OR

471 ( "polling" = TRUE AND

472 "min_admission_time" ISNULL AND "max_admission_time" ISNULL AND

473 "discussion_time" ISNULL AND

474 "verification_time" ISNULL AND

475 "voting_time" ISNULL )

476 );

477

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

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

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

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

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

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

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

485

486

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

488

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

490

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

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

493

494

495 DROP INDEX "area_unit_id_idx";

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

497

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

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

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

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

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

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

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

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

506

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

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

509

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

511

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

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

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

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

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

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

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

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

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

521

522

523 CREATE TABLE "snapshot" (

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

525 "id" SERIAL8 PRIMARY KEY,

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

527 "population" INT4,

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

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

530

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

532

533

534 CREATE TABLE "snapshot_population" (

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

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

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

538

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

540

541

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

543 DROP INDEX "issue_area_id_idx";

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

545 DROP INDEX "issue_policy_id_idx";

546

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

548

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

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

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

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

553 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 "snapshot" ADD FOREIGN KEY ("issue_id") REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE;

583

584

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

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

587

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

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

590

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

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

593

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

595

596

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

598

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

600

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

602

603

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

605

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

607

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

609

610

611 CREATE TABLE "temporary_suggestion_counts" (

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

613 "minus2_unfulfilled_count" INT4 NOT NULL,

614 "minus2_fulfilled_count" INT4 NOT NULL,

615 "minus1_unfulfilled_count" INT4 NOT NULL,

616 "minus1_fulfilled_count" INT4 NOT NULL,

617 "plus1_unfulfilled_count" INT4 NOT NULL,

618 "plus1_fulfilled_count" INT4 NOT NULL,

619 "plus2_unfulfilled_count" INT4 NOT NULL,

620 "plus2_fulfilled_count" INT4 NOT NULL );

621

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

623

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

625

626

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

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

629

630

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

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

633

634

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

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

637

638

639 CREATE TABLE "snapshot_issue" (

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

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

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

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

644

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

646

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

648

649

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

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

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

653

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

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

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

657

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

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

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

661

662

663 CREATE TABLE "direct_interest_snapshot" (

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

665 "snapshot_id" INT8,

666 "issue_id" INT4,

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

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

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

670 "weight" INT4 );

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

672

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

674

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

676

677

678 CREATE TABLE "delegating_interest_snapshot" (

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

680 "snapshot_id" INT8,

681 "issue_id" INT4,

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

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

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

685 "weight" INT4,

686 "scope" "delegation_scope" NOT NULL,

687 "delegate_member_ids" INT4[] NOT NULL );

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

689

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

691

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

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

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

695

696

697 CREATE TABLE "direct_supporter_snapshot" (

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

699 "snapshot_id" INT8,

700 "issue_id" INT4 NOT NULL,

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

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

703 "initiative_id" INT4,

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

705 "draft_id" INT8 NOT NULL,

706 "informed" BOOLEAN NOT NULL,

707 "satisfied" BOOLEAN NOT NULL,

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

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

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

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

712

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

714

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

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

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

718

719

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

721 DROP INDEX "non_voter_member_id_idx";

722

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

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

725

726

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

728 SELECT

729 "member_id",

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

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

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

733

734

735 DROP TABLE "setting";

736 DROP TABLE "setting_map";

737 DROP TABLE "member_relation_setting";

738 DROP TABLE "unit_setting";

739 DROP TABLE "area_setting";

740 DROP TABLE "initiative_setting";

741 DROP TABLE "suggestion_setting";

742

743

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

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

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

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

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

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

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

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

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

753

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

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

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

757

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

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

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

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

762

763 UPDATE "event" SET

764 "unit_id" = "area"."unit_id",

765 "area_id" = "issue"."area_id",

766 "policy_id" = "issue"."policy_id"

767 FROM "issue", "area"

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

769

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

771 "event" != 'issue_state_changed' OR (

772 "member_id" ISNULL AND

773 "other_member_id" ISNULL AND

774 "scope" ISNULL AND

775 "unit_id" NOTNULL AND

776 "area_id" NOTNULL AND

777 "policy_id" NOTNULL AND

778 "issue_id" NOTNULL AND

779 "state" NOTNULL AND

780 "initiative_id" ISNULL AND

781 "draft_id" ISNULL AND

782 "suggestion_id" ISNULL AND

783 "boolean_value" ISNULL AND

784 "numeric_value" ISNULL AND

785 "text_value" ISNULL AND

786 "old_text_value" ISNULL ));

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

788 "event" NOT IN (

789 'initiative_created_in_new_issue',

790 'initiative_created_in_existing_issue',

791 'initiative_revoked',

792 'new_draft_created'

793 ) OR (

794 "member_id" NOTNULL AND

795 "other_member_id" ISNULL AND

796 "scope" ISNULL AND

797 "unit_id" NOTNULL AND

798 "area_id" NOTNULL AND

799 "policy_id" NOTNULL AND

800 "issue_id" NOTNULL AND

801 "state" NOTNULL AND

802 "initiative_id" NOTNULL AND

803 "draft_id" NOTNULL AND

804 "suggestion_id" ISNULL AND

805 "boolean_value" ISNULL AND

806 "numeric_value" ISNULL AND

807 "text_value" ISNULL AND

808 "old_text_value" ISNULL ));

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

810 "event" != 'suggestion_created' OR (

811 "member_id" NOTNULL AND

812 "other_member_id" ISNULL AND

813 "scope" ISNULL AND

814 "unit_id" NOTNULL AND

815 "area_id" NOTNULL AND

816 "policy_id" NOTNULL AND

817 "issue_id" NOTNULL AND

818 "state" NOTNULL AND

819 "initiative_id" NOTNULL AND

820 "draft_id" ISNULL AND

821 "suggestion_id" NOTNULL AND

822 "boolean_value" ISNULL AND

823 "numeric_value" ISNULL AND

824 "text_value" ISNULL AND

825 "old_text_value" ISNULL ));

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

827 "event" != 'suggestion_deleted' OR (

828 "member_id" ISNULL AND

829 "other_member_id" ISNULL AND

830 "scope" ISNULL AND

831 "unit_id" NOTNULL AND

832 "area_id" NOTNULL AND

833 "policy_id" NOTNULL AND

834 "issue_id" NOTNULL AND

835 "state" NOTNULL AND

836 "initiative_id" NOTNULL AND

837 "draft_id" ISNULL AND

838 "suggestion_id" NOTNULL AND

839 "boolean_value" ISNULL AND

840 "numeric_value" ISNULL AND

841 "text_value" ISNULL AND

842 "old_text_value" ISNULL ));

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

844 "event" NOT IN (

845 'member_activated',

846 'member_deleted',

847 'member_profile_updated',

848 'member_image_updated'

849 ) OR (

850 "member_id" NOTNULL AND

851 "other_member_id" ISNULL AND

852 "scope" ISNULL AND

853 "unit_id" ISNULL AND

854 "area_id" ISNULL AND

855 "policy_id" ISNULL AND

856 "issue_id" ISNULL AND

857 "state" ISNULL AND

858 "initiative_id" ISNULL AND

859 "draft_id" ISNULL AND

860 "suggestion_id" ISNULL AND

861 "boolean_value" ISNULL AND

862 "numeric_value" ISNULL AND

863 "text_value" ISNULL AND

864 "old_text_value" ISNULL ));

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

866 "event" != 'member_active' OR (

867 "member_id" NOTNULL AND

868 "other_member_id" ISNULL AND

869 "scope" ISNULL AND

870 "unit_id" ISNULL AND

871 "area_id" ISNULL AND

872 "policy_id" ISNULL AND

873 "issue_id" ISNULL AND

874 "state" ISNULL AND

875 "initiative_id" ISNULL AND

876 "draft_id" ISNULL AND

877 "suggestion_id" ISNULL AND

878 "boolean_value" NOTNULL AND

879 "numeric_value" ISNULL AND

880 "text_value" ISNULL AND

881 "old_text_value" ISNULL ));

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

883 "event" != 'member_name_updated' OR (

884 "member_id" NOTNULL AND

885 "other_member_id" ISNULL AND

886 "scope" ISNULL AND

887 "unit_id" ISNULL AND

888 "area_id" ISNULL AND

889 "policy_id" ISNULL AND

890 "issue_id" ISNULL AND

891 "state" ISNULL AND

892 "initiative_id" ISNULL AND

893 "draft_id" ISNULL AND

894 "suggestion_id" ISNULL AND

895 "boolean_value" ISNULL AND

896 "numeric_value" ISNULL AND

897 "text_value" NOTNULL AND

898 "old_text_value" NOTNULL ));

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

900 "event" != 'interest' OR (

901 "member_id" NOTNULL AND

902 "other_member_id" ISNULL AND

903 "scope" ISNULL AND

904 "unit_id" NOTNULL AND

905 "area_id" NOTNULL AND

906 "policy_id" NOTNULL AND

907 "issue_id" NOTNULL AND

908 "state" NOTNULL AND

909 "initiative_id" ISNULL AND

910 "draft_id" ISNULL AND

911 "suggestion_id" ISNULL AND

912 "boolean_value" NOTNULL AND

913 "numeric_value" ISNULL AND

914 "text_value" ISNULL AND

915 "old_text_value" ISNULL ));

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

917 "event" != 'initiator' OR (

918 "member_id" NOTNULL AND

919 "other_member_id" ISNULL AND

920 "scope" ISNULL AND

921 "unit_id" NOTNULL AND

922 "area_id" NOTNULL AND

923 "policy_id" NOTNULL AND

924 "issue_id" NOTNULL AND

925 "state" NOTNULL AND

926 "initiative_id" NOTNULL AND

927 "draft_id" ISNULL AND

928 "suggestion_id" ISNULL AND

929 "boolean_value" NOTNULL AND

930 "numeric_value" ISNULL AND

931 "text_value" ISNULL AND

932 "old_text_value" ISNULL ));

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

934 "event" != 'support' OR (

935 "member_id" NOTNULL AND

936 "other_member_id" ISNULL AND

937 "scope" ISNULL AND

938 "unit_id" NOTNULL AND

939 "area_id" NOTNULL AND

940 "policy_id" NOTNULL AND

941 "issue_id" NOTNULL AND

942 "state" NOTNULL AND

943 "initiative_id" NOTNULL AND

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

945 "suggestion_id" ISNULL AND

946 "boolean_value" NOTNULL AND

947 "numeric_value" ISNULL AND

948 "text_value" ISNULL AND

949 "old_text_value" ISNULL ));

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

951 "event" != 'support_updated' OR (

952 "member_id" NOTNULL AND

953 "other_member_id" ISNULL AND

954 "scope" ISNULL AND

955 "unit_id" NOTNULL AND

956 "area_id" NOTNULL AND

957 "policy_id" NOTNULL AND

958 "issue_id" NOTNULL AND

959 "state" NOTNULL AND

960 "initiative_id" NOTNULL AND

961 "draft_id" NOTNULL AND

962 "suggestion_id" ISNULL AND

963 "boolean_value" ISNULL AND

964 "numeric_value" ISNULL AND

965 "text_value" ISNULL AND

966 "old_text_value" ISNULL ));

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

968 "event" != 'suggestion_rated' OR (

969 "member_id" NOTNULL AND

970 "other_member_id" ISNULL AND

971 "scope" ISNULL AND

972 "unit_id" NOTNULL AND

973 "area_id" NOTNULL AND

974 "policy_id" NOTNULL AND

975 "issue_id" NOTNULL AND

976 "state" NOTNULL AND

977 "initiative_id" NOTNULL AND

978 "draft_id" ISNULL AND

979 "suggestion_id" NOTNULL AND

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

981 "numeric_value" NOTNULL AND

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

983 "text_value" ISNULL AND

984 "old_text_value" ISNULL ));

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

986 "event" != 'delegation' OR (

987 "member_id" NOTNULL AND

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

989 "scope" NOTNULL AND

990 "unit_id" NOTNULL AND

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

992 "policy_id" ISNULL AND

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

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

995 "initiative_id" ISNULL AND

996 "draft_id" ISNULL AND

997 "suggestion_id" ISNULL AND

998 "boolean_value" NOTNULL AND

999 "numeric_value" ISNULL AND

1000 "text_value" ISNULL AND

1001 "old_text_value" ISNULL ));

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

1003 "event" != 'contact' OR (

1004 "member_id" NOTNULL AND

1005 "other_member_id" NOTNULL AND

1006 "scope" ISNULL AND

1007 "unit_id" ISNULL AND

1008 "area_id" ISNULL AND

1009 "policy_id" ISNULL AND

1010 "issue_id" ISNULL AND

1011 "state" ISNULL AND

1012 "initiative_id" ISNULL AND

1013 "draft_id" ISNULL AND

1014 "suggestion_id" ISNULL AND

1015 "boolean_value" NOTNULL AND

1016 "numeric_value" ISNULL AND

1017 "text_value" ISNULL AND

1018 "old_text_value" ISNULL ));

1019

1020

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

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

1023

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

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

1026

1027

1028 CREATE FUNCTION "write_event_unit_trigger"()

1029 RETURNS TRIGGER

1030 LANGUAGE 'plpgsql' VOLATILE AS $$

1031 DECLARE

1032 "event_v" "event_type";

1033 BEGIN

1034 IF TG_OP = 'UPDATE' THEN

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

1036 RETURN NULL;

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

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

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

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

1041 ELSIF OLD != NEW THEN

1042 "event_v" := 'unit_updated';

1043 ELSE

1044 RETURN NULL;

1045 END IF;

1046 ELSE

1047 "event_v" := 'unit_created';

1048 END IF;

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

1050 RETURN NULL;

1051 END;

1052 $$;

1053

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

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

1056

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

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

1059

1060

1061 CREATE FUNCTION "write_event_area_trigger"()

1062 RETURNS TRIGGER

1063 LANGUAGE 'plpgsql' VOLATILE AS $$

1064 DECLARE

1065 "event_v" "event_type";

1066 BEGIN

1067 IF TG_OP = 'UPDATE' THEN

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

1069 RETURN NULL;

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

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

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

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

1074 ELSIF OLD != NEW THEN

1075 "event_v" := 'area_updated';

1076 ELSE

1077 RETURN NULL;

1078 END IF;

1079 ELSE

1080 "event_v" := 'area_created';

1081 END IF;

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

1083 RETURN NULL;

1084 END;

1085 $$;

1086

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

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

1089

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

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

1092

1093

1094 CREATE FUNCTION "write_event_policy_trigger"()

1095 RETURNS TRIGGER

1096 LANGUAGE 'plpgsql' VOLATILE AS $$

1097 DECLARE

1098 "event_v" "event_type";

1099 BEGIN

1100 IF TG_OP = 'UPDATE' THEN

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

1102 RETURN NULL;

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

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

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

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

1107 ELSIF OLD != NEW THEN

1108 "event_v" := 'policy_updated';

1109 ELSE

1110 RETURN NULL;

1111 END IF;

1112 ELSE

1113 "event_v" := 'policy_created';

1114 END IF;

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

1116 RETURN NULL;

1117 END;

1118 $$;

1119

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

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

1122

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

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

1125

1126

1127 CREATE OR REPLACE FUNCTION "write_event_issue_state_changed_trigger"()

1128 RETURNS TRIGGER

1129 LANGUAGE 'plpgsql' VOLATILE AS $$

1130 DECLARE

1131 "area_row" "area"%ROWTYPE;

1132 BEGIN

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

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

1135 FOR SHARE;

1136 INSERT INTO "event" (

1137 "event",

1138 "unit_id", "area_id", "policy_id", "issue_id", "state"

1139 ) VALUES (

1140 'issue_state_changed',

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

1142 NEW."id", NEW."state"

1143 );

1144 END IF;

1145 RETURN NULL;

1146 END;

1147 $$;

1148

1149

1150 CREATE OR REPLACE FUNCTION "write_event_initiative_or_draft_created_trigger"()

1151 RETURNS TRIGGER

1152 LANGUAGE 'plpgsql' VOLATILE AS $$

1153 DECLARE

1154 "initiative_row" "initiative"%ROWTYPE;

1155 "issue_row" "issue"%ROWTYPE;

1156 "area_row" "area"%ROWTYPE;

1157 "event_v" "event_type";

1158 BEGIN

1159 SELECT * INTO "initiative_row" FROM "initiative"

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

1161 SELECT * INTO "issue_row" FROM "issue"

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

1163 SELECT * INTO "area_row" FROM "area"

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

1165 IF EXISTS (

1166 SELECT NULL FROM "draft"

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

1168 FOR SHARE

1169 ) THEN

1170 "event_v" := 'new_draft_created';

1171 ELSE

1172 IF EXISTS (

1173 SELECT NULL FROM "initiative"

1174 WHERE "issue_id" = "initiative_row"."issue_id"

1175 AND "id" != "initiative_row"."id"

1176 FOR SHARE

1177 ) THEN

1178 "event_v" := 'initiative_created_in_existing_issue';

1179 ELSE

1180 "event_v" := 'initiative_created_in_new_issue';

1181 END IF;

1182 END IF;

1183 INSERT INTO "event" (

1184 "event", "member_id",

1185 "unit_id", "area_id", "policy_id", "issue_id", "state",

1186 "initiative_id", "draft_id"

1187 ) VALUES (

1188 "event_v", NEW."author_id",

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

1190 "initiative_row"."issue_id", "issue_row"."state",

1191 NEW."initiative_id", NEW."id"

1192 );

1193 RETURN NULL;

1194 END;

1195 $$;

1196

1197

1198 CREATE OR REPLACE FUNCTION "write_event_initiative_revoked_trigger"()

1199 RETURNS TRIGGER

1200 LANGUAGE 'plpgsql' VOLATILE AS $$

1201 DECLARE

1202 "issue_row" "issue"%ROWTYPE;

1203 "area_row" "area"%ROWTYPE;

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

1205 BEGIN

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

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

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

1209 SELECT * INTO "issue_row" FROM "issue"

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

1211 SELECT * INTO "area_row" FROM "area"

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

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

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

1215 SELECT "id" INTO "draft_id_v" FROM "current_draft"

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

1217 INSERT INTO "event" (

1218 "event", "member_id",

1219 "unit_id", "area_id", "policy_id", "issue_id", "state",

1220 "initiative_id", "draft_id"

1221 ) VALUES (

1222 'initiative_revoked', NEW."revoked_by_member_id",

1223 "area_row"."unit_id", "issue_row"."area_id",

1224 "issue_row"."policy_id",

1225 NEW."issue_id", "issue_row"."state",

1226 NEW."id", "draft_id_v"

1227 );

1228 END IF;

1229 RETURN NULL;

1230 END;

1231 $$;

1232

1233

1234 CREATE OR REPLACE FUNCTION "write_event_suggestion_created_trigger"()

1235 RETURNS TRIGGER

1236 LANGUAGE 'plpgsql' VOLATILE AS $$

1237 DECLARE

1238 "initiative_row" "initiative"%ROWTYPE;

1239 "issue_row" "issue"%ROWTYPE;

1240 "area_row" "area"%ROWTYPE;

1241 BEGIN

1242 SELECT * INTO "initiative_row" FROM "initiative"

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

1244 SELECT * INTO "issue_row" FROM "issue"

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

1246 SELECT * INTO "area_row" FROM "area"

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

1248 INSERT INTO "event" (

1249 "event", "member_id",

1250 "unit_id", "area_id", "policy_id", "issue_id", "state",

1251 "initiative_id", "suggestion_id"

1252 ) VALUES (

1253 'suggestion_created', NEW."author_id",

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

1255 "initiative_row"."issue_id", "issue_row"."state",

1256 NEW."initiative_id", NEW."id"

1257 );

1258 RETURN NULL;

1259 END;

1260 $$;

1261

1262

1263 CREATE FUNCTION "write_event_suggestion_removed_trigger"()

1264 RETURNS TRIGGER

1265 LANGUAGE 'plpgsql' VOLATILE AS $$

1266 DECLARE

1267 "initiative_row" "initiative"%ROWTYPE;

1268 "issue_row" "issue"%ROWTYPE;

1269 "area_row" "area"%ROWTYPE;

1270 BEGIN

1271 SELECT * INTO "initiative_row" FROM "initiative"

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

1273 IF "initiative_row"."id" NOTNULL THEN

1274 SELECT * INTO "issue_row" FROM "issue"

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

1276 SELECT * INTO "area_row" FROM "area"

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

1278 INSERT INTO "event" (

1279 "event",

1280 "unit_id", "area_id", "policy_id", "issue_id", "state",

1281 "initiative_id", "suggestion_id"

1282 ) VALUES (

1283 'suggestion_deleted',

1284 "area_row"."unit_id", "issue_row"."area_id",

1285 "issue_row"."policy_id",

1286 "initiative_row"."issue_id", "issue_row"."state",

1287 OLD."initiative_id", OLD."id"

1288 );

1289 END IF;

1290 RETURN NULL;

1291 END;

1292 $$;

1293

1294 CREATE TRIGGER "write_event_suggestion_removed"

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

1296 "write_event_suggestion_removed_trigger"();

1297

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

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

1300

1301

1302 CREATE FUNCTION "write_event_member_trigger"()

1303 RETURNS TRIGGER

1304 LANGUAGE 'plpgsql' VOLATILE AS $$

1305 BEGIN

1306 IF TG_OP = 'INSERT' THEN

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

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

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

1310 END IF;

1311 IF NEW."active" THEN

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

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

1314 END IF;

1315 ELSIF TG_OP = 'UPDATE' THEN

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

1317 RAISE EXCEPTION 'Cannot change member ID';

1318 END IF;

1319 IF

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

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

1322 THEN

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

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

1325 END IF;

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

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

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

1329 );

1330 END IF;

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

1332 INSERT INTO "event" (

1333 "event", "member_id", "text_value", "old_text_value"

1334 ) VALUES (

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

1336 );

1337 END IF;

1338 IF

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

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

1341 THEN

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

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

1344 END IF;

1345 END IF;

1346 RETURN NULL;

1347 END;

1348 $$;

1349

1350 CREATE TRIGGER "write_event_member"

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

1352 "write_event_member_trigger"();

1353

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

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

1356

1357

1358 CREATE FUNCTION "write_event_member_profile_updated_trigger"()

1359 RETURNS TRIGGER

1360 LANGUAGE 'plpgsql' VOLATILE AS $$

1361 BEGIN

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

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

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

1365 'member_profile_updated', OLD."member_id"

1366 );

1367 END IF;

1368 END IF;

1369 IF TG_OP = 'UPDATE' THEN

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

1371 RETURN NULL;

1372 END IF;

1373 END IF;

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

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

1376 'member_profile_updated', NEW."member_id"

1377 );

1378 END IF;

1379 RETURN NULL;

1380 END;

1381 $$;

1382

1383 CREATE TRIGGER "write_event_member_profile_updated"

1384 AFTER INSERT OR UPDATE OR DELETE ON "member_profile"

1385 FOR EACH ROW EXECUTE PROCEDURE

1386 "write_event_member_profile_updated_trigger"();

1387

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

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

1390

1391

1392 CREATE FUNCTION "write_event_member_image_updated_trigger"()

1393 RETURNS TRIGGER

1394 LANGUAGE 'plpgsql' VOLATILE AS $$

1395 BEGIN

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

1397 IF NOT OLD."scaled" THEN

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

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

1400 'member_image_updated', OLD."member_id"

1401 );

1402 END IF;

1403 END IF;

1404 END IF;

1405 IF TG_OP = 'UPDATE' THEN

1406 IF

1407 OLD."member_id" = NEW."member_id" AND

1408 OLD."scaled" = NEW."scaled"

1409 THEN

1410 RETURN NULL;

1411 END IF;

1412 END IF;

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

1414 IF NOT NEW."scaled" THEN

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

1416 'member_image_updated', NEW."member_id"

1417 );

1418 END IF;

1419 END IF;

1420 RETURN NULL;

1421 END;

1422 $$;

1423

1424 CREATE TRIGGER "write_event_member_image_updated"

1425 AFTER INSERT OR UPDATE OR DELETE ON "member_image"

1426 FOR EACH ROW EXECUTE PROCEDURE

1427 "write_event_member_image_updated_trigger"();

1428

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

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

1431

1432

1433 CREATE FUNCTION "write_event_interest_trigger"()

1434 RETURNS TRIGGER

1435 LANGUAGE 'plpgsql' VOLATILE AS $$

1436 DECLARE

1437 "issue_row" "issue"%ROWTYPE;

1438 "area_row" "area"%ROWTYPE;

1439 BEGIN

1440 IF TG_OP = 'UPDATE' THEN

1441 IF OLD = NEW THEN

1442 RETURN NULL;

1443 END IF;

1444 END IF;

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

1446 SELECT * INTO "issue_row" FROM "issue"

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

1448 SELECT * INTO "area_row" FROM "area"

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

1450 IF "issue_row"."id" NOTNULL THEN

1451 INSERT INTO "event" (

1452 "event", "member_id",

1453 "unit_id", "area_id", "policy_id", "issue_id", "state",

1454 "boolean_value"

1455 ) VALUES (

1456 'interest', OLD."member_id",

1457 "area_row"."unit_id", "issue_row"."area_id",

1458 "issue_row"."policy_id",

1459 OLD."issue_id", "issue_row"."state",

1460 FALSE

1461 );

1462 END IF;

1463 END IF;

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

1465 SELECT * INTO "issue_row" FROM "issue"

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

1467 SELECT * INTO "area_row" FROM "area"

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

1469 INSERT INTO "event" (

1470 "event", "member_id",

1471 "unit_id", "area_id", "policy_id", "issue_id", "state",

1472 "boolean_value"

1473 ) VALUES (

1474 'interest', NEW."member_id",

1475 "area_row"."unit_id", "issue_row"."area_id",

1476 "issue_row"."policy_id",

1477 NEW."issue_id", "issue_row"."state",

1478 TRUE

1479 );

1480 END IF;

1481 RETURN NULL;

1482 END;

1483 $$;

1484

1485 CREATE TRIGGER "write_event_interest"

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

1487 "write_event_interest_trigger"();

1488

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

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

1491

1492

1493 CREATE FUNCTION "write_event_initiator_trigger"()

1494 RETURNS TRIGGER

1495 LANGUAGE 'plpgsql' VOLATILE AS $$

1496 DECLARE

1497 "initiative_row" "initiative"%ROWTYPE;

1498 "issue_row" "issue"%ROWTYPE;

1499 "area_row" "area"%ROWTYPE;

1500 "accepted_v" BOOLEAN = FALSE;

1501 "rejected_v" BOOLEAN = FALSE;

1502 BEGIN

1503 IF TG_OP = 'UPDATE' THEN

1504 IF

1505 OLD."initiative_id" = NEW."initiative_id" AND

1506 OLD."member_id" = NEW."member_id"

1507 THEN

1508 IF

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

1510 THEN

1511 RETURN NULL;

1512 END IF;

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

1514 "accepted_v" := TRUE;

1515 ELSE

1516 "rejected_v" := TRUE;

1517 END IF;

1518 END IF;

1519 END IF;

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

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

1522 SELECT * INTO "initiative_row" FROM "initiative"

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

1524 IF "initiative_row"."id" NOTNULL THEN

1525 SELECT * INTO "issue_row" FROM "issue"

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

1527 SELECT * INTO "area_row" FROM "area"

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

1529 INSERT INTO "event" (

1530 "event", "member_id",

1531 "unit_id", "area_id", "policy_id", "issue_id", "state",

1532 "initiative_id", "boolean_value"

1533 ) VALUES (

1534 'initiator', OLD."member_id",

1535 "area_row"."unit_id", "issue_row"."area_id",

1536 "issue_row"."policy_id",

1537 "issue_row"."id", "issue_row"."state",

1538 OLD."initiative_id", FALSE

1539 );

1540 END IF;

1541 END IF;

1542 END IF;

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

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

1545 SELECT * INTO "initiative_row" FROM "initiative"

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

1547 SELECT * INTO "issue_row" FROM "issue"

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

1549 SELECT * INTO "area_row" FROM "area"

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

1551 INSERT INTO "event" (

1552 "event", "member_id",

1553 "unit_id", "area_id", "policy_id", "issue_id", "state",

1554 "initiative_id", "boolean_value"

1555 ) VALUES (

1556 'initiator', NEW."member_id",

1557 "area_row"."unit_id", "issue_row"."area_id",

1558 "issue_row"."policy_id",

1559 "issue_row"."id", "issue_row"."state",

1560 NEW."initiative_id", TRUE

1561 );

1562 END IF;

1563 END IF;

1564 RETURN NULL;

1565 END;

1566 $$;

1567

1568 CREATE TRIGGER "write_event_initiator"

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

1570 "write_event_initiator_trigger"();

1571

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

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

1574

1575

1576 CREATE FUNCTION "write_event_support_trigger"()

1577 RETURNS TRIGGER

1578 LANGUAGE 'plpgsql' VOLATILE AS $$

1579 DECLARE

1580 "issue_row" "issue"%ROWTYPE;

1581 "area_row" "area"%ROWTYPE;

1582 BEGIN

1583 IF TG_OP = 'UPDATE' THEN

1584 IF

1585 OLD."initiative_id" = NEW."initiative_id" AND

1586 OLD."member_id" = NEW."member_id"

1587 THEN

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

1589 SELECT * INTO "issue_row" FROM "issue"

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

1591 SELECT * INTO "area_row" FROM "area"

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

1593 INSERT INTO "event" (

1594 "event", "member_id",

1595 "unit_id", "area_id", "policy_id", "issue_id", "state",

1596 "initiative_id", "draft_id"

1597 ) VALUES (

1598 'support_updated', NEW."member_id",

1599 "area_row"."unit_id", "issue_row"."area_id",

1600 "issue_row"."policy_id",

1601 "issue_row"."id", "issue_row"."state",

1602 NEW."initiative_id", NEW."draft_id"

1603 );

1604 END IF;

1605 RETURN NULL;

1606 END IF;

1607 END IF;

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

1609 IF EXISTS (

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

1611 FOR SHARE

1612 ) THEN

1613 SELECT * INTO "issue_row" FROM "issue"

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

1615 SELECT * INTO "area_row" FROM "area"

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

1617 INSERT INTO "event" (

1618 "event", "member_id",

1619 "unit_id", "area_id", "policy_id", "issue_id", "state",

1620 "initiative_id", "boolean_value"

1621 ) VALUES (

1622 'support', OLD."member_id",

1623 "area_row"."unit_id", "issue_row"."area_id",

1624 "issue_row"."policy_id",

1625 "issue_row"."id", "issue_row"."state",

1626 OLD."initiative_id", FALSE

1627 );

1628 END IF;

1629 END IF;

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

1631 SELECT * INTO "issue_row" FROM "issue"

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

1633 SELECT * INTO "area_row" FROM "area"

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

1635 INSERT INTO "event" (

1636 "event", "member_id",

1637 "unit_id", "area_id", "policy_id", "issue_id", "state",

1638 "initiative_id", "draft_id", "boolean_value"

1639 ) VALUES (

1640 'support', NEW."member_id",

1641 "area_row"."unit_id", "issue_row"."area_id",

1642 "issue_row"."policy_id",

1643 "issue_row"."id", "issue_row"."state",

1644 NEW."initiative_id", NEW."draft_id", TRUE

1645 );

1646 END IF;

1647 RETURN NULL;

1648 END;

1649 $$;

1650

1651 CREATE TRIGGER "write_event_support"

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

1653 "write_event_support_trigger"();

1654

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

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

1657

1658

1659 CREATE FUNCTION "write_event_suggestion_rated_trigger"()

1660 RETURNS TRIGGER

1661 LANGUAGE 'plpgsql' VOLATILE AS $$

1662 DECLARE

1663 "same_pkey_v" BOOLEAN = FALSE;

1664 "initiative_row" "initiative"%ROWTYPE;

1665 "issue_row" "issue"%ROWTYPE;

1666 "area_row" "area"%ROWTYPE;

1667 BEGIN

1668 IF TG_OP = 'UPDATE' THEN

1669 IF

1670 OLD."suggestion_id" = NEW."suggestion_id" AND

1671 OLD."member_id" = NEW."member_id"

1672 THEN

1673 IF

1674 OLD."degree" = NEW."degree" AND

1675 OLD."fulfilled" = NEW."fulfilled"

1676 THEN

1677 RETURN NULL;

1678 END IF;

1679 "same_pkey_v" := TRUE;

1680 END IF;

1681 END IF;

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

1683 IF EXISTS (

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

1685 FOR SHARE

1686 ) THEN

1687 SELECT * INTO "initiative_row" FROM "initiative"

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

1689 SELECT * INTO "issue_row" FROM "issue"

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

1691 SELECT * INTO "area_row" FROM "area"

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

1693 INSERT INTO "event" (

1694 "event", "member_id",

1695 "unit_id", "area_id", "policy_id", "issue_id", "state",

1696 "initiative_id", "suggestion_id",

1697 "boolean_value", "numeric_value"

1698 ) VALUES (

1699 'suggestion_rated', OLD."member_id",

1700 "area_row"."unit_id", "issue_row"."area_id",

1701 "issue_row"."policy_id",

1702 "initiative_row"."issue_id", "issue_row"."state",

1703 OLD."initiative_id", OLD."suggestion_id",

1704 NULL, 0

1705 );

1706 END IF;

1707 END IF;

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

1709 SELECT * INTO "initiative_row" FROM "initiative"

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

1711 SELECT * INTO "issue_row" FROM "issue"

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

1713 SELECT * INTO "area_row" FROM "area"

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

1715 INSERT INTO "event" (

1716 "event", "member_id",

1717 "unit_id", "area_id", "policy_id", "issue_id", "state",

1718 "initiative_id", "suggestion_id",

1719 "boolean_value", "numeric_value"

1720 ) VALUES (

1721 'suggestion_rated', NEW."member_id",

1722 "area_row"."unit_id", "issue_row"."area_id",

1723 "issue_row"."policy_id",

1724 "initiative_row"."issue_id", "issue_row"."state",

1725 NEW."initiative_id", NEW."suggestion_id",

1726 NEW."fulfilled", NEW."degree"

1727 );

1728 END IF;

1729 RETURN NULL;

1730 END;

1731 $$;

1732

1733 CREATE TRIGGER "write_event_suggestion_rated"

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

1735 "write_event_suggestion_rated_trigger"();

1736

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

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

1739

1740

1741 CREATE FUNCTION "write_event_delegation_trigger"()

1742 RETURNS TRIGGER

1743 LANGUAGE 'plpgsql' VOLATILE AS $$

1744 DECLARE

1745 "issue_row" "issue"%ROWTYPE;

1746 "area_row" "area"%ROWTYPE;

1747 BEGIN

1748 IF TG_OP = 'DELETE' THEN

1749 IF EXISTS (

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

1751 ) AND (CASE OLD."scope"

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

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

1754 )

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

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

1757 )

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

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

1760 )

1761 END) THEN

1762 SELECT * INTO "issue_row" FROM "issue"

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

1764 SELECT * INTO "area_row" FROM "area"

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

1766 FOR SHARE;

1767 INSERT INTO "event" (

1768 "event", "member_id", "scope",

1769 "unit_id", "area_id", "issue_id", "state",

1770 "boolean_value"

1771 ) VALUES (

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

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

1774 OLD."issue_id", "issue_row"."state",

1775 FALSE

1776 );

1777 END IF;

1778 ELSE

1779 SELECT * INTO "issue_row" FROM "issue"

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

1781 SELECT * INTO "area_row" FROM "area"

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

1783 FOR SHARE;

1784 INSERT INTO "event" (

1785 "event", "member_id", "other_member_id", "scope",

1786 "unit_id", "area_id", "issue_id", "state",

1787 "boolean_value"

1788 ) VALUES (

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

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

1791 NEW."issue_id", "issue_row"."state",

1792 TRUE

1793 );

1794 END IF;

1795 RETURN NULL;

1796 END;

1797 $$;

1798

1799 CREATE TRIGGER "write_event_delegation"

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

1801 "write_event_delegation_trigger"();

1802

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

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

1805

1806

1807 CREATE FUNCTION "write_event_contact_trigger"()

1808 RETURNS TRIGGER

1809 LANGUAGE 'plpgsql' VOLATILE AS $$

1810 BEGIN

1811 IF TG_OP = 'UPDATE' THEN

1812 IF

1813 OLD."member_id" = NEW."member_id" AND

1814 OLD."other_member_id" = NEW."other_member_id" AND

1815 OLD."public" = NEW."public"

1816 THEN

1817 RETURN NULL;

1818 END IF;

1819 END IF;

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

1821 IF OLD."public" THEN

1822 IF EXISTS (

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

1824 FOR SHARE

1825 ) AND EXISTS (

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

1827 FOR SHARE

1828 ) THEN

1829 INSERT INTO "event" (

1830 "event", "member_id", "other_member_id", "boolean_value"

1831 ) VALUES (

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

1833 );

1834 END IF;

1835 END IF;

1836 END IF;

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

1838 IF NEW."public" THEN

1839 INSERT INTO "event" (

1840 "event", "member_id", "other_member_id", "boolean_value"

1841 ) VALUES (

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

1843 );

1844 END IF;

1845 END IF;

1846 RETURN NULL;

1847 END;

1848 $$;

1849

1850 CREATE TRIGGER "write_event_contact"

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

1852 "write_event_contact_trigger"();

1853

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

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

1856

1857

1858 CREATE FUNCTION "send_event_notify_trigger"()

1859 RETURNS TRIGGER

1860 LANGUAGE 'plpgsql' VOLATILE AS $$

1861 BEGIN

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

1863 RETURN NULL;

1864 END;

1865 $$;

1866

1867 CREATE TRIGGER "send_notify"

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

1869 "send_event_notify_trigger"();

1870

1871

1872 CREATE FUNCTION "delete_extended_scope_tokens_trigger"()

1873 RETURNS TRIGGER

1874 LANGUAGE 'plpgsql' VOLATILE AS $$

1875 DECLARE

1876 "system_application_row" "system_application"%ROWTYPE;

1877 BEGIN

1878 IF OLD."system_application_id" NOTNULL THEN

1879 SELECT * FROM "system_application" INTO "system_application_row"

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

1881 DELETE FROM "token"

1882 WHERE "member_id" = OLD."member_id"

1883 AND "system_application_id" = OLD."system_application_id"

1884 AND NOT COALESCE(

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

1886 regexp_split_to_array(

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

1888 ),

1889 FALSE

1890 );

1891 END IF;

1892 RETURN OLD;

1893 END;

1894 $$;

1895

1896 CREATE TRIGGER "delete_extended_scope_tokens"

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

1898 "delete_extended_scope_tokens_trigger"();

1899

1900

1901 CREATE FUNCTION "detach_token_from_session_trigger"()

1902 RETURNS TRIGGER

1903 LANGUAGE 'plpgsql' VOLATILE AS $$

1904 BEGIN

1905 UPDATE "token" SET "session_id" = NULL

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

1907 RETURN OLD;

1908 END;

1909 $$;

1910

1911 CREATE TRIGGER "detach_token_from_session"

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

1913 "detach_token_from_session_trigger"();

1914

1915

1916 CREATE FUNCTION "delete_non_detached_scope_with_session_trigger"()

1917 RETURNS TRIGGER

1918 LANGUAGE 'plpgsql' VOLATILE AS $$

1919 BEGIN

1920 IF NEW."session_id" ISNULL THEN

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

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

1923 WHERE "element" LIKE '%_detached';

1924 END IF;

1925 RETURN NEW;

1926 END;

1927 $$;

1928

1929 CREATE TRIGGER "delete_non_detached_scope_with_session"

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

1931 "delete_non_detached_scope_with_session_trigger"();

1932

1933

1934 CREATE FUNCTION "delete_token_with_empty_scope_trigger"()

1935 RETURNS TRIGGER

1936 LANGUAGE 'plpgsql' VOLATILE AS $$

1937 BEGIN

1938 IF NEW."scope" = '' THEN

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

1940 END IF;

1941 RETURN NULL;

1942 END;

1943 $$;

1944

1945 CREATE TRIGGER "delete_token_with_empty_scope"

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

1947 "delete_token_with_empty_scope_trigger"();

1948

1949

1950 CREATE FUNCTION "delete_snapshot_on_partial_delete_trigger"()

1951 RETURNS TRIGGER

1952 LANGUAGE 'plpgsql' VOLATILE AS $$

1953 BEGIN

1954 IF TG_OP = 'UPDATE' THEN

1955 IF

1956 OLD."snapshot_id" = NEW."snapshot_id" AND

1957 OLD."issue_id" = NEW."issue_id"

1958 THEN

1959 RETURN NULL;

1960 END IF;

1961 END IF;

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

1963 RETURN NULL;

1964 END;

1965 $$;

1966

1967 CREATE TRIGGER "delete_snapshot_on_partial_delete"

1968 AFTER UPDATE OR DELETE ON "snapshot_issue"

1969 FOR EACH ROW EXECUTE PROCEDURE

1970 "delete_snapshot_on_partial_delete_trigger"();

1971

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

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

1974

1975

1976 CREATE FUNCTION "copy_current_draft_data"

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

1978 RETURNS VOID

1979 LANGUAGE 'plpgsql' VOLATILE AS $$

1980 BEGIN

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

1982 FOR UPDATE;

1983 UPDATE "initiative" SET

1984 "location" = "draft"."location",

1985 "draft_text_search_data" = "draft"."text_search_data"

1986 FROM "current_draft" AS "draft"

1987 WHERE "initiative"."id" = "initiative_id_p"

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

1989 END;

1990 $$;

1991

1992 COMMENT ON FUNCTION "copy_current_draft_data"

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

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

1995

1996

1997 CREATE FUNCTION "copy_current_draft_data_trigger"()

1998 RETURNS TRIGGER

1999 LANGUAGE 'plpgsql' VOLATILE AS $$

2000 BEGIN

2001 IF TG_OP='DELETE' THEN

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

2003 ELSE

2004 IF TG_OP='UPDATE' THEN

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

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

2007 END IF;

2008 END IF;

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

2010 END IF;

2011 RETURN NULL;

2012 END;

2013 $$;

2014

2015 CREATE TRIGGER "copy_current_draft_data"

2016 AFTER INSERT OR UPDATE OR DELETE ON "draft"

2017 FOR EACH ROW EXECUTE PROCEDURE

2018 "copy_current_draft_data_trigger"();

2019

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

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

2022

2023

2024 CREATE VIEW "area_quorum" AS

2025 SELECT

2026 "area"."id" AS "area_id",

2027 ceil(

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

2029 coalesce(

2030 ( SELECT sum(

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

2032 extract(epoch from

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

2034 "issue"."discussion_time" +

2035 "issue"."verification_time" +

2036 "issue"."voting_time"

2037 )::FLOAT8

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

2039 )

2040 FROM "issue" JOIN "policy"

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

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

2043 AND "issue"."accepted" NOTNULL

2044 AND "issue"."closed" ISNULL

2045 AND "policy"."polling" = FALSE

2046 )::FLOAT8, 0::FLOAT8

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

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

2049 SELECT "snapshot"."population"

2050 FROM "snapshot"

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

2052 AND "snapshot"."issue_id" ISNULL

2053 ORDER BY "snapshot"."id" DESC

2054 LIMIT 1

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

2056

2057 )::INT4 AS "issue_quorum"

2058 FROM "area";

2059

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

2061

2062

2063 CREATE VIEW "issue_quorum" AS

2064 SELECT DISTINCT ON ("issue_id")

2065 "issue"."id" AS "issue_id",

2066 "subquery"."issue_quorum"

2067 FROM "issue"

2068 CROSS JOIN LATERAL (

2069 SELECT "area_quorum"."issue_quorum"

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

2071 UNION ALL

2072 SELECT "policy"."issue_quorum"

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

2074 UNION ALL

2075 SELECT

2076 ceil(

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

2078 "policy"."issue_quorum_den"::FLOAT8

2079 )::INT4

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

2081 ) AS "subquery"

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

2083

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

2085

2086

2087 CREATE VIEW "area_with_unaccepted_issues" AS

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

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

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

2091

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

2093

2094

2095 DROP VIEW "area_member_count";

2096

2097

2098 DROP TABLE "membership";

2099

2100

2101 DROP FUNCTION "membership_weight"

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

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

2104

2105

2106 DROP FUNCTION "membership_weight_with_skipping"

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

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

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

2110

2111

2112 CREATE OR REPLACE VIEW "issue_delegation" AS

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

2114 "issue"."id" AS "issue_id",

2115 "delegation"."id",

2116 "delegation"."truster_id",

2117 "delegation"."trustee_id",

2118 "delegation"."scope"

2119 FROM "issue"

2120 JOIN "area"

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

2122 JOIN "delegation"

2123 ON "delegation"."unit_id" = "area"."unit_id"

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

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

2126 JOIN "member"

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

2128 JOIN "privilege"

2129 ON "area"."unit_id" = "privilege"."unit_id"

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

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

2132 ORDER BY

2133 "issue"."id",

2134 "delegation"."truster_id",

2135 "delegation"."scope" DESC;

2136

2137

2138 CREATE VIEW "unit_member" AS

2139 SELECT

2140 "unit"."id" AS "unit_id",

2141 "member"."id" AS "member_id"

2142 FROM "privilege"

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

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

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

2146

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

2148

2149

2150 CREATE OR REPLACE VIEW "unit_member_count" AS

2151 SELECT

2152 "unit"."id" AS "unit_id",

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

2154 FROM "unit" LEFT JOIN "unit_member"

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

2156 GROUP BY "unit"."id";

2157

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

2159

2160

2161 CREATE OR REPLACE VIEW "opening_draft" AS

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

2163 ORDER BY "initiative_id", "id";

2164

2165

2166 CREATE OR REPLACE VIEW "current_draft" AS

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

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

2169

2170

2171 CREATE OR REPLACE VIEW "issue_supporter_in_admission_state" AS

2172 SELECT

2173 "area"."unit_id",

2174 "issue"."area_id",

2175 "issue"."id" AS "issue_id",

2176 "supporter"."member_id",

2177 "direct_interest_snapshot"."weight"

2178 FROM "issue"

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

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

2181 JOIN "direct_interest_snapshot"

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

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

2184 AND "direct_interest_snapshot"."member_id" = "supporter"."member_id"

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

2186

2187

2188 CREATE OR REPLACE VIEW "individual_suggestion_ranking" AS

2189 SELECT

2190 "opinion"."initiative_id",

2191 "opinion"."member_id",

2192 "direct_interest_snapshot"."weight",

2193 CASE WHEN

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

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

2196 THEN 1 ELSE

2197 CASE WHEN

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

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

2200 THEN 2 ELSE

2201 CASE WHEN

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

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

2204 THEN 3 ELSE 4 END

2205 END

2206 END AS "preference",

2207 "opinion"."suggestion_id"

2208 FROM "opinion"

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

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

2211 JOIN "direct_interest_snapshot"

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

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

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

2215

2216

2217 CREATE VIEW "expired_session" AS

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

2219

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

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

2222

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

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

2225

2226

2227 CREATE VIEW "expired_token" AS

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

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

2230 SELECT NULL FROM "token" AS "other"

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

2232

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

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

2235

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

2237

2238

2239 CREATE VIEW "unused_snapshot" AS

2240 SELECT "snapshot".* FROM "snapshot"

2241 LEFT JOIN "issue"

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

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

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

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

2246 WHERE "issue"."id" ISNULL;

2247

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

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

2250

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

2252

2253

2254 CREATE VIEW "expired_snapshot" AS

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

2256 WHERE "unused_snapshot"."calculated" <

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

2258

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

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

2261

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

2263

2264

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

2266

2267

2268 CREATE OR REPLACE FUNCTION "delegation_chain"

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

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

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

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

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

2274 "simulate_default_p" BOOLEAN DEFAULT FALSE )

2275 RETURNS SETOF "delegation_chain_row"

2276 LANGUAGE 'plpgsql' STABLE AS $$

2277 DECLARE

2278 "scope_v" "delegation_scope";

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

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

2281 "issue_row" "issue"%ROWTYPE;

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

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

2284 "output_row" "delegation_chain_row";

2285 "output_rows" "delegation_chain_row"[];

2286 "simulate_v" BOOLEAN;

2287 "simulate_here_v" BOOLEAN;

2288 "delegation_row" "delegation"%ROWTYPE;

2289 "row_count" INT4;

2290 "i" INT4;

2291 "loop_v" BOOLEAN;

2292 BEGIN

2293 IF "simulate_trustee_id_p" NOTNULL AND "simulate_default_p" THEN

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

2295 END IF;

2296 IF "simulate_trustee_id_p" NOTNULL OR "simulate_default_p" THEN

2297 "simulate_v" := TRUE;

2298 ELSE

2299 "simulate_v" := FALSE;

2300 END IF;

2301 IF

2302 "unit_id_p" NOTNULL AND

2303 "area_id_p" ISNULL AND

2304 "issue_id_p" ISNULL

2305 THEN

2306 "scope_v" := 'unit';

2307 "unit_id_v" := "unit_id_p";

2308 ELSIF

2309 "unit_id_p" ISNULL AND

2310 "area_id_p" NOTNULL AND

2311 "issue_id_p" ISNULL

2312 THEN

2313 "scope_v" := 'area';

2314 "area_id_v" := "area_id_p";

2315 SELECT "unit_id" INTO "unit_id_v"

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

2317 ELSIF

2318 "unit_id_p" ISNULL AND

2319 "area_id_p" ISNULL AND

2320 "issue_id_p" NOTNULL

2321 THEN

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

2323 IF "issue_row"."id" ISNULL THEN

2324 RETURN;

2325 END IF;

2326 IF "issue_row"."closed" NOTNULL THEN

2327 IF "simulate_v" THEN

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

2329 END IF;

2330 FOR "output_row" IN

2331 SELECT * FROM

2332 "delegation_chain_for_closed_issue"("member_id_p", "issue_id_p")

2333 LOOP

2334 RETURN NEXT "output_row";

2335 END LOOP;

2336 RETURN;

2337 END IF;

2338 "scope_v" := 'issue';

2339 SELECT "area_id" INTO "area_id_v"

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

2341 SELECT "unit_id" INTO "unit_id_v"

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

2343 ELSE

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

2345 END IF;

2346 "visited_member_ids" := '{}';

2347 "loop_member_id_v" := NULL;

2348 "output_rows" := '{}';

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

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

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

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

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

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

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

2356 LOOP

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

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

2359 ELSE

2360 "visited_member_ids" :=

2361 "visited_member_ids" || "output_row"."member_id";

2362 END IF;

2363 IF "output_row"."participation" ISNULL THEN

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

2365 ELSIF "output_row"."participation" THEN

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

2367 END IF;

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

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

2370 SELECT NULL FROM "member" JOIN "privilege"

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

2372 AND "privilege"."unit_id" = "unit_id_v"

2373 WHERE "id" = "output_row"."member_id"

2374 AND "member"."active" AND "privilege"."voting_right"

2375 );

2376 "simulate_here_v" := (

2377 "simulate_v" AND

2378 "output_row"."member_id" = "member_id_p"

2379 );

2380 "delegation_row" := ROW(NULL);

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

2382 IF "scope_v" = 'unit' THEN

2383 IF NOT "simulate_here_v" THEN

2384 SELECT * INTO "delegation_row" FROM "delegation"

2385 WHERE "truster_id" = "output_row"."member_id"

2386 AND "unit_id" = "unit_id_v";

2387 END IF;

2388 ELSIF "scope_v" = 'area' THEN

2389 IF "simulate_here_v" THEN

2390 IF "simulate_trustee_id_p" ISNULL THEN

2391 SELECT * INTO "delegation_row" FROM "delegation"

2392 WHERE "truster_id" = "output_row"."member_id"

2393 AND "unit_id" = "unit_id_v";

2394 END IF;

2395 ELSE

2396 SELECT * INTO "delegation_row" FROM "delegation"

2397 WHERE "truster_id" = "output_row"."member_id"

2398 AND (

2399 "unit_id" = "unit_id_v" OR

2400 "area_id" = "area_id_v"

2401 )

2402 ORDER BY "scope" DESC;

2403 END IF;

2404 ELSIF "scope_v" = 'issue' THEN

2405 IF "issue_row"."fully_frozen" ISNULL THEN

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

2407 SELECT NULL FROM "interest"

2408 WHERE "issue_id" = "issue_id_p"

2409 AND "member_id" = "output_row"."member_id"

2410 );

2411 ELSE

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

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

2414 SELECT NULL FROM "direct_voter"

2415 WHERE "issue_id" = "issue_id_p"

2416 AND "member_id" = "output_row"."member_id"

2417 );

2418 ELSE

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

2420 END IF;

2421 END IF;

2422 IF "simulate_here_v" THEN

2423 IF "simulate_trustee_id_p" ISNULL THEN

2424 SELECT * INTO "delegation_row" FROM "delegation"

2425 WHERE "truster_id" = "output_row"."member_id"

2426 AND (

2427 "unit_id" = "unit_id_v" OR

2428 "area_id" = "area_id_v"

2429 )

2430 ORDER BY "scope" DESC;

2431 END IF;

2432 ELSE

2433 SELECT * INTO "delegation_row" FROM "delegation"

2434 WHERE "truster_id" = "output_row"."member_id"

2435 AND (

2436 "unit_id" = "unit_id_v" OR

2437 "area_id" = "area_id_v" OR

2438 "issue_id" = "issue_id_p"

2439 )

2440 ORDER BY "scope" DESC;

2441 END IF;

2442 END IF;

2443 ELSE

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

2445 END IF;

2446 IF "simulate_here_v" AND "simulate_trustee_id_p" NOTNULL THEN

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

2448 "output_rows" := "output_rows" || "output_row";

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

2450 ELSIF "delegation_row"."trustee_id" NOTNULL THEN

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

2452 "output_rows" := "output_rows" || "output_row";

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

2454 ELSIF "delegation_row"."scope" NOTNULL THEN

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

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

2457 "output_rows" := "output_rows" || "output_row";

2458 EXIT;

2459 ELSE

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

2461 "output_rows" := "output_rows" || "output_row";

2462 EXIT;

2463 END IF;

2464 EXIT WHEN "loop_member_id_v" NOTNULL;

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

2466 END LOOP;

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

2468 "i" := 1;

2469 "loop_v" := FALSE;

2470 LOOP

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

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

2473 IF "loop_v" THEN

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

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

2476 ELSIF "i" = "row_count" THEN

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

2478 ELSE

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

2480 END IF;

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

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

2483 "loop_v" := TRUE;

2484 END IF;

2485 IF "scope_v" = 'unit' THEN

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

2487 END IF;

2488 RETURN NEXT "output_row";

2489 "i" := "i" + 1;

2490 END LOOP;

2491 RETURN;

2492 END;

2493 $$;

2494

2495

2496 CREATE OR REPLACE FUNCTION "get_initiatives_for_notification"

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

2498 RETURNS SETOF "initiative_for_notification"

2499 LANGUAGE 'plpgsql' VOLATILE AS $$

2500 DECLARE

2501 "result_row" "initiative_for_notification"%ROWTYPE;

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

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

2504 BEGIN

2505 PERFORM "require_transaction_isolation"();

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

2507 FOR "result_row" IN

2508 SELECT * FROM "initiative_for_notification"

2509 WHERE "recipient_id" = "recipient_id_p"

2510 LOOP

2511 SELECT "id" INTO "last_draft_id_v" FROM "draft"

2512 WHERE "draft"."initiative_id" = "result_row"."initiative_id"

2513 ORDER BY "id" DESC LIMIT 1;

2514 SELECT "id" INTO "last_suggestion_id_v" FROM "suggestion"

2515 WHERE "suggestion"."initiative_id" = "result_row"."initiative_id"

2516 ORDER BY "id" DESC LIMIT 1;

2517 INSERT INTO "notification_initiative_sent"

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

2519 VALUES (

2520 "recipient_id_p",

2521 "result_row"."initiative_id",

2522 "last_draft_id_v",

2523 "last_suggestion_id_v" )

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

2525 "last_draft_id" = "last_draft_id_v",

2526 "last_suggestion_id" = "last_suggestion_id_v";

2527 RETURN NEXT "result_row";

2528 END LOOP;

2529 DELETE FROM "notification_initiative_sent"

2530 USING "initiative", "issue"

2531 WHERE "notification_initiative_sent"."member_id" = "recipient_id_p"

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

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

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

2535 UPDATE "member" SET

2536 "notification_counter" = "notification_counter" + 1,

2537 "notification_sent" = now()

2538 WHERE "id" = "recipient_id_p";

2539 RETURN;

2540 END;

2541 $$;

2542

2543

2544 CREATE OR REPLACE FUNCTION "calculate_member_counts"()

2545 RETURNS VOID

2546 LANGUAGE 'plpgsql' VOLATILE AS $$

2547 BEGIN

2548 PERFORM "require_transaction_isolation"();

2549 DELETE FROM "member_count";

2550 INSERT INTO "member_count" ("total_count")

2551 SELECT "total_count" FROM "member_count_view";

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

2553 FROM "unit_member_count" AS "view"

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

2555 RETURN;

2556 END;

2557 $$;

2558

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

2560

2561

2562 DROP VIEW "remaining_harmonic_initiative_weight_summands";

2563 DROP VIEW "remaining_harmonic_supporter_weight";

2564

2565

2566 CREATE VIEW "remaining_harmonic_supporter_weight" AS

2567 SELECT

2568 "direct_interest_snapshot"."snapshot_id",

2569 "direct_interest_snapshot"."issue_id",

2570 "direct_interest_snapshot"."member_id",

2571 "direct_interest_snapshot"."weight" AS "weight_num",

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

2573 FROM "issue"

2574 JOIN "direct_interest_snapshot"

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

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

2577 JOIN "initiative"

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

2579 AND "initiative"."harmonic_weight" ISNULL

2580 JOIN "direct_supporter_snapshot"

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

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

2583 AND "direct_interest_snapshot"."member_id" = "direct_supporter_snapshot"."member_id"

2584 AND (

2585 "direct_supporter_snapshot"."satisfied" = TRUE OR

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

2587 )

2588 GROUP BY

2589 "direct_interest_snapshot"."snapshot_id",

2590 "direct_interest_snapshot"."issue_id",

2591 "direct_interest_snapshot"."member_id",

2592 "direct_interest_snapshot"."weight";

2593

2594

2595 CREATE VIEW "remaining_harmonic_initiative_weight_summands" AS

2596 SELECT

2597 "initiative"."issue_id",

2598 "initiative"."id" AS "initiative_id",

2599 "initiative"."admitted",

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

2601 "remaining_harmonic_supporter_weight"."weight_den"

2602 FROM "remaining_harmonic_supporter_weight"

2603 JOIN "initiative"

2604 ON "remaining_harmonic_supporter_weight"."issue_id" = "initiative"."issue_id"

2605 AND "initiative"."harmonic_weight" ISNULL

2606 JOIN "direct_supporter_snapshot"

2607 ON "remaining_harmonic_supporter_weight"."snapshot_id" = "direct_supporter_snapshot"."snapshot_id"

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

2609 AND "remaining_harmonic_supporter_weight"."member_id" = "direct_supporter_snapshot"."member_id"

2610 AND (

2611 "direct_supporter_snapshot"."satisfied" = TRUE OR

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

2613 )

2614 GROUP BY

2615 "initiative"."issue_id",

2616 "initiative"."id",

2617 "initiative"."admitted",

2618 "remaining_harmonic_supporter_weight"."weight_den";

2619

2620

2621 DROP FUNCTION "create_population_snapshot"

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

2623

2624

2625 DROP FUNCTION "weight_of_added_delegations_for_population_snapshot"

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

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

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

2629

2630

2631 DROP FUNCTION "weight_of_added_delegations_for_interest_snapshot"

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

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

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

2635

2636

2637 CREATE FUNCTION "weight_of_added_delegations_for_snapshot"

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

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

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

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

2642 RETURNS "direct_interest_snapshot"."weight"%TYPE

2643 LANGUAGE 'plpgsql' VOLATILE AS $$

2644 DECLARE

2645 "issue_delegation_row" "issue_delegation"%ROWTYPE;

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

2647 "weight_v" INT4;

2648 "sub_weight_v" INT4;

2649 BEGIN

2650 PERFORM "require_transaction_isolation"();

2651 "weight_v" := 0;

2652 FOR "issue_delegation_row" IN

2653 SELECT * FROM "issue_delegation"

2654 WHERE "trustee_id" = "member_id_p"

2655 AND "issue_id" = "issue_id_p"

2656 LOOP

2657 IF NOT EXISTS (

2658 SELECT NULL FROM "direct_interest_snapshot"

2659 WHERE "snapshot_id" = "snapshot_id_p"

2660 AND "issue_id" = "issue_id_p"

2661 AND "member_id" = "issue_delegation_row"."truster_id"

2662 ) AND NOT EXISTS (

2663 SELECT NULL FROM "delegating_interest_snapshot"

2664 WHERE "snapshot_id" = "snapshot_id_p"

2665 AND "issue_id" = "issue_id_p"

2666 AND "member_id" = "issue_delegation_row"."truster_id"

2667 ) THEN

2668 "delegate_member_ids_v" :=

2669 "member_id_p" || "delegate_member_ids_p";

2670 INSERT INTO "delegating_interest_snapshot" (

2671 "snapshot_id",

2672 "issue_id",

2673 "member_id",

2674 "scope",

2675 "delegate_member_ids"

2676 ) VALUES (

2677 "snapshot_id_p",

2678 "issue_id_p",

2679 "issue_delegation_row"."truster_id",

2680 "issue_delegation_row"."scope",

2681 "delegate_member_ids_v"

2682 );

2683 "sub_weight_v" := 1 +

2684 "weight_of_added_delegations_for_snapshot"(

2685 "snapshot_id_p",

2686 "issue_id_p",

2687 "issue_delegation_row"."truster_id",

2688 "delegate_member_ids_v"

2689 );

2690 UPDATE "delegating_interest_snapshot"

2691 SET "weight" = "sub_weight_v"

2692 WHERE "snapshot_id" = "snapshot_id_p"

2693 AND "issue_id" = "issue_id_p"

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

2695 "weight_v" := "weight_v" + "sub_weight_v";

2696 END IF;

2697 END LOOP;

2698 RETURN "weight_v";

2699 END;

2700 $$;

2701

2702 COMMENT ON FUNCTION "weight_of_added_delegations_for_snapshot"

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

2704 "issue"."id"%TYPE,

2705 "member"."id"%TYPE,

2706 "delegating_interest_snapshot"."delegate_member_ids"%TYPE )

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

2708

2709

2710 DROP FUNCTION "create_interest_snapshot"

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

2712

2713

2714 DROP FUNCTION "create_snapshot"

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

2716

2717

2718 CREATE FUNCTION "take_snapshot"

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

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

2721 RETURNS "snapshot"."id"%TYPE

2722 LANGUAGE 'plpgsql' VOLATILE AS $$

2723 DECLARE

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

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

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

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

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

2729 BEGIN

2730 IF "issue_id_p" NOTNULL AND "area_id_p" NOTNULL THEN

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

2732 END IF;

2733 PERFORM "require_transaction_isolation"();

2734 IF "issue_id_p" ISNULL THEN

2735 "area_id_v" := "area_id_p";

2736 ELSE

2737 SELECT "area_id" INTO "area_id_v"

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

2739 END IF;

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

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

2742 VALUES ("area_id_v", "issue_id_p")

2743 RETURNING "id" INTO "snapshot_id_v";

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

2745 SELECT "snapshot_id_v", "member_id"

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

2747 UPDATE "snapshot" SET

2748 "population" = (

2749 SELECT count(1) FROM "snapshot_population"

2750 WHERE "snapshot_id" = "snapshot_id_v"

2751 ) WHERE "id" = "snapshot_id_v";

2752 FOR "issue_id_v" IN

2753 SELECT "id" FROM "issue"

2754 WHERE CASE WHEN "issue_id_p" ISNULL THEN

2755 "area_id" = "area_id_p" AND

2756 "state" = 'admission'

2757 ELSE

2758 "id" = "issue_id_p"

2759 END

2760 LOOP

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

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

2763 INSERT INTO "direct_interest_snapshot"

2764 ("snapshot_id", "issue_id", "member_id")

2765 SELECT

2766 "snapshot_id_v" AS "snapshot_id",

2767 "issue_id_v" AS "issue_id",

2768 "member"."id" AS "member_id"

2769 FROM "issue"

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

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

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

2773 JOIN "privilege"

2774 ON "privilege"."unit_id" = "area"."unit_id"

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

2776 WHERE "issue"."id" = "issue_id_v"

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

2778 FOR "member_id_v" IN

2779 SELECT "member_id" FROM "direct_interest_snapshot"

2780 WHERE "snapshot_id" = "snapshot_id_v"

2781 AND "issue_id" = "issue_id_v"

2782 LOOP

2783 UPDATE "direct_interest_snapshot" SET

2784 "weight" = 1 +

2785 "weight_of_added_delegations_for_snapshot"(

2786 "snapshot_id_v",

2787 "issue_id_v",

2788 "member_id_v",

2789 '{}'

2790 )

2791 WHERE "snapshot_id" = "snapshot_id_v"

2792 AND "issue_id" = "issue_id_v"

2793 AND "member_id" = "member_id_v";

2794 END LOOP;

2795 INSERT INTO "direct_supporter_snapshot"

2796 ( "snapshot_id", "issue_id", "initiative_id", "member_id",

2797 "draft_id", "informed", "satisfied" )

2798 SELECT

2799 "snapshot_id_v" AS "snapshot_id",

2800 "issue_id_v" AS "issue_id",

2801 "initiative"."id" AS "initiative_id",

2802 "supporter"."member_id" AS "member_id",

2803 "supporter"."draft_id" AS "draft_id",

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

2805 NOT EXISTS (

2806 SELECT NULL FROM "critical_opinion"

2807 WHERE "initiative_id" = "initiative"."id"

2808 AND "member_id" = "supporter"."member_id"

2809 ) AS "satisfied"

2810 FROM "initiative"

2811 JOIN "supporter"

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

2813 JOIN "current_draft"

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

2815 JOIN "direct_interest_snapshot"

2816 ON "snapshot_id_v" = "direct_interest_snapshot"."snapshot_id"

2817 AND "supporter"."member_id" = "direct_interest_snapshot"."member_id"

2818 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"

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

2820 DELETE FROM "temporary_suggestion_counts";

2821 INSERT INTO "temporary_suggestion_counts"

2822 ( "id",

2823 "minus2_unfulfilled_count", "minus2_fulfilled_count",

2824 "minus1_unfulfilled_count", "minus1_fulfilled_count",

2825 "plus1_unfulfilled_count", "plus1_fulfilled_count",

2826 "plus2_unfulfilled_count", "plus2_fulfilled_count" )

2827 SELECT

2828 "suggestion"."id",

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

2830 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"

2831 ON "di"."snapshot_id" = "snapshot_id_v"

2832 AND "di"."issue_id" = "issue_id_v"

2833 AND "di"."member_id" = "opinion"."member_id"

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

2835 AND "opinion"."degree" = -2

2836 AND "opinion"."fulfilled" = FALSE

2837 ) AS "minus2_unfulfilled_count",

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

2839 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"

2840 ON "di"."snapshot_id" = "snapshot_id_v"

2841 AND "di"."issue_id" = "issue_id_v"

2842 AND "di"."member_id" = "opinion"."member_id"

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

2844 AND "opinion"."degree" = -2

2845 AND "opinion"."fulfilled" = TRUE

2846 ) AS "minus2_fulfilled_count",

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

2848 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"

2849 ON "di"."snapshot_id" = "snapshot_id_v"

2850 AND "di"."issue_id" = "issue_id_v"

2851 AND "di"."member_id" = "opinion"."member_id"

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

2853 AND "opinion"."degree" = -1

2854 AND "opinion"."fulfilled" = FALSE

2855 ) AS "minus1_unfulfilled_count",

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

2857 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"

2858 ON "di"."snapshot_id" = "snapshot_id_v"

2859 AND "di"."issue_id" = "issue_id_v"

2860 AND "di"."member_id" = "opinion"."member_id"

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

2862 AND "opinion"."degree" = -1

2863 AND "opinion"."fulfilled" = TRUE

2864 ) AS "minus1_fulfilled_count",

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

2866 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"

2867 ON "di"."snapshot_id" = "snapshot_id_v"

2868 AND "di"."issue_id" = "issue_id_v"

2869 AND "di"."member_id" = "opinion"."member_id"

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

2871 AND "opinion"."degree" = 1

2872 AND "opinion"."fulfilled" = FALSE

2873 ) AS "plus1_unfulfilled_count",

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

2875 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"

2876 ON "di"."snapshot_id" = "snapshot_id_v"

2877 AND "di"."issue_id" = "issue_id_v"

2878 AND "di"."member_id" = "opinion"."member_id"

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

2880 AND "opinion"."degree" = 1

2881 AND "opinion"."fulfilled" = TRUE

2882 ) AS "plus1_fulfilled_count",

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

2884 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"

2885 ON "di"."snapshot_id" = "snapshot_id_v"

2886 AND "di"."issue_id" = "issue_id_v"

2887 AND "di"."member_id" = "opinion"."member_id"

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

2889 AND "opinion"."degree" = 2

2890 AND "opinion"."fulfilled" = FALSE

2891 ) AS "plus2_unfulfilled_count",

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

2893 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"

2894 ON "di"."snapshot_id" = "snapshot_id_v"

2895 AND "di"."issue_id" = "issue_id_v"

2896 AND "di"."member_id" = "opinion"."member_id"

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

2898 AND "opinion"."degree" = 2

2899 AND "opinion"."fulfilled" = TRUE

2900 ) AS "plus2_fulfilled_count"

2901 FROM "suggestion" JOIN "initiative"

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

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

2904 END LOOP;

2905 RETURN "snapshot_id_v";

2906 END;

2907 $$;

2908

2909 COMMENT ON FUNCTION "take_snapshot"

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

2911 "area"."id"%TYPE )

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

2913

2914

2915 DROP FUNCTION "set_snapshot_event"

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

2917 "event_p" "snapshot_event" );

2918

2919

2920 CREATE FUNCTION "finish_snapshot"

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

2922 RETURNS VOID

2923 LANGUAGE 'plpgsql' VOLATILE AS $$

2924 DECLARE

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

2926 BEGIN

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

2928 -- "dont_require_snapshot_isolation" here because this function is

2929 -- also invoked by "check_issue"

2930 LOCK TABLE "snapshot" IN EXCLUSIVE MODE;

2931 SELECT "id" INTO "snapshot_id_v" FROM "snapshot"

2932 ORDER BY "id" DESC LIMIT 1;

2933 UPDATE "issue" SET

2934 "calculated" = "snapshot"."calculated",

2935 "latest_snapshot_id" = "snapshot_id_v",

2936 "population" = "snapshot"."population",

2937 "initiative_quorum" = CASE WHEN

2938 "policy"."initiative_quorum" > ceil(

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

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

2941 "policy"."initiative_quorum_den"::FLOAT8

2942 )::INT4

2943 THEN

2944 "policy"."initiative_quorum"

2945 ELSE

2946 ceil(

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

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

2949 "policy"."initiative_quorum_den"::FLOAT8

2950 )::INT4

2951 END

2952 FROM "snapshot", "policy"

2953 WHERE "issue"."id" = "issue_id_p"

2954 AND "snapshot"."id" = "snapshot_id_v"

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

2956 UPDATE "initiative" SET

2957 "supporter_count" = (

2958 SELECT coalesce(sum("di"."weight"), 0)

2959 FROM "direct_interest_snapshot" AS "di"

2960 JOIN "direct_supporter_snapshot" AS "ds"

2961 ON "di"."member_id" = "ds"."member_id"

2962 WHERE "di"."snapshot_id" = "snapshot_id_v"

2963 AND "di"."issue_id" = "issue_id_p"

2964 AND "ds"."snapshot_id" = "snapshot_id_v"

2965 AND "ds"."initiative_id" = "initiative"."id"

2966 ),

2967 "informed_supporter_count" = (

2968 SELECT coalesce(sum("di"."weight"), 0)

2969 FROM "direct_interest_snapshot" AS "di"

2970 JOIN "direct_supporter_snapshot" AS "ds"

2971 ON "di"."member_id" = "ds"."member_id"

2972 WHERE "di"."snapshot_id" = "snapshot_id_v"

2973 AND "di"."issue_id" = "issue_id_p"

2974 AND "ds"."snapshot_id" = "snapshot_id_v"

2975 AND "ds"."initiative_id" = "initiative"."id"

2976 AND "ds"."informed"

2977 ),

2978 "satisfied_supporter_count" = (

2979 SELECT coalesce(sum("di"."weight"), 0)

2980 FROM "direct_interest_snapshot" AS "di"

2981 JOIN "direct_supporter_snapshot" AS "ds"

2982 ON "di"."member_id" = "ds"."member_id"

2983 WHERE "di"."snapshot_id" = "snapshot_id_v"

2984 AND "di"."issue_id" = "issue_id_p"

2985 AND "ds"."snapshot_id" = "snapshot_id_v"

2986 AND "ds"."initiative_id" = "initiative"."id"

2987 AND "ds"."satisfied"

2988 ),

2989 "satisfied_informed_supporter_count" = (

2990 SELECT coalesce(sum("di"."weight"), 0)

2991 FROM "direct_interest_snapshot" AS "di"

2992 JOIN "direct_supporter_snapshot" AS "ds"

2993 ON "di"."member_id" = "ds"."member_id"

2994 WHERE "di"."snapshot_id" = "snapshot_id_v"

2995 AND "di"."issue_id" = "issue_id_p"

2996 AND "ds"."snapshot_id" = "snapshot_id_v"

2997 AND "ds"."initiative_id" = "initiative"."id"

2998 AND "ds"."informed"

2999 AND "ds"."satisfied"

3000 )

3001 WHERE "issue_id" = "issue_id_p";

3002 UPDATE "suggestion" SET

3003 "minus2_unfulfilled_count" = "temp"."minus2_unfulfilled_count",

3004 "minus2_fulfilled_count" = "temp"."minus2_fulfilled_count",

3005 "minus1_unfulfilled_count" = "temp"."minus1_unfulfilled_count",

3006 "minus1_fulfilled_count" = "temp"."minus1_fulfilled_count",

3007 "plus1_unfulfilled_count" = "temp"."plus1_unfulfilled_count",

3008 "plus1_fulfilled_count" = "temp"."plus1_fulfilled_count",

3009 "plus2_unfulfilled_count" = "temp"."plus2_unfulfilled_count",

3010 "plus2_fulfilled_count" = "temp"."plus2_fulfilled_count"

3011 FROM "temporary_suggestion_counts" AS "temp", "initiative"

3012 WHERE "temp"."id" = "suggestion"."id"

3013 AND "initiative"."issue_id" = "issue_id_p"

3014 AND "suggestion"."initiative_id" = "initiative"."id";

3015 DELETE FROM "temporary_suggestion_counts";

3016 RETURN;

3017 END;

3018 $$;

3019

3020 COMMENT ON FUNCTION "finish_snapshot"

3021 ( "issue"."id"%TYPE )

3022 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)';

3023

3024

3025 CREATE FUNCTION "issue_admission"

3026 ( "area_id_p" "area"."id"%TYPE )

3027 RETURNS BOOLEAN

3028 LANGUAGE 'plpgsql' VOLATILE AS $$

3029 DECLARE

3030 "issue_id_v" "issue"."id"%TYPE;

3031 BEGIN

3032 PERFORM "dont_require_transaction_isolation"();

3033 LOCK TABLE "snapshot" IN EXCLUSIVE MODE;

3034 UPDATE "area" SET "issue_quorum" = "view"."issue_quorum"

3035 FROM "area_quorum" AS "view"

3036 WHERE "area"."id" = "view"."area_id"

3037 AND "area"."id" = "area_id_p";

3038 SELECT "id" INTO "issue_id_v" FROM "issue_for_admission"

3039 WHERE "area_id" = "area_id_p";

3040 IF "issue_id_v" ISNULL THEN RETURN FALSE; END IF;

3041 UPDATE "issue" SET

3042 "admission_snapshot_id" = "latest_snapshot_id",

3043 "state" = 'discussion',

3044 "accepted" = now(),

3045 "phase_finished" = NULL,

3046 "issue_quorum" = "issue_quorum"."issue_quorum"

3047 FROM "issue_quorum"

3048 WHERE "id" = "issue_id_v"

3049 AND "issue_quorum"."issue_id" = "issue_id_v";

3050 RETURN TRUE;

3051 END;

3052 $$;

3053

3054 COMMENT ON FUNCTION "issue_admission"

3055 ( "area"."id"%TYPE )

3056 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';

3057

3058

3059 CREATE OR REPLACE FUNCTION "check_issue"

3060 ( "issue_id_p" "issue"."id"%TYPE,

3061 "persist" "check_issue_persistence" )

3062 RETURNS "check_issue_persistence"

3063 LANGUAGE 'plpgsql' VOLATILE AS $$

3064 DECLARE

3065 "issue_row" "issue"%ROWTYPE;

3066 "last_calculated_v" "snapshot"."calculated"%TYPE;

3067 "policy_row" "policy"%ROWTYPE;

3068 "initiative_row" "initiative"%ROWTYPE;

3069 "state_v" "issue_state";

3070 BEGIN

3071 PERFORM "require_transaction_isolation"();

3072 IF "persist" ISNULL THEN

3073 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"

3074 FOR UPDATE;

3075 SELECT "calculated" INTO "last_calculated_v"

3076 FROM "snapshot" JOIN "snapshot_issue"

3077 ON "snapshot"."id" = "snapshot_issue"."snapshot_id"

3078 WHERE "snapshot_issue"."issue_id" = "issue_id_p";

3079 IF "issue_row"."closed" NOTNULL THEN

3080 RETURN NULL;

3081 END IF;

3082 "persist"."state" := "issue_row"."state";

3083 IF

3084 ( "issue_row"."state" = 'admission' AND "last_calculated_v" >=

3085 "issue_row"."created" + "issue_row"."max_admission_time" ) OR

3086 ( "issue_row"."state" = 'discussion' AND now() >=

3087 "issue_row"."accepted" + "issue_row"."discussion_time" ) OR

3088 ( "issue_row"."state" = 'verification' AND now() >=

3089 "issue_row"."half_frozen" + "issue_row"."verification_time" ) OR

3090 ( "issue_row"."state" = 'voting' AND now() >=

3091 "issue_row"."fully_frozen" + "issue_row"."voting_time" )

3092 THEN

3093 "persist"."phase_finished" := TRUE;

3094 ELSE

3095 "persist"."phase_finished" := FALSE;

3096 END IF;

3097 IF

3098 NOT EXISTS (

3099 -- all initiatives are revoked

3100 SELECT NULL FROM "initiative"

3101 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL

3102 ) AND (

3103 -- and issue has not been accepted yet

3104 "persist"."state" = 'admission' OR

3105 -- or verification time has elapsed

3106 ( "persist"."state" = 'verification' AND

3107 "persist"."phase_finished" ) OR

3108 -- or no initiatives have been revoked lately

3109 NOT EXISTS (

3110 SELECT NULL FROM "initiative"

3111 WHERE "issue_id" = "issue_id_p"

3112 AND now() < "revoked" + "issue_row"."verification_time"

3113 )

3114 )

3115 THEN

3116 "persist"."issue_revoked" := TRUE;

3117 ELSE

3118 "persist"."issue_revoked" := FALSE;

3119 END IF;

3120 IF "persist"."phase_finished" OR "persist"."issue_revoked" THEN

3121 UPDATE "issue" SET "phase_finished" = now()

3122 WHERE "id" = "issue_row"."id";

3123 RETURN "persist";

3124 ELSIF

3125 "persist"."state" IN ('admission', 'discussion', 'verification')

3126 THEN

3127 RETURN "persist";

3128 ELSE

3129 RETURN NULL;

3130 END IF;

3131 END IF;

3132 IF

3133 "persist"."state" IN ('admission', 'discussion', 'verification') AND

3134 coalesce("persist"."snapshot_created", FALSE) = FALSE

3135 THEN

3136 IF "persist"."state" != 'admission' THEN

3137 PERFORM "take_snapshot"("issue_id_p");

3138 PERFORM "finish_snapshot"("issue_id_p");

3139 ELSE

3140 UPDATE "issue" SET "issue_quorum" = "issue_quorum"."issue_quorum"

3141 FROM "issue_quorum"

3142 WHERE "id" = "issue_id_p"

3143 AND "issue_quorum"."issue_id" = "issue_id_p";

3144 END IF;

3145 "persist"."snapshot_created" = TRUE;

3146 IF "persist"."phase_finished" THEN

3147 IF "persist"."state" = 'admission' THEN

3148 UPDATE "issue" SET "admission_snapshot_id" = "latest_snapshot_id"

3149 WHERE "id" = "issue_id_p";

3150 ELSIF "persist"."state" = 'discussion' THEN

3151 UPDATE "issue" SET "half_freeze_snapshot_id" = "latest_snapshot_id"

3152 WHERE "id" = "issue_id_p";

3153 ELSIF "persist"."state" = 'verification' THEN

3154 UPDATE "issue" SET "full_freeze_snapshot_id" = "latest_snapshot_id"

3155 WHERE "id" = "issue_id_p";

3156 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";

3157 FOR "initiative_row" IN

3158 SELECT * FROM "initiative"

3159 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL

3160 FOR UPDATE

3161 LOOP

3162 IF

3163 "initiative_row"."polling" OR

3164 "initiative_row"."satisfied_supporter_count" >=

3165 "issue_row"."initiative_quorum"

3166 THEN

3167 UPDATE "initiative" SET "admitted" = TRUE

3168 WHERE "id" = "initiative_row"."id";

3169 ELSE

3170 UPDATE "initiative" SET "admitted" = FALSE

3171 WHERE "id" = "initiative_row"."id";

3172 END IF;

3173 END LOOP;

3174 END IF;

3175 END IF;

3176 RETURN "persist";

3177 END IF;

3178 IF

3179 "persist"."state" IN ('admission', 'discussion', 'verification') AND

3180 coalesce("persist"."harmonic_weights_set", FALSE) = FALSE

3181 THEN

3182 PERFORM "set_harmonic_initiative_weights"("issue_id_p");

3183 "persist"."harmonic_weights_set" = TRUE;

3184 IF

3185 "persist"."phase_finished" OR

3186 "persist"."issue_revoked" OR

3187 "persist"."state" = 'admission'

3188 THEN

3189 RETURN "persist";

3190 ELSE

3191 RETURN NULL;

3192 END IF;

3193 END IF;

3194 IF "persist"."issue_revoked" THEN

3195 IF "persist"."state" = 'admission' THEN

3196 "state_v" := 'canceled_revoked_before_accepted';

3197 ELSIF "persist"."state" = 'discussion' THEN

3198 "state_v" := 'canceled_after_revocation_during_discussion';

3199 ELSIF "persist"."state" = 'verification' THEN

3200 "state_v" := 'canceled_after_revocation_during_verification';

3201 END IF;

3202 UPDATE "issue" SET

3203 "state" = "state_v",

3204 "closed" = "phase_finished",

3205 "phase_finished" = NULL

3206 WHERE "id" = "issue_id_p";

3207 RETURN NULL;

3208 END IF;

3209 IF "persist"."state" = 'admission' THEN

3210 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"

3211 FOR UPDATE;

3212 IF "issue_row"."phase_finished" NOTNULL THEN

3213 UPDATE "issue" SET

3214 "state" = 'canceled_issue_not_accepted',

3215 "closed" = "phase_finished",

3216 "phase_finished" = NULL

3217 WHERE "id" = "issue_id_p";

3218 END IF;

3219 RETURN NULL;

3220 END IF;

3221 IF "persist"."phase_finished" THEN

3222 IF "persist"."state" = 'discussion' THEN

3223 UPDATE "issue" SET

3224 "state" = 'verification',

3225 "half_frozen" = "phase_finished",

3226 "phase_finished" = NULL

3227 WHERE "id" = "issue_id_p";

3228 RETURN NULL;

3229 END IF;

3230 IF "persist"."state" = 'verification' THEN

3231 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"

3232 FOR UPDATE;

3233 SELECT * INTO "policy_row" FROM "policy"

3234 WHERE "id" = "issue_row"."policy_id";

3235 IF EXISTS (

3236 SELECT NULL FROM "initiative"

3237 WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE

3238 ) THEN

3239 UPDATE "issue" SET

3240 "state" = 'voting',

3241 "fully_frozen" = "phase_finished",

3242 "phase_finished" = NULL

3243 WHERE "id" = "issue_id_p";

3244 ELSE

3245 UPDATE "issue" SET

3246 "state" = 'canceled_no_initiative_admitted',

3247 "fully_frozen" = "phase_finished",

3248 "closed" = "phase_finished",

3249 "phase_finished" = NULL

3250 WHERE "id" = "issue_id_p";

3251 -- NOTE: The following DELETE statements have effect only when

3252 -- issue state has been manipulated

3253 DELETE FROM "direct_voter" WHERE "issue_id" = "issue_id_p";

3254 DELETE FROM "delegating_voter" WHERE "issue_id" = "issue_id_p";

3255 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";

3256 END IF;

3257 RETURN NULL;

3258 END IF;

3259 IF "persist"."state" = 'voting' THEN

3260 IF coalesce("persist"."closed_voting", FALSE) = FALSE THEN

3261 PERFORM "close_voting"("issue_id_p");

3262 "persist"."closed_voting" = TRUE;

3263 RETURN "persist";

3264 END IF;

3265 PERFORM "calculate_ranks"("issue_id_p");

3266 RETURN NULL;

3267 END IF;

3268 END IF;

3269 RAISE WARNING 'should not happen';

3270 RETURN NULL;

3271 END;

3272 $$;

3273

3274

3275 CREATE OR REPLACE FUNCTION "check_everything"()

3276 RETURNS VOID

3277 LANGUAGE 'plpgsql' VOLATILE AS $$

3278 DECLARE

3279 "area_id_v" "area"."id"%TYPE;

3280 "snapshot_id_v" "snapshot"."id"%TYPE;

3281 "issue_id_v" "issue"."id"%TYPE;

3282 "persist_v" "check_issue_persistence";

3283 BEGIN

3284 RAISE WARNING 'Function "check_everything" should only be used for development and debugging purposes';

3285 DELETE FROM "expired_session";

3286 DELETE FROM "expired_token";

3287 DELETE FROM "expired_snapshot";

3288 PERFORM "check_activity"();

3289 PERFORM "calculate_member_counts"();

3290 FOR "area_id_v" IN SELECT "id" FROM "area_with_unaccepted_issues" LOOP

3291 SELECT "take_snapshot"(NULL, "area_id_v") INTO "snapshot_id_v";

3292 PERFORM "finish_snapshot"("issue_id") FROM "snapshot_issue"

3293 WHERE "snapshot_id" = "snapshot_id_v";

3294 LOOP

3295 EXIT WHEN "issue_admission"("area_id_v") = FALSE;

3296 END LOOP;

3297 END LOOP;

3298 FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP

3299 "persist_v" := NULL;

3300 LOOP

3301 "persist_v" := "check_issue"("issue_id_v", "persist_v");

3302 EXIT WHEN "persist_v" ISNULL;

3303 END LOOP;

3304 END LOOP;

3305 RETURN;

3306 END;

3307 $$;

3308

3309 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';

3310

3311

3312 CREATE OR REPLACE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE)

3313 RETURNS VOID

3314 LANGUAGE 'plpgsql' VOLATILE AS $$

3315 BEGIN

3316 IF EXISTS (

3317 SELECT NULL FROM "issue" WHERE "id" = "issue_id_p" AND "cleaned" ISNULL

3318 ) THEN

3319 -- override protection triggers:

3320 INSERT INTO "temporary_transaction_data" ("key", "value")

3321 VALUES ('override_protection_triggers', TRUE::TEXT);

3322 -- clean data:

3323 DELETE FROM "delegating_voter"

3324 WHERE "issue_id" = "issue_id_p";

3325 DELETE FROM "direct_voter"

3326 WHERE "issue_id" = "issue_id_p";

3327 DELETE FROM "delegating_interest_snapshot"

3328 WHERE "issue_id" = "issue_id_p";

3329 DELETE FROM "direct_interest_snapshot"

3330 WHERE "issue_id" = "issue_id_p";

3331 DELETE FROM "non_voter"

3332 WHERE "issue_id" = "issue_id_p";

3333 DELETE FROM "delegation"

3334 WHERE "issue_id" = "issue_id_p";

3335 DELETE FROM "supporter"

3336 USING "initiative" -- NOTE: due to missing index on issue_id

3337 WHERE "initiative"."issue_id" = "issue_id_p"

3338 AND "supporter"."initiative_id" = "initiative_id";

3339 -- mark issue as cleaned:

3340 UPDATE "issue" SET "cleaned" = now() WHERE "id" = "issue_id_p";

3341 -- finish overriding protection triggers (avoids garbage):

3342 DELETE FROM "temporary_transaction_data"

3343 WHERE "key" = 'override_protection_triggers';

3344 END IF;

3345 RETURN;

3346 END;

3347 $$;

3348

3349

3350 CREATE OR REPLACE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)

3351 RETURNS VOID

3352 LANGUAGE 'plpgsql' VOLATILE AS $$

3353 BEGIN

3354 UPDATE "member" SET

3355 "last_login" = NULL,

3356 "last_delegation_check" = NULL,

3357 "login" = NULL,

3358 "password" = NULL,

3359 "authority" = NULL,

3360 "authority_uid" = NULL,

3361 "authority_login" = NULL,

3362 "deleted" = coalesce("deleted", now()),

3363 "locked" = TRUE,

3364 "active" = FALSE,

3365 "notify_email" = NULL,

3366 "notify_email_unconfirmed" = NULL,

3367 "notify_email_secret" = NULL,

3368 "notify_email_secret_expiry" = NULL,

3369 "notify_email_lock_expiry" = NULL,

3370 "disable_notifications" = TRUE,

3371 "notification_counter" = DEFAULT,

3372 "notification_sample_size" = 0,

3373 "notification_dow" = NULL,

3374 "notification_hour" = NULL,

3375 "notification_sent" = NULL,

3376 "login_recovery_expiry" = NULL,

3377 "password_reset_secret" = NULL,

3378 "password_reset_secret_expiry" = NULL,

3379 "location" = NULL

3380 WHERE "id" = "member_id_p";

3381 -- "text_search_data" is updated by triggers

3382 DELETE FROM "member_settings" WHERE "member_id" = "member_id_p";

3383 DELETE FROM "member_profile" WHERE "member_id" = "member_id_p";

3384 DELETE FROM "rendered_member_statement" WHERE "member_id" = "member_id_p";

3385 DELETE FROM "member_image" WHERE "member_id" = "member_id_p";

3386 DELETE FROM "contact" WHERE "member_id" = "member_id_p";

3387 DELETE FROM "ignored_member" WHERE "member_id" = "member_id_p";

3388 DELETE FROM "session" WHERE "member_id" = "member_id_p";

3389 DELETE FROM "member_application" WHERE "member_id" = "member_id_p";

3390 DELETE FROM "token" WHERE "member_id" = "member_id_p";

3391 DELETE FROM "subscription" WHERE "member_id" = "member_id_p";

3392 DELETE FROM "ignored_area" WHERE "member_id" = "member_id_p";

3393 DELETE FROM "ignored_initiative" WHERE "member_id" = "member_id_p";

3394 DELETE FROM "delegation" WHERE "truster_id" = "member_id_p";

3395 DELETE FROM "non_voter" WHERE "member_id" = "member_id_p";

3396 DELETE FROM "direct_voter" USING "issue"

3397 WHERE "direct_voter"."issue_id" = "issue"."id"

3398 AND "issue"."closed" ISNULL

3399 AND "member_id" = "member_id_p";

3400 DELETE FROM "notification_initiative_sent" WHERE "member_id" = "member_id_p";

3401 RETURN;

3402 END;

3403 $$;

3404

3405

3406 CREATE OR REPLACE FUNCTION "delete_private_data"()

3407 RETURNS VOID

3408 LANGUAGE 'plpgsql' VOLATILE AS $$

3409 BEGIN

3410 DELETE FROM "temporary_transaction_data";

3411 DELETE FROM "temporary_suggestion_counts";

3412 DELETE FROM "member" WHERE "activated" ISNULL;

3413 UPDATE "member" SET

3414 "invite_code" = NULL,

3415 "invite_code_expiry" = NULL,

3416 "admin_comment" = NULL,

3417 "last_login" = NULL,

3418 "last_delegation_check" = NULL,

3419 "login" = NULL,

3420 "password" = NULL,

3421 "authority" = NULL,

3422 "authority_uid" = NULL,

3423 "authority_login" = NULL,

3424 "lang" = NULL,

3425 "notify_email" = NULL,

3426 "notify_email_unconfirmed" = NULL,

3427 "notify_email_secret" = NULL,

3428 "notify_email_secret_expiry" = NULL,

3429 "notify_email_lock_expiry" = NULL,

3430 "disable_notifications" = TRUE,

3431 "notification_counter" = DEFAULT,

3432 "notification_sample_size" = 0,

3433 "notification_dow" = NULL,

3434 "notification_hour" = NULL,

3435 "notification_sent" = NULL,

3436 "login_recovery_expiry" = NULL,

3437 "password_reset_secret" = NULL,

3438 "password_reset_secret_expiry" = NULL,

3439 "location" = NULL;

3440 -- "text_search_data" is updated by triggers

3441 DELETE FROM "verification";

3442 DELETE FROM "member_settings";

3443 DELETE FROM "member_useterms";

3444 DELETE FROM "member_profile";

3445 DELETE FROM "rendered_member_statement";

3446 DELETE FROM "member_image";

3447 DELETE FROM "contact";

3448 DELETE FROM "ignored_member";

3449 DELETE FROM "session";

3450 DELETE FROM "system_application";

3451 DELETE FROM "system_application_redirect_uri";

3452 DELETE FROM "dynamic_application_scope";

3453 DELETE FROM "member_application";

3454 DELETE FROM "token";

3455 DELETE FROM "subscription";

3456 DELETE FROM "ignored_area";

3457 DELETE FROM "ignored_initiative";

3458 DELETE FROM "non_voter";

3459 DELETE FROM "direct_voter" USING "issue"

3460 WHERE "direct_voter"."issue_id" = "issue"."id"

3461 AND "issue"."closed" ISNULL;

3462 DELETE FROM "event_processed";

3463 DELETE FROM "notification_initiative_sent";

3464 DELETE FROM "newsletter";

3465 RETURN;

3466 END;

3467 $$;

3468

3469

3470 CREATE TEMPORARY TABLE "old_snapshot" AS

3471 SELECT "ordered".*, row_number() OVER () AS "snapshot_id"

3472 FROM (

3473 SELECT * FROM (

3474 SELECT

3475 "id" AS "issue_id",

3476 'end_of_admission'::"snapshot_event" AS "event",

3477 "accepted" AS "calculated"

3478 FROM "issue" WHERE "accepted" NOTNULL

3479 UNION ALL

3480 SELECT

3481 "id" AS "issue_id",

3482 'half_freeze'::"snapshot_event" AS "event",

3483 "half_frozen" AS "calculated"

3484 FROM "issue" WHERE "half_frozen" NOTNULL

3485 UNION ALL

3486 SELECT

3487 "id" AS "issue_id",

3488 'full_freeze'::"snapshot_event" AS "event",

3489 "fully_frozen" AS "calculated"

3490 FROM "issue" WHERE "fully_frozen" NOTNULL

3491 ) AS "unordered"

3492 ORDER BY "calculated", "issue_id", "event"

3493 ) AS "ordered";

3494

3495

3496 INSERT INTO "snapshot" ("id", "calculated", "population", "area_id", "issue_id")

3497 SELECT

3498 "old_snapshot"."snapshot_id" AS "id",

3499 "old_snapshot"."calculated",

3500 ( SELECT COALESCE(sum("weight"), 0)

3501 FROM "direct_population_snapshot" "dps"

3502 WHERE "dps"."issue_id" = "old_snapshot"."issue_id"

3503 AND "dps"."event" = "old_snapshot"."event"

3504 ) AS "population",

3505 "issue"."area_id" AS "area_id",

3506 "issue"."id" AS "issue_id"

3507 FROM "old_snapshot" JOIN "issue"

3508 ON "old_snapshot"."issue_id" = "issue"."id";

3509

3510

3511 INSERT INTO "snapshot_issue" ("snapshot_id", "issue_id")

3512 SELECT "id" AS "snapshot_id", "issue_id" FROM "snapshot";

3513

3514

3515 INSERT INTO "snapshot_population" ("snapshot_id", "member_id")

3516 SELECT

3517 "old_snapshot"."snapshot_id",

3518 "direct_population_snapshot"."member_id"

3519 FROM "old_snapshot" JOIN "direct_population_snapshot"

3520 ON "old_snapshot"."issue_id" = "direct_population_snapshot"."issue_id"

3521 AND "old_snapshot"."event" = "direct_population_snapshot"."event";

3522

3523 INSERT INTO "snapshot_population" ("snapshot_id", "member_id")

3524 SELECT

3525 "old_snapshot"."snapshot_id",

3526 "delegating_population_snapshot"."member_id"

3527 FROM "old_snapshot" JOIN "delegating_population_snapshot"

3528 ON "old_snapshot"."issue_id" = "delegating_population_snapshot"."issue_id"

3529 AND "old_snapshot"."event" = "delegating_population_snapshot"."event";

3530

3531

3532 INSERT INTO "direct_interest_snapshot"

3533 ("snapshot_id", "issue_id", "member_id", "weight")

3534 SELECT

3535 "old_snapshot"."snapshot_id",

3536 "old_snapshot"."issue_id",

3537 "direct_interest_snapshot_old"."member_id",

3538 "direct_interest_snapshot_old"."weight"

3539 FROM "old_snapshot" JOIN "direct_interest_snapshot_old"

3540 ON "old_snapshot"."issue_id" = "direct_interest_snapshot_old"."issue_id"

3541 AND "old_snapshot"."event" = "direct_interest_snapshot_old"."event";

3542

3543 INSERT INTO "delegating_interest_snapshot"

3544 ( "snapshot_id", "issue_id",

3545 "member_id", "weight", "scope", "delegate_member_ids" )

3546 SELECT

3547 "old_snapshot"."snapshot_id",

3548 "old_snapshot"."issue_id",

3549 "delegating_interest_snapshot_old"."member_id",

3550 "delegating_interest_snapshot_old"."weight",

3551 "delegating_interest_snapshot_old"."scope",

3552 "delegating_interest_snapshot_old"."delegate_member_ids"

3553 FROM "old_snapshot" JOIN "delegating_interest_snapshot_old"

3554 ON "old_snapshot"."issue_id" = "delegating_interest_snapshot_old"."issue_id"

3555 AND "old_snapshot"."event" = "delegating_interest_snapshot_old"."event";

3556

3557 INSERT INTO "direct_supporter_snapshot"

3558 ( "snapshot_id", "issue_id",

3559 "initiative_id", "member_id", "draft_id", "informed", "satisfied" )

3560 SELECT

3561 "old_snapshot"."snapshot_id",

3562 "old_snapshot"."issue_id",

3563 "direct_supporter_snapshot_old"."initiative_id",

3564 "direct_supporter_snapshot_old"."member_id",

3565 "direct_supporter_snapshot_old"."draft_id",

3566 "direct_supporter_snapshot_old"."informed",

3567 "direct_supporter_snapshot_old"."satisfied"

3568 FROM "old_snapshot" JOIN "direct_supporter_snapshot_old"

3569 ON "old_snapshot"."issue_id" = "direct_supporter_snapshot_old"."issue_id"

3570 AND "old_snapshot"."event" = "direct_supporter_snapshot_old"."event";

3571

3572

3573 ALTER TABLE "issue" DISABLE TRIGGER USER; -- NOTE: required to modify table later

3574

3575 UPDATE "issue" SET "latest_snapshot_id" = "snapshot"."id"

3576 FROM (

3577 SELECT DISTINCT ON ("issue_id") "issue_id", "id"

3578 FROM "snapshot" ORDER BY "issue_id", "id" DESC

3579 ) AS "snapshot"

3580 WHERE "snapshot"."issue_id" = "issue"."id";

3581

3582 UPDATE "issue" SET "admission_snapshot_id" = "old_snapshot"."snapshot_id"

3583 FROM "old_snapshot"

3584 WHERE "old_snapshot"."issue_id" = "issue"."id"

3585 AND "old_snapshot"."event" = 'end_of_admission';

3586

3587 UPDATE "issue" SET "half_freeze_snapshot_id" = "old_snapshot"."snapshot_id"

3588 FROM "old_snapshot"

3589 WHERE "old_snapshot"."issue_id" = "issue"."id"

3590 AND "old_snapshot"."event" = 'half_freeze';

3591

3592 UPDATE "issue" SET "full_freeze_snapshot_id" = "old_snapshot"."snapshot_id"

3593 FROM "old_snapshot"

3594 WHERE "old_snapshot"."issue_id" = "issue"."id"

3595 AND "old_snapshot"."event" = 'full_freeze';

3596

3597 ALTER TABLE "issue" ENABLE TRIGGER USER;

3598

3599

3600 DROP TABLE "old_snapshot";

3601

3602 DROP TABLE "direct_supporter_snapshot_old";

3603 DROP TABLE "delegating_interest_snapshot_old";

3604 DROP TABLE "direct_interest_snapshot_old";

3605 DROP TABLE "delegating_population_snapshot";

3606 DROP TABLE "direct_population_snapshot";

3607

3608

3609 DROP VIEW "open_issue";

3610

3611

3612 ALTER TABLE "issue" DROP COLUMN "latest_snapshot_event";

3613

3614

3615 CREATE VIEW "open_issue" AS

3616 SELECT * FROM "issue" WHERE "closed" ISNULL;

3617

3618 COMMENT ON VIEW "open_issue" IS 'All open issues';

3619

3620

3621 -- NOTE: create "issue_for_admission" view after altering table "issue"

3622 CREATE VIEW "issue_for_admission" AS

3623 SELECT DISTINCT ON ("issue"."area_id")

3624 "issue".*,

3625 max("initiative"."supporter_count") AS "max_supporter_count"

3626 FROM "issue"

3627 JOIN "policy" ON "issue"."policy_id" = "policy"."id"

3628 JOIN "initiative" ON "issue"."id" = "initiative"."issue_id"

3629 JOIN "area" ON "issue"."area_id" = "area"."id"

3630 WHERE "issue"."state" = 'admission'::"issue_state"

3631 AND now() >= "issue"."created" + "issue"."min_admission_time"

3632 AND "initiative"."supporter_count" >= "policy"."issue_quorum"

3633 AND "initiative"."supporter_count" * "policy"."issue_quorum_den" >=

3634 "issue"."population" * "policy"."issue_quorum_num"

3635 AND "initiative"."supporter_count" >= "area"."issue_quorum"

3636 AND "initiative"."revoked" ISNULL

3637 GROUP BY "issue"."id"

3638 ORDER BY "issue"."area_id", "max_supporter_count" DESC, "issue"."id";

3639

3640 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';

3641

3642

3643 DROP TYPE "snapshot_event";

3644

3645

3646 ALTER TABLE "issue" ADD CONSTRAINT "snapshot_required" CHECK (

3647 ("half_frozen" ISNULL OR "half_freeze_snapshot_id" NOTNULL) AND

3648 ("fully_frozen" ISNULL OR "full_freeze_snapshot_id" NOTNULL) );

3649

3650

3651 COMMIT;