liquid_feedback_core: effdd7a04ea7 core.sql

liquid_feedback_core

view core.sql @ 10:effdd7a04ea7

Version beta11

Support is now automatically deleted, when interest is deleted

All tables dealing with delegations now contain a column scope (including table delegation)

Changes in fields related to notify_email of member

Policies can now specify larger majorities to be reached (e.g. 2/3)

New column agreed in table initiative to mark, if initiative has reached majority specified by policy

Minor bugfix in constraint non_admitted_initiatives_cant_contain_voting_results of initiative table

Function delete_private_data() no longer deletes invite codes, which have been already used

Removed view battle_participant

Added index on opinion (member_id, initiative_id)

author	jbe
date	Fri Dec 25 12:00:00 2009 +0100 (2009-12-25)
parents	4af4df1415f9
children	015825e225ca

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 ('beta11', 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,

65 "notify_email_secret_expiry" TIMESTAMPTZ,

66 "name" TEXT NOT NULL UNIQUE,

67 "identification" TEXT UNIQUE,

68 "organizational_unit" TEXT,

69 "internal_posts" TEXT,

70 "realname" TEXT,

71 "birthday" DATE,

72 "address" TEXT,

73 "email" TEXT,

74 "xmpp_address" TEXT,

75 "website" TEXT,

76 "phone" TEXT,

77 "mobile_phone" TEXT,

78 "profession" TEXT,

79 "external_memberships" TEXT,

80 "external_posts" TEXT,

81 "statement" TEXT,

82 "text_search_data" TSVECTOR );

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

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

85 CREATE TRIGGER "update_text_search_data"

86 BEFORE INSERT OR UPDATE ON "member"

87 FOR EACH ROW EXECUTE PROCEDURE

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

89 "name", "identification", "organizational_unit", "internal_posts",

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

111

112

113 CREATE TABLE "invite_code" (

114 "code" TEXT PRIMARY KEY,

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

116 "used" TIMESTAMPTZ,

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

118 "comment" TEXT,

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

120

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

122

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

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

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

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

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

128

129

130 CREATE TABLE "setting" (

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

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

133 "key" TEXT NOT NULL,

134 "value" TEXT NOT NULL );

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

136

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

138

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

140

141

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

143

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

145

146

147 CREATE TABLE "member_image" (

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

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

150 "image_type" "member_image_type",

151 "scaled" BOOLEAN,

152 "content_type" TEXT,

153 "data" BYTEA NOT NULL );

154

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

156

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

158

159

160 CREATE TABLE "member_count" (

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

162 "total_count" INT4 NOT NULL );

163

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

165

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

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

168

169

170 CREATE TABLE "contact" (

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

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

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

174 "public" BOOLEAN NOT NULL DEFAULT FALSE );

175

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

177

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

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

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

181

182

183 CREATE TABLE "session" (

184 "ident" TEXT PRIMARY KEY,

185 "additional_secret" TEXT,

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

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

188 "lang" TEXT );

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

190

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

192

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

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

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

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

197

198

199 CREATE TABLE "policy" (

200 "id" SERIAL4 PRIMARY KEY,

201 "index" INT4 NOT NULL,

202 "active" BOOLEAN NOT NULL DEFAULT TRUE,

203 "name" TEXT NOT NULL UNIQUE,

204 "description" TEXT NOT NULL DEFAULT '',

205 "admission_time" INTERVAL NOT NULL,

206 "discussion_time" INTERVAL NOT NULL,

207 "verification_time" INTERVAL NOT NULL,

208 "voting_time" INTERVAL NOT NULL,

209 "issue_quorum_num" INT4 NOT NULL,

210 "issue_quorum_den" INT4 NOT NULL,

211 "initiative_quorum_num" INT4 NOT NULL,

212 "initiative_quorum_den" INT4 NOT NULL,

213 "majority_num" INT4 NOT NULL DEFAULT 1,

214 "majority_den" INT4 NOT NULL DEFAULT 2,

215 "majority_strict" BOOLEAN NOT NULL DEFAULT TRUE );

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

217

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

219

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

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

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

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

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

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

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

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

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

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

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

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

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

233

234

235 CREATE TABLE "area" (

236 "id" SERIAL4 PRIMARY KEY,

237 "active" BOOLEAN NOT NULL DEFAULT TRUE,

238 "name" TEXT NOT NULL,

239 "description" TEXT NOT NULL DEFAULT '',

240 "direct_member_count" INT4,

241 "member_weight" INT4,

242 "autoreject_weight" INT4,

243 "text_search_data" TSVECTOR );

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

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

246 CREATE TRIGGER "update_text_search_data"

247 BEFORE INSERT OR UPDATE ON "area"

248 FOR EACH ROW EXECUTE PROCEDURE

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

250 "name", "description" );

251

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

253

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

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

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

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

258

259

260 CREATE TABLE "allowed_policy" (

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

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

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

264 "default_policy" BOOLEAN NOT NULL DEFAULT FALSE );

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

266

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

268

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

270

271

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

273

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

275

276

