[OpenSIPS-Users] variable/avp value check

[Ben Newlin]

Alexey,

Apologies, you are correct about the columns setting.

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.

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.

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.

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.

Ben Newlin

From: Users <users-bounces at lists.opensips.org> on behalf of Alexey <slackway2me at gmail.com>
Date: Tuesday, February 13, 2024 at 1:55 AM
To: OpenSIPS users mailling list <users at lists.opensips.org>
Subject: Re: [OpenSIPS-Users] variable/avp value check
 EXTERNAL EMAIL - Please use caution with links and attachments

Hi Ben,
thank you for your response but this is not the case.

Documentation says: 'columns : If not present, all the columns from
the table will be cached'.
So in our case all columns from the DB are cached. Our main question -
How does '$sql_cached_value(id{sep}col{sep}key)' work ?

We want that this PV exports the full column 'base_id" from the cached DB.
But now it seems that the PV can export only one row which is limited
by the 'key' value.

Could you kindly provide us an example of the string
'$sql_cached_value(id{sep}col{sep}key)'
which lets us extract the full column (array) from the cached DB ?


--
best regards, Alexey
https://alexeyka.zantsev.com

_______________________________________________
Users mailing list
Users at lists.opensips.org
http://lists.opensips.org/cgi-bin/mailman/listinfo/users
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.opensips.org/pipermail/users/attachments/20240214/eb21e6a3/attachment.html>