2 Bacula(R) - The Network Backup Solution
4 Copyright (C) 2000-2016 Kern Sibbald
6 The original author of Bacula is Kern Sibbald, with contributions
7 from many others, a complete list can be found in the file AUTHORS.
9 You may use this file and others of this release according to the
10 license defined in the LICENSE file, which includes the Affero General
11 Public License, v3.0 ("AGPLv3") and some additional permissions and
12 terms pursuant to its AGPLv3 Section 7.
14 This notice must be preserved when any source code is
15 conveyed and/or propagated.
17 Bacula(R) is a registered trademark of Kern Sibbald.
20 * Bacula Catalog Database routines specific to SQLite
22 * Written by Kern Sibbald, January 2002
24 * Note: at one point, this file was changed to class based by a certain
25 * programmer, and other than "wrapping" in a class, which is a trivial
26 * change for a C++ programmer, nothing substantial was done, yet all the
27 * code was recommitted under this programmer's name. Consequently, we
28 * undo those changes here.
37 #define __BDB_SQLITE_H_ 1
38 #include "bdb_sqlite.h"
40 /* -----------------------------------------------------------------------
42 * SQLite dependent defines and subroutines
44 * -----------------------------------------------------------------------
47 /* List of open databases */
48 static dlist *db_list = NULL;
50 static pthread_mutex_t mutex = PTHREAD_MUTEX_INITIALIZER;
53 * When using mult_db_connections
54 * sqlite can be BUSY. We just need sleep a little in this case.
56 static int my_sqlite_busy_handler(void *arg, int calls)
62 BDB_SQLITE::BDB_SQLITE()
64 BDB_SQLITE *mdb = this;
66 if (db_list == NULL) {
67 db_list = New(dlist(mdb, &mdb->m_link));
69 mdb->m_db_driver_type = SQL_DRIVER_TYPE_SQLITE3;
70 mdb->m_db_type = SQL_TYPE_SQLITE3;
71 mdb->m_db_driver = bstrdup("SQLite3");
73 mdb->errmsg = get_pool_memory(PM_EMSG); /* get error message buffer */
75 mdb->cmd = get_pool_memory(PM_EMSG); /* get command buffer */
76 mdb->cached_path = get_pool_memory(PM_FNAME);
77 mdb->cached_path_id = 0;
79 mdb->fname = get_pool_memory(PM_FNAME);
80 mdb->path = get_pool_memory(PM_FNAME);
81 mdb->esc_name = get_pool_memory(PM_FNAME);
82 mdb->esc_path = get_pool_memory(PM_FNAME);
83 mdb->esc_obj = get_pool_memory(PM_FNAME);
84 mdb->m_use_fatal_jmsg = true;
86 /* Initialize the private members. */
87 mdb->m_db_handle = NULL;
89 mdb->m_sqlite_errmsg = NULL;
91 db_list->append(this);
94 BDB_SQLITE::~BDB_SQLITE()
99 * Initialize database data structure. In principal this should
100 * never have errors, or it is really fatal.
102 BDB *db_init_database(JCR *jcr, const char *db_driver, const char *db_name, const char *db_user,
103 const char *db_password, const char *db_address, int db_port, const char *db_socket,
104 const char *db_ssl_mode, const char *db_ssl_key,
105 const char *db_ssl_cert, const char *db_ssl_ca,
106 const char *db_ssl_capath, const char *db_ssl_cipher,
107 bool mult_db_connections, bool disable_batch_insert)
109 BDB_SQLITE *mdb = NULL;
111 P(mutex); /* lock DB queue */
113 * Look to see if DB already open
115 if (db_list && !mult_db_connections) {
116 foreach_dlist(mdb, db_list) {
117 if (mdb->bdb_match_database(db_driver, db_name, db_address, db_port)) {
118 Dmsg1(300, "DB REopen %s\n", db_name);
119 mdb->increment_refcount();
124 Dmsg0(300, "db_init_database first time\n");
125 mdb = New(BDB_SQLITE());
127 mdb->m_db_name = bstrdup(db_name);
128 if (disable_batch_insert) {
129 mdb->m_disabled_batch_insert = true;
130 mdb->m_have_batch_insert = false;
132 mdb->m_disabled_batch_insert = false;
133 #ifdef USE_BATCH_FILE_INSERT
134 #ifdef HAVE_SQLITE3_THREADSAFE
135 mdb->m_have_batch_insert = sqlite3_threadsafe();
137 mdb->m_have_batch_insert = false;
138 #endif /* HAVE_SQLITE3_THREADSAFE */
140 mdb->m_have_batch_insert = false;
141 #endif /* USE_BATCH_FILE_INSERT */
143 mdb->m_allow_transactions = mult_db_connections;
145 /* At this time, when mult_db_connections == true, this is for
146 * specific console command such as bvfs or batch mode, and we don't
147 * want to share a batch mode or bvfs. In the future, we can change
148 * the creation function to add this parameter.
150 mdb->m_dedicated = mult_db_connections;
159 * Now actually open the database. This can generate errors,
160 * which are returned in the errmsg
162 * DO NOT close the database or delete mdb here !!!!
164 bool BDB_SQLITE::bdb_open_database(JCR *jcr)
174 BDB_SQLITE *mdb = this;
177 if (mdb->m_connected) {
182 if ((errstat=rwl_init(&mdb->m_lock)) != 0) {
184 Mmsg1(&mdb->errmsg, _("Unable to initialize DB lock. ERR=%s\n"),
185 be.bstrerror(errstat));
192 len = strlen(working_directory) + strlen(mdb->m_db_name) + 5;
193 db_file = (char *)malloc(len);
194 strcpy(db_file, working_directory);
195 strcat(db_file, "/");
196 strcat(db_file, m_db_name);
197 strcat(db_file, ".db");
198 if (stat(db_file, &statbuf) != 0) {
199 Mmsg1(&mdb->errmsg, _("Database %s does not exist, please create it.\n"),
205 for (mdb->m_db_handle = NULL; !mdb->m_db_handle && retry++ < 10; ) {
206 ret = sqlite3_open(db_file, &mdb->m_db_handle);
207 if (ret != SQLITE_OK) {
208 mdb->m_sqlite_errmsg = (char *)sqlite3_errmsg(mdb->m_db_handle);
209 sqlite3_close(mdb->m_db_handle);
210 mdb->m_db_handle = NULL;
212 mdb->m_sqlite_errmsg = NULL;
215 Dmsg0(300, "sqlite_open\n");
216 if (!mdb->m_db_handle) {
220 if (mdb->m_db_handle == NULL) {
221 Mmsg2(&mdb->errmsg, _("Unable to open Database=%s. ERR=%s\n"),
222 db_file, mdb->m_sqlite_errmsg ? mdb->m_sqlite_errmsg : _("unknown"));
226 mdb->m_connected = true;
230 * Set busy handler to wait when we use mult_db_connections = true
232 sqlite3_busy_handler(mdb->m_db_handle, my_sqlite_busy_handler, NULL);
234 #if defined(SQLITE3_INIT_QUERY)
235 sql_query(SQLITE3_INIT_QUERY);
238 if (!bdb_check_version(jcr)) {
249 void BDB_SQLITE::bdb_close_database(JCR *jcr)
251 BDB_SQLITE *mdb = this;
253 if (mdb->m_connected) {
254 bdb_end_transaction(jcr);
258 if (mdb->m_ref_count == 0) {
259 if (mdb->m_connected) {
262 db_list->remove(mdb);
263 if (mdb->m_connected && mdb->m_db_handle) {
264 sqlite3_close(mdb->m_db_handle);
266 if (is_rwl_valid(&mdb->m_lock)) {
267 rwl_destroy(&mdb->m_lock);
269 free_pool_memory(mdb->errmsg);
270 free_pool_memory(mdb->cmd);
271 free_pool_memory(mdb->cached_path);
272 free_pool_memory(mdb->fname);
273 free_pool_memory(mdb->path);
274 free_pool_memory(mdb->esc_name);
275 free_pool_memory(mdb->esc_path);
276 free_pool_memory(mdb->esc_obj);
277 if (mdb->m_db_driver) {
278 free(mdb->m_db_driver);
280 if (mdb->m_db_name) {
281 free(mdb->m_db_name);
284 if (db_list->size() == 0) {
292 void BDB_SQLITE::bdb_thread_cleanup(void)
294 sqlite3_thread_cleanup();
298 * Escape strings so SQLite is happy
300 * len is the length of the old string. Your new
301 * string must be long enough (max 2*old+1) to hold
302 * the escaped output.
304 void BDB_SQLITE::bdb_escape_string(JCR *jcr, char *snew, char *sold, int len)
331 * Escape binary object so that SQLite is happy
332 * Memory is stored in BDB struct, no need to free it
334 * TODO: this should be implemented (escape \0)
336 char *BDB_SQLITE::bdb_escape_object(JCR *jcr, char *old, int len)
339 int max = len*2; /* TODO: too big, should be *4/3 */
341 esc_obj = check_pool_memory_size(esc_obj, max);
342 l = bin_to_base64(esc_obj, max, old, len, true);
344 ASSERT(l < max); /* TODO: add check for l */
350 * Unescape binary object so that SQLIte is happy
352 * TODO: need to be implemented (escape \0)
355 void BDB_SQLITE::bdb_unescape_object(JCR *jcr, char *from, int32_t expected_len,
356 POOLMEM **dest, int32_t *dest_len)
363 *dest = check_pool_memory_size(*dest, expected_len+1);
364 base64_to_bin(*dest, expected_len+1, from, strlen(from));
365 *dest_len = expected_len;
366 (*dest)[expected_len] = 0;
370 * Start a transaction. This groups inserts and makes things
371 * more efficient. Usually started when inserting file attributes.
373 void BDB_SQLITE::bdb_start_transaction(JCR *jcr)
375 BDB_SQLITE *mdb = this;
378 jcr->attr = get_pool_memory(PM_FNAME);
381 jcr->ar = (ATTR_DBR *)malloc(sizeof(ATTR_DBR));
382 memset(jcr->ar, 0, sizeof(ATTR_DBR));
385 if (!mdb->m_allow_transactions) {
391 * Allow only 10,000 changes per transaction
393 if (mdb->m_transaction && mdb->changes > 10000) {
394 bdb_end_transaction(jcr);
396 if (!mdb->m_transaction) {
397 sql_query("BEGIN"); /* begin transaction */
398 Dmsg0(400, "Start SQLite transaction\n");
399 mdb->m_transaction = true;
404 void BDB_SQLITE::bdb_end_transaction(JCR *jcr)
406 BDB_SQLITE *mdb = this;
408 if (jcr && jcr->cached_attribute) {
409 Dmsg0(400, "Flush last cached attribute.\n");
410 if (!bdb_create_attributes_record(jcr, jcr->ar)) {
411 Jmsg1(jcr, M_FATAL, 0, _("Attribute create error. %s"), jcr->db->bdb_strerror());
413 jcr->cached_attribute = false;
416 if (!mdb->m_allow_transactions) {
421 if (mdb->m_transaction) {
422 sql_query("COMMIT"); /* end transaction */
423 mdb->m_transaction = false;
424 Dmsg1(400, "End SQLite transaction changes=%d\n", changes);
432 DB_RESULT_HANDLER *result_handler;
438 * Convert SQLite's callback into Bacula DB callback
440 static int sqlite_result_handler(void *arh_data, int num_fields, char **rows, char **col_names)
442 struct rh_data *rh_data = (struct rh_data *)arh_data;
444 /* The db_sql_query doesn't have access to m_results, so if we wan't to get
445 * fields information, we need to use col_names
447 if (!rh_data->initialized) {
448 rh_data->mdb->set_column_names(col_names, num_fields);
449 rh_data->initialized = true;
451 if (rh_data->result_handler) {
452 (*(rh_data->result_handler))(rh_data->ctx, num_fields, rows);
459 * Submit a general SQL command (cmd), and for each row returned,
460 * the result_handler is called with the ctx.
462 bool BDB_SQLITE::bdb_sql_query(const char *query, DB_RESULT_HANDLER *result_handler, void *ctx)
464 BDB_SQLITE *mdb = this;
467 struct rh_data rh_data;
469 Dmsg1(500, "db_sql_query starts with '%s'\n", query);
473 if (mdb->m_sqlite_errmsg) {
474 sqlite3_free(mdb->m_sqlite_errmsg);
475 mdb->m_sqlite_errmsg = NULL;
481 rh_data.initialized = false;
482 rh_data.result_handler = result_handler;
484 stat = sqlite3_exec(m_db_handle, query, sqlite_result_handler,
485 (void *)&rh_data, &m_sqlite_errmsg);
487 if (stat != SQLITE_OK) {
488 Mmsg(mdb->errmsg, _("Query failed: %s: ERR=%s\n"), query, sql_strerror());
489 Dmsg0(500, "db_sql_query finished\n");
492 Dmsg0(500, "db_sql_query finished\n");
502 * Submit a sqlite query and retrieve all the data
504 bool BDB_SQLITE::sql_query(const char *query, int flags)
508 BDB_SQLITE *mdb = this;
510 Dmsg1(500, "sql_query starts with '%s'\n", query);
513 if (mdb->m_sqlite_errmsg) {
514 sqlite3_free(mdb->m_sqlite_errmsg);
515 mdb->m_sqlite_errmsg = NULL;
518 stat = sqlite3_get_table(m_db_handle, (char *)query, &m_result,
519 &m_num_rows, &m_num_fields, &m_sqlite_errmsg);
521 mdb->m_row_number = 0; /* no row fetched */
522 if (stat != 0) { /* something went wrong */
523 mdb->m_num_rows = mdb->m_num_fields = 0;
524 Dmsg0(500, "sql_query finished\n");
526 Dmsg0(500, "sql_query finished\n");
532 void BDB_SQLITE::sql_free_result(void)
534 BDB_SQLITE *mdb = this;
539 mdb->m_fields = NULL;
542 sqlite3_free_table(mdb->m_result);
543 mdb->m_result = NULL;
545 mdb->m_col_names = NULL;
546 mdb->m_num_rows = mdb->m_num_fields = 0;
551 * Fetch one row at a time
553 SQL_ROW BDB_SQLITE::sql_fetch_row(void)
555 BDB_SQLITE *mdb = this;
556 if (!mdb->m_result || (mdb->m_row_number >= mdb->m_num_rows)) {
560 return &mdb->m_result[mdb->m_num_fields * mdb->m_row_number];
563 const char *BDB_SQLITE::sql_strerror(void)
565 BDB_SQLITE *mdb = this;
566 return mdb->m_sqlite_errmsg ? mdb->m_sqlite_errmsg : "unknown";
569 void BDB_SQLITE::sql_data_seek(int row)
571 BDB_SQLITE *mdb = this;
572 /* Set the row number to be returned on the next call to sql_fetch_row */
573 mdb->m_row_number = row;
576 int BDB_SQLITE::sql_affected_rows(void)
578 BDB_SQLITE *mdb = this;
579 return sqlite3_changes(mdb->m_db_handle);
582 uint64_t BDB_SQLITE::sql_insert_autokey_record(const char *query, const char *table_name)
584 BDB_SQLITE *mdb = this;
585 /* First execute the insert query and then retrieve the currval. */
586 if (!sql_query(query)) {
590 mdb->m_num_rows = sql_affected_rows();
591 if (mdb->m_num_rows != 1) {
597 return sqlite3_last_insert_rowid(mdb->m_db_handle);
600 SQL_FIELD *BDB_SQLITE::sql_fetch_field(void)
602 BDB_SQLITE *mdb = this;
605 /* We are in the middle of a db_sql_query and we want to get fields info */
606 if (mdb->m_col_names != NULL) {
607 if (mdb->m_num_fields > mdb->m_field_number) {
608 mdb->m_sql_field.name = mdb->m_col_names[mdb->m_field_number];
609 /* We don't have the maximum field length, so we can use 80 as
612 len = MAX(cstrlen(mdb->m_sql_field.name), 80/mdb->m_num_fields);
613 mdb->m_sql_field.max_length = len;
615 mdb->m_field_number++;
616 mdb->m_sql_field.type = 0; /* not numeric */
617 mdb->m_sql_field.flags = 1; /* not null */
618 return &mdb->m_sql_field;
619 } else { /* too much fetch_field() */
624 /* We are after a sql_query() that stores the result in m_results */
625 if (!mdb->m_fields || mdb->m_fields_size < mdb->m_num_fields) {
628 mdb->m_fields = NULL;
630 Dmsg1(500, "allocating space for %d fields\n", m_num_fields);
631 mdb->m_fields = (SQL_FIELD *)malloc(sizeof(SQL_FIELD) * mdb->m_num_fields);
632 mdb->m_fields_size = mdb->m_num_fields;
634 for (i = 0; i < mdb->m_num_fields; i++) {
635 Dmsg1(500, "filling field %d\n", i);
636 mdb->m_fields[i].name = mdb->m_result[i];
637 mdb->m_fields[i].max_length = cstrlen(mdb->m_fields[i].name);
638 for (j = 1; j <= mdb->m_num_rows; j++) {
639 if (mdb->m_result[i + mdb->m_num_fields * j]) {
640 len = (uint32_t)cstrlen(mdb->m_result[i + mdb->m_num_fields * j]);
644 if (len > mdb->m_fields[i].max_length) {
645 mdb->m_fields[i].max_length = len;
648 mdb->m_fields[i].type = 0;
649 mdb->m_fields[i].flags = 1; /* not null */
651 Dmsg4(500, "sql_fetch_field finds field '%s' has length='%d' type='%d' and IsNull=%d\n",
652 mdb->m_fields[i].name, mdb->m_fields[i].max_length, mdb->m_fields[i].type, mdb->m_fields[i].flags);
656 /* Increment field number for the next time around */
657 return &mdb->m_fields[mdb->m_field_number++];
660 bool BDB_SQLITE::sql_field_is_not_null(int field_type)
662 if (field_type == 1) {
668 bool BDB_SQLITE::sql_field_is_numeric(int field_type)
670 if (field_type == 1) {
680 bool BDB_SQLITE::sql_batch_start(JCR *jcr)
685 ret = sql_query("CREATE TEMPORARY TABLE batch ("
692 "DeltaSeq integer)");
698 /* Set error to something to abort operation */
703 bool BDB_SQLITE::sql_batch_end(JCR *jcr, const char *error)
713 bool BDB_SQLITE::sql_batch_insert(JCR *jcr, ATTR_DBR *ar)
715 BDB_SQLITE *mdb = this;
719 mdb->esc_name = check_pool_memory_size(mdb->esc_name, mdb->fnl*2+1);
720 bdb_escape_string(jcr, mdb->esc_name, mdb->fname, mdb->fnl);
722 mdb->esc_path = check_pool_memory_size(mdb->esc_path, mdb->pnl*2+1);
723 bdb_escape_string(jcr, mdb->esc_path, mdb->path, mdb->pnl);
725 if (ar->Digest == NULL || ar->Digest[0] == 0) {
731 Mmsg(mdb->cmd, "INSERT INTO batch VALUES "
732 "(%u,%s,'%s','%s','%s','%s',%u)",
733 ar->FileIndex, edit_int64(ar->JobId,ed1), mdb->esc_path,
734 mdb->esc_name, ar->attr, digest, ar->DeltaSeq);
736 return sql_query(mdb->cmd);
740 #endif /* HAVE_SQLITE3 */