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" ADD CONSTRAINT "timing" CHECK (

25 ( "polling" = FALSE AND

26 "admission_time" NOTNULL AND "discussion_time" NOTNULL AND

27 "verification_time" NOTNULL AND "voting_time" NOTNULL ) OR

28 ( "polling" = TRUE AND

29 "admission_time" ISNULL AND "discussion_time" NOTNULL AND

30 "verification_time" NOTNULL AND "voting_time" NOTNULL ) OR

31 ( "polling" = TRUE AND

32 "admission_time" ISNULL AND "discussion_time" ISNULL AND

33 "verification_time" ISNULL AND "voting_time" ISNULL ) );

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

35

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

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

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

39

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

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

42

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

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

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

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

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

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

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

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

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

52

53 CREATE OR REPLACE FUNCTION "freeze_after_snapshot"

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

55 RETURNS VOID

56 LANGUAGE 'plpgsql' VOLATILE AS $$

57 DECLARE

58 "issue_row" "issue"%ROWTYPE;

59 "policy_row" "policy"%ROWTYPE;

60 "initiative_row" "initiative"%ROWTYPE;

61 BEGIN

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

63 SELECT * INTO "policy_row"

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

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

66 FOR "initiative_row" IN

67 SELECT * FROM "initiative"

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

69 LOOP

70 IF

71 "initiative_row"."polling" OR (

72 "initiative_row"."satisfied_supporter_count" > 0 AND

73 "initiative_row"."satisfied_supporter_count" *

74 "policy_row"."initiative_quorum_den" >=

75 "issue_row"."population" * "policy_row"."initiative_quorum_num"

76 )

77 THEN

78 UPDATE "initiative" SET "admitted" = TRUE

79 WHERE "id" = "initiative_row"."id";

80 ELSE

81 UPDATE "initiative" SET "admitted" = FALSE

82 WHERE "id" = "initiative_row"."id";

83 END IF;

84 END LOOP;

85 IF EXISTS (

86 SELECT NULL FROM "initiative"

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

88 ) THEN

89 UPDATE "issue" SET

90 "state" = 'voting',

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

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

93 "fully_frozen" = now()

94 WHERE "id" = "issue_id_p";

95 ELSE

96 UPDATE "issue" SET

97 "state" = 'canceled_no_initiative_admitted',

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

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

100 "fully_frozen" = now(),

101 "closed" = now(),

102 "ranks_available" = TRUE

103 WHERE "id" = "issue_id_p";

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

105 -- issue state has been manipulated

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

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

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

109 END IF;

110 RETURN;

111 END;

112 $$;

113

114

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

116

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

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

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

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

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

122 CREATE TRIGGER "update_text_search_data"

123 BEFORE INSERT OR UPDATE ON "direct_voter"

124 FOR EACH ROW EXECUTE PROCEDURE

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

126

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

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

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

130

131 CREATE TABLE "rendered_voter_comment" (

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

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

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

135 ON DELETE CASCADE ON UPDATE CASCADE,

136 "issue_id" INT4,

137 "member_id" INT4,

138 "format" TEXT,

139 "content" TEXT NOT NULL );

140

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

142

143 DROP TABLE "rendered_issue_comment";

144 DROP TABLE "issue_comment";

145 DROP TABLE "rendered_voting_comment";

146 DROP TABLE "voting_comment";

147

148 CREATE FUNCTION "voter_comment_fields_only_set_when_voter_comment_is_set_trigger"()

149 RETURNS TRIGGER

150 LANGUAGE 'plpgsql' VOLATILE AS $$

151 BEGIN

152 IF NEW."comment" ISNULL THEN

153 NEW."comment_changed" := NULL;

154 NEW."formatting_engine" := NULL;

155 END IF;

156 RETURN NEW;

157 END;

158 $$;

159

160 CREATE TRIGGER "voter_comment_fields_only_set_when_voter_comment_is_set"

161 BEFORE INSERT OR UPDATE ON "direct_voter"

162 FOR EACH ROW EXECUTE PROCEDURE

163 "voter_comment_fields_only_set_when_voter_comment_is_set_trigger"();

164

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

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

167

168 CREATE OR REPLACE FUNCTION "forbid_changes_on_closed_issue_trigger"()

169 RETURNS TRIGGER

170 LANGUAGE 'plpgsql' VOLATILE AS $$

171 DECLARE

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

173 "issue_row" "issue"%ROWTYPE;

174 BEGIN

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

176 IF

177 OLD."issue_id" = NEW."issue_id" AND

178 OLD."member_id" = NEW."member_id" AND

179 OLD."weight" = NEW."weight"

180 THEN

181 RETURN NULL; -- allows changing of voter comment

182 END IF;

183 END IF;

184 IF TG_OP = 'DELETE' THEN

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

186 ELSE

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

188 END IF;

189 SELECT INTO "issue_row" * FROM "issue"

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

191 IF "issue_row"."closed" NOTNULL THEN

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

193 END IF;

194 RETURN NULL;

195 END;

196 $$;

197

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

199 RETURNS VOID

200 LANGUAGE 'plpgsql' VOLATILE AS $$

201 DECLARE

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

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

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

205 BEGIN

206 PERFORM "lock_issue"("issue_id_p");

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

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

