liquid_feedback_core: b1360dc846be core.sql

liquid_feedback_core

view core.sql @ 276:b1360dc846be

Added field "code_grant_multiple" to "api_client"

author	jbe
date	Fri Aug 10 15:00:07 2012 +0200 (2012-08-10)
parents	fd496b372780
children	028a52d23e56

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.1.0', 2, 1, 0))

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

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

16 -- Full text search --

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

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

21 RETURNS TSQUERY

22 LANGUAGE 'plpgsql' IMMUTABLE AS $$

23 BEGIN

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

25 END;

26 $$;

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

31 CREATE FUNCTION "highlight"

32 ( "body_p" TEXT,

33 "query_text_p" TEXT )

34 RETURNS TEXT

35 LANGUAGE 'plpgsql' IMMUTABLE AS $$

36 BEGIN

37 RETURN ts_headline(

38 'pg_catalog.simple',

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

40 "text_search_query"("query_text_p"),

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

42 END;

43 $$;

45 COMMENT ON FUNCTION "highlight"

46 ( "body_p" TEXT,

47 "query_text_p" TEXT )

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

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

53 -- Tables and indicies --

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

57 CREATE TABLE "system_setting" (

58 "member_ttl" INTERVAL );

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

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

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

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

67 CREATE TABLE "contingent" (

68 "time_frame" INTERVAL PRIMARY KEY,

69 "text_entry_limit" INT4,

70 "initiative_limit" INT4 );

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

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

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

78 CREATE TYPE "notify_level" AS ENUM

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

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

84 CREATE TABLE "member" (

85 "id" SERIAL4 PRIMARY KEY,

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

87 "invite_code" TEXT UNIQUE,

88 "invite_code_expiry" TIMESTAMPTZ,

89 "admin_comment" TEXT,

90 "activated" TIMESTAMPTZ,

91 "last_activity" DATE,

92 "last_login" TIMESTAMPTZ,

93 "login" TEXT UNIQUE,

94 "password" TEXT,

95 "locked" BOOLEAN NOT NULL DEFAULT FALSE,

96 "active" BOOLEAN NOT NULL DEFAULT FALSE,

97 "admin" BOOLEAN NOT NULL DEFAULT FALSE,

98 "lang" TEXT,

99 "notify_email" TEXT,

100 "notify_email_unconfirmed" TEXT,

101 "notify_email_secret" TEXT UNIQUE,

102 "notify_email_secret_expiry" TIMESTAMPTZ,

103 "notify_email_lock_expiry" TIMESTAMPTZ,

104 "notify_level" "notify_level",

105 "password_reset_secret" TEXT UNIQUE,

106 "password_reset_secret_expiry" TIMESTAMPTZ,

107 "name" TEXT UNIQUE,

108 "identification" TEXT UNIQUE,

109 "authentication" TEXT,

110 "organizational_unit" TEXT,

111 "internal_posts" TEXT,

112 "realname" TEXT,

113 "birthday" DATE,

114 "address" TEXT,

115 "email" TEXT,

116 "xmpp_address" TEXT,

117 "website" TEXT,

118 "phone" TEXT,

119 "mobile_phone" TEXT,

120 "profession" TEXT,

121 "external_memberships" TEXT,

122 "external_posts" TEXT,

123 "formatting_engine" TEXT,

124 "statement" TEXT,

125 "text_search_data" TSVECTOR,

126 CONSTRAINT "active_requires_activated_and_last_activity"

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

128 CONSTRAINT "name_not_null_if_activated"

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

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

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

132 CREATE TRIGGER "update_text_search_data"

133 BEFORE INSERT OR UPDATE ON "member"

134 FOR EACH ROW EXECUTE PROCEDURE

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

136 "name", "identification", "organizational_unit", "internal_posts",

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

138

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

140

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

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

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

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

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

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

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

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

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

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

151 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".';

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

171

172

173 CREATE TYPE "api_access_level" AS ENUM (

174 'none', 'anonymous', 'authors_pseudonymous', 'all_pseudonymous', 'everything', 'member' );

175

176

177 CREATE TABLE "api_client" (

178 "id" SERIAL8 PRIMARY KEY,

179 "name" TEXT,

180 UNIQUE ("member_id", "client_identifier"),

181 "member_id" INT4 REFERENCES "member" ("id")

182 ON DELETE CASCADE ON UPDATE CASCADE,

183 "client_identifier" TEXT NOT NULL,

184 "client_secret" TEXT,

185 "code_grant" BOOLEAN NOT NULL,

186 "implicit_grant" BOOLEAN NOT NULL,

187 "client_grant" BOOLEAN NOT NULL,

188 "code_grant_validity_period" INTERVAL,

189 "code_grant_multiple" BOOLEAN,

190 "access_level" "api_access_level",

191 "client_grant_access_level" "api_access_level",

192 "last_usage" TIMESTAMPTZ NOT NULL,

193 CONSTRAINT "system_clients_require_name"

194 CHECK ("name" NOTNULL OR "member_id" ISNULL),

195 CONSTRAINT "code_grant_requires_validity_period"

196 CHECK ("code_grant"=FALSE OR "code_grant_validity_period" NOTNULL),

197 CONSTRAINT "code_or_implicit_grant_requires_access_level"

198 CHECK (("code_grant"=FALSE AND "implicit_grant"=FALSE) OR "access_level" NOTNULL),

199 CONSTRAINT "client_grant_requires_client_grant_access_level"

200 CHECK ("client_grant"=FALSE OR "client_grant_access_level" NOTNULL) );

201 CREATE UNIQUE INDEX "api_client_non_member_client_identifier_idx"

202 ON "api_client" ("client_identifier") WHERE "member_id" ISNULL;

203

204 COMMENT ON TABLE "api_client" IS 'Registered OAuth2 client for a member';

205

206 COMMENT ON COLUMN "api_client"."name" IS 'Name of the client as chosen by member or administrator, NULL is allowed for unnamed member-registered clients';

207 COMMENT ON COLUMN "api_client"."member_id" IS 'Member, who registered the client for him/herself, or NULL for clients registered by administrator';

208 COMMENT ON COLUMN "api_client"."client_identifier" IS 'OAuth2 client id, also used as redirection endpoint if "code_grant" or "implicit_grant" is set to TRUE';

209 COMMENT ON COLUMN "api_client"."client_secret" IS 'Secret for client authentication';

210 COMMENT ON COLUMN "api_client"."code_grant" IS 'Enable OAuth2 Authorization Code Grant';

211 COMMENT ON COLUMN "api_client"."implicit_grant" IS 'Enable OAuth2 Implicit Grant';

212 COMMENT ON COLUMN "api_client"."client_grant" IS 'Enable OAuth2 Client Credentials Grant';

213 COMMENT ON COLUMN "api_client"."code_grant_validity_period" IS 'Maximum validity period of OAuth2 Authorization Code Grant, after which no more refresh is possible';

214 COMMENT ON COLUMN "api_client"."code_grant_multiple" IS 'If set to FALSE, invalidates previously issued OAuth2 Refresh Tokens during authorization; If set to NULL, the member is asked interactively during authorization, whether previously issued authorizations shall be revoked';

215 COMMENT ON COLUMN "api_client"."access_level" IS 'Maximum access level for OAuth2 Authorization Code Grant and Implicit Grant';

216 COMMENT ON COLUMN "api_client"."client_grant_access_level" IS 'Maximum access level for OAuth2 Authorization Code Grant and Implicit Grant';

217 COMMENT ON COLUMN "api_client"."last_usage" IS 'Date/time when this client registration was last used';

218

219

220 CREATE TABLE "api_code_grant" (

221 "id" SERIAL8 PRIMARY KEY,

222 "api_client_id" INT8 NOT NULL REFERENCES "api_client" ("id")

223 ON DELETE CASCADE ON UPDATE CASCADE,

224 "member_id" INT4 REFERENCES "member" ("id")

225 ON DELETE CASCADE ON UPDATE CASCADE,

226 "access_level" "api_access_level" NOT NULL,

227 "validity_period" INTERVAL NOT NULL,

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

229 "authorization_code" TEXT,

230 "refreshed" TIMESTAMPTZ,

231 "refresh_token" TEXT,

232 "old_refresh_token" TEXT,

233 CONSTRAINT "one_of_authorization_code_and_refresh_token_set"

234 CHECK ("authorization_code" NOTNULL OR "refresh_token" NOTNULL),

235 CONSTRAINT "refresh_token_if_and_only_if_refreshed"

236 CHECK ("refreshed" NOTNULL = "refresh_token" NOTNULL),

237 CONSTRAINT "old_refresh_token_requires_current_refresh_token"

238 CHECK ("refresh_token" NOTNULL OR "old_refresh_token" ISNULL) );

239

240 COMMENT ON TABLE "api_code_grant" IS 'Issued OAuth2 authorization codes and refresh tokens';

241

242 COMMENT ON COLUMN "api_code_grant"."validity_period" IS 'Period after which no more refreshing is possible';

243 COMMENT ON COLUMN "api_code_grant"."created" IS 'Date/time when authorization code (or first refresh token when there is no authorization code) has been created';

244 COMMENT ON COLUMN "api_code_grant"."authorization_code" IS 'OAuth2 authorization code (only valid for a very short time after it has been created)';

245 COMMENT ON COLUMN "api_code_grant"."refreshed" IS 'Date/time of last refresh';

246 COMMENT ON COLUMN "api_code_grant"."refresh_token" IS 'OAuth2 refresh token';

247

248

249 CREATE TABLE "member_history" (

250 "id" SERIAL8 PRIMARY KEY,

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

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

253 "active" BOOLEAN NOT NULL,

254 "name" TEXT NOT NULL );

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

256

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

258

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

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

261

262

263 CREATE TABLE "rendered_member_statement" (

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

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

266 "format" TEXT,

267 "content" TEXT NOT NULL );

268

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

270

271

272 CREATE TABLE "setting" (

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

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

275 "key" TEXT NOT NULL,

276 "value" TEXT NOT NULL );

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

278

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

280

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

282

283

284 CREATE TABLE "setting_map" (

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

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

287 "key" TEXT NOT NULL,

288 "subkey" TEXT NOT NULL,

289 "value" TEXT NOT NULL );

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

291

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

293

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

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

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

297

298

299 CREATE TABLE "member_relation_setting" (

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

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

302 "key" TEXT NOT NULL,

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

304 "value" TEXT NOT NULL );

305

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

307

308

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

310

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

312

313

314 CREATE TABLE "member_image" (

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

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

317 "image_type" "member_image_type",

318 "scaled" BOOLEAN,

319 "content_type" TEXT,

320 "data" BYTEA NOT NULL );

321

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

323

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

325

326

327 CREATE TABLE "member_count" (

328 "calculated" TIMESTAMPTZ NOT NULL DEFAULT NOW(),

329 "total_count" INT4 NOT NULL );

330

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

332

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

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

335

336

337 CREATE TABLE "contact" (

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

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

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

341 "public" BOOLEAN NOT NULL DEFAULT FALSE,

342 CONSTRAINT "cant_save_yourself_as_contact"

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

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

345

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

347

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

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

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

351

352

353 CREATE TABLE "ignored_member" (

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

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

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

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

358

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

360

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

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

363

364

365 CREATE TABLE "session" (

366 "ident" TEXT PRIMARY KEY,

367 "additional_secret" TEXT,

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

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

370 "lang" TEXT );

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

372

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

374

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

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

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

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

379

380

381 CREATE TABLE "policy" (

382 "id" SERIAL4 PRIMARY KEY,

383 "index" INT4 NOT NULL,

384 "active" BOOLEAN NOT NULL DEFAULT TRUE,

385 "name" TEXT NOT NULL UNIQUE,

386 "description" TEXT NOT NULL DEFAULT '',

387 "polling" BOOLEAN NOT NULL DEFAULT FALSE,

388 "admission_time" INTERVAL,

389 "discussion_time" INTERVAL,

390 "verification_time" INTERVAL,

391 "voting_time" INTERVAL,

392 "issue_quorum_num" INT4 NOT NULL,

393 "issue_quorum_den" INT4 NOT NULL,

394 "initiative_quorum_num" INT4 NOT NULL,

395 "initiative_quorum_den" INT4 NOT NULL,

396 "direct_majority_num" INT4 NOT NULL DEFAULT 1,

397 "direct_majority_den" INT4 NOT NULL DEFAULT 2,

398 "direct_majority_strict" BOOLEAN NOT NULL DEFAULT TRUE,

399 "direct_majority_positive" INT4 NOT NULL DEFAULT 0,

400 "direct_majority_non_negative" INT4 NOT NULL DEFAULT 0,

401 "indirect_majority_num" INT4 NOT NULL DEFAULT 1,

402 "indirect_majority_den" INT4 NOT NULL DEFAULT 2,

403 "indirect_majority_strict" BOOLEAN NOT NULL DEFAULT TRUE,

404 "indirect_majority_positive" INT4 NOT NULL DEFAULT 0,

405 "indirect_majority_non_negative" INT4 NOT NULL DEFAULT 0,

406 "no_reverse_beat_path" BOOLEAN NOT NULL DEFAULT TRUE,

407 "no_multistage_majority" BOOLEAN NOT NULL DEFAULT FALSE,

408 CONSTRAINT "timing" CHECK (

409 ( "polling" = FALSE AND

410 "admission_time" NOTNULL AND "discussion_time" NOTNULL AND

411 "verification_time" NOTNULL AND "voting_time" NOTNULL ) OR

412 ( "polling" = TRUE AND

413 "admission_time" ISNULL AND "discussion_time" NOTNULL AND

414 "verification_time" NOTNULL AND "voting_time" NOTNULL ) OR

415 ( "polling" = TRUE AND

416 "admission_time" ISNULL AND "discussion_time" ISNULL AND

417 "verification_time" ISNULL AND "voting_time" ISNULL ) ) );

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

419

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

421

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

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

424 COMMENT ON COLUMN "policy"."polling" IS 'TRUE = special policy for non-user-generated issues, i.e. polls ("admission_time" MUST be set to NULL, the other timings may be set to NULL altogether, allowing individual timing for issues)';

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

445

446

447 CREATE TABLE "unit" (

448 "id" SERIAL4 PRIMARY KEY,

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

450 "active" BOOLEAN NOT NULL DEFAULT TRUE,

451 "name" TEXT NOT NULL,

452 "description" TEXT NOT NULL DEFAULT '',

453 "member_count" INT4,

454 "text_search_data" TSVECTOR );

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

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

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

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

459 CREATE TRIGGER "update_text_search_data"

460 BEFORE INSERT OR UPDATE ON "unit"

461 FOR EACH ROW EXECUTE PROCEDURE

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

463 "name", "description" );

464

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

466

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

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

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

470

471

472 CREATE TABLE "unit_setting" (

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

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

475 "key" TEXT NOT NULL,

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

477 "value" TEXT NOT NULL );

478

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

480

481

482 CREATE TABLE "area" (

483 "id" SERIAL4 PRIMARY KEY,

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

485 "active" BOOLEAN NOT NULL DEFAULT TRUE,

486 "name" TEXT NOT NULL,

487 "description" TEXT NOT NULL DEFAULT '',

488 "direct_member_count" INT4,

489 "member_weight" INT4,

490 "text_search_data" TSVECTOR );

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

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

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

494 CREATE TRIGGER "update_text_search_data"

495 BEFORE INSERT OR UPDATE ON "area"

496 FOR EACH ROW EXECUTE PROCEDURE

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

498 "name", "description" );

499

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

501

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

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

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

505

506

507 CREATE TABLE "area_setting" (

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

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

510 "key" TEXT NOT NULL,

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

512 "value" TEXT NOT NULL );

513

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

515

516

517 CREATE TABLE "allowed_policy" (

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

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

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

521 "default_policy" BOOLEAN NOT NULL DEFAULT FALSE );

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

523

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

525

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

527

528

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

530

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

532

533

534 CREATE TYPE "issue_state" AS ENUM (

535 'admission', 'discussion', 'verification', 'voting',

536 'canceled_revoked_before_accepted',

537 'canceled_issue_not_accepted',

538 'canceled_after_revocation_during_discussion',

539 'canceled_after_revocation_during_verification',

540 'calculation',

541 'canceled_no_initiative_admitted',

542 'finished_without_winner', 'finished_with_winner');

543

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

545

546

547 CREATE TABLE "issue" (

548 "id" SERIAL4 PRIMARY KEY,

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

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

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

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

553 "accepted" TIMESTAMPTZ,

554 "half_frozen" TIMESTAMPTZ,

555 "fully_frozen" TIMESTAMPTZ,

556 "closed" TIMESTAMPTZ,

557 "ranks_available" BOOLEAN NOT NULL DEFAULT FALSE,

558 "cleaned" TIMESTAMPTZ,

559 "admission_time" INTERVAL NOT NULL,

560 "discussion_time" INTERVAL NOT NULL,

561 "verification_time" INTERVAL NOT NULL,

562 "voting_time" INTERVAL NOT NULL,

563 "snapshot" TIMESTAMPTZ,

564 "latest_snapshot_event" "snapshot_event",

565 "population" INT4,

566 "voter_count" INT4,

567 "status_quo_schulze_rank" INT4,

568 CONSTRAINT "valid_state" CHECK ((

569 ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR

570 ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR

571 ("accepted" NOTNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR

572 ("accepted" NOTNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR

573 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR

574 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR

575 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR

576 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR

577 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = TRUE)) AND (

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

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

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

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

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

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

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

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

586 ("state" = 'calculation' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = FALSE) OR

587 ("state" = 'canceled_no_initiative_admitted' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE) OR

588 ("state" = 'finished_without_winner' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE) OR

589 ("state" = 'finished_with_winner' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE)

590 )),

591 CONSTRAINT "state_change_order" CHECK (

592 "created" <= "accepted" AND

593 "accepted" <= "half_frozen" AND

594 "half_frozen" <= "fully_frozen" AND

595 "fully_frozen" <= "closed" ),

596 CONSTRAINT "only_closed_issues_may_be_cleaned" CHECK (

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

598 CONSTRAINT "last_snapshot_on_full_freeze"

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

600 CONSTRAINT "freeze_requires_snapshot"

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

602 CONSTRAINT "set_both_or_none_of_snapshot_and_latest_snapshot_event"

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

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

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

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

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

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

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

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

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

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

613

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

615

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

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

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

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

620 COMMENT ON COLUMN "issue"."ranks_available" IS 'TRUE = ranks have been calculated';

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

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

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

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

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

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

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

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

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

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

631

632

633 CREATE TABLE "issue_setting" (

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

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

636 "key" TEXT NOT NULL,

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

638 "value" TEXT NOT NULL );

639

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

641

642

643 CREATE TABLE "initiative" (

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

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

646 "id" SERIAL4 PRIMARY KEY,

647 "name" TEXT NOT NULL,

648 "polling" BOOLEAN NOT NULL DEFAULT FALSE,

649 "discussion_url" TEXT,

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

651 "revoked" TIMESTAMPTZ,

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

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

654 "admitted" BOOLEAN,

655 "supporter_count" INT4,

656 "informed_supporter_count" INT4,

657 "satisfied_supporter_count" INT4,

658 "satisfied_informed_supporter_count" INT4,

659 "positive_votes" INT4,

660 "negative_votes" INT4,

661 "direct_majority" BOOLEAN,

662 "indirect_majority" BOOLEAN,

663 "schulze_rank" INT4,

664 "better_than_status_quo" BOOLEAN,

665 "worse_than_status_quo" BOOLEAN,

666 "reverse_beat_path" BOOLEAN,

667 "multistage_majority" BOOLEAN,

668 "eligible" BOOLEAN,

669 "winner" BOOLEAN,

670 "rank" INT4,

671 "text_search_data" TSVECTOR,

672 CONSTRAINT "all_or_none_of_revoked_and_revoked_by_member_id_must_be_null"

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

674 CONSTRAINT "non_revoked_initiatives_cant_suggest_other"

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

676 CONSTRAINT "revoked_initiatives_cant_be_admitted"

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

678 CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results" CHECK (

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

680 ( "positive_votes" ISNULL AND "negative_votes" ISNULL AND

681 "direct_majority" ISNULL AND "indirect_majority" ISNULL AND

682 "schulze_rank" ISNULL AND

683 "better_than_status_quo" ISNULL AND "worse_than_status_quo" ISNULL AND

684 "reverse_beat_path" ISNULL AND "multistage_majority" ISNULL AND

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

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

687 CONSTRAINT "minimum_requirement_to_be_eligible" CHECK (

688 "eligible" = FALSE OR

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

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

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

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

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

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

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

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

697 CREATE TRIGGER "update_text_search_data"

698 BEFORE INSERT OR UPDATE ON "initiative"

699 FOR EACH ROW EXECUTE PROCEDURE

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

701 "name", "discussion_url");

702

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

704

705 COMMENT ON COLUMN "initiative"."polling" IS 'Initiative is an option for a poll (see "policy"."polling"), and does not need to pass the initiative quorum';

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

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

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

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

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

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

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

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

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

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

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

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

718 COMMENT ON COLUMN "initiative"."schulze_rank" IS 'Schulze-Ranking without tie-breaking';

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

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

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

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

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

724 COMMENT ON COLUMN "initiative"."winner" IS 'Winner is the "eligible" initiative with best "schulze_rank" and in case of ties with lowest "id"';

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

726

727

728 CREATE TABLE "battle" (

729 "issue_id" INT4 NOT NULL,

730 "winning_initiative_id" INT4,

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

732 "losing_initiative_id" INT4,

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

734 "count" INT4 NOT NULL,

735 CONSTRAINT "initiative_ids_not_equal" CHECK (

736 "winning_initiative_id" != "losing_initiative_id" OR

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

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

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

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

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

742

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

744

745

746 CREATE TABLE "ignored_initiative" (

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

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

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

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

751

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

753

754

755 CREATE TABLE "initiative_setting" (

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

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

758 "key" TEXT NOT NULL,

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

760 "value" TEXT NOT NULL );

761

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

763

764

765 CREATE TABLE "draft" (

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

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

768 "id" SERIAL8 PRIMARY KEY,

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

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

771 "formatting_engine" TEXT,

772 "content" TEXT NOT NULL,

773 "text_search_data" TSVECTOR );

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

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

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

777 CREATE TRIGGER "update_text_search_data"

778 BEFORE INSERT OR UPDATE ON "draft"

779 FOR EACH ROW EXECUTE PROCEDURE

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

781

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

783

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

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

786

787

788 CREATE TABLE "rendered_draft" (

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

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

791 "format" TEXT,

792 "content" TEXT NOT NULL );

793

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

795

796

797 CREATE TABLE "suggestion" (

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

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

800 "id" SERIAL8 PRIMARY KEY,

801 "draft_id" INT8 NOT NULL,

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

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

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

805 "name" TEXT NOT NULL,

806 "formatting_engine" TEXT,

807 "content" TEXT NOT NULL DEFAULT '',

808 "text_search_data" TSVECTOR,

809 "minus2_unfulfilled_count" INT4,

810 "minus2_fulfilled_count" INT4,

811 "minus1_unfulfilled_count" INT4,

812 "minus1_fulfilled_count" INT4,

813 "plus1_unfulfilled_count" INT4,

814 "plus1_fulfilled_count" INT4,

815 "plus2_unfulfilled_count" INT4,

816 "plus2_fulfilled_count" INT4 );

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

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

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

820 CREATE TRIGGER "update_text_search_data"

821 BEFORE INSERT OR UPDATE ON "suggestion"

822 FOR EACH ROW EXECUTE PROCEDURE

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

824 "name", "content");

825

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

827

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

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

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

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

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

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

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

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

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

837

838

839 CREATE TABLE "rendered_suggestion" (

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

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

842 "format" TEXT,

843 "content" TEXT NOT NULL );

844

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

846

847

848 CREATE TABLE "suggestion_setting" (

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

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

851 "key" TEXT NOT NULL,

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

853 "value" TEXT NOT NULL );

854

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

856

857

858 CREATE TABLE "privilege" (

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

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

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

862 "admin_manager" BOOLEAN NOT NULL DEFAULT FALSE,

863 "unit_manager" BOOLEAN NOT NULL DEFAULT FALSE,

864 "area_manager" BOOLEAN NOT NULL DEFAULT FALSE,

865 "member_manager" BOOLEAN NOT NULL DEFAULT FALSE,

866 "initiative_right" BOOLEAN NOT NULL DEFAULT TRUE,

867 "voting_right" BOOLEAN NOT NULL DEFAULT TRUE,

868 "polling_right" BOOLEAN NOT NULL DEFAULT FALSE );

869

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

871

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

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

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

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

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

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

878 COMMENT ON COLUMN "privilege"."polling_right" IS 'Right to create polls (see "policy"."polling" and "initiative"."polling")';

879

880

881 CREATE TABLE "membership" (

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

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

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

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

886

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

888

889

890 CREATE TABLE "interest" (

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

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

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

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

895

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

897

898

899 CREATE TABLE "initiator" (

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

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

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

903 "accepted" BOOLEAN );

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

905

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

907

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

909

910

911 CREATE TABLE "supporter" (

912 "issue_id" INT4 NOT NULL,

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

914 "initiative_id" INT4,

915 "member_id" INT4,

916 "draft_id" INT8 NOT NULL,

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

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

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

920

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

922

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

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

925

926

927 CREATE TABLE "opinion" (

928 "initiative_id" INT4 NOT NULL,

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

930 "suggestion_id" INT8,

931 "member_id" INT4,

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

933 "fulfilled" BOOLEAN NOT NULL DEFAULT FALSE,

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

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

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

937

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

939

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

941

942

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

944

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

946

947

948 CREATE TABLE "delegation" (

949 "id" SERIAL8 PRIMARY KEY,

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

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

952 "scope" "delegation_scope" NOT NULL,

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

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

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

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

957 CONSTRAINT "no_unit_delegation_to_null"

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

959 CONSTRAINT "area_id_and_issue_id_set_according_to_scope" CHECK (

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

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

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

963 UNIQUE ("unit_id", "truster_id"),

964 UNIQUE ("area_id", "truster_id"),

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

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

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

968

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

970

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

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

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

974

975

976 CREATE TABLE "direct_population_snapshot" (

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

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

979 "event" "snapshot_event",

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

981 "weight" INT4 );

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

983

984 COMMENT ON TABLE "direct_population_snapshot" IS 'Snapshot of active members having either a "membership" in the "area" or an "interest" in the "issue"';

985

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

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

988

989

990 CREATE TABLE "delegating_population_snapshot" (

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

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

993 "event" "snapshot_event",

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

995 "weight" INT4,

996 "scope" "delegation_scope" NOT NULL,

997 "delegate_member_ids" INT4[] NOT NULL );

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

999

1000 COMMENT ON TABLE "direct_population_snapshot" IS 'Delegations increasing the weight of entries in the "direct_population_snapshot" table';

1001

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

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

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

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

1006

1007

1008 CREATE TABLE "direct_interest_snapshot" (

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

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

1011 "event" "snapshot_event",

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

1013 "weight" INT4 );

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

1015

1016 COMMENT ON TABLE "direct_interest_snapshot" IS 'Snapshot of active members having an "interest" in the "issue"';

1017

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

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

1020

1021

1022 CREATE TABLE "delegating_interest_snapshot" (

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

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

1025 "event" "snapshot_event",

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

1027 "weight" INT4,

1028 "scope" "delegation_scope" NOT NULL,

1029 "delegate_member_ids" INT4[] NOT NULL );

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

1031

1032 COMMENT ON TABLE "delegating_interest_snapshot" IS 'Delegations increasing the weight of entries in the "direct_interest_snapshot" table';

1033

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

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

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

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

1038

1039

1040 CREATE TABLE "direct_supporter_snapshot" (

1041 "issue_id" INT4 NOT NULL,

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

1043 "initiative_id" INT4,

1044 "event" "snapshot_event",

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

1046 "draft_id" INT8 NOT NULL,

1047 "informed" BOOLEAN NOT NULL,

1048 "satisfied" BOOLEAN NOT NULL,

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

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

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

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

1053

1054 COMMENT ON TABLE "direct_supporter_snapshot" IS 'Snapshot of supporters of initiatives (weight is stored in "direct_interest_snapshot")';

1055

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

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

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

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

1060

1061

1062 CREATE TABLE "non_voter" (

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

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

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

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

1067

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

1069

1070

1071 CREATE TABLE "direct_voter" (

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

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

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

1075 "weight" INT4 );

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

1077

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

1079

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

1081

1082

1083 CREATE TABLE "delegating_voter" (

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

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

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

1087 "weight" INT4,

1088 "scope" "delegation_scope" NOT NULL,

1089 "delegate_member_ids" INT4[] NOT NULL );

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

1091

1092 COMMENT ON TABLE "delegating_voter" IS 'Delegations increasing the weight of entries in the "direct_voter" table';

1093

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

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

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

1097

1098

1099 CREATE TABLE "vote" (

1100 "issue_id" INT4 NOT NULL,

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

1102 "initiative_id" INT4,

1103 "member_id" INT4,

1104 "grade" INT4,

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

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

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

1108

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

1110

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

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

1113

1114

1115 CREATE TABLE "voting_comment" (

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

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

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

1119 "changed" TIMESTAMPTZ,

1120 "formatting_engine" TEXT,

1121 "content" TEXT NOT NULL,

1122 "text_search_data" TSVECTOR );

1123 CREATE INDEX "voting_comment_member_id_idx" ON "voting_comment" ("member_id");

1124 CREATE INDEX "voting_comment_text_search_data_idx" ON "voting_comment" USING gin ("text_search_data");

1125 CREATE TRIGGER "update_text_search_data"

1126 BEFORE INSERT OR UPDATE ON "voting_comment"

1127 FOR EACH ROW EXECUTE PROCEDURE

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

1129

1130 COMMENT ON TABLE "voting_comment" IS 'Storage for comments of voters to be published after voting has finished.';

1131

1132 COMMENT ON COLUMN "voting_comment"."changed" 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.';

1133

1134

1135 CREATE TABLE "rendered_voting_comment" (

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

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

1138 REFERENCES "voting_comment" ("issue_id", "member_id")

1139 ON DELETE CASCADE ON UPDATE CASCADE,

1140 "issue_id" INT4,

1141 "member_id" INT4,

1142 "format" TEXT,

1143 "content" TEXT NOT NULL );

1144

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

1146

1147

1148 CREATE TYPE "event_type" AS ENUM (

1149 'issue_state_changed',

1150 'initiative_created_in_new_issue',

1151 'initiative_created_in_existing_issue',

1152 'initiative_revoked',

1153 'new_draft_created',

1154 'suggestion_created');

1155

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

1157

1158

1159 CREATE TABLE "event" (

1160 "id" SERIAL8 PRIMARY KEY,

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

1162 "event" "event_type" NOT NULL,

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

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

1165 "state" "issue_state" CHECK ("state" != 'calculation'),

1166 "initiative_id" INT4,

1167 "draft_id" INT8,

1168 "suggestion_id" INT8,

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

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

1171 ON DELETE CASCADE ON UPDATE CASCADE,

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

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

1174 ON DELETE CASCADE ON UPDATE CASCADE,

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

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

1177 ON DELETE CASCADE ON UPDATE CASCADE,

1178 CONSTRAINT "null_constraints_for_issue_state_changed" CHECK (

1179 "event" != 'issue_state_changed' OR (

1180 "member_id" ISNULL AND

1181 "issue_id" NOTNULL AND

1182 "state" NOTNULL AND

1183 "initiative_id" ISNULL AND

1184 "draft_id" ISNULL AND

1185 "suggestion_id" ISNULL )),

1186 CONSTRAINT "null_constraints_for_initiative_creation_or_revocation_or_new_draft" CHECK (

1187 "event" NOT IN (

1188 'initiative_created_in_new_issue',

1189 'initiative_created_in_existing_issue',

1190 'initiative_revoked',

1191 'new_draft_created'

1192 ) OR (

1193 "member_id" NOTNULL AND

1194 "issue_id" NOTNULL AND

1195 "state" NOTNULL AND

1196 "initiative_id" NOTNULL AND

1197 "draft_id" NOTNULL AND

1198 "suggestion_id" ISNULL )),

1199 CONSTRAINT "null_constraints_for_suggestion_creation" CHECK (

1200 "event" != 'suggestion_created' OR (

1201 "member_id" NOTNULL AND

1202 "issue_id" NOTNULL AND

1203 "state" NOTNULL AND

1204 "initiative_id" NOTNULL AND

1205 "draft_id" ISNULL AND

1206 "suggestion_id" NOTNULL )) );

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

1208

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

1210

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

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

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

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

1215

1216

1217 CREATE TABLE "notification_sent" (

1218 "event_id" INT8 NOT NULL );

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

1220

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

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

1223

1224

1225

1226 ----------------------------------------------

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

1228 ----------------------------------------------

1229

1230

1231 CREATE FUNCTION "write_member_history_trigger"()

1232 RETURNS TRIGGER

1233 LANGUAGE 'plpgsql' VOLATILE AS $$

1234 BEGIN

1235 IF

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

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

1238 OLD."activated" NOTNULL

1239 THEN

1240 INSERT INTO "member_history"

1241 ("member_id", "active", "name")

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

1243 END IF;

1244 RETURN NULL;

1245 END;

1246 $$;

1247

1248 CREATE TRIGGER "write_member_history"

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

1250 "write_member_history_trigger"();

1251

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

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

1254

1255

1256 CREATE FUNCTION "write_event_issue_state_changed_trigger"()

1257 RETURNS TRIGGER

1258 LANGUAGE 'plpgsql' VOLATILE AS $$

1259 BEGIN

1260 IF NEW."state" != OLD."state" AND NEW."state" != 'calculation' THEN

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

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

1263 END IF;

1264 RETURN NULL;

1265 END;

1266 $$;

1267

1268 CREATE TRIGGER "write_event_issue_state_changed"

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

1270 "write_event_issue_state_changed_trigger"();

1271

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

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

1274

1275

1276 CREATE FUNCTION "write_event_initiative_or_draft_created_trigger"()

1277 RETURNS TRIGGER

1278 LANGUAGE 'plpgsql' VOLATILE AS $$

1279 DECLARE

1280 "initiative_row" "initiative"%ROWTYPE;

1281 "issue_row" "issue"%ROWTYPE;

1282 "event_v" "event_type";

1283 BEGIN

1284 SELECT * INTO "initiative_row" FROM "initiative"

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

1286 SELECT * INTO "issue_row" FROM "issue"

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

1288 IF EXISTS (

1289 SELECT NULL FROM "draft"

1290 WHERE "initiative_id" = NEW."initiative_id"

1291 AND "id" != NEW."id"

1292 ) THEN

1293 "event_v" := 'new_draft_created';

1294 ELSE

1295 IF EXISTS (

1296 SELECT NULL FROM "initiative"

1297 WHERE "issue_id" = "initiative_row"."issue_id"

1298 AND "id" != "initiative_row"."id"

1299 ) THEN

1300 "event_v" := 'initiative_created_in_existing_issue';

1301 ELSE

1302 "event_v" := 'initiative_created_in_new_issue';

1303 END IF;

1304 END IF;

1305 INSERT INTO "event" (

1306 "event", "member_id",

1307 "issue_id", "state", "initiative_id", "draft_id"

1308 ) VALUES (

1309 "event_v",

1310 NEW."author_id",

1311 "initiative_row"."issue_id",

1312 "issue_row"."state",

1313 "initiative_row"."id",

1314 NEW."id" );

1315 RETURN NULL;

1316 END;

1317 $$;

1318

1319 CREATE TRIGGER "write_event_initiative_or_draft_created"

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

1321 "write_event_initiative_or_draft_created_trigger"();

1322

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

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

1325

1326

1327 CREATE FUNCTION "write_event_initiative_revoked_trigger"()

1328 RETURNS TRIGGER

1329 LANGUAGE 'plpgsql' VOLATILE AS $$

1330 DECLARE

1331 "issue_row" "issue"%ROWTYPE;

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

1333 BEGIN

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

1335 SELECT * INTO "issue_row" FROM "issue"

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

1337 SELECT "id" INTO "draft_id_v" FROM "current_draft"

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

1339 INSERT INTO "event" (

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

1341 ) VALUES (

1342 'initiative_revoked',

1343 NEW."revoked_by_member_id",

1344 NEW."issue_id",

1345 "issue_row"."state",

1346 NEW."id",

1347 "draft_id_v");

1348 END IF;

1349 RETURN NULL;

1350 END;

1351 $$;

1352

1353 CREATE TRIGGER "write_event_initiative_revoked"

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

1355 "write_event_initiative_revoked_trigger"();

1356

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

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

1359

1360

1361 CREATE FUNCTION "write_event_suggestion_created_trigger"()

1362 RETURNS TRIGGER

1363 LANGUAGE 'plpgsql' VOLATILE AS $$

1364 DECLARE

1365 "initiative_row" "initiative"%ROWTYPE;

1366 "issue_row" "issue"%ROWTYPE;

1367 BEGIN

1368 SELECT * INTO "initiative_row" FROM "initiative"

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

1370 SELECT * INTO "issue_row" FROM "issue"

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

1372 INSERT INTO "event" (

1373 "event", "member_id",

1374 "issue_id", "state", "initiative_id", "suggestion_id"

1375 ) VALUES (

1376 'suggestion_created',

1377 NEW."author_id",

1378 "initiative_row"."issue_id",

1379 "issue_row"."state",

1380 "initiative_row"."id",

1381 NEW."id" );

1382 RETURN NULL;

1383 END;

1384 $$;

1385

1386 CREATE TRIGGER "write_event_suggestion_created"

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

1388 "write_event_suggestion_created_trigger"();

1389

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

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

1392

1393

1394

1395 ----------------------------

1396 -- Additional constraints --

1397 ----------------------------

1398

1399

1400 CREATE FUNCTION "issue_requires_first_initiative_trigger"()

1401 RETURNS TRIGGER

1402 LANGUAGE 'plpgsql' VOLATILE AS $$

1403 BEGIN

1404 IF NOT EXISTS (

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

1406 ) THEN

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

1408 -- ERRCODE = 'integrity_constraint_violation',

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

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

1411 END IF;

1412 RETURN NULL;

1413 END;

1414 $$;

1415

1416 CREATE CONSTRAINT TRIGGER "issue_requires_first_initiative"

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

1418 FOR EACH ROW EXECUTE PROCEDURE

1419 "issue_requires_first_initiative_trigger"();

1420

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

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

1423

1424

1425 CREATE FUNCTION "last_initiative_deletes_issue_trigger"()

1426 RETURNS TRIGGER

1427 LANGUAGE 'plpgsql' VOLATILE AS $$

1428 DECLARE

1429 "reference_lost" BOOLEAN;

1430 BEGIN

1431 IF TG_OP = 'DELETE' THEN

1432 "reference_lost" := TRUE;

1433 ELSE

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

1435 END IF;

1436 IF

1437 "reference_lost" AND NOT EXISTS (

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

1439 )

1440 THEN

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

1442 END IF;

1443 RETURN NULL;

1444 END;

1445 $$;

1446

1447 CREATE CONSTRAINT TRIGGER "last_initiative_deletes_issue"

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

1449 FOR EACH ROW EXECUTE PROCEDURE

1450 "last_initiative_deletes_issue_trigger"();

1451

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

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

1454

1455

1456 CREATE FUNCTION "initiative_requires_first_draft_trigger"()

1457 RETURNS TRIGGER

1458 LANGUAGE 'plpgsql' VOLATILE AS $$

1459 BEGIN

1460 IF NOT EXISTS (

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

1462 ) THEN

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

1464 -- ERRCODE = 'integrity_constraint_violation',

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

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

1467 END IF;

1468 RETURN NULL;

1469 END;

1470 $$;

1471

1472 CREATE CONSTRAINT TRIGGER "initiative_requires_first_draft"

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

1474 FOR EACH ROW EXECUTE PROCEDURE

1475 "initiative_requires_first_draft_trigger"();

1476

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

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

1479

1480

1481 CREATE FUNCTION "last_draft_deletes_initiative_trigger"()

1482 RETURNS TRIGGER

1483 LANGUAGE 'plpgsql' VOLATILE AS $$

1484 DECLARE

1485 "reference_lost" BOOLEAN;

1486 BEGIN

1487 IF TG_OP = 'DELETE' THEN

1488 "reference_lost" := TRUE;

1489 ELSE

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

1491 END IF;

1492 IF

1493 "reference_lost" AND NOT EXISTS (

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

1495 )

1496 THEN

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

1498 END IF;

1499 RETURN NULL;

1500 END;

1501 $$;

1502

1503 CREATE CONSTRAINT TRIGGER "last_draft_deletes_initiative"

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

1505 FOR EACH ROW EXECUTE PROCEDURE

1506 "last_draft_deletes_initiative_trigger"();

1507

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

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

1510

1511

1512 CREATE FUNCTION "suggestion_requires_first_opinion_trigger"()

1513 RETURNS TRIGGER

1514 LANGUAGE 'plpgsql' VOLATILE AS $$

1515 BEGIN

1516 IF NOT EXISTS (

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

1518 ) THEN

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

1520 END IF;

1521 RETURN NULL;

1522 END;

1523 $$;

1524

1525 CREATE CONSTRAINT TRIGGER "suggestion_requires_first_opinion"

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

1527 FOR EACH ROW EXECUTE PROCEDURE

1528 "suggestion_requires_first_opinion_trigger"();

1529

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

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

1532

1533

1534 CREATE FUNCTION "last_opinion_deletes_suggestion_trigger"()

1535 RETURNS TRIGGER

1536 LANGUAGE 'plpgsql' VOLATILE AS $$

1537 DECLARE

1538 "reference_lost" BOOLEAN;

1539 BEGIN

1540 IF TG_OP = 'DELETE' THEN

1541 "reference_lost" := TRUE;

1542 ELSE

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

1544 END IF;

1545 IF

1546 "reference_lost" AND NOT EXISTS (

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

1548 )

1549 THEN

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

1551 END IF;

1552 RETURN NULL;

1553 END;

1554 $$;

1555

1556 CREATE CONSTRAINT TRIGGER "last_opinion_deletes_suggestion"

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

1558 FOR EACH ROW EXECUTE PROCEDURE

1559 "last_opinion_deletes_suggestion_trigger"();

1560

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

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

1563

1564

1565

1566 ---------------------------------------------------------------

1567 -- Ensure that votes are not modified when issues are frozen --

1568 ---------------------------------------------------------------

1569

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

1571 -- errors the following triggers ensure data integrity.

1572

1573

1574 CREATE FUNCTION "forbid_changes_on_closed_issue_trigger"()

1575 RETURNS TRIGGER

1576 LANGUAGE 'plpgsql' VOLATILE AS $$

1577 DECLARE

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

1579 "issue_row" "issue"%ROWTYPE;

1580 BEGIN

1581 IF TG_OP = 'DELETE' THEN

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

1583 ELSE

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

1585 END IF;

1586 SELECT INTO "issue_row" * FROM "issue"

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

1588 IF "issue_row"."closed" NOTNULL THEN

1589 RAISE EXCEPTION 'Tried to modify data belonging to a closed issue.';

1590 END IF;

1591 RETURN NULL;

1592 END;

1593 $$;

1594

1595 CREATE TRIGGER "forbid_changes_on_closed_issue"

1596 AFTER INSERT OR UPDATE OR DELETE ON "direct_voter"

1597 FOR EACH ROW EXECUTE PROCEDURE

1598 "forbid_changes_on_closed_issue_trigger"();

1599

1600 CREATE TRIGGER "forbid_changes_on_closed_issue"

1601 AFTER INSERT OR UPDATE OR DELETE ON "delegating_voter"

1602 FOR EACH ROW EXECUTE PROCEDURE

1603 "forbid_changes_on_closed_issue_trigger"();

1604

1605 CREATE TRIGGER "forbid_changes_on_closed_issue"

1606 AFTER INSERT OR UPDATE OR DELETE ON "vote"

1607 FOR EACH ROW EXECUTE PROCEDURE

1608 "forbid_changes_on_closed_issue_trigger"();

1609

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

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

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

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

1614

1615

1616

1617 --------------------------------------------------------------------

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

1619 --------------------------------------------------------------------

1620

1621

1622 CREATE FUNCTION "autofill_issue_id_trigger"()

1623 RETURNS TRIGGER

1624 LANGUAGE 'plpgsql' VOLATILE AS $$

1625 BEGIN

1626 IF NEW."issue_id" ISNULL THEN

1627 SELECT "issue_id" INTO NEW."issue_id"

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

1629 END IF;

1630 RETURN NEW;

1631 END;

1632 $$;

1633

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

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

1636

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

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

1639

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

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

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

1643

1644

1645 CREATE FUNCTION "autofill_initiative_id_trigger"()

1646 RETURNS TRIGGER

1647 LANGUAGE 'plpgsql' VOLATILE AS $$

1648 BEGIN

1649 IF NEW."initiative_id" ISNULL THEN

1650 SELECT "initiative_id" INTO NEW."initiative_id"

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

1652 END IF;

1653 RETURN NEW;

1654 END;

1655 $$;

1656

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

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

1659

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

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

1662

1663

1664

1665 -----------------------------------------------------

1666 -- Automatic calculation of certain default values --

1667 -----------------------------------------------------

1668

1669

1670 CREATE FUNCTION "copy_timings_trigger"()

1671 RETURNS TRIGGER

1672 LANGUAGE 'plpgsql' VOLATILE AS $$

1673 DECLARE

1674 "policy_row" "policy"%ROWTYPE;

1675 BEGIN

1676 SELECT * INTO "policy_row" FROM "policy"

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

1678 IF NEW."admission_time" ISNULL THEN

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

1680 END IF;

1681 IF NEW."discussion_time" ISNULL THEN

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

1683 END IF;

1684 IF NEW."verification_time" ISNULL THEN

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

1686 END IF;

1687 IF NEW."voting_time" ISNULL THEN

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

1689 END IF;

1690 RETURN NEW;

1691 END;

1692 $$;

1693

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

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

1696

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

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

1699

1700

1701 CREATE FUNCTION "default_for_draft_id_trigger"()

1702 RETURNS TRIGGER

1703 LANGUAGE 'plpgsql' VOLATILE AS $$

1704 BEGIN

1705 IF NEW."draft_id" ISNULL THEN

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

1707 WHERE "initiative_id" = NEW."initiative_id";

1708 END IF;

1709 RETURN NEW;

1710 END;

1711 $$;

1712

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

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

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

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

1717

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

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

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

1721

1722

1723

1724 ----------------------------------------

1725 -- Automatic creation of dependencies --

1726 ----------------------------------------

1727

1728

1729 CREATE FUNCTION "autocreate_interest_trigger"()

1730 RETURNS TRIGGER

1731 LANGUAGE 'plpgsql' VOLATILE AS $$

1732 BEGIN

1733 IF NOT EXISTS (

1734 SELECT NULL FROM "initiative" JOIN "interest"

1735 ON "initiative"."issue_id" = "interest"."issue_id"

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

1737 AND "interest"."member_id" = NEW."member_id"

1738 ) THEN

1739 BEGIN

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

1741 SELECT "issue_id", NEW."member_id"

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

1743 EXCEPTION WHEN unique_violation THEN END;

1744 END IF;

1745 RETURN NEW;

1746 END;

1747 $$;

1748

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

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

1751

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

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

1754

1755

1756 CREATE FUNCTION "autocreate_supporter_trigger"()

1757 RETURNS TRIGGER

1758 LANGUAGE 'plpgsql' VOLATILE AS $$

1759 BEGIN

1760 IF NOT EXISTS (

1761 SELECT NULL FROM "suggestion" JOIN "supporter"

1762 ON "suggestion"."initiative_id" = "supporter"."initiative_id"

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

1764 AND "supporter"."member_id" = NEW."member_id"

1765 ) THEN

1766 BEGIN

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

1768 SELECT "initiative_id", NEW."member_id"

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

1770 EXCEPTION WHEN unique_violation THEN END;

1771 END IF;

1772 RETURN NEW;

1773 END;

1774 $$;

1775

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

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

1778

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

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

1781

1782

1783

1784 ------------------------------------------

1785 -- Views and helper functions for views --

1786 ------------------------------------------

1787

1788

1789 CREATE VIEW "unit_delegation" AS

1790 SELECT

1791 "unit"."id" AS "unit_id",

1792 "delegation"."id",

1793 "delegation"."truster_id",

1794 "delegation"."trustee_id",

1795 "delegation"."scope"

1796 FROM "unit"

1797 JOIN "delegation"

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

1799 JOIN "member"

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

1801 JOIN "privilege"

1802 ON "delegation"."unit_id" = "privilege"."unit_id"

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

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

1805

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

1807

1808

1809 CREATE VIEW "area_delegation" AS

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

1811 "area"."id" AS "area_id",

1812 "delegation"."id",

1813 "delegation"."truster_id",

1814 "delegation"."trustee_id",

1815 "delegation"."scope"

1816 FROM "area"

1817 JOIN "delegation"

1818 ON "delegation"."unit_id" = "area"."unit_id"

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

1820 JOIN "member"

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

1822 JOIN "privilege"

1823 ON "area"."unit_id" = "privilege"."unit_id"

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

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

1826 ORDER BY

1827 "area"."id",

1828 "delegation"."truster_id",

1829 "delegation"."scope" DESC;

1830

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

1832

1833

1834 CREATE VIEW "issue_delegation" AS

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

1836 "issue"."id" AS "issue_id",

1837 "delegation"."id",

1838 "delegation"."truster_id",

1839 "delegation"."trustee_id",

1840 "delegation"."scope"

1841 FROM "issue"

1842 JOIN "area"

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

1844 JOIN "delegation"

1845 ON "delegation"."unit_id" = "area"."unit_id"

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

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

1848 JOIN "member"

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

1850 JOIN "privilege"

1851 ON "area"."unit_id" = "privilege"."unit_id"

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

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

1854 ORDER BY

1855 "issue"."id",

1856 "delegation"."truster_id",

1857 "delegation"."scope" DESC;

1858

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

1860

1861

1862 CREATE FUNCTION "membership_weight_with_skipping"

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

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

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

1866 RETURNS INT4

1867 LANGUAGE 'plpgsql' STABLE AS $$

1868 DECLARE

1869 "sum_v" INT4;

1870 "delegation_row" "area_delegation"%ROWTYPE;

1871 BEGIN

1872 "sum_v" := 1;

1873 FOR "delegation_row" IN

1874 SELECT "area_delegation".*

1875 FROM "area_delegation" LEFT JOIN "membership"

1876 ON "membership"."area_id" = "area_id_p"

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

1878 WHERE "area_delegation"."area_id" = "area_id_p"

1879 AND "area_delegation"."trustee_id" = "member_id_p"

1880 AND "membership"."member_id" ISNULL

1881 LOOP

1882 IF NOT

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

1884 THEN

1885 "sum_v" := "sum_v" + "membership_weight_with_skipping"(

1886 "area_id_p",

1887 "delegation_row"."truster_id",

1888 "skip_member_ids_p" || "delegation_row"."truster_id"

1889 );

1890 END IF;

1891 END LOOP;

1892 RETURN "sum_v";

1893 END;

1894 $$;

1895

1896 COMMENT ON FUNCTION "membership_weight_with_skipping"

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

1898 "member"."id"%TYPE,

1899 INT4[] )

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

1901

1902

1903 CREATE FUNCTION "membership_weight"

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

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

1906 RETURNS INT4

1907 LANGUAGE 'plpgsql' STABLE AS $$

1908 BEGIN

1909 RETURN "membership_weight_with_skipping"(

1910 "area_id_p",

1911 "member_id_p",

1912 ARRAY["member_id_p"]

1913 );

1914 END;

1915 $$;

1916

1917 COMMENT ON FUNCTION "membership_weight"

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

1919 "member"."id"%TYPE )

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

1921

1922

1923 CREATE VIEW "member_count_view" AS

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

1925

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

1927

1928

1929 CREATE VIEW "unit_member_count" AS

1930 SELECT

1931 "unit"."id" AS "unit_id",

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

1933 FROM "unit"

1934 LEFT JOIN "privilege"

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

1936 AND "privilege"."voting_right"

1937 LEFT JOIN "member"

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

1939 AND "member"."active"

1940 GROUP BY "unit"."id";

1941

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

1943

1944

1945 CREATE VIEW "area_member_count" AS

1946 SELECT

1947 "area"."id" AS "area_id",

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

1949 coalesce(

1950 sum(

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

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

1953 ELSE 0 END

1954 )

1955 ) AS "member_weight"

1956 FROM "area"

1957 LEFT JOIN "membership"

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

1959 LEFT JOIN "privilege"

1960 ON "privilege"."unit_id" = "area"."unit_id"

1961 AND "privilege"."member_id" = "membership"."member_id"

1962 AND "privilege"."voting_right"

1963 LEFT JOIN "member"

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

1965 AND "member"."active"

1966 GROUP BY "area"."id";

1967

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

1969

1970

1971 CREATE VIEW "opening_draft" AS

1972 SELECT "draft".* FROM (

1973 SELECT

1974 "initiative"."id" AS "initiative_id",

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

1976 FROM "initiative" JOIN "draft"

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

1978 GROUP BY "initiative"."id"

1979 ) AS "subquery"

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

1981

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

1983

1984

1985 CREATE VIEW "current_draft" AS

1986 SELECT "draft".* FROM (

1987 SELECT

1988 "initiative"."id" AS "initiative_id",

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

1990 FROM "initiative" JOIN "draft"

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

1992 GROUP BY "initiative"."id"

1993 ) AS "subquery"

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

1995

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

1997

1998

1999 CREATE VIEW "critical_opinion" AS

2000 SELECT * FROM "opinion"

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

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

2003

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

2005

2006

2007 CREATE VIEW "battle_participant" AS

2008 SELECT "initiative"."id", "initiative"."issue_id"

2009 FROM "issue" JOIN "initiative"

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

2011 WHERE "initiative"."admitted"

2012 UNION ALL

2013 SELECT NULL, "id" AS "issue_id"

2014 FROM "issue";

2015

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

2017

2018

2019 CREATE VIEW "battle_view" AS

2020 SELECT

2021 "issue"."id" AS "issue_id",

2022 "winning_initiative"."id" AS "winning_initiative_id",

2023 "losing_initiative"."id" AS "losing_initiative_id",

2024 sum(

2025 CASE WHEN

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

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

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

2029 ) AS "count"

2030 FROM "issue"

2031 LEFT JOIN "direct_voter"

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

2033 JOIN "battle_participant" AS "winning_initiative"

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

2035 JOIN "battle_participant" AS "losing_initiative"

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

2037 LEFT JOIN "vote" AS "better_vote"

2038 ON "direct_voter"."member_id" = "better_vote"."member_id"

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

2040 LEFT JOIN "vote" AS "worse_vote"

2041 ON "direct_voter"."member_id" = "worse_vote"."member_id"

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

2043 WHERE "issue"."closed" NOTNULL

2044 AND "issue"."cleaned" ISNULL

2045 AND (

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

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

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

2049 GROUP BY

2050 "issue"."id",

2051 "winning_initiative"."id",

2052 "losing_initiative"."id";

2053

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

2055

2056

2057 CREATE VIEW "expired_session" AS

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

2059

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

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

2062

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

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

2065

2066

2067 CREATE VIEW "open_issue" AS

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

2069

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

2071

2072

2073 CREATE VIEW "issue_with_ranks_missing" AS

2074 SELECT * FROM "issue"

2075 WHERE "fully_frozen" NOTNULL

2076 AND "closed" NOTNULL

2077 AND "ranks_available" = FALSE;

2078

2079 COMMENT ON VIEW "issue_with_ranks_missing" IS 'Issues where voting was finished, but no ranks have been calculated yet';

2080

2081

2082 CREATE VIEW "member_contingent" AS

2083 SELECT

2084 "member"."id" AS "member_id",

2085 "contingent"."time_frame",

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

2087 (

2088 SELECT count(1) FROM "draft"

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

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

2091 ) + (

2092 SELECT count(1) FROM "suggestion"

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

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

2095 )

2096 ELSE NULL END AS "text_entry_count",

2097 "contingent"."text_entry_limit",

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

2099 SELECT count(1) FROM "opening_draft"

2100 WHERE "opening_draft"."author_id" = "member"."id"

2101 AND "opening_draft"."created" > now() - "contingent"."time_frame"

2102 ) ELSE NULL END AS "initiative_count",

2103 "contingent"."initiative_limit"

2104 FROM "member" CROSS JOIN "contingent";

2105

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

2107

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

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

2110

2111

2112 CREATE VIEW "member_contingent_left" AS

2113 SELECT

2114 "member_id",

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

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

2117 FROM "member_contingent" GROUP BY "member_id";

2118

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

2120

2121

2122 CREATE VIEW "event_seen_by_member" AS

2123 SELECT

2124 "member"."id" AS "seen_by_member_id",

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

2126 'voting',

2127 'finished_without_winner',

2128 'finished_with_winner'

2129 ) THEN

2130 'voting'::"notify_level"

2131 ELSE

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

2133 'verification',

2134 'canceled_after_revocation_during_verification',

2135 'canceled_no_initiative_admitted'

2136 ) THEN

2137 'verification'::"notify_level"

2138 ELSE

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

2140 'discussion',

2141 'canceled_after_revocation_during_discussion'

2142 ) THEN

2143 'discussion'::"notify_level"

2144 ELSE

2145 'all'::"notify_level"

2146 END

2147 END

2148 END AS "notify_level",

2149 "event".*

2150 FROM "member" CROSS JOIN "event"

2151 LEFT JOIN "issue"

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

2153 LEFT JOIN "membership"

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

2155 AND "issue"."area_id" = "membership"."area_id"

2156 LEFT JOIN "interest"

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

2158 AND "event"."issue_id" = "interest"."issue_id"

2159 LEFT JOIN "supporter"

2160 ON "member"."id" = "supporter"."member_id"

2161 AND "event"."initiative_id" = "supporter"."initiative_id"

2162 LEFT JOIN "ignored_member"

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

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

2165 LEFT JOIN "ignored_initiative"

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

2167 AND "event"."initiative_id" = "ignored_initiative"."initiative_id"

2168 WHERE (

2169 "supporter"."member_id" NOTNULL OR

2170 "interest"."member_id" NOTNULL OR

2171 ( "membership"."member_id" NOTNULL AND

2172 "event"."event" IN (

2173 'issue_state_changed',

2174 'initiative_created_in_new_issue',

2175 'initiative_created_in_existing_issue',

2176 'initiative_revoked' ) ) )

2177 AND "ignored_member"."member_id" ISNULL

2178 AND "ignored_initiative"."member_id" ISNULL;

2179

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

2181

2182

2183 CREATE VIEW "selected_event_seen_by_member" AS

2184 SELECT

2185 "member"."id" AS "seen_by_member_id",

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

2187 'voting',

2188 'finished_without_winner',

2189 'finished_with_winner'

2190 ) THEN

2191 'voting'::"notify_level"

2192 ELSE

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

2194 'verification',

2195 'canceled_after_revocation_during_verification',

2196 'canceled_no_initiative_admitted'

2197 ) THEN

2198 'verification'::"notify_level"

2199 ELSE

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

2201 'discussion',

2202 'canceled_after_revocation_during_discussion'

2203 ) THEN

2204 'discussion'::"notify_level"

2205 ELSE

2206 'all'::"notify_level"

2207 END

2208 END

2209 END AS "notify_level",

2210 "event".*

2211 FROM "member" CROSS JOIN "event"

2212 LEFT JOIN "issue"

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

2214 LEFT JOIN "membership"

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

2216 AND "issue"."area_id" = "membership"."area_id"

2217 LEFT JOIN "interest"

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

2219 AND "event"."issue_id" = "interest"."issue_id"

2220 LEFT JOIN "supporter"

2221 ON "member"."id" = "supporter"."member_id"

2222 AND "event"."initiative_id" = "supporter"."initiative_id"

2223 LEFT JOIN "ignored_member"

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

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

2226 LEFT JOIN "ignored_initiative"

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

2228 AND "event"."initiative_id" = "ignored_initiative"."initiative_id"

2229 WHERE (

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

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

2232 "event"."state" IN (

2233 'voting',

2234 'finished_without_winner',

2235 'finished_with_winner' ) ) OR

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

2237 "event"."state" IN (

2238 'verification',

2239 'canceled_after_revocation_during_verification',

2240 'canceled_no_initiative_admitted' ) ) OR

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

2242 "event"."state" IN (

2243 'discussion',

2244 'canceled_after_revocation_during_discussion' ) ) )

2245 AND (

2246 "supporter"."member_id" NOTNULL OR

2247 "interest"."member_id" NOTNULL OR

2248 ( "membership"."member_id" NOTNULL AND

2249 "event"."event" IN (

2250 'issue_state_changed',

2251 'initiative_created_in_new_issue',

2252 'initiative_created_in_existing_issue',

2253 'initiative_revoked' ) ) )

2254 AND "ignored_member"."member_id" ISNULL

2255 AND "ignored_initiative"."member_id" ISNULL;

2256

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

2258

2259

2260 CREATE TYPE "timeline_event" AS ENUM (

2261 'issue_created',

2262 'issue_canceled',

2263 'issue_accepted',

2264 'issue_half_frozen',

2265 'issue_finished_without_voting',

2266 'issue_voting_started',

2267 'issue_finished_after_voting',

2268 'initiative_created',

2269 'initiative_revoked',

2270 'draft_created',

2271 'suggestion_created');

2272

2273 COMMENT ON TYPE "timeline_event" IS 'Types of event in timeline tables (DEPRECATED)';

2274

2275

2276 CREATE VIEW "timeline_issue" AS

2277 SELECT

2278 "created" AS "occurrence",

2279 'issue_created'::"timeline_event" AS "event",

2280 "id" AS "issue_id"

2281 FROM "issue"

2282 UNION ALL

2283 SELECT

2284 "closed" AS "occurrence",

2285 'issue_canceled'::"timeline_event" AS "event",

2286 "id" AS "issue_id"

2287 FROM "issue" WHERE "closed" NOTNULL AND "fully_frozen" ISNULL

2288 UNION ALL

2289 SELECT

2290 "accepted" AS "occurrence",

2291 'issue_accepted'::"timeline_event" AS "event",

2292 "id" AS "issue_id"

2293 FROM "issue" WHERE "accepted" NOTNULL

2294 UNION ALL

2295 SELECT

2296 "half_frozen" AS "occurrence",

2297 'issue_half_frozen'::"timeline_event" AS "event",

2298 "id" AS "issue_id"

2299 FROM "issue" WHERE "half_frozen" NOTNULL

2300 UNION ALL

2301 SELECT

2302 "fully_frozen" AS "occurrence",

2303 'issue_voting_started'::"timeline_event" AS "event",

2304 "id" AS "issue_id"

2305 FROM "issue"

2306 WHERE "fully_frozen" NOTNULL

2307 AND ("closed" ISNULL OR "closed" != "fully_frozen")

2308 UNION ALL

2309 SELECT

2310 "closed" AS "occurrence",

2311 CASE WHEN "fully_frozen" = "closed" THEN

2312 'issue_finished_without_voting'::"timeline_event"

2313 ELSE

2314 'issue_finished_after_voting'::"timeline_event"

2315 END AS "event",

2316 "id" AS "issue_id"

2317 FROM "issue" WHERE "closed" NOTNULL AND "fully_frozen" NOTNULL;

2318

2319 COMMENT ON VIEW "timeline_issue" IS 'Helper view for "timeline" view (DEPRECATED)';

2320

2321

2322 CREATE VIEW "timeline_initiative" AS

2323 SELECT

2324 "created" AS "occurrence",

2325 'initiative_created'::"timeline_event" AS "event",

2326 "id" AS "initiative_id"

2327 FROM "initiative"

2328 UNION ALL

2329 SELECT

2330 "revoked" AS "occurrence",

2331 'initiative_revoked'::"timeline_event" AS "event",

2332 "id" AS "initiative_id"

2333 FROM "initiative" WHERE "revoked" NOTNULL;

2334

2335 COMMENT ON VIEW "timeline_initiative" IS 'Helper view for "timeline" view (DEPRECATED)';

2336

2337

2338 CREATE VIEW "timeline_draft" AS

2339 SELECT

2340 "created" AS "occurrence",

2341 'draft_created'::"timeline_event" AS "event",

2342 "id" AS "draft_id"

2343 FROM "draft";

2344

2345 COMMENT ON VIEW "timeline_draft" IS 'Helper view for "timeline" view (DEPRECATED)';

2346

2347

2348 CREATE VIEW "timeline_suggestion" AS

2349 SELECT

2350 "created" AS "occurrence",

2351 'suggestion_created'::"timeline_event" AS "event",

2352 "id" AS "suggestion_id"

2353 FROM "suggestion";

2354

2355 COMMENT ON VIEW "timeline_suggestion" IS 'Helper view for "timeline" view (DEPRECATED)';

2356

2357

2358 CREATE VIEW "timeline" AS

2359 SELECT

2360 "occurrence",

2361 "event",

2362 "issue_id",

2363 NULL AS "initiative_id",

2364 NULL::INT8 AS "draft_id", -- TODO: Why do we need a type-cast here? Is this due to 32 bit architecture?

2365 NULL::INT8 AS "suggestion_id"

2366 FROM "timeline_issue"

2367 UNION ALL

2368 SELECT

2369 "occurrence",

2370 "event",

2371 NULL AS "issue_id",

2372 "initiative_id",

2373 NULL AS "draft_id",

2374 NULL AS "suggestion_id"

2375 FROM "timeline_initiative"

2376 UNION ALL

2377 SELECT

2378 "occurrence",

2379 "event",

2380 NULL AS "issue_id",

2381 NULL AS "initiative_id",

2382 "draft_id",

2383 NULL AS "suggestion_id"

2384 FROM "timeline_draft"

2385 UNION ALL

2386 SELECT

2387 "occurrence",

2388 "event",

2389 NULL AS "issue_id",

2390 NULL AS "initiative_id",

2391 NULL AS "draft_id",

2392 "suggestion_id"

2393 FROM "timeline_suggestion";

2394

2395 COMMENT ON VIEW "timeline" IS 'Aggregation of different events in the system (DEPRECATED)';

2396

2397

2398

2399 ------------------------------------------------------

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

2401 ------------------------------------------------------

2402

2403

2404 CREATE TYPE "delegation_chain_loop_tag" AS ENUM

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

2406

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

2408

2409

2410 CREATE TYPE "delegation_chain_row" AS (

2411 "index" INT4,

2412 "member_id" INT4,

2413 "member_valid" BOOLEAN,

2414 "participation" BOOLEAN,

2415 "overridden" BOOLEAN,

2416 "scope_in" "delegation_scope",

2417 "scope_out" "delegation_scope",

2418 "disabled_out" BOOLEAN,

2419 "loop" "delegation_chain_loop_tag" );

2420

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

2422

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

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

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

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

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

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

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

2430

2431

2432 CREATE FUNCTION "delegation_chain_for_closed_issue"

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

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

2435 RETURNS SETOF "delegation_chain_row"

2436 LANGUAGE 'plpgsql' STABLE AS $$

2437 DECLARE

2438 "output_row" "delegation_chain_row";

2439 "direct_voter_row" "direct_voter"%ROWTYPE;

2440 "delegating_voter_row" "delegating_voter"%ROWTYPE;

2441 BEGIN

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

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

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

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

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

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

2448 LOOP

2449 SELECT INTO "direct_voter_row" * FROM "direct_voter"

2450 WHERE "issue_id" = "issue_id_p"

2451 AND "member_id" = "output_row"."member_id";

2452 IF "direct_voter_row"."member_id" NOTNULL THEN

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

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

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

2456 RETURN NEXT "output_row";

2457 RETURN;

2458 END IF;

2459 SELECT INTO "delegating_voter_row" * FROM "delegating_voter"

2460 WHERE "issue_id" = "issue_id_p"

2461 AND "member_id" = "output_row"."member_id";

2462 IF "delegating_voter_row"."member_id" ISNULL THEN

2463 RETURN;

2464 END IF;

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

2466 RETURN NEXT "output_row";

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

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

2469 END LOOP;

2470 END;

2471 $$;

2472

2473 COMMENT ON FUNCTION "delegation_chain_for_closed_issue"

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

2475 "member"."id"%TYPE )

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

2477

2478

2479 CREATE FUNCTION "delegation_chain"

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

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

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

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

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

2485 "simulate_default_p" BOOLEAN DEFAULT FALSE )

2486 RETURNS SETOF "delegation_chain_row"

2487 LANGUAGE 'plpgsql' STABLE AS $$

2488 DECLARE

2489 "scope_v" "delegation_scope";

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

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

2492 "issue_row" "issue"%ROWTYPE;

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

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

2495 "output_row" "delegation_chain_row";

2496 "output_rows" "delegation_chain_row"[];

2497 "simulate_v" BOOLEAN;

2498 "simulate_here_v" BOOLEAN;

2499 "delegation_row" "delegation"%ROWTYPE;

2500 "row_count" INT4;

2501 "i" INT4;

2502 "loop_v" BOOLEAN;

2503 BEGIN

2504 IF "simulate_trustee_id_p" NOTNULL AND "simulate_default_p" THEN

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

2506 END IF;

2507 IF "simulate_trustee_id_p" NOTNULL OR "simulate_default_p" THEN

2508 "simulate_v" := TRUE;

2509 ELSE

2510 "simulate_v" := FALSE;

2511 END IF;

2512 IF

2513 "unit_id_p" NOTNULL AND

2514 "area_id_p" ISNULL AND

2515 "issue_id_p" ISNULL

2516 THEN

2517 "scope_v" := 'unit';

2518 "unit_id_v" := "unit_id_p";

2519 ELSIF

2520 "unit_id_p" ISNULL AND

2521 "area_id_p" NOTNULL AND

2522 "issue_id_p" ISNULL

2523 THEN

2524 "scope_v" := 'area';

2525 "area_id_v" := "area_id_p";

2526 SELECT "unit_id" INTO "unit_id_v"

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

2528 ELSIF

2529 "unit_id_p" ISNULL AND

2530 "area_id_p" ISNULL AND

2531 "issue_id_p" NOTNULL

2532 THEN

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

2534 IF "issue_row"."id" ISNULL THEN

2535 RETURN;

2536 END IF;

2537 IF "issue_row"."closed" NOTNULL THEN

2538 IF "simulate_v" THEN

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

2540 END IF;

2541 FOR "output_row" IN

2542 SELECT * FROM

2543 "delegation_chain_for_closed_issue"("member_id_p", "issue_id_p")

2544 LOOP

2545 RETURN NEXT "output_row";

2546 END LOOP;

2547 RETURN;

2548 END IF;

2549 "scope_v" := 'issue';

2550 SELECT "area_id" INTO "area_id_v"

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

2552 SELECT "unit_id" INTO "unit_id_v"

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

2554 ELSE

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

2556 END IF;

2557 "visited_member_ids" := '{}';

2558 "loop_member_id_v" := NULL;

2559 "output_rows" := '{}';

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

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

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

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

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

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

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

2567 LOOP

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

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

2570 ELSE

2571 "visited_member_ids" :=

2572 "visited_member_ids" || "output_row"."member_id";

2573 END IF;

2574 IF "output_row"."participation" ISNULL THEN

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

2576 ELSIF "output_row"."participation" THEN

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

2578 END IF;

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

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

2581 SELECT NULL FROM "member" JOIN "privilege"

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

2583 AND "privilege"."unit_id" = "unit_id_v"

2584 WHERE "id" = "output_row"."member_id"

2585 AND "member"."active" AND "privilege"."voting_right"

2586 );

2587 "simulate_here_v" := (

2588 "simulate_v" AND

2589 "output_row"."member_id" = "member_id_p"

2590 );

2591 "delegation_row" := ROW(NULL);

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

2593 IF "scope_v" = 'unit' THEN

2594 IF NOT "simulate_here_v" THEN

2595 SELECT * INTO "delegation_row" FROM "delegation"

2596 WHERE "truster_id" = "output_row"."member_id"

2597 AND "unit_id" = "unit_id_v";

2598 END IF;

2599 ELSIF "scope_v" = 'area' THEN

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

2601 SELECT NULL FROM "membership"

2602 WHERE "area_id" = "area_id_p"

2603 AND "member_id" = "output_row"."member_id"

2604 );

2605 IF "simulate_here_v" THEN

2606 IF "simulate_trustee_id_p" ISNULL THEN

2607 SELECT * INTO "delegation_row" FROM "delegation"

2608 WHERE "truster_id" = "output_row"."member_id"

2609 AND "unit_id" = "unit_id_v";

2610 END IF;

2611 ELSE

2612 SELECT * INTO "delegation_row" FROM "delegation"

2613 WHERE "truster_id" = "output_row"."member_id"

2614 AND (

2615 "unit_id" = "unit_id_v" OR

2616 "area_id" = "area_id_v"

2617 )

2618 ORDER BY "scope" DESC;

2619 END IF;

2620 ELSIF "scope_v" = 'issue' THEN

2621 IF "issue_row"."fully_frozen" ISNULL THEN

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

2623 SELECT NULL FROM "interest"

2624 WHERE "issue_id" = "issue_id_p"

2625 AND "member_id" = "output_row"."member_id"

2626 );

2627 ELSE

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

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

2630 SELECT NULL FROM "direct_voter"

2631 WHERE "issue_id" = "issue_id_p"

2632 AND "member_id" = "output_row"."member_id"

2633 );

2634 ELSE

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

2636 END IF;

2637 END IF;

2638 IF "simulate_here_v" THEN

2639 IF "simulate_trustee_id_p" ISNULL THEN

2640 SELECT * INTO "delegation_row" FROM "delegation"

2641 WHERE "truster_id" = "output_row"."member_id"

2642 AND (

2643 "unit_id" = "unit_id_v" OR

2644 "area_id" = "area_id_v"

2645 )

2646 ORDER BY "scope" DESC;

2647 END IF;

2648 ELSE

2649 SELECT * INTO "delegation_row" FROM "delegation"

2650 WHERE "truster_id" = "output_row"."member_id"

2651 AND (

2652 "unit_id" = "unit_id_v" OR

2653 "area_id" = "area_id_v" OR

2654 "issue_id" = "issue_id_p"

2655 )

2656 ORDER BY "scope" DESC;

2657 END IF;

2658 END IF;

2659 ELSE

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

2661 END IF;

2662 IF "simulate_here_v" AND "simulate_trustee_id_p" NOTNULL THEN

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

2664 "output_rows" := "output_rows" || "output_row";

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

2666 ELSIF "delegation_row"."trustee_id" NOTNULL THEN

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

2668 "output_rows" := "output_rows" || "output_row";

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

2670 ELSIF "delegation_row"."scope" NOTNULL THEN

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

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

2673 "output_rows" := "output_rows" || "output_row";

2674 EXIT;

2675 ELSE

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

2677 "output_rows" := "output_rows" || "output_row";

2678 EXIT;

2679 END IF;

2680 EXIT WHEN "loop_member_id_v" NOTNULL;

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

2682 END LOOP;

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

2684 "i" := 1;

2685 "loop_v" := FALSE;

2686 LOOP

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

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

2689 IF "loop_v" THEN

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

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

2692 ELSIF "i" = "row_count" THEN

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

2694 ELSE

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

2696 END IF;

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

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

2699 "loop_v" := TRUE;

2700 END IF;

2701 IF "scope_v" = 'unit' THEN

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

2703 END IF;

2704 RETURN NEXT "output_row";

2705 "i" := "i" + 1;

2706 END LOOP;

2707 RETURN;

2708 END;

2709 $$;

2710

2711 COMMENT ON FUNCTION "delegation_chain"

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

2713 "unit"."id"%TYPE,

2714 "area"."id"%TYPE,

2715 "issue"."id"%TYPE,

2716 "member"."id"%TYPE,

2717 BOOLEAN )

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

