# HG changeset patch
# User jbe
# Date 1267659767 -3600
# Node ID c3b72b644cc86ca375cb00d0363934961b6d21af
# Parent  07dca93454d6f8c5de7e80b27ecd1cbb73da4762
Critical bugfix in function "create_snapshot": Fixed incorrect calculation of opinion count for suggestions

diff -r 07dca93454d6 -r c3b72b644cc8 core.sql
--- a/core.sql	Sun Feb 21 16:53:05 2010 +0100
+++ b/core.sql	Thu Mar 04 00:42:47 2010 +0100
@@ -6,7 +6,7 @@
 BEGIN;
 
 CREATE VIEW "liquid_feedback_version" AS
-  SELECT * FROM (VALUES ('beta22', NULL, NULL, NULL))
+  SELECT * FROM (VALUES ('beta23', NULL, NULL, NULL))
   AS "subquery"("string", "major", "minor", "revision");
 
 
@@ -2329,73 +2329,97 @@
           UPDATE "suggestion" SET
             "minus2_unfulfilled_count" = (
               SELECT coalesce(sum("snapshot"."weight"), 0)
-              FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
-              ON "opinion"."member_id" = "snapshot"."member_id"
-              WHERE "opinion"."suggestion_id" = "suggestion_id_v"
-              AND "snapshot"."issue_id" = "issue_id_p"
+              FROM "issue" CROSS JOIN "opinion"
+              JOIN "direct_interest_snapshot" AS "snapshot"
+              ON "snapshot"."issue_id" = "issue"."id"
+              AND "snapshot"."event" = "issue"."latest_snapshot_event"
+              AND "snapshot"."member_id" = "opinion"."member_id"
+              WHERE "issue"."id" = "issue_id_p"
+              AND "opinion"."suggestion_id" = "suggestion_id_v"
               AND "opinion"."degree" = -2
               AND "opinion"."fulfilled" = FALSE
             ),
             "minus2_fulfilled_count" = (
               SELECT coalesce(sum("snapshot"."weight"), 0)
-              FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
-              ON "opinion"."member_id" = "snapshot"."member_id"
-              WHERE "opinion"."suggestion_id" = "suggestion_id_v"
-              AND "snapshot"."issue_id" = "issue_id_p"
+              FROM "issue" CROSS JOIN "opinion"
+              JOIN "direct_interest_snapshot" AS "snapshot"
+              ON "snapshot"."issue_id" = "issue"."id"
+              AND "snapshot"."event" = "issue"."latest_snapshot_event"
+              AND "snapshot"."member_id" = "opinion"."member_id"
+              WHERE "issue"."id" = "issue_id_p"
+              AND "opinion"."suggestion_id" = "suggestion_id_v"
               AND "opinion"."degree" = -2
               AND "opinion"."fulfilled" = TRUE
             ),
             "minus1_unfulfilled_count" = (
               SELECT coalesce(sum("snapshot"."weight"), 0)
-              FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
-              ON "opinion"."member_id" = "snapshot"."member_id"
-              WHERE "opinion"."suggestion_id" = "suggestion_id_v"
-              AND "snapshot"."issue_id" = "issue_id_p"
+              FROM "issue" CROSS JOIN "opinion"
+              JOIN "direct_interest_snapshot" AS "snapshot"
+              ON "snapshot"."issue_id" = "issue"."id"
+              AND "snapshot"."event" = "issue"."latest_snapshot_event"
+              AND "snapshot"."member_id" = "opinion"."member_id"
+              WHERE "issue"."id" = "issue_id_p"
+              AND "opinion"."suggestion_id" = "suggestion_id_v"
               AND "opinion"."degree" = -1
               AND "opinion"."fulfilled" = FALSE
             ),
             "minus1_fulfilled_count" = (
               SELECT coalesce(sum("snapshot"."weight"), 0)
-              FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
-              ON "opinion"."member_id" = "snapshot"."member_id"
-              WHERE "opinion"."suggestion_id" = "suggestion_id_v"
-              AND "snapshot"."issue_id" = "issue_id_p"
+              FROM "issue" CROSS JOIN "opinion"
+              JOIN "direct_interest_snapshot" AS "snapshot"
+              ON "snapshot"."issue_id" = "issue"."id"
+              AND "snapshot"."event" = "issue"."latest_snapshot_event"
+              AND "snapshot"."member_id" = "opinion"."member_id"
+              WHERE "issue"."id" = "issue_id_p"
+              AND "opinion"."suggestion_id" = "suggestion_id_v"
               AND "opinion"."degree" = -1
               AND "opinion"."fulfilled" = TRUE
             ),
             "plus1_unfulfilled_count" = (
               SELECT coalesce(sum("snapshot"."weight"), 0)
-              FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
-              ON "opinion"."member_id" = "snapshot"."member_id"
-              WHERE "opinion"."suggestion_id" = "suggestion_id_v"
-              AND "snapshot"."issue_id" = "issue_id_p"
+              FROM "issue" CROSS JOIN "opinion"
+              JOIN "direct_interest_snapshot" AS "snapshot"
+              ON "snapshot"."issue_id" = "issue"."id"
+              AND "snapshot"."event" = "issue"."latest_snapshot_event"
+              AND "snapshot"."member_id" = "opinion"."member_id"
+              WHERE "issue"."id" = "issue_id_p"
+              AND "opinion"."suggestion_id" = "suggestion_id_v"
               AND "opinion"."degree" = 1
               AND "opinion"."fulfilled" = FALSE
             ),
             "plus1_fulfilled_count" = (
               SELECT coalesce(sum("snapshot"."weight"), 0)
-              FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
-              ON "opinion"."member_id" = "snapshot"."member_id"
-              WHERE "opinion"."suggestion_id" = "suggestion_id_v"
-              AND "snapshot"."issue_id" = "issue_id_p"
+              FROM "issue" CROSS JOIN "opinion"
+              JOIN "direct_interest_snapshot" AS "snapshot"
+              ON "snapshot"."issue_id" = "issue"."id"
+              AND "snapshot"."event" = "issue"."latest_snapshot_event"
+              AND "snapshot"."member_id" = "opinion"."member_id"
+              WHERE "issue"."id" = "issue_id_p"
+              AND "opinion"."suggestion_id" = "suggestion_id_v"
               AND "opinion"."degree" = 1
               AND "opinion"."fulfilled" = TRUE
             ),
             "plus2_unfulfilled_count" = (
               SELECT coalesce(sum("snapshot"."weight"), 0)
-              FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
-              ON "opinion"."member_id" = "snapshot"."member_id"
-              WHERE "opinion"."suggestion_id" = "suggestion_id_v"
-              AND "snapshot"."issue_id" = "issue_id_p"
+              FROM "issue" CROSS JOIN "opinion"
+              JOIN "direct_interest_snapshot" AS "snapshot"
+              ON "snapshot"."issue_id" = "issue"."id"
+              AND "snapshot"."event" = "issue"."latest_snapshot_event"
+              AND "snapshot"."member_id" = "opinion"."member_id"
+              WHERE "issue"."id" = "issue_id_p"
+              AND "opinion"."suggestion_id" = "suggestion_id_v"
               AND "opinion"."degree" = 2
               AND "opinion"."fulfilled" = FALSE
             ),
             "plus2_fulfilled_count" = (
               SELECT coalesce(sum("snapshot"."weight"), 0)
-              FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
-              ON "opinion"."member_id" = "snapshot"."member_id"
-              WHERE "opinion"."suggestion_id" = "suggestion_id_v"
-              AND "snapshot"."issue_id" = "issue_id_p"
+              FROM "issue" CROSS JOIN "opinion"
+              JOIN "direct_interest_snapshot" AS "snapshot"
+              ON "snapshot"."issue_id" = "issue"."id"
+              AND "snapshot"."event" = "issue"."latest_snapshot_event"
+              AND "snapshot"."member_id" = "opinion"."member_id"
+              WHERE "issue"."id" = "issue_id_p"
+              AND "opinion"."suggestion_id" = "suggestion_id_v"
               AND "opinion"."degree" = 2
               AND "opinion"."fulfilled" = TRUE
             )
