liquid_feedback_core: fd58f487e1d0 update/core-update.v2.1.0-v2.1.1.sql

liquid_feedback_core

view update/core-update.v2.1.0-v2.1.1.sql @ 322:fd58f487e1d0

Calculate "harmonc_weight" after all other calculations in an issue (as calculation depends on the "admitted" flag now)

author	jbe
date	Fri Feb 08 19:20:17 2013 +0100 (2013-02-08)
parents	48a5036d5eb1
children	4c7a864829b0

line source

1 BEGIN;

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

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

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

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

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

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

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

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 VIEW "remaining_harmonic_opinion_weight" AS

144 SELECT

145 "initiative"."issue_id",

146 "opinion"."initiative_id",

147 "direct_interest_snapshot"."member_id",

148 "direct_interest_snapshot"."weight" AS "weight_num",

149 count("opinion"."suggestion_id") AS "weight_den"

150 FROM "issue"

151 JOIN "direct_interest_snapshot"

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

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

154 JOIN "initiative"

155 ON "direct_interest_snapshot"."issue_id" = "initiative"."issue_id"

156 JOIN "opinion"

157 ON "direct_interest_snapshot"."member_id" = "opinion"."member_id"

158 AND "initiative"."id" = "opinion"."initiative_id"

159 AND (

160 ("opinion"."degree" > 0 AND "opinion"."fulfilled" = FALSE) OR

161 ("opinion"."degree" < 0 AND "opinion"."fulfilled" = TRUE)

162 )

163 JOIN "suggestion"

164 ON "opinion"."suggestion_id" = "suggestion"."id"

165 AND "suggestion"."harmonic_weight" ISNULL

166 GROUP BY

167 "initiative"."issue_id",

168 "opinion"."initiative_id",

169 "direct_interest_snapshot"."member_id",

170 "direct_interest_snapshot"."weight";

171

172 COMMENT ON VIEW "remaining_harmonic_opinion_weight" IS 'Helper view for function "set_harmonic_suggestion_weights"';

173

174 CREATE VIEW "remaining_harmonic_suggestion_weight_summands" AS

175 SELECT

176 "suggestion"."initiative_id",

177 "opinion"."suggestion_id",

178 sum("remaining_harmonic_opinion_weight"."weight_num") AS "weight_num",

179 "remaining_harmonic_opinion_weight"."weight_den"

180 FROM "remaining_harmonic_opinion_weight"

181 JOIN "opinion"

182 ON "remaining_harmonic_opinion_weight"."initiative_id" = "opinion"."initiative_id"

183 AND "remaining_harmonic_opinion_weight"."member_id" = "opinion"."member_id"

184 AND (

185 ("opinion"."degree" > 0 AND "opinion"."fulfilled" = FALSE) OR

186 ("opinion"."degree" < 0 AND "opinion"."fulfilled" = TRUE)

187 )

188 JOIN "suggestion"

189 ON "opinion"."suggestion_id" = "suggestion"."id"

190 AND "suggestion"."harmonic_weight" ISNULL

191 GROUP BY

192 "suggestion"."initiative_id",

193 "opinion"."suggestion_id",

194 "remaining_harmonic_opinion_weight"."weight_den";

195

196 COMMENT ON VIEW "remaining_harmonic_suggestion_weight_summands" IS 'Helper view for function "set_harmonic_suggestion_weights"';

197

198 CREATE FUNCTION "set_harmonic_suggestion_weights"

199 ( "initiative_id_p" "initiative"."id"%TYPE )

200 RETURNS VOID

201 LANGUAGE 'plpgsql' VOLATILE AS $$

202 DECLARE

203 "weight_row" "remaining_harmonic_suggestion_weight_summands"%ROWTYPE;

204 "i" INT4;

205 "count_v" INT4;

206 "summand_v" FLOAT;

207 "id_ary" INT4[];

208 "weight_ary" FLOAT[];

209 "min_weight_v" FLOAT;

210 BEGIN

211 UPDATE "suggestion" SET "harmonic_weight" = NULL

212 WHERE "initiative_id" = "initiative_id_p";

213 LOOP

214 "min_weight_v" := NULL;

215 "i" := 0;

216 "count_v" := 0;

217 FOR "weight_row" IN

218 SELECT * FROM "remaining_harmonic_suggestion_weight_summands"

219 WHERE "initiative_id" = "initiative_id_p"

220 ORDER BY "suggestion_id" DESC, "weight_den" DESC

221 -- NOTE: latest suggestions treated worse in case of tie

222 LOOP

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

224 IF "i" = 0 OR "weight_row"."suggestion_id" != "id_ary"["i"] THEN

225 "i" := "i" + 1;

226 "count_v" := "i";

227 "id_ary"["i"] := "weight_row"."suggestion_id";

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

229 ELSE

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

231 END IF;

232 END LOOP;

233 EXIT WHEN "count_v" = 0;

234 "i" := 1;

235 LOOP

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

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

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

239 END IF;

240 "i" := "i" + 1;

241 EXIT WHEN "i" > "count_v";

242 END LOOP;

243 "i" := 1;

244 LOOP

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

246 UPDATE "suggestion" SET "harmonic_weight" = "min_weight_v"

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

248 EXIT;

249 END IF;

250 "i" := "i" + 1;

251 END LOOP;

252 END LOOP;

253 UPDATE "suggestion" SET "harmonic_weight" = 0

