liquid_feedback_core: 48a5036d5eb1 update/core-update.v2.1.0-v2.1.1.sql

liquid_feedback_core

view update/core-update.v2.1.0-v2.1.1.sql @ 321:48a5036d5eb1

Require "satisfied" support for "admitted" initiatives when calculating "harmonic_weight"

author	jbe
date	Fri Feb 08 18:36:22 2013 +0100 (2013-02-08)
parents	3a7f06a88ecb
children	fd58f487e1d0

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; shall be used as secondary sorting key after "admitted" as primary sorting key';

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 (

31 "direct_supporter_snapshot"."satisfied" = TRUE OR

32 coalesce("initiative"."admitted", FALSE) = FALSE

33 )

34 AND "initiative"."harmonic_weight" ISNULL

35 GROUP BY

36 "direct_interest_snapshot"."issue_id",

37 "direct_interest_snapshot"."event",

38 "direct_interest_snapshot"."member_id",

39 "direct_interest_snapshot"."weight";

41 COMMENT ON VIEW "remaining_harmonic_supporter_weight" IS 'Helper view for function "set_harmonic_initiative_weights"';

43 CREATE VIEW "remaining_harmonic_initiative_weight_summands" AS

44 SELECT

45 "initiative"."issue_id",

46 "initiative"."id" AS "initiative_id",

47 "initiative"."admitted",

48 sum("remaining_harmonic_supporter_weight"."weight_num") AS "weight_num",

49 "remaining_harmonic_supporter_weight"."weight_den"

50 FROM "remaining_harmonic_supporter_weight"

51 JOIN "direct_supporter_snapshot"

52 ON "remaining_harmonic_supporter_weight"."issue_id" = "direct_supporter_snapshot"."issue_id"

53 AND "remaining_harmonic_supporter_weight"."event" = "direct_supporter_snapshot"."event"

54 AND "remaining_harmonic_supporter_weight"."member_id" = "direct_supporter_snapshot"."member_id"

55 JOIN "initiative"

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

57 AND (

58 "direct_supporter_snapshot"."satisfied" = TRUE OR

59 coalesce("initiative"."admitted", FALSE) = FALSE

60 )

61 AND "initiative"."harmonic_weight" ISNULL

62 GROUP BY

63 "initiative"."issue_id",

64 "initiative"."id",

65 "initiative"."admitted",

66 "remaining_harmonic_supporter_weight"."weight_den";

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

70 CREATE FUNCTION "set_harmonic_initiative_weights"

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

72 RETURNS VOID

73 LANGUAGE 'plpgsql' VOLATILE AS $$

74 DECLARE

75 "weight_row" "remaining_harmonic_initiative_weight_summands"%ROWTYPE;

76 "i" INT4;

77 "count_v" INT4;

78 "summand_v" FLOAT;

79 "id_ary" INT4[];

80 "weight_ary" FLOAT[];

81 "min_weight_v" FLOAT;

82 BEGIN

83 UPDATE "initiative" SET "harmonic_weight" = NULL

84 WHERE "issue_id" = "issue_id_p";

85 LOOP

86 "min_weight_v" := NULL;

87 "i" := 0;

88 "count_v" := 0;

89 FOR "weight_row" IN

90 SELECT * FROM "remaining_harmonic_initiative_weight_summands"

91 WHERE "issue_id" = "issue_id_p"

92 AND (

93 coalesce("admitted", FALSE) = FALSE OR NOT EXISTS (

94 SELECT NULL FROM "initiative"

95 WHERE "issue_id" = "issue_id_p"

96 AND "harmonic_weight" ISNULL

97 AND coalesce("admitted", FALSE) = FALSE

98 )

99 )

100 ORDER BY "initiative_id" DESC, "weight_den" DESC

101 -- NOTE: non-admitted initiatives placed first (at last positions),

102 -- latest initiatives treated worse in case of tie

103 LOOP

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

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

106 "i" := "i" + 1;

107 "count_v" := "i";

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

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

110 ELSE

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

112 END IF;

113 END LOOP;

114 EXIT WHEN "count_v" = 0;

115 "i" := 1;

116 LOOP

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

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

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

120 END IF;

121 "i" := "i" + 1;

122 EXIT WHEN "i" > "count_v";

123 END LOOP;

124 "i" := 1;

125 LOOP

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

127 UPDATE "initiative" SET "harmonic_weight" = "min_weight_v"

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

129 EXIT;

130 END IF;

131 "i" := "i" + 1;

132 END LOOP;

133 END LOOP;

134 UPDATE "initiative" SET "harmonic_weight" = 0

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

136 END;

137 $$;

138

139 COMMENT ON FUNCTION "set_harmonic_initiative_weights"

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

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

142

143 CREATE VIEW "remaining_harmonic_opinion_weight" AS

144 SELECT

145 "initiative"."issue_id",

146 "opinion"."initiative_id",

147 "direct_interest_snapshot"."member_id",

148 "direct_interest_snapshot"."weight" AS "weight_num",

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

150 FROM "issue"

151 JOIN "direct_interest_snapshot"

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

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

154 JOIN "initiative"