277 CREATE TABLE "issue" (

278 "id" SERIAL4 PRIMARY KEY,

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

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

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

282 "accepted" TIMESTAMPTZ,

283 "half_frozen" TIMESTAMPTZ,

284 "fully_frozen" TIMESTAMPTZ,

285 "closed" TIMESTAMPTZ,

286 "ranks_available" BOOLEAN NOT NULL DEFAULT FALSE,

287 "snapshot" TIMESTAMPTZ,

288 "latest_snapshot_event" "snapshot_event",

289 "population" INT4,

290 "vote_now" INT4,

291 "vote_later" INT4,

292 "voter_count" INT4,

293 CONSTRAINT "valid_state" CHECK (

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

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

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

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

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

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

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

301 CONSTRAINT "state_change_order" CHECK (

302 "created" <= "accepted" AND

303 "accepted" <= "half_frozen" AND

304 "half_frozen" <= "fully_frozen" AND

305 "fully_frozen" <= "closed" ),

306 CONSTRAINT "last_snapshot_on_full_freeze"

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

308 CONSTRAINT "freeze_requires_snapshot"

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

310 CONSTRAINT "set_both_or_none_of_snapshot_and_latest_snapshot_event"

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

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

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

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

315

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

317

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

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

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

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

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

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

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

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

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

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

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

329

330

331 CREATE TABLE "initiative" (

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

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

334 "id" SERIAL4 PRIMARY KEY,

335 "name" TEXT NOT NULL,

336 "discussion_url" TEXT,

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

338 "revoked" TIMESTAMPTZ,

339 "admitted" BOOLEAN,

340 "supporter_count" INT4,

341 "informed_supporter_count" INT4,

342 "satisfied_supporter_count" INT4,

343 "satisfied_informed_supporter_count" INT4,

344 "positive_votes" INT4,

345 "negative_votes" INT4,

346 "agreed" BOOLEAN,

347 "rank" INT4,

348 "text_search_data" TSVECTOR,

349 CONSTRAINT "revoked_initiatives_cant_be_admitted"

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

351 CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results"

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

353 CONSTRAINT "all_or_none_of_positive_votes_negative_votes_and_agreed_must_be_null"

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

355 CONSTRAINT "non_agreed_initiatives_cant_get_a_rank"

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

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

358 CREATE TRIGGER "update_text_search_data"

359 BEFORE INSERT OR UPDATE ON "initiative"

360 FOR EACH ROW EXECUTE PROCEDURE

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

362 "name", "discussion_url");

363

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

365

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

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

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

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

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

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

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

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

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

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

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

377

378

379 CREATE TABLE "draft" (

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

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

382 "id" SERIAL8 PRIMARY KEY,

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

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

385 "formatting_engine" TEXT,

386 "content" TEXT NOT NULL,

387 "text_search_data" TSVECTOR );

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

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

390 CREATE TRIGGER "update_text_search_data"

391 BEFORE INSERT OR UPDATE ON "draft"

392 FOR EACH ROW EXECUTE PROCEDURE

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

394

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

396

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

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

399

400

401 CREATE TABLE "suggestion" (

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

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

404 "id" SERIAL8 PRIMARY KEY,

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

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

407 "name" TEXT NOT NULL,

408 "description" TEXT NOT NULL DEFAULT '',

409 "text_search_data" TSVECTOR,

410 "minus2_unfulfilled_count" INT4,

411 "minus2_fulfilled_count" INT4,

412 "minus1_unfulfilled_count" INT4,

413 "minus1_fulfilled_count" INT4,

414 "plus1_unfulfilled_count" INT4,

415 "plus1_fulfilled_count" INT4,

416 "plus2_unfulfilled_count" INT4,

417 "plus2_fulfilled_count" INT4 );

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

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

420 CREATE TRIGGER "update_text_search_data"

421 BEFORE INSERT OR UPDATE ON "suggestion"

422 FOR EACH ROW EXECUTE PROCEDURE

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

424 "name", "description");

425

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

427

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

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

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

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

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

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

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

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

436

437

438 CREATE TABLE "membership" (

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

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

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

442 "autoreject" BOOLEAN NOT NULL DEFAULT FALSE );

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

444

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

446

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

448

449

450 CREATE TABLE "interest" (

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

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

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

454 "autoreject" BOOLEAN NOT NULL,

455 "voting_requested" BOOLEAN );

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

457

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

459

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

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

462

463

464 CREATE TABLE "initiator" (

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

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

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

468 "accepted" BOOLEAN NOT NULL DEFAULT TRUE );

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

470

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

472

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

474

475

476 CREATE TABLE "supporter" (

477 "issue_id" INT4 NOT NULL,

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

479 "initiative_id" INT4,

480 "member_id" INT4,

481 "draft_id" INT8 NOT NULL,

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

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

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

485

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

487

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

489

490

491 CREATE TABLE "opinion" (

492 "initiative_id" INT4 NOT NULL,

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

494 "suggestion_id" INT8,

495 "member_id" INT4,

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

497 "fulfilled" BOOLEAN NOT NULL DEFAULT FALSE,

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

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

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

501

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

503

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

505

506

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

508

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

510

511

512 CREATE TABLE "delegation" (

513 "id" SERIAL8 PRIMARY KEY,

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

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

516 "scope" "delegation_scope" NOT NULL,

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

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

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

520 CONSTRAINT "area_id_and_issue_id_set_according_to_scope" CHECK (

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

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

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

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

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

526 CREATE UNIQUE INDEX "delegation_global_truster_id_trustee_id_unique_idx"

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

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

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

530

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

532

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

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

535

536

537 CREATE TABLE "direct_population_snapshot" (

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

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

540 "event" "snapshot_event",

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

542 "weight" INT4,

543 "interest_exists" BOOLEAN NOT NULL );

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

545

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

547

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

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

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

551

552

553 CREATE TABLE "delegating_population_snapshot" (

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

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

556 "event" "snapshot_event",

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

558 "weight" INT4,

559 "scope" "delegation_scope" NOT NULL,

560 "delegate_member_ids" INT4[] NOT NULL );

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

562

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

564

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

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

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

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

569

570

571 CREATE TABLE "direct_interest_snapshot" (

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

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

574 "event" "snapshot_event",

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

576 "weight" INT4,

577 "voting_requested" BOOLEAN );

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

579

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

581

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

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

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

585

586

587 CREATE TABLE "delegating_interest_snapshot" (

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

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

590 "event" "snapshot_event",

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

592 "weight" INT4,

593 "scope" "delegation_scope" NOT NULL,

594 "delegate_member_ids" INT4[] NOT NULL );

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

596

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

598

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

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

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

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

603

604

605 CREATE TABLE "direct_supporter_snapshot" (

606 "issue_id" INT4 NOT NULL,

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

608 "initiative_id" INT4,

609 "event" "snapshot_event",

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

611 "informed" BOOLEAN NOT NULL,

612 "satisfied" BOOLEAN NOT NULL,

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

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

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

616

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

618

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

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

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

622

623

624 CREATE TABLE "direct_voter" (

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

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

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

628 "weight" INT4,

629 "autoreject" BOOLEAN NOT NULL DEFAULT FALSE );

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

631

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

633

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

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

636

637

638 CREATE TABLE "delegating_voter" (

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

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

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

642 "weight" INT4,

643 "scope" "delegation_scope" NOT NULL,

644 "delegate_member_ids" INT4[] NOT NULL );

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

646

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

648

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

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

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

652

653

654 CREATE TABLE "vote" (

655 "issue_id" INT4 NOT NULL,

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

657 "initiative_id" INT4,

658 "member_id" INT4,

659 "grade" INT4,

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

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

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

663

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

665

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

667

668

669 CREATE TABLE "contingent" (

670 "time_frame" INTERVAL PRIMARY KEY,

671 "text_entry_limit" INT4,

672 "initiative_limit" INT4 );

673

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

675

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

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

678

679

680

681 ----------------------------

682 -- Additional constraints --

683 ----------------------------

684

685

686 CREATE FUNCTION "issue_requires_first_initiative_trigger"()

687 RETURNS TRIGGER

688 LANGUAGE 'plpgsql' VOLATILE AS $$

689 BEGIN

690 IF NOT EXISTS (

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

692 ) THEN

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

694 -- ERRCODE = 'integrity_constraint_violation',

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

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

697 END IF;

698 RETURN NULL;

699 END;

700 $$;

701

702 CREATE CONSTRAINT TRIGGER "issue_requires_first_initiative"

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

704 FOR EACH ROW EXECUTE PROCEDURE

705 "issue_requires_first_initiative_trigger"();

706

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

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

709

710

711 CREATE FUNCTION "last_initiative_deletes_issue_trigger"()

712 RETURNS TRIGGER

713 LANGUAGE 'plpgsql' VOLATILE AS $$

714 DECLARE

715 "reference_lost" BOOLEAN;

716 BEGIN

717 IF TG_OP = 'DELETE' THEN

718 "reference_lost" := TRUE;

719 ELSE

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

721 END IF;

722 IF

723 "reference_lost" AND NOT EXISTS (

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

725 )

726 THEN

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

728 END IF;

729 RETURN NULL;

730 END;

731 $$;

732

733 CREATE CONSTRAINT TRIGGER "last_initiative_deletes_issue"

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

735 FOR EACH ROW EXECUTE PROCEDURE

736 "last_initiative_deletes_issue_trigger"();

737

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

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

740

741

742 CREATE FUNCTION "initiative_requires_first_draft_trigger"()

743 RETURNS TRIGGER

744 LANGUAGE 'plpgsql' VOLATILE AS $$

745 BEGIN

746 IF NOT EXISTS (

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

748 ) THEN

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

750 -- ERRCODE = 'integrity_constraint_violation',

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

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

753 END IF;

754 RETURN NULL;

755 END;

756 $$;

757

758 CREATE CONSTRAINT TRIGGER "initiative_requires_first_draft"

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

760 FOR EACH ROW EXECUTE PROCEDURE

761 "initiative_requires_first_draft_trigger"();

762

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

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

765

766

767 CREATE FUNCTION "last_draft_deletes_initiative_trigger"()

768 RETURNS TRIGGER

769 LANGUAGE 'plpgsql' VOLATILE AS $$

770 DECLARE

771 "reference_lost" BOOLEAN;

772 BEGIN

773 IF TG_OP = 'DELETE' THEN

774 "reference_lost" := TRUE;

775 ELSE

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

777 END IF;

778 IF

779 "reference_lost" AND NOT EXISTS (

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

781 )

782 THEN

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

784 END IF;

785 RETURN NULL;

786 END;

787 $$;

788

789 CREATE CONSTRAINT TRIGGER "last_draft_deletes_initiative"

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

791 FOR EACH ROW EXECUTE PROCEDURE

792 "last_draft_deletes_initiative_trigger"();

793

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

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

796

797

798 CREATE FUNCTION "suggestion_requires_first_opinion_trigger"()

799 RETURNS TRIGGER

800 LANGUAGE 'plpgsql' VOLATILE AS $$

801 BEGIN

802 IF NOT EXISTS (

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

804 ) THEN

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

806 END IF;

807 RETURN NULL;

808 END;

809 $$;

810

811 CREATE CONSTRAINT TRIGGER "suggestion_requires_first_opinion"

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

813 FOR EACH ROW EXECUTE PROCEDURE

814 "suggestion_requires_first_opinion_trigger"();

815

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

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

818

819

820 CREATE FUNCTION "last_opinion_deletes_suggestion_trigger"()

821 RETURNS TRIGGER

822 LANGUAGE 'plpgsql' VOLATILE AS $$

823 DECLARE

824 "reference_lost" BOOLEAN;

825 BEGIN

826 IF TG_OP = 'DELETE' THEN

827 "reference_lost" := TRUE;

828 ELSE

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

830 END IF;

831 IF

832 "reference_lost" AND NOT EXISTS (

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

834 )

835 THEN

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

837 END IF;

838 RETURN NULL;

839 END;

840 $$;

841

842 CREATE CONSTRAINT TRIGGER "last_opinion_deletes_suggestion"

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

844 FOR EACH ROW EXECUTE PROCEDURE

845 "last_opinion_deletes_suggestion_trigger"();

846

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

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

849

850

851

852 --------------------------------------------------------------------

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

854 --------------------------------------------------------------------

855

856 CREATE FUNCTION "autofill_issue_id_trigger"()

857 RETURNS TRIGGER

858 LANGUAGE 'plpgsql' VOLATILE AS $$

859 BEGIN

860 IF NEW."issue_id" ISNULL THEN

861 SELECT "issue_id" INTO NEW."issue_id"

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

863 END IF;

864 RETURN NEW;

865 END;

866 $$;

867

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

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

870

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

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

873

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

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

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

877

878

879 CREATE FUNCTION "autofill_initiative_id_trigger"()

880 RETURNS TRIGGER

881 LANGUAGE 'plpgsql' VOLATILE AS $$

882 BEGIN

883 IF NEW."initiative_id" ISNULL THEN

884 SELECT "initiative_id" INTO NEW."initiative_id"

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

886 END IF;

887 RETURN NEW;

888 END;

889 $$;

890

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

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

893

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

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

896

897

898

899 -----------------------------------------------------

900 -- Automatic calculation of certain default values --

901 -----------------------------------------------------

902

903 CREATE FUNCTION "copy_autoreject_trigger"()

904 RETURNS TRIGGER

905 LANGUAGE 'plpgsql' VOLATILE AS $$

906 BEGIN

907 IF NEW."autoreject" ISNULL THEN

908 SELECT "membership"."autoreject" INTO NEW."autoreject"

909 FROM "issue" JOIN "membership"

910 ON "issue"."area_id" = "membership"."area_id"

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

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

913 END IF;

914 IF NEW."autoreject" ISNULL THEN

915 NEW."autoreject" := FALSE;

916 END IF;

917 RETURN NEW;

918 END;

919 $$;

920

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

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

923

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

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

926

927

928 CREATE FUNCTION "supporter_default_for_draft_id_trigger"()

929 RETURNS TRIGGER

930 LANGUAGE 'plpgsql' VOLATILE AS $$

931 BEGIN

932 IF NEW."draft_id" ISNULL THEN

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

934 WHERE "initiative_id" = NEW."initiative_id";

935 END IF;

936 RETURN NEW;

937 END;

938 $$;

939

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

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

942

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

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

945

946

947

948 ----------------------------------------

949 -- Automatic creation of dependencies --

950 ----------------------------------------

951

952 CREATE FUNCTION "autocreate_interest_trigger"()

953 RETURNS TRIGGER

954 LANGUAGE 'plpgsql' VOLATILE AS $$

955 BEGIN

956 IF NOT EXISTS (

957 SELECT NULL FROM "initiative" JOIN "interest"

958 ON "initiative"."issue_id" = "interest"."issue_id"

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

960 AND "interest"."member_id" = NEW."member_id"

961 ) THEN

962 BEGIN

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

964 SELECT "issue_id", NEW."member_id"

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

966 EXCEPTION WHEN unique_violation THEN END;

967 END IF;

968 RETURN NEW;

969 END;

970 $$;

971

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

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

974

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

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

977

978

979 CREATE FUNCTION "autocreate_supporter_trigger"()

980 RETURNS TRIGGER

981 LANGUAGE 'plpgsql' VOLATILE AS $$

982 BEGIN

983 IF NOT EXISTS (

984 SELECT NULL FROM "suggestion" JOIN "supporter"

985 ON "suggestion"."initiative_id" = "supporter"."initiative_id"

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

987 AND "supporter"."member_id" = NEW."member_id"

988 ) THEN

989 BEGIN

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

991 SELECT "initiative_id", NEW."member_id"

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

993 EXCEPTION WHEN unique_violation THEN END;

994 END IF;

995 RETURN NEW;

996 END;

997 $$;

998

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

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

1001

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

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

1004

1005

1006

1007 ------------------------------------------

1008 -- Views and helper functions for views --

1009 ------------------------------------------

1010

1011

1012 CREATE VIEW "global_delegation" AS

1013 SELECT

1014 "delegation"."id",

1015 "delegation"."truster_id",

1016 "delegation"."trustee_id"

1017 FROM "delegation" JOIN "member"

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

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

1020

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

1022

1023

1024 CREATE VIEW "area_delegation" AS

1025 SELECT "subquery".* FROM (

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

1027 "area"."id" AS "area_id",

1028 "delegation"."id",

1029 "delegation"."truster_id",

1030 "delegation"."trustee_id",

1031 "delegation"."scope"

1032 FROM "area" JOIN "delegation"

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

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

1035 ORDER BY

1036 "area"."id",

1037 "delegation"."truster_id",

1038 "delegation"."scope" DESC

1039 ) AS "subquery"

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

1041 WHERE "member"."active";

1042

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

1044

1045

1046 CREATE VIEW "issue_delegation" AS

1047 SELECT "subquery".* FROM (

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

1049 "issue"."id" AS "issue_id",

1050 "delegation"."id",

1051 "delegation"."truster_id",

1052 "delegation"."trustee_id",

1053 "delegation"."scope"

1054 FROM "issue" JOIN "delegation"

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

1056 OR "delegation"."area_id" = "issue"."area_id"

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

1058 ORDER BY

1059 "issue"."id",

1060 "delegation"."truster_id",

1061 "delegation"."scope" DESC

1062 ) AS "subquery"

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

1064 WHERE "member"."active";

1065

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

1067

1068

1069 CREATE FUNCTION "membership_weight_with_skipping"

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

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

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

1073 RETURNS INT4

1074 LANGUAGE 'plpgsql' STABLE AS $$

1075 DECLARE

1076 "sum_v" INT4;

1077 "delegation_row" "area_delegation"%ROWTYPE;

1078 BEGIN

1079 "sum_v" := 1;

1080 FOR "delegation_row" IN

1081 SELECT "area_delegation".*

1082 FROM "area_delegation" LEFT JOIN "membership"

1083 ON "membership"."area_id" = "area_id_p"

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

1085 WHERE "area_delegation"."area_id" = "area_id_p"

1086 AND "area_delegation"."trustee_id" = "member_id_p"

1087 AND "membership"."member_id" ISNULL

1088 LOOP

1089 IF NOT

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

1091 THEN

1092 "sum_v" := "sum_v" + "membership_weight_with_skipping"(

1093 "area_id_p",

1094 "delegation_row"."truster_id",

1095 "skip_member_ids_p" || "delegation_row"."truster_id"

1096 );

1097 END IF;

1098 END LOOP;

1099 RETURN "sum_v";

1100 END;

1101 $$;

1102

1103 COMMENT ON FUNCTION "membership_weight_with_skipping"

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

1105 "member"."id"%TYPE,

1106 INT4[] )

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

1108

1109

1110 CREATE FUNCTION "membership_weight"

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

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

1113 RETURNS INT4

1114 LANGUAGE 'plpgsql' STABLE AS $$

1115 BEGIN

1116 RETURN "membership_weight_with_skipping"(

1117 "area_id_p",

1118 "member_id_p",

1119 ARRAY["member_id_p"]

1120 );

1121 END;

1122 $$;

1123

1124 COMMENT ON FUNCTION "membership_weight"

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

1126 "member"."id"%TYPE )

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

1128

1129

1130 CREATE VIEW "member_count_view" AS

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

1132

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

1134

1135

1136 CREATE VIEW "area_member_count" AS

1137 SELECT

1138 "area"."id" AS "area_id",

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

1140 coalesce(

1141 sum(

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

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

1144 ELSE 0 END

1145 )

1146 ) AS "member_weight",

1147 coalesce(

1148 sum(

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

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

1151 ELSE 0 END

1152 )

1153 ) AS "autoreject_weight"

1154 FROM "area"

1155 LEFT JOIN "membership"

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

1157 LEFT JOIN "member"

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

1159 AND "member"."active"

1160 GROUP BY "area"."id";

1161

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

1163

1164

1165 CREATE VIEW "opening_draft" AS

1166 SELECT "draft".* FROM (

1167 SELECT

1168 "initiative"."id" AS "initiative_id",

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

1170 FROM "initiative" JOIN "draft"

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

1172 GROUP BY "initiative"."id"

1173 ) AS "subquery"

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

1175

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

1177

1178

1179 CREATE VIEW "current_draft" AS

1180 SELECT "draft".* FROM (

1181 SELECT

1182 "initiative"."id" AS "initiative_id",

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

1184 FROM "initiative" JOIN "draft"

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

1186 GROUP BY "initiative"."id"

1187 ) AS "subquery"

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

1189

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

1191

1192

1193 CREATE VIEW "critical_opinion" AS

1194 SELECT * FROM "opinion"

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

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

1197

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

1199

1200

1201 CREATE VIEW "battle" AS

1202 SELECT

1203 "issue"."id" AS "issue_id",

1204 "winning_initiative"."id" AS "winning_initiative_id",

1205 "losing_initiative"."id" AS "losing_initiative_id",

1206 sum(

1207 CASE WHEN

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

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

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

1211 ) AS "count"

1212 FROM "issue"

1213 LEFT JOIN "direct_voter"

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

1215 JOIN "initiative" AS "winning_initiative"

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

1217 AND "winning_initiative"."agreed"

1218 JOIN "initiative" AS "losing_initiative"

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

1220 AND "losing_initiative"."agreed"

1221 LEFT JOIN "vote" AS "better_vote"

1222 ON "direct_voter"."member_id" = "better_vote"."member_id"

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

1224 LEFT JOIN "vote" AS "worse_vote"

1225 ON "direct_voter"."member_id" = "worse_vote"."member_id"

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

1227 WHERE

1228 "winning_initiative"."id" != "losing_initiative"."id"

1229 GROUP BY

1230 "issue"."id",

1231 "winning_initiative"."id",

1232 "losing_initiative"."id";

1233

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

1235

1236

1237 CREATE VIEW "expired_session" AS

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

1239

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

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

1242

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

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

1245

1246

1247 CREATE VIEW "open_issue" AS

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

1249

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

1251

1252

1253 CREATE VIEW "issue_with_ranks_missing" AS

1254 SELECT * FROM "issue"

1255 WHERE "fully_frozen" NOTNULL

1256 AND "closed" NOTNULL

1257 AND "ranks_available" = FALSE;

1258

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

1260

1261

1262 CREATE VIEW "member_contingent" AS

1263 SELECT

1264 "member"."id" AS "member_id",

1265 "contingent"."time_frame",

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

1267 (

1268 SELECT count(1) FROM "draft"

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

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

1271 ) + (

1272 SELECT count(1) FROM "suggestion"

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

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

1275 )

1276 ELSE NULL END AS "text_entry_count",

1277 "contingent"."text_entry_limit",

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

1279 SELECT count(1) FROM "opening_draft"

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

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

1282 ) ELSE NULL END AS "initiative_count",

1283 "contingent"."initiative_limit"

1284 FROM "member" CROSS JOIN "contingent";

1285

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

1287

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

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

1290

1291

1292 CREATE VIEW "member_contingent_left" AS

1293 SELECT

1294 "member_id",

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

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

1297 FROM "member_contingent" GROUP BY "member_id";

1298

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

1300

1301

1302

1303 --------------------------------------------------

1304 -- Set returning function for delegation chains --

1305 --------------------------------------------------

1306

1307

1308 CREATE TYPE "delegation_chain_loop_tag" AS ENUM

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

1310

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

1312

1313

1314 CREATE TYPE "delegation_chain_row" AS (

1315 "index" INT4,

1316 "member_id" INT4,

1317 "member_active" BOOLEAN,

1318 "participation" BOOLEAN,

1319 "overridden" BOOLEAN,

1320 "scope_in" "delegation_scope",

1321 "scope_out" "delegation_scope",

1322 "loop" "delegation_chain_loop_tag" );

1323

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

1325

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

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

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

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

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

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

1332

1333

1334 CREATE FUNCTION "delegation_chain"

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

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

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

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

1339 RETURNS SETOF "delegation_chain_row"

1340 LANGUAGE 'plpgsql' STABLE AS $$

1341 DECLARE

1342 "issue_row" "issue"%ROWTYPE;

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

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

1345 "output_row" "delegation_chain_row";

1346 "output_rows" "delegation_chain_row"[];

1347 "delegation_row" "delegation"%ROWTYPE;

1348 "row_count" INT4;

1349 "i" INT4;

1350 "loop_v" BOOLEAN;

1351 BEGIN

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

1353 "visited_member_ids" := '{}';

1354 "loop_member_id_v" := NULL;

1355 "output_rows" := '{}';

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

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

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

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

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

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

1362 LOOP

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

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

1365 ELSE

1366 "visited_member_ids" :=

1367 "visited_member_ids" || "output_row"."member_id";

1368 END IF;

1369 IF "output_row"."participation" THEN

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

1371 END IF;

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

1373 IF EXISTS (

1374 SELECT NULL FROM "member"

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

1376 ) THEN

1377 IF "area_id_p" ISNULL AND "issue_id_p" ISNULL THEN

1378 SELECT * INTO "delegation_row" FROM "delegation"

1379 WHERE "truster_id" = "output_row"."member_id"

1380 AND "scope" = 'global';

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

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

1383 SELECT NULL FROM "membership"

1384 WHERE "area_id" = "area_id_p"

1385 AND "member_id" = "output_row"."member_id"

1386 );

1387 SELECT * INTO "delegation_row" FROM "delegation"

1388 WHERE "truster_id" = "output_row"."member_id"

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

1390 ORDER BY "scope" DESC;

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

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

1393 SELECT NULL FROM "interest"

1394 WHERE "issue_id" = "issue_id_p"

1395 AND "member_id" = "output_row"."member_id"

1396 );

1397 SELECT * INTO "delegation_row" FROM "delegation"

1398 WHERE "truster_id" = "output_row"."member_id"

1399 AND ("scope" = 'global' OR

1400 "area_id" = "issue_row"."area_id" OR

1401 "issue_id" = "issue_id_p"

1402 )

1403 ORDER BY "scope" DESC;

1404 ELSE

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

1406 END IF;

1407 ELSE

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

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

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

1411 "delegation_row" := ROW(NULL);

1412 END IF;

1413 IF

1414 "output_row"."member_id" = "member_id_p" AND

1415 "simulate_trustee_id_p" NOTNULL

1416 THEN

1417 "output_row"."scope_out" := CASE

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

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

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

1421 END;

1422 "output_rows" := "output_rows" || "output_row";

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

1424 ELSIF "delegation_row"."trustee_id" NOTNULL THEN

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

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

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

1428 ELSE

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

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

1431 EXIT;

1432 END IF;

1433 EXIT WHEN "loop_member_id_v" NOTNULL;

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

1435 END LOOP;

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

1437 "i" := 1;

1438 "loop_v" := FALSE;

1439 LOOP

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

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

1442 IF "loop_v" THEN

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

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

1445 ELSIF "i" = "row_count" THEN

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

1447 ELSE

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

1449 END IF;

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

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

1452 "loop_v" := TRUE;

1453 END IF;

1454 IF "area_id_p" ISNULL AND "issue_id_p" ISNULL THEN

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

1456 END IF;

1457 RETURN NEXT "output_row";

1458 "i" := "i" + 1;

1459 END LOOP;

1460 RETURN;

1461 END;

1462 $$;

1463

1464 COMMENT ON FUNCTION "delegation_chain"

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

1466 "area"."id"%TYPE,

1467 "issue"."id"%TYPE,

1468 "member"."id"%TYPE )

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

1470

1471 CREATE FUNCTION "delegation_chain"

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

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

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

1475 RETURNS SETOF "delegation_chain_row"

1476 LANGUAGE 'plpgsql' STABLE AS $$

1477 DECLARE

1478 "result_row" "delegation_chain_row";

1479 BEGIN

1480 FOR "result_row" IN

1481 SELECT * FROM "delegation_chain"(

1482 "member_id_p", "area_id_p", "issue_id_p", NULL

1483 )

1484 LOOP

1485 RETURN NEXT "result_row";

1486 END LOOP;

1487 RETURN;

1488 END;

1489 $$;

1490

1491 COMMENT ON FUNCTION "delegation_chain"

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

1493 "area"."id"%TYPE,

1494 "issue"."id"%TYPE )

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

