liquid_feedback_core

changeset 36:c3b72b644cc8 beta23

Critical bugfix in function "create_snapshot": Fixed incorrect calculation of opinion count for suggestions
author jbe
date Thu Mar 04 00:42:47 2010 +0100 (2010-03-04)
parents 07dca93454d6
children 28dde4909a35
files core.sql update/core-update.beta22-beta23.sql
line diff
     1.1 --- a/core.sql	Sun Feb 21 16:53:05 2010 +0100
     1.2 +++ b/core.sql	Thu Mar 04 00:42:47 2010 +0100
     1.3 @@ -6,7 +6,7 @@
     1.4  BEGIN;
     1.5  
     1.6  CREATE VIEW "liquid_feedback_version" AS
     1.7 -  SELECT * FROM (VALUES ('beta22', NULL, NULL, NULL))
     1.8 +  SELECT * FROM (VALUES ('beta23', NULL, NULL, NULL))
     1.9    AS "subquery"("string", "major", "minor", "revision");
    1.10  
    1.11  
    1.12 @@ -2329,73 +2329,97 @@
    1.13            UPDATE "suggestion" SET
    1.14              "minus2_unfulfilled_count" = (
    1.15                SELECT coalesce(sum("snapshot"."weight"), 0)
    1.16 -              FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
    1.17 -              ON "opinion"."member_id" = "snapshot"."member_id"
    1.18 -              WHERE "opinion"."suggestion_id" = "suggestion_id_v"
    1.19 -              AND "snapshot"."issue_id" = "issue_id_p"
    1.20 +              FROM "issue" CROSS JOIN "opinion"
    1.21 +              JOIN "direct_interest_snapshot" AS "snapshot"
    1.22 +              ON "snapshot"."issue_id" = "issue"."id"
    1.23 +              AND "snapshot"."event" = "issue"."latest_snapshot_event"
    1.24 +              AND "snapshot"."member_id" = "opinion"."member_id"
    1.25 +              WHERE "issue"."id" = "issue_id_p"
    1.26 +              AND "opinion"."suggestion_id" = "suggestion_id_v"
    1.27                AND "opinion"."degree" = -2
    1.28                AND "opinion"."fulfilled" = FALSE
    1.29              ),
    1.30              "minus2_fulfilled_count" = (
    1.31                SELECT coalesce(sum("snapshot"."weight"), 0)
    1.32 -              FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
    1.33 -              ON "opinion"."member_id" = "snapshot"."member_id"
    1.34 -              WHERE "opinion"."suggestion_id" = "suggestion_id_v"
    1.35 -              AND "snapshot"."issue_id" = "issue_id_p"
    1.36 +              FROM "issue" CROSS JOIN "opinion"
    1.37 +              JOIN "direct_interest_snapshot" AS "snapshot"
    1.38 +              ON "snapshot"."issue_id" = "issue"."id"
    1.39 +              AND "snapshot"."event" = "issue"."latest_snapshot_event"
    1.40 +              AND "snapshot"."member_id" = "opinion"."member_id"
    1.41 +              WHERE "issue"."id" = "issue_id_p"
    1.42 +              AND "opinion"."suggestion_id" = "suggestion_id_v"
    1.43                AND "opinion"."degree" = -2
    1.44                AND "opinion"."fulfilled" = TRUE
    1.45              ),
    1.46              "minus1_unfulfilled_count" = (
    1.47                SELECT coalesce(sum("snapshot"."weight"), 0)
    1.48 -              FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
    1.49 -              ON "opinion"."member_id" = "snapshot"."member_id"
    1.50 -              WHERE "opinion"."suggestion_id" = "suggestion_id_v"
    1.51 -              AND "snapshot"."issue_id" = "issue_id_p"
    1.52 +              FROM "issue" CROSS JOIN "opinion"
    1.53 +              JOIN "direct_interest_snapshot" AS "snapshot"
    1.54 +              ON "snapshot"."issue_id" = "issue"."id"
    1.55 +              AND "snapshot"."event" = "issue"."latest_snapshot_event"
    1.56 +              AND "snapshot"."member_id" = "opinion"."member_id"
    1.57 +              WHERE "issue"."id" = "issue_id_p"
    1.58 +              AND "opinion"."suggestion_id" = "suggestion_id_v"
    1.59                AND "opinion"."degree" = -1
    1.60                AND "opinion"."fulfilled" = FALSE
    1.61              ),
    1.62              "minus1_fulfilled_count" = (
    1.63                SELECT coalesce(sum("snapshot"."weight"), 0)
    1.64 -              FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
    1.65 -              ON "opinion"."member_id" = "snapshot"."member_id"
    1.66 -              WHERE "opinion"."suggestion_id" = "suggestion_id_v"
    1.67 -              AND "snapshot"."issue_id" = "issue_id_p"
    1.68 +              FROM "issue" CROSS JOIN "opinion"
    1.69 +              JOIN "direct_interest_snapshot" AS "snapshot"
    1.70 +              ON "snapshot"."issue_id" = "issue"."id"
    1.71 +              AND "snapshot"."event" = "issue"."latest_snapshot_event"
    1.72 +              AND "snapshot"."member_id" = "opinion"."member_id"
    1.73 +              WHERE "issue"."id" = "issue_id_p"
    1.74 +              AND "opinion"."suggestion_id" = "suggestion_id_v"
    1.75                AND "opinion"."degree" = -1
    1.76                AND "opinion"."fulfilled" = TRUE
    1.77              ),
    1.78              "plus1_unfulfilled_count" = (
    1.79                SELECT coalesce(sum("snapshot"."weight"), 0)
    1.80 -              FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
    1.81 -              ON "opinion"."member_id" = "snapshot"."member_id"
    1.82 -              WHERE "opinion"."suggestion_id" = "suggestion_id_v"
    1.83 -              AND "snapshot"."issue_id" = "issue_id_p"
    1.84 +              FROM "issue" CROSS JOIN "opinion"
    1.85 +              JOIN "direct_interest_snapshot" AS "snapshot"
    1.86 +              ON "snapshot"."issue_id" = "issue"."id"
    1.87 +              AND "snapshot"."event" = "issue"."latest_snapshot_event"
    1.88 +              AND "snapshot"."member_id" = "opinion"."member_id"
    1.89 +              WHERE "issue"."id" = "issue_id_p"
    1.90 +              AND "opinion"."suggestion_id" = "suggestion_id_v"
    1.91                AND "opinion"."degree" = 1
    1.92                AND "opinion"."fulfilled" = FALSE
    1.93              ),
    1.94              "plus1_fulfilled_count" = (
    1.95                SELECT coalesce(sum("snapshot"."weight"), 0)
    1.96 -              FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
    1.97 -              ON "opinion"."member_id" = "snapshot"."member_id"
    1.98 -              WHERE "opinion"."suggestion_id" = "suggestion_id_v"
    1.99 -              AND "snapshot"."issue_id" = "issue_id_p"
   1.100 +              FROM "issue" CROSS JOIN "opinion"
   1.101 +              JOIN "direct_interest_snapshot" AS "snapshot"
   1.102 +              ON "snapshot"."issue_id" = "issue"."id"
   1.103 +              AND "snapshot"."event" = "issue"."latest_snapshot_event"
   1.104 +              AND "snapshot"."member_id" = "opinion"."member_id"
   1.105 +              WHERE "issue"."id" = "issue_id_p"
   1.106 +              AND "opinion"."suggestion_id" = "suggestion_id_v"
   1.107                AND "opinion"."degree" = 1
   1.108                AND "opinion"."fulfilled" = TRUE
   1.109              ),
   1.110              "plus2_unfulfilled_count" = (
   1.111                SELECT coalesce(sum("snapshot"."weight"), 0)
   1.112 -              FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
   1.113 -              ON "opinion"."member_id" = "snapshot"."member_id"
   1.114 -              WHERE "opinion"."suggestion_id" = "suggestion_id_v"
   1.115 -              AND "snapshot"."issue_id" = "issue_id_p"
   1.116 +              FROM "issue" CROSS JOIN "opinion"
   1.117 +              JOIN "direct_interest_snapshot" AS "snapshot"
   1.118 +              ON "snapshot"."issue_id" = "issue"."id"
   1.119 +              AND "snapshot"."event" = "issue"."latest_snapshot_event"
   1.120 +              AND "snapshot"."member_id" = "opinion"."member_id"
   1.121 +              WHERE "issue"."id" = "issue_id_p"
   1.122 +              AND "opinion"."suggestion_id" = "suggestion_id_v"
   1.123                AND "opinion"."degree" = 2
   1.124                AND "opinion"."fulfilled" = FALSE
   1.125              ),
   1.126              "plus2_fulfilled_count" = (
   1.127                SELECT coalesce(sum("snapshot"."weight"), 0)
   1.128 -              FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
   1.129 -              ON "opinion"."member_id" = "snapshot"."member_id"
   1.130 -              WHERE "opinion"."suggestion_id" = "suggestion_id_v"
   1.131 -              AND "snapshot"."issue_id" = "issue_id_p"
   1.132 +              FROM "issue" CROSS JOIN "opinion"
   1.133 +              JOIN "direct_interest_snapshot" AS "snapshot"
   1.134 +              ON "snapshot"."issue_id" = "issue"."id"
   1.135 +              AND "snapshot"."event" = "issue"."latest_snapshot_event"
   1.136 +              AND "snapshot"."member_id" = "opinion"."member_id"
   1.137 +              WHERE "issue"."id" = "issue_id_p"
   1.138 +              AND "opinion"."suggestion_id" = "suggestion_id_v"
   1.139                AND "opinion"."degree" = 2
   1.140                AND "opinion"."fulfilled" = TRUE
   1.141              )
     2.1 --- /dev/null	Thu Jan 01 00:00:00 1970 +0000
     2.2 +++ b/update/core-update.beta22-beta23.sql	Thu Mar 04 00:42:47 2010 +0100
     2.3 @@ -0,0 +1,199 @@
     2.4 +BEGIN;
     2.5 +
     2.6 +CREATE OR REPLACE VIEW "liquid_feedback_version" AS
     2.7 +  SELECT * FROM (VALUES ('beta23', NULL, NULL, NULL))
     2.8 +  AS "subquery"("string", "major", "minor", "revision");
     2.9 +
    2.10 +CREATE OR REPLACE FUNCTION "create_snapshot"
    2.11 +  ( "issue_id_p" "issue"."id"%TYPE )
    2.12 +  RETURNS VOID
    2.13 +  LANGUAGE 'plpgsql' VOLATILE AS $$
    2.14 +    DECLARE
    2.15 +      "initiative_id_v"    "initiative"."id"%TYPE;
    2.16 +      "suggestion_id_v"    "suggestion"."id"%TYPE;
    2.17 +    BEGIN
    2.18 +      PERFORM "global_lock"();
    2.19 +      PERFORM "create_population_snapshot"("issue_id_p");
    2.20 +      PERFORM "create_interest_snapshot"("issue_id_p");
    2.21 +      UPDATE "issue" SET
    2.22 +        "snapshot" = now(),
    2.23 +        "latest_snapshot_event" = 'periodic',
    2.24 +        "population" = (
    2.25 +          SELECT coalesce(sum("weight"), 0)
    2.26 +          FROM "direct_population_snapshot"
    2.27 +          WHERE "issue_id" = "issue_id_p"
    2.28 +          AND "event" = 'periodic'
    2.29 +        ),
    2.30 +        "vote_now" = (
    2.31 +          SELECT coalesce(sum("weight"), 0)
    2.32 +          FROM "direct_interest_snapshot"
    2.33 +          WHERE "issue_id" = "issue_id_p"
    2.34 +          AND "event" = 'periodic'
    2.35 +          AND "voting_requested" = TRUE
    2.36 +        ),
    2.37 +        "vote_later" = (
    2.38 +          SELECT coalesce(sum("weight"), 0)
    2.39 +          FROM "direct_interest_snapshot"
    2.40 +          WHERE "issue_id" = "issue_id_p"
    2.41 +          AND "event" = 'periodic'
    2.42 +          AND "voting_requested" = FALSE
    2.43 +        )
    2.44 +        WHERE "id" = "issue_id_p";
    2.45 +      FOR "initiative_id_v" IN
    2.46 +        SELECT "id" FROM "initiative" WHERE "issue_id" = "issue_id_p"
    2.47 +      LOOP
    2.48 +        UPDATE "initiative" SET
    2.49 +          "supporter_count" = (
    2.50 +            SELECT coalesce(sum("di"."weight"), 0)
    2.51 +            FROM "direct_interest_snapshot" AS "di"
    2.52 +            JOIN "direct_supporter_snapshot" AS "ds"
    2.53 +            ON "di"."member_id" = "ds"."member_id"
    2.54 +            WHERE "di"."issue_id" = "issue_id_p"
    2.55 +            AND "di"."event" = 'periodic'
    2.56 +            AND "ds"."initiative_id" = "initiative_id_v"
    2.57 +            AND "ds"."event" = 'periodic'
    2.58 +          ),
    2.59 +          "informed_supporter_count" = (
    2.60 +            SELECT coalesce(sum("di"."weight"), 0)
    2.61 +            FROM "direct_interest_snapshot" AS "di"
    2.62 +            JOIN "direct_supporter_snapshot" AS "ds"
    2.63 +            ON "di"."member_id" = "ds"."member_id"
    2.64 +            WHERE "di"."issue_id" = "issue_id_p"
    2.65 +            AND "di"."event" = 'periodic'
    2.66 +            AND "ds"."initiative_id" = "initiative_id_v"
    2.67 +            AND "ds"."event" = 'periodic'
    2.68 +            AND "ds"."informed"
    2.69 +          ),
    2.70 +          "satisfied_supporter_count" = (
    2.71 +            SELECT coalesce(sum("di"."weight"), 0)
    2.72 +            FROM "direct_interest_snapshot" AS "di"
    2.73 +            JOIN "direct_supporter_snapshot" AS "ds"
    2.74 +            ON "di"."member_id" = "ds"."member_id"
    2.75 +            WHERE "di"."issue_id" = "issue_id_p"
    2.76 +            AND "di"."event" = 'periodic'
    2.77 +            AND "ds"."initiative_id" = "initiative_id_v"
    2.78 +            AND "ds"."event" = 'periodic'
    2.79 +            AND "ds"."satisfied"
    2.80 +          ),
    2.81 +          "satisfied_informed_supporter_count" = (
    2.82 +            SELECT coalesce(sum("di"."weight"), 0)
    2.83 +            FROM "direct_interest_snapshot" AS "di"
    2.84 +            JOIN "direct_supporter_snapshot" AS "ds"
    2.85 +            ON "di"."member_id" = "ds"."member_id"
    2.86 +            WHERE "di"."issue_id" = "issue_id_p"
    2.87 +            AND "di"."event" = 'periodic'
    2.88 +            AND "ds"."initiative_id" = "initiative_id_v"
    2.89 +            AND "ds"."event" = 'periodic'
    2.90 +            AND "ds"."informed"
    2.91 +            AND "ds"."satisfied"
    2.92 +          )
    2.93 +          WHERE "id" = "initiative_id_v";
    2.94 +        FOR "suggestion_id_v" IN
    2.95 +          SELECT "id" FROM "suggestion"
    2.96 +          WHERE "initiative_id" = "initiative_id_v"
    2.97 +        LOOP
    2.98 +          UPDATE "suggestion" SET
    2.99 +            "minus2_unfulfilled_count" = (
   2.100 +              SELECT coalesce(sum("snapshot"."weight"), 0)
   2.101 +              FROM "issue" CROSS JOIN "opinion"
   2.102 +              JOIN "direct_interest_snapshot" AS "snapshot"
   2.103 +              ON "snapshot"."issue_id" = "issue"."id"
   2.104 +              AND "snapshot"."event" = "issue"."latest_snapshot_event"
   2.105 +              AND "snapshot"."member_id" = "opinion"."member_id"
   2.106 +              WHERE "issue"."id" = "issue_id_p"
   2.107 +              AND "opinion"."suggestion_id" = "suggestion_id_v"
   2.108 +              AND "opinion"."degree" = -2
   2.109 +              AND "opinion"."fulfilled" = FALSE
   2.110 +            ),
   2.111 +            "minus2_fulfilled_count" = (
   2.112 +              SELECT coalesce(sum("snapshot"."weight"), 0)
   2.113 +              FROM "issue" CROSS JOIN "opinion"
   2.114 +              JOIN "direct_interest_snapshot" AS "snapshot"
   2.115 +              ON "snapshot"."issue_id" = "issue"."id"
   2.116 +              AND "snapshot"."event" = "issue"."latest_snapshot_event"
   2.117 +              AND "snapshot"."member_id" = "opinion"."member_id"
   2.118 +              WHERE "issue"."id" = "issue_id_p"
   2.119 +              AND "opinion"."suggestion_id" = "suggestion_id_v"
   2.120 +              AND "opinion"."degree" = -2
   2.121 +              AND "opinion"."fulfilled" = TRUE
   2.122 +            ),
   2.123 +            "minus1_unfulfilled_count" = (
   2.124 +              SELECT coalesce(sum("snapshot"."weight"), 0)
   2.125 +              FROM "issue" CROSS JOIN "opinion"
   2.126 +              JOIN "direct_interest_snapshot" AS "snapshot"
   2.127 +              ON "snapshot"."issue_id" = "issue"."id"
   2.128 +              AND "snapshot"."event" = "issue"."latest_snapshot_event"
   2.129 +              AND "snapshot"."member_id" = "opinion"."member_id"
   2.130 +              WHERE "issue"."id" = "issue_id_p"
   2.131 +              AND "opinion"."suggestion_id" = "suggestion_id_v"
   2.132 +              AND "opinion"."degree" = -1
   2.133 +              AND "opinion"."fulfilled" = FALSE
   2.134 +            ),
   2.135 +            "minus1_fulfilled_count" = (
   2.136 +              SELECT coalesce(sum("snapshot"."weight"), 0)
   2.137 +              FROM "issue" CROSS JOIN "opinion"
   2.138 +              JOIN "direct_interest_snapshot" AS "snapshot"
   2.139 +              ON "snapshot"."issue_id" = "issue"."id"
   2.140 +              AND "snapshot"."event" = "issue"."latest_snapshot_event"
   2.141 +              AND "snapshot"."member_id" = "opinion"."member_id"
   2.142 +              WHERE "issue"."id" = "issue_id_p"
   2.143 +              AND "opinion"."suggestion_id" = "suggestion_id_v"
   2.144 +              AND "opinion"."degree" = -1
   2.145 +              AND "opinion"."fulfilled" = TRUE
   2.146 +            ),
   2.147 +            "plus1_unfulfilled_count" = (
   2.148 +              SELECT coalesce(sum("snapshot"."weight"), 0)
   2.149 +              FROM "issue" CROSS JOIN "opinion"
   2.150 +              JOIN "direct_interest_snapshot" AS "snapshot"
   2.151 +              ON "snapshot"."issue_id" = "issue"."id"
   2.152 +              AND "snapshot"."event" = "issue"."latest_snapshot_event"
   2.153 +              AND "snapshot"."member_id" = "opinion"."member_id"
   2.154 +              WHERE "issue"."id" = "issue_id_p"
   2.155 +              AND "opinion"."suggestion_id" = "suggestion_id_v"
   2.156 +              AND "opinion"."degree" = 1
   2.157 +              AND "opinion"."fulfilled" = FALSE
   2.158 +            ),
   2.159 +            "plus1_fulfilled_count" = (
   2.160 +              SELECT coalesce(sum("snapshot"."weight"), 0)
   2.161 +              FROM "issue" CROSS JOIN "opinion"
   2.162 +              JOIN "direct_interest_snapshot" AS "snapshot"
   2.163 +              ON "snapshot"."issue_id" = "issue"."id"
   2.164 +              AND "snapshot"."event" = "issue"."latest_snapshot_event"
   2.165 +              AND "snapshot"."member_id" = "opinion"."member_id"
   2.166 +              WHERE "issue"."id" = "issue_id_p"
   2.167 +              AND "opinion"."suggestion_id" = "suggestion_id_v"
   2.168 +              AND "opinion"."degree" = 1
   2.169 +              AND "opinion"."fulfilled" = TRUE
   2.170 +            ),
   2.171 +            "plus2_unfulfilled_count" = (
   2.172 +              SELECT coalesce(sum("snapshot"."weight"), 0)
   2.173 +              FROM "issue" CROSS JOIN "opinion"
   2.174 +              JOIN "direct_interest_snapshot" AS "snapshot"
   2.175 +              ON "snapshot"."issue_id" = "issue"."id"
   2.176 +              AND "snapshot"."event" = "issue"."latest_snapshot_event"
   2.177 +              AND "snapshot"."member_id" = "opinion"."member_id"
   2.178 +              WHERE "issue"."id" = "issue_id_p"
   2.179 +              AND "opinion"."suggestion_id" = "suggestion_id_v"
   2.180 +              AND "opinion"."degree" = 2
   2.181 +              AND "opinion"."fulfilled" = FALSE
   2.182 +            ),
   2.183 +            "plus2_fulfilled_count" = (
   2.184 +              SELECT coalesce(sum("snapshot"."weight"), 0)
   2.185 +              FROM "issue" CROSS JOIN "opinion"
   2.186 +              JOIN "direct_interest_snapshot" AS "snapshot"
   2.187 +              ON "snapshot"."issue_id" = "issue"."id"
   2.188 +              AND "snapshot"."event" = "issue"."latest_snapshot_event"
   2.189 +              AND "snapshot"."member_id" = "opinion"."member_id"
   2.190 +              WHERE "issue"."id" = "issue_id_p"
   2.191 +              AND "opinion"."suggestion_id" = "suggestion_id_v"
   2.192 +              AND "opinion"."degree" = 2
   2.193 +              AND "opinion"."fulfilled" = TRUE
   2.194 +            )
   2.195 +            WHERE "suggestion"."id" = "suggestion_id_v";
   2.196 +        END LOOP;
   2.197 +      END LOOP;
   2.198 +      RETURN;
   2.199 +    END;
   2.200 +  $$;
   2.201 +
   2.202 +COMMIT;

Impressum / About Us