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

liquid_feedback_core

view update/core-update.v3.2.2-v4.0.0.sql @ 557:0fc78541dc15

Added "verification" table to store verification requests

author	jbe
date	Mon Sep 18 01:44:32 2017 +0200 (2017-09-18)
parents	3f21631a7f6d
children	25b551e53da2

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 "verified" TIMESTAMPTZ,

42 "verification_origin" JSONB,

43 "verification_data" JSONB,

44 "denied" TIMESTAMPTZ,

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

46 "comment" TEXT,

47 CONSTRAINT "verified_and_denied_conflict" CHECK (

48 "verified" ISNULL OR "denied" ISNULL ) );

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

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

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

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

53 CREATE INDEX "verification_member_id_idx" ON "verification" ("member_id");

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

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

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

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

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

61 COMMENT ON COLUMN "verification"."verification_origin" IS 'JSON data containing information about the authority or operator who accepted or denied the request';

62 COMMENT ON COLUMN "verification"."verification_data" IS 'JSON data containing additional verified data, but all public information shall be copied to "member"."identification", "member"."verification" and/or "member"."name" if applicable for setup';

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

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

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

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

69 ALTER TABLE "member" ADD CONSTRAINT "deleted_requires_locked"

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

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

75 CREATE TABLE "member_settings" (

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

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

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

82 CREATE TABLE "member_useterms" (

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

84 "accepted" TIMESTAMPTZ NOT NULL,

85 "contract_identifier" TEXT NOT NULL );

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

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

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

93 CREATE TABLE "member_profile" (

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

95 "formatting_engine" TEXT,

96 "statement" TEXT,

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

98 "profile_text_data" TEXT,

99 "text_search_data" TSVECTOR );

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

101 CREATE TRIGGER "update_text_search_data"

102 BEFORE INSERT OR UPDATE ON "member_profile"

103 FOR EACH ROW EXECUTE PROCEDURE

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

105 'statement', 'profile_text_data');

106

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

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

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

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

111

112

113 INSERT INTO "member_profile"

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

115 SELECT

116 "id" AS "member_id",

117 "formatting_engine",

118 "statement",

119 json_build_object(

120 'organizational_unit', "organizational_unit",

121 'internal_posts', "internal_posts",

122 'realname', "realname",

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

124 'address', "address",

125 'email', "email",

126 'xmpp_address', "xmpp_address",

127 'website', "website",

128 'phone', "phone",

129 'mobile_phone', "mobile_phone",

130 'profession', "profession",

131 'external_memberships', "external_memberships",

132 'external_posts', "external_posts"

133 ) AS "profile"

134 FROM "member";

135

136 UPDATE "member_profile" SET "profile_text_data" =

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

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

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

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

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

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

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

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

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

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

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

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

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

150

151

152 DROP VIEW "newsletter_to_send";

153 DROP VIEW "scheduled_notification_to_send";

154 DROP VIEW "member_to_notify";

155 DROP VIEW "member_eligible_to_be_notified";

156

157

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

173

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

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

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

177

178 DROP TRIGGER "update_text_search_data" ON "member";

179 CREATE TRIGGER "update_text_search_data"

180 BEFORE INSERT OR UPDATE ON "member"

181 FOR EACH ROW EXECUTE PROCEDURE

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

183 "name", "identification");

184

185

186 CREATE VIEW "member_eligible_to_be_notified" AS

187 SELECT * FROM "member"

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

189

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

191

192

193 CREATE VIEW "member_to_notify" AS

194 SELECT * FROM "member_eligible_to_be_notified"

195 WHERE "disable_notifications" = FALSE;

196

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

198

199

200 CREATE VIEW "scheduled_notification_to_send" AS

201 SELECT * FROM (

202 SELECT

203 "id" AS "recipient_id",

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

205 ( "notification_sent"::DATE + CASE

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

207 THEN 0 ELSE 1 END

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

209 ELSE

210 ( "notification_sent"::DATE +

211 ( 7 + "notification_dow" -

212 EXTRACT(DOW FROM

213 ( "notification_sent"::DATE + CASE

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

215 THEN 0 ELSE 1 END

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

217 )::INTEGER

218 ) % 7 +

219 CASE

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

221 THEN 0 ELSE 1

222 END

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

224 END AS "pending"

225 FROM (

226 SELECT

227 "id",

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

229 "notification_dow",

230 "notification_hour"

231 FROM "member_to_notify"

232 WHERE "notification_hour" NOTNULL

233 ) AS "subquery1"

234 ) AS "subquery2"

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

236

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

238

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

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

241

242

243 CREATE VIEW "newsletter_to_send" AS

244 SELECT

245 "member"."id" AS "recipient_id",

246 "newsletter"."id" AS "newsletter_id",

247 "newsletter"."published"

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

249 LEFT JOIN "privilege" ON

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

251 "privilege"."unit_id" = "newsletter"."unit_id" AND

252 "privilege"."voting_right" = TRUE

253 LEFT JOIN "subscription" ON

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

255 "subscription"."unit_id" = "newsletter"."unit_id"

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

257 AND "newsletter"."sent" ISNULL

258 AND (

259 "member"."disable_notifications" = FALSE OR

260 "newsletter"."include_all_members" = TRUE )

261 AND (

262 "newsletter"."unit_id" ISNULL OR

263 "privilege"."member_id" NOTNULL OR

264 "subscription"."member_id" NOTNULL );

265

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

267

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

269

270

271 DROP VIEW "expired_session";

272 DROP TABLE "session";

273

274

275 CREATE TABLE "session" (

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

277 "id" SERIAL8 PRIMARY KEY,

278 "ident" TEXT NOT NULL UNIQUE,

279 "additional_secret" TEXT,

280 "logout_token" TEXT,

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

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

283 "authority" TEXT,

284 "authority_uid" TEXT,

285 "authority_login" TEXT,

286 "needs_delegation_check" BOOLEAN NOT NULL DEFAULT FALSE,

287 "lang" TEXT );

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

289

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

291

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

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

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

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

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

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

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

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

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

301

302

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

304

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

306

307

308 CREATE TABLE "system_application" (

309 "id" SERIAL4 PRIMARY KEY,

310 "name" TEXT NOT NULL,

311 "discovery_baseurl" TEXT,

312 "client_id" TEXT NOT NULL UNIQUE,

313 "default_redirect_uri" TEXT NOT NULL,

314 "cert_common_name" TEXT,

315 "client_cred_scope" TEXT,

316 "flow" "authflow",

317 "automatic_scope" TEXT,

318 "permitted_scope" TEXT,

319 "forbidden_scope" TEXT );

320

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

322

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

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

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

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

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

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

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

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

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

332

333

334 CREATE TABLE "system_application_redirect_uri" (

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

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

337 "redirect_uri" TEXT );

338

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

340

341

342 CREATE TABLE "dynamic_application_scope" (

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

344 "redirect_uri" TEXT,

345 "flow" TEXT,

346 "scope" TEXT,

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

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

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

350

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

352

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

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

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

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

357

358

359 CREATE TABLE "member_application" (

360 "id" SERIAL4 PRIMARY KEY,

361 UNIQUE ("system_application_id", "member_id"),

362 UNIQUE ("domain", "member_id"),

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

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

365 "domain" TEXT,

366 "session_id" INT8,

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

368 "scope" TEXT NOT NULL,

369 CONSTRAINT "system_application_or_domain_but_not_both" CHECK (

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

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

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

373

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

375

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

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

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

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

380

381

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

383

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

385

386

387 CREATE TABLE "token" (

388 "id" SERIAL8 PRIMARY KEY,

389 "token" TEXT NOT NULL UNIQUE,

390 "token_type" "token_type" NOT NULL,

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

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

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

394 "domain" TEXT,

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

396 "session_id" INT8,

397 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"

398 "redirect_uri" TEXT,

399 "redirect_uri_explicit" BOOLEAN,

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

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

402 "used" BOOLEAN NOT NULL DEFAULT FALSE,

403 "scope" TEXT NOT NULL,

404 CONSTRAINT "access_token_needs_expiry"

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

406 CONSTRAINT "authorization_token_needs_redirect_uri"

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

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

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

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

411

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

413

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

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

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

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

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

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

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

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

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

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

424

425

426 CREATE TABLE "token_scope" (

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

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

429 "index" INT4,

430 "scope" TEXT NOT NULL );

431

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

433

434

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

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

437

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

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

440

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

442

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

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

445 ALTER TABLE "policy" ADD CONSTRAINT

446 "issue_quorum_if_and_only_if_not_polling" CHECK (

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

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

449 "polling" = ("issue_quorum_den" ISNULL)

450 );

451 ALTER TABLE "policy" ADD CONSTRAINT

452 "min_admission_time_smaller_than_max_admission_time" CHECK (

453 "min_admission_time" < "max_admission_time"

454 );

455 ALTER TABLE "policy" ADD CONSTRAINT

456 "timing_null_or_not_null_constraints" CHECK (

457 ( "polling" = FALSE AND

458 "min_admission_time" NOTNULL AND "max_admission_time" NOTNULL AND

459 "discussion_time" NOTNULL AND

460 "verification_time" NOTNULL AND

461 "voting_time" NOTNULL ) OR

462 ( "polling" = TRUE AND

463 "min_admission_time" ISNULL AND "max_admission_time" ISNULL AND

464 "discussion_time" NOTNULL AND

465 "verification_time" NOTNULL AND

466 "voting_time" NOTNULL ) OR

467 ( "polling" = TRUE AND

468 "min_admission_time" ISNULL AND "max_admission_time" ISNULL AND

469 "discussion_time" ISNULL AND

470 "verification_time" ISNULL AND

471 "voting_time" ISNULL )

472 );

473

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

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

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

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

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

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

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

481

482

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

484

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

486

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

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

489

490

491 DROP INDEX "area_unit_id_idx";

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

493

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

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

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

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

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

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

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

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

502

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

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

505

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

507

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

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

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

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

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

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

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

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

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

517

518

519 CREATE TABLE "snapshot" (

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

521 "id" SERIAL8 PRIMARY KEY,

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

523 "population" INT4,

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

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

526

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

528

529

530 CREATE TABLE "snapshot_population" (

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

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

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

534

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

536

537

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

539 DROP INDEX "issue_area_id_idx";

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

541 DROP INDEX "issue_policy_id_idx";

542

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

544

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

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

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

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

549

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

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

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

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

554

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

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

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

558

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

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

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

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

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

564

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

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

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

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

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

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

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

572

573

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

575

576

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

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

579

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

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

582

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

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

585

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

587

588

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

590

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

592

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

594

595

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

597

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

599

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

601

602

603 CREATE TABLE "temporary_suggestion_counts" (

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

605 "minus2_unfulfilled_count" INT4 NOT NULL,

606 "minus2_fulfilled_count" INT4 NOT NULL,

607 "minus1_unfulfilled_count" INT4 NOT NULL,

608 "minus1_fulfilled_count" INT4 NOT NULL,

609 "plus1_unfulfilled_count" INT4 NOT NULL,

610 "plus1_fulfilled_count" INT4 NOT NULL,

611 "plus2_unfulfilled_count" INT4 NOT NULL,

612 "plus2_fulfilled_count" INT4 NOT NULL );

613

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

615

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

617

618

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

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

621

622

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

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

625

626

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

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

629

630

631 CREATE TABLE "snapshot_issue" (

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

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

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

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

636

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

638

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

640

641

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

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

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

645

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

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

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

649

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

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

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

653

654

655 CREATE TABLE "direct_interest_snapshot" (

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

657 "snapshot_id" INT8,

658 "issue_id" INT4,

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

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

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

662 "weight" INT4 );

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

664

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

666

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

668

669

670 CREATE TABLE "delegating_interest_snapshot" (

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

672 "snapshot_id" INT8,

673 "issue_id" INT4,

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

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

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

677 "weight" INT4,

678 "scope" "delegation_scope" NOT NULL,

679 "delegate_member_ids" INT4[] NOT NULL );

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

681

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

683

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

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

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

687

688

689 CREATE TABLE "direct_supporter_snapshot" (

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

691 "snapshot_id" INT8,

692 "issue_id" INT4 NOT NULL,

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

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

695 "initiative_id" INT4,

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

697 "draft_id" INT8 NOT NULL,

698 "informed" BOOLEAN NOT NULL,

699 "satisfied" BOOLEAN NOT NULL,

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

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

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

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

704

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

706

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

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

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

710

711

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

713 DROP INDEX "non_voter_member_id_idx";

714

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

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

717

718

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

720 SELECT

721 "member_id",

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

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

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

725

726

727 DROP TABLE "setting";

728 DROP TABLE "setting_map";

729 DROP TABLE "member_relation_setting";

730 DROP TABLE "unit_setting";

731 DROP TABLE "area_setting";

732 DROP TABLE "initiative_setting";

733 DROP TABLE "suggestion_setting";

734

735

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

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

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

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

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

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

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

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

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

745

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

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

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

749

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

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

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

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

754

755 UPDATE "event" SET

756 "unit_id" = "area"."unit_id",

757 "area_id" = "issue"."area_id",

758 "policy_id" = "issue"."policy_id"

759 FROM "issue", "area"

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

761

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

763 "event" != 'issue_state_changed' OR (

764 "member_id" ISNULL AND

765 "other_member_id" ISNULL AND

766 "scope" ISNULL AND

767 "unit_id" NOTNULL AND

768 "area_id" NOTNULL AND

769 "policy_id" NOTNULL AND

770 "issue_id" NOTNULL AND

771 "state" NOTNULL AND

772 "initiative_id" ISNULL AND

773 "draft_id" ISNULL AND

774 "suggestion_id" ISNULL AND

775 "boolean_value" ISNULL AND

776 "numeric_value" ISNULL AND

777 "text_value" ISNULL AND

778 "old_text_value" ISNULL ));

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

780 "event" NOT IN (

781 'initiative_created_in_new_issue',

782 'initiative_created_in_existing_issue',

783 'initiative_revoked',

784 'new_draft_created'

785 ) OR (

786 "member_id" NOTNULL AND

787 "other_member_id" ISNULL AND

788 "scope" ISNULL AND

789 "unit_id" NOTNULL AND

790 "area_id" NOTNULL AND

791 "policy_id" NOTNULL AND

792 "issue_id" NOTNULL AND

793 "state" NOTNULL AND

794 "initiative_id" NOTNULL AND

795 "draft_id" NOTNULL AND

796 "suggestion_id" ISNULL AND

797 "boolean_value" ISNULL AND

798 "numeric_value" ISNULL AND

799 "text_value" ISNULL AND

800 "old_text_value" ISNULL ));

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

802 "event" != 'suggestion_created' OR (

803 "member_id" NOTNULL AND

804 "other_member_id" ISNULL AND

805 "scope" ISNULL AND

806 "unit_id" NOTNULL AND

807 "area_id" NOTNULL AND

808 "policy_id" NOTNULL AND

809 "issue_id" NOTNULL AND

810 "state" NOTNULL AND

811 "initiative_id" NOTNULL AND

812 "draft_id" ISNULL AND

813 "suggestion_id" NOTNULL AND

814 "boolean_value" ISNULL AND

815 "numeric_value" ISNULL AND

816 "text_value" ISNULL AND

817 "old_text_value" ISNULL ));

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

819 "event" != 'suggestion_deleted' OR (

820 "member_id" ISNULL AND

821 "other_member_id" ISNULL AND

822 "scope" ISNULL AND

823 "unit_id" NOTNULL AND

824 "area_id" NOTNULL AND

825 "policy_id" NOTNULL AND

826 "issue_id" NOTNULL AND

827 "state" NOTNULL AND

828 "initiative_id" NOTNULL AND

829 "draft_id" ISNULL AND

830 "suggestion_id" NOTNULL AND

831 "boolean_value" ISNULL AND

832 "numeric_value" ISNULL AND

833 "text_value" ISNULL AND

834 "old_text_value" ISNULL ));

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

836 "event" NOT IN (

837 'member_activated',

838 'member_deleted',

839 'member_profile_updated',

840 'member_image_updated'

841 ) OR (

842 "member_id" NOTNULL AND

843 "other_member_id" ISNULL AND

844 "scope" ISNULL AND

845 "unit_id" ISNULL AND

846 "area_id" ISNULL AND

847 "policy_id" ISNULL AND

848 "issue_id" ISNULL AND

849 "state" ISNULL AND

850 "initiative_id" ISNULL AND

851 "draft_id" ISNULL AND

852 "suggestion_id" ISNULL AND

853 "boolean_value" ISNULL AND

854 "numeric_value" ISNULL AND

855 "text_value" ISNULL AND

856 "old_text_value" ISNULL ));

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

858 "event" != 'member_active' OR (

859 "member_id" NOTNULL AND

860 "other_member_id" ISNULL AND

861 "scope" ISNULL AND

862 "unit_id" ISNULL AND

863 "area_id" ISNULL AND

864 "policy_id" ISNULL AND

865 "issue_id" ISNULL AND

866 "state" ISNULL AND

867 "initiative_id" ISNULL AND

868 "draft_id" ISNULL AND

869 "suggestion_id" ISNULL AND

870 "boolean_value" NOTNULL AND

871 "numeric_value" ISNULL AND

872 "text_value" ISNULL AND

873 "old_text_value" ISNULL ));

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

875 "event" != 'member_name_updated' OR (

876 "member_id" NOTNULL AND

877 "other_member_id" ISNULL AND

878 "scope" ISNULL AND

879 "unit_id" ISNULL AND

880 "area_id" ISNULL AND

881 "policy_id" ISNULL AND

882 "issue_id" ISNULL AND

883 "state" ISNULL AND

884 "initiative_id" ISNULL AND

885 "draft_id" ISNULL AND

886 "suggestion_id" ISNULL AND

887 "boolean_value" ISNULL AND

888 "numeric_value" ISNULL AND

889 "text_value" NOTNULL AND

890 "old_text_value" NOTNULL ));

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

892 "event" != 'interest' OR (

893 "member_id" NOTNULL AND

894 "other_member_id" ISNULL AND

895 "scope" ISNULL AND

896 "unit_id" NOTNULL AND

897 "area_id" NOTNULL AND

898 "policy_id" NOTNULL AND

899 "issue_id" NOTNULL AND

900 "state" NOTNULL AND

901 "initiative_id" ISNULL AND

902 "draft_id" ISNULL AND

903 "suggestion_id" ISNULL AND

904 "boolean_value" NOTNULL AND

905 "numeric_value" ISNULL AND

906 "text_value" ISNULL AND

907 "old_text_value" ISNULL ));

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

909 "event" != 'initiator' OR (

910 "member_id" NOTNULL AND

911 "other_member_id" ISNULL AND

912 "scope" ISNULL AND

913 "unit_id" NOTNULL AND

914 "area_id" NOTNULL AND

915 "policy_id" NOTNULL AND

916 "issue_id" NOTNULL AND

917 "state" NOTNULL AND

918 "initiative_id" NOTNULL AND

919 "draft_id" ISNULL AND

920 "suggestion_id" ISNULL AND

921 "boolean_value" NOTNULL AND

922 "numeric_value" ISNULL AND

923 "text_value" ISNULL AND

924 "old_text_value" ISNULL ));

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

926 "event" != 'support' OR (

927 "member_id" NOTNULL AND

928 "other_member_id" ISNULL AND

929 "scope" ISNULL AND

930 "unit_id" NOTNULL AND

931 "area_id" NOTNULL AND

932 "policy_id" NOTNULL AND

933 "issue_id" NOTNULL AND

934 "state" NOTNULL AND

935 "initiative_id" NOTNULL AND

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

937 "suggestion_id" ISNULL AND

938 "boolean_value" NOTNULL AND

939 "numeric_value" ISNULL AND

940 "text_value" ISNULL AND

941 "old_text_value" ISNULL ));

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

943 "event" != 'support_updated' OR (

944 "member_id" NOTNULL AND

945 "other_member_id" ISNULL AND

946 "scope" ISNULL AND

947 "unit_id" NOTNULL AND

948 "area_id" NOTNULL AND

949 "policy_id" NOTNULL AND

950 "issue_id" NOTNULL AND

951 "state" NOTNULL AND

952 "initiative_id" NOTNULL AND

953 "draft_id" NOTNULL AND

954 "suggestion_id" ISNULL AND

955 "boolean_value" ISNULL AND

956 "numeric_value" ISNULL AND

957 "text_value" ISNULL AND

958 "old_text_value" ISNULL ));

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

