liquid_feedback_core: 3a7f06a88ecb update/core-update.v2.1.0-v2.1.1.sql

liquid_feedback_core

view update/core-update.v2.1.0-v2.1.1.sql @ 320:3a7f06a88ecb

Calculate "harmonic_weight" to be used as a secondary sorting key, while the "admitted" flag shall be used as the primary sorting key for initiatives

author	jbe
date	Fri Feb 08 18:26:25 2013 +0100 (2013-02-08)
parents	685d38986598
children	48a5036d5eb1

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 "initiative"."harmonic_weight" ISNULL

31 GROUP BY

32 "direct_interest_snapshot"."issue_id",

33 "direct_interest_snapshot"."event",

34 "direct_interest_snapshot"."member_id",

35 "direct_interest_snapshot"."weight";

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

39 CREATE VIEW "remaining_harmonic_initiative_weight_summands" AS

40 SELECT

41 "initiative"."issue_id",

42 "initiative"."id" AS "initiative_id",

43 "initiative"."admitted",

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

45 "remaining_harmonic_supporter_weight"."weight_den"

46 FROM "remaining_harmonic_supporter_weight"

47 JOIN "direct_supporter_snapshot"

48 ON "remaining_harmonic_supporter_weight"."issue_id" = "direct_supporter_snapshot"."issue_id"

49 AND "remaining_harmonic_supporter_weight"."event" = "direct_supporter_snapshot"."event"

50 AND "remaining_harmonic_supporter_weight"."member_id" = "direct_supporter_snapshot"."member_id"

51 JOIN "initiative"

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

53 AND "initiative"."harmonic_weight" ISNULL

54 GROUP BY

55 "initiative"."issue_id",

56 "initiative"."id",

57 "initiative"."admitted",

58 "remaining_harmonic_supporter_weight"."weight_den";

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

62 CREATE FUNCTION "set_harmonic_initiative_weights"

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

64 RETURNS VOID

65 LANGUAGE 'plpgsql' VOLATILE AS $$

66 DECLARE

67 "weight_row" "remaining_harmonic_initiative_weight_summands"%ROWTYPE;

68 "i" INT4;

69 "count_v" INT4;

70 "summand_v" FLOAT;

71 "id_ary" INT4[];

72 "weight_ary" FLOAT[];

73 "min_weight_v" FLOAT;

74 BEGIN

75 UPDATE "initiative" SET "harmonic_weight" = NULL

76 WHERE "issue_id" = "issue_id_p";

77 LOOP

78 "min_weight_v" := NULL;

79 "i" := 0;

80 "count_v" := 0;

81 FOR "weight_row" IN

82 SELECT * FROM "remaining_harmonic_initiative_weight_summands"

83 WHERE "issue_id" = "issue_id_p"

84 AND (

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

86 SELECT NULL FROM "initiative"

87 WHERE "issue_id" = "issue_id_p"

88 AND "harmonic_weight" ISNULL

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

90 )

91 )

92 ORDER BY "initiative_id" DESC, "weight_den" DESC

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

94 -- latest initiatives treated worse in case of tie

95 LOOP

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

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

98 "i" := "i" + 1;

99 "count_v" := "i";

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

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

102 ELSE

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

104 END IF;

105 END LOOP;

106 EXIT WHEN "count_v" = 0;

107 "i" := 1;

108 LOOP

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

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

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

112 END IF;

113 "i" := "i" + 1;

114 EXIT WHEN "i" > "count_v";

115 END LOOP;

116 "i" := 1;

117 LOOP

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

119 UPDATE "initiative" SET "harmonic_weight" = "min_weight_v"

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

121 EXIT;

122 END IF;

123 "i" := "i" + 1;

124 END LOOP;

125 END LOOP;

126 UPDATE "initiative" SET "harmonic_weight" = 0

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

128 END;

129 $$;

130

131 COMMENT ON FUNCTION "set_harmonic_initiative_weights"

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

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

134

135 CREATE VIEW "remaining_harmonic_opinion_weight" AS

136 SELECT

137 "initiative"."issue_id",

138 "opinion"."initiative_id",

139 "direct_interest_snapshot"."member_id",

140 "direct_interest_snapshot"."weight" AS "weight_num",

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

142 FROM "issue"

