2 Bacula® - The Network Backup Solution
4 Copyright (C) 2003-2008 Free Software Foundation Europe e.V.
6 The main author of Bacula is Kern Sibbald, with contributions from
7 many others, a complete list can be found in the file AUTHORS.
8 This program is Free Software; you can redistribute it and/or
9 modify it under the terms of version two of the GNU General Public
10 License as published by the Free Software Foundation and included
13 This program is distributed in the hope that it will be useful, but
14 WITHOUT ANY WARRANTY; without even the implied warranty of
15 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
16 General Public License for more details.
18 You should have received a copy of the GNU General Public License
19 along with this program; if not, write to the Free Software
20 Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA
23 Bacula® is a registered trademark of Kern Sibbald.
24 The licensor of Bacula is the Free Software Foundation Europe
25 (FSFE), Fiduciary Program, Sumatrastrasse 25, 8006 Zürich,
26 Switzerland, email:ftf@fsfeurope.org.
29 * Bacula Catalog Database routines specific to DBI
30 * These are DBI specific routines
32 * João Henrique Freitas, December 2007
33 * based upon work done by Dan Langille, December 2003 and
34 * by Kern Sibbald, March 2000
39 * This code only compiles against a recent version of libdbi. The current
40 * release found on the libdbi website (0.8.3) won't work for this code.
42 * You find the libdbi library on http://sourceforge.net/projects/libdbi
44 * A fairly recent version of libdbi from CVS works, so either make sure
45 * your distribution has a fairly recent version of libdbi installed or
46 * clone the CVS repositories from sourceforge and compile that code and
50 * cvs co :pserver:anonymous@libdbi.cvs.sourceforge.net:/cvsroot/libdbi
51 * cvs co :pserver:anonymous@libdbi-drivers.cvs.sourceforge.net:/cvsroot/libdbi-drivers
55 /* The following is necessary so that we do not include
56 * the dummy external definition of DB.
58 #define __SQL_C /* indicate that this is sql.c */
65 /* -----------------------------------------------------------------------
67 * DBI dependent defines and subroutines
69 * -----------------------------------------------------------------------
72 /* List of open databases */
73 static BQUEUE db_list = {&db_list, &db_list};
75 /* Control allocated fields by my_dbi_getvalue */
76 static BQUEUE dbi_getvalue_list = {&dbi_getvalue_list, &dbi_getvalue_list};
78 static pthread_mutex_t mutex = PTHREAD_MUTEX_INITIALIZER;
81 * Retrieve database type
90 * Initialize database data structure. In principal this should
91 * never have errors, or it is really fatal.
94 db_init_database(JCR *jcr, const char *db_name, const char *db_user, const char *db_password,
95 const char *db_address, int db_port, const char *db_socket,
96 int mult_db_connections)
100 char db_driverdir[256];
102 /* Constraint the db_driver */
104 Jmsg(jcr, M_FATAL, 0, _("A dbi driver for DBI must be supplied.\n"));
108 /* Do the correct selection of driver.
109 * Can be one of the varius supported by libdbi
113 bstrncpy(db_driver,"mysql", sizeof(db_driver));
115 case SQL_TYPE_POSTGRESQL:
116 bstrncpy(db_driver,"pgsql", sizeof(db_driver));
118 case SQL_TYPE_SQLITE:
119 bstrncpy(db_driver,"sqlite", sizeof(db_driver));
121 case SQL_TYPE_SQLITE3:
122 bstrncpy(db_driver,"sqlite3", sizeof(db_driver));
126 /* Set db_driverdir whereis is the libdbi drivers */
127 bstrncpy(db_driverdir, DBI_DRIVER_DIR, 255);
130 Jmsg(jcr, M_FATAL, 0, _("A user name for DBI must be supplied.\n"));
133 P(mutex); /* lock DB queue */
134 if (!mult_db_connections) {
135 /* Look to see if DB already open */
136 for (mdb=NULL; (mdb=(B_DB *)qnext(&db_list, &mdb->bq)); ) {
137 if (bstrcmp(mdb->db_name, db_name) &&
138 bstrcmp(mdb->db_address, db_address) &&
139 bstrcmp(mdb->db_driver, db_driver) &&
140 mdb->db_port == db_port) {
141 Dmsg4(100, "DB REopen %d %s %s erro: %d\n", mdb->ref_count, db_driver, db_name,
142 dbi_conn_error(mdb->db, NULL));
145 return mdb; /* already open */
149 Dmsg0(100, "db_open first time\n");
150 mdb = (B_DB *)malloc(sizeof(B_DB));
151 memset(mdb, 0, sizeof(B_DB));
152 mdb->db_name = bstrdup(db_name);
153 mdb->db_user = bstrdup(db_user);
155 mdb->db_password = bstrdup(db_password);
158 mdb->db_address = bstrdup(db_address);
161 mdb->db_socket = bstrdup(db_socket);
164 mdb->db_driverdir = bstrdup(db_driverdir);
167 mdb->db_driver = bstrdup(db_driver);
169 mdb->db_type = db_type;
170 mdb->db_port = db_port;
171 mdb->have_insert_id = TRUE;
172 mdb->errmsg = get_pool_memory(PM_EMSG); /* get error message buffer */
174 mdb->cmd = get_pool_memory(PM_EMSG); /* get command buffer */
175 mdb->cached_path = get_pool_memory(PM_FNAME);
176 mdb->cached_path_id = 0;
178 mdb->fname = get_pool_memory(PM_FNAME);
179 mdb->path = get_pool_memory(PM_FNAME);
180 mdb->esc_name = get_pool_memory(PM_FNAME);
181 mdb->esc_path = get_pool_memory(PM_FNAME);
182 mdb->allow_transactions = mult_db_connections;
183 qinsert(&db_list, &mdb->bq); /* put db in list */
189 * Now actually open the database. This can generate errors,
190 * which are returned in the errmsg
192 * DO NOT close the database or free(mdb) here !!!!
195 db_open_database(JCR *jcr, B_DB *mdb)
203 char *db_name = NULL;
207 if (mdb->connected) {
211 mdb->connected = false;
213 if ((errstat=rwl_init(&mdb->lock)) != 0) {
215 Mmsg1(&mdb->errmsg, _("Unable to initialize DB lock. ERR=%s\n"),
216 be.bstrerror(errstat));
222 bsnprintf(buf, sizeof(buf), "%d", mdb->db_port);
228 numdrivers = dbi_initialize_r(mdb->db_driverdir, &(mdb->instance));
229 if (numdrivers < 0) {
230 Mmsg2(&mdb->errmsg, _("Unable to locate the DBD drivers to DBI interface in: \n"
231 "db_driverdir=%s. It is probaly not found any drivers\n"),
232 mdb->db_driverdir,numdrivers);
236 mdb->db = (void **)dbi_conn_new_r(mdb->db_driver, mdb->instance);
237 /* Can be many types of databases */
238 switch (mdb->db_type) {
240 dbi_conn_set_option(mdb->db, "host", mdb->db_address); /* default = localhost */
241 dbi_conn_set_option(mdb->db, "port", port); /* default port */
242 dbi_conn_set_option(mdb->db, "username", mdb->db_user); /* login name */
243 dbi_conn_set_option(mdb->db, "password", mdb->db_password); /* password */
244 dbi_conn_set_option(mdb->db, "dbname", mdb->db_name); /* database name */
246 case SQL_TYPE_POSTGRESQL:
247 dbi_conn_set_option(mdb->db, "host", mdb->db_address);
248 dbi_conn_set_option(mdb->db, "port", port);
249 dbi_conn_set_option(mdb->db, "username", mdb->db_user);
250 dbi_conn_set_option(mdb->db, "password", mdb->db_password);
251 dbi_conn_set_option(mdb->db, "dbname", mdb->db_name);
253 case SQL_TYPE_SQLITE:
254 len = strlen(working_directory) + 5;
255 db_dir = (char *)malloc(len);
256 strcpy(db_dir, working_directory);
258 len = strlen(mdb->db_name) + 5;
259 db_name = (char *)malloc(len);
260 strcpy(db_name, mdb->db_name);
261 strcat(db_name, ".db");
262 dbi_conn_set_option(mdb->db, "sqlite_dbdir", db_dir);
263 dbi_conn_set_option(mdb->db, "dbname", db_name);
265 case SQL_TYPE_SQLITE3:
266 len = strlen(working_directory) + 5;
267 db_dir = (char *)malloc(len);
268 strcpy(db_dir, working_directory);
270 len = strlen(mdb->db_name) + 5;
271 db_name = (char *)malloc(len);
272 strcpy(db_name, mdb->db_name);
273 strcat(db_name, ".db");
274 dbi_conn_set_option(mdb->db, "sqlite3_dbdir", db_dir);
275 dbi_conn_set_option(mdb->db, "dbname", db_name);
276 Dmsg2(500, "SQLITE: %s %s\n", db_dir, db_name);
280 /* If connection fails, try at 5 sec intervals for 30 seconds. */
281 for (int retry=0; retry < 6; retry++) {
283 dbstat = dbi_conn_connect(mdb->db);
288 dbi_conn_error(mdb->db, &errmsg);
289 Dmsg1(50, "dbi error: %s\n", errmsg);
296 Mmsg3(&mdb->errmsg, _("Unable to connect to DBI interface. Type=%s Database=%s User=%s\n"
297 "Possible causes: SQL server not running; password incorrect; max_connections exceeded.\n"),
298 mdb->db_driver, mdb->db_name, mdb->db_user);
303 Dmsg0(50, "dbi_real_connect done\n");
304 Dmsg3(50, "db_user=%s db_name=%s db_password=%s\n",
305 mdb->db_user, mdb->db_name,
306 mdb->db_password==NULL?"(NULL)":mdb->db_password);
308 mdb->connected = true;
310 if (!check_tables_version(jcr, mdb)) {
315 switch (mdb->db_type) {
317 /* Set connection timeout to 8 days specialy for batch mode */
318 sql_query(mdb, "SET wait_timeout=691200");
319 sql_query(mdb, "SET interactive_timeout=691200");
321 case SQL_TYPE_POSTGRESQL:
322 /* tell PostgreSQL we are using standard conforming strings
323 and avoid warnings such as:
324 WARNING: nonstandard use of \\ in a string literal
326 sql_query(mdb, "SET datestyle TO 'ISO, YMD'");
327 sql_query(mdb, "set standard_conforming_strings=on");
343 db_close_database(JCR *jcr, B_DB *mdb)
348 db_end_transaction(jcr, mdb);
350 sql_free_result(mdb);
352 if (mdb->ref_count == 0) {
354 if (mdb->connected && mdb->db) {
356 dbi_shutdown_r(mdb->instance);
358 mdb->instance = NULL;
360 rwl_destroy(&mdb->lock);
361 free_pool_memory(mdb->errmsg);
362 free_pool_memory(mdb->cmd);
363 free_pool_memory(mdb->cached_path);
364 free_pool_memory(mdb->fname);
365 free_pool_memory(mdb->path);
366 free_pool_memory(mdb->esc_name);
367 free_pool_memory(mdb->esc_path);
374 if (mdb->db_password) {
375 free(mdb->db_password);
377 if (mdb->db_address) {
378 free(mdb->db_address);
380 if (mdb->db_socket) {
381 free(mdb->db_socket);
383 if (mdb->db_driverdir) {
384 free(mdb->db_driverdir);
386 if (mdb->db_driver) {
387 free(mdb->db_driver);
394 void db_thread_cleanup()
398 * Return the next unique index (auto-increment) for
399 * the given table. Return NULL on error.
402 int db_next_index(JCR *jcr, B_DB *mdb, char *table, char *index)
404 strcpy(index, "NULL");
410 * Escape strings so that DBI is happy
412 * NOTE! len is the length of the old string. Your new
413 * string must be long enough (max 2*old+1) to hold
414 * the escaped output.
416 * dbi_conn_quote_string_copy receives a pointer to pointer.
417 * We need copy the value of pointer to snew because libdbi change the
421 db_escape_string(JCR *jcr, B_DB *mdb, char *snew, char *old, int len)
429 /* correct the size of old basead in len
430 * and copy new string to inew
432 inew = (char *)malloc(sizeof(char) * len + 1);
433 bstrncpy(inew,old,len + 1);
434 /* escape the correct size of old */
435 dbi_conn_escape_string_copy(mdb->db, inew, &pnew);
437 /* copy the escaped string to snew */
438 bstrncpy(snew, pnew, 2 * len + 1);
441 Dmsg2(500, "dbi_conn_escape_string_copy %p %s\n",snew,snew);
446 * Submit a general SQL command (cmd), and for each row returned,
447 * the sqlite_handler is called with the ctx.
449 bool db_sql_query(B_DB *mdb, const char *query, DB_RESULT_HANDLER *result_handler, void *ctx)
453 Dmsg0(500, "db_sql_query started\n");
456 if (sql_query(mdb, query) != 0) {
457 Mmsg(mdb->errmsg, _("Query failed: %s: ERR=%s\n"), query, sql_strerror(mdb));
459 Dmsg0(500, "db_sql_query failed\n");
462 Dmsg0(500, "db_sql_query succeeded. checking handler\n");
464 if (result_handler != NULL) {
465 Dmsg0(500, "db_sql_query invoking handler\n");
466 if ((mdb->result = sql_store_result(mdb)) != NULL) {
467 int num_fields = sql_num_fields(mdb);
469 Dmsg0(500, "db_sql_query sql_store_result suceeded\n");
470 while ((row = sql_fetch_row(mdb)) != NULL) {
472 Dmsg0(500, "db_sql_query sql_fetch_row worked\n");
473 if (result_handler(ctx, num_fields, row))
477 sql_free_result(mdb);
482 Dmsg0(500, "db_sql_query finished\n");
489 DBI_ROW my_dbi_fetch_row(B_DB *mdb)
492 DBI_ROW row = NULL; // by default, return NULL
494 Dmsg0(500, "my_dbi_fetch_row start\n");
495 if ((!mdb->row || mdb->row_size < mdb->num_fields) && mdb->num_rows > 0) {
496 int num_fields = mdb->num_fields;
497 Dmsg1(500, "we have need space of %d bytes\n", sizeof(char *) * mdb->num_fields);
500 Dmsg0(500, "my_dbi_fetch_row freeing space\n");
501 Dmsg2(500, "my_dbi_free_row row: '%p' num_fields: '%d'\n", mdb->row, mdb->num_fields);
502 if (mdb->num_rows != 0) {
503 for(j = 0; j < mdb->num_fields; j++) {
504 Dmsg2(500, "my_dbi_free_row row '%p' '%d'\n", mdb->row[j], j);
512 //num_fields += 20; /* add a bit extra */
513 mdb->row = (DBI_ROW)malloc(sizeof(char *) * num_fields);
514 mdb->row_size = num_fields;
516 // now reset the row_number now that we have the space allocated
520 // if still within the result set
521 if (mdb->row_number <= mdb->num_rows && mdb->row_number != DBI_ERROR_BADPTR) {
522 Dmsg2(500, "my_dbi_fetch_row row number '%d' is acceptable (1..%d)\n", mdb->row_number, mdb->num_rows);
523 // get each value from this row
524 for (j = 0; j < mdb->num_fields; j++) {
525 mdb->row[j] = my_dbi_getvalue(mdb->result, mdb->row_number, j);
526 // allocate space to queue row
527 mdb->field_get = (DBI_FIELD_GET *)malloc(sizeof(DBI_FIELD_GET));
528 // store the pointer in queue
529 mdb->field_get->value = mdb->row[j];
530 Dmsg4(500, "my_dbi_fetch_row row[%d] field: '%p' in queue: '%p' has value: '%s'\n",
531 j, mdb->row[j], mdb->field_get->value, mdb->row[j]);
532 // insert in queue to future free
533 qinsert(&dbi_getvalue_list, &mdb->field_get->bq);
535 // increment the row number for the next call
540 Dmsg2(500, "my_dbi_fetch_row row number '%d' is NOT acceptable (1..%d)\n", mdb->row_number, mdb->num_rows);
543 Dmsg1(500, "my_dbi_fetch_row finishes returning %p\n", row);
548 int my_dbi_max_length(B_DB *mdb, int field_num) {
550 // for a given column, find the max length
558 for (i = 0; i < mdb->num_rows; i++) {
559 if (my_dbi_getisnull(mdb->result, i, field_num)) {
560 this_length = 4; // "NULL"
562 cbuf = my_dbi_getvalue(mdb->result, i, field_num);
563 this_length = cstrlen(cbuf);
564 // cbuf is always free
568 if (max_length < this_length) {
569 max_length = this_length;
576 DBI_FIELD * my_dbi_fetch_field(B_DB *mdb)
581 Dmsg0(500, "my_dbi_fetch_field starts\n");
583 if (!mdb->fields || mdb->fields_size < mdb->num_fields) {
587 Dmsg1(500, "allocating space for %d fields\n", mdb->num_fields);
588 mdb->fields = (DBI_FIELD *)malloc(sizeof(DBI_FIELD) * mdb->num_fields);
589 mdb->fields_size = mdb->num_fields;
591 for (i = 0; i < mdb->num_fields; i++) {
592 // num_fileds is starting at 1, increment i by 1
594 Dmsg1(500, "filling field %d\n", i);
595 mdb->fields[i].name = (char *)dbi_result_get_field_name(mdb->result, dbi_index);
596 mdb->fields[i].max_length = my_dbi_max_length(mdb, i);
597 mdb->fields[i].type = dbi_result_get_field_type_idx(mdb->result, dbi_index);
598 mdb->fields[i].flags = dbi_result_get_field_attribs_idx(mdb->result, dbi_index);
600 Dmsg4(500, "my_dbi_fetch_field finds field '%s' has length='%d' type='%d' and IsNull=%d\n",
601 mdb->fields[i].name, mdb->fields[i].max_length, mdb->fields[i].type,
602 mdb->fields[i].flags);
606 // increment field number for the next time around
608 Dmsg0(500, "my_dbi_fetch_field finishes\n");
609 return &mdb->fields[mdb->field_number++];
612 void my_dbi_data_seek(B_DB *mdb, int row)
614 // set the row number to be returned on the next call
615 // to my_dbi_fetch_row
616 mdb->row_number = row;
619 void my_dbi_field_seek(B_DB *mdb, int field)
621 mdb->field_number = field;
625 * Note, if this routine returns 1 (failure), Bacula expects
626 * that no result has been stored.
628 * Returns: 0 on success
632 int my_dbi_query(B_DB *mdb, const char *query)
635 Dmsg1(500, "my_dbi_query started %s\n", query);
636 // We are starting a new query. reset everything.
638 mdb->row_number = -1;
639 mdb->field_number = -1;
642 dbi_result_free(mdb->result); /* hmm, someone forgot to free?? */
646 mdb->result = (void **)dbi_conn_query(mdb->db, query);
649 Dmsg2(50, "Query failed: %s %p\n", query, mdb->result);
653 mdb->status = (dbi_error_flag) dbi_conn_error(mdb->db, &errmsg);
655 if (mdb->status == DBI_ERROR_NONE) {
656 Dmsg1(500, "we have a result\n", query);
658 // how many fields in the set?
659 // num_fields starting at 1
660 mdb->num_fields = dbi_result_get_numfields(mdb->result);
661 Dmsg1(500, "we have %d fields\n", mdb->num_fields);
662 // if no result num_rows is 0
663 mdb->num_rows = dbi_result_get_numrows(mdb->result);
664 Dmsg1(500, "we have %d rows\n", mdb->num_rows);
666 mdb->status = (dbi_error_flag) 0; /* succeed */
668 Dmsg1(50, "Result status failed: %s\n", query);
672 Dmsg0(500, "my_dbi_query finishing\n");
676 mdb->status = (dbi_error_flag) dbi_conn_error(mdb->db,&errmsg);
677 //dbi_conn_error(mdb->db, &errmsg);
678 Dmsg4(500, "my_dbi_query we failed dbi error: "
679 "'%s' '%p' '%d' flag '%d''\n", errmsg, mdb->result, mdb->result, mdb->status);
680 dbi_result_free(mdb->result);
682 mdb->status = (dbi_error_flag) 1; /* failed */
686 void my_dbi_free_result(B_DB *mdb)
692 Dmsg1(500, "my_dbi_free_result result '%p'\n", mdb->result);
693 dbi_result_free(mdb->result);
702 /* now is time to free all value return by my_dbi_get_value
703 * this is necessary because libdbi don't free memory return by yours results
704 * and Bacula has some routine wich call more than once time my_dbi_fetch_row
706 * Using a queue to store all pointer allocate is a good way to free all things
709 while((f=(DBI_FIELD_GET *)qremove(&dbi_getvalue_list))) {
710 Dmsg2(500, "my_dbi_free_result field value: '%p' in queue: '%p'\n", f->value, f);
722 Dmsg0(500, "my_dbi_free_result finish\n");
726 const char *my_dbi_strerror(B_DB *mdb)
730 dbi_conn_error(mdb->db, &errmsg);
735 #ifdef HAVE_BATCH_FILE_INSERT
738 * This can be a bit strang but is the one way to do
743 int my_dbi_batch_start(JCR *jcr, B_DB *mdb)
745 char *query = "COPY batch FROM STDIN";
747 Dmsg0(500, "my_dbi_batch_start started\n");
749 switch (mdb->db_type) {
752 if (my_dbi_query(mdb,
753 "CREATE TEMPORARY TABLE batch ("
759 "MD5 tinyblob)") == 1)
761 Dmsg0(500, "my_dbi_batch_start failed\n");
765 Dmsg0(500, "my_dbi_batch_start finishing\n");
768 case SQL_TYPE_POSTGRESQL:
770 if (my_dbi_query(mdb, "CREATE TEMPORARY TABLE batch ("
776 "md5 varchar)") == 1)
778 Dmsg0(500, "my_dbi_batch_start failed\n");
782 // We are starting a new query. reset everything.
784 mdb->row_number = -1;
785 mdb->field_number = -1;
787 my_dbi_free_result(mdb);
789 for (int i=0; i < 10; i++) {
790 my_dbi_query(mdb, query);
797 Dmsg1(50, "Query failed: %s\n", query);
801 mdb->status = (dbi_error_flag)dbi_conn_error(mdb->db, NULL);
802 //mdb->status = DBI_ERROR_NONE;
804 if (mdb->status == DBI_ERROR_NONE) {
805 // how many fields in the set?
806 mdb->num_fields = dbi_result_get_numfields(mdb->result);
807 mdb->num_rows = dbi_result_get_numrows(mdb->result);
808 mdb->status = (dbi_error_flag) 1;
810 Dmsg1(50, "Result status failed: %s\n", query);
814 Dmsg0(500, "my_postgresql_batch_start finishing\n");
818 case SQL_TYPE_SQLITE:
820 if (my_dbi_query(mdb, "CREATE TEMPORARY TABLE batch ("
826 "MD5 tinyblob)") == 1)
828 Dmsg0(500, "my_dbi_batch_start failed\n");
832 Dmsg0(500, "my_dbi_batch_start finishing\n");
835 case SQL_TYPE_SQLITE3:
837 if (my_dbi_query(mdb, "CREATE TEMPORARY TABLE batch ("
843 "MD5 tinyblob)") == 1)
845 Dmsg0(500, "my_dbi_batch_start failed\n");
849 Dmsg0(500, "my_dbi_batch_start finishing\n");
855 Mmsg1(&mdb->errmsg, _("error starting batch mode: %s"), my_dbi_strerror(mdb));
856 mdb->status = (dbi_error_flag) 0;
857 my_dbi_free_result(mdb);
862 /* set error to something to abort operation */
863 int my_dbi_batch_end(JCR *jcr, B_DB *mdb, const char *error)
867 int (*custom_function)(void*, const char*) = NULL;
868 dbi_conn_t *myconn = (dbi_conn_t *)(mdb->db);
870 Dmsg0(500, "my_dbi_batch_end started\n");
872 if (!mdb) { /* no files ? */
876 switch (mdb->db_type) {
879 mdb->status = (dbi_error_flag) 0;
882 case SQL_TYPE_POSTGRESQL:
883 custom_function = (custom_function_end_t)dbi_driver_specific_function(dbi_conn_get_driver(mdb->db), "PQputCopyEnd");
887 res = (*custom_function)(myconn->connection, error);
888 } while (res == 0 && --count > 0);
892 mdb->status = (dbi_error_flag) 1;
896 Dmsg0(500, "we failed\n");
897 mdb->status = (dbi_error_flag) 0;
898 //Mmsg1(&mdb->errmsg, _("error ending batch mode: %s"), PQerrorMessage(mdb->db));
901 case SQL_TYPE_SQLITE:
903 mdb->status = (dbi_error_flag) 0;
906 case SQL_TYPE_SQLITE3:
908 mdb->status = (dbi_error_flag) 0;
913 Dmsg0(500, "my_dbi_batch_end finishing\n");
919 * This function is big and use a big switch.
920 * In near future is better split in small functions
924 int my_dbi_batch_insert(JCR *jcr, B_DB *mdb, ATTR_DBR *ar)
928 dbi_conn_t *myconn = (dbi_conn_t *)(mdb->db);
929 int (*custom_function)(void*, const char*, int) = NULL;
930 char* (*custom_function_error)(void*) = NULL;
935 Dmsg0(500, "my_dbi_batch_insert started \n");
937 mdb->esc_name = check_pool_memory_size(mdb->esc_name, mdb->fnl*2+1);
938 mdb->esc_path = check_pool_memory_size(mdb->esc_path, mdb->pnl*2+1);
940 if (ar->Digest == NULL || ar->Digest[0] == 0) {
946 switch (mdb->db_type) {
948 db_escape_string(jcr, mdb, mdb->esc_name, mdb->fname, mdb->fnl);
949 db_escape_string(jcr, mdb, mdb->esc_path, mdb->path, mdb->pnl);
950 len = Mmsg(mdb->cmd, "INSERT INTO batch VALUES (%u,%s,'%s','%s','%s','%s')",
951 ar->FileIndex, edit_int64(ar->JobId,ed1), mdb->esc_path,
952 mdb->esc_name, ar->attr, digest);
954 if (my_dbi_query(mdb,mdb->cmd) == 1)
956 Dmsg0(500, "my_dbi_batch_insert failed\n");
960 Dmsg0(500, "my_dbi_batch_insert finishing\n");
964 case SQL_TYPE_POSTGRESQL:
965 my_postgresql_copy_escape(mdb->esc_name, mdb->fname, mdb->fnl);
966 my_postgresql_copy_escape(mdb->esc_path, mdb->path, mdb->pnl);
967 len = Mmsg(mdb->cmd, "%u\t%s\t%s\t%s\t%s\t%s\n",
968 ar->FileIndex, edit_int64(ar->JobId, ed1), mdb->esc_path,
969 mdb->esc_name, ar->attr, digest);
971 /* libdbi don't support CopyData and we need call a postgresql
972 * specific function to do this work
974 Dmsg2(500, "my_dbi_batch_insert :\n %s \ncmd_size: %d",mdb->cmd, len);
975 if ((custom_function = (custom_function_insert_t)dbi_driver_specific_function(dbi_conn_get_driver(mdb->db),
976 "PQputCopyData")) != NULL) {
978 res = (*custom_function)(myconn->connection, mdb->cmd, len);
979 } while (res == 0 && --count > 0);
984 mdb->status = (dbi_error_flag) 1;
988 Dmsg0(500, "my_dbi_batch_insert failed\n");
992 Dmsg0(500, "my_dbi_batch_insert finishing\n");
995 // ensure to detect a PQerror
996 custom_function_error = (custom_function_error_t)dbi_driver_specific_function(dbi_conn_get_driver(mdb->db), "PQerrorMessage");
997 Dmsg1(500, "my_dbi_batch_insert failed\n PQerrorMessage: %s", (*custom_function_error)(myconn->connection));
1001 case SQL_TYPE_SQLITE:
1002 db_escape_string(jcr, mdb, mdb->esc_name, mdb->fname, mdb->fnl);
1003 db_escape_string(jcr, mdb, mdb->esc_path, mdb->path, mdb->pnl);
1004 len = Mmsg(mdb->cmd, "INSERT INTO batch VALUES (%u,%s,'%s','%s','%s','%s')",
1005 ar->FileIndex, edit_int64(ar->JobId,ed1), mdb->esc_path,
1006 mdb->esc_name, ar->attr, digest);
1007 if (my_dbi_query(mdb,mdb->cmd) == 1)
1009 Dmsg0(500, "my_dbi_batch_insert failed\n");
1013 Dmsg0(500, "my_dbi_batch_insert finishing\n");
1017 case SQL_TYPE_SQLITE3:
1018 db_escape_string(jcr, mdb, mdb->esc_name, mdb->fname, mdb->fnl);
1019 db_escape_string(jcr, mdb, mdb->esc_path, mdb->path, mdb->pnl);
1020 len = Mmsg(mdb->cmd, "INSERT INTO batch VALUES (%u,%s,'%s','%s','%s','%s')",
1021 ar->FileIndex, edit_int64(ar->JobId,ed1), mdb->esc_path,
1022 mdb->esc_name, ar->attr, digest);
1023 if (my_dbi_query(mdb,mdb->cmd) == 1)
1025 Dmsg0(500, "my_dbi_batch_insert failed\n");
1029 Dmsg0(500, "my_dbi_batch_insert finishing\n");
1036 Mmsg1(&mdb->errmsg, _("error inserting batch mode: %s"), my_dbi_strerror(mdb));
1037 mdb->status = (dbi_error_flag) 0;
1038 my_dbi_free_result(mdb);
1043 * Escape strings so that PostgreSQL is happy on COPY
1045 * NOTE! len is the length of the old string. Your new
1046 * string must be long enough (max 2*old+1) to hold
1047 * the escaped output.
1049 char *my_postgresql_copy_escape(char *dest, char *src, size_t len)
1051 /* we have to escape \t, \n, \r, \ */
1054 while (len > 0 && *src) {
1089 #endif /* HAVE_BATCH_FILE_INSERT */
1093 * int PQgetisnull(const PGresult *res,
1095 * int column_number);
1097 * use dbi_result_seek_row to search in result set
1099 int my_dbi_getisnull(dbi_result *result, int row_number, int column_number) {
1102 if(row_number == 0) {
1108 if(dbi_result_seek_row(result, row_number)) {
1110 i = dbi_result_field_is_null_idx(result,column_number);
1121 * char *PQgetvalue(const PGresult *res,
1123 * int column_number);
1125 * use dbi_result_seek_row to search in result set
1126 * use example to return only strings
1128 char *my_dbi_getvalue(dbi_result *result, int row_number, unsigned int column_number) {
1132 const char *field_name;
1133 unsigned short dbitype;
1134 size_t field_length;
1137 /* correct the index for dbi interface
1138 * dbi index begins 1
1139 * I prefer do not change others functions
1141 Dmsg3(600, "my_dbi_getvalue pre-starting result '%p' row number '%d' column number '%d'\n",
1142 result, row_number, column_number);
1146 if(row_number == 0) {
1150 Dmsg3(600, "my_dbi_getvalue starting result '%p' row number '%d' column number '%d'\n",
1151 result, row_number, column_number);
1153 if(dbi_result_seek_row(result, row_number)) {
1155 field_name = dbi_result_get_field_name(result, column_number);
1156 field_length = dbi_result_get_field_length(result, field_name);
1157 dbitype = dbi_result_get_field_type_idx(result,column_number);
1159 Dmsg3(500, "my_dbi_getvalue start: type: '%d' "
1160 "field_length bytes: '%d' fieldname: '%s'\n",
1161 dbitype, field_length, field_name);
1164 //buf = (char *)malloc(sizeof(char *) * field_length + 1);
1165 buf = (char *)malloc(field_length + 1);
1168 buf = (char *)malloc(sizeof(char *) * 50);
1172 case DBI_TYPE_INTEGER:
1173 num = dbi_result_get_longlong(result, field_name);
1174 edit_int64(num, buf);
1175 field_length = strlen(buf);
1177 case DBI_TYPE_STRING:
1179 field_length = bsnprintf(buf, field_length + 1, "%s",
1180 dbi_result_get_string(result, field_name));
1185 case DBI_TYPE_BINARY:
1186 /* dbi_result_get_binary return a NULL pointer if value is empty
1187 * following, change this to what Bacula espected
1190 field_length = bsnprintf(buf, field_length + 1, "%s",
1191 dbi_result_get_binary(result, field_name));
1196 case DBI_TYPE_DATETIME:
1200 last = dbi_result_get_datetime(result, field_name);
1203 field_length = bsnprintf(buf, 20, "0000-00-00 00:00:00");
1205 (void)localtime_r(&last, &tm);
1206 field_length = bsnprintf(buf, 20, "%04d-%02d-%02d %02d:%02d:%02d",
1207 (tm.tm_year + 1900), (tm.tm_mon + 1), tm.tm_mday,
1208 tm.tm_hour, tm.tm_min, tm.tm_sec);
1214 dbi_conn_error(dbi_result_get_conn(result), &errmsg);
1215 Dmsg1(500, "my_dbi_getvalue error: %s\n", errmsg);
1218 Dmsg3(500, "my_dbi_getvalue finish buffer: '%p' num bytes: '%d' data: '%s'\n",
1219 buf, field_length, buf);
1221 // don't worry about this buf
1225 int my_dbi_sql_insert_id(B_DB *mdb, char *table_name)
1228 Obtain the current value of the sequence that
1229 provides the serial value for primary key of the table.
1231 currval is local to our session. It is not affected by
1234 Determine the name of the sequence.
1235 PostgreSQL automatically creates a sequence using
1236 <table>_<column>_seq.
1237 At the time of writing, all tables used this format for
1238 for their primary key: <table>id
1239 Except for basefiles which has a primary key on baseid.
1240 Therefore, we need to special case that one table.
1242 everything else can use the PostgreSQL formula.
1248 if (mdb->db_type == SQL_TYPE_POSTGRESQL) {
1250 if (strcasecmp(table_name, "basefiles") == 0) {
1251 bstrncpy(sequence, "basefiles_baseid", sizeof(sequence));
1253 bstrncpy(sequence, table_name, sizeof(sequence));
1254 bstrncat(sequence, "_", sizeof(sequence));
1255 bstrncat(sequence, table_name, sizeof(sequence));
1256 bstrncat(sequence, "id", sizeof(sequence));
1259 bstrncat(sequence, "_seq", sizeof(sequence));
1260 id = dbi_conn_sequence_last(mdb->db, NT_(sequence));
1262 id = dbi_conn_sequence_last(mdb->db, NT_(table_name));
1268 #ifdef HAVE_BATCH_FILE_INSERT
1269 const char *my_dbi_batch_lock_path_query[4] = {
1271 "LOCK TABLES Path write, batch write, Path as p write",
1273 "BEGIN; LOCK TABLE Path IN SHARE ROW EXCLUSIVE MODE",
1279 const char *my_dbi_batch_lock_filename_query[4] = {
1281 "LOCK TABLES Filename write, batch write, Filename as f write",
1283 "BEGIN; LOCK TABLE Filename IN SHARE ROW EXCLUSIVE MODE",
1289 const char *my_dbi_batch_unlock_tables_query[4] = {
1299 const char *my_dbi_batch_fill_path_query[4] = {
1301 "INSERT INTO Path (Path) "
1302 "SELECT a.Path FROM "
1303 "(SELECT DISTINCT Path FROM batch) AS a WHERE NOT EXISTS "
1304 "(SELECT Path FROM Path AS p WHERE p.Path = a.Path)",
1306 "INSERT INTO Path (Path) "
1307 "SELECT a.Path FROM "
1308 "(SELECT DISTINCT Path FROM batch) AS a "
1309 "WHERE NOT EXISTS (SELECT Path FROM Path WHERE Path = a.Path) ",
1311 "INSERT INTO Path (Path)"
1312 " SELECT DISTINCT Path FROM batch"
1313 " EXCEPT SELECT Path FROM Path",
1315 "INSERT INTO Path (Path)"
1316 " SELECT DISTINCT Path FROM batch"
1317 " EXCEPT SELECT Path FROM Path"};
1319 const char *my_dbi_batch_fill_filename_query[4] = {
1321 "INSERT INTO Filename (Name) "
1322 "SELECT a.Name FROM "
1323 "(SELECT DISTINCT Name FROM batch) AS a WHERE NOT EXISTS "
1324 "(SELECT Name FROM Filename AS f WHERE f.Name = a.Name)",
1326 "INSERT INTO Filename (Name) "
1327 "SELECT a.Name FROM "
1328 "(SELECT DISTINCT Name FROM batch) as a "
1330 "(SELECT Name FROM Filename WHERE Name = a.Name)",
1332 "INSERT INTO Filename (Name)"
1333 " SELECT DISTINCT Name FROM batch "
1334 " EXCEPT SELECT Name FROM Filename",
1336 "INSERT INTO Filename (Name)"
1337 " SELECT DISTINCT Name FROM batch "
1338 " EXCEPT SELECT Name FROM Filename"};
1340 #endif /* HAVE_BATCH_FILE_INSERT */
1342 const char *my_dbi_match[4] = {
1353 #endif /* HAVE_DBI */