2719

2720

2721

2722 ---------------------------------------------------------

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

2724 ---------------------------------------------------------

2725

2726

2727 CREATE TYPE "delegation_info_loop_type" AS ENUM

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

2729

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

2731

2732

2733 CREATE TYPE "delegation_info_type" AS (

2734 "own_participation" BOOLEAN,

2735 "own_delegation_scope" "delegation_scope",

2736 "first_trustee_id" INT4,

2737 "first_trustee_participation" BOOLEAN,

2738 "first_trustee_ellipsis" BOOLEAN,

2739 "other_trustee_id" INT4,

2740 "other_trustee_participation" BOOLEAN,

2741 "other_trustee_ellipsis" BOOLEAN,

2742 "delegation_loop" "delegation_info_loop_type",

2743 "participating_member_id" INT4 );

2744

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

2746

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

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

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

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

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

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

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

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

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

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

2757

2758

2759 CREATE FUNCTION "delegation_info"

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

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

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

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

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

2765 "simulate_default_p" BOOLEAN DEFAULT FALSE )

2766 RETURNS "delegation_info_type"

2767 LANGUAGE 'plpgsql' STABLE AS $$

2768 DECLARE

2769 "current_row" "delegation_chain_row";

2770 "result" "delegation_info_type";

2771 BEGIN

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

