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
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 }