254 WHERE "initiative_id" = "initiative_id_p" AND "harmonic_weight" ISNULL;

255 END;

256 $$;

257

258 COMMENT ON FUNCTION "set_harmonic_suggestion_weights"

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

260 IS 'Calculates and sets "harmonic_weight" of suggestions in a given initiative';

261

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

263 RETURNS VOID

264 LANGUAGE 'plpgsql' VOLATILE AS $$

265 DECLARE

266 "issue_row" "issue"%ROWTYPE;

267 BEGIN

268 PERFORM "create_snapshot"("issue_id_p");

269 PERFORM "freeze_after_snapshot"("issue_id_p");

270 PERFORM "set_harmonic_initiative_weights"("issue_id_p");

271 PERFORM "set_harmonic_suggestion_weights"("id")

272 FROM "initiative" WHERE "issue_id" = "issue_id_p";

273 RETURN;

274 END;

275 $$;

276

277 CREATE OR REPLACE FUNCTION "check_issue"

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

279 RETURNS VOID

280 LANGUAGE 'plpgsql' VOLATILE AS $$

281 DECLARE

282 "issue_row" "issue"%ROWTYPE;

283 "policy_row" "policy"%ROWTYPE;

284 "new_snapshot_v" BOOLEAN;

285 BEGIN

286 PERFORM "lock_issue"("issue_id_p");

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

288 -- only process open issues:

289 IF "issue_row"."closed" ISNULL THEN

290 SELECT * INTO "policy_row" FROM "policy"

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

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

293 IF "issue_row"."fully_frozen" ISNULL THEN

294 PERFORM "create_snapshot"("issue_id_p");

295 "new_snapshot_v" := TRUE;

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

297 ELSE

298 "new_snapshot_v" := FALSE;

299 END IF;

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

301 IF "issue_row"."accepted" ISNULL THEN

302 IF EXISTS (

303 SELECT NULL FROM "initiative"

304 WHERE "issue_id" = "issue_id_p"

305 AND "supporter_count" > 0

306 AND "supporter_count" * "policy_row"."issue_quorum_den"

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

308 ) THEN

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

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

311 -- NOTE: "issue_row" used later

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

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

314 UPDATE "issue" SET

315 "state" = "issue_row"."state",

316 "accepted" = "issue_row"."accepted"

317 WHERE "id" = "issue_row"."id";

318 ELSIF

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

320 THEN

321 -- close issues, if admission time has expired

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

323 UPDATE "issue" SET

324 "state" = 'canceled_issue_not_accepted',

325 "closed" = now()

326 WHERE "id" = "issue_row"."id";

327 END IF;

328 END IF;

329 -- eventually half freeze issues:

330 IF

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

332 "issue_row"."accepted" NOTNULL AND

333 "issue_row"."half_frozen" ISNULL

334 THEN

335 IF

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

337 THEN

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

339 -- NOTE: "issue_row" used later

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

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

342 UPDATE "issue" SET

343 "state" = "issue_row"."state",

344 "half_frozen" = "issue_row"."half_frozen"

345 WHERE "id" = "issue_row"."id";

346 END IF;

347 END IF;

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

349 IF

350 "issue_row"."closed" ISNULL AND

351 NOT EXISTS (

352 -- all initiatives are revoked

353 SELECT NULL FROM "initiative"

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

355 ) AND (

356 -- and issue has not been accepted yet

357 "issue_row"."accepted" ISNULL OR

358 NOT EXISTS (

359 -- or no initiatives have been revoked lately

360 SELECT NULL FROM "initiative"

361 WHERE "issue_id" = "issue_id_p"

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

363 ) OR (

364 -- or verification time has elapsed

365 "issue_row"."half_frozen" NOTNULL AND

366 "issue_row"."fully_frozen" ISNULL AND

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

368 )

369 )

370 THEN

371 -- NOTE: "issue_row" used later

372 IF "issue_row"."accepted" ISNULL THEN

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

374 ELSIF "issue_row"."half_frozen" ISNULL THEN

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

376 ELSE

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

378 END IF;

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

380 UPDATE "issue" SET

381 "state" = "issue_row"."state",

382 "closed" = "issue_row"."closed"

383 WHERE "id" = "issue_row"."id";

384 END IF;

385 -- fully freeze issue after verification time:

386 IF

387 "issue_row"."half_frozen" NOTNULL AND

388 "issue_row"."fully_frozen" ISNULL AND

389 "issue_row"."closed" ISNULL AND

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

391 THEN

392 PERFORM "freeze_after_snapshot"("issue_id_p");

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

394 END IF;

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

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

397 IF

398 "issue_row"."closed" ISNULL AND

399 "issue_row"."fully_frozen" NOTNULL AND

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

401 THEN

402 PERFORM "close_voting"("issue_id_p");

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

404 PERFORM "calculate_ranks"("issue_id_p");

405 END IF;

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

407 IF "new_snapshot_v" THEN

408 PERFORM "set_harmonic_initiative_weights"("issue_id_p");

409 PERFORM "set_harmonic_suggestion_weights"("id")

410 FROM "initiative" WHERE "issue_id" = "issue_id_p";

411 END IF;

412 END IF;

413 RETURN;

414 END;

415 $$;

416

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

418 SELECT "set_harmonic_suggestion_weights"("id") FROM "initiative";

419

420 COMMIT;