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