[sr-dev] git:alexh/master: modules_k/sqlops: Add xavp support to sqlops

Module: sip-router Branch: alexh/master Commit: a1d2cedabf37e17e1d71839225c9652eef61b6eb URL: http://git.sip-router.org/cgi-bin/gitweb.cgi/sip-router/?a=commit;h=a1d2ced… Author: Alex Hermann &lt;alex(a)speakup.nl&gt; Committer: Alex Hermann &lt;alex(a)speakup.nl&gt; Date: Tue Dec 21 10:06:29 2010 +0100 modules_k/sqlops: Add xavp support to sqlops --- modules_k/sqlops/doc/sqlops_admin.xml | 72 ++++++++++++++++++- modules_k/sqlops/sql_api.c | 131 +++++++++++++++++++++++++++++++++ modules_k/sqlops/sql_api.h | 4 + modules_k/sqlops/sqlops.c | 61 +++++++++++++++ 4 files changed, 267 insertions(+), 1 deletions(-) diff --git a/modules_k/sqlops/doc/sqlops_admin.xml b/modules_k/sqlops/doc/sqlops_admin.xml index 997a457..ddb28df 100644 --- a/modules_k/sqlops/doc/sqlops_admin.xml +++ b/modules_k/sqlops/doc/sqlops_admin.xml @@ -60,6 +60,13 @@ times. </para> </listitem> + <listitem> + <para> + <emphasis>alternatively, results can be stored in xavps. Columns are + accessed by their names, rows by xavp index. Xavp's are available + during the transactions lifetime and don't need to be destroyed manually. + </para> + </listitem> </itemizedlist> </section> @@ -213,6 +220,48 @@ sql_result_free("ra"); </programlisting> </example> </section> + <title> + <function moreinfo="none">sql_query(connection, query, result)</function> + </title> + <para> + Make a SQL query using 'connection' and store data in 'result' xavp. + </para> + <itemizedlist> + <listitem> + <para> + <emphasis>connection</emphasis> - the name of the connection + to be used for query (defined via the <quote>sqlcon</quote> parameter). + </para> + </listitem> + <listitem> + <para> + <emphasis>query</emphasis> - SQL query string or pseudo-variables containing SQL query. + </para> + </listitem> + <listitem> + <para> + <emphasis>result</emphasis> - string name to identify the + result xavp. Each row will be added to this xavp, each column can + be accessed by its name. + </para> + </listitem> + </itemizedlist> + <para> + This function can be used from REQUEST_ROUTE, FAILURE_ROUTE, + ONREPLY_ROUTE, BRANCH_ROUTE. + </para> + <example> + <title><function>sql_xquery()</function> usage</title> + <programlisting format="linespecific"> +... +modparam("sqlops","sqlcon","ca=&gt;&exampledb;") +... +sql_xquery("ca", "select * from domain", "ra"); + xlog("first domain: $xavp(ra=>domain) with id: $xavp(ra=>domain_id)\n"); +... +</programlisting> + </example> + </section> <section> <title> <function moreinfo="none">sql_result_free(result)</function> @@ -306,7 +355,28 @@ if($dbr(ra=>rows)>0) } sql_result_free("ra"); ... - </programlisting> + + +... +if (sql_xquery("ca", "select * from domain", "ra") == 1) +{ +# non-destructive iteration + $var(i) = 0; + while($xavp(ra[$var(i)]) != $null) + { + xlog("[id, domain] = [$xavp(ra[$var(i)]=&gt;id), $xavp(ra[$var(i)]=&gt;domain)]\n"); + $var(i) = $var(i) + 1; + } + +# destructive iteration + while($xavp(ra) != $null) + { + xlog("[id, domain] = [$xavp(ra=&gt;id), $xavp(ra=&gt;domain)]\n"); + pv_unset("$xavp(ra)"); + } +} +... + </programlisting> </example> </section> </section> diff --git a/modules_k/sqlops/sql_api.c b/modules_k/sqlops/sql_api.c index 5bf6920..0dc97e0 100644 --- a/modules_k/sqlops/sql_api.c +++ b/modules_k/sqlops/sql_api.c @@ -32,6 +32,9 @@ #include "../../dprint.h" #include "../../lib/kcore/hash_func.h" #include "../../ut.h" +#ifdef WITH_XAVP +#include "../../xavp.h" +#endif #include "sql_api.h" @@ -341,6 +344,134 @@ error: return -1; } +#ifdef WITH_XAVP +int sql_do_xquery(struct sip_msg *msg, sql_con_t *con, pv_elem_t *query, + pv_elem_t *res) +{ + db1_res_t* db_res = NULL; + sr_xavp_t *row = NULL; + sr_xval_t val; + int i, j; + str sv, xavp; + + if(msg==NULL || query==NULL || res==NULL) + { + LM_ERR("bad parameters\n"); + return -1; + } + if(pv_printf_s(msg, query, &sv)!=0) + { + LM_ERR("cannot print the sql query\n"); + return -1; + } + + if(pv_printf_s(msg, res, &xavp)!=0) + { + LM_ERR("cannot print the result parameter\n"); + return -1; + } + + if(con->dbf.raw_query(con->dbh, &sv, &db_res)!=0) + { + LM_ERR("cannot do the query\n"); + return -1; + } + + if(db_res==NULL || RES_ROW_N(db_res)<=0 || RES_COL_N(db_res)<=0) + { + LM_DBG("no result after query\n"); + con->dbf.free_result(con->dbh, db_res); + return 2; + } + + for(i=RES_ROW_N(db_res)-1; i>=0; i--) + { + row = NULL; + for(j=RES_COL_N(db_res)-1; j>=0; j--) + { + if(RES_ROWS(db_res)[i].values[j].nul) + { + val.type = SR_XTYPE_NULL; + } else + { + switch(RES_ROWS(db_res)[i].values[j].type) + { + case DB1_STRING: + val.type = SR_XTYPE_STR; + sv.s= + (char*)RES_ROWS(db_res)[i].values[j].val.string_val; + sv.len=strlen(sv.s); + break; + case DB1_STR: + val.type = SR_XTYPE_STR; + sv.len= + RES_ROWS(db_res)[i].values[j].val.str_val.len; + sv.s= + (char*)RES_ROWS(db_res)[i].values[j].val.str_val.s; + break; + case DB1_BLOB: + val.type = SR_XTYPE_STR; + sv.len= + RES_ROWS(db_res)[i].values[j].val.blob_val.len; + sv.s= + (char*)RES_ROWS(db_res)[i].values[j].val.blob_val.s; + break; + case DB1_INT: + val.type = SR_XTYPE_INT; + val.v.i + = (int)RES_ROWS(db_res)[i].values[j].val.int_val; + break; + case DB1_DATETIME: + val.type = SR_XTYPE_INT; + val.v.i + = (int)RES_ROWS(db_res)[i].values[j].val.time_val; + break; + case DB1_BITMAP: + val.type = SR_XTYPE_INT; + val.v.i + = (int)RES_ROWS(db_res)[i].values[j].val.bitmap_val; + break; + default: + val.type = SR_XTYPE_NULL; + } + if(val.type == SR_XTYPE_STR) + { + if(sv.len==0) + { + val.v.s = _sql_empty_str; + } else { + val.v.s.s = (char*)pkg_malloc(sv.len*sizeof(char)); + if(val.v.s.s == NULL) + { + LM_ERR("no more memory\n"); + goto error; + } + memcpy(val.v.s.s, sv.s, sv.len); + val.v.s.len = sv.len; + } + } + } + /* Add column to current row, under the column's name */ + LM_DBG("Adding column: %.*s\n", RES_NAMES(db_res)[j]->len, RES_NAMES(db_res)[j]->s); + xavp_add_value(RES_NAMES(db_res)[j], &val, &row); + } + /* Add row to result xavp */ + val.type = SR_XTYPE_XAVP; + val.v.xavp = row; + LM_DBG("Adding row\n"); + xavp_add_value(&xavp, &val, NULL); + } + + con->dbf.free_result(con->dbh, db_res); + return 1; + +error: + con->dbf.free_result(con->dbh, db_res); + return -1; + +} +#endif + int sql_parse_param(char *val) { str name; diff --git a/modules_k/sqlops/sql_api.h b/modules_k/sqlops/sql_api.h index b5543d5..ce00672 100644 --- a/modules_k/sqlops/sql_api.h +++ b/modules_k/sqlops/sql_api.h @@ -73,6 +73,10 @@ void sql_destroy(void); int sql_connect(void); int sql_do_query(sql_con_t *con, str *query, sql_result_t *res); +#ifdef WITH_XAVP +int sql_do_xquery(struct sip_msg *msg, sql_con_t *con, pv_elem_t *query, + pv_elem_t *res); +#endif sql_con_t* sql_get_connection(str *name); sql_result_t* sql_get_result(str *name); diff --git a/modules_k/sqlops/sqlops.c b/modules_k/sqlops/sqlops.c index 5242f88..a03b7b5 100644 --- a/modules_k/sqlops/sqlops.c +++ b/modules_k/sqlops/sqlops.c @@ -60,11 +60,17 @@ static int bind_sqlops(sqlops_api_t* api); /** module functions */ static int sql_query(struct sip_msg*, char*, char*, char*); +#ifdef WITH_XAVP +static int sql_xquery(struct sip_msg *msg, char *dbl, char *query, char *res); +#endif static int sql_rfree(struct sip_msg*, char*, char*); static int child_init(int rank); static void destroy(void); static int fixup_sql_query(void** param, int param_no); +#ifdef WITH_XAVP +static int fixup_sql_xquery(void** param, int param_no); +#endif static int fixup_sql_rfree(void** param, int param_no); static int sql_con_param(modparam_t type, void* val); @@ -80,6 +86,11 @@ static cmd_export_t cmds[]={ {"sql_query", (cmd_function)sql_query, 3, fixup_sql_query, 0, REQUEST_ROUTE | FAILURE_ROUTE | ONREPLY_ROUTE | BRANCH_ROUTE | LOCAL_ROUTE}, +#ifdef WITH_XAVP + {"sql_xquery", (cmd_function)sql_xquery, 3, fixup_sql_xquery, 0, + REQUEST_ROUTE | FAILURE_ROUTE | + ONREPLY_ROUTE | BRANCH_ROUTE | LOCAL_ROUTE}, +#endif {"sql_result_free", (cmd_function)sql_rfree, 1, fixup_sql_rfree, 0, REQUEST_ROUTE | FAILURE_ROUTE | ONREPLY_ROUTE | BRANCH_ROUTE | LOCAL_ROUTE}, @@ -181,6 +192,16 @@ static int sql_query(struct sip_msg *msg, char *dbl, char *query, char *res) return sql_do_query((sql_con_t*)dbl, &sq, (sql_result_t*)res); } +#ifdef WITH_XAVP +/** + * + */ +static int sql_xquery(struct sip_msg *msg, char *dbl, char *query, char *res) +{ + return sql_do_xquery(msg, (sql_con_t*)dbl, query, (pv_elem_t*)res); +} +#endif + /** * */ @@ -230,6 +251,46 @@ static int fixup_sql_query(void** param, int param_no) return 0; } +#ifdef WITH_XAVP +/** + * + */ +static int fixup_sql_xquery(void** param, int param_no) +{ + sql_con_t *con = NULL; + pv_elem_t *pv = NULL; + str s; + + s.s = (char*)(*param); + s.len = strlen(s.s); + + if (param_no==1) { + con = sql_get_connection(&s); + if(con==NULL) + { + LM_ERR("invalid connection [%s]\n", s.s); + return E_UNSPEC; + } + *param = (void*)con; + } else if (param_no==2) { + if(pv_parse_format(&s, &pv)<0) + { + LM_ERR("invalid query string [%s]\n", s.s); + return E_UNSPEC; + } + *param = (void*)pv; + } else if (param_no==3) { + if(pv_parse_format(&s, &pv)<0) + { + LM_ERR("invalid result [%s]\n", s.s); + return E_UNSPEC; + } + *param = (void*)pv; + } + return 0; +} +#endif + /** * */