liquid_feedback_core

changeset 102:2daa8ce3d743

Update script added
author jbe
date Tue Jan 04 02:55:01 2011 +0100 (2011-01-04)
parents 9b18ee4682c7
children bc8aa59b0945
files update/core-update.v1.3.0-v1.3.1.sql
line diff
     1.1 --- /dev/null	Thu Jan 01 00:00:00 1970 +0000
     1.2 +++ b/update/core-update.v1.3.0-v1.3.1.sql	Tue Jan 04 02:55:01 2011 +0100
     1.3 @@ -0,0 +1,83 @@
     1.4 +BEGIN;
     1.5 + 
     1.6 +CREATE OR REPLACE VIEW "liquid_feedback_version" AS
     1.7 +  SELECT * FROM (VALUES ('1.3.1', 1, 3, 1))
     1.8 +  AS "subquery"("string", "major", "minor", "revision");
     1.9 +
    1.10 +ALTER TABLE "member" ADD COLUMN "locked" BOOLEAN NOT NULL DEFAULT FALSE;
    1.11 +
    1.12 +COMMENT ON COLUMN "member"."locked" IS 'Locked members can not log in.';
    1.13 +COMMENT ON COLUMN "member"."active" IS 'Memberships, support and votes are taken into account when corresponding members are marked as active. When the user does not log in for an extended period of time, this flag may be set to FALSE. If the user is not locked, he/she may reset the active flag by logging in.';
    1.14 +
    1.15 +CREATE OR REPLACE FUNCTION "create_interest_snapshot"
    1.16 +  ( "issue_id_p" "issue"."id"%TYPE )
    1.17 +  RETURNS VOID
    1.18 +  LANGUAGE 'plpgsql' VOLATILE AS $$
    1.19 +    DECLARE
    1.20 +      "member_id_v" "member"."id"%TYPE;
    1.21 +    BEGIN
    1.22 +      DELETE FROM "direct_interest_snapshot"
    1.23 +        WHERE "issue_id" = "issue_id_p"
    1.24 +        AND "event" = 'periodic';
    1.25 +      DELETE FROM "delegating_interest_snapshot"
    1.26 +        WHERE "issue_id" = "issue_id_p"
    1.27 +        AND "event" = 'periodic';
    1.28 +      DELETE FROM "direct_supporter_snapshot"
    1.29 +        WHERE "issue_id" = "issue_id_p"
    1.30 +        AND "event" = 'periodic';
    1.31 +      INSERT INTO "direct_interest_snapshot"
    1.32 +        ("issue_id", "event", "member_id", "voting_requested")
    1.33 +        SELECT
    1.34 +          "issue_id_p"  AS "issue_id",
    1.35 +          'periodic'    AS "event",
    1.36 +          "member"."id" AS "member_id",
    1.37 +          "interest"."voting_requested"
    1.38 +        FROM "interest" JOIN "member"
    1.39 +        ON "interest"."member_id" = "member"."id"
    1.40 +        WHERE "interest"."issue_id" = "issue_id_p"
    1.41 +        AND "member"."active";
    1.42 +      FOR "member_id_v" IN
    1.43 +        SELECT "member_id" FROM "direct_interest_snapshot"
    1.44 +        WHERE "issue_id" = "issue_id_p"
    1.45 +        AND "event" = 'periodic'
    1.46 +      LOOP
    1.47 +        UPDATE "direct_interest_snapshot" SET
    1.48 +          "weight" = 1 +
    1.49 +            "weight_of_added_delegations_for_interest_snapshot"(
    1.50 +              "issue_id_p",
    1.51 +              "member_id_v",
    1.52 +              '{}'
    1.53 +            )
    1.54 +          WHERE "issue_id" = "issue_id_p"
    1.55 +          AND "event" = 'periodic'
    1.56 +          AND "member_id" = "member_id_v";
    1.57 +      END LOOP;
    1.58 +      INSERT INTO "direct_supporter_snapshot"
    1.59 +        ( "issue_id", "initiative_id", "event", "member_id",
    1.60 +          "informed", "satisfied" )
    1.61 +        SELECT
    1.62 +          "issue_id_p"            AS "issue_id",
    1.63 +          "initiative"."id"       AS "initiative_id",
    1.64 +          'periodic'              AS "event",
    1.65 +          "supporter"."member_id" AS "member_id",
    1.66 +          "supporter"."draft_id" = "current_draft"."id" AS "informed",
    1.67 +          NOT EXISTS (
    1.68 +            SELECT NULL FROM "critical_opinion"
    1.69 +            WHERE "initiative_id" = "initiative"."id"
    1.70 +            AND "member_id" = "supporter"."member_id"
    1.71 +          ) AS "satisfied"
    1.72 +        FROM "initiative"
    1.73 +        JOIN "supporter"
    1.74 +        ON "supporter"."initiative_id" = "initiative"."id"
    1.75 +        JOIN "current_draft"
    1.76 +        ON "initiative"."id" = "current_draft"."initiative_id"
    1.77 +        JOIN "direct_interest_snapshot"
    1.78 +        ON "supporter"."member_id" = "direct_interest_snapshot"."member_id"
    1.79 +        AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"
    1.80 +        AND "event" = 'periodic'
    1.81 +        WHERE "initiative"."issue_id" = "issue_id_p";
    1.82 +      RETURN;
    1.83 +    END;
    1.84 +  $$;
    1.85 +
    1.86 +COMMIT;

Impressum / About Us