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 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)
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
51 uint32_t bacula_db_version = 0;
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 *adb_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)
64 bstrncpy(db_driver, adb_driver, sizeof(db_driver));
66 return db_init_database(jcr, db_name, db_user, db_password, db_address,
67 db_port, db_socket, mult_db_connections);
70 dbid_list::dbid_list()
72 memset(this, 0, sizeof(dbid_list));
74 DBId = (DBId_t *)malloc(max_ids * sizeof(DBId_t));
75 num_ids = num_seen = tot_ids = 0;
79 dbid_list::~dbid_list()
86 * Called here to retrieve an integer from the database
88 static int int_handler(void *ctx, int num_fields, char **row)
90 uint32_t *val = (uint32_t *)ctx;
92 Dmsg1(800, "int_handler starts with row pointing at %x\n", row);
95 Dmsg1(800, "int_handler finds '%s'\n", row[0]);
96 *val = str_to_int64(row[0]);
98 Dmsg0(800, "int_handler finds zero\n");
101 Dmsg0(800, "int_handler finishes\n");
106 * Called here to retrieve a 32/64 bit integer from the database.
107 * The returned integer will be extended to 64 bit.
109 int db_int64_handler(void *ctx, int num_fields, char **row)
111 db_int64_ctx *lctx = (db_int64_ctx *)ctx;
114 lctx->value = str_to_int64(row[0]);
122 /* NOTE!!! The following routines expect that the
123 * calling subroutine sets and clears the mutex
126 /* Check that the tables correspond to the version we want */
127 bool check_tables_version(JCR *jcr, B_DB *mdb)
129 const char *query = "SELECT VersionId FROM Version";
131 bacula_db_version = 0;
132 if (!db_sql_query(mdb, query, int_handler, (void *)&bacula_db_version)) {
133 Jmsg(jcr, M_FATAL, 0, "%s", mdb->errmsg);
136 if (bacula_db_version != BDB_VERSION) {
137 Mmsg(mdb->errmsg, "Version error for database \"%s\". Wanted %d, got %d\n",
138 mdb->db_name, BDB_VERSION, bacula_db_version);
139 Jmsg(jcr, M_FATAL, 0, "%s", mdb->errmsg);
145 /* Utility routine for queries. The database MUST be locked before calling here. */
147 QueryDB(const char *file, int line, JCR *jcr, B_DB *mdb, char *cmd)
151 sql_free_result(mdb);
152 if ((status=sql_query(mdb, cmd)) != 0) {
153 m_msg(file, line, &mdb->errmsg, _("query %s failed:\n%s\n"), cmd, sql_strerror(mdb));
154 j_msg(file, line, jcr, M_FATAL, 0, "%s", mdb->errmsg);
156 j_msg(file, line, jcr, M_INFO, 0, "%s\n", cmd);
161 mdb->result = sql_store_result(mdb);
163 return mdb->result != NULL;
167 * Utility routine to do inserts
168 * Returns: 0 on failure
172 InsertDB(const char *file, int line, JCR *jcr, B_DB *mdb, char *cmd)
174 if (sql_query(mdb, cmd)) {
175 m_msg(file, line, &mdb->errmsg, _("insert %s failed:\n%s\n"), cmd, sql_strerror(mdb));
176 j_msg(file, line, jcr, M_FATAL, 0, "%s", mdb->errmsg);
178 j_msg(file, line, jcr, M_INFO, 0, "%s\n", cmd);
182 if (mdb->have_insert_id) {
183 mdb->num_rows = sql_affected_rows(mdb);
187 if (mdb->num_rows != 1) {
189 m_msg(file, line, &mdb->errmsg, _("Insertion problem: affected_rows=%s\n"),
190 edit_uint64(mdb->num_rows, ed1));
192 j_msg(file, line, jcr, M_INFO, 0, "%s\n", cmd);
200 /* Utility routine for updates.
201 * Returns: 0 on failure
205 UpdateDB(const char *file, int line, JCR *jcr, B_DB *mdb, char *cmd)
208 if (sql_query(mdb, cmd)) {
209 m_msg(file, line, &mdb->errmsg, _("update %s failed:\n%s\n"), cmd, sql_strerror(mdb));
210 j_msg(file, line, jcr, M_ERROR, 0, "%s", mdb->errmsg);
212 j_msg(file, line, jcr, M_INFO, 0, "%s\n", cmd);
216 mdb->num_rows = sql_affected_rows(mdb);
217 if (mdb->num_rows < 1) {
219 m_msg(file, line, &mdb->errmsg, _("Update failed: affected_rows=%s for %s\n"),
220 edit_uint64(mdb->num_rows, ed1), cmd);
222 // j_msg(file, line, jcr, M_INFO, 0, "%s\n", cmd);
230 /* Utility routine for deletes
232 * Returns: -1 on error
233 * n number of rows affected
236 DeleteDB(const char *file, int line, JCR *jcr, B_DB *mdb, char *cmd)
239 if (sql_query(mdb, cmd)) {
240 m_msg(file, line, &mdb->errmsg, _("delete %s failed:\n%s\n"), cmd, sql_strerror(mdb));
241 j_msg(file, line, jcr, M_ERROR, 0, "%s", mdb->errmsg);
243 j_msg(file, line, jcr, M_INFO, 0, "%s\n", cmd);
248 return sql_affected_rows(mdb);
253 * Get record max. Query is already in mdb->cmd
256 * Returns: -1 on failure
259 int get_sql_record_max(JCR *jcr, B_DB *mdb)
264 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
265 if ((row = sql_fetch_row(mdb)) == NULL) {
266 Mmsg1(&mdb->errmsg, _("error fetching row: %s\n"), sql_strerror(mdb));
269 stat = str_to_int64(row[0]);
271 sql_free_result(mdb);
273 Mmsg1(&mdb->errmsg, _("error fetching row: %s\n"), sql_strerror(mdb));
280 * Return pre-edited error message
282 char *db_strerror(B_DB *mdb)
288 * Lock database, this can be called multiple times by the same
289 * thread without blocking, but must be unlocked the number of
290 * times it was locked.
292 void _db_lock(const char *file, int line, B_DB *mdb)
295 if ((errstat=rwl_writelock(&mdb->lock)) != 0) {
297 e_msg(file, line, M_FATAL, 0, "rwl_writelock failure. stat=%d: ERR=%s\n",
298 errstat, be.bstrerror(errstat));
303 * Unlock the database. This can be called multiple times by the
304 * same thread up to the number of times that thread called
307 void _db_unlock(const char *file, int line, B_DB *mdb)
310 if ((errstat=rwl_writeunlock(&mdb->lock)) != 0) {
312 e_msg(file, line, M_FATAL, 0, "rwl_writeunlock failure. stat=%d: ERR=%s\n",
313 errstat, be.bstrerror(errstat));
318 * Start a transaction. This groups inserts and makes things
319 * much more efficient. Usually started when inserting
322 void db_start_transaction(JCR *jcr, B_DB *mdb)
325 jcr->attr = get_pool_memory(PM_FNAME);
328 jcr->ar = (ATTR_DBR *)malloc(sizeof(ATTR_DBR));
332 if (!mdb->allow_transactions) {
336 /* Allow only 10,000 changes per transaction */
337 if (mdb->transaction && mdb->changes > 10000) {
338 db_end_transaction(jcr, mdb);
340 if (!mdb->transaction) {
341 my_sqlite_query(mdb, "BEGIN"); /* begin transaction */
342 Dmsg0(400, "Start SQLite transaction\n");
343 mdb->transaction = 1;
349 * This is turned off because transactions break
350 * if multiple simultaneous jobs are run.
352 #ifdef HAVE_POSTGRESQL
353 if (!mdb->allow_transactions) {
357 /* Allow only 25,000 changes per transaction */
358 if (mdb->transaction && mdb->changes > 25000) {
359 db_end_transaction(jcr, mdb);
361 if (!mdb->transaction) {
362 db_sql_query(mdb, "BEGIN", NULL, NULL); /* begin transaction */
363 Dmsg0(400, "Start PosgreSQL transaction\n");
364 mdb->transaction = 1;
370 void db_end_transaction(JCR *jcr, B_DB *mdb)
373 * This can be called during thread cleanup and
374 * the db may already be closed. So simply return.
380 if (jcr && jcr->cached_attribute) {
381 Dmsg0(400, "Flush last cached attribute.\n");
382 if (!db_create_file_attributes_record(jcr, mdb, jcr->ar)) {
383 Jmsg1(jcr, M_FATAL, 0, _("Attribute create error. %s"), db_strerror(jcr->db));
385 jcr->cached_attribute = false;
389 if (!mdb->allow_transactions) {
393 if (mdb->transaction) {
394 my_sqlite_query(mdb, "COMMIT"); /* end transaction */
395 mdb->transaction = 0;
396 Dmsg1(400, "End SQLite transaction changes=%d\n", mdb->changes);
402 #ifdef HAVE_POSTGRESQL
403 if (!mdb->allow_transactions) {
407 if (mdb->transaction) {
408 db_sql_query(mdb, "COMMIT", NULL, NULL); /* end transaction */
409 mdb->transaction = 0;
410 Dmsg1(400, "End PostgreSQL transaction changes=%d\n", mdb->changes);
418 * Given a full filename, split it into its path
419 * and filename parts. They are returned in pool memory
420 * in the mdb structure.
422 void split_path_and_file(JCR *jcr, B_DB *mdb, const char *fname)
426 /* Find path without the filename.
427 * I.e. everything after the last / is a "filename".
428 * OK, maybe it is a directory name, but we treat it like
429 * a filename. If we don't find a / then the whole name
430 * must be a path name (e.g. c:).
432 for (p=f=fname; *p; p++) {
433 if (IsPathSeparator(*p)) {
434 f = p; /* set pos of last slash */
437 if (IsPathSeparator(*f)) { /* did we find a slash? */
438 f++; /* yes, point to filename */
439 } else { /* no, whole thing must be path name */
443 /* If filename doesn't exist (i.e. root directory), we
444 * simply create a blank name consisting of a single
445 * space. This makes handling zero length filenames
450 mdb->fname = check_pool_memory_size(mdb->fname, mdb->fnl+1);
451 memcpy(mdb->fname, f, mdb->fnl); /* copy filename */
452 mdb->fname[mdb->fnl] = 0;
458 mdb->pnl = f - fname;
460 mdb->path = check_pool_memory_size(mdb->path, mdb->pnl+1);
461 memcpy(mdb->path, fname, mdb->pnl);
462 mdb->path[mdb->pnl] = 0;
464 Mmsg1(&mdb->errmsg, _("Path length is zero. File=%s\n"), fname);
465 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
470 Dmsg2(500, "split path=%s file=%s\n", mdb->path, mdb->fname);
474 * List dashes as part of header for listing SQL results in a table
477 list_dashes(B_DB *mdb, DB_LIST_HANDLER *send, void *ctx)
482 sql_field_seek(mdb, 0);
484 for (i = 0; i < sql_num_fields(mdb); i++) {
485 field = sql_fetch_field(mdb);
486 for (j = 0; j < (int)field->max_length + 2; j++) {
495 * If full_list is set, we list vertically, otherwise, we
496 * list on one line horizontally.
499 list_result(JCR *jcr, B_DB *mdb, DB_LIST_HANDLER *send, void *ctx, e_list_type type)
503 int i, col_len, max_len = 0;
504 char buf[2000], ewc[30];
506 Dmsg0(800, "list_result starts\n");
507 if (mdb->result == NULL || sql_num_rows(mdb) == 0) {
508 send(ctx, _("No results to list.\n"));
512 Dmsg1(800, "list_result starts looking at %d fields\n", sql_num_fields(mdb));
513 /* determine column display widths */
514 sql_field_seek(mdb, 0);
515 for (i = 0; i < sql_num_fields(mdb); i++) {
516 Dmsg1(800, "list_result processing field %d\n", i);
517 field = sql_fetch_field(mdb);
518 col_len = cstrlen(field->name);
519 if (type == VERT_LIST) {
520 if (col_len > max_len) {
524 if (IS_NUM(field->type) && (int)field->max_length > 0) { /* fixup for commas */
525 field->max_length += (field->max_length - 1) / 3;
527 if (col_len < (int)field->max_length) {
528 col_len = field->max_length;
530 if (col_len < 4 && !IS_NOT_NULL(field->flags)) {
531 col_len = 4; /* 4 = length of the word "NULL" */
533 field->max_length = col_len; /* reset column info */
537 Dmsg0(800, "list_result finished first loop\n");
538 if (type == VERT_LIST) {
542 Dmsg1(800, "list_result starts second loop looking at %d fields\n", sql_num_fields(mdb));
543 list_dashes(mdb, send, ctx);
545 sql_field_seek(mdb, 0);
546 for (i = 0; i < sql_num_fields(mdb); i++) {
547 Dmsg1(800, "list_result looking at field %d\n", i);
548 field = sql_fetch_field(mdb);
549 bsnprintf(buf, sizeof(buf), " %-*s |", (int)field->max_length, field->name);
553 list_dashes(mdb, send, ctx);
555 Dmsg1(800, "list_result starts third loop looking at %d fields\n", sql_num_fields(mdb));
556 while ((row = sql_fetch_row(mdb)) != NULL) {
557 sql_field_seek(mdb, 0);
559 for (i = 0; i < sql_num_fields(mdb); i++) {
560 field = sql_fetch_field(mdb);
561 if (row[i] == NULL) {
562 bsnprintf(buf, sizeof(buf), " %-*s |", (int)field->max_length, "NULL");
563 } else if (IS_NUM(field->type) && !jcr->gui && is_an_integer(row[i])) {
564 bsnprintf(buf, sizeof(buf), " %*s |", (int)field->max_length,
565 add_commas(row[i], ewc));
567 bsnprintf(buf, sizeof(buf), " %-*s |", (int)field->max_length, row[i]);
573 list_dashes(mdb, send, ctx);
578 Dmsg1(800, "list_result starts vertical list at %d fields\n", sql_num_fields(mdb));
579 while ((row = sql_fetch_row(mdb)) != NULL) {
580 sql_field_seek(mdb, 0);
581 for (i = 0; i < sql_num_fields(mdb); i++) {
582 field = sql_fetch_field(mdb);
583 if (row[i] == NULL) {
584 bsnprintf(buf, sizeof(buf), " %*s: %s\n", max_len, field->name, "NULL");
585 } else if (IS_NUM(field->type) && !jcr->gui && is_an_integer(row[i])) {
586 bsnprintf(buf, sizeof(buf), " %*s: %s\n", max_len, field->name,
587 add_commas(row[i], ewc));
589 bsnprintf(buf, sizeof(buf), " %*s: %s\n", max_len, field->name, row[i]);
599 #endif /* HAVE_SQLITE3 || HAVE_MYSQL || HAVE_SQLITE || HAVE_POSTGRESQL*/