liquid_feedback_core

changeset 418:1088d83d92e8

Bugfix for battle data reconstruction in update script
author jbe
date Wed Mar 26 20:08:50 2014 +0100 (2014-03-26)
parents bc94ea65757b
children 044a2b65c707
files update/core-update.v3.0.0-v3.0.1.sql
line diff
     1.1 --- a/update/core-update.v3.0.0-v3.0.1.sql	Wed Mar 26 17:09:55 2014 +0100
     1.2 +++ b/update/core-update.v3.0.0-v3.0.1.sql	Wed Mar 26 20:08:50 2014 +0100
     1.3 @@ -71,8 +71,8 @@
     1.4    WHERE "initiative"."admitted"
     1.5    AND "initiative"."id" = "subquery"."initiative_id";
     1.6  
     1.7 --- reconstruct battle data to avoid future data loss when
     1.8 --- executing "clean_issue" to delete voting data:
     1.9 +-- reconstruct battle data (originating from LiquidFeedback Core before v2.0.0)
    1.10 +-- to avoid future data loss when executing "clean_issue" to delete voting data:
    1.11  INSERT INTO "battle" (
    1.12      "issue_id",
    1.13      "winning_initiative_id",
    1.14 @@ -83,12 +83,42 @@
    1.15      "battle_view"."winning_initiative_id",
    1.16      "battle_view"."losing_initiative_id",
    1.17      "battle_view"."count"
    1.18 -  FROM "battle_view"
    1.19 +  FROM (
    1.20 +    SELECT
    1.21 +      "issue"."id" AS "issue_id",
    1.22 +      "winning_initiative"."id" AS "winning_initiative_id",
    1.23 +      "losing_initiative"."id" AS "losing_initiative_id",
    1.24 +      sum(
    1.25 +        CASE WHEN
    1.26 +          coalesce("better_vote"."grade", 0) >
    1.27 +          coalesce("worse_vote"."grade", 0)
    1.28 +        THEN "direct_voter"."weight" ELSE 0 END
    1.29 +      ) AS "count"
    1.30 +    FROM "issue"
    1.31 +    LEFT JOIN "direct_voter"
    1.32 +    ON "issue"."id" = "direct_voter"."issue_id"
    1.33 +    JOIN "battle_participant" AS "winning_initiative"
    1.34 +      ON "issue"."id" = "winning_initiative"."issue_id"
    1.35 +    JOIN "battle_participant" AS "losing_initiative"
    1.36 +      ON "issue"."id" = "losing_initiative"."issue_id"
    1.37 +    LEFT JOIN "vote" AS "better_vote"
    1.38 +      ON "direct_voter"."member_id" = "better_vote"."member_id"
    1.39 +      AND "winning_initiative"."id" = "better_vote"."initiative_id"
    1.40 +    LEFT JOIN "vote" AS "worse_vote"
    1.41 +      ON "direct_voter"."member_id" = "worse_vote"."member_id"
    1.42 +      AND "losing_initiative"."id" = "worse_vote"."initiative_id"
    1.43 +    WHERE "issue"."state" IN ('finished_with_winner', 'finished_without_winner')
    1.44 +    AND "winning_initiative"."id" != "losing_initiative"."id"
    1.45 +    -- NOTE: comparisons with status-quo are intentionally omitted to mark
    1.46 +    --       issues that were counted prior LiquidFeedback Core v2.0.0
    1.47 +    GROUP BY
    1.48 +      "issue"."id",
    1.49 +      "winning_initiative"."id",
    1.50 +      "losing_initiative"."id"
    1.51 +  ) AS "battle_view"
    1.52    LEFT JOIN "battle"
    1.53    ON "battle_view"."winning_initiative_id" = "battle"."winning_initiative_id"
    1.54    AND "battle_view"."losing_initiative_id" = "battle"."losing_initiative_id"
    1.55 -  -- NOTE: comparisons with status-quo are intentionally omitted to mark
    1.56 -  --       issues that were counted prior LiquidFeedback Core v2.0.0
    1.57    WHERE "battle" ISNULL;
    1.58  
    1.59  CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)

Impressum / About Us