Re: [SR-Users] SQL for ordered LCR rules

List overview All Threads
Download

newer

older

SQL for ordered LCR rules

real-time update question

Patrick Wakano

6 Sep 2017 6 Sep '17

2:54 a.m.

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(a)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(a)lists.kamailio.org https://lists.kamailio.org/cgi-bin/mailman/listinfo/sr-users

Attachments:

attachment.html (text/html — 3.8 KB)

Show replies by date

Alex Balashov

6 Sep 6 Sep

2:57 a.m.

New subject: SQL for ordered LCR rules

...

https://github.com/dimitri/prefix

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(a)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(a)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(a)lists.kamailio.org https://lists.kamailio.org/cgi-bin/mailman/listinfo/sr-users

_______________________________________________ Kamailio (SER) - Users Mailing List sr-users(a)lists.kamailio.org https://lists.kamailio.org/cgi-bin/mailman/listinfo/sr-users

Patrick Wakano

5:24 a.m.

New subject: SQL for ordered LCR rules

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(a)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(a)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(a)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:

_______________________________________________ Kamailio (SER) - Users Mailing List sr-users(a)lists.kamailio.org https://lists.kamailio.org/cgi-bin/mailman/listinfo/sr-users _______________________________________________ Kamailio (SER) - Users Mailing List sr-users(a)lists.kamailio.org https://lists.kamailio.org/cgi-bin/mailman/listinfo/sr-users

Alex Balashov

5:25 a.m.

New subject: SQL for ordered LCR rules

Indeed. On September 5, 2017 10:24:07 PM EDT, Patrick Wakano <pwakano(a)gmail.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(a)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

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(a)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(a)lists.kamailio.org >>>> https://lists.kamailio.org/cgi-bin/mailman/listinfo/sr-users >>>> >>>> >>> >> > _______________________________________________ > Kamailio (SER) - Users Mailing List > sr-users(a)lists.kamailio.org > https://lists.kamailio.org/cgi-bin/mailman/listinfo/sr-users > > > _______________________________________________ > Kamailio (SER) - Users Mailing List > sr-users(a)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.

Patrick Wakano

5:52 a.m.

New subject: SQL for ordered LCR rules

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(a)evaristesys.com> wrote:

...

Indeed. On September 5, 2017 10:24:07 PM EDT, Patrick Wakano <pwakano(a)gmail.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

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

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(a)onat.edu.ua>

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

and I

>>> am not using it anyway.... Other than that does anyone did

-- Alex -- Principal, Evariste Systems LLC (www.evaristesys.com) Sent from my Google Nexus. _______________________________________________ Kamailio (SER) - Users Mailing List sr-users(a)lists.kamailio.org https://lists.kamailio.org/cgi-bin/mailman/listinfo/sr-users

2634

days inactive

2635

days old

sr-users@lists.kamailio.org

Manage subscription

4 comments

2 participants

tags (0)

participants (2)

Alex Balashov
Patrick Wakano