liquid_feedback_core: 015825e225ca core.sql

liquid_feedback_core

view core.sql @ 11:015825e225ca

Version beta12

Changed function delete_private_data() to delete more data, including tables setting and member_image, for better data protection

Bugfix related to linux-sh in shell script lf_export

New fields password_reset_secret and password_reset_secret_expiry in member table

UNIQUE constraint for notify_email_secret and password_reset_secret fields in member table

Database constraint forbids adding yourself as a contact

author	jbe
date	Sat Jan 02 12:00:00 2010 +0100 (2010-01-02)
parents	effdd7a04ea7
children	a67c1cd4facf

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 ('beta12', 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 "login" TEXT NOT NULL UNIQUE,

59 "password" TEXT,

60 "active" BOOLEAN NOT NULL DEFAULT TRUE,

61 "admin" BOOLEAN NOT NULL DEFAULT FALSE,

62 "notify_email" TEXT,

63 "notify_email_unconfirmed" TEXT,

64 "notify_email_secret" TEXT UNIQUE,

65 "notify_email_secret_expiry" TIMESTAMPTZ,

66 "password_reset_secret" TEXT UNIQUE,

67 "password_reset_secret_expiry" TIMESTAMPTZ,

68 "name" TEXT NOT NULL UNIQUE,

69 "identification" TEXT UNIQUE,

70 "organizational_unit" TEXT,

71 "internal_posts" TEXT,

72 "realname" TEXT,

73 "birthday" DATE,

74 "address" TEXT,

75 "email" TEXT,

76 "xmpp_address" TEXT,

77 "website" TEXT,

78 "phone" TEXT,

79 "mobile_phone" TEXT,

80 "profession" TEXT,

81 "external_memberships" TEXT,

82 "external_posts" TEXT,

83 "statement" TEXT,

84 "text_search_data" TSVECTOR );

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

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

87 CREATE TRIGGER "update_text_search_data"

88 BEFORE INSERT OR UPDATE ON "member"

89 FOR EACH ROW EXECUTE PROCEDURE

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

91 "name", "identification", "organizational_unit", "internal_posts",

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

113

114

