liquid_feedback_core: 4af4df1415f9 core.sql

liquid_feedback_core

view core.sql @ 9:4af4df1415f9

Version beta10

Voting will be skipped, if no initiative is admitted for voting

Invite code feature allows people having an invite code to create one account

Contingent system to limit the number of initiatives or text entries to be submitted by each member within a given time

Ability to store a formatting engine for each draft, which can be used to allow initiatives to choose between available wiki parsers

New table setting storing user settings for the frontend (replaced hidden_hints column of beta9)

Better policy support:
- New table allowed_policy to select which policies can be used in each area
- Policies are now ordered by an index field

Bugfixes:
- In function close_voting(...): If there were no voters, this resulted in a NULL value as positive and negative vote counts
- In delete_private_data() function: Secret fields of member table are now deleted too
- Major bug fixed in lf_export, which caused data loss on execution

author	jbe
date	Thu Dec 10 12:00:00 2009 +0100 (2009-12-10)
parents	e6faf5ff83af
children	effdd7a04ea7

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 ('beta10', 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_confirmed" BOOLEAN,

64 "name" TEXT NOT NULL UNIQUE,

65 "identification" TEXT UNIQUE,

66 "organizational_unit" TEXT,

67 "internal_posts" TEXT,

68 "realname" TEXT,

69 "birthday" DATE,

70 "address" TEXT,

71 "email" TEXT,

72 "xmpp_address" TEXT,

73 "website" TEXT,

74 "phone" TEXT,

75 "mobile_phone" TEXT,

76 "profession" TEXT,

77 "external_memberships" TEXT,

78 "external_posts" TEXT,

79 "statement" TEXT,

80 "text_search_data" TSVECTOR,

81 CONSTRAINT "notify_email_null_check"

82 CHECK ("notify_email" NOTNULL = "notify_email_confirmed" NOTNULL) );

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_confirmed" IS 'TRUE, if "notify_email" has been confirmed';

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

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

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

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

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

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

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

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

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

109

110

111 CREATE TABLE "invite_code" (

112 "code" TEXT PRIMARY KEY,

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

114 "used" TIMESTAMPTZ,

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

116 "comment" TEXT,

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

118

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

120

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

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

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

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

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

126

127

128 CREATE TABLE "setting" (

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

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

131 "key" TEXT NOT NULL,

132 "value" TEXT NOT NULL );

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

134

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

136

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

138

139

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

141

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

143

144

145 CREATE TABLE "member_image" (

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

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

148 "image_type" "member_image_type",

149 "scaled" BOOLEAN,

150 "content_type" TEXT,

151 "data" BYTEA NOT NULL );

152

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

154

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

156

157

158 CREATE TABLE "member_count" (

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

160 "total_count" INT4 NOT NULL );

161

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

163

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

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

166

167

168 CREATE TABLE "contact" (

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

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

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

172 "public" BOOLEAN NOT NULL DEFAULT FALSE );

173

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

175

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

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

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

179

180

181 CREATE TABLE "session" (

182 "ident" TEXT PRIMARY KEY,

183 "additional_secret" TEXT,

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

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

186 "lang" TEXT );

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

188

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

190

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

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

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

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

195

196

197 CREATE TABLE "policy" (

198 "id" SERIAL4 PRIMARY KEY,

199 "index" INT4 NOT NULL,

200 "active" BOOLEAN NOT NULL DEFAULT TRUE,

201 "name" TEXT NOT NULL UNIQUE,

202 "description" TEXT NOT NULL DEFAULT '',

203 "admission_time" INTERVAL NOT NULL,

204 "discussion_time" INTERVAL NOT NULL,

205 "verification_time" INTERVAL NOT NULL,

206 "voting_time" INTERVAL NOT NULL,

207 "issue_quorum_num" INT4 NOT NULL,

208 "issue_quorum_den" INT4 NOT NULL,

209 "initiative_quorum_num" INT4 NOT NULL,

210 "initiative_quorum_den" INT4 NOT NULL );

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

212

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

214

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

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

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

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

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

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

221 COMMENT ON COLUMN "policy"."issue_quorum_num" IS 'Numerator of quorum to be reached by one initiative of an issue to be "accepted"';

222 COMMENT ON COLUMN "policy"."issue_quorum_den" IS 'Denominator of quorum to be reached by one initiative of an issue to be "accepted"';

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

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

225

226

227 CREATE TABLE "area" (

228 "id" SERIAL4 PRIMARY KEY,

229 "active" BOOLEAN NOT NULL DEFAULT TRUE,

230 "name" TEXT NOT NULL,

231 "description" TEXT NOT NULL DEFAULT '',

232 "direct_member_count" INT4,

233 "member_weight" INT4,

234 "autoreject_weight" INT4,

235 "text_search_data" TSVECTOR );

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

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

238 CREATE TRIGGER "update_text_search_data"

239 BEFORE INSERT OR UPDATE ON "area"

240 FOR EACH ROW EXECUTE PROCEDURE

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

242 "name", "description" );

243

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

245

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

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

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

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

250

251

252 CREATE TABLE "allowed_policy" (

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

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

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

256 "default_policy" BOOLEAN NOT NULL DEFAULT FALSE );

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

258

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

260

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

262

263

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

265

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

267

268

269 CREATE TABLE "issue" (

270 "id" SERIAL4 PRIMARY KEY,

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

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

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

274 "accepted" TIMESTAMPTZ,

275 "half_frozen" TIMESTAMPTZ,

276 "fully_frozen" TIMESTAMPTZ,

277 "closed" TIMESTAMPTZ,

278 "ranks_available" BOOLEAN NOT NULL DEFAULT FALSE,

279 "snapshot" TIMESTAMPTZ,

280 "latest_snapshot_event" "snapshot_event",

281 "population" INT4,

282 "vote_now" INT4,

283 "vote_later" INT4,

284 "voter_count" INT4,

285 CONSTRAINT "valid_state" CHECK (

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

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

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

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

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

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

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

293 CONSTRAINT "state_change_order" CHECK (

294 "created" <= "accepted" AND

295 "accepted" <= "half_frozen" AND

296 "half_frozen" <= "fully_frozen" AND

297 "fully_frozen" <= "closed" ),

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

299 CONSTRAINT "freeze_requires_snapshot" CHECK ("fully_frozen" ISNULL OR "snapshot" NOTNULL),

300 CONSTRAINT "set_both_or_none_of_snapshot_and_latest_snapshot_event" CHECK ("snapshot" NOTNULL = "latest_snapshot_event" NOTNULL) );

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

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

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

304

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

306

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

308 COMMENT ON COLUMN "issue"."half_frozen" IS 'Point in time, when "discussion_time" has elapsed, or members voted for voting';

309 COMMENT ON COLUMN "issue"."fully_frozen" IS 'Point in time, when "verification_time" has elapsed';

310 COMMENT ON COLUMN "issue"."closed" IS 'Point in time, when "admission_time" or "voting_time" have elapsed, and issue is no longer active';

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

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

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

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

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

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

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

318

319

320 CREATE TABLE "initiative" (

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

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

323 "id" SERIAL4 PRIMARY KEY,

324 "name" TEXT NOT NULL,

325 "discussion_url" TEXT,

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

327 "revoked" TIMESTAMPTZ,

328 "admitted" BOOLEAN,

329 "supporter_count" INT4,

330 "informed_supporter_count" INT4,

331 "satisfied_supporter_count" INT4,

332 "satisfied_informed_supporter_count" INT4,

333 "positive_votes" INT4,

334 "negative_votes" INT4,

335 "rank" INT4,

336 "text_search_data" TSVECTOR,

337 CONSTRAINT "revoked_initiatives_cant_be_admitted"

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

339 CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results"

340 CHECK ("admitted" = TRUE OR ("positive_votes" ISNULL AND "negative_votes" ISNULL AND "rank" ISNULL)) );

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

342 CREATE TRIGGER "update_text_search_data"

343 BEFORE INSERT OR UPDATE ON "initiative"

344 FOR EACH ROW EXECUTE PROCEDURE

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

346 "name", "discussion_url");

347

348 COMMENT ON TABLE "initiative" IS 'Group of members publishing drafts for resolutions to be passed';

349

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

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

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

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

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

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

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

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

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

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

360

361

362 CREATE TABLE "draft" (

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

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

365 "id" SERIAL8 PRIMARY KEY,

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

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

368 "formatting_engine" TEXT,

369 "content" TEXT NOT NULL,

370 "text_search_data" TSVECTOR );

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

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

373 CREATE TRIGGER "update_text_search_data"

374 BEFORE INSERT OR UPDATE ON "draft"

375 FOR EACH ROW EXECUTE PROCEDURE

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

377

378 COMMENT ON TABLE "draft" IS 'Drafts of initiatives to solve issues';

379

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

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

382

383

384 CREATE TABLE "suggestion" (

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

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

387 "id" SERIAL8 PRIMARY KEY,

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

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

390 "name" TEXT NOT NULL,

391 "description" TEXT NOT NULL DEFAULT '',

392 "text_search_data" TSVECTOR,

393 "minus2_unfulfilled_count" INT4,

394 "minus2_fulfilled_count" INT4,

395 "minus1_unfulfilled_count" INT4,

396 "minus1_fulfilled_count" INT4,

397 "plus1_unfulfilled_count" INT4,

398 "plus1_fulfilled_count" INT4,

399 "plus2_unfulfilled_count" INT4,

400 "plus2_fulfilled_count" INT4 );

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

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

403 CREATE TRIGGER "update_text_search_data"

404 BEFORE INSERT OR UPDATE ON "suggestion"

405 FOR EACH ROW EXECUTE PROCEDURE

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

407 "name", "description");

408

409 COMMENT ON TABLE "suggestion" IS 'Suggestions to initiators, to change the current draft';

410

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

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

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

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

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

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

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

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

419

420

421 CREATE TABLE "membership" (

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

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

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

425 "autoreject" BOOLEAN NOT NULL DEFAULT FALSE );

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

427

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

429

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

431

432

433 CREATE TABLE "interest" (

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

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

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

437 "autoreject" BOOLEAN NOT NULL,

438 "voting_requested" BOOLEAN );

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

440

441 COMMENT ON TABLE "interest" IS 'Interest of members in a particular issue';

442

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

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

445

446

