liquid_feedback_core: 89974afd890b update/core-update.v2.1.0-v2.1.1.sql

liquid_feedback_core

view update/core-update.v2.1.0-v2.1.1.sql @ 318:89974afd890b

Bugfix in view "remaining_harmonic_opinion_weight"

author	jbe
date	Sat Feb 02 22:26:18 2013 +0100 (2013-02-02)
parents	09e2805decd3
children	685d38986598

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

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

167 AND "suggestion"."harmonic_weight" ISNULL

168 GROUP BY

169 "suggestion"."initiative_id",

170 "opinion"."suggestion_id",

171 "remaining_harmonic_opinion_weight"."weight_den";

172

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

174

175 CREATE FUNCTION "set_harmonic_suggestion_weights"

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

177 RETURNS VOID

178 LANGUAGE 'plpgsql' VOLATILE AS $$

179 DECLARE

180 "weight_row" "remaining_harmonic_suggestion_weight_summands"%ROWTYPE;

181 "i" INT4;

182 "count_v" INT4;

183 "summand_v" FLOAT;

184 "id_ary" INT4[];

185 "weight_ary" FLOAT[];

186 "min_weight_v" FLOAT;

187 BEGIN

188 UPDATE "suggestion" SET "harmonic_weight" = NULL

189 WHERE "initiative_id" = "initiative_id_p";

190 LOOP

191 "min_weight_v" := NULL;

192 "i" := 0;

193 "count_v" := 0;

194 FOR "weight_row" IN

195 SELECT * FROM "remaining_harmonic_suggestion_weight_summands"

196 WHERE "initiative_id" = "initiative_id_p"

197 ORDER BY "suggestion_id" DESC, "weight_den" DESC

198 -- NOTE: latest suggestions treated worse

199 LOOP

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

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

202 "i" := "i" + 1;

203 "count_v" := "i";

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

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

206 ELSE

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

208 END IF;

209 END LOOP;

210 EXIT WHEN "count_v" = 0;

211 "i" := 1;

212 LOOP

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

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

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

216 END IF;

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

218 EXIT WHEN "i" > "count_v";

219 END LOOP;

220 "i" := 1;

221 LOOP

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

223 UPDATE "suggestion" SET "harmonic_weight" = "min_weight_v"

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

225 EXIT;

226 END IF;

227 "i" := "i" + 1;

228 END LOOP;

229 END LOOP;

230 UPDATE "suggestion" SET "harmonic_weight" = 0

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

232 END;

233 $$;

234

235 COMMENT ON FUNCTION "set_harmonic_suggestion_weights"

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

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

238

239 CREATE OR REPLACE FUNCTION "create_snapshot"

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

241 RETURNS VOID

242 LANGUAGE 'plpgsql' VOLATILE AS $$

243 DECLARE

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

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

246 BEGIN

247 PERFORM "lock_issue"("issue_id_p");

248 PERFORM "create_population_snapshot"("issue_id_p");

249 PERFORM "create_interest_snapshot"("issue_id_p");

250 UPDATE "issue" SET

251 "snapshot" = now(),

252 "latest_snapshot_event" = 'periodic',

253 "population" = (

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

255 FROM "direct_population_snapshot"

256 WHERE "issue_id" = "issue_id_p"

257 AND "event" = 'periodic'

258 )

259 WHERE "id" = "issue_id_p";

260 FOR "initiative_id_v" IN

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

262 LOOP

263 UPDATE "initiative" SET

264 "supporter_count" = (

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

266 FROM "direct_interest_snapshot" AS "di"

267 JOIN "direct_supporter_snapshot" AS "ds"

268 ON "di"."member_id" = "ds"."member_id"

269 WHERE "di"."issue_id" = "issue_id_p"

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

271 AND "ds"."initiative_id" = "initiative_id_v"

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

273 ),

274 "informed_supporter_count" = (

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

276 FROM "direct_interest_snapshot" AS "di"

277 JOIN "direct_supporter_snapshot" AS "ds"

278 ON "di"."member_id" = "ds"."member_id"

279 WHERE "di"."issue_id" = "issue_id_p"

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

281 AND "ds"."initiative_id" = "initiative_id_v"

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

283 AND "ds"."informed"

284 ),

285 "satisfied_supporter_count" = (

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

287 FROM "direct_interest_snapshot" AS "di"

288 JOIN "direct_supporter_snapshot" AS "ds"

289 ON "di"."member_id" = "ds"."member_id"

290 WHERE "di"."issue_id" = "issue_id_p"

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

292 AND "ds"."initiative_id" = "initiative_id_v"

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

294 AND "ds"."satisfied"

295 ),