115 CREATE TABLE "invite_code" (

116 "code" TEXT PRIMARY KEY,

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

118 "used" TIMESTAMPTZ,

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

120 "comment" TEXT,

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

122

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

124

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

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

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

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

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

130

131

132 CREATE TABLE "setting" (

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

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

135 "key" TEXT NOT NULL,

136 "value" TEXT NOT NULL );

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

138

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

140

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

142

143

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

145

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

147

148

149 CREATE TABLE "member_image" (

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

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

152 "image_type" "member_image_type",

153 "scaled" BOOLEAN,

154 "content_type" TEXT,

155 "data" BYTEA NOT NULL );

156

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

158

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

160

161

162 CREATE TABLE "member_count" (

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

164 "total_count" INT4 NOT NULL );

165

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

167

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

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

170

171

172 CREATE TABLE "contact" (

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

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

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

176 "public" BOOLEAN NOT NULL DEFAULT FALSE,

177 CONSTRAINT "cant_save_yourself_as_contact"

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

179

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

181

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

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

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

185

186

187 CREATE TABLE "session" (

188 "ident" TEXT PRIMARY KEY,

189 "additional_secret" TEXT,

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

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

192 "lang" TEXT );

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

194

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

196

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

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

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

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

201

202

203 CREATE TABLE "policy" (

204 "id" SERIAL4 PRIMARY KEY,

205 "index" INT4 NOT NULL,

206 "active" BOOLEAN NOT NULL DEFAULT TRUE,

207 "name" TEXT NOT NULL UNIQUE,

208 "description" TEXT NOT NULL DEFAULT '',

209 "admission_time" INTERVAL NOT NULL,

210 "discussion_time" INTERVAL NOT NULL,

211 "verification_time" INTERVAL NOT NULL,

212 "voting_time" INTERVAL NOT NULL,

213 "issue_quorum_num" INT4 NOT NULL,

214 "issue_quorum_den" INT4 NOT NULL,

215 "initiative_quorum_num" INT4 NOT NULL,

216 "initiative_quorum_den" INT4 NOT NULL,

217 "majority_num" INT4 NOT NULL DEFAULT 1,

218 "majority_den" INT4 NOT NULL DEFAULT 2,

219 "majority_strict" BOOLEAN NOT NULL DEFAULT TRUE );

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

221

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

223

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

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

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

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

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

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

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

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

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

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

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

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

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

237

238

239 CREATE TABLE "area" (

240 "id" SERIAL4 PRIMARY KEY,

241 "active" BOOLEAN NOT NULL DEFAULT TRUE,

242 "name" TEXT NOT NULL,

243 "description" TEXT NOT NULL DEFAULT '',

244 "direct_member_count" INT4,

245 "member_weight" INT4,

246 "autoreject_weight" INT4,

247 "text_search_data" TSVECTOR );

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

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

250 CREATE TRIGGER "update_text_search_data"

251 BEFORE INSERT OR UPDATE ON "area"

252 FOR EACH ROW EXECUTE PROCEDURE

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

254 "name", "description" );

255

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

257

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

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

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

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

262

263

264 CREATE TABLE "allowed_policy" (

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

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

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

268 "default_policy" BOOLEAN NOT NULL DEFAULT FALSE );

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

270

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

272

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

274

275

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

277

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

279

280

281 CREATE TABLE "issue" (

282 "id" SERIAL4 PRIMARY KEY,

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

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

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

286 "accepted" TIMESTAMPTZ,

287 "half_frozen" TIMESTAMPTZ,

288 "fully_frozen" TIMESTAMPTZ,

289 "closed" TIMESTAMPTZ,

290 "ranks_available" BOOLEAN NOT NULL DEFAULT FALSE,

291 "snapshot" TIMESTAMPTZ,

292 "latest_snapshot_event" "snapshot_event",

293 "population" INT4,

294 "vote_now" INT4,

295 "vote_later" INT4,

296 "voter_count" INT4,

297 CONSTRAINT "valid_state" CHECK (

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

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

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

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

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

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

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

305 CONSTRAINT "state_change_order" CHECK (

306 "created" <= "accepted" AND

307 "accepted" <= "half_frozen" AND

308 "half_frozen" <= "fully_frozen" AND

309 "fully_frozen" <= "closed" ),

310 CONSTRAINT "last_snapshot_on_full_freeze"

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

312 CONSTRAINT "freeze_requires_snapshot"

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

314 CONSTRAINT "set_both_or_none_of_snapshot_and_latest_snapshot_event"

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

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

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

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

319

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

321

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

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

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

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

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

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

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

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

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

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

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

333

334

335 CREATE TABLE "initiative" (

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

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

338 "id" SERIAL4 PRIMARY KEY,

339 "name" TEXT NOT NULL,

340 "discussion_url" TEXT,

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

342 "revoked" TIMESTAMPTZ,

343 "admitted" BOOLEAN,

344 "supporter_count" INT4,

345 "informed_supporter_count" INT4,

346 "satisfied_supporter_count" INT4,

347 "satisfied_informed_supporter_count" INT4,

348 "positive_votes" INT4,

349 "negative_votes" INT4,

350 "agreed" BOOLEAN,

351 "rank" INT4,

352 "text_search_data" TSVECTOR,

353 CONSTRAINT "revoked_initiatives_cant_be_admitted"

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

355 CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results"

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

357 CONSTRAINT "all_or_none_of_positive_votes_negative_votes_and_agreed_must_be_null"

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

359 CONSTRAINT "non_agreed_initiatives_cant_get_a_rank"

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

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

362 CREATE TRIGGER "update_text_search_data"

363 BEFORE INSERT OR UPDATE ON "initiative"

364 FOR EACH ROW EXECUTE PROCEDURE

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

366 "name", "discussion_url");

367

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

369

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

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

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

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

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

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

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

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

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

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

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

381

382

383 CREATE TABLE "draft" (

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

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

386 "id" SERIAL8 PRIMARY KEY,

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

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

389 "formatting_engine" TEXT,

390 "content" TEXT NOT NULL,

391 "text_search_data" TSVECTOR );

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

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

394 CREATE TRIGGER "update_text_search_data"

395 BEFORE INSERT OR UPDATE ON "draft"

396 FOR EACH ROW EXECUTE PROCEDURE

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

398

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

400

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

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

403

404

405 CREATE TABLE "suggestion" (

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

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

408 "id" SERIAL8 PRIMARY KEY,

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

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

411 "name" TEXT NOT NULL,

412 "description" TEXT NOT NULL DEFAULT '',

413 "text_search_data" TSVECTOR,

414 "minus2_unfulfilled_count" INT4,

415 "minus2_fulfilled_count" INT4,

416 "minus1_unfulfilled_count" INT4,

417 "minus1_fulfilled_count" INT4,

418 "plus1_unfulfilled_count" INT4,

419 "plus1_fulfilled_count" INT4,

420 "plus2_unfulfilled_count" INT4,

421 "plus2_fulfilled_count" INT4 );

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

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

424 CREATE TRIGGER "update_text_search_data"

425 BEFORE INSERT OR UPDATE ON "suggestion"

426 FOR EACH ROW EXECUTE PROCEDURE

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

428 "name", "description");

429

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

431

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

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

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

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

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

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

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

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

440

441

442 CREATE TABLE "membership" (

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

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

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

446 "autoreject" BOOLEAN NOT NULL DEFAULT FALSE );

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

448

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

450

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

452

453

454 CREATE TABLE "interest" (

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

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

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

458 "autoreject" BOOLEAN NOT NULL,

459 "voting_requested" BOOLEAN );

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

461

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

463

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

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

466

467

468 CREATE TABLE "initiator" (

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

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

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

472 "accepted" BOOLEAN NOT NULL DEFAULT TRUE );

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

474

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

476

477 COMMENT ON COLUMN "initiator"."accepted" IS 'If "accepted" = FALSE, then the member was invited to be a co-initiator, but has not answered yet.';

478

479

480 CREATE TABLE "supporter" (

481 "issue_id" INT4 NOT NULL,

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

483 "initiative_id" INT4,

484 "member_id" INT4,

485 "draft_id" INT8 NOT NULL,

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

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

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

489

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

491

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

493

494

495 CREATE TABLE "opinion" (

496 "initiative_id" INT4 NOT NULL,

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

498 "suggestion_id" INT8,

499 "member_id" INT4,

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

501 "fulfilled" BOOLEAN NOT NULL DEFAULT FALSE,

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

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

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

505

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

507

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

509

510

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

512

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

514

515

516 CREATE TABLE "delegation" (

517 "id" SERIAL8 PRIMARY KEY,

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

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

520 "scope" "delegation_scope" NOT NULL,

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

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

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

524 CONSTRAINT "area_id_and_issue_id_set_according_to_scope" CHECK (

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

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

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

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

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

530 CREATE UNIQUE INDEX "delegation_global_truster_id_trustee_id_unique_idx"

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

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

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

534

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

536

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

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

539

540

541 CREATE TABLE "direct_population_snapshot" (

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

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

544 "event" "snapshot_event",

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

546 "weight" INT4,

547 "interest_exists" BOOLEAN NOT NULL );

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

549

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

551

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

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

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

555

556

557 CREATE TABLE "delegating_population_snapshot" (

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

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

560 "event" "snapshot_event",

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

562 "weight" INT4,

563 "scope" "delegation_scope" NOT NULL,

564 "delegate_member_ids" INT4[] NOT NULL );

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

566

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

568

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

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

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

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

573

574

575 CREATE TABLE "direct_interest_snapshot" (

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

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

578 "event" "snapshot_event",

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

580 "weight" INT4,

581 "voting_requested" BOOLEAN );

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

583

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

585

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

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

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

589

590

591 CREATE TABLE "delegating_interest_snapshot" (

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

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

594 "event" "snapshot_event",

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

596 "weight" INT4,

597 "scope" "delegation_scope" NOT NULL,

598 "delegate_member_ids" INT4[] NOT NULL );

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

600

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

602

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

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

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

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

607

608

609 CREATE TABLE "direct_supporter_snapshot" (

610 "issue_id" INT4 NOT NULL,

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

612 "initiative_id" INT4,

613 "event" "snapshot_event",

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

615 "informed" BOOLEAN NOT NULL,

616 "satisfied" BOOLEAN NOT NULL,

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

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

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

620

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

622

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

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

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

626

627

628 CREATE TABLE "direct_voter" (

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

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

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

632 "weight" INT4,

633 "autoreject" BOOLEAN NOT NULL DEFAULT FALSE );

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

635

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

637

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

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

640

641

642 CREATE TABLE "delegating_voter" (

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

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

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

646 "weight" INT4,

647 "scope" "delegation_scope" NOT NULL,

648 "delegate_member_ids" INT4[] NOT NULL );

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

650

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

652

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

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

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

656

657

658 CREATE TABLE "vote" (

659 "issue_id" INT4 NOT NULL,

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

661 "initiative_id" INT4,

662 "member_id" INT4,

663 "grade" INT4,

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

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

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

667

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

669

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

671

672

673 CREATE TABLE "contingent" (

674 "time_frame" INTERVAL PRIMARY KEY,

675 "text_entry_limit" INT4,

676 "initiative_limit" INT4 );

677

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

679

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

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

682

683

684

685 ----------------------------

686 -- Additional constraints --

687 ----------------------------

688

689

690 CREATE FUNCTION "issue_requires_first_initiative_trigger"()

691 RETURNS TRIGGER

692 LANGUAGE 'plpgsql' VOLATILE AS $$

693 BEGIN

694 IF NOT EXISTS (

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

696 ) THEN

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

698 -- ERRCODE = 'integrity_constraint_violation',

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

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

701 END IF;

702 RETURN NULL;

703 END;

704 $$;

705

706 CREATE CONSTRAINT TRIGGER "issue_requires_first_initiative"

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

708 FOR EACH ROW EXECUTE PROCEDURE

709 "issue_requires_first_initiative_trigger"();

710

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

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

713

714

715 CREATE FUNCTION "last_initiative_deletes_issue_trigger"()

716 RETURNS TRIGGER

717 LANGUAGE 'plpgsql' VOLATILE AS $$

718 DECLARE

719 "reference_lost" BOOLEAN;

720 BEGIN

721 IF TG_OP = 'DELETE' THEN

722 "reference_lost" := TRUE;

723 ELSE

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

725 END IF;

726 IF

727 "reference_lost" AND NOT EXISTS (

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

729 )

730 THEN

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

732 END IF;

733 RETURN NULL;

734 END;

735 $$;

736

737 CREATE CONSTRAINT TRIGGER "last_initiative_deletes_issue"

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

739 FOR EACH ROW EXECUTE PROCEDURE

740 "last_initiative_deletes_issue_trigger"();

741

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

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

744

745

746 CREATE FUNCTION "initiative_requires_first_draft_trigger"()

747 RETURNS TRIGGER

748 LANGUAGE 'plpgsql' VOLATILE AS $$

749 BEGIN

750 IF NOT EXISTS (

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

752 ) THEN

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

754 -- ERRCODE = 'integrity_constraint_violation',

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

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

757 END IF;

758 RETURN NULL;

759 END;

760 $$;

761

762 CREATE CONSTRAINT TRIGGER "initiative_requires_first_draft"

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

764 FOR EACH ROW EXECUTE PROCEDURE

765 "initiative_requires_first_draft_trigger"();

766

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

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

769

770

771 CREATE FUNCTION "last_draft_deletes_initiative_trigger"()

772 RETURNS TRIGGER

773 LANGUAGE 'plpgsql' VOLATILE AS $$

774 DECLARE

775 "reference_lost" BOOLEAN;

776 BEGIN

777 IF TG_OP = 'DELETE' THEN

778 "reference_lost" := TRUE;

779 ELSE

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

781 END IF;

782 IF

783 "reference_lost" AND NOT EXISTS (

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

785 )

786 THEN

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

788 END IF;

789 RETURN NULL;

790 END;

791 $$;

792

793 CREATE CONSTRAINT TRIGGER "last_draft_deletes_initiative"

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

795 FOR EACH ROW EXECUTE PROCEDURE

796 "last_draft_deletes_initiative_trigger"();

797

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

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

800

801

802 CREATE FUNCTION "suggestion_requires_first_opinion_trigger"()

803 RETURNS TRIGGER

804 LANGUAGE 'plpgsql' VOLATILE AS $$

805 BEGIN

806 IF NOT EXISTS (

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

808 ) THEN

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

810 END IF;

811 RETURN NULL;

812 END;

813 $$;

814

815 CREATE CONSTRAINT TRIGGER "suggestion_requires_first_opinion"

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

817 FOR EACH ROW EXECUTE PROCEDURE

818 "suggestion_requires_first_opinion_trigger"();

819

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

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

822

823

824 CREATE FUNCTION "last_opinion_deletes_suggestion_trigger"()

825 RETURNS TRIGGER

826 LANGUAGE 'plpgsql' VOLATILE AS $$

827 DECLARE

828 "reference_lost" BOOLEAN;

829 BEGIN

830 IF TG_OP = 'DELETE' THEN

831 "reference_lost" := TRUE;

832 ELSE

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

834 END IF;

835 IF

836 "reference_lost" AND NOT EXISTS (

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

838 )

839 THEN

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

841 END IF;

842 RETURN NULL;

843 END;

844 $$;

845

846 CREATE CONSTRAINT TRIGGER "last_opinion_deletes_suggestion"

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

848 FOR EACH ROW EXECUTE PROCEDURE

849 "last_opinion_deletes_suggestion_trigger"();

850

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

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

853

854

855

856 --------------------------------------------------------------------

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

858 --------------------------------------------------------------------

859

860 CREATE FUNCTION "autofill_issue_id_trigger"()

861 RETURNS TRIGGER

862 LANGUAGE 'plpgsql' VOLATILE AS $$

863 BEGIN

864 IF NEW."issue_id" ISNULL THEN

865 SELECT "issue_id" INTO NEW."issue_id"

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

867 END IF;

868 RETURN NEW;

869 END;

870 $$;

871

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

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

874

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

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

877

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

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

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

881

882

883 CREATE FUNCTION "autofill_initiative_id_trigger"()

884 RETURNS TRIGGER

885 LANGUAGE 'plpgsql' VOLATILE AS $$

886 BEGIN

887 IF NEW."initiative_id" ISNULL THEN

888 SELECT "initiative_id" INTO NEW."initiative_id"

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

890 END IF;

891 RETURN NEW;

892 END;

893 $$;

894

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

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

897

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

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

900

901

902

903 -----------------------------------------------------

904 -- Automatic calculation of certain default values --

905 -----------------------------------------------------

906

907 CREATE FUNCTION "copy_autoreject_trigger"()

908 RETURNS TRIGGER

909 LANGUAGE 'plpgsql' VOLATILE AS $$

910 BEGIN

911 IF NEW."autoreject" ISNULL THEN

912 SELECT "membership"."autoreject" INTO NEW."autoreject"

913 FROM "issue" JOIN "membership"

914 ON "issue"."area_id" = "membership"."area_id"

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

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

917 END IF;

918 IF NEW."autoreject" ISNULL THEN

919 NEW."autoreject" := FALSE;

920 END IF;

921 RETURN NEW;

922 END;

923 $$;

924

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

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

927

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

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

930

931

932 CREATE FUNCTION "supporter_default_for_draft_id_trigger"()

933 RETURNS TRIGGER

934 LANGUAGE 'plpgsql' VOLATILE AS $$

935 BEGIN

936 IF NEW."draft_id" ISNULL THEN

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

938 WHERE "initiative_id" = NEW."initiative_id";

939 END IF;

940 RETURN NEW;

941 END;

942 $$;

943

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

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

946

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

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

949

950

951

952 ----------------------------------------

953 -- Automatic creation of dependencies --

954 ----------------------------------------

955

956 CREATE FUNCTION "autocreate_interest_trigger"()

957 RETURNS TRIGGER

958 LANGUAGE 'plpgsql' VOLATILE AS $$

959 BEGIN

960 IF NOT EXISTS (

961 SELECT NULL FROM "initiative" JOIN "interest"

962 ON "initiative"."issue_id" = "interest"."issue_id"

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

964 AND "interest"."member_id" = NEW."member_id"

965 ) THEN

966 BEGIN

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

968 SELECT "issue_id", NEW."member_id"

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

970 EXCEPTION WHEN unique_violation THEN END;

971 END IF;

972 RETURN NEW;

973 END;

974 $$;

975

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

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

978

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

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

981

982

983 CREATE FUNCTION "autocreate_supporter_trigger"()

984 RETURNS TRIGGER

985 LANGUAGE 'plpgsql' VOLATILE AS $$

986 BEGIN

987 IF NOT EXISTS (

988 SELECT NULL FROM "suggestion" JOIN "supporter"

989 ON "suggestion"."initiative_id" = "supporter"."initiative_id"

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

991 AND "supporter"."member_id" = NEW."member_id"

992 ) THEN

993 BEGIN

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

995 SELECT "initiative_id", NEW."member_id"

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

997 EXCEPTION WHEN unique_violation THEN END;

998 END IF;

999 RETURN NEW;

1000 END;

1001 $$;

1002

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

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

1005

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

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

1008

1009

1010

1011 ------------------------------------------

1012 -- Views and helper functions for views --

1013 ------------------------------------------

1014

1015

1016 CREATE VIEW "global_delegation" AS

1017 SELECT

1018 "delegation"."id",

1019 "delegation"."truster_id",

1020 "delegation"."trustee_id"

1021 FROM "delegation" JOIN "member"

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

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

1024

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

1026

1027

1028 CREATE VIEW "area_delegation" AS

1029 SELECT "subquery".* FROM (

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

1031 "area"."id" AS "area_id",

1032 "delegation"."id",

1033 "delegation"."truster_id",

1034 "delegation"."trustee_id",

1035 "delegation"."scope"

1036 FROM "area" JOIN "delegation"

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

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

1039 ORDER BY

1040 "area"."id",

1041 "delegation"."truster_id",

1042 "delegation"."scope" DESC

1043 ) AS "subquery"

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

1045 WHERE "member"."active";

1046

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

1048

1049

1050 CREATE VIEW "issue_delegation" AS

1051 SELECT "subquery".* FROM (

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

1053 "issue"."id" AS "issue_id",

1054 "delegation"."id",

1055 "delegation"."truster_id",

1056 "delegation"."trustee_id",

1057 "delegation"."scope"

1058 FROM "issue" JOIN "delegation"

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

1060 OR "delegation"."area_id" = "issue"."area_id"

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

1062 ORDER BY

1063 "issue"."id",

1064 "delegation"."truster_id",

1065 "delegation"."scope" DESC

1066 ) AS "subquery"

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

1068 WHERE "member"."active";

1069

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

1071

1072

1073 CREATE FUNCTION "membership_weight_with_skipping"

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

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

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

1077 RETURNS INT4

1078 LANGUAGE 'plpgsql' STABLE AS $$

1079 DECLARE

1080 "sum_v" INT4;

1081 "delegation_row" "area_delegation"%ROWTYPE;

1082 BEGIN

1083 "sum_v" := 1;

1084 FOR "delegation_row" IN

1085 SELECT "area_delegation".*

1086 FROM "area_delegation" LEFT JOIN "membership"

1087 ON "membership"."area_id" = "area_id_p"

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

1089 WHERE "area_delegation"."area_id" = "area_id_p"

1090 AND "area_delegation"."trustee_id" = "member_id_p"

1091 AND "membership"."member_id" ISNULL

1092 LOOP

1093 IF NOT

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

1095 THEN

1096 "sum_v" := "sum_v" + "membership_weight_with_skipping"(

1097 "area_id_p",

1098 "delegation_row"."truster_id",

1099 "skip_member_ids_p" || "delegation_row"."truster_id"

1100 );

1101 END IF;

1102 END LOOP;

1103 RETURN "sum_v";

1104 END;

1105 $$;

1106

1107 COMMENT ON FUNCTION "membership_weight_with_skipping"

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

1109 "member"."id"%TYPE,

1110 INT4[] )

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

1112

1113

1114 CREATE FUNCTION "membership_weight"

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

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

1117 RETURNS INT4

1118 LANGUAGE 'plpgsql' STABLE AS $$

1119 BEGIN

1120 RETURN "membership_weight_with_skipping"(

1121 "area_id_p",

1122 "member_id_p",

1123 ARRAY["member_id_p"]

1124 );

1125 END;

1126 $$;

1127

1128 COMMENT ON FUNCTION "membership_weight"

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

1130 "member"."id"%TYPE )

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

1132

1133

1134 CREATE VIEW "member_count_view" AS

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

1136

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

1138

1139

1140 CREATE VIEW "area_member_count" AS

1141 SELECT

1142 "area"."id" AS "area_id",

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

1144 coalesce(

1145 sum(

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

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

1148 ELSE 0 END

1149 )

1150 ) AS "member_weight",

1151 coalesce(

1152 sum(

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

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

1155 ELSE 0 END

1156 )

1157 ) AS "autoreject_weight"

1158 FROM "area"

1159 LEFT JOIN "membership"

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

1161 LEFT JOIN "member"

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

1163 AND "member"."active"

1164 GROUP BY "area"."id";

1165

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

1167

1168

1169 CREATE VIEW "opening_draft" AS

1170 SELECT "draft".* FROM (

1171 SELECT

1172 "initiative"."id" AS "initiative_id",

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

1174 FROM "initiative" JOIN "draft"

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

1176 GROUP BY "initiative"."id"

1177 ) AS "subquery"

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

1179

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

1181

1182

1183 CREATE VIEW "current_draft" AS

1184 SELECT "draft".* FROM (

1185 SELECT

1186 "initiative"."id" AS "initiative_id",

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

1188 FROM "initiative" JOIN "draft"

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

1190 GROUP BY "initiative"."id"

1191 ) AS "subquery"

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

1193

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

1195

1196

1197 CREATE VIEW "critical_opinion" AS

1198 SELECT * FROM "opinion"

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

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

1201

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

1203

1204

1205 CREATE VIEW "battle" AS

1206 SELECT

1207 "issue"."id" AS "issue_id",

1208 "winning_initiative"."id" AS "winning_initiative_id",

1209 "losing_initiative"."id" AS "losing_initiative_id",

1210 sum(

1211 CASE WHEN

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

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

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

1215 ) AS "count"

1216 FROM "issue"

1217 LEFT JOIN "direct_voter"

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

1219 JOIN "initiative" AS "winning_initiative"

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

1221 AND "winning_initiative"."agreed"

1222 JOIN "initiative" AS "losing_initiative"

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

1224 AND "losing_initiative"."agreed"

1225 LEFT JOIN "vote" AS "better_vote"

1226 ON "direct_voter"."member_id" = "better_vote"."member_id"

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

1228 LEFT JOIN "vote" AS "worse_vote"

1229 ON "direct_voter"."member_id" = "worse_vote"."member_id"

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

1231 WHERE

1232 "winning_initiative"."id" != "losing_initiative"."id"

1233 GROUP BY

1234 "issue"."id",

1235 "winning_initiative"."id",

1236 "losing_initiative"."id";

1237

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

1239

1240

1241 CREATE VIEW "expired_session" AS

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

1243

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

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

1246

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

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

1249

1250

1251 CREATE VIEW "open_issue" AS

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

1253

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

1255

1256

1257 CREATE VIEW "issue_with_ranks_missing" AS

1258 SELECT * FROM "issue"

1259 WHERE "fully_frozen" NOTNULL

1260 AND "closed" NOTNULL

1261 AND "ranks_available" = FALSE;

1262

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

1264

1265

1266 CREATE VIEW "member_contingent" AS

1267 SELECT

1268 "member"."id" AS "member_id",

1269 "contingent"."time_frame",

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

1271 (

1272 SELECT count(1) FROM "draft"

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

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

1275 ) + (

1276 SELECT count(1) FROM "suggestion"

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

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

1279 )

1280 ELSE NULL END AS "text_entry_count",

1281 "contingent"."text_entry_limit",

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

1283 SELECT count(1) FROM "opening_draft"

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

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

1286 ) ELSE NULL END AS "initiative_count",

1287 "contingent"."initiative_limit"

1288 FROM "member" CROSS JOIN "contingent";

1289

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

1291

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

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

1294

1295

1296 CREATE VIEW "member_contingent_left" AS

1297 SELECT

1298 "member_id",

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

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

1301 FROM "member_contingent" GROUP BY "member_id";

1302

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

1304

1305

1306

1307 --------------------------------------------------

1308 -- Set returning function for delegation chains --

1309 --------------------------------------------------

1310

1311

1312 CREATE TYPE "delegation_chain_loop_tag" AS ENUM

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

1314

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

1316

1317

1318 CREATE TYPE "delegation_chain_row" AS (

1319 "index" INT4,

1320 "member_id" INT4,

1321 "member_active" BOOLEAN,

1322 "participation" BOOLEAN,

1323 "overridden" BOOLEAN,

1324 "scope_in" "delegation_scope",

1325 "scope_out" "delegation_scope",

1326 "loop" "delegation_chain_loop_tag" );

1327

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

1329

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

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

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

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

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

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

1336

1337

1338 CREATE FUNCTION "delegation_chain"

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

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

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

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

1343 RETURNS SETOF "delegation_chain_row"

1344 LANGUAGE 'plpgsql' STABLE AS $$

1345 DECLARE

1346 "issue_row" "issue"%ROWTYPE;

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

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

1349 "output_row" "delegation_chain_row";

1350 "output_rows" "delegation_chain_row"[];

1351 "delegation_row" "delegation"%ROWTYPE;

1352 "row_count" INT4;

1353 "i" INT4;

1354 "loop_v" BOOLEAN;

1355 BEGIN

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

1357 "visited_member_ids" := '{}';

1358 "loop_member_id_v" := NULL;

1359 "output_rows" := '{}';

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

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

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

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

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

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

1366 LOOP

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

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

1369 ELSE

1370 "visited_member_ids" :=

1371 "visited_member_ids" || "output_row"."member_id";

1372 END IF;

1373 IF "output_row"."participation" THEN

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

1375 END IF;

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

1377 IF EXISTS (

1378 SELECT NULL FROM "member"

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

1380 ) THEN

1381 IF "area_id_p" ISNULL AND "issue_id_p" ISNULL THEN

1382 SELECT * INTO "delegation_row" FROM "delegation"

1383 WHERE "truster_id" = "output_row"."member_id"

1384 AND "scope" = 'global';

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

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

1387 SELECT NULL FROM "membership"

1388 WHERE "area_id" = "area_id_p"

1389 AND "member_id" = "output_row"."member_id"

1390 );

1391 SELECT * INTO "delegation_row" FROM "delegation"

1392 WHERE "truster_id" = "output_row"."member_id"

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

1394 ORDER BY "scope" DESC;

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

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

1397 SELECT NULL FROM "interest"

1398 WHERE "issue_id" = "issue_id_p"

1399 AND "member_id" = "output_row"."member_id"

1400 );

1401 SELECT * INTO "delegation_row" FROM "delegation"

1402 WHERE "truster_id" = "output_row"."member_id"

1403 AND ("scope" = 'global' OR

1404 "area_id" = "issue_row"."area_id" OR

1405 "issue_id" = "issue_id_p"

1406 )

1407 ORDER BY "scope" DESC;

1408 ELSE

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

1410 END IF;

1411 ELSE

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

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

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

1415 "delegation_row" := ROW(NULL);

1416 END IF;

1417 IF

1418 "output_row"."member_id" = "member_id_p" AND

1419 "simulate_trustee_id_p" NOTNULL

1420 THEN

1421 "output_row"."scope_out" := CASE

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

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

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

1425 END;

1426 "output_rows" := "output_rows" || "output_row";

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

1428 ELSIF "delegation_row"."trustee_id" NOTNULL THEN

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

1430 "output_rows" := "output_rows" || "output_row";

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

1432 ELSE

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

1434 "output_rows" := "output_rows" || "output_row";

1435 EXIT;

1436 END IF;

1437 EXIT WHEN "loop_member_id_v" NOTNULL;

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

1439 END LOOP;

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

1441 "i" := 1;

1442 "loop_v" := FALSE;

1443 LOOP

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

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

1446 IF "loop_v" THEN

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

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

1449 ELSIF "i" = "row_count" THEN

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

1451 ELSE

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

1453 END IF;

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

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

1456 "loop_v" := TRUE;

1457 END IF;

1458 IF "area_id_p" ISNULL AND "issue_id_p" ISNULL THEN

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

1460 END IF;

1461 RETURN NEXT "output_row";

1462 "i" := "i" + 1;

1463 END LOOP;

1464 RETURN;

1465 END;

1466 $$;

1467

1468 COMMENT ON FUNCTION "delegation_chain"

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

1470 "area"."id"%TYPE,

1471 "issue"."id"%TYPE,

1472 "member"."id"%TYPE )

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

1474

1475 CREATE FUNCTION "delegation_chain"

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

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

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

1479 RETURNS SETOF "delegation_chain_row"

1480 LANGUAGE 'plpgsql' STABLE AS $$

1481 DECLARE

1482 "result_row" "delegation_chain_row";

1483 BEGIN

1484 FOR "result_row" IN

1485 SELECT * FROM "delegation_chain"(

1486 "member_id_p", "area_id_p", "issue_id_p", NULL

1487 )

1488 LOOP

1489 RETURN NEXT "result_row";

1490 END LOOP;

1491 RETURN;

1492 END;

1493 $$;

1494

1495 COMMENT ON FUNCTION "delegation_chain"

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

1497 "area"."id"%TYPE,

1498 "issue"."id"%TYPE )

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

1500

1501

1502

1503 ------------------------------

1504 -- Comparison by vote count --

1505 ------------------------------

1506

1507 CREATE FUNCTION "vote_ratio"

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

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

1510 RETURNS FLOAT8

1511 LANGUAGE 'plpgsql' STABLE AS $$

1512 DECLARE

1513 "total_v" INT4;

1514 BEGIN

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

1516 IF "total_v" > 0 THEN

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

1518 ELSE

1519 RETURN 0.5;

1520 END IF;

1521 END;

1522 $$;

1523

1524 COMMENT ON FUNCTION "vote_ratio"

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

1526 "initiative"."negative_votes"%TYPE )

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