2773 FOR "current_row" IN

2774 SELECT * FROM "delegation_chain"(

2775 "member_id_p",

2776 "unit_id_p", "area_id_p", "issue_id_p",

2777 "simulate_trustee_id_p", "simulate_default_p")

2778 LOOP

2779 IF

2780 "result"."participating_member_id" ISNULL AND

2781 "current_row"."participation"

2782 THEN

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

2784 END IF;

2785 IF "current_row"."member_id" = "member_id_p" THEN

2786 "result"."own_participation" := "current_row"."participation";

2787 "result"."own_delegation_scope" := "current_row"."scope_out";

2788 IF "current_row"."loop" = 'first' THEN

2789 "result"."delegation_loop" := 'own';

2790 END IF;

2791 ELSIF

2792 "current_row"."member_valid" AND

2793 ( "current_row"."loop" ISNULL OR

2794 "current_row"."loop" != 'repetition' )

2795 THEN

2796 IF "result"."first_trustee_id" ISNULL THEN

2797 "result"."first_trustee_id" := "current_row"."member_id";

2798 "result"."first_trustee_participation" := "current_row"."participation";

2799 "result"."first_trustee_ellipsis" := FALSE;

2800 IF "current_row"."loop" = 'first' THEN

2801 "result"."delegation_loop" := 'first';

