[SR-Users] SQL for ordered LCR rules

[Alex Balashov]

Indeed. 

On September 5, 2017 10:24:07 PM EDT, Patrick Wakano <pwakano at gmail.com> wrote:
>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
>>
>>


-- Alex

--
Principal, Evariste Systems LLC (www.evaristesys.com)

Sent from my Google Nexus.