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)
173 BDB_SQLITE *mdb = this;
176 if (mdb->m_connected) {
181 if ((errstat=rwl_init(&mdb->m_lock)) != 0) {
183 Mmsg1(&mdb->errmsg, _("Unable to initialize DB lock. ERR=%s\n"),
184 be.bstrerror(errstat));
191 len = strlen(working_directory) + strlen(mdb->m_db_name) + 5;
192 db_file = (char *)malloc(len);
193 strcpy(db_file, working_directory);
194 strcat(db_file, "/");
195 strcat(db_file, m_db_name);
196 strcat(db_file, ".db");
197 if (stat(db_file, &statbuf) != 0) {
198 Mmsg1(&mdb->errmsg, _("Database %s does not exist, please create it.\n"),
204 for (mdb->m_db_handle = NULL; !mdb->m_db_handle && retry++ < 10; ) {
205 ret = sqlite3_open(db_file, &mdb->m_db_handle);
206 if (ret != SQLITE_OK) {
207 mdb->m_sqlite_errmsg = (char *)sqlite3_errmsg(mdb->m_db_handle);
208 sqlite3_close(mdb->m_db_handle);
209 mdb->m_db_handle = NULL;
211 mdb->m_sqlite_errmsg = NULL;
214 Dmsg0(300, "sqlite_open\n");
215 if (!mdb->m_db_handle) {
219 if (mdb->m_db_handle == NULL) {
220 Mmsg2(&mdb->errmsg, _("Unable to open Database=%s. ERR=%s\n"),
221 db_file, mdb->m_sqlite_errmsg ? mdb->m_sqlite_errmsg : _("unknown"));
225 mdb->m_connected = true;
229 * Set busy handler to wait when we use mult_db_connections = true
231 sqlite3_busy_handler(mdb->m_db_handle, my_sqlite_busy_handler, NULL);
233 #if defined(SQLITE3_INIT_QUERY)
234 sql_query(SQLITE3_INIT_QUERY);
237 if (!bdb_check_version(jcr)) {
248 void BDB_SQLITE::bdb_close_database(JCR *jcr)
250 BDB_SQLITE *mdb = this;
252 if (mdb->m_connected) {
253 bdb_end_transaction(jcr);
257 if (mdb->m_ref_count == 0) {
258 if (mdb->m_connected) {
261 db_list->remove(mdb);
262 if (mdb->m_connected && mdb->m_db_handle) {
263 sqlite3_close(mdb->m_db_handle);
265 if (is_rwl_valid(&mdb->m_lock)) {
266 rwl_destroy(&mdb->m_lock);
268 free_pool_memory(mdb->errmsg);
269 free_pool_memory(mdb->cmd);
270 free_pool_memory(mdb->cached_path);
271 free_pool_memory(mdb->fname);
272 free_pool_memory(mdb->path);
273 free_pool_memory(mdb->esc_name);
274 free_pool_memory(mdb->esc_path);
275 free_pool_memory(mdb->esc_obj);
276 if (mdb->m_db_driver) {
277 free(mdb->m_db_driver);
279 if (mdb->m_db_name) {
280 free(mdb->m_db_name);
283 if (db_list->size() == 0) {
291 void BDB_SQLITE::bdb_thread_cleanup(void)
293 sqlite3_thread_cleanup();
297 * Escape strings so SQLite is happy
299 * len is the length of the old string. Your new
300 * string must be long enough (max 2*old+1) to hold
301 * the escaped output.
303 void BDB_SQLITE::bdb_escape_string(JCR *jcr, char *snew, char *sold, int len)
330 * Escape binary object so that SQLite is happy
331 * Memory is stored in BDB struct, no need to free it
333 * TODO: this should be implemented (escape \0)
335 char *BDB_SQLITE::bdb_escape_object(JCR *jcr, char *old, int len)
338 int max = len*2; /* TODO: too big, should be *4/3 */
340 esc_obj = check_pool_memory_size(esc_obj, max);
341 l = bin_to_base64(esc_obj, max, old, len, true);
343 ASSERT(l < max); /* TODO: add check for l */
349 * Unescape binary object so that SQLIte is happy
351 * TODO: need to be implemented (escape \0)
354 void BDB_SQLITE::bdb_unescape_object(JCR *jcr, char *from, int32_t expected_len,
355 POOLMEM **dest, int32_t *dest_len)
362 *dest = check_pool_memory_size(*dest, expected_len+1);
363 base64_to_bin(*dest, expected_len+1, from, strlen(from));
364 *dest_len = expected_len;
365 (*dest)[expected_len] = 0;
369 * Start a transaction. This groups inserts and makes things
370 * more efficient. Usually started when inserting file attributes.
372 void BDB_SQLITE::bdb_start_transaction(JCR *jcr)
374 BDB_SQLITE *mdb = this;
377 jcr->attr = get_pool_memory(PM_FNAME);
380 jcr->ar = (ATTR_DBR *)malloc(sizeof(ATTR_DBR));
381 memset(jcr->ar, 0, sizeof(ATTR_DBR));
384 if (!mdb->m_allow_transactions) {
390 * Allow only 10,000 changes per transaction
392 if (mdb->m_transaction && mdb->changes > 10000) {
393 bdb_end_transaction(jcr);
395 if (!mdb->m_transaction) {
396 sql_query("BEGIN"); /* begin transaction */
397 Dmsg0(400, "Start SQLite transaction\n");
398 mdb->m_transaction = true;
403 void BDB_SQLITE::bdb_end_transaction(JCR *jcr)
405 BDB_SQLITE *mdb = this;
407 if (jcr && jcr->cached_attribute) {
408 Dmsg0(400, "Flush last cached attribute.\n");
409 if (!bdb_create_attributes_record(jcr, jcr->ar)) {
410 Jmsg1(jcr, M_FATAL, 0, _("Attribute create error. %s"), jcr->db->bdb_strerror());
412 jcr->cached_attribute = false;
415 if (!mdb->m_allow_transactions) {
420 if (mdb->m_transaction) {
421 sql_query("COMMIT"); /* end transaction */
422 mdb->m_transaction = false;
423 Dmsg1(400, "End SQLite transaction changes=%d\n", changes);
431 DB_RESULT_HANDLER *result_handler;
437 * Convert SQLite's callback into Bacula DB callback
439 static int sqlite_result_handler(void *arh_data, int num_fields, char **rows, char **col_names)
441 struct rh_data *rh_data = (struct rh_data *)arh_data;
443 /* The db_sql_query doesn't have access to m_results, so if we wan't to get
444 * fields information, we need to use col_names
446 if (!rh_data->initialized) {
447 rh_data->mdb->set_column_names(col_names, num_fields);
448 rh_data->initialized = true;
450 if (rh_data->result_handler) {
451 (*(rh_data->result_handler))(rh_data->ctx, num_fields, rows);
458 * Submit a general SQL command (cmd), and for each row returned,
459 * the result_handler is called with the ctx.
461 bool BDB_SQLITE::bdb_sql_query(const char *query, DB_RESULT_HANDLER *result_handler, void *ctx)
463 BDB_SQLITE *mdb = this;
466 struct rh_data rh_data;
468 Dmsg1(500, "db_sql_query starts with '%s'\n", query);
472 if (mdb->m_sqlite_errmsg) {
473 sqlite3_free(mdb->m_sqlite_errmsg);
474 mdb->m_sqlite_errmsg = NULL;
480 rh_data.initialized = false;
481 rh_data.result_handler = result_handler;
483 stat = sqlite3_exec(m_db_handle, query, sqlite_result_handler,
484 (void *)&rh_data, &m_sqlite_errmsg);
486 if (stat != SQLITE_OK) {
487 Mmsg(mdb->errmsg, _("Query failed: %s: ERR=%s\n"), query, sql_strerror());
488 Dmsg0(500, "db_sql_query finished\n");
491 Dmsg0(500, "db_sql_query finished\n");
501 * Submit a sqlite query and retrieve all the data
503 bool BDB_SQLITE::sql_query(const char *query, int flags)
507 BDB_SQLITE *mdb = this;
509 Dmsg1(500, "sql_query starts with '%s'\n", query);
512 if (mdb->m_sqlite_errmsg) {
513 sqlite3_free(mdb->m_sqlite_errmsg);
514 mdb->m_sqlite_errmsg = NULL;
517 stat = sqlite3_get_table(m_db_handle, (char *)query, &m_result,
518 &m_num_rows, &m_num_fields, &m_sqlite_errmsg);
520 mdb->m_row_number = 0; /* no row fetched */
521 if (stat != 0) { /* something went wrong */
522 mdb->m_num_rows = mdb->m_num_fields = 0;
523 Dmsg0(500, "sql_query finished\n");
525 Dmsg0(500, "sql_query finished\n");
531 void BDB_SQLITE::sql_free_result(void)
533 BDB_SQLITE *mdb = this;
538 mdb->m_fields = NULL;
541 sqlite3_free_table(mdb->m_result);
542 mdb->m_result = NULL;
544 mdb->m_col_names = NULL;
545 mdb->m_num_rows = mdb->m_num_fields = 0;
550 * Fetch one row at a time
552 SQL_ROW BDB_SQLITE::sql_fetch_row(void)
554 BDB_SQLITE *mdb = this;
555 if (!mdb->m_result || (mdb->m_row_number >= mdb->m_num_rows)) {
559 return &mdb->m_result[mdb->m_num_fields * mdb->m_row_number];
562 const char *BDB_SQLITE::sql_strerror(void)
564 BDB_SQLITE *mdb = this;
565 return mdb->m_sqlite_errmsg ? mdb->m_sqlite_errmsg : "unknown";
568 void BDB_SQLITE::sql_data_seek(int row)
570 BDB_SQLITE *mdb = this;
571 /* Set the row number to be returned on the next call to sql_fetch_row */
572 mdb->m_row_number = row;
575 int BDB_SQLITE::sql_affected_rows(void)
577 BDB_SQLITE *mdb = this;
578 return sqlite3_changes(mdb->m_db_handle);
581 uint64_t BDB_SQLITE::sql_insert_autokey_record(const char *query, const char *table_name)
583 BDB_SQLITE *mdb = this;
584 /* First execute the insert query and then retrieve the currval. */
585 if (!sql_query(query)) {
589 mdb->m_num_rows = sql_affected_rows();
590 if (mdb->m_num_rows != 1) {
596 return sqlite3_last_insert_rowid(mdb->m_db_handle);
599 SQL_FIELD *BDB_SQLITE::sql_fetch_field(void)
601 BDB_SQLITE *mdb = this;
604 /* We are in the middle of a db_sql_query and we want to get fields info */
605 if (mdb->m_col_names != NULL) {
606 if (mdb->m_num_fields > mdb->m_field_number) {
607 mdb->m_sql_field.name = mdb->m_col_names[mdb->m_field_number];
608 /* We don't have the maximum field length, so we can use 80 as
611 len = MAX(cstrlen(mdb->m_sql_field.name), 80/mdb->m_num_fields);
612 mdb->m_sql_field.max_length = len;
614 mdb->m_field_number++;
615 mdb->m_sql_field.type = 0; /* not numeric */
616 mdb->m_sql_field.flags = 1; /* not null */
617 return &mdb->m_sql_field;
618 } else { /* too much fetch_field() */
623 /* We are after a sql_query() that stores the result in m_results */
624 if (!mdb->m_fields || mdb->m_fields_size < mdb->m_num_fields) {
627 mdb->m_fields = NULL;
629 Dmsg1(500, "allocating space for %d fields\n", m_num_fields);
630 mdb->m_fields = (SQL_FIELD *)malloc(sizeof(SQL_FIELD) * mdb->m_num_fields);
631 mdb->m_fields_size = mdb->m_num_fields;
633 for (i = 0; i < mdb->m_num_fields; i++) {
634 Dmsg1(500, "filling field %d\n", i);
635 mdb->m_fields[i].name = mdb->m_result[i];
636 mdb->m_fields[i].max_length = cstrlen(mdb->m_fields[i].name);
637 for (j = 1; j <= mdb->m_num_rows; j++) {
638 if (mdb->m_result[i + mdb->m_num_fields * j]) {
639 len = (uint32_t)cstrlen(mdb->m_result[i + mdb->m_num_fields * j]);
643 if (len > mdb->m_fields[i].max_length) {
644 mdb->m_fields[i].max_length = len;
647 mdb->m_fields[i].type = 0;
648 mdb->m_fields[i].flags = 1; /* not null */
650 Dmsg4(500, "sql_fetch_field finds field '%s' has length='%d' type='%d' and IsNull=%d\n",
651 mdb->m_fields[i].name, mdb->m_fields[i].max_length, mdb->m_fields[i].type, mdb->m_fields[i].flags);
655 /* Increment field number for the next time around */
656 return &mdb->m_fields[mdb->m_field_number++];
659 bool BDB_SQLITE::sql_field_is_not_null(int field_type)
661 if (field_type == 1) {
667 bool BDB_SQLITE::sql_field_is_numeric(int field_type)
669 if (field_type == 1) {
679 bool BDB_SQLITE::sql_batch_start(JCR *jcr)
684 ret = sql_query("CREATE TEMPORARY TABLE batch ("
691 "DeltaSeq integer)");
697 /* Set error to something to abort operation */
702 bool BDB_SQLITE::sql_batch_end(JCR *jcr, const char *error)
712 bool BDB_SQLITE::sql_batch_insert(JCR *jcr, ATTR_DBR *ar)
714 BDB_SQLITE *mdb = this;
718 mdb->esc_name = check_pool_memory_size(mdb->esc_name, mdb->fnl*2+1);
719 bdb_escape_string(jcr, mdb->esc_name, mdb->fname, mdb->fnl);
721 mdb->esc_path = check_pool_memory_size(mdb->esc_path, mdb->pnl*2+1);
722 bdb_escape_string(jcr, mdb->esc_path, mdb->path, mdb->pnl);
724 if (ar->Digest == NULL || ar->Digest[0] == 0) {
730 Mmsg(mdb->cmd, "INSERT INTO batch VALUES "
731 "(%u,%s,'%s','%s','%s','%s',%u)",
732 ar->FileIndex, edit_int64(ar->JobId,ed1), mdb->esc_path,
733 mdb->esc_name, ar->attr, digest, ar->DeltaSeq);
735 return sql_query(mdb->cmd);
739 #endif /* HAVE_SQLITE3 */