liquid_feedback_core
view lf_update.c @ 378:e88d0606891f
Bugfix regarding "proportional_order" of suggestions:
Use NULL values explicitly to be sorted last
(includes new suggestions as well as suggestions without any individual rankings)
Use NULL values explicitly to be sorted last
(includes new suggestions as well as suggestions without any individual rankings)
author | jbe |
---|---|
date | Mon Mar 18 09:36:21 2013 +0100 (2013-03-18) |
parents | 3f7a89ad996d |
children | 5855ff9e5c8f |
line source
1 #include <stdlib.h>
2 #include <stdio.h>
3 #include <string.h>
4 #include <libpq-fe.h>
6 static char *escapeLiteral(PGconn *conn, const char *str, size_t len) {
7 // provides compatibility for PostgreSQL versions prior 9.0
8 // in future: return PQescapeLiteral(conn, str, len);
9 char *res;
10 size_t res_len;
11 res = malloc(2*len+3);
12 if (!res) return NULL;
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 int main(int argc, char **argv) {
29 // variable declarations:
30 int err = 0;
31 int i, count;
32 char *conninfo;
33 PGconn *db;
34 PGresult *res;
36 // parse command line:
37 if (argc == 0) return 1;
38 if (argc == 1 || !strcmp(argv[1], "-h") || !strcmp(argv[1], "--help")) {
39 FILE *out;
40 out = argc == 1 ? stderr : stdout;
41 fprintf(out, "\n");
42 fprintf(out, "Usage: %s <conninfo>\n", argv[0]);
43 fprintf(out, "\n");
44 fprintf(out, "<conninfo> is specified by PostgreSQL's libpq,\n");
45 fprintf(out, "see http://www.postgresql.org/docs/9.1/static/libpq-connect.html\n");
46 fprintf(out, "\n");
47 fprintf(out, "Example: %s dbname=liquid_feedback\n", argv[0]);
48 fprintf(out, "\n");
49 return argc == 1 ? 1 : 0;
50 }
51 {
52 size_t len = 0;
53 for (i=1; i<argc; i++) len += strlen(argv[i]) + 1;
54 conninfo = malloc(len * sizeof(char));
55 if (!conninfo) {
56 fprintf(stderr, "Error: Could not allocate memory for conninfo string\n");
57 return 1;
58 }
59 conninfo[0] = 0;
60 for (i=1; i<argc; i++) {
61 if (i>1) strcat(conninfo, " ");
62 strcat(conninfo, argv[i]);
63 }
64 }
66 // connect to database:
67 db = PQconnectdb(conninfo);
68 if (!db) {
69 fprintf(stderr, "Error: Could not create database handle\n");
70 return 1;
71 }
72 if (PQstatus(db) != CONNECTION_OK) {
73 fprintf(stderr, "Could not open connection:\n%s", PQerrorMessage(db));
74 return 1;
75 }
77 // delete expired sessions:
78 res = PQexec(db, "DELETE FROM \"expired_session\"");
79 if (!res) {
80 fprintf(stderr, "Error in pqlib while sending SQL command deleting expired sessions\n");
81 err = 1;
82 } else if (
83 PQresultStatus(res) != PGRES_COMMAND_OK &&
84 PQresultStatus(res) != PGRES_TUPLES_OK
85 ) {
86 fprintf(stderr, "Error while executing SQL command deleting expired sessions:\n%s", PQresultErrorMessage(res));
87 err = 1;
88 PQclear(res);
89 } else {
90 PQclear(res);
91 }
93 // check member activity:
94 res = PQexec(db, "SET TRANSACTION ISOLATION LEVEL READ COMMITTED; SELECT \"check_activity\"()");
95 if (!res) {
96 fprintf(stderr, "Error in pqlib while sending SQL command checking member activity\n");
97 err = 1;
98 } else if (
99 PQresultStatus(res) != PGRES_COMMAND_OK &&
100 PQresultStatus(res) != PGRES_TUPLES_OK
101 ) {
102 fprintf(stderr, "Error while executing SQL command checking member activity:\n%s", PQresultErrorMessage(res));
103 err = 1;
104 PQclear(res);
105 } else {
106 PQclear(res);
107 }
109 // calculate member counts:
110 res = PQexec(db, "SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; SELECT \"calculate_member_counts\"()");
111 if (!res) {
112 fprintf(stderr, "Error in pqlib while sending SQL command calculating member counts\n");
113 err = 1;
114 } else if (
115 PQresultStatus(res) != PGRES_COMMAND_OK &&
116 PQresultStatus(res) != PGRES_TUPLES_OK
117 ) {
118 fprintf(stderr, "Error while executing SQL command calculating member counts:\n%s", PQresultErrorMessage(res));
119 err = 1;
120 PQclear(res);
121 } else {
122 PQclear(res);
123 }
125 // update open issues:
126 res = PQexec(db, "SELECT \"id\" FROM \"open_issue\"");
127 if (!res) {
128 fprintf(stderr, "Error in pqlib while sending SQL command selecting open issues\n");
129 err = 1;
130 } else if (PQresultStatus(res) != PGRES_TUPLES_OK) {
131 fprintf(stderr, "Error while executing SQL command selecting open issues:\n%s", PQresultErrorMessage(res));
132 err = 1;
133 PQclear(res);
134 } else {
135 count = PQntuples(res);
136 for (i=0; i<count; i++) {
137 char *issue_id, *escaped_issue_id;
138 PGresult *res2, *old_res2;
139 int j;
140 issue_id = PQgetvalue(res, i, 0);
141 escaped_issue_id = escapeLiteral(db, issue_id, strlen(issue_id));
142 if (!escaped_issue_id) {
143 fprintf(stderr, "Could not escape literal in memory.\n");
144 err = 1;
145 break;
146 }
147 old_res2 = NULL;
148 for (j=0; ; j++) {
149 if (j >= 20) { // safety to avoid endless loops
150 fprintf(stderr, "Function \"check_issue\"(...) returned non-null value too often.\n");
151 err = 1;
152 if (j > 0) PQclear(old_res2);
153 break;
154 }
155 if (j == 0) {
156 char *cmd;
157 if (asprintf(&cmd, "SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; SELECT \"check_issue\"(%s, NULL)", escaped_issue_id) < 0) {
158 fprintf(stderr, "Could not prepare query string in memory.\n");
159 err = 1;
160 break;
161 }
162 res2 = PQexec(db, cmd);
163 free(cmd);
164 } else {
165 char *persist, *escaped_persist, *cmd;
166 persist = PQgetvalue(old_res2, 0, 0);
167 escaped_persist = escapeLiteral(db, persist, strlen(persist));
168 if (!escaped_persist) {
169 fprintf(stderr, "Could not escape literal in memory.\n");
170 err = 1;
171 PQclear(old_res2);
172 break;
173 }
174 if (asprintf(&cmd, "SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; SELECT \"check_issue\"(%s, %s::\"check_issue_persistence\")", escaped_issue_id, escaped_persist) < 0) {
175 freemem(escaped_persist);
176 fprintf(stderr, "Could not prepare query string in memory.\n");
177 err = 1;
178 PQclear(old_res2);
179 break;
180 }
181 freemem(escaped_persist);
182 res2 = PQexec(db, cmd);
183 free(cmd);
184 PQclear(old_res2);
185 }
186 if (!res2) {
187 fprintf(stderr, "Error in pqlib while sending SQL command to call function \"check_issue\"(...):\n");
188 err = 1;
189 break;
190 } else if (
191 PQresultStatus(res2) != PGRES_COMMAND_OK &&
192 PQresultStatus(res2) != PGRES_TUPLES_OK
193 ) {
194 fprintf(stderr, "Error while calling SQL function \"check_issue\"(...):\n%s", PQresultErrorMessage(res2));
195 err = 1;
196 PQclear(res2);
197 break;
198 } else {
199 if (PQntuples(res2) >= 1 && !PQgetisnull(res2, 0, 0)) {
200 old_res2 = res2;
201 } else {
202 PQclear(res2);
203 break;
204 }
205 }
206 }
207 freemem(escaped_issue_id);
208 }
209 PQclear(res);
210 }
212 // cleanup and exit:
213 PQfinish(db);
214 return err;
216 }