[SR-Users] SQL for ordered LCR rules

[Patrick Wakano]

Nice! but I think I can invert the test and do it like this: 'FULL_NUMBER'
LIKE CONCAT(lr.prefix,'%')
Probably not as fast but should do the job!


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

> 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.
>
> _______________________________________________
> 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/3fdcecbd/attachment.html>