447 CREATE TABLE "initiator" (

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

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

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

451 "accepted" BOOLEAN NOT NULL DEFAULT TRUE );

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

453

454 COMMENT ON TABLE "initiator" IS 'Members who are allowed to post new drafts';

455

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

457

458

459 CREATE TABLE "supporter" (

460 "issue_id" INT4 NOT NULL,

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

462 "initiative_id" INT4,

463 "member_id" INT4,

464 "draft_id" INT8 NOT NULL,

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

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

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

468

469 COMMENT ON TABLE "supporter" IS 'Members who support an initiative (conditionally)';

470

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

472

473

474 CREATE TABLE "opinion" (

475 "initiative_id" INT4 NOT NULL,

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

477 "suggestion_id" INT8,

478 "member_id" INT4,

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

480 "fulfilled" BOOLEAN NOT NULL DEFAULT FALSE,

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

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

483 CREATE INDEX "opinion_member_id_idx" ON "opinion" ("member_id");

484

485 COMMENT ON TABLE "opinion" IS 'Opinion on suggestions (criticism related to initiatives)';

486

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

488

489

490 CREATE TABLE "delegation" (

491 "id" SERIAL8 PRIMARY KEY,

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

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

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

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

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

497 CONSTRAINT "area_id_or_issue_id_can_be_set_but_not_both" CHECK ("area_id" ISNULL OR "issue_id" ISNULL),

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

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

500 CREATE UNIQUE INDEX "delegation_default_truster_id_trustee_id_unique_idx"

501 ON "delegation" ("truster_id", "trustee_id")

502 WHERE "area_id" ISNULL AND "issue_id" ISNULL;

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

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

505

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

507

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

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

510

511

512 CREATE TABLE "direct_population_snapshot" (

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

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

515 "event" "snapshot_event",

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

517 "weight" INT4,

518 "interest_exists" BOOLEAN NOT NULL );

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

520

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

522

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

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

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

526

527

528 CREATE TABLE "delegating_population_snapshot" (

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

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

531 "event" "snapshot_event",

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

533 "weight" INT4,

534 "delegate_member_ids" INT4[] NOT NULL );

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

536

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

538

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

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

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

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

543

544

545 CREATE TABLE "direct_interest_snapshot" (

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

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

548 "event" "snapshot_event",

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

550 "weight" INT4,

551 "voting_requested" BOOLEAN );

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

553

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

555

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

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

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

559

560

561 CREATE TABLE "delegating_interest_snapshot" (

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

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

564 "event" "snapshot_event",

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

566 "weight" INT4,

567 "delegate_member_ids" INT4[] NOT NULL );

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

569

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

571

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

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

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

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

576

577

578 CREATE TABLE "direct_supporter_snapshot" (

579 "issue_id" INT4 NOT NULL,

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

581 "initiative_id" INT4,

582 "event" "snapshot_event",

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

584 "informed" BOOLEAN NOT NULL,

585 "satisfied" BOOLEAN NOT NULL,

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

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

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

589

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

591

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

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

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

595

596

597 CREATE TABLE "direct_voter" (

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

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

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

601 "weight" INT4,

602 "autoreject" BOOLEAN NOT NULL DEFAULT FALSE );

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

604

605 COMMENT ON TABLE "direct_voter" IS 'Members having directly voted for/against initiatives of an issue';

606

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

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

609

610

611 CREATE TABLE "delegating_voter" (

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

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

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

615 "weight" INT4,

616 "delegate_member_ids" INT4[] NOT NULL );

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

618

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

620

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

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

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

624

625

626 CREATE TABLE "vote" (

627 "issue_id" INT4 NOT NULL,

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

629 "initiative_id" INT4,

630 "member_id" INT4,

631 "grade" INT4,

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

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

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

635

636 COMMENT ON TABLE "vote" IS 'Manual and delegated votes without abstentions';

637

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

639

640

641 CREATE TABLE "contingent" (

642 "time_frame" INTERVAL PRIMARY KEY,

643 "text_entry_limit" INT4,

644 "initiative_limit" INT4 );

645

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

647

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

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

650

651

652

653 ----------------------------

654 -- Additional constraints --

655 ----------------------------

656

657

658 CREATE FUNCTION "issue_requires_first_initiative_trigger"()

659 RETURNS TRIGGER

660 LANGUAGE 'plpgsql' VOLATILE AS $$

661 BEGIN

662 IF NOT EXISTS (

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

664 ) THEN

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

666 -- ERRCODE = 'integrity_constraint_violation',

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

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

669 END IF;

670 RETURN NULL;

671 END;

672 $$;

673

674 CREATE CONSTRAINT TRIGGER "issue_requires_first_initiative"

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

676 FOR EACH ROW EXECUTE PROCEDURE

677 "issue_requires_first_initiative_trigger"();

678

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

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

681

682

683 CREATE FUNCTION "last_initiative_deletes_issue_trigger"()

684 RETURNS TRIGGER

685 LANGUAGE 'plpgsql' VOLATILE AS $$

686 DECLARE

687 "reference_lost" BOOLEAN;

688 BEGIN

689 IF TG_OP = 'DELETE' THEN

690 "reference_lost" := TRUE;

691 ELSE

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

693 END IF;

694 IF

695 "reference_lost" AND NOT EXISTS (

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

697 )

698 THEN

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

700 END IF;

701 RETURN NULL;

702 END;

703 $$;

704

705 CREATE CONSTRAINT TRIGGER "last_initiative_deletes_issue"

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

707 FOR EACH ROW EXECUTE PROCEDURE

708 "last_initiative_deletes_issue_trigger"();

709

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

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

712

713

714 CREATE FUNCTION "initiative_requires_first_draft_trigger"()

715 RETURNS TRIGGER

716 LANGUAGE 'plpgsql' VOLATILE AS $$

717 BEGIN

718 IF NOT EXISTS (

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

720 ) THEN

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

722 -- ERRCODE = 'integrity_constraint_violation',

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

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

725 END IF;

726 RETURN NULL;

727 END;

728 $$;

729

730 CREATE CONSTRAINT TRIGGER "initiative_requires_first_draft"

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

732 FOR EACH ROW EXECUTE PROCEDURE

733 "initiative_requires_first_draft_trigger"();

734

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

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

737

738

739 CREATE FUNCTION "last_draft_deletes_initiative_trigger"()

740 RETURNS TRIGGER

741 LANGUAGE 'plpgsql' VOLATILE AS $$

742 DECLARE

743 "reference_lost" BOOLEAN;

744 BEGIN

745 IF TG_OP = 'DELETE' THEN

746 "reference_lost" := TRUE;

747 ELSE

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

749 END IF;

750 IF

751 "reference_lost" AND NOT EXISTS (

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

753 )

754 THEN

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

756 END IF;

757 RETURN NULL;

758 END;

759 $$;

760

761 CREATE CONSTRAINT TRIGGER "last_draft_deletes_initiative"

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

763 FOR EACH ROW EXECUTE PROCEDURE

764 "last_draft_deletes_initiative_trigger"();

765

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

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

768

769

770 CREATE FUNCTION "suggestion_requires_first_opinion_trigger"()

771 RETURNS TRIGGER

772 LANGUAGE 'plpgsql' VOLATILE AS $$

773 BEGIN

774 IF NOT EXISTS (

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

776 ) THEN

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

778 END IF;

779 RETURN NULL;

780 END;

781 $$;

782

783 CREATE CONSTRAINT TRIGGER "suggestion_requires_first_opinion"

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

785 FOR EACH ROW EXECUTE PROCEDURE

786 "suggestion_requires_first_opinion_trigger"();

787

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

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

790

791

792 CREATE FUNCTION "last_opinion_deletes_suggestion_trigger"()

793 RETURNS TRIGGER

794 LANGUAGE 'plpgsql' VOLATILE AS $$

795 DECLARE

796 "reference_lost" BOOLEAN;

797 BEGIN

798 IF TG_OP = 'DELETE' THEN

799 "reference_lost" := TRUE;

800 ELSE

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

802 END IF;

803 IF

804 "reference_lost" AND NOT EXISTS (

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

806 )

807 THEN

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

809 END IF;

810 RETURN NULL;

811 END;

812 $$;

813

814 CREATE CONSTRAINT TRIGGER "last_opinion_deletes_suggestion"

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

816 FOR EACH ROW EXECUTE PROCEDURE

817 "last_opinion_deletes_suggestion_trigger"();

818

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

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

821

822

823

824 --------------------------------------------------------------------

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

826 --------------------------------------------------------------------

827

828 CREATE FUNCTION "autofill_issue_id_trigger"()

829 RETURNS TRIGGER

830 LANGUAGE 'plpgsql' VOLATILE AS $$

831 BEGIN

832 IF NEW."issue_id" ISNULL THEN

833 SELECT "issue_id" INTO NEW."issue_id"

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

835 END IF;

836 RETURN NEW;

837 END;

838 $$;

839

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

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

842

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

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

845

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

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

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

849

850

851 CREATE FUNCTION "autofill_initiative_id_trigger"()

852 RETURNS TRIGGER

853 LANGUAGE 'plpgsql' VOLATILE AS $$

854 BEGIN

855 IF NEW."initiative_id" ISNULL THEN

856 SELECT "initiative_id" INTO NEW."initiative_id"

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

858 END IF;

859 RETURN NEW;

860 END;

861 $$;

862

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

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

865

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

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

868

869

870

871 -----------------------------------------------------

872 -- Automatic calculation of certain default values --

873 -----------------------------------------------------

874

875 CREATE FUNCTION "copy_autoreject_trigger"()

876 RETURNS TRIGGER

877 LANGUAGE 'plpgsql' VOLATILE AS $$

878 BEGIN

879 IF NEW."autoreject" ISNULL THEN

880 SELECT "membership"."autoreject" INTO NEW."autoreject"

881 FROM "issue" JOIN "membership"

882 ON "issue"."area_id" = "membership"."area_id"

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

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

885 END IF;

886 IF NEW."autoreject" ISNULL THEN

887 NEW."autoreject" := FALSE;

888 END IF;

889 RETURN NEW;

890 END;

891 $$;

892

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

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

895

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

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

898

899

900 CREATE FUNCTION "supporter_default_for_draft_id_trigger"()

901 RETURNS TRIGGER

902 LANGUAGE 'plpgsql' VOLATILE AS $$

903 BEGIN

904 IF NEW."draft_id" ISNULL THEN

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

906 WHERE "initiative_id" = NEW."initiative_id";

907 END IF;

908 RETURN NEW;

909 END;

910 $$;

911

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

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

914

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

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

917

918

919

920 ----------------------------------------

921 -- Automatic creation of dependencies --

922 ----------------------------------------

923

924 CREATE FUNCTION "autocreate_interest_trigger"()

925 RETURNS TRIGGER

926 LANGUAGE 'plpgsql' VOLATILE AS $$

927 BEGIN

928 IF NOT EXISTS (

929 SELECT NULL FROM "initiative" JOIN "interest"

930 ON "initiative"."issue_id" = "interest"."issue_id"

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

932 AND "interest"."member_id" = NEW."member_id"

933 ) THEN

934 BEGIN

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

936 SELECT "issue_id", NEW."member_id"

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

938 EXCEPTION WHEN unique_violation THEN END;

939 END IF;

940 RETURN NEW;

941 END;

942 $$;

943

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

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

946

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

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

949

950

951 CREATE FUNCTION "autocreate_supporter_trigger"()

952 RETURNS TRIGGER

953 LANGUAGE 'plpgsql' VOLATILE AS $$

954 BEGIN

955 IF NOT EXISTS (

956 SELECT NULL FROM "suggestion" JOIN "supporter"

957 ON "suggestion"."initiative_id" = "supporter"."initiative_id"

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

959 AND "supporter"."member_id" = NEW."member_id"

960 ) THEN

961 BEGIN

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

963 SELECT "initiative_id", NEW."member_id"

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

965 EXCEPTION WHEN unique_violation THEN END;

966 END IF;

967 RETURN NEW;

968 END;

969 $$;

970

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

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

973

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

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

976

977

978

979 ------------------------------------------

980 -- Views and helper functions for views --

981 ------------------------------------------

982

983

984 CREATE TYPE "delegation_scope" AS ENUM

985 ('global', 'area', 'issue');

986

987 COMMENT ON TYPE "delegation_scope" IS 'Scope for delegations: ''global'', ''area'', or ''issue''';

988

989

990 CREATE VIEW "global_delegation" AS

991 SELECT

992 "delegation"."id",

993 "delegation"."truster_id",

994 "delegation"."trustee_id"

995 FROM "delegation" JOIN "member"

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

997 WHERE "delegation"."area_id" ISNULL

998 AND "delegation"."issue_id" ISNULL

999 AND "member"."active";

1000

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

1002

1003

1004 CREATE VIEW "area_delegation" AS

1005 SELECT "subquery".* FROM (

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

1007 "area"."id" AS "area_id",

1008 "delegation"."id" AS "id",

1009 "delegation"."truster_id" AS "truster_id",

1010 "delegation"."trustee_id" AS "trustee_id",

1011 CASE WHEN "delegation"."area_id" ISNULL THEN

1012 'global'::"delegation_scope"

1013 ELSE

1014 'area'::"delegation_scope"

1015 END AS "scope"

1016 FROM "area" JOIN "delegation"

1017 ON ("delegation"."area_id" ISNULL OR "delegation"."area_id" = "area"."id")

1018 AND "delegation"."issue_id" ISNULL

1019 ORDER BY

1020 "area"."id",

1021 "delegation"."truster_id",

1022 "delegation"."area_id" NULLS LAST

1023 ) AS "subquery"

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

1025 WHERE "member"."active";

1026

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

1028

1029

1030 CREATE VIEW "issue_delegation" AS

1031 SELECT "subquery".* FROM (

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

1033 "issue"."id" AS "issue_id",

1034 "delegation"."id" AS "id",

1035 "delegation"."truster_id" AS "truster_id",

1036 "delegation"."trustee_id" AS "trustee_id",

1037 CASE

1038 WHEN

1039 "delegation"."area_id" ISNULL AND

1040 "delegation"."issue_id" ISNULL

1041 THEN 'global'::"delegation_scope"

1042 WHEN

1043 "delegation"."area_id" NOTNULL

1044 THEN 'area'::"delegation_scope"

1045 ELSE 'issue'::"delegation_scope"

1046 END AS "scope"

1047 FROM "issue" JOIN "delegation"

1048 ON (

1049 "delegation"."area_id" ISNULL OR

1050 "delegation"."area_id" = "issue"."area_id"

1051 ) AND (

1052 "delegation"."issue_id" ISNULL OR

1053 "delegation"."issue_id" = "issue"."id"

1054 )

1055 ORDER BY

1056 "issue"."id",

1057 "delegation"."truster_id",

1058 "delegation"."issue_id" NULLS LAST,

1059 "delegation"."area_id" NULLS LAST

1060 ) AS "subquery"

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

1062 WHERE "member"."active";

1063

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

1065

1066

1067 CREATE FUNCTION "membership_weight_with_skipping"

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

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

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

1071 RETURNS INT4

1072 LANGUAGE 'plpgsql' STABLE AS $$

1073 DECLARE

1074 "sum_v" INT4;

1075 "delegation_row" "area_delegation"%ROWTYPE;

1076 BEGIN

1077 "sum_v" := 1;

1078 FOR "delegation_row" IN

1079 SELECT "area_delegation".*

1080 FROM "area_delegation" LEFT JOIN "membership"

1081 ON "membership"."area_id" = "area_id_p"

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

1083 WHERE "area_delegation"."area_id" = "area_id_p"

1084 AND "area_delegation"."trustee_id" = "member_id_p"

1085 AND "membership"."member_id" ISNULL

1086 LOOP

1087 IF NOT

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

1089 THEN

1090 "sum_v" := "sum_v" + "membership_weight_with_skipping"(

1091 "area_id_p",

1092 "delegation_row"."truster_id",

1093 "skip_member_ids_p" || "delegation_row"."truster_id"

1094 );

1095 END IF;

1096 END LOOP;

1097 RETURN "sum_v";

1098 END;

1099 $$;

1100

1101 COMMENT ON FUNCTION "membership_weight_with_skipping"

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

1103 "member"."id"%TYPE,

1104 INT4[] )

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

1106

1107

1108 CREATE FUNCTION "membership_weight"

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

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

1111 RETURNS INT4

1112 LANGUAGE 'plpgsql' STABLE AS $$

1113 BEGIN

1114 RETURN "membership_weight_with_skipping"(

1115 "area_id_p",

1116 "member_id_p",

1117 ARRAY["member_id_p"]

1118 );

1119 END;

1120 $$;

1121

1122 COMMENT ON FUNCTION "membership_weight"

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

1124 "member"."id"%TYPE )

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

1126

1127

1128 CREATE VIEW "member_count_view" AS

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

1130

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

1132

1133

1134 CREATE VIEW "area_member_count" AS

1135 SELECT

1136 "area"."id" AS "area_id",

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

1138 coalesce(

1139 sum(

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

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

1142 ELSE 0 END

1143 )

1144 ) AS "member_weight",

1145 coalesce(

1146 sum(

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

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

1149 ELSE 0 END

1150 )

1151 ) AS "autoreject_weight"

1152 FROM "area"

1153 LEFT JOIN "membership"

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

1155 LEFT JOIN "member"

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

1157 AND "member"."active"

1158 GROUP BY "area"."id";

1159

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

1161

1162

1163 CREATE VIEW "opening_draft" AS

1164 SELECT "draft".* FROM (

1165 SELECT

1166 "initiative"."id" AS "initiative_id",

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

1168 FROM "initiative" JOIN "draft"

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

1170 GROUP BY "initiative"."id"

1171 ) AS "subquery"

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

1173

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

1175

1176

1177 CREATE VIEW "current_draft" AS

1178 SELECT "draft".* FROM (

1179 SELECT

1180 "initiative"."id" AS "initiative_id",

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

1182 FROM "initiative" JOIN "draft"

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

1184 GROUP BY "initiative"."id"

1185 ) AS "subquery"

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

1187

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

1189

1190

1191 CREATE VIEW "critical_opinion" AS

1192 SELECT * FROM "opinion"

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

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

1195

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

1197

1198

1199 CREATE VIEW "battle_participant" AS

1200 SELECT "issue_id", "id" AS "initiative_id" FROM "initiative"

1201 WHERE "admitted"

1202 AND "positive_votes" > "negative_votes";

1203

1204 COMMENT ON VIEW "battle_participant" IS 'Helper view for "battle" view';

1205

1206

1207 CREATE VIEW "battle" AS

1208 SELECT

1209 "issue"."id" AS "issue_id",

1210 "winning_initiative"."initiative_id" AS "winning_initiative_id",

1211 "losing_initiative"."initiative_id" AS "losing_initiative_id",

1212 sum(

1213 CASE WHEN

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

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

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

1217 ) AS "count"

1218 FROM "issue"

1219 LEFT JOIN "direct_voter"

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

1221 JOIN "battle_participant" AS "winning_initiative"

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

1223 JOIN "battle_participant" AS "losing_initiative"

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

1225 LEFT JOIN "vote" AS "better_vote"

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

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

1228 LEFT JOIN "vote" AS "worse_vote"

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

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

1231 WHERE

1232 "winning_initiative"."initiative_id" !=

1233 "losing_initiative"."initiative_id"

1234 GROUP BY

1235 "issue"."id",

1236 "winning_initiative"."initiative_id",

1237 "losing_initiative"."initiative_id";

1238

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

1240

1241

1242 CREATE VIEW "expired_session" AS

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

1244

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

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

1247

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

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

1250

1251

1252 CREATE VIEW "open_issue" AS

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

1254

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

1256

1257

1258 CREATE VIEW "issue_with_ranks_missing" AS

1259 SELECT * FROM "issue"

1260 WHERE "fully_frozen" NOTNULL

1261 AND "closed" NOTNULL

1262 AND "ranks_available" = FALSE;

1263

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

1265

1266

1267 CREATE VIEW "member_contingent" AS

1268 SELECT

1269 "member"."id" AS "member_id",

1270 "contingent"."time_frame",

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

1272 (

1273 SELECT count(1) FROM "draft"

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

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

1276 ) + (

1277 SELECT count(1) FROM "suggestion"

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

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

1280 )

1281 ELSE NULL END AS "text_entry_count",

1282 "contingent"."text_entry_limit",

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

1284 SELECT count(1) FROM "opening_draft"

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

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

1287 ) ELSE NULL END AS "initiative_count",

1288 "contingent"."initiative_limit"

1289 FROM "member" CROSS JOIN "contingent";

1290

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

1292

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

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

1295

1296

1297 CREATE VIEW "member_contingent_left" AS

1298 SELECT

1299 "member_id",

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

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

1302 FROM "member_contingent" GROUP BY "member_id";

1303

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

1305

1306

1307

1308 --------------------------------------------------

1309 -- Set returning function for delegation chains --

1310 --------------------------------------------------

1311

1312

1313 CREATE TYPE "delegation_chain_loop_tag" AS ENUM

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

1315

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

1317

1318

1319 CREATE TYPE "delegation_chain_row" AS (

1320 "index" INT4,

1321 "member_id" INT4,

1322 "member_active" BOOLEAN,

1323 "participation" BOOLEAN,

1324 "overridden" BOOLEAN,

1325 "scope_in" "delegation_scope",

1326 "scope_out" "delegation_scope",

1327 "loop" "delegation_chain_loop_tag" );

1328

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

1330

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

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

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

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

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

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

1337

1338

1339 CREATE FUNCTION "delegation_chain"

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

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

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

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

1344 RETURNS SETOF "delegation_chain_row"

1345 LANGUAGE 'plpgsql' STABLE AS $$

1346 DECLARE

1347 "issue_row" "issue"%ROWTYPE;

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

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

1350 "output_row" "delegation_chain_row";

1351 "output_rows" "delegation_chain_row"[];

1352 "delegation_row" "delegation"%ROWTYPE;

1353 "row_count" INT4;

1354 "i" INT4;

1355 "loop_v" BOOLEAN;

1356 BEGIN

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

1358 "visited_member_ids" := '{}';

1359 "loop_member_id_v" := NULL;

1360 "output_rows" := '{}';

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

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

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

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

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

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

1367 LOOP

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

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

1370 ELSE

1371 "visited_member_ids" :=

1372 "visited_member_ids" || "output_row"."member_id";

1373 END IF;

1374 IF "output_row"."participation" THEN

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

1376 END IF;

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

1378 IF EXISTS (

1379 SELECT NULL FROM "member"

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

1381 ) THEN

1382 IF "area_id_p" ISNULL AND "issue_id_p" ISNULL THEN

1383 SELECT * INTO "delegation_row" FROM "delegation"

1384 WHERE "truster_id" = "output_row"."member_id"

1385 AND "area_id" ISNULL AND "issue_id" ISNULL;

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

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

1388 SELECT NULL FROM "membership"

1389 WHERE "area_id" = "area_id_p"

1390 AND "member_id" = "output_row"."member_id"

1391 );

1392 SELECT * INTO "delegation_row" FROM "delegation"

1393 WHERE "truster_id" = "output_row"."member_id"

1394 AND ("area_id" ISNULL OR "area_id" = "area_id_p")

1395 AND "issue_id" ISNULL

1396 ORDER BY "area_id" NULLS LAST;

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

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

1399 SELECT NULL FROM "interest"

1400 WHERE "issue_id" = "issue_id_p"

1401 AND "member_id" = "output_row"."member_id"

1402 );

