[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