# HG changeset patch
# User jbe
# Date 1257246000 -3600
# Node ID 23092eb00e16dad8a7deec95c494fad74381cb10
# Parent  8d021cb5eaf430f1b95ca00860f302b93c9c710b
Version beta2

Serious bugfix in SQL function create_snapshot(...), which caused wrong counting of opinions on suggestions

lf_update now deletes expired sessions

Redundancy in SQL function check_everything() removed by using existent views

diff -r 8d021cb5eaf4 -r 23092eb00e16 core.sql
--- a/core.sql	Tue Oct 27 12:00:00 2009 +0100
+++ b/core.sql	Tue Nov 03 12:00:00 2009 +0100
@@ -838,6 +838,16 @@
 COMMENT ON VIEW "battle" IS 'Number of members preferring one initiative over another';
 
 
+CREATE VIEW "expired_session" AS
+  SELECT * FROM "session" WHERE now() > "expiry";
+
+CREATE RULE "delete" AS ON DELETE TO "expired_session" DO INSTEAD
+  DELETE FROM "session" WHERE "ident" = OLD."ident";
+
+COMMENT ON VIEW "expired_session" IS 'View containing all expired sessions where DELETE is possible';
+COMMENT ON RULE "delete" ON "expired_session" IS 'Rule allowing DELETE on rows in "expired_session" view, i.e. DELETE FROM "expired_session"';
+
+
 CREATE VIEW "open_issue" AS
   SELECT * FROM "issue" WHERE "closed" ISNULL;
 
@@ -1276,7 +1286,7 @@
               SELECT coalesce(sum("snapshot"."weight"), 0)
               FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
               ON "opinion"."member_id" = "snapshot"."member_id"
-              WHERE "opinion"."initiative_id" = "initiative_id_v"
+              WHERE "opinion"."suggestion_id" = "suggestion_id_v"
               AND "snapshot"."issue_id" = "issue_id_p"
               AND "opinion"."degree" = -2
               AND "opinion"."fulfilled" = FALSE
@@ -1285,7 +1295,7 @@
               SELECT coalesce(sum("snapshot"."weight"), 0)
               FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
               ON "opinion"."member_id" = "snapshot"."member_id"
-              WHERE "opinion"."initiative_id" = "initiative_id_v"
+              WHERE "opinion"."suggestion_id" = "suggestion_id_v"
               AND "snapshot"."issue_id" = "issue_id_p"
               AND "opinion"."degree" = -2
               AND "opinion"."fulfilled" = TRUE
@@ -1294,7 +1304,7 @@
               SELECT coalesce(sum("snapshot"."weight"), 0)
               FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
               ON "opinion"."member_id" = "snapshot"."member_id"
-              WHERE "opinion"."initiative_id" = "initiative_id_v"
+              WHERE "opinion"."suggestion_id" = "suggestion_id_v"
               AND "snapshot"."issue_id" = "issue_id_p"
               AND "opinion"."degree" = -1
               AND "opinion"."fulfilled" = FALSE
@@ -1303,7 +1313,7 @@
               SELECT coalesce(sum("snapshot"."weight"), 0)
               FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
               ON "opinion"."member_id" = "snapshot"."member_id"
-              WHERE "opinion"."initiative_id" = "initiative_id_v"
+              WHERE "opinion"."suggestion_id" = "suggestion_id_v"
               AND "snapshot"."issue_id" = "issue_id_p"
               AND "opinion"."degree" = -1
               AND "opinion"."fulfilled" = TRUE
@@ -1312,7 +1322,7 @@
               SELECT coalesce(sum("snapshot"."weight"), 0)
               FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
               ON "opinion"."member_id" = "snapshot"."member_id"
-              WHERE "opinion"."initiative_id" = "initiative_id_v"
+              WHERE "opinion"."suggestion_id" = "suggestion_id_v"
               AND "snapshot"."issue_id" = "issue_id_p"
               AND "opinion"."degree" = 1
               AND "opinion"."fulfilled" = FALSE
@@ -1321,7 +1331,7 @@
               SELECT coalesce(sum("snapshot"."weight"), 0)
               FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
               ON "opinion"."member_id" = "snapshot"."member_id"
-              WHERE "opinion"."initiative_id" = "initiative_id_v"
+              WHERE "opinion"."suggestion_id" = "suggestion_id_v"
               AND "snapshot"."issue_id" = "issue_id_p"
               AND "opinion"."degree" = 1
               AND "opinion"."fulfilled" = TRUE
@@ -1330,7 +1340,7 @@
               SELECT coalesce(sum("snapshot"."weight"), 0)
               FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
               ON "opinion"."member_id" = "snapshot"."member_id"
-              WHERE "opinion"."initiative_id" = "initiative_id_v"
+              WHERE "opinion"."suggestion_id" = "suggestion_id_v"
               AND "snapshot"."issue_id" = "issue_id_p"
               AND "opinion"."degree" = 2
               AND "opinion"."fulfilled" = FALSE