1403 SELECT * INTO "delegation_row" FROM "delegation"

1404 WHERE "truster_id" = "output_row"."member_id"

1405 AND ("area_id" ISNULL OR "area_id" = "issue_row"."area_id")

1406 AND ("issue_id" ISNULL OR "issue_id" = "issue_id_p")

1407 ORDER BY "issue_id" NULLS LAST, "area_id" NULLS LAST;

1408 ELSE

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

1410 END IF;

1411 ELSE

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

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

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

1415 "delegation_row" := ROW(NULL);

1416 END IF;

1417 IF

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

1419 "simulate_trustee_id_p" NOTNULL

1420 THEN

1421 "output_row"."scope_out" := CASE

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

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

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

1425 END;

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

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

1428 ELSIF "delegation_row"."trustee_id" NOTNULL THEN

1429 "output_row"."scope_out" := CASE

1430 WHEN

1431 "delegation_row"."area_id" ISNULL AND

1432 "delegation_row"."issue_id" ISNULL

1433 THEN 'global'

1434 WHEN

1435 "delegation_row"."area_id" NOTNULL AND

1436 "delegation_row"."issue_id" ISNULL

1437 THEN 'area'

1438 WHEN

1439 "delegation_row"."area_id" ISNULL AND

1440 "delegation_row"."issue_id" NOTNULL

