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 CREATE OR REPLACE FUNCTION "freeze_after_snapshot"

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

60 RETURNS VOID

61 LANGUAGE 'plpgsql' VOLATILE AS $$

62 DECLARE

63 "issue_row" "issue"%ROWTYPE;

64 "policy_row" "policy"%ROWTYPE;

65 "initiative_row" "initiative"%ROWTYPE;

66 BEGIN

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

68 SELECT * INTO "policy_row"

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

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

71 FOR "initiative_row" IN

72 SELECT * FROM "initiative"

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

74 LOOP

75 IF

76 "initiative_row"."polling" OR (

77 "initiative_row"."satisfied_supporter_count" > 0 AND

78 "initiative_row"."satisfied_supporter_count" *

79 "policy_row"."initiative_quorum_den" >=

80 "issue_row"."population" * "policy_row"."initiative_quorum_num"

81 )

82 THEN

83 UPDATE "initiative" SET "admitted" = TRUE

84 WHERE "id" = "initiative_row"."id";

85 ELSE

86 UPDATE "initiative" SET "admitted" = FALSE

87 WHERE "id" = "initiative_row"."id";

88 END IF;

89 END LOOP;

90 IF EXISTS (

91 SELECT NULL FROM "initiative"

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

93 ) THEN

94 UPDATE "issue" SET

95 "state" = 'voting',

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

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

98 "fully_frozen" = now()

99 WHERE "id" = "issue_id_p";

100 ELSE

101 UPDATE "issue" SET

102 "state" = 'canceled_no_initiative_admitted',

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

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

105 "fully_frozen" = now(),

106 "closed" = now(),

107 "ranks_available" = TRUE

108 WHERE "id" = "issue_id_p";

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

110 -- issue state has been manipulated

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

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

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

114 END IF;

115 RETURN;

116 END;

117 $$;

118

119

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

121

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

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

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

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

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

127 CREATE TRIGGER "update_text_search_data"

128 BEFORE INSERT OR UPDATE ON "direct_voter"

129 FOR EACH ROW EXECUTE PROCEDURE

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

131

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

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

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

135

136 CREATE TABLE "rendered_voter_comment" (

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

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

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

140 ON DELETE CASCADE ON UPDATE CASCADE,

141 "issue_id" INT4,

142 "member_id" INT4,

143 "format" TEXT,

144 "content" TEXT NOT NULL );

145

146 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)';

147

148 DROP TABLE "rendered_issue_comment";

149 DROP TABLE "issue_comment";

150 DROP TABLE "rendered_voting_comment";

151 DROP TABLE "voting_comment";

152

153 CREATE FUNCTION "voter_comment_fields_only_set_when_voter_comment_is_set_trigger"()

154 RETURNS TRIGGER

155 LANGUAGE 'plpgsql' VOLATILE AS $$

156 BEGIN

157 IF NEW."comment" ISNULL THEN

158 NEW."comment_changed" := NULL;

159 NEW."formatting_engine" := NULL;

160 END IF;

161 RETURN NEW;

162 END;

163 $$;

164

165 CREATE TRIGGER "voter_comment_fields_only_set_when_voter_comment_is_set"

166 BEFORE INSERT OR UPDATE ON "direct_voter"

167 FOR EACH ROW EXECUTE PROCEDURE

168 "voter_comment_fields_only_set_when_voter_comment_is_set_trigger"();

169

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

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

172

173 CREATE OR REPLACE FUNCTION "forbid_changes_on_closed_issue_trigger"()

174 RETURNS TRIGGER

175 LANGUAGE 'plpgsql' VOLATILE AS $$

176 DECLARE

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

178 "issue_row" "issue"%ROWTYPE;

179 BEGIN

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

181 IF

182 OLD."issue_id" = NEW."issue_id" AND

183 OLD."member_id" = NEW."member_id" AND

184 OLD."weight" = NEW."weight"

185 THEN

186 RETURN NULL; -- allows changing of voter comment

187 END IF;

188 END IF;

189 IF TG_OP = 'DELETE' THEN

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

191 ELSE

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

193 END IF;

194 SELECT INTO "issue_row" * FROM "issue"

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

196 IF "issue_row"."closed" NOTNULL THEN

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

198 END IF;

199 RETURN NULL;

200 END;

201 $$;

202

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

204 RETURNS VOID

205 LANGUAGE 'plpgsql' VOLATILE AS $$

206 DECLARE

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

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

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

210 BEGIN

211 PERFORM "lock_issue"("issue_id_p");

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

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

214 -- delete timestamp of voting comment:

215 UPDATE "direct_voter" SET "comment_changed" = NULL

216 WHERE "issue_id" = "issue_id_p";

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

218 DELETE FROM "delegating_voter"

219 WHERE "issue_id" = "issue_id_p";

220 -- delete votes from non-privileged voters:

221 DELETE FROM "direct_voter"