960 "event" != 'suggestion_rated' OR (

961 "member_id" NOTNULL AND

962 "other_member_id" ISNULL AND

963 "scope" ISNULL AND

964 "unit_id" NOTNULL AND

965 "area_id" NOTNULL AND

966 "policy_id" NOTNULL AND

967 "issue_id" NOTNULL AND

968 "state" NOTNULL AND

969 "initiative_id" NOTNULL AND

970 "draft_id" ISNULL AND

971 "suggestion_id" NOTNULL AND

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

973 "numeric_value" NOTNULL AND

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

975 "text_value" ISNULL AND

976 "old_text_value" ISNULL ));

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

978 "event" != 'delegation' OR (

979 "member_id" NOTNULL AND

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

981 "scope" NOTNULL AND

982 "unit_id" NOTNULL AND

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

984 "policy_id" ISNULL AND

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

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

987 "initiative_id" ISNULL AND

988 "draft_id" ISNULL AND

989 "suggestion_id" ISNULL AND

990 "boolean_value" NOTNULL AND

991 "numeric_value" ISNULL AND

992 "text_value" ISNULL AND

993 "old_text_value" ISNULL ));

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

995 "event" != 'contact' OR (

996 "member_id" NOTNULL AND

997 "other_member_id" NOTNULL AND

998 "scope" ISNULL AND

999 "unit_id" ISNULL AND

1000 "area_id" ISNULL AND

1001 "policy_id" ISNULL AND

1002 "issue_id" ISNULL AND

1003 "state" ISNULL AND

1004 "initiative_id" ISNULL AND

1005 "draft_id" ISNULL AND

1006 "suggestion_id" ISNULL AND

1007 "boolean_value" NOTNULL AND

1008 "numeric_value" ISNULL AND

1009 "text_value" ISNULL AND

1010 "old_text_value" ISNULL ));

1011

1012

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

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

1015

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

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

1018

1019

1020 CREATE FUNCTION "write_event_unit_trigger"()

1021 RETURNS TRIGGER

1022 LANGUAGE 'plpgsql' VOLATILE AS $$

1023 DECLARE

1024 "event_v" "event_type";

1025 BEGIN

1026 IF TG_OP = 'UPDATE' THEN

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

1028 RETURN NULL;

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

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

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

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

1033 ELSIF OLD != NEW THEN

1034 "event_v" := 'unit_updated';

1035 ELSE

1036 RETURN NULL;

1037 END IF;

1038 ELSE

1039 "event_v" := 'unit_created';

1040 END IF;

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

1042 RETURN NULL;

1043 END;

1044 $$;

1045

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

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

1048

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

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

1051

1052

1053 CREATE FUNCTION "write_event_area_trigger"()

1054 RETURNS TRIGGER

1055 LANGUAGE 'plpgsql' VOLATILE AS $$

1056 DECLARE

1057 "event_v" "event_type";

1058 BEGIN

1059 IF TG_OP = 'UPDATE' THEN

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

1061 RETURN NULL;

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

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

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

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

1066 ELSIF OLD != NEW THEN

1067 "event_v" := 'area_updated';

1068 ELSE

1069 RETURN NULL;

1070 END IF;

1071 ELSE

1072 "event_v" := 'area_created';

1073 END IF;

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

1075 RETURN NULL;

1076 END;

1077 $$;

1078

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

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

1081

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

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

1084

1085

1086 CREATE FUNCTION "write_event_policy_trigger"()

1087 RETURNS TRIGGER

1088 LANGUAGE 'plpgsql' VOLATILE AS $$

1089 DECLARE

1090 "event_v" "event_type";

1091 BEGIN

1092 IF TG_OP = 'UPDATE' THEN

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

1094 RETURN NULL;

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

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

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

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

1099 ELSIF OLD != NEW THEN

1100 "event_v" := 'policy_updated';

1101 ELSE

1102 RETURN NULL;

1103 END IF;

1104 ELSE

1105 "event_v" := 'policy_created';

1106 END IF;

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

1108 RETURN NULL;

1109 END;

1110 $$;

1111

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

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

1114

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

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

1117

1118

1119 CREATE OR REPLACE FUNCTION "write_event_issue_state_changed_trigger"()

1120 RETURNS TRIGGER

1121 LANGUAGE 'plpgsql' VOLATILE AS $$

1122 DECLARE

1123 "area_row" "area"%ROWTYPE;

1124 BEGIN

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

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

1127 FOR SHARE;

1128 INSERT INTO "event" (

1129 "event",

1130 "unit_id", "area_id", "policy_id", "issue_id", "state"

1131 ) VALUES (

1132 'issue_state_changed',

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

1134 NEW."id", NEW."state"

1135 );

1136 END IF;

1137 RETURN NULL;

1138 END;

1139 $$;

1140

1141

1142 CREATE OR REPLACE FUNCTION "write_event_initiative_or_draft_created_trigger"()

1143 RETURNS TRIGGER

1144 LANGUAGE 'plpgsql' VOLATILE AS $$

1145 DECLARE

1146 "initiative_row" "initiative"%ROWTYPE;

1147 "issue_row" "issue"%ROWTYPE;

1148 "area_row" "area"%ROWTYPE;

1149 "event_v" "event_type";

1150 BEGIN

1151 SELECT * INTO "initiative_row" FROM "initiative"

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

1153 SELECT * INTO "issue_row" FROM "issue"

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

1155 SELECT * INTO "area_row" FROM "area"

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

1157 IF EXISTS (

1158 SELECT NULL FROM "draft"

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

1160 FOR SHARE

1161 ) THEN

1162 "event_v" := 'new_draft_created';

1163 ELSE

1164 IF EXISTS (

1165 SELECT NULL FROM "initiative"

1166 WHERE "issue_id" = "initiative_row"."issue_id"

1167 AND "id" != "initiative_row"."id"

1168 FOR SHARE

1169 ) THEN

1170 "event_v" := 'initiative_created_in_existing_issue';

1171 ELSE

1172 "event_v" := 'initiative_created_in_new_issue';

1173 END IF;

1174 END IF;

1175 INSERT INTO "event" (

1176 "event", "member_id",

1177 "unit_id", "area_id", "policy_id", "issue_id", "state",

1178 "initiative_id", "draft_id"

1179 ) VALUES (

1180 "event_v", NEW."author_id",

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

1182 "initiative_row"."issue_id", "issue_row"."state",

1183 NEW."initiative_id", NEW."id"

1184 );

1185 RETURN NULL;

1186 END;

1187 $$;

1188

1189

1190 CREATE OR REPLACE FUNCTION "write_event_initiative_revoked_trigger"()

1191 RETURNS TRIGGER

1192 LANGUAGE 'plpgsql' VOLATILE AS $$

1193 DECLARE

1194 "issue_row" "issue"%ROWTYPE;

1195 "area_row" "area"%ROWTYPE;

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

1197 BEGIN

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

1199 SELECT * INTO "issue_row" FROM "issue"

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

1201 SELECT * INTO "area_row" FROM "area"

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

1203 SELECT "id" INTO "draft_id_v" FROM "current_draft"

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

1205 INSERT INTO "event" (

1206 "event", "member_id",

1207 "unit_id", "area_id", "policy_id", "issue_id", "state",

1208 "initiative_id", "draft_id"

1209 ) VALUES (

1210 'initiative_revoked', NEW."revoked_by_member_id",

1211 "area_row"."unit_id", "issue_row"."area_id",

1212 "issue_row"."policy_id",

1213 NEW."issue_id", "issue_row"."state",

1214 NEW."id", "draft_id_v"

1215 );

