2 Bacula® - The Network Backup Solution
4 Copyright (C) 2003-2010 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 Ingres
30 * These are Ingres specific routines
32 * Stefan Reddig, June 2009 with help of Marco van Wieringen April 2010
33 * based uopn work done
34 * by Dan Langille, December 2003 and
35 * 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 */
52 /* -----------------------------------------------------------------------
54 * Ingres dependent defines and subroutines
56 * -----------------------------------------------------------------------
59 /* List of open databases */
60 static BQUEUE db_list = {&db_list, &db_list};
62 static pthread_mutex_t mutex = PTHREAD_MUTEX_INITIALIZER;
65 * Retrieve database type
74 * Initialize database data structure. In principal this should
75 * never have errors, or it is really fatal.
78 db_init_database(JCR *jcr, const char *db_name, const char *db_user, const char *db_password,
79 const char *db_address, int db_port, const char *db_socket,
80 int mult_db_connections)
83 int next_session_id = 0;
86 Jmsg(jcr, M_FATAL, 0, _("A user name for Ingres must be supplied.\n"));
89 P(mutex); /* lock DB queue */
90 if (!mult_db_connections) {
91 /* Look to see if DB already open */
92 for (mdb=NULL; (mdb=(B_DB *)qnext(&db_list, &mdb->bq)); ) {
93 if (bstrcmp(mdb->db_name, db_name) &&
94 bstrcmp(mdb->db_address, db_address) &&
95 mdb->db_port == db_port) {
96 Dmsg2(100, "DB REopen %d %s\n", mdb->ref_count, db_name);
99 return mdb; /* already open */
102 if (mdb->session_id > next_session_id) {
103 next_session_id = mdb->session_id;
108 * See what the next available session_id is.
109 * We first see what the highest session_id is used now.
111 foreach_dlist(mdb, db_list) {
112 if (mdb->session_id > next_session_id) {
113 next_session_id = mdb->session_id;
117 Dmsg0(100, "db_open first time\n");
118 mdb = (B_DB *)malloc(sizeof(B_DB));
119 memset(mdb, 0, sizeof(B_DB));
120 mdb->db_name = bstrdup(db_name);
121 mdb->db_user = bstrdup(db_user);
123 mdb->db_password = bstrdup(db_password);
126 mdb->db_address = bstrdup(db_address);
129 mdb->db_socket = bstrdup(db_socket);
131 mdb->db_port = db_port;
132 mdb->session_id = ++next_session_id;
133 mdb->have_insert_id = TRUE;
134 mdb->errmsg = get_pool_memory(PM_EMSG); /* get error message buffer */
136 mdb->cmd = get_pool_memory(PM_EMSG); /* get command buffer */
137 mdb->cached_path = get_pool_memory(PM_FNAME);
138 mdb->cached_path_id = 0;
140 mdb->fname = get_pool_memory(PM_FNAME);
141 mdb->path = get_pool_memory(PM_FNAME);
142 mdb->esc_name = get_pool_memory(PM_FNAME);
143 mdb->esc_path = get_pool_memory(PM_FNAME);
144 mdb->allow_transactions = mult_db_connections;
145 mdb->limit_filter = new_bregexp("/LIMIT ([0-9]+)/FETCH FIRST $1 ROW ONLY/g");
146 qinsert(&db_list, &mdb->bq); /* put db in list */
151 /* Check that the database correspond to the encoding we want */
152 static bool check_database_encoding(JCR *jcr, B_DB *mdb)
154 /* SRE: TODO! Needed?
158 if (!db_sql_query(mdb, "SELECT getdatabaseencoding()", NULL, NULL)) {
159 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
163 if ((row = sql_fetch_row(mdb)) == NULL) {
164 Mmsg1(mdb->errmsg, _("error fetching row: %s\n"), sql_strerror(mdb));
165 Jmsg(jcr, M_ERROR, 0, "Can't check database encoding %s", mdb->errmsg);
167 ret = bstrcmp(row[0], "SQL_ASCII");
170 _("Encoding error for database \"%s\". Wanted SQL_ASCII, got %s\n"),
171 mdb->db_name, row[0]);
172 Jmsg(jcr, M_WARNING, 0, "%s", mdb->errmsg);
173 Dmsg1(50, "%s", mdb->errmsg);
182 * Now actually open the database. This can generate errors,
183 * which are returned in the errmsg
185 * DO NOT close the database or free(mdb) here !!!!
188 db_open_database(JCR *jcr, B_DB *mdb)
194 if (mdb->connected) {
198 mdb->connected = false;
200 if ((errstat=rwl_init(&mdb->lock)) != 0) {
202 Mmsg1(&mdb->errmsg, _("Unable to initialize DB lock. ERR=%s\n"),
203 be.bstrerror(errstat));
209 bsnprintf(buf, sizeof(buf), "%d", mdb->db_port);
215 mdb->db = INGconnectDB(mdb->db_name, mdb->db_user, mdb->db_password, mdb->session_id);
217 Dmsg0(50, "Ingres real CONNECT done\n");
218 Dmsg3(50, "db_user=%s db_name=%s db_password=%s\n", mdb->db_user, mdb->db_name,
219 mdb->db_password==NULL?"(NULL)":mdb->db_password);
222 Mmsg2(&mdb->errmsg, _("Unable to connect to Ingres server.\n"
223 "Database=%s User=%s\n"
224 "It is probably not running or your password is incorrect.\n"),
225 mdb->db_name, mdb->db_user);
230 mdb->connected = true;
232 if (!check_tables_version(jcr, mdb)) {
237 //sql_query(mdb, "SET datestyle TO 'ISO, YMD'");
239 /* check that encoding is SQL_ASCII */
240 check_database_encoding(jcr, mdb);
247 db_close_database(JCR *jcr, B_DB *mdb)
252 db_end_transaction(jcr, mdb);
254 sql_free_result(mdb);
256 if (mdb->ref_count == 0) {
258 if (mdb->connected && mdb->db) {
261 rwl_destroy(&mdb->lock);
262 free_pool_memory(mdb->errmsg);
263 free_pool_memory(mdb->cmd);
264 free_pool_memory(mdb->cached_path);
265 free_pool_memory(mdb->fname);
266 free_pool_memory(mdb->path);
267 free_pool_memory(mdb->esc_name);
268 free_pool_memory(mdb->esc_path);
269 free_bregexp(mdb->limit_filter);
276 if (mdb->db_password) {
277 free(mdb->db_password);
279 if (mdb->db_address) {
280 free(mdb->db_address);
282 if (mdb->db_socket) {
283 free(mdb->db_socket);
290 void db_check_backend_thread_safe()
295 void db_thread_cleanup()
299 * Return the next unique index (auto-increment) for
300 * the given table. Return NULL on error.
302 * For Ingres, NULL causes the auto-increment value SRE: true?
305 int db_next_index(JCR *jcr, B_DB *mdb, char *table, char *index)
307 strcpy(index, "NULL");
313 * Escape strings so that Ingres is happy
315 * NOTE! len is the length of the old string. Your new
316 * string must be long enough (max 2*old+1) to hold
317 * the escaped output.
320 db_escape_string(JCR *jcr, B_DB *mdb, char *snew, char *old, int len)
347 * Submit a general SQL command (cmd), and for each row returned,
348 * the sqlite_handler is called with the ctx.
350 bool db_sql_query(B_DB *mdb, const char *query, DB_RESULT_HANDLER *result_handler, void *ctx)
354 Dmsg0(500, "db_sql_query started\n");
357 if (sql_query(mdb, query) != 0) {
358 Mmsg(mdb->errmsg, _("Query failed: %s: ERR=%s\n"), query, sql_strerror(mdb));
360 Dmsg0(500, "db_sql_query failed\n");
363 Dmsg0(500, "db_sql_query succeeded. checking handler\n");
365 if (result_handler != NULL) {
366 Dmsg0(500, "db_sql_query invoking handler\n");
367 if (mdb->result != NULL) {
368 int num_fields = sql_num_fields(mdb);
370 Dmsg0(500, "db_sql_query sql_store_result suceeded\n");
371 while ((row = sql_fetch_row(mdb)) != NULL) {
373 Dmsg0(500, "db_sql_query sql_fetch_row worked\n");
374 if (result_handler(ctx, num_fields, row))
378 sql_free_result(mdb);
383 Dmsg0(500, "db_sql_query finished\n");
389 * Close database connection
391 void my_ingres_close(B_DB *mdb)
393 Dmsg0(500, "my_ingres_close closing database connection\n");
394 INGdisconnectDB(mdb->db);
395 //SRE: error handling?
398 INGRES_ROW my_ingres_fetch_row(B_DB *mdb)
401 INGRES_ROW row = NULL; // by default, return NULL
406 if (mdb->result->num_rows <= 0) {
410 Dmsg0(500, "my_ingres_fetch_row start\n");
412 if (!mdb->row || mdb->row_size < mdb->num_fields) {
413 int num_fields = mdb->num_fields;
414 Dmsg1(500, "we have need space of %d bytes\n", sizeof(char *) * mdb->num_fields);
417 Dmsg0(500, "my_ingres_fetch_row freeing space\n");
420 num_fields += 20; /* add a bit extra */
421 mdb->row = (INGRES_ROW)malloc(sizeof(char *) * num_fields);
422 mdb->row_size = num_fields;
424 // now reset the row_number now that we have the space allocated
425 //mdb->row_number = 1;
429 // if still within the result set
430 //if (mdb->row_number <= mdb->num_rows) {
431 if (mdb->row_number < mdb->num_rows) {
432 Dmsg2(500, "my_ingres_fetch_row row number '%d' is acceptable (0..%d)\n", mdb->row_number, mdb->num_rows);
433 // get each value from this row
434 for (j = 0; j < mdb->num_fields; j++) {
435 mdb->row[j] = INGgetvalue(mdb->result, mdb->row_number, j);
436 Dmsg2(500, "my_ingres_fetch_row field '%d' has value '%s'\n", j, mdb->row[j]);
438 // increment the row number for the next call
443 Dmsg2(500, "my_ingres_fetch_row row number '%d' is NOT acceptable (0..%d)\n", mdb->row_number, mdb->num_rows);
446 Dmsg1(500, "my_ingres_fetch_row finishes returning %p\n", row);
452 int my_ingres_max_length(B_DB *mdb, int field_num) {
454 // for a given column, find the max length
461 for (i = 0; i < mdb->num_rows; i++) {
462 if (INGgetisnull(mdb->result, i, field_num)) {
463 this_length = 4; // "NULL"
465 this_length = cstrlen(INGgetvalue(mdb->result, i, field_num));
468 if (max_length < this_length) {
469 max_length = this_length;
476 INGRES_FIELD * my_ingres_fetch_field(B_DB *mdb)
480 Dmsg0(500, "my_ingres_fetch_field starts\n");
482 if (!mdb->fields || mdb->fields_size < mdb->num_fields) {
486 Dmsg1(500, "allocating space for %d fields\n", mdb->num_fields);
487 mdb->fields = (INGRES_FIELD *)malloc(sizeof(INGRES_FIELD) * mdb->num_fields);
488 mdb->fields_size = mdb->num_fields;
490 for (i = 0; i < mdb->num_fields; i++) {
491 Dmsg1(500, "filling field %d\n", i);
492 strcpy(mdb->fields[i].name,INGfname(mdb->result, i));
493 mdb->fields[i].max_length = my_ingres_max_length(mdb, i);
494 mdb->fields[i].type = INGftype(mdb->result, i);
495 mdb->fields[i].flags = 0;
497 Dmsg4(500, "my_ingres_fetch_field finds field '%s' has length='%d' type='%d' and IsNull=%d\n",
498 mdb->fields[i].name, mdb->fields[i].max_length, mdb->fields[i].type,
499 mdb->fields[i].flags);
503 // increment field number for the next time around
505 Dmsg0(500, "my_ingres_fetch_field finishes\n");
506 return &mdb->fields[mdb->field_number++];
509 void my_ingres_data_seek(B_DB *mdb, int row)
511 // set the row number to be returned on the next call
512 // to my_ingres_fetch_row
513 mdb->row_number = row;
516 void my_ingres_field_seek(B_DB *mdb, int field)
518 mdb->field_number = field;
522 * Note, if this routine returns 1 (failure), Bacula expects
523 * that no result has been stored.
525 * Returns: 0 on success
529 int my_ingres_query(B_DB *mdb, const char *query)
531 const char *new_query;
533 if (strstr(query, "LIMIT") != NULL) {
534 new_query = mdb->limit_filter->replace(query);
539 Dmsg0(500, "my_ingres_query started\n");
540 // We are starting a new query. reset everything.
542 mdb->row_number = -1;
543 mdb->field_number = -1;
548 INGclear(mdb->result); /* hmm, someone forgot to free?? */
552 Dmsg1(500, "my_ingres_query starts with '%s'\n", new_query);
555 * See if we are getting a transaction start or end.
557 if (strcasecmp(new_query, "BEGIN") != NULL) {
559 * Start of a transaction.
561 Dmsg0(500,"my_ingres_query: Start of transaction\n");
562 mdb->transaction = true;
564 } else if (strcasecmp(new_query, "COMMIT") != NULL) {
566 * End of a transaction.
568 Dmsg0(500,"my_ingres_query: End of transaction, commiting work\n");
569 mdb->transaction = false;
574 /* TODO: differentiate between SELECTs and other queries */
576 if ((cols = INGgetCols(mdb->db, new_query, mdb->transaction)) <= 0) {
578 Dmsg0(500,"my_ingres_query: neg.columns: no DML stmt!\n");
580 Dmsg0(500,"my_ingres_query (non SELECT) starting...\n");
582 mdb->num_rows = INGexec(mdb->db, new_query, mdb->transaction);
584 Dmsg0(500,"my_ingres_query (non SELECT) went wrong\n");
587 Dmsg0(500,"my_ingres_query (non SELECT) seems ok\n");
592 Dmsg0(500,"my_ingres_query (SELECT) starting...\n");
593 mdb->result = INGquery(mdb->db, new_query, mdb->transaction);
594 if (mdb->result != NULL) {
595 Dmsg1(500, "we have a result\n", new_query);
597 // how many fields in the set?
598 mdb->num_fields = (int)INGnfields(mdb->result);
599 Dmsg1(500, "we have %d fields\n", mdb->num_fields);
601 mdb->num_rows = INGntuples(mdb->result);
602 Dmsg1(500, "we have %d rows\n", mdb->num_rows);
604 mdb->status = 0; /* succeed */
606 Dmsg0(500, "No resultset...\n");
607 mdb->status = 1; /* failed */
611 Dmsg0(500, "my_ingres_query finishing\n");
615 void my_ingres_free_result(B_DB *mdb)
620 INGclear(mdb->result);
636 int my_ingres_currval(B_DB *mdb, const char *table_name)
639 * Obtain the current value of the sequence that
640 * provides the serial value for primary key of the table.
642 * currval is local to our session. It is not affected by
643 * other transactions.
645 * Determine the name of the sequence.
646 * As we name all sequences as <table>_seq this is easy.
654 bstrncpy(sequence, table_name, sizeof(sequence));
655 bstrncat(sequence, "_seq", sizeof(sequence));
657 bsnprintf(query, sizeof(query), "SELECT %s.currval FROM %s", sequence, table_name);
659 Dmsg1(500, "my_ingres_currval invoked with '%s'\n", query);
661 result = INGquery(mdb->db, query, mdb->transaction);
664 Dmsg1(50, "Query failed: %s\n", query);
668 Dmsg0(500, "exec done");
670 id = atoi(INGgetvalue(result, 0, 0));
678 #ifdef HAVE_BATCH_FILE_INSERT
679 int my_ingres_batch_start(JCR *jcr, B_DB *mdb)
684 ok = db_sql_query(mdb,
685 "DECLARE GLOBAL TEMPORARY TABLE batch ("
690 "LStat varchar(256),"
692 " ON COMMIT PRESERVE ROWS WITH NORECOVERY",NULL, NULL);
697 int my_ingres_batch_end(JCR *jcr, B_DB *mdb, const char *error)
705 int my_ingres_batch_insert(JCR *jcr, B_DB *mdb, ATTR_DBR *ar)
711 mdb->esc_name = check_pool_memory_size(mdb->esc_name, mdb->fnl*2+1);
712 db_escape_string(jcr, mdb, mdb->esc_name, mdb->fname, mdb->fnl);
714 mdb->esc_path = check_pool_memory_size(mdb->esc_path, mdb->pnl*2+1);
715 db_escape_string(jcr, mdb, mdb->esc_path, mdb->path, mdb->pnl);
717 if (ar->Digest == NULL || ar->Digest[0] == 0) {
723 len = Mmsg(mdb->cmd, "INSERT INTO batch VALUES (%u,%s,'%s','%s','%s','%s')",
724 ar->FileIndex, edit_int64(ar->JobId,ed1), mdb->esc_path,
725 mdb->esc_name, ar->attr, digest);
727 return INSERT_DB(jcr, mdb, mdb->cmd);
729 #endif /* HAVE_BATCH_FILE_INSERT */
732 * Escape strings so that Ingres is happy on COPY
734 * NOTE! len is the length of the old string. Your new
735 * string must be long enough (max 2*old+1) to hold
736 * the escaped output.
738 char *my_ingres_copy_escape(char *dest, char *src, size_t len)
740 /* we have to escape \t, \n, \r, \ */
743 while (len > 0 && *src) {
778 #ifdef HAVE_BATCH_FILE_INSERT
779 const char *my_ingres_batch_lock_path_query = "BEGIN";
781 const char *my_ingres_batch_lock_filename_query = "BEGIN";
783 const char *my_ingres_batch_unlock_tables_query = "COMMIT";
785 const char *my_ingres_batch_fill_path_query =
786 "INSERT INTO Path (Path) "
787 "SELECT a.Path FROM "
788 "(SELECT DISTINCT Path FROM batch) AS a "
789 "WHERE NOT EXISTS (SELECT Path FROM Path WHERE Path = a.Path) ";
792 const char *my_ingres_batch_fill_filename_query =
793 "INSERT INTO Filename (Name) "
794 "SELECT a.Name FROM "
795 "(SELECT DISTINCT Name FROM batch) as a "
797 "(SELECT Name FROM Filename WHERE Name = a.Name)";
798 #endif /* HAVE_BATCH_FILE_INSERT */
800 #endif /* HAVE_INGRES */