# -*- text -*-
##
## ippool.conf -- MySQL queries for rlm_sqlippool
##
## $Id$
# ## This series of queries allocates an IP address
# allocate-clear = "UPDATE ${ippool_table} \
# SET nasipaddress = '', pool_key = 0, \
# callingstationid = '', username = '', \
# expiry_time IS NULL \
# WHERE pool_key = '${pool-key}'"
## This series of queries allocates an IP address
## (Note: If your pool-key is set to Calling-Station-Id and not NAS-Port
## then you may wish to delete the "AND nasipaddress = '%{Nas-IP-Address}'
## from the WHERE clause)
allocate-clear = "UPDATE ${ippool_table} \
SET nasipaddress = '', pool_key = 0, \
callingstationid = '', username = '', \
expiry_time IS NULL \
WHERE expiry_time <= NOW() - INTERVAL 1 SECOND
AND nasipaddress = '%{Nas-IP-Address}'"
## The ORDER BY clause of this query tries to allocate the same IP-address
## which user had last session...
allocate-find = "SELECT framedipaddress FROM ${ippool_table} \
WHERE pool_name = '%{control:Pool-Name}' AND expiry_time < NOW() \
ORDER BY (username <> '%{User-Name}'), \
(callingstationid <> '%{Calling-Station-Id}'), \
expiry_time \
LIMIT 1 \
FOR UPDATE"
# ## If you prefer to allocate a random IP address every time, i
# ## use this query instead
# allocate-find = "SELECT framedipaddress FROM ${ippool_table} \
# WHERE pool_name = '%{control:Pool-Name}' \
# AND expiry_time IS NULL \
# ORDER BY RAND() \
# LIMIT 1 \
# FOR UPDATE"
## If an IP could not be allocated, check to see if the pool exists or not
## This allows the module to differentiate between a full pool and no pool
## Note: If you are not running redundant pool modules this query may be
## commented out to save running this query every time an ip is not allocated.
pool-check = "SELECT id FROM ${ippool_table} \
WHERE pool_name='%{control:Pool-Name}' LIMIT 1"
## This is the final IP Allocation query, which saves the allocated ip details
allocate-update = "UPDATE ${ippool_table} \
SET nasipaddress = '%{NAS-IP-Address}', pool_key = '${pool-key}', \
callingstationid = '%{Calling-Station-Id}', username = '%{User-Name}', \
expiry_time = NOW() + INTERVAL ${lease-duration} SECOND \
WHERE framedipaddress = '%I'"
## This series of queries frees an IP number when an accounting
## START record arrives
start-update = "UPDATE ${ippool_table} \
SET expiry_time = NOW() + INTERVAL ${lease-duration} SECOND \
WHERE nasipaddress = '%{NAS-IP-Address}' AND pool_key = '${pool-key}'"
## This series of queries frees an IP number when an accounting
## STOP record arrives
stop-clear = "UPDATE ${ippool_table} \
SET nasipaddress = '', pool_key = 0, callingstationid = '', username = '', \
expiry_time IS NULL \
WHERE nasipaddress = '%{Nas-IP-Address}' AND pool_key = '${pool-key}' \
AND username = '%{User-Name}' \
AND callingstationid = '%{Calling-Station-Id}' \
AND framedipaddress = '%{Framed-IP-Address}'"
## This series of queries frees an IP number when an accounting
## ALIVE record arrives
alive-update = "UPDATE ${ippool_table} \
SET expiry_time = NOW() + INTERVAL ${lease-duration} SECOND \
WHERE nasipaddress = '%{Nas-IP-Address}' AND pool_key = '${pool-key}' \
AND username = '%{User-Name}' \
AND callingstationid = '%{Calling-Station-Id}' \
AND framedipaddress = '%{Framed-IP-Address}'"
## This series of queries frees the IP numbers allocate to a
## NAS when an accounting ON record arrives
on-clear = "UPDATE ${ippool_table} \
SET nasipaddress = '', pool_key = 0, callingstationid = '', username = '', \
expiry_time IS NULL \
WHERE nasipaddress = '%{Nas-IP-Address}'"
## This series of queries frees the IP numbers allocate to a
## NAS when an accounting OFF record arrives
off-clear = "UPDATE ${ippool_table} \
SET nasipaddress = '', pool_key = 0, callingstationid = '', username = '', \
expiry_time IS NULL \
WHERE nasipaddress = '%{Nas-IP-Address}'"