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

liquid_feedback_core

view update/core-update.v2.1.0-v2.1.1.sql @ 315:3e450a518197

Update script for "harmonic_weight" feature

author	jbe
date	Fri Feb 01 20:14:43 2013 +0100 (2013-02-01)
parents
children	727926e290e7

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

116 $$;

117

118 COMMENT ON FUNCTION "set_harmonic_initiative_weights"

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

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

121

122 CREATE VIEW "remaining_harmonic_opinion_weight" AS

123 SELECT

124 "initiative"."issue_id",

125 "opinion"."initiative_id",

126 "direct_interest_snapshot"."member_id",

127 "direct_interest_snapshot"."weight" AS "weight_num",

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

129 FROM "issue"

130 JOIN "direct_interest_snapshot"

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

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

133 JOIN "initiative"

134 ON "direct_interest_snapshot"."issue_id" = "initiative"."issue_id"

135 JOIN "opinion"

136 ON "direct_interest_snapshot"."member_id" = "opinion"."member_id"

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

138 AND "opinion"."degree" > 0

139 GROUP BY

140 "initiative"."issue_id",

141 "opinion"."initiative_id",

142 "direct_interest_snapshot"."member_id",

143 "direct_interest_snapshot"."weight";

144

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

146

147 CREATE VIEW "remaining_harmonic_suggestion_weight_summands" AS

148 SELECT

149 "suggestion"."initiative_id",

150 "opinion"."suggestion_id",

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

152 "remaining_harmonic_opinion_weight"."weight_den"

153 FROM "remaining_harmonic_opinion_weight"

154 JOIN "opinion"

155 ON "remaining_harmonic_opinion_weight"."initiative_id" = "opinion"."initiative_id"

156 AND "remaining_harmonic_opinion_weight"."member_id" = "opinion"."member_id"

157 JOIN "suggestion"

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

159 AND "suggestion"."harmonic_weight" ISNULL

160 GROUP BY

161 "suggestion"."initiative_id",

162 "opinion"."suggestion_id",

163 "remaining_harmonic_opinion_weight"."weight_den";

164

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

166

167 CREATE FUNCTION "set_harmonic_suggestion_weights"

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

169 RETURNS VOID

170 LANGUAGE 'plpgsql' VOLATILE AS $$

171 DECLARE

172 "weight_row" "remaining_harmonic_suggestion_weight_summands"%ROWTYPE;

173 "i" INT4;

174 "count_v" INT4;

175 "summand_v" FLOAT;

176 "id_ary" INT4[];

177 "weight_ary" FLOAT[];

178 "min_weight_v" FLOAT;

179 BEGIN

180 UPDATE "suggestion" SET "harmonic_weight" = NULL

181 WHERE "initiative_id" = "initiative_id_p";

182 LOOP

183 "min_weight_v" := NULL;

184 "i" := 0;

185 "count_v" := 0;

186 FOR "weight_row" IN

187 SELECT * FROM "remaining_harmonic_suggestion_weight_summands"

188 WHERE "initiative_id" = "initiative_id_p"

189 ORDER BY "suggestion_id" DESC, "weight_den" DESC

190 -- NOTE: latest suggestions treated worse

191 LOOP

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

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

194 "i" := "i" + 1;

195 "count_v" := "i";

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

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

198 ELSE

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

200 END IF;

201 END LOOP;

202 EXIT WHEN "count_v" = 0;

203 "i" := 1;

204 LOOP

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

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

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

208 END IF;

209 "i" := "i" + 1;

210 EXIT WHEN "i" > "count_v";

211 END LOOP;

212 "i" := 1;

213 LOOP

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

215 UPDATE "suggestion" SET "harmonic_weight" = "min_weight_v"

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

217 EXIT;

218 END IF;

219 "i" := "i" + 1;

220 END LOOP;

221 END LOOP;

222 END;

223 $$;

224

225 COMMENT ON FUNCTION "set_harmonic_suggestion_weights"

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

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

228

229 CREATE OR REPLACE FUNCTION "create_snapshot"

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

231 RETURNS VOID

232 LANGUAGE 'plpgsql' VOLATILE AS $$

233 DECLARE

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

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

236 BEGIN

237 PERFORM "lock_issue"("issue_id_p");

238 PERFORM "create_population_snapshot"("issue_id_p");

239 PERFORM "create_interest_snapshot"("issue_id_p");

240 UPDATE "issue" SET

241 "snapshot" = now(),

242 "latest_snapshot_event" = 'periodic',

243 "population" = (

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

245 FROM "direct_population_snapshot"

246 WHERE "issue_id" = "issue_id_p"

247 AND "event" = 'periodic'

248 )

249 WHERE "id" = "issue_id_p";

250 FOR "initiative_id_v" IN

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

252 LOOP

253 UPDATE "initiative" SET

254 "supporter_count" = (

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

256 FROM "direct_interest_snapshot" AS "di"

257 JOIN "direct_supporter_snapshot" AS "ds"

258 ON "di"."member_id" = "ds"."member_id"

259 WHERE "di"."issue_id" = "issue_id_p"

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

261 AND "ds"."initiative_id" = "initiative_id_v"

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

263 ),

264 "informed_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 AND "ds"."informed"

274 ),