1496

1497

1498

1499 ------------------------------

1500 -- Comparison by vote count --

1501 ------------------------------

1502

1503 CREATE FUNCTION "vote_ratio"

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

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

1506 RETURNS FLOAT8

1507 LANGUAGE 'plpgsql' STABLE AS $$

1508 DECLARE

1509 "total_v" INT4;

1510 BEGIN

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

1512 IF "total_v" > 0 THEN

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

1514 ELSE

1515 RETURN 0.5;

1516 END IF;

1517 END;

1518 $$;

1519

1520 COMMENT ON FUNCTION "vote_ratio"

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

1522 "initiative"."negative_votes"%TYPE )

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

1524

1525

1526

1527 ------------------------------------------------

1528 -- Locking for snapshots and voting procedure --

1529 ------------------------------------------------

1530

1531 CREATE FUNCTION "global_lock"() RETURNS VOID

1532 LANGUAGE 'plpgsql' VOLATILE AS $$

1533 BEGIN

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

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

1536 LOCK TABLE "member" IN EXCLUSIVE MODE;

1537 LOCK TABLE "area" IN EXCLUSIVE MODE;

1538 LOCK TABLE "membership" IN EXCLUSIVE MODE;

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

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

1541 LOCK TABLE "policy" IN EXCLUSIVE MODE;

