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
40 /* The following is necessary so that we do not include
41 * the dummy external definition of DB.
43 #define __SQL_C /* indicate that this is sql.c */
50 /* -----------------------------------------------------------------------
52 * DBI dependent defines and subroutines
54 * -----------------------------------------------------------------------
57 /* List of open databases */
58 static BQUEUE db_list = {&db_list, &db_list};
60 /* Control allocated fields by my_dbi_getvalue */
61 static BQUEUE dbi_getvalue_list = {&dbi_getvalue_list, &dbi_getvalue_list};
63 static pthread_mutex_t mutex = PTHREAD_MUTEX_INITIALIZER;
66 * Retrieve database type
75 * Initialize database data structure. In principal this should
76 * never have errors, or it is really fatal.
79 db_init_database(JCR *jcr, const char *db_name, const char *db_user, const char *db_password,
80 const char *db_address, int db_port, const char *db_socket,
81 int mult_db_connections)
85 char db_driverdir[256];
87 /* Constraint the db_driver */
89 Jmsg(jcr, M_FATAL, 0, _("A dbi driver for DBI must be supplied.\n"));
93 /* Do the correct selection of driver.
94 * Can be one of the varius supported by libdbi
98 bstrncpy(db_driver,"mysql", sizeof(db_driver));
100 case SQL_TYPE_POSTGRESQL:
101 bstrncpy(db_driver,"pgsql", sizeof(db_driver));
103 case SQL_TYPE_SQLITE:
104 bstrncpy(db_driver,"sqlite", sizeof(db_driver));
106 case SQL_TYPE_SQLITE3:
107 bstrncpy(db_driver,"sqlite3", sizeof(db_driver));
111 /* Set db_driverdir whereis is the libdbi drivers */
112 bstrncpy(db_driverdir, DBI_DRIVER_DIR, 255);
115 Jmsg(jcr, M_FATAL, 0, _("A user name for DBI must be supplied.\n"));
118 P(mutex); /* lock DB queue */
119 if (!mult_db_connections) {
120 /* Look to see if DB already open */
121 for (mdb=NULL; (mdb=(B_DB *)qnext(&db_list, &mdb->bq)); ) {
122 if (bstrcmp(mdb->db_name, db_name) &&
123 bstrcmp(mdb->db_address, db_address) &&
124 bstrcmp(mdb->db_driver, db_driver) &&
125 mdb->db_port == db_port) {
126 Dmsg4(100, "DB REopen %d %s %s erro: %d\n", mdb->ref_count, db_driver, db_name,
127 dbi_conn_error(mdb->db, NULL));
130 return mdb; /* already open */
134 Dmsg0(100, "db_open first time\n");
135 mdb = (B_DB *)malloc(sizeof(B_DB));
136 memset(mdb, 0, sizeof(B_DB));
137 mdb->db_name = bstrdup(db_name);
138 mdb->db_user = bstrdup(db_user);
140 mdb->db_password = bstrdup(db_password);
143 mdb->db_address = bstrdup(db_address);
146 mdb->db_socket = bstrdup(db_socket);
149 mdb->db_driverdir = bstrdup(db_driverdir);
152 mdb->db_driver = bstrdup(db_driver);
154 mdb->db_type = db_type;
155 mdb->db_port = db_port;
156 mdb->have_insert_id = TRUE;
157 mdb->errmsg = get_pool_memory(PM_EMSG); /* get error message buffer */
159 mdb->cmd = get_pool_memory(PM_EMSG); /* get command buffer */
160 mdb->cached_path = get_pool_memory(PM_FNAME);
161 mdb->cached_path_id = 0;
163 mdb->fname = get_pool_memory(PM_FNAME);
164 mdb->path = get_pool_memory(PM_FNAME);
165 mdb->esc_name = get_pool_memory(PM_FNAME);
166 mdb->esc_path = get_pool_memory(PM_FNAME);
167 mdb->allow_transactions = mult_db_connections;
168 qinsert(&db_list, &mdb->bq); /* put db in list */
174 * Now actually open the database. This can generate errors,
175 * which are returned in the errmsg
177 * DO NOT close the database or free(mdb) here !!!!
180 db_open_database(JCR *jcr, B_DB *mdb)
188 char *db_name = NULL;
192 if (mdb->connected) {
196 mdb->connected = false;
198 if ((errstat=rwl_init(&mdb->lock)) != 0) {
200 Mmsg1(&mdb->errmsg, _("Unable to initialize DB lock. ERR=%s\n"),
201 be.bstrerror(errstat));
207 bsnprintf(buf, sizeof(buf), "%d", mdb->db_port);
213 numdrivers = dbi_initialize_r(mdb->db_driverdir, &(mdb->instance));
214 if (numdrivers < 0) {
215 Mmsg2(&mdb->errmsg, _("Unable to locate the DBD drivers to DBI interface in: \n"
216 "db_driverdir=%s. It is probaly not found any drivers\n"),
217 mdb->db_driverdir,numdrivers);
221 mdb->db = (void **)dbi_conn_new_r(mdb->db_driver, mdb->instance);
222 /* Can be many types of databases */
223 switch (mdb->db_type) {
225 dbi_conn_set_option(mdb->db, "host", mdb->db_address); /* default = localhost */
226 dbi_conn_set_option(mdb->db, "port", port); /* default port */
227 dbi_conn_set_option(mdb->db, "username", mdb->db_user); /* login name */
228 dbi_conn_set_option(mdb->db, "password", mdb->db_password); /* password */
229 dbi_conn_set_option(mdb->db, "dbname", mdb->db_name); /* database name */
231 case SQL_TYPE_POSTGRESQL:
232 dbi_conn_set_option(mdb->db, "host", mdb->db_address);
233 dbi_conn_set_option(mdb->db, "port", port);
234 dbi_conn_set_option(mdb->db, "username", mdb->db_user);
235 dbi_conn_set_option(mdb->db, "password", mdb->db_password);
236 dbi_conn_set_option(mdb->db, "dbname", mdb->db_name);
238 case SQL_TYPE_SQLITE:
239 len = strlen(working_directory) + 5;
240 db_dir = (char *)malloc(len);
241 strcpy(db_dir, working_directory);
243 len = strlen(mdb->db_name) + 5;
244 db_name = (char *)malloc(len);
245 strcpy(db_name, mdb->db_name);
246 strcat(db_name, ".db");
247 dbi_conn_set_option(mdb->db, "sqlite_dbdir", db_dir);
248 dbi_conn_set_option(mdb->db, "dbname", db_name);
250 case SQL_TYPE_SQLITE3:
251 len = strlen(working_directory) + 5;
252 db_dir = (char *)malloc(len);
253 strcpy(db_dir, working_directory);
255 len = strlen(mdb->db_name) + 5;
256 db_name = (char *)malloc(len);
257 strcpy(db_name, mdb->db_name);
258 strcat(db_name, ".db");
259 dbi_conn_set_option(mdb->db, "sqlite3_dbdir", db_dir);
260 dbi_conn_set_option(mdb->db, "dbname", db_name);
261 Dmsg2(500, "SQLITE: %s %s\n", db_dir, db_name);
265 /* If connection fails, try at 5 sec intervals for 30 seconds. */
266 for (int retry=0; retry < 6; retry++) {
268 dbstat = dbi_conn_connect(mdb->db);
273 dbi_conn_error(mdb->db, &errmsg);
274 Dmsg1(50, "dbi error: %s\n", errmsg);
281 Mmsg3(&mdb->errmsg, _("Unable to connect to DBI interface. Type=%s Database=%s User=%s\n"
282 "Possible causes: SQL server not running; password incorrect; max_connections exceeded.\n"),
283 mdb->db_driver, mdb->db_name, mdb->db_user);
288 Dmsg0(50, "dbi_real_connect done\n");
289 Dmsg3(50, "db_user=%s db_name=%s db_password=%s\n",
290 mdb->db_user, mdb->db_name,
291 mdb->db_password==NULL?"(NULL)":mdb->db_password);
293 mdb->connected = true;
295 if (!check_tables_version(jcr, mdb)) {
300 switch (mdb->db_type) {
302 /* Set connection timeout to 8 days specialy for batch mode */
303 sql_query(mdb, "SET wait_timeout=691200");
304 sql_query(mdb, "SET interactive_timeout=691200");
306 case SQL_TYPE_POSTGRESQL:
307 /* tell PostgreSQL we are using standard conforming strings
308 and avoid warnings such as:
309 WARNING: nonstandard use of \\ in a string literal
311 sql_query(mdb, "SET datestyle TO 'ISO, YMD'");
312 sql_query(mdb, "set standard_conforming_strings=on");
328 db_close_database(JCR *jcr, B_DB *mdb)
333 db_end_transaction(jcr, mdb);
335 sql_free_result(mdb);
337 if (mdb->ref_count == 0) {
339 if (mdb->connected && mdb->db) {
341 dbi_shutdown_r(mdb->instance);
343 mdb->instance = NULL;
345 rwl_destroy(&mdb->lock);
346 free_pool_memory(mdb->errmsg);
347 free_pool_memory(mdb->cmd);
348 free_pool_memory(mdb->cached_path);
349 free_pool_memory(mdb->fname);
350 free_pool_memory(mdb->path);
351 free_pool_memory(mdb->esc_name);
352 free_pool_memory(mdb->esc_path);
359 if (mdb->db_password) {
360 free(mdb->db_password);
362 if (mdb->db_address) {
363 free(mdb->db_address);
365 if (mdb->db_socket) {
366 free(mdb->db_socket);
368 if (mdb->db_driverdir) {
369 free(mdb->db_driverdir);
371 if (mdb->db_driver) {
372 free(mdb->db_driver);
379 void db_thread_cleanup()
383 * Return the next unique index (auto-increment) for
384 * the given table. Return NULL on error.
387 int db_next_index(JCR *jcr, B_DB *mdb, char *table, char *index)
389 strcpy(index, "NULL");
395 * Escape strings so that DBI is happy
397 * NOTE! len is the length of the old string. Your new
398 * string must be long enough (max 2*old+1) to hold
399 * the escaped output.
401 * dbi_conn_quote_string_copy receives a pointer to pointer.
402 * We need copy the value of pointer to snew because libdbi change the
406 db_escape_string(JCR *jcr, B_DB *mdb, char *snew, char *old, int len)
414 /* correct the size of old basead in len
415 * and copy new string to inew
417 inew = (char *)malloc(sizeof(char) * len + 1);
418 bstrncpy(inew,old,len + 1);
419 /* escape the correct size of old */
420 dbi_conn_escape_string_copy(mdb->db, inew, &pnew);
422 /* copy the escaped string to snew */
423 bstrncpy(snew, pnew, 2 * len + 1);
426 Dmsg2(500, "dbi_conn_escape_string_copy %p %s\n",snew,snew);
431 * Submit a general SQL command (cmd), and for each row returned,
432 * the sqlite_handler is called with the ctx.
434 bool db_sql_query(B_DB *mdb, const char *query, DB_RESULT_HANDLER *result_handler, void *ctx)
438 Dmsg0(500, "db_sql_query started\n");
441 if (sql_query(mdb, query) != 0) {
442 Mmsg(mdb->errmsg, _("Query failed: %s: ERR=%s\n"), query, sql_strerror(mdb));
444 Dmsg0(500, "db_sql_query failed\n");
447 Dmsg0(500, "db_sql_query succeeded. checking handler\n");
449 if (result_handler != NULL) {
450 Dmsg0(500, "db_sql_query invoking handler\n");
451 if ((mdb->result = sql_store_result(mdb)) != NULL) {
452 int num_fields = sql_num_fields(mdb);
454 Dmsg0(500, "db_sql_query sql_store_result suceeded\n");
455 while ((row = sql_fetch_row(mdb)) != NULL) {
457 Dmsg0(500, "db_sql_query sql_fetch_row worked\n");
458 if (result_handler(ctx, num_fields, row))
462 sql_free_result(mdb);
467 Dmsg0(500, "db_sql_query finished\n");
474 DBI_ROW my_dbi_fetch_row(B_DB *mdb)
477 DBI_ROW row = NULL; // by default, return NULL
479 Dmsg0(500, "my_dbi_fetch_row start\n");
480 if ((!mdb->row || mdb->row_size < mdb->num_fields) && mdb->num_rows > 0) {
481 int num_fields = mdb->num_fields;
482 Dmsg1(500, "we have need space of %d bytes\n", sizeof(char *) * mdb->num_fields);
485 Dmsg0(500, "my_dbi_fetch_row freeing space\n");
486 Dmsg2(500, "my_dbi_free_row row: '%p' num_fields: '%d'\n", mdb->row, mdb->num_fields);
487 if (mdb->num_rows != 0) {
488 for(j = 0; j < mdb->num_fields; j++) {
489 Dmsg2(500, "my_dbi_free_row row '%p' '%d'\n", mdb->row[j], j);
497 //num_fields += 20; /* add a bit extra */
498 mdb->row = (DBI_ROW)malloc(sizeof(char *) * num_fields);
499 mdb->row_size = num_fields;
501 // now reset the row_number now that we have the space allocated
505 // if still within the result set
506 if (mdb->row_number <= mdb->num_rows && mdb->row_number != DBI_ERROR_BADPTR) {
507 Dmsg2(500, "my_dbi_fetch_row row number '%d' is acceptable (1..%d)\n", mdb->row_number, mdb->num_rows);
508 // get each value from this row
509 for (j = 0; j < mdb->num_fields; j++) {
510 mdb->row[j] = my_dbi_getvalue(mdb->result, mdb->row_number, j);
511 // allocate space to queue row
512 mdb->field_get = (DBI_FIELD_GET *)malloc(sizeof(DBI_FIELD_GET));
513 // store the pointer in queue
514 mdb->field_get->value = mdb->row[j];
515 Dmsg4(500, "my_dbi_fetch_row row[%d] field: '%p' in queue: '%p' has value: '%s'\n",
516 j, mdb->row[j], mdb->field_get->value, mdb->row[j]);
517 // insert in queue to future free
518 qinsert(&dbi_getvalue_list, &mdb->field_get->bq);
520 // increment the row number for the next call
525 Dmsg2(500, "my_dbi_fetch_row row number '%d' is NOT acceptable (1..%d)\n", mdb->row_number, mdb->num_rows);
528 Dmsg1(500, "my_dbi_fetch_row finishes returning %p\n", row);
533 int my_dbi_max_length(B_DB *mdb, int field_num) {
535 // for a given column, find the max length
543 for (i = 0; i < mdb->num_rows; i++) {
544 if (my_dbi_getisnull(mdb->result, i, field_num)) {
545 this_length = 4; // "NULL"
547 cbuf = my_dbi_getvalue(mdb->result, i, field_num);
548 this_length = cstrlen(cbuf);
549 // cbuf is always free
553 if (max_length < this_length) {
554 max_length = this_length;
561 DBI_FIELD * my_dbi_fetch_field(B_DB *mdb)
566 Dmsg0(500, "my_dbi_fetch_field starts\n");
568 if (!mdb->fields || mdb->fields_size < mdb->num_fields) {
572 Dmsg1(500, "allocating space for %d fields\n", mdb->num_fields);
573 mdb->fields = (DBI_FIELD *)malloc(sizeof(DBI_FIELD) * mdb->num_fields);
574 mdb->fields_size = mdb->num_fields;
576 for (i = 0; i < mdb->num_fields; i++) {
577 // num_fileds is starting at 1, increment i by 1
579 Dmsg1(500, "filling field %d\n", i);
580 mdb->fields[i].name = (char *)dbi_result_get_field_name(mdb->result, dbi_index);
581 mdb->fields[i].max_length = my_dbi_max_length(mdb, i);
582 mdb->fields[i].type = dbi_result_get_field_type_idx(mdb->result, dbi_index);
583 mdb->fields[i].flags = dbi_result_get_field_attribs_idx(mdb->result, dbi_index);
585 Dmsg4(500, "my_dbi_fetch_field finds field '%s' has length='%d' type='%d' and IsNull=%d\n",
586 mdb->fields[i].name, mdb->fields[i].max_length, mdb->fields[i].type,
587 mdb->fields[i].flags);
591 // increment field number for the next time around
593 Dmsg0(500, "my_dbi_fetch_field finishes\n");
594 return &mdb->fields[mdb->field_number++];
597 void my_dbi_data_seek(B_DB *mdb, int row)
599 // set the row number to be returned on the next call
600 // to my_dbi_fetch_row
601 mdb->row_number = row;
604 void my_dbi_field_seek(B_DB *mdb, int field)
606 mdb->field_number = field;
610 * Note, if this routine returns 1 (failure), Bacula expects
611 * that no result has been stored.
613 * Returns: 0 on success
617 int my_dbi_query(B_DB *mdb, const char *query)
620 Dmsg1(500, "my_dbi_query started %s\n", query);
621 // We are starting a new query. reset everything.
623 mdb->row_number = -1;
624 mdb->field_number = -1;
627 dbi_result_free(mdb->result); /* hmm, someone forgot to free?? */
631 mdb->result = (void **)dbi_conn_query(mdb->db, query);
634 Dmsg2(50, "Query failed: %s %p\n", query, mdb->result);
638 mdb->status = (dbi_error_flag) dbi_conn_error(mdb->db, &errmsg);
640 if (mdb->status == DBI_ERROR_NONE) {
641 Dmsg1(500, "we have a result\n", query);
643 // how many fields in the set?
644 // num_fields starting at 1
645 mdb->num_fields = dbi_result_get_numfields(mdb->result);
646 Dmsg1(500, "we have %d fields\n", mdb->num_fields);
647 // if no result num_rows is 0
648 mdb->num_rows = dbi_result_get_numrows(mdb->result);
649 Dmsg1(500, "we have %d rows\n", mdb->num_rows);
651 mdb->status = (dbi_error_flag) 0; /* succeed */
653 Dmsg1(50, "Result status failed: %s\n", query);
657 Dmsg0(500, "my_dbi_query finishing\n");
661 mdb->status = (dbi_error_flag) dbi_conn_error(mdb->db,&errmsg);
662 //dbi_conn_error(mdb->db, &errmsg);
663 Dmsg4(500, "my_dbi_query we failed dbi error: "
664 "'%s' '%p' '%d' flag '%d''\n", errmsg, mdb->result, mdb->result, mdb->status);
665 dbi_result_free(mdb->result);
667 mdb->status = (dbi_error_flag) 1; /* failed */
671 void my_dbi_free_result(B_DB *mdb)
677 Dmsg1(500, "my_dbi_free_result result '%p'\n", mdb->result);
678 dbi_result_free(mdb->result);
687 /* now is time to free all value return by my_dbi_get_value
688 * this is necessary because libdbi don't free memory return by yours results
689 * and Bacula has some routine wich call more than once time my_dbi_fetch_row
691 * Using a queue to store all pointer allocate is a good way to free all things
694 while((f=(DBI_FIELD_GET *)qremove(&dbi_getvalue_list))) {
695 Dmsg2(500, "my_dbi_free_result field value: '%p' in queue: '%p'\n", f->value, f);
707 Dmsg0(500, "my_dbi_free_result finish\n");
711 const char *my_dbi_strerror(B_DB *mdb)
715 dbi_conn_error(mdb->db, &errmsg);
720 #ifdef HAVE_BATCH_FILE_INSERT
723 * This can be a bit strang but is the one way to do
728 int my_dbi_batch_start(JCR *jcr, B_DB *mdb)
730 char *query = "COPY batch FROM STDIN";
732 Dmsg0(500, "my_dbi_batch_start started\n");
734 switch (mdb->db_type) {
737 if (my_dbi_query(mdb,
738 "CREATE TEMPORARY TABLE batch ("
744 "MD5 tinyblob)") == 1)
746 Dmsg0(500, "my_dbi_batch_start failed\n");
750 Dmsg0(500, "my_dbi_batch_start finishing\n");
753 case SQL_TYPE_POSTGRESQL:
755 if (my_dbi_query(mdb, "CREATE TEMPORARY TABLE batch ("
761 "md5 varchar)") == 1)
763 Dmsg0(500, "my_dbi_batch_start failed\n");
767 // We are starting a new query. reset everything.
769 mdb->row_number = -1;
770 mdb->field_number = -1;
772 my_dbi_free_result(mdb);
774 for (int i=0; i < 10; i++) {
775 my_dbi_query(mdb, query);
782 Dmsg1(50, "Query failed: %s\n", query);
786 mdb->status = (dbi_error_flag)dbi_conn_error(mdb->db, NULL);
787 //mdb->status = DBI_ERROR_NONE;
789 if (mdb->status == DBI_ERROR_NONE) {
790 // how many fields in the set?
791 mdb->num_fields = dbi_result_get_numfields(mdb->result);
792 mdb->num_rows = dbi_result_get_numrows(mdb->result);
793 mdb->status = (dbi_error_flag) 1;
795 Dmsg1(50, "Result status failed: %s\n", query);
799 Dmsg0(500, "my_postgresql_batch_start finishing\n");
803 case SQL_TYPE_SQLITE:
805 if (my_dbi_query(mdb, "CREATE TEMPORARY TABLE batch ("
811 "MD5 tinyblob)") == 1)
813 Dmsg0(500, "my_dbi_batch_start failed\n");
817 Dmsg0(500, "my_dbi_batch_start finishing\n");
820 case SQL_TYPE_SQLITE3:
822 if (my_dbi_query(mdb, "CREATE TEMPORARY TABLE batch ("
828 "MD5 tinyblob)") == 1)
830 Dmsg0(500, "my_dbi_batch_start failed\n");
834 Dmsg0(500, "my_dbi_batch_start finishing\n");
840 Mmsg1(&mdb->errmsg, _("error starting batch mode: %s"), my_dbi_strerror(mdb));
841 mdb->status = (dbi_error_flag) 0;
842 my_dbi_free_result(mdb);
847 /* set error to something to abort operation */
848 int my_dbi_batch_end(JCR *jcr, B_DB *mdb, const char *error)
852 int (*custom_function)(void*, const char*) = NULL;
853 dbi_conn_t *myconn = (dbi_conn_t *)(mdb->db);
855 Dmsg0(500, "my_dbi_batch_end started\n");
857 if (!mdb) { /* no files ? */
861 switch (mdb->db_type) {
864 mdb->status = (dbi_error_flag) 0;
867 case SQL_TYPE_POSTGRESQL:
868 custom_function = (custom_function_end_t)dbi_driver_specific_function(dbi_conn_get_driver(mdb->db), "PQputCopyEnd");
872 res = (*custom_function)(myconn->connection, error);
873 } while (res == 0 && --count > 0);
877 mdb->status = (dbi_error_flag) 1;
881 Dmsg0(500, "we failed\n");
882 mdb->status = (dbi_error_flag) 0;
883 //Mmsg1(&mdb->errmsg, _("error ending batch mode: %s"), PQerrorMessage(mdb->db));
886 case SQL_TYPE_SQLITE:
888 mdb->status = (dbi_error_flag) 0;
891 case SQL_TYPE_SQLITE3:
893 mdb->status = (dbi_error_flag) 0;
898 Dmsg0(500, "my_dbi_batch_end finishing\n");
904 * This function is big and use a big switch.
905 * In near future is better split in small functions
909 int my_dbi_batch_insert(JCR *jcr, B_DB *mdb, ATTR_DBR *ar)
913 dbi_conn_t *myconn = (dbi_conn_t *)(mdb->db);
914 int (*custom_function)(void*, const char*, int) = NULL;
915 char* (*custom_function_error)(void*) = NULL;
920 Dmsg0(500, "my_dbi_batch_insert started \n");
922 mdb->esc_name = check_pool_memory_size(mdb->esc_name, mdb->fnl*2+1);
923 mdb->esc_path = check_pool_memory_size(mdb->esc_path, mdb->pnl*2+1);
925 if (ar->Digest == NULL || ar->Digest[0] == 0) {
931 switch (mdb->db_type) {
933 db_escape_string(jcr, mdb, mdb->esc_name, mdb->fname, mdb->fnl);
934 db_escape_string(jcr, mdb, mdb->esc_path, mdb->path, mdb->pnl);
935 len = Mmsg(mdb->cmd, "INSERT INTO batch VALUES (%u,%s,'%s','%s','%s','%s')",
936 ar->FileIndex, edit_int64(ar->JobId,ed1), mdb->esc_path,
937 mdb->esc_name, ar->attr, digest);
939 if (my_dbi_query(mdb,mdb->cmd) == 1)
941 Dmsg0(500, "my_dbi_batch_insert failed\n");
945 Dmsg0(500, "my_dbi_batch_insert finishing\n");
949 case SQL_TYPE_POSTGRESQL:
950 my_postgresql_copy_escape(mdb->esc_name, mdb->fname, mdb->fnl);
951 my_postgresql_copy_escape(mdb->esc_path, mdb->path, mdb->pnl);
952 len = Mmsg(mdb->cmd, "%u\t%s\t%s\t%s\t%s\t%s\n",
953 ar->FileIndex, edit_int64(ar->JobId, ed1), mdb->esc_path,
954 mdb->esc_name, ar->attr, digest);
956 /* libdbi don't support CopyData and we need call a postgresql
957 * specific function to do this work
959 Dmsg2(500, "my_dbi_batch_insert :\n %s \ncmd_size: %d",mdb->cmd, len);
960 if ((custom_function = (custom_function_insert_t)dbi_driver_specific_function(dbi_conn_get_driver(mdb->db),
961 "PQputCopyData")) != NULL) {
963 res = (*custom_function)(myconn->connection, mdb->cmd, len);
964 } while (res == 0 && --count > 0);
969 mdb->status = (dbi_error_flag) 1;
973 Dmsg0(500, "my_dbi_batch_insert failed\n");
977 Dmsg0(500, "my_dbi_batch_insert finishing\n");
980 // ensure to detect a PQerror
981 custom_function_error = (custom_function_error_t)dbi_driver_specific_function(dbi_conn_get_driver(mdb->db), "PQerrorMessage");
982 Dmsg1(500, "my_dbi_batch_insert failed\n PQerrorMessage: %s", (*custom_function_error)(myconn->connection));
986 case SQL_TYPE_SQLITE:
987 db_escape_string(jcr, mdb, mdb->esc_name, mdb->fname, mdb->fnl);
988 db_escape_string(jcr, mdb, mdb->esc_path, mdb->path, mdb->pnl);
989 len = Mmsg(mdb->cmd, "INSERT INTO batch VALUES (%u,%s,'%s','%s','%s','%s')",
990 ar->FileIndex, edit_int64(ar->JobId,ed1), mdb->esc_path,
991 mdb->esc_name, ar->attr, digest);
992 if (my_dbi_query(mdb,mdb->cmd) == 1)
994 Dmsg0(500, "my_dbi_batch_insert failed\n");
998 Dmsg0(500, "my_dbi_batch_insert finishing\n");
1002 case SQL_TYPE_SQLITE3:
1003 db_escape_string(jcr, mdb, mdb->esc_name, mdb->fname, mdb->fnl);
1004 db_escape_string(jcr, mdb, mdb->esc_path, mdb->path, mdb->pnl);
1005 len = Mmsg(mdb->cmd, "INSERT INTO batch VALUES (%u,%s,'%s','%s','%s','%s')",
1006 ar->FileIndex, edit_int64(ar->JobId,ed1), mdb->esc_path,
1007 mdb->esc_name, ar->attr, digest);
1008 if (my_dbi_query(mdb,mdb->cmd) == 1)
1010 Dmsg0(500, "my_dbi_batch_insert failed\n");
1014 Dmsg0(500, "my_dbi_batch_insert finishing\n");
1021 Mmsg1(&mdb->errmsg, _("error inserting batch mode: %s"), my_dbi_strerror(mdb));
1022 mdb->status = (dbi_error_flag) 0;
1023 my_dbi_free_result(mdb);
1028 * Escape strings so that PostgreSQL is happy on COPY
1030 * NOTE! len is the length of the old string. Your new
1031 * string must be long enough (max 2*old+1) to hold
1032 * the escaped output.
1034 char *my_postgresql_copy_escape(char *dest, char *src, size_t len)
1036 /* we have to escape \t, \n, \r, \ */
1039 while (len > 0 && *src) {
1074 #endif /* HAVE_BATCH_FILE_INSERT */
1078 * int PQgetisnull(const PGresult *res,
1080 * int column_number);
1082 * use dbi_result_seek_row to search in result set
1084 int my_dbi_getisnull(dbi_result *result, int row_number, int column_number) {
1087 if(row_number == 0) {
1093 if(dbi_result_seek_row(result, row_number)) {
1095 i = dbi_result_field_is_null_idx(result,column_number);
1106 * char *PQgetvalue(const PGresult *res,
1108 * int column_number);
1110 * use dbi_result_seek_row to search in result set
1111 * use example to return only strings
1113 char *my_dbi_getvalue(dbi_result *result, int row_number, unsigned int column_number) {
1117 const char *field_name;
1118 unsigned short dbitype;
1119 size_t field_length;
1122 /* correct the index for dbi interface
1123 * dbi index begins 1
1124 * I prefer do not change others functions
1126 Dmsg3(600, "my_dbi_getvalue pre-starting result '%p' row number '%d' column number '%d'\n",
1127 result, row_number, column_number);
1131 if(row_number == 0) {
1135 Dmsg3(600, "my_dbi_getvalue starting result '%p' row number '%d' column number '%d'\n",
1136 result, row_number, column_number);
1138 if(dbi_result_seek_row(result, row_number)) {
1140 field_name = dbi_result_get_field_name(result, column_number);
1141 field_length = dbi_result_get_field_length(result, field_name);
1142 dbitype = dbi_result_get_field_type_idx(result,column_number);
1144 Dmsg3(500, "my_dbi_getvalue start: type: '%d' "
1145 "field_length bytes: '%d' fieldname: '%s'\n",
1146 dbitype, field_length, field_name);
1149 //buf = (char *)malloc(sizeof(char *) * field_length + 1);
1150 buf = (char *)malloc(field_length + 1);
1153 buf = (char *)malloc(sizeof(char *) * 50);
1157 case DBI_TYPE_INTEGER:
1158 num = dbi_result_get_longlong(result, field_name);
1159 edit_int64(num, buf);
1160 field_length = strlen(buf);
1162 case DBI_TYPE_STRING:
1164 field_length = bsnprintf(buf, field_length + 1, "%s",
1165 dbi_result_get_string(result, field_name));
1170 case DBI_TYPE_BINARY:
1171 /* dbi_result_get_binary return a NULL pointer if value is empty
1172 * following, change this to what Bacula espected
1175 field_length = bsnprintf(buf, field_length + 1, "%s",
1176 dbi_result_get_binary(result, field_name));
1181 case DBI_TYPE_DATETIME:
1185 last = dbi_result_get_datetime(result, field_name);
1188 field_length = bsnprintf(buf, 20, "0000-00-00 00:00:00");
1190 (void)localtime_r(&last, &tm);
1191 field_length = bsnprintf(buf, 20, "%04d-%02d-%02d %02d:%02d:%02d",
1192 (tm.tm_year + 1900), (tm.tm_mon + 1), tm.tm_mday,
1193 tm.tm_hour, tm.tm_min, tm.tm_sec);
1199 dbi_conn_error(dbi_result_get_conn(result), &errmsg);
1200 Dmsg1(500, "my_dbi_getvalue error: %s\n", errmsg);
1203 Dmsg3(500, "my_dbi_getvalue finish buffer: '%p' num bytes: '%d' data: '%s'\n",
1204 buf, field_length, buf);
1206 // don't worry about this buf
1210 int my_dbi_sql_insert_id(B_DB *mdb, char *table_name)
1213 Obtain the current value of the sequence that
1214 provides the serial value for primary key of the table.
1216 currval is local to our session. It is not affected by
1219 Determine the name of the sequence.
1220 PostgreSQL automatically creates a sequence using
1221 <table>_<column>_seq.
1222 At the time of writing, all tables used this format for
1223 for their primary key: <table>id
1224 Except for basefiles which has a primary key on baseid.
1225 Therefore, we need to special case that one table.
1227 everything else can use the PostgreSQL formula.
1233 if (mdb->db_type == SQL_TYPE_POSTGRESQL) {
1235 if (strcasecmp(table_name, "basefiles") == 0) {
1236 bstrncpy(sequence, "basefiles_baseid", sizeof(sequence));
1238 bstrncpy(sequence, table_name, sizeof(sequence));
1239 bstrncat(sequence, "_", sizeof(sequence));
1240 bstrncat(sequence, table_name, sizeof(sequence));
1241 bstrncat(sequence, "id", sizeof(sequence));
1244 bstrncat(sequence, "_seq", sizeof(sequence));
1245 id = dbi_conn_sequence_last(mdb->db, NT_(sequence));
1247 id = dbi_conn_sequence_last(mdb->db, NT_(table_name));
1253 #ifdef HAVE_BATCH_FILE_INSERT
1254 const char *my_dbi_batch_lock_path_query[4] = {
1256 "LOCK TABLES Path write, batch write, Path as p write",
1258 "BEGIN; LOCK TABLE Path IN SHARE ROW EXCLUSIVE MODE",
1264 const char *my_dbi_batch_lock_filename_query[4] = {
1266 "LOCK TABLES Filename write, batch write, Filename as f write",
1268 "BEGIN; LOCK TABLE Filename IN SHARE ROW EXCLUSIVE MODE",
1274 const char *my_dbi_batch_unlock_tables_query[4] = {
1284 const char *my_dbi_match[4] = {
1295 const char *my_dbi_batch_fill_path_query[4] = {
1297 "INSERT INTO Path (Path) "
1298 "SELECT a.Path FROM "
1299 "(SELECT DISTINCT Path FROM batch) AS a WHERE NOT EXISTS "
1300 "(SELECT Path FROM Path AS p WHERE p.Path = a.Path)",
1302 "INSERT INTO Path (Path) "
1303 "SELECT a.Path FROM "
1304 "(SELECT DISTINCT Path FROM batch) AS a "
1305 "WHERE NOT EXISTS (SELECT Path FROM Path WHERE Path = a.Path) ",
1307 "INSERT INTO Path (Path)"
1308 " SELECT DISTINCT Path FROM batch"
1309 " EXCEPT SELECT Path FROM Path",
1311 "INSERT INTO Path (Path)"
1312 " SELECT DISTINCT Path FROM batch"
1313 " EXCEPT SELECT Path FROM Path"};
1315 const char *my_dbi_batch_fill_filename_query[4] = {
1317 "INSERT INTO Filename (Name) "
1318 "SELECT a.Name FROM "
1319 "(SELECT DISTINCT Name FROM batch) AS a WHERE NOT EXISTS "
1320 "(SELECT Name FROM Filename AS f WHERE f.Name = a.Name)",
1322 "INSERT INTO Filename (Name) "
1323 "SELECT a.Name FROM "
1324 "(SELECT DISTINCT Name FROM batch) as a "
1326 "(SELECT Name FROM Filename WHERE Name = a.Name)",
1328 "INSERT INTO Filename (Name)"
1329 " SELECT DISTINCT Name FROM batch "
1330 " EXCEPT SELECT Name FROM Filename",
1332 "INSERT INTO Filename (Name)"
1333 " SELECT DISTINCT Name FROM batch "
1334 " EXCEPT SELECT Name FROM Filename"};
1336 #endif /* HAVE_BATCH_FILE_INSERT */
1338 #endif /* HAVE_DBI */