src/lib/srdb1/db_query.c
bc4bf265
 /*
  * Copyright (C) 2007-2008 1&1 Internet AG
  *
d520eaf5
  * This file is part of Kamailio, a free SIP server.
bc4bf265
  *
d520eaf5
  * Kamailio is free software; you can redistribute it and/or modify
bc4bf265
  * it under the terms of the GNU General Public License as published by
  * the Free Software Foundation; either version 2 of the License, or
  * (at your option) any later version
  *
d520eaf5
  * Kamailio is distributed in the hope that it will be useful,
bc4bf265
  * but WITHOUT ANY WARRANTY; without even the implied warranty of
  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  * GNU General Public License for more details.
  *
0780e781
  * You should have received a copy of the GNU General Public License
  * along with this program; if not, write to the Free Software
9e1ff448
  * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301  USA
bc4bf265
  *
  */
 
 /**
7d14b037
  * \file lib/srdb1/db_query.c
bc4bf265
  * \brief Query helper for database drivers
a33b9a87
  * \ingroup db1
bc4bf265
  *
  * This helper methods for database queries are used from the database
  * SQL driver to do the actual work. Each function uses some functions from
  * the actual driver with function pointers to the concrete, specific
  * implementation.
 */
 
 #include <stdio.h>
13e07c78
 #include <stdlib.h>
ce8d36f3
 #include <time.h>
cf83221d
 #include "../../core/dprint.h"
bc4bf265
 #include "db_ut.h"
 #include "db_query.h"
cf83221d
 #include "../../core/globals.h"
 #include "../../core/timer.h"
bc4bf265
 
25f17988
 static str  sql_str;
5cd87175
 static char *sql_buf = NULL;
bc4bf265
 
930aba29
 static inline int db_do_submit_query(const db1_con_t* _h, const str *_query,
 		int (*submit_query)(const db1_con_t*, const str*))
 {
 	int ret;
0780e781
 	struct timeval tvb = {0}, tve = {0};
ce8d36f3
 	struct timezone tz;
 	unsigned int tdiff;
930aba29
 
ce8d36f3
 	if(unlikely(cfg_get(core, core_cfg, latency_limit_db)>0)
 			&& is_printable(cfg_get(core, core_cfg, latency_log))) {
 		gettimeofday(&tvb, &tz);
 	}
930aba29
 
 	ret = submit_query(_h, _query);
 
ce8d36f3
 	if(unlikely(cfg_get(core, core_cfg, latency_limit_db)>0)
 			&& is_printable(cfg_get(core, core_cfg, latency_log))) {
 		gettimeofday(&tve, &tz);
 		tdiff = (tve.tv_sec - tvb.tv_sec) * 1000000
 					   + (tve.tv_usec - tvb.tv_usec);
 		if(tdiff >= cfg_get(core, core_cfg, latency_limit_db)) {
 			LOG(cfg_get(core, core_cfg, latency_log),
 					"alert - query execution too long [%u us] for [%.*s]\n",
 				   tdiff, _query->len<100?_query->len:100, _query->s);
930aba29
 		}
 	}
 
635cdbc1
 	LM_DBG("submitted query: %.*s, result %d\n", _query->len, _query->s, ret);
930aba29
 	return ret;
 }
 
6c39a678
 static int db_do_query_internal(const db1_con_t* _h, const db_key_t* _k, const db_op_t* _op,
bc4bf265
 	const db_val_t* _v, const db_key_t* _c, const int _n, const int _nc,
13f9c1e6
 	const db_key_t _o, db1_res_t** _r, int (*val2str) (const db1_con_t*,
a5071c50
 	const db_val_t*, char*, int* _len), int (*submit_query)(const db1_con_t*,
6c39a678
 	const str*), int (*store_result)(const db1_con_t* _h, db1_res_t** _r), int _l)
