# HG changeset patch # User jbe # Date 1359740162 -3600 # Node ID c951f0ed6cb868e1541a95ba7b787c24e3763730 # Parent 4dd3339453b8a57ab44da5ba558a796cda6d3806 Added "harmonic_weight" for suggestions; Bugfix in function "set_harmonic_initiative_weights" (clear weights prior calculation) diff -r 4dd3339453b8 -r c951f0ed6cb8 core.sql --- a/core.sql Fri Feb 01 05:44:34 2013 +0100 +++ b/core.sql Fri Feb 01 18:36:02 2013 +0100 @@ -668,7 +668,7 @@ COMMENT ON COLUMN "initiative"."informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"'; COMMENT ON COLUMN "initiative"."satisfied_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"'; COMMENT ON COLUMN "initiative"."satisfied_informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"'; -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 initiatives sorting position too much'; +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 position too much'; COMMENT ON COLUMN "initiative"."positive_votes" IS 'Calculated from table "direct_voter"'; COMMENT ON COLUMN "initiative"."negative_votes" IS 'Calculated from table "direct_voter"'; COMMENT ON COLUMN "initiative"."direct_majority" IS 'TRUE, if "positive_votes"/("positive_votes"+"negative_votes") is strictly greater or greater-equal than "direct_majority_num"/"direct_majority_den", and "positive_votes" is greater-equal than "direct_majority_positive", and ("positive_votes"+abstentions) is greater-equal than "direct_majority_non_negative"'; @@ -771,7 +771,8 @@ "plus1_unfulfilled_count" INT4, "plus1_fulfilled_count" INT4, "plus2_unfulfilled_count" INT4, - "plus2_fulfilled_count" INT4 ); + "plus2_fulfilled_count" INT4, + "harmonic_weight" NUMERIC(12, 2) ); CREATE INDEX "suggestion_created_idx" ON "suggestion" ("created"); CREATE INDEX "suggestion_author_id_created_idx" ON "suggestion" ("author_id", "created"); CREATE INDEX "suggestion_text_search_data_idx" ON "suggestion" USING gin ("text_search_data"); @@ -792,6 +793,7 @@ COMMENT ON COLUMN "suggestion"."plus1_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters'; COMMENT ON COLUMN "suggestion"."plus2_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters'; COMMENT ON COLUMN "suggestion"."plus2_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters'; +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 position too much'; CREATE TABLE "rendered_suggestion" ( @@ -3051,6 +3053,7 @@ -- Calculation of harmonic weights -- ------------------------------------- + CREATE VIEW "remaining_harmonic_supporter_weight" AS SELECT "direct_interest_snapshot"."issue_id", @@ -3058,10 +3061,10 @@ "direct_interest_snapshot"."member_id", "direct_interest_snapshot"."weight" AS "weight_num", count("initiative"."id") AS "weight_den" - FROM "direct_interest_snapshot" - JOIN "issue" - ON "direct_interest_snapshot"."issue_id" = "issue"."id" - AND "direct_interest_snapshot"."event" = "issue"."latest_snapshot_event" + FROM "issue" + JOIN "direct_interest_snapshot" + ON "issue"."id" = "direct_interest_snapshot"."issue_id" + AND "issue"."latest_snapshot_event" = "direct_interest_snapshot"."event" JOIN "direct_supporter_snapshot" ON "direct_interest_snapshot"."issue_id" = "direct_supporter_snapshot"."issue_id" AND "direct_interest_snapshot"."event" = "direct_supporter_snapshot"."event" @@ -3113,6 +3116,8 @@ "weight_ary" FLOAT[]; "min_weight_v" FLOAT; BEGIN + UPDATE "initiative" SET "harmonic_weight" = NULL + WHERE "issue_id" = "issue_id_p"; LOOP "min_weight_v" := NULL; "i" := 0; @@ -3161,10 +3166,121 @@ IS 'Calculates and sets "harmonic_weight" of initiatives in a given issue'; +CREATE VIEW "remaining_harmonic_opinion_weight" AS + SELECT + "initiative"."issue_id", + "opinion"."initiative_id", + "direct_interest_snapshot"."member_id", + "direct_interest_snapshot"."weight" AS "weight_num", + count("opinion"."suggestion_id") AS "weight_den" + FROM "issue" + JOIN "direct_interest_snapshot" + ON "issue"."id" = "direct_interest_snapshot"."issue_id" + AND "issue"."latest_snapshot_event" = "direct_interest_snapshot"."event" + JOIN "initiative" + ON "direct_interest_snapshot"."issue_id" = "initiative"."issue_id" + JOIN "opinion" + ON "direct_interest_snapshot"."member_id" = "opinion"."member_id" + AND "initiative"."id" = "opinion"."initiative_id" + AND "opinion"."degree" > 0 + GROUP BY + "initiative"."issue_id", + "opinion"."initiative_id", + "direct_interest_snapshot"."member_id", + "direct_interest_snapshot"."weight"; + +COMMENT ON VIEW "remaining_harmonic_opinion_weight" IS 'Helper view for function "set_harmonic_suggestion_weights"'; + + +CREATE VIEW "remaining_harmonic_suggestion_weight_summands" AS + SELECT + "suggestion"."initiative_id", + "opinion"."suggestion_id", + sum("remaining_harmonic_opinion_weight"."weight_num") AS "weight_num", + "remaining_harmonic_opinion_weight"."weight_den" + FROM "remaining_harmonic_opinion_weight" + JOIN "opinion" + ON "remaining_harmonic_opinion_weight"."initiative_id" = "opinion"."initiative_id" + AND "remaining_harmonic_opinion_weight"."member_id" = "opinion"."member_id" + JOIN "suggestion" + ON "opinion"."suggestion_id" = "suggestion"."id" + AND "suggestion"."harmonic_weight" ISNULL + GROUP BY + "suggestion"."initiative_id", + "opinion"."suggestion_id", + "remaining_harmonic_opinion_weight"."weight_den"; + +COMMENT ON VIEW "remaining_harmonic_suggestion_weight_summands" IS 'Helper view for function "set_harmonic_suggestion_weights"'; + + +CREATE FUNCTION "set_harmonic_suggestion_weights" + ( "initiative_id_p" "initiative"."id"%TYPE ) + RETURNS VOID + LANGUAGE 'plpgsql' VOLATILE AS $$ + DECLARE + "weight_row" "remaining_harmonic_suggestion_weight_summands"%ROWTYPE; + "i" INT4; + "count_v" INT4; + "summand_v" FLOAT; + "id_ary" INT4[]; + "weight_ary" FLOAT[]; + "min_weight_v" FLOAT; + BEGIN + UPDATE "suggestion" SET "harmonic_weight" = NULL + WHERE "initiative_id" = "initiative_id_p"; + LOOP + "min_weight_v" := NULL; + "i" := 0; + "count_v" := 0; + FOR "weight_row" IN + SELECT * FROM "remaining_harmonic_suggestion_weight_summands" + WHERE "initiative_id" = "initiative_id_p" + ORDER BY "suggestion_id" DESC, "weight_den" DESC + -- NOTE: latest suggestions treated worse + LOOP + "summand_v" := "weight_row"."weight_num"::FLOAT / "weight_row"."weight_den"::FLOAT; + IF "i" = 0 OR "weight_row"."suggestion_id" != "id_ary"["i"] THEN + "i" := "i" + 1; + "count_v" := "i"; + "id_ary"["i"] := "weight_row"."suggestion_id"; + "weight_ary"["i"] := "summand_v"; + ELSE + "weight_ary"["i"] := "weight_ary"["i"] + "summand_v"; + END IF; + END LOOP; + EXIT WHEN "count_v" = 0; + "i" := 1; + LOOP + "weight_ary"["i"] := "weight_ary"["i"]::NUMERIC(20,10)::NUMERIC(12,2); + IF "min_weight_v" ISNULL OR "weight_ary"["i"] < "min_weight_v" THEN + "min_weight_v" := "weight_ary"["i"]; + END IF; + "i" := "i" + 1; + EXIT WHEN "i" > "count_v"; + END LOOP; + "i" := 1; + LOOP + IF "weight_ary"["i"] = "min_weight_v" THEN + UPDATE "suggestion" SET "harmonic_weight" = "min_weight_v" + WHERE "id" = "id_ary"["i"]; + EXIT; + END IF; + "i" := "i" + 1; + END LOOP; + END LOOP; + END; + $$; + +COMMENT ON FUNCTION "set_harmonic_suggestion_weights" + ( "issue"."id"%TYPE ) + IS 'Calculates and sets "harmonic_weight" of suggestions in a given initiative'; + + ------------------------------ -- Calculation of snapshots -- ------------------------------ + CREATE FUNCTION "weight_of_added_delegations_for_population_snapshot" ( "issue_id_p" "issue"."id"%TYPE, "member_id_p" "member"."id"%TYPE, @@ -3624,6 +3740,7 @@ ) WHERE "suggestion"."id" = "suggestion_id_v"; END LOOP; + PERFORM "set_harmonic_suggestion_weights"("initiative_id_v"); END LOOP; PERFORM "set_harmonic_initiative_weights"("issue_id_p"); RETURN;