1441 THEN 'issue'

1442 END;

1443 "output_rows" := "output_rows" || "output_row";

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

1445 ELSE

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

1447 "output_rows" := "output_rows" || "output_row";

1448 EXIT;

1449 END IF;

1450 EXIT WHEN "loop_member_id_v" NOTNULL;

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

1452 END LOOP;

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

1454 "i" := 1;

1455 "loop_v" := FALSE;

1456 LOOP

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

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

1459 IF "loop_v" THEN

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

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

1462 ELSIF "i" = "row_count" THEN

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

1464 ELSE

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

1466 END IF;

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

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

1469 "loop_v" := TRUE;

1470 END IF;

1471 IF "area_id_p" ISNULL AND "issue_id_p" ISNULL THEN

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

1473 END IF;

1474 RETURN NEXT "output_row";

1475 "i" := "i" + 1;

1476 END LOOP;

1477 RETURN;

1478 END;

1479 $$;

1480

1481 COMMENT ON FUNCTION "delegation_chain"

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

1483 "area"."id"%TYPE,

1484 "issue"."id"%TYPE,

1485 "member"."id"%TYPE )

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

1487

1488 CREATE FUNCTION "delegation_chain"

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

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

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

1492 RETURNS SETOF "delegation_chain_row"

1493 LANGUAGE 'plpgsql' STABLE AS $$

1494 DECLARE

1495 "result_row" "delegation_chain_row";

1496 BEGIN

1497 FOR "result_row" IN

1498 SELECT * FROM "delegation_chain"(

1499 "member_id_p", "area_id_p", "issue_id_p", NULL

1500 )

1501 LOOP

1502 RETURN NEXT "result_row";

1503 END LOOP;

1504 RETURN;

1505 END;

1506 $$;

1507

1508 COMMENT ON FUNCTION "delegation_chain"

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

1510 "area"."id"%TYPE,

1511 "issue"."id"%TYPE )

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

1513

1514

1515

1516 ------------------------------

1517 -- Comparison by vote count --

1518 ------------------------------

1519

1520 CREATE FUNCTION "vote_ratio"

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

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

1523 RETURNS FLOAT8

1524 LANGUAGE 'plpgsql' STABLE AS $$

1525 DECLARE

1526 "total_v" INT4;

1527 BEGIN

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

1529 IF "total_v" > 0 THEN

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

1531 ELSE

1532 RETURN 0.5;

1533 END IF;

1534 END;

1535 $$;

1536

1537 COMMENT ON FUNCTION "vote_ratio"

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

1539 "initiative"."negative_votes"%TYPE )

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

1541

1542

1543

1544 ------------------------------------------------

1545 -- Locking for snapshots and voting procedure --

1546 ------------------------------------------------

1547

1548 CREATE FUNCTION "global_lock"() RETURNS VOID

1549 LANGUAGE 'plpgsql' VOLATILE AS $$

1550 BEGIN

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

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

1553 LOCK TABLE "member" IN EXCLUSIVE MODE;

1554 LOCK TABLE "area" IN EXCLUSIVE MODE;

1555 LOCK TABLE "membership" IN EXCLUSIVE MODE;

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

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

1558 LOCK TABLE "policy" IN EXCLUSIVE MODE;

1559 LOCK TABLE "issue" IN EXCLUSIVE MODE;

1560 LOCK TABLE "initiative" IN EXCLUSIVE MODE;

1561 LOCK TABLE "draft" IN EXCLUSIVE MODE;

1562 LOCK TABLE "suggestion" IN EXCLUSIVE MODE;

1563 LOCK TABLE "interest" IN EXCLUSIVE MODE;

1564 LOCK TABLE "initiator" IN EXCLUSIVE MODE;

1565 LOCK TABLE "supporter" IN EXCLUSIVE MODE;

1566 LOCK TABLE "opinion" IN EXCLUSIVE MODE;

1567 LOCK TABLE "delegation" IN EXCLUSIVE MODE;

1568 LOCK TABLE "direct_population_snapshot" IN EXCLUSIVE MODE;

1569 LOCK TABLE "delegating_population_snapshot" IN EXCLUSIVE MODE;

1570 LOCK TABLE "direct_interest_snapshot" IN EXCLUSIVE MODE;

1571 LOCK TABLE "delegating_interest_snapshot" IN EXCLUSIVE MODE;

1572 LOCK TABLE "direct_supporter_snapshot" IN EXCLUSIVE MODE;

1573 LOCK TABLE "direct_voter" IN EXCLUSIVE MODE;

1574 LOCK TABLE "delegating_voter" IN EXCLUSIVE MODE;

1575 LOCK TABLE "vote" IN EXCLUSIVE MODE;

1576 RETURN;

1577 END;

1578 $$;

1579

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

1581

1582

1583

1584 -------------------------------

1585 -- Materialize member counts --

1586 -------------------------------

1587

1588 CREATE FUNCTION "calculate_member_counts"()

1589 RETURNS VOID

1590 LANGUAGE 'plpgsql' VOLATILE AS $$

1591 BEGIN

1592 LOCK TABLE "member" IN EXCLUSIVE MODE;

1593 LOCK TABLE "area" IN EXCLUSIVE MODE;

1594 LOCK TABLE "membership" IN EXCLUSIVE MODE;

1595 DELETE FROM "member_count";

1596 INSERT INTO "member_count" ("total_count")

1597 SELECT "total_count" FROM "member_count_view";

1598 UPDATE "area" SET

1599 "direct_member_count" = "view"."direct_member_count",

1600 "member_weight" = "view"."member_weight",

1601 "autoreject_weight" = "view"."autoreject_weight"

1602 FROM "area_member_count" AS "view"

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

1604 RETURN;

1605 END;

1606 $$;

1607

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

1609

1610

1611

1612 ------------------------------

1613 -- Calculation of snapshots --

1614 ------------------------------

1615

