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;