[SR-Users] Storing multiple SQL results in htable/mtree

[Daniel-Constantin Mierla]

Hello,

On 11/18/10 3:44 PM, Robert McGilvray wrote:
>
> Hello,
>
> I’m interested in using the htable and/or mtree modules to keep my 
> aliases and did lookups in memory to minimize the amount of SQL 
> lookups that are being performed. It’s easy enough to use htable to 
> cache a single value after it is retrieved but what I’m not clear on 
> is how I can do it when SQL returns multiple destinations. Consider 
> the following query and returned results:
>
> SELECT username,domain,CAST(q_value AS CHAR) FROM aliases where 
> alias_username = '10001' AND alias_domain = 'us.sip.globeop.com' AND 
> active = '1' ORDER by q_value;
>
> +----------+--------------------+-----------------------+
>
> | username | domain             | CAST(q_value AS CHAR) |
>
> +----------+--------------------+-----------------------+
>
> | 8500     | us.sip.globeop.com | 1.000                 |
>
> | 3874     | us.sip.globeop.com | 1.000                 |
>
> | 7050     | us.sip.globeop.com | 1.000                 |
>
> | 2100     | us.sip.globeop.com | 1.000                 |
>
> 	
>
> 	
>
> In my existing script I just append the results as branches and all 
> works well but I end up with a lot of unnecessary SQL traffic. I see 
> the htable supports array but I’m not sure how to go about assigning 
> multiple values to a single key to use later. How can I take the above 
> results and store them in memory for use by other transactions?
>
htable gives you a generic container where you can store items (key, 
value). The key can be any composite, including variables.

Upon a sql query you get the size of result (number of rows) in 
$dbt(r=>nrows), and values in $dbr(r=>[i,j])

You can compose your key from the index in result and alias uri: 
$sht(x=>$var(index)::$var(alias))

The value can be formed from the columns of each row, building a sip uri 
for example. Of course you can store each column as well, making a 
proper key, e.g., using column name together with row index and alias

So you can practically store all the rows of the sql result in share 
table. You can store the number of rows as well under a specific key per 
alias.

Cheers,
Daniel

-- 
Daniel-Constantin Mierla
Kamailio (OpenSER) Advanced Trainings
Nov 22-25, 2010, Berlin, Germany
Jan 24-26, 2011, Irvine, CA, USA
http://www.asipto.com

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.sip-router.org/pipermail/sr-users/attachments/20101119/c26da5c6/attachment.htm>