2802 END IF;

2803 ELSIF "result"."other_trustee_id" ISNULL THEN

2804 IF "current_row"."participation" AND NOT "current_row"."overridden" THEN

2805 "result"."other_trustee_id" := "current_row"."member_id";

2806 "result"."other_trustee_participation" := TRUE;

2807 "result"."other_trustee_ellipsis" := FALSE;

2808 IF "current_row"."loop" = 'first' THEN

2809 "result"."delegation_loop" := 'other';

2810 END IF;

2811 ELSE

2812 "result"."first_trustee_ellipsis" := TRUE;

2813 IF "current_row"."loop" = 'first' THEN

2814 "result"."delegation_loop" := 'first_ellipsis';

2815 END IF;

2816 END IF;

2817 ELSE

2818 "result"."other_trustee_ellipsis" := TRUE;

2819 IF "current_row"."loop" = 'first' THEN

2820 "result"."delegation_loop" := 'other_ellipsis';

2821 END IF;

2822 END IF;

2823 END IF;

2824 END LOOP;

2825 RETURN "result";

2826 END;

2827 $$;

2828

2829 COMMENT ON FUNCTION "delegation_info"

2830 ( "member"."id"%TYPE,

2831 "unit"."id"%TYPE,

2832 "area"."id"%TYPE,

2833 "issue"."id"%TYPE,

2834 "member"."id"%TYPE,

2835 BOOLEAN )