1528

1529

1530

1531 ------------------------------------------------

1532 -- Locking for snapshots and voting procedure --

1533 ------------------------------------------------

1534

1535 CREATE FUNCTION "global_lock"() RETURNS VOID

1536 LANGUAGE 'plpgsql' VOLATILE AS $$

1537 BEGIN

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

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

1540 LOCK TABLE "member" IN EXCLUSIVE MODE;

1541 LOCK TABLE "area" IN EXCLUSIVE MODE;

1542 LOCK TABLE "membership" IN EXCLUSIVE MODE;

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

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

1545 LOCK TABLE "policy" IN EXCLUSIVE MODE;

1546 LOCK TABLE "issue" IN EXCLUSIVE MODE;

1547 LOCK TABLE "initiative" IN EXCLUSIVE MODE;

1548 LOCK TABLE "draft" IN EXCLUSIVE MODE;

1549 LOCK TABLE "suggestion" IN EXCLUSIVE MODE;

1550 LOCK TABLE "interest" IN EXCLUSIVE MODE;

1551 LOCK TABLE "initiator" IN EXCLUSIVE MODE;

1552 LOCK TABLE "supporter" IN EXCLUSIVE MODE;

1553 LOCK TABLE "opinion" IN EXCLUSIVE MODE;

1554 LOCK TABLE "delegation" IN EXCLUSIVE MODE;