bc4bf265
 {
 	int off, ret;
 
 	if (!_h || !val2str || !submit_query || !store_result) {
 		LM_ERR("invalid parameter value\n");
 		return -1;
 	}
 
 	if (!_c) {
ffbe9c2c
 		ret = snprintf(sql_buf, sql_buffer_size, "select * from %s%.*s%s ",
 				CON_TQUOTESZ(_h), CON_TABLE(_h)->len, CON_TABLE(_h)->s, CON_TQUOTESZ(_h));
5cd87175
 		if (ret < 0 || ret >= sql_buffer_size) goto error;
bc4bf265
 		off = ret;
 	} else {
5cd87175
 		ret = snprintf(sql_buf, sql_buffer_size, "select ");
 		if (ret < 0 || ret >= sql_buffer_size) goto error;
bc4bf265
 		off = ret;
 
ffbe9c2c
 		ret = db_print_columns(sql_buf + off, sql_buffer_size - off, _c, _nc, CON_TQUOTESZ(_h));
bc4bf265
 		if (ret < 0) return -1;
 		off += ret;
 
ffbe9c2c
 		ret = snprintf(sql_buf + off, sql_buffer_size - off, "from %s%.*s%s ",
 				CON_TQUOTESZ(_h), CON_TABLE(_h)->len, CON_TABLE(_h)->s, CON_TQUOTESZ(_h));
5cd87175
 		if (ret < 0 || ret >= (sql_buffer_size - off)) goto error;
bc4bf265
 		off += ret;
 	}
 	if (_n) {
5cd87175
 		ret = snprintf(sql_buf + off, sql_buffer_size - off, "where ");
 		if (ret < 0 || ret >= (sql_buffer_size - off)) goto error;
bc4bf265
 		off += ret;
 
 		ret = db_print_where(_h, sql_buf + off,
5cd87175
 				sql_buffer_size - off, _k, _op, _v, _n, val2str);
bc4bf265
 		if (ret < 0) return -1;;
 		off += ret;
 	}
 	if (_o) {
5cd87175
 		ret = snprintf(sql_buf + off, sql_buffer_size - off, " order by %.*s", _o->len, _o->s);
 		if (ret < 0 || ret >= (sql_buffer_size - off)) goto error;
bc4bf265
 		off += ret;
 	}
6c39a678
 	if (_l) {
 		ret = snprintf(sql_buf + off, sql_buffer_size - off, " for update");
 		if (ret < 0 || ret >= (sql_buffer_size - off)) goto error;
 		off += ret;
 	}
25f17988
 	/*
 	 * Null-terminate the string for the postgres driver. Its query function
 	 * don't support a length parameter, so they need this for the correct
 	 * function of strlen. This zero is not included in the 'str' length.
30bde5b0
 	 * We need to check the length here, otherwise we could overwrite the buffer
5cd87175
 	 * boundaries if off is equal to sql_buffer_size.
25f17988
 	 */
5cd87175
 	if (off + 1 >= sql_buffer_size) goto error;
25f17988
 	sql_buf[off + 1] = '\0';
 	sql_str.s = sql_buf;
 	sql_str.len = off;
 
930aba29
 	if (db_do_submit_query(_h, &sql_str, submit_query) < 0) {
bc4bf265
 		LM_ERR("error while submitting query\n");
 		return -2;
 	}
 
 	if(_r) {
 		int tmp = store_result(_h, _r);
 		if (tmp < 0) {
 			LM_ERR("error while storing result");
 			return tmp;
 		}
 	}
 	return 0;
 
 error:
 	LM_ERR("error while preparing query\n");
 	return -1;
 }
 
