liquid_feedback_core
view update/core-update.v1.4.0_rc2-v1.4.0_rc3.sql @ 352:98c14d8d07f1
Support for proportional ordering of suggestions in core.sql; Begin of work on "lf_update_suggestion_order" (a second background job for sorting suggestions based on a proportional preferential voting system)
| author | jbe |
|---|---|
| date | Sat Mar 16 17:22:01 2013 +0100 (2013-03-16) |
| parents | 1be788313b84 |
| children |
line source
1 BEGIN;
3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
4 SELECT * FROM (VALUES ('1.4.0_rc3', 1, 4, -1))
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 "area_id_v" "area"."id"%TYPE;
12 "unit_id_v" "unit"."id"%TYPE;
13 "member_id_v" "member"."id"%TYPE;
14 BEGIN
15 PERFORM "lock_issue"("issue_id_p");
16 SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";
17 SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v";
18 DELETE FROM "delegating_voter"
19 WHERE "issue_id" = "issue_id_p";
20 DELETE FROM "direct_voter"
21 WHERE "issue_id" = "issue_id_p"
22 AND "autoreject" = TRUE;
23 DELETE FROM "direct_voter"
24 USING (
25 SELECT
26 "direct_voter"."member_id"
27 FROM "direct_voter"
28 JOIN "member" ON "direct_voter"."member_id" = "member"."id"
29 LEFT JOIN "privilege"
30 ON "privilege"."unit_id" = "unit_id_v"
31 AND "privilege"."member_id" = "direct_voter"."member_id"
32 WHERE "direct_voter"."issue_id" = "issue_id_p" AND (
33 "member"."active" = FALSE OR
34 "privilege"."voting_right" ISNULL OR
35 "privilege"."voting_right" = FALSE
36 )
37 ) AS "subquery"
38 WHERE "direct_voter"."issue_id" = "issue_id_p"
39 AND "direct_voter"."member_id" = "subquery"."member_id";
40 UPDATE "direct_voter" SET "weight" = 1
41 WHERE "issue_id" = "issue_id_p";
42 PERFORM "add_vote_delegations"("issue_id_p");
43 FOR "member_id_v" IN
44 SELECT "interest"."member_id"
45 FROM "interest"
46 JOIN "member"
47 ON "interest"."member_id" = "member"."id"
48 JOIN "privilege"
49 ON "privilege"."unit_id" = "unit_id_v"
50 AND "privilege"."member_id" = "member"."id"
51 LEFT JOIN "direct_voter"
52 ON "interest"."member_id" = "direct_voter"."member_id"
53 AND "interest"."issue_id" = "direct_voter"."issue_id"
54 LEFT JOIN "delegating_voter"
55 ON "interest"."member_id" = "delegating_voter"."member_id"
56 AND "interest"."issue_id" = "delegating_voter"."issue_id"
57 WHERE "interest"."issue_id" = "issue_id_p"
58 AND "interest"."autoreject" = TRUE
59 AND "member"."active"
60 AND "privilege"."voting_right"
61 AND "direct_voter"."member_id" ISNULL
62 AND "delegating_voter"."member_id" ISNULL
63 UNION SELECT "membership"."member_id"
64 FROM "membership"
65 JOIN "member"
66 ON "membership"."member_id" = "member"."id"
67 JOIN "privilege"
68 ON "privilege"."unit_id" = "unit_id_v"
69 AND "privilege"."member_id" = "member"."id"
70 LEFT JOIN "interest"
71 ON "membership"."member_id" = "interest"."member_id"
72 AND "interest"."issue_id" = "issue_id_p"
73 LEFT JOIN "direct_voter"
74 ON "membership"."member_id" = "direct_voter"."member_id"
75 AND "direct_voter"."issue_id" = "issue_id_p"
76 LEFT JOIN "delegating_voter"
77 ON "membership"."member_id" = "delegating_voter"."member_id"
78 AND "delegating_voter"."issue_id" = "issue_id_p"
79 WHERE "membership"."area_id" = "area_id_v"
80 AND "membership"."autoreject" = TRUE
81 AND "member"."active"
82 AND "privilege"."voting_right"
83 AND "interest"."autoreject" ISNULL
84 AND "direct_voter"."member_id" ISNULL
85 AND "delegating_voter"."member_id" ISNULL
86 LOOP
87 INSERT INTO "direct_voter"
88 ("member_id", "issue_id", "weight", "autoreject") VALUES
89 ("member_id_v", "issue_id_p", 1, TRUE);
90 INSERT INTO "vote" (
91 "member_id",
92 "issue_id",
93 "initiative_id",
94 "grade"
95 ) SELECT
96 "member_id_v" AS "member_id",
97 "issue_id_p" AS "issue_id",
98 "id" AS "initiative_id",
99 -1 AS "grade"
100 FROM "initiative" WHERE "issue_id" = "issue_id_p";
101 END LOOP;
102 PERFORM "add_vote_delegations"("issue_id_p");
103 UPDATE "issue" SET
104 "state" = 'calculation',
105 "closed" = now(),
106 "voter_count" = (
107 SELECT coalesce(sum("weight"), 0)
108 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
109 )
110 WHERE "id" = "issue_id_p";
111 UPDATE "initiative" SET
112 "positive_votes" = "vote_counts"."positive_votes",
113 "negative_votes" = "vote_counts"."negative_votes",
114 "agreed" = CASE WHEN "majority_strict" THEN
115 "vote_counts"."positive_votes" * "majority_den" >
116 "majority_num" *
117 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
118 ELSE
119 "vote_counts"."positive_votes" * "majority_den" >=
120 "majority_num" *
121 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
122 END
123 FROM
124 ( SELECT
125 "initiative"."id" AS "initiative_id",
126 coalesce(
127 sum(
128 CASE WHEN "grade" > 0 THEN "direct_voter"."weight" ELSE 0 END
129 ),
130 0
131 ) AS "positive_votes",
132 coalesce(
133 sum(
134 CASE WHEN "grade" < 0 THEN "direct_voter"."weight" ELSE 0 END
135 ),
136 0
137 ) AS "negative_votes"
138 FROM "initiative"
139 JOIN "issue" ON "initiative"."issue_id" = "issue"."id"
140 JOIN "policy" ON "issue"."policy_id" = "policy"."id"
141 LEFT JOIN "direct_voter"
142 ON "direct_voter"."issue_id" = "initiative"."issue_id"
143 LEFT JOIN "vote"
144 ON "vote"."initiative_id" = "initiative"."id"
145 AND "vote"."member_id" = "direct_voter"."member_id"
146 WHERE "initiative"."issue_id" = "issue_id_p"
147 AND "initiative"."admitted" -- NOTE: NULL case is handled too
148 GROUP BY "initiative"."id"
149 ) AS "vote_counts",
150 "issue",
151 "policy"
152 WHERE "vote_counts"."initiative_id" = "initiative"."id"
153 AND "issue"."id" = "initiative"."issue_id"
154 AND "policy"."id" = "issue"."policy_id";
155 -- NOTE: "closed" column of issue must be set at this point
156 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
157 INSERT INTO "battle" (
158 "issue_id",
159 "winning_initiative_id", "losing_initiative_id",
160 "count"
161 ) SELECT
162 "issue_id",
163 "winning_initiative_id", "losing_initiative_id",
164 "count"
165 FROM "battle_view" WHERE "issue_id" = "issue_id_p";
166 END;
167 $$;
169 COMMIT;