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)
317 if (mdb->allow_transactions) { /* batch connection */
320 if (_db_lock_recurse_count && !pthread_equal(_db_lock_threadid, pthread_self())) {
321 Dmsg2(1, "ERROR: not the same threadif %p != %p\n", _db_lock_threadid, pthread_self());
323 _db_lock_recurse_count++;
324 _db_lock_time = (utime_t) time(NULL);
325 _db_lock_threadid = pthread_self();
329 static void update_unlock_dbg(B_DB *mdb)
332 if (mdb->allow_transactions) { /* batch connection */
335 if (!pthread_equal(_db_lock_threadid, pthread_self())) {
336 Dmsg2(1, "ERROR: not the same threadid %p != %p", _db_lock_threadid, pthread_self());
338 _db_lock_recurse_count--;
339 if (!_db_lock_recurse_count) {
340 memset(&_db_lock_threadid, 0, sizeof(_db_lock_threadid));
346 * Lock database, this can be called multiple times by the same
347 * thread without blocking, but must be unlocked the number of
348 * times it was locked.
350 void _db_lock(const char *file, int line, B_DB *mdb)
353 if ((errstat=rwl_writelock(&mdb->lock)) != 0) {
355 e_msg(file, line, M_FATAL, 0, "rwl_writelock failure. stat=%d: ERR=%s\n",
356 errstat, be.bstrerror(errstat));
358 update_lock_dbg(mdb);
362 * Unlock the database. This can be called multiple times by the
363 * same thread up to the number of times that thread called
366 void _db_unlock(const char *file, int line, B_DB *mdb)
369 update_unlock_dbg(mdb);
370 if ((errstat=rwl_writeunlock(&mdb->lock)) != 0) {
372 e_msg(file, line, M_FATAL, 0, "rwl_writeunlock failure. stat=%d: ERR=%s\n",
373 errstat, be.bstrerror(errstat));
378 * Start a transaction. This groups inserts and makes things
379 * much more efficient. Usually started when inserting
382 void db_start_transaction(JCR *jcr, B_DB *mdb)
385 jcr->attr = get_pool_memory(PM_FNAME);
388 jcr->ar = (ATTR_DBR *)malloc(sizeof(ATTR_DBR));
392 if (!mdb->allow_transactions) {
396 /* Allow only 10,000 changes per transaction */
397 if (mdb->transaction && mdb->changes > 10000) {
398 db_end_transaction(jcr, mdb);
400 if (!mdb->transaction) {
401 my_sqlite_query(mdb, "BEGIN"); /* begin transaction */
402 Dmsg0(400, "Start SQLite transaction\n");
403 mdb->transaction = 1;
409 * This is turned off because transactions break
410 * if multiple simultaneous jobs are run.
412 #ifdef HAVE_POSTGRESQL
413 if (!mdb->allow_transactions) {
417 /* Allow only 25,000 changes per transaction */
418 if (mdb->transaction && mdb->changes > 25000) {
419 db_end_transaction(jcr, mdb);
421 if (!mdb->transaction) {
422 db_sql_query(mdb, "BEGIN", NULL, NULL); /* begin transaction */
423 Dmsg0(400, "Start PosgreSQL transaction\n");
424 mdb->transaction = 1;
430 if (db_type == SQL_TYPE_SQLITE) {
431 if (!mdb->allow_transactions) {
435 /* Allow only 10,000 changes per transaction */
436 if (mdb->transaction && mdb->changes > 10000) {
437 db_end_transaction(jcr, mdb);
439 if (!mdb->transaction) {
440 //my_sqlite_query(mdb, "BEGIN"); /* begin transaction */
441 db_sql_query(mdb, "BEGIN", NULL, NULL); /* begin transaction */
442 Dmsg0(400, "Start SQLite transaction\n");
443 mdb->transaction = 1;
446 } else if (db_type == SQL_TYPE_POSTGRESQL) {
447 if (!mdb->allow_transactions) {
451 /* Allow only 25,000 changes per transaction */
452 if (mdb->transaction && mdb->changes > 25000) {
453 db_end_transaction(jcr, mdb);
455 if (!mdb->transaction) {
456 db_sql_query(mdb, "BEGIN", NULL, NULL); /* begin transaction */
457 Dmsg0(400, "Start PosgreSQL transaction\n");
458 mdb->transaction = 1;
465 void db_end_transaction(JCR *jcr, B_DB *mdb)
468 * This can be called during thread cleanup and
469 * the db may already be closed. So simply return.
475 if (jcr && jcr->cached_attribute) {
476 Dmsg0(400, "Flush last cached attribute.\n");
477 if (!db_create_file_attributes_record(jcr, mdb, jcr->ar)) {
478 Jmsg1(jcr, M_FATAL, 0, _("Attribute create error. %s"), db_strerror(jcr->db));
480 jcr->cached_attribute = false;
484 if (!mdb->allow_transactions) {
488 if (mdb->transaction) {
489 my_sqlite_query(mdb, "COMMIT"); /* end transaction */
490 mdb->transaction = 0;
491 Dmsg1(400, "End SQLite transaction changes=%d\n", mdb->changes);
497 #ifdef HAVE_POSTGRESQL
498 if (!mdb->allow_transactions) {
502 if (mdb->transaction) {
503 db_sql_query(mdb, "COMMIT", NULL, NULL); /* end transaction */
504 mdb->transaction = 0;
505 Dmsg1(400, "End PostgreSQL transaction changes=%d\n", mdb->changes);
512 if (db_type == SQL_TYPE_SQLITE) {
513 if (!mdb->allow_transactions) {
517 if (mdb->transaction) {
518 //my_sqlite_query(mdb, "COMMIT"); /* end transaction */
519 db_sql_query(mdb, "COMMIT", NULL, NULL); /* end transaction */
520 mdb->transaction = 0;
521 Dmsg1(400, "End SQLite transaction changes=%d\n", mdb->changes);
525 } else if (db_type == SQL_TYPE_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);
542 * Given a full filename, split it into its path
543 * and filename parts. They are returned in pool memory
544 * in the mdb structure.
546 void split_path_and_file(JCR *jcr, B_DB *mdb, const char *fname)
550 /* Find path without the filename.
551 * I.e. everything after the last / is a "filename".
552 * OK, maybe it is a directory name, but we treat it like
553 * a filename. If we don't find a / then the whole name
554 * must be a path name (e.g. c:).
556 for (p=f=fname; *p; p++) {
557 if (IsPathSeparator(*p)) {
558 f = p; /* set pos of last slash */
561 if (IsPathSeparator(*f)) { /* did we find a slash? */
562 f++; /* yes, point to filename */
563 } else { /* no, whole thing must be path name */
567 /* If filename doesn't exist (i.e. root directory), we
568 * simply create a blank name consisting of a single
569 * space. This makes handling zero length filenames
574 mdb->fname = check_pool_memory_size(mdb->fname, mdb->fnl+1);
575 memcpy(mdb->fname, f, mdb->fnl); /* copy filename */
576 mdb->fname[mdb->fnl] = 0;
582 mdb->pnl = f - fname;
584 mdb->path = check_pool_memory_size(mdb->path, mdb->pnl+1);
585 memcpy(mdb->path, fname, mdb->pnl);
586 mdb->path[mdb->pnl] = 0;
588 Mmsg1(&mdb->errmsg, _("Path length is zero. File=%s\n"), fname);
589 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
594 Dmsg2(500, "split path=%s file=%s\n", mdb->path, mdb->fname);
598 * List dashes as part of header for listing SQL results in a table
601 list_dashes(B_DB *mdb, DB_LIST_HANDLER *send, void *ctx)
606 sql_field_seek(mdb, 0);
608 for (i = 0; i < sql_num_fields(mdb); i++) {
609 field = sql_fetch_field(mdb);
613 for (j = 0; j < (int)field->max_length + 2; j++) {
622 * If full_list is set, we list vertically, otherwise, we
623 * list on one line horizontally.
626 list_result(JCR *jcr, B_DB *mdb, DB_LIST_HANDLER *send, void *ctx, e_list_type type)
630 int i, col_len, max_len = 0;
631 char buf[2000], ewc[30];
633 Dmsg0(800, "list_result starts\n");
634 if (mdb->result == NULL || sql_num_rows(mdb) == 0) {
635 send(ctx, _("No results to list.\n"));
639 Dmsg1(800, "list_result starts looking at %d fields\n", sql_num_fields(mdb));
640 /* determine column display widths */
641 sql_field_seek(mdb, 0);
642 for (i = 0; i < sql_num_fields(mdb); i++) {
643 Dmsg1(800, "list_result processing field %d\n", i);
644 field = sql_fetch_field(mdb);
648 col_len = cstrlen(field->name);
649 if (type == VERT_LIST) {
650 if (col_len > max_len) {
654 if (IS_NUM(field->type) && (int)field->max_length > 0) { /* fixup for commas */
655 field->max_length += (field->max_length - 1) / 3;
657 if (col_len < (int)field->max_length) {
658 col_len = field->max_length;
660 if (col_len < 4 && !IS_NOT_NULL(field->flags)) {
661 col_len = 4; /* 4 = length of the word "NULL" */
663 field->max_length = col_len; /* reset column info */
667 Dmsg0(800, "list_result finished first loop\n");
668 if (type == VERT_LIST) {
672 Dmsg1(800, "list_result starts second loop looking at %d fields\n", sql_num_fields(mdb));
673 list_dashes(mdb, send, ctx);
675 sql_field_seek(mdb, 0);
676 for (i = 0; i < sql_num_fields(mdb); i++) {
677 Dmsg1(800, "list_result looking at field %d\n", i);
678 field = sql_fetch_field(mdb);
682 bsnprintf(buf, sizeof(buf), " %-*s |", (int)field->max_length, field->name);
686 list_dashes(mdb, send, ctx);
688 Dmsg1(800, "list_result starts third loop looking at %d fields\n", sql_num_fields(mdb));
689 while ((row = sql_fetch_row(mdb)) != NULL) {
690 sql_field_seek(mdb, 0);
692 for (i = 0; i < sql_num_fields(mdb); i++) {
693 field = sql_fetch_field(mdb);
697 if (row[i] == NULL) {
698 bsnprintf(buf, sizeof(buf), " %-*s |", (int)field->max_length, "NULL");
699 } else if (IS_NUM(field->type) && !jcr->gui && is_an_integer(row[i])) {
700 bsnprintf(buf, sizeof(buf), " %*s |", (int)field->max_length,
701 add_commas(row[i], ewc));
703 bsnprintf(buf, sizeof(buf), " %-*s |", (int)field->max_length, row[i]);
709 list_dashes(mdb, send, ctx);
714 Dmsg1(800, "list_result starts vertical list at %d fields\n", sql_num_fields(mdb));
715 while ((row = sql_fetch_row(mdb)) != NULL) {
716 sql_field_seek(mdb, 0);
717 for (i = 0; i < sql_num_fields(mdb); i++) {
718 field = sql_fetch_field(mdb);
722 if (row[i] == NULL) {
723 bsnprintf(buf, sizeof(buf), " %*s: %s\n", max_len, field->name, "NULL");
724 } else if (IS_NUM(field->type) && !jcr->gui && is_an_integer(row[i])) {
725 bsnprintf(buf, sizeof(buf), " %*s: %s\n", max_len, field->name,
726 add_commas(row[i], ewc));
728 bsnprintf(buf, sizeof(buf), " %*s: %s\n", max_len, field->name, row[i]);
738 * Open a new connexion to mdb catalog. This function is used
739 * by batch and accurate mode.
741 bool db_open_batch_connexion(JCR *jcr, B_DB *mdb)
745 #ifdef HAVE_BATCH_FILE_INSERT
746 multi_db=true; /* we force a new connexion only if batch insert is enabled */
749 if (!jcr->db_batch) {
750 jcr->db_batch = db_init_database(jcr,
757 multi_db /* multi_db = true when using batch mode */);
758 if (!jcr->db_batch) {
759 Jmsg0(jcr, M_FATAL, 0, "Could not init batch connexion");
763 if (!db_open_database(jcr, jcr->db_batch)) {
764 Mmsg2(&jcr->db_batch->errmsg, _("Could not open database \"%s\": ERR=%s\n"),
765 jcr->db_batch->db_name, db_strerror(jcr->db_batch));
766 Jmsg1(jcr, M_FATAL, 0, "%s", jcr->db_batch->errmsg);
769 Dmsg3(100, "initdb ref=%d connected=%d db=%p\n", jcr->db_batch->ref_count,
770 jcr->db_batch->connected, jcr->db_batch->db);
776 #endif /* HAVE_SQLITE3 || HAVE_MYSQL || HAVE_SQLITE || HAVE_POSTGRESQL*/