1555 LOCK TABLE "direct_population_snapshot" IN EXCLUSIVE MODE;

1556 LOCK TABLE "delegating_population_snapshot" IN EXCLUSIVE MODE;

1557 LOCK TABLE "direct_interest_snapshot" IN EXCLUSIVE MODE;

1558 LOCK TABLE "delegating_interest_snapshot" IN EXCLUSIVE MODE;

1559 LOCK TABLE "direct_supporter_snapshot" IN EXCLUSIVE MODE;

1560 LOCK TABLE "direct_voter" IN EXCLUSIVE MODE;

1561 LOCK TABLE "delegating_voter" IN EXCLUSIVE MODE;

1562 LOCK TABLE "vote" IN EXCLUSIVE MODE;

1563 RETURN;

1564 END;

1565 $$;

1566

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

1568

1569

1570

1571 -------------------------------

1572 -- Materialize member counts --

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

1574

1575 CREATE FUNCTION "calculate_member_counts"()

1576 RETURNS VOID

1577 LANGUAGE 'plpgsql' VOLATILE AS $$

1578 BEGIN

1579 LOCK TABLE "member" IN EXCLUSIVE MODE;

1580 LOCK TABLE "area" IN EXCLUSIVE MODE;

1581 LOCK TABLE "membership" IN EXCLUSIVE MODE;

1582 DELETE FROM "member_count";

1583 INSERT INTO "member_count" ("total_count")

1584 SELECT "total_count" FROM "member_count_view";

1585 UPDATE "area" SET

1586 "direct_member_count" = "view"."direct_member_count",

1587 "member_weight" = "view"."member_weight",

1588 "autoreject_weight" = "view"."autoreject_weight"

1589 FROM "area_member_count" AS "view"

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

1591 RETURN;

1592 END;

1593 $$;

1594

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

1596

1597

1598

1599 ------------------------------

1600 -- Calculation of snapshots --

1601 ------------------------------

1602

1603 CREATE FUNCTION "weight_of_added_delegations_for_population_snapshot"

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

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

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

1607 RETURNS "direct_population_snapshot"."weight"%TYPE

1608 LANGUAGE 'plpgsql' VOLATILE AS $$

1609 DECLARE

1610 "issue_delegation_row" "issue_delegation"%ROWTYPE;

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

1612 "weight_v" INT4;

1613 "sub_weight_v" INT4;

1614 BEGIN

1615 "weight_v" := 0;

1616 FOR "issue_delegation_row" IN

1617 SELECT * FROM "issue_delegation"

1618 WHERE "trustee_id" = "member_id_p"

1619 AND "issue_id" = "issue_id_p"

1620 LOOP

1621 IF NOT EXISTS (

1622 SELECT NULL FROM "direct_population_snapshot"

1623 WHERE "issue_id" = "issue_id_p"

1624 AND "event" = 'periodic'

1625 AND "member_id" = "issue_delegation_row"."truster_id"

1626 ) AND NOT EXISTS (

1627 SELECT NULL FROM "delegating_population_snapshot"

1628 WHERE "issue_id" = "issue_id_p"

1629 AND "event" = 'periodic'

1630 AND "member_id" = "issue_delegation_row"."truster_id"

1631 ) THEN

1632 "delegate_member_ids_v" :=

1633 "member_id_p" || "delegate_member_ids_p";

1634 INSERT INTO "delegating_population_snapshot" (

1635 "issue_id",

1636 "event",

1637 "member_id",

1638 "scope",

1639 "delegate_member_ids"

1640 ) VALUES (

1641 "issue_id_p",

1642 'periodic',

1643 "issue_delegation_row"."truster_id",

1644 "issue_delegation_row"."scope",

1645 "delegate_member_ids_v"

1646 );

1647 "sub_weight_v" := 1 +

1648 "weight_of_added_delegations_for_population_snapshot"(

1649 "issue_id_p",

1650 "issue_delegation_row"."truster_id",

1651 "delegate_member_ids_v"

1652 );

1653 UPDATE "delegating_population_snapshot"

1654 SET "weight" = "sub_weight_v"

1655 WHERE "issue_id" = "issue_id_p"

1656 AND "event" = 'periodic'

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

1658 "weight_v" := "weight_v" + "sub_weight_v";

1659 END IF;

1660 END LOOP;

1661 RETURN "weight_v";

1662 END;

1663 $$;

1664

1665 COMMENT ON FUNCTION "weight_of_added_delegations_for_population_snapshot"

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

1667 "member"."id"%TYPE,

1668 "delegating_population_snapshot"."delegate_member_ids"%TYPE )

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

1670

1671

1672 CREATE FUNCTION "create_population_snapshot"

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

1674 RETURNS VOID

