liquid_feedback_core
view update/core-update.v1.4.0_rc3-v1.4.0_rc4.sql @ 214:919d16c8e8e3
Added new column "authentication to table "member"; Added update script to v2.0.2
| author | jbe |
|---|---|
| date | Fri Feb 17 18:26:37 2012 +0100 (2012-02-17) |
| parents | 1be788313b84 |
| children |
line source
1 BEGIN;
3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
4 SELECT * FROM (VALUES ('1.4.0_rc4', 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"
101 WHERE "issue_id" = "issue_id_p" AND "admitted";
102 END LOOP;
103 PERFORM "add_vote_delegations"("issue_id_p");
104 UPDATE "issue" SET
105 "state" = 'calculation',
106 "closed" = now(),
107 "voter_count" = (
108 SELECT coalesce(sum("weight"), 0)
109 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
110 )
111 WHERE "id" = "issue_id_p";
112 UPDATE "initiative" SET
113 "positive_votes" = "vote_counts"."positive_votes",
114 "negative_votes" = "vote_counts"."negative_votes",
115 "agreed" = CASE WHEN "majority_strict" THEN
116 "vote_counts"."positive_votes" * "majority_den" >
117 "majority_num" *
118 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
119 ELSE
120 "vote_counts"."positive_votes" * "majority_den" >=
121 "majority_num" *
122 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
123 END
124 FROM
125 ( SELECT
126 "initiative"."id" AS "initiative_id",
127 coalesce(
128 sum(
129 CASE WHEN "grade" > 0 THEN "direct_voter"."weight" ELSE 0 END
130 ),
131 0
132 ) AS "positive_votes",
133 coalesce(
134 sum(
135 CASE WHEN "grade" < 0 THEN "direct_voter"."weight" ELSE 0 END
136 ),
137 0
138 ) AS "negative_votes"
139 FROM "initiative"
140 JOIN "issue" ON "initiative"."issue_id" = "issue"."id"
141 JOIN "policy" ON "issue"."policy_id" = "policy"."id"
142 LEFT JOIN "direct_voter"
143 ON "direct_voter"."issue_id" = "initiative"."issue_id"
144 LEFT JOIN "vote"
145 ON "vote"."initiative_id" = "initiative"."id"
146 AND "vote"."member_id" = "direct_voter"."member_id"
147 WHERE "initiative"."issue_id" = "issue_id_p"
148 AND "initiative"."admitted" -- NOTE: NULL case is handled too
149 GROUP BY "initiative"."id"
150 ) AS "vote_counts",
151 "issue",
152 "policy"
153 WHERE "vote_counts"."initiative_id" = "initiative"."id"
154 AND "issue"."id" = "initiative"."issue_id"
155 AND "policy"."id" = "issue"."policy_id";
156 -- NOTE: "closed" column of issue must be set at this point
157 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
158 INSERT INTO "battle" (
159 "issue_id",
160 "winning_initiative_id", "losing_initiative_id",
161 "count"
162 ) SELECT
163 "issue_id",
164 "winning_initiative_id", "losing_initiative_id",
165 "count"
166 FROM "battle_view" WHERE "issue_id" = "issue_id_p";
167 END;
168 $$;
170 CREATE OR REPLACE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE)
171 RETURNS VOID
172 LANGUAGE 'plpgsql' VOLATILE AS $$
173 DECLARE
174 "issue_row" "issue"%ROWTYPE;
175 BEGIN
176 SELECT * INTO "issue_row"
177 FROM "issue" WHERE "id" = "issue_id_p"
178 FOR UPDATE;
179 IF "issue_row"."cleaned" ISNULL THEN
180 UPDATE "issue" SET
181 "state" = 'voting',
182 "closed" = NULL,
183 "ranks_available" = FALSE
184 WHERE "id" = "issue_id_p";
185 DELETE FROM "issue_comment"
186 WHERE "issue_id" = "issue_id_p";
187 DELETE FROM "voting_comment"
188 WHERE "issue_id" = "issue_id_p";
189 DELETE FROM "delegating_voter"
190 WHERE "issue_id" = "issue_id_p";
191 DELETE FROM "direct_voter"
192 WHERE "issue_id" = "issue_id_p";
193 DELETE FROM "delegating_interest_snapshot"
194 WHERE "issue_id" = "issue_id_p";
195 DELETE FROM "direct_interest_snapshot"
196 WHERE "issue_id" = "issue_id_p";
197 DELETE FROM "delegating_population_snapshot"
198 WHERE "issue_id" = "issue_id_p";
199 DELETE FROM "direct_population_snapshot"
200 WHERE "issue_id" = "issue_id_p";
201 DELETE FROM "non_voter"
202 WHERE "issue_id" = "issue_id_p";
203 DELETE FROM "delegation"
204 WHERE "issue_id" = "issue_id_p";
205 DELETE FROM "supporter"
206 WHERE "issue_id" = "issue_id_p";
207 UPDATE "issue" SET
208 "state" = "issue_row"."state",
209 "closed" = "issue_row"."closed",
210 "ranks_available" = "issue_row"."ranks_available",
211 "cleaned" = now()
212 WHERE "id" = "issue_id_p";
213 END IF;
214 RETURN;
215 END;
216 $$;
218 COMMIT;