liquid_feedback_core: 399dc1a86398 core.sql

liquid_feedback_core

view core.sql @ 412:399dc1a86398

Marked version as v3.0.0

author	jbe
date	Fri Jan 31 12:46:11 2014 +0100 (2014-01-31)
parents	44a07d8f1bb4
children	719ad5c5c940

line source

2 -- Execute the following command manually for PostgreSQL prior version 9.0:

3 -- CREATE LANGUAGE plpgsql;

5 -- NOTE: In PostgreSQL every UNIQUE constraint implies creation of an index

7 BEGIN;

9 CREATE VIEW "liquid_feedback_version" AS

10 SELECT * FROM (VALUES ('3.0.0', 3, 0, 0))

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

15 ----------------------

16 -- Full text search --

17 ----------------------

20 CREATE FUNCTION "text_search_query"("query_text_p" TEXT)

21 RETURNS TSQUERY

22 LANGUAGE 'plpgsql' IMMUTABLE AS $$

23 BEGIN

24 RETURN plainto_tsquery('pg_catalog.simple', "query_text_p");

25 END;

26 $$;

28 COMMENT ON FUNCTION "text_search_query"(TEXT) IS 'Usage: WHERE "text_search_data" @@ "text_search_query"(''<user query>'')';

31 CREATE FUNCTION "highlight"

32 ( "body_p" TEXT,

33 "query_text_p" TEXT )

34 RETURNS TEXT

35 LANGUAGE 'plpgsql' IMMUTABLE AS $$

36 BEGIN

37 RETURN ts_headline(

38 'pg_catalog.simple',

39 replace(replace("body_p", e'\\', e'\\\\'), '*', e'\\*'),

40 "text_search_query"("query_text_p"),

41 'StartSel=* StopSel=* HighlightAll=TRUE' );

42 END;

43 $$;

45 COMMENT ON FUNCTION "highlight"

46 ( "body_p" TEXT,

47 "query_text_p" TEXT )

48 IS 'For a given a user query this function encapsulates all matches with asterisks. Asterisks and backslashes being already present are preceeded with one extra backslash.';

52 -------------------------

53 -- Tables and indicies --

54 -------------------------

57 CREATE TABLE "temporary_transaction_data" (

58 PRIMARY KEY ("txid", "key"),

59 "txid" INT8 DEFAULT txid_current(),

60 "key" TEXT,

61 "value" TEXT NOT NULL );

63 COMMENT ON TABLE "temporary_transaction_data" IS 'Table to store temporary transaction data; shall be emptied before a transaction is committed';

65 COMMENT ON COLUMN "temporary_transaction_data"."txid" IS 'Value returned by function txid_current(); should be added to WHERE clause, when doing SELECT on this table, but ignored when doing DELETE on this table';

68 CREATE TABLE "system_setting" (

69 "member_ttl" INTERVAL );

70 CREATE UNIQUE INDEX "system_setting_singleton_idx" ON "system_setting" ((1));

72 COMMENT ON TABLE "system_setting" IS 'This table contains only one row with different settings in each column.';

73 COMMENT ON INDEX "system_setting_singleton_idx" IS 'This index ensures that "system_setting" only contains one row maximum.';

75 COMMENT ON COLUMN "system_setting"."member_ttl" IS 'Time after members get their "active" flag set to FALSE, if they do not show any activity.';

78 CREATE TABLE "contingent" (

79 PRIMARY KEY ("polling", "time_frame"),

80 "polling" BOOLEAN,

81 "time_frame" INTERVAL,

82 "text_entry_limit" INT4,

83 "initiative_limit" INT4 );

85 COMMENT ON TABLE "contingent" IS 'Amount of text entries or initiatives a user may create within a given time frame. Only one row needs to be fulfilled for a member to be allowed to post. This table must not be empty.';

87 COMMENT ON COLUMN "contingent"."polling" IS 'Determines if settings are for creating initiatives and new drafts of initiatives with "polling" flag set';

88 COMMENT ON COLUMN "contingent"."text_entry_limit" IS 'Number of new drafts or suggestions to be submitted by each member within the given time frame';

89 COMMENT ON COLUMN "contingent"."initiative_limit" IS 'Number of new initiatives to be opened by each member within a given time frame';

92 CREATE TYPE "notify_level" AS ENUM

93 ('none', 'voting', 'verification', 'discussion', 'all');

95 COMMENT ON TYPE "notify_level" IS 'Level of notification: ''none'' = no notifications, ''voting'' = notifications about finished issues and issues in voting, ''verification'' = notifications about finished issues, issues in voting and verification phase, ''discussion'' = notifications about everything except issues in admission phase, ''all'' = notifications about everything';

98 CREATE TABLE "member" (

99 "id" SERIAL4 PRIMARY KEY,

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

101 "invite_code" TEXT UNIQUE,

102 "invite_code_expiry" TIMESTAMPTZ,

103 "admin_comment" TEXT,

104 "activated" TIMESTAMPTZ,

105 "last_activity" DATE,

106 "last_login" TIMESTAMPTZ,

107 "last_delegation_check" TIMESTAMPTZ,

108 "login" TEXT UNIQUE,

109 "password" TEXT,

110 "locked" BOOLEAN NOT NULL DEFAULT FALSE,

111 "active" BOOLEAN NOT NULL DEFAULT FALSE,

112 "admin" BOOLEAN NOT NULL DEFAULT FALSE,

113 "lang" TEXT,

114 "notify_email" TEXT,

115 "notify_email_unconfirmed" TEXT,

116 "notify_email_secret" TEXT UNIQUE,

117 "notify_email_secret_expiry" TIMESTAMPTZ,

118 "notify_email_lock_expiry" TIMESTAMPTZ,

119 "notify_level" "notify_level",

120 "login_recovery_expiry" TIMESTAMPTZ,

121 "password_reset_secret" TEXT UNIQUE,

122 "password_reset_secret_expiry" TIMESTAMPTZ,

123 "name" TEXT UNIQUE,

124 "identification" TEXT UNIQUE,

125 "authentication" TEXT,

126 "organizational_unit" TEXT,

127 "internal_posts" TEXT,

128 "realname" TEXT,

129 "birthday" DATE,

130 "address" TEXT,

131 "email" TEXT,

132 "xmpp_address" TEXT,

133 "website" TEXT,

134 "phone" TEXT,

135 "mobile_phone" TEXT,

136 "profession" TEXT,

137 "external_memberships" TEXT,

138 "external_posts" TEXT,

139 "formatting_engine" TEXT,

140 "statement" TEXT,

141 "text_search_data" TSVECTOR,

142 CONSTRAINT "active_requires_activated_and_last_activity"

143 CHECK ("active" = FALSE OR ("activated" NOTNULL AND "last_activity" NOTNULL)),

144 CONSTRAINT "name_not_null_if_activated"

145 CHECK ("activated" ISNULL OR "name" NOTNULL) );

146 CREATE INDEX "member_active_idx" ON "member" ("active");

147 CREATE INDEX "member_text_search_data_idx" ON "member" USING gin ("text_search_data");

148 CREATE TRIGGER "update_text_search_data"

149 BEFORE INSERT OR UPDATE ON "member"

150 FOR EACH ROW EXECUTE PROCEDURE

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

152 "name", "identification", "organizational_unit", "internal_posts",

153 "realname", "external_memberships", "external_posts", "statement" );

154

155 COMMENT ON TABLE "member" IS 'Users of the system, e.g. members of an organization';

156

157 COMMENT ON COLUMN "member"."created" IS 'Creation of member record and/or invite code';

158 COMMENT ON COLUMN "member"."invite_code" IS 'Optional invite code, to allow a member to initialize his/her account the first time';

159 COMMENT ON COLUMN "member"."invite_code_expiry" IS 'Expiry data/time for "invite_code"';

160 COMMENT ON COLUMN "member"."admin_comment" IS 'Hidden comment for administrative purposes';

161 COMMENT ON COLUMN "member"."activated" IS 'Timestamp of first activation of account (i.e. usage of "invite_code"); required to be set for "active" members';

162 COMMENT ON COLUMN "member"."last_activity" IS 'Date of last activity of member; required to be set for "active" members';

163 COMMENT ON COLUMN "member"."last_login" IS 'Timestamp of last login';

164 COMMENT ON COLUMN "member"."last_delegation_check" IS 'Timestamp of last delegation check (i.e. confirmation of all unit and area delegations)';

165 COMMENT ON COLUMN "member"."login" IS 'Login name';

166 COMMENT ON COLUMN "member"."password" IS 'Password (preferably as crypto-hash, depending on the frontend or access layer)';

167 COMMENT ON COLUMN "member"."locked" IS 'Locked members can not log in.';

168 COMMENT ON COLUMN "member"."active" IS 'Memberships, support and votes are taken into account when corresponding members are marked as active. Automatically set to FALSE, if "last_activity" is older than "system_setting"."member_ttl".';

169 COMMENT ON COLUMN "member"."admin" IS 'TRUE for admins, which can administrate other users and setup policies and areas';

170 COMMENT ON COLUMN "member"."lang" IS 'Language code of the preferred language of the member';

171 COMMENT ON COLUMN "member"."notify_email" IS 'Email address where notifications of the system are sent to';

172 COMMENT ON COLUMN "member"."notify_email_unconfirmed" IS 'Unconfirmed email address provided by the member to be copied into "notify_email" field after verification';

173 COMMENT ON COLUMN "member"."notify_email_secret" IS 'Secret sent to the address in "notify_email_unconformed"';

174 COMMENT ON COLUMN "member"."notify_email_secret_expiry" IS 'Expiry date/time for "notify_email_secret"';

175 COMMENT ON COLUMN "member"."notify_email_lock_expiry" IS 'Date/time until no further email confirmation mails may be sent (abuse protection)';

176 COMMENT ON COLUMN "member"."notify_level" IS 'Selects which event notifications are to be sent to the "notify_email" mail address, may be NULL if member did not make any selection yet';

177 COMMENT ON COLUMN "member"."login_recovery_expiry" IS 'Date/time after which another login recovery attempt is allowed';

178 COMMENT ON COLUMN "member"."password_reset_secret" IS 'Secret string sent via e-mail for password recovery';

179 COMMENT ON COLUMN "member"."password_reset_secret_expiry" IS 'Date/time until the password recovery secret is valid, and date/time after which another password recovery attempt is allowed';

180 COMMENT ON COLUMN "member"."name" IS 'Distinct name of the member, may be NULL if account has not been activated yet';

181 COMMENT ON COLUMN "member"."identification" IS 'Optional identification number or code of the member';

182 COMMENT ON COLUMN "member"."authentication" IS 'Information about how this member was authenticated';

183 COMMENT ON COLUMN "member"."organizational_unit" IS 'Branch or division of the organization the member belongs to';

184 COMMENT ON COLUMN "member"."internal_posts" IS 'Posts (offices) of the member inside the organization';

185 COMMENT ON COLUMN "member"."realname" IS 'Real name of the member, may be identical with "name"';

186 COMMENT ON COLUMN "member"."email" IS 'Published email address of the member; not used for system notifications';

187 COMMENT ON COLUMN "member"."external_memberships" IS 'Other organizations the member is involved in';

188 COMMENT ON COLUMN "member"."external_posts" IS 'Posts (offices) outside the organization';

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

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

191

192

193 -- DEPRECATED API TABLES --

194

195 CREATE TYPE "application_access_level" AS ENUM

196 ('member', 'full', 'pseudonymous', 'anonymous');

197

198 COMMENT ON TYPE "application_access_level" IS 'DEPRECATED, WILL BE REMOVED! Access privileges for applications using the API';

199

200

201 CREATE TABLE "member_application" (

202 "id" SERIAL8 PRIMARY KEY,

203 UNIQUE ("member_id", "name"),

204 "member_id" INT4 NOT NULL REFERENCES "member" ("id")

205 ON DELETE CASCADE ON UPDATE CASCADE,

206 "name" TEXT NOT NULL,

207 "comment" TEXT,

208 "access_level" "application_access_level" NOT NULL,

209 "key" TEXT NOT NULL UNIQUE,

210 "last_usage" TIMESTAMPTZ );

211

212 COMMENT ON TABLE "member_application" IS 'DEPRECATED, WILL BE REMOVED! Registered application being allowed to use the API';

213

214 -- END OF DEPRECARED API TABLES --

215

216

217 CREATE TABLE "member_history" (

218 "id" SERIAL8 PRIMARY KEY,

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

220 "until" TIMESTAMPTZ NOT NULL DEFAULT now(),

221 "active" BOOLEAN NOT NULL,

222 "name" TEXT NOT NULL );

223 CREATE INDEX "member_history_member_id_idx" ON "member_history" ("member_id");

224

225 COMMENT ON TABLE "member_history" IS 'Filled by trigger; keeps information about old names and active flag of members';

226

227 COMMENT ON COLUMN "member_history"."id" IS 'Primary key, which can be used to sort entries correctly (and time warp resistant)';

228 COMMENT ON COLUMN "member_history"."until" IS 'Timestamp until the data was valid';

229

230

231 CREATE TABLE "rendered_member_statement" (

232 PRIMARY KEY ("member_id", "format"),

233 "member_id" INT8 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,

234 "format" TEXT,

235 "content" TEXT NOT NULL );

236

237 COMMENT ON TABLE "rendered_member_statement" IS 'This table may be used by frontends to cache "rendered" member statements (e.g. HTML output generated from wiki text)';

238

239

240 CREATE TABLE "setting" (

241 PRIMARY KEY ("member_id", "key"),

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

243 "key" TEXT NOT NULL,

244 "value" TEXT NOT NULL );

245 CREATE INDEX "setting_key_idx" ON "setting" ("key");

246

247 COMMENT ON TABLE "setting" IS 'Place to store a frontend specific setting for members as a string';

248

249 COMMENT ON COLUMN "setting"."key" IS 'Name of the setting, preceded by a frontend specific prefix';

250

251

252 CREATE TABLE "setting_map" (

253 PRIMARY KEY ("member_id", "key", "subkey"),

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

255 "key" TEXT NOT NULL,

256 "subkey" TEXT NOT NULL,

257 "value" TEXT NOT NULL );

258 CREATE INDEX "setting_map_key_idx" ON "setting_map" ("key");

259

260 COMMENT ON TABLE "setting_map" IS 'Place to store a frontend specific setting for members as a map of key value pairs';

261

262 COMMENT ON COLUMN "setting_map"."key" IS 'Name of the setting, preceded by a frontend specific prefix';

263 COMMENT ON COLUMN "setting_map"."subkey" IS 'Key of a map entry';

264 COMMENT ON COLUMN "setting_map"."value" IS 'Value of a map entry';

265

266

267 CREATE TABLE "member_relation_setting" (

268 PRIMARY KEY ("member_id", "key", "other_member_id"),

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

270 "key" TEXT NOT NULL,

271 "other_member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,

272 "value" TEXT NOT NULL );

273

274 COMMENT ON TABLE "member_relation_setting" IS 'Place to store a frontend specific setting related to relations between members as a string';

275

276

277 CREATE TYPE "member_image_type" AS ENUM ('photo', 'avatar');

278

279 COMMENT ON TYPE "member_image_type" IS 'Types of images for a member';

280

281

282 CREATE TABLE "member_image" (

283 PRIMARY KEY ("member_id", "image_type", "scaled"),

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

285 "image_type" "member_image_type",

286 "scaled" BOOLEAN,

287 "content_type" TEXT,

288 "data" BYTEA NOT NULL );

289

290 COMMENT ON TABLE "member_image" IS 'Images of members';

291

292 COMMENT ON COLUMN "member_image"."scaled" IS 'FALSE for original image, TRUE for scaled version of the image';

293

294

295 CREATE TABLE "member_count" (

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

297 "total_count" INT4 NOT NULL );

298

299 COMMENT ON TABLE "member_count" IS 'Contains one row which contains the total count of active(!) members and a timestamp indicating when the total member count and area member counts were calculated';

300

301 COMMENT ON COLUMN "member_count"."calculated" IS 'timestamp indicating when the total member count and area member counts were calculated';

302 COMMENT ON COLUMN "member_count"."total_count" IS 'Total count of active(!) members';

303

304

305 CREATE TABLE "contact" (

306 PRIMARY KEY ("member_id", "other_member_id"),

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

308 "other_member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,

309 "public" BOOLEAN NOT NULL DEFAULT FALSE,

310 CONSTRAINT "cant_save_yourself_as_contact"

311 CHECK ("member_id" != "other_member_id") );

312 CREATE INDEX "contact_other_member_id_idx" ON "contact" ("other_member_id");

313

314 COMMENT ON TABLE "contact" IS 'Contact lists';

315

316 COMMENT ON COLUMN "contact"."member_id" IS 'Member having the contact list';

317 COMMENT ON COLUMN "contact"."other_member_id" IS 'Member referenced in the contact list';

318 COMMENT ON COLUMN "contact"."public" IS 'TRUE = display contact publically';

319

320

321 CREATE TABLE "ignored_member" (

322 PRIMARY KEY ("member_id", "other_member_id"),

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

324 "other_member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );

325 CREATE INDEX "ignored_member_other_member_id_idx" ON "ignored_member" ("other_member_id");

326

327 COMMENT ON TABLE "ignored_member" IS 'Possibility to filter other members';

328

329 COMMENT ON COLUMN "ignored_member"."member_id" IS 'Member ignoring someone';

330 COMMENT ON COLUMN "ignored_member"."other_member_id" IS 'Member being ignored';

331

332

333 CREATE TABLE "session" (

334 "ident" TEXT PRIMARY KEY,

335 "additional_secret" TEXT,

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

337 "member_id" INT8 REFERENCES "member" ("id") ON DELETE SET NULL,

338 "needs_delegation_check" BOOLEAN NOT NULL DEFAULT FALSE,

339 "lang" TEXT );

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

341

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

343

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

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

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

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

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

349

350

351 CREATE TABLE "policy" (

352 "id" SERIAL4 PRIMARY KEY,

353 "index" INT4 NOT NULL,

354 "active" BOOLEAN NOT NULL DEFAULT TRUE,

355 "name" TEXT NOT NULL UNIQUE,

356 "description" TEXT NOT NULL DEFAULT '',

357 "polling" BOOLEAN NOT NULL DEFAULT FALSE,

358 "admission_time" INTERVAL,

359 "discussion_time" INTERVAL,

360 "verification_time" INTERVAL,

361 "voting_time" INTERVAL,

362 "issue_quorum_num" INT4,

363 "issue_quorum_den" INT4,

364 "initiative_quorum_num" INT4 NOT NULL,

365 "initiative_quorum_den" INT4 NOT NULL,

366 "direct_majority_num" INT4 NOT NULL DEFAULT 1,

367 "direct_majority_den" INT4 NOT NULL DEFAULT 2,

368 "direct_majority_strict" BOOLEAN NOT NULL DEFAULT TRUE,

369 "direct_majority_positive" INT4 NOT NULL DEFAULT 0,

370 "direct_majority_non_negative" INT4 NOT NULL DEFAULT 0,

371 "indirect_majority_num" INT4 NOT NULL DEFAULT 1,

372 "indirect_majority_den" INT4 NOT NULL DEFAULT 2,

373 "indirect_majority_strict" BOOLEAN NOT NULL DEFAULT TRUE,

374 "indirect_majority_positive" INT4 NOT NULL DEFAULT 0,

375 "indirect_majority_non_negative" INT4 NOT NULL DEFAULT 0,

376 "no_reverse_beat_path" BOOLEAN NOT NULL DEFAULT TRUE,

377 "no_multistage_majority" BOOLEAN NOT NULL DEFAULT FALSE,

378 CONSTRAINT "timing" CHECK (

379 ( "polling" = FALSE AND

380 "admission_time" NOTNULL AND "discussion_time" NOTNULL AND

381 "verification_time" NOTNULL AND "voting_time" NOTNULL ) OR

382 ( "polling" = TRUE AND

383 "admission_time" ISNULL AND "discussion_time" NOTNULL AND

384 "verification_time" NOTNULL AND "voting_time" NOTNULL ) OR

385 ( "polling" = TRUE AND

386 "admission_time" ISNULL AND "discussion_time" ISNULL AND

387 "verification_time" ISNULL AND "voting_time" ISNULL ) ),

388 CONSTRAINT "issue_quorum_if_and_only_if_not_polling" CHECK (

389 "polling" = "issue_quorum_num" ISNULL AND

390 "polling" = "issue_quorum_den" ISNULL ) );

391 CREATE INDEX "policy_active_idx" ON "policy" ("active");

392

393 COMMENT ON TABLE "policy" IS 'Policies for a particular proceeding type (timelimits, quorum)';

394

395 COMMENT ON COLUMN "policy"."index" IS 'Determines the order in listings';

396 COMMENT ON COLUMN "policy"."active" IS 'TRUE = policy can be used for new issues';

397 COMMENT ON COLUMN "policy"."polling" IS 'TRUE = special policy for non-user-generated issues without issue quorum, where certain initiatives (those having the "polling" flag set) do not need to pass the initiative quorum; "admission_time" MUST be set to NULL, the other timings may be set to NULL altogether, allowing individual timing for those issues';

398 COMMENT ON COLUMN "policy"."admission_time" IS 'Maximum duration of issue state ''admission''; Maximum time an issue stays open without being "accepted"';

399 COMMENT ON COLUMN "policy"."discussion_time" IS 'Duration of issue state ''discussion''; Regular time until an issue is "half_frozen" after being "accepted"';

400 COMMENT ON COLUMN "policy"."verification_time" IS 'Duration of issue state ''verification''; Regular time until an issue is "fully_frozen" (e.g. entering issue state ''voting'') after being "half_frozen"';

401 COMMENT ON COLUMN "policy"."voting_time" IS 'Duration of issue state ''voting''; Time after an issue is "fully_frozen" but not "closed" (duration of issue state ''voting'')';

402 COMMENT ON COLUMN "policy"."issue_quorum_num" IS 'Numerator of potential supporter quorum to be reached by one initiative of an issue to be "accepted" and enter issue state ''discussion''';

403 COMMENT ON COLUMN "policy"."issue_quorum_den" IS 'Denominator of potential supporter quorum to be reached by one initiative of an issue to be "accepted" and enter issue state ''discussion''';

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

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

406 COMMENT ON COLUMN "policy"."direct_majority_num" IS 'Numerator of fraction of neccessary direct majority for initiatives to be attainable as winner';

407 COMMENT ON COLUMN "policy"."direct_majority_den" IS 'Denominator of fraction of neccessary direct majority for initaitives to be attainable as winner';

408 COMMENT ON COLUMN "policy"."direct_majority_strict" IS 'If TRUE, then the direct majority must be strictly greater than "direct_majority_num"/"direct_majority_den", otherwise it may also be equal.';

409 COMMENT ON COLUMN "policy"."direct_majority_positive" IS 'Absolute number of "positive_votes" neccessary for an initiative to be attainable as winner';

410 COMMENT ON COLUMN "policy"."direct_majority_non_negative" IS 'Absolute number of sum of "positive_votes" and abstentions neccessary for an initiative to be attainable as winner';

411 COMMENT ON COLUMN "policy"."indirect_majority_num" IS 'Numerator of fraction of neccessary indirect majority (through beat path) for initiatives to be attainable as winner';

412 COMMENT ON COLUMN "policy"."indirect_majority_den" IS 'Denominator of fraction of neccessary indirect majority (through beat path) for initiatives to be attainable as winner';

413 COMMENT ON COLUMN "policy"."indirect_majority_strict" IS 'If TRUE, then the indirect majority must be strictly greater than "indirect_majority_num"/"indirect_majority_den", otherwise it may also be equal.';

414 COMMENT ON COLUMN "policy"."indirect_majority_positive" IS 'Absolute number of votes in favor of the winner neccessary in a beat path to the status quo for an initaitive to be attainable as winner';

415 COMMENT ON COLUMN "policy"."indirect_majority_non_negative" IS 'Absolute number of sum of votes in favor and abstentions in a beat path to the status quo for an initiative to be attainable as winner';

416 COMMENT ON COLUMN "policy"."no_reverse_beat_path" IS 'Causes initiatives with "reverse_beat_path" flag to not be "eligible", thus disallowing them to be winner. See comment on column "initiative"."reverse_beat_path". This option ensures both that a winning initiative is never tied in a (weak) condorcet paradox with the status quo and a winning initiative always beats the status quo directly with a simple majority.';

417 COMMENT ON COLUMN "policy"."no_multistage_majority" IS 'Causes initiatives with "multistage_majority" flag to not be "eligible", thus disallowing them to be winner. See comment on column "initiative"."multistage_majority". This disqualifies initiatives which could cause an instable result. An instable result in this meaning is a result such that repeating the ballot with same preferences but with the winner of the first ballot as status quo would lead to a different winner in the second ballot. If there are no direct majorities required for the winner, or if in direct comparison only simple majorities are required and "no_reverse_beat_path" is true, then results are always stable and this flag does not have any effect on the winner (but still affects the "eligible" flag of an "initiative").';

418

419

420 CREATE TABLE "unit" (

421 "id" SERIAL4 PRIMARY KEY,

422 "parent_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,

423 "active" BOOLEAN NOT NULL DEFAULT TRUE,

424 "name" TEXT NOT NULL,

425 "description" TEXT NOT NULL DEFAULT '',

426 "member_count" INT4,

427 "text_search_data" TSVECTOR );

428 CREATE INDEX "unit_root_idx" ON "unit" ("id") WHERE "parent_id" ISNULL;

429 CREATE INDEX "unit_parent_id_idx" ON "unit" ("parent_id");

430 CREATE INDEX "unit_active_idx" ON "unit" ("active");

431 CREATE INDEX "unit_text_search_data_idx" ON "unit" USING gin ("text_search_data");

432 CREATE TRIGGER "update_text_search_data"

433 BEFORE INSERT OR UPDATE ON "unit"

434 FOR EACH ROW EXECUTE PROCEDURE

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

436 "name", "description" );

437

438 COMMENT ON TABLE "unit" IS 'Organizational units organized as trees; Delegations are not inherited through these trees.';

439

440 COMMENT ON COLUMN "unit"."parent_id" IS 'Parent id of tree node; Multiple roots allowed';

441 COMMENT ON COLUMN "unit"."active" IS 'TRUE means new issues can be created in areas of this unit';

442 COMMENT ON COLUMN "unit"."member_count" IS 'Count of members as determined by column "voting_right" in table "privilege"';

443

444

445 CREATE TABLE "unit_setting" (

446 PRIMARY KEY ("member_id", "key", "unit_id"),

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

448 "key" TEXT NOT NULL,

449 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,

450 "value" TEXT NOT NULL );

451

452 COMMENT ON TABLE "unit_setting" IS 'Place for frontend to store unit specific settings of members as strings';

453

454

455 CREATE TABLE "area" (

456 "id" SERIAL4 PRIMARY KEY,

457 "unit_id" INT4 NOT NULL REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,

458 "active" BOOLEAN NOT NULL DEFAULT TRUE,

459 "name" TEXT NOT NULL,

460 "description" TEXT NOT NULL DEFAULT '',

461 "direct_member_count" INT4,

462 "member_weight" INT4,

463 "text_search_data" TSVECTOR );

