liquid_feedback_core
changeset 322:fd58f487e1d0
Calculate "harmonc_weight" after all other calculations in an issue (as calculation depends on the "admitted" flag now)
author | jbe |
---|---|
date | Fri Feb 08 19:20:17 2013 +0100 (2013-02-08) |
parents | 48a5036d5eb1 |
children | 4c7a864829b0 |
files | core.sql update/core-update.v2.1.0-v2.1.1.sql |
line diff
1.1 --- a/core.sql Fri Feb 08 18:36:22 2013 +0100 1.2 +++ b/core.sql Fri Feb 08 19:20:17 2013 +0100 1.3 @@ -3773,9 +3773,7 @@ 1.4 ) 1.5 WHERE "suggestion"."id" = "suggestion_id_v"; 1.6 END LOOP; 1.7 - PERFORM "set_harmonic_suggestion_weights"("initiative_id_v"); 1.8 END LOOP; 1.9 - PERFORM "set_harmonic_initiative_weights"("issue_id_p"); 1.10 RETURN; 1.11 END; 1.12 $$; 1.13 @@ -3896,6 +3894,9 @@ 1.14 BEGIN 1.15 PERFORM "create_snapshot"("issue_id_p"); 1.16 PERFORM "freeze_after_snapshot"("issue_id_p"); 1.17 + PERFORM "set_harmonic_initiative_weights"("issue_id_p"); 1.18 + PERFORM "set_harmonic_suggestion_weights"("id") 1.19 + FROM "initiative" WHERE "issue_id" = "issue_id_p"; 1.20 RETURN; 1.21 END; 1.22 $$; 1.23 @@ -4420,8 +4421,9 @@ 1.24 RETURNS VOID 1.25 LANGUAGE 'plpgsql' VOLATILE AS $$ 1.26 DECLARE 1.27 - "issue_row" "issue"%ROWTYPE; 1.28 - "policy_row" "policy"%ROWTYPE; 1.29 + "issue_row" "issue"%ROWTYPE; 1.30 + "policy_row" "policy"%ROWTYPE; 1.31 + "new_snapshot_v" BOOLEAN; 1.32 BEGIN 1.33 PERFORM "lock_issue"("issue_id_p"); 1.34 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"; 1.35 @@ -4432,7 +4434,10 @@ 1.36 -- create a snapshot, unless issue is already fully frozen: 1.37 IF "issue_row"."fully_frozen" ISNULL THEN 1.38 PERFORM "create_snapshot"("issue_id_p"); 1.39 + "new_snapshot_v" := TRUE; 1.40 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"; 1.41 + ELSE 1.42 + "new_snapshot_v" := FALSE; 1.43 END IF; 1.44 -- eventually close or accept issues, which have not been accepted: 1.45 IF "issue_row"."accepted" ISNULL THEN 1.46 @@ -4540,6 +4545,12 @@ 1.47 -- calculate ranks will not consume much time and can be done now 1.48 PERFORM "calculate_ranks"("issue_id_p"); 1.49 END IF; 1.50 + -- if a new shapshot has been created, then recalculate harmonic weights: 1.51 + IF "new_snapshot_v" THEN 1.52 + PERFORM "set_harmonic_initiative_weights"("issue_id_p"); 1.53 + PERFORM "set_harmonic_suggestion_weights"("id") 1.54 + FROM "initiative" WHERE "issue_id" = "issue_id_p"; 1.55 + END IF; 1.56 END IF; 1.57 RETURN; 1.58 END;
2.1 --- a/update/core-update.v2.1.0-v2.1.1.sql Fri Feb 08 18:36:22 2013 +0100 2.2 +++ b/update/core-update.v2.1.0-v2.1.1.sql Fri Feb 08 19:20:17 2013 +0100 2.3 @@ -259,182 +259,157 @@ 2.4 ( "issue"."id"%TYPE ) 2.5 IS 'Calculates and sets "harmonic_weight" of suggestions in a given initiative'; 2.6 2.7 -CREATE OR REPLACE FUNCTION "create_snapshot" 2.8 +CREATE OR REPLACE FUNCTION "manual_freeze"("issue_id_p" "issue"."id"%TYPE) 2.9 + RETURNS VOID 2.10 + LANGUAGE 'plpgsql' VOLATILE AS $$ 2.11 + DECLARE 2.12 + "issue_row" "issue"%ROWTYPE; 2.13 + BEGIN 2.14 + PERFORM "create_snapshot"("issue_id_p"); 2.15 + PERFORM "freeze_after_snapshot"("issue_id_p"); 2.16 + PERFORM "set_harmonic_initiative_weights"("issue_id_p"); 2.17 + PERFORM "set_harmonic_suggestion_weights"("id") 2.18 + FROM "initiative" WHERE "issue_id" = "issue_id_p"; 2.19 + RETURN; 2.20 + END; 2.21 + $$; 2.22 + 2.23 +CREATE OR REPLACE FUNCTION "check_issue" 2.24 ( "issue_id_p" "issue"."id"%TYPE ) 2.25 RETURNS VOID 2.26 LANGUAGE 'plpgsql' VOLATILE AS $$ 2.27 DECLARE 2.28 - "initiative_id_v" "initiative"."id"%TYPE; 2.29 - "suggestion_id_v" "suggestion"."id"%TYPE; 2.30 + "issue_row" "issue"%ROWTYPE; 2.31 + "policy_row" "policy"%ROWTYPE; 2.32 + "new_snapshot_v" BOOLEAN; 2.33 BEGIN 2.34 PERFORM "lock_issue"("issue_id_p"); 2.35 - PERFORM "create_population_snapshot"("issue_id_p"); 2.36 - PERFORM "create_interest_snapshot"("issue_id_p"); 2.37 - UPDATE "issue" SET 2.38 - "snapshot" = now(), 2.39 - "latest_snapshot_event" = 'periodic', 2.40 - "population" = ( 2.41 - SELECT coalesce(sum("weight"), 0) 2.42 - FROM "direct_population_snapshot" 2.43 - WHERE "issue_id" = "issue_id_p" 2.44 - AND "event" = 'periodic' 2.45 - ) 2.46 - WHERE "id" = "issue_id_p"; 2.47 - FOR "initiative_id_v" IN 2.48 - SELECT "id" FROM "initiative" WHERE "issue_id" = "issue_id_p" 2.49 - LOOP 2.50 - UPDATE "initiative" SET 2.51 - "supporter_count" = ( 2.52 - SELECT coalesce(sum("di"."weight"), 0) 2.53 - FROM "direct_interest_snapshot" AS "di" 2.54 - JOIN "direct_supporter_snapshot" AS "ds" 2.55 - ON "di"."member_id" = "ds"."member_id" 2.56 - WHERE "di"."issue_id" = "issue_id_p" 2.57 - AND "di"."event" = 'periodic' 2.58 - AND "ds"."initiative_id" = "initiative_id_v" 2.59 - AND "ds"."event" = 'periodic' 2.60 - ), 2.61 - "informed_supporter_count" = ( 2.62 - SELECT coalesce(sum("di"."weight"), 0) 2.63 - FROM "direct_interest_snapshot" AS "di" 2.64 - JOIN "direct_supporter_snapshot" AS "ds" 2.65 - ON "di"."member_id" = "ds"."member_id" 2.66 - WHERE "di"."issue_id" = "issue_id_p" 2.67 - AND "di"."event" = 'periodic' 2.68 - AND "ds"."initiative_id" = "initiative_id_v" 2.69 - AND "ds"."event" = 'periodic' 2.70 - AND "ds"."informed" 2.71 - ), 2.72 - "satisfied_supporter_count" = ( 2.73 - SELECT coalesce(sum("di"."weight"), 0) 2.74 - FROM "direct_interest_snapshot" AS "di" 2.75 - JOIN "direct_supporter_snapshot" AS "ds" 2.76 - ON "di"."member_id" = "ds"."member_id" 2.77 - WHERE "di"."issue_id" = "issue_id_p" 2.78 - AND "di"."event" = 'periodic' 2.79 - AND "ds"."initiative_id" = "initiative_id_v" 2.80 - AND "ds"."event" = 'periodic' 2.81 - AND "ds"."satisfied" 2.82 - ), 2.83 - "satisfied_informed_supporter_count" = ( 2.84 - SELECT coalesce(sum("di"."weight"), 0) 2.85 - FROM "direct_interest_snapshot" AS "di" 2.86 - JOIN "direct_supporter_snapshot" AS "ds" 2.87 - ON "di"."member_id" = "ds"."member_id" 2.88 - WHERE "di"."issue_id" = "issue_id_p" 2.89 - AND "di"."event" = 'periodic' 2.90 - AND "ds"."initiative_id" = "initiative_id_v" 2.91 - AND "ds"."event" = 'periodic' 2.92 - AND "ds"."informed" 2.93 - AND "ds"."satisfied" 2.94 + SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"; 2.95 + -- only process open issues: 2.96 + IF "issue_row"."closed" ISNULL THEN 2.97 + SELECT * INTO "policy_row" FROM "policy" 2.98 + WHERE "id" = "issue_row"."policy_id"; 2.99 + -- create a snapshot, unless issue is already fully frozen: 2.100 + IF "issue_row"."fully_frozen" ISNULL THEN 2.101 + PERFORM "create_snapshot"("issue_id_p"); 2.102 + "new_snapshot_v" := TRUE; 2.103 + SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"; 2.104 + ELSE 2.105 + "new_snapshot_v" := FALSE; 2.106 + END IF; 2.107 + -- eventually close or accept issues, which have not been accepted: 2.108 + IF "issue_row"."accepted" ISNULL THEN 2.109 + IF EXISTS ( 2.110 + SELECT NULL FROM "initiative" 2.111 + WHERE "issue_id" = "issue_id_p" 2.112 + AND "supporter_count" > 0 2.113 + AND "supporter_count" * "policy_row"."issue_quorum_den" 2.114 + >= "issue_row"."population" * "policy_row"."issue_quorum_num" 2.115 + ) THEN 2.116 + -- accept issues, if supporter count is high enough 2.117 + PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission'); 2.118 + -- NOTE: "issue_row" used later 2.119 + "issue_row"."state" := 'discussion'; 2.120 + "issue_row"."accepted" := now(); 2.121 + UPDATE "issue" SET 2.122 + "state" = "issue_row"."state", 2.123 + "accepted" = "issue_row"."accepted" 2.124 + WHERE "id" = "issue_row"."id"; 2.125 + ELSIF 2.126 + now() >= "issue_row"."created" + "issue_row"."admission_time" 2.127 + THEN 2.128 + -- close issues, if admission time has expired 2.129 + PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission'); 2.130 + UPDATE "issue" SET 2.131 + "state" = 'canceled_issue_not_accepted', 2.132 + "closed" = now() 2.133 + WHERE "id" = "issue_row"."id"; 2.134 + END IF; 2.135 + END IF; 2.136 + -- eventually half freeze issues: 2.137 + IF 2.138 + -- NOTE: issue can't be closed at this point, if it has been accepted 2.139 + "issue_row"."accepted" NOTNULL AND 2.140 + "issue_row"."half_frozen" ISNULL 2.141 + THEN 2.142 + IF 2.143 + now() >= "issue_row"."accepted" + "issue_row"."discussion_time" 2.144 + THEN 2.145 + PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze'); 2.146 + -- NOTE: "issue_row" used later 2.147 + "issue_row"."state" := 'verification'; 2.148 + "issue_row"."half_frozen" := now(); 2.149 + UPDATE "issue" SET 2.150 + "state" = "issue_row"."state", 2.151 + "half_frozen" = "issue_row"."half_frozen" 2.152 + WHERE "id" = "issue_row"."id"; 2.153 + END IF; 2.154 + END IF; 2.155 + -- close issues after some time, if all initiatives have been revoked: 2.156 + IF 2.157 + "issue_row"."closed" ISNULL AND 2.158 + NOT EXISTS ( 2.159 + -- all initiatives are revoked 2.160 + SELECT NULL FROM "initiative" 2.161 + WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL 2.162 + ) AND ( 2.163 + -- and issue has not been accepted yet 2.164 + "issue_row"."accepted" ISNULL OR 2.165 + NOT EXISTS ( 2.166 + -- or no initiatives have been revoked lately 2.167 + SELECT NULL FROM "initiative" 2.168 + WHERE "issue_id" = "issue_id_p" 2.169 + AND now() < "revoked" + "issue_row"."verification_time" 2.170 + ) OR ( 2.171 + -- or verification time has elapsed 2.172 + "issue_row"."half_frozen" NOTNULL AND 2.173 + "issue_row"."fully_frozen" ISNULL AND 2.174 + now() >= "issue_row"."half_frozen" + "issue_row"."verification_time" 2.175 + ) 2.176 ) 2.177 - WHERE "id" = "initiative_id_v"; 2.178 - FOR "suggestion_id_v" IN 2.179 - SELECT "id" FROM "suggestion" 2.180 - WHERE "initiative_id" = "initiative_id_v" 2.181 - LOOP 2.182 - UPDATE "suggestion" SET 2.183 - "minus2_unfulfilled_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" = FALSE 2.194 - ), 2.195 - "minus2_fulfilled_count" = ( 2.196 - SELECT coalesce(sum("snapshot"."weight"), 0) 2.197 - FROM "issue" CROSS JOIN "opinion" 2.198 - JOIN "direct_interest_snapshot" AS "snapshot" 2.199 - ON "snapshot"."issue_id" = "issue"."id" 2.200 - AND "snapshot"."event" = "issue"."latest_snapshot_event" 2.201 - AND "snapshot"."member_id" = "opinion"."member_id" 2.202 - WHERE "issue"."id" = "issue_id_p" 2.203 - AND "opinion"."suggestion_id" = "suggestion_id_v" 2.204 - AND "opinion"."degree" = -2 2.205 - AND "opinion"."fulfilled" = TRUE 2.206 - ), 2.207 - "minus1_unfulfilled_count" = ( 2.208 - SELECT coalesce(sum("snapshot"."weight"), 0) 2.209 - FROM "issue" CROSS JOIN "opinion" 2.210 - JOIN "direct_interest_snapshot" AS "snapshot" 2.211 - ON "snapshot"."issue_id" = "issue"."id" 2.212 - AND "snapshot"."event" = "issue"."latest_snapshot_event" 2.213 - AND "snapshot"."member_id" = "opinion"."member_id" 2.214 - WHERE "issue"."id" = "issue_id_p" 2.215 - AND "opinion"."suggestion_id" = "suggestion_id_v" 2.216 - AND "opinion"."degree" = -1 2.217 - AND "opinion"."fulfilled" = FALSE 2.218 - ), 2.219 - "minus1_fulfilled_count" = ( 2.220 - SELECT coalesce(sum("snapshot"."weight"), 0) 2.221 - FROM "issue" CROSS JOIN "opinion" 2.222 - JOIN "direct_interest_snapshot" AS "snapshot" 2.223 - ON "snapshot"."issue_id" = "issue"."id" 2.224 - AND "snapshot"."event" = "issue"."latest_snapshot_event" 2.225 - AND "snapshot"."member_id" = "opinion"."member_id" 2.226 - WHERE "issue"."id" = "issue_id_p" 2.227 - AND "opinion"."suggestion_id" = "suggestion_id_v" 2.228 - AND "opinion"."degree" = -1 2.229 - AND "opinion"."fulfilled" = TRUE 2.230 - ), 2.231 - "plus1_unfulfilled_count" = ( 2.232 - SELECT coalesce(sum("snapshot"."weight"), 0) 2.233 - FROM "issue" CROSS JOIN "opinion" 2.234 - JOIN "direct_interest_snapshot" AS "snapshot" 2.235 - ON "snapshot"."issue_id" = "issue"."id" 2.236 - AND "snapshot"."event" = "issue"."latest_snapshot_event" 2.237 - AND "snapshot"."member_id" = "opinion"."member_id" 2.238 - WHERE "issue"."id" = "issue_id_p" 2.239 - AND "opinion"."suggestion_id" = "suggestion_id_v" 2.240 - AND "opinion"."degree" = 1 2.241 - AND "opinion"."fulfilled" = FALSE 2.242 - ), 2.243 - "plus1_fulfilled_count" = ( 2.244 - SELECT coalesce(sum("snapshot"."weight"), 0) 2.245 - FROM "issue" CROSS JOIN "opinion" 2.246 - JOIN "direct_interest_snapshot" AS "snapshot" 2.247 - ON "snapshot"."issue_id" = "issue"."id" 2.248 - AND "snapshot"."event" = "issue"."latest_snapshot_event" 2.249 - AND "snapshot"."member_id" = "opinion"."member_id" 2.250 - WHERE "issue"."id" = "issue_id_p" 2.251 - AND "opinion"."suggestion_id" = "suggestion_id_v" 2.252 - AND "opinion"."degree" = 1 2.253 - AND "opinion"."fulfilled" = TRUE 2.254 - ), 2.255 - "plus2_unfulfilled_count" = ( 2.256 - SELECT coalesce(sum("snapshot"."weight"), 0) 2.257 - FROM "issue" CROSS JOIN "opinion" 2.258 - JOIN "direct_interest_snapshot" AS "snapshot" 2.259 - ON "snapshot"."issue_id" = "issue"."id" 2.260 - AND "snapshot"."event" = "issue"."latest_snapshot_event" 2.261 - AND "snapshot"."member_id" = "opinion"."member_id" 2.262 - WHERE "issue"."id" = "issue_id_p" 2.263 - AND "opinion"."suggestion_id" = "suggestion_id_v" 2.264 - AND "opinion"."degree" = 2 2.265 - AND "opinion"."fulfilled" = FALSE 2.266 - ), 2.267 - "plus2_fulfilled_count" = ( 2.268 - SELECT coalesce(sum("snapshot"."weight"), 0) 2.269 - FROM "issue" CROSS JOIN "opinion" 2.270 - JOIN "direct_interest_snapshot" AS "snapshot" 2.271 - ON "snapshot"."issue_id" = "issue"."id" 2.272 - AND "snapshot"."event" = "issue"."latest_snapshot_event" 2.273 - AND "snapshot"."member_id" = "opinion"."member_id" 2.274 - WHERE "issue"."id" = "issue_id_p" 2.275 - AND "opinion"."suggestion_id" = "suggestion_id_v" 2.276 - AND "opinion"."degree" = 2 2.277 - AND "opinion"."fulfilled" = TRUE 2.278 - ) 2.279 - WHERE "suggestion"."id" = "suggestion_id_v"; 2.280 - END LOOP; 2.281 - PERFORM "set_harmonic_suggestion_weights"("initiative_id_v"); 2.282 - END LOOP; 2.283 - PERFORM "set_harmonic_initiative_weights"("issue_id_p"); 2.284 + THEN 2.285 + -- NOTE: "issue_row" used later 2.286 + IF "issue_row"."accepted" ISNULL THEN 2.287 + "issue_row"."state" := 'canceled_revoked_before_accepted'; 2.288 + ELSIF "issue_row"."half_frozen" ISNULL THEN 2.289 + "issue_row"."state" := 'canceled_after_revocation_during_discussion'; 2.290 + ELSE 2.291 + "issue_row"."state" := 'canceled_after_revocation_during_verification'; 2.292 + END IF; 2.293 + "issue_row"."closed" := now(); 2.294 + UPDATE "issue" SET 2.295 + "state" = "issue_row"."state", 2.296 + "closed" = "issue_row"."closed" 2.297 + WHERE "id" = "issue_row"."id"; 2.298 + END IF; 2.299 + -- fully freeze issue after verification time: 2.300 + IF 2.301 + "issue_row"."half_frozen" NOTNULL AND 2.302 + "issue_row"."fully_frozen" ISNULL AND 2.303 + "issue_row"."closed" ISNULL AND 2.304 + now() >= "issue_row"."half_frozen" + "issue_row"."verification_time" 2.305 + THEN 2.306 + PERFORM "freeze_after_snapshot"("issue_id_p"); 2.307 + -- NOTE: "issue" might change, thus "issue_row" has to be updated below 2.308 + END IF; 2.309 + SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"; 2.310 + -- close issue by calling close_voting(...) after voting time: 2.311 + IF 2.312 + "issue_row"."closed" ISNULL AND 2.313 + "issue_row"."fully_frozen" NOTNULL AND 2.314 + now() >= "issue_row"."fully_frozen" + "issue_row"."voting_time" 2.315 + THEN 2.316 + PERFORM "close_voting"("issue_id_p"); 2.317 + -- calculate ranks will not consume much time and can be done now 2.318 + PERFORM "calculate_ranks"("issue_id_p"); 2.319 + END IF; 2.320 + -- if a new shapshot has been created, then recalculate harmonic weights: 2.321 + IF "new_snapshot_v" THEN 2.322 + PERFORM "set_harmonic_initiative_weights"("issue_id_p"); 2.323 + PERFORM "set_harmonic_suggestion_weights"("id") 2.324 + FROM "initiative" WHERE "issue_id" = "issue_id_p"; 2.325 + END IF; 2.326 + END IF; 2.327 RETURN; 2.328 END; 2.329 $$;