sqlippool sqlippool {

 #
 # SQL connection information
 #
 sql-instance-name = "sql"

 # lease_duration. fix for lost acc-stop packets
 lease-duration = 3600

 # Attribute which should be considered unique per NAS
 pool-key = "%{NAS-Port}"
 # pool-key = "%{Calling-Station-Id}"


 #
 # This series of queries allocates an IP address
 #
 allocate-clear = "UPDATE radippool \
  SET nasipaddress = '', pool_key = 0, callingstationid = '', \
  expiry_time = 'now'::timestamp(0) - '1 second'::interval \
  WHERE pool_key = '%{Calling-Station-Id}'"

 # note the ORDER BY clause of next query, it'll try to allocate IPs
 # like Cisco internal pools do - it _trys_ to allocate the same IP-address
 # which user had last session...
 allocate-find = "SELECT framedipaddress FROM radippool \
  WHERE pool_name = '%{reply:Pool-Name}' AND expiry_time < 'now'::timestamp(0) \
  ORDER BY pool_name, (username <> '%{User-Name}'), (callingstationid <> '%{Calling-Station-Id}'), expiry_time \
  LIMIT 1 \
  FOR UPDATE"

 allocate-update = "UPDATE radippool \
  SET nasipaddress = '%{NAS-IP-Address}', pool_key = '%{Calling-Station-Id}', \
  callingstationid = '%{Calling-Station-Id}', username = '%{User-Name}', \
  expiry_time = 'now'::timestamp(0) + '${lease-duration} second'::interval \
  WHERE framedipaddress = '%I'"



 #
 # This series of queries frees an IP number when an accounting
 # START record arrives
 #
 start-update = "UPDATE radippool \
  SET expiry_time = 'now'::timestamp(0) + '%J second'::interval \
  WHERE nasipaddress = '%n' AND nas_port = '%p' AND pool_name = '%P'"

 #
 # This series of queries frees an IP number when an accounting
 # STOP record arrives
 #
 stop-clear = "UPDATE radippool \
  SET nasipaddress = '', pool_key = 0, callingstationid = '', \
  expiry_time = 'now'::timestamp(0) - '1 second'::interval \
  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 radippool \
  SET expiry_time = 'now'::timestamp(0) + '${lease-duration} seconds'::interval \
  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 radippool \
  SET nasipaddress = '', pool_key = 0, callingstationid = '', \
  expiry_time = 'now'::timestamp(0) - '1 second'::interval \
  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 radippool \
  SET nasipaddress = '', pool_key = 0, callingstationid = '', \
  expiry_time = 'now'::timestamp(0) - '1 second'::interval \
  WHERE nasipaddress = '%{Nas-IP-Address}' AND username = '%{User-Name}' \
  AND callingstationid = '%{Calling-Station-Id}' AND framedipaddress = '%{Framed-IP-Address}'"


}