1.1 --- /dev/null	Thu Jan 01 00:00:00 1970 +0000
     1.2 +++ b/update/core-update.beta22-beta23.sql	Thu Mar 04 00:42:47 2010 +0100
     1.3 @@ -0,0 +1,199 @@
     1.4 +BEGIN;
     1.5 +
     1.6 +CREATE OR REPLACE VIEW "liquid_feedback_version" AS
     1.7 +  SELECT * FROM (VALUES ('beta23', NULL, NULL, NULL))
     1.8 +  AS "subquery"("string", "major", "minor", "revision");
     1.9 +
    1.10 +CREATE OR REPLACE FUNCTION "create_snapshot"
    1.11 +  ( "issue_id_p" "issue"."id"%TYPE )
    1.12 +  RETURNS VOID
    1.13 +  LANGUAGE 'plpgsql' VOLATILE AS $$
    1.14 +    DECLARE
    1.15 +      "initiative_id_v"    "initiative"."id"%TYPE;
    1.16 +      "suggestion_id_v"    "suggestion"."id"%TYPE;
    1.17 +    BEGIN
    1.18 +      PERFORM "global_lock"();
    1.19 +      PERFORM "create_population_snapshot"("issue_id_p");
    1.20 +      PERFORM "create_interest_snapshot"("issue_id_p");
    1.21 +      UPDATE "issue" SET
    1.22 +        "snapshot" = now(),
    1.23 +        "latest_snapshot_event" = 'periodic',
    1.24 +        "population" = (
    1.25 +          SELECT coalesce(sum("weight"), 0)
    1.26 +          FROM "direct_population_snapshot"
    1.27 +          WHERE "issue_id" = "issue_id_p"
    1.28 +          AND "event" = 'periodic'
    1.29 +        ),
    1.30 +        "vote_now" = (
    1.31 +          SELECT coalesce(sum("weight"), 0)
    1.32 +          FROM "direct_interest_snapshot"
    1.33 +          WHERE "issue_id" = "issue_id_p"
    1.34 +          AND "event" = 'periodic'
    1.35 +          AND "voting_requested" = TRUE
    1.36 +        ),
    1.37 +        "vote_later" = (
    1.38 +          SELECT coalesce(sum("weight"), 0)
    1.39 +          FROM "direct_interest_snapshot"
    1.40 +          WHERE "issue_id" = "issue_id_p"
    1.41 +          AND "event" = 'periodic'
    1.42 +          AND "voting_requested" = FALSE
    1.43 +        )
    1.44 +        WHERE "id" = "issue_id_p";
    1.45 +      FOR "initiative_id_v" IN
    1.46 +        SELECT "id" FROM "initiative" WHERE "issue_id" = "issue_id_p"
    1.47 +      LOOP
    1.48 +        UPDATE "initiative" SET
    1.49 +          "supporter_count" = (
    1.50 +            SELECT coalesce(sum("di"."weight"), 0)
    1.51 +            FROM "direct_interest_snapshot" AS "di"
    1.52 +            JOIN "direct_supporter_snapshot" AS "ds"
    1.53 +            ON "di"."member_id" = "ds"."member_id"
    1.54 +            WHERE "di"."issue_id" = "issue_id_p"
    1.55 +            AND "di"."event" = 'periodic'
    1.56 +            AND "ds"."initiative_id" = "initiative_id_v"
    1.57 +            AND "ds"."event" = 'periodic'
    1.58 +          ),
    1.59 +          "informed_supporter_count" = (
    1.60 +            SELECT coalesce(sum("di"."weight"), 0)
    1.61 +            FROM "direct_interest_snapshot" AS "di"
    1.62 +            JOIN "direct_supporter_snapshot" AS "ds"
    1.63 +            ON "di"."member_id" = "ds"."member_id"
    1.64 +            WHERE "di"."issue_id" = "issue_id_p"
    1.65 +            AND "di"."event" = 'periodic'
    1.66 +            AND "ds"."initiative_id" = "initiative_id_v"
    1.67 +            AND "ds"."event" = 'periodic'
    1.68 +            AND "ds"."informed"
    1.69 +          ),
    1.70 +          "satisfied_supporter_count" = (
    1.71 +            SELECT coalesce(sum("di"."weight"), 0)
    1.72 +            FROM "direct_interest_snapshot" AS "di"
    1.73 +            JOIN "direct_supporter_snapshot" AS "ds"
    1.74 +            ON "di"."member_id" = "ds"."member_id"
    1.75 +            WHERE "di"."issue_id" = "issue_id_p"
    1.76 +            AND "di"."event" = 'periodic'
    1.77 +            AND "ds"."initiative_id" = "initiative_id_v"
    1.78 +            AND "ds"."event" = 'periodic'
    1.79 +            AND "ds"."satisfied"
    1.80 +          ),
    1.81 +          "satisfied_informed_supporter_count" = (
    1.82 +            SELECT coalesce(sum("di"."weight"), 0)
    1.83 +            FROM "direct_interest_snapshot" AS "di"
    1.84 +            JOIN "direct_supporter_snapshot" AS "ds"
    1.85 +            ON "di"."member_id" = "ds"."member_id"
    1.86 +            WHERE "di"."issue_id" = "issue_id_p"
    1.87 +            AND "di"."event" = 'periodic'
    1.88 +            AND "ds"."initiative_id" = "initiative_id_v"
    1.89 +            AND "ds"."event" = 'periodic'
    1.90 +            AND "ds"."informed"
    1.91 +            AND "ds"."satisfied"
    1.92 +          )
    1.93 +          WHERE "id" = "initiative_id_v";
    1.94 +        FOR "suggestion_id_v" IN
    1.95 +          SELECT "id" FROM "suggestion"
    1.96 +          WHERE "initiative_id" = "initiative_id_v"
    1.97 +        LOOP
    1.98 +          UPDATE "suggestion" SET
    1.99 +            "minus2_unfulfilled_count" = (
   1.100 +              SELECT coalesce(sum("snapshot"."weight"), 0)
   1.101 +              FROM "issue" CROSS JOIN "opinion"
   1.102 +              JOIN "direct_interest_snapshot" AS "snapshot"
   1.103 +              ON "snapshot"."issue_id" = "issue"."id"
   1.104 +              AND "snapshot"."event" = "issue"."latest_snapshot_event"
   1.105 +              AND "snapshot"."member_id" = "opinion"."member_id"
   1.106 +              WHERE "issue"."id" = "issue_id_p"
   1.107 +              AND "opinion"."suggestion_id" = "suggestion_id_v"
   1.108 +              AND "opinion"."degree" = -2
   1.109 +              AND "opinion"."fulfilled" = FALSE
   1.110 +            ),
   1.111 +            "minus2_fulfilled_count" = (
   1.112 +              SELECT coalesce(sum("snapshot"."weight"), 0)
   1.113 +              FROM "issue" CROSS JOIN "opinion"
   1.114 +              JOIN "direct_interest_snapshot" AS "snapshot"
   1.115 +              ON "snapshot"."issue_id" = "issue"."id"
   1.116 +              AND "snapshot"."event" = "issue"."latest_snapshot_event"
   1.117 +              AND "snapshot"."member_id" = "opinion"."member_id"
   1.118 +              WHERE "issue"."id" = "issue_id_p"
   1.119 +              AND "opinion"."suggestion_id" = "suggestion_id_v"
   1.120 +              AND "opinion"."degree" = -2
   1.121 +              AND "opinion"."fulfilled" = TRUE
   1.122 +            ),
   1.123 +            "minus1_unfulfilled_count" = (
   1.124 +              SELECT coalesce(sum("snapshot"."weight"), 0)
   1.125 +              FROM "issue" CROSS JOIN "opinion"
   1.126 +              JOIN "direct_interest_snapshot" AS "snapshot"
   1.127 +              ON "snapshot"."issue_id" = "issue"."id"
   1.128 +              AND "snapshot"."event" = "issue"."latest_snapshot_event"
   1.129 +              AND "snapshot"."member_id" = "opinion"."member_id"
   1.130 +              WHERE "issue"."id" = "issue_id_p"
   1.131 +              AND "opinion"."suggestion_id" = "suggestion_id_v"
   1.132 +              AND "opinion"."degree" = -1
   1.133 +              AND "opinion"."fulfilled" = FALSE
   1.134 +            ),
   1.135 +            "minus1_fulfilled_count" = (
   1.136 +              SELECT coalesce(sum("snapshot"."weight"), 0)
   1.137 +              FROM "issue" CROSS JOIN "opinion"
   1.138 +              JOIN "direct_interest_snapshot" AS "snapshot"
   1.139 +              ON "snapshot"."issue_id" = "issue"."id"
   1.140 +              AND "snapshot"."event" = "issue"."latest_snapshot_event"
   1.141 +              AND "snapshot"."member_id" = "opinion"."member_id"
   1.142 +              WHERE "issue"."id" = "issue_id_p"
   1.143 +              AND "opinion"."suggestion_id" = "suggestion_id_v"
   1.144 +              AND "opinion"."degree" = -1
   1.145 +              AND "opinion"."fulfilled" = TRUE
   1.146 +            ),
   1.147 +            "plus1_unfulfilled_count" = (
   1.148 +              SELECT coalesce(sum("snapshot"."weight"), 0)
   1.149 +              FROM "issue" CROSS JOIN "opinion"
   1.150 +              JOIN "direct_interest_snapshot" AS "snapshot"
   1.151 +              ON "snapshot"."issue_id" = "issue"."id"
   1.152 +              AND "snapshot"."event" = "issue"."latest_snapshot_event"
   1.153 +              AND "snapshot"."member_id" = "opinion"."member_id"
   1.154 +              WHERE "issue"."id" = "issue_id_p"
   1.155 +              AND "opinion"."suggestion_id" = "suggestion_id_v"
   1.156 +              AND "opinion"."degree" = 1
   1.157 +              AND "opinion"."fulfilled" = FALSE
   1.158 +            ),
   1.159 +            "plus1_fulfilled_count" = (
   1.160 +              SELECT coalesce(sum("snapshot"."weight"), 0)
   1.161 +              FROM "issue" CROSS JOIN "opinion"
   1.162 +              JOIN "direct_interest_snapshot" AS "snapshot"
   1.163 +              ON "snapshot"."issue_id" = "issue"."id"
   1.164 +              AND "snapshot"."event" = "issue"."latest_snapshot_event"
   1.165 +              AND "snapshot"."member_id" = "opinion"."member_id"
   1.166 +              WHERE "issue"."id" = "issue_id_p"
   1.167 +              AND "opinion"."suggestion_id" = "suggestion_id_v"
   1.168 +              AND "opinion"."degree" = 1
   1.169 +              AND "opinion"."fulfilled" = TRUE
   1.170 +            ),
   1.171 +            "plus2_unfulfilled_count" = (
   1.172 +              SELECT coalesce(sum("snapshot"."weight"), 0)
   1.173 +              FROM "issue" CROSS JOIN "opinion"
   1.174 +              JOIN "direct_interest_snapshot" AS "snapshot"
   1.175 +              ON "snapshot"."issue_id" = "issue"."id"
   1.176 +              AND "snapshot"."event" = "issue"."latest_snapshot_event"
   1.177 +              AND "snapshot"."member_id" = "opinion"."member_id"
   1.178 +              WHERE "issue"."id" = "issue_id_p"
   1.179 +              AND "opinion"."suggestion_id" = "suggestion_id_v"
   1.180 +              AND "opinion"."degree" = 2
   1.181 +              AND "opinion"."fulfilled" = FALSE
   1.182 +            ),
   1.183 +            "plus2_fulfilled_count" = (
   1.184 +              SELECT coalesce(sum("snapshot"."weight"), 0)
   1.185 +              FROM "issue" CROSS JOIN "opinion"
   1.186 +              JOIN "direct_interest_snapshot" AS "snapshot"
   1.187 +              ON "snapshot"."issue_id" = "issue"."id"
   1.188 +              AND "snapshot"."event" = "issue"."latest_snapshot_event"
   1.189 +              AND "snapshot"."member_id" = "opinion"."member_id"
   1.190 +              WHERE "issue"."id" = "issue_id_p"
   1.191 +              AND "opinion"."suggestion_id" = "suggestion_id_v"
   1.192 +              AND "opinion"."degree" = 2
   1.193 +              AND "opinion"."fulfilled" = TRUE
   1.194 +            )
   1.195 +            WHERE "suggestion"."id" = "suggestion_id_v";
   1.196 +        END LOOP;
   1.197 +      END LOOP;
   1.198 +      RETURN;
   1.199 +    END;
   1.200 +  $$;
   1.201 +
   1.202 +COMMIT;