liquid_feedback_core: e6faf5ff83af core.sql

liquid_feedback_core

view core.sql @ 8:e6faf5ff83af

Version beta9

Added missing indicies on TSVECTOR fields

New field latest_snapshot_event in table issue specifying the type of the latest snapshot taken

Added weight field also for (intermediate) delegating members in snapshot and voter tables

Possibility for an initiative to specify a URL where an external discussion takes place (discussion_url)

Export concept for creating a database dump, without some non-public information (e.g. private contact data), including:
- Shell script lf_export
- Database function delete_private_data()

Field in member table to be used by a frontend to store information about hints being hidden by the user

Minor cleanup / New comments

author	jbe
date	Mon Nov 30 12:00:00 2009 +0100 (2009-11-30)
parents	69d84040fb93
children	4af4df1415f9

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 ('beta9', 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 "hidden_hints" TEXT[],

63 "notify_email" TEXT,

64 "notify_email_confirmed" BOOLEAN,

65 "name" TEXT NOT NULL UNIQUE,

66 "identification" TEXT UNIQUE,

67 "organizational_unit" TEXT,

68 "internal_posts" TEXT,

69 "realname" TEXT,

70 "birthday" DATE,

71 "address" TEXT,

72 "email" TEXT,

73 "xmpp_address" TEXT,

74 "website" TEXT,

75 "phone" TEXT,

76 "mobile_phone" TEXT,

77 "profession" TEXT,

78 "external_memberships" TEXT,

79 "external_posts" TEXT,

80 "statement" TEXT,

81 "text_search_data" TSVECTOR,

82 CONSTRAINT "notify_email_null_check"

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

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

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

86 CREATE TRIGGER "update_text_search_data"

87 BEFORE INSERT OR UPDATE ON "member"

88 FOR EACH ROW EXECUTE PROCEDURE

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

90 "name", "identification", "organizational_unit", "internal_posts",

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

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

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

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

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

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

99 COMMENT ON COLUMN "member"."hidden_hints" IS 'This field may be used by a frontend to store identification strings for introductory hints, which the user wants to hide.';

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

101 COMMENT ON COLUMN "member"."notify_email_confirmed" IS 'TRUE, if "notify_email" has been confirmed';

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

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

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

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

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

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

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

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

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

111

112

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

114

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

116

117

118

119 CREATE TABLE "member_image" (

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

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

122 "image_type" "member_image_type",

123 "scaled" BOOLEAN,

124 "content_type" TEXT,

125 "data" BYTEA NOT NULL );

126

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

128

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

130

131

132 CREATE TABLE "member_count" (

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

134 "total_count" INT4 NOT NULL );

135

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

137

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

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

140

141

142 CREATE TABLE "contact" (

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

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

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

146 "public" BOOLEAN NOT NULL DEFAULT FALSE );

147

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

149

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

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

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

153

154

155 CREATE TABLE "session" (

156 "ident" TEXT PRIMARY KEY,

157 "additional_secret" TEXT,

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

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

160 "lang" TEXT );

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

162

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

164

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

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

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

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

169

170

171 CREATE TABLE "policy" (

172 "id" SERIAL4 PRIMARY KEY,

173 "active" BOOLEAN NOT NULL DEFAULT TRUE,

174 "name" TEXT NOT NULL UNIQUE,

175 "description" TEXT NOT NULL DEFAULT '',

176 "admission_time" INTERVAL NOT NULL,

177 "discussion_time" INTERVAL NOT NULL,

178 "verification_time" INTERVAL NOT NULL,

179 "voting_time" INTERVAL NOT NULL,

180 "issue_quorum_num" INT4 NOT NULL,

181 "issue_quorum_den" INT4 NOT NULL,

182 "initiative_quorum_num" INT4 NOT NULL,

183 "initiative_quorum_den" INT4 NOT NULL );

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

185

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

187

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

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

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

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

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

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

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

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

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

197

198

199 CREATE TABLE "area" (

200 "id" SERIAL4 PRIMARY KEY,

201 "active" BOOLEAN NOT NULL DEFAULT TRUE,

202 "name" TEXT NOT NULL,

203 "description" TEXT NOT NULL DEFAULT '',

204 "direct_member_count" INT4,

205 "member_weight" INT4,

206 "autoreject_weight" INT4,

207 "text_search_data" TSVECTOR );

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

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

210 CREATE TRIGGER "update_text_search_data"

211 BEFORE INSERT OR UPDATE ON "area"

212 FOR EACH ROW EXECUTE PROCEDURE

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

214 "name", "description" );

215

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

217

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

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

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

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

222

223

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

225

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

227

228

