1.1 --- a/core.sql	Sun Mar 10 19:50:54 2013 +0100
     1.2 +++ b/core.sql	Sat Mar 16 17:22:01 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.2.0', 2, 2, 0))
     1.8 +  SELECT * FROM (VALUES ('2.2.1', 2, 2, 1))
     1.9    AS "subquery"("string", "major", "minor", "revision");
    1.10  
    1.11  
    1.12 @@ -610,6 +610,7 @@
    1.13          "satisfied_supporter_count"          INT4,
    1.14          "satisfied_informed_supporter_count" INT4,
    1.15          "harmonic_weight"       NUMERIC(12, 3),
    1.16 +        "final_suggestion_order_calculated" BOOLEAN NOT NULL DEFAULT FALSE,
    1.17          "positive_votes"        INT4,
    1.18          "negative_votes"        INT4,
    1.19          "direct_majority"       BOOLEAN,
    1.20 @@ -666,6 +667,7 @@
    1.21  COMMENT ON COLUMN "initiative"."satisfied_supporter_count"          IS 'Calculated from table "direct_supporter_snapshot"';
    1.22  COMMENT ON COLUMN "initiative"."satisfied_informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
    1.23  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';
    1.24 +COMMENT ON COLUMN "initiative"."final_suggestion_order_calculated" IS 'Set to TRUE, when "proportional_order" of suggestions has been calculated the last time';
    1.25  COMMENT ON COLUMN "initiative"."positive_votes"         IS 'Calculated from table "direct_voter"';
    1.26  COMMENT ON COLUMN "initiative"."negative_votes"         IS 'Calculated from table "direct_voter"';
    1.27  COMMENT ON COLUMN "initiative"."direct_majority"        IS 'TRUE, if "positive_votes"/("positive_votes"+"negative_votes") is strictly greater or greater-equal than "direct_majority_num"/"direct_majority_den", and "positive_votes" is greater-equal than "direct_majority_positive", and ("positive_votes"+abstentions) is greater-equal than "direct_majority_non_negative"';
    1.28 @@ -768,7 +770,8 @@
    1.29          "plus1_unfulfilled_count"  INT4,
    1.30          "plus1_fulfilled_count"    INT4,
    1.31          "plus2_unfulfilled_count"  INT4,
    1.32 -        "plus2_fulfilled_count"    INT4 );
    1.33 +        "plus2_fulfilled_count"    INT4,
    1.34 +        "proportional_order"    INT4 );
    1.35  CREATE INDEX "suggestion_created_idx" ON "suggestion" ("created");
    1.36  CREATE INDEX "suggestion_author_id_created_idx" ON "suggestion" ("author_id", "created");
    1.37  CREATE INDEX "suggestion_text_search_data_idx" ON "suggestion" USING gin ("text_search_data");
    1.38 @@ -789,6 +792,7 @@
    1.39  COMMENT ON COLUMN "suggestion"."plus1_fulfilled_count"    IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
    1.40  COMMENT ON COLUMN "suggestion"."plus2_unfulfilled_count"  IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
    1.41  COMMENT ON COLUMN "suggestion"."plus2_fulfilled_count"    IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
    1.42 +COMMENT ON COLUMN "suggestion"."proportional_order"       IS 'To be used for sorting suggestions within an initiative; updated by "lf_update_suggestion_order"';
    1.43  
    1.44  
    1.45  CREATE TABLE "rendered_suggestion" (
    1.46 @@ -2028,6 +2032,51 @@
    1.47  COMMENT ON VIEW "critical_opinion" IS 'Opinions currently causing dissatisfaction';
    1.48  
    1.49  
    1.50 +CREATE VIEW "initiative_suggestion_order_calculation" AS
    1.51 +  SELECT
    1.52 +    "initiative"."id" AS "initiative_id",
    1.53 +    ("issue"."closed" NOTNULL OR "issue"."fully_frozen" NOTNULL) AS "final"
    1.54 +  FROM "initiative" JOIN "issue"
    1.55 +  ON "initiative"."issue_id" = "issue"."id"
    1.56 +  WHERE ("issue"."closed" ISNULL AND "issue"."fully_frozen" ISNULL)
    1.57 +  OR ("initiative"."final_suggestion_order_calculated" = FALSE);
    1.58 +
    1.59 +COMMENT ON VIEW "initiative_suggestion_order_calculation" IS 'Initiatives, where the "proportional_order" of its suggestions has to be calculated';
    1.60 +
    1.61 +COMMENT ON COLUMN "initiative_suggestion_order_calculation"."final" IS 'Set to TRUE, if the issue is fully frozen or closed, and the calculation has only be done for one last time';
    1.62 +
    1.63 +
    1.64 +CREATE VIEW "individual_suggestion_ranking" AS
    1.65 +  SELECT
    1.66 +    "opinion"."initiative_id",
    1.67 +    "opinion"."member_id",
    1.68 +    "direct_interest_snapshot"."weight",
    1.69 +    CASE WHEN
    1.70 +      ("opinion"."degree" = 2 AND "opinion"."fulfilled" = FALSE) OR
    1.71 +      ("opinion"."degree" = -2 AND "opinion"."fulfilled" = TRUE)
    1.72 +    THEN 1 ELSE
    1.73 +      CASE WHEN
    1.74 +        ("opinion"."degree" = 1 AND "opinion"."fulfilled" = FALSE) OR
    1.75 +        ("opinion"."degree" = -1 AND "opinion"."fulfilled" = TRUE)
    1.76 +      THEN 2 ELSE
    1.77 +        CASE WHEN
    1.78 +          ("opinion"."degree" = 2 AND "opinion"."fulfilled" = TRUE) OR
    1.79 +          ("opinion"."degree" = -2 AND "opinion"."fulfilled" = FALSE)
    1.80 +        THEN 3 ELSE 4 END
    1.81 +      END
    1.82 +    END AS "preference",
    1.83 +    "opinion"."suggestion_id"
    1.84 +  FROM "opinion"
    1.85 +  JOIN "initiative" ON "initiative"."id" = "opinion"."initiative_id"
    1.86 +  JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
    1.87 +  JOIN "direct_interest_snapshot"
    1.88 +    ON  "direct_interest_snapshot"."issue_id" = "issue"."id"
    1.89 +    AND "direct_interest_snapshot"."event" = "issue"."latest_snapshot_event"
    1.90 +    AND "direct_interest_snapshot"."member_id" = "opinion"."member_id";
    1.91 +
    1.92 +COMMENT ON VIEW "individual_suggestion_ranking" IS 'Helper view for "lf_update_suggestion_order" to allow a proportional ordering of suggestions within an initiative';
    1.93 +
    1.94 +
    1.95  CREATE VIEW "battle_participant" AS
    1.96      SELECT "initiative"."id", "initiative"."issue_id"
    1.97      FROM "issue" JOIN "initiative"