2 Bacula(R) - The Network Backup Solution
4 Copyright (C) 2000-2015 Kern Sibbald
5 Copyright (C) 2000-2014 Free Software Foundation Europe e.V.
7 The original author of Bacula is Kern Sibbald, with contributions
8 from many others, a complete list can be found in the file AUTHORS.
10 You may use this file and others of this release according to the
11 license defined in the LICENSE file, which includes the Affero General
12 Public License, v3.0 ("AGPLv3") and some additional permissions and
13 terms pursuant to its AGPLv3 Section 7.
15 This notice must be preserved when any source code is
16 conveyed and/or propagated.
18 Bacula(R) is a registered trademark of Kern Sibbald.
21 * Bacula Catalog Database routines specific to SQLite
23 * Written by Kern Sibbald, January 2002
25 * Note: at one point, this file was changed to class based by a certain
26 * programmer, and other than "wrapping" in a class, which is a trivial
27 * change for a C++ programmer, nothing substantial was done, yet all the
28 * code was recommitted under this programmer's name. Consequently, we
29 * undo those changes here.
38 #define __BDB_SQLITE_H_ 1
39 #include "bdb_sqlite.h"
41 /* -----------------------------------------------------------------------
43 * SQLite dependent defines and subroutines
45 * -----------------------------------------------------------------------
48 /* List of open databases */
49 static dlist *db_list = NULL;
51 static pthread_mutex_t mutex = PTHREAD_MUTEX_INITIALIZER;
54 * When using mult_db_connections
55 * sqlite can be BUSY. We just need sleep a little in this case.
57 static int my_sqlite_busy_handler(void *arg, int calls)
63 BDB_SQLITE::BDB_SQLITE()
65 BDB_SQLITE *mdb = this;
67 if (db_list == NULL) {
68 db_list = New(dlist(mdb, &mdb->m_link));
70 mdb->m_db_driver_type = SQL_DRIVER_TYPE_SQLITE3;
71 mdb->m_db_type = SQL_TYPE_SQLITE3;
72 mdb->m_db_driver = bstrdup("SQLite3");
74 mdb->errmsg = get_pool_memory(PM_EMSG); /* get error message buffer */
76 mdb->cmd = get_pool_memory(PM_EMSG); /* get command buffer */
77 mdb->cached_path = get_pool_memory(PM_FNAME);
78 mdb->cached_path_id = 0;
80 mdb->fname = get_pool_memory(PM_FNAME);
81 mdb->path = get_pool_memory(PM_FNAME);
82 mdb->esc_name = get_pool_memory(PM_FNAME);
83 mdb->esc_path = get_pool_memory(PM_FNAME);
84 mdb->esc_obj = get_pool_memory(PM_FNAME);
85 mdb->m_use_fatal_jmsg = true;
87 /* Initialize the private members. */
88 mdb->m_db_handle = NULL;
90 mdb->m_sqlite_errmsg = NULL;
92 db_list->append(this);
95 BDB_SQLITE::~BDB_SQLITE()
100 * Initialize database data structure. In principal this should
101 * never have errors, or it is really fatal.
103 BDB *db_init_database(JCR *jcr, const char *db_driver, const char *db_name,
104 const char *db_user, const char *db_password,
105 const char *db_address, int db_port,
106 const char *db_socket, bool mult_db_connections,
107 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));
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 */