liquid_feedback_core: 727926e290e7 update/core-update.v2.1.0-v2.1.1.sql

liquid_feedback_core

view update/core-update.v2.1.0-v2.1.1.sql @ 316:727926e290e7

Set "harmonic_weight" to zero instead of null, where there are no supporters

author	jbe
date	Sat Feb 02 20:21:16 2013 +0100 (2013-02-02)
parents	3e450a518197
children	09e2805decd3

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 "opinion"."degree" > 0

141 GROUP BY

142 "initiative"."issue_id",

143 "opinion"."initiative_id",

144 "direct_interest_snapshot"."member_id",

145 "direct_interest_snapshot"."weight";

146

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

148

149 CREATE VIEW "remaining_harmonic_suggestion_weight_summands" AS

150 SELECT

151 "suggestion"."initiative_id",

152 "opinion"."suggestion_id",

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

154 "remaining_harmonic_opinion_weight"."weight_den"

155 FROM "remaining_harmonic_opinion_weight"

156 JOIN "opinion"

157 ON "remaining_harmonic_opinion_weight"."initiative_id" = "opinion"."initiative_id"

158 AND "remaining_harmonic_opinion_weight"."member_id" = "opinion"."member_id"

159 JOIN "suggestion"

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

161 AND "suggestion"."harmonic_weight" ISNULL

162 GROUP BY

163 "suggestion"."initiative_id",

164 "opinion"."suggestion_id",

165 "remaining_harmonic_opinion_weight"."weight_den";

166

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

168

169 CREATE FUNCTION "set_harmonic_suggestion_weights"

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

171 RETURNS VOID

172 LANGUAGE 'plpgsql' VOLATILE AS $$

173 DECLARE

174 "weight_row" "remaining_harmonic_suggestion_weight_summands"%ROWTYPE;

175 "i" INT4;

176 "count_v" INT4;

177 "summand_v" FLOAT;

178 "id_ary" INT4[];

179 "weight_ary" FLOAT[];

180 "min_weight_v" FLOAT;

181 BEGIN

182 UPDATE "suggestion" SET "harmonic_weight" = NULL

183 WHERE "initiative_id" = "initiative_id_p";

184 LOOP

185 "min_weight_v" := NULL;

186 "i" := 0;

187 "count_v" := 0;

188 FOR "weight_row" IN

189 SELECT * FROM "remaining_harmonic_suggestion_weight_summands"

190 WHERE "initiative_id" = "initiative_id_p"

191 ORDER BY "suggestion_id" DESC, "weight_den" DESC

192 -- NOTE: latest suggestions treated worse

193 LOOP

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

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

196 "i" := "i" + 1;

197 "count_v" := "i";

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

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

200 ELSE

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

202 END IF;

203 END LOOP;

204 EXIT WHEN "count_v" = 0;

205 "i" := 1;

206 LOOP

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

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

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

210 END IF;

211 "i" := "i" + 1;

212 EXIT WHEN "i" > "count_v";

213 END LOOP;

214 "i" := 1;

215 LOOP

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

217 UPDATE "suggestion" SET "harmonic_weight" = "min_weight_v"

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

219 EXIT;

220 END IF;

221 "i" := "i" + 1;

222 END LOOP;

223 END LOOP;

224 UPDATE "suggestion" SET "harmonic_weight" = 0

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

226 END;

227 $$;

228

229 COMMENT ON FUNCTION "set_harmonic_suggestion_weights"

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

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

232

233 CREATE OR REPLACE FUNCTION "create_snapshot"

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

235 RETURNS VOID

236 LANGUAGE 'plpgsql' VOLATILE AS $$

237 DECLARE

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

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

240 BEGIN

241 PERFORM "lock_issue"("issue_id_p");

242 PERFORM "create_population_snapshot"("issue_id_p");

243 PERFORM "create_interest_snapshot"("issue_id_p");

244 UPDATE "issue" SET

245 "snapshot" = now(),

246 "latest_snapshot_event" = 'periodic',

247 "population" = (

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

249 FROM "direct_population_snapshot"

250 WHERE "issue_id" = "issue_id_p"

251 AND "event" = 'periodic'

252 )

253 WHERE "id" = "issue_id_p";

254 FOR "initiative_id_v" IN

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

256 LOOP

257 UPDATE "initiative" SET

258 "supporter_count" = (

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

260 FROM "direct_interest_snapshot" AS "di"

261 JOIN "direct_supporter_snapshot" AS "ds"

262 ON "di"."member_id" = "ds"."member_id"

263 WHERE "di"."issue_id" = "issue_id_p"

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

265 AND "ds"."initiative_id" = "initiative_id_v"

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

267 ),

268 "informed_supporter_count" = (

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

270 FROM "direct_interest_snapshot" AS "di"

271 JOIN "direct_supporter_snapshot" AS "ds"

272 ON "di"."member_id" = "ds"."member_id"

273 WHERE "di"."issue_id" = "issue_id_p"

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

275 AND "ds"."initiative_id" = "initiative_id_v"

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

277 AND "ds"."informed"

278 ),

