# -*- text -*-
#
#  ippool-dhcp/mysql/queries.conf -- MySQL queries for rlm_sqlippool
#
#  $Id: 859d90a17d5ccb69107be1e11d86f910c8a6fee9 $

#
# This series of queries allocates an IP address
#
#allocate_clear = "\
#	UPDATE ${ippool_table} \
#	SET \
#		nasipaddress = '', \
#		pool_key = 0, \
#		callingstationid = '', \
#		username = '', \
#		expiry_time = NOW() \
#	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 = NOW() \
	WHERE expiry_time <= NOW() - INTERVAL 1 SECOND \
	AND nasipaddress = '%{Nas-IP-Address}'"

#
#  Search for the SAME Calling-Station-ID as last time, OR an entry
#  where there is no Calling-Station-Id.
#
#  If the lease was expired, the allocate_clear above will reset the
#  Calling-Station-Id to ''.
#
#  If the lease wasn't expired, we want to give the user the same IP
#  as last time.
#
#  Then, we order by expiry_time, so that when there is no existing
#  entry for the Calling-Station-Id, it picks the OLDEST expired IP.
#
allocate_find = "\
	SELECT framedipaddress \
	FROM ${ippool_table} \
	WHERE pool_name = '%{control:${pool_name}}' \
	AND (callingstationid = '%{Calling-Station-Id}' or callingstationid = '') \
	ORDER BY \
		(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 = '%{control:${pool_name}}' \
#	AND expiry_time < NOW() \
#	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}' \
	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
#  STOP record arrives
#
stop_clear = "UPDATE ${ippool_table} \
	SET \
		nasipaddress = '', \
		pool_key = 0, \
		callingstationid = '', \
		username = '', \
		expiry_time = NOW() \
	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 = NOW() \
	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 = NOW() \
	WHERE nasipaddress = '%{Nas-IP-Address}'"