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-2004 Kern Sibbald and John Walker
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_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(200, "int_handler starts with row pointing at %x\n", row);
58 Dmsg1(200, "int_handler finds '%s'\n", row[0]);
61 Dmsg0(200, "int_handler finds zero\n");
64 Dmsg0(200, "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 char *query = "SELECT VersionId FROM Version";
79 bacula_db_version = 0;
80 db_sql_query(mdb, query, int_handler, (void *)&bacula_db_version);
81 if (bacula_db_version != BDB_VERSION) {
82 Mmsg(&mdb->errmsg, "Version error for database \"%s\". Wanted %d, got %d\n",
83 mdb->db_name, BDB_VERSION, bacula_db_version);
84 Jmsg(jcr, M_FATAL, 0, "%s", mdb->errmsg);
90 /* Utility routine for queries. The database MUST be locked before calling here. */
92 QueryDB(char *file, int line, JCR *jcr, B_DB *mdb, char *cmd)
95 if ((status=sql_query(mdb, cmd)) != 0) {
96 m_msg(file, line, &mdb->errmsg, _("query %s failed:\n%s\n"), cmd, sql_strerror(mdb));
97 j_msg(file, line, jcr, M_FATAL, 0, "%s", mdb->errmsg);
99 j_msg(file, line, jcr, M_INFO, 0, "%s\n", cmd);
104 mdb->result = sql_store_result(mdb);
106 return mdb->result != NULL;
110 * Utility routine to do inserts
111 * Returns: 0 on failure
115 InsertDB(char *file, int line, JCR *jcr, B_DB *mdb, char *cmd)
117 if (sql_query(mdb, cmd)) {
118 m_msg(file, line, &mdb->errmsg, _("insert %s failed:\n%s\n"), cmd, sql_strerror(mdb));
119 j_msg(file, line, jcr, M_FATAL, 0, "%s", mdb->errmsg);
121 j_msg(file, line, jcr, M_INFO, 0, "%s\n", cmd);
125 if (mdb->have_insert_id) {
126 mdb->num_rows = sql_affected_rows(mdb);
130 if (mdb->num_rows != 1) {
132 m_msg(file, line, &mdb->errmsg, _("Insertion problem: affected_rows=%s\n"),
133 edit_uint64(mdb->num_rows, ed1));
135 j_msg(file, line, jcr, M_INFO, 0, "%s\n", cmd);
143 /* Utility routine for updates.
144 * Returns: 0 on failure
148 UpdateDB(char *file, int line, JCR *jcr, B_DB *mdb, char *cmd)
151 if (sql_query(mdb, cmd)) {
152 m_msg(file, line, &mdb->errmsg, _("update %s failed:\n%s\n"), cmd, sql_strerror(mdb));
153 j_msg(file, line, jcr, M_ERROR, 0, "%s", mdb->errmsg);
155 j_msg(file, line, jcr, M_INFO, 0, "%s\n", cmd);
159 mdb->num_rows = sql_affected_rows(mdb);
160 if (mdb->num_rows != 1) {
162 m_msg(file, line, &mdb->errmsg, _("Update problem: affected_rows=%s\n"),
163 edit_uint64(mdb->num_rows, ed1));
165 j_msg(file, line, jcr, M_INFO, 0, "%s\n", cmd);
173 /* Utility routine for deletes
175 * Returns: -1 on error
176 * n number of rows affected
179 DeleteDB(char *file, int line, JCR *jcr, B_DB *mdb, char *cmd)
182 if (sql_query(mdb, cmd)) {
183 m_msg(file, line, &mdb->errmsg, _("delete %s failed:\n%s\n"), cmd, sql_strerror(mdb));
184 j_msg(file, line, jcr, M_ERROR, 0, "%s", mdb->errmsg);
186 j_msg(file, line, jcr, M_INFO, 0, "%s\n", cmd);
191 return sql_affected_rows(mdb);
196 * Get record max. Query is already in mdb->cmd
199 * Returns: -1 on failure
202 int get_sql_record_max(JCR *jcr, B_DB *mdb)
207 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
208 if ((row = sql_fetch_row(mdb)) == NULL) {
209 Mmsg1(&mdb->errmsg, _("error fetching row: %s\n"), sql_strerror(mdb));
214 sql_free_result(mdb);
216 Mmsg1(&mdb->errmsg, _("error fetching row: %s\n"), sql_strerror(mdb));
223 * Return pre-edited error message
225 char *db_strerror(B_DB *mdb)
231 * Lock database, this can be called multiple times by the same
232 * thread without blocking, but must be unlocked the number of
233 * times it was locked.
235 void _db_lock(char *file, int line, B_DB *mdb)
238 if ((errstat=rwl_writelock(&mdb->lock)) != 0) {
239 e_msg(file, line, M_ABORT, 0, "rwl_writelock failure. ERR=%s\n",
245 * Unlock the database. This can be called multiple times by the
246 * same thread up to the number of times that thread called
249 void _db_unlock(char *file, int line, B_DB *mdb)
252 if ((errstat=rwl_writeunlock(&mdb->lock)) != 0) {
253 e_msg(file, line, M_ABORT, 0, "rwl_writeunlock failure. ERR=%s\n",
259 * Start a transaction. This groups inserts and makes things
260 * much more efficient. Usually started when inserting
263 void db_start_transaction(JCR *jcr, B_DB *mdb)
267 /* Allow only 10,000 changes per transaction */
268 if (mdb->transaction && mdb->changes > 10000) {
269 db_end_transaction(jcr, mdb);
271 if (!mdb->transaction) {
272 my_sqlite_query(mdb, "BEGIN"); /* begin transaction */
273 Dmsg0(400, "Start SQLite transaction\n");
274 mdb->transaction = 1;
281 void db_end_transaction(JCR *jcr, B_DB *mdb)
285 if (mdb->transaction) {
286 my_sqlite_query(mdb, "COMMIT"); /* end transaction */
287 mdb->transaction = 0;
288 Dmsg1(400, "End SQLite transaction changes=%d\n", mdb->changes);
296 * Given a full filename, split it into its path
297 * and filename parts. They are returned in pool memory
298 * in the mdb structure.
300 void split_path_and_file(JCR *jcr, B_DB *mdb, const char *fname)
302 split_path_and_filename(fname, &mdb->path, &mdb->pnl, &mdb->fname, &mdb->fnl);
304 mdb->fname[0] = ' '; /* blank filename */
309 Mmsg1(&mdb->errmsg, _("Path length is zero. File=%s\n"), fname);
310 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
315 Dmsg2(400, "split path=%s file=%s\n", mdb->path, mdb->fname);
319 * List dashes as part of header for listing SQL results in a table
322 list_dashes(B_DB *mdb, DB_LIST_HANDLER *send, void *ctx)
327 sql_field_seek(mdb, 0);
329 for (i = 0; i < sql_num_fields(mdb); i++) {
330 field = sql_fetch_field(mdb);
331 for (j = 0; j < (int)field->max_length + 2; j++) {
340 * If full_list is set, we list vertically, otherwise, we
341 * list on one line horizontally.
344 list_result(JCR *jcr, B_DB *mdb, DB_LIST_HANDLER *send, void *ctx, e_list_type type)
348 int i, col_len, max_len = 0;
349 char buf[2000], ewc[30];
351 Dmsg0(200, "list_result starts\n");
352 if (mdb->result == NULL || sql_num_rows(mdb) == 0) {
353 send(ctx, _("No results to list.\n"));
357 Dmsg1(200, "list_result starts looking at %d fields\n", sql_num_fields(mdb));
358 /* determine column display widths */
359 sql_field_seek(mdb, 0);
360 for (i = 0; i < sql_num_fields(mdb); i++) {
361 Dmsg1(200, "list_result processing field %d\n", i);
362 field = sql_fetch_field(mdb);
363 col_len = strlen(field->name);
364 if (type == VERT_LIST) {
365 if (col_len > max_len) {
369 if (IS_NUM(field->type) && (int)field->max_length > 0) { /* fixup for commas */
370 field->max_length += (field->max_length - 1) / 3;
372 if (col_len < (int)field->max_length) {
373 col_len = field->max_length;
375 if (col_len < 4 && !IS_NOT_NULL(field->flags)) {
376 col_len = 4; /* 4 = length of the word "NULL" */
378 field->max_length = col_len; /* reset column info */
382 Dmsg0(200, "list_result finished first loop\n");
383 if (type == VERT_LIST) {
387 Dmsg1(200, "list_result starts second loop looking at %d fields\n", sql_num_fields(mdb));
388 list_dashes(mdb, send, ctx);
390 sql_field_seek(mdb, 0);
391 for (i = 0; i < sql_num_fields(mdb); i++) {
392 Dmsg1(200, "list_result looking at field %d\n", i);
393 field = sql_fetch_field(mdb);
394 bsnprintf(buf, sizeof(buf), " %-*s |", (int)field->max_length, field->name);
398 list_dashes(mdb, send, ctx);
400 Dmsg1(200, "list_result starts third loop looking at %d fields\n", sql_num_fields(mdb));
401 while ((row = sql_fetch_row(mdb)) != NULL) {
402 sql_field_seek(mdb, 0);
404 for (i = 0; i < sql_num_fields(mdb); i++) {
405 field = sql_fetch_field(mdb);
406 if (row[i] == NULL) {
407 bsnprintf(buf, sizeof(buf), " %-*s |", (int)field->max_length, "NULL");
408 } else if (IS_NUM(field->type) && !jcr->gui) {
409 bsnprintf(buf, sizeof(buf), " %*s |", (int)field->max_length,
410 add_commas(row[i], ewc));
412 bsnprintf(buf, sizeof(buf), " %-*s |", (int)field->max_length, row[i]);
418 list_dashes(mdb, send, ctx);
423 Dmsg1(200, "list_result starts vertical list at %d fields\n", sql_num_fields(mdb));
424 while ((row = sql_fetch_row(mdb)) != NULL) {
425 sql_field_seek(mdb, 0);
426 for (i = 0; i < sql_num_fields(mdb); i++) {
427 field = sql_fetch_field(mdb);
428 if (row[i] == NULL) {
429 bsnprintf(buf, sizeof(buf), " %*s: %s\n", max_len, field->name, "NULL");
430 } else if (IS_NUM(field->type) && !jcr->gui) {
431 bsnprintf(buf, sizeof(buf), " %*s: %s\n", max_len, field->name,
432 add_commas(row[i], ewc));
434 bsnprintf(buf, sizeof(buf), " %*s: %s\n", max_len, field->name, row[i]);
444 #endif /* HAVE_MYSQL || HAVE_SQLITE || HAVE_POSTGRESQL */