1 BEGIN;

2

3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS

4 SELECT * FROM (VALUES ('2.1.1', 2, 1, 1))

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

6

7 ALTER TABLE "initiative" ADD COLUMN "harmonic_weight" NUMERIC(12, 3);

8 COMMENT ON COLUMN "initiative"."harmonic_weight" IS 'Indicates the relevancy of the initiative, calculated from the potential supporters weighted with the harmonic series to avoid a large number of clones affecting other initiative''s sorting positions too much; shall be used as secondary sorting key after "admitted" as primary sorting key';

9

10 ALTER TABLE "suggestion" ADD COLUMN "harmonic_weight" NUMERIC(12, 3);

11 COMMENT ON COLUMN "suggestion"."harmonic_weight" IS 'Indicates the relevancy of the suggestion, calculated from the supporters (positive "degree") of the suggestion weighted with the harmonic series to avoid a large number of clones affecting other suggestion''s sortings position too much';

12

13 CREATE VIEW "remaining_harmonic_supporter_weight" AS

14 SELECT

15 "direct_interest_snapshot"."issue_id",

16 "direct_interest_snapshot"."event",

17 "direct_interest_snapshot"."member_id",

18 "direct_interest_snapshot"."weight" AS "weight_num",

19 count("initiative"."id") AS "weight_den"

20 FROM "issue"

21 JOIN "direct_interest_snapshot"

22 ON "issue"."id" = "direct_interest_snapshot"."issue_id"

23 AND "issue"."latest_snapshot_event" = "direct_interest_snapshot"."event"

24 JOIN "direct_supporter_snapshot"

25 ON "direct_interest_snapshot"."issue_id" = "direct_supporter_snapshot"."issue_id"

26 AND "direct_interest_snapshot"."event" = "direct_supporter_snapshot"."event"

27 AND "direct_interest_snapshot"."member_id" = "direct_supporter_snapshot"."member_id"

28 JOIN "initiative"

29 ON "direct_supporter_snapshot"."initiative_id" = "initiative"."id"

30 AND (

31 "direct_supporter_snapshot"."satisfied" = TRUE OR

32 coalesce("initiative"."admitted", FALSE) = FALSE

33 )

34 AND "initiative"."harmonic_weight" ISNULL

35 GROUP BY

36 "direct_interest_snapshot"."issue_id",

37 "direct_interest_snapshot"."event",

38 "direct_interest_snapshot"."member_id",

39 "direct_interest_snapshot"."weight";

40

41 COMMENT ON VIEW "remaining_harmonic_supporter_weight" IS 'Helper view for function "set_harmonic_initiative_weights"';

42

43 CREATE VIEW "remaining_harmonic_initiative_weight_summands" AS

44 SELECT

45 "initiative"."issue_id",

46 "initiative"."id" AS "initiative_id",

47 "initiative"."admitted",

48 sum("remaining_harmonic_supporter_weight"."weight_num") AS "weight_num",

49 "remaining_harmonic_supporter_weight"."weight_den"

50 FROM "remaining_harmonic_supporter_weight"

51 JOIN "direct_supporter_snapshot"

52 ON "remaining_harmonic_supporter_weight"."issue_id" = "direct_supporter_snapshot"."issue_id"

53 AND "remaining_harmonic_supporter_weight"."event" = "direct_supporter_snapshot"."event"

54 AND "remaining_harmonic_supporter_weight"."member_id" = "direct_supporter_snapshot"."member_id"

55 JOIN "initiative"

56 ON "direct_supporter_snapshot"."initiative_id" = "initiative"."id"

57 AND (

58 "direct_supporter_snapshot"."satisfied" = TRUE OR

59 coalesce("initiative"."admitted", FALSE) = FALSE

60 )

61 AND "initiative"."harmonic_weight" ISNULL

62 GROUP BY

63 "initiative"."issue_id",

64 "initiative"."id",

65 "initiative"."admitted",

66 "remaining_harmonic_supporter_weight"."weight_den";

67

68 COMMENT ON VIEW "remaining_harmonic_initiative_weight_summands" IS 'Helper view for function "set_harmonic_initiative_weights"';

69

70 CREATE FUNCTION "set_harmonic_initiative_weights"

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

72 RETURNS VOID

73 LANGUAGE 'plpgsql' VOLATILE AS $$

74 DECLARE

75 "weight_row" "remaining_harmonic_initiative_weight_summands"%ROWTYPE;

76 "i" INT4;

77 "count_v" INT4;

78 "summand_v" FLOAT;

79 "id_ary" INT4[];

