#  $Id$
#
# FreeRADIUS rlm_sqlippool SQL Queries for the MySQL Dialect 

# ## This series of queries allocates an IP address
# allocate-clear = "UPDATE ${ippool_table} \
#  SET nasipaddress = '', pool_key = 0, callingstationid = '', username = '', \
#  expiry_time = '0000-00-00 00:00:00' \
#  WHERE pool_key = '${pool-key}'"

## This will clear all expired leases for lost acc-stop packets
allocate-clear = "UPDATE radippool \
 SET nasipaddress = '', pool_key = 0, callingstationid = '', username = '', \
 expiry_time = '0000-00-00 00:00:00' \
 WHERE expiry_time <= NOW() - INTERVAL 1 SECOND"


 
# ## 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 = '%{check: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, use this query instead
allocate-find = "SELECT framedipaddress FROM ${ippool_table} \
 WHERE pool_name = '%{check:Pool-Name}' AND expiry_time = '0000-00-00 00:00:00' \
 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='%{check:Pool-Name}' LIMIT 1"


 
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 = '0000-00-00 00:00:00' \
 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 = '0000-00-00 00:00:00' \
 WHERE nasipaddress = '%{Nas-IP-Address}' 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 OFF record arrives
off-clear = "UPDATE ${ippool_table} \
 SET nasipaddress = '', pool_key = 0, callingstationid = '', username = '', \
 expiry_time = '0000-00-00 00:00:00' \
 WHERE nasipaddress = '%{Nas-IP-Address}' AND username = '%{User-Name}' \
 AND callingstationid = '%{Calling-Station-Id}' AND framedipaddress = '%{Framed-IP-Address}'"