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 * Retrieve database type
60 * Initialize database data structure. In principal this should
61 * never have errors, or it is really fatal.
64 db_init_database(JCR *jcr, const char *db_name, const char *db_user, const char *db_password,
65 const char *db_address, int db_port, const char *db_socket,
66 int mult_db_connections)
70 P(mutex); /* lock DB queue */
71 /* Look to see if DB already open */
72 if (!mult_db_connections) {
73 for (mdb=NULL; (mdb=(B_DB *)qnext(&db_list, &mdb->bq)); ) {
74 if (bstrcmp(mdb->db_name, db_name) &&
75 bstrcmp(mdb->db_address, db_address) &&
76 mdb->db_port == db_port) {
77 Dmsg2(300, "DB REopen %d %s\n", mdb->ref_count, db_name);
80 return mdb; /* already open */
84 Dmsg0(300, "db_open first time\n");
85 mdb = (B_DB *) malloc(sizeof(B_DB));
86 memset(mdb, 0, sizeof(B_DB));
87 mdb->db_name = bstrdup(db_name);
88 mdb->have_insert_id = TRUE;
89 mdb->errmsg = get_pool_memory(PM_EMSG); /* get error message buffer */
91 mdb->cmd = get_pool_memory(PM_EMSG); /* get command buffer */
92 mdb->cached_path = get_pool_memory(PM_FNAME);
93 mdb->cached_path_id = 0;
95 mdb->fname = get_pool_memory(PM_FNAME);
96 mdb->path = get_pool_memory(PM_FNAME);
97 mdb->esc_name = get_pool_memory(PM_FNAME);
98 mdb->allow_transactions = mult_db_connections;
99 qinsert(&db_list, &mdb->bq); /* put db in list */
105 * Now actually open the database. This can generate errors,
106 * which are returned in the errmsg
108 * DO NOT close the database or free(mdb) here !!!!
111 db_open_database(JCR *jcr, B_DB *mdb)
119 if (mdb->connected) {
123 mdb->connected = FALSE;
125 if ((errstat=rwl_init(&mdb->lock)) != 0) {
126 Mmsg1(&mdb->errmsg, _("Unable to initialize DB lock. ERR=%s\n"),
132 /* open the database */
133 len = strlen(working_directory) + strlen(mdb->db_name) + 5;
134 db_name = (char *)malloc(len);
135 strcpy(db_name, working_directory);
136 strcat(db_name, "/");
137 strcat(db_name, mdb->db_name);
138 strcat(db_name, ".db");
139 if (stat(db_name, &statbuf) != 0) {
140 Mmsg1(&mdb->errmsg, _("Database %s does not exist, please create it.\n"),
148 int stat = sqlite3_open(db_name, &mdb->db);
149 if (stat != SQLITE_OK) {
150 mdb->sqlite_errmsg = (char *)sqlite3_errmsg(mdb->db);
152 mdb->sqlite_errmsg = NULL;
156 mdb->db = sqlite_open(
157 db_name, /* database name */
159 &mdb->sqlite_errmsg); /* error message */
162 Dmsg0(300, "sqlite_open\n");
164 if (mdb->db == NULL) {
165 Mmsg2(&mdb->errmsg, _("Unable to open Database=%s. ERR=%s\n"),
166 db_name, mdb->sqlite_errmsg ? mdb->sqlite_errmsg : _("unknown"));
172 if (!check_tables_version(jcr, mdb)) {
177 mdb->connected = true;
183 db_close_database(JCR *jcr, B_DB *mdb)
188 db_end_transaction(jcr, mdb);
191 if (mdb->ref_count == 0) {
193 if (mdb->connected && mdb->db) {
194 sqlite_close(mdb->db);
196 rwl_destroy(&mdb->lock);
197 free_pool_memory(mdb->errmsg);
198 free_pool_memory(mdb->cmd);
199 free_pool_memory(mdb->cached_path);
200 free_pool_memory(mdb->fname);
201 free_pool_memory(mdb->path);
202 free_pool_memory(mdb->esc_name);
212 * Return the next unique index (auto-increment) for
213 * the given table. Return 0 on error.
215 int db_next_index(JCR *jcr, B_DB *mdb, char *table, char *index)
223 "SELECT id FROM NextId WHERE TableName=\"%s\"", table);
224 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
225 Mmsg(mdb->errmsg, _("next_index query error: ERR=%s\n"), sql_strerror(mdb));
229 if ((row = sql_fetch_row(mdb)) == NULL) {
230 Mmsg(mdb->errmsg, _("Error fetching index: ERR=%s\n"), sql_strerror(mdb));
234 bstrncpy(index, row[0], 28);
235 sql_free_result(mdb);
238 "UPDATE NextId SET id=id+1 WHERE TableName=\"%s\"", table);
239 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
240 Mmsg(mdb->errmsg, _("next_index update error: ERR=%s\n"), sql_strerror(mdb));
244 sql_free_result(mdb);
248 strcpy(index, "NULL");
254 * Escape strings so that SQLite is happy
256 * NOTE! len is the length of the old string. Your new
257 * string must be long enough (max 2*old+1) to hold
258 * the escaped output.
261 db_escape_string(char *snew, char *old, int len)
288 DB_RESULT_HANDLER *result_handler;
293 * Convert SQLite's callback into Bacula DB callback
295 static int sqlite_result(void *arh_data, int num_fields, char **rows, char **col_names)
297 struct rh_data *rh_data = (struct rh_data *)arh_data;
299 if (rh_data->result_handler) {
300 (*(rh_data->result_handler))(rh_data->ctx, num_fields, rows);
306 * Submit a general SQL command (cmd), and for each row returned,
307 * the sqlite_handler is called with the ctx.
309 int db_sql_query(B_DB *mdb, const char *query, DB_RESULT_HANDLER *result_handler, void *ctx)
311 struct rh_data rh_data;
315 if (mdb->sqlite_errmsg) {
317 sqlite3_free(mdb->sqlite_errmsg);
319 actuallyfree(mdb->sqlite_errmsg);
321 mdb->sqlite_errmsg = NULL;
323 rh_data.result_handler = result_handler;
325 stat = sqlite_exec(mdb->db, query, sqlite_result, (void *)&rh_data, &mdb->sqlite_errmsg);
327 Mmsg(mdb->errmsg, _("Query failed: %s: ERR=%s\n"), query, sql_strerror(mdb));
336 * Submit a sqlite query and retrieve all the data
338 int my_sqlite_query(B_DB *mdb, const char *cmd)
342 if (mdb->sqlite_errmsg) {
343 actuallyfree(mdb->sqlite_errmsg);
344 mdb->sqlite_errmsg = NULL;
346 stat = sqlite_get_table(mdb->db, (char *)cmd, &mdb->result, &mdb->nrow, &mdb->ncolumn,
347 &mdb->sqlite_errmsg);
348 mdb->row = 0; /* row fetched */
352 /* Fetch one row at a time */
353 SQL_ROW my_sqlite_fetch_row(B_DB *mdb)
355 if (mdb->row >= mdb->nrow) {
359 return &mdb->result[mdb->ncolumn * mdb->row];
362 void my_sqlite_free_table(B_DB *mdb)
366 if (mdb->fields_defined) {
367 for (i=0; i < sql_num_fields(mdb); i++) {
368 free(mdb->fields[i]);
371 mdb->fields_defined = false;
373 sqlite_free_table(mdb->result);
374 mdb->nrow = mdb->ncolumn = 0;
377 void my_sqlite_field_seek(B_DB *mdb, int field)
380 if (mdb->result == NULL) {
383 /* On first call, set up the fields */
384 if (!mdb->fields_defined && sql_num_fields(mdb) > 0) {
385 mdb->fields = (SQL_FIELD **)malloc(sizeof(SQL_FIELD) * mdb->ncolumn);
386 for (i=0; i < sql_num_fields(mdb); i++) {
387 mdb->fields[i] = (SQL_FIELD *)malloc(sizeof(SQL_FIELD));
388 mdb->fields[i]->name = mdb->result[i];
389 mdb->fields[i]->length = cstrlen(mdb->fields[i]->name);
390 mdb->fields[i]->max_length = mdb->fields[i]->length;
391 for (j=1; j <= mdb->nrow; j++) {
393 if (mdb->result[i + mdb->ncolumn *j]) {
394 len = (uint32_t)cstrlen(mdb->result[i + mdb->ncolumn * j]);
398 if (len > mdb->fields[i]->max_length) {
399 mdb->fields[i]->max_length = len;
402 mdb->fields[i]->type = 0;
403 mdb->fields[i]->flags = 1; /* not null */
405 mdb->fields_defined = TRUE;
407 if (field > sql_num_fields(mdb)) {
408 field = sql_num_fields(mdb);
414 SQL_FIELD *my_sqlite_fetch_field(B_DB *mdb)
416 return mdb->fields[mdb->field++];
419 #endif /* HAVE_SQLITE */