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