143 JOIN "direct_interest_snapshot"

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

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

146 JOIN "initiative"

147 ON "direct_interest_snapshot"."issue_id" = "initiative"."issue_id"

148 JOIN "opinion"

149 ON "direct_interest_snapshot"."member_id" = "opinion"."member_id"

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

151 AND (

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

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

154 )

155 JOIN "suggestion"

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

157 AND "suggestion"."harmonic_weight" ISNULL

158 GROUP BY

159 "initiative"."issue_id",

160 "opinion"."initiative_id",

161 "direct_interest_snapshot"."member_id",

162 "direct_interest_snapshot"."weight";

163

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

165

166 CREATE VIEW "remaining_harmonic_suggestion_weight_summands" AS

167 SELECT

168 "suggestion"."initiative_id",

169 "opinion"."suggestion_id",

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

171 "remaining_harmonic_opinion_weight"."weight_den"

172 FROM "remaining_harmonic_opinion_weight"

173 JOIN "opinion"

174 ON "remaining_harmonic_opinion_weight"."initiative_id" = "opinion"."initiative_id"

175 AND "remaining_harmonic_opinion_weight"."member_id" = "opinion"."member_id"

176 AND (

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

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

179 )

180 JOIN "suggestion"

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

182 AND "suggestion"."harmonic_weight" ISNULL

183 GROUP BY

184 "suggestion"."initiative_id",

185 "opinion"."suggestion_id",

186 "remaining_harmonic_opinion_weight"."weight_den";

187

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

189

190 CREATE FUNCTION "set_harmonic_suggestion_weights"

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

192 RETURNS VOID

193 LANGUAGE 'plpgsql' VOLATILE AS $$

194 DECLARE

195 "weight_row" "remaining_harmonic_suggestion_weight_summands"%ROWTYPE;

196 "i" INT4;

197 "count_v" INT4;

198 "summand_v" FLOAT;

199 "id_ary" INT4[];

200 "weight_ary" FLOAT[];

201 "min_weight_v" FLOAT;

202 BEGIN

203 UPDATE "suggestion" SET "harmonic_weight" = NULL

204 WHERE "initiative_id" = "initiative_id_p";

205 LOOP

206 "min_weight_v" := NULL;

207 "i" := 0;

208 "count_v" := 0;

209 FOR "weight_row" IN

210 SELECT * FROM "remaining_harmonic_suggestion_weight_summands"

211 WHERE "initiative_id" = "initiative_id_p"

212 ORDER BY "suggestion_id" DESC, "weight_den" DESC

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

214 LOOP

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

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

217 "i" := "i" + 1;

218 "count_v" := "i";

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

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

221 ELSE

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

223 END IF;

224 END LOOP;

225 EXIT WHEN "count_v" = 0;

226 "i" := 1;

227 LOOP

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

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

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

231 END IF;

232 "i" := "i" + 1;

233 EXIT WHEN "i" > "count_v";

234 END LOOP;

235 "i" := 1;

236 LOOP

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

238 UPDATE "suggestion" SET "harmonic_weight" = "min_weight_v"

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

240 EXIT;

241 END IF;

242 "i" := "i" + 1;

243 END LOOP;

244 END LOOP;

245 UPDATE "suggestion" SET "harmonic_weight" = 0

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

247 END;

248 $$;

249

250 COMMENT ON FUNCTION "set_harmonic_suggestion_weights"

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

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

253

254 CREATE OR REPLACE FUNCTION "create_snapshot"

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

256 RETURNS VOID

257 LANGUAGE 'plpgsql' VOLATILE AS $$

258 DECLARE

259 "initiative_id_v" "initiative"."id"%TYPE;

260 "suggestion_id_v" "suggestion"."id"%TYPE;

261 BEGIN

262 PERFORM "lock_issue"("issue_id_p");

263 PERFORM "create_population_snapshot"("issue_id_p");

264 PERFORM "create_interest_snapshot"("issue_id_p");

265 UPDATE "issue" SET

266 "snapshot" = now(),

267 "latest_snapshot_event" = 'periodic',

268 "population" = (

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

270 FROM "direct_population_snapshot"

271 WHERE "issue_id" = "issue_id_p"

272 AND "event" = 'periodic'

273 )

274 WHERE "id" = "issue_id_p";

