liquid_feedback_core: 5f24058af0b8 core.sql

liquid_feedback_core

view core.sql @ 409:5f24058af0b8

Secondary sorting key for issues in admission phase

author	jbe
date	Mon Oct 14 00:58:38 2013 +0200 (2013-10-14)
parents	f7b4457cf1a6
children	d301dc24b25c

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 ('2.2.6', 2, 2, 6))

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

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

611 "order_in_admission_state" INT4,

612 "max_supporter_count" INT4 );

613

614 COMMENT ON TABLE "issue_order" IS 'Ordering information for issues that are not stored in the "issue" table to avoid locking of multiple issues at once';

615

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

617 COMMENT ON COLUMN "issue_order"."order_in_admission_state" IS 'To be used for sorting issues within an area, when showing issues in admission state; NULL values sort last; updated by "lf_update_issue_order"';

618 COMMENT ON COLUMN "issue_order"."max_supporter_count" IS 'Secondary sorting key when displaying issues in admission state from different areas';

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 without tie-breaking';

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

712 COMMENT ON COLUMN "initiative"."worse_than_status_quo" IS 'TRUE, if initiative has a schulze-ranking worse than the status quo (without tie-breaking)';

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" and in case of ties with lowest "id"';

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 "issue"."area_id",

2079 "issue"."id" AS "issue_id",

2080 "supporter"."member_id",

2081 "direct_interest_snapshot"."weight"

2082 FROM "issue"

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

2084 JOIN "direct_interest_snapshot"

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

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

2087 AND "direct_interest_snapshot"."member_id" = "supporter"."member_id"

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

2089

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

2091

2092

2093 CREATE VIEW "initiative_suggestion_order_calculation" AS

2094 SELECT

2095 "initiative"."id" AS "initiative_id",

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

2097 FROM "initiative" JOIN "issue"

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

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

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

2101

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

2103

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

2105

2106

2107 CREATE VIEW "individual_suggestion_ranking" AS

2108 SELECT

2109 "opinion"."initiative_id",

2110 "opinion"."member_id",

2111 "direct_interest_snapshot"."weight",

2112 CASE WHEN

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

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

2115 THEN 1 ELSE

2116 CASE WHEN

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

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

2119 THEN 2 ELSE

2120 CASE WHEN

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

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

2123 THEN 3 ELSE 4 END

2124 END

2125 END AS "preference",

2126 "opinion"."suggestion_id"

2127 FROM "opinion"

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

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

2130 JOIN "direct_interest_snapshot"

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

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

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

2134

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

2136

2137

2138 CREATE VIEW "battle_participant" AS

2139 SELECT "initiative"."id", "initiative"."issue_id"

2140 FROM "issue" JOIN "initiative"

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

2142 WHERE "initiative"."admitted"

2143 UNION ALL

2144 SELECT NULL, "id" AS "issue_id"

2145 FROM "issue";

2146

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

2148

2149

2150 CREATE VIEW "battle_view" AS

2151 SELECT

2152 "issue"."id" AS "issue_id",

2153 "winning_initiative"."id" AS "winning_initiative_id",

2154 "losing_initiative"."id" AS "losing_initiative_id",

2155 sum(

2156 CASE WHEN

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

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

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

2160 ) AS "count"

2161 FROM "issue"

2162 LEFT JOIN "direct_voter"

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

2164 JOIN "battle_participant" AS "winning_initiative"

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

2166 JOIN "battle_participant" AS "losing_initiative"

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

2168 LEFT JOIN "vote" AS "better_vote"

2169 ON "direct_voter"."member_id" = "better_vote"."member_id"

2170 AND "winning_initiative"."id" = "better_vote"."initiative_id"

2171 LEFT JOIN "vote" AS "worse_vote"

2172 ON "direct_voter"."member_id" = "worse_vote"."member_id"

2173 AND "losing_initiative"."id" = "worse_vote"."initiative_id"

2174 WHERE "issue"."state" = 'voting'

2175 AND "issue"."phase_finished" NOTNULL

2176 AND (

2177 "winning_initiative"."id" != "losing_initiative"."id" OR

2178 ( ("winning_initiative"."id" NOTNULL AND "losing_initiative"."id" ISNULL) OR

2179 ("winning_initiative"."id" ISNULL AND "losing_initiative"."id" NOTNULL) ) )

2180 GROUP BY

2181 "issue"."id",

2182 "winning_initiative"."id",

2183 "losing_initiative"."id";

2184

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

2186

2187

2188 CREATE VIEW "expired_session" AS

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

2190

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

2192 DELETE FROM "session" WHERE "ident" = OLD."ident";

2193

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

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

2196

2197

2198 CREATE VIEW "open_issue" AS

2199 SELECT * FROM "issue" WHERE "closed" ISNULL;

2200

2201 COMMENT ON VIEW "open_issue" IS 'All open issues';

2202

2203

2204 CREATE VIEW "member_contingent" AS

2205 SELECT

2206 "member"."id" AS "member_id",

2207 "contingent"."polling",

2208 "contingent"."time_frame",

2209 CASE WHEN "contingent"."text_entry_limit" NOTNULL THEN

2210 (

2211 SELECT count(1) FROM "draft"

2212 JOIN "initiative" ON "initiative"."id" = "draft"."initiative_id"

2213 WHERE "draft"."author_id" = "member"."id"

2214 AND "initiative"."polling" = "contingent"."polling"

2215 AND "draft"."created" > now() - "contingent"."time_frame"

2216 ) + (

2217 SELECT count(1) FROM "suggestion"

2218 JOIN "initiative" ON "initiative"."id" = "suggestion"."initiative_id"

2219 WHERE "suggestion"."author_id" = "member"."id"

2220 AND "contingent"."polling" = FALSE

2221 AND "suggestion"."created" > now() - "contingent"."time_frame"

2222 )

2223 ELSE NULL END AS "text_entry_count",

2224 "contingent"."text_entry_limit",

2225 CASE WHEN "contingent"."initiative_limit" NOTNULL THEN (

2226 SELECT count(1) FROM "opening_draft" AS "draft"

2227 JOIN "initiative" ON "initiative"."id" = "draft"."initiative_id"

2228 WHERE "draft"."author_id" = "member"."id"

2229 AND "initiative"."polling" = "contingent"."polling"

2230 AND "draft"."created" > now() - "contingent"."time_frame"

2231 ) ELSE NULL END AS "initiative_count",

2232 "contingent"."initiative_limit"

2233 FROM "member" CROSS JOIN "contingent";

2234

2235 COMMENT ON VIEW "member_contingent" IS 'Actual counts of text entries and initiatives are calculated per member for each limit in the "contingent" table.';

2236

2237 COMMENT ON COLUMN "member_contingent"."text_entry_count" IS 'Only calculated when "text_entry_limit" is not null in the same row';

2238 COMMENT ON COLUMN "member_contingent"."initiative_count" IS 'Only calculated when "initiative_limit" is not null in the same row';

2239

2240

2241 CREATE VIEW "member_contingent_left" AS

2242 SELECT

2243 "member_id",

2244 "polling",

2245 max("text_entry_limit" - "text_entry_count") AS "text_entries_left",

2246 max("initiative_limit" - "initiative_count") AS "initiatives_left"

2247 FROM "member_contingent" GROUP BY "member_id", "polling";

2248

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

2250

2251

2252 CREATE VIEW "event_seen_by_member" AS

2253 SELECT

2254 "member"."id" AS "seen_by_member_id",

2255 CASE WHEN "event"."state" IN (

2256 'voting',

2257 'finished_without_winner',

2258 'finished_with_winner'

2259 ) THEN

2260 'voting'::"notify_level"

2261 ELSE

2262 CASE WHEN "event"."state" IN (

2263 'verification',

2264 'canceled_after_revocation_during_verification',

2265 'canceled_no_initiative_admitted'

2266 ) THEN

2267 'verification'::"notify_level"

2268 ELSE

2269 CASE WHEN "event"."state" IN (

2270 'discussion',

2271 'canceled_after_revocation_during_discussion'

2272 ) THEN

2273 'discussion'::"notify_level"

2274 ELSE

2275 'all'::"notify_level"

2276 END

2277 END

2278 END AS "notify_level",

2279 "event".*

2280 FROM "member" CROSS JOIN "event"

2281 LEFT JOIN "issue"

2282 ON "event"."issue_id" = "issue"."id"

2283 LEFT JOIN "membership"

2284 ON "member"."id" = "membership"."member_id"

2285 AND "issue"."area_id" = "membership"."area_id"

2286 LEFT JOIN "interest"

2287 ON "member"."id" = "interest"."member_id"

2288 AND "event"."issue_id" = "interest"."issue_id"

2289 LEFT JOIN "ignored_member"

2290 ON "member"."id" = "ignored_member"."member_id"

2291 AND "event"."member_id" = "ignored_member"."other_member_id"

2292 LEFT JOIN "ignored_initiative"

2293 ON "member"."id" = "ignored_initiative"."member_id"

2294 AND "event"."initiative_id" = "ignored_initiative"."initiative_id"

2295 WHERE (

2296 "interest"."member_id" NOTNULL OR

2297 ( "membership"."member_id" NOTNULL AND

2298 "event"."event" IN (

2299 'issue_state_changed',

2300 'initiative_created_in_new_issue',

2301 'initiative_created_in_existing_issue',

2302 'initiative_revoked' ) ) )

2303 AND "ignored_member"."member_id" ISNULL

2304 AND "ignored_initiative"."member_id" ISNULL;

2305

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

2307

2308

2309 CREATE VIEW "selected_event_seen_by_member" AS

2310 SELECT

2311 "member"."id" AS "seen_by_member_id",

2312 CASE WHEN "event"."state" IN (

2313 'voting',

2314 'finished_without_winner',

2315 'finished_with_winner'

2316 ) THEN

2317 'voting'::"notify_level"

2318 ELSE

2319 CASE WHEN "event"."state" IN (

2320 'verification',

2321 'canceled_after_revocation_during_verification',

2322 'canceled_no_initiative_admitted'

2323 ) THEN

2324 'verification'::"notify_level"

2325 ELSE

2326 CASE WHEN "event"."state" IN (

2327 'discussion',

2328 'canceled_after_revocation_during_discussion'

2329 ) THEN

2330 'discussion'::"notify_level"

2331 ELSE

2332 'all'::"notify_level"

2333 END

2334 END

2335 END AS "notify_level",

2336 "event".*

2337 FROM "member" CROSS JOIN "event"

2338 LEFT JOIN "issue"

2339 ON "event"."issue_id" = "issue"."id"

2340 LEFT JOIN "membership"

2341 ON "member"."id" = "membership"."member_id"

2342 AND "issue"."area_id" = "membership"."area_id"

2343 LEFT JOIN "interest"

2344 ON "member"."id" = "interest"."member_id"

2345 AND "event"."issue_id" = "interest"."issue_id"

2346 LEFT JOIN "ignored_member"

2347 ON "member"."id" = "ignored_member"."member_id"

2348 AND "event"."member_id" = "ignored_member"."other_member_id"

2349 LEFT JOIN "ignored_initiative"

2350 ON "member"."id" = "ignored_initiative"."member_id"

2351 AND "event"."initiative_id" = "ignored_initiative"."initiative_id"

2352 WHERE (

2353 ( "member"."notify_level" >= 'all' ) OR

2354 ( "member"."notify_level" >= 'voting' AND

2355 "event"."state" IN (

2356 'voting',

2357 'finished_without_winner',

2358 'finished_with_winner' ) ) OR

2359 ( "member"."notify_level" >= 'verification' AND

2360 "event"."state" IN (

2361 'verification',

2362 'canceled_after_revocation_during_verification',

2363 'canceled_no_initiative_admitted' ) ) OR

2364 ( "member"."notify_level" >= 'discussion' AND

2365 "event"."state" IN (

2366 'discussion',

2367 'canceled_after_revocation_during_discussion' ) ) )

2368 AND (

2369 "interest"."member_id" NOTNULL OR

2370 ( "membership"."member_id" NOTNULL AND

2371 "event"."event" IN (

2372 'issue_state_changed',

2373 'initiative_created_in_new_issue',

2374 'initiative_created_in_existing_issue',

2375 'initiative_revoked' ) ) )