80 "weight_ary" FLOAT[];

81 "min_weight_v" FLOAT;

82 BEGIN

83 UPDATE "initiative" SET "harmonic_weight" = NULL

84 WHERE "issue_id" = "issue_id_p";

85 LOOP

86 "min_weight_v" := NULL;

87 "i" := 0;

88 "count_v" := 0;

89 FOR "weight_row" IN

90 SELECT * FROM "remaining_harmonic_initiative_weight_summands"

91 WHERE "issue_id" = "issue_id_p"

92 AND (

93 coalesce("admitted", FALSE) = FALSE OR NOT EXISTS (

94 SELECT NULL FROM "initiative"

95 WHERE "issue_id" = "issue_id_p"

96 AND "harmonic_weight" ISNULL

97 AND coalesce("admitted", FALSE) = FALSE

98 )

99 )

100 ORDER BY "initiative_id" DESC, "weight_den" DESC

101 -- NOTE: non-admitted initiatives placed first (at last positions),

102 -- latest initiatives treated worse in case of tie

103 LOOP

104 "summand_v" := "weight_row"."weight_num"::FLOAT / "weight_row"."weight_den"::FLOAT;

105 IF "i" = 0 OR "weight_row"."initiative_id" != "id_ary"["i"] THEN

106 "i" := "i" + 1;

107 "count_v" := "i";

108 "id_ary"["i"] := "weight_row"."initiative_id";

109 "weight_ary"["i"] := "summand_v";

110 ELSE

111 "weight_ary"["i"] := "weight_ary"["i"] + "summand_v";

112 END IF;

113 END LOOP;

114 EXIT WHEN "count_v" = 0;

115 "i" := 1;

116 LOOP

117 "weight_ary"["i"] := "weight_ary"["i"]::NUMERIC(18,9)::NUMERIC(12,3);

118 IF "min_weight_v" ISNULL OR "weight_ary"["i"] < "min_weight_v" THEN

119 "min_weight_v" := "weight_ary"["i"];

120 END IF;

121 "i" := "i" + 1;

122 EXIT WHEN "i" > "count_v";

123 END LOOP;

124 "i" := 1;

125 LOOP

126 IF "weight_ary"["i"] = "min_weight_v" THEN

127 UPDATE "initiative" SET "harmonic_weight" = "min_weight_v"

128 WHERE "id" = "id_ary"["i"];

129 EXIT;

130 END IF;

131 "i" := "i" + 1;

132 END LOOP;

133 END LOOP;

134 UPDATE "initiative" SET "harmonic_weight" = 0

135 WHERE "issue_id" = "issue_id_p" AND "harmonic_weight" ISNULL;

136 END;

137 $$;

138

139 COMMENT ON FUNCTION "set_harmonic_initiative_weights"

140 ( "issue"."id"%TYPE )

141 IS 'Calculates and sets "harmonic_weight" of initiatives in a given issue';

142

143 CREATE OR REPLACE FUNCTION "manual_freeze"("issue_id_p" "issue"."id"%TYPE)

144 RETURNS VOID

145 LANGUAGE 'plpgsql' VOLATILE AS $$

146 DECLARE

147 "issue_row" "issue"%ROWTYPE;

148 BEGIN

149 PERFORM "create_snapshot"("issue_id_p");

150 PERFORM "freeze_after_snapshot"("issue_id_p");

151 PERFORM "set_harmonic_initiative_weights"("issue_id_p");

152 RETURN;

153 END;

154 $$;

155

156 CREATE OR REPLACE FUNCTION "check_issue"

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

158 RETURNS VOID

159 LANGUAGE 'plpgsql' VOLATILE AS $$

160 DECLARE

161 "issue_row" "issue"%ROWTYPE;

162 "policy_row" "policy"%ROWTYPE;

163 "new_snapshot_v" BOOLEAN;

164 BEGIN

165 PERFORM "lock_issue"("issue_id_p");

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

167 -- only process open issues:

168 IF "issue_row"."closed" ISNULL THEN

169 SELECT * INTO "policy_row" FROM "policy"

170 WHERE "id" = "issue_row"."policy_id";

171 -- create a snapshot, unless issue is already fully frozen:

172 IF "issue_row"."fully_frozen" ISNULL THEN

173 PERFORM "create_snapshot"("issue_id_p");

174 "new_snapshot_v" := TRUE;

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

176 ELSE

177 "new_snapshot_v" := FALSE;

178 END IF;

179 -- eventually close or accept issues, which have not been accepted:

180 IF "issue_row"."accepted" ISNULL THEN

