2 Bacula® - The Network Backup Solution
4 Copyright (C) 2000-2009 Free Software Foundation Europe e.V.
6 The main author of Bacula is Kern Sibbald, with contributions from
7 many others, a complete list can be found in the file AUTHORS.
8 This program is Free Software; you can redistribute it and/or
9 modify it under the terms of version three of the GNU Affero General Public
10 License as published by the Free Software Foundation and included
13 This program is distributed in the hope that it will be useful, but
14 WITHOUT ANY WARRANTY; without even the implied warranty of
15 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
16 General Public License for more details.
18 You should have received a copy of the GNU Affero General Public License
19 along with this program; if not, write to the Free Software
20 Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA
23 Bacula® is a registered trademark of Kern Sibbald.
24 The licensor of Bacula is the Free Software Foundation Europe
25 (FSFE), Fiduciary Program, Sumatrastrasse 25, 8006 Zürich,
26 Switzerland, email:ftf@fsfeurope.org.
29 * Bacula Catalog Database interface routines
31 * Almost generic set of SQL database interface routines
32 * (with a little more work)
33 * SQL engine specific routines are in mysql.c, postgresql.c,
36 * Kern Sibbald, March 2000
38 * Version $Id: sql.c 8034 2008-11-11 14:33:46Z ricozz $
43 #if HAVE_SQLITE3 || HAVE_MYSQL || HAVE_POSTGRESQL || HAVE_INGRES || HAVE_DBI
49 /* Forward referenced subroutines */
50 void print_dashes(B_DB *mdb);
51 void print_result(B_DB *mdb);
53 dbid_list::dbid_list()
55 memset(this, 0, sizeof(dbid_list));
57 DBId = (DBId_t *)malloc(max_ids * sizeof(DBId_t));
58 num_ids = num_seen = tot_ids = 0;
62 dbid_list::~dbid_list()
68 * Called here to retrieve an integer from the database
70 int db_int_handler(void *ctx, int num_fields, char **row)
72 uint32_t *val = (uint32_t *)ctx;
74 Dmsg1(800, "int_handler starts with row pointing at %x\n", row);
77 Dmsg1(800, "int_handler finds '%s'\n", row[0]);
78 *val = str_to_int64(row[0]);
80 Dmsg0(800, "int_handler finds zero\n");
83 Dmsg0(800, "int_handler finishes\n");
88 * Called here to retrieve a 32/64 bit integer from the database.
89 * The returned integer will be extended to 64 bit.
91 int db_int64_handler(void *ctx, int num_fields, char **row)
93 db_int64_ctx *lctx = (db_int64_ctx *)ctx;
96 lctx->value = str_to_int64(row[0]);
103 * Called here to retrieve a btime from the database.
104 * The returned integer will be extended to 64 bit.
106 int db_strtime_handler(void *ctx, int num_fields, char **row)
108 db_int64_ctx *lctx = (db_int64_ctx *)ctx;
111 lctx->value = str_to_utime(row[0]);
118 * Use to build a comma separated list of values from a query. "10,20,30"
120 int db_list_handler(void *ctx, int num_fields, char **row)
122 db_list_ctx *lctx = (db_list_ctx *)ctx;
123 if (num_fields == 1 && row[0]) {
130 * specific context passed from db_check_max_connections to db_max_connections_handler.
132 struct max_connections_context {
134 uint32_t nr_connections;
138 * Called here to retrieve an integer from the database
140 static inline int db_max_connections_handler(void *ctx, int num_fields, char **row)
142 struct max_connections_context *context;
145 context = (struct max_connections_context *)ctx;
146 switch (db_get_type_index(context->db)) {
154 context->nr_connections = str_to_int64(row[index]);
156 Dmsg0(800, "int_handler finds zero\n");
157 context->nr_connections = 0;
163 * Check catalog max_connections setting
165 bool db_check_max_connections(JCR *jcr, B_DB *mdb, uint32_t max_concurrent_jobs)
167 struct max_connections_context context;
169 /* Without Batch insert, no need to verify max_connections */
170 if (!mdb->batch_insert_available())
174 context.nr_connections = 0;
176 /* Check max_connections setting */
177 if (!db_sql_query(mdb, sql_get_max_connections[db_get_type_index(mdb)],
178 db_max_connections_handler, &context)) {
179 Jmsg(jcr, M_ERROR, 0, "Can't verify max_connections settings %s", mdb->errmsg);
182 if (context.nr_connections && max_concurrent_jobs && max_concurrent_jobs > context.nr_connections) {
184 _("Potential performance problem:\n"
185 "max_connections=%d set for %s database \"%s\" should be larger than Director's "
186 "MaxConcurrentJobs=%d\n"),
187 context.nr_connections, db_get_type(mdb), mdb->get_db_name(), max_concurrent_jobs);
188 Jmsg(jcr, M_WARNING, 0, "%s", mdb->errmsg);
195 /* NOTE!!! The following routines expect that the
196 * calling subroutine sets and clears the mutex
199 /* Check that the tables correspond to the version we want */
200 bool check_tables_version(JCR *jcr, B_DB *mdb)
202 uint32_t bacula_db_version = 0;
203 const char *query = "SELECT VersionId FROM Version";
205 bacula_db_version = 0;
206 if (!db_sql_query(mdb, query, db_int_handler, (void *)&bacula_db_version)) {
207 Jmsg(jcr, M_FATAL, 0, "%s", mdb->errmsg);
210 if (bacula_db_version != BDB_VERSION) {
211 Mmsg(mdb->errmsg, "Version error for database \"%s\". Wanted %d, got %d\n",
212 mdb->get_db_name(), BDB_VERSION, bacula_db_version);
213 Jmsg(jcr, M_FATAL, 0, "%s", mdb->errmsg);
220 * Utility routine for queries. The database MUST be locked before calling here.
221 * Returns: 0 on failure
225 QueryDB(const char *file, int line, JCR *jcr, B_DB *mdb, char *cmd)
227 sql_free_result(mdb);
228 if (!sql_query(mdb, cmd, QF_STORE_RESULT)) {
229 m_msg(file, line, &mdb->errmsg, _("query %s failed:\n%s\n"), cmd, sql_strerror(mdb));
230 j_msg(file, line, jcr, M_FATAL, 0, "%s", mdb->errmsg);
232 j_msg(file, line, jcr, M_INFO, 0, "%s\n", cmd);
241 * Utility routine to do inserts
242 * Returns: 0 on failure
246 InsertDB(const char *file, int line, JCR *jcr, B_DB *mdb, char *cmd)
250 if (!sql_query(mdb, cmd)) {
251 m_msg(file, line, &mdb->errmsg, _("insert %s failed:\n%s\n"), cmd, sql_strerror(mdb));
252 j_msg(file, line, jcr, M_FATAL, 0, "%s", mdb->errmsg);
254 j_msg(file, line, jcr, M_INFO, 0, "%s\n", cmd);
258 num_rows = sql_affected_rows(mdb);
261 m_msg(file, line, &mdb->errmsg, _("Insertion problem: affected_rows=%s\n"),
262 edit_uint64(num_rows, ed1));
264 j_msg(file, line, jcr, M_INFO, 0, "%s\n", cmd);
272 /* Utility routine for updates.
273 * Returns: 0 on failure
277 UpdateDB(const char *file, int line, JCR *jcr, B_DB *mdb, char *cmd)
281 if (!sql_query(mdb, cmd)) {
282 m_msg(file, line, &mdb->errmsg, _("update %s failed:\n%s\n"), cmd, sql_strerror(mdb));
283 j_msg(file, line, jcr, M_ERROR, 0, "%s", mdb->errmsg);
285 j_msg(file, line, jcr, M_INFO, 0, "%s\n", cmd);
289 num_rows = sql_affected_rows(mdb);
292 m_msg(file, line, &mdb->errmsg, _("Update failed: affected_rows=%s for %s\n"),
293 edit_uint64(num_rows, ed1), cmd);
295 // j_msg(file, line, jcr, M_INFO, 0, "%s\n", cmd);
304 * Utility routine for deletes
306 * Returns: -1 on error
307 * n number of rows affected
310 DeleteDB(const char *file, int line, JCR *jcr, B_DB *mdb, char *cmd)
313 if (!sql_query(mdb, cmd)) {
314 m_msg(file, line, &mdb->errmsg, _("delete %s failed:\n%s\n"), cmd, sql_strerror(mdb));
315 j_msg(file, line, jcr, M_ERROR, 0, "%s", mdb->errmsg);
317 j_msg(file, line, jcr, M_INFO, 0, "%s\n", cmd);
322 return sql_affected_rows(mdb);
327 * Get record max. Query is already in mdb->cmd
330 * Returns: -1 on failure
333 int get_sql_record_max(JCR *jcr, B_DB *mdb)
338 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
339 if ((row = sql_fetch_row(mdb)) == NULL) {
340 Mmsg1(&mdb->errmsg, _("error fetching row: %s\n"), sql_strerror(mdb));
343 stat = str_to_int64(row[0]);
345 sql_free_result(mdb);
347 Mmsg1(&mdb->errmsg, _("error fetching row: %s\n"), sql_strerror(mdb));
354 * Return pre-edited error message
356 char *db_strerror(B_DB *mdb)
362 * Given a full filename, split it into its path
363 * and filename parts. They are returned in pool memory
364 * in the mdb structure.
366 void split_path_and_file(JCR *jcr, B_DB *mdb, const char *fname)
370 /* Find path without the filename.
371 * I.e. everything after the last / is a "filename".
372 * OK, maybe it is a directory name, but we treat it like
373 * a filename. If we don't find a / then the whole name
374 * must be a path name (e.g. c:).
376 for (p=f=fname; *p; p++) {
377 if (IsPathSeparator(*p)) {
378 f = p; /* set pos of last slash */
381 if (IsPathSeparator(*f)) { /* did we find a slash? */
382 f++; /* yes, point to filename */
383 } else { /* no, whole thing must be path name */
387 /* If filename doesn't exist (i.e. root directory), we
388 * simply create a blank name consisting of a single
389 * space. This makes handling zero length filenames
394 mdb->fname = check_pool_memory_size(mdb->fname, mdb->fnl+1);
395 memcpy(mdb->fname, f, mdb->fnl); /* copy filename */
396 mdb->fname[mdb->fnl] = 0;
402 mdb->pnl = f - fname;
404 mdb->path = check_pool_memory_size(mdb->path, mdb->pnl+1);
405 memcpy(mdb->path, fname, mdb->pnl);
406 mdb->path[mdb->pnl] = 0;
408 Mmsg1(&mdb->errmsg, _("Path length is zero. File=%s\n"), fname);
409 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
414 Dmsg2(500, "split path=%s file=%s\n", mdb->path, mdb->fname);
418 * Set maximum field length to something reasonable
420 static int max_length(int max_length)
422 int max_len = max_length;
426 } else if (max_len > 100) {
433 * List dashes as part of header for listing SQL results in a table
435 void list_dashes(B_DB *mdb, DB_LIST_HANDLER *send, void *ctx)
442 sql_field_seek(mdb, 0);
444 num_fields = sql_num_fields(mdb);
445 for (i = 0; i < num_fields; i++) {
446 field = sql_fetch_field(mdb);
450 len = max_length(field->max_length + 2);
451 for (j = 0; j < len; j++) {
459 /* Small handler to print the last line of a list xxx command */
460 static void last_line_handler(void *vctx, const char *str)
462 LIST_CTX *ctx = (LIST_CTX *)vctx;
463 bstrncat(ctx->line, str, sizeof(ctx->line));
466 int list_result(void *vctx, int nb_col, char **row)
469 int i, col_len, max_len = 0;
471 char buf[2000], ewc[30];
473 LIST_CTX *pctx = (LIST_CTX *)vctx;
474 DB_LIST_HANDLER *send = pctx->send;
475 e_list_type type = pctx->type;
476 B_DB *mdb = pctx->mdb;
477 void *ctx = pctx->ctx;
478 JCR *jcr = pctx->jcr;
480 num_fields = sql_num_fields(mdb);
484 Dmsg1(800, "list_result starts looking at %d fields\n", num_fields);
485 /* determine column display widths */
486 sql_field_seek(mdb, 0);
487 for (i = 0; i < num_fields; i++) {
488 Dmsg1(800, "list_result processing field %d\n", i);
489 field = sql_fetch_field(mdb);
493 col_len = cstrlen(field->name);
494 if (type == VERT_LIST) {
495 if (col_len > max_len) {
499 if (sql_field_is_numeric(mdb, field->type) && (int)field->max_length > 0) { /* fixup for commas */
500 field->max_length += (field->max_length - 1) / 3;
502 if (col_len < (int)field->max_length) {
503 col_len = field->max_length;
505 if (col_len < 4 && !sql_field_is_not_null(mdb, field->flags)) {
506 col_len = 4; /* 4 = length of the word "NULL" */
508 field->max_length = col_len; /* reset column info */
514 Dmsg0(800, "list_result finished first loop\n");
515 if (type == VERT_LIST) {
519 Dmsg1(800, "list_result starts second loop looking at %d fields\n", num_fields);
521 /* Keep the result to display the same line at the end of the table */
522 list_dashes(mdb, last_line_handler, pctx);
523 send(ctx, pctx->line);
526 sql_field_seek(mdb, 0);
527 for (i = 0; i < num_fields; i++) {
528 Dmsg1(800, "list_result looking at field %d\n", i);
529 field = sql_fetch_field(mdb);
533 max_len = max_length(field->max_length);
534 bsnprintf(buf, sizeof(buf), " %-*s |", max_len, field->name);
538 list_dashes(mdb, send, ctx);
541 Dmsg1(800, "list_result starts third loop looking at %d fields\n", num_fields);
543 sql_field_seek(mdb, 0);
545 for (i = 0; i < num_fields; i++) {
546 field = sql_fetch_field(mdb);
550 max_len = max_length(field->max_length);
551 if (row[i] == NULL) {
552 bsnprintf(buf, sizeof(buf), " %-*s |", max_len, "NULL");
553 } else if (sql_field_is_numeric(mdb, field->type) && !jcr->gui && is_an_integer(row[i])) {
554 bsnprintf(buf, sizeof(buf), " %*s |", max_len,
555 add_commas(row[i], ewc));
557 bsnprintf(buf, sizeof(buf), " %-*s |", max_len, row[i]);
566 Dmsg1(800, "list_result starts vertical list at %d fields\n", num_fields);
568 sql_field_seek(mdb, 0);
569 for (i = 0; i < num_fields; i++) {
570 field = sql_fetch_field(mdb);
574 if (row[i] == NULL) {
575 bsnprintf(buf, sizeof(buf), " %*s: %s\n", max_len, field->name, "NULL");
576 } else if (sql_field_is_numeric(mdb, field->type) && !jcr->gui && is_an_integer(row[i])) {
577 bsnprintf(buf, sizeof(buf), " %*s: %s\n", max_len, field->name,
578 add_commas(row[i], ewc));
580 bsnprintf(buf, sizeof(buf), " %*s: %s\n", max_len, field->name, row[i]);
589 * If full_list is set, we list vertically, otherwise, we
590 * list on one line horizontally.
591 * Return number of rows
593 int list_result(JCR *jcr, B_DB *mdb, DB_LIST_HANDLER *send, void *ctx, e_list_type type)
597 int i, col_len, max_len = 0;
599 char buf[2000], ewc[30];
601 Dmsg0(800, "list_result starts\n");
602 if (sql_num_rows(mdb) == 0) {
603 send(ctx, _("No results to list.\n"));
604 return sql_num_rows(mdb);
607 num_fields = sql_num_fields(mdb);
608 Dmsg1(800, "list_result starts looking at %d fields\n", num_fields);
609 /* determine column display widths */
610 sql_field_seek(mdb, 0);
611 for (i = 0; i < num_fields; i++) {
612 Dmsg1(800, "list_result processing field %d\n", i);
613 field = sql_fetch_field(mdb);
617 col_len = cstrlen(field->name);
618 if (type == VERT_LIST) {
619 if (col_len > max_len) {
623 if (sql_field_is_numeric(mdb, field->type) && (int)field->max_length > 0) { /* fixup for commas */
624 field->max_length += (field->max_length - 1) / 3;
626 if (col_len < (int)field->max_length) {
627 col_len = field->max_length;
629 if (col_len < 4 && !sql_field_is_not_null(mdb, field->flags)) {
630 col_len = 4; /* 4 = length of the word "NULL" */
632 field->max_length = col_len; /* reset column info */
636 Dmsg0(800, "list_result finished first loop\n");
637 if (type == VERT_LIST) {
641 Dmsg1(800, "list_result starts second loop looking at %d fields\n", num_fields);
642 list_dashes(mdb, send, ctx);
644 sql_field_seek(mdb, 0);
645 for (i = 0; i < num_fields; i++) {
646 Dmsg1(800, "list_result looking at field %d\n", i);
647 field = sql_fetch_field(mdb);
651 max_len = max_length(field->max_length);
652 bsnprintf(buf, sizeof(buf), " %-*s |", max_len, field->name);
656 list_dashes(mdb, send, ctx);
658 Dmsg1(800, "list_result starts third loop looking at %d fields\n", num_fields);
659 while ((row = sql_fetch_row(mdb)) != NULL) {
660 sql_field_seek(mdb, 0);
662 for (i = 0; i < num_fields; i++) {
663 field = sql_fetch_field(mdb);
667 max_len = max_length(field->max_length);
668 if (row[i] == NULL) {
669 bsnprintf(buf, sizeof(buf), " %-*s |", max_len, "NULL");
670 } else if (sql_field_is_numeric(mdb, field->type) && !jcr->gui && is_an_integer(row[i])) {
671 bsnprintf(buf, sizeof(buf), " %*s |", max_len,
672 add_commas(row[i], ewc));
674 bsnprintf(buf, sizeof(buf), " %-*s |", max_len, row[i]);
680 list_dashes(mdb, send, ctx);
681 return sql_num_rows(mdb);
685 Dmsg1(800, "list_result starts vertical list at %d fields\n", num_fields);
686 while ((row = sql_fetch_row(mdb)) != NULL) {
687 sql_field_seek(mdb, 0);
688 for (i = 0; i < num_fields; i++) {
689 field = sql_fetch_field(mdb);
693 if (row[i] == NULL) {
694 bsnprintf(buf, sizeof(buf), " %*s: %s\n", max_len, field->name, "NULL");
695 } else if (sql_field_is_numeric(mdb, field->type) && !jcr->gui && is_an_integer(row[i])) {
696 bsnprintf(buf, sizeof(buf), " %*s: %s\n", max_len, field->name,
697 add_commas(row[i], ewc));
699 bsnprintf(buf, sizeof(buf), " %*s: %s\n", max_len, field->name, row[i]);
705 return sql_num_rows(mdb);
709 * Open a new connexion to mdb catalog. This function is used
710 * by batch and accurate mode.
712 bool db_open_batch_connexion(JCR *jcr, B_DB *mdb)
716 if (mdb->batch_insert_available())
717 multi_db = true; /* we force a new connection only if batch insert is enabled */
721 if (!jcr->db_batch) {
722 jcr->db_batch = db_clone_database_connection(mdb, jcr, multi_db);
723 if (!jcr->db_batch) {
724 Mmsg0(&mdb->errmsg, _("Could not init database batch connection"));
725 Jmsg(jcr, M_FATAL, 0, "%s", mdb->errmsg);
729 if (!db_open_database(jcr, jcr->db_batch)) {
730 Mmsg2(&mdb->errmsg, _("Could not open database \"%s\": ERR=%s\n"),
731 jcr->db_batch->get_db_name(), db_strerror(jcr->db_batch));
732 Jmsg(jcr, M_FATAL, 0, "%s", mdb->errmsg);
740 * !!! WARNING !!! Use this function only when bacula is stopped.
741 * ie, after a fatal signal and before exiting the program
742 * Print information about a B_DB object.
744 void db_debug_print(JCR *jcr, FILE *fp)
752 fprintf(fp, "B_DB=%p db_name=%s db_user=%s connected=%s\n",
753 mdb, NPRTB(mdb->get_db_name()), NPRTB(mdb->get_db_user()), mdb->is_connected() ? "true" : "false");
754 fprintf(fp, "\tcmd=\"%s\" changes=%i\n", NPRTB(mdb->cmd), mdb->changes);
755 mdb->print_lock_info(fp);
758 #endif /* HAVE_SQLITE3 || HAVE_MYSQL || HAVE_POSTGRESQL || HAVE_INGRES || HAVE_DBI */