3 # FreeRADIUS rlm_sqlippool SQL Queries for the MySQL Dialect
5 # ## This series of queries allocates an IP address
6 # allocate-clear = "UPDATE ${ippool_table} \
7 # SET nasipaddress = '', pool_key = 0, \
8 # callingstationid = '', username = '', \
10 # WHERE pool_key = '${pool-key}'"
12 ## This series of queries allocates an IP address
13 ## (Note: If your pool-key is set to Calling-Station-Id and not NAS-Port
14 ## then you may wish to delete the "AND nasipaddress = '%{Nas-IP-Address}'
15 ## from the WHERE clause)
17 allocate-clear = "UPDATE ${ippool_table} \
18 SET nasipaddress = '', pool_key = 0, \
19 callingstationid = '', username = '', \
21 WHERE expiry_time <= NOW() - INTERVAL 1 SECOND
22 AND nasipaddress = '%{Nas-IP-Address}'"
26 ## The ORDER BY clause of this query tries to allocate the same IP-address
27 ## which user had last session...
28 allocate-find = "SELECT framedipaddress FROM ${ippool_table} \
29 WHERE pool_name = '%{control:Pool-Name}' AND expiry_time < NOW() \
30 ORDER BY (username <> '%{User-Name}'), \
31 (callingstationid <> '%{Calling-Station-Id}'), \
36 # ## If you prefer to allocate a random IP address every time, i
37 # ## use this query instead
38 # allocate-find = "SELECT framedipaddress FROM ${ippool_table} \
39 # WHERE pool_name = '%{control:Pool-Name}' \
40 # AND expiry_time = NULL \
47 ## If an IP could not be allocated, check to see if the pool exists or not
48 ## This allows the module to differentiate between a full pool and no pool
49 ## Note: If you are not running redundant pool modules this query may be
50 ## commented out to save running this query every time an ip is not allocated.
51 pool-check = "SELECT id FROM ${ippool_table} \
52 WHERE pool_name='%{control:Pool-Name}' LIMIT 1"
55 ## This is the final IP Allocation query, which saves the allocated ip details
56 allocate-update = "UPDATE ${ippool_table} \
57 SET nasipaddress = '%{NAS-IP-Address}', pool_key = '${pool-key}', \
58 callingstationid = '%{Calling-Station-Id}', username = '%{User-Name}', \
59 expiry_time = NOW() + INTERVAL ${lease-duration} SECOND \
60 WHERE framedipaddress = '%I'"
64 ## This series of queries frees an IP number when an accounting
65 ## START record arrives
66 start-update = "UPDATE ${ippool_table} \
67 SET expiry_time = NOW() + INTERVAL ${lease-duration} SECOND \
68 WHERE nasipaddress = '%{NAS-IP-Address}' AND pool_key = '${pool-key}'"
70 ## This series of queries frees an IP number when an accounting
71 ## STOP record arrives
72 stop-clear = "UPDATE ${ippool_table} \
73 SET nasipaddress = '', pool_key = 0, callingstationid = '', username = '', \
75 WHERE nasipaddress = '%{Nas-IP-Address}' AND pool_key = '${pool-key}' \
76 AND username = '%{User-Name}' \
77 AND callingstationid = '%{Calling-Station-Id}' \
78 AND framedipaddress = '%{Framed-IP-Address}'"
82 ## This series of queries frees an IP number when an accounting
83 ## ALIVE record arrives
84 alive-update = "UPDATE ${ippool_table} \
85 SET expiry_time = NOW() + INTERVAL ${lease-duration} SECOND \
86 WHERE nasipaddress = '%{Nas-IP-Address}' AND pool_key = '${pool-key}' \
87 AND username = '%{User-Name}' \
88 AND callingstationid = '%{Calling-Station-Id}' \
89 AND framedipaddress = '%{Framed-IP-Address}'"
93 ## This series of queries frees the IP numbers allocate to a
94 ## NAS when an accounting ON record arrives
95 on-clear = "UPDATE ${ippool_table} \
96 SET nasipaddress = '', pool_key = 0, callingstationid = '', username = '', \
98 WHERE nasipaddress = '%{Nas-IP-Address}'"
100 ## This series of queries frees the IP numbers allocate to a
101 ## NAS when an accounting OFF record arrives
102 off-clear = "UPDATE ${ippool_table} \
103 SET nasipaddress = '', pool_key = 0, callingstationid = '', username = '', \
105 WHERE nasipaddress = '%{Nas-IP-Address}'"