2376 AND "ignored_member"."member_id" ISNULL

2377 AND "ignored_initiative"."member_id" ISNULL;

2378

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

2380

2381

2382

2383 ------------------------------------------------------

2384 -- Row set returning function for delegation chains --

2385 ------------------------------------------------------

2386

2387

2388 CREATE TYPE "delegation_chain_loop_tag" AS ENUM

2389 ('first', 'intermediate', 'last', 'repetition');

2390

2391 COMMENT ON TYPE "delegation_chain_loop_tag" IS 'Type for loop tags in "delegation_chain_row" type';

2392

2393

2394 CREATE TYPE "delegation_chain_row" AS (

2395 "index" INT4,

2396 "member_id" INT4,

2397 "member_valid" BOOLEAN,

2398 "participation" BOOLEAN,

2399 "overridden" BOOLEAN,

2400 "scope_in" "delegation_scope",

2401 "scope_out" "delegation_scope",

2402 "disabled_out" BOOLEAN,

2403 "loop" "delegation_chain_loop_tag" );

2404

2405 COMMENT ON TYPE "delegation_chain_row" IS 'Type of rows returned by "delegation_chain" function';

2406

2407 COMMENT ON COLUMN "delegation_chain_row"."index" IS 'Index starting with 0 and counting up';

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

2409 COMMENT ON COLUMN "delegation_chain_row"."overridden" IS 'True, if an entry with lower index has "participation" set to true';

2410 COMMENT ON COLUMN "delegation_chain_row"."scope_in" IS 'Scope of used incoming delegation';

2411 COMMENT ON COLUMN "delegation_chain_row"."scope_out" IS 'Scope of used outgoing delegation';

2412 COMMENT ON COLUMN "delegation_chain_row"."disabled_out" IS 'Outgoing delegation is explicitly disabled by a delegation with trustee_id set to NULL';

2413 COMMENT ON COLUMN "delegation_chain_row"."loop" IS 'Not null, if member is part of a loop, see "delegation_chain_loop_tag" type';

2414

2415

2416 CREATE FUNCTION "delegation_chain_for_closed_issue"

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

2418 "issue_id_p" "issue"."id"%TYPE )

2419 RETURNS SETOF "delegation_chain_row"

2420 LANGUAGE 'plpgsql' STABLE AS $$

2421 DECLARE

2422 "output_row" "delegation_chain_row";

2423 "direct_voter_row" "direct_voter"%ROWTYPE;

2424 "delegating_voter_row" "delegating_voter"%ROWTYPE;

2425 BEGIN

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

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

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

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

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

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

2432 LOOP

2433 SELECT INTO "direct_voter_row" * FROM "direct_voter"

2434 WHERE "issue_id" = "issue_id_p"

2435 AND "member_id" = "output_row"."member_id";

2436 IF "direct_voter_row"."member_id" NOTNULL THEN

2437 "output_row"."participation" := TRUE;

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

2439 "output_row"."disabled_out" := NULL;

2440 RETURN NEXT "output_row";

2441 RETURN;

2442 END IF;

2443 SELECT INTO "delegating_voter_row" * FROM "delegating_voter"

2444 WHERE "issue_id" = "issue_id_p"

2445 AND "member_id" = "output_row"."member_id";

2446 IF "delegating_voter_row"."member_id" ISNULL THEN

2447 RETURN;

2448 END IF;

2449 "output_row"."scope_out" := "delegating_voter_row"."scope";

2450 RETURN NEXT "output_row";

2451 "output_row"."member_id" := "delegating_voter_row"."delegate_member_ids"[1];

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

2453 END LOOP;

2454 END;

2455 $$;

2456

2457 COMMENT ON FUNCTION "delegation_chain_for_closed_issue"

2458 ( "member"."id"%TYPE,

2459 "member"."id"%TYPE )

2460 IS 'Helper function for "delegation_chain" function, handling the special case of closed issues after voting';

2461

2462

2463 CREATE FUNCTION "delegation_chain"

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

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

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

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

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

2469 "simulate_default_p" BOOLEAN DEFAULT FALSE )

2470 RETURNS SETOF "delegation_chain_row"

2471 LANGUAGE 'plpgsql' STABLE AS $$

2472 DECLARE

2473 "scope_v" "delegation_scope";

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

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

2476 "issue_row" "issue"%ROWTYPE;

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

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

2479 "output_row" "delegation_chain_row";

2480 "output_rows" "delegation_chain_row"[];

2481 "simulate_v" BOOLEAN;

2482 "simulate_here_v" BOOLEAN;

2483 "delegation_row" "delegation"%ROWTYPE;

2484 "row_count" INT4;

2485 "i" INT4;

2486 "loop_v" BOOLEAN;

2487 BEGIN

2488 IF "simulate_trustee_id_p" NOTNULL AND "simulate_default_p" THEN

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

2490 END IF;

2491 IF "simulate_trustee_id_p" NOTNULL OR "simulate_default_p" THEN

2492 "simulate_v" := TRUE;

2493 ELSE

2494 "simulate_v" := FALSE;

2495 END IF;

2496 IF

2497 "unit_id_p" NOTNULL AND

2498 "area_id_p" ISNULL AND

2499 "issue_id_p" ISNULL

2500 THEN

2501 "scope_v" := 'unit';

2502 "unit_id_v" := "unit_id_p";

2503 ELSIF

2504 "unit_id_p" ISNULL AND

2505 "area_id_p" NOTNULL AND

2506 "issue_id_p" ISNULL

2507 THEN

2508 "scope_v" := 'area';

2509 "area_id_v" := "area_id_p";

2510 SELECT "unit_id" INTO "unit_id_v"

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

2512 ELSIF

2513 "unit_id_p" ISNULL AND

2514 "area_id_p" ISNULL AND

2515 "issue_id_p" NOTNULL

2516 THEN

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

2518 IF "issue_row"."id" ISNULL THEN

2519 RETURN;

2520 END IF;

2521 IF "issue_row"."closed" NOTNULL THEN

2522 IF "simulate_v" THEN

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

2524 END IF;

2525 FOR "output_row" IN

2526 SELECT * FROM

2527 "delegation_chain_for_closed_issue"("member_id_p", "issue_id_p")

2528 LOOP

2529 RETURN NEXT "output_row";

2530 END LOOP;

2531 RETURN;

2532 END IF;

2533 "scope_v" := 'issue';

2534 SELECT "area_id" INTO "area_id_v"

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

2536 SELECT "unit_id" INTO "unit_id_v"

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

2538 ELSE

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

2540 END IF;

2541 "visited_member_ids" := '{}';

2542 "loop_member_id_v" := NULL;

2543 "output_rows" := '{}';

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

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

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

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

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

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

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

2551 LOOP

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

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

2554 ELSE

2555 "visited_member_ids" :=

2556 "visited_member_ids" || "output_row"."member_id";

2557 END IF;

2558 IF "output_row"."participation" ISNULL THEN

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

2560 ELSIF "output_row"."participation" THEN

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

2562 END IF;

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

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

2565 SELECT NULL FROM "member" JOIN "privilege"

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

2567 AND "privilege"."unit_id" = "unit_id_v"

2568 WHERE "id" = "output_row"."member_id"

2569 AND "member"."active" AND "privilege"."voting_right"

2570 );

2571 "simulate_here_v" := (

2572 "simulate_v" AND

2573 "output_row"."member_id" = "member_id_p"

2574 );

2575 "delegation_row" := ROW(NULL);

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

2577 IF "scope_v" = 'unit' THEN

2578 IF NOT "simulate_here_v" THEN

2579 SELECT * INTO "delegation_row" FROM "delegation"

2580 WHERE "truster_id" = "output_row"."member_id"

2581 AND "unit_id" = "unit_id_v";

2582 END IF;

2583 ELSIF "scope_v" = 'area' THEN

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

2585 SELECT NULL FROM "membership"

2586 WHERE "area_id" = "area_id_p"

2587 AND "member_id" = "output_row"."member_id"

2588 );

2589 IF "simulate_here_v" THEN

2590 IF "simulate_trustee_id_p" ISNULL THEN

2591 SELECT * INTO "delegation_row" FROM "delegation"

2592 WHERE "truster_id" = "output_row"."member_id"

2593 AND "unit_id" = "unit_id_v";

2594 END IF;

2595 ELSE

2596 SELECT * INTO "delegation_row" FROM "delegation"

2597 WHERE "truster_id" = "output_row"."member_id"

2598 AND (

2599 "unit_id" = "unit_id_v" OR

2600 "area_id" = "area_id_v"

2601 )

2602 ORDER BY "scope" DESC;

2603 END IF;

2604 ELSIF "scope_v" = 'issue' THEN

2605 IF "issue_row"."fully_frozen" ISNULL THEN

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

2607 SELECT NULL FROM "interest"

2608 WHERE "issue_id" = "issue_id_p"

2609 AND "member_id" = "output_row"."member_id"

2610 );

2611 ELSE

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

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

2614 SELECT NULL FROM "direct_voter"

2615 WHERE "issue_id" = "issue_id_p"

2616 AND "member_id" = "output_row"."member_id"

2617 );

2618 ELSE

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

2620 END IF;

2621 END IF;

2622 IF "simulate_here_v" THEN

2623 IF "simulate_trustee_id_p" ISNULL THEN

2624 SELECT * INTO "delegation_row" FROM "delegation"

2625 WHERE "truster_id" = "output_row"."member_id"

2626 AND (

2627 "unit_id" = "unit_id_v" OR

2628 "area_id" = "area_id_v"

2629 )

2630 ORDER BY "scope" DESC;

2631 END IF;

2632 ELSE

2633 SELECT * INTO "delegation_row" FROM "delegation"

2634 WHERE "truster_id" = "output_row"."member_id"

2635 AND (

2636 "unit_id" = "unit_id_v" OR

2637 "area_id" = "area_id_v" OR

2638 "issue_id" = "issue_id_p"

2639 )

2640 ORDER BY "scope" DESC;

2641 END IF;

2642 END IF;

2643 ELSE

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

2645 END IF;

2646 IF "simulate_here_v" AND "simulate_trustee_id_p" NOTNULL THEN

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

2648 "output_rows" := "output_rows" || "output_row";

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

2650 ELSIF "delegation_row"."trustee_id" NOTNULL THEN

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

2652 "output_rows" := "output_rows" || "output_row";

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

2654 ELSIF "delegation_row"."scope" NOTNULL THEN

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

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

2657 "output_rows" := "output_rows" || "output_row";

2658 EXIT;

2659 ELSE

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

2661 "output_rows" := "output_rows" || "output_row";

2662 EXIT;

2663 END IF;

2664 EXIT WHEN "loop_member_id_v" NOTNULL;

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

2666 END LOOP;

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

2668 "i" := 1;

2669 "loop_v" := FALSE;

2670 LOOP

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

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

2673 IF "loop_v" THEN

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

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

2676 ELSIF "i" = "row_count" THEN

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

2678 ELSE

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

2680 END IF;

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

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

2683 "loop_v" := TRUE;

2684 END IF;

2685 IF "scope_v" = 'unit' THEN

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

2687 END IF;

2688 RETURN NEXT "output_row";

2689 "i" := "i" + 1;

2690 END LOOP;

2691 RETURN;

2692 END;

2693 $$;

2694

2695 COMMENT ON FUNCTION "delegation_chain"

2696 ( "member"."id"%TYPE,

2697 "unit"."id"%TYPE,

2698 "area"."id"%TYPE,

2699 "issue"."id"%TYPE,

2700 "member"."id"%TYPE,

2701 BOOLEAN )

2702 IS 'Shows a delegation chain for unit, area, or issue; See "delegation_chain_row" type for more information';

2703

2704

2705

2706 ---------------------------------------------------------

2707 -- Single row returning function for delegation chains --

2708 ---------------------------------------------------------

2709

2710

2711 CREATE TYPE "delegation_info_loop_type" AS ENUM

2712 ('own', 'first', 'first_ellipsis', 'other', 'other_ellipsis');

2713

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

2715

2716

