liquid_feedback_core: 3ea7a72ed7e7 core.sql

liquid_feedback_core

view core.sql @ 6:3ea7a72ed7e7

Version beta7

Fixed bug in function close_voting(...) leading to wrong voter_count values in issue table

Added avatar column to member table for storing a small picture of the member

author	jbe
date	Wed Nov 18 12:00:00 2009 +0100 (2009-11-18)
parents	1cbdd3975a61
children	69d84040fb93

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 ('beta7', NULL, NULL, NULL))

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

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

15 -- Tables and indicies --

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

19 CREATE TABLE "member" (

20 "id" SERIAL4 PRIMARY KEY,

21 "login" TEXT NOT NULL UNIQUE,

22 "password" TEXT,

23 "active" BOOLEAN NOT NULL DEFAULT TRUE,

24 "admin" BOOLEAN NOT NULL DEFAULT FALSE,

25 "name" TEXT,

26 "ident_number" TEXT UNIQUE,

27 "avatar" BYTEA );

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

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

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

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

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

35 COMMENT ON COLUMN "member"."ident_number" IS 'Additional information about the members idenficication number within the organization';

38 CREATE TABLE "member_count" (

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

40 "total_count" INT4 NOT NULL );

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

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

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

48 CREATE TABLE "contact" (

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

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

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

52 "public" BOOLEAN NOT NULL DEFAULT FALSE );

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

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

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

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

61 CREATE TABLE "session" (

62 "ident" TEXT PRIMARY KEY,

63 "additional_secret" TEXT,

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

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

66 "lang" TEXT );

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

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

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

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

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

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

77 CREATE TABLE "policy" (

78 "id" SERIAL4 PRIMARY KEY,

79 "active" BOOLEAN NOT NULL DEFAULT TRUE,

80 "name" TEXT NOT NULL UNIQUE,

81 "description" TEXT NOT NULL DEFAULT '',

82 "admission_time" INTERVAL NOT NULL,

83 "discussion_time" INTERVAL NOT NULL,

84 "verification_time" INTERVAL NOT NULL,

85 "voting_time" INTERVAL NOT NULL,

86 "issue_quorum_num" INT4 NOT NULL,

87 "issue_quorum_den" INT4 NOT NULL,

88 "initiative_quorum_num" INT4 NOT NULL,

89 "initiative_quorum_den" INT4 NOT NULL );

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

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

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

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

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

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

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

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

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

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

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

103

104

105 CREATE TABLE "area" (

106 "id" SERIAL4 PRIMARY KEY,

107 "active" BOOLEAN NOT NULL DEFAULT TRUE,

108 "name" TEXT NOT NULL,

109 "description" TEXT NOT NULL DEFAULT '',

110 "direct_member_count" INT4,

111 "member_weight" INT4,

112 "autoreject_weight" INT4 );

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

114

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

116

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

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

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

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

121

122

