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 Mmsg(mdb->errmsg, "Database not created or server not running.\n");
119 Jmsg(jcr, M_FATAL, 0, "%s", mdb->errmsg);
122 if (bacula_db_version != BDB_VERSION) {
123 Mmsg(mdb->errmsg, "Version error for database \"%s\". Wanted %d, got %d\n",
124 mdb->db_name, BDB_VERSION, bacula_db_version);
125 Jmsg(jcr, M_FATAL, 0, "%s", mdb->errmsg);
131 /* Utility routine for queries. The database MUST be locked before calling here. */
133 QueryDB(const char *file, int line, JCR *jcr, B_DB *mdb, char *cmd)
137 sql_free_result(mdb);
138 if ((status=sql_query(mdb, cmd)) != 0) {
139 m_msg(file, line, &mdb->errmsg, _("query %s failed:\n%s\n"), cmd, sql_strerror(mdb));
140 j_msg(file, line, jcr, M_FATAL, 0, "%s", mdb->errmsg);
142 j_msg(file, line, jcr, M_INFO, 0, "%s\n", cmd);
147 mdb->result = sql_store_result(mdb);
149 return mdb->result != NULL;
153 * Utility routine to do inserts
154 * Returns: 0 on failure
158 InsertDB(const char *file, int line, JCR *jcr, B_DB *mdb, char *cmd)
160 if (sql_query(mdb, cmd)) {
161 m_msg(file, line, &mdb->errmsg, _("insert %s failed:\n%s\n"), cmd, sql_strerror(mdb));
162 j_msg(file, line, jcr, M_FATAL, 0, "%s", mdb->errmsg);
164 j_msg(file, line, jcr, M_INFO, 0, "%s\n", cmd);
168 if (mdb->have_insert_id) {
169 mdb->num_rows = sql_affected_rows(mdb);
173 if (mdb->num_rows != 1) {
175 m_msg(file, line, &mdb->errmsg, _("Insertion problem: affected_rows=%s\n"),
176 edit_uint64(mdb->num_rows, ed1));
178 j_msg(file, line, jcr, M_INFO, 0, "%s\n", cmd);
186 /* Utility routine for updates.
187 * Returns: 0 on failure
191 UpdateDB(const char *file, int line, JCR *jcr, B_DB *mdb, char *cmd)
194 if (sql_query(mdb, cmd)) {
195 m_msg(file, line, &mdb->errmsg, _("update %s failed:\n%s\n"), cmd, sql_strerror(mdb));
196 j_msg(file, line, jcr, M_ERROR, 0, "%s", mdb->errmsg);
198 j_msg(file, line, jcr, M_INFO, 0, "%s\n", cmd);
202 mdb->num_rows = sql_affected_rows(mdb);
203 if (mdb->num_rows < 1) {
205 m_msg(file, line, &mdb->errmsg, _("Update failed: affected_rows=%s for %s\n"),
206 edit_uint64(mdb->num_rows, ed1), cmd);
208 // j_msg(file, line, jcr, M_INFO, 0, "%s\n", cmd);
216 /* Utility routine for deletes
218 * Returns: -1 on error
219 * n number of rows affected
222 DeleteDB(const char *file, int line, JCR *jcr, B_DB *mdb, char *cmd)
225 if (sql_query(mdb, cmd)) {
226 m_msg(file, line, &mdb->errmsg, _("delete %s failed:\n%s\n"), cmd, sql_strerror(mdb));
227 j_msg(file, line, jcr, M_ERROR, 0, "%s", mdb->errmsg);
229 j_msg(file, line, jcr, M_INFO, 0, "%s\n", cmd);
234 return sql_affected_rows(mdb);
239 * Get record max. Query is already in mdb->cmd
242 * Returns: -1 on failure
245 int get_sql_record_max(JCR *jcr, B_DB *mdb)
250 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
251 if ((row = sql_fetch_row(mdb)) == NULL) {
252 Mmsg1(&mdb->errmsg, _("error fetching row: %s\n"), sql_strerror(mdb));
255 stat = str_to_int64(row[0]);
257 sql_free_result(mdb);
259 Mmsg1(&mdb->errmsg, _("error fetching row: %s\n"), sql_strerror(mdb));
266 * Return pre-edited error message
268 char *db_strerror(B_DB *mdb)
274 * Lock database, this can be called multiple times by the same
275 * thread without blocking, but must be unlocked the number of
276 * times it was locked.
278 void _db_lock(const char *file, int line, B_DB *mdb)
281 if ((errstat=rwl_writelock(&mdb->lock)) != 0) {
283 e_msg(file, line, M_FATAL, 0, "rwl_writelock failure. stat=%d: ERR=%s\n",
284 errstat, be.bstrerror(errstat));
289 * Unlock the database. This can be called multiple times by the
290 * same thread up to the number of times that thread called
293 void _db_unlock(const char *file, int line, B_DB *mdb)
296 if ((errstat=rwl_writeunlock(&mdb->lock)) != 0) {
298 e_msg(file, line, M_FATAL, 0, "rwl_writeunlock failure. stat=%d: ERR=%s\n",
299 errstat, be.bstrerror(errstat));
304 * Start a transaction. This groups inserts and makes things
305 * much more efficient. Usually started when inserting
308 void db_start_transaction(JCR *jcr, B_DB *mdb)
311 jcr->attr = get_pool_memory(PM_FNAME);
314 jcr->ar = (ATTR_DBR *)malloc(sizeof(ATTR_DBR));
318 if (!mdb->allow_transactions) {
322 /* Allow only 10,000 changes per transaction */
323 if (mdb->transaction && mdb->changes > 10000) {
324 db_end_transaction(jcr, mdb);
326 if (!mdb->transaction) {
327 my_sqlite_query(mdb, "BEGIN"); /* begin transaction */
328 Dmsg0(400, "Start SQLite transaction\n");
329 mdb->transaction = 1;
335 * This is turned off because transactions break
336 * if multiple simultaneous jobs are run.
338 #ifdef HAVE_POSTGRESQL
339 if (!mdb->allow_transactions) {
343 /* Allow only 25,000 changes per transaction */
344 if (mdb->transaction && mdb->changes > 25000) {
345 db_end_transaction(jcr, mdb);
347 if (!mdb->transaction) {
348 db_sql_query(mdb, "BEGIN", NULL, NULL); /* begin transaction */
349 Dmsg0(400, "Start PosgreSQL transaction\n");
350 mdb->transaction = 1;
356 void db_end_transaction(JCR *jcr, B_DB *mdb)
359 * This can be called during thread cleanup and
360 * the db may already be closed. So simply return.
366 if (jcr && jcr->cached_attribute) {
367 Dmsg0(400, "Flush last cached attribute.\n");
368 if (!db_create_file_attributes_record(jcr, mdb, jcr->ar)) {
369 Jmsg1(jcr, M_FATAL, 0, _("Attribute create error. %s"), db_strerror(jcr->db));
371 jcr->cached_attribute = false;
375 if (!mdb->allow_transactions) {
379 if (mdb->transaction) {
380 my_sqlite_query(mdb, "COMMIT"); /* end transaction */
381 mdb->transaction = 0;
382 Dmsg1(400, "End SQLite transaction changes=%d\n", mdb->changes);
388 #ifdef HAVE_POSTGRESQL
389 if (!mdb->allow_transactions) {
393 if (mdb->transaction) {
394 db_sql_query(mdb, "COMMIT", NULL, NULL); /* end transaction */
395 mdb->transaction = 0;
396 Dmsg1(400, "End PostgreSQL transaction changes=%d\n", mdb->changes);
404 * Given a full filename, split it into its path
405 * and filename parts. They are returned in pool memory
406 * in the mdb structure.
408 void split_path_and_file(JCR *jcr, B_DB *mdb, const char *fname)
412 /* Find path without the filename.
413 * I.e. everything after the last / is a "filename".
414 * OK, maybe it is a directory name, but we treat it like
415 * a filename. If we don't find a / then the whole name
416 * must be a path name (e.g. c:).
418 for (p=f=fname; *p; p++) {
419 if (IsPathSeparator(*p)) {
420 f = p; /* set pos of last slash */
423 if (IsPathSeparator(*f)) { /* did we find a slash? */
424 f++; /* yes, point to filename */
425 } else { /* no, whole thing must be path name */
429 /* If filename doesn't exist (i.e. root directory), we
430 * simply create a blank name consisting of a single
431 * space. This makes handling zero length filenames
436 mdb->fname = check_pool_memory_size(mdb->fname, mdb->fnl+1);
437 memcpy(mdb->fname, f, mdb->fnl); /* copy filename */
438 mdb->fname[mdb->fnl] = 0;
444 mdb->pnl = f - fname;
446 mdb->path = check_pool_memory_size(mdb->path, mdb->pnl+1);
447 memcpy(mdb->path, fname, mdb->pnl);
448 mdb->path[mdb->pnl] = 0;
450 Mmsg1(&mdb->errmsg, _("Path length is zero. File=%s\n"), fname);
451 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
456 Dmsg2(500, "split path=%s file=%s\n", mdb->path, mdb->fname);
460 * List dashes as part of header for listing SQL results in a table
463 list_dashes(B_DB *mdb, DB_LIST_HANDLER *send, void *ctx)
468 sql_field_seek(mdb, 0);
470 for (i = 0; i < sql_num_fields(mdb); i++) {
471 field = sql_fetch_field(mdb);
472 for (j = 0; j < (int)field->max_length + 2; j++) {
481 * If full_list is set, we list vertically, otherwise, we
482 * list on one line horizontally.
485 list_result(JCR *jcr, B_DB *mdb, DB_LIST_HANDLER *send, void *ctx, e_list_type type)
489 int i, col_len, max_len = 0;
490 char buf[2000], ewc[30];
492 Dmsg0(800, "list_result starts\n");
493 if (mdb->result == NULL || sql_num_rows(mdb) == 0) {
494 send(ctx, _("No results to list.\n"));
498 Dmsg1(800, "list_result starts looking at %d fields\n", sql_num_fields(mdb));
499 /* determine column display widths */
500 sql_field_seek(mdb, 0);
501 for (i = 0; i < sql_num_fields(mdb); i++) {
502 Dmsg1(800, "list_result processing field %d\n", i);
503 field = sql_fetch_field(mdb);
504 col_len = cstrlen(field->name);
505 if (type == VERT_LIST) {
506 if (col_len > max_len) {
510 if (IS_NUM(field->type) && (int)field->max_length > 0) { /* fixup for commas */
511 field->max_length += (field->max_length - 1) / 3;
513 if (col_len < (int)field->max_length) {
514 col_len = field->max_length;
516 if (col_len < 4 && !IS_NOT_NULL(field->flags)) {
517 col_len = 4; /* 4 = length of the word "NULL" */
519 field->max_length = col_len; /* reset column info */
523 Dmsg0(800, "list_result finished first loop\n");
524 if (type == VERT_LIST) {
528 Dmsg1(800, "list_result starts second loop looking at %d fields\n", sql_num_fields(mdb));
529 list_dashes(mdb, send, ctx);
531 sql_field_seek(mdb, 0);
532 for (i = 0; i < sql_num_fields(mdb); i++) {
533 Dmsg1(800, "list_result looking at field %d\n", i);
534 field = sql_fetch_field(mdb);
535 bsnprintf(buf, sizeof(buf), " %-*s |", (int)field->max_length, field->name);
539 list_dashes(mdb, send, ctx);
541 Dmsg1(800, "list_result starts third loop looking at %d fields\n", sql_num_fields(mdb));
542 while ((row = sql_fetch_row(mdb)) != NULL) {
543 sql_field_seek(mdb, 0);
545 for (i = 0; i < sql_num_fields(mdb); i++) {
546 field = sql_fetch_field(mdb);
547 if (row[i] == NULL) {
548 bsnprintf(buf, sizeof(buf), " %-*s |", (int)field->max_length, "NULL");
549 } else if (IS_NUM(field->type) && !jcr->gui && is_an_integer(row[i])) {
550 bsnprintf(buf, sizeof(buf), " %*s |", (int)field->max_length,
551 add_commas(row[i], ewc));
553 bsnprintf(buf, sizeof(buf), " %-*s |", (int)field->max_length, row[i]);
559 list_dashes(mdb, send, ctx);
564 Dmsg1(800, "list_result starts vertical list at %d fields\n", sql_num_fields(mdb));
565 while ((row = sql_fetch_row(mdb)) != NULL) {
566 sql_field_seek(mdb, 0);
567 for (i = 0; i < sql_num_fields(mdb); i++) {
568 field = sql_fetch_field(mdb);
569 if (row[i] == NULL) {
570 bsnprintf(buf, sizeof(buf), " %*s: %s\n", max_len, field->name, "NULL");
571 } else if (IS_NUM(field->type) && !jcr->gui && is_an_integer(row[i])) {
572 bsnprintf(buf, sizeof(buf), " %*s: %s\n", max_len, field->name,
573 add_commas(row[i], ewc));
575 bsnprintf(buf, sizeof(buf), " %*s: %s\n", max_len, field->name, row[i]);
585 #endif /* HAVE_SQLITE3 || HAVE_MYSQL || HAVE_SQLITE || HAVE_POSTGRESQL*/