liquid_feedback_core

annotate update/core-update.v1.2.8-v1.2.9.sql @ 625:7f488033d5ee

find changesets by author, revision, files, or words in the commit message
Fixed mistake in last-level tie-breaking
author jbe
date Wed Jan 01 16:49:21 2025 +0100 (10 months ago)
parents e588fdf1676e
children
rev   line source
jbe@87 1 BEGIN;
jbe@87 2
jbe@87 3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
jbe@87 4 SELECT * FROM (VALUES ('1.2.9', 1, 2, 9))
jbe@87 5 AS "subquery"("string", "major", "minor", "revision");
jbe@87 6
jbe@87 7 CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
jbe@87 8 RETURNS VOID
jbe@87 9 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@87 10 DECLARE
jbe@87 11 "issue_row" "issue"%ROWTYPE;
jbe@87 12 "member_id_v" "member"."id"%TYPE;
jbe@87 13 BEGIN
jbe@87 14 PERFORM "lock_issue"("issue_id_p");
jbe@87 15 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
jbe@87 16 DELETE FROM "delegating_voter"
jbe@87 17 WHERE "issue_id" = "issue_id_p";
jbe@87 18 DELETE FROM "direct_voter"
jbe@87 19 WHERE "issue_id" = "issue_id_p"
jbe@87 20 AND "autoreject" = TRUE;
jbe@87 21 DELETE FROM "direct_voter" USING "member"
jbe@87 22 WHERE "direct_voter"."member_id" = "member"."id"
jbe@87 23 AND "direct_voter"."issue_id" = "issue_id_p"
jbe@87 24 AND "member"."active" = FALSE;
jbe@87 25 UPDATE "direct_voter" SET "weight" = 1
jbe@87 26 WHERE "issue_id" = "issue_id_p";
jbe@87 27 PERFORM "add_vote_delegations"("issue_id_p");
jbe@87 28 FOR "member_id_v" IN
jbe@87 29 SELECT "interest"."member_id"
jbe@87 30 FROM "interest"
jbe@87 31 JOIN "member"
jbe@87 32 ON "interest"."member_id" = "member"."id"
jbe@87 33 LEFT JOIN "direct_voter"
jbe@87 34 ON "interest"."member_id" = "direct_voter"."member_id"
jbe@87 35 AND "interest"."issue_id" = "direct_voter"."issue_id"
jbe@87 36 LEFT JOIN "delegating_voter"
jbe@87 37 ON "interest"."member_id" = "delegating_voter"."member_id"
jbe@87 38 AND "interest"."issue_id" = "delegating_voter"."issue_id"
jbe@87 39 WHERE "interest"."issue_id" = "issue_id_p"
jbe@87 40 AND "interest"."autoreject" = TRUE
jbe@87 41 AND "member"."active"
jbe@87 42 AND "direct_voter"."member_id" ISNULL
jbe@87 43 AND "delegating_voter"."member_id" ISNULL
jbe@87 44 UNION SELECT "membership"."member_id"
jbe@87 45 FROM "membership"
jbe@87 46 JOIN "member"
jbe@87 47 ON "membership"."member_id" = "member"."id"
jbe@87 48 LEFT JOIN "interest"
jbe@87 49 ON "membership"."member_id" = "interest"."member_id"
jbe@87 50 AND "interest"."issue_id" = "issue_id_p"
jbe@87 51 LEFT JOIN "direct_voter"
jbe@87 52 ON "membership"."member_id" = "direct_voter"."member_id"
jbe@87 53 AND "direct_voter"."issue_id" = "issue_id_p"
jbe@87 54 LEFT JOIN "delegating_voter"
jbe@87 55 ON "membership"."member_id" = "delegating_voter"."member_id"
jbe@87 56 AND "delegating_voter"."issue_id" = "issue_id_p"
jbe@87 57 WHERE "membership"."area_id" = "issue_row"."area_id"
jbe@87 58 AND "membership"."autoreject" = TRUE
jbe@87 59 AND "member"."active"
jbe@87 60 AND "interest"."autoreject" ISNULL
jbe@87 61 AND "direct_voter"."member_id" ISNULL
jbe@87 62 AND "delegating_voter"."member_id" ISNULL
jbe@87 63 LOOP
jbe@87 64 INSERT INTO "direct_voter"
jbe@87 65 ("member_id", "issue_id", "weight", "autoreject") VALUES
jbe@87 66 ("member_id_v", "issue_id_p", 1, TRUE);
jbe@87 67 INSERT INTO "vote" (
jbe@87 68 "member_id",
jbe@87 69 "issue_id",
jbe@87 70 "initiative_id",
jbe@87 71 "grade"
jbe@87 72 ) SELECT
jbe@87 73 "member_id_v" AS "member_id",
jbe@87 74 "issue_id_p" AS "issue_id",
jbe@87 75 "id" AS "initiative_id",
jbe@87 76 -1 AS "grade"
jbe@87 77 FROM "initiative" WHERE "issue_id" = "issue_id_p";
jbe@87 78 END LOOP;
jbe@87 79 PERFORM "add_vote_delegations"("issue_id_p");
jbe@87 80 UPDATE "issue" SET
jbe@87 81 "closed" = now(),
jbe@87 82 "voter_count" = (
jbe@87 83 SELECT coalesce(sum("weight"), 0)
jbe@87 84 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
jbe@87 85 )
jbe@87 86 WHERE "id" = "issue_id_p";
jbe@87 87 UPDATE "initiative" SET
jbe@87 88 "positive_votes" = "vote_counts"."positive_votes",
jbe@87 89 "negative_votes" = "vote_counts"."negative_votes",
jbe@87 90 "agreed" = CASE WHEN "majority_strict" THEN
jbe@87 91 "vote_counts"."positive_votes" * "majority_den" >
jbe@87 92 "majority_num" *
jbe@87 93 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
jbe@87 94 ELSE
jbe@87 95 "vote_counts"."positive_votes" * "majority_den" >=
jbe@87 96 "majority_num" *
jbe@87 97 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
jbe@87 98 END
jbe@87 99 FROM
jbe@87 100 ( SELECT
jbe@87 101 "initiative"."id" AS "initiative_id",
jbe@87 102 coalesce(
jbe@87 103 sum(
jbe@87 104 CASE WHEN "grade" > 0 THEN "direct_voter"."weight" ELSE 0 END
jbe@87 105 ),
jbe@87 106 0
jbe@87 107 ) AS "positive_votes",
jbe@87 108 coalesce(
jbe@87 109 sum(
jbe@87 110 CASE WHEN "grade" < 0 THEN "direct_voter"."weight" ELSE 0 END
jbe@87 111 ),
jbe@87 112 0
jbe@87 113 ) AS "negative_votes"
jbe@87 114 FROM "initiative"
jbe@87 115 JOIN "issue" ON "initiative"."issue_id" = "issue"."id"
jbe@87 116 JOIN "policy" ON "issue"."policy_id" = "policy"."id"
jbe@87 117 LEFT JOIN "direct_voter"
jbe@87 118 ON "direct_voter"."issue_id" = "initiative"."issue_id"
jbe@87 119 LEFT JOIN "vote"
jbe@87 120 ON "vote"."initiative_id" = "initiative"."id"
jbe@87 121 AND "vote"."member_id" = "direct_voter"."member_id"
jbe@87 122 WHERE "initiative"."issue_id" = "issue_id_p"
jbe@87 123 AND "initiative"."admitted" -- NOTE: NULL case is handled too
jbe@87 124 GROUP BY "initiative"."id"
jbe@87 125 ) AS "vote_counts",
jbe@87 126 "issue",
jbe@87 127 "policy"
jbe@87 128 WHERE "vote_counts"."initiative_id" = "initiative"."id"
jbe@87 129 AND "issue"."id" = "initiative"."issue_id"
jbe@87 130 AND "policy"."id" = "issue"."policy_id";
jbe@87 131 -- NOTE: "closed" column of issue must be set at this point
jbe@87 132 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
jbe@87 133 INSERT INTO "battle" (
jbe@87 134 "issue_id",
jbe@87 135 "winning_initiative_id", "losing_initiative_id",
jbe@87 136 "count"
jbe@87 137 ) SELECT
jbe@87 138 "issue_id",
jbe@87 139 "winning_initiative_id", "losing_initiative_id",
jbe@87 140 "count"
jbe@87 141 FROM "battle_view" WHERE "issue_id" = "issue_id_p";
jbe@87 142 END;
jbe@87 143 $$;
jbe@87 144
jbe@87 145 COMMIT;

Impressum / About Us