2836 IS 'Notable information about a delegation chain for unit, area, or issue; See "delegation_info_type" for more information';

2837

2838

2839

2840 ------------------------------

2841 -- Comparison by vote count --

2842 ------------------------------

2843

2844 CREATE FUNCTION "vote_ratio"

2845 ( "positive_votes_p" "initiative"."positive_votes"%TYPE,

2846 "negative_votes_p" "initiative"."negative_votes"%TYPE )

2847 RETURNS FLOAT8

2848 LANGUAGE 'plpgsql' STABLE AS $$

2849 BEGIN

2850 IF "positive_votes_p" > 0 AND "negative_votes_p" > 0 THEN

2851 RETURN

2852 "positive_votes_p"::FLOAT8 /

2853 ("positive_votes_p" + "negative_votes_p")::FLOAT8;

2854 ELSIF "positive_votes_p" > 0 THEN

2855 RETURN "positive_votes_p";

2856 ELSIF "negative_votes_p" > 0 THEN

2857 RETURN 1 - "negative_votes_p";

2858 ELSE

2859 RETURN 0.5;

2860 END IF;

2861 END;

2862 $$;

2863

2864 COMMENT ON FUNCTION "vote_ratio"

2865 ( "initiative"."positive_votes"%TYPE,

2866 "initiative"."negative_votes"%TYPE )

2867 IS 'Returns a number, which can be used for comparison of initiatives based on count of approvals and disapprovals. Greater numbers indicate a better result. This function is NOT injective.';

2868

2869

2870

2871 ------------------------------------------------

2872 -- Locking for snapshots and voting procedure --

2873 ------------------------------------------------

2874

2875

2876 CREATE FUNCTION "share_row_lock_issue_trigger"()

2877 RETURNS TRIGGER

2878 LANGUAGE 'plpgsql' VOLATILE AS $$

2879 BEGIN

2880 IF TG_OP = 'UPDATE' OR TG_OP = 'DELETE' THEN

2881 PERFORM NULL FROM "issue" WHERE "id" = OLD."issue_id" FOR SHARE;

2882 END IF;

2883 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN

2884 PERFORM NULL FROM "issue" WHERE "id" = NEW."issue_id" FOR SHARE;

2885 RETURN NEW;

2886 ELSE

2887 RETURN OLD;

2888 END IF;

2889 END;

2890 $$;

2891

2892 COMMENT ON FUNCTION "share_row_lock_issue_trigger"() IS 'Implementation of triggers "share_row_lock_issue" on multiple tables';

2893

2894

2895 CREATE FUNCTION "share_row_lock_issue_via_initiative_trigger"()

2896 RETURNS TRIGGER

2897 LANGUAGE 'plpgsql' VOLATILE AS $$

2898 BEGIN

2899 IF TG_OP = 'UPDATE' OR TG_OP = 'DELETE' THEN

2900 PERFORM NULL FROM "issue"

2901 JOIN "initiative" ON "issue"."id" = "initiative"."issue_id"

2902 WHERE "initiative"."id" = OLD."initiative_id"

2903 FOR SHARE OF "issue";

2904 END IF;

2905 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN

2906 PERFORM NULL FROM "issue"

2907 JOIN "initiative" ON "issue"."id" = "initiative"."issue_id"

2908 WHERE "initiative"."id" = NEW."initiative_id"

2909 FOR SHARE OF "issue";

2910 RETURN NEW;

2911 ELSE

2912 RETURN OLD;

2913 END IF;

2914 END;

2915 $$;

2916

2917 COMMENT ON FUNCTION "share_row_lock_issue_trigger"() IS 'Implementation of trigger "share_row_lock_issue_via_initiative" on table "opinion"';

2918

2919

2920 CREATE TRIGGER "share_row_lock_issue"

2921 BEFORE INSERT OR UPDATE OR DELETE ON "initiative"

2922 FOR EACH ROW EXECUTE PROCEDURE

2923 "share_row_lock_issue_trigger"();

2924

2925 CREATE TRIGGER "share_row_lock_issue"

2926 BEFORE INSERT OR UPDATE OR DELETE ON "interest"

2927 FOR EACH ROW EXECUTE PROCEDURE

2928 "share_row_lock_issue_trigger"();

2929

2930 CREATE TRIGGER "share_row_lock_issue"

2931 BEFORE INSERT OR UPDATE OR DELETE ON "supporter"

2932 FOR EACH ROW EXECUTE PROCEDURE

2933 "share_row_lock_issue_trigger"();

2934

2935 CREATE TRIGGER "share_row_lock_issue_via_initiative"

2936 BEFORE INSERT OR UPDATE OR DELETE ON "opinion"

2937 FOR EACH ROW EXECUTE PROCEDURE

2938 "share_row_lock_issue_via_initiative_trigger"();

2939

2940 CREATE TRIGGER "share_row_lock_issue"

2941 BEFORE INSERT OR UPDATE OR DELETE ON "direct_voter"

2942 FOR EACH ROW EXECUTE PROCEDURE

2943 "share_row_lock_issue_trigger"();

2944

2945 CREATE TRIGGER "share_row_lock_issue"

2946 BEFORE INSERT OR UPDATE OR DELETE ON "delegating_voter"

2947 FOR EACH ROW EXECUTE PROCEDURE

2948 "share_row_lock_issue_trigger"();

2949

2950 CREATE TRIGGER "share_row_lock_issue"

2951 BEFORE INSERT OR UPDATE OR DELETE ON "vote"

2952 FOR EACH ROW EXECUTE PROCEDURE

2953 "share_row_lock_issue_trigger"();

2954

2955 COMMENT ON TRIGGER "share_row_lock_issue" ON "initiative" IS 'See "lock_issue" function';

2956 COMMENT ON TRIGGER "share_row_lock_issue" ON "interest" IS 'See "lock_issue" function';

2957 COMMENT ON TRIGGER "share_row_lock_issue" ON "supporter" IS 'See "lock_issue" function';

2958 COMMENT ON TRIGGER "share_row_lock_issue_via_initiative" ON "opinion" IS 'See "lock_issue" function';

2959 COMMENT ON TRIGGER "share_row_lock_issue" ON "direct_voter" IS 'See "lock_issue" function';

2960 COMMENT ON TRIGGER "share_row_lock_issue" ON "delegating_voter" IS 'See "lock_issue" function';

2961 COMMENT ON TRIGGER "share_row_lock_issue" ON "vote" IS 'See "lock_issue" function';

2962

2963

2964 CREATE FUNCTION "lock_issue"

2965 ( "issue_id_p" "issue"."id"%TYPE )

2966 RETURNS VOID

2967 LANGUAGE 'plpgsql' VOLATILE AS $$

2968 BEGIN

2969 LOCK TABLE "member" IN SHARE MODE;

2970 LOCK TABLE "privilege" IN SHARE MODE;

2971 LOCK TABLE "membership" IN SHARE MODE;

2972 LOCK TABLE "policy" IN SHARE MODE;

2973 PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE;

2974 -- NOTE: The row-level exclusive lock in combination with the

2975 -- share_row_lock_issue(_via_initiative)_trigger functions (which

2976 -- acquire a row-level share lock on the issue) ensure that no data

2977 -- is changed, which could affect calculation of snapshots or

2978 -- counting of votes. Table "delegation" must be table-level-locked,

2979 -- as it also contains issue- and global-scope delegations.

2980 LOCK TABLE "delegation" IN SHARE MODE;

2981 LOCK TABLE "direct_population_snapshot" IN EXCLUSIVE MODE;

2982 LOCK TABLE "delegating_population_snapshot" IN EXCLUSIVE MODE;

2983 LOCK TABLE "direct_interest_snapshot" IN EXCLUSIVE MODE;

2984 LOCK TABLE "delegating_interest_snapshot" IN EXCLUSIVE MODE;

2985 LOCK TABLE "direct_supporter_snapshot" IN EXCLUSIVE MODE;

2986 RETURN;

2987 END;

2988 $$;

2989

2990 COMMENT ON FUNCTION "lock_issue"

2991 ( "issue"."id"%TYPE )

2992 IS 'Locks the issue and all other data which is used for calculating snapshots or counting votes.';

2993

2994

2995

2996 ------------------------------------------------------------------------

2997 -- Regular tasks, except calculcation of snapshots and voting results --

2998 ------------------------------------------------------------------------

2999

3000 CREATE FUNCTION "check_activity"()

3001 RETURNS VOID

3002 LANGUAGE 'plpgsql' VOLATILE AS $$

3003 DECLARE

3004 "system_setting_row" "system_setting"%ROWTYPE;

3005 BEGIN

3006 SELECT * INTO "system_setting_row" FROM "system_setting";

3007 LOCK TABLE "member" IN SHARE ROW EXCLUSIVE MODE;

3008 IF "system_setting_row"."member_ttl" NOTNULL THEN

3009 UPDATE "member" SET "active" = FALSE

3010 WHERE "active" = TRUE

3011 AND "last_activity" < (now() - "system_setting_row"."member_ttl")::DATE;

3012 END IF;

3013 RETURN;

3014 END;

3015 $$;

3016

3017 COMMENT ON FUNCTION "check_activity"() IS 'Deactivates members when "last_activity" is older than "system_setting"."member_ttl".';

3018

3019

3020 CREATE FUNCTION "calculate_member_counts"()

3021 RETURNS VOID

3022 LANGUAGE 'plpgsql' VOLATILE AS $$

3023 BEGIN

3024 LOCK TABLE "member" IN SHARE MODE;

3025 LOCK TABLE "member_count" IN EXCLUSIVE MODE;

3026 LOCK TABLE "unit" IN EXCLUSIVE MODE;

3027 LOCK TABLE "area" IN EXCLUSIVE MODE;

3028 LOCK TABLE "privilege" IN SHARE MODE;

3029 LOCK TABLE "membership" IN SHARE MODE;

3030 DELETE FROM "member_count";

3031 INSERT INTO "member_count" ("total_count")

3032 SELECT "total_count" FROM "member_count_view";

3033 UPDATE "unit" SET "member_count" = "view"."member_count"

3034 FROM "unit_member_count" AS "view"

3035 WHERE "view"."unit_id" = "unit"."id";

3036 UPDATE "area" SET

3037 "direct_member_count" = "view"."direct_member_count",

3038 "member_weight" = "view"."member_weight"

3039 FROM "area_member_count" AS "view"

3040 WHERE "view"."area_id" = "area"."id";

3041 RETURN;

3042 END;

3043 $$;

3044

3045 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"';

3046

3047

3048

3049 ------------------------------

3050 -- Calculation of snapshots --

3051 ------------------------------

3052

3053 CREATE FUNCTION "weight_of_added_delegations_for_population_snapshot"

3054 ( "issue_id_p" "issue"."id"%TYPE,

3055 "member_id_p" "member"."id"%TYPE,

3056 "delegate_member_ids_p" "delegating_population_snapshot"."delegate_member_ids"%TYPE )

3057 RETURNS "direct_population_snapshot"."weight"%TYPE

3058 LANGUAGE 'plpgsql' VOLATILE AS $$

3059 DECLARE

3060 "issue_delegation_row" "issue_delegation"%ROWTYPE;

3061 "delegate_member_ids_v" "delegating_population_snapshot"."delegate_member_ids"%TYPE;

3062 "weight_v" INT4;

3063 "sub_weight_v" INT4;

3064 BEGIN

3065 "weight_v" := 0;

3066 FOR "issue_delegation_row" IN

3067 SELECT * FROM "issue_delegation"

3068 WHERE "trustee_id" = "member_id_p"

3069 AND "issue_id" = "issue_id_p"

3070 LOOP

3071 IF NOT EXISTS (

3072 SELECT NULL FROM "direct_population_snapshot"

3073 WHERE "issue_id" = "issue_id_p"

3074 AND "event" = 'periodic'

3075 AND "member_id" = "issue_delegation_row"."truster_id"

3076 ) AND NOT EXISTS (

3077 SELECT NULL FROM "delegating_population_snapshot"

3078 WHERE "issue_id" = "issue_id_p"

3079 AND "event" = 'periodic'

3080 AND "member_id" = "issue_delegation_row"."truster_id"

3081 ) THEN

3082 "delegate_member_ids_v" :=

3083 "member_id_p" || "delegate_member_ids_p";

3084 INSERT INTO "delegating_population_snapshot" (

3085 "issue_id",

3086 "event",

3087 "member_id",

3088 "scope",

3089 "delegate_member_ids"

3090 ) VALUES (

3091 "issue_id_p",

3092 'periodic',

3093 "issue_delegation_row"."truster_id",

3094 "issue_delegation_row"."scope",

3095 "delegate_member_ids_v"

3096 );

3097 "sub_weight_v" := 1 +

3098 "weight_of_added_delegations_for_population_snapshot"(

3099 "issue_id_p",

3100 "issue_delegation_row"."truster_id",

3101 "delegate_member_ids_v"

3102 );

3103 UPDATE "delegating_population_snapshot"

3104 SET "weight" = "sub_weight_v"

3105 WHERE "issue_id" = "issue_id_p"

3106 AND "event" = 'periodic'

3107 AND "member_id" = "issue_delegation_row"."truster_id";

3108 "weight_v" := "weight_v" + "sub_weight_v";

3109 END IF;

3110 END LOOP;

3111 RETURN "weight_v";

3112 END;

3113 $$;

3114

3115 COMMENT ON FUNCTION "weight_of_added_delegations_for_population_snapshot"

3116 ( "issue"."id"%TYPE,

3117 "member"."id"%TYPE,

3118 "delegating_population_snapshot"."delegate_member_ids"%TYPE )

3119 IS 'Helper function for "create_population_snapshot" function';

3120

3121

3122 CREATE FUNCTION "create_population_snapshot"

3123 ( "issue_id_p" "issue"."id"%TYPE )

3124 RETURNS VOID

3125 LANGUAGE 'plpgsql' VOLATILE AS $$

3126 DECLARE

3127 "member_id_v" "member"."id"%TYPE;

3128 BEGIN

3129 DELETE FROM "direct_population_snapshot"

3130 WHERE "issue_id" = "issue_id_p"

3131 AND "event" = 'periodic';

3132 DELETE FROM "delegating_population_snapshot"

