1 BEGIN;

2

3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS

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

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

6

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

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

9

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

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

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

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

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

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

16 ( "polling" = FALSE AND

17 "admission_time" NOTNULL AND "discussion_time" NOTNULL AND

18 "verification_time" NOTNULL AND "voting_time" NOTNULL ) OR

19 ( "polling" = TRUE AND

20 "admission_time" ISNULL AND "discussion_time" NOTNULL AND

21 "verification_time" NOTNULL AND "voting_time" NOTNULL ) OR

22 ( "polling" = TRUE AND

23 "admission_time" ISNULL AND "discussion_time" ISNULL AND

24 "verification_time" ISNULL AND "voting_time" ISNULL ) );

25 COMMENT ON COLUMN "policy"."polling" IS 'TRUE = special policy for non-user-generated issues, i.e. polls ("admission_time" MUST be set to NULL, the other timings may be set to NULL altogether, allowing individual timing for issues)';

26

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

28 COMMENT ON COLUMN "initiative"."polling" IS 'Initiative is an option for a poll (see "policy"."polling"), and does not need to pass the initiative quorum';

29

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

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

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

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

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

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

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

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

38 COMMENT ON COLUMN "privilege"."polling_right" IS 'Right to create polls (see "policy"."polling" and "initiative"."polling")';

39

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

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

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

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

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

45 CREATE TRIGGER "update_text_search_data"

46 BEFORE INSERT OR UPDATE ON "direct_voter"

47 FOR EACH ROW EXECUTE PROCEDURE

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

49

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

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

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

53

54 CREATE TABLE "rendered_voter_comment" (

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

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

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

58 ON DELETE CASCADE ON UPDATE CASCADE,

59 "issue_id" INT4,

60 "member_id" INT4,

61 "format" TEXT,

62 "content" TEXT NOT NULL );

63

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

65

66

67 DROP TABLE "rendered_issue_comment";

68 DROP TABLE "issue_comment";

69 DROP TABLE "rendered_voting_comment";

70 DROP TABLE "voting_comment";

71

72 CREATE FUNCTION "non_voter_deletes_direct_voter_trigger"()

73 RETURNS TRIGGER

74 LANGUAGE 'plpgsql' VOLATILE AS $$

75 BEGIN

76 DELETE FROM "direct_voter"

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

78 RETURN NULL;

79 END;

80 $$;

81

82 CREATE TRIGGER "non_voter_deletes_direct_voter"

83 AFTER INSERT OR UPDATE ON "non_voter"

84 FOR EACH ROW EXECUTE PROCEDURE

85 "non_voter_deletes_direct_voter_trigger"();

86

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

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

89

90 CREATE FUNCTION "direct_voter_deletes_non_voter_trigger"()

91 RETURNS TRIGGER

92 LANGUAGE 'plpgsql' VOLATILE AS $$

93 BEGIN

94 DELETE FROM "non_voter"

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

96 RETURN NULL;

97 END;

98 $$;

99

100 CREATE TRIGGER "direct_voter_deletes_non_voter"

101 AFTER INSERT OR UPDATE ON "direct_voter"

102 FOR EACH ROW EXECUTE PROCEDURE

103 "direct_voter_deletes_non_voter_trigger"();

104

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

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

107

108 CREATE FUNCTION "voter_comment_fields_only_set_when_voter_comment_is_set_trigger"()

109 RETURNS TRIGGER

110 LANGUAGE 'plpgsql' VOLATILE AS $$

111 BEGIN

112 IF NEW."comment" ISNULL THEN

113 NEW."comment_changed" := NULL;

114 NEW."formatting_engine" := NULL;

115 END IF;

116 RETURN NEW;

117 END;

118 $$;

119

120 CREATE TRIGGER "voter_comment_fields_only_set_when_voter_comment_is_set"

121 BEFORE INSERT OR UPDATE ON "direct_voter"

122 FOR EACH ROW EXECUTE PROCEDURE

123 "voter_comment_fields_only_set_when_voter_comment_is_set_trigger"();

124

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

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

127

128 CREATE OR REPLACE FUNCTION "freeze_after_snapshot"

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

130 RETURNS VOID

131 LANGUAGE 'plpgsql' VOLATILE AS $$

132 DECLARE

133 "issue_row" "issue"%ROWTYPE;

134 "policy_row" "policy"%ROWTYPE;

135 "initiative_row" "initiative"%ROWTYPE;

136 BEGIN

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

138 SELECT * INTO "policy_row"

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

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

141 FOR "initiative_row" IN

142 SELECT * FROM "initiative"

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

144 LOOP

145 IF

146 "initiative_row"."polling" OR (

147 "initiative_row"."satisfied_supporter_count" > 0 AND

148 "initiative_row"."satisfied_supporter_count" *

149 "policy_row"."initiative_quorum_den" >=

150 "issue_row"."population" * "policy_row"."initiative_quorum_num"

151 )

152 THEN

153 UPDATE "initiative" SET "admitted" = TRUE

154 WHERE "id" = "initiative_row"."id";

155 ELSE

