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,"pgsql", sizeof(db_driver));
108 /* Set db_driverdir whereis is the libdbi drivers */
109 bstrncpy(db_driverdir, DBI_DRIVER_DIR, 255);
112 Jmsg(jcr, M_FATAL, 0, _("A user name for DBI must be supplied.\n"));
115 P(mutex); /* lock DB queue */
116 if (!mult_db_connections) {
117 /* Look to see if DB already open */
118 for (mdb=NULL; (mdb=(B_DB *)qnext(&db_list, &mdb->bq)); ) {
119 if (bstrcmp(mdb->db_name, db_name) &&
120 bstrcmp(mdb->db_address, db_address) &&
121 bstrcmp(mdb->db_driver, db_driver) &&
122 mdb->db_port == db_port) {
123 Dmsg4(100, "DB REopen %d %s %s erro: %d\n", mdb->ref_count, db_driver, db_name,
124 dbi_conn_error(mdb->db, NULL));
127 return mdb; /* already open */
131 Dmsg0(100, "db_open first time\n");
132 mdb = (B_DB *)malloc(sizeof(B_DB));
133 memset(mdb, 0, sizeof(B_DB));
134 mdb->db_name = bstrdup(db_name);
135 mdb->db_user = bstrdup(db_user);
137 mdb->db_password = bstrdup(db_password);
140 mdb->db_address = bstrdup(db_address);
143 mdb->db_socket = bstrdup(db_socket);
146 mdb->db_driverdir = bstrdup(db_driverdir);
149 mdb->db_driver = bstrdup(db_driver);
151 mdb->db_type = db_type;
152 mdb->db_port = db_port;
153 mdb->have_insert_id = TRUE;
154 mdb->errmsg = get_pool_memory(PM_EMSG); /* get error message buffer */
156 mdb->cmd = get_pool_memory(PM_EMSG); /* get command buffer */
157 mdb->cached_path = get_pool_memory(PM_FNAME);
158 mdb->cached_path_id = 0;
160 mdb->fname = get_pool_memory(PM_FNAME);
161 mdb->path = get_pool_memory(PM_FNAME);
162 mdb->esc_name = get_pool_memory(PM_FNAME);
163 mdb->esc_path = get_pool_memory(PM_FNAME);
164 mdb->allow_transactions = mult_db_connections;
165 qinsert(&db_list, &mdb->bq); /* put db in list */
171 * Now actually open the database. This can generate errors,
172 * which are returned in the errmsg
174 * DO NOT close the database or free(mdb) here !!!!
177 db_open_database(JCR *jcr, B_DB *mdb)
186 if (mdb->connected) {
190 mdb->connected = false;
192 if ((errstat=rwl_init(&mdb->lock)) != 0) {
194 Mmsg1(&mdb->errmsg, _("Unable to initialize DB lock. ERR=%s\n"),
195 be.bstrerror(errstat));
201 bsnprintf(buf, sizeof(buf), "%d", mdb->db_port);
207 numdrivers = dbi_initialize_r(mdb->db_driverdir, &(mdb->instance));
208 if (numdrivers < 0) {
209 Mmsg2(&mdb->errmsg, _("Unable to locate the DBD drivers to DBI interface in: \n"
210 "db_driverdir=%s. It is probaly not found any drivers\n"),
211 mdb->db_driverdir,numdrivers);
215 mdb->db = (void **)dbi_conn_new_r(mdb->db_driver, mdb->instance);
216 dbi_conn_set_option(mdb->db, "host", mdb->db_address); /* default = localhost */
217 dbi_conn_set_option(mdb->db, "port", port); /* default port */
218 dbi_conn_set_option(mdb->db, "username", mdb->db_user); /* login name */
219 dbi_conn_set_option(mdb->db, "password", mdb->db_password); /* password */
220 dbi_conn_set_option(mdb->db, "dbname", mdb->db_name); /* database name */
222 /* If connection fails, try at 5 sec intervals for 30 seconds. */
223 for (int retry=0; retry < 6; retry++) {
225 dbstat = dbi_conn_connect(mdb->db);
230 dbi_conn_error(mdb->db, &errmsg);
231 Dmsg1(50, "dbi error: %s\n", errmsg);
238 Mmsg3(&mdb->errmsg, _("Unable to connect to DBI interface.\n"
239 "Type=%s Database=%s User=%s\n"
240 "It is probably not running or your password is incorrect.\n"),
241 mdb->db_driver, mdb->db_name, mdb->db_user);
246 Dmsg0(50, "dbi_real_connect done\n");
247 Dmsg3(50, "db_user=%s db_name=%s db_password=%s\n",
248 mdb->db_user, mdb->db_name,
249 mdb->db_password==NULL?"(NULL)":mdb->db_password);
251 mdb->connected = true;
253 if (!check_tables_version(jcr, mdb)) {
258 switch (mdb->db_type) {
260 /* Set connection timeout to 8 days specialy for batch mode */
261 sql_query(mdb, "SET wait_timeout=691200");
262 sql_query(mdb, "SET interactive_timeout=691200");
264 case SQL_TYPE_POSTGRESQL:
265 /* tell PostgreSQL we are using standard conforming strings
266 and avoid warnings such as:
267 WARNING: nonstandard use of \\ in a string literal
269 sql_query(mdb, "SET datestyle TO 'ISO, YMD'");
270 sql_query(mdb, "set standard_conforming_strings=on");
272 case SQL_TYPE_SQLITE:
281 db_close_database(JCR *jcr, B_DB *mdb)
286 db_end_transaction(jcr, mdb);
288 sql_free_result(mdb);
290 if (mdb->ref_count == 0) {
292 if (mdb->connected && mdb->db) {
294 dbi_shutdown_r(mdb->instance);
296 mdb->instance = NULL;
298 rwl_destroy(&mdb->lock);
299 free_pool_memory(mdb->errmsg);
300 free_pool_memory(mdb->cmd);
301 free_pool_memory(mdb->cached_path);
302 free_pool_memory(mdb->fname);
303 free_pool_memory(mdb->path);
304 free_pool_memory(mdb->esc_name);
305 free_pool_memory(mdb->esc_path);
312 if (mdb->db_password) {
313 free(mdb->db_password);
315 if (mdb->db_address) {
316 free(mdb->db_address);
318 if (mdb->db_socket) {
319 free(mdb->db_socket);
321 if (mdb->db_driverdir) {
322 free(mdb->db_driverdir);
324 if (mdb->db_driver) {
325 free(mdb->db_driver);
332 void db_thread_cleanup()
336 * Return the next unique index (auto-increment) for
337 * the given table. Return NULL on error.
340 int db_next_index(JCR *jcr, B_DB *mdb, char *table, char *index)
342 strcpy(index, "NULL");
348 * Escape strings so that DBI is happy
350 * NOTE! len is the length of the old string. Your new
351 * string must be long enough (max 2*old+1) to hold
352 * the escaped output.
354 * dbi_conn_quote_string_copy receives a pointer to pointer.
355 * We need copy the value of pointer to snew because libdbi change the
359 db_escape_string(JCR *jcr, B_DB *mdb, char *snew, char *old, int len)
367 /* correct the size of old basead in len
368 * and copy new string to inew
370 inew = (char *)malloc(sizeof(char) * len + 1);
371 bstrncpy(inew,old,len + 1);
372 /* escape the correct size of old */
373 dbi_conn_escape_string_copy(mdb->db, inew, &pnew);
375 /* copy the escaped string to snew */
376 bstrncpy(snew, pnew, 2 * len + 1);
379 Dmsg2(500, "dbi_conn_escape_string_copy %p %s\n",snew,snew);
384 * Submit a general SQL command (cmd), and for each row returned,
385 * the sqlite_handler is called with the ctx.
387 bool db_sql_query(B_DB *mdb, const char *query, DB_RESULT_HANDLER *result_handler, void *ctx)
391 Dmsg0(500, "db_sql_query started\n");
394 if (sql_query(mdb, query) != 0) {
395 Mmsg(mdb->errmsg, _("Query failed: %s: ERR=%s\n"), query, sql_strerror(mdb));
397 Dmsg0(500, "db_sql_query failed\n");
400 Dmsg0(500, "db_sql_query succeeded. checking handler\n");
402 if (result_handler != NULL) {
403 Dmsg0(500, "db_sql_query invoking handler\n");
404 if ((mdb->result = sql_store_result(mdb)) != NULL) {
405 int num_fields = sql_num_fields(mdb);
407 Dmsg0(500, "db_sql_query sql_store_result suceeded\n");
408 while ((row = sql_fetch_row(mdb)) != NULL) {
410 Dmsg0(500, "db_sql_query sql_fetch_row worked\n");
411 if (result_handler(ctx, num_fields, row))
415 sql_free_result(mdb);
420 Dmsg0(500, "db_sql_query finished\n");
427 DBI_ROW my_dbi_fetch_row(B_DB *mdb)
430 DBI_ROW row = NULL; // by default, return NULL
432 Dmsg0(500, "my_dbi_fetch_row start\n");
435 if (!mdb->row || mdb->row_size < mdb->num_fields) {
436 int num_fields = mdb->num_fields;
437 Dmsg1(500, "we have need space of %d bytes\n", sizeof(char *) * mdb->num_fields);
440 Dmsg0(500, "my_dbi_fetch_row freeing space\n");
441 Dmsg2(500, "my_dbi_free_row row: '%p' num_fields: '%d'\n", mdb->row, mdb->num_fields);
442 if (mdb->num_rows != 0) {
443 for(j = 0; j < mdb->num_fields; j++) {
444 Dmsg2(500, "my_dbi_free_row row '%p' '%d'\n", mdb->row[j], j);
452 //num_fields += 20; /* add a bit extra */
453 mdb->row = (DBI_ROW)malloc(sizeof(char *) * num_fields);
454 mdb->row_size = num_fields;
456 // now reset the row_number now that we have the space allocated
460 // if still within the result set
461 if (mdb->row_number <= mdb->num_rows) {
462 Dmsg2(500, "my_dbi_fetch_row row number '%d' is acceptable (1..%d)\n", mdb->row_number, mdb->num_rows);
463 // get each value from this row
464 for (j = 0; j < mdb->num_fields; j++) {
465 mdb->row[j] = my_dbi_getvalue(mdb->result, mdb->row_number, j);
466 // allocate space to queue row
467 mdb->field_get = (DBI_FIELD_GET *)malloc(sizeof(DBI_FIELD_GET));
468 // store the pointer in queue
469 mdb->field_get->value = mdb->row[j];
470 Dmsg4(500, "my_dbi_fetch_row row[%d] field: '%p' in queue: '%p' has value: '%s'\n",
471 j, mdb->row[j], mdb->field_get->value, mdb->row[j]);
472 // insert in queue to future free
473 qinsert(&dbi_getvalue_list, &mdb->field_get->bq);
475 // increment the row number for the next call
480 Dmsg2(500, "my_dbi_fetch_row row number '%d' is NOT acceptable (1..%d)\n", mdb->row_number, mdb->num_rows);
483 Dmsg1(500, "my_dbi_fetch_row finishes returning %p\n", row);
488 int my_dbi_max_length(B_DB *mdb, int field_num) {
490 // for a given column, find the max length
498 for (i = 0; i < mdb->num_rows; i++) {
499 if (my_dbi_getisnull(mdb->result, i, field_num)) {
500 this_length = 4; // "NULL"
502 cbuf = my_dbi_getvalue(mdb->result, i, field_num);
503 this_length = cstrlen(cbuf);
504 // cbuf is always free
508 if (max_length < this_length) {
509 max_length = this_length;
516 DBI_FIELD * my_dbi_fetch_field(B_DB *mdb)
521 Dmsg0(500, "my_dbi_fetch_field starts\n");
523 if (!mdb->fields || mdb->fields_size < mdb->num_fields) {
527 Dmsg1(500, "allocating space for %d fields\n", mdb->num_fields);
528 mdb->fields = (DBI_FIELD *)malloc(sizeof(DBI_FIELD) * mdb->num_fields);
529 mdb->fields_size = mdb->num_fields;
531 for (i = 0; i < mdb->num_fields; i++) {
532 // num_fileds is starting at 1, increment i by 1
534 Dmsg1(500, "filling field %d\n", i);
535 mdb->fields[i].name = (char *)dbi_result_get_field_name(mdb->result, dbi_index);
536 mdb->fields[i].max_length = my_dbi_max_length(mdb, i);
537 mdb->fields[i].type = dbi_result_get_field_type_idx(mdb->result, dbi_index);
538 mdb->fields[i].flags = dbi_result_get_field_attribs_idx(mdb->result, dbi_index);
540 Dmsg4(500, "my_dbi_fetch_field finds field '%s' has length='%d' type='%d' and IsNull=%d\n",
541 mdb->fields[i].name, mdb->fields[i].max_length, mdb->fields[i].type,
542 mdb->fields[i].flags);
546 // increment field number for the next time around
548 Dmsg0(500, "my_dbi_fetch_field finishes\n");
549 return &mdb->fields[mdb->field_number++];
552 void my_dbi_data_seek(B_DB *mdb, int row)
554 // set the row number to be returned on the next call
555 // to my_dbi_fetch_row
556 mdb->row_number = row;
559 void my_dbi_field_seek(B_DB *mdb, int field)
561 mdb->field_number = field;
565 * Note, if this routine returns 1 (failure), Bacula expects
566 * that no result has been stored.
568 * Returns: 0 on success
572 int my_dbi_query(B_DB *mdb, const char *query)
575 Dmsg1(500, "my_dbi_query started %s\n", query);
576 // We are starting a new query. reset everything.
578 mdb->row_number = -1;
579 mdb->field_number = -1;
582 dbi_result_free(mdb->result); /* hmm, someone forgot to free?? */
586 mdb->result = (void **)dbi_conn_query(mdb->db, query);
589 Dmsg2(50, "Query failed: %s %p\n", query, mdb->result);
593 mdb->status = (dbi_error_flag) dbi_conn_error(mdb->db, &errmsg);
595 if (mdb->status == DBI_ERROR_NONE) {
596 Dmsg1(500, "we have a result\n", query);
598 // how many fields in the set?
599 // num_fields starting at 1
600 mdb->num_fields = dbi_result_get_numfields(mdb->result);
601 Dmsg1(500, "we have %d fields\n", mdb->num_fields);
602 // if no result num_rows is 0
603 mdb->num_rows = dbi_result_get_numrows(mdb->result);
604 Dmsg1(500, "we have %d rows\n", mdb->num_rows);
606 mdb->status = (dbi_error_flag) 0; /* succeed */
608 Dmsg1(50, "Result status failed: %s\n", query);
612 Dmsg0(500, "my_dbi_query finishing\n");
616 mdb->status = (dbi_error_flag) dbi_conn_error(mdb->db,&errmsg);
617 //dbi_conn_error(mdb->db, &errmsg);
618 Dmsg4(500, "my_dbi_query we failed dbi error: "
619 "'%s' '%p' '%d' flag '%d''\n", errmsg, mdb->result, mdb->result, mdb->status);
620 dbi_result_free(mdb->result);
622 mdb->status = (dbi_error_flag) 1; /* failed */
626 void my_dbi_free_result(B_DB *mdb)
633 Dmsg1(500, "my_dbi_free_result result '%p'\n", mdb->result);
634 dbi_result_free(mdb->result);
643 /* now is time to free all value return by my_dbi_get_value
644 * this is necessary because libdbi don't free memory return by yours results
645 * and Bacula has some routine wich call more than once time my_dbi_fetch_row
647 * Using a queue to store all pointer allocate is a good way to free all things
650 while((f=(DBI_FIELD_GET *)qremove(&dbi_getvalue_list))) {
651 Dmsg2(500, "my_dbi_free_result field value: '%p' in queue: '%p'\n", f->value, f);
663 Dmsg0(500, "my_dbi_free_result finish\n");
667 const char *my_dbi_strerror(B_DB *mdb)
671 dbi_conn_error(mdb->db, &errmsg);
676 #ifdef HAVE_BATCH_FILE_INSERT
679 * This can be a bit strang but is the one way to do
684 int my_dbi_batch_start(JCR *jcr, B_DB *mdb)
686 char *query = "COPY batch FROM STDIN";
688 Dmsg0(500, "my_dbi_batch_start started\n");
690 switch (mdb->db_type) {
693 if (my_dbi_query(mdb,
694 "CREATE TEMPORARY TABLE batch ("
700 "MD5 tinyblob)") == 1)
702 Dmsg0(500, "my_dbi_batch_start failed\n");
706 Dmsg0(500, "my_dbi_batch_start finishing\n");
709 case SQL_TYPE_POSTGRESQL:
711 //query = "COPY batch FROM STDIN";
713 if (my_dbi_query(mdb,
714 "CREATE TEMPORARY TABLE batch ("
720 "md5 varchar)") == 1)
722 Dmsg0(500, "my_dbi_batch_start failed\n");
726 // We are starting a new query. reset everything.
728 mdb->row_number = -1;
729 mdb->field_number = -1;
731 my_dbi_free_result(mdb);
733 for (int i=0; i < 10; i++) {
734 my_dbi_query(mdb, query);
741 Dmsg1(50, "Query failed: %s\n", query);
745 mdb->status = (dbi_error_flag)dbi_conn_error(mdb->db, NULL);
746 //mdb->status = DBI_ERROR_NONE;
748 if (mdb->status == DBI_ERROR_NONE) {
749 // how many fields in the set?
750 mdb->num_fields = dbi_result_get_numfields(mdb->result);
751 mdb->num_rows = dbi_result_get_numrows(mdb->result);
752 mdb->status = (dbi_error_flag) 1;
754 Dmsg1(50, "Result status failed: %s\n", query);
758 Dmsg0(500, "my_postgresql_batch_start finishing\n");
762 case SQL_TYPE_SQLITE:
764 if (my_dbi_query(mdb,
765 "CREATE TEMPORARY TABLE batch ("
771 "MD5 tinyblob)") == 1)
773 Dmsg0(500, "my_dbi_batch_start failed\n");
777 Dmsg0(500, "my_dbi_batch_start finishing\n");
783 Mmsg1(&mdb->errmsg, _("error starting batch mode: %s"), my_dbi_strerror(mdb));
784 mdb->status = (dbi_error_flag) 0;
785 my_dbi_free_result(mdb);
790 /* set error to something to abort operation */
791 int my_dbi_batch_end(JCR *jcr, B_DB *mdb, const char *error)
795 int (*custom_function)(void*, const char*) = NULL;
796 dbi_conn_t *myconn = (dbi_conn_t *)(mdb->db);
798 Dmsg0(500, "my_dbi_batch_end started\n");
800 if (!mdb) { /* no files ? */
804 switch (mdb->db_type) {
807 mdb->status = (dbi_error_flag) 0;
810 case SQL_TYPE_POSTGRESQL:
811 custom_function = (custom_function_end_t)dbi_driver_specific_function(dbi_conn_get_driver(mdb->db), "PQputCopyEnd");
815 res = (*custom_function)(myconn->connection, error);
816 } while (res == 0 && --count > 0);
820 mdb->status = (dbi_error_flag) 1;
824 Dmsg0(500, "we failed\n");
825 mdb->status = (dbi_error_flag) 0;
826 //Mmsg1(&mdb->errmsg, _("error ending batch mode: %s"), PQerrorMessage(mdb->db));
829 case SQL_TYPE_SQLITE:
831 mdb->status = (dbi_error_flag) 0;
836 Dmsg0(500, "my_dbi_batch_end finishing\n");
842 * This function is big and use a big switch.
843 * In near future is better split in small functions
847 int my_dbi_batch_insert(JCR *jcr, B_DB *mdb, ATTR_DBR *ar)
851 dbi_conn_t *myconn = (dbi_conn_t *)(mdb->db);
852 int (*custom_function)(void*, const char*, int) = NULL;
853 char* (*custom_function_error)(void*) = NULL;
858 Dmsg0(500, "my_dbi_batch_insert started \n");
860 mdb->esc_name = check_pool_memory_size(mdb->esc_name, mdb->fnl*2+1);
861 mdb->esc_path = check_pool_memory_size(mdb->esc_path, mdb->pnl*2+1);
863 if (ar->Digest == NULL || ar->Digest[0] == 0) {
869 switch (mdb->db_type) {
871 db_escape_string(jcr, mdb, mdb->esc_name, mdb->fname, mdb->fnl);
872 db_escape_string(jcr, mdb, mdb->esc_path, mdb->path, mdb->pnl);
873 len = Mmsg(mdb->cmd, "INSERT INTO batch VALUES (%u,%s,'%s','%s','%s','%s')",
874 ar->FileIndex, edit_int64(ar->JobId,ed1), mdb->esc_path,
875 mdb->esc_name, ar->attr, digest);
877 if (my_dbi_query(mdb,mdb->cmd) == 1)
879 Dmsg0(500, "my_dbi_batch_insert failed\n");
883 Dmsg0(500, "my_dbi_batch_insert finishing\n");
887 case SQL_TYPE_POSTGRESQL:
888 my_postgresql_copy_escape(mdb->esc_name, mdb->fname, mdb->fnl);
889 my_postgresql_copy_escape(mdb->esc_path, mdb->path, mdb->pnl);
890 len = Mmsg(mdb->cmd, "%u\t%s\t%s\t%s\t%s\t%s\n",
891 ar->FileIndex, edit_int64(ar->JobId, ed1), mdb->esc_path,
892 mdb->esc_name, ar->attr, digest);
894 /* libdbi don't support CopyData and we need call a postgresql
895 * specific function to do this work
897 Dmsg2(500, "my_dbi_batch_insert :\n %s \ncmd_size: %d",mdb->cmd, len);
898 if ((custom_function = (custom_function_insert_t)dbi_driver_specific_function(dbi_conn_get_driver(mdb->db),
899 "PQputCopyData")) != NULL) {
901 res = (*custom_function)(myconn->connection, mdb->cmd, len);
902 } while (res == 0 && --count > 0);
907 mdb->status = (dbi_error_flag) 1;
911 Dmsg0(500, "my_dbi_batch_insert failed\n");
915 Dmsg0(500, "my_dbi_batch_insert finishing\n");
918 // ensure to detect a PQerror
919 custom_function_error = (custom_function_error_t)dbi_driver_specific_function(dbi_conn_get_driver(mdb->db), "PQerrorMessage");
920 Dmsg1(500, "my_dbi_batch_insert failed\n PQerrorMessage: %s", (*custom_function_error)(myconn->connection));
924 case SQL_TYPE_SQLITE:
925 db_escape_string(jcr, mdb, mdb->esc_name, mdb->fname, mdb->fnl);
926 db_escape_string(jcr, mdb, mdb->esc_path, mdb->path, mdb->pnl);
927 len = Mmsg(mdb->cmd, "INSERT INTO batch VALUES (%u,%s,'%s','%s','%s','%s')",
928 ar->FileIndex, edit_int64(ar->JobId,ed1), mdb->esc_path,
929 mdb->esc_name, ar->attr, digest);
930 if (my_dbi_query(mdb,mdb->cmd) == 1)
932 Dmsg0(500, "my_dbi_batch_insert failed\n");
936 Dmsg0(500, "my_dbi_batch_insert finishing\n");
943 Mmsg1(&mdb->errmsg, _("error inserting batch mode: %s"), my_dbi_strerror(mdb));
944 mdb->status = (dbi_error_flag) 0;
945 my_dbi_free_result(mdb);
950 * Escape strings so that PostgreSQL is happy on COPY
952 * NOTE! len is the length of the old string. Your new
953 * string must be long enough (max 2*old+1) to hold
954 * the escaped output.
956 char *my_postgresql_copy_escape(char *dest, char *src, size_t len)
958 /* we have to escape \t, \n, \r, \ */
961 while (len > 0 && *src) {
996 #endif /* HAVE_BATCH_FILE_INSERT */
1000 * int PQgetisnull(const PGresult *res,
1002 * int column_number);
1004 * use dbi_result_seek_row to search in result set
1006 int my_dbi_getisnull(dbi_result *result, int row_number, int column_number) {
1009 if(row_number == 0) {
1015 if(dbi_result_seek_row(result, row_number)) {
1017 i = dbi_result_field_is_null_idx(result,column_number);
1028 * char *PQgetvalue(const PGresult *res,
1030 * int column_number);
1032 * use dbi_result_seek_row to search in result set
1033 * use example to return only strings
1035 char *my_dbi_getvalue(dbi_result *result, int row_number, unsigned int column_number) {
1039 const char *field_name;
1040 unsigned short dbitype;
1041 size_t field_length;
1044 /* correct the index for dbi interface
1045 * dbi index begins 1
1046 * I prefer do not change others functions
1048 Dmsg3(600, "my_dbi_getvalue pre-starting result '%p' row number '%d' column number '%d'\n",
1049 result, row_number, column_number);
1053 if(row_number == 0) {
1057 Dmsg3(600, "my_dbi_getvalue starting result '%p' row number '%d' column number '%d'\n",
1058 result, row_number, column_number);
1060 if(dbi_result_seek_row(result, row_number)) {
1062 field_name = dbi_result_get_field_name(result, column_number);
1063 field_length = dbi_result_get_field_length(result, field_name);
1064 dbitype = dbi_result_get_field_type_idx(result,column_number);
1066 Dmsg3(500, "my_dbi_getvalue start: type: '%d' "
1067 "field_length bytes: '%d' fieldname: '%s'\n",
1068 dbitype, field_length, field_name);
1071 //buf = (char *)malloc(sizeof(char *) * field_length + 1);
1072 buf = (char *)malloc(field_length + 1);
1075 buf = (char *)malloc(sizeof(char *) * 50);
1079 case DBI_TYPE_INTEGER:
1080 num = dbi_result_get_longlong(result, field_name);
1081 edit_int64(num, buf);
1082 field_length = strlen(buf);
1084 case DBI_TYPE_STRING:
1086 field_length = bsnprintf(buf, field_length + 1, "%s",
1087 dbi_result_get_string(result, field_name));
1092 case DBI_TYPE_BINARY:
1093 /* dbi_result_get_binary return a NULL pointer if value is empty
1094 * following, change this to what Bacula espected
1097 field_length = bsnprintf(buf, field_length + 1, "%s",
1098 dbi_result_get_binary(result, field_name));
1103 case DBI_TYPE_DATETIME:
1107 last = dbi_result_get_datetime(result, field_name);
1110 field_length = bsnprintf(buf, 20, "0000-00-00 00:00:00");
1112 (void)localtime_r(&last, &tm);
1113 field_length = bsnprintf(buf, 20, "%04d-%02d-%02d %02d:%02d:%02d",
1114 (tm.tm_year + 1900), (tm.tm_mon + 1), tm.tm_mday,
1115 tm.tm_hour, tm.tm_min, tm.tm_sec);
1121 dbi_conn_error(dbi_result_get_conn(result), &errmsg);
1122 Dmsg1(500, "my_dbi_getvalue error: %s\n", errmsg);
1125 Dmsg3(500, "my_dbi_getvalue finish buffer: '%p' num bytes: '%d' data: '%s'\n",
1126 buf, field_length, buf);
1128 // don't worry about this buf
1132 int my_dbi_sql_insert_id(B_DB *mdb, char *table_name)
1135 Obtain the current value of the sequence that
1136 provides the serial value for primary key of the table.
1138 currval is local to our session. It is not affected by
1141 Determine the name of the sequence.
1142 PostgreSQL automatically creates a sequence using
1143 <table>_<column>_seq.
1144 At the time of writing, all tables used this format for
1145 for their primary key: <table>id
1146 Except for basefiles which has a primary key on baseid.
1147 Therefore, we need to special case that one table.
1149 everything else can use the PostgreSQL formula.
1155 if (mdb->db_type == SQL_TYPE_POSTGRESQL) {
1157 if (strcasecmp(table_name, "basefiles") == 0) {
1158 bstrncpy(sequence, "basefiles_baseid", sizeof(sequence));
1160 bstrncpy(sequence, table_name, sizeof(sequence));
1161 bstrncat(sequence, "_", sizeof(sequence));
1162 bstrncat(sequence, table_name, sizeof(sequence));
1163 bstrncat(sequence, "id", sizeof(sequence));
1166 bstrncat(sequence, "_seq", sizeof(sequence));
1167 id = dbi_conn_sequence_last(mdb->db, NT_(sequence));
1169 id = dbi_conn_sequence_last(mdb->db, NT_(table_name));
1175 #ifdef HAVE_BATCH_FILE_INSERT
1176 const char *my_dbi_batch_lock_path_query[3] = {
1178 "LOCK TABLES Path write, batch write, Path as p write",
1180 "BEGIN; LOCK TABLE Path IN SHARE ROW EXCLUSIVE MODE",
1184 const char *my_dbi_batch_lock_filename_query[3] = {
1186 "LOCK TABLES Filename write, batch write, Filename as f write",
1188 "BEGIN; LOCK TABLE Filename IN SHARE ROW EXCLUSIVE MODE",
1192 const char *my_dbi_batch_unlock_tables_query[3] = {
1200 const char *my_dbi_batch_fill_path_query[3] = {
1202 "INSERT INTO Path (Path) "
1203 "SELECT a.Path FROM "
1204 "(SELECT DISTINCT Path FROM batch) AS a WHERE NOT EXISTS "
1205 "(SELECT Path FROM Path AS p WHERE p.Path = a.Path)",
1207 "INSERT INTO Path (Path) "
1208 "SELECT a.Path FROM "
1209 "(SELECT DISTINCT Path FROM batch) AS a "
1210 "WHERE NOT EXISTS (SELECT Path FROM Path WHERE Path = a.Path) ",
1212 "INSERT INTO Path (Path)"
1213 " SELECT DISTINCT Path FROM batch"
1214 " EXCEPT SELECT Path FROM Path"};
1216 const char *my_dbi_batch_fill_filename_query[3] = {
1218 "INSERT INTO Filename (Name) "
1219 "SELECT a.Name FROM "
1220 "(SELECT DISTINCT Name FROM batch) AS a WHERE NOT EXISTS "
1221 "(SELECT Name FROM Filename AS f WHERE f.Name = a.Name)",
1223 "INSERT INTO Filename (Name) "
1224 "SELECT a.Name FROM "
1225 "(SELECT DISTINCT Name FROM batch) as a "
1227 "(SELECT Name FROM Filename WHERE Name = a.Name)",
1229 "INSERT INTO Filename (Name)"
1230 " SELECT DISTINCT Name FROM batch "
1231 " EXCEPT SELECT Name FROM Filename"};
1232 #endif /* HAVE_BATCH_FILE_INSERT */
1234 #endif /* HAVE_DBI */