liquid_feedback_core
view lf_update_suggestion_order.c @ 369:cad83f274b79
Bugfix: Numerical ordering of suggestion_id in "lf_update_suggestion_order"
| author | jbe |
|---|---|
| date | Sun Mar 17 16:06:16 2013 +0100 (2013-03-17) |
| parents | 18289b64def1 |
| children | 794974af6d6d |
line source
1 #include <stdlib.h>
2 #include <stdio.h>
3 #include <string.h>
4 #include <libpq-fe.h>
5 #include <search.h>
7 static char *escapeLiteral(PGconn *conn, const char *str, size_t len) {
8 // provides compatibility for PostgreSQL versions prior 9.0
9 // in future: return PQescapeLiteral(conn, str, len);
10 char *res;
11 size_t res_len;
12 res = malloc(2*len+3);
13 if (!res) return NULL;
14 res[0] = '\'';
15 res_len = PQescapeStringConn(conn, res+1, str, len, NULL);
16 res[res_len+1] = '\'';
17 res[res_len+2] = 0;
18 return res;
19 }
21 static void freemem(void *ptr) {
22 // to be used for "escapeLiteral" function
23 // provides compatibility for PostgreSQL versions prior 9.0
24 // in future: PQfreemem(ptr);
25 free(ptr);
26 }
28 #define COL_MEMBER_ID 0
29 #define COL_WEIGHT 1
30 #define COL_PREFERENCE 2
31 #define COL_SUGGESTION_ID 3
33 struct candidate {
34 char *key;
35 double score_per_step;
36 int reaches_score;
37 double score;
38 int seat;
39 };
41 static int compare_id(char *id1, char *id2) {
42 int ldiff;
43 ldiff = strlen(id1) - strlen(id2);
44 if (ldiff) return ldiff;
45 else return strcmp(id1, id2);
46 }
48 static int compare_candidate(struct candidate *c1, struct candidate *c2) {
49 return compare_id(c1->key, c2->key);
50 }
52 static int candidate_count;
53 static struct candidate *candidates;
55 static void register_candidate(char **candidate_key, VISIT visit, int level) {
56 if (visit == postorder || visit == leaf) {
57 struct candidate *candidate;
58 candidate = candidates + (candidate_count++);
59 candidate->key = *candidate_key;
60 candidate->seat = 0;
61 }
62 }
64 static struct candidate *candidate_by_key(char *candidate_key) {
65 struct candidate *candidate;
66 struct candidate compare;
67 compare.key = candidate_key;
68 candidate = bsearch(&compare, candidates, candidate_count, sizeof(struct candidate), (void *)compare_candidate);
69 if (!candidate) {
70 fprintf(stderr, "Candidate not found (should not happen).\n");
71 abort();
72 }
73 return candidate;
74 }
76 struct ballot_section {
77 int count;
78 struct candidate **candidates;
79 };
81 struct ballot {
82 int weight;
83 struct ballot_section sections[4];
84 };
86 static struct candidate *loser(int round_number, struct ballot *ballots, int ballot_count) {
87 int i, j, k;
88 int remaining;
89 for (i=0; i<candidate_count; i++) {
90 candidates[i].score = 0.0;
91 }
92 remaining = candidate_count - round_number;
93 while (1) {
94 double scale;
95 if (remaining <= 1) break;
96 for (i=0; i<candidate_count; i++) {
97 candidates[i].score_per_step = 0.0;
98 candidates[i].reaches_score = 0;
99 }
100 for (i=0; i<ballot_count; i++) {
101 for (j=0; j<4; j++) {
102 int matches = 0;
103 for (k=0; k<ballots[i].sections[j].count; k++) {
104 struct candidate *candidate;
105 candidate = ballots[i].sections[j].candidates[k];
106 if (candidate->score < 1.0 && !candidate->seat) matches++;
107 }
108 if (matches) {
109 double score_inc;
110 score_inc = (double)ballots[i].weight / (double)matches;
111 for (k=0; k<ballots[i].sections[j].count; k++) {
112 struct candidate *candidate;
113 candidate = ballots[i].sections[j].candidates[k];
114 if (candidate->score < 1.0 && !candidate->seat) {
115 candidate->score_per_step += score_inc;
116 }
117 }
118 break;
119 }
120 }
121 }
122 scale = (double)0.0;
123 for (i=0; i<candidate_count; i++) {
124 double max_scale;
125 if (candidates[i].score_per_step > 0.0) {
126 max_scale = (1.0-candidates[i].score) / candidates[i].score_per_step;
127 if (scale == 0.0 || max_scale <= scale) {
128 scale = max_scale;
129 candidates[i].reaches_score = 1;
130 }
131 }
132 }
133 for (i=0; i<candidate_count; i++) {
134 if (candidates[i].score_per_step > 0.0) {
135 if (candidates[i].reaches_score) {
136 candidates[i].score = 1.0;
137 remaining--;
138 } else {
139 candidates[i].score += scale * candidates[i].score_per_step;
140 if (candidates[i].score >= 1.0) remaining--;
141 }
142 if (remaining <= 1) break;
143 }
144 }
145 }
146 for (i=candidate_count-1; i>=0; i--) {
147 if (candidates[i].score < 1.0 && !candidates[i].seat) return candidates+i;
148 }
149 fprintf(stderr, "No remaining candidate (should not happen).");
150 abort();
151 }
153 static int write_ranks(PGconn *db, char *escaped_initiative_id, int final) {
154 PGresult *res;
155 char *cmd;
156 int i;
157 if (final) {
158 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) {
159 fprintf(stderr, "Could not prepare query string in memory.\n");
160 abort();
161 }
162 } else {
163 if (asprintf(&cmd, "BEGIN; UPDATE \"suggestion\" SET \"proportional_order\" = NULL WHERE \"initiative_id\" = %s", escaped_initiative_id) < 0) {
164 fprintf(stderr, "Could not prepare query string in memory.\n");
165 abort();
166 }
167 }
168 res = PQexec(db, cmd);
169 free(cmd);
170 if (!res) {
171 fprintf(stderr, "Error in pqlib while sending SQL command to initiate suggestion update.\n");
172 return 1;
173 } else if (
174 PQresultStatus(res) != PGRES_COMMAND_OK &&
175 PQresultStatus(res) != PGRES_TUPLES_OK
176 ) {
177 fprintf(stderr, "Error while executing SQL command to initiate suggestion update:\n%s", PQresultErrorMessage(res));
178 PQclear(res);
179 return 1;
180 } else {
181 PQclear(res);
182 }
183 for (i=0; i<candidate_count; i++) {
184 char *escaped_suggestion_id;
185 escaped_suggestion_id = escapeLiteral(db, candidates[i].key, strlen(candidates[i].key));
186 if (!escaped_suggestion_id) {
187 fprintf(stderr, "Could not escape literal in memory.\n");
188 abort();
189 }
190 if (asprintf(&cmd, "UPDATE \"suggestion\" SET \"proportional_order\" = %i WHERE \"id\" = %s", candidates[i].seat, escaped_suggestion_id) < 0) {
191 fprintf(stderr, "Could not prepare query string in memory.\n");
192 abort();
193 }
194 freemem(escaped_suggestion_id);
195 res = PQexec(db, cmd);
196 free(cmd);
197 if (!res) {
198 fprintf(stderr, "Error in pqlib while sending SQL command to update suggestion order.\n");
199 } else if (
200 PQresultStatus(res) != PGRES_COMMAND_OK &&
201 PQresultStatus(res) != PGRES_TUPLES_OK
202 ) {
203 fprintf(stderr, "Error while executing SQL command to update suggestion order:\n%s", PQresultErrorMessage(res));
204 PQclear(res);
205 } else {
206 PQclear(res);
207 continue;
208 }
209 res = PQexec(db, "ROLLBACK");
210 if (res) PQclear(res);
211 return 1;
212 }
213 res = PQexec(db, "COMMIT");
214 if (!res) {
215 fprintf(stderr, "Error in pqlib while sending SQL command to commit transaction.\n");
216 return 1;
217 } else if (
218 PQresultStatus(res) != PGRES_COMMAND_OK &&
219 PQresultStatus(res) != PGRES_TUPLES_OK
220 ) {
221 fprintf(stderr, "Error while executing SQL command to commit transaction:\n%s", PQresultErrorMessage(res));
222 PQclear(res);
223 return 1;
224 } else {
225 PQclear(res);
226 return 0;
227 }
228 }
230 static int process_initiative(PGconn *db, PGresult *res, char *escaped_initiative_id, int final) {
231 int err;
232 int ballot_count = 1;
233 struct ballot *ballots;
234 int i;
235 {
236 void *candidate_tree = NULL;
237 int tuple_count;
238 char *old_member_id = NULL;
239 struct ballot *ballot;
240 int candidates_in_sections[4] = {0, };
241 candidate_count = 0;
242 tuple_count = PQntuples(res);
243 if (!tuple_count) {
244 if (final) {
245 printf("No suggestions found, but marking initiative as finally calculated.\n");
246 err = write_ranks(db, escaped_initiative_id, final);
247 printf("Done.\n");
248 return err;
249 } else {
250 printf("Nothing to do.\n");
251 return 0;
252 }
253 }
254 for (i=0; i<tuple_count; i++) {
255 char *member_id, *suggestion_id;
256 member_id = PQgetvalue(res, i, COL_MEMBER_ID);
257 suggestion_id = PQgetvalue(res, i, COL_SUGGESTION_ID);
258 if (!candidate_tree || !tfind(suggestion_id, &candidate_tree, (void *)compare_id)) {
259 candidate_count++;
260 if (!tsearch(suggestion_id, &candidate_tree, (void *)compare_id)) {
261 fprintf(stderr, "Insufficient memory while inserting into candidate tree.\n");
262 abort();
263 }
264 }
265 if (old_member_id && strcmp(old_member_id, member_id)) ballot_count++;
266 old_member_id = member_id;
267 }
268 printf("Candidate count: %i\n", candidate_count);
269 candidates = malloc(candidate_count * sizeof(struct candidate));
270 if (!candidates) {
271 fprintf(stderr, "Insufficient memory while creating candidate list.\n");
272 abort();
273 }
274 candidate_count = 0;
275 twalk(candidate_tree, (void *)register_candidate);
276 while (candidate_tree) tdelete(*(void **)candidate_tree, &candidate_tree, (void *)compare_id);
277 printf("Ballot count: %i\n", ballot_count);
278 ballots = calloc(ballot_count, sizeof(struct ballot));
279 if (!ballots) {
280 fprintf(stderr, "Insufficient memory while creating ballot list.\n");
281 abort();
282 }
283 ballot = ballots;
284 old_member_id = NULL;
285 for (i=0; i<tuple_count; i++) {
286 char *member_id;
287 int weight, preference;
288 member_id = PQgetvalue(res, i, COL_MEMBER_ID);
289 weight = (int)strtol(PQgetvalue(res, i, COL_WEIGHT), (char **)NULL, 10);
290 if (weight <= 0) {
291 fprintf(stderr, "Unexpected weight value.\n");
292 free(ballots);
293 free(candidates);
294 return 1;
295 }
296 preference = (int)strtol(PQgetvalue(res, i, COL_PREFERENCE), (char **)NULL, 10);
297 if (preference < 1 || preference > 4) {
298 fprintf(stderr, "Unexpected preference value.\n");
299 free(ballots);
300 free(candidates);
301 return 1;
302 }
303 preference--;
304 if (old_member_id && strcmp(old_member_id, member_id)) ballot++;
305 ballot->weight = weight;
306 ballot->sections[preference].count++;
307 old_member_id = member_id;
308 }
309 for (i=0; i<ballot_count; i++) {
310 int j;
311 for (j=0; j<4; j++) {
312 if (ballots[i].sections[j].count) {
313 ballots[i].sections[j].candidates = malloc(ballots[i].sections[j].count * sizeof(struct candidate *));
314 if (!ballots[i].sections[j].candidates) {
315 fprintf(stderr, "Insufficient memory while creating ballot section.\n");
316 abort();
317 }
318 }
319 }
320 }
321 old_member_id = NULL;
322 ballot = ballots;
323 for (i=0; i<tuple_count; i++) {
324 char *member_id, *suggestion_id;
325 int preference;
326 member_id = PQgetvalue(res, i, COL_MEMBER_ID);
327 suggestion_id = PQgetvalue(res, i, COL_SUGGESTION_ID);
328 preference = (int)strtol(PQgetvalue(res, i, COL_PREFERENCE), (char **)NULL, 10);
329 preference--;
330 if (old_member_id && strcmp(old_member_id, member_id)) {
331 ballot++;
332 candidates_in_sections[0] = 0;
333 candidates_in_sections[1] = 0;
334 candidates_in_sections[2] = 0;
335 candidates_in_sections[3] = 0;
336 }
337 ballot->sections[preference].candidates[candidates_in_sections[preference]++] = candidate_by_key(suggestion_id);
338 old_member_id = member_id;
339 }
340 }
342 for (i=0; i<candidate_count; i++) {
343 struct candidate *candidate = loser(i, ballots, ballot_count);
344 candidate->seat = candidate_count - i;
345 printf("Assigning rank #%i to suggestion #%s.\n", candidate_count - i, candidate->key);
346 }
348 for (i=0; i<ballot_count; i++) {
349 int j;
350 for (j=0; j<4; j++) {
351 if (ballots[i].sections[j].count) {
352 free(ballots[i].sections[j].candidates);
353 }
354 }
355 }
356 free(ballots);
358 if (final) {
359 printf("Writing final ranks to database.\n");
360 } else {
361 printf("Writing ranks to database.\n");
362 }
363 err = write_ranks(db, escaped_initiative_id, final);
364 printf("Done.\n");
366 free(candidates);
368 return err;
369 }
371 int main(int argc, char **argv) {
373 // variable declarations:
374 int err = 0;
375 int i, count;
376 char *conninfo;
377 PGconn *db;
378 PGresult *res;
380 // parse command line:
381 if (argc == 0) return 1;
382 if (argc == 1 || !strcmp(argv[1], "-h") || !strcmp(argv[1], "--help")) {
383 FILE *out;
384 out = argc == 1 ? stderr : stdout;
385 fprintf(out, "\n");
386 fprintf(out, "Usage: %s <conninfo>\n", argv[0]);
387 fprintf(out, "\n");
388 fprintf(out, "<conninfo> is specified by PostgreSQL's libpq,\n");
389 fprintf(out, "see http://www.postgresql.org/docs/9.1/static/libpq-connect.html\n");
390 fprintf(out, "\n");
391 fprintf(out, "Example: %s dbname=liquid_feedback\n", argv[0]);
392 fprintf(out, "\n");
393 return argc == 1 ? 1 : 0;
394 }
395 {
396 size_t len = 0;
397 for (i=1; i<argc; i++) len += strlen(argv[i]) + 1;
398 conninfo = malloc(len * sizeof(char));
399 if (!conninfo) {
400 fprintf(stderr, "Error: Could not allocate memory for conninfo string.\n");
401 abort();
402 }
403 conninfo[0] = 0;
404 for (i=1; i<argc; i++) {
405 if (i>1) strcat(conninfo, " ");
406 strcat(conninfo, argv[i]);
407 }
408 }
410 // connect to database:
411 db = PQconnectdb(conninfo);
412 if (!db) {
413 fprintf(stderr, "Error: Could not create database handle.\n");
414 return 1;
415 }
416 if (PQstatus(db) != CONNECTION_OK) {
417 fprintf(stderr, "Could not open connection:\n%s", PQerrorMessage(db));
418 return 1;
419 }
421 // check initiatives:
422 res = PQexec(db, "SELECT \"initiative_id\", \"final\" FROM \"initiative_suggestion_order_calculation\"");
423 if (!res) {
424 fprintf(stderr, "Error in pqlib while sending SQL command selecting initiatives to process.\n");
425 err = 1;
426 } else if (PQresultStatus(res) != PGRES_TUPLES_OK) {
427 fprintf(stderr, "Error while executing SQL command selecting initiatives to process:\n%s", PQresultErrorMessage(res));
428 err = 1;
429 PQclear(res);
430 } else if (PQnfields(res) < 2) {
431 fprintf(stderr, "Too few columns returned by SQL command selecting initiatives to process.\n");
432 err = 1;
433 PQclear(res);
434 } else {
435 count = PQntuples(res);
436 printf("Number of initiatives to process: %i\n", count);
437 for (i=0; i<count; i++) {
438 char *initiative_id, *escaped_initiative_id;
439 int final;
440 char *cmd;
441 PGresult *res2;
442 initiative_id = PQgetvalue(res, i, 0);
443 final = (PQgetvalue(res, i, 1)[0] == 't') ? 1 : 0;
444 printf("Processing initiative_id: %s\n", initiative_id);
445 escaped_initiative_id = escapeLiteral(db, initiative_id, strlen(initiative_id));
446 if (!escaped_initiative_id) {
447 fprintf(stderr, "Could not escape literal in memory.\n");
448 abort();
449 }
450 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) {
451 fprintf(stderr, "Could not prepare query string in memory.\n");
452 abort();
453 }
454 res2 = PQexec(db, cmd);
455 free(cmd);
456 if (!res2) {
457 fprintf(stderr, "Error in pqlib while sending SQL command selecting individual suggestion rankings.\n");
458 err = 1;
459 } else if (PQresultStatus(res2) != PGRES_TUPLES_OK) {
460 fprintf(stderr, "Error while executing SQL command selecting individual suggestion rankings:\n%s", PQresultErrorMessage(res));
461 err = 1;
462 PQclear(res2);
463 } else if (PQnfields(res2) < 4) {
464 fprintf(stderr, "Too few columns returned by SQL command selecting individual suggestion rankings.\n");
465 err = 1;
466 PQclear(res2);
467 } else {
468 if (process_initiative(db, res2, escaped_initiative_id, final)) err = 1;
469 PQclear(res2);
470 }
471 freemem(escaped_initiative_id);
472 }
473 PQclear(res);
474 }
476 // cleanup and exit
477 PQfinish(db);
478 if (!err) printf("Successfully terminated.\n");
479 else fprintf(stderr, "Exiting with error code #%i.\n", err);
480 return err;
482 }