464 CREATE INDEX "area_unit_id_idx" ON "area" ("unit_id");

465 CREATE INDEX "area_active_idx" ON "area" ("active");

466 CREATE INDEX "area_text_search_data_idx" ON "area" USING gin ("text_search_data");

467 CREATE TRIGGER "update_text_search_data"

468 BEFORE INSERT OR UPDATE ON "area"

469 FOR EACH ROW EXECUTE PROCEDURE

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

471 "name", "description" );

472

473 COMMENT ON TABLE "area" IS 'Subject areas';

474

475 COMMENT ON COLUMN "area"."active" IS 'TRUE means new issues can be created in this area';

476 COMMENT ON COLUMN "area"."direct_member_count" IS 'Number of active members of that area (ignoring their weight), as calculated from view "area_member_count"';

477 COMMENT ON COLUMN "area"."member_weight" IS 'Same as "direct_member_count" but respecting delegations';

478

479

480 CREATE TABLE "area_setting" (

481 PRIMARY KEY ("member_id", "key", "area_id"),

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

483 "key" TEXT NOT NULL,

484 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,

485 "value" TEXT NOT NULL );

486

487 COMMENT ON TABLE "area_setting" IS 'Place for frontend to store area specific settings of members as strings';

488

489

490 CREATE TABLE "allowed_policy" (

491 PRIMARY KEY ("area_id", "policy_id"),

492 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,

493 "policy_id" INT4 NOT NULL REFERENCES "policy" ("id") ON DELETE CASCADE ON UPDATE CASCADE,

494 "default_policy" BOOLEAN NOT NULL DEFAULT FALSE );

495 CREATE UNIQUE INDEX "allowed_policy_one_default_per_area_idx" ON "allowed_policy" ("area_id") WHERE "default_policy";

496

497 COMMENT ON TABLE "allowed_policy" IS 'Selects which policies can be used in each area';

498

499 COMMENT ON COLUMN "allowed_policy"."default_policy" IS 'One policy per area can be set as default.';

500

501

502 CREATE TYPE "snapshot_event" AS ENUM ('periodic', 'end_of_admission', 'half_freeze', 'full_freeze');

503

504 COMMENT ON TYPE "snapshot_event" IS 'Reason for snapshots: ''periodic'' = due to periodic recalculation, ''end_of_admission'' = saved state at end of admission period, ''half_freeze'' = saved state at end of discussion period, ''full_freeze'' = saved state at end of verification period';

505

506

507 CREATE TYPE "issue_state" AS ENUM (

508 'admission', 'discussion', 'verification', 'voting',

509 'canceled_by_admin',

510 'canceled_revoked_before_accepted',

511 'canceled_issue_not_accepted',

512 'canceled_after_revocation_during_discussion',

513 'canceled_after_revocation_during_verification',

514 'canceled_no_initiative_admitted',

515 'finished_without_winner', 'finished_with_winner');

516

517 COMMENT ON TYPE "issue_state" IS 'State of issues';

518

519

520 CREATE TABLE "issue" (

521 "id" SERIAL4 PRIMARY KEY,

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

523 "policy_id" INT4 NOT NULL REFERENCES "policy" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,

524 "admin_notice" TEXT,

525 "state" "issue_state" NOT NULL DEFAULT 'admission',

526 "phase_finished" TIMESTAMPTZ,

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

528 "accepted" TIMESTAMPTZ,

529 "half_frozen" TIMESTAMPTZ,

530 "fully_frozen" TIMESTAMPTZ,

531 "closed" TIMESTAMPTZ,

532 "cleaned" TIMESTAMPTZ,

533 "admission_time" INTERVAL,

534 "discussion_time" INTERVAL NOT NULL,

535 "verification_time" INTERVAL NOT NULL,

536 "voting_time" INTERVAL NOT NULL,

537 "snapshot" TIMESTAMPTZ,

538 "latest_snapshot_event" "snapshot_event",

539 "population" INT4,

540 "voter_count" INT4,

541 "status_quo_schulze_rank" INT4,

542 CONSTRAINT "admission_time_not_null_unless_instantly_accepted" CHECK (

543 "admission_time" NOTNULL OR ("accepted" NOTNULL AND "accepted" = "created") ),

544 CONSTRAINT "valid_state" CHECK (

545 (

546 ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL ) OR

547 ("accepted" NOTNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL ) OR

548 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL ) OR

549 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL)

550 ) AND (

551 ("state" = 'admission' AND "closed" ISNULL AND "accepted" ISNULL) OR

552 ("state" = 'discussion' AND "closed" ISNULL AND "accepted" NOTNULL AND "half_frozen" ISNULL) OR

553 ("state" = 'verification' AND "closed" ISNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL) OR

554 ("state" = 'voting' AND "closed" ISNULL AND "fully_frozen" NOTNULL) OR

555 ("state" = 'canceled_by_admin' AND "closed" NOTNULL) OR

556 ("state" = 'canceled_revoked_before_accepted' AND "closed" NOTNULL AND "accepted" ISNULL) OR

557 ("state" = 'canceled_issue_not_accepted' AND "closed" NOTNULL AND "accepted" ISNULL) OR

558 ("state" = 'canceled_after_revocation_during_discussion' AND "closed" NOTNULL AND "half_frozen" ISNULL) OR

559 ("state" = 'canceled_after_revocation_during_verification' AND "closed" NOTNULL AND "fully_frozen" ISNULL) OR

560 ("state" = 'canceled_no_initiative_admitted' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "closed" = "fully_frozen") OR

561 ("state" = 'finished_without_winner' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "closed" != "fully_frozen") OR

562 ("state" = 'finished_with_winner' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "closed" != "fully_frozen")

563 )),

564 CONSTRAINT "phase_finished_only_when_not_closed" CHECK (

565 "phase_finished" ISNULL OR "closed" ISNULL ),

566 CONSTRAINT "state_change_order" CHECK (

567 "created" <= "accepted" AND

568 "accepted" <= "half_frozen" AND

569 "half_frozen" <= "fully_frozen" AND

570 "fully_frozen" <= "closed" ),

571 CONSTRAINT "only_closed_issues_may_be_cleaned" CHECK (

572 "cleaned" ISNULL OR "closed" NOTNULL ),

573 CONSTRAINT "last_snapshot_on_full_freeze"

574 CHECK ("snapshot" = "fully_frozen"), -- NOTE: snapshot can be set, while frozen is NULL yet

575 CONSTRAINT "freeze_requires_snapshot"

576 CHECK ("fully_frozen" ISNULL OR "snapshot" NOTNULL),

577 CONSTRAINT "set_both_or_none_of_snapshot_and_latest_snapshot_event"

578 CHECK ("snapshot" NOTNULL = "latest_snapshot_event" NOTNULL) );

579 CREATE INDEX "issue_area_id_idx" ON "issue" ("area_id");

580 CREATE INDEX "issue_policy_id_idx" ON "issue" ("policy_id");

581 CREATE INDEX "issue_created_idx" ON "issue" ("created");

582 CREATE INDEX "issue_accepted_idx" ON "issue" ("accepted");

583 CREATE INDEX "issue_half_frozen_idx" ON "issue" ("half_frozen");

584 CREATE INDEX "issue_fully_frozen_idx" ON "issue" ("fully_frozen");

585 CREATE INDEX "issue_closed_idx" ON "issue" ("closed");

586 CREATE INDEX "issue_created_idx_open" ON "issue" ("created") WHERE "closed" ISNULL;

587 CREATE INDEX "issue_closed_idx_canceled" ON "issue" ("closed") WHERE "fully_frozen" ISNULL;

588

589 COMMENT ON TABLE "issue" IS 'Groups of initiatives';

590

591 COMMENT ON COLUMN "issue"."admin_notice" IS 'Public notice by admin to explain manual interventions, or to announce corrections';

592 COMMENT ON COLUMN "issue"."phase_finished" IS 'Set to a value NOTNULL, if the current phase has finished, but calculations are pending; No changes in this issue shall be made by the frontend or API when this value is set';

593 COMMENT ON COLUMN "issue"."accepted" IS 'Point in time, when one initiative of issue reached the "issue_quorum"';

594 COMMENT ON COLUMN "issue"."half_frozen" IS 'Point in time, when "discussion_time" has elapsed; Frontends must ensure that for half_frozen issues a) initiatives are not revoked, b) no new drafts are created, c) no initiators are added or removed.';

595 COMMENT ON COLUMN "issue"."fully_frozen" IS 'Point in time, when "verification_time" has elapsed and voting has started; Frontends must ensure that for fully_frozen issues additionally to the restrictions for half_frozen issues a) initiatives are not created, b) no interest is created or removed, c) no supporters are added or removed, d) no opinions are created, changed or deleted.';

596 COMMENT ON COLUMN "issue"."closed" IS 'Point in time, when "admission_time" or "voting_time" have elapsed, and issue is no longer active; Frontends must ensure that for closed issues additionally to the restrictions for half_frozen and fully_frozen issues a) no voter is added or removed to/from the direct_voter table, b) no votes are added, modified or removed.';

597 COMMENT ON COLUMN "issue"."cleaned" IS 'Point in time, when discussion data and votes had been deleted';

598 COMMENT ON COLUMN "issue"."admission_time" IS 'Copied from "policy" table at creation of issue';

599 COMMENT ON COLUMN "issue"."discussion_time" IS 'Copied from "policy" table at creation of issue';

600 COMMENT ON COLUMN "issue"."verification_time" IS 'Copied from "policy" table at creation of issue';

601 COMMENT ON COLUMN "issue"."voting_time" IS 'Copied from "policy" table at creation of issue';

602 COMMENT ON COLUMN "issue"."snapshot" IS 'Point in time, when snapshot tables have been updated and "population" and *_count values were precalculated';

603 COMMENT ON COLUMN "issue"."latest_snapshot_event" IS 'Event type of latest snapshot for issue; Can be used to select the latest snapshot data in the snapshot tables';

604 COMMENT ON COLUMN "issue"."population" IS 'Sum of "weight" column in table "direct_population_snapshot"';

605 COMMENT ON COLUMN "issue"."voter_count" IS 'Total number of direct and delegating voters; This value is related to the final voting, while "population" is related to snapshots before the final voting';

606 COMMENT ON COLUMN "issue"."status_quo_schulze_rank" IS 'Schulze rank of status quo, as calculated by "calculate_ranks" function';

607

608

609 CREATE TABLE "issue_order_in_admission_state" (

610 "id" INT8 PRIMARY KEY, --REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,

611 "order_in_area" INT4,

612 "order_in_unit" INT4 );

613

614 COMMENT ON TABLE "issue_order_in_admission_state" IS 'Ordering information for issues that are not stored in the "issue" table to avoid locking of multiple issues at once; Filled/updated by "lf_update_issue_order"';

615

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

617 COMMENT ON COLUMN "issue_order_in_admission_state"."order_in_area" IS 'Order of issues in admission state within a single area; NULL values sort last';

618 COMMENT ON COLUMN "issue_order_in_admission_state"."order_in_unit" IS 'Order of issues in admission state within all areas of a unit; NULL values sort last';

619

620

621 CREATE TABLE "issue_setting" (

622 PRIMARY KEY ("member_id", "key", "issue_id"),

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

624 "key" TEXT NOT NULL,

625 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,

626 "value" TEXT NOT NULL );

627

628 COMMENT ON TABLE "issue_setting" IS 'Place for frontend to store issue specific settings of members as strings';

629

630

631 CREATE TABLE "initiative" (

632 UNIQUE ("issue_id", "id"), -- index needed for foreign-key on table "vote"

633 "issue_id" INT4 NOT NULL REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,

634 "id" SERIAL4 PRIMARY KEY,

635 "name" TEXT NOT NULL,

636 "polling" BOOLEAN NOT NULL DEFAULT FALSE,

637 "discussion_url" TEXT,

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

639 "revoked" TIMESTAMPTZ,

640 "revoked_by_member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,

641 "suggested_initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,

642 "admitted" BOOLEAN,

643 "supporter_count" INT4,

644 "informed_supporter_count" INT4,

645 "satisfied_supporter_count" INT4,

646 "satisfied_informed_supporter_count" INT4,

647 "harmonic_weight" NUMERIC(12, 3),

648 "final_suggestion_order_calculated" BOOLEAN NOT NULL DEFAULT FALSE,

649 "positive_votes" INT4,

650 "negative_votes" INT4,

651 "direct_majority" BOOLEAN,

652 "indirect_majority" BOOLEAN,

653 "schulze_rank" INT4,

654 "better_than_status_quo" BOOLEAN,

655 "worse_than_status_quo" BOOLEAN,

656 "reverse_beat_path" BOOLEAN,

657 "multistage_majority" BOOLEAN,

658 "eligible" BOOLEAN,

659 "winner" BOOLEAN,

660 "rank" INT4,

661 "text_search_data" TSVECTOR,

662 CONSTRAINT "all_or_none_of_revoked_and_revoked_by_member_id_must_be_null"

663 CHECK ("revoked" NOTNULL = "revoked_by_member_id" NOTNULL),

664 CONSTRAINT "non_revoked_initiatives_cant_suggest_other"

665 CHECK ("revoked" NOTNULL OR "suggested_initiative_id" ISNULL),

666 CONSTRAINT "revoked_initiatives_cant_be_admitted"

667 CHECK ("revoked" ISNULL OR "admitted" ISNULL),

668 CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results" CHECK (

669 ( "admitted" NOTNULL AND "admitted" = TRUE ) OR

670 ( "positive_votes" ISNULL AND "negative_votes" ISNULL AND

671 "direct_majority" ISNULL AND "indirect_majority" ISNULL AND

672 "schulze_rank" ISNULL AND

673 "better_than_status_quo" ISNULL AND "worse_than_status_quo" ISNULL AND

674 "reverse_beat_path" ISNULL AND "multistage_majority" ISNULL AND

675 "eligible" ISNULL AND "winner" ISNULL AND "rank" ISNULL ) ),

676 CONSTRAINT "better_excludes_worse" CHECK (NOT ("better_than_status_quo" AND "worse_than_status_quo")),

677 CONSTRAINT "minimum_requirement_to_be_eligible" CHECK (

678 "eligible" = FALSE OR

679 ("direct_majority" AND "indirect_majority" AND "better_than_status_quo") ),

680 CONSTRAINT "winner_must_be_eligible" CHECK ("winner"=FALSE OR "eligible"=TRUE),

681 CONSTRAINT "winner_must_have_first_rank" CHECK ("winner"=FALSE OR "rank"=1),

682 CONSTRAINT "eligible_at_first_rank_is_winner" CHECK ("eligible"=FALSE OR "rank"!=1 OR "winner"=TRUE),

683 CONSTRAINT "unique_rank_per_issue" UNIQUE ("issue_id", "rank") );

684 CREATE INDEX "initiative_created_idx" ON "initiative" ("created");

685 CREATE INDEX "initiative_revoked_idx" ON "initiative" ("revoked");

686 CREATE INDEX "initiative_text_search_data_idx" ON "initiative" USING gin ("text_search_data");

687 CREATE TRIGGER "update_text_search_data"

688 BEFORE INSERT OR UPDATE ON "initiative"

689 FOR EACH ROW EXECUTE PROCEDURE

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

691 "name", "discussion_url");

692

693 COMMENT ON TABLE "initiative" IS 'Group of members publishing drafts for resolutions to be passed; Frontends must ensure that initiatives of half_frozen issues are not revoked, and that initiatives of fully_frozen or closed issues are neither revoked nor created.';

694

695 COMMENT ON COLUMN "initiative"."polling" IS 'Initiative does not need to pass the initiative quorum (see "policy"."polling")';

696 COMMENT ON COLUMN "initiative"."discussion_url" IS 'URL pointing to a discussion platform for this initiative';

697 COMMENT ON COLUMN "initiative"."revoked" IS 'Point in time, when one initiator decided to revoke the initiative';

698 COMMENT ON COLUMN "initiative"."revoked_by_member_id" IS 'Member, who decided to revoke the initiative';

699 COMMENT ON COLUMN "initiative"."admitted" IS 'TRUE, if initiative reaches the "initiative_quorum" when freezing the issue';

700 COMMENT ON COLUMN "initiative"."supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';

701 COMMENT ON COLUMN "initiative"."informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';

702 COMMENT ON COLUMN "initiative"."satisfied_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';

703 COMMENT ON COLUMN "initiative"."satisfied_informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';

704 COMMENT ON COLUMN "initiative"."harmonic_weight" IS 'Indicates the relevancy of the initiative, calculated from the potential supporters weighted with the harmonic series to avoid a large number of clones affecting other initiative''s sorting positions too much; shall be used as secondary sorting key after "admitted" as primary sorting key';

705 COMMENT ON COLUMN "initiative"."final_suggestion_order_calculated" IS 'Set to TRUE, when "proportional_order" of suggestions has been calculated the last time';

706 COMMENT ON COLUMN "initiative"."positive_votes" IS 'Calculated from table "direct_voter"';

707 COMMENT ON COLUMN "initiative"."negative_votes" IS 'Calculated from table "direct_voter"';

708 COMMENT ON COLUMN "initiative"."direct_majority" IS 'TRUE, if "positive_votes"/("positive_votes"+"negative_votes") is strictly greater or greater-equal than "direct_majority_num"/"direct_majority_den", and "positive_votes" is greater-equal than "direct_majority_positive", and ("positive_votes"+abstentions) is greater-equal than "direct_majority_non_negative"';

709 COMMENT ON COLUMN "initiative"."indirect_majority" IS 'Same as "direct_majority", but also considering indirect beat paths';

710 COMMENT ON COLUMN "initiative"."schulze_rank" IS 'Schulze-Ranking';

711 COMMENT ON COLUMN "initiative"."better_than_status_quo" IS 'TRUE, if initiative has a schulze-ranking better than the status quo';

712 COMMENT ON COLUMN "initiative"."worse_than_status_quo" IS 'TRUE, if initiative has a schulze-ranking worse than the status quo (DEPRECATED, since schulze-ranking is unique per issue; use "better_than_status_quo"=FALSE)';

713 COMMENT ON COLUMN "initiative"."reverse_beat_path" IS 'TRUE, if there is a beat path (may include ties) from this initiative to the status quo';

714 COMMENT ON COLUMN "initiative"."multistage_majority" IS 'TRUE, if either (a) this initiative has no better rank than the status quo, or (b) there exists a better ranked initiative X, which directly beats this initiative, and either more voters prefer X to this initiative than voters preferring X to the status quo or less voters prefer this initiative to X than voters preferring the status quo to X';

715 COMMENT ON COLUMN "initiative"."eligible" IS 'Initiative has a "direct_majority" and an "indirect_majority", is "better_than_status_quo" and depending on selected policy the initiative has no "reverse_beat_path" or "multistage_majority"';

716 COMMENT ON COLUMN "initiative"."winner" IS 'Winner is the "eligible" initiative with best "schulze_rank"';

717 COMMENT ON COLUMN "initiative"."rank" IS 'Unique ranking for all "admitted" initiatives per issue; lower rank is better; a winner always has rank 1, but rank 1 does not imply that an initiative is winner; initiatives with "direct_majority" AND "indirect_majority" always have a better (lower) rank than other initiatives';

718

719

