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)
307 n = esc_obj = check_pool_memory_size(esc_obj, len*2+1);
331 * Unescape binary object so that SQLIte is happy
333 * 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 *dest_len = expected_len;
345 memcpy(*dest, from, 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)
695 esc_name = check_pool_memory_size(esc_name, fnl*2+1);
696 db_escape_string(jcr, esc_name, fname, fnl);
698 esc_path = check_pool_memory_size(esc_path, pnl*2+1);
699 db_escape_string(jcr, esc_path, path, pnl);
701 if (ar->Digest == NULL || ar->Digest[0] == 0) {
707 len = Mmsg(cmd, "INSERT INTO batch VALUES "
708 "(%u,%s,'%s','%s','%s','%s',%u)",
709 ar->FileIndex, edit_int64(ar->JobId,ed1), esc_path,
710 esc_name, ar->attr, digest, ar->DeltaSeq);
712 return sql_query(cmd);
716 * Initialize database data structure. In principal this should
717 * never have errors, or it is really fatal.
719 B_DB *db_init_database(JCR *jcr, const char *db_driver, const char *db_name,
720 const char *db_user, const char *db_password,
721 const char *db_address, int db_port,
722 const char *db_socket, bool mult_db_connections,
723 bool disable_batch_insert)
727 P(mutex); /* lock DB queue */
729 * Look to see if DB already open
731 if (db_list && !mult_db_connections) {
732 foreach_dlist(mdb, db_list) {
733 if (mdb->db_match_database(db_driver, db_name, db_address, db_port)) {
734 Dmsg1(300, "DB REopen %s\n", db_name);
735 mdb->increment_refcount();
740 Dmsg0(300, "db_init_database first time\n");
741 mdb = New(B_DB_SQLITE(jcr, db_driver, db_name, db_user, db_password,
742 db_address, db_port, db_socket, mult_db_connections,
743 disable_batch_insert));
750 #endif /* HAVE_SQLITE3 */