1542 LOCK TABLE "issue" IN EXCLUSIVE MODE;

1543 LOCK TABLE "initiative" IN EXCLUSIVE MODE;

1544 LOCK TABLE "draft" IN EXCLUSIVE MODE;

1545 LOCK TABLE "suggestion" IN EXCLUSIVE MODE;

1546 LOCK TABLE "interest" IN EXCLUSIVE MODE;

1547 LOCK TABLE "initiator" IN EXCLUSIVE MODE;

1548 LOCK TABLE "supporter" IN EXCLUSIVE MODE;

1549 LOCK TABLE "opinion" IN EXCLUSIVE MODE;

1550 LOCK TABLE "delegation" IN EXCLUSIVE MODE;

1551 LOCK TABLE "direct_population_snapshot" IN EXCLUSIVE MODE;

1552 LOCK TABLE "delegating_population_snapshot" IN EXCLUSIVE MODE;

1553 LOCK TABLE "direct_interest_snapshot" IN EXCLUSIVE MODE;

1554 LOCK TABLE "delegating_interest_snapshot" IN EXCLUSIVE MODE;

1555 LOCK TABLE "direct_supporter_snapshot" IN EXCLUSIVE MODE;

1556 LOCK TABLE "direct_voter" IN EXCLUSIVE MODE;

1557 LOCK TABLE "delegating_voter" IN EXCLUSIVE MODE;

1558 LOCK TABLE "vote" IN EXCLUSIVE MODE;

1559 RETURN;

1560 END;

1561 $$;

1562

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

1564

1565

1566

1567 -------------------------------

1568 -- Materialize member counts --

1569 -------------------------------

1570

1571 CREATE FUNCTION "calculate_member_counts"()

1572 RETURNS VOID

1573 LANGUAGE 'plpgsql' VOLATILE AS $$

1574 BEGIN

1575 LOCK TABLE "member" IN EXCLUSIVE MODE;

1576 LOCK TABLE "area" IN EXCLUSIVE MODE;

1577 LOCK TABLE "membership" IN EXCLUSIVE MODE;

1578 DELETE FROM "member_count";

1579 INSERT INTO "member_count" ("total_count")

1580 SELECT "total_count" FROM "member_count_view";

1581 UPDATE "area" SET

1582 "direct_member_count" = "view"."direct_member_count",

1583 "member_weight" = "view"."member_weight",

1584 "autoreject_weight" = "view"."autoreject_weight"

1585 FROM "area_member_count" AS "view"

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

1587 RETURN;

1588 END;

1589 $$;

1590

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

1592

1593

1594

1595 ------------------------------

1596 -- Calculation of snapshots --

1597 ------------------------------

1598

1599 CREATE FUNCTION "weight_of_added_delegations_for_population_snapshot"

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

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

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

1603 RETURNS "direct_population_snapshot"."weight"%TYPE

1604 LANGUAGE 'plpgsql' VOLATILE AS $$

1605 DECLARE

1606 "issue_delegation_row" "issue_delegation"%ROWTYPE;

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

1608 "weight_v" INT4;

1609 "sub_weight_v" INT4;

1610 BEGIN

1611 "weight_v" := 0;

1612 FOR "issue_delegation_row" IN

1613 SELECT * FROM "issue_delegation"

1614 WHERE "trustee_id" = "member_id_p"

1615 AND "issue_id" = "issue_id_p"

1616 LOOP

1617 IF NOT EXISTS (

1618 SELECT NULL FROM "direct_population_snapshot"

1619 WHERE "issue_id" = "issue_id_p"

1620 AND "event" = 'periodic'

1621 AND "member_id" = "issue_delegation_row"."truster_id"

1622 ) AND NOT EXISTS (

1623 SELECT NULL FROM "delegating_population_snapshot"

1624 WHERE "issue_id" = "issue_id_p"

1625 AND "event" = 'periodic'

1626 AND "member_id" = "issue_delegation_row"."truster_id"

1627 ) THEN

1628 "delegate_member_ids_v" :=

1629 "member_id_p" || "delegate_member_ids_p";

1630 INSERT INTO "delegating_population_snapshot" (

1631 "issue_id",

1632 "event",

1633 "member_id",

1634 "scope",

1635 "delegate_member_ids"

1636 ) VALUES (

1637 "issue_id_p",

1638 'periodic',

1639 "issue_delegation_row"."truster_id",

1640 "issue_delegation_row"."scope",

1641 "delegate_member_ids_v"

1642 );

1643 "sub_weight_v" := 1 +

1644 "weight_of_added_delegations_for_population_snapshot"(

1645 "issue_id_p",

1646 "issue_delegation_row"."truster_id",

1647 "delegate_member_ids_v"

1648 );

1649 UPDATE "delegating_population_snapshot"

1650 SET "weight" = "sub_weight_v"

1651 WHERE "issue_id" = "issue_id_p"

1652 AND "event" = 'periodic'

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

1654 "weight_v" := "weight_v" + "sub_weight_v";

1655 END IF;

