liquid_feedback_core: 1cbdd3975a61 core.sql

liquid_feedback_core

view core.sql @ 5:1cbdd3975a61

Version beta6

Added function for printing delegation chains

Replaced column member_count of table area by 3 new columns:
- direct_member_count
- member_weight (regarding delegations)
- autoreject_weight

Added calculated field to member_count table, storing the time of computation of the total member count and area member counts

Added view liquid_feedback_version

Code cleanup

author	jbe
date	Tue Nov 17 12:00:00 2009 +0100 (2009-11-17)
parents	6133c0a62378
children	3ea7a72ed7e7

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 ('beta6', 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 CREATE INDEX "member_active_idx" ON "member" ("active");

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

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

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

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

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

37 CREATE TABLE "member_count" (

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

39 "total_count" INT4 NOT NULL );

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

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

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

47 CREATE TABLE "contact" (

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

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

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

51 "public" BOOLEAN NOT NULL DEFAULT FALSE );

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

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

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

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

60 CREATE TABLE "session" (

61 "ident" TEXT PRIMARY KEY,

62 "additional_secret" TEXT,

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

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

65 "lang" TEXT );

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

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

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

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

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

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

76 CREATE TABLE "policy" (

77 "id" SERIAL4 PRIMARY KEY,

78 "active" BOOLEAN NOT NULL DEFAULT TRUE,

79 "name" TEXT NOT NULL UNIQUE,

80 "description" TEXT NOT NULL DEFAULT '',

81 "admission_time" INTERVAL NOT NULL,

82 "discussion_time" INTERVAL NOT NULL,

83 "verification_time" INTERVAL NOT NULL,

84 "voting_time" INTERVAL NOT NULL,

85 "issue_quorum_num" INT4 NOT NULL,

86 "issue_quorum_den" INT4 NOT NULL,

87 "initiative_quorum_num" INT4 NOT NULL,

88 "initiative_quorum_den" INT4 NOT NULL );

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

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

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

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

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

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

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

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

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

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

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

102

103

104 CREATE TABLE "area" (

105 "id" SERIAL4 PRIMARY KEY,

106 "active" BOOLEAN NOT NULL DEFAULT TRUE,

107 "name" TEXT NOT NULL,

108 "description" TEXT NOT NULL DEFAULT '',

109 "direct_member_count" INT4,

110 "member_weight" INT4,

111 "autoreject_weight" INT4 );

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

113

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

115

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

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

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

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

120

121

122 CREATE TABLE "issue" (

123 "id" SERIAL4 PRIMARY KEY,

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

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

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

127 "accepted" TIMESTAMPTZ,

128 "half_frozen" TIMESTAMPTZ,

129 "fully_frozen" TIMESTAMPTZ,

130 "closed" TIMESTAMPTZ,

131 "ranks_available" BOOLEAN NOT NULL DEFAULT FALSE,

132 "snapshot" TIMESTAMPTZ,

133 "population" INT4,

134 "vote_now" INT4,

135 "vote_later" INT4,

136 "voter_count" INT4,

137 CONSTRAINT "valid_state" CHECK (

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

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

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

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

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

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

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

145 CONSTRAINT "state_change_order" CHECK (

146 "created" <= "accepted" AND

147 "accepted" <= "half_frozen" AND

148 "half_frozen" <= "fully_frozen" AND

149 "fully_frozen" <= "closed" ),

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

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

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

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

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

155

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

157

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

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

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

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

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

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

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

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

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

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

168

169

170 CREATE TABLE "initiative" (

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

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

173 "id" SERIAL4 PRIMARY KEY,

174 "name" TEXT NOT NULL,

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

176 "revoked" TIMESTAMPTZ,

177 "admitted" BOOLEAN,

178 "supporter_count" INT4,

179 "informed_supporter_count" INT4,

180 "satisfied_supporter_count" INT4,

181 "satisfied_informed_supporter_count" INT4,

182 "positive_votes" INT4,

183 "negative_votes" INT4,

184 "rank" INT4,

185 CONSTRAINT "revoked_initiatives_cant_be_admitted"

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

187 CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results"

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

189

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

191

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

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

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

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

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

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

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

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

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

201

202

203 CREATE TABLE "draft" (

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

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

206 "id" SERIAL8 PRIMARY KEY,

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

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

209 "content" TEXT NOT NULL );

210

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

212

213

214 CREATE TABLE "suggestion" (

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

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

217 "id" SERIAL8 PRIMARY KEY,

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

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

220 "name" TEXT NOT NULL,

221 "description" TEXT NOT NULL DEFAULT '',

222 "minus2_unfulfilled_count" INT4,

223 "minus2_fulfilled_count" INT4,

224 "minus1_unfulfilled_count" INT4,

225 "minus1_fulfilled_count" INT4,

226 "plus1_unfulfilled_count" INT4,

227 "plus1_fulfilled_count" INT4,

228 "plus2_unfulfilled_count" INT4,

229 "plus2_fulfilled_count" INT4 );

230

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

232

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

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

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

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

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

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

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

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

241

242

243 CREATE TABLE "membership" (

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

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

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

247 "autoreject" BOOLEAN NOT NULL DEFAULT FALSE );

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

249

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

251

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

253

254

255 CREATE TABLE "interest" (

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

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

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

259 "autoreject" BOOLEAN NOT NULL,

260 "voting_requested" BOOLEAN );

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

262

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

264

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

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

267

268

269 CREATE TABLE "initiator" (

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

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

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

273 "accepted" BOOLEAN NOT NULL DEFAULT TRUE );

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

275

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

277

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

279

280

281 CREATE TABLE "supporter" (

282 "issue_id" INT4 NOT NULL,

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

284 "initiative_id" INT4,

285 "member_id" INT4,

286 "draft_id" INT8 NOT NULL,

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

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

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

290

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

292

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

294

295

296 CREATE TABLE "opinion" (

297 "initiative_id" INT4 NOT NULL,

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

299 "suggestion_id" INT8,

300 "member_id" INT4,

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

302 "fulfilled" BOOLEAN NOT NULL DEFAULT FALSE,

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

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

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

306

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

308

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

310

311

312 CREATE TABLE "delegation" (

313 "id" SERIAL8 PRIMARY KEY,

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

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

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

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

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

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

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

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

322 CREATE UNIQUE INDEX "delegation_default_truster_id_trustee_id_unique_idx"

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

324 WHERE "area_id" ISNULL AND "issue_id" ISNULL;

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

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

327

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

329

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

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

332

333

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

335

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

337

338

339 CREATE TABLE "direct_population_snapshot" (

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

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

342 "event" "snapshot_event",

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

344 "weight" INT4,

345 "interest_exists" BOOLEAN NOT NULL );

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

347

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

349

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

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

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

353

354

355 CREATE TABLE "delegating_population_snapshot" (

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

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

358 "event" "snapshot_event",

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

360 "delegate_member_ids" INT4[] NOT NULL );

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

362

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

364

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

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

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

368

369

370 CREATE TABLE "direct_interest_snapshot" (

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

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

373 "event" "snapshot_event",

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

375 "weight" INT4,

376 "voting_requested" BOOLEAN );

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

378

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

380

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

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

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

384

385

386 CREATE TABLE "delegating_interest_snapshot" (

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

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

389 "event" "snapshot_event",

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

391 "delegate_member_ids" INT4[] NOT NULL );

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

393

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

395

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

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

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

399

400

401 CREATE TABLE "direct_supporter_snapshot" (

402 "issue_id" INT4 NOT NULL,

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

404 "initiative_id" INT4,

405 "event" "snapshot_event",

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

407 "informed" BOOLEAN NOT NULL,

408 "satisfied" BOOLEAN NOT NULL,

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

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

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

412

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

414

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

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

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

418

419

420 CREATE TABLE "direct_voter" (

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

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

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

424 "weight" INT4,

425 "autoreject" BOOLEAN NOT NULL DEFAULT FALSE );

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

427

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

429

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

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

432

433

434 CREATE TABLE "delegating_voter" (

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

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

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

438 "delegate_member_ids" INT4[] NOT NULL );

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

440

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

442

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

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

445

446

447 CREATE TABLE "vote" (

448 "issue_id" INT4 NOT NULL,

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

450 "initiative_id" INT4,

451 "member_id" INT4,

452 "grade" INT4,

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

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

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

456

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

458

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

460

461

462

463 ----------------------------

464 -- Additional constraints --

465 ----------------------------

466

467

468 CREATE FUNCTION "issue_requires_first_initiative_trigger"()

469 RETURNS TRIGGER

470 LANGUAGE 'plpgsql' VOLATILE AS $$

471 BEGIN

472 IF NOT EXISTS (

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

474 ) THEN

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

476 -- ERRCODE = 'integrity_constraint_violation',

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

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

479 END IF;

480 RETURN NULL;

481 END;

482 $$;

483

484 CREATE CONSTRAINT TRIGGER "issue_requires_first_initiative"

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

486 FOR EACH ROW EXECUTE PROCEDURE

487 "issue_requires_first_initiative_trigger"();

488

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

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

491

492

493 CREATE FUNCTION "last_initiative_deletes_issue_trigger"()

494 RETURNS TRIGGER

495 LANGUAGE 'plpgsql' VOLATILE AS $$

496 DECLARE

497 "reference_lost" BOOLEAN;

498 BEGIN

499 IF TG_OP = 'DELETE' THEN

500 "reference_lost" := TRUE;

501 ELSE

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

503 END IF;

504 IF

505 "reference_lost" AND NOT EXISTS (

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

507 )

508 THEN

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

510 END IF;

511 RETURN NULL;

512 END;

513 $$;

514

515 CREATE CONSTRAINT TRIGGER "last_initiative_deletes_issue"

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

517 FOR EACH ROW EXECUTE PROCEDURE

518 "last_initiative_deletes_issue_trigger"();

519

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

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

522

523

524 CREATE FUNCTION "initiative_requires_first_draft_trigger"()

525 RETURNS TRIGGER

526 LANGUAGE 'plpgsql' VOLATILE AS $$

527 BEGIN

528 IF NOT EXISTS (

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

530 ) THEN

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

532 -- ERRCODE = 'integrity_constraint_violation',

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

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

535 END IF;

536 RETURN NULL;

537 END;

538 $$;

539

540 CREATE CONSTRAINT TRIGGER "initiative_requires_first_draft"

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

542 FOR EACH ROW EXECUTE PROCEDURE

543 "initiative_requires_first_draft_trigger"();

544

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

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

547

548

549 CREATE FUNCTION "last_draft_deletes_initiative_trigger"()

550 RETURNS TRIGGER

551 LANGUAGE 'plpgsql' VOLATILE AS $$

552 DECLARE

553 "reference_lost" BOOLEAN;

554 BEGIN

555 IF TG_OP = 'DELETE' THEN

556 "reference_lost" := TRUE;

557 ELSE

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

559 END IF;

560 IF

561 "reference_lost" AND NOT EXISTS (

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

563 )

564 THEN

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

566 END IF;

567 RETURN NULL;

568 END;

569 $$;

570

571 CREATE CONSTRAINT TRIGGER "last_draft_deletes_initiative"

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

573 FOR EACH ROW EXECUTE PROCEDURE

574 "last_draft_deletes_initiative_trigger"();

575

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

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

578

579

580 CREATE FUNCTION "suggestion_requires_first_opinion_trigger"()

581 RETURNS TRIGGER

582 LANGUAGE 'plpgsql' VOLATILE AS $$

583 BEGIN

584 IF NOT EXISTS (

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

586 ) THEN

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

588 END IF;

589 RETURN NULL;

590 END;

591 $$;

592

593 CREATE CONSTRAINT TRIGGER "suggestion_requires_first_opinion"

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

595 FOR EACH ROW EXECUTE PROCEDURE

596 "suggestion_requires_first_opinion_trigger"();

597

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

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

600

601

602 CREATE FUNCTION "last_opinion_deletes_suggestion_trigger"()

603 RETURNS TRIGGER

604 LANGUAGE 'plpgsql' VOLATILE AS $$

605 DECLARE

606 "reference_lost" BOOLEAN;

607 BEGIN

608 IF TG_OP = 'DELETE' THEN

609 "reference_lost" := TRUE;

610 ELSE

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

612 END IF;

613 IF

614 "reference_lost" AND NOT EXISTS (

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

616 )

617 THEN

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

619 END IF;

620 RETURN NULL;

621 END;

622 $$;

623

624 CREATE CONSTRAINT TRIGGER "last_opinion_deletes_suggestion"

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

626 FOR EACH ROW EXECUTE PROCEDURE

627 "last_opinion_deletes_suggestion_trigger"();

628

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

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

631

632

633

634 --------------------------------------------------------------------

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

636 --------------------------------------------------------------------

637

638 CREATE FUNCTION "autofill_issue_id_trigger"()

639 RETURNS TRIGGER

640 LANGUAGE 'plpgsql' VOLATILE AS $$

641 BEGIN

642 IF NEW."issue_id" ISNULL THEN

643 SELECT "issue_id" INTO NEW."issue_id"

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

645 END IF;

646 RETURN NEW;

647 END;

648 $$;

649

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

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

652

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

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

655

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

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

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

659

660

661 CREATE FUNCTION "autofill_initiative_id_trigger"()

662 RETURNS TRIGGER

663 LANGUAGE 'plpgsql' VOLATILE AS $$

664 BEGIN

665 IF NEW."initiative_id" ISNULL THEN

666 SELECT "initiative_id" INTO NEW."initiative_id"

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

668 END IF;

669 RETURN NEW;

670 END;

671 $$;

672

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

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

675

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

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

678

679

680

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

682 -- Automatic calculation of certain default values --

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

684

685 CREATE FUNCTION "copy_autoreject_trigger"()

686 RETURNS TRIGGER

687 LANGUAGE 'plpgsql' VOLATILE AS $$

688 BEGIN

689 IF NEW."autoreject" ISNULL THEN

690 SELECT "membership"."autoreject" INTO NEW."autoreject"

691 FROM "issue" JOIN "membership"

692 ON "issue"."area_id" = "membership"."area_id"

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

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

695 END IF;

696 IF NEW."autoreject" ISNULL THEN

697 NEW."autoreject" := FALSE;

698 END IF;

699 RETURN NEW;

700 END;

701 $$;

702

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

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

705

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

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

708

709

710 CREATE FUNCTION "supporter_default_for_draft_id_trigger"()

711 RETURNS TRIGGER

712 LANGUAGE 'plpgsql' VOLATILE AS $$

713 BEGIN

714 IF NEW."draft_id" ISNULL THEN

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

716 WHERE "initiative_id" = NEW."initiative_id";

717 END IF;

718 RETURN NEW;

719 END;

720 $$;

721

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

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

724

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

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

727

728

729

730 ----------------------------------------

731 -- Automatic creation of dependencies --

732 ----------------------------------------

733

734 CREATE FUNCTION "autocreate_interest_trigger"()

735 RETURNS TRIGGER

736 LANGUAGE 'plpgsql' VOLATILE AS $$

737 BEGIN

738 IF NOT EXISTS (

739 SELECT NULL FROM "initiative" JOIN "interest"

740 ON "initiative"."issue_id" = "interest"."issue_id"

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

742 AND "interest"."member_id" = NEW."member_id"

743 ) THEN

744 BEGIN

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

746 SELECT "issue_id", NEW."member_id"

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

748 EXCEPTION WHEN unique_violation THEN END;

749 END IF;

750 RETURN NEW;

751 END;

752 $$;

753

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

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

756

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

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

759

760

761 CREATE FUNCTION "autocreate_supporter_trigger"()

762 RETURNS TRIGGER

763 LANGUAGE 'plpgsql' VOLATILE AS $$

764 BEGIN

765 IF NOT EXISTS (

766 SELECT NULL FROM "suggestion" JOIN "supporter"

767 ON "suggestion"."initiative_id" = "supporter"."initiative_id"

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

769 AND "supporter"."member_id" = NEW."member_id"

770 ) THEN

771 BEGIN

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

773 SELECT "initiative_id", NEW."member_id"

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

775 EXCEPTION WHEN unique_violation THEN END;

776 END IF;

777 RETURN NEW;

778 END;

779 $$;

780

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

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

783

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

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

786

787

788

789 ------------------------------------------

790 -- Views and helper functions for views --

791 ------------------------------------------

792

793

794 CREATE TYPE "delegation_scope" AS ENUM

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

796

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

798

799

800 CREATE VIEW "global_delegation" AS

801 SELECT

802 "delegation"."id",

803 "delegation"."truster_id",

804 "delegation"."trustee_id"

805 FROM "delegation" JOIN "member"

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

807 WHERE "delegation"."area_id" ISNULL

808 AND "delegation"."issue_id" ISNULL

809 AND "member"."active";

810

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

812

813

814 CREATE VIEW "area_delegation" AS

815 SELECT "subquery".* FROM (

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

817 "area"."id" AS "area_id",

818 "delegation"."id" AS "id",

819 "delegation"."truster_id" AS "truster_id",

820 "delegation"."trustee_id" AS "trustee_id",

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

822 'global'::"delegation_scope"

823 ELSE

824 'area'::"delegation_scope"

825 END AS "scope"

826 FROM "area" JOIN "delegation"

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

828 AND "delegation"."issue_id" ISNULL

829 ORDER BY

830 "area"."id",

831 "delegation"."truster_id",

832 "delegation"."area_id" NULLS LAST

833 ) AS "subquery"

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

835 WHERE "member"."active";

836

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

838

839

840 CREATE VIEW "issue_delegation" AS

841 SELECT "subquery".* FROM (

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

843 "issue"."id" AS "issue_id",

844 "delegation"."id" AS "id",

845 "delegation"."truster_id" AS "truster_id",

846 "delegation"."trustee_id" AS "trustee_id",

847 CASE

848 WHEN

849 "delegation"."area_id" ISNULL AND

850 "delegation"."issue_id" ISNULL

851 THEN 'global'::"delegation_scope"

852 WHEN

853 "delegation"."area_id" NOTNULL

854 THEN 'area'::"delegation_scope"

855 ELSE 'issue'::"delegation_scope"

856 END AS "scope"

857 FROM "issue" JOIN "delegation"

858 ON (

859 "delegation"."area_id" ISNULL OR

860 "delegation"."area_id" = "issue"."area_id"

861 ) AND (

862 "delegation"."issue_id" ISNULL OR

863 "delegation"."issue_id" = "issue"."id"

864 )

865 ORDER BY

866 "issue"."id",

867 "delegation"."truster_id",

868 "delegation"."issue_id" NULLS LAST,

869 "delegation"."area_id" NULLS LAST

870 ) AS "subquery"

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

872 WHERE "member"."active";

873

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

875

876

877 CREATE FUNCTION "membership_weight_with_skipping"

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

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

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

881 RETURNS INT4

882 LANGUAGE 'plpgsql' STABLE AS $$

883 DECLARE

884 "sum_v" INT4;

885 "delegation_row" "area_delegation"%ROWTYPE;

886 BEGIN

887 "sum_v" := 1;

888 FOR "delegation_row" IN

889 SELECT "area_delegation".*

890 FROM "area_delegation" LEFT JOIN "membership"

891 ON "membership"."area_id" = "area_id_p"

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

893 WHERE "area_delegation"."area_id" = "area_id_p"

894 AND "area_delegation"."trustee_id" = "member_id_p"

895 AND "membership"."member_id" ISNULL

896 LOOP

897 IF NOT

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

899 THEN

900 "sum_v" := "sum_v" + "membership_weight_with_skipping"(

901 "area_id_p",

902 "delegation_row"."truster_id",

903 "skip_member_ids_p" || "delegation_row"."truster_id"

904 );

905 END IF;

906 END LOOP;

907 RETURN "sum_v";

908 END;

909 $$;

910

911 CREATE FUNCTION "membership_weight"

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

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

914 RETURNS INT4

915 LANGUAGE 'plpgsql' STABLE AS $$

916 BEGIN

917 RETURN "membership_weight_with_skipping"(

918 "area_id_p",

919 "member_id_p",

920 ARRAY["member_id_p"]

921 );

922 END;

923 $$;

924

925

926 CREATE VIEW "member_count_view" AS

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

928

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

930

931

932 CREATE VIEW "area_member_count" AS

933 SELECT

934 "area"."id" AS "area_id",

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

936 coalesce(

937 sum(

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

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

940 ELSE 0 END

941 )

942 ) AS "member_weight",

943 coalesce(

944 sum(

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

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

947 ELSE 0 END

948 )

949 ) AS "autoreject_weight"

950 FROM "area"

951 LEFT JOIN "membership"

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

953 LEFT JOIN "member"

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

955 AND "member"."active"

956 GROUP BY "area"."id";

957 -- TODO: count delegations

958

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

960

961

962 CREATE VIEW "current_draft" AS

963 SELECT "draft".* FROM (

964 SELECT

965 "initiative"."id" AS "initiative_id",

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

967 FROM "initiative" JOIN "draft"

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

969 GROUP BY "initiative"."id"

970 ) AS "subquery"

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

972

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

974

975

976 CREATE VIEW "critical_opinion" AS

977 SELECT * FROM "opinion"

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

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

980

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

982

983

984 CREATE VIEW "battle_participant" AS

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

986 WHERE "admitted"

987 AND "positive_votes" > "negative_votes";

988

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

990

991

992 CREATE VIEW "battle" AS

993 SELECT

994 "issue"."id" AS "issue_id",

995 "winning_initiative"."initiative_id" AS "winning_initiative_id",

996 "losing_initiative"."initiative_id" AS "losing_initiative_id",

997 sum(

998 CASE WHEN

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

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

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

1002 ) AS "count"

1003 FROM "issue"

1004 LEFT JOIN "direct_voter"

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

1006 JOIN "battle_participant" AS "winning_initiative"

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

1008 JOIN "battle_participant" AS "losing_initiative"

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

1010 LEFT JOIN "vote" AS "better_vote"

1011 ON "direct_voter"."member_id" = "better_vote"."member_id"

1012 AND "winning_initiative"."initiative_id" = "better_vote"."initiative_id"

1013 LEFT JOIN "vote" AS "worse_vote"

1014 ON "direct_voter"."member_id" = "worse_vote"."member_id"

1015 AND "losing_initiative"."initiative_id" = "worse_vote"."initiative_id"

1016 WHERE

1017 "winning_initiative"."initiative_id" !=

1018 "losing_initiative"."initiative_id"

1019 GROUP BY

1020 "issue"."id",

1021 "winning_initiative"."initiative_id",

1022 "losing_initiative"."initiative_id";

1023

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

1025

1026

1027 CREATE VIEW "expired_session" AS

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

1029

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

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

1032

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

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

1035

1036

1037 CREATE VIEW "open_issue" AS

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

1039

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

1041

1042

1043 CREATE VIEW "issue_with_ranks_missing" AS

1044 SELECT * FROM "issue"

1045 WHERE "fully_frozen" NOTNULL

1046 AND "closed" NOTNULL

1047 AND "ranks_available" = FALSE;

1048

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

1050

1051

1052

1053 --------------------------------------------------

1054 -- Set returning function for delegation chains --

1055 --------------------------------------------------

1056

1057

1058 CREATE TYPE "delegation_chain_loop_tag" AS ENUM

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

1060

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

1062

1063

1064 CREATE TYPE "delegation_chain_row" AS (

1065 "index" INT4,

1066 "member_id" INT4,

1067 "member_active" BOOLEAN,

1068 "participation" BOOLEAN,

1069 "overridden" BOOLEAN,

1070 "scope_in" "delegation_scope",

1071 "scope_out" "delegation_scope",

1072 "loop" "delegation_chain_loop_tag" );

1073

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

1075

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

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

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

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

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

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

1082

1083

1084 CREATE FUNCTION "delegation_chain"

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

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

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

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

1089 RETURNS SETOF "delegation_chain_row"

1090 LANGUAGE 'plpgsql' STABLE AS $$

1091 DECLARE

1092 "issue_row" "issue"%ROWTYPE;

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

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

1095 "output_row" "delegation_chain_row";

1096 "output_rows" "delegation_chain_row"[];

1097 "delegation_row" "delegation"%ROWTYPE;

1098 "row_count" INT4;

1099 "i" INT4;

1100 "loop_v" BOOLEAN;

1101 BEGIN

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

1103 "visited_member_ids" := '{}';

1104 "loop_member_id_v" := NULL;

1105 "output_rows" := '{}';

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

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

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

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

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

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

1112 LOOP

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

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

1115 ELSE

1116 "visited_member_ids" :=

1117 "visited_member_ids" || "output_row"."member_id";

1118 END IF;

1119 IF "output_row"."participation" THEN

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

1121 END IF;

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

1123 IF EXISTS (

1124 SELECT NULL FROM "member"

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

1126 ) THEN

1127 IF "area_id_p" ISNULL AND "issue_id_p" ISNULL THEN

1128 SELECT * INTO "delegation_row" FROM "delegation"

1129 WHERE "truster_id" = "output_row"."member_id"

1130 AND "area_id" ISNULL AND "issue_id" ISNULL;

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

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

1133 SELECT NULL FROM "membership"

1134 WHERE "area_id" = "area_id_p"

1135 AND "member_id" = "output_row"."member_id"

1136 );

1137 SELECT * INTO "delegation_row" FROM "delegation"

1138 WHERE "truster_id" = "output_row"."member_id"

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

1140 AND "issue_id" ISNULL

1141 ORDER BY "area_id" NULLS LAST;

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

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

1144 SELECT NULL FROM "interest"

1145 WHERE "issue_id" = "issue_id_p"

1146 AND "member_id" = "output_row"."member_id"

1147 );

1148 SELECT * INTO "delegation_row" FROM "delegation"

1149 WHERE "truster_id" = "output_row"."member_id"

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

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

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

1153 ELSE

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

1155 END IF;

1156 ELSE

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

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

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

1160 "delegation_row" := ROW(NULL);

1161 END IF;

1162 IF

1163 "output_row"."member_id" = "member_id_p" AND

1164 "simulate_trustee_id_p" NOTNULL

1165 THEN

1166 "output_row"."scope_out" := CASE

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

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

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

1170 END;

1171 "output_rows" := "output_rows" || "output_row";

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

1173 ELSIF "delegation_row"."trustee_id" NOTNULL THEN

1174 "output_row"."scope_out" := CASE

1175 WHEN

1176 "delegation_row"."area_id" ISNULL AND

1177 "delegation_row"."issue_id" ISNULL

1178 THEN 'global'

1179 WHEN

1180 "delegation_row"."area_id" NOTNULL AND

1181 "delegation_row"."issue_id" ISNULL

1182 THEN 'area'

1183 WHEN

1184 "delegation_row"."area_id" ISNULL AND

1185 "delegation_row"."issue_id" NOTNULL

1186 THEN 'issue'

1187 END;

1188 "output_rows" := "output_rows" || "output_row";

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

1190 ELSE

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

1192 "output_rows" := "output_rows" || "output_row";

1193 EXIT;

1194 END IF;

1195 EXIT WHEN "loop_member_id_v" NOTNULL;

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

1197 END LOOP;

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

1199 "i" := 1;

1200 "loop_v" := FALSE;

1201 LOOP

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

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

1204 IF "loop_v" THEN

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

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

1207 ELSIF "i" = "row_count" THEN

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

1209 ELSE

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

1211 END IF;

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

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

1214 "loop_v" := TRUE;

1215 END IF;

1216 IF "area_id_p" ISNULL AND "issue_id_p" ISNULL THEN

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

1218 END IF;

1219 RETURN NEXT "output_row";

1220 "i" := "i" + 1;

1221 END LOOP;

1222 RETURN;

1223 END;

1224 $$;

1225

1226 COMMENT ON FUNCTION "delegation_chain"

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

1228 "area"."id"%TYPE,

1229 "issue"."id"%TYPE,

1230 "member"."id"%TYPE )

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

1232

1233 CREATE FUNCTION "delegation_chain"

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

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

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

1237 RETURNS SETOF "delegation_chain_row"

1238 LANGUAGE 'plpgsql' STABLE AS $$

1239 DECLARE

1240 "result_row" "delegation_chain_row";

1241 BEGIN

1242 FOR "result_row" IN

1243 SELECT * FROM "delegation_chain"(

1244 "member_id_p", "area_id_p", "issue_id_p", NULL

1245 )

1246 LOOP

1247 RETURN NEXT "result_row";

1248 END LOOP;

1249 RETURN;

1250 END;

1251 $$;

1252

1253 COMMENT ON FUNCTION "delegation_chain"

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

1255 "area"."id"%TYPE,

1256 "issue"."id"%TYPE )

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

1258

1259

1260

1261 ------------------------------

1262 -- Comparison by vote count --

1263 ------------------------------

1264

1265 CREATE FUNCTION "vote_ratio"

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

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

1268 RETURNS FLOAT8

1269 LANGUAGE 'plpgsql' STABLE AS $$

1270 DECLARE

1271 "total_v" INT4;

1272 BEGIN

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

1274 IF "total_v" > 0 THEN

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

1276 ELSE

1277 RETURN 0.5;

1278 END IF;

1279 END;

1280 $$;

1281

1282 COMMENT ON FUNCTION "vote_ratio"

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

1284 "initiative"."negative_votes"%TYPE )

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

1286

1287

1288

1289 ------------------------------------------------

1290 -- Locking for snapshots and voting procedure --

1291 ------------------------------------------------

1292

1293 CREATE FUNCTION "global_lock"() RETURNS VOID

1294 LANGUAGE 'plpgsql' VOLATILE AS $$

1295 BEGIN

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

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

1298 LOCK TABLE "member" IN EXCLUSIVE MODE;

1299 LOCK TABLE "area" IN EXCLUSIVE MODE;

1300 LOCK TABLE "membership" IN EXCLUSIVE MODE;

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

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

1303 LOCK TABLE "policy" IN EXCLUSIVE MODE;

1304 LOCK TABLE "issue" IN EXCLUSIVE MODE;

1305 LOCK TABLE "initiative" IN EXCLUSIVE MODE;

1306 LOCK TABLE "draft" IN EXCLUSIVE MODE;

1307 LOCK TABLE "suggestion" IN EXCLUSIVE MODE;

1308 LOCK TABLE "interest" IN EXCLUSIVE MODE;

1309 LOCK TABLE "initiator" IN EXCLUSIVE MODE;

1310 LOCK TABLE "supporter" IN EXCLUSIVE MODE;

1311 LOCK TABLE "opinion" IN EXCLUSIVE MODE;

1312 LOCK TABLE "delegation" IN EXCLUSIVE MODE;

1313 LOCK TABLE "direct_population_snapshot" IN EXCLUSIVE MODE;

1314 LOCK TABLE "delegating_population_snapshot" IN EXCLUSIVE MODE;

1315 LOCK TABLE "direct_interest_snapshot" IN EXCLUSIVE MODE;

1316 LOCK TABLE "delegating_interest_snapshot" IN EXCLUSIVE MODE;

1317 LOCK TABLE "direct_supporter_snapshot" IN EXCLUSIVE MODE;

1318 LOCK TABLE "direct_voter" IN EXCLUSIVE MODE;

1319 LOCK TABLE "delegating_voter" IN EXCLUSIVE MODE;

1320 LOCK TABLE "vote" IN EXCLUSIVE MODE;

1321 RETURN;

1322 END;

1323 $$;

1324

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

1326

1327

1328

1329 -------------------------------

1330 -- Materialize member counts --

1331 -------------------------------

1332

1333 CREATE FUNCTION "calculate_member_counts"()

1334 RETURNS VOID

1335 LANGUAGE 'plpgsql' VOLATILE AS $$

1336 BEGIN

1337 LOCK TABLE "member" IN EXCLUSIVE MODE;

1338 LOCK TABLE "area" IN EXCLUSIVE MODE;

1339 LOCK TABLE "membership" IN EXCLUSIVE MODE;

1340 DELETE FROM "member_count";

1341 INSERT INTO "member_count" ("total_count")

1342 SELECT "total_count" FROM "member_count_view";

1343 UPDATE "area" SET

1344 "direct_member_count" = "view"."direct_member_count",

1345 "member_weight" = "view"."member_weight",

1346 "autoreject_weight" = "view"."autoreject_weight"

1347 FROM "area_member_count" AS "view"

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

1349 RETURN;

1350 END;

1351 $$;

1352

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

1354

1355

1356

1357 ------------------------------

1358 -- Calculation of snapshots --

1359 ------------------------------

1360

1361 CREATE FUNCTION "weight_of_added_delegations_for_population_snapshot"

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

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

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

1365 RETURNS "direct_population_snapshot"."weight"%TYPE

1366 LANGUAGE 'plpgsql' VOLATILE AS $$

1367 DECLARE

1368 "issue_delegation_row" "issue_delegation"%ROWTYPE;

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

1370 "weight_v" INT4;

1371 BEGIN

1372 "weight_v" := 0;

1373 FOR "issue_delegation_row" IN

1374 SELECT * FROM "issue_delegation"

1375 WHERE "trustee_id" = "member_id_p"

1376 AND "issue_id" = "issue_id_p"

1377 LOOP

1378 IF NOT EXISTS (

1379 SELECT NULL FROM "direct_population_snapshot"

1380 WHERE "issue_id" = "issue_id_p"

1381 AND "event" = 'periodic'

1382 AND "member_id" = "issue_delegation_row"."truster_id"

1383 ) AND NOT EXISTS (

1384 SELECT NULL FROM "delegating_population_snapshot"

1385 WHERE "issue_id" = "issue_id_p"

1386 AND "event" = 'periodic'

1387 AND "member_id" = "issue_delegation_row"."truster_id"

1388 ) THEN

1389 "delegate_member_ids_v" :=

1390 "member_id_p" || "delegate_member_ids_p";

1391 INSERT INTO "delegating_population_snapshot"

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

1393 VALUES (

1394 "issue_id_p",

1395 'periodic',

1396 "issue_delegation_row"."truster_id",

1397 "delegate_member_ids_v"

1398 );

1399 "weight_v" := "weight_v" + 1 +

1400 "weight_of_added_delegations_for_population_snapshot"(

1401 "issue_id_p",

1402 "issue_delegation_row"."truster_id",

1403 "delegate_member_ids_v"

1404 );

1405 END IF;

1406 END LOOP;

1407 RETURN "weight_v";

1408 END;

1409 $$;

1410

1411 COMMENT ON FUNCTION "weight_of_added_delegations_for_population_snapshot"

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

1413 "member"."id"%TYPE,

1414 "delegating_population_snapshot"."delegate_member_ids"%TYPE )

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

1416

1417

1418 CREATE FUNCTION "create_population_snapshot"

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

1420 RETURNS VOID

1421 LANGUAGE 'plpgsql' VOLATILE AS $$

1422 DECLARE

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

1424 BEGIN

1425 DELETE FROM "direct_population_snapshot"

1426 WHERE "issue_id" = "issue_id_p"

1427 AND "event" = 'periodic';

1428 DELETE FROM "delegating_population_snapshot"

1429 WHERE "issue_id" = "issue_id_p"

1430 AND "event" = 'periodic';

1431 INSERT INTO "direct_population_snapshot"

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

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

1434 "issue_id_p" AS "issue_id",

1435 'periodic' AS "event",

1436 "subquery"."member_id",

1437 "subquery"."interest_exists"

1438 FROM (

1439 SELECT

1440 "member"."id" AS "member_id",

1441 FALSE AS "interest_exists"

1442 FROM "issue"

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

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

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

1446 WHERE "issue"."id" = "issue_id_p"

1447 AND "member"."active"

1448 UNION

1449 SELECT

1450 "member"."id" AS "member_id",

1451 TRUE AS "interest_exists"

1452 FROM "interest" JOIN "member"

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

1454 WHERE "interest"."issue_id" = "issue_id_p"

1455 AND "member"."active"

1456 ) AS "subquery"

1457 ORDER BY

1458 "issue_id_p",

1459 "subquery"."member_id",

1460 "subquery"."interest_exists" DESC;

1461 FOR "member_id_v" IN

1462 SELECT "member_id" FROM "direct_population_snapshot"

1463 WHERE "issue_id" = "issue_id_p"

1464 AND "event" = 'periodic'

1465 LOOP

1466 UPDATE "direct_population_snapshot" SET

1467 "weight" = 1 +

1468 "weight_of_added_delegations_for_population_snapshot"(

1469 "issue_id_p",

1470 "member_id_v",

1471 '{}'

1472 )

1473 WHERE "issue_id" = "issue_id_p"

1474 AND "event" = 'periodic'

1475 AND "member_id" = "member_id_v";

1476 END LOOP;

1477 RETURN;

1478 END;

1479 $$;

1480

1481 COMMENT ON FUNCTION "create_population_snapshot"

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

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

1484

1485

1486 CREATE FUNCTION "weight_of_added_delegations_for_interest_snapshot"

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

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

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

1490 RETURNS "direct_interest_snapshot"."weight"%TYPE

1491 LANGUAGE 'plpgsql' VOLATILE AS $$

1492 DECLARE

1493 "issue_delegation_row" "issue_delegation"%ROWTYPE;

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

1495 "weight_v" INT4;

1496 BEGIN

1497 "weight_v" := 0;

1498 FOR "issue_delegation_row" IN

1499 SELECT * FROM "issue_delegation"

1500 WHERE "trustee_id" = "member_id_p"

1501 AND "issue_id" = "issue_id_p"

1502 LOOP

1503 IF NOT EXISTS (

1504 SELECT NULL FROM "direct_interest_snapshot"

1505 WHERE "issue_id" = "issue_id_p"

1506 AND "event" = 'periodic'

1507 AND "member_id" = "issue_delegation_row"."truster_id"

1508 ) AND NOT EXISTS (

1509 SELECT NULL FROM "delegating_interest_snapshot"

1510 WHERE "issue_id" = "issue_id_p"

1511 AND "event" = 'periodic'

1512 AND "member_id" = "issue_delegation_row"."truster_id"

1513 ) THEN

1514 "delegate_member_ids_v" :=

1515 "member_id_p" || "delegate_member_ids_p";

1516 INSERT INTO "delegating_interest_snapshot"

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

1518 VALUES (

1519 "issue_id_p",

1520 'periodic',

1521 "issue_delegation_row"."truster_id",

1522 "delegate_member_ids_v"

1523 );

1524 "weight_v" := "weight_v" + 1 +

1525 "weight_of_added_delegations_for_interest_snapshot"(

1526 "issue_id_p",

1527 "issue_delegation_row"."truster_id",

1528 "delegate_member_ids_v"

1529 );

1530 END IF;

1531 END LOOP;

1532 RETURN "weight_v";

1533 END;

1534 $$;

1535

1536 COMMENT ON FUNCTION "weight_of_added_delegations_for_interest_snapshot"

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

1538 "member"."id"%TYPE,

1539 "delegating_interest_snapshot"."delegate_member_ids"%TYPE )

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

1541

1542

1543 CREATE FUNCTION "create_interest_snapshot"

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

1545 RETURNS VOID

1546 LANGUAGE 'plpgsql' VOLATILE AS $$

1547 DECLARE

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

1549 BEGIN

1550 DELETE FROM "direct_interest_snapshot"

1551 WHERE "issue_id" = "issue_id_p"

1552 AND "event" = 'periodic';

1553 DELETE FROM "delegating_interest_snapshot"

1554 WHERE "issue_id" = "issue_id_p"

1555 AND "event" = 'periodic';

1556 DELETE FROM "direct_supporter_snapshot"

1557 WHERE "issue_id" = "issue_id_p"

1558 AND "event" = 'periodic';

1559 INSERT INTO "direct_interest_snapshot"

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

1561 SELECT

1562 "issue_id_p" AS "issue_id",

1563 'periodic' AS "event",

1564 "member"."id" AS "member_id",

1565 "interest"."voting_requested"

1566 FROM "interest" JOIN "member"

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

1568 WHERE "interest"."issue_id" = "issue_id_p"

1569 AND "member"."active";

1570 FOR "member_id_v" IN

1571 SELECT "member_id" FROM "direct_interest_snapshot"

1572 WHERE "issue_id" = "issue_id_p"

1573 AND "event" = 'periodic'

1574 LOOP

1575 UPDATE "direct_interest_snapshot" SET

1576 "weight" = 1 +

1577 "weight_of_added_delegations_for_interest_snapshot"(

1578 "issue_id_p",

1579 "member_id_v",

1580 '{}'

1581 )

1582 WHERE "issue_id" = "issue_id_p"

1583 AND "event" = 'periodic'

1584 AND "member_id" = "member_id_v";

1585 END LOOP;

1586 INSERT INTO "direct_supporter_snapshot"

1587 ( "issue_id", "initiative_id", "event", "member_id",

1588 "informed", "satisfied" )

1589 SELECT

1590 "issue_id_p" AS "issue_id",

1591 "initiative"."id" AS "initiative_id",

1592 'periodic' AS "event",

1593 "member"."id" AS "member_id",

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

1595 NOT EXISTS (

1596 SELECT NULL FROM "critical_opinion"

1597 WHERE "initiative_id" = "initiative"."id"

1598 AND "member_id" = "member"."id"

1599 ) AS "satisfied"

1600 FROM "supporter"

1601 JOIN "member"

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

1603 JOIN "initiative"

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

1605 JOIN "current_draft"

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

1607 JOIN "direct_interest_snapshot"

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

1609 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"

1610 AND "event" = 'periodic'

1611 WHERE "member"."active"

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

1613 RETURN;

1614 END;

1615 $$;

1616

1617 COMMENT ON FUNCTION "create_interest_snapshot"

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

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

1620

1621

1622 CREATE FUNCTION "create_snapshot"

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

1624 RETURNS VOID

1625 LANGUAGE 'plpgsql' VOLATILE AS $$

1626 DECLARE

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

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

1629 BEGIN

1630 PERFORM "global_lock"();

1631 PERFORM "create_population_snapshot"("issue_id_p");

1632 PERFORM "create_interest_snapshot"("issue_id_p");

1633 UPDATE "issue" SET

1634 "snapshot" = now(),

1635 "population" = (

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

1637 FROM "direct_population_snapshot"

1638 WHERE "issue_id" = "issue_id_p"

1639 AND "event" = 'periodic'

1640 ),

1641 "vote_now" = (

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

1643 FROM "direct_interest_snapshot"

1644 WHERE "issue_id" = "issue_id_p"

1645 AND "event" = 'periodic'

1646 AND "voting_requested" = TRUE

1647 ),

1648 "vote_later" = (

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

1650 FROM "direct_interest_snapshot"

1651 WHERE "issue_id" = "issue_id_p"

1652 AND "event" = 'periodic'

1653 AND "voting_requested" = FALSE

1654 )

1655 WHERE "id" = "issue_id_p";

1656 FOR "initiative_id_v" IN

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

1658 LOOP

1659 UPDATE "initiative" SET

1660 "supporter_count" = (

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

1662 FROM "direct_interest_snapshot" AS "di"

1663 JOIN "direct_supporter_snapshot" AS "ds"

1664 ON "di"."member_id" = "ds"."member_id"

1665 WHERE "di"."issue_id" = "issue_id_p"

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

1667 AND "ds"."initiative_id" = "initiative_id_v"

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

1669 ),

1670 "informed_supporter_count" = (

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

1672 FROM "direct_interest_snapshot" AS "di"

1673 JOIN "direct_supporter_snapshot" AS "ds"

1674 ON "di"."member_id" = "ds"."member_id"

1675 WHERE "di"."issue_id" = "issue_id_p"

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

1677 AND "ds"."initiative_id" = "initiative_id_v"

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

1679 AND "ds"."informed"

1680 ),

1681 "satisfied_supporter_count" = (

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

1683 FROM "direct_interest_snapshot" AS "di"

1684 JOIN "direct_supporter_snapshot" AS "ds"

1685 ON "di"."member_id" = "ds"."member_id"

1686 WHERE "di"."issue_id" = "issue_id_p"

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

1688 AND "ds"."initiative_id" = "initiative_id_v"

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

1690 AND "ds"."satisfied"

1691 ),

1692 "satisfied_informed_supporter_count" = (

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

1694 FROM "direct_interest_snapshot" AS "di"

1695 JOIN "direct_supporter_snapshot" AS "ds"

1696 ON "di"."member_id" = "ds"."member_id"

1697 WHERE "di"."issue_id" = "issue_id_p"

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

1699 AND "ds"."initiative_id" = "initiative_id_v"

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

1701 AND "ds"."informed"

1702 AND "ds"."satisfied"

1703 )

1704 WHERE "id" = "initiative_id_v";

1705 FOR "suggestion_id_v" IN

1706 SELECT "id" FROM "suggestion"

1707 WHERE "initiative_id" = "initiative_id_v"

1708 LOOP

1709 UPDATE "suggestion" SET

1710 "minus2_unfulfilled_count" = (

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

1712 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"

1713 ON "opinion"."member_id" = "snapshot"."member_id"

1714 WHERE "opinion"."suggestion_id" = "suggestion_id_v"

1715 AND "snapshot"."issue_id" = "issue_id_p"

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

1717 AND "opinion"."fulfilled" = FALSE

1718 ),

1719 "minus2_fulfilled_count" = (

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

1721 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"

1722 ON "opinion"."member_id" = "snapshot"."member_id"

1723 WHERE "opinion"."suggestion_id" = "suggestion_id_v"

1724 AND "snapshot"."issue_id" = "issue_id_p"

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

1726 AND "opinion"."fulfilled" = TRUE

1727 ),

1728 "minus1_unfulfilled_count" = (

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

1730 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"

1731 ON "opinion"."member_id" = "snapshot"."member_id"

1732 WHERE "opinion"."suggestion_id" = "suggestion_id_v"

1733 AND "snapshot"."issue_id" = "issue_id_p"

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

1735 AND "opinion"."fulfilled" = FALSE

1736 ),

1737 "minus1_fulfilled_count" = (

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

1739 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"

1740 ON "opinion"."member_id" = "snapshot"."member_id"

1741 WHERE "opinion"."suggestion_id" = "suggestion_id_v"

1742 AND "snapshot"."issue_id" = "issue_id_p"

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

1744 AND "opinion"."fulfilled" = TRUE

1745 ),

1746 "plus1_unfulfilled_count" = (

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

1748 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"

1749 ON "opinion"."member_id" = "snapshot"."member_id"

1750 WHERE "opinion"."suggestion_id" = "suggestion_id_v"

1751 AND "snapshot"."issue_id" = "issue_id_p"

1752 AND "opinion"."degree" = 1

1753 AND "opinion"."fulfilled" = FALSE

1754 ),

1755 "plus1_fulfilled_count" = (

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

1757 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"

1758 ON "opinion"."member_id" = "snapshot"."member_id"

1759 WHERE "opinion"."suggestion_id" = "suggestion_id_v"

1760 AND "snapshot"."issue_id" = "issue_id_p"

1761 AND "opinion"."degree" = 1

1762 AND "opinion"."fulfilled" = TRUE

1763 ),

1764 "plus2_unfulfilled_count" = (

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

1766 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"

1767 ON "opinion"."member_id" = "snapshot"."member_id"

1768 WHERE "opinion"."suggestion_id" = "suggestion_id_v"

1769 AND "snapshot"."issue_id" = "issue_id_p"

1770 AND "opinion"."degree" = 2

1771 AND "opinion"."fulfilled" = FALSE

1772 ),

1773 "plus2_fulfilled_count" = (

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

1775 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"

1776 ON "opinion"."member_id" = "snapshot"."member_id"

1777 WHERE "opinion"."suggestion_id" = "suggestion_id_v"

1778 AND "snapshot"."issue_id" = "issue_id_p"

1779 AND "opinion"."degree" = 2

1780 AND "opinion"."fulfilled" = TRUE

1781 )

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

1783 END LOOP;

1784 END LOOP;

1785 RETURN;

1786 END;

1787 $$;

1788

1789 COMMENT ON FUNCTION "create_snapshot"

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

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

1792

1793

1794 CREATE FUNCTION "set_snapshot_event"

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

1796 "event_p" "snapshot_event" )

1797 RETURNS VOID

1798 LANGUAGE 'plpgsql' VOLATILE AS $$

1799 BEGIN

1800 UPDATE "direct_population_snapshot" SET "event" = "event_p"

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

1802 UPDATE "delegating_population_snapshot" SET "event" = "event_p"

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

1804 UPDATE "direct_interest_snapshot" SET "event" = "event_p"

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

1806 UPDATE "delegating_interest_snapshot" SET "event" = "event_p"

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

1808 UPDATE "direct_supporter_snapshot" SET "event" = "event_p"

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

1810 RETURN;

1811 END;

1812 $$;

1813

1814 COMMENT ON FUNCTION "set_snapshot_event"

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

1816 "snapshot_event" )

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