296 "satisfied_informed_supporter_count" = (

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

298 FROM "direct_interest_snapshot" AS "di"

299 JOIN "direct_supporter_snapshot" AS "ds"

300 ON "di"."member_id" = "ds"."member_id"

301 WHERE "di"."issue_id" = "issue_id_p"

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

303 AND "ds"."initiative_id" = "initiative_id_v"

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

305 AND "ds"."informed"

306 AND "ds"."satisfied"

307 )

308 WHERE "id" = "initiative_id_v";

309 FOR "suggestion_id_v" IN

310 SELECT "id" FROM "suggestion"

311 WHERE "initiative_id" = "initiative_id_v"

312 LOOP

313 UPDATE "suggestion" SET

314 "minus2_unfulfilled_count" = (

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

316 FROM "issue" CROSS JOIN "opinion"

317 JOIN "direct_interest_snapshot" AS "snapshot"

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

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

320 AND "snapshot"."member_id" = "opinion"."member_id"

321 WHERE "issue"."id" = "issue_id_p"

322 AND "opinion"."suggestion_id" = "suggestion_id_v"

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

324 AND "opinion"."fulfilled" = FALSE

325 ),

326 "minus2_fulfilled_count" = (

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

328 FROM "issue" CROSS JOIN "opinion"

329 JOIN "direct_interest_snapshot" AS "snapshot"

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

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

332 AND "snapshot"."member_id" = "opinion"."member_id"

333 WHERE "issue"."id" = "issue_id_p"

334 AND "opinion"."suggestion_id" = "suggestion_id_v"

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

336 AND "opinion"."fulfilled" = TRUE

337 ),

338 "minus1_unfulfilled_count" = (

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

340 FROM "issue" CROSS JOIN "opinion"

341 JOIN "direct_interest_snapshot" AS "snapshot"

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

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

344 AND "snapshot"."member_id" = "opinion"."member_id"

345 WHERE "issue"."id" = "issue_id_p"

346 AND "opinion"."suggestion_id" = "suggestion_id_v"

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

348 AND "opinion"."fulfilled" = FALSE

349 ),

350 "minus1_fulfilled_count" = (

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

352 FROM "issue" CROSS JOIN "opinion"

353 JOIN "direct_interest_snapshot" AS "snapshot"

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

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

356 AND "snapshot"."member_id" = "opinion"."member_id"

357 WHERE "issue"."id" = "issue_id_p"

358 AND "opinion"."suggestion_id" = "suggestion_id_v"

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

360 AND "opinion"."fulfilled" = TRUE

361 ),

362 "plus1_unfulfilled_count" = (

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

364 FROM "issue" CROSS JOIN "opinion"

365 JOIN "direct_interest_snapshot" AS "snapshot"

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

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

368 AND "snapshot"."member_id" = "opinion"."member_id"

369 WHERE "issue"."id" = "issue_id_p"

370 AND "opinion"."suggestion_id" = "suggestion_id_v"

371 AND "opinion"."degree" = 1

372 AND "opinion"."fulfilled" = FALSE

373 ),

374 "plus1_fulfilled_count" = (

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

376 FROM "issue" CROSS JOIN "opinion"

377 JOIN "direct_interest_snapshot" AS "snapshot"

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

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

380 AND "snapshot"."member_id" = "opinion"."member_id"

381 WHERE "issue"."id" = "issue_id_p"

382 AND "opinion"."suggestion_id" = "suggestion_id_v"

383 AND "opinion"."degree" = 1

384 AND "opinion"."fulfilled" = TRUE

385 ),

386 "plus2_unfulfilled_count" = (

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

388 FROM "issue" CROSS JOIN "opinion"

389 JOIN "direct_interest_snapshot" AS "snapshot"

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

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

392 AND "snapshot"."member_id" = "opinion"."member_id"

393 WHERE "issue"."id" = "issue_id_p"

394 AND "opinion"."suggestion_id" = "suggestion_id_v"

395 AND "opinion"."degree" = 2

396 AND "opinion"."fulfilled" = FALSE

397 ),

398 "plus2_fulfilled_count" = (

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

400 FROM "issue" CROSS JOIN "opinion"

401 JOIN "direct_interest_snapshot" AS "snapshot"

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

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

404 AND "snapshot"."member_id" = "opinion"."member_id"

405 WHERE "issue"."id" = "issue_id_p"

406 AND "opinion"."suggestion_id" = "suggestion_id_v"

407 AND "opinion"."degree" = 2

408 AND "opinion"."fulfilled" = TRUE

409 )

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

411 END LOOP;

412 PERFORM "set_harmonic_suggestion_weights"("initiative_id_v");

413 END LOOP;

414 PERFORM "set_harmonic_initiative_weights"("issue_id_p");

415 RETURN;

416 END;

417 $$;

418

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

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

421

422 COMMIT;