720 CREATE TABLE "battle" (

721 "issue_id" INT4 NOT NULL,

722 "winning_initiative_id" INT4,

723 FOREIGN KEY ("issue_id", "winning_initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,

724 "losing_initiative_id" INT4,

725 FOREIGN KEY ("issue_id", "losing_initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,

726 "count" INT4 NOT NULL,

727 CONSTRAINT "initiative_ids_not_equal" CHECK (

728 "winning_initiative_id" != "losing_initiative_id" OR

729 ( ("winning_initiative_id" NOTNULL AND "losing_initiative_id" ISNULL) OR

730 ("winning_initiative_id" ISNULL AND "losing_initiative_id" NOTNULL) ) ) );

731 CREATE UNIQUE INDEX "battle_winning_losing_idx" ON "battle" ("issue_id", "winning_initiative_id", "losing_initiative_id");

732 CREATE UNIQUE INDEX "battle_winning_null_idx" ON "battle" ("issue_id", "winning_initiative_id") WHERE "losing_initiative_id" ISNULL;

733 CREATE UNIQUE INDEX "battle_null_losing_idx" ON "battle" ("issue_id", "losing_initiative_id") WHERE "winning_initiative_id" ISNULL;

734

735 COMMENT ON TABLE "battle" IS 'Number of members preferring one initiative to another; Filled by "battle_view" when closing an issue; NULL as initiative_id denotes virtual "status-quo" initiative';

736

737

738 CREATE TABLE "ignored_initiative" (

739 PRIMARY KEY ("initiative_id", "member_id"),

740 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,

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

742 CREATE INDEX "ignored_initiative_member_id_idx" ON "ignored_initiative" ("member_id");

743

744 COMMENT ON TABLE "ignored_initiative" IS 'Possibility to filter initiatives';

745

746

747 CREATE TABLE "initiative_setting" (

748 PRIMARY KEY ("member_id", "key", "initiative_id"),

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

750 "key" TEXT NOT NULL,

751 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,

752 "value" TEXT NOT NULL );

753

754 COMMENT ON TABLE "initiative_setting" IS 'Place for frontend to store initiative specific settings of members as strings';

755

756

757 CREATE TABLE "draft" (

758 UNIQUE ("initiative_id", "id"), -- index needed for foreign-key on table "supporter"

759 "initiative_id" INT4 NOT NULL REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,

760 "id" SERIAL8 PRIMARY KEY,

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

762 "author_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,

763 "formatting_engine" TEXT,

764 "content" TEXT NOT NULL,

765 "text_search_data" TSVECTOR );

766 CREATE INDEX "draft_created_idx" ON "draft" ("created");

767 CREATE INDEX "draft_author_id_created_idx" ON "draft" ("author_id", "created");

768 CREATE INDEX "draft_text_search_data_idx" ON "draft" USING gin ("text_search_data");

769 CREATE TRIGGER "update_text_search_data"

770 BEFORE INSERT OR UPDATE ON "draft"

771 FOR EACH ROW EXECUTE PROCEDURE

772 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "content");

773

774 COMMENT ON TABLE "draft" IS 'Drafts of initiatives to solve issues; Frontends must ensure that new drafts for initiatives of half_frozen, fully_frozen or closed issues can''t be created.';

775

776 COMMENT ON COLUMN "draft"."formatting_engine" IS 'Allows different formatting engines (i.e. wiki formats) to be used';

777 COMMENT ON COLUMN "draft"."content" IS 'Text of the draft in a format depending on the field "formatting_engine"';

778

779

780 CREATE TABLE "rendered_draft" (

781 PRIMARY KEY ("draft_id", "format"),

782 "draft_id" INT8 REFERENCES "draft" ("id") ON DELETE CASCADE ON UPDATE CASCADE,

783 "format" TEXT,

784 "content" TEXT NOT NULL );

785

786 COMMENT ON TABLE "rendered_draft" IS 'This table may be used by frontends to cache "rendered" drafts (e.g. HTML output generated from wiki text)';

787

788

789 CREATE TABLE "suggestion" (

790 UNIQUE ("initiative_id", "id"), -- index needed for foreign-key on table "opinion"

791 "initiative_id" INT4 NOT NULL REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,

792 "id" SERIAL8 PRIMARY KEY,

793 "draft_id" INT8 NOT NULL,

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

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

796 "author_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,

797 "name" TEXT NOT NULL,

798 "formatting_engine" TEXT,

799 "content" TEXT NOT NULL DEFAULT '',

800 "text_search_data" TSVECTOR,

801 "minus2_unfulfilled_count" INT4,

802 "minus2_fulfilled_count" INT4,

803 "minus1_unfulfilled_count" INT4,

804 "minus1_fulfilled_count" INT4,

805 "plus1_unfulfilled_count" INT4,

806 "plus1_fulfilled_count" INT4,

807 "plus2_unfulfilled_count" INT4,

808 "plus2_fulfilled_count" INT4,

809 "proportional_order" INT4 );

810 CREATE INDEX "suggestion_created_idx" ON "suggestion" ("created");

811 CREATE INDEX "suggestion_author_id_created_idx" ON "suggestion" ("author_id", "created");

812 CREATE INDEX "suggestion_text_search_data_idx" ON "suggestion" USING gin ("text_search_data");

813 CREATE TRIGGER "update_text_search_data"

814 BEFORE INSERT OR UPDATE ON "suggestion"

815 FOR EACH ROW EXECUTE PROCEDURE

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

817 "name", "content");

818

819 COMMENT ON TABLE "suggestion" IS 'Suggestions to initiators, to change the current draft; must not be deleted explicitly, as they vanish automatically if the last opinion is deleted';

820

821 COMMENT ON COLUMN "suggestion"."draft_id" IS 'Draft, which the author has seen when composing the suggestion; should always be set by a frontend, but defaults to current draft of the initiative (implemented by trigger "default_for_draft_id")';

822 COMMENT ON COLUMN "suggestion"."minus2_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';

823 COMMENT ON COLUMN "suggestion"."minus2_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';

824 COMMENT ON COLUMN "suggestion"."minus1_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';

825 COMMENT ON COLUMN "suggestion"."minus1_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';

826 COMMENT ON COLUMN "suggestion"."plus1_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';

827 COMMENT ON COLUMN "suggestion"."plus1_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';

828 COMMENT ON COLUMN "suggestion"."plus2_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';

829 COMMENT ON COLUMN "suggestion"."plus2_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';

830 COMMENT ON COLUMN "suggestion"."proportional_order" IS 'To be used for sorting suggestions within an initiative; NULL values sort last; updated by "lf_update_suggestion_order"';

831

832

833 CREATE TABLE "rendered_suggestion" (

834 PRIMARY KEY ("suggestion_id", "format"),

835 "suggestion_id" INT8 REFERENCES "suggestion" ("id") ON DELETE CASCADE ON UPDATE CASCADE,

836 "format" TEXT,

837 "content" TEXT NOT NULL );

838

839 COMMENT ON TABLE "rendered_suggestion" IS 'This table may be used by frontends to cache "rendered" drafts (e.g. HTML output generated from wiki text)';

840

841

842 CREATE TABLE "suggestion_setting" (

843 PRIMARY KEY ("member_id", "key", "suggestion_id"),

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

845 "key" TEXT NOT NULL,

846 "suggestion_id" INT8 REFERENCES "suggestion" ("id") ON DELETE CASCADE ON UPDATE CASCADE,

847 "value" TEXT NOT NULL );

848

849 COMMENT ON TABLE "suggestion_setting" IS 'Place for frontend to store suggestion specific settings of members as strings';

850

851

852 CREATE TABLE "privilege" (

853 PRIMARY KEY ("unit_id", "member_id"),

854 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,

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

856 "admin_manager" BOOLEAN NOT NULL DEFAULT FALSE,

857 "unit_manager" BOOLEAN NOT NULL DEFAULT FALSE,

858 "area_manager" BOOLEAN NOT NULL DEFAULT FALSE,

859 "member_manager" BOOLEAN NOT NULL DEFAULT FALSE,

860 "initiative_right" BOOLEAN NOT NULL DEFAULT TRUE,

861 "voting_right" BOOLEAN NOT NULL DEFAULT TRUE,

862 "polling_right" BOOLEAN NOT NULL DEFAULT FALSE );

863

864 COMMENT ON TABLE "privilege" IS 'Members rights related to each unit';

865

866 COMMENT ON COLUMN "privilege"."admin_manager" IS 'Grant/revoke any privileges to/from other members';

867 COMMENT ON COLUMN "privilege"."unit_manager" IS 'Create and disable sub units';

868 COMMENT ON COLUMN "privilege"."area_manager" IS 'Create and disable areas and set area parameters';

869 COMMENT ON COLUMN "privilege"."member_manager" IS 'Adding/removing members from the unit, granting or revoking "initiative_right" and "voting_right"';

870 COMMENT ON COLUMN "privilege"."initiative_right" IS 'Right to create an initiative';

871 COMMENT ON COLUMN "privilege"."voting_right" IS 'Right to support initiatives, create and rate suggestions, and to vote';

872 COMMENT ON COLUMN "privilege"."polling_right" IS 'Right to create issues with policies having the "policy"."polling" flag set, and to add initiatives having the "initiative"."polling" flag set to those issues';

873

874

875 CREATE TABLE "membership" (

876 PRIMARY KEY ("area_id", "member_id"),

877 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,

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

879 CREATE INDEX "membership_member_id_idx" ON "membership" ("member_id");

880

881 COMMENT ON TABLE "membership" IS 'Interest of members in topic areas';

882

883

884 CREATE TABLE "interest" (

885 PRIMARY KEY ("issue_id", "member_id"),

886 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,

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

888 CREATE INDEX "interest_member_id_idx" ON "interest" ("member_id");

889

890 COMMENT ON TABLE "interest" IS 'Interest of members in a particular issue; Frontends must ensure that interest for fully_frozen or closed issues is not added or removed.';

891

892

893 CREATE TABLE "initiator" (

894 PRIMARY KEY ("initiative_id", "member_id"),

895 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,

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

897 "accepted" BOOLEAN );

898 CREATE INDEX "initiator_member_id_idx" ON "initiator" ("member_id");

899

900 COMMENT ON TABLE "initiator" IS 'Members who are allowed to post new drafts; Frontends must ensure that initiators are not added or removed from half_frozen, fully_frozen or closed initiatives.';

901

902 COMMENT ON COLUMN "initiator"."accepted" IS 'If "accepted" is NULL, then the member was invited to be a co-initiator, but has not answered yet. If it is TRUE, the member has accepted the invitation, if it is FALSE, the member has rejected the invitation.';

903

904

905 CREATE TABLE "supporter" (

906 "issue_id" INT4 NOT NULL,

907 PRIMARY KEY ("initiative_id", "member_id"),

908 "initiative_id" INT4,

909 "member_id" INT4,

910 "draft_id" INT8 NOT NULL,

911 FOREIGN KEY ("issue_id", "member_id") REFERENCES "interest" ("issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE,

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

913 CREATE INDEX "supporter_member_id_idx" ON "supporter" ("member_id");

914

915 COMMENT ON TABLE "supporter" IS 'Members who support an initiative (conditionally); Frontends must ensure that supporters are not added or removed from fully_frozen or closed initiatives.';

916

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

918 COMMENT ON COLUMN "supporter"."draft_id" IS 'Latest seen draft; should always be set by a frontend, but defaults to current draft of the initiative (implemented by trigger "default_for_draft_id")';

919

920

921 CREATE TABLE "opinion" (

922 "initiative_id" INT4 NOT NULL,

923 PRIMARY KEY ("suggestion_id", "member_id"),

924 "suggestion_id" INT8,

925 "member_id" INT4,

926 "degree" INT2 NOT NULL CHECK ("degree" >= -2 AND "degree" <= 2 AND "degree" != 0),

927 "fulfilled" BOOLEAN NOT NULL DEFAULT FALSE,

928 FOREIGN KEY ("initiative_id", "suggestion_id") REFERENCES "suggestion" ("initiative_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,

929 FOREIGN KEY ("initiative_id", "member_id") REFERENCES "supporter" ("initiative_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );

930 CREATE INDEX "opinion_member_id_initiative_id_idx" ON "opinion" ("member_id", "initiative_id");

931

932 COMMENT ON TABLE "opinion" IS 'Opinion on suggestions (criticism related to initiatives); Frontends must ensure that opinions are not created modified or deleted when related to fully_frozen or closed issues.';

933

934 COMMENT ON COLUMN "opinion"."degree" IS '2 = fulfillment required for support; 1 = fulfillment desired; -1 = fulfillment unwanted; -2 = fulfillment cancels support';

935

936

937 CREATE TYPE "delegation_scope" AS ENUM ('unit', 'area', 'issue');

938

939 COMMENT ON TYPE "delegation_scope" IS 'Scope for delegations: ''unit'', ''area'', or ''issue'' (order is relevant)';

940

941

942 CREATE TABLE "delegation" (

943 "id" SERIAL8 PRIMARY KEY,

944 "truster_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,

945 "trustee_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,

946 "scope" "delegation_scope" NOT NULL,

947 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,

948 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,

949 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,

950 CONSTRAINT "cant_delegate_to_yourself" CHECK ("truster_id" != "trustee_id"),

951 CONSTRAINT "no_unit_delegation_to_null"

952 CHECK ("trustee_id" NOTNULL OR "scope" != 'unit'),

953 CONSTRAINT "area_id_and_issue_id_set_according_to_scope" CHECK (

954 ("scope" = 'unit' AND "unit_id" NOTNULL AND "area_id" ISNULL AND "issue_id" ISNULL ) OR

955 ("scope" = 'area' AND "unit_id" ISNULL AND "area_id" NOTNULL AND "issue_id" ISNULL ) OR

956 ("scope" = 'issue' AND "unit_id" ISNULL AND "area_id" ISNULL AND "issue_id" NOTNULL) ),

957 UNIQUE ("unit_id", "truster_id"),

958 UNIQUE ("area_id", "truster_id"),

959 UNIQUE ("issue_id", "truster_id") );

960 CREATE INDEX "delegation_truster_id_idx" ON "delegation" ("truster_id");

961 CREATE INDEX "delegation_trustee_id_idx" ON "delegation" ("trustee_id");

962

963 COMMENT ON TABLE "delegation" IS 'Delegation of vote-weight to other members';

964

965 COMMENT ON COLUMN "delegation"."unit_id" IS 'Reference to unit, if delegation is unit-wide, otherwise NULL';

966 COMMENT ON COLUMN "delegation"."area_id" IS 'Reference to area, if delegation is area-wide, otherwise NULL';

967 COMMENT ON COLUMN "delegation"."issue_id" IS 'Reference to issue, if delegation is issue-wide, otherwise NULL';

968

969

970 CREATE TABLE "direct_population_snapshot" (

971 PRIMARY KEY ("issue_id", "event", "member_id"),

972 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,

973 "event" "snapshot_event",

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

975 "weight" INT4 );

976 CREATE INDEX "direct_population_snapshot_member_id_idx" ON "direct_population_snapshot" ("member_id");

977

978 COMMENT ON TABLE "direct_population_snapshot" IS 'Snapshot of active members having either a "membership" in the "area" or an "interest" in the "issue"; for corrections refer to column "issue_notice" of "issue" table';

979

980 COMMENT ON COLUMN "direct_population_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';

981 COMMENT ON COLUMN "direct_population_snapshot"."weight" IS 'Weight of member (1 or higher) according to "delegating_population_snapshot"';

982

983

984 CREATE TABLE "delegating_population_snapshot" (

985 PRIMARY KEY ("issue_id", "event", "member_id"),

986 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,

987 "event" "snapshot_event",

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

989 "weight" INT4,

990 "scope" "delegation_scope" NOT NULL,

991 "delegate_member_ids" INT4[] NOT NULL );

992 CREATE INDEX "delegating_population_snapshot_member_id_idx" ON "delegating_population_snapshot" ("member_id");

993

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

995

996 COMMENT ON COLUMN "delegating_population_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';

997 COMMENT ON COLUMN "delegating_population_snapshot"."member_id" IS 'Delegating member';

998 COMMENT ON COLUMN "delegating_population_snapshot"."weight" IS 'Intermediate weight';

999 COMMENT ON COLUMN "delegating_population_snapshot"."delegate_member_ids" IS 'Chain of members who act as delegates; last entry referes to "member_id" column of table "direct_population_snapshot"';

1000

1001

1002 CREATE TABLE "direct_interest_snapshot" (

1003 PRIMARY KEY ("issue_id", "event", "member_id"),

1004 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,

1005 "event" "snapshot_event",

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

1007 "weight" INT4 );

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

1009

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

1011

1012 COMMENT ON COLUMN "direct_interest_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';

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

1014

1015

1016 CREATE TABLE "delegating_interest_snapshot" (

1017 PRIMARY KEY ("issue_id", "event", "member_id"),

1018 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,

1019 "event" "snapshot_event",

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

1021 "weight" INT4,

1022 "scope" "delegation_scope" NOT NULL,

1023 "delegate_member_ids" INT4[] NOT NULL );

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

1025

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

1027

1028 COMMENT ON COLUMN "delegating_interest_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';

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

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

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

1032

1033

1034 CREATE TABLE "direct_supporter_snapshot" (

1035 "issue_id" INT4 NOT NULL,

1036 PRIMARY KEY ("initiative_id", "event", "member_id"),

1037 "initiative_id" INT4,

1038 "event" "snapshot_event",

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

1040 "draft_id" INT8 NOT NULL,

1041 "informed" BOOLEAN NOT NULL,

1042 "satisfied" BOOLEAN NOT NULL,

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

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

1045 FOREIGN KEY ("issue_id", "event", "member_id") REFERENCES "direct_interest_snapshot" ("issue_id", "event", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );

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

1047

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

1049

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

1051 COMMENT ON COLUMN "direct_supporter_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';

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

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

1054

1055

1056 CREATE TABLE "non_voter" (

1057 PRIMARY KEY ("issue_id", "member_id"),

1058 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,

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

1060 CREATE INDEX "non_voter_member_id_idx" ON "non_voter" ("member_id");

1061

1062 COMMENT ON TABLE "non_voter" IS 'Members who decided to not vote directly on an issue';

1063

1064

1065 CREATE TABLE "direct_voter" (

1066 PRIMARY KEY ("issue_id", "member_id"),

1067 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,

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

1069 "weight" INT4,

1070 "comment_changed" TIMESTAMPTZ,

1071 "formatting_engine" TEXT,

1072 "comment" TEXT,

1073 "text_search_data" TSVECTOR );

1074 CREATE INDEX "direct_voter_member_id_idx" ON "direct_voter" ("member_id");

1075 CREATE INDEX "direct_voter_text_search_data_idx" ON "direct_voter" USING gin ("text_search_data");

1076 CREATE TRIGGER "update_text_search_data"

1077 BEFORE INSERT OR UPDATE ON "direct_voter"

1078 FOR EACH ROW EXECUTE PROCEDURE

1079 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "comment");

1080

1081 COMMENT ON TABLE "direct_voter" IS 'Members having directly voted for/against initiatives of an issue; frontends must ensure that no voters are added or removed to/from this table when the issue has been closed; for corrections refer to column "issue_notice" of "issue" table';

1082

1083 COMMENT ON COLUMN "direct_voter"."weight" IS 'Weight of member (1 or higher) according to "delegating_voter" table';

1084 COMMENT ON COLUMN "direct_voter"."comment_changed" IS 'Shall be set on comment change, to indicate a comment being modified after voting has been finished; Automatically set to NULL after voting phase; Automatically set to NULL by trigger, if "comment" is set to NULL';

1085 COMMENT ON COLUMN "direct_voter"."formatting_engine" IS 'Allows different formatting engines (i.e. wiki formats) to be used for "direct_voter"."comment"; Automatically set to NULL by trigger, if "comment" is set to NULL';

1086 COMMENT ON COLUMN "direct_voter"."comment" IS 'Is to be set or updated by the frontend, if comment was inserted or updated AFTER the issue has been closed. Otherwise it shall be set to NULL.';

1087

1088

1089 CREATE TABLE "rendered_voter_comment" (

1090 PRIMARY KEY ("issue_id", "member_id", "format"),

1091 FOREIGN KEY ("issue_id", "member_id")

1092 REFERENCES "direct_voter" ("issue_id", "member_id")

1093 ON DELETE CASCADE ON UPDATE CASCADE,

1094 "issue_id" INT4,

1095 "member_id" INT4,

1096 "format" TEXT,

1097 "content" TEXT NOT NULL );

1098

1099 COMMENT ON TABLE "rendered_voter_comment" IS 'This table may be used by frontends to cache "rendered" voter comments (e.g. HTML output generated from wiki text)';

1100

1101

1102 CREATE TABLE "delegating_voter" (

1103 PRIMARY KEY ("issue_id", "member_id"),

1104 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,

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

1106 "weight" INT4,

1107 "scope" "delegation_scope" NOT NULL,

1108 "delegate_member_ids" INT4[] NOT NULL );

1109 CREATE INDEX "delegating_voter_member_id_idx" ON "delegating_voter" ("member_id");

1110

1111 COMMENT ON TABLE "delegating_voter" IS 'Delegations increasing the weight of entries in the "direct_voter" table; for corrections refer to column "issue_notice" of "issue" table';

1112

1113 COMMENT ON COLUMN "delegating_voter"."member_id" IS 'Delegating member';

1114 COMMENT ON COLUMN "delegating_voter"."weight" IS 'Intermediate weight';

1115 COMMENT ON COLUMN "delegating_voter"."delegate_member_ids" IS 'Chain of members who act as delegates; last entry referes to "member_id" column of table "direct_voter"';

1116

1117

1118 CREATE TABLE "vote" (

1119 "issue_id" INT4 NOT NULL,

1120 PRIMARY KEY ("initiative_id", "member_id"),

1121 "initiative_id" INT4,

1122 "member_id" INT4,

1123 "grade" INT4,

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

1125 FOREIGN KEY ("issue_id", "member_id") REFERENCES "direct_voter" ("issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );

1126 CREATE INDEX "vote_member_id_idx" ON "vote" ("member_id");

1127

1128 COMMENT ON TABLE "vote" IS 'Manual and delegated votes without abstentions; frontends must ensure that no votes are added modified or removed when the issue has been closed; for corrections refer to column "issue_notice" of "issue" table';

1129

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

1131 COMMENT ON COLUMN "vote"."grade" IS 'Values smaller than zero mean reject, values greater than zero mean acceptance, zero or missing row means abstention. Preferences are expressed by different positive or negative numbers.';

1132

1133

1134 CREATE TYPE "event_type" AS ENUM (

1135 'issue_state_changed',

1136 'initiative_created_in_new_issue',

1137 'initiative_created_in_existing_issue',

1138 'initiative_revoked',

1139 'new_draft_created',

1140 'suggestion_created');

1141

1142 COMMENT ON TYPE "event_type" IS 'Type used for column "event" of table "event"';

1143

1144

1145 CREATE TABLE "event" (

1146 "id" SERIAL8 PRIMARY KEY,

1147 "occurrence" TIMESTAMPTZ NOT NULL DEFAULT now(),

1148 "event" "event_type" NOT NULL,

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

1150 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,

1151 "state" "issue_state",

1152 "initiative_id" INT4,

1153 "draft_id" INT8,

1154 "suggestion_id" INT8,

1155 FOREIGN KEY ("issue_id", "initiative_id")

1156 REFERENCES "initiative" ("issue_id", "id")

1157 ON DELETE CASCADE ON UPDATE CASCADE,

1158 FOREIGN KEY ("initiative_id", "draft_id")

1159 REFERENCES "draft" ("initiative_id", "id")

1160 ON DELETE CASCADE ON UPDATE CASCADE,

1161 FOREIGN KEY ("initiative_id", "suggestion_id")

1162 REFERENCES "suggestion" ("initiative_id", "id")

1163 ON DELETE CASCADE ON UPDATE CASCADE,

1164 CONSTRAINT "null_constraints_for_issue_state_changed" CHECK (

1165 "event" != 'issue_state_changed' OR (

1166 "member_id" ISNULL AND

1167 "issue_id" NOTNULL AND

1168 "state" NOTNULL AND

1169 "initiative_id" ISNULL AND

1170 "draft_id" ISNULL AND

1171 "suggestion_id" ISNULL )),

1172 CONSTRAINT "null_constraints_for_initiative_creation_or_revocation_or_new_draft" CHECK (

1173 "event" NOT IN (

1174 'initiative_created_in_new_issue',

1175 'initiative_created_in_existing_issue',

1176 'initiative_revoked',

1177 'new_draft_created'

1178 ) OR (

1179 "member_id" NOTNULL AND

1180 "issue_id" NOTNULL AND

1181 "state" NOTNULL AND

1182 "initiative_id" NOTNULL AND

1183 "draft_id" NOTNULL AND

1184 "suggestion_id" ISNULL )),

1185 CONSTRAINT "null_constraints_for_suggestion_creation" CHECK (

1186 "event" != 'suggestion_created' OR (

1187 "member_id" NOTNULL AND

1188 "issue_id" NOTNULL AND

1189 "state" NOTNULL AND

1190 "initiative_id" NOTNULL AND

1191 "draft_id" ISNULL AND

1192 "suggestion_id" NOTNULL )) );

1193 CREATE INDEX "event_occurrence_idx" ON "event" ("occurrence");

1194

1195 COMMENT ON TABLE "event" IS 'Event table, automatically filled by triggers';

1196

1197 COMMENT ON COLUMN "event"."occurrence" IS 'Point in time, when event occurred';

1198 COMMENT ON COLUMN "event"."event" IS 'Type of event (see TYPE "event_type")';

1199 COMMENT ON COLUMN "event"."member_id" IS 'Member who caused the event, if applicable';

1200 COMMENT ON COLUMN "event"."state" IS 'If issue_id is set: state of affected issue; If state changed: new state';

1201

1202

1203 CREATE TABLE "notification_sent" (

1204 "event_id" INT8 NOT NULL );

1205 CREATE UNIQUE INDEX "notification_sent_singleton_idx" ON "notification_sent" ((1));

1206

1207 COMMENT ON TABLE "notification_sent" IS 'This table stores one row with the last event_id, for which notifications have been sent out';

1208 COMMENT ON INDEX "notification_sent_singleton_idx" IS 'This index ensures that "notification_sent" only contains one row maximum.';

1209

1210

1211

1212 ----------------------------------------------

1213 -- Writing of history entries and event log --

1214 ----------------------------------------------

1215

1216

1217 CREATE FUNCTION "write_member_history_trigger"()

1218 RETURNS TRIGGER

1219 LANGUAGE 'plpgsql' VOLATILE AS $$

1220 BEGIN

1221 IF

1222 ( NEW."active" != OLD."active" OR

1223 NEW."name" != OLD."name" ) AND

1224 OLD."activated" NOTNULL

1225 THEN

1226 INSERT INTO "member_history"

1227 ("member_id", "active", "name")

1228 VALUES (NEW."id", OLD."active", OLD."name");

1229 END IF;

1230 RETURN NULL;

1231 END;

1232 $$;

1233

1234 CREATE TRIGGER "write_member_history"

1235 AFTER UPDATE ON "member" FOR EACH ROW EXECUTE PROCEDURE

1236 "write_member_history_trigger"();

1237

1238 COMMENT ON FUNCTION "write_member_history_trigger"() IS 'Implementation of trigger "write_member_history" on table "member"';

1239 COMMENT ON TRIGGER "write_member_history" ON "member" IS 'When changing certain fields of a member, create a history entry in "member_history" table';

1240

1241

1242 CREATE FUNCTION "write_event_issue_state_changed_trigger"()

1243 RETURNS TRIGGER

1244 LANGUAGE 'plpgsql' VOLATILE AS $$

1245 BEGIN

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

1247 INSERT INTO "event" ("event", "issue_id", "state")

1248 VALUES ('issue_state_changed', NEW."id", NEW."state");

1249 END IF;

1250 RETURN NULL;

1251 END;

1252 $$;

1253

1254 CREATE TRIGGER "write_event_issue_state_changed"

1255 AFTER UPDATE ON "issue" FOR EACH ROW EXECUTE PROCEDURE

1256 "write_event_issue_state_changed_trigger"();

1257

1258 COMMENT ON FUNCTION "write_event_issue_state_changed_trigger"() IS 'Implementation of trigger "write_event_issue_state_changed" on table "issue"';

1259 COMMENT ON TRIGGER "write_event_issue_state_changed" ON "issue" IS 'Create entry in "event" table on "state" change';

1260

1261

1262 CREATE FUNCTION "write_event_initiative_or_draft_created_trigger"()

1263 RETURNS TRIGGER

1264 LANGUAGE 'plpgsql' VOLATILE AS $$

1265 DECLARE

1266 "initiative_row" "initiative"%ROWTYPE;

1267 "issue_row" "issue"%ROWTYPE;

1268 "event_v" "event_type";

1269 BEGIN

1270 SELECT * INTO "initiative_row" FROM "initiative"

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

1272 SELECT * INTO "issue_row" FROM "issue"

1273 WHERE "id" = "initiative_row"."issue_id";

1274 IF EXISTS (

1275 SELECT NULL FROM "draft"

1276 WHERE "initiative_id" = NEW."initiative_id"

1277 AND "id" != NEW."id"

1278 ) THEN

1279 "event_v" := 'new_draft_created';

1280 ELSE

1281 IF EXISTS (

1282 SELECT NULL FROM "initiative"

1283 WHERE "issue_id" = "initiative_row"."issue_id"

1284 AND "id" != "initiative_row"."id"

1285 ) THEN

1286 "event_v" := 'initiative_created_in_existing_issue';

1287 ELSE

1288 "event_v" := 'initiative_created_in_new_issue';

1289 END IF;

1290 END IF;

1291 INSERT INTO "event" (

1292 "event", "member_id",

1293 "issue_id", "state", "initiative_id", "draft_id"

1294 ) VALUES (

1295 "event_v",

1296 NEW."author_id",

1297 "initiative_row"."issue_id",

1298 "issue_row"."state",

1299 "initiative_row"."id",

1300 NEW."id" );

1301 RETURN NULL;

1302 END;

1303 $$;

1304

1305 CREATE TRIGGER "write_event_initiative_or_draft_created"

1306 AFTER INSERT ON "draft" FOR EACH ROW EXECUTE PROCEDURE

1307 "write_event_initiative_or_draft_created_trigger"();

1308

1309 COMMENT ON FUNCTION "write_event_initiative_or_draft_created_trigger"() IS 'Implementation of trigger "write_event_initiative_or_draft_created" on table "issue"';

1310 COMMENT ON TRIGGER "write_event_initiative_or_draft_created" ON "draft" IS 'Create entry in "event" table on draft creation';

1311

1312

1313 CREATE FUNCTION "write_event_initiative_revoked_trigger"()

1314 RETURNS TRIGGER

1315 LANGUAGE 'plpgsql' VOLATILE AS $$

1316 DECLARE

1317 "issue_row" "issue"%ROWTYPE;

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

1319 BEGIN

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

1321 SELECT * INTO "issue_row" FROM "issue"

1322 WHERE "id" = NEW."issue_id";

1323 SELECT "id" INTO "draft_id_v" FROM "current_draft"

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

1325 INSERT INTO "event" (

1326 "event", "member_id", "issue_id", "state", "initiative_id", "draft_id"

1327 ) VALUES (

1328 'initiative_revoked',

1329 NEW."revoked_by_member_id",

1330 NEW."issue_id",

1331 "issue_row"."state",

1332 NEW."id",

1333 "draft_id_v");

1334 END IF;

1335 RETURN NULL;

1336 END;

1337 $$;

1338

1339 CREATE TRIGGER "write_event_initiative_revoked"

1340 AFTER UPDATE ON "initiative" FOR EACH ROW EXECUTE PROCEDURE

1341 "write_event_initiative_revoked_trigger"();

1342

1343 COMMENT ON FUNCTION "write_event_initiative_revoked_trigger"() IS 'Implementation of trigger "write_event_initiative_revoked" on table "issue"';

1344 COMMENT ON TRIGGER "write_event_initiative_revoked" ON "initiative" IS 'Create entry in "event" table, when an initiative is revoked';

1345

1346

1347 CREATE FUNCTION "write_event_suggestion_created_trigger"()

1348 RETURNS TRIGGER

1349 LANGUAGE 'plpgsql' VOLATILE AS $$

1350 DECLARE

1351 "initiative_row" "initiative"%ROWTYPE;

1352 "issue_row" "issue"%ROWTYPE;

1353 BEGIN

1354 SELECT * INTO "initiative_row" FROM "initiative"

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

1356 SELECT * INTO "issue_row" FROM "issue"

1357 WHERE "id" = "initiative_row"."issue_id";

1358 INSERT INTO "event" (

1359 "event", "member_id",

1360 "issue_id", "state", "initiative_id", "suggestion_id"

1361 ) VALUES (

1362 'suggestion_created',

1363 NEW."author_id",

1364 "initiative_row"."issue_id",

1365 "issue_row"."state",

1366 "initiative_row"."id",

1367 NEW."id" );

1368 RETURN NULL;

1369 END;

1370 $$;

1371

1372 CREATE TRIGGER "write_event_suggestion_created"

1373 AFTER INSERT ON "suggestion" FOR EACH ROW EXECUTE PROCEDURE

1374 "write_event_suggestion_created_trigger"();

1375

1376 COMMENT ON FUNCTION "write_event_suggestion_created_trigger"() IS 'Implementation of trigger "write_event_suggestion_created" on table "issue"';

1377 COMMENT ON TRIGGER "write_event_suggestion_created" ON "suggestion" IS 'Create entry in "event" table on suggestion creation';

1378

1379

1380

1381 ----------------------------

1382 -- Additional constraints --

1383 ----------------------------

1384

1385

1386 CREATE FUNCTION "issue_requires_first_initiative_trigger"()

1387 RETURNS TRIGGER

1388 LANGUAGE 'plpgsql' VOLATILE AS $$

1389 BEGIN

1390 IF NOT EXISTS (

1391 SELECT NULL FROM "initiative" WHERE "issue_id" = NEW."id"

1392 ) THEN

1393 --RAISE 'Cannot create issue without an initial initiative.' USING

1394 -- ERRCODE = 'integrity_constraint_violation',

1395 -- HINT = 'Create issue, initiative, and draft within the same transaction.';

1396 RAISE EXCEPTION 'Cannot create issue without an initial initiative.';

1397 END IF;

1398 RETURN NULL;

1399 END;

1400 $$;

1401

1402 CREATE CONSTRAINT TRIGGER "issue_requires_first_initiative"

1403 AFTER INSERT OR UPDATE ON "issue" DEFERRABLE INITIALLY DEFERRED

1404 FOR EACH ROW EXECUTE PROCEDURE

1405 "issue_requires_first_initiative_trigger"();

1406

1407 COMMENT ON FUNCTION "issue_requires_first_initiative_trigger"() IS 'Implementation of trigger "issue_requires_first_initiative" on table "issue"';

1408 COMMENT ON TRIGGER "issue_requires_first_initiative" ON "issue" IS 'Ensure that new issues have at least one initiative';

1409

1410

1411 CREATE FUNCTION "last_initiative_deletes_issue_trigger"()

1412 RETURNS TRIGGER

1413 LANGUAGE 'plpgsql' VOLATILE AS $$

1414 DECLARE

1415 "reference_lost" BOOLEAN;

1416 BEGIN

1417 IF TG_OP = 'DELETE' THEN

1418 "reference_lost" := TRUE;

1419 ELSE

1420 "reference_lost" := NEW."issue_id" != OLD."issue_id";

1421 END IF;

1422 IF

1423 "reference_lost" AND NOT EXISTS (

1424 SELECT NULL FROM "initiative" WHERE "issue_id" = OLD."issue_id"

1425 )

1426 THEN

1427 DELETE FROM "issue" WHERE "id" = OLD."issue_id";

1428 END IF;

1429 RETURN NULL;

1430 END;

1431 $$;

1432

1433 CREATE CONSTRAINT TRIGGER "last_initiative_deletes_issue"

1434 AFTER UPDATE OR DELETE ON "initiative" DEFERRABLE INITIALLY DEFERRED

1435 FOR EACH ROW EXECUTE PROCEDURE

1436 "last_initiative_deletes_issue_trigger"();

1437

1438 COMMENT ON FUNCTION "last_initiative_deletes_issue_trigger"() IS 'Implementation of trigger "last_initiative_deletes_issue" on table "initiative"';

1439 COMMENT ON TRIGGER "last_initiative_deletes_issue" ON "initiative" IS 'Removing the last initiative of an issue deletes the issue';

1440

1441

1442 CREATE FUNCTION "initiative_requires_first_draft_trigger"()

1443 RETURNS TRIGGER

1444 LANGUAGE 'plpgsql' VOLATILE AS $$

1445 BEGIN

1446 IF NOT EXISTS (

1447 SELECT NULL FROM "draft" WHERE "initiative_id" = NEW."id"

1448 ) THEN

1449 --RAISE 'Cannot create initiative without an initial draft.' USING

1450 -- ERRCODE = 'integrity_constraint_violation',

1451 -- HINT = 'Create issue, initiative and draft within the same transaction.';

1452 RAISE EXCEPTION 'Cannot create initiative without an initial draft.';

1453 END IF;

1454 RETURN NULL;

1455 END;

1456 $$;

1457

1458 CREATE CONSTRAINT TRIGGER "initiative_requires_first_draft"

1459 AFTER INSERT OR UPDATE ON "initiative" DEFERRABLE INITIALLY DEFERRED

1460 FOR EACH ROW EXECUTE PROCEDURE

1461 "initiative_requires_first_draft_trigger"();

1462

1463 COMMENT ON FUNCTION "initiative_requires_first_draft_trigger"() IS 'Implementation of trigger "initiative_requires_first_draft" on table "initiative"';

1464 COMMENT ON TRIGGER "initiative_requires_first_draft" ON "initiative" IS 'Ensure that new initiatives have at least one draft';

1465

1466

1467 CREATE FUNCTION "last_draft_deletes_initiative_trigger"()

1468 RETURNS TRIGGER

1469 LANGUAGE 'plpgsql' VOLATILE AS $$

1470 DECLARE

1471 "reference_lost" BOOLEAN;

1472 BEGIN

1473 IF TG_OP = 'DELETE' THEN

1474 "reference_lost" := TRUE;

1475 ELSE

1476 "reference_lost" := NEW."initiative_id" != OLD."initiative_id";

1477 END IF;

1478 IF

1479 "reference_lost" AND NOT EXISTS (

1480 SELECT NULL FROM "draft" WHERE "initiative_id" = OLD."initiative_id"

1481 )

1482 THEN

1483 DELETE FROM "initiative" WHERE "id" = OLD."initiative_id";

1484 END IF;

1485 RETURN NULL;

1486 END;

1487 $$;

1488

1489 CREATE CONSTRAINT TRIGGER "last_draft_deletes_initiative"

1490 AFTER UPDATE OR DELETE ON "draft" DEFERRABLE INITIALLY DEFERRED

1491 FOR EACH ROW EXECUTE PROCEDURE

1492 "last_draft_deletes_initiative_trigger"();

1493

1494 COMMENT ON FUNCTION "last_draft_deletes_initiative_trigger"() IS 'Implementation of trigger "last_draft_deletes_initiative" on table "draft"';

1495 COMMENT ON TRIGGER "last_draft_deletes_initiative" ON "draft" IS 'Removing the last draft of an initiative deletes the initiative';

1496

1497

1498 CREATE FUNCTION "suggestion_requires_first_opinion_trigger"()

1499 RETURNS TRIGGER

1500 LANGUAGE 'plpgsql' VOLATILE AS $$

1501 BEGIN

1502 IF NOT EXISTS (

1503 SELECT NULL FROM "opinion" WHERE "suggestion_id" = NEW."id"

1504 ) THEN

1505 RAISE EXCEPTION 'Cannot create a suggestion without an opinion.';

1506 END IF;

1507 RETURN NULL;

1508 END;

1509 $$;

1510

1511 CREATE CONSTRAINT TRIGGER "suggestion_requires_first_opinion"

1512 AFTER INSERT OR UPDATE ON "suggestion" DEFERRABLE INITIALLY DEFERRED

1513 FOR EACH ROW EXECUTE PROCEDURE

1514 "suggestion_requires_first_opinion_trigger"();

1515

1516 COMMENT ON FUNCTION "suggestion_requires_first_opinion_trigger"() IS 'Implementation of trigger "suggestion_requires_first_opinion" on table "suggestion"';

1517 COMMENT ON TRIGGER "suggestion_requires_first_opinion" ON "suggestion" IS 'Ensure that new suggestions have at least one opinion';

1518

1519

1520 CREATE FUNCTION "last_opinion_deletes_suggestion_trigger"()

1521 RETURNS TRIGGER

1522 LANGUAGE 'plpgsql' VOLATILE AS $$

1523 DECLARE

1524 "reference_lost" BOOLEAN;

1525 BEGIN

1526 IF TG_OP = 'DELETE' THEN

1527 "reference_lost" := TRUE;

1528 ELSE

1529 "reference_lost" := NEW."suggestion_id" != OLD."suggestion_id";

1530 END IF;

1531 IF

1532 "reference_lost" AND NOT EXISTS (

1533 SELECT NULL FROM "opinion" WHERE "suggestion_id" = OLD."suggestion_id"

1534 )

1535 THEN

1536 DELETE FROM "suggestion" WHERE "id" = OLD."suggestion_id";

1537 END IF;

1538 RETURN NULL;

1539 END;

1540 $$;

1541

1542 CREATE CONSTRAINT TRIGGER "last_opinion_deletes_suggestion"

1543 AFTER UPDATE OR DELETE ON "opinion" DEFERRABLE INITIALLY DEFERRED

1544 FOR EACH ROW EXECUTE PROCEDURE

1545 "last_opinion_deletes_suggestion_trigger"();

1546

1547 COMMENT ON FUNCTION "last_opinion_deletes_suggestion_trigger"() IS 'Implementation of trigger "last_opinion_deletes_suggestion" on table "opinion"';

1548 COMMENT ON TRIGGER "last_opinion_deletes_suggestion" ON "opinion" IS 'Removing the last opinion of a suggestion deletes the suggestion';

1549

1550

1551 CREATE FUNCTION "non_voter_deletes_direct_voter_trigger"()

1552 RETURNS TRIGGER

1553 LANGUAGE 'plpgsql' VOLATILE AS $$

1554 BEGIN

1555 DELETE FROM "direct_voter"

1556 WHERE "issue_id" = NEW."issue_id" AND "member_id" = NEW."member_id";

1557 RETURN NULL;

1558 END;

1559 $$;

1560

1561 CREATE TRIGGER "non_voter_deletes_direct_voter"

1562 AFTER INSERT OR UPDATE ON "non_voter"

1563 FOR EACH ROW EXECUTE PROCEDURE

1564 "non_voter_deletes_direct_voter_trigger"();

1565

1566 COMMENT ON FUNCTION "non_voter_deletes_direct_voter_trigger"() IS 'Implementation of trigger "non_voter_deletes_direct_voter" on table "non_voter"';

1567 COMMENT ON TRIGGER "non_voter_deletes_direct_voter" ON "non_voter" IS 'An entry in the "non_voter" table deletes an entry in the "direct_voter" table (and vice versa due to trigger "direct_voter_deletes_non_voter" on table "direct_voter")';

1568

1569

1570 CREATE FUNCTION "direct_voter_deletes_non_voter_trigger"()

1571 RETURNS TRIGGER

1572 LANGUAGE 'plpgsql' VOLATILE AS $$

1573 BEGIN

1574 DELETE FROM "non_voter"

1575 WHERE "issue_id" = NEW."issue_id" AND "member_id" = NEW."member_id";

1576 RETURN NULL;

1577 END;

1578 $$;

1579

1580 CREATE TRIGGER "direct_voter_deletes_non_voter"

1581 AFTER INSERT OR UPDATE ON "direct_voter"

1582 FOR EACH ROW EXECUTE PROCEDURE

1583 "direct_voter_deletes_non_voter_trigger"();

1584

1585 COMMENT ON FUNCTION "direct_voter_deletes_non_voter_trigger"() IS 'Implementation of trigger "direct_voter_deletes_non_voter" on table "direct_voter"';

1586 COMMENT ON TRIGGER "direct_voter_deletes_non_voter" ON "direct_voter" IS 'An entry in the "direct_voter" table deletes an entry in the "non_voter" table (and vice versa due to trigger "non_voter_deletes_direct_voter" on table "non_voter")';

1587

1588

1589 CREATE FUNCTION "voter_comment_fields_only_set_when_voter_comment_is_set_trigger"()

1590 RETURNS TRIGGER

1591 LANGUAGE 'plpgsql' VOLATILE AS $$

1592 BEGIN

1593 IF NEW."comment" ISNULL THEN

1594 NEW."comment_changed" := NULL;

1595 NEW."formatting_engine" := NULL;

1596 END IF;

1597 RETURN NEW;

1598 END;

1599 $$;

1600

1601 CREATE TRIGGER "voter_comment_fields_only_set_when_voter_comment_is_set"

1602 BEFORE INSERT OR UPDATE ON "direct_voter"

1603 FOR EACH ROW EXECUTE PROCEDURE

1604 "voter_comment_fields_only_set_when_voter_comment_is_set_trigger"();

1605

1606 COMMENT ON FUNCTION "voter_comment_fields_only_set_when_voter_comment_is_set_trigger"() IS 'Implementation of trigger "voter_comment_fields_only_set_when_voter_comment_is_set" ON table "direct_voter"';

1607 COMMENT ON TRIGGER "voter_comment_fields_only_set_when_voter_comment_is_set" ON "direct_voter" IS 'If "comment" is set to NULL, then other comment related fields are also set to NULL.';

1608

1609

1610 ---------------------------------------------------------------

1611 -- Ensure that votes are not modified when issues are closed --

1612 ---------------------------------------------------------------

1613

1614 -- NOTE: Frontends should ensure this anyway, but in case of programming

1615 -- errors the following triggers ensure data integrity.

1616

1617

1618 CREATE FUNCTION "forbid_changes_on_closed_issue_trigger"()

1619 RETURNS TRIGGER

1620 LANGUAGE 'plpgsql' VOLATILE AS $$

1621 DECLARE

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

1623 "issue_row" "issue"%ROWTYPE;

1624 BEGIN

1625 IF EXISTS (

1626 SELECT NULL FROM "temporary_transaction_data"

1627 WHERE "txid" = txid_current()

1628 AND "key" = 'override_protection_triggers'

1629 AND "value" = TRUE::TEXT

1630 ) THEN

1631 RETURN NULL;

1632 END IF;

1633 IF TG_OP = 'DELETE' THEN

1634 "issue_id_v" := OLD."issue_id";

1635 ELSE

1636 "issue_id_v" := NEW."issue_id";

1637 END IF;

1638 SELECT INTO "issue_row" * FROM "issue"

1639 WHERE "id" = "issue_id_v" FOR SHARE;

1640 IF (

1641 "issue_row"."closed" NOTNULL OR (

1642 "issue_row"."state" = 'voting' AND

1643 "issue_row"."phase_finished" NOTNULL

1644 )

1645 ) THEN

1646 IF

1647 TG_RELID = 'direct_voter'::regclass AND

1648 TG_OP = 'UPDATE'

1649 THEN

1650 IF

1651 OLD."issue_id" = NEW."issue_id" AND

1652 OLD."member_id" = NEW."member_id" AND

1653 OLD."weight" = NEW."weight"

1654 THEN

1655 RETURN NULL; -- allows changing of voter comment

1656 END IF;

1657 END IF;

1658 RAISE EXCEPTION 'Tried to modify data after voting has been closed.';

1659 END IF;

1660 RETURN NULL;

1661 END;

1662 $$;

1663

1664 CREATE TRIGGER "forbid_changes_on_closed_issue"

1665 AFTER INSERT OR UPDATE OR DELETE ON "direct_voter"

1666 FOR EACH ROW EXECUTE PROCEDURE

1667 "forbid_changes_on_closed_issue_trigger"();

1668

1669 CREATE TRIGGER "forbid_changes_on_closed_issue"

1670 AFTER INSERT OR UPDATE OR DELETE ON "delegating_voter"

1671 FOR EACH ROW EXECUTE PROCEDURE

1672 "forbid_changes_on_closed_issue_trigger"();

1673

1674 CREATE TRIGGER "forbid_changes_on_closed_issue"

1675 AFTER INSERT OR UPDATE OR DELETE ON "vote"

1676 FOR EACH ROW EXECUTE PROCEDURE

1677 "forbid_changes_on_closed_issue_trigger"();

1678

1679 COMMENT ON FUNCTION "forbid_changes_on_closed_issue_trigger"() IS 'Implementation of triggers "forbid_changes_on_closed_issue" on tables "direct_voter", "delegating_voter" and "vote"';

1680 COMMENT ON TRIGGER "forbid_changes_on_closed_issue" ON "direct_voter" IS 'Ensures that frontends can''t tamper with votings of closed issues, in case of programming errors';

1681 COMMENT ON TRIGGER "forbid_changes_on_closed_issue" ON "delegating_voter" IS 'Ensures that frontends can''t tamper with votings of closed issues, in case of programming errors';

1682 COMMENT ON TRIGGER "forbid_changes_on_closed_issue" ON "vote" IS 'Ensures that frontends can''t tamper with votings of closed issues, in case of programming errors';

1683

1684

1685

1686 --------------------------------------------------------------------

1687 -- Auto-retrieval of fields only needed for referential integrity --

1688 --------------------------------------------------------------------

1689

1690

1691 CREATE FUNCTION "autofill_issue_id_trigger"()

1692 RETURNS TRIGGER

1693 LANGUAGE 'plpgsql' VOLATILE AS $$

1694 BEGIN

1695 IF NEW."issue_id" ISNULL THEN

1696 SELECT "issue_id" INTO NEW."issue_id"

1697 FROM "initiative" WHERE "id" = NEW."initiative_id";

1698 END IF;

1699 RETURN NEW;

1700 END;

1701 $$;

1702

1703 CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "supporter"

1704 FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();

1705

1706 CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "vote"

1707 FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();

1708

1709 COMMENT ON FUNCTION "autofill_issue_id_trigger"() IS 'Implementation of triggers "autofill_issue_id" on tables "supporter" and "vote"';

1710 COMMENT ON TRIGGER "autofill_issue_id" ON "supporter" IS 'Set "issue_id" field automatically, if NULL';

1711 COMMENT ON TRIGGER "autofill_issue_id" ON "vote" IS 'Set "issue_id" field automatically, if NULL';

1712

1713

1714 CREATE FUNCTION "autofill_initiative_id_trigger"()

1715 RETURNS TRIGGER

1716 LANGUAGE 'plpgsql' VOLATILE AS $$

1717 BEGIN

1718 IF NEW."initiative_id" ISNULL THEN

1719 SELECT "initiative_id" INTO NEW."initiative_id"

1720 FROM "suggestion" WHERE "id" = NEW."suggestion_id";

1721 END IF;

1722 RETURN NEW;

1723 END;

1724 $$;

1725

1726 CREATE TRIGGER "autofill_initiative_id" BEFORE INSERT ON "opinion"

1727 FOR EACH ROW EXECUTE PROCEDURE "autofill_initiative_id_trigger"();

1728

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

1730 COMMENT ON TRIGGER "autofill_initiative_id" ON "opinion" IS 'Set "initiative_id" field automatically, if NULL';

1731

1732

1733

1734 -----------------------------------------------------

1735 -- Automatic calculation of certain default values --

1736 -----------------------------------------------------

1737

1738

1739 CREATE FUNCTION "copy_timings_trigger"()

1740 RETURNS TRIGGER

1741 LANGUAGE 'plpgsql' VOLATILE AS $$

1742 DECLARE

1743 "policy_row" "policy"%ROWTYPE;

1744 BEGIN

1745 SELECT * INTO "policy_row" FROM "policy"

1746 WHERE "id" = NEW."policy_id";

1747 IF NEW."admission_time" ISNULL THEN

1748 NEW."admission_time" := "policy_row"."admission_time";

1749 END IF;

1750 IF NEW."discussion_time" ISNULL THEN

1751 NEW."discussion_time" := "policy_row"."discussion_time";

1752 END IF;

1753 IF NEW."verification_time" ISNULL THEN

1754 NEW."verification_time" := "policy_row"."verification_time";

1755 END IF;

1756 IF NEW."voting_time" ISNULL THEN

1757 NEW."voting_time" := "policy_row"."voting_time";

1758 END IF;

1759 RETURN NEW;

1760 END;

1761 $$;

1762

1763 CREATE TRIGGER "copy_timings" BEFORE INSERT OR UPDATE ON "issue"

1764 FOR EACH ROW EXECUTE PROCEDURE "copy_timings_trigger"();

1765

1766 COMMENT ON FUNCTION "copy_timings_trigger"() IS 'Implementation of trigger "copy_timings" on table "issue"';

1767 COMMENT ON TRIGGER "copy_timings" ON "issue" IS 'If timing fields are NULL, copy values from policy.';

1768

1769

1770 CREATE FUNCTION "default_for_draft_id_trigger"()

1771 RETURNS TRIGGER

1772 LANGUAGE 'plpgsql' VOLATILE AS $$

1773 BEGIN

1774 IF NEW."draft_id" ISNULL THEN

1775 SELECT "id" INTO NEW."draft_id" FROM "current_draft"

1776 WHERE "initiative_id" = NEW."initiative_id";

1777 END IF;

1778 RETURN NEW;

1779 END;

1780 $$;

1781

1782 CREATE TRIGGER "default_for_draft_id" BEFORE INSERT OR UPDATE ON "suggestion"

1783 FOR EACH ROW EXECUTE PROCEDURE "default_for_draft_id_trigger"();

1784 CREATE TRIGGER "default_for_draft_id" BEFORE INSERT OR UPDATE ON "supporter"

1785 FOR EACH ROW EXECUTE PROCEDURE "default_for_draft_id_trigger"();

1786

1787 COMMENT ON FUNCTION "default_for_draft_id_trigger"() IS 'Implementation of trigger "default_for_draft" on tables "supporter" and "suggestion"';

1788 COMMENT ON TRIGGER "default_for_draft_id" ON "suggestion" IS 'If "draft_id" is NULL, then use the current draft of the initiative as default';

1789 COMMENT ON TRIGGER "default_for_draft_id" ON "supporter" IS 'If "draft_id" is NULL, then use the current draft of the initiative as default';

1790

1791

1792

1793 ----------------------------------------

1794 -- Automatic creation of dependencies --

1795 ----------------------------------------

1796

1797

1798 CREATE FUNCTION "autocreate_interest_trigger"()

1799 RETURNS TRIGGER

1800 LANGUAGE 'plpgsql' VOLATILE AS $$

1801 BEGIN

1802 IF NOT EXISTS (

1803 SELECT NULL FROM "initiative" JOIN "interest"

1804 ON "initiative"."issue_id" = "interest"."issue_id"

1805 WHERE "initiative"."id" = NEW."initiative_id"

1806 AND "interest"."member_id" = NEW."member_id"

1807 ) THEN

1808 BEGIN

1809 INSERT INTO "interest" ("issue_id", "member_id")

1810 SELECT "issue_id", NEW."member_id"

1811 FROM "initiative" WHERE "id" = NEW."initiative_id";

1812 EXCEPTION WHEN unique_violation THEN END;

1813 END IF;

1814 RETURN NEW;

1815 END;

1816 $$;

1817

1818 CREATE TRIGGER "autocreate_interest" BEFORE INSERT ON "supporter"

1819 FOR EACH ROW EXECUTE PROCEDURE "autocreate_interest_trigger"();

1820

1821 COMMENT ON FUNCTION "autocreate_interest_trigger"() IS 'Implementation of trigger "autocreate_interest" on table "supporter"';

1822 COMMENT ON TRIGGER "autocreate_interest" ON "supporter" IS 'Supporting an initiative implies interest in the issue, thus automatically creates an entry in the "interest" table';

1823

1824

1825 CREATE FUNCTION "autocreate_supporter_trigger"()

1826 RETURNS TRIGGER

1827 LANGUAGE 'plpgsql' VOLATILE AS $$

1828 BEGIN

1829 IF NOT EXISTS (

1830 SELECT NULL FROM "suggestion" JOIN "supporter"

1831 ON "suggestion"."initiative_id" = "supporter"."initiative_id"

1832 WHERE "suggestion"."id" = NEW."suggestion_id"

1833 AND "supporter"."member_id" = NEW."member_id"

1834 ) THEN

1835 BEGIN

1836 INSERT INTO "supporter" ("initiative_id", "member_id")

1837 SELECT "initiative_id", NEW."member_id"

1838 FROM "suggestion" WHERE "id" = NEW."suggestion_id";

1839 EXCEPTION WHEN unique_violation THEN END;

1840 END IF;

1841 RETURN NEW;

1842 END;

1843 $$;

1844

1845 CREATE TRIGGER "autocreate_supporter" BEFORE INSERT ON "opinion"

1846 FOR EACH ROW EXECUTE PROCEDURE "autocreate_supporter_trigger"();

1847

1848 COMMENT ON FUNCTION "autocreate_supporter_trigger"() IS 'Implementation of trigger "autocreate_supporter" on table "opinion"';

1849 COMMENT ON TRIGGER "autocreate_supporter" ON "opinion" IS 'Opinions can only be added for supported initiatives. This trigger automatrically creates an entry in the "supporter" table, if not existent yet.';

1850

1851

1852

1853 ------------------------------------------

1854 -- Views and helper functions for views --

1855 ------------------------------------------

1856

1857

1858 CREATE VIEW "unit_delegation" AS

1859 SELECT

1860 "unit"."id" AS "unit_id",

1861 "delegation"."id",

1862 "delegation"."truster_id",

1863 "delegation"."trustee_id",

1864 "delegation"."scope"

1865 FROM "unit"

1866 JOIN "delegation"

1867 ON "delegation"."unit_id" = "unit"."id"

1868 JOIN "member"

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

1870 JOIN "privilege"

1871 ON "delegation"."unit_id" = "privilege"."unit_id"

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

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

1874

1875 COMMENT ON VIEW "unit_delegation" IS 'Unit delegations where trusters are active and have voting right';

1876

1877

1878 CREATE VIEW "area_delegation" AS

1879 SELECT DISTINCT ON ("area"."id", "delegation"."truster_id")

1880 "area"."id" AS "area_id",

1881 "delegation"."id",

1882 "delegation"."truster_id",

1883 "delegation"."trustee_id",

1884 "delegation"."scope"

1885 FROM "area"

1886 JOIN "delegation"

1887 ON "delegation"."unit_id" = "area"."unit_id"

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

1889 JOIN "member"

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

1891 JOIN "privilege"

1892 ON "area"."unit_id" = "privilege"."unit_id"

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

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

1895 ORDER BY

1896 "area"."id",

1897 "delegation"."truster_id",

1898 "delegation"."scope" DESC;

1899

1900 COMMENT ON VIEW "area_delegation" IS 'Area delegations where trusters are active and have voting right';

1901

1902

1903 CREATE VIEW "issue_delegation" AS

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

1905 "issue"."id" AS "issue_id",

1906 "delegation"."id",

1907 "delegation"."truster_id",

1908 "delegation"."trustee_id",

1909 "delegation"."scope"

1910 FROM "issue"

1911 JOIN "area"

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

1913 JOIN "delegation"

1914 ON "delegation"."unit_id" = "area"."unit_id"

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

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

1917 JOIN "member"

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

1919 JOIN "privilege"

1920 ON "area"."unit_id" = "privilege"."unit_id"

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

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

1923 ORDER BY

1924 "issue"."id",

1925 "delegation"."truster_id",

1926 "delegation"."scope" DESC;

1927

1928 COMMENT ON VIEW "issue_delegation" IS 'Issue delegations where trusters are active and have voting right';

1929

1930

1931 CREATE FUNCTION "membership_weight_with_skipping"

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

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

1934 "skip_member_ids_p" INT4[] ) -- "member"."id"%TYPE[]

1935 RETURNS INT4

1936 LANGUAGE 'plpgsql' STABLE AS $$

1937 DECLARE

1938 "sum_v" INT4;

1939 "delegation_row" "area_delegation"%ROWTYPE;

1940 BEGIN

1941 "sum_v" := 1;

1942 FOR "delegation_row" IN

1943 SELECT "area_delegation".*

1944 FROM "area_delegation" LEFT JOIN "membership"

1945 ON "membership"."area_id" = "area_id_p"

1946 AND "membership"."member_id" = "area_delegation"."truster_id"

1947 WHERE "area_delegation"."area_id" = "area_id_p"

1948 AND "area_delegation"."trustee_id" = "member_id_p"

1949 AND "membership"."member_id" ISNULL

1950 LOOP

1951 IF NOT

1952 "skip_member_ids_p" @> ARRAY["delegation_row"."truster_id"]

1953 THEN

1954 "sum_v" := "sum_v" + "membership_weight_with_skipping"(

1955 "area_id_p",

1956 "delegation_row"."truster_id",

1957 "skip_member_ids_p" || "delegation_row"."truster_id"

1958 );

1959 END IF;

1960 END LOOP;

1961 RETURN "sum_v";

1962 END;

1963 $$;

1964

1965 COMMENT ON FUNCTION "membership_weight_with_skipping"

1966 ( "area"."id"%TYPE,

1967 "member"."id"%TYPE,

1968 INT4[] )

1969 IS 'Helper function for "membership_weight" function';

1970

1971

1972 CREATE FUNCTION "membership_weight"

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

1974 "member_id_p" "member"."id"%TYPE ) -- "member"."id"%TYPE[]

1975 RETURNS INT4

1976 LANGUAGE 'plpgsql' STABLE AS $$

1977 BEGIN

1978 RETURN "membership_weight_with_skipping"(

1979 "area_id_p",

1980 "member_id_p",

1981 ARRAY["member_id_p"]

1982 );

1983 END;

1984 $$;

1985

1986 COMMENT ON FUNCTION "membership_weight"

1987 ( "area"."id"%TYPE,

1988 "member"."id"%TYPE )

1989 IS 'Calculates the potential voting weight of a member in a given area';

1990

1991

1992 CREATE VIEW "member_count_view" AS

1993 SELECT count(1) AS "total_count" FROM "member" WHERE "active";

1994

1995 COMMENT ON VIEW "member_count_view" IS 'View used to update "member_count" table';

1996

1997

1998 CREATE VIEW "unit_member_count" AS

1999 SELECT

2000 "unit"."id" AS "unit_id",

2001 count("member"."id") AS "member_count"

2002 FROM "unit"

2003 LEFT JOIN "privilege"

2004 ON "privilege"."unit_id" = "unit"."id"

2005 AND "privilege"."voting_right"

2006 LEFT JOIN "member"

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

2008 AND "member"."active"

2009 GROUP BY "unit"."id";

2010

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

2012

2013

2014 CREATE VIEW "area_member_count" AS

2015 SELECT

2016 "area"."id" AS "area_id",

2017 count("member"."id") AS "direct_member_count",

2018 coalesce(

2019 sum(

2020 CASE WHEN "member"."id" NOTNULL THEN

2021 "membership_weight"("area"."id", "member"."id")

2022 ELSE 0 END

2023 )

2024 ) AS "member_weight"

2025 FROM "area"

2026 LEFT JOIN "membership"

2027 ON "area"."id" = "membership"."area_id"

2028 LEFT JOIN "privilege"

2029 ON "privilege"."unit_id" = "area"."unit_id"

2030 AND "privilege"."member_id" = "membership"."member_id"

2031 AND "privilege"."voting_right"

2032 LEFT JOIN "member"

2033 ON "member"."id" = "privilege"."member_id" -- NOTE: no membership here!

2034 AND "member"."active"

2035 GROUP BY "area"."id";

2036

2037 COMMENT ON VIEW "area_member_count" IS 'View used to update "direct_member_count" and "member_weight" columns of table "area"';

2038

2039

2040 CREATE VIEW "opening_draft" AS

2041 SELECT "draft".* FROM (

2042 SELECT

2043 "initiative"."id" AS "initiative_id",

2044 min("draft"."id") AS "draft_id"

2045 FROM "initiative" JOIN "draft"

2046 ON "initiative"."id" = "draft"."initiative_id"

2047 GROUP BY "initiative"."id"

2048 ) AS "subquery"

2049 JOIN "draft" ON "subquery"."draft_id" = "draft"."id";

2050

2051 COMMENT ON VIEW "opening_draft" IS 'First drafts of all initiatives';

2052

2053

2054 CREATE VIEW "current_draft" AS

2055 SELECT "draft".* FROM (

2056 SELECT

2057 "initiative"."id" AS "initiative_id",

2058 max("draft"."id") AS "draft_id"

2059 FROM "initiative" JOIN "draft"

2060 ON "initiative"."id" = "draft"."initiative_id"

2061 GROUP BY "initiative"."id"

2062 ) AS "subquery"

2063 JOIN "draft" ON "subquery"."draft_id" = "draft"."id";

2064

2065 COMMENT ON VIEW "current_draft" IS 'All latest drafts for each initiative';

2066

2067

2068 CREATE VIEW "critical_opinion" AS

2069 SELECT * FROM "opinion"

2070 WHERE ("degree" = 2 AND "fulfilled" = FALSE)

2071 OR ("degree" = -2 AND "fulfilled" = TRUE);

2072

2073 COMMENT ON VIEW "critical_opinion" IS 'Opinions currently causing dissatisfaction';

2074

2075

2076 CREATE VIEW "issue_supporter_in_admission_state" AS

2077 SELECT DISTINCT

2078 "area"."unit_id",

2079 "issue"."area_id",

2080 "issue"."id" AS "issue_id",

2081 "supporter"."member_id",

2082 "direct_interest_snapshot"."weight"

2083 FROM "issue"

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

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

2086 JOIN "direct_interest_snapshot"

2087 ON "direct_interest_snapshot"."issue_id" = "issue"."id"

2088 AND "direct_interest_snapshot"."event" = "issue"."latest_snapshot_event"

2089 AND "direct_interest_snapshot"."member_id" = "supporter"."member_id"

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

2091

2092 COMMENT ON VIEW "issue_supporter_in_admission_state" IS 'Helper view for "lf_update_issue_order" to allow a (proportional) ordering of issues within an area';

2093

2094

2095 CREATE VIEW "initiative_suggestion_order_calculation" AS

2096 SELECT

2097 "initiative"."id" AS "initiative_id",

2098 ("issue"."closed" NOTNULL OR "issue"."fully_frozen" NOTNULL) AS "final"

2099 FROM "initiative" JOIN "issue"

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

2101 WHERE ("issue"."closed" ISNULL AND "issue"."fully_frozen" ISNULL)

2102 OR ("initiative"."final_suggestion_order_calculated" = FALSE);

2103

2104 COMMENT ON VIEW "initiative_suggestion_order_calculation" IS 'Initiatives, where the "proportional_order" of its suggestions has to be calculated';

2105

2106 COMMENT ON COLUMN "initiative_suggestion_order_calculation"."final" IS 'Set to TRUE, if the issue is fully frozen or closed, and the calculation has to be done only once for one last time';

2107

2108

2109 CREATE VIEW "individual_suggestion_ranking" AS

2110 SELECT

2111 "opinion"."initiative_id",

2112 "opinion"."member_id",

2113 "direct_interest_snapshot"."weight",

2114 CASE WHEN

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

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

2117 THEN 1 ELSE

2118 CASE WHEN

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

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

2121 THEN 2 ELSE

2122 CASE WHEN

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

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

2125 THEN 3 ELSE 4 END

2126 END

2127 END AS "preference",

2128 "opinion"."suggestion_id"

2129 FROM "opinion"

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

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

2132 JOIN "direct_interest_snapshot"

2133 ON "direct_interest_snapshot"."issue_id" = "issue"."id"

2134 AND "direct_interest_snapshot"."event" = "issue"."latest_snapshot_event"

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

2136

2137 COMMENT ON VIEW "individual_suggestion_ranking" IS 'Helper view for "lf_update_suggestion_order" to allow a proportional ordering of suggestions within an initiative';

2138

2139

2140 CREATE VIEW "battle_participant" AS

2141 SELECT "initiative"."id", "initiative"."issue_id"

2142 FROM "issue" JOIN "initiative"

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

2144 WHERE "initiative"."admitted"

2145 UNION ALL

2146 SELECT NULL, "id" AS "issue_id"

2147 FROM "issue";

2148

2149 COMMENT ON VIEW "battle_participant" IS 'Helper view for "battle_view" containing admitted initiatives plus virtual "status-quo" initiative denoted by NULL reference';

2150

2151

2152 CREATE VIEW "battle_view" AS

2153 SELECT

2154 "issue"."id" AS "issue_id",

2155 "winning_initiative"."id" AS "winning_initiative_id",

2156 "losing_initiative"."id" AS "losing_initiative_id",

2157 sum(

2158 CASE WHEN

2159 coalesce("better_vote"."grade", 0) >

2160 coalesce("worse_vote"."grade", 0)

2161 THEN "direct_voter"."weight" ELSE 0 END

2162 ) AS "count"

2163 FROM "issue"

2164 LEFT JOIN "direct_voter"

2165 ON "issue"."id" = "direct_voter"."issue_id"

2166 JOIN "battle_participant" AS "winning_initiative"

2167 ON "issue"."id" = "winning_initiative"."issue_id"

2168 JOIN "battle_participant" AS "losing_initiative"

2169 ON "issue"."id" = "losing_initiative"."issue_id"

2170 LEFT JOIN "vote" AS "better_vote"

2171 ON "direct_voter"."member_id" = "better_vote"."member_id"

2172 AND "winning_initiative"."id" = "better_vote"."initiative_id"

2173 LEFT JOIN "vote" AS "worse_vote"

2174 ON "direct_voter"."member_id" = "worse_vote"."member_id"

2175 AND "losing_initiative"."id" = "worse_vote"."initiative_id"

2176 WHERE "issue"."state" = 'voting'

2177 AND "issue"."phase_finished" NOTNULL

2178 AND (

2179 "winning_initiative"."id" != "losing_initiative"."id" OR

2180 ( ("winning_initiative"."id" NOTNULL AND "losing_initiative"."id" ISNULL) OR

2181 ("winning_initiative"."id" ISNULL AND "losing_initiative"."id" NOTNULL) ) )

2182 GROUP BY

2183 "issue"."id",

2184 "winning_initiative"."id",

2185 "losing_initiative"."id";

2186

2187 COMMENT ON VIEW "battle_view" IS 'Number of members preferring one initiative (or status-quo) to another initiative (or status-quo); Used to fill "battle" table';

2188

2189

2190 CREATE VIEW "expired_session" AS

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

2192

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

2194 DELETE FROM "session" WHERE "ident" = OLD."ident";

2195

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

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

2198

2199

2200 CREATE VIEW "open_issue" AS

2201 SELECT * FROM "issue" WHERE "closed" ISNULL;

2202

2203 COMMENT ON VIEW "open_issue" IS 'All open issues';

2204

2205

2206 CREATE VIEW "member_contingent" AS

2207 SELECT

2208 "member"."id" AS "member_id",

2209 "contingent"."polling",

2210 "contingent"."time_frame",

2211 CASE WHEN "contingent"."text_entry_limit" NOTNULL THEN

2212 (

2213 SELECT count(1) FROM "draft"

2214 JOIN "initiative" ON "initiative"."id" = "draft"."initiative_id"

2215 WHERE "draft"."author_id" = "member"."id"

2216 AND "initiative"."polling" = "contingent"."polling"

2217 AND "draft"."created" > now() - "contingent"."time_frame"

2218 ) + (

2219 SELECT count(1) FROM "suggestion"

2220 JOIN "initiative" ON "initiative"."id" = "suggestion"."initiative_id"

2221 WHERE "suggestion"."author_id" = "member"."id"

2222 AND "contingent"."polling" = FALSE

2223 AND "suggestion"."created" > now() - "contingent"."time_frame"

2224 )

2225 ELSE NULL END AS "text_entry_count",

2226 "contingent"."text_entry_limit",

2227 CASE WHEN "contingent"."initiative_limit" NOTNULL THEN (

2228 SELECT count(1) FROM "opening_draft" AS "draft"

2229 JOIN "initiative" ON "initiative"."id" = "draft"."initiative_id"

2230 WHERE "draft"."author_id" = "member"."id"

2231 AND "initiative"."polling" = "contingent"."polling"

2232 AND "draft"."created" > now() - "contingent"."time_frame"

2233 ) ELSE NULL END AS "initiative_count",

2234 "contingent"."initiative_limit"

2235 FROM "member" CROSS JOIN "contingent";

2236

2237 COMMENT ON VIEW "member_contingent" IS 'Actual counts of text entries and initiatives are calculated per member for each limit in the "contingent" table.';

2238

2239 COMMENT ON COLUMN "member_contingent"."text_entry_count" IS 'Only calculated when "text_entry_limit" is not null in the same row';

2240 COMMENT ON COLUMN "member_contingent"."initiative_count" IS 'Only calculated when "initiative_limit" is not null in the same row';

2241

2242

2243 CREATE VIEW "member_contingent_left" AS

2244 SELECT

2245 "member_id",

2246 "polling",

2247 max("text_entry_limit" - "text_entry_count") AS "text_entries_left",

2248 max("initiative_limit" - "initiative_count") AS "initiatives_left"

2249 FROM "member_contingent" GROUP BY "member_id", "polling";

2250

2251 COMMENT ON VIEW "member_contingent_left" IS 'Amount of text entries or initiatives which can be posted now instantly by a member. This view should be used by a frontend to determine, if the contingent for posting is exhausted.';

2252

2253

2254 CREATE VIEW "event_seen_by_member" AS

2255 SELECT

2256 "member"."id" AS "seen_by_member_id",

2257 CASE WHEN "event"."state" IN (

2258 'voting',

2259 'finished_without_winner',

2260 'finished_with_winner'

2261 ) THEN

2262 'voting'::"notify_level"

2263 ELSE

2264 CASE WHEN "event"."state" IN (

2265 'verification',

2266 'canceled_after_revocation_during_verification',

2267 'canceled_no_initiative_admitted'

2268 ) THEN

2269 'verification'::"notify_level"

2270 ELSE

2271 CASE WHEN "event"."state" IN (

2272 'discussion',

2273 'canceled_after_revocation_during_discussion'

2274 ) THEN

2275 'discussion'::"notify_level"

2276 ELSE

2277 'all'::"notify_level"

2278 END

2279 END

2280 END AS "notify_level",

2281 "event".*

2282 FROM "member" CROSS JOIN "event"

2283 LEFT JOIN "issue"

2284 ON "event"."issue_id" = "issue"."id"

2285 LEFT JOIN "membership"

2286 ON "member"."id" = "membership"."member_id"

2287 AND "issue"."area_id" = "membership"."area_id"

2288 LEFT JOIN "interest"

2289 ON "member"."id" = "interest"."member_id"

2290 AND "event"."issue_id" = "interest"."issue_id"

2291 LEFT JOIN "ignored_member"

2292 ON "member"."id" = "ignored_member"."member_id"

2293 AND "event"."member_id" = "ignored_member"."other_member_id"

2294 LEFT JOIN "ignored_initiative"

2295 ON "member"."id" = "ignored_initiative"."member_id"

2296 AND "event"."initiative_id" = "ignored_initiative"."initiative_id"

2297 WHERE (

2298 "interest"."member_id" NOTNULL OR

2299 ( "membership"."member_id" NOTNULL AND

2300 "event"."event" IN (

2301 'issue_state_changed',

2302 'initiative_created_in_new_issue',

2303 'initiative_created_in_existing_issue',

2304 'initiative_revoked' ) ) )

2305 AND "ignored_member"."member_id" ISNULL

2306 AND "ignored_initiative"."member_id" ISNULL;

2307

2308 COMMENT ON VIEW "event_seen_by_member" IS 'Events as seen by a member, depending on its memberships, interests and support, but ignoring members "notify_level"';

2309

2310

2311 CREATE VIEW "selected_event_seen_by_member" AS

2312 SELECT

2313 "member"."id" AS "seen_by_member_id",

2314 CASE WHEN "event"."state" IN (

2315 'voting',

2316 'finished_without_winner',

2317 'finished_with_winner'

2318 ) THEN

2319 'voting'::"notify_level"

2320 ELSE

2321 CASE WHEN "event"."state" IN (

2322 'verification',

2323 'canceled_after_revocation_during_verification',

2324 'canceled_no_initiative_admitted'

2325 ) THEN

2326 'verification'::"notify_level"

2327 ELSE

2328 CASE WHEN "event"."state" IN (

2329 'discussion',

2330 'canceled_after_revocation_during_discussion'

2331 ) THEN

2332 'discussion'::"notify_level"

2333 ELSE

2334 'all'::"notify_level"

2335 END

2336 END

2337 END AS "notify_level",

2338 "event".*

2339 FROM "member" CROSS JOIN "event"

2340 LEFT JOIN "issue"

2341 ON "event"."issue_id" = "issue"."id"

2342 LEFT JOIN "membership"

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

2344 AND "issue"."area_id" = "membership"."area_id"

2345 LEFT JOIN "interest"

2346 ON "member"."id" = "interest"."member_id"

2347 AND "event"."issue_id" = "interest"."issue_id"

2348 LEFT JOIN "ignored_member"

2349 ON "member"."id" = "ignored_member"."member_id"

2350 AND "event"."member_id" = "ignored_member"."other_member_id"

2351 LEFT JOIN "ignored_initiative"

2352 ON "member"."id" = "ignored_initiative"."member_id"

2353 AND "event"."initiative_id" = "ignored_initiative"."initiative_id"

2354 WHERE (

2355 ( "member"."notify_level" >= 'all' ) OR

2356 ( "member"."notify_level" >= 'voting' AND

2357 "event"."state" IN (

2358 'voting',

2359 'finished_without_winner',

2360 'finished_with_winner' ) ) OR

2361 ( "member"."notify_level" >= 'verification' AND

2362 "event"."state" IN (

2363 'verification',

2364 'canceled_after_revocation_during_verification',

2365 'canceled_no_initiative_admitted' ) ) OR

2366 ( "member"."notify_level" >= 'discussion' AND

2367 "event"."state" IN (

2368 'discussion',

2369 'canceled_after_revocation_during_discussion' ) ) )

2370 AND (

2371 "interest"."member_id" NOTNULL OR

2372 ( "membership"."member_id" NOTNULL AND

2373 "event"."event" IN (

2374 'issue_state_changed',

2375 'initiative_created_in_new_issue',

2376 'initiative_created_in_existing_issue',

2377 'initiative_revoked' ) ) )

2378 AND "ignored_member"."member_id" ISNULL

2379 AND "ignored_initiative"."member_id" ISNULL;

2380

2381 COMMENT ON VIEW "selected_event_seen_by_member" IS 'Events as seen by a member, depending on its memberships, interests, support and members "notify_level"';

2382

2383

2384

2385 ------------------------------------------------------

2386 -- Row set returning function for delegation chains --

2387 ------------------------------------------------------

2388

2389

2390 CREATE TYPE "delegation_chain_loop_tag" AS ENUM

2391 ('first', 'intermediate', 'last', 'repetition');

2392

2393 COMMENT ON TYPE "delegation_chain_loop_tag" IS 'Type for loop tags in "delegation_chain_row" type';

2394

2395

2396 CREATE TYPE "delegation_chain_row" AS (

2397 "index" INT4,

2398 "member_id" INT4,

2399 "member_valid" BOOLEAN,

2400 "participation" BOOLEAN,

2401 "overridden" BOOLEAN,

2402 "scope_in" "delegation_scope",

2403 "scope_out" "delegation_scope",

2404 "disabled_out" BOOLEAN,

2405 "loop" "delegation_chain_loop_tag" );

2406

2407 COMMENT ON TYPE "delegation_chain_row" IS 'Type of rows returned by "delegation_chain" function';

2408

2409 COMMENT ON COLUMN "delegation_chain_row"."index" IS 'Index starting with 0 and counting up';

2410 COMMENT ON COLUMN "delegation_chain_row"."participation" IS 'In case of delegation chains for issues: interest, for areas: membership, for global delegation chains: always null';

2411 COMMENT ON COLUMN "delegation_chain_row"."overridden" IS 'True, if an entry with lower index has "participation" set to true';

2412 COMMENT ON COLUMN "delegation_chain_row"."scope_in" IS 'Scope of used incoming delegation';

2413 COMMENT ON COLUMN "delegation_chain_row"."scope_out" IS 'Scope of used outgoing delegation';

2414 COMMENT ON COLUMN "delegation_chain_row"."disabled_out" IS 'Outgoing delegation is explicitly disabled by a delegation with trustee_id set to NULL';

2415 COMMENT ON COLUMN "delegation_chain_row"."loop" IS 'Not null, if member is part of a loop, see "delegation_chain_loop_tag" type';

2416

2417

2418 CREATE FUNCTION "delegation_chain_for_closed_issue"

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

2420 "issue_id_p" "issue"."id"%TYPE )

2421 RETURNS SETOF "delegation_chain_row"

2422 LANGUAGE 'plpgsql' STABLE AS $$

2423 DECLARE

2424 "output_row" "delegation_chain_row";

2425 "direct_voter_row" "direct_voter"%ROWTYPE;

2426 "delegating_voter_row" "delegating_voter"%ROWTYPE;

2427 BEGIN

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

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

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

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

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

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

2434 LOOP

2435 SELECT INTO "direct_voter_row" * FROM "direct_voter"

2436 WHERE "issue_id" = "issue_id_p"

2437 AND "member_id" = "output_row"."member_id";

2438 IF "direct_voter_row"."member_id" NOTNULL THEN

2439 "output_row"."participation" := TRUE;

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

2441 "output_row"."disabled_out" := NULL;

2442 RETURN NEXT "output_row";

2443 RETURN;

2444 END IF;

2445 SELECT INTO "delegating_voter_row" * FROM "delegating_voter"

2446 WHERE "issue_id" = "issue_id_p"

2447 AND "member_id" = "output_row"."member_id";

2448 IF "delegating_voter_row"."member_id" ISNULL THEN

2449 RETURN;

2450 END IF;

2451 "output_row"."scope_out" := "delegating_voter_row"."scope";

2452 RETURN NEXT "output_row";

2453 "output_row"."member_id" := "delegating_voter_row"."delegate_member_ids"[1];

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

2455 END LOOP;

2456 END;

2457 $$;

2458

2459 COMMENT ON FUNCTION "delegation_chain_for_closed_issue"

2460 ( "member"."id"%TYPE,

2461 "member"."id"%TYPE )

2462 IS 'Helper function for "delegation_chain" function, handling the special case of closed issues after voting';

2463

2464

2465 CREATE FUNCTION "delegation_chain"

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

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

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

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

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

2471 "simulate_default_p" BOOLEAN DEFAULT FALSE )

2472 RETURNS SETOF "delegation_chain_row"

2473 LANGUAGE 'plpgsql' STABLE AS $$

2474 DECLARE

2475 "scope_v" "delegation_scope";

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

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

2478 "issue_row" "issue"%ROWTYPE;

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

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

2481 "output_row" "delegation_chain_row";

2482 "output_rows" "delegation_chain_row"[];

2483 "simulate_v" BOOLEAN;

2484 "simulate_here_v" BOOLEAN;

2485 "delegation_row" "delegation"%ROWTYPE;

2486 "row_count" INT4;

2487 "i" INT4;

2488 "loop_v" BOOLEAN;

2489 BEGIN

2490 IF "simulate_trustee_id_p" NOTNULL AND "simulate_default_p" THEN

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

2492 END IF;

2493 IF "simulate_trustee_id_p" NOTNULL OR "simulate_default_p" THEN

2494 "simulate_v" := TRUE;

2495 ELSE

2496 "simulate_v" := FALSE;

2497 END IF;

2498 IF

2499 "unit_id_p" NOTNULL AND

2500 "area_id_p" ISNULL AND

2501 "issue_id_p" ISNULL

2502 THEN

2503 "scope_v" := 'unit';

2504 "unit_id_v" := "unit_id_p";

2505 ELSIF

2506 "unit_id_p" ISNULL AND

2507 "area_id_p" NOTNULL AND

2508 "issue_id_p" ISNULL

2509 THEN

2510 "scope_v" := 'area';

2511 "area_id_v" := "area_id_p";

2512 SELECT "unit_id" INTO "unit_id_v"

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

2514 ELSIF

2515 "unit_id_p" ISNULL AND

2516 "area_id_p" ISNULL AND

2517 "issue_id_p" NOTNULL

2518 THEN

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

2520 IF "issue_row"."id" ISNULL THEN

2521 RETURN;

2522 END IF;

2523 IF "issue_row"."closed" NOTNULL THEN

2524 IF "simulate_v" THEN

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

2526 END IF;

2527 FOR "output_row" IN

2528 SELECT * FROM

2529 "delegation_chain_for_closed_issue"("member_id_p", "issue_id_p")

2530 LOOP

2531 RETURN NEXT "output_row";

2532 END LOOP;

2533 RETURN;

2534 END IF;

2535 "scope_v" := 'issue';

2536 SELECT "area_id" INTO "area_id_v"

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

2538 SELECT "unit_id" INTO "unit_id_v"

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

2540 ELSE

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

2542 END IF;

2543 "visited_member_ids" := '{}';

2544 "loop_member_id_v" := NULL;

2545 "output_rows" := '{}';

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

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

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

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

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

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

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

2553 LOOP

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

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

2556 ELSE

2557 "visited_member_ids" :=

2558 "visited_member_ids" || "output_row"."member_id";

2559 END IF;

2560 IF "output_row"."participation" ISNULL THEN

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

2562 ELSIF "output_row"."participation" THEN

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

2564 END IF;

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

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

2567 SELECT NULL FROM "member" JOIN "privilege"

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

2569 AND "privilege"."unit_id" = "unit_id_v"

2570 WHERE "id" = "output_row"."member_id"

2571 AND "member"."active" AND "privilege"."voting_right"

2572 );

2573 "simulate_here_v" := (

2574 "simulate_v" AND

2575 "output_row"."member_id" = "member_id_p"

2576 );

2577 "delegation_row" := ROW(NULL);

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

2579 IF "scope_v" = 'unit' THEN

2580 IF NOT "simulate_here_v" THEN

2581 SELECT * INTO "delegation_row" FROM "delegation"

2582 WHERE "truster_id" = "output_row"."member_id"

2583 AND "unit_id" = "unit_id_v";

2584 END IF;

2585 ELSIF "scope_v" = 'area' THEN

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

2587 SELECT NULL FROM "membership"

2588 WHERE "area_id" = "area_id_p"

2589 AND "member_id" = "output_row"."member_id"

2590 );

2591 IF "simulate_here_v" THEN

2592 IF "simulate_trustee_id_p" ISNULL THEN

2593 SELECT * INTO "delegation_row" FROM "delegation"

2594 WHERE "truster_id" = "output_row"."member_id"

2595 AND "unit_id" = "unit_id_v";

2596 END IF;

2597 ELSE

2598 SELECT * INTO "delegation_row" FROM "delegation"

2599 WHERE "truster_id" = "output_row"."member_id"

2600 AND (

2601 "unit_id" = "unit_id_v" OR

2602 "area_id" = "area_id_v"

2603 )

2604 ORDER BY "scope" DESC;

2605 END IF;

2606 ELSIF "scope_v" = 'issue' THEN

2607 IF "issue_row"."fully_frozen" ISNULL THEN

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

2609 SELECT NULL FROM "interest"

2610 WHERE "issue_id" = "issue_id_p"

2611 AND "member_id" = "output_row"."member_id"

2612 );

2613 ELSE

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

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

2616 SELECT NULL FROM "direct_voter"

2617 WHERE "issue_id" = "issue_id_p"

2618 AND "member_id" = "output_row"."member_id"

2619 );

2620 ELSE

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

2622 END IF;

2623 END IF;

2624 IF "simulate_here_v" THEN

2625 IF "simulate_trustee_id_p" ISNULL THEN

2626 SELECT * INTO "delegation_row" FROM "delegation"

2627 WHERE "truster_id" = "output_row"."member_id"

2628 AND (

2629 "unit_id" = "unit_id_v" OR

2630 "area_id" = "area_id_v"

2631 )

2632 ORDER BY "scope" DESC;

2633 END IF;

2634 ELSE

2635 SELECT * INTO "delegation_row" FROM "delegation"

2636 WHERE "truster_id" = "output_row"."member_id"

2637 AND (

2638 "unit_id" = "unit_id_v" OR

2639 "area_id" = "area_id_v" OR

2640 "issue_id" = "issue_id_p"

2641 )

2642 ORDER BY "scope" DESC;

2643 END IF;

2644 END IF;

2645 ELSE

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

2647 END IF;

2648 IF "simulate_here_v" AND "simulate_trustee_id_p" NOTNULL THEN

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

2650 "output_rows" := "output_rows" || "output_row";

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

2652 ELSIF "delegation_row"."trustee_id" NOTNULL THEN

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

2654 "output_rows" := "output_rows" || "output_row";

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

2656 ELSIF "delegation_row"."scope" NOTNULL THEN

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

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

2659 "output_rows" := "output_rows" || "output_row";

2660 EXIT;

2661 ELSE

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

2663 "output_rows" := "output_rows" || "output_row";

2664 EXIT;

2665 END IF;

2666 EXIT WHEN "loop_member_id_v" NOTNULL;

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

2668 END LOOP;

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

2670 "i" := 1;

2671 "loop_v" := FALSE;

2672 LOOP

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

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

2675 IF "loop_v" THEN

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

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

2678 ELSIF "i" = "row_count" THEN

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

2680 ELSE

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

2682 END IF;

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

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

2685 "loop_v" := TRUE;

2686 END IF;

2687 IF "scope_v" = 'unit' THEN

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

2689 END IF;

2690 RETURN NEXT "output_row";

2691 "i" := "i" + 1;

2692 END LOOP;

2693 RETURN;

2694 END;

2695 $$;

2696

2697 COMMENT ON FUNCTION "delegation_chain"

2698 ( "member"."id"%TYPE,

2699 "unit"."id"%TYPE,

2700 "area"."id"%TYPE,

2701 "issue"."id"%TYPE,

2702 "member"."id"%TYPE,

2703 BOOLEAN )

2704 IS 'Shows a delegation chain for unit, area, or issue; See "delegation_chain_row" type for more information';

2705

2706

2707

2708 ---------------------------------------------------------

2709 -- Single row returning function for delegation chains --

2710 ---------------------------------------------------------

2711

2712

2713 CREATE TYPE "delegation_info_loop_type" AS ENUM

2714 ('own', 'first', 'first_ellipsis', 'other', 'other_ellipsis');

2715

2716 COMMENT ON TYPE "delegation_info_loop_type" IS 'Type of "delegation_loop" in "delegation_info_type"; ''own'' means loop to self, ''first'' means loop to first trustee, ''first_ellipsis'' means loop to ellipsis after first trustee, ''other'' means loop to other trustee, ''other_ellipsis'' means loop to ellipsis after other trustee''';

2717

2718

2719 CREATE TYPE "delegation_info_type" AS (

2720 "own_participation" BOOLEAN,

2721 "own_delegation_scope" "delegation_scope",

2722 "first_trustee_id" INT4,

2723 "first_trustee_participation" BOOLEAN,

2724 "first_trustee_ellipsis" BOOLEAN,

2725 "other_trustee_id" INT4,

2726 "other_trustee_participation" BOOLEAN,

2727 "other_trustee_ellipsis" BOOLEAN,

2728 "delegation_loop" "delegation_info_loop_type",

2729 "participating_member_id" INT4 );

2730

2731 COMMENT ON TYPE "delegation_info_type" IS 'Type of result returned by "delegation_info" function; For meaning of "participation" check comment on "delegation_chain_row" type';

2732

2733 COMMENT ON COLUMN "delegation_info_type"."own_participation" IS 'Member is directly participating';

2734 COMMENT ON COLUMN "delegation_info_type"."own_delegation_scope" IS 'Delegation scope of member';

2735 COMMENT ON COLUMN "delegation_info_type"."first_trustee_id" IS 'Direct trustee of member';

2736 COMMENT ON COLUMN "delegation_info_type"."first_trustee_participation" IS 'Direct trustee of member is participating';

2737 COMMENT ON COLUMN "delegation_info_type"."first_trustee_ellipsis" IS 'Ellipsis in delegation chain after "first_trustee"';

2738 COMMENT ON COLUMN "delegation_info_type"."other_trustee_id" IS 'Another relevant trustee (due to participation)';

2739 COMMENT ON COLUMN "delegation_info_type"."other_trustee_participation" IS 'Another trustee is participating (redundant field: if "other_trustee_id" is set, then "other_trustee_participation" is always TRUE, else "other_trustee_participation" is NULL)';

2740 COMMENT ON COLUMN "delegation_info_type"."other_trustee_ellipsis" IS 'Ellipsis in delegation chain after "other_trustee"';

2741 COMMENT ON COLUMN "delegation_info_type"."delegation_loop" IS 'Non-NULL value, if delegation chain contains a circle; See comment on "delegation_info_loop_type" for details';

2742 COMMENT ON COLUMN "delegation_info_type"."participating_member_id" IS 'First participating member in delegation chain';

2743

2744

2745 CREATE FUNCTION "delegation_info"

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

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

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

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

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

2751 "simulate_default_p" BOOLEAN DEFAULT FALSE )

2752 RETURNS "delegation_info_type"

2753 LANGUAGE 'plpgsql' STABLE AS $$

2754 DECLARE

2755 "current_row" "delegation_chain_row";

2756 "result" "delegation_info_type";

2757 BEGIN

2758 "result"."own_participation" := FALSE;

2759 FOR "current_row" IN

2760 SELECT * FROM "delegation_chain"(

2761 "member_id_p",

2762 "unit_id_p", "area_id_p", "issue_id_p",

2763 "simulate_trustee_id_p", "simulate_default_p")

2764 LOOP

2765 IF

2766 "result"."participating_member_id" ISNULL AND

2767 "current_row"."participation"

2768 THEN

2769 "result"."participating_member_id" := "current_row"."member_id";

2770 END IF;

2771 IF "current_row"."member_id" = "member_id_p" THEN

2772 "result"."own_participation" := "current_row"."participation";

2773 "result"."own_delegation_scope" := "current_row"."scope_out";

2774 IF "current_row"."loop" = 'first' THEN

2775 "result"."delegation_loop" := 'own';

2776 END IF;

2777 ELSIF

2778 "current_row"."member_valid" AND

2779 ( "current_row"."loop" ISNULL OR

2780 "current_row"."loop" != 'repetition' )

2781 THEN

2782 IF "result"."first_trustee_id" ISNULL THEN

2783 "result"."first_trustee_id" := "current_row"."member_id";

2784 "result"."first_trustee_participation" := "current_row"."participation";

2785 "result"."first_trustee_ellipsis" := FALSE;

2786 IF "current_row"."loop" = 'first' THEN

2787 "result"."delegation_loop" := 'first';

2788 END IF;

2789 ELSIF "result"."other_trustee_id" ISNULL THEN

2790 IF "current_row"."participation" AND NOT "current_row"."overridden" THEN

2791 "result"."other_trustee_id" := "current_row"."member_id";

2792 "result"."other_trustee_participation" := TRUE;

2793 "result"."other_trustee_ellipsis" := FALSE;

2794 IF "current_row"."loop" = 'first' THEN

2795 "result"."delegation_loop" := 'other';

2796 END IF;

2797 ELSE

2798 "result"."first_trustee_ellipsis" := TRUE;

2799 IF "current_row"."loop" = 'first' THEN

2800 "result"."delegation_loop" := 'first_ellipsis';

2801 END IF;

2802 END IF;

2803 ELSE

2804 "result"."other_trustee_ellipsis" := TRUE;

2805 IF "current_row"."loop" = 'first' THEN

2806 "result"."delegation_loop" := 'other_ellipsis';

2807 END IF;

2808 END IF;

2809 END IF;

2810 END LOOP;

2811 RETURN "result";

2812 END;

2813 $$;

2814

2815 COMMENT ON FUNCTION "delegation_info"

2816 ( "member"."id"%TYPE,

2817 "unit"."id"%TYPE,

2818 "area"."id"%TYPE,

2819 "issue"."id"%TYPE,

2820 "member"."id"%TYPE,

2821 BOOLEAN )

2822 IS 'Notable information about a delegation chain for unit, area, or issue; See "delegation_info_type" for more information';

2823

2824

2825

2826 ---------------------------

2827 -- Transaction isolation --

2828 ---------------------------

2829

2830

2831 CREATE FUNCTION "require_transaction_isolation"()

2832 RETURNS VOID

2833 LANGUAGE 'plpgsql' VOLATILE AS $$

2834 BEGIN

2835 IF

2836 current_setting('transaction_isolation') NOT IN

2837 ('repeatable read', 'serializable')

2838 THEN

2839 RAISE EXCEPTION 'Insufficient transaction isolation level';

2840 END IF;

2841 RETURN;

2842 END;

2843 $$;

2844

2845 COMMENT ON FUNCTION "require_transaction_isolation"() IS 'Throws an exception, if transaction isolation level is too low to provide a consistent snapshot';

2846

2847

2848 CREATE FUNCTION "dont_require_transaction_isolation"()

2849 RETURNS VOID

2850 LANGUAGE 'plpgsql' VOLATILE AS $$

2851 BEGIN

2852 IF

2853 current_setting('transaction_isolation') IN

2854 ('repeatable read', 'serializable')

2855 THEN

2856 RAISE WARNING 'Unneccessary transaction isolation level: %',

2857 current_setting('transaction_isolation');

2858 END IF;

2859 RETURN;

2860 END;

2861 $$;

2862

2863 COMMENT ON FUNCTION "dont_require_transaction_isolation"() IS 'Raises a warning, if transaction isolation level is higher than READ COMMITTED';

2864

2865

2866

2867 ------------------------------------------------------------------------

2868 -- Regular tasks, except calculcation of snapshots and voting results --

2869 ------------------------------------------------------------------------

2870

2871

2872 CREATE FUNCTION "check_activity"()

2873 RETURNS VOID

2874 LANGUAGE 'plpgsql' VOLATILE AS $$

2875 DECLARE

2876 "system_setting_row" "system_setting"%ROWTYPE;

2877 BEGIN

2878 PERFORM "dont_require_transaction_isolation"();

2879 SELECT * INTO "system_setting_row" FROM "system_setting";

2880 IF "system_setting_row"."member_ttl" NOTNULL THEN

2881 UPDATE "member" SET "active" = FALSE

2882 WHERE "active" = TRUE

2883 AND "last_activity" < (now() - "system_setting_row"."member_ttl")::DATE;

2884 END IF;

2885 RETURN;

2886 END;

2887 $$;

2888

2889 COMMENT ON FUNCTION "check_activity"() IS 'Deactivates members when "last_activity" is older than "system_setting"."member_ttl".';

2890

2891

2892 CREATE FUNCTION "calculate_member_counts"()

2893 RETURNS VOID

2894 LANGUAGE 'plpgsql' VOLATILE AS $$

2895 BEGIN

2896 PERFORM "require_transaction_isolation"();

2897 DELETE FROM "member_count";

2898 INSERT INTO "member_count" ("total_count")

2899 SELECT "total_count" FROM "member_count_view";

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

2901 FROM "unit_member_count" AS "view"

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

2903 UPDATE "area" SET

2904 "direct_member_count" = "view"."direct_member_count",

2905 "member_weight" = "view"."member_weight"

2906 FROM "area_member_count" AS "view"

2907 WHERE "view"."area_id" = "area"."id";

2908 RETURN;

2909 END;

2910 $$;

2911

2912 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 "area_member_count"';

2913

2914

2915

2916 ------------------------------------

2917 -- Calculation of harmonic weight --

2918 ------------------------------------

2919

2920

2921 CREATE VIEW "remaining_harmonic_supporter_weight" AS

2922 SELECT

2923 "direct_interest_snapshot"."issue_id",

2924 "direct_interest_snapshot"."event",

2925 "direct_interest_snapshot"."member_id",

2926 "direct_interest_snapshot"."weight" AS "weight_num",

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

2928 FROM "issue"

2929 JOIN "direct_interest_snapshot"

2930 ON "issue"."id" = "direct_interest_snapshot"."issue_id"

2931 AND "issue"."latest_snapshot_event" = "direct_interest_snapshot"."event"

2932 JOIN "initiative"

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

2934 AND "initiative"."harmonic_weight" ISNULL

2935 JOIN "direct_supporter_snapshot"

2936 ON "initiative"."id" = "direct_supporter_snapshot"."initiative_id"

2937 AND "direct_interest_snapshot"."event" = "direct_supporter_snapshot"."event"

2938 AND "direct_interest_snapshot"."member_id" = "direct_supporter_snapshot"."member_id"

2939 AND (

2940 "direct_supporter_snapshot"."satisfied" = TRUE OR

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

2942 )

2943 GROUP BY

2944 "direct_interest_snapshot"."issue_id",

2945 "direct_interest_snapshot"."event",

2946 "direct_interest_snapshot"."member_id",

2947 "direct_interest_snapshot"."weight";

2948

2949 COMMENT ON VIEW "remaining_harmonic_supporter_weight" IS 'Helper view for function "set_harmonic_initiative_weights"';

2950

2951

2952 CREATE VIEW "remaining_harmonic_initiative_weight_summands" AS

2953 SELECT

2954 "initiative"."issue_id",

2955 "initiative"."id" AS "initiative_id",

2956 "initiative"."admitted",

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

2958 "remaining_harmonic_supporter_weight"."weight_den"

2959 FROM "remaining_harmonic_supporter_weight"

2960 JOIN "initiative"

2961 ON "remaining_harmonic_supporter_weight"."issue_id" = "initiative"."issue_id"

2962 AND "initiative"."harmonic_weight" ISNULL

2963 JOIN "direct_supporter_snapshot"

2964 ON "initiative"."id" = "direct_supporter_snapshot"."initiative_id"

2965 AND "remaining_harmonic_supporter_weight"."event" = "direct_supporter_snapshot"."event"

2966 AND "remaining_harmonic_supporter_weight"."member_id" = "direct_supporter_snapshot"."member_id"

2967 AND (

2968 "direct_supporter_snapshot"."satisfied" = TRUE OR

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

2970 )

2971 GROUP BY

2972 "initiative"."issue_id",

2973 "initiative"."id",

2974 "initiative"."admitted",

2975 "remaining_harmonic_supporter_weight"."weight_den";

2976

2977 COMMENT ON VIEW "remaining_harmonic_initiative_weight_summands" IS 'Helper view for function "set_harmonic_initiative_weights"';

2978

2979

2980 CREATE VIEW "remaining_harmonic_initiative_weight_dummies" AS

2981 SELECT

2982 "issue_id",

2983 "id" AS "initiative_id",

2984 "admitted",

2985 0 AS "weight_num",

2986 1 AS "weight_den"

2987 FROM "initiative"

2988 WHERE "harmonic_weight" ISNULL;

2989

2990 COMMENT ON VIEW "remaining_harmonic_initiative_weight_dummies" IS 'Helper view for function "set_harmonic_initiative_weights" providing dummy weights of zero value, which are needed for corner cases where there are no supporters for an initiative at all';

2991

2992

2993 CREATE FUNCTION "set_harmonic_initiative_weights"

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

2995 RETURNS VOID

2996 LANGUAGE 'plpgsql' VOLATILE AS $$

2997 DECLARE

2998 "weight_row" "remaining_harmonic_initiative_weight_summands"%ROWTYPE;

2999 "i" INT4;

3000 "count_v" INT4;

3001 "summand_v" FLOAT;

3002 "id_ary" INT4[];

3003 "weight_ary" FLOAT[];

3004 "min_weight_v" FLOAT;

3005 BEGIN

3006 PERFORM "require_transaction_isolation"();

3007 UPDATE "initiative" SET "harmonic_weight" = NULL

3008 WHERE "issue_id" = "issue_id_p";

3009 LOOP

3010 "min_weight_v" := NULL;

3011 "i" := 0;

3012 "count_v" := 0;

3013 FOR "weight_row" IN

3014 SELECT * FROM "remaining_harmonic_initiative_weight_summands"

3015 WHERE "issue_id" = "issue_id_p"

3016 AND (

3017 coalesce("admitted", FALSE) = FALSE OR NOT EXISTS (

3018 SELECT NULL FROM "initiative"

3019 WHERE "issue_id" = "issue_id_p"

3020 AND "harmonic_weight" ISNULL

3021 AND coalesce("admitted", FALSE) = FALSE

3022 )

3023 )

3024 UNION ALL -- needed for corner cases

3025 SELECT * FROM "remaining_harmonic_initiative_weight_dummies"

3026 WHERE "issue_id" = "issue_id_p"

3027 AND (

3028 coalesce("admitted", FALSE) = FALSE OR NOT EXISTS (

3029 SELECT NULL FROM "initiative"

3030 WHERE "issue_id" = "issue_id_p"

3031 AND "harmonic_weight" ISNULL

3032 AND coalesce("admitted", FALSE) = FALSE

3033 )

3034 )

3035 ORDER BY "initiative_id" DESC, "weight_den" DESC

3036 -- NOTE: non-admitted initiatives placed first (at last positions),

3037 -- latest initiatives treated worse in case of tie

3038 LOOP

3039 "summand_v" := "weight_row"."weight_num"::FLOAT / "weight_row"."weight_den"::FLOAT;

3040 IF "i" = 0 OR "weight_row"."initiative_id" != "id_ary"["i"] THEN

3041 "i" := "i" + 1;

3042 "count_v" := "i";

3043 "id_ary"["i"] := "weight_row"."initiative_id";

3044 "weight_ary"["i"] := "summand_v";

3045 ELSE

3046 "weight_ary"["i"] := "weight_ary"["i"] + "summand_v";

3047 END IF;

3048 END LOOP;

3049 EXIT WHEN "count_v" = 0;

3050 "i" := 1;

3051 LOOP

3052 "weight_ary"["i"] := "weight_ary"["i"]::NUMERIC(18,9)::NUMERIC(12,3);

3053 IF "min_weight_v" ISNULL OR "weight_ary"["i"] < "min_weight_v" THEN

3054 "min_weight_v" := "weight_ary"["i"];

3055 END IF;

3056 "i" := "i" + 1;

3057 EXIT WHEN "i" > "count_v";

3058 END LOOP;

3059 "i" := 1;

3060 LOOP

3061 IF "weight_ary"["i"] = "min_weight_v" THEN

3062 UPDATE "initiative" SET "harmonic_weight" = "min_weight_v"

3063 WHERE "id" = "id_ary"["i"];

3064 EXIT;

3065 END IF;

3066 "i" := "i" + 1;

3067 END LOOP;

3068 END LOOP;

3069 UPDATE "initiative" SET "harmonic_weight" = 0

3070 WHERE "issue_id" = "issue_id_p" AND "harmonic_weight" ISNULL;

3071 END;

3072 $$;

3073

3074 COMMENT ON FUNCTION "set_harmonic_initiative_weights"

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

3076 IS 'Calculates and sets "harmonic_weight" of initiatives in a given issue';

3077

3078

3079

3080 ------------------------------

3081 -- Calculation of snapshots --

3082 ------------------------------

3083

3084

3085 CREATE FUNCTION "weight_of_added_delegations_for_population_snapshot"

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

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

3088 "delegate_member_ids_p" "delegating_population_snapshot"."delegate_member_ids"%TYPE )

3089 RETURNS "direct_population_snapshot"."weight"%TYPE

3090 LANGUAGE 'plpgsql' VOLATILE AS $$

3091 DECLARE

3092 "issue_delegation_row" "issue_delegation"%ROWTYPE;

3093 "delegate_member_ids_v" "delegating_population_snapshot"."delegate_member_ids"%TYPE;

3094 "weight_v" INT4;

3095 "sub_weight_v" INT4;

3096 BEGIN

3097 PERFORM "require_transaction_isolation"();

3098 "weight_v" := 0;

3099 FOR "issue_delegation_row" IN

3100 SELECT * FROM "issue_delegation"

3101 WHERE "trustee_id" = "member_id_p"

3102 AND "issue_id" = "issue_id_p"

3103 LOOP

3104 IF NOT EXISTS (

3105 SELECT NULL FROM "direct_population_snapshot"

3106 WHERE "issue_id" = "issue_id_p"

3107 AND "event" = 'periodic'

3108 AND "member_id" = "issue_delegation_row"."truster_id"

3109 ) AND NOT EXISTS (

3110 SELECT NULL FROM "delegating_population_snapshot"

3111 WHERE "issue_id" = "issue_id_p"

3112 AND "event" = 'periodic'

3113 AND "member_id" = "issue_delegation_row"."truster_id"

3114 ) THEN

3115 "delegate_member_ids_v" :=

3116 "member_id_p" || "delegate_member_ids_p";

3117 INSERT INTO "delegating_population_snapshot" (

3118 "issue_id",

3119 "event",

3120 "member_id",

3121 "scope",

3122 "delegate_member_ids"

3123 ) VALUES (

3124 "issue_id_p",

3125 'periodic',

3126 "issue_delegation_row"."truster_id",

3127 "issue_delegation_row"."scope",

3128 "delegate_member_ids_v"

3129 );

3130 "sub_weight_v" := 1 +

3131 "weight_of_added_delegations_for_population_snapshot"(

3132 "issue_id_p",

3133 "issue_delegation_row"."truster_id",

3134 "delegate_member_ids_v"

3135 );

3136 UPDATE "delegating_population_snapshot"

3137 SET "weight" = "sub_weight_v"

3138 WHERE "issue_id" = "issue_id_p"

3139 AND "event" = 'periodic'

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

3141 "weight_v" := "weight_v" + "sub_weight_v";

3142 END IF;

3143 END LOOP;

3144 RETURN "weight_v";

3145 END;

3146 $$;

3147

3148 COMMENT ON FUNCTION "weight_of_added_delegations_for_population_snapshot"

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

3150 "member"."id"%TYPE,

3151 "delegating_population_snapshot"."delegate_member_ids"%TYPE )

