liquid_feedback_core

changeset 178:184b023a5b1d

Set voting results for finished issues in update script to v1.5.0
author jbe
date Wed Jun 08 20:47:19 2011 +0200 (2011-06-08)
parents 9975ce3f20ee
children fb2394116b39
files update/core-update.v1.4.0-v1.5.0.sql
line diff
     1.1 --- a/update/core-update.v1.4.0-v1.5.0.sql	Wed Jun 08 17:31:00 2011 +0200
     1.2 +++ b/update/core-update.v1.4.0-v1.5.0.sql	Wed Jun 08 20:47:19 2011 +0200
     1.3 @@ -1,3 +1,5 @@
     1.4 +SELECT "calculate_ranks"("id") FROM "issue_with_ranks_missing";
     1.5 +
     1.6  BEGIN;
     1.7  
     1.8  CREATE OR REPLACE VIEW "liquid_feedback_version" AS
     1.9 @@ -79,6 +81,7 @@
    1.10  COMMENT ON COLUMN "issue"."snapshot"                IS 'Point in time, when snapshot tables have been updated and "population" and *_count values were precalculated';
    1.11  COMMENT ON COLUMN "issue"."status_quo_schulze_rank" IS 'Schulze rank of status quo, as calculated by "calculate_ranks" function';
    1.12  
    1.13 +ALTER TABLE "initiative" DROP COLUMN "agreed";
    1.14  ALTER TABLE "initiative" ADD COLUMN "direct_majority"        BOOLEAN;
    1.15  ALTER TABLE "initiative" ADD COLUMN "indirect_majority"      BOOLEAN;
    1.16  ALTER TABLE "initiative" ADD COLUMN "schulze_rank"           INT4;
    1.17 @@ -1025,9 +1028,66 @@
    1.18      END;
    1.19    $$;
    1.20  
    1.21 --- TODO: recalculate voting results
    1.22 +COMMIT;
    1.23 +
    1.24 +BEGIN;
    1.25 +
    1.26 +UPDATE "initiative" SET
    1.27 +    "direct_majority"        = "rank" NOTNULL,
    1.28 +    "indirect_majority"      = "rank" NOTNULL,
    1.29 +    "schulze_rank"           = "rank",
    1.30 +    "better_than_status_quo" = "rank" NOTNULL,
    1.31 +    "worse_than_status_quo"  = "rank" ISNULL,
    1.32 +    "reverse_beat_path"      = "rank" ISNULL,
    1.33 +    "multistage_majority"    = "rank" ISNULL,
    1.34 +    "eligible"               = "rank" NOTNULL,
    1.35 +    "winner"                 = ("rank" = 1)
    1.36 +  FROM "issue"
    1.37 +  WHERE "issue"."id" = "initiative"."issue_id"
    1.38 +  AND "issue"."state" IN ('finished_without_winner', 'finished_with_winner')
    1.39 +  AND "initiative"."admitted";
    1.40  
    1.41 -COMMIT;
    1.42 +UPDATE "issue" SET "status_quo_schulze_rank" = "subquery"."rank"
    1.43 +  FROM (
    1.44 +    SELECT
    1.45 +      "issue"."id" AS "issue_id",
    1.46 +      COALESCE(max("initiative"."rank") + 1) AS "rank"
    1.47 +    FROM "issue" JOIN "initiative"
    1.48 +    ON "issue"."id" = "initiative"."issue_id"
    1.49 +    WHERE "issue"."state" IN ('finished_without_winner', 'finished_with_winner')
    1.50 +    AND "initiative"."admitted"
    1.51 +    GROUP BY "issue"."id"
    1.52 +  ) AS "subquery"
    1.53 +  WHERE "issue"."id" = "subquery"."issue_id";
    1.54 +
    1.55 +CREATE FUNCTION "update__set_remaining_ranks"("issue_id_p" "issue"."id"%TYPE)
    1.56 +  RETURNS VOID
    1.57 +  LANGUAGE 'plpgsql' AS $$
    1.58 +    DECLARE
    1.59 +      "rank_v"          INT4;
    1.60 +      "initiative_id_v" INT4;
    1.61 +    BEGIN
    1.62 +      SELECT "status_quo_schulze_rank" INTO "rank_v"
    1.63 +        FROM "issue" WHERE "id" = "issue_id_p";
    1.64 +      FOR "initiative_id_v" IN
    1.65 +        SELECT "id" FROM "initiative"
    1.66 +        WHERE "issue_id" = "issue_id_p" AND "admitted" AND "rank" ISNULL
    1.67 +        ORDER BY "vote_ratio"("positive_votes", "negative_votes") DESC
    1.68 +      LOOP
    1.69 +        UPDATE "initiative" SET
    1.70 +          "schulze_rank" = "rank_v" + 1,
    1.71 +          "rank"         = "rank_v"
    1.72 +          WHERE "id" = "initiative_id_v";
    1.73 +        "rank_v" := "rank_v" + 1;
    1.74 +      END LOOP;
    1.75 +      RETURN;
    1.76 +    END;
    1.77 +  $$;
    1.78 +
    1.79 +SELECT "update__set_remaining_ranks"("id") FROM "issue"
    1.80 +  WHERE "state" IN ('finished_without_winner', 'finished_with_winner');
    1.81 +
    1.82 +DROP FUNCTION "update__set_remaining_ranks"("issue"."id"%TYPE);
    1.83  
    1.84  UPDATE "suggestion" SET "draft_id" = "subquery"."draft_id"
    1.85    FROM (
    1.86 @@ -1041,4 +1101,6 @@
    1.87    ) AS "subquery"
    1.88    WHERE "suggestion"."id" = "subquery"."suggestion_id";
    1.89  
    1.90 +COMMIT;
    1.91 +
    1.92  ALTER TABLE "suggestion" ALTER COLUMN "draft_id" SET NOT NULL;

Impressum / About Us