liquid_feedback_core

changeset 1:23092eb00e16 beta2

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
author jbe
date Tue Nov 03 12:00:00 2009 +0100 (2009-11-03)
parents 8d021cb5eaf4
children d45919d791ff
files core.sql lf_update.c
line diff
     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;
     2.1 --- a/lf_update.c	Tue Oct 27 12:00:00 2009 +0100
     2.2 +++ b/lf_update.c	Tue Nov 03 12:00:00 2009 +0100
     2.3 @@ -4,11 +4,15 @@
     2.4  #include <libpq-fe.h>
     2.5  
     2.6  int main(int argc, char **argv) {
     2.7 +
     2.8 +  // variable declarations:
     2.9    int i, count;
    2.10    char *conninfo;
    2.11    PGconn *db;
    2.12    PGresult *list;
    2.13    PGresult *status;
    2.14 +
    2.15 +  // parse command line:
    2.16    if (argc == 0) return 1;
    2.17    if (argc == 1 || !strcmp(argv[1], "-h") || !strcmp(argv[1], "--help")) {
    2.18      FILE *out;
    2.19 @@ -37,6 +41,8 @@
    2.20        strcat(conninfo, argv[i]);
    2.21      }
    2.22    }
    2.23 +
    2.24 +  // connect to database:
    2.25    db = PQconnectdb(conninfo);
    2.26    if (!db) {
    2.27      fprintf(stderr, "Error: Could not create database handle\n");
    2.28 @@ -46,6 +52,22 @@
    2.29      fprintf(stderr, "Could not open connection:\n%s", PQerrorMessage(db));
    2.30      return 1;
    2.31    }
    2.32 +
    2.33 +  // delete expired sessions:
    2.34 +  status = PQexec(db, "DELETE FROM \"expired_session\"");
    2.35 +  if (!status) {
    2.36 +    fprintf(stderr, "Error in pqlib while sending SQL command deleting expired sessions\n");
    2.37 +    return 1;
    2.38 +  }
    2.39 +  if (
    2.40 +    PQresultStatus(status) != PGRES_COMMAND_OK &&
    2.41 +    PQresultStatus(status) != PGRES_TUPLES_OK
    2.42 +  ) {
    2.43 +    fprintf(stderr, "Error while executing SQL command deleting expired sessions:\n%s", PQresultErrorMessage(status));
    2.44 +    return 1;
    2.45 +  }
    2.46 +
    2.47 +  // update open issues:
    2.48    list = PQexec(db, "SELECT \"id\" FROM \"open_issue\"");
    2.49    if (!list) {
    2.50      fprintf(stderr, "Error in pqlib while sending SQL command selecting open issues\n");
    2.51 @@ -62,6 +84,10 @@
    2.52      status = PQexecParams(
    2.53        db, "SELECT \"check_issue\"($1)", 1, NULL, params, NULL, NULL, 0
    2.54      );
    2.55 +    if (!status) {
    2.56 +      fprintf(stderr, "Error in pqlib while sending SQL command to call function \"check_issue\"(...):\n");
    2.57 +      return 1;
    2.58 +    }
    2.59      if (
    2.60        PQresultStatus(status) != PGRES_COMMAND_OK &&
    2.61        PQresultStatus(status) != PGRES_TUPLES_OK
    2.62 @@ -72,6 +98,9 @@
    2.63      PQclear(status);
    2.64    }
    2.65    PQclear(list);
    2.66 +
    2.67 +  // calculate ranks after voting is finished:
    2.68 +  // (NOTE: This is a seperate process to avoid long transactions with locking)
    2.69    list = PQexec(db, "SELECT \"id\" FROM \"issue_with_ranks_missing\"");
    2.70    if (!list) {
    2.71      fprintf(stderr, "Error in pqlib while sending SQL command selecting issues where ranks are missing\n");
    2.72 @@ -88,6 +117,10 @@
    2.73      status = PQexecParams(
    2.74        db, "SELECT \"calculate_ranks\"($1)", 1, NULL, params, NULL, NULL, 0
    2.75      );
    2.76 +    if (!status) {
    2.77 +      fprintf(stderr, "Error in pqlib while sending SQL command to call function \"calculate_ranks\"(...):\n");
    2.78 +      return 1;
    2.79 +    }
    2.80      if (
    2.81        PQresultStatus(status) != PGRES_COMMAND_OK &&
    2.82        PQresultStatus(status) != PGRES_TUPLES_OK
    2.83 @@ -98,6 +131,9 @@
    2.84      PQclear(status);
    2.85    }
    2.86    PQclear(list);
    2.87 +
    2.88 +  // cleanup and exit
    2.89    PQfinish(db);
    2.90    return 0;
    2.91 +
    2.92  }

Impressum / About Us