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) {
242 db_list->remove(this);
243 if (m_connected && m_db_handle) {
244 sqlite3_close(m_db_handle);
246 if (rwl_is_init(&m_lock)) {
247 rwl_destroy(&m_lock);
249 free_pool_memory(errmsg);
250 free_pool_memory(cmd);
251 free_pool_memory(cached_path);
252 free_pool_memory(fname);
253 free_pool_memory(path);
254 free_pool_memory(esc_name);
255 free_pool_memory(esc_path);
256 free_pool_memory(esc_obj);
264 if (db_list->size() == 0) {
272 void B_DB_SQLITE::db_thread_cleanup(void)
274 sqlite3_thread_cleanup();
278 * Escape strings so that SQLite is happy
280 * NOTE! len is the length of the old string. Your new
281 * string must be long enough (max 2*old+1) to hold
282 * the escaped output.
284 void B_DB_SQLITE::db_escape_string(JCR *jcr, char *snew, char *old, int len)
311 * Escape binary object so that SQLite is happy
312 * Memory is stored in B_DB struct, no need to free it
314 * TODO: this should be implemented (escape \0)
316 char *B_DB_SQLITE::db_escape_object(JCR *jcr, char *old, int len)
319 int max = len*2; /* TODO: too big, should be *4/3 */
321 esc_obj = check_pool_memory_size(esc_obj, max);
322 l = bin_to_base64(esc_obj, max, old, len, true);
324 ASSERT(l < max); /* TODO: add check for l */
330 * Unescape binary object so that SQLIte is happy
332 * TODO: need to be implemented (escape \0)
335 void B_DB_SQLITE::db_unescape_object(JCR *jcr, char *from, int32_t expected_len,
336 POOLMEM **dest, int32_t *dest_len)
343 *dest = check_pool_memory_size(*dest, expected_len+1);
344 base64_to_bin(*dest, expected_len+1, from, strlen(from));
345 *dest_len = expected_len;
346 (*dest)[expected_len]=0;
350 * Start a transaction. This groups inserts and makes things
351 * much more efficient. Usually started when inserting
354 void B_DB_SQLITE::db_start_transaction(JCR *jcr)
357 jcr->attr = get_pool_memory(PM_FNAME);
360 jcr->ar = (ATTR_DBR *)malloc(sizeof(ATTR_DBR));
363 if (!m_allow_transactions) {
369 * Allow only 10,000 changes per transaction
371 if (m_transaction && changes > 10000) {
372 db_end_transaction(jcr);
374 if (!m_transaction) {
375 sql_query("BEGIN"); /* begin transaction */
376 Dmsg0(400, "Start SQLite transaction\n");
377 m_transaction = true;
382 void B_DB_SQLITE::db_end_transaction(JCR *jcr)
384 if (jcr && jcr->cached_attribute) {
385 Dmsg0(400, "Flush last cached attribute.\n");
386 if (!db_create_attributes_record(jcr, this, jcr->ar)) {
387 Jmsg1(jcr, M_FATAL, 0, _("Attribute create error. %s"), db_strerror(jcr->db));
389 jcr->cached_attribute = false;
392 if (!m_allow_transactions) {
398 sql_query("COMMIT"); /* end transaction */
399 m_transaction = false;
400 Dmsg1(400, "End SQLite transaction changes=%d\n", changes);
408 DB_RESULT_HANDLER *result_handler;
414 * Convert SQLite's callback into Bacula DB callback
416 static int sqlite_result_handler(void *arh_data, int num_fields, char **rows, char **col_names)
418 struct rh_data *rh_data = (struct rh_data *)arh_data;
420 /* The db_sql_query doesn't have access to m_results, so if we wan't to get
421 * fields information, we need to use col_names
423 if (!rh_data->initialized) {
424 rh_data->mdb->set_column_names(col_names, num_fields);
425 rh_data->initialized = true;
427 if (rh_data->result_handler) {
428 (*(rh_data->result_handler))(rh_data->ctx, num_fields, rows);
435 * Submit a general SQL command (cmd), and for each row returned,
436 * the result_handler is called with the ctx.
438 bool B_DB_SQLITE::db_sql_query(const char *query, DB_RESULT_HANDLER *result_handler, void *ctx)
442 struct rh_data rh_data;
444 Dmsg1(500, "db_sql_query starts with '%s'\n", query);
447 if (m_sqlite_errmsg) {
448 sqlite3_free(m_sqlite_errmsg);
449 m_sqlite_errmsg = NULL;
455 rh_data.initialized = false;
456 rh_data.result_handler = result_handler;
458 stat = sqlite3_exec(m_db_handle, query, sqlite_result_handler,
459 (void *)&rh_data, &m_sqlite_errmsg);
461 if (stat != SQLITE_OK) {
462 Mmsg(errmsg, _("Query failed: %s: ERR=%s\n"), query, sql_strerror());
463 Dmsg0(500, "db_sql_query finished\n");
466 Dmsg0(500, "db_sql_query finished\n");
476 * Submit a sqlite query and retrieve all the data
478 bool B_DB_SQLITE::sql_query(const char *query, int flags)
483 Dmsg1(500, "sql_query starts with '%s'\n", query);
486 if (m_sqlite_errmsg) {
487 sqlite3_free(m_sqlite_errmsg);
488 m_sqlite_errmsg = NULL;
491 stat = sqlite3_get_table(m_db_handle, (char *)query, &m_result,
492 &m_num_rows, &m_num_fields, &m_sqlite_errmsg);
494 m_row_number = 0; /* no row fetched */
495 if (stat != 0) { /* something went wrong */
496 m_num_rows = m_num_fields = 0;
497 Dmsg0(500, "sql_query finished\n");
499 Dmsg0(500, "sql_query finished\n");
505 void B_DB_SQLITE::sql_free_result(void)
513 sqlite3_free_table(m_result);
517 m_num_rows = m_num_fields = 0;
522 * Fetch one row at a time
524 SQL_ROW B_DB_SQLITE::sql_fetch_row(void)
526 if (!m_result || (m_row_number >= m_num_rows)) {
530 return &m_result[m_num_fields * m_row_number];
533 const char *B_DB_SQLITE::sql_strerror(void)
535 return m_sqlite_errmsg ? m_sqlite_errmsg : "unknown";
538 void B_DB_SQLITE::sql_data_seek(int row)
541 * Set the row number to be returned on the next call to sql_fetch_row
546 int B_DB_SQLITE::sql_affected_rows(void)
548 return sqlite3_changes(m_db_handle);
551 uint64_t B_DB_SQLITE::sql_insert_autokey_record(const char *query, const char *table_name)
554 * First execute the insert query and then retrieve the currval.
556 if (!sql_query(query)) {
560 m_num_rows = sql_affected_rows();
561 if (m_num_rows != 1) {
567 return sqlite3_last_insert_rowid(m_db_handle);
570 SQL_FIELD *B_DB_SQLITE::sql_fetch_field(void)
574 /* We are in the middle of a db_sql_query and we want to get fields info */
575 if (m_col_names != NULL) {
576 if (m_num_fields > m_field_number) {
577 m_sql_field.name = m_col_names[m_field_number];
578 /* We don't have the maximum field length, so we can use 80 as
581 len = MAX(cstrlen(m_sql_field.name), 80/m_num_fields);
582 m_sql_field.max_length = len;
585 m_sql_field.type = 0; /* not numeric */
586 m_sql_field.flags = 1; /* not null */
588 } else { /* too much fetch_field() */
593 /* We are after a sql_query() that stores the result in m_results */
594 if (!m_fields || m_fields_size < m_num_fields) {
599 Dmsg1(500, "allocating space for %d fields\n", m_num_fields);
600 m_fields = (SQL_FIELD *)malloc(sizeof(SQL_FIELD) * m_num_fields);
601 m_fields_size = m_num_fields;
603 for (i = 0; i < m_num_fields; i++) {
604 Dmsg1(500, "filling field %d\n", i);
605 m_fields[i].name = m_result[i];
606 m_fields[i].max_length = cstrlen(m_fields[i].name);
607 for (j = 1; j <= m_num_rows; j++) {
608 if (m_result[i + m_num_fields * j]) {
609 len = (uint32_t)cstrlen(m_result[i + m_num_fields * j]);
613 if (len > m_fields[i].max_length) {
614 m_fields[i].max_length = len;
617 m_fields[i].type = 0;
618 m_fields[i].flags = 1; /* not null */
620 Dmsg4(500, "sql_fetch_field finds field '%s' has length='%d' type='%d' and IsNull=%d\n",
621 m_fields[i].name, m_fields[i].max_length, m_fields[i].type, m_fields[i].flags);
626 * Increment field number for the next time around
628 return &m_fields[m_field_number++];
631 bool B_DB_SQLITE::sql_field_is_not_null(int field_type)
633 switch (field_type) {
641 bool B_DB_SQLITE::sql_field_is_numeric(int field_type)
643 switch (field_type) {
655 bool B_DB_SQLITE::sql_batch_start(JCR *jcr)
660 retval = sql_query("CREATE TEMPORARY TABLE batch ("
667 "DeltaSeq integer)");
673 /* set error to something to abort operation */
678 bool B_DB_SQLITE::sql_batch_end(JCR *jcr, const char *error)
689 bool B_DB_SQLITE::sql_batch_insert(JCR *jcr, ATTR_DBR *ar)
694 esc_name = check_pool_memory_size(esc_name, fnl*2+1);
695 db_escape_string(jcr, esc_name, fname, fnl);
697 esc_path = check_pool_memory_size(esc_path, pnl*2+1);
698 db_escape_string(jcr, esc_path, path, pnl);
700 if (ar->Digest == NULL || ar->Digest[0] == 0) {
706 Mmsg(cmd, "INSERT INTO batch VALUES "
707 "(%u,%s,'%s','%s','%s','%s',%u)",
708 ar->FileIndex, edit_int64(ar->JobId,ed1), esc_path,
709 esc_name, ar->attr, digest, ar->DeltaSeq);
711 return sql_query(cmd);
715 * Initialize database data structure. In principal this should
716 * never have errors, or it is really fatal.
718 B_DB *db_init_database(JCR *jcr, const char *db_driver, const char *db_name,
719 const char *db_user, const char *db_password,
720 const char *db_address, int db_port,
721 const char *db_socket, bool mult_db_connections,
722 bool disable_batch_insert)
726 P(mutex); /* lock DB queue */
728 * Look to see if DB already open
730 if (db_list && !mult_db_connections) {
731 foreach_dlist(mdb, db_list) {
732 if (mdb->db_match_database(db_driver, db_name, db_address, db_port)) {
733 Dmsg1(300, "DB REopen %s\n", db_name);
734 mdb->increment_refcount();
739 Dmsg0(300, "db_init_database first time\n");
740 mdb = New(B_DB_SQLITE(jcr, db_driver, db_name, db_user, db_password,
741 db_address, db_port, db_socket, mult_db_connections,
742 disable_batch_insert));
749 #endif /* HAVE_SQLITE3 */