liquid_feedback_core

annotate lf_update_suggestion_order.c @ 373:88322e31107b

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

Impressum / About Us