155 ON "direct_interest_snapshot"."issue_id" = "initiative"."issue_id"

156 JOIN "opinion"

157 ON "direct_interest_snapshot"."member_id" = "opinion"."member_id"

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

159 AND (

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

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

162 )

163 JOIN "suggestion"

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

165 AND "suggestion"."harmonic_weight" ISNULL

166 GROUP BY

167 "initiative"."issue_id",

168 "opinion"."initiative_id",

169 "direct_interest_snapshot"."member_id",

170 "direct_interest_snapshot"."weight";

171

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

173

174 CREATE VIEW "remaining_harmonic_suggestion_weight_summands" AS

175 SELECT

176 "suggestion"."initiative_id",

177 "opinion"."suggestion_id",

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

179 "remaining_harmonic_opinion_weight"."weight_den"

180 FROM "remaining_harmonic_opinion_weight"

181 JOIN "opinion"

182 ON "remaining_harmonic_opinion_weight"."initiative_id" = "opinion"."initiative_id"

183 AND "remaining_harmonic_opinion_weight"."member_id" = "opinion"."member_id"

184 AND (

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

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

187 )

188 JOIN "suggestion"

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

190 AND "suggestion"."harmonic_weight" ISNULL

191 GROUP BY

192 "suggestion"."initiative_id",

193 "opinion"."suggestion_id",

194 "remaining_harmonic_opinion_weight"."weight_den";

195

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

197

198 CREATE FUNCTION "set_harmonic_suggestion_weights"

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

200 RETURNS VOID

201 LANGUAGE 'plpgsql' VOLATILE AS $$

202 DECLARE

203 "weight_row" "remaining_harmonic_suggestion_weight_summands"%ROWTYPE;

204 "i" INT4;

205 "count_v" INT4;

206 "summand_v" FLOAT;

207 "id_ary" INT4[];

208 "weight_ary" FLOAT[];

209 "min_weight_v" FLOAT;

210 BEGIN

211 UPDATE "suggestion" SET "harmonic_weight" = NULL

212 WHERE "initiative_id" = "initiative_id_p";

213 LOOP

214 "min_weight_v" := NULL;

215 "i" := 0;

216 "count_v" := 0;

217 FOR "weight_row" IN

218 SELECT * FROM "remaining_harmonic_suggestion_weight_summands"

219 WHERE "initiative_id" = "initiative_id_p"

220 ORDER BY "suggestion_id" DESC, "weight_den" DESC

221 -- NOTE: latest suggestions treated worse in case of tie

222 LOOP

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

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

225 "i" := "i" + 1;

226 "count_v" := "i";

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

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

229 ELSE

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

231 END IF;

232 END LOOP;

233 EXIT WHEN "count_v" = 0;

234 "i" := 1;

235 LOOP

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

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

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

239 END IF;

240 "i" := "i" + 1;

241 EXIT WHEN "i" > "count_v";

242 END LOOP;

243 "i" := 1;

244 LOOP

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

246 UPDATE "suggestion" SET "harmonic_weight" = "min_weight_v"

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

248 EXIT;

249 END IF;

250 "i" := "i" + 1;

251 END LOOP;

252 END LOOP;

253 UPDATE "suggestion" SET "harmonic_weight" = 0

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

255 END;

256 $$;

257

258 COMMENT ON FUNCTION "set_harmonic_suggestion_weights"

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

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

261

262 CREATE OR REPLACE FUNCTION "create_snapshot"

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

264 RETURNS VOID

265 LANGUAGE 'plpgsql' VOLATILE AS $$

266 DECLARE

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

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

269 BEGIN

270 PERFORM "lock_issue"("issue_id_p");

271 PERFORM "create_population_snapshot"("issue_id_p");

272 PERFORM "create_interest_snapshot"("issue_id_p");

273 UPDATE "issue" SET

274 "snapshot" = now(),

275 "latest_snapshot_event" = 'periodic',

276 "population" = (

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

278 FROM "direct_population_snapshot"

279 WHERE "issue_id" = "issue_id_p"

280 AND "event" = 'periodic'

281 )

282 WHERE "id" = "issue_id_p";

283 FOR "initiative_id_v" IN

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

285 LOOP

286 UPDATE "initiative" SET

287 "supporter_count" = (

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

289 FROM "direct_interest_snapshot" AS "di"

290 JOIN "direct_supporter_snapshot" AS "ds"

291 ON "di"."member_id" = "ds"."member_id"

292 WHERE "di"."issue_id" = "issue_id_p"

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

294 AND "ds"."initiative_id" = "initiative_id_v"

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

296 ),

297 "informed_supporter_count" = (

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

299 FROM "direct_interest_snapshot" AS "di"

300 JOIN "direct_supporter_snapshot" AS "ds"

301 ON "di"."member_id" = "ds"."member_id"

302 WHERE "di"."issue_id" = "issue_id_p"

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

304 AND "ds"."initiative_id" = "initiative_id_v"

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

306 AND "ds"."informed"

307 ),

