1.1 --- a/core.sql	Tue Oct 27 12:00:00 2009 +0100
     1.2 +++ b/core.sql	Tue Nov 03 12:00:00 2009 +0100
     1.3 @@ -838,6 +838,16 @@
     1.4  COMMENT ON VIEW "battle" IS 'Number of members preferring one initiative over another';
     1.5  
     1.6  
     1.7 +CREATE VIEW "expired_session" AS
     1.8 +  SELECT * FROM "session" WHERE now() > "expiry";
     1.9 +
    1.10 +CREATE RULE "delete" AS ON DELETE TO "expired_session" DO INSTEAD
    1.11 +  DELETE FROM "session" WHERE "ident" = OLD."ident";
    1.12 +
    1.13 +COMMENT ON VIEW "expired_session" IS 'View containing all expired sessions where DELETE is possible';
    1.14 +COMMENT ON RULE "delete" ON "expired_session" IS 'Rule allowing DELETE on rows in "expired_session" view, i.e. DELETE FROM "expired_session"';
    1.15 +
    1.16 +
    1.17  CREATE VIEW "open_issue" AS
    1.18    SELECT * FROM "issue" WHERE "closed" ISNULL;
    1.19  
    1.20 @@ -1276,7 +1286,7 @@
    1.21                SELECT coalesce(sum("snapshot"."weight"), 0)
    1.22                FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
    1.23                ON "opinion"."member_id" = "snapshot"."member_id"
    1.24 -              WHERE "opinion"."initiative_id" = "initiative_id_v"
    1.25 +              WHERE "opinion"."suggestion_id" = "suggestion_id_v"
    1.26                AND "snapshot"."issue_id" = "issue_id_p"
    1.27                AND "opinion"."degree" = -2
    1.28                AND "opinion"."fulfilled" = FALSE
    1.29 @@ -1285,7 +1295,7 @@
    1.30                SELECT coalesce(sum("snapshot"."weight"), 0)
    1.31                FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
    1.32                ON "opinion"."member_id" = "snapshot"."member_id"
    1.33 -              WHERE "opinion"."initiative_id" = "initiative_id_v"
    1.34 +              WHERE "opinion"."suggestion_id" = "suggestion_id_v"
    1.35                AND "snapshot"."issue_id" = "issue_id_p"
    1.36                AND "opinion"."degree" = -2
    1.37                AND "opinion"."fulfilled" = TRUE
    1.38 @@ -1294,7 +1304,7 @@
    1.39                SELECT coalesce(sum("snapshot"."weight"), 0)
    1.40                FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
    1.41                ON "opinion"."member_id" = "snapshot"."member_id"
    1.42 -              WHERE "opinion"."initiative_id" = "initiative_id_v"
    1.43 +              WHERE "opinion"."suggestion_id" = "suggestion_id_v"
    1.44                AND "snapshot"."issue_id" = "issue_id_p"
    1.45                AND "opinion"."degree" = -1
    1.46                AND "opinion"."fulfilled" = FALSE
    1.47 @@ -1303,7 +1313,7 @@
    1.48                SELECT coalesce(sum("snapshot"."weight"), 0)
    1.49                FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
    1.50                ON "opinion"."member_id" = "snapshot"."member_id"
    1.51 -              WHERE "opinion"."initiative_id" = "initiative_id_v"
    1.52 +              WHERE "opinion"."suggestion_id" = "suggestion_id_v"
    1.53                AND "snapshot"."issue_id" = "issue_id_p"
    1.54                AND "opinion"."degree" = -1
    1.55                AND "opinion"."fulfilled" = TRUE
    1.56 @@ -1312,7 +1322,7 @@
    1.57                SELECT coalesce(sum("snapshot"."weight"), 0)
    1.58                FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
    1.59                ON "opinion"."member_id" = "snapshot"."member_id"
    1.60 -              WHERE "opinion"."initiative_id" = "initiative_id_v"
    1.61 +              WHERE "opinion"."suggestion_id" = "suggestion_id_v"
    1.62                AND "snapshot"."issue_id" = "issue_id_p"
    1.63                AND "opinion"."degree" = 1
    1.64                AND "opinion"."fulfilled" = FALSE
    1.65 @@ -1321,7 +1331,7 @@
    1.66                SELECT coalesce(sum("snapshot"."weight"), 0)
    1.67                FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
    1.68                ON "opinion"."member_id" = "snapshot"."member_id"
    1.69 -              WHERE "opinion"."initiative_id" = "initiative_id_v"
    1.70 +              WHERE "opinion"."suggestion_id" = "suggestion_id_v"
    1.71                AND "snapshot"."issue_id" = "issue_id_p"
    1.72                AND "opinion"."degree" = 1
    1.73                AND "opinion"."fulfilled" = TRUE
    1.74 @@ -1330,7 +1340,7 @@
    1.75                SELECT coalesce(sum("snapshot"."weight"), 0)
    1.76                FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
    1.77                ON "opinion"."member_id" = "snapshot"."member_id"
    1.78 -              WHERE "opinion"."initiative_id" = "initiative_id_v"
    1.79 +              WHERE "opinion"."suggestion_id" = "suggestion_id_v"
    1.80                AND "snapshot"."issue_id" = "issue_id_p"
    1.81                AND "opinion"."degree" = 2
    1.82                AND "opinion"."fulfilled" = FALSE
    1.83 @@ -1339,7 +1349,7 @@
    1.84                SELECT coalesce(sum("snapshot"."weight"), 0)
    1.85                FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
    1.86                ON "opinion"."member_id" = "snapshot"."member_id"
    1.87 -              WHERE "opinion"."initiative_id" = "initiative_id_v"
    1.88 +              WHERE "opinion"."suggestion_id" = "suggestion_id_v"
    1.89                AND "snapshot"."issue_id" = "issue_id_p"
    1.90                AND "opinion"."degree" = 2
    1.91                AND "opinion"."fulfilled" = TRUE
    1.92 @@ -1962,16 +1972,14 @@
    1.93      DECLARE
    1.94        "issue_id_v" "issue"."id"%TYPE;
    1.95      BEGIN
    1.96 +      DELETE FROM "expired_session";
    1.97        FOR "issue_id_v" IN
    1.98 -        SELECT "id" FROM "issue" WHERE "closed" ISNULL
    1.99 +        SELECT "id" FROM "open_issue"
   1.100        LOOP
   1.101          PERFORM "check_issue"("issue_id_v");
   1.102        END LOOP;
   1.103        FOR "issue_id_v" IN
   1.104 -        SELECT "id" FROM "issue"
   1.105 -        WHERE "frozen" NOTNULL
   1.106 -        AND "closed" NOTNULL
   1.107 -        AND "ranks_available" = FALSE FOR UPDATE
   1.108 +        SELECT "id" FROM "issue_with_ranks_missing"
   1.109        LOOP
   1.110          PERFORM "calculate_ranks"("issue_id_v");
   1.111        END LOOP;