1 BEGIN;

2

3

4 -- update version number

5

6 CREATE OR REPLACE VIEW "liquid_feedback_version" AS

7 SELECT * FROM (VALUES ('2.1.0', 2, 1, 0))

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

9

10

11 -- old API tables are now deprecated

12

13 COMMENT ON TYPE "application_access_level" IS 'DEPRECATED, WILL BE REMOVED! Access privileges for applications using the API';

14 COMMENT ON TABLE "member_application" IS 'DEPRECATED, WILL BE REMOVED! Registered application being allowed to use the API';

15

16

17 -- new polling mode and changed privileges

18

19 ALTER TABLE "policy" ADD COLUMN "polling" BOOLEAN NOT NULL DEFAULT FALSE;

20 ALTER TABLE "policy" ALTER COLUMN "admission_time" DROP NOT NULL;

21 ALTER TABLE "policy" ALTER COLUMN "discussion_time" DROP NOT NULL;

22 ALTER TABLE "policy" ALTER COLUMN "verification_time" DROP NOT NULL;

23 ALTER TABLE "policy" ALTER COLUMN "voting_time" DROP NOT NULL;

24 ALTER TABLE "policy" ALTER COLUMN "issue_quorum_num" DROP NOT NULL;

25 ALTER TABLE "policy" ALTER COLUMN "issue_quorum_den" DROP NOT NULL;

26 ALTER TABLE "policy" ADD CONSTRAINT "timing" CHECK (

27 ( "polling" = FALSE AND

28 "admission_time" NOTNULL AND "discussion_time" NOTNULL AND

29 "verification_time" NOTNULL AND "voting_time" NOTNULL ) OR

30 ( "polling" = TRUE AND

31 "admission_time" ISNULL AND "discussion_time" NOTNULL AND

32 "verification_time" NOTNULL AND "voting_time" NOTNULL ) OR

33 ( "polling" = TRUE AND

34 "admission_time" ISNULL AND "discussion_time" ISNULL AND

35 "verification_time" ISNULL AND "voting_time" ISNULL ) );

36 ALTER TABLE "policy" ADD CONSTRAINT "issue_quorum_if_and_only_if_not_polling" CHECK (

37 "polling" = "issue_quorum_num" ISNULL AND

38 "polling" = "issue_quorum_den" ISNULL );

39 COMMENT ON COLUMN "policy"."polling" IS 'TRUE = special policy for non-user-generated issues without issue quorum, where certain initiatives (those having the "polling" flag set) do not need to pass the initiative quorum; "admission_time" MUST be set to NULL, the other timings may be set to NULL altogether, allowing individual timing for those issues';

40

41 ALTER TABLE "issue" ALTER COLUMN "admission_time" DROP NOT NULL;

42 ALTER TABLE "issue" ADD CONSTRAINT "admission_time_not_null_unless_instantly_accepted" CHECK (

43 "admission_time" NOTNULL OR ("accepted" NOTNULL AND "accepted" = "created") );

44

45 ALTER TABLE "initiative" ADD COLUMN "polling" BOOLEAN NOT NULL DEFAULT FALSE;

46 COMMENT ON COLUMN "initiative"."polling" IS 'Initiative does not need to pass the initiative quorum (see "policy"."polling")';

47

48 ALTER TABLE "privilege" RENAME COLUMN "voting_right_manager" TO "member_manager";

49 ALTER TABLE "privilege" ADD COLUMN "initiative_right" BOOLEAN NOT NULL DEFAULT TRUE;

50 ALTER TABLE "privilege" ADD COLUMN "polling_right" BOOLEAN NOT NULL DEFAULT FALSE;

51 UPDATE "privilege" SET "initiative_right" = "voting_right";

52 COMMENT ON COLUMN "privilege"."admin_manager" IS 'Grant/revoke any privileges to/from other members';

53 COMMENT ON COLUMN "privilege"."member_manager" IS 'Adding/removing members from the unit, granting or revoking "initiative_right" and "voting_right"';

54 COMMENT ON COLUMN "privilege"."initiative_right" IS 'Right to create an initiative';

55 COMMENT ON COLUMN "privilege"."voting_right" IS 'Right to support initiatives, create and rate suggestions, and to vote';