1818

1819

1820

1821 ---------------------

1822 -- Freezing issues --

1823 ---------------------

1824

1825 CREATE FUNCTION "freeze_after_snapshot"

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

1827 RETURNS VOID

1828 LANGUAGE 'plpgsql' VOLATILE AS $$

1829 DECLARE

1830 "issue_row" "issue"%ROWTYPE;

1831 "policy_row" "policy"%ROWTYPE;

1832 "initiative_row" "initiative"%ROWTYPE;

1833 BEGIN

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

1835 SELECT * INTO "policy_row"

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

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

1838 UPDATE "issue" SET

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

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

1841 "fully_frozen" = now()

1842 WHERE "id" = "issue_id_p";

1843 FOR "initiative_row" IN

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

1845 LOOP

1846 IF

1847 "initiative_row"."satisfied_supporter_count" > 0 AND

1848 "initiative_row"."satisfied_supporter_count" *

1849 "policy_row"."initiative_quorum_den" >=

1850 "issue_row"."population" * "policy_row"."initiative_quorum_num"

1851 THEN

1852 UPDATE "initiative" SET "admitted" = TRUE

1853 WHERE "id" = "initiative_row"."id";

1854 ELSE

1855 UPDATE "initiative" SET "admitted" = FALSE

1856 WHERE "id" = "initiative_row"."id";