229 CREATE TABLE "issue" (

230 "id" SERIAL4 PRIMARY KEY,

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

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

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

234 "accepted" TIMESTAMPTZ,

235 "half_frozen" TIMESTAMPTZ,

236 "fully_frozen" TIMESTAMPTZ,

237 "closed" TIMESTAMPTZ,

238 "ranks_available" BOOLEAN NOT NULL DEFAULT FALSE,

239 "snapshot" TIMESTAMPTZ,

240 "latest_snapshot_event" "snapshot_event",

241 "population" INT4,

242 "vote_now" INT4,

243 "vote_later" INT4,

244 "voter_count" INT4,

245 CONSTRAINT "valid_state" CHECK (

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

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

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

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

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

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

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

253 CONSTRAINT "state_change_order" CHECK (

254 "created" <= "accepted" AND

255 "accepted" <= "half_frozen" AND

256 "half_frozen" <= "fully_frozen" AND

257 "fully_frozen" <= "closed" ),

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

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

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

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

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

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

264

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

266

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

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

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

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

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

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

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

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

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

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

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

278

279

280 CREATE TABLE "initiative" (

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

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

283 "id" SERIAL4 PRIMARY KEY,

284 "name" TEXT NOT NULL,

285 "discussion_url" TEXT,

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

287 "revoked" TIMESTAMPTZ,

288 "admitted" BOOLEAN,

289 "supporter_count" INT4,

290 "informed_supporter_count" INT4,

291 "satisfied_supporter_count" INT4,

292 "satisfied_informed_supporter_count" INT4,

293 "positive_votes" INT4,

294 "negative_votes" INT4,

295 "rank" INT4,

296 "text_search_data" TSVECTOR,

297 CONSTRAINT "revoked_initiatives_cant_be_admitted"

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

299 CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results"

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

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

302 CREATE TRIGGER "update_text_search_data"

303 BEFORE INSERT OR UPDATE ON "initiative"

304 FOR EACH ROW EXECUTE PROCEDURE

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

306 "name", "discussion_url");

307

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

309

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

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

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

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

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

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

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

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

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

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

320

321

322 CREATE TABLE "draft" (

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

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

325 "id" SERIAL8 PRIMARY KEY,

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

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

328 "content" TEXT NOT NULL,

329 "text_search_data" TSVECTOR );

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

331 CREATE TRIGGER "update_text_search_data"

332 BEFORE INSERT OR UPDATE ON "draft"

333 FOR EACH ROW EXECUTE PROCEDURE

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

335

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

337

338

339 CREATE TABLE "suggestion" (

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

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

342 "id" SERIAL8 PRIMARY KEY,

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

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

345 "name" TEXT NOT NULL,

346 "description" TEXT NOT NULL DEFAULT '',

347 "text_search_data" TSVECTOR,

348 "minus2_unfulfilled_count" INT4,

349 "minus2_fulfilled_count" INT4,

350 "minus1_unfulfilled_count" INT4,

351 "minus1_fulfilled_count" INT4,

352 "plus1_unfulfilled_count" INT4,

353 "plus1_fulfilled_count" INT4,

354 "plus2_unfulfilled_count" INT4,

355 "plus2_fulfilled_count" INT4 );

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

357 CREATE TRIGGER "update_text_search_data"

358 BEFORE INSERT OR UPDATE ON "suggestion"

359 FOR EACH ROW EXECUTE PROCEDURE

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

361 "name", "description");

362

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

364

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

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

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

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

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

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

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

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

373

374

375 CREATE TABLE "membership" (

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

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

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

379 "autoreject" BOOLEAN NOT NULL DEFAULT FALSE );

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

381

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

383

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

385

386

387 CREATE TABLE "interest" (

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

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

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

391 "autoreject" BOOLEAN NOT NULL,

392 "voting_requested" BOOLEAN );

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

394

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

396

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

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

399

400

401 CREATE TABLE "initiator" (

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

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

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

405 "accepted" BOOLEAN NOT NULL DEFAULT TRUE );

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

407

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

409

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

411

412

413 CREATE TABLE "supporter" (

414 "issue_id" INT4 NOT NULL,

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

416 "initiative_id" INT4,

417 "member_id" INT4,

418 "draft_id" INT8 NOT NULL,

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

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

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

422

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

424

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

426

427

428 CREATE TABLE "opinion" (

429 "initiative_id" INT4 NOT NULL,

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

431 "suggestion_id" INT8,

432 "member_id" INT4,

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

434 "fulfilled" BOOLEAN NOT NULL DEFAULT FALSE,

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

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

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

438

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

440

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

442

443

444 CREATE TABLE "delegation" (

445 "id" SERIAL8 PRIMARY KEY,

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

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

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

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

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

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

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

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

454 CREATE UNIQUE INDEX "delegation_default_truster_id_trustee_id_unique_idx"

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

456 WHERE "area_id" ISNULL AND "issue_id" ISNULL;

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

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

459

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

461

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

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

464

465

466 CREATE TABLE "direct_population_snapshot" (

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

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

469 "event" "snapshot_event",

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

471 "weight" INT4,

472 "interest_exists" BOOLEAN NOT NULL );

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

474

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

476

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

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

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

480

481

482 CREATE TABLE "delegating_population_snapshot" (

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

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

485 "event" "snapshot_event",

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

487 "weight" INT4,

488 "delegate_member_ids" INT4[] NOT NULL );

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

490

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

492

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

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

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

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

497

498

499 CREATE TABLE "direct_interest_snapshot" (

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

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

502 "event" "snapshot_event",

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

504 "weight" INT4,

505 "voting_requested" BOOLEAN );

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

507

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

509

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

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

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

513

514

515 CREATE TABLE "delegating_interest_snapshot" (

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

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

518 "event" "snapshot_event",

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

520 "weight" INT4,

521 "delegate_member_ids" INT4[] NOT NULL );

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

523

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

525

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

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

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

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

530

531

532 CREATE TABLE "direct_supporter_snapshot" (

533 "issue_id" INT4 NOT NULL,

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

535 "initiative_id" INT4,

536 "event" "snapshot_event",

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

538 "informed" BOOLEAN NOT NULL,

539 "satisfied" BOOLEAN NOT NULL,

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

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

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

543

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

545

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

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

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

549

550

551 CREATE TABLE "direct_voter" (

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

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

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

555 "weight" INT4,

556 "autoreject" BOOLEAN NOT NULL DEFAULT FALSE );

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

558

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

560

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

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

563

564

565 CREATE TABLE "delegating_voter" (

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

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

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

569 "weight" INT4,

570 "delegate_member_ids" INT4[] NOT NULL );

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

572

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

574

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

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

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

578

579

580 CREATE TABLE "vote" (

581 "issue_id" INT4 NOT NULL,

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

583 "initiative_id" INT4,

584 "member_id" INT4,

585 "grade" INT4,

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

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

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

589

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

591

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

593

594

595

596 ----------------------------

597 -- Additional constraints --

598 ----------------------------

599

600

601 CREATE FUNCTION "issue_requires_first_initiative_trigger"()

602 RETURNS TRIGGER

603 LANGUAGE 'plpgsql' VOLATILE AS $$

604 BEGIN

605 IF NOT EXISTS (

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

607 ) THEN

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

609 -- ERRCODE = 'integrity_constraint_violation',

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

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

612 END IF;

613 RETURN NULL;

614 END;

615 $$;

616

617 CREATE CONSTRAINT TRIGGER "issue_requires_first_initiative"

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

619 FOR EACH ROW EXECUTE PROCEDURE

620 "issue_requires_first_initiative_trigger"();

621

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

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

624

625

626 CREATE FUNCTION "last_initiative_deletes_issue_trigger"()

627 RETURNS TRIGGER

628 LANGUAGE 'plpgsql' VOLATILE AS $$

629 DECLARE

630 "reference_lost" BOOLEAN;

631 BEGIN

632 IF TG_OP = 'DELETE' THEN

633 "reference_lost" := TRUE;

634 ELSE

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

636 END IF;

637 IF

638 "reference_lost" AND NOT EXISTS (

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

640 )

641 THEN

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

643 END IF;

644 RETURN NULL;

645 END;

646 $$;

647

648 CREATE CONSTRAINT TRIGGER "last_initiative_deletes_issue"

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

650 FOR EACH ROW EXECUTE PROCEDURE

651 "last_initiative_deletes_issue_trigger"();

652

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

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

655

656

657 CREATE FUNCTION "initiative_requires_first_draft_trigger"()

658 RETURNS TRIGGER

659 LANGUAGE 'plpgsql' VOLATILE AS $$

660 BEGIN

661 IF NOT EXISTS (

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

663 ) THEN

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

665 -- ERRCODE = 'integrity_constraint_violation',

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

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

668 END IF;

669 RETURN NULL;

670 END;

671 $$;

672

673 CREATE CONSTRAINT TRIGGER "initiative_requires_first_draft"

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

675 FOR EACH ROW EXECUTE PROCEDURE

676 "initiative_requires_first_draft_trigger"();

677

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

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

680

681

682 CREATE FUNCTION "last_draft_deletes_initiative_trigger"()

683 RETURNS TRIGGER

684 LANGUAGE 'plpgsql' VOLATILE AS $$

685 DECLARE

686 "reference_lost" BOOLEAN;

687 BEGIN

688 IF TG_OP = 'DELETE' THEN

689 "reference_lost" := TRUE;

690 ELSE

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

692 END IF;

693 IF

694 "reference_lost" AND NOT EXISTS (

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

696 )

697 THEN

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

699 END IF;

700 RETURN NULL;

701 END;

702 $$;

703

704 CREATE CONSTRAINT TRIGGER "last_draft_deletes_initiative"

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

706 FOR EACH ROW EXECUTE PROCEDURE

707 "last_draft_deletes_initiative_trigger"();

708

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

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

711

712

713 CREATE FUNCTION "suggestion_requires_first_opinion_trigger"()

714 RETURNS TRIGGER

715 LANGUAGE 'plpgsql' VOLATILE AS $$

716 BEGIN

717 IF NOT EXISTS (

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

719 ) THEN

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

721 END IF;

722 RETURN NULL;

723 END;

724 $$;

725

726 CREATE CONSTRAINT TRIGGER "suggestion_requires_first_opinion"

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

728 FOR EACH ROW EXECUTE PROCEDURE

729 "suggestion_requires_first_opinion_trigger"();

730

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

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

733

734

735 CREATE FUNCTION "last_opinion_deletes_suggestion_trigger"()

736 RETURNS TRIGGER

737 LANGUAGE 'plpgsql' VOLATILE AS $$

738 DECLARE

739 "reference_lost" BOOLEAN;

740 BEGIN

741 IF TG_OP = 'DELETE' THEN

742 "reference_lost" := TRUE;

743 ELSE

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

745 END IF;

746 IF

747 "reference_lost" AND NOT EXISTS (

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

749 )

750 THEN

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

752 END IF;

753 RETURN NULL;

754 END;

755 $$;

756

757 CREATE CONSTRAINT TRIGGER "last_opinion_deletes_suggestion"

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

759 FOR EACH ROW EXECUTE PROCEDURE

760 "last_opinion_deletes_suggestion_trigger"();

761

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

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

764

765

766

767 --------------------------------------------------------------------

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

769 --------------------------------------------------------------------

770

771 CREATE FUNCTION "autofill_issue_id_trigger"()

772 RETURNS TRIGGER

773 LANGUAGE 'plpgsql' VOLATILE AS $$

774 BEGIN

775 IF NEW."issue_id" ISNULL THEN

776 SELECT "issue_id" INTO NEW."issue_id"

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

778 END IF;

779 RETURN NEW;

780 END;

781 $$;

782

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

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

785

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

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

788

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

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

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

792

793

794 CREATE FUNCTION "autofill_initiative_id_trigger"()

795 RETURNS TRIGGER

796 LANGUAGE 'plpgsql' VOLATILE AS $$

797 BEGIN

798 IF NEW."initiative_id" ISNULL THEN

799 SELECT "initiative_id" INTO NEW."initiative_id"

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

801 END IF;

802 RETURN NEW;

803 END;

804 $$;

805

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

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

808

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

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

811

812

813

814 -----------------------------------------------------

815 -- Automatic calculation of certain default values --

816 -----------------------------------------------------

817

818 CREATE FUNCTION "copy_autoreject_trigger"()

819 RETURNS TRIGGER

820 LANGUAGE 'plpgsql' VOLATILE AS $$

821 BEGIN

822 IF NEW."autoreject" ISNULL THEN

823 SELECT "membership"."autoreject" INTO NEW."autoreject"

824 FROM "issue" JOIN "membership"

825 ON "issue"."area_id" = "membership"."area_id"

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

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

828 END IF;

829 IF NEW."autoreject" ISNULL THEN

830 NEW."autoreject" := FALSE;

831 END IF;

832 RETURN NEW;

833 END;

834 $$;

835

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

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

838

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

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

841

842

843 CREATE FUNCTION "supporter_default_for_draft_id_trigger"()

844 RETURNS TRIGGER

845 LANGUAGE 'plpgsql' VOLATILE AS $$

846 BEGIN

847 IF NEW."draft_id" ISNULL THEN

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

849 WHERE "initiative_id" = NEW."initiative_id";

850 END IF;

851 RETURN NEW;

852 END;

853 $$;

854

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

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

857

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

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

860

861

862

863 ----------------------------------------

864 -- Automatic creation of dependencies --

865 ----------------------------------------

866

867 CREATE FUNCTION "autocreate_interest_trigger"()

868 RETURNS TRIGGER

869 LANGUAGE 'plpgsql' VOLATILE AS $$

870 BEGIN

871 IF NOT EXISTS (

872 SELECT NULL FROM "initiative" JOIN "interest"

873 ON "initiative"."issue_id" = "interest"."issue_id"

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

875 AND "interest"."member_id" = NEW."member_id"

876 ) THEN

877 BEGIN

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

879 SELECT "issue_id", NEW."member_id"

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

881 EXCEPTION WHEN unique_violation THEN END;

882 END IF;

883 RETURN NEW;

884 END;

885 $$;

886

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

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

889

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

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

892

893

894 CREATE FUNCTION "autocreate_supporter_trigger"()

895 RETURNS TRIGGER

896 LANGUAGE 'plpgsql' VOLATILE AS $$

897 BEGIN

898 IF NOT EXISTS (

899 SELECT NULL FROM "suggestion" JOIN "supporter"

900 ON "suggestion"."initiative_id" = "supporter"."initiative_id"

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

902 AND "supporter"."member_id" = NEW."member_id"

903 ) THEN

904 BEGIN

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

906 SELECT "initiative_id", NEW."member_id"

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

908 EXCEPTION WHEN unique_violation THEN END;

909 END IF;

910 RETURN NEW;

911 END;

912 $$;

913

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

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

916

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

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

919

920

921

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

923 -- Views and helper functions for views --

924 ------------------------------------------

925

926

927 CREATE TYPE "delegation_scope" AS ENUM

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

929

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

931

932

933 CREATE VIEW "global_delegation" AS

934 SELECT

935 "delegation"."id",

936 "delegation"."truster_id",

937 "delegation"."trustee_id"

938 FROM "delegation" JOIN "member"

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

940 WHERE "delegation"."area_id" ISNULL

941 AND "delegation"."issue_id" ISNULL

942 AND "member"."active";

943

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

945

946

947 CREATE VIEW "area_delegation" AS

948 SELECT "subquery".* FROM (

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

950 "area"."id" AS "area_id",

951 "delegation"."id" AS "id",

952 "delegation"."truster_id" AS "truster_id",

953 "delegation"."trustee_id" AS "trustee_id",

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

955 'global'::"delegation_scope"

956 ELSE

957 'area'::"delegation_scope"

958 END AS "scope"

959 FROM "area" JOIN "delegation"

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

961 AND "delegation"."issue_id" ISNULL

962 ORDER BY

963 "area"."id",

964 "delegation"."truster_id",

965 "delegation"."area_id" NULLS LAST

966 ) AS "subquery"

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

968 WHERE "member"."active";

969

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

971

972

973 CREATE VIEW "issue_delegation" AS

974 SELECT "subquery".* FROM (

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

976 "issue"."id" AS "issue_id",

977 "delegation"."id" AS "id",

978 "delegation"."truster_id" AS "truster_id",

979 "delegation"."trustee_id" AS "trustee_id",

980 CASE

981 WHEN

982 "delegation"."area_id" ISNULL AND

983 "delegation"."issue_id" ISNULL

984 THEN 'global'::"delegation_scope"

985 WHEN

986 "delegation"."area_id" NOTNULL

987 THEN 'area'::"delegation_scope"

988 ELSE 'issue'::"delegation_scope"

989 END AS "scope"

990 FROM "issue" JOIN "delegation"

991 ON (

992 "delegation"."area_id" ISNULL OR

993 "delegation"."area_id" = "issue"."area_id"

994 ) AND (

995 "delegation"."issue_id" ISNULL OR

996 "delegation"."issue_id" = "issue"."id"

997 )

998 ORDER BY

999 "issue"."id",

1000 "delegation"."truster_id",

1001 "delegation"."issue_id" NULLS LAST,

1002 "delegation"."area_id" NULLS LAST

1003 ) AS "subquery"

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

1005 WHERE "member"."active";

1006

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

1008

1009

1010 CREATE FUNCTION "membership_weight_with_skipping"

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

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

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

1014 RETURNS INT4

1015 LANGUAGE 'plpgsql' STABLE AS $$

1016 DECLARE

1017 "sum_v" INT4;

1018 "delegation_row" "area_delegation"%ROWTYPE;

1019 BEGIN

1020 "sum_v" := 1;

1021 FOR "delegation_row" IN

1022 SELECT "area_delegation".*

1023 FROM "area_delegation" LEFT JOIN "membership"

1024 ON "membership"."area_id" = "area_id_p"

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

1026 WHERE "area_delegation"."area_id" = "area_id_p"

1027 AND "area_delegation"."trustee_id" = "member_id_p"

1028 AND "membership"."member_id" ISNULL

1029 LOOP

1030 IF NOT

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

1032 THEN

1033 "sum_v" := "sum_v" + "membership_weight_with_skipping"(

1034 "area_id_p",

1035 "delegation_row"."truster_id",

1036 "skip_member_ids_p" || "delegation_row"."truster_id"

1037 );

1038 END IF;

1039 END LOOP;

1040 RETURN "sum_v";

1041 END;

1042 $$;

1043

1044 COMMENT ON FUNCTION "membership_weight_with_skipping"

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

1046 "member"."id"%TYPE,

1047 INT4[] )

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

1049

1050

1051 CREATE FUNCTION "membership_weight"

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

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

1054 RETURNS INT4

1055 LANGUAGE 'plpgsql' STABLE AS $$

1056 BEGIN

1057 RETURN "membership_weight_with_skipping"(

1058 "area_id_p",

1059 "member_id_p",

1060 ARRAY["member_id_p"]

1061 );

1062 END;

1063 $$;

1064

1065 COMMENT ON FUNCTION "membership_weight"

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

1067 "member"."id"%TYPE )

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

1069

1070

1071 CREATE VIEW "member_count_view" AS

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

1073

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

1075

1076

1077 CREATE VIEW "area_member_count" AS

1078 SELECT

1079 "area"."id" AS "area_id",

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

1081 coalesce(

1082 sum(

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

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

1085 ELSE 0 END

1086 )

1087 ) AS "member_weight",

1088 coalesce(

1089 sum(

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

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

1092 ELSE 0 END

1093 )

1094 ) AS "autoreject_weight"

1095 FROM "area"

1096 LEFT JOIN "membership"

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

1098 LEFT JOIN "member"

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

1100 AND "member"."active"

1101 GROUP BY "area"."id";

1102

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

1104

1105

1106 CREATE VIEW "current_draft" AS

1107 SELECT "draft".* FROM (

1108 SELECT

1109 "initiative"."id" AS "initiative_id",

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

1111 FROM "initiative" JOIN "draft"

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

1113 GROUP BY "initiative"."id"

1114 ) AS "subquery"

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

1116

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

1118

1119

1120 CREATE VIEW "critical_opinion" AS

1121 SELECT * FROM "opinion"

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

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

1124

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

1126

1127

1128 CREATE VIEW "battle_participant" AS

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

1130 WHERE "admitted"

1131 AND "positive_votes" > "negative_votes";

1132

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

1134

1135

1136 CREATE VIEW "battle" AS

1137 SELECT

1138 "issue"."id" AS "issue_id",

1139 "winning_initiative"."initiative_id" AS "winning_initiative_id",

1140 "losing_initiative"."initiative_id" AS "losing_initiative_id",

1141 sum(

1142 CASE WHEN

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

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

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

1146 ) AS "count"

1147 FROM "issue"

1148 LEFT JOIN "direct_voter"

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

1150 JOIN "battle_participant" AS "winning_initiative"

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

1152 JOIN "battle_participant" AS "losing_initiative"

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

1154 LEFT JOIN "vote" AS "better_vote"

1155 ON "direct_voter"."member_id" = "better_vote"."member_id"

1156 AND "winning_initiative"."initiative_id" = "better_vote"."initiative_id"

1157 LEFT JOIN "vote" AS "worse_vote"

1158 ON "direct_voter"."member_id" = "worse_vote"."member_id"

1159 AND "losing_initiative"."initiative_id" = "worse_vote"."initiative_id"

1160 WHERE

1161 "winning_initiative"."initiative_id" !=

1162 "losing_initiative"."initiative_id"

1163 GROUP BY

1164 "issue"."id",

1165 "winning_initiative"."initiative_id",

1166 "losing_initiative"."initiative_id";

1167

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

1169

1170

1171 CREATE VIEW "expired_session" AS

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

1173

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

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

1176

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

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

1179

1180

1181 CREATE VIEW "open_issue" AS

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

1183

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

1185

1186

1187 CREATE VIEW "issue_with_ranks_missing" AS

1188 SELECT * FROM "issue"

1189 WHERE "fully_frozen" NOTNULL

1190 AND "closed" NOTNULL

1191 AND "ranks_available" = FALSE;

1192

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

1194

1195

1196

1197 --------------------------------------------------

1198 -- Set returning function for delegation chains --

1199 --------------------------------------------------

1200

1201

1202 CREATE TYPE "delegation_chain_loop_tag" AS ENUM

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

1204

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

1206

1207

1208 CREATE TYPE "delegation_chain_row" AS (

1209 "index" INT4,

1210 "member_id" INT4,

1211 "member_active" BOOLEAN,

1212 "participation" BOOLEAN,

1213 "overridden" BOOLEAN,

1214 "scope_in" "delegation_scope",

1215 "scope_out" "delegation_scope",

1216 "loop" "delegation_chain_loop_tag" );

1217

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

1219

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

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

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

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

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

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

1226

1227

1228 CREATE FUNCTION "delegation_chain"

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

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

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

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

1233 RETURNS SETOF "delegation_chain_row"

1234 LANGUAGE 'plpgsql' STABLE AS $$

1235 DECLARE

1236 "issue_row" "issue"%ROWTYPE;

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

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

1239 "output_row" "delegation_chain_row";

1240 "output_rows" "delegation_chain_row"[];

1241 "delegation_row" "delegation"%ROWTYPE;

1242 "row_count" INT4;

1243 "i" INT4;

1244 "loop_v" BOOLEAN;

1245 BEGIN

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

1247 "visited_member_ids" := '{}';

1248 "loop_member_id_v" := NULL;

1249 "output_rows" := '{}';

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

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

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

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

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

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

1256 LOOP

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

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

1259 ELSE

1260 "visited_member_ids" :=

1261 "visited_member_ids" || "output_row"."member_id";

1262 END IF;

1263 IF "output_row"."participation" THEN

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

1265 END IF;

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

1267 IF EXISTS (

1268 SELECT NULL FROM "member"

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

1270 ) THEN

1271 IF "area_id_p" ISNULL AND "issue_id_p" ISNULL THEN

1272 SELECT * INTO "delegation_row" FROM "delegation"

1273 WHERE "truster_id" = "output_row"."member_id"

1274 AND "area_id" ISNULL AND "issue_id" ISNULL;

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

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

1277 SELECT NULL FROM "membership"

1278 WHERE "area_id" = "area_id_p"

1279 AND "member_id" = "output_row"."member_id"

1280 );

1281 SELECT * INTO "delegation_row" FROM "delegation"

1282 WHERE "truster_id" = "output_row"."member_id"

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

1284 AND "issue_id" ISNULL

1285 ORDER BY "area_id" NULLS LAST;

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

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

1288 SELECT NULL FROM "interest"

1289 WHERE "issue_id" = "issue_id_p"

1290 AND "member_id" = "output_row"."member_id"

1291 );

1292 SELECT * INTO "delegation_row" FROM "delegation"

1293 WHERE "truster_id" = "output_row"."member_id"

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

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

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

1297 ELSE

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

1299 END IF;

1300 ELSE

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

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

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

1304 "delegation_row" := ROW(NULL);

1305 END IF;

1306 IF

1307 "output_row"."member_id" = "member_id_p" AND

1308 "simulate_trustee_id_p" NOTNULL

1309 THEN

1310 "output_row"."scope_out" := CASE

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

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

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

1314 END;

1315 "output_rows" := "output_rows" || "output_row";

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

1317 ELSIF "delegation_row"."trustee_id" NOTNULL THEN

1318 "output_row"."scope_out" := CASE

1319 WHEN

1320 "delegation_row"."area_id" ISNULL AND

1321 "delegation_row"."issue_id" ISNULL

1322 THEN 'global'

1323 WHEN

1324 "delegation_row"."area_id" NOTNULL AND

1325 "delegation_row"."issue_id" ISNULL

1326 THEN 'area'

1327 WHEN

1328 "delegation_row"."area_id" ISNULL AND

1329 "delegation_row"."issue_id" NOTNULL

1330 THEN 'issue'

1331 END;

1332 "output_rows" := "output_rows" || "output_row";

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

1334 ELSE

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

1336 "output_rows" := "output_rows" || "output_row";

1337 EXIT;

1338 END IF;

1339 EXIT WHEN "loop_member_id_v" NOTNULL;

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

1341 END LOOP;

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

1343 "i" := 1;

1344 "loop_v" := FALSE;

1345 LOOP

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

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

1348 IF "loop_v" THEN

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

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

1351 ELSIF "i" = "row_count" THEN

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

1353 ELSE

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

1355 END IF;

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

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

1358 "loop_v" := TRUE;

1359 END IF;

1360 IF "area_id_p" ISNULL AND "issue_id_p" ISNULL THEN

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

1362 END IF;

1363 RETURN NEXT "output_row";

1364 "i" := "i" + 1;

1365 END LOOP;

1366 RETURN;

1367 END;

1368 $$;

1369

1370 COMMENT ON FUNCTION "delegation_chain"

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

1372 "area"."id"%TYPE,

1373 "issue"."id"%TYPE,

1374 "member"."id"%TYPE )

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

1376

1377 CREATE FUNCTION "delegation_chain"

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

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

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

1381 RETURNS SETOF "delegation_chain_row"

1382 LANGUAGE 'plpgsql' STABLE AS $$

1383 DECLARE

1384 "result_row" "delegation_chain_row";

1385 BEGIN

1386 FOR "result_row" IN

1387 SELECT * FROM "delegation_chain"(

1388 "member_id_p", "area_id_p", "issue_id_p", NULL

1389 )

1390 LOOP

1391 RETURN NEXT "result_row";

1392 END LOOP;

1393 RETURN;

1394 END;

1395 $$;

1396

1397 COMMENT ON FUNCTION "delegation_chain"

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

1399 "area"."id"%TYPE,

1400 "issue"."id"%TYPE )

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

