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    $$;

Impressum / About Us