From eede46de3159f382b60acd9d329ef3cce2e1f0f8 Mon Sep 17 00:00:00 2001 From: Kern Sibbald Date: Mon, 29 Sep 2008 20:53:51 +0000 Subject: [PATCH] Apply dbi driver patch from Joao git-svn-id: https://bacula.svn.sourceforge.net/svnroot/bacula/trunk@7665 91ce42f0-d328-0410-95d8-f526ca767f89 --- bacula/AUTHORS | 1 + bacula/autoconf/bacula-macros/db.m4 | 58 ++++ bacula/autoconf/configure.in | 5 + bacula/configure | 69 +++++ bacula/src/cats/cats.h | 57 ++-- bacula/src/cats/dbi.c | 392 +++++++++++++++++----------- bacula/src/cats/sql.c | 82 +++++- bacula/src/cats/sql_cmds.c | 53 +++- bacula/src/cats/sql_cmds.h | 10 +- bacula/src/cats/sql_list.c | 37 +-- bacula/technotes-2.5 | 1 + 11 files changed, 552 insertions(+), 213 deletions(-) diff --git a/bacula/AUTHORS b/bacula/AUTHORS index 42269bdb44..a358522a2f 100644 --- a/bacula/AUTHORS +++ b/bacula/AUTHORS @@ -95,4 +95,5 @@ Tomas Cameron Tullio Andreatta Ulrich Leodolter Wolfgang Denk +Yuri Timofeev Yves Orton diff --git a/bacula/autoconf/bacula-macros/db.m4 b/bacula/autoconf/bacula-macros/db.m4 index 4ff0db9f77..69cce458e1 100644 --- a/bacula/autoconf/bacula-macros/db.m4 +++ b/bacula/autoconf/bacula-macros/db.m4 @@ -222,6 +222,64 @@ AC_ARG_WITH(dbi-driver, fi DB_PROG_LIB=$SQL_LIBDIR/libpq.a ;; + "sqlite") + db_prog="sqlite" + if test -f /usr/local/bin/sqlite; then + SQL_BINDIR=/usr/local/bin + if test -d /usr/local/lib64; then + SQL_LIBDIR=/usr/local/lib64 + else + SQL_LIBDIR=/usr/local/lib + fi + elif test -f /usr/bin/sqlite; then + SQL_BINDIR=/usr/bin + if test -d /usr/lib64; then + SQL_LIBDIR=/usr/lib64 + else + SQL_LIBDIR=/usr/lib + fi + elif test -f $withval/bin/sqlite; then + SQL_BINDIR=$withval/bin + if test -d $withval/lib64; then + SQL_LIBDIR=$withval/lib64 + else + SQL_LIBDIR=$withval/lib + fi + else + AC_MSG_RESULT(no) + AC_MSG_ERROR(Unable to find sqlite in standard locations) + fi + DB_PROG_LIB=$SQL_LIBDIR/libsqlite.a + ;; + "sqlite3") + db_prog="sqlite3" + if test -f /usr/local/bin/sqlite3; then + SQL_BINDIR=/usr/local/bin + if test -d /usr/local/lib64; then + SQL_LIBDIR=/usr/local/lib64 + else + SQL_LIBDIR=/usr/local/lib + fi + elif test -f /usr/bin/sqlite3; then + SQL_BINDIR=/usr/bin + if test -d /usr/lib64; then + SQL_LIBDIR=/usr/lib64 + else + SQL_LIBDIR=/usr/lib + fi + elif test -f $withval/bin/sqlite3; then + SQL_BINDIR=$withval/bin + if test -d $withval/lib64; then + SQL_LIBDIR=$withval/lib64 + else + SQL_LIBDIR=$withval/lib + fi + else + AC_MSG_RESULT(no) + AC_MSG_ERROR(Unable to find sqlite in standard locations) + fi + DB_PROG_LIB=$SQL_LIBDIR/libsqlite3.a + ;; *) AC_MSG_RESULT(no) AC_MSG_ERROR(Unable to set DBI driver. $withval is not supported) diff --git a/bacula/autoconf/configure.in b/bacula/autoconf/configure.in index 77ac433bb1..81527a19f9 100644 --- a/bacula/autoconf/configure.in +++ b/bacula/autoconf/configure.in @@ -1451,6 +1451,11 @@ if test x$DB_TYPE = xdbi; then pkg=$? fi + if test $DB_PROG = sqlite3; then + A=`nm $DB_PROG_LIB | grep pthread_mutex_lock` + pkg=$? + fi + if test $pkg = 0; then AC_ARG_ENABLE(batch-insert, [ --enable-batch-insert enable the DB batch insert code [disabled]], diff --git a/bacula/configure b/bacula/configure index 9afe47dd81..0b48a6c0e6 100755 --- a/bacula/configure +++ b/bacula/configure @@ -22438,6 +22438,70 @@ echo "$as_me: error: Unable to find psql in standard locations" >&2;} fi DB_PROG_LIB=$SQL_LIBDIR/libpq.a ;; + "sqlite") + db_prog="sqlite" + if test -f /usr/local/bin/sqlite; then + SQL_BINDIR=/usr/local/bin + if test -d /usr/local/lib64; then + SQL_LIBDIR=/usr/local/lib64 + else + SQL_LIBDIR=/usr/local/lib + fi + elif test -f /usr/bin/sqlite; then + SQL_BINDIR=/usr/bin + if test -d /usr/lib64; then + SQL_LIBDIR=/usr/lib64 + else + SQL_LIBDIR=/usr/lib + fi + elif test -f $withval/bin/sqlite; then + SQL_BINDIR=$withval/bin + if test -d $withval/lib64; then + SQL_LIBDIR=$withval/lib64 + else + SQL_LIBDIR=$withval/lib + fi + else + { echo "$as_me:$LINENO: result: no" >&5 +echo "${ECHO_T}no" >&6; } + { { echo "$as_me:$LINENO: error: Unable to find sqlite in standard locations" >&5 +echo "$as_me: error: Unable to find sqlite in standard locations" >&2;} + { (exit 1); exit 1; }; } + fi + DB_PROG_LIB=$SQL_LIBDIR/libsqlite.a + ;; + "sqlite3") + db_prog="sqlite3" + if test -f /usr/local/bin/sqlite3; then + SQL_BINDIR=/usr/local/bin + if test -d /usr/local/lib64; then + SQL_LIBDIR=/usr/local/lib64 + else + SQL_LIBDIR=/usr/local/lib + fi + elif test -f /usr/bin/sqlite3; then + SQL_BINDIR=/usr/bin + if test -d /usr/lib64; then + SQL_LIBDIR=/usr/lib64 + else + SQL_LIBDIR=/usr/lib + fi + elif test -f $withval/bin/sqlite3; then + SQL_BINDIR=$withval/bin + if test -d $withval/lib64; then + SQL_LIBDIR=$withval/lib64 + else + SQL_LIBDIR=$withval/lib + fi + else + { echo "$as_me:$LINENO: result: no" >&5 +echo "${ECHO_T}no" >&6; } + { { echo "$as_me:$LINENO: error: Unable to find sqlite in standard locations" >&5 +echo "$as_me: error: Unable to find sqlite in standard locations" >&2;} + { (exit 1); exit 1; }; } + fi + DB_PROG_LIB=$SQL_LIBDIR/libsqlite3.a + ;; *) { echo "$as_me:$LINENO: result: no" >&5 echo "${ECHO_T}no" >&6; } @@ -22514,6 +22578,11 @@ if test x$DB_TYPE = xdbi; then pkg=$? fi + if test $DB_PROG = sqlite3; then + A=`nm $DB_PROG_LIB | grep pthread_mutex_lock` + pkg=$? + fi + if test $pkg = 0; then # Check whether --enable-batch-insert was given. if test "${enable_batch_insert+set}" = set; then diff --git a/bacula/src/cats/cats.h b/bacula/src/cats/cats.h index f79a173fc1..63e6b08a53 100644 --- a/bacula/src/cats/cats.h +++ b/bacula/src/cats/cats.h @@ -42,7 +42,7 @@ */ /* - Here is how database versions work. + Here is how database versions work. While I am working on a new release with database changes, the update scripts are in the src/cats directory under the names @@ -59,7 +59,7 @@ will be copied to the updatedb directory with the correct name (in the present case 8 to 9). - Now, in principle, each of the different DB implementations + Now, in principle, each of the different DB implementations can have a different version, but in practice they are all the same (simplifies things). The exception is the internal database, which is no longer used, and hence, no longer changes. @@ -72,7 +72,8 @@ enum { SQL_TYPE_MYSQL = 0, SQL_TYPE_POSTGRESQL = 1, - SQL_TYPE_SQLITE = 2 + SQL_TYPE_SQLITE = 2, + SQL_TYPE_SQLITE3 }; @@ -178,14 +179,14 @@ struct B_DB { #define sql_num_fields(x) ((x)->ncolumn) #define SQL_ROW char** -#define sql_batch_start(x,y) my_batch_start(x,y) -#define sql_batch_end(x,y,z) my_batch_end(x,y,z) +#define sql_batch_start(x,y) my_batch_start(x,y) +#define sql_batch_end(x,y,z) my_batch_end(x,y,z) #define sql_batch_insert(x,y,z) my_batch_insert(x,y,z) #define sql_batch_lock_path_query my_sqlite_batch_lock_query #define sql_batch_lock_filename_query my_sqlite_batch_lock_query #define sql_batch_unlock_tables_query my_sqlite_batch_unlock_query #define sql_batch_fill_filename_query my_sqlite_batch_fill_filename_query -#define sql_batch_fill_path_query my_sqlite_batch_fill_path_query +#define sql_batch_fill_path_query my_sqlite_batch_fill_path_query /* In cats/sqlite.c */ void my_sqlite_free_table(B_DB *mdb); @@ -202,7 +203,7 @@ extern const char* my_sqlite_batch_fill_path_query; #else /* S Q L I T E 3 */ - + #ifdef HAVE_SQLITE3 @@ -307,8 +308,8 @@ struct B_DB { #define sql_field_seek(x, y) my_sqlite_field_seek((x), (y)) #define sql_fetch_field(x) my_sqlite_fetch_field(x) #define sql_num_fields(x) ((x)->ncolumn) -#define sql_batch_start(x,y) my_batch_start(x,y) -#define sql_batch_end(x,y,z) my_batch_end(x,y,z) +#define sql_batch_start(x,y) my_batch_start(x,y) +#define sql_batch_end(x,y,z) my_batch_end(x,y,z) #define sql_batch_insert(x,y,z) my_batch_insert(x,y,z) #define SQL_ROW char** #define sql_batch_lock_path_query my_sqlite_batch_lock_query @@ -395,8 +396,8 @@ struct B_DB { #define SQL_FIELD MYSQL_FIELD #define sql_batch_start(x,y) my_batch_start(x,y) -#define sql_batch_end(x,y,z) my_batch_end(x,y,z) -#define sql_batch_insert(x,y,z) my_batch_insert(x,y,z) +#define sql_batch_end(x,y,z) my_batch_end(x,y,z) +#define sql_batch_insert(x,y,z) my_batch_insert(x,y,z) #define sql_batch_lock_path_query my_mysql_batch_lock_path_query #define sql_batch_lock_filename_query my_mysql_batch_lock_filename_query #define sql_batch_unlock_tables_query my_mysql_batch_unlock_tables_query @@ -476,7 +477,7 @@ struct B_DB { POOLMEM *esc_path; /* Escaped path name */ int fnl; /* file name length */ int pnl; /* path name length */ -}; +}; void my_postgresql_free_result(B_DB *mdb); POSTGRESQL_ROW my_postgresql_fetch_row (B_DB *mdb); @@ -513,8 +514,8 @@ extern const char* my_pg_batch_fill_path_query; #define sql_fetch_field(x) my_postgresql_fetch_field(x) #define sql_num_fields(x) ((x)->num_fields) -#define sql_batch_start(x,y) my_postgresql_batch_start(x,y) -#define sql_batch_end(x,y,z) my_postgresql_batch_end(x,y,z) +#define sql_batch_start(x,y) my_postgresql_batch_start(x,y) +#define sql_batch_end(x,y,z) my_postgresql_batch_end(x,y,z) #define sql_batch_insert(x,y,z) my_postgresql_batch_insert(x,y,z) #define sql_batch_lock_path_query my_pg_batch_lock_path_query #define sql_batch_lock_filename_query my_pg_batch_lock_filename_query @@ -602,7 +603,7 @@ struct B_DB { POOLMEM *esc_path; /* Escaped path name */ int fnl; /* file name length */ int pnl; /* path name length */ -}; +}; void my_dbi_free_result(B_DB *mdb); DBI_ROW my_dbi_fetch_row (B_DB *mdb); @@ -622,15 +623,15 @@ typedef struct ATTR_DBR ATTR_DBR; int my_dbi_batch_insert(JCR *jcr, B_DB *mdb, ATTR_DBR *ar); char *my_postgresql_copy_escape(char *dest, char *src, size_t len); // typedefs for libdbi work with postgresql copy insert -typedef int (*custom_function_insert_t)(void*, const char*, int); +typedef int (*custom_function_insert_t)(void*, const char*, int); typedef char* (*custom_function_error_t)(void*); typedef int (*custom_function_end_t)(void*, const char*); -extern const char* my_dbi_batch_lock_path_query[3]; -extern const char* my_dbi_batch_lock_filename_query[3]; -extern const char* my_dbi_batch_unlock_tables_query[3]; -extern const char* my_dbi_batch_fill_filename_query[3]; -extern const char* my_dbi_batch_fill_path_query[3]; +extern const char* my_dbi_batch_lock_path_query[4]; +extern const char* my_dbi_batch_lock_filename_query[4]; +extern const char* my_dbi_batch_unlock_tables_query[4]; +extern const char* my_dbi_batch_fill_filename_query[4]; +extern const char* my_dbi_batch_fill_path_query[4]; /* "Generic" names for easier conversion */ #define sql_store_result(x) (x)->result @@ -647,8 +648,8 @@ extern const char* my_dbi_batch_fill_path_query[3]; #define sql_field_seek(x, y) my_dbi_field_seek((x), (y)) #define sql_fetch_field(x) my_dbi_fetch_field(x) #define sql_num_fields(x) ((x)->num_fields) -#define sql_batch_start(x,y) my_dbi_batch_start(x,y) -#define sql_batch_end(x,y,z) my_dbi_batch_end(x,y,z) +#define sql_batch_start(x,y) my_dbi_batch_start(x,y) +#define sql_batch_end(x,y,z) my_dbi_batch_end(x,y,z) #define sql_batch_insert(x,y,z) my_dbi_batch_insert(x,y,z) #define sql_batch_lock_path_query my_dbi_batch_lock_path_query[db_type] #define sql_batch_lock_filename_query my_dbi_batch_lock_filename_query[db_type] @@ -725,13 +726,13 @@ struct B_DB { */ struct B_DB { int dummy; /* for SunOS compiler */ -}; +}; #endif /* __SQL_C */ -/* ============================================================== +/* ============================================================== * - * What follows are definitions that are used "globally" for all + * What follows are definitions that are used "globally" for all * the different SQL engines and both inside and external to the * cats directory. */ @@ -1002,7 +1003,7 @@ struct MEDIA_DBR { char cLastWritten[MAX_TIME_LENGTH]; /* LastWritten returned from DB */ char cLabelDate[MAX_TIME_LENGTH]; /* LabelData returned from DB */ char cInitialWrite[MAX_TIME_LENGTH]; /* InitialWrite returned from DB */ - bool set_first_written; + bool set_first_written; bool set_label_date; }; @@ -1052,7 +1053,7 @@ struct db_int64_ctx { #include "sql_cmds.h" /* - * Exported globals from sql.c + * Exported globals from sql.c */ extern int DLL_IMP_EXP db_type; /* SQL engine type index */ diff --git a/bacula/src/cats/dbi.c b/bacula/src/cats/dbi.c index 4638689a5a..ea089a1955 100644 --- a/bacula/src/cats/dbi.c +++ b/bacula/src/cats/dbi.c @@ -77,7 +77,7 @@ db_get_type(void) */ B_DB * db_init_database(JCR *jcr, const char *db_name, const char *db_user, const char *db_password, - const char *db_address, int db_port, const char *db_socket, + const char *db_address, int db_port, const char *db_socket, int mult_db_connections) { B_DB *mdb; @@ -89,9 +89,9 @@ db_init_database(JCR *jcr, const char *db_name, const char *db_user, const char Jmsg(jcr, M_FATAL, 0, _("A dbi driver for DBI must be supplied.\n")); return NULL; } - - /* Do the correct selection of driver. - * Can be one of the varius supported by libdbi + + /* Do the correct selection of driver. + * Can be one of the varius supported by libdbi */ switch (db_type) { case SQL_TYPE_MYSQL: @@ -101,13 +101,16 @@ db_init_database(JCR *jcr, const char *db_name, const char *db_user, const char bstrncpy(db_driver,"pgsql", sizeof(db_driver)); break; case SQL_TYPE_SQLITE: - bstrncpy(db_driver,"pgsql", sizeof(db_driver)); + bstrncpy(db_driver,"sqlite", sizeof(db_driver)); + break; + case SQL_TYPE_SQLITE3: + bstrncpy(db_driver,"sqlite3", sizeof(db_driver)); break; } - + /* Set db_driverdir whereis is the libdbi drivers */ bstrncpy(db_driverdir, DBI_DRIVER_DIR, 255); - + if (!db_user) { Jmsg(jcr, M_FATAL, 0, _("A user name for DBI must be supplied.\n")); return NULL; @@ -119,8 +122,8 @@ db_init_database(JCR *jcr, const char *db_name, const char *db_user, const char if (bstrcmp(mdb->db_name, db_name) && bstrcmp(mdb->db_address, db_address) && bstrcmp(mdb->db_driver, db_driver) && - mdb->db_port == db_port) { - Dmsg4(100, "DB REopen %d %s %s erro: %d\n", mdb->ref_count, db_driver, db_name, + mdb->db_port == db_port) { + Dmsg4(100, "DB REopen %d %s %s erro: %d\n", mdb->ref_count, db_driver, db_name, dbi_conn_error(mdb->db, NULL)); mdb->ref_count++; V(mutex); @@ -175,13 +178,16 @@ db_init_database(JCR *jcr, const char *db_name, const char *db_user, const char */ int db_open_database(JCR *jcr, B_DB *mdb) -{ +{ int errstat; int dbstat; + uint8_t len; const char *errmsg; char buf[10], *port; - int numdrivers; - + int numdrivers; + char *db_name = NULL; + char *db_dir = NULL; + P(mutex); if (mdb->connected) { V(mutex); @@ -203,7 +209,7 @@ db_open_database(JCR *jcr, B_DB *mdb) } else { port = NULL; } - + numdrivers = dbi_initialize_r(mdb->db_driverdir, &(mdb->instance)); if (numdrivers < 0) { Mmsg2(&mdb->errmsg, _("Unable to locate the DBD drivers to DBI interface in: \n" @@ -213,36 +219,73 @@ db_open_database(JCR *jcr, B_DB *mdb) return 0; } mdb->db = (void **)dbi_conn_new_r(mdb->db_driver, mdb->instance); - dbi_conn_set_option(mdb->db, "host", mdb->db_address); /* default = localhost */ - dbi_conn_set_option(mdb->db, "port", port); /* default port */ - dbi_conn_set_option(mdb->db, "username", mdb->db_user); /* login name */ - dbi_conn_set_option(mdb->db, "password", mdb->db_password); /* password */ - dbi_conn_set_option(mdb->db, "dbname", mdb->db_name); /* database name */ - + /* Can be many types of databases */ + switch (mdb->db_type) { + case SQL_TYPE_MYSQL: + dbi_conn_set_option(mdb->db, "host", mdb->db_address); /* default = localhost */ + dbi_conn_set_option(mdb->db, "port", port); /* default port */ + dbi_conn_set_option(mdb->db, "username", mdb->db_user); /* login name */ + dbi_conn_set_option(mdb->db, "password", mdb->db_password); /* password */ + dbi_conn_set_option(mdb->db, "dbname", mdb->db_name); /* database name */ + break; + case SQL_TYPE_POSTGRESQL: + dbi_conn_set_option(mdb->db, "host", mdb->db_address); + dbi_conn_set_option(mdb->db, "port", port); + dbi_conn_set_option(mdb->db, "username", mdb->db_user); + dbi_conn_set_option(mdb->db, "password", mdb->db_password); + dbi_conn_set_option(mdb->db, "dbname", mdb->db_name); + break; + case SQL_TYPE_SQLITE: + len = strlen(working_directory) + 5; + db_dir = (char *)malloc(len); + strcpy(db_dir, working_directory); + strcat(db_dir, "/"); + len = strlen(mdb->db_name) + 5; + db_name = (char *)malloc(len); + strcpy(db_name, mdb->db_name); + strcat(db_name, ".db"); + dbi_conn_set_option(mdb->db, "sqlite_dbdir", db_dir); + dbi_conn_set_option(mdb->db, "dbname", db_name); + break; + case SQL_TYPE_SQLITE3: + len = strlen(working_directory) + 5; + db_dir = (char *)malloc(len); + strcpy(db_dir, working_directory); + strcat(db_dir, "/"); + len = strlen(mdb->db_name) + 5; + db_name = (char *)malloc(len); + strcpy(db_name, mdb->db_name); + strcat(db_name, ".db"); + dbi_conn_set_option(mdb->db, "sqlite3_dbdir", db_dir); + dbi_conn_set_option(mdb->db, "dbname", db_name); + Dmsg2(500, "SQLITE: %s %s\n", db_dir, db_name); + break; + } + /* If connection fails, try at 5 sec intervals for 30 seconds. */ for (int retry=0; retry < 6; retry++) { - - dbstat = dbi_conn_connect(mdb->db); + + dbstat = dbi_conn_connect(mdb->db); if ( dbstat == 0) { break; } - + dbi_conn_error(mdb->db, &errmsg); Dmsg1(50, "dbi error: %s\n", errmsg); - + bmicrosleep(5, 0); - + } - + if ( dbstat != 0 ) { Mmsg3(&mdb->errmsg, _("Unable to connect to DBI interface.\n" "Type=%s Database=%s User=%s\n" "It is probably not running or your password is incorrect.\n"), mdb->db_driver, mdb->db_name, mdb->db_user); V(mutex); - return 0; - } - + return 0; + } + Dmsg0(50, "dbi_real_connect done\n"); Dmsg3(50, "db_user=%s db_name=%s db_password=%s\n", mdb->db_user, mdb->db_name, @@ -254,7 +297,7 @@ db_open_database(JCR *jcr, B_DB *mdb) V(mutex); return 0; } - + switch (mdb->db_type) { case SQL_TYPE_MYSQL: /* Set connection timeout to 8 days specialy for batch mode */ @@ -269,10 +312,15 @@ db_open_database(JCR *jcr, B_DB *mdb) sql_query(mdb, "SET datestyle TO 'ISO, YMD'"); sql_query(mdb, "set standard_conforming_strings=on"); break; - case SQL_TYPE_SQLITE: - break; } - + + if(db_dir) { + free(db_dir); + } + if(db_name) { + free(db_name); + } + V(mutex); return 1; } @@ -324,8 +372,8 @@ db_close_database(JCR *jcr, B_DB *mdb) if (mdb->db_driver) { free(mdb->db_driver); } - free(mdb); - } + free(mdb); + } V(mutex); } @@ -350,9 +398,9 @@ int db_next_index(JCR *jcr, B_DB *mdb, char *table, char *index) * NOTE! len is the length of the old string. Your new * string must be long enough (max 2*old+1) to hold * the escaped output. - * + * * dbi_conn_quote_string_copy receives a pointer to pointer. - * We need copy the value of pointer to snew because libdbi change the + * We need copy the value of pointer to snew because libdbi change the * pointer */ void @@ -360,9 +408,9 @@ db_escape_string(JCR *jcr, B_DB *mdb, char *snew, char *old, int len) { char *inew; char *pnew; - + if (len == 0) { - snew[0] = 0; + snew[0] = 0; } else { /* correct the size of old basead in len * and copy new string to inew @@ -373,11 +421,11 @@ db_escape_string(JCR *jcr, B_DB *mdb, char *snew, char *old, int len) dbi_conn_escape_string_copy(mdb->db, inew, &pnew); free(inew); /* copy the escaped string to snew */ - bstrncpy(snew, pnew, 2 * len + 1); + bstrncpy(snew, pnew, 2 * len + 1); } - + Dmsg2(500, "dbi_conn_escape_string_copy %p %s\n",snew,snew); - + } /* @@ -430,9 +478,7 @@ DBI_ROW my_dbi_fetch_row(B_DB *mdb) DBI_ROW row = NULL; // by default, return NULL Dmsg0(500, "my_dbi_fetch_row start\n"); - - - if (!mdb->row || mdb->row_size < mdb->num_fields) { + if ((!mdb->row || mdb->row_size < mdb->num_fields) && mdb->num_rows > 0) { int num_fields = mdb->num_fields; Dmsg1(500, "we have need space of %d bytes\n", sizeof(char *) * mdb->num_fields); @@ -441,11 +487,11 @@ DBI_ROW my_dbi_fetch_row(B_DB *mdb) Dmsg2(500, "my_dbi_free_row row: '%p' num_fields: '%d'\n", mdb->row, mdb->num_fields); if (mdb->num_rows != 0) { for(j = 0; j < mdb->num_fields; j++) { - Dmsg2(500, "my_dbi_free_row row '%p' '%d'\n", mdb->row[j], j); + Dmsg2(500, "my_dbi_free_row row '%p' '%d'\n", mdb->row[j], j); if(mdb->row[j]) { free(mdb->row[j]); - } - } + } + } } free(mdb->row); } @@ -458,19 +504,19 @@ DBI_ROW my_dbi_fetch_row(B_DB *mdb) } // if still within the result set - if (mdb->row_number <= mdb->num_rows) { + if (mdb->row_number <= mdb->num_rows && mdb->row_number != DBI_ERROR_BADPTR) { Dmsg2(500, "my_dbi_fetch_row row number '%d' is acceptable (1..%d)\n", mdb->row_number, mdb->num_rows); // get each value from this row for (j = 0; j < mdb->num_fields; j++) { - mdb->row[j] = my_dbi_getvalue(mdb->result, mdb->row_number, j); - // allocate space to queue row + mdb->row[j] = my_dbi_getvalue(mdb->result, mdb->row_number, j); + // allocate space to queue row mdb->field_get = (DBI_FIELD_GET *)malloc(sizeof(DBI_FIELD_GET)); // store the pointer in queue - mdb->field_get->value = mdb->row[j]; + mdb->field_get->value = mdb->row[j]; Dmsg4(500, "my_dbi_fetch_row row[%d] field: '%p' in queue: '%p' has value: '%s'\n", j, mdb->row[j], mdb->field_get->value, mdb->row[j]); // insert in queue to future free - qinsert(&dbi_getvalue_list, &mdb->field_get->bq); + qinsert(&dbi_getvalue_list, &mdb->field_get->bq); } // increment the row number for the next call mdb->row_number++; @@ -527,7 +573,7 @@ DBI_FIELD * my_dbi_fetch_field(B_DB *mdb) Dmsg1(500, "allocating space for %d fields\n", mdb->num_fields); mdb->fields = (DBI_FIELD *)malloc(sizeof(DBI_FIELD) * mdb->num_fields); mdb->fields_size = mdb->num_fields; - + for (i = 0; i < mdb->num_fields; i++) { // num_fileds is starting at 1, increment i by 1 dbi_index = i + 1; @@ -582,16 +628,16 @@ int my_dbi_query(B_DB *mdb, const char *query) dbi_result_free(mdb->result); /* hmm, someone forgot to free?? */ mdb->result = NULL; } - + mdb->result = (void **)dbi_conn_query(mdb->db, query); - + if (!mdb->result) { - Dmsg2(50, "Query failed: %s %p\n", query, mdb->result); + Dmsg2(50, "Query failed: %s %p\n", query, mdb->result); goto bail_out; } - + mdb->status = (dbi_error_flag) dbi_conn_error(mdb->db, &errmsg); - + if (mdb->status == DBI_ERROR_NONE) { Dmsg1(500, "we have a result\n", query); @@ -624,26 +670,25 @@ bail_out: } void my_dbi_free_result(B_DB *mdb) -{ - +{ + DBI_FIELD_GET *f; - db_lock(mdb); - int i = 0; + db_lock(mdb); if (mdb->result) { Dmsg1(500, "my_dbi_free_result result '%p'\n", mdb->result); dbi_result_free(mdb->result); } mdb->result = NULL; - + if (mdb->row) { free(mdb->row); } - + /* now is time to free all value return by my_dbi_get_value * this is necessary because libdbi don't free memory return by yours results * and Bacula has some routine wich call more than once time my_dbi_fetch_row - * + * * Using a queue to store all pointer allocate is a good way to free all things * when necessary */ @@ -652,24 +697,24 @@ void my_dbi_free_result(B_DB *mdb) free(f->value); free(f); } - + mdb->row = NULL; - + if (mdb->fields) { free(mdb->fields); mdb->fields = NULL; } db_unlock(mdb); Dmsg0(500, "my_dbi_free_result finish\n"); - + } -const char *my_dbi_strerror(B_DB *mdb) +const char *my_dbi_strerror(B_DB *mdb) { const char *errmsg; - + dbi_conn_error(mdb->db, &errmsg); - + return errmsg; } @@ -677,16 +722,16 @@ const char *my_dbi_strerror(B_DB *mdb) /* * This can be a bit strang but is the one way to do - * + * * Returns 1 if OK * 0 if failed */ int my_dbi_batch_start(JCR *jcr, B_DB *mdb) { char *query = "COPY batch FROM STDIN"; - + Dmsg0(500, "my_dbi_batch_start started\n"); - + switch (mdb->db_type) { case SQL_TYPE_MYSQL: db_lock(mdb); @@ -707,11 +752,8 @@ int my_dbi_batch_start(JCR *jcr, B_DB *mdb) return 1; break; case SQL_TYPE_POSTGRESQL: - - //query = "COPY batch FROM STDIN"; - if (my_dbi_query(mdb, - "CREATE TEMPORARY TABLE batch (" + if (my_dbi_query(mdb, "CREATE TEMPORARY TABLE batch (" "fileindex int," "jobid int," "path varchar," @@ -722,7 +764,7 @@ int my_dbi_batch_start(JCR *jcr, B_DB *mdb) Dmsg0(500, "my_dbi_batch_start failed\n"); return 1; } - + // We are starting a new query. reset everything. mdb->num_rows = -1; mdb->row_number = -1; @@ -744,7 +786,7 @@ int my_dbi_batch_start(JCR *jcr, B_DB *mdb) mdb->status = (dbi_error_flag)dbi_conn_error(mdb->db, NULL); //mdb->status = DBI_ERROR_NONE; - + if (mdb->status == DBI_ERROR_NONE) { // how many fields in the set? mdb->num_fields = dbi_result_get_numfields(mdb->result); @@ -761,8 +803,24 @@ int my_dbi_batch_start(JCR *jcr, B_DB *mdb) break; case SQL_TYPE_SQLITE: db_lock(mdb); - if (my_dbi_query(mdb, - "CREATE TEMPORARY TABLE batch (" + if (my_dbi_query(mdb, "CREATE TEMPORARY TABLE batch (" + "FileIndex integer," + "JobId integer," + "Path blob," + "Name blob," + "LStat tinyblob," + "MD5 tinyblob)") == 1) + { + Dmsg0(500, "my_dbi_batch_start failed\n"); + goto bail_out; + } + db_unlock(mdb); + Dmsg0(500, "my_dbi_batch_start finishing\n"); + return 1; + break; + case SQL_TYPE_SQLITE3: + db_lock(mdb); + if (my_dbi_query(mdb, "CREATE TEMPORARY TABLE batch (" "FileIndex integer," "JobId integer," "Path blob," @@ -778,7 +836,7 @@ int my_dbi_batch_start(JCR *jcr, B_DB *mdb) return 1; break; } - + bail_out: Mmsg1(&mdb->errmsg, _("error starting batch mode: %s"), my_dbi_strerror(mdb)); mdb->status = (dbi_error_flag) 0; @@ -792,9 +850,9 @@ int my_dbi_batch_end(JCR *jcr, B_DB *mdb, const char *error) { int res = 0; int count = 30; - int (*custom_function)(void*, const char*) = NULL; + int (*custom_function)(void*, const char*) = NULL; dbi_conn_t *myconn = (dbi_conn_t *)(mdb->db); - + Dmsg0(500, "my_dbi_batch_end started\n"); if (!mdb) { /* no files ? */ @@ -810,16 +868,16 @@ int my_dbi_batch_end(JCR *jcr, B_DB *mdb, const char *error) case SQL_TYPE_POSTGRESQL: custom_function = (custom_function_end_t)dbi_driver_specific_function(dbi_conn_get_driver(mdb->db), "PQputCopyEnd"); - - do { - res = (*custom_function)(myconn->connection, error); + + do { + res = (*custom_function)(myconn->connection, error); } while (res == 0 && --count > 0); if (res == 1) { Dmsg0(500, "ok\n"); mdb->status = (dbi_error_flag) 1; } - + if (res <= 0) { Dmsg0(500, "we failed\n"); mdb->status = (dbi_error_flag) 0; @@ -831,23 +889,28 @@ int my_dbi_batch_end(JCR *jcr, B_DB *mdb, const char *error) mdb->status = (dbi_error_flag) 0; } break; + case SQL_TYPE_SQLITE3: + if(mdb) { + mdb->status = (dbi_error_flag) 0; + } + break; } Dmsg0(500, "my_dbi_batch_end finishing\n"); - return true; + return true; } -/* - * This function is big and use a big switch. +/* + * This function is big and use a big switch. * In near future is better split in small functions * and refactory. - * + * */ int my_dbi_batch_insert(JCR *jcr, B_DB *mdb, ATTR_DBR *ar) { int res; - int count=30; + int count=30; dbi_conn_t *myconn = (dbi_conn_t *)(mdb->db); int (*custom_function)(void*, const char*, int) = NULL; char* (*custom_function_error)(void*) = NULL; @@ -857,7 +920,7 @@ int my_dbi_batch_insert(JCR *jcr, B_DB *mdb, ATTR_DBR *ar) Dmsg0(500, "my_dbi_batch_insert started \n"); - mdb->esc_name = check_pool_memory_size(mdb->esc_name, mdb->fnl*2+1); + mdb->esc_name = check_pool_memory_size(mdb->esc_name, mdb->fnl*2+1); mdb->esc_path = check_pool_memory_size(mdb->esc_path, mdb->pnl*2+1); if (ar->Digest == NULL || ar->Digest[0] == 0) { @@ -871,33 +934,33 @@ int my_dbi_batch_insert(JCR *jcr, B_DB *mdb, ATTR_DBR *ar) db_escape_string(jcr, mdb, mdb->esc_name, mdb->fname, mdb->fnl); db_escape_string(jcr, mdb, mdb->esc_path, mdb->path, mdb->pnl); len = Mmsg(mdb->cmd, "INSERT INTO batch VALUES (%u,%s,'%s','%s','%s','%s')", - ar->FileIndex, edit_int64(ar->JobId,ed1), mdb->esc_path, + ar->FileIndex, edit_int64(ar->JobId,ed1), mdb->esc_path, mdb->esc_name, ar->attr, digest); - + if (my_dbi_query(mdb,mdb->cmd) == 1) { Dmsg0(500, "my_dbi_batch_insert failed\n"); goto bail_out; } - + Dmsg0(500, "my_dbi_batch_insert finishing\n"); - + return 1; break; case SQL_TYPE_POSTGRESQL: my_postgresql_copy_escape(mdb->esc_name, mdb->fname, mdb->fnl); my_postgresql_copy_escape(mdb->esc_path, mdb->path, mdb->pnl); - len = Mmsg(mdb->cmd, "%u\t%s\t%s\t%s\t%s\t%s\n", - ar->FileIndex, edit_int64(ar->JobId, ed1), mdb->esc_path, + len = Mmsg(mdb->cmd, "%u\t%s\t%s\t%s\t%s\t%s\n", + ar->FileIndex, edit_int64(ar->JobId, ed1), mdb->esc_path, mdb->esc_name, ar->attr, digest); - + /* libdbi don't support CopyData and we need call a postgresql * specific function to do this work */ Dmsg2(500, "my_dbi_batch_insert :\n %s \ncmd_size: %d",mdb->cmd, len); if ((custom_function = (custom_function_insert_t)dbi_driver_specific_function(dbi_conn_get_driver(mdb->db), "PQputCopyData")) != NULL) { - do { + do { res = (*custom_function)(myconn->connection, mdb->cmd, len); } while (res == 0 && --count > 0); @@ -915,7 +978,7 @@ int my_dbi_batch_insert(JCR *jcr, B_DB *mdb, ATTR_DBR *ar) Dmsg0(500, "my_dbi_batch_insert finishing\n"); return mdb->status; } else { - // ensure to detect a PQerror + // ensure to detect a PQerror custom_function_error = (custom_function_error_t)dbi_driver_specific_function(dbi_conn_get_driver(mdb->db), "PQerrorMessage"); Dmsg1(500, "my_dbi_batch_insert failed\n PQerrorMessage: %s", (*custom_function_error)(myconn->connection)); goto bail_out; @@ -925,24 +988,40 @@ int my_dbi_batch_insert(JCR *jcr, B_DB *mdb, ATTR_DBR *ar) db_escape_string(jcr, mdb, mdb->esc_name, mdb->fname, mdb->fnl); db_escape_string(jcr, mdb, mdb->esc_path, mdb->path, mdb->pnl); len = Mmsg(mdb->cmd, "INSERT INTO batch VALUES (%u,%s,'%s','%s','%s','%s')", - ar->FileIndex, edit_int64(ar->JobId,ed1), mdb->esc_path, + ar->FileIndex, edit_int64(ar->JobId,ed1), mdb->esc_path, mdb->esc_name, ar->attr, digest); if (my_dbi_query(mdb,mdb->cmd) == 1) { Dmsg0(500, "my_dbi_batch_insert failed\n"); goto bail_out; } - + Dmsg0(500, "my_dbi_batch_insert finishing\n"); - + + return 1; + break; + case SQL_TYPE_SQLITE3: + db_escape_string(jcr, mdb, mdb->esc_name, mdb->fname, mdb->fnl); + db_escape_string(jcr, mdb, mdb->esc_path, mdb->path, mdb->pnl); + len = Mmsg(mdb->cmd, "INSERT INTO batch VALUES (%u,%s,'%s','%s','%s','%s')", + ar->FileIndex, edit_int64(ar->JobId,ed1), mdb->esc_path, + mdb->esc_name, ar->attr, digest); + if (my_dbi_query(mdb,mdb->cmd) == 1) + { + Dmsg0(500, "my_dbi_batch_insert failed\n"); + goto bail_out; + } + + Dmsg0(500, "my_dbi_batch_insert finishing\n"); + return 1; break; } - + bail_out: Mmsg1(&mdb->errmsg, _("error inserting batch mode: %s"), my_dbi_strerror(mdb)); mdb->status = (dbi_error_flag) 0; - my_dbi_free_result(mdb); + my_dbi_free_result(mdb); return mdb->status; } @@ -1000,7 +1079,7 @@ char *my_postgresql_copy_escape(char *dest, char *src, size_t len) * int PQgetisnull(const PGresult *res, * int row_number, * int column_number); - * + * * use dbi_result_seek_row to search in result set */ int my_dbi_getisnull(dbi_result *result, int row_number, int column_number) { @@ -1009,21 +1088,21 @@ int my_dbi_getisnull(dbi_result *result, int row_number, int column_number) { if(row_number == 0) { row_number++; } - + column_number++; - + if(dbi_result_seek_row(result, row_number)) { i = dbi_result_field_is_null_idx(result,column_number); return i; } else { - + return 0; } - + } -/* my_dbi_getvalue +/* my_dbi_getvalue * like PQgetvalue; * char *PQgetvalue(const PGresult *res, * int row_number, @@ -1036,54 +1115,54 @@ char *my_dbi_getvalue(dbi_result *result, int row_number, unsigned int column_nu char *buf = NULL; const char *errmsg; - const char *field_name; + const char *field_name; unsigned short dbitype; size_t field_length; int64_t num; - + /* correct the index for dbi interface * dbi index begins 1 * I prefer do not change others functions */ - Dmsg3(600, "my_dbi_getvalue pre-starting result '%p' row number '%d' column number '%d'\n", + Dmsg3(600, "my_dbi_getvalue pre-starting result '%p' row number '%d' column number '%d'\n", result, row_number, column_number); - + column_number++; if(row_number == 0) { row_number++; } - - Dmsg3(600, "my_dbi_getvalue starting result '%p' row number '%d' column number '%d'\n", + + Dmsg3(600, "my_dbi_getvalue starting result '%p' row number '%d' column number '%d'\n", result, row_number, column_number); - + if(dbi_result_seek_row(result, row_number)) { field_name = dbi_result_get_field_name(result, column_number); field_length = dbi_result_get_field_length(result, field_name); dbitype = dbi_result_get_field_type_idx(result,column_number); - + Dmsg3(500, "my_dbi_getvalue start: type: '%d' " - "field_length bytes: '%d' fieldname: '%s'\n", + "field_length bytes: '%d' fieldname: '%s'\n", dbitype, field_length, field_name); - + if(field_length) { //buf = (char *)malloc(sizeof(char *) * field_length + 1); buf = (char *)malloc(field_length + 1); } else { /* if numbers */ buf = (char *)malloc(sizeof(char *) * 50); - } - + } + switch (dbitype) { case DBI_TYPE_INTEGER: - num = dbi_result_get_longlong(result, field_name); + num = dbi_result_get_longlong(result, field_name); edit_int64(num, buf); field_length = strlen(buf); break; case DBI_TYPE_STRING: if(field_length) { - field_length = bsnprintf(buf, field_length + 1, "%s", + field_length = bsnprintf(buf, field_length + 1, "%s", dbi_result_get_string(result, field_name)); } else { buf[0] = 0; @@ -1094,7 +1173,7 @@ char *my_dbi_getvalue(dbi_result *result, int row_number, unsigned int column_nu * following, change this to what Bacula espected */ if(field_length) { - field_length = bsnprintf(buf, field_length + 1, "%s", + field_length = bsnprintf(buf, field_length + 1, "%s", dbi_result_get_binary(result, field_name)); } else { buf[0] = 0; @@ -1103,11 +1182,11 @@ char *my_dbi_getvalue(dbi_result *result, int row_number, unsigned int column_nu case DBI_TYPE_DATETIME: time_t last; struct tm tm; - + last = dbi_result_get_datetime(result, field_name); - + if(last == -1) { - field_length = bsnprintf(buf, 20, "0000-00-00 00:00:00"); + field_length = bsnprintf(buf, 20, "0000-00-00 00:00:00"); } else { (void)localtime_r(&last, &tm); field_length = bsnprintf(buf, 20, "%04d-%02d-%02d %02d:%02d:%02d", @@ -1121,10 +1200,10 @@ char *my_dbi_getvalue(dbi_result *result, int row_number, unsigned int column_nu dbi_conn_error(dbi_result_get_conn(result), &errmsg); Dmsg1(500, "my_dbi_getvalue error: %s\n", errmsg); } - - Dmsg3(500, "my_dbi_getvalue finish buffer: '%p' num bytes: '%d' data: '%s'\n", + + Dmsg3(500, "my_dbi_getvalue finish buffer: '%p' num bytes: '%d' data: '%s'\n", buf, field_length, buf); - + // don't worry about this buf return buf; } @@ -1148,12 +1227,12 @@ int my_dbi_sql_insert_id(B_DB *mdb, char *table_name) everything else can use the PostgreSQL formula. */ - - char sequence[30]; + + char sequence[30]; uint64_t id = 0; if (mdb->db_type == SQL_TYPE_POSTGRESQL) { - + if (strcasecmp(table_name, "basefiles") == 0) { bstrncpy(sequence, "basefiles_baseid", sizeof(sequence)); } else { @@ -1168,39 +1247,45 @@ int my_dbi_sql_insert_id(B_DB *mdb, char *table_name) } else { id = dbi_conn_sequence_last(mdb->db, NT_(table_name)); } - + return id; } #ifdef HAVE_BATCH_FILE_INSERT -const char *my_dbi_batch_lock_path_query[3] = { +const char *my_dbi_batch_lock_path_query[4] = { /* Mysql */ "LOCK TABLES Path write, batch write, Path as p write", /* Postgresql */ "BEGIN; LOCK TABLE Path IN SHARE ROW EXCLUSIVE MODE", /* SQLite */ - "BEGIN"}; + "BEGIN", + /* SQLite3 */ + "BEGIN"}; -const char *my_dbi_batch_lock_filename_query[3] = { +const char *my_dbi_batch_lock_filename_query[4] = { /* Mysql */ "LOCK TABLES Filename write, batch write, Filename as f write", /* Postgresql */ "BEGIN; LOCK TABLE Filename IN SHARE ROW EXCLUSIVE MODE", /* SQLite */ + "BEGIN", + /* SQLite3 */ "BEGIN"}; -const char *my_dbi_batch_unlock_tables_query[3] = { +const char *my_dbi_batch_unlock_tables_query[4] = { /* Mysql */ "UNLOCK TABLES", /* Postgresql */ "COMMIT", /* SQLite */ + "COMMIT", + /* SQLite3 */ "COMMIT"}; -const char *my_dbi_batch_fill_path_query[3] = { +const char *my_dbi_batch_fill_path_query[4] = { /* Mysql */ "INSERT INTO Path (Path) " - "SELECT a.Path FROM " + "SELECT a.Path FROM " "(SELECT DISTINCT Path FROM batch) AS a WHERE NOT EXISTS " "(SELECT Path FROM Path AS p WHERE p.Path = a.Path)", /* Postgresql */ @@ -1209,14 +1294,18 @@ const char *my_dbi_batch_fill_path_query[3] = { "(SELECT DISTINCT Path FROM batch) AS a " "WHERE NOT EXISTS (SELECT Path FROM Path WHERE Path = a.Path) ", /* SQLite */ - "INSERT INTO Path (Path)" + "INSERT INTO Path (Path)" + " SELECT DISTINCT Path FROM batch" + " EXCEPT SELECT Path FROM Path", + /* SQLite3 */ + "INSERT INTO Path (Path)" " SELECT DISTINCT Path FROM batch" " EXCEPT SELECT Path FROM Path"}; -const char *my_dbi_batch_fill_filename_query[3] = { +const char *my_dbi_batch_fill_filename_query[4] = { /* Mysql */ "INSERT INTO Filename (Name) " - "SELECT a.Name FROM " + "SELECT a.Name FROM " "(SELECT DISTINCT Name FROM batch) AS a WHERE NOT EXISTS " "(SELECT Name FROM Filename AS f WHERE f.Name = a.Name)", /* Postgresql */ @@ -1228,7 +1317,12 @@ const char *my_dbi_batch_fill_filename_query[3] = { /* SQLite */ "INSERT INTO Filename (Name)" " SELECT DISTINCT Name FROM batch " + " EXCEPT SELECT Name FROM Filename", + /* SQLite3 */ + "INSERT INTO Filename (Name)" + " SELECT DISTINCT Name FROM batch " " EXCEPT SELECT Name FROM Filename"}; + #endif /* HAVE_BATCH_FILE_INSERT */ #endif /* HAVE_DBI */ diff --git a/bacula/src/cats/sql.c b/bacula/src/cats/sql.c index a0cc7899a6..f5e9baf4dc 100644 --- a/bacula/src/cats/sql.c +++ b/bacula/src/cats/sql.c @@ -56,10 +56,10 @@ int db_type = -1; void print_dashes(B_DB *mdb); void print_result(B_DB *mdb); -B_DB *db_init(JCR *jcr, const char *db_driver, const char *db_name, const char *db_user, - const char *db_password, const char *db_address, int db_port, +B_DB *db_init(JCR *jcr, const char *db_driver, const char *db_name, const char *db_user, + const char *db_password, const char *db_address, int db_port, const char *db_socket, int mult_db_connections) -{ +{ #ifdef HAVE_DBI char *p; if (!db_driver) { @@ -68,13 +68,15 @@ B_DB *db_init(JCR *jcr, const char *db_driver, const char *db_name, const char * if (strlen(db_driver) < 5 || db_driver[3] != ':' || strncasecmp(db_driver, "dbi", 3) != 0) { Jmsg0(jcr, M_ABORT, 0, _("Invalid driver type, must be \"dbi:\"\n")); } - p = (char *)(db_driver + 4); + p = (char *)(db_driver + 4); if (strcasecmp(p, "mysql") == 0) { db_type = SQL_TYPE_MYSQL; } else if (strcasecmp(p, "postgresql") == 0) { db_type = SQL_TYPE_POSTGRESQL; } else if (strcasecmp(p, "sqlite") == 0) { db_type = SQL_TYPE_SQLITE; + } else if (strcasecmp(p, "sqlite3") == 0) { + db_type = SQL_TYPE_SQLITE3; } else { Jmsg1(jcr, M_ABORT, 0, _("Unknown database type: %s\n"), p); } @@ -85,14 +87,14 @@ B_DB *db_init(JCR *jcr, const char *db_driver, const char *db_name, const char * #elif HAVE_SQLITE db_type = SQL_TYPE_SQLITE; #elif HAVE_SQLITE3 - db_type = SQL_TYPE_SQLITE; + db_type = SQL_TYPE_SQLITE3; #endif return db_init_database(jcr, db_name, db_user, db_password, db_address, db_port, db_socket, mult_db_connections); } -dbid_list::dbid_list() +dbid_list::dbid_list() { memset(this, 0, sizeof(dbid_list)); max_ids = 1000; @@ -101,8 +103,8 @@ dbid_list::dbid_list() PurgedFiles = NULL; } -dbid_list::~dbid_list() -{ +dbid_list::~dbid_list() +{ free(DBId); } @@ -390,6 +392,41 @@ void db_start_transaction(JCR *jcr, B_DB *mdb) } db_unlock(mdb); #endif + +#ifdef HAVE_DBI + if (db_type == SQL_TYPE_SQLITE) { + if (!mdb->allow_transactions) { + return; + } + db_lock(mdb); + /* Allow only 10,000 changes per transaction */ + if (mdb->transaction && mdb->changes > 10000) { + db_end_transaction(jcr, mdb); + } + if (!mdb->transaction) { + //my_sqlite_query(mdb, "BEGIN"); /* begin transaction */ + db_sql_query(mdb, "BEGIN", NULL, NULL); /* begin transaction */ + Dmsg0(400, "Start SQLite transaction\n"); + mdb->transaction = 1; + } + db_unlock(mdb); + } else if (db_type == SQL_TYPE_POSTGRESQL) { + if (!mdb->allow_transactions) { + return; + } + db_lock(mdb); + /* Allow only 25,000 changes per transaction */ + if (mdb->transaction && mdb->changes > 25000) { + db_end_transaction(jcr, mdb); + } + if (!mdb->transaction) { + db_sql_query(mdb, "BEGIN", NULL, NULL); /* begin transaction */ + Dmsg0(400, "Start PosgreSQL transaction\n"); + mdb->transaction = 1; + } + db_unlock(mdb); + } +#endif } void db_end_transaction(JCR *jcr, B_DB *mdb) @@ -437,6 +474,35 @@ void db_end_transaction(JCR *jcr, B_DB *mdb) mdb->changes = 0; db_unlock(mdb); #endif + +#ifdef HAVE_DBI + if (db_type == SQL_TYPE_SQLITE) { + if (!mdb->allow_transactions) { + return; + } + db_lock(mdb); + if (mdb->transaction) { + //my_sqlite_query(mdb, "COMMIT"); /* end transaction */ + db_sql_query(mdb, "COMMIT", NULL, NULL); /* end transaction */ + mdb->transaction = 0; + Dmsg1(400, "End SQLite transaction changes=%d\n", mdb->changes); + } + mdb->changes = 0; + db_unlock(mdb); + } else if (db_type == SQL_TYPE_POSTGRESQL) { + if (!mdb->allow_transactions) { + return; + } + db_lock(mdb); + if (mdb->transaction) { + db_sql_query(mdb, "COMMIT", NULL, NULL); /* end transaction */ + mdb->transaction = 0; + Dmsg1(400, "End PostgreSQL transaction changes=%d\n", mdb->changes); + } + mdb->changes = 0; + db_unlock(mdb); + } +#endif } /* diff --git a/bacula/src/cats/sql_cmds.c b/bacula/src/cats/sql_cmds.c index da02db7cd4..87c16b5044 100644 --- a/bacula/src/cats/sql_cmds.c +++ b/bacula/src/cats/sql_cmds.c @@ -421,7 +421,7 @@ const char *uar_jobid_fileindex_from_table = /* ====== ua_prune.c */ /* List of SQL commands to create temp table and indicies */ -const char *create_deltabs[3] = { +const char *create_deltabs[4] = { /* MySQL */ "CREATE TEMPORARY TABLE DelCandidates (" "JobId INTEGER UNSIGNED NOT NULL, " @@ -442,12 +442,19 @@ const char *create_deltabs[3] = { "PurgedFiles TINYINT, " "FileSetId INTEGER UNSIGNED, " "JobFiles INTEGER UNSIGNED, " + "JobStatus CHAR)", + /* SQLite3 */ + "CREATE TEMPORARY TABLE DelCandidates (" + "JobId INTEGER UNSIGNED NOT NULL, " + "PurgedFiles TINYINT, " + "FileSetId INTEGER UNSIGNED, " + "JobFiles INTEGER UNSIGNED, " "JobStatus CHAR)"}; /* ======= ua_restore.c */ /* List Jobs where a particular file is saved */ -const char *uar_file[3] = { +const char *uar_file[4] = { /* Mysql */ "SELECT Job.JobId as JobId," "CONCAT(Path.Path,Filename.Name) as Name, " @@ -474,9 +481,18 @@ const char *uar_file[3] = { "AND Client.ClientId=Job.ClientId " "AND Job.JobId=File.JobId " "AND Path.PathId=File.PathId AND Filename.FilenameId=File.FilenameId " + "AND Filename.Name='%s' ORDER BY StartTime DESC LIMIT 20", + /* SQLite3 */ + "SELECT Job.JobId as JobId," + "Path.Path||Filename.Name as Name, " + "StartTime,Type as JobType,JobStatus,JobFiles,JobBytes " + "FROM Client,Job,File,Filename,Path WHERE Client.Name='%s' " + "AND Client.ClientId=Job.ClientId " + "AND Job.JobId=File.JobId " + "AND Path.PathId=File.PathId AND Filename.FilenameId=File.FilenameId " "AND Filename.Name='%s' ORDER BY StartTime DESC LIMIT 20"}; -const char *uar_create_temp[3] = { +const char *uar_create_temp[4] = { /* Mysql */ "CREATE TEMPORARY TABLE temp (" "JobId INTEGER UNSIGNED NOT NULL," @@ -515,9 +531,22 @@ const char *uar_create_temp[3] = { "VolumeName TEXT," "StartFile INTEGER UNSIGNED," "VolSessionId INTEGER UNSIGNED," + "VolSessionTime INTEGER UNSIGNED)", + /* SQLite3 */ + "CREATE TEMPORARY TABLE temp (" + "JobId INTEGER UNSIGNED NOT NULL," + "JobTDate BIGINT UNSIGNED," + "ClientId INTEGER UNSIGNED," + "Level CHAR," + "JobFiles INTEGER UNSIGNED," + "JobBytes BIGINT UNSIGNED," + "StartTime TEXT," + "VolumeName TEXT," + "StartFile INTEGER UNSIGNED," + "VolSessionId INTEGER UNSIGNED," "VolSessionTime INTEGER UNSIGNED)"}; -const char *uar_create_temp1[3] = { +const char *uar_create_temp1[4] = { /* Mysql */ "CREATE TEMPORARY TABLE temp1 (" "JobId INTEGER UNSIGNED NOT NULL," @@ -529,6 +558,10 @@ const char *uar_create_temp1[3] = { /* SQLite */ "CREATE TEMPORARY TABLE temp1 (" "JobId INTEGER UNSIGNED NOT NULL," + "JobTDate BIGINT UNSIGNED)", + /* SQLite3 */ + "CREATE TEMPORARY TABLE temp1 (" + "JobId INTEGER UNSIGNED NOT NULL," "JobTDate BIGINT UNSIGNED)"}; /* Query to get all files in a directory -- no recursing @@ -538,7 +571,7 @@ const char *uar_create_temp1[3] = { * for each time it was backed up. */ -const char *uar_jobid_fileindex_from_dir[3] = { +const char *uar_jobid_fileindex_from_dir[4] = { /* Mysql */ "SELECT Job.JobId,File.FileIndex FROM Job,File,Path,Filename,Client " "WHERE Job.JobId IN (%s) " @@ -567,4 +600,14 @@ const char *uar_jobid_fileindex_from_dir[3] = { "AND Job.ClientId=Client.ClientId " "AND Path.PathId=File.Pathid " "AND Filename.FilenameId=File.FilenameId " + "GROUP BY File.FileIndex ", + /* SQLite3 */ + "SELECT Job.JobId,File.FileIndex FROM Job,File,Path,Filename,Client " + "WHERE Job.JobId IN (%s) " + "AND Job.JobId=File.JobId " + "AND Path.Path='%s' " + "AND Client.Name='%s' " + "AND Job.ClientId=Client.ClientId " + "AND Path.PathId=File.Pathid " + "AND Filename.FilenameId=File.FilenameId " "GROUP BY File.FileIndex "}; diff --git a/bacula/src/cats/sql_cmds.h b/bacula/src/cats/sql_cmds.h index 930c4b1b59..9dbc16e40b 100644 --- a/bacula/src/cats/sql_cmds.h +++ b/bacula/src/cats/sql_cmds.h @@ -70,9 +70,9 @@ extern const char CATS_IMP_EXP *uar_jobids_fileindex; extern const char CATS_IMP_EXP *uar_jobid_fileindex_from_table; extern const char CATS_IMP_EXP *uar_sel_jobid_temp; -extern const char CATS_IMP_EXP *create_deltabs[3]; +extern const char CATS_IMP_EXP *create_deltabs[4]; -extern const char CATS_IMP_EXP *uar_file[3]; -extern const char CATS_IMP_EXP *uar_create_temp[3]; -extern const char CATS_IMP_EXP *uar_create_temp1[3]; -extern const char CATS_IMP_EXP *uar_jobid_fileindex_from_dir[3]; +extern const char CATS_IMP_EXP *uar_file[4]; +extern const char CATS_IMP_EXP *uar_create_temp[4]; +extern const char CATS_IMP_EXP *uar_create_temp1[4]; +extern const char CATS_IMP_EXP *uar_jobid_fileindex_from_dir[4]; diff --git a/bacula/src/cats/sql_list.c b/bacula/src/cats/sql_list.c index 0ac4143816..33765bf874 100644 --- a/bacula/src/cats/sql_list.c +++ b/bacula/src/cats/sql_list.c @@ -78,7 +78,7 @@ int db_list_sql_query(JCR *jcr, B_DB *mdb, const char *query, DB_LIST_HANDLER *s } void -db_list_pool_records(JCR *jcr, B_DB *mdb, POOL_DBR *pdbr, +db_list_pool_records(JCR *jcr, B_DB *mdb, POOL_DBR *pdbr, DB_LIST_HANDLER *sendit, void *ctx, e_list_type type) { db_lock(mdb); @@ -172,7 +172,7 @@ db_list_media_records(JCR *jcr, B_DB *mdb, MEDIA_DBR *mdbr, "EndFile,EndBlock,VolParts,LabelType,StorageId,DeviceId," "LocationId,RecycleCount,InitialWrite,ScratchPoolId,RecyclePoolId, " "Comment" - " FROM Media WHERE Media.PoolId=%s ORDER BY MediaId", + " FROM Media WHERE Media.PoolId=%s ORDER BY MediaId", edit_int64(mdbr->PoolId, ed1)); } } else { @@ -183,7 +183,7 @@ db_list_media_records(JCR *jcr, B_DB *mdb, MEDIA_DBR *mdbr, } else { Mmsg(mdb->cmd, "SELECT MediaId,VolumeName,VolStatus,Enabled," "VolBytes,VolFiles,VolRetention,Recycle,Slot,InChanger,MediaType,LastWritten " - "FROM Media WHERE Media.PoolId=%s ORDER BY MediaId", + "FROM Media WHERE Media.PoolId=%s ORDER BY MediaId", edit_int64(mdbr->PoolId, ed1)); } } @@ -281,7 +281,7 @@ db_list_job_records(JCR *jcr, B_DB *mdb, JOB_DBR *jr, DB_LIST_HANDLER *sendit, "Job.FileSetId,FileSet.FileSet " "FROM Job,Client,Pool,FileSet WHERE Job.JobId=%s AND " "Client.ClientId=Job.ClientId AND Pool.PoolId=Job.PoolId " - "AND FileSet.FileSetId=Job.FileSetId", + "AND FileSet.FileSetId=Job.FileSetId", edit_int64(jr->JobId, ed1)); } } else { @@ -294,7 +294,7 @@ db_list_job_records(JCR *jcr, B_DB *mdb, JOB_DBR *jr, DB_LIST_HANDLER *sendit, "SELECT JobId,Name,StartTime,Type,Level,JobFiles,JobBytes,JobStatus " "FROM Job WHERE Job='%s' ORDER BY StartTime,JobId ASC", jr->Job); } else if (jr->JobId != 0) { - Mmsg(mdb->cmd, + Mmsg(mdb->cmd, "SELECT JobId,Name,StartTime,Type,Level,JobFiles,JobBytes,JobStatus " "FROM Job WHERE JobId=%s", edit_int64(jr->JobId, ed1)); } else { /* all records */ @@ -350,25 +350,26 @@ db_list_job_totals(JCR *jcr, B_DB *mdb, JOB_DBR *jr, DB_LIST_HANDLER *sendit, vo db_unlock(mdb); } -/* - * Stupid MySQL is NON-STANDARD ! - */ -#ifdef HAVE_MYSQL -#define FN "CONCAT(Path.Path,Filename.Name)" -#else -#define FN "Path.Path||Filename.Name" -#endif - void db_list_files_for_job(JCR *jcr, B_DB *mdb, JobId_t jobid, DB_LIST_HANDLER *sendit, void *ctx) { char ed1[50]; db_lock(mdb); - Mmsg(mdb->cmd, "SELECT " FN " AS Filename FROM File," -"Filename,Path WHERE File.JobId=%s AND Filename.FilenameId=File.FilenameId " -"AND Path.PathId=File.PathId", - edit_int64(jobid, ed1)); + /* + * Stupid MySQL is NON-STANDARD ! + */ + if (db_type == SQL_TYPE_MYSQL) { + Mmsg(mdb->cmd, "SELECT CONCAT(Path.Path,Filename.Name) AS Filename FROM File," + "Filename,Path WHERE File.JobId=%s AND Filename.FilenameId=File.FilenameId " + "AND Path.PathId=File.PathId", + edit_int64(jobid, ed1)); + } else { + Mmsg(mdb->cmd, "SELECT Path.Path||Filename.Name AS Filename FROM File," + "Filename,Path WHERE File.JobId=%s AND Filename.FilenameId=File.FilenameId " + "AND Path.PathId=File.PathId", + edit_int64(jobid, ed1)); + } if (!QUERY_DB(jcr, mdb, mdb->cmd)) { db_unlock(mdb); diff --git a/bacula/technotes-2.5 b/bacula/technotes-2.5 index 6185eab979..ebc925f1da 100644 --- a/bacula/technotes-2.5 +++ b/bacula/technotes-2.5 @@ -18,6 +18,7 @@ remove reader/writer in FOPTS???? General: 29Sep08 +kes Apply dbi driver patch from Joao. kes Correct a bug in passing the context to the endRestoreFile() plugin command. Bastien Friedrich reported the bug. kes Create plugin instance only when FD job starts. -- 2.39.5