Modify MySQL accurate query with Delta

[bacula/bacula] / bacula / src / cats / sql.c

diff --git a/bacula/src/cats/sql.c b/bacula/src/cats/sql.c
index 6b8d3faf87a537a73769e89f3769f3009ed16742..1f79a814b806461cf32f1bb76b16ba62cd983bd9 100644 (file)
--- a/bacula/src/cats/sql.c
+++ b/bacula/src/cats/sql.c
@@ -6,7 +6,7 @@
    The main author of Bacula is Kern Sibbald, with contributions from
    many others, a complete list can be found in the file AUTHORS.
    This program is Free Software; you can redistribute it and/or
    The main author of Bacula is Kern Sibbald, with contributions from
    many others, a complete list can be found in the file AUTHORS.
    This program is Free Software; you can redistribute it and/or

-   modify it under the terms of version two of the GNU General Public
+   modify it under the terms of version three of the GNU Affero General Public

    License as published by the Free Software Foundation and included
    in the file LICENSE.
 
    License as published by the Free Software Foundation and included
    in the file LICENSE.
 


@@ -15,7 +15,7 @@
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
    General Public License for more details.
 
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
    General Public License for more details.
 

-   You should have received a copy of the GNU General Public License
+   You should have received a copy of the GNU Affero General Public License

    along with this program; if not, write to the Free Software
    Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA
    02110-1301, USA.
    along with this program; if not, write to the Free Software
    Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA
    02110-1301, USA.


@@ -35,7 +35,7 @@
  *
  *    Kern Sibbald, March 2000
  *
  *
  *    Kern Sibbald, March 2000
  *

- *    Version $Id$
+ *    Version $Id: sql.c 8034 2008-11-11 14:33:46Z ricozz $

  */
 
 /* The following is necessary so that we do not include
  */
 
 /* The following is necessary so that we do not include


@@ -46,7 +46,7 @@
 #include "bacula.h"
 #include "cats.h"
 
 #include "bacula.h"
 #include "cats.h"
 

-#if    HAVE_SQLITE3 || HAVE_MYSQL || HAVE_SQLITE || HAVE_POSTGRESQL || HAVE_DBI
+#if    HAVE_SQLITE3 || HAVE_MYSQL || HAVE_SQLITE || HAVE_POSTGRESQL || HAVE_INGRES || HAVE_DBI

 
 uint32_t bacula_db_version = 0;
 
 
 uint32_t bacula_db_version = 0;
 


@@ -77,6 +77,8 @@ B_DB *db_init(JCR *jcr, const char *db_driver, const char *db_name, const char *
       db_type = SQL_TYPE_SQLITE;
    } else if (strcasecmp(p, "sqlite3") == 0) {
       db_type = SQL_TYPE_SQLITE3;
       db_type = SQL_TYPE_SQLITE;
    } else if (strcasecmp(p, "sqlite3") == 0) {
       db_type = SQL_TYPE_SQLITE3;

+   } else if (strcasecmp(p, "ingres") == 0) {
+      db_type = SQL_TYPE_INGRES;

    } else {
       Jmsg1(jcr, M_ABORT, 0, _("Unknown database type: %s\n"), p);
    }
    } else {
       Jmsg1(jcr, M_ABORT, 0, _("Unknown database type: %s\n"), p);
    }


@@ -84,6 +86,8 @@ B_DB *db_init(JCR *jcr, const char *db_driver, const char *db_name, const char *
    db_type = SQL_TYPE_MYSQL;
 #elif HAVE_POSTGRESQL
    db_type = SQL_TYPE_POSTGRESQL;
    db_type = SQL_TYPE_MYSQL;
 #elif HAVE_POSTGRESQL
    db_type = SQL_TYPE_POSTGRESQL;

+#elif HAVE_INGRES
+   db_type = SQL_TYPE_INGRES;

 #elif HAVE_SQLITE
    db_type = SQL_TYPE_SQLITE;
 #elif HAVE_SQLITE3
 #elif HAVE_SQLITE
    db_type = SQL_TYPE_SQLITE;
 #elif HAVE_SQLITE3


@@ -108,7 +112,6 @@ dbid_list::~dbid_list()
    free(DBId);
 }
 
    free(DBId);
 }
 

-

 /*
  * Called here to retrieve an integer from the database
  */
 /*
  * Called here to retrieve an integer from the database
  */


