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