[SR-Users] SQL for ordered LCR rules

[Patrick Wakano]

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
JOIN lcr_gw lg ON lg.lcr_id = lr.lcr_id AND 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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.kamailio.org/pipermail/sr-users/attachments/20170905/8b23c888/attachment.html>