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