2 * Bacula Catalog Database routines specific to SQLite
4 * Kern Sibbald, January 2002
9 Copyright (C) 2002-2006 Kern Sibbald
11 This program is free software; you can redistribute it and/or
12 modify it under the terms of the GNU General Public License
13 version 2 as amended with additional clauses defined in the
14 file LICENSE in the main source directory.
16 This program is distributed in the hope that it will be useful,
17 but WITHOUT ANY WARRANTY; without even the implied warranty of
18 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
19 the file LICENSE for additional details.
25 /* The following is necessary so that we do not include
26 * the dummy external definition of DB.
28 #define __SQL_C /* indicate that this is sql.c */
33 #if HAVE_SQLITE || HAVE_SQLITE3
35 /* -----------------------------------------------------------------------
37 * SQLite dependent defines and subroutines
39 * -----------------------------------------------------------------------
42 extern const char *working_directory;
44 /* List of open databases */
45 static BQUEUE db_list = {&db_list, &db_list};
47 static pthread_mutex_t mutex = PTHREAD_MUTEX_INITIALIZER;
49 int QueryDB(const char *file, int line, JCR *jcr, B_DB *db, char *select_cmd);
53 * Initialize database data structure. In principal this should
54 * never have errors, or it is really fatal.
57 db_init_database(JCR *jcr, const char *db_name, const char *db_user, const char *db_password,
58 const char *db_address, int db_port, const char *db_socket,
59 int mult_db_connections)
63 P(mutex); /* lock DB queue */
64 /* Look to see if DB already open */
65 if (!mult_db_connections) {
66 for (mdb=NULL; (mdb=(B_DB *)qnext(&db_list, &mdb->bq)); ) {
67 if (bstrcmp(mdb->db_name, db_name) &&
68 bstrcmp(mdb->db_address, db_address) &&
69 mdb->db_port == db_port) {
70 Dmsg2(300, "DB REopen %d %s\n", mdb->ref_count, db_name);
73 return mdb; /* already open */
77 Dmsg0(300, "db_open first time\n");
78 mdb = (B_DB *) malloc(sizeof(B_DB));
79 memset(mdb, 0, sizeof(B_DB));
80 mdb->db_name = bstrdup(db_name);
81 mdb->have_insert_id = TRUE;
82 mdb->errmsg = get_pool_memory(PM_EMSG); /* get error message buffer */
84 mdb->cmd = get_pool_memory(PM_EMSG); /* get command buffer */
85 mdb->cached_path = get_pool_memory(PM_FNAME);
86 mdb->cached_path_id = 0;
88 mdb->fname = get_pool_memory(PM_FNAME);
89 mdb->path = get_pool_memory(PM_FNAME);
90 mdb->esc_name = get_pool_memory(PM_FNAME);
91 mdb->allow_transactions = mult_db_connections;
92 qinsert(&db_list, &mdb->bq); /* put db in list */
98 * Now actually open the database. This can generate errors,
99 * which are returned in the errmsg
101 * DO NOT close the database or free(mdb) here !!!!
104 db_open_database(JCR *jcr, B_DB *mdb)
112 if (mdb->connected) {
116 mdb->connected = FALSE;
118 if ((errstat=rwl_init(&mdb->lock)) != 0) {
119 Mmsg1(&mdb->errmsg, _("Unable to initialize DB lock. ERR=%s\n"),
125 /* open the database */
126 len = strlen(working_directory) + strlen(mdb->db_name) + 5;
127 db_name = (char *)malloc(len);
128 strcpy(db_name, working_directory);
129 strcat(db_name, "/");
130 strcat(db_name, mdb->db_name);
131 strcat(db_name, ".db");
132 if (stat(db_name, &statbuf) != 0) {
133 Mmsg1(&mdb->errmsg, _("Database %s does not exist, please create it.\n"),
141 int stat = sqlite3_open(db_name, &mdb->db);
142 if (stat != SQLITE_OK) {
143 mdb->sqlite_errmsg = (char *)sqlite3_errmsg(mdb->db);
145 mdb->sqlite_errmsg = NULL;
149 mdb->db = sqlite_open(
150 db_name, /* database name */
152 &mdb->sqlite_errmsg); /* error message */
155 Dmsg0(300, "sqlite_open\n");
157 if (mdb->db == NULL) {
158 Mmsg2(&mdb->errmsg, _("Unable to open Database=%s. ERR=%s\n"),
159 db_name, mdb->sqlite_errmsg ? mdb->sqlite_errmsg : _("unknown"));
165 if (!check_tables_version(jcr, mdb)) {
170 mdb->connected = true;
176 db_close_database(JCR *jcr, B_DB *mdb)
181 db_end_transaction(jcr, mdb);
184 if (mdb->ref_count == 0) {
186 if (mdb->connected && mdb->db) {
187 sqlite_close(mdb->db);
189 rwl_destroy(&mdb->lock);
190 free_pool_memory(mdb->errmsg);
191 free_pool_memory(mdb->cmd);
192 free_pool_memory(mdb->cached_path);
193 free_pool_memory(mdb->fname);
194 free_pool_memory(mdb->path);
195 free_pool_memory(mdb->esc_name);
205 * Return the next unique index (auto-increment) for
206 * the given table. Return 0 on error.
208 int db_next_index(JCR *jcr, B_DB *mdb, char *table, char *index)
216 "SELECT id FROM NextId WHERE TableName=\"%s\"", table);
217 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
218 Mmsg(mdb->errmsg, _("next_index query error: ERR=%s\n"), sql_strerror(mdb));
222 if ((row = sql_fetch_row(mdb)) == NULL) {
223 Mmsg(mdb->errmsg, _("Error fetching index: ERR=%s\n"), sql_strerror(mdb));
227 bstrncpy(index, row[0], 28);
228 sql_free_result(mdb);
231 "UPDATE NextId SET id=id+1 WHERE TableName=\"%s\"", table);
232 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
233 Mmsg(mdb->errmsg, _("next_index update error: ERR=%s\n"), sql_strerror(mdb));
237 sql_free_result(mdb);
241 strcpy(index, "NULL");
247 * Escape strings so that SQLite is happy
249 * NOTE! len is the length of the old string. Your new
250 * string must be long enough (max 2*old+1) to hold
251 * the escaped output.
254 db_escape_string(char *snew, char *old, int len)
281 DB_RESULT_HANDLER *result_handler;
286 * Convert SQLite's callback into Bacula DB callback
288 static int sqlite_result(void *arh_data, int num_fields, char **rows, char **col_names)
290 struct rh_data *rh_data = (struct rh_data *)arh_data;
292 if (rh_data->result_handler) {
293 (*(rh_data->result_handler))(rh_data->ctx, num_fields, rows);
299 * Submit a general SQL command (cmd), and for each row returned,
300 * the sqlite_handler is called with the ctx.
302 int db_sql_query(B_DB *mdb, const char *query, DB_RESULT_HANDLER *result_handler, void *ctx)
304 struct rh_data rh_data;
308 if (mdb->sqlite_errmsg) {
310 sqlite3_free(mdb->sqlite_errmsg);
312 actuallyfree(mdb->sqlite_errmsg);
314 mdb->sqlite_errmsg = NULL;
316 rh_data.result_handler = result_handler;
318 stat = sqlite_exec(mdb->db, query, sqlite_result, (void *)&rh_data, &mdb->sqlite_errmsg);
320 Mmsg(mdb->errmsg, _("Query failed: %s: ERR=%s\n"), query, sql_strerror(mdb));
329 * Submit a sqlite query and retrieve all the data
331 int my_sqlite_query(B_DB *mdb, const char *cmd)
335 if (mdb->sqlite_errmsg) {
336 actuallyfree(mdb->sqlite_errmsg);
337 mdb->sqlite_errmsg = NULL;
339 stat = sqlite_get_table(mdb->db, (char *)cmd, &mdb->result, &mdb->nrow, &mdb->ncolumn,
340 &mdb->sqlite_errmsg);
341 mdb->row = 0; /* row fetched */
345 /* Fetch one row at a time */
346 SQL_ROW my_sqlite_fetch_row(B_DB *mdb)
348 if (mdb->row >= mdb->nrow) {
352 return &mdb->result[mdb->ncolumn * mdb->row];
355 void my_sqlite_free_table(B_DB *mdb)
359 if (mdb->fields_defined) {
360 for (i=0; i < sql_num_fields(mdb); i++) {
361 free(mdb->fields[i]);
364 mdb->fields_defined = false;
366 sqlite_free_table(mdb->result);
367 mdb->nrow = mdb->ncolumn = 0;
370 void my_sqlite_field_seek(B_DB *mdb, int field)
373 if (mdb->result == NULL) {
376 /* On first call, set up the fields */
377 if (!mdb->fields_defined && sql_num_fields(mdb) > 0) {
378 mdb->fields = (SQL_FIELD **)malloc(sizeof(SQL_FIELD) * mdb->ncolumn);
379 for (i=0; i < sql_num_fields(mdb); i++) {
380 mdb->fields[i] = (SQL_FIELD *)malloc(sizeof(SQL_FIELD));
381 mdb->fields[i]->name = mdb->result[i];
382 mdb->fields[i]->length = cstrlen(mdb->fields[i]->name);
383 mdb->fields[i]->max_length = mdb->fields[i]->length;
384 for (j=1; j <= mdb->nrow; j++) {
386 if (mdb->result[i + mdb->ncolumn *j]) {
387 len = (uint32_t)cstrlen(mdb->result[i + mdb->ncolumn * j]);
391 if (len > mdb->fields[i]->max_length) {
392 mdb->fields[i]->max_length = len;
395 mdb->fields[i]->type = 0;
396 mdb->fields[i]->flags = 1; /* not null */
398 mdb->fields_defined = TRUE;
400 if (field > sql_num_fields(mdb)) {
401 field = sql_num_fields(mdb);
407 SQL_FIELD *my_sqlite_fetch_field(B_DB *mdb)
409 return mdb->fields[mdb->field++];
412 #endif /* HAVE_SQLITE */