| rev |
line source |
|
jbe@36
|
1 BEGIN;
|
|
jbe@36
|
2
|
|
jbe@36
|
3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
|
|
jbe@36
|
4 SELECT * FROM (VALUES ('beta23', NULL, NULL, NULL))
|
|
jbe@36
|
5 AS "subquery"("string", "major", "minor", "revision");
|
|
jbe@36
|
6
|
|
jbe@36
|
7 CREATE OR REPLACE FUNCTION "create_snapshot"
|
|
jbe@36
|
8 ( "issue_id_p" "issue"."id"%TYPE )
|
|
jbe@36
|
9 RETURNS VOID
|
|
jbe@36
|
10 LANGUAGE 'plpgsql' VOLATILE AS $$
|
|
jbe@36
|
11 DECLARE
|
|
jbe@36
|
12 "initiative_id_v" "initiative"."id"%TYPE;
|
|
jbe@36
|
13 "suggestion_id_v" "suggestion"."id"%TYPE;
|
|
jbe@36
|
14 BEGIN
|
|
jbe@36
|
15 PERFORM "global_lock"();
|
|
jbe@36
|
16 PERFORM "create_population_snapshot"("issue_id_p");
|
|
jbe@36
|
17 PERFORM "create_interest_snapshot"("issue_id_p");
|
|
jbe@36
|
18 UPDATE "issue" SET
|
|
jbe@36
|
19 "snapshot" = now(),
|
|
jbe@36
|
20 "latest_snapshot_event" = 'periodic',
|
|
jbe@36
|
21 "population" = (
|
|
jbe@36
|
22 SELECT coalesce(sum("weight"), 0)
|
|
jbe@36
|
23 FROM "direct_population_snapshot"
|
|
jbe@36
|
24 WHERE "issue_id" = "issue_id_p"
|
|
jbe@36
|
25 AND "event" = 'periodic'
|
|
jbe@36
|
26 ),
|
|
jbe@36
|
27 "vote_now" = (
|
|
jbe@36
|
28 SELECT coalesce(sum("weight"), 0)
|
|
jbe@36
|
29 FROM "direct_interest_snapshot"
|
|
jbe@36
|
30 WHERE "issue_id" = "issue_id_p"
|
|
jbe@36
|
31 AND "event" = 'periodic'
|
|
jbe@36
|
32 AND "voting_requested" = TRUE
|
|
jbe@36
|
33 ),
|
|
jbe@36
|
34 "vote_later" = (
|
|
jbe@36
|
35 SELECT coalesce(sum("weight"), 0)
|
|
jbe@36
|
36 FROM "direct_interest_snapshot"
|
|
jbe@36
|
37 WHERE "issue_id" = "issue_id_p"
|
|
jbe@36
|
38 AND "event" = 'periodic'
|
|
jbe@36
|
39 AND "voting_requested" = FALSE
|
|
jbe@36
|
40 )
|
|
jbe@36
|
41 WHERE "id" = "issue_id_p";
|
|
jbe@36
|
42 FOR "initiative_id_v" IN
|
|
jbe@36
|
43 SELECT "id" FROM "initiative" WHERE "issue_id" = "issue_id_p"
|
|
jbe@36
|
44 LOOP
|
|
jbe@36
|
45 UPDATE "initiative" SET
|
|
jbe@36
|
46 "supporter_count" = (
|
|
jbe@36
|
47 SELECT coalesce(sum("di"."weight"), 0)
|
|
jbe@36
|
48 FROM "direct_interest_snapshot" AS "di"
|
|
jbe@36
|
49 JOIN "direct_supporter_snapshot" AS "ds"
|
|
jbe@36
|
50 ON "di"."member_id" = "ds"."member_id"
|
|
jbe@36
|
51 WHERE "di"."issue_id" = "issue_id_p"
|
|
jbe@36
|
52 AND "di"."event" = 'periodic'
|
|
jbe@36
|
53 AND "ds"."initiative_id" = "initiative_id_v"
|
|
jbe@36
|
54 AND "ds"."event" = 'periodic'
|
|
jbe@36
|
55 ),
|
|
jbe@36
|
56 "informed_supporter_count" = (
|
|
jbe@36
|
57 SELECT coalesce(sum("di"."weight"), 0)
|
|
jbe@36
|
58 FROM "direct_interest_snapshot" AS "di"
|
|
jbe@36
|
59 JOIN "direct_supporter_snapshot" AS "ds"
|
|
jbe@36
|
60 ON "di"."member_id" = "ds"."member_id"
|
|
jbe@36
|
61 WHERE "di"."issue_id" = "issue_id_p"
|
|
jbe@36
|
62 AND "di"."event" = 'periodic'
|
|
jbe@36
|
63 AND "ds"."initiative_id" = "initiative_id_v"
|
|
jbe@36
|
64 AND "ds"."event" = 'periodic'
|
|
jbe@36
|
65 AND "ds"."informed"
|
|
jbe@36
|
66 ),
|
|
jbe@36
|
67 "satisfied_supporter_count" = (
|
|
jbe@36
|
68 SELECT coalesce(sum("di"."weight"), 0)
|
|
jbe@36
|
69 FROM "direct_interest_snapshot" AS "di"
|
|
jbe@36
|
70 JOIN "direct_supporter_snapshot" AS "ds"
|
|
jbe@36
|
71 ON "di"."member_id" = "ds"."member_id"
|
|
jbe@36
|
72 WHERE "di"."issue_id" = "issue_id_p"
|
|
jbe@36
|
73 AND "di"."event" = 'periodic'
|
|
jbe@36
|
74 AND "ds"."initiative_id" = "initiative_id_v"
|
|
jbe@36
|
75 AND "ds"."event" = 'periodic'
|
|
jbe@36
|
76 AND "ds"."satisfied"
|
|
jbe@36
|
77 ),
|
|
jbe@36
|
78 "satisfied_informed_supporter_count" = (
|
|
jbe@36
|
79 SELECT coalesce(sum("di"."weight"), 0)
|
|
jbe@36
|
80 FROM "direct_interest_snapshot" AS "di"
|
|
jbe@36
|
81 JOIN "direct_supporter_snapshot" AS "ds"
|
|
jbe@36
|
82 ON "di"."member_id" = "ds"."member_id"
|
|
jbe@36
|
83 WHERE "di"."issue_id" = "issue_id_p"
|
|
jbe@36
|
84 AND "di"."event" = 'periodic'
|
|
jbe@36
|
85 AND "ds"."initiative_id" = "initiative_id_v"
|
|
jbe@36
|
86 AND "ds"."event" = 'periodic'
|
|
jbe@36
|
87 AND "ds"."informed"
|
|
jbe@36
|
88 AND "ds"."satisfied"
|
|
jbe@36
|
89 )
|
|
jbe@36
|
90 WHERE "id" = "initiative_id_v";
|
|
jbe@36
|
91 FOR "suggestion_id_v" IN
|
|
jbe@36
|
92 SELECT "id" FROM "suggestion"
|
|
jbe@36
|
93 WHERE "initiative_id" = "initiative_id_v"
|
|
jbe@36
|
94 LOOP
|
|
jbe@36
|
95 UPDATE "suggestion" SET
|
|
jbe@36
|
96 "minus2_unfulfilled_count" = (
|
|
jbe@36
|
97 SELECT coalesce(sum("snapshot"."weight"), 0)
|
|
jbe@36
|
98 FROM "issue" CROSS JOIN "opinion"
|
|
jbe@36
|
99 JOIN "direct_interest_snapshot" AS "snapshot"
|
|
jbe@36
|
100 ON "snapshot"."issue_id" = "issue"."id"
|
|
jbe@36
|
101 AND "snapshot"."event" = "issue"."latest_snapshot_event"
|
|
jbe@36
|
102 AND "snapshot"."member_id" = "opinion"."member_id"
|
|
jbe@36
|
103 WHERE "issue"."id" = "issue_id_p"
|
|
jbe@36
|
104 AND "opinion"."suggestion_id" = "suggestion_id_v"
|
|
jbe@36
|
105 AND "opinion"."degree" = -2
|
|
jbe@36
|
106 AND "opinion"."fulfilled" = FALSE
|
|
jbe@36
|
107 ),
|
|
jbe@36
|
108 "minus2_fulfilled_count" = (
|
|
jbe@36
|
109 SELECT coalesce(sum("snapshot"."weight"), 0)
|
|
jbe@36
|
110 FROM "issue" CROSS JOIN "opinion"
|
|
jbe@36
|
111 JOIN "direct_interest_snapshot" AS "snapshot"
|
|
jbe@36
|
112 ON "snapshot"."issue_id" = "issue"."id"
|
|
jbe@36
|
113 AND "snapshot"."event" = "issue"."latest_snapshot_event"
|
|
jbe@36
|
114 AND "snapshot"."member_id" = "opinion"."member_id"
|
|
jbe@36
|
115 WHERE "issue"."id" = "issue_id_p"
|
|
jbe@36
|
116 AND "opinion"."suggestion_id" = "suggestion_id_v"
|
|
jbe@36
|
117 AND "opinion"."degree" = -2
|
|
jbe@36
|
118 AND "opinion"."fulfilled" = TRUE
|
|
jbe@36
|
119 ),
|
|
jbe@36
|
120 "minus1_unfulfilled_count" = (
|
|
jbe@36
|
121 SELECT coalesce(sum("snapshot"."weight"), 0)
|
|
jbe@36
|
122 FROM "issue" CROSS JOIN "opinion"
|
|
jbe@36
|
123 JOIN "direct_interest_snapshot" AS "snapshot"
|
|
jbe@36
|
124 ON "snapshot"."issue_id" = "issue"."id"
|
|
jbe@36
|
125 AND "snapshot"."event" = "issue"."latest_snapshot_event"
|
|
jbe@36
|
126 AND "snapshot"."member_id" = "opinion"."member_id"
|
|
jbe@36
|
127 WHERE "issue"."id" = "issue_id_p"
|
|
jbe@36
|
128 AND "opinion"."suggestion_id" = "suggestion_id_v"
|
|
jbe@36
|
129 AND "opinion"."degree" = -1
|
|
jbe@36
|
130 AND "opinion"."fulfilled" = FALSE
|
|
jbe@36
|
131 ),
|
|
jbe@36
|
132 "minus1_fulfilled_count" = (
|
|
jbe@36
|
133 SELECT coalesce(sum("snapshot"."weight"), 0)
|
|
jbe@36
|
134 FROM "issue" CROSS JOIN "opinion"
|
|
jbe@36
|
135 JOIN "direct_interest_snapshot" AS "snapshot"
|
|
jbe@36
|
136 ON "snapshot"."issue_id" = "issue"."id"
|
|
jbe@36
|
137 AND "snapshot"."event" = "issue"."latest_snapshot_event"
|
|
jbe@36
|
138 AND "snapshot"."member_id" = "opinion"."member_id"
|
|
jbe@36
|
139 WHERE "issue"."id" = "issue_id_p"
|
|
jbe@36
|
140 AND "opinion"."suggestion_id" = "suggestion_id_v"
|
|
jbe@36
|
141 AND "opinion"."degree" = -1
|
|
jbe@36
|
142 AND "opinion"."fulfilled" = TRUE
|
|
jbe@36
|
143 ),
|
|
jbe@36
|
144 "plus1_unfulfilled_count" = (
|
|
jbe@36
|
145 SELECT coalesce(sum("snapshot"."weight"), 0)
|
|
jbe@36
|
146 FROM "issue" CROSS JOIN "opinion"
|
|
jbe@36
|
147 JOIN "direct_interest_snapshot" AS "snapshot"
|
|
jbe@36
|
148 ON "snapshot"."issue_id" = "issue"."id"
|
|
jbe@36
|
149 AND "snapshot"."event" = "issue"."latest_snapshot_event"
|
|
jbe@36
|
150 AND "snapshot"."member_id" = "opinion"."member_id"
|
|
jbe@36
|
151 WHERE "issue"."id" = "issue_id_p"
|
|
jbe@36
|
152 AND "opinion"."suggestion_id" = "suggestion_id_v"
|
|
jbe@36
|
153 AND "opinion"."degree" = 1
|
|
jbe@36
|
154 AND "opinion"."fulfilled" = FALSE
|
|
jbe@36
|
155 ),
|
|
jbe@36
|
156 "plus1_fulfilled_count" = (
|
|
jbe@36
|
157 SELECT coalesce(sum("snapshot"."weight"), 0)
|
|
jbe@36
|
158 FROM "issue" CROSS JOIN "opinion"
|
|
jbe@36
|
159 JOIN "direct_interest_snapshot" AS "snapshot"
|
|
jbe@36
|
160 ON "snapshot"."issue_id" = "issue"."id"
|
|
jbe@36
|
161 AND "snapshot"."event" = "issue"."latest_snapshot_event"
|
|
jbe@36
|
162 AND "snapshot"."member_id" = "opinion"."member_id"
|
|
jbe@36
|
163 WHERE "issue"."id" = "issue_id_p"
|
|
jbe@36
|
164 AND "opinion"."suggestion_id" = "suggestion_id_v"
|
|
jbe@36
|
165 AND "opinion"."degree" = 1
|
|
jbe@36
|
166 AND "opinion"."fulfilled" = TRUE
|
|
jbe@36
|
167 ),
|
|
jbe@36
|
168 "plus2_unfulfilled_count" = (
|
|
jbe@36
|
169 SELECT coalesce(sum("snapshot"."weight"), 0)
|
|
jbe@36
|
170 FROM "issue" CROSS JOIN "opinion"
|
|
jbe@36
|
171 JOIN "direct_interest_snapshot" AS "snapshot"
|
|
jbe@36
|
172 ON "snapshot"."issue_id" = "issue"."id"
|
|
jbe@36
|
173 AND "snapshot"."event" = "issue"."latest_snapshot_event"
|
|
jbe@36
|
174 AND "snapshot"."member_id" = "opinion"."member_id"
|
|
jbe@36
|
175 WHERE "issue"."id" = "issue_id_p"
|
|
jbe@36
|
176 AND "opinion"."suggestion_id" = "suggestion_id_v"
|
|
jbe@36
|
177 AND "opinion"."degree" = 2
|
|
jbe@36
|
178 AND "opinion"."fulfilled" = FALSE
|
|
jbe@36
|
179 ),
|
|
jbe@36
|
180 "plus2_fulfilled_count" = (
|
|
jbe@36
|
181 SELECT coalesce(sum("snapshot"."weight"), 0)
|
|
jbe@36
|
182 FROM "issue" CROSS JOIN "opinion"
|
|
jbe@36
|
183 JOIN "direct_interest_snapshot" AS "snapshot"
|
|
jbe@36
|
184 ON "snapshot"."issue_id" = "issue"."id"
|
|
jbe@36
|
185 AND "snapshot"."event" = "issue"."latest_snapshot_event"
|
|
jbe@36
|
186 AND "snapshot"."member_id" = "opinion"."member_id"
|
|
jbe@36
|
187 WHERE "issue"."id" = "issue_id_p"
|
|
jbe@36
|
188 AND "opinion"."suggestion_id" = "suggestion_id_v"
|
|
jbe@36
|
189 AND "opinion"."degree" = 2
|
|
jbe@36
|
190 AND "opinion"."fulfilled" = TRUE
|
|
jbe@36
|
191 )
|
|
jbe@36
|
192 WHERE "suggestion"."id" = "suggestion_id_v";
|
|
jbe@36
|
193 END LOOP;
|
|
jbe@36
|
194 END LOOP;
|
|
jbe@36
|
195 RETURN;
|
|
jbe@36
|
196 END;
|
|
jbe@36
|
197 $$;
|
|
jbe@36
|
198
|
|
jbe@36
|
199 COMMIT;
|