liquid_feedback_core: 685d38986598 update/core-update.v2.1.0-v2.1.1.sql

liquid_feedback_core

view update/core-update.v2.1.0-v2.1.1.sql @ 319:685d38986598

Bugfix in view "remaining_harmonic_suggestion_weight_summands"

author	jbe
date	Sat Feb 02 22:31:55 2013 +0100 (2013-02-02)
parents	89974afd890b
children	3a7f06a88ecb

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 JOIN "suggestion"

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

146 AND "suggestion"."harmonic_weight" ISNULL

147 GROUP BY

148 "initiative"."issue_id",

149 "opinion"."initiative_id",

150 "direct_interest_snapshot"."member_id",

151 "direct_interest_snapshot"."weight";

152

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

154

155 CREATE VIEW "remaining_harmonic_suggestion_weight_summands" AS

156 SELECT

157 "suggestion"."initiative_id",

158 "opinion"."suggestion_id",

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

160 "remaining_harmonic_opinion_weight"."weight_den"

161 FROM "remaining_harmonic_opinion_weight"

162 JOIN "opinion"

163 ON "remaining_harmonic_opinion_weight"."initiative_id" = "opinion"."initiative_id"

164 AND "remaining_harmonic_opinion_weight"."member_id" = "opinion"."member_id"

165 AND (

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

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

168 )

169 JOIN "suggestion"

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

171 AND "suggestion"."harmonic_weight" ISNULL

172 GROUP BY

173 "suggestion"."initiative_id",

174 "opinion"."suggestion_id",

175 "remaining_harmonic_opinion_weight"."weight_den";

176

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

178

179 CREATE FUNCTION "set_harmonic_suggestion_weights"

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

181 RETURNS VOID

182 LANGUAGE 'plpgsql' VOLATILE AS $$

183 DECLARE

184 "weight_row" "remaining_harmonic_suggestion_weight_summands"%ROWTYPE;

185 "i" INT4;

186 "count_v" INT4;

187 "summand_v" FLOAT;

188 "id_ary" INT4[];

189 "weight_ary" FLOAT[];

190 "min_weight_v" FLOAT;

191 BEGIN

192 UPDATE "suggestion" SET "harmonic_weight" = NULL

193 WHERE "initiative_id" = "initiative_id_p";

194 LOOP

195 "min_weight_v" := NULL;

196 "i" := 0;

197 "count_v" := 0;

198 FOR "weight_row" IN

199 SELECT * FROM "remaining_harmonic_suggestion_weight_summands"

200 WHERE "initiative_id" = "initiative_id_p"

201 ORDER BY "suggestion_id" DESC, "weight_den" DESC

202 -- NOTE: latest suggestions treated worse

203 LOOP

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

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

206 "i" := "i" + 1;

207 "count_v" := "i";

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

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

210 ELSE

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

212 END IF;

213 END LOOP;

214 EXIT WHEN "count_v" = 0;

215 "i" := 1;

216 LOOP

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

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

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

220 END IF;

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

222 EXIT WHEN "i" > "count_v";

223 END LOOP;

224 "i" := 1;

225 LOOP

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

227 UPDATE "suggestion" SET "harmonic_weight" = "min_weight_v"

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

229 EXIT;

230 END IF;

231 "i" := "i" + 1;

232 END LOOP;

233 END LOOP;

234 UPDATE "suggestion" SET "harmonic_weight" = 0

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

236 END;

237 $$;

238

239 COMMENT ON FUNCTION "set_harmonic_suggestion_weights"

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

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

242

243 CREATE OR REPLACE FUNCTION "create_snapshot"

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

245 RETURNS VOID

246 LANGUAGE 'plpgsql' VOLATILE AS $$

247 DECLARE

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

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

250 BEGIN

251 PERFORM "lock_issue"("issue_id_p");

252 PERFORM "create_population_snapshot"("issue_id_p");

253 PERFORM "create_interest_snapshot"("issue_id_p");

254 UPDATE "issue" SET

255 "snapshot" = now(),

256 "latest_snapshot_event" = 'periodic',

257 "population" = (

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

259 FROM "direct_population_snapshot"

260 WHERE "issue_id" = "issue_id_p"

261 AND "event" = 'periodic'

262 )

263 WHERE "id" = "issue_id_p";

264 FOR "initiative_id_v" IN

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

266 LOOP

267 UPDATE "initiative" SET

268 "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 ),

278 "informed_supporter_count" = (

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

280 FROM "direct_interest_snapshot" AS "di"

281 JOIN "direct_supporter_snapshot" AS "ds"

282 ON "di"."member_id" = "ds"."member_id"

283 WHERE "di"."issue_id" = "issue_id_p"

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

285 AND "ds"."initiative_id" = "initiative_id_v"

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

287 AND "ds"."informed"

288 ),

