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_sqlite_result(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_sqlite_result,
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);
488 if (m_sqlite_errmsg) {
489 sqlite3_free(m_sqlite_errmsg);
490 m_sqlite_errmsg = NULL;
493 stat = sqlite3_get_table(m_db_handle, (char *)query, &m_result,
494 &m_num_rows, &m_num_fields, &m_sqlite_errmsg);
496 m_row_number = 0; /* no row fetched */
497 if (stat != 0) { /* something went wrong */
498 m_num_rows = m_num_fields = 0;
499 Dmsg0(500, "sql_query finished\n");
501 Dmsg0(500, "sql_query finished\n");
507 void B_DB_SQLITE::sql_free_result(void)
515 sqlite3_free_table(m_result);
519 m_num_rows = m_num_fields = 0;
524 * Fetch one row at a time
526 SQL_ROW B_DB_SQLITE::sql_fetch_row(void)
528 if (!m_result || (m_row_number >= m_num_rows)) {
532 return &m_result[m_num_fields * m_row_number];
535 const char *B_DB_SQLITE::sql_strerror(void)
537 return m_sqlite_errmsg ? m_sqlite_errmsg : "unknown";
540 void B_DB_SQLITE::sql_data_seek(int row)
543 * Set the row number to be returned on the next call to sql_fetch_row
548 int B_DB_SQLITE::sql_affected_rows(void)
550 return sqlite3_changes(m_db_handle);
553 uint64_t B_DB_SQLITE::sql_insert_autokey_record(const char *query, const char *table_name)
556 * First execute the insert query and then retrieve the currval.
558 if (!sql_query(query)) {
562 m_num_rows = sql_affected_rows();
563 if (m_num_rows != 1) {
569 return sqlite3_last_insert_rowid(m_db_handle);
572 SQL_FIELD *B_DB_SQLITE::sql_fetch_field(void)
576 /* We are in the middle of a db_sql_query and we want to get fields info */
577 if (m_col_names != NULL) {
578 if (m_num_fields > m_field_number) {
579 m_sql_field.name = m_col_names[m_field_number];
580 /* We don't have the maximum field length, so we can use 80 as
583 len = MAX(cstrlen(m_sql_field.name), 80/m_num_fields);
584 m_sql_field.max_length = len;
587 m_sql_field.type = 0; /* not numeric */
588 m_sql_field.flags = 1; /* not null */
590 } else { /* too much fetch_field() */
595 /* We are after a sql_query() that stores the result in m_results */
596 if (!m_fields || m_fields_size < m_num_fields) {
601 Dmsg1(500, "allocating space for %d fields\n", m_num_fields);
602 m_fields = (SQL_FIELD *)malloc(sizeof(SQL_FIELD) * m_num_fields);
603 m_fields_size = m_num_fields;
605 for (i = 0; i < m_num_fields; i++) {
606 Dmsg1(500, "filling field %d\n", i);
607 m_fields[i].name = m_result[i];
608 m_fields[i].max_length = cstrlen(m_fields[i].name);
609 for (j = 1; j <= m_num_rows; j++) {
610 if (m_result[i + m_num_fields * j]) {
611 len = (uint32_t)cstrlen(m_result[i + m_num_fields * j]);
615 if (len > m_fields[i].max_length) {
616 m_fields[i].max_length = len;
619 m_fields[i].type = 0;
620 m_fields[i].flags = 1; /* not null */
622 Dmsg4(500, "sql_fetch_field finds field '%s' has length='%d' type='%d' and IsNull=%d\n",
623 m_fields[i].name, m_fields[i].max_length, m_fields[i].type, m_fields[i].flags);
628 * Increment field number for the next time around
630 return &m_fields[m_field_number++];
633 bool B_DB_SQLITE::sql_field_is_not_null(int field_type)
635 switch (field_type) {
643 bool B_DB_SQLITE::sql_field_is_numeric(int field_type)
645 switch (field_type) {
657 bool B_DB_SQLITE::sql_batch_start(JCR *jcr)
662 retval = sql_query("CREATE TEMPORARY TABLE batch ("
669 "DeltaSeq integer)");
675 /* set error to something to abort operation */
680 bool B_DB_SQLITE::sql_batch_end(JCR *jcr, const char *error)
691 bool B_DB_SQLITE::sql_batch_insert(JCR *jcr, ATTR_DBR *ar)
697 esc_name = check_pool_memory_size(esc_name, fnl*2+1);
698 db_escape_string(jcr, esc_name, fname, fnl);
700 esc_path = check_pool_memory_size(esc_path, pnl*2+1);
701 db_escape_string(jcr, esc_path, path, pnl);
703 if (ar->Digest == NULL || ar->Digest[0] == 0) {
709 len = Mmsg(cmd, "INSERT INTO batch VALUES "
710 "(%u,%s,'%s','%s','%s','%s',%u)",
711 ar->FileIndex, edit_int64(ar->JobId,ed1), esc_path,
712 esc_name, ar->attr, digest, ar->DeltaSeq);
714 return sql_query(cmd);
718 * Initialize database data structure. In principal this should
719 * never have errors, or it is really fatal.
721 B_DB *db_init_database(JCR *jcr, const char *db_driver, const char *db_name,
722 const char *db_user, const char *db_password,
723 const char *db_address, int db_port,
724 const char *db_socket, bool mult_db_connections,
725 bool disable_batch_insert)
729 P(mutex); /* lock DB queue */
731 * Look to see if DB already open
733 if (db_list && !mult_db_connections) {
734 foreach_dlist(mdb, db_list) {
735 if (mdb->db_match_database(db_driver, db_name, db_address, db_port)) {
736 Dmsg1(300, "DB REopen %s\n", db_name);
737 mdb->increment_refcount();
742 Dmsg0(300, "db_init_database first time\n");
743 mdb = New(B_DB_SQLITE(jcr, db_driver, db_name, db_user, db_password,
744 db_address, db_port, db_socket, mult_db_connections,
745 disable_batch_insert));
752 #endif /* HAVE_SQLITE3 */