liquid_feedback_core

annotate lf_update.c @ 542:f5c5d2b12726

find changesets by author, revision, files, or words in the commit message
Removed settings tables
author jbe
date Fri Jul 14 20:27:33 2017 +0200 (2017-07-14)
parents 5855ff9e5c8f
children 48ea1c309928
rev   line source
jbe@0 1 #include <stdlib.h>
jbe@0 2 #include <stdio.h>
jbe@0 3 #include <string.h>
jbe@532 4 #include <stdint.h>
jbe@0 5 #include <libpq-fe.h>
jbe@0 6
jbe@532 7 #define exec_sql_error(message) do { \
jbe@532 8 fprintf(stderr, message ": %s\n%s", command, PQresultErrorMessage(res)); \
jbe@532 9 goto exec_sql_error_clear; \
jbe@532 10 } while (0)
jbe@345 11
jbe@532 12 int exec_sql(PGconn *db, PGresult **resptr, int *errptr, int onerow, char *command) {
jbe@532 13 int count = 0;
jbe@532 14 PGresult *res = PQexec(db, command);
jbe@532 15 if (!res) {
jbe@532 16 fprintf(stderr, "Error in pqlib while sending the following SQL command: %s\n", command);
jbe@532 17 goto exec_sql_error_exit;
jbe@532 18 }
jbe@532 19 if (
jbe@532 20 PQresultStatus(res) != PGRES_COMMAND_OK &&
jbe@532 21 PQresultStatus(res) != PGRES_TUPLES_OK
jbe@532 22 ) exec_sql_error("Error while executing the following SQL command");
jbe@532 23 if (resptr) {
jbe@532 24 if (PQresultStatus(res) != PGRES_TUPLES_OK) exec_sql_error("The following SQL command returned no result");
jbe@532 25 count = PQntuples(res);
jbe@532 26 if (count < 0) exec_sql_error("The following SQL command returned too many rows");
jbe@532 27 if (onerow) {
jbe@532 28 if (count < 1) exec_sql_error("The following SQL command returned less than one row");
jbe@532 29 else if (count > 1) exec_sql_error("The following SQL command returned more than one row");
jbe@532 30 }
jbe@532 31 *resptr = res;
jbe@532 32 } else {
jbe@532 33 PQclear(res);
jbe@532 34 }
jbe@532 35 return count;
jbe@532 36 exec_sql_error_clear:
jbe@532 37 PQclear(res);
jbe@532 38 exec_sql_error_exit:
jbe@532 39 if (resptr) *resptr = NULL;
jbe@532 40 if (errptr) *errptr = 1;
jbe@532 41 return -1;
jbe@345 42 }
jbe@345 43
jbe@0 44 int main(int argc, char **argv) {
jbe@1 45
jbe@1 46 // variable declarations:
jbe@532 47 int err = 0; /* set to 1 if any error occured */
jbe@532 48 int admission_failed = 0; /* set to 1 if error occurred during admission */
jbe@0 49 int i, count;
jbe@0 50 char *conninfo;
jbe@0 51 PGconn *db;
jbe@334 52 PGresult *res;
jbe@1 53
jbe@1 54 // parse command line:
jbe@0 55 if (argc == 0) return 1;
jbe@0 56 if (argc == 1 || !strcmp(argv[1], "-h") || !strcmp(argv[1], "--help")) {
jbe@0 57 FILE *out;
jbe@0 58 out = argc == 1 ? stderr : stdout;
jbe@362 59 fprintf(out, "\n");
jbe@362 60 fprintf(out, "Usage: %s <conninfo>\n", argv[0]);
jbe@362 61 fprintf(out, "\n");
jbe@362 62 fprintf(out, "<conninfo> is specified by PostgreSQL's libpq,\n");
jbe@532 63 fprintf(out, "see http://www.postgresql.org/docs/9.6/static/libpq-connect.html\n");
jbe@362 64 fprintf(out, "\n");
jbe@362 65 fprintf(out, "Example: %s dbname=liquid_feedback\n", argv[0]);
jbe@362 66 fprintf(out, "\n");
jbe@0 67 return argc == 1 ? 1 : 0;
jbe@0 68 }
jbe@0 69 {
jbe@532 70 size_t len = 0, seglen;
jbe@532 71 for (i=1; i<argc; i++) {
jbe@532 72 seglen = strlen(argv[i]) + 1;
jbe@532 73 if (seglen >= SIZE_MAX/2 || len >= SIZE_MAX/2) {
jbe@532 74 fprintf(stderr, "Error: Command line arguments too long\n");
jbe@532 75 return 1;
jbe@532 76 }
jbe@532 77 len += seglen;
jbe@532 78 }
jbe@0 79 conninfo = malloc(len * sizeof(char));
jbe@0 80 if (!conninfo) {
jbe@0 81 fprintf(stderr, "Error: Could not allocate memory for conninfo string\n");
jbe@0 82 return 1;
jbe@0 83 }
jbe@0 84 conninfo[0] = 0;
jbe@0 85 for (i=1; i<argc; i++) {
jbe@0 86 if (i>1) strcat(conninfo, " ");
jbe@0 87 strcat(conninfo, argv[i]);
jbe@0 88 }
jbe@0 89 }
jbe@1 90
jbe@1 91 // connect to database:
jbe@0 92 db = PQconnectdb(conninfo);
jbe@0 93 if (!db) {
jbe@0 94 fprintf(stderr, "Error: Could not create database handle\n");
jbe@0 95 return 1;
jbe@0 96 }
jbe@0 97 if (PQstatus(db) != CONNECTION_OK) {
jbe@0 98 fprintf(stderr, "Could not open connection:\n%s", PQerrorMessage(db));
jbe@0 99 return 1;
jbe@0 100 }
jbe@1 101
jbe@235 102 // delete expired sessions:
jbe@532 103 exec_sql(db, NULL, &err, 0, "DELETE FROM \"expired_session\"");
jbe@532 104
jbe@532 105 // delete expired tokens and authorization codes:
jbe@532 106 exec_sql(db, NULL, &err, 0, "DELETE FROM \"expired_token\"");
jbe@532 107
jbe@532 108 // delete expired snapshots:
jbe@532 109 exec_sql(db, NULL, &err, 0, "DELETE FROM \"expired_snapshot\"");
jbe@235 110
jbe@184 111 // check member activity:
jbe@532 112 exec_sql(db, NULL, &err, 0, "SET TRANSACTION ISOLATION LEVEL READ COMMITTED; SELECT \"check_activity\"()");
jbe@103 113
jbe@4 114 // calculate member counts:
jbe@532 115 exec_sql(db, NULL, &err, 0, "SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; SELECT \"calculate_member_counts\"()");
jbe@532 116
jbe@532 117 // issue admission:
jbe@532 118 count = exec_sql(db, &res, &err, 0, "SET TRANSACTION ISOLATION LEVEL READ COMMITTED; SELECT \"id\" FROM \"area_with_unaccepted_issues\"");
jbe@532 119 if (!res) admission_failed = 1;
jbe@532 120 else {
jbe@532 121 char *area_id, *escaped_area_id, *cmd;
jbe@532 122 PGresult *res2;
jbe@532 123 for (i=0; i<count; i++) {
jbe@532 124 area_id = PQgetvalue(res, i, 0);
jbe@532 125 escaped_area_id = PQescapeLiteral(db, area_id, strlen(area_id));
jbe@532 126 if (!escaped_area_id) {
jbe@532 127 fprintf(stderr, "Could not escape literal in memory.\n");
jbe@532 128 err = admission_failed = 1;
jbe@532 129 continue;
jbe@532 130 }
jbe@532 131 if (asprintf(&cmd, "SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; SELECT \"take_snapshot\"(NULL, %s)", escaped_area_id) < 0) {
jbe@532 132 fprintf(stderr, "Could not prepare query string in memory.\n");
jbe@532 133 err = admission_failed = 1;
jbe@532 134 PQfreemem(escaped_area_id);
jbe@532 135 continue;
jbe@532 136 }
jbe@532 137 exec_sql(db, &res2, &err, 1, cmd);
jbe@532 138 free(cmd);
jbe@532 139 if (!res2) admission_failed = 1;
jbe@532 140 else {
jbe@532 141 char *snapshot_id, *escaped_snapshot_id;
jbe@532 142 int j, count2;
jbe@532 143 snapshot_id = PQgetvalue(res, 0, 0);
jbe@532 144 escaped_snapshot_id = PQescapeLiteral(db, snapshot_id, strlen(snapshot_id));
jbe@532 145 PQclear(res2);
jbe@532 146 if (!escaped_snapshot_id) {
jbe@532 147 fprintf(stderr, "Could not escape literal in memory.\n");
jbe@532 148 err = admission_failed = 1;
jbe@532 149 goto area_admission_cleanup;
jbe@532 150 }
jbe@532 151 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 152 fprintf(stderr, "Could not prepare query string in memory.\n");
jbe@532 153 err = admission_failed = 1;
jbe@532 154 PQfreemem(escaped_snapshot_id);
jbe@532 155 goto area_admission_cleanup;
jbe@532 156 }
jbe@532 157 PQfreemem(escaped_snapshot_id);
jbe@532 158 count2 = exec_sql(db, &res2, &err, 0, cmd);
jbe@532 159 free(cmd);
jbe@532 160 if (!res2) admission_failed = 1;
jbe@532 161 else {
jbe@532 162 char *issue_id, *escaped_issue_id;
jbe@532 163 for (j=0; j<count2; j++) {
jbe@532 164 issue_id = PQgetvalue(res2, j, 0);
jbe@532 165 escaped_issue_id = PQescapeLiteral(db, issue_id, strlen(issue_id));
jbe@532 166 if (!escaped_issue_id) {
jbe@532 167 fprintf(stderr, "Could not escape literal in memory.\n");
jbe@532 168 err = admission_failed = 1;
jbe@532 169 continue;
jbe@532 170 }
jbe@532 171 if (asprintf(&cmd, "SET TRANSACTION ISOLATION LEVEL READ COMMITTED; SELECT \"finish_snapshot\"(%s)", escaped_issue_id) < 0) {
jbe@532 172 fprintf(stderr, "Could not prepare query string in memory.\n");
jbe@532 173 err = admission_failed = 1;
jbe@532 174 PQfreemem(escaped_issue_id);
jbe@532 175 continue;
jbe@532 176 }
jbe@532 177 PQfreemem(escaped_issue_id);
jbe@532 178 if (exec_sql(db, NULL, &err, 0, cmd) < 0) admission_failed = 1;
jbe@532 179 free(cmd);
jbe@532 180 }
jbe@532 181 PQclear(res2);
jbe@532 182 }
jbe@532 183 if (!admission_failed) {
jbe@532 184 if (asprintf(&cmd, "SET TRANSACTION ISOLATION LEVEL READ COMMITTED; SELECT \"issue_admission\"(%s)", escaped_area_id) < 0) {
jbe@532 185 fprintf(stderr, "Could not prepare query string in memory.\n");
jbe@532 186 err = admission_failed = 1;
jbe@532 187 goto area_admission_cleanup;
jbe@532 188 }
jbe@532 189 }
jbe@532 190 while (1) {
jbe@532 191 exec_sql(db, &res2, &err, 1, cmd);
jbe@532 192 if (!res2) {
jbe@532 193 admission_failed = 1;
jbe@532 194 break;
jbe@532 195 }
jbe@532 196 if (PQgetvalue(res2, 0, 0)[0] != 't') {
jbe@532 197 PQclear(res2);
jbe@532 198 break;
jbe@532 199 }
jbe@532 200 PQclear(res2);
jbe@532 201 }
jbe@532 202 }
jbe@532 203 area_admission_cleanup:
jbe@532 204 PQfreemem(escaped_area_id);
jbe@532 205 }
jbe@334 206 PQclear(res);
jbe@4 207 }
jbe@4 208
jbe@1 209 // update open issues:
jbe@532 210 count = exec_sql(
jbe@532 211 db, &res, &err, 0,
jbe@532 212 admission_failed ?
jbe@532 213 "SELECT \"id\" FROM \"open_issue\" WHERE \"state\" != 'admission'::\"issue_state\"" :
jbe@532 214 "SELECT \"id\" FROM \"open_issue\""
jbe@532 215 );
jbe@532 216 for (i=0; i<count; i++) {
jbe@532 217 char *issue_id, *escaped_issue_id;
jbe@532 218 PGresult *res2, *old_res2;
jbe@532 219 int j;
jbe@532 220 issue_id = PQgetvalue(res, i, 0);
jbe@532 221 escaped_issue_id = PQescapeLiteral(db, issue_id, strlen(issue_id));
jbe@532 222 if (!escaped_issue_id) {
jbe@532 223 fprintf(stderr, "Could not escape literal in memory.\n");
jbe@532 224 err = 1;
jbe@532 225 continue;
jbe@532 226 }
jbe@532 227 old_res2 = NULL;
jbe@532 228 for (j=0; ; j++) {
jbe@532 229 if (j >= 20) { // safety to avoid endless loops
jbe@532 230 fprintf(stderr, "Function \"check_issue\"(...) returned non-null value too often.\n");
jbe@357 231 err = 1;
jbe@532 232 if (j > 0) PQclear(old_res2);
jbe@357 233 break;
jbe@357 234 }
jbe@532 235 if (j == 0) {
jbe@532 236 char *cmd;
jbe@532 237 if (asprintf(&cmd, "SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; SELECT \"check_issue\"(%s, NULL)", escaped_issue_id) < 0) {
jbe@532 238 fprintf(stderr, "Could not prepare query string in memory.\n");
jbe@334 239 err = 1;
jbe@334 240 break;
jbe@334 241 }
jbe@532 242 exec_sql(db, &res2, &err, 1, cmd);
jbe@532 243 free(cmd);
jbe@532 244 } else {
jbe@532 245 char *persist, *escaped_persist, *cmd;
jbe@532 246 persist = PQgetvalue(old_res2, 0, 0);
jbe@532 247 escaped_persist = PQescapeLiteral(db, persist, strlen(persist));
jbe@532 248 if (!escaped_persist) {
jbe@532 249 fprintf(stderr, "Could not escape literal in memory.\n");
jbe@532 250 err = 1;
jbe@532 251 PQclear(old_res2);
jbe@532 252 break;
jbe@532 253 }
jbe@532 254 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 255 PQfreemem(escaped_persist);
jbe@532 256 fprintf(stderr, "Could not prepare query string in memory.\n");
jbe@532 257 err = 1;
jbe@334 258 PQclear(old_res2);
jbe@532 259 break;
jbe@334 260 }
jbe@532 261 PQfreemem(escaped_persist);
jbe@532 262 exec_sql(db, &res2, &err, 1, cmd);
jbe@532 263 free(cmd);
jbe@532 264 PQclear(old_res2);
jbe@65 265 }
jbe@532 266 if (!res2) break;
jbe@532 267 if (PQgetisnull(res2, 0, 0)) {
jbe@532 268 PQclear(res2);
jbe@532 269 break;
jbe@532 270 }
jbe@532 271 old_res2 = res2;
jbe@65 272 }
jbe@532 273 PQfreemem(escaped_issue_id);
jbe@0 274 }
jbe@532 275 if (res) PQclear(res);
jbe@1 276
jbe@375 277 // cleanup and exit:
jbe@0 278 PQfinish(db);
jbe@65 279 return err;
jbe@1 280
jbe@0 281 }

Impressum / About Us