1 // This file is part of BOINC.
2 // http://boinc.berkeley.edu
3 // Copyright (C) 2008 University of California
4 //
5 // BOINC is free software; you can redistribute it and/or modify it
6 // under the terms of the GNU Lesser General Public License
7 // as published by the Free Software Foundation,
8 // either version 3 of the License, or (at your option) any later version.
9 //
10 // BOINC is distributed in the hope that it will be useful,
11 // but WITHOUT ANY WARRANTY; without even the implied warranty of
12 // MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
13 // See the GNU Lesser General Public License for more details.
14 //
15 // You should have received a copy of the GNU Lesser General Public License
16 // along with BOINC. If not, see <http://www.gnu.org/licenses/>.
17
18 #include "config.h"
19 #include <cstdio>
20 #include <cstring>
21 #include <cstdlib>
22 #include <mysql.h>
23
24 #include "error_numbers.h"
25 #include "str_util.h"
26 #include "str_replace.h"
27 #include "db_base.h"
28
29 #ifdef _USING_FCGI_
30 #include "fcgi_stdio.h"
31 #include "sched_msgs.h"
32 #endif
33
34 bool g_print_queries = false;
35
DB_CONN()36 DB_CONN::DB_CONN() {
37 mysql = 0;
38 }
39
open(char * db_name,char * db_host,char * db_user,char * dbpassword)40 int DB_CONN::open(
41 char* db_name, char* db_host, char* db_user, char* dbpassword
42 ) {
43 mysql = mysql_init(0);
44 if (!mysql) return ERR_DB_CANT_INIT;
45
46 // MySQL's support for the reconnect option has changed over time:
47 // see http://dev.mysql.com/doc/refman/5.0/en/mysql-options.html
48 // and http://dev.mysql.com/doc/refman/5.1/en/mysql-options.html
49 //
50 // v < 5.0.13: not supported
51 // 5.0.13 <= v < 5.0.19: set option after real_connect()
52 // 5.0.19 < v < 5.1: set option before real_connect();
53 // 5.1.0 <= v < 5.1.6: set option after real_connect()
54 // 5.1.6 <= v: set option before real_connect
55
56 int v = MYSQL_VERSION_ID;
57 bool set_opt_before = false, set_opt_after = false;
58 if (v < 50013 ) {
59 } else if (v < 50019) {
60 set_opt_after = true;
61 } else if (v < 50100) {
62 set_opt_before = true;
63 } else if (v < 50106) {
64 set_opt_after = true;
65 } else {
66 set_opt_before = true;
67 }
68
69 if (set_opt_before) {
70 my_bool mbReconnect = 1;
71 mysql_options(mysql, MYSQL_OPT_RECONNECT, &mbReconnect);
72 }
73
74 // parse hostname:port
75 //
76 char host[256];
77 int port = 0;
78 safe_strcpy(host, db_host);
79 char* p = strchr(host, ':');
80 if (p) {
81 *p = 0;
82 port = atoi(p+1);
83 }
84
85 // CLIENT_FOUND_ROWS means that the # of affected rows for an update
86 // is the # matched by the where, rather than the # actually changed
87 //
88 mysql = mysql_real_connect(
89 mysql, host, db_user, dbpassword, db_name, port, 0, CLIENT_FOUND_ROWS
90 );
91 if (mysql == 0) return ERR_DB_CANT_CONNECT;
92
93 if (set_opt_after) {
94 my_bool mbReconnect = 1;
95 mysql_options(mysql, MYSQL_OPT_RECONNECT, &mbReconnect);
96 }
97 return 0;
98 }
99
close()100 void DB_CONN::close() {
101 if (mysql) mysql_close(mysql);
102 }
103
set_isolation_level(ISOLATION_LEVEL level)104 int DB_CONN::set_isolation_level(ISOLATION_LEVEL level) {
105 const char* level_str;
106 char query[256];
107
108 switch(level) {
109 case READ_UNCOMMITTED:
110 level_str = "READ UNCOMMITTED";
111 break;
112 case READ_COMMITTED:
113 level_str = "READ COMMITTED";
114 break;
115 case REPEATABLE_READ:
116 level_str = "REPEATABLE READ";
117 break;
118 case SERIALIZABLE:
119 level_str = "SERIALIZABLE";
120 break;
121 default:
122 return -1;
123 }
124 sprintf(query, "SET SESSION TRANSACTION ISOLATION LEVEL %s", level_str);
125 return do_query(query);
126 }
127
do_query(const char * p)128 int DB_CONN::do_query(const char* p) {
129 int retval;
130 if (g_print_queries) {
131 #ifdef _USING_FCGI_
132 log_messages.printf(MSG_NORMAL, "query: %s\n", p);
133 #else
134 fprintf(stderr, "query: %s\n", p);
135 #endif
136 }
137 retval = mysql_query(mysql, p);
138 if (retval) {
139 fprintf(stderr, "Database error: %s\nquery=%s\n", error_string(), p);
140 }
141 return retval;
142 }
143
144 // returns the number of rows matched by an update query
145 //
affected_rows()146 int DB_CONN::affected_rows() {
147 unsigned long x = (unsigned long)mysql_affected_rows(mysql);
148 //fprintf(stderr, "x: %lu i: %d\n", x, (int)x);
149 return (int)x;
150 }
151
insert_id()152 int DB_CONN::insert_id() {
153 int retval;
154 MYSQL_ROW row;
155 MYSQL_RES* rp;
156
157 retval = do_query("select LAST_INSERT_ID()");
158 if (retval) return retval;
159 rp = mysql_store_result(mysql);
160 row = mysql_fetch_row(rp);
161 int x = atoi(row[0]);
162 mysql_free_result(rp);
163 return x;
164 }
165
print_error(const char * p)166 void DB_CONN::print_error(const char* p) {
167 fprintf(stderr, "%s: Database error: %s\n", p, error_string());
168 }
169
error_string()170 const char* DB_CONN::error_string() {
171 return mysql?mysql_error(mysql):"Not connected";
172 }
173
start_transaction()174 int DB_CONN::start_transaction() {
175 return do_query("START TRANSACTION");
176 }
177
commit_transaction()178 int DB_CONN::commit_transaction() {
179 return do_query("COMMIT");
180 }
181
rollback_transaction()182 int DB_CONN::rollback_transaction() {
183 return do_query("ROLLBACK");
184 }
185
ping()186 int DB_CONN::ping() {
187 int retval = mysql_ping(mysql);
188 if (retval) return ERR_DB_CANT_CONNECT;
189 return 0;
190 }
191
DB_BASE(const char * tn,DB_CONN * p)192 DB_BASE::DB_BASE(const char *tn, DB_CONN* p) : db(p), table_name(tn) {
193 }
194
get_id()195 DB_ID_TYPE DB_BASE::get_id() { return 0;}
db_print(char *)196 void DB_BASE::db_print(char*) {}
db_parse(MYSQL_ROW &)197 void DB_BASE::db_parse(MYSQL_ROW&) {}
198
insert()199 int DB_BASE::insert() {
200 char vals[MAX_QUERY_LEN*2], query[MAX_QUERY_LEN*2];
201 db_print(vals);
202 sprintf(query, "insert into %s set %s", table_name, vals);
203 return db->do_query(query);
204 }
205
insert_batch(std::string & values)206 int DB_BASE::insert_batch(std::string& values) {
207 std::string query;
208 query = "insert into " + std::string(table_name) + " values " + values;
209 return db->do_query(query.c_str());
210 }
211
affected_rows()212 int DB_BASE::affected_rows() {
213 return db->affected_rows();
214 }
215
216 //////////// FUNCTIONS FOR TABLES THAT HAVE AN ID FIELD ///////
217
lookup_id(DB_ID_TYPE id)218 int DB_BASE::lookup_id(DB_ID_TYPE id) {
219 char query[MAX_QUERY_LEN];
220 int retval;
221 MYSQL_ROW row;
222 MYSQL_RES* rp;
223
224 sprintf(query, "select * from %s where id=%lu", table_name, id);
225
226 retval = db->do_query(query);
227 if (retval) return retval;
228 rp = mysql_store_result(db->mysql);
229 if (!rp) return -1;
230 row = mysql_fetch_row(rp);
231 if (row) db_parse(row);
232 mysql_free_result(rp);
233 if (row == 0) return ERR_DB_NOT_FOUND;
234
235 // don't bother checking for uniqueness here
236 return 0;
237 }
238
239 // update an entire record
240 //
update()241 int DB_BASE::update() {
242 char vals[MAX_QUERY_LEN], query[MAX_QUERY_LEN];
243 db_print(vals);
244 sprintf(query, "update %s set %s where id=%lu", table_name, vals, get_id());
245 int retval = db->do_query(query);
246 if (retval) return retval;
247 if (db->affected_rows() != 1) return ERR_DB_NOT_FOUND;
248 return 0;
249 }
250
251 // update one or more fields
252 // "clause" is something like "foo=5, blah='xxx'" or "foo=foo+5"
253 //
update_field(const char * clause,const char * where_clause)254 int DB_BASE::update_field(const char* clause, const char* where_clause) {
255 char query[MAX_QUERY_LEN];
256 if (where_clause) {
257 sprintf(query, "update %s set %s where id=%lu and %s",
258 table_name, clause, get_id(), where_clause
259 );
260 } else {
261 sprintf(query, "update %s set %s where id=%lu",
262 table_name, clause, get_id()
263 );
264 }
265 return db->do_query(query);
266 }
267
268 // delete record
269 //
delete_from_db()270 int DB_BASE::delete_from_db() {
271 char query[MAX_QUERY_LEN];
272 sprintf(query, "delete from %s where id=%lu", table_name, get_id());
273 return db->do_query(query);
274 }
275
delete_from_db_multi(const char * where_clause)276 int DB_BASE::delete_from_db_multi(const char* where_clause) {
277 char query[MAX_QUERY_LEN];
278 sprintf(query, "delete from %s where %s", table_name, where_clause);
279 return db->do_query(query);
280 }
281
get_field_ints(const char * fields,int nfields,int * vals)282 int DB_BASE::get_field_ints(const char* fields, int nfields, int* vals) {
283 char query[MAX_QUERY_LEN];
284 int retval;
285 MYSQL_ROW row;
286 MYSQL_RES* rp;
287
288 sprintf(query,
289 "select %s from %s where id=%lu", fields, table_name, get_id()
290 );
291 retval = db->do_query(query);
292 if (retval) return retval;
293 rp = mysql_store_result(db->mysql);
294 if (!rp) return -1;
295 row = mysql_fetch_row(rp);
296 if (row) {
297 for (int i=0; i<nfields; i++) {
298 vals[i] = atoi(row[i]);
299 }
300 }
301 mysql_free_result(rp);
302 if (row == 0) return ERR_DB_NOT_FOUND;
303 return 0;
304 }
305
get_field_str(const char * field,char * buf,int buflen)306 int DB_BASE::get_field_str(const char* field, char* buf, int buflen) {
307 char query[MAX_QUERY_LEN];
308 int retval;
309 MYSQL_ROW row;
310 MYSQL_RES* rp;
311
312 sprintf(query,
313 "select %s from %s where id=%lu", field, table_name, get_id()
314 );
315 retval = db->do_query(query);
316 if (retval) return retval;
317 rp = mysql_store_result(db->mysql);
318 if (!rp) return -1;
319 row = mysql_fetch_row(rp);
320 if (row && row[0]) {
321 strlcpy(buf, row[0], buflen);
322 }
323 mysql_free_result(rp);
324 if (row == 0) return ERR_DB_NOT_FOUND;
325 return 0;
326 }
327
max_id(DB_ID_TYPE & n,const char * clause)328 int DB_BASE::max_id(DB_ID_TYPE& n, const char* clause) {
329 char query[MAX_QUERY_LEN];
330 sprintf(query, "select max(id) from %s %s", table_name, clause);
331 return get_long(query, n);
332 }
333
334 /////////////// FUNCTIONS THAT DON'T REQUIRE AN ID FIELD ///////////////
335
lookup(const char * clause)336 int DB_BASE::lookup(const char* clause) {
337 char query[MAX_QUERY_LEN];
338 int retval;
339 MYSQL_ROW row;
340 MYSQL_RES* rp;
341
342 sprintf(query, "select * from %s %s", table_name, clause);
343
344 retval = db->do_query(query);
345 if (retval) return retval;
346 rp = mysql_store_result(db->mysql);
347 if (!rp) return -1;
348 row = mysql_fetch_row(rp);
349 if (row) db_parse(row);
350 mysql_free_result(rp);
351 if (row == 0) return ERR_DB_NOT_FOUND;
352 return 0;
353 }
354
update_fields_noid(const char * set_clause,const char * where_clause)355 int DB_BASE::update_fields_noid(const char* set_clause, const char* where_clause) {
356 char query[MAX_QUERY_LEN];
357 sprintf(query,
358 "update %s set %s where %s",
359 table_name, set_clause, where_clause
360 );
361 int retval = db->do_query(query);
362 if (retval) return retval;
363 return 0;
364 }
365
366 // Note: AFAIK, if one enumeration is active you can't do another one
367 //
enumerate(const char * clause,bool use_use_result)368 int DB_BASE::enumerate(const char* clause, bool use_use_result) {
369 int x;
370 char query[MAX_QUERY_LEN];
371 MYSQL_ROW row;
372
373 if (!cursor.active) {
374 cursor.active = true;
375
376 sprintf(query, "select * from %s %s", table_name, clause);
377
378 x = db->do_query(query);
379 if (x) return mysql_errno(db->mysql);
380
381 // if you use mysql_use_result() here,
382 // any other transactions will fail
383 //
384 if (use_use_result) {
385 cursor.rp = mysql_use_result(db->mysql);
386 } else {
387 cursor.rp = mysql_store_result(db->mysql);
388 }
389 if (!cursor.rp) return mysql_errno(db->mysql);
390 }
391 row = mysql_fetch_row(cursor.rp);
392 if (!row) {
393 mysql_free_result(cursor.rp);
394 cursor.active = false;
395 x = mysql_errno(db->mysql);
396 if (x) return x;
397 return ERR_DB_NOT_FOUND;
398 } else {
399 db_parse(row);
400 }
401 return 0;
402 }
403
404 // call this to end an enumeration before reaching end
405 //
end_enumerate()406 int DB_BASE::end_enumerate() {
407 if (cursor.active) {
408 mysql_free_result(cursor.rp);
409 cursor.active = false;
410 }
411 return 0;
412 }
413
get_long(const char * query,long & n)414 int DB_BASE::get_long(const char* query, long& n) {
415 int retval;
416 MYSQL_ROW row;
417 MYSQL_RES* resp;
418
419 retval = db->do_query(query);
420 if (retval) return retval;
421 resp = mysql_store_result(db->mysql);
422 if (!resp) return ERR_DB_NOT_FOUND;
423 row = mysql_fetch_row(resp);
424 if (!row || !row[0]) {
425 retval = ERR_DB_NOT_FOUND;
426 } else {
427 n = atol(row[0]);
428 }
429 mysql_free_result(resp);
430 return retval;
431 }
432
get_double(const char * query,double & x)433 int DB_CONN::get_double(const char* query, double& x) {
434 int retval;
435 MYSQL_ROW row;
436 MYSQL_RES* resp;
437
438 retval = do_query(query);
439 if (retval) return retval;
440 resp = mysql_store_result(mysql);
441 if (!resp) return ERR_DB_NOT_FOUND;
442 row = mysql_fetch_row(resp);
443 if (!row || !row[0]) {
444 retval = ERR_DB_NOT_FOUND;
445 } else {
446 x = atof(row[0]);
447 }
448 mysql_free_result(resp);
449 return retval;
450 }
451
count(long & n,const char * clause)452 int DB_BASE::count(long& n, const char* clause) {
453 char query[MAX_QUERY_LEN];
454
455 sprintf(query, "select count(*) from %s %s", table_name, clause);
456
457 return get_long(query, n);
458 }
459
sum(double & x,const char * field,const char * clause)460 int DB_BASE::sum(double& x, const char* field, const char* clause) {
461 char query[MAX_QUERY_LEN];
462
463 sprintf(query, "select sum(%s) from %s %s", field, table_name, clause);
464
465 return db->get_double(query, x);
466 }
467
DB_BASE_SPECIAL(DB_CONN * p)468 DB_BASE_SPECIAL::DB_BASE_SPECIAL(DB_CONN* p) : db(p) {
469 }
470
471 // convert a string into a form that allows it to be used
472 // in SQL queries delimited by single quotes:
473 // replace ' with \', '\' with '\\'
474 //
escape_string(char * field,int len)475 void escape_string(char* field, int len) {
476 char buf[MAX_QUERY_LEN];
477 char* q = buf, *p = field;
478
479 if (len > MAX_QUERY_LEN) {
480 len = MAX_QUERY_LEN;
481 }
482
483 // Make sure that final result won't overflow field[].
484 //
485 while (*p && q < buf+len-3) {
486 if (*p == '\'') {
487 // this does ' to \' transformation
488 //
489 *q++ = '\\';
490 *q++ = '\'';
491 } else if (*p == '\\') {
492 // this does \ to \\ transformation
493 //
494 *q++ = '\\';
495 *q++ = '\\';
496 } else {
497 // this handles all other characters
498 //
499 *q++ = *p;
500 }
501 p++;
502 }
503 *q = 0;
504 strlcpy(field, buf, len);
505 }
506
507 // undo the above process
508 // (len not used because this doesn't expand the string)
509 //
unescape_string(char * p,int)510 void unescape_string(char* p, int /*len*/) {
511 char* q = p;
512 while (*p) {
513 if (*p == '\\') {
514 p++;
515 if (!*p) break; // SHOULD NEVER HAPPEN!
516 }
517 *q++ = *p++;
518 }
519 *q='\0';
520 }
521
522 // replace _ with \\_, % with \\%
523 //
escape_mysql_like_pattern(const char * in,char * out)524 void escape_mysql_like_pattern(const char* in, char* out) {
525 while (*in) {
526 if (*in == '_' || *in == '%') {
527 *out++ = '\\';
528 *out++ = '\\';
529 }
530 *out++ = *in++;
531 }
532 }
533
534 const char *BOINC_RCSID_43d919556b = "$Id$";
535