123 CREATE TABLE "issue" (

124 "id" SERIAL4 PRIMARY KEY,

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

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

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

128 "accepted" TIMESTAMPTZ,

129 "half_frozen" TIMESTAMPTZ,

130 "fully_frozen" TIMESTAMPTZ,

131 "closed" TIMESTAMPTZ,

132 "ranks_available" BOOLEAN NOT NULL DEFAULT FALSE,

133 "snapshot" TIMESTAMPTZ,

134 "population" INT4,

135 "vote_now" INT4,

136 "vote_later" INT4,

137 "voter_count" INT4,

138 CONSTRAINT "valid_state" CHECK (

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

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

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

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

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

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

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

146 CONSTRAINT "state_change_order" CHECK (

147 "created" <= "accepted" AND

148 "accepted" <= "half_frozen" AND

149 "half_frozen" <= "fully_frozen" AND

150 "fully_frozen" <= "closed" ),

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

152 CONSTRAINT "freeze_requires_snapshot" CHECK ("fully_frozen" ISNULL OR "snapshot" NOTNULL) );

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

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

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

156

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

158

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

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

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

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

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

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

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

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

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

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

169

170

171 CREATE TABLE "initiative" (

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

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

174 "id" SERIAL4 PRIMARY KEY,

175 "name" TEXT NOT NULL,

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

177 "revoked" TIMESTAMPTZ,

178 "admitted" BOOLEAN,

179 "supporter_count" INT4,

180 "informed_supporter_count" INT4,

181 "satisfied_supporter_count" INT4,

182 "satisfied_informed_supporter_count" INT4,

183 "positive_votes" INT4,

184 "negative_votes" INT4,

185 "rank" INT4,

186 CONSTRAINT "revoked_initiatives_cant_be_admitted"

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

188 CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results"

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

190

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

192

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

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

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

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

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

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

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

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

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

202

203

204 CREATE TABLE "draft" (

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

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

207 "id" SERIAL8 PRIMARY KEY,

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

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

210 "content" TEXT NOT NULL );

211

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

213

214

215 CREATE TABLE "suggestion" (

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

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

218 "id" SERIAL8 PRIMARY KEY,

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

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

221 "name" TEXT NOT NULL,

222 "description" TEXT NOT NULL DEFAULT '',

223 "minus2_unfulfilled_count" INT4,

224 "minus2_fulfilled_count" INT4,

225 "minus1_unfulfilled_count" INT4,

226 "minus1_fulfilled_count" INT4,

227 "plus1_unfulfilled_count" INT4,

228 "plus1_fulfilled_count" INT4,

229 "plus2_unfulfilled_count" INT4,

230 "plus2_fulfilled_count" INT4 );

231

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

233

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

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

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

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

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

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

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

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

242

243

244 CREATE TABLE "membership" (

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

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

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

248 "autoreject" BOOLEAN NOT NULL DEFAULT FALSE );

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

250

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

252

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

254

255

256 CREATE TABLE "interest" (

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

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

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

260 "autoreject" BOOLEAN NOT NULL,

261 "voting_requested" BOOLEAN );

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

263

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

265

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

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

268

269

270 CREATE TABLE "initiator" (

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

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

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

274 "accepted" BOOLEAN NOT NULL DEFAULT TRUE );

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

276

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

278

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

280

281

282 CREATE TABLE "supporter" (

283 "issue_id" INT4 NOT NULL,

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

285 "initiative_id" INT4,

286 "member_id" INT4,

287 "draft_id" INT8 NOT NULL,

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

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

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

291

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

293

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

295

296

297 CREATE TABLE "opinion" (

298 "initiative_id" INT4 NOT NULL,

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

300 "suggestion_id" INT8,

301 "member_id" INT4,

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

303 "fulfilled" BOOLEAN NOT NULL DEFAULT FALSE,

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

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

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

307

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

309

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

311

312

313 CREATE TABLE "delegation" (

314 "id" SERIAL8 PRIMARY KEY,

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

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

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

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

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

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

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

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

323 CREATE UNIQUE INDEX "delegation_default_truster_id_trustee_id_unique_idx"

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

325 WHERE "area_id" ISNULL AND "issue_id" ISNULL;

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

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

328

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

330

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

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

333

334

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

336

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

338

339

340 CREATE TABLE "direct_population_snapshot" (

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

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

343 "event" "snapshot_event",

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

345 "weight" INT4,

346 "interest_exists" BOOLEAN NOT NULL );

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

348

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

350

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

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

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

354

355

356 CREATE TABLE "delegating_population_snapshot" (

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

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

359 "event" "snapshot_event",

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

361 "delegate_member_ids" INT4[] NOT NULL );

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

363

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

365

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

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

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

369

370

371 CREATE TABLE "direct_interest_snapshot" (

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

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

374 "event" "snapshot_event",

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

376 "weight" INT4,

377 "voting_requested" BOOLEAN );

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

379

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

381

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

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

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

385

386

387 CREATE TABLE "delegating_interest_snapshot" (

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

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

390 "event" "snapshot_event",

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

392 "delegate_member_ids" INT4[] NOT NULL );

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

394

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

396

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

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

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

400

401

402 CREATE TABLE "direct_supporter_snapshot" (

403 "issue_id" INT4 NOT NULL,

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

405 "initiative_id" INT4,

406 "event" "snapshot_event",

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

408 "informed" BOOLEAN NOT NULL,

409 "satisfied" BOOLEAN NOT NULL,

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

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

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

413

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

415

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

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

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

419

420

421 CREATE TABLE "direct_voter" (

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

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

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

425 "weight" INT4,

426 "autoreject" BOOLEAN NOT NULL DEFAULT FALSE );

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

428

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

430

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

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

433

434

435 CREATE TABLE "delegating_voter" (

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

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

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

439 "delegate_member_ids" INT4[] NOT NULL );

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

441

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

443

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

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

446

447

448 CREATE TABLE "vote" (

449 "issue_id" INT4 NOT NULL,

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

451 "initiative_id" INT4,

452 "member_id" INT4,

453 "grade" INT4,

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

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

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

457

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

459

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

461

462

463

464 ----------------------------

465 -- Additional constraints --

466 ----------------------------

467

468

469 CREATE FUNCTION "issue_requires_first_initiative_trigger"()

470 RETURNS TRIGGER

471 LANGUAGE 'plpgsql' VOLATILE AS $$

472 BEGIN

473 IF NOT EXISTS (

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

475 ) THEN

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

477 -- ERRCODE = 'integrity_constraint_violation',

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

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

480 END IF;

481 RETURN NULL;

482 END;

483 $$;

484

485 CREATE CONSTRAINT TRIGGER "issue_requires_first_initiative"

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

487 FOR EACH ROW EXECUTE PROCEDURE

488 "issue_requires_first_initiative_trigger"();

489

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

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

492

493

494 CREATE FUNCTION "last_initiative_deletes_issue_trigger"()

495 RETURNS TRIGGER

496 LANGUAGE 'plpgsql' VOLATILE AS $$

497 DECLARE

498 "reference_lost" BOOLEAN;

499 BEGIN

500 IF TG_OP = 'DELETE' THEN

501 "reference_lost" := TRUE;

502 ELSE

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

504 END IF;

505 IF

506 "reference_lost" AND NOT EXISTS (

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

508 )

509 THEN

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

511 END IF;

512 RETURN NULL;

513 END;

514 $$;

515

516 CREATE CONSTRAINT TRIGGER "last_initiative_deletes_issue"

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

518 FOR EACH ROW EXECUTE PROCEDURE

519 "last_initiative_deletes_issue_trigger"();

520

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

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

523

524

525 CREATE FUNCTION "initiative_requires_first_draft_trigger"()

526 RETURNS TRIGGER

527 LANGUAGE 'plpgsql' VOLATILE AS $$

528 BEGIN

529 IF NOT EXISTS (

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

531 ) THEN

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

533 -- ERRCODE = 'integrity_constraint_violation',

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

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

536 END IF;

537 RETURN NULL;

538 END;

539 $$;

540

541 CREATE CONSTRAINT TRIGGER "initiative_requires_first_draft"

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

543 FOR EACH ROW EXECUTE PROCEDURE

544 "initiative_requires_first_draft_trigger"();

545

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

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

548

549

550 CREATE FUNCTION "last_draft_deletes_initiative_trigger"()

551 RETURNS TRIGGER

552 LANGUAGE 'plpgsql' VOLATILE AS $$

553 DECLARE

554 "reference_lost" BOOLEAN;

555 BEGIN

556 IF TG_OP = 'DELETE' THEN

557 "reference_lost" := TRUE;

558 ELSE

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

560 END IF;

561 IF

562 "reference_lost" AND NOT EXISTS (

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

564 )

565 THEN

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

567 END IF;

568 RETURN NULL;

569 END;

570 $$;

571

572 CREATE CONSTRAINT TRIGGER "last_draft_deletes_initiative"

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

574 FOR EACH ROW EXECUTE PROCEDURE

575 "last_draft_deletes_initiative_trigger"();

576

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

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

579

580

581 CREATE FUNCTION "suggestion_requires_first_opinion_trigger"()

582 RETURNS TRIGGER

583 LANGUAGE 'plpgsql' VOLATILE AS $$

584 BEGIN

585 IF NOT EXISTS (

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

587 ) THEN

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

589 END IF;

590 RETURN NULL;

591 END;

592 $$;

593

594 CREATE CONSTRAINT TRIGGER "suggestion_requires_first_opinion"

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

596 FOR EACH ROW EXECUTE PROCEDURE

597 "suggestion_requires_first_opinion_trigger"();

598

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

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

601

602

603 CREATE FUNCTION "last_opinion_deletes_suggestion_trigger"()

604 RETURNS TRIGGER

605 LANGUAGE 'plpgsql' VOLATILE AS $$

606 DECLARE

607 "reference_lost" BOOLEAN;

608 BEGIN

609 IF TG_OP = 'DELETE' THEN

610 "reference_lost" := TRUE;

611 ELSE

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

613 END IF;

614 IF

615 "reference_lost" AND NOT EXISTS (

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

617 )

618 THEN

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

620 END IF;

621 RETURN NULL;

622 END;

623 $$;

624

625 CREATE CONSTRAINT TRIGGER "last_opinion_deletes_suggestion"

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

627 FOR EACH ROW EXECUTE PROCEDURE

628 "last_opinion_deletes_suggestion_trigger"();

629

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

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

632

633

634

635 --------------------------------------------------------------------

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

637 --------------------------------------------------------------------

638

639 CREATE FUNCTION "autofill_issue_id_trigger"()

640 RETURNS TRIGGER

641 LANGUAGE 'plpgsql' VOLATILE AS $$

642 BEGIN

643 IF NEW."issue_id" ISNULL THEN

644 SELECT "issue_id" INTO NEW."issue_id"

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

646 END IF;

647 RETURN NEW;

648 END;

649 $$;

650

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

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

653

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

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

656

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

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

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

660

661

662 CREATE FUNCTION "autofill_initiative_id_trigger"()

663 RETURNS TRIGGER

664 LANGUAGE 'plpgsql' VOLATILE AS $$

665 BEGIN

666 IF NEW."initiative_id" ISNULL THEN

667 SELECT "initiative_id" INTO NEW."initiative_id"

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

669 END IF;

670 RETURN NEW;

671 END;

672 $$;

673

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

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

676

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

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

679

680

681

682 -----------------------------------------------------

683 -- Automatic calculation of certain default values --

684 -----------------------------------------------------

685

686 CREATE FUNCTION "copy_autoreject_trigger"()

687 RETURNS TRIGGER

688 LANGUAGE 'plpgsql' VOLATILE AS $$

689 BEGIN

690 IF NEW."autoreject" ISNULL THEN

691 SELECT "membership"."autoreject" INTO NEW."autoreject"

692 FROM "issue" JOIN "membership"

693 ON "issue"."area_id" = "membership"."area_id"

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

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

696 END IF;

697 IF NEW."autoreject" ISNULL THEN

698 NEW."autoreject" := FALSE;

699 END IF;

700 RETURN NEW;

701 END;

702 $$;

703

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

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

706

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

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

709

710

711 CREATE FUNCTION "supporter_default_for_draft_id_trigger"()

712 RETURNS TRIGGER

713 LANGUAGE 'plpgsql' VOLATILE AS $$

714 BEGIN

715 IF NEW."draft_id" ISNULL THEN

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

717 WHERE "initiative_id" = NEW."initiative_id";

718 END IF;

719 RETURN NEW;

720 END;

721 $$;

722

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

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

725

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

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

728

729

730

731 ----------------------------------------

732 -- Automatic creation of dependencies --

733 ----------------------------------------

734

735 CREATE FUNCTION "autocreate_interest_trigger"()

736 RETURNS TRIGGER

737 LANGUAGE 'plpgsql' VOLATILE AS $$

738 BEGIN

739 IF NOT EXISTS (

740 SELECT NULL FROM "initiative" JOIN "interest"

741 ON "initiative"."issue_id" = "interest"."issue_id"

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

743 AND "interest"."member_id" = NEW."member_id"

744 ) THEN

745 BEGIN

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

747 SELECT "issue_id", NEW."member_id"

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

749 EXCEPTION WHEN unique_violation THEN END;

750 END IF;

751 RETURN NEW;

752 END;

753 $$;

754

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

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

757

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

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

760

761

762 CREATE FUNCTION "autocreate_supporter_trigger"()

763 RETURNS TRIGGER

764 LANGUAGE 'plpgsql' VOLATILE AS $$

765 BEGIN

766 IF NOT EXISTS (

767 SELECT NULL FROM "suggestion" JOIN "supporter"

768 ON "suggestion"."initiative_id" = "supporter"."initiative_id"

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

770 AND "supporter"."member_id" = NEW."member_id"

771 ) THEN

772 BEGIN

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

774 SELECT "initiative_id", NEW."member_id"

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

776 EXCEPTION WHEN unique_violation THEN END;

777 END IF;

778 RETURN NEW;

779 END;

780 $$;

781

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

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

784

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

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

787

788

789

790 ------------------------------------------

791 -- Views and helper functions for views --

792 ------------------------------------------

793

794

795 CREATE TYPE "delegation_scope" AS ENUM

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

797

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

799

800

801 CREATE VIEW "global_delegation" AS

802 SELECT

803 "delegation"."id",

804 "delegation"."truster_id",

805 "delegation"."trustee_id"

806 FROM "delegation" JOIN "member"

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

808 WHERE "delegation"."area_id" ISNULL

809 AND "delegation"."issue_id" ISNULL

810 AND "member"."active";

811

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

813

814

815 CREATE VIEW "area_delegation" AS

816 SELECT "subquery".* FROM (

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

818 "area"."id" AS "area_id",

819 "delegation"."id" AS "id",

820 "delegation"."truster_id" AS "truster_id",

821 "delegation"."trustee_id" AS "trustee_id",

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

823 'global'::"delegation_scope"

824 ELSE

825 'area'::"delegation_scope"

826 END AS "scope"

827 FROM "area" JOIN "delegation"

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

829 AND "delegation"."issue_id" ISNULL

830 ORDER BY

831 "area"."id",

832 "delegation"."truster_id",

833 "delegation"."area_id" NULLS LAST

834 ) AS "subquery"

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

836 WHERE "member"."active";

837

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

839

840

841 CREATE VIEW "issue_delegation" AS

842 SELECT "subquery".* FROM (

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

844 "issue"."id" AS "issue_id",

845 "delegation"."id" AS "id",

846 "delegation"."truster_id" AS "truster_id",

847 "delegation"."trustee_id" AS "trustee_id",

848 CASE

849 WHEN

850 "delegation"."area_id" ISNULL AND

851 "delegation"."issue_id" ISNULL

852 THEN 'global'::"delegation_scope"

853 WHEN

854 "delegation"."area_id" NOTNULL

855 THEN 'area'::"delegation_scope"

856 ELSE 'issue'::"delegation_scope"

857 END AS "scope"

858 FROM "issue" JOIN "delegation"

859 ON (

860 "delegation"."area_id" ISNULL OR

861 "delegation"."area_id" = "issue"."area_id"

862 ) AND (

863 "delegation"."issue_id" ISNULL OR

864 "delegation"."issue_id" = "issue"."id"

865 )

866 ORDER BY

867 "issue"."id",

868 "delegation"."truster_id",

869 "delegation"."issue_id" NULLS LAST,

870 "delegation"."area_id" NULLS LAST

871 ) AS "subquery"

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

873 WHERE "member"."active";

874

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

876

877

878 CREATE FUNCTION "membership_weight_with_skipping"

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

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

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

882 RETURNS INT4

883 LANGUAGE 'plpgsql' STABLE AS $$

884 DECLARE

885 "sum_v" INT4;

886 "delegation_row" "area_delegation"%ROWTYPE;

887 BEGIN

888 "sum_v" := 1;

889 FOR "delegation_row" IN

890 SELECT "area_delegation".*

891 FROM "area_delegation" LEFT JOIN "membership"

892 ON "membership"."area_id" = "area_id_p"

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

894 WHERE "area_delegation"."area_id" = "area_id_p"

895 AND "area_delegation"."trustee_id" = "member_id_p"

896 AND "membership"."member_id" ISNULL

897 LOOP

898 IF NOT

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

900 THEN

901 "sum_v" := "sum_v" + "membership_weight_with_skipping"(

902 "area_id_p",

903 "delegation_row"."truster_id",

904 "skip_member_ids_p" || "delegation_row"."truster_id"

905 );

906 END IF;

907 END LOOP;

908 RETURN "sum_v";

909 END;

910 $$;

911

912 CREATE FUNCTION "membership_weight"

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

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

915 RETURNS INT4

916 LANGUAGE 'plpgsql' STABLE AS $$

917 BEGIN

918 RETURN "membership_weight_with_skipping"(

919 "area_id_p",

920 "member_id_p",

921 ARRAY["member_id_p"]

922 );

923 END;

924 $$;

925

926

927 CREATE VIEW "member_count_view" AS

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

929

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

931

932

933 CREATE VIEW "area_member_count" AS

934 SELECT

935 "area"."id" AS "area_id",

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

937 coalesce(

938 sum(

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

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

941 ELSE 0 END

942 )

943 ) AS "member_weight",

944 coalesce(

945 sum(

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

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

948 ELSE 0 END

949 )

950 ) AS "autoreject_weight"

951 FROM "area"

952 LEFT JOIN "membership"

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

954 LEFT JOIN "member"

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

956 AND "member"."active"

957 GROUP BY "area"."id";

958 -- TODO: count delegations

959

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

961

962

963 CREATE VIEW "current_draft" AS

964 SELECT "draft".* FROM (

965 SELECT

966 "initiative"."id" AS "initiative_id",

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

968 FROM "initiative" JOIN "draft"

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

970 GROUP BY "initiative"."id"

971 ) AS "subquery"

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

973

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

975

976

977 CREATE VIEW "critical_opinion" AS

978 SELECT * FROM "opinion"

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

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

981

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

983

984

985 CREATE VIEW "battle_participant" AS

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

987 WHERE "admitted"

988 AND "positive_votes" > "negative_votes";

989

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

991

992

993 CREATE VIEW "battle" AS

994 SELECT

995 "issue"."id" AS "issue_id",

996 "winning_initiative"."initiative_id" AS "winning_initiative_id",

997 "losing_initiative"."initiative_id" AS "losing_initiative_id",

998 sum(

999 CASE WHEN

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

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

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

1003 ) AS "count"

1004 FROM "issue"

1005 LEFT JOIN "direct_voter"

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

1007 JOIN "battle_participant" AS "winning_initiative"

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

1009 JOIN "battle_participant" AS "losing_initiative"

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

1011 LEFT JOIN "vote" AS "better_vote"

1012 ON "direct_voter"."member_id" = "better_vote"."member_id"

1013 AND "winning_initiative"."initiative_id" = "better_vote"."initiative_id"

1014 LEFT JOIN "vote" AS "worse_vote"

1015 ON "direct_voter"."member_id" = "worse_vote"."member_id"

1016 AND "losing_initiative"."initiative_id" = "worse_vote"."initiative_id"

1017 WHERE

1018 "winning_initiative"."initiative_id" !=

1019 "losing_initiative"."initiative_id"

1020 GROUP BY

1021 "issue"."id",

1022 "winning_initiative"."initiative_id",

1023 "losing_initiative"."initiative_id";

1024

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

1026

1027

1028 CREATE VIEW "expired_session" AS

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

1030

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

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

1033

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

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

1036

1037

1038 CREATE VIEW "open_issue" AS

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

1040

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

1042

1043

1044 CREATE VIEW "issue_with_ranks_missing" AS

1045 SELECT * FROM "issue"

1046 WHERE "fully_frozen" NOTNULL

1047 AND "closed" NOTNULL

1048 AND "ranks_available" = FALSE;

1049

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

1051

1052

1053

1054 --------------------------------------------------

1055 -- Set returning function for delegation chains --

1056 --------------------------------------------------

1057

1058

1059 CREATE TYPE "delegation_chain_loop_tag" AS ENUM

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

1061

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

1063

1064

1065 CREATE TYPE "delegation_chain_row" AS (

1066 "index" INT4,

1067 "member_id" INT4,

1068 "member_active" BOOLEAN,

1069 "participation" BOOLEAN,

1070 "overridden" BOOLEAN,

1071 "scope_in" "delegation_scope",

1072 "scope_out" "delegation_scope",

1073 "loop" "delegation_chain_loop_tag" );

1074

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

1076

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

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

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

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

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

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

1083

1084

1085 CREATE FUNCTION "delegation_chain"

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

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

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

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

1090 RETURNS SETOF "delegation_chain_row"

1091 LANGUAGE 'plpgsql' STABLE AS $$

1092 DECLARE

1093 "issue_row" "issue"%ROWTYPE;

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

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

1096 "output_row" "delegation_chain_row";

1097 "output_rows" "delegation_chain_row"[];

1098 "delegation_row" "delegation"%ROWTYPE;

1099 "row_count" INT4;

1100 "i" INT4;

1101 "loop_v" BOOLEAN;

1102 BEGIN

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

1104 "visited_member_ids" := '{}';

1105 "loop_member_id_v" := NULL;

1106 "output_rows" := '{}';

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

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

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

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

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

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

1113 LOOP

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

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

1116 ELSE

1117 "visited_member_ids" :=

1118 "visited_member_ids" || "output_row"."member_id";

1119 END IF;

1120 IF "output_row"."participation" THEN

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

1122 END IF;

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

1124 IF EXISTS (

1125 SELECT NULL FROM "member"

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

1127 ) THEN

1128 IF "area_id_p" ISNULL AND "issue_id_p" ISNULL THEN

1129 SELECT * INTO "delegation_row" FROM "delegation"

1130 WHERE "truster_id" = "output_row"."member_id"

1131 AND "area_id" ISNULL AND "issue_id" ISNULL;

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

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

1134 SELECT NULL FROM "membership"

1135 WHERE "area_id" = "area_id_p"

1136 AND "member_id" = "output_row"."member_id"

1137 );

1138 SELECT * INTO "delegation_row" FROM "delegation"

1139 WHERE "truster_id" = "output_row"."member_id"

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

1141 AND "issue_id" ISNULL

1142 ORDER BY "area_id" NULLS LAST;

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

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

1145 SELECT NULL FROM "interest"

1146 WHERE "issue_id" = "issue_id_p"

1147 AND "member_id" = "output_row"."member_id"

1148 );

1149 SELECT * INTO "delegation_row" FROM "delegation"

1150 WHERE "truster_id" = "output_row"."member_id"

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

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

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

1154 ELSE

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

1156 END IF;

1157 ELSE

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

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

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

1161 "delegation_row" := ROW(NULL);

1162 END IF;

1163 IF

1164 "output_row"."member_id" = "member_id_p" AND

1165 "simulate_trustee_id_p" NOTNULL

1166 THEN

1167 "output_row"."scope_out" := CASE

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

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

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

1171 END;

1172 "output_rows" := "output_rows" || "output_row";

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

1174 ELSIF "delegation_row"."trustee_id" NOTNULL THEN

1175 "output_row"."scope_out" := CASE

1176 WHEN

1177 "delegation_row"."area_id" ISNULL AND

1178 "delegation_row"."issue_id" ISNULL

1179 THEN 'global'

1180 WHEN

1181 "delegation_row"."area_id" NOTNULL AND

1182 "delegation_row"."issue_id" ISNULL

1183 THEN 'area'

1184 WHEN

1185 "delegation_row"."area_id" ISNULL AND

1186 "delegation_row"."issue_id" NOTNULL

1187 THEN 'issue'

1188 END;

1189 "output_rows" := "output_rows" || "output_row";

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

1191 ELSE

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

1193 "output_rows" := "output_rows" || "output_row";

1194 EXIT;

1195 END IF;

1196 EXIT WHEN "loop_member_id_v" NOTNULL;

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

1198 END LOOP;

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

1200 "i" := 1;

1201 "loop_v" := FALSE;

1202 LOOP

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

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

1205 IF "loop_v" THEN

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

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

1208 ELSIF "i" = "row_count" THEN

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

1210 ELSE

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

1212 END IF;

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

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

1215 "loop_v" := TRUE;

1216 END IF;

1217 IF "area_id_p" ISNULL AND "issue_id_p" ISNULL THEN

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

1219 END IF;

1220 RETURN NEXT "output_row";

1221 "i" := "i" + 1;

1222 END LOOP;

1223 RETURN;

1224 END;

1225 $$;

1226

1227 COMMENT ON FUNCTION "delegation_chain"

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

1229 "area"."id"%TYPE,

1230 "issue"."id"%TYPE,

1231 "member"."id"%TYPE )

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

1233

1234 CREATE FUNCTION "delegation_chain"

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

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

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

1238 RETURNS SETOF "delegation_chain_row"

1239 LANGUAGE 'plpgsql' STABLE AS $$

1240 DECLARE

1241 "result_row" "delegation_chain_row";

1242 BEGIN

1243 FOR "result_row" IN

1244 SELECT * FROM "delegation_chain"(

1245 "member_id_p", "area_id_p", "issue_id_p", NULL

1246 )

1247 LOOP

1248 RETURN NEXT "result_row";

1249 END LOOP;

1250 RETURN;

1251 END;

1252 $$;

1253

1254 COMMENT ON FUNCTION "delegation_chain"

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

1256 "area"."id"%TYPE,

1257 "issue"."id"%TYPE )

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

1259

1260

1261

1262 ------------------------------

1263 -- Comparison by vote count --

1264 ------------------------------

1265

1266 CREATE FUNCTION "vote_ratio"

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

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

1269 RETURNS FLOAT8

1270 LANGUAGE 'plpgsql' STABLE AS $$

1271 DECLARE

1272 "total_v" INT4;

1273 BEGIN

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

1275 IF "total_v" > 0 THEN

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

1277 ELSE

1278 RETURN 0.5;

1279 END IF;

1280 END;

1281 $$;

1282

1283 COMMENT ON FUNCTION "vote_ratio"

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

1285 "initiative"."negative_votes"%TYPE )

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

1287

1288

1289

1290 ------------------------------------------------

1291 -- Locking for snapshots and voting procedure --

1292 ------------------------------------------------

1293

1294 CREATE FUNCTION "global_lock"() RETURNS VOID

1295 LANGUAGE 'plpgsql' VOLATILE AS $$

1296 BEGIN

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

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

1299 LOCK TABLE "member" IN EXCLUSIVE MODE;

1300 LOCK TABLE "area" IN EXCLUSIVE MODE;

1301 LOCK TABLE "membership" IN EXCLUSIVE MODE;

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

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

1304 LOCK TABLE "policy" IN EXCLUSIVE MODE;

1305 LOCK TABLE "issue" IN EXCLUSIVE MODE;

1306 LOCK TABLE "initiative" IN EXCLUSIVE MODE;

1307 LOCK TABLE "draft" IN EXCLUSIVE MODE;

1308 LOCK TABLE "suggestion" IN EXCLUSIVE MODE;

1309 LOCK TABLE "interest" IN EXCLUSIVE MODE;

1310 LOCK TABLE "initiator" IN EXCLUSIVE MODE;

1311 LOCK TABLE "supporter" IN EXCLUSIVE MODE;

1312 LOCK TABLE "opinion" IN EXCLUSIVE MODE;

1313 LOCK TABLE "delegation" IN EXCLUSIVE MODE;

1314 LOCK TABLE "direct_population_snapshot" IN EXCLUSIVE MODE;

1315 LOCK TABLE "delegating_population_snapshot" IN EXCLUSIVE MODE;

1316 LOCK TABLE "direct_interest_snapshot" IN EXCLUSIVE MODE;

1317 LOCK TABLE "delegating_interest_snapshot" IN EXCLUSIVE MODE;

1318 LOCK TABLE "direct_supporter_snapshot" IN EXCLUSIVE MODE;

1319 LOCK TABLE "direct_voter" IN EXCLUSIVE MODE;

1320 LOCK TABLE "delegating_voter" IN EXCLUSIVE MODE;

1321 LOCK TABLE "vote" IN EXCLUSIVE MODE;

1322 RETURN;

1323 END;

1324 $$;

1325

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

1327

1328

1329

1330 -------------------------------

1331 -- Materialize member counts --

1332 -------------------------------

1333

1334 CREATE FUNCTION "calculate_member_counts"()

1335 RETURNS VOID

1336 LANGUAGE 'plpgsql' VOLATILE AS $$

1337 BEGIN

1338 LOCK TABLE "member" IN EXCLUSIVE MODE;

1339 LOCK TABLE "area" IN EXCLUSIVE MODE;

1340 LOCK TABLE "membership" IN EXCLUSIVE MODE;

1341 DELETE FROM "member_count";

1342 INSERT INTO "member_count" ("total_count")

1343 SELECT "total_count" FROM "member_count_view";

1344 UPDATE "area" SET

1345 "direct_member_count" = "view"."direct_member_count",

1346 "member_weight" = "view"."member_weight",

1347 "autoreject_weight" = "view"."autoreject_weight"

1348 FROM "area_member_count" AS "view"

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

1350 RETURN;

1351 END;

1352 $$;

1353

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

1355

1356

1357

1358 ------------------------------

1359 -- Calculation of snapshots --

1360 ------------------------------

1361

1362 CREATE FUNCTION "weight_of_added_delegations_for_population_snapshot"

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

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

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

1366 RETURNS "direct_population_snapshot"."weight"%TYPE

1367 LANGUAGE 'plpgsql' VOLATILE AS $$

1368 DECLARE

1369 "issue_delegation_row" "issue_delegation"%ROWTYPE;

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

1371 "weight_v" INT4;

1372 BEGIN

1373 "weight_v" := 0;

1374 FOR "issue_delegation_row" IN

1375 SELECT * FROM "issue_delegation"

1376 WHERE "trustee_id" = "member_id_p"

1377 AND "issue_id" = "issue_id_p"

1378 LOOP

1379 IF NOT EXISTS (

1380 SELECT NULL FROM "direct_population_snapshot"

1381 WHERE "issue_id" = "issue_id_p"

1382 AND "event" = 'periodic'

1383 AND "member_id" = "issue_delegation_row"."truster_id"

1384 ) AND NOT EXISTS (

1385 SELECT NULL FROM "delegating_population_snapshot"

1386 WHERE "issue_id" = "issue_id_p"

1387 AND "event" = 'periodic'

1388 AND "member_id" = "issue_delegation_row"."truster_id"

1389 ) THEN

1390 "delegate_member_ids_v" :=

1391 "member_id_p" || "delegate_member_ids_p";

1392 INSERT INTO "delegating_population_snapshot"

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

1394 VALUES (

1395 "issue_id_p",

1396 'periodic',

1397 "issue_delegation_row"."truster_id",

1398 "delegate_member_ids_v"

1399 );

1400 "weight_v" := "weight_v" + 1 +

1401 "weight_of_added_delegations_for_population_snapshot"(

1402 "issue_id_p",

1403 "issue_delegation_row"."truster_id",

1404 "delegate_member_ids_v"

1405 );

1406 END IF;

1407 END LOOP;

1408 RETURN "weight_v";

1409 END;

1410 $$;

1411

1412 COMMENT ON FUNCTION "weight_of_added_delegations_for_population_snapshot"

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

1414 "member"."id"%TYPE,

1415 "delegating_population_snapshot"."delegate_member_ids"%TYPE )

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

1417

1418

1419 CREATE FUNCTION "create_population_snapshot"

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

1421 RETURNS VOID

1422 LANGUAGE 'plpgsql' VOLATILE AS $$

1423 DECLARE

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

1425 BEGIN

1426 DELETE FROM "direct_population_snapshot"

1427 WHERE "issue_id" = "issue_id_p"

1428 AND "event" = 'periodic';

1429 DELETE FROM "delegating_population_snapshot"

1430 WHERE "issue_id" = "issue_id_p"

1431 AND "event" = 'periodic';

1432 INSERT INTO "direct_population_snapshot"

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

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

1435 "issue_id_p" AS "issue_id",

1436 'periodic' AS "event",

1437 "subquery"."member_id",

1438 "subquery"."interest_exists"

1439 FROM (

1440 SELECT

1441 "member"."id" AS "member_id",

1442 FALSE AS "interest_exists"

1443 FROM "issue"

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

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

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

1447 WHERE "issue"."id" = "issue_id_p"

1448 AND "member"."active"

1449 UNION

1450 SELECT

1451 "member"."id" AS "member_id",

1452 TRUE AS "interest_exists"

1453 FROM "interest" JOIN "member"

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

1455 WHERE "interest"."issue_id" = "issue_id_p"

1456 AND "member"."active"

1457 ) AS "subquery"

1458 ORDER BY

1459 "issue_id_p",

1460 "subquery"."member_id",

1461 "subquery"."interest_exists" DESC;

1462 FOR "member_id_v" IN

1463 SELECT "member_id" FROM "direct_population_snapshot"

1464 WHERE "issue_id" = "issue_id_p"

1465 AND "event" = 'periodic'

1466 LOOP

1467 UPDATE "direct_population_snapshot" SET

1468 "weight" = 1 +

1469 "weight_of_added_delegations_for_population_snapshot"(

1470 "issue_id_p",

1471 "member_id_v",

1472 '{}'

1473 )

1474 WHERE "issue_id" = "issue_id_p"

1475 AND "event" = 'periodic'

1476 AND "member_id" = "member_id_v";

1477 END LOOP;

1478 RETURN;

1479 END;

1480 $$;

1481

1482 COMMENT ON FUNCTION "create_population_snapshot"

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

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

1485

1486

1487 CREATE FUNCTION "weight_of_added_delegations_for_interest_snapshot"

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

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

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

1491 RETURNS "direct_interest_snapshot"."weight"%TYPE

1492 LANGUAGE 'plpgsql' VOLATILE AS $$

1493 DECLARE

1494 "issue_delegation_row" "issue_delegation"%ROWTYPE;

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

1496 "weight_v" INT4;

1497 BEGIN

1498 "weight_v" := 0;

1499 FOR "issue_delegation_row" IN

1500 SELECT * FROM "issue_delegation"

1501 WHERE "trustee_id" = "member_id_p"

1502 AND "issue_id" = "issue_id_p"

1503 LOOP

1504 IF NOT EXISTS (

1505 SELECT NULL FROM "direct_interest_snapshot"

1506 WHERE "issue_id" = "issue_id_p"

1507 AND "event" = 'periodic'

1508 AND "member_id" = "issue_delegation_row"."truster_id"

1509 ) AND NOT EXISTS (

1510 SELECT NULL FROM "delegating_interest_snapshot"

1511 WHERE "issue_id" = "issue_id_p"

1512 AND "event" = 'periodic'

1513 AND "member_id" = "issue_delegation_row"."truster_id"

1514 ) THEN

1515 "delegate_member_ids_v" :=

1516 "member_id_p" || "delegate_member_ids_p";

1517 INSERT INTO "delegating_interest_snapshot"

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

1519 VALUES (

1520 "issue_id_p",

1521 'periodic',

1522 "issue_delegation_row"."truster_id",

1523 "delegate_member_ids_v"

1524 );

1525 "weight_v" := "weight_v" + 1 +

1526 "weight_of_added_delegations_for_interest_snapshot"(

1527 "issue_id_p",

1528 "issue_delegation_row"."truster_id",

1529 "delegate_member_ids_v"

1530 );

1531 END IF;

1532 END LOOP;

1533 RETURN "weight_v";

1534 END;

1535 $$;

1536

1537 COMMENT ON FUNCTION "weight_of_added_delegations_for_interest_snapshot"

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

1539 "member"."id"%TYPE,

1540 "delegating_interest_snapshot"."delegate_member_ids"%TYPE )

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

