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;
116 * Initialize the private members.
120 m_sqlite_errmsg = NULL;
123 * Put the db in the list.
125 if (db_list == NULL) {
126 db_list = New(dlist(this, &this->m_link));
128 db_list->append(this);
131 B_DB_SQLITE::~B_DB_SQLITE()
136 * Now actually open the database. This can generate errors,
137 * which are returned in the errmsg
139 * DO NOT close the database or delete mdb here !!!!
141 bool B_DB_SQLITE::db_open_database(JCR *jcr)
157 if ((errstat=rwl_init(&m_lock)) != 0) {
159 Mmsg1(&errmsg, _("Unable to initialize DB lock. ERR=%s\n"),
160 be.bstrerror(errstat));
167 len = strlen(working_directory) + strlen(m_db_name) + 5;
168 db_path = (char *)malloc(len);
169 strcpy(db_path, working_directory);
170 strcat(db_path, "/");
171 strcat(db_path, m_db_name);
172 strcat(db_path, ".db");
173 if (stat(db_path, &statbuf) != 0) {
174 Mmsg1(&errmsg, _("Database %s does not exist, please create it.\n"),
180 for (m_db_handle = NULL; !m_db_handle && retry++ < 10; ) {
181 ret = sqlite3_open(db_path, &m_db_handle);
182 if (ret != SQLITE_OK) {
183 m_sqlite_errmsg = (char *)sqlite3_errmsg(m_db_handle);
184 sqlite3_close(m_db_handle);
187 m_sqlite_errmsg = NULL;
190 Dmsg0(300, "sqlite_open\n");
195 if (m_db_handle == NULL) {
196 Mmsg2(&errmsg, _("Unable to open Database=%s. ERR=%s\n"),
197 db_path, m_sqlite_errmsg ? m_sqlite_errmsg : _("unknown"));
205 * Set busy handler to wait when we use mult_db_connections = true
207 sqlite3_busy_handler(m_db_handle, sqlite_busy_handler, NULL);
209 #if defined(SQLITE3_INIT_QUERY)
210 sql_query(SQLITE3_INIT_QUERY);
213 if (!check_tables_version(jcr, this)) {
224 void B_DB_SQLITE::db_close_database(JCR *jcr)
226 db_end_transaction(jcr);
229 if (m_ref_count == 0) {
231 db_list->remove(this);
232 if (m_connected && m_db_handle) {
233 sqlite3_close(m_db_handle);
235 rwl_destroy(&m_lock);
236 free_pool_memory(errmsg);
237 free_pool_memory(cmd);
238 free_pool_memory(cached_path);
239 free_pool_memory(fname);
240 free_pool_memory(path);
241 free_pool_memory(esc_name);
242 free_pool_memory(esc_path);
243 free_pool_memory(esc_obj);
251 if (db_list->size() == 0) {
259 void B_DB_SQLITE::db_thread_cleanup(void)
261 sqlite3_thread_cleanup();
265 * Escape strings so that SQLite is happy
267 * NOTE! len is the length of the old string. Your new
268 * string must be long enough (max 2*old+1) to hold
269 * the escaped output.
271 void B_DB_SQLITE::db_escape_string(JCR *jcr, char *snew, char *old, int len)
298 * Escape binary object so that SQLite is happy
299 * Memory is stored in B_DB struct, no need to free it
301 * TODO: this should be implemented (escape \0)
303 char *B_DB_SQLITE::db_escape_object(JCR *jcr, char *old, int len)
306 int max = len*2; /* TODO: too big, should be *4/3 */
308 esc_obj = check_pool_memory_size(esc_obj, max);
309 l = bin_to_base64(esc_obj, max, old, len, true);
311 ASSERT(l < max); /* TODO: add check for l */
317 * Unescape binary object so that SQLIte is happy
319 * TODO: need to be implemented (escape \0)
322 void B_DB_SQLITE::db_unescape_object(JCR *jcr, char *from, int32_t expected_len,
323 POOLMEM **dest, int32_t *dest_len)
330 *dest = check_pool_memory_size(*dest, expected_len+1);
331 base64_to_bin(*dest, expected_len+1, from, strlen(from));
332 *dest_len = expected_len;
333 (*dest)[expected_len]=0;
337 * Start a transaction. This groups inserts and makes things
338 * much more efficient. Usually started when inserting
341 void B_DB_SQLITE::db_start_transaction(JCR *jcr)
344 jcr->attr = get_pool_memory(PM_FNAME);
347 jcr->ar = (ATTR_DBR *)malloc(sizeof(ATTR_DBR));
350 if (!m_allow_transactions) {
356 * Allow only 10,000 changes per transaction
358 if (m_transaction && changes > 10000) {
359 db_end_transaction(jcr);
361 if (!m_transaction) {
362 sql_query("BEGIN"); /* begin transaction */
363 Dmsg0(400, "Start SQLite transaction\n");
364 m_transaction = true;
369 void B_DB_SQLITE::db_end_transaction(JCR *jcr)
371 if (jcr && jcr->cached_attribute) {
372 Dmsg0(400, "Flush last cached attribute.\n");
373 if (!db_create_attributes_record(jcr, this, jcr->ar)) {
374 Jmsg1(jcr, M_FATAL, 0, _("Attribute create error. %s"), db_strerror(jcr->db));
376 jcr->cached_attribute = false;
379 if (!m_allow_transactions) {
385 sql_query("COMMIT"); /* end transaction */
386 m_transaction = false;
387 Dmsg1(400, "End SQLite transaction changes=%d\n", changes);
395 DB_RESULT_HANDLER *result_handler;
401 * Convert SQLite's callback into Bacula DB callback
403 static int sqlite_result_handler(void *arh_data, int num_fields, char **rows, char **col_names)
405 struct rh_data *rh_data = (struct rh_data *)arh_data;
407 /* The db_sql_query doesn't have access to m_results, so if we wan't to get
408 * fields information, we need to use col_names
410 if (!rh_data->initialized) {
411 rh_data->mdb->set_column_names(col_names, num_fields);
412 rh_data->initialized = true;
414 if (rh_data->result_handler) {
415 (*(rh_data->result_handler))(rh_data->ctx, num_fields, rows);
422 * Submit a general SQL command (cmd), and for each row returned,
423 * the result_handler is called with the ctx.
425 bool B_DB_SQLITE::db_sql_query(const char *query, DB_RESULT_HANDLER *result_handler, void *ctx)
429 struct rh_data rh_data;
431 Dmsg1(500, "db_sql_query starts with '%s'\n", query);
434 if (m_sqlite_errmsg) {
435 sqlite3_free(m_sqlite_errmsg);
436 m_sqlite_errmsg = NULL;
442 rh_data.initialized = false;
443 rh_data.result_handler = result_handler;
445 stat = sqlite3_exec(m_db_handle, query, sqlite_result_handler,
446 (void *)&rh_data, &m_sqlite_errmsg);
448 if (stat != SQLITE_OK) {
449 Mmsg(errmsg, _("Query failed: %s: ERR=%s\n"), query, sql_strerror());
450 Dmsg0(500, "db_sql_query finished\n");
453 Dmsg0(500, "db_sql_query finished\n");
463 * Submit a sqlite query and retrieve all the data
465 bool B_DB_SQLITE::sql_query(const char *query, int flags)
470 Dmsg1(500, "sql_query starts with '%s'\n", query);
473 if (m_sqlite_errmsg) {
474 sqlite3_free(m_sqlite_errmsg);
475 m_sqlite_errmsg = NULL;
478 stat = sqlite3_get_table(m_db_handle, (char *)query, &m_result,
479 &m_num_rows, &m_num_fields, &m_sqlite_errmsg);
481 m_row_number = 0; /* no row fetched */
482 if (stat != 0) { /* something went wrong */
483 m_num_rows = m_num_fields = 0;
484 Dmsg0(500, "sql_query finished\n");
486 Dmsg0(500, "sql_query finished\n");
492 void B_DB_SQLITE::sql_free_result(void)
500 sqlite3_free_table(m_result);
504 m_num_rows = m_num_fields = 0;
509 * Fetch one row at a time
511 SQL_ROW B_DB_SQLITE::sql_fetch_row(void)
513 if (!m_result || (m_row_number >= m_num_rows)) {
517 return &m_result[m_num_fields * m_row_number];
520 const char *B_DB_SQLITE::sql_strerror(void)
522 return m_sqlite_errmsg ? m_sqlite_errmsg : "unknown";
525 void B_DB_SQLITE::sql_data_seek(int row)
528 * Set the row number to be returned on the next call to sql_fetch_row
533 int B_DB_SQLITE::sql_affected_rows(void)
535 return sqlite3_changes(m_db_handle);
538 uint64_t B_DB_SQLITE::sql_insert_autokey_record(const char *query, const char *table_name)
541 * First execute the insert query and then retrieve the currval.
543 if (!sql_query(query)) {
547 m_num_rows = sql_affected_rows();
548 if (m_num_rows != 1) {
554 return sqlite3_last_insert_rowid(m_db_handle);
557 SQL_FIELD *B_DB_SQLITE::sql_fetch_field(void)
561 /* We are in the middle of a db_sql_query and we want to get fields info */
562 if (m_col_names != NULL) {
563 if (m_num_fields > m_field_number) {
564 m_sql_field.name = m_col_names[m_field_number];
565 /* We don't have the maximum field length, so we can use 80 as
568 len = MAX(cstrlen(m_sql_field.name), 80/m_num_fields);
569 m_sql_field.max_length = len;
572 m_sql_field.type = 0; /* not numeric */
573 m_sql_field.flags = 1; /* not null */
575 } else { /* too much fetch_field() */
580 /* We are after a sql_query() that stores the result in m_results */
581 if (!m_fields || m_fields_size < m_num_fields) {
586 Dmsg1(500, "allocating space for %d fields\n", m_num_fields);
587 m_fields = (SQL_FIELD *)malloc(sizeof(SQL_FIELD) * m_num_fields);
588 m_fields_size = m_num_fields;
590 for (i = 0; i < m_num_fields; i++) {
591 Dmsg1(500, "filling field %d\n", i);
592 m_fields[i].name = m_result[i];
593 m_fields[i].max_length = cstrlen(m_fields[i].name);
594 for (j = 1; j <= m_num_rows; j++) {
595 if (m_result[i + m_num_fields * j]) {
596 len = (uint32_t)cstrlen(m_result[i + m_num_fields * j]);
600 if (len > m_fields[i].max_length) {
601 m_fields[i].max_length = len;
604 m_fields[i].type = 0;
605 m_fields[i].flags = 1; /* not null */
607 Dmsg4(500, "sql_fetch_field finds field '%s' has length='%d' type='%d' and IsNull=%d\n",
608 m_fields[i].name, m_fields[i].max_length, m_fields[i].type, m_fields[i].flags);
613 * Increment field number for the next time around
615 return &m_fields[m_field_number++];
618 bool B_DB_SQLITE::sql_field_is_not_null(int field_type)
620 switch (field_type) {
628 bool B_DB_SQLITE::sql_field_is_numeric(int field_type)
630 switch (field_type) {
642 bool B_DB_SQLITE::sql_batch_start(JCR *jcr)
647 retval = sql_query("CREATE TEMPORARY TABLE batch ("
654 "DeltaSeq integer)");
660 /* set error to something to abort operation */
665 bool B_DB_SQLITE::sql_batch_end(JCR *jcr, const char *error)
676 bool B_DB_SQLITE::sql_batch_insert(JCR *jcr, ATTR_DBR *ar)
682 esc_name = check_pool_memory_size(esc_name, fnl*2+1);
683 db_escape_string(jcr, esc_name, fname, fnl);
685 esc_path = check_pool_memory_size(esc_path, pnl*2+1);
686 db_escape_string(jcr, esc_path, path, pnl);
688 if (ar->Digest == NULL || ar->Digest[0] == 0) {
694 len = Mmsg(cmd, "INSERT INTO batch VALUES "
695 "(%u,%s,'%s','%s','%s','%s',%u)",
696 ar->FileIndex, edit_int64(ar->JobId,ed1), esc_path,
697 esc_name, ar->attr, digest, ar->DeltaSeq);
699 return sql_query(cmd);
703 * Initialize database data structure. In principal this should
704 * never have errors, or it is really fatal.
706 B_DB *db_init_database(JCR *jcr, const char *db_driver, const char *db_name,
707 const char *db_user, const char *db_password,
708 const char *db_address, int db_port,
709 const char *db_socket, bool mult_db_connections,
710 bool disable_batch_insert)
714 P(mutex); /* lock DB queue */
716 * Look to see if DB already open
718 if (db_list && !mult_db_connections) {
719 foreach_dlist(mdb, db_list) {
720 if (mdb->db_match_database(db_driver, db_name, db_address, db_port)) {
721 Dmsg1(300, "DB REopen %s\n", db_name);
722 mdb->increment_refcount();
727 Dmsg0(300, "db_init_database first time\n");
728 mdb = New(B_DB_SQLITE(jcr, db_driver, db_name, db_user, db_password,
729 db_address, db_port, db_socket, mult_db_connections,
730 disable_batch_insert));
737 #endif /* HAVE_SQLITE3 */