2717 CREATE TYPE "delegation_info_type" AS (

2718 "own_participation" BOOLEAN,

2719 "own_delegation_scope" "delegation_scope",

2720 "first_trustee_id" INT4,

2721 "first_trustee_participation" BOOLEAN,

2722 "first_trustee_ellipsis" BOOLEAN,

2723 "other_trustee_id" INT4,

2724 "other_trustee_participation" BOOLEAN,

2725 "other_trustee_ellipsis" BOOLEAN,

2726 "delegation_loop" "delegation_info_loop_type",

2727 "participating_member_id" INT4 );

2728

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

2730

2731 COMMENT ON COLUMN "delegation_info_type"."own_participation" IS 'Member is directly participating';

2732 COMMENT ON COLUMN "delegation_info_type"."own_delegation_scope" IS 'Delegation scope of member';

2733 COMMENT ON COLUMN "delegation_info_type"."first_trustee_id" IS 'Direct trustee of member';

2734 COMMENT ON COLUMN "delegation_info_type"."first_trustee_participation" IS 'Direct trustee of member is participating';

2735 COMMENT ON COLUMN "delegation_info_type"."first_trustee_ellipsis" IS 'Ellipsis in delegation chain after "first_trustee"';

2736 COMMENT ON COLUMN "delegation_info_type"."other_trustee_id" IS 'Another relevant trustee (due to participation)';

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

2738 COMMENT ON COLUMN "delegation_info_type"."other_trustee_ellipsis" IS 'Ellipsis in delegation chain after "other_trustee"';

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

2740 COMMENT ON COLUMN "delegation_info_type"."participating_member_id" IS 'First participating member in delegation chain';

2741

2742

2743 CREATE FUNCTION "delegation_info"

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

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

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

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

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

2749 "simulate_default_p" BOOLEAN DEFAULT FALSE )

2750 RETURNS "delegation_info_type"

2751 LANGUAGE 'plpgsql' STABLE AS $$

2752 DECLARE

2753 "current_row" "delegation_chain_row";

2754 "result" "delegation_info_type";

2755 BEGIN

2756 "result"."own_participation" := FALSE;

2757 FOR "current_row" IN

2758 SELECT * FROM "delegation_chain"(

2759 "member_id_p",

2760 "unit_id_p", "area_id_p", "issue_id_p",

2761 "simulate_trustee_id_p", "simulate_default_p")

2762 LOOP

2763 IF

2764 "result"."participating_member_id" ISNULL AND

2765 "current_row"."participation"

2766 THEN

2767 "result"."participating_member_id" := "current_row"."member_id";

2768 END IF;

2769 IF "current_row"."member_id" = "member_id_p" THEN

2770 "result"."own_participation" := "current_row"."participation";

2771 "result"."own_delegation_scope" := "current_row"."scope_out";

2772 IF "current_row"."loop" = 'first' THEN

2773 "result"."delegation_loop" := 'own';

2774 END IF;

2775 ELSIF

2776 "current_row"."member_valid" AND

2777 ( "current_row"."loop" ISNULL OR

2778 "current_row"."loop" != 'repetition' )

2779 THEN

2780 IF "result"."first_trustee_id" ISNULL THEN

2781 "result"."first_trustee_id" := "current_row"."member_id";

2782 "result"."first_trustee_participation" := "current_row"."participation";

2783 "result"."first_trustee_ellipsis" := FALSE;

2784 IF "current_row"."loop" = 'first' THEN

2785 "result"."delegation_loop" := 'first';

2786 END IF;

2787 ELSIF "result"."other_trustee_id" ISNULL THEN

2788 IF "current_row"."participation" AND NOT "current_row"."overridden" THEN

2789 "result"."other_trustee_id" := "current_row"."member_id";

2790 "result"."other_trustee_participation" := TRUE;

2791 "result"."other_trustee_ellipsis" := FALSE;

2792 IF "current_row"."loop" = 'first' THEN

2793 "result"."delegation_loop" := 'other';

2794 END IF;

2795 ELSE

2796 "result"."first_trustee_ellipsis" := TRUE;

2797 IF "current_row"."loop" = 'first' THEN

2798 "result"."delegation_loop" := 'first_ellipsis';

2799 END IF;

2800 END IF;

2801 ELSE

2802 "result"."other_trustee_ellipsis" := TRUE;

2803 IF "current_row"."loop" = 'first' THEN

2804 "result"."delegation_loop" := 'other_ellipsis';

2805 END IF;

2806 END IF;

2807 END IF;

2808 END LOOP;

2809 RETURN "result";

2810 END;

2811 $$;

2812

2813 COMMENT ON FUNCTION "delegation_info"

2814 ( "member"."id"%TYPE,

2815 "unit"."id"%TYPE,

2816 "area"."id"%TYPE,

2817 "issue"."id"%TYPE,

2818 "member"."id"%TYPE,

2819 BOOLEAN )

2820 IS 'Notable information about a delegation chain for unit, area, or issue; See "delegation_info_type" for more information';

2821

2822

2823

2824 ---------------------------

2825 -- Transaction isolation --

2826 ---------------------------

2827

2828

2829 CREATE FUNCTION "require_transaction_isolation"()

2830 RETURNS VOID

2831 LANGUAGE 'plpgsql' VOLATILE AS $$

2832 BEGIN

2833 IF

2834 current_setting('transaction_isolation') NOT IN

2835 ('repeatable read', 'serializable')

2836 THEN

2837 RAISE EXCEPTION 'Insufficient transaction isolation level';

2838 END IF;

2839 RETURN;

2840 END;

2841 $$;

2842

2843 COMMENT ON FUNCTION "require_transaction_isolation"() IS 'Throws an exception, if transaction isolation level is too low to provide a consistent snapshot';

2844

2845

2846 CREATE FUNCTION "dont_require_transaction_isolation"()

2847 RETURNS VOID

2848 LANGUAGE 'plpgsql' VOLATILE AS $$

2849 BEGIN

2850 IF

2851 current_setting('transaction_isolation') IN

2852 ('repeatable read', 'serializable')

2853 THEN

2854 RAISE WARNING 'Unneccessary transaction isolation level: %',

2855 current_setting('transaction_isolation');

2856 END IF;

2857 RETURN;

2858 END;

2859 $$;

2860

2861 COMMENT ON FUNCTION "dont_require_transaction_isolation"() IS 'Raises a warning, if transaction isolation level is higher than READ COMMITTED';

2862

2863

2864

2865 ------------------------------------------------------------------------

2866 -- Regular tasks, except calculcation of snapshots and voting results --

2867 ------------------------------------------------------------------------

2868

2869

2870 CREATE FUNCTION "check_activity"()

2871 RETURNS VOID

2872 LANGUAGE 'plpgsql' VOLATILE AS $$

2873 DECLARE

2874 "system_setting_row" "system_setting"%ROWTYPE;

2875 BEGIN

2876 PERFORM "dont_require_transaction_isolation"();

2877 SELECT * INTO "system_setting_row" FROM "system_setting";

2878 IF "system_setting_row"."member_ttl" NOTNULL THEN

2879 UPDATE "member" SET "active" = FALSE

2880 WHERE "active" = TRUE

2881 AND "last_activity" < (now() - "system_setting_row"."member_ttl")::DATE;

2882 END IF;

2883 RETURN;

2884 END;

2885 $$;

2886

2887 COMMENT ON FUNCTION "check_activity"() IS 'Deactivates members when "last_activity" is older than "system_setting"."member_ttl".';

2888

2889

2890 CREATE FUNCTION "calculate_member_counts"()

2891 RETURNS VOID

2892 LANGUAGE 'plpgsql' VOLATILE AS $$

2893 BEGIN

2894 PERFORM "require_transaction_isolation"();

2895 DELETE FROM "member_count";

2896 INSERT INTO "member_count" ("total_count")

2897 SELECT "total_count" FROM "member_count_view";

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

2899 FROM "unit_member_count" AS "view"

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

2901 UPDATE "area" SET

2902 "direct_member_count" = "view"."direct_member_count",

2903 "member_weight" = "view"."member_weight"

2904 FROM "area_member_count" AS "view"

2905 WHERE "view"."area_id" = "area"."id";

2906 RETURN;

2907 END;

2908 $$;

2909

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

2911

2912

2913

2914 ------------------------------------

2915 -- Calculation of harmonic weight --

2916 ------------------------------------

2917

2918

2919 CREATE VIEW "remaining_harmonic_supporter_weight" AS

2920 SELECT

2921 "direct_interest_snapshot"."issue_id",

2922 "direct_interest_snapshot"."event",

2923 "direct_interest_snapshot"."member_id",

2924 "direct_interest_snapshot"."weight" AS "weight_num",

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

2926 FROM "issue"

2927 JOIN "direct_interest_snapshot"

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

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

2930 JOIN "initiative"

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

2932 AND "initiative"."harmonic_weight" ISNULL

2933 JOIN "direct_supporter_snapshot"

2934 ON "initiative"."id" = "direct_supporter_snapshot"."initiative_id"

2935 AND "direct_interest_snapshot"."event" = "direct_supporter_snapshot"."event"

2936 AND "direct_interest_snapshot"."member_id" = "direct_supporter_snapshot"."member_id"

2937 AND (

2938 "direct_supporter_snapshot"."satisfied" = TRUE OR

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

2940 )

2941 GROUP BY

2942 "direct_interest_snapshot"."issue_id",

2943 "direct_interest_snapshot"."event",

2944 "direct_interest_snapshot"."member_id",

2945 "direct_interest_snapshot"."weight";

2946

2947 COMMENT ON VIEW "remaining_harmonic_supporter_weight" IS 'Helper view for function "set_harmonic_initiative_weights"';

2948

2949

2950 CREATE VIEW "remaining_harmonic_initiative_weight_summands" AS

2951 SELECT

2952 "initiative"."issue_id",

2953 "initiative"."id" AS "initiative_id",

2954 "initiative"."admitted",

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

2956 "remaining_harmonic_supporter_weight"."weight_den"

2957 FROM "remaining_harmonic_supporter_weight"

2958 JOIN "initiative"

2959 ON "remaining_harmonic_supporter_weight"."issue_id" = "initiative"."issue_id"

2960 AND "initiative"."harmonic_weight" ISNULL

2961 JOIN "direct_supporter_snapshot"

2962 ON "initiative"."id" = "direct_supporter_snapshot"."initiative_id"

2963 AND "remaining_harmonic_supporter_weight"."event" = "direct_supporter_snapshot"."event"

2964 AND "remaining_harmonic_supporter_weight"."member_id" = "direct_supporter_snapshot"."member_id"

2965 AND (

2966 "direct_supporter_snapshot"."satisfied" = TRUE OR

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

2968 )

2969 GROUP BY

2970 "initiative"."issue_id",

2971 "initiative"."id",

2972 "initiative"."admitted",

2973 "remaining_harmonic_supporter_weight"."weight_den";

2974

2975 COMMENT ON VIEW "remaining_harmonic_initiative_weight_summands" IS 'Helper view for function "set_harmonic_initiative_weights"';

2976

2977

2978 CREATE VIEW "remaining_harmonic_initiative_weight_dummies" AS

2979 SELECT

2980 "issue_id",

2981 "id" AS "initiative_id",

2982 "admitted",

2983 0 AS "weight_num",

2984 1 AS "weight_den"

2985 FROM "initiative"

2986 WHERE "harmonic_weight" ISNULL;

2987

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

2989

2990

2991 CREATE FUNCTION "set_harmonic_initiative_weights"

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

2993 RETURNS VOID

2994 LANGUAGE 'plpgsql' VOLATILE AS $$

2995 DECLARE

2996 "weight_row" "remaining_harmonic_initiative_weight_summands"%ROWTYPE;

2997 "i" INT4;

2998 "count_v" INT4;

2999 "summand_v" FLOAT;

3000 "id_ary" INT4[];

3001 "weight_ary" FLOAT[];

3002 "min_weight_v" FLOAT;

3003 BEGIN

3004 PERFORM "require_transaction_isolation"();

3005 UPDATE "initiative" SET "harmonic_weight" = NULL

3006 WHERE "issue_id" = "issue_id_p";

3007 LOOP

3008 "min_weight_v" := NULL;

3009 "i" := 0;

3010 "count_v" := 0;

3011 FOR "weight_row" IN