1542

1543

1544 CREATE FUNCTION "create_interest_snapshot"

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

1546 RETURNS VOID

1547 LANGUAGE 'plpgsql' VOLATILE AS $$

1548 DECLARE

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

1550 BEGIN

1551 DELETE FROM "direct_interest_snapshot"

1552 WHERE "issue_id" = "issue_id_p"

1553 AND "event" = 'periodic';

1554 DELETE FROM "delegating_interest_snapshot"

1555 WHERE "issue_id" = "issue_id_p"

1556 AND "event" = 'periodic';

1557 DELETE FROM "direct_supporter_snapshot"

1558 WHERE "issue_id" = "issue_id_p"

1559 AND "event" = 'periodic';

1560 INSERT INTO "direct_interest_snapshot"

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

1562 SELECT

1563 "issue_id_p" AS "issue_id",

1564 'periodic' AS "event",

1565 "member"."id" AS "member_id",

1566 "interest"."voting_requested"

1567 FROM "interest" JOIN "member"

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

1569 WHERE "interest"."issue_id" = "issue_id_p"

1570 AND "member"."active";

1571 FOR "member_id_v" IN

1572 SELECT "member_id" FROM "direct_interest_snapshot"

1573 WHERE "issue_id" = "issue_id_p"

1574 AND "event" = 'periodic'

