How to set multiple reply values via a single SQL query?
Hi Is there any way to set multiple reply items via a single SQL query? At the moment, I have a table called settings and within that I have a number of columns like Session-Timeout. I am having to do multiple SQL queries to pull these out, even though they are in the same table, which is both introducing time and load on the server. I am using: update reply { Session-Timeout = "%{sql:SELECT `Session-Timeout` FROM settings WHERE router_mac = \"%{Called-Station-Id}\"}" Idle-Timeout = "%{sql:SELECT `Idle-Timeout` FROM settings WHERE router_mac = \"%{Called-Station-Id}\"}" Filter-Id = "%{sql:SELECT `Filter-Id` FROM settings WHERE router_mac = \"%{Called-Station-Id}\"}" } Is there any way to do this in a single query? The SQL query above is simplified as we join some other tables in (which is why we are not just using entries in radreply) but that's a different topic. Thanks, James
On Mar 3, 2015, at 3:14 PM, James Wood <james.wood@purplewifi.net> wrote:
Is there any way to set multiple reply items via a single SQL query?
No. Arran and I have discussed that. We have some ideas, but nothing final.
At the moment, I have a table called settings and within that I have a number of columns like Session-Timeout. I am having to do multiple SQL queries to pull these out, even though they are in the same table, which is both introducing time and load on the server
The the short term you can select 3 fields, and use SQL to concatenate them as strings. Then, use FreeRADIUS to split the string. Ugly, but less load for your SQL server. Alan DeKok.
Thanks, that sounds like a reasonable plan until something is (hopefully) introduced. So if I had this simple query: SELECT CONCAT(`Session-Timeout`, '#', `Idle-Timeout`, '#', `Filter-Id`) FROM settings... That returns: 86400#3600#Test Where 86400 is the Session Timeout, 3600 is the Idle-Timeout and Test is the Filter-Id. How can I split this in the FreeRadius conf in to the three separate variables using # as the delimiter? Thanks James
On Mar 3, 2015, at 3:41 PM, James Wood <james.wood@purplewifi.com> wrote:
How can I split this in the FreeRadius conf in to the three separate variables using # as the delimiter?
Regular expressions. Put the data into a temporary attribute: update control { Tmp-String-0 := “”%{sql:SELECT CONCAT(`Session-Timeout`, '#', `Idle-Timeout`, '#', `Filter-Id`) FROM settings…}" } if (Tmp-String-0 =~ /^([^#]+)#([^#]+)([^#]+)/) { update reply { Session-Timeout := “%{1}” Idle-Timeout := “%{2}” Filter-Id := “%{3}" } } That should work… Alan DeKok.
On 3 Mar 2015, at 15:52, Alan DeKok <aland@deployingradius.com> wrote:
On Mar 3, 2015, at 3:41 PM, James Wood <james.wood@purplewifi.com> wrote:
How can I split this in the FreeRadius conf in to the three separate variables using # as the delimiter?
Regular expressions. Put the data into a temporary attribute:
update control { Tmp-String-0 := “”" }
if (Tmp-String-0 =~ /^([^#]+)#([^#]+)([^#]+)/) { update reply { Session-Timeout := “%{1}” Idle-Timeout := “%{2}” Filter-Id := “%{3}" } }
That should work…
or update control { Tmp-String-0 := "%{sql:SELECT CONCAT(`Session-Timeout`, '#', `Idle-Timeout`, '#', `Filter-Id`) FROM settings…}" } if ("%{explode:&control:Tmp-String-0 #}" > 0) { update reply { Session-Timeout := &control:Tmp-String-0[0] Idle-Timeout := &control:Tmp-String-0[1] Filter-Id := &control:Tmp-String-0[2] } } or update control { Tmp-String-0 := "%{sql:SELECT CONCAT('Session-Timeout=',`Session-Timeout`, '#Idle-Timeout=', `Idle-Timeout`, '#Filter-Id=', `Filter-Id`) FROM settings…}" } if ("%{explode:&control:Tmp-String-0 #}" > 0) { foreach &control:Tmp-String-0 { if ("%{Foreach-Variable-0}" =~ /^([^=]+)=(.*)$/) { update reply { "%{1}" := "%{2}" } } } } above examples are 3.0.7 only. We really need to sort those mapping sections out. Arran Cudbard-Bell <a.cudbardb@freeradius.org> FreeRADIUS development team FD31 3077 42EC 7FCD 32FE 5EE2 56CF 27F9 30A8 CAA2
Your suggestion worked well, thanks Alan. I did have to add control: to the if (Tmp-String-0... however, so if (control:Tmp-String-0... Thanks for your advice also Arran James
participants (4)
-
Alan DeKok -
Arran Cudbard-Bell -
James Wood -
James Wood