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);
303 /* Utility routine for deletes
305 * Returns: -1 on error
306 * n number of rows affected
309 DeleteDB(const char *file, int line, JCR *jcr, B_DB *mdb, char *cmd)
312 if (!sql_query(mdb, cmd)) {
313 m_msg(file, line, &mdb->errmsg, _("delete %s failed:\n%s\n"), cmd, sql_strerror(mdb));
314 j_msg(file, line, jcr, M_ERROR, 0, "%s", mdb->errmsg);
316 j_msg(file, line, jcr, M_INFO, 0, "%s\n", cmd);
321 return sql_affected_rows(mdb);
326 * Get record max. Query is already in mdb->cmd
329 * Returns: -1 on failure
332 int get_sql_record_max(JCR *jcr, B_DB *mdb)
337 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
338 if ((row = sql_fetch_row(mdb)) == NULL) {
339 Mmsg1(&mdb->errmsg, _("error fetching row: %s\n"), sql_strerror(mdb));
342 stat = str_to_int64(row[0]);
344 sql_free_result(mdb);
346 Mmsg1(&mdb->errmsg, _("error fetching row: %s\n"), sql_strerror(mdb));
353 * * Return pre-edited error message
355 char *db_strerror(B_DB *mdb)
361 * Given a full filename, split it into its path
362 * and filename parts. They are returned in pool memory
363 * in the mdb structure.
365 void split_path_and_file(JCR *jcr, B_DB *mdb, const char *fname)
369 /* Find path without the filename.
370 * I.e. everything after the last / is a "filename".
371 * OK, maybe it is a directory name, but we treat it like
372 * a filename. If we don't find a / then the whole name
373 * must be a path name (e.g. c:).
375 for (p=f=fname; *p; p++) {
376 if (IsPathSeparator(*p)) {
377 f = p; /* set pos of last slash */
380 if (IsPathSeparator(*f)) { /* did we find a slash? */
381 f++; /* yes, point to filename */
382 } else { /* no, whole thing must be path name */
386 /* If filename doesn't exist (i.e. root directory), we
387 * simply create a blank name consisting of a single
388 * space. This makes handling zero length filenames
393 mdb->fname = check_pool_memory_size(mdb->fname, mdb->fnl+1);
394 memcpy(mdb->fname, f, mdb->fnl); /* copy filename */
395 mdb->fname[mdb->fnl] = 0;
401 mdb->pnl = f - fname;
403 mdb->path = check_pool_memory_size(mdb->path, mdb->pnl+1);
404 memcpy(mdb->path, fname, mdb->pnl);
405 mdb->path[mdb->pnl] = 0;
407 Mmsg1(&mdb->errmsg, _("Path length is zero. File=%s\n"), fname);
408 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
413 Dmsg2(500, "split path=%s file=%s\n", mdb->path, mdb->fname);
417 * Set maximum field length to something reasonable
419 static int max_length(int max_length)
421 int max_len = max_length;
425 } else if (max_len > 100) {
432 * List dashes as part of header for listing SQL results in a table
434 void list_dashes(B_DB *mdb, DB_LIST_HANDLER *send, void *ctx)
441 sql_field_seek(mdb, 0);
443 num_fields = sql_num_fields(mdb);
444 for (i = 0; i < num_fields; i++) {
445 field = sql_fetch_field(mdb);
449 len = max_length(field->max_length + 2);
450 for (j = 0; j < len; j++) {
458 /* Small handler to print the last line of a list xxx command */
459 static void last_line_handler(void *vctx, const char *str)
461 LIST_CTX *ctx = (LIST_CTX *)vctx;
462 bstrncat(ctx->line, str, sizeof(ctx->line));
465 int list_result(void *vctx, int nb_col, char **row)
468 int i, col_len, max_len = 0;
470 char buf[2000], ewc[30];
472 LIST_CTX *pctx = (LIST_CTX *)vctx;
473 DB_LIST_HANDLER *send = pctx->send;
474 e_list_type type = pctx->type;
475 B_DB *mdb = pctx->mdb;
476 void *ctx = pctx->ctx;
477 JCR *jcr = pctx->jcr;
479 num_fields = sql_num_fields(mdb);
483 Dmsg1(800, "list_result starts looking at %d fields\n", num_fields);
484 /* determine column display widths */
485 sql_field_seek(mdb, 0);
486 for (i = 0; i < num_fields; i++) {
487 Dmsg1(800, "list_result processing field %d\n", i);
488 field = sql_fetch_field(mdb);
492 col_len = cstrlen(field->name);
493 if (type == VERT_LIST) {
494 if (col_len > max_len) {
498 if (sql_field_is_numeric(mdb, field->type) && (int)field->max_length > 0) { /* fixup for commas */
499 field->max_length += (field->max_length - 1) / 3;
501 if (col_len < (int)field->max_length) {
502 col_len = field->max_length;
504 if (col_len < 4 && !sql_field_is_not_null(mdb, field->flags)) {
505 col_len = 4; /* 4 = length of the word "NULL" */
507 field->max_length = col_len; /* reset column info */
513 Dmsg0(800, "list_result finished first loop\n");
514 if (type == VERT_LIST) {
518 Dmsg1(800, "list_result starts second loop looking at %d fields\n", num_fields);
520 /* Keep the result to display the same line at the end of the table */
521 list_dashes(mdb, last_line_handler, pctx);
522 send(ctx, pctx->line);
525 sql_field_seek(mdb, 0);
526 for (i = 0; i < num_fields; i++) {
527 Dmsg1(800, "list_result looking at field %d\n", i);
528 field = sql_fetch_field(mdb);
532 max_len = max_length(field->max_length);
533 bsnprintf(buf, sizeof(buf), " %-*s |", max_len, field->name);
537 list_dashes(mdb, send, ctx);
540 Dmsg1(800, "list_result starts third loop looking at %d fields\n", num_fields);
542 sql_field_seek(mdb, 0);
544 for (i = 0; i < num_fields; i++) {
545 field = sql_fetch_field(mdb);
549 max_len = max_length(field->max_length);
550 if (row[i] == NULL) {
551 bsnprintf(buf, sizeof(buf), " %-*s |", max_len, "NULL");
552 } else if (sql_field_is_numeric(mdb, field->type) && !jcr->gui && is_an_integer(row[i])) {
553 bsnprintf(buf, sizeof(buf), " %*s |", max_len,
554 add_commas(row[i], ewc));
556 bsnprintf(buf, sizeof(buf), " %-*s |", max_len, row[i]);
565 Dmsg1(800, "list_result starts vertical list at %d fields\n", num_fields);
567 sql_field_seek(mdb, 0);
568 for (i = 0; i < num_fields; i++) {
569 field = sql_fetch_field(mdb);
573 if (row[i] == NULL) {
574 bsnprintf(buf, sizeof(buf), " %*s: %s\n", max_len, field->name, "NULL");
575 } else if (sql_field_is_numeric(mdb, field->type) && !jcr->gui && is_an_integer(row[i])) {
576 bsnprintf(buf, sizeof(buf), " %*s: %s\n", max_len, field->name,
577 add_commas(row[i], ewc));
579 bsnprintf(buf, sizeof(buf), " %*s: %s\n", max_len, field->name, row[i]);
588 * If full_list is set, we list vertically, otherwise, we
589 * list on one line horizontally.
591 void list_result(JCR *jcr, B_DB *mdb, DB_LIST_HANDLER *send, void *ctx, e_list_type type)
595 int i, col_len, max_len = 0;
597 char buf[2000], ewc[30];
599 Dmsg0(800, "list_result starts\n");
600 if (sql_num_rows(mdb) == 0) {
601 send(ctx, _("No results to list.\n"));
605 num_fields = sql_num_fields(mdb);
606 Dmsg1(800, "list_result starts looking at %d fields\n", num_fields);
607 /* determine column display widths */
608 sql_field_seek(mdb, 0);
609 for (i = 0; i < num_fields; i++) {
610 Dmsg1(800, "list_result processing field %d\n", i);
611 field = sql_fetch_field(mdb);
615 col_len = cstrlen(field->name);
616 if (type == VERT_LIST) {
617 if (col_len > max_len) {
621 if (sql_field_is_numeric(mdb, field->type) && (int)field->max_length > 0) { /* fixup for commas */
622 field->max_length += (field->max_length - 1) / 3;
624 if (col_len < (int)field->max_length) {
625 col_len = field->max_length;
627 if (col_len < 4 && !sql_field_is_not_null(mdb, field->flags)) {
628 col_len = 4; /* 4 = length of the word "NULL" */
630 field->max_length = col_len; /* reset column info */
634 Dmsg0(800, "list_result finished first loop\n");
635 if (type == VERT_LIST) {
639 Dmsg1(800, "list_result starts second loop looking at %d fields\n", num_fields);
640 list_dashes(mdb, send, ctx);
642 sql_field_seek(mdb, 0);
643 for (i = 0; i < num_fields; i++) {
644 Dmsg1(800, "list_result looking at field %d\n", i);
645 field = sql_fetch_field(mdb);
649 max_len = max_length(field->max_length);
650 bsnprintf(buf, sizeof(buf), " %-*s |", max_len, field->name);
654 list_dashes(mdb, send, ctx);
656 Dmsg1(800, "list_result starts third loop looking at %d fields\n", num_fields);
657 while ((row = sql_fetch_row(mdb)) != NULL) {
658 sql_field_seek(mdb, 0);
660 for (i = 0; i < num_fields; i++) {
661 field = sql_fetch_field(mdb);
665 max_len = max_length(field->max_length);
666 if (row[i] == NULL) {
667 bsnprintf(buf, sizeof(buf), " %-*s |", max_len, "NULL");
668 } else if (sql_field_is_numeric(mdb, field->type) && !jcr->gui && is_an_integer(row[i])) {
669 bsnprintf(buf, sizeof(buf), " %*s |", max_len,
670 add_commas(row[i], ewc));
672 bsnprintf(buf, sizeof(buf), " %-*s |", max_len, row[i]);
678 list_dashes(mdb, send, ctx);
683 Dmsg1(800, "list_result starts vertical list at %d fields\n", num_fields);
684 while ((row = sql_fetch_row(mdb)) != NULL) {
685 sql_field_seek(mdb, 0);
686 for (i = 0; i < num_fields; i++) {
687 field = sql_fetch_field(mdb);
691 if (row[i] == NULL) {
692 bsnprintf(buf, sizeof(buf), " %*s: %s\n", max_len, field->name, "NULL");
693 } else if (sql_field_is_numeric(mdb, field->type) && !jcr->gui && is_an_integer(row[i])) {
694 bsnprintf(buf, sizeof(buf), " %*s: %s\n", max_len, field->name,
695 add_commas(row[i], ewc));
697 bsnprintf(buf, sizeof(buf), " %*s: %s\n", max_len, field->name, row[i]);
707 * Open a new connexion to mdb catalog. This function is used
708 * by batch and accurate mode.
710 bool db_open_batch_connexion(JCR *jcr, B_DB *mdb)
714 if (mdb->batch_insert_available())
715 multi_db = true; /* we force a new connection only if batch insert is enabled */
719 if (!jcr->db_batch) {
720 jcr->db_batch = db_clone_database_connection(mdb, jcr, multi_db);
721 if (!jcr->db_batch) {
722 Mmsg0(&mdb->errmsg, _("Could not init database batch connection"));
723 Jmsg(jcr, M_FATAL, 0, "%s", mdb->errmsg);
727 if (!db_open_database(jcr, jcr->db_batch)) {
728 Mmsg2(&mdb->errmsg, _("Could not open database \"%s\": ERR=%s\n"),
729 jcr->db_batch->get_db_name(), db_strerror(jcr->db_batch));
730 Jmsg(jcr, M_FATAL, 0, "%s", mdb->errmsg);
738 * !!! WARNING !!! Use this function only when bacula is stopped.
739 * ie, after a fatal signal and before exiting the program
740 * Print information about a B_DB object.
742 void db_debug_print(JCR *jcr, FILE *fp)
750 fprintf(fp, "B_DB=%p db_name=%s db_user=%s connected=%s\n",
751 mdb, NPRTB(mdb->get_db_name()), NPRTB(mdb->get_db_user()), mdb->is_connected() ? "true" : "false");
752 fprintf(fp, "\tcmd=\"%s\" changes=%i\n", NPRTB(mdb->cmd), mdb->changes);
753 mdb->print_lock_info(fp);
756 #endif /* HAVE_SQLITE3 || HAVE_MYSQL || HAVE_POSTGRESQL || HAVE_INGRES || HAVE_DBI */