diff -r 07dca93454d6 -r c3b72b644cc8 update/core-update.beta22-beta23.sql
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/update/core-update.beta22-beta23.sql	Thu Mar 04 00:42:47 2010 +0100
@@ -0,0 +1,199 @@
+BEGIN;
+
+CREATE OR REPLACE VIEW "liquid_feedback_version" AS
+  SELECT * FROM (VALUES ('beta23', NULL, NULL, NULL))
+  AS "subquery"("string", "major", "minor", "revision");
+
+CREATE OR REPLACE FUNCTION "create_snapshot"
+  ( "issue_id_p" "issue"."id"%TYPE )
+  RETURNS VOID
+  LANGUAGE 'plpgsql' VOLATILE AS $$
+    DECLARE
+      "initiative_id_v"    "initiative"."id"%TYPE;
+      "suggestion_id_v"    "suggestion"."id"%TYPE;
+    BEGIN
+      PERFORM "global_lock"();
+      PERFORM "create_population_snapshot"("issue_id_p");
+      PERFORM "create_interest_snapshot"("issue_id_p");
+      UPDATE "issue" SET
+        "snapshot" = now(),
+        "latest_snapshot_event" = 'periodic',
+        "population" = (
+          SELECT coalesce(sum("weight"), 0)
+          FROM "direct_population_snapshot"
+          WHERE "issue_id" = "issue_id_p"
+          AND "event" = 'periodic'
+        ),
+        "vote_now" = (
+          SELECT coalesce(sum("weight"), 0)
+          FROM "direct_interest_snapshot"
+          WHERE "issue_id" = "issue_id_p"
+          AND "event" = 'periodic'
+          AND "voting_requested" = TRUE
+        ),
+        "vote_later" = (
+          SELECT coalesce(sum("weight"), 0)
+          FROM "direct_interest_snapshot"
+          WHERE "issue_id" = "issue_id_p"
+          AND "event" = 'periodic'
+          AND "voting_requested" = FALSE
+        )
+        WHERE "id" = "issue_id_p";
+      FOR "initiative_id_v" IN
+        SELECT "id" FROM "initiative" WHERE "issue_id" = "issue_id_p"
+      LOOP
+        UPDATE "initiative" SET
+          "supporter_count" = (
+            SELECT coalesce(sum("di"."weight"), 0)
+            FROM "direct_interest_snapshot" AS "di"
+            JOIN "direct_supporter_snapshot" AS "ds"
+            ON "di"."member_id" = "ds"."member_id"
+            WHERE "di"."issue_id" = "issue_id_p"
+            AND "di"."event" = 'periodic'
+            AND "ds"."initiative_id" = "initiative_id_v"
+            AND "ds"."event" = 'periodic'
+          ),
+          "informed_supporter_count" = (
+            SELECT coalesce(sum("di"."weight"), 0)
+            FROM "direct_interest_snapshot" AS "di"
+            JOIN "direct_supporter_snapshot" AS "ds"
+            ON "di"."member_id" = "ds"."member_id"
+            WHERE "di"."issue_id" = "issue_id_p"
+            AND "di"."event" = 'periodic'
+            AND "ds"."initiative_id" = "initiative_id_v"
+            AND "ds"."event" = 'periodic'
+            AND "ds"."informed"
+          ),
+          "satisfied_supporter_count" = (
+            SELECT coalesce(sum("di"."weight"), 0)
+            FROM "direct_interest_snapshot" AS "di"
+            JOIN "direct_supporter_snapshot" AS "ds"
+            ON "di"."member_id" = "ds"."member_id"
+            WHERE "di"."issue_id" = "issue_id_p"
+            AND "di"."event" = 'periodic'
+            AND "ds"."initiative_id" = "initiative_id_v"
+            AND "ds"."event" = 'periodic'
+            AND "ds"."satisfied"
+          ),
+          "satisfied_informed_supporter_count" = (
+            SELECT coalesce(sum("di"."weight"), 0)
+            FROM "direct_interest_snapshot" AS "di"
+            JOIN "direct_supporter_snapshot" AS "ds"
+            ON "di"."member_id" = "ds"."member_id"
+            WHERE "di"."issue_id" = "issue_id_p"
+            AND "di"."event" = 'periodic'
+            AND "ds"."initiative_id" = "initiative_id_v"
+            AND "ds"."event" = 'periodic'
+            AND "ds"."informed"
+            AND "ds"."satisfied"
+          )
+          WHERE "id" = "initiative_id_v";
+        FOR "suggestion_id_v" IN
+          SELECT "id" FROM "suggestion"
+          WHERE "initiative_id" = "initiative_id_v"
+        LOOP
+          UPDATE "suggestion" SET
+            "minus2_unfulfilled_count" = (
+              SELECT coalesce(sum("snapshot"."weight"), 0)
+              FROM "issue" CROSS JOIN "opinion"
+              JOIN "direct_interest_snapshot" AS "snapshot"
+              ON "snapshot"."issue_id" = "issue"."id"
+              AND "snapshot"."event" = "issue"."latest_snapshot_event"
+              AND "snapshot"."member_id" = "opinion"."member_id"
+              WHERE "issue"."id" = "issue_id_p"
+              AND "opinion"."suggestion_id" = "suggestion_id_v"
+              AND "opinion"."degree" = -2
+              AND "opinion"."fulfilled" = FALSE
+            ),
+            "minus2_fulfilled_count" = (
+              SELECT coalesce(sum("snapshot"."weight"), 0)
+              FROM "issue" CROSS JOIN "opinion"
+              JOIN "direct_interest_snapshot" AS "snapshot"
+              ON "snapshot"."issue_id" = "issue"."id"
+              AND "snapshot"."event" = "issue"."latest_snapshot_event"
+              AND "snapshot"."member_id" = "opinion"."member_id"
+              WHERE "issue"."id" = "issue_id_p"
+              AND "opinion"."suggestion_id" = "suggestion_id_v"
+              AND "opinion"."degree" = -2
+              AND "opinion"."fulfilled" = TRUE
+            ),
+            "minus1_unfulfilled_count" = (
+              SELECT coalesce(sum("snapshot"."weight"), 0)
+              FROM "issue" CROSS JOIN "opinion"
+              JOIN "direct_interest_snapshot" AS "snapshot"
+              ON "snapshot"."issue_id" = "issue"."id"
+              AND "snapshot"."event" = "issue"."latest_snapshot_event"
+              AND "snapshot"."member_id" = "opinion"."member_id"
+              WHERE "issue"."id" = "issue_id_p"
+              AND "opinion"."suggestion_id" = "suggestion_id_v"
+              AND "opinion"."degree" = -1
+              AND "opinion"."fulfilled" = FALSE
+            ),
+            "minus1_fulfilled_count" = (
+              SELECT coalesce(sum("snapshot"."weight"), 0)
+              FROM "issue" CROSS JOIN "opinion"
+              JOIN "direct_interest_snapshot" AS "snapshot"
+              ON "snapshot"."issue_id" = "issue"."id"
+              AND "snapshot"."event" = "issue"."latest_snapshot_event"
+              AND "snapshot"."member_id" = "opinion"."member_id"
+              WHERE "issue"."id" = "issue_id_p"
+              AND "opinion"."suggestion_id" = "suggestion_id_v"
+              AND "opinion"."degree" = -1
+              AND "opinion"."fulfilled" = TRUE
+            ),
+            "plus1_unfulfilled_count" = (
+              SELECT coalesce(sum("snapshot"."weight"), 0)
+              FROM "issue" CROSS JOIN "opinion"
+              JOIN "direct_interest_snapshot" AS "snapshot"
+              ON "snapshot"."issue_id" = "issue"."id"
+              AND "snapshot"."event" = "issue"."latest_snapshot_event"
+              AND "snapshot"."member_id" = "opinion"."member_id"
+              WHERE "issue"."id" = "issue_id_p"
+              AND "opinion"."suggestion_id" = "suggestion_id_v"
+              AND "opinion"."degree" = 1
+              AND "opinion"."fulfilled" = FALSE
+            ),
+            "plus1_fulfilled_count" = (
+              SELECT coalesce(sum("snapshot"."weight"), 0)
+              FROM "issue" CROSS JOIN "opinion"
+              JOIN "direct_interest_snapshot" AS "snapshot"
+              ON "snapshot"."issue_id" = "issue"."id"
+              AND "snapshot"."event" = "issue"."latest_snapshot_event"
+              AND "snapshot"."member_id" = "opinion"."member_id"
+              WHERE "issue"."id" = "issue_id_p"
+              AND "opinion"."suggestion_id" = "suggestion_id_v"
+              AND "opinion"."degree" = 1
+              AND "opinion"."fulfilled" = TRUE
+            ),
+            "plus2_unfulfilled_count" = (
+              SELECT coalesce(sum("snapshot"."weight"), 0)
+              FROM "issue" CROSS JOIN "opinion"
+              JOIN "direct_interest_snapshot" AS "snapshot"
+              ON "snapshot"."issue_id" = "issue"."id"
+              AND "snapshot"."event" = "issue"."latest_snapshot_event"
+              AND "snapshot"."member_id" = "opinion"."member_id"
+              WHERE "issue"."id" = "issue_id_p"
+              AND "opinion"."suggestion_id" = "suggestion_id_v"
+              AND "opinion"."degree" = 2
+              AND "opinion"."fulfilled" = FALSE
+            ),
+            "plus2_fulfilled_count" = (
+              SELECT coalesce(sum("snapshot"."weight"), 0)
+              FROM "issue" CROSS JOIN "opinion"
+              JOIN "direct_interest_snapshot" AS "snapshot"
+              ON "snapshot"."issue_id" = "issue"."id"
+              AND "snapshot"."event" = "issue"."latest_snapshot_event"
+              AND "snapshot"."member_id" = "opinion"."member_id"
+              WHERE "issue"."id" = "issue_id_p"
+              AND "opinion"."suggestion_id" = "suggestion_id_v"
+              AND "opinion"."degree" = 2
+              AND "opinion"."fulfilled" = TRUE
+            )
+            WHERE "suggestion"."id" = "suggestion_id_v";
+        END LOOP;
+      END LOOP;
+      RETURN;
+    END;
+  $$;
+
+COMMIT;