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

liquid_feedback_core

view update/core-update.v3.2.2-v4.0.0.sql @ 561:fb2663ca1e6b

Added missing WHERE clause in "check_issue" function when updating snapshot IDs of issues

author	jbe
date	Mon Sep 25 14:20:07 2017 +0200 (2017-09-25)
parents	318827ecd041
children	c3931054bb55

line source

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

23 BEGIN;

26 CREATE OR REPLACE VIEW "liquid_feedback_version" AS

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

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

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

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

36 CREATE TABLE "verification" (

37 "id" SERIAL8 PRIMARY KEY,

38 "requested" TIMESTAMPTZ,

39 "request_origin" JSONB,

40 "request_data" JSONB,

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

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

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

44 "verified" TIMESTAMPTZ,

45 "verification_data" JSONB,

46 "denied" TIMESTAMPTZ,

47 "comment" TEXT,

48 CONSTRAINT "verified_and_denied_conflict" CHECK (

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

72 ALTER TABLE "member" ADD CONSTRAINT "deleted_requires_locked"

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

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

78 CREATE TABLE "member_settings" (

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

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

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

85 CREATE TABLE "member_useterms" (

86 "id" SERIAL8 PRIMARY KEY,

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

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

89 "contract_identifier" TEXT NOT NULL );

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

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

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

97 CREATE TABLE "member_profile" (

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

99 "formatting_engine" TEXT,

100 "statement" TEXT,

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

102 "profile_text_data" TEXT,

103 "text_search_data" TSVECTOR );

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

105 CREATE TRIGGER "update_text_search_data"

106 BEFORE INSERT OR UPDATE ON "member_profile"

107 FOR EACH ROW EXECUTE PROCEDURE

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

109 'statement', 'profile_text_data');

110

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

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

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

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

115

116

117 INSERT INTO "member_profile"

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

119 SELECT

120 "id" AS "member_id",

121 "formatting_engine",

122 "statement",

123 json_build_object(

124 'organizational_unit', "organizational_unit",

125 'internal_posts', "internal_posts",

126 'realname', "realname",

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

128 'address', "address",

129 'email', "email",

130 'xmpp_address', "xmpp_address",

131 'website', "website",

132 'phone', "phone",

133 'mobile_phone', "mobile_phone",

134 'profession', "profession",

135 'external_memberships', "external_memberships",

136 'external_posts', "external_posts"

137 ) AS "profile"

138 FROM "member";

139

140 UPDATE "member_profile" SET "profile_text_data" =

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

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

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

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

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

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

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

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

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

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

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

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

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

154

155

156 DROP VIEW "newsletter_to_send";

157 DROP VIEW "scheduled_notification_to_send";

158 DROP VIEW "member_to_notify";

159 DROP VIEW "member_eligible_to_be_notified";

160

161

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

177

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

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

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

181

182 DROP TRIGGER "update_text_search_data" ON "member";

183 CREATE TRIGGER "update_text_search_data"

184 BEFORE INSERT OR UPDATE ON "member"

185 FOR EACH ROW EXECUTE PROCEDURE

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

187 "name", "identification");

188

189

190 CREATE VIEW "member_eligible_to_be_notified" AS

191 SELECT * FROM "member"

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

193

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

195

196

197 CREATE VIEW "member_to_notify" AS

198 SELECT * FROM "member_eligible_to_be_notified"

199 WHERE "disable_notifications" = FALSE;

200

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

202

203

204 CREATE VIEW "scheduled_notification_to_send" AS

205 SELECT * FROM (

206 SELECT

207 "id" AS "recipient_id",

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

209 ( "notification_sent"::DATE + CASE

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

211 THEN 0 ELSE 1 END

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

213 ELSE

214 ( "notification_sent"::DATE +

215 ( 7 + "notification_dow" -

216 EXTRACT(DOW FROM

217 ( "notification_sent"::DATE + CASE

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

219 THEN 0 ELSE 1 END

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

221 )::INTEGER

222 ) % 7 +

223 CASE

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

225 THEN 0 ELSE 1

226 END

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

228 END AS "pending"

229 FROM (

230 SELECT

231 "id",

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

233 "notification_dow",

234 "notification_hour"

235 FROM "member_to_notify"

236 WHERE "notification_hour" NOTNULL

237 ) AS "subquery1"

238 ) AS "subquery2"

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

240

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

242

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

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

245

246

247 CREATE VIEW "newsletter_to_send" AS

248 SELECT

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

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

251 "newsletter"."published"

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

253 LEFT JOIN "privilege" ON

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

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

256 "privilege"."voting_right" = TRUE

257 LEFT JOIN "subscription" ON

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

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

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

261 AND "newsletter"."sent" ISNULL

262 AND (

263 "member"."disable_notifications" = FALSE OR

264 "newsletter"."include_all_members" = TRUE )

265 AND (

266 "newsletter"."unit_id" ISNULL OR

267 "privilege"."member_id" NOTNULL OR

268 "subscription"."member_id" NOTNULL );

269

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

271

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

273

274

275 DROP VIEW "expired_session";

276 DROP TABLE "session";

277

278

279 CREATE TABLE "session" (

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

281 "id" SERIAL8 PRIMARY KEY,

282 "ident" TEXT NOT NULL UNIQUE,

283 "additional_secret" TEXT,

284 "logout_token" TEXT,

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

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

287 "authority" TEXT,

288 "authority_uid" TEXT,

289 "authority_login" TEXT,

290 "needs_delegation_check" BOOLEAN NOT NULL DEFAULT FALSE,

291 "lang" TEXT );

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

293

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

295

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

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

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

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

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

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

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

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

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

305

306

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

308

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

310

311

312 CREATE TABLE "system_application" (

313 "id" SERIAL4 PRIMARY KEY,

314 "name" TEXT NOT NULL,

315 "discovery_baseurl" TEXT,

316 "client_id" TEXT NOT NULL UNIQUE,

317 "default_redirect_uri" TEXT NOT NULL,

318 "cert_common_name" TEXT,

319 "client_cred_scope" TEXT,

320 "flow" "authflow",

321 "automatic_scope" TEXT,

322 "permitted_scope" TEXT,

323 "forbidden_scope" TEXT );

324

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

326

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

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

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

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

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

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

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

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

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

336

337

338 CREATE TABLE "system_application_redirect_uri" (

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

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

341 "redirect_uri" TEXT );

342

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

344

345

346 CREATE TABLE "dynamic_application_scope" (

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

348 "redirect_uri" TEXT,

349 "flow" TEXT,

350 "scope" TEXT,

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

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

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

354

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

356

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

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

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

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

361

362

363 CREATE TABLE "member_application" (

364 "id" SERIAL4 PRIMARY KEY,

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

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

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

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

369 "domain" TEXT,

370 "session_id" INT8,

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

372 "scope" TEXT NOT NULL,

373 CONSTRAINT "system_application_or_domain_but_not_both" CHECK (

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

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

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

377

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

379

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

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

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

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

384

385

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

387

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

389

390

391 CREATE TABLE "token" (

392 "id" SERIAL8 PRIMARY KEY,

393 "token" TEXT NOT NULL UNIQUE,

394 "token_type" "token_type" NOT NULL,

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

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

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

398 "domain" TEXT,

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

400 "session_id" INT8,

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

402 "redirect_uri" TEXT,

403 "redirect_uri_explicit" BOOLEAN,

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

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

406 "used" BOOLEAN NOT NULL DEFAULT FALSE,

407 "scope" TEXT NOT NULL,

408 CONSTRAINT "access_token_needs_expiry"

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

410 CONSTRAINT "authorization_token_needs_redirect_uri"

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

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

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

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

415

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

417

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

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

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

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

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

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

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

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

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

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

428

429

430 CREATE TABLE "token_scope" (

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

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

433 "index" INT4,

434 "scope" TEXT NOT NULL );

435

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

437

438

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

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

441

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

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

444

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

446

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

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

449 ALTER TABLE "policy" ADD CONSTRAINT

450 "issue_quorum_if_and_only_if_not_polling" CHECK (

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

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

453 "polling" = ("issue_quorum_den" ISNULL)

454 );

455 ALTER TABLE "policy" ADD CONSTRAINT

456 "min_admission_time_smaller_than_max_admission_time" CHECK (

457 "min_admission_time" < "max_admission_time"

458 );

459 ALTER TABLE "policy" ADD CONSTRAINT

460 "timing_null_or_not_null_constraints" CHECK (

461 ( "polling" = FALSE AND

462 "min_admission_time" NOTNULL AND "max_admission_time" NOTNULL AND

463 "discussion_time" NOTNULL AND

464 "verification_time" NOTNULL AND

465 "voting_time" NOTNULL ) OR

466 ( "polling" = TRUE AND

467 "min_admission_time" ISNULL AND "max_admission_time" ISNULL AND

468 "discussion_time" NOTNULL AND

469 "verification_time" NOTNULL AND

470 "voting_time" NOTNULL ) OR

471 ( "polling" = TRUE AND

472 "min_admission_time" ISNULL AND "max_admission_time" ISNULL AND

473 "discussion_time" ISNULL AND

474 "verification_time" ISNULL AND

475 "voting_time" ISNULL )

476 );

477

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

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

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

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

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

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

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

485

486

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

488

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

490

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

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

493

494

495 DROP INDEX "area_unit_id_idx";

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

497

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

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

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

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

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

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

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

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

506

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

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

509

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

511

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

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

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

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

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

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

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

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

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

521

522

523 CREATE TABLE "snapshot" (

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

525 "id" SERIAL8 PRIMARY KEY,

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

527 "population" INT4,

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

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

530

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

532

533

534 CREATE TABLE "snapshot_population" (

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

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

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

538

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

540

541

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

543 DROP INDEX "issue_area_id_idx";

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

545 DROP INDEX "issue_policy_id_idx";

546

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

548

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

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

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

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

553

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

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

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

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

558

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

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

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

562

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

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

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

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

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

568

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

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

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

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

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

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

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

576

577

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

579

580

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

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

583

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

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

586

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

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

589

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

591

592

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

594

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

596

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

598

599

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

601

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

603

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

605

606

607 CREATE TABLE "temporary_suggestion_counts" (

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

609 "minus2_unfulfilled_count" INT4 NOT NULL,

610 "minus2_fulfilled_count" INT4 NOT NULL,

611 "minus1_unfulfilled_count" INT4 NOT NULL,

612 "minus1_fulfilled_count" INT4 NOT NULL,

613 "plus1_unfulfilled_count" INT4 NOT NULL,

614 "plus1_fulfilled_count" INT4 NOT NULL,

615 "plus2_unfulfilled_count" INT4 NOT NULL,

616 "plus2_fulfilled_count" INT4 NOT NULL );

617

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

619

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

621

622

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

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

625

626

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

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

629

630

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

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

633

634

635 CREATE TABLE "snapshot_issue" (

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

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

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

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

640

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

642

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

644

645

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

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

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

649

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

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

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

653

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

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

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

657

658

659 CREATE TABLE "direct_interest_snapshot" (

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

661 "snapshot_id" INT8,

662 "issue_id" INT4,

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

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

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

666 "weight" INT4 );

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

668

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

670

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

672

673

674 CREATE TABLE "delegating_interest_snapshot" (

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

676 "snapshot_id" INT8,

677 "issue_id" INT4,

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

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

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

681 "weight" INT4,

682 "scope" "delegation_scope" NOT NULL,

683 "delegate_member_ids" INT4[] NOT NULL );

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

685

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

687

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

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

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

691

692

693 CREATE TABLE "direct_supporter_snapshot" (

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

695 "snapshot_id" INT8,

696 "issue_id" INT4 NOT NULL,

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

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

699 "initiative_id" INT4,

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

701 "draft_id" INT8 NOT NULL,

702 "informed" BOOLEAN NOT NULL,

703 "satisfied" BOOLEAN NOT NULL,

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

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

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

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

708

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

710

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

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

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

714

715

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

717 DROP INDEX "non_voter_member_id_idx";

718

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

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

721

722

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

724 SELECT

725 "member_id",

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

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

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

729

730

731 DROP TABLE "setting";

732 DROP TABLE "setting_map";

733 DROP TABLE "member_relation_setting";

734 DROP TABLE "unit_setting";

735 DROP TABLE "area_setting";

736 DROP TABLE "initiative_setting";

737 DROP TABLE "suggestion_setting";

738

739

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

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

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

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

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

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

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

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

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

749

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

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

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

753

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

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

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

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

758

759 UPDATE "event" SET

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

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

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

763 FROM "issue", "area"

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

765

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

767 "event" != 'issue_state_changed' OR (

768 "member_id" ISNULL AND

769 "other_member_id" ISNULL AND

770 "scope" ISNULL AND

771 "unit_id" NOTNULL AND

772 "area_id" NOTNULL AND

773 "policy_id" NOTNULL AND

774 "issue_id" NOTNULL AND

775 "state" NOTNULL AND

776 "initiative_id" ISNULL AND

777 "draft_id" ISNULL AND

778 "suggestion_id" ISNULL AND

779 "boolean_value" ISNULL AND

780 "numeric_value" ISNULL AND

781 "text_value" ISNULL AND

782 "old_text_value" ISNULL ));

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

784 "event" NOT IN (

785 'initiative_created_in_new_issue',

786 'initiative_created_in_existing_issue',

787 'initiative_revoked',

788 'new_draft_created'

789 ) OR (

790 "member_id" NOTNULL AND

791 "other_member_id" ISNULL AND

792 "scope" ISNULL AND

793 "unit_id" NOTNULL AND

794 "area_id" NOTNULL AND

795 "policy_id" NOTNULL AND

796 "issue_id" NOTNULL AND

797 "state" NOTNULL AND

798 "initiative_id" NOTNULL AND

799 "draft_id" NOTNULL AND

800 "suggestion_id" ISNULL AND

801 "boolean_value" ISNULL AND

802 "numeric_value" ISNULL AND

803 "text_value" ISNULL AND

804 "old_text_value" ISNULL ));

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

806 "event" != 'suggestion_created' OR (

807 "member_id" NOTNULL AND

808 "other_member_id" ISNULL AND

809 "scope" ISNULL AND

810 "unit_id" NOTNULL AND

811 "area_id" NOTNULL AND

812 "policy_id" NOTNULL AND

813 "issue_id" NOTNULL AND

814 "state" NOTNULL AND

815 "initiative_id" NOTNULL AND

816 "draft_id" ISNULL AND

817 "suggestion_id" NOTNULL AND

818 "boolean_value" ISNULL AND

819 "numeric_value" ISNULL AND

820 "text_value" ISNULL AND

821 "old_text_value" ISNULL ));

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

823 "event" != 'suggestion_deleted' OR (

824 "member_id" ISNULL AND

825 "other_member_id" ISNULL AND

826 "scope" ISNULL AND

827 "unit_id" NOTNULL AND

828 "area_id" NOTNULL AND

829 "policy_id" NOTNULL AND

830 "issue_id" NOTNULL AND

831 "state" NOTNULL AND

832 "initiative_id" NOTNULL AND

833 "draft_id" ISNULL AND

834 "suggestion_id" NOTNULL AND

835 "boolean_value" ISNULL AND

836 "numeric_value" ISNULL AND

837 "text_value" ISNULL AND

838 "old_text_value" ISNULL ));

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

840 "event" NOT IN (

841 'member_activated',

842 'member_deleted',

843 'member_profile_updated',

844 'member_image_updated'

845 ) OR (

846 "member_id" NOTNULL AND

847 "other_member_id" ISNULL AND

848 "scope" ISNULL AND

849 "unit_id" ISNULL AND

850 "area_id" ISNULL AND

851 "policy_id" ISNULL AND

852 "issue_id" ISNULL AND

853 "state" ISNULL AND

854 "initiative_id" ISNULL AND

855 "draft_id" ISNULL AND

856 "suggestion_id" ISNULL AND

857 "boolean_value" ISNULL AND

858 "numeric_value" ISNULL AND

859 "text_value" ISNULL AND

860 "old_text_value" ISNULL ));

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

862 "event" != 'member_active' OR (

863 "member_id" NOTNULL AND

864 "other_member_id" ISNULL AND

865 "scope" ISNULL AND

866 "unit_id" ISNULL AND

867 "area_id" ISNULL AND

868 "policy_id" ISNULL AND

869 "issue_id" ISNULL AND

870 "state" ISNULL AND

871 "initiative_id" ISNULL AND

872 "draft_id" ISNULL AND

873 "suggestion_id" ISNULL AND

874 "boolean_value" NOTNULL AND

875 "numeric_value" ISNULL AND

876 "text_value" ISNULL AND

877 "old_text_value" ISNULL ));

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

879 "event" != 'member_name_updated' OR (

880 "member_id" NOTNULL AND

881 "other_member_id" ISNULL AND

882 "scope" ISNULL AND

883 "unit_id" ISNULL AND

884 "area_id" ISNULL AND

885 "policy_id" ISNULL AND

886 "issue_id" ISNULL AND

887 "state" ISNULL AND

888 "initiative_id" ISNULL AND

889 "draft_id" ISNULL AND

890 "suggestion_id" ISNULL AND

891 "boolean_value" ISNULL AND

892 "numeric_value" ISNULL AND

893 "text_value" NOTNULL AND

894 "old_text_value" NOTNULL ));

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

896 "event" != 'interest' OR (

897 "member_id" NOTNULL AND

898 "other_member_id" ISNULL AND

899 "scope" ISNULL AND

900 "unit_id" NOTNULL AND

901 "area_id" NOTNULL AND

902 "policy_id" NOTNULL AND

903 "issue_id" NOTNULL AND

904 "state" NOTNULL AND

905 "initiative_id" ISNULL AND

906 "draft_id" ISNULL AND

907 "suggestion_id" ISNULL AND

908 "boolean_value" NOTNULL AND

909 "numeric_value" ISNULL AND

910 "text_value" ISNULL AND

911 "old_text_value" ISNULL ));

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

913 "event" != 'initiator' OR (

914 "member_id" NOTNULL AND

915 "other_member_id" ISNULL AND

916 "scope" ISNULL AND

917 "unit_id" NOTNULL AND

918 "area_id" NOTNULL AND

919 "policy_id" NOTNULL AND

920 "issue_id" NOTNULL AND

921 "state" NOTNULL AND

922 "initiative_id" NOTNULL AND

923 "draft_id" ISNULL AND

924 "suggestion_id" ISNULL AND

925 "boolean_value" NOTNULL AND

926 "numeric_value" ISNULL AND

927 "text_value" ISNULL AND

928 "old_text_value" ISNULL ));

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

930 "event" != 'support' OR (

931 "member_id" NOTNULL AND

932 "other_member_id" ISNULL AND

933 "scope" ISNULL AND

934 "unit_id" NOTNULL AND

935 "area_id" NOTNULL AND

936 "policy_id" NOTNULL AND

937 "issue_id" NOTNULL AND

938 "state" NOTNULL AND

939 "initiative_id" NOTNULL AND

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

941 "suggestion_id" ISNULL AND

942 "boolean_value" NOTNULL AND

943 "numeric_value" ISNULL AND

944 "text_value" ISNULL AND

945 "old_text_value" ISNULL ));

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

947 "event" != 'support_updated' OR (

948 "member_id" NOTNULL AND

949 "other_member_id" ISNULL AND

950 "scope" ISNULL AND

951 "unit_id" NOTNULL AND

952 "area_id" NOTNULL AND

953 "policy_id" NOTNULL AND

954 "issue_id" NOTNULL AND

955 "state" NOTNULL AND

956 "initiative_id" NOTNULL AND

957 "draft_id" NOTNULL AND

958 "suggestion_id" ISNULL AND

959 "boolean_value" ISNULL AND

960 "numeric_value" ISNULL AND

961 "text_value" ISNULL AND

962 "old_text_value" ISNULL ));

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

964 "event" != 'suggestion_rated' OR (

965 "member_id" NOTNULL AND

966 "other_member_id" ISNULL AND

967 "scope" ISNULL AND

968 "unit_id" NOTNULL AND

969 "area_id" NOTNULL AND

970 "policy_id" NOTNULL AND

971 "issue_id" NOTNULL AND

972 "state" NOTNULL AND

973 "initiative_id" NOTNULL AND

974 "draft_id" ISNULL AND

975 "suggestion_id" NOTNULL AND

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

977 "numeric_value" NOTNULL AND

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

979 "text_value" ISNULL AND

980 "old_text_value" ISNULL ));

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

