liquid_feedback_core: 09e2805decd3 update/core-update.v2.1.0-v2.1.1.sql

liquid_feedback_core

view update/core-update.v2.1.0-v2.1.1.sql @ 317:09e2805decd3

Count non-fulfilled suggestions with positive opinion and fulfilled suggestions with negative opinion for "harmonic_weight" of suggestions

author	jbe
date	Sat Feb 02 20:37:03 2013 +0100 (2013-02-02)
parents	727926e290e7
children	89974afd890b

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

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 sum("remaining_harmonic_supporter_weight"."weight_num") AS "weight_num",

44 "remaining_harmonic_supporter_weight"."weight_den"

45 FROM "remaining_harmonic_supporter_weight"

46 JOIN "direct_supporter_snapshot"

47 ON "remaining_harmonic_supporter_weight"."issue_id" = "direct_supporter_snapshot"."issue_id"

48 AND "remaining_harmonic_supporter_weight"."event" = "direct_supporter_snapshot"."event"

49 AND "remaining_harmonic_supporter_weight"."member_id" = "direct_supporter_snapshot"."member_id"

50 JOIN "initiative"

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

52 AND "initiative"."harmonic_weight" ISNULL

53 GROUP BY

54 "initiative"."issue_id",

55 "initiative"."id",

56 "remaining_harmonic_supporter_weight"."weight_den";

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

60 CREATE FUNCTION "set_harmonic_initiative_weights"

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

62 RETURNS VOID

63 LANGUAGE 'plpgsql' VOLATILE AS $$

64 DECLARE

65 "weight_row" "remaining_harmonic_initiative_weight_summands"%ROWTYPE;

66 "i" INT4;

67 "count_v" INT4;

68 "summand_v" FLOAT;

69 "id_ary" INT4[];

70 "weight_ary" FLOAT[];

71 "min_weight_v" FLOAT;

72 BEGIN

73 UPDATE "initiative" SET "harmonic_weight" = NULL

74 WHERE "issue_id" = "issue_id_p";

75 LOOP

76 "min_weight_v" := NULL;

77 "i" := 0;

78 "count_v" := 0;

79 FOR "weight_row" IN

80 SELECT * FROM "remaining_harmonic_initiative_weight_summands"

81 WHERE "issue_id" = "issue_id_p"

82 ORDER BY "initiative_id" DESC, "weight_den" DESC

83 -- NOTE: latest initiatives treated worse

84 LOOP

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

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

87 "i" := "i" + 1;

88 "count_v" := "i";

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

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

91 ELSE

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

93 END IF;

94 END LOOP;

95 EXIT WHEN "count_v" = 0;

96 "i" := 1;

97 LOOP

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

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

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

101 END IF;

102 "i" := "i" + 1;

103 EXIT WHEN "i" > "count_v";

104 END LOOP;

105 "i" := 1;

106 LOOP

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

108 UPDATE "initiative" SET "harmonic_weight" = "min_weight_v"

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

110 EXIT;

111 END IF;

112 "i" := "i" + 1;

113 END LOOP;

114 END LOOP;

115 UPDATE "initiative" SET "harmonic_weight" = 0

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

117 END;

118 $$;

119

120 COMMENT ON FUNCTION "set_harmonic_initiative_weights"

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

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

123

124 CREATE VIEW "remaining_harmonic_opinion_weight" AS

125 SELECT

126 "initiative"."issue_id",

127 "opinion"."initiative_id",

128 "direct_interest_snapshot"."member_id",

129 "direct_interest_snapshot"."weight" AS "weight_num",

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

131 FROM "issue"

132 JOIN "direct_interest_snapshot"

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

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

135 JOIN "initiative"

136 ON "direct_interest_snapshot"."issue_id" = "initiative"."issue_id"

137 JOIN "opinion"

138 ON "direct_interest_snapshot"."member_id" = "opinion"."member_id"

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

140 AND (

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

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

143 )

144 GROUP BY

145 "initiative"."issue_id",

146 "opinion"."initiative_id",

147 "direct_interest_snapshot"."member_id",

148 "direct_interest_snapshot"."weight";

149

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

151

152 CREATE VIEW "remaining_harmonic_suggestion_weight_summands" AS

153 SELECT

154 "suggestion"."initiative_id",

155 "opinion"."suggestion_id",

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

157 "remaining_harmonic_opinion_weight"."weight_den"

158 FROM "remaining_harmonic_opinion_weight"

159 JOIN "opinion"

160 ON "remaining_harmonic_opinion_weight"."initiative_id" = "opinion"."initiative_id"

