1.1 --- a/lf_update.c	Sun Aug 21 17:31:44 2016 +0200
     1.2 +++ b/lf_update.c	Thu Mar 30 19:42:38 2017 +0200
     1.3 @@ -1,33 +1,51 @@
     1.4  #include <stdlib.h>
     1.5  #include <stdio.h>
     1.6  #include <string.h>
     1.7 +#include <stdint.h>
     1.8  #include <libpq-fe.h>
     1.9  
    1.10 -static char *escapeLiteral(PGconn *conn, const char *str, size_t len) {
    1.11 -  // provides compatibility for PostgreSQL versions prior 9.0
    1.12 -  // in future: return PQescapeLiteral(conn, str, len);
    1.13 -  char *res;
    1.14 -  size_t res_len;
    1.15 -  res = malloc(2*len+3);
    1.16 -  if (!res) return NULL;
    1.17 -  res[0] = '\'';
    1.18 -  res_len = PQescapeStringConn(conn, res+1, str, len, NULL);
    1.19 -  res[res_len+1] = '\'';
    1.20 -  res[res_len+2] = 0;
    1.21 -  return res;
    1.22 -}
    1.23 +#define exec_sql_error(message) do { \
    1.24 +    fprintf(stderr, message ": %s\n%s", command, PQresultErrorMessage(res)); \
    1.25 +    goto exec_sql_error_clear; \
    1.26 +  } while (0)
    1.27  
    1.28 -static void freemem(void *ptr) {
    1.29 -  // to be used for "escapeLiteral" function
    1.30 -  // provides compatibility for PostgreSQL versions prior 9.0
    1.31 -  // in future: PQfreemem(ptr);
    1.32 -  free(ptr);
    1.33 +int exec_sql(PGconn *db, PGresult **resptr, int *errptr, int onerow, char *command) {
    1.34 +  int count = 0;
    1.35 +  PGresult *res = PQexec(db, command);
    1.36 +  if (!res) {
    1.37 +    fprintf(stderr, "Error in pqlib while sending the following SQL command: %s\n", command);
    1.38 +    goto exec_sql_error_exit;
    1.39 +  }
    1.40 +  if (
    1.41 +    PQresultStatus(res) != PGRES_COMMAND_OK &&
    1.42 +    PQresultStatus(res) != PGRES_TUPLES_OK
    1.43 +  ) exec_sql_error("Error while executing the following SQL command");
    1.44 +  if (resptr) {
    1.45 +    if (PQresultStatus(res) != PGRES_TUPLES_OK) exec_sql_error("The following SQL command returned no result");
    1.46 +    count = PQntuples(res);
    1.47 +    if (count < 0) exec_sql_error("The following SQL command returned too many rows");
    1.48 +    if (onerow) {
    1.49 +      if      (count < 1) exec_sql_error("The following SQL command returned less than one row");
    1.50 +      else if (count > 1) exec_sql_error("The following SQL command returned more than one row");
    1.51 +    }
    1.52 +    *resptr = res;
    1.53 +  } else {
    1.54 +    PQclear(res);
    1.55 +  }
    1.56 +  return count;
    1.57 +  exec_sql_error_clear:
    1.58 +  PQclear(res);
    1.59 +  exec_sql_error_exit:
    1.60 +  if (resptr) *resptr = NULL;
    1.61 +  if (errptr) *errptr = 1;
    1.62 +  return -1;
    1.63  }
    1.64  
    1.65  int main(int argc, char **argv) {
    1.66  
    1.67    // variable declarations:
    1.68 -  int err = 0;
    1.69 +  int err = 0;               /* set to 1 if any error occured */
    1.70 +  int admission_failed = 0;  /* set to 1 if error occurred during admission */
    1.71    int i, count;
    1.72    char *conninfo;
    1.73    PGconn *db;
    1.74 @@ -42,15 +60,22 @@
    1.75      fprintf(out, "Usage: %s <conninfo>\n", argv[0]);
    1.76      fprintf(out, "\n");
    1.77      fprintf(out, "<conninfo> is specified by PostgreSQL's libpq,\n");
    1.78 -    fprintf(out, "see http://www.postgresql.org/docs/9.1/static/libpq-connect.html\n");
    1.79 +    fprintf(out, "see http://www.postgresql.org/docs/9.6/static/libpq-connect.html\n");
    1.80      fprintf(out, "\n");
    1.81      fprintf(out, "Example: %s dbname=liquid_feedback\n", argv[0]);
    1.82      fprintf(out, "\n");
    1.83      return argc == 1 ? 1 : 0;
    1.84    }
    1.85    {
    1.86 -    size_t len = 0;
    1.87 -    for (i=1; i<argc; i++) len += strlen(argv[i]) + 1;
    1.88 +    size_t len = 0, seglen;
    1.89 +    for (i=1; i<argc; i++) {
    1.90 +      seglen = strlen(argv[i]) + 1;
    1.91 +      if (seglen >= SIZE_MAX/2 || len >= SIZE_MAX/2) {
    1.92 +        fprintf(stderr, "Error: Command line arguments too long\n");
    1.93 +        return 1;
    1.94 +      }
    1.95 +      len += seglen;
    1.96 +    }
    1.97      conninfo = malloc(len * sizeof(char));
    1.98      if (!conninfo) {
    1.99        fprintf(stderr, "Error: Could not allocate memory for conninfo string\n");
   1.100 @@ -75,139 +100,179 @@
   1.101    }
   1.102  
   1.103    // delete expired sessions:
   1.104 -  res = PQexec(db, "DELETE FROM \"expired_session\"");
   1.105 -  if (!res) {
   1.106 -    fprintf(stderr, "Error in pqlib while sending SQL command deleting expired sessions\n");
   1.107 -    err = 1;
   1.108 -  } else if (
   1.109 -    PQresultStatus(res) != PGRES_COMMAND_OK &&
   1.110 -    PQresultStatus(res) != PGRES_TUPLES_OK
   1.111 -  ) {
   1.112 -    fprintf(stderr, "Error while executing SQL command deleting expired sessions:\n%s", PQresultErrorMessage(res));
   1.113 -    err = 1;
   1.114 -    PQclear(res);
   1.115 -  } else {
   1.116 -    PQclear(res);
   1.117 -  }
   1.118 +  exec_sql(db, NULL, &err, 0, "DELETE FROM \"expired_session\"");
   1.119 +
   1.120 +  // delete expired tokens and authorization codes:
   1.121 +  exec_sql(db, NULL, &err, 0, "DELETE FROM \"expired_token\"");
   1.122 + 
   1.123 +  // delete expired snapshots:
   1.124 +  exec_sql(db, NULL, &err, 0, "DELETE FROM \"expired_snapshot\"");
   1.125   
   1.126    // check member activity:
   1.127 -  res = PQexec(db, "SET TRANSACTION ISOLATION LEVEL READ COMMITTED; SELECT \"check_activity\"()");
   1.128 -  if (!res) {
   1.129 -    fprintf(stderr, "Error in pqlib while sending SQL command checking member activity\n");
   1.130 -    err = 1;
   1.131 -  } else if (
   1.132 -    PQresultStatus(res) != PGRES_COMMAND_OK &&
   1.133 -    PQresultStatus(res) != PGRES_TUPLES_OK
   1.134 -  ) {
   1.135 -    fprintf(stderr, "Error while executing SQL command checking member activity:\n%s", PQresultErrorMessage(res));
   1.136 -    err = 1;
   1.137 -    PQclear(res);
   1.138 -  } else {
   1.139 -    PQclear(res);
   1.140 -  }
   1.141 +  exec_sql(db, NULL, &err, 0, "SET TRANSACTION ISOLATION LEVEL READ COMMITTED; SELECT \"check_activity\"()");
   1.142  
   1.143    // calculate member counts:
   1.144 -  res = PQexec(db, "SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; SELECT \"calculate_member_counts\"()");
   1.145 -  if (!res) {
   1.146 -    fprintf(stderr, "Error in pqlib while sending SQL command calculating member counts\n");
   1.147 -    err = 1;
   1.148 -  } else if (
   1.149 -    PQresultStatus(res) != PGRES_COMMAND_OK &&
   1.150 -    PQresultStatus(res) != PGRES_TUPLES_OK
   1.151 -  ) {
   1.152 -    fprintf(stderr, "Error while executing SQL command calculating member counts:\n%s", PQresultErrorMessage(res));
   1.153 -    err = 1;
   1.154 -    PQclear(res);
   1.155 -  } else {
   1.156 +  exec_sql(db, NULL, &err, 0, "SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; SELECT \"calculate_member_counts\"()");
   1.157 +
   1.158 +  // issue admission:
   1.159 +  count = exec_sql(db, &res, &err, 0, "SET TRANSACTION ISOLATION LEVEL READ COMMITTED; SELECT \"id\" FROM \"area_with_unaccepted_issues\"");
   1.160 +  if (!res) admission_failed = 1;
   1.161 +  else {
   1.162 +    char *area_id, *escaped_area_id, *cmd;
   1.163 +    PGresult *res2;
   1.164 +    for (i=0; i<count; i++) {
   1.165 +      area_id = PQgetvalue(res, i, 0);
   1.166 +      escaped_area_id = PQescapeLiteral(db, area_id, strlen(area_id));
   1.167 +      if (!escaped_area_id) {
   1.168 +        fprintf(stderr, "Could not escape literal in memory.\n");
   1.169 +        err = admission_failed = 1;
   1.170 +        continue;
   1.171 +      }
   1.172 +      if (asprintf(&cmd, "SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; SELECT \"take_snapshot\"(NULL, %s)", escaped_area_id) < 0) {
   1.173 +        fprintf(stderr, "Could not prepare query string in memory.\n");
   1.174 +        err = admission_failed = 1;
   1.175 +        PQfreemem(escaped_area_id);
   1.176 +        continue;
   1.177 +      }
   1.178 +      exec_sql(db, &res2, &err, 1, cmd);
   1.179 +      free(cmd);
   1.180 +      if (!res2) admission_failed = 1;
   1.181 +      else {
   1.182 +        char *snapshot_id, *escaped_snapshot_id;
   1.183 +        int j, count2;
   1.184 +        snapshot_id = PQgetvalue(res, 0, 0);
   1.185 +        escaped_snapshot_id = PQescapeLiteral(db, snapshot_id, strlen(snapshot_id));
   1.186 +        PQclear(res2);
   1.187 +        if (!escaped_snapshot_id) {
   1.188 +          fprintf(stderr, "Could not escape literal in memory.\n");
   1.189 +          err = admission_failed = 1;
   1.190 +          goto area_admission_cleanup;
   1.191 +        }
   1.192 +        if (asprintf(&cmd, "SET TRANSACTION ISOLATION LEVEL READ COMMITTED; SELECT \"issue_id\" FROM \"snapshot_issue\" WHERE \"snapshot_id\" = %s", escaped_snapshot_id) < 0) {
   1.193 +          fprintf(stderr, "Could not prepare query string in memory.\n");
   1.194 +          err = admission_failed = 1;
   1.195 +          PQfreemem(escaped_snapshot_id);
   1.196 +          goto area_admission_cleanup;
   1.197 +        }
   1.198 +        PQfreemem(escaped_snapshot_id);
   1.199 +        count2 = exec_sql(db, &res2, &err, 0, cmd);
   1.200 +        free(cmd);
   1.201 +        if (!res2) admission_failed = 1;
   1.202 +        else {
   1.203 +          char *issue_id, *escaped_issue_id;
   1.204 +          for (j=0; j<count2; j++) {
   1.205 +            issue_id = PQgetvalue(res2, j, 0);
   1.206 +            escaped_issue_id = PQescapeLiteral(db, issue_id, strlen(issue_id));
   1.207 +            if (!escaped_issue_id) {
   1.208 +              fprintf(stderr, "Could not escape literal in memory.\n");
   1.209 +              err = admission_failed = 1;
   1.210 +              continue;
   1.211 +            }
   1.212 +            if (asprintf(&cmd, "SET TRANSACTION ISOLATION LEVEL READ COMMITTED; SELECT \"finish_snapshot\"(%s)", escaped_issue_id) < 0) {
   1.213 +              fprintf(stderr, "Could not prepare query string in memory.\n");
   1.214 +              err = admission_failed = 1;
   1.215 +              PQfreemem(escaped_issue_id);
   1.216 +              continue;
   1.217 +            }
   1.218 +            PQfreemem(escaped_issue_id);
   1.219 +            if (exec_sql(db, NULL, &err, 0, cmd) < 0) admission_failed = 1;
   1.220 +            free(cmd);
   1.221 +          }
   1.222 +          PQclear(res2);
   1.223 +        }
   1.224 +        if (!admission_failed) {
   1.225 +          if (asprintf(&cmd, "SET TRANSACTION ISOLATION LEVEL READ COMMITTED; SELECT \"issue_admission\"(%s)", escaped_area_id) < 0) {
   1.226 +            fprintf(stderr, "Could not prepare query string in memory.\n");
   1.227 +            err = admission_failed = 1;
   1.228 +            goto area_admission_cleanup;
   1.229 +          }
   1.230 +        }
   1.231 +        while (1) {
   1.232 +          exec_sql(db, &res2, &err, 1, cmd);
   1.233 +          if (!res2) {
   1.234 +            admission_failed = 1;
   1.235 +            break;
   1.236 +          }
   1.237 +          if (PQgetvalue(res2, 0, 0)[0] != 't') {
   1.238 +            PQclear(res2);
   1.239 +            break;
   1.240 +          }
   1.241 +          PQclear(res2);
   1.242 +        }
   1.243 +      }
   1.244 +      area_admission_cleanup:
   1.245 +      PQfreemem(escaped_area_id);
   1.246 +    }
   1.247      PQclear(res);
   1.248    }
   1.249  
   1.250    // update open issues:
   1.251 -  res = PQexec(db, "SELECT \"id\" FROM \"open_issue\"");
   1.252 -  if (!res) {
   1.253 -    fprintf(stderr, "Error in pqlib while sending SQL command selecting open issues\n");
   1.254 -    err = 1;
   1.255 -  } else if (PQresultStatus(res) != PGRES_TUPLES_OK) {
   1.256 -    fprintf(stderr, "Error while executing SQL command selecting open issues:\n%s", PQresultErrorMessage(res));
   1.257 -    err = 1;
   1.258 -    PQclear(res);
   1.259 -  } else {
   1.260 -    count = PQntuples(res);
   1.261 -    for (i=0; i<count; i++) {
   1.262 -      char *issue_id, *escaped_issue_id;
   1.263 -      PGresult *res2, *old_res2;
   1.264 -      int j;
   1.265 -      issue_id = PQgetvalue(res, i, 0);
   1.266 -      escaped_issue_id = escapeLiteral(db, issue_id, strlen(issue_id));
   1.267 -      if (!escaped_issue_id) {
   1.268 -        fprintf(stderr, "Could not escape literal in memory.\n");
   1.269 +  count = exec_sql(
   1.270 +    db, &res, &err, 0,
   1.271 +    admission_failed ?
   1.272 +    "SELECT \"id\" FROM \"open_issue\" WHERE \"state\" != 'admission'::\"issue_state\"" :
   1.273 +    "SELECT \"id\" FROM \"open_issue\""
   1.274 +  );
   1.275 +  for (i=0; i<count; i++) {
   1.276 +    char *issue_id, *escaped_issue_id;
   1.277 +    PGresult *res2, *old_res2;
   1.278 +    int j;
   1.279 +    issue_id = PQgetvalue(res, i, 0);
   1.280 +    escaped_issue_id = PQescapeLiteral(db, issue_id, strlen(issue_id));
   1.281 +    if (!escaped_issue_id) {
   1.282 +      fprintf(stderr, "Could not escape literal in memory.\n");
   1.283 +      err = 1;
   1.284 +      continue;
   1.285 +    }
   1.286 +    old_res2 = NULL;
   1.287 +    for (j=0; ; j++) {
   1.288 +      if (j >= 20) {  // safety to avoid endless loops
   1.289 +        fprintf(stderr, "Function \"check_issue\"(...) returned non-null value too often.\n");
   1.290          err = 1;
   1.291 +        if (j > 0) PQclear(old_res2);
   1.292          break;
   1.293        }
   1.294 -      old_res2 = NULL;
   1.295 -      for (j=0; ; j++) {
   1.296 -        if (j >= 20) {  // safety to avoid endless loops
   1.297 -          fprintf(stderr, "Function \"check_issue\"(...) returned non-null value too often.\n");
   1.298 +      if (j == 0) {
   1.299 +        char *cmd;
   1.300 +        if (asprintf(&cmd, "SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; SELECT \"check_issue\"(%s, NULL)", escaped_issue_id) < 0) {
   1.301 +          fprintf(stderr, "Could not prepare query string in memory.\n");
   1.302            err = 1;
   1.303 -          if (j > 0) PQclear(old_res2);
   1.304            break;
   1.305          }
   1.306 -        if (j == 0) {
   1.307 -          char *cmd;
   1.308 -          if (asprintf(&cmd, "SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; SELECT \"check_issue\"(%s, NULL)", escaped_issue_id) < 0) {
   1.309 -            fprintf(stderr, "Could not prepare query string in memory.\n");
   1.310 -            err = 1;
   1.311 -            break;
   1.312 -          }
   1.313 -          res2 = PQexec(db, cmd);
   1.314 -          free(cmd);
   1.315 -        } else {
   1.316 -          char *persist, *escaped_persist, *cmd;
   1.317 -          persist = PQgetvalue(old_res2, 0, 0);
   1.318 -          escaped_persist = escapeLiteral(db, persist, strlen(persist));
   1.319 -          if (!escaped_persist) {
   1.320 -            fprintf(stderr, "Could not escape literal in memory.\n");
   1.321 -            err = 1;
   1.322 -            PQclear(old_res2);
   1.323 -            break;
   1.324 -          }
   1.325 -          if (asprintf(&cmd, "SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; SELECT \"check_issue\"(%s, %s::\"check_issue_persistence\")", escaped_issue_id, escaped_persist) < 0) {
   1.326 -            freemem(escaped_persist);
   1.327 -            fprintf(stderr, "Could not prepare query string in memory.\n");
   1.328 -            err = 1;
   1.329 -            PQclear(old_res2);
   1.330 -            break;
   1.331 -          }
   1.332 -          freemem(escaped_persist);
   1.333 -          res2 = PQexec(db, cmd);
   1.334 -          free(cmd);
   1.335 +        exec_sql(db, &res2, &err, 1, cmd);
   1.336 +        free(cmd);
   1.337 +      } else {
   1.338 +        char *persist, *escaped_persist, *cmd;
   1.339 +        persist = PQgetvalue(old_res2, 0, 0);
   1.340 +        escaped_persist = PQescapeLiteral(db, persist, strlen(persist));
   1.341 +        if (!escaped_persist) {
   1.342 +          fprintf(stderr, "Could not escape literal in memory.\n");
   1.343 +          err = 1;
   1.344 +          PQclear(old_res2);
   1.345 +          break;
   1.346 +        }
   1.347 +        if (asprintf(&cmd, "SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; SELECT \"check_issue\"(%s, %s::\"check_issue_persistence\")", escaped_issue_id, escaped_persist) < 0) {
   1.348 +          PQfreemem(escaped_persist);
   1.349 +          fprintf(stderr, "Could not prepare query string in memory.\n");
   1.350 +          err = 1;
   1.351            PQclear(old_res2);
   1.352 +          break;
   1.353          }
   1.354 -        if (!res2) {
   1.355 -          fprintf(stderr, "Error in pqlib while sending SQL command to call function \"check_issue\"(...):\n");
   1.356 -          err = 1;
   1.357 -          break;
   1.358 -        } else if (
   1.359 -          PQresultStatus(res2) != PGRES_COMMAND_OK &&
   1.360 -          PQresultStatus(res2) != PGRES_TUPLES_OK
   1.361 -        ) {
   1.362 -          fprintf(stderr, "Error while calling SQL function \"check_issue\"(...):\n%s", PQresultErrorMessage(res2));
   1.363 -          err = 1;
   1.364 -          PQclear(res2);
   1.365 -          break;
   1.366 -        } else {
   1.367 -          if (PQntuples(res2) >= 1 && !PQgetisnull(res2, 0, 0)) {
   1.368 -            old_res2 = res2;
   1.369 -          } else {
   1.370 -            PQclear(res2);
   1.371 -            break;
   1.372 -          }
   1.373 -        }
   1.374 +        PQfreemem(escaped_persist);
   1.375 +        exec_sql(db, &res2, &err, 1, cmd);
   1.376 +        free(cmd);
   1.377 +        PQclear(old_res2);
   1.378        }
   1.379 -      freemem(escaped_issue_id);
   1.380 +      if (!res2) break;
   1.381 +      if (PQgetisnull(res2, 0, 0)) {
   1.382 +        PQclear(res2);
   1.383 +        break;
   1.384 +      }
   1.385 +      old_res2 = res2;
   1.386      }
   1.387 -    PQclear(res);
   1.388 +    PQfreemem(escaped_issue_id);
   1.389    }
   1.390 +  if (res) PQclear(res);
   1.391  
   1.392    // cleanup and exit:
   1.393    PQfinish(db);