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 "initiative" ADD COLUMN "polling" BOOLEAN NOT NULL DEFAULT FALSE;

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

38

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

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

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

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

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

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

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

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

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

48

49 CREATE OR REPLACE FUNCTION "freeze_after_snapshot"

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

51 RETURNS VOID

52 LANGUAGE 'plpgsql' VOLATILE AS $$

53 DECLARE

54 "issue_row" "issue"%ROWTYPE;

55 "policy_row" "policy"%ROWTYPE;

56 "initiative_row" "initiative"%ROWTYPE;

57 BEGIN

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

59 SELECT * INTO "policy_row"

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

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

62 FOR "initiative_row" IN

63 SELECT * FROM "initiative"

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

65 LOOP

66 IF

67 "initiative_row"."polling" OR (

68 "initiative_row"."satisfied_supporter_count" > 0 AND

69 "initiative_row"."satisfied_supporter_count" *

70 "policy_row"."initiative_quorum_den" >=

71 "issue_row"."population" * "policy_row"."initiative_quorum_num"

72 )

73 THEN

74 UPDATE "initiative" SET "admitted" = TRUE

75 WHERE "id" = "initiative_row"."id";

76 ELSE

77 UPDATE "initiative" SET "admitted" = FALSE

78 WHERE "id" = "initiative_row"."id";

79 END IF;

80 END LOOP;

81 IF EXISTS (

82 SELECT NULL FROM "initiative"

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

84 ) THEN

85 UPDATE "issue" SET

86 "state" = 'voting',

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

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

89 "fully_frozen" = now()

90 WHERE "id" = "issue_id_p";

91 ELSE

92 UPDATE "issue" SET

93 "state" = 'canceled_no_initiative_admitted',

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

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

96 "fully_frozen" = now(),

97 "closed" = now(),

98 "ranks_available" = TRUE

99 WHERE "id" = "issue_id_p";

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

101 -- issue state has been manipulated

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

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

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

105 END IF;

106 RETURN;

107 END;

108 $$;

109

110

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

112

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

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

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

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

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

118 CREATE TRIGGER "update_text_search_data"

119 BEFORE INSERT OR UPDATE ON "direct_voter"

120 FOR EACH ROW EXECUTE PROCEDURE

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

122

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

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

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

126

127 CREATE TABLE "rendered_voter_comment" (

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

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

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

131 ON DELETE CASCADE ON UPDATE CASCADE,

132 "issue_id" INT4,

133 "member_id" INT4,

134 "format" TEXT,

135 "content" TEXT NOT NULL );

136

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

138

139 DROP TABLE "rendered_issue_comment";

140 DROP TABLE "issue_comment";

141 DROP TABLE "rendered_voting_comment";

142 DROP TABLE "voting_comment";

143

144 CREATE FUNCTION "voter_comment_fields_only_set_when_voter_comment_is_set_trigger"()

145 RETURNS TRIGGER

146 LANGUAGE 'plpgsql' VOLATILE AS $$

147 BEGIN

148 IF NEW."comment" ISNULL THEN

149 NEW."comment_changed" := NULL;

150 NEW."formatting_engine" := NULL;

151 END IF;

152 RETURN NEW;

153 END;

154 $$;

155

156 CREATE TRIGGER "voter_comment_fields_only_set_when_voter_comment_is_set"

157 BEFORE INSERT OR UPDATE ON "direct_voter"

158 FOR EACH ROW EXECUTE PROCEDURE

159 "voter_comment_fields_only_set_when_voter_comment_is_set_trigger"();

160

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

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

163

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

165 RETURNS VOID

166 LANGUAGE 'plpgsql' VOLATILE AS $$

167 DECLARE

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

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

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

171 BEGIN

172 PERFORM "lock_issue"("issue_id_p");

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

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

175 -- delete timestamp of voting comment:

176 UPDATE "direct_voter" SET "comment_changed" = NULL

177 WHERE "issue_id" = "issue_id_p";

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

179 DELETE FROM "delegating_voter"

180 WHERE "issue_id" = "issue_id_p";

181 -- delete votes from non-privileged voters:

182 DELETE FROM "direct_voter"

183 USING (

184 SELECT

185 "direct_voter"."member_id"

186 FROM "direct_voter"

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

188 LEFT JOIN "privilege"

189 ON "privilege"."unit_id" = "unit_id_v"

190 AND "privilege"."member_id" = "direct_voter"."member_id"

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

192 "member"."active" = FALSE OR

193 "privilege"."voting_right" ISNULL OR

194 "privilege"."voting_right" = FALSE

195 )