308 "satisfied_supporter_count" = (

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

310 FROM "direct_interest_snapshot" AS "di"

311 JOIN "direct_supporter_snapshot" AS "ds"

312 ON "di"."member_id" = "ds"."member_id"

313 WHERE "di"."issue_id" = "issue_id_p"

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

315 AND "ds"."initiative_id" = "initiative_id_v"

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

317 AND "ds"."satisfied"

318 ),

319 "satisfied_informed_supporter_count" = (

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

321 FROM "direct_interest_snapshot" AS "di"

322 JOIN "direct_supporter_snapshot" AS "ds"

323 ON "di"."member_id" = "ds"."member_id"

324 WHERE "di"."issue_id" = "issue_id_p"

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

326 AND "ds"."initiative_id" = "initiative_id_v"

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

328 AND "ds"."informed"

329 AND "ds"."satisfied"

330 )

331 WHERE "id" = "initiative_id_v";

332 FOR "suggestion_id_v" IN

333 SELECT "id" FROM "suggestion"

334 WHERE "initiative_id" = "initiative_id_v"

335 LOOP

336 UPDATE "suggestion" SET

337 "minus2_unfulfilled_count" = (

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

339 FROM "issue" CROSS JOIN "opinion"

340 JOIN "direct_interest_snapshot" AS "snapshot"

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

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

343 AND "snapshot"."member_id" = "opinion"."member_id"

344 WHERE "issue"."id" = "issue_id_p"

345 AND "opinion"."suggestion_id" = "suggestion_id_v"

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

347 AND "opinion"."fulfilled" = FALSE

348 ),

349 "minus2_fulfilled_count" = (

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

351 FROM "issue" CROSS JOIN "opinion"

352 JOIN "direct_interest_snapshot" AS "snapshot"

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

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

355 AND "snapshot"."member_id" = "opinion"."member_id"

356 WHERE "issue"."id" = "issue_id_p"

357 AND "opinion"."suggestion_id" = "suggestion_id_v"

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

359 AND "opinion"."fulfilled" = TRUE

360 ),

361 "minus1_unfulfilled_count" = (

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

363 FROM "issue" CROSS JOIN "opinion"

364 JOIN "direct_interest_snapshot" AS "snapshot"

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

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

367 AND "snapshot"."member_id" = "opinion"."member_id"

368 WHERE "issue"."id" = "issue_id_p"

369 AND "opinion"."suggestion_id" = "suggestion_id_v"

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

371 AND "opinion"."fulfilled" = FALSE

372 ),

373 "minus1_fulfilled_count" = (

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

375 FROM "issue" CROSS JOIN "opinion"

376 JOIN "direct_interest_snapshot" AS "snapshot"

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

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

379 AND "snapshot"."member_id" = "opinion"."member_id"

380 WHERE "issue"."id" = "issue_id_p"

381 AND "opinion"."suggestion_id" = "suggestion_id_v"

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

383 AND "opinion"."fulfilled" = TRUE

384 ),

385 "plus1_unfulfilled_count" = (

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

387 FROM "issue" CROSS JOIN "opinion"

388 JOIN "direct_interest_snapshot" AS "snapshot"

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

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

391 AND "snapshot"."member_id" = "opinion"."member_id"

392 WHERE "issue"."id" = "issue_id_p"

393 AND "opinion"."suggestion_id" = "suggestion_id_v"

394 AND "opinion"."degree" = 1

395 AND "opinion"."fulfilled" = FALSE

396 ),

397 "plus1_fulfilled_count" = (

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

399 FROM "issue" CROSS JOIN "opinion"

400 JOIN "direct_interest_snapshot" AS "snapshot"

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

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

403 AND "snapshot"."member_id" = "opinion"."member_id"

404 WHERE "issue"."id" = "issue_id_p"

405 AND "opinion"."suggestion_id" = "suggestion_id_v"

406 AND "opinion"."degree" = 1

407 AND "opinion"."fulfilled" = TRUE

408 ),

409 "plus2_unfulfilled_count" = (

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

411 FROM "issue" CROSS JOIN "opinion"

412 JOIN "direct_interest_snapshot" AS "snapshot"

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

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

415 AND "snapshot"."member_id" = "opinion"."member_id"

416 WHERE "issue"."id" = "issue_id_p"

417 AND "opinion"."suggestion_id" = "suggestion_id_v"

418 AND "opinion"."degree" = 2

419 AND "opinion"."fulfilled" = FALSE

420 ),

421 "plus2_fulfilled_count" = (

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

423 FROM "issue" CROSS JOIN "opinion"

424 JOIN "direct_interest_snapshot" AS "snapshot"

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

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

427 AND "snapshot"."member_id" = "opinion"."member_id"

428 WHERE "issue"."id" = "issue_id_p"

429 AND "opinion"."suggestion_id" = "suggestion_id_v"

430 AND "opinion"."degree" = 2

431 AND "opinion"."fulfilled" = TRUE

432 )

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

434 END LOOP;

435 PERFORM "set_harmonic_suggestion_weights"("initiative_id_v");

436 END LOOP;

437 PERFORM "set_harmonic_initiative_weights"("issue_id_p");

438 RETURN;

439 END;

440 $$;

441

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

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

444

445 COMMIT;