liquid_feedback_core

changeset 320:3a7f06a88ecb

Calculate "harmonic_weight" to be used as a secondary sorting key, while the "admitted" flag shall be used as the primary sorting key for initiatives
author jbe
date Fri Feb 08 18:26:25 2013 +0100 (2013-02-08)
parents 685d38986598
children 48a5036d5eb1
files core.sql update/core-update.v2.1.0-v2.1.1.sql
line diff
     1.1 --- a/core.sql	Sat Feb 02 22:31:55 2013 +0100
     1.2 +++ b/core.sql	Fri Feb 08 18:26:25 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 initiative''s sorting positions 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 positions too much; shall be used as secondary sorting key after "admitted" as primary sorting key';
     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 @@ -3085,6 +3085,7 @@
    1.13    SELECT
    1.14      "initiative"."issue_id",
    1.15      "initiative"."id" AS "initiative_id",
    1.16 +    "initiative"."admitted",
    1.17      sum("remaining_harmonic_supporter_weight"."weight_num") AS "weight_num",
    1.18      "remaining_harmonic_supporter_weight"."weight_den"
    1.19    FROM "remaining_harmonic_supporter_weight"
    1.20 @@ -3098,6 +3099,7 @@
    1.21    GROUP BY
    1.22      "initiative"."issue_id",
    1.23      "initiative"."id",
    1.24 +    "initiative"."admitted",
    1.25      "remaining_harmonic_supporter_weight"."weight_den";
    1.26  
    1.27  COMMENT ON VIEW "remaining_harmonic_initiative_weight_summands" IS 'Helper view for function "set_harmonic_initiative_weights"';
    1.28 @@ -3125,8 +3127,17 @@
    1.29          FOR "weight_row" IN
    1.30            SELECT * FROM "remaining_harmonic_initiative_weight_summands"
    1.31            WHERE "issue_id" = "issue_id_p"
    1.32 +          AND (
    1.33 +            coalesce("admitted", FALSE) = FALSE OR NOT EXISTS (
    1.34 +              SELECT NULL FROM "initiative"
    1.35 +              WHERE "issue_id" = "issue_id_p"
    1.36 +              AND "harmonic_weight" ISNULL
    1.37 +              AND coalesce("admitted", FALSE) = FALSE
    1.38 +            )
    1.39 +          )
    1.40            ORDER BY "initiative_id" DESC, "weight_den" DESC
    1.41 -          -- NOTE: latest initiatives treated worse
    1.42 +          -- NOTE: non-admitted initiatives placed first (at last positions),
    1.43 +          --       latest initiatives treated worse in case of tie
    1.44          LOOP
    1.45            "summand_v" := "weight_row"."weight_num"::FLOAT / "weight_row"."weight_den"::FLOAT;
    1.46            IF "i" = 0 OR "weight_row"."initiative_id" != "id_ary"["i"] THEN
    1.47 @@ -3248,7 +3259,7 @@
    1.48            SELECT * FROM "remaining_harmonic_suggestion_weight_summands"
    1.49            WHERE "initiative_id" = "initiative_id_p"
    1.50            ORDER BY "suggestion_id" DESC, "weight_den" DESC
    1.51 -          -- NOTE: latest suggestions treated worse
    1.52 +          -- NOTE: latest suggestions treated worse in case of tie
    1.53          LOOP
    1.54            "summand_v" := "weight_row"."weight_num"::FLOAT / "weight_row"."weight_den"::FLOAT;
    1.55            IF "i" = 0 OR "weight_row"."suggestion_id" != "id_ary"["i"] THEN
     2.1 --- a/update/core-update.v2.1.0-v2.1.1.sql	Sat Feb 02 22:31:55 2013 +0100
     2.2 +++ b/update/core-update.v2.1.0-v2.1.1.sql	Fri Feb 08 18:26:25 2013 +0100
     2.3 @@ -5,7 +5,7 @@
     2.4    AS "subquery"("string", "major", "minor", "revision");
     2.5  
     2.6  ALTER TABLE "initiative" ADD COLUMN "harmonic_weight" NUMERIC(12, 3);
     2.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 initiative''s sorting positions too much';
     2.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 positions too much; shall be used as secondary sorting key after "admitted" as primary sorting key';
     2.9  
    2.10  ALTER TABLE "suggestion" ADD COLUMN "harmonic_weight" NUMERIC(12, 3);
    2.11  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 sortings position too much';
    2.12 @@ -40,6 +40,7 @@
    2.13    SELECT
    2.14      "initiative"."issue_id",
    2.15      "initiative"."id" AS "initiative_id",
    2.16 +    "initiative"."admitted",
    2.17      sum("remaining_harmonic_supporter_weight"."weight_num") AS "weight_num",
    2.18      "remaining_harmonic_supporter_weight"."weight_den"
    2.19    FROM "remaining_harmonic_supporter_weight"
    2.20 @@ -53,6 +54,7 @@
    2.21    GROUP BY
    2.22      "initiative"."issue_id",
    2.23      "initiative"."id",
    2.24 +    "initiative"."admitted",
    2.25      "remaining_harmonic_supporter_weight"."weight_den";
    2.26  
    2.27  COMMENT ON VIEW "remaining_harmonic_initiative_weight_summands" IS 'Helper view for function "set_harmonic_initiative_weights"';
    2.28 @@ -79,8 +81,17 @@
    2.29          FOR "weight_row" IN
    2.30            SELECT * FROM "remaining_harmonic_initiative_weight_summands"
    2.31            WHERE "issue_id" = "issue_id_p"
    2.32 +          AND (
    2.33 +            coalesce("admitted", FALSE) = FALSE OR NOT EXISTS (
    2.34 +              SELECT NULL FROM "initiative"
    2.35 +              WHERE "issue_id" = "issue_id_p"
    2.36 +              AND "harmonic_weight" ISNULL
    2.37 +              AND coalesce("admitted", FALSE) = FALSE
    2.38 +            )
    2.39 +          )
    2.40            ORDER BY "initiative_id" DESC, "weight_den" DESC
    2.41 -          -- NOTE: latest initiatives treated worse
    2.42 +          -- NOTE: non-admitted initiatives placed first (at last positions),
    2.43 +          --       latest initiatives treated worse in case of tie
    2.44          LOOP
    2.45            "summand_v" := "weight_row"."weight_num"::FLOAT / "weight_row"."weight_den"::FLOAT;
    2.46            IF "i" = 0 OR "weight_row"."initiative_id" != "id_ary"["i"] THEN
    2.47 @@ -199,7 +210,7 @@
    2.48            SELECT * FROM "remaining_harmonic_suggestion_weight_summands"
    2.49            WHERE "initiative_id" = "initiative_id_p"
    2.50            ORDER BY "suggestion_id" DESC, "weight_den" DESC
    2.51 -          -- NOTE: latest suggestions treated worse
    2.52 +          -- NOTE: latest suggestions treated worse in case of tie
    2.53          LOOP
    2.54            "summand_v" := "weight_row"."weight_num"::FLOAT / "weight_row"."weight_den"::FLOAT;
    2.55            IF "i" = 0 OR "weight_row"."suggestion_id" != "id_ary"["i"] THEN

Impressum / About Us