/*
* Insert a row into specified table
* _con: structure representing database connection
* _k: key names
* _v: values of the keys
* _n: number of key=value pairs
*/
int db_postgres_insert(const db_con_t* _h, const db_key_t* _k,
const db_val_t* _v, const int _n)
{
db_res_t* _r = NULL;
CON_RESET_CURR_PS(_h); /* no prepared statements support */
int tmp = db_do_insert(_h, _k, _v, _n, db_postgres_val2str,
db_postgres_submit_query);
if (submit_func_called)
{
/* finish the async query,
* otherwise the next query will not complete */
/* only call this if the DB API has effectively called
* our submit_query function
*
* in case of insert queueing,
* it may postpone calling the insert func until
* enough rows have piled up */
if (db_postgres_store_result(_h, &_r) != 0)
LM_WARN("unexpected result returned\n");
submit_func_called = 0;
}
if (_r)
db_free_result(_r);
if (CON_HAS_INSLIST(_h))
CON_RESET_INSLIST(_h);
return tmp;
}
/*
* Insert a row into specified table
* _con: structure representing database connection
* _k: key names
* _v: values of the keys
* _n: number of key=value pairs
*/
int db_postgres_insert(const db_con_t* _h, const db_key_t* _k,
const db_val_t* _v, const int _n)
{
db_res_t* _r = NULL;
CON_RESET_CURR_PS(_h); /* no prepared statements support */
/* This needs to be reset before each call to db_do_insert.
This is only used by inserts, but as a side effect delete and updates
will set it to 1 without resetting it. */
submit_func_called = 0;
int tmp = db_do_insert(_h, _k, _v, _n, db_postgres_val2str,
db_postgres_submit_query);
/* For bulk queries the insert may not be submitted until enough rows are queued */
if (submit_func_called)
{
/* Query was submitted.
Result must be handled. */
if (db_postgres_store_result(_h, &_r) != 0)
LM_WARN("unexpected result returned\n");
}
if (_r)
db_free_result(_r);
if (CON_HAS_INSLIST(_h))
CON_RESET_INSLIST(_h);
return tmp;
}
/*
* Update some rows in the specified table
* _h: structure representing database connection
* _k: key names
* _o: operators
* _v: values of the keys that must match
* _uk: updated columns
* _uv: updated values of the columns
* _n: number of key=value pairs
* _un: number of columns to update
*/
int db_oracle_update(const db_con_t* _h, const db_key_t* _k, const db_op_t* _o,
const db_val_t* _v, const db_key_t* _uk, const db_val_t* _uv,
int _n, int _un)
{
query_data_t cb;
int rc;
if (!_h || !CON_TABLE(_h)) {
LM_ERR("invalid parameter value\n");
return -1;
}
cb._rs = NULL;
cb._v = _uv;
cb._n = _un;
cb._w = _v;
cb._nw = _n;
CON_ORA(_h)->pqdata = &cb;
CON_ORA(_h)->bindpos = 0;
CON_RESET_CURR_PS(_h); /* no prepared statements support */
rc = db_do_update(_h, _k, _o, _v, _uk, _uv, _n, _un,
db_oracle_val2str, db_oracle_submit_query);
CON_ORA(_h)->pqdata = NULL; /* paranoid for next call */
return rc;
}
/*
* Query table for specified rows
* _h: structure representing database connection
* _k: key names
* _op: operators
* _v: values of the keys that must match
* _c: column names to return
* _n: number of key=values pairs to compare
* _nc: number of columns to return
* _o: order by the specified column
*/
int db_oracle_query(const db_con_t* _h, const db_key_t* _k, const db_op_t* _op,
const db_val_t* _v, const db_key_t* _c, int _n, int _nc,
const db_key_t _o, db_res_t** _r)
{
query_data_t cb;
OCIStmt* reshp;
int rc;
if (!_h || !CON_TABLE(_h) || !_r) {
LM_ERR("invalid parameter value\n");
return -1;
}
cb._rs = &reshp;
cb._v = _v;
cb._n = _n;
cb._w = NULL;
cb._nw = 0;
CON_ORA(_h)->pqdata = &cb;
CON_ORA(_h)->bindpos = 0;
CON_RESET_CURR_PS(_h); /* no prepared statements support */
rc = db_do_query(_h, _k, _op, _v, _c, _n, _nc, _o, _r,
db_oracle_val2str, db_oracle_submit_query, db_oracle_store_result);
CON_ORA(_h)->pqdata = NULL; /* paranoid for next call */
return rc;
}
/*
* Just like insert, but replace the row if it exists
*/
int db_unixodbc_replace(const db_con_t* _h, const db_key_t* _k,
const db_val_t* _v, const int _n)
{
CON_RESET_CURR_PS(_h); /* no prepared statements support */
return db_do_replace(_h, _k, _v, _n, db_unixodbc_val2str,
db_unixodbc_submit_query);
}
/*
* Query table for specified rows
* _con: structure representing database connection
* _k: key names
* _op: operators
* _v: values of the keys that must match
* _c: column names to return
* _n: nmber of key=values pairs to compare
* _nc: number of columns to return
* _o: order by the specified column
*/
int db_postgres_query(const db_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, db_res_t** _r)
{
CON_RESET_CURR_PS(_h); /* no prepared statements support */
return db_do_query(_h, _k, _op, _v, _c, _n, _nc, _o, _r,
db_postgres_val2str, db_postgres_submit_query,
db_postgres_store_result);
}
/**
* Begins execution of a raw SQL query. Returns immediately.
*
* \param _h handle for the database
* \param _s raw query string
* \param _priv internal parameter; holds the conn that the query is bound to
* \return
* success: Unix FD for polling
* failure: negative error code
*/
int db_postgres_async_raw_query(db_con_t *_h, const str *_s, void **_priv)
{
int *fd_ref;
int code;
struct timeval start;
struct my_con *con;
if (!_h || !_s || !_s->s) {
LM_ERR("invalid parameter value\n");
return -1;
}
con = (struct my_con *)db_init_async(_h, db_postgres_get_con_fd,
&fd_ref, (void *)db_postgres_new_async_connection);
*_priv = con;
if (!con)
LM_INFO("Failed to open new connection (current: 1 + %d). Running "
"in sync mode!\n", ((struct pool_con *)_h->tail)->no_transfers);
/* no prepared statements support */
CON_RESET_CURR_PS(_h);
start_expire_timer(start, db_postgres_exec_query_threshold);
/* async mode */
if (con) {
code = db_postgres_submit_async_query(_h, _s);
/* sync mode */
} else {
code = db_postgres_submit_query(_h, _s);
}
_stop_expire_timer(start, db_postgres_exec_query_threshold,
"pgsql async query", _s->s, _s->len, 0,
sql_slow_queries, sql_total_queries);
if (code < 0) {
LM_ERR("failed to send postgres query %.*s",_s->len,_s->s);
goto out;
} else {
/* success */
if (!con)
return -1;
*fd_ref = db_postgres_get_con_fd(con);
db_switch_to_sync(_h);
return *fd_ref;
}
out:
if (!con)
return -1;
db_switch_to_sync(_h);
db_store_async_con(_h, (struct pool_con *)con);
return -2;
}
int db_sqlite_query(const db_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, db_res_t** _r)
{
int ret=-1;
#ifdef SQLITE_BIND
db_ps_t ps;
CON_SET_CURR_PS(_h, &ps);
#else
CON_RESET_CURR_PS(_h);
#endif
CON_RAW_QUERY(_h) = 0;
ret = db_do_query(_h, _k, _op, _v, _c, _n, _nc, _o, NULL,
db_sqlite_val2str, db_sqlite_submit_dummy_query, NULL);
if (ret != 0) {
if (_r)
*_r = NULL;
return ret;
}
if (db_copy_rest_of_count(&query_holder, &count_str)) {
LM_ERR("failed to build row counter query\n");
return -1;
}
again:
ret=sqlite3_prepare_v2(CON_CONNECTION(_h),
query_holder.s, query_holder.len, &CON_SQLITE_PS(_h), NULL);
if (ret==SQLITE_BUSY)
goto again;
if (ret!=SQLITE_OK)
LM_ERR("failed to prepare: (%s)\n", sqlite3_errmsg(CON_CONNECTION(_h)));
#ifdef SQLITE_BIND
if (db_sqlite_bind_values(CON_SQLITE_PS(_h), _v, _n) != SQLITE_OK) {
LM_ERR("failed to bind values\n");
return -1;
}
#endif
if (_r) {
ret = db_sqlite_store_result(_h, _r, _v, _n);
CON_SQLITE_PS(_h) = NULL;
} else {
/* need to fetch now the total number of rows in query
* because later won't have the query string */
CON_PS_ROWS(_h) = db_sqlite_get_query_rows(_h, &count_str, _v, _n);
}
return ret;
}
/**
* Update some rows in the specified table
* \param _h structure representing database connection
* \param _k key names
* \param _o operators
* \param _v values of the keys that must match
* \param _uk updated columns
* \param _uv updated values of the columns
* \param _n number of key=value pairs
* \param _un number of columns to update
* \return zero on success, negative value on failure
*/
int db_sqlite_update(const db_con_t* _h, const db_key_t* _k, const db_op_t* _o,
const db_val_t* _v, const db_key_t* _uk, const db_val_t* _uv, const int _n,
const int _un)
{
int ret;
sqlite3_stmt* stmt;
#ifdef SQLITE_BIND
db_ps_t ps;
CON_SET_CURR_PS(_h, &ps);
#else
CON_RESET_CURR_PS(_h);
#endif
ret = db_do_update(_h, _k, _o, _v, _uk, _uv, _n, _un,
db_sqlite_val2str, db_sqlite_submit_dummy_query);
if (ret != 0) {
return ret;
}
again:
ret=sqlite3_prepare_v2(CON_CONNECTION(_h),
query_holder.s, query_holder.len, &stmt, NULL);
if (ret==SQLITE_BUSY)
goto again;
if (ret!=SQLITE_OK)
LM_ERR("failed to prepare: (%s)\n",
sqlite3_errmsg(CON_CONNECTION(_h)));
#ifdef SQLITE_BIND
if (db_sqlite_bind_values(stmt, _uv, _un) != SQLITE_OK
&& db_sqlite_bind_values(stmt, _v, _n)) {
LM_ERR("failed to bind values\n");
return -1;
}
#endif
again2:
ret = sqlite3_step(stmt);
if (ret==SQLITE_BUSY)
goto again2;
if (ret != SQLITE_DONE) {
LM_ERR("insert query failed %s\n", sqlite3_errmsg(CON_CONNECTION(_h)));
return -1;
}
sqlite3_finalize(stmt);
return 0;
}
/*
* Delete a row from the specified table
* _con: structure representing database connection
* _k: key names
* _o: operators
* _v: values of the keys that must match
* _n: number of key=value pairs
*/
int db_postgres_delete(const db_con_t* _h, const db_key_t* _k,
const db_op_t* _o, const db_val_t* _v, const int _n)
{
db_res_t* _r = NULL;
CON_RESET_CURR_PS(_h); /* no prepared statements support */
int tmp = db_do_delete(_h, _k, _o, _v, _n, db_postgres_val2str,
db_postgres_submit_query);
if (db_postgres_store_result(_h, &_r) != 0)
LM_WARN("unexpected result returned");
if (_r)
db_free_result(_r);
return tmp;
}
/**
* Execute a raw SQL query.
* \param _h handle for the database
* \param _s raw query string
* \param _r result set for storage
* \return zero on success, negative value on failure
*/
int db_sqlite_raw_query(const db_con_t* _h, const str* _s, db_res_t** _r)
{
int ret=-1;
char* errmsg;
str select_str={"select", 6};
CON_RESET_CURR_PS(_h);
if (!str_strstr(_s, &select_str)) {
/* not a select statement; can execute the query and exit*/
if (sqlite3_exec(CON_CONNECTION(_h),
query_holder.s, NULL, NULL, &errmsg)) {
LM_ERR("query failed: %s\n", errmsg);
return -2;
}
return 0;
}
CON_RAW_QUERY(_h) = 1;
if (db_copy_rest_of_count(_s, &count_str)) {
LM_ERR("failed to build count str!\n");
return -1;
}
again:
ret=sqlite3_prepare_v2(CON_CONNECTION(_h),
_s->s, _s->len, &CON_SQLITE_PS(_h), NULL);
if (ret==SQLITE_BUSY)
goto again;
if (ret!=SQLITE_OK)
LM_ERR("failed to prepare: (%s)\n",
sqlite3_errmsg(CON_CONNECTION(_h)));
if (_r) {
ret = db_sqlite_store_result(_h, _r, NULL, 0);
} else {
/* need to fetch now the total number of rows in query
* because later won't have the query string */
CON_PS_ROWS(_h) = db_sqlite_get_query_rows(_h, &count_str, NULL, 0);
}
return ret;
}
/*
* Execute a raw SQL query
*/
int db_oracle_raw_query(const db_con_t* _h, const str* _s, db_res_t** _r)
{
query_data_t cb;
OCIStmt* reshp;
int len;
const char *p;
if (!_h || !_s || !_s->s) {
badparam:
LM_ERR("invalid parameter value\n");
return -1;
}
CON_RESET_CURR_PS(_h); /* no prepared statements support */
memset(&cb, 0, sizeof(cb));
p = _s->s;
len = _s->len;
while (len && *p == ' ') ++p, --len;
#define _S_DIFF(p, l, S) (l <= sizeof(S)-1 || strncasecmp(p, S, sizeof(S)-1))
if (!_S_DIFF(p, len, "select ")) {
if (!_r) goto badparam;
cb._rs = &reshp;
} else {
if ( _S_DIFF(p, len, "insert ")
&& _S_DIFF(p, len, "delete ")
&& _S_DIFF(p, len, "update "))
{
LM_ERR("unknown raw_query: '%.*s'\n", _s->len, _s->s);
return -2;
}
#undef _S_DIFF
if (_r) goto badparam;
cb._rs = NULL;
}
len = db_do_raw_query(_h, _s, _r, db_oracle_submit_query, db_oracle_store_result);
CON_ORA(_h)->pqdata = NULL; /* paranoid for next call */
return len;
}
/**
* Execute a raw SQL query.
* \param _h handle for the database
* \param _s raw query string
* \param _r result set for storage
* \return zero on success, negative value on failure
*/
int db_sqlite_raw_query(const db_con_t* _h, const str* _s, db_res_t** _r)
{
static char sql_str[SQL_BUF_LEN];
int ret=-1, i=0;
char* errmsg;
str select_str={"select", 6};
str _scpy;
CON_RESET_CURR_PS(_h);
while (i < _s->len && !isalpha(_s->s[i])) i++;
/* if any blank spaces or anything else before the actual query */
if (i) {
_scpy.s = _s->s+i;
_scpy.len = _s->len-i;
} else {
_scpy = *_s;
}
if (_scpy.len >= select_str.len &&
str_strncasecmp(&_scpy, &select_str, select_str.len)) {
/* not a select statement; can execute the query and exit*/
if (_s->len + 1 > SQL_BUF_LEN) {
LM_ERR("query too big! try reducing the size of your query!"
"Current max size [%d]!\n", SQL_BUF_LEN);
return -1;
}
memcpy(sql_str, _s->s, _s->len);
sql_str[_s->len] = '\0';
if (sqlite3_exec(CON_CONNECTION(_h),
sql_str, NULL, NULL, &errmsg)) {
LM_ERR("query failed: %s\n", errmsg);
return -2;
}
return 0;
}
CON_RAW_QUERY(_h) = 1;
if (db_copy_rest_of_count(&_scpy, &count_str)) {
LM_ERR("failed to build count str!\n");
return -1;
}
again:
ret=sqlite3_prepare_v2(CON_CONNECTION(_h),
_s->s, _s->len, &CON_SQLITE_PS(_h), NULL);
if (ret==SQLITE_BUSY)
goto again;
if (ret!=SQLITE_OK)
LM_ERR("failed to prepare: (%s)\n",
sqlite3_errmsg(CON_CONNECTION(_h)));
if (_r) {
ret = db_sqlite_store_result(_h, _r, NULL, 0);
} else {
/* need to fetch now the total number of rows in query
* because later won't have the query string */
CON_PS_ROWS(_h) = db_sqlite_get_query_rows(_h, &count_str, NULL, 0);
}
return ret;
}
/*
* Execute a raw SQL query
*/
int db_postgres_raw_query(const db_con_t* _h, const str* _s, db_res_t** _r)
{
CON_RESET_CURR_PS(_h); /* no prepared statements support */
return db_do_raw_query(_h, _s, _r, db_postgres_submit_query,
db_postgres_store_result);
}