1857 END IF;

1858 END LOOP;

1859 RETURN;

1860 END;

1861 $$;

1862

1863 COMMENT ON FUNCTION "freeze_after_snapshot"

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

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

1866

1867

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

1869 RETURNS VOID

1870 LANGUAGE 'plpgsql' VOLATILE AS $$

1871 DECLARE

1872 "issue_row" "issue"%ROWTYPE;

1873 BEGIN

1874 PERFORM "create_snapshot"("issue_id_p");

1875 PERFORM "freeze_after_snapshot"("issue_id_p");

1876 RETURN;

1877 END;

1878 $$;

1879

1880 COMMENT ON FUNCTION "freeze_after_snapshot"

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

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

1883

1884

1885

1886 -----------------------

1887 -- Counting of votes --

1888 -----------------------

1889

1890

1891 CREATE FUNCTION "weight_of_added_vote_delegations"

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

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

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

1895 RETURNS "direct_voter"."weight"%TYPE

1896 LANGUAGE 'plpgsql' VOLATILE AS $$

1897 DECLARE

1898 "issue_delegation_row" "issue_delegation"%ROWTYPE;

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

1900 "weight_v" INT4;

1901 BEGIN

1902 "weight_v" := 0;

1903 FOR "issue_delegation_row" IN

1904 SELECT * FROM "issue_delegation"