3152 IS 'Helper function for "create_population_snapshot" function';

3153

3154

3155 CREATE FUNCTION "create_population_snapshot"

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

3157 RETURNS VOID

3158 LANGUAGE 'plpgsql' VOLATILE AS $$

3159 DECLARE

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

3161 BEGIN

3162 PERFORM "require_transaction_isolation"();

3163 DELETE FROM "direct_population_snapshot"

3164 WHERE "issue_id" = "issue_id_p"

3165 AND "event" = 'periodic';

3166 DELETE FROM "delegating_population_snapshot"

3167 WHERE "issue_id" = "issue_id_p"

3168 AND "event" = 'periodic';

3169 INSERT INTO "direct_population_snapshot"

3170 ("issue_id", "event", "member_id")

3171 SELECT

3172 "issue_id_p" AS "issue_id",

3173 'periodic'::"snapshot_event" AS "event",

3174 "member"."id" AS "member_id"

3175 FROM "issue"

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

3177 JOIN "membership" ON "area"."id" = "membership"."area_id"

3178 JOIN "member" ON "membership"."member_id" = "member"."id"

3179 JOIN "privilege"

3180 ON "privilege"."unit_id" = "area"."unit_id"

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

3182 WHERE "issue"."id" = "issue_id_p"

3183 AND "member"."active" AND "privilege"."voting_right"