3133 WHERE "issue_id" = "issue_id_p"

3134 AND "event" = 'periodic';

3135 INSERT INTO "direct_population_snapshot"

3136 ("issue_id", "event", "member_id")

3137 SELECT

3138 "issue_id_p" AS "issue_id",

3139 'periodic'::"snapshot_event" AS "event",

3140 "member"."id" AS "member_id"

3141 FROM "issue"

3142 JOIN "area" ON "issue"."area_id" = "area"."id"

3143 JOIN "membership" ON "area"."id" = "membership"."area_id"

3144 JOIN "member" ON "membership"."member_id" = "member"."id"

3145 JOIN "privilege"

3146 ON "privilege"."unit_id" = "area"."unit_id"

3147 AND "privilege"."member_id" = "member"."id"

3148 WHERE "issue"."id" = "issue_id_p"

3149 AND "member"."active" AND "privilege"."voting_right"

3150 UNION

3151 SELECT

3152 "issue_id_p" AS "issue_id",

3153 'periodic'::"snapshot_event" AS "event",

3154 "member"."id" AS "member_id"

3155 FROM "issue"

3156 JOIN "area" ON "issue"."area_id" = "area"."id"

3157 JOIN "interest" ON "issue"."id" = "interest"."issue_id"

3158 JOIN "member" ON "interest"."member_id" = "member"."id"

3159 JOIN "privilege"

3160 ON "privilege"."unit_id" = "area"."unit_id"

3161 AND "privilege"."member_id" = "member"."id"

3162 WHERE "issue"."id" = "issue_id_p"

3163 AND "member"."active" AND "privilege"."voting_right";

3164 FOR "member_id_v" IN

3165 SELECT "member_id" FROM "direct_population_snapshot"

3166 WHERE "issue_id" = "issue_id_p"

3167 AND "event" = 'periodic'

3168 LOOP

3169 UPDATE "direct_population_snapshot" SET

3170 "weight" = 1 +

3171 "weight_of_added_delegations_for_population_snapshot"(

3172 "issue_id_p",

3173 "member_id_v",

3174 '{}'

3175 )

3176 WHERE "issue_id" = "issue_id_p"

3177 AND "event" = 'periodic'

3178 AND "member_id" = "member_id_v";

3179 END LOOP;

3180 RETURN;

3181 END;

3182 $$;

3183

3184 COMMENT ON FUNCTION "create_population_snapshot"

3185 ( "issue"."id"%TYPE )

3186 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.';

3187

3188

3189 CREATE FUNCTION "weight_of_added_delegations_for_interest_snapshot"

3190 ( "issue_id_p" "issue"."id"%TYPE,

3191 "member_id_p" "member"."id"%TYPE,

3192 "delegate_member_ids_p" "delegating_interest_snapshot"."delegate_member_ids"%TYPE )

3193 RETURNS "direct_interest_snapshot"."weight"%TYPE

3194 LANGUAGE 'plpgsql' VOLATILE AS $$

3195 DECLARE

3196 "issue_delegation_row" "issue_delegation"%ROWTYPE;

3197 "delegate_member_ids_v" "delegating_interest_snapshot"."delegate_member_ids"%TYPE;

3198 "weight_v" INT4;

3199 "sub_weight_v" INT4;

3200 BEGIN

3201 "weight_v" := 0;

3202 FOR "issue_delegation_row" IN

3203 SELECT * FROM "issue_delegation"

3204 WHERE "trustee_id" = "member_id_p"

3205 AND "issue_id" = "issue_id_p"

3206 LOOP

3207 IF NOT EXISTS (

3208 SELECT NULL FROM "direct_interest_snapshot"

3209 WHERE "issue_id" = "issue_id_p"

3210 AND "event" = 'periodic'

3211 AND "member_id" = "issue_delegation_row"."truster_id"

3212 ) AND NOT EXISTS (

3213 SELECT NULL FROM "delegating_interest_snapshot"

3214 WHERE "issue_id" = "issue_id_p"

3215 AND "event" = 'periodic'

3216 AND "member_id" = "issue_delegation_row"."truster_id"

3217 ) THEN

3218 "delegate_member_ids_v" :=

3219 "member_id_p" || "delegate_member_ids_p";

3220 INSERT INTO "delegating_interest_snapshot" (

3221 "issue_id",

3222 "event",

3223 "member_id",

3224 "scope",

3225 "delegate_member_ids"

3226 ) VALUES (

3227 "issue_id_p",

3228 'periodic',

3229 "issue_delegation_row"."truster_id",

3230 "issue_delegation_row"."scope",

3231 "delegate_member_ids_v"

3232 );

3233 "sub_weight_v" := 1 +

3234 "weight_of_added_delegations_for_interest_snapshot"(

3235 "issue_id_p",

3236 "issue_delegation_row"."truster_id",

3237 "delegate_member_ids_v"

3238 );

3239 UPDATE "delegating_interest_snapshot"

3240 SET "weight" = "sub_weight_v"

3241 WHERE "issue_id" = "issue_id_p"

3242 AND "event" = 'periodic'

3243 AND "member_id" = "issue_delegation_row"."truster_id";

3244 "weight_v" := "weight_v" + "sub_weight_v";

3245 END IF;

3246 END LOOP;

3247 RETURN "weight_v";

3248 END;

3249 $$;

3250

3251 COMMENT ON FUNCTION "weight_of_added_delegations_for_interest_snapshot"

3252 ( "issue"."id"%TYPE,

3253 "member"."id"%TYPE,

3254 "delegating_interest_snapshot"."delegate_member_ids"%TYPE )

3255 IS 'Helper function for "create_interest_snapshot" function';

3256

3257

3258 CREATE FUNCTION "create_interest_snapshot"

3259 ( "issue_id_p" "issue"."id"%TYPE )

3260 RETURNS VOID

3261 LANGUAGE 'plpgsql' VOLATILE AS $$

3262 DECLARE

3263 "member_id_v" "member"."id"%TYPE;

3264 BEGIN

3265 DELETE FROM "direct_interest_snapshot"

3266 WHERE "issue_id" = "issue_id_p"

3267 AND "event" = 'periodic';

3268 DELETE FROM "delegating_interest_snapshot"

3269 WHERE "issue_id" = "issue_id_p"

3270 AND "event" = 'periodic';

3271 DELETE FROM "direct_supporter_snapshot"

3272 WHERE "issue_id" = "issue_id_p"

3273 AND "event" = 'periodic';

3274 INSERT INTO "direct_interest_snapshot"

3275 ("issue_id", "event", "member_id")

3276 SELECT

3277 "issue_id_p" AS "issue_id",

3278 'periodic' AS "event",

3279 "member"."id" AS "member_id"

3280 FROM "issue"

3281 JOIN "area" ON "issue"."area_id" = "area"."id"

3282 JOIN "interest" ON "issue"."id" = "interest"."issue_id"

3283 JOIN "member" ON "interest"."member_id" = "member"."id"

3284 JOIN "privilege"

3285 ON "privilege"."unit_id" = "area"."unit_id"

3286 AND "privilege"."member_id" = "member"."id"

3287 WHERE "issue"."id" = "issue_id_p"

3288 AND "member"."active" AND "privilege"."voting_right";

3289 FOR "member_id_v" IN

3290 SELECT "member_id" FROM "direct_interest_snapshot"

3291 WHERE "issue_id" = "issue_id_p"

3292 AND "event" = 'periodic'

3293 LOOP

3294 UPDATE "direct_interest_snapshot" SET

3295 "weight" = 1 +

3296 "weight_of_added_delegations_for_interest_snapshot"(

3297 "issue_id_p",

3298 "member_id_v",

3299 '{}'

3300 )

3301 WHERE "issue_id" = "issue_id_p"

3302 AND "event" = 'periodic'

3303 AND "member_id" = "member_id_v";

3304 END LOOP;

3305 INSERT INTO "direct_supporter_snapshot"

3306 ( "issue_id", "initiative_id", "event", "member_id",

3307 "draft_id", "informed", "satisfied" )

3308 SELECT

3309 "issue_id_p" AS "issue_id",

3310 "initiative"."id" AS "initiative_id",

3311 'periodic' AS "event",

3312 "supporter"."member_id" AS "member_id",

3313 "supporter"."draft_id" AS "draft_id",

3314 "supporter"."draft_id" = "current_draft"."id" AS "informed",

3315 NOT EXISTS (

3316 SELECT NULL FROM "critical_opinion"

3317 WHERE "initiative_id" = "initiative"."id"

3318 AND "member_id" = "supporter"."member_id"

3319 ) AS "satisfied"

3320 FROM "initiative"

3321 JOIN "supporter"

3322 ON "supporter"."initiative_id" = "initiative"."id"

3323 JOIN "current_draft"

3324 ON "initiative"."id" = "current_draft"."initiative_id"

3325 JOIN "direct_interest_snapshot"

3326 ON "supporter"."member_id" = "direct_interest_snapshot"."member_id"

3327 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"

3328 AND "event" = 'periodic'

3329 WHERE "initiative"."issue_id" = "issue_id_p";

3330 RETURN;

3331 END;

3332 $$;

3333

3334 COMMENT ON FUNCTION "create_interest_snapshot"

3335 ( "issue"."id"%TYPE )

3336 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.';

3337

3338

3339 CREATE FUNCTION "create_snapshot"

3340 ( "issue_id_p" "issue"."id"%TYPE )

3341 RETURNS VOID

3342 LANGUAGE 'plpgsql' VOLATILE AS $$

3343 DECLARE

3344 "initiative_id_v" "initiative"."id"%TYPE;

3345 "suggestion_id_v" "suggestion"."id"%TYPE;

3346 BEGIN

3347 PERFORM "lock_issue"("issue_id_p");

3348 PERFORM "create_population_snapshot"("issue_id_p");

3349 PERFORM "create_interest_snapshot"("issue_id_p");

3350 UPDATE "issue" SET

3351 "snapshot" = now(),

3352 "latest_snapshot_event" = 'periodic',

3353 "population" = (

3354 SELECT coalesce(sum("weight"), 0)

3355 FROM "direct_population_snapshot"

3356 WHERE "issue_id" = "issue_id_p"

3357 AND "event" = 'periodic'

3358 )

3359 WHERE "id" = "issue_id_p";

3360 FOR "initiative_id_v" IN

3361 SELECT "id" FROM "initiative" WHERE "issue_id" = "issue_id_p"

3362 LOOP

3363 UPDATE "initiative" SET

3364 "supporter_count" = (

3365 SELECT coalesce(sum("di"."weight"), 0)

3366 FROM "direct_interest_snapshot" AS "di"

3367 JOIN "direct_supporter_snapshot" AS "ds"

3368 ON "di"."member_id" = "ds"."member_id"

3369 WHERE "di"."issue_id" = "issue_id_p"

3370 AND "di"."event" = 'periodic'

3371 AND "ds"."initiative_id" = "initiative_id_v"

3372 AND "ds"."event" = 'periodic'

3373 ),

3374 "informed_supporter_count" = (

3375 SELECT coalesce(sum("di"."weight"), 0)

3376 FROM "direct_interest_snapshot" AS "di"

3377 JOIN "direct_supporter_snapshot" AS "ds"

3378 ON "di"."member_id" = "ds"."member_id"

3379 WHERE "di"."issue_id" = "issue_id_p"

3380 AND "di"."event" = 'periodic'

3381 AND "ds"."initiative_id" = "initiative_id_v"

3382 AND "ds"."event" = 'periodic'

3383 AND "ds"."informed"

3384 ),

3385 "satisfied_supporter_count" = (

3386 SELECT coalesce(sum("di"."weight"), 0)

3387 FROM "direct_interest_snapshot" AS "di"

3388 JOIN "direct_supporter_snapshot" AS "ds"

3389 ON "di"."member_id" = "ds"."member_id"

3390 WHERE "di"."issue_id" = "issue_id_p"

3391 AND "di"."event" = 'periodic'

3392 AND "ds"."initiative_id" = "initiative_id_v"

3393 AND "ds"."event" = 'periodic'

3394 AND "ds"."satisfied"

3395 ),

3396 "satisfied_informed_supporter_count" = (

3397 SELECT coalesce(sum("di"."weight"), 0)

3398 FROM "direct_interest_snapshot" AS "di"

3399 JOIN "direct_supporter_snapshot" AS "ds"

3400 ON "di"."member_id" = "ds"."member_id"

3401 WHERE "di"."issue_id" = "issue_id_p"

3402 AND "di"."event" = 'periodic'

3403 AND "ds"."initiative_id" = "initiative_id_v"

3404 AND "ds"."event" = 'periodic'

3405 AND "ds"."informed"

3406 AND "ds"."satisfied"

3407 )

3408 WHERE "id" = "initiative_id_v";

3409 FOR "suggestion_id_v" IN

3410 SELECT "id" FROM "suggestion"

3411 WHERE "initiative_id" = "initiative_id_v"

3412 LOOP

3413 UPDATE "suggestion" SET

3414 "minus2_unfulfilled_count" = (

3415 SELECT coalesce(sum("snapshot"."weight"), 0)

3416 FROM "issue" CROSS JOIN "opinion"

3417 JOIN "direct_interest_snapshot" AS "snapshot"

3418 ON "snapshot"."issue_id" = "issue"."id"

3419 AND "snapshot"."event" = "issue"."latest_snapshot_event"

3420 AND "snapshot"."member_id" = "opinion"."member_id"

3421 WHERE "issue"."id" = "issue_id_p"

3422 AND "opinion"."suggestion_id" = "suggestion_id_v"

3423 AND "opinion"."degree" = -2

3424 AND "opinion"."fulfilled" = FALSE

3425 ),

3426 "minus2_fulfilled_count" = (

3427 SELECT coalesce(sum("snapshot"."weight"), 0)

3428 FROM "issue" CROSS JOIN "opinion"

3429 JOIN "direct_interest_snapshot" AS "snapshot"

3430 ON "snapshot"."issue_id" = "issue"."id"

3431 AND "snapshot"."event" = "issue"."latest_snapshot_event"

3432 AND "snapshot"."member_id" = "opinion"."member_id"

3433 WHERE "issue"."id" = "issue_id_p"

3434 AND "opinion"."suggestion_id" = "suggestion_id_v"

3435 AND "opinion"."degree" = -2

3436 AND "opinion"."fulfilled" = TRUE

3437 ),

3438 "minus1_unfulfilled_count" = (

3439 SELECT coalesce(sum("snapshot"."weight"), 0)

3440 FROM "issue" CROSS JOIN "opinion"

3441 JOIN "direct_interest_snapshot" AS "snapshot"

3442 ON "snapshot"."issue_id" = "issue"."id"

3443 AND "snapshot"."event" = "issue"."latest_snapshot_event"

3444 AND "snapshot"."member_id" = "opinion"."member_id"

3445 WHERE "issue"."id" = "issue_id_p"

3446 AND "opinion"."suggestion_id" = "suggestion_id_v"

3447 AND "opinion"."degree" = -1

3448 AND "opinion"."fulfilled" = FALSE

3449 ),

3450 "minus1_fulfilled_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" = -1

3460 AND "opinion"."fulfilled" = TRUE

3461 ),

3462 "plus1_unfulfilled_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" = 1

3472 AND "opinion"."fulfilled" = FALSE

3473 ),

3474 "plus1_fulfilled_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" = TRUE

3485 ),

3486 "plus2_unfulfilled_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" = 2

3496 AND "opinion"."fulfilled" = FALSE

3497 ),

3498 "plus2_fulfilled_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" = 2

3508 AND "opinion"."fulfilled" = TRUE

3509 )

3510 WHERE "suggestion"."id" = "suggestion_id_v";

3511 END LOOP;

3512 END LOOP;

3513 RETURN;

3514 END;

3515 $$;

3516

3517 COMMENT ON FUNCTION "create_snapshot"

3518 ( "issue"."id"%TYPE )

3519 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.';

3520

3521

3522 CREATE FUNCTION "set_snapshot_event"

3523 ( "issue_id_p" "issue"."id"%TYPE,

3524 "event_p" "snapshot_event" )

3525 RETURNS VOID

3526 LANGUAGE 'plpgsql' VOLATILE AS $$

3527 DECLARE

3528 "event_v" "issue"."latest_snapshot_event"%TYPE;

3529 BEGIN

3530 SELECT "latest_snapshot_event" INTO "event_v" FROM "issue"

3531 WHERE "id" = "issue_id_p" FOR UPDATE;

3532 UPDATE "issue" SET "latest_snapshot_event" = "event_p"

3533 WHERE "id" = "issue_id_p";

3534 UPDATE "direct_population_snapshot" SET "event" = "event_p"

3535 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";

3536 UPDATE "delegating_population_snapshot" SET "event" = "event_p"

3537 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";

3538 UPDATE "direct_interest_snapshot" SET "event" = "event_p"

3539 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";

3540 UPDATE "delegating_interest_snapshot" SET "event" = "event_p"

3541 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";

3542 UPDATE "direct_supporter_snapshot" SET "event" = "event_p"

3543 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";

3544 RETURN;

3545 END;

3546 $$;

3547

3548 COMMENT ON FUNCTION "set_snapshot_event"

3549 ( "issue"."id"%TYPE,

3550 "snapshot_event" )

3551 IS 'Change "event" attribute of the previous ''periodic'' snapshot';

3552

3553

3554

3555 ---------------------

3556 -- Freezing issues --

3557 ---------------------

3558

3559 CREATE FUNCTION "freeze_after_snapshot"

3560 ( "issue_id_p" "issue"."id"%TYPE )

3561 RETURNS VOID

3562 LANGUAGE 'plpgsql' VOLATILE AS $$

3563 DECLARE

3564 "issue_row" "issue"%ROWTYPE;

3565 "policy_row" "policy"%ROWTYPE;

3566 "initiative_row" "initiative"%ROWTYPE;

3567 BEGIN

3568 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";

3569 SELECT * INTO "policy_row"

3570 FROM "policy" WHERE "id" = "issue_row"."policy_id";

3571 PERFORM "set_snapshot_event"("issue_id_p", 'full_freeze');

3572 FOR "initiative_row" IN

3573 SELECT * FROM "initiative"

3574 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL

3575 LOOP

3576 IF

3577 "initiative_row"."polling" OR (

3578 "initiative_row"."satisfied_supporter_count" > 0 AND

3579 "initiative_row"."satisfied_supporter_count" *

3580 "policy_row"."initiative_quorum_den" >=

3581 "issue_row"."population" * "policy_row"."initiative_quorum_num"

3582 )

3583 THEN

3584 UPDATE "initiative" SET "admitted" = TRUE

3585 WHERE "id" = "initiative_row"."id";

3586 ELSE

3587 UPDATE "initiative" SET "admitted" = FALSE

3588 WHERE "id" = "initiative_row"."id";

3589 END IF;

3590 END LOOP;

3591 IF EXISTS (

3592 SELECT NULL FROM "initiative"

3593 WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE

3594 ) THEN

