liquid_feedback_core

changeset 312:c951f0ed6cb8

Added "harmonic_weight" for suggestions; Bugfix in function "set_harmonic_initiative_weights" (clear weights prior calculation)
author jbe
date Fri Feb 01 18:36:02 2013 +0100 (2013-02-01)
parents 4dd3339453b8
children abf6ea622741
files core.sql
line diff
     1.1 --- a/core.sql	Fri Feb 01 05:44:34 2013 +0100
     1.2 +++ b/core.sql	Fri Feb 01 18:36:02 2013 +0100
     1.3 @@ -668,7 +668,7 @@
     1.4  COMMENT ON COLUMN "initiative"."informed_supporter_count"           IS 'Calculated from table "direct_supporter_snapshot"';
     1.5  COMMENT ON COLUMN "initiative"."satisfied_supporter_count"          IS 'Calculated from table "direct_supporter_snapshot"';
     1.6  COMMENT ON COLUMN "initiative"."satisfied_informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
     1.7 -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';
     1.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 position too much';
     1.9  COMMENT ON COLUMN "initiative"."positive_votes"         IS 'Calculated from table "direct_voter"';
    1.10  COMMENT ON COLUMN "initiative"."negative_votes"         IS 'Calculated from table "direct_voter"';
    1.11  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"';
    1.12 @@ -771,7 +771,8 @@
    1.13          "plus1_unfulfilled_count"  INT4,
    1.14          "plus1_fulfilled_count"    INT4,
    1.15          "plus2_unfulfilled_count"  INT4,
    1.16 -        "plus2_fulfilled_count"    INT4 );
    1.17 +        "plus2_fulfilled_count"    INT4,
    1.18 +        "harmonic_weight"       NUMERIC(12, 2) );
    1.19  CREATE INDEX "suggestion_created_idx" ON "suggestion" ("created");
    1.20  CREATE INDEX "suggestion_author_id_created_idx" ON "suggestion" ("author_id", "created");
    1.21  CREATE INDEX "suggestion_text_search_data_idx" ON "suggestion" USING gin ("text_search_data");
    1.22 @@ -792,6 +793,7 @@
    1.23  COMMENT ON COLUMN "suggestion"."plus1_fulfilled_count"    IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
    1.24  COMMENT ON COLUMN "suggestion"."plus2_unfulfilled_count"  IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
    1.25  COMMENT ON COLUMN "suggestion"."plus2_fulfilled_count"    IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
    1.26 +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';
    1.27  
    1.28  
    1.29  CREATE TABLE "rendered_suggestion" (
    1.30 @@ -3051,6 +3053,7 @@
    1.31  -- Calculation of harmonic weights --
    1.32  -------------------------------------
    1.33  
    1.34 +
    1.35  CREATE VIEW "remaining_harmonic_supporter_weight" AS
    1.36    SELECT
    1.37      "direct_interest_snapshot"."issue_id",
    1.38 @@ -3058,10 +3061,10 @@
    1.39      "direct_interest_snapshot"."member_id",
    1.40      "direct_interest_snapshot"."weight" AS "weight_num",
    1.41      count("initiative"."id") AS "weight_den"
    1.42 -  FROM "direct_interest_snapshot"
    1.43 -  JOIN "issue"
    1.44 -    ON "direct_interest_snapshot"."issue_id" = "issue"."id"
    1.45 -    AND "direct_interest_snapshot"."event" = "issue"."latest_snapshot_event"
    1.46 +  FROM "issue"
    1.47 +  JOIN "direct_interest_snapshot"
    1.48 +    ON "issue"."id" = "direct_interest_snapshot"."issue_id"
    1.49 +    AND "issue"."latest_snapshot_event" = "direct_interest_snapshot"."event"
    1.50    JOIN "direct_supporter_snapshot"
    1.51      ON "direct_interest_snapshot"."issue_id" = "direct_supporter_snapshot"."issue_id"
    1.52      AND "direct_interest_snapshot"."event" = "direct_supporter_snapshot"."event"
    1.53 @@ -3113,6 +3116,8 @@
    1.54        "weight_ary"   FLOAT[];
    1.55        "min_weight_v" FLOAT;
    1.56      BEGIN
    1.57 +      UPDATE "initiative" SET "harmonic_weight" = NULL
    1.58 +        WHERE "issue_id" = "issue_id_p";
    1.59        LOOP
    1.60          "min_weight_v" := NULL;
    1.61          "i" := 0;
    1.62 @@ -3161,10 +3166,121 @@
    1.63    IS 'Calculates and sets "harmonic_weight" of initiatives in a given issue';
    1.64  
    1.65  
    1.66 +CREATE VIEW "remaining_harmonic_opinion_weight" AS
    1.67 +  SELECT
    1.68 +    "initiative"."issue_id",
    1.69 +    "opinion"."initiative_id",
    1.70 +    "direct_interest_snapshot"."member_id",
    1.71 +    "direct_interest_snapshot"."weight" AS "weight_num",
    1.72 +    count("opinion"."suggestion_id") AS "weight_den"
    1.73 +  FROM "issue"
    1.74 +  JOIN "direct_interest_snapshot"
    1.75 +    ON "issue"."id" = "direct_interest_snapshot"."issue_id"
    1.76 +    AND "issue"."latest_snapshot_event" = "direct_interest_snapshot"."event"
    1.77 +  JOIN "initiative"
    1.78 +    ON "direct_interest_snapshot"."issue_id" = "initiative"."issue_id"
    1.79 +  JOIN "opinion"
    1.80 +    ON "direct_interest_snapshot"."member_id" = "opinion"."member_id"
    1.81 +    AND "initiative"."id" = "opinion"."initiative_id"
    1.82 +    AND "opinion"."degree" > 0
    1.83 +  GROUP BY
    1.84 +    "initiative"."issue_id",
    1.85 +    "opinion"."initiative_id",
    1.86 +    "direct_interest_snapshot"."member_id",
    1.87 +    "direct_interest_snapshot"."weight";
    1.88 +
    1.89 +COMMENT ON VIEW "remaining_harmonic_opinion_weight" IS 'Helper view for function "set_harmonic_suggestion_weights"';
    1.90 +
    1.91 +
    1.92 +CREATE VIEW "remaining_harmonic_suggestion_weight_summands" AS
    1.93 +  SELECT
    1.94 +    "suggestion"."initiative_id",
    1.95 +    "opinion"."suggestion_id",
    1.96 +    sum("remaining_harmonic_opinion_weight"."weight_num") AS "weight_num",
    1.97 +    "remaining_harmonic_opinion_weight"."weight_den"
    1.98 +  FROM "remaining_harmonic_opinion_weight"
    1.99 +  JOIN "opinion"
   1.100 +    ON "remaining_harmonic_opinion_weight"."initiative_id" = "opinion"."initiative_id"
   1.101 +    AND "remaining_harmonic_opinion_weight"."member_id" = "opinion"."member_id"
   1.102 +  JOIN "suggestion"
   1.103 +    ON "opinion"."suggestion_id" = "suggestion"."id"
   1.104 +    AND "suggestion"."harmonic_weight" ISNULL
   1.105 +  GROUP BY
   1.106 +    "suggestion"."initiative_id",
   1.107 +    "opinion"."suggestion_id",
   1.108 +    "remaining_harmonic_opinion_weight"."weight_den";
   1.109 +
   1.110 +COMMENT ON VIEW "remaining_harmonic_suggestion_weight_summands" IS 'Helper view for function "set_harmonic_suggestion_weights"';
   1.111 +
   1.112 +
   1.113 +CREATE FUNCTION "set_harmonic_suggestion_weights"
   1.114 +  ( "initiative_id_p" "initiative"."id"%TYPE )
   1.115 +  RETURNS VOID
   1.116 +  LANGUAGE 'plpgsql' VOLATILE AS $$
   1.117 +    DECLARE
   1.118 +      "weight_row"   "remaining_harmonic_suggestion_weight_summands"%ROWTYPE;
   1.119 +      "i"            INT4;
   1.120 +      "count_v"      INT4;
   1.121 +      "summand_v"    FLOAT;
   1.122 +      "id_ary"       INT4[];
   1.123 +      "weight_ary"   FLOAT[];
   1.124 +      "min_weight_v" FLOAT;
   1.125 +    BEGIN
   1.126 +      UPDATE "suggestion" SET "harmonic_weight" = NULL
   1.127 +        WHERE "initiative_id" = "initiative_id_p";
   1.128 +      LOOP
   1.129 +        "min_weight_v" := NULL;
   1.130 +        "i" := 0;
   1.131 +        "count_v" := 0;
   1.132 +        FOR "weight_row" IN
   1.133 +          SELECT * FROM "remaining_harmonic_suggestion_weight_summands"
   1.134 +          WHERE "initiative_id" = "initiative_id_p"
   1.135 +          ORDER BY "suggestion_id" DESC, "weight_den" DESC
   1.136 +          -- NOTE: latest suggestions treated worse
   1.137 +        LOOP
   1.138 +          "summand_v" := "weight_row"."weight_num"::FLOAT / "weight_row"."weight_den"::FLOAT;
   1.139 +          IF "i" = 0 OR "weight_row"."suggestion_id" != "id_ary"["i"] THEN
   1.140 +            "i" := "i" + 1;
   1.141 +            "count_v" := "i";
   1.142 +            "id_ary"["i"] := "weight_row"."suggestion_id";
   1.143 +            "weight_ary"["i"] := "summand_v";
   1.144 +          ELSE
   1.145 +            "weight_ary"["i"] := "weight_ary"["i"] + "summand_v";
   1.146 +          END IF;
   1.147 +        END LOOP;
   1.148 +        EXIT WHEN "count_v" = 0;
   1.149 +        "i" := 1;
   1.150 +        LOOP
   1.151 +          "weight_ary"["i"] := "weight_ary"["i"]::NUMERIC(20,10)::NUMERIC(12,2);
   1.152 +          IF "min_weight_v" ISNULL OR "weight_ary"["i"] < "min_weight_v" THEN
   1.153 +            "min_weight_v" := "weight_ary"["i"];
   1.154 +          END IF;
   1.155 +          "i" := "i" + 1;
   1.156 +          EXIT WHEN "i" > "count_v";
   1.157 +        END LOOP;
   1.158 +        "i" := 1;
   1.159 +        LOOP
   1.160 +          IF "weight_ary"["i"] = "min_weight_v" THEN
   1.161 +            UPDATE "suggestion" SET "harmonic_weight" = "min_weight_v"
   1.162 +              WHERE "id" = "id_ary"["i"];
   1.163 +            EXIT;
   1.164 +          END IF;
   1.165 +          "i" := "i" + 1;
   1.166 +        END LOOP;
   1.167 +      END LOOP;
   1.168 +    END;
   1.169 +  $$;
   1.170 +
   1.171 +COMMENT ON FUNCTION "set_harmonic_suggestion_weights"
   1.172 +  ( "issue"."id"%TYPE )
   1.173 +  IS 'Calculates and sets "harmonic_weight" of suggestions in a given initiative';
   1.174 +
   1.175 +
   1.176  ------------------------------
   1.177  -- Calculation of snapshots --
   1.178  ------------------------------
   1.179  
   1.180 +
   1.181  CREATE FUNCTION "weight_of_added_delegations_for_population_snapshot"
   1.182    ( "issue_id_p"            "issue"."id"%TYPE,
   1.183      "member_id_p"           "member"."id"%TYPE,
   1.184 @@ -3624,6 +3740,7 @@
   1.185              )
   1.186              WHERE "suggestion"."id" = "suggestion_id_v";
   1.187          END LOOP;
   1.188 +        PERFORM "set_harmonic_suggestion_weights"("initiative_id_v");
   1.189        END LOOP;
   1.190        PERFORM "set_harmonic_initiative_weights"("issue_id_p");
   1.191        RETURN;

Impressum / About Us