Commits

jbe  committed 23092eb

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

  • Participants
  • Parent commits 8d021cb
  • Tags beta2

Comments (0)

Files changed (2)

 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;
 
               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
               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
               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
               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
               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
               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
               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
               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
     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;
 #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;
       strcat(conninfo, argv[i]);
     }
   }
+
+  // connect to database:
   db = PQconnectdb(conninfo);
   if (!db) {
     fprintf(stderr, "Error: Could not create database handle\n");
     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");
     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
     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");
     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
     PQclear(status);
   }
   PQclear(list);
+
+  // cleanup and exit
   PQfinish(db);
   return 0;
+
 }