<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=Windows-1252">
<meta name="Generator" content="Microsoft Word 15 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:Aptos;
panose-1:2 11 0 4 2 2 2 2 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
font-size:10.0pt;
font-family:"Aptos",sans-serif;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
span.EmailStyle19
{mso-style-type:personal-reply;
font-family:"Aptos",sans-serif;
color:windowtext;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;
mso-ligatures:none;}
@page WordSection1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
{page:WordSection1;}
--></style>
</head>
<body lang="EN-US" link="blue" vlink="purple" style="word-wrap:break-word">
<div class="WordSection1">
<p class="MsoNormal"><span style="font-size:11.0pt">Alexey,<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt">Apologies, you are correct about the columns setting.<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt">I also did not understand your use case. Unfortunately the answer is that as far as I know this cannot be done, at least not directly using the DB model you have. The OpenSIPS cache interface is fundamentally
designed to be a key/value store. You can query any data for a specific key, but you cannot query data for all keys. This is a limitation of converting the data from SQL to a key/value store. There are cachedb implementations that support wildcards in the
key – e.g. redis – but sql_cacher only works with OpenSIPS local cache, which makes sense as it is intended to cache the data locally. If you were caching the data between 2 external systems you would not want/need OpenSIPS to do that for you. And afaik OpenSIPS
local cache implementation does not support wildcards, so you can only look up a specific key.<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt">A workaround for this could be to create an SQL view in your database, and direct OpenSIPS sql_cacher module to query the view, rather than the table directly. You can use the view to reformat the data in
any way you would like, so that the cache query can return the data you want given whatever key makes sense. We use views in this way in our system, though not to achieve this specific use case of getting all values.<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt">For example, you could create a view that formats the data into two columns: “original column name” and “all values” (or something like that). The original column name would be the key, and would be “base_id”.
The second column would be a list or set of all the values of “base_id”. The sql_cacher query would use “base_id” as the key and would return all the base_id values. You could do this with any or all columns of the original table. The view would return only
a single row for each column of the original table included.<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt">There are probably other ways to solve this as well, though I must admit it’s not clear to me the use case for having the list of values without knowing which key they were associated with in the original
data.<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:black">Ben Newlin</span><span style="font-size:11.0pt"><o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt"><o:p> </o:p></span></p>
<div id="mail-editor-reference-message-container">
<div>
<div style="border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0in 0in 0in">
<p class="MsoNormal" style="margin-bottom:12.0pt"><b><span style="font-size:12.0pt;color:black">From:
</span></b><span style="font-size:12.0pt;color:black">Users <users-bounces@lists.opensips.org> on behalf of Alexey <slackway2me@gmail.com><br>
<b>Date: </b>Tuesday, February 13, 2024 at 1:55 AM<br>
<b>To: </b>OpenSIPS users mailling list <users@lists.opensips.org><br>
<b>Subject: </b>Re: [OpenSIPS-Users] variable/avp value check<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal" style="margin-bottom:12.0pt"><span style="font-size:11.0pt"> EXTERNAL EMAIL - Please use caution with links and attachments <br>
<br>
Hi Ben,<br>
thank you for your response but this is not the case.<br>
<br>
Documentation says: 'columns : If not present, all the columns from<br>
the table will be cached'.<br>
So in our case all columns from the DB are cached. Our main question -<br>
How does '$sql_cached_value(id{sep}col{sep}key)' work ?<br>
<br>
We want that this PV exports the full column 'base_id" from the cached DB.<br>
But now it seems that the PV can export only one row which is limited<br>
by the 'key' value.<br>
<br>
Could you kindly provide us an example of the string<br>
'$sql_cached_value(id{sep}col{sep}key)'<br>
which lets us extract the full column (array) from the cached DB ?<br>
<br>
<br>
-- <br>
best regards, Alexey<br>
<a href="https://alexeyka.zantsev.com">https://alexeyka.zantsev.com</a><br>
<br>
_______________________________________________<br>
Users mailing list<br>
Users@lists.opensips.org<br>
<a href="http://lists.opensips.org/cgi-bin/mailman/listinfo/users">http://lists.opensips.org/cgi-bin/mailman/listinfo/users</a><o:p></o:p></span></p>
</div>
</div>
</div>
</div>
</body>
</html>