982 "event" != 'delegation' OR (

983 "member_id" NOTNULL AND

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

985 "scope" NOTNULL AND

986 "unit_id" NOTNULL AND

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

988 "policy_id" ISNULL AND

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

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

991 "initiative_id" ISNULL AND

992 "draft_id" ISNULL AND

993 "suggestion_id" ISNULL AND

994 "boolean_value" NOTNULL AND

995 "numeric_value" ISNULL AND

996 "text_value" ISNULL AND

997 "old_text_value" ISNULL ));

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

999 "event" != 'contact' OR (

1000 "member_id" NOTNULL AND

1001 "other_member_id" NOTNULL AND

1002 "scope" ISNULL AND

1003 "unit_id" ISNULL AND

1004 "area_id" ISNULL AND

1005 "policy_id" ISNULL AND

1006 "issue_id" ISNULL AND

1007 "state" ISNULL AND

1008 "initiative_id" ISNULL AND

1009 "draft_id" ISNULL AND

1010 "suggestion_id" ISNULL AND

1011 "boolean_value" NOTNULL AND

1012 "numeric_value" ISNULL AND

1013 "text_value" ISNULL AND

1014 "old_text_value" ISNULL ));

1015

1016

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

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

1019

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

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

1022

1023

1024 CREATE FUNCTION "write_event_unit_trigger"()

1025 RETURNS TRIGGER

1026 LANGUAGE 'plpgsql' VOLATILE AS $$

1027 DECLARE

1028 "event_v" "event_type";

1029 BEGIN

1030 IF TG_OP = 'UPDATE' THEN

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

1032 RETURN NULL;

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

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

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

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

1037 ELSIF OLD != NEW THEN

1038 "event_v" := 'unit_updated';

1039 ELSE

1040 RETURN NULL;

1041 END IF;

1042 ELSE

1043 "event_v" := 'unit_created';

1044 END IF;

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

1046 RETURN NULL;

1047 END;

1048 $$;

1049

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

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

1052

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

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

1055

1056

1057 CREATE FUNCTION "write_event_area_trigger"()

1058 RETURNS TRIGGER

1059 LANGUAGE 'plpgsql' VOLATILE AS $$

1060 DECLARE

1061 "event_v" "event_type";

1062 BEGIN

1063 IF TG_OP = 'UPDATE' THEN

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

1065 RETURN NULL;

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

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

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

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

1070 ELSIF OLD != NEW THEN

1071 "event_v" := 'area_updated';

1072 ELSE

1073 RETURN NULL;

1074 END IF;

1075 ELSE

1076 "event_v" := 'area_created';

1077 END IF;

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

1079 RETURN NULL;

1080 END;

1081 $$;

1082

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

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

1085

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

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

1088

1089

1090 CREATE FUNCTION "write_event_policy_trigger"()

1091 RETURNS TRIGGER

1092 LANGUAGE 'plpgsql' VOLATILE AS $$

1093 DECLARE

1094 "event_v" "event_type";

1095 BEGIN

1096 IF TG_OP = 'UPDATE' THEN

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

1098 RETURN NULL;

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

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

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

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

1103 ELSIF OLD != NEW THEN

1104 "event_v" := 'policy_updated';

1105 ELSE

1106 RETURN NULL;

1107 END IF;

1108 ELSE

1109 "event_v" := 'policy_created';

1110 END IF;

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

1112 RETURN NULL;

1113 END;

1114 $$;

1115

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

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

1118

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

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

1121

1122

1123 CREATE OR REPLACE FUNCTION "write_event_issue_state_changed_trigger"()

1124 RETURNS TRIGGER

1125 LANGUAGE 'plpgsql' VOLATILE AS $$

1126 DECLARE

1127 "area_row" "area"%ROWTYPE;

1128 BEGIN

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

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

1131 FOR SHARE;

1132 INSERT INTO "event" (

1133 "event",

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

1135 ) VALUES (

1136 'issue_state_changed',

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

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

1139 );

1140 END IF;

1141 RETURN NULL;

1142 END;

1143 $$;

1144

1145

1146 CREATE OR REPLACE FUNCTION "write_event_initiative_or_draft_created_trigger"()

1147 RETURNS TRIGGER

1148 LANGUAGE 'plpgsql' VOLATILE AS $$

1149 DECLARE

1150 "initiative_row" "initiative"%ROWTYPE;

1151 "issue_row" "issue"%ROWTYPE;

1152 "area_row" "area"%ROWTYPE;

1153 "event_v" "event_type";

1154 BEGIN

1155 SELECT * INTO "initiative_row" FROM "initiative"

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

1157 SELECT * INTO "issue_row" FROM "issue"

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

1159 SELECT * INTO "area_row" FROM "area"

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

1161 IF EXISTS (

1162 SELECT NULL FROM "draft"

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

1164 FOR SHARE

1165 ) THEN

1166 "event_v" := 'new_draft_created';

1167 ELSE

1168 IF EXISTS (

1169 SELECT NULL FROM "initiative"

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

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

1172 FOR SHARE

1173 ) THEN

1174 "event_v" := 'initiative_created_in_existing_issue';

1175 ELSE

1176 "event_v" := 'initiative_created_in_new_issue';

1177 END IF;

1178 END IF;

1179 INSERT INTO "event" (

1180 "event", "member_id",

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

1182 "initiative_id", "draft_id"

1183 ) VALUES (

1184 "event_v", NEW."author_id",

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

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

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

1188 );

1189 RETURN NULL;

1190 END;

1191 $$;

1192

1193

1194 CREATE OR REPLACE FUNCTION "write_event_initiative_revoked_trigger"()

1195 RETURNS TRIGGER

1196 LANGUAGE 'plpgsql' VOLATILE AS $$

1197 DECLARE

1198 "issue_row" "issue"%ROWTYPE;

1199 "area_row" "area"%ROWTYPE;

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

1201 BEGIN

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

1203 SELECT * INTO "issue_row" FROM "issue"

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

1205 SELECT * INTO "area_row" FROM "area"

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

1207 SELECT "id" INTO "draft_id_v" FROM "current_draft"

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

1209 INSERT INTO "event" (

1210 "event", "member_id",

1211 "unit_id", "area_id", "policy_id", "issue_id", "state",

1212 "initiative_id", "draft_id"

1213 ) VALUES (

1214 'initiative_revoked', NEW."revoked_by_member_id",

1215 "area_row"."unit_id", "issue_row"."area_id",

1216 "issue_row"."policy_id",

1217 NEW."issue_id", "issue_row"."state",

1218 NEW."id", "draft_id_v"

1219 );

1220 END IF;

1221 RETURN NULL;

1222 END;

1223 $$;

1224

1225

1226 CREATE OR REPLACE FUNCTION "write_event_suggestion_created_trigger"()

1227 RETURNS TRIGGER

1228 LANGUAGE 'plpgsql' VOLATILE AS $$

1229 DECLARE

1230 "initiative_row" "initiative"%ROWTYPE;

1231 "issue_row" "issue"%ROWTYPE;

1232 "area_row" "area"%ROWTYPE;

1233 BEGIN

1234 SELECT * INTO "initiative_row" FROM "initiative"

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

1236 SELECT * INTO "issue_row" FROM "issue"

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

1238 SELECT * INTO "area_row" FROM "area"

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

1240 INSERT INTO "event" (

1241 "event", "member_id",

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

1243 "initiative_id", "suggestion_id"

1244 ) VALUES (

1245 'suggestion_created', NEW."author_id",

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

1247 "initiative_row"."issue_id", "issue_row"."state",

1248 NEW."initiative_id", NEW."id"

1249 );

1250 RETURN NULL;

1251 END;

1252 $$;

1253

1254

1255 CREATE FUNCTION "write_event_suggestion_removed_trigger"()

1256 RETURNS TRIGGER

1257 LANGUAGE 'plpgsql' VOLATILE AS $$

1258 DECLARE

1259 "initiative_row" "initiative"%ROWTYPE;

1260 "issue_row" "issue"%ROWTYPE;

