1.1 --- /dev/null	Thu Jan 01 00:00:00 1970 +0000
     1.2 +++ b/lf_update_suggestion_order.c	Sat Mar 16 17:22:01 2013 +0100
     1.3 @@ -0,0 +1,272 @@
     1.4 +#include <stdlib.h>
     1.5 +#include <stdio.h>
     1.6 +#include <string.h>
     1.7 +#include <libpq-fe.h>
     1.8 +#include <search.h>
     1.9 +
    1.10 +static char *escapeLiteral(PGconn *conn, const char *str, size_t len) {
    1.11 +  // provides compatibility for PostgreSQL versions prior 9.0
    1.12 +  // in future: return PQescapeLiteral(conn, str, len);
    1.13 +  char *res;
    1.14 +  size_t res_len;
    1.15 +  res = malloc(2*len+3);
    1.16 +  res[0] = '\'';
    1.17 +  res_len = PQescapeStringConn(conn, res+1, str, len, NULL);
    1.18 +  res[res_len+1] = '\'';
    1.19 +  res[res_len+2] = 0;
    1.20 +  return res;
    1.21 +}
    1.22 +
    1.23 +static void freemem(void *ptr) {
    1.24 +  // to be used for "escapeLiteral" function
    1.25 +  // provides compatibility for PostgreSQL versions prior 9.0
    1.26 +  // in future: PQfreemem(ptr);
    1.27 +  free(ptr);
    1.28 +}
    1.29 +
    1.30 +#define COL_MEMBER_ID     0
    1.31 +#define COL_WEIGHT        1
    1.32 +#define COL_PREFERENCE    2
    1.33 +#define COL_SUGGESTION_ID 3
    1.34 +
    1.35 +static int candidate_count;
    1.36 +static char **candidates;
    1.37 +
    1.38 +static void register_candidate(char **candidate, VISIT visit, int level) {
    1.39 +  if (visit == postorder || visit == leaf) {
    1.40 +    candidates[candidate_count++] = *candidate;
    1.41 +  }
    1.42 +}
    1.43 +
    1.44 +static int ptrstrcmp(char **s1, char **s2) {
    1.45 +  return strcmp(*s1, *s2);
    1.46 +}
    1.47 +
    1.48 +static int candidate_number(char *candidate) {
    1.49 +  char **addr;
    1.50 +  addr = bsearch(&candidate, candidates, candidate_count, sizeof(char *), (void *)ptrstrcmp);
    1.51 +  if (!addr) {
    1.52 +    fprintf(stderr, "Candidate not found (should not happen)\n");
    1.53 +    abort();
    1.54 +  }
    1.55 +  return addr - candidates;
    1.56 +}
    1.57 +
    1.58 +struct ballot_section {
    1.59 +  int count;
    1.60 +  int *candidates;
    1.61 +};
    1.62 +
    1.63 +struct ballot {
    1.64 +  int weight;
    1.65 +  struct ballot_section sections[4];
    1.66 +};
    1.67 +
    1.68 +static void process_initiative(PGresult *res) {
    1.69 +  void *candidate_tree = NULL;
    1.70 +  int ballot_count = 0;
    1.71 +  int tuple_count, i;
    1.72 +  char *old_member_id = NULL;
    1.73 +  struct ballot *ballots, *ballot;
    1.74 +  int candidates_in_sections[4] = {0, };
    1.75 +  candidate_count = 0;
    1.76 +  tuple_count = PQntuples(res);
    1.77 +  for (i=0; i<=tuple_count; i++) {
    1.78 +    char *member_id, *suggestion_id;
    1.79 +    if (i<tuple_count) {
    1.80 +      member_id = PQgetvalue(res, i, COL_MEMBER_ID);
    1.81 +      suggestion_id = PQgetvalue(res, i, COL_SUGGESTION_ID);
    1.82 +      if (!candidate_tree || !tfind(suggestion_id, &candidate_tree, (void *)strcmp)) {
    1.83 +        candidate_count++;
    1.84 +        if (!tsearch(suggestion_id, &candidate_tree, (void *)strcmp)) {
    1.85 +          fprintf(stderr, "Insufficient memory\n");
    1.86 +          abort();
    1.87 +        }
    1.88 +      }
    1.89 +    }
    1.90 +    if (i==tuple_count || (old_member_id && strcmp(old_member_id, member_id))) {
    1.91 +      ballot_count++;
    1.92 +    }
    1.93 +    old_member_id = member_id;
    1.94 +  }
    1.95 +  printf("Candidate count: %i\n", candidate_count);
    1.96 +  candidates = malloc(candidate_count * sizeof(char *));
    1.97 +  if (!candidates) {
    1.98 +    fprintf(stderr, "Insufficient memory\n");
    1.99 +    abort();
   1.100 +  }
   1.101 +  candidate_count = 0;
   1.102 +  twalk(candidate_tree, (void *)register_candidate);
   1.103 +  while (candidate_tree) tdelete(*(void **)candidate_tree, &candidate_tree, (void *)strcmp);
   1.104 +  printf("Ballot count: %i\n", ballot_count);
   1.105 +  ballots = calloc(ballot_count, sizeof(struct ballot));
   1.106 +  if (!ballots) {
   1.107 +    fprintf(stderr, "Insufficient memory\n");
   1.108 +    abort();
   1.109 +  }
   1.110 +  ballot = ballots;
   1.111 +  for (i=0; i<=tuple_count; i++) {
   1.112 +    char *member_id, *suggestion_id;
   1.113 +    int weight, preference;
   1.114 +    if (i<tuple_count) {
   1.115 +      member_id = PQgetvalue(res, i, COL_MEMBER_ID);
   1.116 +      suggestion_id = PQgetvalue(res, i, COL_SUGGESTION_ID);
   1.117 +      weight = (int)strtol(PQgetvalue(res, i, COL_WEIGHT), (char **)NULL, 10);
   1.118 +      if (weight <= 0) {
   1.119 +        fprintf(stderr, "Unexpected weight value\n");
   1.120 +        abort();
   1.121 +      }
   1.122 +      preference = (int)strtol(PQgetvalue(res, i, COL_PREFERENCE), (char **)NULL, 10);
   1.123 +      if (preference < 1 || preference > 4) {
   1.124 +        fprintf(stderr, "Unexpected preference value\n");
   1.125 +        abort();
   1.126 +      }
   1.127 +      preference--;
   1.128 +      ballot->weight = weight;
   1.129 +      ballot->sections[preference].count++;
   1.130 +    }
   1.131 +    if (i==tuple_count || (old_member_id && strcmp(old_member_id, member_id))) {
   1.132 +      ballot++;
   1.133 +    }
   1.134 +    old_member_id = member_id;
   1.135 +  }
   1.136 +  for (i=0; i<ballot_count; i++) {
   1.137 +    int j;
   1.138 +    for (j=0; j<4; j++) {
   1.139 +      if (ballots[i].sections[j].count) {
   1.140 +        ballots[i].sections[j].candidates = malloc(ballots[i].sections[j].count * sizeof(int));
   1.141 +        if (!ballots[i].sections[j].candidates) {
   1.142 +          fprintf(stderr, "Insufficient memory\n");
   1.143 +          abort();
   1.144 +        }
   1.145 +      }
   1.146 +    }
   1.147 +  }
   1.148 +  ballot = ballots;
   1.149 +  for (i=0; i<=tuple_count; i++) {
   1.150 +    char *member_id, *suggestion_id;
   1.151 +    int preference;
   1.152 +    if (i<tuple_count) {
   1.153 +      member_id = PQgetvalue(res, i, COL_MEMBER_ID);
   1.154 +      suggestion_id = PQgetvalue(res, i, COL_SUGGESTION_ID);
   1.155 +      preference = (int)strtol(PQgetvalue(res, i, COL_PREFERENCE), (char **)NULL, 10);
   1.156 +      if (preference < 1 || preference > 4) {
   1.157 +        fprintf(stderr, "Unexpected preference value\n");
   1.158 +        abort();
   1.159 +      }
   1.160 +      preference--;
   1.161 +      ballot->sections[preference].candidates[candidates_in_sections[preference]++] = candidate_number(suggestion_id);
   1.162 +    }
   1.163 +    if (i==tuple_count || (old_member_id && strcmp(old_member_id, member_id))) {
   1.164 +      ballot++;
   1.165 +      candidates_in_sections[0] = 0;
   1.166 +      candidates_in_sections[1] = 0;
   1.167 +      candidates_in_sections[2] = 0;
   1.168 +      candidates_in_sections[3] = 0;
   1.169 +    }
   1.170 +    old_member_id = member_id;
   1.171 +  }
   1.172 +}
   1.173 +
   1.174 +int main(int argc, char **argv) {
   1.175 +
   1.176 +  // variable declarations:
   1.177 +  int err = 0;
   1.178 +  int i, count;
   1.179 +  char *conninfo;
   1.180 +  PGconn *db;
   1.181 +  PGresult *res;
   1.182 +
   1.183 +  // parse command line:
   1.184 +  if (argc == 0) return 1;
   1.185 +  if (argc == 1 || !strcmp(argv[1], "-h") || !strcmp(argv[1], "--help")) {
   1.186 +    FILE *out;
   1.187 +    out = argc == 1 ? stderr : stdout;
   1.188 +    fprintf(stdout, "\n");
   1.189 +    fprintf(stdout, "Usage: %s <conninfo>\n", argv[0]);
   1.190 +    fprintf(stdout, "\n");
   1.191 +    fprintf(stdout, "<conninfo> is specified by PostgreSQL's libpq,\n");
   1.192 +    fprintf(stdout, "see http://www.postgresql.org/docs/9.1/static/libpq-connect.html\n");
   1.193 +    fprintf(stdout, "\n");
   1.194 +    fprintf(stdout, "Example: %s dbname=liquid_feedback\n", argv[0]);
   1.195 +    fprintf(stdout, "\n");
   1.196 +    return argc == 1 ? 1 : 0;
   1.197 +  }
   1.198 +  {
   1.199 +    size_t len = 0;
   1.200 +    for (i=1; i<argc; i++) len += strlen(argv[i]) + 1;
   1.201 +    conninfo = malloc(len * sizeof(char));
   1.202 +    if (!conninfo) {
   1.203 +      fprintf(stderr, "Error: Could not allocate memory for conninfo string\n");
   1.204 +      return 1;
   1.205 +    }
   1.206 +    conninfo[0] = 0;
   1.207 +    for (i=1; i<argc; i++) {
   1.208 +      if (i>1) strcat(conninfo, " ");
   1.209 +      strcat(conninfo, argv[i]);
   1.210 +    }
   1.211 +  }
   1.212 +
   1.213 +  // connect to database:
   1.214 +  db = PQconnectdb(conninfo);
   1.215 +  if (!db) {
   1.216 +    fprintf(stderr, "Error: Could not create database handle\n");
   1.217 +    return 1;
   1.218 +  }
   1.219 +  if (PQstatus(db) != CONNECTION_OK) {
   1.220 +    fprintf(stderr, "Could not open connection:\n%s", PQerrorMessage(db));
   1.221 +    return 1;
   1.222 +  }
   1.223 +
   1.224 +  // check initiatives:
   1.225 +  res = PQexec(db, "SELECT \"initiative_id\", \"final\" FROM \"initiative_suggestion_order_calculation\"");
   1.226 +  if (!res) {
   1.227 +    fprintf(stderr, "Error in pqlib while sending SQL command selecting open issues\n");
   1.228 +    err = 1;
   1.229 +  } else if (PQresultStatus(res) != PGRES_TUPLES_OK) {
   1.230 +    fprintf(stderr, "Error while executing SQL command selecting open issues:\n%s", PQresultErrorMessage(res));
   1.231 +    err = 1;
   1.232 +    PQclear(res);
   1.233 +  } else {
   1.234 +    count = PQntuples(res);
   1.235 +    printf("Number of initiatives to process: %i\n", count);
   1.236 +    for (i=0; i<count; i++) {
   1.237 +      char *initiative_id, *escaped_initiative_id;
   1.238 +      char *cmd;
   1.239 +      PGresult *res2;
   1.240 +      initiative_id = PQgetvalue(res, i, 0);
   1.241 +      printf("Processing initiative_id: %s\n", initiative_id);
   1.242 +      escaped_initiative_id = escapeLiteral(db, initiative_id, strlen(initiative_id));
   1.243 +      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) {
   1.244 +        fprintf(stderr, "Could not prepare query string in memory.\n");
   1.245 +        err = 1;
   1.246 +        freemem(escaped_initiative_id);
   1.247 +        break;
   1.248 +      }
   1.249 +      res2 = PQexec(db, cmd);
   1.250 +      free(cmd);
   1.251 +      if (!res2) {
   1.252 +        fprintf(stderr, "Error in pqlib while sending SQL command selecting open issues\n");
   1.253 +        err = 1;
   1.254 +      } else if (PQresultStatus(res2) != PGRES_TUPLES_OK) {
   1.255 +        fprintf(stderr, "Error while executing SQL command selecting open issues:\n%s", PQresultErrorMessage(res));
   1.256 +        err = 1;
   1.257 +        PQclear(res2);
   1.258 +      } else {
   1.259 +        if (PQntuples(res2) == 0) {
   1.260 +          printf("Nothing to do.\n");
   1.261 +        } else {
   1.262 +          process_initiative(res2);
   1.263 +        }
   1.264 +        PQclear(res2);
   1.265 +      }
   1.266 +      freemem(escaped_initiative_id);
   1.267 +    }
   1.268 +    PQclear(res);
   1.269 +  }
   1.270 +
   1.271 +  // cleanup and exit
   1.272 +  PQfinish(db);
   1.273 +  return err;
   1.274 +
   1.275 +}