1402

1403

1404

1405 ------------------------------

1406 -- Comparison by vote count --

1407 ------------------------------

1408

1409 CREATE FUNCTION "vote_ratio"

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

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

1412 RETURNS FLOAT8

1413 LANGUAGE 'plpgsql' STABLE AS $$

1414 DECLARE

1415 "total_v" INT4;

1416 BEGIN

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

1418 IF "total_v" > 0 THEN

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

1420 ELSE

1421 RETURN 0.5;

1422 END IF;

1423 END;

1424 $$;

1425

1426 COMMENT ON FUNCTION "vote_ratio"

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

1428 "initiative"."negative_votes"%TYPE )

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

1430

1431

1432

1433 ------------------------------------------------

1434 -- Locking for snapshots and voting procedure --

1435 ------------------------------------------------

1436

1437 CREATE FUNCTION "global_lock"() RETURNS VOID

1438 LANGUAGE 'plpgsql' VOLATILE AS $$

1439 BEGIN

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

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

1442 LOCK TABLE "member" IN EXCLUSIVE MODE;

1443 LOCK TABLE "area" IN EXCLUSIVE MODE;

1444 LOCK TABLE "membership" IN EXCLUSIVE MODE;

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

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

1447 LOCK TABLE "policy" IN EXCLUSIVE MODE;

