liquid_feedback_core
changeset 36:c3b72b644cc8 beta23
Critical bugfix in function "create_snapshot": Fixed incorrect calculation of opinion count for suggestions
author | jbe |
---|---|
date | Thu Mar 04 00:42:47 2010 +0100 (2010-03-04) |
parents | 07dca93454d6 |
children | 28dde4909a35 |
files | core.sql update/core-update.beta22-beta23.sql |
line diff
1.1 --- a/core.sql Sun Feb 21 16:53:05 2010 +0100 1.2 +++ b/core.sql Thu Mar 04 00:42:47 2010 +0100 1.3 @@ -6,7 +6,7 @@ 1.4 BEGIN; 1.5 1.6 CREATE VIEW "liquid_feedback_version" AS 1.7 - SELECT * FROM (VALUES ('beta22', NULL, NULL, NULL)) 1.8 + SELECT * FROM (VALUES ('beta23', NULL, NULL, NULL)) 1.9 AS "subquery"("string", "major", "minor", "revision"); 1.10 1.11 1.12 @@ -2329,73 +2329,97 @@ 1.13 UPDATE "suggestion" SET 1.14 "minus2_unfulfilled_count" = ( 1.15 SELECT coalesce(sum("snapshot"."weight"), 0) 1.16 - FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot" 1.17 - ON "opinion"."member_id" = "snapshot"."member_id" 1.18 - WHERE "opinion"."suggestion_id" = "suggestion_id_v" 1.19 - AND "snapshot"."issue_id" = "issue_id_p" 1.20 + FROM "issue" CROSS JOIN "opinion" 1.21 + JOIN "direct_interest_snapshot" AS "snapshot" 1.22 + ON "snapshot"."issue_id" = "issue"."id" 1.23 + AND "snapshot"."event" = "issue"."latest_snapshot_event" 1.24 + AND "snapshot"."member_id" = "opinion"."member_id" 1.25 + WHERE "issue"."id" = "issue_id_p" 1.26 + AND "opinion"."suggestion_id" = "suggestion_id_v" 1.27 AND "opinion"."degree" = -2 1.28 AND "opinion"."fulfilled" = FALSE 1.29 ), 1.30 "minus2_fulfilled_count" = ( 1.31 SELECT coalesce(sum("snapshot"."weight"), 0) 1.32 - FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot" 1.33 - ON "opinion"."member_id" = "snapshot"."member_id" 1.34 - WHERE "opinion"."suggestion_id" = "suggestion_id_v" 1.35 - AND "snapshot"."issue_id" = "issue_id_p" 1.36 + FROM "issue" CROSS JOIN "opinion" 1.37 + JOIN "direct_interest_snapshot" AS "snapshot" 1.38 + ON "snapshot"."issue_id" = "issue"."id" 1.39 + AND "snapshot"."event" = "issue"."latest_snapshot_event" 1.40 + AND "snapshot"."member_id" = "opinion"."member_id" 1.41 + WHERE "issue"."id" = "issue_id_p" 1.42 + AND "opinion"."suggestion_id" = "suggestion_id_v" 1.43 AND "opinion"."degree" = -2 1.44 AND "opinion"."fulfilled" = TRUE 1.45 ), 1.46 "minus1_unfulfilled_count" = ( 1.47 SELECT coalesce(sum("snapshot"."weight"), 0) 1.48 - FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot" 1.49 - ON "opinion"."member_id" = "snapshot"."member_id" 1.50 - WHERE "opinion"."suggestion_id" = "suggestion_id_v" 1.51 - AND "snapshot"."issue_id" = "issue_id_p" 1.52 + FROM "issue" CROSS JOIN "opinion" 1.53 + JOIN "direct_interest_snapshot" AS "snapshot" 1.54 + ON "snapshot"."issue_id" = "issue"."id" 1.55 + AND "snapshot"."event" = "issue"."latest_snapshot_event" 1.56 + AND "snapshot"."member_id" = "opinion"."member_id" 1.57 + WHERE "issue"."id" = "issue_id_p" 1.58 + AND "opinion"."suggestion_id" = "suggestion_id_v" 1.59 AND "opinion"."degree" = -1 1.60 AND "opinion"."fulfilled" = FALSE 1.61 ), 1.62 "minus1_fulfilled_count" = ( 1.63 SELECT coalesce(sum("snapshot"."weight"), 0) 1.64 - FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot" 1.65 - ON "opinion"."member_id" = "snapshot"."member_id" 1.66 - WHERE "opinion"."suggestion_id" = "suggestion_id_v" 1.67 - AND "snapshot"."issue_id" = "issue_id_p" 1.68 + FROM "issue" CROSS JOIN "opinion" 1.69 + JOIN "direct_interest_snapshot" AS "snapshot" 1.70 + ON "snapshot"."issue_id" = "issue"."id" 1.71 + AND "snapshot"."event" = "issue"."latest_snapshot_event" 1.72 + AND "snapshot"."member_id" = "opinion"."member_id" 1.73 + WHERE "issue"."id" = "issue_id_p" 1.74 + AND "opinion"."suggestion_id" = "suggestion_id_v" 1.75 AND "opinion"."degree" = -1 1.76 AND "opinion"."fulfilled" = TRUE 1.77 ), 1.78 "plus1_unfulfilled_count" = ( 1.79 SELECT coalesce(sum("snapshot"."weight"), 0) 1.80 - FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot" 1.81 - ON "opinion"."member_id" = "snapshot"."member_id" 1.82 - WHERE "opinion"."suggestion_id" = "suggestion_id_v" 1.83 - AND "snapshot"."issue_id" = "issue_id_p" 1.84 + FROM "issue" CROSS JOIN "opinion" 1.85 + JOIN "direct_interest_snapshot" AS "snapshot" 1.86 + ON "snapshot"."issue_id" = "issue"."id" 1.87 + AND "snapshot"."event" = "issue"."latest_snapshot_event" 1.88 + AND "snapshot"."member_id" = "opinion"."member_id" 1.89 + WHERE "issue"."id" = "issue_id_p" 1.90 + AND "opinion"."suggestion_id" = "suggestion_id_v" 1.91 AND "opinion"."degree" = 1 1.92 AND "opinion"."fulfilled" = FALSE 1.93 ), 1.94 "plus1_fulfilled_count" = ( 1.95 SELECT coalesce(sum("snapshot"."weight"), 0) 1.96 - FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot" 1.97 - ON "opinion"."member_id" = "snapshot"."member_id" 1.98 - WHERE "opinion"."suggestion_id" = "suggestion_id_v" 1.99 - AND "snapshot"."issue_id" = "issue_id_p" 1.100 + FROM "issue" CROSS JOIN "opinion" 1.101 + JOIN "direct_interest_snapshot" AS "snapshot" 1.102 + ON "snapshot"."issue_id" = "issue"."id" 1.103 + AND "snapshot"."event" = "issue"."latest_snapshot_event" 1.104 + AND "snapshot"."member_id" = "opinion"."member_id" 1.105 + WHERE "issue"."id" = "issue_id_p" 1.106 + AND "opinion"."suggestion_id" = "suggestion_id_v" 1.107 AND "opinion"."degree" = 1 1.108 AND "opinion"."fulfilled" = TRUE 1.109 ), 1.110 "plus2_unfulfilled_count" = ( 1.111 SELECT coalesce(sum("snapshot"."weight"), 0) 1.112 - FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot" 1.113 - ON "opinion"."member_id" = "snapshot"."member_id" 1.114 - WHERE "opinion"."suggestion_id" = "suggestion_id_v" 1.115 - AND "snapshot"."issue_id" = "issue_id_p" 1.116 + FROM "issue" CROSS JOIN "opinion" 1.117 + JOIN "direct_interest_snapshot" AS "snapshot" 1.118 + ON "snapshot"."issue_id" = "issue"."id" 1.119 + AND "snapshot"."event" = "issue"."latest_snapshot_event" 1.120 + AND "snapshot"."member_id" = "opinion"."member_id" 1.121 + WHERE "issue"."id" = "issue_id_p" 1.122 + AND "opinion"."suggestion_id" = "suggestion_id_v" 1.123 AND "opinion"."degree" = 2 1.124 AND "opinion"."fulfilled" = FALSE 1.125 ), 1.126 "plus2_fulfilled_count" = ( 1.127 SELECT coalesce(sum("snapshot"."weight"), 0) 1.128 - FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot" 1.129 - ON "opinion"."member_id" = "snapshot"."member_id" 1.130 - WHERE "opinion"."suggestion_id" = "suggestion_id_v" 1.131 - AND "snapshot"."issue_id" = "issue_id_p" 1.132 + FROM "issue" CROSS JOIN "opinion" 1.133 + JOIN "direct_interest_snapshot" AS "snapshot" 1.134 + ON "snapshot"."issue_id" = "issue"."id" 1.135 + AND "snapshot"."event" = "issue"."latest_snapshot_event" 1.136 + AND "snapshot"."member_id" = "opinion"."member_id" 1.137 + WHERE "issue"."id" = "issue_id_p" 1.138 + AND "opinion"."suggestion_id" = "suggestion_id_v" 1.139 AND "opinion"."degree" = 2 1.140 AND "opinion"."fulfilled" = TRUE 1.141 )
2.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000 2.2 +++ b/update/core-update.beta22-beta23.sql Thu Mar 04 00:42:47 2010 +0100 2.3 @@ -0,0 +1,199 @@ 2.4 +BEGIN; 2.5 + 2.6 +CREATE OR REPLACE VIEW "liquid_feedback_version" AS 2.7 + SELECT * FROM (VALUES ('beta23', NULL, NULL, NULL)) 2.8 + AS "subquery"("string", "major", "minor", "revision"); 2.9 + 2.10 +CREATE OR REPLACE FUNCTION "create_snapshot" 2.11 + ( "issue_id_p" "issue"."id"%TYPE ) 2.12 + RETURNS VOID 2.13 + LANGUAGE 'plpgsql' VOLATILE AS $$ 2.14 + DECLARE 2.15 + "initiative_id_v" "initiative"."id"%TYPE; 2.16 + "suggestion_id_v" "suggestion"."id"%TYPE; 2.17 + BEGIN 2.18 + PERFORM "global_lock"(); 2.19 + PERFORM "create_population_snapshot"("issue_id_p"); 2.20 + PERFORM "create_interest_snapshot"("issue_id_p"); 2.21 + UPDATE "issue" SET 2.22 + "snapshot" = now(), 2.23 + "latest_snapshot_event" = 'periodic', 2.24 + "population" = ( 2.25 + SELECT coalesce(sum("weight"), 0) 2.26 + FROM "direct_population_snapshot" 2.27 + WHERE "issue_id" = "issue_id_p" 2.28 + AND "event" = 'periodic' 2.29 + ), 2.30 + "vote_now" = ( 2.31 + SELECT coalesce(sum("weight"), 0) 2.32 + FROM "direct_interest_snapshot" 2.33 + WHERE "issue_id" = "issue_id_p" 2.34 + AND "event" = 'periodic' 2.35 + AND "voting_requested" = TRUE 2.36 + ), 2.37 + "vote_later" = ( 2.38 + SELECT coalesce(sum("weight"), 0) 2.39 + FROM "direct_interest_snapshot" 2.40 + WHERE "issue_id" = "issue_id_p" 2.41 + AND "event" = 'periodic' 2.42 + AND "voting_requested" = FALSE 2.43 + ) 2.44 + WHERE "id" = "issue_id_p"; 2.45 + FOR "initiative_id_v" IN 2.46 + SELECT "id" FROM "initiative" WHERE "issue_id" = "issue_id_p" 2.47 + LOOP 2.48 + UPDATE "initiative" SET 2.49 + "supporter_count" = ( 2.50 + SELECT coalesce(sum("di"."weight"), 0) 2.51 + FROM "direct_interest_snapshot" AS "di" 2.52 + JOIN "direct_supporter_snapshot" AS "ds" 2.53 + ON "di"."member_id" = "ds"."member_id" 2.54 + WHERE "di"."issue_id" = "issue_id_p" 2.55 + AND "di"."event" = 'periodic' 2.56 + AND "ds"."initiative_id" = "initiative_id_v" 2.57 + AND "ds"."event" = 'periodic' 2.58 + ), 2.59 + "informed_supporter_count" = ( 2.60 + SELECT coalesce(sum("di"."weight"), 0) 2.61 + FROM "direct_interest_snapshot" AS "di" 2.62 + JOIN "direct_supporter_snapshot" AS "ds" 2.63 + ON "di"."member_id" = "ds"."member_id" 2.64 + WHERE "di"."issue_id" = "issue_id_p" 2.65 + AND "di"."event" = 'periodic' 2.66 + AND "ds"."initiative_id" = "initiative_id_v" 2.67 + AND "ds"."event" = 'periodic' 2.68 + AND "ds"."informed" 2.69 + ), 2.70 + "satisfied_supporter_count" = ( 2.71 + SELECT coalesce(sum("di"."weight"), 0) 2.72 + FROM "direct_interest_snapshot" AS "di" 2.73 + JOIN "direct_supporter_snapshot" AS "ds" 2.74 + ON "di"."member_id" = "ds"."member_id" 2.75 + WHERE "di"."issue_id" = "issue_id_p" 2.76 + AND "di"."event" = 'periodic' 2.77 + AND "ds"."initiative_id" = "initiative_id_v" 2.78 + AND "ds"."event" = 'periodic' 2.79 + AND "ds"."satisfied" 2.80 + ), 2.81 + "satisfied_informed_supporter_count" = ( 2.82 + SELECT coalesce(sum("di"."weight"), 0) 2.83 + FROM "direct_interest_snapshot" AS "di" 2.84 + JOIN "direct_supporter_snapshot" AS "ds" 2.85 + ON "di"."member_id" = "ds"."member_id" 2.86 + WHERE "di"."issue_id" = "issue_id_p" 2.87 + AND "di"."event" = 'periodic' 2.88 + AND "ds"."initiative_id" = "initiative_id_v" 2.89 + AND "ds"."event" = 'periodic' 2.90 + AND "ds"."informed" 2.91 + AND "ds"."satisfied" 2.92 + ) 2.93 + WHERE "id" = "initiative_id_v"; 2.94 + FOR "suggestion_id_v" IN 2.95 + SELECT "id" FROM "suggestion" 2.96 + WHERE "initiative_id" = "initiative_id_v" 2.97 + LOOP 2.98 + UPDATE "suggestion" SET 2.99 + "minus2_unfulfilled_count" = ( 2.100 + SELECT coalesce(sum("snapshot"."weight"), 0) 2.101 + FROM "issue" CROSS JOIN "opinion" 2.102 + JOIN "direct_interest_snapshot" AS "snapshot" 2.103 + ON "snapshot"."issue_id" = "issue"."id" 2.104 + AND "snapshot"."event" = "issue"."latest_snapshot_event" 2.105 + AND "snapshot"."member_id" = "opinion"."member_id" 2.106 + WHERE "issue"."id" = "issue_id_p" 2.107 + AND "opinion"."suggestion_id" = "suggestion_id_v" 2.108 + AND "opinion"."degree" = -2 2.109 + AND "opinion"."fulfilled" = FALSE 2.110 + ), 2.111 + "minus2_fulfilled_count" = ( 2.112 + SELECT coalesce(sum("snapshot"."weight"), 0) 2.113 + FROM "issue" CROSS JOIN "opinion" 2.114 + JOIN "direct_interest_snapshot" AS "snapshot" 2.115 + ON "snapshot"."issue_id" = "issue"."id" 2.116 + AND "snapshot"."event" = "issue"."latest_snapshot_event" 2.117 + AND "snapshot"."member_id" = "opinion"."member_id" 2.118 + WHERE "issue"."id" = "issue_id_p" 2.119 + AND "opinion"."suggestion_id" = "suggestion_id_v" 2.120 + AND "opinion"."degree" = -2 2.121 + AND "opinion"."fulfilled" = TRUE 2.122 + ), 2.123 + "minus1_unfulfilled_count" = ( 2.124 + SELECT coalesce(sum("snapshot"."weight"), 0) 2.125 + FROM "issue" CROSS JOIN "opinion" 2.126 + JOIN "direct_interest_snapshot" AS "snapshot" 2.127 + ON "snapshot"."issue_id" = "issue"."id" 2.128 + AND "snapshot"."event" = "issue"."latest_snapshot_event" 2.129 + AND "snapshot"."member_id" = "opinion"."member_id" 2.130 + WHERE "issue"."id" = "issue_id_p" 2.131 + AND "opinion"."suggestion_id" = "suggestion_id_v" 2.132 + AND "opinion"."degree" = -1 2.133 + AND "opinion"."fulfilled" = FALSE 2.134 + ), 2.135 + "minus1_fulfilled_count" = ( 2.136 + SELECT coalesce(sum("snapshot"."weight"), 0) 2.137 + FROM "issue" CROSS JOIN "opinion" 2.138 + JOIN "direct_interest_snapshot" AS "snapshot" 2.139 + ON "snapshot"."issue_id" = "issue"."id" 2.140 + AND "snapshot"."event" = "issue"."latest_snapshot_event" 2.141 + AND "snapshot"."member_id" = "opinion"."member_id" 2.142 + WHERE "issue"."id" = "issue_id_p" 2.143 + AND "opinion"."suggestion_id" = "suggestion_id_v" 2.144 + AND "opinion"."degree" = -1 2.145 + AND "opinion"."fulfilled" = TRUE 2.146 + ), 2.147 + "plus1_unfulfilled_count" = ( 2.148 + SELECT coalesce(sum("snapshot"."weight"), 0) 2.149 + FROM "issue" CROSS JOIN "opinion" 2.150 + JOIN "direct_interest_snapshot" AS "snapshot" 2.151 + ON "snapshot"."issue_id" = "issue"."id" 2.152 + AND "snapshot"."event" = "issue"."latest_snapshot_event" 2.153 + AND "snapshot"."member_id" = "opinion"."member_id" 2.154 + WHERE "issue"."id" = "issue_id_p" 2.155 + AND "opinion"."suggestion_id" = "suggestion_id_v" 2.156 + AND "opinion"."degree" = 1 2.157 + AND "opinion"."fulfilled" = FALSE 2.158 + ), 2.159 + "plus1_fulfilled_count" = ( 2.160 + SELECT coalesce(sum("snapshot"."weight"), 0) 2.161 + FROM "issue" CROSS JOIN "opinion" 2.162 + JOIN "direct_interest_snapshot" AS "snapshot" 2.163 + ON "snapshot"."issue_id" = "issue"."id" 2.164 + AND "snapshot"."event" = "issue"."latest_snapshot_event" 2.165 + AND "snapshot"."member_id" = "opinion"."member_id" 2.166 + WHERE "issue"."id" = "issue_id_p" 2.167 + AND "opinion"."suggestion_id" = "suggestion_id_v" 2.168 + AND "opinion"."degree" = 1 2.169 + AND "opinion"."fulfilled" = TRUE 2.170 + ), 2.171 + "plus2_unfulfilled_count" = ( 2.172 + SELECT coalesce(sum("snapshot"."weight"), 0) 2.173 + FROM "issue" CROSS JOIN "opinion" 2.174 + JOIN "direct_interest_snapshot" AS "snapshot" 2.175 + ON "snapshot"."issue_id" = "issue"."id" 2.176 + AND "snapshot"."event" = "issue"."latest_snapshot_event" 2.177 + AND "snapshot"."member_id" = "opinion"."member_id" 2.178 + WHERE "issue"."id" = "issue_id_p" 2.179 + AND "opinion"."suggestion_id" = "suggestion_id_v" 2.180 + AND "opinion"."degree" = 2 2.181 + AND "opinion"."fulfilled" = FALSE 2.182 + ), 2.183 + "plus2_fulfilled_count" = ( 2.184 + SELECT coalesce(sum("snapshot"."weight"), 0) 2.185 + FROM "issue" CROSS JOIN "opinion" 2.186 + JOIN "direct_interest_snapshot" AS "snapshot" 2.187 + ON "snapshot"."issue_id" = "issue"."id" 2.188 + AND "snapshot"."event" = "issue"."latest_snapshot_event" 2.189 + AND "snapshot"."member_id" = "opinion"."member_id" 2.190 + WHERE "issue"."id" = "issue_id_p" 2.191 + AND "opinion"."suggestion_id" = "suggestion_id_v" 2.192 + AND "opinion"."degree" = 2 2.193 + AND "opinion"."fulfilled" = TRUE 2.194 + ) 2.195 + WHERE "suggestion"."id" = "suggestion_id_v"; 2.196 + END LOOP; 2.197 + END LOOP; 2.198 + RETURN; 2.199 + END; 2.200 + $$; 2.201 + 2.202 +COMMIT;