3184 UNION

3185 SELECT

3186 "issue_id_p" AS "issue_id",

3187 'periodic'::"snapshot_event" AS "event",

3188 "member"."id" AS "member_id"

3189 FROM "issue"

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

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

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

3193 JOIN "privilege"

3194 ON "privilege"."unit_id" = "area"."unit_id"

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

3196 WHERE "issue"."id" = "issue_id_p"

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

3198 FOR "member_id_v" IN

3199 SELECT "member_id" FROM "direct_population_snapshot"

3200 WHERE "issue_id" = "issue_id_p"

3201 AND "event" = 'periodic'

3202 LOOP

3203 UPDATE "direct_population_snapshot" SET

3204 "weight" = 1 +

3205 "weight_of_added_delegations_for_population_snapshot"(

3206 "issue_id_p",

3207 "member_id_v",

3208 '{}'

3209 )

3210 WHERE "issue_id" = "issue_id_p"

3211 AND "event" = 'periodic'

3212 AND "member_id" = "member_id_v";

3213 END LOOP;

3214 RETURN;

3215 END;

3216 $$;

3217

3218 COMMENT ON FUNCTION "create_population_snapshot"

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

3220 IS 'This function creates a new ''periodic'' population snapshot for the given issue. It does neither lock any tables, nor updates precalculated values in other tables.';

