1.1 --- a/update/core-update.v2.1.0-v2.1.1.sql	Fri Feb 08 19:20:17 2013 +0100
     1.2 +++ b/update/core-update.v2.1.0-v2.1.1.sql	Sat Feb 09 13:43:17 2013 +0100
     1.3 @@ -140,125 +140,6 @@
     1.4    ( "issue"."id"%TYPE )
     1.5    IS 'Calculates and sets "harmonic_weight" of initiatives in a given issue';
     1.6  
     1.7 -CREATE VIEW "remaining_harmonic_opinion_weight" AS
     1.8 -  SELECT
     1.9 -    "initiative"."issue_id",
    1.10 -    "opinion"."initiative_id",
    1.11 -    "direct_interest_snapshot"."member_id",
    1.12 -    "direct_interest_snapshot"."weight" AS "weight_num",
    1.13 -    count("opinion"."suggestion_id") AS "weight_den"
    1.14 -  FROM "issue"
    1.15 -  JOIN "direct_interest_snapshot"
    1.16 -    ON "issue"."id" = "direct_interest_snapshot"."issue_id"
    1.17 -    AND "issue"."latest_snapshot_event" = "direct_interest_snapshot"."event"
    1.18 -  JOIN "initiative"
    1.19 -    ON "direct_interest_snapshot"."issue_id" = "initiative"."issue_id"
    1.20 -  JOIN "opinion"
    1.21 -    ON "direct_interest_snapshot"."member_id" = "opinion"."member_id"
    1.22 -    AND "initiative"."id" = "opinion"."initiative_id"
    1.23 -    AND (
    1.24 -      ("opinion"."degree" > 0 AND "opinion"."fulfilled" = FALSE) OR
    1.25 -      ("opinion"."degree" < 0 AND "opinion"."fulfilled" = TRUE)
    1.26 -    )
    1.27 -  JOIN "suggestion"
    1.28 -    ON "opinion"."suggestion_id" = "suggestion"."id"
    1.29 -    AND "suggestion"."harmonic_weight" ISNULL
    1.30 -  GROUP BY
    1.31 -    "initiative"."issue_id",
    1.32 -    "opinion"."initiative_id",
    1.33 -    "direct_interest_snapshot"."member_id",
    1.34 -    "direct_interest_snapshot"."weight";
    1.35 -
    1.36 -COMMENT ON VIEW "remaining_harmonic_opinion_weight" IS 'Helper view for function "set_harmonic_suggestion_weights"';
    1.37 -
    1.38 -CREATE VIEW "remaining_harmonic_suggestion_weight_summands" AS
    1.39 -  SELECT
    1.40 -    "suggestion"."initiative_id",
    1.41 -    "opinion"."suggestion_id",
    1.42 -    sum("remaining_harmonic_opinion_weight"."weight_num") AS "weight_num",
    1.43 -    "remaining_harmonic_opinion_weight"."weight_den"
    1.44 -  FROM "remaining_harmonic_opinion_weight"
    1.45 -  JOIN "opinion"
    1.46 -    ON "remaining_harmonic_opinion_weight"."initiative_id" = "opinion"."initiative_id"
    1.47 -    AND "remaining_harmonic_opinion_weight"."member_id" = "opinion"."member_id"
    1.48 -    AND (
    1.49 -      ("opinion"."degree" > 0 AND "opinion"."fulfilled" = FALSE) OR
    1.50 -      ("opinion"."degree" < 0 AND "opinion"."fulfilled" = TRUE)
    1.51 -    )
    1.52 -  JOIN "suggestion"
    1.53 -    ON "opinion"."suggestion_id" = "suggestion"."id"
    1.54 -    AND "suggestion"."harmonic_weight" ISNULL
    1.55 -  GROUP BY
    1.56 -    "suggestion"."initiative_id",
    1.57 -    "opinion"."suggestion_id",
    1.58 -    "remaining_harmonic_opinion_weight"."weight_den";
    1.59 -
    1.60 -COMMENT ON VIEW "remaining_harmonic_suggestion_weight_summands" IS 'Helper view for function "set_harmonic_suggestion_weights"';
    1.61 -
    1.62 -CREATE FUNCTION "set_harmonic_suggestion_weights"
    1.63 -  ( "initiative_id_p" "initiative"."id"%TYPE )
    1.64 -  RETURNS VOID
    1.65 -  LANGUAGE 'plpgsql' VOLATILE AS $$
    1.66 -    DECLARE
    1.67 -      "weight_row"   "remaining_harmonic_suggestion_weight_summands"%ROWTYPE;
    1.68 -      "i"            INT4;
    1.69 -      "count_v"      INT4;
    1.70 -      "summand_v"    FLOAT;
    1.71 -      "id_ary"       INT4[];
    1.72 -      "weight_ary"   FLOAT[];
    1.73 -      "min_weight_v" FLOAT;
    1.74 -    BEGIN
    1.75 -      UPDATE "suggestion" SET "harmonic_weight" = NULL
    1.76 -        WHERE "initiative_id" = "initiative_id_p";
    1.77 -      LOOP
    1.78 -        "min_weight_v" := NULL;
    1.79 -        "i" := 0;
    1.80 -        "count_v" := 0;
    1.81 -        FOR "weight_row" IN
    1.82 -          SELECT * FROM "remaining_harmonic_suggestion_weight_summands"
    1.83 -          WHERE "initiative_id" = "initiative_id_p"
    1.84 -          ORDER BY "suggestion_id" DESC, "weight_den" DESC
    1.85 -          -- NOTE: latest suggestions treated worse in case of tie
    1.86 -        LOOP
    1.87 -          "summand_v" := "weight_row"."weight_num"::FLOAT / "weight_row"."weight_den"::FLOAT;
    1.88 -          IF "i" = 0 OR "weight_row"."suggestion_id" != "id_ary"["i"] THEN
    1.89 -            "i" := "i" + 1;
    1.90 -            "count_v" := "i";
    1.91 -            "id_ary"["i"] := "weight_row"."suggestion_id";
    1.92 -            "weight_ary"["i"] := "summand_v";
    1.93 -          ELSE
    1.94 -            "weight_ary"["i"] := "weight_ary"["i"] + "summand_v";
    1.95 -          END IF;
    1.96 -        END LOOP;
    1.97 -        EXIT WHEN "count_v" = 0;
    1.98 -        "i" := 1;
    1.99 -        LOOP
   1.100 -          "weight_ary"["i"] := "weight_ary"["i"]::NUMERIC(18,9)::NUMERIC(12,3);
   1.101 -          IF "min_weight_v" ISNULL OR "weight_ary"["i"] < "min_weight_v" THEN
   1.102 -            "min_weight_v" := "weight_ary"["i"];
   1.103 -          END IF;
   1.104 -          "i" := "i" + 1;
   1.105 -          EXIT WHEN "i" > "count_v";
   1.106 -        END LOOP;
   1.107 -        "i" := 1;
   1.108 -        LOOP
   1.109 -          IF "weight_ary"["i"] = "min_weight_v" THEN
   1.110 -            UPDATE "suggestion" SET "harmonic_weight" = "min_weight_v"
   1.111 -              WHERE "id" = "id_ary"["i"];
   1.112 -            EXIT;
   1.113 -          END IF;
   1.114 -          "i" := "i" + 1;
   1.115 -        END LOOP;
   1.116 -      END LOOP;
   1.117 -      UPDATE "suggestion" SET "harmonic_weight" = 0
   1.118 -        WHERE "initiative_id" = "initiative_id_p" AND "harmonic_weight" ISNULL;
   1.119 -    END;
   1.120 -  $$;
   1.121 -
   1.122 -COMMENT ON FUNCTION "set_harmonic_suggestion_weights"
   1.123 -  ( "issue"."id"%TYPE )
   1.124 -  IS 'Calculates and sets "harmonic_weight" of suggestions in a given initiative';
   1.125 -
   1.126  CREATE OR REPLACE FUNCTION "manual_freeze"("issue_id_p" "issue"."id"%TYPE)
   1.127    RETURNS VOID
   1.128    LANGUAGE 'plpgsql' VOLATILE AS $$
   1.129 @@ -268,8 +149,6 @@
   1.130        PERFORM "create_snapshot"("issue_id_p");
   1.131        PERFORM "freeze_after_snapshot"("issue_id_p");
   1.132        PERFORM "set_harmonic_initiative_weights"("issue_id_p");
   1.133 -      PERFORM "set_harmonic_suggestion_weights"("id")
   1.134 -        FROM "initiative" WHERE "issue_id" = "issue_id_p";
   1.135        RETURN;
   1.136      END;
   1.137    $$;
   1.138 @@ -406,8 +285,6 @@
   1.139          -- if a new shapshot has been created, then recalculate harmonic weights:
   1.140          IF "new_snapshot_v" THEN
   1.141            PERFORM "set_harmonic_initiative_weights"("issue_id_p");
   1.142 -          PERFORM "set_harmonic_suggestion_weights"("id")
   1.143 -            FROM "initiative" WHERE "issue_id" = "issue_id_p";
   1.144          END IF;
   1.145        END IF;
   1.146        RETURN;
   1.147 @@ -415,6 +292,5 @@
   1.148    $$;
   1.149  
   1.150  SELECT "set_harmonic_initiative_weights"("id") FROM "issue";
   1.151 -SELECT "set_harmonic_suggestion_weights"("id") FROM "initiative";
   1.152  
   1.153  COMMIT;