@@ -144,7 +147,68 @@ int db_int64_handler(void *ctx, int num_fields, char **row)
    return 0;
 }
 
    return 0;
 }
 

+/*
+ * Use to build a comma separated list of values from a query. "10,20,30"
+ */
+int db_list_handler(void *ctx, int num_fields, char **row)
+{
+   db_list_ctx *lctx = (db_list_ctx *)ctx;
+   if (num_fields == 1 && row[0]) {
+      if (lctx->list[0]) {
+         pm_strcat(lctx->list, ",");
+      }
+      pm_strcat(lctx->list, row[0]);
+      lctx->count++;
+   }
+   return 0;
+}
+
+
+/*
+ * Called here to retrieve an integer from the database
+ */
+static int db_max_connections_handler(void *ctx, int num_fields, char **row)
+{
+   uint32_t *val = (uint32_t *)ctx;
+   uint32_t index = sql_get_max_connections_index[db_type];
+   if (row[index]) {
+      *val = str_to_int64(row[index]);
+   } else {
+      Dmsg0(800, "int_handler finds zero\n");
+      *val = 0;
+   }
+   return 0;
+}
+
+/* 
+ * Check catalog max_connections setting
+ */
+bool db_check_max_connections(JCR *jcr, B_DB *mdb, uint32_t max_concurrent_jobs)
+{
+#ifdef HAVE_BATCH_FILE_INSERT
+
+   uint32_t max_conn = 0;

 
 

+   /* With Batch insert, verify max_connections */
+   if (!db_sql_query(mdb, sql_get_max_connections[db_type], 
+                     db_max_connections_handler, &max_conn)) {
+      Jmsg(jcr, M_ERROR, 0, "Can't verify max_connections settings %s", mdb->errmsg);
+      return false;
+   }
+   if (max_conn && max_concurrent_jobs > max_conn) {
+      Mmsg(mdb->errmsg, 
+           _("Potential performance problem:\n"
+             "max_connections=%d set for %s database \"%s\" should be larger than Director's "
+             "MaxConcurrentJobs=%d\n"),
+           max_conn, db_get_type(), mdb->db_name, max_concurrent_jobs);
+      Jmsg(jcr, M_WARNING, 0, "%s", mdb->errmsg);
+      return false;
+   }
+
+#endif
+
+   return true;
+}

 
 /* NOTE!!! The following routines expect that the
  *  calling subroutine sets and clears the mutex
 
 /* NOTE!!! The following routines expect that the
  *  calling subroutine sets and clears the mutex


@@ -206,11 +270,7 @@ InsertDB(const char *file, int line, JCR *jcr, B_DB *mdb, char *cmd)
       }
       return 0;
    }
       }
       return 0;
    }

-   if (mdb->have_insert_id) {
-      mdb->num_rows = sql_affected_rows(mdb);
-   } else {
-      mdb->num_rows = 1;
-   }
+   mdb->num_rows = sql_affected_rows(mdb);

    if (mdb->num_rows != 1) {
       char ed1[30];
       m_msg(file, line, &mdb->errmsg, _("Insertion problem: affected_rows=%s\n"),
    if (mdb->num_rows != 1) {
       char ed1[30];
       m_msg(file, line, &mdb->errmsg, _("Insertion problem: affected_rows=%s\n"),


@@ -319,7 +379,7 @@ char *db_strerror(B_DB *mdb)
 void _db_lock(const char *file, int line, B_DB *mdb)
 {
    int errstat;
 void _db_lock(const char *file, int line, B_DB *mdb)
 {
    int errstat;

-   if ((errstat=rwl_writelock(&mdb->lock)) != 0) {
+   if ((errstat=rwl_writelock_p(&mdb->lock, file, line)) != 0) {

       berrno be;
       e_msg(file, line, M_FATAL, 0, "rwl_writelock failure. stat=%d: ERR=%s\n",
            errstat, be.bstrerror(errstat));
       berrno be;
       e_msg(file, line, M_FATAL, 0, "rwl_writelock failure. stat=%d: ERR=%s\n",
            errstat, be.bstrerror(errstat));


@@ -393,6 +453,23 @@ void db_start_transaction(JCR *jcr, B_DB *mdb)
    db_unlock(mdb);
 #endif
 
    db_unlock(mdb);
 #endif
 

+#ifdef HAVE_INGRES
+   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 Ingres transaction\n");
+      mdb->transaction = 1;
+   }
+   db_unlock(mdb);
+#endif
+

 #ifdef HAVE_DBI
    if (db_type == SQL_TYPE_SQLITE) {
       if (!mdb->allow_transactions) {
 #ifdef HAVE_DBI
    if (db_type == SQL_TYPE_SQLITE) {
       if (!mdb->allow_transactions) {


@@ -461,6 +538,23 @@ void db_end_transaction(JCR *jcr, B_DB *mdb)
    db_unlock(mdb);
 #endif
 
    db_unlock(mdb);
 #endif
 

+
+
+#ifdef HAVE_INGRES
+   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 Ingres transaction changes=%d\n", mdb->changes);
+   }
+   mdb->changes = 0;
+   db_unlock(mdb);
+#endif
+
+

 #ifdef HAVE_POSTGRESQL
    if (!mdb->allow_transactions) {
       return;
 #ifdef HAVE_POSTGRESQL
    if (!mdb->allow_transactions) {
       return;


@@ -726,10 +820,10 @@ vertical_list:
  */
 bool db_open_batch_connexion(JCR *jcr, B_DB *mdb)
 {
  */
 bool db_open_batch_connexion(JCR *jcr, B_DB *mdb)
 {

-   int multi_db=false;
-

 #ifdef HAVE_BATCH_FILE_INSERT
 #ifdef HAVE_BATCH_FILE_INSERT

-   multi_db=true;               /* we force a new connexion only if batch insert is enabled */
+   const int multi_db = true;   /* we force a new connection only if batch insert is enabled */
+#else
+   const int multi_db = false;

 #endif
 
    if (!jcr->db_batch) {
 #endif
 
    if (!jcr->db_batch) {


@@ -742,14 +836,15 @@ bool db_open_batch_connexion(JCR *jcr, B_DB *mdb)
                                       mdb->db_socket,
                                       multi_db /* multi_db = true when using batch mode */);
       if (!jcr->db_batch) {
                                       mdb->db_socket,
                                       multi_db /* multi_db = true when using batch mode */);
       if (!jcr->db_batch) {

-         Jmsg0(jcr, M_FATAL, 0, "Could not init batch connexion");
+         Mmsg0(&mdb->errmsg, _("Could not init database batch connection"));
+         Jmsg(jcr, M_FATAL, 0, "%s", mdb->errmsg);

          return false;
       }
 
       if (!db_open_database(jcr, jcr->db_batch)) {
          return false;
       }
 
       if (!db_open_database(jcr, jcr->db_batch)) {

-         Mmsg2(&jcr->db_batch->errmsg,  _("Could not open database \"%s\": ERR=%s\n"),
+         Mmsg2(&mdb->errmsg,  _("Could not open database \"%s\": ERR=%s\n"),

               jcr->db_batch->db_name, db_strerror(jcr->db_batch));
               jcr->db_batch->db_name, db_strerror(jcr->db_batch));

-         Jmsg1(jcr, M_FATAL, 0, "%s", jcr->db_batch->errmsg);
+         Jmsg(jcr, M_FATAL, 0, "%s", mdb->errmsg);

          return false;
       }      
       Dmsg3(100, "initdb ref=%d connected=%d db=%p\n", jcr->db_batch->ref_count,
          return false;
       }      
       Dmsg3(100, "initdb ref=%d connected=%d db=%p\n", jcr->db_batch->ref_count,


@@ -764,7 +859,7 @@ bool db_open_batch_connexion(JCR *jcr, B_DB *mdb)
  * ie, after a fatal signal and before exiting the program
  * Print information about a B_DB object.
  */
  * ie, after a fatal signal and before exiting the program
  * Print information about a B_DB object.
  */

-void _dbg_print_db(JCR *jcr, FILE *fp)
+void db_debug_print(JCR *jcr, FILE *fp)

 {
    B_DB *mdb = jcr->db;
 
 {
    B_DB *mdb = jcr->db;
 


@@ -780,4 +875,4 @@ void _dbg_print_db(JCR *jcr, FILE *fp)
    }
 }
 
    }
 }
 

-#endif /* HAVE_SQLITE3 || HAVE_MYSQL || HAVE_SQLITE || HAVE_POSTGRESQL*/
+#endif /* HAVE_SQLITE3 || HAVE_MYSQL || HAVE_SQLITE || HAVE_POSTGRESQL || HAVE_INGRES*/