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 > <[email protected]> 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 > [email protected] > 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 > [email protected] > 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 [email protected] http://lists.freeswitch.org/mailman/listinfo/freeswitch-dev UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-dev http://www.freeswitch.org
