jbe@0: #include <stdlib.h>
jbe@0: #include <stdio.h>
jbe@0: #include <string.h>
jbe@0: #include <libpq-fe.h>
jbe@0: 
jbe@345: static char *escapeLiteral(PGconn *conn, const char *str, size_t len) {
jbe@345:   // provides compatibility for PostgreSQL versions prior 9.0
jbe@345:   // in future: return PQescapeLiteral(conn, str, len);
jbe@345:   char *res;
jbe@345:   size_t res_len;
jbe@345:   res = malloc(2*len+3);
jbe@357:   if (!res) return NULL;
jbe@345:   res[0] = '\'';
jbe@345:   res_len = PQescapeStringConn(conn, res+1, str, len, NULL);
jbe@345:   res[res_len+1] = '\'';
jbe@345:   res[res_len+2] = 0;
jbe@345:   return res;
jbe@345: }
jbe@345: 
jbe@345: static void freemem(void *ptr) {
jbe@345:   // to be used for "escapeLiteral" function
jbe@345:   // provides compatibility for PostgreSQL versions prior 9.0
jbe@345:   // in future: PQfreemem(ptr);
jbe@345:   free(ptr);
jbe@345: }
jbe@345: 
jbe@0: int main(int argc, char **argv) {
jbe@1: 
jbe@1:   // variable declarations:
jbe@65:   int err = 0;
jbe@0:   int i, count;
jbe@0:   char *conninfo;
jbe@0:   PGconn *db;
jbe@334:   PGresult *res;
jbe@1: 
jbe@1:   // parse command line:
jbe@0:   if (argc == 0) return 1;
jbe@0:   if (argc == 1 || !strcmp(argv[1], "-h") || !strcmp(argv[1], "--help")) {
jbe@0:     FILE *out;
jbe@0:     out = argc == 1 ? stderr : stdout;
jbe@362:     fprintf(out, "\n");
jbe@362:     fprintf(out, "Usage: %s <conninfo>\n", argv[0]);
jbe@362:     fprintf(out, "\n");
jbe@362:     fprintf(out, "<conninfo> is specified by PostgreSQL's libpq,\n");
jbe@362:     fprintf(out, "see http://www.postgresql.org/docs/9.1/static/libpq-connect.html\n");
jbe@362:     fprintf(out, "\n");
jbe@362:     fprintf(out, "Example: %s dbname=liquid_feedback\n", argv[0]);
jbe@362:     fprintf(out, "\n");
jbe@0:     return argc == 1 ? 1 : 0;
jbe@0:   }
jbe@0:   {
jbe@0:     size_t len = 0;
jbe@0:     for (i=1; i<argc; i++) len += strlen(argv[i]) + 1;
jbe@0:     conninfo = malloc(len * sizeof(char));
jbe@0:     if (!conninfo) {
jbe@0:       fprintf(stderr, "Error: Could not allocate memory for conninfo string\n");
jbe@0:       return 1;
jbe@0:     }
jbe@0:     conninfo[0] = 0;
jbe@0:     for (i=1; i<argc; i++) {
jbe@0:       if (i>1) strcat(conninfo, " ");
jbe@0:       strcat(conninfo, argv[i]);
jbe@0:     }
jbe@0:   }
jbe@1: 
jbe@1:   // connect to database:
jbe@0:   db = PQconnectdb(conninfo);
jbe@0:   if (!db) {
jbe@0:     fprintf(stderr, "Error: Could not create database handle\n");
jbe@0:     return 1;
jbe@0:   }
jbe@0:   if (PQstatus(db) != CONNECTION_OK) {
jbe@0:     fprintf(stderr, "Could not open connection:\n%s", PQerrorMessage(db));
jbe@0:     return 1;
jbe@0:   }
jbe@1: 
jbe@235:   // delete expired sessions:
jbe@334:   res = PQexec(db, "DELETE FROM \"expired_session\"");
jbe@334:   if (!res) {
jbe@235:     fprintf(stderr, "Error in pqlib while sending SQL command deleting expired sessions\n");
jbe@235:     err = 1;
jbe@235:   } else if (
jbe@334:     PQresultStatus(res) != PGRES_COMMAND_OK &&
jbe@334:     PQresultStatus(res) != PGRES_TUPLES_OK
jbe@235:   ) {
jbe@334:     fprintf(stderr, "Error while executing SQL command deleting expired sessions:\n%s", PQresultErrorMessage(res));
jbe@235:     err = 1;
jbe@334:     PQclear(res);
jbe@235:   } else {
jbe@334:     PQclear(res);
jbe@235:   }
jbe@235:  
jbe@184:   // check member activity:
jbe@334:   res = PQexec(db, "SET TRANSACTION ISOLATION LEVEL READ COMMITTED; SELECT \"check_activity\"()");
jbe@334:   if (!res) {
jbe@184:     fprintf(stderr, "Error in pqlib while sending SQL command checking member activity\n");
jbe@103:     err = 1;
jbe@103:   } else if (
jbe@334:     PQresultStatus(res) != PGRES_COMMAND_OK &&
jbe@334:     PQresultStatus(res) != PGRES_TUPLES_OK
jbe@103:   ) {
jbe@334:     fprintf(stderr, "Error while executing SQL command checking member activity:\n%s", PQresultErrorMessage(res));
jbe@103:     err = 1;
jbe@334:     PQclear(res);
jbe@103:   } else {
jbe@334:     PQclear(res);
jbe@103:   }
jbe@103: 
jbe@4:   // calculate member counts:
jbe@334:   res = PQexec(db, "SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; SELECT \"calculate_member_counts\"()");
jbe@334:   if (!res) {
jbe@4:     fprintf(stderr, "Error in pqlib while sending SQL command calculating member counts\n");
jbe@65:     err = 1;
jbe@65:   } else if (
jbe@334:     PQresultStatus(res) != PGRES_COMMAND_OK &&
jbe@334:     PQresultStatus(res) != PGRES_TUPLES_OK
jbe@4:   ) {
jbe@334:     fprintf(stderr, "Error while executing SQL command calculating member counts:\n%s", PQresultErrorMessage(res));
jbe@65:     err = 1;
jbe@334:     PQclear(res);
jbe@65:   } else {
jbe@334:     PQclear(res);
jbe@4:   }
jbe@4: 
jbe@1:   // update open issues:
jbe@334:   res = PQexec(db, "SELECT \"id\" FROM \"open_issue\"");
jbe@334:   if (!res) {
jbe@0:     fprintf(stderr, "Error in pqlib while sending SQL command selecting open issues\n");
jbe@65:     err = 1;
jbe@334:   } else if (PQresultStatus(res) != PGRES_TUPLES_OK) {
jbe@334:     fprintf(stderr, "Error while executing SQL command selecting open issues:\n%s", PQresultErrorMessage(res));
jbe@65:     err = 1;
jbe@334:     PQclear(res);
jbe@65:   } else {
jbe@334:     count = PQntuples(res);
jbe@65:     for (i=0; i<count; i++) {
jbe@334:       char *issue_id, *escaped_issue_id;
jbe@334:       PGresult *res2, *old_res2;
jbe@334:       int j;
jbe@334:       issue_id = PQgetvalue(res, i, 0);
jbe@345:       escaped_issue_id = escapeLiteral(db, issue_id, strlen(issue_id));
jbe@357:       if (!escaped_issue_id) {
jbe@357:         fprintf(stderr, "Could not escape literal in memory.\n");
jbe@357:         err = 1;
jbe@357:         break;
jbe@357:       }
jbe@334:       old_res2 = NULL;
jbe@334:       for (j=0; ; j++) {
jbe@334:         if (j >= 20) {  // safety to avoid endless loops
jbe@334:           fprintf(stderr, "Function \"check_issue\"(...) returned non-null value too often.\n");
jbe@334:           err = 1;
jbe@334:           if (j > 0) PQclear(old_res2);
jbe@334:           break;
jbe@334:         }
jbe@334:         if (j == 0) {
jbe@334:           char *cmd;
jbe@334:           if (asprintf(&cmd, "SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; SELECT \"check_issue\"(%s, NULL)", escaped_issue_id) < 0) {
jbe@334:             fprintf(stderr, "Could not prepare query string in memory.\n");
jbe@334:             err = 1;
jbe@334:             break;
jbe@334:           }
jbe@334:           res2 = PQexec(db, cmd);
jbe@334:           free(cmd);
jbe@334:         } else {
jbe@334:           char *persist, *escaped_persist, *cmd;
jbe@334:           persist = PQgetvalue(old_res2, 0, 0);
jbe@345:           escaped_persist = escapeLiteral(db, persist, strlen(persist));
jbe@357:           if (!escaped_persist) {
jbe@357:             fprintf(stderr, "Could not escape literal in memory.\n");
jbe@357:             err = 1;
jbe@357:             PQclear(old_res2);
jbe@357:             break;
jbe@357:           }
jbe@334:           if (asprintf(&cmd, "SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; SELECT \"check_issue\"(%s, %s::\"check_issue_persistence\")", escaped_issue_id, escaped_persist) < 0) {
jbe@345:             freemem(escaped_persist);
jbe@334:             fprintf(stderr, "Could not prepare query string in memory.\n");
jbe@334:             err = 1;
jbe@334:             PQclear(old_res2);
jbe@334:             break;
jbe@334:           }
jbe@345:           freemem(escaped_persist);
jbe@334:           res2 = PQexec(db, cmd);
jbe@334:           free(cmd);
jbe@334:           PQclear(old_res2);
jbe@334:         }
jbe@334:         if (!res2) {
jbe@334:           fprintf(stderr, "Error in pqlib while sending SQL command to call function \"check_issue\"(...):\n");
jbe@334:           err = 1;
jbe@334:           break;
jbe@334:         } else if (
jbe@334:           PQresultStatus(res2) != PGRES_COMMAND_OK &&
jbe@334:           PQresultStatus(res2) != PGRES_TUPLES_OK
jbe@334:         ) {
jbe@334:           fprintf(stderr, "Error while calling SQL function \"check_issue\"(...):\n%s", PQresultErrorMessage(res2));
jbe@334:           err = 1;
jbe@334:           PQclear(res2);
jbe@334:           break;
jbe@334:         } else {
jbe@334:           if (PQntuples(res2) >= 1 && !PQgetisnull(res2, 0, 0)) {
jbe@334:             old_res2 = res2;
jbe@334:           } else {
jbe@334:             PQclear(res2);
jbe@334:             break;
jbe@334:           }
jbe@334:         }
jbe@65:       }
jbe@345:       freemem(escaped_issue_id);
jbe@65:     }
jbe@334:     PQclear(res);
jbe@0:   }
jbe@1: 
jbe@375:   // cleanup and exit:
jbe@0:   PQfinish(db);
jbe@65:   return err;
jbe@1: 
jbe@0: }