<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html; charset=UTF-8" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
Hello,<br>
<br>
On 11/18/10 3:44 PM, Robert McGilvray wrote:
<blockquote
cite="mid:888FFEF718B66147A7F4EEAED6E3B385060F4B88@wpf1malprd4.globeop.com"
type="cite">
<meta content="text/html; charset=UTF-8" http-equiv="Content-Type">
<meta content="text/html; charset=UTF-8" http-equiv="Content-Type">
<meta content="Microsoft Word 12 (filtered medium)"
name="Generator">
<style><!--
/* Font Definitions */
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri","sans-serif";}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
span.EmailStyle17
{mso-style-type:personal-compose;
font-family:"Calibri","sans-serif";
color:windowtext;}
.MsoChpDefault
{mso-style-type:export-only;}
@page WordSection1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
<div>
<div class="WordSection1">
<p class="MsoNormal">Hello, <o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">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:<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">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;<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">+----------+--------------------+-----------------------+<o:p></o:p></p>
<p class="MsoNormal">| username | domain |
CAST(q_value AS CHAR) |<o:p></o:p></p>
<p class="MsoNormal">+----------+--------------------+-----------------------+<o:p></o:p></p>
<p class="MsoNormal">| 8500 | us.sip.globeop.com |
1.000 |<o:p></o:p></p>
<p class="MsoNormal">| 3874 | us.sip.globeop.com |
1.000 |<o:p></o:p></p>
<p class="MsoNormal">| 7050 | us.sip.globeop.com |
1.000 |<o:p></o:p></p>
<p class="MsoNormal">| 2100 | us.sip.globeop.com |
1.000 |<o:p></o:p></p>
<table class="MsoNormalTable" style="margin-left: 8.4pt;
border-collapse: collapse; border: medium none;" border="1"
cellpadding="0" cellspacing="0">
<tbody>
<tr>
<td style="width: 43.5pt; border: 1pt solid windowtext;
padding: 0in 5.4pt;" valign="top" width="58">
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
</td>
<td style="width: 81pt; border-width: 1pt 1pt 1pt
medium; border-style: solid solid solid none;
border-color: windowtext windowtext windowtext
-moz-use-text-color; padding: 0in 5.4pt;" valign="top"
width="108">
<p class="MsoNormal"><o:p> </o:p></p>
</td>
<td style="width: 92.25pt; border-width: 1pt 1pt 1pt
medium; border-style: solid solid solid none;
border-color: windowtext windowtext windowtext
-moz-use-text-color; padding: 0in 5.4pt;" valign="top"
width="123">
<p class="MsoNormal"><o:p> </o:p></p>
</td>
</tr>
</tbody>
</table>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">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? </p>
</div>
</div>
</blockquote>
htable gives you a generic container where you can store items (key,
value). The key can be any composite, including variables.<br>
<br>
Upon a sql query you get the size of result (number of rows) in
$dbt(r=>nrows), and values in $dbr(r=>[i,j])<br>
<br>
You can compose your key from the index in result and alias uri:
$sht(x=>$var(index)::$var(alias))<br>
<br>
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<br>
<br>
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.<br>
<br>
Cheers,<br>
Daniel<br>
<br>
<pre class="moz-signature" cols="72">--
Daniel-Constantin Mierla
Kamailio (OpenSER) Advanced Trainings
Nov 22-25, 2010, Berlin, Germany
Jan 24-26, 2011, Irvine, CA, USA
<a class="moz-txt-link-freetext" href="http://www.asipto.com">http://www.asipto.com</a></pre>
</body>
</html>