56 COMMENT ON COLUMN "privilege"."polling_right" IS 'Right to create issues with policies having the "policy"."polling" flag set, and to add initiatives having the "initiative"."polling" flag set to those issues';

57

58 DROP VIEW "member_contingent_left";

59 DROP VIEW "member_contingent";

60 ALTER TABLE "contingent" DROP CONSTRAINT "contingent_pkey";

61 ALTER TABLE "contingent" ALTER COLUMN "time_frame" DROP NOT NULL;

62 ALTER TABLE "contingent" ADD COLUMN "polling" BOOLEAN;

63 ALTER TABLE "contingent" ADD PRIMARY KEY ("polling", "time_frame");

64 COMMENT ON COLUMN "contingent"."polling" IS 'Determines if settings are for creating initiatives and new drafts of initiatives with "polling" flag set';

65

66 CREATE VIEW "member_contingent" AS

67 SELECT

68 "member"."id" AS "member_id",

69 "contingent"."polling",

70 "contingent"."time_frame",

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

72 (

73 SELECT count(1) FROM "draft"

74 JOIN "initiative" ON "initiative"."id" = "draft"."initiative_id"

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

76 AND "initiative"."polling" = "contingent"."polling"

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

78 ) + (

79 SELECT count(1) FROM "suggestion"

80 JOIN "initiative" ON "initiative"."id" = "suggestion"."initiative_id"

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

82 AND "contingent"."polling" = FALSE

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

84 )

85 ELSE NULL END AS "text_entry_count",

86 "contingent"."text_entry_limit",

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

88 SELECT count(1) FROM "opening_draft" AS "draft"

89 JOIN "initiative" ON "initiative"."id" = "draft"."initiative_id"

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

91 AND "initiative"."polling" = "contingent"."polling"

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

93 ) ELSE NULL END AS "initiative_count",

94 "contingent"."initiative_limit"

95 FROM "member" CROSS JOIN "contingent";

96

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

98

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

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

101

102 CREATE VIEW "member_contingent_left" AS

103 SELECT

104 "member_id",

105 "polling",

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

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

108 FROM "member_contingent" GROUP BY "member_id", "polling";

109

110 COMMENT ON VIEW "member_contingent_left" IS 'Amount of text entries or initiatives which can be posted now instantly by a member. This view should be used by a frontend to determine, if the contingent for posting is exhausted.';

111

112 CREATE OR REPLACE FUNCTION "freeze_after_snapshot"

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

114 RETURNS VOID

115 LANGUAGE 'plpgsql' VOLATILE AS $$

116 DECLARE

117 "issue_row" "issue"%ROWTYPE;

118 "policy_row" "policy"%ROWTYPE;

119 "initiative_row" "initiative"%ROWTYPE;

120 BEGIN

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

122 SELECT * INTO "policy_row"

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

124 PERFORM "set_snapshot_event"("issue_id_p", 'full_freeze');

125 FOR "initiative_row" IN

126 SELECT * FROM "initiative"

127 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL

128 LOOP

129 IF

130 "initiative_row"."polling" OR (

131 "initiative_row"."satisfied_supporter_count" > 0 AND

132 "initiative_row"."satisfied_supporter_count" *

133 "policy_row"."initiative_quorum_den" >=

134 "issue_row"."population" * "policy_row"."initiative_quorum_num"

135 )

136 THEN

137 UPDATE "initiative" SET "admitted" = TRUE

138 WHERE "id" = "initiative_row"."id";

139 ELSE

140 UPDATE "initiative" SET "admitted" = FALSE

141 WHERE "id" = "initiative_row"."id";

142 END IF;

143 END LOOP;

144 IF EXISTS (

145 SELECT NULL FROM "initiative"

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

147 ) THEN

148 UPDATE "issue" SET

149 "state" = 'voting',

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

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

152 "fully_frozen" = now()

153 WHERE "id" = "issue_id_p";

154 ELSE

155 UPDATE "issue" SET

156 "state" = 'canceled_no_initiative_admitted',

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

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

159 "fully_frozen" = now(),

160 "closed" = now(),

161 "ranks_available" = TRUE

162 WHERE "id" = "issue_id_p";

163 -- NOTE: The following DELETE statements have effect only when

164 -- issue state has been manipulated

165 DELETE FROM "direct_voter" WHERE "issue_id" = "issue_id_p";

166 DELETE FROM "delegating_voter" WHERE "issue_id" = "issue_id_p";