3012 SELECT * FROM "remaining_harmonic_initiative_weight_summands"

3013 WHERE "issue_id" = "issue_id_p"

3014 AND (

3015 coalesce("admitted", FALSE) = FALSE OR NOT EXISTS (

3016 SELECT NULL FROM "initiative"

3017 WHERE "issue_id" = "issue_id_p"

3018 AND "harmonic_weight" ISNULL

3019 AND coalesce("admitted", FALSE) = FALSE

3020 )

3021 )

3022 UNION ALL -- needed for corner cases

3023 SELECT * FROM "remaining_harmonic_initiative_weight_dummies"

3024 WHERE "issue_id" = "issue_id_p"

3025 AND (

3026 coalesce("admitted", FALSE) = FALSE OR NOT EXISTS (

3027 SELECT NULL FROM "initiative"

3028 WHERE "issue_id" = "issue_id_p"

3029 AND "harmonic_weight" ISNULL

3030 AND coalesce("admitted", FALSE) = FALSE

3031 )

3032 )

3033 ORDER BY "initiative_id" DESC, "weight_den" DESC

3034 -- NOTE: non-admitted initiatives placed first (at last positions),

3035 -- latest initiatives treated worse in case of tie

3036 LOOP

3037 "summand_v" := "weight_row"."weight_num"::FLOAT / "weight_row"."weight_den"::FLOAT;

3038 IF "i" = 0 OR "weight_row"."initiative_id" != "id_ary"["i"] THEN

3039 "i" := "i" + 1;

3040 "count_v" := "i";

3041 "id_ary"["i"] := "weight_row"."initiative_id";

3042 "weight_ary"["i"] := "summand_v";

3043 ELSE

3044 "weight_ary"["i"] := "weight_ary"["i"] + "summand_v";

3045 END IF;

3046 END LOOP;

3047 EXIT WHEN "count_v" = 0;

3048 "i" := 1;

3049 LOOP

3050 "weight_ary"["i"] := "weight_ary"["i"]::NUMERIC(18,9)::NUMERIC(12,3);

3051 IF "min_weight_v" ISNULL OR "weight_ary"["i"] < "min_weight_v" THEN

3052 "min_weight_v" := "weight_ary"["i"];

3053 END IF;

3054 "i" := "i" + 1;

3055 EXIT WHEN "i" > "count_v";

3056 END LOOP;

3057 "i" := 1;

3058 LOOP

3059 IF "weight_ary"["i"] = "min_weight_v" THEN

3060 UPDATE "initiative" SET "harmonic_weight" = "min_weight_v"

3061 WHERE "id" = "id_ary"["i"];

3062 EXIT;

3063 END IF;

3064 "i" := "i" + 1;

3065 END LOOP;

3066 END LOOP;

3067 UPDATE "initiative" SET "harmonic_weight" = 0

3068 WHERE "issue_id" = "issue_id_p" AND "harmonic_weight" ISNULL;

3069 END;

3070 $$;

3071

3072 COMMENT ON FUNCTION "set_harmonic_initiative_weights"

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

3074 IS 'Calculates and sets "harmonic_weight" of initiatives in a given issue';

3075

3076

3077

3078 ------------------------------

3079 -- Calculation of snapshots --

3080 ------------------------------

3081

3082

3083 CREATE FUNCTION "weight_of_added_delegations_for_population_snapshot"

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

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

3086 "delegate_member_ids_p" "delegating_population_snapshot"."delegate_member_ids"%TYPE )

3087 RETURNS "direct_population_snapshot"."weight"%TYPE

3088 LANGUAGE 'plpgsql' VOLATILE AS $$

3089 DECLARE

3090 "issue_delegation_row" "issue_delegation"%ROWTYPE;

3091 "delegate_member_ids_v" "delegating_population_snapshot"."delegate_member_ids"%TYPE;

3092 "weight_v" INT4;

3093 "sub_weight_v" INT4;

3094 BEGIN

3095 PERFORM "require_transaction_isolation"();

3096 "weight_v" := 0;

3097 FOR "issue_delegation_row" IN

3098 SELECT * FROM "issue_delegation"

3099 WHERE "trustee_id" = "member_id_p"

3100 AND "issue_id" = "issue_id_p"

3101 LOOP

3102 IF NOT EXISTS (

3103 SELECT NULL FROM "direct_population_snapshot"

3104 WHERE "issue_id" = "issue_id_p"

3105 AND "event" = 'periodic'

3106 AND "member_id" = "issue_delegation_row"."truster_id"

3107 ) AND NOT EXISTS (

3108 SELECT NULL FROM "delegating_population_snapshot"

3109 WHERE "issue_id" = "issue_id_p"

3110 AND "event" = 'periodic'

3111 AND "member_id" = "issue_delegation_row"."truster_id"

3112 ) THEN

3113 "delegate_member_ids_v" :=

3114 "member_id_p" || "delegate_member_ids_p";

3115 INSERT INTO "delegating_population_snapshot" (

3116 "issue_id",

3117 "event",

3118 "member_id",

3119 "scope",

3120 "delegate_member_ids"

3121 ) VALUES (

3122 "issue_id_p",

3123 'periodic',

3124 "issue_delegation_row"."truster_id",

3125 "issue_delegation_row"."scope",

3126 "delegate_member_ids_v"

3127 );

3128 "sub_weight_v" := 1 +

3129 "weight_of_added_delegations_for_population_snapshot"(

3130 "issue_id_p",

3131 "issue_delegation_row"."truster_id",

3132 "delegate_member_ids_v"

3133 );

3134 UPDATE "delegating_population_snapshot"

3135 SET "weight" = "sub_weight_v"

3136 WHERE "issue_id" = "issue_id_p"

3137 AND "event" = 'periodic'

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

3139 "weight_v" := "weight_v" + "sub_weight_v";

3140 END IF;

3141 END LOOP;

3142 RETURN "weight_v";

3143 END;

3144 $$;

3145

3146 COMMENT ON FUNCTION "weight_of_added_delegations_for_population_snapshot"

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

3148 "member"."id"%TYPE,

3149 "delegating_population_snapshot"."delegate_member_ids"%TYPE )

3150 IS 'Helper function for "create_population_snapshot" function';

3151

3152

3153 CREATE FUNCTION "create_population_snapshot"

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

3155 RETURNS VOID

3156 LANGUAGE 'plpgsql' VOLATILE AS $$

3157 DECLARE

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

3159 BEGIN

3160 PERFORM "require_transaction_isolation"();

3161 DELETE FROM "direct_population_snapshot"

3162 WHERE "issue_id" = "issue_id_p"

3163 AND "event" = 'periodic';

3164 DELETE FROM "delegating_population_snapshot"

3165 WHERE "issue_id" = "issue_id_p"

3166 AND "event" = 'periodic';

3167 INSERT INTO "direct_population_snapshot"

3168 ("issue_id", "event", "member_id")

3169 SELECT

3170 "issue_id_p" AS "issue_id",

3171 'periodic'::"snapshot_event" AS "event",

3172 "member"."id" AS "member_id"

3173 FROM "issue"

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

3175 JOIN "membership" ON "area"."id" = "membership"."area_id"

3176 JOIN "member" ON "membership"."member_id" = "member"."id"

3177 JOIN "privilege"

3178 ON "privilege"."unit_id" = "area"."unit_id"

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

3180 WHERE "issue"."id" = "issue_id_p"

3181 AND "member"."active" AND "privilege"."voting_right"

3182 UNION

3183 SELECT

3184 "issue_id_p" AS "issue_id",

3185 'periodic'::"snapshot_event" AS "event",

3186 "member"."id" AS "member_id"

3187 FROM "issue"

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

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

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

3191 JOIN "privilege"

3192 ON "privilege"."unit_id" = "area"."unit_id"

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

3194 WHERE "issue"."id" = "issue_id_p"

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

3196 FOR "member_id_v" IN

3197 SELECT "member_id" FROM "direct_population_snapshot"

3198 WHERE "issue_id" = "issue_id_p"

3199 AND "event" = 'periodic'

3200 LOOP

3201 UPDATE "direct_population_snapshot" SET

3202 "weight" = 1 +

3203 "weight_of_added_delegations_for_population_snapshot"(

3204 "issue_id_p",

3205 "member_id_v",

3206 '{}'

3207 )

3208 WHERE "issue_id" = "issue_id_p"

3209 AND "event" = 'periodic'

3210 AND "member_id" = "member_id_v";

3211 END LOOP;

3212 RETURN;

3213 END;

3214 $$;

3215

3216 COMMENT ON FUNCTION "create_population_snapshot"

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

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

3219

3220

3221 CREATE FUNCTION "weight_of_added_delegations_for_interest_snapshot"

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

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

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

3225 RETURNS "direct_interest_snapshot"."weight"%TYPE

3226 LANGUAGE 'plpgsql' VOLATILE AS $$

3227 DECLARE

3228 "issue_delegation_row" "issue_delegation"%ROWTYPE;

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

3230 "weight_v" INT4;

3231 "sub_weight_v" INT4;

3232 BEGIN

3233 PERFORM "require_transaction_isolation"();

3234 "weight_v" := 0;

3235 FOR "issue_delegation_row" IN

3236 SELECT * FROM "issue_delegation"

3237 WHERE "trustee_id" = "member_id_p"

3238 AND "issue_id" = "issue_id_p"

3239 LOOP

3240 IF NOT EXISTS (

3241 SELECT NULL FROM "direct_interest_snapshot"

3242 WHERE "issue_id" = "issue_id_p"

3243 AND "event" = 'periodic'

3244 AND "member_id" = "issue_delegation_row"."truster_id"

3245 ) AND NOT EXISTS (

3246 SELECT NULL FROM "delegating_interest_snapshot"

3247 WHERE "issue_id" = "issue_id_p"

3248 AND "event" = 'periodic'

3249 AND "member_id" = "issue_delegation_row"."truster_id"

3250 ) THEN

3251 "delegate_member_ids_v" :=

3252 "member_id_p" || "delegate_member_ids_p";

3253 INSERT INTO "delegating_interest_snapshot" (

3254 "issue_id",

3255 "event",

3256 "member_id",

3257 "scope",

3258 "delegate_member_ids"

3259 ) VALUES (

3260 "issue_id_p",

3261 'periodic',

3262 "issue_delegation_row"."truster_id",

3263 "issue_delegation_row"."scope",

3264 "delegate_member_ids_v"

3265 );

3266 "sub_weight_v" := 1 +

3267 "weight_of_added_delegations_for_interest_snapshot"(

3268 "issue_id_p",

3269 "issue_delegation_row"."truster_id",

3270 "delegate_member_ids_v"

3271 );

3272 UPDATE "delegating_interest_snapshot"

3273 SET "weight" = "sub_weight_v"

3274 WHERE "issue_id" = "issue_id_p"

3275 AND "event" = 'periodic'

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

3277 "weight_v" := "weight_v" + "sub_weight_v";

3278 END IF;

3279 END LOOP;

3280 RETURN "weight_v";

3281 END;

3282 $$;

3283

3284 COMMENT ON FUNCTION "weight_of_added_delegations_for_interest_snapshot"

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

3286 "member"."id"%TYPE,

3287 "delegating_interest_snapshot"."delegate_member_ids"%TYPE )

3288 IS 'Helper function for "create_interest_snapshot" function';

3289

3290

3291 CREATE FUNCTION "create_interest_snapshot"

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

3293 RETURNS VOID

3294 LANGUAGE 'plpgsql' VOLATILE AS $$

3295 DECLARE

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

3297 BEGIN

3298 PERFORM "require_transaction_isolation"();

3299 DELETE FROM "direct_interest_snapshot"

3300 WHERE "issue_id" = "issue_id_p"

3301 AND "event" = 'periodic';

3302 DELETE FROM "delegating_interest_snapshot"

3303 WHERE "issue_id" = "issue_id_p"

3304 AND "event" = 'periodic';

3305 DELETE FROM "direct_supporter_snapshot"

3306 USING "initiative" -- NOTE: due to missing index on issue_id

3307 WHERE "initiative"."issue_id" = "issue_id_p"

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

3309 AND "direct_supporter_snapshot"."event" = 'periodic';

