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