1575 LOOP

1576 UPDATE "direct_interest_snapshot" SET

1577 "weight" = 1 +

1578 "weight_of_added_delegations_for_interest_snapshot"(

1579 "issue_id_p",

1580 "member_id_v",

1581 '{}'

1582 )

1583 WHERE "issue_id" = "issue_id_p"

1584 AND "event" = 'periodic'

1585 AND "member_id" = "member_id_v";

1586 END LOOP;

1587 INSERT INTO "direct_supporter_snapshot"

1588 ( "issue_id", "initiative_id", "event", "member_id",

1589 "informed", "satisfied" )

1590 SELECT

1591 "issue_id_p" AS "issue_id",

1592 "initiative"."id" AS "initiative_id",

1593 'periodic' AS "event",

1594 "member"."id" AS "member_id",

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

1596 NOT EXISTS (

1597 SELECT NULL FROM "critical_opinion"

1598 WHERE "initiative_id" = "initiative"."id"

1599 AND "member_id" = "member"."id"

1600 ) AS "satisfied"

1601 FROM "supporter"

1602 JOIN "member"

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

1604 JOIN "initiative"

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

1606 JOIN "current_draft"

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

1608 JOIN "direct_interest_snapshot"

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

1610 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"

1611 AND "event" = 'periodic'

