liquid_feedback_core
annotate lf_update.c @ 532:5855ff9e5c8f
Several changes/additions for upcoming major release
- OAuth 2.0 support
- storing profiles as JSON document
- removed subject area membership
- revised snapshot system
- additional issue limiter (dynamic quorum in subject area)
- extended event logging in "event" table
- OAuth 2.0 support
- storing profiles as JSON document
- removed subject area membership
- revised snapshot system
- additional issue limiter (dynamic quorum in subject area)
- extended event logging in "event" table
author | jbe |
---|---|
date | Thu Mar 30 19:42:38 2017 +0200 (2017-03-30) |
parents | 3f7a89ad996d |
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 } |