3310 INSERT INTO "direct_interest_snapshot"

3311 ("issue_id", "event", "member_id")

3312 SELECT

3313 "issue_id_p" AS "issue_id",

3314 'periodic' AS "event",

3315 "member"."id" AS "member_id"

3316 FROM "issue"

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

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

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

3320 JOIN "privilege"

3321 ON "privilege"."unit_id" = "area"."unit_id"

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

3323 WHERE "issue"."id" = "issue_id_p"

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

3325 FOR "member_id_v" IN

3326 SELECT "member_id" FROM "direct_interest_snapshot"

3327 WHERE "issue_id" = "issue_id_p"

3328 AND "event" = 'periodic'

3329 LOOP

3330 UPDATE "direct_interest_snapshot" SET

3331 "weight" = 1 +

3332 "weight_of_added_delegations_for_interest_snapshot"(

3333 "issue_id_p",

3334 "member_id_v",

3335 '{}'

3336 )

3337 WHERE "issue_id" = "issue_id_p"

3338 AND "event" = 'periodic'

3339 AND "member_id" = "member_id_v";

3340 END LOOP;

3341 INSERT INTO "direct_supporter_snapshot"

3342 ( "issue_id", "initiative_id", "event", "member_id",

3343 "draft_id", "informed", "satisfied" )

3344 SELECT

3345 "issue_id_p" AS "issue_id",

3346 "initiative"."id" AS "initiative_id",

3347 'periodic' AS "event",

3348 "supporter"."member_id" AS "member_id",

3349 "supporter"."draft_id" AS "draft_id",

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

3351 NOT EXISTS (

3352 SELECT NULL FROM "critical_opinion"

3353 WHERE "initiative_id" = "initiative"."id"

3354 AND "member_id" = "supporter"."member_id"

3355 ) AS "satisfied"

3356 FROM "initiative"

3357 JOIN "supporter"

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

3359 JOIN "current_draft"

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

3361 JOIN "direct_interest_snapshot"

3362 ON "supporter"."member_id" = "direct_interest_snapshot"."member_id"

3363 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"

3364 AND "event" = 'periodic'

3365 WHERE "initiative"."issue_id" = "issue_id_p";

3366 RETURN;

3367 END;

3368 $$;

3369

3370 COMMENT ON FUNCTION "create_interest_snapshot"

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

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

3373

3374

3375 CREATE FUNCTION "create_snapshot"

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

3377 RETURNS VOID

3378 LANGUAGE 'plpgsql' VOLATILE AS $$

3379 DECLARE

3380 "initiative_id_v" "initiative"."id"%TYPE;

3381 "suggestion_id_v" "suggestion"."id"%TYPE;

3382 BEGIN

3383 PERFORM "require_transaction_isolation"();

3384 PERFORM "create_population_snapshot"("issue_id_p");

3385 PERFORM "create_interest_snapshot"("issue_id_p");

3386 UPDATE "issue" SET

3387 "snapshot" = coalesce("phase_finished", now()),

3388 "latest_snapshot_event" = 'periodic',

3389 "population" = (

3390 SELECT coalesce(sum("weight"), 0)

3391 FROM "direct_population_snapshot"

3392 WHERE "issue_id" = "issue_id_p"

3393 AND "event" = 'periodic'

3394 )

3395 WHERE "id" = "issue_id_p";

3396 FOR "initiative_id_v" IN

3397 SELECT "id" FROM "initiative" WHERE "issue_id" = "issue_id_p"

3398 LOOP

3399 UPDATE "initiative" SET

3400 "supporter_count" = (

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

3402 FROM "direct_interest_snapshot" AS "di"

3403 JOIN "direct_supporter_snapshot" AS "ds"

3404 ON "di"."member_id" = "ds"."member_id"

3405 WHERE "di"."issue_id" = "issue_id_p"

3406 AND "di"."event" = 'periodic'

3407 AND "ds"."initiative_id" = "initiative_id_v"

3408 AND "ds"."event" = 'periodic'

3409 ),

3410 "informed_supporter_count" = (

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

3412 FROM "direct_interest_snapshot" AS "di"

3413 JOIN "direct_supporter_snapshot" AS "ds"

3414 ON "di"."member_id" = "ds"."member_id"

3415 WHERE "di"."issue_id" = "issue_id_p"

3416 AND "di"."event" = 'periodic'

3417 AND "ds"."initiative_id" = "initiative_id_v"

3418 AND "ds"."event" = 'periodic'

3419 AND "ds"."informed"

3420 ),

3421 "satisfied_supporter_count" = (

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

3423 FROM "direct_interest_snapshot" AS "di"

3424 JOIN "direct_supporter_snapshot" AS "ds"

3425 ON "di"."member_id" = "ds"."member_id"

3426 WHERE "di"."issue_id" = "issue_id_p"

3427 AND "di"."event" = 'periodic'

3428 AND "ds"."initiative_id" = "initiative_id_v"

3429 AND "ds"."event" = 'periodic'

3430 AND "ds"."satisfied"

3431 ),

3432 "satisfied_informed_supporter_count" = (

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

3434 FROM "direct_interest_snapshot" AS "di"

3435 JOIN "direct_supporter_snapshot" AS "ds"

3436 ON "di"."member_id" = "ds"."member_id"

3437 WHERE "di"."issue_id" = "issue_id_p"

3438 AND "di"."event" = 'periodic'

3439 AND "ds"."initiative_id" = "initiative_id_v"

3440 AND "ds"."event" = 'periodic'

3441 AND "ds"."informed"

3442 AND "ds"."satisfied"

3443 )

3444 WHERE "id" = "initiative_id_v";

3445 FOR "suggestion_id_v" IN

3446 SELECT "id" FROM "suggestion"

3447 WHERE "initiative_id" = "initiative_id_v"

3448 LOOP

3449 UPDATE "suggestion" SET

3450 "minus2_unfulfilled_count" = (

3451 SELECT coalesce(sum("snapshot"."weight"), 0)

3452 FROM "issue" CROSS JOIN "opinion"

3453 JOIN "direct_interest_snapshot" AS "snapshot"

3454 ON "snapshot"."issue_id" = "issue"."id"

3455 AND "snapshot"."event" = "issue"."latest_snapshot_event"

3456 AND "snapshot"."member_id" = "opinion"."member_id"

3457 WHERE "issue"."id" = "issue_id_p"

3458 AND "opinion"."suggestion_id" = "suggestion_id_v"

3459 AND "opinion"."degree" = -2

3460 AND "opinion"."fulfilled" = FALSE

3461 ),

3462 "minus2_fulfilled_count" = (

3463 SELECT coalesce(sum("snapshot"."weight"), 0)

3464 FROM "issue" CROSS JOIN "opinion"

3465 JOIN "direct_interest_snapshot" AS "snapshot"

3466 ON "snapshot"."issue_id" = "issue"."id"

3467 AND "snapshot"."event" = "issue"."latest_snapshot_event"

3468 AND "snapshot"."member_id" = "opinion"."member_id"

3469 WHERE "issue"."id" = "issue_id_p"

3470 AND "opinion"."suggestion_id" = "suggestion_id_v"

3471 AND "opinion"."degree" = -2

3472 AND "opinion"."fulfilled" = TRUE

3473 ),

3474 "minus1_unfulfilled_count" = (

3475 SELECT coalesce(sum("snapshot"."weight"), 0)

3476 FROM "issue" CROSS JOIN "opinion"

3477 JOIN "direct_interest_snapshot" AS "snapshot"

3478 ON "snapshot"."issue_id" = "issue"."id"

3479 AND "snapshot"."event" = "issue"."latest_snapshot_event"

3480 AND "snapshot"."member_id" = "opinion"."member_id"

3481 WHERE "issue"."id" = "issue_id_p"

3482 AND "opinion"."suggestion_id" = "suggestion_id_v"

3483 AND "opinion"."degree" = -1

3484 AND "opinion"."fulfilled" = FALSE

3485 ),

3486 "minus1_fulfilled_count" = (

3487 SELECT coalesce(sum("snapshot"."weight"), 0)

3488 FROM "issue" CROSS JOIN "opinion"

3489 JOIN "direct_interest_snapshot" AS "snapshot"

3490 ON "snapshot"."issue_id" = "issue"."id"

3491 AND "snapshot"."event" = "issue"."latest_snapshot_event"

3492 AND "snapshot"."member_id" = "opinion"."member_id"

3493 WHERE "issue"."id" = "issue_id_p"

3494 AND "opinion"."suggestion_id" = "suggestion_id_v"

3495 AND "opinion"."degree" = -1

3496 AND "opinion"."fulfilled" = TRUE

3497 ),

3498 "plus1_unfulfilled_count" = (

3499 SELECT coalesce(sum("snapshot"."weight"), 0)

3500 FROM "issue" CROSS JOIN "opinion"

3501 JOIN "direct_interest_snapshot" AS "snapshot"

3502 ON "snapshot"."issue_id" = "issue"."id"

3503 AND "snapshot"."event" = "issue"."latest_snapshot_event"

3504 AND "snapshot"."member_id" = "opinion"."member_id"

3505 WHERE "issue"."id" = "issue_id_p"

3506 AND "opinion"."suggestion_id" = "suggestion_id_v"

3507 AND "opinion"."degree" = 1

3508 AND "opinion"."fulfilled" = FALSE

3509 ),

3510 "plus1_fulfilled_count" = (

3511 SELECT coalesce(sum("snapshot"."weight"), 0)

3512 FROM "issue" CROSS JOIN "opinion"

3513 JOIN "direct_interest_snapshot" AS "snapshot"

3514 ON "snapshot"."issue_id" = "issue"."id"

3515 AND "snapshot"."event" = "issue"."latest_snapshot_event"

3516 AND "snapshot"."member_id" = "opinion"."member_id"

3517 WHERE "issue"."id" = "issue_id_p"

3518 AND "opinion"."suggestion_id" = "suggestion_id_v"

3519 AND "opinion"."degree" = 1

3520 AND "opinion"."fulfilled" = TRUE

3521 ),

3522 "plus2_unfulfilled_count" = (

3523 SELECT coalesce(sum("snapshot"."weight"), 0)

3524 FROM "issue" CROSS JOIN "opinion"

3525 JOIN "direct_interest_snapshot" AS "snapshot"

3526 ON "snapshot"."issue_id" = "issue"."id"

3527 AND "snapshot"."event" = "issue"."latest_snapshot_event"

3528 AND "snapshot"."member_id" = "opinion"."member_id"

3529 WHERE "issue"."id" = "issue_id_p"

3530 AND "opinion"."suggestion_id" = "suggestion_id_v"

3531 AND "opinion"."degree" = 2

3532 AND "opinion"."fulfilled" = FALSE

3533 ),

3534 "plus2_fulfilled_count" = (

3535 SELECT coalesce(sum("snapshot"."weight"), 0)

3536 FROM "issue" CROSS JOIN "opinion"

3537 JOIN "direct_interest_snapshot" AS "snapshot"

3538 ON "snapshot"."issue_id" = "issue"."id"

3539 AND "snapshot"."event" = "issue"."latest_snapshot_event"

3540 AND "snapshot"."member_id" = "opinion"."member_id"

3541 WHERE "issue"."id" = "issue_id_p"

3542 AND "opinion"."suggestion_id" = "suggestion_id_v"

3543 AND "opinion"."degree" = 2

3544 AND "opinion"."fulfilled" = TRUE

3545 )

3546 WHERE "suggestion"."id" = "suggestion_id_v";

3547 END LOOP;

3548 END LOOP;

3549 RETURN;

3550 END;

3551 $$;

3552

3553 COMMENT ON FUNCTION "create_snapshot"

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

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

3556

3557

3558 CREATE FUNCTION "set_snapshot_event"

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

3560 "event_p" "snapshot_event" )

3561 RETURNS VOID

3562 LANGUAGE 'plpgsql' VOLATILE AS $$

3563 DECLARE

3564 "event_v" "issue"."latest_snapshot_event"%TYPE;

3565 BEGIN

3566 PERFORM "require_transaction_isolation"();

3567 SELECT "latest_snapshot_event" INTO "event_v" FROM "issue"

3568 WHERE "id" = "issue_id_p" FOR UPDATE;