1656 END LOOP;

1657 RETURN "weight_v";

1658 END;

1659 $$;

1660

1661 COMMENT ON FUNCTION "weight_of_added_delegations_for_population_snapshot"

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

1663 "member"."id"%TYPE,

1664 "delegating_population_snapshot"."delegate_member_ids"%TYPE )

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

1666

1667

1668 CREATE FUNCTION "create_population_snapshot"

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

1670 RETURNS VOID

1671 LANGUAGE 'plpgsql' VOLATILE AS $$

1672 DECLARE

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

1674 BEGIN

1675 DELETE FROM "direct_population_snapshot"

1676 WHERE "issue_id" = "issue_id_p"

1677 AND "event" = 'periodic';

1678 DELETE FROM "delegating_population_snapshot"

1679 WHERE "issue_id" = "issue_id_p"

1680 AND "event" = 'periodic';

1681 INSERT INTO "direct_population_snapshot"

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

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

1684 "issue_id_p" AS "issue_id",

1685 'periodic' AS "event",

1686 "subquery"."member_id",

1687 "subquery"."interest_exists"

1688 FROM (

1689 SELECT

1690 "member"."id" AS "member_id",

1691 FALSE AS "interest_exists"

1692 FROM "issue"

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

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

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

1696 WHERE "issue"."id" = "issue_id_p"

1697 AND "member"."active"

1698 UNION

1699 SELECT

1700 "member"."id" AS "member_id",

1701 TRUE AS "interest_exists"

1702 FROM "interest" JOIN "member"

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

1704 WHERE "interest"."issue_id" = "issue_id_p"

1705 AND "member"."active"

1706 ) AS "subquery"

1707 ORDER BY

1708 "issue_id_p",

1709 "subquery"."member_id",

1710 "subquery"."interest_exists" DESC;

1711 FOR "member_id_v" IN

1712 SELECT "member_id" FROM "direct_population_snapshot"

1713 WHERE "issue_id" = "issue_id_p"

1714 AND "event" = 'periodic'

1715 LOOP

1716 UPDATE "direct_population_snapshot" SET

1717 "weight" = 1 +

1718 "weight_of_added_delegations_for_population_snapshot"(

1719 "issue_id_p",

1720 "member_id_v",

1721 '{}'

1722 )

1723 WHERE "issue_id" = "issue_id_p"

1724 AND "event" = 'periodic'

1725 AND "member_id" = "member_id_v";

1726 END LOOP;

1727 RETURN;

1728 END;

1729 $$;

1730

1731 COMMENT ON FUNCTION "create_population_snapshot"

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

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

1734

1735

1736 CREATE FUNCTION "weight_of_added_delegations_for_interest_snapshot"

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

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

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

1740 RETURNS "direct_interest_snapshot"."weight"%TYPE

1741 LANGUAGE 'plpgsql' VOLATILE AS $$

1742 DECLARE

1743 "issue_delegation_row" "issue_delegation"%ROWTYPE;

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

1745 "weight_v" INT4;

1746 "sub_weight_v" INT4;

1747 BEGIN

1748 "weight_v" := 0;

1749 FOR "issue_delegation_row" IN

1750 SELECT * FROM "issue_delegation"

1751 WHERE "trustee_id" = "member_id_p"

1752 AND "issue_id" = "issue_id_p"

1753 LOOP

1754 IF NOT EXISTS (

1755 SELECT NULL FROM "direct_interest_snapshot"

1756 WHERE "issue_id" = "issue_id_p"

1757 AND "event" = 'periodic'

1758 AND "member_id" = "issue_delegation_row"."truster_id"

1759 ) AND NOT EXISTS (

1760 SELECT NULL FROM "delegating_interest_snapshot"

1761 WHERE "issue_id" = "issue_id_p"

1762 AND "event" = 'periodic'

1763 AND "member_id" = "issue_delegation_row"."truster_id"

1764 ) THEN

1765 "delegate_member_ids_v" :=

1766 "member_id_p" || "delegate_member_ids_p";

1767 INSERT INTO "delegating_interest_snapshot" (

1768 "issue_id",

1769 "event",

1770 "member_id",

1771 "scope",

1772 "delegate_member_ids"

1773 ) VALUES (

1774 "issue_id_p",

1775 'periodic',

1776 "issue_delegation_row"."truster_id",

1777 "issue_delegation_row"."scope",

1778 "delegate_member_ids_v"

1779 );

1780 "sub_weight_v" := 1 +

1781 "weight_of_added_delegations_for_interest_snapshot"(

1782 "issue_id_p",

1783 "issue_delegation_row"."truster_id",

1784 "delegate_member_ids_v"

1785 );

1786 UPDATE "delegating_interest_snapshot"

1787 SET "weight" = "sub_weight_v"

1788 WHERE "issue_id" = "issue_id_p"

1789 AND "event" = 'periodic'

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

1791 "weight_v" := "weight_v" + "sub_weight_v";

1792 END IF;

1793 END LOOP;

1794 RETURN "weight_v";

1795 END;

1796 $$;

1797

1798 COMMENT ON FUNCTION "weight_of_added_delegations_for_interest_snapshot"

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

1800 "member"."id"%TYPE,

1801 "delegating_interest_snapshot"."delegate_member_ids"%TYPE )

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

1803

1804

1805 CREATE FUNCTION "create_interest_snapshot"

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

1807 RETURNS VOID

1808 LANGUAGE 'plpgsql' VOLATILE AS $$

1809 DECLARE

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

1811 BEGIN

1812 DELETE FROM "direct_interest_snapshot"

1813 WHERE "issue_id" = "issue_id_p"

1814 AND "event" = 'periodic';

1815 DELETE FROM "delegating_interest_snapshot"

1816 WHERE "issue_id" = "issue_id_p"

1817 AND "event" = 'periodic';

1818 DELETE FROM "direct_supporter_snapshot"

1819 WHERE "issue_id" = "issue_id_p"

1820 AND "event" = 'periodic';

1821 INSERT INTO "direct_interest_snapshot"

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

1823 SELECT

1824 "issue_id_p" AS "issue_id",

1825 'periodic' AS "event",

1826 "member"."id" AS "member_id",

1827 "interest"."voting_requested"

1828 FROM "interest" JOIN "member"

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

1830 WHERE "interest"."issue_id" = "issue_id_p"

1831 AND "member"."active";

1832 FOR "member_id_v" IN

1833 SELECT "member_id" FROM "direct_interest_snapshot"

1834 WHERE "issue_id" = "issue_id_p"

1835 AND "event" = 'periodic'

1836 LOOP

1837 UPDATE "direct_interest_snapshot" SET

1838 "weight" = 1 +

1839 "weight_of_added_delegations_for_interest_snapshot"(

1840 "issue_id_p",

1841 "member_id_v",

1842 '{}'

1843 )

1844 WHERE "issue_id" = "issue_id_p"

1845 AND "event" = 'periodic'

1846 AND "member_id" = "member_id_v";

1847 END LOOP;

1848 INSERT INTO "direct_supporter_snapshot"

1849 ( "issue_id", "initiative_id", "event", "member_id",

1850 "informed", "satisfied" )

1851 SELECT

1852 "issue_id_p" AS "issue_id",

1853 "initiative"."id" AS "initiative_id",

1854 'periodic' AS "event",

1855 "member"."id" AS "member_id",

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

1857 NOT EXISTS (

1858 SELECT NULL FROM "critical_opinion"

1859 WHERE "initiative_id" = "initiative"."id"

1860 AND "member_id" = "member"."id"

1861 ) AS "satisfied"

1862 FROM "supporter"

1863 JOIN "member"

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

1865 JOIN "initiative"

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

1867 JOIN "current_draft"

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

1869 JOIN "direct_interest_snapshot"

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

1871 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"

1872 AND "event" = 'periodic'

1873 WHERE "member"."active"

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

1875 RETURN;

1876 END;

1877 $$;

1878

1879 COMMENT ON FUNCTION "create_interest_snapshot"

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

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

1882

1883

1884 CREATE FUNCTION "create_snapshot"

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

1886 RETURNS VOID

1887 LANGUAGE 'plpgsql' VOLATILE AS $$

1888 DECLARE

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

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

1891 BEGIN

1892 PERFORM "global_lock"();

1893 PERFORM "create_population_snapshot"("issue_id_p");

1894 PERFORM "create_interest_snapshot"("issue_id_p");

1895 UPDATE "issue" SET

1896 "snapshot" = now(),

1897 "latest_snapshot_event" = 'periodic',

1898 "population" = (

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

1900 FROM "direct_population_snapshot"

1901 WHERE "issue_id" = "issue_id_p"

1902 AND "event" = 'periodic'

1903 ),

1904 "vote_now" = (

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

1906 FROM "direct_interest_snapshot"

1907 WHERE "issue_id" = "issue_id_p"

1908 AND "event" = 'periodic'

1909 AND "voting_requested" = TRUE

1910 ),

1911 "vote_later" = (

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

1913 FROM "direct_interest_snapshot"

1914 WHERE "issue_id" = "issue_id_p"

1915 AND "event" = 'periodic'

1916 AND "voting_requested" = FALSE

1917 )

1918 WHERE "id" = "issue_id_p";

1919 FOR "initiative_id_v" IN

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

1921 LOOP

1922 UPDATE "initiative" SET

1923 "supporter_count" = (

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

1925 FROM "direct_interest_snapshot" AS "di"

1926 JOIN "direct_supporter_snapshot" AS "ds"

1927 ON "di"."member_id" = "ds"."member_id"

1928 WHERE "di"."issue_id" = "issue_id_p"

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

1930 AND "ds"."initiative_id" = "initiative_id_v"

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

1932 ),

1933 "informed_supporter_count" = (

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

1935 FROM "direct_interest_snapshot" AS "di"

1936 JOIN "direct_supporter_snapshot" AS "ds"

1937 ON "di"."member_id" = "ds"."member_id"

1938 WHERE "di"."issue_id" = "issue_id_p"

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

1940 AND "ds"."initiative_id" = "initiative_id_v"

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

1942 AND "ds"."informed"

1943 ),