1612 WHERE "member"."active"

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

1614 RETURN;

1615 END;

1616 $$;

1617

1618 COMMENT ON FUNCTION "create_interest_snapshot"

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

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

1621

1622

1623 CREATE FUNCTION "create_snapshot"

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

1625 RETURNS VOID

1626 LANGUAGE 'plpgsql' VOLATILE AS $$

1627 DECLARE

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

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

1630 BEGIN

1631 PERFORM "global_lock"();

1632 PERFORM "create_population_snapshot"("issue_id_p");

1633 PERFORM "create_interest_snapshot"("issue_id_p");

1634 UPDATE "issue" SET

1635 "snapshot" = now(),

1636 "population" = (

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

1638 FROM "direct_population_snapshot"

1639 WHERE "issue_id" = "issue_id_p"

1640 AND "event" = 'periodic'

1641 ),

1642 "vote_now" = (

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

1644 FROM "direct_interest_snapshot"

1645 WHERE "issue_id" = "issue_id_p"

1646 AND "event" = 'periodic'

1647 AND "voting_requested" = TRUE

1648 ),

1649 "vote_later" = (

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

1651 FROM "direct_interest_snapshot"

1652 WHERE "issue_id" = "issue_id_p"

1653 AND "event" = 'periodic'

1654 AND "voting_requested" = FALSE

1655 )

1656 WHERE "id" = "issue_id_p";

1657 FOR "initiative_id_v" IN

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

1659 LOOP

1660 UPDATE "initiative" SET