1261 "area_row" "area"%ROWTYPE;

1262 BEGIN

1263 SELECT * INTO "initiative_row" FROM "initiative"

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

1265 IF "initiative_row"."id" NOTNULL THEN

1266 SELECT * INTO "issue_row" FROM "issue"

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

1268 SELECT * INTO "area_row" FROM "area"

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

1270 INSERT INTO "event" (

1271 "event",

1272 "unit_id", "area_id", "policy_id", "issue_id", "state",

1273 "initiative_id", "suggestion_id"

1274 ) VALUES (

1275 'suggestion_deleted',

1276 "area_row"."unit_id", "issue_row"."area_id",

1277 "issue_row"."policy_id",

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

1279 OLD."initiative_id", OLD."id"

1280 );

1281 END IF;

1282 RETURN NULL;

1283 END;

1284 $$;

1285

1286 CREATE TRIGGER "write_event_suggestion_removed"

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

1288 "write_event_suggestion_removed_trigger"();

1289

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

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

1292

1293

1294 CREATE FUNCTION "write_event_member_trigger"()

1295 RETURNS TRIGGER

1296 LANGUAGE 'plpgsql' VOLATILE AS $$

1297 BEGIN

1298 IF TG_OP = 'INSERT' THEN

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

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

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

1302 END IF;

1303 IF NEW."active" THEN

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

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

1306 END IF;

1307 ELSIF TG_OP = 'UPDATE' THEN

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

1309 RAISE EXCEPTION 'Cannot change member ID';

1310 END IF;

1311 IF

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

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

1314 THEN

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

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

1317 END IF;

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

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

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

1321 );

1322 END IF;

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

1324 INSERT INTO "event" (

1325 "event", "member_id", "text_value", "old_text_value"

1326 ) VALUES (

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

1328 );

1329 END IF;

1330 IF

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

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

1333 THEN

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

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

1336 END IF;

1337 END IF;

1338 RETURN NULL;

1339 END;

1340 $$;

1341

1342 CREATE TRIGGER "write_event_member"

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

1344 "write_event_member_trigger"();

1345

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

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

1348

1349

1350 CREATE FUNCTION "write_event_member_profile_updated_trigger"()

1351 RETURNS TRIGGER

1352 LANGUAGE 'plpgsql' VOLATILE AS $$

1353 BEGIN

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

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

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

1357 'member_profile_updated', OLD."member_id"

1358 );

1359 END IF;

1360 END IF;

1361 IF TG_OP = 'UPDATE' THEN

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

1363 RETURN NULL;

1364 END IF;

1365 END IF;

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

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

1368 'member_profile_updated', NEW."member_id"

1369 );

1370 END IF;

1371 RETURN NULL;

1372 END;

1373 $$;

1374

1375 CREATE TRIGGER "write_event_member_profile_updated"

1376 AFTER INSERT OR UPDATE OR DELETE ON "member_profile"

1377 FOR EACH ROW EXECUTE PROCEDURE

1378 "write_event_member_profile_updated_trigger"();

1379

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

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

1382

1383

1384 CREATE FUNCTION "write_event_member_image_updated_trigger"()

1385 RETURNS TRIGGER

1386 LANGUAGE 'plpgsql' VOLATILE AS $$

1387 BEGIN

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

1389 IF NOT OLD."scaled" THEN

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

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

1392 'member_image_updated', OLD."member_id"

1393 );

1394 END IF;

1395 END IF;

1396 END IF;

1397 IF TG_OP = 'UPDATE' THEN

1398 IF

1399 OLD."member_id" = NEW."member_id" AND

1400 OLD."scaled" = NEW."scaled"

1401 THEN

1402 RETURN NULL;

1403 END IF;

1404 END IF;

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

1406 IF NOT NEW."scaled" THEN

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

1408 'member_image_updated', NEW."member_id"

1409 );

1410 END IF;

1411 END IF;

1412 RETURN NULL;

1413 END;

1414 $$;

1415

1416 CREATE TRIGGER "write_event_member_image_updated"

1417 AFTER INSERT OR UPDATE OR DELETE ON "member_image"

1418 FOR EACH ROW EXECUTE PROCEDURE

1419 "write_event_member_image_updated_trigger"();

1420

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

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

1423

1424

1425 CREATE FUNCTION "write_event_interest_trigger"()

1426 RETURNS TRIGGER

1427 LANGUAGE 'plpgsql' VOLATILE AS $$

1428 DECLARE

1429 "issue_row" "issue"%ROWTYPE;

1430 "area_row" "area"%ROWTYPE;

1431 BEGIN

1432 IF TG_OP = 'UPDATE' THEN

1433 IF OLD = NEW THEN

1434 RETURN NULL;

1435 END IF;

1436 END IF;

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

1438 SELECT * INTO "issue_row" FROM "issue"

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

1440 SELECT * INTO "area_row" FROM "area"

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

1442 IF "issue_row"."id" NOTNULL THEN

1443 INSERT INTO "event" (

1444 "event", "member_id",

1445 "unit_id", "area_id", "policy_id", "issue_id", "state",

1446 "boolean_value"

1447 ) VALUES (

1448 'interest', OLD."member_id",

1449 "area_row"."unit_id", "issue_row"."area_id",

1450 "issue_row"."policy_id",

1451 OLD."issue_id", "issue_row"."state",

1452 FALSE

1453 );

1454 END IF;

1455 END IF;

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

1457 SELECT * INTO "issue_row" FROM "issue"

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

1459 SELECT * INTO "area_row" FROM "area"

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

1461 INSERT INTO "event" (

1462 "event", "member_id",

1463 "unit_id", "area_id", "policy_id", "issue_id", "state",

1464 "boolean_value"

1465 ) VALUES (

1466 'interest', NEW."member_id",

1467 "area_row"."unit_id", "issue_row"."area_id",

1468 "issue_row"."policy_id",

1469 NEW."issue_id", "issue_row"."state",

1470 TRUE

1471 );

1472 END IF;

1473 RETURN NULL;

1474 END;

1475 $$;

1476

1477 CREATE TRIGGER "write_event_interest"

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

1479 "write_event_interest_trigger"();

1480

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

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

1483

1484

1485 CREATE FUNCTION "write_event_initiator_trigger"()

1486 RETURNS TRIGGER

1487 LANGUAGE 'plpgsql' VOLATILE AS $$

1488 DECLARE

1489 "initiative_row" "initiative"%ROWTYPE;

1490 "issue_row" "issue"%ROWTYPE;

1491 "area_row" "area"%ROWTYPE;

1492 "accepted_v" BOOLEAN = FALSE;

1493 "rejected_v" BOOLEAN = FALSE;

1494 BEGIN

1495 IF TG_OP = 'UPDATE' THEN

1496 IF

1497 OLD."initiative_id" = NEW."initiative_id" AND

1498 OLD."member_id" = NEW."member_id"

1499 THEN

1500 IF

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

1502 THEN

1503 RETURN NULL;

1504 END IF;

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

1506 "accepted_v" := TRUE;

1507 ELSE

1508 "rejected_v" := TRUE;

1509 END IF;

1510 END IF;

1511 END IF;

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

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

1514 SELECT * INTO "initiative_row" FROM "initiative"

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

1516 IF "initiative_row"."id" NOTNULL THEN

1517 SELECT * INTO "issue_row" FROM "issue"

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

1519 SELECT * INTO "area_row" FROM "area"

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

1521 INSERT INTO "event" (

1522 "event", "member_id",

1523 "unit_id", "area_id", "policy_id", "issue_id", "state",

1524 "initiative_id", "boolean_value"

1525 ) VALUES (

1526 'initiator', OLD."member_id",

1527 "area_row"."unit_id", "issue_row"."area_id",

1528 "issue_row"."policy_id",

1529 "issue_row"."id", "issue_row"."state",

1530 OLD."initiative_id", FALSE

1531 );

1532 END IF;

1533 END IF;

1534 END IF;

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

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

1537 SELECT * INTO "initiative_row" FROM "initiative"

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

1539 SELECT * INTO "issue_row" FROM "issue"

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

1541 SELECT * INTO "area_row" FROM "area"

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

1543 INSERT INTO "event" (

1544 "event", "member_id",

1545 "unit_id", "area_id", "policy_id", "issue_id", "state",

1546 "initiative_id", "boolean_value"

1547 ) VALUES (

1548 'initiator', NEW."member_id",

1549 "area_row"."unit_id", "issue_row"."area_id",

1550 "issue_row"."policy_id",

1551 "issue_row"."id", "issue_row"."state",

1552 NEW."initiative_id", TRUE

1553 );

1554 END IF;

1555 END IF;

1556 RETURN NULL;

1557 END;

1558 $$;

1559

1560 CREATE TRIGGER "write_event_initiator"

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

1562 "write_event_initiator_trigger"();

1563

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

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

1566

1567

1568 CREATE FUNCTION "write_event_support_trigger"()

1569 RETURNS TRIGGER

1570 LANGUAGE 'plpgsql' VOLATILE AS $$

1571 DECLARE

1572 "issue_row" "issue"%ROWTYPE;

1573 "area_row" "area"%ROWTYPE;

1574 BEGIN

1575 IF TG_OP = 'UPDATE' THEN

1576 IF

1577 OLD."initiative_id" = NEW."initiative_id" AND

1578 OLD."member_id" = NEW."member_id"

1579 THEN

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

1581 SELECT * INTO "issue_row" FROM "issue"

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

1583 SELECT * INTO "area_row" FROM "area"

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

1585 INSERT INTO "event" (

1586 "event", "member_id",

1587 "unit_id", "area_id", "policy_id", "issue_id", "state",

1588 "initiative_id", "draft_id"

1589 ) VALUES (

1590 'support_updated', NEW."member_id",

1591 "area_row"."unit_id", "issue_row"."area_id",

1592 "issue_row"."policy_id",

1593 "issue_row"."id", "issue_row"."state",

1594 NEW."initiative_id", NEW."draft_id"

1595 );

1596 END IF;

1597 RETURN NULL;

1598 END IF;

1599 END IF;

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

1601 IF EXISTS (

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

1603 FOR SHARE

1604 ) THEN

1605 SELECT * INTO "issue_row" FROM "issue"

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

1607 SELECT * INTO "area_row" FROM "area"

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

1609 INSERT INTO "event" (

1610 "event", "member_id",

1611 "unit_id", "area_id", "policy_id", "issue_id", "state",

1612 "initiative_id", "boolean_value"

1613 ) VALUES (

1614 'support', OLD."member_id",

1615 "area_row"."unit_id", "issue_row"."area_id",

1616 "issue_row"."policy_id",

1617 "issue_row"."id", "issue_row"."state",

1618 OLD."initiative_id", FALSE

1619 );

1620 END IF;

1621 END IF;

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

1623 SELECT * INTO "issue_row" FROM "issue"

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

1625 SELECT * INTO "area_row" FROM "area"

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

1627 INSERT INTO "event" (

1628 "event", "member_id",

1629 "unit_id", "area_id", "policy_id", "issue_id", "state",

1630 "initiative_id", "draft_id", "boolean_value"

1631 ) VALUES (

1632 'support', NEW."member_id",

1633 "area_row"."unit_id", "issue_row"."area_id",

1634 "issue_row"."policy_id",

1635 "issue_row"."id", "issue_row"."state",

1636 NEW."initiative_id", NEW."draft_id", TRUE

1637 );

1638 END IF;

1639 RETURN NULL;

1640 END;

1641 $$;

1642

1643 CREATE TRIGGER "write_event_support"

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

1645 "write_event_support_trigger"();

1646

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

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

1649

1650

1651 CREATE FUNCTION "write_event_suggestion_rated_trigger"()

1652 RETURNS TRIGGER

1653 LANGUAGE 'plpgsql' VOLATILE AS $$

1654 DECLARE

1655 "same_pkey_v" BOOLEAN = FALSE;

1656 "initiative_row" "initiative"%ROWTYPE;

1657 "issue_row" "issue"%ROWTYPE;

1658 "area_row" "area"%ROWTYPE;

1659 BEGIN

1660 IF TG_OP = 'UPDATE' THEN

1661 IF

1662 OLD."suggestion_id" = NEW."suggestion_id" AND

1663 OLD."member_id" = NEW."member_id"

1664 THEN

1665 IF

1666 OLD."degree" = NEW."degree" AND

1667 OLD."fulfilled" = NEW."fulfilled"

1668 THEN

1669 RETURN NULL;

1670 END IF;

1671 "same_pkey_v" := TRUE;

1672 END IF;

1673 END IF;

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

1675 IF EXISTS (

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

1677 FOR SHARE

1678 ) THEN

1679 SELECT * INTO "initiative_row" FROM "initiative"

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

1681 SELECT * INTO "issue_row" FROM "issue"

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

1683 SELECT * INTO "area_row" FROM "area"

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

1685 INSERT INTO "event" (

1686 "event", "member_id",

1687 "unit_id", "area_id", "policy_id", "issue_id", "state",

1688 "initiative_id", "suggestion_id",

1689 "boolean_value", "numeric_value"

1690 ) VALUES (

1691 'suggestion_rated', OLD."member_id",

1692 "area_row"."unit_id", "issue_row"."area_id",

1693 "issue_row"."policy_id",

1694 "initiative_row"."issue_id", "issue_row"."state",

1695 OLD."initiative_id", OLD."suggestion_id",

1696 NULL, 0

1697 );

1698 END IF;

1699 END IF;

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

1701 SELECT * INTO "initiative_row" FROM "initiative"

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

1703 SELECT * INTO "issue_row" FROM "issue"

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

1705 SELECT * INTO "area_row" FROM "area"

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

1707 INSERT INTO "event" (

1708 "event", "member_id",

1709 "unit_id", "area_id", "policy_id", "issue_id", "state",

1710 "initiative_id", "suggestion_id",

1711 "boolean_value", "numeric_value"

1712 ) VALUES (

1713 'suggestion_rated', NEW."member_id",

1714 "area_row"."unit_id", "issue_row"."area_id",

1715 "issue_row"."policy_id",

1716 "initiative_row"."issue_id", "issue_row"."state",

1717 NEW."initiative_id", NEW."suggestion_id",

1718 NEW."fulfilled", NEW."degree"

1719 );