279 "satisfied_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 AND "ds"."satisfied"

289 ),

290 "satisfied_informed_supporter_count" = (

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

292 FROM "direct_interest_snapshot" AS "di"

293 JOIN "direct_supporter_snapshot" AS "ds"

294 ON "di"."member_id" = "ds"."member_id"

295 WHERE "di"."issue_id" = "issue_id_p"

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

297 AND "ds"."initiative_id" = "initiative_id_v"

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

299 AND "ds"."informed"

300 AND "ds"."satisfied"

301 )

302 WHERE "id" = "initiative_id_v";

303 FOR "suggestion_id_v" IN

304 SELECT "id" FROM "suggestion"

305 WHERE "initiative_id" = "initiative_id_v"

306 LOOP

307 UPDATE "suggestion" SET

308 "minus2_unfulfilled_count" = (

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

310 FROM "issue" CROSS JOIN "opinion"

311 JOIN "direct_interest_snapshot" AS "snapshot"

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

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

314 AND "snapshot"."member_id" = "opinion"."member_id"

315 WHERE "issue"."id" = "issue_id_p"

316 AND "opinion"."suggestion_id" = "suggestion_id_v"

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

318 AND "opinion"."fulfilled" = FALSE

319 ),

320 "minus2_fulfilled_count" = (

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

322 FROM "issue" CROSS JOIN "opinion"

323 JOIN "direct_interest_snapshot" AS "snapshot"

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

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

326 AND "snapshot"."member_id" = "opinion"."member_id"

327 WHERE "issue"."id" = "issue_id_p"

328 AND "opinion"."suggestion_id" = "suggestion_id_v"

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

330 AND "opinion"."fulfilled" = TRUE

331 ),

332 "minus1_unfulfilled_count" = (

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

334 FROM "issue" CROSS JOIN "opinion"

335 JOIN "direct_interest_snapshot" AS "snapshot"

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

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

338 AND "snapshot"."member_id" = "opinion"."member_id"

339 WHERE "issue"."id" = "issue_id_p"

340 AND "opinion"."suggestion_id" = "suggestion_id_v"

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

342 AND "opinion"."fulfilled" = FALSE

343 ),

344 "minus1_fulfilled_count" = (

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

346 FROM "issue" CROSS JOIN "opinion"

347 JOIN "direct_interest_snapshot" AS "snapshot"

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

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

350 AND "snapshot"."member_id" = "opinion"."member_id"

351 WHERE "issue"."id" = "issue_id_p"

352 AND "opinion"."suggestion_id" = "suggestion_id_v"

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

354 AND "opinion"."fulfilled" = TRUE

355 ),

356 "plus1_unfulfilled_count" = (

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

358 FROM "issue" CROSS JOIN "opinion"

359 JOIN "direct_interest_snapshot" AS "snapshot"

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

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

362 AND "snapshot"."member_id" = "opinion"."member_id"

363 WHERE "issue"."id" = "issue_id_p"

364 AND "opinion"."suggestion_id" = "suggestion_id_v"

365 AND "opinion"."degree" = 1

366 AND "opinion"."fulfilled" = FALSE

367 ),

368 "plus1_fulfilled_count" = (

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

370 FROM "issue" CROSS JOIN "opinion"

371 JOIN "direct_interest_snapshot" AS "snapshot"

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

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

374 AND "snapshot"."member_id" = "opinion"."member_id"

375 WHERE "issue"."id" = "issue_id_p"

376 AND "opinion"."suggestion_id" = "suggestion_id_v"

377 AND "opinion"."degree" = 1

378 AND "opinion"."fulfilled" = TRUE

379 ),

380 "plus2_unfulfilled_count" = (

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

382 FROM "issue" CROSS JOIN "opinion"

383 JOIN "direct_interest_snapshot" AS "snapshot"

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

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

386 AND "snapshot"."member_id" = "opinion"."member_id"

387 WHERE "issue"."id" = "issue_id_p"

388 AND "opinion"."suggestion_id" = "suggestion_id_v"

389 AND "opinion"."degree" = 2

390 AND "opinion"."fulfilled" = FALSE

391 ),

392 "plus2_fulfilled_count" = (

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

394 FROM "issue" CROSS JOIN "opinion"

395 JOIN "direct_interest_snapshot" AS "snapshot"

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

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

398 AND "snapshot"."member_id" = "opinion"."member_id"

399 WHERE "issue"."id" = "issue_id_p"

400 AND "opinion"."suggestion_id" = "suggestion_id_v"

401 AND "opinion"."degree" = 2

402 AND "opinion"."fulfilled" = TRUE

403 )

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

405 END LOOP;

406 PERFORM "set_harmonic_suggestion_weights"("initiative_id_v");

407 END LOOP;

408 PERFORM "set_harmonic_initiative_weights"("issue_id_p");

409 RETURN;

410 END;

411 $$;

412

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

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

415

416 COMMIT;