1661 "supporter_count" = (

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

1663 FROM "direct_interest_snapshot" AS "di"

1664 JOIN "direct_supporter_snapshot" AS "ds"

1665 ON "di"."member_id" = "ds"."member_id"

1666 WHERE "di"."issue_id" = "issue_id_p"

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

1668 AND "ds"."initiative_id" = "initiative_id_v"

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

1670 ),

1671 "informed_supporter_count" = (

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

1673 FROM "direct_interest_snapshot" AS "di"

1674 JOIN "direct_supporter_snapshot" AS "ds"

1675 ON "di"."member_id" = "ds"."member_id"

1676 WHERE "di"."issue_id" = "issue_id_p"

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

1678 AND "ds"."initiative_id" = "initiative_id_v"

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

1680 AND "ds"."informed"

1681 ),

1682 "satisfied_supporter_count" = (

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

1684 FROM "direct_interest_snapshot" AS "di"

1685 JOIN "direct_supporter_snapshot" AS "ds"

1686 ON "di"."member_id" = "ds"."member_id"

1687 WHERE "di"."issue_id" = "issue_id_p"

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

1689 AND "ds"."initiative_id" = "initiative_id_v"

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

1691 AND "ds"."satisfied"

1692 ),

1693 "satisfied_informed_supporter_count" = (

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

1695 FROM "direct_interest_snapshot" AS "di"

1696 JOIN "direct_supporter_snapshot" AS "ds"

1697 ON "di"."member_id" = "ds"."member_id"

1698 WHERE "di"."issue_id" = "issue_id_p"

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

1700 AND "ds"."initiative_id" = "initiative_id_v"

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

1702 AND "ds"."informed"

1703 AND "ds"."satisfied"

1704 )

1705 WHERE "id" = "initiative_id_v";

1706 FOR "suggestion_id_v" IN

1707 SELECT "id" FROM "suggestion"

1708 WHERE "initiative_id" = "initiative_id_v"

1709 LOOP

1710 UPDATE "suggestion" SET

1711 "minus2_unfulfilled_count" = (

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

1713 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"

1714 ON "opinion"."member_id" = "snapshot"."member_id"

1715 WHERE "opinion"."suggestion_id" = "suggestion_id_v"

1716 AND "snapshot"."issue_id" = "issue_id_p"

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

1718 AND "opinion"."fulfilled" = FALSE

1719 ),

1720 "minus2_fulfilled_count" = (

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

1722 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"

1723 ON "opinion"."member_id" = "snapshot"."member_id"

1724 WHERE "opinion"."suggestion_id" = "suggestion_id_v"

1725 AND "snapshot"."issue_id" = "issue_id_p"

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

1727 AND "opinion"."fulfilled" = TRUE

1728 ),

1729 "minus1_unfulfilled_count" = (

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

1731 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"

1732 ON "opinion"."member_id" = "snapshot"."member_id"

1733 WHERE "opinion"."suggestion_id" = "suggestion_id_v"

1734 AND "snapshot"."issue_id" = "issue_id_p"

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

1736 AND "opinion"."fulfilled" = FALSE

1737 ),

1738 "minus1_fulfilled_count" = (

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

1740 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"

1741 ON "opinion"."member_id" = "snapshot"."member_id"

1742 WHERE "opinion"."suggestion_id" = "suggestion_id_v"

1743 AND "snapshot"."issue_id" = "issue_id_p"

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

1745 AND "opinion"."fulfilled" = TRUE

1746 ),

1747 "plus1_unfulfilled_count" = (

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

1749 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"

1750 ON "opinion"."member_id" = "snapshot"."member_id"

1751 WHERE "opinion"."suggestion_id" = "suggestion_id_v"

1752 AND "snapshot"."issue_id" = "issue_id_p"

1753 AND "opinion"."degree" = 1

1754 AND "opinion"."fulfilled" = FALSE

1755 ),

1756 "plus1_fulfilled_count" = (

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

1758 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"

1759 ON "opinion"."member_id" = "snapshot"."member_id"

1760 WHERE "opinion"."suggestion_id" = "suggestion_id_v"

1761 AND "snapshot"."issue_id" = "issue_id_p"

1762 AND "opinion"."degree" = 1

1763 AND "opinion"."fulfilled" = TRUE

1764 ),

1765 "plus2_unfulfilled_count" = (

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

1767 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"

1768 ON "opinion"."member_id" = "snapshot"."member_id"

1769 WHERE "opinion"."suggestion_id" = "suggestion_id_v"

1770 AND "snapshot"."issue_id" = "issue_id_p"

1771 AND "opinion"."degree" = 2

1772 AND "opinion"."fulfilled" = FALSE

1773 ),

1774 "plus2_fulfilled_count" = (

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

1776 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"

1777 ON "opinion"."member_id" = "snapshot"."member_id"

1778 WHERE "opinion"."suggestion_id" = "suggestion_id_v"

1779 AND "snapshot"."issue_id" = "issue_id_p"

1780 AND "opinion"."degree" = 2

1781 AND "opinion"."fulfilled" = TRUE

1782 )

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

1784 END LOOP;

1785 END LOOP;

1786 RETURN;

1787 END;

1788 $$;

1789

1790 COMMENT ON FUNCTION "create_snapshot"

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

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

1793

1794

1795 CREATE FUNCTION "set_snapshot_event"

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

1797 "event_p" "snapshot_event" )

1798 RETURNS VOID

1799 LANGUAGE 'plpgsql' VOLATILE AS $$

1800 BEGIN

1801 UPDATE "direct_population_snapshot" SET "event" = "event_p"

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

1803 UPDATE "delegating_population_snapshot" SET "event" = "event_p"

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

1805 UPDATE "direct_interest_snapshot" SET "event" = "event_p"

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

1807 UPDATE "delegating_interest_snapshot" SET "event" = "event_p"

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

1809 UPDATE "direct_supporter_snapshot" SET "event" = "event_p"

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

1811 RETURN;

1812 END;

1813 $$;

1814

1815 COMMENT ON FUNCTION "set_snapshot_event"

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

1817 "snapshot_event" )

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

1819

1820

1821

1822 ---------------------

1823 -- Freezing issues --

1824 ---------------------

1825

1826 CREATE FUNCTION "freeze_after_snapshot"

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

1828 RETURNS VOID

1829 LANGUAGE 'plpgsql' VOLATILE AS $$

1830 DECLARE

1831 "issue_row" "issue"%ROWTYPE;

1832 "policy_row" "policy"%ROWTYPE;

1833 "initiative_row" "initiative"%ROWTYPE;

1834 BEGIN

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

1836 SELECT * INTO "policy_row"

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

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

1839 UPDATE "issue" SET

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

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

1842 "fully_frozen" = now()

1843 WHERE "id" = "issue_id_p";

1844 FOR "initiative_row" IN

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

1846 LOOP

1847 IF

1848 "initiative_row"."satisfied_supporter_count" > 0 AND

1849 "initiative_row"."satisfied_supporter_count" *

1850 "policy_row"."initiative_quorum_den" >=

1851 "issue_row"."population" * "policy_row"."initiative_quorum_num"

1852 THEN

1853 UPDATE "initiative" SET "admitted" = TRUE

1854 WHERE "id" = "initiative_row"."id";

1855 ELSE

1856 UPDATE "initiative" SET "admitted" = FALSE

1857 WHERE "id" = "initiative_row"."id";

1858 END IF;

1859 END LOOP;

1860 RETURN;

1861 END;

1862 $$;

1863

1864 COMMENT ON FUNCTION "freeze_after_snapshot"

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

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

1867

1868

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

1870 RETURNS VOID

1871 LANGUAGE 'plpgsql' VOLATILE AS $$

1872 DECLARE

1873 "issue_row" "issue"%ROWTYPE;

1874 BEGIN

1875 PERFORM "create_snapshot"("issue_id_p");

1876 PERFORM "freeze_after_snapshot"("issue_id_p");

1877 RETURN;

1878 END;

1879 $$;

1880

1881 COMMENT ON FUNCTION "freeze_after_snapshot"

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

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

1884

1885

1886

1887 -----------------------

1888 -- Counting of votes --

1889 -----------------------

1890

1891

1892 CREATE FUNCTION "weight_of_added_vote_delegations"

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

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

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

1896 RETURNS "direct_voter"."weight"%TYPE

1897 LANGUAGE 'plpgsql' VOLATILE AS $$

1898 DECLARE

1899 "issue_delegation_row" "issue_delegation"%ROWTYPE;

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

1901 "weight_v" INT4;

1902 BEGIN

1903 "weight_v" := 0;

1904 FOR "issue_delegation_row" IN

1905 SELECT * FROM "issue_delegation"

1906 WHERE "trustee_id" = "member_id_p"

1907 AND "issue_id" = "issue_id_p"

1908 LOOP

1909 IF NOT EXISTS (

1910 SELECT NULL FROM "direct_voter"

1911 WHERE "member_id" = "issue_delegation_row"."truster_id"

1912 AND "issue_id" = "issue_id_p"

1913 ) AND NOT EXISTS (

1914 SELECT NULL FROM "delegating_voter"

1915 WHERE "member_id" = "issue_delegation_row"."truster_id"

1916 AND "issue_id" = "issue_id_p"

1917 ) THEN