1905 WHERE "trustee_id" = "member_id_p"

1906 AND "issue_id" = "issue_id_p"

1907 LOOP

1908 IF NOT EXISTS (

1909 SELECT NULL FROM "direct_voter"

1910 WHERE "member_id" = "issue_delegation_row"."truster_id"

1911 AND "issue_id" = "issue_id_p"

1912 ) AND NOT EXISTS (

1913 SELECT NULL FROM "delegating_voter"

1914 WHERE "member_id" = "issue_delegation_row"."truster_id"

1915 AND "issue_id" = "issue_id_p"

1916 ) THEN

1917 "delegate_member_ids_v" :=

1918 "member_id_p" || "delegate_member_ids_p";

1919 INSERT INTO "delegating_voter"

1920 ("member_id", "issue_id", "delegate_member_ids")

1921 VALUES (

1922 "issue_delegation_row"."truster_id",

1923 "issue_id_p",

1924 "delegate_member_ids_v"

1925 );

1926 "weight_v" := "weight_v" +

1927 1 + "weight_of_added_vote_delegations"(

1928 "issue_id_p",

1929 "issue_delegation_row"."truster_id",

1930 "delegate_member_ids_v"

1931 );

1932 END IF;

1933 END LOOP;

1934 RETURN "weight_v";

