2 Bacula® - The Network Backup Solution
4 Copyright (C) 2000-2008 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 two of the GNU 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 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
41 /* The following is necessary so that we do not include
42 * the dummy external definition of B_DB.
44 #define __SQL_C /* indicate that this is sql.c */
49 #if HAVE_SQLITE3 || HAVE_MYSQL || HAVE_SQLITE || HAVE_POSTGRESQL || HAVE_DBI
51 uint32_t bacula_db_version = 0;
53 int db_type = -1; /* SQL engine type index */
55 /* Forward referenced subroutines */
56 void print_dashes(B_DB *mdb);
57 void print_result(B_DB *mdb);
59 B_DB *db_init(JCR *jcr, const char *db_driver, const char *db_name, const char *db_user,
60 const char *db_password, const char *db_address, int db_port,
61 const char *db_socket, int mult_db_connections)
66 Jmsg0(jcr, M_ABORT, 0, _("Driver type not specified in Catalog resource.\n"));
68 if (strlen(db_driver) < 5 || db_driver[3] != ':' || strncasecmp(db_driver, "dbi", 3) != 0) {
69 Jmsg0(jcr, M_ABORT, 0, _("Invalid driver type, must be \"dbi:<type>\"\n"));
71 p = (char *)(db_driver + 4);
72 if (strcasecmp(p, "mysql") == 0) {
73 db_type = SQL_TYPE_MYSQL;
74 } else if (strcasecmp(p, "postgresql") == 0) {
75 db_type = SQL_TYPE_POSTGRESQL;
76 } else if (strcasecmp(p, "sqlite") == 0) {
77 db_type = SQL_TYPE_SQLITE;
78 } else if (strcasecmp(p, "sqlite3") == 0) {
79 db_type = SQL_TYPE_SQLITE3;
81 Jmsg1(jcr, M_ABORT, 0, _("Unknown database type: %s\n"), p);
84 db_type = SQL_TYPE_MYSQL;
86 db_type = SQL_TYPE_POSTGRESQL;
88 db_type = SQL_TYPE_SQLITE;
90 db_type = SQL_TYPE_SQLITE3;
93 return db_init_database(jcr, db_name, db_user, db_password, db_address,
94 db_port, db_socket, mult_db_connections);
97 dbid_list::dbid_list()
99 memset(this, 0, sizeof(dbid_list));
101 DBId = (DBId_t *)malloc(max_ids * sizeof(DBId_t));
102 num_ids = num_seen = tot_ids = 0;
106 dbid_list::~dbid_list()
113 * Called here to retrieve an integer from the database
115 static int int_handler(void *ctx, int num_fields, char **row)
117 uint32_t *val = (uint32_t *)ctx;
119 Dmsg1(800, "int_handler starts with row pointing at %x\n", row);
122 Dmsg1(800, "int_handler finds '%s'\n", row[0]);
123 *val = str_to_int64(row[0]);
125 Dmsg0(800, "int_handler finds zero\n");
128 Dmsg0(800, "int_handler finishes\n");
133 * Called here to retrieve a 32/64 bit integer from the database.
134 * The returned integer will be extended to 64 bit.
136 int db_int64_handler(void *ctx, int num_fields, char **row)
138 db_int64_ctx *lctx = (db_int64_ctx *)ctx;
141 lctx->value = str_to_int64(row[0]);
149 /* NOTE!!! The following routines expect that the
150 * calling subroutine sets and clears the mutex
153 /* Check that the tables correspond to the version we want */
154 bool check_tables_version(JCR *jcr, B_DB *mdb)
156 const char *query = "SELECT VersionId FROM Version";
158 bacula_db_version = 0;
159 if (!db_sql_query(mdb, query, int_handler, (void *)&bacula_db_version)) {
160 Jmsg(jcr, M_FATAL, 0, "%s", mdb->errmsg);
163 if (bacula_db_version != BDB_VERSION) {
164 Mmsg(mdb->errmsg, "Version error for database \"%s\". Wanted %d, got %d\n",
165 mdb->db_name, BDB_VERSION, bacula_db_version);
166 Jmsg(jcr, M_FATAL, 0, "%s", mdb->errmsg);
172 /* Utility routine for queries. The database MUST be locked before calling here. */
174 QueryDB(const char *file, int line, JCR *jcr, B_DB *mdb, char *cmd)
178 sql_free_result(mdb);
179 if ((status=sql_query(mdb, cmd)) != 0) {
180 m_msg(file, line, &mdb->errmsg, _("query %s failed:\n%s\n"), cmd, sql_strerror(mdb));
181 j_msg(file, line, jcr, M_FATAL, 0, "%s", mdb->errmsg);
183 j_msg(file, line, jcr, M_INFO, 0, "%s\n", cmd);
188 mdb->result = sql_store_result(mdb);
190 return mdb->result != NULL;
194 * Utility routine to do inserts
195 * Returns: 0 on failure
199 InsertDB(const char *file, int line, JCR *jcr, B_DB *mdb, char *cmd)
201 if (sql_query(mdb, cmd)) {
202 m_msg(file, line, &mdb->errmsg, _("insert %s failed:\n%s\n"), cmd, sql_strerror(mdb));
203 j_msg(file, line, jcr, M_FATAL, 0, "%s", mdb->errmsg);
205 j_msg(file, line, jcr, M_INFO, 0, "%s\n", cmd);
209 if (mdb->have_insert_id) {
210 mdb->num_rows = sql_affected_rows(mdb);
214 if (mdb->num_rows != 1) {
216 m_msg(file, line, &mdb->errmsg, _("Insertion problem: affected_rows=%s\n"),
217 edit_uint64(mdb->num_rows, ed1));
219 j_msg(file, line, jcr, M_INFO, 0, "%s\n", cmd);
227 /* Utility routine for updates.
228 * Returns: 0 on failure
232 UpdateDB(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, _("update %s failed:\n%s\n"), cmd, sql_strerror(mdb));
237 j_msg(file, line, jcr, M_ERROR, 0, "%s", mdb->errmsg);
239 j_msg(file, line, jcr, M_INFO, 0, "%s\n", cmd);
243 mdb->num_rows = sql_affected_rows(mdb);
244 if (mdb->num_rows < 1) {
246 m_msg(file, line, &mdb->errmsg, _("Update failed: affected_rows=%s for %s\n"),
247 edit_uint64(mdb->num_rows, ed1), cmd);
249 // j_msg(file, line, jcr, M_INFO, 0, "%s\n", cmd);
257 /* Utility routine for deletes
259 * Returns: -1 on error
260 * n number of rows affected
263 DeleteDB(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, _("delete %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);
275 return sql_affected_rows(mdb);
280 * Get record max. Query is already in mdb->cmd
283 * Returns: -1 on failure
286 int get_sql_record_max(JCR *jcr, B_DB *mdb)
291 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
292 if ((row = sql_fetch_row(mdb)) == NULL) {
293 Mmsg1(&mdb->errmsg, _("error fetching row: %s\n"), sql_strerror(mdb));
296 stat = str_to_int64(row[0]);
298 sql_free_result(mdb);
300 Mmsg1(&mdb->errmsg, _("error fetching row: %s\n"), sql_strerror(mdb));
307 * Return pre-edited error message
309 char *db_strerror(B_DB *mdb)
315 * Lock database, this can be called multiple times by the same
316 * thread without blocking, but must be unlocked the number of
317 * times it was locked.
319 void _db_lock(const char *file, int line, B_DB *mdb)
322 if ((errstat=rwl_writelock(&mdb->lock)) != 0) {
324 e_msg(file, line, M_FATAL, 0, "rwl_writelock failure. stat=%d: ERR=%s\n",
325 errstat, be.bstrerror(errstat));
330 * Unlock the database. This can be called multiple times by the
331 * same thread up to the number of times that thread called
334 void _db_unlock(const char *file, int line, B_DB *mdb)
337 if ((errstat=rwl_writeunlock(&mdb->lock)) != 0) {
339 e_msg(file, line, M_FATAL, 0, "rwl_writeunlock failure. stat=%d: ERR=%s\n",
340 errstat, be.bstrerror(errstat));
345 * Start a transaction. This groups inserts and makes things
346 * much more efficient. Usually started when inserting
349 void db_start_transaction(JCR *jcr, B_DB *mdb)
352 jcr->attr = get_pool_memory(PM_FNAME);
355 jcr->ar = (ATTR_DBR *)malloc(sizeof(ATTR_DBR));
359 if (!mdb->allow_transactions) {
363 /* Allow only 10,000 changes per transaction */
364 if (mdb->transaction && mdb->changes > 10000) {
365 db_end_transaction(jcr, mdb);
367 if (!mdb->transaction) {
368 my_sqlite_query(mdb, "BEGIN"); /* begin transaction */
369 Dmsg0(400, "Start SQLite transaction\n");
370 mdb->transaction = 1;
376 * This is turned off because transactions break
377 * if multiple simultaneous jobs are run.
379 #ifdef HAVE_POSTGRESQL
380 if (!mdb->allow_transactions) {
384 /* Allow only 25,000 changes per transaction */
385 if (mdb->transaction && mdb->changes > 25000) {
386 db_end_transaction(jcr, mdb);
388 if (!mdb->transaction) {
389 db_sql_query(mdb, "BEGIN", NULL, NULL); /* begin transaction */
390 Dmsg0(400, "Start PosgreSQL transaction\n");
391 mdb->transaction = 1;
397 if (db_type == SQL_TYPE_SQLITE) {
398 if (!mdb->allow_transactions) {
402 /* Allow only 10,000 changes per transaction */
403 if (mdb->transaction && mdb->changes > 10000) {
404 db_end_transaction(jcr, mdb);
406 if (!mdb->transaction) {
407 //my_sqlite_query(mdb, "BEGIN"); /* begin transaction */
408 db_sql_query(mdb, "BEGIN", NULL, NULL); /* begin transaction */
409 Dmsg0(400, "Start SQLite transaction\n");
410 mdb->transaction = 1;
413 } else if (db_type == SQL_TYPE_POSTGRESQL) {
414 if (!mdb->allow_transactions) {
418 /* Allow only 25,000 changes per transaction */
419 if (mdb->transaction && mdb->changes > 25000) {
420 db_end_transaction(jcr, mdb);
422 if (!mdb->transaction) {
423 db_sql_query(mdb, "BEGIN", NULL, NULL); /* begin transaction */
424 Dmsg0(400, "Start PosgreSQL transaction\n");
425 mdb->transaction = 1;
432 void db_end_transaction(JCR *jcr, B_DB *mdb)
435 * This can be called during thread cleanup and
436 * the db may already be closed. So simply return.
442 if (jcr && jcr->cached_attribute) {
443 Dmsg0(400, "Flush last cached attribute.\n");
444 if (!db_create_file_attributes_record(jcr, mdb, jcr->ar)) {
445 Jmsg1(jcr, M_FATAL, 0, _("Attribute create error. %s"), db_strerror(jcr->db));
447 jcr->cached_attribute = false;
451 if (!mdb->allow_transactions) {
455 if (mdb->transaction) {
456 my_sqlite_query(mdb, "COMMIT"); /* end transaction */
457 mdb->transaction = 0;
458 Dmsg1(400, "End SQLite transaction changes=%d\n", mdb->changes);
464 #ifdef HAVE_POSTGRESQL
465 if (!mdb->allow_transactions) {
469 if (mdb->transaction) {
470 db_sql_query(mdb, "COMMIT", NULL, NULL); /* end transaction */
471 mdb->transaction = 0;
472 Dmsg1(400, "End PostgreSQL transaction changes=%d\n", mdb->changes);
479 if (db_type == SQL_TYPE_SQLITE) {
480 if (!mdb->allow_transactions) {
484 if (mdb->transaction) {
485 //my_sqlite_query(mdb, "COMMIT"); /* end transaction */
486 db_sql_query(mdb, "COMMIT", NULL, NULL); /* end transaction */
487 mdb->transaction = 0;
488 Dmsg1(400, "End SQLite transaction changes=%d\n", mdb->changes);
492 } else if (db_type == SQL_TYPE_POSTGRESQL) {
493 if (!mdb->allow_transactions) {
497 if (mdb->transaction) {
498 db_sql_query(mdb, "COMMIT", NULL, NULL); /* end transaction */
499 mdb->transaction = 0;
500 Dmsg1(400, "End PostgreSQL transaction changes=%d\n", mdb->changes);
509 * Given a full filename, split it into its path
510 * and filename parts. They are returned in pool memory
511 * in the mdb structure.
513 void split_path_and_file(JCR *jcr, B_DB *mdb, const char *fname)
517 /* Find path without the filename.
518 * I.e. everything after the last / is a "filename".
519 * OK, maybe it is a directory name, but we treat it like
520 * a filename. If we don't find a / then the whole name
521 * must be a path name (e.g. c:).
523 for (p=f=fname; *p; p++) {
524 if (IsPathSeparator(*p)) {
525 f = p; /* set pos of last slash */
528 if (IsPathSeparator(*f)) { /* did we find a slash? */
529 f++; /* yes, point to filename */
530 } else { /* no, whole thing must be path name */
534 /* If filename doesn't exist (i.e. root directory), we
535 * simply create a blank name consisting of a single
536 * space. This makes handling zero length filenames
541 mdb->fname = check_pool_memory_size(mdb->fname, mdb->fnl+1);
542 memcpy(mdb->fname, f, mdb->fnl); /* copy filename */
543 mdb->fname[mdb->fnl] = 0;
549 mdb->pnl = f - fname;
551 mdb->path = check_pool_memory_size(mdb->path, mdb->pnl+1);
552 memcpy(mdb->path, fname, mdb->pnl);
553 mdb->path[mdb->pnl] = 0;
555 Mmsg1(&mdb->errmsg, _("Path length is zero. File=%s\n"), fname);
556 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
561 Dmsg2(500, "split path=%s file=%s\n", mdb->path, mdb->fname);
565 * List dashes as part of header for listing SQL results in a table
568 list_dashes(B_DB *mdb, DB_LIST_HANDLER *send, void *ctx)
573 sql_field_seek(mdb, 0);
575 for (i = 0; i < sql_num_fields(mdb); i++) {
576 field = sql_fetch_field(mdb);
580 for (j = 0; j < (int)field->max_length + 2; j++) {
589 * If full_list is set, we list vertically, otherwise, we
590 * list on one line horizontally.
593 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;
598 char buf[2000], ewc[30];
600 Dmsg0(800, "list_result starts\n");
601 if (mdb->result == NULL || sql_num_rows(mdb) == 0) {
602 send(ctx, _("No results to list.\n"));
606 Dmsg1(800, "list_result starts looking at %d fields\n", sql_num_fields(mdb));
607 /* determine column display widths */
608 sql_field_seek(mdb, 0);
609 for (i = 0; i < sql_num_fields(mdb); 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 (IS_NUM(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 && !IS_NOT_NULL(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", sql_num_fields(mdb));
640 list_dashes(mdb, send, ctx);
642 sql_field_seek(mdb, 0);
643 for (i = 0; i < sql_num_fields(mdb); i++) {
644 Dmsg1(800, "list_result looking at field %d\n", i);
645 field = sql_fetch_field(mdb);
649 bsnprintf(buf, sizeof(buf), " %-*s |", (int)field->max_length, field->name);
653 list_dashes(mdb, send, ctx);
655 Dmsg1(800, "list_result starts third loop looking at %d fields\n", sql_num_fields(mdb));
656 while ((row = sql_fetch_row(mdb)) != NULL) {
657 sql_field_seek(mdb, 0);
659 for (i = 0; i < sql_num_fields(mdb); i++) {
660 field = sql_fetch_field(mdb);
664 if (row[i] == NULL) {
665 bsnprintf(buf, sizeof(buf), " %-*s |", (int)field->max_length, "NULL");
666 } else if (IS_NUM(field->type) && !jcr->gui && is_an_integer(row[i])) {
667 bsnprintf(buf, sizeof(buf), " %*s |", (int)field->max_length,
668 add_commas(row[i], ewc));
670 bsnprintf(buf, sizeof(buf), " %-*s |", (int)field->max_length, row[i]);
676 list_dashes(mdb, send, ctx);
681 Dmsg1(800, "list_result starts vertical list at %d fields\n", sql_num_fields(mdb));
682 while ((row = sql_fetch_row(mdb)) != NULL) {
683 sql_field_seek(mdb, 0);
684 for (i = 0; i < sql_num_fields(mdb); i++) {
685 field = sql_fetch_field(mdb);
689 if (row[i] == NULL) {
690 bsnprintf(buf, sizeof(buf), " %*s: %s\n", max_len, field->name, "NULL");
691 } else if (IS_NUM(field->type) && !jcr->gui && is_an_integer(row[i])) {
692 bsnprintf(buf, sizeof(buf), " %*s: %s\n", max_len, field->name,
693 add_commas(row[i], ewc));
695 bsnprintf(buf, sizeof(buf), " %*s: %s\n", max_len, field->name, row[i]);
705 * Open a new connexion to mdb catalog. This function is used
706 * by batch and accurate mode.
708 bool db_open_batch_connexion(JCR *jcr, B_DB *mdb)
712 #ifdef HAVE_BATCH_FILE_INSERT
713 multi_db=true; /* we force a new connexion only if batch insert is enabled */
716 if (!jcr->db_batch) {
717 jcr->db_batch = db_init_database(jcr,
724 multi_db /* multi_db = true when using batch mode */);
725 if (!jcr->db_batch) {
726 Jmsg0(jcr, M_FATAL, 0, "Could not init batch connexion");
730 if (!db_open_database(jcr, jcr->db_batch)) {
731 Mmsg2(&jcr->db_batch->errmsg, _("Could not open database \"%s\": ERR=%s\n"),
732 jcr->db_batch->db_name, db_strerror(jcr->db_batch));
733 Jmsg1(jcr, M_FATAL, 0, "%s", jcr->db_batch->errmsg);
736 Dmsg3(100, "initdb ref=%d connected=%d db=%p\n", jcr->db_batch->ref_count,
737 jcr->db_batch->connected, jcr->db_batch->db);
744 * !!! WARNING !!! Use this function only when bacula is stopped.
745 * ie, after a fatal signal and before exiting the program
746 * Print information about a B_DB object.
748 void _dbg_print_db(JCR *jcr, FILE *fp)
756 fprintf(fp, "B_DB=%p db_name=%s db_user=%s connected=%i\n",
757 mdb, NPRTB(mdb->db_name), NPRTB(mdb->db_user), mdb->connected);
758 fprintf(fp, "\tcmd=\"%s\" changes=%i\n", NPRTB(mdb->cmd), mdb->changes);
759 if (mdb->lock.valid == RWLOCK_VALID) {
760 fprintf(fp, "\tRWLOCK=%p w_active=%i w_wait=%i\n", &mdb->lock, mdb->lock.w_active, mdb->lock.w_wait);
764 #endif /* HAVE_SQLITE3 || HAVE_MYSQL || HAVE_SQLITE || HAVE_POSTGRESQL*/