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;

Impressum / About Us