6c39a678
 int db_do_query(const db1_con_t* _h, const db_key_t* _k, const db_op_t* _op,
 	const db_val_t* _v, const db_key_t* _c, const int _n, const int _nc,
 	const db_key_t _o, db1_res_t** _r, int (*val2str) (const db1_con_t*,
 	const db_val_t*, char*, int* _len), int (*submit_query)(const db1_con_t*,
 	const str*), int (*store_result)(const db1_con_t* _h, db1_res_t** _r))
 {
 	return db_do_query_internal(_h, _k, _op, _v, _c, _n, _nc, _o, _r, val2str,
 					submit_query, store_result, 0);
 }
 
 int db_do_query_lock(const db1_con_t* _h, const db_key_t* _k, const db_op_t* _op,
 	const db_val_t* _v, const db_key_t* _c, const int _n, const int _nc,
 	const db_key_t _o, db1_res_t** _r, int (*val2str) (const db1_con_t*,
 	const db_val_t*, char*, int* _len), int (*submit_query)(const db1_con_t*,
 	const str*), int (*store_result)(const db1_con_t* _h, db1_res_t** _r))
 {
 	return db_do_query_internal(_h, _k, _op, _v, _c, _n, _nc, _o, _r, val2str,
 					submit_query, store_result, 1);
 }
 
bc4bf265
 
13f9c1e6
 int db_do_raw_query(const db1_con_t* _h, const str* _s, db1_res_t** _r,
a5071c50
 	int (*submit_query)(const db1_con_t* _h, const str* _c),
13f9c1e6
 	int (*store_result)(const db1_con_t* _h, db1_res_t** _r))
bc4bf265
 {
 	if (!_h || !_s || !submit_query || !store_result) {
 		LM_ERR("invalid parameter value\n");
 		return -1;
 	}
 
930aba29
 	if (db_do_submit_query(_h, _s, submit_query) < 0) {
bc4bf265
 		LM_ERR("error while submitting query\n");
 		return -2;
 	}
 
 	if(_r) {
 		int tmp = store_result(_h, _r);
 		if (tmp < 0) {
 			LM_ERR("error while storing result");
 			return tmp;
 		}
 	}
 	return 0;
 }
 
 
13e07c78
 int db_do_insert_cmd(const db1_con_t* _h, const db_key_t* _k, const db_val_t* _v,
a5071c50
 	const int _n, int (*val2str) (const db1_con_t*, const db_val_t*, char*, int*),
13e07c78
 	int (*submit_query)(const db1_con_t* _h, const str* _c), int mode)
bc4bf265
 {
 	int off, ret;
 
 	if (!_h || !_k || !_v || !_n || !val2str || !submit_query) {
 		LM_ERR("invalid parameter value\n");
 		return -1;
 	}
 
13e07c78
 	if(mode==1)
ffbe9c2c
 		ret = snprintf(sql_buf, sql_buffer_size, "insert delayed into %s%.*s%s (",
 				CON_TQUOTESZ(_h), CON_TABLE(_h)->len, CON_TABLE(_h)->s, CON_TQUOTESZ(_h));
13e07c78
 	else
ffbe9c2c
 		ret = snprintf(sql_buf, sql_buffer_size, "insert into %s%.*s%s (",
 				CON_TQUOTESZ(_h), CON_TABLE(_h)->len, CON_TABLE(_h)->s, CON_TQUOTESZ(_h));
5cd87175
 	if (ret < 0 || ret >= sql_buffer_size) goto error;
bc4bf265
 	off = ret;
 
ffbe9c2c
 	ret = db_print_columns(sql_buf + off, sql_buffer_size - off, _k, _n, CON_TQUOTESZ(_h));
bc4bf265
 	if (ret < 0) return -1;
 	off += ret;
 
5cd87175
 	ret = snprintf(sql_buf + off, sql_buffer_size - off, ") values (");
 	if (ret < 0 || ret >= (sql_buffer_size - off)) goto error;
bc4bf265
 	off += ret;
 
5cd87175
 	ret = db_print_values(_h, sql_buf + off, sql_buffer_size - off, _v, _n, val2str);
bc4bf265
 	if (ret < 0) return -1;
 	off += ret;
 
5cd87175
 	if (off + 2 > sql_buffer_size) goto error;
25f17988
 	sql_buf[off++] = ')';
e2a96019
 	sql_buf[off] = '\0';
25f17988
 	sql_str.s = sql_buf;
 	sql_str.len = off;
bc4bf265
 
930aba29
 	if (db_do_submit_query(_h, &sql_str, submit_query) < 0) {
bc4bf265
 	        LM_ERR("error while submitting query\n");
 		return -2;
 	}
 	return 0;
 
 error:
 	LM_ERR("error while preparing insert operation\n");
 	return -1;
 }
 
