liquid_feedback_core

annotate lf_update_suggestion_order.c @ 380:0f5841ef67c6

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

Impressum / About Us