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 /* List of open databases */
43 static BQUEUE db_list = {&db_list, &db_list};
45 static pthread_mutex_t mutex = PTHREAD_MUTEX_INITIALIZER;
47 int QueryDB(const char *file, int line, JCR *jcr, B_DB *db, char *select_cmd);
51 * Initialize database data structure. In principal this should
52 * never have errors, or it is really fatal.
55 db_init_database(JCR *jcr, const char *db_name, const char *db_user, const char *db_password,
56 const char *db_address, int db_port, const char *db_socket,
57 int mult_db_connections)
61 P(mutex); /* lock DB queue */
62 /* Look to see if DB already open */
63 if (!mult_db_connections) {
64 for (mdb=NULL; (mdb=(B_DB *)qnext(&db_list, &mdb->bq)); ) {
65 if (bstrcmp(mdb->db_name, db_name) &&
66 bstrcmp(mdb->db_address, db_address) &&
67 mdb->db_port == db_port) {
68 Dmsg2(300, "DB REopen %d %s\n", mdb->ref_count, db_name);
71 return mdb; /* already open */
75 Dmsg0(300, "db_open first time\n");
76 mdb = (B_DB *) malloc(sizeof(B_DB));
77 memset(mdb, 0, sizeof(B_DB));
78 mdb->db_name = bstrdup(db_name);
79 mdb->have_insert_id = TRUE;
80 mdb->errmsg = get_pool_memory(PM_EMSG); /* get error message buffer */
82 mdb->cmd = get_pool_memory(PM_EMSG); /* get command buffer */
83 mdb->cached_path = get_pool_memory(PM_FNAME);
84 mdb->cached_path_id = 0;
86 mdb->fname = get_pool_memory(PM_FNAME);
87 mdb->path = get_pool_memory(PM_FNAME);
88 mdb->esc_name = get_pool_memory(PM_FNAME);
89 mdb->allow_transactions = mult_db_connections;
90 qinsert(&db_list, &mdb->bq); /* put db in list */
96 * Now actually open the database. This can generate errors,
97 * which are returned in the errmsg
99 * DO NOT close the database or free(mdb) here !!!!
102 db_open_database(JCR *jcr, B_DB *mdb)
110 if (mdb->connected) {
114 mdb->connected = FALSE;
116 if ((errstat=rwl_init(&mdb->lock)) != 0) {
117 Mmsg1(&mdb->errmsg, _("Unable to initialize DB lock. ERR=%s\n"),
123 /* open the database */
124 len = strlen(working_directory) + strlen(mdb->db_name) + 5;
125 db_name = (char *)malloc(len);
126 strcpy(db_name, working_directory);
127 strcat(db_name, "/");
128 strcat(db_name, mdb->db_name);
129 strcat(db_name, ".db");
130 if (stat(db_name, &statbuf) != 0) {
131 Mmsg1(&mdb->errmsg, _("Database %s does not exist, please create it.\n"),
139 int stat = sqlite3_open(db_name, &mdb->db);
140 if (stat != SQLITE_OK) {
141 mdb->sqlite_errmsg = (char *)sqlite3_errmsg(mdb->db);
143 mdb->sqlite_errmsg = NULL;
147 mdb->db = sqlite_open(
148 db_name, /* database name */
150 &mdb->sqlite_errmsg); /* error message */
153 Dmsg0(300, "sqlite_open\n");
155 if (mdb->db == NULL) {
156 Mmsg2(&mdb->errmsg, _("Unable to open Database=%s. ERR=%s\n"),
157 db_name, mdb->sqlite_errmsg ? mdb->sqlite_errmsg : _("unknown"));
163 if (!check_tables_version(jcr, mdb)) {
168 mdb->connected = true;
174 db_close_database(JCR *jcr, B_DB *mdb)
179 db_end_transaction(jcr, mdb);
182 if (mdb->ref_count == 0) {
184 if (mdb->connected && mdb->db) {
185 sqlite_close(mdb->db);
187 rwl_destroy(&mdb->lock);
188 free_pool_memory(mdb->errmsg);
189 free_pool_memory(mdb->cmd);
190 free_pool_memory(mdb->cached_path);
191 free_pool_memory(mdb->fname);
192 free_pool_memory(mdb->path);
193 free_pool_memory(mdb->esc_name);
203 * Return the next unique index (auto-increment) for
204 * the given table. Return 0 on error.
206 int db_next_index(JCR *jcr, B_DB *mdb, char *table, char *index)
214 "SELECT id FROM NextId WHERE TableName=\"%s\"", table);
215 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
216 Mmsg(mdb->errmsg, _("next_index query error: ERR=%s\n"), sql_strerror(mdb));
220 if ((row = sql_fetch_row(mdb)) == NULL) {
221 Mmsg(mdb->errmsg, _("Error fetching index: ERR=%s\n"), sql_strerror(mdb));
225 bstrncpy(index, row[0], 28);
226 sql_free_result(mdb);
229 "UPDATE NextId SET id=id+1 WHERE TableName=\"%s\"", table);
230 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
231 Mmsg(mdb->errmsg, _("next_index update error: ERR=%s\n"), sql_strerror(mdb));
235 sql_free_result(mdb);
239 strcpy(index, "NULL");
245 * Escape strings so that SQLite is happy
247 * NOTE! len is the length of the old string. Your new
248 * string must be long enough (max 2*old+1) to hold
249 * the escaped output.
252 db_escape_string(char *snew, char *old, int len)
279 DB_RESULT_HANDLER *result_handler;
284 * Convert SQLite's callback into Bacula DB callback
286 static int sqlite_result(void *arh_data, int num_fields, char **rows, char **col_names)
288 struct rh_data *rh_data = (struct rh_data *)arh_data;
290 if (rh_data->result_handler) {
291 (*(rh_data->result_handler))(rh_data->ctx, num_fields, rows);
297 * Submit a general SQL command (cmd), and for each row returned,
298 * the sqlite_handler is called with the ctx.
300 int db_sql_query(B_DB *mdb, const char *query, DB_RESULT_HANDLER *result_handler, void *ctx)
302 struct rh_data rh_data;
306 if (mdb->sqlite_errmsg) {
308 sqlite3_free(mdb->sqlite_errmsg);
310 actuallyfree(mdb->sqlite_errmsg);
312 mdb->sqlite_errmsg = NULL;
314 rh_data.result_handler = result_handler;
316 stat = sqlite_exec(mdb->db, query, sqlite_result, (void *)&rh_data, &mdb->sqlite_errmsg);
318 Mmsg(mdb->errmsg, _("Query failed: %s: ERR=%s\n"), query, sql_strerror(mdb));
327 * Submit a sqlite query and retrieve all the data
329 int my_sqlite_query(B_DB *mdb, const char *cmd)
333 if (mdb->sqlite_errmsg) {
334 actuallyfree(mdb->sqlite_errmsg);
335 mdb->sqlite_errmsg = NULL;
337 stat = sqlite_get_table(mdb->db, (char *)cmd, &mdb->result, &mdb->nrow, &mdb->ncolumn,
338 &mdb->sqlite_errmsg);
339 mdb->row = 0; /* row fetched */
343 /* Fetch one row at a time */
344 SQL_ROW my_sqlite_fetch_row(B_DB *mdb)
346 if (mdb->row >= mdb->nrow) {
350 return &mdb->result[mdb->ncolumn * mdb->row];
353 void my_sqlite_free_table(B_DB *mdb)
357 if (mdb->fields_defined) {
358 for (i=0; i < sql_num_fields(mdb); i++) {
359 free(mdb->fields[i]);
362 mdb->fields_defined = false;
364 sqlite_free_table(mdb->result);
365 mdb->nrow = mdb->ncolumn = 0;
368 void my_sqlite_field_seek(B_DB *mdb, int field)
371 if (mdb->result == NULL) {
374 /* On first call, set up the fields */
375 if (!mdb->fields_defined && sql_num_fields(mdb) > 0) {
376 mdb->fields = (SQL_FIELD **)malloc(sizeof(SQL_FIELD) * mdb->ncolumn);
377 for (i=0; i < sql_num_fields(mdb); i++) {
378 mdb->fields[i] = (SQL_FIELD *)malloc(sizeof(SQL_FIELD));
379 mdb->fields[i]->name = mdb->result[i];
380 mdb->fields[i]->length = cstrlen(mdb->fields[i]->name);
381 mdb->fields[i]->max_length = mdb->fields[i]->length;
382 for (j=1; j <= mdb->nrow; j++) {
384 if (mdb->result[i + mdb->ncolumn *j]) {
385 len = (uint32_t)cstrlen(mdb->result[i + mdb->ncolumn * j]);
389 if (len > mdb->fields[i]->max_length) {
390 mdb->fields[i]->max_length = len;
393 mdb->fields[i]->type = 0;
394 mdb->fields[i]->flags = 1; /* not null */
396 mdb->fields_defined = TRUE;
398 if (field > sql_num_fields(mdb)) {
399 field = sql_num_fields(mdb);
405 SQL_FIELD *my_sqlite_fetch_field(B_DB *mdb)
407 return mdb->fields[mdb->field++];
410 #endif /* HAVE_SQLITE */