liquid_feedback_core
view lf_update.c @ 554:3e7ad7233404
Renamed events 'member_removed' and 'suggestion_removed' to 'member_deleted' and 'suggestion_deleted'
| author | jbe |
|---|---|
| date | Sat Sep 16 21:20:38 2017 +0200 (2017-09-16) |
| parents | 48ea1c309928 |
| children | aa23fa17604d |
line source
1 #include <stdlib.h>
2 #include <stdio.h>
3 #include <string.h>
4 #include <stdint.h>
5 #include <libpq-fe.h>
7 #define exec_sql_error(message) do { \
8 fprintf(stderr, message ": %s\n%s", command, PQresultErrorMessage(res)); \
9 goto exec_sql_error_clear; \
10 } while (0)
12 int exec_sql(PGconn *db, PGresult **resptr, int *errptr, int onerow, char *command) {
13 int count = 0;
14 PGresult *res = PQexec(db, command);
15 if (!res) {
16 fprintf(stderr, "Error in pqlib while sending the following SQL command: %s\n", command);
17 goto exec_sql_error_exit;
18 }
19 if (
20 PQresultStatus(res) != PGRES_COMMAND_OK &&
21 PQresultStatus(res) != PGRES_TUPLES_OK
22 ) exec_sql_error("Error while executing the following SQL command");
23 if (resptr) {
24 if (PQresultStatus(res) != PGRES_TUPLES_OK) exec_sql_error("The following SQL command returned no result");
25 count = PQntuples(res);
26 if (count < 0) exec_sql_error("The following SQL command returned too many rows");
27 if (onerow) {
28 if (count < 1) exec_sql_error("The following SQL command returned less than one row");
29 else if (count > 1) exec_sql_error("The following SQL command returned more than one row");
30 }
31 *resptr = res;
32 } else {
33 PQclear(res);
34 }
35 return count;
36 exec_sql_error_clear:
37 PQclear(res);
38 exec_sql_error_exit:
39 if (resptr) *resptr = NULL;
40 if (errptr) *errptr = 1;
41 return -1;
42 }
44 int main(int argc, char **argv) {
46 // variable declarations:
47 int err = 0; /* set to 1 if any error occured */
48 int admission_failed = 0; /* set to 1 if error occurred during admission */
49 int i, count;
50 char *conninfo;
51 PGconn *db;
52 PGresult *res;
54 // parse command line:
55 if (argc == 0) return 1;
56 if (argc == 1 || !strcmp(argv[1], "-h") || !strcmp(argv[1], "--help")) {
57 FILE *out;
58 out = argc == 1 ? stderr : stdout;
59 fprintf(out, "\n");
60 fprintf(out, "Usage: %s <conninfo>\n", argv[0]);
61 fprintf(out, "\n");
62 fprintf(out, "<conninfo> is specified by PostgreSQL's libpq,\n");
63 fprintf(out, "see http://www.postgresql.org/docs/9.6/static/libpq-connect.html\n");
64 fprintf(out, "\n");
65 fprintf(out, "Example: %s dbname=liquid_feedback\n", argv[0]);
66 fprintf(out, "\n");
67 return argc == 1 ? 1 : 0;
68 }
69 {
70 size_t len = 0, seglen;
71 for (i=1; i<argc; i++) {
72 seglen = strlen(argv[i]) + 1;
73 if (seglen >= SIZE_MAX/2 || len >= SIZE_MAX/2) {
74 fprintf(stderr, "Error: Command line arguments too long\n");
75 return 1;
76 }
77 len += seglen;
78 }
79 conninfo = malloc(len * sizeof(char));
80 if (!conninfo) {
81 fprintf(stderr, "Error: Could not allocate memory for conninfo string\n");
82 return 1;
83 }
84 conninfo[0] = 0;
85 for (i=1; i<argc; i++) {
86 if (i>1) strcat(conninfo, " ");
87 strcat(conninfo, argv[i]);
88 }
89 }
91 // connect to database:
92 db = PQconnectdb(conninfo);
93 if (!db) {
94 fprintf(stderr, "Error: Could not create database handle\n");
95 return 1;
96 }
97 if (PQstatus(db) != CONNECTION_OK) {
98 fprintf(stderr, "Could not open connection:\n%s", PQerrorMessage(db));
99 return 1;
100 }
102 // delete expired sessions:
103 exec_sql(db, NULL, &err, 0, "DELETE FROM \"expired_session\"");
105 // delete expired tokens and authorization codes:
106 exec_sql(db, NULL, &err, 0, "DELETE FROM \"expired_token\"");
108 // delete expired snapshots:
109 exec_sql(db, NULL, &err, 0, "DELETE FROM \"expired_snapshot\"");
111 // check member activity:
112 exec_sql(db, NULL, &err, 0, "SET TRANSACTION ISOLATION LEVEL READ COMMITTED; SELECT \"check_activity\"()");
114 // calculate member counts:
115 exec_sql(db, NULL, &err, 0, "SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; SELECT \"calculate_member_counts\"()");
117 // issue admission:
118 count = exec_sql(db, &res, &err, 0, "SET TRANSACTION ISOLATION LEVEL READ COMMITTED; SELECT \"id\" FROM \"area_with_unaccepted_issues\"");
119 if (!res) admission_failed = 1;
120 else {
121 char *area_id, *escaped_area_id, *cmd;
122 PGresult *res2;
123 for (i=0; i<count; i++) {
124 area_id = PQgetvalue(res, i, 0);
125 escaped_area_id = PQescapeLiteral(db, area_id, strlen(area_id));
126 if (!escaped_area_id) {
127 fprintf(stderr, "Could not escape literal in memory.\n");
128 err = admission_failed = 1;
129 continue;
130 }
131 if (asprintf(&cmd, "SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; SELECT \"take_snapshot\"(NULL, %s)", escaped_area_id) < 0) {
132 fprintf(stderr, "Could not prepare query string in memory.\n");
133 err = admission_failed = 1;
134 PQfreemem(escaped_area_id);
135 continue;
136 }
137 exec_sql(db, &res2, &err, 1, cmd);
138 free(cmd);
139 if (!res2) admission_failed = 1;
140 else {
141 char *snapshot_id, *escaped_snapshot_id;
142 int j, count2;
143 snapshot_id = PQgetvalue(res2, 0, 0);
144 escaped_snapshot_id = PQescapeLiteral(db, snapshot_id, strlen(snapshot_id));
145 PQclear(res2);
146 if (!escaped_snapshot_id) {
147 fprintf(stderr, "Could not escape literal in memory.\n");
148 err = admission_failed = 1;
149 goto area_admission_cleanup;
150 }
151 if (asprintf(&cmd, "SET TRANSACTION ISOLATION LEVEL READ COMMITTED; SELECT \"issue_id\" FROM \"snapshot_issue\" WHERE \"snapshot_id\" = %s", escaped_snapshot_id) < 0) {
152 fprintf(stderr, "Could not prepare query string in memory.\n");
153 err = admission_failed = 1;
154 PQfreemem(escaped_snapshot_id);
155 goto area_admission_cleanup;
156 }
157 PQfreemem(escaped_snapshot_id);
158 count2 = exec_sql(db, &res2, &err, 0, cmd);
159 free(cmd);
160 if (!res2) admission_failed = 1;
161 else {
162 char *issue_id, *escaped_issue_id;
163 for (j=0; j<count2; j++) {
164 issue_id = PQgetvalue(res2, j, 0);
165 escaped_issue_id = PQescapeLiteral(db, issue_id, strlen(issue_id));
166 if (!escaped_issue_id) {
167 fprintf(stderr, "Could not escape literal in memory.\n");
168 err = admission_failed = 1;
169 continue;
170 }
171 if (asprintf(&cmd, "SET TRANSACTION ISOLATION LEVEL READ COMMITTED; SELECT \"finish_snapshot\"(%s)", escaped_issue_id) < 0) {
172 fprintf(stderr, "Could not prepare query string in memory.\n");
173 err = admission_failed = 1;
174 PQfreemem(escaped_issue_id);
175 continue;
176 }
177 PQfreemem(escaped_issue_id);
178 if (exec_sql(db, NULL, &err, 0, cmd) < 0) admission_failed = 1;
179 free(cmd);
180 }
181 PQclear(res2);
182 }
183 if (!admission_failed) {
184 if (asprintf(&cmd, "SET TRANSACTION ISOLATION LEVEL READ COMMITTED; SELECT \"issue_admission\"(%s)", escaped_area_id) < 0) {
185 fprintf(stderr, "Could not prepare query string in memory.\n");
186 err = admission_failed = 1;
187 goto area_admission_cleanup;
188 }
189 }
190 while (1) {
191 exec_sql(db, &res2, &err, 1, cmd);
192 if (!res2) {
193 admission_failed = 1;
194 break;
195 }
196 if (PQgetvalue(res2, 0, 0)[0] != 't') {
197 PQclear(res2);
198 break;
199 }
200 PQclear(res2);
201 }
202 }
203 area_admission_cleanup:
204 PQfreemem(escaped_area_id);
205 }
206 PQclear(res);
207 }
209 // update open issues:
210 count = exec_sql(
211 db, &res, &err, 0,
212 admission_failed ?
213 "SELECT \"id\" FROM \"open_issue\" WHERE \"state\" != 'admission'::\"issue_state\"" :
214 "SELECT \"id\" FROM \"open_issue\""
215 );
216 for (i=0; i<count; i++) {
217 char *issue_id, *escaped_issue_id;
218 PGresult *res2, *old_res2;
219 int j;
220 issue_id = PQgetvalue(res, i, 0);
221 escaped_issue_id = PQescapeLiteral(db, issue_id, strlen(issue_id));
222 if (!escaped_issue_id) {
223 fprintf(stderr, "Could not escape literal in memory.\n");
224 err = 1;
225 continue;
226 }
227 old_res2 = NULL;
228 for (j=0; ; j++) {
229 if (j >= 20) { // safety to avoid endless loops
230 fprintf(stderr, "Function \"check_issue\"(...) returned non-null value too often.\n");
231 err = 1;
232 if (j > 0) PQclear(old_res2);
233 break;
234 }
235 if (j == 0) {
236 char *cmd;
237 if (asprintf(&cmd, "SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; SELECT \"check_issue\"(%s, NULL)", escaped_issue_id) < 0) {
238 fprintf(stderr, "Could not prepare query string in memory.\n");
239 err = 1;
240 break;
241 }
242 exec_sql(db, &res2, &err, 1, cmd);
243 free(cmd);
244 } else {
245 char *persist, *escaped_persist, *cmd;
246 persist = PQgetvalue(old_res2, 0, 0);
247 escaped_persist = PQescapeLiteral(db, persist, strlen(persist));
248 if (!escaped_persist) {
249 fprintf(stderr, "Could not escape literal in memory.\n");
250 err = 1;
251 PQclear(old_res2);
252 break;
253 }
254 if (asprintf(&cmd, "SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; SELECT \"check_issue\"(%s, %s::\"check_issue_persistence\")", escaped_issue_id, escaped_persist) < 0) {
255 PQfreemem(escaped_persist);
256 fprintf(stderr, "Could not prepare query string in memory.\n");
257 err = 1;
258 PQclear(old_res2);
259 break;
260 }
261 PQfreemem(escaped_persist);
262 exec_sql(db, &res2, &err, 1, cmd);
263 free(cmd);
264 PQclear(old_res2);
265 }
266 if (!res2) break;
267 if (PQgetisnull(res2, 0, 0)) {
268 PQclear(res2);
269 break;
270 }
271 old_res2 = res2;
272 }
273 PQfreemem(escaped_issue_id);
274 }
275 if (res) PQclear(res);
277 // cleanup and exit:
278 PQfinish(db);
279 return err;
281 }