liquid_feedback_core: ac7836ac00d9 core.sql

liquid_feedback_core

view core.sql @ 14:ac7836ac00d9

Version beta15

Bugfix: Revoked initiatives will never be admitted

Possibility to recommend another initiative to support when revoking an initiative

Allow "initiator"."accepted" to be NULL (NULL means "no answer yet", while FALSE means "rejected")

author	jbe
date	Sun Jan 10 12:00:00 2010 +0100 (2010-01-10)
parents	fd9295e23be4
children	6f6ec6b5eee9

line source

2 CREATE LANGUAGE plpgsql; -- Triggers are implemented in PL/pgSQL

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

6 BEGIN;

8 CREATE VIEW "liquid_feedback_version" AS

9 SELECT * FROM (VALUES ('beta15', NULL, NULL, NULL))

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

14 ----------------------

15 -- Full text search --

16 ----------------------

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

20 RETURNS TSQUERY

21 LANGUAGE 'plpgsql' IMMUTABLE AS $$

22 BEGIN

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

24 END;

25 $$;

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

30 CREATE FUNCTION "highlight"

31 ( "body_p" TEXT,

32 "query_text_p" TEXT )

33 RETURNS TEXT

34 LANGUAGE 'plpgsql' IMMUTABLE AS $$

35 BEGIN