3569 UPDATE "issue" SET "latest_snapshot_event" = "event_p"

3570 WHERE "id" = "issue_id_p";

3571 UPDATE "direct_population_snapshot" SET "event" = "event_p"

3572 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";

3573 UPDATE "delegating_population_snapshot" SET "event" = "event_p"

3574 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";

3575 UPDATE "direct_interest_snapshot" SET "event" = "event_p"

3576 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";

3577 UPDATE "delegating_interest_snapshot" SET "event" = "event_p"

3578 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";

3579 UPDATE "direct_supporter_snapshot" SET "event" = "event_p"

3580 FROM "initiative" -- NOTE: due to missing index on issue_id

3581 WHERE "initiative"."issue_id" = "issue_id_p"

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

3583 AND "direct_supporter_snapshot"."event" = "event_v";

3584 RETURN;

3585 END;

3586 $$;

3587

3588 COMMENT ON FUNCTION "set_snapshot_event"

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

3590 "snapshot_event" )

3591 IS 'Change "event" attribute of the previous ''periodic'' snapshot';

3592

3593

3594

3595 -----------------------

3596 -- Counting of votes --

3597 -----------------------

3598

3599

3600 CREATE FUNCTION "weight_of_added_vote_delegations"

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

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

3603 "delegate_member_ids_p" "delegating_voter"."delegate_member_ids"%TYPE )

3604 RETURNS "direct_voter"."weight"%TYPE

3605 LANGUAGE 'plpgsql' VOLATILE AS $$

3606 DECLARE

3607 "issue_delegation_row" "issue_delegation"%ROWTYPE;

3608 "delegate_member_ids_v" "delegating_voter"."delegate_member_ids"%TYPE;

3609 "weight_v" INT4;

3610 "sub_weight_v" INT4;

3611 BEGIN

3612 PERFORM "require_transaction_isolation"();

3613 "weight_v" := 0;

3614 FOR "issue_delegation_row" IN

3615 SELECT * FROM "issue_delegation"

3616 WHERE "trustee_id" = "member_id_p"

3617 AND "issue_id" = "issue_id_p"

3618 LOOP

3619 IF NOT EXISTS (

3620 SELECT NULL FROM "direct_voter"

3621 WHERE "member_id" = "issue_delegation_row"."truster_id"

3622 AND "issue_id" = "issue_id_p"

3623 ) AND NOT EXISTS (

3624 SELECT NULL FROM "delegating_voter"

3625 WHERE "member_id" = "issue_delegation_row"."truster_id"

3626 AND "issue_id" = "issue_id_p"

3627 ) THEN

3628 "delegate_member_ids_v" :=

3629 "member_id_p" || "delegate_member_ids_p";

3630 INSERT INTO "delegating_voter" (

3631 "issue_id",

3632 "member_id",

3633 "scope",

3634 "delegate_member_ids"

3635 ) VALUES (

3636 "issue_id_p",

3637 "issue_delegation_row"."truster_id",

3638 "issue_delegation_row"."scope",

3639 "delegate_member_ids_v"

3640 );

3641 "sub_weight_v" := 1 +

3642 "weight_of_added_vote_delegations"(

3643 "issue_id_p",

3644 "issue_delegation_row"."truster_id",

3645 "delegate_member_ids_v"

3646 );

3647 UPDATE "delegating_voter"

3648 SET "weight" = "sub_weight_v"

3649 WHERE "issue_id" = "issue_id_p"

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

3651 "weight_v" := "weight_v" + "sub_weight_v";

3652 END IF;

3653 END LOOP;

3654 RETURN "weight_v";

3655 END;

3656 $$;

3657

3658 COMMENT ON FUNCTION "weight_of_added_vote_delegations"

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

3660 "member"."id"%TYPE,

3661 "delegating_voter"."delegate_member_ids"%TYPE )

3662 IS 'Helper function for "add_vote_delegations" function';

3663

3664

3665 CREATE FUNCTION "add_vote_delegations"

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

3667 RETURNS VOID

3668 LANGUAGE 'plpgsql' VOLATILE AS $$

3669 DECLARE

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

3671 BEGIN

3672 PERFORM "require_transaction_isolation"();

3673 FOR "member_id_v" IN

3674 SELECT "member_id" FROM "direct_voter"

3675 WHERE "issue_id" = "issue_id_p"

3676 LOOP

3677 UPDATE "direct_voter" SET

3678 "weight" = "weight" + "weight_of_added_vote_delegations"(

3679 "issue_id_p",

3680 "member_id_v",

3681 '{}'

3682 )

3683 WHERE "member_id" = "member_id_v"

3684 AND "issue_id" = "issue_id_p";

3685 END LOOP;

3686 RETURN;

3687 END;

3688 $$;

3689

3690 COMMENT ON FUNCTION "add_vote_delegations"

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

3692 IS 'Helper function for "close_voting" function';

3693

3694

3695 CREATE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)

3696 RETURNS VOID

3697 LANGUAGE 'plpgsql' VOLATILE AS $$

3698 DECLARE

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

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

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

3702 BEGIN

3703 PERFORM "require_transaction_isolation"();

3704 SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";

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

3706 -- override protection triggers:

3707 INSERT INTO "temporary_transaction_data" ("key", "value")

3708 VALUES ('override_protection_triggers', TRUE::TEXT);

3709 -- delete timestamp of voting comment:

3710 UPDATE "direct_voter" SET "comment_changed" = NULL

3711 WHERE "issue_id" = "issue_id_p";

3712 -- delete delegating votes (in cases of manual reset of issue state):

3713 DELETE FROM "delegating_voter"

3714 WHERE "issue_id" = "issue_id_p";

3715 -- delete votes from non-privileged voters:

3716 DELETE FROM "direct_voter"

3717 USING (

3718 SELECT

3719 "direct_voter"."member_id"

3720 FROM "direct_voter"

3721 JOIN "member" ON "direct_voter"."member_id" = "member"."id"

3722 LEFT JOIN "privilege"

3723 ON "privilege"."unit_id" = "unit_id_v"

3724 AND "privilege"."member_id" = "direct_voter"."member_id"

3725 WHERE "direct_voter"."issue_id" = "issue_id_p" AND (

3726 "member"."active" = FALSE OR

3727 "privilege"."voting_right" ISNULL OR

3728 "privilege"."voting_right" = FALSE

3729 )

3730 ) AS "subquery"

3731 WHERE "direct_voter"."issue_id" = "issue_id_p"

3732 AND "direct_voter"."member_id" = "subquery"."member_id";

3733 -- consider delegations:

3734 UPDATE "direct_voter" SET "weight" = 1

3735 WHERE "issue_id" = "issue_id_p";

3736 PERFORM "add_vote_delegations"("issue_id_p");

3737 -- finish overriding protection triggers (avoids garbage):

3738 DELETE FROM "temporary_transaction_data"

3739 WHERE "key" = 'override_protection_triggers';

3740 -- materialize battle_view:

3741 -- NOTE: "closed" column of issue must be set at this point

3742 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";

3743 INSERT INTO "battle" (

3744 "issue_id",

3745 "winning_initiative_id", "losing_initiative_id",

3746 "count"

3747 ) SELECT

3748 "issue_id",

3749 "winning_initiative_id", "losing_initiative_id",

3750 "count"

3751 FROM "battle_view" WHERE "issue_id" = "issue_id_p";

3752 -- set voter count:

3753 UPDATE "issue" SET

3754 "voter_count" = (

3755 SELECT coalesce(sum("weight"), 0)

3756 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"

3757 )

3758 WHERE "id" = "issue_id_p";

3759 -- copy "positive_votes" and "negative_votes" from "battle" table:

3760 UPDATE "initiative" SET

3761 "positive_votes" = "battle_win"."count",

3762 "negative_votes" = "battle_lose"."count"

3763 FROM "battle" AS "battle_win", "battle" AS "battle_lose"

3764 WHERE

3765 "battle_win"."issue_id" = "issue_id_p" AND

3766 "battle_win"."winning_initiative_id" = "initiative"."id" AND

3767 "battle_win"."losing_initiative_id" ISNULL AND

3768 "battle_lose"."issue_id" = "issue_id_p" AND

3769 "battle_lose"."losing_initiative_id" = "initiative"."id" AND

3770 "battle_lose"."winning_initiative_id" ISNULL;

3771 END;

3772 $$;

3773

3774 COMMENT ON FUNCTION "close_voting"

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

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

3777

3778

3779 CREATE FUNCTION "defeat_strength"

3780 ( "positive_votes_p" INT4, "negative_votes_p" INT4 )

3781 RETURNS INT8

3782 LANGUAGE 'plpgsql' IMMUTABLE AS $$

3783 BEGIN

3784 IF "positive_votes_p" > "negative_votes_p" THEN

3785 RETURN ("positive_votes_p"::INT8 << 31) - "negative_votes_p"::INT8;

3786 ELSIF "positive_votes_p" = "negative_votes_p" THEN

3787 RETURN 0;

3788 ELSE

3789 RETURN -1;

3790 END IF;

3791 END;

3792 $$;

3793

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

3795

3796

3797 CREATE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE)

3798 RETURNS VOID

3799 LANGUAGE 'plpgsql' VOLATILE AS $$

3800 DECLARE

3801 "issue_row" "issue"%ROWTYPE;

3802 "policy_row" "policy"%ROWTYPE;

3803 "dimension_v" INTEGER;

3804 "vote_matrix" INT4[][]; -- absolute votes

3805 "matrix" INT8[][]; -- defeat strength / best paths

3806 "i" INTEGER;

3807 "j" INTEGER;

3808 "k" INTEGER;

3809 "battle_row" "battle"%ROWTYPE;

3810 "rank_ary" INT4[];

3811 "rank_v" INT4;

3812 "done_v" INTEGER;

3813 "winners_ary" INTEGER[];

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 LAST,

3832 "losing_initiative_id" NULLS LAST

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 "done_v" := 0;

3900 LOOP

3901 "winners_ary" := '{}';

3902 "i" := 1;

3903 LOOP

3904 IF "rank_ary"["i"] ISNULL THEN

3905 "j" := 1;

3906 LOOP

3907 IF

3908 "i" != "j" AND

3909 "rank_ary"["j"] ISNULL AND

3910 "matrix"["j"]["i"] > "matrix"["i"]["j"]

3911 THEN

3912 -- someone else is better

3913 EXIT;

3914 END IF;

3915 IF "j" = "dimension_v" THEN

3916 -- noone is better

3917 "winners_ary" := "winners_ary" || "i";

3918 EXIT;

3919 END IF;

3920 "j" := "j" + 1;

3921 END LOOP;

3922 END IF;

3923 EXIT WHEN "i" = "dimension_v";

3924 "i" := "i" + 1;

3925 END LOOP;

3926 "i" := 1;

3927 LOOP

3928 "rank_ary"["winners_ary"["i"]] := "rank_v";

3929 "done_v" := "done_v" + 1;

3930 EXIT WHEN "i" = array_upper("winners_ary", 1);

3931 "i" := "i" + 1;

3932 END LOOP;

3933 EXIT WHEN "done_v" = "dimension_v";

3934 "rank_v" := "rank_v" + 1;

3935 END LOOP;

3936 -- write preliminary results:

3937 "i" := 1;

3938 FOR "initiative_id_v" IN

3939 SELECT "id" FROM "initiative"

3940 WHERE "issue_id" = "issue_id_p" AND "admitted"

3941 ORDER BY "id"

3942 LOOP

3943 UPDATE "initiative" SET

3944 "direct_majority" =

3945 CASE WHEN "policy_row"."direct_majority_strict" THEN

3946 "positive_votes" * "policy_row"."direct_majority_den" >

3947 "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")

3948 ELSE

3949 "positive_votes" * "policy_row"."direct_majority_den" >=

3950 "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")

3951 END

3952 AND "positive_votes" >= "policy_row"."direct_majority_positive"

3953 AND "issue_row"."voter_count"-"negative_votes" >=

3954 "policy_row"."direct_majority_non_negative",

3955 "indirect_majority" =

3956 CASE WHEN "policy_row"."indirect_majority_strict" THEN

3957 "positive_votes" * "policy_row"."indirect_majority_den" >

