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) {
315 if (mdb->allow_transactions) { /* batch connection */
318 if (_db_lock_recurse_count && !pthread_equal(_db_lock_threadid, pthread_self())) {
319 Dmsg2(1, "ERROR: not the same threadif %p != %p\n", _db_lock_threadid, pthread_self());
321 _db_lock_recurse_count++;
322 _db_lock_time = (utime_t) time(NULL);
323 _db_lock_threadid = pthread_self();
326 static void update_unlock_dbg(B_DB *mdb) {
327 if (mdb->allow_transactions) { /* batch connection */
330 if (!pthread_equal(_db_lock_threadid, pthread_self())) {
331 Dmsg2(1, "ERROR: not the same threadid %p != %p", _db_lock_threadid, pthread_self());
333 _db_lock_recurse_count--;
334 if (!_db_lock_recurse_count) {
335 memset(&_db_lock_threadid, 0, sizeof(_db_lock_threadid));
340 * Lock database, this can be called multiple times by the same
341 * thread without blocking, but must be unlocked the number of
342 * times it was locked.
344 void _db_lock(const char *file, int line, B_DB *mdb)
347 if ((errstat=rwl_writelock(&mdb->lock)) != 0) {
349 e_msg(file, line, M_FATAL, 0, "rwl_writelock failure. stat=%d: ERR=%s\n",
350 errstat, be.bstrerror(errstat));
352 update_lock_dbg(mdb);
356 * Unlock the database. This can be called multiple times by the
357 * same thread up to the number of times that thread called
360 void _db_unlock(const char *file, int line, B_DB *mdb)
363 update_unlock_dbg(mdb);
364 if ((errstat=rwl_writeunlock(&mdb->lock)) != 0) {
366 e_msg(file, line, M_FATAL, 0, "rwl_writeunlock failure. stat=%d: ERR=%s\n",
367 errstat, be.bstrerror(errstat));
372 * Start a transaction. This groups inserts and makes things
373 * much more efficient. Usually started when inserting
376 void db_start_transaction(JCR *jcr, B_DB *mdb)
379 jcr->attr = get_pool_memory(PM_FNAME);
382 jcr->ar = (ATTR_DBR *)malloc(sizeof(ATTR_DBR));
386 if (!mdb->allow_transactions) {
390 /* Allow only 10,000 changes per transaction */
391 if (mdb->transaction && mdb->changes > 10000) {
392 db_end_transaction(jcr, mdb);
394 if (!mdb->transaction) {
395 my_sqlite_query(mdb, "BEGIN"); /* begin transaction */
396 Dmsg0(400, "Start SQLite transaction\n");
397 mdb->transaction = 1;
403 * This is turned off because transactions break
404 * if multiple simultaneous jobs are run.
406 #ifdef HAVE_POSTGRESQL
407 if (!mdb->allow_transactions) {
411 /* Allow only 25,000 changes per transaction */
412 if (mdb->transaction && mdb->changes > 25000) {
413 db_end_transaction(jcr, mdb);
415 if (!mdb->transaction) {
416 db_sql_query(mdb, "BEGIN", NULL, NULL); /* begin transaction */
417 Dmsg0(400, "Start PosgreSQL transaction\n");
418 mdb->transaction = 1;
424 if (db_type == SQL_TYPE_SQLITE) {
425 if (!mdb->allow_transactions) {
429 /* Allow only 10,000 changes per transaction */
430 if (mdb->transaction && mdb->changes > 10000) {
431 db_end_transaction(jcr, mdb);
433 if (!mdb->transaction) {
434 //my_sqlite_query(mdb, "BEGIN"); /* begin transaction */
435 db_sql_query(mdb, "BEGIN", NULL, NULL); /* begin transaction */
436 Dmsg0(400, "Start SQLite transaction\n");
437 mdb->transaction = 1;
440 } else if (db_type == SQL_TYPE_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;
459 void db_end_transaction(JCR *jcr, B_DB *mdb)
462 * This can be called during thread cleanup and
463 * the db may already be closed. So simply return.
469 if (jcr && jcr->cached_attribute) {
470 Dmsg0(400, "Flush last cached attribute.\n");
471 if (!db_create_file_attributes_record(jcr, mdb, jcr->ar)) {
472 Jmsg1(jcr, M_FATAL, 0, _("Attribute create error. %s"), db_strerror(jcr->db));
474 jcr->cached_attribute = false;
478 if (!mdb->allow_transactions) {
482 if (mdb->transaction) {
483 my_sqlite_query(mdb, "COMMIT"); /* end transaction */
484 mdb->transaction = 0;
485 Dmsg1(400, "End SQLite transaction changes=%d\n", mdb->changes);
491 #ifdef HAVE_POSTGRESQL
492 if (!mdb->allow_transactions) {
496 if (mdb->transaction) {
497 db_sql_query(mdb, "COMMIT", NULL, NULL); /* end transaction */
498 mdb->transaction = 0;
499 Dmsg1(400, "End PostgreSQL transaction changes=%d\n", mdb->changes);
506 if (db_type == SQL_TYPE_SQLITE) {
507 if (!mdb->allow_transactions) {
511 if (mdb->transaction) {
512 //my_sqlite_query(mdb, "COMMIT"); /* end transaction */
513 db_sql_query(mdb, "COMMIT", NULL, NULL); /* end transaction */
514 mdb->transaction = 0;
515 Dmsg1(400, "End SQLite transaction changes=%d\n", mdb->changes);
519 } else if (db_type == SQL_TYPE_POSTGRESQL) {
520 if (!mdb->allow_transactions) {
524 if (mdb->transaction) {
525 db_sql_query(mdb, "COMMIT", NULL, NULL); /* end transaction */
526 mdb->transaction = 0;
527 Dmsg1(400, "End PostgreSQL transaction changes=%d\n", mdb->changes);
536 * Given a full filename, split it into its path
537 * and filename parts. They are returned in pool memory
538 * in the mdb structure.
540 void split_path_and_file(JCR *jcr, B_DB *mdb, const char *fname)
544 /* Find path without the filename.
545 * I.e. everything after the last / is a "filename".
546 * OK, maybe it is a directory name, but we treat it like
547 * a filename. If we don't find a / then the whole name
548 * must be a path name (e.g. c:).
550 for (p=f=fname; *p; p++) {
551 if (IsPathSeparator(*p)) {
552 f = p; /* set pos of last slash */
555 if (IsPathSeparator(*f)) { /* did we find a slash? */
556 f++; /* yes, point to filename */
557 } else { /* no, whole thing must be path name */
561 /* If filename doesn't exist (i.e. root directory), we
562 * simply create a blank name consisting of a single
563 * space. This makes handling zero length filenames
568 mdb->fname = check_pool_memory_size(mdb->fname, mdb->fnl+1);
569 memcpy(mdb->fname, f, mdb->fnl); /* copy filename */
570 mdb->fname[mdb->fnl] = 0;
576 mdb->pnl = f - fname;
578 mdb->path = check_pool_memory_size(mdb->path, mdb->pnl+1);
579 memcpy(mdb->path, fname, mdb->pnl);
580 mdb->path[mdb->pnl] = 0;
582 Mmsg1(&mdb->errmsg, _("Path length is zero. File=%s\n"), fname);
583 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
588 Dmsg2(500, "split path=%s file=%s\n", mdb->path, mdb->fname);
592 * List dashes as part of header for listing SQL results in a table
595 list_dashes(B_DB *mdb, DB_LIST_HANDLER *send, void *ctx)
600 sql_field_seek(mdb, 0);
602 for (i = 0; i < sql_num_fields(mdb); i++) {
603 field = sql_fetch_field(mdb);
607 for (j = 0; j < (int)field->max_length + 2; j++) {
616 * If full_list is set, we list vertically, otherwise, we
617 * list on one line horizontally.
620 list_result(JCR *jcr, B_DB *mdb, DB_LIST_HANDLER *send, void *ctx, e_list_type type)
624 int i, col_len, max_len = 0;
625 char buf[2000], ewc[30];
627 Dmsg0(800, "list_result starts\n");
628 if (mdb->result == NULL || sql_num_rows(mdb) == 0) {
629 send(ctx, _("No results to list.\n"));
633 Dmsg1(800, "list_result starts looking at %d fields\n", sql_num_fields(mdb));
634 /* determine column display widths */
635 sql_field_seek(mdb, 0);
636 for (i = 0; i < sql_num_fields(mdb); i++) {
637 Dmsg1(800, "list_result processing field %d\n", i);
638 field = sql_fetch_field(mdb);
642 col_len = cstrlen(field->name);
643 if (type == VERT_LIST) {
644 if (col_len > max_len) {
648 if (IS_NUM(field->type) && (int)field->max_length > 0) { /* fixup for commas */
649 field->max_length += (field->max_length - 1) / 3;
651 if (col_len < (int)field->max_length) {
652 col_len = field->max_length;
654 if (col_len < 4 && !IS_NOT_NULL(field->flags)) {
655 col_len = 4; /* 4 = length of the word "NULL" */
657 field->max_length = col_len; /* reset column info */
661 Dmsg0(800, "list_result finished first loop\n");
662 if (type == VERT_LIST) {
666 Dmsg1(800, "list_result starts second loop looking at %d fields\n", sql_num_fields(mdb));
667 list_dashes(mdb, send, ctx);
669 sql_field_seek(mdb, 0);
670 for (i = 0; i < sql_num_fields(mdb); i++) {
671 Dmsg1(800, "list_result looking at field %d\n", i);
672 field = sql_fetch_field(mdb);
676 bsnprintf(buf, sizeof(buf), " %-*s |", (int)field->max_length, field->name);
680 list_dashes(mdb, send, ctx);
682 Dmsg1(800, "list_result starts third loop looking at %d fields\n", sql_num_fields(mdb));
683 while ((row = sql_fetch_row(mdb)) != NULL) {
684 sql_field_seek(mdb, 0);
686 for (i = 0; i < sql_num_fields(mdb); i++) {
687 field = sql_fetch_field(mdb);
691 if (row[i] == NULL) {
692 bsnprintf(buf, sizeof(buf), " %-*s |", (int)field->max_length, "NULL");
693 } else if (IS_NUM(field->type) && !jcr->gui && is_an_integer(row[i])) {
694 bsnprintf(buf, sizeof(buf), " %*s |", (int)field->max_length,
695 add_commas(row[i], ewc));
697 bsnprintf(buf, sizeof(buf), " %-*s |", (int)field->max_length, row[i]);
703 list_dashes(mdb, send, ctx);
708 Dmsg1(800, "list_result starts vertical list at %d fields\n", sql_num_fields(mdb));
709 while ((row = sql_fetch_row(mdb)) != NULL) {
710 sql_field_seek(mdb, 0);
711 for (i = 0; i < sql_num_fields(mdb); i++) {
712 field = sql_fetch_field(mdb);
716 if (row[i] == NULL) {
717 bsnprintf(buf, sizeof(buf), " %*s: %s\n", max_len, field->name, "NULL");
718 } else if (IS_NUM(field->type) && !jcr->gui && is_an_integer(row[i])) {
719 bsnprintf(buf, sizeof(buf), " %*s: %s\n", max_len, field->name,
720 add_commas(row[i], ewc));
722 bsnprintf(buf, sizeof(buf), " %*s: %s\n", max_len, field->name, row[i]);
732 * Open a new connexion to mdb catalog. This function is used
733 * by batch and accurate mode.
735 bool db_open_batch_connexion(JCR *jcr, B_DB *mdb)
739 #ifdef HAVE_BATCH_FILE_INSERT
740 multi_db=true; /* we force a new connexion only if batch insert is enabled */
743 if (!jcr->db_batch) {
744 jcr->db_batch = db_init_database(jcr,
751 multi_db /* multi_db = true when using batch mode */);
752 if (!jcr->db_batch) {
753 Jmsg0(jcr, M_FATAL, 0, "Could not init batch connexion");
757 if (!db_open_database(jcr, jcr->db_batch)) {
758 Mmsg2(&jcr->db_batch->errmsg, _("Could not open database \"%s\": ERR=%s\n"),
759 jcr->db_batch->db_name, db_strerror(jcr->db_batch));
760 Jmsg1(jcr, M_FATAL, 0, "%s", jcr->db_batch->errmsg);
763 Dmsg3(100, "initdb ref=%d connected=%d db=%p\n", jcr->db_batch->ref_count,
764 jcr->db_batch->connected, jcr->db_batch->db);
770 #endif /* HAVE_SQLITE3 || HAVE_MYSQL || HAVE_SQLITE || HAVE_POSTGRESQL*/