liquid_feedback_core
changeset 433:603f6e4bbedf v3.0.2
Bugfix in "close_voting" function that only affected PostgreSQL versions prior 9.1
author | jbe |
---|---|
date | Wed Jun 11 00:34:12 2014 +0200 (2014-06-11) |
parents | 5676b0d33833 |
children | 692729de151a |
files | core.sql update/core-update.v3.0.1-v3.0.2.sql |
line diff
1.1 --- a/core.sql Thu May 22 12:40:30 2014 +0200 1.2 +++ b/core.sql Wed Jun 11 00:34:12 2014 +0200 1.3 @@ -3777,7 +3777,7 @@ 1.4 JOIN "vote" AS "agg" 1.5 ON "initiative"."id" = "agg"."initiative_id" 1.6 AND "vote"."member_id" = "agg"."member_id" 1.7 - GROUP BY "vote"."initiative_id", "vote"."member_id" 1.8 + GROUP BY "vote"."initiative_id", "vote"."member_id", "vote"."grade" 1.9 ) AS "subquery" 1.10 WHERE "vote"."issue_id" = "issue_id_p" 1.11 AND "vote"."initiative_id" = "subquery"."initiative_id"
2.1 --- a/update/core-update.v3.0.1-v3.0.2.sql Thu May 22 12:40:30 2014 +0200 2.2 +++ b/update/core-update.v3.0.1-v3.0.2.sql Wed Jun 11 00:34:12 2014 +0200 2.3 @@ -28,6 +28,121 @@ 2.4 COMMENT ON COLUMN "initiative"."reverse_beat_path" IS 'TRUE, if there is a beat path (may include ties) from this initiative to the status quo; set to NULL if "policy"."defeat_strength" is set to ''simple'''; 2.5 2.6 2.7 +CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE) 2.8 + RETURNS VOID 2.9 + LANGUAGE 'plpgsql' VOLATILE AS $$ 2.10 + DECLARE 2.11 + "area_id_v" "area"."id"%TYPE; 2.12 + "unit_id_v" "unit"."id"%TYPE; 2.13 + "member_id_v" "member"."id"%TYPE; 2.14 + BEGIN 2.15 + PERFORM "require_transaction_isolation"(); 2.16 + SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p"; 2.17 + SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v"; 2.18 + -- override protection triggers: 2.19 + INSERT INTO "temporary_transaction_data" ("key", "value") 2.20 + VALUES ('override_protection_triggers', TRUE::TEXT); 2.21 + -- delete timestamp of voting comment: 2.22 + UPDATE "direct_voter" SET "comment_changed" = NULL 2.23 + WHERE "issue_id" = "issue_id_p"; 2.24 + -- delete delegating votes (in cases of manual reset of issue state): 2.25 + DELETE FROM "delegating_voter" 2.26 + WHERE "issue_id" = "issue_id_p"; 2.27 + -- delete votes from non-privileged voters: 2.28 + DELETE FROM "direct_voter" 2.29 + USING ( 2.30 + SELECT 2.31 + "direct_voter"."member_id" 2.32 + FROM "direct_voter" 2.33 + JOIN "member" ON "direct_voter"."member_id" = "member"."id" 2.34 + LEFT JOIN "privilege" 2.35 + ON "privilege"."unit_id" = "unit_id_v" 2.36 + AND "privilege"."member_id" = "direct_voter"."member_id" 2.37 + WHERE "direct_voter"."issue_id" = "issue_id_p" AND ( 2.38 + "member"."active" = FALSE OR 2.39 + "privilege"."voting_right" ISNULL OR 2.40 + "privilege"."voting_right" = FALSE 2.41 + ) 2.42 + ) AS "subquery" 2.43 + WHERE "direct_voter"."issue_id" = "issue_id_p" 2.44 + AND "direct_voter"."member_id" = "subquery"."member_id"; 2.45 + -- consider delegations: 2.46 + UPDATE "direct_voter" SET "weight" = 1 2.47 + WHERE "issue_id" = "issue_id_p"; 2.48 + PERFORM "add_vote_delegations"("issue_id_p"); 2.49 + -- mark first preferences: 2.50 + UPDATE "vote" SET "first_preference" = "subquery"."first_preference" 2.51 + FROM ( 2.52 + SELECT 2.53 + "vote"."initiative_id", 2.54 + "vote"."member_id", 2.55 + CASE WHEN "vote"."grade" > 0 THEN 2.56 + CASE WHEN "vote"."grade" = max("agg"."grade") THEN TRUE ELSE FALSE END 2.57 + ELSE NULL 2.58 + END AS "first_preference" 2.59 + FROM "vote" 2.60 + JOIN "initiative" -- NOTE: due to missing index on issue_id 2.61 + ON "vote"."issue_id" = "initiative"."issue_id" 2.62 + JOIN "vote" AS "agg" 2.63 + ON "initiative"."id" = "agg"."initiative_id" 2.64 + AND "vote"."member_id" = "agg"."member_id" 2.65 + GROUP BY "vote"."initiative_id", "vote"."member_id", "vote"."grade" 2.66 + ) AS "subquery" 2.67 + WHERE "vote"."issue_id" = "issue_id_p" 2.68 + AND "vote"."initiative_id" = "subquery"."initiative_id" 2.69 + AND "vote"."member_id" = "subquery"."member_id"; 2.70 + -- finish overriding protection triggers (avoids garbage): 2.71 + DELETE FROM "temporary_transaction_data" 2.72 + WHERE "key" = 'override_protection_triggers'; 2.73 + -- materialize battle_view: 2.74 + -- NOTE: "closed" column of issue must be set at this point 2.75 + DELETE FROM "battle" WHERE "issue_id" = "issue_id_p"; 2.76 + INSERT INTO "battle" ( 2.77 + "issue_id", 2.78 + "winning_initiative_id", "losing_initiative_id", 2.79 + "count" 2.80 + ) SELECT 2.81 + "issue_id", 2.82 + "winning_initiative_id", "losing_initiative_id", 2.83 + "count" 2.84 + FROM "battle_view" WHERE "issue_id" = "issue_id_p"; 2.85 + -- set voter count: 2.86 + UPDATE "issue" SET 2.87 + "voter_count" = ( 2.88 + SELECT coalesce(sum("weight"), 0) 2.89 + FROM "direct_voter" WHERE "issue_id" = "issue_id_p" 2.90 + ) 2.91 + WHERE "id" = "issue_id_p"; 2.92 + -- calculate "first_preference_votes": 2.93 + UPDATE "initiative" 2.94 + SET "first_preference_votes" = coalesce("subquery"."sum", 0) 2.95 + FROM ( 2.96 + SELECT "vote"."initiative_id", sum("direct_voter"."weight") 2.97 + FROM "vote" JOIN "direct_voter" 2.98 + ON "vote"."issue_id" = "direct_voter"."issue_id" 2.99 + AND "vote"."member_id" = "direct_voter"."member_id" 2.100 + WHERE "vote"."first_preference" 2.101 + GROUP BY "vote"."initiative_id" 2.102 + ) AS "subquery" 2.103 + WHERE "initiative"."issue_id" = "issue_id_p" 2.104 + AND "initiative"."admitted" 2.105 + AND "initiative"."id" = "subquery"."initiative_id"; 2.106 + -- copy "positive_votes" and "negative_votes" from "battle" table: 2.107 + UPDATE "initiative" SET 2.108 + "positive_votes" = "battle_win"."count", 2.109 + "negative_votes" = "battle_lose"."count" 2.110 + FROM "battle" AS "battle_win", "battle" AS "battle_lose" 2.111 + WHERE 2.112 + "battle_win"."issue_id" = "issue_id_p" AND 2.113 + "battle_win"."winning_initiative_id" = "initiative"."id" AND 2.114 + "battle_win"."losing_initiative_id" ISNULL AND 2.115 + "battle_lose"."issue_id" = "issue_id_p" AND 2.116 + "battle_lose"."losing_initiative_id" = "initiative"."id" AND 2.117 + "battle_lose"."winning_initiative_id" ISNULL; 2.118 + END; 2.119 + $$; 2.120 + 2.121 + 2.122 DROP FUNCTION "calculate_ranks"("issue"."id"%TYPE); 2.123 DROP FUNCTION "defeat_strength"(INT4, INT4); 2.124