1918 "delegate_member_ids_v" :=

1919 "member_id_p" || "delegate_member_ids_p";

1920 INSERT INTO "delegating_voter"

1921 ("member_id", "issue_id", "delegate_member_ids")

1922 VALUES (

1923 "issue_delegation_row"."truster_id",

1924 "issue_id_p",

1925 "delegate_member_ids_v"

1926 );

1927 "weight_v" := "weight_v" +

1928 1 + "weight_of_added_vote_delegations"(

1929 "issue_id_p",

1930 "issue_delegation_row"."truster_id",

1931 "delegate_member_ids_v"

1932 );

1933 END IF;

1934 END LOOP;

1935 RETURN "weight_v";

1936 END;

1937 $$;

1938

1939 COMMENT ON FUNCTION "weight_of_added_vote_delegations"

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

1941 "member"."id"%TYPE,

1942 "delegating_voter"."delegate_member_ids"%TYPE )

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

1944

1945

1946 CREATE FUNCTION "add_vote_delegations"

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

1948 RETURNS VOID

1949 LANGUAGE 'plpgsql' VOLATILE AS $$

1950 DECLARE

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

1952 BEGIN

1953 FOR "member_id_v" IN

1954 SELECT "member_id" FROM "direct_voter"

1955 WHERE "issue_id" = "issue_id_p"

1956 LOOP

1957 UPDATE "direct_voter" SET

1958 "weight" = "weight" + "weight_of_added_vote_delegations"(

1959 "issue_id_p",

1960 "member_id_v",

1961 '{}'

1962 )

1963 WHERE "member_id" = "member_id_v"

1964 AND "issue_id" = "issue_id_p";

1965 END LOOP;

1966 RETURN;

1967 END;

1968 $$;

1969

1970 COMMENT ON FUNCTION "add_vote_delegations"

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

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

1973

1974

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

1976 RETURNS VOID

1977 LANGUAGE 'plpgsql' VOLATILE AS $$

1978 DECLARE

1979 "issue_row" "issue"%ROWTYPE;

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

1981 BEGIN

1982 PERFORM "global_lock"();

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

1984 DELETE FROM "delegating_voter"

1985 WHERE "issue_id" = "issue_id_p";

1986 DELETE FROM "direct_voter"

1987 WHERE "issue_id" = "issue_id_p"

1988 AND "autoreject" = TRUE;

1989 DELETE FROM "direct_voter" USING "member"

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

1991 AND "direct_voter"."issue_id" = "issue_id_p"

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

1993 UPDATE "direct_voter" SET "weight" = 1

1994 WHERE "issue_id" = "issue_id_p";

1995 PERFORM "add_vote_delegations"("issue_id_p");

1996 FOR "member_id_v" IN

1997 SELECT "interest"."member_id"

1998 FROM "interest"

1999 LEFT JOIN "direct_voter"

2000 ON "interest"."member_id" = "direct_voter"."member_id"

2001 AND "interest"."issue_id" = "direct_voter"."issue_id"

2002 LEFT JOIN "delegating_voter"

2003 ON "interest"."member_id" = "delegating_voter"."member_id"

2004 AND "interest"."issue_id" = "delegating_voter"."issue_id"

2005 WHERE "interest"."issue_id" = "issue_id_p"

2006 AND "interest"."autoreject" = TRUE

2007 AND "direct_voter"."member_id" ISNULL

2008 AND "delegating_voter"."member_id" ISNULL

2009 UNION SELECT "membership"."member_id"

2010 FROM "membership"

2011 LEFT JOIN "interest"

2012 ON "membership"."member_id" = "interest"."member_id"

2013 AND "interest"."issue_id" = "issue_id_p"

2014 LEFT JOIN "direct_voter"

2015 ON "membership"."member_id" = "direct_voter"."member_id"

2016 AND "direct_voter"."issue_id" = "issue_id_p"

2017 LEFT JOIN "delegating_voter"

2018 ON "membership"."member_id" = "delegating_voter"."member_id"

2019 AND "delegating_voter"."issue_id" = "issue_id_p"

2020 WHERE "membership"."area_id" = "issue_row"."area_id"

2021 AND "membership"."autoreject" = TRUE

2022 AND "interest"."autoreject" ISNULL

2023 AND "direct_voter"."member_id" ISNULL

2024 AND "delegating_voter"."member_id" ISNULL

2025 LOOP

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

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

2028 INSERT INTO "vote" (

2029 "member_id",

2030 "issue_id",

2031 "initiative_id",

2032 "grade"

2033 ) SELECT

2034 "member_id_v" AS "member_id",

2035 "issue_id_p" AS "issue_id",

2036 "id" AS "initiative_id",

2037 -1 AS "grade"

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

2039 END LOOP;

2040 PERFORM "add_vote_delegations"("issue_id_p");

2041 UPDATE "issue" SET

2042 "voter_count" = (

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

2044 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"

2045 )

2046 WHERE "id" = "issue_id_p";

2047 UPDATE "initiative" SET

2048 "positive_votes" = "subquery"."positive_votes",

2049 "negative_votes" = "subquery"."negative_votes"

2050 FROM (

2051 SELECT

2052 "initiative_id",

2053 coalesce(

2054 sum(

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

2056 ),

2057 0

2058 ) AS "positive_votes",

2059 coalesce(

2060 sum(

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

2062 ),

2063 0

2064 ) AS "negative_votes"

2065 FROM "vote" JOIN "direct_voter"

2066 ON "vote"."member_id" = "direct_voter"."member_id"

2067 AND "vote"."issue_id" = "direct_voter"."issue_id"

2068 WHERE "vote"."issue_id" = "issue_id_p"

2069 GROUP BY "initiative_id"

2070 ) AS "subquery"

2071 WHERE "initiative"."admitted"

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

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

2074 END;

2075 $$;

2076

2077 COMMENT ON FUNCTION "close_voting"

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

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

2080

2081

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

2083 RETURNS INT4[]

2084 LANGUAGE 'plpgsql' IMMUTABLE AS $$

2085 DECLARE

2086 "i" INTEGER;

2087 "ary_text_v" TEXT;

2088 BEGIN

2089 IF "dim_p" >= 1 THEN

2090 "ary_text_v" := '{NULL';

2091 "i" := "dim_p";

2092 LOOP

2093 "i" := "i" - 1;

2094 EXIT WHEN "i" = 0;

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

2096 END LOOP;

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

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

2099 ELSE

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

2101 END IF;

2102 END;

2103 $$;

2104

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

2106

2107

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

2109 RETURNS INT4[][]

2110 LANGUAGE 'plpgsql' IMMUTABLE AS $$

2111 DECLARE

2112 "i" INTEGER;

2113 "row_text_v" TEXT;

2114 "ary_text_v" TEXT;

2115 BEGIN

2116 IF "dim_p" >= 1 THEN

2117 "row_text_v" := '{NULL';

2118 "i" := "dim_p";

2119 LOOP

2120 "i" := "i" - 1;

2121 EXIT WHEN "i" = 0;

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

2123 END LOOP;

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

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

2126 "i" := "dim_p";

2127 LOOP

2128 "i" := "i" - 1;

2129 EXIT WHEN "i" = 0;

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

2131 END LOOP;

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

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

2134 ELSE

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

2136 END IF;

2137 END;

2138 $$;

2139

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

2141

2142

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

2144 RETURNS VOID

2145 LANGUAGE 'plpgsql' VOLATILE AS $$

2146 DECLARE

2147 "dimension_v" INTEGER;

2148 "matrix" INT4[][];

2149 "i" INTEGER;

2150 "j" INTEGER;

2151 "k" INTEGER;

2152 "battle_row" "battle"%ROWTYPE;

2153 "rank_ary" INT4[];

2154 "rank_v" INT4;

2155 "done_v" INTEGER;

2156 "winners_ary" INTEGER[];

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

2158 BEGIN

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

2160 -- Prepare matrix for Schulze-Method:

2161 SELECT count(1) INTO "dimension_v"

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

2163 IF "dimension_v" = 1 THEN

2164 UPDATE "initiative" SET

2165 "rank" = 1

2166 FROM "battle_participant"

2167 WHERE "initiative"."issue_id" = "issue_id_p"

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

2169 ELSIF "dimension_v" > 1 THEN

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

2171 "i" := 1;

2172 "j" := 2;

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

2174 FOR "battle_row" IN

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

2176 ORDER BY "winning_initiative_id", "losing_initiative_id"

2177 LOOP

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

2179 IF "j" = "dimension_v" THEN

2180 "i" := "i" + 1;

2181 "j" := 1;

2182 ELSE

2183 "j" := "j" + 1;

2184 IF "j" = "i" THEN