1944 "satisfied_supporter_count" = (

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

1946 FROM "direct_interest_snapshot" AS "di"

1947 JOIN "direct_supporter_snapshot" AS "ds"

1948 ON "di"."member_id" = "ds"."member_id"

1949 WHERE "di"."issue_id" = "issue_id_p"

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

1951 AND "ds"."initiative_id" = "initiative_id_v"

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

1953 AND "ds"."satisfied"

1954 ),

1955 "satisfied_informed_supporter_count" = (

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

1957 FROM "direct_interest_snapshot" AS "di"

1958 JOIN "direct_supporter_snapshot" AS "ds"

1959 ON "di"."member_id" = "ds"."member_id"

1960 WHERE "di"."issue_id" = "issue_id_p"

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

1962 AND "ds"."initiative_id" = "initiative_id_v"

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

1964 AND "ds"."informed"

1965 AND "ds"."satisfied"

1966 )

1967 WHERE "id" = "initiative_id_v";

1968 FOR "suggestion_id_v" IN

1969 SELECT "id" FROM "suggestion"

1970 WHERE "initiative_id" = "initiative_id_v"

1971 LOOP

1972 UPDATE "suggestion" SET

1973 "minus2_unfulfilled_count" = (

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

1975 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"

1976 ON "opinion"."member_id" = "snapshot"."member_id"

1977 WHERE "opinion"."suggestion_id" = "suggestion_id_v"

1978 AND "snapshot"."issue_id" = "issue_id_p"

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

1980 AND "opinion"."fulfilled" = FALSE

1981 ),

1982 "minus2_fulfilled_count" = (

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

1984 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"

1985 ON "opinion"."member_id" = "snapshot"."member_id"

1986 WHERE "opinion"."suggestion_id" = "suggestion_id_v"

1987 AND "snapshot"."issue_id" = "issue_id_p"

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

1989 AND "opinion"."fulfilled" = TRUE

1990 ),

1991 "minus1_unfulfilled_count" = (

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

1993 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"

1994 ON "opinion"."member_id" = "snapshot"."member_id"

1995 WHERE "opinion"."suggestion_id" = "suggestion_id_v"

1996 AND "snapshot"."issue_id" = "issue_id_p"

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

1998 AND "opinion"."fulfilled" = FALSE

1999 ),

2000 "minus1_fulfilled_count" = (

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

2002 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"

2003 ON "opinion"."member_id" = "snapshot"."member_id"

2004 WHERE "opinion"."suggestion_id" = "suggestion_id_v"

2005 AND "snapshot"."issue_id" = "issue_id_p"

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

2007 AND "opinion"."fulfilled" = TRUE

2008 ),

2009 "plus1_unfulfilled_count" = (

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

2011 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"

2012 ON "opinion"."member_id" = "snapshot"."member_id"

2013 WHERE "opinion"."suggestion_id" = "suggestion_id_v"

2014 AND "snapshot"."issue_id" = "issue_id_p"

2015 AND "opinion"."degree" = 1

2016 AND "opinion"."fulfilled" = FALSE

2017 ),

2018 "plus1_fulfilled_count" = (

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

2020 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"

2021 ON "opinion"."member_id" = "snapshot"."member_id"

2022 WHERE "opinion"."suggestion_id" = "suggestion_id_v"

2023 AND "snapshot"."issue_id" = "issue_id_p"

2024 AND "opinion"."degree" = 1

2025 AND "opinion"."fulfilled" = TRUE

2026 ),

2027 "plus2_unfulfilled_count" = (

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

2029 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"

2030 ON "opinion"."member_id" = "snapshot"."member_id"

2031 WHERE "opinion"."suggestion_id" = "suggestion_id_v"

2032 AND "snapshot"."issue_id" = "issue_id_p"

2033 AND "opinion"."degree" = 2

2034 AND "opinion"."fulfilled" = FALSE

2035 ),

2036 "plus2_fulfilled_count" = (

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

2038 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"

2039 ON "opinion"."member_id" = "snapshot"."member_id"

2040 WHERE "opinion"."suggestion_id" = "suggestion_id_v"

2041 AND "snapshot"."issue_id" = "issue_id_p"

2042 AND "opinion"."degree" = 2

2043 AND "opinion"."fulfilled" = TRUE

2044 )

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

2046 END LOOP;

2047 END LOOP;

2048 RETURN;

2049 END;

2050 $$;

2051

2052 COMMENT ON FUNCTION "create_snapshot"

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

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

2055

2056

2057 CREATE FUNCTION "set_snapshot_event"

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

2059 "event_p" "snapshot_event" )

2060 RETURNS VOID

2061 LANGUAGE 'plpgsql' VOLATILE AS $$

2062 BEGIN

2063 UPDATE "issue" SET "latest_snapshot_event" = "event_p"

2064 WHERE "id" = "issue_id_p";

2065 UPDATE "direct_population_snapshot" SET "event" = "event_p"

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

2067 UPDATE "delegating_population_snapshot" SET "event" = "event_p"

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

2069 UPDATE "direct_interest_snapshot" SET "event" = "event_p"

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

2071 UPDATE "delegating_interest_snapshot" SET "event" = "event_p"

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

2073 UPDATE "direct_supporter_snapshot" SET "event" = "event_p"

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

2075 RETURN;

2076 END;

2077 $$;

2078

2079 COMMENT ON FUNCTION "set_snapshot_event"

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

2081 "snapshot_event" )

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

2083

2084

2085

2086 ---------------------

2087 -- Freezing issues --

2088 ---------------------

2089

2090 CREATE FUNCTION "freeze_after_snapshot"

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

2092 RETURNS VOID

2093 LANGUAGE 'plpgsql' VOLATILE AS $$

2094 DECLARE

2095 "issue_row" "issue"%ROWTYPE;

2096 "policy_row" "policy"%ROWTYPE;

2097 "initiative_row" "initiative"%ROWTYPE;

2098 BEGIN

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

2100 SELECT * INTO "policy_row"

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

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

2103 UPDATE "issue" SET

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

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

2106 "fully_frozen" = now()

2107 WHERE "id" = "issue_id_p";

2108 FOR "initiative_row" IN

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

2110 LOOP

2111 IF

2112 "initiative_row"."satisfied_supporter_count" > 0 AND

2113 "initiative_row"."satisfied_supporter_count" *

2114 "policy_row"."initiative_quorum_den" >=

2115 "issue_row"."population" * "policy_row"."initiative_quorum_num"

2116 THEN

2117 UPDATE "initiative" SET "admitted" = TRUE

2118 WHERE "id" = "initiative_row"."id";

2119 ELSE

2120 UPDATE "initiative" SET "admitted" = FALSE

2121 WHERE "id" = "initiative_row"."id";

2122 END IF;

2123 END LOOP;

2124 IF NOT EXISTS (

2125 SELECT NULL FROM "initiative"

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

2127 ) THEN

2128 PERFORM "close_voting"("issue_id_p");

2129 END IF;

2130 RETURN;

2131 END;

2132 $$;

2133

2134 COMMENT ON FUNCTION "freeze_after_snapshot"

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

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

2137

2138

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

2140 RETURNS VOID

2141 LANGUAGE 'plpgsql' VOLATILE AS $$

2142 DECLARE

2143 "issue_row" "issue"%ROWTYPE;

2144 BEGIN

2145 PERFORM "create_snapshot"("issue_id_p");

2146 PERFORM "freeze_after_snapshot"("issue_id_p");

2147 RETURN;

2148 END;

2149 $$;

2150

2151 COMMENT ON FUNCTION "freeze_after_snapshot"

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

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

2154

2155

2156

2157 -----------------------

2158 -- Counting of votes --

2159 -----------------------

2160

2161

2162 CREATE FUNCTION "weight_of_added_vote_delegations"

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

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

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

2166 RETURNS "direct_voter"."weight"%TYPE

2167 LANGUAGE 'plpgsql' VOLATILE AS $$

2168 DECLARE

2169 "issue_delegation_row" "issue_delegation"%ROWTYPE;

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

2171 "weight_v" INT4;

2172 "sub_weight_v" INT4;

2173 BEGIN

2174 "weight_v" := 0;

2175 FOR "issue_delegation_row" IN

2176 SELECT * FROM "issue_delegation"

2177 WHERE "trustee_id" = "member_id_p"

2178 AND "issue_id" = "issue_id_p"

2179 LOOP

2180 IF NOT EXISTS (

2181 SELECT NULL FROM "direct_voter"

2182 WHERE "member_id" = "issue_delegation_row"."truster_id"

2183 AND "issue_id" = "issue_id_p"

2184 ) AND NOT EXISTS (

2185 SELECT NULL FROM "delegating_voter"

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

2187 AND "issue_id" = "issue_id_p"

2188 ) THEN

2189 "delegate_member_ids_v" :=

2190 "member_id_p" || "delegate_member_ids_p";

2191 INSERT INTO "delegating_voter" (

2192 "issue_id",

2193 "member_id",

2194 "scope",

2195 "delegate_member_ids"

2196 ) VALUES (

2197 "issue_id_p",

2198 "issue_delegation_row"."truster_id",

2199 "issue_delegation_row"."scope",

2200 "delegate_member_ids_v"

2201 );

2202 "sub_weight_v" := 1 +

2203 "weight_of_added_vote_delegations"(

2204 "issue_id_p",

2205 "issue_delegation_row"."truster_id",

2206 "delegate_member_ids_v"

2207 );

2208 UPDATE "delegating_voter"

2209 SET "weight" = "sub_weight_v"

2210 WHERE "issue_id" = "issue_id_p"

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

2212 "weight_v" := "weight_v" + "sub_weight_v";

2213 END IF;

2214 END LOOP;

2215 RETURN "weight_v";

2216 END;

2217 $$;

