liquid_feedback_core
changeset 323:4c7a864829b0
Removed "harmonic_weight" for suggestions, because another proportional ranking algorithm is needed there
author | jbe |
---|---|
date | Sat Feb 09 13:43:17 2013 +0100 (2013-02-09) |
parents | fd58f487e1d0 |
children | a0dd8c78bd10 |
files | core.sql update/core-update.v2.1.0-v2.1.1.sql |
line diff
1.1 --- a/core.sql Fri Feb 08 19:20:17 2013 +0100 1.2 +++ b/core.sql Sat Feb 09 13:43:17 2013 +0100 1.3 @@ -771,8 +771,7 @@ 1.4 "plus1_unfulfilled_count" INT4, 1.5 "plus1_fulfilled_count" INT4, 1.6 "plus2_unfulfilled_count" INT4, 1.7 - "plus2_fulfilled_count" INT4, 1.8 - "harmonic_weight" NUMERIC(12, 3) ); 1.9 + "plus2_fulfilled_count" INT4 ); 1.10 CREATE INDEX "suggestion_created_idx" ON "suggestion" ("created"); 1.11 CREATE INDEX "suggestion_author_id_created_idx" ON "suggestion" ("author_id", "created"); 1.12 CREATE INDEX "suggestion_text_search_data_idx" ON "suggestion" USING gin ("text_search_data"); 1.13 @@ -793,7 +792,6 @@ 1.14 COMMENT ON COLUMN "suggestion"."plus1_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters'; 1.15 COMMENT ON COLUMN "suggestion"."plus2_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters'; 1.16 COMMENT ON COLUMN "suggestion"."plus2_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters'; 1.17 -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 sorting positions too much'; 1.18 1.19 1.20 CREATE TABLE "rendered_suggestion" ( 1.21 @@ -3187,127 +3185,6 @@ 1.22 IS 'Calculates and sets "harmonic_weight" of initiatives in a given issue'; 1.23 1.24 1.25 -CREATE VIEW "remaining_harmonic_opinion_weight" AS 1.26 - SELECT 1.27 - "initiative"."issue_id", 1.28 - "opinion"."initiative_id", 1.29 - "direct_interest_snapshot"."member_id", 1.30 - "direct_interest_snapshot"."weight" AS "weight_num", 1.31 - count("opinion"."suggestion_id") AS "weight_den" 1.32 - FROM "issue" 1.33 - JOIN "direct_interest_snapshot" 1.34 - ON "issue"."id" = "direct_interest_snapshot"."issue_id" 1.35 - AND "issue"."latest_snapshot_event" = "direct_interest_snapshot"."event" 1.36 - JOIN "initiative" 1.37 - ON "direct_interest_snapshot"."issue_id" = "initiative"."issue_id" 1.38 - JOIN "opinion" 1.39 - ON "direct_interest_snapshot"."member_id" = "opinion"."member_id" 1.40 - AND "initiative"."id" = "opinion"."initiative_id" 1.41 - AND ( 1.42 - ("opinion"."degree" > 0 AND "opinion"."fulfilled" = FALSE) OR 1.43 - ("opinion"."degree" < 0 AND "opinion"."fulfilled" = TRUE) 1.44 - ) 1.45 - JOIN "suggestion" 1.46 - ON "opinion"."suggestion_id" = "suggestion"."id" 1.47 - AND "suggestion"."harmonic_weight" ISNULL 1.48 - GROUP BY 1.49 - "initiative"."issue_id", 1.50 - "opinion"."initiative_id", 1.51 - "direct_interest_snapshot"."member_id", 1.52 - "direct_interest_snapshot"."weight"; 1.53 - 1.54 -COMMENT ON VIEW "remaining_harmonic_opinion_weight" IS 'Helper view for function "set_harmonic_suggestion_weights"'; 1.55 - 1.56 - 1.57 -CREATE VIEW "remaining_harmonic_suggestion_weight_summands" AS 1.58 - SELECT 1.59 - "suggestion"."initiative_id", 1.60 - "opinion"."suggestion_id", 1.61 - sum("remaining_harmonic_opinion_weight"."weight_num") AS "weight_num", 1.62 - "remaining_harmonic_opinion_weight"."weight_den" 1.63 - FROM "remaining_harmonic_opinion_weight" 1.64 - JOIN "opinion" 1.65 - ON "remaining_harmonic_opinion_weight"."initiative_id" = "opinion"."initiative_id" 1.66 - AND "remaining_harmonic_opinion_weight"."member_id" = "opinion"."member_id" 1.67 - AND ( 1.68 - ("opinion"."degree" > 0 AND "opinion"."fulfilled" = FALSE) OR 1.69 - ("opinion"."degree" < 0 AND "opinion"."fulfilled" = TRUE) 1.70 - ) 1.71 - JOIN "suggestion" 1.72 - ON "opinion"."suggestion_id" = "suggestion"."id" 1.73 - AND "suggestion"."harmonic_weight" ISNULL 1.74 - GROUP BY 1.75 - "suggestion"."initiative_id", 1.76 - "opinion"."suggestion_id", 1.77 - "remaining_harmonic_opinion_weight"."weight_den"; 1.78 - 1.79 -COMMENT ON VIEW "remaining_harmonic_suggestion_weight_summands" IS 'Helper view for function "set_harmonic_suggestion_weights"'; 1.80 - 1.81 - 1.82 -CREATE FUNCTION "set_harmonic_suggestion_weights" 1.83 - ( "initiative_id_p" "initiative"."id"%TYPE ) 1.84 - RETURNS VOID 1.85 - LANGUAGE 'plpgsql' VOLATILE AS $$ 1.86 - DECLARE 1.87 - "weight_row" "remaining_harmonic_suggestion_weight_summands"%ROWTYPE; 1.88 - "i" INT4; 1.89 - "count_v" INT4; 1.90 - "summand_v" FLOAT; 1.91 - "id_ary" INT4[]; 1.92 - "weight_ary" FLOAT[]; 1.93 - "min_weight_v" FLOAT; 1.94 - BEGIN 1.95 - UPDATE "suggestion" SET "harmonic_weight" = NULL 1.96 - WHERE "initiative_id" = "initiative_id_p"; 1.97 - LOOP 1.98 - "min_weight_v" := NULL; 1.99 - "i" := 0; 1.100 - "count_v" := 0; 1.101 - FOR "weight_row" IN 1.102 - SELECT * FROM "remaining_harmonic_suggestion_weight_summands" 1.103 - WHERE "initiative_id" = "initiative_id_p" 1.104 - ORDER BY "suggestion_id" DESC, "weight_den" DESC 1.105 - -- NOTE: latest suggestions treated worse in case of tie 1.106 - LOOP 1.107 - "summand_v" := "weight_row"."weight_num"::FLOAT / "weight_row"."weight_den"::FLOAT; 1.108 - IF "i" = 0 OR "weight_row"."suggestion_id" != "id_ary"["i"] THEN 1.109 - "i" := "i" + 1; 1.110 - "count_v" := "i"; 1.111 - "id_ary"["i"] := "weight_row"."suggestion_id"; 1.112 - "weight_ary"["i"] := "summand_v"; 1.113 - ELSE 1.114 - "weight_ary"["i"] := "weight_ary"["i"] + "summand_v"; 1.115 - END IF; 1.116 - END LOOP; 1.117 - EXIT WHEN "count_v" = 0; 1.118 - "i" := 1; 1.119 - LOOP 1.120 - "weight_ary"["i"] := "weight_ary"["i"]::NUMERIC(18,9)::NUMERIC(12,3); 1.121 - IF "min_weight_v" ISNULL OR "weight_ary"["i"] < "min_weight_v" THEN 1.122 - "min_weight_v" := "weight_ary"["i"]; 1.123 - END IF; 1.124 - "i" := "i" + 1; 1.125 - EXIT WHEN "i" > "count_v"; 1.126 - END LOOP; 1.127 - "i" := 1; 1.128 - LOOP 1.129 - IF "weight_ary"["i"] = "min_weight_v" THEN 1.130 - UPDATE "suggestion" SET "harmonic_weight" = "min_weight_v" 1.131 - WHERE "id" = "id_ary"["i"]; 1.132 - EXIT; 1.133 - END IF; 1.134 - "i" := "i" + 1; 1.135 - END LOOP; 1.136 - END LOOP; 1.137 - UPDATE "suggestion" SET "harmonic_weight" = 0 1.138 - WHERE "initiative_id" = "initiative_id_p" AND "harmonic_weight" ISNULL; 1.139 - END; 1.140 - $$; 1.141 - 1.142 -COMMENT ON FUNCTION "set_harmonic_suggestion_weights" 1.143 - ( "issue"."id"%TYPE ) 1.144 - IS 'Calculates and sets "harmonic_weight" of suggestions in a given initiative'; 1.145 - 1.146 1.147 ------------------------------ 1.148 -- Calculation of snapshots -- 1.149 @@ -3895,8 +3772,6 @@ 1.150 PERFORM "create_snapshot"("issue_id_p"); 1.151 PERFORM "freeze_after_snapshot"("issue_id_p"); 1.152 PERFORM "set_harmonic_initiative_weights"("issue_id_p"); 1.153 - PERFORM "set_harmonic_suggestion_weights"("id") 1.154 - FROM "initiative" WHERE "issue_id" = "issue_id_p"; 1.155 RETURN; 1.156 END; 1.157 $$; 1.158 @@ -4548,8 +4423,6 @@ 1.159 -- if a new shapshot has been created, then recalculate harmonic weights: 1.160 IF "new_snapshot_v" THEN 1.161 PERFORM "set_harmonic_initiative_weights"("issue_id_p"); 1.162 - PERFORM "set_harmonic_suggestion_weights"("id") 1.163 - FROM "initiative" WHERE "issue_id" = "issue_id_p"; 1.164 END IF; 1.165 END IF; 1.166 RETURN;
2.1 --- a/update/core-update.v2.1.0-v2.1.1.sql Fri Feb 08 19:20:17 2013 +0100 2.2 +++ b/update/core-update.v2.1.0-v2.1.1.sql Sat Feb 09 13:43:17 2013 +0100 2.3 @@ -140,125 +140,6 @@ 2.4 ( "issue"."id"%TYPE ) 2.5 IS 'Calculates and sets "harmonic_weight" of initiatives in a given issue'; 2.6 2.7 -CREATE VIEW "remaining_harmonic_opinion_weight" AS 2.8 - SELECT 2.9 - "initiative"."issue_id", 2.10 - "opinion"."initiative_id", 2.11 - "direct_interest_snapshot"."member_id", 2.12 - "direct_interest_snapshot"."weight" AS "weight_num", 2.13 - count("opinion"."suggestion_id") AS "weight_den" 2.14 - FROM "issue" 2.15 - JOIN "direct_interest_snapshot" 2.16 - ON "issue"."id" = "direct_interest_snapshot"."issue_id" 2.17 - AND "issue"."latest_snapshot_event" = "direct_interest_snapshot"."event" 2.18 - JOIN "initiative" 2.19 - ON "direct_interest_snapshot"."issue_id" = "initiative"."issue_id" 2.20 - JOIN "opinion" 2.21 - ON "direct_interest_snapshot"."member_id" = "opinion"."member_id" 2.22 - AND "initiative"."id" = "opinion"."initiative_id" 2.23 - AND ( 2.24 - ("opinion"."degree" > 0 AND "opinion"."fulfilled" = FALSE) OR 2.25 - ("opinion"."degree" < 0 AND "opinion"."fulfilled" = TRUE) 2.26 - ) 2.27 - JOIN "suggestion" 2.28 - ON "opinion"."suggestion_id" = "suggestion"."id" 2.29 - AND "suggestion"."harmonic_weight" ISNULL 2.30 - GROUP BY 2.31 - "initiative"."issue_id", 2.32 - "opinion"."initiative_id", 2.33 - "direct_interest_snapshot"."member_id", 2.34 - "direct_interest_snapshot"."weight"; 2.35 - 2.36 -COMMENT ON VIEW "remaining_harmonic_opinion_weight" IS 'Helper view for function "set_harmonic_suggestion_weights"'; 2.37 - 2.38 -CREATE VIEW "remaining_harmonic_suggestion_weight_summands" AS 2.39 - SELECT 2.40 - "suggestion"."initiative_id", 2.41 - "opinion"."suggestion_id", 2.42 - sum("remaining_harmonic_opinion_weight"."weight_num") AS "weight_num", 2.43 - "remaining_harmonic_opinion_weight"."weight_den" 2.44 - FROM "remaining_harmonic_opinion_weight" 2.45 - JOIN "opinion" 2.46 - ON "remaining_harmonic_opinion_weight"."initiative_id" = "opinion"."initiative_id" 2.47 - AND "remaining_harmonic_opinion_weight"."member_id" = "opinion"."member_id" 2.48 - AND ( 2.49 - ("opinion"."degree" > 0 AND "opinion"."fulfilled" = FALSE) OR 2.50 - ("opinion"."degree" < 0 AND "opinion"."fulfilled" = TRUE) 2.51 - ) 2.52 - JOIN "suggestion" 2.53 - ON "opinion"."suggestion_id" = "suggestion"."id" 2.54 - AND "suggestion"."harmonic_weight" ISNULL 2.55 - GROUP BY 2.56 - "suggestion"."initiative_id", 2.57 - "opinion"."suggestion_id", 2.58 - "remaining_harmonic_opinion_weight"."weight_den"; 2.59 - 2.60 -COMMENT ON VIEW "remaining_harmonic_suggestion_weight_summands" IS 'Helper view for function "set_harmonic_suggestion_weights"'; 2.61 - 2.62 -CREATE FUNCTION "set_harmonic_suggestion_weights" 2.63 - ( "initiative_id_p" "initiative"."id"%TYPE ) 2.64 - RETURNS VOID 2.65 - LANGUAGE 'plpgsql' VOLATILE AS $$ 2.66 - DECLARE 2.67 - "weight_row" "remaining_harmonic_suggestion_weight_summands"%ROWTYPE; 2.68 - "i" INT4; 2.69 - "count_v" INT4; 2.70 - "summand_v" FLOAT; 2.71 - "id_ary" INT4[]; 2.72 - "weight_ary" FLOAT[]; 2.73 - "min_weight_v" FLOAT; 2.74 - BEGIN 2.75 - UPDATE "suggestion" SET "harmonic_weight" = NULL 2.76 - WHERE "initiative_id" = "initiative_id_p"; 2.77 - LOOP 2.78 - "min_weight_v" := NULL; 2.79 - "i" := 0; 2.80 - "count_v" := 0; 2.81 - FOR "weight_row" IN 2.82 - SELECT * FROM "remaining_harmonic_suggestion_weight_summands" 2.83 - WHERE "initiative_id" = "initiative_id_p" 2.84 - ORDER BY "suggestion_id" DESC, "weight_den" DESC 2.85 - -- NOTE: latest suggestions treated worse in case of tie 2.86 - LOOP 2.87 - "summand_v" := "weight_row"."weight_num"::FLOAT / "weight_row"."weight_den"::FLOAT; 2.88 - IF "i" = 0 OR "weight_row"."suggestion_id" != "id_ary"["i"] THEN 2.89 - "i" := "i" + 1; 2.90 - "count_v" := "i"; 2.91 - "id_ary"["i"] := "weight_row"."suggestion_id"; 2.92 - "weight_ary"["i"] := "summand_v"; 2.93 - ELSE 2.94 - "weight_ary"["i"] := "weight_ary"["i"] + "summand_v"; 2.95 - END IF; 2.96 - END LOOP; 2.97 - EXIT WHEN "count_v" = 0; 2.98 - "i" := 1; 2.99 - LOOP 2.100 - "weight_ary"["i"] := "weight_ary"["i"]::NUMERIC(18,9)::NUMERIC(12,3); 2.101 - IF "min_weight_v" ISNULL OR "weight_ary"["i"] < "min_weight_v" THEN 2.102 - "min_weight_v" := "weight_ary"["i"]; 2.103 - END IF; 2.104 - "i" := "i" + 1; 2.105 - EXIT WHEN "i" > "count_v"; 2.106 - END LOOP; 2.107 - "i" := 1; 2.108 - LOOP 2.109 - IF "weight_ary"["i"] = "min_weight_v" THEN 2.110 - UPDATE "suggestion" SET "harmonic_weight" = "min_weight_v" 2.111 - WHERE "id" = "id_ary"["i"]; 2.112 - EXIT; 2.113 - END IF; 2.114 - "i" := "i" + 1; 2.115 - END LOOP; 2.116 - END LOOP; 2.117 - UPDATE "suggestion" SET "harmonic_weight" = 0 2.118 - WHERE "initiative_id" = "initiative_id_p" AND "harmonic_weight" ISNULL; 2.119 - END; 2.120 - $$; 2.121 - 2.122 -COMMENT ON FUNCTION "set_harmonic_suggestion_weights" 2.123 - ( "issue"."id"%TYPE ) 2.124 - IS 'Calculates and sets "harmonic_weight" of suggestions in a given initiative'; 2.125 - 2.126 CREATE OR REPLACE FUNCTION "manual_freeze"("issue_id_p" "issue"."id"%TYPE) 2.127 RETURNS VOID 2.128 LANGUAGE 'plpgsql' VOLATILE AS $$ 2.129 @@ -268,8 +149,6 @@ 2.130 PERFORM "create_snapshot"("issue_id_p"); 2.131 PERFORM "freeze_after_snapshot"("issue_id_p"); 2.132 PERFORM "set_harmonic_initiative_weights"("issue_id_p"); 2.133 - PERFORM "set_harmonic_suggestion_weights"("id") 2.134 - FROM "initiative" WHERE "issue_id" = "issue_id_p"; 2.135 RETURN; 2.136 END; 2.137 $$; 2.138 @@ -406,8 +285,6 @@ 2.139 -- if a new shapshot has been created, then recalculate harmonic weights: 2.140 IF "new_snapshot_v" THEN 2.141 PERFORM "set_harmonic_initiative_weights"("issue_id_p"); 2.142 - PERFORM "set_harmonic_suggestion_weights"("id") 2.143 - FROM "initiative" WHERE "issue_id" = "issue_id_p"; 2.144 END IF; 2.145 END IF; 2.146 RETURN; 2.147 @@ -415,6 +292,5 @@ 2.148 $$; 2.149 2.150 SELECT "set_harmonic_initiative_weights"("id") FROM "issue"; 2.151 -SELECT "set_harmonic_suggestion_weights"("id") FROM "initiative"; 2.152 2.153 COMMIT;