1448 LOCK TABLE "issue" IN EXCLUSIVE MODE;

1449 LOCK TABLE "initiative" IN EXCLUSIVE MODE;

1450 LOCK TABLE "draft" IN EXCLUSIVE MODE;

1451 LOCK TABLE "suggestion" IN EXCLUSIVE MODE;

1452 LOCK TABLE "interest" IN EXCLUSIVE MODE;

1453 LOCK TABLE "initiator" IN EXCLUSIVE MODE;

1454 LOCK TABLE "supporter" IN EXCLUSIVE MODE;

1455 LOCK TABLE "opinion" IN EXCLUSIVE MODE;

1456 LOCK TABLE "delegation" IN EXCLUSIVE MODE;

1457 LOCK TABLE "direct_population_snapshot" IN EXCLUSIVE MODE;

1458 LOCK TABLE "delegating_population_snapshot" IN EXCLUSIVE MODE;

1459 LOCK TABLE "direct_interest_snapshot" IN EXCLUSIVE MODE;

1460 LOCK TABLE "delegating_interest_snapshot" IN EXCLUSIVE MODE;

1461 LOCK TABLE "direct_supporter_snapshot" IN EXCLUSIVE MODE;

1462 LOCK TABLE "direct_voter" IN EXCLUSIVE MODE;

1463 LOCK TABLE "delegating_voter" IN EXCLUSIVE MODE;

1464 LOCK TABLE "vote" IN EXCLUSIVE MODE;

1465 RETURN;

1466 END;

1467 $$;

1468

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

1470

1471

1472

1473 -------------------------------

1474 -- Materialize member counts --

1475 -------------------------------

1476

1477 CREATE FUNCTION "calculate_member_counts"()

1478 RETURNS VOID

1479 LANGUAGE 'plpgsql' VOLATILE AS $$

1480 BEGIN

1481 LOCK TABLE "member" IN EXCLUSIVE MODE;

1482 LOCK TABLE "area" IN EXCLUSIVE MODE;

1483 LOCK TABLE "membership" IN EXCLUSIVE MODE;

1484 DELETE FROM "member_count";

1485 INSERT INTO "member_count" ("total_count")

1486 SELECT "total_count" FROM "member_count_view";

1487 UPDATE "area" SET

1488 "direct_member_count" = "view"."direct_member_count",

1489 "member_weight" = "view"."member_weight",

1490 "autoreject_weight" = "view"."autoreject_weight"

1491 FROM "area_member_count" AS "view"

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

1493 RETURN;

1494 END;

1495 $$;

1496

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

1498

1499

1500

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

1502 -- Calculation of snapshots --

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

1504

1505 CREATE FUNCTION "weight_of_added_delegations_for_population_snapshot"

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

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

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

1509 RETURNS "direct_population_snapshot"."weight"%TYPE

1510 LANGUAGE 'plpgsql' VOLATILE AS $$

1511 DECLARE

1512 "issue_delegation_row" "issue_delegation"%ROWTYPE;

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

1514 "weight_v" INT4;

1515 "sub_weight_v" INT4;

1516 BEGIN

1517 "weight_v" := 0;

1518 FOR "issue_delegation_row" IN

1519 SELECT * FROM "issue_delegation"

1520 WHERE "trustee_id" = "member_id_p"

1521 AND "issue_id" = "issue_id_p"

1522 LOOP

1523 IF NOT EXISTS (

1524 SELECT NULL FROM "direct_population_snapshot"

1525 WHERE "issue_id" = "issue_id_p"

1526 AND "event" = 'periodic'

1527 AND "member_id" = "issue_delegation_row"."truster_id"

1528 ) AND NOT EXISTS (

1529 SELECT NULL FROM "delegating_population_snapshot"

1530 WHERE "issue_id" = "issue_id_p"

1531 AND "event" = 'periodic'

1532 AND "member_id" = "issue_delegation_row"."truster_id"

1533 ) THEN

1534 "delegate_member_ids_v" :=

1535 "member_id_p" || "delegate_member_ids_p";

1536 INSERT INTO "delegating_population_snapshot"

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

1538 VALUES (

1539 "issue_id_p",

1540 'periodic',

1541 "issue_delegation_row"."truster_id",

1542 "delegate_member_ids_v"

1543 );

1544 "sub_weight_v" := 1 +

1545 "weight_of_added_delegations_for_population_snapshot"(

1546 "issue_id_p",

1547 "issue_delegation_row"."truster_id",

1548 "delegate_member_ids_v"

1549 );

1550 UPDATE "delegating_population_snapshot"

1551 SET "weight" = "sub_weight_v"

