1.1 --- /dev/null	Thu Jan 01 00:00:00 1970 +0000
     1.2 +++ b/update/core-update.v2.2.6-v3.0.1.sql	Sat Apr 05 23:04:43 2014 +0200
     1.3 @@ -0,0 +1,339 @@
     1.4 +BEGIN;
     1.5 +
     1.6 +CREATE OR REPLACE VIEW "liquid_feedback_version" AS
     1.7 +  SELECT * FROM (VALUES ('3.0.1', 3, 0, 1))
     1.8 +  AS "subquery"("string", "major", "minor", "revision");
     1.9 +
    1.10 +CREATE TABLE "issue_order_in_admission_state" (
    1.11 +        "id"                    INT8            PRIMARY KEY, --REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    1.12 +        "order_in_area"         INT4,
    1.13 +        "order_in_unit"         INT4 );
    1.14 +
    1.15 +COMMENT ON TABLE "issue_order_in_admission_state" IS 'Ordering information for issues that are not stored in the "issue" table to avoid locking of multiple issues at once; Filled/updated by "lf_update_issue_order"';
    1.16 +
    1.17 +COMMENT ON COLUMN "issue_order_in_admission_state"."id"            IS 'References "issue" ("id") but has no referential integrity trigger associated, due to performance/locking issues';
    1.18 +COMMENT ON COLUMN "issue_order_in_admission_state"."order_in_area" IS 'Order of issues in admission state within a single area; NULL values sort last';
    1.19 +COMMENT ON COLUMN "issue_order_in_admission_state"."order_in_unit" IS 'Order of issues in admission state within all areas of a unit; NULL values sort last';
    1.20 +
    1.21 +CREATE VIEW "issue_supporter_in_admission_state" AS
    1.22 +  SELECT DISTINCT
    1.23 +    "area"."unit_id",
    1.24 +    "issue"."area_id",
    1.25 +    "issue"."id" AS "issue_id",
    1.26 +    "supporter"."member_id",
    1.27 +    "direct_interest_snapshot"."weight"
    1.28 +  FROM "issue"
    1.29 +  JOIN "area" ON "area"."id" = "issue"."area_id"
    1.30 +  JOIN "supporter" ON "supporter"."issue_id" = "issue"."id"
    1.31 +  JOIN "direct_interest_snapshot"
    1.32 +    ON  "direct_interest_snapshot"."issue_id" = "issue"."id"
    1.33 +    AND "direct_interest_snapshot"."event" = "issue"."latest_snapshot_event"
    1.34 +    AND "direct_interest_snapshot"."member_id" = "supporter"."member_id"
    1.35 +  WHERE "issue"."state" = 'admission'::"issue_state";
    1.36 +
    1.37 +COMMENT ON VIEW "issue_supporter_in_admission_state" IS 'Helper view for "lf_update_issue_order" to allow a (proportional) ordering of issues within an area';
    1.38 +
    1.39 +COMMENT ON COLUMN "initiative"."schulze_rank"           IS 'Schulze-Ranking';
    1.40 +COMMENT ON COLUMN "initiative"."better_than_status_quo" IS 'TRUE, if initiative has a schulze-ranking better than the status quo';
    1.41 +COMMENT ON COLUMN "initiative"."worse_than_status_quo"  IS 'TRUE, if initiative has a schulze-ranking worse than the status quo (DEPRECATED, since schulze-ranking is unique per issue; use "better_than_status_quo"=FALSE)';
    1.42 +COMMENT ON COLUMN "initiative"."winner"                 IS 'Winner is the "eligible" initiative with best "schulze_rank"';
    1.43 +
    1.44 +CREATE OR REPLACE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE)
    1.45 +  RETURNS VOID
    1.46 +  LANGUAGE 'plpgsql' VOLATILE AS $$
    1.47 +    DECLARE
    1.48 +      "issue_row"         "issue"%ROWTYPE;
    1.49 +      "policy_row"        "policy"%ROWTYPE;
    1.50 +      "dimension_v"       INTEGER;
    1.51 +      "vote_matrix"       INT4[][];  -- absolute votes
    1.52 +      "matrix"            INT8[][];  -- defeat strength / best paths
    1.53 +      "i"                 INTEGER;
    1.54 +      "j"                 INTEGER;
    1.55 +      "k"                 INTEGER;
    1.56 +      "battle_row"        "battle"%ROWTYPE;
    1.57 +      "rank_ary"          INT4[];
    1.58 +      "rank_v"            INT4;
    1.59 +      "initiative_id_v"   "initiative"."id"%TYPE;
    1.60 +    BEGIN
    1.61 +      PERFORM "require_transaction_isolation"();
    1.62 +      SELECT * INTO "issue_row"
    1.63 +        FROM "issue" WHERE "id" = "issue_id_p";
    1.64 +      SELECT * INTO "policy_row"
    1.65 +        FROM "policy" WHERE "id" = "issue_row"."policy_id";
    1.66 +      SELECT count(1) INTO "dimension_v"
    1.67 +        FROM "battle_participant" WHERE "issue_id" = "issue_id_p";
    1.68 +      -- Create "vote_matrix" with absolute number of votes in pairwise
    1.69 +      -- comparison:
    1.70 +      "vote_matrix" := array_fill(NULL::INT4, ARRAY["dimension_v", "dimension_v"]);
    1.71 +      "i" := 1;
    1.72 +      "j" := 2;
    1.73 +      FOR "battle_row" IN
    1.74 +        SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p"
    1.75 +        ORDER BY
    1.76 +        "winning_initiative_id" NULLS FIRST,
    1.77 +        "losing_initiative_id" NULLS FIRST
    1.78 +      LOOP
    1.79 +        "vote_matrix"["i"]["j"] := "battle_row"."count";
    1.80 +        IF "j" = "dimension_v" THEN
    1.81 +          "i" := "i" + 1;
    1.82 +          "j" := 1;
    1.83 +        ELSE
    1.84 +          "j" := "j" + 1;
    1.85 +          IF "j" = "i" THEN
    1.86 +            "j" := "j" + 1;
    1.87 +          END IF;
    1.88 +        END IF;
    1.89 +      END LOOP;
    1.90 +      IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN
    1.91 +        RAISE EXCEPTION 'Wrong battle count (should not happen)';
    1.92 +      END IF;
    1.93 +      -- Store defeat strengths in "matrix" using "defeat_strength"
    1.94 +      -- function:
    1.95 +      "matrix" := array_fill(NULL::INT8, ARRAY["dimension_v", "dimension_v"]);
    1.96 +      "i" := 1;
    1.97 +      LOOP
    1.98 +        "j" := 1;
    1.99 +        LOOP
   1.100 +          IF "i" != "j" THEN
   1.101 +            "matrix"["i"]["j"] := "defeat_strength"(
   1.102 +              "vote_matrix"["i"]["j"],
   1.103 +              "vote_matrix"["j"]["i"]
   1.104 +            );
   1.105 +          END IF;
   1.106 +          EXIT WHEN "j" = "dimension_v";
   1.107 +          "j" := "j" + 1;
   1.108 +        END LOOP;
   1.109 +        EXIT WHEN "i" = "dimension_v";
   1.110 +        "i" := "i" + 1;
   1.111 +      END LOOP;
   1.112 +      -- Find best paths:
   1.113 +      "i" := 1;
   1.114 +      LOOP
   1.115 +        "j" := 1;
   1.116 +        LOOP
   1.117 +          IF "i" != "j" THEN
   1.118 +            "k" := 1;
   1.119 +            LOOP
   1.120 +              IF "i" != "k" AND "j" != "k" THEN
   1.121 +                IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN
   1.122 +                  IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN
   1.123 +                    "matrix"["j"]["k"] := "matrix"["j"]["i"];
   1.124 +                  END IF;
   1.125 +                ELSE
   1.126 +                  IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN
   1.127 +                    "matrix"["j"]["k"] := "matrix"["i"]["k"];
   1.128 +                  END IF;
   1.129 +                END IF;
   1.130 +              END IF;
   1.131 +              EXIT WHEN "k" = "dimension_v";
   1.132 +              "k" := "k" + 1;
   1.133 +            END LOOP;
   1.134 +          END IF;
   1.135 +          EXIT WHEN "j" = "dimension_v";
   1.136 +          "j" := "j" + 1;
   1.137 +        END LOOP;
   1.138 +        EXIT WHEN "i" = "dimension_v";
   1.139 +        "i" := "i" + 1;
   1.140 +      END LOOP;
   1.141 +      -- Determine order of winners:
   1.142 +      "rank_ary" := array_fill(NULL::INT4, ARRAY["dimension_v"]);
   1.143 +      "rank_v" := 1;
   1.144 +      LOOP
   1.145 +        "i" := 1;
   1.146 +        LOOP
   1.147 +          IF "rank_ary"["i"] ISNULL THEN
   1.148 +            "j" := 1;
   1.149 +            LOOP
   1.150 +              IF
   1.151 +                "i" != "j" AND
   1.152 +                "rank_ary"["j"] ISNULL AND
   1.153 +                ( "matrix"["j"]["i"] > "matrix"["i"]["j"] OR
   1.154 +                  -- tie-breaking by "id"
   1.155 +                  ( "matrix"["j"]["i"] = "matrix"["i"]["j"] AND
   1.156 +                    "j" < "i" ) )
   1.157 +              THEN
   1.158 +                -- someone else is better
   1.159 +                EXIT;
   1.160 +              END IF;
   1.161 +              "j" := "j" + 1;
   1.162 +              IF "j" = "dimension_v" + 1 THEN
   1.163 +                -- noone is better
   1.164 +                "rank_ary"["i"] := "rank_v";
   1.165 +                EXIT;
   1.166 +              END IF;
   1.167 +            END LOOP;
   1.168 +            EXIT WHEN "j" = "dimension_v" + 1;
   1.169 +          END IF;
   1.170 +          "i" := "i" + 1;
   1.171 +          IF "i" > "dimension_v" THEN
   1.172 +            RAISE EXCEPTION 'Schulze ranking does not compute (should not happen)';
   1.173 +          END IF;
   1.174 +        END LOOP;
   1.175 +        EXIT WHEN "rank_v" = "dimension_v";
   1.176 +        "rank_v" := "rank_v" + 1;
   1.177 +      END LOOP;
   1.178 +      -- write preliminary results:
   1.179 +      "i" := 2;  -- omit status quo with "i" = 1
   1.180 +      FOR "initiative_id_v" IN
   1.181 +        SELECT "id" FROM "initiative"
   1.182 +        WHERE "issue_id" = "issue_id_p" AND "admitted"
   1.183 +        ORDER BY "id"
   1.184 +      LOOP
   1.185 +        UPDATE "initiative" SET
   1.186 +          "direct_majority" =
   1.187 +            CASE WHEN "policy_row"."direct_majority_strict" THEN
   1.188 +              "positive_votes" * "policy_row"."direct_majority_den" >
   1.189 +              "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")
   1.190 +            ELSE
   1.191 +              "positive_votes" * "policy_row"."direct_majority_den" >=
   1.192 +              "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")
   1.193 +            END
   1.194 +            AND "positive_votes" >= "policy_row"."direct_majority_positive"
   1.195 +            AND "issue_row"."voter_count"-"negative_votes" >=
   1.196 +                "policy_row"."direct_majority_non_negative",
   1.197 +            "indirect_majority" =
   1.198 +            CASE WHEN "policy_row"."indirect_majority_strict" THEN
   1.199 +              "positive_votes" * "policy_row"."indirect_majority_den" >
   1.200 +              "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")
   1.201 +            ELSE
   1.202 +              "positive_votes" * "policy_row"."indirect_majority_den" >=
   1.203 +              "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")
   1.204 +            END
   1.205 +            AND "positive_votes" >= "policy_row"."indirect_majority_positive"
   1.206 +            AND "issue_row"."voter_count"-"negative_votes" >=
   1.207 +                "policy_row"."indirect_majority_non_negative",
   1.208 +          "schulze_rank"           = "rank_ary"["i"],
   1.209 +          "better_than_status_quo" = "rank_ary"["i"] < "rank_ary"[1],
   1.210 +          "worse_than_status_quo"  = "rank_ary"["i"] > "rank_ary"[1],
   1.211 +          "multistage_majority"    = "rank_ary"["i"] >= "rank_ary"[1],
   1.212 +          "reverse_beat_path"      = "matrix"[1]["i"] >= 0,
   1.213 +          "eligible"               = FALSE,
   1.214 +          "winner"                 = FALSE,
   1.215 +          "rank"                   = NULL  -- NOTE: in cases of manual reset of issue state
   1.216 +          WHERE "id" = "initiative_id_v";
   1.217 +        "i" := "i" + 1;
   1.218 +      END LOOP;
   1.219 +      IF "i" != "dimension_v" + 1 THEN
   1.220 +        RAISE EXCEPTION 'Wrong winner count (should not happen)';
   1.221 +      END IF;
   1.222 +      -- take indirect majorities into account:
   1.223 +      LOOP
   1.224 +        UPDATE "initiative" SET "indirect_majority" = TRUE
   1.225 +          FROM (
   1.226 +            SELECT "new_initiative"."id" AS "initiative_id"
   1.227 +            FROM "initiative" "old_initiative"
   1.228 +            JOIN "initiative" "new_initiative"
   1.229 +              ON "new_initiative"."issue_id" = "issue_id_p"
   1.230 +              AND "new_initiative"."indirect_majority" = FALSE
   1.231 +            JOIN "battle" "battle_win"
   1.232 +              ON "battle_win"."issue_id" = "issue_id_p"
   1.233 +              AND "battle_win"."winning_initiative_id" = "new_initiative"."id"
   1.234 +              AND "battle_win"."losing_initiative_id" = "old_initiative"."id"
   1.235 +            JOIN "battle" "battle_lose"
   1.236 +              ON "battle_lose"."issue_id" = "issue_id_p"
   1.237 +              AND "battle_lose"."losing_initiative_id" = "new_initiative"."id"
   1.238 +              AND "battle_lose"."winning_initiative_id" = "old_initiative"."id"
   1.239 +            WHERE "old_initiative"."issue_id" = "issue_id_p"
   1.240 +            AND "old_initiative"."indirect_majority" = TRUE
   1.241 +            AND CASE WHEN "policy_row"."indirect_majority_strict" THEN
   1.242 +              "battle_win"."count" * "policy_row"."indirect_majority_den" >
   1.243 +              "policy_row"."indirect_majority_num" *
   1.244 +              ("battle_win"."count"+"battle_lose"."count")
   1.245 +            ELSE
   1.246 +              "battle_win"."count" * "policy_row"."indirect_majority_den" >=
   1.247 +              "policy_row"."indirect_majority_num" *
   1.248 +              ("battle_win"."count"+"battle_lose"."count")
   1.249 +            END
   1.250 +            AND "battle_win"."count" >= "policy_row"."indirect_majority_positive"
   1.251 +            AND "issue_row"."voter_count"-"battle_lose"."count" >=
   1.252 +                "policy_row"."indirect_majority_non_negative"
   1.253 +          ) AS "subquery"
   1.254 +          WHERE "id" = "subquery"."initiative_id";
   1.255 +        EXIT WHEN NOT FOUND;
   1.256 +      END LOOP;
   1.257 +      -- set "multistage_majority" for remaining matching initiatives:
   1.258 +      UPDATE "initiative" SET "multistage_majority" = TRUE
   1.259 +        FROM (
   1.260 +          SELECT "losing_initiative"."id" AS "initiative_id"
   1.261 +          FROM "initiative" "losing_initiative"
   1.262 +          JOIN "initiative" "winning_initiative"
   1.263 +            ON "winning_initiative"."issue_id" = "issue_id_p"
   1.264 +            AND "winning_initiative"."admitted"
   1.265 +          JOIN "battle" "battle_win"
   1.266 +            ON "battle_win"."issue_id" = "issue_id_p"
   1.267 +            AND "battle_win"."winning_initiative_id" = "winning_initiative"."id"
   1.268 +            AND "battle_win"."losing_initiative_id" = "losing_initiative"."id"
   1.269 +          JOIN "battle" "battle_lose"
   1.270 +            ON "battle_lose"."issue_id" = "issue_id_p"
   1.271 +            AND "battle_lose"."losing_initiative_id" = "winning_initiative"."id"
   1.272 +            AND "battle_lose"."winning_initiative_id" = "losing_initiative"."id"
   1.273 +          WHERE "losing_initiative"."issue_id" = "issue_id_p"
   1.274 +          AND "losing_initiative"."admitted"
   1.275 +          AND "winning_initiative"."schulze_rank" <
   1.276 +              "losing_initiative"."schulze_rank"
   1.277 +          AND "battle_win"."count" > "battle_lose"."count"
   1.278 +          AND (
   1.279 +            "battle_win"."count" > "winning_initiative"."positive_votes" OR
   1.280 +            "battle_lose"."count" < "losing_initiative"."negative_votes" )
   1.281 +        ) AS "subquery"
   1.282 +        WHERE "id" = "subquery"."initiative_id";
   1.283 +      -- mark eligible initiatives:
   1.284 +      UPDATE "initiative" SET "eligible" = TRUE
   1.285 +        WHERE "issue_id" = "issue_id_p"
   1.286 +        AND "initiative"."direct_majority"
   1.287 +        AND "initiative"."indirect_majority"
   1.288 +        AND "initiative"."better_than_status_quo"
   1.289 +        AND (
   1.290 +          "policy_row"."no_multistage_majority" = FALSE OR
   1.291 +          "initiative"."multistage_majority" = FALSE )
   1.292 +        AND (
   1.293 +          "policy_row"."no_reverse_beat_path" = FALSE OR
   1.294 +          "initiative"."reverse_beat_path" = FALSE );
   1.295 +      -- mark final winner:
   1.296 +      UPDATE "initiative" SET "winner" = TRUE
   1.297 +        FROM (
   1.298 +          SELECT "id" AS "initiative_id"
   1.299 +          FROM "initiative"
   1.300 +          WHERE "issue_id" = "issue_id_p" AND "eligible"
   1.301 +          ORDER BY
   1.302 +            "schulze_rank",
   1.303 +            "id"
   1.304 +          LIMIT 1
   1.305 +        ) AS "subquery"
   1.306 +        WHERE "id" = "subquery"."initiative_id";
   1.307 +      -- write (final) ranks:
   1.308 +      "rank_v" := 1;
   1.309 +      FOR "initiative_id_v" IN
   1.310 +        SELECT "id"
   1.311 +        FROM "initiative"
   1.312 +        WHERE "issue_id" = "issue_id_p" AND "admitted"
   1.313 +        ORDER BY
   1.314 +          "winner" DESC,
   1.315 +          "eligible" DESC,
   1.316 +          "schulze_rank",
   1.317 +          "id"
   1.318 +      LOOP
   1.319 +        UPDATE "initiative" SET "rank" = "rank_v"
   1.320 +          WHERE "id" = "initiative_id_v";
   1.321 +        "rank_v" := "rank_v" + 1;
   1.322 +      END LOOP;
   1.323 +      -- set schulze rank of status quo and mark issue as finished:
   1.324 +      UPDATE "issue" SET
   1.325 +        "status_quo_schulze_rank" = "rank_ary"[1],
   1.326 +        "state" =
   1.327 +          CASE WHEN EXISTS (
   1.328 +            SELECT NULL FROM "initiative"
   1.329 +            WHERE "issue_id" = "issue_id_p" AND "winner"
   1.330 +          ) THEN
   1.331 +            'finished_with_winner'::"issue_state"
   1.332 +          ELSE
   1.333 +            'finished_without_winner'::"issue_state"
   1.334 +          END,
   1.335 +        "closed" = "phase_finished",
   1.336 +        "phase_finished" = NULL
   1.337 +        WHERE "id" = "issue_id_p";
   1.338 +      RETURN;
   1.339 +    END;
   1.340 +  $$;
   1.341 +
   1.342 +COMMIT;