1216 END IF;

1217 RETURN NULL;

1218 END;

1219 $$;

1220

1221

1222 CREATE OR REPLACE FUNCTION "write_event_suggestion_created_trigger"()

1223 RETURNS TRIGGER

1224 LANGUAGE 'plpgsql' VOLATILE AS $$

1225 DECLARE

1226 "initiative_row" "initiative"%ROWTYPE;

1227 "issue_row" "issue"%ROWTYPE;

1228 "area_row" "area"%ROWTYPE;

1229 BEGIN

1230 SELECT * INTO "initiative_row" FROM "initiative"

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

1232 SELECT * INTO "issue_row" FROM "issue"

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

1234 SELECT * INTO "area_row" FROM "area"

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

1236 INSERT INTO "event" (

1237 "event", "member_id",

1238 "unit_id", "area_id", "policy_id", "issue_id", "state",

1239 "initiative_id", "suggestion_id"

1240 ) VALUES (

1241 'suggestion_created', NEW."author_id",

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

1243 "initiative_row"."issue_id", "issue_row"."state",

1244 NEW."initiative_id", NEW."id"

1245 );

1246 RETURN NULL;

1247 END;

1248 $$;

1249

1250

1251 CREATE FUNCTION "write_event_suggestion_removed_trigger"()

1252 RETURNS TRIGGER

1253 LANGUAGE 'plpgsql' VOLATILE AS $$

1254 DECLARE

1255 "initiative_row" "initiative"%ROWTYPE;

1256 "issue_row" "issue"%ROWTYPE;

1257 "area_row" "area"%ROWTYPE;

1258 BEGIN

1259 SELECT * INTO "initiative_row" FROM "initiative"

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

1261 IF "initiative_row"."id" NOTNULL THEN

1262 SELECT * INTO "issue_row" FROM "issue"

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

1264 SELECT * INTO "area_row" FROM "area"

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

1266 INSERT INTO "event" (

1267 "event",

1268 "unit_id", "area_id", "policy_id", "issue_id", "state",

1269 "initiative_id", "suggestion_id"

1270 ) VALUES (

1271 'suggestion_deleted',

1272 "area_row"."unit_id", "issue_row"."area_id",

1273 "issue_row"."policy_id",

1274 "initiative_row"."issue_id", "issue_row"."state",

1275 OLD."initiative_id", OLD."id"

1276 );

1277 END IF;

1278 RETURN NULL;

1279 END;

1280 $$;

1281

1282 CREATE TRIGGER "write_event_suggestion_removed"

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

1284 "write_event_suggestion_removed_trigger"();

1285

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

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

1288

1289

1290 CREATE FUNCTION "write_event_member_trigger"()

1291 RETURNS TRIGGER

1292 LANGUAGE 'plpgsql' VOLATILE AS $$

1293 BEGIN

1294 IF TG_OP = 'INSERT' THEN

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

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

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

1298 END IF;

1299 IF NEW."active" THEN

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

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

1302 END IF;

1303 ELSIF TG_OP = 'UPDATE' THEN

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

1305 RAISE EXCEPTION 'Cannot change member ID';

1306 END IF;

1307 IF

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

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

1310 THEN

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

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

1313 END IF;

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

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

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

1317 );

1318 END IF;

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

1320 INSERT INTO "event" (

1321 "event", "member_id", "text_value", "old_text_value"

1322 ) VALUES (

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

1324 );

1325 END IF;

1326 IF

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

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

1329 THEN

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

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

1332 END IF;

1333 END IF;

1334 RETURN NULL;

1335 END;

1336 $$;

1337

1338 CREATE TRIGGER "write_event_member"

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

1340 "write_event_member_trigger"();

1341

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

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

1344

1345

1346 CREATE FUNCTION "write_event_member_profile_updated_trigger"()

1347 RETURNS TRIGGER

1348 LANGUAGE 'plpgsql' VOLATILE AS $$

1349 BEGIN

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

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

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

1353 'member_profile_updated', OLD."member_id"

1354 );

1355 END IF;

1356 END IF;

1357 IF TG_OP = 'UPDATE' THEN

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

1359 RETURN NULL;

1360 END IF;

1361 END IF;

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

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

1364 'member_profile_updated', NEW."member_id"

1365 );

1366 END IF;

1367 RETURN NULL;

1368 END;

1369 $$;

1370

1371 CREATE TRIGGER "write_event_member_profile_updated"

1372 AFTER INSERT OR UPDATE OR DELETE ON "member_profile"

1373 FOR EACH ROW EXECUTE PROCEDURE

1374 "write_event_member_profile_updated_trigger"();

1375

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

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

1378

1379

1380 CREATE FUNCTION "write_event_member_image_updated_trigger"()

1381 RETURNS TRIGGER

1382 LANGUAGE 'plpgsql' VOLATILE AS $$

1383 BEGIN

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

1385 IF NOT OLD."scaled" THEN

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

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

1388 'member_image_updated', OLD."member_id"

1389 );

1390 END IF;

1391 END IF;

1392 END IF;

1393 IF TG_OP = 'UPDATE' THEN

1394 IF

1395 OLD."member_id" = NEW."member_id" AND

1396 OLD."scaled" = NEW."scaled"

1397 THEN

1398 RETURN NULL;

1399 END IF;

1400 END IF;

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

1402 IF NOT NEW."scaled" THEN

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

1404 'member_image_updated', NEW."member_id"

1405 );

1406 END IF;

1407 END IF;

1408 RETURN NULL;

1409 END;

1410 $$;

1411

1412 CREATE TRIGGER "write_event_member_image_updated"

1413 AFTER INSERT OR UPDATE OR DELETE ON "member_image"

1414 FOR EACH ROW EXECUTE PROCEDURE

1415 "write_event_member_image_updated_trigger"();

1416

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

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

1419

1420

1421 CREATE FUNCTION "write_event_interest_trigger"()

1422 RETURNS TRIGGER

1423 LANGUAGE 'plpgsql' VOLATILE AS $$

1424 DECLARE

1425 "issue_row" "issue"%ROWTYPE;

1426 "area_row" "area"%ROWTYPE;

1427 BEGIN

1428 IF TG_OP = 'UPDATE' THEN

1429 IF OLD = NEW THEN

1430 RETURN NULL;

1431 END IF;

1432 END IF;

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

1434 SELECT * INTO "issue_row" FROM "issue"

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

1436 SELECT * INTO "area_row" FROM "area"

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

1438 IF "issue_row"."id" NOTNULL THEN

1439 INSERT INTO "event" (

1440 "event", "member_id",

1441 "unit_id", "area_id", "policy_id", "issue_id", "state",

1442 "boolean_value"

1443 ) VALUES (

1444 'interest', OLD."member_id",

1445 "area_row"."unit_id", "issue_row"."area_id",

1446 "issue_row"."policy_id",

1447 OLD."issue_id", "issue_row"."state",

1448 FALSE

1449 );

1450 END IF;

1451 END IF;

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

1453 SELECT * INTO "issue_row" FROM "issue"

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

1455 SELECT * INTO "area_row" FROM "area"

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

1457 INSERT INTO "event" (

1458 "event", "member_id",

1459 "unit_id", "area_id", "policy_id", "issue_id", "state",

1460 "boolean_value"

1461 ) VALUES (

1462 'interest', NEW."member_id",

1463 "area_row"."unit_id", "issue_row"."area_id",

1464 "issue_row"."policy_id",

1465 NEW."issue_id", "issue_row"."state",

1466 TRUE

1467 );

1468 END IF;

1469 RETURN NULL;

1470 END;

1471 $$;

1472

1473 CREATE TRIGGER "write_event_interest"

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

1475 "write_event_interest_trigger"();

1476

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

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

1479

1480

1481 CREATE FUNCTION "write_event_initiator_trigger"()

1482 RETURNS TRIGGER

1483 LANGUAGE 'plpgsql' VOLATILE AS $$

1484 DECLARE

1485 "initiative_row" "initiative"%ROWTYPE;

1486 "issue_row" "issue"%ROWTYPE;

1487 "area_row" "area"%ROWTYPE;

1488 "accepted_v" BOOLEAN = FALSE;

1489 "rejected_v" BOOLEAN = FALSE;

1490 BEGIN

1491 IF TG_OP = 'UPDATE' THEN

1492 IF

1493 OLD."initiative_id" = NEW."initiative_id" AND

1494 OLD."member_id" = NEW."member_id"

1495 THEN

1496 IF

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

1498 THEN

1499 RETURN NULL;

1500 END IF;

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

1502 "accepted_v" := TRUE;

1503 ELSE

1504 "rejected_v" := TRUE;

1505 END IF;

1506 END IF;

1507 END IF;

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

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

1510 SELECT * INTO "initiative_row" FROM "initiative"

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

1512 IF "initiative_row"."id" NOTNULL THEN

1513 SELECT * INTO "issue_row" FROM "issue"

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

1515 SELECT * INTO "area_row" FROM "area"

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

1517 INSERT INTO "event" (

1518 "event", "member_id",

1519 "unit_id", "area_id", "policy_id", "issue_id", "state",

1520 "initiative_id", "boolean_value"

1521 ) VALUES (

1522 'initiator', OLD."member_id",

1523 "area_row"."unit_id", "issue_row"."area_id",

1524 "issue_row"."policy_id",

1525 "issue_row"."id", "issue_row"."state",

1526 OLD."initiative_id", FALSE

1527 );

1528 END IF;

1529 END IF;

1530 END IF;

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

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

1533 SELECT * INTO "initiative_row" FROM "initiative"

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

1535 SELECT * INTO "issue_row" FROM "issue"

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

1537 SELECT * INTO "area_row" FROM "area"

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

1539 INSERT INTO "event" (

1540 "event", "member_id",

1541 "unit_id", "area_id", "policy_id", "issue_id", "state",

1542 "initiative_id", "boolean_value"

1543 ) VALUES (

1544 'initiator', NEW."member_id",

1545 "area_row"."unit_id", "issue_row"."area_id",

1546 "issue_row"."policy_id",

1547 "issue_row"."id", "issue_row"."state",

1548 NEW."initiative_id", TRUE

1549 );

1550 END IF;

1551 END IF;

1552 RETURN NULL;

1553 END;

1554 $$;

1555

1556 CREATE TRIGGER "write_event_initiator"

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

1558 "write_event_initiator_trigger"();

1559

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

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

1562

1563

1564 CREATE FUNCTION "write_event_support_trigger"()

1565 RETURNS TRIGGER

1566 LANGUAGE 'plpgsql' VOLATILE AS $$

1567 DECLARE

1568 "issue_row" "issue"%ROWTYPE;

1569 "area_row" "area"%ROWTYPE;

1570 BEGIN

1571 IF TG_OP = 'UPDATE' THEN

1572 IF

1573 OLD."initiative_id" = NEW."initiative_id" AND

1574 OLD."member_id" = NEW."member_id"

1575 THEN

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

1577 SELECT * INTO "issue_row" FROM "issue"

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

1579 SELECT * INTO "area_row" FROM "area"

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

1581 INSERT INTO "event" (

1582 "event", "member_id",

1583 "unit_id", "area_id", "policy_id", "issue_id", "state",

1584 "initiative_id", "draft_id"

1585 ) VALUES (

1586 'support_updated', NEW."member_id",

1587 "area_row"."unit_id", "issue_row"."area_id",

1588 "issue_row"."policy_id",

1589 "issue_row"."id", "issue_row"."state",

1590 NEW."initiative_id", NEW."draft_id"

1591 );

1592 END IF;

1593 RETURN NULL;

1594 END IF;

1595 END IF;

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

1597 IF EXISTS (

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

1599 FOR SHARE

1600 ) THEN

1601 SELECT * INTO "issue_row" FROM "issue"

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

1603 SELECT * INTO "area_row" FROM "area"

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

1605 INSERT INTO "event" (

1606 "event", "member_id",

1607 "unit_id", "area_id", "policy_id", "issue_id", "state",

1608 "initiative_id", "boolean_value"

1609 ) VALUES (

1610 'support', OLD."member_id",

1611 "area_row"."unit_id", "issue_row"."area_id",

1612 "issue_row"."policy_id",

1613 "issue_row"."id", "issue_row"."state",

1614 OLD."initiative_id", FALSE

1615 );

1616 END IF;

1617 END IF;

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

1619 SELECT * INTO "issue_row" FROM "issue"

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

1621 SELECT * INTO "area_row" FROM "area"

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

1623 INSERT INTO "event" (

1624 "event", "member_id",

1625 "unit_id", "area_id", "policy_id", "issue_id", "state",

1626 "initiative_id", "draft_id", "boolean_value"

1627 ) VALUES (

1628 'support', NEW."member_id",

1629 "area_row"."unit_id", "issue_row"."area_id",

1630 "issue_row"."policy_id",

1631 "issue_row"."id", "issue_row"."state",

1632 NEW."initiative_id", NEW."draft_id", TRUE

1633 );

1634 END IF;

1635 RETURN NULL;

1636 END;

1637 $$;

1638

1639 CREATE TRIGGER "write_event_support"

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

1641 "write_event_support_trigger"();

1642

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

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

1645

1646

1647 CREATE FUNCTION "write_event_suggestion_rated_trigger"()

1648 RETURNS TRIGGER

1649 LANGUAGE 'plpgsql' VOLATILE AS $$

1650 DECLARE

1651 "same_pkey_v" BOOLEAN = FALSE;

1652 "initiative_row" "initiative"%ROWTYPE;

1653 "issue_row" "issue"%ROWTYPE;

1654 "area_row" "area"%ROWTYPE;

1655 BEGIN

1656 IF TG_OP = 'UPDATE' THEN

1657 IF

1658 OLD."suggestion_id" = NEW."suggestion_id" AND

1659 OLD."member_id" = NEW."member_id"

1660 THEN

1661 IF

1662 OLD."degree" = NEW."degree" AND