1616 CREATE FUNCTION "weight_of_added_delegations_for_population_snapshot"

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

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

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

1620 RETURNS "direct_population_snapshot"."weight"%TYPE

1621 LANGUAGE 'plpgsql' VOLATILE AS $$

1622 DECLARE

1623 "issue_delegation_row" "issue_delegation"%ROWTYPE;

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

1625 "weight_v" INT4;

1626 "sub_weight_v" INT4;

1627 BEGIN

1628 "weight_v" := 0;

1629 FOR "issue_delegation_row" IN

1630 SELECT * FROM "issue_delegation"

1631 WHERE "trustee_id" = "member_id_p"

1632 AND "issue_id" = "issue_id_p"

1633 LOOP

1634 IF NOT EXISTS (

1635 SELECT NULL FROM "direct_population_snapshot"

1636 WHERE "issue_id" = "issue_id_p"

1637 AND "event" = 'periodic'

1638 AND "member_id" = "issue_delegation_row"."truster_id"

1639 ) AND NOT EXISTS (

1640 SELECT NULL FROM "delegating_population_snapshot"

1641 WHERE "issue_id" = "issue_id_p"

1642 AND "event" = 'periodic'

1643 AND "member_id" = "issue_delegation_row"."truster_id"

1644 ) THEN

1645 "delegate_member_ids_v" :=

1646 "member_id_p" || "delegate_member_ids_p";

1647 INSERT INTO "delegating_population_snapshot"

1648 ("issue_id", "event", "member_id", "delegate_member_ids")

1649 VALUES (

1650 "issue_id_p",

1651 'periodic',

1652 "issue_delegation_row"."truster_id",

1653 "delegate_member_ids_v"

1654 );

1655 "sub_weight_v" := 1 +

1656 "weight_of_added_delegations_for_population_snapshot"(

1657 "issue_id_p",

1658 "issue_delegation_row"."truster_id",

1659 "delegate_member_ids_v"

1660 );

1661 UPDATE "delegating_population_snapshot"

1662 SET "weight" = "sub_weight_v"

1663 WHERE "issue_id" = "issue_id_p"

1664 AND "event" = 'periodic'

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

1666 "weight_v" := "weight_v" + "sub_weight_v";

1667 END IF;

1668 END LOOP;

1669 RETURN "weight_v";

1670 END;

1671 $$;

1672

1673 COMMENT ON FUNCTION "weight_of_added_delegations_for_population_snapshot"

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

1675 "member"."id"%TYPE,

1676 "delegating_population_snapshot"."delegate_member_ids"%TYPE )

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

1678

1679

1680 CREATE FUNCTION "create_population_snapshot"

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

1682 RETURNS VOID

1683 LANGUAGE 'plpgsql' VOLATILE AS $$

1684 DECLARE

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

1686 BEGIN

1687 DELETE FROM "direct_population_snapshot"

1688 WHERE "issue_id" = "issue_id_p"

1689 AND "event" = 'periodic';

1690 DELETE FROM "delegating_population_snapshot"

1691 WHERE "issue_id" = "issue_id_p"

1692 AND "event" = 'periodic';

1693 INSERT INTO "direct_population_snapshot"

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

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

1696 "issue_id_p" AS "issue_id",

1697 'periodic' AS "event",

1698 "subquery"."member_id",

1699 "subquery"."interest_exists"

1700 FROM (

1701 SELECT

1702 "member"."id" AS "member_id",

1703 FALSE AS "interest_exists"

1704 FROM "issue"

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

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

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

1708 WHERE "issue"."id" = "issue_id_p"

1709 AND "member"."active"

1710 UNION

1711 SELECT

1712 "member"."id" AS "member_id",

1713 TRUE AS "interest_exists"

1714 FROM "interest" JOIN "member"

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

1716 WHERE "interest"."issue_id" = "issue_id_p"

1717 AND "member"."active"

1718 ) AS "subquery"

1719 ORDER BY

1720 "issue_id_p",

1721 "subquery"."member_id",

1722 "subquery"."interest_exists" DESC;

1723 FOR "member_id_v" IN

1724 SELECT "member_id" FROM "direct_population_snapshot"

1725 WHERE "issue_id" = "issue_id_p"

1726 AND "event" = 'periodic'

1727 LOOP

1728 UPDATE "direct_population_snapshot" SET

1729 "weight" = 1 +

1730 "weight_of_added_delegations_for_population_snapshot"(

1731 "issue_id_p",

1732 "member_id_v",

1733 '{}'

1734 )

1735 WHERE "issue_id" = "issue_id_p"

1736 AND "event" = 'periodic'

1737 AND "member_id" = "member_id_v";

1738 END LOOP;

1739 RETURN;

1740 END;

1741 $$;

1742

1743 COMMENT ON FUNCTION "create_population_snapshot"

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

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

1746

1747

1748 CREATE FUNCTION "weight_of_added_delegations_for_interest_snapshot"

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

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

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

1752 RETURNS "direct_interest_snapshot"."weight"%TYPE

1753 LANGUAGE 'plpgsql' VOLATILE AS $$

1754 DECLARE

1755 "issue_delegation_row" "issue_delegation"%ROWTYPE;

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

1757 "weight_v" INT4;

1758 "sub_weight_v" INT4;

1759 BEGIN

1760 "weight_v" := 0;

1761 FOR "issue_delegation_row" IN

1762 SELECT * FROM "issue_delegation"

1763 WHERE "trustee_id" = "member_id_p"

1764 AND "issue_id" = "issue_id_p"

1765 LOOP

1766 IF NOT EXISTS (

1767 SELECT NULL FROM "direct_interest_snapshot"

1768 WHERE "issue_id" = "issue_id_p"

1769 AND "event" = 'periodic'

1770 AND "member_id" = "issue_delegation_row"."truster_id"

1771 ) AND NOT EXISTS (

1772 SELECT NULL FROM "delegating_interest_snapshot"

1773 WHERE "issue_id" = "issue_id_p"

1774 AND "event" = 'periodic'

1775 AND "member_id" = "issue_delegation_row"."truster_id"

1776 ) THEN

1777 "delegate_member_ids_v" :=

1778 "member_id_p" || "delegate_member_ids_p";

1779 INSERT INTO "delegating_interest_snapshot"

1780 ("issue_id", "event", "member_id", "delegate_member_ids")

1781 VALUES (

1782 "issue_id_p",

1783 'periodic',

1784 "issue_delegation_row"."truster_id",

1785 "delegate_member_ids_v"

1786 );

1787 "sub_weight_v" := 1 +

1788 "weight_of_added_delegations_for_interest_snapshot"(

1789 "issue_id_p",

1790 "issue_delegation_row"."truster_id",

1791 "delegate_member_ids_v"

1792 );

1793 UPDATE "delegating_interest_snapshot"

1794 SET "weight" = "sub_weight_v"

1795 WHERE "issue_id" = "issue_id_p"

1796 AND "event" = 'periodic'

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

1798 "weight_v" := "weight_v" + "sub_weight_v";

1799 END IF;

1800 END LOOP;

1801 RETURN "weight_v";

1802 END;

1803 $$;

1804

1805 COMMENT ON FUNCTION "weight_of_added_delegations_for_interest_snapshot"

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

1807 "member"."id"%TYPE,

1808 "delegating_interest_snapshot"."delegate_member_ids"%TYPE )

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

1810

1811

1812 CREATE FUNCTION "create_interest_snapshot"

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

1814 RETURNS VOID

1815 LANGUAGE 'plpgsql' VOLATILE AS $$

1816 DECLARE

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

1818 BEGIN

1819 DELETE FROM "direct_interest_snapshot"

1820 WHERE "issue_id" = "issue_id_p"

1821 AND "event" = 'periodic';

1822 DELETE FROM "delegating_interest_snapshot"

1823 WHERE "issue_id" = "issue_id_p"

1824 AND "event" = 'periodic';

1825 DELETE FROM "direct_supporter_snapshot"

1826 WHERE "issue_id" = "issue_id_p"

1827 AND "event" = 'periodic';

1828 INSERT INTO "direct_interest_snapshot"

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

1830 SELECT

1831 "issue_id_p" AS "issue_id",

1832 'periodic' AS "event",

1833 "member"."id" AS "member_id",

1834 "interest"."voting_requested"

1835 FROM "interest" JOIN "member"

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

1837 WHERE "interest"."issue_id" = "issue_id_p"

1838 AND "member"."active";

1839 FOR "member_id_v" IN

1840 SELECT "member_id" FROM "direct_interest_snapshot"

1841 WHERE "issue_id" = "issue_id_p"

1842 AND "event" = 'periodic'

1843 LOOP

1844 UPDATE "direct_interest_snapshot" SET

1845 "weight" = 1 +

1846 "weight_of_added_delegations_for_interest_snapshot"(

1847 "issue_id_p",

1848 "member_id_v",

1849 '{}'

1850 )

1851 WHERE "issue_id" = "issue_id_p"

1852 AND "event" = 'periodic'

1853 AND "member_id" = "member_id_v";

1854 END LOOP;

1855 INSERT INTO "direct_supporter_snapshot"

1856 ( "issue_id", "initiative_id", "event", "member_id",

1857 "informed", "satisfied" )

1858 SELECT

1859 "issue_id_p" AS "issue_id",

1860 "initiative"."id" AS "initiative_id",

1861 'periodic' AS "event",

1862 "member"."id" AS "member_id",

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

1864 NOT EXISTS (

1865 SELECT NULL FROM "critical_opinion"

1866 WHERE "initiative_id" = "initiative"."id"

1867 AND "member_id" = "member"."id"

1868 ) AS "satisfied"

1869 FROM "supporter"

1870 JOIN "member"

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

1872 JOIN "initiative"

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

1874 JOIN "current_draft"

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

1876 JOIN "direct_interest_snapshot"

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

1878 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"

1879 AND "event" = 'periodic'

1880 WHERE "member"."active"

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

1882 RETURN;

1883 END;

1884 $$;

1885

1886 COMMENT ON FUNCTION "create_interest_snapshot"

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

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

1889

1890

1891 CREATE FUNCTION "create_snapshot"

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

1893 RETURNS VOID

1894 LANGUAGE 'plpgsql' VOLATILE AS $$

1895 DECLARE

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

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

1898 BEGIN

1899 PERFORM "global_lock"();

