2 * Bacula Catalog Database routines specific to PostgreSQL
3 * These are PostgreSQL specific routines
5 * Dan Langille, December 2003
6 * based upon work done by Kern Sibbald, March 2000
11 Bacula® - The Network Backup Solution
13 Copyright (C) 2003-2006 Free Software Foundation Europe e.V.
15 The main author of Bacula is Kern Sibbald, with contributions from
16 many others, a complete list can be found in the file AUTHORS.
17 This program is Free Software; you can redistribute it and/or
18 modify it under the terms of version two of the GNU General Public
19 License as published by the Free Software Foundation plus additions
20 that are listed in the file LICENSE.
22 This program is distributed in the hope that it will be useful, but
23 WITHOUT ANY WARRANTY; without even the implied warranty of
24 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
25 General Public License for more details.
27 You should have received a copy of the GNU General Public License
28 along with this program; if not, write to the Free Software
29 Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA
32 Bacula® is a registered trademark of John Walker.
33 The licensor of Bacula is the Free Software Foundation Europe
34 (FSFE), Fiduciary Program, Sumatrastrasse 25, 8006 Zürich,
35 Switzerland, email:ftf@fsfeurope.org.
39 /* The following is necessary so that we do not include
40 * the dummy external definition of DB.
42 #define __SQL_C /* indicate that this is sql.c */
47 #ifdef HAVE_POSTGRESQL
49 #include "postgres_ext.h" /* needed for NAMEDATALEN */
51 /* -----------------------------------------------------------------------
53 * PostgreSQL dependent defines and subroutines
55 * -----------------------------------------------------------------------
58 /* List of open databases */
59 static BQUEUE db_list = {&db_list, &db_list};
61 static pthread_mutex_t mutex = PTHREAD_MUTEX_INITIALIZER;
64 * 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)
85 Jmsg(jcr, M_FATAL, 0, _("A user name for PostgreSQL must be supplied.\n"));
88 P(mutex); /* lock DB queue */
89 if (!mult_db_connections) {
90 /* Look to see if DB already open */
91 for (mdb=NULL; (mdb=(B_DB *)qnext(&db_list, &mdb->bq)); ) {
92 if (bstrcmp(mdb->db_name, db_name) &&
93 bstrcmp(mdb->db_address, db_address) &&
94 mdb->db_port == db_port) {
95 Dmsg2(100, "DB REopen %d %s\n", mdb->ref_count, db_name);
98 return mdb; /* already open */
102 Dmsg0(100, "db_open first time\n");
103 mdb = (B_DB *)malloc(sizeof(B_DB));
104 memset(mdb, 0, sizeof(B_DB));
105 mdb->db_name = bstrdup(db_name);
106 mdb->db_user = bstrdup(db_user);
108 mdb->db_password = bstrdup(db_password);
111 mdb->db_address = bstrdup(db_address);
114 mdb->db_socket = bstrdup(db_socket);
116 mdb->db_port = db_port;
117 mdb->have_insert_id = TRUE;
118 mdb->errmsg = get_pool_memory(PM_EMSG); /* get error message buffer */
120 mdb->cmd = get_pool_memory(PM_EMSG); /* get command buffer */
121 mdb->cached_path = get_pool_memory(PM_FNAME);
122 mdb->cached_path_id = 0;
124 mdb->fname = get_pool_memory(PM_FNAME);
125 mdb->path = get_pool_memory(PM_FNAME);
126 mdb->esc_name = get_pool_memory(PM_FNAME);
127 mdb->allow_transactions = mult_db_connections;
128 qinsert(&db_list, &mdb->bq); /* put db in list */
134 * Now actually open the database. This can generate errors,
135 * which are returned in the errmsg
137 * DO NOT close the database or free(mdb) here !!!!
140 db_open_database(JCR *jcr, B_DB *mdb)
146 if (mdb->connected) {
150 mdb->connected = false;
152 if ((errstat=rwl_init(&mdb->lock)) != 0) {
153 Mmsg1(&mdb->errmsg, _("Unable to initialize DB lock. ERR=%s\n"),
160 bsnprintf(buf, sizeof(buf), "%d", mdb->db_port);
166 /* If connection fails, try at 5 sec intervals for 30 seconds. */
167 for (int retry=0; retry < 6; retry++) {
168 /* connect to the database */
169 mdb->db = PQsetdbLogin(
170 mdb->db_address, /* default = localhost */
171 port, /* default port */
172 NULL, /* pg options */
173 NULL, /* tty, ignored */
174 mdb->db_name, /* database name */
175 mdb->db_user, /* login name */
176 mdb->db_password); /* password */
178 /* If no connect, try once more in case it is a timing problem */
179 if (PQstatus(mdb->db) == CONNECTION_OK) {
185 Dmsg0(50, "pg_real_connect done\n");
186 Dmsg3(50, "db_user=%s db_name=%s db_password=%s\n", mdb->db_user, mdb->db_name,
187 mdb->db_password==NULL?"(NULL)":mdb->db_password);
189 if (PQstatus(mdb->db) != CONNECTION_OK) {
190 Mmsg2(&mdb->errmsg, _("Unable to connect to PostgreSQL server.\n"
191 "Database=%s User=%s\n"
192 "It is probably not running or your password is incorrect.\n"),
193 mdb->db_name, mdb->db_user);
198 if (!check_tables_version(jcr, mdb)) {
203 sql_query(mdb, "SET datestyle TO 'ISO, YMD'");
205 mdb->connected = true;
211 db_close_database(JCR *jcr, B_DB *mdb)
216 db_end_transaction(jcr, mdb);
219 if (mdb->ref_count == 0) {
221 if (mdb->connected && mdb->db) {
224 rwl_destroy(&mdb->lock);
225 free_pool_memory(mdb->errmsg);
226 free_pool_memory(mdb->cmd);
227 free_pool_memory(mdb->cached_path);
228 free_pool_memory(mdb->fname);
229 free_pool_memory(mdb->path);
230 free_pool_memory(mdb->esc_name);
237 if (mdb->db_password) {
238 free(mdb->db_password);
240 if (mdb->db_address) {
241 free(mdb->db_address);
243 if (mdb->db_socket) {
244 free(mdb->db_socket);
246 my_postgresql_free_result(mdb);
253 * Return the next unique index (auto-increment) for
254 * the given table. Return NULL on error.
256 * For PostgreSQL, NULL causes the auto-increment value
259 int db_next_index(JCR *jcr, B_DB *mdb, char *table, char *index)
261 strcpy(index, "NULL");
267 * Escape strings so that PostgreSQL is happy
269 * NOTE! len is the length of the old string. Your new
270 * string must be long enough (max 2*old+1) to hold
271 * the escaped output.
274 db_escape_string(char *snew, char *old, int len)
276 PQescapeString(snew, old, len);
280 * Submit a general SQL command (cmd), and for each row returned,
281 * the sqlite_handler is called with the ctx.
283 int db_sql_query(B_DB *mdb, const char *query, DB_RESULT_HANDLER *result_handler, void *ctx)
287 Dmsg0(500, "db_sql_query started\n");
290 if (sql_query(mdb, query) != 0) {
291 Mmsg(mdb->errmsg, _("Query failed: %s: ERR=%s\n"), query, sql_strerror(mdb));
293 Dmsg0(500, "db_sql_query failed\n");
296 Dmsg0(500, "db_sql_query succeeded. checking handler\n");
298 if (result_handler != NULL) {
299 Dmsg0(500, "db_sql_query invoking handler\n");
300 if ((mdb->result = sql_store_result(mdb)) != NULL) {
301 int num_fields = sql_num_fields(mdb);
303 Dmsg0(500, "db_sql_query sql_store_result suceeded\n");
304 while ((row = sql_fetch_row(mdb)) != NULL) {
306 Dmsg0(500, "db_sql_query sql_fetch_row worked\n");
307 if (result_handler(ctx, num_fields, row))
311 sql_free_result(mdb);
316 Dmsg0(500, "db_sql_query finished\n");
323 POSTGRESQL_ROW my_postgresql_fetch_row(B_DB *mdb)
326 POSTGRESQL_ROW row = NULL; // by default, return NULL
328 Dmsg0(500, "my_postgresql_fetch_row start\n");
330 if (mdb->row_number == -1 || mdb->row == NULL) {
331 Dmsg1(500, "we have need space of %d bytes\n", sizeof(char *) * mdb->num_fields);
333 if (mdb->row != NULL) {
334 Dmsg0(500, "my_postgresql_fetch_row freeing space\n");
339 mdb->row = (POSTGRESQL_ROW) malloc(sizeof(char *) * mdb->num_fields);
341 // now reset the row_number now that we have the space allocated
345 // if still within the result set
346 if (mdb->row_number < mdb->num_rows) {
347 Dmsg2(500, "my_postgresql_fetch_row row number '%d' is acceptable (0..%d)\n", mdb->row_number, mdb->num_rows);
348 // get each value from this row
349 for (j = 0; j < mdb->num_fields; j++) {
350 mdb->row[j] = PQgetvalue(mdb->result, mdb->row_number, j);
351 Dmsg2(500, "my_postgresql_fetch_row field '%d' has value '%s'\n", j, mdb->row[j]);
353 // increment the row number for the next call
358 Dmsg2(500, "my_postgresql_fetch_row row number '%d' is NOT acceptable (0..%d)\n", mdb->row_number, mdb->num_rows);
361 Dmsg1(500, "my_postgresql_fetch_row finishes returning %x\n", row);
366 int my_postgresql_max_length(B_DB *mdb, int field_num) {
368 // for a given column, find the max length
375 for (i = 0; i < mdb->num_rows; i++) {
376 if (PQgetisnull(mdb->result, i, field_num)) {
377 this_length = 4; // "NULL"
379 this_length = cstrlen(PQgetvalue(mdb->result, i, field_num));
382 if (max_length < this_length) {
383 max_length = this_length;
390 POSTGRESQL_FIELD * my_postgresql_fetch_field(B_DB *mdb)
394 Dmsg0(500, "my_postgresql_fetch_field starts\n");
395 if (mdb->fields == NULL) {
396 Dmsg1(500, "allocating space for %d fields\n", mdb->num_fields);
397 mdb->fields = (POSTGRESQL_FIELD *)malloc(sizeof(POSTGRESQL_FIELD) * mdb->num_fields);
399 for (i = 0; i < mdb->num_fields; i++) {
400 Dmsg1(500, "filling field %d\n", i);
401 mdb->fields[i].name = PQfname(mdb->result, i);
402 mdb->fields[i].max_length = my_postgresql_max_length(mdb, i);
403 mdb->fields[i].type = PQftype(mdb->result, i);
404 mdb->fields[i].flags = 0;
406 Dmsg4(500, "my_postgresql_fetch_field finds field '%s' has length='%d' type='%d' and IsNull=%d\n",
407 mdb->fields[i].name, mdb->fields[i].max_length, mdb->fields[i].type,
408 mdb->fields[i].flags);
412 // increment field number for the next time around
414 Dmsg0(500, "my_postgresql_fetch_field finishes\n");
415 return &mdb->fields[mdb->field_number++];
418 void my_postgresql_data_seek(B_DB *mdb, int row)
420 // set the row number to be returned on the next call
421 // to my_postgresql_fetch_row
422 mdb->row_number = row;
425 void my_postgresql_field_seek(B_DB *mdb, int field)
427 mdb->field_number = field;
431 * Note, if this routine returns 1 (failure), Bacula expects
432 * that no result has been stored.
434 int my_postgresql_query(B_DB *mdb, const char *query) {
435 Dmsg0(500, "my_postgresql_query started\n");
436 // We are starting a new query. reset everything.
438 mdb->row_number = -1;
439 mdb->field_number = -1;
441 if (mdb->result != NULL) {
442 PQclear(mdb->result); /* hmm, someone forgot to free?? */
445 Dmsg1(500, "my_postgresql_query starts with '%s'\n", query);
446 mdb->result = PQexec(mdb->db, query);
447 mdb->status = PQresultStatus(mdb->result);
448 if (mdb->status == PGRES_TUPLES_OK || mdb->status == PGRES_COMMAND_OK) {
449 Dmsg1(500, "we have a result\n", query);
451 // how many fields in the set?
452 mdb->num_fields = (int) PQnfields(mdb->result);
453 Dmsg1(500, "we have %d fields\n", mdb->num_fields);
455 mdb->num_rows = PQntuples(mdb->result);
456 Dmsg1(500, "we have %d rows\n", mdb->num_rows);
460 Dmsg1(500, "we failed\n", query);
464 Dmsg0(500, "my_postgresql_query finishing\n");
469 void my_postgresql_free_result (B_DB *mdb)
472 PQclear(mdb->result);
487 int my_postgresql_currval(B_DB *mdb, char *table_name)
489 // Obtain the current value of the sequence that
490 // provides the serial value for primary key of the table.
492 // currval is local to our session. It is not affected by
493 // other transactions.
495 // Determine the name of the sequence.
496 // PostgreSQL automatically creates a sequence using
497 // <table>_<column>_seq.
498 // At the time of writing, all tables used this format for
499 // for their primary key: <table>id
500 // Except for basefiles which has a primary key on baseid.
501 // Therefore, we need to special case that one table.
503 // everything else can use the PostgreSQL formula.
505 char sequence[NAMEDATALEN-1];
506 char query [NAMEDATALEN+50];
510 if (strcasecmp(table_name, "basefiles") == 0) {
511 bstrncpy(sequence, "basefiles_baseid", sizeof(sequence));
513 bstrncpy(sequence, table_name, sizeof(sequence));
514 bstrncat(sequence, "_", sizeof(sequence));
515 bstrncat(sequence, table_name, sizeof(sequence));
516 bstrncat(sequence, "id", sizeof(sequence));
519 bstrncat(sequence, "_seq", sizeof(sequence));
520 bsnprintf(query, sizeof(query), "SELECT currval('%s')", sequence);
522 // Mmsg(query, "SELECT currval('%s')", sequence);
523 Dmsg1(500, "my_postgresql_currval invoked with '%s'\n", query);
524 result = PQexec(mdb->db, query);
526 Dmsg0(500, "exec done");
528 if (PQresultStatus(result) == PGRES_TUPLES_OK) {
529 Dmsg0(500, "getting value");
530 id = atoi(PQgetvalue(result, 0, 0));
531 Dmsg2(500, "got value '%s' which became %d\n", PQgetvalue(result, 0, 0), id);
533 Mmsg1(&mdb->errmsg, _("error fetching currval: %s\n"), PQerrorMessage(mdb->db));
542 #endif /* HAVE_POSTGRESQL */