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 * Use to build a comma separated list of values from a query. "10,20,30"
105 int db_list_handler(void *ctx, int num_fields, char **row)
107 db_list_ctx *lctx = (db_list_ctx *)ctx;
108 if (num_fields == 1 && row[0]) {
110 pm_strcat(lctx->list, ",");
112 pm_strcat(lctx->list, row[0]);
119 * * specific context passed from db_check_max_connections to db_max_connections_handler.
121 struct max_connections_context {
123 uint32_t nr_connections;
127 * * Called here to retrieve an integer from the database
129 static int db_max_connections_handler(void *ctx, int num_fields, char **row)
131 struct max_connections_context *context;
134 context = (struct max_connections_context *)ctx;
135 switch (db_get_type_index(context->db)) {
143 context->nr_connections = str_to_int64(row[index]);
145 Dmsg0(800, "int_handler finds zero\n");
146 context->nr_connections = 0;
152 * * Check catalog max_connections setting
154 bool db_check_max_connections(JCR *jcr, B_DB *mdb, uint32_t max_concurrent_jobs)
156 struct max_connections_context context;
158 /* Without Batch insert, no need to verify max_connections */
159 if (!mdb->batch_insert_available())
163 context.nr_connections = 0;
165 /* Check max_connections setting */
166 if (!db_sql_query(mdb, sql_get_max_connections[db_get_type_index(mdb)],
167 db_max_connections_handler, &context)) {
168 Jmsg(jcr, M_ERROR, 0, "Can't verify max_connections settings %s", mdb->errmsg);
171 if (context.nr_connections && max_concurrent_jobs && max_concurrent_jobs > context.nr_connections) {
173 _("Potential performance problem:\n"
174 "max_connections=%d set for %s database \"%s\" should be larger than Director's "
175 "MaxConcurrentJobs=%d\n"),
176 context.nr_connections, db_get_type(mdb), mdb->get_db_name(), max_concurrent_jobs);
177 Jmsg(jcr, M_WARNING, 0, "%s", mdb->errmsg);
184 /* NOTE!!! The following routines expect that the
185 * calling subroutine sets and clears the mutex
188 /* Check that the tables correspond to the version we want */
189 bool check_tables_version(JCR *jcr, B_DB *mdb)
191 uint32_t bacula_db_version = 0;
192 const char *query = "SELECT VersionId FROM Version";
194 bacula_db_version = 0;
195 if (!db_sql_query(mdb, query, db_int_handler, (void *)&bacula_db_version)) {
196 Jmsg(jcr, M_FATAL, 0, "%s", mdb->errmsg);
199 if (bacula_db_version != BDB_VERSION) {
200 Mmsg(mdb->errmsg, "Version error for database \"%s\". Wanted %d, got %d\n",
201 mdb->get_db_name(), BDB_VERSION, bacula_db_version);
202 Jmsg(jcr, M_FATAL, 0, "%s", mdb->errmsg);
209 * Utility routine for queries. The database MUST be locked before calling here.
210 * Returns: 0 on failure
214 QueryDB(const char *file, int line, JCR *jcr, B_DB *mdb, char *cmd)
216 sql_free_result(mdb);
217 if (!sql_query(mdb, cmd, QF_STORE_RESULT)) {
218 m_msg(file, line, &mdb->errmsg, _("query %s failed:\n%s\n"), cmd, sql_strerror(mdb));
219 j_msg(file, line, jcr, M_FATAL, 0, "%s", mdb->errmsg);
221 j_msg(file, line, jcr, M_INFO, 0, "%s\n", cmd);
230 * Utility routine to do inserts
231 * Returns: 0 on failure
235 InsertDB(const char *file, int line, JCR *jcr, B_DB *mdb, char *cmd)
239 if (!sql_query(mdb, cmd)) {
240 m_msg(file, line, &mdb->errmsg, _("insert %s failed:\n%s\n"), cmd, sql_strerror(mdb));
241 j_msg(file, line, jcr, M_FATAL, 0, "%s", mdb->errmsg);
243 j_msg(file, line, jcr, M_INFO, 0, "%s\n", cmd);
247 num_rows = sql_affected_rows(mdb);
250 m_msg(file, line, &mdb->errmsg, _("Insertion problem: affected_rows=%s\n"),
251 edit_uint64(num_rows, ed1));
253 j_msg(file, line, jcr, M_INFO, 0, "%s\n", cmd);
261 /* Utility routine for updates.
262 * Returns: 0 on failure
266 UpdateDB(const char *file, int line, JCR *jcr, B_DB *mdb, char *cmd)
270 if (!sql_query(mdb, cmd)) {
271 m_msg(file, line, &mdb->errmsg, _("update %s failed:\n%s\n"), cmd, sql_strerror(mdb));
272 j_msg(file, line, jcr, M_ERROR, 0, "%s", mdb->errmsg);
274 j_msg(file, line, jcr, M_INFO, 0, "%s\n", cmd);
278 num_rows = sql_affected_rows(mdb);
281 m_msg(file, line, &mdb->errmsg, _("Update failed: affected_rows=%s for %s\n"),
282 edit_uint64(num_rows, ed1), cmd);
284 // j_msg(file, line, jcr, M_INFO, 0, "%s\n", cmd);
292 /* Utility routine for deletes
294 * Returns: -1 on error
295 * n number of rows affected
298 DeleteDB(const char *file, int line, JCR *jcr, B_DB *mdb, char *cmd)
301 if (!sql_query(mdb, cmd)) {
302 m_msg(file, line, &mdb->errmsg, _("delete %s failed:\n%s\n"), cmd, sql_strerror(mdb));
303 j_msg(file, line, jcr, M_ERROR, 0, "%s", mdb->errmsg);
305 j_msg(file, line, jcr, M_INFO, 0, "%s\n", cmd);
310 return sql_affected_rows(mdb);
315 * Get record max. Query is already in mdb->cmd
318 * Returns: -1 on failure
321 int get_sql_record_max(JCR *jcr, B_DB *mdb)
326 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
327 if ((row = sql_fetch_row(mdb)) == NULL) {
328 Mmsg1(&mdb->errmsg, _("error fetching row: %s\n"), sql_strerror(mdb));
331 stat = str_to_int64(row[0]);
333 sql_free_result(mdb);
335 Mmsg1(&mdb->errmsg, _("error fetching row: %s\n"), sql_strerror(mdb));
342 * * Return pre-edited error message
344 char *db_strerror(B_DB *mdb)
350 * Given a full filename, split it into its path
351 * and filename parts. They are returned in pool memory
352 * in the mdb structure.
354 void split_path_and_file(JCR *jcr, B_DB *mdb, const char *fname)
358 /* Find path without the filename.
359 * I.e. everything after the last / is a "filename".
360 * OK, maybe it is a directory name, but we treat it like
361 * a filename. If we don't find a / then the whole name
362 * must be a path name (e.g. c:).
364 for (p=f=fname; *p; p++) {
365 if (IsPathSeparator(*p)) {
366 f = p; /* set pos of last slash */
369 if (IsPathSeparator(*f)) { /* did we find a slash? */
370 f++; /* yes, point to filename */
371 } else { /* no, whole thing must be path name */
375 /* If filename doesn't exist (i.e. root directory), we
376 * simply create a blank name consisting of a single
377 * space. This makes handling zero length filenames
382 mdb->fname = check_pool_memory_size(mdb->fname, mdb->fnl+1);
383 memcpy(mdb->fname, f, mdb->fnl); /* copy filename */
384 mdb->fname[mdb->fnl] = 0;
390 mdb->pnl = f - fname;
392 mdb->path = check_pool_memory_size(mdb->path, mdb->pnl+1);
393 memcpy(mdb->path, fname, mdb->pnl);
394 mdb->path[mdb->pnl] = 0;
396 Mmsg1(&mdb->errmsg, _("Path length is zero. File=%s\n"), fname);
397 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
402 Dmsg2(500, "split path=%s file=%s\n", mdb->path, mdb->fname);
406 * Set maximum field length to something reasonable
408 static int max_length(int max_length)
410 int max_len = max_length;
414 } else if (max_len > 100) {
421 * List dashes as part of header for listing SQL results in a table
424 list_dashes(B_DB *mdb, DB_LIST_HANDLER *send, void *ctx)
430 sql_field_seek(mdb, 0);
432 for (i = 0; i < sql_num_fields(mdb); i++) {
433 field = sql_fetch_field(mdb);
437 len = max_length(field->max_length + 2);
438 for (j = 0; j < len; j++) {
446 /* Small handler to print the last line of a list xxx command */
447 static void last_line_handler(void *vctx, const char *str)
449 LIST_CTX *ctx = (LIST_CTX *)vctx;
450 bstrncat(ctx->line, str, sizeof(ctx->line));
453 int list_result(void *vctx, int nb_col, char **row)
456 int i, col_len, max_len = 0;
457 char buf[2000], ewc[30];
459 LIST_CTX *pctx = (LIST_CTX *)vctx;
460 DB_LIST_HANDLER *send = pctx->send;
461 e_list_type type = pctx->type;
462 B_DB *mdb = pctx->mdb;
463 void *ctx = pctx->ctx;
464 JCR *jcr = pctx->jcr;
469 Dmsg1(800, "list_result starts looking at %d fields\n", sql_num_fields(mdb));
470 /* determine column display widths */
471 sql_field_seek(mdb, 0);
472 for (i = 0; i < sql_num_fields(mdb); i++) {
473 Dmsg1(800, "list_result processing field %d\n", i);
474 field = sql_fetch_field(mdb);
478 col_len = cstrlen(field->name);
479 if (type == VERT_LIST) {
480 if (col_len > max_len) {
484 if (sql_field_is_numeric(mdb, field->type) && (int)field->max_length > 0) { /* fixup for commas */
485 field->max_length += (field->max_length - 1) / 3;
487 if (col_len < (int)field->max_length) {
488 col_len = field->max_length;
490 if (col_len < 4 && !sql_field_is_not_null(mdb, field->flags)) {
491 col_len = 4; /* 4 = length of the word "NULL" */
493 field->max_length = col_len; /* reset column info */
499 Dmsg0(800, "list_result finished first loop\n");
500 if (type == VERT_LIST) {
504 Dmsg1(800, "list_result starts second loop looking at %d fields\n",
505 sql_num_fields(mdb));
507 /* Keep the result to display the same line at the end of the table */
508 list_dashes(mdb, last_line_handler, pctx);
509 send(ctx, pctx->line);
512 sql_field_seek(mdb, 0);
513 for (i = 0; i < sql_num_fields(mdb); i++) {
514 Dmsg1(800, "list_result looking at field %d\n", i);
515 field = sql_fetch_field(mdb);
519 max_len = max_length(field->max_length);
520 bsnprintf(buf, sizeof(buf), " %-*s |", max_len, field->name);
524 list_dashes(mdb, send, ctx);
527 Dmsg1(800, "list_result starts third loop looking at %d fields\n",
528 sql_num_fields(mdb));
530 sql_field_seek(mdb, 0);
532 for (i = 0; i < sql_num_fields(mdb); i++) {
533 field = sql_fetch_field(mdb);
537 max_len = max_length(field->max_length);
538 if (row[i] == NULL) {
539 bsnprintf(buf, sizeof(buf), " %-*s |", max_len, "NULL");
540 } else if (sql_field_is_numeric(mdb, field->type) && !jcr->gui && is_an_integer(row[i])) {
541 bsnprintf(buf, sizeof(buf), " %*s |", max_len,
542 add_commas(row[i], ewc));
544 bsnprintf(buf, sizeof(buf), " %-*s |", max_len, row[i]);
553 Dmsg1(800, "list_result starts vertical list at %d fields\n", sql_num_fields(mdb));
555 sql_field_seek(mdb, 0);
556 for (i = 0; i < sql_num_fields(mdb); i++) {
557 field = sql_fetch_field(mdb);
561 if (row[i] == NULL) {
562 bsnprintf(buf, sizeof(buf), " %*s: %s\n", max_len, field->name, "NULL");
563 } else if (sql_field_is_numeric(mdb, field->type) && !jcr->gui && is_an_integer(row[i])) {
564 bsnprintf(buf, sizeof(buf), " %*s: %s\n", max_len, field->name,
565 add_commas(row[i], ewc));
567 bsnprintf(buf, sizeof(buf), " %*s: %s\n", max_len, field->name, row[i]);
576 * Open a new connexion to mdb catalog. This function is used
577 * by batch and accurate mode.
579 bool db_open_batch_connexion(JCR *jcr, B_DB *mdb)
583 if (mdb->batch_insert_available())
584 multi_db = true; /* we force a new connection only if batch insert is enabled */
588 if (!jcr->db_batch) {
589 jcr->db_batch = db_clone_database_connection(mdb, jcr, multi_db);
590 if (!jcr->db_batch) {
591 Mmsg0(&mdb->errmsg, _("Could not init database batch connection"));
592 Jmsg(jcr, M_FATAL, 0, "%s", mdb->errmsg);
596 if (!db_open_database(jcr, jcr->db_batch)) {
597 Mmsg2(&mdb->errmsg, _("Could not open database \"%s\": ERR=%s\n"),
598 jcr->db_batch->get_db_name(), db_strerror(jcr->db_batch));
599 Jmsg(jcr, M_FATAL, 0, "%s", mdb->errmsg);
607 * !!! WARNING !!! Use this function only when bacula is stopped.
608 * ie, after a fatal signal and before exiting the program
609 * Print information about a B_DB object.
611 void db_debug_print(JCR *jcr, FILE *fp)
619 fprintf(fp, "B_DB=%p db_name=%s db_user=%s connected=%s\n",
620 mdb, NPRTB(mdb->get_db_name()), NPRTB(mdb->get_db_user()), mdb->is_connected() ? "true" : "false");
621 fprintf(fp, "\tcmd=\"%s\" changes=%i\n", NPRTB(mdb->cmd), mdb->changes);
622 mdb->print_lock_info(fp);
625 #endif /* HAVE_SQLITE3 || HAVE_MYSQL || HAVE_POSTGRESQL || HAVE_INGRES || HAVE_DBI */