1900 PERFORM "create_population_snapshot"("issue_id_p");

1901 PERFORM "create_interest_snapshot"("issue_id_p");

1902 UPDATE "issue" SET

1903 "snapshot" = now(),

1904 "latest_snapshot_event" = 'periodic',

1905 "population" = (

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

1907 FROM "direct_population_snapshot"

1908 WHERE "issue_id" = "issue_id_p"

1909 AND "event" = 'periodic'

1910 ),

1911 "vote_now" = (

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" = TRUE

1917 ),

1918 "vote_later" = (

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

1920 FROM "direct_interest_snapshot"

1921 WHERE "issue_id" = "issue_id_p"

1922 AND "event" = 'periodic'

1923 AND "voting_requested" = FALSE

1924 )

1925 WHERE "id" = "issue_id_p";

1926 FOR "initiative_id_v" IN

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

1928 LOOP

1929 UPDATE "initiative" SET

1930 "supporter_count" = (

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

1932 FROM "direct_interest_snapshot" AS "di"

1933 JOIN "direct_supporter_snapshot" AS "ds"

1934 ON "di"."member_id" = "ds"."member_id"

1935 WHERE "di"."issue_id" = "issue_id_p"

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

1937 AND "ds"."initiative_id" = "initiative_id_v"

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

1939 ),

1940 "informed_supporter_count" = (

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

1942 FROM "direct_interest_snapshot" AS "di"

1943 JOIN "direct_supporter_snapshot" AS "ds"

1944 ON "di"."member_id" = "ds"."member_id"

1945 WHERE "di"."issue_id" = "issue_id_p"

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

1947 AND "ds"."initiative_id" = "initiative_id_v"

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

1949 AND "ds"."informed"

1950 ),

1951 "satisfied_supporter_count" = (

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

1953 FROM "direct_interest_snapshot" AS "di"

1954 JOIN "direct_supporter_snapshot" AS "ds"

1955 ON "di"."member_id" = "ds"."member_id"

1956 WHERE "di"."issue_id" = "issue_id_p"

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

1958 AND "ds"."initiative_id" = "initiative_id_v"

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

1960 AND "ds"."satisfied"

1961 ),

1962 "satisfied_informed_supporter_count" = (

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

1964 FROM "direct_interest_snapshot" AS "di"

1965 JOIN "direct_supporter_snapshot" AS "ds"

1966 ON "di"."member_id" = "ds"."member_id"

1967 WHERE "di"."issue_id" = "issue_id_p"

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

1969 AND "ds"."initiative_id" = "initiative_id_v"

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

1971 AND "ds"."informed"

1972 AND "ds"."satisfied"

1973 )

1974 WHERE "id" = "initiative_id_v";

1975 FOR "suggestion_id_v" IN

1976 SELECT "id" FROM "suggestion"

1977 WHERE "initiative_id" = "initiative_id_v"

1978 LOOP

1979 UPDATE "suggestion" SET

1980 "minus2_unfulfilled_count" = (

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

1982 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"

1983 ON "opinion"."member_id" = "snapshot"."member_id"

1984 WHERE "opinion"."suggestion_id" = "suggestion_id_v"

1985 AND "snapshot"."issue_id" = "issue_id_p"

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

1987 AND "opinion"."fulfilled" = FALSE

1988 ),

1989 "minus2_fulfilled_count" = (

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

1991 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"

1992 ON "opinion"."member_id" = "snapshot"."member_id"

1993 WHERE "opinion"."suggestion_id" = "suggestion_id_v"

1994 AND "snapshot"."issue_id" = "issue_id_p"

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

1996 AND "opinion"."fulfilled" = TRUE

1997 ),

1998 "minus1_unfulfilled_count" = (

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

2000 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"

2001 ON "opinion"."member_id" = "snapshot"."member_id"

2002 WHERE "opinion"."suggestion_id" = "suggestion_id_v"

2003 AND "snapshot"."issue_id" = "issue_id_p"

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

2005 AND "opinion"."fulfilled" = FALSE

2006 ),

2007 "minus1_fulfilled_count" = (

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

2009 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"

2010 ON "opinion"."member_id" = "snapshot"."member_id"

2011 WHERE "opinion"."suggestion_id" = "suggestion_id_v"

2012 AND "snapshot"."issue_id" = "issue_id_p"

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

2014 AND "opinion"."fulfilled" = TRUE

2015 ),

2016 "plus1_unfulfilled_count" = (

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

2018 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"

2019 ON "opinion"."member_id" = "snapshot"."member_id"

2020 WHERE "opinion"."suggestion_id" = "suggestion_id_v"

2021 AND "snapshot"."issue_id" = "issue_id_p"

2022 AND "opinion"."degree" = 1

2023 AND "opinion"."fulfilled" = FALSE

2024 ),

2025 "plus1_fulfilled_count" = (

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

2027 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"

2028 ON "opinion"."member_id" = "snapshot"."member_id"

2029 WHERE "opinion"."suggestion_id" = "suggestion_id_v"

2030 AND "snapshot"."issue_id" = "issue_id_p"

2031 AND "opinion"."degree" = 1

2032 AND "opinion"."fulfilled" = TRUE

2033 ),

2034 "plus2_unfulfilled_count" = (

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

2036 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"

2037 ON "opinion"."member_id" = "snapshot"."member_id"

2038 WHERE "opinion"."suggestion_id" = "suggestion_id_v"

2039 AND "snapshot"."issue_id" = "issue_id_p"

2040 AND "opinion"."degree" = 2

2041 AND "opinion"."fulfilled" = FALSE

2042 ),

2043 "plus2_fulfilled_count" = (

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

2045 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"

2046 ON "opinion"."member_id" = "snapshot"."member_id"

2047 WHERE "opinion"."suggestion_id" = "suggestion_id_v"

2048 AND "snapshot"."issue_id" = "issue_id_p"

2049 AND "opinion"."degree" = 2

2050 AND "opinion"."fulfilled" = TRUE

2051 )

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

2053 END LOOP;

2054 END LOOP;

2055 RETURN;

2056 END;

2057 $$;

2058

2059 COMMENT ON FUNCTION "create_snapshot"

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

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

2062

2063

2064 CREATE FUNCTION "set_snapshot_event"

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

2066 "event_p" "snapshot_event" )

2067 RETURNS VOID

2068 LANGUAGE 'plpgsql' VOLATILE AS $$

2069 BEGIN

2070 UPDATE "issue" SET "latest_snapshot_event" = "event_p"

2071 WHERE "id" = "issue_id_p";

2072 UPDATE "direct_population_snapshot" SET "event" = "event_p"

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

2074 UPDATE "delegating_population_snapshot" SET "event" = "event_p"

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

2076 UPDATE "direct_interest_snapshot" SET "event" = "event_p"

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

2078 UPDATE "delegating_interest_snapshot" SET "event" = "event_p"

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

2080 UPDATE "direct_supporter_snapshot" SET "event" = "event_p"

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

2082 RETURN;

2083 END;

2084 $$;

2085

2086 COMMENT ON FUNCTION "set_snapshot_event"

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

2088 "snapshot_event" )

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

2090

2091

2092

2093 ---------------------

2094 -- Freezing issues --

2095 ---------------------

2096

2097 CREATE FUNCTION "freeze_after_snapshot"

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

2099 RETURNS VOID

2100 LANGUAGE 'plpgsql' VOLATILE AS $$

2101 DECLARE

2102 "issue_row" "issue"%ROWTYPE;

2103 "policy_row" "policy"%ROWTYPE;

2104 "initiative_row" "initiative"%ROWTYPE;

2105 BEGIN

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

2107 SELECT * INTO "policy_row"

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

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

2110 UPDATE "issue" SET

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

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

2113 "fully_frozen" = now()

2114 WHERE "id" = "issue_id_p";

2115 FOR "initiative_row" IN

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

2117 LOOP

2118 IF

2119 "initiative_row"."satisfied_supporter_count" > 0 AND

2120 "initiative_row"."satisfied_supporter_count" *

2121 "policy_row"."initiative_quorum_den" >=

2122 "issue_row"."population" * "policy_row"."initiative_quorum_num"

2123 THEN

2124 UPDATE "initiative" SET "admitted" = TRUE

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

2126 ELSE

2127 UPDATE "initiative" SET "admitted" = FALSE

2128 WHERE "id" = "initiative_row"."id";

2129 END IF;

2130 END LOOP;

2131 IF NOT EXISTS (

2132 SELECT NULL FROM "initiative"

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

2134 ) THEN

2135 PERFORM "close_voting"("issue_id_p");

2136 END IF;

2137 RETURN;

2138 END;

2139 $$;

2140

2141 COMMENT ON FUNCTION "freeze_after_snapshot"

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

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

2144

2145

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

2147 RETURNS VOID

2148 LANGUAGE 'plpgsql' VOLATILE AS $$

2149 DECLARE

2150 "issue_row" "issue"%ROWTYPE;

2151 BEGIN

2152 PERFORM "create_snapshot"("issue_id_p");

2153 PERFORM "freeze_after_snapshot"("issue_id_p");

2154 RETURN;

2155 END;

2156 $$;

2157

2158 COMMENT ON FUNCTION "freeze_after_snapshot"

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

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

2161

2162

2163

2164 -----------------------

2165 -- Counting of votes --

2166 -----------------------

2167

2168

2169 CREATE FUNCTION "weight_of_added_vote_delegations"

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

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

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

2173 RETURNS "direct_voter"."weight"%TYPE

2174 LANGUAGE 'plpgsql' VOLATILE AS $$

2175 DECLARE

2176 "issue_delegation_row" "issue_delegation"%ROWTYPE;

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

2178 "weight_v" INT4;

2179 "sub_weight_v" INT4;

2180 BEGIN

2181 "weight_v" := 0;

2182 FOR "issue_delegation_row" IN

2183 SELECT * FROM "issue_delegation"

2184 WHERE "trustee_id" = "member_id_p"

2185 AND "issue_id" = "issue_id_p"

2186 LOOP

2187 IF NOT EXISTS (

2188 SELECT NULL FROM "direct_voter"

2189 WHERE "member_id" = "issue_delegation_row"."truster_id"

2190 AND "issue_id" = "issue_id_p"

2191 ) AND NOT EXISTS (

2192 SELECT NULL FROM "delegating_voter"

2193 WHERE "member_id" = "issue_delegation_row"."truster_id"

2194 AND "issue_id" = "issue_id_p"

2195 ) THEN

