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]) {
115 * * specific context passed from db_check_max_connections to db_max_connections_handler.
117 struct max_connections_context {
119 uint32_t nr_connections;
123 * * Called here to retrieve an integer from the database
125 static int db_max_connections_handler(void *ctx, int num_fields, char **row)
127 struct max_connections_context *context;
130 context = (struct max_connections_context *)ctx;
131 switch (db_get_type_index(context->db)) {
139 context->nr_connections = str_to_int64(row[index]);
141 Dmsg0(800, "int_handler finds zero\n");
142 context->nr_connections = 0;
148 * * Check catalog max_connections setting
150 bool db_check_max_connections(JCR *jcr, B_DB *mdb, uint32_t max_concurrent_jobs)
152 struct max_connections_context context;
154 /* Without Batch insert, no need to verify max_connections */
155 if (!mdb->batch_insert_available())
159 context.nr_connections = 0;
161 /* Check max_connections setting */
162 if (!db_sql_query(mdb, sql_get_max_connections[db_get_type_index(mdb)],
163 db_max_connections_handler, &context)) {
164 Jmsg(jcr, M_ERROR, 0, "Can't verify max_connections settings %s", mdb->errmsg);
167 if (context.nr_connections && max_concurrent_jobs && max_concurrent_jobs > context.nr_connections) {
169 _("Potential performance problem:\n"
170 "max_connections=%d set for %s database \"%s\" should be larger than Director's "
171 "MaxConcurrentJobs=%d\n"),
172 context.nr_connections, db_get_type(mdb), mdb->get_db_name(), max_concurrent_jobs);
173 Jmsg(jcr, M_WARNING, 0, "%s", mdb->errmsg);
180 /* NOTE!!! The following routines expect that the
181 * calling subroutine sets and clears the mutex
184 /* Check that the tables correspond to the version we want */
185 bool check_tables_version(JCR *jcr, B_DB *mdb)
187 uint32_t bacula_db_version = 0;
188 const char *query = "SELECT VersionId FROM Version";
190 bacula_db_version = 0;
191 if (!db_sql_query(mdb, query, db_int_handler, (void *)&bacula_db_version)) {
192 Jmsg(jcr, M_FATAL, 0, "%s", mdb->errmsg);
195 if (bacula_db_version != BDB_VERSION) {
196 Mmsg(mdb->errmsg, "Version error for database \"%s\". Wanted %d, got %d\n",
197 mdb->get_db_name(), BDB_VERSION, bacula_db_version);
198 Jmsg(jcr, M_FATAL, 0, "%s", mdb->errmsg);
205 * Utility routine for queries. The database MUST be locked before calling here.
206 * Returns: 0 on failure
210 QueryDB(const char *file, int line, JCR *jcr, B_DB *mdb, char *cmd)
212 sql_free_result(mdb);
213 if (!sql_query(mdb, cmd, QF_STORE_RESULT)) {
214 m_msg(file, line, &mdb->errmsg, _("query %s failed:\n%s\n"), cmd, sql_strerror(mdb));
215 j_msg(file, line, jcr, M_FATAL, 0, "%s", mdb->errmsg);
217 j_msg(file, line, jcr, M_INFO, 0, "%s\n", cmd);
226 * Utility routine to do inserts
227 * Returns: 0 on failure
231 InsertDB(const char *file, int line, JCR *jcr, B_DB *mdb, char *cmd)
235 if (!sql_query(mdb, cmd)) {
236 m_msg(file, line, &mdb->errmsg, _("insert %s failed:\n%s\n"), cmd, sql_strerror(mdb));
237 j_msg(file, line, jcr, M_FATAL, 0, "%s", mdb->errmsg);
239 j_msg(file, line, jcr, M_INFO, 0, "%s\n", cmd);
243 num_rows = sql_affected_rows(mdb);
246 m_msg(file, line, &mdb->errmsg, _("Insertion problem: affected_rows=%s\n"),
247 edit_uint64(num_rows, ed1));
249 j_msg(file, line, jcr, M_INFO, 0, "%s\n", cmd);
257 /* Utility routine for updates.
258 * Returns: 0 on failure
262 UpdateDB(const char *file, int line, JCR *jcr, B_DB *mdb, char *cmd)
266 if (!sql_query(mdb, cmd)) {
267 m_msg(file, line, &mdb->errmsg, _("update %s failed:\n%s\n"), cmd, sql_strerror(mdb));
268 j_msg(file, line, jcr, M_ERROR, 0, "%s", mdb->errmsg);
270 j_msg(file, line, jcr, M_INFO, 0, "%s\n", cmd);
274 num_rows = sql_affected_rows(mdb);
277 m_msg(file, line, &mdb->errmsg, _("Update failed: affected_rows=%s for %s\n"),
278 edit_uint64(num_rows, ed1), cmd);
280 // j_msg(file, line, jcr, M_INFO, 0, "%s\n", cmd);
288 /* Utility routine for deletes
290 * Returns: -1 on error
291 * n number of rows affected
294 DeleteDB(const char *file, int line, JCR *jcr, B_DB *mdb, char *cmd)
297 if (!sql_query(mdb, cmd)) {
298 m_msg(file, line, &mdb->errmsg, _("delete %s failed:\n%s\n"), cmd, sql_strerror(mdb));
299 j_msg(file, line, jcr, M_ERROR, 0, "%s", mdb->errmsg);
301 j_msg(file, line, jcr, M_INFO, 0, "%s\n", cmd);
306 return sql_affected_rows(mdb);
311 * Get record max. Query is already in mdb->cmd
314 * Returns: -1 on failure
317 int get_sql_record_max(JCR *jcr, B_DB *mdb)
322 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
323 if ((row = sql_fetch_row(mdb)) == NULL) {
324 Mmsg1(&mdb->errmsg, _("error fetching row: %s\n"), sql_strerror(mdb));
327 stat = str_to_int64(row[0]);
329 sql_free_result(mdb);
331 Mmsg1(&mdb->errmsg, _("error fetching row: %s\n"), sql_strerror(mdb));
338 * * Return pre-edited error message
340 char *db_strerror(B_DB *mdb)
346 * Given a full filename, split it into its path
347 * and filename parts. They are returned in pool memory
348 * in the mdb structure.
350 void split_path_and_file(JCR *jcr, B_DB *mdb, const char *fname)
354 /* Find path without the filename.
355 * I.e. everything after the last / is a "filename".
356 * OK, maybe it is a directory name, but we treat it like
357 * a filename. If we don't find a / then the whole name
358 * must be a path name (e.g. c:).
360 for (p=f=fname; *p; p++) {
361 if (IsPathSeparator(*p)) {
362 f = p; /* set pos of last slash */
365 if (IsPathSeparator(*f)) { /* did we find a slash? */
366 f++; /* yes, point to filename */
367 } else { /* no, whole thing must be path name */
371 /* If filename doesn't exist (i.e. root directory), we
372 * simply create a blank name consisting of a single
373 * space. This makes handling zero length filenames
378 mdb->fname = check_pool_memory_size(mdb->fname, mdb->fnl+1);
379 memcpy(mdb->fname, f, mdb->fnl); /* copy filename */
380 mdb->fname[mdb->fnl] = 0;
386 mdb->pnl = f - fname;
388 mdb->path = check_pool_memory_size(mdb->path, mdb->pnl+1);
389 memcpy(mdb->path, fname, mdb->pnl);
390 mdb->path[mdb->pnl] = 0;
392 Mmsg1(&mdb->errmsg, _("Path length is zero. File=%s\n"), fname);
393 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
398 Dmsg2(500, "split path=%s file=%s\n", mdb->path, mdb->fname);
402 * Set maximum field length to something reasonable
404 static int max_length(int max_length)
406 int max_len = max_length;
410 } else if (max_len > 100) {
417 * List dashes as part of header for listing SQL results in a table
420 list_dashes(B_DB *mdb, DB_LIST_HANDLER *send, void *ctx)
426 sql_field_seek(mdb, 0);
428 for (i = 0; i < sql_num_fields(mdb); i++) {
429 field = sql_fetch_field(mdb);
433 len = max_length(field->max_length + 2);
434 for (j = 0; j < len; j++) {
442 /* Small handler to print the last line of a list xxx command */
443 static void last_line_handler(void *vctx, const char *str)
445 LIST_CTX *ctx = (LIST_CTX *)vctx;
446 bstrncat(ctx->line, str, sizeof(ctx->line));
449 int list_result(void *vctx, int nb_col, char **row)
452 int i, col_len, max_len = 0;
453 char buf[2000], ewc[30];
455 LIST_CTX *pctx = (LIST_CTX *)vctx;
456 DB_LIST_HANDLER *send = pctx->send;
457 e_list_type type = pctx->type;
458 B_DB *mdb = pctx->mdb;
459 void *ctx = pctx->ctx;
460 JCR *jcr = pctx->jcr;
465 Dmsg1(800, "list_result starts looking at %d fields\n", sql_num_fields(mdb));
466 /* determine column display widths */
467 sql_field_seek(mdb, 0);
468 for (i = 0; i < sql_num_fields(mdb); i++) {
469 Dmsg1(800, "list_result processing field %d\n", i);
470 field = sql_fetch_field(mdb);
474 col_len = cstrlen(field->name);
475 if (type == VERT_LIST) {
476 if (col_len > max_len) {
480 if (sql_field_is_numeric(mdb, field->type) && (int)field->max_length > 0) { /* fixup for commas */
481 field->max_length += (field->max_length - 1) / 3;
483 if (col_len < (int)field->max_length) {
484 col_len = field->max_length;
486 if (col_len < 4 && !sql_field_is_not_null(mdb, field->flags)) {
487 col_len = 4; /* 4 = length of the word "NULL" */
489 field->max_length = col_len; /* reset column info */
495 Dmsg0(800, "list_result finished first loop\n");
496 if (type == VERT_LIST) {
500 Dmsg1(800, "list_result starts second loop looking at %d fields\n",
501 sql_num_fields(mdb));
503 /* Keep the result to display the same line at the end of the table */
504 list_dashes(mdb, last_line_handler, pctx);
505 send(ctx, pctx->line);
508 sql_field_seek(mdb, 0);
509 for (i = 0; i < sql_num_fields(mdb); i++) {
510 Dmsg1(800, "list_result looking at field %d\n", i);
511 field = sql_fetch_field(mdb);
515 max_len = max_length(field->max_length);
516 bsnprintf(buf, sizeof(buf), " %-*s |", max_len, field->name);
520 list_dashes(mdb, send, ctx);
523 Dmsg1(800, "list_result starts third loop looking at %d fields\n",
524 sql_num_fields(mdb));
526 sql_field_seek(mdb, 0);
528 for (i = 0; i < sql_num_fields(mdb); i++) {
529 field = sql_fetch_field(mdb);
533 max_len = max_length(field->max_length);
534 if (row[i] == NULL) {
535 bsnprintf(buf, sizeof(buf), " %-*s |", max_len, "NULL");
536 } else if (sql_field_is_numeric(mdb, field->type) && !jcr->gui && is_an_integer(row[i])) {
537 bsnprintf(buf, sizeof(buf), " %*s |", max_len,
538 add_commas(row[i], ewc));
540 bsnprintf(buf, sizeof(buf), " %-*s |", max_len, row[i]);
549 Dmsg1(800, "list_result starts vertical list at %d fields\n", sql_num_fields(mdb));
551 sql_field_seek(mdb, 0);
552 for (i = 0; i < sql_num_fields(mdb); i++) {
553 field = sql_fetch_field(mdb);
557 if (row[i] == NULL) {
558 bsnprintf(buf, sizeof(buf), " %*s: %s\n", max_len, field->name, "NULL");
559 } else if (sql_field_is_numeric(mdb, field->type) && !jcr->gui && is_an_integer(row[i])) {
560 bsnprintf(buf, sizeof(buf), " %*s: %s\n", max_len, field->name,
561 add_commas(row[i], ewc));
563 bsnprintf(buf, sizeof(buf), " %*s: %s\n", max_len, field->name, row[i]);
572 * Open a new connexion to mdb catalog. This function is used
573 * by batch and accurate mode.
575 bool db_open_batch_connexion(JCR *jcr, B_DB *mdb)
579 if (mdb->batch_insert_available())
580 multi_db = true; /* we force a new connection only if batch insert is enabled */
584 if (!jcr->db_batch) {
585 jcr->db_batch = db_clone_database_connection(mdb, jcr, multi_db);
586 if (!jcr->db_batch) {
587 Mmsg0(&mdb->errmsg, _("Could not init database batch connection"));
588 Jmsg(jcr, M_FATAL, 0, "%s", mdb->errmsg);
592 if (!db_open_database(jcr, jcr->db_batch)) {
593 Mmsg2(&mdb->errmsg, _("Could not open database \"%s\": ERR=%s\n"),
594 jcr->db_batch->get_db_name(), db_strerror(jcr->db_batch));
595 Jmsg(jcr, M_FATAL, 0, "%s", mdb->errmsg);
603 * !!! WARNING !!! Use this function only when bacula is stopped.
604 * ie, after a fatal signal and before exiting the program
605 * Print information about a B_DB object.
607 void db_debug_print(JCR *jcr, FILE *fp)
615 fprintf(fp, "B_DB=%p db_name=%s db_user=%s connected=%s\n",
616 mdb, NPRTB(mdb->get_db_name()), NPRTB(mdb->get_db_user()), mdb->is_connected() ? "true" : "false");
617 fprintf(fp, "\tcmd=\"%s\" changes=%i\n", NPRTB(mdb->cmd), mdb->changes);
618 mdb->print_lock_info(fp);
621 #endif /* HAVE_SQLITE3 || HAVE_MYSQL || HAVE_POSTGRESQL || HAVE_INGRES || HAVE_DBI */