275 "satisfied_supporter_count" = (

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

277 FROM "direct_interest_snapshot" AS "di"

278 JOIN "direct_supporter_snapshot" AS "ds"

279 ON "di"."member_id" = "ds"."member_id"

280 WHERE "di"."issue_id" = "issue_id_p"

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

282 AND "ds"."initiative_id" = "initiative_id_v"

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

284 AND "ds"."satisfied"

285 ),

286 "satisfied_informed_supporter_count" = (

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

288 FROM "direct_interest_snapshot" AS "di"

289 JOIN "direct_supporter_snapshot" AS "ds"

290 ON "di"."member_id" = "ds"."member_id"

291 WHERE "di"."issue_id" = "issue_id_p"

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

293 AND "ds"."initiative_id" = "initiative_id_v"

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

295 AND "ds"."informed"

296 AND "ds"."satisfied"

297 )

298 WHERE "id" = "initiative_id_v";

299 FOR "suggestion_id_v" IN

300 SELECT "id" FROM "suggestion"

301 WHERE "initiative_id" = "initiative_id_v"

302 LOOP

303 UPDATE "suggestion" SET

304 "minus2_unfulfilled_count" = (

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

306 FROM "issue" CROSS JOIN "opinion"

307 JOIN "direct_interest_snapshot" AS "snapshot"

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

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

310 AND "snapshot"."member_id" = "opinion"."member_id"

311 WHERE "issue"."id" = "issue_id_p"

312 AND "opinion"."suggestion_id" = "suggestion_id_v"

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

314 AND "opinion"."fulfilled" = FALSE

315 ),

316 "minus2_fulfilled_count" = (

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

318 FROM "issue" CROSS JOIN "opinion"

319 JOIN "direct_interest_snapshot" AS "snapshot"

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

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

322 AND "snapshot"."member_id" = "opinion"."member_id"

323 WHERE "issue"."id" = "issue_id_p"

324 AND "opinion"."suggestion_id" = "suggestion_id_v"

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

326 AND "opinion"."fulfilled" = TRUE

327 ),

328 "minus1_unfulfilled_count" = (

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

330 FROM "issue" CROSS JOIN "opinion"

331 JOIN "direct_interest_snapshot" AS "snapshot"

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

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

334 AND "snapshot"."member_id" = "opinion"."member_id"

335 WHERE "issue"."id" = "issue_id_p"

336 AND "opinion"."suggestion_id" = "suggestion_id_v"

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

338 AND "opinion"."fulfilled" = FALSE

339 ),

340 "minus1_fulfilled_count" = (

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

342 FROM "issue" CROSS JOIN "opinion"

343 JOIN "direct_interest_snapshot" AS "snapshot"

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

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

346 AND "snapshot"."member_id" = "opinion"."member_id"

347 WHERE "issue"."id" = "issue_id_p"

348 AND "opinion"."suggestion_id" = "suggestion_id_v"

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

350 AND "opinion"."fulfilled" = TRUE

351 ),

352 "plus1_unfulfilled_count" = (

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

354 FROM "issue" CROSS JOIN "opinion"

355 JOIN "direct_interest_snapshot" AS "snapshot"

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

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

358 AND "snapshot"."member_id" = "opinion"."member_id"

359 WHERE "issue"."id" = "issue_id_p"

360 AND "opinion"."suggestion_id" = "suggestion_id_v"

361 AND "opinion"."degree" = 1

362 AND "opinion"."fulfilled" = FALSE

363 ),

364 "plus1_fulfilled_count" = (

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

366 FROM "issue" CROSS JOIN "opinion"

367 JOIN "direct_interest_snapshot" AS "snapshot"

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

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

370 AND "snapshot"."member_id" = "opinion"."member_id"

371 WHERE "issue"."id" = "issue_id_p"

372 AND "opinion"."suggestion_id" = "suggestion_id_v"

373 AND "opinion"."degree" = 1

374 AND "opinion"."fulfilled" = TRUE

375 ),

376 "plus2_unfulfilled_count" = (

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

378 FROM "issue" CROSS JOIN "opinion"

379 JOIN "direct_interest_snapshot" AS "snapshot"

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

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

382 AND "snapshot"."member_id" = "opinion"."member_id"

383 WHERE "issue"."id" = "issue_id_p"

384 AND "opinion"."suggestion_id" = "suggestion_id_v"

385 AND "opinion"."degree" = 2

386 AND "opinion"."fulfilled" = FALSE

387 ),

388 "plus2_fulfilled_count" = (

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

390 FROM "issue" CROSS JOIN "opinion"

391 JOIN "direct_interest_snapshot" AS "snapshot"

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

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

394 AND "snapshot"."member_id" = "opinion"."member_id"

395 WHERE "issue"."id" = "issue_id_p"

396 AND "opinion"."suggestion_id" = "suggestion_id_v"

397 AND "opinion"."degree" = 2

398 AND "opinion"."fulfilled" = TRUE

399 )

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

401 END LOOP;

402 PERFORM "set_harmonic_suggestion_weights"("initiative_id_v");

403 END LOOP;

404 PERFORM "set_harmonic_initiative_weights"("issue_id_p");

405 RETURN;

406 END;

407 $$;

408

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

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

411

412 COMMIT;