2218

2219 COMMENT ON FUNCTION "weight_of_added_vote_delegations"

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

2221 "member"."id"%TYPE,

2222 "delegating_voter"."delegate_member_ids"%TYPE )

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

2224

2225

2226 CREATE FUNCTION "add_vote_delegations"

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

2228 RETURNS VOID

2229 LANGUAGE 'plpgsql' VOLATILE AS $$

2230 DECLARE

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

2232 BEGIN

2233 FOR "member_id_v" IN

2234 SELECT "member_id" FROM "direct_voter"

2235 WHERE "issue_id" = "issue_id_p"

2236 LOOP

2237 UPDATE "direct_voter" SET

2238 "weight" = "weight" + "weight_of_added_vote_delegations"(

2239 "issue_id_p",

2240 "member_id_v",

2241 '{}'

2242 )

2243 WHERE "member_id" = "member_id_v"

2244 AND "issue_id" = "issue_id_p";

2245 END LOOP;

2246 RETURN;

2247 END;

2248 $$;

2249

2250 COMMENT ON FUNCTION "add_vote_delegations"

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

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

2253

2254

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

2256 RETURNS VOID

2257 LANGUAGE 'plpgsql' VOLATILE AS $$

2258 DECLARE

2259 "issue_row" "issue"%ROWTYPE;

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

2261 BEGIN

2262 PERFORM "global_lock"();

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

2264 DELETE FROM "delegating_voter"

2265 WHERE "issue_id" = "issue_id_p";

2266 DELETE FROM "direct_voter"

2267 WHERE "issue_id" = "issue_id_p"

2268 AND "autoreject" = TRUE;

2269 DELETE FROM "direct_voter" USING "member"

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

2271 AND "direct_voter"."issue_id" = "issue_id_p"

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

2273 UPDATE "direct_voter" SET "weight" = 1

2274 WHERE "issue_id" = "issue_id_p";

2275 PERFORM "add_vote_delegations"("issue_id_p");

2276 FOR "member_id_v" IN

2277 SELECT "interest"."member_id"

2278 FROM "interest"

2279 LEFT JOIN "direct_voter"

2280 ON "interest"."member_id" = "direct_voter"."member_id"

2281 AND "interest"."issue_id" = "direct_voter"."issue_id"

2282 LEFT JOIN "delegating_voter"

2283 ON "interest"."member_id" = "delegating_voter"."member_id"

2284 AND "interest"."issue_id" = "delegating_voter"."issue_id"

2285 WHERE "interest"."issue_id" = "issue_id_p"

2286 AND "interest"."autoreject" = TRUE

2287 AND "direct_voter"."member_id" ISNULL

2288 AND "delegating_voter"."member_id" ISNULL

2289 UNION SELECT "membership"."member_id"

2290 FROM "membership"

2291 LEFT JOIN "interest"

2292 ON "membership"."member_id" = "interest"."member_id"

2293 AND "interest"."issue_id" = "issue_id_p"

2294 LEFT JOIN "direct_voter"

2295 ON "membership"."member_id" = "direct_voter"."member_id"

2296 AND "direct_voter"."issue_id" = "issue_id_p"

2297 LEFT JOIN "delegating_voter"

2298 ON "membership"."member_id" = "delegating_voter"."member_id"

2299 AND "delegating_voter"."issue_id" = "issue_id_p"

2300 WHERE "membership"."area_id" = "issue_row"."area_id"

2301 AND "membership"."autoreject" = TRUE

2302 AND "interest"."autoreject" ISNULL

2303 AND "direct_voter"."member_id" ISNULL

2304 AND "delegating_voter"."member_id" ISNULL

2305 LOOP

2306 INSERT INTO "direct_voter" ("member_id", "issue_id", "autoreject")

2307 VALUES ("member_id_v", "issue_id_p", TRUE);

2308 INSERT INTO "vote" (

2309 "member_id",

2310 "issue_id",

2311 "initiative_id",

2312 "grade"

2313 ) SELECT

2314 "member_id_v" AS "member_id",

2315 "issue_id_p" AS "issue_id",

2316 "id" AS "initiative_id",

2317 -1 AS "grade"

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

2319 END LOOP;

2320 PERFORM "add_vote_delegations"("issue_id_p");

2321 UPDATE "issue" SET

2322 "voter_count" = (

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

2324 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"

2325 )

2326 WHERE "id" = "issue_id_p";

2327 UPDATE "initiative" SET

2328 "positive_votes" = "vote_counts"."positive_votes",

2329 "negative_votes" = "vote_counts"."negative_votes",

2330 "agreed" = CASE WHEN "majority_strict" THEN

2331 "vote_counts"."positive_votes" * "majority_den" >

2332 "majority_num" *

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

2334 ELSE

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

2336 "majority_num" *

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

2338 END

2339 FROM

2340 ( SELECT

2341 "initiative"."id" AS "initiative_id",

2342 coalesce(

2343 sum(

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

2345 ),

2346 0

2347 ) AS "positive_votes",

2348 coalesce(

2349 sum(

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

2351 ),

2352 0

2353 ) AS "negative_votes"

2354 FROM "initiative"

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

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

2357 LEFT JOIN "direct_voter"

2358 ON "direct_voter"."issue_id" = "initiative"."issue_id"

2359 LEFT JOIN "vote"

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

2361 AND "vote"."member_id" = "direct_voter"."member_id"

2362 WHERE "initiative"."issue_id" = "issue_id_p"

2363 AND "initiative"."admitted"

2364 GROUP BY "initiative"."id"

2365 ) AS "vote_counts",

2366 "issue",

2367 "policy"

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

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

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

2371 UPDATE "issue" SET "closed" = now() WHERE "id" = "issue_id_p";

2372 END;

2373 $$;

2374

2375 COMMENT ON FUNCTION "close_voting"

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

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

2378

2379

2380 CREATE FUNCTION "init_array"("dim_p" INTEGER)

2381 RETURNS INT4[]

2382 LANGUAGE 'plpgsql' IMMUTABLE AS $$

2383 DECLARE

2384 "i" INTEGER;

2385 "ary_text_v" TEXT;

2386 BEGIN

2387 IF "dim_p" >= 1 THEN

2388 "ary_text_v" := '{NULL';

2389 "i" := "dim_p";

2390 LOOP

2391 "i" := "i" - 1;

2392 EXIT WHEN "i" = 0;

2393 "ary_text_v" := "ary_text_v" || ',NULL';

2394 END LOOP;

2395 "ary_text_v" := "ary_text_v" || '}';

2396 RETURN "ary_text_v"::INT4[][];

2397 ELSE

2398 RAISE EXCEPTION 'Dimension needs to be at least 1.';

2399 END IF;

2400 END;

2401 $$;

2402

2403 COMMENT ON FUNCTION "init_array"(INTEGER) IS 'Needed for PostgreSQL < 8.4, due to missing "array_fill" function';

2404

2405

2406 CREATE FUNCTION "init_square_matrix"("dim_p" INTEGER)

2407 RETURNS INT4[][]

2408 LANGUAGE 'plpgsql' IMMUTABLE AS $$

2409 DECLARE

2410 "i" INTEGER;

2411 "row_text_v" TEXT;

2412 "ary_text_v" TEXT;

2413 BEGIN

2414 IF "dim_p" >= 1 THEN

2415 "row_text_v" := '{NULL';

2416 "i" := "dim_p";

2417 LOOP

2418 "i" := "i" - 1;

2419 EXIT WHEN "i" = 0;

2420 "row_text_v" := "row_text_v" || ',NULL';

2421 END LOOP;

2422 "row_text_v" := "row_text_v" || '}';

2423 "ary_text_v" := '{' || "row_text_v";

2424 "i" := "dim_p";

2425 LOOP

2426 "i" := "i" - 1;

2427 EXIT WHEN "i" = 0;

2428 "ary_text_v" := "ary_text_v" || ',' || "row_text_v";

2429 END LOOP;

2430 "ary_text_v" := "ary_text_v" || '}';

2431 RETURN "ary_text_v"::INT4[][];

2432 ELSE

2433 RAISE EXCEPTION 'Dimension needs to be at least 1.';

2434 END IF;

2435 END;

2436 $$;

2437

2438 COMMENT ON FUNCTION "init_square_matrix"(INTEGER) IS 'Needed for PostgreSQL < 8.4, due to missing "array_fill" function';

2439

2440

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

2442 RETURNS VOID

2443 LANGUAGE 'plpgsql' VOLATILE AS $$

2444 DECLARE

2445 "dimension_v" INTEGER;

2446 "matrix" INT4[][];

2447 "i" INTEGER;

2448 "j" INTEGER;

2449 "k" INTEGER;

2450 "battle_row" "battle"%ROWTYPE;

2451 "rank_ary" INT4[];

2452 "rank_v" INT4;

2453 "done_v" INTEGER;

2454 "winners_ary" INTEGER[];

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

2456 BEGIN

2457 PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE;

2458 -- Prepare matrix for Schulze-Method:

2459 SELECT count(1) INTO "dimension_v" FROM "initiative"

2460 WHERE "issue_id" = "issue_id_p" AND "agreed";

2461 IF "dimension_v" = 1 THEN

2462 UPDATE "initiative" SET "rank" = 1

2463 WHERE "issue_id" = "issue_id_p" AND "agreed";

2464 ELSIF "dimension_v" > 1 THEN

2465 "matrix" := "init_square_matrix"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4)

2466 "i" := 1;

2467 "j" := 2;

2468 -- Fill matrix with data from "battle" view

2469 FOR "battle_row" IN

2470 SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p"

2471 ORDER BY "winning_initiative_id", "losing_initiative_id"

2472 LOOP

2473 "matrix"["i"]["j"] := "battle_row"."count";

2474 IF "j" = "dimension_v" THEN

2475 "i" := "i" + 1;

2476 "j" := 1;

2477 ELSE

2478 "j" := "j" + 1;

2479 IF "j" = "i" THEN

