liquid_feedback_core
changeset 315:3e450a518197
Update script for "harmonic_weight" feature
author | jbe |
---|---|
date | Fri Feb 01 20:14:43 2013 +0100 (2013-02-01) |
parents | 5289d62c028c |
children | 727926e290e7 |
files | update/core-update.v2.1.0-v2.1.1.sql |
line diff
1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000 1.2 +++ b/update/core-update.v2.1.0-v2.1.1.sql Fri Feb 01 20:14:43 2013 +0100 1.3 @@ -0,0 +1,412 @@ 1.4 +BEGIN; 1.5 + 1.6 +CREATE OR REPLACE VIEW "liquid_feedback_version" AS 1.7 + SELECT * FROM (VALUES ('2.1.1', 2, 1, 1)) 1.8 + AS "subquery"("string", "major", "minor", "revision"); 1.9 + 1.10 +ALTER TABLE "initiative" ADD COLUMN "harmonic_weight" NUMERIC(12, 3); 1.11 +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'; 1.12 + 1.13 +ALTER TABLE "suggestion" ADD COLUMN "harmonic_weight" NUMERIC(12, 3); 1.14 +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'; 1.15 + 1.16 +CREATE VIEW "remaining_harmonic_supporter_weight" AS 1.17 + SELECT 1.18 + "direct_interest_snapshot"."issue_id", 1.19 + "direct_interest_snapshot"."event", 1.20 + "direct_interest_snapshot"."member_id", 1.21 + "direct_interest_snapshot"."weight" AS "weight_num", 1.22 + count("initiative"."id") AS "weight_den" 1.23 + FROM "issue" 1.24 + JOIN "direct_interest_snapshot" 1.25 + ON "issue"."id" = "direct_interest_snapshot"."issue_id" 1.26 + AND "issue"."latest_snapshot_event" = "direct_interest_snapshot"."event" 1.27 + JOIN "direct_supporter_snapshot" 1.28 + ON "direct_interest_snapshot"."issue_id" = "direct_supporter_snapshot"."issue_id" 1.29 + AND "direct_interest_snapshot"."event" = "direct_supporter_snapshot"."event" 1.30 + AND "direct_interest_snapshot"."member_id" = "direct_supporter_snapshot"."member_id" 1.31 + JOIN "initiative" 1.32 + ON "direct_supporter_snapshot"."initiative_id" = "initiative"."id" 1.33 + AND "initiative"."harmonic_weight" ISNULL 1.34 + GROUP BY 1.35 + "direct_interest_snapshot"."issue_id", 1.36 + "direct_interest_snapshot"."event", 1.37 + "direct_interest_snapshot"."member_id", 1.38 + "direct_interest_snapshot"."weight"; 1.39 + 1.40 +COMMENT ON VIEW "remaining_harmonic_supporter_weight" IS 'Helper view for function "set_harmonic_initiative_weights"'; 1.41 + 1.42 +CREATE VIEW "remaining_harmonic_initiative_weight_summands" AS 1.43 + SELECT 1.44 + "initiative"."issue_id", 1.45 + "initiative"."id" AS "initiative_id", 1.46 + sum("remaining_harmonic_supporter_weight"."weight_num") AS "weight_num", 1.47 + "remaining_harmonic_supporter_weight"."weight_den" 1.48 + FROM "remaining_harmonic_supporter_weight" 1.49 + JOIN "direct_supporter_snapshot" 1.50 + ON "remaining_harmonic_supporter_weight"."issue_id" = "direct_supporter_snapshot"."issue_id" 1.51 + AND "remaining_harmonic_supporter_weight"."event" = "direct_supporter_snapshot"."event" 1.52 + AND "remaining_harmonic_supporter_weight"."member_id" = "direct_supporter_snapshot"."member_id" 1.53 + JOIN "initiative" 1.54 + ON "direct_supporter_snapshot"."initiative_id" = "initiative"."id" 1.55 + AND "initiative"."harmonic_weight" ISNULL 1.56 + GROUP BY 1.57 + "initiative"."issue_id", 1.58 + "initiative"."id", 1.59 + "remaining_harmonic_supporter_weight"."weight_den"; 1.60 + 1.61 +COMMENT ON VIEW "remaining_harmonic_initiative_weight_summands" IS 'Helper view for function "set_harmonic_initiative_weights"'; 1.62 + 1.63 +CREATE FUNCTION "set_harmonic_initiative_weights" 1.64 + ( "issue_id_p" "issue"."id"%TYPE ) 1.65 + RETURNS VOID 1.66 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.67 + DECLARE 1.68 + "weight_row" "remaining_harmonic_initiative_weight_summands"%ROWTYPE; 1.69 + "i" INT4; 1.70 + "count_v" INT4; 1.71 + "summand_v" FLOAT; 1.72 + "id_ary" INT4[]; 1.73 + "weight_ary" FLOAT[]; 1.74 + "min_weight_v" FLOAT; 1.75 + BEGIN 1.76 + UPDATE "initiative" SET "harmonic_weight" = NULL 1.77 + WHERE "issue_id" = "issue_id_p"; 1.78 + LOOP 1.79 + "min_weight_v" := NULL; 1.80 + "i" := 0; 1.81 + "count_v" := 0; 1.82 + FOR "weight_row" IN 1.83 + SELECT * FROM "remaining_harmonic_initiative_weight_summands" 1.84 + WHERE "issue_id" = "issue_id_p" 1.85 + ORDER BY "initiative_id" DESC, "weight_den" DESC 1.86 + -- NOTE: latest initiatives treated worse 1.87 + LOOP 1.88 + "summand_v" := "weight_row"."weight_num"::FLOAT / "weight_row"."weight_den"::FLOAT; 1.89 + IF "i" = 0 OR "weight_row"."initiative_id" != "id_ary"["i"] THEN 1.90 + "i" := "i" + 1; 1.91 + "count_v" := "i"; 1.92 + "id_ary"["i"] := "weight_row"."initiative_id"; 1.93 + "weight_ary"["i"] := "summand_v"; 1.94 + ELSE 1.95 + "weight_ary"["i"] := "weight_ary"["i"] + "summand_v"; 1.96 + END IF; 1.97 + END LOOP; 1.98 + EXIT WHEN "count_v" = 0; 1.99 + "i" := 1; 1.100 + LOOP 1.101 + "weight_ary"["i"] := "weight_ary"["i"]::NUMERIC(18,9)::NUMERIC(12,3); 1.102 + IF "min_weight_v" ISNULL OR "weight_ary"["i"] < "min_weight_v" THEN 1.103 + "min_weight_v" := "weight_ary"["i"]; 1.104 + END IF; 1.105 + "i" := "i" + 1; 1.106 + EXIT WHEN "i" > "count_v"; 1.107 + END LOOP; 1.108 + "i" := 1; 1.109 + LOOP 1.110 + IF "weight_ary"["i"] = "min_weight_v" THEN 1.111 + UPDATE "initiative" SET "harmonic_weight" = "min_weight_v" 1.112 + WHERE "id" = "id_ary"["i"]; 1.113 + EXIT; 1.114 + END IF; 1.115 + "i" := "i" + 1; 1.116 + END LOOP; 1.117 + END LOOP; 1.118 + END; 1.119 + $$; 1.120 + 1.121 +COMMENT ON FUNCTION "set_harmonic_initiative_weights" 1.122 + ( "issue"."id"%TYPE ) 1.123 + IS 'Calculates and sets "harmonic_weight" of initiatives in a given issue'; 1.124 + 1.125 +CREATE VIEW "remaining_harmonic_opinion_weight" AS 1.126 + SELECT 1.127 + "initiative"."issue_id", 1.128 + "opinion"."initiative_id", 1.129 + "direct_interest_snapshot"."member_id", 1.130 + "direct_interest_snapshot"."weight" AS "weight_num", 1.131 + count("opinion"."suggestion_id") AS "weight_den" 1.132 + FROM "issue" 1.133 + JOIN "direct_interest_snapshot" 1.134 + ON "issue"."id" = "direct_interest_snapshot"."issue_id" 1.135 + AND "issue"."latest_snapshot_event" = "direct_interest_snapshot"."event" 1.136 + JOIN "initiative" 1.137 + ON "direct_interest_snapshot"."issue_id" = "initiative"."issue_id" 1.138 + JOIN "opinion" 1.139 + ON "direct_interest_snapshot"."member_id" = "opinion"."member_id" 1.140 + AND "initiative"."id" = "opinion"."initiative_id" 1.141 + AND "opinion"."degree" > 0 1.142 + GROUP BY 1.143 + "initiative"."issue_id", 1.144 + "opinion"."initiative_id", 1.145 + "direct_interest_snapshot"."member_id", 1.146 + "direct_interest_snapshot"."weight"; 1.147 + 1.148 +COMMENT ON VIEW "remaining_harmonic_opinion_weight" IS 'Helper view for function "set_harmonic_suggestion_weights"'; 1.149 + 1.150 +CREATE VIEW "remaining_harmonic_suggestion_weight_summands" AS 1.151 + SELECT 1.152 + "suggestion"."initiative_id", 1.153 + "opinion"."suggestion_id", 1.154 + sum("remaining_harmonic_opinion_weight"."weight_num") AS "weight_num", 1.155 + "remaining_harmonic_opinion_weight"."weight_den" 1.156 + FROM "remaining_harmonic_opinion_weight" 1.157 + JOIN "opinion" 1.158 + ON "remaining_harmonic_opinion_weight"."initiative_id" = "opinion"."initiative_id" 1.159 + AND "remaining_harmonic_opinion_weight"."member_id" = "opinion"."member_id" 1.160 + JOIN "suggestion" 1.161 + ON "opinion"."suggestion_id" = "suggestion"."id" 1.162 + AND "suggestion"."harmonic_weight" ISNULL 1.163 + GROUP BY 1.164 + "suggestion"."initiative_id", 1.165 + "opinion"."suggestion_id", 1.166 + "remaining_harmonic_opinion_weight"."weight_den"; 1.167 + 1.168 +COMMENT ON VIEW "remaining_harmonic_suggestion_weight_summands" IS 'Helper view for function "set_harmonic_suggestion_weights"'; 1.169 + 1.170 +CREATE FUNCTION "set_harmonic_suggestion_weights" 1.171 + ( "initiative_id_p" "initiative"."id"%TYPE ) 1.172 + RETURNS VOID 1.173 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.174 + DECLARE 1.175 + "weight_row" "remaining_harmonic_suggestion_weight_summands"%ROWTYPE; 1.176 + "i" INT4; 1.177 + "count_v" INT4; 1.178 + "summand_v" FLOAT; 1.179 + "id_ary" INT4[]; 1.180 + "weight_ary" FLOAT[]; 1.181 + "min_weight_v" FLOAT; 1.182 + BEGIN 1.183 + UPDATE "suggestion" SET "harmonic_weight" = NULL 1.184 + WHERE "initiative_id" = "initiative_id_p"; 1.185 + LOOP 1.186 + "min_weight_v" := NULL; 1.187 + "i" := 0; 1.188 + "count_v" := 0; 1.189 + FOR "weight_row" IN 1.190 + SELECT * FROM "remaining_harmonic_suggestion_weight_summands" 1.191 + WHERE "initiative_id" = "initiative_id_p" 1.192 + ORDER BY "suggestion_id" DESC, "weight_den" DESC 1.193 + -- NOTE: latest suggestions treated worse 1.194 + LOOP 1.195 + "summand_v" := "weight_row"."weight_num"::FLOAT / "weight_row"."weight_den"::FLOAT; 1.196 + IF "i" = 0 OR "weight_row"."suggestion_id" != "id_ary"["i"] THEN 1.197 + "i" := "i" + 1; 1.198 + "count_v" := "i"; 1.199 + "id_ary"["i"] := "weight_row"."suggestion_id"; 1.200 + "weight_ary"["i"] := "summand_v"; 1.201 + ELSE 1.202 + "weight_ary"["i"] := "weight_ary"["i"] + "summand_v"; 1.203 + END IF; 1.204 + END LOOP; 1.205 + EXIT WHEN "count_v" = 0; 1.206 + "i" := 1; 1.207 + LOOP 1.208 + "weight_ary"["i"] := "weight_ary"["i"]::NUMERIC(18,9)::NUMERIC(12,3); 1.209 + IF "min_weight_v" ISNULL OR "weight_ary"["i"] < "min_weight_v" THEN 1.210 + "min_weight_v" := "weight_ary"["i"]; 1.211 + END IF; 1.212 + "i" := "i" + 1; 1.213 + EXIT WHEN "i" > "count_v"; 1.214 + END LOOP; 1.215 + "i" := 1; 1.216 + LOOP 1.217 + IF "weight_ary"["i"] = "min_weight_v" THEN 1.218 + UPDATE "suggestion" SET "harmonic_weight" = "min_weight_v" 1.219 + WHERE "id" = "id_ary"["i"]; 1.220 + EXIT; 1.221 + END IF; 1.222 + "i" := "i" + 1; 1.223 + END LOOP; 1.224 + END LOOP; 1.225 + END; 1.226 + $$; 1.227 + 1.228 +COMMENT ON FUNCTION "set_harmonic_suggestion_weights" 1.229 + ( "issue"."id"%TYPE ) 1.230 + IS 'Calculates and sets "harmonic_weight" of suggestions in a given initiative'; 1.231 + 1.232 +CREATE OR REPLACE FUNCTION "create_snapshot" 1.233 + ( "issue_id_p" "issue"."id"%TYPE ) 1.234 + RETURNS VOID 1.235 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.236 + DECLARE 1.237 + "initiative_id_v" "initiative"."id"%TYPE; 1.238 + "suggestion_id_v" "suggestion"."id"%TYPE; 1.239 + BEGIN 1.240 + PERFORM "lock_issue"("issue_id_p"); 1.241 + PERFORM "create_population_snapshot"("issue_id_p"); 1.242 + PERFORM "create_interest_snapshot"("issue_id_p"); 1.243 + UPDATE "issue" SET 1.244 + "snapshot" = now(), 1.245 + "latest_snapshot_event" = 'periodic', 1.246 + "population" = ( 1.247 + SELECT coalesce(sum("weight"), 0) 1.248 + FROM "direct_population_snapshot" 1.249 + WHERE "issue_id" = "issue_id_p" 1.250 + AND "event" = 'periodic' 1.251 + ) 1.252 + WHERE "id" = "issue_id_p"; 1.253 + FOR "initiative_id_v" IN 1.254 + SELECT "id" FROM "initiative" WHERE "issue_id" = "issue_id_p" 1.255 + LOOP 1.256 + UPDATE "initiative" SET 1.257 + "supporter_count" = ( 1.258 + SELECT coalesce(sum("di"."weight"), 0) 1.259 + FROM "direct_interest_snapshot" AS "di" 1.260 + JOIN "direct_supporter_snapshot" AS "ds" 1.261 + ON "di"."member_id" = "ds"."member_id" 1.262 + WHERE "di"."issue_id" = "issue_id_p" 1.263 + AND "di"."event" = 'periodic' 1.264 + AND "ds"."initiative_id" = "initiative_id_v" 1.265 + AND "ds"."event" = 'periodic' 1.266 + ), 1.267 + "informed_supporter_count" = ( 1.268 + SELECT coalesce(sum("di"."weight"), 0) 1.269 + FROM "direct_interest_snapshot" AS "di" 1.270 + JOIN "direct_supporter_snapshot" AS "ds" 1.271 + ON "di"."member_id" = "ds"."member_id" 1.272 + WHERE "di"."issue_id" = "issue_id_p" 1.273 + AND "di"."event" = 'periodic' 1.274 + AND "ds"."initiative_id" = "initiative_id_v" 1.275 + AND "ds"."event" = 'periodic' 1.276 + AND "ds"."informed" 1.277 + ), 1.278 + "satisfied_supporter_count" = ( 1.279 + SELECT coalesce(sum("di"."weight"), 0) 1.280 + FROM "direct_interest_snapshot" AS "di" 1.281 + JOIN "direct_supporter_snapshot" AS "ds" 1.282 + ON "di"."member_id" = "ds"."member_id" 1.283 + WHERE "di"."issue_id" = "issue_id_p" 1.284 + AND "di"."event" = 'periodic' 1.285 + AND "ds"."initiative_id" = "initiative_id_v" 1.286 + AND "ds"."event" = 'periodic' 1.287 + AND "ds"."satisfied" 1.288 + ), 1.289 + "satisfied_informed_supporter_count" = ( 1.290 + SELECT coalesce(sum("di"."weight"), 0) 1.291 + FROM "direct_interest_snapshot" AS "di" 1.292 + JOIN "direct_supporter_snapshot" AS "ds" 1.293 + ON "di"."member_id" = "ds"."member_id" 1.294 + WHERE "di"."issue_id" = "issue_id_p" 1.295 + AND "di"."event" = 'periodic' 1.296 + AND "ds"."initiative_id" = "initiative_id_v" 1.297 + AND "ds"."event" = 'periodic' 1.298 + AND "ds"."informed" 1.299 + AND "ds"."satisfied" 1.300 + ) 1.301 + WHERE "id" = "initiative_id_v"; 1.302 + FOR "suggestion_id_v" IN 1.303 + SELECT "id" FROM "suggestion" 1.304 + WHERE "initiative_id" = "initiative_id_v" 1.305 + LOOP 1.306 + UPDATE "suggestion" SET 1.307 + "minus2_unfulfilled_count" = ( 1.308 + SELECT coalesce(sum("snapshot"."weight"), 0) 1.309 + FROM "issue" CROSS JOIN "opinion" 1.310 + JOIN "direct_interest_snapshot" AS "snapshot" 1.311 + ON "snapshot"."issue_id" = "issue"."id" 1.312 + AND "snapshot"."event" = "issue"."latest_snapshot_event" 1.313 + AND "snapshot"."member_id" = "opinion"."member_id" 1.314 + WHERE "issue"."id" = "issue_id_p" 1.315 + AND "opinion"."suggestion_id" = "suggestion_id_v" 1.316 + AND "opinion"."degree" = -2 1.317 + AND "opinion"."fulfilled" = FALSE 1.318 + ), 1.319 + "minus2_fulfilled_count" = ( 1.320 + SELECT coalesce(sum("snapshot"."weight"), 0) 1.321 + FROM "issue" CROSS JOIN "opinion" 1.322 + JOIN "direct_interest_snapshot" AS "snapshot" 1.323 + ON "snapshot"."issue_id" = "issue"."id" 1.324 + AND "snapshot"."event" = "issue"."latest_snapshot_event" 1.325 + AND "snapshot"."member_id" = "opinion"."member_id" 1.326 + WHERE "issue"."id" = "issue_id_p" 1.327 + AND "opinion"."suggestion_id" = "suggestion_id_v" 1.328 + AND "opinion"."degree" = -2 1.329 + AND "opinion"."fulfilled" = TRUE 1.330 + ), 1.331 + "minus1_unfulfilled_count" = ( 1.332 + SELECT coalesce(sum("snapshot"."weight"), 0) 1.333 + FROM "issue" CROSS JOIN "opinion" 1.334 + JOIN "direct_interest_snapshot" AS "snapshot" 1.335 + ON "snapshot"."issue_id" = "issue"."id" 1.336 + AND "snapshot"."event" = "issue"."latest_snapshot_event" 1.337 + AND "snapshot"."member_id" = "opinion"."member_id" 1.338 + WHERE "issue"."id" = "issue_id_p" 1.339 + AND "opinion"."suggestion_id" = "suggestion_id_v" 1.340 + AND "opinion"."degree" = -1 1.341 + AND "opinion"."fulfilled" = FALSE 1.342 + ), 1.343 + "minus1_fulfilled_count" = ( 1.344 + SELECT coalesce(sum("snapshot"."weight"), 0) 1.345 + FROM "issue" CROSS JOIN "opinion" 1.346 + JOIN "direct_interest_snapshot" AS "snapshot" 1.347 + ON "snapshot"."issue_id" = "issue"."id" 1.348 + AND "snapshot"."event" = "issue"."latest_snapshot_event" 1.349 + AND "snapshot"."member_id" = "opinion"."member_id" 1.350 + WHERE "issue"."id" = "issue_id_p" 1.351 + AND "opinion"."suggestion_id" = "suggestion_id_v" 1.352 + AND "opinion"."degree" = -1 1.353 + AND "opinion"."fulfilled" = TRUE 1.354 + ), 1.355 + "plus1_unfulfilled_count" = ( 1.356 + SELECT coalesce(sum("snapshot"."weight"), 0) 1.357 + FROM "issue" CROSS JOIN "opinion" 1.358 + JOIN "direct_interest_snapshot" AS "snapshot" 1.359 + ON "snapshot"."issue_id" = "issue"."id" 1.360 + AND "snapshot"."event" = "issue"."latest_snapshot_event" 1.361 + AND "snapshot"."member_id" = "opinion"."member_id" 1.362 + WHERE "issue"."id" = "issue_id_p" 1.363 + AND "opinion"."suggestion_id" = "suggestion_id_v" 1.364 + AND "opinion"."degree" = 1 1.365 + AND "opinion"."fulfilled" = FALSE 1.366 + ), 1.367 + "plus1_fulfilled_count" = ( 1.368 + SELECT coalesce(sum("snapshot"."weight"), 0) 1.369 + FROM "issue" CROSS JOIN "opinion" 1.370 + JOIN "direct_interest_snapshot" AS "snapshot" 1.371 + ON "snapshot"."issue_id" = "issue"."id" 1.372 + AND "snapshot"."event" = "issue"."latest_snapshot_event" 1.373 + AND "snapshot"."member_id" = "opinion"."member_id" 1.374 + WHERE "issue"."id" = "issue_id_p" 1.375 + AND "opinion"."suggestion_id" = "suggestion_id_v" 1.376 + AND "opinion"."degree" = 1 1.377 + AND "opinion"."fulfilled" = TRUE 1.378 + ), 1.379 + "plus2_unfulfilled_count" = ( 1.380 + SELECT coalesce(sum("snapshot"."weight"), 0) 1.381 + FROM "issue" CROSS JOIN "opinion" 1.382 + JOIN "direct_interest_snapshot" AS "snapshot" 1.383 + ON "snapshot"."issue_id" = "issue"."id" 1.384 + AND "snapshot"."event" = "issue"."latest_snapshot_event" 1.385 + AND "snapshot"."member_id" = "opinion"."member_id" 1.386 + WHERE "issue"."id" = "issue_id_p" 1.387 + AND "opinion"."suggestion_id" = "suggestion_id_v" 1.388 + AND "opinion"."degree" = 2 1.389 + AND "opinion"."fulfilled" = FALSE 1.390 + ), 1.391 + "plus2_fulfilled_count" = ( 1.392 + SELECT coalesce(sum("snapshot"."weight"), 0) 1.393 + FROM "issue" CROSS JOIN "opinion" 1.394 + JOIN "direct_interest_snapshot" AS "snapshot" 1.395 + ON "snapshot"."issue_id" = "issue"."id" 1.396 + AND "snapshot"."event" = "issue"."latest_snapshot_event" 1.397 + AND "snapshot"."member_id" = "opinion"."member_id" 1.398 + WHERE "issue"."id" = "issue_id_p" 1.399 + AND "opinion"."suggestion_id" = "suggestion_id_v" 1.400 + AND "opinion"."degree" = 2 1.401 + AND "opinion"."fulfilled" = TRUE 1.402 + ) 1.403 + WHERE "suggestion"."id" = "suggestion_id_v"; 1.404 + END LOOP; 1.405 + PERFORM "set_harmonic_suggestion_weights"("initiative_id_v"); 1.406 + END LOOP; 1.407 + PERFORM "set_harmonic_initiative_weights"("issue_id_p"); 1.408 + RETURN; 1.409 + END; 1.410 + $$; 1.411 + 1.412 +SELECT "set_harmonic_initiative_weights"("id") FROM "issue"; 1.413 +SELECT "set_harmonic_suggestion_weights"("id") FROM "initiative"; 1.414 + 1.415 +COMMIT;