1720 END IF;

1721 RETURN NULL;

1722 END;

1723 $$;

1724

1725 CREATE TRIGGER "write_event_suggestion_rated"

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

1727 "write_event_suggestion_rated_trigger"();

1728

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

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

1731

1732

1733 CREATE FUNCTION "write_event_delegation_trigger"()

1734 RETURNS TRIGGER

1735 LANGUAGE 'plpgsql' VOLATILE AS $$

1736 DECLARE

1737 "issue_row" "issue"%ROWTYPE;

1738 "area_row" "area"%ROWTYPE;

1739 BEGIN

1740 IF TG_OP = 'DELETE' THEN

1741 IF EXISTS (

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

1743 ) AND (CASE OLD."scope"

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

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

1746 )

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

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

1749 )

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

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

1752 )

1753 END) THEN

1754 SELECT * INTO "issue_row" FROM "issue"

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

1756 SELECT * INTO "area_row" FROM "area"

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

1758 FOR SHARE;

1759 INSERT INTO "event" (

1760 "event", "member_id", "scope",

1761 "unit_id", "area_id", "issue_id", "state",

1762 "boolean_value"

1763 ) VALUES (

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

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

1766 OLD."issue_id", "issue_row"."state",

1767 FALSE

1768 );

1769 END IF;

1770 ELSE

1771 SELECT * INTO "issue_row" FROM "issue"

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

1773 SELECT * INTO "area_row" FROM "area"

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

1775 FOR SHARE;

1776 INSERT INTO "event" (

1777 "event", "member_id", "other_member_id", "scope",

1778 "unit_id", "area_id", "issue_id", "state",

1779 "boolean_value"

1780 ) VALUES (

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

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

1783 NEW."issue_id", "issue_row"."state",

1784 TRUE

1785 );

1786 END IF;

1787 RETURN NULL;

1788 END;

1789 $$;

1790

1791 CREATE TRIGGER "write_event_delegation"

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

1793 "write_event_delegation_trigger"();

1794

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

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

1797

1798

1799 CREATE FUNCTION "write_event_contact_trigger"()

1800 RETURNS TRIGGER

1801 LANGUAGE 'plpgsql' VOLATILE AS $$

1802 BEGIN

1803 IF TG_OP = 'UPDATE' THEN

1804 IF

1805 OLD."member_id" = NEW."member_id" AND

1806 OLD."other_member_id" = NEW."other_member_id" AND

1807 OLD."public" = NEW."public"

1808 THEN

1809 RETURN NULL;

1810 END IF;

1811 END IF;

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

1813 IF OLD."public" THEN

1814 IF EXISTS (

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

1816 FOR SHARE

1817 ) AND EXISTS (

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

1819 FOR SHARE

1820 ) THEN

1821 INSERT INTO "event" (

1822 "event", "member_id", "other_member_id", "boolean_value"

1823 ) VALUES (

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

1825 );

1826 END IF;

1827 END IF;

1828 END IF;

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

1830 IF NEW."public" THEN

1831 INSERT INTO "event" (

1832 "event", "member_id", "other_member_id", "boolean_value"

1833 ) VALUES (

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

1835 );

1836 END IF;

1837 END IF;

1838 RETURN NULL;

1839 END;

1840 $$;

1841

1842 CREATE TRIGGER "write_event_contact"

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

1844 "write_event_contact_trigger"();

1845

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

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

1848

1849

1850 CREATE FUNCTION "send_event_notify_trigger"()

1851 RETURNS TRIGGER

1852 LANGUAGE 'plpgsql' VOLATILE AS $$

1853 BEGIN

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

1855 RETURN NULL;

1856 END;

1857 $$;

1858

1859 CREATE TRIGGER "send_notify"

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

1861 "send_event_notify_trigger"();

1862

1863

1864 CREATE FUNCTION "delete_extended_scope_tokens_trigger"()

1865 RETURNS TRIGGER

1866 LANGUAGE 'plpgsql' VOLATILE AS $$

1867 DECLARE

1868 "system_application_row" "system_application"%ROWTYPE;

1869 BEGIN

1870 IF OLD."system_application_id" NOTNULL THEN

1871 SELECT * FROM "system_application" INTO "system_application_row"

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

1873 DELETE FROM "token"

1874 WHERE "member_id" = OLD."member_id"

1875 AND "system_application_id" = OLD."system_application_id"

1876 AND NOT COALESCE(

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

1878 regexp_split_to_array(

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

1880 ),

1881 FALSE

1882 );

1883 END IF;

1884 RETURN OLD;

1885 END;

1886 $$;

1887

1888 CREATE TRIGGER "delete_extended_scope_tokens"

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

1890 "delete_extended_scope_tokens_trigger"();

1891

1892

1893 CREATE FUNCTION "detach_token_from_session_trigger"()

1894 RETURNS TRIGGER

1895 LANGUAGE 'plpgsql' VOLATILE AS $$

1896 BEGIN

1897 UPDATE "token" SET "session_id" = NULL

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

1899 RETURN OLD;

1900 END;

1901 $$;

1902

1903 CREATE TRIGGER "detach_token_from_session"

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

1905 "detach_token_from_session_trigger"();

1906

1907

1908 CREATE FUNCTION "delete_non_detached_scope_with_session_trigger"()

1909 RETURNS TRIGGER

1910 LANGUAGE 'plpgsql' VOLATILE AS $$

1911 BEGIN

1912 IF NEW."session_id" ISNULL THEN

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

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

1915 WHERE "element" LIKE '%_detached';

1916 END IF;

1917 RETURN NEW;

1918 END;

1919 $$;

1920

1921 CREATE TRIGGER "delete_non_detached_scope_with_session"

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

1923 "delete_non_detached_scope_with_session_trigger"();

1924

1925

1926 CREATE FUNCTION "delete_token_with_empty_scope_trigger"()

1927 RETURNS TRIGGER

1928 LANGUAGE 'plpgsql' VOLATILE AS $$

1929 BEGIN

1930 IF NEW."scope" = '' THEN

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

1932 END IF;

1933 RETURN NULL;

1934 END;

1935 $$;

1936

1937 CREATE TRIGGER "delete_token_with_empty_scope"

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

1939 "delete_token_with_empty_scope_trigger"();

1940

1941

1942 CREATE FUNCTION "delete_snapshot_on_partial_delete_trigger"()

1943 RETURNS TRIGGER

1944 LANGUAGE 'plpgsql' VOLATILE AS $$

1945 BEGIN

1946 IF TG_OP = 'UPDATE' THEN

1947 IF

1948 OLD."snapshot_id" = NEW."snapshot_id" AND

1949 OLD."issue_id" = NEW."issue_id"

1950 THEN

1951 RETURN NULL;

1952 END IF;

1953 END IF;

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

1955 RETURN NULL;

1956 END;

1957 $$;

1958

1959 CREATE TRIGGER "delete_snapshot_on_partial_delete"

1960 AFTER UPDATE OR DELETE ON "snapshot_issue"

1961 FOR EACH ROW EXECUTE PROCEDURE

1962 "delete_snapshot_on_partial_delete_trigger"();

1963

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

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

1966

1967

1968 CREATE FUNCTION "copy_current_draft_data"

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

1970 RETURNS VOID

1971 LANGUAGE 'plpgsql' VOLATILE AS $$

1972 BEGIN

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

1974 FOR UPDATE;

1975 UPDATE "initiative" SET

1976 "location" = "draft"."location",

1977 "draft_text_search_data" = "draft"."text_search_data"

1978 FROM "current_draft" AS "draft"

1979 WHERE "initiative"."id" = "initiative_id_p"

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

1981 END;

1982 $$;

1983

1984 COMMENT ON FUNCTION "copy_current_draft_data"

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

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

1987

1988

1989 CREATE FUNCTION "copy_current_draft_data_trigger"()

1990 RETURNS TRIGGER

1991 LANGUAGE 'plpgsql' VOLATILE AS $$

1992 BEGIN

1993 IF TG_OP='DELETE' THEN

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

1995 ELSE

1996 IF TG_OP='UPDATE' THEN

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

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

1999 END IF;

2000 END IF;

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

2002 END IF;

2003 RETURN NULL;

2004 END;

2005 $$;

2006

2007 CREATE TRIGGER "copy_current_draft_data"

2008 AFTER INSERT OR UPDATE OR DELETE ON "draft"

2009 FOR EACH ROW EXECUTE PROCEDURE

2010 "copy_current_draft_data_trigger"();

2011

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

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

2014

2015

2016 CREATE VIEW "area_quorum" AS

2017 SELECT

2018 "area"."id" AS "area_id",

2019 ceil(

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

2021 coalesce(

2022 ( SELECT sum(

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

2024 extract(epoch from

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

2026 "issue"."discussion_time" +

2027 "issue"."verification_time" +

2028 "issue"."voting_time"

2029 )::FLOAT8

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

2031 )

2032 FROM "issue" JOIN "policy"

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

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

2035 AND "issue"."accepted" NOTNULL

2036 AND "issue"."closed" ISNULL

2037 AND "policy"."polling" = FALSE

2038 )::FLOAT8, 0::FLOAT8

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

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

2041 SELECT "snapshot"."population"

2042 FROM "snapshot"

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

2044 AND "snapshot"."issue_id" ISNULL

2045 ORDER BY "snapshot"."id" DESC

2046 LIMIT 1

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

2048

2049 )::INT4 AS "issue_quorum"

2050 FROM "area";

2051

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

2053

2054

2055 CREATE VIEW "area_with_unaccepted_issues" AS

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

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

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

2059

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

2061

2062

2063 DROP VIEW "area_member_count";

2064

2065

2066 DROP TABLE "membership";

2067

2068

2069 DROP FUNCTION "membership_weight"

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

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

2072

2073

2074 DROP FUNCTION "membership_weight_with_skipping"

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

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

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

2078

2079

2080 CREATE OR REPLACE VIEW "issue_delegation" AS

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

2082 "issue"."id" AS "issue_id",

2083 "delegation"."id",

2084 "delegation"."truster_id",

2085 "delegation"."trustee_id",

2086 "delegation"."scope"

2087 FROM "issue"

2088 JOIN "area"

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

2090 JOIN "delegation"

2091 ON "delegation"."unit_id" = "area"."unit_id"

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

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

2094 JOIN "member"

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

2096 JOIN "privilege"

2097 ON "area"."unit_id" = "privilege"."unit_id"

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

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

2100 ORDER BY

2101 "issue"."id",

2102 "delegation"."truster_id",

2103 "delegation"."scope" DESC;

2104

2105

2106 CREATE VIEW "unit_member" AS

2107 SELECT

2108 "unit"."id" AS "unit_id",

2109 "member"."id" AS "member_id"

2110 FROM "privilege"

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

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

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

2114

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

2116

2117

2118 CREATE OR REPLACE VIEW "unit_member_count" AS

2119 SELECT

2120 "unit"."id" AS "unit_id",

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

2122 FROM "unit" LEFT JOIN "unit_member"

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

2124 GROUP BY "unit"."id";

2125

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

2127

2128

2129 CREATE OR REPLACE VIEW "opening_draft" AS

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

2131 ORDER BY "initiative_id", "id";

2132

2133

2134 CREATE OR REPLACE VIEW "current_draft" AS

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

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

2137

2138

2139 CREATE OR REPLACE VIEW "issue_supporter_in_admission_state" AS

2140 SELECT

2141 "area"."unit_id",

2142 "issue"."area_id",

2143 "issue"."id" AS "issue_id",

2144 "supporter"."member_id",

2145 "direct_interest_snapshot"."weight"

2146 FROM "issue"

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

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

2149 JOIN "direct_interest_snapshot"

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

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

2152 AND "direct_interest_snapshot"."member_id" = "supporter"."member_id"

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

2154

2155

2156 CREATE OR REPLACE VIEW "individual_suggestion_ranking" AS

2157 SELECT

2158 "opinion"."initiative_id",

2159 "opinion"."member_id",

2160 "direct_interest_snapshot"."weight",

2161 CASE WHEN

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

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

2164 THEN 1 ELSE

2165 CASE WHEN

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

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

2168 THEN 2 ELSE

2169 CASE WHEN

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

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

2172 THEN 3 ELSE 4 END

2173 END

2174 END AS "preference",

2175 "opinion"."suggestion_id"

2176 FROM "opinion"

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

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

2179 JOIN "direct_interest_snapshot"

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

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

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

2183

2184

2185 CREATE VIEW "expired_session" AS

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

2187

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

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

2190

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

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

2193

2194

2195 CREATE VIEW "expired_token" AS

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

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

2198 SELECT NULL FROM "token" AS "other"

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

2200

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

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

2203

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

2205

2206

2207 CREATE VIEW "unused_snapshot" AS

2208 SELECT "snapshot".* FROM "snapshot"

2209 LEFT JOIN "issue"

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

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

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

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

2214 WHERE "issue"."id" ISNULL;

2215

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

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

2218

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

2220

2221

2222 CREATE VIEW "expired_snapshot" AS

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

2224 WHERE "unused_snapshot"."calculated" <

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

2226

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

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

2229

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

2231

2232

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

2234

2235

2236 CREATE OR REPLACE FUNCTION "delegation_chain"

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

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

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

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

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

2242 "simulate_default_p" BOOLEAN DEFAULT FALSE )

2243 RETURNS SETOF "delegation_chain_row"

2244 LANGUAGE 'plpgsql' STABLE AS $$

2245 DECLARE

2246 "scope_v" "delegation_scope";

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

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

2249 "issue_row" "issue"%ROWTYPE;

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

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

2252 "output_row" "delegation_chain_row";

2253 "output_rows" "delegation_chain_row"[];

2254 "simulate_v" BOOLEAN;

2255 "simulate_here_v" BOOLEAN;

2256 "delegation_row" "delegation"%ROWTYPE;

2257 "row_count" INT4;

2258 "i" INT4;

2259 "loop_v" BOOLEAN;

2260 BEGIN

2261 IF "simulate_trustee_id_p" NOTNULL AND "simulate_default_p" THEN

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

2263 END IF;

2264 IF "simulate_trustee_id_p" NOTNULL OR "simulate_default_p" THEN

2265 "simulate_v" := TRUE;

2266 ELSE

2267 "simulate_v" := FALSE;