275 FOR "initiative_id_v" IN

276 SELECT "id" FROM "initiative" WHERE "issue_id" = "issue_id_p"

277 LOOP

278 UPDATE "initiative" SET

279 "supporter_count" = (

280 SELECT coalesce(sum("di"."weight"), 0)

281 FROM "direct_interest_snapshot" AS "di"

282 JOIN "direct_supporter_snapshot" AS "ds"

283 ON "di"."member_id" = "ds"."member_id"

284 WHERE "di"."issue_id" = "issue_id_p"

285 AND "di"."event" = 'periodic'

286 AND "ds"."initiative_id" = "initiative_id_v"

287 AND "ds"."event" = 'periodic'

288 ),

289 "informed_supporter_count" = (

290 SELECT coalesce(sum("di"."weight"), 0)

291 FROM "direct_interest_snapshot" AS "di"

292 JOIN "direct_supporter_snapshot" AS "ds"

293 ON "di"."member_id" = "ds"."member_id"

294 WHERE "di"."issue_id" = "issue_id_p"

295 AND "di"."event" = 'periodic'

296 AND "ds"."initiative_id" = "initiative_id_v"

297 AND "ds"."event" = 'periodic'

298 AND "ds"."informed"

299 ),

300 "satisfied_supporter_count" = (

301 SELECT coalesce(sum("di"."weight"), 0)

302 FROM "direct_interest_snapshot" AS "di"

303 JOIN "direct_supporter_snapshot" AS "ds"

304 ON "di"."member_id" = "ds"."member_id"

305 WHERE "di"."issue_id" = "issue_id_p"

306 AND "di"."event" = 'periodic'

307 AND "ds"."initiative_id" = "initiative_id_v"

308 AND "ds"."event" = 'periodic'

309 AND "ds"."satisfied"

310 ),

311 "satisfied_informed_supporter_count" = (

312 SELECT coalesce(sum("di"."weight"), 0)

313 FROM "direct_interest_snapshot" AS "di"

314 JOIN "direct_supporter_snapshot" AS "ds"

315 ON "di"."member_id" = "ds"."member_id"

316 WHERE "di"."issue_id" = "issue_id_p"

317 AND "di"."event" = 'periodic'

318 AND "ds"."initiative_id" = "initiative_id_v"

319 AND "ds"."event" = 'periodic'

320 AND "ds"."informed"

321 AND "ds"."satisfied"

322 )

323 WHERE "id" = "initiative_id_v";

324 FOR "suggestion_id_v" IN

325 SELECT "id" FROM "suggestion"

326 WHERE "initiative_id" = "initiative_id_v"

327 LOOP

328 UPDATE "suggestion" SET

329 "minus2_unfulfilled_count" = (

330 SELECT coalesce(sum("snapshot"."weight"), 0)

331 FROM "issue" CROSS JOIN "opinion"

332 JOIN "direct_interest_snapshot" AS "snapshot"

333 ON "snapshot"."issue_id" = "issue"."id"

334 AND "snapshot"."event" = "issue"."latest_snapshot_event"

335 AND "snapshot"."member_id" = "opinion"."member_id"

336 WHERE "issue"."id" = "issue_id_p"

337 AND "opinion"."suggestion_id" = "suggestion_id_v"

338 AND "opinion"."degree" = -2

339 AND "opinion"."fulfilled" = FALSE

340 ),

341 "minus2_fulfilled_count" = (

342 SELECT coalesce(sum("snapshot"."weight"), 0)

343 FROM "issue" CROSS JOIN "opinion"

344 JOIN "direct_interest_snapshot" AS "snapshot"

345 ON "snapshot"."issue_id" = "issue"."id"

346 AND "snapshot"."event" = "issue"."latest_snapshot_event"

347 AND "snapshot"."member_id" = "opinion"."member_id"

348 WHERE "issue"."id" = "issue_id_p"

349 AND "opinion"."suggestion_id" = "suggestion_id_v"

350 AND "opinion"."degree" = -2

351 AND "opinion"."fulfilled" = TRUE

352 ),