196 ) AS "subquery"

197 WHERE "direct_voter"."issue_id" = "issue_id_p"

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

199 -- consider delegations:

200 UPDATE "direct_voter" SET "weight" = 1

201 WHERE "issue_id" = "issue_id_p";

202 PERFORM "add_vote_delegations"("issue_id_p");

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

204 UPDATE "issue" SET

205 "state" = 'calculation',

206 "closed" = now(),

207 "voter_count" = (

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

209 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"

210 )

211 WHERE "id" = "issue_id_p";

212 -- materialize battle_view:

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

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

215 INSERT INTO "battle" (

216 "issue_id",

217 "winning_initiative_id", "losing_initiative_id",

218 "count"

219 ) SELECT

220 "issue_id",

221 "winning_initiative_id", "losing_initiative_id",

222 "count"

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

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

225 UPDATE "initiative" SET

226 "positive_votes" = "battle_win"."count",

227 "negative_votes" = "battle_lose"."count"

228 FROM "battle" AS "battle_win", "battle" AS "battle_lose"

229 WHERE

230 "battle_win"."issue_id" = "issue_id_p" AND

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

232 "battle_win"."losing_initiative_id" ISNULL AND

233 "battle_lose"."issue_id" = "issue_id_p" AND

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

235 "battle_lose"."winning_initiative_id" ISNULL;

236 END;

237 $$;

238

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

240 RETURNS VOID

241 LANGUAGE 'plpgsql' VOLATILE AS $$

242 DECLARE

243 "issue_row" "issue"%ROWTYPE;

244 BEGIN

245 SELECT * INTO "issue_row"

246 FROM "issue" WHERE "id" = "issue_id_p"

247 FOR UPDATE;

248 IF "issue_row"."cleaned" ISNULL THEN

249 UPDATE "issue" SET

250 "state" = 'voting',

251 "closed" = NULL,

252 "ranks_available" = FALSE

253 WHERE "id" = "issue_id_p";

254 DELETE FROM "delegating_voter"

255 WHERE "issue_id" = "issue_id_p";

256 DELETE FROM "direct_voter"

257 WHERE "issue_id" = "issue_id_p";

258 DELETE FROM "delegating_interest_snapshot"

259 WHERE "issue_id" = "issue_id_p";

260 DELETE FROM "direct_interest_snapshot"

261 WHERE "issue_id" = "issue_id_p";

262 DELETE FROM "delegating_population_snapshot"

263 WHERE "issue_id" = "issue_id_p";

264 DELETE FROM "direct_population_snapshot"

265 WHERE "issue_id" = "issue_id_p";

266 DELETE FROM "non_voter"

267 WHERE "issue_id" = "issue_id_p";

268 DELETE FROM "delegation"

269 WHERE "issue_id" = "issue_id_p";

270 DELETE FROM "supporter"

271 WHERE "issue_id" = "issue_id_p";

272 UPDATE "issue" SET

273 "state" = "issue_row"."state",

274 "closed" = "issue_row"."closed",

275 "ranks_available" = "issue_row"."ranks_available",

276 "cleaned" = now()

277 WHERE "id" = "issue_id_p";

278 END IF;

279 RETURN;

280 END;

281 $$;

282

283

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

285

286 CREATE FUNCTION "non_voter_deletes_direct_voter_trigger"()

287 RETURNS TRIGGER

288 LANGUAGE 'plpgsql' VOLATILE AS $$

289 BEGIN

290 DELETE FROM "direct_voter"

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

292 RETURN NULL;

293 END;

294 $$;

295

296 CREATE TRIGGER "non_voter_deletes_direct_voter"

297 AFTER INSERT OR UPDATE ON "non_voter"

298 FOR EACH ROW EXECUTE PROCEDURE

299 "non_voter_deletes_direct_voter_trigger"();

300

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

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

303

304 CREATE FUNCTION "direct_voter_deletes_non_voter_trigger"()

305 RETURNS TRIGGER

306 LANGUAGE 'plpgsql' VOLATILE AS $$

307 BEGIN

308 DELETE FROM "non_voter"

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

310 RETURN NULL;

311 END;

312 $$;

313

314 CREATE TRIGGER "direct_voter_deletes_non_voter"

315 AFTER INSERT OR UPDATE ON "direct_voter"

316 FOR EACH ROW EXECUTE PROCEDURE

317 "direct_voter_deletes_non_voter_trigger"();

318

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

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

321

322

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

324

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

326

327

328 COMMIT;