2196 "delegate_member_ids_v" :=

2197 "member_id_p" || "delegate_member_ids_p";

2198 INSERT INTO "delegating_voter"

2199 ("issue_id", "member_id", "delegate_member_ids")

2200 VALUES (

2201 "issue_id_p",

2202 "issue_delegation_row"."truster_id",

2203 "delegate_member_ids_v"

2204 );

2205 "sub_weight_v" := 1 +

2206 "weight_of_added_vote_delegations"(

2207 "issue_id_p",

2208 "issue_delegation_row"."truster_id",

2209 "delegate_member_ids_v"

2210 );

2211 UPDATE "delegating_voter"

2212 SET "weight" = "sub_weight_v"

2213 WHERE "issue_id" = "issue_id_p"

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

2215 "weight_v" := "weight_v" + "sub_weight_v";

2216 END IF;

2217 END LOOP;

2218 RETURN "weight_v";

2219 END;

2220 $$;

2221

2222 COMMENT ON FUNCTION "weight_of_added_vote_delegations"

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

2224 "member"."id"%TYPE,

2225 "delegating_voter"."delegate_member_ids"%TYPE )

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

2227

2228

2229 CREATE FUNCTION "add_vote_delegations"

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

2231 RETURNS VOID

2232 LANGUAGE 'plpgsql' VOLATILE AS $$

2233 DECLARE

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

2235 BEGIN

2236 FOR "member_id_v" IN

2237 SELECT "member_id" FROM "direct_voter"

2238 WHERE "issue_id" = "issue_id_p"

2239 LOOP

2240 UPDATE "direct_voter" SET

2241 "weight" = "weight" + "weight_of_added_vote_delegations"(

2242 "issue_id_p",

2243 "member_id_v",

2244 '{}'

2245 )

2246 WHERE "member_id" = "member_id_v"

2247 AND "issue_id" = "issue_id_p";

2248 END LOOP;

2249 RETURN;

2250 END;

2251 $$;

2252

2253 COMMENT ON FUNCTION "add_vote_delegations"

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

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

2256

2257

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

2259 RETURNS VOID

2260 LANGUAGE 'plpgsql' VOLATILE AS $$

2261 DECLARE

2262 "issue_row" "issue"%ROWTYPE;

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

2264 BEGIN

2265 PERFORM "global_lock"();

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

2267 DELETE FROM "delegating_voter"

2268 WHERE "issue_id" = "issue_id_p";

2269 DELETE FROM "direct_voter"

2270 WHERE "issue_id" = "issue_id_p"

2271 AND "autoreject" = TRUE;

2272 DELETE FROM "direct_voter" USING "member"

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

2274 AND "direct_voter"."issue_id" = "issue_id_p"

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

2276 UPDATE "direct_voter" SET "weight" = 1

2277 WHERE "issue_id" = "issue_id_p";

2278 PERFORM "add_vote_delegations"("issue_id_p");

2279 FOR "member_id_v" IN

2280 SELECT "interest"."member_id"

2281 FROM "interest"

2282 LEFT JOIN "direct_voter"

2283 ON "interest"."member_id" = "direct_voter"."member_id"

2284 AND "interest"."issue_id" = "direct_voter"."issue_id"

2285 LEFT JOIN "delegating_voter"

2286 ON "interest"."member_id" = "delegating_voter"."member_id"

2287 AND "interest"."issue_id" = "delegating_voter"."issue_id"

2288 WHERE "interest"."issue_id" = "issue_id_p"

2289 AND "interest"."autoreject" = TRUE

2290 AND "direct_voter"."member_id" ISNULL

2291 AND "delegating_voter"."member_id" ISNULL

2292 UNION SELECT "membership"."member_id"

2293 FROM "membership"

2294 LEFT JOIN "interest"

2295 ON "membership"."member_id" = "interest"."member_id"

2296 AND "interest"."issue_id" = "issue_id_p"

2297 LEFT JOIN "direct_voter"

2298 ON "membership"."member_id" = "direct_voter"."member_id"

2299 AND "direct_voter"."issue_id" = "issue_id_p"

2300 LEFT JOIN "delegating_voter"

2301 ON "membership"."member_id" = "delegating_voter"."member_id"

2302 AND "delegating_voter"."issue_id" = "issue_id_p"

2303 WHERE "membership"."area_id" = "issue_row"."area_id"

2304 AND "membership"."autoreject" = TRUE

2305 AND "interest"."autoreject" ISNULL

2306 AND "direct_voter"."member_id" ISNULL

2307 AND "delegating_voter"."member_id" ISNULL

2308 LOOP

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

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

2311 INSERT INTO "vote" (

2312 "member_id",

2313 "issue_id",

2314 "initiative_id",

2315 "grade"

2316 ) SELECT

2317 "member_id_v" AS "member_id",

2318 "issue_id_p" AS "issue_id",

2319 "id" AS "initiative_id",

2320 -1 AS "grade"

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

2322 END LOOP;

2323 PERFORM "add_vote_delegations"("issue_id_p");

2324 UPDATE "issue" SET

2325 "voter_count" = (

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

2327 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"

2328 )

2329 WHERE "id" = "issue_id_p";

2330 UPDATE "initiative" SET

2331 "positive_votes" = "subquery"."positive_votes",

2332 "negative_votes" = "subquery"."negative_votes"

2333 FROM (

2334 SELECT

2335 "initiative"."id" AS "initiative_id",

2336 coalesce(

2337 sum(

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

2339 ),

2340 0

2341 ) AS "positive_votes",

2342 coalesce(

2343 sum(

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

2345 ),

2346 0

2347 ) AS "negative_votes"

2348 FROM "initiative"

2349 LEFT JOIN "direct_voter"

2350 ON "direct_voter"."issue_id" = "initiative"."issue_id"

2351 LEFT JOIN "vote"

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

2353 AND "vote"."member_id" = "direct_voter"."member_id"

2354 WHERE "initiative"."issue_id" = "issue_id_p"

2355 GROUP BY "initiative"."id"

2356 ) AS "subquery"

2357 WHERE "initiative"."admitted"

2358 AND "initiative"."id" = "subquery"."initiative_id";

2359 UPDATE "issue" SET "closed" = now() WHERE "id" = "issue_id_p";

2360 END;

2361 $$;

2362

2363 COMMENT ON FUNCTION "close_voting"

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

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

2366

2367

2368 CREATE FUNCTION "init_array"("dim_p" INTEGER)

2369 RETURNS INT4[]

2370 LANGUAGE 'plpgsql' IMMUTABLE AS $$

2371 DECLARE

2372 "i" INTEGER;

2373 "ary_text_v" TEXT;

2374 BEGIN

2375 IF "dim_p" >= 1 THEN

2376 "ary_text_v" := '{NULL';

2377 "i" := "dim_p";

2378 LOOP

2379 "i" := "i" - 1;

2380 EXIT WHEN "i" = 0;

2381 "ary_text_v" := "ary_text_v" || ',NULL';

2382 END LOOP;

2383 "ary_text_v" := "ary_text_v" || '}';

2384 RETURN "ary_text_v"::INT4[][];

2385 ELSE

2386 RAISE EXCEPTION 'Dimension needs to be at least 1.';

2387 END IF;

2388 END;

2389 $$;

2390

2391 COMMENT ON FUNCTION "init_array"(INTEGER) IS 'Needed for PostgreSQL < 8.4, due to missing "array_fill" function';

2392

2393

2394 CREATE FUNCTION "init_square_matrix"("dim_p" INTEGER)

2395 RETURNS INT4[][]

2396 LANGUAGE 'plpgsql' IMMUTABLE AS $$

2397 DECLARE

2398 "i" INTEGER;

2399 "row_text_v" TEXT;

2400 "ary_text_v" TEXT;

2401 BEGIN

2402 IF "dim_p" >= 1 THEN

2403 "row_text_v" := '{NULL';

2404 "i" := "dim_p";

2405 LOOP

2406 "i" := "i" - 1;

2407 EXIT WHEN "i" = 0;

2408 "row_text_v" := "row_text_v" || ',NULL';

2409 END LOOP;

2410 "row_text_v" := "row_text_v" || '}';

2411 "ary_text_v" := '{' || "row_text_v";

2412 "i" := "dim_p";

2413 LOOP

2414 "i" := "i" - 1;

2415 EXIT WHEN "i" = 0;

2416 "ary_text_v" := "ary_text_v" || ',' || "row_text_v";

2417 END LOOP;

2418 "ary_text_v" := "ary_text_v" || '}';

2419 RETURN "ary_text_v"::INT4[][];

2420 ELSE

2421 RAISE EXCEPTION 'Dimension needs to be at least 1.';

2422 END IF;

2423 END;

2424 $$;

2425

2426 COMMENT ON FUNCTION "init_square_matrix"(INTEGER) IS 'Needed for PostgreSQL < 8.4, due to missing "array_fill" function';

2427

2428

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

2430 RETURNS VOID

2431 LANGUAGE 'plpgsql' VOLATILE AS $$

2432 DECLARE

2433 "dimension_v" INTEGER;

2434 "matrix" INT4[][];

2435 "i" INTEGER;

2436 "j" INTEGER;

2437 "k" INTEGER;

2438 "battle_row" "battle"%ROWTYPE;

2439 "rank_ary" INT4[];

2440 "rank_v" INT4;

2441 "done_v" INTEGER;

2442 "winners_ary" INTEGER[];

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

2444 BEGIN

2445 PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE;

2446 -- Prepare matrix for Schulze-Method:

2447 SELECT count(1) INTO "dimension_v"

2448 FROM "battle_participant" WHERE "issue_id" = "issue_id_p";

2449 IF "dimension_v" = 1 THEN

2450 UPDATE "initiative" SET

2451 "rank" = 1

2452 FROM "battle_participant"

2453 WHERE "initiative"."issue_id" = "issue_id_p"

2454 AND "initiative"."id" = "battle_participant"."initiative_id";

2455 ELSIF "dimension_v" > 1 THEN

2456 "matrix" := "init_square_matrix"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4)

2457 "i" := 1;

2458 "j" := 2;

2459 -- Fill matrix with data from "battle" view

2460 FOR "battle_row" IN

