jbe@394: #include <stdlib.h>
jbe@394: #include <stdio.h>
jbe@394: #include <string.h>
jbe@394: #include <libpq-fe.h>
jbe@394: #include <search.h>
jbe@394: 
jbe@394: static int logging = 0;
jbe@394: 
jbe@394: static char *escapeLiteral(PGconn *conn, const char *str, size_t len) {
jbe@394:   // provides compatibility for PostgreSQL versions prior 9.0
jbe@394:   // in future: return PQescapeLiteral(conn, str, len);
jbe@394:   char *res;
jbe@394:   size_t res_len;
jbe@394:   res = malloc(2*len+3);
jbe@394:   if (!res) return NULL;
jbe@394:   res[0] = '\'';
jbe@394:   res_len = PQescapeStringConn(conn, res+1, str, len, NULL);
jbe@394:   res[res_len+1] = '\'';
jbe@394:   res[res_len+2] = 0;
jbe@394:   return res;
jbe@394: }
jbe@394: 
jbe@394: static void freemem(void *ptr) {
jbe@394:   // to be used for "escapeLiteral" function
jbe@394:   // provides compatibility for PostgreSQL versions prior 9.0
jbe@394:   // in future: PQfreemem(ptr);
jbe@394:   free(ptr);
jbe@394: }
jbe@394: 
jbe@394: // column numbers when querying "issue_supporter_in_admission_state" view in function main():
jbe@394: #define COL_MEMBER_ID 0
jbe@394: #define COL_WEIGHT    1
jbe@394: #define COL_ISSUE_ID  2
jbe@394: 
jbe@407: // data structure for a candidate (in this case an issue) to the proportional runoff system:
jbe@394: struct candidate {
jbe@407:   char *key;              // identifier of the candidate, which is the "issue_id" string
jbe@394:   double score_per_step;  // added score per step
jbe@394:   double score;           // current score of candidate; a score of 1.0 is needed to survive a round
jbe@394:   int seat;               // equals 0 for unseated candidates, or contains rank number
jbe@394: };
jbe@394: 
jbe@394: // compare two integers stored as strings (invocation like strcmp):
jbe@394: static int compare_id(char *id1, char *id2) {
jbe@394:   int ldiff;
jbe@394:   ldiff = strlen(id1) - strlen(id2);
jbe@394:   if (ldiff) return ldiff;
jbe@394:   else return strcmp(id1, id2);
jbe@394: }
jbe@394: 
jbe@394: // compare two candidates by their key (invocation like strcmp):
jbe@394: static int compare_candidate(struct candidate *c1, struct candidate *c2) {
jbe@394:   return compare_id(c1->key, c2->key);
jbe@394: }
jbe@394: 
jbe@394: // candidates are stored as global variables due to the constrained twalk() interface:
jbe@394: static int candidate_count;
jbe@394: static struct candidate *candidates;
jbe@394: 
jbe@394: // function to be passed to twalk() to store candidates ordered in candidates[] array:
jbe@394: static void register_candidate(char **candidate_key, VISIT visit, int level) {
jbe@394:   if (visit == postorder || visit == leaf) {
jbe@394:     struct candidate *candidate;
jbe@394:     candidate = candidates + (candidate_count++);
jbe@394:     candidate->key  = *candidate_key;
jbe@394:     candidate->seat = 0;
jbe@407:     if (logging) printf("Candidate #%i is issue #%s.\n", candidate_count, candidate->key);
jbe@394:   }
jbe@394: }
jbe@394: 
jbe@407: // performs a binary search in candidates[] array to lookup a candidate by its key (which is the issue_id):
jbe@394: static struct candidate *candidate_by_key(char *candidate_key) {
jbe@394:   struct candidate *candidate;
jbe@394:   struct candidate compare;
jbe@394:   compare.key = candidate_key;
jbe@394:   candidate = bsearch(&compare, candidates, candidate_count, sizeof(struct candidate), (void *)compare_candidate);
jbe@394:   if (!candidate) {
jbe@394:     fprintf(stderr, "Candidate not found (should not happen).\n");
jbe@394:     abort();
jbe@394:   }
jbe@394:   return candidate;
jbe@394: }
jbe@394: 
jbe@402: // ballot of the proportional runoff system, containing only one preference section:
jbe@394: struct ballot {
jbe@394:   int weight;  // if weight is greater than 1, then the ballot is counted multiple times
jbe@402:   int count;   // number of candidates
jbe@402:   struct candidate **candidates;  // all candidates equally preferred
jbe@394: };
jbe@394: 
jbe@394: // determine candidate, which is assigned the next seat (starting with the worst rank):
jbe@394: static struct candidate *loser(int round_number, struct ballot *ballots, int ballot_count) {
jbe@394:   int i, j;       // index variables for loops
jbe@394:   int remaining;  // remaining candidates to be seated
jbe@394:   // reset scores of all candidates:
jbe@394:   for (i=0; i<candidate_count; i++) {
jbe@394:     candidates[i].score = 0.0;
jbe@394:   }
jbe@394:   // calculate remaining candidates to be seated:
jbe@394:   remaining = candidate_count - round_number;
jbe@394:   // repeat following loop, as long as there is more than one remaining candidate:
jbe@394:   while (remaining > 1) {
jbe@394:     if (logging) printf("There are %i remaining candidates.\n", remaining);
jbe@394:     double scale;  // factor to be later multiplied with score_per_step:
jbe@394:     // reset score_per_step for all candidates:
jbe@394:     for (i=0; i<candidate_count; i++) {
jbe@394:       candidates[i].score_per_step = 0.0;
jbe@394:     }
jbe@394:     // calculate score_per_step for all candidates:
jbe@394:     for (i=0; i<ballot_count; i++) {
jbe@394:       int matches = 0;
jbe@394:       for (j=0; j<ballots[i].count; j++) {
jbe@394:         struct candidate *candidate;
jbe@394:         candidate = ballots[i].candidates[j];
jbe@394:         if (candidate->score < 1.0 && !candidate->seat) matches++;
jbe@394:       }
jbe@394:       if (matches) {
jbe@394:         double score_inc;
jbe@394:         score_inc = (double)ballots[i].weight / (double)matches;
jbe@394:         for (j=0; j<ballots[i].count; j++) {
jbe@394:           struct candidate *candidate;
jbe@394:           candidate = ballots[i].candidates[j];
jbe@394:           if (candidate->score < 1.0 && !candidate->seat) {
jbe@394:             candidate->score_per_step += score_inc;
jbe@394:           }
jbe@394:         }
jbe@394:       }
jbe@394:     }
jbe@394:     // calculate scale factor:
jbe@394:     scale = (double)0.0;  // 0.0 is used to indicate that there is no value yet
jbe@394:     for (i=0; i<candidate_count; i++) {
jbe@394:       double max_scale;
jbe@394:       if (candidates[i].score_per_step > 0.0) {
jbe@394:         max_scale = (1.0-candidates[i].score) / candidates[i].score_per_step;
jbe@394:         if (scale == 0.0 || max_scale <= scale) {
jbe@394:           scale = max_scale;
jbe@394:         }
jbe@394:       }
jbe@394:     }
jbe@394:     // add scale*score_per_step to each candidates score:
jbe@394:     for (i=0; i<candidate_count; i++) {
jbe@394:       int log_candidate = 0;
jbe@394:       if (logging && candidates[i].score < 1.0 && !candidates[i].seat) log_candidate = 1;
jbe@407:       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:       if (candidates[i].score_per_step > 0.0) {
jbe@394:         double max_scale;
jbe@394:         max_scale = (1.0-candidates[i].score) / candidates[i].score_per_step;
jbe@394:         if (max_scale == scale) {
jbe@394:           // score of 1.0 should be reached, so we set score directly to avoid floating point errors:
jbe@394:           candidates[i].score = 1.0;
jbe@394:           remaining--;
jbe@394:         } else {
jbe@394:           candidates[i].score += scale * candidates[i].score_per_step;
jbe@394:           if (candidates[i].score >= 1.0) remaining--;
jbe@394:         }
jbe@394:       }
jbe@394:       if (log_candidate) {
jbe@394:         if (candidates[i].score >= 1.0) printf("=1\n");
jbe@394:         else printf("=%.4f\n", candidates[i].score);
jbe@394:       }
jbe@394:       // when there is only one candidate remaining, then break inner (and thus outer) loop:
jbe@394:       if (remaining <= 1) {
jbe@394:         break;
jbe@394:       }
jbe@394:     }
jbe@394:   }
jbe@394:   // return remaining candidate:
jbe@394:   for (i=0; i<candidate_count; i++) {
jbe@394:     if (candidates[i].score < 1.0 && !candidates[i].seat) return candidates+i;
jbe@394:   }
jbe@394:   // if there is no remaining candidate, then something went wrong:
jbe@394:   fprintf(stderr, "No remaining candidate (should not happen).");
jbe@394:   abort();
jbe@394: }
jbe@394: 
jbe@406: // write results to database:
jbe@410: static int write_ranks(PGconn *db, char *escaped_area_or_unit_id, char *mode) {
jbe@394:   PGresult *res;
jbe@394:   char *cmd;
jbe@394:   int i;
jbe@410:   res = PQexec(db, "BEGIN");
jbe@394:   if (!res) {
jbe@398:     fprintf(stderr, "Error in pqlib while sending SQL command to initiate issue order update.\n");
jbe@394:     return 1;
jbe@394:   } else if (
jbe@394:     PQresultStatus(res) != PGRES_COMMAND_OK &&
jbe@394:     PQresultStatus(res) != PGRES_TUPLES_OK
jbe@394:   ) {
jbe@398:     fprintf(stderr, "Error while executing SQL command to initiate issue order update:\n%s", PQresultErrorMessage(res));
jbe@394:     PQclear(res);
jbe@394:     return 1;
jbe@394:   } else {
jbe@394:     PQclear(res);
jbe@394:   }
jbe@394:   for (i=0; i<candidate_count; i++) {
jbe@394:     char *escaped_issue_id;
jbe@394:     escaped_issue_id = escapeLiteral(db, candidates[i].key, strlen(candidates[i].key));
jbe@394:     if (!escaped_issue_id) {
jbe@394:       fprintf(stderr, "Could not escape literal in memory.\n");
jbe@394:       abort();
jbe@394:     }
jbe@410:     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:       fprintf(stderr, "Could not prepare query string in memory.\n");
jbe@394:       abort();
jbe@394:     }
jbe@394:     freemem(escaped_issue_id);
jbe@394:     res = PQexec(db, cmd);
jbe@394:     free(cmd);
jbe@394:     if (!res) {
jbe@410:       fprintf(stderr, "Error in pqlib while sending SQL command to update issue order.\n");
jbe@394:     } else if (
jbe@394:       PQresultStatus(res) != PGRES_COMMAND_OK &&
jbe@394:       PQresultStatus(res) != PGRES_TUPLES_OK
jbe@394:     ) {
jbe@410:       fprintf(stderr, "Error while executing SQL command to update issue order:\n%s", PQresultErrorMessage(res));
jbe@394:       PQclear(res);
jbe@394:     } else {
jbe@394:       PQclear(res);
jbe@394:       continue;
jbe@394:     }
jbe@394:     res = PQexec(db, "ROLLBACK");
jbe@394:     if (res) PQclear(res);
jbe@394:     return 1;
jbe@394:   }
jbe@394:   res = PQexec(db, "COMMIT");
jbe@394:   if (!res) {
jbe@394:     fprintf(stderr, "Error in pqlib while sending SQL command to commit transaction.\n");
jbe@394:     return 1;
jbe@394:   } else if (
jbe@394:     PQresultStatus(res) != PGRES_COMMAND_OK &&
jbe@394:     PQresultStatus(res) != PGRES_TUPLES_OK
jbe@394:   ) {
jbe@394:     fprintf(stderr, "Error while executing SQL command to commit transaction:\n%s", PQresultErrorMessage(res));
jbe@394:     PQclear(res);
jbe@394:     return 1;
jbe@394:   } else {
jbe@394:     PQclear(res);
jbe@394:   }
jbe@399:   return 0;
jbe@394: }
jbe@394: 
jbe@394: // calculate ordering of issues in admission state for an area and call write_ranks() to write it to database:
jbe@410: static int process_area_or_unit(PGconn *db, PGresult *res, char *escaped_area_or_unit_id, char *mode) {
jbe@394:   int err;                 // variable to store an error condition (0 = success)
jbe@394:   int ballot_count = 1;    // number of ballots, must be initiatized to 1, due to loop below
jbe@394:   struct ballot *ballots;  // data structure containing the ballots
jbe@394:   int i;                   // index variable for loops
jbe@394:   // create candidates[] and ballots[] arrays:
jbe@394:   {
jbe@394:     void *candidate_tree = NULL;  // temporary structure to create a sorted unique list of all candidate keys
jbe@394:     int tuple_count;              // number of tuples returned from the database
jbe@394:     char *old_member_id = NULL;   // old member_id to be able to detect a new ballot in loops
jbe@394:     struct ballot *ballot;        // pointer to current ballot
jbe@394:     int candidates_in_ballot = 0; // number of candidates in ballot
jbe@394:     // reset candidate count:
jbe@394:     candidate_count = 0;
jbe@394:     // determine number of tuples:
jbe@394:     tuple_count = PQntuples(res);
jbe@394:     // trivial case, when there are no tuples:
jbe@394:     if (!tuple_count) {
jbe@395:       // write results to database:
jbe@402:       if (logging) printf("No supporters for any issue. Writing ranks to database.\n");
jbe@410:       err = write_ranks(db, escaped_area_or_unit_id, mode);
jbe@395:       if (logging) printf("Done.\n");
jbe@394:       return 0;
jbe@394:     }
jbe@407:     // calculate ballot_count and generate set of candidate keys (issue_id is used as key):
jbe@394:     for (i=0; i<tuple_count; i++) {
jbe@394:       char *member_id, *issue_id;
jbe@394:       member_id = PQgetvalue(res, i, COL_MEMBER_ID);
jbe@394:       issue_id = PQgetvalue(res, i, COL_ISSUE_ID);
jbe@394:       if (!candidate_tree || !tfind(issue_id, &candidate_tree, (void *)compare_id)) {
jbe@394:         candidate_count++;
jbe@394:         if (!tsearch(issue_id, &candidate_tree, (void *)compare_id)) {
jbe@394:           fprintf(stderr, "Insufficient memory while inserting into candidate tree.\n");
jbe@394:           abort();
jbe@394:         }
jbe@394:       }
jbe@394:       if (old_member_id && strcmp(old_member_id, member_id)) ballot_count++;
jbe@394:       old_member_id = member_id;
jbe@394:     }
jbe@394:     // allocate memory for candidates[] array:
jbe@394:     candidates = malloc(candidate_count * sizeof(struct candidate));
jbe@394:     if (!candidates) {
jbe@394:       fprintf(stderr, "Insufficient memory while creating candidate list.\n");
jbe@394:       abort();
jbe@394:     }
jbe@394:     // transform tree of candidate keys into sorted array:
jbe@394:     candidate_count = 0;  // needed by register_candidate()
jbe@394:     twalk(candidate_tree, (void *)register_candidate);
jbe@394:     // free memory of tree structure (tdestroy() is not available on all platforms):
jbe@394:     while (candidate_tree) tdelete(*(void **)candidate_tree, &candidate_tree, (void *)compare_id);
jbe@394:     // allocate memory for ballots[] array:
jbe@394:     ballots = calloc(ballot_count, sizeof(struct ballot));
jbe@394:     if (!ballots) {
jbe@394:       fprintf(stderr, "Insufficient memory while creating ballot list.\n");
jbe@394:       abort();
jbe@394:     }
jbe@394:     // set ballot weights, determine ballot section sizes, and verify preference values:
jbe@394:     ballot = ballots;
jbe@394:     old_member_id = NULL;
jbe@394:     for (i=0; i<tuple_count; i++) {
jbe@394:       char *member_id;
jbe@394:       int weight;
jbe@394:       member_id = PQgetvalue(res, i, COL_MEMBER_ID);
jbe@394:       weight = (int)strtol(PQgetvalue(res, i, COL_WEIGHT), (char **)NULL, 10);
jbe@394:       if (weight <= 0) {
jbe@394:         fprintf(stderr, "Unexpected weight value.\n");
jbe@394:         free(ballots);
jbe@394:         free(candidates);
jbe@394:         return 1;
jbe@394:       }
jbe@394:       if (old_member_id && strcmp(old_member_id, member_id)) ballot++;
jbe@394:       ballot->weight = weight;
jbe@394:       ballot->count++;
jbe@394:       old_member_id = member_id;
jbe@394:     }
jbe@394:     // allocate memory for ballot sections:
jbe@394:     for (i=0; i<ballot_count; i++) {
jbe@394:       if (ballots[i].count) {
jbe@394:         ballots[i].candidates = malloc(ballots[i].count * sizeof(struct candidate *));
jbe@394:         if (!ballots[i].candidates) {
jbe@394:           fprintf(stderr, "Insufficient memory while creating ballot section.\n");
jbe@394:           abort();
jbe@394:         }
jbe@394:       }
jbe@394:     }
jbe@394:     // fill ballot sections with candidate references:
jbe@394:     old_member_id = NULL;
jbe@394:     ballot = ballots;
jbe@394:     for (i=0; i<tuple_count; i++) {
jbe@394:       char *member_id, *issue_id;
jbe@394:       member_id = PQgetvalue(res, i, COL_MEMBER_ID);
jbe@394:       issue_id = PQgetvalue(res, i, COL_ISSUE_ID);
jbe@394:       if (old_member_id && strcmp(old_member_id, member_id)) {
jbe@394:         ballot++;
jbe@394:         candidates_in_ballot = 0;
jbe@394:       }
jbe@394:       ballot->candidates[candidates_in_ballot++] = candidate_by_key(issue_id);
jbe@394:       old_member_id = member_id;
jbe@394:     }
jbe@394:     // print ballots, if logging is enabled:
jbe@394:     if (logging) {
jbe@394:       for (i=0; i<ballot_count; i++) {
jbe@394:         int j;
jbe@394:         printf("Ballot #%i: ", i+1);
jbe@394:         for (j=0; j<ballots[i].count; j++) {
jbe@394:           if (!j) printf("issues ");
jbe@394:           else printf(", ");
jbe@394:           printf("#%s", ballots[i].candidates[j]->key);
jbe@394:         }
jbe@394:         // if (!j) printf("empty");  // should not happen
jbe@394:         printf(".\n");
jbe@394:       }
jbe@394:     }
jbe@394:   }
jbe@394: 
jbe@394:   // calculate ranks based on constructed data structures:
jbe@394:   for (i=0; i<candidate_count; i++) {
jbe@394:     struct candidate *candidate = loser(i, ballots, ballot_count);
jbe@394:     candidate->seat = candidate_count - i;
jbe@394:     if (logging) printf("Assigning rank #%i to issue #%s.\n", candidate_count-i, candidate->key);
jbe@394:   }
jbe@394: 
jbe@394:   // free ballots[] array:
jbe@394:   for (i=0; i<ballot_count; i++) {
jbe@394:     // if (ballots[i].count) {  // count should not be zero
jbe@394:       free(ballots[i].candidates);
jbe@394:     // }
jbe@394:   }
jbe@394:   free(ballots);
jbe@394: 
jbe@394:   // write results to database:
jbe@394:   if (logging) printf("Writing ranks to database.\n");
jbe@410:   err = write_ranks(db, escaped_area_or_unit_id, mode);
jbe@394:   if (logging) printf("Done.\n");
jbe@394: 
jbe@394:   // free candidates[] array:
jbe@394:   free(candidates);
jbe@394: 
jbe@394:   // return error code of write_ranks() call
jbe@394:   return err;
jbe@394: }
jbe@394: 
jbe@394: int main(int argc, char **argv) {
jbe@394: 
jbe@394:   // variable declarations:
jbe@394:   int err = 0;
jbe@394:   int i, count;
jbe@394:   char *conninfo;
jbe@394:   PGconn *db;
jbe@394:   PGresult *res;
jbe@394: 
jbe@394:   // parse command line:
jbe@394:   if (argc == 0) return 1;
jbe@394:   if (argc == 1 || !strcmp(argv[1], "-h") || !strcmp(argv[1], "--help")) {
jbe@394:     FILE *out;
jbe@394:     out = argc == 1 ? stderr : stdout;
jbe@394:     fprintf(out, "\n");
jbe@394:     fprintf(out, "Usage: %s [-v|--verbose] <conninfo>\n", argv[0]);
jbe@394:     fprintf(out, "\n");
jbe@394:     fprintf(out, "<conninfo> is specified by PostgreSQL's libpq,\n");
jbe@394:     fprintf(out, "see http://www.postgresql.org/docs/9.1/static/libpq-connect.html\n");
jbe@394:     fprintf(out, "\n");
jbe@394:     fprintf(out, "Example: %s dbname=liquid_feedback\n", argv[0]);
jbe@394:     fprintf(out, "\n");
jbe@394:     return argc == 1 ? 1 : 0;
jbe@394:   }
jbe@394:   {
jbe@394:     size_t len = 0;
jbe@394:     int argb = 1;
jbe@394:     if (
jbe@394:       argc >= 2 &&
jbe@394:       (!strcmp(argv[1], "-v") || !strcmp(argv[1], "--verbose"))
jbe@394:     ) {
jbe@394:       argb = 2;
jbe@394:       logging = 1;
jbe@394:     }
jbe@394:     for (i=argb; i<argc; i++) len += strlen(argv[i]) + 1;
jbe@394:     conninfo = malloc(len * sizeof(char));
jbe@394:     if (!conninfo) {
jbe@394:       fprintf(stderr, "Error: Could not allocate memory for conninfo string.\n");
jbe@394:       abort();
jbe@394:     }
jbe@394:     conninfo[0] = 0;
jbe@394:     for (i=argb; i<argc; i++) {
jbe@394:       if (i>argb) strcat(conninfo, " ");
jbe@394:       strcat(conninfo, argv[i]);
jbe@394:     }
jbe@394:   }
jbe@394: 
jbe@394:   // connect to database:
jbe@394:   db = PQconnectdb(conninfo);
jbe@394:   if (!db) {
jbe@394:     fprintf(stderr, "Error: Could not create database handle.\n");
jbe@394:     return 1;
jbe@394:   }
jbe@394:   if (PQstatus(db) != CONNECTION_OK) {
jbe@394:     fprintf(stderr, "Could not open connection:\n%s", PQerrorMessage(db));
jbe@394:     return 1;
jbe@394:   }
jbe@394: 
jbe@410:   // create missing "issue_order_in_admission_state" entries for issues
jbe@410:   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:   if (!res) {
jbe@410:     fprintf(stderr, "Error in pqlib while sending SQL command creating new issue order entries.\n");
jbe@410:     err = 1;
jbe@410:   } else if (
jbe@410:     PQresultStatus(res) != PGRES_COMMAND_OK &&
jbe@410:     PQresultStatus(res) != PGRES_TUPLES_OK
jbe@410:   ) {
jbe@410:     fprintf(stderr, "Error while executing SQL command creating new issue order entries:\n%s", PQresultErrorMessage(res));
jbe@410:     err = 1;
jbe@410:     PQclear(res);
jbe@410:   } else {
jbe@410:     if (logging) printf("Created %s new issue order entries.\n", PQcmdTuples(res));
jbe@410:     PQclear(res);
jbe@410:   }
jbe@410: 
jbe@394:   // go through areas:
jbe@394:   res = PQexec(db, "SELECT \"id\" FROM \"area\"");
jbe@394:   if (!res) {
jbe@394:     fprintf(stderr, "Error in pqlib while sending SQL command selecting areas to process.\n");
jbe@394:     err = 1;
jbe@394:   } else if (PQresultStatus(res) != PGRES_TUPLES_OK) {
jbe@394:     fprintf(stderr, "Error while executing SQL command selecting areas to process:\n%s", PQresultErrorMessage(res));
jbe@394:     err = 1;
jbe@394:     PQclear(res);
jbe@394:   } else if (PQnfields(res) < 1) {
jbe@394:     fprintf(stderr, "Too few columns returned by SQL command selecting areas to process.\n");
jbe@394:     err = 1;
jbe@394:     PQclear(res);
jbe@394:   } else {
jbe@394:     count = PQntuples(res);
jbe@394:     if (logging) printf("Number of areas to process: %i\n", count);
jbe@394:     for (i=0; i<count; i++) {
jbe@394:       char *area_id, *escaped_area_id;
jbe@394:       char *cmd;
jbe@394:       PGresult *res2;
jbe@394:       area_id = PQgetvalue(res, i, 0);
jbe@394:       if (logging) printf("Processing area #%s:\n", area_id);
jbe@394:       escaped_area_id = escapeLiteral(db, area_id, strlen(area_id));
jbe@394:       if (!escaped_area_id) {
jbe@394:         fprintf(stderr, "Could not escape literal in memory.\n");
jbe@394:         abort();
jbe@394:       }
jbe@394:       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:         fprintf(stderr, "Could not prepare query string in memory.\n");
jbe@394:         abort();
jbe@394:       }
jbe@394:       res2 = PQexec(db, cmd);
jbe@394:       free(cmd);
jbe@394:       if (!res2) {
jbe@394:         fprintf(stderr, "Error in pqlib while sending SQL command selecting issue supporter in admission state.\n");
jbe@394:         err = 1;
jbe@394:       } else if (PQresultStatus(res2) != PGRES_TUPLES_OK) {
jbe@394:         fprintf(stderr, "Error while executing SQL command selecting issue supporter in admission state:\n%s", PQresultErrorMessage(res));
jbe@394:         err = 1;
jbe@394:         PQclear(res2);
jbe@394:       } else if (PQnfields(res2) < 3) {
jbe@394:         fprintf(stderr, "Too few columns returned by SQL command selecting issue supporter in admission state.\n");
jbe@394:         err = 1;
jbe@394:         PQclear(res2);
jbe@394:       } else {
jbe@410:         if (process_area_or_unit(db, res2, escaped_area_id, "area")) err = 1;
jbe@394:         PQclear(res2);
jbe@394:       }
jbe@394:       freemem(escaped_area_id);
jbe@394:     }
jbe@394:     PQclear(res);
jbe@394:   }
jbe@394: 
jbe@410:   // go through units:
jbe@410:   res = PQexec(db, "SELECT \"id\" FROM \"unit\"");
jbe@410:   if (!res) {
jbe@410:     fprintf(stderr, "Error in pqlib while sending SQL command selecting units to process.\n");
jbe@410:     err = 1;
jbe@410:   } else if (PQresultStatus(res) != PGRES_TUPLES_OK) {
jbe@410:     fprintf(stderr, "Error while executing SQL command selecting units to process:\n%s", PQresultErrorMessage(res));
jbe@410:     err = 1;
jbe@410:     PQclear(res);
jbe@410:   } else if (PQnfields(res) < 1) {
jbe@410:     fprintf(stderr, "Too few columns returned by SQL command selecting units to process.\n");
jbe@410:     err = 1;
jbe@410:     PQclear(res);
jbe@410:   } else {
jbe@410:     count = PQntuples(res);
jbe@410:     if (logging) printf("Number of units to process: %i\n", count);
jbe@410:     for (i=0; i<count; i++) {
jbe@410:       char *unit_id, *escaped_unit_id;
jbe@410:       char *cmd;
jbe@410:       PGresult *res2;
jbe@410:       unit_id = PQgetvalue(res, i, 0);
jbe@410:       if (logging) printf("Processing unit #%s:\n", unit_id);
jbe@410:       escaped_unit_id = escapeLiteral(db, unit_id, strlen(unit_id));
jbe@410:       if (!escaped_unit_id) {
jbe@410:         fprintf(stderr, "Could not escape literal in memory.\n");
jbe@410:         abort();
jbe@410:       }
jbe@410:       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:         fprintf(stderr, "Could not prepare query string in memory.\n");
jbe@410:         abort();
jbe@410:       }
jbe@410:       res2 = PQexec(db, cmd);
jbe@410:       free(cmd);
jbe@410:       if (!res2) {
jbe@410:         fprintf(stderr, "Error in pqlib while sending SQL command selecting issue supporter in admission state.\n");
jbe@410:         err = 1;
jbe@410:       } else if (PQresultStatus(res2) != PGRES_TUPLES_OK) {
jbe@410:         fprintf(stderr, "Error while executing SQL command selecting issue supporter in admission state:\n%s", PQresultErrorMessage(res));
jbe@410:         err = 1;
jbe@410:         PQclear(res2);
jbe@410:       } else if (PQnfields(res2) < 3) {
jbe@410:         fprintf(stderr, "Too few columns returned by SQL command selecting issue supporter in admission state.\n");
jbe@410:         err = 1;
jbe@410:         PQclear(res2);
jbe@410:       } else {
jbe@410:         if (process_area_or_unit(db, res2, escaped_unit_id, "unit")) err = 1;
jbe@410:         PQclear(res2);
jbe@410:       }
jbe@410:       freemem(escaped_unit_id);
jbe@410:     }
jbe@410:     PQclear(res);
jbe@410:   }
jbe@410: 
jbe@401:   // clean-up entries of deleted issues
jbe@410:   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:   if (!res) {
jbe@401:     fprintf(stderr, "Error in pqlib while sending SQL command deleting ordering data of deleted issues.\n");
jbe@401:     err = 1;
jbe@401:   } else if (
jbe@401:     PQresultStatus(res) != PGRES_COMMAND_OK &&
jbe@401:     PQresultStatus(res) != PGRES_TUPLES_OK
jbe@401:   ) {
jbe@401:     fprintf(stderr, "Error while executing SQL command deleting ordering data of deleted issues:\n%s", PQresultErrorMessage(res));
jbe@401:     err = 1;
jbe@401:     PQclear(res);
jbe@401:   } else {
jbe@401:     if (logging) printf("Cleaned up ordering data of %s deleted issues.\n", PQcmdTuples(res));
jbe@401:     PQclear(res);
jbe@401:   }
jbe@401: 
jbe@394:   // cleanup and exit:
jbe@394:   PQfinish(db);
jbe@394:   if (!err) {
jbe@394:     if (logging) printf("Successfully terminated.\n");
jbe@394:   } else {
jbe@394:     fprintf(stderr, "Exiting with error code %i.\n", err);
jbe@394:   }
jbe@394:   return err;
jbe@394: 
jbe@394: }