liquid_feedback_core
annotate lf_update_issue_order.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 | d301dc24b25c |
children | 82387194519b |
rev | line source |
---|---|
jbe@394 | 1 #include <stdlib.h> |
jbe@394 | 2 #include <stdio.h> |
jbe@394 | 3 #include <string.h> |
jbe@394 | 4 #include <libpq-fe.h> |
jbe@394 | 5 #include <search.h> |
jbe@394 | 6 |
jbe@394 | 7 static int logging = 0; |
jbe@394 | 8 |
jbe@394 | 9 static char *escapeLiteral(PGconn *conn, const char *str, size_t len) { |
jbe@394 | 10 // provides compatibility for PostgreSQL versions prior 9.0 |
jbe@394 | 11 // in future: return PQescapeLiteral(conn, str, len); |
jbe@394 | 12 char *res; |
jbe@394 | 13 size_t res_len; |
jbe@394 | 14 res = malloc(2*len+3); |
jbe@394 | 15 if (!res) return NULL; |
jbe@394 | 16 res[0] = '\''; |
jbe@394 | 17 res_len = PQescapeStringConn(conn, res+1, str, len, NULL); |
jbe@394 | 18 res[res_len+1] = '\''; |
jbe@394 | 19 res[res_len+2] = 0; |
jbe@394 | 20 return res; |
jbe@394 | 21 } |
jbe@394 | 22 |
jbe@394 | 23 static void freemem(void *ptr) { |
jbe@394 | 24 // to be used for "escapeLiteral" function |
jbe@394 | 25 // provides compatibility for PostgreSQL versions prior 9.0 |
jbe@394 | 26 // in future: PQfreemem(ptr); |
jbe@394 | 27 free(ptr); |
jbe@394 | 28 } |
jbe@394 | 29 |
jbe@394 | 30 // column numbers when querying "issue_supporter_in_admission_state" view in function main(): |
jbe@394 | 31 #define COL_MEMBER_ID 0 |
jbe@394 | 32 #define COL_WEIGHT 1 |
jbe@394 | 33 #define COL_ISSUE_ID 2 |
jbe@394 | 34 |
jbe@407 | 35 // data structure for a candidate (in this case an issue) to the proportional runoff system: |
jbe@394 | 36 struct candidate { |
jbe@407 | 37 char *key; // identifier of the candidate, which is the "issue_id" string |
jbe@394 | 38 double score_per_step; // added score per step |
jbe@394 | 39 double score; // current score of candidate; a score of 1.0 is needed to survive a round |
jbe@394 | 40 int seat; // equals 0 for unseated candidates, or contains rank number |
jbe@394 | 41 }; |
jbe@394 | 42 |
jbe@394 | 43 // compare two integers stored as strings (invocation like strcmp): |
jbe@394 | 44 static int compare_id(char *id1, char *id2) { |
jbe@394 | 45 int ldiff; |
jbe@394 | 46 ldiff = strlen(id1) - strlen(id2); |
jbe@394 | 47 if (ldiff) return ldiff; |
jbe@394 | 48 else return strcmp(id1, id2); |
jbe@394 | 49 } |
jbe@394 | 50 |
jbe@394 | 51 // compare two candidates by their key (invocation like strcmp): |
jbe@394 | 52 static int compare_candidate(struct candidate *c1, struct candidate *c2) { |
jbe@394 | 53 return compare_id(c1->key, c2->key); |
jbe@394 | 54 } |
jbe@394 | 55 |
jbe@394 | 56 // candidates are stored as global variables due to the constrained twalk() interface: |
jbe@394 | 57 static int candidate_count; |
jbe@394 | 58 static struct candidate *candidates; |
jbe@394 | 59 |
jbe@394 | 60 // function to be passed to twalk() to store candidates ordered in candidates[] array: |
jbe@394 | 61 static void register_candidate(char **candidate_key, VISIT visit, int level) { |
jbe@394 | 62 if (visit == postorder || visit == leaf) { |
jbe@394 | 63 struct candidate *candidate; |
jbe@394 | 64 candidate = candidates + (candidate_count++); |
jbe@394 | 65 candidate->key = *candidate_key; |
jbe@394 | 66 candidate->seat = 0; |
jbe@407 | 67 if (logging) printf("Candidate #%i is issue #%s.\n", candidate_count, candidate->key); |
jbe@394 | 68 } |
jbe@394 | 69 } |
jbe@394 | 70 |
jbe@407 | 71 // performs a binary search in candidates[] array to lookup a candidate by its key (which is the issue_id): |
jbe@394 | 72 static struct candidate *candidate_by_key(char *candidate_key) { |
jbe@394 | 73 struct candidate *candidate; |
jbe@394 | 74 struct candidate compare; |
jbe@394 | 75 compare.key = candidate_key; |
jbe@394 | 76 candidate = bsearch(&compare, candidates, candidate_count, sizeof(struct candidate), (void *)compare_candidate); |
jbe@394 | 77 if (!candidate) { |
jbe@394 | 78 fprintf(stderr, "Candidate not found (should not happen).\n"); |
jbe@394 | 79 abort(); |
jbe@394 | 80 } |
jbe@394 | 81 return candidate; |
jbe@394 | 82 } |
jbe@394 | 83 |
jbe@402 | 84 // ballot of the proportional runoff system, containing only one preference section: |
jbe@394 | 85 struct ballot { |
jbe@394 | 86 int weight; // if weight is greater than 1, then the ballot is counted multiple times |
jbe@402 | 87 int count; // number of candidates |
jbe@402 | 88 struct candidate **candidates; // all candidates equally preferred |
jbe@394 | 89 }; |
jbe@394 | 90 |
jbe@394 | 91 // determine candidate, which is assigned the next seat (starting with the worst rank): |
jbe@394 | 92 static struct candidate *loser(int round_number, struct ballot *ballots, int ballot_count) { |
jbe@394 | 93 int i, j; // index variables for loops |
jbe@394 | 94 int remaining; // remaining candidates to be seated |
jbe@394 | 95 // reset scores of all candidates: |
jbe@394 | 96 for (i=0; i<candidate_count; i++) { |
jbe@394 | 97 candidates[i].score = 0.0; |
jbe@394 | 98 } |
jbe@394 | 99 // calculate remaining candidates to be seated: |
jbe@394 | 100 remaining = candidate_count - round_number; |
jbe@394 | 101 // repeat following loop, as long as there is more than one remaining candidate: |
jbe@394 | 102 while (remaining > 1) { |
jbe@394 | 103 if (logging) printf("There are %i remaining candidates.\n", remaining); |
jbe@394 | 104 double scale; // factor to be later multiplied with score_per_step: |
jbe@394 | 105 // reset score_per_step for all candidates: |
jbe@394 | 106 for (i=0; i<candidate_count; i++) { |
jbe@394 | 107 candidates[i].score_per_step = 0.0; |
jbe@394 | 108 } |
jbe@394 | 109 // calculate score_per_step for all candidates: |
jbe@394 | 110 for (i=0; i<ballot_count; i++) { |
jbe@394 | 111 int matches = 0; |
jbe@394 | 112 for (j=0; j<ballots[i].count; j++) { |
jbe@394 | 113 struct candidate *candidate; |
jbe@394 | 114 candidate = ballots[i].candidates[j]; |
jbe@394 | 115 if (candidate->score < 1.0 && !candidate->seat) matches++; |
jbe@394 | 116 } |
jbe@394 | 117 if (matches) { |
jbe@394 | 118 double score_inc; |
jbe@394 | 119 score_inc = (double)ballots[i].weight / (double)matches; |
jbe@394 | 120 for (j=0; j<ballots[i].count; j++) { |
jbe@394 | 121 struct candidate *candidate; |
jbe@394 | 122 candidate = ballots[i].candidates[j]; |
jbe@394 | 123 if (candidate->score < 1.0 && !candidate->seat) { |
jbe@394 | 124 candidate->score_per_step += score_inc; |
jbe@394 | 125 } |
jbe@394 | 126 } |
jbe@394 | 127 } |
jbe@394 | 128 } |
jbe@394 | 129 // calculate scale factor: |
jbe@394 | 130 scale = (double)0.0; // 0.0 is used to indicate that there is no value yet |
jbe@394 | 131 for (i=0; i<candidate_count; i++) { |
jbe@394 | 132 double max_scale; |
jbe@394 | 133 if (candidates[i].score_per_step > 0.0) { |
jbe@394 | 134 max_scale = (1.0-candidates[i].score) / candidates[i].score_per_step; |
jbe@394 | 135 if (scale == 0.0 || max_scale <= scale) { |
jbe@394 | 136 scale = max_scale; |
jbe@394 | 137 } |
jbe@394 | 138 } |
jbe@394 | 139 } |
jbe@394 | 140 // add scale*score_per_step to each candidates score: |
jbe@394 | 141 for (i=0; i<candidate_count; i++) { |
jbe@394 | 142 int log_candidate = 0; |
jbe@394 | 143 if (logging && candidates[i].score < 1.0 && !candidates[i].seat) log_candidate = 1; |
jbe@407 | 144 if (log_candidate) printf("Score for issue #%s = %.4f+%.4f*%.4f", candidates[i].key, candidates[i].score, scale, candidates[i].score_per_step); |
jbe@394 | 145 if (candidates[i].score_per_step > 0.0) { |
jbe@394 | 146 double max_scale; |
jbe@394 | 147 max_scale = (1.0-candidates[i].score) / candidates[i].score_per_step; |
jbe@394 | 148 if (max_scale == scale) { |
jbe@394 | 149 // score of 1.0 should be reached, so we set score directly to avoid floating point errors: |
jbe@394 | 150 candidates[i].score = 1.0; |
jbe@394 | 151 remaining--; |
jbe@394 | 152 } else { |
jbe@394 | 153 candidates[i].score += scale * candidates[i].score_per_step; |
jbe@394 | 154 if (candidates[i].score >= 1.0) remaining--; |
jbe@394 | 155 } |
jbe@394 | 156 } |
jbe@394 | 157 if (log_candidate) { |
jbe@394 | 158 if (candidates[i].score >= 1.0) printf("=1\n"); |
jbe@394 | 159 else printf("=%.4f\n", candidates[i].score); |
jbe@394 | 160 } |
jbe@394 | 161 // when there is only one candidate remaining, then break inner (and thus outer) loop: |
jbe@394 | 162 if (remaining <= 1) { |
jbe@394 | 163 break; |
jbe@394 | 164 } |
jbe@394 | 165 } |
jbe@394 | 166 } |
jbe@394 | 167 // return remaining candidate: |
jbe@394 | 168 for (i=0; i<candidate_count; i++) { |
jbe@394 | 169 if (candidates[i].score < 1.0 && !candidates[i].seat) return candidates+i; |
jbe@394 | 170 } |
jbe@394 | 171 // if there is no remaining candidate, then something went wrong: |
jbe@394 | 172 fprintf(stderr, "No remaining candidate (should not happen)."); |
jbe@394 | 173 abort(); |
jbe@394 | 174 } |
jbe@394 | 175 |
jbe@406 | 176 // write results to database: |
jbe@410 | 177 static int write_ranks(PGconn *db, char *escaped_area_or_unit_id, char *mode) { |
jbe@394 | 178 PGresult *res; |
jbe@394 | 179 char *cmd; |
jbe@394 | 180 int i; |
jbe@410 | 181 res = PQexec(db, "BEGIN"); |
jbe@394 | 182 if (!res) { |
jbe@398 | 183 fprintf(stderr, "Error in pqlib while sending SQL command to initiate issue order update.\n"); |
jbe@394 | 184 return 1; |
jbe@394 | 185 } else if ( |
jbe@394 | 186 PQresultStatus(res) != PGRES_COMMAND_OK && |
jbe@394 | 187 PQresultStatus(res) != PGRES_TUPLES_OK |
jbe@394 | 188 ) { |
jbe@398 | 189 fprintf(stderr, "Error while executing SQL command to initiate issue order update:\n%s", PQresultErrorMessage(res)); |
jbe@394 | 190 PQclear(res); |
jbe@394 | 191 return 1; |
jbe@394 | 192 } else { |
jbe@394 | 193 PQclear(res); |
jbe@394 | 194 } |
jbe@394 | 195 for (i=0; i<candidate_count; i++) { |
jbe@394 | 196 char *escaped_issue_id; |
jbe@394 | 197 escaped_issue_id = escapeLiteral(db, candidates[i].key, strlen(candidates[i].key)); |
jbe@394 | 198 if (!escaped_issue_id) { |
jbe@394 | 199 fprintf(stderr, "Could not escape literal in memory.\n"); |
jbe@394 | 200 abort(); |
jbe@394 | 201 } |
jbe@410 | 202 if (asprintf(&cmd, "UPDATE \"issue_order_in_admission_state\" SET \"order_in_%s\" = %i WHERE \"id\" = %s", mode, candidates[i].seat, escaped_issue_id) < 0) { |
jbe@394 | 203 fprintf(stderr, "Could not prepare query string in memory.\n"); |
jbe@394 | 204 abort(); |
jbe@394 | 205 } |
jbe@394 | 206 freemem(escaped_issue_id); |
jbe@394 | 207 res = PQexec(db, cmd); |
jbe@394 | 208 free(cmd); |
jbe@394 | 209 if (!res) { |
jbe@410 | 210 fprintf(stderr, "Error in pqlib while sending SQL command to update issue order.\n"); |
jbe@394 | 211 } else if ( |
jbe@394 | 212 PQresultStatus(res) != PGRES_COMMAND_OK && |
jbe@394 | 213 PQresultStatus(res) != PGRES_TUPLES_OK |
jbe@394 | 214 ) { |
jbe@410 | 215 fprintf(stderr, "Error while executing SQL command to update issue order:\n%s", PQresultErrorMessage(res)); |
jbe@394 | 216 PQclear(res); |
jbe@394 | 217 } else { |
jbe@394 | 218 PQclear(res); |
jbe@394 | 219 continue; |
jbe@394 | 220 } |
jbe@394 | 221 res = PQexec(db, "ROLLBACK"); |
jbe@394 | 222 if (res) PQclear(res); |
jbe@394 | 223 return 1; |
jbe@394 | 224 } |
jbe@394 | 225 res = PQexec(db, "COMMIT"); |
jbe@394 | 226 if (!res) { |
jbe@394 | 227 fprintf(stderr, "Error in pqlib while sending SQL command to commit transaction.\n"); |
jbe@394 | 228 return 1; |
jbe@394 | 229 } else if ( |
jbe@394 | 230 PQresultStatus(res) != PGRES_COMMAND_OK && |
jbe@394 | 231 PQresultStatus(res) != PGRES_TUPLES_OK |
jbe@394 | 232 ) { |
jbe@394 | 233 fprintf(stderr, "Error while executing SQL command to commit transaction:\n%s", PQresultErrorMessage(res)); |
jbe@394 | 234 PQclear(res); |
jbe@394 | 235 return 1; |
jbe@394 | 236 } else { |
jbe@394 | 237 PQclear(res); |
jbe@394 | 238 } |
jbe@399 | 239 return 0; |
jbe@394 | 240 } |
jbe@394 | 241 |
jbe@394 | 242 // calculate ordering of issues in admission state for an area and call write_ranks() to write it to database: |
jbe@410 | 243 static int process_area_or_unit(PGconn *db, PGresult *res, char *escaped_area_or_unit_id, char *mode) { |
jbe@394 | 244 int err; // variable to store an error condition (0 = success) |
jbe@394 | 245 int ballot_count = 1; // number of ballots, must be initiatized to 1, due to loop below |
jbe@394 | 246 struct ballot *ballots; // data structure containing the ballots |
jbe@394 | 247 int i; // index variable for loops |
jbe@394 | 248 // create candidates[] and ballots[] arrays: |
jbe@394 | 249 { |
jbe@394 | 250 void *candidate_tree = NULL; // temporary structure to create a sorted unique list of all candidate keys |
jbe@394 | 251 int tuple_count; // number of tuples returned from the database |
jbe@394 | 252 char *old_member_id = NULL; // old member_id to be able to detect a new ballot in loops |
jbe@394 | 253 struct ballot *ballot; // pointer to current ballot |
jbe@394 | 254 int candidates_in_ballot = 0; // number of candidates in ballot |
jbe@394 | 255 // reset candidate count: |
jbe@394 | 256 candidate_count = 0; |
jbe@394 | 257 // determine number of tuples: |
jbe@394 | 258 tuple_count = PQntuples(res); |
jbe@394 | 259 // trivial case, when there are no tuples: |
jbe@394 | 260 if (!tuple_count) { |
jbe@395 | 261 // write results to database: |
jbe@402 | 262 if (logging) printf("No supporters for any issue. Writing ranks to database.\n"); |
jbe@410 | 263 err = write_ranks(db, escaped_area_or_unit_id, mode); |
jbe@395 | 264 if (logging) printf("Done.\n"); |
jbe@394 | 265 return 0; |
jbe@394 | 266 } |
jbe@407 | 267 // calculate ballot_count and generate set of candidate keys (issue_id is used as key): |
jbe@394 | 268 for (i=0; i<tuple_count; i++) { |
jbe@394 | 269 char *member_id, *issue_id; |
jbe@394 | 270 member_id = PQgetvalue(res, i, COL_MEMBER_ID); |
jbe@394 | 271 issue_id = PQgetvalue(res, i, COL_ISSUE_ID); |
jbe@394 | 272 if (!candidate_tree || !tfind(issue_id, &candidate_tree, (void *)compare_id)) { |
jbe@394 | 273 candidate_count++; |
jbe@394 | 274 if (!tsearch(issue_id, &candidate_tree, (void *)compare_id)) { |
jbe@394 | 275 fprintf(stderr, "Insufficient memory while inserting into candidate tree.\n"); |
jbe@394 | 276 abort(); |
jbe@394 | 277 } |
jbe@394 | 278 } |
jbe@394 | 279 if (old_member_id && strcmp(old_member_id, member_id)) ballot_count++; |
jbe@394 | 280 old_member_id = member_id; |
jbe@394 | 281 } |
jbe@394 | 282 // allocate memory for candidates[] array: |
jbe@394 | 283 candidates = malloc(candidate_count * sizeof(struct candidate)); |
jbe@394 | 284 if (!candidates) { |
jbe@394 | 285 fprintf(stderr, "Insufficient memory while creating candidate list.\n"); |
jbe@394 | 286 abort(); |
jbe@394 | 287 } |
jbe@394 | 288 // transform tree of candidate keys into sorted array: |
jbe@394 | 289 candidate_count = 0; // needed by register_candidate() |
jbe@394 | 290 twalk(candidate_tree, (void *)register_candidate); |
jbe@394 | 291 // free memory of tree structure (tdestroy() is not available on all platforms): |
jbe@394 | 292 while (candidate_tree) tdelete(*(void **)candidate_tree, &candidate_tree, (void *)compare_id); |
jbe@394 | 293 // allocate memory for ballots[] array: |
jbe@394 | 294 ballots = calloc(ballot_count, sizeof(struct ballot)); |
jbe@394 | 295 if (!ballots) { |
jbe@394 | 296 fprintf(stderr, "Insufficient memory while creating ballot list.\n"); |
jbe@394 | 297 abort(); |
jbe@394 | 298 } |
jbe@394 | 299 // set ballot weights, determine ballot section sizes, and verify preference values: |
jbe@394 | 300 ballot = ballots; |
jbe@394 | 301 old_member_id = NULL; |
jbe@394 | 302 for (i=0; i<tuple_count; i++) { |
jbe@394 | 303 char *member_id; |
jbe@394 | 304 int weight; |
jbe@394 | 305 member_id = PQgetvalue(res, i, COL_MEMBER_ID); |
jbe@394 | 306 weight = (int)strtol(PQgetvalue(res, i, COL_WEIGHT), (char **)NULL, 10); |
jbe@394 | 307 if (weight <= 0) { |
jbe@394 | 308 fprintf(stderr, "Unexpected weight value.\n"); |
jbe@394 | 309 free(ballots); |
jbe@394 | 310 free(candidates); |
jbe@394 | 311 return 1; |
jbe@394 | 312 } |
jbe@394 | 313 if (old_member_id && strcmp(old_member_id, member_id)) ballot++; |
jbe@394 | 314 ballot->weight = weight; |
jbe@394 | 315 ballot->count++; |
jbe@394 | 316 old_member_id = member_id; |
jbe@394 | 317 } |
jbe@394 | 318 // allocate memory for ballot sections: |
jbe@394 | 319 for (i=0; i<ballot_count; i++) { |
jbe@394 | 320 if (ballots[i].count) { |
jbe@394 | 321 ballots[i].candidates = malloc(ballots[i].count * sizeof(struct candidate *)); |
jbe@394 | 322 if (!ballots[i].candidates) { |
jbe@394 | 323 fprintf(stderr, "Insufficient memory while creating ballot section.\n"); |
jbe@394 | 324 abort(); |
jbe@394 | 325 } |
jbe@394 | 326 } |
jbe@394 | 327 } |
jbe@394 | 328 // fill ballot sections with candidate references: |
jbe@394 | 329 old_member_id = NULL; |
jbe@394 | 330 ballot = ballots; |
jbe@394 | 331 for (i=0; i<tuple_count; i++) { |
jbe@394 | 332 char *member_id, *issue_id; |
jbe@394 | 333 member_id = PQgetvalue(res, i, COL_MEMBER_ID); |
jbe@394 | 334 issue_id = PQgetvalue(res, i, COL_ISSUE_ID); |
jbe@394 | 335 if (old_member_id && strcmp(old_member_id, member_id)) { |
jbe@394 | 336 ballot++; |
jbe@394 | 337 candidates_in_ballot = 0; |
jbe@394 | 338 } |
jbe@394 | 339 ballot->candidates[candidates_in_ballot++] = candidate_by_key(issue_id); |
jbe@394 | 340 old_member_id = member_id; |
jbe@394 | 341 } |
jbe@394 | 342 // print ballots, if logging is enabled: |
jbe@394 | 343 if (logging) { |
jbe@394 | 344 for (i=0; i<ballot_count; i++) { |
jbe@394 | 345 int j; |
jbe@394 | 346 printf("Ballot #%i: ", i+1); |
jbe@394 | 347 for (j=0; j<ballots[i].count; j++) { |
jbe@394 | 348 if (!j) printf("issues "); |
jbe@394 | 349 else printf(", "); |
jbe@394 | 350 printf("#%s", ballots[i].candidates[j]->key); |
jbe@394 | 351 } |
jbe@394 | 352 // if (!j) printf("empty"); // should not happen |
jbe@394 | 353 printf(".\n"); |
jbe@394 | 354 } |
jbe@394 | 355 } |
jbe@394 | 356 } |
jbe@394 | 357 |
jbe@394 | 358 // calculate ranks based on constructed data structures: |
jbe@394 | 359 for (i=0; i<candidate_count; i++) { |
jbe@394 | 360 struct candidate *candidate = loser(i, ballots, ballot_count); |
jbe@394 | 361 candidate->seat = candidate_count - i; |
jbe@394 | 362 if (logging) printf("Assigning rank #%i to issue #%s.\n", candidate_count-i, candidate->key); |
jbe@394 | 363 } |
jbe@394 | 364 |
jbe@394 | 365 // free ballots[] array: |
jbe@394 | 366 for (i=0; i<ballot_count; i++) { |
jbe@394 | 367 // if (ballots[i].count) { // count should not be zero |
jbe@394 | 368 free(ballots[i].candidates); |
jbe@394 | 369 // } |
jbe@394 | 370 } |
jbe@394 | 371 free(ballots); |
jbe@394 | 372 |
jbe@394 | 373 // write results to database: |
jbe@394 | 374 if (logging) printf("Writing ranks to database.\n"); |
jbe@410 | 375 err = write_ranks(db, escaped_area_or_unit_id, mode); |
jbe@394 | 376 if (logging) printf("Done.\n"); |
jbe@394 | 377 |
jbe@394 | 378 // free candidates[] array: |
jbe@394 | 379 free(candidates); |
jbe@394 | 380 |
jbe@394 | 381 // return error code of write_ranks() call |
jbe@394 | 382 return err; |
jbe@394 | 383 } |
jbe@394 | 384 |
jbe@394 | 385 int main(int argc, char **argv) { |
jbe@394 | 386 |
jbe@394 | 387 // variable declarations: |
jbe@394 | 388 int err = 0; |
jbe@394 | 389 int i, count; |
jbe@394 | 390 char *conninfo; |
jbe@394 | 391 PGconn *db; |
jbe@394 | 392 PGresult *res; |
jbe@394 | 393 |
jbe@394 | 394 // parse command line: |
jbe@394 | 395 if (argc == 0) return 1; |
jbe@394 | 396 if (argc == 1 || !strcmp(argv[1], "-h") || !strcmp(argv[1], "--help")) { |
jbe@394 | 397 FILE *out; |
jbe@394 | 398 out = argc == 1 ? stderr : stdout; |
jbe@394 | 399 fprintf(out, "\n"); |
jbe@394 | 400 fprintf(out, "Usage: %s [-v|--verbose] <conninfo>\n", argv[0]); |
jbe@394 | 401 fprintf(out, "\n"); |
jbe@394 | 402 fprintf(out, "<conninfo> is specified by PostgreSQL's libpq,\n"); |
jbe@394 | 403 fprintf(out, "see http://www.postgresql.org/docs/9.1/static/libpq-connect.html\n"); |
jbe@394 | 404 fprintf(out, "\n"); |
jbe@394 | 405 fprintf(out, "Example: %s dbname=liquid_feedback\n", argv[0]); |
jbe@394 | 406 fprintf(out, "\n"); |
jbe@394 | 407 return argc == 1 ? 1 : 0; |
jbe@394 | 408 } |
jbe@394 | 409 { |
jbe@394 | 410 size_t len = 0; |
jbe@394 | 411 int argb = 1; |
jbe@394 | 412 if ( |
jbe@394 | 413 argc >= 2 && |
jbe@394 | 414 (!strcmp(argv[1], "-v") || !strcmp(argv[1], "--verbose")) |
jbe@394 | 415 ) { |
jbe@394 | 416 argb = 2; |
jbe@394 | 417 logging = 1; |
jbe@394 | 418 } |
jbe@394 | 419 for (i=argb; i<argc; i++) len += strlen(argv[i]) + 1; |
jbe@394 | 420 conninfo = malloc(len * sizeof(char)); |
jbe@394 | 421 if (!conninfo) { |
jbe@394 | 422 fprintf(stderr, "Error: Could not allocate memory for conninfo string.\n"); |
jbe@394 | 423 abort(); |
jbe@394 | 424 } |
jbe@394 | 425 conninfo[0] = 0; |
jbe@394 | 426 for (i=argb; i<argc; i++) { |
jbe@394 | 427 if (i>argb) strcat(conninfo, " "); |
jbe@394 | 428 strcat(conninfo, argv[i]); |
jbe@394 | 429 } |
jbe@394 | 430 } |
jbe@394 | 431 |
jbe@394 | 432 // connect to database: |
jbe@394 | 433 db = PQconnectdb(conninfo); |
jbe@394 | 434 if (!db) { |
jbe@394 | 435 fprintf(stderr, "Error: Could not create database handle.\n"); |
jbe@394 | 436 return 1; |
jbe@394 | 437 } |
jbe@394 | 438 if (PQstatus(db) != CONNECTION_OK) { |
jbe@394 | 439 fprintf(stderr, "Could not open connection:\n%s", PQerrorMessage(db)); |
jbe@394 | 440 return 1; |
jbe@394 | 441 } |
jbe@394 | 442 |
jbe@410 | 443 // create missing "issue_order_in_admission_state" entries for issues |
jbe@410 | 444 res = PQexec(db, "INSERT INTO \"issue_order_in_admission_state\" (\"id\") SELECT \"issue\".\"id\" FROM \"issue\" NATURAL LEFT JOIN \"issue_order_in_admission_state\" WHERE \"issue\".\"state\" = 'admission'::\"issue_state\" AND \"issue_order_in_admission_state\".\"id\" ISNULL"); |
jbe@410 | 445 if (!res) { |
jbe@410 | 446 fprintf(stderr, "Error in pqlib while sending SQL command creating new issue order entries.\n"); |
jbe@410 | 447 err = 1; |
jbe@410 | 448 } else if ( |
jbe@410 | 449 PQresultStatus(res) != PGRES_COMMAND_OK && |
jbe@410 | 450 PQresultStatus(res) != PGRES_TUPLES_OK |
jbe@410 | 451 ) { |
jbe@410 | 452 fprintf(stderr, "Error while executing SQL command creating new issue order entries:\n%s", PQresultErrorMessage(res)); |
jbe@410 | 453 err = 1; |
jbe@410 | 454 PQclear(res); |
jbe@410 | 455 } else { |
jbe@410 | 456 if (logging) printf("Created %s new issue order entries.\n", PQcmdTuples(res)); |
jbe@410 | 457 PQclear(res); |
jbe@410 | 458 } |
jbe@410 | 459 |
jbe@394 | 460 // go through areas: |
jbe@394 | 461 res = PQexec(db, "SELECT \"id\" FROM \"area\""); |
jbe@394 | 462 if (!res) { |
jbe@394 | 463 fprintf(stderr, "Error in pqlib while sending SQL command selecting areas to process.\n"); |
jbe@394 | 464 err = 1; |
jbe@394 | 465 } else if (PQresultStatus(res) != PGRES_TUPLES_OK) { |
jbe@394 | 466 fprintf(stderr, "Error while executing SQL command selecting areas to process:\n%s", PQresultErrorMessage(res)); |
jbe@394 | 467 err = 1; |
jbe@394 | 468 PQclear(res); |
jbe@394 | 469 } else if (PQnfields(res) < 1) { |
jbe@394 | 470 fprintf(stderr, "Too few columns returned by SQL command selecting areas to process.\n"); |
jbe@394 | 471 err = 1; |
jbe@394 | 472 PQclear(res); |
jbe@394 | 473 } else { |
jbe@394 | 474 count = PQntuples(res); |
jbe@394 | 475 if (logging) printf("Number of areas to process: %i\n", count); |
jbe@394 | 476 for (i=0; i<count; i++) { |
jbe@394 | 477 char *area_id, *escaped_area_id; |
jbe@394 | 478 char *cmd; |
jbe@394 | 479 PGresult *res2; |
jbe@394 | 480 area_id = PQgetvalue(res, i, 0); |
jbe@394 | 481 if (logging) printf("Processing area #%s:\n", area_id); |
jbe@394 | 482 escaped_area_id = escapeLiteral(db, area_id, strlen(area_id)); |
jbe@394 | 483 if (!escaped_area_id) { |
jbe@394 | 484 fprintf(stderr, "Could not escape literal in memory.\n"); |
jbe@394 | 485 abort(); |
jbe@394 | 486 } |
jbe@394 | 487 if (asprintf(&cmd, "SELECT \"member_id\", \"weight\", \"issue_id\" FROM \"issue_supporter_in_admission_state\" WHERE \"area_id\" = %s ORDER BY \"member_id\"", escaped_area_id) < 0) { |
jbe@394 | 488 fprintf(stderr, "Could not prepare query string in memory.\n"); |
jbe@394 | 489 abort(); |
jbe@394 | 490 } |
jbe@394 | 491 res2 = PQexec(db, cmd); |
jbe@394 | 492 free(cmd); |
jbe@394 | 493 if (!res2) { |
jbe@394 | 494 fprintf(stderr, "Error in pqlib while sending SQL command selecting issue supporter in admission state.\n"); |
jbe@394 | 495 err = 1; |
jbe@394 | 496 } else if (PQresultStatus(res2) != PGRES_TUPLES_OK) { |
jbe@394 | 497 fprintf(stderr, "Error while executing SQL command selecting issue supporter in admission state:\n%s", PQresultErrorMessage(res)); |
jbe@394 | 498 err = 1; |
jbe@394 | 499 PQclear(res2); |
jbe@394 | 500 } else if (PQnfields(res2) < 3) { |
jbe@394 | 501 fprintf(stderr, "Too few columns returned by SQL command selecting issue supporter in admission state.\n"); |
jbe@394 | 502 err = 1; |
jbe@394 | 503 PQclear(res2); |
jbe@394 | 504 } else { |
jbe@410 | 505 if (process_area_or_unit(db, res2, escaped_area_id, "area")) err = 1; |
jbe@394 | 506 PQclear(res2); |
jbe@394 | 507 } |
jbe@394 | 508 freemem(escaped_area_id); |
jbe@394 | 509 } |
jbe@394 | 510 PQclear(res); |
jbe@394 | 511 } |
jbe@394 | 512 |
jbe@410 | 513 // go through units: |
jbe@410 | 514 res = PQexec(db, "SELECT \"id\" FROM \"unit\""); |
jbe@410 | 515 if (!res) { |
jbe@410 | 516 fprintf(stderr, "Error in pqlib while sending SQL command selecting units to process.\n"); |
jbe@410 | 517 err = 1; |
jbe@410 | 518 } else if (PQresultStatus(res) != PGRES_TUPLES_OK) { |
jbe@410 | 519 fprintf(stderr, "Error while executing SQL command selecting units to process:\n%s", PQresultErrorMessage(res)); |
jbe@410 | 520 err = 1; |
jbe@410 | 521 PQclear(res); |
jbe@410 | 522 } else if (PQnfields(res) < 1) { |
jbe@410 | 523 fprintf(stderr, "Too few columns returned by SQL command selecting units to process.\n"); |
jbe@410 | 524 err = 1; |
jbe@410 | 525 PQclear(res); |
jbe@410 | 526 } else { |
jbe@410 | 527 count = PQntuples(res); |
jbe@410 | 528 if (logging) printf("Number of units to process: %i\n", count); |
jbe@410 | 529 for (i=0; i<count; i++) { |
jbe@410 | 530 char *unit_id, *escaped_unit_id; |
jbe@410 | 531 char *cmd; |
jbe@410 | 532 PGresult *res2; |
jbe@410 | 533 unit_id = PQgetvalue(res, i, 0); |
jbe@410 | 534 if (logging) printf("Processing unit #%s:\n", unit_id); |
jbe@410 | 535 escaped_unit_id = escapeLiteral(db, unit_id, strlen(unit_id)); |
jbe@410 | 536 if (!escaped_unit_id) { |
jbe@410 | 537 fprintf(stderr, "Could not escape literal in memory.\n"); |
jbe@410 | 538 abort(); |
jbe@410 | 539 } |
jbe@410 | 540 if (asprintf(&cmd, "SELECT \"member_id\", \"weight\", \"issue_id\" FROM \"issue_supporter_in_admission_state\" WHERE \"unit_id\" = %s ORDER BY \"member_id\"", escaped_unit_id) < 0) { |
jbe@410 | 541 fprintf(stderr, "Could not prepare query string in memory.\n"); |
jbe@410 | 542 abort(); |
jbe@410 | 543 } |
jbe@410 | 544 res2 = PQexec(db, cmd); |
jbe@410 | 545 free(cmd); |
jbe@410 | 546 if (!res2) { |
jbe@410 | 547 fprintf(stderr, "Error in pqlib while sending SQL command selecting issue supporter in admission state.\n"); |
jbe@410 | 548 err = 1; |
jbe@410 | 549 } else if (PQresultStatus(res2) != PGRES_TUPLES_OK) { |
jbe@410 | 550 fprintf(stderr, "Error while executing SQL command selecting issue supporter in admission state:\n%s", PQresultErrorMessage(res)); |
jbe@410 | 551 err = 1; |
jbe@410 | 552 PQclear(res2); |
jbe@410 | 553 } else if (PQnfields(res2) < 3) { |
jbe@410 | 554 fprintf(stderr, "Too few columns returned by SQL command selecting issue supporter in admission state.\n"); |
jbe@410 | 555 err = 1; |
jbe@410 | 556 PQclear(res2); |
jbe@410 | 557 } else { |
jbe@410 | 558 if (process_area_or_unit(db, res2, escaped_unit_id, "unit")) err = 1; |
jbe@410 | 559 PQclear(res2); |
jbe@410 | 560 } |
jbe@410 | 561 freemem(escaped_unit_id); |
jbe@410 | 562 } |
jbe@410 | 563 PQclear(res); |
jbe@410 | 564 } |
jbe@410 | 565 |
jbe@401 | 566 // clean-up entries of deleted issues |
jbe@410 | 567 res = PQexec(db, "DELETE FROM \"issue_order_in_admission_state\" USING \"issue_order_in_admission_state\" AS \"self\" NATURAL LEFT JOIN \"issue\" WHERE \"issue_order_in_admission_state\".\"id\" = \"self\".\"id\" AND (\"issue\".\"id\" ISNULL OR \"issue\".\"state\" != 'admission'::\"issue_state\")"); |
jbe@401 | 568 if (!res) { |
jbe@401 | 569 fprintf(stderr, "Error in pqlib while sending SQL command deleting ordering data of deleted issues.\n"); |
jbe@401 | 570 err = 1; |
jbe@401 | 571 } else if ( |
jbe@401 | 572 PQresultStatus(res) != PGRES_COMMAND_OK && |
jbe@401 | 573 PQresultStatus(res) != PGRES_TUPLES_OK |
jbe@401 | 574 ) { |
jbe@401 | 575 fprintf(stderr, "Error while executing SQL command deleting ordering data of deleted issues:\n%s", PQresultErrorMessage(res)); |
jbe@401 | 576 err = 1; |
jbe@401 | 577 PQclear(res); |
jbe@401 | 578 } else { |
jbe@401 | 579 if (logging) printf("Cleaned up ordering data of %s deleted issues.\n", PQcmdTuples(res)); |
jbe@401 | 580 PQclear(res); |
jbe@401 | 581 } |
jbe@401 | 582 |
jbe@394 | 583 // cleanup and exit: |
jbe@394 | 584 PQfinish(db); |
jbe@394 | 585 if (!err) { |
jbe@394 | 586 if (logging) printf("Successfully terminated.\n"); |
jbe@394 | 587 } else { |
jbe@394 | 588 fprintf(stderr, "Exiting with error code %i.\n", err); |
jbe@394 | 589 } |
jbe@394 | 590 return err; |
jbe@394 | 591 |
jbe@394 | 592 } |