36 RETURN ts_headline(

37 'pg_catalog.simple',

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

39 "text_search_query"("query_text_p"),

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

41 END;

42 $$;

44 COMMENT ON FUNCTION "highlight"

45 ( "body_p" TEXT,

46 "query_text_p" TEXT )

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

51 -------------------------

52 -- Tables and indicies --

53 -------------------------

56 CREATE TABLE "member" (

57 "id" SERIAL4 PRIMARY KEY,

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

59 "login" TEXT NOT NULL UNIQUE,

60 "password" TEXT,

61 "active" BOOLEAN NOT NULL DEFAULT TRUE,

62 "admin" BOOLEAN NOT NULL DEFAULT FALSE,

63 "notify_email" TEXT,

64 "notify_email_unconfirmed" TEXT,

65 "notify_email_secret" TEXT UNIQUE,

66 "notify_email_secret_expiry" TIMESTAMPTZ,

67 "password_reset_secret" TEXT UNIQUE,

68 "password_reset_secret_expiry" TIMESTAMPTZ,

69 "name" TEXT NOT NULL UNIQUE,

70 "identification" TEXT UNIQUE,

71 "organizational_unit" TEXT,

72 "internal_posts" TEXT,

73 "realname" TEXT,

74 "birthday" DATE,

75 "address" TEXT,

76 "email" TEXT,

77 "xmpp_address" TEXT,

78 "website" TEXT,

79 "phone" TEXT,

80 "mobile_phone" TEXT,

81 "profession" TEXT,

82 "external_memberships" TEXT,

83 "external_posts" TEXT,

84 "statement" TEXT,

85 "text_search_data" TSVECTOR );

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

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

88 CREATE TRIGGER "update_text_search_data"

89 BEFORE INSERT OR UPDATE ON "member"

90 FOR EACH ROW EXECUTE PROCEDURE

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

92 "name", "identification", "organizational_unit", "internal_posts",

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

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

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

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

99 COMMENT ON COLUMN "member"."active" IS 'Inactive members can not login and their supports/votes are not counted by the system.';

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

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

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

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

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

105 COMMENT ON COLUMN "member"."name" IS 'Distinct name of the member';

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

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

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

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

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

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

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

113 COMMENT ON COLUMN "member"."statement" IS 'Freely chosen text of the member for his homepage within the system';

114

115

116 CREATE TABLE "member_history" (

117 "id" SERIAL8 PRIMARY KEY,

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

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

120 "login" TEXT NOT NULL,

121 "name" TEXT NOT NULL );

122

123 COMMENT ON TABLE "member_history" IS 'Filled by trigger; keeps information about old names and login names of members';

124

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

126 COMMENT ON COLUMN "member_history"."until" IS 'Timestamp until the name and login had been valid';

127

128

129 CREATE TABLE "invite_code" (

130 "code" TEXT PRIMARY KEY,

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

132 "used" TIMESTAMPTZ,

133 "member_id" INT4 UNIQUE REFERENCES "member" ("id") ON DELETE SET NULL ON UPDATE CASCADE,

134 "comment" TEXT,

135 CONSTRAINT "only_used_codes_may_refer_to_member" CHECK ("used" NOTNULL OR "member_id" ISNULL) );

136

137 COMMENT ON TABLE "invite_code" IS 'Invite codes can be used once to create a new member account.';

138

139 COMMENT ON COLUMN "invite_code"."code" IS 'Secret code';

140 COMMENT ON COLUMN "invite_code"."created" IS 'Time of creation of the secret code';

141 COMMENT ON COLUMN "invite_code"."used" IS 'NULL, if not used yet, otherwise tells when this code was used to create a member account';

142 COMMENT ON COLUMN "invite_code"."member_id" IS 'References the member whose account was created with this code';

143 COMMENT ON COLUMN "invite_code"."comment" IS 'Comment on the code, which is to be used for administrative reasons only';

144

145

146 CREATE TABLE "setting" (

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

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

149 "key" TEXT NOT NULL,

150 "value" TEXT NOT NULL );

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

152

153 COMMENT ON TABLE "setting" IS 'Place to store frontend specific member settings';

154

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

156

157

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

159

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

161

162

163 CREATE TABLE "member_image" (

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

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

166 "image_type" "member_image_type",

167 "scaled" BOOLEAN,

168 "content_type" TEXT,

169 "data" BYTEA NOT NULL );

170

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

172

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

174

175

176 CREATE TABLE "member_count" (

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

178 "total_count" INT4 NOT NULL );

179

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

181

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

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

184

185

186 CREATE TABLE "contact" (

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

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

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

190 "public" BOOLEAN NOT NULL DEFAULT FALSE,

191 CONSTRAINT "cant_save_yourself_as_contact"

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

193

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

195

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

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

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

199

200

201 CREATE TABLE "session" (

202 "ident" TEXT PRIMARY KEY,

203 "additional_secret" TEXT,

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

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

206 "lang" TEXT );

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

208

209 COMMENT ON TABLE "session" IS 'Sessions, i.e. for a web-frontend';

210

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

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

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

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

215

216

217 CREATE TABLE "policy" (

218 "id" SERIAL4 PRIMARY KEY,

219 "index" INT4 NOT NULL,

220 "active" BOOLEAN NOT NULL DEFAULT TRUE,

221 "name" TEXT NOT NULL UNIQUE,

222 "description" TEXT NOT NULL DEFAULT '',

223 "admission_time" INTERVAL NOT NULL,

224 "discussion_time" INTERVAL NOT NULL,

225 "verification_time" INTERVAL NOT NULL,

226 "voting_time" INTERVAL NOT NULL,

227 "issue_quorum_num" INT4 NOT NULL,

228 "issue_quorum_den" INT4 NOT NULL,

229 "initiative_quorum_num" INT4 NOT NULL,

230 "initiative_quorum_den" INT4 NOT NULL,

231 "majority_num" INT4 NOT NULL DEFAULT 1,

232 "majority_den" INT4 NOT NULL DEFAULT 2,

233 "majority_strict" BOOLEAN NOT NULL DEFAULT TRUE );

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

235

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

237

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

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

240 COMMENT ON COLUMN "policy"."admission_time" IS 'Maximum time an issue stays open without being "accepted"';

241 COMMENT ON COLUMN "policy"."discussion_time" IS 'Regular time until an issue is "half_frozen" after being "accepted"';

242 COMMENT ON COLUMN "policy"."verification_time" IS 'Regular time until an issue is "fully_frozen" after being "half_frozen"';

243 COMMENT ON COLUMN "policy"."voting_time" IS 'Time after an issue is "fully_frozen" but not "closed"';

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

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

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

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

248 COMMENT ON COLUMN "policy"."majority_num" IS 'Numerator of fraction of majority to be reached during voting by an initiative to be aggreed upon';

249 COMMENT ON COLUMN "policy"."majority_den" IS 'Denominator of fraction of majority to be reached during voting by an initiative to be aggreed upon';

250 COMMENT ON COLUMN "policy"."majority_strict" IS 'If TRUE, then the majority must be strictly greater than "majority_num"/"majority_den", otherwise it may also be equal.';

251

252

253 CREATE TABLE "area" (

254 "id" SERIAL4 PRIMARY KEY,

255 "active" BOOLEAN NOT NULL DEFAULT TRUE,

256 "name" TEXT NOT NULL,

257 "description" TEXT NOT NULL DEFAULT '',

258 "direct_member_count" INT4,

259 "member_weight" INT4,

260 "autoreject_weight" INT4,

261 "text_search_data" TSVECTOR );

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

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

264 CREATE TRIGGER "update_text_search_data"

265 BEFORE INSERT OR UPDATE ON "area"

266 FOR EACH ROW EXECUTE PROCEDURE

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

268 "name", "description" );

269

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

271

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

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

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

275 COMMENT ON COLUMN "area"."autoreject_weight" IS 'Sum of weight of members using the autoreject feature';

276

277

278 CREATE TABLE "allowed_policy" (

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

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

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

282 "default_policy" BOOLEAN NOT NULL DEFAULT FALSE );

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

284

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

286

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

288

289

290 CREATE TYPE "snapshot_event" AS ENUM ('periodic', 'end_of_admission', 'start_of_voting');

291

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

293

294

295 CREATE TABLE "issue" (

296 "id" SERIAL4 PRIMARY KEY,

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

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

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

300 "accepted" TIMESTAMPTZ,

301 "half_frozen" TIMESTAMPTZ,

302 "fully_frozen" TIMESTAMPTZ,

303 "closed" TIMESTAMPTZ,

304 "ranks_available" BOOLEAN NOT NULL DEFAULT FALSE,

305 "snapshot" TIMESTAMPTZ,

306 "latest_snapshot_event" "snapshot_event",

307 "population" INT4,

308 "vote_now" INT4,

309 "vote_later" INT4,

310 "voter_count" INT4,

311 CONSTRAINT "valid_state" CHECK (

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

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

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

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

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

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

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

319 CONSTRAINT "state_change_order" CHECK (

320 "created" <= "accepted" AND

321 "accepted" <= "half_frozen" AND

322 "half_frozen" <= "fully_frozen" AND

323 "fully_frozen" <= "closed" ),

324 CONSTRAINT "last_snapshot_on_full_freeze"

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

326 CONSTRAINT "freeze_requires_snapshot"

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

328 CONSTRAINT "set_both_or_none_of_snapshot_and_latest_snapshot_event"

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

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

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

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

333

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

335

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

337 COMMENT ON COLUMN "issue"."half_frozen" IS 'Point in time, when "discussion_time" has elapsed, or members voted for voting; 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.';

338 COMMENT ON COLUMN "issue"."fully_frozen" IS 'Point in time, when "verification_time" has elapsed; 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.';

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

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

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

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

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

344 COMMENT ON COLUMN "issue"."vote_now" IS 'Number of votes in favor of voting now, as calculated from table "direct_interest_snapshot"';

345 COMMENT ON COLUMN "issue"."vote_later" IS 'Number of votes against voting now, as calculated from table "direct_interest_snapshot"';

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

347

348

349 CREATE TABLE "initiative" (

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

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

352 "id" SERIAL4 PRIMARY KEY,

353 "name" TEXT NOT NULL,

354 "discussion_url" TEXT,

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

356 "revoked" TIMESTAMPTZ,

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

358 "admitted" BOOLEAN,

359 "supporter_count" INT4,

360 "informed_supporter_count" INT4,

361 "satisfied_supporter_count" INT4,

362 "satisfied_informed_supporter_count" INT4,

363 "positive_votes" INT4,

364 "negative_votes" INT4,

365 "agreed" BOOLEAN,

366 "rank" INT4,

367 "text_search_data" TSVECTOR,

368 CONSTRAINT "non_revoked_initiatives_cant_suggest_other"

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

370 CONSTRAINT "revoked_initiatives_cant_be_admitted"

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

372 CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results"

373 CHECK (("admitted" NOTNULL AND "admitted" = TRUE) OR ("positive_votes" ISNULL AND "negative_votes" ISNULL AND "agreed" ISNULL)),

374 CONSTRAINT "all_or_none_of_positive_votes_negative_votes_and_agreed_must_be_null"

375 CHECK ("positive_votes" NOTNULL = "negative_votes" NOTNULL AND "positive_votes" NOTNULL = "agreed" NOTNULL),

376 CONSTRAINT "non_agreed_initiatives_cant_get_a_rank"

377 CHECK (("agreed" NOTNULL AND "agreed" = TRUE) OR "rank" ISNULL) );

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

379 CREATE TRIGGER "update_text_search_data"

380 BEFORE INSERT OR UPDATE ON "initiative"

381 FOR EACH ROW EXECUTE PROCEDURE

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

383 "name", "discussion_url");

384

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

386

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

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

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

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

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

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

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

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

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

396 COMMENT ON COLUMN "initiative"."agreed" IS 'TRUE, if "positive_votes"/("positive_votes"+"negative_votes") is strictly greater or greater-equal than "majority_num"/"majority_den"';

397 COMMENT ON COLUMN "initiative"."rank" IS 'Rank of approved initiatives (winner is 1), calculated from table "direct_voter"';

398

399

400 CREATE TABLE "draft" (

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

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

403 "id" SERIAL8 PRIMARY KEY,

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

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

406 "formatting_engine" TEXT,

407 "content" TEXT NOT NULL,

408 "text_search_data" TSVECTOR );

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

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

411 CREATE TRIGGER "update_text_search_data"

412 BEFORE INSERT OR UPDATE ON "draft"

413 FOR EACH ROW EXECUTE PROCEDURE

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

415

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

417

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

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

420

421

422 CREATE TABLE "suggestion" (

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

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

425 "id" SERIAL8 PRIMARY KEY,

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

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

428 "name" TEXT NOT NULL,

429 "description" TEXT NOT NULL DEFAULT '',

430 "text_search_data" TSVECTOR,

431 "minus2_unfulfilled_count" INT4,

432 "minus2_fulfilled_count" INT4,

433 "minus1_unfulfilled_count" INT4,

434 "minus1_fulfilled_count" INT4,

435 "plus1_unfulfilled_count" INT4,

436 "plus1_fulfilled_count" INT4,

437 "plus2_unfulfilled_count" INT4,

438 "plus2_fulfilled_count" INT4 );

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

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

441 CREATE TRIGGER "update_text_search_data"

442 BEFORE INSERT OR UPDATE ON "suggestion"

443 FOR EACH ROW EXECUTE PROCEDURE

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

445 "name", "description");

446

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

448

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

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

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

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

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

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

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

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

457

458

459 CREATE TABLE "membership" (

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

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

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

463 "autoreject" BOOLEAN NOT NULL DEFAULT FALSE );

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

465

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

467

468 COMMENT ON COLUMN "membership"."autoreject" IS 'TRUE = member votes against all initiatives in case of not explicitly taking part in the voting procedure; If there exists an "interest" entry, the interest entry has precedence';

469

470

471 CREATE TABLE "interest" (

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

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

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

475 "autoreject" BOOLEAN NOT NULL,

476 "voting_requested" BOOLEAN );

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

478

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

480

481 COMMENT ON COLUMN "interest"."autoreject" IS 'TRUE = member votes against all initiatives in case of not explicitly taking part in the voting procedure';

482 COMMENT ON COLUMN "interest"."voting_requested" IS 'TRUE = member wants to vote now, FALSE = member wants to vote later, NULL = policy rules should apply';

483

484

485 CREATE TABLE "initiator" (

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

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

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

489 "accepted" BOOLEAN );

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

491

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

493

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

495

496

497 CREATE TABLE "supporter" (

498 "issue_id" INT4 NOT NULL,

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

500 "initiative_id" INT4,

501 "member_id" INT4,

502 "draft_id" INT8 NOT NULL,

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

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

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

506

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

508

509 COMMENT ON COLUMN "supporter"."draft_id" IS 'Latest seen draft, defaults to current draft of the initiative (implemented by trigger "default_for_draft_id")';

510

511

512 CREATE TABLE "opinion" (

513 "initiative_id" INT4 NOT NULL,

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

515 "suggestion_id" INT8,

516 "member_id" INT4,

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

518 "fulfilled" BOOLEAN NOT NULL DEFAULT FALSE,

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

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

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

522

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

524

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

526

527

528 CREATE TYPE "delegation_scope" AS ENUM ('global', 'area', 'issue');

529

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

531

532

533 CREATE TABLE "delegation" (

534 "id" SERIAL8 PRIMARY KEY,

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

536 "trustee_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,

537 "scope" "delegation_scope" NOT NULL,

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

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

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

541 CONSTRAINT "area_id_and_issue_id_set_according_to_scope" CHECK (

542 ("scope" = 'global' AND "area_id" ISNULL AND "issue_id" ISNULL ) OR

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

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

545 UNIQUE ("area_id", "truster_id", "trustee_id"),

546 UNIQUE ("issue_id", "truster_id", "trustee_id") );

547 CREATE UNIQUE INDEX "delegation_global_truster_id_trustee_id_unique_idx"

548 ON "delegation" ("truster_id", "trustee_id") WHERE "scope" = 'global';

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

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

551

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

553

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

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

556

557

558 CREATE TABLE "direct_population_snapshot" (

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

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

561 "event" "snapshot_event",

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

563 "weight" INT4,

564 "interest_exists" BOOLEAN NOT NULL );

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

566

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

568

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

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

571 COMMENT ON COLUMN "direct_population_snapshot"."interest_exists" IS 'TRUE if entry is due to interest in issue, FALSE if entry is only due to membership in area';

572

573

574 CREATE TABLE "delegating_population_snapshot" (

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

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

577 "event" "snapshot_event",

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

579 "weight" INT4,

580 "scope" "delegation_scope" NOT NULL,

581 "delegate_member_ids" INT4[] NOT NULL );

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

583

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

585

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

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

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

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

590

591

592 CREATE TABLE "direct_interest_snapshot" (

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

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

595 "event" "snapshot_event",

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

597 "weight" INT4,

598 "voting_requested" BOOLEAN );

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

600

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

602

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

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

605 COMMENT ON COLUMN "direct_interest_snapshot"."voting_requested" IS 'Copied from column "voting_requested" of table "interest"';

606

607

608 CREATE TABLE "delegating_interest_snapshot" (

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

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

611 "event" "snapshot_event",

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

613 "weight" INT4,

614 "scope" "delegation_scope" NOT NULL,

615 "delegate_member_ids" INT4[] NOT NULL );

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

617

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

619

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

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

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

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

624

625

626 CREATE TABLE "direct_supporter_snapshot" (

627 "issue_id" INT4 NOT NULL,

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

629 "initiative_id" INT4,

630 "event" "snapshot_event",

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

632 "informed" BOOLEAN NOT NULL,

633 "satisfied" BOOLEAN NOT NULL,

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

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

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

637

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

639

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

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

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

643

644

645 CREATE TABLE "direct_voter" (

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

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

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

649 "weight" INT4,

650 "autoreject" BOOLEAN NOT NULL DEFAULT FALSE );

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

652

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

654

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

656 COMMENT ON COLUMN "direct_voter"."autoreject" IS 'Votes were inserted due to "autoreject" feature';

657

658

659 CREATE TABLE "delegating_voter" (

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

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

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

663 "weight" INT4,

664 "scope" "delegation_scope" NOT NULL,

665 "delegate_member_ids" INT4[] NOT NULL );

666 CREATE INDEX "delegating_voter_member_id_idx" ON "direct_voter" ("member_id");

667

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

669

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

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

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

673

674

675 CREATE TABLE "vote" (

676 "issue_id" INT4 NOT NULL,

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

678 "initiative_id" INT4,

679 "member_id" INT4,

680 "grade" INT4,

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

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

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

684

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

686

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

688

689

690 CREATE TABLE "contingent" (

691 "time_frame" INTERVAL PRIMARY KEY,

692 "text_entry_limit" INT4,

693 "initiative_limit" INT4 );

694

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

696

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

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

699

700

701

702 --------------------------------

703 -- Writing of history entries --

704 --------------------------------

705

706 CREATE FUNCTION "write_member_history_trigger"()

707 RETURNS TRIGGER

708 LANGUAGE 'plpgsql' VOLATILE AS $$

709 BEGIN

710 IF NEW."login" != OLD."login" OR NEW."name" != OLD."name" THEN

711 INSERT INTO "member_history" ("member_id", "login", "name")

712 VALUES (NEW."id", OLD."login", OLD."name");

713 END IF;

714 RETURN NULL;

715 END;

716 $$;

717

718 CREATE TRIGGER "write_member_history"

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

720 "write_member_history_trigger"();

721

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

723 COMMENT ON TRIGGER "write_member_history" ON "member" IS 'When changing name or login of a member, create a history entry in "member_history" table';

724

725

726

727 ----------------------------

728 -- Additional constraints --

729 ----------------------------

730

731

732 CREATE FUNCTION "issue_requires_first_initiative_trigger"()

733 RETURNS TRIGGER

734 LANGUAGE 'plpgsql' VOLATILE AS $$

735 BEGIN

736 IF NOT EXISTS (

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

738 ) THEN

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

740 -- ERRCODE = 'integrity_constraint_violation',

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

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

743 END IF;

744 RETURN NULL;

745 END;

746 $$;

747

748 CREATE CONSTRAINT TRIGGER "issue_requires_first_initiative"

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

750 FOR EACH ROW EXECUTE PROCEDURE

751 "issue_requires_first_initiative_trigger"();

752

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

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

755

756

757 CREATE FUNCTION "last_initiative_deletes_issue_trigger"()

758 RETURNS TRIGGER

759 LANGUAGE 'plpgsql' VOLATILE AS $$

760 DECLARE

761 "reference_lost" BOOLEAN;

762 BEGIN

763 IF TG_OP = 'DELETE' THEN

764 "reference_lost" := TRUE;

765 ELSE

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

767 END IF;

768 IF

769 "reference_lost" AND NOT EXISTS (

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

771 )

772 THEN

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

774 END IF;

775 RETURN NULL;

776 END;

777 $$;

778

779 CREATE CONSTRAINT TRIGGER "last_initiative_deletes_issue"

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

781 FOR EACH ROW EXECUTE PROCEDURE

782 "last_initiative_deletes_issue_trigger"();

783

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

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

786

787

788 CREATE FUNCTION "initiative_requires_first_draft_trigger"()

789 RETURNS TRIGGER

790 LANGUAGE 'plpgsql' VOLATILE AS $$

791 BEGIN

792 IF NOT EXISTS (

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

794 ) THEN

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

796 -- ERRCODE = 'integrity_constraint_violation',

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

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

799 END IF;

800 RETURN NULL;

801 END;

802 $$;

803

804 CREATE CONSTRAINT TRIGGER "initiative_requires_first_draft"

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

806 FOR EACH ROW EXECUTE PROCEDURE

807 "initiative_requires_first_draft_trigger"();

808

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

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

811

812

813 CREATE FUNCTION "last_draft_deletes_initiative_trigger"()

814 RETURNS TRIGGER

815 LANGUAGE 'plpgsql' VOLATILE AS $$

816 DECLARE

817 "reference_lost" BOOLEAN;

818 BEGIN

819 IF TG_OP = 'DELETE' THEN

820 "reference_lost" := TRUE;

821 ELSE

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

823 END IF;

824 IF

825 "reference_lost" AND NOT EXISTS (

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

827 )

828 THEN

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

830 END IF;

831 RETURN NULL;

832 END;

833 $$;

834

835 CREATE CONSTRAINT TRIGGER "last_draft_deletes_initiative"

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

837 FOR EACH ROW EXECUTE PROCEDURE

838 "last_draft_deletes_initiative_trigger"();

839

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

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

842

843

844 CREATE FUNCTION "suggestion_requires_first_opinion_trigger"()

845 RETURNS TRIGGER

846 LANGUAGE 'plpgsql' VOLATILE AS $$

847 BEGIN

848 IF NOT EXISTS (

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

850 ) THEN

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

852 END IF;

853 RETURN NULL;

854 END;

855 $$;

856

857 CREATE CONSTRAINT TRIGGER "suggestion_requires_first_opinion"

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

859 FOR EACH ROW EXECUTE PROCEDURE

860 "suggestion_requires_first_opinion_trigger"();

861

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

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

864

865

866 CREATE FUNCTION "last_opinion_deletes_suggestion_trigger"()

867 RETURNS TRIGGER

868 LANGUAGE 'plpgsql' VOLATILE AS $$

869 DECLARE

870 "reference_lost" BOOLEAN;

871 BEGIN

872 IF TG_OP = 'DELETE' THEN

873 "reference_lost" := TRUE;

874 ELSE

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

876 END IF;

877 IF

878 "reference_lost" AND NOT EXISTS (

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

880 )

881 THEN

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

883 END IF;

884 RETURN NULL;

885 END;

886 $$;

887

888 CREATE CONSTRAINT TRIGGER "last_opinion_deletes_suggestion"

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

890 FOR EACH ROW EXECUTE PROCEDURE

891 "last_opinion_deletes_suggestion_trigger"();

892

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

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

895

896

897

898 --------------------------------------------------------------------

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

900 --------------------------------------------------------------------

901

902 CREATE FUNCTION "autofill_issue_id_trigger"()

903 RETURNS TRIGGER

904 LANGUAGE 'plpgsql' VOLATILE AS $$

905 BEGIN

906 IF NEW."issue_id" ISNULL THEN

907 SELECT "issue_id" INTO NEW."issue_id"

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

909 END IF;

910 RETURN NEW;

911 END;

912 $$;

913

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

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

916

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

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

919

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

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

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

923

924

925 CREATE FUNCTION "autofill_initiative_id_trigger"()

926 RETURNS TRIGGER

927 LANGUAGE 'plpgsql' VOLATILE AS $$

928 BEGIN

929 IF NEW."initiative_id" ISNULL THEN

930 SELECT "initiative_id" INTO NEW."initiative_id"

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

932 END IF;

933 RETURN NEW;

934 END;

935 $$;

936

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

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

939

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

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

942

943

944

945 -----------------------------------------------------

946 -- Automatic calculation of certain default values --

947 -----------------------------------------------------

948

949 CREATE FUNCTION "copy_autoreject_trigger"()

950 RETURNS TRIGGER

951 LANGUAGE 'plpgsql' VOLATILE AS $$

952 BEGIN

953 IF NEW."autoreject" ISNULL THEN

954 SELECT "membership"."autoreject" INTO NEW."autoreject"

955 FROM "issue" JOIN "membership"

956 ON "issue"."area_id" = "membership"."area_id"

957 WHERE "issue"."id" = NEW."issue_id"

958 AND "membership"."member_id" = NEW."member_id";

959 END IF;

960 IF NEW."autoreject" ISNULL THEN

961 NEW."autoreject" := FALSE;

962 END IF;

963 RETURN NEW;

964 END;

965 $$;

966

967 CREATE TRIGGER "copy_autoreject" BEFORE INSERT OR UPDATE ON "interest"

968 FOR EACH ROW EXECUTE PROCEDURE "copy_autoreject_trigger"();

969

970 COMMENT ON FUNCTION "copy_autoreject_trigger"() IS 'Implementation of trigger "copy_autoreject" on table "interest"';

971 COMMENT ON TRIGGER "copy_autoreject" ON "interest" IS 'If "autoreject" is NULL, then copy it from the area setting, or set to FALSE, if no membership existent';

972

973

974 CREATE FUNCTION "supporter_default_for_draft_id_trigger"()

975 RETURNS TRIGGER

976 LANGUAGE 'plpgsql' VOLATILE AS $$

977 BEGIN

978 IF NEW."draft_id" ISNULL THEN

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

980 WHERE "initiative_id" = NEW."initiative_id";

981 END IF;

982 RETURN NEW;

983 END;

984 $$;

985

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

987 FOR EACH ROW EXECUTE PROCEDURE "supporter_default_for_draft_id_trigger"();

988

989 COMMENT ON FUNCTION "supporter_default_for_draft_id_trigger"() IS 'Implementation of trigger "default_for_draft" on table "supporter"';

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

991

992

993

994 ----------------------------------------

995 -- Automatic creation of dependencies --

996 ----------------------------------------

997

998 CREATE FUNCTION "autocreate_interest_trigger"()

999 RETURNS TRIGGER

1000 LANGUAGE 'plpgsql' VOLATILE AS $$

1001 BEGIN

1002 IF NOT EXISTS (

1003 SELECT NULL FROM "initiative" JOIN "interest"

1004 ON "initiative"."issue_id" = "interest"."issue_id"

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

1006 AND "interest"."member_id" = NEW."member_id"

1007 ) THEN

1008 BEGIN

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

1010 SELECT "issue_id", NEW."member_id"

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

1012 EXCEPTION WHEN unique_violation THEN END;

1013 END IF;

1014 RETURN NEW;

1015 END;

1016 $$;

1017

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

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

1020

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

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

1023

1024

1025 CREATE FUNCTION "autocreate_supporter_trigger"()

1026 RETURNS TRIGGER

1027 LANGUAGE 'plpgsql' VOLATILE AS $$

1028 BEGIN

1029 IF NOT EXISTS (

1030 SELECT NULL FROM "suggestion" JOIN "supporter"

1031 ON "suggestion"."initiative_id" = "supporter"."initiative_id"

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

1033 AND "supporter"."member_id" = NEW."member_id"

1034 ) THEN

1035 BEGIN

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

1037 SELECT "initiative_id", NEW."member_id"

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

1039 EXCEPTION WHEN unique_violation THEN END;

1040 END IF;

1041 RETURN NEW;

1042 END;

1043 $$;

1044

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

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

1047

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

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

1050

1051

1052

1053 ------------------------------------------

1054 -- Views and helper functions for views --

1055 ------------------------------------------

1056

1057

1058 CREATE VIEW "global_delegation" AS

1059 SELECT

1060 "delegation"."id",

1061 "delegation"."truster_id",

1062 "delegation"."trustee_id"

1063 FROM "delegation" JOIN "member"

1064 ON "delegation"."trustee_id" = "member"."id"

1065 WHERE "delegation"."scope" = 'global' AND "member"."active";

1066

1067 COMMENT ON VIEW "global_delegation" IS 'Global delegations to active members';

1068

1069

1070 CREATE VIEW "area_delegation" AS

1071 SELECT "subquery".* FROM (

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

1073 "area"."id" AS "area_id",

1074 "delegation"."id",

1075 "delegation"."truster_id",

1076 "delegation"."trustee_id",

1077 "delegation"."scope"

1078 FROM "area" JOIN "delegation"

1079 ON "delegation"."scope" = 'global'

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

1081 ORDER BY

1082 "area"."id",

1083 "delegation"."truster_id",

1084 "delegation"."scope" DESC

1085 ) AS "subquery"

1086 JOIN "member" ON "subquery"."trustee_id" = "member"."id"

1087 WHERE "member"."active";

1088

1089 COMMENT ON VIEW "area_delegation" IS 'Active delegations for areas';

1090

1091

1092 CREATE VIEW "issue_delegation" AS

1093 SELECT "subquery".* FROM (

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

1095 "issue"."id" AS "issue_id",

1096 "delegation"."id",

1097 "delegation"."truster_id",

1098 "delegation"."trustee_id",

1099 "delegation"."scope"

1100 FROM "issue" JOIN "delegation"

1101 ON "delegation"."scope" = 'global'

1102 OR "delegation"."area_id" = "issue"."area_id"

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

1104 ORDER BY

1105 "issue"."id",

1106 "delegation"."truster_id",

1107 "delegation"."scope" DESC

1108 ) AS "subquery"

1109 JOIN "member" ON "subquery"."trustee_id" = "member"."id"

1110 WHERE "member"."active";

1111

1112 COMMENT ON VIEW "issue_delegation" IS 'Active delegations for issues';

1113

1114

1115 CREATE FUNCTION "membership_weight_with_skipping"

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

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

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

1119 RETURNS INT4

1120 LANGUAGE 'plpgsql' STABLE AS $$

1121 DECLARE

1122 "sum_v" INT4;

1123 "delegation_row" "area_delegation"%ROWTYPE;

1124 BEGIN

1125 "sum_v" := 1;

1126 FOR "delegation_row" IN

1127 SELECT "area_delegation".*

1128 FROM "area_delegation" LEFT JOIN "membership"

1129 ON "membership"."area_id" = "area_id_p"

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

1131 WHERE "area_delegation"."area_id" = "area_id_p"

1132 AND "area_delegation"."trustee_id" = "member_id_p"

1133 AND "membership"."member_id" ISNULL

1134 LOOP

1135 IF NOT

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

1137 THEN

1138 "sum_v" := "sum_v" + "membership_weight_with_skipping"(

1139 "area_id_p",

1140 "delegation_row"."truster_id",

1141 "skip_member_ids_p" || "delegation_row"."truster_id"

1142 );

1143 END IF;

1144 END LOOP;

1145 RETURN "sum_v";

1146 END;

1147 $$;

1148

1149 COMMENT ON FUNCTION "membership_weight_with_skipping"

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

1151 "member"."id"%TYPE,

1152 INT4[] )

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

1154

1155

1156 CREATE FUNCTION "membership_weight"

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

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

1159 RETURNS INT4

1160 LANGUAGE 'plpgsql' STABLE AS $$

1161 BEGIN

1162 RETURN "membership_weight_with_skipping"(

1163 "area_id_p",

1164 "member_id_p",

1165 ARRAY["member_id_p"]

1166 );

1167 END;

1168 $$;

1169

1170 COMMENT ON FUNCTION "membership_weight"

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

1172 "member"."id"%TYPE )

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

1174

1175

1176 CREATE VIEW "member_count_view" AS

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

1178

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

1180

1181

1182 CREATE VIEW "area_member_count" AS

1183 SELECT

1184 "area"."id" AS "area_id",

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

1186 coalesce(

1187 sum(

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

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

1190 ELSE 0 END

1191 )

1192 ) AS "member_weight",

1193 coalesce(

1194 sum(

1195 CASE WHEN "member"."id" NOTNULL AND "membership"."autoreject" THEN

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

1197 ELSE 0 END

1198 )

1199 ) AS "autoreject_weight"

1200 FROM "area"

1201 LEFT JOIN "membership"

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

1203 LEFT JOIN "member"

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

1205 AND "member"."active"

1206 GROUP BY "area"."id";

1207

1208 COMMENT ON VIEW "area_member_count" IS 'View used to update "member_count" column of table "area"';

1209

1210

1211 CREATE VIEW "opening_draft" AS

1212 SELECT "draft".* FROM (

1213 SELECT

1214 "initiative"."id" AS "initiative_id",

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

1216 FROM "initiative" JOIN "draft"

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

1218 GROUP BY "initiative"."id"

1219 ) AS "subquery"

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

1221

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

1223

1224

1225 CREATE VIEW "current_draft" AS

1226 SELECT "draft".* FROM (

1227 SELECT

1228 "initiative"."id" AS "initiative_id",

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

1230 FROM "initiative" JOIN "draft"

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

1232 GROUP BY "initiative"."id"

1233 ) AS "subquery"

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

1235

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

1237

1238

1239 CREATE VIEW "critical_opinion" AS

1240 SELECT * FROM "opinion"

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

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

1243

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

1245

1246

1247 CREATE VIEW "battle" AS

1248 SELECT

1249 "issue"."id" AS "issue_id",

1250 "winning_initiative"."id" AS "winning_initiative_id",

1251 "losing_initiative"."id" AS "losing_initiative_id",

1252 sum(

1253 CASE WHEN

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

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

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

1257 ) AS "count"

1258 FROM "issue"

1259 LEFT JOIN "direct_voter"

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

1261 JOIN "initiative" AS "winning_initiative"

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

1263 AND "winning_initiative"."agreed"

1264 JOIN "initiative" AS "losing_initiative"

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

1266 AND "losing_initiative"."agreed"

1267 LEFT JOIN "vote" AS "better_vote"

1268 ON "direct_voter"."member_id" = "better_vote"."member_id"

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

1270 LEFT JOIN "vote" AS "worse_vote"

1271 ON "direct_voter"."member_id" = "worse_vote"."member_id"

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

1273 WHERE

1274 "winning_initiative"."id" != "losing_initiative"."id"

1275 GROUP BY

1276 "issue"."id",

1277 "winning_initiative"."id",

1278 "losing_initiative"."id";

1279

1280 COMMENT ON VIEW "battle" IS 'Number of members preferring one initiative over another';

1281

1282

1283 CREATE VIEW "expired_session" AS

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

1285

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

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

1288

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

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

1291

1292

1293 CREATE VIEW "open_issue" AS

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

1295

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

1297

1298

1299 CREATE VIEW "issue_with_ranks_missing" AS

1300 SELECT * FROM "issue"

1301 WHERE "fully_frozen" NOTNULL

1302 AND "closed" NOTNULL

1303 AND "ranks_available" = FALSE;

1304

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

1306

1307

1308 CREATE VIEW "member_contingent" AS

1309 SELECT

1310 "member"."id" AS "member_id",

1311 "contingent"."time_frame",

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

1313 (

1314 SELECT count(1) FROM "draft"

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

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

1317 ) + (

1318 SELECT count(1) FROM "suggestion"

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

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

1321 )

1322 ELSE NULL END AS "text_entry_count",

1323 "contingent"."text_entry_limit",

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

1325 SELECT count(1) FROM "opening_draft"

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

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

1328 ) ELSE NULL END AS "initiative_count",

1329 "contingent"."initiative_limit"

1330 FROM "member" CROSS JOIN "contingent";

1331

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

1333

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

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

1336

1337

1338 CREATE VIEW "member_contingent_left" AS

1339 SELECT

1340 "member_id",

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

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

1343 FROM "member_contingent" GROUP BY "member_id";

1344

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

1346

1347

1348

1349 --------------------------------------------------

1350 -- Set returning function for delegation chains --

1351 --------------------------------------------------

1352

1353

1354 CREATE TYPE "delegation_chain_loop_tag" AS ENUM

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

1356

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

1358

1359

1360 CREATE TYPE "delegation_chain_row" AS (

1361 "index" INT4,

1362 "member_id" INT4,

1363 "member_active" BOOLEAN,

1364 "participation" BOOLEAN,

1365 "overridden" BOOLEAN,

1366 "scope_in" "delegation_scope",

1367 "scope_out" "delegation_scope",

1368 "loop" "delegation_chain_loop_tag" );

1369

1370 COMMENT ON TYPE "delegation_chain_row" IS 'Type of rows returned by "delegation_chain"(...) functions';

1371

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

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

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

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

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

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

1378

1379

1380 CREATE FUNCTION "delegation_chain"

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

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

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

1384 "simulate_trustee_id_p" "member"."id"%TYPE )

1385 RETURNS SETOF "delegation_chain_row"

1386 LANGUAGE 'plpgsql' STABLE AS $$

1387 DECLARE

1388 "issue_row" "issue"%ROWTYPE;

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

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

1391 "output_row" "delegation_chain_row";

1392 "output_rows" "delegation_chain_row"[];

1393 "delegation_row" "delegation"%ROWTYPE;

1394 "row_count" INT4;

1395 "i" INT4;

1396 "loop_v" BOOLEAN;

1397 BEGIN

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

1399 "visited_member_ids" := '{}';

1400 "loop_member_id_v" := NULL;

1401 "output_rows" := '{}';

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

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

1404 "output_row"."member_active" := TRUE;

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

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

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

1408 LOOP

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

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

1411 ELSE

1412 "visited_member_ids" :=

1413 "visited_member_ids" || "output_row"."member_id";

1414 END IF;

1415 IF "output_row"."participation" THEN

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

1417 END IF;

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

1419 IF EXISTS (

1420 SELECT NULL FROM "member"

1421 WHERE "id" = "output_row"."member_id" AND "active"

1422 ) THEN

1423 IF "area_id_p" ISNULL AND "issue_id_p" ISNULL THEN

1424 SELECT * INTO "delegation_row" FROM "delegation"

1425 WHERE "truster_id" = "output_row"."member_id"

1426 AND "scope" = 'global';

1427 ELSIF "area_id_p" NOTNULL AND "issue_id_p" ISNULL THEN

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

1429 SELECT NULL FROM "membership"

1430 WHERE "area_id" = "area_id_p"

1431 AND "member_id" = "output_row"."member_id"

1432 );

1433 SELECT * INTO "delegation_row" FROM "delegation"

1434 WHERE "truster_id" = "output_row"."member_id"

1435 AND ("scope" = 'global' OR "area_id" = "area_id_p")

1436 ORDER BY "scope" DESC;

1437 ELSIF "area_id_p" ISNULL AND "issue_id_p" NOTNULL THEN

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

1439 SELECT NULL FROM "interest"

1440 WHERE "issue_id" = "issue_id_p"

1441 AND "member_id" = "output_row"."member_id"

1442 );

