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