2268 END IF;

2269 IF

2270 "unit_id_p" NOTNULL AND

2271 "area_id_p" ISNULL AND

2272 "issue_id_p" ISNULL

2273 THEN

2274 "scope_v" := 'unit';

2275 "unit_id_v" := "unit_id_p";

2276 ELSIF

2277 "unit_id_p" ISNULL AND

2278 "area_id_p" NOTNULL AND

2279 "issue_id_p" ISNULL

2280 THEN

2281 "scope_v" := 'area';

2282 "area_id_v" := "area_id_p";

2283 SELECT "unit_id" INTO "unit_id_v"

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

2285 ELSIF

2286 "unit_id_p" ISNULL AND

2287 "area_id_p" ISNULL AND

2288 "issue_id_p" NOTNULL

2289 THEN

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

2291 IF "issue_row"."id" ISNULL THEN

2292 RETURN;

2293 END IF;

2294 IF "issue_row"."closed" NOTNULL THEN

2295 IF "simulate_v" THEN

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

2297 END IF;

2298 FOR "output_row" IN

2299 SELECT * FROM

2300 "delegation_chain_for_closed_issue"("member_id_p", "issue_id_p")

2301 LOOP

2302 RETURN NEXT "output_row";

2303 END LOOP;

2304 RETURN;

2305 END IF;

2306 "scope_v" := 'issue';

2307 SELECT "area_id" INTO "area_id_v"

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

2309 SELECT "unit_id" INTO "unit_id_v"

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

2311 ELSE

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

2313 END IF;

2314 "visited_member_ids" := '{}';

2315 "loop_member_id_v" := NULL;

2316 "output_rows" := '{}';

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

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

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

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

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

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

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

2324 LOOP

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

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

2327 ELSE

2328 "visited_member_ids" :=

2329 "visited_member_ids" || "output_row"."member_id";

2330 END IF;

2331 IF "output_row"."participation" ISNULL THEN

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

2333 ELSIF "output_row"."participation" THEN

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

2335 END IF;

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

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

2338 SELECT NULL FROM "member" JOIN "privilege"

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

2340 AND "privilege"."unit_id" = "unit_id_v"

2341 WHERE "id" = "output_row"."member_id"

2342 AND "member"."active" AND "privilege"."voting_right"

2343 );

2344 "simulate_here_v" := (

2345 "simulate_v" AND

2346 "output_row"."member_id" = "member_id_p"

2347 );

2348 "delegation_row" := ROW(NULL);

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

2350 IF "scope_v" = 'unit' THEN

2351 IF NOT "simulate_here_v" THEN

2352 SELECT * INTO "delegation_row" FROM "delegation"

2353 WHERE "truster_id" = "output_row"."member_id"

2354 AND "unit_id" = "unit_id_v";

2355 END IF;

2356 ELSIF "scope_v" = 'area' THEN

2357 IF "simulate_here_v" THEN

2358 IF "simulate_trustee_id_p" ISNULL THEN

2359 SELECT * INTO "delegation_row" FROM "delegation"

2360 WHERE "truster_id" = "output_row"."member_id"

2361 AND "unit_id" = "unit_id_v";

2362 END IF;

2363 ELSE

2364 SELECT * INTO "delegation_row" FROM "delegation"

2365 WHERE "truster_id" = "output_row"."member_id"

2366 AND (

2367 "unit_id" = "unit_id_v" OR

2368 "area_id" = "area_id_v"

2369 )

2370 ORDER BY "scope" DESC;

2371 END IF;

2372 ELSIF "scope_v" = 'issue' THEN

2373 IF "issue_row"."fully_frozen" ISNULL THEN

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

2375 SELECT NULL FROM "interest"

2376 WHERE "issue_id" = "issue_id_p"

2377 AND "member_id" = "output_row"."member_id"

2378 );

2379 ELSE

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

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

2382 SELECT NULL FROM "direct_voter"

2383 WHERE "issue_id" = "issue_id_p"

2384 AND "member_id" = "output_row"."member_id"

2385 );

2386 ELSE

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

2388 END IF;

2389 END IF;

2390 IF "simulate_here_v" THEN

2391 IF "simulate_trustee_id_p" ISNULL THEN

2392 SELECT * INTO "delegation_row" FROM "delegation"

2393 WHERE "truster_id" = "output_row"."member_id"

2394 AND (

2395 "unit_id" = "unit_id_v" OR

2396 "area_id" = "area_id_v"

2397 )

2398 ORDER BY "scope" DESC;

2399 END IF;

2400 ELSE

2401 SELECT * INTO "delegation_row" FROM "delegation"

2402 WHERE "truster_id" = "output_row"."member_id"

2403 AND (

2404 "unit_id" = "unit_id_v" OR

2405 "area_id" = "area_id_v" OR

2406 "issue_id" = "issue_id_p"

2407 )

2408 ORDER BY "scope" DESC;

2409 END IF;

2410 END IF;

2411 ELSE

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

2413 END IF;

2414 IF "simulate_here_v" AND "simulate_trustee_id_p" NOTNULL THEN

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

2416 "output_rows" := "output_rows" || "output_row";

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

2418 ELSIF "delegation_row"."trustee_id" NOTNULL THEN

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

2420 "output_rows" := "output_rows" || "output_row";

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

2422 ELSIF "delegation_row"."scope" NOTNULL THEN

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

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

2425 "output_rows" := "output_rows" || "output_row";

2426 EXIT;

2427 ELSE

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

2429 "output_rows" := "output_rows" || "output_row";

2430 EXIT;

2431 END IF;

2432 EXIT WHEN "loop_member_id_v" NOTNULL;

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

2434 END LOOP;

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

2436 "i" := 1;

2437 "loop_v" := FALSE;

2438 LOOP

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

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

2441 IF "loop_v" THEN

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

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

2444 ELSIF "i" = "row_count" THEN

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

2446 ELSE

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

2448 END IF;

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

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

2451 "loop_v" := TRUE;

2452 END IF;

2453 IF "scope_v" = 'unit' THEN

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

2455 END IF;

2456 RETURN NEXT "output_row";

2457 "i" := "i" + 1;

2458 END LOOP;

2459 RETURN;

2460 END;

2461 $$;

2462

2463

2464 CREATE OR REPLACE FUNCTION "get_initiatives_for_notification"

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

2466 RETURNS SETOF "initiative_for_notification"

2467 LANGUAGE 'plpgsql' VOLATILE AS $$

2468 DECLARE

2469 "result_row" "initiative_for_notification"%ROWTYPE;

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

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

2472 BEGIN

2473 PERFORM "require_transaction_isolation"();

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

2475 FOR "result_row" IN

2476 SELECT * FROM "initiative_for_notification"

2477 WHERE "recipient_id" = "recipient_id_p"

2478 LOOP

2479 SELECT "id" INTO "last_draft_id_v" FROM "draft"

2480 WHERE "draft"."initiative_id" = "result_row"."initiative_id"

2481 ORDER BY "id" DESC LIMIT 1;

2482 SELECT "id" INTO "last_suggestion_id_v" FROM "suggestion"

2483 WHERE "suggestion"."initiative_id" = "result_row"."initiative_id"

2484 ORDER BY "id" DESC LIMIT 1;

2485 INSERT INTO "notification_initiative_sent"

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

2487 VALUES (

2488 "recipient_id_p",

2489 "result_row"."initiative_id",

2490 "last_draft_id_v",

2491 "last_suggestion_id_v" )

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

2493 "last_draft_id" = "last_draft_id_v",

2494 "last_suggestion_id" = "last_suggestion_id_v";

2495 RETURN NEXT "result_row";

2496 END LOOP;

2497 DELETE FROM "notification_initiative_sent"

2498 USING "initiative", "issue"

2499 WHERE "notification_initiative_sent"."member_id" = "recipient_id_p"

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

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

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

2503 UPDATE "member" SET

2504 "notification_counter" = "notification_counter" + 1,

2505 "notification_sent" = now()

2506 WHERE "id" = "recipient_id_p";

2507 RETURN;

2508 END;

2509 $$;

2510

2511

2512 CREATE OR REPLACE FUNCTION "calculate_member_counts"()

2513 RETURNS VOID

2514 LANGUAGE 'plpgsql' VOLATILE AS $$

2515 BEGIN

2516 PERFORM "require_transaction_isolation"();

2517 DELETE FROM "member_count";

2518 INSERT INTO "member_count" ("total_count")

2519 SELECT "total_count" FROM "member_count_view";

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

2521 FROM "unit_member_count" AS "view"

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

2523 RETURN;

2524 END;

2525 $$;

2526

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

2528

2529

2530 CREATE FUNCTION "calculate_area_quorum"()

2531 RETURNS VOID

2532 LANGUAGE 'plpgsql' VOLATILE AS $$

2533 BEGIN

2534 PERFORM "dont_require_transaction_isolation"();

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

2536 FROM "area_quorum" AS "view"

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

2538 RETURN;

2539 END;

2540 $$;

2541

2542 COMMENT ON FUNCTION "calculate_area_quorum"() IS 'Calculate column "issue_quorum" in table "area" from view "area_quorum"';

2543

2544

2545 DROP VIEW "remaining_harmonic_initiative_weight_summands";

2546 DROP VIEW "remaining_harmonic_supporter_weight";

2547

2548

2549 CREATE VIEW "remaining_harmonic_supporter_weight" AS

2550 SELECT

2551 "direct_interest_snapshot"."snapshot_id",

2552 "direct_interest_snapshot"."issue_id",

2553 "direct_interest_snapshot"."member_id",

2554 "direct_interest_snapshot"."weight" AS "weight_num",

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

2556 FROM "issue"

2557 JOIN "direct_interest_snapshot"

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

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

2560 JOIN "initiative"

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

2562 AND "initiative"."harmonic_weight" ISNULL

2563 JOIN "direct_supporter_snapshot"

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

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

2566 AND "direct_interest_snapshot"."member_id" = "direct_supporter_snapshot"."member_id"

2567 AND (

2568 "direct_supporter_snapshot"."satisfied" = TRUE OR

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

2570 )

2571 GROUP BY

2572 "direct_interest_snapshot"."snapshot_id",

2573 "direct_interest_snapshot"."issue_id",

2574 "direct_interest_snapshot"."member_id",

2575 "direct_interest_snapshot"."weight";

2576

2577

2578 CREATE VIEW "remaining_harmonic_initiative_weight_summands" AS

2579 SELECT

2580 "initiative"."issue_id",

2581 "initiative"."id" AS "initiative_id",

2582 "initiative"."admitted",

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

2584 "remaining_harmonic_supporter_weight"."weight_den"

2585 FROM "remaining_harmonic_supporter_weight"

2586 JOIN "initiative"

2587 ON "remaining_harmonic_supporter_weight"."issue_id" = "initiative"."issue_id"

2588 AND "initiative"."harmonic_weight" ISNULL

2589 JOIN "direct_supporter_snapshot"

2590 ON "remaining_harmonic_supporter_weight"."snapshot_id" = "direct_supporter_snapshot"."snapshot_id"

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

2592 AND "remaining_harmonic_supporter_weight"."member_id" = "direct_supporter_snapshot"."member_id"

2593 AND (

2594 "direct_supporter_snapshot"."satisfied" = TRUE OR

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

2596 )

2597 GROUP BY

2598 "initiative"."issue_id",

2599 "initiative"."id",

2600 "initiative"."admitted",

2601 "remaining_harmonic_supporter_weight"."weight_den";

2602

2603

2604 DROP FUNCTION "create_population_snapshot"

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

2606

2607

2608 DROP FUNCTION "weight_of_added_delegations_for_population_snapshot"

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

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

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

2612

2613

2614 DROP FUNCTION "weight_of_added_delegations_for_interest_snapshot"

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

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

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

2618

2619

2620 CREATE FUNCTION "weight_of_added_delegations_for_snapshot"

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

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

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

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

2625 RETURNS "direct_interest_snapshot"."weight"%TYPE

2626 LANGUAGE 'plpgsql' VOLATILE AS $$

2627 DECLARE

2628 "issue_delegation_row" "issue_delegation"%ROWTYPE;

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

2630 "weight_v" INT4;

2631 "sub_weight_v" INT4;

2632 BEGIN

2633 PERFORM "require_transaction_isolation"();

2634 "weight_v" := 0;

2635 FOR "issue_delegation_row" IN

2636 SELECT * FROM "issue_delegation"

2637 WHERE "trustee_id" = "member_id_p"

2638 AND "issue_id" = "issue_id_p"

2639 LOOP

2640 IF NOT EXISTS (

2641 SELECT NULL FROM "direct_interest_snapshot"

2642 WHERE "snapshot_id" = "snapshot_id_p"

2643 AND "issue_id" = "issue_id_p"

2644 AND "member_id" = "issue_delegation_row"."truster_id"

2645 ) AND NOT EXISTS (

2646 SELECT NULL FROM "delegating_interest_snapshot"

2647 WHERE "snapshot_id" = "snapshot_id_p"

2648 AND "issue_id" = "issue_id_p"

2649 AND "member_id" = "issue_delegation_row"."truster_id"

2650 ) THEN

2651 "delegate_member_ids_v" :=

2652 "member_id_p" || "delegate_member_ids_p";

2653 INSERT INTO "delegating_interest_snapshot" (

2654 "snapshot_id",

2655 "issue_id",

2656 "member_id",

2657 "scope",

2658 "delegate_member_ids"

2659 ) VALUES (

2660 "snapshot_id_p",

2661 "issue_id_p",

2662 "issue_delegation_row"."truster_id",

2663 "issue_delegation_row"."scope",

2664 "delegate_member_ids_v"

2665 );

2666 "sub_weight_v" := 1 +

2667 "weight_of_added_delegations_for_snapshot"(

2668 "snapshot_id_p",

2669 "issue_id_p",

2670 "issue_delegation_row"."truster_id",

2671 "delegate_member_ids_v"

2672 );

2673 UPDATE "delegating_interest_snapshot"

2674 SET "weight" = "sub_weight_v"

2675 WHERE "snapshot_id" = "snapshot_id_p"

2676 AND "issue_id" = "issue_id_p"

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

2678 "weight_v" := "weight_v" + "sub_weight_v";

2679 END IF;

