1.1 --- a/core.sql	Sat May 28 03:34:20 2011 +0200
     1.2 +++ b/core.sql	Sat May 28 04:19:30 2011 +0200
     1.3 @@ -310,7 +310,8 @@
     1.4          "initiative_quorum_den" INT4            NOT NULL,
     1.5          "majority_num"          INT4            NOT NULL DEFAULT 1,
     1.6          "majority_den"          INT4            NOT NULL DEFAULT 2,
     1.7 -        "majority_strict"       BOOLEAN         NOT NULL DEFAULT TRUE );
     1.8 +        "majority_strict"       BOOLEAN         NOT NULL DEFAULT TRUE,
     1.9 +        "majority_indirect"     BOOLEAN         NOT NULL DEFAULT TRUE );
    1.10  CREATE INDEX "policy_active_idx" ON "policy" ("active");
    1.11  
    1.12  COMMENT ON TABLE "policy" IS 'Policies for a particular proceeding type (timelimits, quorum)';
    1.13 @@ -328,6 +329,7 @@
    1.14  COMMENT ON COLUMN "policy"."majority_num"          IS   'Numerator of fraction of majority to be reached during voting by an initiative to be aggreed upon';
    1.15  COMMENT ON COLUMN "policy"."majority_den"          IS 'Denominator of fraction of majority to be reached during voting by an initiative to be aggreed upon';
    1.16  COMMENT ON COLUMN "policy"."majority_strict"       IS 'If TRUE, then the majority must be strictly greater than "majority_num"/"majority_den", otherwise it may also be equal.';
    1.17 +COMMENT ON COLUMN "policy"."majority_indirect"     IS 'If TRUE, then also indirect majorities (though beat paths) are taken into account, when an initiative has at least as many "positive_votes" as "negative_votes".'
    1.18  
    1.19  
    1.20  CREATE TABLE "unit" (
    1.21 @@ -3444,6 +3446,7 @@
    1.22        PERFORM "lock_issue"("issue_id_p");
    1.23        SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";
    1.24        SELECT "unit_id" INTO "unit_id_v" FROM "area"  WHERE "id" = "area_id_v";
    1.25 +      -- consider delegations and auto-reject:
    1.26        DELETE FROM "delegating_voter"
    1.27          WHERE "issue_id" = "issue_id_p";
    1.28        DELETE FROM "direct_voter"
    1.29 @@ -3521,6 +3524,7 @@
    1.30            FROM "initiative" WHERE "issue_id" = "issue_id_p";
    1.31        END LOOP;
    1.32        PERFORM "add_vote_delegations"("issue_id_p");
    1.33 +      -- set voter count and mark issue as being calculated:
    1.34        UPDATE "issue" SET
    1.35          "state"  = 'calculation',
    1.36          "closed" = now(),
    1.37 @@ -3529,6 +3533,8 @@
    1.38            FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
    1.39          )
    1.40          WHERE "id" = "issue_id_p";
    1.41 +      -- calculate "positive_votes" and "negative_votes"
    1.42 +      -- and set "attainable" flag without regarding indirect majorities:
    1.43        UPDATE "initiative" SET
    1.44          "positive_votes" = "vote_counts"."positive_votes",
    1.45          "negative_votes" = "vote_counts"."negative_votes",
    1.46 @@ -3573,6 +3579,7 @@
    1.47          WHERE "vote_counts"."initiative_id" = "initiative"."id"
    1.48          AND "issue"."id" = "initiative"."issue_id"
    1.49          AND "policy"."id" = "issue"."policy_id";
    1.50 +      -- materialize battle_view:
    1.51        -- NOTE: "closed" column of issue must be set at this point
    1.52        DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
    1.53        INSERT INTO "battle" (
    1.54 @@ -3584,6 +3591,46 @@
    1.55          "winning_initiative_id", "losing_initiative_id",
    1.56          "count"
    1.57          FROM "battle_view" WHERE "issue_id" = "issue_id_p";
    1.58 +      -- take indirect majorities into account,
    1.59 +      -- if "policy"."majority_indirect" = TRUE:
    1.60 +      LOOP
    1.61 +        UPDATE "initiative" SET "attainable" = TRUE
    1.62 +          FROM (
    1.63 +            SELECT "new_initiative"."id" AS "initiative_id"
    1.64 +            FROM "issue"
    1.65 +            JOIN "policy" ON "issue"."policy_id" = "policy"."id"
    1.66 +            JOIN "initiative" "old_initiative"
    1.67 +              ON "old_initiative"."issue_id" = "issue_id_p"
    1.68 +              AND "old_initiative"."admitted"
    1.69 +              AND "old_initiative"."attainable"
    1.70 +            JOIN "initiative" "new_initiative"
    1.71 +              ON "new_initiative"."issue_id" = "issue_id_p"
    1.72 +              AND "new_initiative"."admitted"
    1.73 +              AND NOT "new_initiative"."attainable"
    1.74 +              AND "new_initiative"."positive_votes" >= "new_initiative"."negative_votes"
    1.75 +            JOIN "battle" "battle_win"
    1.76 +              ON "battle_win"."issue_id" = "issue_id_p"
    1.77 +              AND "battle_win"."winning_initiative_id" = "new_initiative"."id"
    1.78 +              AND "battle_win"."losing_initiative_id" = "old_initiative"."id"
    1.79 +            JOIN "battle" "battle_lose"
    1.80 +              ON "battle_lose"."issue_id" = "issue_id_p"
    1.81 +              AND "battle_lose"."losing_initiative_id" = "new_initiative"."id"
    1.82 +              AND "battle_lose"."winning_initiative_id" = "old_initiative"."id"
    1.83 +            WHERE "issue"."id" = "issue_id_p"
    1.84 +            AND "policy"."majority_indirect"
    1.85 +            AND CASE WHEN "policy"."majority_strict" THEN
    1.86 +              "battle_win"."count" * "policy"."majority_den" >
    1.87 +              "policy"."majority_num" *
    1.88 +              ("battle_win"."count"+"battle_lose"."count")
    1.89 +            ELSE
    1.90 +              "battle_win"."count" * "policy"."majority_den" >=
    1.91 +              "policy"."majority_num" *
    1.92 +              ("battle_win"."count"+"battle_lose"."count")
    1.93 +            END
    1.94 +          ) AS "subquery"
    1.95 +          WHERE "id" = "subquery"."initiative_id";
    1.96 +        EXIT WHEN NOT FOUND;
    1.97 +      END LOOP;
    1.98      END;
    1.99    $$;
   1.100