3221

3222

3223 CREATE FUNCTION "weight_of_added_delegations_for_interest_snapshot"

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

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

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

3227 RETURNS "direct_interest_snapshot"."weight"%TYPE

3228 LANGUAGE 'plpgsql' VOLATILE AS $$

3229 DECLARE

3230 "issue_delegation_row" "issue_delegation"%ROWTYPE;

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

3232 "weight_v" INT4;

3233 "sub_weight_v" INT4;

3234 BEGIN

3235 PERFORM "require_transaction_isolation"();

3236 "weight_v" := 0;

3237 FOR "issue_delegation_row" IN

3238 SELECT * FROM "issue_delegation"

3239 WHERE "trustee_id" = "member_id_p"

3240 AND "issue_id" = "issue_id_p"

3241 LOOP

3242 IF NOT EXISTS (

3243 SELECT NULL FROM "direct_interest_snapshot"

3244 WHERE "issue_id" = "issue_id_p"

3245 AND "event" = 'periodic'

3246 AND "member_id" = "issue_delegation_row"."truster_id"

3247 ) AND NOT EXISTS (

3248 SELECT NULL FROM "delegating_interest_snapshot"

3249 WHERE "issue_id" = "issue_id_p"

3250 AND "event" = 'periodic'

3251 AND "member_id" = "issue_delegation_row"."truster_id"

3252 ) THEN

3253 "delegate_member_ids_v" :=

3254 "member_id_p" || "delegate_member_ids_p";

3255 INSERT INTO "delegating_interest_snapshot" (

3256 "issue_id",

3257 "event",

3258 "member_id",

3259 "scope",

3260 "delegate_member_ids"

3261 ) VALUES (

3262 "issue_id_p",

3263 'periodic',

3264 "issue_delegation_row"."truster_id",

3265 "issue_delegation_row"."scope",

3266 "delegate_member_ids_v"

3267 );

3268 "sub_weight_v" := 1 +

3269 "weight_of_added_delegations_for_interest_snapshot"(

3270 "issue_id_p",

3271 "issue_delegation_row"."truster_id",

3272 "delegate_member_ids_v"

3273 );

3274 UPDATE "delegating_interest_snapshot"

3275 SET "weight" = "sub_weight_v"

3276 WHERE "issue_id" = "issue_id_p"

3277 AND "event" = 'periodic'

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

3279 "weight_v" := "weight_v" + "sub_weight_v";

