# -*- text -*-
#
#  ippool/mysql/queries.conf -- MySQL queries for rlm_sqlippool
#
#  $Id$


#  Using SKIP LOCKED speeds up selection queries
#  However, it requires MySQL >= 8.0.1 or MariaDB >= 10.6.
#  Uncomment the following if you are running a suitable
#  version of MySQL
#
#skip_locked = "SKIP LOCKED"
skip_locked = ""

#
#  This series of queries allocates an IP address
#

#
# Attempt to allocate the address a client previously had.  This is based on pool_key
# and nasipaddress.  Change the criteria if the identifier for "stickyness" is different.
# If different criteria are used, check the indexes on the IP pool table to ensure the fields
# are appropriately indexed.  To disable stickyness comment out this query.
#
allocate_existing = "\
	SELECT framedipaddress FROM ${ippool_table} \
	WHERE pool_name = '%{control:${pool_name}}' \
	AND nasipaddress = '%{NAS-IP-Address}' AND pool_key = '${pool_key}' \
	ORDER BY expiry_time DESC \
	LIMIT 1 \
	FOR UPDATE ${skip_locked}"

#
#  Find a free IP address from the pool, choosing the oldest expired one.
#
allocate_find = "\
	SELECT framedipaddress FROM ${ippool_table} \
	WHERE pool_name = '%{control:${pool_name}}' \
	AND expiry_time < NOW() \
	ORDER BY expiry_time \
	LIMIT 1 \
	FOR UPDATE ${skip_locked}"

#
#  If you prefer to allocate a random IP address every time, use this query instead.
#  Note: This is very slow if you have a lot of free IPs.
#
#allocate_find = "\
#	SELECT framedipaddress FROM ${ippool_table} \
#	WHERE pool_name = '%{control:${pool_name}}' \
#	AND expiry_time < NOW() \
#	ORDER BY \
#		RAND() \
#	LIMIT 1 \
#	FOR UPDATE ${skip_locked}"

#
#  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'"

#
#  Use a stored procedure to find AND allocate the address. Read and customise
#  `procedure.sql` in this directory to determine the optimal configuration.
#
#allocate_begin = ""
#allocate_find = "\
#	CALL fr_allocate_previous_or_new_framedipaddress( \
#		'%{control:${pool_name}}', \
#		'%{User-Name}', \
#		'%{Calling-Station-Id}', \
#		'%{Called-Station-Id}', \
#		'%{NAS-IP-Address}', \
#		'${pool_key}', \
#		${lease_duration} \
#	)"
#allocate_update = ""
#allocate_commit = ""

#
#  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 = '%{${attribute_name}}'"

#
#  This query expires an IP number when an accounting STOP record arrives.
#
stop_clear = "\
	UPDATE ${ippool_table} \
	SET \
		expiry_time = NOW() \
	WHERE nasipaddress = '%{%{Nas-IP-Address}:-%{Nas-IPv6-Address}}' \
	AND pool_key = '${pool_key}' \
	AND username = '%{User-Name}' \
	AND callingstationid = '%{Calling-Station-Id}' \
	AND framedipaddress = '%{${attribute_name}}'"

#
#  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}:-%{Nas-IPv6-Address}}' \
	AND pool_key = '${pool_key}' \
	AND username = '%{User-Name}' \
	AND callingstationid = '%{Calling-Station-Id}' \
	AND framedipaddress = '%{${attribute_name}}'"

#
#  This series of queries expires the IP numbers allocate to a
#  NAS when an accounting ON record arrives
#
on_clear = "\
	UPDATE ${ippool_table} \
	SET \
		expiry_time = NOW() \
	WHERE nasipaddress = '%{%{Nas-IP-Address}:-%{Nas-IPv6-Address}}'"

#
#  This series of queries expires the IP numbers allocate to a
#  NAS when an accounting OFF record arrives
#
off_clear = "\
	UPDATE ${ippool_table} \
	SET \
		expiry_time = NOW() \
	WHERE nasipaddress = '%{%{Nas-IP-Address}:-%{Nas-IPv6-Address}}'"