@@ -1339,7 +1349,7 @@
               SELECT coalesce(sum("snapshot"."weight"), 0)
               FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
               ON "opinion"."member_id" = "snapshot"."member_id"
-              WHERE "opinion"."initiative_id" = "initiative_id_v"
+              WHERE "opinion"."suggestion_id" = "suggestion_id_v"
               AND "snapshot"."issue_id" = "issue_id_p"
               AND "opinion"."degree" = 2
               AND "opinion"."fulfilled" = TRUE
@@ -1962,16 +1972,14 @@
     DECLARE
       "issue_id_v" "issue"."id"%TYPE;
     BEGIN
+      DELETE FROM "expired_session";
       FOR "issue_id_v" IN
-        SELECT "id" FROM "issue" WHERE "closed" ISNULL
+        SELECT "id" FROM "open_issue"
       LOOP
         PERFORM "check_issue"("issue_id_v");
       END LOOP;
       FOR "issue_id_v" IN
-        SELECT "id" FROM "issue"
-        WHERE "frozen" NOTNULL
-        AND "closed" NOTNULL
-        AND "ranks_available" = FALSE FOR UPDATE
+        SELECT "id" FROM "issue_with_ranks_missing"
       LOOP
         PERFORM "calculate_ranks"("issue_id_v");
       END LOOP;
diff -r 8d021cb5eaf4 -r 23092eb00e16 lf_update.c
--- a/lf_update.c	Tue Oct 27 12:00:00 2009 +0100
+++ b/lf_update.c	Tue Nov 03 12:00:00 2009 +0100
@@ -4,11 +4,15 @@
 #include <libpq-fe.h>
 
 int main(int argc, char **argv) {
+
+  // variable declarations:
   int i, count;
   char *conninfo;
   PGconn *db;
   PGresult *list;
   PGresult *status;
+
+  // parse command line:
   if (argc == 0) return 1;
   if (argc == 1 || !strcmp(argv[1], "-h") || !strcmp(argv[1], "--help")) {
     FILE *out;
@@ -37,6 +41,8 @@
       strcat(conninfo, argv[i]);
     }
   }
+
+  // connect to database:
   db = PQconnectdb(conninfo);
   if (!db) {
     fprintf(stderr, "Error: Could not create database handle\n");
@@ -46,6 +52,22 @@
     fprintf(stderr, "Could not open connection:\n%s", PQerrorMessage(db));
     return 1;
   }
+
+  // delete expired sessions:
+  status = PQexec(db, "DELETE FROM \"expired_session\"");
+  if (!status) {
+    fprintf(stderr, "Error in pqlib while sending SQL command deleting expired sessions\n");
+    return 1;
+  }
+  if (
+    PQresultStatus(status) != PGRES_COMMAND_OK &&
+    PQresultStatus(status) != PGRES_TUPLES_OK
+  ) {
+    fprintf(stderr, "Error while executing SQL command deleting expired sessions:\n%s", PQresultErrorMessage(status));
+    return 1;
+  }
+
+  // update open issues:
   list = PQexec(db, "SELECT \"id\" FROM \"open_issue\"");
   if (!list) {
     fprintf(stderr, "Error in pqlib while sending SQL command selecting open issues\n");
@@ -62,6 +84,10 @@
     status = PQexecParams(
       db, "SELECT \"check_issue\"($1)", 1, NULL, params, NULL, NULL, 0
     );
+    if (!status) {
+      fprintf(stderr, "Error in pqlib while sending SQL command to call function \"check_issue\"(...):\n");
+      return 1;
+    }
     if (
       PQresultStatus(status) != PGRES_COMMAND_OK &&
       PQresultStatus(status) != PGRES_TUPLES_OK
@@ -72,6 +98,9 @@
     PQclear(status);
   }
   PQclear(list);
+
+  // calculate ranks after voting is finished:
+  // (NOTE: This is a seperate process to avoid long transactions with locking)
   list = PQexec(db, "SELECT \"id\" FROM \"issue_with_ranks_missing\"");
   if (!list) {
     fprintf(stderr, "Error in pqlib while sending SQL command selecting issues where ranks are missing\n");
@@ -88,6 +117,10 @@
     status = PQexecParams(
       db, "SELECT \"calculate_ranks\"($1)", 1, NULL, params, NULL, NULL, 0
     );
+    if (!status) {
+      fprintf(stderr, "Error in pqlib while sending SQL command to call function \"calculate_ranks\"(...):\n");
+      return 1;
+    }
     if (
       PQresultStatus(status) != PGRES_COMMAND_OK &&
       PQresultStatus(status) != PGRES_TUPLES_OK
@@ -98,6 +131,9 @@
     PQclear(status);
   }
   PQclear(list);
+
+  // cleanup and exit
   PQfinish(db);
   return 0;
+
 }