1663 OLD."fulfilled" = NEW."fulfilled"

1664 THEN

1665 RETURN NULL;

1666 END IF;

1667 "same_pkey_v" := TRUE;

1668 END IF;

1669 END IF;

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

1671 IF EXISTS (

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

1673 FOR SHARE

1674 ) THEN

1675 SELECT * INTO "initiative_row" FROM "initiative"

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

1677 SELECT * INTO "issue_row" FROM "issue"

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

1679 SELECT * INTO "area_row" FROM "area"

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

1681 INSERT INTO "event" (

1682 "event", "member_id",

1683 "unit_id", "area_id", "policy_id", "issue_id", "state",

1684 "initiative_id", "suggestion_id",

1685 "boolean_value", "numeric_value"

1686 ) VALUES (

1687 'suggestion_rated', OLD."member_id",

1688 "area_row"."unit_id", "issue_row"."area_id",

1689 "issue_row"."policy_id",

1690 "initiative_row"."issue_id", "issue_row"."state",

1691 OLD."initiative_id", OLD."suggestion_id",

1692 NULL, 0

1693 );

1694 END IF;

1695 END IF;

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

1697 SELECT * INTO "initiative_row" FROM "initiative"

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

1699 SELECT * INTO "issue_row" FROM "issue"

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

1701 SELECT * INTO "area_row" FROM "area"

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

1703 INSERT INTO "event" (

1704 "event", "member_id",

1705 "unit_id", "area_id", "policy_id", "issue_id", "state",

1706 "initiative_id", "suggestion_id",

1707 "boolean_value", "numeric_value"

1708 ) VALUES (

1709 'suggestion_rated', NEW."member_id",

1710 "area_row"."unit_id", "issue_row"."area_id",

1711 "issue_row"."policy_id",

1712 "initiative_row"."issue_id", "issue_row"."state",

1713 NEW."initiative_id", NEW."suggestion_id",

1714 NEW."fulfilled", NEW."degree"

1715 );

1716 END IF;

1717 RETURN NULL;

1718 END;

1719 $$;

1720

1721 CREATE TRIGGER "write_event_suggestion_rated"

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

1723 "write_event_suggestion_rated_trigger"();

1724

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

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

1727

1728

1729 CREATE FUNCTION "write_event_delegation_trigger"()

1730 RETURNS TRIGGER

1731 LANGUAGE 'plpgsql' VOLATILE AS $$

1732 DECLARE

1733 "issue_row" "issue"%ROWTYPE;

1734 "area_row" "area"%ROWTYPE;

1735 BEGIN

1736 IF TG_OP = 'DELETE' THEN

1737 IF EXISTS (

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

1739 ) AND (CASE OLD."scope"

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

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

1742 )

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

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

1745 )

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

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

1748 )

1749 END) THEN

1750 SELECT * INTO "issue_row" FROM "issue"

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

1752 SELECT * INTO "area_row" FROM "area"

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

1754 FOR SHARE;

1755 INSERT INTO "event" (

1756 "event", "member_id", "scope",

1757 "unit_id", "area_id", "issue_id", "state",

1758 "boolean_value"

1759 ) VALUES (

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

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

1762 OLD."issue_id", "issue_row"."state",

1763 FALSE

1764 );

1765 END IF;

1766 ELSE

1767 SELECT * INTO "issue_row" FROM "issue"

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

1769 SELECT * INTO "area_row" FROM "area"

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

1771 FOR SHARE;

1772 INSERT INTO "event" (

1773 "event", "member_id", "other_member_id", "scope",

1774 "unit_id", "area_id", "issue_id", "state",

1775 "boolean_value"

1776 ) VALUES (

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

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

1779 NEW."issue_id", "issue_row"."state",

1780 TRUE

1781 );

1782 END IF;

1783 RETURN NULL;

1784 END;

1785 $$;

1786

1787 CREATE TRIGGER "write_event_delegation"

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

1789 "write_event_delegation_trigger"();

1790

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

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

1793

1794

1795 CREATE FUNCTION "write_event_contact_trigger"()

1796 RETURNS TRIGGER

1797 LANGUAGE 'plpgsql' VOLATILE AS $$

1798 BEGIN

1799 IF TG_OP = 'UPDATE' THEN

1800 IF

1801 OLD."member_id" = NEW."member_id" AND

1802 OLD."other_member_id" = NEW."other_member_id" AND

1803 OLD."public" = NEW."public"

1804 THEN

1805 RETURN NULL;

1806 END IF;

1807 END IF;

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

1809 IF OLD."public" THEN

1810 IF EXISTS (

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

1812 FOR SHARE

1813 ) AND EXISTS (

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

1815 FOR SHARE

1816 ) THEN

1817 INSERT INTO "event" (

1818 "event", "member_id", "other_member_id", "boolean_value"

1819 ) VALUES (

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

1821 );

1822 END IF;

1823 END IF;

1824 END IF;

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

1826 IF NEW."public" THEN

1827 INSERT INTO "event" (

1828 "event", "member_id", "other_member_id", "boolean_value"

1829 ) VALUES (

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

1831 );

1832 END IF;

1833 END IF;

1834 RETURN NULL;

1835 END;

1836 $$;

1837

1838 CREATE TRIGGER "write_event_contact"

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

1840 "write_event_contact_trigger"();

1841

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

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

1844

1845

1846 CREATE FUNCTION "send_event_notify_trigger"()

1847 RETURNS TRIGGER

1848 LANGUAGE 'plpgsql' VOLATILE AS $$

1849 BEGIN

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

1851 RETURN NULL;

1852 END;

1853 $$;

1854

1855 CREATE TRIGGER "send_notify"

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

1857 "send_event_notify_trigger"();

1858

1859

1860 CREATE FUNCTION "delete_extended_scope_tokens_trigger"()

1861 RETURNS TRIGGER

1862 LANGUAGE 'plpgsql' VOLATILE AS $$

1863 DECLARE

1864 "system_application_row" "system_application"%ROWTYPE;

1865 BEGIN

1866 IF OLD."system_application_id" NOTNULL THEN

1867 SELECT * FROM "system_application" INTO "system_application_row"

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

1869 DELETE FROM "token"

1870 WHERE "member_id" = OLD."member_id"

1871 AND "system_application_id" = OLD."system_application_id"

1872 AND NOT COALESCE(

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

1874 regexp_split_to_array(

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

1876 ),

1877 FALSE

1878 );

1879 END IF;

1880 RETURN OLD;

1881 END;

1882 $$;

1883

1884 CREATE TRIGGER "delete_extended_scope_tokens"

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

1886 "delete_extended_scope_tokens_trigger"();

1887

1888

1889 CREATE FUNCTION "detach_token_from_session_trigger"()

1890 RETURNS TRIGGER

1891 LANGUAGE 'plpgsql' VOLATILE AS $$

1892 BEGIN

1893 UPDATE "token" SET "session_id" = NULL

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

1895 RETURN OLD;

1896 END;

1897 $$;

1898

1899 CREATE TRIGGER "detach_token_from_session"

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

1901 "detach_token_from_session_trigger"();

1902

1903

1904 CREATE FUNCTION "delete_non_detached_scope_with_session_trigger"()

1905 RETURNS TRIGGER

1906 LANGUAGE 'plpgsql' VOLATILE AS $$

1907 BEGIN

1908 IF NEW."session_id" ISNULL THEN

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

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

1911 WHERE "element" LIKE '%_detached';

1912 END IF;

1913 RETURN NEW;

1914 END;

1915 $$;

1916

1917 CREATE TRIGGER "delete_non_detached_scope_with_session"

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

1919 "delete_non_detached_scope_with_session_trigger"();

1920

1921

1922 CREATE FUNCTION "delete_token_with_empty_scope_trigger"()

1923 RETURNS TRIGGER

1924 LANGUAGE 'plpgsql' VOLATILE AS $$

1925 BEGIN

1926 IF NEW."scope" = '' THEN

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

1928 END IF;

1929 RETURN NULL;

1930 END;

1931 $$;

1932

1933 CREATE TRIGGER "delete_token_with_empty_scope"

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

1935 "delete_token_with_empty_scope_trigger"();

1936

1937

1938 CREATE FUNCTION "delete_snapshot_on_partial_delete_trigger"()

1939 RETURNS TRIGGER

1940 LANGUAGE 'plpgsql' VOLATILE AS $$

1941 BEGIN

1942 IF TG_OP = 'UPDATE' THEN

1943 IF

1944 OLD."snapshot_id" = NEW."snapshot_id" AND

1945 OLD."issue_id" = NEW."issue_id"

1946 THEN

1947 RETURN NULL;

1948 END IF;

1949 END IF;

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

1951 RETURN NULL;

1952 END;

1953 $$;

1954

1955 CREATE TRIGGER "delete_snapshot_on_partial_delete"

1956 AFTER UPDATE OR DELETE ON "snapshot_issue"

1957 FOR EACH ROW EXECUTE PROCEDURE

1958 "delete_snapshot_on_partial_delete_trigger"();

1959

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

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

1962

1963

1964 CREATE FUNCTION "copy_current_draft_data"

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

1966 RETURNS VOID

1967 LANGUAGE 'plpgsql' VOLATILE AS $$

1968 BEGIN

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

1970 FOR UPDATE;

1971 UPDATE "initiative" SET

1972 "location" = "draft"."location",

1973 "draft_text_search_data" = "draft"."text_search_data"

1974 FROM "current_draft" AS "draft"

1975 WHERE "initiative"."id" = "initiative_id_p"

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

1977 END;

1978 $$;

1979

1980 COMMENT ON FUNCTION "copy_current_draft_data"

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

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

1983

1984

1985 CREATE FUNCTION "copy_current_draft_data_trigger"()

1986 RETURNS TRIGGER

1987 LANGUAGE 'plpgsql' VOLATILE AS $$

1988 BEGIN

1989 IF TG_OP='DELETE' THEN

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

1991 ELSE

1992 IF TG_OP='UPDATE' THEN

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

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

1995 END IF;

1996 END IF;

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

1998 END IF;

1999 RETURN NULL;

2000 END;

2001 $$;

2002

2003 CREATE TRIGGER "copy_current_draft_data"

2004 AFTER INSERT OR UPDATE OR DELETE ON "draft"

2005 FOR EACH ROW EXECUTE PROCEDURE

2006 "copy_current_draft_data_trigger"();

2007

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

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

2010

2011

2012 CREATE VIEW "area_quorum" AS

2013 SELECT

2014 "area"."id" AS "area_id",

2015 ceil(

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

2017 coalesce(

2018 ( SELECT sum(

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

2020 extract(epoch from

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

2022 "issue"."discussion_time" +

2023 "issue"."verification_time" +

2024 "issue"."voting_time"

2025 )::FLOAT8

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

2027 )

2028 FROM "issue" JOIN "policy"

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

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

2031 AND "issue"."accepted" NOTNULL

2032 AND "issue"."closed" ISNULL

2033 AND "policy"."polling" = FALSE

2034 )::FLOAT8, 0::FLOAT8

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

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

2037 SELECT "snapshot"."population"

2038 FROM "snapshot"

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

2040 AND "snapshot"."issue_id" ISNULL

2041 ORDER BY "snapshot"."id" DESC

2042 LIMIT 1

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

2044

2045 )::INT4 AS "issue_quorum"

2046 FROM "area";

2047

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

2049

2050

2051 CREATE VIEW "area_with_unaccepted_issues" AS

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

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

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

2055

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

2057

2058

2059 DROP VIEW "area_member_count";

2060

2061

2062 DROP TABLE "membership";

2063

2064

2065 DROP FUNCTION "membership_weight"

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

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

2068

2069

2070 DROP FUNCTION "membership_weight_with_skipping"

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

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

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

2074

2075

2076 CREATE OR REPLACE VIEW "issue_delegation" AS

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

2078 "issue"."id" AS "issue_id",

2079 "delegation"."id",

2080 "delegation"."truster_id",

2081 "delegation"."trustee_id",

2082 "delegation"."scope"

2083 FROM "issue"

2084 JOIN "area"

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

2086 JOIN "delegation"

2087 ON "delegation"."unit_id" = "area"."unit_id"

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

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

2090 JOIN "member"

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

2092 JOIN "privilege"

2093 ON "area"."unit_id" = "privilege"."unit_id"

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

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

2096 ORDER BY

2097 "issue"."id",

2098 "delegation"."truster_id",

2099 "delegation"."scope" DESC;

2100

2101

2102 CREATE VIEW "unit_member" AS

2103 SELECT

2104 "unit"."id" AS "unit_id",

2105 "member"."id" AS "member_id"

2106 FROM "privilege"

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

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

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

2110

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

2112

2113

2114 CREATE OR REPLACE VIEW "unit_member_count" AS

2115 SELECT

2116 "unit"."id" AS "unit_id",

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

2118 FROM "unit" LEFT JOIN "unit_member"

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

2120 GROUP BY "unit"."id";

2121

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

2123

2124

2125 CREATE OR REPLACE VIEW "opening_draft" AS

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

2127 ORDER BY "initiative_id", "id";

2128

2129

2130 CREATE OR REPLACE VIEW "current_draft" AS

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

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

2133

2134

2135 CREATE OR REPLACE VIEW "issue_supporter_in_admission_state" AS

2136 SELECT

2137 "area"."unit_id",

2138 "issue"."area_id",

2139 "issue"."id" AS "issue_id",

2140 "supporter"."member_id",

2141 "direct_interest_snapshot"."weight"

2142 FROM "issue"

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

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

2145 JOIN "direct_interest_snapshot"

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

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

2148 AND "direct_interest_snapshot"."member_id" = "supporter"."member_id"

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

2150

2151

2152 CREATE OR REPLACE VIEW "individual_suggestion_ranking" AS

2153 SELECT

2154 "opinion"."initiative_id",

2155 "opinion"."member_id",

2156 "direct_interest_snapshot"."weight",

2157 CASE WHEN

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

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

2160 THEN 1 ELSE

2161 CASE WHEN

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

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

2164 THEN 2 ELSE

2165 CASE WHEN

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

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

2168 THEN 3 ELSE 4 END

2169 END

2170 END AS "preference",

2171 "opinion"."suggestion_id"

2172 FROM "opinion"

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

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

2175 JOIN "direct_interest_snapshot"

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

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

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

2179

2180

2181 CREATE VIEW "expired_session" AS

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

2183

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

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

2186

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

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

2189

2190

2191 CREATE VIEW "expired_token" AS

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

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

2194 SELECT NULL FROM "token" AS "other"

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

2196

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

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

2199

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

2201

2202

2203 CREATE VIEW "unused_snapshot" AS

2204 SELECT "snapshot".* FROM "snapshot"

2205 LEFT JOIN "issue"

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

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

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

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

2210 WHERE "issue"."id" ISNULL;

2211

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

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

2214

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

2216

2217

2218 CREATE VIEW "expired_snapshot" AS

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

2220 WHERE "unused_snapshot"."calculated" <

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

2222

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

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

2225

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

2227

2228

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

2230

2231

2232 CREATE OR REPLACE FUNCTION "delegation_chain"

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

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

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

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

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

2238 "simulate_default_p" BOOLEAN DEFAULT FALSE )

