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;
}
/**
* 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.
* \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;
}
/**
* Gets a partial result set.
* \param _h structure representing the database connection
* \param _r pointer to a structure representing the result
* \param nrows number of fetched rows
* \return zero on success, negative value on failure
*/
int db_sqlite_fetch_result(const db_con_t* _h, db_res_t** _r, const int nrows)
{
int ret;
int rows, i;
sqlite3_stmt* stmt;
if (!_h || !_r || nrows < 0) {
LM_ERR("Invalid parameter value\n");
return -1;
}
/* exit if the fetch count is zero */
if (nrows == 0) {
db_free_result(*_r);
*_r = 0;
return 0;
}
if(*_r==0) {
/* Allocate a new result structure */
*_r = db_new_result();
if (*_r == 0) {
LM_ERR("no memory left\n");
return -2;
}
if (db_sqlite_get_columns(_h, *_r) < 0) {
LM_ERR("error while getting column names\n");
return -4;
}
RES_NUM_ROWS(*_r) = CON_PS_ROWS(_h);
if (!RES_NUM_ROWS(*_r)) {
LM_DBG("no rows returned from the query\n");
RES_ROWS(*_r) = 0;
return 0;
}
} else {
/* free old rows */
if(RES_ROWS(*_r)!=0)
db_free_rows(*_r);
RES_ROWS(*_r) = 0;
RES_ROW_N(*_r) = 0;
}
/* determine the number of rows remaining to be processed */
rows = RES_NUM_ROWS(*_r) - RES_LAST_ROW(*_r);
/* If there aren't any more rows left to process, exit */
if(rows<=0)
return 0;
/* if the fetch count is less than the remaining rows to process */
/* set the number of rows to process (during this call) equal
to the fetch count */
if(nrows < rows)
rows = nrows;
RES_ROW_N(*_r) = rows;
if (db_sqlite_allocate_rows(*_r, rows)!=0) {
LM_ERR("no memory left\n");
return -5;
}
i = 0;
ret=-1;
stmt = CON_SQLITE_PS(_h);
while (ret != SQLITE_DONE) {
if (i == nrows) {
RES_LAST_ROW(*_r) = i - 1;
break;
}
ret = sqlite3_step(stmt);
if (ret == SQLITE_DONE) {
RES_ROW_N(*_r) = RES_LAST_ROW(*_r) = RES_NUM_ROWS(*_r) = i;
sqlite3_finalize(CON_SQLITE_PS(_h));
CON_SQLITE_PS(_h) = NULL;
break;
}
if (i >= RES_ROW_N(*_r) && i < nrows) {
db_sqlite_realloc_rows(*_r, RES_ROW_N(*_r) + db_sqlite_alloc_limit);
RES_ROW_N(*_r) += db_sqlite_alloc_limit;
}
if ((ret=db_sqlite_convert_row(_h, *_r, &(RES_ROWS(*_r)[i]))) < 0) {
LM_ERR("error while converting row #%d\n", i);
RES_ROW_N(*_r) = i;
db_free_rows(*_r);
return -4;
}
i++;
}
return 0;
}
/**
* Convert a row from result into db API representation
*/
int db_sqlite_convert_row(const db_con_t* _h, db_res_t* _res, db_row_t* _r)
{
int col;
db_val_t* _v;
const char* db_value;
if ((!_h) || (!_res) || (!_r)) {
LM_ERR("invalid parameter value\n");
return -1;
}
if (!CON_SQLITE_PS(_h)) {
LM_ERR("conn has no prepared statement! sqlite requires one\n");
return -1;
}
/* Save the number of columns in the ROW structure */
ROW_N(_r) = RES_COL_N(_res);
for(col=0; col < RES_COL_N(_res); col++) {
_v = &(ROW_VALUES(_r)[col]);
if (sqlite3_column_type(CON_SQLITE_PS(_h), col) == SQLITE_NULL) {
VAL_NULL(_v) = 1;
continue;
}
switch (RES_TYPES(_res)[col]) {
case DB_INT:
VAL_INT(_v) = sqlite3_column_int(CON_SQLITE_PS(_h), col);
VAL_TYPE(_v) = DB_INT;
break;
case DB_BIGINT:
VAL_BIGINT(_v) = sqlite3_column_int64(CON_SQLITE_PS(_h), col);
VAL_TYPE(_v) = DB_BIGINT;
break;
case DB_DATETIME:
db_value = (char *)sqlite3_column_text(CON_SQLITE_PS(_h), col);
if (db_str2time(db_value, &VAL_TIME(_v)) < 0) {
LM_ERR("error while converting datetime value from string\n");
return -1;
}
VAL_TYPE(_v) = DB_DATETIME;
break;
case DB_DOUBLE:
VAL_DOUBLE(_v) = sqlite3_column_double(CON_SQLITE_PS(_h), col);
VAL_TYPE(_v) = DB_DOUBLE;
break;
case DB_BLOB:
VAL_BLOB(_v).len = sqlite3_column_bytes(CON_SQLITE_PS(_h), col);
db_value = sqlite3_column_blob(CON_SQLITE_PS(_h), col);
VAL_BLOB(_v).s = pkg_malloc(VAL_BLOB(_v).len+1);
memcpy(VAL_BLOB(_v).s, db_value, VAL_BLOB(_v).len);
VAL_BLOB(_v).s[VAL_BLOB(_v).len]='\0';
VAL_TYPE(_v) = DB_BLOB;
break;
case DB_STRING:
VAL_STR(_v).len = sqlite3_column_bytes(CON_SQLITE_PS(_h), col);
db_value = (char *)sqlite3_column_text(CON_SQLITE_PS(_h), col);
VAL_STR(_v).s = pkg_malloc(VAL_STR(_v).len+1);
memcpy(VAL_STR(_v).s, db_value, VAL_STR(_v).len);
VAL_STR(_v).s[VAL_STR(_v).len]='\0';
VAL_TYPE(_v) = DB_STR;
break;
default:
LM_ERR("invalid type for sqlite!\n");
return -1;
}
}
return 0;
}
