multiple columns in rlm_sql
Hi, I have question, is it possible in virtual server config do something like this? update control { &value1,&value2, ... ,&valueN="%{sql:SELECT value1,value2, ... ,valueN from some_table where ...}" } I know, that is no problem to do more sql queries like: update control { &value1="%{sql:SELECT value1,from some_table where ...}" &value2="%{sql:SELECT value2,from some_table where ...}" ... &valueN="%{sql:SELECT valueN,from some_table where ...}" } but it is too expensive for performance, much better is to do with one sql select. I looked into rlm_sql.c, and the line #462 - "for (i = 0; i < fields; i++) names[i] = field_info[i].name;" looks as multiple column separation. I tried find some info in the freerarius WiKi and in the documentation, but without success. Thank you for any idea, Petr Linke
On Apr 13, 2018, at 11:40 AM, petr.linke@seznam.cz wrote:
Hi, I have question, is it possible in virtual server config do something like this?
update control { &value1,&value2, ... ,&valueN="%{sql:SELECT value1,value2, ... ,valueN from some_table where ...}" }
No. We have a "map" keyword in v4 which does exactly that. Back-porting it do v3 is impossible.
I know, that is no problem to do more sql queries like: update control { &value1="%{sql:SELECT value1,from some_table where ...}" &value2="%{sql:SELECT value2,from some_table where ...}" ... &valueN="%{sql:SELECT valueN,from some_table where ...}" } but it is too expensive for performance, much better is to do with one sql select.
Yes. That's why we added "map".
I looked into rlm_sql.c, and the line #462 - "for (i = 0; i < fields; i++) names[i] = field_info[i].name;" looks as multiple column separation. I tried find some info in the freerarius WiKi and in the documentation, but without success.
The documentation describes what the server does. If it's not documented, it doesn't exist. Alan DeKok.
with later versions of 3.0.x you can do things like update control { Tmp-String-0 := "%{sql:SELECT CONCAT(attribute,'#',op,'#',value) from database_table where username = '%{User-Name}'}" } if ("%{explode:&control:Tmp-String-0 #}" > 0) { foreach &control:Tmp-String-0 { if ("%{Foreach-Variable-0}" =~ /^([^=]+)=(.*)$/) { update reply { "%{1}" "%{2}" "%{3}" } } } } edit as required....should do the job. alan
Hi Alan, thank you very much for idea, great. I set all in similar way, but it looks, that with xlat is no possible for define first column in update reply section. for example: update control { Tmp-String-0 := %{sql:SELECT CONCAT(attribute,'#',value) from database_ table where...} } if ( control:Tmp-String-0 =~ /^(\S+)#(.*)$/ ) { update reply { "%{1}" += "%{2}" test_attribute += "%{2}" } } if the sql SELECT CONCAT(attribute,'#',value) ... return in attribute "some_ attribute" and in value "some_value", the answer should be 2 lines: some_attribute += some_value --- this is from xlat "%{1}" += "%{2}" test_attribute += some_value --- this is from xlat test_ attribute += "%{2}" but in the answer is only one line "test_attribute += some_value" according xlat test_attribute += "%{2}" In debug I can see, that the expansion of %{1} is correct, and that in update reply section is the line "some_attribute += some_value" inserted, but in radius response is no line "some_attribute += some_value" Maybe I do something wrong. Thank you, Petr Linke ---------- Original-mail ---------- From: Alan Buxey <alan.buxey@gmail.com> To: FreeRadius users mailing list <freeradius-users@lists.freeradius.org> Date: 13. 4. 2018 19:19:29 Subject: Re: multiple columns in rlm_sql "with later versions of 3.0.x you can do things like update control { Tmp-String-0 := %{sql:SELECT CONCAT(attribute,'#',op,'#',value) from database_table where username = '%{User-Name}'}" } if ("%{explode:&control:Tmp-String-0 #}" > 0) { foreach &control:Tmp-String-0 { if ("%{Foreach-Variable-0}" =~ /^([^=]+)=(.*)$/) { update reply { "%{1}" "%{2}" "%{3}" } } } } edit as required....should do the job. alan - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users. html"
On Apr 16, 2018, at 7:45 AM, <petr.linke@seznam.cz> <petr.linke@seznam.cz> wrote: update control { Tmp-String-0 := %{sql:SELECT CONCAT(attribute,'#',value) from database_ table where...} } if ( control:Tmp-String-0 =~ /^(\S+)#(.*)$/ ) { update reply { "%{1}" += "%{2}"
You can't dynamically assign the left side in v3. i.e. the left side of an attribute assignment *must* be a fixed string. All of the examples and documentation show this.
test_attribute += "%{2}"
What's "test_attribute" ? The left side of assignments MUST be a known attribute name, taken from the dictionaries. Again, all of the examples and documentation show this. Nothing suggests that you can just invent things here. Alan DeKok.
---------- Original e-mail ---------- From: Alan DeKok <aland@deployingradius.com> To: FreeRadius users mailing list <freeradius-users@lists.freeradius.org> Date: 16. 4. 2018 14:02:24 Subject: Re: multiple columns in rlm_sql Hi Alan, the answer "You can't dynamically assign the left side in v3." is enough for me. I start testing according Alan Buxey response, but now I know, that the way of dynamic assignment left side is wrong. Thank you. And the test_ attribute is defined in my dictionary only for tests. Many thanks you for your excellent work and really fast response, nice day, Petr Linke "
On Apr 16, 2018, at 7:45 AM, <petr.linke@seznam.cz> <petr.linke@seznam.cz> wrote: update control { Tmp-String-0 := %{sql:SELECT CONCAT(attribute,'#',value) from database_ table where...} } if ( control:Tmp-String-0 =~ /^(\S+)#(.*)$/ ) { update reply { "%{1}" += "%{2}"
You can't dynamically assign the left side in v3. i.e. the left side of an attribute assignment *must* be a fixed string. All of the examples and documentation show this.
test_attribute += "%{2}"
What's "test_attribute" ? The left side of assignments MUST be a known attribute name, taken from the dictionaries. Again, all of the examples and documentation show this. Nothing suggests that you can just invent things here. Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users. html"
participants (4)
-
Alan Buxey -
Alan DeKok -
Arran Cudbard-Bell -
petr.linke＠seznam.cz