167 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";

168 END IF;

169 RETURN;

170 END;

171 $$;

172

173

174 -- issue comments removed, voting comments integrated in "direct_voter" table

175

176 ALTER TABLE "direct_voter" ADD COLUMN "comment_changed" TIMESTAMPTZ;

177 ALTER TABLE "direct_voter" ADD COLUMN "formatting_engine" TEXT;

178 ALTER TABLE "direct_voter" ADD COLUMN "comment" TEXT;

179 ALTER TABLE "direct_voter" ADD COLUMN "text_search_data" TSVECTOR;

180 CREATE INDEX "direct_voter_text_search_data_idx" ON "direct_voter" USING gin ("text_search_data");

181 CREATE TRIGGER "update_text_search_data"

182 BEFORE INSERT OR UPDATE ON "direct_voter"

183 FOR EACH ROW EXECUTE PROCEDURE

184 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "comment");

185

186 COMMENT ON COLUMN "direct_voter"."comment_changed" IS 'Shall be set on comment change, to indicate a comment being modified after voting has been finished; Automatically set to NULL after voting phase; Automatically set to NULL by trigger, if "comment" is set to NULL';

187 COMMENT ON COLUMN "direct_voter"."formatting_engine" IS 'Allows different formatting engines (i.e. wiki formats) to be used for "direct_voter"."comment"; Automatically set to NULL by trigger, if "comment" is set to NULL';

188 COMMENT ON COLUMN "direct_voter"."comment" IS 'Is to be set or updated by the frontend, if comment was inserted or updated AFTER the issue has been closed. Otherwise it shall be set to NULL.';

189

190 CREATE TABLE "rendered_voter_comment" (

191 PRIMARY KEY ("issue_id", "member_id", "format"),

192 FOREIGN KEY ("issue_id", "member_id")

193 REFERENCES "direct_voter" ("issue_id", "member_id")

194 ON DELETE CASCADE ON UPDATE CASCADE,

195 "issue_id" INT4,

196 "member_id" INT4,

197 "format" TEXT,

198 "content" TEXT NOT NULL );

199

200 COMMENT ON TABLE "rendered_voter_comment" IS 'This table may be used by frontends to cache "rendered" voter comments (e.g. HTML output generated from wiki text)';

201

202 DROP TABLE "rendered_issue_comment";

203 DROP TABLE "issue_comment";

204 DROP TABLE "rendered_voting_comment";

205 DROP TABLE "voting_comment";

206

207 CREATE FUNCTION "voter_comment_fields_only_set_when_voter_comment_is_set_trigger"()

208 RETURNS TRIGGER

209 LANGUAGE 'plpgsql' VOLATILE AS $$

210 BEGIN

211 IF NEW."comment" ISNULL THEN

212 NEW."comment_changed" := NULL;

213 NEW."formatting_engine" := NULL;

214 END IF;

215 RETURN NEW;

216 END;

217 $$;

218

219 CREATE TRIGGER "voter_comment_fields_only_set_when_voter_comment_is_set"

220 BEFORE INSERT OR UPDATE ON "direct_voter"

221 FOR EACH ROW EXECUTE PROCEDURE

222 "voter_comment_fields_only_set_when_voter_comment_is_set_trigger"();

223

224 COMMENT ON FUNCTION "voter_comment_fields_only_set_when_voter_comment_is_set_trigger"() IS 'Implementation of trigger "voter_comment_fields_only_set_when_voter_comment_is_set" ON table "direct_voter"';

225 COMMENT ON TRIGGER "voter_comment_fields_only_set_when_voter_comment_is_set" ON "direct_voter" IS 'If "comment" is set to NULL, then other comment related fields are also set to NULL.';

226

227 CREATE OR REPLACE FUNCTION "forbid_changes_on_closed_issue_trigger"()

228 RETURNS TRIGGER

229 LANGUAGE 'plpgsql' VOLATILE AS $$

230 DECLARE

231 "issue_id_v" "issue"."id"%TYPE;

232 "issue_row" "issue"%ROWTYPE;

233 BEGIN

234 IF TG_RELID = 'direct_voter'::regclass AND TG_OP = 'UPDATE' THEN

235 IF

236 OLD."issue_id" = NEW."issue_id" AND