1935 END;

1936 $$;

1937

1938 COMMENT ON FUNCTION "weight_of_added_vote_delegations"

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

1940 "member"."id"%TYPE,

1941 "delegating_voter"."delegate_member_ids"%TYPE )

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

1943

1944

1945 CREATE FUNCTION "add_vote_delegations"

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

1947 RETURNS VOID

1948 LANGUAGE 'plpgsql' VOLATILE AS $$

1949 DECLARE

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

1951 BEGIN

1952 FOR "member_id_v" IN

1953 SELECT "member_id" FROM "direct_voter"

1954 WHERE "issue_id" = "issue_id_p"

1955 LOOP

1956 UPDATE "direct_voter" SET

1957 "weight" = "weight" + "weight_of_added_vote_delegations"(

1958 "issue_id_p",

1959 "member_id_v",

1960 '{}'

1961 )

1962 WHERE "member_id" = "member_id_v"

1963 AND "issue_id" = "issue_id_p";

1964 END LOOP;

1965 RETURN;

1966 END;

1967 $$;

1968

1969 COMMENT ON FUNCTION "add_vote_delegations"

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

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

1972

1973

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

1975 RETURNS VOID

1976 LANGUAGE 'plpgsql' VOLATILE AS $$

1977 DECLARE

1978 "issue_row" "issue"%ROWTYPE;

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