3595 UPDATE "issue" SET

3596 "state" = 'voting',

3597 "accepted" = coalesce("accepted", now()),

3598 "half_frozen" = coalesce("half_frozen", now()),

3599 "fully_frozen" = now()

3600 WHERE "id" = "issue_id_p";

3601 ELSE

3602 UPDATE "issue" SET

3603 "state" = 'canceled_no_initiative_admitted',

3604 "accepted" = coalesce("accepted", now()),

3605 "half_frozen" = coalesce("half_frozen", now()),

3606 "fully_frozen" = now(),

3607 "closed" = now(),

3608 "ranks_available" = TRUE

3609 WHERE "id" = "issue_id_p";

3610 -- NOTE: The following DELETE statements have effect only when

3611 -- issue state has been manipulated

3612 DELETE FROM "direct_voter" WHERE "issue_id" = "issue_id_p";

3613 DELETE FROM "delegating_voter" WHERE "issue_id" = "issue_id_p";

3614 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";

3615 END IF;

3616 RETURN;

3617 END;

3618 $$;

3619

3620 COMMENT ON FUNCTION "freeze_after_snapshot"

3621 ( "issue"."id"%TYPE )

3622 IS 'This function freezes an issue (fully) and starts voting, but must only be called when "create_snapshot" was called in the same transaction.';

3623

3624

3625 CREATE FUNCTION "manual_freeze"("issue_id_p" "issue"."id"%TYPE)

3626 RETURNS VOID

3627 LANGUAGE 'plpgsql' VOLATILE AS $$

3628 DECLARE

3629 "issue_row" "issue"%ROWTYPE;

3630 BEGIN

3631 PERFORM "create_snapshot"("issue_id_p");

3632 PERFORM "freeze_after_snapshot"("issue_id_p");

3633 RETURN;

3634 END;

3635 $$;

3636

3637 COMMENT ON FUNCTION "manual_freeze"

3638 ( "issue"."id"%TYPE )

3639 IS 'Freeze an issue manually (fully) and start voting';

3640

3641

3642

3643 -----------------------

3644 -- Counting of votes --

3645 -----------------------

3646

3647

3648 CREATE FUNCTION "weight_of_added_vote_delegations"

3649 ( "issue_id_p" "issue"."id"%TYPE,

3650 "member_id_p" "member"."id"%TYPE,

3651 "delegate_member_ids_p" "delegating_voter"."delegate_member_ids"%TYPE )

3652 RETURNS "direct_voter"."weight"%TYPE

3653 LANGUAGE 'plpgsql' VOLATILE AS $$

3654 DECLARE

3655 "issue_delegation_row" "issue_delegation"%ROWTYPE;

3656 "delegate_member_ids_v" "delegating_voter"."delegate_member_ids"%TYPE;

3657 "weight_v" INT4;

3658 "sub_weight_v" INT4;

3659 BEGIN

3660 "weight_v" := 0;

3661 FOR "issue_delegation_row" IN

3662 SELECT * FROM "issue_delegation"

3663 WHERE "trustee_id" = "member_id_p"

3664 AND "issue_id" = "issue_id_p"

3665 LOOP

3666 IF NOT EXISTS (

3667 SELECT NULL FROM "direct_voter"

3668 WHERE "member_id" = "issue_delegation_row"."truster_id"

3669 AND "issue_id" = "issue_id_p"

3670 ) AND NOT EXISTS (

3671 SELECT NULL FROM "delegating_voter"

3672 WHERE "member_id" = "issue_delegation_row"."truster_id"

3673 AND "issue_id" = "issue_id_p"

3674 ) THEN

3675 "delegate_member_ids_v" :=

3676 "member_id_p" || "delegate_member_ids_p";

3677 INSERT INTO "delegating_voter" (

3678 "issue_id",

3679 "member_id",

3680 "scope",

3681 "delegate_member_ids"

3682 ) VALUES (

3683 "issue_id_p",

3684 "issue_delegation_row"."truster_id",

3685 "issue_delegation_row"."scope",

3686 "delegate_member_ids_v"

3687 );

3688 "sub_weight_v" := 1 +

3689 "weight_of_added_vote_delegations"(

3690 "issue_id_p",

3691 "issue_delegation_row"."truster_id",

3692 "delegate_member_ids_v"

3693 );

3694 UPDATE "delegating_voter"

3695 SET "weight" = "sub_weight_v"

3696 WHERE "issue_id" = "issue_id_p"

3697 AND "member_id" = "issue_delegation_row"."truster_id";

3698 "weight_v" := "weight_v" + "sub_weight_v";

3699 END IF;

3700 END LOOP;

3701 RETURN "weight_v";

3702 END;

3703 $$;

3704

3705 COMMENT ON FUNCTION "weight_of_added_vote_delegations"

3706 ( "issue"."id"%TYPE,

3707 "member"."id"%TYPE,

3708 "delegating_voter"."delegate_member_ids"%TYPE )

3709 IS 'Helper function for "add_vote_delegations" function';

3710

3711

3712 CREATE FUNCTION "add_vote_delegations"

3713 ( "issue_id_p" "issue"."id"%TYPE )

3714 RETURNS VOID

3715 LANGUAGE 'plpgsql' VOLATILE AS $$

3716 DECLARE

3717 "member_id_v" "member"."id"%TYPE;

3718 BEGIN

3719 FOR "member_id_v" IN

3720 SELECT "member_id" FROM "direct_voter"

3721 WHERE "issue_id" = "issue_id_p"

3722 LOOP

3723 UPDATE "direct_voter" SET

3724 "weight" = "weight" + "weight_of_added_vote_delegations"(

3725 "issue_id_p",

3726 "member_id_v",

3727 '{}'

3728 )

3729 WHERE "member_id" = "member_id_v"

3730 AND "issue_id" = "issue_id_p";

3731 END LOOP;

3732 RETURN;

3733 END;

3734 $$;

3735

3736 COMMENT ON FUNCTION "add_vote_delegations"

3737 ( "issue_id_p" "issue"."id"%TYPE )

3738 IS 'Helper function for "close_voting" function';

3739

3740

3741 CREATE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)

3742 RETURNS VOID

3743 LANGUAGE 'plpgsql' VOLATILE AS $$

3744 DECLARE

3745 "area_id_v" "area"."id"%TYPE;

3746 "unit_id_v" "unit"."id"%TYPE;

3747 "member_id_v" "member"."id"%TYPE;

3748 BEGIN

3749 PERFORM "lock_issue"("issue_id_p");

3750 SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";

3751 SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v";

3752 -- delete delegating votes (in cases of manual reset of issue state):

3753 DELETE FROM "delegating_voter"

3754 WHERE "issue_id" = "issue_id_p";

3755 -- delete votes from non-privileged voters:

3756 DELETE FROM "direct_voter"

3757 USING (

3758 SELECT

3759 "direct_voter"."member_id"

3760 FROM "direct_voter"

3761 JOIN "member" ON "direct_voter"."member_id" = "member"."id"

3762 LEFT JOIN "privilege"

3763 ON "privilege"."unit_id" = "unit_id_v"

3764 AND "privilege"."member_id" = "direct_voter"."member_id"

3765 WHERE "direct_voter"."issue_id" = "issue_id_p" AND (

3766 "member"."active" = FALSE OR

3767 "privilege"."voting_right" ISNULL OR

3768 "privilege"."voting_right" = FALSE

3769 )

3770 ) AS "subquery"

3771 WHERE "direct_voter"."issue_id" = "issue_id_p"

3772 AND "direct_voter"."member_id" = "subquery"."member_id";

3773 -- consider delegations:

3774 UPDATE "direct_voter" SET "weight" = 1

3775 WHERE "issue_id" = "issue_id_p";

3776 PERFORM "add_vote_delegations"("issue_id_p");

3777 -- set voter count and mark issue as being calculated:

3778 UPDATE "issue" SET

3779 "state" = 'calculation',

3780 "closed" = now(),

3781 "voter_count" = (

3782 SELECT coalesce(sum("weight"), 0)

3783 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"

3784 )

3785 WHERE "id" = "issue_id_p";

3786 -- materialize battle_view:

3787 -- NOTE: "closed" column of issue must be set at this point

3788 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";

3789 INSERT INTO "battle" (

3790 "issue_id",

3791 "winning_initiative_id", "losing_initiative_id",

3792 "count"

3793 ) SELECT

3794 "issue_id",

3795 "winning_initiative_id", "losing_initiative_id",

3796 "count"

3797 FROM "battle_view" WHERE "issue_id" = "issue_id_p";

3798 -- copy "positive_votes" and "negative_votes" from "battle" table:

3799 UPDATE "initiative" SET

3800 "positive_votes" = "battle_win"."count",

3801 "negative_votes" = "battle_lose"."count"

3802 FROM "battle" AS "battle_win", "battle" AS "battle_lose"

3803 WHERE

3804 "battle_win"."issue_id" = "issue_id_p" AND

3805 "battle_win"."winning_initiative_id" = "initiative"."id" AND

3806 "battle_win"."losing_initiative_id" ISNULL AND

3807 "battle_lose"."issue_id" = "issue_id_p" AND

3808 "battle_lose"."losing_initiative_id" = "initiative"."id" AND

3809 "battle_lose"."winning_initiative_id" ISNULL;

3810 END;

3811 $$;

3812

3813 COMMENT ON FUNCTION "close_voting"

3814 ( "issue"."id"%TYPE )

3815 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.';

3816

3817

3818 CREATE FUNCTION "defeat_strength"

3819 ( "positive_votes_p" INT4, "negative_votes_p" INT4 )

3820 RETURNS INT8

3821 LANGUAGE 'plpgsql' IMMUTABLE AS $$

3822 BEGIN

3823 IF "positive_votes_p" > "negative_votes_p" THEN

3824 RETURN ("positive_votes_p"::INT8 << 31) - "negative_votes_p"::INT8;

3825 ELSIF "positive_votes_p" = "negative_votes_p" THEN

3826 RETURN 0;

3827 ELSE

3828 RETURN -1;

3829 END IF;

3830 END;

3831 $$;

3832

3833 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';

3834

3835

3836 CREATE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE)

3837 RETURNS VOID

3838 LANGUAGE 'plpgsql' VOLATILE AS $$

3839 DECLARE

3840 "issue_row" "issue"%ROWTYPE;

3841 "policy_row" "policy"%ROWTYPE;

3842 "dimension_v" INTEGER;

3843 "vote_matrix" INT4[][]; -- absolute votes

3844 "matrix" INT8[][]; -- defeat strength / best paths

3845 "i" INTEGER;

3846 "j" INTEGER;

3847 "k" INTEGER;

3848 "battle_row" "battle"%ROWTYPE;

3849 "rank_ary" INT4[];

3850 "rank_v" INT4;

3851 "done_v" INTEGER;

3852 "winners_ary" INTEGER[];

3853 "initiative_id_v" "initiative"."id"%TYPE;

3854 BEGIN

3855 SELECT * INTO "issue_row"

3856 FROM "issue" WHERE "id" = "issue_id_p"

3857 FOR UPDATE;

3858 SELECT * INTO "policy_row"

3859 FROM "policy" WHERE "id" = "issue_row"."policy_id";

3860 SELECT count(1) INTO "dimension_v"

3861 FROM "battle_participant" WHERE "issue_id" = "issue_id_p";

3862 -- Create "vote_matrix" with absolute number of votes in pairwise

3863 -- comparison:

3864 "vote_matrix" := array_fill(NULL::INT4, ARRAY["dimension_v", "dimension_v"]);

3865 "i" := 1;

3866 "j" := 2;

3867 FOR "battle_row" IN

3868 SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p"

3869 ORDER BY

3870 "winning_initiative_id" NULLS LAST,

3871 "losing_initiative_id" NULLS LAST

3872 LOOP

3873 "vote_matrix"["i"]["j"] := "battle_row"."count";

3874 IF "j" = "dimension_v" THEN

3875 "i" := "i" + 1;

3876 "j" := 1;

3877 ELSE

3878 "j" := "j" + 1;

3879 IF "j" = "i" THEN

3880 "j" := "j" + 1;

3881 END IF;

3882 END IF;

3883 END LOOP;

3884 IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN

3885 RAISE EXCEPTION 'Wrong battle count (should not happen)';

3886 END IF;

3887 -- Store defeat strengths in "matrix" using "defeat_strength"

3888 -- function:

3889 "matrix" := array_fill(NULL::INT8, ARRAY["dimension_v", "dimension_v"]);

3890 "i" := 1;

3891 LOOP

3892 "j" := 1;

3893 LOOP

3894 IF "i" != "j" THEN

3895 "matrix"["i"]["j"] := "defeat_strength"(

3896 "vote_matrix"["i"]["j"],

3897 "vote_matrix"["j"]["i"]

3898 );

3899 END IF;

3900 EXIT WHEN "j" = "dimension_v";

3901 "j" := "j" + 1;

3902 END LOOP;

3903 EXIT WHEN "i" = "dimension_v";

3904 "i" := "i" + 1;

3905 END LOOP;

3906 -- Find best paths:

3907 "i" := 1;

3908 LOOP

3909 "j" := 1;

3910 LOOP

3911 IF "i" != "j" THEN

3912 "k" := 1;

3913 LOOP

3914 IF "i" != "k" AND "j" != "k" THEN

3915 IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN

3916 IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN

3917 "matrix"["j"]["k"] := "matrix"["j"]["i"];

3918 END IF;

3919 ELSE

3920 IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN

3921 "matrix"["j"]["k"] := "matrix"["i"]["k"];

3922 END IF;

3923 END IF;

3924 END IF;

3925 EXIT WHEN "k" = "dimension_v";

3926 "k" := "k" + 1;

3927 END LOOP;

3928 END IF;

3929 EXIT WHEN "j" = "dimension_v";

3930 "j" := "j" + 1;

3931 END LOOP;

3932 EXIT WHEN "i" = "dimension_v";

3933 "i" := "i" + 1;

3934 END LOOP;

3935 -- Determine order of winners:

3936 "rank_ary" := array_fill(NULL::INT4, ARRAY["dimension_v"]);

3937 "rank_v" := 1;

3938 "done_v" := 0;

3939 LOOP

3940 "winners_ary" := '{}';

3941 "i" := 1;

3942 LOOP

3943 IF "rank_ary"["i"] ISNULL THEN

3944 "j" := 1;

3945 LOOP

3946 IF

3947 "i" != "j" AND

3948 "rank_ary"["j"] ISNULL AND

3949 "matrix"["j"]["i"] > "matrix"["i"]["j"]

3950 THEN

3951 -- someone else is better

3952 EXIT;

3953 END IF;

3954 IF "j" = "dimension_v" THEN

3955 -- noone is better

3956 "winners_ary" := "winners_ary" || "i";

3957 EXIT;

3958 END IF;

3959 "j" := "j" + 1;

3960 END LOOP;

3961 END IF;

3962 EXIT WHEN "i" = "dimension_v";

3963 "i" := "i" + 1;

3964 END LOOP;

3965 "i" := 1;

3966 LOOP

3967 "rank_ary"["winners_ary"["i"]] := "rank_v";

3968 "done_v" := "done_v" + 1;

3969 EXIT WHEN "i" = array_upper("winners_ary", 1);

3970 "i" := "i" + 1;

3971 END LOOP;

3972 EXIT WHEN "done_v" = "dimension_v";

3973 "rank_v" := "rank_v" + 1;

3974 END LOOP;

3975 -- write preliminary results:

3976 "i" := 1;

3977 FOR "initiative_id_v" IN

3978 SELECT "id" FROM "initiative"

3979 WHERE "issue_id" = "issue_id_p" AND "admitted"

3980 ORDER BY "id"

3981 LOOP

3982 UPDATE "initiative" SET

3983 "direct_majority" =

3984 CASE WHEN "policy_row"."direct_majority_strict" THEN

3985 "positive_votes" * "policy_row"."direct_majority_den" >

3986 "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")

3987 ELSE

3988 "positive_votes" * "policy_row"."direct_majority_den" >=

3989 "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")

3990 END

3991 AND "positive_votes" >= "policy_row"."direct_majority_positive"

3992 AND "issue_row"."voter_count"-"negative_votes" >=

3993 "policy_row"."direct_majority_non_negative",

3994 "indirect_majority" =

3995 CASE WHEN "policy_row"."indirect_majority_strict" THEN

3996 "positive_votes" * "policy_row"."indirect_majority_den" >

3997 "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")

3998 ELSE

3999 "positive_votes" * "policy_row"."indirect_majority_den" >=

4000 "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")

4001 END

4002 AND "positive_votes" >= "policy_row"."indirect_majority_positive"

4003 AND "issue_row"."voter_count"-"negative_votes" >=

4004 "policy_row"."indirect_majority_non_negative",

4005 "schulze_rank" = "rank_ary"["i"],

4006 "better_than_status_quo" = "rank_ary"["i"] < "rank_ary"["dimension_v"],

4007 "worse_than_status_quo" = "rank_ary"["i"] > "rank_ary"["dimension_v"],

4008 "multistage_majority" = "rank_ary"["i"] >= "rank_ary"["dimension_v"],

4009 "reverse_beat_path" = "matrix"["dimension_v"]["i"] >= 0,

4010 "eligible" = FALSE,

4011 "winner" = FALSE,

4012 "rank" = NULL -- NOTE: in cases of manual reset of issue state

4013 WHERE "id" = "initiative_id_v";

4014 "i" := "i" + 1;

4015 END LOOP;

4016 IF "i" != "dimension_v" THEN

4017 RAISE EXCEPTION 'Wrong winner count (should not happen)';

4018 END IF;

4019 -- take indirect majorities into account:

4020 LOOP

4021 UPDATE "initiative" SET "indirect_majority" = TRUE

4022 FROM (

4023 SELECT "new_initiative"."id" AS "initiative_id"

4024 FROM "initiative" "old_initiative"

4025 JOIN "initiative" "new_initiative"

4026 ON "new_initiative"."issue_id" = "issue_id_p"

4027 AND "new_initiative"."indirect_majority" = FALSE

4028 JOIN "battle" "battle_win"

4029 ON "battle_win"."issue_id" = "issue_id_p"

4030 AND "battle_win"."winning_initiative_id" = "new_initiative"."id"

4031 AND "battle_win"."losing_initiative_id" = "old_initiative"."id"

4032 JOIN "battle" "battle_lose"

4033 ON "battle_lose"."issue_id" = "issue_id_p"

4034 AND "battle_lose"."losing_initiative_id" = "new_initiative"."id"

4035 AND "battle_lose"."winning_initiative_id" = "old_initiative"."id"

4036 WHERE "old_initiative"."issue_id" = "issue_id_p"

4037 AND "old_initiative"."indirect_majority" = TRUE

4038 AND CASE WHEN "policy_row"."indirect_majority_strict" THEN

4039 "battle_win"."count" * "policy_row"."indirect_majority_den" >

4040 "policy_row"."indirect_majority_num" *

4041 ("battle_win"."count"+"battle_lose"."count")

4042 ELSE

4043 "battle_win"."count" * "policy_row"."indirect_majority_den" >=

4044 "policy_row"."indirect_majority_num" *

4045 ("battle_win"."count"+"battle_lose"."count")

4046 END

4047 AND "battle_win"."count" >= "policy_row"."indirect_majority_positive"

4048 AND "issue_row"."voter_count"-"battle_lose"."count" >=

4049 "policy_row"."indirect_majority_non_negative"

4050 ) AS "subquery"