237 OLD."member_id" = NEW."member_id" AND

238 OLD."weight" = NEW."weight"

239 THEN

240 RETURN NULL; -- allows changing of voter comment

241 END IF;

242 END IF;

243 IF TG_OP = 'DELETE' THEN

244 "issue_id_v" := OLD."issue_id";

245 ELSE

246 "issue_id_v" := NEW."issue_id";

247 END IF;

248 SELECT INTO "issue_row" * FROM "issue"

249 WHERE "id" = "issue_id_v" FOR SHARE;

250 IF "issue_row"."closed" NOTNULL THEN

251 RAISE EXCEPTION 'Tried to modify data belonging to a closed issue.';

252 END IF;

253 RETURN NULL;

254 END;

255 $$;

256

257 CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)

258 RETURNS VOID

259 LANGUAGE 'plpgsql' VOLATILE AS $$

260 DECLARE

261 "area_id_v" "area"."id"%TYPE;

262 "unit_id_v" "unit"."id"%TYPE;

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

264 BEGIN

265 PERFORM "lock_issue"("issue_id_p");

266 SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";

267 SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v";

268 -- delete timestamp of voting comment:

269 UPDATE "direct_voter" SET "comment_changed" = NULL

270 WHERE "issue_id" = "issue_id_p";

271 -- delete delegating votes (in cases of manual reset of issue state):

272 DELETE FROM "delegating_voter"

273 WHERE "issue_id" = "issue_id_p";

274 -- delete votes from non-privileged voters:

275 DELETE FROM "direct_voter"

276 USING (

277 SELECT

278 "direct_voter"."member_id"

279 FROM "direct_voter"

280 JOIN "member" ON "direct_voter"."member_id" = "member"."id"

281 LEFT JOIN "privilege"

282 ON "privilege"."unit_id" = "unit_id_v"

283 AND "privilege"."member_id" = "direct_voter"."member_id"

284 WHERE "direct_voter"."issue_id" = "issue_id_p" AND (

285 "member"."active" = FALSE OR

286 "privilege"."voting_right" ISNULL OR

287 "privilege"."voting_right" = FALSE

288 )

289 ) AS "subquery"

290 WHERE "direct_voter"."issue_id" = "issue_id_p"

291 AND "direct_voter"."member_id" = "subquery"."member_id";

292 -- consider delegations:

293 UPDATE "direct_voter" SET "weight" = 1

294 WHERE "issue_id" = "issue_id_p";

295 PERFORM "add_vote_delegations"("issue_id_p");

296 -- set voter count and mark issue as being calculated:

297 UPDATE "issue" SET

298 "state" = 'calculation',

299 "closed" = now(),

300 "voter_count" = (

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

302 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"

303 )

304 WHERE "id" = "issue_id_p";

305 -- materialize battle_view:

306 -- NOTE: "closed" column of issue must be set at this point

307 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";

308 INSERT INTO "battle" (

309 "issue_id",

310 "winning_initiative_id", "losing_initiative_id",

311 "count"

312 ) SELECT

313 "issue_id",

314 "winning_initiative_id", "losing_initiative_id",

315 "count"

316 FROM "battle_view" WHERE "issue_id" = "issue_id_p";

317 -- copy "positive_votes" and "negative_votes" from "battle" table:

318 UPDATE "initiative" SET

319 "positive_votes" = "battle_win"."count",

320 "negative_votes" = "battle_lose"."count"

321 FROM "battle" AS "battle_win", "battle" AS "battle_lose"

322 WHERE

323 "battle_win"."issue_id" = "issue_id_p" AND

324 "battle_win"."winning_initiative_id" = "initiative"."id" AND

325 "battle_win"."losing_initiative_id" ISNULL AND

326 "battle_lose"."issue_id" = "issue_id_p" AND

327 "battle_lose"."losing_initiative_id" = "initiative"."id" AND

328 "battle_lose"."winning_initiative_id" ISNULL;

329 END;

330 $$;

331

332 CREATE OR REPLACE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE)

333 RETURNS VOID

334 LANGUAGE 'plpgsql' VOLATILE AS $$

335 DECLARE

336 "issue_row" "issue"%ROWTYPE;

337 BEGIN

338 SELECT * INTO "issue_row"

339 FROM "issue" WHERE "id" = "issue_id_p"

340 FOR UPDATE;