156 UPDATE "initiative" SET "admitted" = FALSE

157 WHERE "id" = "initiative_row"."id";

158 END IF;

159 END LOOP;

160 IF EXISTS (

161 SELECT NULL FROM "initiative"

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

163 ) THEN

164 UPDATE "issue" SET

165 "state" = 'voting',

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

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

168 "fully_frozen" = now()

169 WHERE "id" = "issue_id_p";

170 ELSE

171 UPDATE "issue" SET

172 "state" = 'canceled_no_initiative_admitted',

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

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

175 "fully_frozen" = now(),

176 "closed" = now(),

177 "ranks_available" = TRUE

178 WHERE "id" = "issue_id_p";

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

180 -- issue state has been manipulated

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

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

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

184 END IF;

185 RETURN;

186 END;

187 $$;

188

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

190 RETURNS VOID

191 LANGUAGE 'plpgsql' VOLATILE AS $$

192 DECLARE

193 "issue_row" "issue"%ROWTYPE;

194 BEGIN

195 SELECT * INTO "issue_row"

196 FROM "issue" WHERE "id" = "issue_id_p"

197 FOR UPDATE;

198 IF "issue_row"."cleaned" ISNULL THEN

199 UPDATE "issue" SET

200 "state" = 'voting',

201 "closed" = NULL,

202 "ranks_available" = FALSE

203 WHERE "id" = "issue_id_p";

204 DELETE FROM "delegating_voter"

205 WHERE "issue_id" = "issue_id_p";

206 DELETE FROM "direct_voter"

207 WHERE "issue_id" = "issue_id_p";

208 DELETE FROM "delegating_interest_snapshot"

209 WHERE "issue_id" = "issue_id_p";

210 DELETE FROM "direct_interest_snapshot"

211 WHERE "issue_id" = "issue_id_p";

212 DELETE FROM "delegating_population_snapshot"

213 WHERE "issue_id" = "issue_id_p";

214 DELETE FROM "direct_population_snapshot"

215 WHERE "issue_id" = "issue_id_p";

216 DELETE FROM "non_voter"

217 WHERE "issue_id" = "issue_id_p";

218 DELETE FROM "delegation"

219 WHERE "issue_id" = "issue_id_p";

220 DELETE FROM "supporter"

221 WHERE "issue_id" = "issue_id_p";

222 UPDATE "issue" SET

223 "state" = "issue_row"."state",

224 "closed" = "issue_row"."closed",

225 "ranks_available" = "issue_row"."ranks_available",

226 "cleaned" = now()

227 WHERE "id" = "issue_id_p";

228 END IF;

229 RETURN;

230 END;

231 $$;

232

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

234 RETURNS VOID

235 LANGUAGE 'plpgsql' VOLATILE AS $$

236 DECLARE

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

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

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

240 BEGIN

241 PERFORM "lock_issue"("issue_id_p");

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

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

244 -- delete timestamp of voting comment:

245 UPDATE "direct_voter" SET "comment_changed" = NULL

246 WHERE "issue_id" = "issue_id_p";

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

248 DELETE FROM "delegating_voter"

249 WHERE "issue_id" = "issue_id_p";

250 -- delete votes from non-privileged voters:

251 DELETE FROM "direct_voter"

252 USING (

253 SELECT

254 "direct_voter"."member_id"

255 FROM "direct_voter"

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

257 LEFT JOIN "privilege"

258 ON "privilege"."unit_id" = "unit_id_v"

259 AND "privilege"."member_id" = "direct_voter"."member_id"

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

261 "member"."active" = FALSE OR

262 "privilege"."voting_right" ISNULL OR

263 "privilege"."voting_right" = FALSE

264 )

265 ) AS "subquery"

266 WHERE "direct_voter"."issue_id" = "issue_id_p"

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

268 -- consider delegations:

269 UPDATE "direct_voter" SET "weight" = 1

270 WHERE "issue_id" = "issue_id_p";

271 PERFORM "add_vote_delegations"("issue_id_p");

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

273 UPDATE "issue" SET

274 "state" = 'calculation',

275 "closed" = now(),

276 "voter_count" = (

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

278 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"

279 )

280 WHERE "id" = "issue_id_p";

281 -- materialize battle_view:

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

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

284 INSERT INTO "battle" (

285 "issue_id",

286 "winning_initiative_id", "losing_initiative_id",

287 "count"

288 ) SELECT

289 "issue_id",

290 "winning_initiative_id", "losing_initiative_id",

291 "count"

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

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

294 UPDATE "initiative" SET

295 "positive_votes" = "battle_win"."count",

296 "negative_votes" = "battle_lose"."count"

297 FROM "battle" AS "battle_win", "battle" AS "battle_lose"

298 WHERE

299 "battle_win"."issue_id" = "issue_id_p" AND

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

301 "battle_win"."losing_initiative_id" ISNULL AND

302 "battle_lose"."issue_id" = "issue_id_p" AND

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

304 "battle_lose"."winning_initiative_id" ISNULL;

305 END;

306 $$;

307

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

309

310 COMMIT;