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