1443 SELECT * INTO "delegation_row" FROM "delegation"

1444 WHERE "truster_id" = "output_row"."member_id"

1445 AND ("scope" = 'global' OR

1446 "area_id" = "issue_row"."area_id" OR

1447 "issue_id" = "issue_id_p"

1448 )

1449 ORDER BY "scope" DESC;

1450 ELSE

1451 RAISE EXCEPTION 'Either area_id or issue_id or both must be NULL.';

1452 END IF;

1453 ELSE

1454 "output_row"."member_active" := FALSE;

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

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

1457 "delegation_row" := ROW(NULL);

1458 END IF;

1459 IF

1460 "output_row"."member_id" = "member_id_p" AND

1461 "simulate_trustee_id_p" NOTNULL

1462 THEN

1463 "output_row"."scope_out" := CASE

1464 WHEN "area_id_p" ISNULL AND "issue_id_p" ISNULL THEN 'global'

1465 WHEN "area_id_p" NOTNULL AND "issue_id_p" ISNULL THEN 'area'

1466 WHEN "area_id_p" ISNULL AND "issue_id_p" NOTNULL THEN 'issue'

1467 END;

1468 "output_rows" := "output_rows" || "output_row";

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

1470 ELSIF "delegation_row"."trustee_id" NOTNULL THEN

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