1552 WHERE "issue_id" = "issue_id_p"

1553 AND "event" = 'periodic'

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

1555 "weight_v" := "weight_v" + "sub_weight_v";

1556 END IF;

1557 END LOOP;

1558 RETURN "weight_v";

1559 END;

1560 $$;

1561

1562 COMMENT ON FUNCTION "weight_of_added_delegations_for_population_snapshot"

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

1564 "member"."id"%TYPE,

1565 "delegating_population_snapshot"."delegate_member_ids"%TYPE )

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

1567

1568

1569 CREATE FUNCTION "create_population_snapshot"

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

1571 RETURNS VOID

1572 LANGUAGE 'plpgsql' VOLATILE AS $$

1573 DECLARE

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

1575 BEGIN

1576 DELETE FROM "direct_population_snapshot"

1577 WHERE "issue_id" = "issue_id_p"

1578 AND "event" = 'periodic';

1579 DELETE FROM "delegating_population_snapshot"

1580 WHERE "issue_id" = "issue_id_p"

1581 AND "event" = 'periodic';

1582 INSERT INTO "direct_population_snapshot"

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

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

1585 "issue_id_p" AS "issue_id",

1586 'periodic' AS "event",

1587 "subquery"."member_id",

1588 "subquery"."interest_exists"

1589 FROM (

1590 SELECT

1591 "member"."id" AS "member_id",

1592 FALSE AS "interest_exists"

1593 FROM "issue"

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

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

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

1597 WHERE "issue"."id" = "issue_id_p"

1598 AND "member"."active"

1599 UNION

1600 SELECT

1601 "member"."id" AS "member_id",

1602 TRUE AS "interest_exists"

1603 FROM "interest" JOIN "member"

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

1605 WHERE "interest"."issue_id" = "issue_id_p"

1606 AND "member"."active"

1607 ) AS "subquery"

1608 ORDER BY

1609 "issue_id_p",

1610 "subquery"."member_id",

1611 "subquery"."interest_exists" DESC;

1612 FOR "member_id_v" IN

1613 SELECT "member_id" FROM "direct_population_snapshot"

1614 WHERE "issue_id" = "issue_id_p"

1615 AND "event" = 'periodic'

1616 LOOP

1617 UPDATE "direct_population_snapshot" SET

1618 "weight" = 1 +

1619 "weight_of_added_delegations_for_population_snapshot"(

1620 "issue_id_p",

1621 "member_id_v",

1622 '{}'

1623 )

1624 WHERE "issue_id" = "issue_id_p"

1625 AND "event" = 'periodic'

1626 AND "member_id" = "member_id_v";

1627 END LOOP;

1628 RETURN;

1629 END;

1630 $$;

1631

1632 COMMENT ON FUNCTION "create_population_snapshot"

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

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

1635

1636

1637 CREATE FUNCTION "weight_of_added_delegations_for_interest_snapshot"

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

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

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

1641 RETURNS "direct_interest_snapshot"."weight"%TYPE

1642 LANGUAGE 'plpgsql' VOLATILE AS $$

1643 DECLARE

1644 "issue_delegation_row" "issue_delegation"%ROWTYPE;

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

1646 "weight_v" INT4;

1647 "sub_weight_v" INT4;

1648 BEGIN

1649 "weight_v" := 0;

1650 FOR "issue_delegation_row" IN

1651 SELECT * FROM "issue_delegation"

1652 WHERE "trustee_id" = "member_id_p"

1653 AND "issue_id" = "issue_id_p"

1654 LOOP

1655 IF NOT EXISTS (

1656 SELECT NULL FROM "direct_interest_snapshot"

1657 WHERE "issue_id" = "issue_id_p"

1658 AND "event" = 'periodic'

1659 AND "member_id" = "issue_delegation_row"."truster_id"

1660 ) AND NOT EXISTS (

1661 SELECT NULL FROM "delegating_interest_snapshot"

1662 WHERE "issue_id" = "issue_id_p"

1663 AND "event" = 'periodic'

1664 AND "member_id" = "issue_delegation_row"."truster_id"

1665 ) THEN

1666 "delegate_member_ids_v" :=

1667 "member_id_p" || "delegate_member_ids_p";

1668 INSERT INTO "delegating_interest_snapshot"

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

1670 VALUES (

1671 "issue_id_p",

1672 'periodic',

1673 "issue_delegation_row"."truster_id",

1674 "delegate_member_ids_v"

1675 );

1676 "sub_weight_v" := 1 +

1677 "weight_of_added_delegations_for_interest_snapshot"(

1678 "issue_id_p",

1679 "issue_delegation_row"."truster_id",

1680 "delegate_member_ids_v"

1681 );

1682 UPDATE "delegating_interest_snapshot"

1683 SET "weight" = "sub_weight_v"

1684 WHERE "issue_id" = "issue_id_p"

1685 AND "event" = 'periodic'

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

1687 "weight_v" := "weight_v" + "sub_weight_v";

1688 END IF;

1689 END LOOP;

1690 RETURN "weight_v";

1691 END;

1692 $$;

1693

1694 COMMENT ON FUNCTION "weight_of_added_delegations_for_interest_snapshot"

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

1696 "member"."id"%TYPE,

1697 "delegating_interest_snapshot"."delegate_member_ids"%TYPE )

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

1699

1700

1701 CREATE FUNCTION "create_interest_snapshot"

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

1703 RETURNS VOID

1704 LANGUAGE 'plpgsql' VOLATILE AS $$

1705 DECLARE

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

1707 BEGIN

1708 DELETE FROM "direct_interest_snapshot"

1709 WHERE "issue_id" = "issue_id_p"

1710 AND "event" = 'periodic';

1711 DELETE FROM "delegating_interest_snapshot"

1712 WHERE "issue_id" = "issue_id_p"

1713 AND "event" = 'periodic';

1714 DELETE FROM "direct_supporter_snapshot"

1715 WHERE "issue_id" = "issue_id_p"

1716 AND "event" = 'periodic';

1717 INSERT INTO "direct_interest_snapshot"

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

1719 SELECT

1720 "issue_id_p" AS "issue_id",

1721 'periodic' AS "event",

1722 "member"."id" AS "member_id",

1723 "interest"."voting_requested"

1724 FROM "interest" JOIN "member"

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

1726 WHERE "interest"."issue_id" = "issue_id_p"

1727 AND "member"."active";

1728 FOR "member_id_v" IN

1729 SELECT "member_id" FROM "direct_interest_snapshot"

1730 WHERE "issue_id" = "issue_id_p"

1731 AND "event" = 'periodic'

1732 LOOP

1733 UPDATE "direct_interest_snapshot" SET

1734 "weight" = 1 +

1735 "weight_of_added_delegations_for_interest_snapshot"(

1736 "issue_id_p",

1737 "member_id_v",

1738 '{}'

1739 )

1740 WHERE "issue_id" = "issue_id_p"

1741 AND "event" = 'periodic'

1742 AND "member_id" = "member_id_v";

1743 END LOOP;

1744 INSERT INTO "direct_supporter_snapshot"

1745 ( "issue_id", "initiative_id", "event", "member_id",

1746 "informed", "satisfied" )

1747 SELECT

1748 "issue_id_p" AS "issue_id",

1749 "initiative"."id" AS "initiative_id",

1750 'periodic' AS "event",

1751 "member"."id" AS "member_id",

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

1753 NOT EXISTS (

1754 SELECT NULL FROM "critical_opinion"

1755 WHERE "initiative_id" = "initiative"."id"

1756 AND "member_id" = "member"."id"

1757 ) AS "satisfied"

1758 FROM "supporter"

1759 JOIN "member"

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

1761 JOIN "initiative"

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

1763 JOIN "current_draft"

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

1765 JOIN "direct_interest_snapshot"

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

1767 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"

1768 AND "event" = 'periodic'

1769 WHERE "member"."active"

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

1771 RETURN;

1772 END;

1773 $$;

1774

1775 COMMENT ON FUNCTION "create_interest_snapshot"

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

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

1778

1779

1780 CREATE FUNCTION "create_snapshot"

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

1782 RETURNS VOID

1783 LANGUAGE 'plpgsql' VOLATILE AS $$

1784 DECLARE

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

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

1787 BEGIN

1788 PERFORM "global_lock"();

1789 PERFORM "create_population_snapshot"("issue_id_p");

1790 PERFORM "create_interest_snapshot"("issue_id_p");

1791 UPDATE "issue" SET

1792 "snapshot" = now(),

1793 "latest_snapshot_event" = 'periodic',

1794 "population" = (

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

1796 FROM "direct_population_snapshot"

1797 WHERE "issue_id" = "issue_id_p"

1798 AND "event" = 'periodic'

1799 ),

1800 "vote_now" = (

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

1802 FROM "direct_interest_snapshot"

1803 WHERE "issue_id" = "issue_id_p"

1804 AND "event" = 'periodic'

1805 AND "voting_requested" = TRUE

1806 ),

1807 "vote_later" = (

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

1809 FROM "direct_interest_snapshot"

1810 WHERE "issue_id" = "issue_id_p"

1811 AND "event" = 'periodic'

1812 AND "voting_requested" = FALSE

1813 )

1814 WHERE "id" = "issue_id_p";

1815 FOR "initiative_id_v" IN

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

1817 LOOP

1818 UPDATE "initiative" SET

1819 "supporter_count" = (

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

1821 FROM "direct_interest_snapshot" AS "di"

1822 JOIN "direct_supporter_snapshot" AS "ds"

1823 ON "di"."member_id" = "ds"."member_id"

1824 WHERE "di"."issue_id" = "issue_id_p"

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

1826 AND "ds"."initiative_id" = "initiative_id_v"

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

1828 ),

