liquid_feedback_core

changeset 360:47965760b1b8

Update script to v2.2.1
author jbe
date Sun Mar 17 11:30:44 2013 +0100 (2013-03-17)
parents c37c0b4f2027
children 3bb60d324a22
files core.sql update/core-update.v2.2.0-v2.2.1.sql
line diff
     1.1 --- a/core.sql	Sun Mar 17 11:18:24 2013 +0100
     1.2 +++ b/core.sql	Sun Mar 17 11:30:44 2013 +0100
     1.3 @@ -2043,7 +2043,7 @@
     1.4  
     1.5  COMMENT ON VIEW "initiative_suggestion_order_calculation" IS 'Initiatives, where the "proportional_order" of its suggestions has to be calculated';
     1.6  
     1.7 -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.8 +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.9  
    1.10  
    1.11  CREATE VIEW "individual_suggestion_ranking" AS
     2.1 --- /dev/null	Thu Jan 01 00:00:00 1970 +0000
     2.2 +++ b/update/core-update.v2.2.0-v2.2.1.sql	Sun Mar 17 11:30:44 2013 +0100
     2.3 @@ -0,0 +1,53 @@
     2.4 +BEGIN;
     2.5 +
     2.6 +CREATE OR REPLACE VIEW "liquid_feedback_version" AS
     2.7 +  SELECT * FROM (VALUES ('2.2.1', 2, 2, 1))
     2.8 +  AS "subquery"("string", "major", "minor", "revision");
     2.9 +
    2.10 +ALTER TABLE "initiative" ADD COLUMN "final_suggestion_order_calculated" BOOLEAN NOT NULL DEFAULT FALSE;
    2.11 +COMMENT ON COLUMN "initiative"."final_suggestion_order_calculated" IS 'Set to TRUE, when "proportional_order" of suggestions has been calculated the last time';
    2.12 +
    2.13 +ALTER TABLE "suggestion" ADD COLUMN "proportional_order" INT4;
    2.14 +COMMENT ON COLUMN "suggestion"."proportional_order" IS 'To be used for sorting suggestions within an initiative; updated by "lf_update_suggestion_order"';
    2.15 +
    2.16 +CREATE VIEW "initiative_suggestion_order_calculation" AS
    2.17 +  SELECT
    2.18 +    "initiative"."id" AS "initiative_id",
    2.19 +    ("issue"."closed" NOTNULL OR "issue"."fully_frozen" NOTNULL) AS "final"
    2.20 +  FROM "initiative" JOIN "issue"
    2.21 +  ON "initiative"."issue_id" = "issue"."id"
    2.22 +  WHERE ("issue"."closed" ISNULL AND "issue"."fully_frozen" ISNULL)
    2.23 +  OR ("initiative"."final_suggestion_order_calculated" = FALSE);
    2.24 +COMMENT ON VIEW "initiative_suggestion_order_calculation" IS 'Initiatives, where the "proportional_order" of its suggestions has to be calculated';
    2.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';
    2.26 +
    2.27 +CREATE VIEW "individual_suggestion_ranking" AS
    2.28 +  SELECT
    2.29 +    "opinion"."initiative_id",
    2.30 +    "opinion"."member_id",
    2.31 +    "direct_interest_snapshot"."weight",
    2.32 +    CASE WHEN
    2.33 +      ("opinion"."degree" = 2 AND "opinion"."fulfilled" = FALSE) OR
    2.34 +      ("opinion"."degree" = -2 AND "opinion"."fulfilled" = TRUE)
    2.35 +    THEN 1 ELSE
    2.36 +      CASE WHEN
    2.37 +        ("opinion"."degree" = 1 AND "opinion"."fulfilled" = FALSE) OR
    2.38 +        ("opinion"."degree" = -1 AND "opinion"."fulfilled" = TRUE)
    2.39 +      THEN 2 ELSE
    2.40 +        CASE WHEN
    2.41 +          ("opinion"."degree" = 2 AND "opinion"."fulfilled" = TRUE) OR
    2.42 +          ("opinion"."degree" = -2 AND "opinion"."fulfilled" = FALSE)
    2.43 +        THEN 3 ELSE 4 END
    2.44 +      END
    2.45 +    END AS "preference",
    2.46 +    "opinion"."suggestion_id"
    2.47 +  FROM "opinion"
    2.48 +  JOIN "initiative" ON "initiative"."id" = "opinion"."initiative_id"
    2.49 +  JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
    2.50 +  JOIN "direct_interest_snapshot"
    2.51 +    ON  "direct_interest_snapshot"."issue_id" = "issue"."id"
    2.52 +    AND "direct_interest_snapshot"."event" = "issue"."latest_snapshot_event"
    2.53 +    AND "direct_interest_snapshot"."member_id" = "opinion"."member_id";
    2.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';
    2.55 +
    2.56 +COMMIT;

Impressum / About Us