1472 "output_rows" := "output_rows" || "output_row";

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

1474 ELSE

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

1476 "output_rows" := "output_rows" || "output_row";

1477 EXIT;

1478 END IF;

1479 EXIT WHEN "loop_member_id_v" NOTNULL;

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

1481 END LOOP;

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

1483 "i" := 1;

1484 "loop_v" := FALSE;

1485 LOOP

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

1487 EXIT WHEN "output_row"."member_id" ISNULL;

1488 IF "loop_v" THEN

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

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

1491 ELSIF "i" = "row_count" THEN

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

1493 ELSE

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

1495 END IF;

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

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

1498 "loop_v" := TRUE;

1499 END IF;

1500 IF "area_id_p" ISNULL AND "issue_id_p" ISNULL THEN

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

1502 END IF;

1503 RETURN NEXT "output_row";

1504 "i" := "i" + 1;

1505 END LOOP;

1506 RETURN;

1507 END;

1508 $$;

1509

1510 COMMENT ON FUNCTION "delegation_chain"

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

1512 "area"."id"%TYPE,

1513 "issue"."id"%TYPE,

1514 "member"."id"%TYPE )

1515 IS 'Helper function for frontends to display delegation chains; Not part of internal voting logic';

1516

1517 CREATE FUNCTION "delegation_chain"

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

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

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

1521 RETURNS SETOF "delegation_chain_row"

1522 LANGUAGE 'plpgsql' STABLE AS $$

1523 DECLARE

1524 "result_row" "delegation_chain_row";

1525 BEGIN

1526 FOR "result_row" IN

1527 SELECT * FROM "delegation_chain"(

1528 "member_id_p", "area_id_p", "issue_id_p", NULL

1529 )

1530 LOOP

1531 RETURN NEXT "result_row";

1532 END LOOP;

1533 RETURN;

1534 END;

1535 $$;

1536

1537 COMMENT ON FUNCTION "delegation_chain"

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

1539 "area"."id"%TYPE,

1540 "issue"."id"%TYPE )

1541 IS 'Shortcut for "delegation_chain"(...) function where 4th parameter is null';

1542

1543

1544

1545 ------------------------------

1546 -- Comparison by vote count --

1547 ------------------------------

1548

1549 CREATE FUNCTION "vote_ratio"

1550 ( "positive_votes_p" "initiative"."positive_votes"%TYPE,

1551 "negative_votes_p" "initiative"."negative_votes"%TYPE )

1552 RETURNS FLOAT8

1553 LANGUAGE 'plpgsql' STABLE AS $$

1554 DECLARE

1555 "total_v" INT4;

1556 BEGIN

1557 "total_v" := "positive_votes_p" + "negative_votes_p";

1558 IF "total_v" > 0 THEN

1559 RETURN "positive_votes_p"::FLOAT8 / "total_v"::FLOAT8;

1560 ELSE

1561 RETURN 0.5;

1562 END IF;

1563 END;

1564 $$;

1565

1566 COMMENT ON FUNCTION "vote_ratio"

1567 ( "initiative"."positive_votes"%TYPE,

1568 "initiative"."negative_votes"%TYPE )

1569 IS 'Ratio of positive votes to sum of positive and negative votes; 0.5, if there are neither positive nor negative votes';

1570

1571

1572

1573 ------------------------------------------------

1574 -- Locking for snapshots and voting procedure --

1575 ------------------------------------------------

1576

1577 CREATE FUNCTION "global_lock"() RETURNS VOID

1578 LANGUAGE 'plpgsql' VOLATILE AS $$

1579 BEGIN

1580 -- NOTE: PostgreSQL allows reading, while tables are locked in

1581 -- exclusive move. Transactions should be kept short anyway!

1582 LOCK TABLE "member" IN EXCLUSIVE MODE;

1583 LOCK TABLE "area" IN EXCLUSIVE MODE;

1584 LOCK TABLE "membership" IN EXCLUSIVE MODE;

1585 -- NOTE: "member", "area" and "membership" are locked first to

1586 -- prevent deadlocks in combination with "calculate_member_counts"()

1587 LOCK TABLE "policy" IN EXCLUSIVE MODE;

1588 LOCK TABLE "issue" IN EXCLUSIVE MODE;

1589 LOCK TABLE "initiative" IN EXCLUSIVE MODE;

1590 LOCK TABLE "draft" IN EXCLUSIVE MODE;

1591 LOCK TABLE "suggestion" IN EXCLUSIVE MODE;

1592 LOCK TABLE "interest" IN EXCLUSIVE MODE;

1593 LOCK TABLE "initiator" IN EXCLUSIVE MODE;