341 IF "issue_row"."cleaned" ISNULL THEN

342 UPDATE "issue" SET

343 "state" = 'voting',

344 "closed" = NULL,

345 "ranks_available" = FALSE

346 WHERE "id" = "issue_id_p";

347 DELETE FROM "delegating_voter"

348 WHERE "issue_id" = "issue_id_p";

349 DELETE FROM "direct_voter"

350 WHERE "issue_id" = "issue_id_p";

351 DELETE FROM "delegating_interest_snapshot"

352 WHERE "issue_id" = "issue_id_p";

353 DELETE FROM "direct_interest_snapshot"

354 WHERE "issue_id" = "issue_id_p";

355 DELETE FROM "delegating_population_snapshot"

356 WHERE "issue_id" = "issue_id_p";

357 DELETE FROM "direct_population_snapshot"

358 WHERE "issue_id" = "issue_id_p";

359 DELETE FROM "non_voter"

360 WHERE "issue_id" = "issue_id_p";

361 DELETE FROM "delegation"

362 WHERE "issue_id" = "issue_id_p";

363 DELETE FROM "supporter"

364 WHERE "issue_id" = "issue_id_p";

365 UPDATE "issue" SET

366 "state" = "issue_row"."state",

367 "closed" = "issue_row"."closed",

368 "ranks_available" = "issue_row"."ranks_available",

369 "cleaned" = now()

370 WHERE "id" = "issue_id_p";

371 END IF;

372 RETURN;

373 END;

374 $$;

375

376

377 -- "non_voter" deletes "direct_voter" and vice versa

378

379 CREATE FUNCTION "non_voter_deletes_direct_voter_trigger"()

380 RETURNS TRIGGER

381 LANGUAGE 'plpgsql' VOLATILE AS $$

382 BEGIN

383 DELETE FROM "direct_voter"

384 WHERE "issue_id" = NEW."issue_id" AND "member_id" = NEW."member_id";

385 RETURN NULL;

386 END;

387 $$;

388

389 CREATE TRIGGER "non_voter_deletes_direct_voter"

390 AFTER INSERT OR UPDATE ON "non_voter"

391 FOR EACH ROW EXECUTE PROCEDURE

392 "non_voter_deletes_direct_voter_trigger"();

393

394 COMMENT ON FUNCTION "non_voter_deletes_direct_voter_trigger"() IS 'Implementation of trigger "non_voter_deletes_direct_voter" on table "non_voter"';

395 COMMENT ON TRIGGER "non_voter_deletes_direct_voter" ON "non_voter" IS 'An entry in the "non_voter" table deletes an entry in the "direct_voter" table (and vice versa due to trigger "direct_voter_deletes_non_voter" on table "direct_voter")';

396

397 CREATE FUNCTION "direct_voter_deletes_non_voter_trigger"()

398 RETURNS TRIGGER

399 LANGUAGE 'plpgsql' VOLATILE AS $$

400 BEGIN

401 DELETE FROM "non_voter"

402 WHERE "issue_id" = NEW."issue_id" AND "member_id" = NEW."member_id";

403 RETURN NULL;

404 END;

405 $$;

406

407 CREATE TRIGGER "direct_voter_deletes_non_voter"

408 AFTER INSERT OR UPDATE ON "direct_voter"

409 FOR EACH ROW EXECUTE PROCEDURE

410 "direct_voter_deletes_non_voter_trigger"();

411

412 COMMENT ON FUNCTION "direct_voter_deletes_non_voter_trigger"() IS 'Implementation of trigger "direct_voter_deletes_non_voter" on table "direct_voter"';

413 COMMENT ON TRIGGER "direct_voter_deletes_non_voter" ON "direct_voter" IS 'An entry in the "direct_voter" table deletes an entry in the "non_voter" table (and vice versa due to trigger "non_voter_deletes_direct_voter" on table "non_voter")';

414

415

416 -- new comment on function "delete_private_data"()

417

418 COMMENT ON FUNCTION "delete_private_data"() IS 'Used by lf_export script. DO NOT USE on productive database, but only on a copy! This function deletes all data which should not be publicly available, and can be used to create a database dump for publication. See source code to see which data is deleted. If you need a different behaviour, copy this function and modify lf_export accordingly, to avoid data-leaks after updating.';

419

420

421 COMMIT;