1980 BEGIN

1981 PERFORM "global_lock"();

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

1983 DELETE FROM "delegating_voter"

1984 WHERE "issue_id" = "issue_id_p";

1985 DELETE FROM "direct_voter"

1986 WHERE "issue_id" = "issue_id_p"

1987 AND "autoreject" = TRUE;

1988 DELETE FROM "direct_voter" USING "member"

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

1990 AND "direct_voter"."issue_id" = "issue_id_p"

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

1992 UPDATE "direct_voter" SET "weight" = 1

1993 WHERE "issue_id" = "issue_id_p";

1994 PERFORM "add_vote_delegations"("issue_id_p");

1995 FOR "member_id_v" IN

1996 SELECT "interest"."member_id"

1997 FROM "interest"

1998 LEFT JOIN "direct_voter"

1999 ON "interest"."member_id" = "direct_voter"."member_id"

2000 AND "interest"."issue_id" = "direct_voter"."issue_id"

2001 LEFT JOIN "delegating_voter"

2002 ON "interest"."member_id" = "delegating_voter"."member_id"

2003 AND "interest"."issue_id" = "delegating_voter"."issue_id"

2004 WHERE "interest"."issue_id" = "issue_id_p"

2005 AND "interest"."autoreject" = TRUE

2006 AND "direct_voter"."member_id" ISNULL