1829 "informed_supporter_count" = (

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

1831 FROM "direct_interest_snapshot" AS "di"

1832 JOIN "direct_supporter_snapshot" AS "ds"

1833 ON "di"."member_id" = "ds"."member_id"

1834 WHERE "di"."issue_id" = "issue_id_p"

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

1836 AND "ds"."initiative_id" = "initiative_id_v"

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

1838 AND "ds"."informed"

1839 ),

1840 "satisfied_supporter_count" = (

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

1842 FROM "direct_interest_snapshot" AS "di"

1843 JOIN "direct_supporter_snapshot" AS "ds"

1844 ON "di"."member_id" = "ds"."member_id"

1845 WHERE "di"."issue_id" = "issue_id_p"

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

1847 AND "ds"."initiative_id" = "initiative_id_v"

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

1849 AND "ds"."satisfied"

1850 ),

1851 "satisfied_informed_supporter_count" = (

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

1853 FROM "direct_interest_snapshot" AS "di"

1854 JOIN "direct_supporter_snapshot" AS "ds"

1855 ON "di"."member_id" = "ds"."member_id"

1856 WHERE "di"."issue_id" = "issue_id_p"

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

1858 AND "ds"."initiative_id" = "initiative_id_v"

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

1860 AND "ds"."informed"

1861 AND "ds"."satisfied"

1862 )

1863 WHERE "id" = "initiative_id_v";

1864 FOR "suggestion_id_v" IN

1865 SELECT "id" FROM "suggestion"

1866 WHERE "initiative_id" = "initiative_id_v"

1867 LOOP

1868 UPDATE "suggestion" SET

1869 "minus2_unfulfilled_count" = (

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

1871 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"

1872 ON "opinion"."member_id" = "snapshot"."member_id"

1873 WHERE "opinion"."suggestion_id" = "suggestion_id_v"

1874 AND "snapshot"."issue_id" = "issue_id_p"

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

1876 AND "opinion"."fulfilled" = FALSE

1877 ),

1878 "minus2_fulfilled_count" = (

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

1880 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"

1881 ON "opinion"."member_id" = "snapshot"."member_id"

1882 WHERE "opinion"."suggestion_id" = "suggestion_id_v"

1883 AND "snapshot"."issue_id" = "issue_id_p"

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

1885 AND "opinion"."fulfilled" = TRUE

1886 ),

1887 "minus1_unfulfilled_count" = (

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

1889 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"

1890 ON "opinion"."member_id" = "snapshot"."member_id"

1891 WHERE "opinion"."suggestion_id" = "suggestion_id_v"

1892 AND "snapshot"."issue_id" = "issue_id_p"

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

1894 AND "opinion"."fulfilled" = FALSE

1895 ),

1896 "minus1_fulfilled_count" = (

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

1898 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"

1899 ON "opinion"."member_id" = "snapshot"."member_id"

1900 WHERE "opinion"."suggestion_id" = "suggestion_id_v"

1901 AND "snapshot"."issue_id" = "issue_id_p"

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

1903 AND "opinion"."fulfilled" = TRUE

1904 ),

1905 "plus1_unfulfilled_count" = (

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

1907 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"

1908 ON "opinion"."member_id" = "snapshot"."member_id"

1909 WHERE "opinion"."suggestion_id" = "suggestion_id_v"

1910 AND "snapshot"."issue_id" = "issue_id_p"

1911 AND "opinion"."degree" = 1

1912 AND "opinion"."fulfilled" = FALSE

1913 ),

1914 "plus1_fulfilled_count" = (

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

1916 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"

1917 ON "opinion"."member_id" = "snapshot"."member_id"

1918 WHERE "opinion"."suggestion_id" = "suggestion_id_v"

1919 AND "snapshot"."issue_id" = "issue_id_p"

1920 AND "opinion"."degree" = 1

1921 AND "opinion"."fulfilled" = TRUE

1922 ),

1923 "plus2_unfulfilled_count" = (

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

1925 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"

1926 ON "opinion"."member_id" = "snapshot"."member_id"

1927 WHERE "opinion"."suggestion_id" = "suggestion_id_v"

1928 AND "snapshot"."issue_id" = "issue_id_p"

1929 AND "opinion"."degree" = 2

1930 AND "opinion"."fulfilled" = FALSE

1931 ),

1932 "plus2_fulfilled_count" = (

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

1934 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"

1935 ON "opinion"."member_id" = "snapshot"."member_id"

1936 WHERE "opinion"."suggestion_id" = "suggestion_id_v"

1937 AND "snapshot"."issue_id" = "issue_id_p"

1938 AND "opinion"."degree" = 2

1939 AND "opinion"."fulfilled" = TRUE

1940 )

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

1942 END LOOP;

1943 END LOOP;

1944 RETURN;

1945 END;

1946 $$;

1947

1948 COMMENT ON FUNCTION "create_snapshot"

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

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

1951

1952

1953 CREATE FUNCTION "set_snapshot_event"

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

1955 "event_p" "snapshot_event" )

1956 RETURNS VOID

1957 LANGUAGE 'plpgsql' VOLATILE AS $$

1958 BEGIN

1959 UPDATE "issue" SET "latest_snapshot_event" = "event_p"

1960 WHERE "id" = "issue_id_p";

1961 UPDATE "direct_population_snapshot" SET "event" = "event_p"

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

1963 UPDATE "delegating_population_snapshot" SET "event" = "event_p"

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

1965 UPDATE "direct_interest_snapshot" SET "event" = "event_p"

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

1967 UPDATE "delegating_interest_snapshot" SET "event" = "event_p"

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

1969 UPDATE "direct_supporter_snapshot" SET "event" = "event_p"

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

1971 RETURN;

1972 END;

1973 $$;

1974

1975 COMMENT ON FUNCTION "set_snapshot_event"

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

1977 "snapshot_event" )

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

1979

1980

1981

1982 ---------------------

1983 -- Freezing issues --

1984 ---------------------

1985

1986 CREATE FUNCTION "freeze_after_snapshot"

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

1988 RETURNS VOID

1989 LANGUAGE 'plpgsql' VOLATILE AS $$

1990 DECLARE

1991 "issue_row" "issue"%ROWTYPE;

1992 "policy_row" "policy"%ROWTYPE;

1993 "initiative_row" "initiative"%ROWTYPE;

1994 BEGIN

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

1996 SELECT * INTO "policy_row"

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

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

1999 UPDATE "issue" SET

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

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

2002 "fully_frozen" = now()

2003 WHERE "id" = "issue_id_p";

2004 FOR "initiative_row" IN

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

2006 LOOP

2007 IF

2008 "initiative_row"."satisfied_supporter_count" > 0 AND

2009 "initiative_row"."satisfied_supporter_count" *

2010 "policy_row"."initiative_quorum_den" >=

2011 "issue_row"."population" * "policy_row"."initiative_quorum_num"

2012 THEN

2013 UPDATE "initiative" SET "admitted" = TRUE

2014 WHERE "id" = "initiative_row"."id";

2015 ELSE

2016 UPDATE "initiative" SET "admitted" = FALSE

2017 WHERE "id" = "initiative_row"."id";

2018 END IF;

2019 END LOOP;

2020 RETURN;

2021 END;

2022 $$;

2023

2024 COMMENT ON FUNCTION "freeze_after_snapshot"

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

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

2027

2028

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

2030 RETURNS VOID

2031 LANGUAGE 'plpgsql' VOLATILE AS $$

2032 DECLARE

2033 "issue_row" "issue"%ROWTYPE;

2034 BEGIN

2035 PERFORM "create_snapshot"("issue_id_p");

2036 PERFORM "freeze_after_snapshot"("issue_id_p");

2037 RETURN;

2038 END;

2039 $$;

2040

2041 COMMENT ON FUNCTION "freeze_after_snapshot"

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

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

2044

2045

2046

2047 -----------------------

2048 -- Counting of votes --

2049 -----------------------

2050

2051

2052 CREATE FUNCTION "weight_of_added_vote_delegations"

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

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

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

2056 RETURNS "direct_voter"."weight"%TYPE

2057 LANGUAGE 'plpgsql' VOLATILE AS $$

2058 DECLARE

2059 "issue_delegation_row" "issue_delegation"%ROWTYPE;

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

2061 "weight_v" INT4;

2062 "sub_weight_v" INT4;

2063 BEGIN

2064 "weight_v" := 0;

2065 FOR "issue_delegation_row" IN

2066 SELECT * FROM "issue_delegation"

2067 WHERE "trustee_id" = "member_id_p"

2068 AND "issue_id" = "issue_id_p"

2069 LOOP

2070 IF NOT EXISTS (

2071 SELECT NULL FROM "direct_voter"

2072 WHERE "member_id" = "issue_delegation_row"."truster_id"

2073 AND "issue_id" = "issue_id_p"

2074 ) AND NOT EXISTS (

2075 SELECT NULL FROM "delegating_voter"

2076 WHERE "member_id" = "issue_delegation_row"."truster_id"

2077 AND "issue_id" = "issue_id_p"

2078 ) THEN

2079 "delegate_member_ids_v" :=

2080 "member_id_p" || "delegate_member_ids_p";

2081 INSERT INTO "delegating_voter"

2082 ("issue_id", "member_id", "delegate_member_ids")

2083 VALUES (

2084 "issue_id_p",

2085 "issue_delegation_row"."truster_id",

2086 "delegate_member_ids_v"

2087 );

2088 "sub_weight_v" := 1 +

