[SR-Users] SQL for ordered LCR rules

[Patrick Wakano]

Thanks for the response guys!
The link https://github.com/dimitri/prefixIt is returning 404....
Regarding the performance itself I am not worried since this select it is
just for management and I don't expect having millions of rules.
The idea is just to have an easy way to have a picture of how the LCR will
order and select the gateways based on a given prefix. The three LCR tables
are not so easy to handle and manage from command line so my idea was to
have a single SELECT or VIEW to return me all I need at once!
>From what I could check, I think the select I sent pretty much translates
what LCR module does internally, I am just trying to verify if it has some
flaw, which could mislead me in the rules management.

Cheers,
Patrick Wakano


On 6 September 2017 at 00:32, Dmitry Sinina <dmitry.sinina at onat.edu.ua>
wrote:

> https://yeti-switch.org/demo.html
>
>
> On 9/5/17 5:29 PM, Dmitry Sinina wrote:
>
>> And you can try our opensource LCR engine. We use kamailio as load
>> balancer and SEMS as SBC.
>>
>> On 9/5/17 3:02 AM, Patrick Wakano wrote:
>>
>>> Hello list,
>>>
>>> Hope you all doing well!
>>> I am trying to ease the management of LCR routing rules, since once we
>>> begin to have multiple prefixes, multiple GWs and so on, the visualization
>>> and management of the rules priorities becomes exponentially hard to do.
>>> So first thing I am trying to achieve is an easy way of retrieving the
>>> rules in an ordered manner. I couldn't find any tool to do such thing and
>>> source code was not very friendly.... so I've come up with this Postgresql
>>> query that I think retrieves all rules in the same order I expect LCR to
>>> select the GWs.
>>>
>>> SELECT lr.lcr_id, lr.prefix, lrt.priority, lg.gw_name, lg.ip_addr
>>> FROM lcr_rule lr
>>> JOIN lcr_rule_target lrt ON lrt.lcr_id = lr.lcr_id AND lrt.rule_id =
>>> lr.id <http://lr.id>
>>> JOIN lcr_gw lg ON lg.lcr_id = lr.lcr_id AND lg.id <http://lg.id> =
>>> lrt.gw_id
>>> WHERE lr.enabled = 1 AND lg.defunct = 0 AND lr.lcr_id = ID AND lr.prefix
>>> SIMILAR TO '(|PREFIX%)'
>>> ORDER BY lr.lcr_id, LENGTH(lr.prefix) DESC, lrt.priority;
>>>
>>> It is missing the weights calculation, but it is rather complex and I am
>>> not using it anyway.... Other than that does anyone did something similar
>>> to check if my query really matches what LCR engine does?
>>>
>>> Thanks,
>>> Patrick Wakano
>>>
>>> _______________________________________________
>>> Kamailio (SER) - Users Mailing List
>>> sr-users at lists.kamailio.org
>>> https://lists.kamailio.org/cgi-bin/mailman/listinfo/sr-users
>>>
>>>
>>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.kamailio.org/pipermail/sr-users/attachments/20170906/7702ef87/attachment.html>