1594 LOCK TABLE "supporter" IN EXCLUSIVE MODE;

1595 LOCK TABLE "opinion" IN EXCLUSIVE MODE;

1596 LOCK TABLE "delegation" IN EXCLUSIVE MODE;

1597 LOCK TABLE "direct_population_snapshot" IN EXCLUSIVE MODE;

1598 LOCK TABLE "delegating_population_snapshot" IN EXCLUSIVE MODE;

1599 LOCK TABLE "direct_interest_snapshot" IN EXCLUSIVE MODE;

1600 LOCK TABLE "delegating_interest_snapshot" IN EXCLUSIVE MODE;

1601 LOCK TABLE "direct_supporter_snapshot" IN EXCLUSIVE MODE;

1602 LOCK TABLE "direct_voter" IN EXCLUSIVE MODE;

1603 LOCK TABLE "delegating_voter" IN EXCLUSIVE MODE;

1604 LOCK TABLE "vote" IN EXCLUSIVE MODE;

1605 RETURN;

1606 END;

1607 $$;

1608

1609 COMMENT ON FUNCTION "global_lock"() IS 'Locks all tables related to support/voting until end of transaction; read access is still possible though';

1610

1611

1612

1613 -------------------------------

1614 -- Materialize member counts --

1615 -------------------------------

1616

1617 CREATE FUNCTION "calculate_member_counts"()

1618 RETURNS VOID

1619 LANGUAGE 'plpgsql' VOLATILE AS $$

1620 BEGIN

1621 LOCK TABLE "member" IN EXCLUSIVE MODE;

1622 LOCK TABLE "area" IN EXCLUSIVE MODE;

1623 LOCK TABLE "membership" IN EXCLUSIVE MODE;

1624 DELETE FROM "member_count";

1625 INSERT INTO "member_count" ("total_count")

1626 SELECT "total_count" FROM "member_count_view";

1627 UPDATE "area" SET

1628 "direct_member_count" = "view"."direct_member_count",

1629 "member_weight" = "view"."member_weight",

1630 "autoreject_weight" = "view"."autoreject_weight"

1631 FROM "area_member_count" AS "view"

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

1633 RETURN;

1634 END;

1635 $$;

1636

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

1638

1639

1640

1641 ------------------------------

1642 -- Calculation of snapshots --

1643 ------------------------------

1644

1645 CREATE FUNCTION "weight_of_added_delegations_for_population_snapshot"

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

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

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

1649 RETURNS "direct_population_snapshot"."weight"%TYPE

1650 LANGUAGE 'plpgsql' VOLATILE AS $$

1651 DECLARE

1652 "issue_delegation_row" "issue_delegation"%ROWTYPE;

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

1654 "weight_v" INT4;

1655 "sub_weight_v" INT4;

1656 BEGIN

1657 "weight_v" := 0;

1658 FOR "issue_delegation_row" IN

1659 SELECT * FROM "issue_delegation"

1660 WHERE "trustee_id" = "member_id_p"

1661 AND "issue_id" = "issue_id_p"

1662 LOOP

1663 IF NOT EXISTS (

1664 SELECT NULL FROM "direct_population_snapshot"

1665 WHERE "issue_id" = "issue_id_p"

1666 AND "event" = 'periodic'

1667 AND "member_id" = "issue_delegation_row"."truster_id"

1668 ) AND NOT EXISTS (

1669 SELECT NULL FROM "delegating_population_snapshot"

1670 WHERE "issue_id" = "issue_id_p"

1671 AND "event" = 'periodic'

1672 AND "member_id" = "issue_delegation_row"."truster_id"

1673 ) THEN

1674 "delegate_member_ids_v" :=

1675 "member_id_p" || "delegate_member_ids_p";

1676 INSERT INTO "delegating_population_snapshot" (

1677 "issue_id",

1678 "event",

1679 "member_id",

1680 "scope",

1681 "delegate_member_ids"

1682 ) VALUES (

1683 "issue_id_p",

1684 'periodic',

1685 "issue_delegation_row"."truster_id",

1686 "issue_delegation_row"."scope",

1687 "delegate_member_ids_v"

1688 );

1689 "sub_weight_v" := 1 +

1690 "weight_of_added_delegations_for_population_snapshot"(

1691 "issue_id_p",

1692 "issue_delegation_row"."truster_id",

1693 "delegate_member_ids_v"

1694 );

1695 UPDATE "delegating_population_snapshot"

1696 SET "weight" = "sub_weight_v"

1697 WHERE "issue_id" = "issue_id_p"

1698 AND "event" = 'periodic'

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

1700 "weight_v" := "weight_v" + "sub_weight_v";

1701 END IF;

1702 END LOOP;

1703 RETURN "weight_v";

1704 END;

1705 $$;

1706

1707 COMMENT ON FUNCTION "weight_of_added_delegations_for_population_snapshot"

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

1709 "member"."id"%TYPE,

1710 "delegating_population_snapshot"."delegate_member_ids"%TYPE )

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

1712

1713

1714 CREATE FUNCTION "create_population_snapshot"

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

1716 RETURNS VOID

1717 LANGUAGE 'plpgsql' VOLATILE AS $$

1718 DECLARE

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

1720 BEGIN

1721 DELETE FROM "direct_population_snapshot"

1722 WHERE "issue_id" = "issue_id_p"

1723 AND "event" = 'periodic';

1724 DELETE FROM "delegating_population_snapshot"

1725 WHERE "issue_id" = "issue_id_p"

1726 AND "event" = 'periodic';

1727 INSERT INTO "direct_population_snapshot"

1728 ("issue_id", "event", "member_id", "interest_exists")

1729 SELECT DISTINCT ON ("issue_id", "member_id")

1730 "issue_id_p" AS "issue_id",

1731 'periodic' AS "event",

1732 "subquery"."member_id",

1733 "subquery"."interest_exists"

1734 FROM (

1735 SELECT

1736 "member"."id" AS "member_id",

1737 FALSE AS "interest_exists"

1738 FROM "issue"

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

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

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

1742 WHERE "issue"."id" = "issue_id_p"

1743 AND "member"."active"

1744 UNION

1745 SELECT

1746 "member"."id" AS "member_id",

1747 TRUE AS "interest_exists"

1748 FROM "interest" JOIN "member"

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

1750 WHERE "interest"."issue_id" = "issue_id_p"

1751 AND "member"."active"

1752 ) AS "subquery"

1753 ORDER BY

1754 "issue_id_p",

1755 "subquery"."member_id",

1756 "subquery"."interest_exists" DESC;

1757 FOR "member_id_v" IN

1758 SELECT "member_id" FROM "direct_population_snapshot"

1759 WHERE "issue_id" = "issue_id_p"

1760 AND "event" = 'periodic'

1761 LOOP

1762 UPDATE "direct_population_snapshot" SET

1763 "weight" = 1 +

1764 "weight_of_added_delegations_for_population_snapshot"(

1765 "issue_id_p",

1766 "member_id_v",

1767 '{}'

1768 )

1769 WHERE "issue_id" = "issue_id_p"

1770 AND "event" = 'periodic'

1771 AND "member_id" = "member_id_v";

1772 END LOOP;

1773 RETURN;

1774 END;

1775 $$;

1776

1777 COMMENT ON FUNCTION "create_population_snapshot"

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

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

1780

1781

1782 CREATE FUNCTION "weight_of_added_delegations_for_interest_snapshot"

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

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

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

1786 RETURNS "direct_interest_snapshot"."weight"%TYPE

1787 LANGUAGE 'plpgsql' VOLATILE AS $$

1788 DECLARE

1789 "issue_delegation_row" "issue_delegation"%ROWTYPE;

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

1791 "weight_v" INT4;

1792 "sub_weight_v" INT4;

1793 BEGIN

1794 "weight_v" := 0;

1795 FOR "issue_delegation_row" IN

1796 SELECT * FROM "issue_delegation"

1797 WHERE "trustee_id" = "member_id_p"

1798 AND "issue_id" = "issue_id_p"

1799 LOOP

1800 IF NOT EXISTS (

1801 SELECT NULL FROM "direct_interest_snapshot"

1802 WHERE "issue_id" = "issue_id_p"

1803 AND "event" = 'periodic'

1804 AND "member_id" = "issue_delegation_row"."truster_id"

1805 ) AND NOT EXISTS (

1806 SELECT NULL FROM "delegating_interest_snapshot"

1807 WHERE "issue_id" = "issue_id_p"

1808 AND "event" = 'periodic'

1809 AND "member_id" = "issue_delegation_row"."truster_id"

1810 ) THEN

1811 "delegate_member_ids_v" :=

1812 "member_id_p" || "delegate_member_ids_p";

1813 INSERT INTO "delegating_interest_snapshot" (

1814 "issue_id",

1815 "event",

1816 "member_id",

1817 "scope",

1818 "delegate_member_ids"

1819 ) VALUES (

1820 "issue_id_p",

1821 'periodic',

1822 "issue_delegation_row"."truster_id",

1823 "issue_delegation_row"."scope",

1824 "delegate_member_ids_v"

1825 );

1826 "sub_weight_v" := 1 +

1827 "weight_of_added_delegations_for_interest_snapshot"(

1828 "issue_id_p",

1829 "issue_delegation_row"."truster_id",

1830 "delegate_member_ids_v"

1831 );

1832 UPDATE "delegating_interest_snapshot"

1833 SET "weight" = "sub_weight_v"

1834 WHERE "issue_id" = "issue_id_p"

1835 AND "event" = 'periodic'

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

1837 "weight_v" := "weight_v" + "sub_weight_v";

1838 END IF;

1839 END LOOP;

1840 RETURN "weight_v";

1841 END;

1842 $$;

1843

1844 COMMENT ON FUNCTION "weight_of_added_delegations_for_interest_snapshot"

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

1846 "member"."id"%TYPE,

1847 "delegating_interest_snapshot"."delegate_member_ids"%TYPE )

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

1849

1850

1851 CREATE FUNCTION "create_interest_snapshot"

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

1853 RETURNS VOID

1854 LANGUAGE 'plpgsql' VOLATILE AS $$

1855 DECLARE

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

1857 BEGIN

1858 DELETE FROM "direct_interest_snapshot"

1859 WHERE "issue_id" = "issue_id_p"

1860 AND "event" = 'periodic';

1861 DELETE FROM "delegating_interest_snapshot"

1862 WHERE "issue_id" = "issue_id_p"

1863 AND "event" = 'periodic';

1864 DELETE FROM "direct_supporter_snapshot"

1865 WHERE "issue_id" = "issue_id_p"

1866 AND "event" = 'periodic';

1867 INSERT INTO "direct_interest_snapshot"

1868 ("issue_id", "event", "member_id", "voting_requested")

1869 SELECT

1870 "issue_id_p" AS "issue_id",

1871 'periodic' AS "event",

1872 "member"."id" AS "member_id",

1873 "interest"."voting_requested"

1874 FROM "interest" JOIN "member"

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

1876 WHERE "interest"."issue_id" = "issue_id_p"

1877 AND "member"."active";

1878 FOR "member_id_v" IN

1879 SELECT "member_id" FROM "direct_interest_snapshot"

1880 WHERE "issue_id" = "issue_id_p"

1881 AND "event" = 'periodic'

1882 LOOP

1883 UPDATE "direct_interest_snapshot" SET

1884 "weight" = 1 +

1885 "weight_of_added_delegations_for_interest_snapshot"(

1886 "issue_id_p",

1887 "member_id_v",

1888 '{}'

1889 )

1890 WHERE "issue_id" = "issue_id_p"

1891 AND "event" = 'periodic'

1892 AND "member_id" = "member_id_v";

1893 END LOOP;

1894 INSERT INTO "direct_supporter_snapshot"

1895 ( "issue_id", "initiative_id", "event", "member_id",

1896 "informed", "satisfied" )