1675 LANGUAGE 'plpgsql' VOLATILE AS $$

1676 DECLARE

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

1678 BEGIN

1679 DELETE FROM "direct_population_snapshot"

1680 WHERE "issue_id" = "issue_id_p"

1681 AND "event" = 'periodic';

1682 DELETE FROM "delegating_population_snapshot"

1683 WHERE "issue_id" = "issue_id_p"

1684 AND "event" = 'periodic';

1685 INSERT INTO "direct_population_snapshot"

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

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

1688 "issue_id_p" AS "issue_id",

1689 'periodic' AS "event",

1690 "subquery"."member_id",

1691 "subquery"."interest_exists"

1692 FROM (

1693 SELECT

1694 "member"."id" AS "member_id",

1695 FALSE AS "interest_exists"

1696 FROM "issue"

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

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

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

1700 WHERE "issue"."id" = "issue_id_p"

1701 AND "member"."active"

1702 UNION

1703 SELECT

1704 "member"."id" AS "member_id",

1705 TRUE AS "interest_exists"

1706 FROM "interest" JOIN "member"

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

1708 WHERE "interest"."issue_id" = "issue_id_p"

1709 AND "member"."active"

1710 ) AS "subquery"

1711 ORDER BY

1712 "issue_id_p",

1713 "subquery"."member_id",

1714 "subquery"."interest_exists" DESC;

1715 FOR "member_id_v" IN

1716 SELECT "member_id" FROM "direct_population_snapshot"

1717 WHERE "issue_id" = "issue_id_p"

1718 AND "event" = 'periodic'

1719 LOOP

1720 UPDATE "direct_population_snapshot" SET

1721 "weight" = 1 +

1722 "weight_of_added_delegations_for_population_snapshot"(

1723 "issue_id_p",

1724 "member_id_v",

1725 '{}'

1726 )

1727 WHERE "issue_id" = "issue_id_p"

1728 AND "event" = 'periodic'

1729 AND "member_id" = "member_id_v";

1730 END LOOP;

1731 RETURN;

1732 END;

1733 $$;

1734

1735 COMMENT ON FUNCTION "create_population_snapshot"

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

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

1738

1739

1740 CREATE FUNCTION "weight_of_added_delegations_for_interest_snapshot"

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

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

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

1744 RETURNS "direct_interest_snapshot"."weight"%TYPE

1745 LANGUAGE 'plpgsql' VOLATILE AS $$

1746 DECLARE

1747 "issue_delegation_row" "issue_delegation"%ROWTYPE;

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

1749 "weight_v" INT4;

1750 "sub_weight_v" INT4;

1751 BEGIN

1752 "weight_v" := 0;

1753 FOR "issue_delegation_row" IN

1754 SELECT * FROM "issue_delegation"

1755 WHERE "trustee_id" = "member_id_p"

1756 AND "issue_id" = "issue_id_p"

1757 LOOP

1758 IF NOT EXISTS (

1759 SELECT NULL FROM "direct_interest_snapshot"

1760 WHERE "issue_id" = "issue_id_p"

1761 AND "event" = 'periodic'

1762 AND "member_id" = "issue_delegation_row"."truster_id"

1763 ) AND NOT EXISTS (

1764 SELECT NULL FROM "delegating_interest_snapshot"

1765 WHERE "issue_id" = "issue_id_p"

1766 AND "event" = 'periodic'

1767 AND "member_id" = "issue_delegation_row"."truster_id"

1768 ) THEN

1769 "delegate_member_ids_v" :=

1770 "member_id_p" || "delegate_member_ids_p";

1771 INSERT INTO "delegating_interest_snapshot" (

1772 "issue_id",

1773 "event",

1774 "member_id",

1775 "scope",

1776 "delegate_member_ids"

1777 ) VALUES (

1778 "issue_id_p",

1779 'periodic',

1780 "issue_delegation_row"."truster_id",

1781 "issue_delegation_row"."scope",

1782 "delegate_member_ids_v"

1783 );

1784 "sub_weight_v" := 1 +

1785 "weight_of_added_delegations_for_interest_snapshot"(

1786 "issue_id_p",

1787 "issue_delegation_row"."truster_id",

1788 "delegate_member_ids_v"

1789 );

1790 UPDATE "delegating_interest_snapshot"

1791 SET "weight" = "sub_weight_v"

1792 WHERE "issue_id" = "issue_id_p"

1793 AND "event" = 'periodic'

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

1795 "weight_v" := "weight_v" + "sub_weight_v";

1796 END IF;

1797 END LOOP;

1798 RETURN "weight_v";

1799 END;

1800 $$;

1801

1802 COMMENT ON FUNCTION "weight_of_added_delegations_for_interest_snapshot"

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

1804 "member"."id"%TYPE,

1805 "delegating_interest_snapshot"."delegate_member_ids"%TYPE )

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

1807

1808

1809 CREATE FUNCTION "create_interest_snapshot"

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

1811 RETURNS VOID

1812 LANGUAGE 'plpgsql' VOLATILE AS $$

1813 DECLARE

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

1815 BEGIN

1816 DELETE FROM "direct_interest_snapshot"

1817 WHERE "issue_id" = "issue_id_p"

1818 AND "event" = 'periodic';

1819 DELETE FROM "delegating_interest_snapshot"

1820 WHERE "issue_id" = "issue_id_p"

1821 AND "event" = 'periodic';

1822 DELETE FROM "direct_supporter_snapshot"

1823 WHERE "issue_id" = "issue_id_p"

1824 AND "event" = 'periodic';

1825 INSERT INTO "direct_interest_snapshot"

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

1827 SELECT

1828 "issue_id_p" AS "issue_id",

1829 'periodic' AS "event",

1830 "member"."id" AS "member_id",

1831 "interest"."voting_requested"

1832 FROM "interest" JOIN "member"

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

1834 WHERE "interest"."issue_id" = "issue_id_p"

1835 AND "member"."active";

1836 FOR "member_id_v" IN

1837 SELECT "member_id" FROM "direct_interest_snapshot"

1838 WHERE "issue_id" = "issue_id_p"

1839 AND "event" = 'periodic'

1840 LOOP

1841 UPDATE "direct_interest_snapshot" SET

1842 "weight" = 1 +

1843 "weight_of_added_delegations_for_interest_snapshot"(

1844 "issue_id_p",

1845 "member_id_v",

1846 '{}'

1847 )

1848 WHERE "issue_id" = "issue_id_p"

1849 AND "event" = 'periodic'

1850 AND "member_id" = "member_id_v";

1851 END LOOP;

1852 INSERT INTO "direct_supporter_snapshot"

1853 ( "issue_id", "initiative_id", "event", "member_id",

1854 "informed", "satisfied" )

1855 SELECT

1856 "issue_id_p" AS "issue_id",

1857 "initiative"."id" AS "initiative_id",

1858 'periodic' AS "event",

1859 "member"."id" AS "member_id",

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

1861 NOT EXISTS (

1862 SELECT NULL FROM "critical_opinion"

1863 WHERE "initiative_id" = "initiative"."id"

1864 AND "member_id" = "member"."id"

1865 ) AS "satisfied"

1866 FROM "supporter"

1867 JOIN "member"

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

1869 JOIN "initiative"

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

1871 JOIN "current_draft"

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

1873 JOIN "direct_interest_snapshot"

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

1875 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"

1876 AND "event" = 'periodic'

1877 WHERE "member"."active"

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

1879 RETURN;

1880 END;

1881 $$;

1882

1883 COMMENT ON FUNCTION "create_interest_snapshot"

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

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

1886

1887

1888 CREATE FUNCTION "create_snapshot"

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

1890 RETURNS VOID

1891 LANGUAGE 'plpgsql' VOLATILE AS $$

1892 DECLARE

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

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

1895 BEGIN

1896 PERFORM "global_lock"();

1897 PERFORM "create_population_snapshot"("issue_id_p");

1898 PERFORM "create_interest_snapshot"("issue_id_p");

1899 UPDATE "issue" SET

1900 "snapshot" = now(),

1901 "latest_snapshot_event" = 'periodic',

1902 "population" = (

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

1904 FROM "direct_population_snapshot"

1905 WHERE "issue_id" = "issue_id_p"

1906 AND "event" = 'periodic'

1907 ),

1908 "vote_now" = (

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

1910 FROM "direct_interest_snapshot"

1911 WHERE "issue_id" = "issue_id_p"

1912 AND "event" = 'periodic'

1913 AND "voting_requested" = TRUE

1914 ),

1915 "vote_later" = (

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

1917 FROM "direct_interest_snapshot"

1918 WHERE "issue_id" = "issue_id_p"

1919 AND "event" = 'periodic'

1920 AND "voting_requested" = FALSE

1921 )

1922 WHERE "id" = "issue_id_p";

1923 FOR "initiative_id_v" IN

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

1925 LOOP

1926 UPDATE "initiative" SET

1927 "supporter_count" = (

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

1929 FROM "direct_interest_snapshot" AS "di"

1930 JOIN "direct_supporter_snapshot" AS "ds"

1931 ON "di"."member_id" = "ds"."member_id"

1932 WHERE "di"."issue_id" = "issue_id_p"

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

1934 AND "ds"."initiative_id" = "initiative_id_v"

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

1936 ),

1937 "informed_supporter_count" = (

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

1939 FROM "direct_interest_snapshot" AS "di"

1940 JOIN "direct_supporter_snapshot" AS "ds"

1941 ON "di"."member_id" = "ds"."member_id"

1942 WHERE "di"."issue_id" = "issue_id_p"

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

1944 AND "ds"."initiative_id" = "initiative_id_v"

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

1946 AND "ds"."informed"

1947 ),

1948 "satisfied_supporter_count" = (

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

1950 FROM "direct_interest_snapshot" AS "di"

1951 JOIN "direct_supporter_snapshot" AS "ds"

1952 ON "di"."member_id" = "ds"."member_id"

1953 WHERE "di"."issue_id" = "issue_id_p"

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

1955 AND "ds"."initiative_id" = "initiative_id_v"

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

1957 AND "ds"."satisfied"

1958 ),

