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;