1897 SELECT

1898 "issue_id_p" AS "issue_id",

1899 "initiative"."id" AS "initiative_id",

1900 'periodic' AS "event",

1901 "member"."id" AS "member_id",

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

1903 NOT EXISTS (

1904 SELECT NULL FROM "critical_opinion"

1905 WHERE "initiative_id" = "initiative"."id"

1906 AND "member_id" = "member"."id"

1907 ) AS "satisfied"

1908 FROM "supporter"

1909 JOIN "member"

1910 ON "supporter"."member_id" = "member"."id"

1911 JOIN "initiative"

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

1913 JOIN "current_draft"

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

1915 JOIN "direct_interest_snapshot"

1916 ON "member"."id" = "direct_interest_snapshot"."member_id"

1917 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"

1918 AND "event" = 'periodic'

1919 WHERE "member"."active"

1920 AND "initiative"."issue_id" = "issue_id_p";

1921 RETURN;

1922 END;

1923 $$;

1924

1925 COMMENT ON FUNCTION "create_interest_snapshot"

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

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

1928

1929

1930 CREATE FUNCTION "create_snapshot"

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

1932 RETURNS VOID

1933 LANGUAGE 'plpgsql' VOLATILE AS $$

1934 DECLARE

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

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

1937 BEGIN

1938 PERFORM "global_lock"();

1939 PERFORM "create_population_snapshot"("issue_id_p");

1940 PERFORM "create_interest_snapshot"("issue_id_p");

1941 UPDATE "issue" SET

1942 "snapshot" = now(),

1943 "latest_snapshot_event" = 'periodic',

1944 "population" = (

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

1946 FROM "direct_population_snapshot"

1947 WHERE "issue_id" = "issue_id_p"

1948 AND "event" = 'periodic'

1949 ),

1950 "vote_now" = (

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

1952 FROM "direct_interest_snapshot"

1953 WHERE "issue_id" = "issue_id_p"

1954 AND "event" = 'periodic'

1955 AND "voting_requested" = TRUE

1956 ),

1957 "vote_later" = (

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

1959 FROM "direct_interest_snapshot"

1960 WHERE "issue_id" = "issue_id_p"

1961 AND "event" = 'periodic'

1962 AND "voting_requested" = FALSE

1963 )

1964 WHERE "id" = "issue_id_p";

1965 FOR "initiative_id_v" IN

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

1967 LOOP

1968 UPDATE "initiative" SET

1969 "supporter_count" = (

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

1971 FROM "direct_interest_snapshot" AS "di"

1972 JOIN "direct_supporter_snapshot" AS "ds"

1973 ON "di"."member_id" = "ds"."member_id"

1974 WHERE "di"."issue_id" = "issue_id_p"

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

1976 AND "ds"."initiative_id" = "initiative_id_v"

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

1978 ),

1979 "informed_supporter_count" = (

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

1981 FROM "direct_interest_snapshot" AS "di"

1982 JOIN "direct_supporter_snapshot" AS "ds"

1983 ON "di"."member_id" = "ds"."member_id"

1984 WHERE "di"."issue_id" = "issue_id_p"

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

1986 AND "ds"."initiative_id" = "initiative_id_v"

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

1988 AND "ds"."informed"

1989 ),

1990 "satisfied_supporter_count" = (

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

1992 FROM "direct_interest_snapshot" AS "di"

1993 JOIN "direct_supporter_snapshot" AS "ds"

1994 ON "di"."member_id" = "ds"."member_id"

1995 WHERE "di"."issue_id" = "issue_id_p"

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

1997 AND "ds"."initiative_id" = "initiative_id_v"

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

1999 AND "ds"."satisfied"

2000 ),

2001 "satisfied_informed_supporter_count" = (

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

2003 FROM "direct_interest_snapshot" AS "di"

2004 JOIN "direct_supporter_snapshot" AS "ds"

2005 ON "di"."member_id" = "ds"."member_id"

2006 WHERE "di"."issue_id" = "issue_id_p"

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

2008 AND "ds"."initiative_id" = "initiative_id_v"

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

2010 AND "ds"."informed"

2011 AND "ds"."satisfied"

2012 )

2013 WHERE "id" = "initiative_id_v";

2014 FOR "suggestion_id_v" IN

2015 SELECT "id" FROM "suggestion"

2016 WHERE "initiative_id" = "initiative_id_v"

2017 LOOP

2018 UPDATE "suggestion" SET

2019 "minus2_unfulfilled_count" = (

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

2021 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"

2022 ON "opinion"."member_id" = "snapshot"."member_id"

2023 WHERE "opinion"."suggestion_id" = "suggestion_id_v"

2024 AND "snapshot"."issue_id" = "issue_id_p"

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

2026 AND "opinion"."fulfilled" = FALSE

2027 ),

2028 "minus2_fulfilled_count" = (

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

2030 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"

2031 ON "opinion"."member_id" = "snapshot"."member_id"

2032 WHERE "opinion"."suggestion_id" = "suggestion_id_v"

2033 AND "snapshot"."issue_id" = "issue_id_p"

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

2035 AND "opinion"."fulfilled" = TRUE

2036 ),

2037 "minus1_unfulfilled_count" = (

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

2039 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"

2040 ON "opinion"."member_id" = "snapshot"."member_id"

2041 WHERE "opinion"."suggestion_id" = "suggestion_id_v"

2042 AND "snapshot"."issue_id" = "issue_id_p"

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

2044 AND "opinion"."fulfilled" = FALSE

2045 ),

2046 "minus1_fulfilled_count" = (

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

2048 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"

2049 ON "opinion"."member_id" = "snapshot"."member_id"

2050 WHERE "opinion"."suggestion_id" = "suggestion_id_v"

2051 AND "snapshot"."issue_id" = "issue_id_p"

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

2053 AND "opinion"."fulfilled" = TRUE

2054 ),

2055 "plus1_unfulfilled_count" = (

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

2057 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"

2058 ON "opinion"."member_id" = "snapshot"."member_id"

2059 WHERE "opinion"."suggestion_id" = "suggestion_id_v"

2060 AND "snapshot"."issue_id" = "issue_id_p"

2061 AND "opinion"."degree" = 1

2062 AND "opinion"."fulfilled" = FALSE

2063 ),

2064 "plus1_fulfilled_count" = (

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

2066 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"

2067 ON "opinion"."member_id" = "snapshot"."member_id"

2068 WHERE "opinion"."suggestion_id" = "suggestion_id_v"

2069 AND "snapshot"."issue_id" = "issue_id_p"

2070 AND "opinion"."degree" = 1

2071 AND "opinion"."fulfilled" = TRUE

2072 ),

2073 "plus2_unfulfilled_count" = (

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

2075 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"

2076 ON "opinion"."member_id" = "snapshot"."member_id"

2077 WHERE "opinion"."suggestion_id" = "suggestion_id_v"

2078 AND "snapshot"."issue_id" = "issue_id_p"

2079 AND "opinion"."degree" = 2

2080 AND "opinion"."fulfilled" = FALSE

2081 ),

2082 "plus2_fulfilled_count" = (

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

2084 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"

2085 ON "opinion"."member_id" = "snapshot"."member_id"

2086 WHERE "opinion"."suggestion_id" = "suggestion_id_v"

2087 AND "snapshot"."issue_id" = "issue_id_p"

2088 AND "opinion"."degree" = 2

2089 AND "opinion"."fulfilled" = TRUE

2090 )

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

2092 END LOOP;

2093 END LOOP;

2094 RETURN;

2095 END;

2096 $$;

2097

2098 COMMENT ON FUNCTION "create_snapshot"

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

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

2101

2102

2103 CREATE FUNCTION "set_snapshot_event"

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

2105 "event_p" "snapshot_event" )

2106 RETURNS VOID

2107 LANGUAGE 'plpgsql' VOLATILE AS $$

2108 BEGIN

2109 UPDATE "issue" SET "latest_snapshot_event" = "event_p"

2110 WHERE "id" = "issue_id_p";

2111 UPDATE "direct_population_snapshot" SET "event" = "event_p"

2112 WHERE "issue_id" = "issue_id_p" AND "event" = 'periodic';

2113 UPDATE "delegating_population_snapshot" SET "event" = "event_p"

2114 WHERE "issue_id" = "issue_id_p" AND "event" = 'periodic';

2115 UPDATE "direct_interest_snapshot" SET "event" = "event_p"

2116 WHERE "issue_id" = "issue_id_p" AND "event" = 'periodic';

2117 UPDATE "delegating_interest_snapshot" SET "event" = "event_p"

2118 WHERE "issue_id" = "issue_id_p" AND "event" = 'periodic';

2119 UPDATE "direct_supporter_snapshot" SET "event" = "event_p"

2120 WHERE "issue_id" = "issue_id_p" AND "event" = 'periodic';

2121 RETURN;

2122 END;

2123 $$;

2124

2125 COMMENT ON FUNCTION "set_snapshot_event"

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

2127 "snapshot_event" )

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

2129

2130

2131

2132 ---------------------

2133 -- Freezing issues --

2134 ---------------------

2135

2136 CREATE FUNCTION "freeze_after_snapshot"

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

2138 RETURNS VOID

2139 LANGUAGE 'plpgsql' VOLATILE AS $$

2140 DECLARE

2141 "issue_row" "issue"%ROWTYPE;

2142 "policy_row" "policy"%ROWTYPE;

2143 "initiative_row" "initiative"%ROWTYPE;

2144 BEGIN

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

2146 SELECT * INTO "policy_row"

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

2148 PERFORM "set_snapshot_event"("issue_id_p", 'start_of_voting');

2149 UPDATE "issue" SET

2150 "accepted" = coalesce("accepted", now()),

2151 "half_frozen" = coalesce("half_frozen", now()),

2152 "fully_frozen" = now()

2153 WHERE "id" = "issue_id_p";

2154 FOR "initiative_row" IN

2155 SELECT * FROM "initiative" WHERE "issue_id" = "issue_id_p"

2156 LOOP

2157 IF

2158 "initiative_row"."revoked" ISNULL AND

2159 "initiative_row"."satisfied_supporter_count" > 0 AND

2160 "initiative_row"."satisfied_supporter_count" *

2161 "policy_row"."initiative_quorum_den" >=

2162 "issue_row"."population" * "policy_row"."initiative_quorum_num"

2163 THEN

2164 UPDATE "initiative" SET "admitted" = TRUE

2165 WHERE "id" = "initiative_row"."id";

2166 ELSE

2167 UPDATE "initiative" SET "admitted" = FALSE

2168 WHERE "id" = "initiative_row"."id";

2169 END IF;

2170 END LOOP;

2171 IF NOT EXISTS (

2172 SELECT NULL FROM "initiative"

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

2174 ) THEN

2175 PERFORM "close_voting"("issue_id_p");

2176 END IF;

2177 RETURN;

2178 END;

2179 $$;

2180

2181 COMMENT ON FUNCTION "freeze_after_snapshot"

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

2183 IS 'This function freezes an issue (fully) and starts voting, but must only be called when "create_snapshot" was called in the same transaction.';

2184

2185

2186 CREATE FUNCTION "manual_freeze"("issue_id_p" "issue"."id"%TYPE)

2187 RETURNS VOID

2188 LANGUAGE 'plpgsql' VOLATILE AS $$

2189 DECLARE

2190 "issue_row" "issue"%ROWTYPE;

2191 BEGIN

2192 PERFORM "create_snapshot"("issue_id_p");

2193 PERFORM "freeze_after_snapshot"("issue_id_p");

2194 RETURN;

2195 END;

2196 $$;

2197

2198 COMMENT ON FUNCTION "freeze_after_snapshot"

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

2200 IS 'Freeze an issue manually (fully) and start voting';

2201

2202

2203

2204 -----------------------

2205 -- Counting of votes --

2206 -----------------------

2207

2208