2007 AND "delegating_voter"."member_id" ISNULL

2008 UNION SELECT "membership"."member_id"

2009 FROM "membership"

2010 LEFT JOIN "interest"

2011 ON "membership"."member_id" = "interest"."member_id"

2012 AND "interest"."issue_id" = "issue_id_p"

2013 LEFT JOIN "direct_voter"

2014 ON "membership"."member_id" = "direct_voter"."member_id"

2015 AND "direct_voter"."issue_id" = "issue_id_p"

2016 LEFT JOIN "delegating_voter"

2017 ON "membership"."member_id" = "delegating_voter"."member_id"

2018 AND "delegating_voter"."issue_id" = "issue_id_p"

2019 WHERE "membership"."area_id" = "issue_row"."area_id"

2020 AND "membership"."autoreject" = TRUE

2021 AND "interest"."autoreject" ISNULL

2022 AND "direct_voter"."member_id" ISNULL

2023 AND "delegating_voter"."member_id" ISNULL

2024 LOOP

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

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

2027 INSERT INTO "vote" (

2028 "member_id",

2029 "issue_id",

2030 "initiative_id",

2031 "grade"

2032 ) SELECT

2033 "member_id_v" AS "member_id",

2034 "issue_id_p" AS "issue_id",

2035 "id" AS "initiative_id",

2036 -1 AS "grade"

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

2038 END LOOP;

2039 PERFORM "add_vote_delegations"("issue_id_p");

2040 UPDATE "issue" SET

2041 "voter_count" = (

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

2043 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"

2044 );

2045 UPDATE "initiative" SET

2046 "positive_votes" = "subquery"."positive_votes",

2047 "negative_votes" = "subquery"."negative_votes"

2048 FROM (

2049 SELECT

2050 "initiative_id",

2051 coalesce(

2052 sum(

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

2054 ),

2055 0

2056 ) AS "positive_votes",

2057 coalesce(

2058 sum(

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

2060 ),

2061 0

2062 ) AS "negative_votes"

2063 FROM "vote" JOIN "direct_voter"

2064 ON "vote"."member_id" = "direct_voter"."member_id"

2065 AND "vote"."issue_id" = "direct_voter"."issue_id"

2066 WHERE "vote"."issue_id" = "issue_id_p"

2067 GROUP BY "initiative_id"

2068 ) AS "subquery"

2069 WHERE "initiative"."admitted"

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

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

2072 END;

2073 $$;

2074

2075 COMMENT ON FUNCTION "close_voting"

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

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

2078

2079

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

2081 RETURNS INT4[]

2082 LANGUAGE 'plpgsql' IMMUTABLE AS $$

2083 DECLARE

2084 "i" INTEGER;

2085 "ary_text_v" TEXT;

2086 BEGIN

2087 IF "dim_p" >= 1 THEN

2088 "ary_text_v" := '{NULL';

2089 "i" := "dim_p";

2090 LOOP

2091 "i" := "i" - 1;

2092 EXIT WHEN "i" = 0;

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

2094 END LOOP;

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

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

2097 ELSE

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

2099 END IF;

2100 END;

2101 $$;

2102

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

2104

2105

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

2107 RETURNS INT4[][]

2108 LANGUAGE 'plpgsql' IMMUTABLE AS $$

2109 DECLARE

2110 "i" INTEGER;

2111 "row_text_v" TEXT;

2112 "ary_text_v" TEXT;

2113 BEGIN

2114 IF "dim_p" >= 1 THEN

2115 "row_text_v" := '{NULL';

2116 "i" := "dim_p";

2117 LOOP

2118 "i" := "i" - 1;

2119 EXIT WHEN "i" = 0;

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

2121 END LOOP;

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

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

2124 "i" := "dim_p";

2125 LOOP

2126 "i" := "i" - 1;

2127 EXIT WHEN "i" = 0;

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

2129 END LOOP;

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

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

2132 ELSE

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

2134 END IF;

2135 END;

2136 $$;

2137

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

2139

2140

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

2142 RETURNS VOID

2143 LANGUAGE 'plpgsql' VOLATILE AS $$

