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)