liquid_feedback_core
changeset 324:a0dd8c78bd10
Removed update script to v2.1.1 due to upcoming major changes
author | jbe |
---|---|
date | Sun Feb 10 18:41:56 2013 +0100 (2013-02-10) |
parents | 4c7a864829b0 |
children | d59d0c3e4a18 |
files | core.sql update/core-update.v2.1.0-v2.1.1.sql |
line diff
1.1 --- a/core.sql Sat Feb 09 13:43:17 2013 +0100 1.2 +++ b/core.sql Sun Feb 10 18:41:56 2013 +0100 1.3 @@ -7,7 +7,7 @@ 1.4 BEGIN; 1.5 1.6 CREATE VIEW "liquid_feedback_version" AS 1.7 - SELECT * FROM (VALUES ('2.1.1', 2, 1, 1)) 1.8 + SELECT * FROM (VALUES ('2.2.0', 2, 2, 0)) 1.9 AS "subquery"("string", "major", "minor", "revision"); 1.10 1.11
2.1 --- a/update/core-update.v2.1.0-v2.1.1.sql Sat Feb 09 13:43:17 2013 +0100 2.2 +++ /dev/null Thu Jan 01 00:00:00 1970 +0000 2.3 @@ -1,296 +0,0 @@ 2.4 -BEGIN; 2.5 - 2.6 -CREATE OR REPLACE VIEW "liquid_feedback_version" AS 2.7 - SELECT * FROM (VALUES ('2.1.1', 2, 1, 1)) 2.8 - AS "subquery"("string", "major", "minor", "revision"); 2.9 - 2.10 -ALTER TABLE "initiative" ADD COLUMN "harmonic_weight" NUMERIC(12, 3); 2.11 -COMMENT ON COLUMN "initiative"."harmonic_weight" IS 'Indicates the relevancy of the initiative, calculated from the potential supporters weighted with the harmonic series to avoid a large number of clones affecting other initiative''s sorting positions too much; shall be used as secondary sorting key after "admitted" as primary sorting key'; 2.12 - 2.13 -ALTER TABLE "suggestion" ADD COLUMN "harmonic_weight" NUMERIC(12, 3); 2.14 -COMMENT ON COLUMN "suggestion"."harmonic_weight" IS 'Indicates the relevancy of the suggestion, calculated from the supporters (positive "degree") of the suggestion weighted with the harmonic series to avoid a large number of clones affecting other suggestion''s sortings position too much'; 2.15 - 2.16 -CREATE VIEW "remaining_harmonic_supporter_weight" AS 2.17 - SELECT 2.18 - "direct_interest_snapshot"."issue_id", 2.19 - "direct_interest_snapshot"."event", 2.20 - "direct_interest_snapshot"."member_id", 2.21 - "direct_interest_snapshot"."weight" AS "weight_num", 2.22 - count("initiative"."id") AS "weight_den" 2.23 - FROM "issue" 2.24 - JOIN "direct_interest_snapshot" 2.25 - ON "issue"."id" = "direct_interest_snapshot"."issue_id" 2.26 - AND "issue"."latest_snapshot_event" = "direct_interest_snapshot"."event" 2.27 - JOIN "direct_supporter_snapshot" 2.28 - ON "direct_interest_snapshot"."issue_id" = "direct_supporter_snapshot"."issue_id" 2.29 - AND "direct_interest_snapshot"."event" = "direct_supporter_snapshot"."event" 2.30 - AND "direct_interest_snapshot"."member_id" = "direct_supporter_snapshot"."member_id" 2.31 - JOIN "initiative" 2.32 - ON "direct_supporter_snapshot"."initiative_id" = "initiative"."id" 2.33 - AND ( 2.34 - "direct_supporter_snapshot"."satisfied" = TRUE OR 2.35 - coalesce("initiative"."admitted", FALSE) = FALSE 2.36 - ) 2.37 - AND "initiative"."harmonic_weight" ISNULL 2.38 - GROUP BY 2.39 - "direct_interest_snapshot"."issue_id", 2.40 - "direct_interest_snapshot"."event", 2.41 - "direct_interest_snapshot"."member_id", 2.42 - "direct_interest_snapshot"."weight"; 2.43 - 2.44 -COMMENT ON VIEW "remaining_harmonic_supporter_weight" IS 'Helper view for function "set_harmonic_initiative_weights"'; 2.45 - 2.46 -CREATE VIEW "remaining_harmonic_initiative_weight_summands" AS 2.47 - SELECT 2.48 - "initiative"."issue_id", 2.49 - "initiative"."id" AS "initiative_id", 2.50 - "initiative"."admitted", 2.51 - sum("remaining_harmonic_supporter_weight"."weight_num") AS "weight_num", 2.52 - "remaining_harmonic_supporter_weight"."weight_den" 2.53 - FROM "remaining_harmonic_supporter_weight" 2.54 - JOIN "direct_supporter_snapshot" 2.55 - ON "remaining_harmonic_supporter_weight"."issue_id" = "direct_supporter_snapshot"."issue_id" 2.56 - AND "remaining_harmonic_supporter_weight"."event" = "direct_supporter_snapshot"."event" 2.57 - AND "remaining_harmonic_supporter_weight"."member_id" = "direct_supporter_snapshot"."member_id" 2.58 - JOIN "initiative" 2.59 - ON "direct_supporter_snapshot"."initiative_id" = "initiative"."id" 2.60 - AND ( 2.61 - "direct_supporter_snapshot"."satisfied" = TRUE OR 2.62 - coalesce("initiative"."admitted", FALSE) = FALSE 2.63 - ) 2.64 - AND "initiative"."harmonic_weight" ISNULL 2.65 - GROUP BY 2.66 - "initiative"."issue_id", 2.67 - "initiative"."id", 2.68 - "initiative"."admitted", 2.69 - "remaining_harmonic_supporter_weight"."weight_den"; 2.70 - 2.71 -COMMENT ON VIEW "remaining_harmonic_initiative_weight_summands" IS 'Helper view for function "set_harmonic_initiative_weights"'; 2.72 - 2.73 -CREATE FUNCTION "set_harmonic_initiative_weights" 2.74 - ( "issue_id_p" "issue"."id"%TYPE ) 2.75 - RETURNS VOID 2.76 - LANGUAGE 'plpgsql' VOLATILE AS $$ 2.77 - DECLARE 2.78 - "weight_row" "remaining_harmonic_initiative_weight_summands"%ROWTYPE; 2.79 - "i" INT4; 2.80 - "count_v" INT4; 2.81 - "summand_v" FLOAT; 2.82 - "id_ary" INT4[]; 2.83 - "weight_ary" FLOAT[]; 2.84 - "min_weight_v" FLOAT; 2.85 - BEGIN 2.86 - UPDATE "initiative" SET "harmonic_weight" = NULL 2.87 - WHERE "issue_id" = "issue_id_p"; 2.88 - LOOP 2.89 - "min_weight_v" := NULL; 2.90 - "i" := 0; 2.91 - "count_v" := 0; 2.92 - FOR "weight_row" IN 2.93 - SELECT * FROM "remaining_harmonic_initiative_weight_summands" 2.94 - WHERE "issue_id" = "issue_id_p" 2.95 - AND ( 2.96 - coalesce("admitted", FALSE) = FALSE OR NOT EXISTS ( 2.97 - SELECT NULL FROM "initiative" 2.98 - WHERE "issue_id" = "issue_id_p" 2.99 - AND "harmonic_weight" ISNULL 2.100 - AND coalesce("admitted", FALSE) = FALSE 2.101 - ) 2.102 - ) 2.103 - ORDER BY "initiative_id" DESC, "weight_den" DESC 2.104 - -- NOTE: non-admitted initiatives placed first (at last positions), 2.105 - -- latest initiatives treated worse in case of tie 2.106 - LOOP 2.107 - "summand_v" := "weight_row"."weight_num"::FLOAT / "weight_row"."weight_den"::FLOAT; 2.108 - IF "i" = 0 OR "weight_row"."initiative_id" != "id_ary"["i"] THEN 2.109 - "i" := "i" + 1; 2.110 - "count_v" := "i"; 2.111 - "id_ary"["i"] := "weight_row"."initiative_id"; 2.112 - "weight_ary"["i"] := "summand_v"; 2.113 - ELSE 2.114 - "weight_ary"["i"] := "weight_ary"["i"] + "summand_v"; 2.115 - END IF; 2.116 - END LOOP; 2.117 - EXIT WHEN "count_v" = 0; 2.118 - "i" := 1; 2.119 - LOOP 2.120 - "weight_ary"["i"] := "weight_ary"["i"]::NUMERIC(18,9)::NUMERIC(12,3); 2.121 - IF "min_weight_v" ISNULL OR "weight_ary"["i"] < "min_weight_v" THEN 2.122 - "min_weight_v" := "weight_ary"["i"]; 2.123 - END IF; 2.124 - "i" := "i" + 1; 2.125 - EXIT WHEN "i" > "count_v"; 2.126 - END LOOP; 2.127 - "i" := 1; 2.128 - LOOP 2.129 - IF "weight_ary"["i"] = "min_weight_v" THEN 2.130 - UPDATE "initiative" SET "harmonic_weight" = "min_weight_v" 2.131 - WHERE "id" = "id_ary"["i"]; 2.132 - EXIT; 2.133 - END IF; 2.134 - "i" := "i" + 1; 2.135 - END LOOP; 2.136 - END LOOP; 2.137 - UPDATE "initiative" SET "harmonic_weight" = 0 2.138 - WHERE "issue_id" = "issue_id_p" AND "harmonic_weight" ISNULL; 2.139 - END; 2.140 - $$; 2.141 - 2.142 -COMMENT ON FUNCTION "set_harmonic_initiative_weights" 2.143 - ( "issue"."id"%TYPE ) 2.144 - IS 'Calculates and sets "harmonic_weight" of initiatives in a given issue'; 2.145 - 2.146 -CREATE OR REPLACE FUNCTION "manual_freeze"("issue_id_p" "issue"."id"%TYPE) 2.147 - RETURNS VOID 2.148 - LANGUAGE 'plpgsql' VOLATILE AS $$ 2.149 - DECLARE 2.150 - "issue_row" "issue"%ROWTYPE; 2.151 - BEGIN 2.152 - PERFORM "create_snapshot"("issue_id_p"); 2.153 - PERFORM "freeze_after_snapshot"("issue_id_p"); 2.154 - PERFORM "set_harmonic_initiative_weights"("issue_id_p"); 2.155 - RETURN; 2.156 - END; 2.157 - $$; 2.158 - 2.159 -CREATE OR REPLACE FUNCTION "check_issue" 2.160 - ( "issue_id_p" "issue"."id"%TYPE ) 2.161 - RETURNS VOID 2.162 - LANGUAGE 'plpgsql' VOLATILE AS $$ 2.163 - DECLARE 2.164 - "issue_row" "issue"%ROWTYPE; 2.165 - "policy_row" "policy"%ROWTYPE; 2.166 - "new_snapshot_v" BOOLEAN; 2.167 - BEGIN 2.168 - PERFORM "lock_issue"("issue_id_p"); 2.169 - SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"; 2.170 - -- only process open issues: 2.171 - IF "issue_row"."closed" ISNULL THEN 2.172 - SELECT * INTO "policy_row" FROM "policy" 2.173 - WHERE "id" = "issue_row"."policy_id"; 2.174 - -- create a snapshot, unless issue is already fully frozen: 2.175 - IF "issue_row"."fully_frozen" ISNULL THEN 2.176 - PERFORM "create_snapshot"("issue_id_p"); 2.177 - "new_snapshot_v" := TRUE; 2.178 - SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"; 2.179 - ELSE 2.180 - "new_snapshot_v" := FALSE; 2.181 - END IF; 2.182 - -- eventually close or accept issues, which have not been accepted: 2.183 - IF "issue_row"."accepted" ISNULL THEN 2.184 - IF EXISTS ( 2.185 - SELECT NULL FROM "initiative" 2.186 - WHERE "issue_id" = "issue_id_p" 2.187 - AND "supporter_count" > 0 2.188 - AND "supporter_count" * "policy_row"."issue_quorum_den" 2.189 - >= "issue_row"."population" * "policy_row"."issue_quorum_num" 2.190 - ) THEN 2.191 - -- accept issues, if supporter count is high enough 2.192 - PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission'); 2.193 - -- NOTE: "issue_row" used later 2.194 - "issue_row"."state" := 'discussion'; 2.195 - "issue_row"."accepted" := now(); 2.196 - UPDATE "issue" SET 2.197 - "state" = "issue_row"."state", 2.198 - "accepted" = "issue_row"."accepted" 2.199 - WHERE "id" = "issue_row"."id"; 2.200 - ELSIF 2.201 - now() >= "issue_row"."created" + "issue_row"."admission_time" 2.202 - THEN 2.203 - -- close issues, if admission time has expired 2.204 - PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission'); 2.205 - UPDATE "issue" SET 2.206 - "state" = 'canceled_issue_not_accepted', 2.207 - "closed" = now() 2.208 - WHERE "id" = "issue_row"."id"; 2.209 - END IF; 2.210 - END IF; 2.211 - -- eventually half freeze issues: 2.212 - IF 2.213 - -- NOTE: issue can't be closed at this point, if it has been accepted 2.214 - "issue_row"."accepted" NOTNULL AND 2.215 - "issue_row"."half_frozen" ISNULL 2.216 - THEN 2.217 - IF 2.218 - now() >= "issue_row"."accepted" + "issue_row"."discussion_time" 2.219 - THEN 2.220 - PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze'); 2.221 - -- NOTE: "issue_row" used later 2.222 - "issue_row"."state" := 'verification'; 2.223 - "issue_row"."half_frozen" := now(); 2.224 - UPDATE "issue" SET 2.225 - "state" = "issue_row"."state", 2.226 - "half_frozen" = "issue_row"."half_frozen" 2.227 - WHERE "id" = "issue_row"."id"; 2.228 - END IF; 2.229 - END IF; 2.230 - -- close issues after some time, if all initiatives have been revoked: 2.231 - IF 2.232 - "issue_row"."closed" ISNULL AND 2.233 - NOT EXISTS ( 2.234 - -- all initiatives are revoked 2.235 - SELECT NULL FROM "initiative" 2.236 - WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL 2.237 - ) AND ( 2.238 - -- and issue has not been accepted yet 2.239 - "issue_row"."accepted" ISNULL OR 2.240 - NOT EXISTS ( 2.241 - -- or no initiatives have been revoked lately 2.242 - SELECT NULL FROM "initiative" 2.243 - WHERE "issue_id" = "issue_id_p" 2.244 - AND now() < "revoked" + "issue_row"."verification_time" 2.245 - ) OR ( 2.246 - -- or verification time has elapsed 2.247 - "issue_row"."half_frozen" NOTNULL AND 2.248 - "issue_row"."fully_frozen" ISNULL AND 2.249 - now() >= "issue_row"."half_frozen" + "issue_row"."verification_time" 2.250 - ) 2.251 - ) 2.252 - THEN 2.253 - -- NOTE: "issue_row" used later 2.254 - IF "issue_row"."accepted" ISNULL THEN 2.255 - "issue_row"."state" := 'canceled_revoked_before_accepted'; 2.256 - ELSIF "issue_row"."half_frozen" ISNULL THEN 2.257 - "issue_row"."state" := 'canceled_after_revocation_during_discussion'; 2.258 - ELSE 2.259 - "issue_row"."state" := 'canceled_after_revocation_during_verification'; 2.260 - END IF; 2.261 - "issue_row"."closed" := now(); 2.262 - UPDATE "issue" SET 2.263 - "state" = "issue_row"."state", 2.264 - "closed" = "issue_row"."closed" 2.265 - WHERE "id" = "issue_row"."id"; 2.266 - END IF; 2.267 - -- fully freeze issue after verification time: 2.268 - IF 2.269 - "issue_row"."half_frozen" NOTNULL AND 2.270 - "issue_row"."fully_frozen" ISNULL AND 2.271 - "issue_row"."closed" ISNULL AND 2.272 - now() >= "issue_row"."half_frozen" + "issue_row"."verification_time" 2.273 - THEN 2.274 - PERFORM "freeze_after_snapshot"("issue_id_p"); 2.275 - -- NOTE: "issue" might change, thus "issue_row" has to be updated below 2.276 - END IF; 2.277 - SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"; 2.278 - -- close issue by calling close_voting(...) after voting time: 2.279 - IF 2.280 - "issue_row"."closed" ISNULL AND 2.281 - "issue_row"."fully_frozen" NOTNULL AND 2.282 - now() >= "issue_row"."fully_frozen" + "issue_row"."voting_time" 2.283 - THEN 2.284 - PERFORM "close_voting"("issue_id_p"); 2.285 - -- calculate ranks will not consume much time and can be done now 2.286 - PERFORM "calculate_ranks"("issue_id_p"); 2.287 - END IF; 2.288 - -- if a new shapshot has been created, then recalculate harmonic weights: 2.289 - IF "new_snapshot_v" THEN 2.290 - PERFORM "set_harmonic_initiative_weights"("issue_id_p"); 2.291 - END IF; 2.292 - END IF; 2.293 - RETURN; 2.294 - END; 2.295 - $$; 2.296 - 2.297 -SELECT "set_harmonic_initiative_weights"("id") FROM "issue"; 2.298 - 2.299 -COMMIT;