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

Alex Hermann alex at speakup.nl
Mon Mar 7 15:19:03 CET 2011


Module: sip-router
Branch: alexh/master
Commit: a1d2cedabf37e17e1d71839225c9652eef61b6eb
URL:    http://git.sip-router.org/cgi-bin/gitweb.cgi/sip-router/?a=commit;h=a1d2cedabf37e17e1d71839225c9652eef61b6eb

Author: Alex Hermann <alex at speakup.nl>
Committer: Alex Hermann <alex at speakup.nl>
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
+
 /**
  *
  */




More information about the sr-dev mailing list