2461 SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p"

2462 ORDER BY "winning_initiative_id", "losing_initiative_id"

2463 LOOP

2464 "matrix"["i"]["j"] := "battle_row"."count";

2465 IF "j" = "dimension_v" THEN

2466 "i" := "i" + 1;

2467 "j" := 1;

2468 ELSE

2469 "j" := "j" + 1;

2470 IF "j" = "i" THEN

2471 "j" := "j" + 1;

2472 END IF;

2473 END IF;

2474 END LOOP;

2475 IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN

2476 RAISE EXCEPTION 'Wrong battle count (should not happen)';

2477 END IF;

2478 -- Delete losers from matrix:

2479 "i" := 1;

2480 LOOP

2481 "j" := "i" + 1;

2482 LOOP

2483 IF "i" != "j" THEN

2484 IF "matrix"["i"]["j"] < "matrix"["j"]["i"] THEN

2485 "matrix"["i"]["j"] := 0;

2486 ELSIF matrix[j][i] < matrix[i][j] THEN

2487 "matrix"["j"]["i"] := 0;

2488 ELSE

2489 "matrix"["i"]["j"] := 0;

2490 "matrix"["j"]["i"] := 0;

2491 END IF;

2492 END IF;

2493 EXIT WHEN "j" = "dimension_v";

2494 "j" := "j" + 1;

2495 END LOOP;

2496 EXIT WHEN "i" = "dimension_v" - 1;

2497 "i" := "i" + 1;

2498 END LOOP;

2499 -- Find best paths:

2500 "i" := 1;

2501 LOOP

2502 "j" := 1;

2503 LOOP

2504 IF "i" != "j" THEN

2505 "k" := 1;

2506 LOOP

2507 IF "i" != "k" AND "j" != "k" THEN

2508 IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN

2509 IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN

2510 "matrix"["j"]["k"] := "matrix"["j"]["i"];

2511 END IF;

2512 ELSE

2513 IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN

2514 "matrix"["j"]["k"] := "matrix"["i"]["k"];

2515 END IF;

2516 END IF;

2517 END IF;

2518 EXIT WHEN "k" = "dimension_v";

2519 "k" := "k" + 1;

2520 END LOOP;

2521 END IF;

2522 EXIT WHEN "j" = "dimension_v";

2523 "j" := "j" + 1;

2524 END LOOP;

2525 EXIT WHEN "i" = "dimension_v";

2526 "i" := "i" + 1;

2527 END LOOP;

2528 -- Determine order of winners:

2529 "rank_ary" := "init_array"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4)

2530 "rank_v" := 1;

2531 "done_v" := 0;

2532 LOOP

2533 "winners_ary" := '{}';

2534 "i" := 1;

2535 LOOP

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

2537 "j" := 1;

2538 LOOP

2539 IF

2540 "i" != "j" AND

2541 "rank_ary"["j"] ISNULL AND

2542 "matrix"["j"]["i"] > "matrix"["i"]["j"]

2543 THEN

2544 -- someone else is better

2545 EXIT;

2546 END IF;

2547 IF "j" = "dimension_v" THEN

2548 -- noone is better

2549 "winners_ary" := "winners_ary" || "i";

2550 EXIT;

2551 END IF;

2552 "j" := "j" + 1;

2553 END LOOP;

2554 END IF;

2555 EXIT WHEN "i" = "dimension_v";

2556 "i" := "i" + 1;

2557 END LOOP;

2558 "i" := 1;

2559 LOOP

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

2561 "done_v" := "done_v" + 1;

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

2563 "i" := "i" + 1;

2564 END LOOP;

2565 EXIT WHEN "done_v" = "dimension_v";

2566 "rank_v" := "rank_v" + 1;

2567 END LOOP;

2568 -- write preliminary ranks:

2569 "i" := 1;

2570 FOR "initiative_id_v" IN

2571 SELECT "initiative"."id"

2572 FROM "initiative" JOIN "battle_participant"

2573 ON "initiative"."id" = "battle_participant"."initiative_id"

2574 WHERE "initiative"."issue_id" = "issue_id_p"

2575 ORDER BY "initiative"."id"

2576 LOOP

2577 UPDATE "initiative" SET "rank" = "rank_ary"["i"]

2578 WHERE "id" = "initiative_id_v";

2579 "i" := "i" + 1;

2580 END LOOP;

2581 IF "i" != "dimension_v" + 1 THEN

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

2583 END IF;

2584 -- straighten ranks (start counting with 1, no equal ranks):

2585 "rank_v" := 1;

2586 FOR "initiative_id_v" IN

2587 SELECT "id" FROM "initiative"

2588 WHERE "issue_id" = "issue_id_p" AND "rank" NOTNULL

2589 ORDER BY

2590 "rank",

2591 "vote_ratio"("positive_votes", "negative_votes") DESC,

2592 "id"

2593 LOOP

2594 UPDATE "initiative" SET "rank" = "rank_v"

2595 WHERE "id" = "initiative_id_v";

2596 "rank_v" := "rank_v" + 1;

2597 END LOOP;

2598 END IF;

2599 -- mark issue as finished

2600 UPDATE "issue" SET "ranks_available" = TRUE

2601 WHERE "id" = "issue_id_p";

2602 RETURN;

2603 END;

2604 $$;

2605

2606 COMMENT ON FUNCTION "calculate_ranks"

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

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

2609

2610

2611

2612 -----------------------------

2613 -- Automatic state changes --

2614 -----------------------------

2615

2616

2617 CREATE FUNCTION "check_issue"

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

2619 RETURNS VOID

2620 LANGUAGE 'plpgsql' VOLATILE AS $$

2621 DECLARE

2622 "issue_row" "issue"%ROWTYPE;

2623 "policy_row" "policy"%ROWTYPE;

2624 "voting_requested_v" BOOLEAN;

2625 BEGIN

2626 PERFORM "global_lock"();

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

2628 IF "issue_row"."closed" ISNULL THEN

2629 SELECT * INTO "policy_row" FROM "policy"

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

2631 IF "issue_row"."fully_frozen" ISNULL THEN

2632 PERFORM "create_snapshot"("issue_id_p");

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

2634 END IF;

2635 IF "issue_row"."accepted" ISNULL THEN

2636 IF EXISTS (

2637 SELECT NULL FROM "initiative"

2638 WHERE "issue_id" = "issue_id_p"

2639 AND "supporter_count" > 0

2640 AND "supporter_count" * "policy_row"."issue_quorum_den"

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

2642 ) THEN

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

2644 "issue_row"."accepted" = now(); -- NOTE: "issue_row" used later

2645 UPDATE "issue" SET "accepted" = "issue_row"."accepted"

2646 WHERE "id" = "issue_row"."id";

2647 ELSIF

2648 now() >= "issue_row"."created" + "policy_row"."admission_time"

2649 THEN

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

2651 UPDATE "issue" SET "closed" = now()

2652 WHERE "id" = "issue_row"."id";

2653 END IF;

2654 END IF;

2655 IF

2656 "issue_row"."accepted" NOTNULL AND

2657 "issue_row"."half_frozen" ISNULL

2658 THEN

2659 SELECT

2660 CASE

2661 WHEN "vote_now" * 2 > "issue_row"."population" THEN

2662 TRUE

2663 WHEN "vote_later" * 2 > "issue_row"."population" THEN

2664 FALSE

2665 ELSE NULL

2666 END

2667 INTO "voting_requested_v"

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

2669 IF

2670 "voting_requested_v" OR (

2671 "voting_requested_v" ISNULL AND

2672 now() >= "issue_row"."accepted" + "policy_row"."discussion_time"

2673 )

2674 THEN

2675 "issue_row"."half_frozen" = now(); -- NOTE: "issue_row" used later

2676 UPDATE "issue" SET "half_frozen" = "issue_row"."half_frozen"

2677 WHERE "id" = "issue_row"."id";

2678 END IF;

2679 END IF;

2680 IF

2681 "issue_row"."half_frozen" NOTNULL AND

2682 "issue_row"."fully_frozen" ISNULL AND

2683 now() >= "issue_row"."half_frozen" + "policy_row"."verification_time"

2684 THEN

2685 PERFORM "freeze_after_snapshot"("issue_id_p");

2686 -- "issue" might change, thus "issue_row" has to be updated below

2687 END IF;

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

2689 IF

2690 "issue_row"."closed" ISNULL AND

2691 "issue_row"."fully_frozen" NOTNULL AND

2692 now() >= "issue_row"."fully_frozen" + "policy_row"."voting_time"

2693 THEN

2694 PERFORM "close_voting"("issue_id_p");

2695 END IF;

2696 END IF;

2697 RETURN;

2698 END;

2699 $$;

2700

2701 COMMENT ON FUNCTION "check_issue"

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

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

2704

2705

2706 CREATE FUNCTION "check_everything"()

2707 RETURNS VOID

2708 LANGUAGE 'plpgsql' VOLATILE AS $$

2709 DECLARE

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

2711 BEGIN

2712 DELETE FROM "expired_session";

2713 PERFORM "calculate_member_counts"();

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

2715 PERFORM "check_issue"("issue_id_v");

2716 END LOOP;

2717 FOR "issue_id_v" IN SELECT "id" FROM "issue_with_ranks_missing" LOOP

2718 PERFORM "calculate_ranks"("issue_id_v");

2719 END LOOP;

2720 RETURN;

2721 END;

2722 $$;

2723

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

2725

2726

2727

2728 ------------------------------

2729 -- Deletion of private data --

2730 ------------------------------

2731

2732

2733 CREATE FUNCTION "delete_private_data"()

2734 RETURNS VOID

2735 LANGUAGE 'plpgsql' VOLATILE AS $$

2736 DECLARE

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

2738 BEGIN

2739 UPDATE "member" SET

2740 "login" = 'login' || "id"::text,

2741 "password" = NULL,

2742 "notify_email" = NULL,

2743 "notify_email_confirmed" = NULL;

2744 DELETE FROM "session";

2745 DELETE FROM "invite_code";

2746 DELETE FROM "contact" WHERE NOT "public";

2747 DELETE FROM "direct_voter" USING "issue"

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

2749 AND "issue"."closed" ISNULL;

2750 RETURN;

2751 END;

2752 $$;

2753

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

2755

2756

2757

2758 COMMIT;