2239 RETURNS SETOF "delegation_chain_row"

2240 LANGUAGE 'plpgsql' STABLE AS $$

2241 DECLARE

2242 "scope_v" "delegation_scope";

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

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

2245 "issue_row" "issue"%ROWTYPE;

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

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

2248 "output_row" "delegation_chain_row";

2249 "output_rows" "delegation_chain_row"[];

2250 "simulate_v" BOOLEAN;

2251 "simulate_here_v" BOOLEAN;

2252 "delegation_row" "delegation"%ROWTYPE;

2253 "row_count" INT4;

2254 "i" INT4;

2255 "loop_v" BOOLEAN;

2256 BEGIN

2257 IF "simulate_trustee_id_p" NOTNULL AND "simulate_default_p" THEN

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

2259 END IF;

2260 IF "simulate_trustee_id_p" NOTNULL OR "simulate_default_p" THEN

2261 "simulate_v" := TRUE;

2262 ELSE

2263 "simulate_v" := FALSE;

2264 END IF;

2265 IF

2266 "unit_id_p" NOTNULL AND

2267 "area_id_p" ISNULL AND

2268 "issue_id_p" ISNULL

2269 THEN

2270 "scope_v" := 'unit';

2271 "unit_id_v" := "unit_id_p";

2272 ELSIF

2273 "unit_id_p" ISNULL AND

2274 "area_id_p" NOTNULL AND

2275 "issue_id_p" ISNULL

2276 THEN

2277 "scope_v" := 'area';

2278 "area_id_v" := "area_id_p";

2279 SELECT "unit_id" INTO "unit_id_v"

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

2281 ELSIF

2282 "unit_id_p" ISNULL AND

2283 "area_id_p" ISNULL AND

2284 "issue_id_p" NOTNULL

2285 THEN

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

2287 IF "issue_row"."id" ISNULL THEN

2288 RETURN;

2289 END IF;

2290 IF "issue_row"."closed" NOTNULL THEN

2291 IF "simulate_v" THEN

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

2293 END IF;

2294 FOR "output_row" IN

2295 SELECT * FROM

2296 "delegation_chain_for_closed_issue"("member_id_p", "issue_id_p")

2297 LOOP

2298 RETURN NEXT "output_row";

2299 END LOOP;

2300 RETURN;

2301 END IF;

2302 "scope_v" := 'issue';

2303 SELECT "area_id" INTO "area_id_v"

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

2305 SELECT "unit_id" INTO "unit_id_v"

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

2307 ELSE

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

2309 END IF;

2310 "visited_member_ids" := '{}';

2311 "loop_member_id_v" := NULL;

2312 "output_rows" := '{}';

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

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

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

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

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

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

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

2320 LOOP

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

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

2323 ELSE

2324 "visited_member_ids" :=

2325 "visited_member_ids" || "output_row"."member_id";

2326 END IF;

2327 IF "output_row"."participation" ISNULL THEN

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

2329 ELSIF "output_row"."participation" THEN

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

2331 END IF;

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

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

2334 SELECT NULL FROM "member" JOIN "privilege"

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

2336 AND "privilege"."unit_id" = "unit_id_v"

2337 WHERE "id" = "output_row"."member_id"

2338 AND "member"."active" AND "privilege"."voting_right"

2339 );

2340 "simulate_here_v" := (

2341 "simulate_v" AND

2342 "output_row"."member_id" = "member_id_p"

2343 );

2344 "delegation_row" := ROW(NULL);

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

2346 IF "scope_v" = 'unit' THEN

2347 IF NOT "simulate_here_v" THEN

2348 SELECT * INTO "delegation_row" FROM "delegation"

2349 WHERE "truster_id" = "output_row"."member_id"

2350 AND "unit_id" = "unit_id_v";

2351 END IF;

2352 ELSIF "scope_v" = 'area' THEN

2353 IF "simulate_here_v" THEN

2354 IF "simulate_trustee_id_p" ISNULL THEN

2355 SELECT * INTO "delegation_row" FROM "delegation"

2356 WHERE "truster_id" = "output_row"."member_id"

2357 AND "unit_id" = "unit_id_v";

2358 END IF;

2359 ELSE

2360 SELECT * INTO "delegation_row" FROM "delegation"

2361 WHERE "truster_id" = "output_row"."member_id"

2362 AND (

2363 "unit_id" = "unit_id_v" OR

2364 "area_id" = "area_id_v"

2365 )

2366 ORDER BY "scope" DESC;

2367 END IF;

2368 ELSIF "scope_v" = 'issue' THEN

2369 IF "issue_row"."fully_frozen" ISNULL THEN

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

2371 SELECT NULL FROM "interest"

2372 WHERE "issue_id" = "issue_id_p"

2373 AND "member_id" = "output_row"."member_id"

2374 );

2375 ELSE

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

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

2378 SELECT NULL FROM "direct_voter"

2379 WHERE "issue_id" = "issue_id_p"

2380 AND "member_id" = "output_row"."member_id"

2381 );

2382 ELSE

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

2384 END IF;

2385 END IF;

2386 IF "simulate_here_v" THEN

2387 IF "simulate_trustee_id_p" ISNULL THEN

2388 SELECT * INTO "delegation_row" FROM "delegation"

2389 WHERE "truster_id" = "output_row"."member_id"

2390 AND (

2391 "unit_id" = "unit_id_v" OR

2392 "area_id" = "area_id_v"

2393 )

2394 ORDER BY "scope" DESC;

2395 END IF;

2396 ELSE

2397 SELECT * INTO "delegation_row" FROM "delegation"

2398 WHERE "truster_id" = "output_row"."member_id"

2399 AND (

2400 "unit_id" = "unit_id_v" OR

2401 "area_id" = "area_id_v" OR

2402 "issue_id" = "issue_id_p"

2403 )

2404 ORDER BY "scope" DESC;

2405 END IF;

2406 END IF;

2407 ELSE

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

2409 END IF;

2410 IF "simulate_here_v" AND "simulate_trustee_id_p" NOTNULL THEN

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

2412 "output_rows" := "output_rows" || "output_row";

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

2414 ELSIF "delegation_row"."trustee_id" NOTNULL THEN

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

2416 "output_rows" := "output_rows" || "output_row";

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

2418 ELSIF "delegation_row"."scope" NOTNULL THEN

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

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

2421 "output_rows" := "output_rows" || "output_row";

2422 EXIT;

2423 ELSE

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

2425 "output_rows" := "output_rows" || "output_row";

2426 EXIT;

2427 END IF;

2428 EXIT WHEN "loop_member_id_v" NOTNULL;

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

2430 END LOOP;

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

2432 "i" := 1;

2433 "loop_v" := FALSE;

2434 LOOP

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

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

2437 IF "loop_v" THEN

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

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

2440 ELSIF "i" = "row_count" THEN

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

2442 ELSE

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

2444 END IF;

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

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

2447 "loop_v" := TRUE;

2448 END IF;

2449 IF "scope_v" = 'unit' THEN

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

2451 END IF;

2452 RETURN NEXT "output_row";

2453 "i" := "i" + 1;

2454 END LOOP;

2455 RETURN;

2456 END;

2457 $$;

2458

2459

2460 CREATE OR REPLACE FUNCTION "get_initiatives_for_notification"

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

2462 RETURNS SETOF "initiative_for_notification"

2463 LANGUAGE 'plpgsql' VOLATILE AS $$

2464 DECLARE

2465 "result_row" "initiative_for_notification"%ROWTYPE;

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

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

2468 BEGIN

2469 PERFORM "require_transaction_isolation"();

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

2471 FOR "result_row" IN

2472 SELECT * FROM "initiative_for_notification"

2473 WHERE "recipient_id" = "recipient_id_p"

2474 LOOP

2475 SELECT "id" INTO "last_draft_id_v" FROM "draft"

2476 WHERE "draft"."initiative_id" = "result_row"."initiative_id"

2477 ORDER BY "id" DESC LIMIT 1;

2478 SELECT "id" INTO "last_suggestion_id_v" FROM "suggestion"

2479 WHERE "suggestion"."initiative_id" = "result_row"."initiative_id"

2480 ORDER BY "id" DESC LIMIT 1;

2481 INSERT INTO "notification_initiative_sent"

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

2483 VALUES (

2484 "recipient_id_p",

2485 "result_row"."initiative_id",

2486 "last_draft_id_v",

2487 "last_suggestion_id_v" )

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

2489 "last_draft_id" = "last_draft_id_v",

2490 "last_suggestion_id" = "last_suggestion_id_v";

2491 RETURN NEXT "result_row";

2492 END LOOP;

2493 DELETE FROM "notification_initiative_sent"

2494 USING "initiative", "issue"

2495 WHERE "notification_initiative_sent"."member_id" = "recipient_id_p"

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

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

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

2499 UPDATE "member" SET

2500 "notification_counter" = "notification_counter" + 1,

2501 "notification_sent" = now()

2502 WHERE "id" = "recipient_id_p";

2503 RETURN;

2504 END;

2505 $$;

2506

2507

2508 CREATE OR REPLACE FUNCTION "calculate_member_counts"()

2509 RETURNS VOID

2510 LANGUAGE 'plpgsql' VOLATILE AS $$

2511 BEGIN

2512 PERFORM "require_transaction_isolation"();

2513 DELETE FROM "member_count";

2514 INSERT INTO "member_count" ("total_count")

2515 SELECT "total_count" FROM "member_count_view";

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

2517 FROM "unit_member_count" AS "view"

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

2519 RETURN;

2520 END;

2521 $$;

2522

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

2524

2525

2526 CREATE FUNCTION "calculate_area_quorum"()

2527 RETURNS VOID

2528 LANGUAGE 'plpgsql' VOLATILE AS $$

2529 BEGIN

2530 PERFORM "dont_require_transaction_isolation"();

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

2532 FROM "area_quorum" AS "view"

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

2534 RETURN;

2535 END;

2536 $$;

2537

2538 COMMENT ON FUNCTION "calculate_area_quorum"() IS 'Calculate column "issue_quorum" in table "area" from view "area_quorum"';

2539

2540

2541 DROP VIEW "remaining_harmonic_initiative_weight_summands";

2542 DROP VIEW "remaining_harmonic_supporter_weight";

2543

2544

2545 CREATE VIEW "remaining_harmonic_supporter_weight" AS

2546 SELECT

2547 "direct_interest_snapshot"."snapshot_id",

2548 "direct_interest_snapshot"."issue_id",

2549 "direct_interest_snapshot"."member_id",

2550 "direct_interest_snapshot"."weight" AS "weight_num",

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

2552 FROM "issue"

2553 JOIN "direct_interest_snapshot"

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

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

2556 JOIN "initiative"

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

2558 AND "initiative"."harmonic_weight" ISNULL

2559 JOIN "direct_supporter_snapshot"

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

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

2562 AND "direct_interest_snapshot"."member_id" = "direct_supporter_snapshot"."member_id"

2563 AND (

2564 "direct_supporter_snapshot"."satisfied" = TRUE OR

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

2566 )

2567 GROUP BY

2568 "direct_interest_snapshot"."snapshot_id",

2569 "direct_interest_snapshot"."issue_id",

2570 "direct_interest_snapshot"."member_id",

2571 "direct_interest_snapshot"."weight";

2572

2573

2574 CREATE VIEW "remaining_harmonic_initiative_weight_summands" AS

2575 SELECT

2576 "initiative"."issue_id",

2577 "initiative"."id" AS "initiative_id",

2578 "initiative"."admitted",

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

2580 "remaining_harmonic_supporter_weight"."weight_den"

2581 FROM "remaining_harmonic_supporter_weight"

2582 JOIN "initiative"

2583 ON "remaining_harmonic_supporter_weight"."issue_id" = "initiative"."issue_id"

2584 AND "initiative"."harmonic_weight" ISNULL

2585 JOIN "direct_supporter_snapshot"

2586 ON "remaining_harmonic_supporter_weight"."snapshot_id" = "direct_supporter_snapshot"."snapshot_id"

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

2588 AND "remaining_harmonic_supporter_weight"."member_id" = "direct_supporter_snapshot"."member_id"

2589 AND (

2590 "direct_supporter_snapshot"."satisfied" = TRUE OR

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

2592 )

2593 GROUP BY

2594 "initiative"."issue_id",

2595 "initiative"."id",

2596 "initiative"."admitted",

2597 "remaining_harmonic_supporter_weight"."weight_den";

2598

2599

2600 DROP FUNCTION "create_population_snapshot"

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

2602

2603

2604 DROP FUNCTION "weight_of_added_delegations_for_population_snapshot"

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

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

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

2608

2609

2610 DROP FUNCTION "weight_of_added_delegations_for_interest_snapshot"

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

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

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

2614

2615

2616 CREATE FUNCTION "weight_of_added_delegations_for_snapshot"

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

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

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

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