3280 END IF;

3281 END LOOP;

3282 RETURN "weight_v";

3283 END;

3284 $$;

3285

3286 COMMENT ON FUNCTION "weight_of_added_delegations_for_interest_snapshot"

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

3288 "member"."id"%TYPE,

3289 "delegating_interest_snapshot"."delegate_member_ids"%TYPE )

3290 IS 'Helper function for "create_interest_snapshot" function';

3291

3292

3293 CREATE FUNCTION "create_interest_snapshot"

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

3295 RETURNS VOID

3296 LANGUAGE 'plpgsql' VOLATILE AS $$

3297 DECLARE

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

3299 BEGIN

3300 PERFORM "require_transaction_isolation"();

3301 DELETE FROM "direct_interest_snapshot"

3302 WHERE "issue_id" = "issue_id_p"

3303 AND "event" = 'periodic';

3304 DELETE FROM "delegating_interest_snapshot"

3305 WHERE "issue_id" = "issue_id_p"

3306 AND "event" = 'periodic';

3307 DELETE FROM "direct_supporter_snapshot"

3308 USING "initiative" -- NOTE: due to missing index on issue_id

3309 WHERE "initiative"."issue_id" = "issue_id_p"

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

3311 AND "direct_supporter_snapshot"."event" = 'periodic';

3312 INSERT INTO "direct_interest_snapshot"

3313 ("issue_id", "event", "member_id")

3314 SELECT

3315 "issue_id_p" AS "issue_id",

3316 'periodic' AS "event",

3317 "member"."id" AS "member_id"

3318 FROM "issue"

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

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

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

3322 JOIN "privilege"

3323 ON "privilege"."unit_id" = "area"."unit_id"

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

3325 WHERE "issue"."id" = "issue_id_p"

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

3327 FOR "member_id_v" IN

3328 SELECT "member_id" FROM "direct_interest_snapshot"

3329 WHERE "issue_id" = "issue_id_p"

3330 AND "event" = 'periodic'

3331 LOOP

3332 UPDATE "direct_interest_snapshot" SET

3333 "weight" = 1 +

3334 "weight_of_added_delegations_for_interest_snapshot"(

3335 "issue_id_p",

3336 "member_id_v",

3337 '{}'

3338 )

3339 WHERE "issue_id" = "issue_id_p"

3340 AND "event" = 'periodic'

3341 AND "member_id" = "member_id_v";

3342 END LOOP;

3343 INSERT INTO "direct_supporter_snapshot"

3344 ( "issue_id", "initiative_id", "event", "member_id",

3345 "draft_id", "informed", "satisfied" )

3346 SELECT

3347 "issue_id_p" AS "issue_id",

3348 "initiative"."id" AS "initiative_id",

3349 'periodic' AS "event",

3350 "supporter"."member_id" AS "member_id",

3351 "supporter"."draft_id" AS "draft_id",

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

3353 NOT EXISTS (

3354 SELECT NULL FROM "critical_opinion"

3355 WHERE "initiative_id" = "initiative"."id"

3356 AND "member_id" = "supporter"."member_id"

3357 ) AS "satisfied"

3358 FROM "initiative"

3359 JOIN "supporter"

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

3361 JOIN "current_draft"

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

3363 JOIN "direct_interest_snapshot"

3364 ON "supporter"."member_id" = "direct_interest_snapshot"."member_id"

3365 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"

3366 AND "event" = 'periodic'

3367 WHERE "initiative"."issue_id" = "issue_id_p";

3368 RETURN;

3369 END;

3370 $$;

3371

3372 COMMENT ON FUNCTION "create_interest_snapshot"

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

3374 IS 'This function creates a new ''periodic'' interest/supporter snapshot for the given issue. It does neither lock any tables, nor updates precalculated values in other tables.';

3375

3376

3377 CREATE FUNCTION "create_snapshot"

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

3379 RETURNS VOID

3380 LANGUAGE 'plpgsql' VOLATILE AS $$

3381 DECLARE

3382 "initiative_id_v" "initiative"."id"%TYPE;

3383 "suggestion_id_v" "suggestion"."id"%TYPE;

3384 BEGIN

3385 PERFORM "require_transaction_isolation"();

3386 PERFORM "create_population_snapshot"("issue_id_p");

3387 PERFORM "create_interest_snapshot"("issue_id_p");

3388 UPDATE "issue" SET

3389 "snapshot" = coalesce("phase_finished", now()),

3390 "latest_snapshot_event" = 'periodic',

3391 "population" = (

3392 SELECT coalesce(sum("weight"), 0)

3393 FROM "direct_population_snapshot"

3394 WHERE "issue_id" = "issue_id_p"

3395 AND "event" = 'periodic'

3396 )

3397 WHERE "id" = "issue_id_p";

3398 FOR "initiative_id_v" IN

3399 SELECT "id" FROM "initiative" WHERE "issue_id" = "issue_id_p"

3400 LOOP

3401 UPDATE "initiative" SET

3402 "supporter_count" = (

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

3404 FROM "direct_interest_snapshot" AS "di"

3405 JOIN "direct_supporter_snapshot" AS "ds"

3406 ON "di"."member_id" = "ds"."member_id"

3407 WHERE "di"."issue_id" = "issue_id_p"

3408 AND "di"."event" = 'periodic'

3409 AND "ds"."initiative_id" = "initiative_id_v"

3410 AND "ds"."event" = 'periodic'

3411 ),

3412 "informed_supporter_count" = (

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

3414 FROM "direct_interest_snapshot" AS "di"

3415 JOIN "direct_supporter_snapshot" AS "ds"

3416 ON "di"."member_id" = "ds"."member_id"

3417 WHERE "di"."issue_id" = "issue_id_p"

3418 AND "di"."event" = 'periodic'

3419 AND "ds"."initiative_id" = "initiative_id_v"

3420 AND "ds"."event" = 'periodic'

3421 AND "ds"."informed"

3422 ),

3423 "satisfied_supporter_count" = (

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

3425 FROM "direct_interest_snapshot" AS "di"

3426 JOIN "direct_supporter_snapshot" AS "ds"

3427 ON "di"."member_id" = "ds"."member_id"

3428 WHERE "di"."issue_id" = "issue_id_p"

3429 AND "di"."event" = 'periodic'

3430 AND "ds"."initiative_id" = "initiative_id_v"

3431 AND "ds"."event" = 'periodic'

3432 AND "ds"."satisfied"

3433 ),

3434 "satisfied_informed_supporter_count" = (

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

3436 FROM "direct_interest_snapshot" AS "di"

3437 JOIN "direct_supporter_snapshot" AS "ds"

3438 ON "di"."member_id" = "ds"."member_id"

3439 WHERE "di"."issue_id" = "issue_id_p"

3440 AND "di"."event" = 'periodic'

3441 AND "ds"."initiative_id" = "initiative_id_v"

3442 AND "ds"."event" = 'periodic'

3443 AND "ds"."informed"

3444 AND "ds"."satisfied"

3445 )

3446 WHERE "id" = "initiative_id_v";

3447 FOR "suggestion_id_v" IN

3448 SELECT "id" FROM "suggestion"

3449 WHERE "initiative_id" = "initiative_id_v"

3450 LOOP

3451 UPDATE "suggestion" SET

3452 "minus2_unfulfilled_count" = (

3453 SELECT coalesce(sum("snapshot"."weight"), 0)

3454 FROM "issue" CROSS JOIN "opinion"

3455 JOIN "direct_interest_snapshot" AS "snapshot"

3456 ON "snapshot"."issue_id" = "issue"."id"

3457 AND "snapshot"."event" = "issue"."latest_snapshot_event"

3458 AND "snapshot"."member_id" = "opinion"."member_id"

3459 WHERE "issue"."id" = "issue_id_p"

3460 AND "opinion"."suggestion_id" = "suggestion_id_v"

3461 AND "opinion"."degree" = -2

3462 AND "opinion"."fulfilled" = FALSE

3463 ),

3464 "minus2_fulfilled_count" = (

3465 SELECT coalesce(sum("snapshot"."weight"), 0)

3466 FROM "issue" CROSS JOIN "opinion"

3467 JOIN "direct_interest_snapshot" AS "snapshot"

3468 ON "snapshot"."issue_id" = "issue"."id"

3469 AND "snapshot"."event" = "issue"."latest_snapshot_event"

3470 AND "snapshot"."member_id" = "opinion"."member_id"

3471 WHERE "issue"."id" = "issue_id_p"

3472 AND "opinion"."suggestion_id" = "suggestion_id_v"

3473 AND "opinion"."degree" = -2

3474 AND "opinion"."fulfilled" = TRUE

3475 ),

3476 "minus1_unfulfilled_count" = (

3477 SELECT coalesce(sum("snapshot"."weight"), 0)

3478 FROM "issue" CROSS JOIN "opinion"

3479 JOIN "direct_interest_snapshot" AS "snapshot"

3480 ON "snapshot"."issue_id" = "issue"."id"

3481 AND "snapshot"."event" = "issue"."latest_snapshot_event"

3482 AND "snapshot"."member_id" = "opinion"."member_id"

3483 WHERE "issue"."id" = "issue_id_p"

3484 AND "opinion"."suggestion_id" = "suggestion_id_v"

3485 AND "opinion"."degree" = -1

3486 AND "opinion"."fulfilled" = FALSE

3487 ),

3488 "minus1_fulfilled_count" = (

3489 SELECT coalesce(sum("snapshot"."weight"), 0)

3490 FROM "issue" CROSS JOIN "opinion"

3491 JOIN "direct_interest_snapshot" AS "snapshot"

3492 ON "snapshot"."issue_id" = "issue"."id"

3493 AND "snapshot"."event" = "issue"."latest_snapshot_event"

3494 AND "snapshot"."member_id" = "opinion"."member_id"

3495 WHERE "issue"."id" = "issue_id_p"

3496 AND "opinion"."suggestion_id" = "suggestion_id_v"

3497 AND "opinion"."degree" = -1

3498 AND "opinion"."fulfilled" = TRUE

3499 ),

3500 "plus1_unfulfilled_count" = (

3501 SELECT coalesce(sum("snapshot"."weight"), 0)

3502 FROM "issue" CROSS JOIN "opinion"

3503 JOIN "direct_interest_snapshot" AS "snapshot"

3504 ON "snapshot"."issue_id" = "issue"."id"

3505 AND "snapshot"."event" = "issue"."latest_snapshot_event"

3506 AND "snapshot"."member_id" = "opinion"."member_id"

3507 WHERE "issue"."id" = "issue_id_p"

3508 AND "opinion"."suggestion_id" = "suggestion_id_v"

3509 AND "opinion"."degree" = 1

3510 AND "opinion"."fulfilled" = FALSE

3511 ),

3512 "plus1_fulfilled_count" = (

3513 SELECT coalesce(sum("snapshot"."weight"), 0)

3514 FROM "issue" CROSS JOIN "opinion"

3515 JOIN "direct_interest_snapshot" AS "snapshot"

3516 ON "snapshot"."issue_id" = "issue"."id"

3517 AND "snapshot"."event" = "issue"."latest_snapshot_event"

3518 AND "snapshot"."member_id" = "opinion"."member_id"

3519 WHERE "issue"."id" = "issue_id_p"

3520 AND "opinion"."suggestion_id" = "suggestion_id_v"

3521 AND "opinion"."degree" = 1

3522 AND "opinion"."fulfilled" = TRUE

3523 ),

3524 "plus2_unfulfilled_count" = (

3525 SELECT coalesce(sum("snapshot"."weight"), 0)

3526 FROM "issue" CROSS JOIN "opinion"

3527 JOIN "direct_interest_snapshot" AS "snapshot"

3528 ON "snapshot"."issue_id" = "issue"."id"

3529 AND "snapshot"."event" = "issue"."latest_snapshot_event"

3530 AND "snapshot"."member_id" = "opinion"."member_id"

3531 WHERE "issue"."id" = "issue_id_p"

3532 AND "opinion"."suggestion_id" = "suggestion_id_v"

3533 AND "opinion"."degree" = 2

3534 AND "opinion"."fulfilled" = FALSE

3535 ),

3536 "plus2_fulfilled_count" = (

3537 SELECT coalesce(sum("snapshot"."weight"), 0)

3538 FROM "issue" CROSS JOIN "opinion"

3539 JOIN "direct_interest_snapshot" AS "snapshot"

3540 ON "snapshot"."issue_id" = "issue"."id"

3541 AND "snapshot"."event" = "issue"."latest_snapshot_event"

3542 AND "snapshot"."member_id" = "opinion"."member_id"

3543 WHERE "issue"."id" = "issue_id_p"

3544 AND "opinion"."suggestion_id" = "suggestion_id_v"

3545 AND "opinion"."degree" = 2

3546 AND "opinion"."fulfilled" = TRUE

3547 )

3548 WHERE "suggestion"."id" = "suggestion_id_v";

3549 END LOOP;

3550 END LOOP;

3551 RETURN;

3552 END;

3553 $$;

3554

3555 COMMENT ON FUNCTION "create_snapshot"

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

3557 IS 'This function creates a complete new ''periodic'' snapshot of population, interest and support for the given issue. All involved tables are locked, and after completion precalculated values in the source tables are updated.';

3558

3559

3560 CREATE FUNCTION "set_snapshot_event"

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

3562 "event_p" "snapshot_event" )

3563 RETURNS VOID

3564 LANGUAGE 'plpgsql' VOLATILE AS $$

3565 DECLARE

3566 "event_v" "issue"."latest_snapshot_event"%TYPE;

3567 BEGIN

3568 PERFORM "require_transaction_isolation"();

3569 SELECT "latest_snapshot_event" INTO "event_v" FROM "issue"

3570 WHERE "id" = "issue_id_p" FOR UPDATE;

3571 UPDATE "issue" SET "latest_snapshot_event" = "event_p"

3572 WHERE "id" = "issue_id_p";

3573 UPDATE "direct_population_snapshot" SET "event" = "event_p"

3574 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";

3575 UPDATE "delegating_population_snapshot" SET "event" = "event_p"

3576 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";

3577 UPDATE "direct_interest_snapshot" SET "event" = "event_p"

3578 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";

3579 UPDATE "delegating_interest_snapshot" SET "event" = "event_p"

3580 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";

3581 UPDATE "direct_supporter_snapshot" SET "event" = "event_p"

3582 FROM "initiative" -- NOTE: due to missing index on issue_id

3583 WHERE "initiative"."issue_id" = "issue_id_p"

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

3585 AND "direct_supporter_snapshot"."event" = "event_v";

3586 RETURN;

3587 END;

3588 $$;

3589

3590 COMMENT ON FUNCTION "set_snapshot_event"

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

3592 "snapshot_event" )

3593 IS 'Change "event" attribute of the previous ''periodic'' snapshot';

3594

3595

3596

3597 -----------------------

3598 -- Counting of votes --

3599 -----------------------

3600

3601

3602 CREATE FUNCTION "weight_of_added_vote_delegations"

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

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

3605 "delegate_member_ids_p" "delegating_voter"."delegate_member_ids"%TYPE )

3606 RETURNS "direct_voter"."weight"%TYPE

3607 LANGUAGE 'plpgsql' VOLATILE AS $$

3608 DECLARE

3609 "issue_delegation_row" "issue_delegation"%ROWTYPE;

3610 "delegate_member_ids_v" "delegating_voter"."delegate_member_ids"%TYPE;

3611 "weight_v" INT4;

3612 "sub_weight_v" INT4;

3613 BEGIN

3614 PERFORM "require_transaction_isolation"();

3615 "weight_v" := 0;

3616 FOR "issue_delegation_row" IN

3617 SELECT * FROM "issue_delegation"

3618 WHERE "trustee_id" = "member_id_p"

3619 AND "issue_id" = "issue_id_p"

3620 LOOP

3621 IF NOT EXISTS (

3622 SELECT NULL FROM "direct_voter"

3623 WHERE "member_id" = "issue_delegation_row"."truster_id"

3624 AND "issue_id" = "issue_id_p"

3625 ) AND NOT EXISTS (

3626 SELECT NULL FROM "delegating_voter"

3627 WHERE "member_id" = "issue_delegation_row"."truster_id"

3628 AND "issue_id" = "issue_id_p"

3629 ) THEN

3630 "delegate_member_ids_v" :=

3631 "member_id_p" || "delegate_member_ids_p";

3632 INSERT INTO "delegating_voter" (

3633 "issue_id",

3634 "member_id",

3635 "scope",

3636 "delegate_member_ids"

3637 ) VALUES (

3638 "issue_id_p",

3639 "issue_delegation_row"."truster_id",

3640 "issue_delegation_row"."scope",

3641 "delegate_member_ids_v"

3642 );

3643 "sub_weight_v" := 1 +

3644 "weight_of_added_vote_delegations"(

3645 "issue_id_p",

3646 "issue_delegation_row"."truster_id",

3647 "delegate_member_ids_v"

3648 );

3649 UPDATE "delegating_voter"

3650 SET "weight" = "sub_weight_v"

3651 WHERE "issue_id" = "issue_id_p"

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

3653 "weight_v" := "weight_v" + "sub_weight_v";

3654 END IF;

3655 END LOOP;

3656 RETURN "weight_v";

3657 END;

3658 $$;

3659

3660 COMMENT ON FUNCTION "weight_of_added_vote_delegations"

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

3662 "member"."id"%TYPE,

3663 "delegating_voter"."delegate_member_ids"%TYPE )

3664 IS 'Helper function for "add_vote_delegations" function';

3665

3666

3667 CREATE FUNCTION "add_vote_delegations"

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

3669 RETURNS VOID

3670 LANGUAGE 'plpgsql' VOLATILE AS $$

3671 DECLARE

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

3673 BEGIN

3674 PERFORM "require_transaction_isolation"();

3675 FOR "member_id_v" IN

3676 SELECT "member_id" FROM "direct_voter"

3677 WHERE "issue_id" = "issue_id_p"

3678 LOOP

3679 UPDATE "direct_voter" SET

3680 "weight" = "weight" + "weight_of_added_vote_delegations"(

3681 "issue_id_p",

3682 "member_id_v",

3683 '{}'

3684 )

3685 WHERE "member_id" = "member_id_v"

3686 AND "issue_id" = "issue_id_p";

3687 END LOOP;

3688 RETURN;

3689 END;

3690 $$;

3691

3692 COMMENT ON FUNCTION "add_vote_delegations"

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

3694 IS 'Helper function for "close_voting" function';

3695

3696

3697 CREATE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)

3698 RETURNS VOID

3699 LANGUAGE 'plpgsql' VOLATILE AS $$

3700 DECLARE

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

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

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

3704 BEGIN

3705 PERFORM "require_transaction_isolation"();

3706 SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";

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

3708 -- override protection triggers:

3709 INSERT INTO "temporary_transaction_data" ("key", "value")

3710 VALUES ('override_protection_triggers', TRUE::TEXT);

3711 -- delete timestamp of voting comment:

3712 UPDATE "direct_voter" SET "comment_changed" = NULL

3713 WHERE "issue_id" = "issue_id_p";

3714 -- delete delegating votes (in cases of manual reset of issue state):

3715 DELETE FROM "delegating_voter"

3716 WHERE "issue_id" = "issue_id_p";

3717 -- delete votes from non-privileged voters:

3718 DELETE FROM "direct_voter"

3719 USING (

3720 SELECT

3721 "direct_voter"."member_id"

3722 FROM "direct_voter"

3723 JOIN "member" ON "direct_voter"."member_id" = "member"."id"

3724 LEFT JOIN "privilege"

3725 ON "privilege"."unit_id" = "unit_id_v"

3726 AND "privilege"."member_id" = "direct_voter"."member_id"

3727 WHERE "direct_voter"."issue_id" = "issue_id_p" AND (

3728 "member"."active" = FALSE OR

3729 "privilege"."voting_right" ISNULL OR

3730 "privilege"."voting_right" = FALSE

3731 )

3732 ) AS "subquery"

3733 WHERE "direct_voter"."issue_id" = "issue_id_p"

3734 AND "direct_voter"."member_id" = "subquery"."member_id";

3735 -- consider delegations:

3736 UPDATE "direct_voter" SET "weight" = 1

3737 WHERE "issue_id" = "issue_id_p";

3738 PERFORM "add_vote_delegations"("issue_id_p");

3739 -- finish overriding protection triggers (avoids garbage):

3740 DELETE FROM "temporary_transaction_data"

3741 WHERE "key" = 'override_protection_triggers';

3742 -- materialize battle_view:

3743 -- NOTE: "closed" column of issue must be set at this point

3744 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";

3745 INSERT INTO "battle" (

3746 "issue_id",

3747 "winning_initiative_id", "losing_initiative_id",

3748 "count"

3749 ) SELECT

3750 "issue_id",

3751 "winning_initiative_id", "losing_initiative_id",

3752 "count"

3753 FROM "battle_view" WHERE "issue_id" = "issue_id_p";

3754 -- set voter count:

3755 UPDATE "issue" SET

3756 "voter_count" = (

3757 SELECT coalesce(sum("weight"), 0)

3758 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"

3759 )

3760 WHERE "id" = "issue_id_p";

3761 -- copy "positive_votes" and "negative_votes" from "battle" table:

3762 UPDATE "initiative" SET

3763 "positive_votes" = "battle_win"."count",

3764 "negative_votes" = "battle_lose"."count"

3765 FROM "battle" AS "battle_win", "battle" AS "battle_lose"

3766 WHERE

3767 "battle_win"."issue_id" = "issue_id_p" AND

3768 "battle_win"."winning_initiative_id" = "initiative"."id" AND

3769 "battle_win"."losing_initiative_id" ISNULL AND

3770 "battle_lose"."issue_id" = "issue_id_p" AND

3771 "battle_lose"."losing_initiative_id" = "initiative"."id" AND

3772 "battle_lose"."winning_initiative_id" ISNULL;

3773 END;

3774 $$;

3775

3776 COMMENT ON FUNCTION "close_voting"

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

3778 IS 'Closes the voting on an issue, and calculates positive and negative votes for each initiative; The ranking is not calculated yet, to keep the (locking) transaction short.';

3779

3780

3781 CREATE FUNCTION "defeat_strength"

3782 ( "positive_votes_p" INT4, "negative_votes_p" INT4 )

3783 RETURNS INT8

3784 LANGUAGE 'plpgsql' IMMUTABLE AS $$

3785 BEGIN

3786 IF "positive_votes_p" > "negative_votes_p" THEN

3787 RETURN ("positive_votes_p"::INT8 << 31) - "negative_votes_p"::INT8;

3788 ELSIF "positive_votes_p" = "negative_votes_p" THEN

3789 RETURN 0;

3790 ELSE

3791 RETURN -1;

3792 END IF;

3793 END;

3794 $$;

3795

3796 COMMENT ON FUNCTION "defeat_strength"(INT4, INT4) IS 'Calculates defeat strength (INT8!) of a pairwise defeat primarily by the absolute number of votes for the winner and secondarily by the absolute number of votes for the loser';

3797

3798

3799 CREATE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE)

3800 RETURNS VOID

3801 LANGUAGE 'plpgsql' VOLATILE AS $$

3802 DECLARE

3803 "issue_row" "issue"%ROWTYPE;

3804 "policy_row" "policy"%ROWTYPE;

3805 "dimension_v" INTEGER;

3806 "vote_matrix" INT4[][]; -- absolute votes

3807 "matrix" INT8[][]; -- defeat strength / best paths

3808 "i" INTEGER;

3809 "j" INTEGER;

3810 "k" INTEGER;

3811 "battle_row" "battle"%ROWTYPE;

3812 "rank_ary" INT4[];

3813 "rank_v" INT4;

3814 "initiative_id_v" "initiative"."id"%TYPE;

3815 BEGIN

3816 PERFORM "require_transaction_isolation"();

3817 SELECT * INTO "issue_row"

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

3819 SELECT * INTO "policy_row"

3820 FROM "policy" WHERE "id" = "issue_row"."policy_id";

3821 SELECT count(1) INTO "dimension_v"

3822 FROM "battle_participant" WHERE "issue_id" = "issue_id_p";

3823 -- Create "vote_matrix" with absolute number of votes in pairwise

3824 -- comparison:

3825 "vote_matrix" := array_fill(NULL::INT4, ARRAY["dimension_v", "dimension_v"]);

3826 "i" := 1;

3827 "j" := 2;

3828 FOR "battle_row" IN

3829 SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p"

3830 ORDER BY

3831 "winning_initiative_id" NULLS FIRST,

3832 "losing_initiative_id" NULLS FIRST

3833 LOOP

3834 "vote_matrix"["i"]["j"] := "battle_row"."count";

3835 IF "j" = "dimension_v" THEN

3836 "i" := "i" + 1;

3837 "j" := 1;

3838 ELSE

3839 "j" := "j" + 1;

3840 IF "j" = "i" THEN

3841 "j" := "j" + 1;

3842 END IF;

3843 END IF;

3844 END LOOP;

3845 IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN

3846 RAISE EXCEPTION 'Wrong battle count (should not happen)';

3847 END IF;

3848 -- Store defeat strengths in "matrix" using "defeat_strength"

3849 -- function:

3850 "matrix" := array_fill(NULL::INT8, ARRAY["dimension_v", "dimension_v"]);

3851 "i" := 1;

3852 LOOP

3853 "j" := 1;

3854 LOOP

3855 IF "i" != "j" THEN

3856 "matrix"["i"]["j"] := "defeat_strength"(

3857 "vote_matrix"["i"]["j"],

3858 "vote_matrix"["j"]["i"]

3859 );

3860 END IF;

3861 EXIT WHEN "j" = "dimension_v";

3862 "j" := "j" + 1;

3863 END LOOP;

3864 EXIT WHEN "i" = "dimension_v";

3865 "i" := "i" + 1;

3866 END LOOP;

3867 -- Find best paths:

3868 "i" := 1;

3869 LOOP

3870 "j" := 1;

3871 LOOP

3872 IF "i" != "j" THEN

3873 "k" := 1;

3874 LOOP

3875 IF "i" != "k" AND "j" != "k" THEN

3876 IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN

3877 IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN

3878 "matrix"["j"]["k"] := "matrix"["j"]["i"];

3879 END IF;

3880 ELSE

3881 IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN

3882 "matrix"["j"]["k"] := "matrix"["i"]["k"];

3883 END IF;

3884 END IF;

3885 END IF;

3886 EXIT WHEN "k" = "dimension_v";

3887 "k" := "k" + 1;

3888 END LOOP;

3889 END IF;

3890 EXIT WHEN "j" = "dimension_v";

3891 "j" := "j" + 1;

3892 END LOOP;

3893 EXIT WHEN "i" = "dimension_v";

3894 "i" := "i" + 1;

3895 END LOOP;

3896 -- Determine order of winners:

3897 "rank_ary" := array_fill(NULL::INT4, ARRAY["dimension_v"]);

3898 "rank_v" := 1;

3899 LOOP

3900 "i" := 1;

3901 LOOP

3902 IF "rank_ary"["i"] ISNULL THEN

