2 * Bacula Catalog Database interface routines
4 * Almost generic set of SQL database interface routines
5 * (with a little more work)
7 * Kern Sibbald, March 2000
13 Copyright (C) 2000-2005 Kern Sibbald
15 This program is free software; you can redistribute it and/or
16 modify it under the terms of the GNU General Public License as
17 published by the Free Software Foundation; either version 2 of
18 the License, or (at your option) any later version.
20 This program is distributed in the hope that it will be useful,
21 but WITHOUT ANY WARRANTY; without even the implied warranty of
22 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
23 General Public License for more details.
25 You should have received a copy of the GNU General Public
26 License along with this program; if not, write to the Free
27 Software Foundation, Inc., 59 Temple Place - Suite 330, Boston,
32 /* The following is necessary so that we do not include
33 * the dummy external definition of B_DB.
35 #define __SQL_C /* indicate that this is sql.c */
40 #if HAVE_SQLITE3 || HAVE_MYSQL || HAVE_SQLITE || HAVE_POSTGRESQL
42 uint32_t bacula_db_version = 0;
44 /* Forward referenced subroutines */
45 void print_dashes(B_DB *mdb);
46 void print_result(B_DB *mdb);
49 * Called here to retrieve an integer from the database
51 static int int_handler(void *ctx, int num_fields, char **row)
53 uint32_t *val = (uint32_t *)ctx;
55 Dmsg1(800, "int_handler starts with row pointing at %x\n", row);
58 Dmsg1(800, "int_handler finds '%s'\n", row[0]);
59 *val = str_to_int64(row[0]);
61 Dmsg0(800, "int_handler finds zero\n");
64 Dmsg0(800, "int_handler finishes\n");
70 /* NOTE!!! The following routines expect that the
71 * calling subroutine sets and clears the mutex
74 /* Check that the tables correspond to the version we want */
75 int check_tables_version(JCR *jcr, B_DB *mdb)
77 const char *query = "SELECT VersionId FROM Version";
79 bacula_db_version = 0;
80 if (!db_sql_query(mdb, query, int_handler, (void *)&bacula_db_version)) {
81 Mmsg(mdb->errmsg, "Database not created or server not running.\n");
82 Jmsg(jcr, M_FATAL, 0, "%s", mdb->errmsg);
84 if (bacula_db_version != BDB_VERSION) {
85 Mmsg(mdb->errmsg, "Version error for database \"%s\". Wanted %d, got %d\n",
86 mdb->db_name, BDB_VERSION, bacula_db_version);
87 Jmsg(jcr, M_FATAL, 0, "%s", mdb->errmsg);
93 /* Utility routine for queries. The database MUST be locked before calling here. */
95 QueryDB(const char *file, int line, JCR *jcr, B_DB *mdb, char *cmd)
98 if ((status=sql_query(mdb, cmd)) != 0) {
99 m_msg(file, line, &mdb->errmsg, _("query %s failed:\n%s\n"), cmd, sql_strerror(mdb));
100 j_msg(file, line, jcr, M_FATAL, 0, "%s", mdb->errmsg);
102 j_msg(file, line, jcr, M_INFO, 0, "%s\n", cmd);
107 mdb->result = sql_store_result(mdb);
109 return mdb->result != NULL;
113 * Utility routine to do inserts
114 * Returns: 0 on failure
118 InsertDB(const char *file, int line, JCR *jcr, B_DB *mdb, char *cmd)
120 if (sql_query(mdb, cmd)) {
121 m_msg(file, line, &mdb->errmsg, _("insert %s failed:\n%s\n"), cmd, sql_strerror(mdb));
122 j_msg(file, line, jcr, M_FATAL, 0, "%s", mdb->errmsg);
124 j_msg(file, line, jcr, M_INFO, 0, "%s\n", cmd);
128 if (mdb->have_insert_id) {
129 mdb->num_rows = sql_affected_rows(mdb);
133 if (mdb->num_rows != 1) {
135 m_msg(file, line, &mdb->errmsg, _("Insertion problem: affected_rows=%s\n"),
136 edit_uint64(mdb->num_rows, ed1));
138 j_msg(file, line, jcr, M_INFO, 0, "%s\n", cmd);
146 /* Utility routine for updates.
147 * Returns: 0 on failure
151 UpdateDB(const char *file, int line, JCR *jcr, B_DB *mdb, char *cmd)
154 if (sql_query(mdb, cmd)) {
155 m_msg(file, line, &mdb->errmsg, _("update %s failed:\n%s\n"), cmd, sql_strerror(mdb));
156 j_msg(file, line, jcr, M_ERROR, 0, "%s", mdb->errmsg);
158 j_msg(file, line, jcr, M_INFO, 0, "%s\n", cmd);
162 mdb->num_rows = sql_affected_rows(mdb);
163 if (mdb->num_rows < 1) {
165 m_msg(file, line, &mdb->errmsg, _("Update problem: affected_rows=%s\n"),
166 edit_uint64(mdb->num_rows, ed1));
168 // j_msg(file, line, jcr, M_INFO, 0, "%s\n", cmd);
176 /* Utility routine for deletes
178 * Returns: -1 on error
179 * n number of rows affected
182 DeleteDB(const char *file, int line, JCR *jcr, B_DB *mdb, char *cmd)
185 if (sql_query(mdb, cmd)) {
186 m_msg(file, line, &mdb->errmsg, _("delete %s failed:\n%s\n"), cmd, sql_strerror(mdb));
187 j_msg(file, line, jcr, M_ERROR, 0, "%s", mdb->errmsg);
189 j_msg(file, line, jcr, M_INFO, 0, "%s\n", cmd);
194 return sql_affected_rows(mdb);
199 * Get record max. Query is already in mdb->cmd
202 * Returns: -1 on failure
205 int get_sql_record_max(JCR *jcr, B_DB *mdb)
210 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
211 if ((row = sql_fetch_row(mdb)) == NULL) {
212 Mmsg1(&mdb->errmsg, _("error fetching row: %s\n"), sql_strerror(mdb));
215 stat = str_to_int64(row[0]);
217 sql_free_result(mdb);
219 Mmsg1(&mdb->errmsg, _("error fetching row: %s\n"), sql_strerror(mdb));
226 * Return pre-edited error message
228 char *db_strerror(B_DB *mdb)
234 * Lock database, this can be called multiple times by the same
235 * thread without blocking, but must be unlocked the number of
236 * times it was locked.
238 void _db_lock(const char *file, int line, B_DB *mdb)
241 if ((errstat=rwl_writelock(&mdb->lock)) != 0) {
243 e_msg(file, line, M_FATAL, 0, "rwl_writelock failure. stat=%d: ERR=%s\n",
244 errstat, be.strerror(errstat));
249 * Unlock the database. This can be called multiple times by the
250 * same thread up to the number of times that thread called
253 void _db_unlock(const char *file, int line, B_DB *mdb)
256 if ((errstat=rwl_writeunlock(&mdb->lock)) != 0) {
258 e_msg(file, line, M_FATAL, 0, "rwl_writeunlock failure. stat=%d: ERR=%s\n",
259 errstat, be.strerror(errstat));
264 * Start a transaction. This groups inserts and makes things
265 * much more efficient. Usually started when inserting
268 void db_start_transaction(JCR *jcr, B_DB *mdb)
271 jcr->attr = get_pool_memory(PM_FNAME);
274 jcr->ar = (ATTR_DBR *)malloc(sizeof(ATTR_DBR));
278 if (!mdb->allow_transactions) {
282 /* Allow only 10,000 changes per transaction */
283 if (mdb->transaction && mdb->changes > 10000) {
284 db_end_transaction(jcr, mdb);
286 if (!mdb->transaction) {
287 my_sqlite_query(mdb, "BEGIN"); /* begin transaction */
288 Dmsg0(400, "Start SQLite transaction\n");
289 mdb->transaction = 1;
295 * This is turned off because transactions break
296 * if multiple simultaneous jobs are run.
298 #ifdef HAVE_POSTGRESQL
299 if (!mdb->allow_transactions) {
303 /* Allow only 25,000 changes per transaction */
304 if (mdb->transaction && mdb->changes > 25000) {
305 db_end_transaction(jcr, mdb);
307 if (!mdb->transaction) {
308 db_sql_query(mdb, "BEGIN", NULL, NULL); /* begin transaction */
309 Dmsg0(400, "Start PosgreSQL transaction\n");
310 mdb->transaction = 1;
316 void db_end_transaction(JCR *jcr, B_DB *mdb)
319 * This can be called during thread cleanup and
320 * the db may already be closed. So simply return.
326 if (jcr && jcr->cached_attribute) {
327 Dmsg0(400, "Flush last cached attribute.\n");
328 if (!db_create_file_attributes_record(jcr, mdb, jcr->ar)) {
329 Jmsg1(jcr, M_FATAL, 0, _("Attribute create error. %s"), db_strerror(jcr->db));
331 jcr->cached_attribute = false;
335 if (!mdb->allow_transactions) {
339 if (mdb->transaction) {
340 my_sqlite_query(mdb, "COMMIT"); /* end transaction */
341 mdb->transaction = 0;
342 Dmsg1(400, "End SQLite transaction changes=%d\n", mdb->changes);
348 #ifdef HAVE_POSTGRESQL
349 if (!mdb->allow_transactions) {
353 if (mdb->transaction) {
354 db_sql_query(mdb, "COMMIT", NULL, NULL); /* end transaction */
355 mdb->transaction = 0;
356 Dmsg1(400, "End PostgreSQL transaction changes=%d\n", mdb->changes);
364 * Given a full filename, split it into its path
365 * and filename parts. They are returned in pool memory
366 * in the mdb structure.
368 void split_path_and_file(JCR *jcr, B_DB *mdb, const char *fname)
372 /* Find path without the filename.
373 * I.e. everything after the last / is a "filename".
374 * OK, maybe it is a directory name, but we treat it like
375 * a filename. If we don't find a / then the whole name
376 * must be a path name (e.g. c:).
378 for (p=f=fname; *p; p++) {
380 f = p; /* set pos of last slash */
383 if (*f == '/') { /* did we find a slash? */
384 f++; /* yes, point to filename */
385 } else { /* no, whole thing must be path name */
389 /* If filename doesn't exist (i.e. root directory), we
390 * simply create a blank name consisting of a single
391 * space. This makes handling zero length filenames
396 mdb->fname = check_pool_memory_size(mdb->fname, mdb->fnl+1);
397 memcpy(mdb->fname, f, mdb->fnl); /* copy filename */
398 mdb->fname[mdb->fnl] = 0;
404 mdb->pnl = f - fname;
406 mdb->path = check_pool_memory_size(mdb->path, mdb->pnl+1);
407 memcpy(mdb->path, fname, mdb->pnl);
408 mdb->path[mdb->pnl] = 0;
410 Mmsg1(&mdb->errmsg, _("Path length is zero. File=%s\n"), fname);
411 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
416 Dmsg2(500, "split path=%s file=%s\n", mdb->path, mdb->fname);
420 * List dashes as part of header for listing SQL results in a table
423 list_dashes(B_DB *mdb, DB_LIST_HANDLER *send, void *ctx)
428 sql_field_seek(mdb, 0);
430 for (i = 0; i < sql_num_fields(mdb); i++) {
431 field = sql_fetch_field(mdb);
432 for (j = 0; j < (int)field->max_length + 2; j++) {
441 * If full_list is set, we list vertically, otherwise, we
442 * list on one line horizontally.
445 list_result(JCR *jcr, B_DB *mdb, DB_LIST_HANDLER *send, void *ctx, e_list_type type)
449 int i, col_len, max_len = 0;
450 char buf[2000], ewc[30];
452 Dmsg0(800, "list_result starts\n");
453 if (mdb->result == NULL || sql_num_rows(mdb) == 0) {
454 send(ctx, _("No results to list.\n"));
458 Dmsg1(800, "list_result starts looking at %d fields\n", sql_num_fields(mdb));
459 /* determine column display widths */
460 sql_field_seek(mdb, 0);
461 for (i = 0; i < sql_num_fields(mdb); i++) {
462 Dmsg1(800, "list_result processing field %d\n", i);
463 field = sql_fetch_field(mdb);
464 col_len = cstrlen(field->name);
465 if (type == VERT_LIST) {
466 if (col_len > max_len) {
470 if (IS_NUM(field->type) && (int)field->max_length > 0) { /* fixup for commas */
471 field->max_length += (field->max_length - 1) / 3;
473 if (col_len < (int)field->max_length) {
474 col_len = field->max_length;
476 if (col_len < 4 && !IS_NOT_NULL(field->flags)) {
477 col_len = 4; /* 4 = length of the word "NULL" */
479 field->max_length = col_len; /* reset column info */
483 Dmsg0(800, "list_result finished first loop\n");
484 if (type == VERT_LIST) {
488 Dmsg1(800, "list_result starts second loop looking at %d fields\n", sql_num_fields(mdb));
489 list_dashes(mdb, send, ctx);
491 sql_field_seek(mdb, 0);
492 for (i = 0; i < sql_num_fields(mdb); i++) {
493 Dmsg1(800, "list_result looking at field %d\n", i);
494 field = sql_fetch_field(mdb);
495 bsnprintf(buf, sizeof(buf), " %-*s |", (int)field->max_length, field->name);
499 list_dashes(mdb, send, ctx);
501 Dmsg1(800, "list_result starts third loop looking at %d fields\n", sql_num_fields(mdb));
502 while ((row = sql_fetch_row(mdb)) != NULL) {
503 sql_field_seek(mdb, 0);
505 for (i = 0; i < sql_num_fields(mdb); i++) {
506 field = sql_fetch_field(mdb);
507 if (row[i] == NULL) {
508 bsnprintf(buf, sizeof(buf), " %-*s |", (int)field->max_length, "NULL");
509 } else if (IS_NUM(field->type) && !jcr->gui && is_an_integer(row[i])) {
510 bsnprintf(buf, sizeof(buf), " %*s |", (int)field->max_length,
511 add_commas(row[i], ewc));
513 bsnprintf(buf, sizeof(buf), " %-*s |", (int)field->max_length, row[i]);
519 list_dashes(mdb, send, ctx);
524 Dmsg1(800, "list_result starts vertical list at %d fields\n", sql_num_fields(mdb));
525 while ((row = sql_fetch_row(mdb)) != NULL) {
526 sql_field_seek(mdb, 0);
527 for (i = 0; i < sql_num_fields(mdb); i++) {
528 field = sql_fetch_field(mdb);
529 if (row[i] == NULL) {
530 bsnprintf(buf, sizeof(buf), " %*s: %s\n", max_len, field->name, "NULL");
531 } else if (IS_NUM(field->type) && !jcr->gui && is_an_integer(row[i])) {
532 bsnprintf(buf, sizeof(buf), " %*s: %s\n", max_len, field->name,
533 add_commas(row[i], ewc));
535 bsnprintf(buf, sizeof(buf), " %*s: %s\n", max_len, field->name, row[i]);
545 #endif /* HAVE_SQLITE3 || HAVE_MYSQL || HAVE_SQLITE || HAVE_POSTGRESQL*/