222 USING (

223 SELECT

224 "direct_voter"."member_id"

225 FROM "direct_voter"

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

227 LEFT JOIN "privilege"

228 ON "privilege"."unit_id" = "unit_id_v"

229 AND "privilege"."member_id" = "direct_voter"."member_id"

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

231 "member"."active" = FALSE OR

232 "privilege"."voting_right" ISNULL OR

233 "privilege"."voting_right" = FALSE

234 )

235 ) AS "subquery"

236 WHERE "direct_voter"."issue_id" = "issue_id_p"

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

238 -- consider delegations:

239 UPDATE "direct_voter" SET "weight" = 1

240 WHERE "issue_id" = "issue_id_p";

241 PERFORM "add_vote_delegations"("issue_id_p");

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

243 UPDATE "issue" SET

244 "state" = 'calculation',

245 "closed" = now(),

246 "voter_count" = (

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

248 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"

249 )

250 WHERE "id" = "issue_id_p";

251 -- materialize battle_view:

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

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

254 INSERT INTO "battle" (

255 "issue_id",

256 "winning_initiative_id", "losing_initiative_id",

257 "count"

258 ) SELECT

259 "issue_id",

260 "winning_initiative_id", "losing_initiative_id",

261 "count"

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

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

264 UPDATE "initiative" SET

265 "positive_votes" = "battle_win"."count",

266 "negative_votes" = "battle_lose"."count"

267 FROM "battle" AS "battle_win", "battle" AS "battle_lose"

268 WHERE

269 "battle_win"."issue_id" = "issue_id_p" AND

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

271 "battle_win"."losing_initiative_id" ISNULL AND

272 "battle_lose"."issue_id" = "issue_id_p" AND

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

274 "battle_lose"."winning_initiative_id" ISNULL;

275 END;

276 $$;

277

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

279 RETURNS VOID

280 LANGUAGE 'plpgsql' VOLATILE AS $$

281 DECLARE

282 "issue_row" "issue"%ROWTYPE;

283 BEGIN

284 SELECT * INTO "issue_row"

285 FROM "issue" WHERE "id" = "issue_id_p"

286 FOR UPDATE;

287 IF "issue_row"."cleaned" ISNULL THEN

288 UPDATE "issue" SET

289 "state" = 'voting',

290 "closed" = NULL,

291 "ranks_available" = FALSE

292 WHERE "id" = "issue_id_p";

293 DELETE FROM "delegating_voter"

294 WHERE "issue_id" = "issue_id_p";

295 DELETE FROM "direct_voter"

296 WHERE "issue_id" = "issue_id_p";

297 DELETE FROM "delegating_interest_snapshot"

298 WHERE "issue_id" = "issue_id_p";

299 DELETE FROM "direct_interest_snapshot"

300 WHERE "issue_id" = "issue_id_p";

301 DELETE FROM "delegating_population_snapshot"

302 WHERE "issue_id" = "issue_id_p";

303 DELETE FROM "direct_population_snapshot"

304 WHERE "issue_id" = "issue_id_p";

305 DELETE FROM "non_voter"

306 WHERE "issue_id" = "issue_id_p";

307 DELETE FROM "delegation"

308 WHERE "issue_id" = "issue_id_p";

309 DELETE FROM "supporter"

310 WHERE "issue_id" = "issue_id_p";

311 UPDATE "issue" SET

312 "state" = "issue_row"."state",

313 "closed" = "issue_row"."closed",

314 "ranks_available" = "issue_row"."ranks_available",

315 "cleaned" = now()

316 WHERE "id" = "issue_id_p";

317 END IF;

318 RETURN;

319 END;

320 $$;

321

322

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

324

325 CREATE FUNCTION "non_voter_deletes_direct_voter_trigger"()

326 RETURNS TRIGGER

327 LANGUAGE 'plpgsql' VOLATILE AS $$

328 BEGIN

329 DELETE FROM "direct_voter"

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

331 RETURN NULL;

332 END;

333 $$;

334

335 CREATE TRIGGER "non_voter_deletes_direct_voter"

336 AFTER INSERT OR UPDATE ON "non_voter"

337 FOR EACH ROW EXECUTE PROCEDURE

338 "non_voter_deletes_direct_voter_trigger"();

339

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

341 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")';

342

343 CREATE FUNCTION "direct_voter_deletes_non_voter_trigger"()

344 RETURNS TRIGGER

345 LANGUAGE 'plpgsql' VOLATILE AS $$

346 BEGIN

347 DELETE FROM "non_voter"

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

349 RETURN NULL;

350 END;

351 $$;

352

353 CREATE TRIGGER "direct_voter_deletes_non_voter"

354 AFTER INSERT OR UPDATE ON "direct_voter"

355 FOR EACH ROW EXECUTE PROCEDURE

356 "direct_voter_deletes_non_voter_trigger"();

357

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

359 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")';

360

361

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

363

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

365

366

367 COMMIT;