2 Bacula® - The Network Backup Solution
4 Copyright (C) 2000-2007 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 John Walker.
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)
34 * Kern Sibbald, March 2000
39 /* The following is necessary so that we do not include
40 * the dummy external definition of B_DB.
42 #define __SQL_C /* indicate that this is sql.c */
47 #if HAVE_SQLITE3 || HAVE_MYSQL || HAVE_SQLITE || HAVE_POSTGRESQL
49 uint32_t bacula_db_version = 0;
51 /* Forward referenced subroutines */
52 void print_dashes(B_DB *mdb);
53 void print_result(B_DB *mdb);
55 dbid_list::dbid_list()
57 memset(this, 0, sizeof(dbid_list));
59 DBId = (DBId_t *)malloc(max_ids * sizeof(DBId_t));
60 num_ids = num_seen = tot_ids = 0;
64 dbid_list::~dbid_list()
71 * Called here to retrieve an integer from the database
73 static int int_handler(void *ctx, int num_fields, char **row)
75 uint32_t *val = (uint32_t *)ctx;
77 Dmsg1(800, "int_handler starts with row pointing at %x\n", row);
80 Dmsg1(800, "int_handler finds '%s'\n", row[0]);
81 *val = str_to_int64(row[0]);
83 Dmsg0(800, "int_handler finds zero\n");
86 Dmsg0(800, "int_handler finishes\n");
91 * Called here to retrieve a 32/64 bit integer from the database.
92 * The returned integer will be extended to 64 bit.
94 int db_int64_handler(void *ctx, int num_fields, char **row)
96 db_int64_ctx *lctx = (db_int64_ctx *)ctx;
99 lctx->value = str_to_int64(row[0]);
107 /* NOTE!!! The following routines expect that the
108 * calling subroutine sets and clears the mutex
111 /* Check that the tables correspond to the version we want */
112 bool check_tables_version(JCR *jcr, B_DB *mdb)
114 const char *query = "SELECT VersionId FROM Version";
116 bacula_db_version = 0;
117 if (!db_sql_query(mdb, query, int_handler, (void *)&bacula_db_version)) {
118 Jmsg(jcr, M_FATAL, 0, "%s", mdb->errmsg);
121 if (bacula_db_version != BDB_VERSION) {
122 Mmsg(mdb->errmsg, "Version error for database \"%s\". Wanted %d, got %d\n",
123 mdb->db_name, BDB_VERSION, bacula_db_version);
124 Jmsg(jcr, M_FATAL, 0, "%s", mdb->errmsg);
130 /* Utility routine for queries. The database MUST be locked before calling here. */
132 QueryDB(const char *file, int line, JCR *jcr, B_DB *mdb, char *cmd)
136 sql_free_result(mdb);
137 if ((status=sql_query(mdb, cmd)) != 0) {
138 m_msg(file, line, &mdb->errmsg, _("query %s failed:\n%s\n"), cmd, sql_strerror(mdb));
139 j_msg(file, line, jcr, M_FATAL, 0, "%s", mdb->errmsg);
141 j_msg(file, line, jcr, M_INFO, 0, "%s\n", cmd);
146 mdb->result = sql_store_result(mdb);
148 return mdb->result != NULL;
152 * Utility routine to do inserts
153 * Returns: 0 on failure
157 InsertDB(const char *file, int line, JCR *jcr, B_DB *mdb, char *cmd)
159 if (sql_query(mdb, cmd)) {
160 m_msg(file, line, &mdb->errmsg, _("insert %s failed:\n%s\n"), cmd, sql_strerror(mdb));
161 j_msg(file, line, jcr, M_FATAL, 0, "%s", mdb->errmsg);
163 j_msg(file, line, jcr, M_INFO, 0, "%s\n", cmd);
167 if (mdb->have_insert_id) {
168 mdb->num_rows = sql_affected_rows(mdb);
172 if (mdb->num_rows != 1) {
174 m_msg(file, line, &mdb->errmsg, _("Insertion problem: affected_rows=%s\n"),
175 edit_uint64(mdb->num_rows, ed1));
177 j_msg(file, line, jcr, M_INFO, 0, "%s\n", cmd);
185 /* Utility routine for updates.
186 * Returns: 0 on failure
190 UpdateDB(const char *file, int line, JCR *jcr, B_DB *mdb, char *cmd)
193 if (sql_query(mdb, cmd)) {
194 m_msg(file, line, &mdb->errmsg, _("update %s failed:\n%s\n"), cmd, sql_strerror(mdb));
195 j_msg(file, line, jcr, M_ERROR, 0, "%s", mdb->errmsg);
197 j_msg(file, line, jcr, M_INFO, 0, "%s\n", cmd);
201 mdb->num_rows = sql_affected_rows(mdb);
202 if (mdb->num_rows < 1) {
204 m_msg(file, line, &mdb->errmsg, _("Update failed: affected_rows=%s for %s\n"),
205 edit_uint64(mdb->num_rows, ed1), cmd);
207 // j_msg(file, line, jcr, M_INFO, 0, "%s\n", cmd);
215 /* Utility routine for deletes
217 * Returns: -1 on error
218 * n number of rows affected
221 DeleteDB(const char *file, int line, JCR *jcr, B_DB *mdb, char *cmd)
224 if (sql_query(mdb, cmd)) {
225 m_msg(file, line, &mdb->errmsg, _("delete %s failed:\n%s\n"), cmd, sql_strerror(mdb));
226 j_msg(file, line, jcr, M_ERROR, 0, "%s", mdb->errmsg);
228 j_msg(file, line, jcr, M_INFO, 0, "%s\n", cmd);
233 return sql_affected_rows(mdb);
238 * Get record max. Query is already in mdb->cmd
241 * Returns: -1 on failure
244 int get_sql_record_max(JCR *jcr, B_DB *mdb)
249 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
250 if ((row = sql_fetch_row(mdb)) == NULL) {
251 Mmsg1(&mdb->errmsg, _("error fetching row: %s\n"), sql_strerror(mdb));
254 stat = str_to_int64(row[0]);
256 sql_free_result(mdb);
258 Mmsg1(&mdb->errmsg, _("error fetching row: %s\n"), sql_strerror(mdb));
265 * Return pre-edited error message
267 char *db_strerror(B_DB *mdb)
273 * Lock database, this can be called multiple times by the same
274 * thread without blocking, but must be unlocked the number of
275 * times it was locked.
277 void _db_lock(const char *file, int line, B_DB *mdb)
280 if ((errstat=rwl_writelock(&mdb->lock)) != 0) {
282 e_msg(file, line, M_FATAL, 0, "rwl_writelock failure. stat=%d: ERR=%s\n",
283 errstat, be.bstrerror(errstat));
288 * Unlock the database. This can be called multiple times by the
289 * same thread up to the number of times that thread called
292 void _db_unlock(const char *file, int line, B_DB *mdb)
295 if ((errstat=rwl_writeunlock(&mdb->lock)) != 0) {
297 e_msg(file, line, M_FATAL, 0, "rwl_writeunlock failure. stat=%d: ERR=%s\n",
298 errstat, be.bstrerror(errstat));
303 * Start a transaction. This groups inserts and makes things
304 * much more efficient. Usually started when inserting
307 void db_start_transaction(JCR *jcr, B_DB *mdb)
310 jcr->attr = get_pool_memory(PM_FNAME);
313 jcr->ar = (ATTR_DBR *)malloc(sizeof(ATTR_DBR));
317 if (!mdb->allow_transactions) {
321 /* Allow only 10,000 changes per transaction */
322 if (mdb->transaction && mdb->changes > 10000) {
323 db_end_transaction(jcr, mdb);
325 if (!mdb->transaction) {
326 my_sqlite_query(mdb, "BEGIN"); /* begin transaction */
327 Dmsg0(400, "Start SQLite transaction\n");
328 mdb->transaction = 1;
334 * This is turned off because transactions break
335 * if multiple simultaneous jobs are run.
337 #ifdef HAVE_POSTGRESQL
338 if (!mdb->allow_transactions) {
342 /* Allow only 25,000 changes per transaction */
343 if (mdb->transaction && mdb->changes > 25000) {
344 db_end_transaction(jcr, mdb);
346 if (!mdb->transaction) {
347 db_sql_query(mdb, "BEGIN", NULL, NULL); /* begin transaction */
348 Dmsg0(400, "Start PosgreSQL transaction\n");
349 mdb->transaction = 1;
355 void db_end_transaction(JCR *jcr, B_DB *mdb)
358 * This can be called during thread cleanup and
359 * the db may already be closed. So simply return.
365 if (jcr && jcr->cached_attribute) {
366 Dmsg0(400, "Flush last cached attribute.\n");
367 if (!db_create_file_attributes_record(jcr, mdb, jcr->ar)) {
368 Jmsg1(jcr, M_FATAL, 0, _("Attribute create error. %s"), db_strerror(jcr->db));
370 jcr->cached_attribute = false;
374 if (!mdb->allow_transactions) {
378 if (mdb->transaction) {
379 my_sqlite_query(mdb, "COMMIT"); /* end transaction */
380 mdb->transaction = 0;
381 Dmsg1(400, "End SQLite transaction changes=%d\n", mdb->changes);
387 #ifdef HAVE_POSTGRESQL
388 if (!mdb->allow_transactions) {
392 if (mdb->transaction) {
393 db_sql_query(mdb, "COMMIT", NULL, NULL); /* end transaction */
394 mdb->transaction = 0;
395 Dmsg1(400, "End PostgreSQL transaction changes=%d\n", mdb->changes);
403 * Given a full filename, split it into its path
404 * and filename parts. They are returned in pool memory
405 * in the mdb structure.
407 void split_path_and_file(JCR *jcr, B_DB *mdb, const char *fname)
411 /* Find path without the filename.
412 * I.e. everything after the last / is a "filename".
413 * OK, maybe it is a directory name, but we treat it like
414 * a filename. If we don't find a / then the whole name
415 * must be a path name (e.g. c:).
417 for (p=f=fname; *p; p++) {
418 if (IsPathSeparator(*p)) {
419 f = p; /* set pos of last slash */
422 if (IsPathSeparator(*f)) { /* did we find a slash? */
423 f++; /* yes, point to filename */
424 } else { /* no, whole thing must be path name */
428 /* If filename doesn't exist (i.e. root directory), we
429 * simply create a blank name consisting of a single
430 * space. This makes handling zero length filenames
435 mdb->fname = check_pool_memory_size(mdb->fname, mdb->fnl+1);
436 memcpy(mdb->fname, f, mdb->fnl); /* copy filename */
437 mdb->fname[mdb->fnl] = 0;
443 mdb->pnl = f - fname;
445 mdb->path = check_pool_memory_size(mdb->path, mdb->pnl+1);
446 memcpy(mdb->path, fname, mdb->pnl);
447 mdb->path[mdb->pnl] = 0;
449 Mmsg1(&mdb->errmsg, _("Path length is zero. File=%s\n"), fname);
450 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
455 Dmsg2(500, "split path=%s file=%s\n", mdb->path, mdb->fname);
459 * List dashes as part of header for listing SQL results in a table
462 list_dashes(B_DB *mdb, DB_LIST_HANDLER *send, void *ctx)
467 sql_field_seek(mdb, 0);
469 for (i = 0; i < sql_num_fields(mdb); i++) {
470 field = sql_fetch_field(mdb);
471 for (j = 0; j < (int)field->max_length + 2; j++) {
480 * If full_list is set, we list vertically, otherwise, we
481 * list on one line horizontally.
484 list_result(JCR *jcr, B_DB *mdb, DB_LIST_HANDLER *send, void *ctx, e_list_type type)
488 int i, col_len, max_len = 0;
489 char buf[2000], ewc[30];
491 Dmsg0(800, "list_result starts\n");
492 if (mdb->result == NULL || sql_num_rows(mdb) == 0) {
493 send(ctx, _("No results to list.\n"));
497 Dmsg1(800, "list_result starts looking at %d fields\n", sql_num_fields(mdb));
498 /* determine column display widths */
499 sql_field_seek(mdb, 0);
500 for (i = 0; i < sql_num_fields(mdb); i++) {
501 Dmsg1(800, "list_result processing field %d\n", i);
502 field = sql_fetch_field(mdb);
503 col_len = cstrlen(field->name);
504 if (type == VERT_LIST) {
505 if (col_len > max_len) {
509 if (IS_NUM(field->type) && (int)field->max_length > 0) { /* fixup for commas */
510 field->max_length += (field->max_length - 1) / 3;
512 if (col_len < (int)field->max_length) {
513 col_len = field->max_length;
515 if (col_len < 4 && !IS_NOT_NULL(field->flags)) {
516 col_len = 4; /* 4 = length of the word "NULL" */
518 field->max_length = col_len; /* reset column info */
522 Dmsg0(800, "list_result finished first loop\n");
523 if (type == VERT_LIST) {
527 Dmsg1(800, "list_result starts second loop looking at %d fields\n", sql_num_fields(mdb));
528 list_dashes(mdb, send, ctx);
530 sql_field_seek(mdb, 0);
531 for (i = 0; i < sql_num_fields(mdb); i++) {
532 Dmsg1(800, "list_result looking at field %d\n", i);
533 field = sql_fetch_field(mdb);
534 bsnprintf(buf, sizeof(buf), " %-*s |", (int)field->max_length, field->name);
538 list_dashes(mdb, send, ctx);
540 Dmsg1(800, "list_result starts third loop looking at %d fields\n", sql_num_fields(mdb));
541 while ((row = sql_fetch_row(mdb)) != NULL) {
542 sql_field_seek(mdb, 0);
544 for (i = 0; i < sql_num_fields(mdb); i++) {
545 field = sql_fetch_field(mdb);
546 if (row[i] == NULL) {
547 bsnprintf(buf, sizeof(buf), " %-*s |", (int)field->max_length, "NULL");
548 } else if (IS_NUM(field->type) && !jcr->gui && is_an_integer(row[i])) {
549 bsnprintf(buf, sizeof(buf), " %*s |", (int)field->max_length,
550 add_commas(row[i], ewc));
552 bsnprintf(buf, sizeof(buf), " %-*s |", (int)field->max_length, row[i]);
558 list_dashes(mdb, send, ctx);
563 Dmsg1(800, "list_result starts vertical list at %d fields\n", sql_num_fields(mdb));
564 while ((row = sql_fetch_row(mdb)) != NULL) {
565 sql_field_seek(mdb, 0);
566 for (i = 0; i < sql_num_fields(mdb); i++) {
567 field = sql_fetch_field(mdb);
568 if (row[i] == NULL) {
569 bsnprintf(buf, sizeof(buf), " %*s: %s\n", max_len, field->name, "NULL");
570 } else if (IS_NUM(field->type) && !jcr->gui && is_an_integer(row[i])) {
571 bsnprintf(buf, sizeof(buf), " %*s: %s\n", max_len, field->name,
572 add_commas(row[i], ewc));
574 bsnprintf(buf, sizeof(buf), " %*s: %s\n", max_len, field->name, row[i]);
584 #endif /* HAVE_SQLITE3 || HAVE_MYSQL || HAVE_SQLITE || HAVE_POSTGRESQL*/