13e07c78
 int db_do_insert(const db1_con_t* _h, const db_key_t* _k, const db_val_t* _v,
 	const int _n, int (*val2str) (const db1_con_t*, const db_val_t*, char*, int*),
 	int (*submit_query)(const db1_con_t* _h, const str* _c))
 {
 	return db_do_insert_cmd(_h, _k, _v, _n, val2str, submit_query, 0);
 }
 
 int db_do_insert_delayed(const db1_con_t* _h, const db_key_t* _k, const db_val_t* _v,
 	const int _n, int (*val2str) (const db1_con_t*, const db_val_t*, char*, int*),
 	int (*submit_query)(const db1_con_t* _h, const str* _c))
 {
 	return db_do_insert_cmd(_h, _k, _v, _n, val2str, submit_query, 1);
 }
bc4bf265
 
a5071c50
 int db_do_delete(const db1_con_t* _h, const db_key_t* _k, const db_op_t* _o,
 	const db_val_t* _v, const int _n, int (*val2str) (const db1_con_t*,
 	const db_val_t*, char*, int*), int (*submit_query)(const db1_con_t* _h,
25f17988
 	const str* _c))
bc4bf265
 {
 	int off, ret;
 
 	if (!_h || !val2str || !submit_query) {
 		LM_ERR("invalid parameter value\n");
 		return -1;
 	}
 
ffbe9c2c
 	ret = snprintf(sql_buf, sql_buffer_size, "delete from %s%.*s%s",
 			CON_TQUOTESZ(_h), CON_TABLE(_h)->len, CON_TABLE(_h)->s, CON_TQUOTESZ(_h));
5cd87175
 	if (ret < 0 || ret >= sql_buffer_size) goto error;
bc4bf265
 	off = ret;
 
 	if (_n) {
5cd87175
 		ret = snprintf(sql_buf + off, sql_buffer_size - off, " where ");
 		if (ret < 0 || ret >= (sql_buffer_size - off)) goto error;
bc4bf265
 		off += ret;
 
 		ret = db_print_where(_h, sql_buf + off,
5cd87175
 				sql_buffer_size - off, _k, _o, _v, _n, val2str);
bc4bf265
 		if (ret < 0) return -1;
 		off += ret;
 	}
5cd87175
 	if (off + 1 > sql_buffer_size) goto error;
e2a96019
 	sql_buf[off] = '\0';
25f17988
 	sql_str.s = sql_buf;
 	sql_str.len = off;
bc4bf265
 
930aba29
 	if (db_do_submit_query(_h, &sql_str, submit_query) < 0) {
bc4bf265
 		LM_ERR("error while submitting query\n");
 		return -2;
 	}
 	return 0;
 
 error:
 	LM_ERR("error while preparing delete operation\n");
 	return -1;
 }
 
 
a5071c50
 int db_do_update(const db1_con_t* _h, const db_key_t* _k, const db_op_t* _o,
bc4bf265
 	const db_val_t* _v, const db_key_t* _uk, const db_val_t* _uv, const int _n,
a5071c50
 	const int _un, int (*val2str) (const db1_con_t*, const db_val_t*, char*, int*),
 	int (*submit_query)(const db1_con_t* _h, const str* _c))