3958 "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")

3959 ELSE

3960 "positive_votes" * "policy_row"."indirect_majority_den" >=

3961 "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")

3962 END

3963 AND "positive_votes" >= "policy_row"."indirect_majority_positive"

3964 AND "issue_row"."voter_count"-"negative_votes" >=

3965 "policy_row"."indirect_majority_non_negative",

3966 "schulze_rank" = "rank_ary"["i"],

3967 "better_than_status_quo" = "rank_ary"["i"] < "rank_ary"["dimension_v"],

3968 "worse_than_status_quo" = "rank_ary"["i"] > "rank_ary"["dimension_v"],

3969 "multistage_majority" = "rank_ary"["i"] >= "rank_ary"["dimension_v"],

3970 "reverse_beat_path" = "matrix"["dimension_v"]["i"] >= 0,

3971 "eligible" = FALSE,

3972 "winner" = FALSE,

3973 "rank" = NULL -- NOTE: in cases of manual reset of issue state

3974 WHERE "id" = "initiative_id_v";

3975 "i" := "i" + 1;

3976 END LOOP;

3977 IF "i" != "dimension_v" THEN

3978 RAISE EXCEPTION 'Wrong winner count (should not happen)';

3979 END IF;

3980 -- take indirect majorities into account:

3981 LOOP

3982 UPDATE "initiative" SET "indirect_majority" = TRUE

3983 FROM (

3984 SELECT "new_initiative"."id" AS "initiative_id"

3985 FROM "initiative" "old_initiative"

3986 JOIN "initiative" "new_initiative"

3987 ON "new_initiative"."issue_id" = "issue_id_p"

3988 AND "new_initiative"."indirect_majority" = FALSE

3989 JOIN "battle" "battle_win"

3990 ON "battle_win"."issue_id" = "issue_id_p"

3991 AND "battle_win"."winning_initiative_id" = "new_initiative"."id"

3992 AND "battle_win"."losing_initiative_id" = "old_initiative"."id"

3993 JOIN "battle" "battle_lose"

3994 ON "battle_lose"."issue_id" = "issue_id_p"

3995 AND "battle_lose"."losing_initiative_id" = "new_initiative"."id"

3996 AND "battle_lose"."winning_initiative_id" = "old_initiative"."id"

3997 WHERE "old_initiative"."issue_id" = "issue_id_p"

3998 AND "old_initiative"."indirect_majority" = TRUE

3999 AND CASE WHEN "policy_row"."indirect_majority_strict" THEN

4000 "battle_win"."count" * "policy_row"."indirect_majority_den" >

4001 "policy_row"."indirect_majority_num" *

4002 ("battle_win"."count"+"battle_lose"."count")

4003 ELSE

4004 "battle_win"."count" * "policy_row"."indirect_majority_den" >=

4005 "policy_row"."indirect_majority_num" *

4006 ("battle_win"."count"+"battle_lose"."count")

4007 END

4008 AND "battle_win"."count" >= "policy_row"."indirect_majority_positive"

4009 AND "issue_row"."voter_count"-"battle_lose"."count" >=

4010 "policy_row"."indirect_majority_non_negative"

4011 ) AS "subquery"

4012 WHERE "id" = "subquery"."initiative_id";

4013 EXIT WHEN NOT FOUND;

4014 END LOOP;

4015 -- set "multistage_majority" for remaining matching initiatives:

4016 UPDATE "initiative" SET "multistage_majority" = TRUE

4017 FROM (

4018 SELECT "losing_initiative"."id" AS "initiative_id"

4019 FROM "initiative" "losing_initiative"

4020 JOIN "initiative" "winning_initiative"

4021 ON "winning_initiative"."issue_id" = "issue_id_p"

4022 AND "winning_initiative"."admitted"

4023 JOIN "battle" "battle_win"

4024 ON "battle_win"."issue_id" = "issue_id_p"

4025 AND "battle_win"."winning_initiative_id" = "winning_initiative"."id"

4026 AND "battle_win"."losing_initiative_id" = "losing_initiative"."id"

4027 JOIN "battle" "battle_lose"

4028 ON "battle_lose"."issue_id" = "issue_id_p"

4029 AND "battle_lose"."losing_initiative_id" = "winning_initiative"."id"

4030 AND "battle_lose"."winning_initiative_id" = "losing_initiative"."id"

4031 WHERE "losing_initiative"."issue_id" = "issue_id_p"

4032 AND "losing_initiative"."admitted"

4033 AND "winning_initiative"."schulze_rank" <

4034 "losing_initiative"."schulze_rank"

4035 AND "battle_win"."count" > "battle_lose"."count"

4036 AND (

4037 "battle_win"."count" > "winning_initiative"."positive_votes" OR

4038 "battle_lose"."count" < "losing_initiative"."negative_votes" )

4039 ) AS "subquery"

4040 WHERE "id" = "subquery"."initiative_id";

4041 -- mark eligible initiatives:

4042 UPDATE "initiative" SET "eligible" = TRUE

4043 WHERE "issue_id" = "issue_id_p"

4044 AND "initiative"."direct_majority"

4045 AND "initiative"."indirect_majority"

4046 AND "initiative"."better_than_status_quo"

4047 AND (

4048 "policy_row"."no_multistage_majority" = FALSE OR

4049 "initiative"."multistage_majority" = FALSE )

4050 AND (

4051 "policy_row"."no_reverse_beat_path" = FALSE OR

4052 "initiative"."reverse_beat_path" = FALSE );

4053 -- mark final winner:

4054 UPDATE "initiative" SET "winner" = TRUE

4055 FROM (

4056 SELECT "id" AS "initiative_id"

4057 FROM "initiative"

4058 WHERE "issue_id" = "issue_id_p" AND "eligible"

4059 ORDER BY

4060 "schulze_rank",

4061 "id"

4062 LIMIT 1

4063 ) AS "subquery"

4064 WHERE "id" = "subquery"."initiative_id";

4065 -- write (final) ranks:

4066 "rank_v" := 1;

4067 FOR "initiative_id_v" IN

4068 SELECT "id"

4069 FROM "initiative"

4070 WHERE "issue_id" = "issue_id_p" AND "admitted"

4071 ORDER BY

4072 "winner" DESC,

4073 "eligible" DESC,

4074 "schulze_rank",

4075 "id"

4076 LOOP

4077 UPDATE "initiative" SET "rank" = "rank_v"

4078 WHERE "id" = "initiative_id_v";

4079 "rank_v" := "rank_v" + 1;

4080 END LOOP;

4081 -- set schulze rank of status quo and mark issue as finished:

4082 UPDATE "issue" SET

4083 "status_quo_schulze_rank" = "rank_ary"["dimension_v"],

4084 "state" =

4085 CASE WHEN EXISTS (

4086 SELECT NULL FROM "initiative"

4087 WHERE "issue_id" = "issue_id_p" AND "winner"

4088 ) THEN

4089 'finished_with_winner'::"issue_state"

4090 ELSE

4091 'finished_without_winner'::"issue_state"

4092 END,

4093 "closed" = "phase_finished",

4094 "phase_finished" = NULL

4095 WHERE "id" = "issue_id_p";

4096 RETURN;

4097 END;

4098 $$;

4099

4100 COMMENT ON FUNCTION "calculate_ranks"

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

4102 IS 'Determine ranking (Votes have to be counted first)';

4103

4104

4105

4106 -----------------------------

4107 -- Automatic state changes --

4108 -----------------------------

4109

4110

4111 CREATE TYPE "check_issue_persistence" AS (

4112 "state" "issue_state",

4113 "phase_finished" BOOLEAN,

4114 "issue_revoked" BOOLEAN,

4115 "snapshot_created" BOOLEAN,

4116 "harmonic_weights_set" BOOLEAN,

4117 "closed_voting" BOOLEAN );

4118

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

4120

4121

4122 CREATE FUNCTION "check_issue"

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

4124 "persist" "check_issue_persistence" )

4125 RETURNS "check_issue_persistence"

4126 LANGUAGE 'plpgsql' VOLATILE AS $$

4127 DECLARE

4128 "issue_row" "issue"%ROWTYPE;

4129 "policy_row" "policy"%ROWTYPE;

4130 "initiative_row" "initiative"%ROWTYPE;

4131 "state_v" "issue_state";

4132 BEGIN

4133 PERFORM "require_transaction_isolation"();

4134 IF "persist" ISNULL THEN

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

4136 FOR UPDATE;

4137 IF "issue_row"."closed" NOTNULL THEN

4138 RETURN NULL;

4139 END IF;

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

4141 IF

4142 ( "issue_row"."state" = 'admission' AND now() >=

4143 "issue_row"."created" + "issue_row"."admission_time" ) OR

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

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

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

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

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

4149 "issue_row"."fully_frozen" + "issue_row"."voting_time" )

4150 THEN

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

4152 ELSE

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

4154 END IF;

4155 IF

4156 NOT EXISTS (

4157 -- all initiatives are revoked

4158 SELECT NULL FROM "initiative"

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

4160 ) AND (

4161 -- and issue has not been accepted yet

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

4163 -- or verification time has elapsed

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

4165 "persist"."phase_finished" ) OR

4166 -- or no initiatives have been revoked lately

4167 NOT EXISTS (

4168 SELECT NULL FROM "initiative"

4169 WHERE "issue_id" = "issue_id_p"

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

4171 )

4172 )

4173 THEN

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

4175 ELSE

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

4177 END IF;

4178 IF "persist"."phase_finished" OR "persist"."issue_revoked" THEN

4179 UPDATE "issue" SET "phase_finished" = now()

4180 WHERE "id" = "issue_row"."id";

4181 RETURN "persist";

4182 ELSIF

4183 "persist"."state" IN ('admission', 'discussion', 'verification')

4184 THEN

4185 RETURN "persist";

4186 ELSE

4187 RETURN NULL;

4188 END IF;

4189 END IF;

4190 IF

4191 "persist"."state" IN ('admission', 'discussion', 'verification') AND

4192 coalesce("persist"."snapshot_created", FALSE) = FALSE

4193 THEN

4194 PERFORM "create_snapshot"("issue_id_p");

4195 "persist"."snapshot_created" = TRUE;

4196 IF "persist"."phase_finished" THEN

4197 IF "persist"."state" = 'admission' THEN

4198 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');

4199 ELSIF "persist"."state" = 'discussion' THEN

4200 PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze');

4201 ELSIF "persist"."state" = 'verification' THEN

4202 PERFORM "set_snapshot_event"("issue_id_p", 'full_freeze');

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

4204 SELECT * INTO "policy_row" FROM "policy"

4205 WHERE "id" = "issue_row"."policy_id";

4206 FOR "initiative_row" IN

4207 SELECT * FROM "initiative"

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

4209 FOR UPDATE

4210 LOOP

4211 IF

4212 "initiative_row"."polling" OR (

4213 "initiative_row"."satisfied_supporter_count" > 0 AND

4214 "initiative_row"."satisfied_supporter_count" *

4215 "policy_row"."initiative_quorum_den" >=

4216 "issue_row"."population" * "policy_row"."initiative_quorum_num"

4217 )

4218 THEN

4219 UPDATE "initiative" SET "admitted" = TRUE

4220 WHERE "id" = "initiative_row"."id";

4221 ELSE

4222 UPDATE "initiative" SET "admitted" = FALSE

4223 WHERE "id" = "initiative_row"."id";

4224 END IF;

4225 END LOOP;

4226 END IF;

4227 END IF;

4228 RETURN "persist";

4229 END IF;

4230 IF

4231 "persist"."state" IN ('admission', 'discussion', 'verification') AND

4232 coalesce("persist"."harmonic_weights_set", FALSE) = FALSE

4233 THEN

4234 PERFORM "set_harmonic_initiative_weights"("issue_id_p");

4235 "persist"."harmonic_weights_set" = TRUE;

4236 IF

4237 "persist"."phase_finished" OR

4238 "persist"."issue_revoked" OR

4239 "persist"."state" = 'admission'

4240 THEN

4241 RETURN "persist";

4242 ELSE

4243 RETURN NULL;

4244 END IF;

4245 END IF;

4246 IF "persist"."issue_revoked" THEN