2480 "j" := "j" + 1;

2481 END IF;

2482 END IF;

2483 END LOOP;

2484 IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN

2485 RAISE EXCEPTION 'Wrong battle count (should not happen)';

2486 END IF;

2487 -- Delete losers from matrix:

2488 "i" := 1;

2489 LOOP

2490 "j" := "i" + 1;

2491 LOOP

2492 IF "i" != "j" THEN

2493 IF "matrix"["i"]["j"] < "matrix"["j"]["i"] THEN

2494 "matrix"["i"]["j"] := 0;

2495 ELSIF matrix[j][i] < matrix[i][j] THEN

2496 "matrix"["j"]["i"] := 0;

2497 ELSE

2498 "matrix"["i"]["j"] := 0;

2499 "matrix"["j"]["i"] := 0;

2500 END IF;

2501 END IF;

2502 EXIT WHEN "j" = "dimension_v";

2503 "j" := "j" + 1;

2504 END LOOP;

2505 EXIT WHEN "i" = "dimension_v" - 1;

2506 "i" := "i" + 1;

2507 END LOOP;

2508 -- Find best paths:

2509 "i" := 1;

2510 LOOP

2511 "j" := 1;

2512 LOOP

2513 IF "i" != "j" THEN

2514 "k" := 1;

2515 LOOP

2516 IF "i" != "k" AND "j" != "k" THEN

2517 IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN

2518 IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN

2519 "matrix"["j"]["k"] := "matrix"["j"]["i"];

2520 END IF;

2521 ELSE

2522 IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN

2523 "matrix"["j"]["k"] := "matrix"["i"]["k"];

2524 END IF;

2525 END IF;

2526 END IF;

2527 EXIT WHEN "k" = "dimension_v";

2528 "k" := "k" + 1;

2529 END LOOP;

2530 END IF;

2531 EXIT WHEN "j" = "dimension_v";

2532 "j" := "j" + 1;

2533 END LOOP;

2534 EXIT WHEN "i" = "dimension_v";

2535 "i" := "i" + 1;

2536 END LOOP;

2537 -- Determine order of winners:

2538 "rank_ary" := "init_array"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4)

2539 "rank_v" := 1;

2540 "done_v" := 0;

2541 LOOP

2542 "winners_ary" := '{}';

2543 "i" := 1;

2544 LOOP

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

2546 "j" := 1;

2547 LOOP

2548 IF

2549 "i" != "j" AND

2550 "rank_ary"["j"] ISNULL AND

2551 "matrix"["j"]["i"] > "matrix"["i"]["j"]

2552 THEN

2553 -- someone else is better

2554 EXIT;

2555 END IF;

2556 IF "j" = "dimension_v" THEN

2557 -- noone is better

2558 "winners_ary" := "winners_ary" || "i";

2559 EXIT;

2560 END IF;

2561 "j" := "j" + 1;

2562 END LOOP;

2563 END IF;

2564 EXIT WHEN "i" = "dimension_v";

2565 "i" := "i" + 1;

2566 END LOOP;

2567 "i" := 1;

2568 LOOP

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

2570 "done_v" := "done_v" + 1;

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

2572 "i" := "i" + 1;

2573 END LOOP;

2574 EXIT WHEN "done_v" = "dimension_v";

2575 "rank_v" := "rank_v" + 1;

2576 END LOOP;

2577 -- write preliminary ranks:

2578 "i" := 1;

2579 FOR "initiative_id_v" IN

2580 SELECT "id" FROM "initiative"

2581 WHERE "issue_id" = "issue_id_p" AND "agreed"

2582 ORDER BY "id"

2583 LOOP

2584 UPDATE "initiative" SET "rank" = "rank_ary"["i"]

2585 WHERE "id" = "initiative_id_v";

2586 "i" := "i" + 1;

2587 END LOOP;

2588 IF "i" != "dimension_v" + 1 THEN

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

2590 END IF;

2591 -- straighten ranks (start counting with 1, no equal ranks):

2592 "rank_v" := 1;

2593 FOR "initiative_id_v" IN

2594 SELECT "id" FROM "initiative"

2595 WHERE "issue_id" = "issue_id_p" AND "rank" NOTNULL

2596 ORDER BY

2597 "rank",

2598 "vote_ratio"("positive_votes", "negative_votes") DESC,

2599 "id"

2600 LOOP

2601 UPDATE "initiative" SET "rank" = "rank_v"

2602 WHERE "id" = "initiative_id_v";

2603 "rank_v" := "rank_v" + 1;

2604 END LOOP;

2605 END IF;

2606 -- mark issue as finished

2607 UPDATE "issue" SET "ranks_available" = TRUE

2608 WHERE "id" = "issue_id_p";

2609 RETURN;

2610 END;

2611 $$;

2612

2613 COMMENT ON FUNCTION "calculate_ranks"

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

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

2616

2617

2618

2619 -----------------------------

2620 -- Automatic state changes --

2621 -----------------------------

2622

2623

2624 CREATE FUNCTION "check_issue"

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

2626 RETURNS VOID

2627 LANGUAGE 'plpgsql' VOLATILE AS $$

2628 DECLARE

2629 "issue_row" "issue"%ROWTYPE;

2630 "policy_row" "policy"%ROWTYPE;

2631 "voting_requested_v" BOOLEAN;

2632 BEGIN

2633 PERFORM "global_lock"();

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

2635 IF "issue_row"."closed" ISNULL THEN

2636 SELECT * INTO "policy_row" FROM "policy"

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

2638 IF "issue_row"."fully_frozen" ISNULL THEN

2639 PERFORM "create_snapshot"("issue_id_p");

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

2641 END IF;

2642 IF "issue_row"."accepted" ISNULL THEN

2643 IF EXISTS (

2644 SELECT NULL FROM "initiative"

2645 WHERE "issue_id" = "issue_id_p"

2646 AND "supporter_count" > 0

2647 AND "supporter_count" * "policy_row"."issue_quorum_den"

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

2649 ) THEN

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

2651 "issue_row"."accepted" = now(); -- NOTE: "issue_row" used later

2652 UPDATE "issue" SET "accepted" = "issue_row"."accepted"

2653 WHERE "id" = "issue_row"."id";

2654 ELSIF

2655 now() >= "issue_row"."created" + "policy_row"."admission_time"

2656 THEN

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

2658 UPDATE "issue" SET "closed" = now()

2659 WHERE "id" = "issue_row"."id";

2660 END IF;

2661 END IF;

2662 IF

2663 "issue_row"."accepted" NOTNULL AND

2664 "issue_row"."half_frozen" ISNULL

2665 THEN

2666 SELECT

2667 CASE

2668 WHEN "vote_now" * 2 > "issue_row"."population" THEN

2669 TRUE

2670 WHEN "vote_later" * 2 > "issue_row"."population" THEN

2671 FALSE

2672 ELSE NULL

2673 END

2674 INTO "voting_requested_v"

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

2676 IF

2677 "voting_requested_v" OR (

2678 "voting_requested_v" ISNULL AND

2679 now() >= "issue_row"."accepted" + "policy_row"."discussion_time"

2680 )

2681 THEN

2682 "issue_row"."half_frozen" = now(); -- NOTE: "issue_row" used later

2683 UPDATE "issue" SET "half_frozen" = "issue_row"."half_frozen"

2684 WHERE "id" = "issue_row"."id";

2685 END IF;

2686 END IF;

2687 IF

2688 "issue_row"."half_frozen" NOTNULL AND

2689 "issue_row"."fully_frozen" ISNULL AND

2690 now() >= "issue_row"."half_frozen" + "policy_row"."verification_time"

2691 THEN

2692 PERFORM "freeze_after_snapshot"("issue_id_p");

2693 -- "issue" might change, thus "issue_row" has to be updated below

2694 END IF;

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

2696 IF

2697 "issue_row"."closed" ISNULL AND

2698 "issue_row"."fully_frozen" NOTNULL AND

2699 now() >= "issue_row"."fully_frozen" + "policy_row"."voting_time"

2700 THEN

2701 PERFORM "close_voting"("issue_id_p");

2702 END IF;

2703 END IF;

2704 RETURN;

2705 END;

2706 $$;

2707

2708 COMMENT ON FUNCTION "check_issue"

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

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

2711

2712

2713 CREATE FUNCTION "check_everything"()

2714 RETURNS VOID

2715 LANGUAGE 'plpgsql' VOLATILE AS $$

2716 DECLARE

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

2718 BEGIN

2719 DELETE FROM "expired_session";

2720 PERFORM "calculate_member_counts"();

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

2722 PERFORM "check_issue"("issue_id_v");

2723 END LOOP;

2724 FOR "issue_id_v" IN SELECT "id" FROM "issue_with_ranks_missing" LOOP

2725 PERFORM "calculate_ranks"("issue_id_v");

2726 END LOOP;

2727 RETURN;

2728 END;

2729 $$;

2730

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

2732

2733

2734

2735 ------------------------------

2736 -- Deletion of private data --

2737 ------------------------------

2738

2739

2740 CREATE FUNCTION "delete_private_data"()

2741 RETURNS VOID

2742 LANGUAGE 'plpgsql' VOLATILE AS $$

2743 DECLARE

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

2745 BEGIN

2746 UPDATE "member" SET

2747 "login" = 'login' || "id"::text,

2748 "password" = NULL,

2749 "notify_email" = NULL,

2750 "notify_email_unconfirmed" = NULL,

2751 "notify_email_secret" = NULL,

2752 "notify_email_secret_expiry" = NULL;

2753 DELETE FROM "session";

2754 DELETE FROM "invite_code" WHERE "used" ISNULL;

2755 DELETE FROM "contact" WHERE NOT "public";

2756 DELETE FROM "direct_voter" USING "issue"

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

2758 AND "issue"."closed" ISNULL;

2759 RETURN;

2760 END;

2761 $$;

2762

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

2764

2765

2766

2767 COMMIT;