[SR-Users] SQL for ordered LCR rules

[Patrick Wakano]

Thanks for the attention Alex, so this "prefix" add on for Postgresql is
supposed to replace my lr.prefix SIMILAR TO '(|PREFIX%)' ? So then I could
actually use a complete number against the LCR prefixes, instead of having
to use a prefix in the test?

Cheers,
Patrick Wakano



On 6 September 2017 at 09:57, Alex Balashov <abalashov at evaristesys.com>
wrote:

> https://github.com/dimitri/prefix <https://github.com/dimitri/prefixIt>
>
>
> Regardless of how many routes you have, you don't want to do it the way
> you're doing it. Trust me.
>
> -- Alex
>
> On Sep 5, 2017, at 7:54 PM, Patrick Wakano <pwakano at gmail.com> wrote:
>
> 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
>>>>
>>>>
>>>
>>
> _______________________________________________
> Kamailio (SER) - Users Mailing List
> sr-users at lists.kamailio.org
> https://lists.kamailio.org/cgi-bin/mailman/listinfo/sr-users
>
>
> _______________________________________________
> 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/dc51fb15/attachment.html>