2680 END LOOP;

2681 RETURN "weight_v";

2682 END;

2683 $$;

2684

2685 COMMENT ON FUNCTION "weight_of_added_delegations_for_snapshot"

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

2687 "issue"."id"%TYPE,

2688 "member"."id"%TYPE,

2689 "delegating_interest_snapshot"."delegate_member_ids"%TYPE )

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

2691

2692

2693 DROP FUNCTION "create_interest_snapshot"

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

2695

2696

2697 DROP FUNCTION "create_snapshot"

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

2699

2700

2701 CREATE FUNCTION "take_snapshot"

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

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

2704 RETURNS "snapshot"."id"%TYPE

2705 LANGUAGE 'plpgsql' VOLATILE AS $$

2706 DECLARE

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

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

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

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

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

2712 BEGIN

2713 IF "issue_id_p" NOTNULL AND "area_id_p" NOTNULL THEN

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

2715 END IF;

2716 PERFORM "require_transaction_isolation"();

2717 IF "issue_id_p" ISNULL THEN

2718 "area_id_v" := "area_id_p";

2719 ELSE

2720 SELECT "area_id" INTO "area_id_v"

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

2722 END IF;

2723 SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_p";

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

2725 VALUES ("area_id_v", "issue_id_p")

2726 RETURNING "id" INTO "snapshot_id_v";

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

2728 SELECT "snapshot_id_v", "member_id"

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

2730 UPDATE "snapshot" SET

2731 "population" = (

2732 SELECT count(1) FROM "snapshot_population"

2733 WHERE "snapshot_id" = "snapshot_id_v"

2734 ) WHERE "id" = "snapshot_id_v";

2735 FOR "issue_id_v" IN

2736 SELECT "id" FROM "issue"

2737 WHERE CASE WHEN "issue_id_p" ISNULL THEN

2738 "area_id" = "area_id_p" AND

2739 "state" = 'admission'

2740 ELSE

2741 "id" = "issue_id_p"

2742 END

2743 LOOP

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

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

2746 INSERT INTO "direct_interest_snapshot"

2747 ("snapshot_id", "issue_id", "member_id")

2748 SELECT

2749 "snapshot_id_v" AS "snapshot_id",

2750 "issue_id_v" AS "issue_id",

2751 "member"."id" AS "member_id"

2752 FROM "issue"

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

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

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

2756 JOIN "privilege"

2757 ON "privilege"."unit_id" = "area"."unit_id"

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

2759 WHERE "issue"."id" = "issue_id_v"

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

2761 FOR "member_id_v" IN

2762 SELECT "member_id" FROM "direct_interest_snapshot"

2763 WHERE "snapshot_id" = "snapshot_id_v"

2764 AND "issue_id" = "issue_id_v"

2765 LOOP

2766 UPDATE "direct_interest_snapshot" SET

2767 "weight" = 1 +

2768 "weight_of_added_delegations_for_snapshot"(

2769 "snapshot_id_v",

2770 "issue_id_v",

2771 "member_id_v",

2772 '{}'

2773 )

2774 WHERE "snapshot_id" = "snapshot_id_v"

2775 AND "issue_id" = "issue_id_v"

2776 AND "member_id" = "member_id_v";

2777 END LOOP;

2778 INSERT INTO "direct_supporter_snapshot"

2779 ( "snapshot_id", "issue_id", "initiative_id", "member_id",

2780 "draft_id", "informed", "satisfied" )

2781 SELECT

2782 "snapshot_id_v" AS "snapshot_id",

2783 "issue_id_v" AS "issue_id",

2784 "initiative"."id" AS "initiative_id",

2785 "supporter"."member_id" AS "member_id",

2786 "supporter"."draft_id" AS "draft_id",

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

2788 NOT EXISTS (

2789 SELECT NULL FROM "critical_opinion"

2790 WHERE "initiative_id" = "initiative"."id"

2791 AND "member_id" = "supporter"."member_id"

2792 ) AS "satisfied"

2793 FROM "initiative"

2794 JOIN "supporter"

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

2796 JOIN "current_draft"

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

2798 JOIN "direct_interest_snapshot"

2799 ON "snapshot_id_v" = "direct_interest_snapshot"."snapshot_id"

2800 AND "supporter"."member_id" = "direct_interest_snapshot"."member_id"

2801 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"

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

2803 DELETE FROM "temporary_suggestion_counts";

2804 INSERT INTO "temporary_suggestion_counts"

2805 ( "id",

2806 "minus2_unfulfilled_count", "minus2_fulfilled_count",

2807 "minus1_unfulfilled_count", "minus1_fulfilled_count",

2808 "plus1_unfulfilled_count", "plus1_fulfilled_count",

2809 "plus2_unfulfilled_count", "plus2_fulfilled_count" )

2810 SELECT

2811 "suggestion"."id",

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

2813 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"

2814 ON "di"."snapshot_id" = "snapshot_id_v"

2815 AND "di"."issue_id" = "issue_id_v"

2816 AND "di"."member_id" = "opinion"."member_id"

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

2818 AND "opinion"."degree" = -2

2819 AND "opinion"."fulfilled" = FALSE

2820 ) AS "minus2_unfulfilled_count",

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

2822 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"

2823 ON "di"."snapshot_id" = "snapshot_id_v"

2824 AND "di"."issue_id" = "issue_id_v"

2825 AND "di"."member_id" = "opinion"."member_id"

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

2827 AND "opinion"."degree" = -2

2828 AND "opinion"."fulfilled" = TRUE

2829 ) AS "minus2_fulfilled_count",

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

2831 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"

2832 ON "di"."snapshot_id" = "snapshot_id_v"

2833 AND "di"."issue_id" = "issue_id_v"

2834 AND "di"."member_id" = "opinion"."member_id"

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

2836 AND "opinion"."degree" = -1

2837 AND "opinion"."fulfilled" = FALSE

2838 ) AS "minus1_unfulfilled_count",

2839 ( SELECT coalesce(sum("di"."weight"), 0)

2840 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"

2841 ON "di"."snapshot_id" = "snapshot_id_v"

2842 AND "di"."issue_id" = "issue_id_v"

2843 AND "di"."member_id" = "opinion"."member_id"

2844 WHERE "opinion"."suggestion_id" = "suggestion"."id"

2845 AND "opinion"."degree" = -1

2846 AND "opinion"."fulfilled" = TRUE

2847 ) AS "minus1_fulfilled_count",

2848 ( SELECT coalesce(sum("di"."weight"), 0)

2849 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"

2850 ON "di"."snapshot_id" = "snapshot_id_v"

2851 AND "di"."issue_id" = "issue_id_v"

2852 AND "di"."member_id" = "opinion"."member_id"

2853 WHERE "opinion"."suggestion_id" = "suggestion"."id"

2854 AND "opinion"."degree" = 1

2855 AND "opinion"."fulfilled" = FALSE

2856 ) AS "plus1_unfulfilled_count",

2857 ( SELECT coalesce(sum("di"."weight"), 0)

2858 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"

2859 ON "di"."snapshot_id" = "snapshot_id_v"

2860 AND "di"."issue_id" = "issue_id_v"

2861 AND "di"."member_id" = "opinion"."member_id"

2862 WHERE "opinion"."suggestion_id" = "suggestion"."id"

2863 AND "opinion"."degree" = 1

2864 AND "opinion"."fulfilled" = TRUE

2865 ) AS "plus1_fulfilled_count",

2866 ( SELECT coalesce(sum("di"."weight"), 0)

2867 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"

2868 ON "di"."snapshot_id" = "snapshot_id_v"

2869 AND "di"."issue_id" = "issue_id_v"

2870 AND "di"."member_id" = "opinion"."member_id"

2871 WHERE "opinion"."suggestion_id" = "suggestion"."id"

2872 AND "opinion"."degree" = 2

2873 AND "opinion"."fulfilled" = FALSE

2874 ) AS "plus2_unfulfilled_count",

2875 ( SELECT coalesce(sum("di"."weight"), 0)

2876 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"

2877 ON "di"."snapshot_id" = "snapshot_id_v"

2878 AND "di"."issue_id" = "issue_id_v"

2879 AND "di"."member_id" = "opinion"."member_id"

2880 WHERE "opinion"."suggestion_id" = "suggestion"."id"

2881 AND "opinion"."degree" = 2

2882 AND "opinion"."fulfilled" = TRUE

2883 ) AS "plus2_fulfilled_count"

2884 FROM "suggestion" JOIN "initiative"

2885 ON "suggestion"."initiative_id" = "initiative"."id"

2886 WHERE "initiative"."issue_id" = "issue_id_v";

2887 END LOOP;

2888 RETURN "snapshot_id_v";

2889 END;

2890 $$;

2891

2892 COMMENT ON FUNCTION "take_snapshot"

2893 ( "issue"."id"%TYPE,

2894 "area"."id"%TYPE )

2895 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.';

2896

2897

2898 DROP FUNCTION "set_snapshot_event"

2899 ( "issue_id_p" "issue"."id"%TYPE,

2900 "event_p" "snapshot_event" );

2901

2902

2903 CREATE FUNCTION "finish_snapshot"

2904 ( "issue_id_p" "issue"."id"%TYPE )

2905 RETURNS VOID

2906 LANGUAGE 'plpgsql' VOLATILE AS $$

2907 DECLARE

2908 "snapshot_id_v" "snapshot"."id"%TYPE;

2909 BEGIN

2910 -- NOTE: function does not require snapshot isolation but we don't call

2911 -- "dont_require_snapshot_isolation" here because this function is

2912 -- also invoked by "check_issue"

2913 LOCK TABLE "snapshot" IN EXCLUSIVE MODE;

2914 SELECT "id" INTO "snapshot_id_v" FROM "snapshot"

2915 ORDER BY "id" DESC LIMIT 1;

2916 UPDATE "issue" SET

2917 "calculated" = "snapshot"."calculated",

2918 "latest_snapshot_id" = "snapshot_id_v",

2919 "population" = "snapshot"."population"

2920 FROM "snapshot"

2921 WHERE "issue"."id" = "issue_id_p"

2922 AND "snapshot"."id" = "snapshot_id_v";

2923 UPDATE "initiative" SET

2924 "supporter_count" = (

2925 SELECT coalesce(sum("di"."weight"), 0)

2926 FROM "direct_interest_snapshot" AS "di"

2927 JOIN "direct_supporter_snapshot" AS "ds"

2928 ON "di"."member_id" = "ds"."member_id"

2929 WHERE "di"."snapshot_id" = "snapshot_id_v"

2930 AND "di"."issue_id" = "issue_id_p"

2931 AND "ds"."snapshot_id" = "snapshot_id_v"

2932 AND "ds"."initiative_id" = "initiative"."id"

2933 ),

2934 "informed_supporter_count" = (

2935 SELECT coalesce(sum("di"."weight"), 0)

2936 FROM "direct_interest_snapshot" AS "di"

2937 JOIN "direct_supporter_snapshot" AS "ds"

2938 ON "di"."member_id" = "ds"."member_id"

2939 WHERE "di"."snapshot_id" = "snapshot_id_v"

2940 AND "di"."issue_id" = "issue_id_p"

2941 AND "ds"."snapshot_id" = "snapshot_id_v"

2942 AND "ds"."initiative_id" = "initiative"."id"

2943 AND "ds"."informed"

2944 ),

2945 "satisfied_supporter_count" = (

2946 SELECT coalesce(sum("di"."weight"), 0)

2947 FROM "direct_interest_snapshot" AS "di"

2948 JOIN "direct_supporter_snapshot" AS "ds"

2949 ON "di"."member_id" = "ds"."member_id"

2950 WHERE "di"."snapshot_id" = "snapshot_id_v"

2951 AND "di"."issue_id" = "issue_id_p"

2952 AND "ds"."snapshot_id" = "snapshot_id_v"

2953 AND "ds"."initiative_id" = "initiative"."id"

2954 AND "ds"."satisfied"

2955 ),

2956 "satisfied_informed_supporter_count" = (

2957 SELECT coalesce(sum("di"."weight"), 0)

2958 FROM "direct_interest_snapshot" AS "di"

2959 JOIN "direct_supporter_snapshot" AS "ds"

2960 ON "di"."member_id" = "ds"."member_id"

2961 WHERE "di"."snapshot_id" = "snapshot_id_v"

2962 AND "di"."issue_id" = "issue_id_p"

2963 AND "ds"."snapshot_id" = "snapshot_id_v"

2964 AND "ds"."initiative_id" = "initiative"."id"

2965 AND "ds"."informed"

2966 AND "ds"."satisfied"

2967 )

2968 WHERE "issue_id" = "issue_id_p";

2969 UPDATE "suggestion" SET

2970 "minus2_unfulfilled_count" = "temp"."minus2_unfulfilled_count",

2971 "minus2_fulfilled_count" = "temp"."minus2_fulfilled_count",

2972 "minus1_unfulfilled_count" = "temp"."minus1_unfulfilled_count",

2973 "minus1_fulfilled_count" = "temp"."minus1_fulfilled_count",

2974 "plus1_unfulfilled_count" = "temp"."plus1_unfulfilled_count",

2975 "plus1_fulfilled_count" = "temp"."plus1_fulfilled_count",

2976 "plus2_unfulfilled_count" = "temp"."plus2_unfulfilled_count",

2977 "plus2_fulfilled_count" = "temp"."plus2_fulfilled_count"

2978 FROM "temporary_suggestion_counts" AS "temp", "initiative"

2979 WHERE "temp"."id" = "suggestion"."id"

2980 AND "initiative"."issue_id" = "issue_id_p"

2981 AND "suggestion"."initiative_id" = "initiative"."id";

2982 DELETE FROM "temporary_suggestion_counts";

2983 RETURN;

2984 END;

2985 $$;

2986

2987 COMMENT ON FUNCTION "finish_snapshot"

2988 ( "issue"."id"%TYPE )

2989 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)';

2990

2991

2992 CREATE FUNCTION "issue_admission"

2993 ( "area_id_p" "area"."id"%TYPE )

2994 RETURNS BOOLEAN

2995 LANGUAGE 'plpgsql' VOLATILE AS $$

2996 DECLARE

2997 "issue_id_v" "issue"."id"%TYPE;

2998 BEGIN

