Hi, I wrote a new module mod_odbc_query the last few days and I'm wondering what other people think of it. It works here, tested it and valgrind sees no leaks.. You can check it out here: http://fisheye.freeswitch.org/browse/FreeSWITCH/contrib/ledr/c/mod_odbc_quer... It's pretty flexible and useful for me. regards, Leon --- mod_odbc_query readme This module can be used for doing odbc queries from the dialplan. Usage: <application name="odbc_query" data="SELECT some_column_name AS target_channel_variable_name FROM some_table_name WHERE 1;"/> or: <application name="odbc_query" data="my-query"/> The module simply checks whether the data attr contains a space. If it does, then that field will be seen as an SQL query, otherwise it will be seen as a query 'name' which then has to be defined in the modules configuration in the <queries> section like this: <queries> <query name="my-query" value="SELECT some_column_name AS target_channel_variable_name FROM some_table_name WHERE 1;"/> </queries> The module will do the query and store each returned column name as channel variable name together with its corresponding value. Another feature is, that if only two columns are returned, which have the column names "name" and "value", then the channel variables will be set according to them. This way you can have the query return multiple rows with different channel variables. If the query returns something else than column-names "name" and "value" and it returns multiple rows, then the channel variables will be overwritten with each iteration of the rows - which is probably useless. The query may contain ${blah} variables that will be expanded from channel variables before the query is performed. For example: query: "SELECT foo, bar FROM huk WHERE tilde='kek';" returns: foo bar ---------- a b c d then the channel variables that will be set are: foo=c bar=d --- query: "SELECT foo AS name, bar AS value FROM huk WHERE tilde='kek';" returns: name value ------------ a b c d then the channel variables that will be set are: a=b c=d --- So, the first example should only be used when you know that only zero or one row will be returned, and second one if you know zero or more rows will be returned. If zero rows are returned (in either foo/bar or name/value case) then no channel variables will be set, overwritten or deleted. --- <configuration name="odbc_query.conf" description="ODBC Query Module"> <settings> <param name="odbc-dsn" value="freeswitch:freeswitch:secret"/> </settings> <queries> <!-- the ${foo} variable will be expanded from channel variables at query time --> <query name="map-did-users" value=" SELECT dir_users.username AS dest_user, dir_domains.name AS dest_domain FROM map_did_users, dir_domains, dir_users WHERE map_did_users.did='${destination_number}' AND dir_users.id = map_did_users.user_id AND dir_domains.id = dir_users.dir_domain_id;"/> </queries> </configuration>
Leon, Two things: first, would you mind putting this info into the wiki? Just create this page: http://wiki.freeswitch.org/index.php?title=Mod_odbc&action=edit&redlink=1 Second, I was curious what kind of throughput you've seen with this? Just curious how much you think it might scale. I'm sure someone will find a way to mix this with SIPp in an effort to break it. :P -MC On Mon, Sep 14, 2009 at 7:23 PM, Leon de Rooij <leon@scarlet-internet.nl>wrote:
Hi,
I wrote a new module mod_odbc_query the last few days and I'm wondering what other people think of it. It works here, tested it and valgrind sees no leaks..
You can check it out here:
http://fisheye.freeswitch.org/browse/FreeSWITCH/contrib/ledr/c/mod_odbc_quer...
It's pretty flexible and useful for me.
regards,
Leon
---
mod_odbc_query readme
This module can be used for doing odbc queries from the dialplan.
Usage:
<application name="odbc_query" data="SELECT some_column_name AS target_channel_variable_name FROM some_table_name WHERE 1;"/>
or:
<application name="odbc_query" data="my-query"/>
The module simply checks whether the data attr contains a space. If it does, then that field will be seen as an SQL query, otherwise it will be seen as a query 'name' which then has to be defined in the modules configuration in the <queries> section like this:
<queries> <query name="my-query" value="SELECT some_column_name AS target_channel_variable_name FROM some_table_name WHERE 1;"/> </queries>
The module will do the query and store each returned column name as channel variable name together with its corresponding value.
Another feature is, that if only two columns are returned, which have the column names "name" and "value", then the channel variables will be set according to them. This way you can have the query return multiple rows with different channel variables. If the query returns something else than column-names "name" and "value" and it returns multiple rows, then the channel variables will be overwritten with each iteration of the rows - which is probably useless.
The query may contain ${blah} variables that will be expanded from channel variables before the query is performed.
For example:
query: "SELECT foo, bar FROM huk WHERE tilde='kek';"
returns:
foo bar ---------- a b c d
then the channel variables that will be set are:
foo=c bar=d
---
query: "SELECT foo AS name, bar AS value FROM huk WHERE tilde='kek';"
returns:
name value ------------ a b c d
then the channel variables that will be set are:
a=b c=d
---
So, the first example should only be used when you know that only zero or one row will be returned, and second one if you know zero or more rows will be returned.
If zero rows are returned (in either foo/bar or name/value case) then no channel variables will be set, overwritten or deleted.
---
<configuration name="odbc_query.conf" description="ODBC Query Module">
<settings> <param name="odbc-dsn" value="freeswitch:freeswitch:secret"/> </settings>
<queries> <!-- the ${foo} variable will be expanded from channel variables at query time --> <query name="map-did-users" value=" SELECT dir_users.username AS dest_user, dir_domains.name AS dest_domain FROM map_did_users, dir_domains, dir_users WHERE map_did_users.did='${destination_number}' AND dir_users.id = map_did_users.user_id AND dir_domains.id = dir_users.dir_domain_id;"/> </queries> </configuration>
_______________________________________________ FreeSWITCH-dev mailing list FreeSWITCH-dev@lists.freeswitch.org http://lists.freeswitch.org/mailman/listinfo/freeswitch-dev UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-dev http://www.freeswitch.org
Hi Michael, Thanks for your reply. As you can see, the actual APP call is very short and efficient, so I think it can handle a very high throughput - which was my goal. The way of doing the query and saving the variables is about the same as how I did it in mod_xml_odbc, except that module is more complicated. For numbers: I tested mod_xml_odbc with sipp - which does 6 queries in my configuration - and got 300 registrations per second, which means 600 user lookups per second, which is 3600 sql queries per second. Granted, that was on a fast workstation (core i7 920), local mysql with only one or two users provisioned, but still it shows some good numbers. Also in the case of mod_xml_odbc more things are done than just a query: traversing and copying a switch_xml_t, tag by tag, multiple times at query time, etc, which isn't done in this module. I'll put some documentation on the wiki this afternoon and see what I can do for load testing. If you (or anyone else) have any suggestions on how things could be done better, please let me know. I chose for example on reloadxml, not to check the odbc-dsn setting - whether it changed and reconnect to the db if it did. I tried adding the functionality, but it gives a lot of cruft in the code for a case that hardly ever happens - you can just un- and load the module in that case. regards, Leon On Mon, 2009-09-14 at 21:12 -0700, Michael Collins wrote:
Leon,
Two things: first, would you mind putting this info into the wiki? Just create this page: http://wiki.freeswitch.org/index.php?title=Mod_odbc&action=edit&redlink=1
Second, I was curious what kind of throughput you've seen with this? Just curious how much you think it might scale. I'm sure someone will find a way to mix this with SIPp in an effort to break it. :P
-MC
On Mon, Sep 14, 2009 at 7:23 PM, Leon de Rooij <leon@scarlet-internet.nl> wrote: Hi,
I wrote a new module mod_odbc_query the last few days and I'm wondering what other people think of it. It works here, tested it and valgrind sees no leaks..
You can check it out here: http://fisheye.freeswitch.org/browse/FreeSWITCH/contrib/ledr/c/mod_odbc_quer...
It's pretty flexible and useful for me.
regards,
Leon
---
mod_odbc_query readme
This module can be used for doing odbc queries from the dialplan.
Usage:
<application name="odbc_query" data="SELECT some_column_name AS target_channel_variable_name FROM some_table_name WHERE 1;"/>
or:
<application name="odbc_query" data="my-query"/>
The module simply checks whether the data attr contains a space. If it does, then that field will be seen as an SQL query, otherwise it will be seen as a query 'name' which then has to be defined in the modules configuration in the <queries> section like this:
<queries> <query name="my-query" value="SELECT some_column_name AS target_channel_variable_name FROM some_table_name WHERE 1;"/> </queries>
The module will do the query and store each returned column name as channel variable name together with its corresponding value.
Another feature is, that if only two columns are returned, which have the column names "name" and "value", then the channel variables will be set according to them. This way you can have the query return multiple rows with different channel variables. If the query returns something else than column-names "name" and "value" and it returns multiple rows, then the channel variables will be overwritten with each iteration of the rows - which is probably useless.
The query may contain ${blah} variables that will be expanded from channel variables before the query is performed.
For example:
query: "SELECT foo, bar FROM huk WHERE tilde='kek';"
returns:
foo bar ---------- a b c d
then the channel variables that will be set are:
foo=c bar=d
---
query: "SELECT foo AS name, bar AS value FROM huk WHERE tilde='kek';"
returns:
name value ------------ a b c d
then the channel variables that will be set are:
a=b c=d
---
So, the first example should only be used when you know that only zero or one row will be returned, and second one if you know zero or more rows will be returned.
If zero rows are returned (in either foo/bar or name/value case) then no channel variables will be set, overwritten or deleted.
---
<configuration name="odbc_query.conf" description="ODBC Query Module">
<settings> <param name="odbc-dsn" value="freeswitch:freeswitch:secret"/> </settings>
<queries> <!-- the ${foo} variable will be expanded from channel variables at query time --> <query name="map-did-users" value=" SELECT dir_users.username AS dest_user, dir_domains.name AS dest_domain FROM map_did_users, dir_domains, dir_users WHERE map_did_users.did='${destination_number}' AND dir_users.id = map_did_users.user_id AND dir_domains.id = dir_users.dir_domain_id;"/> </queries> </configuration>
_______________________________________________ FreeSWITCH-dev mailing list FreeSWITCH-dev@lists.freeswitch.org http://lists.freeswitch.org/mailman/listinfo/freeswitch-dev UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-dev http://www.freeswitch.org
_______________________________________________ FreeSWITCH-dev mailing list FreeSWITCH-dev@lists.freeswitch.org http://lists.freeswitch.org/mailman/listinfo/freeswitch-dev UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-dev http://www.freeswitch.org
participants (2)
-
Leon de Rooij
-
Michael Collins