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++) {
447 * If full_list is set, we list vertically, otherwise, we
448 * list on one line horizontally.
451 list_result(JCR *jcr, B_DB *mdb, DB_LIST_HANDLER *send, void *ctx, e_list_type type)
455 int i, col_len, max_len = 0;
456 char buf[2000], ewc[30];
458 Dmsg0(800, "list_result starts\n");
459 if (sql_num_rows(mdb) == 0) {
460 send(ctx, _("No results to list.\n"));
464 Dmsg1(800, "list_result starts looking at %d fields\n", sql_num_fields(mdb));
465 /* determine column display widths */
466 sql_field_seek(mdb, 0);
467 for (i = 0; i < sql_num_fields(mdb); i++) {
468 Dmsg1(800, "list_result processing field %d\n", i);
469 field = sql_fetch_field(mdb);
473 col_len = cstrlen(field->name);
474 if (type == VERT_LIST) {
475 if (col_len > max_len) {
479 if (sql_field_is_numeric(mdb, field->type) && (int)field->max_length > 0) { /* fixup for commas */
480 field->max_length += (field->max_length - 1) / 3;
482 if (col_len < (int)field->max_length) {
483 col_len = field->max_length;
485 if (col_len < 4 && !sql_field_is_not_null(mdb, field->flags)) {
486 col_len = 4; /* 4 = length of the word "NULL" */
488 field->max_length = col_len; /* reset column info */
492 Dmsg0(800, "list_result finished first loop\n");
493 if (type == VERT_LIST) {
497 Dmsg1(800, "list_result starts second loop looking at %d fields\n", sql_num_fields(mdb));
498 list_dashes(mdb, send, ctx);
500 sql_field_seek(mdb, 0);
501 for (i = 0; i < sql_num_fields(mdb); i++) {
502 Dmsg1(800, "list_result looking at field %d\n", i);
503 field = sql_fetch_field(mdb);
507 max_len = max_length(field->max_length);
508 bsnprintf(buf, sizeof(buf), " %-*s |", max_len, field->name);
512 list_dashes(mdb, send, ctx);
514 Dmsg1(800, "list_result starts third loop looking at %d fields\n", sql_num_fields(mdb));
515 while ((row = sql_fetch_row(mdb)) != NULL) {
516 sql_field_seek(mdb, 0);
518 for (i = 0; i < sql_num_fields(mdb); i++) {
519 field = sql_fetch_field(mdb);
523 max_len = max_length(field->max_length);
524 if (row[i] == NULL) {
525 bsnprintf(buf, sizeof(buf), " %-*s |", max_len, "NULL");
526 } else if (sql_field_is_numeric(mdb, field->type) && !jcr->gui && is_an_integer(row[i])) {
527 bsnprintf(buf, sizeof(buf), " %*s |", max_len,
528 add_commas(row[i], ewc));
530 bsnprintf(buf, sizeof(buf), " %-*s |", max_len, row[i]);
536 list_dashes(mdb, send, ctx);
541 Dmsg1(800, "list_result starts vertical list at %d fields\n", sql_num_fields(mdb));
542 while ((row = sql_fetch_row(mdb)) != NULL) {
543 sql_field_seek(mdb, 0);
544 for (i = 0; i < sql_num_fields(mdb); i++) {
545 field = sql_fetch_field(mdb);
549 if (row[i] == NULL) {
550 bsnprintf(buf, sizeof(buf), " %*s: %s\n", max_len, field->name, "NULL");
551 } else if (sql_field_is_numeric(mdb, field->type) && !jcr->gui && is_an_integer(row[i])) {
552 bsnprintf(buf, sizeof(buf), " %*s: %s\n", max_len, field->name,
553 add_commas(row[i], ewc));
555 bsnprintf(buf, sizeof(buf), " %*s: %s\n", max_len, field->name, row[i]);
565 * Open a new connexion to mdb catalog. This function is used
566 * by batch and accurate mode.
568 bool db_open_batch_connexion(JCR *jcr, B_DB *mdb)
572 if (mdb->batch_insert_available())
573 multi_db = true; /* we force a new connection only if batch insert is enabled */
577 if (!jcr->db_batch) {
578 jcr->db_batch = db_clone_database_connection(mdb, jcr, multi_db);
579 if (!jcr->db_batch) {
580 Mmsg0(&mdb->errmsg, _("Could not init database batch connection"));
581 Jmsg(jcr, M_FATAL, 0, "%s", mdb->errmsg);
585 if (!db_open_database(jcr, jcr->db_batch)) {
586 Mmsg2(&mdb->errmsg, _("Could not open database \"%s\": ERR=%s\n"),
587 jcr->db_batch->get_db_name(), db_strerror(jcr->db_batch));
588 Jmsg(jcr, M_FATAL, 0, "%s", mdb->errmsg);
596 * !!! WARNING !!! Use this function only when bacula is stopped.
597 * ie, after a fatal signal and before exiting the program
598 * Print information about a B_DB object.
600 void db_debug_print(JCR *jcr, FILE *fp)
608 fprintf(fp, "B_DB=%p db_name=%s db_user=%s connected=%s\n",
609 mdb, NPRTB(mdb->get_db_name()), NPRTB(mdb->get_db_user()), mdb->is_connected() ? "true" : "false");
610 fprintf(fp, "\tcmd=\"%s\" changes=%i\n", NPRTB(mdb->cmd), mdb->changes);
611 mdb->print_lock_info(fp);
614 #endif /* HAVE_SQLITE3 || HAVE_MYSQL || HAVE_POSTGRESQL || HAVE_INGRES || HAVE_DBI */