bc4bf265
 {
 	int off, ret;
 
 	if (!_h || !_uk || !_uv || !_un || !val2str || !submit_query) {
 		LM_ERR("invalid parameter value\n");
 		return -1;
 	}
 
ffbe9c2c
 	ret = snprintf(sql_buf, sql_buffer_size, "update %s%.*s%s set ",
 			CON_TQUOTESZ(_h), CON_TABLE(_h)->len, CON_TABLE(_h)->s, CON_TQUOTESZ(_h));
5cd87175
 	if (ret < 0 || ret >= sql_buffer_size) goto error;
bc4bf265
 	off = ret;
 
5cd87175
 	ret = db_print_set(_h, sql_buf + off, sql_buffer_size - off, _uk, _uv, _un, val2str);
bc4bf265
 	if (ret < 0) return -1;
 	off += ret;
 
 	if (_n) {
5cd87175
 		ret = snprintf(sql_buf + off, sql_buffer_size - off, " where ");
 		if (ret < 0 || ret >= (sql_buffer_size - off)) goto error;
bc4bf265
 		off += ret;
 
5cd87175
 		ret = db_print_where(_h, sql_buf + off, sql_buffer_size - off, _k, _o, _v, _n, val2str);
bc4bf265
 		if (ret < 0) return -1;
 		off += ret;
 	}
5cd87175
 	if (off + 1 > sql_buffer_size) goto error;
e2a96019
 	sql_buf[off] = '\0';
25f17988
 	sql_str.s = sql_buf;
 	sql_str.len = off;
bc4bf265
 
930aba29
 	if (db_do_submit_query(_h, &sql_str, submit_query) < 0) {
bc4bf265
 		LM_ERR("error while submitting query\n");
 		return -2;
 	}
 	return 0;
 
 error:
 	LM_ERR("error while preparing update operation\n");
 	return -1;
 }
 
 
a5071c50
 int db_do_replace(const db1_con_t* _h, const db_key_t* _k, const db_val_t* _v,
 	const int _n, int (*val2str) (const db1_con_t*, const db_val_t*, char*,
 	int*), int (*submit_query)(const db1_con_t* _h, const str* _c))
bc4bf265
 {
 	int off, ret;
 
 	if (!_h || !_k || !_v || !val2str|| !submit_query) {
 		LM_ERR("invalid parameter value\n");
 		return -1;
 	}
 
ffbe9c2c
 	ret = snprintf(sql_buf, sql_buffer_size, "replace %s%.*s%s (",
 			CON_TQUOTESZ(_h), CON_TABLE(_h)->len, CON_TABLE(_h)->s, CON_TQUOTESZ(_h));
5cd87175
 	if (ret < 0 || ret >= sql_buffer_size) goto error;
bc4bf265
 	off = ret;
 
ffbe9c2c
 	ret = db_print_columns(sql_buf + off, sql_buffer_size - off, _k, _n, CON_TQUOTESZ(_h));
bc4bf265
 	if (ret < 0) return -1;
 	off += ret;
 
5cd87175
 	ret = snprintf(sql_buf + off, sql_buffer_size - off, ") values (");
 	if (ret < 0 || ret >= (sql_buffer_size - off)) goto error;
bc4bf265
 	off += ret;
 
5cd87175
 	ret = db_print_values(_h, sql_buf + off, sql_buffer_size - off, _v, _n,
bc4bf265
 	val2str);
 	if (ret < 0) return -1;
 	off += ret;
 
5cd87175
 	if (off + 2 > sql_buffer_size) goto error;
25f17988
 	sql_buf[off++] = ')';
e2a96019
 	sql_buf[off] = '\0';
25f17988
 	sql_str.s = sql_buf;
 	sql_str.len = off;
bc4bf265
 
930aba29
 	if (db_do_submit_query(_h, &sql_str, submit_query) < 0) {
bc4bf265
 	        LM_ERR("error while submitting query\n");
 		return -2;
 	}
 	return 0;
 
  error:
 	LM_ERR("error while preparing replace operation\n");
 	return -1;
 }
5cd87175
 
 int db_query_init(void)
 {
     if (sql_buf != NULL)
     {
         LM_DBG("sql_buf not NULL on init\n");
         return 0;
     }
     LM_DBG("About to allocate sql_buf size = %d\n", sql_buffer_size);
     sql_buf = (char*)malloc(sql_buffer_size);
     if (sql_buf == NULL)
     {
3b0c06ef
 		PKG_MEM_ERROR;
5cd87175
         return -1;
     }
     return 0;
 }
