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 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()
112 * Called here to retrieve an integer from the database
114 int db_int_handler(void *ctx, int num_fields, char **row)
116 uint32_t *val = (uint32_t *)ctx;
118 Dmsg1(800, "int_handler starts with row pointing at %x\n", row);
121 Dmsg1(800, "int_handler finds '%s'\n", row[0]);
122 *val = str_to_int64(row[0]);
124 Dmsg0(800, "int_handler finds zero\n");
127 Dmsg0(800, "int_handler finishes\n");
132 * Called here to retrieve a 32/64 bit integer from the database.
133 * The returned integer will be extended to 64 bit.
135 int db_int64_handler(void *ctx, int num_fields, char **row)
137 db_int64_ctx *lctx = (db_int64_ctx *)ctx;
140 lctx->value = str_to_int64(row[0]);
147 * Use to build a comma separated list of values from a query. "10,20,30"
149 int db_list_handler(void *ctx, int num_fields, char **row)
151 db_list_ctx *lctx = (db_list_ctx *)ctx;
152 if (num_fields == 1 && row[0]) {
154 pm_strcat(lctx->list, ",");
156 pm_strcat(lctx->list, row[0]);
164 * Called here to retrieve an integer from the database
166 static int db_max_connections_handler(void *ctx, int num_fields, char **row)
168 uint32_t *val = (uint32_t *)ctx;
169 uint32_t index = sql_get_max_connections_index[db_type];
171 *val = str_to_int64(row[index]);
173 Dmsg0(800, "int_handler finds zero\n");
180 * Check catalog max_connections setting
182 bool db_check_max_connections(JCR *jcr, B_DB *mdb, uint32_t max_concurrent_jobs)
187 /* Without Batch insert, no need to verify max_connections */
188 #ifndef HAVE_BATCH_FILE_INSERT
192 /* Check max_connections setting */
193 if (!db_sql_query(mdb, sql_get_max_connections[db_type],
194 db_max_connections_handler, &max_conn)) {
195 Jmsg(jcr, M_ERROR, 0, "Can't verify max_connections settings %s", mdb->errmsg);
198 if (max_conn && max_concurrent_jobs && max_concurrent_jobs > max_conn) {
200 _("On db_name=%s, %s max_connections=%d is lower than Director "
201 "MaxConcurentJobs=%d\n"),
202 mdb->db_name, db_get_type(), max_conn, max_concurrent_jobs);
203 Jmsg(jcr, M_WARNING, 0, "%s", mdb->errmsg);
210 /* NOTE!!! The following routines expect that the
211 * calling subroutine sets and clears the mutex
214 /* Check that the tables correspond to the version we want */
215 bool check_tables_version(JCR *jcr, B_DB *mdb)
217 const char *query = "SELECT VersionId FROM Version";
219 bacula_db_version = 0;
220 if (!db_sql_query(mdb, query, db_int_handler, (void *)&bacula_db_version)) {
221 Jmsg(jcr, M_FATAL, 0, "%s", mdb->errmsg);
224 if (bacula_db_version != BDB_VERSION) {
225 Mmsg(mdb->errmsg, "Version error for database \"%s\". Wanted %d, got %d\n",
226 mdb->db_name, BDB_VERSION, bacula_db_version);
227 Jmsg(jcr, M_FATAL, 0, "%s", mdb->errmsg);
233 /* Utility routine for queries. The database MUST be locked before calling here. */
235 QueryDB(const char *file, int line, JCR *jcr, B_DB *mdb, char *cmd)
239 sql_free_result(mdb);
240 if ((status=sql_query(mdb, cmd)) != 0) {
241 m_msg(file, line, &mdb->errmsg, _("query %s failed:\n%s\n"), cmd, sql_strerror(mdb));
242 j_msg(file, line, jcr, M_FATAL, 0, "%s", mdb->errmsg);
244 j_msg(file, line, jcr, M_INFO, 0, "%s\n", cmd);
249 mdb->result = sql_store_result(mdb);
251 return mdb->result != NULL;
255 * Utility routine to do inserts
256 * Returns: 0 on failure
260 InsertDB(const char *file, int line, JCR *jcr, B_DB *mdb, char *cmd)
262 if (sql_query(mdb, cmd)) {
263 m_msg(file, line, &mdb->errmsg, _("insert %s failed:\n%s\n"), cmd, sql_strerror(mdb));
264 j_msg(file, line, jcr, M_FATAL, 0, "%s", mdb->errmsg);
266 j_msg(file, line, jcr, M_INFO, 0, "%s\n", cmd);
270 if (mdb->have_insert_id) {
271 mdb->num_rows = sql_affected_rows(mdb);
275 if (mdb->num_rows != 1) {
277 m_msg(file, line, &mdb->errmsg, _("Insertion problem: affected_rows=%s\n"),
278 edit_uint64(mdb->num_rows, ed1));
280 j_msg(file, line, jcr, M_INFO, 0, "%s\n", cmd);
288 /* Utility routine for updates.
289 * Returns: 0 on failure
293 UpdateDB(const char *file, int line, JCR *jcr, B_DB *mdb, char *cmd)
296 if (sql_query(mdb, cmd)) {
297 m_msg(file, line, &mdb->errmsg, _("update %s failed:\n%s\n"), cmd, sql_strerror(mdb));
298 j_msg(file, line, jcr, M_ERROR, 0, "%s", mdb->errmsg);
300 j_msg(file, line, jcr, M_INFO, 0, "%s\n", cmd);
304 mdb->num_rows = sql_affected_rows(mdb);
305 if (mdb->num_rows < 1) {
307 m_msg(file, line, &mdb->errmsg, _("Update failed: affected_rows=%s for %s\n"),
308 edit_uint64(mdb->num_rows, ed1), cmd);
310 // j_msg(file, line, jcr, M_INFO, 0, "%s\n", cmd);
318 /* Utility routine for deletes
320 * Returns: -1 on error
321 * n number of rows affected
324 DeleteDB(const char *file, int line, JCR *jcr, B_DB *mdb, char *cmd)
327 if (sql_query(mdb, cmd)) {
328 m_msg(file, line, &mdb->errmsg, _("delete %s failed:\n%s\n"), cmd, sql_strerror(mdb));
329 j_msg(file, line, jcr, M_ERROR, 0, "%s", mdb->errmsg);
331 j_msg(file, line, jcr, M_INFO, 0, "%s\n", cmd);
336 return sql_affected_rows(mdb);
341 * Get record max. Query is already in mdb->cmd
344 * Returns: -1 on failure
347 int get_sql_record_max(JCR *jcr, B_DB *mdb)
352 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
353 if ((row = sql_fetch_row(mdb)) == NULL) {
354 Mmsg1(&mdb->errmsg, _("error fetching row: %s\n"), sql_strerror(mdb));
357 stat = str_to_int64(row[0]);
359 sql_free_result(mdb);
361 Mmsg1(&mdb->errmsg, _("error fetching row: %s\n"), sql_strerror(mdb));
368 * Return pre-edited error message
370 char *db_strerror(B_DB *mdb)
376 * Lock database, this can be called multiple times by the same
377 * thread without blocking, but must be unlocked the number of
378 * times it was locked.
380 void _db_lock(const char *file, int line, B_DB *mdb)
383 if ((errstat=rwl_writelock(&mdb->lock)) != 0) {
385 e_msg(file, line, M_FATAL, 0, "rwl_writelock failure. stat=%d: ERR=%s\n",
386 errstat, be.bstrerror(errstat));
391 * Unlock the database. This can be called multiple times by the
392 * same thread up to the number of times that thread called
395 void _db_unlock(const char *file, int line, B_DB *mdb)
398 if ((errstat=rwl_writeunlock(&mdb->lock)) != 0) {
400 e_msg(file, line, M_FATAL, 0, "rwl_writeunlock failure. stat=%d: ERR=%s\n",
401 errstat, be.bstrerror(errstat));
406 * Start a transaction. This groups inserts and makes things
407 * much more efficient. Usually started when inserting
410 void db_start_transaction(JCR *jcr, B_DB *mdb)
413 jcr->attr = get_pool_memory(PM_FNAME);
416 jcr->ar = (ATTR_DBR *)malloc(sizeof(ATTR_DBR));
420 if (!mdb->allow_transactions) {
424 /* Allow only 10,000 changes per transaction */
425 if (mdb->transaction && mdb->changes > 10000) {
426 db_end_transaction(jcr, mdb);
428 if (!mdb->transaction) {
429 my_sqlite_query(mdb, "BEGIN"); /* begin transaction */
430 Dmsg0(400, "Start SQLite transaction\n");
431 mdb->transaction = 1;
437 * This is turned off because transactions break
438 * if multiple simultaneous jobs are run.
440 #ifdef HAVE_POSTGRESQL
441 if (!mdb->allow_transactions) {
445 /* Allow only 25,000 changes per transaction */
446 if (mdb->transaction && mdb->changes > 25000) {
447 db_end_transaction(jcr, mdb);
449 if (!mdb->transaction) {
450 db_sql_query(mdb, "BEGIN", NULL, NULL); /* begin transaction */
451 Dmsg0(400, "Start PosgreSQL transaction\n");
452 mdb->transaction = 1;
458 if (db_type == SQL_TYPE_SQLITE) {
459 if (!mdb->allow_transactions) {
463 /* Allow only 10,000 changes per transaction */
464 if (mdb->transaction && mdb->changes > 10000) {
465 db_end_transaction(jcr, mdb);
467 if (!mdb->transaction) {
468 //my_sqlite_query(mdb, "BEGIN"); /* begin transaction */
469 db_sql_query(mdb, "BEGIN", NULL, NULL); /* begin transaction */
470 Dmsg0(400, "Start SQLite transaction\n");
471 mdb->transaction = 1;
474 } else if (db_type == SQL_TYPE_POSTGRESQL) {
475 if (!mdb->allow_transactions) {
479 /* Allow only 25,000 changes per transaction */
480 if (mdb->transaction && mdb->changes > 25000) {
481 db_end_transaction(jcr, mdb);
483 if (!mdb->transaction) {
484 db_sql_query(mdb, "BEGIN", NULL, NULL); /* begin transaction */
485 Dmsg0(400, "Start PosgreSQL transaction\n");
486 mdb->transaction = 1;
493 void db_end_transaction(JCR *jcr, B_DB *mdb)
496 * This can be called during thread cleanup and
497 * the db may already be closed. So simply return.
503 if (jcr && jcr->cached_attribute) {
504 Dmsg0(400, "Flush last cached attribute.\n");
505 if (!db_create_attributes_record(jcr, mdb, jcr->ar)) {
506 Jmsg1(jcr, M_FATAL, 0, _("Attribute create error. %s"), db_strerror(jcr->db));
508 jcr->cached_attribute = false;
512 if (!mdb->allow_transactions) {
516 if (mdb->transaction) {
517 my_sqlite_query(mdb, "COMMIT"); /* end transaction */
518 mdb->transaction = 0;
519 Dmsg1(400, "End SQLite transaction changes=%d\n", mdb->changes);
525 #ifdef HAVE_POSTGRESQL
526 if (!mdb->allow_transactions) {
530 if (mdb->transaction) {
531 db_sql_query(mdb, "COMMIT", NULL, NULL); /* end transaction */
532 mdb->transaction = 0;
533 Dmsg1(400, "End PostgreSQL transaction changes=%d\n", mdb->changes);
540 if (db_type == SQL_TYPE_SQLITE) {
541 if (!mdb->allow_transactions) {
545 if (mdb->transaction) {
546 //my_sqlite_query(mdb, "COMMIT"); /* end transaction */
547 db_sql_query(mdb, "COMMIT", NULL, NULL); /* end transaction */
548 mdb->transaction = 0;
549 Dmsg1(400, "End SQLite transaction changes=%d\n", mdb->changes);
553 } else if (db_type == SQL_TYPE_POSTGRESQL) {
554 if (!mdb->allow_transactions) {
558 if (mdb->transaction) {
559 db_sql_query(mdb, "COMMIT", NULL, NULL); /* end transaction */
560 mdb->transaction = 0;
561 Dmsg1(400, "End PostgreSQL transaction changes=%d\n", mdb->changes);
570 * Given a full filename, split it into its path
571 * and filename parts. They are returned in pool memory
572 * in the mdb structure.
574 void split_path_and_file(JCR *jcr, B_DB *mdb, const char *fname)
578 /* Find path without the filename.
579 * I.e. everything after the last / is a "filename".
580 * OK, maybe it is a directory name, but we treat it like
581 * a filename. If we don't find a / then the whole name
582 * must be a path name (e.g. c:).
584 for (p=f=fname; *p; p++) {
585 if (IsPathSeparator(*p)) {
586 f = p; /* set pos of last slash */
589 if (IsPathSeparator(*f)) { /* did we find a slash? */
590 f++; /* yes, point to filename */
591 } else { /* no, whole thing must be path name */
595 /* If filename doesn't exist (i.e. root directory), we
596 * simply create a blank name consisting of a single
597 * space. This makes handling zero length filenames
602 mdb->fname = check_pool_memory_size(mdb->fname, mdb->fnl+1);
603 memcpy(mdb->fname, f, mdb->fnl); /* copy filename */
604 mdb->fname[mdb->fnl] = 0;
610 mdb->pnl = f - fname;
612 mdb->path = check_pool_memory_size(mdb->path, mdb->pnl+1);
613 memcpy(mdb->path, fname, mdb->pnl);
614 mdb->path[mdb->pnl] = 0;
616 Mmsg1(&mdb->errmsg, _("Path length is zero. File=%s\n"), fname);
617 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
622 Dmsg2(500, "split path=%s file=%s\n", mdb->path, mdb->fname);
626 * Set maximum field length to something reasonable
628 static int max_length(int max_length)
630 int max_len = max_length;
634 } else if (max_len > 100) {
641 * List dashes as part of header for listing SQL results in a table
644 list_dashes(B_DB *mdb, DB_LIST_HANDLER *send, void *ctx)
650 sql_field_seek(mdb, 0);
652 for (i = 0; i < sql_num_fields(mdb); i++) {
653 field = sql_fetch_field(mdb);
657 len = max_length(field->max_length + 2);
658 for (j = 0; j < len; j++) {
667 * If full_list is set, we list vertically, otherwise, we
668 * list on one line horizontally.
671 list_result(JCR *jcr, B_DB *mdb, DB_LIST_HANDLER *send, void *ctx, e_list_type type)
675 int i, col_len, max_len = 0;
676 char buf[2000], ewc[30];
678 Dmsg0(800, "list_result starts\n");
679 if (mdb->result == NULL || sql_num_rows(mdb) == 0) {
680 send(ctx, _("No results to list.\n"));
684 Dmsg1(800, "list_result starts looking at %d fields\n", sql_num_fields(mdb));
685 /* determine column display widths */
686 sql_field_seek(mdb, 0);
687 for (i = 0; i < sql_num_fields(mdb); i++) {
688 Dmsg1(800, "list_result processing field %d\n", i);
689 field = sql_fetch_field(mdb);
693 col_len = cstrlen(field->name);
694 if (type == VERT_LIST) {
695 if (col_len > max_len) {
699 if (IS_NUM(field->type) && (int)field->max_length > 0) { /* fixup for commas */
700 field->max_length += (field->max_length - 1) / 3;
702 if (col_len < (int)field->max_length) {
703 col_len = field->max_length;
705 if (col_len < 4 && !IS_NOT_NULL(field->flags)) {
706 col_len = 4; /* 4 = length of the word "NULL" */
708 field->max_length = col_len; /* reset column info */
712 Dmsg0(800, "list_result finished first loop\n");
713 if (type == VERT_LIST) {
717 Dmsg1(800, "list_result starts second loop looking at %d fields\n", sql_num_fields(mdb));
718 list_dashes(mdb, send, ctx);
720 sql_field_seek(mdb, 0);
721 for (i = 0; i < sql_num_fields(mdb); i++) {
722 Dmsg1(800, "list_result looking at field %d\n", i);
723 field = sql_fetch_field(mdb);
727 max_len = max_length(field->max_length);
728 bsnprintf(buf, sizeof(buf), " %-*s |", max_len, field->name);
732 list_dashes(mdb, send, ctx);
734 Dmsg1(800, "list_result starts third loop looking at %d fields\n", sql_num_fields(mdb));
735 while ((row = sql_fetch_row(mdb)) != NULL) {
736 sql_field_seek(mdb, 0);
738 for (i = 0; i < sql_num_fields(mdb); i++) {
739 field = sql_fetch_field(mdb);
743 max_len = max_length(field->max_length);
744 if (row[i] == NULL) {
745 bsnprintf(buf, sizeof(buf), " %-*s |", max_len, "NULL");
746 } else if (IS_NUM(field->type) && !jcr->gui && is_an_integer(row[i])) {
747 bsnprintf(buf, sizeof(buf), " %*s |", max_len,
748 add_commas(row[i], ewc));
750 bsnprintf(buf, sizeof(buf), " %-*s |", max_len, row[i]);
756 list_dashes(mdb, send, ctx);
761 Dmsg1(800, "list_result starts vertical list at %d fields\n", sql_num_fields(mdb));
762 while ((row = sql_fetch_row(mdb)) != NULL) {
763 sql_field_seek(mdb, 0);
764 for (i = 0; i < sql_num_fields(mdb); i++) {
765 field = sql_fetch_field(mdb);
769 if (row[i] == NULL) {
770 bsnprintf(buf, sizeof(buf), " %*s: %s\n", max_len, field->name, "NULL");
771 } else if (IS_NUM(field->type) && !jcr->gui && is_an_integer(row[i])) {
772 bsnprintf(buf, sizeof(buf), " %*s: %s\n", max_len, field->name,
773 add_commas(row[i], ewc));
775 bsnprintf(buf, sizeof(buf), " %*s: %s\n", max_len, field->name, row[i]);
785 * Open a new connexion to mdb catalog. This function is used
786 * by batch and accurate mode.
788 bool db_open_batch_connexion(JCR *jcr, B_DB *mdb)
790 #ifdef HAVE_BATCH_FILE_INSERT
791 const int multi_db = true; /* we force a new connection only if batch insert is enabled */
793 const int multi_db = false;
796 if (!jcr->db_batch) {
797 jcr->db_batch = db_init_database(jcr,
804 multi_db /* multi_db = true when using batch mode */);
805 if (!jcr->db_batch) {
806 Mmsg0(&mdb->errmsg, _("Could not init database batch connection"));
807 Jmsg(jcr, M_FATAL, 0, "%s", mdb->errmsg);
811 if (!db_open_database(jcr, jcr->db_batch)) {
812 Mmsg2(&mdb->errmsg, _("Could not open database \"%s\": ERR=%s\n"),
813 jcr->db_batch->db_name, db_strerror(jcr->db_batch));
814 Jmsg(jcr, M_FATAL, 0, "%s", mdb->errmsg);
817 Dmsg3(100, "initdb ref=%d connected=%d db=%p\n", jcr->db_batch->ref_count,
818 jcr->db_batch->connected, jcr->db_batch->db);
825 * !!! WARNING !!! Use this function only when bacula is stopped.
826 * ie, after a fatal signal and before exiting the program
827 * Print information about a B_DB object.
829 void dbg_print_db(JCR *jcr, FILE *fp)
837 fprintf(fp, "B_DB=%p db_name=%s db_user=%s connected=%i\n",
838 mdb, NPRTB(mdb->db_name), NPRTB(mdb->db_user), mdb->connected);
839 fprintf(fp, "\tcmd=\"%s\" changes=%i\n", NPRTB(mdb->cmd), mdb->changes);
840 if (mdb->lock.valid == RWLOCK_VALID) {
841 fprintf(fp, "\tRWLOCK=%p w_active=%i w_wait=%i\n", &mdb->lock, mdb->lock.w_active, mdb->lock.w_wait);
845 #endif /* HAVE_SQLITE3 || HAVE_MYSQL || HAVE_SQLITE || HAVE_POSTGRESQL*/