4051 WHERE "id" = "subquery"."initiative_id";

4052 EXIT WHEN NOT FOUND;

4053 END LOOP;

4054 -- set "multistage_majority" for remaining matching initiatives:

4055 UPDATE "initiative" SET "multistage_majority" = TRUE

4056 FROM (

4057 SELECT "losing_initiative"."id" AS "initiative_id"

4058 FROM "initiative" "losing_initiative"

4059 JOIN "initiative" "winning_initiative"

4060 ON "winning_initiative"."issue_id" = "issue_id_p"

4061 AND "winning_initiative"."admitted"

4062 JOIN "battle" "battle_win"

4063 ON "battle_win"."issue_id" = "issue_id_p"

4064 AND "battle_win"."winning_initiative_id" = "winning_initiative"."id"

4065 AND "battle_win"."losing_initiative_id" = "losing_initiative"."id"

4066 JOIN "battle" "battle_lose"

4067 ON "battle_lose"."issue_id" = "issue_id_p"

4068 AND "battle_lose"."losing_initiative_id" = "winning_initiative"."id"

4069 AND "battle_lose"."winning_initiative_id" = "losing_initiative"."id"

4070 WHERE "losing_initiative"."issue_id" = "issue_id_p"

4071 AND "losing_initiative"."admitted"

4072 AND "winning_initiative"."schulze_rank" <

4073 "losing_initiative"."schulze_rank"

4074 AND "battle_win"."count" > "battle_lose"."count"

4075 AND (

4076 "battle_win"."count" > "winning_initiative"."positive_votes" OR

4077 "battle_lose"."count" < "losing_initiative"."negative_votes" )

4078 ) AS "subquery"

4079 WHERE "id" = "subquery"."initiative_id";

4080 -- mark eligible initiatives:

4081 UPDATE "initiative" SET "eligible" = TRUE

4082 WHERE "issue_id" = "issue_id_p"

4083 AND "initiative"."direct_majority"

4084 AND "initiative"."indirect_majority"

4085 AND "initiative"."better_than_status_quo"

4086 AND (

4087 "policy_row"."no_multistage_majority" = FALSE OR

4088 "initiative"."multistage_majority" = FALSE )

4089 AND (

4090 "policy_row"."no_reverse_beat_path" = FALSE OR

4091 "initiative"."reverse_beat_path" = FALSE );

4092 -- mark final winner:

4093 UPDATE "initiative" SET "winner" = TRUE

4094 FROM (

4095 SELECT "id" AS "initiative_id"

4096 FROM "initiative"

4097 WHERE "issue_id" = "issue_id_p" AND "eligible"

4098 ORDER BY

4099 "schulze_rank",

4100 "vote_ratio"("positive_votes", "negative_votes"),

4101 "id"

4102 LIMIT 1

4103 ) AS "subquery"

4104 WHERE "id" = "subquery"."initiative_id";

4105 -- write (final) ranks:

4106 "rank_v" := 1;

4107 FOR "initiative_id_v" IN

4108 SELECT "id"

4109 FROM "initiative"

4110 WHERE "issue_id" = "issue_id_p" AND "admitted"

4111 ORDER BY

4112 "winner" DESC,

4113 "eligible" DESC,

4114 "schulze_rank",

4115 "vote_ratio"("positive_votes", "negative_votes"),

4116 "id"

4117 LOOP

4118 UPDATE "initiative" SET "rank" = "rank_v"

4119 WHERE "id" = "initiative_id_v";

4120 "rank_v" := "rank_v" + 1;

4121 END LOOP;

4122 -- set schulze rank of status quo and mark issue as finished:

4123 UPDATE "issue" SET

4124 "status_quo_schulze_rank" = "rank_ary"["dimension_v"],

4125 "state" =

4126 CASE WHEN EXISTS (

4127 SELECT NULL FROM "initiative"

4128 WHERE "issue_id" = "issue_id_p" AND "winner"

4129 ) THEN

4130 'finished_with_winner'::"issue_state"

4131 ELSE

4132 'finished_without_winner'::"issue_state"

4133 END,

4134 "ranks_available" = TRUE

4135 WHERE "id" = "issue_id_p";

4136 RETURN;

4137 END;

4138 $$;

4139

4140 COMMENT ON FUNCTION "calculate_ranks"

4141 ( "issue"."id"%TYPE )

4142 IS 'Determine ranking (Votes have to be counted first)';

4143

4144

4145

4146 -----------------------------

4147 -- Automatic state changes --

4148 -----------------------------

4149

4150

4151 CREATE FUNCTION "check_issue"

4152 ( "issue_id_p" "issue"."id"%TYPE )

4153 RETURNS VOID

4154 LANGUAGE 'plpgsql' VOLATILE AS $$

4155 DECLARE

4156 "issue_row" "issue"%ROWTYPE;

4157 "policy_row" "policy"%ROWTYPE;

4158 BEGIN

4159 PERFORM "lock_issue"("issue_id_p");

4160 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";

4161 -- only process open issues:

4162 IF "issue_row"."closed" ISNULL THEN

4163 SELECT * INTO "policy_row" FROM "policy"

4164 WHERE "id" = "issue_row"."policy_id";

4165 -- create a snapshot, unless issue is already fully frozen:

4166 IF "issue_row"."fully_frozen" ISNULL THEN

4167 PERFORM "create_snapshot"("issue_id_p");

4168 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";

4169 END IF;

4170 -- eventually close or accept issues, which have not been accepted:

4171 IF "issue_row"."accepted" ISNULL THEN

4172 IF EXISTS (

4173 SELECT NULL FROM "initiative"

4174 WHERE "issue_id" = "issue_id_p"

4175 AND "supporter_count" > 0

4176 AND "supporter_count" * "policy_row"."issue_quorum_den"

4177 >= "issue_row"."population" * "policy_row"."issue_quorum_num"

4178 ) THEN

4179 -- accept issues, if supporter count is high enough

4180 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');

4181 -- NOTE: "issue_row" used later

4182 "issue_row"."state" := 'discussion';

4183 "issue_row"."accepted" := now();

4184 UPDATE "issue" SET

4185 "state" = "issue_row"."state",

4186 "accepted" = "issue_row"."accepted"

4187 WHERE "id" = "issue_row"."id";

4188 ELSIF

4189 now() >= "issue_row"."created" + "issue_row"."admission_time"

4190 THEN

4191 -- close issues, if admission time has expired

4192 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');

4193 UPDATE "issue" SET

4194 "state" = 'canceled_issue_not_accepted',

4195 "closed" = now()

4196 WHERE "id" = "issue_row"."id";

4197 END IF;

4198 END IF;

4199 -- eventually half freeze issues:

4200 IF

4201 -- NOTE: issue can't be closed at this point, if it has been accepted

4202 "issue_row"."accepted" NOTNULL AND

4203 "issue_row"."half_frozen" ISNULL

4204 THEN

4205 IF

4206 now() >= "issue_row"."accepted" + "issue_row"."discussion_time"

4207 THEN

4208 PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze');

4209 -- NOTE: "issue_row" used later

4210 "issue_row"."state" := 'verification';

4211 "issue_row"."half_frozen" := now();

4212 UPDATE "issue" SET

4213 "state" = "issue_row"."state",

4214 "half_frozen" = "issue_row"."half_frozen"

4215 WHERE "id" = "issue_row"."id";

4216 END IF;

4217 END IF;

4218 -- close issues after some time, if all initiatives have been revoked:

4219 IF

4220 "issue_row"."closed" ISNULL AND

4221 NOT EXISTS (

4222 -- all initiatives are revoked

4223 SELECT NULL FROM "initiative"

4224 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL

4225 ) AND (

4226 -- and issue has not been accepted yet

4227 "issue_row"."accepted" ISNULL OR

4228 NOT EXISTS (

4229 -- or no initiatives have been revoked lately

4230 SELECT NULL FROM "initiative"

4231 WHERE "issue_id" = "issue_id_p"

4232 AND now() < "revoked" + "issue_row"."verification_time"

4233 ) OR (

4234 -- or verification time has elapsed

4235 "issue_row"."half_frozen" NOTNULL AND

4236 "issue_row"."fully_frozen" ISNULL AND

4237 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"

4238 )

4239 )

4240 THEN

4241 -- NOTE: "issue_row" used later

4242 IF "issue_row"."accepted" ISNULL THEN

4243 "issue_row"."state" := 'canceled_revoked_before_accepted';

4244 ELSIF "issue_row"."half_frozen" ISNULL THEN

4245 "issue_row"."state" := 'canceled_after_revocation_during_discussion';

4246 ELSE

4247 "issue_row"."state" := 'canceled_after_revocation_during_verification';

4248 END IF;

4249 "issue_row"."closed" := now();

4250 UPDATE "issue" SET

4251 "state" = "issue_row"."state",

4252 "closed" = "issue_row"."closed"

4253 WHERE "id" = "issue_row"."id";

4254 END IF;

4255 -- fully freeze issue after verification time:

4256 IF

4257 "issue_row"."half_frozen" NOTNULL AND

4258 "issue_row"."fully_frozen" ISNULL AND

4259 "issue_row"."closed" ISNULL AND

4260 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"

4261 THEN

4262 PERFORM "freeze_after_snapshot"("issue_id_p");

4263 -- NOTE: "issue" might change, thus "issue_row" has to be updated below

4264 END IF;

4265 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";

4266 -- close issue by calling close_voting(...) after voting time:

4267 IF

4268 "issue_row"."closed" ISNULL AND

4269 "issue_row"."fully_frozen" NOTNULL AND

4270 now() >= "issue_row"."fully_frozen" + "issue_row"."voting_time"

4271 THEN

4272 PERFORM "close_voting"("issue_id_p");

4273 -- calculate ranks will not consume much time and can be done now

4274 PERFORM "calculate_ranks"("issue_id_p");

4275 END IF;

4276 END IF;

4277 RETURN;

4278 END;

4279 $$;

4280

4281 COMMENT ON FUNCTION "check_issue"

4282 ( "issue"."id"%TYPE )

4283 IS 'Precalculate supporter counts etc. for a given issue, and check, if status change is required; At end of voting the ranking is not calculated by this function, but must be calculated in a seperate transaction using the "calculate_ranks" function.';

4284

4285

4286 CREATE FUNCTION "check_everything"()

4287 RETURNS VOID

4288 LANGUAGE 'plpgsql' VOLATILE AS $$

4289 DECLARE

4290 "issue_id_v" "issue"."id"%TYPE;

4291 BEGIN

4292 DELETE FROM "expired_session";

4293 PERFORM "check_activity"();

4294 PERFORM "calculate_member_counts"();

4295 FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP

4296 PERFORM "check_issue"("issue_id_v");

4297 END LOOP;

4298 FOR "issue_id_v" IN SELECT "id" FROM "issue_with_ranks_missing" LOOP

4299 PERFORM "calculate_ranks"("issue_id_v");

4300 END LOOP;

4301 RETURN;

4302 END;

4303 $$;

4304

4305 COMMENT ON FUNCTION "check_everything"() IS 'Amongst other regular tasks this function performs "check_issue" for every open issue, and if possible, automatically calculates ranks. Use this function only for development and debugging purposes, as long transactions with exclusive locking may result. In productive environments you should call the lf_update program instead.';

4306

4307

4308

4309 ----------------------

4310 -- Deletion of data --

4311 ----------------------

4312

4313

4314 CREATE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE)

4315 RETURNS VOID

4316 LANGUAGE 'plpgsql' VOLATILE AS $$

4317 DECLARE

4318 "issue_row" "issue"%ROWTYPE;

4319 BEGIN

4320 SELECT * INTO "issue_row"

4321 FROM "issue" WHERE "id" = "issue_id_p"

4322 FOR UPDATE;

4323 IF "issue_row"."cleaned" ISNULL THEN

4324 UPDATE "issue" SET

4325 "state" = 'voting',

4326 "closed" = NULL,

4327 "ranks_available" = FALSE

4328 WHERE "id" = "issue_id_p";

4329 DELETE FROM "voting_comment"

4330 WHERE "issue_id" = "issue_id_p";

4331 DELETE FROM "delegating_voter"

4332 WHERE "issue_id" = "issue_id_p";

4333 DELETE FROM "direct_voter"

4334 WHERE "issue_id" = "issue_id_p";

4335 DELETE FROM "delegating_interest_snapshot"

4336 WHERE "issue_id" = "issue_id_p";

4337 DELETE FROM "direct_interest_snapshot"

4338 WHERE "issue_id" = "issue_id_p";

4339 DELETE FROM "delegating_population_snapshot"

4340 WHERE "issue_id" = "issue_id_p";

4341 DELETE FROM "direct_population_snapshot"

4342 WHERE "issue_id" = "issue_id_p";

4343 DELETE FROM "non_voter"

4344 WHERE "issue_id" = "issue_id_p";

4345 DELETE FROM "delegation"

4346 WHERE "issue_id" = "issue_id_p";

4347 DELETE FROM "supporter"

4348 WHERE "issue_id" = "issue_id_p";

4349 UPDATE "issue" SET

4350 "state" = "issue_row"."state",

4351 "closed" = "issue_row"."closed",

4352 "ranks_available" = "issue_row"."ranks_available",

4353 "cleaned" = now()

4354 WHERE "id" = "issue_id_p";

4355 END IF;

4356 RETURN;

4357 END;

4358 $$;

4359

4360 COMMENT ON FUNCTION "clean_issue"("issue"."id"%TYPE) IS 'Delete discussion data and votes belonging to an issue';

4361

4362

4363 CREATE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)

4364 RETURNS VOID

4365 LANGUAGE 'plpgsql' VOLATILE AS $$

4366 BEGIN

4367 UPDATE "member" SET

4368 "last_login" = NULL,

4369 "login" = NULL,

4370 "password" = NULL,

4371 "locked" = TRUE,

4372 "active" = FALSE,

4373 "notify_email" = NULL,

4374 "notify_email_unconfirmed" = NULL,

4375 "notify_email_secret" = NULL,

4376 "notify_email_secret_expiry" = NULL,

4377 "notify_email_lock_expiry" = NULL,

4378 "password_reset_secret" = NULL,

4379 "password_reset_secret_expiry" = NULL,

4380 "organizational_unit" = NULL,

4381 "internal_posts" = NULL,

4382 "realname" = NULL,

4383 "birthday" = NULL,

4384 "address" = NULL,

4385 "email" = NULL,

4386 "xmpp_address" = NULL,

4387 "website" = NULL,

4388 "phone" = NULL,

4389 "mobile_phone" = NULL,

4390 "profession" = NULL,

4391 "external_memberships" = NULL,

4392 "external_posts" = NULL,

4393 "statement" = NULL

4394 WHERE "id" = "member_id_p";

4395 -- "text_search_data" is updated by triggers

4396 DELETE FROM "setting" WHERE "member_id" = "member_id_p";

4397 DELETE FROM "setting_map" WHERE "member_id" = "member_id_p";

4398 DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p";

4399 DELETE FROM "member_image" WHERE "member_id" = "member_id_p";

4400 DELETE FROM "contact" WHERE "member_id" = "member_id_p";

4401 DELETE FROM "ignored_member" WHERE "member_id" = "member_id_p";

4402 DELETE FROM "session" WHERE "member_id" = "member_id_p";

4403 DELETE FROM "area_setting" WHERE "member_id" = "member_id_p";

4404 DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p";

4405 DELETE FROM "ignored_initiative" WHERE "member_id" = "member_id_p";

4406 DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p";

4407 DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p";

4408 DELETE FROM "membership" WHERE "member_id" = "member_id_p";

4409 DELETE FROM "delegation" WHERE "truster_id" = "member_id_p";

4410 DELETE FROM "non_voter" WHERE "member_id" = "member_id_p";

4411 DELETE FROM "direct_voter" USING "issue"

4412 WHERE "direct_voter"."issue_id" = "issue"."id"

4413 AND "issue"."closed" ISNULL

4414 AND "member_id" = "member_id_p";

4415 RETURN;

4416 END;

4417 $$;

4418

4419 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)';

4420

4421

4422 CREATE FUNCTION "delete_private_data"()

4423 RETURNS VOID

4424 LANGUAGE 'plpgsql' VOLATILE AS $$

4425 BEGIN

4426 DELETE FROM "member" WHERE "activated" ISNULL;

4427 UPDATE "member" SET

4428 "invite_code" = NULL,

4429 "invite_code_expiry" = NULL,

4430 "admin_comment" = NULL,

4431 "last_login" = NULL,

4432 "login" = NULL,

4433 "password" = NULL,

4434 "lang" = NULL,

4435 "notify_email" = NULL,

4436 "notify_email_unconfirmed" = NULL,

4437 "notify_email_secret" = NULL,

4438 "notify_email_secret_expiry" = NULL,

4439 "notify_email_lock_expiry" = NULL,

4440 "notify_level" = NULL,

4441 "password_reset_secret" = NULL,

4442 "password_reset_secret_expiry" = NULL,

4443 "organizational_unit" = NULL,

4444 "internal_posts" = NULL,

4445 "realname" = NULL,

4446 "birthday" = NULL,

4447 "address" = NULL,

4448 "email" = NULL,

4449 "xmpp_address" = NULL,

4450 "website" = NULL,

4451 "phone" = NULL,

4452 "mobile_phone" = NULL,

4453 "profession" = NULL,

4454 "external_memberships" = NULL,

4455 "external_posts" = NULL,

4456 "formatting_engine" = NULL,

4457 "statement" = NULL;

4458 -- "text_search_data" is updated by triggers

4459 DELETE FROM "setting";

4460 DELETE FROM "setting_map";

4461 DELETE FROM "member_relation_setting";

4462 DELETE FROM "member_image";

4463 DELETE FROM "contact";

4464 DELETE FROM "ignored_member";

4465 DELETE FROM "session";

4466 DELETE FROM "area_setting";

4467 DELETE FROM "issue_setting";

4468 DELETE FROM "ignored_initiative";

4469 DELETE FROM "initiative_setting";

4470 DELETE FROM "suggestion_setting";

4471 DELETE FROM "non_voter";

4472 DELETE FROM "direct_voter" USING "issue"

4473 WHERE "direct_voter"."issue_id" = "issue"."id"

4474 AND "issue"."closed" ISNULL;

4475 RETURN;

4476 END;

4477 $$;

4478

4479 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.';

4480

4481

4482

4483 COMMIT;