[Freeswitch-dev] mod_odbc_query
Leon de Rooij
leon at scarlet-internet.nl
Tue Sep 15 03:10:10 PDT 2009
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 at 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_query
>
> 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 at 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 at lists.freeswitch.org
> http://lists.freeswitch.org/mailman/listinfo/freeswitch-dev
> UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-dev
> http://www.freeswitch.org
More information about the FreeSWITCH-dev
mailing list