liquid_feedback_core
view update/core-update.v1.4.0_rc1-v1.4.0_rc2.sql @ 388:d990f212ee55
Added tag v2.2.4 for changeset ae69cf82c05f
| author | jbe |
|---|---|
| date | Sun Jul 14 00:42:11 2013 +0200 (2013-07-14) |
| parents | 07cfc7c1ce3f |
| children |
line source
1 BEGIN;
3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
4 SELECT * FROM (VALUES ('1.4.0_rc2', 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 LEFT JOIN "direct_voter"
49 ON "interest"."member_id" = "direct_voter"."member_id"
50 AND "interest"."issue_id" = "direct_voter"."issue_id"
51 LEFT JOIN "delegating_voter"
52 ON "interest"."member_id" = "delegating_voter"."member_id"
53 AND "interest"."issue_id" = "delegating_voter"."issue_id"
54 WHERE "interest"."issue_id" = "issue_id_p"
55 AND "interest"."autoreject" = TRUE
56 AND "member"."active"
57 AND "direct_voter"."member_id" ISNULL
58 AND "delegating_voter"."member_id" ISNULL
59 UNION SELECT "membership"."member_id"
60 FROM "membership"
61 JOIN "member"
62 ON "membership"."member_id" = "member"."id"
63 LEFT JOIN "interest"
64 ON "membership"."member_id" = "interest"."member_id"
65 AND "interest"."issue_id" = "issue_id_p"
66 LEFT JOIN "direct_voter"
67 ON "membership"."member_id" = "direct_voter"."member_id"
68 AND "direct_voter"."issue_id" = "issue_id_p"
69 LEFT JOIN "delegating_voter"
70 ON "membership"."member_id" = "delegating_voter"."member_id"
71 AND "delegating_voter"."issue_id" = "issue_id_p"
72 WHERE "membership"."area_id" = "area_id_v"
73 AND "membership"."autoreject" = TRUE
74 AND "member"."active"
75 AND "interest"."autoreject" ISNULL
76 AND "direct_voter"."member_id" ISNULL
77 AND "delegating_voter"."member_id" ISNULL
78 LOOP
79 INSERT INTO "direct_voter"
80 ("member_id", "issue_id", "weight", "autoreject") VALUES
81 ("member_id_v", "issue_id_p", 1, TRUE);
82 INSERT INTO "vote" (
83 "member_id",
84 "issue_id",
85 "initiative_id",
86 "grade"
87 ) SELECT
88 "member_id_v" AS "member_id",
89 "issue_id_p" AS "issue_id",
90 "id" AS "initiative_id",
91 -1 AS "grade"
92 FROM "initiative" WHERE "issue_id" = "issue_id_p";
93 END LOOP;
94 PERFORM "add_vote_delegations"("issue_id_p");
95 UPDATE "issue" SET
96 "state" = 'calculation',
97 "closed" = now(),
98 "voter_count" = (
99 SELECT coalesce(sum("weight"), 0)
100 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
101 )
102 WHERE "id" = "issue_id_p";
103 UPDATE "initiative" SET
104 "positive_votes" = "vote_counts"."positive_votes",
105 "negative_votes" = "vote_counts"."negative_votes",
106 "agreed" = CASE WHEN "majority_strict" THEN
107 "vote_counts"."positive_votes" * "majority_den" >
108 "majority_num" *
109 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
110 ELSE
111 "vote_counts"."positive_votes" * "majority_den" >=
112 "majority_num" *
113 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
114 END
115 FROM
116 ( SELECT
117 "initiative"."id" AS "initiative_id",
118 coalesce(
119 sum(
120 CASE WHEN "grade" > 0 THEN "direct_voter"."weight" ELSE 0 END
121 ),
122 0
123 ) AS "positive_votes",
124 coalesce(
125 sum(
126 CASE WHEN "grade" < 0 THEN "direct_voter"."weight" ELSE 0 END
127 ),
128 0
129 ) AS "negative_votes"
130 FROM "initiative"
131 JOIN "issue" ON "initiative"."issue_id" = "issue"."id"
132 JOIN "policy" ON "issue"."policy_id" = "policy"."id"
133 LEFT JOIN "direct_voter"
134 ON "direct_voter"."issue_id" = "initiative"."issue_id"
135 LEFT JOIN "vote"
136 ON "vote"."initiative_id" = "initiative"."id"
137 AND "vote"."member_id" = "direct_voter"."member_id"
138 WHERE "initiative"."issue_id" = "issue_id_p"
139 AND "initiative"."admitted" -- NOTE: NULL case is handled too
140 GROUP BY "initiative"."id"
141 ) AS "vote_counts",
142 "issue",
143 "policy"
144 WHERE "vote_counts"."initiative_id" = "initiative"."id"
145 AND "issue"."id" = "initiative"."issue_id"
146 AND "policy"."id" = "issue"."policy_id";
147 -- NOTE: "closed" column of issue must be set at this point
148 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
149 INSERT INTO "battle" (
150 "issue_id",
151 "winning_initiative_id", "losing_initiative_id",
152 "count"
153 ) SELECT
154 "issue_id",
155 "winning_initiative_id", "losing_initiative_id",
156 "count"
157 FROM "battle_view" WHERE "issue_id" = "issue_id_p";
158 END;
159 $$;
161 COMMIT;