jbe@0: #include <stdlib.h>
jbe@0: #include <stdio.h>
jbe@0: #include <string.h>
jbe@532: #include <stdint.h>
jbe@0: #include <libpq-fe.h>
jbe@0: 
jbe@532: #define exec_sql_error(message) do { \
jbe@532:     fprintf(stderr, message ": %s\n%s", command, PQresultErrorMessage(res)); \
jbe@532:     goto exec_sql_error_clear; \
jbe@532:   } while (0)
jbe@345: 
jbe@532: int exec_sql(PGconn *db, PGresult **resptr, int *errptr, int onerow, char *command) {
jbe@532:   int count = 0;
jbe@532:   PGresult *res = PQexec(db, command);
jbe@532:   if (!res) {
jbe@532:     fprintf(stderr, "Error in pqlib while sending the following SQL command: %s\n", command);
jbe@532:     goto exec_sql_error_exit;
jbe@532:   }
jbe@532:   if (
jbe@532:     PQresultStatus(res) != PGRES_COMMAND_OK &&
jbe@532:     PQresultStatus(res) != PGRES_TUPLES_OK
jbe@532:   ) exec_sql_error("Error while executing the following SQL command");
jbe@532:   if (resptr) {
jbe@532:     if (PQresultStatus(res) != PGRES_TUPLES_OK) exec_sql_error("The following SQL command returned no result");
jbe@532:     count = PQntuples(res);
jbe@532:     if (count < 0) exec_sql_error("The following SQL command returned too many rows");
jbe@532:     if (onerow) {
jbe@532:       if      (count < 1) exec_sql_error("The following SQL command returned less than one row");
jbe@532:       else if (count > 1) exec_sql_error("The following SQL command returned more than one row");
jbe@532:     }
jbe@532:     *resptr = res;
jbe@532:   } else {
jbe@532:     PQclear(res);
jbe@532:   }
jbe@532:   return count;
jbe@532:   exec_sql_error_clear:
jbe@532:   PQclear(res);
jbe@532:   exec_sql_error_exit:
jbe@532:   if (resptr) *resptr = NULL;
jbe@532:   if (errptr) *errptr = 1;
jbe@532:   return -1;
jbe@345: }
jbe@345: 
jbe@0: int main(int argc, char **argv) {
jbe@1: 
jbe@1:   // variable declarations:
jbe@532:   int err = 0;               /* set to 1 if any error occured */
jbe@532:   int admission_failed = 0;  /* set to 1 if error occurred during admission */
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@532:     fprintf(out, "see http://www.postgresql.org/docs/9.6/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@532:     size_t len = 0, seglen;
jbe@532:     for (i=1; i<argc; i++) {
jbe@532:       seglen = strlen(argv[i]) + 1;
jbe@532:       if (seglen >= SIZE_MAX/2 || len >= SIZE_MAX/2) {
jbe@532:         fprintf(stderr, "Error: Command line arguments too long\n");
jbe@532:         return 1;
jbe@532:       }
jbe@532:       len += seglen;
jbe@532:     }
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@532:   exec_sql(db, NULL, &err, 0, "DELETE FROM \"expired_session\"");
jbe@532: 
jbe@532:   // delete expired tokens and authorization codes:
jbe@532:   exec_sql(db, NULL, &err, 0, "DELETE FROM \"expired_token\"");
jbe@532:  
jbe@532:   // delete expired snapshots:
jbe@532:   exec_sql(db, NULL, &err, 0, "DELETE FROM \"expired_snapshot\"");
jbe@235:  
jbe@184:   // check member activity:
jbe@532:   exec_sql(db, NULL, &err, 0, "SET TRANSACTION ISOLATION LEVEL READ COMMITTED; SELECT \"check_activity\"()");
jbe@103: 
jbe@4:   // calculate member counts:
jbe@532:   exec_sql(db, NULL, &err, 0, "SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; SELECT \"calculate_member_counts\"()");
jbe@532: 
jbe@532:   // issue admission:
jbe@532:   count = exec_sql(db, &res, &err, 0, "SET TRANSACTION ISOLATION LEVEL READ COMMITTED; SELECT \"id\" FROM \"area_with_unaccepted_issues\"");
jbe@532:   if (!res) admission_failed = 1;
jbe@532:   else {
jbe@532:     char *area_id, *escaped_area_id, *cmd;
jbe@532:     PGresult *res2;
jbe@532:     for (i=0; i<count; i++) {
jbe@532:       area_id = PQgetvalue(res, i, 0);
jbe@532:       escaped_area_id = PQescapeLiteral(db, area_id, strlen(area_id));
jbe@532:       if (!escaped_area_id) {
jbe@532:         fprintf(stderr, "Could not escape literal in memory.\n");
jbe@532:         err = admission_failed = 1;
jbe@532:         continue;
jbe@532:       }
jbe@532:       if (asprintf(&cmd, "SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; SELECT \"take_snapshot\"(NULL, %s)", escaped_area_id) < 0) {
jbe@532:         fprintf(stderr, "Could not prepare query string in memory.\n");
jbe@532:         err = admission_failed = 1;
jbe@532:         PQfreemem(escaped_area_id);
jbe@532:         continue;
jbe@532:       }
jbe@532:       exec_sql(db, &res2, &err, 1, cmd);
jbe@532:       free(cmd);
jbe@532:       if (!res2) admission_failed = 1;
jbe@532:       else {
jbe@532:         char *snapshot_id, *escaped_snapshot_id;
jbe@532:         int j, count2;
jbe@532:         snapshot_id = PQgetvalue(res, 0, 0);
jbe@532:         escaped_snapshot_id = PQescapeLiteral(db, snapshot_id, strlen(snapshot_id));
jbe@532:         PQclear(res2);
jbe@532:         if (!escaped_snapshot_id) {
jbe@532:           fprintf(stderr, "Could not escape literal in memory.\n");
jbe@532:           err = admission_failed = 1;
jbe@532:           goto area_admission_cleanup;
jbe@532:         }
jbe@532:         if (asprintf(&cmd, "SET TRANSACTION ISOLATION LEVEL READ COMMITTED; SELECT \"issue_id\" FROM \"snapshot_issue\" WHERE \"snapshot_id\" = %s", escaped_snapshot_id) < 0) {
jbe@532:           fprintf(stderr, "Could not prepare query string in memory.\n");
jbe@532:           err = admission_failed = 1;
jbe@532:           PQfreemem(escaped_snapshot_id);
jbe@532:           goto area_admission_cleanup;
jbe@532:         }
jbe@532:         PQfreemem(escaped_snapshot_id);
jbe@532:         count2 = exec_sql(db, &res2, &err, 0, cmd);
jbe@532:         free(cmd);
jbe@532:         if (!res2) admission_failed = 1;
jbe@532:         else {
jbe@532:           char *issue_id, *escaped_issue_id;
jbe@532:           for (j=0; j<count2; j++) {
jbe@532:             issue_id = PQgetvalue(res2, j, 0);
jbe@532:             escaped_issue_id = PQescapeLiteral(db, issue_id, strlen(issue_id));
jbe@532:             if (!escaped_issue_id) {
jbe@532:               fprintf(stderr, "Could not escape literal in memory.\n");
jbe@532:               err = admission_failed = 1;
jbe@532:               continue;
jbe@532:             }
jbe@532:             if (asprintf(&cmd, "SET TRANSACTION ISOLATION LEVEL READ COMMITTED; SELECT \"finish_snapshot\"(%s)", escaped_issue_id) < 0) {
jbe@532:               fprintf(stderr, "Could not prepare query string in memory.\n");
jbe@532:               err = admission_failed = 1;
jbe@532:               PQfreemem(escaped_issue_id);
jbe@532:               continue;
jbe@532:             }
jbe@532:             PQfreemem(escaped_issue_id);
jbe@532:             if (exec_sql(db, NULL, &err, 0, cmd) < 0) admission_failed = 1;
jbe@532:             free(cmd);
jbe@532:           }
jbe@532:           PQclear(res2);
jbe@532:         }
jbe@532:         if (!admission_failed) {
jbe@532:           if (asprintf(&cmd, "SET TRANSACTION ISOLATION LEVEL READ COMMITTED; SELECT \"issue_admission\"(%s)", escaped_area_id) < 0) {
jbe@532:             fprintf(stderr, "Could not prepare query string in memory.\n");
jbe@532:             err = admission_failed = 1;
jbe@532:             goto area_admission_cleanup;
jbe@532:           }
jbe@532:         }
jbe@532:         while (1) {
jbe@532:           exec_sql(db, &res2, &err, 1, cmd);
jbe@532:           if (!res2) {
jbe@532:             admission_failed = 1;
jbe@532:             break;
jbe@532:           }
jbe@532:           if (PQgetvalue(res2, 0, 0)[0] != 't') {
jbe@532:             PQclear(res2);
jbe@532:             break;
jbe@532:           }
jbe@532:           PQclear(res2);
jbe@532:         }
jbe@532:       }
jbe@532:       area_admission_cleanup:
jbe@532:       PQfreemem(escaped_area_id);
jbe@532:     }
jbe@334:     PQclear(res);
jbe@4:   }
jbe@4: 
jbe@1:   // update open issues:
jbe@532:   count = exec_sql(
jbe@532:     db, &res, &err, 0,
jbe@532:     admission_failed ?
jbe@532:     "SELECT \"id\" FROM \"open_issue\" WHERE \"state\" != 'admission'::\"issue_state\"" :
jbe@532:     "SELECT \"id\" FROM \"open_issue\""
jbe@532:   );
jbe@532:   for (i=0; i<count; i++) {
jbe@532:     char *issue_id, *escaped_issue_id;
jbe@532:     PGresult *res2, *old_res2;
jbe@532:     int j;
jbe@532:     issue_id = PQgetvalue(res, i, 0);
jbe@532:     escaped_issue_id = PQescapeLiteral(db, issue_id, strlen(issue_id));
jbe@532:     if (!escaped_issue_id) {
jbe@532:       fprintf(stderr, "Could not escape literal in memory.\n");
jbe@532:       err = 1;
jbe@532:       continue;
jbe@532:     }
jbe@532:     old_res2 = NULL;
jbe@532:     for (j=0; ; j++) {
jbe@532:       if (j >= 20) {  // safety to avoid endless loops
jbe@532:         fprintf(stderr, "Function \"check_issue\"(...) returned non-null value too often.\n");
jbe@357:         err = 1;
jbe@532:         if (j > 0) PQclear(old_res2);
jbe@357:         break;
jbe@357:       }
jbe@532:       if (j == 0) {
jbe@532:         char *cmd;
jbe@532:         if (asprintf(&cmd, "SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; SELECT \"check_issue\"(%s, NULL)", escaped_issue_id) < 0) {
jbe@532:           fprintf(stderr, "Could not prepare query string in memory.\n");
jbe@334:           err = 1;
jbe@334:           break;
jbe@334:         }
jbe@532:         exec_sql(db, &res2, &err, 1, cmd);
jbe@532:         free(cmd);
jbe@532:       } else {
jbe@532:         char *persist, *escaped_persist, *cmd;
jbe@532:         persist = PQgetvalue(old_res2, 0, 0);
jbe@532:         escaped_persist = PQescapeLiteral(db, persist, strlen(persist));
jbe@532:         if (!escaped_persist) {
jbe@532:           fprintf(stderr, "Could not escape literal in memory.\n");
jbe@532:           err = 1;
jbe@532:           PQclear(old_res2);
jbe@532:           break;
jbe@532:         }
jbe@532:         if (asprintf(&cmd, "SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; SELECT \"check_issue\"(%s, %s::\"check_issue_persistence\")", escaped_issue_id, escaped_persist) < 0) {
jbe@532:           PQfreemem(escaped_persist);
jbe@532:           fprintf(stderr, "Could not prepare query string in memory.\n");
jbe@532:           err = 1;
jbe@334:           PQclear(old_res2);
jbe@532:           break;
jbe@334:         }
jbe@532:         PQfreemem(escaped_persist);
jbe@532:         exec_sql(db, &res2, &err, 1, cmd);
jbe@532:         free(cmd);
jbe@532:         PQclear(old_res2);
jbe@65:       }
jbe@532:       if (!res2) break;
jbe@532:       if (PQgetisnull(res2, 0, 0)) {
jbe@532:         PQclear(res2);
jbe@532:         break;
jbe@532:       }
jbe@532:       old_res2 = res2;
jbe@65:     }
jbe@532:     PQfreemem(escaped_issue_id);
jbe@0:   }
jbe@532:   if (res) PQclear(res);
jbe@1: 
jbe@375:   // cleanup and exit:
jbe@0:   PQfinish(db);
jbe@65:   return err;
jbe@1: 
jbe@0: }