2089 "weight_of_added_vote_delegations"(

2090 "issue_id_p",

2091 "issue_delegation_row"."truster_id",

2092 "delegate_member_ids_v"

2093 );

2094 UPDATE "delegating_voter"

2095 SET "weight" = "sub_weight_v"

2096 WHERE "issue_id" = "issue_id_p"

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

2098 "weight_v" := "weight_v" + "sub_weight_v";

2099 END IF;

2100 END LOOP;

2101 RETURN "weight_v";

2102 END;

2103 $$;

2104

2105 COMMENT ON FUNCTION "weight_of_added_vote_delegations"

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

2107 "member"."id"%TYPE,

2108 "delegating_voter"."delegate_member_ids"%TYPE )

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

2110

2111

2112 CREATE FUNCTION "add_vote_delegations"

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

2114 RETURNS VOID

2115 LANGUAGE 'plpgsql' VOLATILE AS $$

2116 DECLARE

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

2118 BEGIN

2119 FOR "member_id_v" IN

2120 SELECT "member_id" FROM "direct_voter"

2121 WHERE "issue_id" = "issue_id_p"

2122 LOOP

2123 UPDATE "direct_voter" SET

2124 "weight" = "weight" + "weight_of_added_vote_delegations"(

2125 "issue_id_p",

2126 "member_id_v",

2127 '{}'

2128 )

2129 WHERE "member_id" = "member_id_v"

2130 AND "issue_id" = "issue_id_p";

2131 END LOOP;

2132 RETURN;

2133 END;

2134 $$;

2135

2136 COMMENT ON FUNCTION "add_vote_delegations"

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

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

2139

2140

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

2142 RETURNS VOID

2143 LANGUAGE 'plpgsql' VOLATILE AS $$

2144 DECLARE

2145 "issue_row" "issue"%ROWTYPE;

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

2147 BEGIN

2148 PERFORM "global_lock"();

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

2150 DELETE FROM "delegating_voter"

2151 WHERE "issue_id" = "issue_id_p";

2152 DELETE FROM "direct_voter"

2153 WHERE "issue_id" = "issue_id_p"

2154 AND "autoreject" = TRUE;

2155 DELETE FROM "direct_voter" USING "member"

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

2157 AND "direct_voter"."issue_id" = "issue_id_p"

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

2159 UPDATE "direct_voter" SET "weight" = 1

2160 WHERE "issue_id" = "issue_id_p";

2161 PERFORM "add_vote_delegations"("issue_id_p");

2162 FOR "member_id_v" IN

2163 SELECT "interest"."member_id"

2164 FROM "interest"

2165 LEFT JOIN "direct_voter"

2166 ON "interest"."member_id" = "direct_voter"."member_id"

2167 AND "interest"."issue_id" = "direct_voter"."issue_id"

2168 LEFT JOIN "delegating_voter"

2169 ON "interest"."member_id" = "delegating_voter"."member_id"

2170 AND "interest"."issue_id" = "delegating_voter"."issue_id"

2171 WHERE "interest"."issue_id" = "issue_id_p"

2172 AND "interest"."autoreject" = TRUE

2173 AND "direct_voter"."member_id" ISNULL

2174 AND "delegating_voter"."member_id" ISNULL

2175 UNION SELECT "membership"."member_id"

2176 FROM "membership"

2177 LEFT JOIN "interest"

2178 ON "membership"."member_id" = "interest"."member_id"

2179 AND "interest"."issue_id" = "issue_id_p"

2180 LEFT JOIN "direct_voter"

2181 ON "membership"."member_id" = "direct_voter"."member_id"

2182 AND "direct_voter"."issue_id" = "issue_id_p"

2183 LEFT JOIN "delegating_voter"

2184 ON "membership"."member_id" = "delegating_voter"."member_id"

2185 AND "delegating_voter"."issue_id" = "issue_id_p"

2186 WHERE "membership"."area_id" = "issue_row"."area_id"

2187 AND "membership"."autoreject" = TRUE

2188 AND "interest"."autoreject" ISNULL

2189 AND "direct_voter"."member_id" ISNULL

2190 AND "delegating_voter"."member_id" ISNULL

2191 LOOP

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

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

2194 INSERT INTO "vote" (

2195 "member_id",

2196 "issue_id",

2197 "initiative_id",

2198 "grade"

2199 ) SELECT

2200 "member_id_v" AS "member_id",

2201 "issue_id_p" AS "issue_id",

2202 "id" AS "initiative_id",

2203 -1 AS "grade"

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

2205 END LOOP;

2206 PERFORM "add_vote_delegations"("issue_id_p");

2207 UPDATE "issue" SET

2208 "voter_count" = (

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

2210 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"

2211 )

2212 WHERE "id" = "issue_id_p";

2213 UPDATE "initiative" SET

2214 "positive_votes" = "subquery"."positive_votes",

2215 "negative_votes" = "subquery"."negative_votes"

2216 FROM (

2217 SELECT

2218 "initiative_id",

2219 coalesce(

2220 sum(

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

2222 ),

2223 0

2224 ) AS "positive_votes",

2225 coalesce(

2226 sum(

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

2228 ),

2229 0

2230 ) AS "negative_votes"

2231 FROM "vote" JOIN "direct_voter"

2232 ON "vote"."member_id" = "direct_voter"."member_id"

2233 AND "vote"."issue_id" = "direct_voter"."issue_id"

2234 WHERE "vote"."issue_id" = "issue_id_p"

2235 GROUP BY "initiative_id"

2236 ) AS "subquery"

2237 WHERE "initiative"."admitted"

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

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

2240 END;

2241 $$;

2242

2243 COMMENT ON FUNCTION "close_voting"

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

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

2246

2247

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

2249 RETURNS INT4[]

2250 LANGUAGE 'plpgsql' IMMUTABLE AS $$

2251 DECLARE

2252 "i" INTEGER;

2253 "ary_text_v" TEXT;

2254 BEGIN

2255 IF "dim_p" >= 1 THEN

2256 "ary_text_v" := '{NULL';

2257 "i" := "dim_p";

2258 LOOP

2259 "i" := "i" - 1;

2260 EXIT WHEN "i" = 0;

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

2262 END LOOP;

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

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

2265 ELSE

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

2267 END IF;

2268 END;

2269 $$;

2270

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

2272

2273

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

2275 RETURNS INT4[][]

2276 LANGUAGE 'plpgsql' IMMUTABLE AS $$

2277 DECLARE

2278 "i" INTEGER;

2279 "row_text_v" TEXT;

2280 "ary_text_v" TEXT;

2281 BEGIN

2282 IF "dim_p" >= 1 THEN

2283 "row_text_v" := '{NULL';

2284 "i" := "dim_p";

2285 LOOP

2286 "i" := "i" - 1;

2287 EXIT WHEN "i" = 0;

2288 "row_text_v" := "row_text_v" || ',NULL';

2289 END LOOP;

2290 "row_text_v" := "row_text_v" || '}';

2291 "ary_text_v" := '{' || "row_text_v";

2292 "i" := "dim_p";

2293 LOOP

2294 "i" := "i" - 1;

2295 EXIT WHEN "i" = 0;

2296 "ary_text_v" := "ary_text_v" || ',' || "row_text_v";

2297 END LOOP;

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

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

2300 ELSE

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

2302 END IF;

2303 END;

2304 $$;

2305

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

2307

2308

2309 CREATE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE)

2310 RETURNS VOID

2311 LANGUAGE 'plpgsql' VOLATILE AS $$

2312 DECLARE

2313 "dimension_v" INTEGER;

2314 "matrix" INT4[][];

2315 "i" INTEGER;

2316 "j" INTEGER;

2317 "k" INTEGER;

2318 "battle_row" "battle"%ROWTYPE;

2319 "rank_ary" INT4[];

2320 "rank_v" INT4;

2321 "done_v" INTEGER;

2322 "winners_ary" INTEGER[];

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

2324 BEGIN

2325 PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE;

2326 -- Prepare matrix for Schulze-Method:

2327 SELECT count(1) INTO "dimension_v"

2328 FROM "battle_participant" WHERE "issue_id" = "issue_id_p";

2329 IF "dimension_v" = 1 THEN

2330 UPDATE "initiative" SET

2331 "rank" = 1

2332 FROM "battle_participant"

2333 WHERE "initiative"."issue_id" = "issue_id_p"

2334 AND "initiative"."id" = "battle_participant"."initiative_id";

2335 ELSIF "dimension_v" > 1 THEN

2336 "matrix" := "init_square_matrix"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4)

2337 "i" := 1;

2338 "j" := 2;

2339 -- Fill matrix with data from "battle" view

2340 FOR "battle_row" IN

2341 SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p"

2342 ORDER BY "winning_initiative_id", "losing_initiative_id"

2343 LOOP

2344 "matrix"["i"]["j"] := "battle_row"."count";

2345 IF "j" = "dimension_v" THEN

2346 "i" := "i" + 1;

2347 "j" := 1;

2348 ELSE

2349 "j" := "j" + 1;

2350 IF "j" = "i" THEN

2351 "j" := "j" + 1;

2352 END IF;

2353 END IF;

2354 END LOOP;

2355 IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN

2356 RAISE EXCEPTION 'Wrong battle count (should not happen)';

2357 END IF;

2358 -- Delete losers from matrix:

2359 "i" := 1;

2360 LOOP

2361 "j" := "i" + 1;

2362 LOOP

2363 IF "i" != "j" THEN

2364 IF "matrix"["i"]["j"] < "matrix"["j"]["i"] THEN

2365 "matrix"["i"]["j"] := 0;

2366 ELSIF matrix[j][i] < matrix[i][j] THEN

