liquid_feedback_core
view 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 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 res[0] = '\'';
14 res_len = PQescapeStringConn(conn, res+1, str, len, NULL);
15 res[res_len+1] = '\'';
16 res[res_len+2] = 0;
17 return res;
18 }
20 static void freemem(void *ptr) {
21 // to be used for "escapeLiteral" function
22 // provides compatibility for PostgreSQL versions prior 9.0
23 // in future: PQfreemem(ptr);
24 free(ptr);
25 }
27 #define COL_MEMBER_ID 0
28 #define COL_WEIGHT 1
29 #define COL_PREFERENCE 2
30 #define COL_SUGGESTION_ID 3
32 static int candidate_count;
33 static char **candidates;
35 static void register_candidate(char **candidate, VISIT visit, int level) {
36 if (visit == postorder || visit == leaf) {
37 candidates[candidate_count++] = *candidate;
38 }
39 }
41 static int ptrstrcmp(char **s1, char **s2) {
42 return strcmp(*s1, *s2);
43 }
45 static int candidate_number(char *candidate) {
46 char **addr;
47 addr = bsearch(&candidate, candidates, candidate_count, sizeof(char *), (void *)ptrstrcmp);
48 if (!addr) {
49 fprintf(stderr, "Candidate not found (should not happen)\n");
50 abort();
51 }
52 return addr - candidates;
53 }
55 struct ballot_section {
56 int count;
57 int *candidates;
58 };
60 struct ballot {
61 int weight;
62 struct ballot_section sections[4];
63 };
65 static void process_initiative(PGresult *res) {
66 void *candidate_tree = NULL;
67 int ballot_count = 0;
68 int tuple_count, i;
69 char *old_member_id = NULL;
70 struct ballot *ballots, *ballot;
71 int candidates_in_sections[4] = {0, };
72 candidate_count = 0;
73 tuple_count = PQntuples(res);
74 for (i=0; i<=tuple_count; i++) {
75 char *member_id, *suggestion_id;
76 if (i<tuple_count) {
77 member_id = PQgetvalue(res, i, COL_MEMBER_ID);
78 suggestion_id = PQgetvalue(res, i, COL_SUGGESTION_ID);
79 if (!candidate_tree || !tfind(suggestion_id, &candidate_tree, (void *)strcmp)) {
80 candidate_count++;
81 if (!tsearch(suggestion_id, &candidate_tree, (void *)strcmp)) {
82 fprintf(stderr, "Insufficient memory\n");
83 abort();
84 }
85 }
86 }
87 if (i==tuple_count || (old_member_id && strcmp(old_member_id, member_id))) {
88 ballot_count++;
89 }
90 old_member_id = member_id;
91 }
92 printf("Candidate count: %i\n", candidate_count);
93 candidates = malloc(candidate_count * sizeof(char *));
94 if (!candidates) {
95 fprintf(stderr, "Insufficient memory\n");
96 abort();
97 }
98 candidate_count = 0;
99 twalk(candidate_tree, (void *)register_candidate);
100 while (candidate_tree) tdelete(*(void **)candidate_tree, &candidate_tree, (void *)strcmp);
101 printf("Ballot count: %i\n", ballot_count);
102 ballots = calloc(ballot_count, sizeof(struct ballot));
103 if (!ballots) {
104 fprintf(stderr, "Insufficient memory\n");
105 abort();
106 }
107 ballot = ballots;
108 for (i=0; i<=tuple_count; i++) {
109 char *member_id, *suggestion_id;
110 int weight, preference;
111 if (i<tuple_count) {
112 member_id = PQgetvalue(res, i, COL_MEMBER_ID);
113 suggestion_id = PQgetvalue(res, i, COL_SUGGESTION_ID);
114 weight = (int)strtol(PQgetvalue(res, i, COL_WEIGHT), (char **)NULL, 10);
115 if (weight <= 0) {
116 fprintf(stderr, "Unexpected weight value\n");
117 abort();
118 }
119 preference = (int)strtol(PQgetvalue(res, i, COL_PREFERENCE), (char **)NULL, 10);
120 if (preference < 1 || preference > 4) {
121 fprintf(stderr, "Unexpected preference value\n");
122 abort();
123 }
124 preference--;
125 ballot->weight = weight;
126 ballot->sections[preference].count++;
127 }
128 if (i==tuple_count || (old_member_id && strcmp(old_member_id, member_id))) {
129 ballot++;
130 }
131 old_member_id = member_id;
132 }
133 for (i=0; i<ballot_count; i++) {
134 int j;
135 for (j=0; j<4; j++) {
136 if (ballots[i].sections[j].count) {
137 ballots[i].sections[j].candidates = malloc(ballots[i].sections[j].count * sizeof(int));
138 if (!ballots[i].sections[j].candidates) {
139 fprintf(stderr, "Insufficient memory\n");
140 abort();
141 }
142 }
143 }
144 }
145 ballot = ballots;
146 for (i=0; i<=tuple_count; i++) {
147 char *member_id, *suggestion_id;
148 int preference;
149 if (i<tuple_count) {
150 member_id = PQgetvalue(res, i, COL_MEMBER_ID);
151 suggestion_id = PQgetvalue(res, i, COL_SUGGESTION_ID);
152 preference = (int)strtol(PQgetvalue(res, i, COL_PREFERENCE), (char **)NULL, 10);
153 if (preference < 1 || preference > 4) {
154 fprintf(stderr, "Unexpected preference value\n");
155 abort();
156 }
157 preference--;
158 ballot->sections[preference].candidates[candidates_in_sections[preference]++] = candidate_number(suggestion_id);
159 }
160 if (i==tuple_count || (old_member_id && strcmp(old_member_id, member_id))) {
161 ballot++;
162 candidates_in_sections[0] = 0;
163 candidates_in_sections[1] = 0;
164 candidates_in_sections[2] = 0;
165 candidates_in_sections[3] = 0;
166 }
167 old_member_id = member_id;
168 }
169 }
171 int main(int argc, char **argv) {
173 // variable declarations:
174 int err = 0;
175 int i, count;
176 char *conninfo;
177 PGconn *db;
178 PGresult *res;
180 // parse command line:
181 if (argc == 0) return 1;
182 if (argc == 1 || !strcmp(argv[1], "-h") || !strcmp(argv[1], "--help")) {
183 FILE *out;
184 out = argc == 1 ? stderr : stdout;
185 fprintf(stdout, "\n");
186 fprintf(stdout, "Usage: %s <conninfo>\n", argv[0]);
187 fprintf(stdout, "\n");
188 fprintf(stdout, "<conninfo> is specified by PostgreSQL's libpq,\n");
189 fprintf(stdout, "see http://www.postgresql.org/docs/9.1/static/libpq-connect.html\n");
190 fprintf(stdout, "\n");
191 fprintf(stdout, "Example: %s dbname=liquid_feedback\n", argv[0]);
192 fprintf(stdout, "\n");
193 return argc == 1 ? 1 : 0;
194 }
195 {
196 size_t len = 0;
197 for (i=1; i<argc; i++) len += strlen(argv[i]) + 1;
198 conninfo = malloc(len * sizeof(char));
199 if (!conninfo) {
200 fprintf(stderr, "Error: Could not allocate memory for conninfo string\n");
201 return 1;
202 }
203 conninfo[0] = 0;
204 for (i=1; i<argc; i++) {
205 if (i>1) strcat(conninfo, " ");
206 strcat(conninfo, argv[i]);
207 }
208 }
210 // connect to database:
211 db = PQconnectdb(conninfo);
212 if (!db) {
213 fprintf(stderr, "Error: Could not create database handle\n");
214 return 1;
215 }
216 if (PQstatus(db) != CONNECTION_OK) {
217 fprintf(stderr, "Could not open connection:\n%s", PQerrorMessage(db));
218 return 1;
219 }
221 // check initiatives:
222 res = PQexec(db, "SELECT \"initiative_id\", \"final\" FROM \"initiative_suggestion_order_calculation\"");
223 if (!res) {
224 fprintf(stderr, "Error in pqlib while sending SQL command selecting open issues\n");
225 err = 1;
226 } else if (PQresultStatus(res) != PGRES_TUPLES_OK) {
227 fprintf(stderr, "Error while executing SQL command selecting open issues:\n%s", PQresultErrorMessage(res));
228 err = 1;
229 PQclear(res);
230 } else {
231 count = PQntuples(res);
232 printf("Number of initiatives to process: %i\n", count);
233 for (i=0; i<count; i++) {
234 char *initiative_id, *escaped_initiative_id;
235 char *cmd;
236 PGresult *res2;
237 initiative_id = PQgetvalue(res, i, 0);
238 printf("Processing initiative_id: %s\n", initiative_id);
239 escaped_initiative_id = escapeLiteral(db, initiative_id, strlen(initiative_id));
240 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) {
241 fprintf(stderr, "Could not prepare query string in memory.\n");
242 err = 1;
243 freemem(escaped_initiative_id);
244 break;
245 }
246 res2 = PQexec(db, cmd);
247 free(cmd);
248 if (!res2) {
249 fprintf(stderr, "Error in pqlib while sending SQL command selecting open issues\n");
250 err = 1;
251 } else if (PQresultStatus(res2) != PGRES_TUPLES_OK) {
252 fprintf(stderr, "Error while executing SQL command selecting open issues:\n%s", PQresultErrorMessage(res));
253 err = 1;
254 PQclear(res2);
255 } else {
256 if (PQntuples(res2) == 0) {
257 printf("Nothing to do.\n");
258 } else {
259 process_initiative(res2);
260 }
261 PQclear(res2);
262 }
263 freemem(escaped_initiative_id);
264 }
265 PQclear(res);
266 }
268 // cleanup and exit
269 PQfinish(db);
270 return err;
272 }