2209 CREATE FUNCTION "weight_of_added_vote_delegations"

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

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

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

2213 RETURNS "direct_voter"."weight"%TYPE

2214 LANGUAGE 'plpgsql' VOLATILE AS $$

2215 DECLARE

2216 "issue_delegation_row" "issue_delegation"%ROWTYPE;

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

2218 "weight_v" INT4;

2219 "sub_weight_v" INT4;

2220 BEGIN

2221 "weight_v" := 0;

2222 FOR "issue_delegation_row" IN

2223 SELECT * FROM "issue_delegation"

2224 WHERE "trustee_id" = "member_id_p"

2225 AND "issue_id" = "issue_id_p"

2226 LOOP

2227 IF NOT EXISTS (

2228 SELECT NULL FROM "direct_voter"

2229 WHERE "member_id" = "issue_delegation_row"."truster_id"

2230 AND "issue_id" = "issue_id_p"

2231 ) AND NOT EXISTS (

2232 SELECT NULL FROM "delegating_voter"

2233 WHERE "member_id" = "issue_delegation_row"."truster_id"

2234 AND "issue_id" = "issue_id_p"

2235 ) THEN

2236 "delegate_member_ids_v" :=

2237 "member_id_p" || "delegate_member_ids_p";

2238 INSERT INTO "delegating_voter" (

2239 "issue_id",

2240 "member_id",

2241 "scope",

2242 "delegate_member_ids"

2243 ) VALUES (

2244 "issue_id_p",

2245 "issue_delegation_row"."truster_id",

2246 "issue_delegation_row"."scope",

2247 "delegate_member_ids_v"

2248 );

2249 "sub_weight_v" := 1 +

2250 "weight_of_added_vote_delegations"(

2251 "issue_id_p",

2252 "issue_delegation_row"."truster_id",

2253 "delegate_member_ids_v"

2254 );

2255 UPDATE "delegating_voter"

2256 SET "weight" = "sub_weight_v"

2257 WHERE "issue_id" = "issue_id_p"

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

2259 "weight_v" := "weight_v" + "sub_weight_v";

2260 END IF;

2261 END LOOP;

2262 RETURN "weight_v";

2263 END;

2264 $$;

2265

2266 COMMENT ON FUNCTION "weight_of_added_vote_delegations"

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

2268 "member"."id"%TYPE,

2269 "delegating_voter"."delegate_member_ids"%TYPE )

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

2271

2272

2273 CREATE FUNCTION "add_vote_delegations"

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

2275 RETURNS VOID

2276 LANGUAGE 'plpgsql' VOLATILE AS $$

2277 DECLARE

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

2279 BEGIN

2280 FOR "member_id_v" IN

2281 SELECT "member_id" FROM "direct_voter"

2282 WHERE "issue_id" = "issue_id_p"

2283 LOOP

2284 UPDATE "direct_voter" SET

2285 "weight" = "weight" + "weight_of_added_vote_delegations"(

2286 "issue_id_p",

2287 "member_id_v",

2288 '{}'

2289 )

2290 WHERE "member_id" = "member_id_v"

2291 AND "issue_id" = "issue_id_p";

2292 END LOOP;

2293 RETURN;

2294 END;

2295 $$;

2296

2297 COMMENT ON FUNCTION "add_vote_delegations"

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

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

2300

2301

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

2303 RETURNS VOID

2304 LANGUAGE 'plpgsql' VOLATILE AS $$

2305 DECLARE

2306 "issue_row" "issue"%ROWTYPE;

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

2308 BEGIN

2309 PERFORM "global_lock"();

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

2311 DELETE FROM "delegating_voter"

2312 WHERE "issue_id" = "issue_id_p";

2313 DELETE FROM "direct_voter"

2314 WHERE "issue_id" = "issue_id_p"

2315 AND "autoreject" = TRUE;

2316 DELETE FROM "direct_voter" USING "member"

2317 WHERE "direct_voter"."member_id" = "member"."id"

2318 AND "direct_voter"."issue_id" = "issue_id_p"

2319 AND "member"."active" = FALSE;

2320 UPDATE "direct_voter" SET "weight" = 1

2321 WHERE "issue_id" = "issue_id_p";

2322 PERFORM "add_vote_delegations"("issue_id_p");

2323 FOR "member_id_v" IN

2324 SELECT "interest"."member_id"

2325 FROM "interest"

2326 LEFT JOIN "direct_voter"

2327 ON "interest"."member_id" = "direct_voter"."member_id"

2328 AND "interest"."issue_id" = "direct_voter"."issue_id"

2329 LEFT JOIN "delegating_voter"

2330 ON "interest"."member_id" = "delegating_voter"."member_id"

2331 AND "interest"."issue_id" = "delegating_voter"."issue_id"

2332 WHERE "interest"."issue_id" = "issue_id_p"

2333 AND "interest"."autoreject" = TRUE

2334 AND "direct_voter"."member_id" ISNULL

2335 AND "delegating_voter"."member_id" ISNULL

2336 UNION SELECT "membership"."member_id"

2337 FROM "membership"

2338 LEFT JOIN "interest"

2339 ON "membership"."member_id" = "interest"."member_id"

2340 AND "interest"."issue_id" = "issue_id_p"

2341 LEFT JOIN "direct_voter"

2342 ON "membership"."member_id" = "direct_voter"."member_id"

2343 AND "direct_voter"."issue_id" = "issue_id_p"

2344 LEFT JOIN "delegating_voter"

2345 ON "membership"."member_id" = "delegating_voter"."member_id"

2346 AND "delegating_voter"."issue_id" = "issue_id_p"

2347 WHERE "membership"."area_id" = "issue_row"."area_id"

2348 AND "membership"."autoreject" = TRUE

2349 AND "interest"."autoreject" ISNULL

2350 AND "direct_voter"."member_id" ISNULL

2351 AND "delegating_voter"."member_id" ISNULL

2352 LOOP

2353 INSERT INTO "direct_voter"

2354 ("member_id", "issue_id", "weight", "autoreject") VALUES

2355 ("member_id_v", "issue_id_p", 1, TRUE);

2356 INSERT INTO "vote" (

2357 "member_id",

2358 "issue_id",

2359 "initiative_id",

2360 "grade"

2361 ) SELECT

2362 "member_id_v" AS "member_id",

2363 "issue_id_p" AS "issue_id",

2364 "id" AS "initiative_id",

2365 -1 AS "grade"

2366 FROM "initiative" WHERE "issue_id" = "issue_id_p";

2367 END LOOP;

2368 PERFORM "add_vote_delegations"("issue_id_p");

2369 UPDATE "issue" SET

2370 "voter_count" = (

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

2372 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"

2373 )

2374 WHERE "id" = "issue_id_p";

2375 UPDATE "initiative" SET

2376 "positive_votes" = "vote_counts"."positive_votes",

2377 "negative_votes" = "vote_counts"."negative_votes",

2378 "agreed" = CASE WHEN "majority_strict" THEN

2379 "vote_counts"."positive_votes" * "majority_den" >

2380 "majority_num" *

2381 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")

2382 ELSE

2383 "vote_counts"."positive_votes" * "majority_den" >=

2384 "majority_num" *

2385 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")

2386 END

2387 FROM

2388 ( SELECT

2389 "initiative"."id" AS "initiative_id",

2390 coalesce(

2391 sum(

2392 CASE WHEN "grade" > 0 THEN "direct_voter"."weight" ELSE 0 END

2393 ),

2394 0

2395 ) AS "positive_votes",

2396 coalesce(

2397 sum(

2398 CASE WHEN "grade" < 0 THEN "direct_voter"."weight" ELSE 0 END

2399 ),

2400 0

2401 ) AS "negative_votes"

2402 FROM "initiative"

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

2404 JOIN "policy" ON "issue"."policy_id" = "policy"."id"

2405 LEFT JOIN "direct_voter"

2406 ON "direct_voter"."issue_id" = "initiative"."issue_id"

2407 LEFT JOIN "vote"

2408 ON "vote"."initiative_id" = "initiative"."id"

2409 AND "vote"."member_id" = "direct_voter"."member_id"

2410 WHERE "initiative"."issue_id" = "issue_id_p"

2411 AND "initiative"."admitted"

2412 GROUP BY "initiative"."id"

2413 ) AS "vote_counts",

2414 "issue",

2415 "policy"

2416 WHERE "vote_counts"."initiative_id" = "initiative"."id"

2417 AND "issue"."id" = "initiative"."issue_id"

2418 AND "policy"."id" = "issue"."policy_id";

2419 UPDATE "issue" SET "closed" = now() WHERE "id" = "issue_id_p";

2420 END;

2421 $$;

2422

2423 COMMENT ON FUNCTION "close_voting"

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

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

2426

2427

2428 CREATE FUNCTION "init_array"("dim_p" INTEGER)

2429 RETURNS INT4[]

2430 LANGUAGE 'plpgsql' IMMUTABLE AS $$

2431 DECLARE

2432 "i" INTEGER;

2433 "ary_text_v" TEXT;

2434 BEGIN

2435 IF "dim_p" >= 1 THEN

2436 "ary_text_v" := '{NULL';

2437 "i" := "dim_p";

2438 LOOP

2439 "i" := "i" - 1;

2440 EXIT WHEN "i" = 0;

2441 "ary_text_v" := "ary_text_v" || ',NULL';

2442 END LOOP;

2443 "ary_text_v" := "ary_text_v" || '}';

2444 RETURN "ary_text_v"::INT4[][];

2445 ELSE

2446 RAISE EXCEPTION 'Dimension needs to be at least 1.';

2447 END IF;

2448 END;

2449 $$;

2450

2451 COMMENT ON FUNCTION "init_array"(INTEGER) IS 'Needed for PostgreSQL < 8.4, due to missing "array_fill" function';

2452

2453

2454 CREATE FUNCTION "init_square_matrix"("dim_p" INTEGER)

2455 RETURNS INT4[][]

2456 LANGUAGE 'plpgsql' IMMUTABLE AS $$

2457 DECLARE

2458 "i" INTEGER;

2459 "row_text_v" TEXT;

2460 "ary_text_v" TEXT;

2461 BEGIN

2462 IF "dim_p" >= 1 THEN

2463 "row_text_v" := '{NULL';

2464 "i" := "dim_p";

2465 LOOP

2466 "i" := "i" - 1;

2467 EXIT WHEN "i" = 0;

2468 "row_text_v" := "row_text_v" || ',NULL';

2469 END LOOP;

2470 "row_text_v" := "row_text_v" || '}';

2471 "ary_text_v" := '{' || "row_text_v";

2472 "i" := "dim_p";

2473 LOOP

2474 "i" := "i" - 1;

2475 EXIT WHEN "i" = 0;

2476 "ary_text_v" := "ary_text_v" || ',' || "row_text_v";

2477 END LOOP;

2478 "ary_text_v" := "ary_text_v" || '}';

2479 RETURN "ary_text_v"::INT4[][];

2480 ELSE

2481 RAISE EXCEPTION 'Dimension needs to be at least 1.';

2482 END IF;

2483 END;

2484 $$;

2485

2486 COMMENT ON FUNCTION "init_square_matrix"(INTEGER) IS 'Needed for PostgreSQL < 8.4, due to missing "array_fill" function';

2487

2488

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

2490 RETURNS VOID

2491 LANGUAGE 'plpgsql' VOLATILE AS $$

2492 DECLARE

2493 "dimension_v" INTEGER;

2494 "matrix" INT4[][];

2495 "i" INTEGER;

2496 "j" INTEGER;

2497 "k" INTEGER;

2498 "battle_row" "battle"%ROWTYPE;

2499 "rank_ary" INT4[];

2500 "rank_v" INT4;

2501 "done_v" INTEGER;

2502 "winners_ary" INTEGER[];

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

2504 BEGIN

2505 PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE;