1959 "satisfied_informed_supporter_count" = (

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

1961 FROM "direct_interest_snapshot" AS "di"

1962 JOIN "direct_supporter_snapshot" AS "ds"

1963 ON "di"."member_id" = "ds"."member_id"

1964 WHERE "di"."issue_id" = "issue_id_p"

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

1966 AND "ds"."initiative_id" = "initiative_id_v"

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

1968 AND "ds"."informed"

1969 AND "ds"."satisfied"

1970 )

1971 WHERE "id" = "initiative_id_v";

1972 FOR "suggestion_id_v" IN

1973 SELECT "id" FROM "suggestion"

1974 WHERE "initiative_id" = "initiative_id_v"

1975 LOOP

1976 UPDATE "suggestion" SET

1977 "minus2_unfulfilled_count" = (

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

1979 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"

1980 ON "opinion"."member_id" = "snapshot"."member_id"

1981 WHERE "opinion"."suggestion_id" = "suggestion_id_v"

1982 AND "snapshot"."issue_id" = "issue_id_p"

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

1984 AND "opinion"."fulfilled" = FALSE

1985 ),

1986 "minus2_fulfilled_count" = (

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

1988 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"

1989 ON "opinion"."member_id" = "snapshot"."member_id"

1990 WHERE "opinion"."suggestion_id" = "suggestion_id_v"

1991 AND "snapshot"."issue_id" = "issue_id_p"

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

1993 AND "opinion"."fulfilled" = TRUE

1994 ),

1995 "minus1_unfulfilled_count" = (

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

1997 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"

1998 ON "opinion"."member_id" = "snapshot"."member_id"

1999 WHERE "opinion"."suggestion_id" = "suggestion_id_v"

2000 AND "snapshot"."issue_id" = "issue_id_p"

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

2002 AND "opinion"."fulfilled" = FALSE

2003 ),

2004 "minus1_fulfilled_count" = (

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

2006 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"

2007 ON "opinion"."member_id" = "snapshot"."member_id"

2008 WHERE "opinion"."suggestion_id" = "suggestion_id_v"

2009 AND "snapshot"."issue_id" = "issue_id_p"

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

2011 AND "opinion"."fulfilled" = TRUE

2012 ),

2013 "plus1_unfulfilled_count" = (

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

2015 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"

2016 ON "opinion"."member_id" = "snapshot"."member_id"

2017 WHERE "opinion"."suggestion_id" = "suggestion_id_v"

2018 AND "snapshot"."issue_id" = "issue_id_p"

2019 AND "opinion"."degree" = 1

2020 AND "opinion"."fulfilled" = FALSE

2021 ),

2022 "plus1_fulfilled_count" = (

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

2024 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"

2025 ON "opinion"."member_id" = "snapshot"."member_id"

2026 WHERE "opinion"."suggestion_id" = "suggestion_id_v"

2027 AND "snapshot"."issue_id" = "issue_id_p"

2028 AND "opinion"."degree" = 1

2029 AND "opinion"."fulfilled" = TRUE

2030 ),

2031 "plus2_unfulfilled_count" = (

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

2033 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"

2034 ON "opinion"."member_id" = "snapshot"."member_id"

2035 WHERE "opinion"."suggestion_id" = "suggestion_id_v"

2036 AND "snapshot"."issue_id" = "issue_id_p"

2037 AND "opinion"."degree" = 2

2038 AND "opinion"."fulfilled" = FALSE

2039 ),

2040 "plus2_fulfilled_count" = (

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

2042 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"

2043 ON "opinion"."member_id" = "snapshot"."member_id"

2044 WHERE "opinion"."suggestion_id" = "suggestion_id_v"

2045 AND "snapshot"."issue_id" = "issue_id_p"

2046 AND "opinion"."degree" = 2

2047 AND "opinion"."fulfilled" = TRUE

2048 )

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

2050 END LOOP;

2051 END LOOP;

2052 RETURN;

2053 END;

2054 $$;

2055

2056 COMMENT ON FUNCTION "create_snapshot"

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

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

2059

2060

2061 CREATE FUNCTION "set_snapshot_event"

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

2063 "event_p" "snapshot_event" )

2064 RETURNS VOID

2065 LANGUAGE 'plpgsql' VOLATILE AS $$

2066 BEGIN

2067 UPDATE "issue" SET "latest_snapshot_event" = "event_p"

2068 WHERE "id" = "issue_id_p";

2069 UPDATE "direct_population_snapshot" SET "event" = "event_p"

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

2071 UPDATE "delegating_population_snapshot" SET "event" = "event_p"

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

2073 UPDATE "direct_interest_snapshot" SET "event" = "event_p"

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

2075 UPDATE "delegating_interest_snapshot" SET "event" = "event_p"

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

2077 UPDATE "direct_supporter_snapshot" SET "event" = "event_p"

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

2079 RETURN;

2080 END;

2081 $$;

2082

2083 COMMENT ON FUNCTION "set_snapshot_event"

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

2085 "snapshot_event" )

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

2087

2088

2089

2090 ---------------------

2091 -- Freezing issues --

2092 ---------------------

2093

2094 CREATE FUNCTION "freeze_after_snapshot"

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

2096 RETURNS VOID

2097 LANGUAGE 'plpgsql' VOLATILE AS $$

2098 DECLARE

2099 "issue_row" "issue"%ROWTYPE;

2100 "policy_row" "policy"%ROWTYPE;

2101 "initiative_row" "initiative"%ROWTYPE;

2102 BEGIN

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

2104 SELECT * INTO "policy_row"

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

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

2107 UPDATE "issue" SET

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

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

2110 "fully_frozen" = now()

2111 WHERE "id" = "issue_id_p";

2112 FOR "initiative_row" IN

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

2114 LOOP

2115 IF

2116 "initiative_row"."satisfied_supporter_count" > 0 AND

2117 "initiative_row"."satisfied_supporter_count" *

2118 "policy_row"."initiative_quorum_den" >=

2119 "issue_row"."population" * "policy_row"."initiative_quorum_num"

2120 THEN

2121 UPDATE "initiative" SET "admitted" = TRUE

2122 WHERE "id" = "initiative_row"."id";

2123 ELSE

2124 UPDATE "initiative" SET "admitted" = FALSE

2125 WHERE "id" = "initiative_row"."id";

2126 END IF;

2127 END LOOP;

2128 IF NOT EXISTS (

2129 SELECT NULL FROM "initiative"

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

2131 ) THEN

2132 PERFORM "close_voting"("issue_id_p");

2133 END IF;

2134 RETURN;

2135 END;

2136 $$;

2137

2138 COMMENT ON FUNCTION "freeze_after_snapshot"

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

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

2141

2142

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

2144 RETURNS VOID

2145 LANGUAGE 'plpgsql' VOLATILE AS $$

2146 DECLARE

2147 "issue_row" "issue"%ROWTYPE;

2148 BEGIN

2149 PERFORM "create_snapshot"("issue_id_p");

2150 PERFORM "freeze_after_snapshot"("issue_id_p");

2151 RETURN;

2152 END;

2153 $$;

2154

2155 COMMENT ON FUNCTION "freeze_after_snapshot"

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

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

2158

2159

2160

2161 -----------------------

2162 -- Counting of votes --

2163 -----------------------

2164

2165

2166 CREATE FUNCTION "weight_of_added_vote_delegations"

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

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

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

2170 RETURNS "direct_voter"."weight"%TYPE

2171 LANGUAGE 'plpgsql' VOLATILE AS $$

2172 DECLARE

2173 "issue_delegation_row" "issue_delegation"%ROWTYPE;

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

2175 "weight_v" INT4;

2176 "sub_weight_v" INT4;

2177 BEGIN

2178 "weight_v" := 0;

2179 FOR "issue_delegation_row" IN

2180 SELECT * FROM "issue_delegation"

2181 WHERE "trustee_id" = "member_id_p"

2182 AND "issue_id" = "issue_id_p"

2183 LOOP

2184 IF NOT EXISTS (

2185 SELECT NULL FROM "direct_voter"

2186 WHERE "member_id" = "issue_delegation_row"."truster_id"

2187 AND "issue_id" = "issue_id_p"

2188 ) AND NOT EXISTS (

2189 SELECT NULL FROM "delegating_voter"

2190 WHERE "member_id" = "issue_delegation_row"."truster_id"

2191 AND "issue_id" = "issue_id_p"

2192 ) THEN

2193 "delegate_member_ids_v" :=

2194 "member_id_p" || "delegate_member_ids_p";

2195 INSERT INTO "delegating_voter" (

2196 "issue_id",

2197 "member_id",

2198 "scope",

2199 "delegate_member_ids"

2200 ) VALUES (

2201 "issue_id_p",

2202 "issue_delegation_row"."truster_id",

2203 "issue_delegation_row"."scope",

2204 "delegate_member_ids_v"

2205 );

2206 "sub_weight_v" := 1 +

2207 "weight_of_added_vote_delegations"(

2208 "issue_id_p",

2209 "issue_delegation_row"."truster_id",

2210 "delegate_member_ids_v"

2211 );

2212 UPDATE "delegating_voter"

2213 SET "weight" = "sub_weight_v"

2214 WHERE "issue_id" = "issue_id_p"

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

2216 "weight_v" := "weight_v" + "sub_weight_v";

2217 END IF;

2218 END LOOP;

2219 RETURN "weight_v";

2220 END;

2221 $$;

2222

2223 COMMENT ON FUNCTION "weight_of_added_vote_delegations"

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

2225 "member"."id"%TYPE,

2226 "delegating_voter"."delegate_member_ids"%TYPE )

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

2228

2229

2230 CREATE FUNCTION "add_vote_delegations"

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

2232 RETURNS VOID

2233 LANGUAGE 'plpgsql' VOLATILE AS $$

2234 DECLARE

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

2236 BEGIN

2237 FOR "member_id_v" IN

2238 SELECT "member_id" FROM "direct_voter"

2239 WHERE "issue_id" = "issue_id_p"

2240 LOOP

2241 UPDATE "direct_voter" SET

2242 "weight" = "weight" + "weight_of_added_vote_delegations"(

2243 "issue_id_p",

2244 "member_id_v",

2245 '{}'

2246 )

2247 WHERE "member_id" = "member_id_v"

