liquid_feedback_core

annotate update/core-update.v2.1.0-v2.1.1.sql @ 323:4c7a864829b0

find changesets by author, revision, files, or words in the commit message
Removed "harmonic_weight" for suggestions, because another proportional ranking algorithm is needed there
author jbe
date Sat Feb 09 13:43:17 2013 +0100 (2013-02-09)
parents fd58f487e1d0
children
rev   line source
jbe@315 1 BEGIN;
jbe@315 2
jbe@315 3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
jbe@315 4 SELECT * FROM (VALUES ('2.1.1', 2, 1, 1))
jbe@315 5 AS "subquery"("string", "major", "minor", "revision");
jbe@315 6
jbe@315 7 ALTER TABLE "initiative" ADD COLUMN "harmonic_weight" NUMERIC(12, 3);
jbe@320 8 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';
jbe@315 9
jbe@315 10 ALTER TABLE "suggestion" ADD COLUMN "harmonic_weight" NUMERIC(12, 3);
jbe@315 11 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';
jbe@315 12
jbe@315 13 CREATE VIEW "remaining_harmonic_supporter_weight" AS
jbe@315 14 SELECT
jbe@315 15 "direct_interest_snapshot"."issue_id",
jbe@315 16 "direct_interest_snapshot"."event",
jbe@315 17 "direct_interest_snapshot"."member_id",
jbe@315 18 "direct_interest_snapshot"."weight" AS "weight_num",
jbe@315 19 count("initiative"."id") AS "weight_den"
jbe@315 20 FROM "issue"
jbe@315 21 JOIN "direct_interest_snapshot"
jbe@315 22 ON "issue"."id" = "direct_interest_snapshot"."issue_id"
jbe@315 23 AND "issue"."latest_snapshot_event" = "direct_interest_snapshot"."event"
jbe@315 24 JOIN "direct_supporter_snapshot"
jbe@315 25 ON "direct_interest_snapshot"."issue_id" = "direct_supporter_snapshot"."issue_id"
jbe@315 26 AND "direct_interest_snapshot"."event" = "direct_supporter_snapshot"."event"
jbe@315 27 AND "direct_interest_snapshot"."member_id" = "direct_supporter_snapshot"."member_id"
jbe@315 28 JOIN "initiative"
jbe@315 29 ON "direct_supporter_snapshot"."initiative_id" = "initiative"."id"
jbe@321 30 AND (
jbe@321 31 "direct_supporter_snapshot"."satisfied" = TRUE OR
jbe@321 32 coalesce("initiative"."admitted", FALSE) = FALSE
jbe@321 33 )
jbe@315 34 AND "initiative"."harmonic_weight" ISNULL
jbe@315 35 GROUP BY
jbe@315 36 "direct_interest_snapshot"."issue_id",
jbe@315 37 "direct_interest_snapshot"."event",
jbe@315 38 "direct_interest_snapshot"."member_id",
jbe@315 39 "direct_interest_snapshot"."weight";
jbe@315 40
jbe@315 41 COMMENT ON VIEW "remaining_harmonic_supporter_weight" IS 'Helper view for function "set_harmonic_initiative_weights"';
jbe@315 42
jbe@315 43 CREATE VIEW "remaining_harmonic_initiative_weight_summands" AS
jbe@315 44 SELECT
jbe@315 45 "initiative"."issue_id",
jbe@315 46 "initiative"."id" AS "initiative_id",
jbe@320 47 "initiative"."admitted",
jbe@315 48 sum("remaining_harmonic_supporter_weight"."weight_num") AS "weight_num",
jbe@315 49 "remaining_harmonic_supporter_weight"."weight_den"
jbe@315 50 FROM "remaining_harmonic_supporter_weight"
jbe@315 51 JOIN "direct_supporter_snapshot"
jbe@315 52 ON "remaining_harmonic_supporter_weight"."issue_id" = "direct_supporter_snapshot"."issue_id"
jbe@315 53 AND "remaining_harmonic_supporter_weight"."event" = "direct_supporter_snapshot"."event"
jbe@315 54 AND "remaining_harmonic_supporter_weight"."member_id" = "direct_supporter_snapshot"."member_id"
jbe@315 55 JOIN "initiative"
jbe@315 56 ON "direct_supporter_snapshot"."initiative_id" = "initiative"."id"
jbe@321 57 AND (
jbe@321 58 "direct_supporter_snapshot"."satisfied" = TRUE OR
jbe@321 59 coalesce("initiative"."admitted", FALSE) = FALSE
jbe@321 60 )
jbe@315 61 AND "initiative"."harmonic_weight" ISNULL
jbe@315 62 GROUP BY
jbe@315 63 "initiative"."issue_id",
jbe@315 64 "initiative"."id",
jbe@320 65 "initiative"."admitted",
jbe@315 66 "remaining_harmonic_supporter_weight"."weight_den";
jbe@315 67
jbe@315 68 COMMENT ON VIEW "remaining_harmonic_initiative_weight_summands" IS 'Helper view for function "set_harmonic_initiative_weights"';
jbe@315 69
jbe@315 70 CREATE FUNCTION "set_harmonic_initiative_weights"
jbe@315 71 ( "issue_id_p" "issue"."id"%TYPE )
jbe@315 72 RETURNS VOID
jbe@315 73 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@315 74 DECLARE
jbe@315 75 "weight_row" "remaining_harmonic_initiative_weight_summands"%ROWTYPE;
jbe@315 76 "i" INT4;
jbe@315 77 "count_v" INT4;
jbe@315 78 "summand_v" FLOAT;
jbe@315 79 "id_ary" INT4[];
jbe@315 80 "weight_ary" FLOAT[];
jbe@315 81 "min_weight_v" FLOAT;
jbe@315 82 BEGIN
jbe@315 83 UPDATE "initiative" SET "harmonic_weight" = NULL
jbe@315 84 WHERE "issue_id" = "issue_id_p";
jbe@315 85 LOOP
jbe@315 86 "min_weight_v" := NULL;
jbe@315 87 "i" := 0;
jbe@315 88 "count_v" := 0;
jbe@315 89 FOR "weight_row" IN
jbe@315 90 SELECT * FROM "remaining_harmonic_initiative_weight_summands"
jbe@315 91 WHERE "issue_id" = "issue_id_p"
jbe@320 92 AND (
jbe@320 93 coalesce("admitted", FALSE) = FALSE OR NOT EXISTS (
jbe@320 94 SELECT NULL FROM "initiative"
jbe@320 95 WHERE "issue_id" = "issue_id_p"
jbe@320 96 AND "harmonic_weight" ISNULL
jbe@320 97 AND coalesce("admitted", FALSE) = FALSE
jbe@320 98 )
jbe@320 99 )
jbe@315 100 ORDER BY "initiative_id" DESC, "weight_den" DESC
jbe@320 101 -- NOTE: non-admitted initiatives placed first (at last positions),
jbe@320 102 -- latest initiatives treated worse in case of tie
jbe@315 103 LOOP
jbe@315 104 "summand_v" := "weight_row"."weight_num"::FLOAT / "weight_row"."weight_den"::FLOAT;
jbe@315 105 IF "i" = 0 OR "weight_row"."initiative_id" != "id_ary"["i"] THEN
jbe@315 106 "i" := "i" + 1;
jbe@315 107 "count_v" := "i";
jbe@315 108 "id_ary"["i"] := "weight_row"."initiative_id";
jbe@315 109 "weight_ary"["i"] := "summand_v";
jbe@315 110 ELSE
jbe@315 111 "weight_ary"["i"] := "weight_ary"["i"] + "summand_v";
jbe@315 112 END IF;
jbe@315 113 END LOOP;
jbe@315 114 EXIT WHEN "count_v" = 0;
jbe@315 115 "i" := 1;
jbe@315 116 LOOP
jbe@315 117 "weight_ary"["i"] := "weight_ary"["i"]::NUMERIC(18,9)::NUMERIC(12,3);
jbe@315 118 IF "min_weight_v" ISNULL OR "weight_ary"["i"] < "min_weight_v" THEN
jbe@315 119 "min_weight_v" := "weight_ary"["i"];
jbe@315 120 END IF;
jbe@315 121 "i" := "i" + 1;
jbe@315 122 EXIT WHEN "i" > "count_v";
jbe@315 123 END LOOP;
jbe@315 124 "i" := 1;
jbe@315 125 LOOP
jbe@315 126 IF "weight_ary"["i"] = "min_weight_v" THEN
jbe@315 127 UPDATE "initiative" SET "harmonic_weight" = "min_weight_v"
jbe@315 128 WHERE "id" = "id_ary"["i"];
jbe@315 129 EXIT;
jbe@315 130 END IF;
jbe@315 131 "i" := "i" + 1;
jbe@315 132 END LOOP;
jbe@315 133 END LOOP;
jbe@316 134 UPDATE "initiative" SET "harmonic_weight" = 0
jbe@316 135 WHERE "issue_id" = "issue_id_p" AND "harmonic_weight" ISNULL;
jbe@315 136 END;
jbe@315 137 $$;
jbe@315 138
jbe@315 139 COMMENT ON FUNCTION "set_harmonic_initiative_weights"
jbe@315 140 ( "issue"."id"%TYPE )
jbe@315 141 IS 'Calculates and sets "harmonic_weight" of initiatives in a given issue';
jbe@315 142
jbe@322 143 CREATE OR REPLACE FUNCTION "manual_freeze"("issue_id_p" "issue"."id"%TYPE)
jbe@322 144 RETURNS VOID
jbe@322 145 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@322 146 DECLARE
jbe@322 147 "issue_row" "issue"%ROWTYPE;
jbe@322 148 BEGIN
jbe@322 149 PERFORM "create_snapshot"("issue_id_p");
jbe@322 150 PERFORM "freeze_after_snapshot"("issue_id_p");
jbe@322 151 PERFORM "set_harmonic_initiative_weights"("issue_id_p");
jbe@322 152 RETURN;
jbe@322 153 END;
jbe@322 154 $$;
jbe@322 155
jbe@322 156 CREATE OR REPLACE FUNCTION "check_issue"
jbe@315 157 ( "issue_id_p" "issue"."id"%TYPE )
jbe@315 158 RETURNS VOID
jbe@315 159 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@315 160 DECLARE
jbe@322 161 "issue_row" "issue"%ROWTYPE;
jbe@322 162 "policy_row" "policy"%ROWTYPE;
jbe@322 163 "new_snapshot_v" BOOLEAN;
jbe@315 164 BEGIN
jbe@315 165 PERFORM "lock_issue"("issue_id_p");
jbe@322 166 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
jbe@322 167 -- only process open issues:
jbe@322 168 IF "issue_row"."closed" ISNULL THEN
jbe@322 169 SELECT * INTO "policy_row" FROM "policy"
jbe@322 170 WHERE "id" = "issue_row"."policy_id";
jbe@322 171 -- create a snapshot, unless issue is already fully frozen:
jbe@322 172 IF "issue_row"."fully_frozen" ISNULL THEN
jbe@322 173 PERFORM "create_snapshot"("issue_id_p");
jbe@322 174 "new_snapshot_v" := TRUE;
jbe@322 175 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
jbe@322 176 ELSE
jbe@322 177 "new_snapshot_v" := FALSE;
jbe@322 178 END IF;
jbe@322 179 -- eventually close or accept issues, which have not been accepted:
jbe@322 180 IF "issue_row"."accepted" ISNULL THEN
jbe@322 181 IF EXISTS (
jbe@322 182 SELECT NULL FROM "initiative"
jbe@322 183 WHERE "issue_id" = "issue_id_p"
jbe@322 184 AND "supporter_count" > 0
jbe@322 185 AND "supporter_count" * "policy_row"."issue_quorum_den"
jbe@322 186 >= "issue_row"."population" * "policy_row"."issue_quorum_num"
jbe@322 187 ) THEN
jbe@322 188 -- accept issues, if supporter count is high enough
jbe@322 189 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
jbe@322 190 -- NOTE: "issue_row" used later
jbe@322 191 "issue_row"."state" := 'discussion';
jbe@322 192 "issue_row"."accepted" := now();
jbe@322 193 UPDATE "issue" SET
jbe@322 194 "state" = "issue_row"."state",
jbe@322 195 "accepted" = "issue_row"."accepted"
jbe@322 196 WHERE "id" = "issue_row"."id";
jbe@322 197 ELSIF
jbe@322 198 now() >= "issue_row"."created" + "issue_row"."admission_time"
jbe@322 199 THEN
jbe@322 200 -- close issues, if admission time has expired
jbe@322 201 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
jbe@322 202 UPDATE "issue" SET
jbe@322 203 "state" = 'canceled_issue_not_accepted',
jbe@322 204 "closed" = now()
jbe@322 205 WHERE "id" = "issue_row"."id";
jbe@322 206 END IF;
jbe@322 207 END IF;
jbe@322 208 -- eventually half freeze issues:
jbe@322 209 IF
jbe@322 210 -- NOTE: issue can't be closed at this point, if it has been accepted
jbe@322 211 "issue_row"."accepted" NOTNULL AND
jbe@322 212 "issue_row"."half_frozen" ISNULL
jbe@322 213 THEN
jbe@322 214 IF
jbe@322 215 now() >= "issue_row"."accepted" + "issue_row"."discussion_time"
jbe@322 216 THEN
jbe@322 217 PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze');
jbe@322 218 -- NOTE: "issue_row" used later
jbe@322 219 "issue_row"."state" := 'verification';
jbe@322 220 "issue_row"."half_frozen" := now();
jbe@322 221 UPDATE "issue" SET
jbe@322 222 "state" = "issue_row"."state",
jbe@322 223 "half_frozen" = "issue_row"."half_frozen"
jbe@322 224 WHERE "id" = "issue_row"."id";
jbe@322 225 END IF;
jbe@322 226 END IF;
jbe@322 227 -- close issues after some time, if all initiatives have been revoked:
jbe@322 228 IF
jbe@322 229 "issue_row"."closed" ISNULL AND
jbe@322 230 NOT EXISTS (
jbe@322 231 -- all initiatives are revoked
jbe@322 232 SELECT NULL FROM "initiative"
jbe@322 233 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
jbe@322 234 ) AND (
jbe@322 235 -- and issue has not been accepted yet
jbe@322 236 "issue_row"."accepted" ISNULL OR
jbe@322 237 NOT EXISTS (
jbe@322 238 -- or no initiatives have been revoked lately
jbe@322 239 SELECT NULL FROM "initiative"
jbe@322 240 WHERE "issue_id" = "issue_id_p"
jbe@322 241 AND now() < "revoked" + "issue_row"."verification_time"
jbe@322 242 ) OR (
jbe@322 243 -- or verification time has elapsed
jbe@322 244 "issue_row"."half_frozen" NOTNULL AND
jbe@322 245 "issue_row"."fully_frozen" ISNULL AND
jbe@322 246 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
jbe@322 247 )
jbe@315 248 )
jbe@322 249 THEN
jbe@322 250 -- NOTE: "issue_row" used later
jbe@322 251 IF "issue_row"."accepted" ISNULL THEN
jbe@322 252 "issue_row"."state" := 'canceled_revoked_before_accepted';
jbe@322 253 ELSIF "issue_row"."half_frozen" ISNULL THEN
jbe@322 254 "issue_row"."state" := 'canceled_after_revocation_during_discussion';
jbe@322 255 ELSE
jbe@322 256 "issue_row"."state" := 'canceled_after_revocation_during_verification';
jbe@322 257 END IF;
jbe@322 258 "issue_row"."closed" := now();
jbe@322 259 UPDATE "issue" SET
jbe@322 260 "state" = "issue_row"."state",
jbe@322 261 "closed" = "issue_row"."closed"
jbe@322 262 WHERE "id" = "issue_row"."id";
jbe@322 263 END IF;
jbe@322 264 -- fully freeze issue after verification time:
jbe@322 265 IF
jbe@322 266 "issue_row"."half_frozen" NOTNULL AND
jbe@322 267 "issue_row"."fully_frozen" ISNULL AND
jbe@322 268 "issue_row"."closed" ISNULL AND
jbe@322 269 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
jbe@322 270 THEN
jbe@322 271 PERFORM "freeze_after_snapshot"("issue_id_p");
jbe@322 272 -- NOTE: "issue" might change, thus "issue_row" has to be updated below
jbe@322 273 END IF;
jbe@322 274 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
jbe@322 275 -- close issue by calling close_voting(...) after voting time:
jbe@322 276 IF
jbe@322 277 "issue_row"."closed" ISNULL AND
jbe@322 278 "issue_row"."fully_frozen" NOTNULL AND
jbe@322 279 now() >= "issue_row"."fully_frozen" + "issue_row"."voting_time"
jbe@322 280 THEN
jbe@322 281 PERFORM "close_voting"("issue_id_p");
jbe@322 282 -- calculate ranks will not consume much time and can be done now
jbe@322 283 PERFORM "calculate_ranks"("issue_id_p");
jbe@322 284 END IF;
jbe@322 285 -- if a new shapshot has been created, then recalculate harmonic weights:
jbe@322 286 IF "new_snapshot_v" THEN
jbe@322 287 PERFORM "set_harmonic_initiative_weights"("issue_id_p");
jbe@322 288 END IF;
jbe@322 289 END IF;
jbe@315 290 RETURN;
jbe@315 291 END;
jbe@315 292 $$;
jbe@315 293
jbe@315 294 SELECT "set_harmonic_initiative_weights"("id") FROM "issue";
jbe@315 295
jbe@315 296 COMMIT;

Impressum / About Us