rev |
line source |
jbe@164
|
1 BEGIN;
|
jbe@164
|
2
|
jbe@164
|
3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
|
jbe@164
|
4 SELECT * FROM (VALUES ('1.4.0_rc4', 1, 4, -1))
|
jbe@164
|
5 AS "subquery"("string", "major", "minor", "revision");
|
jbe@164
|
6
|
jbe@164
|
7 CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
|
jbe@164
|
8 RETURNS VOID
|
jbe@164
|
9 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@164
|
10 DECLARE
|
jbe@164
|
11 "area_id_v" "area"."id"%TYPE;
|
jbe@164
|
12 "unit_id_v" "unit"."id"%TYPE;
|
jbe@164
|
13 "member_id_v" "member"."id"%TYPE;
|
jbe@164
|
14 BEGIN
|
jbe@164
|
15 PERFORM "lock_issue"("issue_id_p");
|
jbe@164
|
16 SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";
|
jbe@164
|
17 SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v";
|
jbe@164
|
18 DELETE FROM "delegating_voter"
|
jbe@164
|
19 WHERE "issue_id" = "issue_id_p";
|
jbe@164
|
20 DELETE FROM "direct_voter"
|
jbe@164
|
21 WHERE "issue_id" = "issue_id_p"
|
jbe@164
|
22 AND "autoreject" = TRUE;
|
jbe@164
|
23 DELETE FROM "direct_voter"
|
jbe@164
|
24 USING (
|
jbe@164
|
25 SELECT
|
jbe@164
|
26 "direct_voter"."member_id"
|
jbe@164
|
27 FROM "direct_voter"
|
jbe@164
|
28 JOIN "member" ON "direct_voter"."member_id" = "member"."id"
|
jbe@164
|
29 LEFT JOIN "privilege"
|
jbe@164
|
30 ON "privilege"."unit_id" = "unit_id_v"
|
jbe@164
|
31 AND "privilege"."member_id" = "direct_voter"."member_id"
|
jbe@164
|
32 WHERE "direct_voter"."issue_id" = "issue_id_p" AND (
|
jbe@164
|
33 "member"."active" = FALSE OR
|
jbe@164
|
34 "privilege"."voting_right" ISNULL OR
|
jbe@164
|
35 "privilege"."voting_right" = FALSE
|
jbe@164
|
36 )
|
jbe@164
|
37 ) AS "subquery"
|
jbe@164
|
38 WHERE "direct_voter"."issue_id" = "issue_id_p"
|
jbe@164
|
39 AND "direct_voter"."member_id" = "subquery"."member_id";
|
jbe@164
|
40 UPDATE "direct_voter" SET "weight" = 1
|
jbe@164
|
41 WHERE "issue_id" = "issue_id_p";
|
jbe@164
|
42 PERFORM "add_vote_delegations"("issue_id_p");
|
jbe@164
|
43 FOR "member_id_v" IN
|
jbe@164
|
44 SELECT "interest"."member_id"
|
jbe@164
|
45 FROM "interest"
|
jbe@164
|
46 JOIN "member"
|
jbe@164
|
47 ON "interest"."member_id" = "member"."id"
|
jbe@164
|
48 JOIN "privilege"
|
jbe@164
|
49 ON "privilege"."unit_id" = "unit_id_v"
|
jbe@164
|
50 AND "privilege"."member_id" = "member"."id"
|
jbe@164
|
51 LEFT JOIN "direct_voter"
|
jbe@164
|
52 ON "interest"."member_id" = "direct_voter"."member_id"
|
jbe@164
|
53 AND "interest"."issue_id" = "direct_voter"."issue_id"
|
jbe@164
|
54 LEFT JOIN "delegating_voter"
|
jbe@164
|
55 ON "interest"."member_id" = "delegating_voter"."member_id"
|
jbe@164
|
56 AND "interest"."issue_id" = "delegating_voter"."issue_id"
|
jbe@164
|
57 WHERE "interest"."issue_id" = "issue_id_p"
|
jbe@164
|
58 AND "interest"."autoreject" = TRUE
|
jbe@164
|
59 AND "member"."active"
|
jbe@164
|
60 AND "privilege"."voting_right"
|
jbe@164
|
61 AND "direct_voter"."member_id" ISNULL
|
jbe@164
|
62 AND "delegating_voter"."member_id" ISNULL
|
jbe@164
|
63 UNION SELECT "membership"."member_id"
|
jbe@164
|
64 FROM "membership"
|
jbe@164
|
65 JOIN "member"
|
jbe@164
|
66 ON "membership"."member_id" = "member"."id"
|
jbe@164
|
67 JOIN "privilege"
|
jbe@164
|
68 ON "privilege"."unit_id" = "unit_id_v"
|
jbe@164
|
69 AND "privilege"."member_id" = "member"."id"
|
jbe@164
|
70 LEFT JOIN "interest"
|
jbe@164
|
71 ON "membership"."member_id" = "interest"."member_id"
|
jbe@164
|
72 AND "interest"."issue_id" = "issue_id_p"
|
jbe@164
|
73 LEFT JOIN "direct_voter"
|
jbe@164
|
74 ON "membership"."member_id" = "direct_voter"."member_id"
|
jbe@164
|
75 AND "direct_voter"."issue_id" = "issue_id_p"
|
jbe@164
|
76 LEFT JOIN "delegating_voter"
|
jbe@164
|
77 ON "membership"."member_id" = "delegating_voter"."member_id"
|
jbe@164
|
78 AND "delegating_voter"."issue_id" = "issue_id_p"
|
jbe@164
|
79 WHERE "membership"."area_id" = "area_id_v"
|
jbe@164
|
80 AND "membership"."autoreject" = TRUE
|
jbe@164
|
81 AND "member"."active"
|
jbe@164
|
82 AND "privilege"."voting_right"
|
jbe@164
|
83 AND "interest"."autoreject" ISNULL
|
jbe@164
|
84 AND "direct_voter"."member_id" ISNULL
|
jbe@164
|
85 AND "delegating_voter"."member_id" ISNULL
|
jbe@164
|
86 LOOP
|
jbe@164
|
87 INSERT INTO "direct_voter"
|
jbe@164
|
88 ("member_id", "issue_id", "weight", "autoreject") VALUES
|
jbe@164
|
89 ("member_id_v", "issue_id_p", 1, TRUE);
|
jbe@164
|
90 INSERT INTO "vote" (
|
jbe@164
|
91 "member_id",
|
jbe@164
|
92 "issue_id",
|
jbe@164
|
93 "initiative_id",
|
jbe@164
|
94 "grade"
|
jbe@164
|
95 ) SELECT
|
jbe@164
|
96 "member_id_v" AS "member_id",
|
jbe@164
|
97 "issue_id_p" AS "issue_id",
|
jbe@164
|
98 "id" AS "initiative_id",
|
jbe@164
|
99 -1 AS "grade"
|
jbe@164
|
100 FROM "initiative"
|
jbe@164
|
101 WHERE "issue_id" = "issue_id_p" AND "admitted";
|
jbe@164
|
102 END LOOP;
|
jbe@164
|
103 PERFORM "add_vote_delegations"("issue_id_p");
|
jbe@164
|
104 UPDATE "issue" SET
|
jbe@164
|
105 "state" = 'calculation',
|
jbe@164
|
106 "closed" = now(),
|
jbe@164
|
107 "voter_count" = (
|
jbe@164
|
108 SELECT coalesce(sum("weight"), 0)
|
jbe@164
|
109 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
|
jbe@164
|
110 )
|
jbe@164
|
111 WHERE "id" = "issue_id_p";
|
jbe@164
|
112 UPDATE "initiative" SET
|
jbe@164
|
113 "positive_votes" = "vote_counts"."positive_votes",
|
jbe@164
|
114 "negative_votes" = "vote_counts"."negative_votes",
|
jbe@164
|
115 "agreed" = CASE WHEN "majority_strict" THEN
|
jbe@164
|
116 "vote_counts"."positive_votes" * "majority_den" >
|
jbe@164
|
117 "majority_num" *
|
jbe@164
|
118 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
|
jbe@164
|
119 ELSE
|
jbe@164
|
120 "vote_counts"."positive_votes" * "majority_den" >=
|
jbe@164
|
121 "majority_num" *
|
jbe@164
|
122 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
|
jbe@164
|
123 END
|
jbe@164
|
124 FROM
|
jbe@164
|
125 ( SELECT
|
jbe@164
|
126 "initiative"."id" AS "initiative_id",
|
jbe@164
|
127 coalesce(
|
jbe@164
|
128 sum(
|
jbe@164
|
129 CASE WHEN "grade" > 0 THEN "direct_voter"."weight" ELSE 0 END
|
jbe@164
|
130 ),
|
jbe@164
|
131 0
|
jbe@164
|
132 ) AS "positive_votes",
|
jbe@164
|
133 coalesce(
|
jbe@164
|
134 sum(
|
jbe@164
|
135 CASE WHEN "grade" < 0 THEN "direct_voter"."weight" ELSE 0 END
|
jbe@164
|
136 ),
|
jbe@164
|
137 0
|
jbe@164
|
138 ) AS "negative_votes"
|
jbe@164
|
139 FROM "initiative"
|
jbe@164
|
140 JOIN "issue" ON "initiative"."issue_id" = "issue"."id"
|
jbe@164
|
141 JOIN "policy" ON "issue"."policy_id" = "policy"."id"
|
jbe@164
|
142 LEFT JOIN "direct_voter"
|
jbe@164
|
143 ON "direct_voter"."issue_id" = "initiative"."issue_id"
|
jbe@164
|
144 LEFT JOIN "vote"
|
jbe@164
|
145 ON "vote"."initiative_id" = "initiative"."id"
|
jbe@164
|
146 AND "vote"."member_id" = "direct_voter"."member_id"
|
jbe@164
|
147 WHERE "initiative"."issue_id" = "issue_id_p"
|
jbe@164
|
148 AND "initiative"."admitted" -- NOTE: NULL case is handled too
|
jbe@164
|
149 GROUP BY "initiative"."id"
|
jbe@164
|
150 ) AS "vote_counts",
|
jbe@164
|
151 "issue",
|
jbe@164
|
152 "policy"
|
jbe@164
|
153 WHERE "vote_counts"."initiative_id" = "initiative"."id"
|
jbe@164
|
154 AND "issue"."id" = "initiative"."issue_id"
|
jbe@164
|
155 AND "policy"."id" = "issue"."policy_id";
|
jbe@164
|
156 -- NOTE: "closed" column of issue must be set at this point
|
jbe@164
|
157 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
|
jbe@164
|
158 INSERT INTO "battle" (
|
jbe@164
|
159 "issue_id",
|
jbe@164
|
160 "winning_initiative_id", "losing_initiative_id",
|
jbe@164
|
161 "count"
|
jbe@164
|
162 ) SELECT
|
jbe@164
|
163 "issue_id",
|
jbe@164
|
164 "winning_initiative_id", "losing_initiative_id",
|
jbe@164
|
165 "count"
|
jbe@164
|
166 FROM "battle_view" WHERE "issue_id" = "issue_id_p";
|
jbe@164
|
167 END;
|
jbe@164
|
168 $$;
|
jbe@164
|
169
|
jbe@164
|
170 CREATE OR REPLACE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE)
|
jbe@164
|
171 RETURNS VOID
|
jbe@164
|
172 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@164
|
173 DECLARE
|
jbe@164
|
174 "issue_row" "issue"%ROWTYPE;
|
jbe@164
|
175 BEGIN
|
jbe@164
|
176 SELECT * INTO "issue_row"
|
jbe@164
|
177 FROM "issue" WHERE "id" = "issue_id_p"
|
jbe@164
|
178 FOR UPDATE;
|
jbe@164
|
179 IF "issue_row"."cleaned" ISNULL THEN
|
jbe@164
|
180 UPDATE "issue" SET
|
jbe@164
|
181 "state" = 'voting',
|
jbe@164
|
182 "closed" = NULL,
|
jbe@164
|
183 "ranks_available" = FALSE
|
jbe@164
|
184 WHERE "id" = "issue_id_p";
|
jbe@164
|
185 DELETE FROM "issue_comment"
|
jbe@164
|
186 WHERE "issue_id" = "issue_id_p";
|
jbe@164
|
187 DELETE FROM "voting_comment"
|
jbe@164
|
188 WHERE "issue_id" = "issue_id_p";
|
jbe@164
|
189 DELETE FROM "delegating_voter"
|
jbe@164
|
190 WHERE "issue_id" = "issue_id_p";
|
jbe@164
|
191 DELETE FROM "direct_voter"
|
jbe@164
|
192 WHERE "issue_id" = "issue_id_p";
|
jbe@164
|
193 DELETE FROM "delegating_interest_snapshot"
|
jbe@164
|
194 WHERE "issue_id" = "issue_id_p";
|
jbe@164
|
195 DELETE FROM "direct_interest_snapshot"
|
jbe@164
|
196 WHERE "issue_id" = "issue_id_p";
|
jbe@164
|
197 DELETE FROM "delegating_population_snapshot"
|
jbe@164
|
198 WHERE "issue_id" = "issue_id_p";
|
jbe@164
|
199 DELETE FROM "direct_population_snapshot"
|
jbe@164
|
200 WHERE "issue_id" = "issue_id_p";
|
jbe@164
|
201 DELETE FROM "non_voter"
|
jbe@164
|
202 WHERE "issue_id" = "issue_id_p";
|
jbe@164
|
203 DELETE FROM "delegation"
|
jbe@164
|
204 WHERE "issue_id" = "issue_id_p";
|
jbe@164
|
205 DELETE FROM "supporter"
|
jbe@164
|
206 WHERE "issue_id" = "issue_id_p";
|
jbe@164
|
207 UPDATE "issue" SET
|
jbe@164
|
208 "state" = "issue_row"."state",
|
jbe@164
|
209 "closed" = "issue_row"."closed",
|
jbe@164
|
210 "ranks_available" = "issue_row"."ranks_available",
|
jbe@164
|
211 "cleaned" = now()
|
jbe@164
|
212 WHERE "id" = "issue_id_p";
|
jbe@164
|
213 END IF;
|
jbe@164
|
214 RETURN;
|
jbe@164
|
215 END;
|
jbe@164
|
216 $$;
|
jbe@164
|
217
|
jbe@164
|
218 COMMIT;
|