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)
314 static void update_lock_dbg(B_DB *mdb)
316 if (mdb->allow_transactions) { /* batch connection */
319 if (_db_lock_recurse_count && !pthread_equal(_db_lock_threadid, pthread_self())) {
320 Dmsg2(1, "ERROR: not the same threadif %p != %p\n", _db_lock_threadid, pthread_self());
322 _db_lock_recurse_count++;
323 _db_lock_time = (utime_t) time(NULL);
324 _db_lock_threadid = pthread_self();
327 static void update_unlock_dbg(B_DB *mdb)
329 if (mdb->allow_transactions) { /* batch connection */
332 if (!pthread_equal(_db_lock_threadid, pthread_self())) {
333 Dmsg2(1, "ERROR: not the same threadid %p != %p", _db_lock_threadid, pthread_self());
335 _db_lock_recurse_count--;
336 if (!_db_lock_recurse_count) {
337 memset(&_db_lock_threadid, 0, sizeof(_db_lock_threadid));
342 * Lock database, this can be called multiple times by the same
343 * thread without blocking, but must be unlocked the number of
344 * times it was locked.
346 void _db_lock(const char *file, int line, B_DB *mdb)
349 if ((errstat=rwl_writelock(&mdb->lock)) != 0) {
351 e_msg(file, line, M_FATAL, 0, "rwl_writelock failure. stat=%d: ERR=%s\n",
352 errstat, be.bstrerror(errstat));
354 update_lock_dbg(mdb);
358 * Unlock the database. This can be called multiple times by the
359 * same thread up to the number of times that thread called
362 void _db_unlock(const char *file, int line, B_DB *mdb)
365 update_unlock_dbg(mdb);
366 if ((errstat=rwl_writeunlock(&mdb->lock)) != 0) {
368 e_msg(file, line, M_FATAL, 0, "rwl_writeunlock failure. stat=%d: ERR=%s\n",
369 errstat, be.bstrerror(errstat));
374 * Start a transaction. This groups inserts and makes things
375 * much more efficient. Usually started when inserting
378 void db_start_transaction(JCR *jcr, B_DB *mdb)
381 jcr->attr = get_pool_memory(PM_FNAME);
384 jcr->ar = (ATTR_DBR *)malloc(sizeof(ATTR_DBR));
388 if (!mdb->allow_transactions) {
392 /* Allow only 10,000 changes per transaction */
393 if (mdb->transaction && mdb->changes > 10000) {
394 db_end_transaction(jcr, mdb);
396 if (!mdb->transaction) {
397 my_sqlite_query(mdb, "BEGIN"); /* begin transaction */
398 Dmsg0(400, "Start SQLite transaction\n");
399 mdb->transaction = 1;
405 * This is turned off because transactions break
406 * if multiple simultaneous jobs are run.
408 #ifdef HAVE_POSTGRESQL
409 if (!mdb->allow_transactions) {
413 /* Allow only 25,000 changes per transaction */
414 if (mdb->transaction && mdb->changes > 25000) {
415 db_end_transaction(jcr, mdb);
417 if (!mdb->transaction) {
418 db_sql_query(mdb, "BEGIN", NULL, NULL); /* begin transaction */
419 Dmsg0(400, "Start PosgreSQL transaction\n");
420 mdb->transaction = 1;
426 if (db_type == SQL_TYPE_SQLITE) {
427 if (!mdb->allow_transactions) {
431 /* Allow only 10,000 changes per transaction */
432 if (mdb->transaction && mdb->changes > 10000) {
433 db_end_transaction(jcr, mdb);
435 if (!mdb->transaction) {
436 //my_sqlite_query(mdb, "BEGIN"); /* begin transaction */
437 db_sql_query(mdb, "BEGIN", NULL, NULL); /* begin transaction */
438 Dmsg0(400, "Start SQLite transaction\n");
439 mdb->transaction = 1;
442 } else if (db_type == SQL_TYPE_POSTGRESQL) {
443 if (!mdb->allow_transactions) {
447 /* Allow only 25,000 changes per transaction */
448 if (mdb->transaction && mdb->changes > 25000) {
449 db_end_transaction(jcr, mdb);
451 if (!mdb->transaction) {
452 db_sql_query(mdb, "BEGIN", NULL, NULL); /* begin transaction */
453 Dmsg0(400, "Start PosgreSQL transaction\n");
454 mdb->transaction = 1;
461 void db_end_transaction(JCR *jcr, B_DB *mdb)
464 * This can be called during thread cleanup and
465 * the db may already be closed. So simply return.
471 if (jcr && jcr->cached_attribute) {
472 Dmsg0(400, "Flush last cached attribute.\n");
473 if (!db_create_file_attributes_record(jcr, mdb, jcr->ar)) {
474 Jmsg1(jcr, M_FATAL, 0, _("Attribute create error. %s"), db_strerror(jcr->db));
476 jcr->cached_attribute = false;
480 if (!mdb->allow_transactions) {
484 if (mdb->transaction) {
485 my_sqlite_query(mdb, "COMMIT"); /* end transaction */
486 mdb->transaction = 0;
487 Dmsg1(400, "End SQLite transaction changes=%d\n", mdb->changes);
493 #ifdef HAVE_POSTGRESQL
494 if (!mdb->allow_transactions) {
498 if (mdb->transaction) {
499 db_sql_query(mdb, "COMMIT", NULL, NULL); /* end transaction */
500 mdb->transaction = 0;
501 Dmsg1(400, "End PostgreSQL transaction changes=%d\n", mdb->changes);
508 if (db_type == SQL_TYPE_SQLITE) {
509 if (!mdb->allow_transactions) {
513 if (mdb->transaction) {
514 //my_sqlite_query(mdb, "COMMIT"); /* end transaction */
515 db_sql_query(mdb, "COMMIT", NULL, NULL); /* end transaction */
516 mdb->transaction = 0;
517 Dmsg1(400, "End SQLite transaction changes=%d\n", mdb->changes);
521 } else if (db_type == SQL_TYPE_POSTGRESQL) {
522 if (!mdb->allow_transactions) {
526 if (mdb->transaction) {
527 db_sql_query(mdb, "COMMIT", NULL, NULL); /* end transaction */
528 mdb->transaction = 0;
529 Dmsg1(400, "End PostgreSQL transaction changes=%d\n", mdb->changes);
538 * Given a full filename, split it into its path
539 * and filename parts. They are returned in pool memory
540 * in the mdb structure.
542 void split_path_and_file(JCR *jcr, B_DB *mdb, const char *fname)
546 /* Find path without the filename.
547 * I.e. everything after the last / is a "filename".
548 * OK, maybe it is a directory name, but we treat it like
549 * a filename. If we don't find a / then the whole name
550 * must be a path name (e.g. c:).
552 for (p=f=fname; *p; p++) {
553 if (IsPathSeparator(*p)) {
554 f = p; /* set pos of last slash */
557 if (IsPathSeparator(*f)) { /* did we find a slash? */
558 f++; /* yes, point to filename */
559 } else { /* no, whole thing must be path name */
563 /* If filename doesn't exist (i.e. root directory), we
564 * simply create a blank name consisting of a single
565 * space. This makes handling zero length filenames
570 mdb->fname = check_pool_memory_size(mdb->fname, mdb->fnl+1);
571 memcpy(mdb->fname, f, mdb->fnl); /* copy filename */
572 mdb->fname[mdb->fnl] = 0;
578 mdb->pnl = f - fname;
580 mdb->path = check_pool_memory_size(mdb->path, mdb->pnl+1);
581 memcpy(mdb->path, fname, mdb->pnl);
582 mdb->path[mdb->pnl] = 0;
584 Mmsg1(&mdb->errmsg, _("Path length is zero. File=%s\n"), fname);
585 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
590 Dmsg2(500, "split path=%s file=%s\n", mdb->path, mdb->fname);
594 * List dashes as part of header for listing SQL results in a table
597 list_dashes(B_DB *mdb, DB_LIST_HANDLER *send, void *ctx)
602 sql_field_seek(mdb, 0);
604 for (i = 0; i < sql_num_fields(mdb); i++) {
605 field = sql_fetch_field(mdb);
609 for (j = 0; j < (int)field->max_length + 2; j++) {
618 * If full_list is set, we list vertically, otherwise, we
619 * list on one line horizontally.
622 list_result(JCR *jcr, B_DB *mdb, DB_LIST_HANDLER *send, void *ctx, e_list_type type)
626 int i, col_len, max_len = 0;
627 char buf[2000], ewc[30];
629 Dmsg0(800, "list_result starts\n");
630 if (mdb->result == NULL || sql_num_rows(mdb) == 0) {
631 send(ctx, _("No results to list.\n"));
635 Dmsg1(800, "list_result starts looking at %d fields\n", sql_num_fields(mdb));
636 /* determine column display widths */
637 sql_field_seek(mdb, 0);
638 for (i = 0; i < sql_num_fields(mdb); i++) {
639 Dmsg1(800, "list_result processing field %d\n", i);
640 field = sql_fetch_field(mdb);
644 col_len = cstrlen(field->name);
645 if (type == VERT_LIST) {
646 if (col_len > max_len) {
650 if (IS_NUM(field->type) && (int)field->max_length > 0) { /* fixup for commas */
651 field->max_length += (field->max_length - 1) / 3;
653 if (col_len < (int)field->max_length) {
654 col_len = field->max_length;
656 if (col_len < 4 && !IS_NOT_NULL(field->flags)) {
657 col_len = 4; /* 4 = length of the word "NULL" */
659 field->max_length = col_len; /* reset column info */
663 Dmsg0(800, "list_result finished first loop\n");
664 if (type == VERT_LIST) {
668 Dmsg1(800, "list_result starts second loop looking at %d fields\n", sql_num_fields(mdb));
669 list_dashes(mdb, send, ctx);
671 sql_field_seek(mdb, 0);
672 for (i = 0; i < sql_num_fields(mdb); i++) {
673 Dmsg1(800, "list_result looking at field %d\n", i);
674 field = sql_fetch_field(mdb);
678 bsnprintf(buf, sizeof(buf), " %-*s |", (int)field->max_length, field->name);
682 list_dashes(mdb, send, ctx);
684 Dmsg1(800, "list_result starts third loop looking at %d fields\n", sql_num_fields(mdb));
685 while ((row = sql_fetch_row(mdb)) != NULL) {
686 sql_field_seek(mdb, 0);
688 for (i = 0; i < sql_num_fields(mdb); i++) {
689 field = sql_fetch_field(mdb);
693 if (row[i] == NULL) {
694 bsnprintf(buf, sizeof(buf), " %-*s |", (int)field->max_length, "NULL");
695 } else if (IS_NUM(field->type) && !jcr->gui && is_an_integer(row[i])) {
696 bsnprintf(buf, sizeof(buf), " %*s |", (int)field->max_length,
697 add_commas(row[i], ewc));
699 bsnprintf(buf, sizeof(buf), " %-*s |", (int)field->max_length, row[i]);
705 list_dashes(mdb, send, ctx);
710 Dmsg1(800, "list_result starts vertical list at %d fields\n", sql_num_fields(mdb));
711 while ((row = sql_fetch_row(mdb)) != NULL) {
712 sql_field_seek(mdb, 0);
713 for (i = 0; i < sql_num_fields(mdb); i++) {
714 field = sql_fetch_field(mdb);
718 if (row[i] == NULL) {
719 bsnprintf(buf, sizeof(buf), " %*s: %s\n", max_len, field->name, "NULL");
720 } else if (IS_NUM(field->type) && !jcr->gui && is_an_integer(row[i])) {
721 bsnprintf(buf, sizeof(buf), " %*s: %s\n", max_len, field->name,
722 add_commas(row[i], ewc));
724 bsnprintf(buf, sizeof(buf), " %*s: %s\n", max_len, field->name, row[i]);
734 * Open a new connexion to mdb catalog. This function is used
735 * by batch and accurate mode.
737 bool db_open_batch_connexion(JCR *jcr, B_DB *mdb)
741 #ifdef HAVE_BATCH_FILE_INSERT
742 multi_db=true; /* we force a new connexion only if batch insert is enabled */
745 if (!jcr->db_batch) {
746 jcr->db_batch = db_init_database(jcr,
753 multi_db /* multi_db = true when using batch mode */);
754 if (!jcr->db_batch) {
755 Jmsg0(jcr, M_FATAL, 0, "Could not init batch connexion");
759 if (!db_open_database(jcr, jcr->db_batch)) {
760 Mmsg2(&jcr->db_batch->errmsg, _("Could not open database \"%s\": ERR=%s\n"),
761 jcr->db_batch->db_name, db_strerror(jcr->db_batch));
762 Jmsg1(jcr, M_FATAL, 0, "%s", jcr->db_batch->errmsg);
765 Dmsg3(100, "initdb ref=%d connected=%d db=%p\n", jcr->db_batch->ref_count,
766 jcr->db_batch->connected, jcr->db_batch->db);
772 #endif /* HAVE_SQLITE3 || HAVE_MYSQL || HAVE_SQLITE || HAVE_POSTGRESQL*/