2
* jabberd - Jabber Open Source Server
3
* Copyright (c) 2002-2003 Jeremie Miller, Thomas Muldowney,
4
* Ryan Eatmon, Robert Norris
6
* This program is free software; you can redistribute it and/or modify
7
* it under the terms of the GNU General Public License as published by
8
* the Free Software Foundation; either version 2 of the License, or
9
* (at your option) any later version.
11
* This program is distributed in the hope that it will be useful,
12
* but WITHOUT ANY WARRANTY; without even the implied warranty of
13
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.See the
14
* GNU General Public License for more details.
16
* You should have received a copy of the GNU General Public License
17
* along with this program; if not, write to the Free Software
18
* Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA02111-1307USA
21
/** @file sm/storage_pgsql.c
22
* @brief postgresql storage module
23
* @author Robert Norris
24
* $Date: 2005/03/23 18:01:25 $
25
* $Revision: 1.18.2.8 $
34
/** internal structure, holds our data */
35
typedef struct drvdata_st {
45
#define FALLBACK_BLOCKSIZE (4096)
47
/** internal: do and return the math and ensure it gets realloc'd */
48
static size_t _st_pgsql_realloc(char **oblocks, size_t len) {
51
static size_t block_size = 0;
53
if (block_size == 0) {
54
#ifdef HAVE_GETPAGESIZE
55
block_size = getpagesize();
56
#elif defined(_SC_PAGESIZE)
57
block_size = sysconf(_SC_PAGESIZE);
58
#elif defined(_SC_PAGE_SIZE)
59
block_size = sysconf(_SC_PAGE_SIZE);
61
block_size = FALLBACK_BLOCKSIZE;
64
/* round up to standard block sizes */
65
nlen = (((len-1)/block_size)+1)*block_size;
67
/* keep trying till we get it */
68
while((nblocks = realloc(*oblocks, nlen)) == NULL) sleep(1);
73
/** this is the safety check used to make sure there's always enough mem */
74
#define PGSQL_SAFE(blocks, size, len) if((size) >= len) len = _st_pgsql_realloc(&(blocks),(size + 1));
76
static void _st_pgsql_convert_filter_recursive(st_driver_t drv, st_filter_t f, char **buf, int *buflen, int *nbuf) {
82
case st_filter_type_PAIR:
83
/* do sql escaping for apostrophes */
84
cval = (char *) malloc(sizeof(char) * ((strlen(f->val) * 2) + 1));
85
vlen = PQescapeString(cval, f->val, strlen(f->val));
87
PGSQL_SAFE((*buf), *buflen + 12 + vlen - strlen(f->val), *buflen);
88
*nbuf += sprintf(&((*buf)[*nbuf]), "( \"%s\" = \'%s\' ) ", f->key, f->val);
93
case st_filter_type_AND:
94
PGSQL_SAFE((*buf), *buflen + 2, *buflen);
95
*nbuf += sprintf(&((*buf)[*nbuf]), "( ");
97
for(scan = f->sub; scan != NULL; scan = scan->next) {
98
_st_pgsql_convert_filter_recursive(drv, scan, buf, buflen, nbuf);
100
if(scan->next != NULL) {
101
PGSQL_SAFE((*buf), *buflen + 4, *buflen);
102
*nbuf += sprintf(&((*buf)[*nbuf]), "AND ");
106
PGSQL_SAFE((*buf), *buflen + 2, *buflen);
107
*nbuf += sprintf(&((*buf)[*nbuf]), ") ");
111
case st_filter_type_OR:
112
PGSQL_SAFE((*buf), *buflen + 2, *buflen);
113
*nbuf += sprintf(&((*buf)[*nbuf]), "( ");
115
for(scan = f->sub; scan != NULL; scan = scan->next) {
116
_st_pgsql_convert_filter_recursive(drv, scan, buf, buflen, nbuf);
118
if(scan->next != NULL) {
119
PGSQL_SAFE((*buf), *buflen + 3, *buflen);
120
*nbuf += sprintf(&((*buf)[*nbuf]), "OR ");
124
PGSQL_SAFE((*buf), *buflen + 2, *buflen);
125
*nbuf += sprintf(&((*buf)[*nbuf]), ") ");
129
case st_filter_type_NOT:
130
PGSQL_SAFE((*buf), *buflen + 6, *buflen);
131
*nbuf += sprintf(&((*buf)[*nbuf]), "( NOT ");
133
_st_pgsql_convert_filter_recursive(drv, f->sub, buf, buflen, nbuf);
135
PGSQL_SAFE((*buf), *buflen + 2, *buflen);
136
*nbuf += sprintf(&((*buf)[*nbuf]), ") ");
142
static char *_st_pgsql_convert_filter(st_driver_t drv, const char *owner, const char *filter) {
143
drvdata_t data = (drvdata_t) drv->private;
144
char *buf = NULL, *sbuf = NULL, *cfilter;
145
int buflen = 0, nbuf = 0, fbuf;
148
PGSQL_SAFE(buf, 24 + strlen(owner), buflen);
150
nbuf = sprintf(buf, "\"collection-owner\" = '%s'", owner);
152
sbuf = xhash_get(data->filters, filter);
154
PGSQL_SAFE(buf, buflen + strlen(sbuf) + 7, buflen);
155
nbuf += sprintf(&buf[nbuf], " AND %s", sbuf);
159
cfilter = pstrdup(xhash_pool(data->filters), filter);
161
f = storage_filter(filter);
165
PGSQL_SAFE(buf, buflen + 5, buflen);
166
nbuf += sprintf(&buf[nbuf], " AND ");
170
_st_pgsql_convert_filter_recursive(drv, f, &buf, &buflen, &nbuf);
172
xhash_put(data->filters, cfilter, pstrdup(xhash_pool(data->filters), &buf[fbuf]));
179
static st_ret_t _st_pgsql_add_type(st_driver_t drv, const char *type) {
183
static st_ret_t _st_pgsql_put_guts(st_driver_t drv, const char *type, const char *owner, os_t os) {
184
drvdata_t data = (drvdata_t) drv->private;
185
char *left = NULL, *right = NULL;
186
int lleft = 0, lright = 0, nleft, nright;
188
char *key, *cval = NULL;
197
if(os_count(os) == 0)
200
if(data->prefix != NULL) {
201
snprintf(tbuf, sizeof(tbuf), "%s%s", data->prefix, type);
205
if(os_iter_first(os))
207
PGSQL_SAFE(left, strlen(type) + 55, lleft);
208
nleft = sprintf(left, "INSERT INTO \"%s\" ( \"collection-owner\", \"object-sequence\"", type);
210
PGSQL_SAFE(right, strlen(owner) + 43, lright);
211
nright = sprintf(right, " ) VALUES ( '%s', nextval('object-sequence')", owner);
213
o = os_iter_object(os);
214
if(os_object_iter_first(o))
216
os_object_iter_get(o, &key, &val, &ot);
219
case os_type_BOOLEAN:
220
cval = val ? strdup("t") : strdup("f");
224
case os_type_INTEGER:
225
cval = (char *) malloc(sizeof(char) * 20);
226
sprintf(cval, "%d", (int) val);
231
cval = (char *) malloc(sizeof(char) * ((strlen((char *) val) * 2) + 1));
232
vlen = PQescapeString(cval, (char *) val, strlen((char *) val));
236
nad_print((nad_t) val, 0, &xml, &xlen);
237
cval = (char *) malloc(sizeof(char) * ((xlen * 2) + 4));
238
vlen = PQescapeString(&cval[3], xml, xlen) + 3;
239
strncpy(cval, "NAD", 3);
242
case os_type_UNKNOWN:
246
log_debug(ZONE, "key %s val %s", key, cval);
248
PGSQL_SAFE(left, lleft + strlen(key) + 4, lleft);
249
nleft += sprintf(&left[nleft], ", \"%s\"", key);
251
PGSQL_SAFE(right, lright + strlen(cval) + 4, lright);
252
nright += sprintf(&right[nright], ", '%s'", cval);
255
} while(os_object_iter_next(o));
257
PGSQL_SAFE(left, lleft + strlen(right) + 3, lleft);
258
sprintf(&left[nleft], "%s );", right);
260
log_debug(ZONE, "prepared sql: %s", left);
262
res = PQexec(data->conn, left);
264
if(PQresultStatus(res) != PGRES_COMMAND_OK && PQstatus(data->conn) != CONNECTION_OK) {
265
log_write(drv->st->sm->log, LOG_ERR, "pgsql: lost connection to database, attempting reconnect");
268
res = PQexec(data->conn, left);
270
if(PQresultStatus(res) != PGRES_COMMAND_OK) {
271
log_write(drv->st->sm->log, LOG_ERR, "pgsql: sql insert failed: %s", PQresultErrorMessage(res));
280
} while(os_iter_next(os));
288
static st_ret_t _st_pgsql_put(st_driver_t drv, const char *type, const char *owner, os_t os) {
289
drvdata_t data = (drvdata_t) drv->private;
292
if(os_count(os) == 0)
296
res = PQexec(data->conn, "BEGIN;");
297
if(PQresultStatus(res) != PGRES_COMMAND_OK && PQstatus(data->conn) != CONNECTION_OK) {
298
log_write(drv->st->sm->log, LOG_ERR, "pgsql: lost connection to database, attempting reconnect");
301
res = PQexec(data->conn, "BEGIN;");
303
if(PQresultStatus(res) != PGRES_COMMAND_OK) {
304
log_write(drv->st->sm->log, LOG_ERR, "pgsql: sql transaction begin failed: %s", PQresultErrorMessage(res));
310
res = PQexec(data->conn, "SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;");
311
if(PQresultStatus(res) != PGRES_COMMAND_OK && PQstatus(data->conn) != CONNECTION_OK) {
312
log_write(drv->st->sm->log, LOG_ERR, "pgsql: lost connection to database, attempting reconnect");
315
res = PQexec(data->conn, "SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;");
317
if(PQresultStatus(res) != PGRES_COMMAND_OK) {
318
log_write(drv->st->sm->log, LOG_ERR, "pgsql: sql transaction setup failed: %s", PQresultErrorMessage(res));
320
PQclear(PQexec(data->conn, "ROLLBACK;"));
326
if(_st_pgsql_put_guts(drv, type, owner, os) != st_SUCCESS) {
328
PQclear(PQexec(data->conn, "ROLLBACK;"));
333
res = PQexec(data->conn, "COMMIT;");
334
if(PQresultStatus(res) != PGRES_COMMAND_OK && PQstatus(data->conn) != CONNECTION_OK) {
335
log_write(drv->st->sm->log, LOG_ERR, "pgsql: lost connection to database, attempting reconnect");
338
res = PQexec(data->conn, "COMMIT;");
340
if(PQresultStatus(res) != PGRES_COMMAND_OK) {
341
log_write(drv->st->sm->log, LOG_ERR, "pgsql: sql transaction commit failed: %s", PQresultErrorMessage(res));
343
PQclear(PQexec(data->conn, "ROLLBACK;"));
352
static st_ret_t _st_pgsql_get(st_driver_t drv, const char *type, const char *owner, const char *filter, os_t *os) {
353
drvdata_t data = (drvdata_t) drv->private;
354
char *cond, *buf = NULL;
357
int ntuples, nfields, i, j;
364
if(data->prefix != NULL) {
365
snprintf(tbuf, sizeof(tbuf), "%s%s", data->prefix, type);
369
cond = _st_pgsql_convert_filter(drv, owner, filter);
370
log_debug(ZONE, "generated filter: %s", cond);
372
PGSQL_SAFE(buf, strlen(type) + strlen(cond) + 51, buflen);
373
sprintf(buf, "SELECT * FROM \"%s\" WHERE %s ORDER BY \"object-sequence\";", type, cond);
376
log_debug(ZONE, "prepared sql: %s", buf);
378
res = PQexec(data->conn, buf);
380
if(PQresultStatus(res) != PGRES_TUPLES_OK && PQstatus(data->conn) != CONNECTION_OK) {
381
log_write(drv->st->sm->log, LOG_ERR, "pgsql: lost connection to database, attempting reconnect");
384
res = PQexec(data->conn, buf);
389
if(PQresultStatus(res) != PGRES_TUPLES_OK) {
390
log_write(drv->st->sm->log, LOG_ERR, "pgsql: sql select failed: %s", PQresultErrorMessage(res));
395
ntuples = PQntuples(res);
401
log_debug(ZONE, "%d tuples returned", ntuples);
403
nfields = PQnfields(res);
406
log_debug(ZONE, "weird, tuples were returned but no fields *shrug*");
413
for(i = 0; i < ntuples; i++) {
414
o = os_object_new(*os);
416
for(j = 0; j < nfields; j++) {
417
fname = PQfname(res, j);
418
if(strcmp(fname, "collection-owner") == 0 || strcmp(fname, "object-sequence") == 0)
421
switch(PQftype(res, j)) {
422
case 16: /* boolean */
423
ot = os_type_BOOLEAN;
426
case 23: /* integer */
427
ot = os_type_INTEGER;
435
log_debug(ZONE, "unknown oid %d, ignoring it", PQfname(res, j));
439
if(PQgetisnull(res, i, j))
442
val = PQgetvalue(res, i, j);
445
case os_type_BOOLEAN:
446
ival = (val[0] == 't') ? 1 : 0;
447
os_object_put(o, fname, &ival, ot);
450
case os_type_INTEGER:
452
os_object_put(o, fname, &ival, ot);
456
os_object_put(o, fname, val, os_type_STRING);
460
case os_type_UNKNOWN:
471
static st_ret_t _st_pgsql_delete(st_driver_t drv, const char *type, const char *owner, const char *filter) {
472
drvdata_t data = (drvdata_t) drv->private;
473
char *cond, *buf = NULL;
478
if(data->prefix != NULL) {
479
snprintf(tbuf, sizeof(tbuf), "%s%s", data->prefix, type);
483
cond = _st_pgsql_convert_filter(drv, owner, filter);
484
log_debug(ZONE, "generated filter: %s", cond);
486
PGSQL_SAFE(buf, strlen(type) + strlen(cond) + 23, buflen);
487
sprintf(buf, "DELETE FROM \"%s\" WHERE %s;", type, cond);
490
log_debug(ZONE, "prepared sql: %s", buf);
492
res = PQexec(data->conn, buf);
494
if(PQresultStatus(res) != PGRES_COMMAND_OK && PQstatus(data->conn) != CONNECTION_OK) {
495
log_write(drv->st->sm->log, LOG_ERR, "pgsql: lost connection to database, attempting reconnect");
498
res = PQexec(data->conn, buf);
503
if(PQresultStatus(res) != PGRES_COMMAND_OK) {
504
log_write(drv->st->sm->log, LOG_ERR, "pgsql: sql delete failed: %s", PQresultErrorMessage(res));
514
static st_ret_t _st_pgsql_replace(st_driver_t drv, const char *type, const char *owner, const char *filter, os_t os) {
515
drvdata_t data = (drvdata_t) drv->private;
519
res = PQexec(data->conn, "BEGIN;");
520
if(PQresultStatus(res) != PGRES_COMMAND_OK && PQstatus(data->conn) != CONNECTION_OK) {
521
log_write(drv->st->sm->log, LOG_ERR, "pgsql: lost connection to database, attempting reconnect");
524
res = PQexec(data->conn, "BEGIN;");
526
if(PQresultStatus(res) != PGRES_COMMAND_OK) {
527
log_write(drv->st->sm->log, LOG_ERR, "pgsql: sql transaction begin failed: %s", PQresultErrorMessage(res));
533
res = PQexec(data->conn, "SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;");
534
if(PQresultStatus(res) != PGRES_COMMAND_OK && PQstatus(data->conn) != CONNECTION_OK) {
535
log_write(drv->st->sm->log, LOG_ERR, "pgsql: lost connection to database, attempting reconnect");
538
res = PQexec(data->conn, "SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;");
540
if(PQresultStatus(res) != PGRES_COMMAND_OK) {
541
log_write(drv->st->sm->log, LOG_ERR, "pgsql: sql transaction setup failed: %s", PQresultErrorMessage(res));
543
PQclear(PQexec(data->conn, "ROLLBACK;"));
549
if(_st_pgsql_delete(drv, type, owner, filter) == st_FAILED) {
551
PQclear(PQexec(data->conn, "ROLLBACK;"));
555
if(_st_pgsql_put_guts(drv, type, owner, os) == st_FAILED) {
557
PQclear(PQexec(data->conn, "ROLLBACK;"));
562
res = PQexec(data->conn, "COMMIT;");
563
if(PQresultStatus(res) != PGRES_COMMAND_OK && PQstatus(data->conn) != CONNECTION_OK) {
564
log_write(drv->st->sm->log, LOG_ERR, "pgsql: lost connection to database, attempting reconnect");
567
res = PQexec(data->conn, "COMMIT;");
569
if(PQresultStatus(res) != PGRES_COMMAND_OK) {
570
log_write(drv->st->sm->log, LOG_ERR, "pgsql: sql transaction commit failed: %s", PQresultErrorMessage(res));
572
PQclear(PQexec(data->conn, "ROLLBACK;"));
581
static void _st_pgsql_free(st_driver_t drv) {
582
drvdata_t data = (drvdata_t) drv->private;
584
PQfinish(data->conn);
586
xhash_free(data->filters);
591
st_ret_t st_pgsql_init(st_driver_t drv) {
592
char *host, *port, *dbname, *user, *pass;
596
host = config_get_one(drv->st->sm->config, "storage.pgsql.host", 0);
597
port = config_get_one(drv->st->sm->config, "storage.pgsql.port", 0);
598
dbname = config_get_one(drv->st->sm->config, "storage.pgsql.dbname", 0);
599
user = config_get_one(drv->st->sm->config, "storage.pgsql.user", 0);
600
pass = config_get_one(drv->st->sm->config, "storage.pgsql.pass", 0);
602
if(host == NULL || port == NULL || dbname == NULL || user == NULL || pass == NULL) {
603
log_write(drv->st->sm->log, LOG_ERR, "pgsql: invalid driver config");
607
conn = PQsetdbLogin(host, port, NULL, NULL, dbname, user, pass);
609
log_write(drv->st->sm->log, LOG_ERR, "pgsql: unable to allocate database connection state");
613
if(PQstatus(conn) != CONNECTION_OK)
614
log_write(drv->st->sm->log, LOG_ERR, "pgsql: connection to database failed: %s", PQerrorMessage(conn));
616
data = (drvdata_t) malloc(sizeof(struct drvdata_st));
617
memset(data, 0, sizeof(struct drvdata_st));
621
data->filters = xhash_new(17);
623
if(config_get_one(drv->st->sm->config, "storage.pgsql.transactions", 0) != NULL)
626
log_write(drv->st->sm->log, LOG_WARNING, "pgsql: transactions disabled");
628
data->prefix = config_get_one(drv->st->sm->config, "storage.pgsql.prefix", 0);
630
drv->private = (void *) data;
632
drv->add_type = _st_pgsql_add_type;
633
drv->put = _st_pgsql_put;
634
drv->get = _st_pgsql_get;
635
drv->delete = _st_pgsql_delete;
636
drv->replace = _st_pgsql_replace;
637
drv->free = _st_pgsql_free;