2248 AND "issue_id" = "issue_id_p";

2249 END LOOP;

2250 RETURN;

2251 END;

2252 $$;

2253

2254 COMMENT ON FUNCTION "add_vote_delegations"

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

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

2257

2258

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

2260 RETURNS VOID

2261 LANGUAGE 'plpgsql' VOLATILE AS $$

2262 DECLARE

2263 "issue_row" "issue"%ROWTYPE;

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

2265 BEGIN

2266 PERFORM "global_lock"();

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

2268 DELETE FROM "delegating_voter"

2269 WHERE "issue_id" = "issue_id_p";

2270 DELETE FROM "direct_voter"

2271 WHERE "issue_id" = "issue_id_p"

2272 AND "autoreject" = TRUE;

2273 DELETE FROM "direct_voter" USING "member"

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

2275 AND "direct_voter"."issue_id" = "issue_id_p"

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

2277 UPDATE "direct_voter" SET "weight" = 1

2278 WHERE "issue_id" = "issue_id_p";

2279 PERFORM "add_vote_delegations"("issue_id_p");

2280 FOR "member_id_v" IN

2281 SELECT "interest"."member_id"

2282 FROM "interest"

2283 LEFT JOIN "direct_voter"

2284 ON "interest"."member_id" = "direct_voter"."member_id"

2285 AND "interest"."issue_id" = "direct_voter"."issue_id"

2286 LEFT JOIN "delegating_voter"

2287 ON "interest"."member_id" = "delegating_voter"."member_id"

2288 AND "interest"."issue_id" = "delegating_voter"."issue_id"

2289 WHERE "interest"."issue_id" = "issue_id_p"

2290 AND "interest"."autoreject" = TRUE

2291 AND "direct_voter"."member_id" ISNULL

2292 AND "delegating_voter"."member_id" ISNULL

2293 UNION SELECT "membership"."member_id"

2294 FROM "membership"

2295 LEFT JOIN "interest"

2296 ON "membership"."member_id" = "interest"."member_id"

2297 AND "interest"."issue_id" = "issue_id_p"

2298 LEFT JOIN "direct_voter"

2299 ON "membership"."member_id" = "direct_voter"."member_id"

2300 AND "direct_voter"."issue_id" = "issue_id_p"

2301 LEFT JOIN "delegating_voter"

2302 ON "membership"."member_id" = "delegating_voter"."member_id"

2303 AND "delegating_voter"."issue_id" = "issue_id_p"

2304 WHERE "membership"."area_id" = "issue_row"."area_id"

2305 AND "membership"."autoreject" = TRUE

2306 AND "interest"."autoreject" ISNULL

2307 AND "direct_voter"."member_id" ISNULL

2308 AND "delegating_voter"."member_id" ISNULL

2309 LOOP

2310 INSERT INTO "direct_voter"

2311 ("member_id", "issue_id", "weight", "autoreject") VALUES

2312 ("member_id_v", "issue_id_p", 1, TRUE);

2313 INSERT INTO "vote" (

2314 "member_id",

2315 "issue_id",

2316 "initiative_id",

2317 "grade"

2318 ) SELECT

2319 "member_id_v" AS "member_id",

2320 "issue_id_p" AS "issue_id",

2321 "id" AS "initiative_id",

2322 -1 AS "grade"

2323 FROM "initiative" WHERE "issue_id" = "issue_id_p";

2324 END LOOP;

2325 PERFORM "add_vote_delegations"("issue_id_p");

2326 UPDATE "issue" SET

2327 "voter_count" = (

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

2329 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"

2330 )

2331 WHERE "id" = "issue_id_p";

2332 UPDATE "initiative" SET

2333 "positive_votes" = "vote_counts"."positive_votes",

2334 "negative_votes" = "vote_counts"."negative_votes",

2335 "agreed" = CASE WHEN "majority_strict" THEN

2336 "vote_counts"."positive_votes" * "majority_den" >

2337 "majority_num" *

2338 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")

2339 ELSE

2340 "vote_counts"."positive_votes" * "majority_den" >=

2341 "majority_num" *

2342 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")

2343 END

2344 FROM

2345 ( SELECT

2346 "initiative"."id" AS "initiative_id",

2347 coalesce(

2348 sum(

2349 CASE WHEN "grade" > 0 THEN "direct_voter"."weight" ELSE 0 END

2350 ),

2351 0

2352 ) AS "positive_votes",

2353 coalesce(

2354 sum(

2355 CASE WHEN "grade" < 0 THEN "direct_voter"."weight" ELSE 0 END

2356 ),

2357 0

2358 ) AS "negative_votes"

2359 FROM "initiative"

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

2361 JOIN "policy" ON "issue"."policy_id" = "policy"."id"

2362 LEFT JOIN "direct_voter"

2363 ON "direct_voter"."issue_id" = "initiative"."issue_id"

2364 LEFT JOIN "vote"

2365 ON "vote"."initiative_id" = "initiative"."id"

2366 AND "vote"."member_id" = "direct_voter"."member_id"

2367 WHERE "initiative"."issue_id" = "issue_id_p"

2368 AND "initiative"."admitted"

2369 GROUP BY "initiative"."id"

2370 ) AS "vote_counts",

2371 "issue",

2372 "policy"

2373 WHERE "vote_counts"."initiative_id" = "initiative"."id"

2374 AND "issue"."id" = "initiative"."issue_id"

2375 AND "policy"."id" = "issue"."policy_id";

2376 UPDATE "issue" SET "closed" = now() WHERE "id" = "issue_id_p";

2377 END;

2378 $$;

2379

2380 COMMENT ON FUNCTION "close_voting"

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

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

2383

2384

2385 CREATE FUNCTION "init_array"("dim_p" INTEGER)

2386 RETURNS INT4[]

2387 LANGUAGE 'plpgsql' IMMUTABLE AS $$

2388 DECLARE

2389 "i" INTEGER;

2390 "ary_text_v" TEXT;

2391 BEGIN

2392 IF "dim_p" >= 1 THEN

2393 "ary_text_v" := '{NULL';

2394 "i" := "dim_p";

2395 LOOP

2396 "i" := "i" - 1;

2397 EXIT WHEN "i" = 0;

2398 "ary_text_v" := "ary_text_v" || ',NULL';

2399 END LOOP;

2400 "ary_text_v" := "ary_text_v" || '}';

2401 RETURN "ary_text_v"::INT4[][];

2402 ELSE

2403 RAISE EXCEPTION 'Dimension needs to be at least 1.';

2404 END IF;

2405 END;

2406 $$;

2407

2408 COMMENT ON FUNCTION "init_array"(INTEGER) IS 'Needed for PostgreSQL < 8.4, due to missing "array_fill" function';

2409

2410

2411 CREATE FUNCTION "init_square_matrix"("dim_p" INTEGER)

2412 RETURNS INT4[][]

2413 LANGUAGE 'plpgsql' IMMUTABLE AS $$

2414 DECLARE

2415 "i" INTEGER;

2416 "row_text_v" TEXT;

2417 "ary_text_v" TEXT;

2418 BEGIN

2419 IF "dim_p" >= 1 THEN

2420 "row_text_v" := '{NULL';

2421 "i" := "dim_p";

2422 LOOP

2423 "i" := "i" - 1;

2424 EXIT WHEN "i" = 0;

2425 "row_text_v" := "row_text_v" || ',NULL';

2426 END LOOP;

2427 "row_text_v" := "row_text_v" || '}';

2428 "ary_text_v" := '{' || "row_text_v";

2429 "i" := "dim_p";

2430 LOOP

2431 "i" := "i" - 1;

2432 EXIT WHEN "i" = 0;

2433 "ary_text_v" := "ary_text_v" || ',' || "row_text_v";

2434 END LOOP;

2435 "ary_text_v" := "ary_text_v" || '}';

2436 RETURN "ary_text_v"::INT4[][];

2437 ELSE

2438 RAISE EXCEPTION 'Dimension needs to be at least 1.';

2439 END IF;

2440 END;

2441 $$;

2442

2443 COMMENT ON FUNCTION "init_square_matrix"(INTEGER) IS 'Needed for PostgreSQL < 8.4, due to missing "array_fill" function';

2444

2445

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

2447 RETURNS VOID

2448 LANGUAGE 'plpgsql' VOLATILE AS $$

2449 DECLARE

2450 "dimension_v" INTEGER;

2451 "matrix" INT4[][];

2452 "i" INTEGER;

2453 "j" INTEGER;

2454 "k" INTEGER;

2455 "battle_row" "battle"%ROWTYPE;

2456 "rank_ary" INT4[];

2457 "rank_v" INT4;

2458 "done_v" INTEGER;

2459 "winners_ary" INTEGER[];

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

2461 BEGIN

2462 PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE;

2463 -- Prepare matrix for Schulze-Method:

2464 SELECT count(1) INTO "dimension_v" FROM "initiative"

2465 WHERE "issue_id" = "issue_id_p" AND "agreed";

2466 IF "dimension_v" = 1 THEN

2467 UPDATE "initiative" SET "rank" = 1

2468 WHERE "issue_id" = "issue_id_p" AND "agreed";

2469 ELSIF "dimension_v" > 1 THEN

2470 "matrix" := "init_square_matrix"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4)

2471 "i" := 1;

2472 "j" := 2;

2473 -- Fill matrix with data from "battle" view

2474 FOR "battle_row" IN

2475 SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p"

2476 ORDER BY "winning_initiative_id", "losing_initiative_id"

2477 LOOP

2478 "matrix"["i"]["j"] := "battle_row"."count";

2479 IF "j" = "dimension_v" THEN

2480 "i" := "i" + 1;

2481 "j" := 1;

2482 ELSE

2483 "j" := "j" + 1;

2484 IF "j" = "i" THEN

2485 "j" := "j" + 1;

2486 END IF;

2487 END IF;

2488 END LOOP;

2489 IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN

2490 RAISE EXCEPTION 'Wrong battle count (should not happen)';

2491 END IF;

2492 -- Delete losers from matrix:

2493 "i" := 1;

2494 LOOP

2495 "j" := "i" + 1;

2496 LOOP

2497 IF "i" != "j" THEN

