# -*- text -*-
#
#  ippool/postgresql/queries.conf -- PostgreSQL queries for rlm_sqlippool
#
#  $Id$


#  Using SKIP LOCKED speeds up selection queries
#  However, it requires PostgreSQL >= 9.5  Uncomment the
#  following if you are running a suitable version of PostgreSQL
#
#skip_locked = "SKIP LOCKED"
skip_locked = ""

#
#  This series of queries allocates an IP address
#

#
# The suggested queries locate IPs and update them in one query
# so no need for transaction wrappers
#
allocate_begin = ""
allocate_commit = ""

#
# 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 = "\
	WITH cte AS ( \
		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} \
	) \
	UPDATE ${ippool_table} \
	SET \
		nasipaddress = '%{NAS-IP-Address}', \
		pool_key = '${pool_key}', \
		callingstationid = '%{Calling-Station-Id}', \
		username = '%{SQL-User-Name}', \
		expiry_time = 'now'::timestamp(0) + '${lease_duration} second'::interval \
	FROM cte WHERE cte.framedipaddress = ${ippool_table}.framedipaddress \
	RETURNING cte.framedipaddress"

#
# Find a free IP address from the pool, choosing the oldest expired one.
#
allocate_find = "\
	WITH cte AS ( \
		SELECT framedipaddress FROM ${ippool_table} \
		WHERE pool_name = '%{control:${pool_name}}' \
		AND expiry_time < 'now'::timestamp(0) \
		ORDER BY expiry_time \
		LIMIT 1 \
		FOR UPDATE ${skip_locked} \
	) \
	UPDATE ${ippool_table} \
	SET \
		nasipaddress = '%{NAS-IP-Address}', \
		pool_key = '${pool_key}', \
		callingstationid = '%{Calling-Station-Id}', \
		username = '%{SQL-User-Name}', \
		expiry_time = 'now'::timestamp(0) + '${lease_duration} second'::interval \
	FROM cte WHERE cte.framedipaddress = ${ippool_table}.framedipaddress \
	RETURNING cte.framedipaddress"

#
#  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 = "\
#	WITH cte AS ( \
#		SELECT framedipaddress FROM ${ippool_table} \
#		WHERE pool_name = '%{control:${pool_name}}' \
#		AND expiry_time < 'now'::timestamp(0) \
#		ORDER BY RANDOM() \
#		LIMIT 1 \
#		FOR UPDATE ${skip_locked} \
#	) \
#	UPDATE ${ippool_table} \
#	SET \
#		nasipaddress = '%{NAS-IP-Address}', \
#		pool_key = '${pool_key}', \
#		callingstationid = '%{Calling-Station-Id}', \
#		username = '%{SQL-User-Name}', \
#		expiry_time = 'now'::timestamp(0) + '${lease_duration} second'::interval \
#	FROM cte WHERE cte.framedipaddress = ${ippool_table}.framedipaddress \
#	RETURNING cte.framedipaddress"

#
#  If an IP could not be allocated, check to see whether 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 query marks the IP address handed out by "allocate-find" as used
#  for the period of "lease_duration" after which time it may be reused.
#  This is only needed if the allocate_existing / allocate_find queries
#  do not update the pool
#
#allocate_update = "\
#	UPDATE ${ippool_table} \
#	SET \
#		nasipaddress = '%{NAS-IP-Address}', \
#		pool_key = '${pool_key}', \
#		callingstationid = '%{Calling-Station-Id}', \
#		username = '%{SQL-User-Name}', \
#		expiry_time = 'now'::timestamp(0) + '${lease_duration} second'::interval \
#	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.
#
#  This requires PostgreSQL >= 9.5 as SKIP LOCKED is used.
#
#  The "NO LOAD BALANCE" comment is included here to indicate to a PgPool
#  system that this needs to be a write transaction. PgPool itself cannot
#  detect this from the statement alone. If you are using PgPool and do not
#  have this comment, the query may go to a read only server, and will fail.
#  This has no negative effect if you are not using PgPool.
#
#allocate_begin = ""
#allocate_find = "\
#	/*NO LOAD BALANCE*/ \
#	SELECT fr_allocate_previous_or_new_framedipaddress( \
#		'%{control:${pool_name}}', \
#		'%{SQL-User-Name}', \
#		'%{Calling-Station-Id}', \
#		'%{NAS-IP-Address}', \
#		'${pool_key}', \
#		'${lease_duration}' \
#	)"
#allocate_update = ""
#allocate_commit = ""

#
#  This query extends an IP address lease by "lease_duration" when an accounting
#  START record arrives
#
start_update = "\
	UPDATE ${ippool_table} \
	SET \
		expiry_time = 'now'::timestamp(0) + '${lease_duration} second'::interval \
	WHERE nasipaddress = '%{NAS-IP-Address}' \
	AND pool_key = '${pool_key}'"

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

#
#  This query extends an IP address lease by "lease_duration" when an accounting
#  ALIVE record arrives
#
alive_update = "\
	UPDATE ${ippool_table} \
	SET \
		expiry_time = 'now'::timestamp(0) + '${lease_duration} seconds'::interval \
	WHERE nasipaddress = '%{%{Nas-IP-Address}:-%{Nas-IPv6-Address}}' \
	AND pool_key = '${pool_key}' \
	AND framedipaddress = '%{${attribute_name}}' \
	AND username = '%{SQL-User-Name}' \
	AND callingstationid = '%{Calling-Station-Id}'"

#
#  This query expires all IP addresses allocated to a NAS when an
#  accounting ON record arrives from that NAS
#
on_clear = "\
	UPDATE ${ippool_table} \
	SET \
		expiry_time = 'now'::timestamp(0) - '1 second'::interval \
	WHERE nasipaddress = '%{%{Nas-IP-Address}:-%{Nas-IPv6-Address}}'"

#
#  This query expires all IP addresses allocated to a NAS when an
#  accounting OFF record arrives from that NAS
#
off_clear = "\
	UPDATE ${ippool_table} \
	SET \
		expiry_time = 'now'::timestamp(0) - '1 second'::interval \
	WHERE nasipaddress = '%{%{Nas-IP-Address}:-%{Nas-IPv6-Address}}'"