2 Bacula® - The Network Backup Solution
4 Copyright (C) 2000-2010 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 Kern Sibbald.
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 routines specific to SQLite
31 * Kern Sibbald, January 2002
37 /* The following is necessary so that we do not include
38 * the dummy external definition of DB.
40 #define __SQL_C /* indicate that this is sql.c */
45 #if HAVE_SQLITE || HAVE_SQLITE3
47 /* -----------------------------------------------------------------------
49 * SQLite dependent defines and subroutines
51 * -----------------------------------------------------------------------
54 /* List of open databases */
55 static dlist *db_list = NULL;
57 static pthread_mutex_t mutex = PTHREAD_MUTEX_INITIALIZER;
60 * Retrieve database type
73 * When using mult_db_connections = 1,
74 * sqlite can be BUSY. We just need sleep a little in this case.
78 static int my_busy_handler(void *arg, int calls)
84 static int my_busy_handler(void *arg, const char* p, int calls)
93 * Initialize database data structure. In principal this should
94 * never have errors, or it is really fatal.
97 db_init_database(JCR *jcr, const char *db_name, const char *db_user, const char *db_password,
98 const char *db_address, int db_port, const char *db_socket,
99 int mult_db_connections)
103 P(mutex); /* lock DB queue */
104 if (db_list == NULL) {
105 db_list = New(dlist(mdb, &mdb->link));
107 /* Look to see if DB already open */
108 if (!mult_db_connections) {
109 foreach_dlist(mdb, db_list) {
110 if (bstrcmp(mdb->db_name, db_name) &&
111 bstrcmp(mdb->db_address, db_address) &&
112 mdb->db_port == db_port) {
113 Dmsg2(300, "DB REopen %d %s\n", mdb->ref_count, db_name);
116 return mdb; /* already open */
120 Dmsg0(300, "db_open first time\n");
121 mdb = (B_DB *)malloc(sizeof(B_DB));
122 memset(mdb, 0, sizeof(B_DB));
123 mdb->db_name = bstrdup(db_name);
124 mdb->errmsg = get_pool_memory(PM_EMSG); /* get error message buffer */
126 mdb->cmd = get_pool_memory(PM_EMSG); /* get command buffer */
127 mdb->cached_path = get_pool_memory(PM_FNAME);
128 mdb->cached_path_id = 0;
130 mdb->fname = get_pool_memory(PM_FNAME);
131 mdb->path = get_pool_memory(PM_FNAME);
132 mdb->esc_name = get_pool_memory(PM_FNAME);
133 mdb->esc_path = get_pool_memory(PM_FNAME);
134 mdb->esc_obj = get_pool_memory(PM_FNAME);
135 mdb->allow_transactions = mult_db_connections;
136 db_list->append(mdb);
142 * Now actually open the database. This can generate errors,
143 * which are returned in the errmsg
145 * DO NOT close the database or free(mdb) here !!!!
148 db_open_database(JCR *jcr, B_DB *mdb)
157 if (mdb->connected) {
161 mdb->connected = FALSE;
163 if ((errstat=rwl_init(&mdb->lock)) != 0) {
165 Mmsg1(&mdb->errmsg, _("Unable to initialize DB lock. ERR=%s\n"),
166 be.bstrerror(errstat));
171 /* open the database */
172 len = strlen(working_directory) + strlen(mdb->db_name) + 5;
173 db_name = (char *)malloc(len);
174 strcpy(db_name, working_directory);
175 strcat(db_name, "/");
176 strcat(db_name, mdb->db_name);
177 strcat(db_name, ".db");
178 if (stat(db_name, &statbuf) != 0) {
179 Mmsg1(&mdb->errmsg, _("Database %s does not exist, please create it.\n"),
186 for (mdb->db=NULL; !mdb->db && retry++ < 10; ) {
188 int stat = sqlite3_open(db_name, &mdb->db);
189 if (stat != SQLITE_OK) {
190 mdb->sqlite_errmsg = (char *)sqlite3_errmsg(mdb->db);
191 sqlite3_close(mdb->db);
194 mdb->sqlite_errmsg = NULL;
197 mdb->db = sqlite_open(
198 db_name, /* database name */
200 &mdb->sqlite_errmsg); /* error message */
203 Dmsg0(300, "sqlite_open\n");
208 if (mdb->db == NULL) {
209 Mmsg2(&mdb->errmsg, _("Unable to open Database=%s. ERR=%s\n"),
210 db_name, mdb->sqlite_errmsg ? mdb->sqlite_errmsg : _("unknown"));
215 mdb->connected = true;
218 /* set busy handler to wait when we use mult_db_connections = 1 */
220 sqlite3_busy_handler(mdb->db, my_busy_handler, NULL);
222 sqlite_busy_handler(mdb->db, my_busy_handler, NULL);
225 #if defined(HAVE_SQLITE3) && defined(SQLITE3_INIT_QUERY)
226 db_sql_query(mdb, SQLITE3_INIT_QUERY, NULL, NULL);
229 if (!check_tables_version(jcr, mdb)) {
240 db_close_database(JCR *jcr, B_DB *mdb)
245 db_end_transaction(jcr, mdb);
247 sql_free_result(mdb);
249 if (mdb->ref_count == 0) {
250 db_list->remove(mdb);
251 if (mdb->connected && mdb->db) {
252 sqlite_close(mdb->db);
254 rwl_destroy(&mdb->lock);
255 free_pool_memory(mdb->errmsg);
256 free_pool_memory(mdb->cmd);
257 free_pool_memory(mdb->cached_path);
258 free_pool_memory(mdb->fname);
259 free_pool_memory(mdb->path);
260 free_pool_memory(mdb->esc_name);
261 free_pool_memory(mdb->esc_path);
262 free_pool_memory(mdb->esc_obj);
267 if (db_list->size() == 0) {
275 void db_check_backend_thread_safe()
277 #ifdef HAVE_BATCH_FILE_INSERT
278 # ifdef HAVE_SQLITE3_THREADSAFE
279 if (!sqlite3_threadsafe()) {
280 Emsg0(M_ABORT, 0, _("SQLite3 client library must be thread-safe "
281 "when using BatchMode.\n"));
287 void db_thread_cleanup()
290 sqlite3_thread_cleanup();
295 * Return the next unique index (auto-increment) for
296 * the given table. Return 0 on error.
298 int db_next_index(JCR *jcr, B_DB *mdb, char *table, char *index)
300 strcpy(index, "NULL");
305 * Escape binary object so that SQLite is happy
306 * Memory is stored in B_DB struct, no need to free it
308 * TODO: this should be implemented (escape \0)
311 db_escape_object(JCR *jcr, B_DB *db, char *old, int len)
315 n = mdb->esc_obj = check_pool_memory_size(mdb->esc_obj, len*2+1);
339 * Unescape binary object so that SQLIte is happy
340 * Memory is stored in B_DB struct, no need to free it
342 * TODO: need to be implemented (escape \0)
345 db_unescape_object(JCR *jcr, B_DB *db,
346 char *from, int32_t expected_len,
347 POOLMEM **dest, int32_t *dest_len)
354 *dest = check_pool_memory_size(*dest, expected_len+1);
355 *dest_len = expected_len;
356 memcpy(*dest, from, expected_len);
357 (*dest)[expected_len]=0;
361 * Escape strings so that SQLite is happy
363 * NOTE! len is the length of the old string. Your new
364 * string must be long enough (max 2*old+1) to hold
365 * the escaped output.
368 db_escape_string(JCR *jcr, B_DB *db, char *snew, char *old, int len)
395 DB_RESULT_HANDLER *result_handler;
400 * Convert SQLite's callback into Bacula DB callback
402 static int sqlite_result(void *arh_data, int num_fields, char **rows, char **col_names)
404 struct rh_data *rh_data = (struct rh_data *)arh_data;
406 if (rh_data->result_handler) {
407 (*(rh_data->result_handler))(rh_data->ctx, num_fields, rows);
413 * Submit a general SQL command (cmd), and for each row returned,
414 * the sqlite_handler is called with the ctx.
416 bool db_sql_query(B_DB *mdb, const char *query, DB_RESULT_HANDLER *result_handler, void *ctx)
418 struct rh_data rh_data;
422 if (mdb->sqlite_errmsg) {
424 sqlite3_free(mdb->sqlite_errmsg);
426 actuallyfree(mdb->sqlite_errmsg);
428 mdb->sqlite_errmsg = NULL;
430 rh_data.result_handler = result_handler;
432 stat = sqlite_exec(mdb->db, query, sqlite_result, (void *)&rh_data, &mdb->sqlite_errmsg);
433 if (stat != SQLITE_OK) {
434 Mmsg(mdb->errmsg, _("Query failed: %s: ERR=%s\n"), query, sql_strerror(mdb));
443 * Submit a sqlite query and retrieve all the data
445 int my_sqlite_query(B_DB *mdb, const char *cmd)
449 my_sqlite_free_table(mdb);
450 if (mdb->sqlite_errmsg) {
452 sqlite3_free(mdb->sqlite_errmsg);
454 actuallyfree(mdb->sqlite_errmsg);
456 mdb->sqlite_errmsg = NULL;
458 stat = sqlite_get_table(mdb->db, (char *)cmd, &mdb->result, &mdb->nrow, &mdb->ncolumn,
459 &mdb->sqlite_errmsg);
460 mdb->row = 0; /* no row fetched yet */
461 if (stat != 0) { /* something went wrong */
462 mdb->nrow = mdb->ncolumn = 0;
467 /* Fetch one row at a time */
468 SQL_ROW my_sqlite_fetch_row(B_DB *mdb)
470 if (!mdb->result || (mdb->row >= mdb->nrow)) {
474 return &mdb->result[mdb->ncolumn * mdb->row];
477 void my_sqlite_free_table(B_DB *mdb)
481 if (mdb->fields_defined) {
482 for (i=0; i < sql_num_fields(mdb); i++) {
483 if (mdb->fields[i]) {
484 free(mdb->fields[i]);
485 mdb->fields[i] = NULL;
492 mdb->fields_defined = false;
495 sqlite_free_table(mdb->result);
498 mdb->nrow = mdb->ncolumn = 0;
501 void my_sqlite_field_seek(B_DB *mdb, int field)
504 if (mdb->result == NULL) {
508 /* On first call, set up the fields */
509 if (!mdb->fields_defined && sql_num_fields(mdb) > 0) {
510 mdb->fields = (SQL_FIELD **)malloc(sizeof(SQL_FIELD) * mdb->ncolumn);
511 for (i=0; i < sql_num_fields(mdb); i++) {
512 mdb->fields[i] = (SQL_FIELD *)malloc(sizeof(SQL_FIELD));
513 /* ***FIXME*** it seems to me that this is wrong
514 * fields has lots of items
516 if (mdb->result[i] == NULL) {
517 mdb->fields_defined = false;
523 mdb->fields[i]->name = mdb->result[i];
524 mdb->fields[i]->length = cstrlen(mdb->fields[i]->name);
525 mdb->fields[i]->max_length = mdb->fields[i]->length;
526 for (j=1; j <= mdb->nrow; j++) {
528 if (mdb->result[i + mdb->ncolumn *j]) {
529 len = (uint32_t)cstrlen(mdb->result[i + mdb->ncolumn * j]);
533 if (len > mdb->fields[i]->max_length) {
534 mdb->fields[i]->max_length = len;
537 mdb->fields[i]->type = 0;
538 mdb->fields[i]->flags = 1; /* not null */
540 mdb->fields_defined = true;
542 if (sql_num_fields(mdb) <= 0) {
544 } else if (field > sql_num_fields(mdb) - 1) {
545 field = sql_num_fields(mdb) - 1;
550 SQL_FIELD *my_sqlite_fetch_field(B_DB *mdb)
552 if (mdb->fields_defined && mdb->field < sql_num_fields(mdb)) {
553 return mdb->fields[mdb->field++];
560 int my_sqlite_insert_autokey_record(B_DB *mdb, const char *query, const char *table_name)
563 * First execute the insert query and then retrieve the currval.
565 if (my_sqlite_query(mdb, query)) {
569 mdb->num_rows = sql_affected_rows(mdb);
570 if (mdb->num_rows != 1) {
577 return sqlite3_last_insert_rowid(mdb->db);
579 return sqlite_last_insert_rowid(mdb->db);
583 #ifdef HAVE_BATCH_FILE_INSERT
584 const char *my_sqlite_batch_lock_query = "BEGIN";
585 const char *my_sqlite_batch_unlock_query = "COMMIT";
587 const char *my_sqlite_batch_fill_path_query =
588 "INSERT INTO Path (Path)"
589 " SELECT DISTINCT Path FROM batch"
590 " EXCEPT SELECT Path FROM Path";
592 const char *my_sqlite_batch_fill_filename_query =
593 "INSERT INTO Filename (Name)"
594 " SELECT DISTINCT Name FROM batch "
595 " EXCEPT SELECT Name FROM Filename";
596 #endif /* HAVE_BATCH_FILE_INSERT */
599 #endif /* HAVE_SQLITE */