2367 "matrix"["j"]["i"] := 0;

2368 ELSE

2369 "matrix"["i"]["j"] := 0;

2370 "matrix"["j"]["i"] := 0;

2371 END IF;

2372 END IF;

2373 EXIT WHEN "j" = "dimension_v";

2374 "j" := "j" + 1;

2375 END LOOP;

2376 EXIT WHEN "i" = "dimension_v" - 1;

2377 "i" := "i" + 1;

2378 END LOOP;

2379 -- Find best paths:

2380 "i" := 1;

2381 LOOP

2382 "j" := 1;

2383 LOOP

2384 IF "i" != "j" THEN

2385 "k" := 1;

2386 LOOP

2387 IF "i" != "k" AND "j" != "k" THEN

2388 IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN

2389 IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN

2390 "matrix"["j"]["k"] := "matrix"["j"]["i"];

2391 END IF;

2392 ELSE

2393 IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN

2394 "matrix"["j"]["k"] := "matrix"["i"]["k"];

2395 END IF;

2396 END IF;

2397 END IF;

2398 EXIT WHEN "k" = "dimension_v";

2399 "k" := "k" + 1;

2400 END LOOP;

2401 END IF;

2402 EXIT WHEN "j" = "dimension_v";

2403 "j" := "j" + 1;

2404 END LOOP;

2405 EXIT WHEN "i" = "dimension_v";

2406 "i" := "i" + 1;

2407 END LOOP;

2408 -- Determine order of winners:

2409 "rank_ary" := "init_array"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4)

2410 "rank_v" := 1;

2411 "done_v" := 0;

2412 LOOP

2413 "winners_ary" := '{}';

2414 "i" := 1;

2415 LOOP

2416 IF "rank_ary"["i"] ISNULL THEN

2417 "j" := 1;

2418 LOOP

2419 IF

2420 "i" != "j" AND

2421 "rank_ary"["j"] ISNULL AND

2422 "matrix"["j"]["i"] > "matrix"["i"]["j"]

2423 THEN

2424 -- someone else is better

2425 EXIT;

2426 END IF;

2427 IF "j" = "dimension_v" THEN

2428 -- noone is better

2429 "winners_ary" := "winners_ary" || "i";

2430 EXIT;

2431 END IF;

2432 "j" := "j" + 1;

2433 END LOOP;

2434 END IF;

2435 EXIT WHEN "i" = "dimension_v";

2436 "i" := "i" + 1;

2437 END LOOP;

2438 "i" := 1;

2439 LOOP

2440 "rank_ary"["winners_ary"["i"]] := "rank_v";

2441 "done_v" := "done_v" + 1;

2442 EXIT WHEN "i" = array_upper("winners_ary", 1);

2443 "i" := "i" + 1;

2444 END LOOP;

2445 EXIT WHEN "done_v" = "dimension_v";

2446 "rank_v" := "rank_v" + 1;

2447 END LOOP;

2448 -- write preliminary ranks:

2449 "i" := 1;

2450 FOR "initiative_id_v" IN

2451 SELECT "initiative"."id"

2452 FROM "initiative" JOIN "battle_participant"

2453 ON "initiative"."id" = "battle_participant"."initiative_id"

2454 WHERE "initiative"."issue_id" = "issue_id_p"

2455 ORDER BY "initiative"."id"

2456 LOOP

2457 UPDATE "initiative" SET "rank" = "rank_ary"["i"]

2458 WHERE "id" = "initiative_id_v";

2459 "i" := "i" + 1;

2460 END LOOP;

2461 IF "i" != "dimension_v" + 1 THEN

2462 RAISE EXCEPTION 'Wrong winner count (should not happen)';

2463 END IF;

2464 -- straighten ranks (start counting with 1, no equal ranks):

2465 "rank_v" := 1;

2466 FOR "initiative_id_v" IN

2467 SELECT "id" FROM "initiative"

2468 WHERE "issue_id" = "issue_id_p" AND "rank" NOTNULL

2469 ORDER BY

2470 "rank",

2471 "vote_ratio"("positive_votes", "negative_votes") DESC,

2472 "id"

2473 LOOP

2474 UPDATE "initiative" SET "rank" = "rank_v"

2475 WHERE "id" = "initiative_id_v";

2476 "rank_v" := "rank_v" + 1;

2477 END LOOP;

2478 END IF;

2479 -- mark issue as finished

2480 UPDATE "issue" SET "ranks_available" = TRUE

2481 WHERE "id" = "issue_id_p";

2482 RETURN;

2483 END;

2484 $$;

2485

2486 COMMENT ON FUNCTION "calculate_ranks"

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

2488 IS 'Determine ranking (Votes have to be counted first)';

2489

2490

2491

2492 -----------------------------

2493 -- Automatic state changes --

2494 -----------------------------

2495

2496

2497 CREATE FUNCTION "check_issue"

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

2499 RETURNS VOID

2500 LANGUAGE 'plpgsql' VOLATILE AS $$

2501 DECLARE

2502 "issue_row" "issue"%ROWTYPE;

2503 "policy_row" "policy"%ROWTYPE;

2504 "voting_requested_v" BOOLEAN;

2505 BEGIN

2506 PERFORM "global_lock"();

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

2508 IF "issue_row"."closed" ISNULL THEN

2509 SELECT * INTO "policy_row" FROM "policy"

2510 WHERE "id" = "issue_row"."policy_id";

2511 IF "issue_row"."fully_frozen" ISNULL THEN

2512 PERFORM "create_snapshot"("issue_id_p");

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

2514 END IF;

2515 IF "issue_row"."accepted" ISNULL THEN

2516 IF EXISTS (

2517 SELECT NULL FROM "initiative"

2518 WHERE "issue_id" = "issue_id_p"

2519 AND "supporter_count" > 0

2520 AND "supporter_count" * "policy_row"."issue_quorum_den"

2521 >= "issue_row"."population" * "policy_row"."issue_quorum_num"

2522 ) THEN

2523 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');

2524 "issue_row"."accepted" = now(); -- NOTE: "issue_row" used later

2525 UPDATE "issue" SET "accepted" = "issue_row"."accepted"

2526 WHERE "id" = "issue_row"."id";

2527 ELSIF

2528 now() >= "issue_row"."created" + "policy_row"."admission_time"

2529 THEN

2530 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');

2531 UPDATE "issue" SET "closed" = now()

2532 WHERE "id" = "issue_row"."id";

2533 END IF;

2534 END IF;

2535 IF

2536 "issue_row"."accepted" NOTNULL AND

2537 "issue_row"."half_frozen" ISNULL

2538 THEN

2539 SELECT

2540 CASE

2541 WHEN "vote_now" * 2 > "issue_row"."population" THEN

2542 TRUE

2543 WHEN "vote_later" * 2 > "issue_row"."population" THEN

2544 FALSE

2545 ELSE NULL

2546 END

2547 INTO "voting_requested_v"

2548 FROM "issue" WHERE "id" = "issue_id_p";

2549 IF

2550 "voting_requested_v" OR (

2551 "voting_requested_v" ISNULL AND

2552 now() >= "issue_row"."accepted" + "policy_row"."discussion_time"

2553 )

2554 THEN

2555 "issue_row"."half_frozen" = now(); -- NOTE: "issue_row" used later

2556 UPDATE "issue" SET "half_frozen" = "issue_row"."half_frozen"

2557 WHERE "id" = "issue_row"."id";

2558 END IF;

2559 END IF;

2560 IF

2561 "issue_row"."half_frozen" NOTNULL AND

2562 "issue_row"."fully_frozen" ISNULL AND

2563 now() >= "issue_row"."half_frozen" + "policy_row"."verification_time"

2564 THEN

2565 "issue_row"."fully_frozen" = now(); -- NOTE: "issue_row" used later

2566 PERFORM "freeze_after_snapshot"("issue_id_p");

2567 END IF;

2568 IF

2569 "issue_row"."fully_frozen" NOTNULL AND

2570 now() >= "issue_row"."fully_frozen" + "policy_row"."voting_time"

2571 THEN

2572 PERFORM "close_voting"("issue_id_p");

2573 END IF;

2574 END IF;

2575 RETURN;

2576 END;

2577 $$;

2578

2579 COMMENT ON FUNCTION "check_issue"

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

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

2582

2583

2584 CREATE FUNCTION "check_everything"()

2585 RETURNS VOID

2586 LANGUAGE 'plpgsql' VOLATILE AS $$

2587 DECLARE

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

2589 BEGIN

2590 DELETE FROM "expired_session";

2591 PERFORM "calculate_member_counts"();

2592 FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP

2593 PERFORM "check_issue"("issue_id_v");

2594 END LOOP;

2595 FOR "issue_id_v" IN SELECT "id" FROM "issue_with_ranks_missing" LOOP

2596 PERFORM "calculate_ranks"("issue_id_v");

2597 END LOOP;

2598 RETURN;

2599 END;

2600 $$;

2601

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

2603

2604

2605

2606 ------------------------------

2607 -- Deletion of private data --

2608 ------------------------------

2609

2610

2611 CREATE FUNCTION "delete_private_data"()

2612 RETURNS VOID

2613 LANGUAGE 'plpgsql' VOLATILE AS $$

2614 DECLARE

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

2616 BEGIN

2617 DELETE FROM "session";

2618 DELETE FROM "contact" WHERE NOT "public";

2619 DELETE FROM "direct_voter" USING "issue"

2620 WHERE "direct_voter"."issue_id" = "issue"."id"

2621 AND "issue"."closed" ISNULL;

2622 RETURN;

2623 END;

2624 $$;

2625

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

2627

2628

2629

2630 COMMIT;