2621 RETURNS "direct_interest_snapshot"."weight"%TYPE

2622 LANGUAGE 'plpgsql' VOLATILE AS $$

2623 DECLARE

2624 "issue_delegation_row" "issue_delegation"%ROWTYPE;

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

2626 "weight_v" INT4;

2627 "sub_weight_v" INT4;

2628 BEGIN

2629 PERFORM "require_transaction_isolation"();

2630 "weight_v" := 0;

2631 FOR "issue_delegation_row" IN

2632 SELECT * FROM "issue_delegation"

2633 WHERE "trustee_id" = "member_id_p"

2634 AND "issue_id" = "issue_id_p"

2635 LOOP

2636 IF NOT EXISTS (

2637 SELECT NULL FROM "direct_interest_snapshot"

2638 WHERE "snapshot_id" = "snapshot_id_p"

2639 AND "issue_id" = "issue_id_p"

2640 AND "member_id" = "issue_delegation_row"."truster_id"

2641 ) AND NOT EXISTS (

2642 SELECT NULL FROM "delegating_interest_snapshot"

2643 WHERE "snapshot_id" = "snapshot_id_p"

2644 AND "issue_id" = "issue_id_p"

2645 AND "member_id" = "issue_delegation_row"."truster_id"

2646 ) THEN

2647 "delegate_member_ids_v" :=

2648 "member_id_p" || "delegate_member_ids_p";

2649 INSERT INTO "delegating_interest_snapshot" (

2650 "snapshot_id",

2651 "issue_id",

2652 "member_id",

2653 "scope",

2654 "delegate_member_ids"

2655 ) VALUES (

2656 "snapshot_id_p",

2657 "issue_id_p",

2658 "issue_delegation_row"."truster_id",

2659 "issue_delegation_row"."scope",

2660 "delegate_member_ids_v"

2661 );

2662 "sub_weight_v" := 1 +

2663 "weight_of_added_delegations_for_snapshot"(

2664 "snapshot_id_p",

2665 "issue_id_p",

2666 "issue_delegation_row"."truster_id",

2667 "delegate_member_ids_v"

2668 );

2669 UPDATE "delegating_interest_snapshot"

2670 SET "weight" = "sub_weight_v"

2671 WHERE "snapshot_id" = "snapshot_id_p"

2672 AND "issue_id" = "issue_id_p"

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

2674 "weight_v" := "weight_v" + "sub_weight_v";

2675 END IF;

2676 END LOOP;

2677 RETURN "weight_v";

2678 END;

2679 $$;

2680

2681 COMMENT ON FUNCTION "weight_of_added_delegations_for_snapshot"

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

2683 "issue"."id"%TYPE,

2684 "member"."id"%TYPE,

2685 "delegating_interest_snapshot"."delegate_member_ids"%TYPE )

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

2687

2688

2689 DROP FUNCTION "create_interest_snapshot"

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

2691

2692

2693 DROP FUNCTION "create_snapshot"

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

2695

2696

2697 CREATE FUNCTION "take_snapshot"

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

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

2700 RETURNS "snapshot"."id"%TYPE

2701 LANGUAGE 'plpgsql' VOLATILE AS $$

2702 DECLARE

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

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

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

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

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

2708 BEGIN

2709 IF "issue_id_p" NOTNULL AND "area_id_p" NOTNULL THEN

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

2711 END IF;

2712 PERFORM "require_transaction_isolation"();

2713 IF "issue_id_p" ISNULL THEN

2714 "area_id_v" := "area_id_p";

2715 ELSE

2716 SELECT "area_id" INTO "area_id_v"

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

2718 END IF;

2719 SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_p";

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

2721 VALUES ("area_id_v", "issue_id_p")

2722 RETURNING "id" INTO "snapshot_id_v";

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

2724 SELECT "snapshot_id_v", "member_id"

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

2726 UPDATE "snapshot" SET

2727 "population" = (

2728 SELECT count(1) FROM "snapshot_population"

2729 WHERE "snapshot_id" = "snapshot_id_v"

2730 ) WHERE "id" = "snapshot_id_v";

2731 FOR "issue_id_v" IN

2732 SELECT "id" FROM "issue"

2733 WHERE CASE WHEN "issue_id_p" ISNULL THEN

2734 "area_id" = "area_id_p" AND

2735 "state" = 'admission'

2736 ELSE

2737 "id" = "issue_id_p"

2738 END

2739 LOOP

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

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

2742 INSERT INTO "direct_interest_snapshot"

2743 ("snapshot_id", "issue_id", "member_id")

2744 SELECT

2745 "snapshot_id_v" AS "snapshot_id",

2746 "issue_id_v" AS "issue_id",

2747 "member"."id" AS "member_id"

2748 FROM "issue"

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

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

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

2752 JOIN "privilege"

2753 ON "privilege"."unit_id" = "area"."unit_id"

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

2755 WHERE "issue"."id" = "issue_id_v"

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

2757 FOR "member_id_v" IN

2758 SELECT "member_id" FROM "direct_interest_snapshot"

2759 WHERE "snapshot_id" = "snapshot_id_v"

2760 AND "issue_id" = "issue_id_v"

2761 LOOP

2762 UPDATE "direct_interest_snapshot" SET

2763 "weight" = 1 +

2764 "weight_of_added_delegations_for_snapshot"(

2765 "snapshot_id_v",

2766 "issue_id_v",

2767 "member_id_v",

2768 '{}'

2769 )

2770 WHERE "snapshot_id" = "snapshot_id_v"

2771 AND "issue_id" = "issue_id_v"

2772 AND "member_id" = "member_id_v";

2773 END LOOP;

2774 INSERT INTO "direct_supporter_snapshot"

2775 ( "snapshot_id", "issue_id", "initiative_id", "member_id",

2776 "draft_id", "informed", "satisfied" )

2777 SELECT

2778 "snapshot_id_v" AS "snapshot_id",

2779 "issue_id_v" AS "issue_id",

2780 "initiative"."id" AS "initiative_id",

2781 "supporter"."member_id" AS "member_id",

2782 "supporter"."draft_id" AS "draft_id",

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

2784 NOT EXISTS (

2785 SELECT NULL FROM "critical_opinion"

2786 WHERE "initiative_id" = "initiative"."id"

2787 AND "member_id" = "supporter"."member_id"

2788 ) AS "satisfied"

2789 FROM "initiative"

2790 JOIN "supporter"

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

2792 JOIN "current_draft"

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

2794 JOIN "direct_interest_snapshot"

2795 ON "snapshot_id_v" = "direct_interest_snapshot"."snapshot_id"

2796 AND "supporter"."member_id" = "direct_interest_snapshot"."member_id"

2797 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"

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

2799 DELETE FROM "temporary_suggestion_counts";

2800 INSERT INTO "temporary_suggestion_counts"

2801 ( "id",

2802 "minus2_unfulfilled_count", "minus2_fulfilled_count",

2803 "minus1_unfulfilled_count", "minus1_fulfilled_count",

2804 "plus1_unfulfilled_count", "plus1_fulfilled_count",

2805 "plus2_unfulfilled_count", "plus2_fulfilled_count" )

2806 SELECT

2807 "suggestion"."id",

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

2809 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"

2810 ON "di"."snapshot_id" = "snapshot_id_v"

2811 AND "di"."issue_id" = "issue_id_v"

2812 AND "di"."member_id" = "opinion"."member_id"

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

2814 AND "opinion"."degree" = -2

2815 AND "opinion"."fulfilled" = FALSE

2816 ) AS "minus2_unfulfilled_count",

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

2818 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"

2819 ON "di"."snapshot_id" = "snapshot_id_v"

2820 AND "di"."issue_id" = "issue_id_v"

2821 AND "di"."member_id" = "opinion"."member_id"

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

2823 AND "opinion"."degree" = -2

2824 AND "opinion"."fulfilled" = TRUE

2825 ) AS "minus2_fulfilled_count",

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

2827 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"

2828 ON "di"."snapshot_id" = "snapshot_id_v"

2829 AND "di"."issue_id" = "issue_id_v"

2830 AND "di"."member_id" = "opinion"."member_id"

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

2832 AND "opinion"."degree" = -1

2833 AND "opinion"."fulfilled" = FALSE

2834 ) AS "minus1_unfulfilled_count",

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

2836 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"

2837 ON "di"."snapshot_id" = "snapshot_id_v"

2838 AND "di"."issue_id" = "issue_id_v"

2839 AND "di"."member_id" = "opinion"."member_id"

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

2841 AND "opinion"."degree" = -1

2842 AND "opinion"."fulfilled" = TRUE

2843 ) AS "minus1_fulfilled_count",

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

2845 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"

2846 ON "di"."snapshot_id" = "snapshot_id_v"

2847 AND "di"."issue_id" = "issue_id_v"

2848 AND "di"."member_id" = "opinion"."member_id"

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

2850 AND "opinion"."degree" = 1

2851 AND "opinion"."fulfilled" = FALSE

2852 ) AS "plus1_unfulfilled_count",

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

2854 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"

2855 ON "di"."snapshot_id" = "snapshot_id_v"

2856 AND "di"."issue_id" = "issue_id_v"

2857 AND "di"."member_id" = "opinion"."member_id"

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

2859 AND "opinion"."degree" = 1

2860 AND "opinion"."fulfilled" = TRUE

2861 ) AS "plus1_fulfilled_count",

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

2863 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"

2864 ON "di"."snapshot_id" = "snapshot_id_v"

2865 AND "di"."issue_id" = "issue_id_v"

2866 AND "di"."member_id" = "opinion"."member_id"

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

2868 AND "opinion"."degree" = 2

2869 AND "opinion"."fulfilled" = FALSE

2870 ) AS "plus2_unfulfilled_count",

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

2872 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"

2873 ON "di"."snapshot_id" = "snapshot_id_v"

2874 AND "di"."issue_id" = "issue_id_v"

2875 AND "di"."member_id" = "opinion"."member_id"

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

2877 AND "opinion"."degree" = 2

2878 AND "opinion"."fulfilled" = TRUE

2879 ) AS "plus2_fulfilled_count"

2880 FROM "suggestion" JOIN "initiative"

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

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

2883 END LOOP;

2884 RETURN "snapshot_id_v";

2885 END;

2886 $$;

2887

2888 COMMENT ON FUNCTION "take_snapshot"

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

2890 "area"."id"%TYPE )

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

2892

2893

2894 DROP FUNCTION "set_snapshot_event"

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

2896 "event_p" "snapshot_event" );

2897

2898

2899 CREATE FUNCTION "finish_snapshot"

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

2901 RETURNS VOID

2902 LANGUAGE 'plpgsql' VOLATILE AS $$

2903 DECLARE

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

2905 BEGIN

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

2907 -- "dont_require_snapshot_isolation" here because this function is

2908 -- also invoked by "check_issue"

2909 LOCK TABLE "snapshot" IN EXCLUSIVE MODE;

2910 SELECT "id" INTO "snapshot_id_v" FROM "snapshot"

2911 ORDER BY "id" DESC LIMIT 1;

2912 UPDATE "issue" SET

2913 "calculated" = "snapshot"."calculated",

2914 "latest_snapshot_id" = "snapshot_id_v",

2915 "population" = "snapshot"."population"

2916 FROM "snapshot"

2917 WHERE "issue"."id" = "issue_id_p"

2918 AND "snapshot"."id" = "snapshot_id_v";

2919 UPDATE "initiative" SET

2920 "supporter_count" = (

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

2922 FROM "direct_interest_snapshot" AS "di"

2923 JOIN "direct_supporter_snapshot" AS "ds"

2924 ON "di"."member_id" = "ds"."member_id"

2925 WHERE "di"."snapshot_id" = "snapshot_id_v"

2926 AND "di"."issue_id" = "issue_id_p"

2927 AND "ds"."snapshot_id" = "snapshot_id_v"

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

2929 ),

2930 "informed_supporter_count" = (

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

2932 FROM "direct_interest_snapshot" AS "di"

2933 JOIN "direct_supporter_snapshot" AS "ds"

2934 ON "di"."member_id" = "ds"."member_id"

2935 WHERE "di"."snapshot_id" = "snapshot_id_v"

2936 AND "di"."issue_id" = "issue_id_p"

2937 AND "ds"."snapshot_id" = "snapshot_id_v"

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

2939 AND "ds"."informed"

2940 ),

2941 "satisfied_supporter_count" = (

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

2943 FROM "direct_interest_snapshot" AS "di"

2944 JOIN "direct_supporter_snapshot" AS "ds"

2945 ON "di"."member_id" = "ds"."member_id"

2946 WHERE "di"."snapshot_id" = "snapshot_id_v"

2947 AND "di"."issue_id" = "issue_id_p"

2948 AND "ds"."snapshot_id" = "snapshot_id_v"

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

2950 AND "ds"."satisfied"

2951 ),

2952 "satisfied_informed_supporter_count" = (

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

2954 FROM "direct_interest_snapshot" AS "di"

2955 JOIN "direct_supporter_snapshot" AS "ds"

2956 ON "di"."member_id" = "ds"."member_id"

2957 WHERE "di"."snapshot_id" = "snapshot_id_v"

2958 AND "di"."issue_id" = "issue_id_p"

2959 AND "ds"."snapshot_id" = "snapshot_id_v"

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

2961 AND "ds"."informed"

2962 AND "ds"."satisfied"

2963 )

2964 WHERE "issue_id" = "issue_id_p";

2965 UPDATE "suggestion" SET

2966 "minus2_unfulfilled_count" = "temp"."minus2_unfulfilled_count",

2967 "minus2_fulfilled_count" = "temp"."minus2_fulfilled_count",

2968 "minus1_unfulfilled_count" = "temp"."minus1_unfulfilled_count",

2969 "minus1_fulfilled_count" = "temp"."minus1_fulfilled_count",

2970 "plus1_unfulfilled_count" = "temp"."plus1_unfulfilled_count",