2506 -- Prepare matrix for Schulze-Method:

2507 SELECT count(1) INTO "dimension_v" FROM "initiative"

2508 WHERE "issue_id" = "issue_id_p" AND "agreed";

2509 IF "dimension_v" = 1 THEN

2510 UPDATE "initiative" SET "rank" = 1

2511 WHERE "issue_id" = "issue_id_p" AND "agreed";

2512 ELSIF "dimension_v" > 1 THEN

2513 "matrix" := "init_square_matrix"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4)

2514 "i" := 1;

2515 "j" := 2;

2516 -- Fill matrix with data from "battle" view

2517 FOR "battle_row" IN

2518 SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p"

2519 ORDER BY "winning_initiative_id", "losing_initiative_id"

2520 LOOP

2521 "matrix"["i"]["j"] := "battle_row"."count";

2522 IF "j" = "dimension_v" THEN

2523 "i" := "i" + 1;

2524 "j" := 1;

2525 ELSE

2526 "j" := "j" + 1;

2527 IF "j" = "i" THEN

2528 "j" := "j" + 1;

2529 END IF;

2530 END IF;

2531 END LOOP;

2532 IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN

2533 RAISE EXCEPTION 'Wrong battle count (should not happen)';

2534 END IF;

2535 -- Delete losers from matrix:

2536 "i" := 1;

2537 LOOP

2538 "j" := "i" + 1;

2539 LOOP

2540 IF "i" != "j" THEN

2541 IF "matrix"["i"]["j"] < "matrix"["j"]["i"] THEN

2542 "matrix"["i"]["j"] := 0;

2543 ELSIF matrix[j][i] < matrix[i][j] THEN

2544 "matrix"["j"]["i"] := 0;

2545 ELSE

2546 "matrix"["i"]["j"] := 0;

2547 "matrix"["j"]["i"] := 0;

2548 END IF;

2549 END IF;

2550 EXIT WHEN "j" = "dimension_v";

2551 "j" := "j" + 1;

2552 END LOOP;

2553 EXIT WHEN "i" = "dimension_v" - 1;

2554 "i" := "i" + 1;

2555 END LOOP;

2556 -- Find best paths:

2557 "i" := 1;

2558 LOOP

2559 "j" := 1;

2560 LOOP

2561 IF "i" != "j" THEN

2562 "k" := 1;

2563 LOOP

2564 IF "i" != "k" AND "j" != "k" THEN

2565 IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN

2566 IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN

2567 "matrix"["j"]["k"] := "matrix"["j"]["i"];

2568 END IF;

2569 ELSE

2570 IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN

2571 "matrix"["j"]["k"] := "matrix"["i"]["k"];

2572 END IF;

2573 END IF;

2574 END IF;

2575 EXIT WHEN "k" = "dimension_v";

2576 "k" := "k" + 1;

2577 END LOOP;

2578 END IF;

2579 EXIT WHEN "j" = "dimension_v";

2580 "j" := "j" + 1;

2581 END LOOP;

2582 EXIT WHEN "i" = "dimension_v";

2583 "i" := "i" + 1;

2584 END LOOP;

2585 -- Determine order of winners:

2586 "rank_ary" := "init_array"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4)

2587 "rank_v" := 1;

2588 "done_v" := 0;

2589 LOOP

2590 "winners_ary" := '{}';

2591 "i" := 1;

2592 LOOP

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

2594 "j" := 1;

2595 LOOP

2596 IF

2597 "i" != "j" AND

2598 "rank_ary"["j"] ISNULL AND

2599 "matrix"["j"]["i"] > "matrix"["i"]["j"]

2600 THEN

2601 -- someone else is better

2602 EXIT;

2603 END IF;

2604 IF "j" = "dimension_v" THEN

2605 -- noone is better

2606 "winners_ary" := "winners_ary" || "i";

2607 EXIT;

2608 END IF;

2609 "j" := "j" + 1;

2610 END LOOP;

2611 END IF;

2612 EXIT WHEN "i" = "dimension_v";

2613 "i" := "i" + 1;

2614 END LOOP;

2615 "i" := 1;

2616 LOOP

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

2618 "done_v" := "done_v" + 1;

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

2620 "i" := "i" + 1;

2621 END LOOP;

2622 EXIT WHEN "done_v" = "dimension_v";

2623 "rank_v" := "rank_v" + 1;

2624 END LOOP;

2625 -- write preliminary ranks:

2626 "i" := 1;

2627 FOR "initiative_id_v" IN

2628 SELECT "id" FROM "initiative"

2629 WHERE "issue_id" = "issue_id_p" AND "agreed"

2630 ORDER BY "id"

2631 LOOP

2632 UPDATE "initiative" SET "rank" = "rank_ary"["i"]

2633 WHERE "id" = "initiative_id_v";

2634 "i" := "i" + 1;

2635 END LOOP;

2636 IF "i" != "dimension_v" + 1 THEN

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

2638 END IF;

2639 -- straighten ranks (start counting with 1, no equal ranks):

2640 "rank_v" := 1;

2641 FOR "initiative_id_v" IN

2642 SELECT "id" FROM "initiative"

2643 WHERE "issue_id" = "issue_id_p" AND "rank" NOTNULL

2644 ORDER BY

2645 "rank",

2646 "vote_ratio"("positive_votes", "negative_votes") DESC,

2647 "id"

2648 LOOP

2649 UPDATE "initiative" SET "rank" = "rank_v"

2650 WHERE "id" = "initiative_id_v";

2651 "rank_v" := "rank_v" + 1;

2652 END LOOP;

2653 END IF;

2654 -- mark issue as finished

2655 UPDATE "issue" SET "ranks_available" = TRUE

2656 WHERE "id" = "issue_id_p";

2657 RETURN;

2658 END;

2659 $$;

2660

2661 COMMENT ON FUNCTION "calculate_ranks"

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

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

2664

2665

2666

2667 -----------------------------

2668 -- Automatic state changes --

2669 -----------------------------

2670

2671

2672 CREATE FUNCTION "check_issue"

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

2674 RETURNS VOID

2675 LANGUAGE 'plpgsql' VOLATILE AS $$

2676 DECLARE

2677 "issue_row" "issue"%ROWTYPE;

2678 "policy_row" "policy"%ROWTYPE;

2679 "voting_requested_v" BOOLEAN;

2680 BEGIN

2681 PERFORM "global_lock"();

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

2683 IF "issue_row"."closed" ISNULL THEN

2684 SELECT * INTO "policy_row" FROM "policy"

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

2686 IF "issue_row"."fully_frozen" ISNULL THEN

2687 PERFORM "create_snapshot"("issue_id_p");

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

2689 END IF;

2690 IF "issue_row"."accepted" ISNULL THEN

2691 IF EXISTS (

2692 SELECT NULL FROM "initiative"

2693 WHERE "issue_id" = "issue_id_p"

2694 AND "supporter_count" > 0

2695 AND "supporter_count" * "policy_row"."issue_quorum_den"

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

2697 ) THEN

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

2699 "issue_row"."accepted" = now(); -- NOTE: "issue_row" used later

2700 UPDATE "issue" SET "accepted" = "issue_row"."accepted"

2701 WHERE "id" = "issue_row"."id";

2702 ELSIF

2703 now() >= "issue_row"."created" + "policy_row"."admission_time"

2704 THEN

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

2706 UPDATE "issue" SET "closed" = now()

2707 WHERE "id" = "issue_row"."id";

2708 END IF;

2709 END IF;

2710 IF

2711 "issue_row"."accepted" NOTNULL AND

2712 "issue_row"."half_frozen" ISNULL

2713 THEN

2714 SELECT

2715 CASE

2716 WHEN "vote_now" * 2 > "issue_row"."population" THEN

2717 TRUE

2718 WHEN "vote_later" * 2 > "issue_row"."population" THEN

2719 FALSE

2720 ELSE NULL

2721 END

2722 INTO "voting_requested_v"

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

2724 IF

2725 "voting_requested_v" OR (

2726 "voting_requested_v" ISNULL AND

2727 now() >= "issue_row"."accepted" + "policy_row"."discussion_time"

2728 )

2729 THEN

2730 "issue_row"."half_frozen" = now(); -- NOTE: "issue_row" used later

2731 UPDATE "issue" SET "half_frozen" = "issue_row"."half_frozen"

2732 WHERE "id" = "issue_row"."id";

2733 END IF;

2734 END IF;

2735 IF

2736 "issue_row"."half_frozen" NOTNULL AND

2737 "issue_row"."fully_frozen" ISNULL AND

2738 now() >= "issue_row"."half_frozen" + "policy_row"."verification_time"

2739 THEN

2740 PERFORM "freeze_after_snapshot"("issue_id_p");

2741 -- "issue" might change, thus "issue_row" has to be updated below

2742 END IF;

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

2744 IF

2745 "issue_row"."closed" ISNULL AND

2746 "issue_row"."fully_frozen" NOTNULL AND

2747 now() >= "issue_row"."fully_frozen" + "policy_row"."voting_time"

2748 THEN

2749 PERFORM "close_voting"("issue_id_p");

2750 END IF;

2751 END IF;

2752 RETURN;

2753 END;

2754 $$;

2755

2756 COMMENT ON FUNCTION "check_issue"

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

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

2759

2760

2761 CREATE FUNCTION "check_everything"()

2762 RETURNS VOID

2763 LANGUAGE 'plpgsql' VOLATILE AS $$

2764 DECLARE

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

2766 BEGIN

2767 DELETE FROM "expired_session";

2768 PERFORM "calculate_member_counts"();

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

2770 PERFORM "check_issue"("issue_id_v");

2771 END LOOP;

2772 FOR "issue_id_v" IN SELECT "id" FROM "issue_with_ranks_missing" LOOP

2773 PERFORM "calculate_ranks"("issue_id_v");

2774 END LOOP;

2775 RETURN;

2776 END;

2777 $$;

2778

2779 COMMENT ON FUNCTION "check_everything"() IS 'Perform "check_issue" for every open issue, and if possible, automatically calculate ranks. Use this function only for development and debugging purposes, as long transactions with exclusive locking may result.';

2780

2781

2782

2783 ------------------------------

2784 -- Deletion of private data --

2785 ------------------------------

2786

2787

2788 CREATE FUNCTION "delete_private_data"()

2789 RETURNS VOID

2790 LANGUAGE 'plpgsql' VOLATILE AS $$

2791 DECLARE

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

2793 BEGIN

2794 UPDATE "member" SET

2795 "login" = 'login' || "id"::text,

2796 "password" = NULL,

2797 "notify_email" = NULL,

2798 "notify_email_unconfirmed" = NULL,

2799 "notify_email_secret" = NULL,

2800 "notify_email_secret_expiry" = NULL,

2801 "password_reset_secret" = NULL,

2802 "password_reset_secret_expiry" = NULL,

2803 "organizational_unit" = NULL,

2804 "internal_posts" = NULL,

2805 "realname" = NULL,

2806 "birthday" = NULL,

2807 "address" = NULL,

2808 "email" = NULL,

2809 "xmpp_address" = NULL,

2810 "website" = NULL,

2811 "phone" = NULL,

2812 "mobile_phone" = NULL,

2813 "profession" = NULL,

2814 "external_memberships" = NULL,

2815 "external_posts" = NULL,

2816 "statement" = NULL;

2817 -- "text_search_data" is updated by triggers

2818 DELETE FROM "session";

2819 DELETE FROM "invite_code";

2820 DELETE FROM "contact";

2821 DELETE FROM "setting";

2822 DELETE FROM "member_image";

2823 DELETE FROM "direct_voter" USING "issue"

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

2825 AND "issue"."closed" ISNULL;

2826 RETURN;

2827 END;

2828 $$;

2829

2830 COMMENT ON FUNCTION "delete_private_data"() IS '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.';

2831

2832

2833

2834 COMMIT;