289 "satisfied_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"."satisfied"

299 ),

300 "satisfied_informed_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"."informed"

310 AND "ds"."satisfied"

311 )

312 WHERE "id" = "initiative_id_v";

313 FOR "suggestion_id_v" IN

314 SELECT "id" FROM "suggestion"

315 WHERE "initiative_id" = "initiative_id_v"

316 LOOP

317 UPDATE "suggestion" SET

318 "minus2_unfulfilled_count" = (

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

320 FROM "issue" CROSS JOIN "opinion"

321 JOIN "direct_interest_snapshot" AS "snapshot"

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

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

324 AND "snapshot"."member_id" = "opinion"."member_id"

325 WHERE "issue"."id" = "issue_id_p"

326 AND "opinion"."suggestion_id" = "suggestion_id_v"

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

328 AND "opinion"."fulfilled" = FALSE

329 ),

330 "minus2_fulfilled_count" = (

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

332 FROM "issue" CROSS JOIN "opinion"

333 JOIN "direct_interest_snapshot" AS "snapshot"

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

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

336 AND "snapshot"."member_id" = "opinion"."member_id"

337 WHERE "issue"."id" = "issue_id_p"

338 AND "opinion"."suggestion_id" = "suggestion_id_v"

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

340 AND "opinion"."fulfilled" = TRUE

341 ),

342 "minus1_unfulfilled_count" = (

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

344 FROM "issue" CROSS JOIN "opinion"

345 JOIN "direct_interest_snapshot" AS "snapshot"

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

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

348 AND "snapshot"."member_id" = "opinion"."member_id"

349 WHERE "issue"."id" = "issue_id_p"

350 AND "opinion"."suggestion_id" = "suggestion_id_v"

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

352 AND "opinion"."fulfilled" = FALSE

353 ),

354 "minus1_fulfilled_count" = (

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

356 FROM "issue" CROSS JOIN "opinion"

357 JOIN "direct_interest_snapshot" AS "snapshot"

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

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

360 AND "snapshot"."member_id" = "opinion"."member_id"

361 WHERE "issue"."id" = "issue_id_p"

362 AND "opinion"."suggestion_id" = "suggestion_id_v"

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

364 AND "opinion"."fulfilled" = TRUE

365 ),

366 "plus1_unfulfilled_count" = (

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

368 FROM "issue" CROSS JOIN "opinion"

369 JOIN "direct_interest_snapshot" AS "snapshot"

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

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

372 AND "snapshot"."member_id" = "opinion"."member_id"

373 WHERE "issue"."id" = "issue_id_p"

374 AND "opinion"."suggestion_id" = "suggestion_id_v"

375 AND "opinion"."degree" = 1

376 AND "opinion"."fulfilled" = FALSE

377 ),

378 "plus1_fulfilled_count" = (

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

380 FROM "issue" CROSS JOIN "opinion"

381 JOIN "direct_interest_snapshot" AS "snapshot"

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

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

384 AND "snapshot"."member_id" = "opinion"."member_id"

385 WHERE "issue"."id" = "issue_id_p"

386 AND "opinion"."suggestion_id" = "suggestion_id_v"

387 AND "opinion"."degree" = 1

388 AND "opinion"."fulfilled" = TRUE

389 ),

390 "plus2_unfulfilled_count" = (

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

392 FROM "issue" CROSS JOIN "opinion"

393 JOIN "direct_interest_snapshot" AS "snapshot"

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

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

396 AND "snapshot"."member_id" = "opinion"."member_id"

397 WHERE "issue"."id" = "issue_id_p"

398 AND "opinion"."suggestion_id" = "suggestion_id_v"

399 AND "opinion"."degree" = 2

400 AND "opinion"."fulfilled" = FALSE

401 ),

402 "plus2_fulfilled_count" = (

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

404 FROM "issue" CROSS JOIN "opinion"

405 JOIN "direct_interest_snapshot" AS "snapshot"

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

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

408 AND "snapshot"."member_id" = "opinion"."member_id"

409 WHERE "issue"."id" = "issue_id_p"

410 AND "opinion"."suggestion_id" = "suggestion_id_v"

411 AND "opinion"."degree" = 2

412 AND "opinion"."fulfilled" = TRUE

413 )

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

415 END LOOP;

416 PERFORM "set_harmonic_suggestion_weights"("initiative_id_v");

417 END LOOP;

418 PERFORM "set_harmonic_initiative_weights"("issue_id_p");

419 RETURN;

420 END;

421 $$;

422

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

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

425

426 COMMIT;