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 plus additions
11 that are listed in the file LICENSE.
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);
56 * Called here to retrieve an integer from the database
58 static int int_handler(void *ctx, int num_fields, char **row)
60 uint32_t *val = (uint32_t *)ctx;
62 Dmsg1(800, "int_handler starts with row pointing at %x\n", row);
65 Dmsg1(800, "int_handler finds '%s'\n", row[0]);
66 *val = str_to_int64(row[0]);
68 Dmsg0(800, "int_handler finds zero\n");
71 Dmsg0(800, "int_handler finishes\n");
76 * Called here to retrieve a 32/64 bit integer from the database.
77 * The returned integer will be extended to 64 bit.
79 int db_int64_handler(void *ctx, int num_fields, char **row)
81 db_int64_ctx *lctx = (db_int64_ctx *)ctx;
84 lctx->value = str_to_int64(row[0]);
92 /* NOTE!!! The following routines expect that the
93 * calling subroutine sets and clears the mutex
96 /* Check that the tables correspond to the version we want */
97 bool check_tables_version(JCR *jcr, B_DB *mdb)
99 const char *query = "SELECT VersionId FROM Version";
101 bacula_db_version = 0;
102 if (!db_sql_query(mdb, query, int_handler, (void *)&bacula_db_version)) {
103 Mmsg(mdb->errmsg, "Database not created or server not running.\n");
104 Jmsg(jcr, M_FATAL, 0, "%s", mdb->errmsg);
107 if (bacula_db_version != BDB_VERSION) {
108 Mmsg(mdb->errmsg, "Version error for database \"%s\". Wanted %d, got %d\n",
109 mdb->db_name, BDB_VERSION, bacula_db_version);
110 Jmsg(jcr, M_FATAL, 0, "%s", mdb->errmsg);
116 /* Utility routine for queries. The database MUST be locked before calling here. */
118 QueryDB(const char *file, int line, JCR *jcr, B_DB *mdb, char *cmd)
121 if ((status=sql_query(mdb, cmd)) != 0) {
122 m_msg(file, line, &mdb->errmsg, _("query %s failed:\n%s\n"), cmd, sql_strerror(mdb));
123 j_msg(file, line, jcr, M_FATAL, 0, "%s", mdb->errmsg);
125 j_msg(file, line, jcr, M_INFO, 0, "%s\n", cmd);
130 mdb->result = sql_store_result(mdb);
132 return mdb->result != NULL;
136 * Utility routine to do inserts
137 * Returns: 0 on failure
141 InsertDB(const char *file, int line, JCR *jcr, B_DB *mdb, char *cmd)
143 if (sql_query(mdb, cmd)) {
144 m_msg(file, line, &mdb->errmsg, _("insert %s failed:\n%s\n"), cmd, sql_strerror(mdb));
145 j_msg(file, line, jcr, M_FATAL, 0, "%s", mdb->errmsg);
147 j_msg(file, line, jcr, M_INFO, 0, "%s\n", cmd);
151 if (mdb->have_insert_id) {
152 mdb->num_rows = sql_affected_rows(mdb);
156 if (mdb->num_rows != 1) {
158 m_msg(file, line, &mdb->errmsg, _("Insertion problem: affected_rows=%s\n"),
159 edit_uint64(mdb->num_rows, ed1));
161 j_msg(file, line, jcr, M_INFO, 0, "%s\n", cmd);
169 /* Utility routine for updates.
170 * Returns: 0 on failure
174 UpdateDB(const char *file, int line, JCR *jcr, B_DB *mdb, char *cmd)
177 if (sql_query(mdb, cmd)) {
178 m_msg(file, line, &mdb->errmsg, _("update %s failed:\n%s\n"), cmd, sql_strerror(mdb));
179 j_msg(file, line, jcr, M_ERROR, 0, "%s", mdb->errmsg);
181 j_msg(file, line, jcr, M_INFO, 0, "%s\n", cmd);
185 mdb->num_rows = sql_affected_rows(mdb);
186 if (mdb->num_rows < 1) {
188 m_msg(file, line, &mdb->errmsg, _("Update problem: affected_rows=%s\n"),
189 edit_uint64(mdb->num_rows, ed1));
191 // j_msg(file, line, jcr, M_INFO, 0, "%s\n", cmd);
199 /* Utility routine for deletes
201 * Returns: -1 on error
202 * n number of rows affected
205 DeleteDB(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, _("delete %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);
217 return sql_affected_rows(mdb);
222 * Get record max. Query is already in mdb->cmd
225 * Returns: -1 on failure
228 int get_sql_record_max(JCR *jcr, B_DB *mdb)
233 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
234 if ((row = sql_fetch_row(mdb)) == NULL) {
235 Mmsg1(&mdb->errmsg, _("error fetching row: %s\n"), sql_strerror(mdb));
238 stat = str_to_int64(row[0]);
240 sql_free_result(mdb);
242 Mmsg1(&mdb->errmsg, _("error fetching row: %s\n"), sql_strerror(mdb));
249 * Return pre-edited error message
251 char *db_strerror(B_DB *mdb)
257 * Lock database, this can be called multiple times by the same
258 * thread without blocking, but must be unlocked the number of
259 * times it was locked.
261 void _db_lock(const char *file, int line, B_DB *mdb)
264 if ((errstat=rwl_writelock(&mdb->lock)) != 0) {
266 e_msg(file, line, M_FATAL, 0, "rwl_writelock failure. stat=%d: ERR=%s\n",
267 errstat, be.strerror(errstat));
272 * Unlock the database. This can be called multiple times by the
273 * same thread up to the number of times that thread called
276 void _db_unlock(const char *file, int line, B_DB *mdb)
279 if ((errstat=rwl_writeunlock(&mdb->lock)) != 0) {
281 e_msg(file, line, M_FATAL, 0, "rwl_writeunlock failure. stat=%d: ERR=%s\n",
282 errstat, be.strerror(errstat));
287 * Start a transaction. This groups inserts and makes things
288 * much more efficient. Usually started when inserting
291 void db_start_transaction(JCR *jcr, B_DB *mdb)
294 jcr->attr = get_pool_memory(PM_FNAME);
297 jcr->ar = (ATTR_DBR *)malloc(sizeof(ATTR_DBR));
301 if (!mdb->allow_transactions) {
305 /* Allow only 10,000 changes per transaction */
306 if (mdb->transaction && mdb->changes > 10000) {
307 db_end_transaction(jcr, mdb);
309 if (!mdb->transaction) {
310 my_sqlite_query(mdb, "BEGIN"); /* begin transaction */
311 Dmsg0(400, "Start SQLite transaction\n");
312 mdb->transaction = 1;
318 * This is turned off because transactions break
319 * if multiple simultaneous jobs are run.
321 #ifdef HAVE_POSTGRESQL
322 if (!mdb->allow_transactions) {
326 /* Allow only 25,000 changes per transaction */
327 if (mdb->transaction && mdb->changes > 25000) {
328 db_end_transaction(jcr, mdb);
330 if (!mdb->transaction) {
331 db_sql_query(mdb, "BEGIN", NULL, NULL); /* begin transaction */
332 Dmsg0(400, "Start PosgreSQL transaction\n");
333 mdb->transaction = 1;
339 void db_end_transaction(JCR *jcr, B_DB *mdb)
342 * This can be called during thread cleanup and
343 * the db may already be closed. So simply return.
349 if (jcr && jcr->cached_attribute) {
350 Dmsg0(400, "Flush last cached attribute.\n");
351 if (!db_create_file_attributes_record(jcr, mdb, jcr->ar)) {
352 Jmsg1(jcr, M_FATAL, 0, _("Attribute create error. %s"), db_strerror(jcr->db));
354 jcr->cached_attribute = false;
358 if (!mdb->allow_transactions) {
362 if (mdb->transaction) {
363 my_sqlite_query(mdb, "COMMIT"); /* end transaction */
364 mdb->transaction = 0;
365 Dmsg1(400, "End SQLite transaction changes=%d\n", mdb->changes);
371 #ifdef HAVE_POSTGRESQL
372 if (!mdb->allow_transactions) {
376 if (mdb->transaction) {
377 db_sql_query(mdb, "COMMIT", NULL, NULL); /* end transaction */
378 mdb->transaction = 0;
379 Dmsg1(400, "End PostgreSQL transaction changes=%d\n", mdb->changes);
387 * Given a full filename, split it into its path
388 * and filename parts. They are returned in pool memory
389 * in the mdb structure.
391 void split_path_and_file(JCR *jcr, B_DB *mdb, const char *fname)
395 /* Find path without the filename.
396 * I.e. everything after the last / is a "filename".
397 * OK, maybe it is a directory name, but we treat it like
398 * a filename. If we don't find a / then the whole name
399 * must be a path name (e.g. c:).
401 for (p=f=fname; *p; p++) {
402 if (IsPathSeparator(*p)) {
403 f = p; /* set pos of last slash */
406 if (IsPathSeparator(*f)) { /* did we find a slash? */
407 f++; /* yes, point to filename */
408 } else { /* no, whole thing must be path name */
412 /* If filename doesn't exist (i.e. root directory), we
413 * simply create a blank name consisting of a single
414 * space. This makes handling zero length filenames
419 mdb->fname = check_pool_memory_size(mdb->fname, mdb->fnl+1);
420 memcpy(mdb->fname, f, mdb->fnl); /* copy filename */
421 mdb->fname[mdb->fnl] = 0;
427 mdb->pnl = f - fname;
429 mdb->path = check_pool_memory_size(mdb->path, mdb->pnl+1);
430 memcpy(mdb->path, fname, mdb->pnl);
431 mdb->path[mdb->pnl] = 0;
433 Mmsg1(&mdb->errmsg, _("Path length is zero. File=%s\n"), fname);
434 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
439 Dmsg2(500, "split path=%s file=%s\n", mdb->path, mdb->fname);
443 * List dashes as part of header for listing SQL results in a table
446 list_dashes(B_DB *mdb, DB_LIST_HANDLER *send, void *ctx)
451 sql_field_seek(mdb, 0);
453 for (i = 0; i < sql_num_fields(mdb); i++) {
454 field = sql_fetch_field(mdb);
455 for (j = 0; j < (int)field->max_length + 2; j++) {
464 * If full_list is set, we list vertically, otherwise, we
465 * list on one line horizontally.
468 list_result(JCR *jcr, B_DB *mdb, DB_LIST_HANDLER *send, void *ctx, e_list_type type)
472 int i, col_len, max_len = 0;
473 char buf[2000], ewc[30];
475 Dmsg0(800, "list_result starts\n");
476 if (mdb->result == NULL || sql_num_rows(mdb) == 0) {
477 send(ctx, _("No results to list.\n"));
481 Dmsg1(800, "list_result starts looking at %d fields\n", sql_num_fields(mdb));
482 /* determine column display widths */
483 sql_field_seek(mdb, 0);
484 for (i = 0; i < sql_num_fields(mdb); i++) {
485 Dmsg1(800, "list_result processing field %d\n", i);
486 field = sql_fetch_field(mdb);
487 col_len = cstrlen(field->name);
488 if (type == VERT_LIST) {
489 if (col_len > max_len) {
493 if (IS_NUM(field->type) && (int)field->max_length > 0) { /* fixup for commas */
494 field->max_length += (field->max_length - 1) / 3;
496 if (col_len < (int)field->max_length) {
497 col_len = field->max_length;
499 if (col_len < 4 && !IS_NOT_NULL(field->flags)) {
500 col_len = 4; /* 4 = length of the word "NULL" */
502 field->max_length = col_len; /* reset column info */
506 Dmsg0(800, "list_result finished first loop\n");
507 if (type == VERT_LIST) {
511 Dmsg1(800, "list_result starts second loop looking at %d fields\n", sql_num_fields(mdb));
512 list_dashes(mdb, send, ctx);
514 sql_field_seek(mdb, 0);
515 for (i = 0; i < sql_num_fields(mdb); i++) {
516 Dmsg1(800, "list_result looking at field %d\n", i);
517 field = sql_fetch_field(mdb);
518 bsnprintf(buf, sizeof(buf), " %-*s |", (int)field->max_length, field->name);
522 list_dashes(mdb, send, ctx);
524 Dmsg1(800, "list_result starts third loop looking at %d fields\n", sql_num_fields(mdb));
525 while ((row = sql_fetch_row(mdb)) != NULL) {
526 sql_field_seek(mdb, 0);
528 for (i = 0; i < sql_num_fields(mdb); i++) {
529 field = sql_fetch_field(mdb);
530 if (row[i] == NULL) {
531 bsnprintf(buf, sizeof(buf), " %-*s |", (int)field->max_length, "NULL");
532 } else if (IS_NUM(field->type) && !jcr->gui && is_an_integer(row[i])) {
533 bsnprintf(buf, sizeof(buf), " %*s |", (int)field->max_length,
534 add_commas(row[i], ewc));
536 bsnprintf(buf, sizeof(buf), " %-*s |", (int)field->max_length, row[i]);
542 list_dashes(mdb, send, ctx);
547 Dmsg1(800, "list_result starts vertical list at %d fields\n", sql_num_fields(mdb));
548 while ((row = sql_fetch_row(mdb)) != NULL) {
549 sql_field_seek(mdb, 0);
550 for (i = 0; i < sql_num_fields(mdb); i++) {
551 field = sql_fetch_field(mdb);
552 if (row[i] == NULL) {
553 bsnprintf(buf, sizeof(buf), " %*s: %s\n", max_len, field->name, "NULL");
554 } else if (IS_NUM(field->type) && !jcr->gui && is_an_integer(row[i])) {
555 bsnprintf(buf, sizeof(buf), " %*s: %s\n", max_len, field->name,
556 add_commas(row[i], ewc));
558 bsnprintf(buf, sizeof(buf), " %*s: %s\n", max_len, field->name, row[i]);
568 #endif /* HAVE_SQLITE3 || HAVE_MYSQL || HAVE_SQLITE || HAVE_POSTGRESQL*/