161 AND "remaining_harmonic_opinion_weight"."member_id" = "opinion"."member_id"

162 JOIN "suggestion"

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

164 AND "suggestion"."harmonic_weight" ISNULL

165 GROUP BY

166 "suggestion"."initiative_id",

167 "opinion"."suggestion_id",

168 "remaining_harmonic_opinion_weight"."weight_den";

169

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

171

172 CREATE FUNCTION "set_harmonic_suggestion_weights"

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

174 RETURNS VOID

175 LANGUAGE 'plpgsql' VOLATILE AS $$

176 DECLARE

177 "weight_row" "remaining_harmonic_suggestion_weight_summands"%ROWTYPE;

178 "i" INT4;

179 "count_v" INT4;

180 "summand_v" FLOAT;

181 "id_ary" INT4[];

182 "weight_ary" FLOAT[];

183 "min_weight_v" FLOAT;

184 BEGIN

185 UPDATE "suggestion" SET "harmonic_weight" = NULL

186 WHERE "initiative_id" = "initiative_id_p";

187 LOOP

188 "min_weight_v" := NULL;

189 "i" := 0;

190 "count_v" := 0;

191 FOR "weight_row" IN

192 SELECT * FROM "remaining_harmonic_suggestion_weight_summands"

193 WHERE "initiative_id" = "initiative_id_p"

194 ORDER BY "suggestion_id" DESC, "weight_den" DESC

195 -- NOTE: latest suggestions treated worse

196 LOOP

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

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

199 "i" := "i" + 1;

200 "count_v" := "i";

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

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

203 ELSE

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

205 END IF;

206 END LOOP;

207 EXIT WHEN "count_v" = 0;

208 "i" := 1;

209 LOOP

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

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

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

213 END IF;

214 "i" := "i" + 1;

215 EXIT WHEN "i" > "count_v";

216 END LOOP;

217 "i" := 1;

218 LOOP

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

220 UPDATE "suggestion" SET "harmonic_weight" = "min_weight_v"

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

222 EXIT;

223 END IF;

224 "i" := "i" + 1;

225 END LOOP;

226 END LOOP;

227 UPDATE "suggestion" SET "harmonic_weight" = 0

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

229 END;

230 $$;

231

232 COMMENT ON FUNCTION "set_harmonic_suggestion_weights"

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

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

235

236 CREATE OR REPLACE FUNCTION "create_snapshot"

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

238 RETURNS VOID

239 LANGUAGE 'plpgsql' VOLATILE AS $$

240 DECLARE

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

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

243 BEGIN

244 PERFORM "lock_issue"("issue_id_p");

245 PERFORM "create_population_snapshot"("issue_id_p");

246 PERFORM "create_interest_snapshot"("issue_id_p");

247 UPDATE "issue" SET

248 "snapshot" = now(),

249 "latest_snapshot_event" = 'periodic',

250 "population" = (

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

252 FROM "direct_population_snapshot"

253 WHERE "issue_id" = "issue_id_p"

254 AND "event" = 'periodic'

255 )

256 WHERE "id" = "issue_id_p";

257 FOR "initiative_id_v" IN

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

259 LOOP

260 UPDATE "initiative" SET

261 "supporter_count" = (

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

263 FROM "direct_interest_snapshot" AS "di"

264 JOIN "direct_supporter_snapshot" AS "ds"

265 ON "di"."member_id" = "ds"."member_id"

266 WHERE "di"."issue_id" = "issue_id_p"

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

268 AND "ds"."initiative_id" = "initiative_id_v"

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

270 ),

271 "informed_supporter_count" = (

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

273 FROM "direct_interest_snapshot" AS "di"

274 JOIN "direct_supporter_snapshot" AS "ds"

275 ON "di"."member_id" = "ds"."member_id"

276 WHERE "di"."issue_id" = "issue_id_p"

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

278 AND "ds"."initiative_id" = "initiative_id_v"

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

280 AND "ds"."informed"

281 ),

282 "satisfied_supporter_count" = (

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

284 FROM "direct_interest_snapshot" AS "di"

285 JOIN "direct_supporter_snapshot" AS "ds"

286 ON "di"."member_id" = "ds"."member_id"

287 WHERE "di"."issue_id" = "issue_id_p"

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

289 AND "ds"."initiative_id" = "initiative_id_v"

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

291 AND "ds"."satisfied"

292 ),

293 "satisfied_informed_supporter_count" = (

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

295 FROM "direct_interest_snapshot" AS "di"

296 JOIN "direct_supporter_snapshot" AS "ds"

297 ON "di"."member_id" = "ds"."member_id"

298 WHERE "di"."issue_id" = "issue_id_p"

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

300 AND "ds"."initiative_id" = "initiative_id_v"

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

302 AND "ds"."informed"

303 AND "ds"."satisfied"

304 )

