Persistent MySQL connections to arbitrary databases

[Jonathan Gazeley]

Well, I solved my own problem by creating another instance of the sql 
module...


sql myVlanDB {
     database = "mysql"
     driver = "rlm_sql_${database}"
     server = "db.resnet.bris.ac.uk"
     port = 3306
     login = "radiusd"
     password = "********"
     radius_db = "VLANS"
}


...and then querying it directly from unlang in my virtual server, 
skipping perl, like so...


if (User-Name) {
     update reply {
         Tunnel-Private-Group-Id := "%{%{myVlanDB:select vlanNumber from 
VLANS where common_username = '%{User-Name}' limit 1}:-448}"
     }
}
else {
     reject
}


This causes radiusd to open N database handles to its usual radius 
database, and N database handles to the vlan database. Querying is much 
faster than calling a perl script each time that opens the handle and 
does the query.

I hope this is useful to someone else :)

Cheers,
Jonathan

----------------------------
Jonathan Gazeley
Systems Support Specialist
ResNet | Wireless & VPN Team
IT Services
University of Bristol
----------------------------



On 09/02/11 13:17, Alexander Clouter wrote:
> Jonathan Gazeley<jonathan.gazeley at bristol.ac.uk>  wrote:
>>
>>> However, why do you need to close the handle?  Just when you open it, do
>>> your work, store it to the side in a hash and mark it available for use
>>> (remember to add locking as it sounds like your script is threaded).
>>
>> Please excuse the ignorance - can you elaborate a bit on the process of
>> storing it in a hash, and marking it as available?
>>
> Meant to say an array of hashes:
> ----
> my @foobar;
> [snipped]
> push @foobar, {
> 	dbh	=>  $dbh,
> 	busy	=>  0,
> };
> ----
>
> An example of dumping objects into an array of hashes can be seen with:
> ----
> alex at berk:~$ perl -e 'use Digest::MD5; use Data::Dumper; my @foobar; my $md5 = Digest::MD5->new; push @foobar, { md5 =>  $md5, busy =>  0 }; print Dumper \@foobar'
> $VAR1 = [
>            {
>              'busy' =>  0,
>              'md5' =>  bless( do{\(my $o = 23868256)}, 'Digest::MD5' )
>            }
>          ];
> ----
>
> Without knowing what queries and logic you are throwing at your database
> and passing back to FreeRADIUS, I have a hard time understanding why you
> could not just use unlang with sql-xlat?
>
> Cheers
>