353 "minus1_unfulfilled_count" = (

354 SELECT coalesce(sum("snapshot"."weight"), 0)

355 FROM "issue" CROSS JOIN "opinion"

356 JOIN "direct_interest_snapshot" AS "snapshot"

357 ON "snapshot"."issue_id" = "issue"."id"

358 AND "snapshot"."event" = "issue"."latest_snapshot_event"

359 AND "snapshot"."member_id" = "opinion"."member_id"

360 WHERE "issue"."id" = "issue_id_p"

361 AND "opinion"."suggestion_id" = "suggestion_id_v"

362 AND "opinion"."degree" = -1

363 AND "opinion"."fulfilled" = FALSE

364 ),

365 "minus1_fulfilled_count" = (

366 SELECT coalesce(sum("snapshot"."weight"), 0)

367 FROM "issue" CROSS JOIN "opinion"

368 JOIN "direct_interest_snapshot" AS "snapshot"

369 ON "snapshot"."issue_id" = "issue"."id"

370 AND "snapshot"."event" = "issue"."latest_snapshot_event"

371 AND "snapshot"."member_id" = "opinion"."member_id"

372 WHERE "issue"."id" = "issue_id_p"

373 AND "opinion"."suggestion_id" = "suggestion_id_v"

374 AND "opinion"."degree" = -1

375 AND "opinion"."fulfilled" = TRUE

376 ),

377 "plus1_unfulfilled_count" = (

378 SELECT coalesce(sum("snapshot"."weight"), 0)

379 FROM "issue" CROSS JOIN "opinion"

380 JOIN "direct_interest_snapshot" AS "snapshot"

381 ON "snapshot"."issue_id" = "issue"."id"

382 AND "snapshot"."event" = "issue"."latest_snapshot_event"

383 AND "snapshot"."member_id" = "opinion"."member_id"

384 WHERE "issue"."id" = "issue_id_p"

385 AND "opinion"."suggestion_id" = "suggestion_id_v"

386 AND "opinion"."degree" = 1

387 AND "opinion"."fulfilled" = FALSE

388 ),

389 "plus1_fulfilled_count" = (

390 SELECT coalesce(sum("snapshot"."weight"), 0)

391 FROM "issue" CROSS JOIN "opinion"

392 JOIN "direct_interest_snapshot" AS "snapshot"

393 ON "snapshot"."issue_id" = "issue"."id"

394 AND "snapshot"."event" = "issue"."latest_snapshot_event"

395 AND "snapshot"."member_id" = "opinion"."member_id"

396 WHERE "issue"."id" = "issue_id_p"

397 AND "opinion"."suggestion_id" = "suggestion_id_v"

398 AND "opinion"."degree" = 1

399 AND "opinion"."fulfilled" = TRUE

400 ),

401 "plus2_unfulfilled_count" = (

402 SELECT coalesce(sum("snapshot"."weight"), 0)

403 FROM "issue" CROSS JOIN "opinion"

404 JOIN "direct_interest_snapshot" AS "snapshot"

405 ON "snapshot"."issue_id" = "issue"."id"

406 AND "snapshot"."event" = "issue"."latest_snapshot_event"

407 AND "snapshot"."member_id" = "opinion"."member_id"

408 WHERE "issue"."id" = "issue_id_p"

409 AND "opinion"."suggestion_id" = "suggestion_id_v"

410 AND "opinion"."degree" = 2

411 AND "opinion"."fulfilled" = FALSE

412 ),

413 "plus2_fulfilled_count" = (

414 SELECT coalesce(sum("snapshot"."weight"), 0)

415 FROM "issue" CROSS JOIN "opinion"

416 JOIN "direct_interest_snapshot" AS "snapshot"

417 ON "snapshot"."issue_id" = "issue"."id"

418 AND "snapshot"."event" = "issue"."latest_snapshot_event"

419 AND "snapshot"."member_id" = "opinion"."member_id"

420 WHERE "issue"."id" = "issue_id_p"

421 AND "opinion"."suggestion_id" = "suggestion_id_v"

422 AND "opinion"."degree" = 2

423 AND "opinion"."fulfilled" = TRUE

424 )

425 WHERE "suggestion"."id" = "suggestion_id_v";

426 END LOOP;

427 PERFORM "set_harmonic_suggestion_weights"("initiative_id_v");

428 END LOOP;

429 PERFORM "set_harmonic_initiative_weights"("issue_id_p");

430 RETURN;

431 END;

432 $$;

433

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

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

436

437 COMMIT;