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;

Impressum / About Us