181 IF EXISTS (

182 SELECT NULL FROM "initiative"

183 WHERE "issue_id" = "issue_id_p"

184 AND "supporter_count" > 0

185 AND "supporter_count" * "policy_row"."issue_quorum_den"

186 >= "issue_row"."population" * "policy_row"."issue_quorum_num"

187 ) THEN

188 -- accept issues, if supporter count is high enough

189 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');

190 -- NOTE: "issue_row" used later

191 "issue_row"."state" := 'discussion';

192 "issue_row"."accepted" := now();

193 UPDATE "issue" SET

194 "state" = "issue_row"."state",

195 "accepted" = "issue_row"."accepted"

196 WHERE "id" = "issue_row"."id";

197 ELSIF

198 now() >= "issue_row"."created" + "issue_row"."admission_time"

199 THEN

200 -- close issues, if admission time has expired

201 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');

202 UPDATE "issue" SET

203 "state" = 'canceled_issue_not_accepted',

204 "closed" = now()

205 WHERE "id" = "issue_row"."id";

206 END IF;

207 END IF;

208 -- eventually half freeze issues:

209 IF

210 -- NOTE: issue can't be closed at this point, if it has been accepted

211 "issue_row"."accepted" NOTNULL AND

212 "issue_row"."half_frozen" ISNULL

213 THEN

214 IF

215 now() >= "issue_row"."accepted" + "issue_row"."discussion_time"

216 THEN

217 PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze');

218 -- NOTE: "issue_row" used later

219 "issue_row"."state" := 'verification';

220 "issue_row"."half_frozen" := now();

221 UPDATE "issue" SET

222 "state" = "issue_row"."state",

223 "half_frozen" = "issue_row"."half_frozen"

224 WHERE "id" = "issue_row"."id";

225 END IF;

226 END IF;

227 -- close issues after some time, if all initiatives have been revoked:

228 IF

229 "issue_row"."closed" ISNULL AND

230 NOT EXISTS (

231 -- all initiatives are revoked

232 SELECT NULL FROM "initiative"

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

234 ) AND (

235 -- and issue has not been accepted yet

236 "issue_row"."accepted" ISNULL OR

237 NOT EXISTS (

238 -- or no initiatives have been revoked lately

239 SELECT NULL FROM "initiative"

240 WHERE "issue_id" = "issue_id_p"

241 AND now() < "revoked" + "issue_row"."verification_time"

242 ) OR (

243 -- or verification time has elapsed

244 "issue_row"."half_frozen" NOTNULL AND

245 "issue_row"."fully_frozen" ISNULL AND

246 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"

247 )

248 )

249 THEN

250 -- NOTE: "issue_row" used later

251 IF "issue_row"."accepted" ISNULL THEN

252 "issue_row"."state" := 'canceled_revoked_before_accepted';

253 ELSIF "issue_row"."half_frozen" ISNULL THEN

254 "issue_row"."state" := 'canceled_after_revocation_during_discussion';

255 ELSE

256 "issue_row"."state" := 'canceled_after_revocation_during_verification';

257 END IF;

258 "issue_row"."closed" := now();

259 UPDATE "issue" SET

260 "state" = "issue_row"."state",

261 "closed" = "issue_row"."closed"

262 WHERE "id" = "issue_row"."id";

263 END IF;

264 -- fully freeze issue after verification time:

265 IF

266 "issue_row"."half_frozen" NOTNULL AND

267 "issue_row"."fully_frozen" ISNULL AND

268 "issue_row"."closed" ISNULL AND

269 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"

270 THEN

271 PERFORM "freeze_after_snapshot"("issue_id_p");

272 -- NOTE: "issue" might change, thus "issue_row" has to be updated below

273 END IF;

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

275 -- close issue by calling close_voting(...) after voting time:

276 IF

277 "issue_row"."closed" ISNULL AND

278 "issue_row"."fully_frozen" NOTNULL AND

279 now() >= "issue_row"."fully_frozen" + "issue_row"."voting_time"

280 THEN

281 PERFORM "close_voting"("issue_id_p");

282 -- calculate ranks will not consume much time and can be done now

283 PERFORM "calculate_ranks"("issue_id_p");

284 END IF;

285 -- if a new shapshot has been created, then recalculate harmonic weights:

286 IF "new_snapshot_v" THEN

287 PERFORM "set_harmonic_initiative_weights"("issue_id_p");

288 END IF;

289 END IF;

290 RETURN;

291 END;

292 $$;

293

294 SELECT "set_harmonic_initiative_weights"("id") FROM "issue";

295

296 COMMIT;