305 WHERE "id" = "initiative_id_v";

306 FOR "suggestion_id_v" IN

307 SELECT "id" FROM "suggestion"

308 WHERE "initiative_id" = "initiative_id_v"

309 LOOP

310 UPDATE "suggestion" SET

311 "minus2_unfulfilled_count" = (

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

313 FROM "issue" CROSS JOIN "opinion"

314 JOIN "direct_interest_snapshot" AS "snapshot"

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

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

317 AND "snapshot"."member_id" = "opinion"."member_id"

318 WHERE "issue"."id" = "issue_id_p"

319 AND "opinion"."suggestion_id" = "suggestion_id_v"

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

321 AND "opinion"."fulfilled" = FALSE

322 ),

323 "minus2_fulfilled_count" = (

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

325 FROM "issue" CROSS JOIN "opinion"

326 JOIN "direct_interest_snapshot" AS "snapshot"

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

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

329 AND "snapshot"."member_id" = "opinion"."member_id"

330 WHERE "issue"."id" = "issue_id_p"

331 AND "opinion"."suggestion_id" = "suggestion_id_v"

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

333 AND "opinion"."fulfilled" = TRUE

334 ),

335 "minus1_unfulfilled_count" = (

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

337 FROM "issue" CROSS JOIN "opinion"

338 JOIN "direct_interest_snapshot" AS "snapshot"

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

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

341 AND "snapshot"."member_id" = "opinion"."member_id"

342 WHERE "issue"."id" = "issue_id_p"

343 AND "opinion"."suggestion_id" = "suggestion_id_v"

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

345 AND "opinion"."fulfilled" = FALSE

346 ),

347 "minus1_fulfilled_count" = (

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

349 FROM "issue" CROSS JOIN "opinion"

350 JOIN "direct_interest_snapshot" AS "snapshot"

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

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

353 AND "snapshot"."member_id" = "opinion"."member_id"

354 WHERE "issue"."id" = "issue_id_p"

355 AND "opinion"."suggestion_id" = "suggestion_id_v"

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

357 AND "opinion"."fulfilled" = TRUE

358 ),

359 "plus1_unfulfilled_count" = (

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

361 FROM "issue" CROSS JOIN "opinion"

362 JOIN "direct_interest_snapshot" AS "snapshot"

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

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

365 AND "snapshot"."member_id" = "opinion"."member_id"

366 WHERE "issue"."id" = "issue_id_p"

367 AND "opinion"."suggestion_id" = "suggestion_id_v"

368 AND "opinion"."degree" = 1

369 AND "opinion"."fulfilled" = FALSE

370 ),

371 "plus1_fulfilled_count" = (

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

373 FROM "issue" CROSS JOIN "opinion"

374 JOIN "direct_interest_snapshot" AS "snapshot"

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

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

377 AND "snapshot"."member_id" = "opinion"."member_id"

378 WHERE "issue"."id" = "issue_id_p"

379 AND "opinion"."suggestion_id" = "suggestion_id_v"

380 AND "opinion"."degree" = 1

381 AND "opinion"."fulfilled" = TRUE

382 ),

383 "plus2_unfulfilled_count" = (

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

385 FROM "issue" CROSS JOIN "opinion"

386 JOIN "direct_interest_snapshot" AS "snapshot"

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

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

389 AND "snapshot"."member_id" = "opinion"."member_id"

390 WHERE "issue"."id" = "issue_id_p"

391 AND "opinion"."suggestion_id" = "suggestion_id_v"

392 AND "opinion"."degree" = 2

393 AND "opinion"."fulfilled" = FALSE

394 ),

395 "plus2_fulfilled_count" = (

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

397 FROM "issue" CROSS JOIN "opinion"

398 JOIN "direct_interest_snapshot" AS "snapshot"

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

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

401 AND "snapshot"."member_id" = "opinion"."member_id"

402 WHERE "issue"."id" = "issue_id_p"

403 AND "opinion"."suggestion_id" = "suggestion_id_v"

404 AND "opinion"."degree" = 2

405 AND "opinion"."fulfilled" = TRUE

406 )

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

408 END LOOP;

409 PERFORM "set_harmonic_suggestion_weights"("initiative_id_v");

410 END LOOP;

411 PERFORM "set_harmonic_initiative_weights"("issue_id_p");

412 RETURN;

413 END;

414 $$;

415

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

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

418

419 COMMIT;