2144 DECLARE

2145 "dimension_v" INTEGER;

2146 "matrix" INT4[][];

2147 "i" INTEGER;

2148 "j" INTEGER;

2149 "k" INTEGER;

2150 "battle_row" "battle"%ROWTYPE;

2151 "rank_ary" INT4[];

2152 "rank_v" INT4;

2153 "done_v" INTEGER;

2154 "winners_ary" INTEGER[];

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

2156 BEGIN

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

2158 -- Prepare matrix for Schulze-Method:

2159 SELECT count(1) INTO "dimension_v"

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

2161 IF "dimension_v" = 1 THEN

2162 UPDATE "initiative" SET

2163 "rank" = 1

2164 FROM "battle_participant"

2165 WHERE "initiative"."issue_id" = "issue_id_p"

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

2167 ELSIF "dimension_v" > 1 THEN

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

2169 "i" := 1;

2170 "j" := 2;

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

2172 FOR "battle_row" IN

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

2174 ORDER BY "winning_initiative_id", "losing_initiative_id"

2175 LOOP

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

2177 IF "j" = "dimension_v" THEN

2178 "i" := "i" + 1;

2179 "j" := 1;

2180 ELSE

2181 "j" := "j" + 1;

2182 IF "j" = "i" THEN

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

2184 END IF;

2185 END IF;

2186 END LOOP;

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

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

2189 END IF;

2190 -- Delete losers from matrix:

2191 "i" := 1;

2192 LOOP

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

2194 LOOP

2195 IF "i" != "j" THEN

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

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

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

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

2200 ELSE

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

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

2203 END IF;

2204 END IF;

2205 EXIT WHEN "j" = "dimension_v";

2206 "j" := "j" + 1;

2207 END LOOP;

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

2209 "i" := "i" + 1;

2210 END LOOP;

2211 -- Find best paths:

2212 "i" := 1;

2213 LOOP

2214 "j" := 1;

2215 LOOP

2216 IF "i" != "j" THEN

2217 "k" := 1;

2218 LOOP

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

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

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

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

2223 END IF;

2224 ELSE

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

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

2227 END IF;

2228 END IF;

2229 END IF;

2230 EXIT WHEN "k" = "dimension_v";

2231 "k" := "k" + 1;

2232 END LOOP;

2233 END IF;

2234 EXIT WHEN "j" = "dimension_v";

2235 "j" := "j" + 1;

2236 END LOOP;

2237 EXIT WHEN "i" = "dimension_v";

2238 "i" := "i" + 1;

2239 END LOOP;

2240 -- Determine order of winners:

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

2242 "rank_v" := 1;

2243 "done_v" := 0;

2244 LOOP

2245 "winners_ary" := '{}';

2246 "i" := 1;

2247 LOOP

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

2249 "j" := 1;

2250 LOOP

2251 IF

2252 "i" != "j" AND

2253 "rank_ary"["j"] ISNULL AND

2254 "matrix"["j"]["i"] > "matrix"["i"]["j"]

2255 THEN

2256 -- someone else is better

2257 EXIT;

2258 END IF;

2259 IF "j" = "dimension_v" THEN

2260 -- noone is better

2261 "winners_ary" := "winners_ary" || "i";

2262 EXIT;

2263 END IF;

2264 "j" := "j" + 1;

2265 END LOOP;

2266 END IF;

2267 EXIT WHEN "i" = "dimension_v";

2268 "i" := "i" + 1;

2269 END LOOP;

2270 "i" := 1;

2271 LOOP

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

2273 "done_v" := "done_v" + 1;

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

2275 "i" := "i" + 1;

2276 END LOOP;

2277 EXIT WHEN "done_v" = "dimension_v";

2278 "rank_v" := "rank_v" + 1;

2279 END LOOP;

2280 -- write preliminary ranks:

2281 "i" := 1;

2282 FOR "initiative_id_v" IN

2283 SELECT "initiative"."id"

2284 FROM "initiative" JOIN "battle_participant"

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

2286 WHERE "initiative"."issue_id" = "issue_id_p"

2287 ORDER BY "initiative"."id"

2288 LOOP

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

2290 WHERE "id" = "initiative_id_v";

2291 "i" := "i" + 1;

2292 END LOOP;

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

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

2295 END IF;

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

2297 "rank_v" := 1;

2298 FOR "initiative_id_v" IN

2299 SELECT "id" FROM "initiative"

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

2301 ORDER BY

2302 "rank",

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

2304 "id"

2305 LOOP

2306 UPDATE "initiative" SET "rank" = "rank_v"

2307 WHERE "id" = "initiative_id_v";

2308 "rank_v" := "rank_v" + 1;

2309 END LOOP;

2310 END IF;

2311 -- mark issue as finished

2312 UPDATE "issue" SET "ranks_available" = TRUE

2313 WHERE "id" = "issue_id_p";

2314 RETURN;

2315 END;

2316 $$;

2317

2318 COMMENT ON FUNCTION "calculate_ranks"

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

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

2321

2322

2323

2324 -----------------------------

2325 -- Automatic state changes --

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

2327

2328

2329 CREATE FUNCTION "check_issue"

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

2331 RETURNS VOID

2332 LANGUAGE 'plpgsql' VOLATILE AS $$

2333 DECLARE

2334 "issue_row" "issue"%ROWTYPE;

2335 "policy_row" "policy"%ROWTYPE;

2336 "voting_requested_v" BOOLEAN;

2337 BEGIN

2338 PERFORM "global_lock"();

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

2340 IF "issue_row"."closed" ISNULL THEN

2341 SELECT * INTO "policy_row" FROM "policy"

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

2343 IF "issue_row"."fully_frozen" ISNULL THEN

2344 PERFORM "create_snapshot"("issue_id_p");

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

2346 END IF;

2347 IF "issue_row"."accepted" ISNULL THEN

2348 IF EXISTS (

2349 SELECT NULL FROM "initiative"

2350 WHERE "issue_id" = "issue_id_p"

2351 AND "supporter_count" > 0

2352 AND "supporter_count" * "policy_row"."issue_quorum_den"

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

2354 ) THEN

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

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

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

2358 WHERE "id" = "issue_row"."id";

2359 ELSIF

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

2361 THEN

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

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

2364 WHERE "id" = "issue_row"."id";

2365 END IF;

2366 END IF;

2367 IF

2368 "issue_row"."accepted" NOTNULL AND

2369 "issue_row"."half_frozen" ISNULL

2370 THEN

2371 SELECT

2372 CASE

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

2374 TRUE

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

2376 FALSE

2377 ELSE NULL

2378 END

2379 INTO "voting_requested_v"

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

2381 IF

2382 "voting_requested_v" OR (

2383 "voting_requested_v" ISNULL AND

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

2385 )

2386 THEN

2387 "issue_row"."half_frozen" = now(); -- NOTE: "issue_row" used later

2388 UPDATE "issue" SET "half_frozen" = "issue_row"."half_frozen"

2389 WHERE "id" = "issue_row"."id";

2390 END IF;

2391 END IF;

2392 IF

2393 "issue_row"."half_frozen" NOTNULL AND

2394 "issue_row"."fully_frozen" ISNULL AND

2395 now() >= "issue_row"."half_frozen" + "policy_row"."verification_time"

2396 THEN

2397 "issue_row"."fully_frozen" = now(); -- NOTE: "issue_row" used later

2398 PERFORM "freeze_after_snapshot"("issue_id_p");

2399 END IF;

2400 IF

2401 "issue_row"."fully_frozen" NOTNULL AND

2402 now() >= "issue_row"."fully_frozen" + "policy_row"."voting_time"

2403 THEN

2404 PERFORM "close_voting"("issue_id_p");

2405 END IF;

2406 END IF;

2407 RETURN;

2408 END;

2409 $$;

2410

2411 COMMENT ON FUNCTION "check_issue"

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

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

2414

2415

2416 CREATE FUNCTION "check_everything"()

2417 RETURNS VOID

2418 LANGUAGE 'plpgsql' VOLATILE AS $$

2419 DECLARE

2420 "issue_id_v" "issue"."id"%TYPE;

2421 BEGIN

2422 DELETE FROM "expired_session";

2423 PERFORM "calculate_member_counts"();

2424 FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP

2425 PERFORM "check_issue"("issue_id_v");

2426 END LOOP;

2427 FOR "issue_id_v" IN SELECT "id" FROM "issue_with_ranks_missing" LOOP

2428 PERFORM "calculate_ranks"("issue_id_v");

2429 END LOOP;

2430 RETURN;

2431 END;

2432 $$;

2433

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

2435

2436

2437

2438 COMMIT;