209 -- delete timestamp of voting comment:

210 UPDATE "direct_voter" SET "comment_changed" = NULL

211 WHERE "issue_id" = "issue_id_p";

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

213 DELETE FROM "delegating_voter"

214 WHERE "issue_id" = "issue_id_p";

215 -- delete votes from non-privileged voters:

216 DELETE FROM "direct_voter"

217 USING (

218 SELECT

219 "direct_voter"."member_id"

220 FROM "direct_voter"

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

222 LEFT JOIN "privilege"

223 ON "privilege"."unit_id" = "unit_id_v"

224 AND "privilege"."member_id" = "direct_voter"."member_id"

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

226 "member"."active" = FALSE OR

227 "privilege"."voting_right" ISNULL OR

228 "privilege"."voting_right" = FALSE

229 )

230 ) AS "subquery"

231 WHERE "direct_voter"."issue_id" = "issue_id_p"

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

233 -- consider delegations:

234 UPDATE "direct_voter" SET "weight" = 1

235 WHERE "issue_id" = "issue_id_p";

236 PERFORM "add_vote_delegations"("issue_id_p");

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

238 UPDATE "issue" SET

239 "state" = 'calculation',

240 "closed" = now(),

241 "voter_count" = (

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

243 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"

244 )

245 WHERE "id" = "issue_id_p";

246 -- materialize battle_view:

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

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

249 INSERT INTO "battle" (

250 "issue_id",

251 "winning_initiative_id", "losing_initiative_id",

252 "count"

253 ) SELECT

254 "issue_id",

255 "winning_initiative_id", "losing_initiative_id",

256 "count"

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

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

259 UPDATE "initiative" SET

260 "positive_votes" = "battle_win"."count",

261 "negative_votes" = "battle_lose"."count"

262 FROM "battle" AS "battle_win", "battle" AS "battle_lose"

263 WHERE

264 "battle_win"."issue_id" = "issue_id_p" AND

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

266 "battle_win"."losing_initiative_id" ISNULL AND

267 "battle_lose"."issue_id" = "issue_id_p" AND

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

269 "battle_lose"."winning_initiative_id" ISNULL;

270 END;

271 $$;

272

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

274 RETURNS VOID

275 LANGUAGE 'plpgsql' VOLATILE AS $$

276 DECLARE

277 "issue_row" "issue"%ROWTYPE;

278 BEGIN

279 SELECT * INTO "issue_row"

280 FROM "issue" WHERE "id" = "issue_id_p"

281 FOR UPDATE;

282 IF "issue_row"."cleaned" ISNULL THEN

283 UPDATE "issue" SET

284 "state" = 'voting',

285 "closed" = NULL,

286 "ranks_available" = FALSE

287 WHERE "id" = "issue_id_p";

288 DELETE FROM "delegating_voter"

289 WHERE "issue_id" = "issue_id_p";

290 DELETE FROM "direct_voter"

291 WHERE "issue_id" = "issue_id_p";

292 DELETE FROM "delegating_interest_snapshot"

293 WHERE "issue_id" = "issue_id_p";

294 DELETE FROM "direct_interest_snapshot"

295 WHERE "issue_id" = "issue_id_p";

296 DELETE FROM "delegating_population_snapshot"

297 WHERE "issue_id" = "issue_id_p";

298 DELETE FROM "direct_population_snapshot"

299 WHERE "issue_id" = "issue_id_p";

300 DELETE FROM "non_voter"

301 WHERE "issue_id" = "issue_id_p";

302 DELETE FROM "delegation"

303 WHERE "issue_id" = "issue_id_p";

304 DELETE FROM "supporter"

305 WHERE "issue_id" = "issue_id_p";

306 UPDATE "issue" SET

307 "state" = "issue_row"."state",

308 "closed" = "issue_row"."closed",

309 "ranks_available" = "issue_row"."ranks_available",

310 "cleaned" = now()

311 WHERE "id" = "issue_id_p";

312 END IF;

313 RETURN;

314 END;

315 $$;

316

317

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

319

320 CREATE FUNCTION "non_voter_deletes_direct_voter_trigger"()

321 RETURNS TRIGGER

322 LANGUAGE 'plpgsql' VOLATILE AS $$

323 BEGIN

324 DELETE FROM "direct_voter"

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

326 RETURN NULL;

327 END;

328 $$;

329

330 CREATE TRIGGER "non_voter_deletes_direct_voter"

331 AFTER INSERT OR UPDATE ON "non_voter"

332 FOR EACH ROW EXECUTE PROCEDURE

333 "non_voter_deletes_direct_voter_trigger"();

334

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

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

337

338 CREATE FUNCTION "direct_voter_deletes_non_voter_trigger"()

339 RETURNS TRIGGER

340 LANGUAGE 'plpgsql' VOLATILE AS $$

341 BEGIN

342 DELETE FROM "non_voter"

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

344 RETURN NULL;

345 END;

346 $$;

347

348 CREATE TRIGGER "direct_voter_deletes_non_voter"

349 AFTER INSERT OR UPDATE ON "direct_voter"

350 FOR EACH ROW EXECUTE PROCEDURE

351 "direct_voter_deletes_non_voter_trigger"();

352

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

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

355

356

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

358

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

360

361

362 COMMIT;