2971 "plus1_fulfilled_count" = "temp"."plus1_fulfilled_count",

2972 "plus2_unfulfilled_count" = "temp"."plus2_unfulfilled_count",

2973 "plus2_fulfilled_count" = "temp"."plus2_fulfilled_count"

2974 FROM "temporary_suggestion_counts" AS "temp", "initiative"

2975 WHERE "temp"."id" = "suggestion"."id"

2976 AND "initiative"."issue_id" = "issue_id_p"

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

2978 DELETE FROM "temporary_suggestion_counts";

2979 RETURN;

2980 END;

2981 $$;

2982

2983 COMMENT ON FUNCTION "finish_snapshot"

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

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

2986

2987

2988 CREATE FUNCTION "issue_admission"

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

2990 RETURNS BOOLEAN

2991 LANGUAGE 'plpgsql' VOLATILE AS $$

2992 DECLARE

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

2994 BEGIN

2995 PERFORM "dont_require_transaction_isolation"();

2996 LOCK TABLE "snapshot" IN EXCLUSIVE MODE;

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

2998 FROM "area_quorum" AS "view"

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

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

3001 SELECT "id" INTO "issue_id_v" FROM "issue_for_admission"

3002 WHERE "area_id" = "area_id_p";

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

3004 UPDATE "issue" SET

3005 "admission_snapshot_id" = "latest_snapshot_id",

3006 "state" = 'discussion',

3007 "accepted" = now(),

3008 "phase_finished" = NULL

3009 WHERE "id" = "issue_id_v";

3010 RETURN TRUE;

3011 END;

3012 $$;

3013

3014 COMMENT ON FUNCTION "issue_admission"

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

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

3017

3018

3019 CREATE OR REPLACE FUNCTION "check_issue"

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

3021 "persist" "check_issue_persistence" )

3022 RETURNS "check_issue_persistence"

3023 LANGUAGE 'plpgsql' VOLATILE AS $$

3024 DECLARE

3025 "issue_row" "issue"%ROWTYPE;

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

3027 "policy_row" "policy"%ROWTYPE;

3028 "initiative_row" "initiative"%ROWTYPE;

3029 "state_v" "issue_state";

3030 BEGIN

3031 PERFORM "require_transaction_isolation"();

3032 IF "persist" ISNULL THEN

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

3034 FOR UPDATE;

3035 SELECT "calculated" INTO "last_calculated_v"

3036 FROM "snapshot" JOIN "snapshot_issue"

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

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

3039 IF "issue_row"."closed" NOTNULL THEN

3040 RETURN NULL;

3041 END IF;

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

3043 IF

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

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

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

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

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

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

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

3051 "issue_row"."fully_frozen" + "issue_row"."voting_time" )

3052 THEN

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

3054 ELSE

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

3056 END IF;

3057 IF

3058 NOT EXISTS (

3059 -- all initiatives are revoked

3060 SELECT NULL FROM "initiative"

3061 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL

3062 ) AND (

3063 -- and issue has not been accepted yet

3064 "persist"."state" = 'admission' OR

3065 -- or verification time has elapsed

3066 ( "persist"."state" = 'verification' AND

3067 "persist"."phase_finished" ) OR

3068 -- or no initiatives have been revoked lately

3069 NOT EXISTS (

3070 SELECT NULL FROM "initiative"

3071 WHERE "issue_id" = "issue_id_p"

3072 AND now() < "revoked" + "issue_row"."verification_time"

3073 )

3074 )

3075 THEN

3076 "persist"."issue_revoked" := TRUE;

3077 ELSE

3078 "persist"."issue_revoked" := FALSE;

3079 END IF;

3080 IF "persist"."phase_finished" OR "persist"."issue_revoked" THEN

3081 UPDATE "issue" SET "phase_finished" = now()

3082 WHERE "id" = "issue_row"."id";

3083 RETURN "persist";

3084 ELSIF

3085 "persist"."state" IN ('admission', 'discussion', 'verification')

3086 THEN

3087 RETURN "persist";

3088 ELSE

3089 RETURN NULL;

3090 END IF;

3091 END IF;

3092 IF

3093 "persist"."state" IN ('admission', 'discussion', 'verification') AND

3094 coalesce("persist"."snapshot_created", FALSE) = FALSE

3095 THEN

3096 IF "persist"."state" != 'admission' THEN

3097 PERFORM "take_snapshot"("issue_id_p");

3098 PERFORM "finish_snapshot"("issue_id_p");

3099 END IF;

3100 "persist"."snapshot_created" = TRUE;

3101 IF "persist"."phase_finished" THEN

3102 IF "persist"."state" = 'admission' THEN

3103 UPDATE "issue" SET "admission_snapshot_id" = "latest_snapshot_id";

3104 ELSIF "persist"."state" = 'discussion' THEN

3105 UPDATE "issue" SET "half_freeze_snapshot_id" = "latest_snapshot_id";

3106 ELSIF "persist"."state" = 'verification' THEN

3107 UPDATE "issue" SET "full_freeze_snapshot_id" = "latest_snapshot_id";

3108 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";

3109 SELECT * INTO "policy_row" FROM "policy"

3110 WHERE "id" = "issue_row"."policy_id";

3111 FOR "initiative_row" IN

3112 SELECT * FROM "initiative"

3113 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL

3114 FOR UPDATE

3115 LOOP

3116 IF

3117 "initiative_row"."polling" OR (

3118 "initiative_row"."satisfied_supporter_count" >

3119 "policy_row"."initiative_quorum" AND

3120 "initiative_row"."satisfied_supporter_count" *

3121 "policy_row"."initiative_quorum_den" >=

3122 "issue_row"."population" * "policy_row"."initiative_quorum_num"

3123 )

3124 THEN

3125 UPDATE "initiative" SET "admitted" = TRUE

3126 WHERE "id" = "initiative_row"."id";

3127 ELSE

3128 UPDATE "initiative" SET "admitted" = FALSE

3129 WHERE "id" = "initiative_row"."id";

3130 END IF;

3131 END LOOP;

3132 END IF;

3133 END IF;

3134 RETURN "persist";

3135 END IF;

3136 IF

3137 "persist"."state" IN ('admission', 'discussion', 'verification') AND

3138 coalesce("persist"."harmonic_weights_set", FALSE) = FALSE

3139 THEN

3140 PERFORM "set_harmonic_initiative_weights"("issue_id_p");

3141 "persist"."harmonic_weights_set" = TRUE;

3142 IF

3143 "persist"."phase_finished" OR

3144 "persist"."issue_revoked" OR

3145 "persist"."state" = 'admission'

3146 THEN

3147 RETURN "persist";

3148 ELSE

3149 RETURN NULL;

3150 END IF;

3151 END IF;

3152 IF "persist"."issue_revoked" THEN

3153 IF "persist"."state" = 'admission' THEN

3154 "state_v" := 'canceled_revoked_before_accepted';

3155 ELSIF "persist"."state" = 'discussion' THEN

3156 "state_v" := 'canceled_after_revocation_during_discussion';

3157 ELSIF "persist"."state" = 'verification' THEN

3158 "state_v" := 'canceled_after_revocation_during_verification';

3159 END IF;

3160 UPDATE "issue" SET

3161 "state" = "state_v",

3162 "closed" = "phase_finished",

3163 "phase_finished" = NULL

3164 WHERE "id" = "issue_id_p";

3165 RETURN NULL;

3166 END IF;

3167 IF "persist"."state" = 'admission' THEN

3168 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"

3169 FOR UPDATE;

3170 IF "issue_row"."phase_finished" NOTNULL THEN

3171 UPDATE "issue" SET

3172 "state" = 'canceled_issue_not_accepted',

3173 "closed" = "phase_finished",

3174 "phase_finished" = NULL

3175 WHERE "id" = "issue_id_p";

3176 END IF;

3177 RETURN NULL;

3178 END IF;

3179 IF "persist"."phase_finished" THEN

3180 IF "persist"."state" = 'discussion' THEN

3181 UPDATE "issue" SET

3182 "state" = 'verification',

3183 "half_frozen" = "phase_finished",

3184 "phase_finished" = NULL

3185 WHERE "id" = "issue_id_p";

3186 RETURN NULL;

3187 END IF;

3188 IF "persist"."state" = 'verification' THEN

3189 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"

3190 FOR UPDATE;

3191 SELECT * INTO "policy_row" FROM "policy"

3192 WHERE "id" = "issue_row"."policy_id";

3193 IF EXISTS (

3194 SELECT NULL FROM "initiative"

3195 WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE

3196 ) THEN

3197 UPDATE "issue" SET

3198 "state" = 'voting',

3199 "fully_frozen" = "phase_finished",

3200 "phase_finished" = NULL

3201 WHERE "id" = "issue_id_p";

3202 ELSE

3203 UPDATE "issue" SET

3204 "state" = 'canceled_no_initiative_admitted',

3205 "fully_frozen" = "phase_finished",

3206 "closed" = "phase_finished",

3207 "phase_finished" = NULL

3208 WHERE "id" = "issue_id_p";

3209 -- NOTE: The following DELETE statements have effect only when

3210 -- issue state has been manipulated

3211 DELETE FROM "direct_voter" WHERE "issue_id" = "issue_id_p";

3212 DELETE FROM "delegating_voter" WHERE "issue_id" = "issue_id_p";

3213 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";

3214 END IF;

3215 RETURN NULL;

3216 END IF;

3217 IF "persist"."state" = 'voting' THEN

3218 IF coalesce("persist"."closed_voting", FALSE) = FALSE THEN

3219 PERFORM "close_voting"("issue_id_p");

3220 "persist"."closed_voting" = TRUE;

3221 RETURN "persist";

3222 END IF;

3223 PERFORM "calculate_ranks"("issue_id_p");

3224 RETURN NULL;

3225 END IF;

3226 END IF;

3227 RAISE WARNING 'should not happen';

3228 RETURN NULL;

3229 END;

3230 $$;

3231

3232

3233 CREATE OR REPLACE FUNCTION "check_everything"()

3234 RETURNS VOID

3235 LANGUAGE 'plpgsql' VOLATILE AS $$

3236 DECLARE

3237 "area_id_v" "area"."id"%TYPE;

3238 "snapshot_id_v" "snapshot"."id"%TYPE;

3239 "issue_id_v" "issue"."id"%TYPE;

3240 "persist_v" "check_issue_persistence";

3241 BEGIN

3242 RAISE WARNING 'Function "check_everything" should only be used for development and debugging purposes';

3243 DELETE FROM "expired_session";

3244 DELETE FROM "expired_token";

3245 DELETE FROM "expired_snapshot";

3246 PERFORM "check_activity"();

3247 PERFORM "calculate_member_counts"();

3248 FOR "area_id_v" IN SELECT "id" FROM "area_with_unaccepted_issues" LOOP

3249 SELECT "take_snapshot"(NULL, "area_id_v") INTO "snapshot_id_v";

3250 PERFORM "finish_snapshot"("issue_id") FROM "snapshot_issue"

3251 WHERE "snapshot_id" = "snapshot_id_v";

3252 LOOP

3253 EXIT WHEN "issue_admission"("area_id_v") = FALSE;

3254 END LOOP;

3255 END LOOP;

3256 FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP

3257 "persist_v" := NULL;

3258 LOOP

3259 "persist_v" := "check_issue"("issue_id_v", "persist_v");

3260 EXIT WHEN "persist_v" ISNULL;

3261 END LOOP;

3262 END LOOP;

3263 RETURN;

3264 END;

3265 $$;

3266

3267 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';

3268

3269

3270 CREATE OR REPLACE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE)

3271 RETURNS VOID

3272 LANGUAGE 'plpgsql' VOLATILE AS $$

3273 BEGIN

3274 IF EXISTS (

3275 SELECT NULL FROM "issue" WHERE "id" = "issue_id_p" AND "cleaned" ISNULL

3276 ) THEN

3277 -- override protection triggers:

3278 INSERT INTO "temporary_transaction_data" ("key", "value")

3279 VALUES ('override_protection_triggers', TRUE::TEXT);

3280 -- clean data:

3281 DELETE FROM "delegating_voter"

3282 WHERE "issue_id" = "issue_id_p";

3283 DELETE FROM "direct_voter"

3284 WHERE "issue_id" = "issue_id_p";

3285 DELETE FROM "delegating_interest_snapshot"

3286 WHERE "issue_id" = "issue_id_p";

3287 DELETE FROM "direct_interest_snapshot"

3288 WHERE "issue_id" = "issue_id_p";

3289 DELETE FROM "non_voter"

3290 WHERE "issue_id" = "issue_id_p";

3291 DELETE FROM "delegation"

3292 WHERE "issue_id" = "issue_id_p";

3293 DELETE FROM "supporter"

3294 USING "initiative" -- NOTE: due to missing index on issue_id

3295 WHERE "initiative"."issue_id" = "issue_id_p"

3296 AND "supporter"."initiative_id" = "initiative_id";

3297 -- mark issue as cleaned:

3298 UPDATE "issue" SET "cleaned" = now() WHERE "id" = "issue_id_p";

3299 -- finish overriding protection triggers (avoids garbage):

3300 DELETE FROM "temporary_transaction_data"

3301 WHERE "key" = 'override_protection_triggers';

3302 END IF;

3303 RETURN;

3304 END;

3305 $$;

3306

3307

3308 CREATE OR REPLACE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)

3309 RETURNS VOID

3310 LANGUAGE 'plpgsql' VOLATILE AS $$

3311 BEGIN

3312 UPDATE "member" SET

3313 "last_login" = NULL,

3314 "last_delegation_check" = NULL,

3315 "login" = NULL,

3316 "password" = NULL,

3317 "authority" = NULL,

3318 "authority_uid" = NULL,

3319 "authority_login" = NULL,

3320 "deleted" = coalesce("deleted", now()),

3321 "locked" = TRUE,

3322 "active" = FALSE,

3323 "notify_email" = NULL,

3324 "notify_email_unconfirmed" = NULL,

3325 "notify_email_secret" = NULL,