2498 IF "matrix"["i"]["j"] < "matrix"["j"]["i"] THEN

2499 "matrix"["i"]["j"] := 0;

2500 ELSIF matrix[j][i] < matrix[i][j] THEN

2501 "matrix"["j"]["i"] := 0;

2502 ELSE

2503 "matrix"["i"]["j"] := 0;

2504 "matrix"["j"]["i"] := 0;

2505 END IF;

2506 END IF;

2507 EXIT WHEN "j" = "dimension_v";

2508 "j" := "j" + 1;

2509 END LOOP;

2510 EXIT WHEN "i" = "dimension_v" - 1;

2511 "i" := "i" + 1;

2512 END LOOP;

2513 -- Find best paths:

2514 "i" := 1;

2515 LOOP

2516 "j" := 1;

2517 LOOP

2518 IF "i" != "j" THEN

2519 "k" := 1;

2520 LOOP

2521 IF "i" != "k" AND "j" != "k" THEN

2522 IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN

2523 IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN

2524 "matrix"["j"]["k"] := "matrix"["j"]["i"];

2525 END IF;

2526 ELSE

2527 IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN

2528 "matrix"["j"]["k"] := "matrix"["i"]["k"];

2529 END IF;

2530 END IF;

2531 END IF;

2532 EXIT WHEN "k" = "dimension_v";

2533 "k" := "k" + 1;

2534 END LOOP;

2535 END IF;

2536 EXIT WHEN "j" = "dimension_v";

2537 "j" := "j" + 1;

2538 END LOOP;

2539 EXIT WHEN "i" = "dimension_v";

2540 "i" := "i" + 1;

2541 END LOOP;

2542 -- Determine order of winners:

2543 "rank_ary" := "init_array"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4)

2544 "rank_v" := 1;

2545 "done_v" := 0;

2546 LOOP

2547 "winners_ary" := '{}';

2548 "i" := 1;

2549 LOOP

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

2551 "j" := 1;

2552 LOOP

2553 IF

2554 "i" != "j" AND

2555 "rank_ary"["j"] ISNULL AND

2556 "matrix"["j"]["i"] > "matrix"["i"]["j"]

2557 THEN

2558 -- someone else is better

2559 EXIT;

2560 END IF;

2561 IF "j" = "dimension_v" THEN

2562 -- noone is better

2563 "winners_ary" := "winners_ary" || "i";

2564 EXIT;

2565 END IF;

2566 "j" := "j" + 1;

2567 END LOOP;

2568 END IF;

2569 EXIT WHEN "i" = "dimension_v";

2570 "i" := "i" + 1;

2571 END LOOP;

2572 "i" := 1;

2573 LOOP

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

2575 "done_v" := "done_v" + 1;

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

2577 "i" := "i" + 1;

2578 END LOOP;

2579 EXIT WHEN "done_v" = "dimension_v";

2580 "rank_v" := "rank_v" + 1;

2581 END LOOP;

2582 -- write preliminary ranks:

2583 "i" := 1;

2584 FOR "initiative_id_v" IN

2585 SELECT "id" FROM "initiative"

2586 WHERE "issue_id" = "issue_id_p" AND "agreed"

2587 ORDER BY "id"

2588 LOOP

2589 UPDATE "initiative" SET "rank" = "rank_ary"["i"]

2590 WHERE "id" = "initiative_id_v";

2591 "i" := "i" + 1;

2592 END LOOP;

2593 IF "i" != "dimension_v" + 1 THEN

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

2595 END IF;

2596 -- straighten ranks (start counting with 1, no equal ranks):

2597 "rank_v" := 1;

2598 FOR "initiative_id_v" IN

2599 SELECT "id" FROM "initiative"

2600 WHERE "issue_id" = "issue_id_p" AND "rank" NOTNULL

2601 ORDER BY

2602 "rank",

2603 "vote_ratio"("positive_votes", "negative_votes") DESC,

2604 "id"

2605 LOOP

2606 UPDATE "initiative" SET "rank" = "rank_v"

2607 WHERE "id" = "initiative_id_v";

2608 "rank_v" := "rank_v" + 1;

2609 END LOOP;

2610 END IF;

2611 -- mark issue as finished

2612 UPDATE "issue" SET "ranks_available" = TRUE

2613 WHERE "id" = "issue_id_p";

2614 RETURN;

2615 END;

2616 $$;

2617

2618 COMMENT ON FUNCTION "calculate_ranks"

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

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

2621

2622

2623

2624 -----------------------------

2625 -- Automatic state changes --

2626 -----------------------------

2627

2628

2629 CREATE FUNCTION "check_issue"

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

2631 RETURNS VOID

2632 LANGUAGE 'plpgsql' VOLATILE AS $$

2633 DECLARE

2634 "issue_row" "issue"%ROWTYPE;

2635 "policy_row" "policy"%ROWTYPE;

2636 "voting_requested_v" BOOLEAN;

2637 BEGIN

2638 PERFORM "global_lock"();

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

2640 IF "issue_row"."closed" ISNULL THEN

2641 SELECT * INTO "policy_row" FROM "policy"

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

2643 IF "issue_row"."fully_frozen" ISNULL THEN

2644 PERFORM "create_snapshot"("issue_id_p");

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

2646 END IF;

2647 IF "issue_row"."accepted" ISNULL THEN

2648 IF EXISTS (

2649 SELECT NULL FROM "initiative"

2650 WHERE "issue_id" = "issue_id_p"

2651 AND "supporter_count" > 0

2652 AND "supporter_count" * "policy_row"."issue_quorum_den"

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

2654 ) THEN

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

2656 "issue_row"."accepted" = now(); -- NOTE: "issue_row" used later

2657 UPDATE "issue" SET "accepted" = "issue_row"."accepted"

2658 WHERE "id" = "issue_row"."id";

2659 ELSIF

2660 now() >= "issue_row"."created" + "policy_row"."admission_time"

2661 THEN

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

2663 UPDATE "issue" SET "closed" = now()

2664 WHERE "id" = "issue_row"."id";

2665 END IF;

2666 END IF;

2667 IF

2668 "issue_row"."accepted" NOTNULL AND

2669 "issue_row"."half_frozen" ISNULL

2670 THEN

2671 SELECT

2672 CASE

2673 WHEN "vote_now" * 2 > "issue_row"."population" THEN

2674 TRUE

2675 WHEN "vote_later" * 2 > "issue_row"."population" THEN

2676 FALSE

2677 ELSE NULL

2678 END

2679 INTO "voting_requested_v"

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

2681 IF

2682 "voting_requested_v" OR (

2683 "voting_requested_v" ISNULL AND

2684 now() >= "issue_row"."accepted" + "policy_row"."discussion_time"

2685 )

2686 THEN

2687 "issue_row"."half_frozen" = now(); -- NOTE: "issue_row" used later

2688 UPDATE "issue" SET "half_frozen" = "issue_row"."half_frozen"

2689 WHERE "id" = "issue_row"."id";

2690 END IF;

2691 END IF;

2692 IF

2693 "issue_row"."half_frozen" NOTNULL AND

2694 "issue_row"."fully_frozen" ISNULL AND

2695 now() >= "issue_row"."half_frozen" + "policy_row"."verification_time"

2696 THEN

2697 PERFORM "freeze_after_snapshot"("issue_id_p");

2698 -- "issue" might change, thus "issue_row" has to be updated below

2699 END IF;

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

2701 IF

2702 "issue_row"."closed" ISNULL AND

2703 "issue_row"."fully_frozen" NOTNULL AND

2704 now() >= "issue_row"."fully_frozen" + "policy_row"."voting_time"

2705 THEN

2706 PERFORM "close_voting"("issue_id_p");

2707 END IF;

2708 END IF;

2709 RETURN;

2710 END;

2711 $$;

2712

2713 COMMENT ON FUNCTION "check_issue"

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

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

2716

2717

2718 CREATE FUNCTION "check_everything"()

2719 RETURNS VOID

2720 LANGUAGE 'plpgsql' VOLATILE AS $$

2721 DECLARE

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

2723 BEGIN

2724 DELETE FROM "expired_session";

2725 PERFORM "calculate_member_counts"();

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

2727 PERFORM "check_issue"("issue_id_v");

2728 END LOOP;

2729 FOR "issue_id_v" IN SELECT "id" FROM "issue_with_ranks_missing" LOOP

2730 PERFORM "calculate_ranks"("issue_id_v");

2731 END LOOP;

2732 RETURN;

2733 END;

2734 $$;

2735

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

2737

2738

2739

2740 ------------------------------

2741 -- Deletion of private data --

2742 ------------------------------

2743

2744

2745 CREATE FUNCTION "delete_private_data"()

2746 RETURNS VOID

2747 LANGUAGE 'plpgsql' VOLATILE AS $$

2748 DECLARE

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

2750 BEGIN

2751 UPDATE "member" SET

2752 "login" = 'login' || "id"::text,

2753 "password" = NULL,

2754 "notify_email" = NULL,

2755 "notify_email_unconfirmed" = NULL,

2756 "notify_email_secret" = NULL,

2757 "notify_email_secret_expiry" = NULL,

2758 "password_reset_secret" = NULL,

2759 "password_reset_secret_expiry" = NULL,

2760 "organizational_unit" = NULL,

2761 "internal_posts" = NULL,

2762 "realname" = NULL,

2763 "birthday" = NULL,

2764 "address" = NULL,

2765 "email" = NULL,

2766 "xmpp_address" = NULL,

2767 "website" = NULL,

2768 "phone" = NULL,

2769 "mobile_phone" = NULL,

2770 "profession" = NULL,

2771 "external_memberships" = NULL,

2772 "external_posts" = NULL,

2773 "statement" = NULL;

2774 -- "text_search_data" is updated by triggers

2775 DELETE FROM "session";

2776 DELETE FROM "invite_code" WHERE "used" ISNULL;

2777 DELETE FROM "contact" WHERE NOT "public";

2778 DELETE FROM "setting";

2779 DELETE FROM "member_image";

2780 DELETE FROM "direct_voter" USING "issue"

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

2782 AND "issue"."closed" ISNULL;

2783 RETURN;

2784 END;

2785 $$;

2786

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

2788

2789

2790

2791 COMMIT;