2185 "j" := "j" + 1;

2186 END IF;

2187 END IF;

2188 END LOOP;

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

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

2191 END IF;

2192 -- Delete losers from matrix:

2193 "i" := 1;

2194 LOOP

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

2196 LOOP

2197 IF "i" != "j" THEN

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

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

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

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

2202 ELSE

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

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

2205 END IF;

2206 END IF;

2207 EXIT WHEN "j" = "dimension_v";

2208 "j" := "j" + 1;

2209 END LOOP;

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

2211 "i" := "i" + 1;

2212 END LOOP;

2213 -- Find best paths:

2214 "i" := 1;

2215 LOOP

2216 "j" := 1;

2217 LOOP

2218 IF "i" != "j" THEN

2219 "k" := 1;

2220 LOOP

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

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

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

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

2225 END IF;

2226 ELSE

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

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

2229 END IF;

2230 END IF;

2231 END IF;

2232 EXIT WHEN "k" = "dimension_v";

2233 "k" := "k" + 1;

2234 END LOOP;

2235 END IF;

2236 EXIT WHEN "j" = "dimension_v";

2237 "j" := "j" + 1;

2238 END LOOP;

2239 EXIT WHEN "i" = "dimension_v";

2240 "i" := "i" + 1;

2241 END LOOP;

2242 -- Determine order of winners:

2243 "rank_ary" := "init_array"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4)

2244 "rank_v" := 1;

2245 "done_v" := 0;

2246 LOOP

2247 "winners_ary" := '{}';

2248 "i" := 1;

2249 LOOP

2250 IF "rank_ary"["i"] ISNULL THEN

2251 "j" := 1;

2252 LOOP

2253 IF

2254 "i" != "j" AND

2255 "rank_ary"["j"] ISNULL AND

2256 "matrix"["j"]["i"] > "matrix"["i"]["j"]

2257 THEN

2258 -- someone else is better

2259 EXIT;

2260 END IF;

2261 IF "j" = "dimension_v" THEN

2262 -- noone is better

2263 "winners_ary" := "winners_ary" || "i";

2264 EXIT;

2265 END IF;

2266 "j" := "j" + 1;

2267 END LOOP;

2268 END IF;

2269 EXIT WHEN "i" = "dimension_v";

2270 "i" := "i" + 1;

2271 END LOOP;

2272 "i" := 1;

2273 LOOP

2274 "rank_ary"["winners_ary"["i"]] := "rank_v";

2275 "done_v" := "done_v" + 1;

2276 EXIT WHEN "i" = array_upper("winners_ary", 1);

2277 "i" := "i" + 1;

2278 END LOOP;

2279 EXIT WHEN "done_v" = "dimension_v";

2280 "rank_v" := "rank_v" + 1;

2281 END LOOP;

2282 -- write preliminary ranks:

2283 "i" := 1;

2284 FOR "initiative_id_v" IN

2285 SELECT "initiative"."id"

2286 FROM "initiative" JOIN "battle_participant"

2287 ON "initiative"."id" = "battle_participant"."initiative_id"

2288 WHERE "initiative"."issue_id" = "issue_id_p"

2289 ORDER BY "initiative"."id"

2290 LOOP

2291 UPDATE "initiative" SET "rank" = "rank_ary"["i"]

2292 WHERE "id" = "initiative_id_v";

2293 "i" := "i" + 1;

2294 END LOOP;

2295 IF "i" != "dimension_v" + 1 THEN

2296 RAISE EXCEPTION 'Wrong winner count (should not happen)';

2297 END IF;

2298 -- straighten ranks (start counting with 1, no equal ranks):

2299 "rank_v" := 1;

2300 FOR "initiative_id_v" IN

2301 SELECT "id" FROM "initiative"

2302 WHERE "issue_id" = "issue_id_p" AND "rank" NOTNULL

2303 ORDER BY

2304 "rank",

2305 "vote_ratio"("positive_votes", "negative_votes") DESC,

2306 "id"

2307 LOOP

2308 UPDATE "initiative" SET "rank" = "rank_v"

2309 WHERE "id" = "initiative_id_v";

2310 "rank_v" := "rank_v" + 1;

2311 END LOOP;

2312 END IF;

2313 -- mark issue as finished

2314 UPDATE "issue" SET "ranks_available" = TRUE

2315 WHERE "id" = "issue_id_p";

2316 RETURN;

2317 END;

2318 $$;

2319

2320 COMMENT ON FUNCTION "calculate_ranks"

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

2322 IS 'Determine ranking (Votes have to be counted first)';

2323

2324

2325

2326 -----------------------------

2327 -- Automatic state changes --

2328 -----------------------------

2329

2330

2331 CREATE FUNCTION "check_issue"

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

2333 RETURNS VOID

2334 LANGUAGE 'plpgsql' VOLATILE AS $$

2335 DECLARE

2336 "issue_row" "issue"%ROWTYPE;

2337 "policy_row" "policy"%ROWTYPE;

2338 "voting_requested_v" BOOLEAN;

2339 BEGIN

2340 PERFORM "global_lock"();

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

2342 IF "issue_row"."closed" ISNULL THEN

2343 SELECT * INTO "policy_row" FROM "policy"

2344 WHERE "id" = "issue_row"."policy_id";

2345 IF "issue_row"."fully_frozen" ISNULL THEN

2346 PERFORM "create_snapshot"("issue_id_p");

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

2348 END IF;

2349 IF "issue_row"."accepted" ISNULL THEN

2350 IF EXISTS (

2351 SELECT NULL FROM "initiative"

2352 WHERE "issue_id" = "issue_id_p"

2353 AND "supporter_count" > 0

2354 AND "supporter_count" * "policy_row"."issue_quorum_den"

2355 >= "issue_row"."population" * "policy_row"."issue_quorum_num"

2356 ) THEN

2357 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');

2358 "issue_row"."accepted" = now(); -- NOTE: "issue_row" used later

2359 UPDATE "issue" SET "accepted" = "issue_row"."accepted"

2360 WHERE "id" = "issue_row"."id";

2361 ELSIF

2362 now() >= "issue_row"."created" + "policy_row"."admission_time"

2363 THEN

2364 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');

2365 UPDATE "issue" SET "closed" = now()

2366 WHERE "id" = "issue_row"."id";

2367 END IF;

2368 END IF;

2369 IF

2370 "issue_row"."accepted" NOTNULL AND

2371 "issue_row"."half_frozen" ISNULL

2372 THEN

2373 SELECT

2374 CASE

2375 WHEN "vote_now" * 2 > "issue_row"."population" THEN

2376 TRUE

2377 WHEN "vote_later" * 2 > "issue_row"."population" THEN

2378 FALSE

2379 ELSE NULL

2380 END

2381 INTO "voting_requested_v"

2382 FROM "issue" WHERE "id" = "issue_id_p";

2383 IF

2384 "voting_requested_v" OR (

2385 "voting_requested_v" ISNULL AND

2386 now() >= "issue_row"."accepted" + "policy_row"."discussion_time"

2387 )

2388 THEN

2389 "issue_row"."half_frozen" = now(); -- NOTE: "issue_row" used later

2390 UPDATE "issue" SET "half_frozen" = "issue_row"."half_frozen"

2391 WHERE "id" = "issue_row"."id";

2392 END IF;

2393 END IF;

2394 IF

2395 "issue_row"."half_frozen" NOTNULL AND

2396 "issue_row"."fully_frozen" ISNULL AND

2397 now() >= "issue_row"."half_frozen" + "policy_row"."verification_time"

2398 THEN

2399 "issue_row"."fully_frozen" = now(); -- NOTE: "issue_row" used later

2400 PERFORM "freeze_after_snapshot"("issue_id_p");

2401 END IF;

2402 IF

2403 "issue_row"."fully_frozen" NOTNULL AND

2404 now() >= "issue_row"."fully_frozen" + "policy_row"."voting_time"

2405 THEN

2406 PERFORM "close_voting"("issue_id_p");

2407 END IF;

2408 END IF;

2409 RETURN;

2410 END;

2411 $$;

2412

2413 COMMENT ON FUNCTION "check_issue"

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

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

2416

2417

2418 CREATE FUNCTION "check_everything"()

2419 RETURNS VOID

2420 LANGUAGE 'plpgsql' VOLATILE AS $$

2421 DECLARE

2422 "issue_id_v" "issue"."id"%TYPE;

2423 BEGIN

2424 DELETE FROM "expired_session";

2425 PERFORM "calculate_member_counts"();

2426 FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP

2427 PERFORM "check_issue"("issue_id_v");

2428 END LOOP;

2429 FOR "issue_id_v" IN SELECT "id" FROM "issue_with_ranks_missing" LOOP

2430 PERFORM "calculate_ranks"("issue_id_v");

2431 END LOOP;

2432 RETURN;

2433 END;

2434 $$;

2435

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

2437

2438

2439

2440 COMMIT;