liquid_feedback_core
view lf_update.c @ 334:a2ab4fb1d0c7
Use different transaction isolation levels in "lf_update" program
| author | jbe |
|---|---|
| date | Tue Feb 19 14:40:38 2013 +0100 (2013-02-19) |
| parents | 2a6984869ba3 |
| children | ab370f3b9892 |
line source
1 #include <stdlib.h>
2 #include <stdio.h>
3 #include <string.h>
4 #include <libpq-fe.h>
6 int main(int argc, char **argv) {
8 // variable declarations:
9 int err = 0;
10 int i, count;
11 char *conninfo;
12 PGconn *db;
13 PGresult *res;
15 // parse command line:
16 if (argc == 0) return 1;
17 if (argc == 1 || !strcmp(argv[1], "-h") || !strcmp(argv[1], "--help")) {
18 FILE *out;
19 out = argc == 1 ? stderr : stdout;
20 fprintf(stdout, "\n");
21 fprintf(stdout, "Usage: %s <conninfo>\n", argv[0]);
22 fprintf(stdout, "\n");
23 fprintf(stdout, "<conninfo> is specified by PostgreSQL's libpq,\n");
24 fprintf(stdout, "see http://www.postgresql.org/docs/9.1/static/libpq-connect.html\n");
25 fprintf(stdout, "\n");
26 fprintf(stdout, "Example: %s dbname=liquid_feedback\n", argv[0]);
27 fprintf(stdout, "\n");
28 return argc == 1 ? 1 : 0;
29 }
30 {
31 size_t len = 0;
32 for (i=1; i<argc; i++) len += strlen(argv[i]) + 1;
33 conninfo = malloc(len * sizeof(char));
34 if (!conninfo) {
35 fprintf(stderr, "Error: Could not allocate memory for conninfo string\n");
36 return 1;
37 }
38 conninfo[0] = 0;
39 for (i=1; i<argc; i++) {
40 if (i>1) strcat(conninfo, " ");
41 strcat(conninfo, argv[i]);
42 }
43 }
45 // connect to database:
46 db = PQconnectdb(conninfo);
47 if (!db) {
48 fprintf(stderr, "Error: Could not create database handle\n");
49 return 1;
50 }
51 if (PQstatus(db) != CONNECTION_OK) {
52 fprintf(stderr, "Could not open connection:\n%s", PQerrorMessage(db));
53 return 1;
54 }
56 // delete expired sessions:
57 res = PQexec(db, "DELETE FROM \"expired_session\"");
58 if (!res) {
59 fprintf(stderr, "Error in pqlib while sending SQL command deleting expired sessions\n");
60 err = 1;
61 } else if (
62 PQresultStatus(res) != PGRES_COMMAND_OK &&
63 PQresultStatus(res) != PGRES_TUPLES_OK
64 ) {
65 fprintf(stderr, "Error while executing SQL command deleting expired sessions:\n%s", PQresultErrorMessage(res));
66 err = 1;
67 PQclear(res);
68 } else {
69 PQclear(res);
70 }
72 // check member activity:
73 res = PQexec(db, "SET TRANSACTION ISOLATION LEVEL READ COMMITTED; SELECT \"check_activity\"()");
74 if (!res) {
75 fprintf(stderr, "Error in pqlib while sending SQL command checking member activity\n");
76 err = 1;
77 } else if (
78 PQresultStatus(res) != PGRES_COMMAND_OK &&
79 PQresultStatus(res) != PGRES_TUPLES_OK
80 ) {
81 fprintf(stderr, "Error while executing SQL command checking member activity:\n%s", PQresultErrorMessage(res));
82 err = 1;
83 PQclear(res);
84 } else {
85 PQclear(res);
86 }
88 // calculate member counts:
89 res = PQexec(db, "SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; SELECT \"calculate_member_counts\"()");
90 if (!res) {
91 fprintf(stderr, "Error in pqlib while sending SQL command calculating member counts\n");
92 err = 1;
93 } else if (
94 PQresultStatus(res) != PGRES_COMMAND_OK &&
95 PQresultStatus(res) != PGRES_TUPLES_OK
96 ) {
97 fprintf(stderr, "Error while executing SQL command calculating member counts:\n%s", PQresultErrorMessage(res));
98 err = 1;
99 PQclear(res);
100 } else {
101 PQclear(res);
102 }
104 // update open issues:
105 res = PQexec(db, "SELECT \"id\" FROM \"open_issue\"");
106 if (!res) {
107 fprintf(stderr, "Error in pqlib while sending SQL command selecting open issues\n");
108 err = 1;
109 } else if (PQresultStatus(res) != PGRES_TUPLES_OK) {
110 fprintf(stderr, "Error while executing SQL command selecting open issues:\n%s", PQresultErrorMessage(res));
111 err = 1;
112 PQclear(res);
113 } else {
114 count = PQntuples(res);
115 for (i=0; i<count; i++) {
116 char *issue_id, *escaped_issue_id;
117 PGresult *res2, *old_res2;
118 int j;
119 issue_id = PQgetvalue(res, i, 0);
120 escaped_issue_id = PQescapeLiteral(db, issue_id, strlen(issue_id));
121 old_res2 = NULL;
122 for (j=0; ; j++) {
123 fprintf(stderr, "%i\n", j); // DEBUG
124 if (j >= 20) { // safety to avoid endless loops
125 fprintf(stderr, "Function \"check_issue\"(...) returned non-null value too often.\n");
126 err = 1;
127 if (j > 0) PQclear(old_res2);
128 break;
129 }
130 if (j == 0) {
131 char *cmd;
132 if (asprintf(&cmd, "SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; SELECT \"check_issue\"(%s, NULL)", escaped_issue_id) < 0) {
133 fprintf(stderr, "Could not prepare query string in memory.\n");
134 err = 1;
135 break;
136 }
137 res2 = PQexec(db, cmd);
138 free(cmd);
139 } else {
140 char *persist, *escaped_persist, *cmd;
141 persist = PQgetvalue(old_res2, 0, 0);
142 escaped_persist = PQescapeLiteral(db, persist, strlen(persist));
143 if (asprintf(&cmd, "SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; SELECT \"check_issue\"(%s, %s::\"check_issue_persistence\")", escaped_issue_id, escaped_persist) < 0) {
144 PQfreemem(escaped_persist);
145 fprintf(stderr, "Could not prepare query string in memory.\n");
146 err = 1;
147 PQclear(old_res2);
148 break;
149 }
150 PQfreemem(escaped_persist);
151 res2 = PQexec(db, cmd);
152 free(cmd);
153 PQclear(old_res2);
154 }
155 if (!res2) {
156 fprintf(stderr, "Error in pqlib while sending SQL command to call function \"check_issue\"(...):\n");
157 err = 1;
158 break;
159 } else if (
160 PQresultStatus(res2) != PGRES_COMMAND_OK &&
161 PQresultStatus(res2) != PGRES_TUPLES_OK
162 ) {
163 fprintf(stderr, "Error while calling SQL function \"check_issue\"(...):\n%s", PQresultErrorMessage(res2));
164 err = 1;
165 PQclear(res2);
166 break;
167 } else {
168 if (PQntuples(res2) >= 1 && !PQgetisnull(res2, 0, 0)) {
169 old_res2 = res2;
170 } else {
171 PQclear(res2);
172 break;
173 }
174 }
175 }
176 PQfreemem(escaped_issue_id);
177 }
178 PQclear(res);
179 }
181 // cleanup and exit
182 PQfinish(db);
183 return err;
185 }