2999 PERFORM "dont_require_transaction_isolation"();

3000 LOCK TABLE "snapshot" IN EXCLUSIVE MODE;

3001 UPDATE "area" SET "issue_quorum" = "view"."issue_quorum"

3002 FROM "area_quorum" AS "view"

3003 WHERE "area"."id" = "view"."area_id"

3004 AND "area"."id" = "area_id_p";

3005 SELECT "id" INTO "issue_id_v" FROM "issue_for_admission"

3006 WHERE "area_id" = "area_id_p";

3007 IF "issue_id_v" ISNULL THEN RETURN FALSE; END IF;

3008 UPDATE "issue" SET

3009 "admission_snapshot_id" = "latest_snapshot_id",

3010 "state" = 'discussion',

3011 "accepted" = now(),

3012 "phase_finished" = NULL

3013 WHERE "id" = "issue_id_v";

3014 RETURN TRUE;

3015 END;

3016 $$;

3017

3018 COMMENT ON FUNCTION "issue_admission"

3019 ( "area"."id"%TYPE )

3020 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';

3021

3022

3023 CREATE OR REPLACE FUNCTION "check_issue"

3024 ( "issue_id_p" "issue"."id"%TYPE,

3025 "persist" "check_issue_persistence" )

3026 RETURNS "check_issue_persistence"

3027 LANGUAGE 'plpgsql' VOLATILE AS $$

3028 DECLARE

3029 "issue_row" "issue"%ROWTYPE;

3030 "last_calculated_v" "snapshot"."calculated"%TYPE;

3031 "policy_row" "policy"%ROWTYPE;

3032 "initiative_row" "initiative"%ROWTYPE;

3033 "state_v" "issue_state";

3034 BEGIN

3035 PERFORM "require_transaction_isolation"();

3036 IF "persist" ISNULL THEN

3037 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"

3038 FOR UPDATE;

3039 SELECT "calculated" INTO "last_calculated_v"

3040 FROM "snapshot" JOIN "snapshot_issue"

3041 ON "snapshot"."id" = "snapshot_issue"."snapshot_id"

3042 WHERE "snapshot_issue"."issue_id" = "issue_id_p";

3043 IF "issue_row"."closed" NOTNULL THEN

3044 RETURN NULL;

3045 END IF;

3046 "persist"."state" := "issue_row"."state";

3047 IF

3048 ( "issue_row"."state" = 'admission' AND "last_calculated_v" >=

3049 "issue_row"."created" + "issue_row"."max_admission_time" ) OR

3050 ( "issue_row"."state" = 'discussion' AND now() >=

3051 "issue_row"."accepted" + "issue_row"."discussion_time" ) OR

3052 ( "issue_row"."state" = 'verification' AND now() >=

3053 "issue_row"."half_frozen" + "issue_row"."verification_time" ) OR

3054 ( "issue_row"."state" = 'voting' AND now() >=

3055 "issue_row"."fully_frozen" + "issue_row"."voting_time" )

3056 THEN

3057 "persist"."phase_finished" := TRUE;

3058 ELSE

3059 "persist"."phase_finished" := FALSE;

3060 END IF;

3061 IF

3062 NOT EXISTS (

3063 -- all initiatives are revoked

3064 SELECT NULL FROM "initiative"

3065 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL

3066 ) AND (

3067 -- and issue has not been accepted yet

3068 "persist"."state" = 'admission' OR

3069 -- or verification time has elapsed

3070 ( "persist"."state" = 'verification' AND

3071 "persist"."phase_finished" ) OR

3072 -- or no initiatives have been revoked lately

3073 NOT EXISTS (

3074 SELECT NULL FROM "initiative"

3075 WHERE "issue_id" = "issue_id_p"

3076 AND now() < "revoked" + "issue_row"."verification_time"

3077 )

3078 )

3079 THEN

3080 "persist"."issue_revoked" := TRUE;

3081 ELSE

3082 "persist"."issue_revoked" := FALSE;

3083 END IF;

3084 IF "persist"."phase_finished" OR "persist"."issue_revoked" THEN

3085 UPDATE "issue" SET "phase_finished" = now()

3086 WHERE "id" = "issue_row"."id";

3087 RETURN "persist";

3088 ELSIF

3089 "persist"."state" IN ('admission', 'discussion', 'verification')

3090 THEN

3091 RETURN "persist";

3092 ELSE

3093 RETURN NULL;

3094 END IF;

3095 END IF;

3096 IF

3097 "persist"."state" IN ('admission', 'discussion', 'verification') AND

3098 coalesce("persist"."snapshot_created", FALSE) = FALSE

3099 THEN

3100 IF "persist"."state" != 'admission' THEN

3101 PERFORM "take_snapshot"("issue_id_p");

3102 PERFORM "finish_snapshot"("issue_id_p");

3103 END IF;

3104 "persist"."snapshot_created" = TRUE;

3105 IF "persist"."phase_finished" THEN

3106 IF "persist"."state" = 'admission' THEN

3107 UPDATE "issue" SET "admission_snapshot_id" = "latest_snapshot_id"

3108 WHERE "id" = "issue_id_p";

3109 ELSIF "persist"."state" = 'discussion' THEN

3110 UPDATE "issue" SET "half_freeze_snapshot_id" = "latest_snapshot_id"

3111 WHERE "id" = "issue_id_p";

3112 ELSIF "persist"."state" = 'verification' THEN

3113 UPDATE "issue" SET "full_freeze_snapshot_id" = "latest_snapshot_id"

3114 WHERE "id" = "issue_id_p";

3115 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";

3116 SELECT * INTO "policy_row" FROM "policy"

3117 WHERE "id" = "issue_row"."policy_id";

3118 FOR "initiative_row" IN

3119 SELECT * FROM "initiative"

3120 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL

3121 FOR UPDATE

3122 LOOP

3123 IF

3124 "initiative_row"."polling" OR (

3125 "initiative_row"."satisfied_supporter_count" >

3126 "policy_row"."initiative_quorum" AND

3127 "initiative_row"."satisfied_supporter_count" *

3128 "policy_row"."initiative_quorum_den" >=

3129 "issue_row"."population" * "policy_row"."initiative_quorum_num"

3130 )

3131 THEN

3132 UPDATE "initiative" SET "admitted" = TRUE

3133 WHERE "id" = "initiative_row"."id";

3134 ELSE

3135 UPDATE "initiative" SET "admitted" = FALSE

3136 WHERE "id" = "initiative_row"."id";

3137 END IF;

3138 END LOOP;

3139 END IF;

3140 END IF;

3141 RETURN "persist";

3142 END IF;

3143 IF

3144 "persist"."state" IN ('admission', 'discussion', 'verification') AND

3145 coalesce("persist"."harmonic_weights_set", FALSE) = FALSE

3146 THEN

3147 PERFORM "set_harmonic_initiative_weights"("issue_id_p");

3148 "persist"."harmonic_weights_set" = TRUE;

3149 IF

3150 "persist"."phase_finished" OR

3151 "persist"."issue_revoked" OR

3152 "persist"."state" = 'admission'

3153 THEN

3154 RETURN "persist";

3155 ELSE

3156 RETURN NULL;

3157 END IF;

3158 END IF;

3159 IF "persist"."issue_revoked" THEN

3160 IF "persist"."state" = 'admission' THEN

3161 "state_v" := 'canceled_revoked_before_accepted';

3162 ELSIF "persist"."state" = 'discussion' THEN

3163 "state_v" := 'canceled_after_revocation_during_discussion';

3164 ELSIF "persist"."state" = 'verification' THEN

3165 "state_v" := 'canceled_after_revocation_during_verification';

3166 END IF;

3167 UPDATE "issue" SET

3168 "state" = "state_v",

3169 "closed" = "phase_finished",

3170 "phase_finished" = NULL

3171 WHERE "id" = "issue_id_p";

3172 RETURN NULL;

3173 END IF;

3174 IF "persist"."state" = 'admission' THEN

3175 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"

3176 FOR UPDATE;

3177 IF "issue_row"."phase_finished" NOTNULL THEN

3178 UPDATE "issue" SET

3179 "state" = 'canceled_issue_not_accepted',

3180 "closed" = "phase_finished",

3181 "phase_finished" = NULL

3182 WHERE "id" = "issue_id_p";

3183 END IF;

3184 RETURN NULL;

3185 END IF;

3186 IF "persist"."phase_finished" THEN

3187 IF "persist"."state" = 'discussion' THEN

3188 UPDATE "issue" SET

3189 "state" = 'verification',

3190 "half_frozen" = "phase_finished",

3191 "phase_finished" = NULL

3192 WHERE "id" = "issue_id_p";

3193 RETURN NULL;

3194 END IF;

3195 IF "persist"."state" = 'verification' THEN

3196 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"

3197 FOR UPDATE;

3198 SELECT * INTO "policy_row" FROM "policy"

3199 WHERE "id" = "issue_row"."policy_id";

3200 IF EXISTS (

3201 SELECT NULL FROM "initiative"

3202 WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE

3203 ) THEN

3204 UPDATE "issue" SET

3205 "state" = 'voting',

3206 "fully_frozen" = "phase_finished",

3207 "phase_finished" = NULL

3208 WHERE "id" = "issue_id_p";

3209 ELSE

3210 UPDATE "issue" SET

3211 "state" = 'canceled_no_initiative_admitted',

3212 "fully_frozen" = "phase_finished",

3213 "closed" = "phase_finished",

3214 "phase_finished" = NULL

3215 WHERE "id" = "issue_id_p";

3216 -- NOTE: The following DELETE statements have effect only when

3217 -- issue state has been manipulated

3218 DELETE FROM "direct_voter" WHERE "issue_id" = "issue_id_p";

3219 DELETE FROM "delegating_voter" WHERE "issue_id" = "issue_id_p";

3220 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";

3221 END IF;

3222 RETURN NULL;

3223 END IF;

3224 IF "persist"."state" = 'voting' THEN

3225 IF coalesce("persist"."closed_voting", FALSE) = FALSE THEN

3226 PERFORM "close_voting"("issue_id_p");

3227 "persist"."closed_voting" = TRUE;

3228 RETURN "persist";

3229 END IF;

3230 PERFORM "calculate_ranks"("issue_id_p");

3231 RETURN NULL;

3232 END IF;

3233 END IF;

3234 RAISE WARNING 'should not happen';

3235 RETURN NULL;

3236 END;

3237 $$;

3238

3239

3240 CREATE OR REPLACE FUNCTION "check_everything"()

3241 RETURNS VOID

3242 LANGUAGE 'plpgsql' VOLATILE AS $$

3243 DECLARE

3244 "area_id_v" "area"."id"%TYPE;

3245 "snapshot_id_v" "snapshot"."id"%TYPE;

3246 "issue_id_v" "issue"."id"%TYPE;

3247 "persist_v" "check_issue_persistence";

3248 BEGIN

3249 RAISE WARNING 'Function "check_everything" should only be used for development and debugging purposes';

3250 DELETE FROM "expired_session";

3251 DELETE FROM "expired_token";

3252 DELETE FROM "expired_snapshot";

3253 PERFORM "check_activity"();

3254 PERFORM "calculate_member_counts"();

3255 FOR "area_id_v" IN SELECT "id" FROM "area_with_unaccepted_issues" LOOP

3256 SELECT "take_snapshot"(NULL, "area_id_v") INTO "snapshot_id_v";

3257 PERFORM "finish_snapshot"("issue_id") FROM "snapshot_issue"

3258 WHERE "snapshot_id" = "snapshot_id_v";

3259 LOOP

3260 EXIT WHEN "issue_admission"("area_id_v") = FALSE;

3261 END LOOP;

3262 END LOOP;

3263 FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP

3264 "persist_v" := NULL;

3265 LOOP

3266 "persist_v" := "check_issue"("issue_id_v", "persist_v");

3267 EXIT WHEN "persist_v" ISNULL;

3268 END LOOP;

3269 END LOOP;

3270 RETURN;

3271 END;

3272 $$;

3273

3274 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';

3275

3276

3277 CREATE OR REPLACE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE)

3278 RETURNS VOID

3279 LANGUAGE 'plpgsql' VOLATILE AS $$

3280 BEGIN

3281 IF EXISTS (

3282 SELECT NULL FROM "issue" WHERE "id" = "issue_id_p" AND "cleaned" ISNULL

3283 ) THEN

3284 -- override protection triggers:

3285 INSERT INTO "temporary_transaction_data" ("key", "value")

3286 VALUES ('override_protection_triggers', TRUE::TEXT);

3287 -- clean data:

3288 DELETE FROM "delegating_voter"

3289 WHERE "issue_id" = "issue_id_p";

3290 DELETE FROM "direct_voter"

3291 WHERE "issue_id" = "issue_id_p";

3292 DELETE FROM "delegating_interest_snapshot"

3293 WHERE "issue_id" = "issue_id_p";

3294 DELETE FROM "direct_interest_snapshot"

3295 WHERE "issue_id" = "issue_id_p";

3296 DELETE FROM "non_voter"

3297 WHERE "issue_id" = "issue_id_p";

3298 DELETE FROM "delegation"

3299 WHERE "issue_id" = "issue_id_p";

3300 DELETE FROM "supporter"

3301 USING "initiative" -- NOTE: due to missing index on issue_id

3302 WHERE "initiative"."issue_id" = "issue_id_p"

3303 AND "supporter"."initiative_id" = "initiative_id";

3304 -- mark issue as cleaned:

3305 UPDATE "issue" SET "cleaned" = now() WHERE "id" = "issue_id_p";

3306 -- finish overriding protection triggers (avoids garbage):

3307 DELETE FROM "temporary_transaction_data"

3308 WHERE "key" = 'override_protection_triggers';

3309 END IF;

3310 RETURN;

3311 END;

3312 $$;

3313

3314

3315 CREATE OR REPLACE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)

3316 RETURNS VOID

3317 LANGUAGE 'plpgsql' VOLATILE AS $$

3318 BEGIN

3319 UPDATE "member" SET

3320 "last_login" = NULL,

3321 "last_delegation_check" = NULL,

3322 "login" = NULL,

3323 "password" = NULL,

3324 "authority" = NULL,

3325 "authority_uid" = NULL,

3326 "authority_login" = NULL,