3326 "notify_email_secret_expiry" = NULL,

3327 "notify_email_lock_expiry" = NULL,

3328 "disable_notifications" = TRUE,

3329 "notification_counter" = DEFAULT,

3330 "notification_sample_size" = 0,

3331 "notification_dow" = NULL,

3332 "notification_hour" = NULL,

3333 "notification_sent" = NULL,

3334 "login_recovery_expiry" = NULL,

3335 "password_reset_secret" = NULL,

3336 "password_reset_secret_expiry" = NULL,

3337 "location" = NULL

3338 WHERE "id" = "member_id_p";

3339 -- "text_search_data" is updated by triggers

3340 DELETE FROM "member_settings" WHERE "member_id" = "member_id_p";

3341 DELETE FROM "member_profile" WHERE "member_id" = "member_id_p";

3342 DELETE FROM "rendered_member_statement" WHERE "member_id" = "member_id_p";

3343 DELETE FROM "member_image" WHERE "member_id" = "member_id_p";

3344 DELETE FROM "contact" WHERE "member_id" = "member_id_p";

3345 DELETE FROM "ignored_member" WHERE "member_id" = "member_id_p";

3346 DELETE FROM "session" WHERE "member_id" = "member_id_p";

3347 DELETE FROM "member_application" WHERE "member_id" = "member_id_p";

3348 DELETE FROM "token" WHERE "member_id" = "member_id_p";

3349 DELETE FROM "subscription" WHERE "member_id" = "member_id_p";

3350 DELETE FROM "ignored_area" WHERE "member_id" = "member_id_p";

3351 DELETE FROM "ignored_initiative" WHERE "member_id" = "member_id_p";

3352 DELETE FROM "delegation" WHERE "truster_id" = "member_id_p";

3353 DELETE FROM "non_voter" WHERE "member_id" = "member_id_p";

3354 DELETE FROM "direct_voter" USING "issue"

3355 WHERE "direct_voter"."issue_id" = "issue"."id"

3356 AND "issue"."closed" ISNULL

3357 AND "member_id" = "member_id_p";

3358 DELETE FROM "notification_initiative_sent" WHERE "member_id" = "member_id_p";

3359 RETURN;

3360 END;

3361 $$;

3362

3363

3364 CREATE OR REPLACE FUNCTION "delete_private_data"()

3365 RETURNS VOID

3366 LANGUAGE 'plpgsql' VOLATILE AS $$

3367 BEGIN

3368 DELETE FROM "temporary_transaction_data";

3369 DELETE FROM "temporary_suggestion_counts";

3370 DELETE FROM "member" WHERE "activated" ISNULL;

3371 UPDATE "member" SET

3372 "invite_code" = NULL,

3373 "invite_code_expiry" = NULL,

3374 "admin_comment" = NULL,

3375 "last_login" = NULL,

3376 "last_delegation_check" = NULL,

3377 "login" = NULL,

3378 "password" = NULL,

3379 "authority" = NULL,

3380 "authority_uid" = NULL,

3381 "authority_login" = NULL,

3382 "lang" = NULL,

3383 "notify_email" = NULL,

3384 "notify_email_unconfirmed" = NULL,

3385 "notify_email_secret" = NULL,

3386 "notify_email_secret_expiry" = NULL,

3387 "notify_email_lock_expiry" = NULL,

3388 "disable_notifications" = TRUE,

3389 "notification_counter" = DEFAULT,

3390 "notification_sample_size" = 0,

3391 "notification_dow" = NULL,

3392 "notification_hour" = NULL,

3393 "notification_sent" = NULL,

3394 "login_recovery_expiry" = NULL,

3395 "password_reset_secret" = NULL,

3396 "password_reset_secret_expiry" = NULL,

3397 "location" = NULL;

3398 -- "text_search_data" is updated by triggers

3399 DELETE FROM "verification";

3400 DELETE FROM "member_settings";

3401 DELETE FROM "member_useterms";

3402 DELETE FROM "member_profile";

3403 DELETE FROM "rendered_member_statement";

3404 DELETE FROM "member_image";

3405 DELETE FROM "contact";

3406 DELETE FROM "ignored_member";

3407 DELETE FROM "session";

3408 DELETE FROM "system_application";

3409 DELETE FROM "system_application_redirect_uri";

3410 DELETE FROM "dynamic_application_scope";

3411 DELETE FROM "member_application";

3412 DELETE FROM "token";

3413 DELETE FROM "subscription";

3414 DELETE FROM "ignored_area";

3415 DELETE FROM "ignored_initiative";

3416 DELETE FROM "non_voter";

3417 DELETE FROM "direct_voter" USING "issue"

3418 WHERE "direct_voter"."issue_id" = "issue"."id"

3419 AND "issue"."closed" ISNULL;

3420 DELETE FROM "event_processed";

3421 DELETE FROM "notification_initiative_sent";

3422 DELETE FROM "newsletter";

3423 RETURN;

3424 END;

3425 $$;

3426

3427

3428 CREATE TEMPORARY TABLE "old_snapshot" AS

3429 SELECT "ordered".*, row_number() OVER () AS "snapshot_id"

3430 FROM (

3431 SELECT * FROM (

3432 SELECT

3433 "id" AS "issue_id",

3434 'end_of_admission'::"snapshot_event" AS "event",

3435 "accepted" AS "calculated"

3436 FROM "issue" WHERE "accepted" NOTNULL

3437 UNION ALL

3438 SELECT

3439 "id" AS "issue_id",

3440 'half_freeze'::"snapshot_event" AS "event",

3441 "half_frozen" AS "calculated"

3442 FROM "issue" WHERE "half_frozen" NOTNULL

3443 UNION ALL

3444 SELECT

3445 "id" AS "issue_id",

3446 'full_freeze'::"snapshot_event" AS "event",

3447 "fully_frozen" AS "calculated"

3448 FROM "issue" WHERE "fully_frozen" NOTNULL

3449 ) AS "unordered"

3450 ORDER BY "calculated", "issue_id", "event"

3451 ) AS "ordered";

3452

3453

3454 INSERT INTO "snapshot" ("id", "calculated", "population", "area_id", "issue_id")

3455 SELECT

3456 "old_snapshot"."snapshot_id" AS "id",

3457 "old_snapshot"."calculated",

3458 ( SELECT COALESCE(sum("weight"), 0)

3459 FROM "direct_population_snapshot" "dps"

3460 WHERE "dps"."issue_id" = "old_snapshot"."issue_id"

3461 AND "dps"."event" = "old_snapshot"."event"

3462 ) AS "population",

3463 "issue"."area_id" AS "area_id",

3464 "issue"."id" AS "issue_id"

3465 FROM "old_snapshot" JOIN "issue"

3466 ON "old_snapshot"."issue_id" = "issue"."id";

3467

3468

3469 INSERT INTO "snapshot_issue" ("snapshot_id", "issue_id")

3470 SELECT "id" AS "snapshot_id", "issue_id" FROM "snapshot";

3471

3472

3473 INSERT INTO "snapshot_population" ("snapshot_id", "member_id")

3474 SELECT

3475 "old_snapshot"."snapshot_id",

3476 "direct_population_snapshot"."member_id"

3477 FROM "old_snapshot" JOIN "direct_population_snapshot"

3478 ON "old_snapshot"."issue_id" = "direct_population_snapshot"."issue_id"

3479 AND "old_snapshot"."event" = "direct_population_snapshot"."event";

3480

3481 INSERT INTO "snapshot_population" ("snapshot_id", "member_id")

3482 SELECT

3483 "old_snapshot"."snapshot_id",

3484 "delegating_population_snapshot"."member_id"

3485 FROM "old_snapshot" JOIN "delegating_population_snapshot"

3486 ON "old_snapshot"."issue_id" = "delegating_population_snapshot"."issue_id"

3487 AND "old_snapshot"."event" = "delegating_population_snapshot"."event";

3488

3489

3490 INSERT INTO "direct_interest_snapshot"

3491 ("snapshot_id", "issue_id", "member_id", "weight")

3492 SELECT

3493 "old_snapshot"."snapshot_id",

3494 "old_snapshot"."issue_id",

3495 "direct_interest_snapshot_old"."member_id",

3496 "direct_interest_snapshot_old"."weight"

3497 FROM "old_snapshot" JOIN "direct_interest_snapshot_old"

3498 ON "old_snapshot"."issue_id" = "direct_interest_snapshot_old"."issue_id"

3499 AND "old_snapshot"."event" = "direct_interest_snapshot_old"."event";

3500

3501 INSERT INTO "delegating_interest_snapshot"

3502 ( "snapshot_id", "issue_id",

3503 "member_id", "weight", "scope", "delegate_member_ids" )

3504 SELECT

3505 "old_snapshot"."snapshot_id",

3506 "old_snapshot"."issue_id",

3507 "delegating_interest_snapshot_old"."member_id",

3508 "delegating_interest_snapshot_old"."weight",

3509 "delegating_interest_snapshot_old"."scope",

3510 "delegating_interest_snapshot_old"."delegate_member_ids"

3511 FROM "old_snapshot" JOIN "delegating_interest_snapshot_old"

3512 ON "old_snapshot"."issue_id" = "delegating_interest_snapshot_old"."issue_id"

3513 AND "old_snapshot"."event" = "delegating_interest_snapshot_old"."event";

3514

3515 INSERT INTO "direct_supporter_snapshot"

3516 ( "snapshot_id", "issue_id",

3517 "initiative_id", "member_id", "draft_id", "informed", "satisfied" )

3518 SELECT

3519 "old_snapshot"."snapshot_id",

3520 "old_snapshot"."issue_id",

3521 "direct_supporter_snapshot_old"."initiative_id",

3522 "direct_supporter_snapshot_old"."member_id",

3523 "direct_supporter_snapshot_old"."draft_id",

3524 "direct_supporter_snapshot_old"."informed",

3525 "direct_supporter_snapshot_old"."satisfied"

3526 FROM "old_snapshot" JOIN "direct_supporter_snapshot_old"

3527 ON "old_snapshot"."issue_id" = "direct_supporter_snapshot_old"."issue_id"

3528 AND "old_snapshot"."event" = "direct_supporter_snapshot_old"."event";

3529

3530

3531 ALTER TABLE "issue" DISABLE TRIGGER USER; -- NOTE: required to modify table later

3532

3533 UPDATE "issue" SET "latest_snapshot_id" = "snapshot"."id"

3534 FROM (

3535 SELECT DISTINCT ON ("issue_id") "issue_id", "id"

3536 FROM "snapshot" ORDER BY "issue_id", "id" DESC

3537 ) AS "snapshot"

3538 WHERE "snapshot"."issue_id" = "issue"."id";

3539

3540 UPDATE "issue" SET "admission_snapshot_id" = "old_snapshot"."snapshot_id"

3541 FROM "old_snapshot"

3542 WHERE "old_snapshot"."issue_id" = "issue"."id"

3543 AND "old_snapshot"."event" = 'end_of_admission';

3544

3545 UPDATE "issue" SET "half_freeze_snapshot_id" = "old_snapshot"."snapshot_id"

3546 FROM "old_snapshot"

3547 WHERE "old_snapshot"."issue_id" = "issue"."id"

3548 AND "old_snapshot"."event" = 'half_freeze';

3549

3550 UPDATE "issue" SET "full_freeze_snapshot_id" = "old_snapshot"."snapshot_id"

3551 FROM "old_snapshot"

3552 WHERE "old_snapshot"."issue_id" = "issue"."id"

3553 AND "old_snapshot"."event" = 'full_freeze';

3554

3555 ALTER TABLE "issue" ENABLE TRIGGER USER;

3556

3557

3558 DROP TABLE "old_snapshot";

3559

3560 DROP TABLE "direct_supporter_snapshot_old";

3561 DROP TABLE "delegating_interest_snapshot_old";

3562 DROP TABLE "direct_interest_snapshot_old";

3563 DROP TABLE "delegating_population_snapshot";

3564 DROP TABLE "direct_population_snapshot";

3565

3566

3567 DROP VIEW "open_issue";

3568

3569

3570 ALTER TABLE "issue" DROP COLUMN "latest_snapshot_event";

3571

3572

3573 CREATE VIEW "open_issue" AS

3574 SELECT * FROM "issue" WHERE "closed" ISNULL;

3575

3576 COMMENT ON VIEW "open_issue" IS 'All open issues';

3577

3578

3579 -- NOTE: create "issue_for_admission" view after altering table "issue"

3580 CREATE VIEW "issue_for_admission" AS

3581 SELECT DISTINCT ON ("issue"."area_id")

3582 "issue".*,

3583 max("initiative"."supporter_count") AS "max_supporter_count"

3584 FROM "issue"

3585 JOIN "policy" ON "issue"."policy_id" = "policy"."id"

3586 JOIN "initiative" ON "issue"."id" = "initiative"."issue_id"

3587 JOIN "area" ON "issue"."area_id" = "area"."id"

3588 WHERE "issue"."state" = 'admission'::"issue_state"

3589 AND now() >= "issue"."created" + "issue"."min_admission_time"

3590 AND "initiative"."supporter_count" >= "policy"."issue_quorum"

3591 AND "initiative"."supporter_count" * "policy"."issue_quorum_den" >=

3592 "issue"."population" * "policy"."issue_quorum_num"

3593 AND "initiative"."supporter_count" >= "area"."issue_quorum"

3594 AND "initiative"."revoked" ISNULL

3595 GROUP BY "issue"."id"

3596 ORDER BY "issue"."area_id", "max_supporter_count" DESC, "issue"."id";

3597

3598 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';

3599

3600

3601 DROP TYPE "snapshot_event";

3602

3603

3604 ALTER TABLE "issue" ADD CONSTRAINT "snapshot_required" CHECK (

3605 ("half_frozen" ISNULL OR "half_freeze_snapshot_id" NOTNULL) AND

3606 ("fully_frozen" ISNULL OR "full_freeze_snapshot_id" NOTNULL) );

3607

3608

3609 COMMIT;