4247 IF "persist"."state" = 'admission' THEN

4248 "state_v" := 'canceled_revoked_before_accepted';

4249 ELSIF "persist"."state" = 'discussion' THEN

4250 "state_v" := 'canceled_after_revocation_during_discussion';

4251 ELSIF "persist"."state" = 'verification' THEN

4252 "state_v" := 'canceled_after_revocation_during_verification';

4253 END IF;

4254 UPDATE "issue" SET

4255 "state" = "state_v",

4256 "closed" = "phase_finished",

4257 "phase_finished" = NULL

4258 WHERE "id" = "issue_id_p";

4259 RETURN NULL;

4260 END IF;

4261 IF "persist"."state" = 'admission' THEN

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

4263 FOR UPDATE;

4264 SELECT * INTO "policy_row"

4265 FROM "policy" WHERE "id" = "issue_row"."policy_id";

4266 IF EXISTS (

4267 SELECT NULL FROM "initiative"

4268 WHERE "issue_id" = "issue_id_p"

4269 AND "supporter_count" > 0

4270 AND "supporter_count" * "policy_row"."issue_quorum_den"

4271 >= "issue_row"."population" * "policy_row"."issue_quorum_num"

4272 ) THEN

4273 UPDATE "issue" SET

4274 "state" = 'discussion',

4275 "accepted" = coalesce("phase_finished", now()),

4276 "phase_finished" = NULL

4277 WHERE "id" = "issue_id_p";

4278 ELSIF "issue_row"."phase_finished" NOTNULL THEN

4279 UPDATE "issue" SET

4280 "state" = 'canceled_issue_not_accepted',

4281 "closed" = "phase_finished",

4282 "phase_finished" = NULL

4283 WHERE "id" = "issue_id_p";

4284 END IF;

4285 RETURN NULL;

4286 END IF;

4287 IF "persist"."phase_finished" THEN

4288 if "persist"."state" = 'discussion' THEN

4289 UPDATE "issue" SET

4290 "state" = 'verification',

4291 "half_frozen" = "phase_finished",

4292 "phase_finished" = NULL

4293 WHERE "id" = "issue_id_p";

4294 RETURN NULL;

4295 END IF;

4296 IF "persist"."state" = 'verification' THEN

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

4298 FOR UPDATE;

4299 SELECT * INTO "policy_row" FROM "policy"

4300 WHERE "id" = "issue_row"."policy_id";

4301 IF EXISTS (

4302 SELECT NULL FROM "initiative"

4303 WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE

4304 ) THEN

4305 UPDATE "issue" SET

4306 "state" = 'voting',

4307 "fully_frozen" = "phase_finished",

4308 "phase_finished" = NULL

4309 WHERE "id" = "issue_id_p";

4310 ELSE

4311 UPDATE "issue" SET

4312 "state" = 'canceled_no_initiative_admitted',

4313 "fully_frozen" = "phase_finished",

4314 "closed" = "phase_finished",

4315 "phase_finished" = NULL

4316 WHERE "id" = "issue_id_p";

4317 -- NOTE: The following DELETE statements have effect only when

4318 -- issue state has been manipulated

4319 DELETE FROM "direct_voter" WHERE "issue_id" = "issue_id_p";

4320 DELETE FROM "delegating_voter" WHERE "issue_id" = "issue_id_p";

4321 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";

4322 END IF;

4323 RETURN NULL;

4324 END IF;

4325 IF "persist"."state" = 'voting' THEN

4326 IF coalesce("persist"."closed_voting", FALSE) = FALSE THEN

4327 PERFORM "close_voting"("issue_id_p");

4328 "persist"."closed_voting" = TRUE;

4329 RETURN "persist";

4330 END IF;

4331 PERFORM "calculate_ranks"("issue_id_p");

4332 RETURN NULL;

4333 END IF;

4334 END IF;

4335 RAISE WARNING 'should not happen';

4336 RETURN NULL;

4337 END;

4338 $$;

4339

4340 COMMENT ON FUNCTION "check_issue"

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

4342 "check_issue_persistence" )

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

4344

4345

4346 CREATE FUNCTION "check_everything"()

4347 RETURNS VOID

4348 LANGUAGE 'plpgsql' VOLATILE AS $$

4349 DECLARE

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

4351 "persist_v" "check_issue_persistence";

4352 BEGIN

4353 RAISE WARNING 'Function "check_everything" should only be used for development and debugging purposes';

4354 DELETE FROM "expired_session";

4355 PERFORM "check_activity"();

4356 PERFORM "calculate_member_counts"();

4357 FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP

4358 "persist_v" := NULL;

4359 LOOP

4360 "persist_v" := "check_issue"("issue_id_v", "persist_v");

4361 EXIT WHEN "persist_v" ISNULL;

4362 END LOOP;

4363 END LOOP;

4364 RETURN;

4365 END;

4366 $$;

4367

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

4369

4370

4371

4372 ----------------------

4373 -- Deletion of data --

4374 ----------------------

4375

4376

4377 CREATE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE)

4378 RETURNS VOID

4379 LANGUAGE 'plpgsql' VOLATILE AS $$

4380 BEGIN

4381 IF EXISTS (

4382 SELECT NULL FROM "issue" WHERE "id" = "issue_id_p" AND "cleaned" ISNULL

4383 ) THEN

4384 -- override protection triggers:

4385 INSERT INTO "temporary_transaction_data" ("key", "value")

4386 VALUES ('override_protection_triggers', TRUE::TEXT);

4387 -- clean data:

4388 DELETE FROM "delegating_voter"

4389 WHERE "issue_id" = "issue_id_p";

4390 DELETE FROM "direct_voter"

4391 WHERE "issue_id" = "issue_id_p";

4392 DELETE FROM "delegating_interest_snapshot"

4393 WHERE "issue_id" = "issue_id_p";

4394 DELETE FROM "direct_interest_snapshot"

4395 WHERE "issue_id" = "issue_id_p";

4396 DELETE FROM "delegating_population_snapshot"

4397 WHERE "issue_id" = "issue_id_p";

4398 DELETE FROM "direct_population_snapshot"

4399 WHERE "issue_id" = "issue_id_p";

4400 DELETE FROM "non_voter"

4401 WHERE "issue_id" = "issue_id_p";

4402 DELETE FROM "delegation"

4403 WHERE "issue_id" = "issue_id_p";

4404 DELETE FROM "supporter"

4405 USING "initiative" -- NOTE: due to missing index on issue_id

4406 WHERE "initiative"."issue_id" = "issue_id_p"

4407 AND "supporter"."initiative_id" = "initiative_id";

4408 -- mark issue as cleaned:

4409 UPDATE "issue" SET "cleaned" = now() WHERE "id" = "issue_id_p";

4410 -- finish overriding protection triggers (avoids garbage):

4411 DELETE FROM "temporary_transaction_data"

4412 WHERE "key" = 'override_protection_triggers';

4413 END IF;

4414 RETURN;

4415 END;

4416 $$;

4417

4418 COMMENT ON FUNCTION "clean_issue"("issue"."id"%TYPE) IS 'Delete discussion data and votes belonging to an issue';

4419

4420

4421 CREATE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)

4422 RETURNS VOID

4423 LANGUAGE 'plpgsql' VOLATILE AS $$

4424 BEGIN

4425 UPDATE "member" SET

4426 "last_login" = NULL,

4427 "last_delegation_check" = NULL,

4428 "login" = NULL,

4429 "password" = NULL,

4430 "locked" = TRUE,

4431 "active" = FALSE,

4432 "notify_email" = NULL,

4433 "notify_email_unconfirmed" = NULL,

4434 "notify_email_secret" = NULL,

4435 "notify_email_secret_expiry" = NULL,

4436 "notify_email_lock_expiry" = NULL,

4437 "login_recovery_expiry" = NULL,

4438 "password_reset_secret" = NULL,

4439 "password_reset_secret_expiry" = NULL,

4440 "organizational_unit" = NULL,

4441 "internal_posts" = NULL,

4442 "realname" = NULL,

4443 "birthday" = NULL,

4444 "address" = NULL,

4445 "email" = NULL,

4446 "xmpp_address" = NULL,

4447 "website" = NULL,

4448 "phone" = NULL,

4449 "mobile_phone" = NULL,

4450 "profession" = NULL,

4451 "external_memberships" = NULL,

4452 "external_posts" = NULL,

4453 "statement" = NULL

4454 WHERE "id" = "member_id_p";

4455 -- "text_search_data" is updated by triggers

4456 DELETE FROM "setting" WHERE "member_id" = "member_id_p";

4457 DELETE FROM "setting_map" WHERE "member_id" = "member_id_p";

4458 DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p";

4459 DELETE FROM "member_image" WHERE "member_id" = "member_id_p";

4460 DELETE FROM "contact" WHERE "member_id" = "member_id_p";

4461 DELETE FROM "ignored_member" WHERE "member_id" = "member_id_p";

4462 DELETE FROM "session" WHERE "member_id" = "member_id_p";

4463 DELETE FROM "area_setting" WHERE "member_id" = "member_id_p";

4464 DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p";

4465 DELETE FROM "ignored_initiative" WHERE "member_id" = "member_id_p";

4466 DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p";

4467 DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p";

4468 DELETE FROM "membership" WHERE "member_id" = "member_id_p";

4469 DELETE FROM "delegation" WHERE "truster_id" = "member_id_p";

4470 DELETE FROM "non_voter" WHERE "member_id" = "member_id_p";

4471 DELETE FROM "direct_voter" USING "issue"

4472 WHERE "direct_voter"."issue_id" = "issue"."id"

4473 AND "issue"."closed" ISNULL

4474 AND "member_id" = "member_id_p";

4475 RETURN;

4476 END;

4477 $$;

4478

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

4480

4481

4482 CREATE FUNCTION "delete_private_data"()

4483 RETURNS VOID

4484 LANGUAGE 'plpgsql' VOLATILE AS $$

4485 BEGIN

4486 DELETE FROM "temporary_transaction_data";

4487 DELETE FROM "member" WHERE "activated" ISNULL;

4488 UPDATE "member" SET

4489 "invite_code" = NULL,

4490 "invite_code_expiry" = NULL,

4491 "admin_comment" = NULL,

4492 "last_login" = NULL,

4493 "last_delegation_check" = NULL,

4494 "login" = NULL,

4495 "password" = NULL,

4496 "lang" = NULL,

4497 "notify_email" = NULL,

4498 "notify_email_unconfirmed" = NULL,

4499 "notify_email_secret" = NULL,

4500 "notify_email_secret_expiry" = NULL,

4501 "notify_email_lock_expiry" = NULL,

4502 "notify_level" = NULL,

4503 "login_recovery_expiry" = NULL,

4504 "password_reset_secret" = NULL,

4505 "password_reset_secret_expiry" = NULL,

4506 "organizational_unit" = NULL,

4507 "internal_posts" = NULL,

4508 "realname" = NULL,

4509 "birthday" = NULL,

4510 "address" = NULL,

4511 "email" = NULL,

4512 "xmpp_address" = NULL,

4513 "website" = NULL,

4514 "phone" = NULL,

4515 "mobile_phone" = NULL,

4516 "profession" = NULL,

4517 "external_memberships" = NULL,

4518 "external_posts" = NULL,

4519 "formatting_engine" = NULL,

4520 "statement" = NULL;

4521 -- "text_search_data" is updated by triggers

4522 DELETE FROM "setting";

4523 DELETE FROM "setting_map";

4524 DELETE FROM "member_relation_setting";

4525 DELETE FROM "member_image";

4526 DELETE FROM "contact";

4527 DELETE FROM "ignored_member";

4528 DELETE FROM "session";

4529 DELETE FROM "area_setting";

4530 DELETE FROM "issue_setting";

4531 DELETE FROM "ignored_initiative";

4532 DELETE FROM "initiative_setting";

4533 DELETE FROM "suggestion_setting";

4534 DELETE FROM "non_voter";

4535 DELETE FROM "direct_voter" USING "issue"

4536 WHERE "direct_voter"."issue_id" = "issue"."id"

4537 AND "issue"."closed" ISNULL;

4538 RETURN;

4539 END;

4540 $$;

4541

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

4543

4544

4545

4546 COMMIT;