/**
* Insert a row into a specified table, update on duplicate key.
* \param _h structure representing database connection
* \param _k key names
* \param _v values of the keys
* \param _n number of key=value pairs
*/
int db_insert_update(const db1_con_t* _h, const db_key_t* _k, const db_val_t* _v,
const int _n)
{
int off, ret;
static str sql_str;
static char sql_buf[SQL_BUF_LEN];
if ((!_h) || (!_k) || (!_v) || (!_n)) {
LM_ERR("invalid parameter value\n");
return -1;
}
ret = snprintf(sql_buf, SQL_BUF_LEN, "insert into %.*s (", CON_TABLE(_h)->len, CON_TABLE(_h)->s);
if (ret < 0 || ret >= SQL_BUF_LEN) goto error;
off = ret;
ret = db_print_columns(sql_buf + off, SQL_BUF_LEN - off, _k, _n);
if (ret < 0) return -1;
off += ret;
ret = snprintf(sql_buf + off, SQL_BUF_LEN - off, ") values (");
if (ret < 0 || ret >= (SQL_BUF_LEN - off)) goto error;
off += ret;
ret = db_print_values(_h, sql_buf + off, SQL_BUF_LEN - off, _v, _n, db_mysql_val2str);
if (ret < 0) return -1;
off += ret;
*(sql_buf + off++) = ')';
ret = snprintf(sql_buf + off, SQL_BUF_LEN - off, " on duplicate key update ");
if (ret < 0 || ret >= (SQL_BUF_LEN - off)) goto error;
off += ret;
ret = db_print_set(_h, sql_buf + off, SQL_BUF_LEN - off, _k, _v, _n, db_mysql_val2str);
if (ret < 0) return -1;
off += ret;
sql_str.s = sql_buf;
sql_str.len = off;
if (db_mysql_submit_query(_h, &sql_str) < 0) {
LM_ERR("error while submitting query\n");
return -2;
}
return 0;
error:
LM_ERR("error while preparing insert_update operation\n");
return -1;
}
int db_do_insert_cmd(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), int mode)
{
int off, ret;
if (!_h || !_k || !_v || !_n || !val2str || !submit_query) {
LM_ERR("invalid parameter value\n");
return -1;
}
if(mode==1)
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));
else
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));
if (ret < 0 || ret >= sql_buffer_size) goto error;
off = ret;
ret = db_print_columns(sql_buf + off, sql_buffer_size - off, _k, _n, CON_TQUOTESZ(_h));
if (ret < 0) return -1;
off += ret;
ret = snprintf(sql_buf + off, sql_buffer_size - off, ") values (");
if (ret < 0 || ret >= (sql_buffer_size - off)) goto error;
off += ret;
ret = db_print_values(_h, sql_buf + off, sql_buffer_size - off, _v, _n, val2str);
if (ret < 0) return -1;
off += ret;
if (off + 2 > sql_buffer_size) goto error;
sql_buf[off++] = ')';
sql_buf[off] = '\0';
sql_str.s = sql_buf;
sql_str.len = off;
if (db_do_submit_query(_h, &sql_str, submit_query) < 0) {
LM_ERR("error while submitting query\n");
return -2;
}
return 0;
error:
LM_ERR("error while preparing insert operation\n");
return -1;
}
/*
* Insert a row into specified table, update on duplicate key
* _h: structure representing database connection
* _k: key names
* _v: values of the keys
* _n: number of key=value pairs
*/
int db_insert_update(db_con_t* _h, db_key_t* _k, db_val_t* _v, int _n)
{
int off, ret;
if ((!_h) || (!_k) || (!_v) || (!_n)) {
LM_ERR("invalid parameter value\n");
return -1;
}
ret = snprintf(sql_buf, SQL_BUF_LEN, "insert into %s (", CON_TABLE(_h));
if (ret < 0 || ret >= SQL_BUF_LEN) goto error;
off = ret;
ret = db_print_columns(sql_buf + off, SQL_BUF_LEN - off, _k, _n);
if (ret < 0) return -1;
off += ret;
ret = snprintf(sql_buf + off, SQL_BUF_LEN - off, ") values (");
if (ret < 0 || ret >= (SQL_BUF_LEN - off)) goto error;
off += ret;
ret = db_print_values(_h, sql_buf + off, SQL_BUF_LEN - off, _v, _n, val2str);
if (ret < 0) return -1;
off += ret;
*(sql_buf + off++) = ')';
ret = snprintf(sql_buf + off, SQL_BUF_LEN - off, " ON DUPLICATE KEY UPDATE ");
if (ret < 0 || ret >= (SQL_BUF_LEN - off)) goto error;
off += ret;
ret = db_print_set(_h, sql_buf + off, SQL_BUF_LEN - off, _k, _v, _n, val2str);
if (ret < 0) return -1;
off += ret;
*(sql_buf + off) = '\0';
if (db_mysql_submit_query(_h, sql_buf) < 0) {
LM_ERR("error while submitting query\n");
return -2;
}
return 0;
error:
LM_ERR("error in snprintf\n");
return -1;
}
int db_do_replace( db_con_t* _h, db_key_t* _k, db_val_t* _v,
int _n, int (*val2str) ( db_con_t*, db_val_t*, char*,
int*), int (*submit_query)( db_con_t* _h, str* _c))
{
int off, ret;
str sql_str;
char sql_buf[SQL_BUF_LEN];
if (!_h || !_k || !_v || !val2str|| !submit_query) {
LM_ERR("invalid parameter value\n");
return -1;
}
ret = snprintf(sql_buf, SQL_BUF_LEN, "replace %.*s (", CON_TABLE(_h)->len, CON_TABLE(_h)->s);
if (ret < 0 || ret >= SQL_BUF_LEN) goto error;
off = ret;
ret = db_print_columns(sql_buf + off, SQL_BUF_LEN - off, _k, _n);
if (ret < 0) return -1;
off += ret;
ret = snprintf(sql_buf + off, SQL_BUF_LEN - off, ") values (");
if (ret < 0 || ret >= (SQL_BUF_LEN - off)) goto error;
off += ret;
ret = db_print_values(_h, sql_buf + off, SQL_BUF_LEN - off, _v, _n,
val2str);
if (ret < 0) return -1;
off += ret;
if (off + 2 > SQL_BUF_LEN) goto error;
sql_buf[off++] = ')';
sql_buf[off] = '\0';
sql_str.s = sql_buf;
sql_str.len = off;
if (submit_query(_h, &sql_str) < 0) {
LM_ERR("error while submitting query\n");
return -2;
}
return 0;
error:
LM_ERR("error while preparing replace operation\n");
return -1;
}
/*
* Just like insert, but replace the row if it exists
*/
int db_mysql_replace(db_con_t* handle, db_key_t* keys, db_val_t* vals, int n)
{
int off, ret;
if (!handle || !keys || !vals) {
LM_ERR("invalid parameter value\n");
return -1;
}
ret = snprintf(sql_buf, SQL_BUF_LEN, "replace %s (", CON_TABLE(handle));
if (ret < 0 || ret >= SQL_BUF_LEN) goto error;
off = ret;
ret = db_print_columns(sql_buf + off, SQL_BUF_LEN - off, keys, n);
if (ret < 0) return -1;
off += ret;
ret = snprintf(sql_buf + off, SQL_BUF_LEN - off, ") values (");
if (ret < 0 || ret >= (SQL_BUF_LEN - off)) goto error;
off += ret;
ret = db_print_values(handle, sql_buf + off, SQL_BUF_LEN - off, vals, n, val2str);
if (ret < 0) return -1;
off += ret;
*(sql_buf + off++) = ')';
*(sql_buf + off) = '\0';
if (db_mysql_submit_query(handle, sql_buf) < 0) {
LM_ERR("error while submitting query\n");
return -2;
}
return 0;
error:
LM_ERR("error in snprintf\n");
return -1;
}
/**
* Insert a row into a specified table, update on duplicate key.
* \param _h structure representing database connection
* \param _k key names
* \param _v values of the keys
* \param _n number of key=value pairs
*/
int db_insert_update(const db_con_t* _h, const db_key_t* _k, const db_val_t* _v,
const int _n)
{
#define SQL_BUF_LEN 65536
int off, ret;
static str sql_str;
static char sql_buf[SQL_BUF_LEN];
sqlite3_stmt* stmt;
if ((!_h) || (!_k) || (!_v) || (!_n)) {
LM_ERR("invalid parameter value\n");
return -1;
}
#ifdef SQLITE_BIND
db_ps_t ps;
CON_SET_CURR_PS(_h, &ps);
#endif
ret = snprintf(sql_buf, SQL_BUF_LEN, "insert into %.*s (",
CON_TABLE(_h)->len, CON_TABLE(_h)->s);
if (ret < 0 || ret >= SQL_BUF_LEN) goto error;
off = ret;
ret = db_print_columns(sql_buf + off, SQL_BUF_LEN - off, _k, _n);
if (ret < 0) return -1;
off += ret;
ret = snprintf(sql_buf + off, SQL_BUF_LEN - off, ") values (");
if (ret < 0 || ret >= (SQL_BUF_LEN - off)) goto error;
off += ret;
ret = db_print_values(_h, sql_buf + off, SQL_BUF_LEN - off, _v, _n,
db_sqlite_val2str);
if (ret < 0) return -1;
off += ret;
*(sql_buf + off++) = ')';
ret = snprintf(sql_buf + off, SQL_BUF_LEN - off, " on duplicate key update ");
if (ret < 0 || ret >= (SQL_BUF_LEN - off)) goto error;
off += ret;
ret = db_print_set(_h, sql_buf + off, SQL_BUF_LEN - off, _k, _v, _n,
db_sqlite_val2str);
if (ret < 0) return -1;
off += ret;
sql_str.s = sql_buf;
sql_str.len = off;
again:
ret=sqlite3_prepare_v2(CON_CONNECTION(_h),
sql_str.s, sql_str.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, _v, _n) != SQLITE_OK) {
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;
#undef SQL_BUF_LEN
error:
LM_ERR("error while preparing insert_update operation\n");
return -1;
}
int db_do_insert(const db_con_t* _h, const db_key_t* _k, const db_val_t* _v,
const int _n, int (*val2str) (const db_con_t*, const db_val_t*, char*, int*),
int (*submit_query)(const db_con_t* _h, const str* _c))
{
int off, ret,i,no_rows=0;
db_val_t **buffered_rows = NULL;
if (!_h || !_k || !_v || !_n || !val2str || !submit_query) {
LM_ERR("invalid parameter value\n");
return -1;
}
/* insert buffering is enabled ? */
if (CON_HAS_INSLIST(_h) && !CON_HAS_PS(_h))
{
LM_DBG("inlist %p\n",CON_HAS_INSLIST(_h));
if (IS_INSTANT_FLUSH(_h))
{
LM_DBG("timer wishing to flush \n");
/* if caller signals it's flush time ( timer, etc ),
* detach rows in queue
* the caller is holding the lock at this point */
no_rows = ql_detach_rows_unsafe(_h->ins_list,&buffered_rows);
CON_FLUSH_RESET(_h,_h->ins_list);
if (no_rows == -1)
{
LM_ERR("failed to detach rows for insertion\n");
goto error;
}
if (no_rows > 0)
goto build_query;
else
{
/* caller wanted to make sure that everything if flushed
* but queue is empty */
return 0;
}
}
/* if connection has prepared statement, leave
the row insertion to the proper module func,
as the submit_query func provided is a dummy one*/
if ( (no_rows = ql_row_add(_h->ins_list,_v,&buffered_rows)) < 0)
{
LM_ERR("failed to insert row to buffered list \n");
goto error;
}
LM_DBG("no rows = %d\n",no_rows);
if (no_rows == 0)
{
/* wait for queries to pile up */
return 0;
}
}
build_query:
ret = snprintf(sql_buf, SQL_BUF_LEN, "insert into %.*s (", CON_TABLE(_h)->len, CON_TABLE(_h)->s);
if (ret < 0 || ret >= SQL_BUF_LEN) goto error;
off = ret;
ret = db_print_columns(sql_buf + off, SQL_BUF_LEN - off, _k, _n);
if (ret < 0) goto error;
off += ret;
if (CON_HAS_INSLIST(_h))
{
if (buffered_rows != NULL || CON_HAS_PS(_h))
{
/* if we have to insert now, build the query
*
* if a prep stmt is provided,
* build a prep stmt with query_buffer_size elements */
if (CON_HAS_PS(_h))
no_rows = query_buffer_size;
ret = snprintf(sql_buf + off, SQL_BUF_LEN - off, ") values");
if (ret < 0 || ret >= (SQL_BUF_LEN - off)) goto error;
off += ret;
for (i=0;i<no_rows;i++)
{
sql_buf[off++]='(';
ret = db_print_values(_h, sql_buf + off, SQL_BUF_LEN - off,
CON_HAS_PS(_h)?_v:buffered_rows[i], _n, val2str);
if (ret < 0) goto error;
off += ret;
sql_buf[off++]=')';
if (i != (no_rows -1))
sql_buf[off++]=',';
/* if we have a PS, leave the function handling prep stmts
in the module to free the rows once it's done */
if (!CON_HAS_PS(_h)) {
shm_free(buffered_rows[i]);
buffered_rows[i] = NULL;
}
}
if (off + 1 > SQL_BUF_LEN) goto error0;
sql_buf[off] = '\0';
sql_str.s = sql_buf;
sql_str.len = off;
goto submit;
}
else
{
/* wait for queries to pile up */
return 0;
}
}
else
{
ret = snprintf(sql_buf + off, SQL_BUF_LEN - off, ") values (");
if (ret < 0 || ret >= (SQL_BUF_LEN - off)) goto error0;
off += ret;
ret = db_print_values(_h, sql_buf + off, SQL_BUF_LEN - off, _v, _n, val2str);
if (ret < 0) goto error0;
off += ret;
if (off + 2 > SQL_BUF_LEN) goto error0;
}
sql_buf[off++] = ')';
sql_buf[off] = '\0';
sql_str.s = sql_buf;
sql_str.len = off;
submit:
if (submit_query(_h, &sql_str) < 0) {
LM_ERR("error while submitting query\n");
return -2;
}
return 0;
error:
cleanup_rows(buffered_rows);
error0:
LM_ERR("error while preparing insert operation\n");
return -1;
}
int db_do_insert( db_con_t* _h, db_key_t* _k, db_val_t* _v,
int _n, int (*val2str) ( db_con_t*, db_val_t*, char*, int*),
int (*submit_query)( db_con_t* _h, str* _c),str *query_holder)
{
int off, ret;
str sql_str;
char sql_buf[SQL_BUF_LEN];
if (!_h || !_k || !_v || !_n || !val2str || (!submit_query && !query_holder)) {
LM_ERR("invalid parameter value\n");
return -1;
}
ret = snprintf(sql_buf, SQL_BUF_LEN, "insert into %.*s (", CON_TABLE(_h)->len, CON_TABLE(_h)->s);
if (ret < 0 || ret >= SQL_BUF_LEN) goto error;
off = ret;
ret = db_print_columns(sql_buf + off, SQL_BUF_LEN - off, _k, _n);
if (ret < 0) return -1;
off += ret;
ret = snprintf(sql_buf + off, SQL_BUF_LEN - off, ") values (");
if (ret < 0 || ret >= (SQL_BUF_LEN - off)) goto error;
off += ret;
ret = db_print_values(_h, sql_buf + off, SQL_BUF_LEN - off, _v, _n, val2str);
if (ret < 0) return -1;
off += ret;
if (off + 2 > SQL_BUF_LEN) goto error;
sql_buf[off++] = ')';
sql_buf[off] = '\0';
sql_str.s = sql_buf;
sql_str.len = off;
if (submit_query)
{
if (submit_query(_h, &sql_str) < 0)
{
LM_ERR("error while submitting query\n");
return -2;
}
}
else
{
query_holder->s = pkg_malloc(off);
if (!query_holder->s)
{
LM_ERR("no more pkg mem\n");
return -2;
}
memcpy(query_holder->s,sql_buf,off);
query_holder->len = off;
}
return 0;
error:
LM_ERR("error while preparing insert operation\n");
return -1;
}
