1.1 --- /dev/null	Thu Jan 01 00:00:00 1970 +0000
     1.2 +++ b/update/core-update.v2.2.0-v2.2.1.sql	Sun Mar 17 11:30:44 2013 +0100
     1.3 @@ -0,0 +1,53 @@
     1.4 +BEGIN;
     1.5 +
     1.6 +CREATE OR REPLACE VIEW "liquid_feedback_version" AS
     1.7 +  SELECT * FROM (VALUES ('2.2.1', 2, 2, 1))
     1.8 +  AS "subquery"("string", "major", "minor", "revision");
     1.9 +
    1.10 +ALTER TABLE "initiative" ADD COLUMN "final_suggestion_order_calculated" BOOLEAN NOT NULL DEFAULT FALSE;
    1.11 +COMMENT ON COLUMN "initiative"."final_suggestion_order_calculated" IS 'Set to TRUE, when "proportional_order" of suggestions has been calculated the last time';
    1.12 +
    1.13 +ALTER TABLE "suggestion" ADD COLUMN "proportional_order" INT4;
    1.14 +COMMENT ON COLUMN "suggestion"."proportional_order" IS 'To be used for sorting suggestions within an initiative; updated by "lf_update_suggestion_order"';
    1.15 +
    1.16 +CREATE VIEW "initiative_suggestion_order_calculation" AS
    1.17 +  SELECT
    1.18 +    "initiative"."id" AS "initiative_id",
    1.19 +    ("issue"."closed" NOTNULL OR "issue"."fully_frozen" NOTNULL) AS "final"
    1.20 +  FROM "initiative" JOIN "issue"
    1.21 +  ON "initiative"."issue_id" = "issue"."id"
    1.22 +  WHERE ("issue"."closed" ISNULL AND "issue"."fully_frozen" ISNULL)
    1.23 +  OR ("initiative"."final_suggestion_order_calculated" = FALSE);
    1.24 +COMMENT ON VIEW "initiative_suggestion_order_calculation" IS 'Initiatives, where the "proportional_order" of its suggestions has to be calculated';
    1.25 +COMMENT ON COLUMN "initiative_suggestion_order_calculation"."final" IS 'Set to TRUE, if the issue is fully frozen or closed, and the calculation has to be done only once for one last time';
    1.26 +
    1.27 +CREATE VIEW "individual_suggestion_ranking" AS
    1.28 +  SELECT
    1.29 +    "opinion"."initiative_id",
    1.30 +    "opinion"."member_id",
    1.31 +    "direct_interest_snapshot"."weight",
    1.32 +    CASE WHEN
    1.33 +      ("opinion"."degree" = 2 AND "opinion"."fulfilled" = FALSE) OR
    1.34 +      ("opinion"."degree" = -2 AND "opinion"."fulfilled" = TRUE)
    1.35 +    THEN 1 ELSE
    1.36 +      CASE WHEN
    1.37 +        ("opinion"."degree" = 1 AND "opinion"."fulfilled" = FALSE) OR
    1.38 +        ("opinion"."degree" = -1 AND "opinion"."fulfilled" = TRUE)
    1.39 +      THEN 2 ELSE
    1.40 +        CASE WHEN
    1.41 +          ("opinion"."degree" = 2 AND "opinion"."fulfilled" = TRUE) OR
    1.42 +          ("opinion"."degree" = -2 AND "opinion"."fulfilled" = FALSE)
    1.43 +        THEN 3 ELSE 4 END
    1.44 +      END
    1.45 +    END AS "preference",
    1.46 +    "opinion"."suggestion_id"
    1.47 +  FROM "opinion"
    1.48 +  JOIN "initiative" ON "initiative"."id" = "opinion"."initiative_id"
    1.49 +  JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
    1.50 +  JOIN "direct_interest_snapshot"
    1.51 +    ON  "direct_interest_snapshot"."issue_id" = "issue"."id"
    1.52 +    AND "direct_interest_snapshot"."event" = "issue"."latest_snapshot_event"
    1.53 +    AND "direct_interest_snapshot"."member_id" = "opinion"."member_id";
    1.54 +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.55 +
    1.56 +COMMIT;