09c9abb0
 
6c39a678
 static int db_fetch_query_internal(db_func_t *dbf, int frows,
09c9abb0
 		db1_con_t* _h, const db_key_t* _k, const db_op_t* _op,
 		const db_val_t* _v, const db_key_t* _c, const int _n, const int _nc,
6c39a678
 		const db_key_t _o, db1_res_t** _r, db_query_f _query)
09c9abb0
 {
 
 	int ret;
 
6c39a678
 	if (!_query) {
 		LM_ERR("bad query function pointer\n");
 		goto error;
 	}
 
09c9abb0
 	ret = 0;
 	*_r = NULL;
 
 	if (DB_CAPABILITY(*dbf, DB_CAP_FETCH)) {
6c39a678
 		if(_query(_h, _k, _op, _v, _c, _n, _nc, _o, 0) < 0)
09c9abb0
 		{
 			LM_ERR("unable to query db for fetch\n");
 			goto error;
 		}
 		if(dbf->fetch_result(_h, _r, frows)<0)
 		{
 			LM_ERR("unable to fetch the db result\n");
 			goto error;
 		}
 		ret = 1;
 	} else {
6c39a678
 		if(_query(_h, _k, _op, _v, _c, _n, _nc, _o, _r) < 0)
09c9abb0
 		{
 			LM_ERR("unable to do full db querry\n");
 			goto error;
 		}
 	}
 
 	return ret;
 
 error:
 	if(*_r)
 	{
 		dbf->free_result(_h, *_r);
 		*_r = NULL;
 	}
 	return -1;
 }
 
6c39a678
 /**
  * wrapper around db query to handle fetch capability
  * return: -1 error; 0 ok with no fetch capability; 1 ok with fetch capability
  */
 int db_fetch_query(db_func_t *dbf, int frows,
 		db1_con_t* _h, const db_key_t* _k, const db_op_t* _op,
 		const db_val_t* _v, const db_key_t* _c, const int _n, const int _nc,
 		const db_key_t _o, db1_res_t** _r)
 {
 	return db_fetch_query_internal(dbf, frows, _h, _k, _op, _v, _c, _n,
 					_nc, _o, _r, dbf->query);
 }
 
 /**
  * wrapper around db query_lock to handle fetch capability
  * return: -1 error; 0 ok with no fetch capability; 1 ok with fetch capability
  */
 int db_fetch_query_lock(db_func_t *dbf, int frows,
 		db1_con_t* _h, const db_key_t* _k, const db_op_t* _op,
 		const db_val_t* _v, const db_key_t* _c, const int _n, const int _nc,
 		const db_key_t _o, db1_res_t** _r)
 {
 	if (!dbf->query_lock)
 	{
 		LM_ERR("query_lock not supported by this database module\n");
 		return -1;
 	}
 
 	return db_fetch_query_internal(dbf, frows, _h, _k, _op, _v, _c, _n,
 					_nc, _o, _r, dbf->query_lock);
 }
 
09c9abb0
 /**
  * wrapper around db fetch to handle fetch capability
  * return: -1 error; 0 ok with no fetch capability; 1 ok with fetch capability
  */
 int db_fetch_next(db_func_t *dbf, int frows, db1_con_t* _h,
 		db1_res_t** _r)
 {
 	int ret;
 
 	ret = 0;
 
 	if (DB_CAPABILITY(*dbf, DB_CAP_FETCH)) {
 		if(dbf->fetch_result(_h, _r, frows)<0) {
 			LM_ERR("unable to fetch next rows\n");
 			goto error;
 		}
 		ret = 1;
 	}
 	return ret;
 
 error:
 	if(*_r)
 	{
 		dbf->free_result(_h, *_r);
 		*_r = NULL;
 	}
 	return -1;
 }