2 * Bacula Catalog Database routines specific to SQLite
4 * Kern Sibbald, January 2002
9 Bacula® - The Network Backup Solution
11 Copyright (C) 2000-2006 Free Software Foundation Europe e.V.
13 The main author of Bacula is Kern Sibbald, with contributions from
14 many others, a complete list can be found in the file AUTHORS.
15 This program is Free Software; you can redistribute it and/or
16 modify it under the terms of version two of the GNU General Public
17 License as published by the Free Software Foundation plus additions
18 that are listed in the file LICENSE.
20 This program is distributed in the hope that it will be useful, but
21 WITHOUT ANY WARRANTY; without even the implied warranty of
22 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
23 General Public License for more details.
25 You should have received a copy of the GNU General Public License
26 along with this program; if not, write to the Free Software
27 Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA
30 Bacula® is a registered trademark of John Walker.
31 The licensor of Bacula is the Free Software Foundation Europe
32 (FSFE), Fiduciary Program, Sumatrastrasse 25, 8006 Zürich,
33 Switzerland, email:ftf@fsfeurope.org.
38 /* The following is necessary so that we do not include
39 * the dummy external definition of DB.
41 #define __SQL_C /* indicate that this is sql.c */
46 #if HAVE_SQLITE || HAVE_SQLITE3
48 /* -----------------------------------------------------------------------
50 * SQLite dependent defines and subroutines
52 * -----------------------------------------------------------------------
55 /* List of open databases */
56 static BQUEUE db_list = {&db_list, &db_list};
58 static pthread_mutex_t mutex = PTHREAD_MUTEX_INITIALIZER;
60 int QueryDB(const char *file, int line, JCR *jcr, B_DB *db, char *select_cmd);
64 * Retrieve database type
73 * Initialize database data structure. In principal this should
74 * never have errors, or it is really fatal.
77 db_init_database(JCR *jcr, const char *db_name, const char *db_user, const char *db_password,
78 const char *db_address, int db_port, const char *db_socket,
79 int mult_db_connections)
83 P(mutex); /* lock DB queue */
84 /* Look to see if DB already open */
85 if (!mult_db_connections) {
86 for (mdb=NULL; (mdb=(B_DB *)qnext(&db_list, &mdb->bq)); ) {
87 if (bstrcmp(mdb->db_name, db_name) &&
88 bstrcmp(mdb->db_address, db_address) &&
89 mdb->db_port == db_port) {
90 Dmsg2(300, "DB REopen %d %s\n", mdb->ref_count, db_name);
93 return mdb; /* already open */
97 Dmsg0(300, "db_open first time\n");
98 mdb = (B_DB *) malloc(sizeof(B_DB));
99 memset(mdb, 0, sizeof(B_DB));
100 mdb->db_name = bstrdup(db_name);
101 mdb->have_insert_id = TRUE;
102 mdb->errmsg = get_pool_memory(PM_EMSG); /* get error message buffer */
104 mdb->cmd = get_pool_memory(PM_EMSG); /* get command buffer */
105 mdb->cached_path = get_pool_memory(PM_FNAME);
106 mdb->cached_path_id = 0;
108 mdb->fname = get_pool_memory(PM_FNAME);
109 mdb->path = get_pool_memory(PM_FNAME);
110 mdb->esc_name = get_pool_memory(PM_FNAME);
111 mdb->allow_transactions = mult_db_connections;
112 qinsert(&db_list, &mdb->bq); /* put db in list */
118 * Now actually open the database. This can generate errors,
119 * which are returned in the errmsg
121 * DO NOT close the database or free(mdb) here !!!!
124 db_open_database(JCR *jcr, B_DB *mdb)
132 if (mdb->connected) {
136 mdb->connected = FALSE;
138 if ((errstat=rwl_init(&mdb->lock)) != 0) {
139 Mmsg1(&mdb->errmsg, _("Unable to initialize DB lock. ERR=%s\n"),
145 /* open the database */
146 len = strlen(working_directory) + strlen(mdb->db_name) + 5;
147 db_name = (char *)malloc(len);
148 strcpy(db_name, working_directory);
149 strcat(db_name, "/");
150 strcat(db_name, mdb->db_name);
151 strcat(db_name, ".db");
152 if (stat(db_name, &statbuf) != 0) {
153 Mmsg1(&mdb->errmsg, _("Database %s does not exist, please create it.\n"),
161 int stat = sqlite3_open(db_name, &mdb->db);
162 if (stat != SQLITE_OK) {
163 mdb->sqlite_errmsg = (char *)sqlite3_errmsg(mdb->db);
164 sqlite3_close(mdb->db);
167 mdb->sqlite_errmsg = NULL;
171 mdb->db = sqlite_open(
172 db_name, /* database name */
174 &mdb->sqlite_errmsg); /* error message */
177 Dmsg0(300, "sqlite_open\n");
179 if (mdb->db == NULL) {
180 Mmsg2(&mdb->errmsg, _("Unable to open Database=%s. ERR=%s\n"),
181 db_name, mdb->sqlite_errmsg ? mdb->sqlite_errmsg : _("unknown"));
187 if (!check_tables_version(jcr, mdb)) {
192 mdb->connected = true;
198 db_close_database(JCR *jcr, B_DB *mdb)
203 db_end_transaction(jcr, mdb);
206 if (mdb->ref_count == 0) {
208 if (mdb->connected && mdb->db) {
209 sqlite_close(mdb->db);
211 rwl_destroy(&mdb->lock);
212 free_pool_memory(mdb->errmsg);
213 free_pool_memory(mdb->cmd);
214 free_pool_memory(mdb->cached_path);
215 free_pool_memory(mdb->fname);
216 free_pool_memory(mdb->path);
217 free_pool_memory(mdb->esc_name);
227 * Return the next unique index (auto-increment) for
228 * the given table. Return 0 on error.
230 int db_next_index(JCR *jcr, B_DB *mdb, char *table, char *index)
238 "SELECT id FROM NextId WHERE TableName=\"%s\"", table);
239 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
240 Mmsg(mdb->errmsg, _("next_index query error: ERR=%s\n"), sql_strerror(mdb));
244 if ((row = sql_fetch_row(mdb)) == NULL) {
245 Mmsg(mdb->errmsg, _("Error fetching index: ERR=%s\n"), sql_strerror(mdb));
249 bstrncpy(index, row[0], 28);
250 sql_free_result(mdb);
253 "UPDATE NextId SET id=id+1 WHERE TableName=\"%s\"", table);
254 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
255 Mmsg(mdb->errmsg, _("next_index update error: ERR=%s\n"), sql_strerror(mdb));
259 sql_free_result(mdb);
263 strcpy(index, "NULL");
269 * Escape strings so that SQLite is happy
271 * NOTE! len is the length of the old string. Your new
272 * string must be long enough (max 2*old+1) to hold
273 * the escaped output.
276 db_escape_string(char *snew, char *old, int len)
303 DB_RESULT_HANDLER *result_handler;
308 * Convert SQLite's callback into Bacula DB callback
310 static int sqlite_result(void *arh_data, int num_fields, char **rows, char **col_names)
312 struct rh_data *rh_data = (struct rh_data *)arh_data;
314 if (rh_data->result_handler) {
315 (*(rh_data->result_handler))(rh_data->ctx, num_fields, rows);
321 * Submit a general SQL command (cmd), and for each row returned,
322 * the sqlite_handler is called with the ctx.
324 int db_sql_query(B_DB *mdb, const char *query, DB_RESULT_HANDLER *result_handler, void *ctx)
326 struct rh_data rh_data;
330 if (mdb->sqlite_errmsg) {
332 sqlite3_free(mdb->sqlite_errmsg);
334 actuallyfree(mdb->sqlite_errmsg);
336 mdb->sqlite_errmsg = NULL;
338 rh_data.result_handler = result_handler;
340 stat = sqlite_exec(mdb->db, query, sqlite_result, (void *)&rh_data, &mdb->sqlite_errmsg);
342 Mmsg(mdb->errmsg, _("Query failed: %s: ERR=%s\n"), query, sql_strerror(mdb));
351 * Submit a sqlite query and retrieve all the data
353 int my_sqlite_query(B_DB *mdb, const char *cmd)
357 if (mdb->sqlite_errmsg) {
359 sqlite3_free(mdb->sqlite_errmsg);
361 actuallyfree(mdb->sqlite_errmsg);
363 mdb->sqlite_errmsg = NULL;
365 stat = sqlite_get_table(mdb->db, (char *)cmd, &mdb->result, &mdb->nrow, &mdb->ncolumn,
366 &mdb->sqlite_errmsg);
367 mdb->row = 0; /* row fetched */
371 /* Fetch one row at a time */
372 SQL_ROW my_sqlite_fetch_row(B_DB *mdb)
374 if (mdb->row >= mdb->nrow) {
378 return &mdb->result[mdb->ncolumn * mdb->row];
381 void my_sqlite_free_table(B_DB *mdb)
385 if (mdb->fields_defined) {
386 for (i=0; i < sql_num_fields(mdb); i++) {
387 free(mdb->fields[i]);
390 mdb->fields_defined = false;
392 sqlite_free_table(mdb->result);
393 mdb->nrow = mdb->ncolumn = 0;
396 void my_sqlite_field_seek(B_DB *mdb, int field)
399 if (mdb->result == NULL) {
402 /* On first call, set up the fields */
403 if (!mdb->fields_defined && sql_num_fields(mdb) > 0) {
404 mdb->fields = (SQL_FIELD **)malloc(sizeof(SQL_FIELD) * mdb->ncolumn);
405 for (i=0; i < sql_num_fields(mdb); i++) {
406 mdb->fields[i] = (SQL_FIELD *)malloc(sizeof(SQL_FIELD));
407 mdb->fields[i]->name = mdb->result[i];
408 mdb->fields[i]->length = cstrlen(mdb->fields[i]->name);
409 mdb->fields[i]->max_length = mdb->fields[i]->length;
410 for (j=1; j <= mdb->nrow; j++) {
412 if (mdb->result[i + mdb->ncolumn *j]) {
413 len = (uint32_t)cstrlen(mdb->result[i + mdb->ncolumn * j]);
417 if (len > mdb->fields[i]->max_length) {
418 mdb->fields[i]->max_length = len;
421 mdb->fields[i]->type = 0;
422 mdb->fields[i]->flags = 1; /* not null */
424 mdb->fields_defined = TRUE;
426 if (field > sql_num_fields(mdb)) {
427 field = sql_num_fields(mdb);
433 SQL_FIELD *my_sqlite_fetch_field(B_DB *mdb)
435 return mdb->fields[mdb->field++];
438 #endif /* HAVE_SQLITE */