1.1 --- a/update/core-update.v2.1.0-v2.1.1.sql	Fri Feb 08 18:36:22 2013 +0100
     1.2 +++ b/update/core-update.v2.1.0-v2.1.1.sql	Fri Feb 08 19:20:17 2013 +0100
     1.3 @@ -259,182 +259,157 @@
     1.4    ( "issue"."id"%TYPE )
     1.5    IS 'Calculates and sets "harmonic_weight" of suggestions in a given initiative';
     1.6  
     1.7 -CREATE OR REPLACE FUNCTION "create_snapshot"
     1.8 +CREATE OR REPLACE FUNCTION "manual_freeze"("issue_id_p" "issue"."id"%TYPE)
     1.9 +  RETURNS VOID
    1.10 +  LANGUAGE 'plpgsql' VOLATILE AS $$
    1.11 +    DECLARE
    1.12 +      "issue_row" "issue"%ROWTYPE;
    1.13 +    BEGIN
    1.14 +      PERFORM "create_snapshot"("issue_id_p");
    1.15 +      PERFORM "freeze_after_snapshot"("issue_id_p");
    1.16 +      PERFORM "set_harmonic_initiative_weights"("issue_id_p");
    1.17 +      PERFORM "set_harmonic_suggestion_weights"("id")
    1.18 +        FROM "initiative" WHERE "issue_id" = "issue_id_p";
    1.19 +      RETURN;
    1.20 +    END;
    1.21 +  $$;
    1.22 +
    1.23 +CREATE OR REPLACE FUNCTION "check_issue"
    1.24    ( "issue_id_p" "issue"."id"%TYPE )
    1.25    RETURNS VOID
    1.26    LANGUAGE 'plpgsql' VOLATILE AS $$
    1.27      DECLARE
    1.28 -      "initiative_id_v"    "initiative"."id"%TYPE;
    1.29 -      "suggestion_id_v"    "suggestion"."id"%TYPE;
    1.30 +      "issue_row"      "issue"%ROWTYPE;
    1.31 +      "policy_row"     "policy"%ROWTYPE;
    1.32 +      "new_snapshot_v" BOOLEAN;
    1.33      BEGIN
    1.34        PERFORM "lock_issue"("issue_id_p");
    1.35 -      PERFORM "create_population_snapshot"("issue_id_p");
    1.36 -      PERFORM "create_interest_snapshot"("issue_id_p");
    1.37 -      UPDATE "issue" SET
    1.38 -        "snapshot" = now(),
    1.39 -        "latest_snapshot_event" = 'periodic',
    1.40 -        "population" = (
    1.41 -          SELECT coalesce(sum("weight"), 0)
    1.42 -          FROM "direct_population_snapshot"
    1.43 -          WHERE "issue_id" = "issue_id_p"
    1.44 -          AND "event" = 'periodic'
    1.45 -        )
    1.46 -        WHERE "id" = "issue_id_p";
    1.47 -      FOR "initiative_id_v" IN
    1.48 -        SELECT "id" FROM "initiative" WHERE "issue_id" = "issue_id_p"
    1.49 -      LOOP
    1.50 -        UPDATE "initiative" SET
    1.51 -          "supporter_count" = (
    1.52 -            SELECT coalesce(sum("di"."weight"), 0)
    1.53 -            FROM "direct_interest_snapshot" AS "di"
    1.54 -            JOIN "direct_supporter_snapshot" AS "ds"
    1.55 -            ON "di"."member_id" = "ds"."member_id"
    1.56 -            WHERE "di"."issue_id" = "issue_id_p"
    1.57 -            AND "di"."event" = 'periodic'
    1.58 -            AND "ds"."initiative_id" = "initiative_id_v"
    1.59 -            AND "ds"."event" = 'periodic'
    1.60 -          ),
    1.61 -          "informed_supporter_count" = (
    1.62 -            SELECT coalesce(sum("di"."weight"), 0)
    1.63 -            FROM "direct_interest_snapshot" AS "di"
    1.64 -            JOIN "direct_supporter_snapshot" AS "ds"
    1.65 -            ON "di"."member_id" = "ds"."member_id"
    1.66 -            WHERE "di"."issue_id" = "issue_id_p"
    1.67 -            AND "di"."event" = 'periodic'
    1.68 -            AND "ds"."initiative_id" = "initiative_id_v"
    1.69 -            AND "ds"."event" = 'periodic'
    1.70 -            AND "ds"."informed"
    1.71 -          ),
    1.72 -          "satisfied_supporter_count" = (
    1.73 -            SELECT coalesce(sum("di"."weight"), 0)
    1.74 -            FROM "direct_interest_snapshot" AS "di"
    1.75 -            JOIN "direct_supporter_snapshot" AS "ds"
    1.76 -            ON "di"."member_id" = "ds"."member_id"
    1.77 -            WHERE "di"."issue_id" = "issue_id_p"
    1.78 -            AND "di"."event" = 'periodic'
    1.79 -            AND "ds"."initiative_id" = "initiative_id_v"
    1.80 -            AND "ds"."event" = 'periodic'
    1.81 -            AND "ds"."satisfied"
    1.82 -          ),
    1.83 -          "satisfied_informed_supporter_count" = (
    1.84 -            SELECT coalesce(sum("di"."weight"), 0)
    1.85 -            FROM "direct_interest_snapshot" AS "di"
    1.86 -            JOIN "direct_supporter_snapshot" AS "ds"
    1.87 -            ON "di"."member_id" = "ds"."member_id"
    1.88 -            WHERE "di"."issue_id" = "issue_id_p"
    1.89 -            AND "di"."event" = 'periodic'
    1.90 -            AND "ds"."initiative_id" = "initiative_id_v"
    1.91 -            AND "ds"."event" = 'periodic'
    1.92 -            AND "ds"."informed"
    1.93 -            AND "ds"."satisfied"
    1.94 +      SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
    1.95 +      -- only process open issues:
    1.96 +      IF "issue_row"."closed" ISNULL THEN
    1.97 +        SELECT * INTO "policy_row" FROM "policy"
    1.98 +          WHERE "id" = "issue_row"."policy_id";
    1.99 +        -- create a snapshot, unless issue is already fully frozen:
   1.100 +        IF "issue_row"."fully_frozen" ISNULL THEN
   1.101 +          PERFORM "create_snapshot"("issue_id_p");
   1.102 +          "new_snapshot_v" := TRUE;
   1.103 +          SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
   1.104 +        ELSE
   1.105 +          "new_snapshot_v" := FALSE;
   1.106 +        END IF;
   1.107 +        -- eventually close or accept issues, which have not been accepted:
   1.108 +        IF "issue_row"."accepted" ISNULL THEN
   1.109 +          IF EXISTS (
   1.110 +            SELECT NULL FROM "initiative"
   1.111 +            WHERE "issue_id" = "issue_id_p"
   1.112 +            AND "supporter_count" > 0
   1.113 +            AND "supporter_count" * "policy_row"."issue_quorum_den"
   1.114 +            >= "issue_row"."population" * "policy_row"."issue_quorum_num"
   1.115 +          ) THEN
   1.116 +            -- accept issues, if supporter count is high enough
   1.117 +            PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
   1.118 +            -- NOTE: "issue_row" used later
   1.119 +            "issue_row"."state" := 'discussion';
   1.120 +            "issue_row"."accepted" := now();
   1.121 +            UPDATE "issue" SET
   1.122 +              "state"    = "issue_row"."state",
   1.123 +              "accepted" = "issue_row"."accepted"
   1.124 +              WHERE "id" = "issue_row"."id";
   1.125 +          ELSIF
   1.126 +            now() >= "issue_row"."created" + "issue_row"."admission_time"
   1.127 +          THEN
   1.128 +            -- close issues, if admission time has expired
   1.129 +            PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
   1.130 +            UPDATE "issue" SET
   1.131 +              "state" = 'canceled_issue_not_accepted',
   1.132 +              "closed" = now()
   1.133 +              WHERE "id" = "issue_row"."id";
   1.134 +          END IF;
   1.135 +        END IF;
   1.136 +        -- eventually half freeze issues:
   1.137 +        IF
   1.138 +          -- NOTE: issue can't be closed at this point, if it has been accepted
   1.139 +          "issue_row"."accepted" NOTNULL AND
   1.140 +          "issue_row"."half_frozen" ISNULL
   1.141 +        THEN
   1.142 +          IF
   1.143 +            now() >= "issue_row"."accepted" + "issue_row"."discussion_time"
   1.144 +          THEN
   1.145 +            PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze');
   1.146 +            -- NOTE: "issue_row" used later
   1.147 +            "issue_row"."state" := 'verification';
   1.148 +            "issue_row"."half_frozen" := now();
   1.149 +            UPDATE "issue" SET
   1.150 +              "state"       = "issue_row"."state",
   1.151 +              "half_frozen" = "issue_row"."half_frozen"
   1.152 +              WHERE "id" = "issue_row"."id";
   1.153 +          END IF;
   1.154 +        END IF;
   1.155 +        -- close issues after some time, if all initiatives have been revoked:
   1.156 +        IF
   1.157 +          "issue_row"."closed" ISNULL AND
   1.158 +          NOT EXISTS (
   1.159 +            -- all initiatives are revoked
   1.160 +            SELECT NULL FROM "initiative"
   1.161 +            WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
   1.162 +          ) AND (
   1.163 +            -- and issue has not been accepted yet
   1.164 +            "issue_row"."accepted" ISNULL OR
   1.165 +            NOT EXISTS (
   1.166 +              -- or no initiatives have been revoked lately
   1.167 +              SELECT NULL FROM "initiative"
   1.168 +              WHERE "issue_id" = "issue_id_p"
   1.169 +              AND now() < "revoked" + "issue_row"."verification_time"
   1.170 +            ) OR (
   1.171 +              -- or verification time has elapsed
   1.172 +              "issue_row"."half_frozen" NOTNULL AND
   1.173 +              "issue_row"."fully_frozen" ISNULL AND
   1.174 +              now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
   1.175 +            )
   1.176            )
   1.177 -          WHERE "id" = "initiative_id_v";
   1.178 -        FOR "suggestion_id_v" IN
   1.179 -          SELECT "id" FROM "suggestion"
   1.180 -          WHERE "initiative_id" = "initiative_id_v"
   1.181 -        LOOP
   1.182 -          UPDATE "suggestion" SET
   1.183 -            "minus2_unfulfilled_count" = (
   1.184 -              SELECT coalesce(sum("snapshot"."weight"), 0)
   1.185 -              FROM "issue" CROSS JOIN "opinion"
   1.186 -              JOIN "direct_interest_snapshot" AS "snapshot"
   1.187 -              ON "snapshot"."issue_id" = "issue"."id"
   1.188 -              AND "snapshot"."event" = "issue"."latest_snapshot_event"
   1.189 -              AND "snapshot"."member_id" = "opinion"."member_id"
   1.190 -              WHERE "issue"."id" = "issue_id_p"
   1.191 -              AND "opinion"."suggestion_id" = "suggestion_id_v"
   1.192 -              AND "opinion"."degree" = -2
   1.193 -              AND "opinion"."fulfilled" = FALSE
   1.194 -            ),
   1.195 -            "minus2_fulfilled_count" = (
   1.196 -              SELECT coalesce(sum("snapshot"."weight"), 0)
   1.197 -              FROM "issue" CROSS JOIN "opinion"
   1.198 -              JOIN "direct_interest_snapshot" AS "snapshot"
   1.199 -              ON "snapshot"."issue_id" = "issue"."id"
   1.200 -              AND "snapshot"."event" = "issue"."latest_snapshot_event"
   1.201 -              AND "snapshot"."member_id" = "opinion"."member_id"
   1.202 -              WHERE "issue"."id" = "issue_id_p"
   1.203 -              AND "opinion"."suggestion_id" = "suggestion_id_v"
   1.204 -              AND "opinion"."degree" = -2
   1.205 -              AND "opinion"."fulfilled" = TRUE
   1.206 -            ),
   1.207 -            "minus1_unfulfilled_count" = (
   1.208 -              SELECT coalesce(sum("snapshot"."weight"), 0)
   1.209 -              FROM "issue" CROSS JOIN "opinion"
   1.210 -              JOIN "direct_interest_snapshot" AS "snapshot"
   1.211 -              ON "snapshot"."issue_id" = "issue"."id"
   1.212 -              AND "snapshot"."event" = "issue"."latest_snapshot_event"
   1.213 -              AND "snapshot"."member_id" = "opinion"."member_id"
   1.214 -              WHERE "issue"."id" = "issue_id_p"
   1.215 -              AND "opinion"."suggestion_id" = "suggestion_id_v"
   1.216 -              AND "opinion"."degree" = -1
   1.217 -              AND "opinion"."fulfilled" = FALSE
   1.218 -            ),
   1.219 -            "minus1_fulfilled_count" = (
   1.220 -              SELECT coalesce(sum("snapshot"."weight"), 0)
   1.221 -              FROM "issue" CROSS JOIN "opinion"
   1.222 -              JOIN "direct_interest_snapshot" AS "snapshot"
   1.223 -              ON "snapshot"."issue_id" = "issue"."id"
   1.224 -              AND "snapshot"."event" = "issue"."latest_snapshot_event"
   1.225 -              AND "snapshot"."member_id" = "opinion"."member_id"
   1.226 -              WHERE "issue"."id" = "issue_id_p"
   1.227 -              AND "opinion"."suggestion_id" = "suggestion_id_v"
   1.228 -              AND "opinion"."degree" = -1
   1.229 -              AND "opinion"."fulfilled" = TRUE
   1.230 -            ),
   1.231 -            "plus1_unfulfilled_count" = (
   1.232 -              SELECT coalesce(sum("snapshot"."weight"), 0)
   1.233 -              FROM "issue" CROSS JOIN "opinion"
   1.234 -              JOIN "direct_interest_snapshot" AS "snapshot"
   1.235 -              ON "snapshot"."issue_id" = "issue"."id"
   1.236 -              AND "snapshot"."event" = "issue"."latest_snapshot_event"
   1.237 -              AND "snapshot"."member_id" = "opinion"."member_id"
   1.238 -              WHERE "issue"."id" = "issue_id_p"
   1.239 -              AND "opinion"."suggestion_id" = "suggestion_id_v"
   1.240 -              AND "opinion"."degree" = 1
   1.241 -              AND "opinion"."fulfilled" = FALSE
   1.242 -            ),
   1.243 -            "plus1_fulfilled_count" = (
   1.244 -              SELECT coalesce(sum("snapshot"."weight"), 0)
   1.245 -              FROM "issue" CROSS JOIN "opinion"
   1.246 -              JOIN "direct_interest_snapshot" AS "snapshot"
   1.247 -              ON "snapshot"."issue_id" = "issue"."id"
   1.248 -              AND "snapshot"."event" = "issue"."latest_snapshot_event"
   1.249 -              AND "snapshot"."member_id" = "opinion"."member_id"
   1.250 -              WHERE "issue"."id" = "issue_id_p"
   1.251 -              AND "opinion"."suggestion_id" = "suggestion_id_v"
   1.252 -              AND "opinion"."degree" = 1
   1.253 -              AND "opinion"."fulfilled" = TRUE
   1.254 -            ),
   1.255 -            "plus2_unfulfilled_count" = (
   1.256 -              SELECT coalesce(sum("snapshot"."weight"), 0)
   1.257 -              FROM "issue" CROSS JOIN "opinion"
   1.258 -              JOIN "direct_interest_snapshot" AS "snapshot"
   1.259 -              ON "snapshot"."issue_id" = "issue"."id"
   1.260 -              AND "snapshot"."event" = "issue"."latest_snapshot_event"
   1.261 -              AND "snapshot"."member_id" = "opinion"."member_id"
   1.262 -              WHERE "issue"."id" = "issue_id_p"
   1.263 -              AND "opinion"."suggestion_id" = "suggestion_id_v"
   1.264 -              AND "opinion"."degree" = 2
   1.265 -              AND "opinion"."fulfilled" = FALSE
   1.266 -            ),
   1.267 -            "plus2_fulfilled_count" = (
   1.268 -              SELECT coalesce(sum("snapshot"."weight"), 0)
   1.269 -              FROM "issue" CROSS JOIN "opinion"
   1.270 -              JOIN "direct_interest_snapshot" AS "snapshot"
   1.271 -              ON "snapshot"."issue_id" = "issue"."id"
   1.272 -              AND "snapshot"."event" = "issue"."latest_snapshot_event"
   1.273 -              AND "snapshot"."member_id" = "opinion"."member_id"
   1.274 -              WHERE "issue"."id" = "issue_id_p"
   1.275 -              AND "opinion"."suggestion_id" = "suggestion_id_v"
   1.276 -              AND "opinion"."degree" = 2
   1.277 -              AND "opinion"."fulfilled" = TRUE
   1.278 -            )
   1.279 -            WHERE "suggestion"."id" = "suggestion_id_v";
   1.280 -        END LOOP;
   1.281 -        PERFORM "set_harmonic_suggestion_weights"("initiative_id_v");
   1.282 -      END LOOP;
   1.283 -      PERFORM "set_harmonic_initiative_weights"("issue_id_p");
   1.284 +        THEN
   1.285 +          -- NOTE: "issue_row" used later
   1.286 +          IF "issue_row"."accepted" ISNULL THEN
   1.287 +            "issue_row"."state" := 'canceled_revoked_before_accepted';
   1.288 +          ELSIF "issue_row"."half_frozen" ISNULL THEN
   1.289 +            "issue_row"."state" := 'canceled_after_revocation_during_discussion';
   1.290 +          ELSE
   1.291 +            "issue_row"."state" := 'canceled_after_revocation_during_verification';
   1.292 +          END IF;
   1.293 +          "issue_row"."closed" := now();
   1.294 +          UPDATE "issue" SET
   1.295 +            "state"  = "issue_row"."state",
   1.296 +            "closed" = "issue_row"."closed"
   1.297 +            WHERE "id" = "issue_row"."id";
   1.298 +        END IF;
   1.299 +        -- fully freeze issue after verification time:
   1.300 +        IF
   1.301 +          "issue_row"."half_frozen" NOTNULL AND
   1.302 +          "issue_row"."fully_frozen" ISNULL AND
   1.303 +          "issue_row"."closed" ISNULL AND
   1.304 +          now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
   1.305 +        THEN
   1.306 +          PERFORM "freeze_after_snapshot"("issue_id_p");
   1.307 +          -- NOTE: "issue" might change, thus "issue_row" has to be updated below
   1.308 +        END IF;
   1.309 +        SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
   1.310 +        -- close issue by calling close_voting(...) after voting time:
   1.311 +        IF
   1.312 +          "issue_row"."closed" ISNULL AND
   1.313 +          "issue_row"."fully_frozen" NOTNULL AND
   1.314 +          now() >= "issue_row"."fully_frozen" + "issue_row"."voting_time"
   1.315 +        THEN
   1.316 +          PERFORM "close_voting"("issue_id_p");
   1.317 +          -- calculate ranks will not consume much time and can be done now
   1.318 +          PERFORM "calculate_ranks"("issue_id_p");
   1.319 +        END IF;
   1.320 +        -- if a new shapshot has been created, then recalculate harmonic weights:
   1.321 +        IF "new_snapshot_v" THEN
   1.322 +          PERFORM "set_harmonic_initiative_weights"("issue_id_p");
   1.323 +          PERFORM "set_harmonic_suggestion_weights"("id")
   1.324 +            FROM "initiative" WHERE "issue_id" = "issue_id_p";
   1.325 +        END IF;
   1.326 +      END IF;
   1.327        RETURN;
   1.328      END;
   1.329    $$;