# -*- text -*-
##
## ippool-dhcp.conf -- MySQL queries for sqlippool-dhcp instance
## Only post-auth method is used
##
##	$Id: 1716ba99792b89e74ca65215bd1151ef3a060509 $

## This series of queries allocates an IP address
## First, clear expired entries
 allocate-clear = "UPDATE ${ippool_table} \
  SET nasipaddress = '', pool_key = 0, \
  callingstationid = '', username = '', \
  expiry_time = NULL \
  WHERE expiry_time <= NOW() - INTERVAL 1 SECOND \
  "

## Then find an available IP address
## The ORDER BY clause of this query tries to allocate the same IP address
## which user had last session regardless of expiry time 
## to handle DHCP request and duplicates from the same client
allocate-find = "SELECT framedipaddress FROM ${ippool_table} \
 WHERE pool_name = '%{control:Pool-Name}' AND \
 ( \
 (callingstationid = '%{Calling-Station-Id}') OR \
 (expiry_time < NOW() OR expiry_time IS NULL) \
 ) \
 ORDER BY (callingstationid = '%{Calling-Station-Id}') DESC, \
 expiry_time DESC\
 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}', \
 calledstationid = 'Freeradius-DHCP', \
 expiry_time = GREATEST( \
  IF(ISNULL(expiry_time),'0000-00-00 00:00:00',expiry_time), \
  NOW() + INTERVAL ${lease-duration} SECOND) \
 WHERE framedipaddress = '%I' AND \
 ( \
 (callingstationid = '%{Calling-Station-Id}') OR \
 (expiry_time < NOW() OR expiry_time IS NULL) \
 ) \
 "

## This series of queries frees an IP number when an accounting
## START record arrives. Unused, but must be set to non-empty query
start-update = "SELECT NOW()"

## This series of queries frees an IP number when an accounting
## STOP record arrives. Unused, but must be set to non-empty query
stop-clear = "SELECT NOW()"

## This series of queries frees an IP number when an accounting
## ALIVE record arrives. Unused, but must be set to non-empty query
alive-update = "SELECT NOW()"

## This series of queries frees the IP numbers allocate to a
## NAS when an accounting ON record arrives. Unused, but must be set
## to non-empty query
on-clear = "SELECT NOW()"

## This series of queries frees the IP numbers allocate to a
## NAS when an accounting OFF record arrives. Unused, but must be set
## to non-empty query
off-clear = "SELECT NOW()"