3903 "j" := 1;

3904 LOOP

3905 IF

3906 "i" != "j" AND

3907 "rank_ary"["j"] ISNULL AND

3908 ( "matrix"["j"]["i"] > "matrix"["i"]["j"] OR

3909 -- tie-breaking by "id"

3910 ( "matrix"["j"]["i"] = "matrix"["i"]["j"] AND

3911 "j" < "i" ) )

3912 THEN

3913 -- someone else is better

3914 EXIT;

3915 END IF;

3916 "j" := "j" + 1;

3917 IF "j" = "dimension_v" + 1 THEN

3918 -- noone is better

3919 "rank_ary"["i"] := "rank_v";

3920 EXIT;

3921 END IF;

3922 END LOOP;

3923 EXIT WHEN "j" = "dimension_v" + 1;

3924 END IF;

3925 "i" := "i" + 1;

3926 IF "i" > "dimension_v" THEN

3927 RAISE EXCEPTION 'Schulze ranking does not compute (should not happen)';

3928 END IF;

3929 END LOOP;

3930 EXIT WHEN "rank_v" = "dimension_v";

3931 "rank_v" := "rank_v" + 1;

3932 END LOOP;

3933 -- write preliminary results:

3934 "i" := 2; -- omit status quo with "i" = 1

3935 FOR "initiative_id_v" IN

3936 SELECT "id" FROM "initiative"

3937 WHERE "issue_id" = "issue_id_p" AND "admitted"

3938 ORDER BY "id"

3939 LOOP

3940 UPDATE "initiative" SET

3941 "direct_majority" =

3942 CASE WHEN "policy_row"."direct_majority_strict" THEN

3943 "positive_votes" * "policy_row"."direct_majority_den" >

3944 "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")

3945 ELSE

3946 "positive_votes" * "policy_row"."direct_majority_den" >=

3947 "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")

3948 END

3949 AND "positive_votes" >= "policy_row"."direct_majority_positive"

3950 AND "issue_row"."voter_count"-"negative_votes" >=

3951 "policy_row"."direct_majority_non_negative",

3952 "indirect_majority" =

3953 CASE WHEN "policy_row"."indirect_majority_strict" THEN

3954 "positive_votes" * "policy_row"."indirect_majority_den" >

3955 "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")

3956 ELSE

3957 "positive_votes" * "policy_row"."indirect_majority_den" >=

3958 "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")

3959 END

3960 AND "positive_votes" >= "policy_row"."indirect_majority_positive"

3961 AND "issue_row"."voter_count"-"negative_votes" >=

3962 "policy_row"."indirect_majority_non_negative",

3963 "schulze_rank" = "rank_ary"["i"],

3964 "better_than_status_quo" = "rank_ary"["i"] < "rank_ary"[1],

3965 "worse_than_status_quo" = "rank_ary"["i"] > "rank_ary"[1],

3966 "multistage_majority" = "rank_ary"["i"] >= "rank_ary"[1],

3967 "reverse_beat_path" = "matrix"[1]["i"] >= 0,

3968 "eligible" = FALSE,

3969 "winner" = FALSE,

3970 "rank" = NULL -- NOTE: in cases of manual reset of issue state

3971 WHERE "id" = "initiative_id_v";

3972 "i" := "i" + 1;

3973 END LOOP;

3974 IF "i" != "dimension_v" + 1 THEN

3975 RAISE EXCEPTION 'Wrong winner count (should not happen)';

3976 END IF;

3977 -- take indirect majorities into account:

3978 LOOP

3979 UPDATE "initiative" SET "indirect_majority" = TRUE

3980 FROM (

3981 SELECT "new_initiative"."id" AS "initiative_id"

3982 FROM "initiative" "old_initiative"

3983 JOIN "initiative" "new_initiative"

3984 ON "new_initiative"."issue_id" = "issue_id_p"

3985 AND "new_initiative"."indirect_majority" = FALSE

3986 JOIN "battle" "battle_win"

3987 ON "battle_win"."issue_id" = "issue_id_p"

3988 AND "battle_win"."winning_initiative_id" = "new_initiative"."id"

3989 AND "battle_win"."losing_initiative_id" = "old_initiative"."id"

3990 JOIN "battle" "battle_lose"

3991 ON "battle_lose"."issue_id" = "issue_id_p"

3992 AND "battle_lose"."losing_initiative_id" = "new_initiative"."id"

3993 AND "battle_lose"."winning_initiative_id" = "old_initiative"."id"

3994 WHERE "old_initiative"."issue_id" = "issue_id_p"

3995 AND "old_initiative"."indirect_majority" = TRUE

3996 AND CASE WHEN "policy_row"."indirect_majority_strict" THEN

3997 "battle_win"."count" * "policy_row"."indirect_majority_den" >

3998 "policy_row"."indirect_majority_num" *

3999 ("battle_win"."count"+"battle_lose"."count")

4000 ELSE

4001 "battle_win"."count" * "policy_row"."indirect_majority_den" >=

4002 "policy_row"."indirect_majority_num" *

4003 ("battle_win"."count"+"battle_lose"."count")

4004 END

4005 AND "battle_win"."count" >= "policy_row"."indirect_majority_positive"

4006 AND "issue_row"."voter_count"-"battle_lose"."count" >=

4007 "policy_row"."indirect_majority_non_negative"

4008 ) AS "subquery"

4009 WHERE "id" = "subquery"."initiative_id";

4010 EXIT WHEN NOT FOUND;

4011 END LOOP;

4012 -- set "multistage_majority" for remaining matching initiatives:

4013 UPDATE "initiative" SET "multistage_majority" = TRUE

4014 FROM (

4015 SELECT "losing_initiative"."id" AS "initiative_id"

4016 FROM "initiative" "losing_initiative"

4017 JOIN "initiative" "winning_initiative"

4018 ON "winning_initiative"."issue_id" = "issue_id_p"

4019 AND "winning_initiative"."admitted"

4020 JOIN "battle" "battle_win"

4021 ON "battle_win"."issue_id" = "issue_id_p"

4022 AND "battle_win"."winning_initiative_id" = "winning_initiative"."id"

4023 AND "battle_win"."losing_initiative_id" = "losing_initiative"."id"

4024 JOIN "battle" "battle_lose"

4025 ON "battle_lose"."issue_id" = "issue_id_p"

4026 AND "battle_lose"."losing_initiative_id" = "winning_initiative"."id"

4027 AND "battle_lose"."winning_initiative_id" = "losing_initiative"."id"

4028 WHERE "losing_initiative"."issue_id" = "issue_id_p"

4029 AND "losing_initiative"."admitted"

4030 AND "winning_initiative"."schulze_rank" <

4031 "losing_initiative"."schulze_rank"

4032 AND "battle_win"."count" > "battle_lose"."count"

4033 AND (

4034 "battle_win"."count" > "winning_initiative"."positive_votes" OR

4035 "battle_lose"."count" < "losing_initiative"."negative_votes" )

4036 ) AS "subquery"

4037 WHERE "id" = "subquery"."initiative_id";

4038 -- mark eligible initiatives:

4039 UPDATE "initiative" SET "eligible" = TRUE

4040 WHERE "issue_id" = "issue_id_p"

4041 AND "initiative"."direct_majority"

4042 AND "initiative"."indirect_majority"

4043 AND "initiative"."better_than_status_quo"

4044 AND (

4045 "policy_row"."no_multistage_majority" = FALSE OR

4046 "initiative"."multistage_majority" = FALSE )

4047 AND (

4048 "policy_row"."no_reverse_beat_path" = FALSE OR

4049 "initiative"."reverse_beat_path" = FALSE );

4050 -- mark final winner:

4051 UPDATE "initiative" SET "winner" = TRUE

4052 FROM (

4053 SELECT "id" AS "initiative_id"

4054 FROM "initiative"

4055 WHERE "issue_id" = "issue_id_p" AND "eligible"

4056 ORDER BY

4057 "schulze_rank",

4058 "id"

4059 LIMIT 1

4060 ) AS "subquery"

4061 WHERE "id" = "subquery"."initiative_id";

4062 -- write (final) ranks:

4063 "rank_v" := 1;

4064 FOR "initiative_id_v" IN

4065 SELECT "id"

4066 FROM "initiative"

4067 WHERE "issue_id" = "issue_id_p" AND "admitted"

4068 ORDER BY

4069 "winner" DESC,

4070 "eligible" DESC,

4071 "schulze_rank",

4072 "id"

4073 LOOP

4074 UPDATE "initiative" SET "rank" = "rank_v"

4075 WHERE "id" = "initiative_id_v";

4076 "rank_v" := "rank_v" + 1;

4077 END LOOP;

4078 -- set schulze rank of status quo and mark issue as finished:

4079 UPDATE "issue" SET

4080 "status_quo_schulze_rank" = "rank_ary"[1],

4081 "state" =

4082 CASE WHEN EXISTS (

4083 SELECT NULL FROM "initiative"

4084 WHERE "issue_id" = "issue_id_p" AND "winner"

4085 ) THEN

4086 'finished_with_winner'::"issue_state"

4087 ELSE

4088 'finished_without_winner'::"issue_state"

4089 END,

4090 "closed" = "phase_finished",

4091 "phase_finished" = NULL

4092 WHERE "id" = "issue_id_p";

4093 RETURN;

4094 END;

4095 $$;

4096

4097 COMMENT ON FUNCTION "calculate_ranks"

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

4099 IS 'Determine ranking (Votes have to be counted first)';

4100

4101

4102

4103 -----------------------------

4104 -- Automatic state changes --

4105 -----------------------------

4106

4107

4108 CREATE TYPE "check_issue_persistence" AS (

4109 "state" "issue_state",

4110 "phase_finished" BOOLEAN,

4111 "issue_revoked" BOOLEAN,

4112 "snapshot_created" BOOLEAN,

4113 "harmonic_weights_set" BOOLEAN,

4114 "closed_voting" BOOLEAN );

4115

4116 COMMENT ON TYPE "check_issue_persistence" IS 'Type of data returned by "check_issue" function, to be passed to subsequent calls of the same function';

4117

4118

4119 CREATE FUNCTION "check_issue"

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

4121 "persist" "check_issue_persistence" )

4122 RETURNS "check_issue_persistence"

4123 LANGUAGE 'plpgsql' VOLATILE AS $$

4124 DECLARE

4125 "issue_row" "issue"%ROWTYPE;

4126 "policy_row" "policy"%ROWTYPE;

4127 "initiative_row" "initiative"%ROWTYPE;

4128 "state_v" "issue_state";

4129 BEGIN

4130 PERFORM "require_transaction_isolation"();

4131 IF "persist" ISNULL THEN

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

4133 FOR UPDATE;

4134 IF "issue_row"."closed" NOTNULL THEN

4135 RETURN NULL;

4136 END IF;

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

4138 IF

4139 ( "issue_row"."state" = 'admission' AND now() >=

4140 "issue_row"."created" + "issue_row"."admission_time" ) OR

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

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

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

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

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

4146 "issue_row"."fully_frozen" + "issue_row"."voting_time" )

4147 THEN

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

4149 ELSE

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

4151 END IF;

4152 IF

4153 NOT EXISTS (

4154 -- all initiatives are revoked

4155 SELECT NULL FROM "initiative"

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

4157 ) AND (

4158 -- and issue has not been accepted yet

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

4160 -- or verification time has elapsed

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

4162 "persist"."phase_finished" ) OR

4163 -- or no initiatives have been revoked lately

4164 NOT EXISTS (

4165 SELECT NULL FROM "initiative"

4166 WHERE "issue_id" = "issue_id_p"

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

4168 )

4169 )

4170 THEN

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

4172 ELSE

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

4174 END IF;

4175 IF "persist"."phase_finished" OR "persist"."issue_revoked" THEN

4176 UPDATE "issue" SET "phase_finished" = now()

4177 WHERE "id" = "issue_row"."id";

4178 RETURN "persist";

4179 ELSIF

4180 "persist"."state" IN ('admission', 'discussion', 'verification')

4181 THEN

4182 RETURN "persist";

4183 ELSE

4184 RETURN NULL;

4185 END IF;

4186 END IF;

4187 IF

4188 "persist"."state" IN ('admission', 'discussion', 'verification') AND

4189 coalesce("persist"."snapshot_created", FALSE) = FALSE

4190 THEN

4191 PERFORM "create_snapshot"("issue_id_p");

4192 "persist"."snapshot_created" = TRUE;

4193 IF "persist"."phase_finished" THEN

4194 IF "persist"."state" = 'admission' THEN

4195 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');

4196 ELSIF "persist"."state" = 'discussion' THEN

4197 PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze');

4198 ELSIF "persist"."state" = 'verification' THEN

4199 PERFORM "set_snapshot_event"("issue_id_p", 'full_freeze');

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

4201 SELECT * INTO "policy_row" FROM "policy"

4202 WHERE "id" = "issue_row"."policy_id";

4203 FOR "initiative_row" IN

4204 SELECT * FROM "initiative"

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

4206 FOR UPDATE

4207 LOOP

4208 IF

4209 "initiative_row"."polling" OR (

4210 "initiative_row"."satisfied_supporter_count" > 0 AND

4211 "initiative_row"."satisfied_supporter_count" *

4212 "policy_row"."initiative_quorum_den" >=

4213 "issue_row"."population" * "policy_row"."initiative_quorum_num"

4214 )

4215 THEN

4216 UPDATE "initiative" SET "admitted" = TRUE

4217 WHERE "id" = "initiative_row"."id";

4218 ELSE

4219 UPDATE "initiative" SET "admitted" = FALSE

4220 WHERE "id" = "initiative_row"."id";

4221 END IF;

4222 END LOOP;

4223 END IF;

4224 END IF;

4225 RETURN "persist";

4226 END IF;

4227 IF

4228 "persist"."state" IN ('admission', 'discussion', 'verification') AND

4229 coalesce("persist"."harmonic_weights_set", FALSE) = FALSE

4230 THEN

4231 PERFORM "set_harmonic_initiative_weights"("issue_id_p");

4232 "persist"."harmonic_weights_set" = TRUE;

4233 IF

4234 "persist"."phase_finished" OR

4235 "persist"."issue_revoked" OR

4236 "persist"."state" = 'admission'

4237 THEN

4238 RETURN "persist";

4239 ELSE

4240 RETURN NULL;

4241 END IF;

4242 END IF;

4243 IF "persist"."issue_revoked" THEN

4244 IF "persist"."state" = 'admission' THEN

4245 "state_v" := 'canceled_revoked_before_accepted';

4246 ELSIF "persist"."state" = 'discussion' THEN

4247 "state_v" := 'canceled_after_revocation_during_discussion';

4248 ELSIF "persist"."state" = 'verification' THEN

4249 "state_v" := 'canceled_after_revocation_during_verification';

4250 END IF;

4251 UPDATE "issue" SET

4252 "state" = "state_v",

4253 "closed" = "phase_finished",

4254 "phase_finished" = NULL

4255 WHERE "id" = "issue_id_p";

4256 RETURN NULL;

4257 END IF;

4258 IF "persist"."state" = 'admission' THEN

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

4260 FOR UPDATE;

4261 SELECT * INTO "policy_row"

4262 FROM "policy" WHERE "id" = "issue_row"."policy_id";

4263 IF EXISTS (

4264 SELECT NULL FROM "initiative"

4265 WHERE "issue_id" = "issue_id_p"

4266 AND "supporter_count" > 0

4267 AND "supporter_count" * "policy_row"."issue_quorum_den"

4268 >= "issue_row"."population" * "policy_row"."issue_quorum_num"

4269 ) THEN

4270 UPDATE "issue" SET

4271 "state" = 'discussion',

4272 "accepted" = coalesce("phase_finished", now()),

4273 "phase_finished" = NULL

4274 WHERE "id" = "issue_id_p";

4275 ELSIF "issue_row"."phase_finished" NOTNULL THEN

4276 UPDATE "issue" SET

4277 "state" = 'canceled_issue_not_accepted',

4278 "closed" = "phase_finished",

4279 "phase_finished" = NULL

4280 WHERE "id" = "issue_id_p";

4281 END IF;

4282 RETURN NULL;

4283 END IF;

4284 IF "persist"."phase_finished" THEN

4285 if "persist"."state" = 'discussion' THEN

4286 UPDATE "issue" SET

4287 "state" = 'verification',

4288 "half_frozen" = "phase_finished",

4289 "phase_finished" = NULL

4290 WHERE "id" = "issue_id_p";

4291 RETURN NULL;

4292 END IF;

4293 IF "persist"."state" = 'verification' THEN

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

4295 FOR UPDATE;

4296 SELECT * INTO "policy_row" FROM "policy"

4297 WHERE "id" = "issue_row"."policy_id";

4298 IF EXISTS (

4299 SELECT NULL FROM "initiative"

4300 WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE

4301 ) THEN

4302 UPDATE "issue" SET

4303 "state" = 'voting',

4304 "fully_frozen" = "phase_finished",

4305 "phase_finished" = NULL

4306 WHERE "id" = "issue_id_p";

4307 ELSE

4308 UPDATE "issue" SET

4309 "state" = 'canceled_no_initiative_admitted',

4310 "fully_frozen" = "phase_finished",

4311 "closed" = "phase_finished",

4312 "phase_finished" = NULL

4313 WHERE "id" = "issue_id_p";

4314 -- NOTE: The following DELETE statements have effect only when

4315 -- issue state has been manipulated

4316 DELETE FROM "direct_voter" WHERE "issue_id" = "issue_id_p";

4317 DELETE FROM "delegating_voter" WHERE "issue_id" = "issue_id_p";

4318 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";

4319 END IF;

4320 RETURN NULL;

4321 END IF;

4322 IF "persist"."state" = 'voting' THEN

4323 IF coalesce("persist"."closed_voting", FALSE) = FALSE THEN

4324 PERFORM "close_voting"("issue_id_p");

4325 "persist"."closed_voting" = TRUE;

4326 RETURN "persist";

4327 END IF;

4328 PERFORM "calculate_ranks"("issue_id_p");

4329 RETURN NULL;

4330 END IF;

4331 END IF;

4332 RAISE WARNING 'should not happen';

4333 RETURN NULL;

4334 END;

4335 $$;

4336

4337 COMMENT ON FUNCTION "check_issue"

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

4339 "check_issue_persistence" )

4340 IS 'Precalculate supporter counts etc. for a given issue, and check, if status change is required, and perform the status change when necessary; Function must be called multiple times with the previous result as second parameter, until the result is NULL (see source code of function "check_everything")';

4341

4342

4343 CREATE FUNCTION "check_everything"()

4344 RETURNS VOID

4345 LANGUAGE 'plpgsql' VOLATILE AS $$

4346 DECLARE

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

4348 "persist_v" "check_issue_persistence";

4349 BEGIN

4350 RAISE WARNING 'Function "check_everything" should only be used for development and debugging purposes';

4351 DELETE FROM "expired_session";

4352 PERFORM "check_activity"();

4353 PERFORM "calculate_member_counts"();

4354 FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP

4355 "persist_v" := NULL;

4356 LOOP

4357 "persist_v" := "check_issue"("issue_id_v", "persist_v");

4358 EXIT WHEN "persist_v" ISNULL;

4359 END LOOP;

4360 END LOOP;

4361 RETURN;

4362 END;

4363 $$;

4364

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

4366

4367

4368

4369 ----------------------

4370 -- Deletion of data --

4371 ----------------------

4372

4373

4374 CREATE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE)

4375 RETURNS VOID

4376 LANGUAGE 'plpgsql' VOLATILE AS $$

4377 BEGIN

4378 IF EXISTS (

4379 SELECT NULL FROM "issue" WHERE "id" = "issue_id_p" AND "cleaned" ISNULL

4380 ) THEN

4381 -- override protection triggers:

4382 INSERT INTO "temporary_transaction_data" ("key", "value")

4383 VALUES ('override_protection_triggers', TRUE::TEXT);

4384 -- clean data:

4385 DELETE FROM "delegating_voter"

4386 WHERE "issue_id" = "issue_id_p";

4387 DELETE FROM "direct_voter"

4388 WHERE "issue_id" = "issue_id_p";

4389 DELETE FROM "delegating_interest_snapshot"

4390 WHERE "issue_id" = "issue_id_p";

4391 DELETE FROM "direct_interest_snapshot"

4392 WHERE "issue_id" = "issue_id_p";

4393 DELETE FROM "delegating_population_snapshot"

4394 WHERE "issue_id" = "issue_id_p";

4395 DELETE FROM "direct_population_snapshot"

4396 WHERE "issue_id" = "issue_id_p";

4397 DELETE FROM "non_voter"

4398 WHERE "issue_id" = "issue_id_p";

4399 DELETE FROM "delegation"

4400 WHERE "issue_id" = "issue_id_p";

4401 DELETE FROM "supporter"

4402 USING "initiative" -- NOTE: due to missing index on issue_id

4403 WHERE "initiative"."issue_id" = "issue_id_p"

4404 AND "supporter"."initiative_id" = "initiative_id";

4405 -- mark issue as cleaned:

4406 UPDATE "issue" SET "cleaned" = now() WHERE "id" = "issue_id_p";

4407 -- finish overriding protection triggers (avoids garbage):

4408 DELETE FROM "temporary_transaction_data"

4409 WHERE "key" = 'override_protection_triggers';

4410 END IF;

4411 RETURN;

4412 END;

4413 $$;

4414

4415 COMMENT ON FUNCTION "clean_issue"("issue"."id"%TYPE) IS 'Delete discussion data and votes belonging to an issue';

4416

4417

4418 CREATE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)

4419 RETURNS VOID

4420 LANGUAGE 'plpgsql' VOLATILE AS $$

4421 BEGIN

4422 UPDATE "member" SET

4423 "last_login" = NULL,

4424 "last_delegation_check" = NULL,

4425 "login" = NULL,

4426 "password" = NULL,

4427 "locked" = TRUE,

4428 "active" = FALSE,

4429 "notify_email" = NULL,

4430 "notify_email_unconfirmed" = NULL,

4431 "notify_email_secret" = NULL,

4432 "notify_email_secret_expiry" = NULL,

4433 "notify_email_lock_expiry" = NULL,

4434 "login_recovery_expiry" = NULL,

4435 "password_reset_secret" = NULL,

4436 "password_reset_secret_expiry" = NULL,

4437 "organizational_unit" = NULL,

4438 "internal_posts" = NULL,

4439 "realname" = NULL,

4440 "birthday" = NULL,

4441 "address" = NULL,

4442 "email" = NULL,

4443 "xmpp_address" = NULL,

4444 "website" = NULL,

4445 "phone" = NULL,

4446 "mobile_phone" = NULL,

4447 "profession" = NULL,

4448 "external_memberships" = NULL,

4449 "external_posts" = NULL,

4450 "statement" = NULL

4451 WHERE "id" = "member_id_p";

4452 -- "text_search_data" is updated by triggers

4453 DELETE FROM "setting" WHERE "member_id" = "member_id_p";

4454 DELETE FROM "setting_map" WHERE "member_id" = "member_id_p";

4455 DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p";

4456 DELETE FROM "member_image" WHERE "member_id" = "member_id_p";

4457 DELETE FROM "contact" WHERE "member_id" = "member_id_p";

4458 DELETE FROM "ignored_member" WHERE "member_id" = "member_id_p";

4459 DELETE FROM "session" WHERE "member_id" = "member_id_p";

4460 DELETE FROM "area_setting" WHERE "member_id" = "member_id_p";

4461 DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p";

4462 DELETE FROM "ignored_initiative" WHERE "member_id" = "member_id_p";

4463 DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p";

4464 DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p";

4465 DELETE FROM "membership" WHERE "member_id" = "member_id_p";

4466 DELETE FROM "delegation" WHERE "truster_id" = "member_id_p";

4467 DELETE FROM "non_voter" WHERE "member_id" = "member_id_p";

4468 DELETE FROM "direct_voter" USING "issue"

4469 WHERE "direct_voter"."issue_id" = "issue"."id"

4470 AND "issue"."closed" ISNULL

4471 AND "member_id" = "member_id_p";

4472 RETURN;

4473 END;

4474 $$;

4475

4476 COMMENT ON FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE) IS 'Deactivate member and clear certain settings and data of this member (data protection)';

4477

4478

4479 CREATE FUNCTION "delete_private_data"()

4480 RETURNS VOID

4481 LANGUAGE 'plpgsql' VOLATILE AS $$

4482 BEGIN

4483 DELETE FROM "temporary_transaction_data";

4484 DELETE FROM "member" WHERE "activated" ISNULL;

4485 UPDATE "member" SET

4486 "invite_code" = NULL,

4487 "invite_code_expiry" = NULL,

4488 "admin_comment" = NULL,

4489 "last_login" = NULL,

4490 "last_delegation_check" = NULL,

4491 "login" = NULL,

4492 "password" = NULL,

4493 "lang" = NULL,

4494 "notify_email" = NULL,

4495 "notify_email_unconfirmed" = NULL,

4496 "notify_email_secret" = NULL,

4497 "notify_email_secret_expiry" = NULL,

4498 "notify_email_lock_expiry" = NULL,

4499 "notify_level" = NULL,

4500 "login_recovery_expiry" = NULL,

4501 "password_reset_secret" = NULL,

4502 "password_reset_secret_expiry" = NULL,

4503 "organizational_unit" = NULL,

4504 "internal_posts" = NULL,

4505 "realname" = NULL,

4506 "birthday" = NULL,

4507 "address" = NULL,

4508 "email" = NULL,

4509 "xmpp_address" = NULL,

4510 "website" = NULL,

4511 "phone" = NULL,

4512 "mobile_phone" = NULL,

4513 "profession" = NULL,

4514 "external_memberships" = NULL,

4515 "external_posts" = NULL,

4516 "formatting_engine" = NULL,

4517 "statement" = NULL;

4518 -- "text_search_data" is updated by triggers

4519 DELETE FROM "setting";

4520 DELETE FROM "setting_map";

4521 DELETE FROM "member_relation_setting";

4522 DELETE FROM "member_image";

4523 DELETE FROM "contact";

4524 DELETE FROM "ignored_member";

4525 DELETE FROM "session";

4526 DELETE FROM "area_setting";

4527 DELETE FROM "issue_setting";

4528 DELETE FROM "ignored_initiative";

4529 DELETE FROM "initiative_setting";

4530 DELETE FROM "suggestion_setting";

4531 DELETE FROM "non_voter";

4532 DELETE FROM "direct_voter" USING "issue"

4533 WHERE "direct_voter"."issue_id" = "issue"."id"

4534 AND "issue"."closed" ISNULL;

4535 RETURN;

4536 END;

4537 $$;

4538

4539 COMMENT ON FUNCTION "delete_private_data"() IS 'Used by lf_export script. DO NOT USE on productive database, but only on a copy! This function deletes all data which should not be publicly available, and can be used to create a database dump for publication. See source code to see which data is deleted. If you need a different behaviour, copy this function and modify lf_export accordingly, to avoid data-leaks after updating.';

4540

4541

4542

4543 COMMIT;