liquid_feedback_core
diff lf_update_suggestion_order.c @ 352:98c14d8d07f1
Support for proportional ordering of suggestions in core.sql; Begin of work on "lf_update_suggestion_order" (a second background job for sorting suggestions based on a proportional preferential voting system)
author | jbe |
---|---|
date | Sat Mar 16 17:22:01 2013 +0100 (2013-03-16) |
parents | |
children | 31ce1877320b |
line diff
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 +}