2 Bacula® - The Network Backup Solution
4 Copyright (C) 2000-2011 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 three of the GNU Affero 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 Affero 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
33 * Major rewrite by Marco van Wieringen, January 2010 for catalog refactoring.
43 #include "bdb_sqlite.h"
45 /* -----------------------------------------------------------------------
47 * SQLite dependent defines and subroutines
49 * -----------------------------------------------------------------------
53 * List of open databases
55 static dlist *db_list = NULL;
57 static pthread_mutex_t mutex = PTHREAD_MUTEX_INITIALIZER;
60 * When using mult_db_connections = true,
61 * sqlite can be BUSY. We just need sleep a little in this case.
63 static int sqlite_busy_handler(void *arg, int calls)
69 B_DB_SQLITE::B_DB_SQLITE(JCR *jcr,
70 const char *db_driver,
73 const char *db_password,
74 const char *db_address,
76 const char *db_socket,
77 bool mult_db_connections,
78 bool disable_batch_insert)
81 * Initialize the parent class members.
83 m_db_interface_type = SQL_INTERFACE_TYPE_SQLITE3;
84 m_db_type = SQL_TYPE_SQLITE3;
85 m_db_driver = bstrdup("SQLite3");
86 m_db_name = bstrdup(db_name);
87 if (disable_batch_insert) {
88 m_disabled_batch_insert = true;
89 m_have_batch_insert = false;
91 m_disabled_batch_insert = false;
92 #if defined(USE_BATCH_FILE_INSERT)
93 #if defined(HAVE_SQLITE3_THREADSAFE)
94 m_have_batch_insert = sqlite3_threadsafe();
96 m_have_batch_insert = false;
97 #endif /* HAVE_SQLITE3_THREADSAFE */
99 m_have_batch_insert = false;
100 #endif /* USE_BATCH_FILE_INSERT */
102 errmsg = get_pool_memory(PM_EMSG); /* get error message buffer */
104 cmd = get_pool_memory(PM_EMSG); /* get command buffer */
105 cached_path = get_pool_memory(PM_FNAME);
108 fname = get_pool_memory(PM_FNAME);
109 path = get_pool_memory(PM_FNAME);
110 esc_name = get_pool_memory(PM_FNAME);
111 esc_path = get_pool_memory(PM_FNAME);
112 esc_obj = get_pool_memory(PM_FNAME);
113 m_allow_transactions = mult_db_connections;
115 /* At this time, when mult_db_connections == true, this is for
116 * specific console command such as bvfs or batch mode, and we don't
117 * want to share a batch mode or bvfs. In the future, we can change
118 * the creation function to add this parameter.
120 m_dedicated = mult_db_connections;
123 * Initialize the private members.
127 m_sqlite_errmsg = NULL;
130 * Put the db in the list.
132 if (db_list == NULL) {
133 db_list = New(dlist(this, &this->m_link));
135 db_list->append(this);
138 B_DB_SQLITE::~B_DB_SQLITE()
143 * Now actually open the database. This can generate errors,
144 * which are returned in the errmsg
146 * DO NOT close the database or delete mdb here !!!!
148 bool B_DB_SQLITE::db_open_database(JCR *jcr)
164 if ((errstat=rwl_init(&m_lock)) != 0) {
166 Mmsg1(&errmsg, _("Unable to initialize DB lock. ERR=%s\n"),
167 be.bstrerror(errstat));
174 len = strlen(working_directory) + strlen(m_db_name) + 5;
175 db_path = (char *)malloc(len);
176 strcpy(db_path, working_directory);
177 strcat(db_path, "/");
178 strcat(db_path, m_db_name);
179 strcat(db_path, ".db");
180 if (stat(db_path, &statbuf) != 0) {
181 Mmsg1(&errmsg, _("Database %s does not exist, please create it.\n"),
187 for (m_db_handle = NULL; !m_db_handle && retry++ < 10; ) {
188 ret = sqlite3_open(db_path, &m_db_handle);
189 if (ret != SQLITE_OK) {
190 m_sqlite_errmsg = (char *)sqlite3_errmsg(m_db_handle);
191 sqlite3_close(m_db_handle);
194 m_sqlite_errmsg = NULL;
197 Dmsg0(300, "sqlite_open\n");
202 if (m_db_handle == NULL) {
203 Mmsg2(&errmsg, _("Unable to open Database=%s. ERR=%s\n"),
204 db_path, m_sqlite_errmsg ? m_sqlite_errmsg : _("unknown"));
212 * Set busy handler to wait when we use mult_db_connections = true
214 sqlite3_busy_handler(m_db_handle, sqlite_busy_handler, NULL);
216 #if defined(SQLITE3_INIT_QUERY)
217 sql_query(SQLITE3_INIT_QUERY);
220 if (!check_tables_version(jcr, this)) {
231 void B_DB_SQLITE::db_close_database(JCR *jcr)
234 db_end_transaction(jcr);
238 if (m_ref_count == 0) {
240 db_list->remove(this);
241 if (m_connected && m_db_handle) {
242 sqlite3_close(m_db_handle);
244 if (rwl_is_init(&m_lock)) {
245 rwl_destroy(&m_lock);
247 free_pool_memory(errmsg);
248 free_pool_memory(cmd);
249 free_pool_memory(cached_path);
250 free_pool_memory(fname);
251 free_pool_memory(path);
252 free_pool_memory(esc_name);
253 free_pool_memory(esc_path);
254 free_pool_memory(esc_obj);
262 if (db_list->size() == 0) {
270 void B_DB_SQLITE::db_thread_cleanup(void)
272 sqlite3_thread_cleanup();
276 * Escape strings so that SQLite is happy
278 * NOTE! len is the length of the old string. Your new
279 * string must be long enough (max 2*old+1) to hold
280 * the escaped output.
282 void B_DB_SQLITE::db_escape_string(JCR *jcr, char *snew, char *old, int len)
309 * Escape binary object so that SQLite is happy
310 * Memory is stored in B_DB struct, no need to free it
312 * TODO: this should be implemented (escape \0)
314 char *B_DB_SQLITE::db_escape_object(JCR *jcr, char *old, int len)
317 int max = len*2; /* TODO: too big, should be *4/3 */
319 esc_obj = check_pool_memory_size(esc_obj, max);
320 l = bin_to_base64(esc_obj, max, old, len, true);
322 ASSERT(l < max); /* TODO: add check for l */
328 * Unescape binary object so that SQLIte is happy
330 * TODO: need to be implemented (escape \0)
333 void B_DB_SQLITE::db_unescape_object(JCR *jcr, char *from, int32_t expected_len,
334 POOLMEM **dest, int32_t *dest_len)
341 *dest = check_pool_memory_size(*dest, expected_len+1);
342 base64_to_bin(*dest, expected_len+1, from, strlen(from));
343 *dest_len = expected_len;
344 (*dest)[expected_len]=0;
348 * Start a transaction. This groups inserts and makes things
349 * much more efficient. Usually started when inserting
352 void B_DB_SQLITE::db_start_transaction(JCR *jcr)
355 jcr->attr = get_pool_memory(PM_FNAME);
358 jcr->ar = (ATTR_DBR *)malloc(sizeof(ATTR_DBR));
361 if (!m_allow_transactions) {
367 * Allow only 10,000 changes per transaction
369 if (m_transaction && changes > 10000) {
370 db_end_transaction(jcr);
372 if (!m_transaction) {
373 sql_query("BEGIN"); /* begin transaction */
374 Dmsg0(400, "Start SQLite transaction\n");
375 m_transaction = true;
380 void B_DB_SQLITE::db_end_transaction(JCR *jcr)
382 if (jcr && jcr->cached_attribute) {
383 Dmsg0(400, "Flush last cached attribute.\n");
384 if (!db_create_attributes_record(jcr, this, jcr->ar)) {
385 Jmsg1(jcr, M_FATAL, 0, _("Attribute create error. %s"), db_strerror(jcr->db));
387 jcr->cached_attribute = false;
390 if (!m_allow_transactions) {
396 sql_query("COMMIT"); /* end transaction */
397 m_transaction = false;
398 Dmsg1(400, "End SQLite transaction changes=%d\n", changes);
406 DB_RESULT_HANDLER *result_handler;
412 * Convert SQLite's callback into Bacula DB callback
414 static int sqlite_result_handler(void *arh_data, int num_fields, char **rows, char **col_names)
416 struct rh_data *rh_data = (struct rh_data *)arh_data;
418 /* The db_sql_query doesn't have access to m_results, so if we wan't to get
419 * fields information, we need to use col_names
421 if (!rh_data->initialized) {
422 rh_data->mdb->set_column_names(col_names, num_fields);
423 rh_data->initialized = true;
425 if (rh_data->result_handler) {
426 (*(rh_data->result_handler))(rh_data->ctx, num_fields, rows);
433 * Submit a general SQL command (cmd), and for each row returned,
434 * the result_handler is called with the ctx.
436 bool B_DB_SQLITE::db_sql_query(const char *query, DB_RESULT_HANDLER *result_handler, void *ctx)
440 struct rh_data rh_data;
442 Dmsg1(500, "db_sql_query starts with '%s'\n", query);
445 if (m_sqlite_errmsg) {
446 sqlite3_free(m_sqlite_errmsg);
447 m_sqlite_errmsg = NULL;
453 rh_data.initialized = false;
454 rh_data.result_handler = result_handler;
456 stat = sqlite3_exec(m_db_handle, query, sqlite_result_handler,
457 (void *)&rh_data, &m_sqlite_errmsg);
459 if (stat != SQLITE_OK) {
460 Mmsg(errmsg, _("Query failed: %s: ERR=%s\n"), query, sql_strerror());
461 Dmsg0(500, "db_sql_query finished\n");
464 Dmsg0(500, "db_sql_query finished\n");
474 * Submit a sqlite query and retrieve all the data
476 bool B_DB_SQLITE::sql_query(const char *query, int flags)
481 Dmsg1(500, "sql_query starts with '%s'\n", query);
484 if (m_sqlite_errmsg) {
485 sqlite3_free(m_sqlite_errmsg);
486 m_sqlite_errmsg = NULL;
489 stat = sqlite3_get_table(m_db_handle, (char *)query, &m_result,
490 &m_num_rows, &m_num_fields, &m_sqlite_errmsg);
492 m_row_number = 0; /* no row fetched */
493 if (stat != 0) { /* something went wrong */
494 m_num_rows = m_num_fields = 0;
495 Dmsg0(500, "sql_query finished\n");
497 Dmsg0(500, "sql_query finished\n");
503 void B_DB_SQLITE::sql_free_result(void)
511 sqlite3_free_table(m_result);
515 m_num_rows = m_num_fields = 0;
520 * Fetch one row at a time
522 SQL_ROW B_DB_SQLITE::sql_fetch_row(void)
524 if (!m_result || (m_row_number >= m_num_rows)) {
528 return &m_result[m_num_fields * m_row_number];
531 const char *B_DB_SQLITE::sql_strerror(void)
533 return m_sqlite_errmsg ? m_sqlite_errmsg : "unknown";
536 void B_DB_SQLITE::sql_data_seek(int row)
539 * Set the row number to be returned on the next call to sql_fetch_row
544 int B_DB_SQLITE::sql_affected_rows(void)
546 return sqlite3_changes(m_db_handle);
549 uint64_t B_DB_SQLITE::sql_insert_autokey_record(const char *query, const char *table_name)
552 * First execute the insert query and then retrieve the currval.
554 if (!sql_query(query)) {
558 m_num_rows = sql_affected_rows();
559 if (m_num_rows != 1) {
565 return sqlite3_last_insert_rowid(m_db_handle);
568 SQL_FIELD *B_DB_SQLITE::sql_fetch_field(void)
572 /* We are in the middle of a db_sql_query and we want to get fields info */
573 if (m_col_names != NULL) {
574 if (m_num_fields > m_field_number) {
575 m_sql_field.name = m_col_names[m_field_number];
576 /* We don't have the maximum field length, so we can use 80 as
579 len = MAX(cstrlen(m_sql_field.name), 80/m_num_fields);
580 m_sql_field.max_length = len;
583 m_sql_field.type = 0; /* not numeric */
584 m_sql_field.flags = 1; /* not null */
586 } else { /* too much fetch_field() */
591 /* We are after a sql_query() that stores the result in m_results */
592 if (!m_fields || m_fields_size < m_num_fields) {
597 Dmsg1(500, "allocating space for %d fields\n", m_num_fields);
598 m_fields = (SQL_FIELD *)malloc(sizeof(SQL_FIELD) * m_num_fields);
599 m_fields_size = m_num_fields;
601 for (i = 0; i < m_num_fields; i++) {
602 Dmsg1(500, "filling field %d\n", i);
603 m_fields[i].name = m_result[i];
604 m_fields[i].max_length = cstrlen(m_fields[i].name);
605 for (j = 1; j <= m_num_rows; j++) {
606 if (m_result[i + m_num_fields * j]) {
607 len = (uint32_t)cstrlen(m_result[i + m_num_fields * j]);
611 if (len > m_fields[i].max_length) {
612 m_fields[i].max_length = len;
615 m_fields[i].type = 0;
616 m_fields[i].flags = 1; /* not null */
618 Dmsg4(500, "sql_fetch_field finds field '%s' has length='%d' type='%d' and IsNull=%d\n",
619 m_fields[i].name, m_fields[i].max_length, m_fields[i].type, m_fields[i].flags);
624 * Increment field number for the next time around
626 return &m_fields[m_field_number++];
629 bool B_DB_SQLITE::sql_field_is_not_null(int field_type)
631 switch (field_type) {
639 bool B_DB_SQLITE::sql_field_is_numeric(int field_type)
641 switch (field_type) {
653 bool B_DB_SQLITE::sql_batch_start(JCR *jcr)
658 retval = sql_query("CREATE TEMPORARY TABLE batch ("
665 "DeltaSeq integer)");
671 /* set error to something to abort operation */
676 bool B_DB_SQLITE::sql_batch_end(JCR *jcr, const char *error)
687 bool B_DB_SQLITE::sql_batch_insert(JCR *jcr, ATTR_DBR *ar)
692 esc_name = check_pool_memory_size(esc_name, fnl*2+1);
693 db_escape_string(jcr, esc_name, fname, fnl);
695 esc_path = check_pool_memory_size(esc_path, pnl*2+1);
696 db_escape_string(jcr, esc_path, path, pnl);
698 if (ar->Digest == NULL || ar->Digest[0] == 0) {
704 Mmsg(cmd, "INSERT INTO batch VALUES "
705 "(%u,%s,'%s','%s','%s','%s',%u)",
706 ar->FileIndex, edit_int64(ar->JobId,ed1), esc_path,
707 esc_name, ar->attr, digest, ar->DeltaSeq);
709 return sql_query(cmd);
713 * Initialize database data structure. In principal this should
714 * never have errors, or it is really fatal.
716 B_DB *db_init_database(JCR *jcr, const char *db_driver, const char *db_name,
717 const char *db_user, const char *db_password,
718 const char *db_address, int db_port,
719 const char *db_socket, bool mult_db_connections,
720 bool disable_batch_insert)
724 P(mutex); /* lock DB queue */
726 * Look to see if DB already open
728 if (db_list && !mult_db_connections) {
729 foreach_dlist(mdb, db_list) {
730 if (mdb->db_match_database(db_driver, db_name, db_address, db_port)) {
731 Dmsg1(300, "DB REopen %s\n", db_name);
732 mdb->increment_refcount();
737 Dmsg0(300, "db_init_database first time\n");
738 mdb = New(B_DB_SQLITE(jcr, db_driver, db_name, db_user, db_password,
739 db_address, db_port, db_socket, mult_db_connections,
740 disable_batch_insert));
747 #endif /* HAVE_SQLITE3 */