3327 "deleted" = coalesce("deleted", now()),

3328 "locked" = TRUE,

3329 "active" = FALSE,

3330 "notify_email" = NULL,

3331 "notify_email_unconfirmed" = NULL,

3332 "notify_email_secret" = NULL,

3333 "notify_email_secret_expiry" = NULL,

3334 "notify_email_lock_expiry" = NULL,

3335 "disable_notifications" = TRUE,

3336 "notification_counter" = DEFAULT,

3337 "notification_sample_size" = 0,

3338 "notification_dow" = NULL,

3339 "notification_hour" = NULL,

3340 "notification_sent" = NULL,

3341 "login_recovery_expiry" = NULL,

3342 "password_reset_secret" = NULL,

3343 "password_reset_secret_expiry" = NULL,

3344 "location" = NULL

3345 WHERE "id" = "member_id_p";

3346 -- "text_search_data" is updated by triggers

3347 DELETE FROM "member_settings" WHERE "member_id" = "member_id_p";

3348 DELETE FROM "member_profile" WHERE "member_id" = "member_id_p";

3349 DELETE FROM "rendered_member_statement" WHERE "member_id" = "member_id_p";

3350 DELETE FROM "member_image" WHERE "member_id" = "member_id_p";

3351 DELETE FROM "contact" WHERE "member_id" = "member_id_p";

3352 DELETE FROM "ignored_member" WHERE "member_id" = "member_id_p";

3353 DELETE FROM "session" WHERE "member_id" = "member_id_p";

3354 DELETE FROM "member_application" WHERE "member_id" = "member_id_p";

3355 DELETE FROM "token" WHERE "member_id" = "member_id_p";

3356 DELETE FROM "subscription" WHERE "member_id" = "member_id_p";

3357 DELETE FROM "ignored_area" WHERE "member_id" = "member_id_p";

3358 DELETE FROM "ignored_initiative" WHERE "member_id" = "member_id_p";

3359 DELETE FROM "delegation" WHERE "truster_id" = "member_id_p";

3360 DELETE FROM "non_voter" WHERE "member_id" = "member_id_p";

3361 DELETE FROM "direct_voter" USING "issue"

3362 WHERE "direct_voter"."issue_id" = "issue"."id"

3363 AND "issue"."closed" ISNULL

3364 AND "member_id" = "member_id_p";

3365 DELETE FROM "notification_initiative_sent" WHERE "member_id" = "member_id_p";

3366 RETURN;

3367 END;

3368 $$;

3369

3370

3371 CREATE OR REPLACE FUNCTION "delete_private_data"()

3372 RETURNS VOID

3373 LANGUAGE 'plpgsql' VOLATILE AS $$

3374 BEGIN

3375 DELETE FROM "temporary_transaction_data";

3376 DELETE FROM "temporary_suggestion_counts";

3377 DELETE FROM "member" WHERE "activated" ISNULL;

3378 UPDATE "member" SET

3379 "invite_code" = NULL,

3380 "invite_code_expiry" = NULL,

3381 "admin_comment" = NULL,

3382 "last_login" = NULL,

3383 "last_delegation_check" = NULL,

3384 "login" = NULL,

3385 "password" = NULL,

3386 "authority" = NULL,

3387 "authority_uid" = NULL,

3388 "authority_login" = NULL,

3389 "lang" = NULL,

3390 "notify_email" = NULL,

3391 "notify_email_unconfirmed" = NULL,

3392 "notify_email_secret" = NULL,

3393 "notify_email_secret_expiry" = NULL,

3394 "notify_email_lock_expiry" = NULL,

3395 "disable_notifications" = TRUE,

3396 "notification_counter" = DEFAULT,

3397 "notification_sample_size" = 0,

3398 "notification_dow" = NULL,

3399 "notification_hour" = NULL,

3400 "notification_sent" = NULL,

3401 "login_recovery_expiry" = NULL,

3402 "password_reset_secret" = NULL,

3403 "password_reset_secret_expiry" = NULL,

3404 "location" = NULL;

3405 -- "text_search_data" is updated by triggers

3406 DELETE FROM "verification";

3407 DELETE FROM "member_settings";

3408 DELETE FROM "member_useterms";

3409 DELETE FROM "member_profile";

3410 DELETE FROM "rendered_member_statement";

3411 DELETE FROM "member_image";

3412 DELETE FROM "contact";

3413 DELETE FROM "ignored_member";

3414 DELETE FROM "session";

3415 DELETE FROM "system_application";

3416 DELETE FROM "system_application_redirect_uri";

3417 DELETE FROM "dynamic_application_scope";

3418 DELETE FROM "member_application";

3419 DELETE FROM "token";

3420 DELETE FROM "subscription";

3421 DELETE FROM "ignored_area";

3422 DELETE FROM "ignored_initiative";

3423 DELETE FROM "non_voter";

3424 DELETE FROM "direct_voter" USING "issue"

3425 WHERE "direct_voter"."issue_id" = "issue"."id"

3426 AND "issue"."closed" ISNULL;

3427 DELETE FROM "event_processed";

3428 DELETE FROM "notification_initiative_sent";

3429 DELETE FROM "newsletter";

3430 RETURN;

3431 END;

3432 $$;

3433

3434

3435 CREATE TEMPORARY TABLE "old_snapshot" AS

3436 SELECT "ordered".*, row_number() OVER () AS "snapshot_id"

3437 FROM (

3438 SELECT * FROM (

3439 SELECT

3440 "id" AS "issue_id",

3441 'end_of_admission'::"snapshot_event" AS "event",

3442 "accepted" AS "calculated"

3443 FROM "issue" WHERE "accepted" NOTNULL

3444 UNION ALL

3445 SELECT

3446 "id" AS "issue_id",

3447 'half_freeze'::"snapshot_event" AS "event",

3448 "half_frozen" AS "calculated"

3449 FROM "issue" WHERE "half_frozen" NOTNULL

3450 UNION ALL

3451 SELECT

3452 "id" AS "issue_id",

3453 'full_freeze'::"snapshot_event" AS "event",

3454 "fully_frozen" AS "calculated"

3455 FROM "issue" WHERE "fully_frozen" NOTNULL

3456 ) AS "unordered"

3457 ORDER BY "calculated", "issue_id", "event"

3458 ) AS "ordered";

3459

3460

3461 INSERT INTO "snapshot" ("id", "calculated", "population", "area_id", "issue_id")

3462 SELECT

3463 "old_snapshot"."snapshot_id" AS "id",

3464 "old_snapshot"."calculated",

3465 ( SELECT COALESCE(sum("weight"), 0)

3466 FROM "direct_population_snapshot" "dps"

3467 WHERE "dps"."issue_id" = "old_snapshot"."issue_id"

3468 AND "dps"."event" = "old_snapshot"."event"

3469 ) AS "population",

3470 "issue"."area_id" AS "area_id",

3471 "issue"."id" AS "issue_id"

3472 FROM "old_snapshot" JOIN "issue"

3473 ON "old_snapshot"."issue_id" = "issue"."id";

3474

3475

3476 INSERT INTO "snapshot_issue" ("snapshot_id", "issue_id")

3477 SELECT "id" AS "snapshot_id", "issue_id" FROM "snapshot";

3478

3479

3480 INSERT INTO "snapshot_population" ("snapshot_id", "member_id")

3481 SELECT

3482 "old_snapshot"."snapshot_id",

3483 "direct_population_snapshot"."member_id"

3484 FROM "old_snapshot" JOIN "direct_population_snapshot"

3485 ON "old_snapshot"."issue_id" = "direct_population_snapshot"."issue_id"

3486 AND "old_snapshot"."event" = "direct_population_snapshot"."event";

3487

3488 INSERT INTO "snapshot_population" ("snapshot_id", "member_id")

3489 SELECT

3490 "old_snapshot"."snapshot_id",

3491 "delegating_population_snapshot"."member_id"

3492 FROM "old_snapshot" JOIN "delegating_population_snapshot"

3493 ON "old_snapshot"."issue_id" = "delegating_population_snapshot"."issue_id"

3494 AND "old_snapshot"."event" = "delegating_population_snapshot"."event";

3495

3496

3497 INSERT INTO "direct_interest_snapshot"

3498 ("snapshot_id", "issue_id", "member_id", "weight")

3499 SELECT

3500 "old_snapshot"."snapshot_id",

3501 "old_snapshot"."issue_id",

3502 "direct_interest_snapshot_old"."member_id",

3503 "direct_interest_snapshot_old"."weight"

3504 FROM "old_snapshot" JOIN "direct_interest_snapshot_old"

3505 ON "old_snapshot"."issue_id" = "direct_interest_snapshot_old"."issue_id"

3506 AND "old_snapshot"."event" = "direct_interest_snapshot_old"."event";

3507

3508 INSERT INTO "delegating_interest_snapshot"

3509 ( "snapshot_id", "issue_id",

3510 "member_id", "weight", "scope", "delegate_member_ids" )

3511 SELECT

3512 "old_snapshot"."snapshot_id",

3513 "old_snapshot"."issue_id",

3514 "delegating_interest_snapshot_old"."member_id",

3515 "delegating_interest_snapshot_old"."weight",

3516 "delegating_interest_snapshot_old"."scope",

3517 "delegating_interest_snapshot_old"."delegate_member_ids"

3518 FROM "old_snapshot" JOIN "delegating_interest_snapshot_old"

3519 ON "old_snapshot"."issue_id" = "delegating_interest_snapshot_old"."issue_id"

3520 AND "old_snapshot"."event" = "delegating_interest_snapshot_old"."event";

3521

3522 INSERT INTO "direct_supporter_snapshot"

3523 ( "snapshot_id", "issue_id",

3524 "initiative_id", "member_id", "draft_id", "informed", "satisfied" )

3525 SELECT

3526 "old_snapshot"."snapshot_id",

3527 "old_snapshot"."issue_id",

3528 "direct_supporter_snapshot_old"."initiative_id",

3529 "direct_supporter_snapshot_old"."member_id",

3530 "direct_supporter_snapshot_old"."draft_id",

3531 "direct_supporter_snapshot_old"."informed",

3532 "direct_supporter_snapshot_old"."satisfied"

3533 FROM "old_snapshot" JOIN "direct_supporter_snapshot_old"

3534 ON "old_snapshot"."issue_id" = "direct_supporter_snapshot_old"."issue_id"

3535 AND "old_snapshot"."event" = "direct_supporter_snapshot_old"."event";

3536

3537

3538 ALTER TABLE "issue" DISABLE TRIGGER USER; -- NOTE: required to modify table later

3539

3540 UPDATE "issue" SET "latest_snapshot_id" = "snapshot"."id"

3541 FROM (

3542 SELECT DISTINCT ON ("issue_id") "issue_id", "id"

3543 FROM "snapshot" ORDER BY "issue_id", "id" DESC

3544 ) AS "snapshot"

3545 WHERE "snapshot"."issue_id" = "issue"."id";

3546

3547 UPDATE "issue" SET "admission_snapshot_id" = "old_snapshot"."snapshot_id"

3548 FROM "old_snapshot"

3549 WHERE "old_snapshot"."issue_id" = "issue"."id"

3550 AND "old_snapshot"."event" = 'end_of_admission';

3551

3552 UPDATE "issue" SET "half_freeze_snapshot_id" = "old_snapshot"."snapshot_id"

3553 FROM "old_snapshot"

3554 WHERE "old_snapshot"."issue_id" = "issue"."id"

3555 AND "old_snapshot"."event" = 'half_freeze';

3556

3557 UPDATE "issue" SET "full_freeze_snapshot_id" = "old_snapshot"."snapshot_id"

3558 FROM "old_snapshot"

3559 WHERE "old_snapshot"."issue_id" = "issue"."id"

3560 AND "old_snapshot"."event" = 'full_freeze';

3561

3562 ALTER TABLE "issue" ENABLE TRIGGER USER;

3563

3564

3565 DROP TABLE "old_snapshot";

3566

3567 DROP TABLE "direct_supporter_snapshot_old";

3568 DROP TABLE "delegating_interest_snapshot_old";

3569 DROP TABLE "direct_interest_snapshot_old";

3570 DROP TABLE "delegating_population_snapshot";

3571 DROP TABLE "direct_population_snapshot";

3572

3573

3574 DROP VIEW "open_issue";

3575

3576

3577 ALTER TABLE "issue" DROP COLUMN "latest_snapshot_event";

3578

3579

3580 CREATE VIEW "open_issue" AS

3581 SELECT * FROM "issue" WHERE "closed" ISNULL;

3582

3583 COMMENT ON VIEW "open_issue" IS 'All open issues';

3584

3585

3586 -- NOTE: create "issue_for_admission" view after altering table "issue"

3587 CREATE VIEW "issue_for_admission" AS

3588 SELECT DISTINCT ON ("issue"."area_id")

3589 "issue".*,

3590 max("initiative"."supporter_count") AS "max_supporter_count"

3591 FROM "issue"

3592 JOIN "policy" ON "issue"."policy_id" = "policy"."id"

3593 JOIN "initiative" ON "issue"."id" = "initiative"."issue_id"

3594 JOIN "area" ON "issue"."area_id" = "area"."id"

3595 WHERE "issue"."state" = 'admission'::"issue_state"

3596 AND now() >= "issue"."created" + "issue"."min_admission_time"

3597 AND "initiative"."supporter_count" >= "policy"."issue_quorum"

3598 AND "initiative"."supporter_count" * "policy"."issue_quorum_den" >=

3599 "issue"."population" * "policy"."issue_quorum_num"

3600 AND "initiative"."supporter_count" >= "area"."issue_quorum"

3601 AND "initiative"."revoked" ISNULL

3602 GROUP BY "issue"."id"

3603 ORDER BY "issue"."area_id", "max_supporter_count" DESC, "issue"."id";

3604

3605 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';

3606

3607

3608 DROP TYPE "snapshot_event";

3609

3610

3611 ALTER TABLE "issue" ADD CONSTRAINT "snapshot_required" CHECK (

3612 ("half_frozen" ISNULL OR "half_freeze_snapshot_id" NOTNULL) AND

3613 ("fully_frozen" ISNULL OR "full_freeze_snapshot_id" NOTNULL) );

3614

3615

3616 COMMIT;